BOM查看多个物料下的子物料组件

--KOL_BOM_ITEMS_TMP
--KOL_HEADER_ITEMS

--删除表
--begin
--execute immediate 'drop table KOL_BOM_ITEMS_TMP';   -- 最终数据表
--execute immediate 'drop table KOL_HEADER_ITEMS ';   -- 条件表
--end;

--用于存储所要数据的表
CREATE TABLE APPS.KOL_HEADER_ITEMS
(
  ITEM_ID      NUMBER(5),
  ITEM_NUMBER  NUMBER(10),
  ITEM         VARCHAR2(50 BYTE)
)

--用于装ITEMS以作为条件的表
CREATE TABLE APPS.KOL_BOM_ITEMS_TMP
(
  LEVEL_ID           NUMBER,
  ASSEMBLY_ITEM_ID   NUMBER,
  COMPONENT_ITEM_ID  NUMBER,
  ORGANIZATION_ID    NUMBER                     NOT NULL,
  HEADER_ITEM        CHAR(16 BYTE),
  PARENT_ITEM        VARCHAR2(40 BYTE),
  CHILDREN_ITEM      VARCHAR2(40 BYTE),
  children_qty       number
)

--把所要展BOM的物料插入此表作为条件表处理
begin

execute immediate 'truncate table kol_header_items';

insert into kol_header_items(item) values('44600006');
insert into kol_header_items(item) values('44600007');
insert into kol_header_items(item) values('8800GS2-12-010');
insert into kol_header_items(item) values('8800GS2-14-000');
insert into kol_header_items(item) values('8800GS2-15-000');

commit;

end;

--==========================================================

--从条件表APPS.KOL_HEADER_ITEMS中取出每个ITEM循环去展BOM
DECLARE
   v_item_no   VARCHAR2 (20);
   v_org_id    NUMBER (5);
   v_insert_count number(10);

   CURSOR cur_items
   IS
      SELECT item
      FROM kol_header_items;
BEGIN
   v_insert_count := 0;
   v_item_no   := '';
   v_org_id    := 190;
   execute immediate 'truncate table kol_bom_items_tmp';

   FOR cur IN cur_items
   LOOP
      v_item_no   := cur.item;

      --dbms_output.put_line(v_item_no);

      begin
        INSERT INTO kol_bom_items_tmp(LEVEL_ID, ASSEMBLY_ITEM_ID, COMPONENT_ITEM_ID, ORGANIZATION_ID, HEADER_ITEM, PARENT_ITEM, CHILDREN_ITEM, children_qty)
         -- bom list
         SELECT LEVEL,
                lst.assembly_item_id,
                lst.component_item_id,
                lst.organization_id,
                v_item_no header_item,
                lst.parent_item,
                lst.children_item,
                lst.COMPONENT_QUANTITY
         FROM (SELECT bom.assembly_item_id,
                      bic.component_item_id,
                      bom.organization_id,
                      bic.COMPONENT_QUANTITY,
                      (SELECT msi.segment1
                       FROM inv.mtl_system_items_b msi
                       WHERE msi.organization_id = v_org_id
                             AND msi.inventory_item_id = bom.assembly_item_id)
                         parent_item,
                      (SELECT msi.segment1
                       FROM inv.mtl_system_items_b msi
                       WHERE msi.organization_id = v_org_id
                             AND msi.inventory_item_id = bic.component_item_id)
                         children_item
               FROM bom_bill_of_materials bom, bom_inventory_components bic
               WHERE bom.bill_sequence_id = bic.bill_sequence_id
                     AND BIC.DISABLE_DATE  IS NULL    -- 物料组件是否失效：NULL有效 / NOT NULL失效
                     AND bom.organization_id = v_org_id) lst
         START WITH (lst.assembly_item_id =
                        (SELECT msi.inventory_item_id
                         FROM inv.mtl_system_items_b msi
                         WHERE msi.organization_id = v_org_id
                               AND msi.segment1 = v_item_no))         --15395)
         CONNECT BY lst.assembly_item_id = PRIOR lst.component_item_id;
         commit;
      exception when others then
         dbms_output.put_line(sqlcode || ' - ' || sqlerrm);
      end;
   END LOOP;
   commit;
   select count(1) into v_insert_count from kol_bom_items_tmp;
   dbms_output.put_line('kol_bom_items_tmp Insert count : ' || v_insert_count);
   --  select * from kol_bom_items_tmp;
END;