on 06-09-2008 9:06 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
Oops! Nevermind, I see now how to scroll to the right. The bar is at the very bottom of the window.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.