Tuesday, 11 November 2014

SQL Query to know the Fiscal Month End close status for Financials (AP,AR and GL)

SELECT   glps.period_name,
           fa.application_name "Application Name",
           DECODE (closing_status, 'N', 'N/a', fu.user_name) "Updated By",
           DECODE (closing_status,
                   'N', 'Never Opened',
                   'C', 'Closed',
                   'O', 'Open',
              "Closing Status"
    FROM   apps.gl_period_statuses glps,
           apps.fnd_application_tl fa,
           apps.fnd_user fu,
           apps.gl_sets_of_books gsob
   WHERE       fa.application_id = glps.application_id
           AND glps.last_updated_by = fu.user_id
           AND glps.set_of_books_id = gsob.set_of_books_id
           AND glps.period_name LIKE 'Mar-11'    ----- Provide the month name as of your choice
           AND glps.closing_status NOT IN ('N', 'F')
           AND fa.application_id IN (101, 140, 200, 201, 222)  ---- Provide the Application ID as of your oracle setup
ORDER BY   5 DESC, 2, 3;

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.