03 Mar 11 Bill Of Material Open Interface

CREATE TABLE APPS.XXINN_CREATING_BOM
(ORGANIZATION_CODE VARCHAR2(5 BYTE),
ITEM_NUMBER VARCHAR2(240 BYTE),
FROM_END_ITEM_UNIT_NUMBER VARCHAR2(30 BYTE),
TO_END_ITEM_UNIT_NUMBER VARCHAR2(30 BYTE),
COMPONENT_ITEM_NUMBER VARCHAR2(240 BYTE),
ITEM_NUM NUMBER,
COMPONENT_QUANTITY NUMBER,
COMPONENT_REMARKS VARCHAR2(240 BYTE)
)

INSERT INTO BOM_BILL_OF_MTLS_INTERFACE
(ASSEMBLY_ITEM_ID, ITEM_NUMBER,
ORGANIZATION_ID, --123
TRANSACTION_TYPE,/*CREATE*/
PROCESS_FLAG, /*1*/
ASSEMBLY_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
(
SELECT DISTINCT m.inventory_item_id ,x.ITEM_NUMBER ,902,'CREATE',1,1,
sysdate,
1117,
sysdate,
1117
FROM APPS.XXINN_CREATING_BOM x,
inv.mtl_system_items_b m
where m.segment1=x.item_NUMBER
and m.ORGANIZATION_ID=902 --BSB
and X.ORGANIZATION_CODE = 'BSB'
and m.inventory_item_id not in (select assembly_item_id
from bom_bill_of_materials where organization_id=902)
);

INSERT INTO BOM_INVENTORY_COMPS_INTERFACE
(COMPONENT_SEQUENCE_ID,
COMPONENT_ITEM_ID,
ASSEMBLY_ITEM_NUMBER,
ASSEMBLY_ITEM_ID,
OPERATION_SEQ_NUM,
COMPONENT_QUANTITY,
EFFECTIVITY_DATE,
DISABLE_DATE,
WIP_SUPPLY_TYPE,
BOM_ITEM_TYPE,
ORGANIZATION_ID,
COMPONENT_ITEM_NUMBER,
PROCESS_FLAG,
ASSEMBLY_TYPE,
FROM_END_ITEM_UNIT_NUMBER,
TO_END_ITEM_UNIT_NUMBER,
TRANSACTION_TYPE,
item_num,
SUPPLY_LOCATOR_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
COMPONENT_YIELD_FACTOR,
PLANNING_FACTOR,
QUANTITY_RELATED,
SO_BASIS,
OPTIONAL,
MUTUALLY_EXCLUSIVE_OPTIONS,
INCLUDE_IN_COST_ROLLUP,
CHECK_ATP,
REQUIRED_TO_SHIP,
REQUIRED_FOR_REVENUE,
INCLUDE_ON_SHIP_DOCS,
COMPONENT_REMARKS)
(SELECT BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
m1.INVENTORY_ITEM_ID,
x.item_number,
m.INVENTORY_ITEM_ID,
1,
x.COMPONENT_QUANTITY,
SYSDATE,
NULL,
1,
4,
902,
x.COMPONENT_ITEM_NUMBER,
1,
1,
x.FROM_END_ITEM_UNIT_NUMBER,
x.TO_END_ITEM_UNIT_NUMBER,
'CREATE',
x.item_num,
NULL,
SYSDATE,
1117,
SYSDATE,
1117,
1,
100,
2,
2,
2,
2,
1,
2,
2,
2,
2,
x.COMPONENT_REMARKS                   --    l.INVENTORY_LOCATION_ID
FROM APPS.XXINN_CREATING_BOM x,
--MTL_ITEM_LOCATIONS_kfv l,
inv.mtl_system_items_b m,
inv.mtl_system_items_b m1
WHERE     m.segment1 = x.ITEM_NUMBER
AND m1.segment1 = x.COMPONENT_ITEM_NUMBER
AND m.organization_id = 902
AND m1.organization_id = 902
AND X.ORGANIZATION_CODE = 'BSB'--and l.SUBINVENTORY_code(+)=x.supply_subinventory
--and l.CONCATENATED_SEGMENTS(+)=x.SUPPLY_LOCATOR
)