Processing Details

Use the information in this section to know more about the processing details when importing manual bills.

Processing Details

When you select Process Manual Bill Input File from the drop-down list, the following actions take place:

  1. Costpoint checks the input CSV file. If this file does not exist or is formatted incorrectly, you will not be able to upload the worktables.
  2. Costpoint reads each record in the input file. Any record with missing values in the mandatory fields or with invalid values will be flagged as an error. Validations are performed for the following columns:

    PROJ_ABBRV_CD: If this exists and PROJ_ID is blank in the input table, PROJ_ABBRV_CD must exist in the PROJ table.

    PROJ_ID: If this exists and PROJ_ABBRV_CD is blank in the input table, PROJ_ID must exist in the PROJ and PROJ_BILL_INFO tables.

    PROJ_ID/PROJ_ABBRV_CD: If both exist in the input table, then both must exist in the PROJ table for the same record.

    CUST_ID: This must exist in the CUST table.

    CUST_ID/ADDR_DC: If both exist in the input table, then both must exist in the CUST_ADDR table for the same record.

    SALES_TAX_CD: If this exists in the input table, then it must exist in the SALES_TAX table.

    OTH_CHG_CD1/OTH_CHG_CD2/OTH_CHG_CD3: If these exist in the input table, then they must exist in the BILL_OTH_CHGS table.

    FY_CD/PD_NO/SUB_PD_NO: These must exist in the SUB_PD table and must be open for editing. If information for these fields is not provided in the input file, then Costpoint uses the defaults from the screen and performs the same validation.

    USER_ID: If this exists in the input table, then it must exist in the USER_ID table.

    INVC_ID: This must not already exist in the system unless the Allow duplicate invoice IDs check box is selected on the Import Manual Bills screen.  

    INVC_ID/FY_CD/PD_NO/SUB_PD_NO: These are the keys to the MANUAL_BILL_EDIT table. These must not already exist in the MANUAL_BILL_EDIT table or in the MANUAL_BILL_HS table.

  3. Error information is inserted into the Z_BLPUMB_ERRORS table. If there are no errors, then the Z_BLPUMB_EDIT table will be populated with the Z_BLPUMB_INPUT table.

Input File Excel Template

Go to the Developer Resources page of the Costpoint Information Center to download the Excel template file specifically designed for this preprocessor. Templates for other Costpoint preprocessors are also available at the same location.

Import Details

When you select Import Manual Bills from the drop-down list, the following actions take place:

  1. If there are errors loaded into the Z_BLPUMB_ERRORS table or if there are no rows in the Z_BLPUMB_EDIT table, then Costpoint displays a message stating that errors exist or there are no records to process. If there are no errors, then Costpoint assigns a unique numeric value to the MANUAL_BILL_SRL column and updates each row in the Z_BLPUMB_EDIT table that belongs to the company of the logged-in user.
  2. Costpoint populates the MANUAL_BILL_EDIT production table with the Z_BLPUMB_EDIT table for the records from the company being imported.
  3. Worktables are truncated from the database for the company being imported.

Input Costpoint Database Tables

Input tables are used for validation and/or obtaining current information from Costpoint. This application reads the following Costpoint tables:

  • Customer (CUST)
  • Customer Address (CUST_ADDR)
  • Manual Bill Edit (MANUAL_BILL_EDIT)
  • Other Charges (BILL_OTH_CHGS)
  • Project (PROJ)
  • Sales Tax (SALES_TAX)
  • Subperiod (SUB_PD)
  • User ID (USER_ID)
  • Company (GL_CONFIG, COMPANY_ID)
  • Billing Remittance Addresses (BILL_RMT_ADDR_CD)

Input File Layout

The input file must be a standard CSV file. Each record that will be uploaded must be on a separate line in the input file, separated with a combination of carriage return and line feed. The following table shows the input file layout the application expects.

