Processing Details (Oracle)
Use the information in this section to know more about the processing details that Import Items use for Oracle databases.
Files Necessary to Run the Import Items Preprocessor
The following custom files must be installed and accessible in order to run the Import Items Preprocessor:
| File | Description |
|---|---|
| AOPITEM.EXE | Application executable |
| AOPITEM.ORA, AOPITEM2.ORA, AOPITEM3.ORA, AOPITEM4.ORA, AOPITEM5.ORA, AOPITEM6.ORA | You must apply Oracle stored procedures before you can execute the Import Items Preprocessor. |
| AOPITEM.QRP | Report Template |
Note: If your Costpoint system is configured on an Oracle database platform, the Import Items Preprocessor supports all file layouts listed in this documentation.
Import Items Preprocessor Tables
The following Costpoint tables are maintained by this preprocessor:
| Table | Table Name | Definition |
|---|---|---|
| ITEM | Item | Stores parts, services, and goods. |
| PART | Part | Stores additional information required by parts. Many of these characteristics act as defaults in other modules as this part is used. The Comments tab and the Units of Measure andU/M Conversions subtasks are available on the Manage Parts screen, and can be assigned to this part. |
| ITEM_UM | Item Unit of Measure | A cross-reference between parts and units of measure that indicates the applicable units of measure for a given part and their use. Each part has three of these cross-references, one each for inventory, ordering, and selling. |
| ITEM_UM_CONV | Item Unit of Measure Conversion | Part specific conversions. Example: plate to pound for specific sheet metal part. |
| ITEM_VEND | Item Vendor | A cross-reference between items and vendors. Used to indicate the vendors from which this item is available. Can be used to track vendors when the vendor item number is unknown, and to indicate preferred or approved vendors for a given item. |
| ITEM_CST | Item Cost | Use to enter cost information for individual items. |
| ITEM_PROJ_CST | Item Project Cost | Use to enter cost information for individual items fir a specific project. |
| ITEM_LT | Item Lead Time | The span of time required to perform a series of operations. The time between recognition of the need for an order and the receipt of goods. |
| ITEM_TEXT | Item Text | Use to establish text, associated with a given validated text label, that can be associated with any item. |
| ITEM_UDEF | Item User-Defined | This table holds user-defined information associated with the item. |
| ALT_PART | Alternate Part | Contains a cross-reference list of manufacturer and vendor part numbers that are alternates to the specified part. The manufacturer part has functional and physical characteristics that are equivalent in performance, reliability, and maintainability, and can be exchanged for one or more internal parts without alteration. |
| SUBST_PART | Substitute Part | Substitute parts are used in Part Project Data for materials planning. Substitute parts are similar enough to make it exchangeable with another part. |
| PART_PROJ | Part Project | Links one or more projects to the part and enters material planning and QC information specific to that part/project. |
| PROJ_SUBST_PART | Project Substitute Part | An item that can be exchanged for another (only within the referenced project). A substitute part is an internal part. Usage would be within bills of materials or order entry. |
| ITEM_PRODUCT | Item Product | The Item Product table stores information for selling items, including the sales and shipping information that accompanies a billed item. Subsets of products can be stored. |
| PROD_PRICE_CATLG | Product Price Catalog | This table stores Catalog Product Unit Pricing information and is established on the Manage Product Price Catalogs screen in Costpoint Product Definition. |
| PROD_PRICE_SCH | Product Price Schedule | This table is maintained on the Manage Product Price Catalogs screen and stores sales price information for a given catalog item. |
| PROD_CST_SCH | Product Cost Schedule | This table is maintained on the Manage Product Price Catalogs screen and stores cost information for a given catalog item. |
| PROD_WARR_CST_SCH | Product Warranty Cost Schedule | This table is maintained on the Manaqge Product Price Catalogs screen and stores warranty information for a given catalog item. |
| PROJ_CLIN | Project Contract Line Item Number | This table contains the valid project/CLIN combinations. The combinations are stored by unique project and CLIN. |
| PROJ_CLIN_PROD | Project Contract Line Item Number Product | This table contains the items for the project/CLIN combinations. Items are stored by unique project, CLIN, and item. |
| CLIN_PRICE_SCH | Contract Line Item Number Price Schedule | This table stores information on the CLIN price schedule. Information is stored by unique project, CLIN, item, and price schedule. |
| CLIN_CST_SCH | Contract Line Item Number Cost Schedule | This table stores information on the CLIN cost schedule and its item subtask. Information is stored by unique project, CLIN, item, and cost schedule. |
| CLIN_WARR_CST_SCH | Contract Line Item Number Warranty Cost Schedule | This table stores warranty cost information for the project with CLINs. Information is stored by unique project, CLIN, item, and warranty cost schedule. |
Input File Details
The input file line layouts for each line type maintained by this process are documented in the following sections. You can process only one input file at a time, but the file can contain different line types. Only the Item record is required for inserting new services and goods. All other lines are optional, and their order is inconsequential.
To create a part with an ITEM input file record, you must also include PART and ITEM UM records. If you use the PT2 record format for part setup, it contains all information required to populate the required tables for a part.
Each table lists:
- Input File Field: This column displays the name of the Costpoint field for which data is processed.
- Costpoint Column: This column displays the functional field name within Costpoint for which data is processed.
- Column Type and Max Length: This column displays the allowable data type and maximum character length for the Input File Field.
- Starting Position: This field displays the input file field's starting position number for the first character in a string of data in a fixed-length input file.
- Ending Position: This field displays the input file field's ending position number for the last character in a string of data in a fixed-length input file. The Ending Position number is the Starting Position number plus the maximum length for a given input file field. This is the last character of data that can exist before the next input file field (that is, the next starting position number) is populated.
- Required: This field indicates whether or not the input file field must be populated on the input file.
- Values, Defaults, and Notes: This field indicates any miscellaneous processing data for the input file field.
In the following sections, each table containing input file fields that are defined as decimals in Costpoint must include the decimal point in the data being processed. Otherwise, integer (whole) numbers are assumed.
- Input file fields that are defined as dates in Costpoint must be 10 characters and must be in YYYY-MM-DD format (a four-character year (including century), month (01-12) and day (01-31)). Year, month, and day must be separated by hyphens (-).
- Time stamp fields in Costpoint must be 19 characters and in YYYY-MM-DD-hh.mm.ss format (a four-character year (including century), month (01-12), day (01-31), hour (00-24), minute (00-59) and second (00-59)). Year, month and day must be separated by hyphens (-); another hyphen must follow day and hour; and minute and second must be separated by periods (.). Time can be omitted.
- Data for percentage fields must be entered as its decimal equivalent. For example, 25.5% must be entered in the input file record as .255.
Note: If a new line is rejected, all dependent new lines for that item are rejected (and included in the error report and written to the error output file), even if there are no errors associated with the dependent line. For example, if a new item line is rejected, all lines associated with that item are rejected.
When you set up a part with the Item, Part, and Item UM input file records, if a Part or Item U/M on a new line is rejected, the associated Item line must also be rejected, and therefore all new lines associated with the new item line are also rejected.
Input File Excel Template
Go to the Developer Resources page of the Costpoint Information Center to download the Excel template file specifically designed for this preprocessor. Templates for other Costpoint preprocessors are also available at the same location.