create or replace procedure sap_sm_up2
is
m_MANDT varchar2(3); ---client
m_GROUP_ID varchar2(30); ---GROUP_ID
m_WERKS varchar2(4); -----？紅
--m_LGORT varchar2(4); -----祇砯？?？絏
--m_UMLGO varchar2(4); -----?Μ？?？絏
---m_MATNR varchar2(18); -----？？腹絏
--m_CHARG varchar2(10); -----?腹
m_sapreadflag number(2); -----SAP HAS READ THE UPLOAD'S DATA.
m_has_send_count number(4); ----癘魁琌？？?肚？Interface table
z_sqlcode varchar2(200);
z_sqlerrm varchar2(4000);
pc_wvlscount number(7);
pmpc_wvlscount number(7);
ztmm22_count number(7);
cursor bf_sdupdata is
select distinct MANDT,GROUP_ID,WERKS from MM_ZTMM22 where trans_ite_flag is null;
begin
m_MANDT:=null;
m_GROUP_ID:=null;
m_WERKS:=null;
--m_LGORT:=null;
--m_UMLGO:=null;
--m_CHARG:=null;
m_sapreadflag:=0;
m_has_send_count:=0;
z_sqlcode:='';
z_sqlerrm:='';
open bf_sdupdata;
loop
fetch bf_sdupdata into m_MANDT,m_GROUP_ID,m_WERKS;
exit when bf_sdupdata%notfound;
pc_wvlscount:=0;
pmpc_wvlscount:=0;
ztmm22_count:=0;
select sum(menge) into ztmm22_count from mm_ztmm22 t where t.group_id=m_GROUP_ID;
if ztmm22_count is null then
ztmm22_count:=0;
end if;
if substr(m_GROUP_ID,1,1)='V' then ---lcm2 mdl Group id
select sum(a.pnl_count) into pc_wvlscount from pc_wvls a where a.fac_id=4 and a.group_id =TRIM(m_GROUP_ID);
if pc_wvlscount is null then
pc_wvlscount:=0;
end if;
elsif substr(m_GROUP_ID,1,1)='M' then ---pnl Group id
select sum(pnl_count) into pmpc_wvlscount from pmpc_wvls where group_id =TRIM(m_GROUP_ID);
if pmpc_wvlscount is null then
pmpc_wvlscount:=0;
end if;
else
null;
end if;
----
----
if pc_wvlscount=ztmm22_count and substr(m_GROUP_ID,1,1)='V' then
select count(MANDT) into m_has_send_count from MM_ZTMM22@SAP_PRD_MM where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS=m_WERKS;
if m_has_send_count>0 then
select count(MANDT) into m_sapreadflag from MM_ZTMM22@SAP_PRD_MM where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS=m_WERKS and zflag='Y';
if m_sapreadflag>0 then
null;
else
delete MM_ZTMM22@SAP_PRD_MM where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS=m_WERKS ;
insert into MM_ZTMM22@SAP_PRD_MM
(MANDT,
GROUP_ID,
BUDAT,
WERKS,
LGORT,
UMLGO,
BWART,
RSNUM,
MATNR,
MAKTX,
CHARG,
ZPALLETID,
BWTAR,
MENGE,
MEINS,
SGTXT,
WEMPF,
MBLNR,
AEDAT,
AEZEIT,
ZFLAG,
ZSTATUS,
ZERMSG,
READDATE,
CLDATETIME,
ZFLAG2,GRBEW)
(select
MANDT,
GROUP_ID,
BUDAT,
WERKS,
LGORT,
UMLGO,
BWART,
RSNUM,
MATNR,
MAKTX,
CHARG,
ZPALLETID,
BWTAR,
MENGE,
MEINS,
SGTXT,
WEMPF,
MBLNR,
AEDAT,
AEZEIT,
ZFLAG,
ZSTATUS,
ZERMSG,
READDATE,
sysdate+rownum/(24*3600),
ZFLAG2,GRBEW from MM_ZTMM22 where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS=m_WERKS );
UPDATE MM_ZTMM22@SAP_PRD_MM SET BWTAR=null
where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS='230V';
end if;
else
insert into MM_ZTMM22@SAP_PRD_MM
(MANDT,
GROUP_ID,
BUDAT,
WERKS,
LGORT,
UMLGO,
BWART,
RSNUM,
MATNR,
MAKTX,
CHARG,
ZPALLETID,
BWTAR,
MENGE,
MEINS,
SGTXT,
WEMPF,
MBLNR,
AEDAT,
AEZEIT,
ZFLAG,
ZSTATUS,
ZERMSG,
READDATE,
CLDATETIME,
ZFLAG2,GRBEW)
(select
MANDT,
GROUP_ID,
BUDAT,
WERKS,
LGORT,
UMLGO,
BWART,
RSNUM,
MATNR,
MAKTX,
CHARG,
ZPALLETID,
BWTAR,
MENGE,
MEINS,
SGTXT,
WEMPF,
MBLNR,
AEDAT,
AEZEIT,
ZFLAG,
ZSTATUS,
ZERMSG,
READDATE,
sysdate+rownum/(24*3600),
ZFLAG2,GRBEW from MM_ZTMM22 where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS=m_WERKS );
UPDATE MM_ZTMM22@SAP_PRD_MM SET BWTAR=null
where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS='230V';
end if;
update MM_ZTMM22 set TRANS_ITE_FLAG='Y',TRANS_DATE=SYSDATE where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS=m_WERKS ;
end if;
----
----++++++++++
if substr(m_GROUP_ID,1,1)='M' and pmpc_wvlscount=ztmm22_count then
select count(MANDT) into m_has_send_count from MM_ZTMM22@SAP_PRD_MM where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS=m_WERKS ;
if m_has_send_count>0 then
select count(MANDT) into m_sapreadflag from MM_ZTMM22@SAP_PRD_MM where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS=m_WERKS and zflag='Y';
if m_sapreadflag>0 then
null;
else
delete MM_ZTMM22@SAP_PRD_MM where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS=m_WERKS ;
insert into MM_ZTMM22@SAP_PRD_MM
(MANDT,
GROUP_ID,
BUDAT,
WERKS,
LGORT,
UMLGO,
BWART,
RSNUM,
MATNR,
MAKTX,
CHARG,
ZPALLETID,
BWTAR,
MENGE,
MEINS,
SGTXT,
WEMPF,
MBLNR,
AEDAT,
AEZEIT,
ZFLAG,
ZSTATUS,
ZERMSG,
READDATE,
CLDATETIME,
ZFLAG2,GRBEW)
(select
MANDT,
GROUP_ID,
BUDAT,
WERKS,
LGORT,
UMLGO,
BWART,
RSNUM,
MATNR,
MAKTX,
CHARG,
ZPALLETID,
BWTAR,
MENGE,
MEINS,
SGTXT,
WEMPF,
MBLNR,
AEDAT,
AEZEIT,
ZFLAG,
ZSTATUS,
ZERMSG,
READDATE,
sysdate+rownum/(24*3600),
ZFLAG2,GRBEW from MM_ZTMM22 where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS=m_WERKS );
UPDATE MM_ZTMM22@SAP_PRD_MM SET BWTAR=null
where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS='230V';
end if;
else
insert into MM_ZTMM22@SAP_PRD_MM
(MANDT,
GROUP_ID,
BUDAT,
WERKS,
LGORT,
UMLGO,
BWART,
RSNUM,
MATNR,
MAKTX,
CHARG,
ZPALLETID,
BWTAR,
MENGE,
MEINS,
SGTXT,
WEMPF,
MBLNR,
AEDAT,
AEZEIT,
ZFLAG,
ZSTATUS,
ZERMSG,
READDATE,
CLDATETIME,
ZFLAG2,GRBEW)
(select
MANDT,
GROUP_ID,
BUDAT,
WERKS,
LGORT,
UMLGO,
BWART,
RSNUM,
MATNR,
MAKTX,
CHARG,
ZPALLETID,
BWTAR,
MENGE,
MEINS,
SGTXT,
WEMPF,
MBLNR,
AEDAT,
AEZEIT,
ZFLAG,
ZSTATUS,
ZERMSG,
READDATE,
sysdate+rownum/(24*3600),
ZFLAG2,GRBEW from MM_ZTMM22 where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS=m_WERKS );
UPDATE MM_ZTMM22@SAP_PRD_MM SET BWTAR=null
where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS='230V';
end if;
update MM_ZTMM22 set TRANS_ITE_FLAG='Y',TRANS_DATE=SYSDATE where
MANDT=m_MANDT and GROUP_ID=m_GROUP_ID and WERKS=m_WERKS ;
end if;
----+++++++++++
end loop;
close bf_sdupdata;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
z_sqlcode := SQLCODE;
z_sqlerrm := SQLERRM;
ROLLBACK;
insert into SAP_MMUP_LOG (FAC_ID,trans_date,log_content,err_code)
values
(0,SYSDATE,z_sqlerrm,z_sqlcode);
commit;
end sap_sm_up2;
==========我的第一篇博文，测试一下。