SQL For Period Close Status snapshot for All Ledgers with All Subledgers.
SELECT (SELECT sob.NAME
FROM apps.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id) “SOB_Name”,
a.period_name “Period_Name”, a.period_num “Period_Num”,
a.gl_status “GL_Status”,TO_CHAR(a.LAST_UPDATE_DATE, ‘MM-DD-YY HH:MI:SS AM’) “GL_Update_Date”,
c.ap_status “AP_Status”, TO_CHAR(c.LAST_UPDATE_DATE, ‘MM-DD-YY HH:MI:SS AM’) “AP_Update_Date”,
d.ar_status “AR_Status”,TO_CHAR(d.LAST_UPDATE_DATE, ‘MM-DD-YY HH:MI:SS AM’) “AR_Update_Date”,
e.fa_status “FA_Status”,TO_CHAR(e.LAST_UPDATE_DATE, ‘MM-DD-YY HH:MI:SS AM’) “FA_Update_Date”
FROM
(SELECT period_name, period_num,
DECODE (closing_status,
‘O’, ‘Open’,
‘C’, ‘Closed’,
‘F’, ‘Future’,
‘N’, ‘Never’,
closing_status
) gl_status,
set_of_books_id,LAST_UPDATE_DATE
FROM apps.gl_period_statuses
WHERE application_id = 101
AND UPPER (period_name) = UPPER (‘MAR-20’)
AND set_of_books_id in (<<Ledger_id>>) ) a,
(SELECT period_name,
DECODE (closing_status,
‘O’, ‘Open’,
‘C’, ‘Closed’,
‘F’, ‘Future’,
‘N’, ‘Never’,
closing_status
) ap_status,set_of_books_id,LAST_UPDATE_DATE
FROM apps.gl_period_statuses
WHERE application_id = 200
AND UPPER (period_name) = UPPER (‘MAR-20’)
AND set_of_books_id in ( <<Ledger_id>>) ) c,
(SELECT period_name,
DECODE (closing_status,
‘O’, ‘Open’,
‘C’, ‘Closed’,
‘F’, ‘Future’,
‘N’, ‘Never’,
closing_status
) ar_status,set_of_books_id,LAST_UPDATE_DATE
FROM apps.gl_period_statuses
WHERE application_id = 222
AND UPPER (period_name) = UPPER (‘MAR-20’)
AND set_of_books_id in ( <<Ledger_id>>)) d,
(SELECT fdp.period_name,
DECODE (fdp.period_close_date,
NULL, ‘Open’,
‘Closed’
) fa_status,fbc.set_of_books_id,fbc.LAST_UPDATE_DATE
FROM apps.fa_book_controls fbc, apps.fa_deprn_periods fdp
WHERE fbc.set_of_books_id in (<<Ledger_id>>)
and fbc.book_class=’CORPORATE’
AND fbc.book_type_code = fdp.book_type_code
AND UPPER (fdp.period_name) = UPPER (‘MAR-20’)) e
WHERE 1=1
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
and a.set_of_books_id=c.set_of_books_id(+)
and a.set_of_books_id=d.set_of_books_id(+)
and a.set_of_books_id=e.set_of_books_id(+)
ORDER BY 1
;
