STO单没有取进FP，IN_SAELS_ORDER表无，但IN_PO_STO有

描述 ：业务反馈STO单没有取进FP,经检查IN_SALES_ORDER表没有此单数据，但在IN_PO_STO表却有

 

跟进如下：

1、检查IN_PO_STO表是否有数据

SELECT * FROM IN_PO_STO WHERE PO_ID ='4700016874';

2、检查SAP_SALES_ORDER 存储过程插入IN_SALES_ORDER表为什么没有写进去；

经检查TEMP_IN_SALES_ORDER_STO是没有数据的

 SELECT *
        FROM TEMP_IN_SALES_ORDER_STO OE
             --JOIN SAP_MARA SM ON OE.ITEM_ID = SM.MATNR
             LEFT JOIN SAP_CUSTOMER_INSPECT CI
                ON OE.ITEM_ID = CI.MATNR AND OE.CUSTOMER_ID = CI.KUNNR
             JOIN STG.IN_PO_STO SI 
                ON LTRIM(OE.SO_ID,'0')=LTRIM(SI.PO_ID,'0') AND OE.SO_LINE_ID = SI.PO_LINE_NO
       WHERE  
       NOT EXISTS
                        (SELECT NULL
                           FROM IN_SALES_ORDER
                          WHERE OE.SO_ID || '_' || OE.SO_LINE_ID =
                                      LTRIM(IN_SALES_ORDER.SO_ID,'0')
                                   || '_'
                                   || IN_SALES_ORDER.SO_LINE_ID)AND 
         TRIM(OE.CUSTOMER_ID) IS NOT NULL 
         AND TRIM(OE.SITE_ID) IS NOT NULL
        and OE.SO_ID='4700016874';

3、TEMP_IN_SALES_ORDER_STO没有数据，数据来源于SSIS中的SapToSapTemp_ForPO.dtsx

--INSERT INTO STG.TEMP_IN_SALES_ORDER_STO(SO_ID, SO_LINE_ID, POSNR, FAMILY_ID, CUSTOMER_ID, VC_OR_NOT, ITEM_ID, SITE_ID, CREATION_DATE, SALES_GROUP, CHANNEL_ID, DISTRIBUTION, PURCHASE_GROUP, REQUIRE_DATE, PROMISE_DATE, QTY, EX_PROMISE_DATE, STATUS, TOEC_OR_NOT)
SELECT SZ.EBELN AS SO_ID,
LTRIM(SUBSTR(SZ.POSNR,1,6),'0')||'_'||LTRIM(SUBSTR(SZ.POSNR,7,4),'0') AS SO_LINE_ID,
SZ.POSNR AS POSNR,
SZ.MVGR2 AS FAMILY_ID,
SZ.KUNNR AS CUSTOMER_ID,
SZ.KZKFG AS VC_OR_NOT,
SZ.MATNR AS ITEM_ID,
LTRIM(SE.LIFNR,'R') AS SITE_ID,
TO_DATE(SUBSTR(SZ.ERDAT,1,4)||'-'||SUBSTR(SZ.ERDAT,5,2)||'-'||SUBSTR(SZ.ERDAT,7,2),'YYYY-MM-DD') AS CREATION_DATE,
SZ.VKORG AS SALES_GROUP,
SZ.VTWEG AS CHANNEL_ID,
SZ.BZIRK AS DISTRIBUTION,
SZ.EKORG AS PURCHASE_GROUP,
TO_DATE(SUBSTR(SZ.VDATU,1,4)||'-'||SUBSTR(SZ.VDATU,5,2)||'-'||SUBSTR(SZ.VDATU,7,2),'YYYY-MM-DD') AS REQUIRE_DATE,
TO_DATE(SUBSTR(SZ.EDATU,1,4)||'-'||SUBSTR(SZ.EDATU,5,2)||'-'||SUBSTR(SZ.EDATU,7,2),'YYYY-MM-DD') AS PROMISE_DATE,
SZ.WMENG AS QTY,
CASE WHEN SZ.ZEDATU = '00000000' THEN NULL ELSE TO_DATE(SUBSTR(SZ.ZEDATU,1,4)||'-'||SUBSTR(SZ.ZEDATU,5,2)||'-'||SUBSTR(SZ.ZEDATU,7,2),'YYYY-MM-DD') END AS EX_PROMISE_DATE,
SZ.ZSTATE AS STATUS,
SZ.ZTO_EC AS TOEC_OR_NOT
FROM SAPSR3.ZTSD_008_1@SAP_SEP SZ JOIN SAPSR3.EKKO@SAP_SEP SE ON SZ.MANDT = SE.MANDT AND SZ.EBELN = SE.EBELN
WHERE SZ.EKORG  IN ('2090','2510') 
  AND TRIM(SZ.ZSFCH) IS NULL
