CSV Input File Format
The Import Cash Receipts screen allows you to import cash receipts from a comma-separated values (CSV) file.
This help topic contains information on how to prepare a CSV input file that you can use import cash receipts Into Costpoint.
CSV File
A CSV file is a delimited text file that uses a commas to separate values. Each line of the file is a data record.
Each cash receipt must be in a separate line in the input file. There are separate formats for the cash receipt header and detail line records. Header records are identified by the H record type and will be imported to the CASH_RECPT_HEADER table, while detail line records are identified by the L record type and will be imported to the CASH_RECPT_TRN table.
When starting a line in the input file, you must first enter the record type as the first field to identify the record as a cash receipt header or detail line. Costpoint then imports that record to the appropriate table.
CSV Input File Format
Cash Receipt Header
Use this input file layout to enter a cash receipt header record (record type 'H'). The following table lists the entries contained in each header record of the input file.
Column | Costpoint Field | Type | Length | Required or Optional | Format/Notes |
|---|---|---|---|---|---|
Line Type | N/A | Character | 1 | Required | H for header |
Receipt Date | RECPT_DT | Date | 10 | Required | MM/DD/YYYY |
Receipt ID | CASH_RECPT_NO | Number | 10 | Required | Enter a value to correctly link a header to its detail lines. If you selected the Use Last System Cash Receipt Number or Supply Beginning Cash Receipt Number option, the Receipt ID you entered in the input file will be replaced with the system-generated number after the import process. |
Bank Deposit No. | BANK_DEPOSIT_NO | Alphanumeric | 15 | Optional | |
Fiscal Year | FY_CD | Alphanumeric | 6 | Optional | If null, value from screen is applied |
Period | PD_NO | Number | 5 | Optional | If null, value from screen is applied |
Subperiod | SUB_PD_NO | Number | 5 | Optional | If null, value from screen is applied |
Pay Currency Code | PAY_CRNCY_CD | Alphanumeric | 3 | Optional | Enter value if Multicurrency is activated |
Rate Group ID | RATE_GRP_ID | Alphanumeric | 6 | Optional | Enter value if Multicurrency is activated |
Currency Date | PAY_CRNCY_DT | Date | 10 | Optional | Enter value if Multicurrency is activated |
Cash Receipt Detail Lines
Use this input file layout to enter a cash receipt detail line (record type 'L'). The following table lists the entries contained in each record of the input file.
Column | Costpoint Field | Type | Length | Required or Optional | Format/Notes |
|---|---|---|---|---|---|
Line Type | N/A | Character | 1 | Required | L for line |
Line Source Code | S_LINE_SOURCE_CD | Character | 1 | Required | I for invoice M for miscellaneous T for transfer |
Receipt ID | CASH_RECPT_NO | Number | 10 | Required | Enter a value to correctly link a detail line to a cash receipt header. If you selected the Use Last System Cash Receipt Number or Supply Beginning Cash Receipt Number option, the Receipt ID you entered in the input file will be replaced with the system-generated number after the import process. |
Line No. | LN_NO | Number | 5 | Required | |
Invoice ID | INVC_ID | Alphanumeric | 15 | Optional | If null, UNK invoice is created |
Customer ID | CUST_ID | Alphanumeric | 12 | Optional | |
Check ID | CR_CHECK_ID | Alphanumeric | 40 | Optional | |
Transaction Amount | TRN_AMT | Decimal | 17, 2 | Required | |
Transaction Description | TRN_DESC | Alphanumeric | 30 | Required | |
Discount Taken Amount | DISC_TAKEN_AMT | Decimal | 17, 2 | Required | |
Cash Account Description | CASH_ACCT_KEY | Optional | If null, default is provided | ||
Bank Abbreviation | BANK_ACCT_ABBRV | Alphanumeric | 6 | Optional | |
Account ID | ACCT_ID | Alphanumeric | 15 | Required | |
Org ID | ORG_ID | Alphanumeric | 20 | Required | |
Project ID | PROJ_ID | Alphanumeric | 30 | Optional | |
Reference 1 | REF1_ID | Alphanumeric | 20 | Optional | |
Reference 2 | REF2_ID | Alphanumeric | 20 | Optional | |
Project Account Abbreviation | PROJ_ACCT_ABBRV_CD | Optional | |||
Finance Charge | FINCHG_RCV_AMT | Decimal | 17, 2 | Optional | |
Expense Report ID | EXP_RPT_ID | Optional | |||
Bank Account Amount | BANK_AMT | Decimal | 17, 2 | Optional | |
Line Notes | NOTES | Alphanumeric | 250 | Optional | |
Cash Receipt Type | CASH_RECEIPT_TYPE | Alphanumeric | 15 | Optional | If blank, the Default Cash Receipt Type will be assigned. |
Cash Line Description | CASH_ACCT_TRN_DESC | Alphanumeric | 30 | Optional | If blank, the value will default to the cash account description. |