BOM Interfaces Tables
1. BOM_BILL_OF_MTLS_INTERFACE
2. BOM_INVENTORY_COMPS_INTERFACE
3. BOM_REF_DESGS_INTERFACE
4. BOM_SUB_COMPS_INTERFACE
5. MTL_ITEM_REVISIONS_INTERFACE
Bills of Material Tables
1. bom_bill_of_materials
2. bom_inventory_components
3. bom_reference_designators
4. bom_substitute_components
5. mtl_item_revisions
Oracle Routing Interface Tables
1. BOM_OP_ROUTINGS_INTERFACE
2. BOM_OP_SEQUENCES_INTERFACE
3. BOM_OP_RESOURCES_INTERFACE
4. MTL_RTG_ITEM_REVS_INTERFACE
Oracle Routing tables
1. bom_operational_routings
2. bom_operation_sequences
3. bom_operation_resources
Procedure for Interfacing the BOM & Routing
Import Program:
Once you load the data into the interface tables, you can launch the Bill and Routing Interface program from the Import Bills and Routings form in Oracle Bills of Material or Oracle Engineering. This program
assigns values, validates the data you include, and then imports the new bills of material (BOMs).
Item Revision: You can optionally create an item revision when you import a BOM, by inserting a value for a revision at the same time you insert your BOM data.
If you enter a value in the REVISION column of the BOM_BILL_OF_MTLS_INTERFACE table, the Bill and Routing Interface program inserts a row into the MTL_ITEM_REVISIONS_INTERFACE table. In order to assign multiple item revisions, it is better to insert data directly into the MTL_ITEM_REVISIONS_INTERFACE table.
Status Flags: The column PROCESS_FLAG indicates the current state of processing for a row in the interface table. All inserted rows must have the PROCESS_FLAG set to 1.
After populating the data into the interface tables, run the Bill and Routing Interface program. The Program assigns and validates all rows with a status of 1 (Pending), and then imports them into the production tables. If the assign or validate procedure fails for a row, the program sets the PROCESS_FLAG to 3 (Assign/Validation Failed) for that row.
The successful rows continue through the process of importing into the production tables. If a row fails on import, the program assigns a value of 4 (Import Failed) to the PROCESS_FLAG. Successfully imported rows have a PROCESS_FLAG value of 7 (Import Succeeded).
TRANSACTION AND REQUEST ID'S
The Bill and Routing Interface program automatically updates the TRANSACTION_ID and REQUEST_ID columns in each of the interface tables. The column TRANSACION_ID stores a unique id for each row in the interface table and the REQUEST_ID column stores the concurrent request id number.
IMPORT CONSIDERATIONS -clause
Even though you can import bills and routings simultaneously, all routing operations must exist before you can assign a component to an operation. If a routing does not exist, you cannot assign an operation
sequence to a component on a BOM.
You can simultaneously import primary and alternate BOMs. Since the Bill and Routing Interface program validates data the same way the Define Routing or Define Engineering Routing form verifies data, you cannot define an alternate bill if the primary bill does not exist. Therefore, you should import primary
BOMs before importing alternate BOMs. If the program tries to validate an alternate bill before validating the primary bill, the record fails.
In order to import a BOM with components, you need to populate below tables:
BOM_BILL_OF_MTLS_INTERFACE
BOM_INVENTORY_COMPS_INTERFACE
With these two tables, you can create BOM header information and assign component details.
BOM_BILL_OF_MTLS_INTERFACE TABLE.
Mandatory columns to be populated for BOM_BILL_OF_MTLS_INTERFACE.
You must always enter values for the following required columns when you insert rows into the BOM_BILL_OF_MTLS_INTERFACE table:
ASSEMBLY_ITEM_ID
ORGANIZATION_ID
ASSEMBLY_TYPE
PROCESS_FLAG
If you create an alternate BOM, you must also enter a value in the ALTERNATE_BOM_DESIGNATOR column.
If the BOM you import references a common BOM, you must enter a value in the COMMON_ORGANIZATION_ID and COMMON_ASSEMBLY_ITEM_ID columns, or you can enter a value in the COMMON_BILL_SEQUENCE_ID column.
If the bill does not reference a common bill, the Bill and Routing interface program defaults the value of the BILL_SEQUENCE_ID for the COMMON_BILL_SEQUENCE_ID.
You can specify in the ASSEMBLY_TYPE column whether the BOM is a manufacturing BOM or an engineering BOM. If you do not include a value for this column, Oracle Bills of Material defaults a value of 1 (manufacturing), and creates a manufacturing BOM. To create an engineering bill, you must enter a value of 2 (engineering) for the ASSEMBLY_TYPE column.
For each new row you insert into the BOM_BILL_OF_MTLS_INTERFACE table, you should set the PROCESS_FLAG to 1 (Pending).
DERIVED/DEFAULTED VALUES FOR BOM_BILL_OF_MTLS_INTERFACE.
The Bill and Routing Interface program derives or defaults most of the data required to create a manufacturing or an engineering BOM. The Bill and Routing Interface program derives or defaults the columns using the same logic as the Define Bill of Material form or the Define Engineering Bill of Material form. When you populate a column in the interface table, the program imports the row with the data you included and does not default a value.
BOM_BILL_OF_MTLS_INTERFACE Derived or Defaulted Value
ASSEMBLY_ITEM_ID From ITEM_NUMBER
ORGANIZATION_ID From ORGANIZATION_CODE
LAST_UPDATE_DATE System Date
LAST_UPDATE_BY User_id
CREATION_DATE System Date
CREATED_BY User_id
COMMON_ASSEMBLY_ITEM_ID From COMMON_ITEM_NUMBER
ASSEMBLY_TYPE 1
COMMON_BILL_SEQUENCE_ID Sequence BOM_INVENTORY_COMPONENTS_S
COMMON_ORGANIZATION_ID From COMMON_ORG_CODE
REQUEST_ID From FND_CONCURRENT_REQUESTS
BOM_INVENTORY_COMPS_INTERFACE TABLE
Mandatory COLUMNS FOR BOM_INVENTORY_COMPS_INTERFACE TABLE.
Each imported record must have a value for the following columns:
PROCESS_FLAG
COMPONENT_ITEM_ID
COMPONENT_SEQUENCE_ID
OPERATION_SEQ_NUM
EFFECTIVITY_DATE
BILL_SEQUENCE_ID
You must also specify a value in the ALTERNATE_BOM_DESIGNATOR column if you assign components to an alternate BOM and have not entered a value for the BILL_SEQUENCE_ID column.
When you insert rows into BOM_INVENTORY_COMPS_INTERFACE, you must set the PROCESS_FLAG to 1 (Pending) for the Bill and Routing Interface program to process the record.
DERIVED/DEFAULTED COLUMN VALUES FOR BOM_INVENTORY_COMPS_INTERFACE
The Bill and Routing Interface program derives or defaults most of the data required to assign components to a BOM. You can optionally include a value for derived or defaulted columns, as well as data for any of the other columns in the interface. The interface program uses the same logic to derive or default column values in the BOM_INVENTORY_COMPS_INTERFACE table as it does in the BOM_BILL_OF_MTLS_INTERFACE table. When you populate a column in the interface table, the program imports the row with the data you included and does not default a value. However, if you do not enter data in a derived or defaulted column, the program automatically imports the row with the derived or defaulted value.
BOM_BILLS_OF_MTLS_INTERFACE Derived or Defaulted Value
COMPONENT_ITEM_ID From COMPONENT_ITEM_NUMBER
LAST_UPDATE_DATE System Date
LAST_UPDATE_BY User_id
CREATION_DATE System Date
CREATED_BY User_id
ITEM_NUM 1
COMPONENT_QUANTITY 1
COMPONENT_YIELD_FACTOR 1
PLANNING_FACTOR 100
QUANTITY_RELATED 2
SO_BASIS 2
OPTIONAL 2
MUTUALLY_EXCLUSIVE_OPTIONS 2
INCLUDE_IN_COST_ROLLUP 1
CHECK_ATP 2
REQUIRED_TO_SHIP 2
REQUIRED_FOR_REVENUE 2
INCLUDE_ON_SHIP_DOC 2
COMPONENT_SEQUENCE_ID Sequence, BOM_INVENTORY_COMPONENTS_S
BILL_SEQUENCE_ID From BOM_BILL_OF_MTLS_INTERFACE or
BOM_BILL_OF_MATERIALS
WIP_SUPPLY_TYPE 1
SUPPLY_LOCATOR_ID From LOCATION_NAME
ASSEMBLY_ITEM_ID From ASSEMBLY_ITEM_NUMBER
ORGANIZATION_ID From ORGANIZATION_CODE
SUBSTITUTE_COMP_ID From SUBSTITUTE_COMP_NUMBER
REQUEST_ID From FND_CONCURRENT_REQUEST
IMPORTING ADDITIONAL BILL INFORMATION
Additional columns (Optional): If you want to assign standard comments, reference designators, and substitute components to your BOM, you need to populate:
BOM_ASSY_COMMENTS_INTERFACE
BOM_REF_DESGS_INTERFACE
BOM_SUB_COMPS_INTERFACE
PROCESS_FLAG
When you create BOMs and assign components using the Bill and Routing Interface program, you can also import additional BOM information using three different interface tables. You can import standard comments for each BOM using the BOM_ASSY_COMMENTS_INTERFACE table. You can assign component reference designators using the BOM_REF_DESGS_INTERFACE table and substitute components using the BOM_SUB_COMPS_INTERFACE table.
You can assign standard comments to any bill of material type. However, only standard components assigned to standard, model, and option class BOMs can have reference designators and substitute components.
If you insert data in the BOM_REF_DESGS_INTERFACE or BOM_SUB_COMPS_INTERFACE
tables for a planning bill, the Bill and Routing Interface program fails to import the record and sets the PROCESS_FLAG to 3 (Assign/Validation Failed).
BOM_ASSY_COMMENTS_INTERFACE TABLE
Mandatory COLUMNS FOR THE BOM_ASSY_COMMENTS_INTERFACE TABLE.
To import data into the BOM_ASSY_COMMENTS_INTERFACE table, you must assign a value to the following columns:
STANDARD_REMARKS_DESIGNATOR
BILL_SEQUENCE_ID
PROCESS_FLAG
DERIVED/DEFAULTED COLUMN VALUES FOR BOM_ASSY_COMMENTS_INTERFACE.
After inserting data into the BOM_ASSY_COMMENTS_INTERFACE table, the Bill and Routing Interface program derives the value for the BILL_SEQUENCE_ID column if you assign values to the columns:
ASSEMBLY_ITEM_ID
ORGANIZATION_ID
ALTERNATE_BOM_DESIGNATOR
BOM_REF_DESGS_INTERFACE AND BOM_SUBS_COMPS_INTERFACE TABLES
Mandatory COLUMNS FOR BOM_REF_DESGS_INTERFACE TABLE.
You can only import data into the BOM_REF_DESGS_INTERFACE table for standard components assigned to standard, model and option class BOMs. You must assign values to the following columns:
COMPONENT_REFERENCE_DESIGNATOR
COMPONENT_SEQUENCE_ID
PROCESS_FLAG
Mandatory COLUMNS FOR BOM_SUBS_COMPS_INTERFACE TABLE.
You can only import data into the BOM_SUBS_COMPS_INTERFACE table for standard components assigned to standard, model and option class BOMs. You must assign values to the following columns:
SUBSTITUTE_COMPONENT_ID
SUBSTITUTE_ITEM_QUANTITY
COMPONENT_SEQUENCE_ID
PROCESS_FLAG
DERIVED/DEFAULT VALUES FOR BOM_REF_DESGS_INTERFACE AND
BOM_SUB_COMPS_INTERFACE.
After inserting data into the BOM_REF_DESGS_INTERFACE or BOM_SUB_COMPS_INTERFACE tables, the Bill and Routing Interface program derives the value for the BILL_SEQUENCE_ID column if you assign values to the following columns:
ASSEMBLY_ITEM_ID
ORGANIZATION_ID
ALTERNATE_BOM_DESIGNATOR
The program also assigns a value for the COMPONENT_SEQUENCE_ID column if you enter values into the following columns:
BILL_SEQUENCE_ID
COMPONENT_ITEM_ID
OPERATION_SEQ_NUM
EFFECTIIVITY_DATE
VALIDATING INTERFACE TABLE ROWS
After you load the BOM and component data, the Bill and Routing Interface program validates the required data for the six interface tables. BOM validation insures that each row has an included or defaulted value for all the required columns and verifies the same way as the Define Bill of Material form
and the Define Engineering Bill of Material form validate manually entered bills. For example, you cannot import a standard bill and assign model, option class or planning items as components.
If the Bill and Routing Interface program cannot assign a value to a row or validate that row, the program sets the PROCESS_FLAG to 3 (Assign/Validation Failed) and inserts a row in the MTL_INTERFACE_ERRORS table.
To identify the error message for a failed row, the program automatically populates the UNIQUE_ID column in the error interface table with the same value as the TRANSACTION_ID value. Each error has a value for the MESSAGE_NAME and REQUEST_ID columns in the error interface table. The MESSAGE_NAME column corresponds to messages stored in the Oracle Application Message Dictionary.
The REQUEST_ID column stores the concurrent request id. If the program detects any internal database error, the program stores the internal error in the MESSAGE_NAME column and stores the specific database error message in the ERROR_MESSAGE column.
If you import a BOM with multiple components and one of the components fails
validation, then the bill will be created without the failed component. However, if the row in the BOM_BILL_OF_MTLS_INTERFACE table fails, the BOM and all of its details are not imported.
CORRECTING FAILED ROWS
You can review and report rows in the interface tables using SQL*Plus or any custom report you develop. Since all rows in the interface table have a value for PROCESS_FLAG, you can easily identify records that are successfully imported into Oracle Bill of Material and Oracle Engineering, or records that failed validation or import. You can also identify individual records by the unique value for the TRANSACTION_ID column.
You can update any row from the interface tables using SQL*Plus. If you update a row to resolve invalid data, you must set the PROCESS_FLAG to 1(Pending) for that row to re-process.
If you delete a failed row and insert a replacement row, you should set the PROCESS_FLAG to 1 (Pending) for the new row. When you resubmit the Bill and Routing Interface program, all rows pending validation are processed.
Updating a Bill or Product Family
To update a bill or family, you must enter “Update” in the TRANSACTION_TYPE column.
To update a column to a NULL value, you must enter the following values:
Column Data Type Required value to create NULL column
For Char columns char (12)
For Date columns TO_DATE (’1’,’j’)
For Number columns 9.99E125
1. BOM_BILL_OF_MTLS_INTERFACE
2. BOM_INVENTORY_COMPS_INTERFACE
3. BOM_REF_DESGS_INTERFACE
4. BOM_SUB_COMPS_INTERFACE
5. MTL_ITEM_REVISIONS_INTERFACE
Bills of Material Tables
1. bom_bill_of_materials
2. bom_inventory_components
3. bom_reference_designators
4. bom_substitute_components
5. mtl_item_revisions
Oracle Routing Interface Tables
1. BOM_OP_ROUTINGS_INTERFACE
2. BOM_OP_SEQUENCES_INTERFACE
3. BOM_OP_RESOURCES_INTERFACE
4. MTL_RTG_ITEM_REVS_INTERFACE
Oracle Routing tables
1. bom_operational_routings
2. bom_operation_sequences
3. bom_operation_resources
Procedure for Interfacing the BOM & Routing
Import Program:
Once you load the data into the interface tables, you can launch the Bill and Routing Interface program from the Import Bills and Routings form in Oracle Bills of Material or Oracle Engineering. This program
assigns values, validates the data you include, and then imports the new bills of material (BOMs).
Item Revision: You can optionally create an item revision when you import a BOM, by inserting a value for a revision at the same time you insert your BOM data.
If you enter a value in the REVISION column of the BOM_BILL_OF_MTLS_INTERFACE table, the Bill and Routing Interface program inserts a row into the MTL_ITEM_REVISIONS_INTERFACE table. In order to assign multiple item revisions, it is better to insert data directly into the MTL_ITEM_REVISIONS_INTERFACE table.
Status Flags: The column PROCESS_FLAG indicates the current state of processing for a row in the interface table. All inserted rows must have the PROCESS_FLAG set to 1.
After populating the data into the interface tables, run the Bill and Routing Interface program. The Program assigns and validates all rows with a status of 1 (Pending), and then imports them into the production tables. If the assign or validate procedure fails for a row, the program sets the PROCESS_FLAG to 3 (Assign/Validation Failed) for that row.
The successful rows continue through the process of importing into the production tables. If a row fails on import, the program assigns a value of 4 (Import Failed) to the PROCESS_FLAG. Successfully imported rows have a PROCESS_FLAG value of 7 (Import Succeeded).
TRANSACTION AND REQUEST ID'S
The Bill and Routing Interface program automatically updates the TRANSACTION_ID and REQUEST_ID columns in each of the interface tables. The column TRANSACION_ID stores a unique id for each row in the interface table and the REQUEST_ID column stores the concurrent request id number.
IMPORT CONSIDERATIONS -clause
Even though you can import bills and routings simultaneously, all routing operations must exist before you can assign a component to an operation. If a routing does not exist, you cannot assign an operation
sequence to a component on a BOM.
You can simultaneously import primary and alternate BOMs. Since the Bill and Routing Interface program validates data the same way the Define Routing or Define Engineering Routing form verifies data, you cannot define an alternate bill if the primary bill does not exist. Therefore, you should import primary
BOMs before importing alternate BOMs. If the program tries to validate an alternate bill before validating the primary bill, the record fails.
In order to import a BOM with components, you need to populate below tables:
BOM_BILL_OF_MTLS_INTERFACE
BOM_INVENTORY_COMPS_INTERFACE
With these two tables, you can create BOM header information and assign component details.
BOM_BILL_OF_MTLS_INTERFACE TABLE.
Mandatory columns to be populated for BOM_BILL_OF_MTLS_INTERFACE.
You must always enter values for the following required columns when you insert rows into the BOM_BILL_OF_MTLS_INTERFACE table:
ASSEMBLY_ITEM_ID
ORGANIZATION_ID
ASSEMBLY_TYPE
PROCESS_FLAG
If you create an alternate BOM, you must also enter a value in the ALTERNATE_BOM_DESIGNATOR column.
If the BOM you import references a common BOM, you must enter a value in the COMMON_ORGANIZATION_ID and COMMON_ASSEMBLY_ITEM_ID columns, or you can enter a value in the COMMON_BILL_SEQUENCE_ID column.
If the bill does not reference a common bill, the Bill and Routing interface program defaults the value of the BILL_SEQUENCE_ID for the COMMON_BILL_SEQUENCE_ID.
You can specify in the ASSEMBLY_TYPE column whether the BOM is a manufacturing BOM or an engineering BOM. If you do not include a value for this column, Oracle Bills of Material defaults a value of 1 (manufacturing), and creates a manufacturing BOM. To create an engineering bill, you must enter a value of 2 (engineering) for the ASSEMBLY_TYPE column.
For each new row you insert into the BOM_BILL_OF_MTLS_INTERFACE table, you should set the PROCESS_FLAG to 1 (Pending).
DERIVED/DEFAULTED VALUES FOR BOM_BILL_OF_MTLS_INTERFACE.
The Bill and Routing Interface program derives or defaults most of the data required to create a manufacturing or an engineering BOM. The Bill and Routing Interface program derives or defaults the columns using the same logic as the Define Bill of Material form or the Define Engineering Bill of Material form. When you populate a column in the interface table, the program imports the row with the data you included and does not default a value.
BOM_BILL_OF_MTLS_INTERFACE Derived or Defaulted Value
ASSEMBLY_ITEM_ID From ITEM_NUMBER
ORGANIZATION_ID From ORGANIZATION_CODE
LAST_UPDATE_DATE System Date
LAST_UPDATE_BY User_id
CREATION_DATE System Date
CREATED_BY User_id
COMMON_ASSEMBLY_ITEM_ID From COMMON_ITEM_NUMBER
ASSEMBLY_TYPE 1
COMMON_BILL_SEQUENCE_ID Sequence BOM_INVENTORY_COMPONENTS_S
COMMON_ORGANIZATION_ID From COMMON_ORG_CODE
REQUEST_ID From FND_CONCURRENT_REQUESTS
BOM_INVENTORY_COMPS_INTERFACE TABLE
Mandatory COLUMNS FOR BOM_INVENTORY_COMPS_INTERFACE TABLE.
Each imported record must have a value for the following columns:
PROCESS_FLAG
COMPONENT_ITEM_ID
COMPONENT_SEQUENCE_ID
OPERATION_SEQ_NUM
EFFECTIVITY_DATE
BILL_SEQUENCE_ID
You must also specify a value in the ALTERNATE_BOM_DESIGNATOR column if you assign components to an alternate BOM and have not entered a value for the BILL_SEQUENCE_ID column.
When you insert rows into BOM_INVENTORY_COMPS_INTERFACE, you must set the PROCESS_FLAG to 1 (Pending) for the Bill and Routing Interface program to process the record.
DERIVED/DEFAULTED COLUMN VALUES FOR BOM_INVENTORY_COMPS_INTERFACE
The Bill and Routing Interface program derives or defaults most of the data required to assign components to a BOM. You can optionally include a value for derived or defaulted columns, as well as data for any of the other columns in the interface. The interface program uses the same logic to derive or default column values in the BOM_INVENTORY_COMPS_INTERFACE table as it does in the BOM_BILL_OF_MTLS_INTERFACE table. When you populate a column in the interface table, the program imports the row with the data you included and does not default a value. However, if you do not enter data in a derived or defaulted column, the program automatically imports the row with the derived or defaulted value.
BOM_BILLS_OF_MTLS_INTERFACE Derived or Defaulted Value
COMPONENT_ITEM_ID From COMPONENT_ITEM_NUMBER
LAST_UPDATE_DATE System Date
LAST_UPDATE_BY User_id
CREATION_DATE System Date
CREATED_BY User_id
ITEM_NUM 1
COMPONENT_QUANTITY 1
COMPONENT_YIELD_FACTOR 1
PLANNING_FACTOR 100
QUANTITY_RELATED 2
SO_BASIS 2
OPTIONAL 2
MUTUALLY_EXCLUSIVE_OPTIONS 2
INCLUDE_IN_COST_ROLLUP 1
CHECK_ATP 2
REQUIRED_TO_SHIP 2
REQUIRED_FOR_REVENUE 2
INCLUDE_ON_SHIP_DOC 2
COMPONENT_SEQUENCE_ID Sequence, BOM_INVENTORY_COMPONENTS_S
BILL_SEQUENCE_ID From BOM_BILL_OF_MTLS_INTERFACE or
BOM_BILL_OF_MATERIALS
WIP_SUPPLY_TYPE 1
SUPPLY_LOCATOR_ID From LOCATION_NAME
ASSEMBLY_ITEM_ID From ASSEMBLY_ITEM_NUMBER
ORGANIZATION_ID From ORGANIZATION_CODE
SUBSTITUTE_COMP_ID From SUBSTITUTE_COMP_NUMBER
REQUEST_ID From FND_CONCURRENT_REQUEST
IMPORTING ADDITIONAL BILL INFORMATION
Additional columns (Optional): If you want to assign standard comments, reference designators, and substitute components to your BOM, you need to populate:
BOM_ASSY_COMMENTS_INTERFACE
BOM_REF_DESGS_INTERFACE
BOM_SUB_COMPS_INTERFACE
PROCESS_FLAG
When you create BOMs and assign components using the Bill and Routing Interface program, you can also import additional BOM information using three different interface tables. You can import standard comments for each BOM using the BOM_ASSY_COMMENTS_INTERFACE table. You can assign component reference designators using the BOM_REF_DESGS_INTERFACE table and substitute components using the BOM_SUB_COMPS_INTERFACE table.
You can assign standard comments to any bill of material type. However, only standard components assigned to standard, model, and option class BOMs can have reference designators and substitute components.
If you insert data in the BOM_REF_DESGS_INTERFACE or BOM_SUB_COMPS_INTERFACE
tables for a planning bill, the Bill and Routing Interface program fails to import the record and sets the PROCESS_FLAG to 3 (Assign/Validation Failed).
BOM_ASSY_COMMENTS_INTERFACE TABLE
Mandatory COLUMNS FOR THE BOM_ASSY_COMMENTS_INTERFACE TABLE.
To import data into the BOM_ASSY_COMMENTS_INTERFACE table, you must assign a value to the following columns:
STANDARD_REMARKS_DESIGNATOR
BILL_SEQUENCE_ID
PROCESS_FLAG
DERIVED/DEFAULTED COLUMN VALUES FOR BOM_ASSY_COMMENTS_INTERFACE.
After inserting data into the BOM_ASSY_COMMENTS_INTERFACE table, the Bill and Routing Interface program derives the value for the BILL_SEQUENCE_ID column if you assign values to the columns:
ASSEMBLY_ITEM_ID
ORGANIZATION_ID
ALTERNATE_BOM_DESIGNATOR
BOM_REF_DESGS_INTERFACE AND BOM_SUBS_COMPS_INTERFACE TABLES
Mandatory COLUMNS FOR BOM_REF_DESGS_INTERFACE TABLE.
You can only import data into the BOM_REF_DESGS_INTERFACE table for standard components assigned to standard, model and option class BOMs. You must assign values to the following columns:
COMPONENT_REFERENCE_DESIGNATOR
COMPONENT_SEQUENCE_ID
PROCESS_FLAG
Mandatory COLUMNS FOR BOM_SUBS_COMPS_INTERFACE TABLE.
You can only import data into the BOM_SUBS_COMPS_INTERFACE table for standard components assigned to standard, model and option class BOMs. You must assign values to the following columns:
SUBSTITUTE_COMPONENT_ID
SUBSTITUTE_ITEM_QUANTITY
COMPONENT_SEQUENCE_ID
PROCESS_FLAG
DERIVED/DEFAULT VALUES FOR BOM_REF_DESGS_INTERFACE AND
BOM_SUB_COMPS_INTERFACE.
After inserting data into the BOM_REF_DESGS_INTERFACE or BOM_SUB_COMPS_INTERFACE tables, the Bill and Routing Interface program derives the value for the BILL_SEQUENCE_ID column if you assign values to the following columns:
ASSEMBLY_ITEM_ID
ORGANIZATION_ID
ALTERNATE_BOM_DESIGNATOR
The program also assigns a value for the COMPONENT_SEQUENCE_ID column if you enter values into the following columns:
BILL_SEQUENCE_ID
COMPONENT_ITEM_ID
OPERATION_SEQ_NUM
EFFECTIIVITY_DATE
VALIDATING INTERFACE TABLE ROWS
After you load the BOM and component data, the Bill and Routing Interface program validates the required data for the six interface tables. BOM validation insures that each row has an included or defaulted value for all the required columns and verifies the same way as the Define Bill of Material form
and the Define Engineering Bill of Material form validate manually entered bills. For example, you cannot import a standard bill and assign model, option class or planning items as components.
If the Bill and Routing Interface program cannot assign a value to a row or validate that row, the program sets the PROCESS_FLAG to 3 (Assign/Validation Failed) and inserts a row in the MTL_INTERFACE_ERRORS table.
To identify the error message for a failed row, the program automatically populates the UNIQUE_ID column in the error interface table with the same value as the TRANSACTION_ID value. Each error has a value for the MESSAGE_NAME and REQUEST_ID columns in the error interface table. The MESSAGE_NAME column corresponds to messages stored in the Oracle Application Message Dictionary.
The REQUEST_ID column stores the concurrent request id. If the program detects any internal database error, the program stores the internal error in the MESSAGE_NAME column and stores the specific database error message in the ERROR_MESSAGE column.
If you import a BOM with multiple components and one of the components fails
validation, then the bill will be created without the failed component. However, if the row in the BOM_BILL_OF_MTLS_INTERFACE table fails, the BOM and all of its details are not imported.
CORRECTING FAILED ROWS
You can review and report rows in the interface tables using SQL*Plus or any custom report you develop. Since all rows in the interface table have a value for PROCESS_FLAG, you can easily identify records that are successfully imported into Oracle Bill of Material and Oracle Engineering, or records that failed validation or import. You can also identify individual records by the unique value for the TRANSACTION_ID column.
You can update any row from the interface tables using SQL*Plus. If you update a row to resolve invalid data, you must set the PROCESS_FLAG to 1(Pending) for that row to re-process.
If you delete a failed row and insert a replacement row, you should set the PROCESS_FLAG to 1 (Pending) for the new row. When you resubmit the Bill and Routing Interface program, all rows pending validation are processed.
Updating a Bill or Product Family
To update a bill or family, you must enter “Update” in the TRANSACTION_TYPE column.
To update a column to a NULL value, you must enter the following values:
Column Data Type Required value to create NULL column
For Char columns char (12)
For Date columns TO_DATE (’1’,’j’)
For Number columns 9.99E125
