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='';

SELECT * FROM SAP_STO1_STO2 WHERE STO1_NO=''; --STO1_NO='4700005411'

select * from SAP_SO_STO1_STO2 WHERE STO1_NO='';

select * from  SEINEEBS.FP_SALE_REQ_LINES_RPY@EC where ERP_SO_ID='';

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),'')||'_'||LTRIM(ZS.ETENR,''),

b.STO1_NO,

LTRIM(SUBSTR(B.STO1_LINE_NO,1,6),''),

TO_NUMBER(LTRIM(ZS.ETENR,'')),

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,''),

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, '')=  LTRIM (B.STO2_LINE_NO, '')

 WHERE EBELN IN ('');--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),'')||'_'||LTRIM(ZS.ETENR,'') ,

b.STO1_NO,

LTRIM(SUBSTR(B.STO1_LINE_NO,1,6),''),

TO_NUMBER(LTRIM(ZS.ETENR,'')),

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,''),

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, '')=  LTRIM (B.STO2_LINE_NO, '')

join V_OUT_SHIPMENT_PLAN A on A.SO_ID=B.STO2_NO  AND LTRIM (B.STO2_LINE_NO, '')=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),'')||'_'||LTRIM(ZS.ETENR,''),

b.STO1_NO,

LTRIM(SUBSTR(B.STO1_LINE_NO,1,6),''),

TO_NUMBER(LTRIM(ZS.ETENR,'')),

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,''),

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, '')=  LTRIM (B.STO2_LINE_NO, '')

 WHERE ZS.ebeln=''--STO2NO

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