create or replace procedure p_bom_expend1029
is
begin
declare
cursor c1 is
--select TOP_ITEM item_num from ch_model_all where top_item='2D20-0000000'
--select ITEM item_num from ch_zero20 --a ch0803
select msi.inventory_item_id,msi.segment1 item_num
from mtl_system_items msi
where
msi.organization_id=255 and
msi.segment1 like '%0-0000000' and
UPPER(MSI.inventory_ITEM_status_CODE)='ACTIVE';
v1 c1%rowtype;
begin
open c1;
loop
fetch c1 into v1;
exit when c1%notfound;
begin
insert into ch_bom_expend1029
select top.organization_id,
bom.ASSEMBLY_ITEM_ID TOP_ITEM_ID,
top.segment1 top_item_num,
top.description top_desc,
bom.ASSEMBLY_ITEM_ID ASSEMBLY_ITEM_ID,
bic.ITEM_num ITEM_NO,
1 item_level,
bic.COMPONENT_ITEM_ID COMPONENT_ITEM_ID,
msi.segment1 item_num,
msi.description item_desc,
msi.unit_weight,
msi.WEIGHT_UOM_CODE,
msi.primary_uom_code,
NVL(bic.WIP_SUPPLY_TYPE,MSI.WIP_SUPPLY_TYPE) WIP_SUPPLY_TYPE,
msi.PLANNING_MAKE_BUY_CODE,
bic.COMPONENT_QUANTITY COMPONENT_QUANTITY,
bic.COMPONENT_QUANTITY PER_UNIT_QTY,
bic.COMPONENT_REMARKS COMPONENT_REMARKS,
substr(ltrim(bic.COMPONENT_REMARKS),1,2) cc_code,
bic.COMPONENT_QUANTITY require_qty,
bic.EFFECTIVITY_DATE,
BIC.DISABLE_DATE,
to_date(null) dis_date1,
to_date(null) dis_date2,
to_date(null) dis_date3,
to_date(null) dis_date4,
to_date(null) dis_date5,
to_date(null) dis_date6,
null,
msi.FIXED_LEAD_TIME request_id
,nvl(CST.item_cost,0) item_cost,''
from
mtl_system_items top,
bom.BOM_BILL_OF_MATERIALS bom,
bom.BOM_INVENTORY_COMPONENTS bic,
mtl_system_items msi,
cst_item_costs cst
where
top.inventory_item_id=bom.ASSEMBLY_ITEM_ID
and top.organization_id=bom.organization_id
AND bic.BILL_SEQUENCE_ID=bom.common_BILL_SEQUENCE_ID
AND bom.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=bom.organization_id
and msi.inventory_item_id=cst.inventory_item_id
and msi.organization_id=cst.organization_id
and cst.cost_type_id=2
-- AND NVL(bic.WIP_SUPPLY_TYPE,MSI.WIP_SUPPLY_TYPE)!=6--
AND ( BIC.DISABLE_DATE IS NULL)
and top.organization_id=255
and top.segment1=v1.item_num
;
commit;
-- 2 level
insert into ch_bom_expend1029
select top.organization_id,
bom.ASSEMBLY_ITEM_ID TOP_ITEM_ID,
top.segment1 top_item_num,
top.description top_desc,
bom1.ASSEMBLY_ITEM_ID ASSEMBLY_ITEM_ID,
bic1.ITEM_num ITEM_NO,
2 item_level,
bic1.COMPONENT_ITEM_ID COMPONENT_ITEM_ID,
msi1.segment1 item_num,
msi1.description item_desc,
msi1.unit_weight,
msi1.WEIGHT_UOM_CODE,
msi1.primary_uom_code,
NVL(bic1.WIP_SUPPLY_TYPE,MSI1.WIP_SUPPLY_TYPE) WIP_SUPPLY_TYPE,
msi1.PLANNING_MAKE_BUY_CODE,
bic.COMPONENT_QUANTITY*bic1.COMPONENT_QUANTITY COMPONENT_QUANTITY,
bic1.COMPONENT_QUANTITY PER_UNIT_QTY,
bic1.COMPONENT_REMARKS COMPONENT_REMARKS,
substr(ltrim(bic1.COMPONENT_REMARKS),1,2) cc_code,
bic.COMPONENT_QUANTITY*bic1.COMPONENT_QUANTITY require_qty,
bic1.EFFECTIVITY_DATE,
BIC1.DISABLE_DATE,
bic.disable_date dis_date1,
to_date(null) dis_date2,
to_date(null) dis_date3,
to_date(null) dis_date4,
to_date(null) dis_date5,
to_date(null) dis_date6,
null,
msi1.FIXED_LEAD_TIME request_id
,nvl(cst.item_cost,0) item_cost,''
from
inv.mtl_system_items top,
bom.BOM_BILL_OF_MATERIALS bom,
bom.BOM_INVENTORY_COMPONENTS bic,
mtl_system_items msi,
bom.BOM_BILL_OF_MATERIALS bom1,
APPS.BOM_INVENTORY_COMPONENTS bic1,
INV.MTL_SYSTEM_ITEMS MSI1,
cst_item_costs cst
where
top.inventory_item_id=bom.ASSEMBLY_ITEM_ID
and top.organization_id=bom.organization_id
AND bic.BILL_SEQUENCE_ID=bom.common_BILL_SEQUENCE_ID
AND bom.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=bom.organization_id
AND bom1.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=BOM1.ASSEMBLY_ITEM_ID
and top.organization_id=bom1.organization_id
AND bic1.BILL_SEQUENCE_ID=bom1.common_BILL_SEQUENCE_ID
AND bic1.COMPONENT_ITEM_ID=MSI1.INVENTORY_ITEM_ID
AND MSI1.ORGANIZATION_ID=bom1.organization_id
and msi1.inventory_item_id=cst.inventory_item_id
and msi1.organization_id=cst.organization_id
and cst.cost_type_id=2
and ( BIC.DISABLE_DATE IS NULL) AND
( BIC1.DISABLE_DATE IS NULL )
-- and NVL(bic1.WIP_SUPPLY_TYPE,MSI1.WIP_SUPPLY_TYPE)!=6
-- AND NVL(bic.WIP_SUPPLY_TYPE,MSI.WIP_SUPPLY_TYPE)=6
and top.organization_id=255
and top.segment1=v1.item_num ;
commit;
-- 3 level
insert into ch_bom_expend1029
select top.organization_id,
bom.ASSEMBLY_ITEM_ID TOP_ITEM_ID,
top.segment1 top_item_num,
top.description top_desc,
bom2.ASSEMBLY_ITEM_ID ASSEMBLY_ITEM_ID,
bic2.ITEM_num ITEM_NO,
3 item_level,
bic2.COMPONENT_ITEM_ID COMPONENT_ITEM_ID,
msi2.segment1 item_num,
msi2.description item_desc,
msi2.unit_weight,
msi2.WEIGHT_UOM_CODE,
msi2.primary_uom_code,
NVL(bic2.WIP_SUPPLY_TYPE,MSI2.WIP_SUPPLY_TYPE) WIP_SUPPLY_TYPE,
msi2.PLANNING_MAKE_BUY_CODE,
bic.COMPONENT_QUANTITY*bic1.COMPONENT_QUANTITY*bic2.COMPONENT_QUANTITY COMPONENT_QUANTITY,
bic2.COMPONENT_QUANTITY PER_UNIT_QTY,
bic2.COMPONENT_REMARKS COMPONENT_REMARKS,
substr(ltrim(bic2.COMPONENT_REMARKS),1,2) cc_code,
bic.COMPONENT_QUANTITY*bic1.COMPONENT_QUANTITY *bic2.COMPONENT_QUANTITY require_qty,
bic2.EFFECTIVITY_DATE,
BIC2.DISABLE_DATE,
bic1.disable_date dis_date1,
bic.disable_date dis_date2,
to_date(null) dis_date3,
to_date(null) dis_date4,
to_date(null) dis_date5,
to_date(null) dis_date6,
null,
msi2.FIXED_LEAD_TIME request_id
,nvl(cst.item_cost,0) item_cost,
''
from
mtl_system_items top,
bom.BOM_BILL_OF_MATERIALS bom,
bom.BOM_INVENTORY_COMPONENTS bic,
mtl_system_items msi,
bom.BOM_BILL_OF_MATERIALS bom1,
APPS.BOM_INVENTORY_COMPONENTS bic1,
INV.MTL_SYSTEM_ITEMS MSI1,
bom.BOM_BILL_OF_MATERIALS bom2,
APPS.BOM_INVENTORY_COMPONENTS bic2,
INV.MTL_SYSTEM_ITEMS MSI2,
cst_item_costs cst
where
top.inventory_item_id=bom.ASSEMBLY_ITEM_ID
and top.organization_id=bom.organization_id
AND bic.BILL_SEQUENCE_ID=bom.common_BILL_SEQUENCE_ID
AND bom.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=bom.organization_id
AND top.organization_id=bom1.organization_id
AND bom1.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=BOM1.ASSEMBLY_ITEM_ID
AND bic1.BILL_SEQUENCE_ID=bom1.common_BILL_SEQUENCE_ID
AND bic1.COMPONENT_ITEM_ID=MSI1.INVENTORY_ITEM_ID
AND MSI1.ORGANIZATION_ID=bom1.organization_id
AND top.organization_id=bom2.organization_id
AND bom2.ALTERNATE_BOM_DESIGNATOR is null
AND bic1.COMPONENT_ITEM_ID=BOM2.ASSEMBLY_ITEM_ID
AND bic2.BILL_SEQUENCE_ID=bom2.common_BILL_SEQUENCE_ID
AND bic2.COMPONENT_ITEM_ID=MSI2.INVENTORY_ITEM_ID
and msi2.inventory_item_id=cst.inventory_item_id
and msi2.organization_id=cst.organization_id
and cst.cost_type_id=2
AND MSI2.ORGANIZATION_ID=bom2.organization_id and
( BIC.DISABLE_DATE IS NULL ) AND
( BIC1.DISABLE_DATE IS NULL ) AND
( BIC2.DISABLE_DATE IS NULL )
and NVL(bic.WIP_SUPPLY_TYPE,MSI.WIP_SUPPLY_TYPE)=6
AND NVL(bic1.WIP_SUPPLY_TYPE,MSI1.WIP_SUPPLY_TYPE)=6
AND NVL(bic2.WIP_SUPPLY_TYPE,MSI2.WIP_SUPPLY_TYPE)!=6
and top.organization_id=255
and top.segment1=v1.item_num ;
commit;
-- 4 level
insert into ch_bom_expend1029
select top.organization_id,
bom.ASSEMBLY_ITEM_ID TOP_ITEM_ID,
top.segment1 top_item_num,
top.description top_desc,
bom3.ASSEMBLY_ITEM_ID ASSEMBLY_ITEM_ID,
bic3.ITEM_num ITEM_NO,
4 item_level,
bic3.COMPONENT_ITEM_ID COMPONENT_ITEM_ID,
msi3.segment1 item_num,
msi3.description item_desc,
msi3.unit_weight,
msi3.WEIGHT_UOM_CODE,
msi3.primary_uom_code,
NVL(bic3.WIP_SUPPLY_TYPE,MSI3.WIP_SUPPLY_TYPE) WIP_SUPPLY_TYPE,
msi3.PLANNING_MAKE_BUY_CODE,
bic.COMPONENT_QUANTITY*bic1.COMPONENT_QUANTITY*bic2.COMPONENT_QUANTITY*bic3.COMPONENT_QUANTITY COMPONENT_QUANTITY,
bic3.COMPONENT_QUANTITY PER_UNIT_QTY,
bic3.COMPONENT_REMARKS COMPONENT_REMARKS,
substr(ltrim(bic3.COMPONENT_REMARKS),1,2) cc_code,
bic.COMPONENT_QUANTITY *bic1.COMPONENT_QUANTITY *bic2.COMPONENT_QUANTITY *bic3.COMPONENT_QUANTITY require_qty,
bic3.EFFECTIVITY_DATE,
BIC3.DISABLE_DATE,
bic2.disable_date dis_date1,
bic1.disable_date dis_date2,
bic.disable_date dis_date3,
to_date(null) dis_date4,
to_date(null) dis_date5,
to_date(null) dis_date6,
null,
msi3.FIXED_LEAD_TIME request_id
,nvl(cst.item_cost,0) item_cost,''
from
mtl_system_items top,
bom.BOM_BILL_OF_MATERIALS bom,
bom.BOM_INVENTORY_COMPONENTS bic,
mtl_system_items msi,
bom.BOM_BILL_OF_MATERIALS bom1,
APPS.BOM_INVENTORY_COMPONENTS bic1,
INV.MTL_SYSTEM_ITEMS MSI1,
bom.BOM_BILL_OF_MATERIALS bom2,
APPS.BOM_INVENTORY_COMPONENTS bic2,
INV.MTL_SYSTEM_ITEMS MSI2,
bom.BOM_BILL_OF_MATERIALS bom3,
APPS.BOM_INVENTORY_COMPONENTS bic3,
INV.MTL_SYSTEM_ITEMS MSI3,
cst_item_costs cst
where
top.inventory_item_id=bom.ASSEMBLY_ITEM_ID
and top.organization_id=bom.organization_id
AND bic.BILL_SEQUENCE_ID=bom.common_BILL_SEQUENCE_ID
AND bom.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=bom.organization_id
AND top.organization_id=bom1.organization_id
AND bom1.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=BOM1.ASSEMBLY_ITEM_ID
AND bic1.BILL_SEQUENCE_ID=bom1.common_BILL_SEQUENCE_ID
AND bic1.COMPONENT_ITEM_ID=MSI1.INVENTORY_ITEM_ID
AND MSI1.ORGANIZATION_ID=bom1.organization_id
AND top.organization_id=bom2.organization_id
AND bom2.ALTERNATE_BOM_DESIGNATOR is null
AND bic1.COMPONENT_ITEM_ID=BOM2.ASSEMBLY_ITEM_ID
AND bic2.BILL_SEQUENCE_ID=bom2.common_BILL_SEQUENCE_ID
AND bic2.COMPONENT_ITEM_ID=MSI2.INVENTORY_ITEM_ID
AND MSI2.ORGANIZATION_ID=bom2.organization_id
AND top.organization_id=bom3.organization_id
AND bom3.ALTERNATE_BOM_DESIGNATOR is null
AND bic2.COMPONENT_ITEM_ID=BOM3.ASSEMBLY_ITEM_ID
AND bic3.BILL_SEQUENCE_ID=bom3.common_BILL_SEQUENCE_ID
AND bic3.COMPONENT_ITEM_ID=MSI3.INVENTORY_ITEM_ID
AND MSI3.ORGANIZATION_ID=bom3.organization_id
and msi3.inventory_item_id=cst.inventory_item_id
and msi3.organization_id=cst.organization_id
and cst.cost_type_id=2
and ( BIC.DISABLE_DATE IS NULL ) AND
( BIC1.DISABLE_DATE IS NULL ) AND
( BIC2.DISABLE_DATE IS NULL) AND
( BIC3.DISABLE_DATE IS NULL )
and NVL(bic.WIP_SUPPLY_TYPE,MSI.WIP_SUPPLY_TYPE)=6
AND NVL(bic1.WIP_SUPPLY_TYPE,MSI1.WIP_SUPPLY_TYPE)=6
AND NVL(bic2.WIP_SUPPLY_TYPE,MSI2.WIP_SUPPLY_TYPE)=6
AND NVL(bic3.WIP_SUPPLY_TYPE,MSI3.WIP_SUPPLY_TYPE)!=6
and top.organization_id=255
and top.segment1=v1.item_num ;
commit;
-- 5 level
insert into ch_bom_expend1029
select top.organization_id,
bom.ASSEMBLY_ITEM_ID TOP_ITEM_ID,
top.segment1 top_item_num,
top.description top_desc,
bom4.ASSEMBLY_ITEM_ID ASSEMBLY_ITEM_ID,
bic4.ITEM_num ITEM_NO,
5 item_level,
bic4.COMPONENT_ITEM_ID COMPONENT_ITEM_ID,
msi4.segment1 item_num,
msi4.description item_desc,
msi4.unit_weight,
msi4.WEIGHT_UOM_CODE,
msi4.primary_uom_code,
NVL(bic4.WIP_SUPPLY_TYPE,MSI4.WIP_SUPPLY_TYPE) WIP_SUPPLY_TYPE,
msi4.PLANNING_MAKE_BUY_CODE,
bic.COMPONENT_QUANTITY*bic1.COMPONENT_QUANTITY*bic2.COMPONENT_QUANTITY*bic3.COMPONENT_QUANTITY*bic4.COMPONENT_QUANTITY COMPONENT_QUANTITY,
bic4.COMPONENT_QUANTITY PER_UNIT_QTY,
bic4.COMPONENT_REMARKS COMPONENT_REMARKS,
substr(ltrim(bic4.COMPONENT_REMARKS),1,2) cc_code,
bic.COMPONENT_QUANTITY *bic1.COMPONENT_QUANTITY *bic2.COMPONENT_QUANTITY *bic3.COMPONENT_QUANTITY*bic4.COMPONENT_QUANTITY require_qty,
bic4.EFFECTIVITY_DATE,
BIC4.DISABLE_DATE,
bic3.disable_date dis_date1,
bic2.disable_date dis_date2,
bic1.disable_date dis_date3,
bic.disable_date dis_date4,
to_date(null) dis_date5,
to_date(null) dis_date6,
null,
msi4.FIXED_LEAD_TIME request_id
,nvl(cst.item_cost,0) item_cost,''
from
mtl_system_items top,
bom.BOM_BILL_OF_MATERIALS bom,
bom.BOM_INVENTORY_COMPONENTS bic,
mtl_system_items msi,
bom.BOM_BILL_OF_MATERIALS bom1,
APPS.BOM_INVENTORY_COMPONENTS bic1,
INV.MTL_SYSTEM_ITEMS MSI1,
bom.BOM_BILL_OF_MATERIALS bom2,
APPS.BOM_INVENTORY_COMPONENTS bic2,
INV.MTL_SYSTEM_ITEMS MSI2,
bom.BOM_BILL_OF_MATERIALS bom3,
APPS.BOM_INVENTORY_COMPONENTS bic3,
INV.MTL_SYSTEM_ITEMS MSI3,
bom.BOM_BILL_OF_MATERIALS bom4,
APPS.BOM_INVENTORY_COMPONENTS bic4,
INV.MTL_SYSTEM_ITEMS MSI4,
cst_item_costs cst
where
top.inventory_item_id=bom.ASSEMBLY_ITEM_ID
and top.organization_id=bom.organization_id
AND bic.BILL_SEQUENCE_ID=bom.common_BILL_SEQUENCE_ID
AND bom.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=bom.organization_id
AND top.organization_id=bom1.organization_id
AND bom1.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=BOM1.ASSEMBLY_ITEM_ID
AND bic1.BILL_SEQUENCE_ID=bom1.common_BILL_SEQUENCE_ID
AND bic1.COMPONENT_ITEM_ID=MSI1.INVENTORY_ITEM_ID
AND MSI1.ORGANIZATION_ID=bom1.organization_id
AND top.organization_id=bom2.organization_id
AND bom2.ALTERNATE_BOM_DESIGNATOR is null
AND bic1.COMPONENT_ITEM_ID=BOM2.ASSEMBLY_ITEM_ID
AND bic2.BILL_SEQUENCE_ID=bom2.common_BILL_SEQUENCE_ID
AND bic2.COMPONENT_ITEM_ID=MSI2.INVENTORY_ITEM_ID
AND MSI2.ORGANIZATION_ID=bom2.organization_id
AND top.organization_id=bom3.organization_id
AND bom3.ALTERNATE_BOM_DESIGNATOR is null
AND bic2.COMPONENT_ITEM_ID=BOM3.ASSEMBLY_ITEM_ID
AND bic3.BILL_SEQUENCE_ID=bom3.common_BILL_SEQUENCE_ID
AND bic3.COMPONENT_ITEM_ID=MSI3.INVENTORY_ITEM_ID
AND MSI3.ORGANIZATION_ID=bom3.organization_id
AND top.organization_id=bom4.organization_id
AND bom4.ALTERNATE_BOM_DESIGNATOR is null
AND bic3.COMPONENT_ITEM_ID=BOM4.ASSEMBLY_ITEM_ID
AND bic4.BILL_SEQUENCE_ID=bom4.common_BILL_SEQUENCE_ID
AND bic4.COMPONENT_ITEM_ID=MSI4.INVENTORY_ITEM_ID
AND MSI4.ORGANIZATION_ID=bom4.organization_id
and msi4.inventory_item_id=cst.inventory_item_id
and msi4.organization_id=cst.organization_id
and cst.cost_type_id=2
and ( BIC.DISABLE_DATE IS NULL ) AND
( BIC1.DISABLE_DATE IS NULL ) AND
( BIC2.DISABLE_DATE IS NULL ) AND
( BIC3.DISABLE_DATE IS NULL ) AND
( BIC4.DISABLE_DATE IS NULL )
and NVL(bic.WIP_SUPPLY_TYPE,MSI.WIP_SUPPLY_TYPE)=6
AND NVL(bic1.WIP_SUPPLY_TYPE,MSI1.WIP_SUPPLY_TYPE)=6
AND NVL(bic2.WIP_SUPPLY_TYPE,MSI2.WIP_SUPPLY_TYPE)=6
AND NVL(bic3.WIP_SUPPLY_TYPE,MSI3.WIP_SUPPLY_TYPE)=6
AND NVL(bic4.WIP_SUPPLY_TYPE,MSI4.WIP_SUPPLY_TYPE)!=6
and top.organization_id=255
and top.segment1=v1.item_num ;
commit;
-- 6 level
insert into ch_bom_expend1029
select top.organization_id,
bom.ASSEMBLY_ITEM_ID TOP_ITEM_ID,
top.segment1 top_item_num,
top.description top_desc,
bom5.ASSEMBLY_ITEM_ID ASSEMBLY_ITEM_ID,
bic5.ITEM_num ITEM_NO,
6 item_level,
bic5.COMPONENT_ITEM_ID COMPONENT_ITEM_ID,
msi5.segment1 item_num,
msi5.description item_desc,
msi5.unit_weight,
msi5.WEIGHT_UOM_CODE,
msi5.primary_uom_code,
NVL(bic5.WIP_SUPPLY_TYPE,MSI5.WIP_SUPPLY_TYPE) WIP_SUPPLY_TYPE,
msi5.PLANNING_MAKE_BUY_CODE,
bic.COMPONENT_QUANTITY*bic1.COMPONENT_QUANTITY*bic2.COMPONENT_QUANTITY*bic3.COMPONENT_QUANTITY*bic4.COMPONENT_QUANTITY*bic5.COMPONENT_QUANTITY COMPONENT_QUANTITY,
bic5.COMPONENT_QUANTITY PER_UNIT_QTY,
bic5.COMPONENT_REMARKS COMPONENT_REMARKS,
substr(ltrim(bic5.COMPONENT_REMARKS),1,2) cc_code,
bic.COMPONENT_QUANTITY *bic1.COMPONENT_QUANTITY *bic2.COMPONENT_QUANTITY *bic3.COMPONENT_QUANTITY*bic4.COMPONENT_QUANTITY*bic5.COMPONENT_QUANTITY require_qty,
bic5.EFFECTIVITY_DATE,
BIC5.DISABLE_DATE,
bic4.disable_date dis_date1,
bic3.disable_date dis_date2,
bic2.disable_date dis_date3,
bic1.disable_date dis_date4,
bic.disable_date dis_date5,
to_date(null) dis_date6,
null,
msi5.FIXED_LEAD_TIME request_id
,nvl(cst.item_cost,0) item_cost,''
from
mtl_system_items top,
bom.BOM_BILL_OF_MATERIALS bom,
bom.BOM_INVENTORY_COMPONENTS bic,
mtl_system_items msi,
bom.BOM_BILL_OF_MATERIALS bom1,
APPS.BOM_INVENTORY_COMPONENTS bic1,
INV.MTL_SYSTEM_ITEMS MSI1,
bom.BOM_BILL_OF_MATERIALS bom2,
APPS.BOM_INVENTORY_COMPONENTS bic2,
INV.MTL_SYSTEM_ITEMS MSI2,
bom.BOM_BILL_OF_MATERIALS bom3,
APPS.BOM_INVENTORY_COMPONENTS bic3,
INV.MTL_SYSTEM_ITEMS MSI3,
bom.BOM_BILL_OF_MATERIALS bom4,
APPS.BOM_INVENTORY_COMPONENTS bic4,
INV.MTL_SYSTEM_ITEMS MSI4,
bom.BOM_BILL_OF_MATERIALS bom5,
APPS.BOM_INVENTORY_COMPONENTS bic5,
INV.MTL_SYSTEM_ITEMS MSI5,
cst_item_costs cst
where
top.inventory_item_id=bom.ASSEMBLY_ITEM_ID
and top.organization_id=bom.organization_id
AND bic.BILL_SEQUENCE_ID=bom.common_BILL_SEQUENCE_ID
AND bom.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=bom.organization_id
AND top.organization_id=bom1.organization_id
AND bom1.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=BOM1.ASSEMBLY_ITEM_ID
AND bic1.BILL_SEQUENCE_ID=bom1.common_BILL_SEQUENCE_ID
AND bic1.COMPONENT_ITEM_ID=MSI1.INVENTORY_ITEM_ID
AND MSI1.ORGANIZATION_ID=bom1.organization_id
AND top.organization_id=bom2.organization_id
AND bom2.ALTERNATE_BOM_DESIGNATOR is null
AND bic1.COMPONENT_ITEM_ID=BOM2.ASSEMBLY_ITEM_ID
AND bic2.BILL_SEQUENCE_ID=bom2.common_BILL_SEQUENCE_ID
AND bic2.COMPONENT_ITEM_ID=MSI2.INVENTORY_ITEM_ID
AND MSI2.ORGANIZATION_ID=bom2.organization_id
AND top.organization_id=bom3.organization_id
AND bom3.ALTERNATE_BOM_DESIGNATOR is null
AND bic2.COMPONENT_ITEM_ID=BOM3.ASSEMBLY_ITEM_ID
AND bic3.BILL_SEQUENCE_ID=bom3.common_BILL_SEQUENCE_ID
AND bic3.COMPONENT_ITEM_ID=MSI3.INVENTORY_ITEM_ID
AND MSI3.ORGANIZATION_ID=bom3.organization_id
AND top.organization_id=bom4.organization_id
AND bom4.ALTERNATE_BOM_DESIGNATOR is null
AND bic3.COMPONENT_ITEM_ID=BOM4.ASSEMBLY_ITEM_ID
AND bic4.BILL_SEQUENCE_ID=bom4.common_BILL_SEQUENCE_ID
AND bic4.COMPONENT_ITEM_ID=MSI4.INVENTORY_ITEM_ID
AND MSI4.ORGANIZATION_ID=bom4.organization_id
AND top.organization_id=bom5.organization_id
AND bom5.ALTERNATE_BOM_DESIGNATOR is null
AND bic4.COMPONENT_ITEM_ID=BOM5.ASSEMBLY_ITEM_ID
AND bic5.BILL_SEQUENCE_ID=bom5.common_BILL_SEQUENCE_ID
AND bic5.COMPONENT_ITEM_ID=MSI5.INVENTORY_ITEM_ID
AND MSI5.ORGANIZATION_ID=bom5.organization_id
and msi5.inventory_item_id=cst.inventory_item_id
and msi5.organization_id=cst.organization_id
and cst.cost_type_id=2
and ( BIC.DISABLE_DATE IS NULL ) AND
( BIC1.DISABLE_DATE IS NULL ) AND
( BIC2.DISABLE_DATE IS NULL ) AND
( BIC3.DISABLE_DATE IS NULL ) AND
( BIC4.DISABLE_DATE IS NULL) AND
( BIC5.DISABLE_DATE IS NULL )
and NVL(bic.WIP_SUPPLY_TYPE,MSI.WIP_SUPPLY_TYPE)=6
AND NVL(bic1.WIP_SUPPLY_TYPE,MSI1.WIP_SUPPLY_TYPE)=6
AND NVL(bic2.WIP_SUPPLY_TYPE,MSI2.WIP_SUPPLY_TYPE)=6
AND NVL(bic3.WIP_SUPPLY_TYPE,MSI3.WIP_SUPPLY_TYPE)=6
AND NVL(bic4.WIP_SUPPLY_TYPE,MSI4.WIP_SUPPLY_TYPE)=6
AND NVL(bic5.WIP_SUPPLY_TYPE,MSI5.WIP_SUPPLY_TYPE)!=6
and top.organization_id=255
and top.segment1=v1.item_num ;
commit;
-- 7 level
insert into ch_bom_expend1029
select top.organization_id,
bom.ASSEMBLY_ITEM_ID TOP_ITEM_ID,
top.segment1 top_item_num,
top.description top_desc,
bom6.ASSEMBLY_ITEM_ID ASSEMBLY_ITEM_ID,
bic6.ITEM_num ITEM_NO,
7 item_level,
bic6.COMPONENT_ITEM_ID COMPONENT_ITEM_ID,
msi6.segment1 item_num,
msi6.description item_desc,
msi6.unit_weight,
msi6.WEIGHT_UOM_CODE,
msi6.primary_uom_code,
NVL(bic6.WIP_SUPPLY_TYPE,MSI6.WIP_SUPPLY_TYPE) WIP_SUPPLY_TYPE,
msi6.PLANNING_MAKE_BUY_CODE,
bic.COMPONENT_QUANTITY*bic1.COMPONENT_QUANTITY*bic2.COMPONENT_QUANTITY*bic3.COMPONENT_QUANTITY*bic4.COMPONENT_QUANTITY*bic5.COMPONENT_QUANTITY*bic6.COMPONENT_QUANTITY COMPONENT_QUANTITY,
bic6.COMPONENT_QUANTITY PER_UNIT_QTY,
bic6.COMPONENT_REMARKS COMPONENT_REMARKS,
substr(ltrim(bic6.COMPONENT_REMARKS),1,2) cc_code,
bic.COMPONENT_QUANTITY*bic1.COMPONENT_QUANTITY*bic2.COMPONENT_QUANTITY*bic3.COMPONENT_QUANTITY*bic4.COMPONENT_QUANTITY*bic5.COMPONENT_QUANTITY*bic6.COMPONENT_QUANTITY require_qty,
bic1.EFFECTIVITY_DATE,
BIC1.DISABLE_DATE,
bic5.disable_date dis_date1,
bic4.disable_date dis_date2,
bic3.disable_date dis_date3,
bic2.disable_date dis_date4,
bic1.disable_date dis_date5,
bic.disable_date dis_date6,
null,
msi6.FIXED_LEAD_TIME request_id
,nvl(cst.item_cost,0) item_cost,''
from
mtl_system_items top,
bom.BOM_BILL_OF_MATERIALS bom,
bom.BOM_INVENTORY_COMPONENTS bic,
mtl_system_items msi,
bom.BOM_BILL_OF_MATERIALS bom1,
APPS.BOM_INVENTORY_COMPONENTS bic1,
INV.MTL_SYSTEM_ITEMS MSI1,
bom.BOM_BILL_OF_MATERIALS bom2,
APPS.BOM_INVENTORY_COMPONENTS bic2,
INV.MTL_SYSTEM_ITEMS MSI2,
bom.BOM_BILL_OF_MATERIALS bom3,
APPS.BOM_INVENTORY_COMPONENTS bic3,
INV.MTL_SYSTEM_ITEMS MSI3,
bom.BOM_BILL_OF_MATERIALS bom4,
APPS.BOM_INVENTORY_COMPONENTS bic4,
INV.MTL_SYSTEM_ITEMS MSI4,
bom.BOM_BILL_OF_MATERIALS bom5,
APPS.BOM_INVENTORY_COMPONENTS bic5,
INV.MTL_SYSTEM_ITEMS MSI5,
bom.BOM_BILL_OF_MATERIALS bom6,
APPS.BOM_INVENTORY_COMPONENTS bic6,
INV.MTL_SYSTEM_ITEMS MSI6,
cst_item_costs cst
where
top.inventory_item_id=bom.ASSEMBLY_ITEM_ID
and top.organization_id=bom.organization_id
AND bic.BILL_SEQUENCE_ID=bom.common_BILL_SEQUENCE_ID
AND bom.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=bom.organization_id
AND top.organization_id=bom1.organization_id
AND bom1.ALTERNATE_BOM_DESIGNATOR is null
AND bic.COMPONENT_ITEM_ID=BOM1.ASSEMBLY_ITEM_ID
AND bic1.BILL_SEQUENCE_ID=bom1.common_BILL_SEQUENCE_ID
AND bic1.COMPONENT_ITEM_ID=MSI1.INVENTORY_ITEM_ID
AND MSI1.ORGANIZATION_ID=bom1.organization_id
AND top.organization_id=bom2.organization_id
AND bom2.ALTERNATE_BOM_DESIGNATOR is null
AND bic1.COMPONENT_ITEM_ID=BOM2.ASSEMBLY_ITEM_ID
AND bic2.BILL_SEQUENCE_ID=bom2.common_BILL_SEQUENCE_ID
AND bic2.COMPONENT_ITEM_ID=MSI2.INVENTORY_ITEM_ID
AND MSI2.ORGANIZATION_ID=bom2.organization_id
AND top.organization_id=bom3.organization_id
AND bom3.ALTERNATE_BOM_DESIGNATOR is null
AND bic2.COMPONENT_ITEM_ID=BOM3.ASSEMBLY_ITEM_ID
AND bic3.BILL_SEQUENCE_ID=bom3.common_BILL_SEQUENCE_ID
AND bic3.COMPONENT_ITEM_ID=MSI3.INVENTORY_ITEM_ID
AND MSI3.ORGANIZATION_ID=bom3.organization_id
AND top.organization_id=bom4.organization_id
AND bom4.ALTERNATE_BOM_DESIGNATOR is null
AND bic3.COMPONENT_ITEM_ID=BOM4.ASSEMBLY_ITEM_ID
AND bic4.BILL_SEQUENCE_ID=bom4.common_BILL_SEQUENCE_ID
AND bic4.COMPONENT_ITEM_ID=MSI4.INVENTORY_ITEM_ID
AND MSI4.ORGANIZATION_ID=bom4.organization_id
AND top.organization_id=bom5.organization_id
AND bom5.ALTERNATE_BOM_DESIGNATOR is null
AND bic4.COMPONENT_ITEM_ID=BOM5.ASSEMBLY_ITEM_ID
AND bic5.BILL_SEQUENCE_ID=bom5.common_BILL_SEQUENCE_ID
AND bic5.COMPONENT_ITEM_ID=MSI5.INVENTORY_ITEM_ID
AND MSI5.ORGANIZATION_ID=bom5.organization_id
AND top.organization_id=bom6.organization_id
AND bom6.ALTERNATE_BOM_DESIGNATOR is null
AND bic5.COMPONENT_ITEM_ID=BOM6.ASSEMBLY_ITEM_ID
AND bic6.BILL_SEQUENCE_ID=bom6.common_BILL_SEQUENCE_ID
AND bic6.COMPONENT_ITEM_ID=MSI6.INVENTORY_ITEM_ID
AND MSI6.ORGANIZATION_ID=bom6.organization_id
and msi6.inventory_item_id=cst.inventory_item_id
and msi6.organization_id=cst.organization_id
and cst.cost_type_id=2
and ( BIC.DISABLE_DATE IS NULL ) AND
( BIC1.DISABLE_DATE IS NULL ) AND
( BIC2.DISABLE_DATE IS NULL ) AND
( BIC3.DISABLE_DATE IS NULL ) AND
( BIC4.DISABLE_DATE IS NULL ) AND
( BIC5.DISABLE_DATE IS NULL ) AND
( BIC6.DISABLE_DATE IS NULL )
and NVL(bic.WIP_SUPPLY_TYPE,MSI.WIP_SUPPLY_TYPE)=6
AND NVL(bic1.WIP_SUPPLY_TYPE,MSI1.WIP_SUPPLY_TYPE)=6
AND NVL(bic2.WIP_SUPPLY_TYPE,MSI2.WIP_SUPPLY_TYPE)=6
AND NVL(bic3.WIP_SUPPLY_TYPE,MSI3.WIP_SUPPLY_TYPE)=6
AND NVL(bic4.WIP_SUPPLY_TYPE,MSI4.WIP_SUPPLY_TYPE)=6
AND NVL(bic5.WIP_SUPPLY_TYPE,MSI5.WIP_SUPPLY_TYPE)=6
AND NVL(bic6.WIP_SUPPLY_TYPE,MSI6.WIP_SUPPLY_TYPE)!=6
and top.organization_id=255
and top.segment1=v1.item_num
;
commit;
exception
when others then
null;
end;
end loop;
close c1;
end;
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12122734/viewspace-481710/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12122734/viewspace-481710/