会加入DUMMY的表 IN_ITEM,IN_ITEM_SITE,IN_SALES_ORDER
加入DUMMY的存储过程名为SAP_MATERIAL_SO、
FP_CHANGE_MO_ROUTING的第122行数据IN_ROUTING_HEADER
--存储过程SAP_MATERIAL_SO
SELECT * FROM TEMP_MATERIAL_SALESORDER WHERE DUMMY_ITEM_ID LIKE '000000101%'; SELECT SO_ID,
SO_LINE_ID,
SITEID,
ITEM_ID,
QTYUOM,
ITEM_ID || V_LastChar DUMMY_ITEM_ID,
SO_ID || '_' || SITEID || '_' || SO_LINE_ID DUMMY_BOM_ID,
V_DummyRouting || '_' || SITEID DUMMY_ROUTING_ID
FROM (SELECT SO_ID,
SUBSTR (SO_LINE_ID, 1, INSTR (SO_LINE_ID, '_') - 1)
SO_LINE_ID,
SITEID,
ITEM_ID,
QTYUOM,
ROW_NUMBER ()
OVER (
PARTITION BY SO_ID,
SUBSTR (SO_LINE_ID,
1,
INSTR (SO_LINE_ID, '_') - 1)
ORDER BY SO_LINE_ID)
RN,UDF_MATERIAL_SO,BOMID
FROM IN_SALES_ORDER
WHERE SO_ID='' AND SO_LINE_ID='490_1' AND UDF_MATERIAL_SO = '' AND BOMID IS NULL )
WHERE RN = 1; SELECT SO_ID,
SUBSTR (SO_LINE_ID, 1, INSTR (SO_LINE_ID, '_') - 1)
SO_LINE_ID,
SITEID,
ITEM_ID,
QTYUOM,
ROW_NUMBER ()
OVER (
PARTITION BY SO_ID,
SUBSTR (SO_LINE_ID,
1,
INSTR (SO_LINE_ID, '_') - 1)
ORDER BY SO_LINE_ID)
RN,UDF_MATERIAL_SO,BOMID
FROM IN_SALES_ORDER
WHERE SO_ID='' AND SO_LINE_ID='490_1' AND UDF_MATERIAL_SO = '' AND BOMID IS NULL ;
--SAP_SALES_ORDER SELECT vbap.VBELN,vbep.POSNR,
vbep.POSNR || '_' || vbep.ETENR AS SO_LINE_ID,
vbak.VBTYP,
vbak.KUNNR,
vbap.MATNR,
vbap.WERKS,
vbap.ERDAT,
knvv.BZIRK,
knvv.VTWEG,
vbak.VDATU, SUBSTR (mara.matnr, 1, 9), TRIM (MARA.MTART),VBAP.MATNR || VBAP.WERKS , CASE
WHEN TRIM (MARA.MTART) = 'Z001'
AND SUBSTR (mara.matnr, 1, 9) NOT IN
('', '')
THEN
''
-- add by zhangguipeng on 20170323 增加SO\STO需求出货物料编码开头为402或102，设定为材料订单；
WHEN SUBSTR(VBAP.MATNR,1,9) IN('','') THEN
''
WHEN VBAP.MATNR || VBAP.WERKS IN
(SELECT DISTINCT MATNR || WERKS FROM SAP_MAST)
THEN
''
ELSE
''
END
AS UDF_MATERIAL_SO,-------- IN_BOM_HEADER.BOM_ID,
VBAK.AUART,
CASE WHEN TRIM(VBAK.VKORG) = '' AND TRIM(VBAK.ERNAM) = 'SAPRFC' THEN VBEP.EDATU
ELSE
NVL (so_date.edatu3, so_date.edatu2) END AS edatu2,
--ALTER BY HUANGYANGXIONG ON 20150114 新增栏位判断是否回写SO交期
CASE
WHEN TRIM (SO_DATE.EDATU2) IS NOT NULL
THEN
VBEP.EDATU
ELSE
NULL
END
AS EXT_FORDATE,
ROW_NUMBER ()
OVER (
PARTITION BY VBAK.VBELN,
vbep.POSNR || '_' || vbep.ETENR
ORDER BY
vbap.VBELN,
vbep.POSNR || '_' || vbep.ETENR DESC,
VBEP.EDATU DESC)
rn
FROM SAP_VBAP vbap
JOIN
SAP_VBEP vbep
ON vbap.VBELN = vbep.VBELN
AND vbep.posnr = vbap.posnr
JOIN SAP_VBAK vbak ON vbap.VBELN = vbak.VBELN
JOIN
SAP_KNVV knvv
ON vbak.KUNNR = knvv.KUNNR
AND vbak.vkorg = knvv.vkorg
AND vbak.vtweg = knvv.vtweg
JOIN
SAP_MARC MARC
ON VBAP.MATNR = MARC.MATNR
AND MARC.WERKS = VBAP.WERKS
JOIN SAP_MARA MARA ON VBAP.MATNR = MARA.MATNR
LEFT JOIN
SAP_CUSTOMER_INSPECT
ON VBAK.KUNNR = SAP_CUSTOMER_INSPECT.KUNNR
AND VBAP.MATNR = SAP_CUSTOMER_INSPECT.MATNR
LEFT JOIN
IN_BOM_HEADER
ON VBAP.MATNR = IN_BOM_HEADER.PRODUCT_ID
AND IN_BOM_HEADER.MO_BOM_MARK = ''
AND VBAP.WERKS =
SUBSTR (
IN_BOM_HEADER.bom_id,
INSTR (IN_BOM_HEADER.bom_id, '_') + 1,
4)
LEFT JOIN SAP_SHIPPING_CODE SHIPPING
ON VBAK.VBELN = SHIPPING.VBELN
LEFT JOIN
SAP_OSAL_OUT osal_out
ON vbep.vbeln = osal_out.vbeln
AND VBEP.POSNR = OSAL_OUT.POSNR
AND VBEP.ETENR = OSAL_OUT.ETENR
LEFT JOIN
TEMP_SAP_MSKA MSKA
ON VBEP.VBELN = MSKA.VBELN
AND VBEP.POSNR = MSKA.POSNR
AND VBEP.ETENR = MSKA.ETENR
AND VBAP.MATNR = MSKA.MATNR
LEFT JOIN
SAP_SO_DATE SO_DATE
ON VBEP.VBELN = SO_DATE.VBELN
AND VBEP.POSNR = SO_DATE.POSNR
AND VBEP.ETENR = SO_DATE.ETENR
WHERE TRIM (MARC.LVORM) IS NULL
AND TRIM (MARA.LVORM) IS NULL AND vbap.VBELN='' AND vbep.POSNR=''
SELECT * FROM SAP_MAST WHERE MATNR || WERKS ='';--有问题
SELECT * FROM SAP_MAST WHERE MATNR || WERKS ='';--无 --INSERT INTO STG.SAP_MAST(MATNR,WERKS,STLNR,STLAN,ANDAT,AEDAT)
SELECT DISTINCT
TRIM(MAST.MATNR) as MATNR,
TRIM(MAST.WERKS) as WERKS,
TRIM(MAST.STLNR) as STLNR,
--TRIM(MAST.STLAN) as STLAN,
TRIM(MAST.STLAL) as STLAN,
TO_DATE(SUBSTR(MAST.ANDAT,1,4)||'-'||SUBSTR(MAST.ANDAT,5,2)||'-'||SUBSTR(MAST.ANDAT,7,2),'YYYY-MM-DD')
AS ANDAT,
CASE WHEN AEDAT='' then null
WHEN AEDAT IS NULL THEN NULL
ELSE
TO_DATE(SUBSTR(MAST.AEDAT,1,4)||'-'||SUBSTR(MAST.AEDAT,5,2)||'-'||SUBSTR(MAST.AEDAT,7,2),'YYYY-MM-DD')
END AS AEDAT
FROM SAPSR3.MAST@SAP_SEP MAST
JOIN SAPSR3.ZFP_STLNR@SAP_SEP ZFP_STLNR ON MAST.MANDT = ZFP_STLNR.MANDT
AND MAST.STLNR = ZFP_STLNR.STLNR
WHERE TRIM(MAST.WERKS) IN (SELECT WERKS FROM SAPSR3.ZFM_FP_WERKS@SAP_SEP SZ WHERE SZ.ID IN (1,2,3))
AND MAST.MATNR IN ('',''); SELECT * FROM SAPSR3.ZFP_STLNR@SAP_SEP WHERE MATNR IN ('',''); --INSERT INTO SAPSR3.ZFP_STLNR(MANDT,STLNR,MATNR,WERKS)
SELECT
DISTINCT TRIM(MAST.MANDT) AS MANDT,
TRIM(MAST.STLNR) AS STLNR,
TRIM(MAST.MATNR) AS MATNR,
TRIM(MAST.WERKS) AS WERKS
FROM SAPSR3.MAST@SAP_SEP MAST JOIN SAPSR3.MARC@SAP_SEP MARC ON MAST.MATNR = MARC.MATNR AND MAST.WERKS = MARC.WERKS AND MAST.MANDT = MARC.MANDT
WHERE MAST.MANDT = ''
--AND MAST.WERKS IN (SELECT WERKS FROM SAPSR3.ZFM_FP_WERKS@SAP_SEP SZ WHERE SZ.ID IN (1,2,3))
--AND TRIM(MARC.BESKZ) IN ('E','X')
AND MAST.MATNR IN ('',''); SELECT * FROM SAPSR3.MAST@SAP_SEP WHERE MATNR IN ('','');
有问题的数据为MAST中没有数据导致