ORACLE BOM正反向展開例項

近日有USER要求 抓字尾帶GB/GE的成品機型,將其BOM正展後,對所有物料做反向展BOM,並判斷反展後的所有成品機型是否都是帶GB/GE字尾的,是的就抓出來. 處理步聚: 1,先將字尾帶GB/GE的ITEM放臨時TABLE:TEST_CSITEM 2,依TEST_CSITEM逐筆正展BOM,寫入:TEST_CSBOMS 3,對TEST_CSBOMS中的下階,逐筆反向展到成品,判定成品是不是都帶GB或GE,是的話,就INSERT INTO TEST_CSBOMAA 4,TEST_CSBOMAA就是GB/GE專用的物料.[@more@]select * from test_csboms drop table test_csboms commit declare cursor cur is select * from test_csitem; begin for rec in cur loop insert into test_csboms SELECT distinct msi.organization_id, bb.master_item_id, bb.BOMcc, bb.zhpeijian, bb.zhpeijiandesc, bb.SUB_SEQUENCE, bb.sub_item_id, bb.SUB_ITEM, bb.SUB_ITEMDESC, bb.component_quantity, bb.UOM, bb.LYL_RATE, bb.SUBINV, bb.LOCATION, bb.ITEM_TYPE, bb.BOMtimes, BOR.COMPLETION_SUBINVENTORY ,--入庫, MSI.WIP_SUPPLY_SUBINVENTORY,-- 沖減, MSI.MAXIMUM_ORDER_QUANTITY ,-- 最大批次, MSI.LEAD_TIME_LOT_SIZE ,-- 提前期, MSI.FULL_LEAD_TIME ,-- 製造週期, MSI.FIXED_LOT_MULTIPLIER ,-- 固定增加, MSI.MINIMUM_ORDER_QUANTITY ,--最小批次, MSI.FIXED_ORDER_QUANTITY ,-- 固定定貨量, MSI.FIXED_DAYS_SUPPLY ,-- 固定天數, MSI.PLANNER_CODE -- 計畫員 FROM MTL_SYSTEM_ITEMS MSI, BOM_OPERATIONAL_ROUTINGS BOR, BOM_OPERATION_SEQUENCES BOS, (select distinct aa.lvl BOMcc, msi.inventory_ITEM_ID master_item_id, msi.segment1 zhpeijian, msi.description zhpeijiandesc, aa.item_num SUB_SEQUENCE, msi1.inventory_item_id sub_item_id, msi1.segment1 SUB_ITEM, msi1.description SUB_ITEMDESC, aa.component_quantity , msi1.primary_unit_of_measure UOM, aa.COMPONENT_YIELD_FACTOR LYL_RATE, msi1.wip_supply_subinventory SUBINV, mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' || mil.segment4 LOCATION, decode(msi1.planning_make_buy_code,1,'SUB',2,'PUR') ITEM_TYPE, to_char(msi1.creation_date,'yyyy.mm.dd hh24:mm.ss') BOMtimes, msi1.inventory_item_id from mtl_system_items_b msi, mtl_system_items_b msi1, bom_bill_of_materials bom, bom_inventory_components bic, mtl_item_locations mil, ( select level lvl, bic.bill_sequence_id, bic.component_item_id, bic.component_quantity, bic.OPERATION_SEQ_NUM, bic.COMPONENT_YIELD_FACTOR, bic.COMPONENT_SEQUENCE_ID, bic.item_num, bic.wip_supply_type, bic.supply_subinventory, bic.effectivity_date FROM bom_inventory_components bic where disable_date IS NULL start with bic.bill_sequence_id in (select bill_sequence_id from bom_bill_of_materials bom2, inv.mtl_system_items_b msi where bom2.assembly_item_id = msi.inventory_item_id and bom2.organization_id = msi.organization_id and msi.segment1 =rec.segment1 --'B1601200AW-GBC1' and msi.organization_id =rec.organization_id --803 -- 組織ID and bom2.alternate_bom_designator is null) CONNECT BY bic.bill_sequence_id in prior (SELECT distinct bill_sequence_id FROM bom_bill_of_materials BO, inv.mtl_system_items_b msi WHERE BO.assembly_item_id = bic.component_item_id AND BO.organization_id =rec.organization_id -- 803 --組織ID and bo.ORGANIZATION_ID = msi.ORGANIZATION_ID and bo.ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID and bo.alternate_bom_designator is null and disable_date IS NULL)) aa where msi.organization_id=rec.organization_id --803 --組織ID and msi1.organization_id=rec.organization_id --803 --組織ID and bom.organization_id=rec.organization_id --803 --組織ID and msi.inventory_item_id=bom.assembly_item_id and bom.bill_sequence_id=bic.bill_sequence_id and bic.component_item_id=msi1.inventory_item_id and bic.disable_date is null and aa.bill_sequence_id=bic.bill_sequence_id and aa.component_item_id=msi1.inventory_item_id and mil.inventory_location_id(+)=msi1.wip_supply_locator_id and mil.organization_id(+)=rec.organization_id --803 --組織ID order by aa.lvl, aa.item_num) bb WHERE BOR.ASSEMBLY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID AND BOS.ROUTING_SEQUENCE_ID(+) = BOR.ROUTING_SEQUENCE_ID AND MSI.ORGANIZATION_ID(+) =rec.organization_id --803 -- 組織ID and bor.alternate_routing_designator is null and bb.inventory_item_id=msi.inventory_item_id; end loop; commit; end ; order by bb.BOMcc, bb.zhpeijian, bb.zhpeijiandesc, bb.sub_SEQUENCE, bb.sub_item select * from test_csitem where organization_id=803 select a.* from TBL_TEST a select a.* from TBL_TEST a,tbl_test b where a.id=b.id start with b.id=5 connect by prior b.pid = a.id select * from test_csboms where master_ITEM_ID=642300 A601404RB-GBA3 create table test_csbomaa (organization_id number,master_item_id number, master_item varchar2(20), sub_item_id number,sub_item varchar2(20)) declare nums number; orgid number; mitemid number; mitem varchar2(20); sitemid number; sitem varchar2(20); cursor cur1 is select * from test_csboms; begin for rec1 in cur1 loop orgid:=rec1.organization_id; mitemid:=rec1.master_item_id; mitem:=rec1.zhpeijian; sitemid:=rec1.sub_item_id; sitem:=rec1.sub_item; nums:=0; select count(*) into nums from ( select aa.* from ( select bom.organization_id,msi.segment1 item, bom.ASSEMBLY_ITEM_ID, bic.* from bom_inventory_components bic ,bom_bill_of_materials bom,mtl_system_items_b msi where bom.ORGANIZATION_ID=orgid and bom.ASSEMBLY_ITEM_ID=msi.inventory_item_id and msi.organization_id=1 and bom.bill_sequence_id=bic.bill_sequence_id and bic.COMPONENT_ITEM_ID=sitemid ) aa start with aa.COMPONENT_ITEM_ID=sitemid connect by prior aa.ASSEMBLY_ITEM_ID=aa.COMPONENT_ITEM_ID ) bb where bb.item not like '%-GB%' and bb.item not like '%-GE%'; if nvl(nums,0)=0 then insert into test_csbomaa values (orgid,mitemid,mitem,sitemid,sitem); end if; end loop; commit; end ; select * from bom_bill_of_materials bom