AR AGING QUERY

[Former Member]

Dear Experts,

Kindly help me edit the following query as follows:

1. Show the balance of the unreconciled amount. For example, If an incoming payment is partially unreconciled, the query report should display the unreconciled amount instead of the full amount in the incoming payment
2. To run for a specific BP only

The initial query thread is http://scn.sap.com/thread/1624725 

select T1.cardcode 'Bp Code',T1.cardname 'Name',sysdeb 'Debit Amount',syscred 'Credit Amount', (sysdeb - syscred) as 'Balance Due',

case T0.transtype

when '13' then 'INV'

when '14' then 'AR CN'

when '24' then 'INCOMING'

else 'Other'

end 'Type',

Ref1,

fccurrency 'BP Currency',

CONVERT(VARCHAR(10), refdate, 103) 'Posting Date',

CONVERT(VARCHAR(10), duedate, 103) 'Due Date',

CONVERT(VARCHAR(10), taxdate, 103) 'Doc Date' ,

CASE

when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31

then

case

when syscred <> 0 then -syscred

else sysdeb

end

end "0-30 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 30

and (datediff(dd,refdate,current_timestamp))+1< 61)

then

case

when syscred <> 0 then -syscred

else sysdeb

end

end "31 to 60 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 60

and (datediff(dd,refdate,current_timestamp))+1< 91)

then

case

when syscred <> 0 then -syscred

else sysdeb

end

end "61 to 90 days",

CASE

when (DATEDIFF(dd,refdate,current_timestamp))+1 > 90

then

case

when syscred= 0 then sysdeb

when sysdeb= 0 then -syscred

end

end "90 + days"

from dbo.JDT1 T0

INNER JOIN dbo.OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'c'

where T0.intrnmatch = '0' and T0.BALDUEDEB != T0.BALDUECRED

ORDER BY T1.CARDCODE, T0.taxdate

Thanks for your kind help always

George Njuguna

[KennedyT21 · Accepted solution]

HI

Try This

select T1.cardcode 'Bp Code',T1.cardname 'Name',sysdeb 'Debit Amount',syscred 'Credit Amount',

T0.BALDUEDEB as 'Balance Due',

case T0.transtype

when '13' then 'INV'

when '14' then 'AR CN'

when '24' then 'INCOMING'

else 'Other'

end 'Type',

Ref1,

fccurrency 'BP Currency',

CONVERT(VARCHAR(10), refdate, 103) 'Posting Date',

CONVERT(VARCHAR(10), duedate, 103) 'Due Date',

CONVERT(VARCHAR(10), taxdate, 103) 'Doc Date' ,

CASE

when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31

then

case

when syscred <> 0 then -syscred

else sysdeb

end

end "0-30 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 30

and (datediff(dd,refdate,current_timestamp))+1< 61)

then

case

when syscred <> 0 then -syscred

else sysdeb

end

end "31 to 60 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 60

and (datediff(dd,refdate,current_timestamp))+1< 91)

then

case

when syscred <> 0 then -syscred

else sysdeb

end

end "61 to 90 days",

CASE

when (DATEDIFF(dd,refdate,current_timestamp))+1 > 90

then

case

when syscred= 0 then sysdeb

when sysdeb= 0 then -syscred

end

end "90 + days"

from dbo.JDT1 T0

INNER JOIN dbo.OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'c'

where T0.intrnmatch = '0' and T0.BALDUEDEB != T0.BALDUECRED and t1.CardCode='[%0]'

ORDER BY T1.CARDCODE, T0.taxdate

Regards

Kennedy

[Former Member · Accepted solution]

Thanks Kennedy for your help. The query is still not giving the correct result. The issues are:

1. On the balance due column, the query is not showing the balance due on the receipts.
2. On the aging columns, Invoices, journals and Credit notes are not showing the balance due though this was correctly picked on the balance due column

Further help will be appreciated.

Regards

George

[KennedyT21 · Accepted solution]

Can you explain in values..

[former_member184718 · Accepted solution]

Hi George,

Customer receivable ageing report will help you. Check,

Business Partner --> Business Partner Reports --.> Customer Receivables Aging

Thanks.

Hari

[Former Member · Accepted solution]

@Jimmy/Rakesh

We want to use the query to schedule report to non sap users for customer tracking.

@Kennedy T

I have attached a screen shot of the query results.

Receipt balances are not reflected on the balance due column. On the aging columns, invoices, CN and other transaction display the document sum instead of the unreconciled amount i.e balance due.

Your help is appreciated.

Regards

George

[KennedyT21 · Accepted solution]

Try This

select T1.cardcode 'Bp Code',T1.cardname 'Name',sysdeb 'Debit Amount',syscred 'Credit Amount',

(T0.BALDUEDEB - T0.BALDUECRED) as 'Balance Due',

case T0.transtype

when '13' then 'INV'

when '14' then 'AR CN'

