Skip to Content

Sales Analysis report for AR Invoice

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 28, 2017 at 06:57 AM

    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]'

    Add comment
    10|10000 characters needed characters exceeded