EBS 展开bom api

BOM_EXPLOSION_temp是一个临时表，在展BOM的时候，是一个很有用的表，存放了组成料件层次。默认时是没有记录的，加入下列的语句：

DECLARE

l_group_id NUMBER;

l_error_message VARCHAR2(1000);

l_error_code NUMBER;

BEGIN

SELECT bom.bom_explosion_temp_s.NEXTVAL --取下一个序列

INTO l_group_id

FROM DUAL;

BEGIN

apps.bompexpl.exploder_userexit (verify_flag => NULL,

                                                    org_id => 122,

                                                    order_by => 1,

                                                    grp_id => l_group_id,

                                                    session_id => NULL,

                                                    levels_to_explode => 10, ----展开十层

                                                    bom_or_eng => 1,

                                                    impl_flag => 1,

                                                    plan_factor_flag => NULL,

                                                    explode_option => 2,

                                                    MODULE => 2,

                                                    cst_type_id => NULL,

                                                    std_comp_flag => 2,

                                                    expl_qty => 1,

                                                    item_id =>32523,

                                                    alt_desg => NULL,

                                                    comp_code => NULL,

                                                    rev_date => to_char(sysdate,'yyyy/mm/dd hh24:mi:ss'),--版本日期

                                                    err_msg => l_error_message,

                                                    ERROR_CODE => l_error_code);

COMMIT;

END;

dbms_output.put_line(l_error_code||l_error_message);

END;

---查询

select * from BOM_EXPLOSION_temp;

--说明

Bet.Component_Quantity：是单层bom的单机用量

Bet.Extended_Quantity：是从顶层产品展开到当前组件的整体用量，及顶层产品的用量乘以每一层的bom用量，并且每一层要除以产出率

Bet.Component_Yield_Factor：每个单层bom的产出率

bet.sort_order:是该组件在bom树中的位置，是按照每层bom的需求前补0七位，组成测字符串，可以用来排序也可以用来作为该物料在bom树中的唯一标识。

SELECT Msib_Parent.Segment1 Parent_Item
      ,lpad(Msib_Com.Segment1,LENGTH(Msib_Com.Segment1) + bet.plan_level * 2,'-') Com_Item
      ,Bet.Plan_Level lev
      ,Bet.Operation_Seq_Num seq_num
      ,Bet.Item_Num
      ,Bet.Component_Quantity com_qty
      ,Bet.Extended_Quantity ext_qty
      ,Bet.Component_Yield_Factor Yield
      ,(Bet.Component_Quantity / Bet.Component_Yield_Factor) Yield_Qty
      ,Bet.*
  FROM Bom_Explosion_Temp Bet
      ,Mtl_System_Items_b Msib_Parent
      ,Mtl_System_Items_b Msib_Com
 WHERE 1 = 1
   AND Nvl(Bet.Component_Item_Id, -999) = Msib_Com.Inventory_Item_Id(+)
   AND Nvl(Bet.Organization_Id, -999) = Msib_Com.Organization_Id(+)
   AND Nvl(Bet.Assembly_Item_Id, -999) = Msib_Parent.Inventory_Item_Id(+)
   AND Nvl(Bet.Organization_Id, -999) = Msib_Parent.Organization_Id(+)
   AND Bet.Group_Id = 68844229
 START WITH Bet.Plan_Level = 0
CONNECT BY PRIOR Bet.Component_Item_Id = Bet.Assembly_Item_Id
 --AND PRIOR bet.plan_level = bet.plan_level + 1
 AND PRIOR bet.sort_order = SUBSTR(bet.sort_order,1,LENGTH(bet.sort_order) - 7)
 ORDER SIBLINGS BY Bet.Plan_Level, Bet.Item_Num;

还有一种更加准确的

DECLARE

          err_meg    VARCHAR2(100);

          ERROR_CODE VARCHAR2(100);

BEGIN

  bompxinq.exploder_userexit(verify_flag       => 0,

                             org_id            => 122,

                             order_by          => 1,

                             grp_id            => 0,

                             session_id        => 0,

                             levels_to_explode => 10,

                             bom_or_eng        => 1, -- 1 bom 2 eng

                             impl_flag         => 2, 

                             plan_factor_flag => 1, 

                             explode_option => 1, 

                             module => 2, 

                             cst_type_id   => 2, --average

                             std_comp_flag => 2, 

                             expl_qty         => 1,

                             item_id          => 35463,

                             unit_number_from => NULL,

                             unit_number_to   => NULL,

                             alt_desg  => '',

                             comp_code => '',

                             rev_date  => '', 

                             err_msg    => err_meg,

                             ERROR_CODE => ERROR_CODE);

END;

SELECT *  FROM bom_small_expl_temp