Skip to Content
0
Jul 28, 2023 at 05:29 PM

Sales Employees Sales and Collections Query in SAP Business One

186 Views Last edit Aug 27, 2023 at 01:38 AM 2 rev

Dears,

Please, I need help

I write query for Sales Employees Sales and Collections Both in same Crystal report

and collections for sales employees must equal :
Incoming payment based on AR Invoice + Incoming payment based on Payment of account of customer

Query calculated collection for sales employee correct

But Sales and Credit Note not correct by sales employee


what is the problem in this query

Kindly, find the below query

Do
BEGIN
DECLARE FromDate Date;
DECLARE ToDate Date;

FromDate := {?FromDate} ;
ToDate := {?ToDate} ;

SELECT OSLP."SlpName",


IFNULL((SELECT (SUM(O2."DocTotal"-O2."VatSum")) FROM OINV O2
WHERE O2.CANCELED = 'N' AND
O2."DocDate" BETWEEN :FromDate AND :ToDate AND O2."SlpCode" = OSLP."SlpCode"),0)
AS "TotalOfInvoices",


IFNULL((SELECT (SUM(OR2."DocTotal"- OR2."VatSum")) FROM ORIN OR2 WHERE OR2.CANCELED = 'N'
AND OR2."DocDate" BETWEEN :FromDate AND :ToDate AND OR2."SlpCode" = OSLP."SlpCode"), 0)
AS "TotalCreditNotes" ,

(SELECT SUM ( CT."DocTotal" ) FROM ORCT CT
WHERE CT."Canceled" ='N' and CT."DocDate" BETWEEN :FromDate AND :ToDate
AND CT."CardCode" = OCRD."CardCode") AS "Total Income Payment"


FROM OSLP
Inner JOIN OCRD ON OSLP."SlpCode" = OCRD."SlpCode"
group by OSLP."SlpName" , OSLP."SlpCode" , OCRD."CardCode";
END;

Thanks