Chaoniubi欢(提问者)SELECT
CASE WHEN CL.VBELN_IM IS NULL THEN MV.VBELN_IM ELSE CL.VBELN_IM END AS 交货单,
CASE WHEN CL.VBELP_IM2 IS NULL THEN MV.VBELP_IM2 ELSE CL.VBELP_IM2 END AS 交货单行项目,
CL.AWREF AS 物料凭证,
CL.T_MSL AS 发货数量,
CL.T_TSL AS 发货金额,
CASE WHEN CL.T_MSL=0 THEN 0 ELSE CL.T_TSL/CL.T_MSL END AS 发货单价,
CL.T_FKIMG AS 开票数量,
CL.I_TSL AS 发货已开票金额,
CL.T_NETWR AS 已开票未税金额,
CL.T_MSL-CL.T_FKIMG AS 未开票数量,
(CL.T_MSL-CL.T_FKIMG)*SO.NETWR AS 未开票金额,
CL.T_TSL+CL.I_TSL AS 发出成本,
MV.TSL AS 调整,
CASE WHEN MV.TSL IS NULL THEN CL.T_TSL+CL.I_TSL WHEN CL.T_TSL IS NULL OR CL.I_TSL IS NULL THEN MV.TSL ELSE CL.T_TSL+CL.I_TSL+MV.TSL END AS 发出成本调整,
SO.AUART AS 订单类型,
SO.VBELN AS 销售订单,
SO.POSNR AS 销售订单行项目,
SO.VKORG AS 销售组织,
SO.VKBUR AS 销售大区,
TVK.BEZEI AS 销售大区名称,
SO.LIFNR AS 销售员编号,
lfa1.name1 AS 销售员名称,
SO.KWMENG AS 订单数量,
SO.NETWR AS 订单未税单价,
SO.KUNNR AS 客户,
B.NAME_ORG1 AS 客户名称,
left(WL.BUDAT,6) AS 过账日期,
REPLACE(LTRIM(REPLACE( WL.MATNR, \'0\', \' \')),\' \', \'0\') AS 物料,
WL.MAKTX AS 物料描述,
WL.VERPR/ WL.PEINH AS 交货实际单价,
CASE WHEN (WL.VERPR/ WL.PEINH)*(CL.T_MSL-CL.T_FKIMG) IS NULL THEN
MV.A_TSL ELSE (WL.VERPR/ WL.PEINH)*(CL.T_MSL-CL.T_FKIMG) END
AS 发出实际成本
FROM(
SELECT
CASE WHEN AL.VBELN_IM IS NULL THEN BL.VGBEL ELSE AL.VBELN_IM END AS VBELN_IM,
CASE WHEN AL.VBELP_IM2 IS NULL THEN BL.VGPOS2 ELSE AL.VBELP_IM2 END AS VBELP_IM2,
AL.AWREF,
--AL.BELNR,
CASE WHEN AL.T_MSL IS NULL THEN 0 ELSE AL.T_MSL END AS T_MSL,
CASE WHEN AL.T_TSL IS NULL THEN 0 ELSE AL.T_TSL END AS T_TSL,
CASE WHEN BL.T_FKIMG IS NULL THEN 0 ELSE BL.T_FKIMG END AS T_FKIMG,
CASE WHEN BL.T_TSL IS NULL THEN 0 ELSE BL.T_TSL END AS I_TSL,
CASE WHEN BL.T_NETWR IS NULL THEN 0 ELSE BL.T_NETWR END AS T_NETWR
FROM (
-----发货求和
SELECT
AW.VBELN_IM,
AW.VBELP_IM2,
--MAX(AW.BELNR) AS BELNR,
MAX(AW.AWREF) AS AWREF,
SUM(AW.MSL) AS T_MSL,
SUM(AW.TSL) AS T_TSL
FROM DW_T01_FACT_ACDOCA_1406010000_WL AW
--WHERE AW.RBUKRS=\'1000\' AND AW.BUDAT<=\'20221031\'
WHERE AW.RBUKRS=\'${pBUKRS}\' AND left(AW.BUDAT,6)>=\'${开始日期}\' AND left(AW.BUDAT,6)<=\'${结束日期}\'
GROUP BY AW.VBELN_IM,AW.VBELP_IM2
HAVING SUM(AW.TSL)<>0
)AL
FULL JOIN(
----开票求和
SELECT
AV.VGBEL,
AV.VGPOS2,
SUM(AV.FKIMG) AS T_FKIMG,
SUM(AV.TSL) AS T_TSL,
SUM(AV.NETWR) AS T_NETWR
FROM DW_T01_FACT_ACDOCA_1406010000_RV AV
--WHERE AV.RBUKRS=\'1000\' AND AV.BUDAT<=\'20221031\'
WHERE AV.RBUKRS=\'${pBUKRS}\' AND left(AV.BUDAT,6)>=\'${开始日期}\' AND left(AV.BUDAT,6)<=\'${结束日期}\'
GROUP BY AV.VGBEL,AV.VGPOS2
HAVING SUM(AV.TSL) <>0
)BL ON AL.VBELN_IM =BL.VGBEL AND AL.VBELP_IM2 =BL.VGPOS2
)CL
LEFT JOIN DW_T01_FACT_SALES_ORDER SO ON SO.LIPS_VBELN = CL.VBELN_IM AND SO.LIPS_POSNR =CL.VBELP_IM2
LEFT JOIN DW_T01_FACT_ACDOCA_1406010000_WL WL ON WL.AWREF = CL.AWREF AND WL.VBELN_IM =CL.VBELN_IM AND WL.VBELP_IM2 =CL.VBELP_IM2
--AND WL.BELNR = CL.BELNR
LEFT JOIN ODS_SAP_T_BUT000 B ON B.PARTNER= SO.KUNNR
LEFT JOIN ODS_SAP_T_LFA1 LFA1 ON LFA1.LIFNR = SO.LIFNR
LEFT JOIN ODS_SAP_T_TVKBT TVK ON TVK.VKBUR=SO.VKBUR
FULL JOIN
(
SELECT *
FROM ODS_FR_T_MANUAL_VOUCHER_1406010000 MA
WHERE MA.RBUKRS=\'${pBUKRS}\' AND left(MA.BUDAT,6)>=\'${开始日期}\' AND left(MA.BUDAT,6)<=\'${结束日期}\')MV
ON MV.VBELN_IM = CL.VBELN_IM AND MV.VBELP_IM2 =CL.VBELP_IM2
--AND MV.RBUKRS=\'${pBUKRS}\' AND left(MV.BUDAT,6)>=\'${开始日期}\' AND left(MV.BUDAT,6)<=\'${结束日期}\'
WHERE
--1=1
--(CL.T_TSL+CL.I_TSL+MV.TSL) <>0
(CASE WHEN MV.TSL IS NULL THEN CL.T_TSL+CL.I_TSL WHEN CL.T_TSL IS NULL OR CL.I_TSL IS NULL THEN MV.TSL ELSE CL.T_TSL+CL.I_TSL+MV.TSL END)<>0
--未开票数量<>0 AND 发出成本<>0
${if(len(pDELIVERY_NUM) == 0,\"\",\"and CL.VBELN_IM LIKE \'%\" + pDELIVERY_NUM + \"%\'\")}
${if(len(pORDER_NUMBER) == 0,\"\",\"and SO.VBELN LIKE \'\" + pORDER_NUMBER + \"\'\")}
${if(len(pCOSTOMER_NUMBER) == 0,\"\",\"and SO.KUNNR LIKE \'%\" + pCOSTOMER_NUMBER + \"%\'\")}
${if(len(pMARKET_ORG) == 0,\"\",\"and SO.VKORG = \'\" + pMARKET_ORG + \"\'\")}
--${if(len(pMATERIAL_NUMBER) == 0,\"\",\"and WL.MATNR LIKE \'%${pMATERIAL_NUMBER}%\')}
--AND WL.MATNR LIKE \'%${pMATERIAL_NUMBER}%\'
${if(len(pMATERIAL_NUMBER) == 0,\"\",\"AND WL.MATNR LIKE \'%\" + pMATERIAL_NUMBER + \"%\'\")}
1min目标场景问卷
立即参与
