SQL Part 2 Line Layout (PT2) (SQL)

The Part and Item tables in Costpoint store basic information for parts. Use the PT2 input file to upload data to the ITEM, PART, and ITEM_UM tables.

You can view or update the information stored for each field on the Manage Parts screen in Costpoint Product Definition.

Note:

If your Costpoint system is configured on a Microsoft SQL Server database platform, the Import Items preprocessor supports only the Part (PT2) file layout.

The application uses default values for INSERTS ONLY if the input file field value is not provided. The exception is MODIFIED_BY, which is updated using the default value if no value is provided.

Input File Field Costpoint Column Column Type & Max Length Starting/Ending Position No. Required Values/Defaults/ Notes
Line Type Character 3 1 - 3 Yes PT2 is used to indicate PART 2 information.
Part ID ITEM_ID, PART_ID Character 30 4 - 33 Yes If a Part ID/Part Revision ID exists, the ITEM, PART, and ITEM_UM tables are updated.

The fixed length file format accepts 30 characters while delimited file format accepts up to 50 characters.

Part Revision ID ITEM_RVSN_ ID, PART_RVSN_ ID Character 3 34 - 36 Yes (if revisions are used) If an Item ID/Item Revision ID exists, the ITEM table is updated. If you did not select the Allow Multiple Revisions for Each Item check box on the Configure Product Definition Settings screen, only the Item ID is checked to see if it exists. The Item Revision ID defaults with a space, and always loads a space if the Allow Multiple Revisions for Each Item check box is not selected. Load the item's revision in the Last Revision field. The default is a space.

The fixed length file format accepts 3 characters while delimited file format accepts up to 10 characters.

