SELECT distinct (
SELECT sob.NAME
FROM apps.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id
) "SOB_Name"
,(
SELECT sob.set_of_books_id
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"
,b.po_status "PO_Status"
,c.ap_status "AP_Status"
,d.ar_status "AR_Status"
,DECODE(e.fa_status,null,'NA',e.fa_status) "FA_Status"
,DECODE(f.rl_status,null,'NA',f.rl_status） "RL_Status"
,g.pa_status "PA_Status"
FROM (
SELECT period_name
,period_num
,DECODE(closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never','W','Pending Close', closing_status) gl_status
,set_of_books_id
FROM apps.gl_period_statuses
WHERE application_id = 101
) a
,(
SELECT period_name
,DECODE(closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never','W','Pending Close', closing_status) po_status
,set_of_books_id
FROM apps.gl_period_statuses
WHERE application_id = 201
) b
,(
SELECT period_name
,DECODE(closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never','W','Pending Close', closing_status) ap_status
,set_of_books_id
FROM apps.gl_period_statuses
WHERE application_id = 200
) c
,(
SELECT period_name
,DECODE(closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', 'W','Pending Close',closing_status) ar_status
,set_of_books_id
FROM apps.gl_period_statuses
WHERE application_id = 222
) d
,(
SELECT fdp.period_name
,DECODE(fdp.period_close_date, null, 'Open', 'Closed') fa_status
,fbc.set_of_books_id
FROM apps.fa_book_controls fbc
,apps.fa_deprn_periods fdp
WHERE fbc.book_type_code = fdp.book_type_code
) e
,(
SELECT period_name
,period_num
,DECODE(closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never','W','Pending Close', closing_status) rl_status
,from_ledger_id
FROM apps.gl_period_statuses gps
,apps.gl_consolidation gc
WHERE gps.application_id = 101
AND gps.set_of_books_id = gc.to_ledger_id
) f
,(
SELECT period_name
,DECODE(closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never','W','Pending Close', closing_status) pa_status
,set_of_books_id
FROM apps.gl_period_statuses
WHERE application_id = 8721
) g
WHERE a.period_name IN (
SELECT period_name
FROM apps.gl_period_statuses
WHERE start_date between '01/OCT/2018' and sysdate+60
)
AND a.period_name = b.period_name(+)
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
AND a.period_name = f.period_name(+)
AND a.period_name = g.period_name(+)
AND a.set_of_books_id IN (
SELECT ls.ledger_id
FROM apps.gl_ledger_segment_values ls
,apps.xle_entity_profiles xle
,apps.gl_ledgers l
WHERE ls.segment_value = xle.LEGAL_ENTITY_IDENTIFIER
AND ls.ledger_id = l.ledger_id
AND xle.effective_from IS NOT NULL
AND (
xle.effective_to > sysdate
OR xle.effective_to IS NULL
)
)
AND a.set_of_books_id = b.set_of_books_id(+)
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(+)
AND a.set_of_books_id = f.from_ledger_id(+)
AND a.set_of_books_id = g.set_of_books_id(+)
ORDER BY 1,4 |