/*
AND (SZ.ZSTATE IN ('R','E') OR TO_DATE(SUBSTR(SZ.ERDAT,1,4)||'-'||SUBSTR(SZ.ERDAT,5,2)||'-'||SUBSTR(SZ.ERDAT,7,2),'YYYY-MM-DD')=TO_DATE(SYSDATE,'YYYY-MM-DD'))
*/
AND SZ.MANDT = '800'
AND TRIM(SZ.KZKFG) IS NULL
AND LTRIM(SE.LIFNR,'R') LIKE '2%'

UNION ALL

SELECT SZ.EBELN AS SO_ID,
LTRIM(SUBSTR(SZ.POSNR,1,6),'0')||'_'||LTRIM(SUBSTR(SZ.POSNR,7,4),'0') AS SO_LINE_ID,
SZ.POSNR AS POSNR,
SZ.MVGR2 AS FAMILY_ID,
SZ.KUNNR AS CUSTOMER_ID,
SZ.KZKFG AS VC_OR_NOT,
SZ.MATNR AS ITEM_ID,
LTRIM(SE.LIFNR,'R') AS SITE_ID,
TO_DATE(SUBSTR(SZ.ERDAT,1,4)||'-'||SUBSTR(SZ.ERDAT,5,2)||'-'||SUBSTR(SZ.ERDAT,7,2),'YYYY-MM-DD') AS CREATION_DATE,
SZ.VKORG AS SALES_GROUP,
SZ.VTWEG AS CHANNEL_ID,
SZ.BZIRK AS DISTRIBUTION,
SZ.EKORG AS PURCHASE_GROUP,
TO_DATE(SUBSTR(SZ.VDATU,1,4)||'-'||SUBSTR(SZ.VDATU,5,2)||'-'||SUBSTR(SZ.VDATU,7,2),'YYYY-MM-DD') AS REQUIRE_DATE,
TO_DATE(SUBSTR(SZ.EDATU,1,4)||'-'||SUBSTR(SZ.EDATU,5,2)||'-'||SUBSTR(SZ.EDATU,7,2),'YYYY-MM-DD') AS PROMISE_DATE,
SZ.WMENG AS QTY,
CASE WHEN SZ.ZEDATU = '00000000' THEN NULL ELSE TO_DATE(SUBSTR(SZ.ZEDATU,1,4)||'-'||SUBSTR(SZ.ZEDATU,5,2)||'-'||SUBSTR(SZ.ZEDATU,7,2),'YYYY-MM-DD') END AS EX_PROMISE_DATE,
SZ.ZSTATE AS STATUS,
SZ.ZTO_EC AS TOEC_OR_NOT
FROM SAPSR3.ZTSD_008_1@SAP_SEP SZ JOIN SAPSR3.EKKO@SAP_SEP SE ON SZ.MANDT = SE.MANDT AND SZ.EBELN = SE.EBELN
WHERE SZ.EKORG='2020' 
  AND TRIM(SZ.ZSFCH) IS NULL
AND SZ.MANDT = '800'
AND TRIM(SZ.KZKFG) IS NULL
AND SZ.EBELN LIKE '52%'
AND SZ.EBELN='4700016874';

SELECT * FROM  SAPSR3.ZTSD_008_1@SAP_SEP WHERE EBELN='4700016874';

 

第二条SQL语句可以看出数据是在凌晨1点才写进去的，但取数的时候是在12：03分执行的，所以执行第一条SQL语句是没有数据写进去的