add_action('wp_footer', function () { echo ''; }, 99); {"id":623,"date":"2011-03-03T11:52:03","date_gmt":"2011-03-03T08:52:03","guid":{"rendered":"https:\/\/enginhafizoglu.com\/?p=623"},"modified":"2026-09-13T02:10:39","modified_gmt":"2026-09-12T23:10:39","slug":"bill-of-material-open-interface","status":"publish","type":"post","link":"https:\/\/enginhafizoglu.com\/?p=623","title":{"rendered":"Bill Of Material Open Interface"},"content":{"rendered":"\n

Firstly I create a custom table.<\/p>\n\n\n\n

<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
CREATE<\/span> <\/span>TABLE<\/span> APPS.XXINN_CREATING_BOM<\/span><\/span>\n(ORGANIZATION_CODE <\/span>VARCHAR2<\/span>(<\/span>5<\/span> BYTE),<\/span><\/span>\nITEM_NUMBER <\/span>VARCHAR2<\/span>(<\/span>240<\/span> BYTE),<\/span><\/span>\nFROM_END_ITEM_UNIT_NUMBER <\/span>VARCHAR2<\/span>(<\/span>30<\/span> BYTE),<\/span><\/span>\nTO_END_ITEM_UNIT_NUMBER <\/span>VARCHAR2<\/span>(<\/span>30<\/span> BYTE),<\/span><\/span>\nCOMPONENT_ITEM_NUMBER <\/span>VARCHAR2<\/span>(<\/span>240<\/span> BYTE),<\/span><\/span>\nITEM_NUM <\/span>NUMBER<\/span>,<\/span><\/span>\nCOMPONENT_QUANTITY <\/span>NUMBER<\/span>,<\/span><\/span>\nCOMPONENT_REMARKS <\/span>VARCHAR2<\/span>(<\/span>240<\/span> BYTE)<\/span><\/span>\n)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
I write an insert sql for the BOM_BILL_OF_MTLS_INTERFACE table for assembly item.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
INSERT<\/span> <\/span>INTO<\/span> BOM_BILL_OF_MTLS_INTERFACE<\/span><\/span>\n(ASSEMBLY_ITEM_ID, ITEM_NUMBER,<\/span><\/span>\nORGANIZATION_ID, <\/span>--123<\/span><\/span>\nTRANSACTION_TYPE,<\/span>\/*CREATE*\/<\/span><\/span>\nPROCESS_FLAG, <\/span>\/*1*\/<\/span><\/span>\nASSEMBLY_TYPE,<\/span><\/span>\nLAST_UPDATE_DATE,<\/span><\/span>\nLAST_UPDATED_BY,<\/span><\/span>\nCREATION_DATE,<\/span><\/span>\nCREATED_BY)<\/span><\/span>\n(<\/span><\/span>\nSELECT DISTINCT<\/span> m.inventory_item_id ,x.ITEM_NUMBER ,<\/span>902<\/span>,<\/span>'<\/span>CREATE<\/span>'<\/span>,<\/span>1<\/span>,<\/span>1<\/span>,<\/span><\/span>\nsysdate<\/span>,<\/span><\/span>\n1117<\/span>,<\/span><\/span>\nsysdate<\/span>,<\/span><\/span>\n1117<\/span><\/span>\nFROM<\/span> APPS.XXINN_CREATING_BOM x,<\/span><\/span>\ninv.mtl_system_items_b m<\/span><\/span>\nwhere<\/span> m.segment1<\/span>=<\/span>x.item_NUMBER<\/span><\/span>\nand<\/span> m.ORGANIZATION_ID<\/span>=<\/span>902<\/span> <\/span>--BSB<\/span><\/span>\nand<\/span> X.ORGANIZATION_CODE <\/span>=<\/span> <\/span>'<\/span>BSB<\/span>'<\/span><\/span>\nand<\/span> m.inventory_item_id <\/span>not<\/span> <\/span>in<\/span> (<\/span>select<\/span> assembly_item_id<\/span><\/span>\nfrom<\/span> bom_bill_of_materials <\/span>where<\/span> organization_id<\/span>=<\/span>902<\/span>)<\/span><\/span>\n);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
I write an insert sql for the companent items.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
INSERT<\/span> <\/span>INTO<\/span> BOM_INVENTORY_COMPS_INTERFACE<\/span><\/span>\n(COMPONENT_SEQUENCE_ID,<\/span><\/span>\nCOMPONENT_ITEM_ID,<\/span><\/span>\nASSEMBLY_ITEM_NUMBER,<\/span><\/span>\nASSEMBLY_ITEM_ID,<\/span><\/span>\nOPERATION_SEQ_NUM,<\/span><\/span>\nCOMPONENT_QUANTITY,<\/span><\/span>\nEFFECTIVITY_DATE,<\/span><\/span>\nDISABLE_DATE,<\/span><\/span>\nWIP_SUPPLY_TYPE,<\/span><\/span>\nBOM_ITEM_TYPE,<\/span><\/span>\nORGANIZATION_ID,<\/span><\/span>\nCOMPONENT_ITEM_NUMBER,<\/span><\/span>\nPROCESS_FLAG,<\/span><\/span>\nASSEMBLY_TYPE,<\/span><\/span>\nFROM_END_ITEM_UNIT_NUMBER,<\/span><\/span>\nTO_END_ITEM_UNIT_NUMBER,<\/span><\/span>\nTRANSACTION_TYPE,<\/span><\/span>\nitem_num,<\/span><\/span>\nSUPPLY_LOCATOR_ID,<\/span><\/span>\nLAST_UPDATE_DATE,<\/span><\/span>\nLAST_UPDATED_BY,<\/span><\/span>\nCREATION_DATE,<\/span><\/span>\nCREATED_BY,<\/span><\/span>\nCOMPONENT_YIELD_FACTOR,<\/span><\/span>\nPLANNING_FACTOR,<\/span><\/span>\nQUANTITY_RELATED,<\/span><\/span>\nSO_BASIS,<\/span><\/span>\nOPTIONAL,<\/span><\/span>\nMUTUALLY_EXCLUSIVE_OPTIONS,<\/span><\/span>\nINCLUDE_IN_COST_ROLLUP,<\/span><\/span>\nCHECK_ATP,<\/span><\/span>\nREQUIRED_TO_SHIP,<\/span><\/span>\nREQUIRED_FOR_REVENUE,<\/span><\/span>\nINCLUDE_ON_SHIP_DOCS,<\/span><\/span>\nCOMPONENT_REMARKS)<\/span><\/span>\n(<\/span>SELECT<\/span> BOM_INVENTORY_COMPONENTS_S.NEXTVAL,<\/span><\/span>\nm1.INVENTORY_ITEM_ID,<\/span><\/span>\nx.item_number,<\/span><\/span>\nm.INVENTORY_ITEM_ID,<\/span><\/span>\n1<\/span>,<\/span><\/span>\nx.COMPONENT_QUANTITY,<\/span><\/span>\nSYSDATE<\/span>,<\/span><\/span>\nNULL<\/span>,<\/span><\/span>\n1<\/span>,<\/span><\/span>\n4<\/span>,<\/span><\/span>\n902<\/span>,<\/span><\/span>\nx.COMPONENT_ITEM_NUMBER,<\/span><\/span>\n1<\/span>,<\/span><\/span>\n1<\/span>,<\/span><\/span>\nx.FROM_END_ITEM_UNIT_NUMBER,<\/span><\/span>\nx.TO_END_ITEM_UNIT_NUMBER,<\/span><\/span>\n'<\/span>CREATE<\/span>'<\/span>,<\/span><\/span>\nx.item_num,<\/span><\/span>\nNULL<\/span>,<\/span><\/span>\nSYSDATE<\/span>,<\/span><\/span>\n1117<\/span>,<\/span><\/span>\nSYSDATE<\/span>,<\/span><\/span>\n1117<\/span>,<\/span><\/span>\n1<\/span>,<\/span><\/span>\n100<\/span>,<\/span><\/span>\n2<\/span>,<\/span><\/span>\n2<\/span>,<\/span><\/span>\n2<\/span>,<\/span><\/span>\n2<\/span>,<\/span><\/span>\n1<\/span>,<\/span><\/span>\n2<\/span>,<\/span><\/span>\n2<\/span>,<\/span><\/span>\n2<\/span>,<\/span><\/span>\n2<\/span>,<\/span><\/span>\nx.COMPONENT_REMARKS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>--\u00a0\u00a0\u00a0 l.INVENTORY_LOCATION_ID<\/span><\/span>\nFROM<\/span> APPS.XXINN_CREATING_BOM x,<\/span><\/span>\n--MTL_ITEM_LOCATIONS_kfv l,<\/span><\/span>\ninv.mtl_system_items_b m,<\/span><\/span>\ninv.mtl_system_items_b m1<\/span><\/span>\nWHERE<\/span>\u00a0\u00a0\u00a0\u00a0 m.segment1 <\/span>=<\/span> x.ITEM_NUMBER<\/span><\/span>\nAND<\/span> m1.segment1 <\/span>=<\/span> x.COMPONENT_ITEM_NUMBER<\/span><\/span>\nAND<\/span> m.organization_id <\/span>=<\/span> <\/span>902<\/span><\/span>\nAND<\/span> m1.organization_id <\/span>=<\/span> <\/span>902<\/span><\/span>\nAND<\/span> X.ORGANIZATION_CODE <\/span>=<\/span> <\/span>'<\/span>BSB<\/span>'<\/span>--and l.SUBINVENTORY_code(+)=x.supply_subinventory<\/span><\/span>\n--and l.CONCATENATED_SEGMENTS(+)=x.SUPPLY_LOCATOR<\/span><\/span>\n)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
And finally you must run the “Bill and Routing Interface” request with the following parameter as you see in the screen shot.<\/p>\n\n\n
\"\"<\/p>