Use this application to import inventory transactions from an ASCII text file. You can capture transactions from a non-Costpoint source (e.g., bar code systems) and export them into a text file in the defined format for import.
This implementation supports the loading of new inventory transactions, where the preprocessor: (1) reads the input file, (2) validates the input file against the Costpoint database, and (3) inserts and/or updates data in the necessary Inventory, Purchasing, and Receiving tables. The format of the input file records differs according to the transaction type being processed. The first field/character of a record determines the inventory transaction type, the format expected for that transaction, the validations performed, and the database updates to be made.
You can view transactions that are loaded by this preprocessor for I transaction types (Issues to Project/Account/Organizations) in the Materials » Inventory » Issues » Enter Issues screen.
You can view transactions that are loaded by this preprocessor for W transaction types (Issues to Manufacturing Orders) in the Materials » Inventory » Issues » Enter Manufacturing Order Issues screen.
You can view transactions that are loaded by this preprocessor for L transaction types (Location Transfers) in the Materials » Inventory » Transfers » Enter Location Transfers screen.
You can view transactions that are loaded by this preprocessor for H transaction types (Actual Counts) in the Materials » Inventory » Physical Counts » Enter Actual Counts screen.
You can access this application from the Others » Product Interfaces » Preprocessors menu. There are validation rules for Issues to Manufacturing Orders transactions (type W), Issues to Project/Account/Organization Combinations (type I), Location Transfers (type L), and Actual Counts (type H).
Validation of company tables is triggered by the company ID of the user executing the process. When looking up valid values, the system validates all company-related tables using the user's company ID. When a new inventory record is saved, it is automatically assigned the user's company ID. If you use Find or Query, the process filters on parameters by company. All new parameter rows are saved with the user's company ID. You can also keep parts separated by company in Costpoint if you select the Separate Items By Company check box in the Product Definition Corporate Settings subtask of the Materials » Product Definition » Product Definition Controls » Product Definition Settings screen.
The system validates certain values by company during the input file validation process (i.e., checking for valid values in the input file). When the system validates whether an input file value exists in the following tables, the company ID assigned to the user executing the process is filtered with the list of valid values in the tables:
|
Costpoint Table |
Costpoint Table Name |
|
WHSE |
Warehouse |
|
WHSE_LOC |
Warehouse Location |
|
INVT_ABBRV_CD |
Inventory Abbreviation Code |
|
EMPL |
Employee |
|
PROJ |
Project |
|
ORG |
Organization |
|
MO_HDR |
Manufacturing Order Header |
|
RSN_CD |
Reason Code |
|
RES_HDR |
Reservation Header |
|
PHYS_COUNT |
Physical Count |
|
ITEM/PART |
Item and Part (only if you select the Separate Items By Company check box in the Product Definition Corporate Settings subtask). |
As much as possible, the preprocessor employs default values from the settings screens in Costpoint, including (but not limited to) establishing default values in the Inventory Settings and Serial/Lot Settings screens, warehouse and location information in Inventory, and inventory abbreviation information in the Inventory Projects screen.
In the Input File group box, select whether you wish the process to run against a specific file, or whether you wish to have the process run against all eligible files in a file directory.
If you select the Single File option, enter the path and name of the input file, or click the Select button to open a Windows Open File dialog box, where you can select a file for processing.
If you select the All Files in Directory option, enter the directory path, or click the Select button to open a Windows Open File dialog box, where you can select a directory. All files with a suffix of .TXT in the specified directory will be processed.
The format of each record in the input file is based on the transaction type of the record being processed (I, W, L, or H). The first field/character determines the inventory transaction type, the format expected for that transaction, the validations performed, and the database updates to be made. When you have entered the input file, use the Input File Format group box to choose the format in which the input file data will be processed and saved.
When you have chosen the input file, determine the format in which the input file will be processed and saved by selecting an option.
Select this option to process files with fields separated by a pre-defined delimiter (commas or another character). Selecting this option enables the use of the Comma and Other options in the File Delimiter group box.
Select this option to process the file with no field delimiters. For fixed-length, ASCII text input files, you must fill every position in the input file 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, it is not necessary to fill the remaining column lines with spaces.
For example, if the Issue to Project/Account/Organization Combinations input file's maximum line length (i.e., the entire input file) is a total of 479 characters and the last character of meaningful data is in position 222, you do not need to add the remaining 257 spaces. However, if the Reservation ID (beginning at position 213 on the input file) is only seven characters long, you must include the Reservation ID plus three additional spaces before entering the Reservation Line Number in position 223.
End every row with a carriage return and a line feed.
The options in this group box are enabled only when you have selected the Delimited option in the Input File Format group box.
Select this option to use a comma as the file delimiter.
Select this option to use another character as the file delimiter; enter the character in the field to the right.
There are two options for numbering the inventory transactions, depending on how the input file is loaded.
Select this option to use the input file's transaction ID as the Costpoint inventory transaction ID on all saved transactions. You must provide transaction IDs for all input file rows. The warehouse last transaction ID is not changed.
Select this option to have Costpoint assign IDs to the valid transactions based on the warehouse and transaction type. The application uses the warehouse and transaction type to look up the next system transaction ID. If transaction IDs are provided in the input file, the program uses them only to group transaction rows together. All input file rows with the same warehouse, transaction type, and transaction ID are given the same transaction header. If you have not set up that transaction type for auto-assigning in the selected warehouse in the Transaction IDs subtask of the Materials » Inventory » Inventory Controls » Warehouses screen, the system returns an error.
If an error is detected, all rows that were not processed are moved to an ASCII error file in the same format in which they were originally saved. The error file is placed in the same directory as the input file with the name "INUSER_IDMMDDYY.ERR," where the "USER_ID" is the user ID of the person executing the preprocessor, and MMDDYY is the month/day/year that the process was run. If a file with the same name already exists, the new error(s) are appended to the existing file. Select an option to determine what happens if an error occurs during processing:
If you select this option and an error occurs, only those input rows that share the same Inventory Transaction ID, Transaction Type, and Warehouse as the error row are suspended (not loaded to Costpoint) and moved to the error file.
If you select this option and an error occurs, all rows within that input file are suspended (not loaded to Costpoint) and moved to the error file.
The Inventory Transaction Preprocessor application inserts inventory transaction rows (not actual count transactions) into the Inventory Transaction (INVT_TRN), Inventory Transaction Line (INVT_TRN_LN), and Inventory Transaction Line Serial/Lot (INVT_TRN_LN_SR_LT) tables. Only insertions are performed. This application does not update existing inventory transactions.
For Actual Count transactions, the preprocessor updates the Physical Count Detail (PHYS_COUNT_DETL), Serial/Lot tracking (SERIAL_LOT), and the Physical Count Serial/Lot Line (PHYS_CNT_DTL_SR_LT) tables instead of the inventory transaction tables noted above.
All header information for inventory transactions is stored in this table. The record identifies the warehouse, the transaction ID, and the transaction type for the transaction, and indicates whether the transaction has been posted or not. The system creates rows or columns in this table whenever an inventory transaction occurs.
The detailed information for all inventory transactions, including (but not limited to) the part number, quantity, cost, inventory abbreviation, and location, is stored in this table.
This table identifies the inventory transactions of serial/lot tracked parts.
The system reads rows from the Input File and inserts them into the Inventory Transaction Preprocessor work tables.
The application applies defaults and validates each transaction in its entirety.
The system inserts any errors found into the error work table.
The system inserts transactions with no errors into the Costpoint tables and updates the Inventory tables.
If the application finds transactions with errors, it writes all rows for those errors to the Error File "INUSER_IDMMDDYY.ERR."
Once you exit the screen, the system deletes data in all the work tables, including the error table.
Once transactions are processed and imported, you can view them in the maintenance screens listed in the summary section above.
You cannot change transaction lines once they are read and imported into the Costpoint database.
ISSUES TO MANUFACTURING ORDERS (TRANSACTION TYPE "W")
ISSUES TO PROJECT/ACCOUNT/ORGANIZATION COMBINATIONS (TRANSACTION TYPE "I")