Extraction of the report based on the Subsidiary and the date taken is 5/31/2021. The Report here used is JJ Open Purchase Orders. Each Report are generated and extracted based on the 12 subsidiaries.

Import of Purchase Order

Extraction of the report based on the Subsidiary and the date taken is 5/31/2021. The Report here used is JJ Open Purchase Orders. Each Report are generated and extracted based on the 12 subsidiaries.

PFHC LTG Inc
PFHC:LTG Holdings Limited:NetDimensions, Inc.
PFHC:LTG Inc:PeopleFluent, Ltd.
PFHC:LTG Holdings Limited:NetDimensions (UK) Limited
PFHC:LTG Holdings Limited:LTG (Hong Kong) Limited
PFHC:LTG Holdings Limited:NetDimensions Ltd (China):LTG Pty (Aust) Limited
PFHC:LTG Holdings Limited:NetDimensions (UK) Limited:LTG GmbH
PFHC:LTG Holdings Limited:LTG (Colombia) S.A.S
PFHC:LTG Holdings Limited:LTG (Brazil)
PFHC:LTG Holdings Limited:LTG (Hong Kong) Limited:NetDimensions Asia Limited
PFHC:LTG Holdings Limited:LTG (Hong Kong) Limited:NetDimensions Asia Limited:NetDimensions Services Asia Limited
PFHC:LTG Holdings Limited:LTG UK MEX SDRL
PFHC:LTG (Canada) Inc

Here below taken an example to show how the file looks like after extraction of each subsidiary. (Here eg taken is for the subsidiary PFHC : LTG Inc

After the report is generated for 12 subsidiaries, consolidate them into one single file as the Main PO Report File. Copy all to a fresh excel sheet and likewise copy for all the subsidiaries into a single file.

Next is to generate a search file by creating a search from the Reports-Saved Searches-All Saves Searches-New. The Search File should contain all the fields that are not there in the Main Report and also meant to be in the PO Import. So, take the fields accordingly. This is for the Vlookup and mapping purposes.

Under the New Saved search-Transaction-Criteria-Set Type as PO.

Go to the Results Sublist – Ste all the fields needed in the report (Budget ID, Approval Status, Billing City, Internal ID, Vendor Name, Vendor Internal ID, Document Number, Account(LIne Fields)-Name, Number,Type)

Once the search file is created, create a unique id by concatenating Document Number + vendor name in both files.

In Search File

Doc Number – Col F

Vendor Name – Col D

In Main Consolidated File

Doc Number – G

Vendor Name – H

Insert a Col External Field in the Report File (Main file)- Col E and vlookup the Internal ID(Col D) of the Search File that will become the External ID in the Report File.

Insert another Column “Line External ID” (Col F)
in the report file to give unique values to each PO consolidated to avoid the confusion in NetSuite to take all the PO’s accordingly.

The Vendor Internal ID(Col G) is vlooked up and mapped to the main report from the search file using the Unique Id created in both files.

The Approval status, billing city and budget ID is vlooked up from the Search file and bring it to the main file before segregation.

The external ID of the Vendor is generated by concatenating the text “NS” + Vendor Internal ID(which has been Vlookedup).

There are some adjustments done on a temporary basis.
The Col “Term” is filled with the value “Net 30” for all the blank values under the Term Field in the Main Report temporarily.
The fields “Bill Approver” and “Employee” is given the value of “Ryan Howson”.
Next is to insert a new Column called “PO Currency Amount”. calculate the PO Currency Amount, by dividing the Open Currency Amount/ Exchange Rate. Formula is =Round(Open Currency Amount/Exchange Rate, 2). Before that we need to remove the “$” by using if and replace option.Use “$” with null. After removing the $ symbol, calculate the PO Currency Amount.
After Calculating the PO Currency Amount, Insert a new Column called “Item Rate”. For that we need to Divide the PO Currency Amount/Quantity Open. The formula used is =Round(PO Currency Amount/Quantity Open).

Mapping for the New Department, New Business Unit, New Subsidary and New COA Internal ID is done by using the files given
Subsidiary Mappinghttps://drive.google.com/file/d/15vbZmwV0SOem2tZYpqhKev3XQCD50I6H/view?usp=sharingKndly note that when mapping subsidiary the hierarcial name of the subsidiary should be mapped.
Department Mappinghttps://docs.google.com/spreadsheets/d/1B-RaEiEphmE1NHYkq5og4IYGf09hJoQcYWQ03PMLi8k/edit?usp=sharing,
BU Mappinghttps://drive.google.com/file/d/1db3xojSc58wLbURxmN0C0hy-v87lMyxa/view?usp=sharing
COA Mapping (Account Mapping)https://drive.google.com/file/d/1gUNkafqTm2q7SNy9Tk4AmS993isO8FIY/view?usp=sharing

For the new COA Internal ID, the old account needs to be taken for vlookup instead of the old one.

Item Name and Item Code Mapping

The item file is stored in the PO folder – Errored – Other Files_PO _Item File

Take the New Item Name and Its Internal Code and vlookup to the Report file.

Tax Code Mapping

The tax code is mapped using the 2 files, one given by Atna and the next one is tax code file given along with the requirement.

The fields contaning the currency value “Euro” need to be changed to “EUR” as NetSuite does not accept “Euro”.
Now the Segregation begins
The consolidated file is filtered for the Current Status field and not the Approval Status field and the file is filtered into 3.
Closed and Fully Billed (Need not be imported) – so remove the records from the report file and keep it separately
Pending Supervisor
Other status POs with approved status.
Pending Supervisor is further divided into 2 more files
Pending Supervisor_with Country
Pending Supervisor_without country
Other Status file
Other Status_with country
Other Status_without country

These 4 files need to be imported seperately bcoz country is a required field

Mapping Process

Before starting the import, the allow override option should be enabled for the document numbers. For that need go to Setup-Company-Autogenerated Numbers-Under Document Numbers sub list- Enable the check box “Allow Override” for the PO section.
Setup-Import/Export-Import CSV Records
The Import type – Transaction and Record Type-Purchase Order. Load the file to be imported.
Add
Map all the fields to the corresponding NetSuite Fields.