Script to Show Period Statuses in GL, Purchasing, and Payables [ID 429905.1]

Script to Show Period Statuses in GL, Purchasing, and Payables [ID 429905.1]

To check the period status for AP & GL & PO via backend in oracle apps.

To pass the input as Set of Books ID

SELECTa.period_name, a.period_num, a.gl_status, b.po_status, c.ap_status
FROM (SELECT period_name, period_num,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status) gl_status
FROM gl_period_statuses
WHERE application_id = 101
AND start_date >= '01-JAN-11'
AND end_date < '01-JAN-13'
AND set_of_books_id = &&set_of_books_id) a,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status) po_status
FROM gl_period_statuses
WHERE application_id = 201
AND start_date >= '01-JAN-11'
AND end_date < '01-JAN-13'
AND set_of_books_id = &&set_of_books_id) b,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) ap_status
FROM gl_period_statuses
WHERE application_id = 200
AND start_date >= '01-JAN-11'
AND end_date < '01-JAN-13'
AND set_of_books_id = &&set_of_books_id) c
WHERE a.period_name = b.period_name AND a.period_name = c.period_name
ORDER
BY a.period_num