VENDOR QUOTE PREPROCESSOR  Version 5.0

Use this screen to load new vendor quotes into Costpoint with up to five price breakpoints and up to three line charges each. You cannot delete or change existing vendor quotes; however, you can update the item vendor and alternate part tables with information provided in the input file. Only one user can access this application at a given time. You can also operate this application from Costpoint Process Management. You must have access to Costpoint Procurement Planning to use this preprocessor.

You can view the quotes loaded by this preprocessor in the Enter Quotes by Vendor or Enter Quotes by Item screens in Costpoint Procurement Planning.

New for Costpoint 5.0

Multicompany

If the Separate Items by Company checkbox is selected in the Product Definition Corporate Settings subtask of the Product Definition Settings screen, the system will filter field validations by the user's company ID, and the Item and Part tables. The company ID of the user executing the process triggers the validation of all company tables. When a new vendor quote record is saved in Costpoint, the system automatically assigns it the user's company ID and search parameters. All company-related tables and Lookup-related valid values (or search parameters in Find or Query) are validated using the user's company ID. Uploads can be processed for only one company at a time (the user's company).

The following tables are validated against the user's company ID:

• Vendor (VEND)

• Request for Quote Settings (RFQ_SETTINGS)

• Employee (EMPL)

• Line Charge Type (LN_CHG_TYPE)

• Item Vendor (ITEM_VEND) (for updates)

Multicurrency

We have added multicurrency features to the Vendor Quote preprocessor, which, in addition to requiring additional calculations, has meant adding fields to the input files. The new input fields are in the same format as the Quote Header and Quote Line Record.

