Query : GL, AP, AR, FA and PO Period Statuses

--*************************************************************************
--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)