Scribd
Upload
17 views2 pages

Query Merchandiser

The document contains a SQL query that retrieves and aggregates sales data based on various conditions such as invoice date, organization, and material type. It calculates order quantities, amounts in PKR, and includes multiple joins with related tables to gather necessary information. The results are grouped by year, month, invoice number, and other relevant fields.

Uploaded by

zainktsap
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
17 views2 pages

Query Merchandiser

The document contains a SQL query that retrieves and aggregates sales data based on various conditions such as invoice date, organization, and material type. It calculates order quantities, amounts in PKR, and includes multiple joins with related tables to gather necessary information. The results are grouped by year, month, invoice number, and other relevant fields.

Uploaded by

zainktsap
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

SELECT

SUBSTR(M.FKDAT, 1, 4) AS YEAR,
SUBSTR(M.FKDAT, 5, 2)AS MONTH,
M.VBELN AS INVOICE#,
MAX(SLD.NAME_ORG1) || MAX(SLD.NAME_ORG2) AS SOLD_TO_PARTY,
SUM((CASE
WHEN M.FKART = 'S1' OR M.FKART = 'Z2KR' OR M.FKART = 'Z3FL' OR M.FKART =
'Z4FL' OR M.FKART = 'Z1DR' OR M.FKART = 'Z1KR'
THEN D.FKLMG * -1
ELSE D.FKLMG
END)*0.91) AS ORDER_QTYMTR,
MD.CV_LONG AS MERCHANDISER,
ROUND(
( (CASE
WHEN M.FKART IN ('S1','Z2KR','Z3FL','Z4FL','Z1DR','Z1KR')
THEN ZCURRCONV(CC1.CURRDEC,D.NETWR) * -1
ELSE ZCURRCONV(CC1.CURRDEC,D.NETWR)
END)
* CASE WHEN M.WAERK = 'PKR' THEN 1 ELSE M.KURRF END
), 2
) AS PKR_AMOUNT,
M.WAERK AS CURRENCY,
CASE WHEN M.WAERK = 'PKR' THEN 1 ELSE M.KURRF END AS EXCHANGE_RATE,

ROUND(
( ( (CASE
WHEN M.FKART IN ('S1','Z2KR','Z3FL','Z4FL','Z1DR','Z1KR')
THEN ZCURRCONV(CC1.CURRDEC,D.NETWR) * -1
ELSE ZCURRCONV(CC1.CURRDEC,D.NETWR)
END)
* CASE WHEN M.WAERK = 'PKR' THEN 1 ELSE M.KURRF END
) / (CASE WHEN M.WAERK = 'PKR' THEN 1 ELSE M.KURRF END)
), 2
) AS AMOUNT

FROM
VBRK AS M
INNER JOIN VBRP AS D ON (M.VBELN = D.VBELN AND M.MANDT = D.MANDT AND D.NETWR <> 0 )
LEFT OUTER JOIN VBAP AS VP ON (D.AUBEL = VP.VBELN AND D.AUPOS = VP.POSNR AND
D.MANDT = VP.MANDT)
LEFT OUTER JOIN VBAK AS VPM ON ( VP.VBELN = VPM.VBELN AND VP.MANDT = VPM.MANDT )
LEFT OUTER JOIN TVGRT AS SGD ON ( VPM.VKGRP = SGD.VKGRP AND SGD.SPRAS = 'E' )
LEFT OUTER JOIN ZCH_D AS MD ON ( VPM.ZZMERCHANDISER = MD.CV_SHORT AND MD.DOCID =
'0000000563' )
LEFT JOIN BUT000 AS SLD ON ( M.KUNAG = SLD.PARTNER AND M.MANDT = SLD.CLIENT )
LEFT OUTER join MARA AS MARA ON (D.MATNR = MARA.MATNR AND D.MANDT = M.MANDT)
LEFT OUTER JOIN PRCD_ELEMENTS AS ZDOR1 ON ( M.KNUMV = ZDOR1.KNUMV AND M.MANDT =
ZDOR1.CLIENT AND RIGHT(D.POSNR,4) = RIGHT(ZDOR1.KPOSN,4) AND ZDOR1.KWERT > '0'
AND (
(M.VKORG = '8000' AND ZDOR1.KSCHL = 'PR00')
OR (M.VKORG <> '8000' AND ZDOR1.KSCHL IN ('ZSP1' , 'PR00' , 'ZSPS',
'ZSPB'))
) )

LEFT OUTER JOIN ZTCURX as CC1 ON (ZDOR1.WAERS = CC1.CURRKEY)

WHERE M.FKDAT = '20250802' AND VP.VKORG_ANA = '1000' AND MARA.MTART = 'ZFF' AND
D.WERKS = '3000'
GROUP BY
SUBSTR(M.FKDAT, 1, 4),
SUBSTR(M.FKDAT, 5, 2),
M.VBELN,
M.NETWR,
MD.CV_LONG,
M.FKART,
D.FKLMG,
CC1.CURRDEC,
D.NETWR,
M.WAERK,
M.KURRF,
(CASE
WHEN M.FKART IN ('S1','Z2KR','Z3FL','Z4FL','Z1DR','Z1KR')
THEN D.FKLMG * -1
ELSE D.FKLMG
END)

You might also like