WITH T1 AS (--销售订单

  SELECT TT1.VBELN,  TT1.POSNR,  TT1.KWMENG,  TT1.MATNR,  TT1.ARKTX,  IFNULL(DDH.NETWR_CNY, 0) XSJ

    FROM "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.SD/VBAP" AS TT1

   INNER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.SD/VBAK" AS TT2

      ON TT1.VBELN = TT2.VBELN

    LEFT JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.01_DATAMART_LAYER.Z003_SD_ZT/Z003SD_ZT_VBAP02DN" AS DDH

      ON TT1.VBELN = DDH.VBELN

     AND TT1.POSNR = DDH.POSNR

   WHERE TT1.MANDT = '800'

     AND TT1.MANDT = TT2.MANDT

     AND TT2.VBTYP IN ('C')

     AND TT2.AUART IN ('ZOR2')

     AND TT2.VKORG IN ('2020', '1032')

     AND TT2.AUGRU NOT IN ('750', '760')

     --AND TT1.VBELN = '0006306169' AND TT1.POSNR = '000030'

--AND TT1.VBELN = '0006306053' AND TT1.POSNR = '000010'

${if(len(XXDJ) == 0,"","and ltrim(TT1.VBELN ,0) = '" + XXDJ+ "' ")}

       ${if(len(HX) == 0,""," AND TT1.POSNR ='" + HX + "' ")} 

    -- ${if(len(HX) == 0,"","AND TT1.POSNR   ='" + HX + "' ")} 

)

,T81 AS (

  SELECT *

    FROM (SELECT TT10.ORDER_NUMBER,TT10.ORDER_LINE_NUMBER,TT10.PRODUCT_MATERIAL_CODE,

    LPAD(TT10.ORDER_NUMBER, 10, '0') AS "SAP_ORDER_NUMBER",

                 LPAD(TT10.ORDER_LINE_NUMBER, 6, '0') AS "SAP_ORDER_LINE_NUMBER",

                 LPAD(TT10.PRODUCT_MATERIAL_CODE, 18, '0') AS "SAP_PRODUCT_MATERIAL_CODE",

                 TT10.QTY,

                 TT10.PRICE,

                 TT10.PRODUCT_REV,

                 ROW_NUMBER() OVER(PARTITION BY ORDER_NUMBER, ORDER_LINE_NUMBER, PRODUCT_MATERIAL_CODE ORDER BY LPAD(ORDER_NUMBER, 10, '0') DESC, PRODUCT_REV DESC) AS RR

            FROM "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.006_PLM/PLM_DESIGNCOST" AS TT10

           WHERE TT10.PRODUCT_MATERIAL_CODE = TT10.MATERIAL_CODE) T

   WHERE T.RR = 1

)

,T82 AS (

  SELECT T1.VBELN,  T1.POSNR,  T1.KWMENG,  T1.MATNR,  T1.ARKTX,  T1.XSJ,

  T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

  T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",

        T8.QTY, T8.PRICE,  T8.PRODUCT_REV

    FROM T1

    LEFT OUTER JOIN T81 AS T8

      ON T1.VBELN = T8."SAP_ORDER_NUMBER"

     AND T1.POSNR = T8."SAP_ORDER_LINE_NUMBER"

     AND T1.MATNR = T8."SAP_PRODUCT_MATERIAL_CODE"

     

    UNION ALL

    

    SELECT T1.VBELN,  T1.POSNR,  T1.KWMENG,  T1.MATNR,  T1.ARKTX,  T1.XSJ,

  T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

  T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",

        T8.QTY, T8.PRICE,  T8.PRODUCT_REV

    FROM T1

    LEFT OUTER JOIN T81 AS T8

      ON  T1.MATNR = T8."SAP_PRODUCT_MATERIAL_CODE" AND T8."SAP_ORDER_LINE_NUMBER" = '000000'

)

,T83 AS (

SELECT T8.VBELN,  T8.POSNR,  T8.KWMENG,  T8.MATNR,  T8.ARKTX,  T8.XSJ,

  T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

  T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",

        T8.QTY, T8.PRICE,  T8.PRODUCT_REV,

        ROW_NUMBER() OVER(PARTITION BY T8.VBELN, T8.POSNR, T8.PRODUCT_MATERIAL_CODE,T8.MATNR ORDER BY T8."SAP_ORDER_LINE_NUMBER" DESC NULLS LAST) AS RR

FROM T82 AS T8

)

--最新版本的设计BOM

,T84 AS (

SELECT T8.VBELN,  T8.POSNR,  T8.KWMENG,  T8.MATNR,  T8.ARKTX,  T8.XSJ,

  T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

  T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",

        T8.QTY, T8.PRICE,  T8.PRODUCT_REV

FROM T83 AS T8

WHERE T8.RR = 1

)

,T85 AS (

SELECT T8.VBELN,  T8.POSNR,  T8.KWMENG,  T8.MATNR,  T8.ARKTX,  T8.XSJ,

  T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

  T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",

        TD.PARENT_CODE,TD.PRODUCT_REV,

        TD.MATERIAL_CODE,

        TD.QTY,

        IFNULL(TD.QTY,0) * T8.KWMENG AS SUMQTY,

        ROUND(TD.PRICE,3) AS PRICE,

        ROUND(IFNULL(TD.PRICE,0) * T8.KWMENG,3) AS SUMPRICE

FROM T84 AS T8

INNER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.006_PLM/PLM_DESIGNCOST" AS TD

--LEFT OUTER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.006_PLM/PLM_DESIGNCOST" AS TD

ON T8.PRODUCT_MATERIAL_CODE = TD.PRODUCT_MATERIAL_CODE

AND T8.ORDER_NUMBER = TD.ORDER_NUMBER

AND T8.ORDER_LINE_NUMBER = TD.ORDER_LINE_NUMBER

AND TD.MATERIAL_CODE <> ''

AND NOT EXISTS (

SELECT 'X'

FROM "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.006_PLM/PLM_DESIGNCOST" AS TE

WHERE TD.ORDER_NUMBER = TE.ORDER_NUMBER

AND TD.ORDER_LINE_NUMBER = TE.ORDER_LINE_NUMBER

AND TD.PRODUCT_MATERIAL_CODE = TE.PRODUCT_MATERIAL_CODE

AND TD.MATERIAL_CODE = TE.PARENT_CODE

AND 1=2

)

)

