This application provides a means of transferring Vendor data between Deltek’s Costpoint product and another software product. It is desirable to maintain a single data entry point for this data to reduce errors and double entry of data. The transfer of data will be accomplished through the use of ASCII format files which will be created by one system and imported by the other. Costpoint is a Windows application and will generate (and expect to receive) records that are terminated with a carriage return-line feed combination. UNIX applications may expect (and generate) records with only a carriage return as a terminator. File transfer protocols should be able to handle any translation required, but this is an issue to be considered.
Vendor information may be maintained another system. A subset of the data will be transferred to Costpoint so that purchase order and accounts payable data may be maintained in Costpoint.
This is a custom application for release 5.1 of the Costpoint system.
IMPORTANT: Custom work must be upgraded to be compatible with each new release of Costpoint. DO NOT install future releases of the Costpoint system without first contacting the Custom Solutions group for directions. This should be done sufficiently in advance so that your installation schedule is not affected. Deltek will only perform such work on request and, depending on the scope of the upgrade and current work backlogs, the modifications may not be available for 60 to 90 days. If you do not have an Ongoing Support Plan specifically for your custom work, there will be a charge for the upgrade programming.
AOPUTLVU.EXE - Vendor Preprocessor executable
The user will receive the following custom files:
AOPUTLVU.EXE - Vendor Preprocessor executable
AOUTLVU1.QRP - Vendor Preprocessor report template
AOUTLVU 2.QRP - Vendor Preprocessor report template
This application utilizes the following custom tables:
Z_AOPUTLVU_ERROR - Work Table
Z_AOPUTLVU_TEMP - Work Table
Z_AOPUTLVU_WORK - Work Table
Z_AOPUTLVU_WORK2 - Work Table
The input file used by this application must
be a standard ASCII text file without null characters. Each record
to be included must be on a separate line in the input file, separated
with a combination of carriage return and line feed characters (ASCII
decimal characters 13 and 10). This table displays the input file
layout expected by the application.
|
Field |
Size |
Format Notes |
|
Vendor ID |
Character 12 |
Required |
|
Vendor Name |
Character 25 |
Required |
|
Vendor Terms |
Character 15 |
Required |
|
Location |
Character 6 |
Optional |
|
1099 Tax ID |
Character 20 |
Optional |
|
1099 Type Code |
Character 6 |
Optional |
|
Order Address Line 1 |
Character 30 |
Optional |
|
Order Address Line 2 |
Character 30 |
Optional |
|
Order Address Line 3 |
Character 30 |
Optional |
|
Order City |
Character 25 |
Optional |
|
Order State |
Character 15 |
Optional |
|
Order Country Code |
Character 8 |
Optional |
|
Order Postal Code |
Character 10 |
Optional |
|
Payment Address Line 1 |
Character 30 |
Optional |
|
Payment Address Line 2 |
Character 30 |
Optional |
|
Payment Address Line 3 |
Character 30 |
Optional |
|
Payment City |
Character 25 |
Optional |
|
Payment State |
Character 15 |
Optional |
|
Payment Country Code |
Character 8 |
Optional |
|
Payment Postal Code |
Character 10 |
Optional |
|
Vendor Class |
Character 1 |
Optional |
|
Employee ID |
Character 12 |
Optional |
Input File
Click the Select button to choose the ASCII text file to be used in the import process, or enter the filename. This is a required field.
A/P Account Description
Enter the A/P Account Description to be assigned to all records in the input file. Use the Lookup button to see a list of available A/P Account Descriptions. This is a required field.
Cash Account Description
Enter the Cash Account Description to be assigned to all records in the input file. Use the Lookup button to see a list of available Cash Account Descriptions. This is a required field.
Hold Payments Flag
Check this box if Hold Payments Flag should be set to yes for all records in the input file.
Pay When Paid Flag
Check this box if Pay When Paid Flag should be set to yes for all records in the input file.
Allow Edits to Pay Vendor on Voucher
Check this box if Allow Edits to Pay Vendor on Voucher Flag should be set to yes for all records in the input file.
Vendor Status for PO
Select the value from the list to be applied to all records in the input file. The choices are OK, Warning and Hold POs. This is a required field.
1099 Type
Select the 1099 type, if any, to be applied to any records in the input file that do not have a 1099 type. This field is optional.
Print Audit Report
Check this box if the Audit Report should be printed. If it is not checked, only the Error Report will be printed.
Print Setup
This menu option allows you to set up the printer options for printing the reports. Select this menu option to see the various printer options or to change the output destination of the reports.
Press this button on the tool bar or use the menu option, to read the input file, create the temporary tables, validate the records, write invalid records to an error output file, and print the Error and Audit Reports.
Process
Press this button on the tool bar or use the menu option, to perform all of the actions that the Print button executes, and also perform the actual data upload into the Costpoint database tables.
Costpoint Input Tables
Vendor (VEND)
Vendor Address (VEND_ADDR)
1099 Box Number (S_AP_1099_TYPE)
Default Accounts Payable Accounts (DFLT_AP_ACCTS)
Default Cash Accounts (DFLT_CASH_ACCTS)
Mail State (MAIL_STATE)
Vendor Terms (VEND_TERMS)
Employee (EMPL)
This application allows Costpoint users to import vendor information from an ASCII file produced from PRO-III system. The generated file will be copied to the system where Costpoint is running.
This application may be run at any time, but may be run by only one user at a time.
The following actions take place when the user clicks the Process button. The same actions will be performed if the user clicks the Print button with the exception of updating Costpoint master tables.
The work tables required for processing are cleared.
The application opens the input file specified by the user in text mode, read-only access. (See section 4.1 for the input file layout.) The application reads and inserts one record at a time from the input file into a work table until the entire input file has been processed.
The application verifies all input file information and writes errors to the Error Report. The Audit Report is also created (if the Print Audit Report checkbox is checked).
If the user clicked on the Process button, the Vendor and Vendor Address tables will be updated to incorporate the information in the input file. The remainder of this section details how these tables will be affected.
The reports are printed and the processing is complete. Refer to Section 4.7 for the report layouts.
The Vendor table is keyed on the Vendor (VEND_ID) column. If an input file record contains a Vendor that does not exist in the Vendor table, that record is added to the Vendor table. Otherwise, the existing information for the vendors is updated with the data contained in the input record. The individual columns in the Vendor table are added or updated as follows:
Vendor (VEND_ID)
Vendor is taken from the input record.
The validations for Vendor are as follows:
Vendor is a required field and must exist in the input record.
Vendor must be unique in the input record.
Name (VEND_NAME)
Name is taken from the input record.
Name is a required field and must exist in the input record.
Long Name (VEND_LONG_NAME)
Long Name is defaulted to the Name value.
Long Name is a required field.
Location (VEND_NAME_EXT)
Location is taken from the input record.
If no Location is provided the field is set to a space by the application.
Company ID (COMPANY_ID)
Company ID is set to the Logged-In User’s Company ID.
Terms (TERMS_DC)
Terms are taken from the input record.
The validations for Terms are as follows:
Terms is a required field and must exist in the input record.
A value for Terms must exist in the Vendor Terms table.
1099 Tax ID (AP_1099_TAX_ID)
1099 Tax ID is taken from the input record.
1099 Type (S_AP_1099_TYPE_CD)
The 1099 Type is taken from the input record.
A default value for 1099 Type may be selected at runtime.
The validations for 1099 Type are as follows:
The 1099 Type must exist in the 1099 Box Number table. Currently, the following 1099 Types are supported: INT, DIV, R, OID, G, B, A, PATR, S, RENT, ROYAL, OTHER, MED, AND NONEMP.
A 1099 Tax ID must be provided for the 1099 Type to be included in the Vendor record.
Vendor Status for PO (S_VEND_PO_CNTL_CD)
A default value for Vendor Status for PO will be selected at runtime.
The valid values for the Vendor Status for PO are as follows:
OK
Warning
Hold POs
FOB (FOB_FLD)
FOB is set to a space by the application
Ship Via (SHIP_VIA_FLD)
Ship Via is set to a space by the application.
Hold Payments (HOLD_PMT_FL)
A default value for Hold Payments will be selected at runtime.
The valid values for Hold Payments are as follows:
“Y” (Yes)
“N” (No)
Small Business (S_CL_SM_BUS_CD)
Small Business is set to “S” (to indicate a Small Business designation) if the “Vendor Class” in the input record is 1. Otherwise, it is set to “L” (to indicate a Large Business designation). “L” is used as a default since the field cannot be null.
Disadvantaged (Includes Minority Owned) (CL_DISADV_FL)
Disadvantaged is set to “Y” if the “Vendor Class” in the input record is 6. Otherwise, it is set to “N”.
Woman-Owned (CL_WOM_OWN_FL)
Woman-Owned is set to “Y” if the “Vendor Class” in the input record is 3. Otherwise, it is set to “N”.
Labor Surplus Area (CL_LAB_SRPL_FL)
Labor Surplus Area is set to “N” by the application.
Historical Black College and Universities/Minority Institutions (CL_HIST_BL_CLG_FL)
Historical Black College and Universities/Minority Institutions is set to “Y” if the “Vendor Class” in the input record is 9. Otherwise, it is set to “N”.
Print 1099 (PRNT_1099_FL)
Print 1099 is set to “N” by the application unless either 1099 Type or 1099 Tax ID are provided.
Customer Account Number (CUST_ACCT_FLD)
Customer Account Number is set to a space by the application.
Notes (VEND_NOTES)
Notes is set to a space by the application.
A/P Account Key (AP_ACCTS_KEY)
A default value for the A/P Account Description will be selected at runtime. Please note that both the Vendor Preprocessor screen and the Vendor Maintenance screen display the description, but a system generated key is stored in the Vendor table.
Cash Account Key (CASH_ACCTS_KEY)
A default value for the Cash Account Description will be selected at runtime. Please note that both the Vendor Preprocessor screen and the Vendor Maintenance screen display the description, but a system generated key is stored in the Vendor table.
Pay When Paid (PAY_WHEN_PAID_FL)
A default value for Pay When Paid will be selected at runtime.
The valid values for Pay When Paid are as follows:
“Y” (Yes)
“N” (No)
Pay Vendor (AP_CHK_VEND_ID)
Pay Vendor is set to the Vendor ID by the application.
Employee (EMPL_ID)
Employee ID is taken from the input record.
The validations for Employee ID are as follows:
The Employee must exist in the EMPL table. This table is maintained by the user via the People/Labor Module.
User (USER_ID)
User is set to the current User at runtime.
Date (ENTRY_DTT)
Date is set to the current System Date at runtime.
Allow Edits to Pay Vendor on Voucher (ED_VCH_PAY_VEND_FL)
A default value for Allow Edits will be selected at runtime.
The valid values for Allow Edits are as follows:
“Y” (Yes)
“N” (No)
Allow Auto-Vouchering for POs (AUTO-VCHR-FL)
Allow Auto-Vouchering for POs is set to “N” by the application.
Number of Receipt Lines (RECPT_LN_NO)
Number of Receipt Lines is set to zero by the application.
Payroll Vendor Flag (PR_VEND_FL)
Payroll Vendor Flag is set to ‘N’ by the application.
Performance Calculation Start Date (CALC_START_DT)
Performance Calculation Start Date is left null by the application.
Performance Calculation End Date (CALC_END_DT)
Performance Calculation End Date is left null by the application.
Percent Rejected (REJ_PCT_RT)
Percent Rejected is set to zero by the application.
Percent of Receipts Late (LATE_RECPT_PCT_RT)
Percent of Receipts Late is set to zero by the application.
Percent of Receipts Early (EARLY_RECPT_PCT_RT)
Percent of Receipts Early is set to zero by the application.
Percent Late Original Due Date (LATE_REC_ORIG_RT)
Percent Late Original Due Date is set to zero by the application.
Certification Date (VEND_CERT_DT)
Certification Date is left null by the application.
Check Memo (CHK_MEMO_S)
Check Memo is set to a space by the application
Vendor Group Code (VEND_GRP_CD)
Vendor Group Code is set to a space by the application
Limit Pay Currency Flag(LIMIT_PAY_CRNCY_FL)
Limit Pay Currency Flag is set to a space by the application
Limit Trn Currency Flag(LIMIT_TRN_CRNCY_FL)
Limit Trn Currency Flag is set to a space by the application
The Vendor Address table is keyed on the Vendor (VEND_ID) column and the Address Code (ADDR_DC) column. If an input file record contains a Vendor that does not exist in the Vendor Address table, that record is added to the Vendor Address table. Otherwise, the existing information for the vendor is updated with the data contained in the input record. Currently, the Vendor Preprocessor application supports one Order and one Payment address that are uploaded through this process. If there are additional Vendor Address records for an existing vendor, that record will not be updated by this process. The individual columns in the Vendor Address table are added or updated as follows:
Vendor (VEND_ID)
Vendor is taken from the input record.
The validations for Vendor are as follows:
Vendor is a required field and must exist in the input record.
Vendor must be unique in the input record.
Address Code (ADDR_DC)
Address Code set to “PMT” for a Payment address or “ORD” for an Order address. The choice is determined by the position of the data in the input record.
Address Line 1 (LN_1_ADR)
Address Line 1 is taken from the input record.
Address Line 2 (LN_2_ADR)
Address Line 2 is taken from the input record.
Address Line 3 (LN_3_ADR)
Address Line 3 is taken from the input record.
City (CITY_NAME)
City is taken from the input record.
State (MAIL_STATE_DC)
State is taken from the input record.
The validations for State are as follows:
The State must exist in the Mail State table. This table is maintained by the user via the System Administration Module.
Country (COUNTRY_CD)
Country is taken from the input record.
The validations for Country are as follows:
The Country must exist in the Mail State table. This table is maintained by the user via the System Administration Module.
Postal Code (POSTAL_CD)
Postal Code is taken from the input record.
Phone No. (PHONE_ID)
Phone No. is set to a space by the application.
Other Phone No. (OTH_PHONE_ID)
Other Phone No. is set to a space by the application.
Fax No. (FAX_ID)
Fax No. is set to a space by the application.
Tax Code (SALES_TAX_CD)
Tax Code is left null by the application.
Ship ID (SHIP_ID)
Ship ID is left null by the application.
Pmt Addr Code (S_PMT_ADDR_CD)
If the address is a payment address (determined by placement in the input record). For new vendors the Pmt Addr Code will be set to “D”. For existing vendors with new address information, a check is performed to see if the vendor has a “D” record and ADDR_DC code other than PMT. If so, Pmt Addr Code will be set to “Y”. Otherwise, if no “D” record exists, Pmt Addr Code will be set to “D”.
The Pmt Addr Code will be set to “N” if the address is an order address (determined by placement in the input record).
Ord Addr Code (S_ORD_ADDR_CD)
If the address is an order address (determined by placement in the input record). For new vendors the Ord Addr Code will be set to “D”. For existing vendors with new address information, a check is performed to see if the vendor has a “D” record and ADDR_DC code other than ORD. If so, Ord Addr Code will be set to “Y”. Otherwise, if no “D” record exists, Ord Addr Code will be set to “D”.
The Ord Addr Code will be set to “N” if the address is a payment address (determined by placement in the input record).
Company ID (COMPANY_ID)
Company ID is set to the Logged-In User’s Company ID.
Active Flag (ACTIVE_FL)
Active Flag is set to “N” by the application.
ABA Number (BANK_ABA_NO)
ABA Number is left null by the application.
Bank Account ID (BANK_ACCT_ID_S)
Bank Account ID is set to a space by the application.
ACH Transaction Code (S_ACH_TRN_CD)
ACH Transaction Code is set to “23” by the application.
The following errors may appear on the Error Report. If an error is encountered in the input record, it will cause the rejection of the record containing the error.
1099 Type
Not found in 1099 Box Type table
The 1099 type in the input file does not exist in the 1099 Box Type table.
Employee
The Employee ID in the input file does not exist in the Employee table.
Order Country
Not found in Mail State table
The country in the order address section of the input file does not exist in the Mail State table.
Order State
Not found in Mail State table
The state in the order address section of the input file does not exist in the Mail State table.
Payment Country
Not found in Mail State table
The country in the payment address section of the input file does not exist in the Mail State table.
Payment State
Not found in Mail State table
The state in the payment address section of the input file does not exist in the Mail State table.
Terms
Must be in Input File
The terms must be in the input file.
Terms
Not found in Vendor Terms table
The terms in the input file do not exist in the Vendor Terms table.
Vendor
Duplicates found in Input File
A particular Vendor ID was found more than once in the input file. An individual vendor may occur only once in the input file.
Vendor
Must be in Input File
The Vendor ID must be in the input file.
Vendor Name
Must be in Input File
The Vendor Name must be in the input file.
The following error messages may appear on the screen:
An error has occurred while trying to access a table. Processing has stopped.
This error should not normally occur. Record the status of the processing meter and contact technical support for assistance.
Invalid A/P account description.
The A/P account description entered on the screen does not exist in the Default Accounts Payable Accounts table. Use the "Lookup" button to see a list of valid A/P Account Descriptions.
Invalid cash account description.
The cash account description entered on the screen does not exist in the Default Cash Accounts table. Use the "Lookup" button to see a list of valid Cash Account Descriptions.
The input file that you have entered does not exist. Press the Select button to search for available files.
The application was unable to find the file specified in the input file field. If the file is not in the current Windows directory, pre-pend the file name with the full directory path or press the “Select” button to search for available files.