AP & AR Summary Query

[Former Member]

Does anybody have a query that will return the AP & AR balances in the following format:

Current

30-60 Days

60-90 Days

Over 90 Days

Total

Thanks

Jeff

[Former Member]

Adrian,

I am getting an error in the query you posted so I cannot tell if it is what I am looking for.

I will be using this query outside of B1 otherwise I would use the standard aging report. I am just looking for a summary to give my executives.

Thanks for your help.

[former_member583013]

Jeff,

That query from Adrain is a detailed one. I will try to get you something.

Suda

[Former Member]

Hi Jeff,

Give this a go. You can tailer this more by giving it subtotals per BP and a Grand Total by creating a query print layout.

Regards,

Adrian

select OCRD.cardcode 'Supplier Code',OCRD.cardname 'Name',sysdeb 'Debit GBP',syscred 'Credit GBP',

case JDT1.transtype

when '-2' then 'OB'

when '18' then 'PU'

when '19' then 'PC'

when '46' then 'PS'

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,taxdate,current_timestamp) < 31

then

case

when syscred <> 0 then syscred * - 1

else sysdeb

end

end "0-30 days",

case when (datediff(dd,taxdate,current_timestamp) > 30

and datediff(dd,taxdate,current_timestamp)< 61)

then

case

when syscred <> 0 then syscred * - 1

else sysdeb

end

end "31 to 60 days",

case when (datediff(dd,taxdate,current_timestamp) > 60

and datediff(dd,taxdate,current_timestamp)< 91)

then

case

when syscred <> 0 then syscred * - 1

else sysdeb

end

end "61 to 90 days",

CASE

when DATEDIFF(dd,taxdate,current_timestamp) > 90

then

case

when syscred <> 0 then syscred * - 1

else sysdeb

end

end "90 + days"

from JDT1,OCRD where JDT1.shortname = OCRD.cardcode and cardtype = 's' and intrnmatch = '0'

ORDER BY OCRD.CARDCODE, taxdate

[former_member1190345]

Hi Jeff,

Please help me understyand your question more clearly . From what I understand is you need to have a list of the payments to be received from the customer agewise i.e. which customers payment is due in the next 0 - 30 days,30 - 60 days and so on.Am I right. This similar in case of Vendors liablity also. Here you want to know how much you want to pay to the vendor.

If that is what your need is then you can as well get it from the sales analysis report from sales reports and Vendor analyses reports from Purchase reports. Please check these reports out.

If your requirement is something other than this please let me now.

Nagesh

[Former Member]

Yes, I would like the grand total for each of the columns.

I would prefer it to be in rows:

Current - Value

30-60 - Value

60-90 - Value

90+ - Value

Total - Value

Thanks

[former_member583013]

Could you ellaborate on what other information you need along with this.

Are you simply looking for grand totals of AR and AP in the columns of

Current...30-60 Days............60-90 Days...............Over 90 Days..............Total

Suda