--汇总设计BOM物料的数量和价格

,T86 AS (

SELECT T8.VBELN,  T8.POSNR,  T8.KWMENG,  T8.MATNR,  T8.ARKTX,  T8.XSJ,

  T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

  T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",T8.PRODUCT_REV,

        --T8.PARENT_CODE,T8.PRODUCT_REV,

        T8.MATERIAL_CODE,

        LPAD(T8.MATERIAL_CODE, 18, '0') AS "SAP_MATERIAL_CODE",

        SUM(T8.QTY) AS QTY,

        SUM(T8.SUMQTY) AS SUMQTY,

        SUM(T8.PRICE) AS PRICE,

        SUM(T8.SUMPRICE) AS SUMPRICE

    FROM T85 AS T8

GROUP BY T8.VBELN,  T8.POSNR,  T8.KWMENG,  T8.MATNR,  T8.ARKTX,  T8.XSJ,

  T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

  T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",T8.PRODUCT_REV,

        --T8.PARENT_CODE,T8.PRODUCT_REV,

        T8.MATERIAL_CODE

)

--判断是 = 产品 , 组件 , MPN

,T87 AS (

SELECT  T8.VBELN,  T8.POSNR,  T8.KWMENG,  T8.MATNR,  T8.ARKTX,  T8.XSJ,

  T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

  T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",T8.PRODUCT_REV,

        --T8.PARENT_CODE,T8.PRODUCT_REV,

        T8.MATERIAL_CODE,

        T8."SAP_MATERIAL_CODE",

        T8.QTY,

        T8.SUMQTY,

        T8.PRICE,

        T8.SUMPRICE,

        (SELECT max('MPN')

FROM "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.MM/MSUBST" AS MPN

    WHERE T8."SAP_MATERIAL_CODE" = MPN."MATNR" ) AS MATERIAL_TYPEA,

(SELECT max(TE.PARENT_CODE)

FROM "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.006_PLM/PLM_DESIGNCOST" AS TE

WHERE T8.ORDER_NUMBER = TE.ORDER_NUMBER

AND T8.ORDER_LINE_NUMBER = TE.ORDER_LINE_NUMBER

AND T8.PRODUCT_MATERIAL_CODE = TE.PRODUCT_MATERIAL_CODE

AND T8.MATERIAL_CODE = TE.PARENT_CODE ) AS MATERIAL_TYPEB

    FROM T86 AS T8

)

,T881 AS (

SELECT ROW_NUMBER() OVER(PARTITION BY T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE ORDER BY T8.MATERIAL_CODE) AS LINENO,

T8.VBELN,  T8.POSNR,  T8.KWMENG,  T8.MATNR,  T8.ARKTX,  T8.XSJ,

  T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

  T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",T8.PRODUCT_REV,

        --T8.PARENT_CODE,T8.PRODUCT_REV,

        T8.MATERIAL_CODE,

        T8."SAP_MATERIAL_CODE",

        T8.QTY,

        T8.SUMQTY,

        T8.PRICE,

        T8.SUMPRICE,

        --T8.MATERIAL_TYPEA,

        --T8.MATERIAL_TYPEB,

MAP(IFNULL(T8.MATERIAL_TYPEA,''),'MPN','MPN',

        MAP(IFNULL(T8.MATERIAL_TYPEB,''),'','',T8.PRODUCT_MATERIAL_CODE,'产品','组件')

        ) AS MATERIAL_TYPE

    FROM T87 AS T8

)

--MPN的转换后的物料显示出来但数量和价格置为 0 

,T882 AS (

SELECT T8.LINENO, T8.VBELN,  T8.POSNR,  T8.KWMENG,  T8.MATNR,  T8.ARKTX,  T8.XSJ,

  T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

  T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",T8.PRODUCT_REV,

        --T8.PARENT_CODE,T8.PRODUCT_REV,

        LTRIM(MPN."SUBSTMAT",'0') AS MATERIAL_CODE,

        MPN."SUBSTMAT" AS "SAP_MATERIAL_CODE",

        0 AS QTY,

        0 AS SUMQTY,

        0 AS PRICE,

        0 AS SUMPRICE,

        'MPNSON' AS MATERIAL_TYPE

FROM T881 AS T8

INNER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.MM/MSUBST" AS MPN

   ON T8."SAP_MATERIAL_CODE" = MPN."MATNR" 

)

