Skip to Content
0

Sales Analysis report for AR Invoice

Nov 20, 2017 at 07:59 AM

36

avatar image

HI Experts

How to generate a query Sales analysis report for AR Invoice.(Document no wise)

My query is not matching with existing AR invoice Standard analysis report.

SELECT t0.DocEntry, t0.DocDate, t0.cardcode, t0.CardName, (T0.[GrosProfit]/ NULLIF((T0.[DocTotal] - T0.[VatSum]), 0) * 100) As 'GrossProfit%', convert(int,t0.u_udf_quant) as Quant,(Select slpname from oslp where t0.SlpCode=slpcode) as Employee, t0.GrosProfit, (t0.DocTotal-t0.VatSum) as value FROM ORDR t0 WHERE t0.DocDate BETWEEN '06-01-2017' and '06-12-2017' AND t0.CANCELED='N' ORDER BY (T0.[GrosProfit] / NULLIF((T0.[DocTotal] - T0.[VatSum]), 0) * 100) DESC

Thanks

Vinoth

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Mohd Danish Nov 28, 2017 at 06:57 AM
0

please try...

FOR SAP HANA

SELECT a."CardCode", a."CardName", a."ShipToCode" as "Consignee",V."CityS",a."DocDate", a."DocNum",a."U_UNE_EXIN" AS "Export Invoice NO",
a."NumAtCard" as "PO Number", b."ItemCode" as "Product Code",
c."ItemName" as "Product Name",d."ItmsGrpNam" AS "ItemGroupName", c."SalUnitMsr", b."Quantity" as "No of Pack",
b."NumPerMsr" as "Items per Unit", b."InvQty" as "Qty (TAB/CAP)",b."U_UNE_PKSZ",
b."GrossBuyPr",
b."Currency",
b."Rate",
b."Price",
( b."Price" * b."Rate" ) as "Sale Price",
(b."LineTotal"+ b."DistribSum") as "Total",

CASE
WHEN ((a."DocTotal" - a."VatSum") - a."TotalExpns") = 0.000 THEN 0.000
ELSE ((a."GrosProfit") / ((a."DocTotal" - a."VatSum") - a."TotalExpns")) * 100
END AS "Gross Prof %"
FROM OINV a
INNER JOIN INV1 b ON a."DocEntry" = b."DocEntry"
INNER JOIN OITM c ON b."ItemCode" = c."ItemCode"
INNER JOIN OITB d ON c."ItmsGrpCod" = d."ItmsGrpCod"
INNER JOIN OSLP e ON a."SlpCode" = e."SlpCode"
INNER JOIN OWHS l ON b."WhsCode" = l."WhsCode"
INNER JOIN INV12 V ON A."DocEntry" = V."DocEntry"
INNER JOIN OCRD M ON M."CardCode" = A."CardCode"
INNER JOIN OCRG N ON M."GroupCode" = N."GroupCode"
WHERE a."DocType" = 'I'
and a."DocDate">='20170401' and a."DocDate" <='20171128'
----and N."GroupName" ='[%2]'

UNION ALL
SELECT f."CardCode", f."CardName", f."ShipToCode" as "Consignee",P."CityS",f."DocDate",f."DocNum", f."U_UNE_EXIN" AS "Export Invoice NO",
f."NumAtCard" as "BP Ref", g."ItemCode" as "Product Code",
h."ItemName"as "Product Name", i."ItmsGrpNam" AS "ItemGroupName", h."SalUnitMsr", g."Quantity" as "No of Pack",
g."NumPerMsr" as "Items per Unit", g."InvQty" as "Qty (TAB/CAP)",g."U_UNE_PKSZ",
g."GrossBuyPr",
g."Currency",
g."Rate",
g."Price",
(g."Price" *g."Rate")as "Sale Price",
(g."LineTotal"+ g."DistribSum") as "Total" ,

CASE
WHEN ((f."DocTotal" - f."VatSum") - f."TotalExpns") = 0.000 THEN 0.000
ELSE ((f."GrosProfit") / ((f."DocTotal" - f."VatSum") - f."TotalExpns")) * 100
END AS "Gross Prof %"

FROM ORIN f
INNER JOIN RIN1 g ON f."DocEntry" = g."DocEntry"
INNER JOIN OITM h ON g."ItemCode" = h."ItemCode"
INNER JOIN OITB i ON h."ItmsGrpCod" = i."ItmsGrpCod"
INNER JOIN OSLP j ON f."SlpCode" = j."SlpCode"
INNER JOIN OWHS k ON g."WhsCode" = k."WhsCode"
INNER JOIN INV12 P ON F."DocEntry" = P."DocEntry"
INNER JOIN OCRD N ON N."CardCode" = F."CardCode"
INNER JOIN OCRG M ON N."GroupCode" = M."GroupCode"
WHERE f."DocType" = 'I'
and f."DocDate">='20170401' and f."DocDate" <='20171128'
----and M."GroupName" ='[%2]'

Share
10 |10000 characters needed characters left characters exceeded