调用ORACLE ERP标准过程展开BOM代码记录

declare
l*}'B+N%rl0  v_item              varchar2(240); -- 要展开的BOM的根编码ITPUB个人空间}j:P2l@ N1O
  v_org               varchar2(3) := 'BSI'; -- BOM展开的组织ITPUB个人空间*@w7RlY(nPbD
  v_cnt               NUMBER := 0;
'H @Jixv/d0  v_err_msg           varchar2(240);
.}*q6V8\]MF0  v_err_code          NUMBER := 0;
.q*Z$]$MO2F:HW0  v_verify_flag       NUMBER := 0; -- DEFAULT 0ITPUB个人空间 b Jtwa0b6Z0FF
  v_online_flag       NUMBER := 2; -- DEFAULT 0ITPUB个人空间Q W)R D j#TP8z
  v_item_id           NUMBER := 0; -- set to inventory_item_id of item to explode
JC't.Mu,u0  v_org_id            NUMBER := 0; -- set to organization_id of item to explode
2t2^ W5o;p`5R0  v_alternate         VARCHAR2(240) := NULL; -- DEFAULT nullITPUB个人空间:gBH `bK}
  v_list_id           NUMBER := 0; -- for reports (default 0)ITPUB个人空间@/x
t\5jG
  v_order_by          NUMBER := 1; -- DEFAULT 1