,T88 AS (

SELECT T8.LINENO, T8.VBELN,  T8.POSNR,  T8.KWMENG,  T8.MATNR,  T8.ARKTX,  T8.XSJ,

  T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

  T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",T8.PRODUCT_REV,

        --T8.PARENT_CODE,T8.PRODUCT_REV,

        T8.MATERIAL_CODE,

        T8."SAP_MATERIAL_CODE",

        T8.QTY,

        T8.SUMQTY,

        T8.PRICE,

        T8.SUMPRICE,

        T8.MATERIAL_TYPE

FROM T881 AS T8

UNION ALL

SELECT T8.LINENO, T8.VBELN,  T8.POSNR,  T8.KWMENG,  T8.MATNR,  T8.ARKTX,  T8.XSJ,

  T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

  T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",T8.PRODUCT_REV,

        --T8.PARENT_CODE,T8.PRODUCT_REV,

        T8.MATERIAL_CODE,

        T8."SAP_MATERIAL_CODE",

        T8.QTY,

        T8.SUMQTY,

        T8.PRICE,

        T8.SUMPRICE,

        T8.MATERIAL_TYPE

FROM T882 AS T8

)

--输出PLM

,PLMHZ AS (

SELECT T8.LINENO, T8.VBELN,  T8.POSNR,  T8.KWMENG,  T8.MATNR,  T8.ARKTX,  T8.XSJ,

        T8.ORDER_NUMBER, T8.ORDER_LINE_NUMBER, T8.PRODUCT_MATERIAL_CODE,

        T8."SAP_ORDER_NUMBER", T8."SAP_ORDER_LINE_NUMBER", T8."SAP_PRODUCT_MATERIAL_CODE",T8.PRODUCT_REV,

        --T8.PARENT_CODE,T8.PRODUCT_REV,

        T8.MATERIAL_CODE,

        T8."SAP_MATERIAL_CODE",

        MAKT.MAKTX,

        TO_CHAR(TH.VTEXT) AS TUHAO,

        T8.QTY,

        T8.SUMQTY,

        T8.PRICE,

        T8.SUMPRICE,

        T8.MATERIAL_TYPE

FROM T88 AS T8

LEFT OUTER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.MM/MAKT" AS MAKT

ON T8."SAP_MATERIAL_CODE"  = MAKT.MATNR AND MAKT.SPRAS = '1'

LEFT OUTER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.SD/ZSTXH" AS TH

ON T8."SAP_MATERIAL_CODE"  = TH.TDNAME AND TH.TDOBJECT = 'MATERIAL' AND TH.TDSPRAS = '1' AND TH.TDID = 'GRUN'

ORDER BY T8.LINENO,MAP(T8.MATERIAL_TYPE,'MPN',1,'MPNSON',2,0)

)

---WIP

,WIPMX AS (

SELECT T1.VBELN,  T1.POSNR,  T1.KWMENG,  T1.MATNR,  T1.ARKTX,  T1.XSJ,

   AUFK.WERKS,   AUFK.AUFNR,

       AFPO.MATNR AS "SAP_MATERIAL_CODE",   MAKT.MAKTX,   AFKO.GAMNG AS QTY,   TO_CHAR(TH.VTEXT) AS TUHAO

FROM T1 INNER JOIN  "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.PP/AFPO" AS AFPO

  ON T1.VBELN = AFPO.KDAUF AND T1.POSNR = AFPO.KDPOS

  INNER JOIN  "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.PP/AUFK" AS AUFK

  ON AUFK.AUFNR = AFPO.AUFNR AND AUFK.WERKS = AFPO.DWERK AND AFPO.POSNR = '0001'

INNER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.PP/AFKO" AS AFKO

    ON AUFK.AUFNR = AFKO.AUFNR  AND AUFK.AUTYP = '10'  AND AUFK.LOEKZ = ''

   AND AUFK.AUART = 'ZP10' AND AUFK.WERKS = '2021'

INNER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.MM/MAKT" AS MAKT

    ON AFPO.MATNR = MAKT.MATNR  AND MAKT.SPRAS = '1'

    LEFT OUTER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.SD/ZSTXH" AS TH

    ON AFPO.MATNR = TH.TDNAME

   AND TH.TDOBJECT = 'MATERIAL'

   AND TH.TDSPRAS = '1'

   AND TH.TDID = 'GRUN'

)

,WIPHZ AS (

SELECT WIP.VBELN,  WIP.POSNR,  WIP.KWMENG,  WIP.MATNR,  WIP.ARKTX,  WIP.XSJ,

WIP.WERKS,   WIP."SAP_MATERIAL_CODE",   WIP.MAKTX, WIP.TUHAO,

SUM(WIP.QTY) AS SUMQTY  

FROM WIPMX AS WIP

GROUP BY WIP.VBELN,  WIP.POSNR,  WIP.KWMENG,  WIP.MATNR,  WIP.ARKTX,  WIP.XSJ,

WIP.WERKS,  WIP."SAP_MATERIAL_CODE",   WIP.MAKTX, WIP.TUHAO

)

--REQ

,REQMX AS (

SELECT T1.VBELN,  T1.POSNR,  T1.KWMENG,  T1.MATNR,  T1.ARKTX,  T1.XSJ,

   EBAN.MATNR AS "SAP_MATERIAL_CODE",   MAKT.MAKTX,

       TO_CHAR(TH.VTEXT) AS TUHAO,

       EBAN.MENGE AS QTY,

       EBAN.MEINS

  FROM T1 INNER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.MM/EBKN" AS EBKN

ON T1.VBELN = EBKN.VBELN AND T1.POSNR = EBKN.VBELP

INNER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.MM/EBAN" AS EBAN

    ON EBAN.BANFN = EBKN.BANFN

   AND EBAN.BNFPO = EBKN.BNFPO

   INNER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.MM/MAKT" AS MAKT

    ON EBAN.MATNR = MAKT.MATNR  AND MAKT.SPRAS = '1'

  LEFT OUTER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.SD/ZSTXH" AS TH

    ON EBAN.MATNR = TH.TDNAME

   AND TH.TDOBJECT = 'MATERIAL'

   AND TH.TDSPRAS = '1'

   AND TH.TDID = 'GRUN'

)

