TABLE OF CONTENTS
There are six transactional data related with orders which need to perform the migration:
- Purchase Orders
- Customer Orders
- Transfer Orders
- Job Orders
- Job Routes
- 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:
| FIELD | DESCRIPTION | DATA TYPE | MAX LENGTH | CAN ADD | CAN EDIT | COMPULSORY |
|---|---|---|---|---|---|---|
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 line | Numeric | 10 | Yes | No | Yes 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:
| FIELD | DESCRIPTION | DATA TYPE | MAX LENGTH | CAN ADD | CAN EDIT | COMPULSORY |
|---|---|---|---|---|---|---|
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 | Numeric | 10 | Yes | No | Yes 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:
| FIELD | DESCRIPTION | DATA TYPE | MAX LENGTH | CAN ADD | CAN EDIT | COMPULSORY |
|---|---|---|---|---|---|---|
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:
| FIELD | DESCRIPTION | DATA TYPE | MAX LENGTH | CAN ADD | CAN EDIT | COMPULSORY |
|---|---|---|---|---|---|---|
job_num | A job order number uniquely identifies a job order | String | 30 | Yes | No | Yes |
| suffix | The job order suffix number. (job_num + suffix must be unique)j | Numeric | 4 | Yes | No | Yes |
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 Initial job order status, must be "Open". "Released" status is not allowed without at least one job route | 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:
| 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 |
| suffix | The job order suffix number. (job_num + suffix must exist in Job Orders transactions) | Numeric | 4 | Yes | No | Yes |
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:
| FIELD | DESCRIPTION | DATA TYPE | MAX LENGTH | CAN ADD | CAN 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 |
| suffix | The job order suffix number. (job_num + suffix must exist in Job Routes transactions) | Numeric | 4 | Yes | No | Yes |
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 |