SQL Query to find Status of GL ,PO, INV Accounting Periods

The following SQL may be used to check for valid periods applicable to GL and PO:

SELECT sob.name "Set of Books",

       fnd.product_code "Porduct Code",

       ps.PERIOD_NAME "Period Name",

       ps.START_DATE "Period Start Date",

       ps.END_DATE "Period End Date",

       decode(ps.closing_status,

              'O',

              'O - Open',

              'N',

              'N - Never Opened',

              'F',

              'F - Future Enterable',

              'C',

              'C - Closed',

              'Unknown') "Period Status"

  FROM gl_period_statuses ps, GL_SETS_OF_BOOKS sob, FND_APPLICATION_VL fnd

 WHERE ps.application_id in (101, 201) -- GL & PO

   and sob.SET_OF_BOOKS_ID = ps.SET_OF_BOOKS_ID

   and fnd.application_id = ps.application_id

   AND ps.adjustment_period_flag = 'N'

   AND (trunc(sysdate) -- Comment line if a a date other than SYSDATE is being tested.

       --AND ('01-APR-2011' -- Uncomment line if a date other than SYSDATE is being tested.

       BETWEEN trunc(ps.start_date) AND trunc(ps.end_date))

order by ps.SET_OF_BOOKS_ID,fnd.product_code, ps.start_date;

The following SQL may be used to check for valid periods applicable to INV:

SELECT mp.organization_id "Organization ID",

       mp.ORGANIZATION_CODE "Organization Code",

       ood.ORGANIZATION_NAME "Organization Name",

       oap.period_name "Period Name",

       oap.period_start_date "Start Date",

       oap.PERIOD_CLOSE_DATE "Closed Date",

       oap.schedule_close_date "Scheduled Close",

       decode(oap.open_flag,

              'P',

              'P - Period Close is processing',

              'N',

              'N - Period Close process is completed',

              'Y',

              'Y - Period is open if Closed Date is NULL',

              'Unknown') "Period Status"

  FROM org_acct_periods             oap,

       org_organization_definitions ood,

       mtl_parameters               mp

 WHERE oap.organization_id = mp.organization_id

   AND mp.organization_id = ood.organization_id(+)

   AND (trunc(sysdate) -- Comment line if a a date other than SYSDATE is being tested.

       --AND ('01-APR-2011' -- Uncomment line if a date other than SYSDATE is being tested.

       BETWEEN trunc(oap.period_start_date) AND

       trunc(oap.schedule_close_date))

 ORDER BY mp.organization_id, oap.period_start_date;

-- If Period Status is 'Y' and Closed Date is not NULL then the closing of the INV period failed.


Source: Metalink Doc ID 1317136.1