,REQHZ AS (

SELECT REQ.VBELN,  REQ.POSNR,  REQ.KWMENG,  REQ.MATNR,  REQ.ARKTX,  REQ.XSJ,

REQ."SAP_MATERIAL_CODE",   REQ.MAKTX,   REQ.TUHAO,

       SUM(REQ.QTY ) AS SUMQTY,

       REQ.MEINS

FROM REQMX AS REQ

GROUP BY REQ.VBELN,  REQ.POSNR,  REQ.KWMENG,  REQ.MATNR,  REQ.ARKTX,  REQ.XSJ,

REQ."SAP_MATERIAL_CODE",   REQ.MAKTX,   REQ.TUHAO,REQ.MEINS

)

--PO

,POMX AS (

SELECT T1.VBELN,  T1.POSNR,  T1.KWMENG,  T1.MATNR,  T1.ARKTX,  T1.XSJ,

       EKPO.MATNR AS "SAP_MATERIAL_CODE", MAKT.MAKTX, TO_CHAR(TH.VTEXT) AS TUHAO,

       EKPO.MENGE AS QTY,

        round(EKPO.NETPR/EKPO.PEINH*EKPO.MENGE,2) AS PRICE,

       EKPO.MEINS

  FROM T1 INNER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.MM/EKKN" AS EKKN

  ON T1.VBELN = EKKN.VBELN AND T1.POSNR = EKKN.VBELP

  INNER JOIN  "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.MM/EKPO" AS EKPO

    ON EKPO.EBELN = EKKN.EBELN  AND EKPO.EBELP = EKKN.EBELP AND EKPO.STAPO <> 'X'

INNER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.MM/MAKT" AS MAKT

    ON EKPO.MATNR = MAKT.MATNR  AND MAKT.SPRAS = '1'

  LEFT OUTER JOIN "_SYS_BIC"."XXX_HANA_MODEL.EDW.03_BASAL_LAYER.001SAP.SD/ZSTXH" AS TH

    ON EKPO.MATNR = TH.TDNAME

   AND TH.TDOBJECT = 'MATERIAL'

   AND TH.TDSPRAS = '1'

   AND TH.TDID = 'GRUN'

)

,POHZ AS (

SELECT PO.VBELN,  PO.POSNR,  PO.KWMENG,  PO.MATNR,  PO.ARKTX,  PO.XSJ,

       PO."SAP_MATERIAL_CODE", PO.MAKTX, PO.TUHAO,

       SUM(PO.QTY) AS SUMQTY,

       SUM(PO.PRICE) AS SUMPRICE,

       PO.MEINS

FROM POMX AS PO

GROUP BY PO.VBELN,  PO.POSNR,  PO.KWMENG,  PO.MATNR,  PO.ARKTX,  PO.XSJ,

       PO."SAP_MATERIAL_CODE", PO.MAKTX, PO.TUHAO,  PO.MEINS

)

--PLM=1 WIP=2 REQ=3 PO=4 A=BOM匹配 B=BOM不匹配 C=BOM外

,T12AB AS (

SELECT PLMHZ.LINENO, PLMHZ.VBELN,  PLMHZ.POSNR,  PLMHZ.KWMENG,  PLMHZ.MATNR,  PLMHZ.ARKTX,  PLMHZ.XSJ,

        PLMHZ.ORDER_NUMBER, PLMHZ.ORDER_LINE_NUMBER, PLMHZ.PRODUCT_MATERIAL_CODE,

        PLMHZ."SAP_ORDER_NUMBER", PLMHZ."SAP_ORDER_LINE_NUMBER", PLMHZ."SAP_PRODUCT_MATERIAL_CODE",PLMHZ.PRODUCT_REV,

        PLMHZ.MATERIAL_CODE,   PLMHZ."SAP_MATERIAL_CODE",    PLMHZ.MAKTX,     PLMHZ.TUHAO,

        PLMHZ.QTY AS PLMQTY,   PLMHZ.SUMQTY AS PLMSUMQTY,  

        PLMHZ.PRICE AS PLMPRICE,  PLMHZ.SUMPRICE AS PLMSUMPRICE,

        PLMHZ.MATERIAL_TYPE,

        WIPHZ.SUMQTY AS WIPSUMQTY

        

FROM PLMHZ LEFT OUTER JOIN WIPHZ

ON PLMHZ.VBELN = WIPHZ.VBELN AND PLMHZ.POSNR = WIPHZ.POSNR AND PLMHZ."SAP_MATERIAL_CODE" = WIPHZ."SAP_MATERIAL_CODE" 

WHERE 1=2

)

