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
Back Dated Outstanding Ageing for Debtor/Supplier
-
- Posts: 15
- Joined: Wed Jan 14, 2009 1:51 am
- Location: India
-
- Posts: 15
- Joined: Wed Jan 14, 2009 1:51 am
- Location: India
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
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
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
-
- Posts: 15
- Joined: Wed Jan 14, 2009 1:51 am
- Location: India
-- 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
-- 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
-
- Posts: 15
- Joined: Wed Jan 14, 2009 1:51 am
- Location: India
Who is online
Users browsing this forum: Bing [Bot] and 2 guests