再谈bom展开[内容转载]

1.标准的BOM API展开程序是采用插入数据到:BOM_EXPLOSION_TEMP 表,但由于此表是全局临时表,只对当前的会话有效,只要会话退出,数据马上被删除,因此需要建立表来保存BOM物料数据;
脚本如下:
 create table BOM_EXPLOSION_TEMP_SAVE as select * from BOM_EXPLOSION_TEMP;

2.写BOM展开程序如下:
(多级清单BOM的展开)
declare
    v_item               varchar2(240) := '251200200168'; -- item to explode
    v_org                varchar2(3) := 'W09';        -- org in which item is exploded

    v_cnt                NUMBER := 0;

    v_err_msg            varchar2(240);
    v_err_code           NUMBER := 0;
    v_verify_flag        NUMBER := 0;  -- DEFAULT 0
    v_online_flag        NUMBER := 2;  -- DEFAULT 0
    v_item_id            NUMBER := 0;  -- set to inventory_item_id of item to explode
    v_org_id             NUMBER := 0;  -- set to organization_id of item to explode
    v_alternate          VARCHAR2(240) := NULL;  -- DEFAULT null
    v_list_id            NUMBER := 0;  -- for reports (default 0)
    v_order_by           NUMBER := 1;  -- DEFAULT 1
    v_grp_id             NUMBER := 0;  --
    v_session_id         NUMBER := 0;  -- DEFAULT 0
    v_req_id             NUMBER := 0;  -- DEFAULT 0
    v_prgm_appl_id       NUMBER := -1;  -- DEFAULT -1
    v_prgm_id            NUMBER := -1;  -- DEFAULT -1
    v_levels_to_explode  NUMBER := 1;   -- DEFAULT 1
    v_bom_or_eng         NUMBER := 1;  -- DEFAULT 1
    v_impl_flag          NUMBER := 1;  -- DEFAULT 1
    v_plan_factor_flag   NUMBER := 2;  -- DEFAULT 2
    v_incl_lt_flag       NUMBER := 2;  -- DEFAULT 2
    v_explode_option     NUMBER := 2;  -- DEFAULT 2
    v_module             NUMBER := 2;  -- DEFAULT 2
    v_cst_type_id        NUMBER := 0;  -- DEFAULT 0
    v_std_comp_flag      NUMBER := 0;  -- DEFAULT 0
    v_rev_date           VARCHAR2(240);    --
    v_comp_code          VARCHAR2(240) := NULL;  --
    v_expl_qty           NUMBER := 1;  -- DEFAULT 1
 
begin

    -- item revision will be based on this explode date.
    -- In this example, we use current date/time
    v_rev_date := to_char(SYSDATE);

    -- Find org_id
    select mp.organization_id into v_org_id
    from MTL_PARAMETERS mp
    where mp.organization_code = v_org;

    -- Find item_id
    select inventory_item_id into v_item_id
    from MTL_ITEM_FLEXFIELDS
    where organization_id = v_org_id and item_number = v_item;

    -- v_grp_id is a unique identifier for this run of the exploder
    select bom_explosion_temp_s.nextval into v_grp_id from dual;

    -- determine maximum levels to explode from bom_explosions
--这里是多级BOM展开,包括BOM头的信息也存在
    select maximum_bom_level into v_levels_to_explode
    from bom_parameters where organization_id = v_org_id;
--可以设置 v_levels_to_explode=1;

    apps.bompexpl.exploder_userexit (
    v_verify_flag,
    v_org_id,
    v_order_by,
    v_grp_id,
    v_session_id,
    v_levels_to_explode,
    v_bom_or_eng,
    v_impl_flag,
    v_plan_factor_flag,
    v_explode_option,
    v_module,
    v_cst_type_id,
    v_std_comp_flag,
    v_expl_qty,
    v_item_id,
    v_alternate,
    v_comp_code,
    v_rev_date,
    v_err_msg,
    v_err_code);

    if ( v_err_code <> 0 ) then
    rollback;
    dbms_output.put_line('ERROR: ' || v_err_msg);
    else
    select count(*) into v_cnt from bom_explosion_temp where group_id=v_grp_id;
    dbms_output.put_line('Count=' || v_cnt);
    commit;
    dbms_output.put_line('.');
    dbms_output.put_line('Group Id=' || v_grp_id);
    dbms_output.put_line('Org    ='  || v_org);
    dbms_output.put_line('Item   =' || v_item);
    dbms_output.put_line('Ord Id =' || v_org_id);
    dbms_output.put_line('Item Id=' || v_item_id);
    dbms_output.put_line('Levels =' || v_levels_to_explode);
 
    end if;