,PLMA AS ( -- 按物料编码匹配结果

SELECT PLMHZ.LINENO, PLMHZ.VBELN,  PLMHZ.POSNR,  PLMHZ.KWMENG,  PLMHZ.MATNR,  PLMHZ.ARKTX,  PLMHZ.XSJ,

        PLMHZ.ORDER_NUMBER, PLMHZ.ORDER_LINE_NUMBER, PLMHZ.PRODUCT_MATERIAL_CODE,

        PLMHZ."SAP_ORDER_NUMBER", PLMHZ."SAP_ORDER_LINE_NUMBER", PLMHZ."SAP_PRODUCT_MATERIAL_CODE",PLMHZ.PRODUCT_REV,

        PLMHZ.MATERIAL_CODE,   PLMHZ."SAP_MATERIAL_CODE",    PLMHZ.MAKTX,     PLMHZ.TUHAO,

        PLMHZ.QTY AS PLMQTY,   PLMHZ.SUMQTY AS PLMSUMQTY,  

        PLMHZ.PRICE AS PLMPRICE,  PLMHZ.SUMPRICE AS PLMSUMPRICE,

        PLMHZ.MATERIAL_TYPE,

        WIPHZ.SUMQTY AS WIPSUMQTY,

        REQHZ.SUMQTY AS REQSUMQTY, REQHZ.MEINS AS "REQHZ_MEINS", 

        REQHZ."SAP_MATERIAL_CODE" AS "REQHZ_MATERIAL_CODE", REQHZ.TUHAO AS "REQHZ_TUHAO",

        POHZ.SUMQTY AS POSUMQTY, POHZ.SUMPRICE AS POSUMPRICE, 

        POHZ.MEINS AS "POHZ_MEINS", 

        POHZ."SAP_MATERIAL_CODE" AS "POHZ_MATERIAL_CODE", POHZ.TUHAO AS "POHZ_TUHAO"

        

FROM PLMHZ LEFT OUTER JOIN WIPHZ

ON PLMHZ.VBELN = WIPHZ.VBELN AND PLMHZ.POSNR = WIPHZ.POSNR AND PLMHZ."SAP_MATERIAL_CODE" = WIPHZ."SAP_MATERIAL_CODE" 

LEFT OUTER JOIN REQHZ

ON PLMHZ.VBELN = REQHZ.VBELN AND PLMHZ.POSNR = REQHZ.POSNR AND PLMHZ."SAP_MATERIAL_CODE" = REQHZ."SAP_MATERIAL_CODE" 

LEFT OUTER JOIN POHZ

ON PLMHZ.VBELN = POHZ.VBELN AND PLMHZ.POSNR = POHZ.POSNR AND PLMHZ."SAP_MATERIAL_CODE" = POHZ."SAP_MATERIAL_CODE" 

)

,PLMB AS ( -- 按图号匹配REQ结果

SELECT PLMHZ.LINENO, PLMHZ.VBELN,  PLMHZ.POSNR,  PLMHZ.KWMENG,  PLMHZ.MATNR,  PLMHZ.ARKTX,  PLMHZ.XSJ,

        PLMHZ.ORDER_NUMBER, PLMHZ.ORDER_LINE_NUMBER, PLMHZ.PRODUCT_MATERIAL_CODE,

        PLMHZ."SAP_ORDER_NUMBER", PLMHZ."SAP_ORDER_LINE_NUMBER", PLMHZ."SAP_PRODUCT_MATERIAL_CODE",PLMHZ.PRODUCT_REV,

        PLMHZ.MATERIAL_CODE,   PLMHZ."SAP_MATERIAL_CODE",    PLMHZ.MAKTX,     PLMHZ.TUHAO,

        PLMHZ.PLMQTY,   PLMHZ.PLMSUMQTY,  

        PLMHZ.PLMPRICE,  PLMHZ.PLMSUMPRICE,

        PLMHZ.MATERIAL_TYPE,

        PLMHZ.WIPSUMQTY,

        MAP(IFNULL(PLMHZ.REQSUMQTY,0),0,REQHZ.SUMQTY,PLMHZ.REQSUMQTY) AS REQSUMQTY,

        MAP(IFNULL(PLMHZ."REQHZ_MEINS",''),'',REQHZ.MEINS,PLMHZ."REQHZ_MEINS") AS "REQHZ_MEINS",

        MAP(IFNULL(PLMHZ."REQHZ_MATERIAL_CODE",''),'',REQHZ."SAP_MATERIAL_CODE",PLMHZ."REQHZ_MATERIAL_CODE") AS "REQHZ_MATERIAL_CODE", 

        MAP(IFNULL(PLMHZ."REQHZ_TUHAO",''),'',REQHZ.TUHAO,PLMHZ."REQHZ_TUHAO") AS "REQHZ_TUHAO",

        --PLMHZ.REQSUMQTY, 

        --PLMHZ.MEINS, 

        --PLMHZ."REQHZ_MATERIAL_CODE", 

        --PLMHZ."REQHZ_TUHAO",

        PLMHZ.POSUMQTY, PLMHZ.POSUMPRICE,  PLMHZ."POHZ_MEINS", PLMHZ."POHZ_MATERIAL_CODE", PLMHZ."POHZ_TUHAO"

FROM PLMA AS PLMHZ LEFT OUTER JOIN REQHZ

ON PLMHZ.VBELN = REQHZ.VBELN AND PLMHZ.POSNR = REQHZ.POSNR AND MAP(PLMHZ.TUHAO,'','NOTNEEDMATCH',PLMHZ.TUHAO) = REQHZ.TUHAO

)

