The Purchase Requisition Preprocessor is an add-on product for Costpoint. This enhancement allows you to load new requisition information from an external system, validate that information, and then enter it into Costpoint. These requisitions can be submitted for approval and workflow messages can be generated for approvers.
Multiple users can use this preprocessor simultaneously only if they use different company IDs.
Your company ID triggers the validation of company tables when looking up valid values. When you save a new requisition or any new parameter rows, it is automatically assigned with your company ID. The Find/Query button on the toolbar will filter on parameters by company.
This preprocessor reads the specified input files (or staging tables) and performs the relevant process validations against the requisition lines and other associated tables. This process can only be used to update the status of requisitions that match your company.
You can only process whole requisitions with this preprocessor. You can insert a new requisition or void an existing requisition, but you cannot update individual requisition lines. When uploading valid records with requisition/requisition line information that already exists in Costpoint, you can either replace the existing requisition in its entirety or have the system give you an error to avoid overwriting existing records.
Requisition/requisition line records can be uploaded with a status of Pending, In Approval, or Void. Automatic initiation of the requisition approval process (if required at either the header or the line level, per the parameters established in the Requisition Settings screen of Costpoint Procurement Planning) depends on the option you select in the Submit for Approval drop-down box.
A trial run will generate an Error report if any header or line record contains an error or warning message. If you select the Process Valid Requisitions option, input file (or staging table) data without errors will be processed while those with errors will not. Warnings within the records (which will be listed in the Error report) are acceptable, and processing will continue if there are just warnings and no errors. If you select the Process No Records option, any data in the input file (or staging table) containing errors will result in no requisitions created.
Consider the following example in which there are four requisitions in the input file (or staging table): Requisition 1, which has an error; Requisition 2, which has only a warning; Requisition 3, which has an error and a warning; and Requisition 4, which has neither an error nor a warning. If you selected Process Valid Requisitions, Requisitions 2 and 4 will be created. If you selected Process No Records, no requisitions will be created.
The Preview, Print, Process, and Process/Print buttons are available in the toolbar for use with this preprocessor.
Use this screen to upload requisition information (new or update existing) after you have created an input file (or staging table) with the desired information.
To set up Costpoint data, establish default values in settings for Costpoint Requisitions, Product Definition, Purchasing, and Production Control, along with information needed in other related applications in order to validate uploaded data and load default values. Details are provided in the Table Update section.
In this group box, enter the path and name of the input file, or use the Select pushbutton to open a Windows Open File dialog box, where you can select a file for processing. When you have entered the input file, use the Format drop-down list to choose the format in which the input file data will be processed and saved.
Use this drop-down list to select the ASCII file format to use with the preprocessor: Fixed Length ASCII File or Delimited ASCII File. In either case, you must use a previously saved file containing data entered according to the specified layout of the input file.
An additional option, Tables, is available. Select this option to use staging tables to import your data.
Fixed Length ASCII File – Fixed Length ASCII text files do not have commas to delineate separate fields. Each field is a fixed length and must be filled with either an appropriate character or a space to the specified width. It is not necessary to zero fill numbers; spaces can be used to maintain the proper format. Numeric fields should be right justified; character fields should be left justified. Each record must be on a separate line. For example, if the line length is 200 characters and the last piece of meaningful data is in position 100, another 100 spaces are not required. Every row should end with a carriage return and a line feed.
Delimited ASCII File – Delimited ASCII text files have a comma, or other user-defined symbols, to delineate separate fields. It is not required to put a comma (or user-defined symbol) after the last field entry. If you choose this option, you must choose a file delimiter using the File Delimiter drop-down box.
Each Header input file record must have at least one Line input file record. Similarly, a Line input file record must have an associated Header input file record. Line records do not need to physically follow their corresponding Header input file record (although recommended). A single header row is sufficient for multiple requisition line rows associated with the same requisition. Requisition ID is used to determine which line records belong to which header record. Similarly the requisition ID/requisition line combination will be used to determine lower level details such as line accounts and line charges.
After processing is complete, the input file will be renamed with an extension of .OLD.
Tables – Select this option to use staging tables to import your data. Selecting this option enables the Tables group box. If you select this option, you do not need to select a file. Use a custom program to load the data tables. You can update them and use them repeatedly to import requisitions. Costpoint keeps track of the most recent date and time of staging table modifications and of Costpoint imports. See the documentation for the Tables group box for more information.
If the input file format you are planning to use is a Delimited ASCII File, use this drop-down list to select the file delimiter you’ve defined within the file. If you are using a Fixed Length ASCII File, then this drop-down list will be grayed out and unavailable.
A file delimiter is an ASCII character used to separate fields of data within an ASCII flat file.
Comma – Each field in the ASCII file is separated by a comma (,). It is not required to have a comma appear after the last field entry.
Other – Each field in the ASCII file is separated by a character you define. Enter this character in the adjacent field. It is not required to have this character appear after the last field entry.
Use this button to open an Open File window and browse for a flat ASCII file to use with this preprocessor. The file you choose will appear in the adjacent field. You can also manually enter the exact file name (you must include the directory/path location of the file) into the adjacent field without using the Select button.
If you selected Tables as your Input File Format, use this group box to select your staging table options.
These staging tables are pre-defined in the database. They have been designed to mirror the fields on the input file records. Additional columns called REC_NO and S_STATUS_CD have been added to each table listed below. These are permanent database tables. The primary key is REC_NO. When you add new data to the tables, this must be a unique number. The values for column S_STATUS_CD can be NULL, E (Error), and I (Imported). When you add new data to the tables, this column must be set to NULL.
You can load these tables via a custom program. The program reads these tables (instead of input ASCII files) and uploads information into Costpoint. Upon successful processing of one or more records, the corresponding rows from these tables are not deleted, but are assigned an uploaded date. The system keeps track of the date and time that the staging tables were modified. The system also keeps track of the date and time of the last Costpoint upload.
If there are errors generated, corresponding records are not deleted from the staging tables but remain there marked as an error (S_STATUS_CD = E) and without an uploaded date assigned to them. You can correct the problem (repopulate the tables if necessary) and rerun the process with the Reprocess Error Records option selected.
Only records associated with your company are processed.
Use this drop-down list to choose from one of four ways to process records in the staging tables. All four options work only with the Process or Process/Print buttons on the toolbar.
The process runs through the same validation and defaulting as it does for records from an ASCII input file. In order to pass validation, the requisition header, requisition line, and all associated table rows must have no errors. If any one associated row has an error, the whole requisition is treated as an error and all the associated rows are marked with a status of E (Error). Good records that pass validation and error records that fail validation are loaded into separate temporary tables. The status code (S_STATUS_CD) column is updated to E in all the input tables for the corresponding records that have errors. During processing, all the good records are imported into appropriate Costpoint tables. Such records remain in the staging tables until deleted, but the status code column is updated to I to indicate that these rows have been imported.
Load Unprocessed Records – Use this option to import records that were not previously processed. When you select this option, the application processes rows from the input staging tables with a status (S_STATUS_CD) of NULL. If any associated row has an error, the whole requisition is treated as an error and all the associated rows are marked with a status of E.
Reprocess Error Records – Use this option to import records that were previously processed but were rejected because of errors. When you select this option, the application processes rows from the input tables with a status code of E. This process runs through the same validation and defaulting. Good records and records with errors are handled in the same way as mentioned previously.
Delete Imported Records – If you select this option, the application deletes rows with a status code of I from the input tables. The process deletes all associated records from the input tables and displays a confirmation message: "Previously Imported Records are deleted."
Delete Error Records – If you select this option, the application deletes rows with a status code of E from the input tables. The process deletes all associated records from the input tables and displays a confirmation message: "Error Records are deleted."
The field displays the date and time of the most recent change made to one or more of the staging tables (based on TIME_STAMP). If there are multiple tables updated at different times, the latest date and time is displayed.
This field displays the date and time of the last successful upload to the Costpoint tables from the staging tables (based on LAST_UPLOAD_DTT column). Such uploads can be partial (requisitions with no errors were processed even though other requisitions existed with errors, if that option was selected) or complete (all records had no errors). If any part of the requisition (header, header text, line, line account, line charge, line notes, line text) has an error, that whole requisition/requisition line and all related rows are considered to be in error. A given requisition row can be uploaded successfully only if there are no errors for it in any associated row of all the staging tables.
Use this drop-down box to select the approval submission method for the requisitions to be uploaded.
Depending upon the option selected, uploaded requisitions and requisition lines will initiate the approval process. Input file (or staging table) records can only have Requisition (or Requisition Line) Status of Pending or In-Approval (for new record inserts) or Void (for existing record updates).
The system-defined options are:
Submit only In-Approval – Requisition or requisition lines from the input file (or staging table) that have an approval status of "In-Approval" will be uploaded with a status of In-Approval and rows will be created in either the RQ_HDR_APPR or RQ_LN_APPR tables. If approvals are not required, such records will be automatically Approved.
Requisitions or lines from the input file (or staging table) with an approval status of Pending will be uploaded with a status of Pending and will not initiate the approval process (no rows created in either the RQ_HDR_APPR or RQ_LN_APPR tables). If approvals are not required, such records will still have a status of "Pending."
Requisitions or lines from the input file (or staging table) with an approval status of "Void" will be uploaded with a status of Void (if other validations pass) and will not initiate the approval process (no rows created in either the RQ_HDR_APPR or RQ_LN_APPR tables). If approvals are not required, such records will still have a status of Void.
Submit All – Requisition or requisition lines from the input file (or staging table) that have an approval status of either Pending or In-Approval will be uploaded with a status of In-Approval and rows will be created in either the RQ_HDR_APPR or RQ_LN_APPR tables. If approvals are not required, such records will be automatically Approved.
Requisitions or lines from the input file (or staging table) with an approval status of Void will be uploaded with a status of Void (if other validations pass) and will not initiate the approval process (no rows created in either the RQ_HDR_APPR or RQ_LN_APPR tables). If approvals are not required, such records will still have a status of "Void."
Submit None – Requisition or requisition lines from the input file (or staging table) that have an approval status of either Pending or In-Approval will be uploaded with a status of Pending and will not initiate the approval process (no rows created in either the RQ_HDR_APPR or RQ_LN_APPR tables). If approvals are not required, such records will still have a status of Pending.
Requisitions or lines from the input file (or staging table) with an approval status of Void will be uploaded with a status of Void (if other validations pass) and will not initiate the approval process (no rows created in either the RQ_HDR_APPR or RQ_LN_APPR tables). If approvals are not required, such records will still have a status of Void.
The settings you assigned in the Requisition Settings screen in Costpoint Procurement Planning determine whether approval processes are to be assigned at the header or line level. If approvals are done at the header level, the RQ_LN approval process code will be null. If approvals are done at the line level, the RQ_HDR approval process code will be null.
The settings you assigned in the Requisition Settings screen (Materials » Procurement Planning » Requisition Controls) determine how approval processes are assigned. If No Approval Required is selected, this preprocessor will leave both the header and line blank. If Global is selected, this preprocessor will load either the header or line in accordance with the default approval process designated in the Requisition Settings screen. If PAO is selected, this preprocessor will first assign by the inventory project by looking up the PROJ table or, if that does not exist, use the inventory abbreviation’s material acct/org (ACCT_ORG). If assigned by Item Type, this preprocessor will use the part’s approval process (by looking up RQ_APPROV_PROC where PART_TYPE_FL = Y). If approval processes are manually assigned, this preprocessor will pull the approval process from the appropriate INVT_PROJ row. If that row is null, the preprocessor will pull from the RQ_SETTINGS default. If that is also null, an error message will appear.
If the generated requisitions are given a status of Pending, this preprocessor will not create rows in either the RQ_HDR_APPR or RQ_LN_APPR tables.
If they are given a status of In-Approval, the preprocessor will perform the following steps:
When the requisition is validated and saved with an approval process code, the appropriate approval process steps will be copied to either the RQ_HDR_APPR or RQ_LN_APPR tables (depending on the RQ SETTINGS). The estimate dollar amount of the requisition or requisition line should be checked against each approval title’s minimum and maximum dollar limits. Only those approval titles that are within the limit will be added to the requisition or requisition lines approval steps.
If approval is required for the requisition or requisition line(s), and an approval process has been manually or system assigned, all approval title rows with an approval type of E or B, and the lowest approval sequence number (it is possible to have more that one approval title with the same sequence number) will have their NEXT_SEQ_FL set to Y. Rows with an Approval Type of S can be ignored for this purpose. This tells the system which approvals are to be performed next. For instance, if there are six approval title steps for a requisition with the sequence numbers 010, 010, 010, 020, 030, and 040, the three approval titles with the sequence number of 010 will have their NEXT_SEQ_FL set to Y. The others would be set to N (this assumes the estimated requisition amount is within all of the approval titles’ minimum/maximum dollar limits).
Set all S_APPRVL_CD values to P, except for Signature approval type rows, which can be set to A.
If due to dollar limits and approval types, the requisition/requisition line will require no approval set the status to "A" and load the system date to the requisition line approval date.
Default Costs
Default estimated costs can be loaded in the requisition line. If an item is loaded, the program can access the RQ_EST_CST_TYPE to find the preferred sequence numbers for the cost types IL (Item Last), IS (Item Standard), and IR (Item Reference). If the preferred sequence number specifies PL (Project/Item Last), PS (Project/Item Standard), PR (Project/Item Reference), and PA (Project/Item Average), cost will be assigned after you enter an account line. The system will lookup the table and row of the type with the lowest preferred sequence number (unless the number = zero, in which case the type will be ignored).
If the lowest sequence number row has zero cost associated with it, the system will read the costs value for the type with the next lowest sequence number. If that total cost is not zero, the system will load it into the estimated unit cost field.
Suggested Blanket PO
The system will look up the PO_LN table to find a blanket line that has the same part key as the requisition line item and a S_PURCH_AGRMT_CD of S or A with line status of O. If a match is found, the system will then check if the requisition date is greater or equal to the PO_LN. PERF_START_DT and less than or equal to the PO_LN. PERF_END_DT (if not null). The requested date of the requisition line for that part must also be greater than or equal to the current date plus PO_LN. LT_DAYS_NO (using shop floor calendar days if PC_SETTINGS. S_LT_CAL_CD = S). Finally, the requisition line’s quantity (converted to the PO U/M (unit of measure)), must be equal to or greater than the PO_LN. MIN_QTY. If all criteria are met, the system will populate the RQ_LN. SUG_BLKT_PO_ID. The following fields will be populated from the PO: PO_LN.SUG_BLKT_PO_ID. PO_HDR. PREF_VEND_ID (from the PO_HDR. VEND_ID), PO_LN. MANUF_PART_ID, PO_LN. MANUF_PART_RVSN_ID, PO_LN. VEND_PART_ID, and PO_LN. VEND_PART_RVSN_ID.
If the S_PURCH_AGRMT_CD = A and the period of performance, lead time, and minimum quantity requirements are met, a RQ_LN_PO will be generated for the requisition line. If the blanket order U/M is different than the requisition U/M, the quantity is converted to the blanket U/M provided a conversion factor exists. If there is no conversion factor available, no RQ_LN_PO row will be created.
Workflow Notification
If the initial status of the uploaded requisition or requisition line is to be In-Approval (as described above), the system may also need to initiate a workflow case, depending on the approval title.
Multicurrency
Functional vs. Transactional Currency: With multicurrency, requisitions can be created in terms of a different currency (called transactional currency) than its functional currency (which is the currency that is used for the company’s financial statements, and is selected at Costpoint initialization). Requisition information is inputted primarily in the transactional currency, and the system calculates the corresponding value for the functional currency fields. Data is stored in both currencies.
For example, the Estimated Net Unit Amt in the requisition line from the input file (or staging table) loads into the Transaction Net Unit Cost column (RQ_LN. TRN_NET_UNIT_AMT) and the system calculates the value of Net Unit Cost (RQ_LN. EST_NET_UNIT_AMT) in terms of functional currency. This calculated similarly for other amount fields.
Calculations for Exchange Rates: Standard multicurrency functions are used to calculate exchange rates between transactional and functional currencies. This applies exchange rates corresponding to the Rate Group and Rate Date (or Rate Period). For non-Euro currencies, the Trans to Func exchange rate is used. If a direct exchange rate is not available, then the triangulation method using a third common rate is used to derive the exchange rate (if set up in the Multicurrency Settings screen (Accounting » Multicurrency » Controls)). For Euro currencies, the product of Trans to Euro and Euro to Func exchange rates are used to derive the appropriate exchange rate between the transactional and functional currencies.
Requisition Line Account
If the input file (or staging table) has an inventory abbreviation on the requisition line, the line account input file (or staging table) information is optional. However, if P/A/O (project/account/organization) information is also entered in the line account input file (or staging table), there can only be one line account row, and the P/A/O combination must be consistent with the inventory abbreviation entered on the associated requisition line.
Updating Inventory Tables
Inventory
The system will either create or update inventory records (within the INVT table) for lines with inventory part numbers and inventory abbreviations. On-requisition quantities include all requisition line quantities for that part/inventory abbreviation that do not have the status of Void. Requisition lines with a status of Pending, In Approval, or Approved, are included in the on-requisition total. If an existing requisition line is deleted (using the Overwrite Existing option in the Duplicates drop-down list in the Error Handling group box), the system subtracts the associated requisition line quantity (converted to the inventory U/M (unit of measure) if necessary) from the inventory on-requisition quantity.
The Requisition U/M must be convertible to the part’s inventory U/M using the part’s conversion factor or a generic U/M conversion.
When creating a new requisition line with an inventory abbreviation, the INVT for the RQ_LN’s item key and inventory abbreviation is either updated or inserted. The system calculates the RQ_LN. RQSTD_QTY in the part’s inventory U/M if the RQ_LN unit of measure and the part’s inventory U/M are different. If the INVT row exists, row’s ON_RQ_QTY increases by the requisition line’s converted requested quantity. If a matching INVT row does not exist, a new row is inserted, setting the ON_RQ_QTY to the requisition line’s converted order quantity.
Upon deleting an existing requisition line (in the case of duplicate input with the Overwrite Existing option selected in the Duplicates drop-down list in the Error Handling group box), the original part/inventory abbreviation combination’s INVT. ON_RQ_QTY is reduced by the ORIGINAL RQ_LN. RQSTD_QTY (converted to the part’s default U/M if necessary). If the original requisition line did not have an inventory abbreviation, this step is skipped. If the new version of the RQ_LN has an inventory abbreviation, the INVT. ON_RQ_QTY increases by the NEW RQ_LN. RQSTD_QTY (converted to the part’s default U/M if necessary). If the INVT row does not exist, one will be created.
Net Change Flag
When adding or deleting a requisition line row where the INVT has been updated or inserted, if MRP_SETTINGS_CORP. ENABLE_NET_CHG_FL = Y, and PART. S_PLAN_TYPE = P (and S_PLAN_TYPE = S if MRP_SETTINGS_CORP. PLAN_MPS_FL = Y), PART. NET_CHG_FL = N, the PART. NET_CHG_FL is set to Y. In the case of duplicate input with the overwrite option selected, the check is performed on both the ORIGINAL and NEW parts.
A trial run will generate an Error report if there is at least one set of header/line records with an error or warning message.
Use this drop-down list to select the process option you wish to use in the event of errors within the input ASCII file. Choose from the two system-defined options:
Process Valid Requisitions - Upon executing the trial run or upload process, input file (or staging table) records without errors (warnings are acceptable) will be processed while those with errors will not be processed. This is the system default.
Process None - Upon executing the trial run or upload process, no rows will be processed if there is any error present within the input ASCII file. In this case, for staging tables, the error rows have a status of E, and the non-error rows still have a status of Null.
Use this drop-down list to select the process for handling duplicate records in the uploaded file with the existing requisition/requisition line data in Costpoint. Select one of the two available system-defined options:
Do Not Overwrite – If duplicate records are encountered, an error message will appear and corresponding duplicate records will not be uploaded.
Overwrite Existing - If duplicate records are encountered, a warning message will appear and processing will completely replace existing records with the corresponding new duplicate row information (that is, delete old and add new).
Overwriting of existing rows will not be allowed if the requisition lines have a status of C (Closed) or G (Generated), regardless of the option selected.
Select this check box if you would prefer to not have the system create an error file after the trial run or file upload. The system default leaves this checkbox blank wherein the system will create an error file as follows:
Records not processed due to one or more errors will be written to an ASCII error file using the same format in which they were originally saved. The error file is placed in the same path as the input file and will be saved as ZZZZZZxxxxmmddyyyy.ERR (where ZZZZZZ = original input file name, xxxx = sequentially increased number for each day (in case the same input file has been used multiple times in a given day) starting with 0001, 0002, etc., mmddyyyy = date with a two-digit month (01-12), two-digit day (01-31), and four-digit year. For example, the first error file created on April 15, 2005 will be named Testfile000104152005.ERR (where "Testfile" is the name of the input file) followed by Testfile000204152005.ERR, etc.
This is a set of display-only fields on the screen that shows the performance of the process. It provides information about the load and print process, and shows the number of records (lines in the input file (or staging table)) processed along with the number of errors found.
These non-editable fields provide you with information regarding the upload, validation, and print processes as the preprocessor executes them.
This non-editable field displays the date and time the upload process started.
This non-editable field displays the date and time the upload process ended. Once the upload process ends, the validation process immediately begins.
The application reads the specified input file and performs relevant process validations against requisition lines and other associated tables. See the Report/Process Validations table for the report/process validations performed by this preprocessor.
This non-editable field displays the date and time the validation process ended. Once the validation process ends, the Error report immediately begins to print.
This non-editable field displays the date and time the Error report stopped printing.
This non-editable field displays the total number of records read by the preprocessor.
This non-editable field displays the total number of records found with errors during the process.