Note: The input file layout may contain fields that are available only in Costpoint Advanced. If you are using Costpoint Essentials, these fields are excluded from the import process and may be left blank or null when you create the input file.
Column Name Data Type (Length) Required? Notes
BILL_NO_ID VARCHAR (15) N If this is already used for the project, then the system displays a warning but does not prevent upload.
CUST_ID VARCHAR (12) N If blank, the system retrieves it from the PROJ table using the Project ID.
ADDR_DC VARCHAR (10) N If blank, the system retrieves it from the CUST_ADDR table.
CUST_PO_ID VARCHAR (20) N
DISC_AVAIL_AMT DECIMAL (14,2) N
DUE_DT DATE N The system uses the information from the input file if provided; otherwise, it uses the screen defaults. This uses the format MM/DD/YYYY.
ENTR_DTT DATE N If blank, today’s date will be inserted. This uses the format MM/DD/YYYY.
ENTR_USER_ID VARCHAR (12) N If blank, USER_ID will be inserted.
FY_CD VARCHAR (6) N This is the fiscal year for which the bill will be applied. If blank, the system uses the default value from the screen.
PD_NO SMALLINT N This is the period for which the bill will be applied. If blank, the system uses the default value from the screen.
SUB_PD SMALLINT N This is the subperiod for which the bill will be applied. If blank, the system uses the default value from the screen.
INVC_DT DATE N This is the invoice date and uses the format MM/DD/YYYY. If blank, the system uses the default value from the screen, which is today's date.
INVC_ID VARCHAR (15) Y/N See the Invoice Numbering Method section in Import Manual Bills.
PROJ_ID VARCHAR (30) Y/N This is required if PROJ_ABBRV_CD is not provided.
PROJ_ABBRV_CD VARCHAR (6) N/Y This is required if PROJ_ID is not provided.
SALES_TAX_AMT DECIMAL (14,2) N If present, SALES_TAX_CD must also be present.
SALES_TAX_CD VARCHAR (6) N If present, this must be in the SALES_TAX table.
OTH_CHG_AMT1 DECIMAL (14,2) N If present, OTH_CHG_CD1 must also be present.
OTH_CHG_CD1 VARCHAR (6) N If present, this must be in the BILL_OTH_CHGS table.
OTH_CHG_AMT2 DECIMAL (14,2) N If present, OTH_CHG_CD2 must also be present.
OTH_CHG_CD2 VARCHAR (6) N If present, this must be in the BILL_OTH_CHGS table.
OTH_CHG_AMT3 DECIMAL (14,2) N If present, OTH_CHG_CD3 must also be present.
OTH_CHG_CD3 VARCHAR (6) N If present, this must be in the BILL_OTH_CHGS table.
TOT_INVC_AMT DECIMAL (14,2) Y
BILL_RMT_ADDR_CD VARCHAR (6) N If present, this must be in the BILL_REMIT_ADDR table. If blank, leave as NULL.
NOTES VARCHAR (254) N If blank, leave as NULL.
DOC_LOCATION VARCHAR (254) N If blank, leave as NULL.

Output File Layout

The following table shows how the application populates every column of the MANUAL_BILL_EDIT table.

Column Name Data Source or Value
ADDR_DC Input File or default from ADDR_DC table (if blank)
BILL_NO_ID Input File or null (if blank)
BILL_RMT_ADDR_CD Input File or null (if blank)
CUST_ID Input File or from PROJ table (if blank)
CUST_PO_ID Input File or null (if blank)
DISC_AVAIL_AMT Input File or 0 (if blank)
DUE_DT Input File or default (if provided and Input File is blank)
ENTR_DTT Input File or System Date (if blank)
ENTR_USER_ID Input File or Database connection ID for user running the application (if blank)
FY_CD Input File or default (if blank)
INVC_DT Input File or default (if blank) or system date
INVC_ID Input File or other. See description of invoice number.
MANUAL_BILL_SRL Unique numeric value generated by the system
MODIFIED_BY "BLPUMB"
OTH_CHG_AMT1 Input File
OTH_CHG_AMT2 Input File
OTH_CHG_AMT3 Input File
OTH_CHG_CD1 Input File
OTH_CHG_CD2 Input File
OTH_CHG_CD3 Input File
PD_NO Input File or default
POST_SEQ_NO (null)
PROJ_ABBRV_CD Input File or space (if blank)
PROJ_ID Input File or PROJ_ID from the PROJ table, where PROJ_ABBRV_CD = PROJ_ABBRV_CD input file field
ROWVERSION 0
SALES_TAX_AMT Input File else 0
SALES_TAX_CD Input File else null
SUB_PD_NO Input File or default
S_JNL_CD "BJ"
TIME_STAMP Current system date/time value
TOT_INVC_AMT Input File
COMPANY_ID Logged-in company ID
NOTES Input File or blank
DOC_LOCATION Input File or blank