,PLMC AS ( -- 按图号匹配PO结果

SELECT 'BOM内' AS BOMTYPE,PLMHZ.LINENO, PLMHZ.VBELN,  PLMHZ.POSNR,  PLMHZ.KWMENG,  PLMHZ.MATNR,  PLMHZ.ARKTX,  PLMHZ.XSJ,

        PLMHZ.ORDER_NUMBER, PLMHZ.ORDER_LINE_NUMBER, PLMHZ.PRODUCT_MATERIAL_CODE,

        PLMHZ."SAP_ORDER_NUMBER", PLMHZ."SAP_ORDER_LINE_NUMBER", PLMHZ."SAP_PRODUCT_MATERIAL_CODE",PLMHZ.PRODUCT_REV,

        PLMHZ.MATERIAL_CODE,   PLMHZ."SAP_MATERIAL_CODE",    PLMHZ.MAKTX,     PLMHZ.TUHAO,

        PLMHZ.PLMQTY,   PLMHZ.PLMSUMQTY,  

        PLMHZ.PLMPRICE,  PLMHZ.PLMSUMPRICE,

        PLMHZ.MATERIAL_TYPE,

        PLMHZ.WIPSUMQTY,

        PLMHZ.REQSUMQTY, 

        PLMHZ."REQHZ_MEINS",

        PLMHZ."REQHZ_MATERIAL_CODE", 

        PLMHZ."REQHZ_TUHAO",

        

        MAP(IFNULL(PLMHZ.POSUMQTY,0),0,POHZ.SUMQTY,PLMHZ.POSUMQTY) AS POSUMQTY,

        MAP(IFNULL(PLMHZ.POSUMPRICE,0),0,POHZ.SUMPRICE,PLMHZ.POSUMPRICE) AS POSUMPRICE,

        MAP(IFNULL(PLMHZ."POHZ_MEINS",''),'',POHZ.MEINS,PLMHZ."POHZ_MEINS") AS "POHZ_MEINS",

        MAP(IFNULL(PLMHZ."POHZ_MATERIAL_CODE",''),'',POHZ."SAP_MATERIAL_CODE",PLMHZ."POHZ_MATERIAL_CODE") AS "POHZ_MATERIAL_CODE", 

        MAP(IFNULL(PLMHZ."POHZ_TUHAO",''),'',POHZ.TUHAO,PLMHZ."POHZ_TUHAO") AS "POHZ_TUHAO"

        

        --PLMHZ.POSUMQTY, 

        --PLMHZ."POHZ_MEINS", 

        --PLMHZ."POHZ_MATERIAL_CODE", 

        --PLMHZ."POHZ_TUHAO"

FROM PLMB AS PLMHZ LEFT OUTER JOIN POHZ

ON PLMHZ.VBELN = POHZ.VBELN AND PLMHZ.POSNR = POHZ.POSNR AND MAP(PLMHZ.TUHAO,'','NOTNEEDMATCH',PLMHZ.TUHAO) = POHZ.TUHAO

)

,PLMD AS ( -- PLM的BOM没有匹配到结果的记录

SELECT 'BOM外' AS BOMTYPE,

0 AS LINENO, WIP.VBELN,  WIP.POSNR,  WIP.KWMENG,  WIP.MATNR,  WIP.ARKTX,  WIP.XSJ,

        '' AS ORDER_NUMBER, '' AS ORDER_LINE_NUMBER, '' AS PRODUCT_MATERIAL_CODE,

        '' AS "SAP_ORDER_NUMBER", '' AS "SAP_ORDER_LINE_NUMBER", '' AS "SAP_PRODUCT_MATERIAL_CODE",'' AS PRODUCT_REV,

        WIP."SAP_MATERIAL_CODE" AS MATERIAL_CODE,   WIP."SAP_MATERIAL_CODE",    WIP.MAKTX,     WIP.TUHAO,

        NULL AS PLMQTY,   NULL AS PLMSUMQTY,  

        NULL AS PLMPRICE,  NULL AS PLMSUMPRICE,

        '' AS MATERIAL_TYPE,

        WIP.SUMQTY AS WIPSUMQTY,

        NULL AS REQSUMQTY, 

        '' AS "REQHZ_MEINS",

        '' AS "REQHZ_MATERIAL_CODE", 

        '' AS "REQHZ_TUHAO",

        NULL AS POSUMQTY,

        NULL AS POSUMPRICE,

        '' AS "POHZ_MEINS",

        '' AS "POHZ_MATERIAL_CODE", 

        '' AS "POHZ_TUHAO"

FROM WIPHZ AS WIP

WHERE NOT EXISTS (

SELECT 'X'

FROM PLMC AS PLMHZ

WHERE WIP.VBELN = PLMHZ.VBELN

AND  WIP.POSNR = PLMHZ.POSNR

AND WIP."SAP_MATERIAL_CODE" = PLMHZ."SAP_MATERIAL_CODE"

--AND IFNULL(PLMHZ.WIPSUMQTY,0) > 0

)

UNION ALL

SELECT 'BOM外' AS BOMTYPE,

0 AS LINENO, WIP.VBELN,  WIP.POSNR,  WIP.KWMENG,  WIP.MATNR,  WIP.ARKTX,  WIP.XSJ,

        '' AS ORDER_NUMBER, '' AS ORDER_LINE_NUMBER, '' AS PRODUCT_MATERIAL_CODE,

        '' AS "SAP_ORDER_NUMBER", '' AS "SAP_ORDER_LINE_NUMBER", '' AS "SAP_PRODUCT_MATERIAL_CODE",'' AS PRODUCT_REV,

        WIP."SAP_MATERIAL_CODE" AS MATERIAL_CODE,   WIP."SAP_MATERIAL_CODE",    WIP.MAKTX,     WIP.TUHAO,

        NULL AS PLMQTY,   NULL AS PLMSUMQTY,  

        NULL AS PLMPRICE,  NULL AS PLMSUMPRICE,

        '' AS MATERIAL_TYPE,

        NULL AS WIPSUMQTY,

        WIP.SUMQTY AS REQSUMQTY, 

        WIP.MEINS AS "REQHZ_MEINS",

        WIP."SAP_MATERIAL_CODE" AS "REQHZ_MATERIAL_CODE", 

        WIP.TUHAO AS "REQHZ_TUHAO",

        NULL AS POSUMQTY,

        NULL AS POSUMPRICE,

        WIP.MEINS AS "POHZ_MEINS",

        '' AS "POHZ_MATERIAL_CODE", 

        '' AS "POHZ_TUHAO"

FROM REQHZ AS WIP

WHERE NOT EXISTS (

SELECT 'X'

FROM PLMC AS PLMHZ

WHERE WIP.VBELN = PLMHZ.VBELN

AND  WIP.POSNR = PLMHZ.POSNR

AND (WIP."SAP_MATERIAL_CODE" = PLMHZ."SAP_MATERIAL_CODE" OR MAP(WIP.TUHAO,'','NOTNEEDMATCH',WIP.TUHAO) = PLMHZ.TUHAO)

--AND IFNULL(PLMHZ.REQSUMQTY,0) > 0

)

UNION ALL

SELECT 'BOM外' AS BOMTYPE,

0 AS LINENO, WIP.VBELN,  WIP.POSNR,  WIP.KWMENG,  WIP.MATNR,  WIP.ARKTX,  WIP.XSJ,

        '' AS ORDER_NUMBER, '' AS ORDER_LINE_NUMBER, '' AS PRODUCT_MATERIAL_CODE,

        '' AS "SAP_ORDER_NUMBER", '' AS "SAP_ORDER_LINE_NUMBER", '' AS "SAP_PRODUCT_MATERIAL_CODE",'' AS PRODUCT_REV,

        WIP."SAP_MATERIAL_CODE" AS MATERIAL_CODE,   WIP."SAP_MATERIAL_CODE",    WIP.MAKTX,     WIP.TUHAO,

        NULL AS PLMQTY,   NULL AS PLMSUMQTY,  

        NULL AS PLMPRICE,  NULL AS PLMSUMPRICE,

        '' AS MATERIAL_TYPE,

        NULL AS WIPSUMQTY,

        NULL AS REQSUMQTY, 

        '' AS "REQHZ_MEINS",

        '' AS "REQHZ_MATERIAL_CODE", 

        '' AS "REQHZ_TUHAO",

        WIP.SUMQTY AS POSUMQTY,

        WIP.SUMPRICE AS POSUMPRICE,

        WIP.MEINS AS "POHZ_MEINS",

        WIP."SAP_MATERIAL_CODE" AS "POHZ_MATERIAL_CODE", 

        WIP.TUHAO AS "POHZ_TUHAO"

FROM POHZ AS WIP

WHERE NOT EXISTS (

SELECT 'X'

FROM PLMC AS PLMHZ

WHERE WIP.VBELN = PLMHZ.VBELN

AND  WIP.POSNR = PLMHZ.POSNR

AND (WIP."SAP_MATERIAL_CODE" = PLMHZ."SAP_MATERIAL_CODE" OR MAP(WIP.TUHAO,'','NOTNEEDMATCH',WIP.TUHAO) = PLMHZ.TUHAO)

--AND IFNULL(PLMHZ.POSUMQTY,0) > 0

)

)

