--*************************************************************************
--Query for getting GL, AP, AR, FA and PO Period Statuses
--Pass Period or Ledger ID for selected data; otherwise all statuses will appear as per setup.
--*************************************************************************
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.set_of_books_id ledger_id
, 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"
, e.fa_status "FA_Status"
--, f.inv_status "INV_Status"
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
FROM gl.gl_period_statuses
WHERE application_id = 101)a-- AND UPPER( period_name ) = UPPER( :period_name ) )a
, (SELECT period_name
, DECODE( closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', closing_status ) po_status
, set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 201)b-- AND UPPER( period_name ) = UPPER( :period_name ))b
, (SELECT period_name
, DECODE( closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', closing_status ) ap_status
, set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 200)c-- AND UPPER( period_name ) = UPPER( :period_name ))c
, (SELECT period_name
, DECODE( closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', closing_status ) ar_status
, set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 222)d-- AND UPPER( period_name ) = UPPER(:period_name ) )d
, (SELECT fdp.period_name, DECODE( fdp.period_close_date, NULL, 'Open', 'Closed' ) fa_status, fbc.set_of_books_id
FROM fa.fa_book_controls fbc, fa.fa_deprn_periods fdp
WHERE fbc.book_type_code = fdp.book_type_code
--AND UPPER( fdp.period_name ) = UPPER( nvl(:period_name,fdp.period_name) )
) e
, (SELECT period_name
, DECODE( closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', closing_status ) inv_status
, set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 401)f-- AND UPPER( period_name ) = UPPER(:period_name ) )d
WHERE 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.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.set_of_books_id(+)
AND a.set_of_books_id = nvl(:p_ledger_id,a.set_of_books_id)
AND a.period_name = upper(nvl(:period_name,a.period_name))
AND substr(a.period_name,1,1)||substr(a.period_name,5,2)||substr(a.period_name,2,2) >= 'P01-12'
ORDER BY 1,2, substr(a.period_name,1,1)||substr(a.period_name,5,2)||substr(a.period_name,2,2)