With the multicurrency feature, all amount fields in the input file are in the transactional currency (which may or may not be the same as the functional currency). Vendor quote information is entered primarily in the transactional currency (i.e., the quote's currency, per the vendor), and the system calculates the corresponding amounts for the related functional currency fields in accordance with exchange rate information in other tables. Data is stored in both currencies in Costpoint.

If exchange rate information does not exist, the system will assume that the transactional and functional currencies are the same (1:1). All currency-related columns will still be populated with default values, the system exchange rate will be "1," and amount columns will therefore have the same values for transactional and functional currencies.

Costpoint Multicurrency tables

To select transactional currencies, calculate exchange rates, and validate customs information, the multicurrency functionality relies on data in the following tables (which must exist in Costpoint before you process any transactions): 

• Multicurrency Settings (MU_SETTINGS) - Sets default multicurrency information. Validate by the user's company ID.

• Currency Status (MU_CRNCY_STATUS) - Stores status on currency and/or Euro information. Used to validate exchange rates.

• Rate Group (RT_GRP) - Stores rate group information. Used for exchange rate information.

• Rate Group Currency (RT_GRP_CRNCY) - Contains a list of From/To currencies for valid rate groups. Used for exchange rate information.

• Rate Source (RT_SRCE) - Stores exchange rate source information.

• Rate by Date (RT_BY_DT) - Stores exchange rates for valid From/To currency sets by start/end dates.

• Rate by Period (RT_BY_PD) - Stores exchange rates for valid From/To currency sets by FY/PD.

• Currencies (CURRENCY) - Stores information for currencies used in the database.

• Vendor Quote Multicurrency tables

The following tables/input columns support multicurrency specifically for vendor quotes:

Quote Header table (QT_HDR):  

• Euro to Functional Rate (EUR_TO_FUNC_RT)

• Rate Group (RATE_GRP_ID)

• Transaction Currency Code (TRN_CRNCY_CD)

• Transaction Freeze Rate Flag (TRN_FREEZE_RT_FL)

• Transaction to Functional Currency Rate (TRN_TO_EUR_RT)

• Transaction to Functional Currency Rate Flag (TRN_TO_EUR_RT_FL)

Quote Line table (QT_LN): 

• Default Gross Unit Amount - Transaction (TRN_DFLT_GR_UN_AMT)

• Default Net Unit Amount - Transaction (TRN_DFLT_NT_UN_AMT)

• Quote Line Charge Amount - Transaction (TRN_QT_LN_CHG_AMT)

Quote Line Break table (QT_LN_BRK): 

• Gross Unit Cost Amount - Transaction (TRN_GR_UN_CST_AMT)

• Net Unit Cost Amount – Transaction (TRN_NT_UN_CST_AMT)

Quote Line Charge table (QT_LN_CHG): 

• Quote Line Charge Amount - Transaction (TRN_QT_LN_CHG_AMT)

Files Necessary to Run Vendor Quote Preprocessor

Once you have copied the new files onto your system and executed the script file, establish default settings in the Vendor Settings screen in Costpoint Procurement Planning, Purchasing, and/or Accounts Payable, including setting up the appropriate quote header, quote line, and vendor ID information. You must apply the following custom files and stored procedures files to the database to run the Vendor Quote preprocessor:

Input File

Enter, or use the Select pushbutton to choose, the delimited or fixed-length file to upload. If you choose the Select pushbutton, a dialog window will display and you can select the file. This application supports two input file record formats (delimited and fixed-length). Please refer to the "Processing Details" section for additional details. 

1.         The first file record format contains the information necessary to populate the Vendor Quote Header table (QT_HDR).

2.         The application uses the second file record format to populate the Vendor Quote lines and the Vendor Quote child tables (QT_LN, QT_LN_BRK, QT_LN_CHG). 

Note (International users): The data can contain non-English characters, provided they are in the ASCII character set. Before creating an ASCII text (.TXT) file, be sure that your keyboard language is set to "EN" (United States English) to prevent the application from generating errors.  

We recommend the use of .TXT and .CSV file-naming conventions.

Input File Format

Delimited

Select this radio button if the input file has delimiters as field separators. This is the default.

Fixed Length

Select this radio button if the input file contains fields that start and end in a specified column. If the Input File is a fixed-length format file, you must fill every position with the appropriate number of either characters or spaces for a given column before entering data for the next column. It is not necessary to zero fill numbers; you can use spaces to maintain the proper format. Numeric fields should be right justified; character fields should be left justified. Once you have entered the last character of meaningful data for the input file, you need not fill the remaining column lines with spaces. 

For example, if the Vendor Quote Header record format's maximum line length (i.e., the entire input file) is a total of 467 characters and the last character of meaningful data is in position 36 (Buyer), you do not need to add the remaining 431 spaces. However, if the Vendor (beginning at position 12 on the input file) is only five characters long, you must include the Vendor ID plus seven additional spaces before entering the Quote Type (if applicable) in position number 24. Please refer to the "Processing Details" section for additional information.

File Delimiter

If the input file is delimited, you must choose the delimiter. The default is Comma. To use another delimiter, select the Other radio button and enter a single character in the field to the right.

Note:  Do not embed the delimiter in any of the input fields.  When the application encounters the declared delimiter during processing, it will interpret it as the end of the input field.

Quote IDs

Depending on which input file is being loaded, select one of two radio buttons in this group box to designate the method of quote numbering for this preprocessor.

Use Input File Values

Select this radio button to use the input file quote ID as the vendor quote ID in Costpoint. If you select this radio button, you must populate the Quote field in the input file.

Auto-Assign

You can select this radio button if the Auto-Assign Quotes checkbox is selected in the RFQ Settings screen in Costpoint Procurement Planning. If you select this option, all quote lines for a given vendor will be grouped together. To use this option, you must provide the vendor ID for all Vendor Quote Line input files.

Costpoint will automatically assign a quote line number, incrementing the line number one per quote line input file row. 

Processing Details

This preprocessor supports up to five quantity breakpoints and three line charges per vendor quote line. The application deletes the input file after processing.

Each Quote Header (QT_HDR) input file record must have at least one Quote Line input file record and vice versa. If you auto-assign quote IDs by selecting the Auto-Assign radio button, the vendor ID on the Quote Line input file record must exist on a Quote Header input file record and vice versa. If you do not select the Auto-Assign radio button, the quote ID/vendor ID combination on the Quote Line input file record must exist on a Quote Header input file record and vice versa.

Note:  Input file fields that are defined as a date in Costpoint must be 10 characters, formatted as YYYY-MM-DD (a four-character year (including century), month (01-12), and day (01-31)).  You must separate year, month, and day fields by hyphens (-).  The format for timestamp fields in Costpoint is 19 characters, formatted as YYYY-MM-DD-hh.mm.ss (a four-character year (including century), month (01-12), day (01-31), hour (00-24), minute (00-59) and second (00-59)). You must separate year, month, and day by hyphens (-), with another hyphen following day. Separate hour, minute, and second by periods (.).  Timestamp settings are not required.

Error Handling

The system creates an ASCII text error file for each input file record that is not processed.  Refer to the "Error Messages" section for each input file's validation rules. For some errors, the process does not update the database tables for that input file or for any related input files. If you selected the Auto-Assign radio button (for quote IDs) and the program encounters an error, the system will move all records for that vendor ID (header and line) to the error file. Similarly, if you selected the Use Input File Values radio button (i.e., Quote IDs are manually assigned in the input file) and the program encounters an error, the system will move all records for that quote ID/vendor ID combination to the error file. The application will save unprocessed rows to the error file in the format in which they exist in the input file. The error file should be in the same path as the input file and named "VQYYYYMMDD.ERR," where "YYYYMMDD" is the process run date. If the file already exists, the application will append new errors to the file. If "VQYYYYMMDD.ERR" does not exist, the process will create the file.

The system produces an error file report detailing all problems that caused the process not to load a given row.

Certain types of errors do not cause the processing to stop or messages to print. If the system encounters these types of errors, processing for that record and any related records will continue, but the data rows in error will not be loaded (or placed in the error file). For a list of these fields and further information, see the "Vendor Quote Line Input File Error Messages and Conditions" section at the end of the document for the fields where Prevents Processing is "No."

Expiration Date

If you do not provide an Expiration Date in the Vendor Quote Header input file but enter a non-zero number in the Expiration Days field, the system will add the Expiration Days number to the provided or defaulted Quote Date input file field to obtain an Expiration Date that it will load for the quote header. If you provide neither an Expiration Date nor a non-zero Expiration Days amount, no expiration date will be included for the vendor quote. 

Quote Line Break (QT_LN_BRK) Processing

Each Quote Line (QT_LN) row will have at least one Quote Line Break row. The first Quote Line Break row is populated with the Quote Line's Minimum Quantity and Default Net Unit Cost Amount. You can add up to four more rows to the Quote Line Break table if you have provided values in the Break Quantity 2-5 input file fields. 

Quote Line Charge (QT_LN_CHG) Processing

You can add up to three rows to the Quote Line Charge table for each quote line added in the preprocessor.  If you include Line Charge (1-3) information in the Vendor Quote Line input file, the system will add the provided values to the Quote Line Charge table.

Updates to Other Tables

Item Vendor (ITEM_VEND) Updates

If you add a quote for an item, the program will update the Item Vendor table. If that vendor does not already exist for that item in the Item Vendor table, the system will add a row filling in the item and vendor information and the last quote ID and last quote date (LAST_QT_ID and LAST_QT_DT from the QT_HDR). Fill all other columns with zeroes, spaces, or nulls as necessary.  If the Item Vendor record does exist, the program will update the last quote ID and last quote date if the quote date is later than the previous last quote date.

Alternate Part (ALT_PART) Updates

If the following conditions are met, the program will attempt to load a new value in the Alternate Part table. If it generates an ALT_PART row, the user's company ID will be loaded if the VEND_ID is populated in the row. If the VEND_ID column is null, the COMPANY_ID columns should be left null. If a row already exists with the same combination of PART_KEY, manufacturer, manufacturer part, manufacturer part revision, vendor, vendor part, and vendor part revision, the program will not insert a new Alternate Part row. Otherwise, it will insert a new row w.

• A quote line input file's Add Alternate Part field is "Y" (Yes),

• You enter a part in the Item field, and

• You included either a vendor part or a manufacturer and manufacturer part number in the quote line input file.

RFQ Status Updates

If you entered an RFQ ID in the Quote Header input file, the system will attempt to match each line of that quote to a corresponding RFQ line for the RFQ ID entered.  If the item and revision match (if item is entered) or the misc line charge matches the RFQ line, the program will update the status of the RFQ line to "C" (Closed). Each time this is done, all statuses for RFQ lines will be checked. If all lines for an RFQ have a status of "C" (Closed) or "V" (Void), the system will set the RFQ Header Status to "C" (Closed).

Vq-INPUT FILE

Vendor Quote Header Input File Layout (QT_HDR)

Vendor Quote Line Input File Layout (QT_LN, QT_LN_BRK, QT_LN_CHG)

One QT_LN will be written per vendor quote line input record. Depending on the contents of the input record, up to five Vendor Quote Line Break (QT_LN_BRK) rows and up to three Vendor Quote Line Charge (QT_LN_CHG) rows may also be written.

DETAILED TABLE SPECIFICATIONS

ERROR MESSAGES