--合并

,PLME AS (

SELECT PLMHZ.BOMTYPE,PLMHZ.LINENO, PLMHZ.VBELN,  PLMHZ.POSNR,  PLMHZ.KWMENG,  PLMHZ.MATNR,  PLMHZ.ARKTX,  PLMHZ.XSJ,

        PLMHZ.ORDER_NUMBER, PLMHZ.ORDER_LINE_NUMBER, PLMHZ.PRODUCT_MATERIAL_CODE,

        PLMHZ."SAP_ORDER_NUMBER", PLMHZ."SAP_ORDER_LINE_NUMBER", PLMHZ."SAP_PRODUCT_MATERIAL_CODE",PLMHZ.PRODUCT_REV,

        PLMHZ.MATERIAL_CODE,   PLMHZ."SAP_MATERIAL_CODE",    PLMHZ.MAKTX,     PLMHZ.TUHAO,

        PLMHZ.PLMQTY,   PLMHZ.PLMSUMQTY,  

        PLMHZ.PLMPRICE,  PLMHZ.PLMSUMPRICE,

        PLMHZ.MATERIAL_TYPE,

        PLMHZ.WIPSUMQTY,

        PLMHZ.REQSUMQTY, 

        PLMHZ."REQHZ_MEINS",

        PLMHZ."REQHZ_MATERIAL_CODE", 

        PLMHZ."REQHZ_TUHAO",

        PLMHZ.POSUMQTY,

        PLMHZ.POSUMPRICE,

        PLMHZ."POHZ_MEINS",

        PLMHZ."POHZ_MATERIAL_CODE", 

        PLMHZ."POHZ_TUHAO"

FROM PLMC AS PLMHZ

UNION ALL

SELECT PLMHZ.BOMTYPE,PLMHZ.LINENO, PLMHZ.VBELN,  PLMHZ.POSNR,  PLMHZ.KWMENG,  PLMHZ.MATNR,  PLMHZ.ARKTX,  PLMHZ.XSJ,

        PLMHZ.ORDER_NUMBER, PLMHZ.ORDER_LINE_NUMBER, PLMHZ.PRODUCT_MATERIAL_CODE,

        PLMHZ."SAP_ORDER_NUMBER", PLMHZ."SAP_ORDER_LINE_NUMBER", PLMHZ."SAP_PRODUCT_MATERIAL_CODE",PLMHZ.PRODUCT_REV,

        PLMHZ.MATERIAL_CODE,   PLMHZ."SAP_MATERIAL_CODE",    PLMHZ.MAKTX,     PLMHZ.TUHAO,

        PLMHZ.PLMQTY,   PLMHZ.PLMSUMQTY,  

        PLMHZ.PLMPRICE,  PLMHZ.PLMSUMPRICE,

        PLMHZ.MATERIAL_TYPE,

        PLMHZ.WIPSUMQTY,

        PLMHZ.REQSUMQTY, 

        PLMHZ."REQHZ_MEINS",

        PLMHZ."REQHZ_MATERIAL_CODE", 

        PLMHZ."REQHZ_TUHAO",

        PLMHZ.POSUMQTY,

        PLMHZ.POSUMPRICE,

        PLMHZ."POHZ_MEINS",

        PLMHZ."POHZ_MATERIAL_CODE", 

        PLMHZ."POHZ_TUHAO"

FROM PLMD AS PLMHZ

)

