truncate table SAP_SO_STO1_STO2; truncate table SAP_STO1_STO2; INSERT INTO STG.SAP_SO_STO1_STO2(VBELN, POSNR, ZZPOSNR, STO1_NO, STO1_LINE_NO, STO2_NO, STO2_LINE_NO) SELECT VBELN, POSNR, ZZPOSNR, EBELN_IMAGE, EBELP_IMAGE, EBELN, EBELP FROM SAPSR3.ZTSD_002@SAP_SEP INSERT INTO STG.SAP_STO1_STO2(STO1_NO, STO1_LINE_NO, STO2_NO, STO2_LINE_NO) SELECT EBELN_IMAGE,EBELP_IMAGE,EBELN,EBELP FROM SAPSR3.ZTSD_004@SAP_SEP;
select * from SAPSR3.ZTSD_008_1@SAP_SEP where ebeln='4700017139'; SELECT * FROM SAP_STO1_STO2 WHERE STO1_NO='4700017139'; --STO1_NO='4700005411' select * from SAP_SO_STO1_STO2 WHERE STO1_NO='4700017139'; select * from SEINEEBS.FP_SALE_REQ_LINES_RPY@EC where ERP_SO_ID='4700017139';
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 ('4700017140');--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 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 ('4700017139');