TABLE OF CONTENTS

There are six transactional data related with orders which need to perform the migration:

  1. Purchase Orders
  2. Customer Orders
  3. Transfer Orders
  4. Job Orders
  5. Job Routes
  6. Job Materials

Note: import ONLY Open Orders during data migration (partially shipped/received or not shipped/received at all).


Purchase Orders

User can enter the purchase order record under Transactions Maintenance. After download the Excel templates, user can open the Excel with file name ‘Import-PO’ and input the details. The format of Purchase Orders import file is as below:

FIELDDESCRIPTIONDATA TYPEMAX LENGTHCAN ADDCAN EDITCOMPULSORY

po_num

A PO Number uniquely identifies a purchase order

String

30

Yes

No

Yes

po_line

Purchase order line number

Numeric

30

Yes

No

Yes

po_rel

Release number for the order lineNumeric10YesNoYes
Can use 1 for all PO lines

vend_do

Vendor delivery order number

String

30

Yes

No (via web)

Yes (via import Excel file)

No

qty_ordered

The quantity item required.

Numeric

NA

Yes

Yes

Must not less than quantity received

Yes

due_date

Target receiving date for the line

Date (YYYY-MM-DD)

NA

Yes

Yes

Yes

item_num

Item that is ordered from the vendor

(Must exist in Items master)

String

30

Yes

Yes, if quantity received is 0

Yes, if item is exist in Items master

No, if is non-inventory item

item_desc

Enter description for non-inventory item

String

191

No, if item is exist in Items master

Yes, if is non-inventory item

No

No, if item is exist in Items master

Yes, if is non-inventory item

vend_num

Vendor of PO

(Must exists in Vendors master)

String

30

Yes

Yes if the Qty Receipt is 0.

Yes

whse_num

Warehouse to receive the item

(Must exist in Warehouses master)

String

30

Yes

No

Yes

rel_status

Open: PO line still in process.

Completed: PO line completely received

List:

O - Open (Default)

C - Completed

NA

Yes

Yes

Yes

notes

Remarks on the PO Line

String

100

Yes

Yes

No

uom

Order line item’s unit of measurement

(Must exist in UOMs master and UOM Conversions master if is not item’s base uom)

String

3

Yes

No

Yes


Customer Orders

User can enter the customer order record under Transactions Maintenance. After download the Excel templates, user can open up the Excel with file name ‘Import-CO’ and input the details. The format of Customer Orders import file is as below:

FIELDDESCRIPTIONDATA TYPEMAX LENGTHCAN ADDCAN EDITCOMPULSORY

co_num

A CO Number uniquely identifies a customer order

String

30

Yes

No

Yes

co_line

Customer order line number

Numeric

30

Yes

No

Yes

co_rel

Release number for the order line
Numeric10YesNoYes
Can use 1 for all CO lines

cust_num

Customer of the order

(Must exist in Customers master)

String

30

Yes

No (via web)

Yes (via import Excel file)

Yes

add_num

Delivery address

String

30

Yes

Yes

No

due_date

Target shipping date of the line

Date

NA

Yes

Yes

Yes

qty_ordered

Quantity of the item ordered

Numeric

NA

Yes

Yes

Must not less than quantity shipped

Yes

item_num

Item that is ordered by customer

(Must exist in Items master)

String

30

Yes

Yes, if Quantity Picked, Quantity Allocated and Quantity Shipped are 0

Yes

whse_num

Warehouse to ship from

(Must exist in Warehouses master)

String

30

Yes

No

Yes

rel_status

Open: CO line is still in process. Line is not completely shipped.

Completed: CO line fully shipped

List:

O - Open (Default)

C - Completed

NA

Yes

Yes

Yes

uom

Order line item’s unit of measurement

(Must exist in UOMs master and UOM Conversions master if is not item’s base uom)

String

3

Yes

No

Yes


Transfer Orders

User can enter the transfer order record under Transactions Maintenance. After download the Excel templates, user can open up the Excel with file name ‘Import-TO’ and input the details. The format of Transfer Orders import file is as below:

FIELDDESCRIPTIONDATA TYPEMAX LENGTHCAN ADDCAN EDITCOMPULSORY

trn_num

A TO Number uniquely identifies a transfer order

String

30

Yes

No

Yes

from_whse

From warehouse. Identifies the warehouse from which inventory items are being transferred.

(Must exist in Warehouses master)

String

30

Yes

No

Yes

Must be different from To Warehouse

to_whse

To warehouse. Identifies the warehouse to which inventory items are being transferred.

(Must exist in Warehouses master)

String

 

30

Yes

No

Yes

Must be different from From Warehouse

trn_loc

Transit location. Temporary location to hold the item while moving to another warehouse. Also known as in-transit location

(Must exist in Locations master and location type is Transit)

String

30

Yes

Yes, if quantity shipped is 0

Yes

schedule_receive_date

Scheduled receive date of the transfer order line

Date

NA

Yes

Yes

No

schedule_ship_date

Scheduled ship date of the transfer order line

Date

NA

Yes

Yes

No

pickup_date

Pickup date of the transfer order line

