請問如何展ＢＯＭ到底層？

bin2bb

如果是用的Oracle ERP,好象有现成的包可以做这个事情

hphubei

最初由 bin2bb 发布
[B]如果是用的Oracle ERP,好象有现成的包可以做这个事情 [/B]

是的

hphubei

最初由 isbb 发布
[B]我也在苏州,看来苏州用oracle的人不少啊,大多数是台资,各位是吗? [/B]

最初由 robincao 发布
[B]呵呵，偶要是苏州的！ [/B]

苏州还是满多的了

jszlq428

不过偶下个月就要去浙江混了

seanes123

WITH temptab(IXITM,IXLITM, IXKIT,IXKITL,IXQNTY,IXUM,IXSCRP,IXEFFT) AS  
(SELECT root.IXITM, root.IXLITM, root.IXKIT,root.IXKITL,root.IXQNTY,root.IXUM,
root.IXSCRP,root.IXEFFT  FROM proddta.f3002 root  
WHERE  ixkitl in('100875200100') and IXTBM='M'
UNION ALL  SELECT sub.IXITM, sub.IXLITM, sub.IXKIT,sub.IXKITL,sub.IXQNTY,
sub.IXUM,sub.IXSCRP,sub.IXEFFT FROM proddta.f3002 sub, temptab super
WHERE sub.IXKIT = super.IXITM )
SELECT IXITM,IXLITM, IXKIT,IXKITL,IXQNTY,IXUM,IXSCRP FROM temptab WHERE IXEFFT >=107011

zhuguangwei

楼上用的是系统里TABLE吗？还是自己建立的

eyrewu

SAP-R3展开多阶BOM原码(ORACLE)
create or replace  
PROCEDURE ZS_BOMLIST
( MATNR_IN    IN      VARCHAR2,
   WERKS_IN     IN       VARCHAR2,
   STLAN_IN     in       varchar2,
   STLNR_IN     IN       VARCHAR2,
   STLAL_IN     IN       VARCHAR2,
  ref_cursor   OUT      sys_refcursor   /* 这个sys_refcursor类型在SYS.STANDARD包中 */
)
is
STR  varchar2(500);
i NUMBER;
y number;
begin
STR:='truncate TABLE ZT_BOMLIST_TMP';
execute immediate STR;
I:=1;
insert into ZT_BOMLIST_TMP(MATNR,WERKS,STLAN,STLNR,STLAL,STLTY,STKOZ,BMEIN,BMENG,STLKN,DATUV,ANDAT,ANNAM,AEDAT,AENAM,IDNRK,BISMT,MAKTX,POSTP,POSNR,MEINS,MENGE,AUSCH,POTX1,POTX2,ALPGR,ALPRF,EWAHR,SANKA,JC,MATNR_MJ)
select MATNR,WERKS,STLAN,STLNR,STLAL,STLTY,STKOZ,BMEIN,BMENG,STLKN,DATUV,ANDAT,ANNAM,AEDAT,AENAM,IDNRK,BISMT,MAKTX,POSTP,POSNR,MEINS,MENGE,AUSCH,POTX1,POTX2,ALPGR,ALPRF,EWAHR,SANKA,I as JC,MATNR_IN
from ZV_BOMLIST
where MATNR=MATNR_IN and WERKS=WERKS_IN and STLAN=STLAN_IN;
LOOP
I:=I+1;
insert into ZT_BOMLIST_TMP(MATNR,WERKS,STLAN,STLNR,STLAL,STLTY,STKOZ,BMEIN,BMENG,STLKN,DATUV,ANDAT,ANNAM,AEDAT,AENAM,IDNRK,BISMT,MAKTX,POSTP,POSNR,MEINS,MENGE,AUSCH,POTX1,POTX2,ALPGR,ALPRF,EWAHR,SANKA,JC,MATNR_MJ)
SELECT A.MATNR,A.WERKS,A.STLAN,A.STLNR,A.STLAL,A.STLTY,A.STKOZ,A.BMEIN,A.BMENG,A.STLKN,A.DATUV,A.ANDAT,A.ANNAM,A.AEDAT,
a.AENAM,a.IDNRK,a.BISMT,a.MAKTX,a.POSTP,a.POSNR,a.MEINS,a.MENGE AS MENGE,a.AUSCH,a.POTX1,a.POTX2,a.ALPGR,a.ALPRF,a.EWAHR,a.SANKA,I as JC,a.MATNR
from ZV_BOMLIST a inner join ZT_BOMLIST_TMP b on a.MATNR=b.IDNRK and a.WERKS=b.WERKS and a.STLAN=b.STLAN and JC=(I-1);
-- and STLNR=STLNR_IN and STLAL=STLAL_IN
SELECT count(*) INTO y FROM ZT_BOMLIST_TMP WHERE JC=I;
if Y=0 or I>9 then
  EXIT;
end if;
end LOOP;

  open REF_CURSOR for
    SELECT * FROM ZT_BOMLIST_TMP order by JC,POSNR;

END ZS_BOMLIST;

ainyxiao

用start with...connect by prior ...语句就可以展开

elephantcalf

不就1句sql可以解决的问题吧？

看过其它人写的递归Precedure展，可能还在用这种模式的人不知道oracle数据库早就已经开始提供了这样的 语句

elephantcalf

N年前Oracle就提供了 start with...connect by prior这个语句