--输出

,OUTA AS (

SELECT PLMHZ.BOMTYPE,PLMHZ.LINENO, LTRIM(PLMHZ.VBELN,'0') AS VBELN,  PLMHZ.POSNR,  PLMHZ.KWMENG,  LTRIM(PLMHZ.MATNR,'0') AS MATNR,  PLMHZ.ARKTX,  PLMHZ.XSJ,

        PLMHZ.ORDER_NUMBER, PLMHZ.ORDER_LINE_NUMBER, PLMHZ.PRODUCT_MATERIAL_CODE,

        PLMHZ."SAP_ORDER_NUMBER", PLMHZ."SAP_ORDER_LINE_NUMBER", PLMHZ."SAP_PRODUCT_MATERIAL_CODE",PLMHZ.PRODUCT_REV,

        LTRIM(PLMHZ.MATERIAL_CODE,'0') AS MATERIAL_CODE,   PLMHZ."SAP_MATERIAL_CODE",    PLMHZ.MAKTX,     PLMHZ.TUHAO,

        PLMHZ.PLMQTY,   PLMHZ.PLMSUMQTY,  

        PLMHZ.PLMPRICE,  PLMHZ.PLMSUMPRICE,

        PLMHZ.MATERIAL_TYPE,

        PLMHZ.WIPSUMQTY,

        PLMHZ.REQSUMQTY, 

        PLMHZ."REQHZ_MEINS",

        LTRIM(PLMHZ."REQHZ_MATERIAL_CODE",'0') AS "REQHZ_MATERIAL_CODE",

        PLMHZ."REQHZ_TUHAO",

        PLMHZ.POSUMQTY,

        PLMHZ.POSUMPRICE,

        PLMHZ."POHZ_MEINS",

        MAP(LTRIM(PLMHZ."POHZ_MATERIAL_CODE",'0'),LTRIM(PLMHZ.MATERIAL_CODE,'0'),'',LTRIM(PLMHZ."POHZ_MATERIAL_CODE",'0')) AS "POHZ_MATERIAL_CODE",

        PLMHZ."POHZ_TUHAO",

        (SELECT max(T.SUMPRICE) FROM PLMHZ AS T 

        WHERE T."SAP_PRODUCT_MATERIAL_CODE" = T."SAP_MATERIAL_CODE") AS PLMCPZJ

FROM PLME  AS PLMHZ

)

SELECT PLMHZ.BOMTYPE,PLMHZ.LINENO, PLMHZ.VBELN,  PLMHZ.POSNR,  PLMHZ.KWMENG,  PLMHZ.MATNR,  PLMHZ.ARKTX,  PLMHZ.XSJ,PLMHZ.PLMCPZJ,

        PLMHZ.MATERIAL_CODE,   PLMHZ.MAKTX,     --PLMHZ.TUHAO,

        PLMHZ.PLMQTY,   PLMHZ.PLMSUMQTY,  

        PLMHZ.PLMPRICE,  PLMHZ.PLMSUMPRICE,

        PLMHZ.MATERIAL_TYPE,

        MAP(SUM(IFNULL(PLMHZ.WIPSUMQTY,0)),0,NULL,SUM(IFNULL(PLMHZ.WIPSUMQTY,0))) AS WIPSUMQTY,

        MAP(SUM(IFNULL(PLMHZ.REQSUMQTY,0)),0,NULL,SUM(IFNULL(PLMHZ.REQSUMQTY,0))) AS REQSUMQTY,

        MAP(SUM(IFNULL(PLMHZ.POSUMQTY,0)),0,NULL,SUM(IFNULL(PLMHZ.POSUMQTY,0))) AS POSUMQTY,

        MAP(SUM(IFNULL(PLMHZ.POSUMPRICE,0)),0,NULL,SUM(IFNULL(PLMHZ.POSUMPRICE,0))) AS POSUMPRICE,

        --PLMHZ.REQSUMQTY, 

        --PLMHZ.POSUMQTY,

        --PLMHZ.POSUMPRICE,

        PLMHZ."POHZ_MEINS",

        PLMHZ."POHZ_MATERIAL_CODE"

FROM OUTA AS PLMHZ

GROUP BY PLMHZ.BOMTYPE,PLMHZ.LINENO, PLMHZ.VBELN,  PLMHZ.POSNR,  PLMHZ.KWMENG,  PLMHZ.MATNR,  PLMHZ.ARKTX,  PLMHZ.XSJ,PLMHZ.PLMCPZJ,

        PLMHZ.MATERIAL_CODE,   PLMHZ.MAKTX,     --PLMHZ.TUHAO,

        PLMHZ.PLMQTY,   PLMHZ.PLMSUMQTY,  

        PLMHZ.PLMPRICE,  PLMHZ.PLMSUMPRICE,

        PLMHZ.MATERIAL_TYPE,

        PLMHZ."POHZ_MEINS",

        PLMHZ."POHZ_MATERIAL_CODE"      

ORDER BY PLMHZ.BOMTYPE,PLMHZ.LINENO,MAP(PLMHZ.MATERIAL_TYPE,'MPN',1,'MPNSON',2,0),PLMHZ.MATERIAL_CODE