drop-down list, the following actions take place:When you select Process Manual Bill Input File from the drop-down list, the following actions take place:
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.
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.
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.
When you select Import Manual Bills from the drop-down list, the following actions take place:
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.
Costpoint populates the MANUAL_BILL_EDIT production table with the Z_BLPUMB_EDIT table for the records from the company being imported.
Worktables are truncated from the database for the company being imported.
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)
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. Some fields in the input file can be empty but none should have a value of NULL. The following table shows the input file layout the application expects.
|
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. |
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 |