BOM Open Interfaces

BOM Open Interfaces - An Oracle White Paper [185958.1]

After populating the interface tables, you can run the Bill and Routing Interface program.
The column, PROCESS_FLAG, indicates the current state of processing for a row in the interface table. Possible values for the column include:

1 - Pending 

2 - Assigned Succeeded 

3 - Assign/Validation Failed 

4 - Validation Succeeded 

7 - Import Succeeded 

You can use the following sql scripts to check for errors which will be located in the MTL_INTERFACE_ERRORS table to help resolve problems.

select table_name, column_name, organization_id, message_name, error_message
from mtl_interface_errors
where request_id = xxxx; (xxxx is the concurrent request id)
or
select table_name, column_name, organization_id, message_name, error_message
from mtl_interface_errors
where transaction_id = xxxx; (xxxx is the transaction id)

BOM_BILL_OF_MTLS_INTERFACE TABLE

This table temporarily stores bills of material header information. Each row in this table represents a unique Manufacturing or Engineering bill and is identified by the BILL_SEQUENCE_ID.

If you are creating an alternate bill of material you must also enter a value in the ALTERNATE_BOM_DESIGNATOR column.

If you enter something into REVISION, Oracle Bills of Materials will insert a record into
MTL_ITEM_REVISIONS.

If the bill you import references a common bill of material, 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.

The Bill and Routing Interface program derives the COMMON_BILL_SEQUENCE_ID from the
COMMON_ITEM_ID, COMMON_ORGANIZATION_ID, AND ALTERNATE_BOM_DESIGNATOR.

The required fields to create a Bill in the BOM_BILL_OF_MTLS_INTERFACE table are:

• PROCESS_FLAG
• ORGANIZATION_ID
• ASSEMBLY_ITEM_ID
• ASSEMBLY_TYPE
• TRANSACTION TYPE
• The PROCESS_FLAG needs to be 1 for pending.
• The ASSEMBLY_TYPE of the BOM; is 1 for manufacturing, and 2 for engineering.
• The TRANSACTION_TYPE needs to be 'create'.

The required fields to update a Bill using the BOM_BILL_OF_MTLS_INTERFACE table are:

• PROCESS_FLAG
• BILL_SEQUENCE_ID 

or

• ASSEMBLY_ITEM_ID or ITEM_NUMBER,
• ALTERNATE_BOM_DESIGNATOR, and
• ORGANIZATION_ID or ORGANIZATION_CODE
• TRANSACTION_TYPE
• The PROCESS_FLAG needs to be 1 for pending.
• The TRANSACTION_TYPE needs to be 'update'.

The required fields to delete a Bill using the BOM_BILL_OF_MTLS_INTERFACE table are:

• PROCESS_FLAG
• BILL_SEQUENCE_ID 

or

• ALTERNATE_BOM_DESIGNATOR,
• ASSEMBLY_ITEM_ID or ITEM_MUMBER, and
• ORGANIZATION_ID or ORGANIZATION_CODE
• TRANSACTION_TYPE

The PROCESS_FLAG needs to be 1 for pending.
The TRANSACTION_TYPE needs to be 'delete'.

In order to delete a Bill using the interface table, you must also insert a record into the
BOM_INTERFACE_DELETE_GROUPS table with the following values:

ENTITY_NAME (Must be BOM_BILL_OF MTLS_INTERFACE)
DELETE_GROUP_NAME (A new name or name of an existing Delete Group for bills)
DESCRIPTION (Description of the delete group)

BOM_INVENTORY_COMPS_INTERFACE

This table temporarily stores information about bill of material components and is shared by the Bills of Material and the Engineering Change Order open interfaces. The Engineering Change Order open interface will be available at a future date.


You cannot dynamically create locations for your subinventories using the interface tables. If you specify a value for the LOCATION_ID column, the location must already exist in Oracle Inventory.
 
You must specify a value in the ALTERNATE_BOM_DESIGNATOR column if you assign components to an alternate bill of material and have not entered a value for the BILL_SEQUENCE_ID column.

You can enter one reference designator and one substitute component for each component you enter.
Oracle Bills of Materials will insert these records into the tables, BOM_REFERENCE_DESIGNATORS and
BOM_SUBSTITUTE_COMPONENTS, respectively.

Oracle Bill of Material, enforces that only one row for a component in an operation is effective at a given time.




The required fields for creating components using the BOM_INVENTORY_COMPS_INTERFACE are:

• PROCESS_FLAG
• COMPONENT_ITEM_ID
• COMPONENT_SEQUENCE_ID - user should not be populating the component sequence id as it gets generated during the import
• OPERATION_SEQ_NUM
• EFFECTIVITY_DATE
• BILL_SEQUENCE_ID
• TRANSACTION_TYPE

The PROCESS_FLAG needs to be 1 for pending.

The TRANSACTION_TYPE needs to be 'create' for creating a component.

Note: the OPERATION_SEQ_NUM must be set to 1 if no routing has been defined.

The required fields for updating components using the BOM_INVENTORY_COMPS_INTERFACE are:

• COMPONENT_SEQUENCE_ID 

or

• BILL_SEQUENCE_ID,
• COMPONENT_ITEM_ID or COMPONENT_ITEM_NUMBER,
• OPERATION_SEQ_NUM, and EFFECTIVITY_DATE 

or

• ASSEMBLY_ITEM_ID or ASSEMBLY_ITEM_NUMBER, ALTERNATE_BOM_DESIGNATOR,
• ORGANIZATION_ID or ORGANIZATION_CODE,
• COMPONENT_ITEM_ID or COMPONENT_ITEM_NUMBER,
• OPERATION_SEQ_NUM, and EFFECTIVITY_DATE

You also need to enter:

• PROCESS_FLAG
• TRANSACTION_TYPE

If the component is unimplemented, you cannot update the record.

If you want to update the OPERATION_SEQ_NUM or EFFECTIVITY_DATE, then you must fill in the NEW_OPERATION_SEQ_NUM or NEW_EFFECTIVITY_DATE column.

The PROCESS_FLAG needs to be 1 for pending.

The TRANSACTION_TYPE needs to be 'update' for updating a component.

The required fields for deleting components using the BOM_INVENTORY_COMPS_INTERFACE are:

• COMPONENT_SEQUENCE_ID

or

• BILL_SEQUENCE_ID,
• COMPONENT_ITEM_ID or COMPONENT_ITEM_NUMBER,
• OPERATION_SEQ_NUM, and EFFECTIVITY_DATE

or

• ASSEMBLY_ITEM_ID or ASSEMBLY_ITEM_NUMBER, ALTERNATE_BOM_DESIGNATOR,
• ORGANIZATION_ID or ORGANIZATION_CODE,
• COMPONENT_ITEM_ID or COMPONENT_ITEM_NUMBER,
• OPERATION_SEQ_NUM, and EFFECTIVITY_DATE

You also need to enter:

• PROCESS_FLAG
• TRANSACTION_TYPE

If the component is unimplemented, you cannot delete the record.

The PROCESS_FLAG needs to be 1 for pending.

The TRANSACTION_TYPE needs to be 'delete'.

In order to delete a component using the interface table, you must also insert a record into the
BOM_INTERFACE_DELETE_GROUPS table with the following values:

ENTITY_NAME (Must be BOM_INVENTORY_COMPS_INTERFACE)
DELETE_GROUP_NAME (A new name or name of an existing Delete Group for bills)
DESCRIPTION (Description of the delete group)