Item Description *ITEM. ITEM_DESC Character 60 37 - 96 Yes (NOT required for an update)
Default Unit of Measure Code *ITEM. DFLT_UM_CD Character 3 97 - 99 No The default is EA (Each). Also used as Inventory U/M in ITEM_UM table. If ITEM_DFLT_UM_ CD is provided, the U/M must exist in the Units of Measure table.
Commodity Code * ITEM. COMM_CD Character 8 100 - 107 No If a value is provided, must exist in Commodity Codes table. If the Separate Items by Company check box is selected on the Corporate Settings block of the Configure Product Definition Settings screen, this field's value must exist for your company ID.
Make or Buy Code * PART. S_MAKE_BUY_CD Character 1 108 No Value must be B (Buy) or M (Make). Default is B.
Status Type * PART. S_STATUS_TYPE Character 1 109 No Value must be E (Estimating), O (Obsolete), P (Pre-Release), R (Release), or S (Phase-Out). The default isR.
Part Type * PART. S_PART_TYPE Character 1 110 No Value must be B (Buy With Components), P (Phantom), R (Reference), S (Standard), or T (Tool). the default is S.
As Required Flag * PART. AS_REQD_FL Character 1 111 No Value must be Y (Yes) or N (No). The default is N.
Planning Type * PART. S_PLAN_TYPE Character 1 112 No Value must be N (None), P (MRP), R (Reorder), or S (MPS). The default isP.
Buyer ID * ITEM. BUYER_ID Character 12 113 - 124 No The default is null. If a Commodity Codeis provided, the Buyer IDdefaults if selected on the Manage Commodity Codes screen. If ITEM.BUYER_ID is provided, theBuyer IDmust exist in Buyers table. If the Separate Items by Company check box is selected in the Corporate Settings block of the Configure Product Definition Settings screen, this field's value must exist for your company ID.
Planner ID * PART. PLANNER_ID Character 12 125 - 136 No The default is a space. If the Separate Items by Company check box is selected on the Corporate Settings block of the Configure Product Definition Settings screen, this field's value must exist for your company ID.
Product Classification Code *PROD_CLASSIF_ CD Character 6 137 - 142 No The default is null. If a value is provided, it must exist in the Product Classifications table.
Hazardous Material Flag * ITEM. HAZMAT_FL Character 1 143 No The value must be Y (Yes) or N (No). The default is N.
Allow Overshipment Flag * ITEM. OVRSHP_ ALLOW_FL Character 1 144 No The value must be Y (Yes) or N (No). The default is N.
Receipt Tolerance Percentage Rate * ITEM.RECPT_ TOL_ PCT_RT Decimal 10.8 (maximum input file length - 4) 145 - 148 No The default is0. The input file format is 9.99 (maximum four characters including the decimal point). The maximum value is 1.00, which indicates 100%.
Commercial and Government Entity ID * PART. CAGE_ID_FLD Character 6 149 - 154 No The default is retrieved from the Company CAGE Code field on the Configure Product Definition Settings screen (ITEM_ SETTINGS table) if the Make/Buy Code input file field is M. Otherwise, use a space.
Military Specification ID * PART. MIL_SPEC_ID Character 20 155 - 174 No The default is a space.
National Stock Number * PART. NSN_ID Character 20 175 - 194 No The default is a space.
Quality Control Required Flag * PART. QC_REQD_FL Character 1 195 No The value must be Y (Yes) or N (No). The default is retrieved from theQC Inspection Required check box on the Configure Product Definition Settings screen (ITEM_SETTINGS table).
Certificate of Conformance Flag * PART. CERT_ OF_CNFRM_FL Character 1 196 No The value must be Y (Yes) or N (No). The default is retrieved from the Certificate of Conformance Required check box on the Configure Product Definition Settings screen (ITEM_SETTINGS table).
Source Inspection Flag * PART. SRCE_INSP_FL Character 1 197 No The value must be Y (Yes) or N (No). The default is retrieved from the Source Inspection Required check box on the Configure Product Definition Settings screen (ITEM_SETTINGS table).
Lot Required Flag * PART. LOT_REQD_FL Character 1 198 No The value must be Y (Yes) or N (No). The default is N.
Serial Number Required Flag * PART. SERIAL_REQD_FL Character 1 199 No The value must be Y (Yes) or N (No). The default is N.
Last Assigned Lot ID * PART. LAST_LOT_ID Character 20 200 - 219 No The default is null.
Last Serial ID * PART. LAST_SERIAL_ID Character 20 220 - 239 No The default is null.
Shelf Life in Days * PART.SHELF_ LIFE_ DAYS_NO Integer (maximum input file length - 4) 240 - 243 No The default is0. The maximum value is 9999. No embedded decimals or commas are allowed.
Sales Order Configuration Lot Required * PART.SO_CFG_ LOT_ REQD_FL Character 1 244 No The value must be Y (Yes) or N (No). The default is N.
Sales Order Configuration Serial Required * PART.SO_CFG_ SER_ REQD_FL Character 1 245 No The value must be Y (Yes) or N (No). The default is N.
Weight * PART. WEIGHT_NO Integer 2 (maximum input file length - 4) 246 - 249 No The default is0. The maximum value is 9999. No embedded decimals or commas are allowed.
Order Policy Type * PART.S_ORD_ POLICY_ TYPE Character 1 250 No The value must be D(Discrete Lot Size), M (Min/Mult Lot Size), O(Order To Point), P (Period), or R(Reorder Point). The default is D, or the Default Order Policy from the Configure Product Definition Settings screen (ITEM_SETTINGS. S_ORD_POLICY_TYPE).
Period Order Days *PART.PD_ORD_ DAYS_ NO Integer  (maximum input file length - 4) 251 - 254 No. Required if the Order Policy Type is P. The default is0. The maximum value is 9999. No embedded decimals or commas are allowed. The value must be greater than zero only if the Order Policy Type isP (Period).
Minimum Lot Size Quantity *PART.MIN_LOT_ SIZE_ QTY Decimal 14.4 (Maximum input file length - 11) 255 - 265 No. Required if the Order Policy Typeis R. If the Order Policy Type isM, you must supply either the Minimum Lot Size Quantity or the Multiple Lot Size Quantity. The default is 0 if the Order Policy Type isP (Period). The value must be greater than zero if the Order Policy Type isR (Reorder Point) or M (Min/Mult Lot Size). If it is M, either the Minimum Lot Size Quantity or the Multiple Lot Size Quantitymust be greater than zero. The input file format is 999999.9999(maximum 11 characters including decimal point). No embedded commas are allowed.

The value cannot be greater than zero, if the Order Policy Type isD (Discrete Lot Size), P (Period), or O(Order to Point).

