寻源

---AutooutToSap_without_pr            Data TO ec temp
select * from SAPSR3.ZTSD_008_1@SAP_SEP where ebeln='4700005413'
SELECT * FROM SAP_STO1_STO2 WHERE STO2_NO='4700005413' --STO1_NO='4700005411'
select * from SAP_SO_STO1_STO2 WHERE STO2_NO='4700005462'
select * from  SEINEEBS.FP_SALE_REQ_LINES_RPY@EC where ERP_SO_ID='4700005411'



INSERT INTO SEINEEBS.FP_SALE_REQ_LINES_RPY@EC(SO_LINE_ID, 
ERP_SO_ID, 
ERP_SO_LINE_ID, 
SEQ, 
QUANTITY, 
PRO_DATE, 
ITEM_ID, 
SITEID,
FP_DATE)
SELECT --ZS.EBELN||'_'||LTRIM(SUBSTR(ZS.POSNR,1,6),'0')||'_'||LTRIM(ZS.ETENR,'0'),
B.STO1_NO||'_'||LTRIM(SUBSTR(B.STO1_LINE_NO,1,6),'0')||'_'||LTRIM(ZS.ETENR,'0'),
b.STO1_NO,
LTRIM(SUBSTR(B.STO1_LINE_NO,1,6),'0'),
TO_NUMBER(LTRIM(ZS.ETENR,'0')),
ZS.WMENG,
TO_DATE(SUBSTR(ZS.EDATU,1,4)||'-'||SUBSTR(ZS.EDATU,5,2)||'-'||SUBSTR(ZS.EDATU,7,2),'YYYY-MM-DD'),
LTRIM(ZS.MATNR,'0'),
ZS.WERKS,
SYSDATE
FROM SAPSR3.ZTSD_008_1@SAP_SEP ZS
join SAP_STO1_STO2 B on ZS.ebeln=B.STO2_NO   and  LTRIM (ZS.EBELP_STO, '0')=  LTRIM (B.STO2_LINE_NO, '0')
 WHERE EBELN IN ('4700005413');--STO2NO


SELECT --ZS.EBELN||'_'||LTRIM(SUBSTR(ZS.POSNR,1,6),'0')||'_'||LTRIM(ZS.ETENR,'0'),
B.STO1_NO||'_'||LTRIM(SUBSTR(B.STO1_LINE_NO,1,6),'0')||'_'||LTRIM(ZS.ETENR,'0') ,
b.STO1_NO,
LTRIM(SUBSTR(B.STO1_LINE_NO,1,6),'0'),
TO_NUMBER(LTRIM(ZS.ETENR,'0')),
ZS.WMENG,
A.PROMISED_MAT_AVAILDATE,
--TO_DATE(SUBSTR(ZS.EDATU,1,4)||'-'||SUBSTR(ZS.EDATU,5,2)||'-'||SUBSTR(ZS.EDATU,7,2),'YYYY-MM-DD'),
LTRIM(ZS.MATNR,'0'),
ZS.WERKS,
SYSDATE
FROM SAPSR3.ZTSD_008_1@SAP_SEP ZS
join SAP_STO1_STO2 B on ZS.ebeln=B.STO2_NO   and  LTRIM (ZS.EBELP_STO, '0')=  LTRIM (B.STO2_LINE_NO, '0')
join V_OUT_SHIPMENT_PLAN A on A.SO_ID=B.STO2_NO  AND LTRIM (B.STO2_LINE_NO, '0')=SUBSTR (A.SO_LINE_ID, 1,INSTR (A.SO_LINE_ID, '_') - 1)
  where TRIM(B.STO1_NO) IS NOT NULL
 --WHERE ZS.EBELN='4700005541'--STO2NO    

此情况为STO01_STO02
将STO1的数据回写到EC中间表后通知双琴去更新



INSERT INTO SEINEEBS.FP_SALE_REQ_LINES_RPY@EC(SO_LINE_ID, 
ERP_SO_ID, 
ERP_SO_LINE_ID, 
SEQ, 
QUANTITY, 
PRO_DATE, 
ITEM_ID, 
SITEID,
FP_DATE)
SELECT --ZS.EBELN||'_'||LTRIM(SUBSTR(ZS.POSNR,1,6),'0')||'_'||LTRIM(ZS.ETENR,'0'),
B.STO1_NO||'_'||LTRIM(SUBSTR(B.STO1_LINE_NO,1,6),'0')||'_'||LTRIM(ZS.ETENR,'0'),
b.STO1_NO,
LTRIM(SUBSTR(B.STO1_LINE_NO,1,6),'0'),
TO_NUMBER(LTRIM(ZS.ETENR,'0')),
ZS.WMENG,
TO_DATE(SUBSTR(ZS.EDATU,1,4)||'-'||SUBSTR(ZS.EDATU,5,2)||'-'||SUBSTR(ZS.EDATU,7,2),'YYYY-MM-DD'),
LTRIM(ZS.MATNR,'0'),
ZS.WERKS,
SYSDATE
FROM SAPSR3.ZTSD_008_1@SAP_SEP ZS
join SAP_SO_STO1_STO2 B on ZS.ebeln=B.STO2_NO   and  LTRIM (ZS.EBELP_STO, '0')=  LTRIM (B.STO2_LINE_NO, '0')
 WHERE ZS.ebeln='4700005459'--STO2NO


 UPDATE SAPSR3.ZTSD_008_1@SAP_SEP ZS SET  zstate='S' where  EBELN IN ('5000036232');











INSERT INTO SEINEEBS.FP_SALE_REQ_LINES_RPY@EC(SO_LINE_ID, 
ERP_SO_ID, 
ERP_SO_LINE_ID, 
SEQ, 
QUANTITY, 
PRO_DATE, 
ITEM_ID, 
SITEID,
FP_DATE)
SELECT 
ZS.EBELN||'_'||LTRIM(SUBSTR(ZS.POSNR_SO,1,6),'0')||'_'||LTRIM(ZS.ETENR,'0'),
--B.STO1_NO||'_'||LTRIM(SUBSTR(B.STO1_LINE_NO,1,6),'0')||'_'||LTRIM(ZS.ETENR,'0'),
ZS.EBELN,
LTRIM(SUBSTR(ZS.POSNR_SO,1,6),'0'),
TO_NUMBER(LTRIM(ZS.ETENR,'0')),
ZS.WMENG,
TO_DATE(SUBSTR(ZS.EDATU,1,4)||'-'||SUBSTR(ZS.EDATU,5,2)||'-'||SUBSTR(ZS.EDATU,7,2),'YYYY-MM-DD'),
LTRIM(ZS.MATNR,'0'),
ZS.WERKS,
SYSDATE
FROM SAPSR3.ZTSD_008_1@SAP_SEP ZS
 WHERE EBELN ='5000033144'  --2022工厂的，业务员下的是SO，直接在2500组织下的，没有STO