请赐教bom_bill_of_mtls_interface与bom_inventory_comps_interface表的数据结构

yongliu1210

我想做外挂的导入bom的程序，但不知这两个接口的数据结构，需要将什么数据放入哪些相应的栏位
谢谢

xxzhaobb

你在SQL PLUS下使用Descript ，然后就可以看到表的结构了

enjoylifedass

近日也准备做bom接口，正好手头有一些资料，分享一下：
Checked for relevance on 14-APR-2008

     A Guide to Using the Bill of Material (BOM) Open Interface


This paper serves as a reference when using the BOM Open Interface.
It defines the tables used in importing bills and explains the mandatory,
derived and optional columns.  Refer to the Oracle Manufacturing, Distribution,
Sales and Service Open Interfaces Manual (A-57332) for a complete set of
instructions.


1.  TABLES.

You need to populate following interface tables with data from your legacy
system:

    BOM_BILL_OF_MTLS_INTERFACE
    BOM_INVENTORY_COMPS_INTERFACE
    BOM_ASSY_COMMENTS_INTERFACE
    BOM_REF_DESGS_INTERFACE
    BOM_SUB_COMPS_INTERFACE|
    MTL_ITEM_REVISIONS_INTERFACE

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

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.

In order to import a BOM with components, you need to populate:

    BOM_BILL_OF_MTLS_INTERFACE
    BOM_INVENTORY_COMPS_INTERFACE

With these two tables, you can create BOM header information and assign
component details.

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

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


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


3.  IMPORT CONSIDERATIONS.

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.


4.  BOM_BILL_OF_MTLS_INTERFACE TABLE.

    a.  REQUIRED COLUMNS 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).


    b.  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                            Userid
        CREATION_DATE                            System Date
        CREATED_BY                            Userid
        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


5.  BOM_INVENTORY_COMPS_INTERFACE TABLE.

    a.  REQUIRED 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.


    b.  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                          Userid
        CREATION_DATE                          System Date
        CREATED_BY                          Userid
        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


6.  IMPORTING ADDITIONAL BILL INFORMATION

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


7.  BOM_ASSY_COMMENTS_INTERFACE TABLE.

    a.  REQUIRED 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


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


8.  BOM_REF_DESGS_INTERFACE AND BOM_SUBS_COMPS_INTERFACE TABLES.

    a.  REQUIRED 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

    b.  REQUIRED 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

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


9.  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.  If,
however, the row in the BOM_BILL_OF_MTLS_INTERFACE table fails, the BOM and
and all of its details are not imported.


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

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.

yongliu1210

sorry,but I do not know the column  COMPONENT_SEQUENCE_ID which in the BOM_INVENTORY_COMPS_INTERFACE why is a required column ,
what  differents for the   bill_SEQUENCE_ID column and what data should be  populated in this cloumn.
hope message for all , thank very much

champiom

COMPONENT_SEQUENCE_ID ,这个不用输入的，系统会自动加上去的。

champiom

process_flag,
component_item_id,
operation_seq_num,
assembly_item_id,
transaction_type,
Effectivity_date,
organization_id ,
item_num
这几个是关键的

yongliu1210

thanks very much,

heshao-oracle

insert into bom_bill_of_mtls_interface(
                        assembly_item_id,
                        organization_id,
                        last_update_date,
                        last_updated_by,
                        creation_date,
                        created_by,
                        assembly_type,
                        process_flag,
                        organization_code,
                        common_org_code,
                        item_number,
                        transaction_type
                        )
                select
                        null,--assembly_item_id,
                        p_target_organization_id,--organization_id,
                        sysdate,--last_update_date,
                        p_user_id,--last_updated_by,
                        sysdate,--creation_date,
                        p_user_id,--created_by,
                        1,--assembly_type,
                        1,--process_flag,
                        null,--organization_code,
                        null,--common_org_code,
                        v_header_item_number,--item_number,
                        'CREATE'--transaction_type,
                  from  dual;
      
  
          insert into bom_inventory_comps_interface(
                        operation_seq_num,
                        component_item_id,
                        last_update_date,
                        last_updated_by,
                        creation_date,
                        created_by,
                        last_update_login,
                        item_num,
                        component_quantity,
                        component_remarks,
                        effectivity_date,
                        organization_id,
                        organization_code,
                        component_item_number,
                        assembly_item_number,
                        process_flag,
                        transaction_type
                        )
                select  
                        1,--operation_seq_num            
                        null,--component_item_id            
                        sysdate,--last_update_date              
                        p_user_id,--last_updated_by               
                        sysdate,--creation_date                 
                        p_user_id,--created_by                    
                        null,--last_update_login            
                        null,--item_num                     
                        r1.component_quantity,--component_quantity                 
                        null,--component_remarks            
                        sysdate,--effectivity_date                                       
                        p_target_organization_id,--organization_id               
                        null,--organization_code            
                        r1.item_number,--component_item_number         
                        v_header_item_number,--assembly_item_number                       
                        1,--process_flag                          
                        'CREATE'--transaction_type      
                   from dual;