Multiple Lot Size Quantity *PART.MULT_LOT_SIZE_QTY Decimal 14.4 (Maximum input file length - 11) 266 - 276 No. If the Order Policy Type isM, you must supply either the Minimum Lot Size Quantity or the Multiple Lot Size Quantity. The default is 0, if the Order Policy Type isP (Period). The value must be greater than zero if the Order Policy Type isM (Min/Mult Lot Size).  If it isM, either the Minimum Lot Size Quantity or the Multiple Lot Size Quantitymust be greater than zero. The input file format is 999999.9999(maximum 11 characters including decimal point). No embedded commas are allowed.

The value cannot be greater than zero, if the Order Policy Type isR (Reorder Point), P (Period), D(Discrete Lot Size), orO (Order to Point).

Maximum Lot Size Quantity *PART.MAX_LOT_ SIZE_ QTY Decimal 14.4 (Maximum input file length - 11) 277 - 287 No The default is 0. Must not be less than zero. If not, set to 0.
Order Unit of Measure *ITEM_UM. UM_CD Character 3 288 - 290 No This is added to ITEM_UM table with a U/M Type (S_UM_TYPE) of  O (Order). If a value is not supplied, the default U/M is used.
Sales Unit Of Measure *ITEM_UM. UM_CD Character 3 291 - 293 No This is added to ITEM_UM table with a U/M Type (S_UM_TYPE) of  S (Sales). If a value is not supplied, the default U/M is used.
Inventory Flag * PART. INVT_FL Character 1 294 No The value must be Y (Yes) or N (No). The default is Y.
Default Warehouse ID * PART. DFLT_WHSE_ID Character 8 295 - 302 No If provided, the value must exist in the Warehouses table, and you must provide a Default Inventory Location. Both warehouses and inventory locations are maintained in the Inventory Controls menu. The default is null. If the Separate Items by Company check box is selected on the Configure Product Definition Settings screen, this field's value must exist for your company ID.
Default Inventory Location * PART. DFLT_INVT_LOC_ ID Character 15 303 - 317 No If a Default Inventory Location is provided, a Default Warehouse ID must be provided. Default inventory locations are assigned to warehouse IDs on the Manage Locations screen in the Inventory Controls menu. The default is null.
Floor Stock Flag * PART.FLOOR_ STOCK_ FL Character 1 318 No The value must be Y (Yes) or N (No). The default is N.
Common Stock Flag * PART.COMMON_ STK_ FL Character 1 319 No The value must be Y (Yes) or N (No). The default is N.
Backflush Flag * PART. BKFLSH_FL Character 1 320 No The value must be Y (Yes) or N (No). The default is N.
Default Comp Lead Time Offset Days *DFLT_LT_ OFFSET_NO Integer (maximum input file length - 4) 321 - 324 No The default is0. The maximum value is 9999. No embedded decimals or commas are allowed.
Scrap Factor Percentage Rate * PART.SCRAP_ FACT_ PCT_RT Decimal 10.8 (maximum input file length - 4) 325 - 328 No The default is0. The input file format is 9.99 (maximum four characters including the decimal point). The maximum value is 1.00, which indicates 100%.
Manufacturing BOM Yield Percentage Rate * PART. YIELD_PCT_RT Decimal 10.8 (maximum input file length - 4) 329 - 332 No The default is1, which indicates 100%. The input file format is 9.99 (maximum four characters including the decimal point).  Maximum value is 1.00, which indicates 100%.
ABC Classification Code *PART. ABC_CLASSIF_CD Character 1 333 No If a value is provided, it must exist in the ABC Classification table (code is system-defined; assigned to the part on the Manage Parts screen). The default is null. If the Separate Items by Company check box is selected on the Configure Product Definition Settings screen, this field's value must exist for your company ID.
Count Frequency * PART.COUNT_ FREQ_NO Integer (maximum input file length - 3) 334 - 336 No The default is0. The maximum value is 999. No decimals allowed.
Count Tolerance Percentage Rate * PART.COUNT_ TOL_ PCT_RT Decimal 10.8 (maximum input file length - 4) 337 - 340 No The default is0. The input file format is 9.99 (maximum four characters including the decimal point). The maximum value is 1.00, which indicates 100%.
Item Note * ITEM. ITEM_NT Character 254 341 - 594 No The default is a space.
Always Quote Flag * PART. ALWAYS_QT_FL Character 1 595 No The value must be Y (Yes) or N (No). The default is N.
Active Flag *ITEM. ACTIVE_FL Character 1 596 No The value must be Y (Yes) or N (No). The default is N.
Inspection Type *INSP_TYPE Character 1 597 No The default is a space.
Item Added Date *ITEM_ADDED_DT Date (maximum input file length - 10) 598 - 607 No The value must be valid date format YYYY-MM-DD. The default is the system date.
Last Order Date * ITEM. LAST_ORD_DATE Date (maximum input file length - 10) 608 - 617 No The value must be valid date format YYYY-MM-DD. The system date defaults if the Item Type is S (Service) or G (Good); it defaults as null if the Item Type is P (Part).
Last Order ID * ITEM. LAST_ORD_ID Character 10 618 - 627 No The default is a space.
Status Change Date * PART.STATUS_ CHNG_ DT Date (maximum input file length - 10) 628 - 637 No The value must be valid date format YYYY-MM-DD. The default is the system date.
Effectivity Type *PART. S_EFFECT_TYPE Character 1 638 No The value must be a space (none), D (Date), or S (Serial).  The default is a space.
User ID last modified by *ITEM, PART, ITEM_ UM.MODIFIED_BY Character 18 639 - 656 No If a value is not provided, the MODIFIED_BY column is updated using the current user ID as the default.
Common Stock Inventory Abbreviation * PART.CS_INVT_ ABBRV_CD Character 20 657 - 662 No Default if input file's Common Stock Flag is Y, set to value in Default Common Stock Inv Abbrev field on the Configure Production Control Settings screen. Must be a valid Inventory Abbreviation code (from the Manage Inventory Projects screen).

