ERPstuff - Oracle EBS Community

Hi Guru

Please help me to find the back dated outstanding ageing query , example Today is 14/01/2009 and i want outstanding ageing for 31.10.2008.


Thanks

Yogesh Kumar

Hello.

On AP yu have the Accounts Payable Trial Balance. In AR you have all the Aging Buckets.

Octavio

Dear

In AP, all bucket ageing as on date bases. But I want to get back dated ageing . I want to explain in detail:

Let us take one example :

Today is 14/01/2009 , My management wants to get ageing as on 31/10/2008. If i see ageing of 31/10/2008 on 14/01/2009 , this report will be different than the ageing was on 31/10/2008

Please help me to give query , if you have

Hai,

In AP you can take Accounts payables trial balance report, that gives back dated aging report which has to match with GL figure as on the date. This gives for Liability Acount. Similarly for AP prepayments Prepayments register or you can have a query.

In AR Aging 7 buckets or aging 4 buckets Report or Customer open balance letter ( This has some letter header) or Aging by Account Report gives your required details.

But The AR reports donot give all details i.e. Transaction/recpt number, GL date, Trx/Recpt date, original amunt , balance , code combination, Customer name.

Accounts Payables TrBalance is a good report . For Advances from Customers I can give a query tomarrow - most of the details as per your requirement you can get from gl_je_lines.

AR details is difficult. But in 2 days I can give that query also.


Ravi Sankar

Thanks for reply, Please give query for both AR/AP.I will be thankful to you.

-- For advances from Customers Account, run queries seperatly for AR source items and nonAR source items.

-- AR source items query gives balances as of required date.--the query is


select B.REFERENCE_4 "TRX NO",
K.CUSTOMER_NAME,
SUM(NVL(B.ACCOUNTED_DR,0)-NVL(B.ACCOUNTED_CR,0) )"NET",
min(a.default_effective_date) "EFFECTIVE_DATE"
c.concatenated_segments "CODE_COMBINATION"
from gl_je_headers a, AR_CUSTOMERS_V K,
gl_je_lines b,
gl_code_combinations_kfv c,
gl_je_batches d,
gl_je_categories e
where a.je_header_id = b.je_header_id
and b.code_combination_id = c.code_combination_id
and a.je_batch_id = d.je_batch_id
AND a.default_effective_date <= '31-DEC-08' -- as on the date required
and a.je_source = 'Receivables'
and c.segmentn = 'the account number' --the account number
AND K.CUSTOMER_ID (+) = B.REFERENCE_7
and a.je_category = e.je_category_name
AND a.status = 'P'
AND A.SET_OF_BOOKS_ID = 'your set of books id' -- your set of books id
GROUP BY B.REFERENCE_4 , K.CUSTOMER_NAME,
c.concatenated_segments
HAVING SUM(NVL(B.ACCOUNTED_DR,0)-NVL(B.ACCOUNTED_CR,0) )<>0


--for entries on non AR source query is

select a.name "Journal Name", d.name "Batch Name",a.je_source "Source",
(NVL(B.ACCOUNTED_DR,0)-NVL(B.ACCOUNTED_CR,0) )"NET",
TO_CHAR(a.default_effective_date,'DD-MON-YYYY') "EFFECTIVE_DATE",
c.concatenated_segments "CODE_COMBINATION"
from gl_je_headers a,
gl_je_lines b,
gl_code_combinations_kfv c,
gl_je_batches d,
gl_je_categories e
where a.je_header_id = b.je_header_id
and b.code_combination_id = c.code_combination_id
and a.je_batch_id = d.je_batch_id
AND a.default_effective_date <= '31-DEC-08' -- as on the date required
and a.je_source <> 'Receivables'
and c.segmentn = 'the account number' --the account number
and a.je_category = e.je_category_name
AND a.status = 'P'
AND A.SET_OF_BOOKS_ID = 'your set of books id' -- your set of books id

-- the net of both the queries will be equal to GL balance.


Regards
Ravi Sankar

I want Ageing on the basis of AR_Payments_schedule_all Table & GL Basis.

Attached query has some problem