客制的展BOM程式

逻辑如下：成品物料为第一层，从成品开始展，每展一层用数字标示，然后插入临时表，然后从临时表的最后一层又开始展，循环，展开你所需要的层次，然后临时表中所有的就是这个成品下面的BOM，每个都对应到它的上一阶是哪个物料，然后对这个临时表做递归查询，即可对这个BOM做一个分明清楚的层级展示

create or replace procedure JW_BOM_EXPLOSE_TEMP_PROC(P_ORG_ID IN NUMBER,

                                                     P_ASS_ITEM_ID IN NUMBER,
                                                     P_MAX_LEVEL IN NUMBER      
                                                 ) is
                                                 
   N_BOM_LEVEL NUMBER:=1;    
   n_num_sub number;   
   V_SUBTITUTE VARCHAR2(1000);
   v_REFERENCE VARCHAR2(2000);                                     
     CURSOR C_structures(V_ORG_ID NUMBER,V_ASS_ITEM_ID NUMBER) IS
     select BILL_SEQUENCE_ID
     from bom_structures_b
     WHERE ASSEMBLY_ITEM_ID = V_ASS_ITEM_ID
       AND ORGANIZATION_ID = V_ORG_ID;
     V_structures C_structures%rowtype;
       
     CURSOR C_BOM_TEMP(V_LEVEL NUMBER) IS
      SELECT INVENTORY_ITEM_ID,
             ORGANIZATION_ID,
             COMPONENT_SEQUENCE_ID
        FROM JW_BOM_EXPLOSE_TEMP
       WHERE BOM_LEVEL = V_LEVEL;
    V_BOM_TEMP C_BOM_TEMP%rowtype;
    
     CURSOR C_BOM_TEMP_SUB IS
       SELECT ROWID,
             INVENTORY_ITEM_ID,
             COMPONENT_SEQUENCE_id,
            ORGANIZATION_ID
       FROM JW_BOM_EXPLOSE_TEMP;
    V_BOM_TEMP_SUB C_BOM_TEMP_SUB%ROWTYPE;
    
     CURSOR C_SUB(V_COMPONENT_SEQ NUMBER) IS
     SELECT BSC.SUBSTITUTE_COMPONENT_ID,
            MSIB.SEGMENT1         
       FROM BOM_SUBSTITUTE_COMPONENTS BSC,MTL_SYSTEM_ITEMS_B MSIB
      WHERE BSC.SUBSTITUTE_COMPONENT_ID = MSIB.INVENTORY_ITEM_ID
        AND MSIB.ORGANIZATION_ID = P_ORG_ID
        AND COMPONENT_SEQUENCE_ID = V_COMPONENT_SEQ;
     V_SUB C_SUB%ROWTYPE;
     
     CURSOR C_REF(V_COMPONENT_SEQ NUMBER) IS
     SELECT COMPONENT_REFERENCE_DESIGNATOR         
       FROM BOM_REFERENCE_DESIGNATORS  
      WHERE COMPONENT_SEQUENCE_ID = V_COMPONENT_SEQ;
     V_REF C_REF%ROWTYPE;
