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