Skip to Content
0
May 30, 2023 at 07:14 PM

SAP B1 - REPORT SAME IDEA OF SALES ANALYSIS QUERY

59 Views

Hello Dears ;

iam trying to create customized report same idea of sales analysis but always quantities and values not the same

select OINV."TaxDate" ,

SUM (INV1."InvQty") AS InvQty , SUM(INV1."LineTotal") AS LineTotal , SUM(OINV."DiscSum") AS DiscSum , (SUM(INV1."LineTotal") - SUM(OINV."DiscSum")) AS NET

, OINV."U_InvoiceType"

from OINV

left join INV1 on INV1."DocEntry" = OINV."DocEntry"

GROUP BY OINV."TaxDate" , INV1."LineStatus" , OINV."U_InvoiceType"

HAVING OINV."TaxDate" between {?FromDate} and {?ToDate} and INV1."LineStatus" != 'C'

UNION all

select ORIN."TaxDate" ,

-SUM (RIN1."InvQty") AS InvQty , -SUM(RIN1."LineTotal") AS LineTotal , -SUM(ORIN."DiscSum") AS DiscSum , -(SUM(RIN1."LineTotal") - SUM(ORIN."DiscSum")) AS NET

, ORIN."U_InvoiceType"

from ORIN

left join RIN1 on RIN1."DocEntry" = ORIN."DocEntry"

GROUP BY ORIN."TaxDate" , RIN1."LineStatus" , ORIN."U_InvoiceType"

HAVING ORIN."TaxDate" between {?FromDate} and {?ToDate} and RIN1."LineStatus" != 'C'