cancel
Showing results for 
Search instead for 
Did you mean: 

Customer Receivables Aging - Query

Former Member
0 Kudos

I hope someone can help me on this.

I have looked all over the forumn for assistance before posting.

I am looking for a SQL Query that produces the same results that the 'Customer Receivables Aging' report produces.

So far I have this but when ever I attemt to seubtract Credits the totals are off.

SELECT "OCRD"."U_ASINumber", "OCRG"."GroupName", "OCRD"."U_Channel", "OINV"."DocTotal", "OINV"."DocDueDate", "OINV"."DocStatus", "OCRD"."CardName", "OCRD"."CardCode"

FROM ("DRI_DUCK"."dbo"."OINV" "OINV" INNER JOIN "DRI_DUCK"."dbo"."OCRD" "OCRD" ON "OINV"."CardCode"="OCRD"."CardCode") INNER JOIN "DRI_DUCK"."dbo"."OCRG" "OCRG" ON "OCRD"."GroupCode"="OCRG"."GroupCode"

WHERE "OCRG"."GroupName"=N'Corporate' AND "OCRD"."U_Channel"=N'2'

ORDER BY "OCRD"."CardCode", "OCRD"."U_ASINumber"

Ant help would be great.

Thanks,

Craig

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Craig,

The query you included has a lot of user defined fields as well as conditions that are based on your business.

The ff factors must be taken into consideration in the query you want to create,

1. Invoices

2. Credit Memos

3. Payments

4. Manual Journal Entries which involves Business Partners (AR)

By the way, what is your purpose of customizing the Customer A/R Aging Report?

Laurence Resubal

Former Member
0 Kudos

I need to create the query based on Invoices minus Credits. We send out a aging report to a select few customers. The out of the box solution works but there are a lot of filters that need to go through before we can send it out. Which becomes very time consuming when this is done weekly (dont ask me why).

Based on what is needed at this time, all I need in A/R Invoices and Credit Memo's based on DocDueDate but when ever I attempt to subtract the Credit Memo's from Invoices my totals are inaccurate.

Is there another table that contains this data based on BP and dates that my be more effective to draw this data from?

Thanks,

Craig

Former Member
0 Kudos

Hi Craig,

The ff query considered Invoices, Credit Memos and Journal Entries. Kindly review if this will give you the result you need.


DECLARE @CutOff datetime
set @CutOff = '1/31/2008'

declare @openinvoice table(
  docnum numeric(19,0),
  cardcode varchar(15),
  cardname varchar(100),
  unapplied numeric(19,2),
  source varchar(5),
  docdate datetime, 
  docduedate datetime,
  daysPastDue int,
  Balance numeric(19,2)
)

insert into @openinvoice(docnum, cardcode, cardname,docdate,docduedate,dayspastdue,balance)
select 
  docnum, cardcode, cardname, docdate,docduedate, cast(@cutoff-docduedate as int) as DaysPastDue, doctotal-paidtodate
from 
  oinv
where 
  docdate<@cutoff+1 

insert into @openinvoice(docnum, cardcode,cardname,unapplied,source)
select 
  t0.docnum, t0.cardcode, t0.cardname, doctotal, 'OR' as Src
from 
  orct t0 left join rct2 t1 on t0.docnum=t1.docnum
where
  t1.invoiceid is null and
  t0.doctype='C' and
  t0.docdate < @CutOff+1 
 
insert into @openinvoice(docnum, cardcode,cardname,unapplied,source)
select 
  t2.transid, t0.shortname, t1.cardname,
  case when t0.debit>0 then t0.debit else t0.credit*-1 end AmtPosted,
  'JE' as Src
from 
  ojdt t2 inner join jdt1 t0 on t2.transid=t0.transid inner join ocrd t1 on t0.shortname=t1.cardcode
where 
  t1.cardtype='C' and
  t2.transtype=30 and
  t2.REFdate < @CutOff+1 

INSERT into @openinvoice(docnum, cardcode, cardname,unapplied,source)
select
  t0.docnum, t0.cardcode, t0.cardname,
  doctotal-PAIDTODATE as balance, 'CM' as Src
from 
  orin t0 inner join (select distinct docentry,baseentry from rin1 where docdate < @CutOff+1 ) T1 on t0.docentry=t1.docentry
where 
  t0.docstatus='O' and
  t1.baseentry is null and
  t0.docdate<@cutoff+1 

select  
  cardcode, cardname,
  sum(case when DaysPastDue<=0 then balance else 0 end) as CurrBal,
  sum(unapplied) as UnApplied, source,
  sum(case when DaysPastDue<=30 then balance else 0 end) as Days30,
  sum(case when DaysPastDue<=60 then balance else 0 end) as Days60,
  sum(case when DaysPastDue<=90 then balance else 0 end) as Days90,
  sum(case when DaysPastDue>90 then balance else 0 end) as Days90Over
from 
  @openinvoice
group by 
  cardcode, cardname,source
order by 
  cardcode

Laurence Resubal

Former Member
0 Kudos

Hi Laurence,

May I know is there any field from JDT1 mention which invoices is already offset by the Incoming payment or AR Credit note? Because I do not want those invoices that already offset by incoming payment appear at the Customer Aging Report. Meaning, the data appear in the Customer Aging Report is only those invoices that yet to pay by the customer. Kindly give some advise on it. Thanks a lot.

Thanks and regards,

Angie Ng

Answers (3)

Answers (3)

Former Member
0 Kudos

Oops! Nevermind, I see now how to scroll to the right. The bar is at the very bottom of the window.

Former Member
0 Kudos

I would like to try the customer receivables query posted in this thread by Laurence Resubal. But some of the query does not appear on the right-side of the screen. Would someone please re-post it? Thanks!

former_member186095
Active Contributor
0 Kudos

Hi,

you could develop query aging but it can't be like aging report in B1. Because it is different. it must be divided into some parts. E.g. vendor aging and customer aging without properties and without control accounts, etc.

The difficult parts are to separate the aging days i.e. 30, 60, 90, etc or 45, 90, 140, etc. for them, the query can't do that or even query can do, it will need a lot of effort

Rgds,