Date (YYYY-MM-DD)

NA

Yes

Yes

No

driver_name

Driver name

String

30

Yes

Yes

No

driver_contact

Driver contact

String

30

Yes

Yes

No

vehicle_num

Vehicle number

String

30

Yes

Yes

No

trn_line

Transfer order line number

Numeric

30

Yes

No

Yes

item_num

Item which need to be transfer

(Must exist in Items master)

String

30

Yes

Yes, if Quantity Shipped is 0 and is not with Open/Generated 

Yes

qty_required

Quantity of the item need to transfer

Numeric

NA

Yes

Yes

Must not less than quantity shipped

Yes

line_stat

Open: TO line is still in process. Line is not completely shipped and received.

Completed: TO line fully shipped and received

List:

O - Open (Default)

C - Completed

NA

Yes

Yes

Yes

uom

Order line item’s unit of measurement

(Must exist in UOMs master and UOM Conversions master if is not item’s base uom)

String

3

Yes

No

Yes

status

Open: TO is still in process. 

Completed: TO fully shipped and received.

List:

O - Open (Default)

C - Completed

NA
Yes
Yes
Yes

Job Orders

User can enter the job order record under Transactions Maintenance. After download the Excel templates, user can open up the Excel with file name ‘Import-JobOrder’ and input the details. The format of Job Orders import file is as below:

FIELDDESCRIPTIONDATA TYPEMAX LENGTHCAN ADDCAN EDITCOMPULSORY

job_num

A job order number uniquely identifies a job order

String

30

Yes

No

Yes

suffixThe job order suffix number.

(job_num + suffix must be unique)j
Numeric4YesNoYes

whse_num

Warehouse to receive the job

(Must exist in Warehouses master)

String

30

Yes

No

Yes

cust_num

Identifies if the job order is to fulfill which customer

(Must exist in Customers master)

String

30

Yes

No

No

job_date

Job order creation date

Date (YYYY-MM-DD)

NA

Yes

Yes

Yes

start_date_plan

Date to begin production

Date (YYYY-MM-DD)

NA

Yes

Yes

Yes

end_date_plan

Target date of job completion

Date (YYYY-MM-DD)

NA

Yes

Yes

No

item_num

Item that is being produced from the job order

(Must exist in Items master)

String

30

Yes

No

Yes

job_status

 

Open: Job is still not ready for execution

Released: Job is ready to execute or in process

Completed: Job order is closed

List:

O - Open (Default)

R - Released

C - Completed

NA

Yes

Yes

Yes

qty_released

The quantity required to be produced

Numeric

NA

Yes

Yes

Must not less than quantity completed

Yes

notes

Remarks on the job order

String

100

Yes

Yes

No


Job Routes

User can enter the job routes record under Transactions Maintenance. User must create Job Order before entering the Job Routes record. After download the Excel templates, user can open up the Excel with file name ‘Import-JobRoute’ and input the details. The format of Job Routes import file is as below:

FIELDDESCRIPTIONDATA TYPEMAX LENGTHCAN ADDCAN EDITCOMPULSORY

job_num

A job order number uniquely identifies a job order

(Must exist in Job Orders transactions and is from the selected warehouse)

String

30

Yes

No

Yes

suffixThe job order suffix number.

(job_num + suffix must exist in Job Orders transactions)
Numeric4YesNoYes

oper_num

Sequence number of the job route

Numeric

NA

Yes

Must not repeat for the same job order

No

Yes

oper_status

Indicator of activeness. If unchecked, the system will not show the job route in the drop-down lists.

Boolean:

0 - Inactive

1 - Active (Default)

NA

Yes

Yes

Yes

wc_num

The station/work center performing the process

(Must exist in Work Centers master)

String

30

Yes

No

Yes

notes

Remarks on the job route

String

100

Yes

Yes

No


Job Materials

User can enter the job materials record under Transactions Maintenance. User must create Job Order and Job Routes before entering the Job Materials record. After download the Excel templates, user can open up the Excel with file name ‘Import-JobMaterial’ and input the details. The format of Job Routes import file is as below:

FIELDDESCRIPTIONDATA TYPEMAX LENGTHCAN ADDCAN ED

FIELD

DESCRIPTION

DATA TYPE

MAX LENGTH

CAN ADD

CAN EDIT

COMPULSORY

job_num

A job order number uniquely identifies a job order

(Must exist in Job Orders transactions and is from the selected warehouse)

String

30

Yes

No

Yes

suffixThe job order suffix number.

(job_num + suffix must exist in Job Routes transactions)
Numeric4YesNoYes

oper_num

Sequence number of the job route

(Must exist in Job Routes transactions and is from the selected job order)

Numeric

NA

Yes

No

Yes

sequence

Sequence number of job material

Numeric

NA

Yes

Must not repeat for the same job route

No

Yes

matl_item

Job material item required in the operation

(Must exist in Items master)

String

30

Yes

No

Yes

qty_per

Average quantity required for single unit of job item

Numeric

NA

Yes

Yes

Yes

scrap_factor

The expected loss factor

Numeric

NA

Yes

Yes

Yes