Note: For fixed input file, only six (6) characters are allowed, but for delimited input file, 20 characters are allowed.

Last Change Notice * PART.LST_ CHNG_ NOTICE_ID Character 15 663 - 677 No
ITEM. S_ITEM_TYPE Character 1 No P (Part), S (Service), or G (Good)
*PART.NET_CHG_FL Character 1 No This is set to Y (Yes) for new records and updates.
*ITEM,PART, ITEM_UM.ROWVERSION Integer 2 No This is system-generated. Default value is 0 and increments by 1, when updated.
*TIME_STAMP Timestamp No This is a system date.
*ITEM,TOT_LT_DAYS_NO Integer 4 No The default value is 0. If ITEM_LT lines are included in the days provided on the lines, these are added and stored in this column.
ITEM_KEY_, PART_KEY Integer 4 No For new records, this is system-generated and the value provided is assigned as ITEM_KEY for all rows linked to this ITEM_ID/ITEM_RVSN_ID. For existing records, the value is retrieved using ITEM_ID and ITEM_RVSN_ID.
PART.ASY_CHNG_USER_ID Character 12 No This defaults to NULL. Always ignore this field in the input file. If new part, set to NULL, otherwise, do not modify the column.
PART.ASY_ENTR_DTT Timestamp (Maximum input file length is 19.) No This defaults to NULL. Always ignore this field in the input file. If new part, set to NULL, otherwise, do not modify the column.
PART.ASY_ENTR_USER_ID Character 12 No This defaults to NULL. Always ignore this field in the input file. If new part, set to NULL, otherwise, do not modify the column.
PART.ASY_LAST_CHNG_DTT Timestamp (Maximum input file length is 19.) No This defaults to NULL. Always ignore this field in the input file. If new part, set to NULL, otherwise, do not modify the column.
PART_ASY_LLCD_UPDATE_FL Character 1 No This defaults to N. Always ignore this field in the input file. If new part, set to N, otherwise, do not modify the column.
ITEM_LST_RVSN_ID Character 3 No Set to input file PART_RVSN_ID value.

The fixed length file format accepts 3 characters while delimited file format accepts up to 10 characters.