begin
 DELETE FROM  JW_BOM_EXPLOSE_TEMP;
 COMMIT;
 /*INSERT INTO JW_BOM_EXPLOSE_TEMP      -----成品
 (ASSEMBLY_ITEM_ID,
    ASSEMBLY_ITEM_NUMBER,
    ASSEMBLY_DESCRIPTION,
    ORGANIZATION_ID,
    BOM_LEVEL,
    component_sequence_id)
  SELECT P_ASS_ITEM_ID,
          MSIB.SEGMENT1,
          MSIB.DESCRIPTION,
          P_ORG_ID,
          N_BOM_LEVEL BOM_LEVEL,
          bsb.bill_sequence_id
     FROM bom_structures_b   BSB,
          MTL_SYSTEM_ITEMS_B MSIB
    WHERE BSB.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
      AND BSB.ASSEMBLY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
      AND BSB.ALTERNATE_BOM_DESIGNATOR IS NULL
      AND BSB.ORGANIZATION_ID = P_ORG_ID
      AND BSB.ASSEMBLY_ITEM_ID = P_ASS_ITEM_ID;
  COMMIT;*/
 N_BOM_LEVEL:= N_BOM_LEVEL + 1;
 INSERT INTO JW_BOM_EXPLOSE_TEMP    ----虚拟键
   (INVENTORY_ITEM_ID,
    INVENTORY_ITEM_NUMBER,
    INVENTORY_DESCRIPTION,
    ORGANIZATION_ID,
    BOM_LEVEL,
    WIP_SUPPLY_TYPE,
    QUANTITY,
    EFFECTIVITY_DATE,
    ASSEMBLY_ITEM_ID,
    ASSEMBLY_ITEM_NUMBER,
    ASSEMBLY_DESCRIPTION,
    COMPONENT_SEQUENCE_ID,
    yield_factor,
    operation_seq_num)
   SELECT BCB.COMPONENT_ITEM_ID,
          MSIB1.SEGMENT1,
          MSIB1.DESCRIPTION,
          P_ORG_ID,
          N_BOM_LEVEL BOM_LEVEL,
          BCB.WIP_SUPPLY_TYPE,
          BCB.component_quantity,
          BCB.EFFECTIVITY_DATE,
          BSB.ASSEMBLY_ITEM_ID,
          MSIB2.SEGMENT1,
          MSIB2.DESCRIPTION,
          BCB.COMPONENT_SEQUENCE_ID,
          bcb.component_yield_factor,
          bcb.operation_seq_num
     FROM bom_components_b   BCB,
          bom_structures_b   BSB,
          MTL_SYSTEM_ITEMS_B MSIB1,
          MTL_SYSTEM_ITEMS_B MSIB2
    WHERE BCB.BILL_SEQUENCE_ID = BSB.BILL_SEQUENCE_ID
      AND BCB.COMPONENT_ITEM_ID = MSIB1.INVENTORY_ITEM_ID
      AND BSB.ORGANIZATION_ID = MSIB1.ORGANIZATION_ID
      AND BSB.ASSEMBLY_ITEM_ID = MSIB2.INVENTORY_ITEM_ID
      AND BSB.ORGANIZATION_ID = MSIB2.ORGANIZATION_ID
      AND BCB.EFFECTIVITY_DATE <=SYSDATE
      AND (BCB.DISABLE_DATE > SYSDATE OR BCB.DISABLE_DATE IS NULL)
      AND BSB.ALTERNATE_BOM_DESIGNATOR IS NULL
      AND BSB.ORGANIZATION_ID = P_ORG_ID
      AND BSB.ASSEMBLY_ITEM_ID = P_ASS_ITEM_ID;
  COMMIT;
  NULL;
 
  for i in 2..P_MAX_LEVEL loop
  if C_BOM_TEMP%isopen then    
    --close cursor   
      close C_BOM_TEMP;  
  end if;
  open C_BOM_TEMP(N_BOM_LEVEL);     --虚拟键下的原物料
  Loop   
     Fetch C_BOM_TEMP into V_BOM_TEMP;  
     Exit when C_BOM_TEMP%notfound;
        
        ----------------------------------------下阶料
        if C_structures%isopen then    
        --close cursor   
           close C_structures;  
        end if;
        OPEN C_structures(V_BOM_TEMP.ORGANIZATION_ID,V_BOM_TEMP.INVENTORY_ITEM_ID);
        LOOP
             Fetch C_structures into V_structures;  
             Exit when C_structures%notfound;
             
                 INSERT INTO JW_BOM_EXPLOSE_TEMP
                 (INVENTORY_ITEM_ID,
                  INVENTORY_ITEM_NUMBER,
                  INVENTORY_DESCRIPTION,
                  ORGANIZATION_ID,
                  BOM_LEVEL,
                  WIP_SUPPLY_TYPE,
                  QUANTITY,
                  EFFECTIVITY_DATE,
                  ASSEMBLY_ITEM_ID,
                  ASSEMBLY_ITEM_NUMBER,
                  ASSEMBLY_DESCRIPTION,
                  COMPONENT_SEQUENCE_ID,
                  yield_factor,
                  operation_seq_num)
                 SELECT BCB.COMPONENT_ITEM_ID,
                        MSIB1.SEGMENT1,
                        MSIB1.DESCRIPTION,
                        P_ORG_ID,
                        N_BOM_LEVEL + 1  BOM_LEVEL,
                        BCB.WIP_SUPPLY_TYPE,
                        BCB.component_quantity,
                        BCB.EFFECTIVITY_DATE,
                        BSB.ASSEMBLY_ITEM_ID,
                        MSIB2.SEGMENT1,
                        MSIB2.DESCRIPTION,
                        BCB.COMPONENT_SEQUENCE_ID,
                        bcb.component_yield_factor,
                        bcb.operation_seq_num
                   FROM bom_components_b   BCB,
                        bom_structures_b   BSB,
                        MTL_SYSTEM_ITEMS_B MSIB1,
                        MTL_SYSTEM_ITEMS_B MSIB2
                  WHERE BCB.BILL_SEQUENCE_ID = BSB.BILL_SEQUENCE_ID
                    AND BCB.COMPONENT_ITEM_ID = MSIB1.INVENTORY_ITEM_ID
                    AND BSB.ORGANIZATION_ID = MSIB1.ORGANIZATION_ID
                    AND BSB.ASSEMBLY_ITEM_ID = MSIB2.INVENTORY_ITEM_ID
                    AND BSB.ORGANIZATION_ID = MSIB2.ORGANIZATION_ID
                    AND BCB.EFFECTIVITY_DATE <= SYSDATE
                    AND (BCB.DISABLE_DATE > SYSDATE OR BCB.DISABLE_DATE IS NULL)
                    AND BSB.ALTERNATE_BOM_DESIGNATOR IS NULL
                    AND BSB.BILL_SEQUENCE_ID = V_structures.Bill_Sequence_Id;
                
        END LOOP;
         
  end loop;
  N_BOM_LEVEL := N_BOM_LEVEL + 1;
  COMMIT;
  end loop;
 
  if C_BOM_TEMP_SUB%isopen then    
    --close cursor   
      close C_BOM_TEMP_SUB;  
  end if;
  open C_BOM_TEMP_SUB;     --虚拟键下的原物料
  Loop   
     Fetch C_BOM_TEMP_SUB into V_BOM_TEMP_SUB;  
     Exit when C_BOM_TEMP_SUB%notfound;
     ----------------------------------------替代料
        select count(*)
          into n_num_sub
          from BOM_SUBSTITUTE_COMPONENTS
         where COMPONENT_SEQUENCE_ID = V_BOM_TEMP_SUB.Component_Sequence_Id;
       if n_num_sub > 0  then
           insert into JW_BOM_EXPLOSE_TEMP
            (inventory_item_id,
             inventory_item_number,
             inventory_description,
             bom_level
             )
            SELECT BSsC.SUBSTITUTE_COMPONENT_ID,
                   MSIB.SEGMENT1,
                   MSIB.DESCRIPTION,
                   0
              FROM BOM_SUBSTITUTE_COMPONENTS BsSC, MTL_SYSTEM_ITEMS_B MSIB
             WHERE BSsC.SUBSTITUTE_COMPONENT_ID = MSIB.INVENTORY_ITEM_ID
               AND MSIB.ORGANIZATION_ID = P_ORG_ID
               AND BSsC.COMPONENT_SEQUENCE_ID = V_BOM_TEMP_SUB.Component_Sequence_Id;
           end if;
           
       V_SUBTITUTE:=NULL;
        if c_sub%isopen then
           close c_sub;
        end if;   
        open c_sub(V_BOM_TEMP_SUB.Component_Sequence_Id);
        loop
       
      
            fetch c_sub into v_sub;
            exit when c_sub%notfound;
             
          
           V_SUBTITUTE:=V_SUBTITUTE || v_sub.SEGMENT1 || ',';
         
        end loop;

       ------------------------------------------------------位标
       v_REFERENCE:=NULL;
        if c_ref%isopen then
           close c_ref;
        end if;   
        open c_ref(V_BOM_TEMP_SUB.Component_Sequence_Id);
        loop
           fetch c_ref into v_ref;
            exit when c_ref%notfound;
             v_REFERENCE:=v_REFERENCE || v_ref.COMPONENT_REFERENCE_DESIGNATOR || ',';
        end loop;
        
         UPDATE JW_BOM_EXPLOSE_TEMP
           SET substitute_item_NUMBER =  V_SUBTITUTE,
               reference_code = v_REFERENCE
         WHERE ROWID = V_BOM_TEMP_SUB.ROWID;
        commit;
  end loop;
  commit;
  null;
  EXCEPTION
    WHEN OTHERS THEN
       RAISE_APPLICATION_ERROR(-20001,SQLCODE || ',' || SQLERRM);
end JW_BOM_EXPLOSE_TEMP_PROC;

层级展示代码如下：

SELECT      boms.organization_id,  --org_id
            LEVEL + 1 boms_level,  --阶  成品第一阶，虚拟件第二阶
            boms.INVENTORY_ITEM_NUMBER INVENTORY_ITEM_NUMBER,  --料号
            lpad('  ', (LEVEL) * 3, '  ') || boms.INVENTORY_ITEM_NUMBER ind_INVENTORY_ITEM_NUMBER,
            boms.INVENTORY_DESCRIPTION,         --料号描述
            boms.quantity component_quantity,  --数量
            boms.yield_factor,                   --良率
            DECODE(operation_seq_num,10,'SMT',20,'DIP',30,'ASS',40,'PK',50,'COM',1),
            boms.substitute_item_NUMBER,  --替代料，用逗号隔开
            boms.reference_code            --位标，用逗号隔开
       from JW_BOM_EXPLOSE_TEMP boms   --展BOM临时表
      where bom_level <> 0 --0代表此料是替代料
        and wip_supply_type <> 6 --6代表此料是虚拟件
      start with boms.assembly_item_id = 1368
       connect by prior boms.inventory_item_id = boms.assembly_item_id