How to check Whether Period Of /AP/GL/FA/PO/AR IS Closed

SELECT (SELECT sob.NAME

FROM gl.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",
e.fa_status "FA_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
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) 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
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) 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
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) 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
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob') 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.set_of_books_id ='&sob'
AND fbc.book_type_code = fdp.book_type_code
AND UPPER (fdp.period_name) = UPPER ('&period_name')) e
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.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(+)
ORDER BY 1;

2. To Check Period Status (open/close) of different Oracle Apps modules

SELECT ROWID,
       (SELECT application_short_name
        FROM fnd_application fa
        WHERE fa.application_id = gps.application_id)
          application,
       (SELECT name
        FROM gl_sets_of_books gsp
        WHERE gsp.set_of_books_id = gps.set_of_books_id)
          "setofbookname",
       period_name,
       closing_status,
       DECODE (gps.closing_status,
               'O',
               'Open',
               'C',
               'Closed',
               'F',
               'Future',
               'N',
               'Never'
       )
          status,
       period_num,
       period_year,
       start_date,
       end_date
FROM gl_period_statuses gps
WHERE period_year = '2013'
ORDER BY period_year DESC, period_num DESC;

3. SQL query to find Open/Close Periods in Oracle Apps R12

 

Query to find the Gl Set of Books.

To find SET_OF_BOOKS_ID:

SELECT * FROM gl_sets_of_books

Inventory

SELECT DISTINCT opu.name AS operating_unit
                , per.organization_id AS inv_org_id
                , par.organization_code AS inv_org_code
                , org1.name AS Organization_name
                , per.period_name
                , per.period_year
                , flv.meaning AS status
    FROM org_acct_periods per
       , fnd_lookup_values flv
       , mtl_parameters par
       , hr_all_organization_units org1
       , hr_all_organization_units_tl otl
       , hr_organization_information org2
       , hr_organization_information org3
       , hr_operating_units opu
   WHERE 1 = 1
     AND flv.lookup_type(+) = 'MTL_ACCT_PERIOD_STATUS'
     AND flv.enabled_flag(+) = 'Y'
     AND per.organization_id = par.organization_id
     AND flv.lookup_code(+) =
            DECODE (
               NVL (per.period_close_date, SYSDATE)
             , per.period_close_date, DECODE (
                                         per.open_flag
                                       , 'N', DECODE (summarized_flag
                                                    , 'N', 65
                                                    , 66)
                                       , 'Y', 4
                                       , 'P', 2
                                       , 4)
             , 3)
     AND flv.language = 'US'
     AND UPPER (flv.meaning) != 'CLOSED'
     AND per.organization_id = org1.organization_id
     AND org1.organization_id = otl.organization_id
     AND org1.organization_id = org2.organization_id
     AND org1.organization_id = org3.organization_id
     AND org2.org_information_context = 'Accounting Information'
     AND org3.org_information_context = 'CLASS'
     AND org3.org_information1 = 'INV'
     AND org3.org_information2 = 'Y'
     AND org2.org_information3 = opu.organization_id
     AND PER.PERIOD_NAME = '&Period_Name'
     and opu.set_of_books_id = '&SOB'
ORDER BY opu.name
       , per.organization_id;

To Check whether Periods of AP/AR/GL/FA/PO is closed?

  SELECT DISTINCT (SELECT sob.NAME
                     FROM 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"
                , e.fa_status "FA_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_period_statuses
           WHERE application_id = 101
             AND UPPER (period_name) = UPPER ('&period_name')
             AND set_of_books_id = '&sob') 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_period_statuses
           WHERE application_id = 201
             AND UPPER (period_name) = UPPER ('&period_name')
             AND set_of_books_id = '&sob') 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_period_statuses
           WHERE application_id = 200
             AND UPPER (period_name) = UPPER ('&period_name')
             AND set_of_books_id = '&sob') 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_period_statuses
           WHERE application_id = 222
             AND UPPER (period_name) = UPPER ('&period_name')
             AND set_of_books_id = '&sob') d
       , (SELECT fdp.period_name
               , DECODE (fdp.period_close_date, NULL, 'Open', 'Closed')
                    fa_status
               , fbc.set_of_books_id
            FROM fa_book_controls fbc, fa_deprn_periods fdp
           WHERE fbc.set_of_books_id = '&sob'
             AND fbc.book_type_code = fdp.book_type_code
             AND UPPER (fdp.period_name) = UPPER ('&period_name')) e
   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.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(+)
ORDER BY 1;

 Source: Internet & Others