SQL Query to find Status of GL and PO Accounting Periods

Shailender Thallam

10 years ago

Here is the query to find out the status of GL and PO Accounting Periods from back end:

SELECT sob.name "Set of Books" ,
  fnd.product_code "Product 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-DEC-2014' -- 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;

Source: Metalink Doc ID 1317136.1

For Further Reading