Need a customer outstanding report quer

[former_member392795]

Dear Gurus.

Greeting of the advice, please help me with customer outstanding report query.  In this report I want to see sale employee wised group customers, there invoice during the selected date range, payments made against those invoice,Numbers of Days from Invoice date to current date mode of payment, check number, outstanding balance against those invoice(portion that is unpaid), customers total receivable on that date.

Selection perimeters: sale employee, greater or equal posting date, less or equal posting date.

Your prompt answer is needed.

Regards

Ghufran

[former_member212181]

Hi Ghufran,

Please try below query

This is not exactly what you want.

Because it doesn't give any details of adjusted documents such as Incoming payment, Manual Reco etc.

But you should realize that SAP B1 allows partial payment of AR Invoice, Multiple checks in single Incoming payment.

If we try to bring all those information in single report, it may lead to duplication of value.

You can try for Crystal report or SSRS report for your requirement which helps you to group in presentation.

---Query Starts

Declare @FDate Datetime

Declare @TDate Datetime

Select @FDate = Min(X.DocDate) from OINV X where X.DocDate>='[%0]'

Select @TDate = Max(Y.DocDate) from OINV Y where Y.DocDate<='[%1]'

;WITH Reco AS

(

SELECT T1.SrcObjAbs[RecoEntry]

    , Max(T0.[ReconDate])[LastAdjDate]

    ,Sum(T1.[ReconSum])[AdjAmt]

FROM OITR T0

    INNER JOIN ITR1 T1 ON T0.[ReconNum] = T1.[ReconNum]

WHERE   T1.[SrcObjTyp] =13 and T1.IsCredit='D'

    and  T0.[ReconDate] >=@FDate and  T0.[ReconDate] <=@TDate

GROUP BY T1.[SrcObjAbs]

)

---

Select A.DocEntry,D.SlpName, A.DocNum,A.DocStatus[Current Status], A.DocDate, A.CardCode, B.CardName, A.DocTotal, C.LastAdjDate,  A.DocTotal-isnull(C.AdjAmt,0)[Due]

From OINV A

    Inner Join OCRD B on A.CardCode = B.CardCode

    Left Outer Join Reco C on A.DocEntry = C.RecoEntry

    Inner Join OSLP D on A.SlpCode = D.SlpCode

Where A.Canceled = 'N'

    and  A.DocDate>=@FDate

    and  A.DocDate<=@TDate

    and A.DocTotal-isnull(C.AdjAmt,0) <>0

    and D.SlpName = N'[%2]'

----Query Ends

Thanks

Unnikrishnan

Message was edited by: Unnikrishnan Balan

[former_member392795]

Dear Unnikrishnan

Thank you, your reply was very helpful and informative.

Currently I am using this query, it fulfills my all requirements except numbers of days from invoice date to current date and the amount remaining after payment of crdt memo.

Plus I want all invoices from a customer even if they are paid fully.

Regards

Ghufran

[former_member392795]

Dear Unnikrishnan

Thank you, your reply was very helpful and informative.

Currently I am using this query, it fulfills my all requirements except numbers of days from invoice date to current date and the amount remaining after payment of crdt memo.

Plus I want all invoices from a customer even if they are paid fully.

SELECT

T0.DocDate as 'Posting Date',

T0.DocNum as 'AR Invoice Number',

T0.CardName,

T0.DocDate as 'Payment Date',

T1.DocNum as 'Incoming Payment Number ',

T0.DocTotal as 'AR Invoice Total',

T1.TrsfrSum,

T1.CashSum as 'Cash Amount',

T1.CreditSum,

T1.CheckSum,

T2.CheckNum as 'Cheque Number'

FROM OINV T0

LEFT JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry

LEFT JOIN RCT1 T2 ON T1.DocNum = T2.DocNum

Left Join OSLP T3 ON T3.SlpCode= T0.SlpCode

where T0.DocDate >='[%0]' and T0.DocDate <='[%1]' and T3.SlpName=[%2]

Regards

Ghufran

[former_member212181]

Hi Ghufran,

You query is incomplete as it will fail in case of multiple incoming payment against single invoice.

As i mentioned in last post, we can't bring adjustment of AR invoice data in one line.

One AR Invoice having $100 may adjust with 2 Cash/Cheque/BT payment, 2 AR Credit Note adjusted in incoming payment, 2 Manual reconciliation.

If we tries to link those data, query will generate duplicate records.

Thanks

Unnikrishnan

[former_member392795]

Yes you are right, I tried it right now and my query is showing no result. Thank you.

but my client require from me to at least provide him with number of days invoice is outstanding  and the amount of amount outstanding.

Please help me the query for it.

[former_member392795]

Chech out this query, it is giving the days outstanding but other info is missing.

SELECT

T0.CardCode AS 'Cust Num',

T0.CardName AS 'Customer Name',

T0.DocNum AS 'Inv Num',

T0.NumAtCard AS 'Cust Reference',

T0.DocDate AS 'Post Date',

T0.DocDueDate AS 'Due Date',

T0.DocTotal AS 'Orig Dollars',

T0.PaidToDate AS ' Paid on Inv',

(T0.DocTotal - T0.PaidToDate) AS 'Open Inv Amt',

DATEDIFF (DD,T0.DocDate, GETDATE ( ) ) AS 'Days To/Past'

FROM OINV T0 

WHERE

(T0.CardCode LIKE '%%[%0]%%' OR '[%0]' = ' ')

AND T0.DocStatus = 'O'

AND T0.DocDate >= '[%1]'

AND (T0.DocTotal - T0.PaidToDate) < > 0

UNION ALL

SELECT

T0.CardCode AS 'Cust Num',

T0.CardName AS 'Customer Name',

0 AS 'Inv Num',

'*** CUSTOMER BALANCE *** ' AS 'Cust Reference',

'01/01/2000' AS 'Post Date',

'01/01/2000' AS 'Due Date',

0 AS 'Orig Dollars',

0 AS ' Paid on Inv',

T0.Balance AS 'Open Inv Amt',

0 AS 'Days To/Past'

FROM OCRD T0

WHERE

(T0.CardCode LIKE '%%[%0]%%' OR '[%0]' = ' ')

AND T0.Balance < > 0

ORDER BY

T0.CardCode,

T0.DocNum