when '24' then 'INCOMING'

else 'Other'

end 'Type',

Ref1,

fccurrency 'BP Currency',

CONVERT(VARCHAR(10), refdate, 103) 'Posting Date',

CONVERT(VARCHAR(10), duedate, 103) 'Due Date',

CONVERT(VARCHAR(10), taxdate, 103) 'Doc Date' ,

CASE when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31 then

case

when BALDUECRED <> 0 then -BALDUECRED

else BALDUEDEB

end

end "0-30 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 30

and (datediff(dd,refdate,current_timestamp))+1< 61)

then

case

when BALDUECRED <> 0 then -BALDUECRED

else BALDUEDEB

end

end "31 to 60 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 60

and (datediff(dd,refdate,current_timestamp))+1< 91)

then case when BALDUECRED <> 0 then -BALDUECRED else BALDUEDEB  end end "61 to 90 days",

CASE

when (DATEDIFF(dd,refdate,current_timestamp))+1 > 90

then

case

when BALDUECRED= 0 then BALDUEDEB

when BALDUEDEB= 0 then -BALDUECRED

end

end "90 + days"

from dbo.JDT1 T0

INNER JOIN dbo.OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'c'

where T0.intrnmatch = '0' and T0.BALDUEDEB != T0.BALDUECRED and t1.CardCode='[%0]'

ORDER BY T1.CARDCODE, T0.taxdate

Hope Helpful

Regards

Kennedy

[Former Member · Accepted solution]

Thanks experts for your great help.

[former_member609283 · Accepted solution]

Hey Kennedy,

     I saw your post regarding Aging Report, I have a query below that works fine but is not cattering Partial Payments, like if I do Incoming payment partially of an invoice, it simply took off that invoice from the aging as it has to be there untill it is closed.

SELECT T1.CardCode, T1.CardName, T1.CreditLine, T0.RefDate, T0.Ref1 'Document Number',

CASE  WHEN T0.TransType=13 THEN 'Invoice'

          WHEN T0.TransType=14 THEN 'Credit Note'

          WHEN T0.TransType=30 THEN 'Journal'

          WHEN T0.TransType=24 THEN 'Receipt'

          END AS 'Document Type',

     T2.[U_AcknowDueDate], (T0.Debit- T0.Credit) 'Balance'

,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T2.[U_AcknowDueDate],getdate())<=-1),0) 'Future'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T2.[U_AcknowDueDate],GETDATE())>=0 and DateDiff(day, T2.[U_AcknowDueDate],GETDATE())<=30),0) 'Current'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T2.[U_AcknowDueDate],GETDATE())>30 and DateDiff(day, T2.[U_AcknowDueDate],GETDATE())<=60),0) '31-60 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T2.[U_AcknowDueDate],getdate())>60 and DateDiff(day, T2.[U_AcknowDueDate],GETDATE())<=90),0) '61-90 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T2.[U_AcknowDueDate],getdate())>90 and DateDiff(day, T2.[U_AcknowDueDate],GETDATE())<=120),0) '91-120 Days'

,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T2.[U_AcknowDueDate],GETDATE())>=121),0) '121+ Days'

FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode

INNER JOIN OJDT T2 ON T2.Transid=T0.TransID

WHERE (T0.MthDate IS NULL OR T0.MthDate >getdate()) AND T0.RefDate <= getdate() AND T1.CardType = 'C'

ORDER BY T1.CardCode, T2.[U_AcknowDueDate], T0.Ref1

How can I do that??

Regards,

Idrees

[max-huster]

This worked for me (HANA):

SELECT 
 "OCRD"."CardCode",
 "OCRD"."CardName",
 SUM("JDT1"."BalDueDeb"-"JDT1"."BalDueCred")
FROM "JDT1"
LEFT JOIN "OCRD" ON "JDT1"."ShortName" = "OCRD"."CardCode"
WHERE "JDT1"."VatGroup" IS NULL
 AND "OCRD"."CardType" = 'C' /* S = Supplier, C = Customer */
 AND "JDT1"."BalDueDeb" != "JDT1"."BalDueCred"
GROUP BY "OCRD"."CardCode", "OCRD"."CardName"

[Former Member]

Dear George,

Try: T0.BALDUEDEB-T0.BALDUECRED  as 'Balance Due'

Thanks,

Gordon

[Former Member]

Hi George,

Customer receivable ageing report will help you. Check,

Business Partner --> Business Partner Reports --.> Customer Receivables Aging

You can check SAP B1 8.82 PL08. The SAP B1 8.82 PL08 have complete aging report and very details.

Rather than using query generator, that can give different result if the query is not correct and waste time.

Thanks

Rakesh

[former_member186095]

Hi George,

You can check SAP B1 8.82 PL08. The SAP B1 8.82 PL08 have complete aging report and very details.

Rather than using query generator, that can give different result if the query is not correct and waste time.

Rgds,

Jmm