Friday 17 August 2012

Period Status Validation Query

Period status in oracle application is very important, since if you want to post or execute a transaction you need to have open/future period depending on the module (application). One company may use many applications like General Ledger, Payables, Receivables, Inventory etc.. . If you want to check which are the periods are open/closed status in a each module from by going to each application, its waste of time. Alternative is, you may run the following query by giving your set of books id and require application details.

SELECT gs.period_name,
fa.application_name,
gs.closing_status,
DECODE (gs.closing_status,
'C',
'Closed',
'O',
'Open',
'F',
'Future',
'W',
'Closed Pending',
'N',
'Never Opened',
'P',
'Permanently Closed'
)
"PERIOD_CLOSING_STATUS"
FROM gl.gl_period_statuses gs, apps.fnd_application_tl fa
WHERE fa.application_id = gs.application_id
AND fa.application_name IN
('Payables', 'Receivables', 'General Ledger')
AND gs.set_of_books_id = :set_of_books_d -- your set of books id
AND gs.period_name in -- Period Name/s for which you want to check
and closing_status in ('O','W','C')
group by Period_name,application_name, gs.closing_status
order by Period_name desc

No comments:

Post a Comment