PART_ASY_NOTES Character 254 No The default is a space.
PART_BOM_EXIST_FL Character 1 No This defaults to N. Always ignore this field in the input file. If new part, set to N, otherwise, do not modify the column.
PART.S_ASY_REL_CD Character 1 No This defaults to N. Always ignore this field in the input file. If new part, set to N, otherwise, do not modify the column.
PART.S_BOM_CHNG_CD Character 1 No This defaults to N. Always ignore this field in the input file. If new part, set to N, otherwise, do not modify the column.
PART.LAST_ALT_SEQ_NO Integer 2 (Maximum input file length is 4.) No The default value is 0. Always ignore this field in the input file. If new part, set to 0, otherwise do not modify the column.
PART.LOW_LVL_CD_NO Integer 2 (Maximum input file length is 4.) No The default value is 0. Always ignore this field in the input file. If new part, set to 0, otherwise do not modify the column.
ECN_PENDING_FL Character 1 No The value must be Y (Yes) or N (No). The default is N.
COMPANY_ID Character 10 No If both item and part, and ITEM_SETTINGS_CORP.ITEMS_BY_COMP_FL=Y, set this to user's company, otherwise, set to 1.
MPS Time Fence Days * PART.MPS_FENCE_CD Integer (maximum input file length) 678 - 681 No The default is0. The maximum value is 9999. No embedded decimals or commas are allowed.
SO Time Fence Days * PART.SO_FENCE_CD Integer(maximum input file length) 682 - 685 No The default is0. The maximum value is 9999. No embedded decimals or commas are allowed.
Cumulative Lead Time Days * PART. CUM_LT_DAYS_NO Integer(maximum input file length) 686-689 No The default is0. The maximum value is 9999. No embedded decimals or commas are allowed.
Weight – Long * PART. WEIGHT_NO Decimal14, 4 690 - 704 No The default is 0.
Industry Class Code * IND_CLASS_CD Character 8 705 - 712 No Default from Commodity Codes (COMM table. 

If this is not null, it must exist in the Industry Class table.

UPC Code *UPC_CD Character 14 713 - 726 No The default is NULL.
Vendor Restriction *S_VEND_RESTRICT_CD Character 1 No Values are A=Approved, S=Assigned, N=No Restriction. If left blank, the default value is retrieved from ITEM_SETTINGS.S_VEND_RESTRICT_CD.
Substitute Part Planning Order PART.S_PLAN_ORDER_CD Character 3 727 No. Required if the MRP Settings Use Part Plan check box is selected. Values must be OP (Original Part Across Projects), OS (Original/Substitute Part in Project Demand), SP (Substitute Part Across Projects), SO (Substitute /Original Part in Project Demand).
NAICS Code NAICS_CD Character 15 No This defaults from the COMM table (but only if currently active (OPP_NAICS_CODES.SHOW_LOOKUP_FL = Y)). This must exist in OPP_NAICS_CODES table if not null.

* Indicates columns that may be may be updated when changing an existing record with this preprocessor.

Order Policy Type Summary

The table below lists the actions, values, and field requirements for each Order Policy Type. The fields in the Order Policy group box on the Planning Details subtask of the Manage Parts screen are active and display all applicable Order Policy data for the part based on the Order Policy Type.

The Costpoint column names for the fields listed in the table are as follows (for Parts only):

  • Max: Maximum Lot Size Quantity (MAX_LOT_SIZE_ QTY)
  • Minimum: Minimum Lot Size Quantity (MIN_LOT_SIZE_QTY).
  • Multiple: Multiple Lot Size Quantity (MULT_LOT_SIZE_QTY)
  • Period Order Days (PD_ORD_DAYS_NO)
Order Policy Type Active Field(s) Must be greater than zero (0) Cannot be greater than zero (0)
Discrete Lot Size (D) Max (Max must be greater than or equal to zero) Period Order Days
Minimum
Multiple
Min/Mult Lot Size (M) Minimum Minimum or Multiple Period Order Days
Multiple
Max (Max must be greater than or equal to zero)
Order To Point (O) Max (Max must be greater than or equal to zero) Period Order Days
Minimum
Multiple
Period (P) Max (Max must be greater than or equal to zero) Minimum**
Period Order Days Period Order Days Multiple**
Reorder Point (R) Max (Max must be greater than or equal to zero) Multiple
Minimum* Minimum* Period Order Days

* The Minimum field displays as Reorder (that is, the Reorder Point Quantity) on the Planning Details subtask of the Manage Parts screen.

** The default is zero (0).