使用Oracle里的包Bompexpl将BOM展开

declare   
    V_L_SEQ_ID NUMBER; -- bom list sequence id bom_lists_s.nextval
        V_GROUP_ID NUMBER; -- explode group id bom_explosion_temp_s.nextval
        V_LEVEL_EXPLODE NUMBER:= 9; -- levels to explode
        V_bom_or_eng  NUMBER:= 1; -- BOM is 1, ENG is 2
        V_IMPL_FLAG NUMBER:= 1; -- 'Implemented only' or 'Implemented or not''
        V_EXPLODE_OPTION VARCHAR2(10):= 2; --''All' , Current
        V_err_msg varchar(20);
        V_err_code varchar(20);
     --2).Explore BOM
    BEGIN
      SELECT BOM_LISTS_S.NEXTVAL INTO V_L_SEQ_ID FROM DUAL;
      INSERT INTO BOM_LISTS(SEQUENCE_ID,ASSEMBLY_ITEM_ID) VALUES(V_L_SEQ_ID,'53289');
      SELECT BOM_EXPLOSION_TEMP_S.NEXTVAL INTO V_GROUP_ID FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('GROUP ID:' || TO_CHAR(V_GROUP_ID));
     Bompexpl.explosion_report
     (org_id => 81,
     order_by =>1,      --:P_ORDER_BY_TYPE,
     list_id =>V_l_seq_id,
     grp_id =>V_GROUP_ID,
     session_id => -1,
     levels_to_explode => V_LEVEL_EXPLODE,    --:P_EXPLOSION_LEVEL,
     bom_or_eng => V_bom_or_eng,
     impl_flag => V_IMPL_FLAG,      --:P_IMPL_FLAG,
     explode_option => V_EXPLODE_OPTION,    --:P_EXPLODE_OPTION_TYPE,
     module => 2,      --:P_MODULE,
     cst_type_id => -1,
     std_comp_flag => -1,
     expl_qty => 1,      --:P_EXPLOSION_QUANTITY,
     report_option => -1,
     req_id => 0,        --:P_CONC_REQUEST_ID,
     lock_flag => -1,
     rollup_option => -1,
     alt_rtg_desg => '',
     alt_desg =>'',    --P_ALTERNATE_DESG,    --:P_ALTERNATE_DESG,
     rev_date =>'',        --:P_REVISION_DATE,
     err_msg =>V_err_msg,
     error_code =>V_err_code,
     verify_flag =>0,
     cst_rlp_id =>0,
     plan_factor_flag =>2,      -- :P_PLAN_FACTOR_FLAG,
     incl_lt_flag =>2);
   END;
  
  
              SELECT ORGANIZATION_ID,
                     COMPONENT_ITEM_ID CII,
                     COMPONENT_QUANTITY CQ,
                     COMPONENT_SEQUENCE_ID CSI,
                     PLAN_LEVEL,
                     ITEM_TYPE IT
              FROM BOM_EXPLOSION_VIEW A
              WHERE
              ( ((TRUNC(EFFECTIVITY_DATE)<=TRUNC(Sysdate)) AND
                (TRUNC(sysdate)<TRUNC(DISABLE_DATE))
                )
                OR (DISABLE_DATE IS NULL) OR (Sysdate IS NULL)
              )
              AND PLAN_LEVEL > 0
              ORDER BY SORT_ORDER;  
             
             
select * from BOM_EXPLOSION_VIEW

工艺路线,部门,资源:

Select distinct Bd.Department_Id,Bd.Department_Code,Br.Resource_Code,Bor.Assembly_Item_Id,Mtl.Segment1,Res.Usage_Rate_Or_Amount,Res.Usage_Rate_Or_Amount_Inverse
From Bom_Operational_Routings Bor,
           Bom_Operation_Sequences  Bos,
           Bom_Operation_Resources  Res,
           Bom_Departments          Bd,
           Bom_Resources            Br,
           mtl_system_items         Mtl
     Where Bor.Organization_Id =81
       --And Bor.Assembly_Item_Id =74694
       And Bos.Routing_Sequence_Id = Bor.Routing_Sequence_Id
       And Res.Operation_Sequence_Id = Bos.Operation_Sequence_Id
       And Res.Schedule_Flag = 1 --可计划
       And Bd.Organization_Id = 81
       And Bd.Department_Id = Bos.Department_Id
       And Br.Organization_Id =81
       And Br.Resource_Id = Res.Resource_Id
       and Bos.Disable_date is null
       And Mtl.Inventory_Item_id=Bor.Assembly_Item_Id

 

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/Baconxu/archive/2008/03/25/2217207.aspx