7LTz}
Nz ]0  v_grp_id            NUMBER := 0; --
R,o.H#@1XVu0  v_session_id        NUMBER := 0; -- DEFAULT 0
p;lJ({ Y7d;]v1G0  v_req_id            NUMBER := 0; -- DEFAULT 0ITPUB个人空间l3j,Y4c_@pp
  v_prgm_appl_id      NUMBER := -1; -- DEFAULT -1ITPUB个人空间9nF!{tWEF
  v_prgm_id           NUMBER := -1; -- DEFAULT -1ITPUB个人空间D5Rlgm5]xh
  v_levels_to_explode NUMBER := 1; -- DEFAULT 1
%~]a-l:a,FE'@?(y k2w0  v_bom_or_eng        NUMBER := 1; -- DEFAULT 1
:C5SKt Y!u`0  v_impl_flag         NUMBER := 1; -- DEFAULT 1ITPUB个人空间~6A mM?-\c
  v_plan_factor_flag  NUMBER := 2; -- DEFAULT 2ITPUB个人空间2f)U L?8G1Y(C
  v_incl_lt_flag      NUMBER := 2; -- DEFAULT 2
`O Zy)iu0  v_explode_option    NUMBER := 2; -- DEFAULT 2ITPUB个人空间xm(i7YL
  v_module            NUMBER := 2; -- DEFAULT 2ITPUB个人空间0lt
GY$E
  v_cst_type_id       NUMBER := 0; -- DEFAULT 0
J#hhj` fhy5_@%uO0  v_std_comp_flag     NUMBER := 0; -- DEFAULT 0ITPUB个人空间Z R@S,fg1v]:W
  v_rev_date          VARCHAR2(240); --
dA#Ef9`7O h0  v_comp_code         VARCHAR2(240) := NULL; --
"X(IY$mu8| g(l_)U0  v_expl_qty          NUMBER := 1; -- DEFAULT 1

  /****************** 要展开BOM的编码******************/
Re8qG#NN+p0  cursor je_cursor isITPUB个人空间Kb+J:X7XM^s-~9q
    select msi.segment1ITPUB个人空间$Py_
fI5s
      from mtl_system_items_b msiITPUB个人空间xg9s Zk_N"n
     where msi.organization_id = 103
4Vr"yH C1}_0       and msi.segment1 like '项目编码';
"I1qw1jJ;nz0  /****************** 要展开BOM的编码******************/

begin

  --清空存放展开BOM目标表的数据
0y`XbDI0  delete from INTF_IMP.DEV_BOM_EXPLOSION_TEMP_SAVE;

  --打开游标进入循环 
u-H[Q)K0  OPEN JE_CURSOR;
r1E6u'{5UP-R0  LOOPITPUB个人空间z;YCU3x
    FETCH JE_CURSOR
,Y9Fom PH_0      INTO v_item;
{uG!E)I0    EXIT WHEN JE_CURSOR%NOTFOUND;
feIHG4['b0  ITPUB个人空间 UHb5K/u)n!UI+P
    -- item revision will be based on this explode date.
{ Ap+A/x\*lB0    -- In this example, we use current date/time
)A2jE5eYr;k%t%y0    v_rev_date := to_char(SYSDATE, 'YYYY-MM-DD:HH24:MI:SS');
r.pgI,_\g+l0 
3V yi0f5conl3S;p3__0    -- 查找组织ID
C]6xy'J%}0    select mp.organization_idITPUB个人空间;?%C(Hz2K-B T2xF
      into v_org_idITPUB个人空间 M
OtQ Bgp
      from MTL_PARAMETERS mpITPUB个人空间3o4Sj$gQ6\h
     where mp.organization_code = 'BSJ';ITPUB个人空间2NM7Lw,W6|.L
 
?[!rH2O`0c0    -- 查找组织ID
Z}{A_coRJ0    select inventory_item_id
9^LA7XP*FVh0      into v_item_id

CU*UgXw[)w0      from MTL_ITEM_FLEXFIELDS
_w
Fl |%T4uK:y#O0     where organization_id = v_org_idITPUB个人空间Zm%G~5T!O;a[5a
       and item_number = v_item;ITPUB个人空间3]9@(?i`v8Y
 
(KV,QvVo#E.xuy0    -- v_grp_id is a unique identifier for this run of the exploderITPUB个人空间'Z+M0s^(`7[
    select bom_explosion_temp_s.nextval into v_grp_id from dual;
` TJS5M;r y0 
{ hnlF!P.@X0    -- determine maximum levels to explode from bom_explosions
.UnT x+^Od;j0    select maximum_bom_levelITPUB个人空间&KNXx!dI]
      into v_levels_to_explode
AJrF:QN*L(I0      from bom_parameters
p*d1y&S(I a#q[I_$B0     where organization_id = v_org_id;
m3A+DUN.A"T? Dy0     ITPUB个人空间eUPh@"E.^2sW
     ITPUB个人空间sOR:nn0ZwK
e
    /*****************************调用展开BOM的过程******************************/ITPUB个人空间L_:J+k"D&[l'e$_?
    apps.bompexpl.exploder_userexit(v_verify_flag,ITPUB个人空间/P4stV ?(h C#t
                                    v_org_id,ITPUB个人空间J/jxm.amr
                                    v_order_by,
"M0|*WV)u*C s0                                    v_grp_id,
1a BT R0VM.{C0                                    v_session_id,
z9K$bI(r_"OG0                                    v_levels_to_explode,
x;w ew1i0F0                                    v_bom_or_eng,
Y&Q W W6mM
|'Mr/Xh,D!g0                                    v_impl_flag,
%|.T{R5u{-z9[a$xa0                                    v_plan_factor_flag,ITPUB个人空间A7h]'B'bd\'}i4CZa{
                                    v_explode_option,
oz TxHX#d0                                    v_module,
gRL"Pr4s]0                                    v_cst_type_id,
]dm
Ob3|0                                    v_std_comp_flag,
.W!Ixmj0                                    v_expl_qty,ITPUB个人空间x5l n,I$h }8C1I&C~!_
                                    v_item_id,
b:WI"|Y^#qWh0                                    v_alternate,
}3\0gB%q-s)u)U0                                    v_comp_code,ITPUB个人空间cmt/T7{a,})eS
                                    v_rev_date,
8j v U;[)q(bM)VT9Yc0                                    v_err_msg,
?/|MpO0                                    v_err_code);
f,w%Eig|F0   /*****************************调用展开BOM的过程******************************/ITPUB个人空间,T'Mk%x xK
  
~C6y8f
[:^S&i*I0   ITPUB个人空间 ^c ?2Y&r4n a?Y
  --把展开的结果写入到BOM展开目标表
'Cjh,hc(I#nx0    insert into INTF_IMP.DEV_BOM_EXPLOSION_TEMP_SAVE
sGz#U.p Ot0      select * from BOM_EXPLOSION_TEMP;ITPUB个人空间j\.`O:r$o
     
j(B};B-c4@bt0  --结束循环ITPUB个人空间4R7yv+z
Uu
  end loop;
)Y7vo6U/uvFw`9t0  close je_cursor;ITPUB个人空间7]H8zJE*o2kJ
  commit;

  /*************************错误处理和导出数据统计**************************/
da7B SA0  if (v_err_code <> 0) thenITPUB个人空间 q(T*~6n)sU!p
    rollback;ITPUB个人空间@p-e9iKT
    dbms_output.put_line('ERROR: ' || v_err_msg);ITPUB个人空间|w4y].\b[U)n
  else
1y6}
ed {b0    select count(*)ITPUB个人空间1L.DI A/E9QT"~C
      into v_cntITPUB个人空间;O/Wd]q4U8Z
      from bom_explosion_temp
+xm_Rw$[ `u0     where group_id = v_grp_id;
SL1Do.a yF'Dxs
}
g0    dbms_output.put_line('Count=' || v_cnt);
p{/ZA%t0    commit;ITPUB个人空间2piS9z!l;K_9oHXj
    dbms_output.put_line('.');
3iP^'R7f*]-?0    dbms_output.put_line('Group Id=' || v_grp_id);ITPUB个人空间%]\Cc*k_I
    dbms_output.put_line('Org =' || v_org);
s c:H ]D0    dbms_output.put_line('Item =' || v_item);ITPUB个人空间)j)P&G\B%PH4?
    dbms_output.put_line('Ord Id =' || v_org_id);ITPUB个人空间[/U8r{ ?0[v
    dbms_output.put_line('Item Id=' || v_item_id);
4m
\j1\Q0    dbms_output.put_line('Levels =' || v_levels_to_explode);ITPUB个人空间-JIzXp`:J@.g e
  end if;ITPUB个人空间"UL2L(OA-u b`2w~
   /*************************错误处理和导出数据统计**************************/ITPUB个人空间i%g ~Jh#cN O9g
   ITPUB个人空间u7`g A0s)t0k\tW
end;ITPUB个人空间2h v8E,Q A

查看结果

select msi.segment1 根编码,
/e Ky,X[nOJ5W0       be.plan_level BOM层数,
5fJ]8gn0       msi2.segment1 装配件编码,
z|M u6_aE0       msi1.segment1 组件编码,
z|k
A3A4KB0       round(be.extended_quantity, 6) 扩展用量,
`8Cw
}YU#Q0       msi1.primary_unit_of_measure 组件单位
1@5PN&uVXG j f0  from mtl_system_items_b                   msi,ITPUB个人空间)o6L5L8l-{ fx
       mtl_system_items_b                   msi1,ITPUB个人空间D/~K SO}
       mtl_system_items_b                   msi2,
.?4T4t7q#Lp"q0       INTF_IMP.DEV_BOM_EXPLOSION_TEMP_SAVE beITPUB个人空间q$m#J(c"lh,a~mI
 where msi.organization_id = 103
J'xl*j/`:@ X9K1Xd0   and msi1.organization_id = 103ITPUB个人空间/eM8}n7q/H:m!J0_7`6Y2C
   and be.organization_id = 103
5{#i;OX(c0   and msi2.organization_id = 103ITPUB个人空间u9eT~Lu
   and msi.inventory_item_id = be.top_item_id
Mj/_P9F:T0   and msi1.inventory_item_id = be.component_item_idITPUB个人空间'p3i5skQ1z
   and msi2.inventory_item_id = be.assembly_item_id
W`^&JCOwV0   and msi.inventory_item_status_code = 'Active'
(Rj+}U2^iT f0   and msi1.inventory_item_status_code = 'Active'
q-\6tOh9q]#\0   and msi2.inventory_item_status_code = 'Active'ITPUB个人空间Leq^Pc h!]#^
   and be.disable_date is null
Bb~7\Zp E0 order by msi.segment1, be.plan_level, msi2.segment1