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.