The application copies rows from the input file into the worktables.
Each sales order is validated in its entirety.
The application inserts any errors found into the error table.
Defaults are applied.
Sales orders with no errors are inserted or updated in the Costpoint tables.
If a sales order has errors, all its rows in the input file are written to the error file (*.ERR).
The application renames the input file (*.OLD).
You can view or print the Error Report from the screen.
Upon initialization, the application deletes data from the work files.
Header modification records can exist in the input file without associated line records, provided that a sales order header and line record already exist for that sales order in the data tables.
Line modification records cannot exist in the input file without the associated header record.
Note modification records cannot exist in the input file without the associated line record and header record.
When you enter sales orders through this interface, the system marks them as loaded implementing the Modified By field in all affected tables using the Input Sales Order program name (AOPSOPP).
After running this application, you can view and/or update the sales orders on the Manage Sales Orders screen. We recommend that you update sales orders in the source system and upload them through the preprocessor.
When you create a new sales order, your company ID is inserted in the SO_HDR table. Before you can change an existing SO, the company ID in the SO header must match your company ID. Users can perform uploads for only one company (the user's) at a time.
The preprocessor performs a number of calculations and edits while uploading header and line records. The following information outlines specific edits and calculations performed by the preprocessor for line records:
Order Quantity is required in the input file.
All amounts in the input file should be in the transactional currency.
Discount Amount is not a field in the input file, but is calculated for sales order lines as follows:
Gross Unit Price * Volume Discount Percentage = Discount Amount
Net Unit Price is not a field in the input file, but is a field on the Manage Sales Orders screen. The preprocessor calculates Net Unit Price for sales order lines as follows:
Gross Unit Price – Discount Amount = Net Unit Price
Extended Price Amount is optional in the input file. If the Extended Price Amount is not provided for the sales order line in the input file, the preprocessor calculates as follows:
Order Quantity * Net Unit Price = SO Line Extended Price Amount
If the Extended Price Amount is in the input file, the preprocessor verifies that the calculation holds true. If the calculation does not, the line record is sent to the error file.
NOTE: This validation only applies if SO Line Type = MISC/ Service and Order Qty = ‘0’
Sales Tax/VAT Code is optional in the input file. If the Sales Tax/VAT Code is provided in the input file, the code is verified in the Sales Tax/VAT table and the preprocessor retrieves the associated tax rate. The Sales Tax/VAT Amount is calculated as follows:
SO Line Extended Price Amount * Sales Tax/VAT Rate = Sales Tax/VAT Amount
If the Sales Tax/VAT Code is not provided in the input file, no Sales Tax/VAT amount is calculated for the line.
SO Line Total Amount is not a field in the input file, but is a field on the Manage Sales Orders screen. The preprocessor calculates the amount as follows:
SO Line Extended Price Amount + Sales Tax/VAT Amount = SO Line Total Amount
Additionally, the preprocessor performs a number of calculations specific to Header records.
SO Total Amount is not a field in the input file, but is a field on the Manage Sales Orders screen. The preprocessor calculates the amount as the sum of all SO line Total Amounts.
Discount Amount is not an input file field. It is located on the Manage Sales Order screen. The preprocessor calculates the amount as the sum of all SO line Discount Amounts.
If the input file contains sales orders and/or lines and/or notes records that already exist in the database, the system assumes that the record in the system needs to be updated. Therefore, fields in the input file take precedence and override existing data in the system. For example, if a sales order line quantity has changed for a previously uploaded line record, the program uses the quantity in the input file and overwrites the quantity in the system. Modifications to existing records pass through the same calculations and edits as listed above.
DRP (Dropshipment), INO (Invoice Only), INT (Intransit), INV (Inventory), and MSC (Miscellaneous) are the only line types supported at this time. REC (Recurring) line types will be available in the future.
When you use the INV line type, entries/updates are made in the Reservation Header (RES_HDR), Reservation Line (RES_LN), and the Inventory (INVT) tables for valid input files only. If the sales order line Order Qty is changed to zero, then the reservation line is deleted. If no more reservation lines exist in the reservation, the RES_HDR is deleted as well. The SO line Order Qty cannot be modified to a value that is less than the total issued quantity or total invoiced quantity for the line. If the sales order status is pending (SO_HDR. S_SO_STATUS_CD is P) and MRP_SETTINGS.INCL_PEND_SO_FL is Y, the planned reserved quantity (INVT.PLND_RES_QTY) is incremented, and reservation remains pending (RES_HDR.S_APPRVL_CD is P). If the SO_HDR status is approved, the reserved quantity (INVT.RES_QTY) is updated and the planned reserved quantity is decreased (if previously pending), and reservation is changed to Approved (RES_HDR.S_APPRVL_CD is A). If the SO_HDR status is pending (SO_HDR. S_SO_STATUS_CD is P) and MRP_SETTINGS. INCL_PEND_SO_FL is N, neither the planned reserved quantity nor the reserved quantity in INVT are incremented; and a row is not inserted into RES_HDR. If the SO_HDR status is approved, a row is inserted into RES_HDR/RES_LN, and the reserved quantity (INVT.RES_QTY) is updated.
If you do not use a Pricing Project, you must provide a valid Warehouse ID in the input file for INV lines, or the record will be rejected.
The preprocessor allows the use of Sales Abbreviation codes that are project substitutable, but you must include the Project To Charge (CHG_PROJ_ID) for each Sales Order Line in the input file.
With multicurrency, you can create sales orders in a currency (called transactional currency) that is different than the functional currency (which is the currency that you use for your company's financial statements, and is selected during Costpoint initialization). You enter sales order information primarily in the transactional currency, and the system calculates the corresponding value for the functional currency fields. Data is stored in both currencies.
All amount fields in the input file are assumed to be in the transactional currency (which may or may not be the same as the functional currency). During processing, the system calculates the corresponding value in terms of the functional currency using appropriate exchange rates (depending upon settings and currencies involved). Data is stored in both currencies. The system performs subsequent calculations separately for each currency. Refer to the Detailed Table Specifications section for more information.
For example, the Gross Unit Price Amt in the SO line from the input file loads into the Trans Currency Gross Unit Price column (SO_LN.TRN_GR_UNIT_PR_AMT), and the system calculates the value of Gross Unit Price (SO_LN.GROSS_UNIT_PRC_AMT) in terms of functional currency. Similarly, this calculation applies to other fields that also use functional and transaction currency amounts.
The system performs subsequent calculations separately for each currency. For example, the input file has a Gross Unit Price of "100" for the sales order line, and the transactional currency is in British Pounds (GBP). This translates to "160," where the functional currency is in US Dollars (USD) and the exchange rate of GBP to USD is 1.6. Both values are stored separately in different fields for the sales order line (Transaction Gross Unit Price equals 100, Gross Unit Price equals 160). Subsequent calculations (like Discount Amount) are also calculated and stored separately. If the discount rate is10%, the SO line Discount Amount is 10 (GBP) and 16 (USD) and stored as Transaction Discount Amount (SO_LN.TRN_DISC_AMT) equals 10, and Discount Amount (SO_LN.DISC_AMT) equals 16.
The system uses standard multicurrency functions to calculate exchange rates between transactional and functional currencies. This applies exchange rates corresponding to the Rate Group and Rate Date (or Rate Period). For non-Euro currencies, the Trans to Func exchange rate is used. If a direct exchange rate is not available, the system uses the triangulation method (using a third common rate) to derive the exchange rate (if one is set up on the Configure Multicurrency Settings screen). For Euro currencies, the system uses the product of Trans to Euro and Euro to Func exchange rates to derive the appropriate exchange rate between the transactional and functional currencies.
This section defines the data elements in each table that are loaded or updated by the Sales Order preprocessor. Use the following outline as a guide for entering default and edited data in columns and tables in Costpoint. The outline is organized by table (for example, Sales Order Header, Sales Order Line, etc.), and lists the Costpoint column (and associated column name), data source, default field data, and any applicable validations and notes about the column data. Before you can execute the preprocessor, be sure that the required data tables and system controls are implemented in Costpoint. Likewise, you should set up any optional tables containing default data. The preprocessor employs default values from the required and optional tables as much as possible. You can enter most of the default data in the Customer table and in the Sales Order Entry Controls menu. Please refer to the Sales Order Entry documentation and/or contact your Deltek representative for assistance before executing this preprocessor.
|
Column Name |
Costpoint Column Name |
Source |
Defaults |
Validations/Notes |
|
ACRN (Accounting Classification Reference Number) |
ACRN_ID |
Input file |
None |
None |
|
Acknowledgment Printed |
ACKN_PRNTD_FL |
Set by application |
N (No) |
None |
|
Acknowledgment Print Date |
ACKN_PRNT_DT |
Set by application |
Null |
None |
|
Acknowledgment Required |
ACKN_REQD_FL |
Set by application |
Customer table |
Must be Y (Yes) or N (No) |
|
Account (Accounts Receivable) |
AR_ACCT_ID |
Set by application |
Sales Group Accounts table where Product Transaction Type (S_PROD_TRN_TYPE) is AR |
None |
|
Organization (Accounts Receivable) |
AR_ORG_ID |
Set by application |
Sales Group Accounts table where Product Transaction Type is AR |
See note 1 |
|
Project (Accounts Receivable) |
AR_PROJ_ID |
Set by application |
Sales Group Accounts table where Product Transaction Type is AR |
See note 1 |
|
Reference 1 (Accounts Receivable) |
AR_REF1_ID |
Set by application |
Sales Group Accounts table where Product Transaction Type is AR |
See note 1 |
|
Reference 2 (Accounts Receivable) |
AR_REF2_ID |
Set by application |
Sales Group Accounts table where Product Transaction Type is AR |
See note 1 |
|
Alternate Release Procedure |
ARP_FL |
Input file |
N |
Must be Y (Yes) or N (No) |
|
Billing Cycle |
BILL_CYCLE_CD |
Input file |
None |
Must exist in Bill Cycle table |
|
Bill Payment Address |
BILL_PMT_ADDR_CD |
Input file |
Customer table, or Order Entry Settings table depending on either Pricing Project or Pricing Catalog; otherwise, null |
See note 1 Must exist in the Bill Payment Address table |
|
Bill To Address code |
BILL_TO_ADDR_DC |
Input file |
Customer Address table |
See note 1 Must exist in the Customer Address table, and Billing Address Code must be D (Default) or Y (Yes) |
|
Charge Project |
CHG_PROJ_ID |
Input file |
Customer table or Sales Abbreviation table |
See note 1 Must exist in the Projects table |
|
Contact First Name |
CNTACT_FIRST_NAME |
Input file |
Customer table; otherwise, null |
None |
|
Contact Last Name |
CNTACT_LAST_NAME |
Input file |
Customer table; otherwise, null |
None |
|
Certificate of Conformance |
COC_FL |
Input file |
N |
Must be Y (Yes) or N (No) |
|
Construction Certificate |
CONSTR_FL |
Input file |
N |
Must be Y (Yes) or N (No) |
|
Contractor Address code |
CONTR_ADDR_CD |
Input file |
Customer table or Order Entry Settings table based on either Pricing Project or Pricing Catalog; otherwise, null |
Contractor Address table; field is validated based on user's company ID |
|
Prime Contractor CAGE Code |
CONTR_CAGE_ID |
Input file |
OE_PROJ_SETTINGS.CONTR_CAGE_ID if SO_HDR.PRICE_PROJ_ID is not null; OE_CATLG_SETTINGS.CONTR_CAGE_ID if SO_HDR.PRICE_CATLG_CD is not null; otherwise, null |
None |
|
Customer |
CUST_ID |
Input file |
None |
See note 1 Required; must exist in Customer table |
|
Customer Terms Key |
CUST_TERMS_KEY |
Input file |
Customer Terms table or Order Entry Settings table depending on either Pricing Project or Pricing Catalog |
Must exist in the Customer Terms table |
|
Print DD250 Invoice |
DD250_INVC_FL |
Set by application |
Order Entry Settings table or Order Entry Project Settings/Order Entry Catalog Settings tables depending on either Pricing Project or Pricing Catalog |
Must be Y (Yes) or N (No) |
|
Print DD250 Packing Slip |
DD250_PS_FL |
Set by application |
Order Entry Settings table or Order Entry Project Settings/Order Entry Catalog Settings tables depending on either Pricing Project or Pricing Catalog |
Must be Y (Yes) or N (No) |
|
Deliver To |
DEL_TO_FLD |
Input file |
Null |
None |
|
Desired Date |
DESIRED_DT |
Input file |
Due Date |
Must be a valid date equal to or later than Order Date |
|
Default Taxable |
DFLT_TAXABLE_FL |
Input file |
If Tax Exemption ID is not populated for the Customer ID, Default Taxable flag is Y (Yes); otherwise, N (No) |
Must be Y (Yes) or N (No) |
|
Discount Amount (Func Currency) |
DISC_AMT |
Set by application |
Sum of Discount Amounts for all sales order lines (functional currency) |
None |
|
DPAS Code (Defense Priority Acquisition System) |
DMS_DPS_CD |
Set by application |
Projects table |
None |
|
Due Date |
DUE_DT |
Input file |
None; optional for Header record when provided in Line record |
Must be a valid date later than or the same as the Order Date |
|
EDI (Electronic Data Interchange) Generated |
EDI_GEN_FL |
Set by application |
Y (Yes) |
None |
|
EDI (Electronic Data Interchange) Generated Date |
EDI_GEN_DT |
Set by application |
Current Date |
None |
|
Exceed Blanket Total |
EXCD_BLKT_TOT_FL |
Set by application |
N (No) |
Must be N (No) |
|
Customer Modification ID |
EXT_CUST_MOD_ID |
Input file |
Null |
None |
|
Customer Purchase Order |
EXT_CUST_PO_ID |
Input file |
Null |
None |
|
Fax Number |
FAX_ID |
Input file |
Customer table |
None |
|
Free on Board |
FOB_FLD |
Input file |
Customer table |
None |
|
Gross Unit Cost Flag |
GROSS_UNIT_CST_FL |
Set by application |
N (No) |
None |
|
GSA Flag |
GSA_FL |
Input file |
Order Entry Project Settings table or Order Entry Catalog Settings table; otherwise, N (No) |
None |
|
Inspect By DoDAAC |
INSPECT_BY_ADDR_CD |
Input file |
CUST.INSPECT_BY_ADDR_CD; OE_PROJ_SETTINGS.INSPECT_BY_ADDR_CD corresponding to SO_HDR.PRICE_PROJ_ID or OE_CATLG_SETTINGS.INSPECT_BY_ADDR_CD corresponding to SO_HDR.PRICE_CATLG_CD; otherwise, null |
None |
|
International Shipment |
INTL_SHIP_FL |
Input file |
Y (Yes) if Ship To country is not USA; otherwise, N (No) |
Must be Y (Yes) or N (No) |
|
Invoice Required |
INVC_FL |
Set by application |
Order Entry Project Settings table; Order Entry Catalog Settings table; Order Entry Settings table |
Must be Y (Yes) or N (No) |
|
Invoice Freight Charge |
INVC_FRGHT_CHG_FL |
Set by application |
Order Entry Project Settings table; Order Entry Catalog Settings table |
None |
|
Inventory Abbreviation Code |
INVT_ABBRV_CD |
Input file |
Order Entry Project Settings table; Order Entry Catalog Settings table |
See note 1 |
|
Issued By Address Code |
ISSUE_BY_ADDR_CD |
Input file |
Customer table; Order Entry Project Settings table; Order Entry Catalog Settings table; otherwise, null |
See note 1 Must exist in the Issue By Address table |
|
Liquidation Account |
LIQ_ACCT_ID |
Set by application |
Sales Group Accounts table where Product Transaction Type (S_PROD_TRN_TYPE) is LIQ |
None |
|
Liquidation Organization |
LIQ_ORG_ID |
Set by application |
Sales Group Accounts table where Product Transaction Type (S_PROD_TRN_TYPE) is LIQ |
See note 1 |
|
Liquidation Project |
LIQ_PROJ_ID |
Set by application |
Sales Group Accounts table where Product Transaction Type (S_PROD_TRN_TYPE) is LIQ |
See note 1 |
|
Liquidation Reference 1 |
LIQ_REF1_ID |
Set by application |
Sales Group Accounts table where Product Transaction Type (S_PROD_TRN_TYPE) is LIQ |
See note 1 |
|
Liquidation Reference 2 |
LIQ_REF2_ID |
Set by application |
Sales Group Accounts table where Product Transaction Type (S_PROD_TRN_TYPE) is LIQ |
See note 1 |
|
Local Processing Office DoDAAC |
LOCAL_PROC_ADDR_CD |
Input file |
CUST.LOCAL_PROC_ADDR_CD; OE_PROJ_SETTINGS.LOCAL_PROC_ADDR_CD corresponding to SO_HDR.PRICE_PROJ_ID or OE_CATLG_SETTINGS.LOCAL_PROC_ADDR_CD corresponding to SO_HDR.PRICE_CATLG_CD; otherwise, null |
None |
|
Locked |
LOCK_FL |
Set by application |
N (No) |
None |
|
Mark For Address Code |
MARK_FOR_ADDR_DC |
Input file |
Customer Address table where Mark For Address code is D (Default) or Y (Yes); otherwise, null |
See note 1 Must exist in the Customer Address table |
|
Modified By |
MODIFIED_BY |
Set by application |
AOPSOPP |
None |
|
Approval Procedure Code |
OE_APPR_PROC_CD |
Input file |
Order Entry Settings table; Order Entry Projects Settings table; Order Entry Catalog Settings table |
See note 1 |
|
Order Date |
ORD_DT |
Input file |
None |
Required; must be earlier than or the same as the Due Date |
|
3rd Party/Other Office DoDAAC |
OTHER_OFFC_ADDR_CD |
Input file |
CUST.OTHER_OFFC_ADDR_CD; OE_PROJ_SETTINGS.OTHER_OFFC_ADDR_CD corresponding to SO_HDR.PRICE_PROJ_ID or OE_CATLG_SETTINGS.OTHER_OFFC_ADDR_CD corresponding to SO_HDR.PRICE_CATLG_CD; otherwise, null |
None |
|
Allow Overshipment |
OVRSHP_ALLOW_FL |
Input file |
Customer table |
Must be Y (Yes) or N (No) |
|
Number of Partial Shipments Allowed |
PARTIAL_ALLOW_NO |
Set by application |
1 |
None |
|
Partial Shipments |
PARTIAL_SHIP_FL |
Input file |
Customer table |
Must be Y (Yes) or N (No) |
|
Phone Number |
PHONE_ID |
Input file |
Customer table |
None |
|
Print Pick List |
PICK_LIST_PRNT_FL |
Set by application |
N (No) |
None |
|
Price Catalog Code |
PRICE_CATLG_CD |
Input file |
Pricing Catalog Code from Customer table; otherwise, null |
See note 1 Must exist in Price Catalog table |
|
Project Price Catalog |
PRICE_PROJ_ID |
Input file |
Pricing Projects ID from Customer table; otherwise, null |
See note 1 Must exist in Projects table |
|
Prime Contract |
PRIME_CONTR_ID |
Input file |
From Projects table |
None |
|
Print Packing Slip |
PS_FL |
Set by application |
Order Entry Projects Settings Table; Order Entry Catalog Settings Table; Order Entry Settings Table |
None |
|
Quote ID |
QUOTE_ID |
Input file |
None |
None |
|
Recurring End Date |
RECUR_END_DT |
Input file |
None |
Must be a valid date later than the Start Date |
|
Recurring Start Date |
RECUR_START_DT |
Input file |
None |
Must be a valid date earlier than the Ending Date |
|
Bill Remittance Address Code |
REMIT_ADDR_CD |
Input file |
Order Entry Projects Settings Table; Order Entry Catalog Settings Table; Customer table; null |
See note 1 Must exist in Bill Remit Address table |
|
Retain Sales Order |
RETAIN_SO_FL |
Set by application |
N (No) |
None |
|
Release from Blanket |
RLSE_FROM_BLKT_FL |
Set by application |
Null |
None |
|
Release Line Total |
RLSE_LN_TOT_FL |
Set by application |
Null |
None |
|
Rowversion |
ROWVERSION |
Set by application |
Zero |
None |
|
Revision Number |
RVSN_NO |
Set by application |
Zero |
None |
|
Acceptance Point |
S_ACCEPT_PT_CD |
Input file |
CUST.S_ACCEPT_PT_CD |
Must be S (Source), D (Destination), or O (Other). |
|
Inspection Point |
S_INSPECT_PT_CD |
Input file |
CUST.S_INSPECT_PT_CD |
Must be S (Source), D (Destination), or O (Other). |
|
FOB Point |
S_WAWF_FOB_CD |
Input file |
CUST.S_WAWF_FOB_CD |
Must be S (Source), D (Destination), or O (Other). |
|
Sales Group Abbreviation |
SALES_ABBRV_CD |
Input file |
Order Entry Project Settings table; Order Entry Catalog Settings table; Customer table |
See note 1 Must exist in Sales Abbreviation table |
|
Sales Representative |
SALES_REP_ID |
Input file |
None |
See note 1 Must exist in Sales Representative table |
|
Sales Tax/VAT Amount (Func Currency) |
SALES_TAX_AMT |
Set by application |
Sum of Sales Tax/VAT Amounts for all sales order lines (functional currency) |
None |
|
Shipment Number ID |
SHIPMENT_NO_ID |
Set by application |
Order Entry Project Settings table; Order Entry Catalog Settings table; null |
None |
|
Ship By Date |
SHIP_BY_DT |
Input file |
Due Date |
Must be a valid date less than or equal to due date |
|
Ship From Address Code |
SHIP_FROM_ADDR_CD |
Input file |
Order Entry Project Settings table; Order Entry Catalog Settings table; Customer table |
See note 1 Must exist in the Contractor Address table |
|
Ship ID |
SHIP_ID |
Input file |
Customer Address table |
See note 1 Must exist in the Ship ID and Customer Address tables where the Ship Address code is D (Default) |
|
Ship Via |
SHIP_VIA_FLD |
Input file |
Customer table |
None |
|
Sales Order ID |
SO_ID |
Input file/Set by application |
Next available SO number, if SO ID is blank in the input file and you have enabled the automatic assignment of SO IDs in the Configure Sales Order Entry Settings screen |
See note 1 SO Header record must be associated with at least one valid sales order line record |
|
SO Notes |
SO_NOTES_NT |
Input file |
Null |
None |
|
SO Total Amount |
SO_TOT_AMT |
Set by application |
Sum of Total Amounts for all sales order lines (functional currency) |
None |
|
Substitutions Allowed |
SUBST_FL |
Set by application |
Customer table |
Must be Y (Yes) or N (No) |
|
Revenue Recognition Method |
S_REV_RECOG_CD |
Input file/Set by application |
Order Entry Project Settings table; Order Entry Catalog Settings table; Order Entry Settings table |
Must be D (Deferred Revenue), L (Liquidations), P (Prepay), R (Ready For Use), S (Shipment/Sales), or U (Unbilled A/R) |
|
Sales Order Status |
S_SO_STATUS_CD |
Input file |
A (Approved) if Approval Process Assignment Code (S_APPR_PROC_ASG_CD) is N (No) in Order Entry Settings table; otherwise, P (Pending) |
Must be P (Pending) or A (Approved) |
|
SO Type |
S_SO_TYPE |
Set by application |
S (Sales Order) |
None |
|
Timestamp |
TIME_STAMP |
Set by application |
Current system date and time |
None |
|
Volume Discount Percentage |
VOL_DISC_PCT_RT |
Input file |
Zero |
Value must be between 0 and 1, in five-digit format: 0.0000 |
|
Euro to Functional Currency Rate |
EUR_TO_FUNC_RT |
Set by application |
When transaction currency is an EU country currency (with Convert to Euro Currency flag selected): = MU_CRNCY_STATUS. EURO_TO_CRNCY_RT for EU country functional currencies (with Convert to Euro Currency flag selected); = RT_BY_DT.EXCH_RT (or from RT_BY_PD depending upon setting) for EU country functional currencies (with Convert to Euro Currency flag cleared); = RT_BY_DT.EXCH_RT (or from RT_BY_PD depending upon setting) for non EU country functional currencies; When transaction currency is either non-EU currency or is an EU country currency (with Convert to Euro Currency flag cleared), value = 1; If this is unavailable, 1 |
None |
|
Rate Group ID |
RATE_GRP_ID |
Input file/Set by application |
Default Rate Group from Customer table; Default Rate Group from the Configure Multicurrency Settings screen; null |
Must exist in Rate Group table Transaction to Functional Currency combination must be a member of the SO Header Rate Group in the Rate Group Currency table |
|
Discount Amount (Transaction Currency) |
TRN_DISC_AMT |
Set by application |
Sum of Discount Amounts for all sales order lines (transaction currency) |
None |
|
Transaction Currency Code |
TRN_CRNCY_CD |
Input file/Set by application |
If not in input file (for a new sales order), default General Ledger Functional Currency Code |
See note 1 Must exist in Currencies Used in Costpoint table (CURRENCY) Transaction to Functional Currency combination must be a member of the SO Header Rate Group in the Rate Group Currency table (RT_GRP_CRNCY) Transaction Currency must be included in the list of currencies in the Customer Limit Currency table (CUST_LIMIT_CRNCY), if the customer is limited to certain transaction currencies (CUST.LIMIT_TRN_CRNCY_FL=Y); for example, the Sales Order's transaction currency must be in the Transaction Currencies group box (in the Multicurrency subtask of the Manage Customers screen) if the Limit Transaction Currencies check box is selected; if this check box is not selected, you can use any valid transaction currency |
|
Transaction Currency Date |
TRN_CRNCY_DT |
Input file/Set by application |
Current Date |
None |
|
Transaction Currency Freeze Rate |
TRN_FREEZE_RT_FL |
Set by application |
N (No) if SO Header Status is P (Pending); otherwise, Y (Yes) |
None |
|
SO Total Amount (Transaction Currency) |
TRN_SO_TOT_AMT |
Set by application |
Sum of total amounts for all sales order lines (transaction currency) |
None |
|
Sales Tax/VAT Amount (Transaction Currency) |
TRN_SALES_TAX_AMT |
Set by application |
Sum of Sales Tax/VAT amounts for all sales order lines (transaction currency) |
None |
|
Transaction Currency To Euro Rate |
TRN_TO_EUR_RT |
Set by application |
When transaction currency is an EU country currency (with Convert to Euro Currency flag selected), value = Euro-to-Currency Exchange Rate in the Multicurrency Status table for matching transactional currency code; When transaction currency is either non-EU currency or is an EU country currency (with Convert to Euro Currency flag cleared), value = RT_BY_DT.EXCH_RT (or from RT_BY_PD depending upon setting); If this is unavailable, default is 1 |
None |
|
Transaction Currency to Euro Rate Flag |
TRN_TO_EUR_RT_FL |
Set by application |
Y (Yes) if the transaction currency is a Euro currency, where the row exists in Multicurrency Status table with matching transactional currency code; otherwise, N (No) |
None |
|
Enable SO Invoices for WAWF |
USE_WAWF_FL |
Input file |
CUST.USE_WAWF_FL |
Must be Y (Yes) or N (No) |
|
Value Added Tax ID |
VAT_TAX_ID |
Input file/Set by application |
Customer's VAT Tax ID where the Default is Y (Yes); if this is unavailable, default is null |
See note 1 Must exist in Customer Value Added Tax Information table (CUST_VAT_INFO) for matching Sales Order Customer (SO_HDR.CUST_ID) |
|
Destination Country |
EC_COUNTRY_DEST_CD |
Input file/Set by application |
Null |
Must exist in Customs Country table (EC_COUNTRY) |
|
Permit/License ID |
PERMIT_ID |
Input file/Set by application |
Null |
None |
|
Declaration Point |
DECL_POINT_CD |
Input file/Set by application |
Null |
Must exist in Customs Declaration Point table (CUSTOMS_DECL_ POINT) |
|
Traffic Direction |
TRAFFIC_DIR_CD |
Input file/Set by application |
Null |
Must exist in Customs Traffic Direction table (CUSTMS_TRAFFIC_DIR) |
|
Mode of Transportation |
MODE_OF_TRANSP_NO |
Input file/Set by application |
Null |
Must exist in Customs Mode of Transportation table (EC_MODE_OF_TRANSP) |
|
Intrastat Reference Number |
INTRASTAT_REF_NO |
Input file/Set by application |
Null |
None |
|
Commission |
COMM_PCT_RT |
Input file |
Zero |
Value must be between 0 and 1, in five-digit format: 0.0000 |
|
SO Release Number |
SO_RLSE_NO |
Set by application |
Zero |
None |
|
SO Change Order Number |
SO_CHNG_ORD_NO |
Set by application |
Zero |
None |
|
Blanket Amount (Func Currency) |
BLKT_AMT |
Set by application |
Zero |
None |
|
Blanket Amount (Trans Currency) |
TRN_BLKT_AMT |
Set by application |
Zero |
None |
|
Total Released Amount (Func Currency) |
TOT_RLSED_AMT |
Set by application |
Zero |
None |
|
Total Released Amount (Trans Currency) |
TRN_TOT_RLSED_AMT |
Set by application |
Zero |
None |
|
Performance End Date |
PERF_END_DT |
Set by application |
Null |
None |
|
Performance Start Date |
PERF_START_DT |
Set by application |
Null |
None |
|
Company ID |
COMPANY_ID |
Set by application |
User's company ID |
None |
1 - System validates the field for the user's company ID.
|
Column Name |
Costpoint Column Name |
Source |
Defaults |
Validations/Notes |
|
ACRN (Accounting Classification Reference Number) |
ACRN_ID |
Input file |
Null |
None |
|
Allow Forecast Consumption Changes |
ALL_FRCST_CONS_FL |
Set by application |
N if inventory reservations are generated (or have been generated) for the Sales Order Line; otherwise, Y |
None |
|
Billing Cycle |
BILL_CYCLE_CD |
Input file |
Null |
Required for recurring line types; must exist in the Bill Cycle table |
|
Bills of Material Configuration ID |
BOM_CONFIG_ID |
Input file |
Null |
See note 1 Must exist in Bills of Material Configuration (BOM_CONFIG) table |
|
Certificate of Conformance |
CERT_OF_CNFRM_FL |
Set by application |
Item Product table; otherwise, N |
Must be Y (Yes) or N (No) |
|
Client Line Item Number |
CLIN_ID |
Input file |
Space |
None |
|
Consume Forecasts |
CONSUME_FORECST_FL |
Input file |
If the MPS module is not enabled, or if not an inventory line type, ignore input file and set to N; If the Warn if SO Qty Exceeds Open Forecast Qty check box is cleared in MPS Settings, and there are no matching open forecast quantities for the SO Line, ignore input file and set to N; Otherwise, if input file value is space or null, use the MPS Settings Default SO Line Consume Forecast Flag value. |
Must be Y (Yes), N (No), space, or null |
|
Defer Revenue |
DEFER_REV_FL |
Set by application |
Item Product table; otherwise, N |
Must be Y (Yes) or N (No) |
|
Deliver To |
DEL_TO_FLD |
Input file |
Space |
None |
|
Desired Date |
DESIRED_DT |
Input file |
Due Date |
Must be a valid date later than, or the same as, the Order Date |
|
Discount Amount |
DISC_AMT |
Set by application |
Calculated as SO_LN.GROSS_UNIT_PRC_AMT * SO_LN.DISC_PCT_RT |
None |
|
Discount Percentage |
DISC_PCT_RT |
Input file |
Zero |
Value must be between 0 and 1, in five-digit format: 0.0000 |
|
Due Date |
DUE_DT |
Input file |
None |
Must be a valid date later than, or the same as, the Order Date; required if not in header record |
|
External Customer Item ID |
EXT_CUST_ITEM_ID |
Input file |
None |
None |
|
Forecast Consumed |
FRCST_CONSUMED_FL |
Set by application |
Y if forecast requirement quantities were consumed; otherwise, N |
None |
|
GFE |
GFE_FL |
Input file |
N |
Must be Y (Yes) or N (No) |
|
Gross Unit Price (Func Currency) |
GROSS_UNIT_PRC_AMT |
Set by application |
Gross Unit Price (in transaction currency) * Exchange Rate |
NOTE: You can upload Gross Unit Price records with either positive or negative values. |
|
Incremental Billing |
INCREM_BILL_FL |
Set by application |
N (No) |
None |
|
Installment Billing |
INSTALL_BILL_FL |
Set by application |
Item Product table; otherwise, null |
None |
|
Invoice Amount (Func Currency) |
INVC_AMT |
Set by application |
Invoice Amount (in transaction currency) * Exchange Rate |
None |
|
Invoice Quantity |
INVC_QTY |
Set by application |
Zero |
None |
|
Inventory Abbreviation |
INVT_ABBRV_CD |
Input file |
Order Entry Project Settings table; Order Entry Catalog Settings table; Project CLIN Product table; Product Price Catalog |
See note 2 None |
|
Issue Quantity |
ISSUE_QTY |
Set by application |
Order Quantity |
None |
|
Item ID |
ITEM_ID |
Input file |
None |
See note 1 Required; must exist in Item Product table |
|
Item Key |
ITEM_KEY |
Input file |
None |
Must exist in Item Product table |
|
Item Revision |
ITEM_RVSN_ID |
Input file |
None |
See note 1 Required; must exist in Item Product table |
|
Last Bill Date |
LAST_BILL_DT |
Set by application |
Null |
None |
|
Manufacturing Order |
MANUF_ORD_ID |
Set by application |
Space |
None |
|
Milstrip ID |
MILSTRIP_ID |
Input file |
Part Project table |
None |
|
Miscellaneous Line Charge Type |
MISC_LN_CHG_TYPE |
Input file |
None |
See note 2 Required if line type is MSC (Miscellaneous) |
|
Model |
MODEL_ID |
Input file |
Item Product; otherwise, space |
None |
|
Modified By |
MODIFIED_BY |
Set by application |
AOPSOPP |
None |
|
Manufacturing Order Generated |
MO_GEN_FL |
Set by application |
N (No) |
None |
|
Net Unit Price (Func Currency) |
NET_UNIT_PRC_AMT |
Set by application |
Calculated as Gross Unit Price - Extended Discount Amount (functional currency) |
None |
|
National Stock Number |
NSN_ID |
Input file |
Part table; otherwise, space |
None |
|
Order Quantity |
ORD_QTY |
Input file |
None |
Required; cannot be less than existing issued quantity |
|
Original Due Date |
ORIG_DUE_DT |
Set by application |
Due Date |
Must be a valid date later than, or the same as, the Order Date |
|
Allow Overshipment |
OVRSHP_ALLOW_FL |
Input file |
Customer table; otherwise, N |
Must be Y (Yes) or N (No) |
|
Invoice Amount Posted to General Ledger (Func Currency) |
PSTD_AMT |
Set by application |
Invoice Amount Posted to General Ledger (in functional currency) * Exchange Rate |
None |
|
Quote ID |
QUOTE_ID |
Input file |
Null |
None |
|
Recurring End Date |
RECUR_END_DT |
Input file |
None |
Must be a valid date later than the Start Date |
|
Recurring Start Date |
RECUR_START_DT |
Input file |
None |
Must be a valid date earlier than the End Date |
|
Reservation |
RES_ID |
Set by application |
Null |
None |
|
Rowversion |
ROWVERSION |
Set by application |
Zero |
None |
|
Requisition Generated |
RQ_GEN_FL |
Set by application |
N (No) |
None |
|
Requisition |
RQ_ID |
Set by application |
Space |
None |
|
Product Service Type |
S_PROD_SVC_TYPE_CD |
Input file |
Null |
Product Service Type value must be B8, CL, F8, FS, FT, MG, MN, SN, SV, or VP |
|
Sales Group Abbreviation |
SALES_ABBRV_CD |
Input file |
Sales Order Header; Order Entry Projects Settings table; Customer table |
See note 2 Must exist in Sales Abbreviation table |
|
Sales Tax/VAT Amount (Func Currency) |
SALES_TAX_AMT |
Set by application |
If Taxable (TAXABLE_FL = Y), Sales Tax/VAT Amount is calculated as SO Line Extended Price Amount (in functional currency) * Sales Tax/VAT Rate; otherwise, Sales Tax/VAT Amount is zero |
None |
|
Sales Tax/VAT Code |
SALES_TAX_CD |
Input file |
Customer Address table |
Must exist in Sales Tax/VAT table; if the value is not provided, no Sales Tax/VAT amount is calculated for the line |
|
Sales Tax/VAT Rate |
SALES_TAX_RT |
Set by application |
Rate from Sales Tax/VAT table associated with Sales Tax/VAT Code; otherwise, zero |
None |
|
SDN |
SDN_ID |
Input file |
Null |
None |
|
Ship by Date |
SHIP_BY_DT |
Input file |
Due Date |
Must be a valid date earlier than the Due Date |
|
Ship ID |
SHIP_ID |
Input file |
Sales Order Header; Customer Address table |
See note 2 Must exist in the Ship ID and Customer Address tables with the Ship To (S_SHIP_ADDR_CD) set as D (Default) or Y (Yes) for the row |
|
Ship Quantity |
SHIP_QTY |
Set by application |
Zero |
None |
|
Shipping Tolerance Percentage |
SHIP_TOL_PCT_RT |
Set by application |
Item Product table; otherwise, zero |
None |
|
Ship Via |
SHIP_VIA_FLD
|
Input file |
Customer table |
Must exist in Customer Address table |
|
Sales Order ID |
SO_ID |
Input file/Set by application |
Sales Order Header; Order Entry Settings |
See note 2 Sales Order ID must exist and be valid in the Sales Order Header table; SO ID defaults based on SO Header Record Format |
|
SO Line Charge Tax Amount (Func Currency) |
SO_LN_CHG_TAX_AMT |
Set by application |
Zero |
None |
|
SO Line Description |
SO_LN_DESC |
Input file |
None |
|
|
SO Line Extended Price Amount (Func Currency) |
SO_LN_EXT_AMT |
Input file |
Order Quantity * Net Unit Price (functional currency) |
None |
|
SO Line Key |
SO_LN_KEY |
Input file/Set by application |
None |
None |
|
SO Line Number |
SO_LN_NO |
Input file |
None |
Required |
|
SO Line Total Amount (Func Currency) |
SO_LN_TOT_AMT |
Set by application |
SO Line Extended Price Amount + SO Line Sales Tax/VAT Amount (functional currency) |
None |
|
Source Inspection |
SRCE_INSP_FL |
Set by application |
Item Product |
None |
|
Substitutions Allowed |
SUBST_FL |
Set by application |
Customer table |
None |
|
SO Line Status |
S_LN_STATUS_TYPE |
Set by application |
O (Open) |
None |
|
Make/Buy |
S_MAKE_BUY_CD |
Set by application |
Part table |
None |
|
SO Line Type |
S_SO_LN_TYPE |
Input file |
Project CLIN Product table; Product Price Catalog table; Item Product table; otherwise, MSC (Miscellaneous) |
Must exist in SO Line Type table |
|
Taxable |
TAXABLE_FL |
Input file |
None |
Must be Y (Yes) or N (No) |
|
Timestamp |
TIME_STAMP |
Set by application |
Current system date and time |
None |
|
UID Required |
UID_REQD_FL |
Input file |
N |
Must be Y (Yes) or N (No) |
|
Unit of Measure (U/M) |
UM_CD |
Input file |
Item UM table; otherwise, null |
Must exist in Item UM table |
|
User Revision |
USER_RVSN_FLD |
Input file |
Null |
Must exist in Item Product table |
|
Warehouse |
WHSE_ID |
Input file |
Inventory Projects table |
See note 2 Must exist in WHSE table |
|
Project To Charge |
CHG_PROJ_ID |
Input file |
Sales Group Accounts table; Customer table |
See note 2 Must exist in the Projects table |
|
Sales Representative ID |
SALES_REP_ID |
Input file |
Null |
See note 2 Must exist in the Sales Representative table |
|
Discount Amount (Transaction Currency) |
TRN_DISC_AMT |
Set by application |
Gross Unit Price (in transaction currency) * Discount Percent Rate for the sales order line |
None |
|
Gross Unit Price (Transaction Currency) |
TRN_GR_UNIT_PR_AMT |
Input file |
Product Price Search table; Contract Line Item Number Search table; Customer table |
Must be numeric and equal to or greater than zero; Gross Unit Price (TRN_GR_UNIT_PR_AMT) cannot be less than Net Unit Price (TRN_NT_UNIT_PR_AMT) |
|
Invoice Amount (Transaction Currency) |
TRN_INVC_AMT |
Set by application |
Zero |
None |
|
Net Unit Price (Transaction Currency) |
TRN_NT_UNIT_PR_AMT |
Set by application |
Gross Unit Price - Discount Amount for the sales order line (in transaction currency) |
None |
|
SO Line Extended Price Amount (Transaction Currency) |
TRN_SO_LN_EXT_AMT |
Input file/ Set by application |
Order Quantity * Net Unit Price for the sales order line (in transaction currency) |
Validation was removed to allow users to upload records with negative Extended PriceAmount; Extended Price (TRN_SO_LN_EXT_AMT) cannot be less than Net Unit Price (TRN_NT_UNIT_ PR_AMT). NOTE: This validation only applies if SO Line Type = MISC/Service and Order Qty = '0'. |
|
SO Line Total Amount (Transaction Currency) |
TRN_SO_LN_TOT_AMT |
Set by application |
Extended Price Amount + Sales Tax/ VAT amount for the sales order line (in transaction currency) |
None |
|
Sales Tax/VAT Amount (Transaction Currency) |
TRN_SALES_TAX_AMT |
Set by application |
If sales order line is taxable, calculate as SO line Extended Price Amount (in transaction currency) * SO line Sales Tax/VAT Rate; otherwise, zero |
None |
|
Invoice Amount Posted to General Ledger (Transaction Currency) |
TRN_PSTD_AMT |
Set by application |
Zero |
None |
|
Mass Quantity |
MASS_QTY |
Input file/ Set by application |
Null |
None |
|
Mass Unit of Measure |
MASS_UM_CD |
Input file/ Set by application |
Null |
None |
|
Nature of Transaction Code |
NOTC_NO |
Input file/ Set by application |
Null |
Must exist in the Nature of Transaction Code table (EC_NOTC) |
|
Correction Code |
CORRECTION_CD |
Input file/ Set by application |
Null |
See note 2 Must exist in the Customs Correction table (EC_CORRECTION) |
|
Commodity Code |
COMM_CD |
Input file/ Set by application |
Null |
See note 2 Must exist in the Customs Commodity table (EC_COMM) |
|
Supplementary Units |
SUPPL_UNITS_NO |
Input file/ Set by application |
Null |
None |
|
Country of Origin |
EC_COUNTRY_ORIG_CD |
Input file/ Set by application |
Null |
Must exist in the Customs Country table (EC_COUNTRY) |
|
Process Components |
COMP_PROC_FL |
Set by application |
N (No) |
None |
|
Issue Components |
ISSUE_COMP_FL |
Set by application |
N (No) |
None |
|
Invoice Components |
COMP_INVC_FL |
Set by application |
N (No) |
None |
|
Commission |
COMM_PCT_RT |
Input file |
SO_HDR.COMM_PCT_RT |
Value must be between 0 and 1, in five-digit format: 0.0000 |
|
License Type |
LICENSE_TYPE_ID |
Input file |
Null |
None |
|
Platform Type |
PLATFORM_TYPE_ID |
Input file |
Null |
None |
|
Number of Users |
USERS_NO |
Input file |
Null |
Value cannot be negative, or greater than 32,767 |
|
Version |
VERSION_ID |
Input file |
Null |
None |
|
Amortization Code |
AMORT_CD |
Input file |
Null |
Revenue Recognition Method (SO_HDR.S_REV_RECOG_CD) must be D (Deferred revenue) Must exist in Amortization Schedule table and must be active (AMORT_SCH.ACTIVE_FL=Y) Must have the same company ID as the user Sales Abbreviation Code must include accounts for Deferred Revenue and Sales, if the SO line has an Amortization Code |
|
PO Ship ID |
PO_SHIP_ID |
Input file |
Default from Warehouse Ship ID (WHSE.SHIP_ID) if line type is INV (Inventory) or INT (Intransit) Default from SO Line's Ship ID (SO_LN.SHIP_ID) if SO Line's warehouse ID or warehouse ship ID (WHSE.SHIP_ID) is null Default from SO Line's Ship ID if line type is DRP (Dropshipment) |
Must exist in SHIP_ID table Company ID must be same as user's |
|
Posted Quantity |
PSTD_QTY |
Set by application |
Zero |
None |
|
SO Release |
SO_RLSE_NO |
Set by application |
Zero |
None |
|
SO Line Charge Amt (Func Currency) |
SO_LN_CHG_AMT |
Set by application |
Zero |
None |
|
SO Line Charge Amt (Trans Currency) |
TRN_SO_LN_CHG_AMT |
Set by application |
Zero |
None |
|
SO Line Charge Tax Amt (Trans Currency) |
TRN_SO_LN_CHG_TAX |
Set by application |
Zero |
None |
|
Planner |
PLANNER_ID |
Set by application |
Null |
None |
|
Effective Date |
EFF_DT |
Set by application |
Null |
None |
|
European Community Country Ship to Code |
EC_COUNTRY_SHIP_CD |
Set by application |
Null |
None |
|
Customs Type Code |
CUSTOMS_TYPE_CD |
Set by application |
Null |
None |
|
Value Amount |
VALUE_AMT |
Set by application |
Null |
None |
|
Base Unit Price Amount |
BASE_UNIT_PRC_AMT |
Set by application |
Null |
None |
1 – If you select the Separate Items by Company check box in the Corporate Settings group box of the Configure Product Definition Settings screen, the system validates this field for the user's company ID
2 – System validates the field for the user's company ID
|
Column Name |
Costpoint Column Name |
Source |
Defaults |
Validations/Notes |
|
Sales Order ID |
SO_ID |
Input file |
None |
See Note 1 Not required if in the SO Header or SO Line input file; must exist in the Sales Order Header table |
|
Sales Order Line Number |
SO_LN_NO |
Input file |
None |
Must exist in Sales Order Line |
|
Sales Order Line Text |
SO_LN_TX |
Input file |
None |
See note 1 Required |
|
Modified By |
MODIFIED_BY |
Set by application |
AOPSOPP |
None |
|
Rowversion |
ROWVERSION |
Set by application |
Zero |
None |
|
Timestamp |
TIME_STAMP |
Set by application |
Current system date and time |
None |
|
SO Release |
SO_RLSE_NO |
Set by application |
Zero |
None |
1 - System validates the field for the user's company ID
|
Column Name |
Costpoint Column Name |
Source |
Defaults |
Validations/Notes |
|
Sales Order ID |
SO_ID |
Input file |
None |
Not required if in SO Header or SO Line input file; must exist in the Sales Order Line table |
|
Sales Order Line Number |
SO_LN_NO |
Input file |
None |
Required; must exist in Sales Order Line |
|
Text Code |
TEXT_CD |
Input file |
None |
See note 1 Required |
|
Sequence Number |
SEQ_NO |
Input file |
None |
Required |
|
Text Source Code |
S_TEXT_SRCE_CD |
Input file |
None |
Required; must be either I (Item) or P (Project) |
|
Modified By |
MODIFIED_BY |
Set by application |
AOPSOPP |
None |
|
Rowversion |
ROWVERSION |
Set by application |
Zero |
None |
|
Timestamp |
TIME_STAMP |
Set by application |
Current system date and time |
None |
|
SO Release |
SO_RLSE_NO |
Set by application |
Zero |
None |
1 - System validates the field for the user's company ID
|
Column Name |
Costpoint Column Name |
Source |
Defaults |
Validations/Notes |
|
Sales Order ID |
SO_ID |
Input file |
None |
See note 1 Not required if in SO Header input file; must exist in the Sales Order Header table |
|
Text Code |
TEXT_CD |
Input file |
For new sales orders, default standard text codes that have been set up for use in sales orders, and have auto-default of Y; this does not override matching text code in the input file. Otherwise, none. |
See note 1 Required |
|
Sequence Number |
SEQ_NO |
Input file |
For new sales orders, default the auto-loaded sequence number associated with the standard text codes; this does not override matching sequence number in the input file. Otherwise, none. |
Required |
|
Modified By |
MODIFIED_BY |
Set by application |
AOPSOPP |
None |
|
Rowversion |
ROWVERSION |
Set by application |
Zero |
None |
|
Timestamp |
TIME_STAMP |
Set by application |
Current system date and time |
None |
|
SO Release |
SO_RLSE_NO |
Set by application |
Zero |
None |
1 - System validates the field for the user's company ID