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).