end;
/

3.插入到自己创建的临时表中:
 insert into BOM_EXPLOSION_TEMP_SAVE select * from BOM_EXPLOSION_TEMP;
 commit;

4.推出会话,查看临时表;

 

//

 

SELECT distinct
       bb.BOM层次,
       bb.装配件,
       bb.装配件说明,
       bb.组件序号,
       bb.组件,
       bb.组件说明,
       bb.组件用量,
       bb.单位,
       bb.利用率,
       bb.冲减库房,
       bb.冲减货位,
       bb.属性,
       bb.BOM创建时间,
       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                       BOM层次,
               msi.segment1                 装配件,
               msi.description              装配件说明,
               aa.item_num                  组件序号,
               msi1.segment1                组件,
               msi1.description             组件说明,
               aa.component_quantity        组件用量,
               msi1.primary_unit_of_measure 单位,
               aa.COMPONENT_YIELD_FACTOR    利用率,
               msi1.wip_supply_subinventory 冲减库房,
               mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' ||
               mil.segment4                 冲减货位,
               decode(msi1.planning_make_buy_code,1,'制造',2,'采购') 属性,
               to_char(msi1.creation_date,'yyyy.mm.dd hh24:mm.ss')   BOM创建时间,
               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 =&ITEM_NUMBER
---------------------------------------输入要查询的项目---------------------------------------------
                        and msi.organization_id = &组织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 = &组织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=&组织ID
          and msi1.organization_id=&组织ID
          and bom.organization_id=&组织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(+)=&组织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(+) = &组织ID
   and bor.alternate_routing_designator is null
   and bb.inventory_item_id=msi.inventory_item_id
order by
       bb.BOM层次,
       bb.装配件,
       bb.装配件说明,
       bb.组件序号,
       bb.组件

 

//

 

多组织的展开

多组织的
  select rownum seq_num
      ,top_item
      ,top_org
      ,lpad(to_char(level),decode(level,1,1,level+1),'.') bom_level
      ,bbm.ASSEMBLY_ITEM_ID
      ,msi.segment1    assembly_item   
      ,msi.description assembly_description
      ,bbm.COMMON_ASSEMBLY_ITEM_ID
      ,bic.item_NUM
      ,bbm.COMMON_BILL_SEQUENCE_ID      
      ,bbm.BILL_SEQUENCE_ID
      ,msic.segment1 component_item
      ,msic.description c_item_description
      ,bic.COMPONENT_ITEM_ID
      ,bic.COMPONENT_QUANTITY
      ,msic.primary_unit_of_measure
      ,bic.COMPONENT_YIELD_FACTOR
      ,bic.EFFECTIVITY_DATE
      ,bic.ATTRIBUTE1
      ,bic.ATTRIBUTE2
      ,bic.CHANGE_NOTICE
      ,ood.ORGANIZATION_CODE
      ,ood.ORGANIZATION_NAME
      ,bbm.ORGANIZATION_ID
      ,bbm.COMMON_ORGANIZATION_ID

      
      
      
from bom_bill_of_materials bbm
    ,bom_inventory_components bic
    ,mtl_system_items_b msi
    ,mtl_system_items_b msic
    ,org_organization_definitions ood


where
      bbm.ASSEMBLY_ITEM_ID  = msi.inventory_item_id
  and bbm.ORGANIZATION_ID   = msi.organization_id
  and bic.COMPONENT_ITEM_ID =msic.inventory_item_id
  --and bic.PK2_VALUE        =msic.organization_id
  and bbm.ORGANIZATION_ID =msic.organization_id  
  --and bbm.BILL_SEQUENCE_ID=bic.BILL_SEQUENCE_ID
  and decode(bbm.COMMON_ASSEMBLY_ITEM_ID,null,bbm.BILL_SEQUENCE_ID,bbm.COMMON_BILL_SEQUENCE_ID)=bic.BILL_SEQUENCE_ID
  and (bic.DISABLE_DATE is null or bic.DISABLE_DATE >= sysdate)
  and bic.EFFECTIVITY_DATE <= sysdate
  and ood.ORGANIZATION_ID=msi.organization_id


  
connect by bbm.ASSEMBLY_ITEM_ID = prior bic.COMPONENT_ITEM_ID
       and bbm.ORGANIZATION_ID  = decode (prior bbm.COMMON_ORGANIZATION_ID, null, prior bbm.ORGANIZATION_ID, prior bbm.COMMON_ORGANIZATION_ID)
start with msi.segment1=:P_ITEM

and ood.ORGANIZATION_CODE =:P_ORG