Period Status Report

Description
Categories: Exclude, Volz
/* +=============================================================================+ -- | SQL Code Copyright 2011-2016 Douglas Volz Consulting, Inc. | -- | All rights reserved. | -- +=============================================================================+ -- | -- | Original Author: Douglas Volz (doug@volzconsulting.com) -...  /* +=============================================================================+
-- | SQL Code Copyright 2011-2016 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz (doug@volzconsulting.com)
-- |
-- | Program Name: XXX_PERIOD_STATUS_REPT.sql
-- |
-- | Parameters:
-- | P_FUNCTIONAL_AREA -- functional area you wish to report, works with NULL,
-- | % or valid functional areas. The names of the
-- | functional areas are: Inventory, Payables, Projects,
-- | Purchasing and Receivables.
-- | P_LEDGER -- general ledger you wish to report, works with
-- | NULL, % or valid ledger names
-- | P_PERIOD_NAME -- The desired accounting period you wish to report
-- | P_REPORT_OPTION -- Parameter used to combine the Period Open and Period Close
-- | reports. The list of value choices are:
-- | 'Periods Not Opened' (similar to 'Only Missing Periods')
-- | 'Open Periods'
-- | 'Closed Periods'
-- | 'All Period Statuses'
-- | Description:
-- | Report to show the inventory accounting period status, whether open or closed,
-- | showing inventory organizations that are not open that should be open or
-- | inventory organizatoins which are closed but should be open. Also shows the
-- | open/close status for Payables, Purchasing, Projects and Receivables.
-- | This report will show open process organizations even if the respective inventory
-- | accounting period is closed for the same period name.
-- |
-- | History for XXX_PERIOD_STATUS_REPT.sql
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== =============== =========================================
-- | 1.0 19 Jan 2015 Douglas Volz Combined the XXX_PERIOD_OPEN_STATUS_REPT.sql
-- | Apps Associates and XXX_PERIOD_CLOSE_STATUS_REPT.sql into
-- | one report. Originally written in 2006 and 2011.
-- | Added OPM Cost Calendar status and Projects.
-- | 1.1 18 Nov 2016 Douglas Volz Commented out the OPM Cost Calendar column.
-- | 1.2 19 Dec 2016 Douglas Volz Fixed list of value choices, was preventing
-- | purchasing operating units from being reported.
-- | Changed to:
-- | 'Periods Not Opened' (similar to 'Only Missing Periods')
-- | 'Open Periods'
-- | 'Closed Periods'
-- | 'All Period Statuses'
-- | 1.3 18 May 2016 Douglas Volz Minor fix for reporting the Organization Hierarchy
-- +=============================================================================+*/

-- =====================================================================
-- Inventory Calendar Periods Not Closed
-- =====================================================================

XXX_PERIOD_STATUS_REPT.sql
   more
SELECT 'Inventory' "Functional Area",
       oap.PERIOD_NAME "Period Name",
       gl.name "Ledger",
       haou2.name "Operating Unit",
       mp.organization_code "Org Code",
       haou.name "Organization Name",
       DECODE (oap.open_flag,
               'N', 'Closed',
               'Y', 'Open',
               'P', 'Processing',
               oap.open_flag)
          "Inventory Period Status",
       fl2.meaning "Summarized Flag",
       -- Revision for version 1.1
       -- DECODE (opm_status.period_status,
       --         'N', 'Never Opened',
       --         'O', 'Open',
       --         'F', 'Frozen',
       --         'C', 'Closed')
       --    "OPM Period Status",
       NVL (
          (SELECT MAX(hoh.ORGANIZATION_HIERARCHY_NAME)
             FROM HRFV_ORGANIZATION_HIERARCHIES hoh
            WHERE     hoh.ORGANIZATION_HIERARCHY_NAME IN
                         (SELECT DISTINCT pos.name name
                            FROM per_organization_structures pos
                            -- Revision for version 1.3
                            WHERE hoh.ORGANIZATION_HIERARCHY_NAME LIKE ('%Open%'))
                  -- Revision for version 1.3
                  AND hoh.ORGANIZATION_HIERARCHY_NAME LIKE ('%Open%')
                  AND (mp.organization_id = hoh.child_organization_id
                       -- Revision for version 1.3
                       OR
                       mp.organization_id = hoh.parent_organization_id)),'None') "Hierarchy Name"
  FROM org_acct_periods oap,
       mtl_parameters mp,
       hr_organization_information hoi,
       hr_all_organization_units haou,
       hr_all_organization_units haou2,
       gl_ledgers gl,
       fnd_lookups fl2,
       (SELECT hoi.organization_id,
               gps.period_code,
               gps.start_date,
               gps.period_status
          FROM mtl_parameters mp1,
               gmf_fiscal_policies gfp,
               gmf_period_statuses gps,
               HR_ORGANIZATION_INFORMATION hoi,
               hr_all_organization_units haou      -- inv_organization_id
         WHERE     hoi.org_information_context = 'Accounting Information'
               AND hoi.organization_id = haou.organization_id -- this gets the organization name
               AND gfp.legal_entity_id = TO_NUMBER (hoi.org_information2)
               AND gps.legal_entity_id = gfp.legal_entity_id
               AND gps.cost_type_id = gfp.cost_type_id
               AND hoi.organization_id = mp1.organization_id
               AND mp1.process_enabled_flag = 'Y') opm_status
 WHERE 1=1 and mp.ORGANIZATION_ID = oap.ORGANIZATION_ID
       AND oap.period_name = :P_PERIOD_NAME                   -- P_PERIOD_NAME
       -- ===================================================================
       -- Show accounting periods which are open
       -- ===================================================================
       AND fl2.lookup_type = 'YES_NO'
       AND fl2.lookup_code = NVL (oap.summarized_flag, 'N')
       -- ===================================================================
       -- using the base tables to avoid the performance issues
       -- with org_organization_definitions and hr_operating_units
       -- ===================================================================
       AND hoi.org_information_context = 'Accounting Information'
       AND hoi.organization_id = mp.organization_id
       AND hoi.organization_id = haou.organization_id -- this gets the organization name
       -- avoid selecting disabled inventory organizations
       AND SYSDATE < NVL (haou.date_to, SYSDATE + 1)
       -- avoid selecting item master orgs
       AND mp.master_organization_id <> mp.organization_id
       AND haou2.organization_id = TO_NUMBER (hoi.org_information3) -- this gets the operating unit id
       AND gl.ledger_id = TO_NUMBER (hoi.org_information1) -- this gets the ledger id
       AND 'INVENTORY' =
              DECODE (UPPER ('&P_FUNCTIONAL_AREA'),
                      '%', 'INVENTORY',
                      NULL, 'INVENTORY',
                      UPPER ('&P_FUNCTIONAL_AREA'))         -- P_FUNCTIONAL_AREA
       AND oap.organization_id = opm_status.organization_id(+)
       AND oap.period_start_date = opm_status.start_date(+)
       --Report Option Logic
       AND (   (oap.open_flag =
                   DECODE ('&P_REPORT_OPTION',
                           'Open Periods', 'Y',
                           'Closed Periods', 'N',
                           'Periods Not Opened', 'X',
                           oap.open_flag))
            OR (NVL (opm_status.period_status, 'Z') =
                   DECODE ('&P_REPORT_OPTION',
                           'Open Periods', 'Y',
                           'Closed Periods', 'C',
                           'Periods Not Opened', 'X',
                           opm_status.period_status))
            OR (NVL (opm_status.period_status, 'Z') =
                   DECODE ('&P_REPORT_OPTION',
                           'Open Periods', 'F',
                           'X')))
-- =====================================================================
-- Show accounting periods which should be open but are not
-- If an accounting period is not opened it will not exist in the
-- inventory period calendar, will not be in org_acct_periods
-- =====================================================================
UNION ALL
SELECT 'Inventory' "Functional Area",
       gp.PERIOD_NAME "Period Name",
       gl.name "Ledger",
       haou2.name "Operating Unit",
       mp.organization_code "Org Code",
       haou.name "Organization Name",
       'Never Opened' "Inventory Period Status",
       'N/A' "Summarized Flag",
       -- Revision for version 1.1
       -- 'N/A' "OPM Period Status",
       NVL (
          (SELECT MAX (hoh.ORGANIZATION_HIERARCHY_NAME)
             FROM HRFV_ORGANIZATION_HIERARCHIES hoh
            WHERE     hoh.ORGANIZATION_HIERARCHY_NAME IN
                         (SELECT DISTINCT pos.name name
                            FROM per_organization_structures pos
                            -- Revision for version 1.3
                            WHERE hoh.ORGANIZATION_HIERARCHY_NAME LIKE ('%Open%'))
                  -- Revision for version 1.3
                  AND hoh.ORGANIZATION_HIERARCHY_NAME LIKE ('%Open%')
                       -- Revision for version 1.3
                  AND (mp.organization_id = hoh.child_organization_id
                       -- Revision for version 1.3
                       OR
                       mp.organization_id = hoh.parent_organization_id)),'None') "Hierarchy Name"
  FROM gl_periods gp,
       mtl_parameters mp,
       hr_organization_information hoi,
       hr_all_organization_units haou,
       hr_all_organization_units haou2,
       gl_ledgers gl
 WHERE 1=1 and gp.period_set_name = gl.period_set_name
       AND gp.period_name = :P_PERIOD_NAME                    -- P_PERIOD_NAME
       -- ===================================================================
       -- using the base tables to avoid the performance issues
       -- with org_organization_definitions and hr_operating_units
       -- ===================================================================
       AND hoi.org_information_context = 'Accounting Information'
       AND hoi.organization_id = mp.organization_id
       AND hoi.organization_id = haou.organization_id -- this gets the organization name
       -- avoid selecting disabled inventory organizations
       AND SYSDATE < NVL (haou.date_to, SYSDATE + 1)
       -- avoid selecting item master orgs
       AND mp.master_organization_id <> mp.organization_id
       -- avoid selecting inventory orgs created after the period end date
       -- for reporting against prior accounting periods
       AND gp.end_date >= mp.creation_date
       AND haou2.organization_id = TO_NUMBER (hoi.org_information3) -- this gets the operating unit id
       AND gl.ledger_id = TO_NUMBER (hoi.org_information1) -- this gets the ledger id
       -- ===================================================================
       -- Check to see if the accounting period already exists in the
       -- inventory calendar, in ORG_ACCT_PERIODS
       -- ===================================================================
       AND NOT EXISTS
                  (SELECT 'x'
                     FROM inv.org_acct_periods oap
                    WHERE     oap.organization_id = mp.organization_id
                          AND oap.period_name = :P_PERIOD_NAME) -- P_PERIOD_NAME
       -- Revision for version 1.1
       AND 'INVENTORY' =
              DECODE (UPPER ('&P_FUNCTIONAL_AREA'),
                      '%', 'INVENTORY',
                      NULL, 'INVENTORY',
                      UPPER ('&P_FUNCTIONAL_AREA'))         -- P_FUNCTIONAL_AREA
       --Report Option Logic
       AND 'Y' = DECODE ('&P_REPORT_OPTION', 'Periods Not Opened', 'Y', 'N')
-- =====================================================================
-- Payables
-- =====================================================================
UNION ALL
SELECT 'Payables' "Functional Area",
       gps.PERIOD_NAME "Period Name",
       gl.name "Ledger",
       haou.name "Operating Unit",
       'N/A' "Org Code",
       'N/A' "Organization Name",
       -- Fix for version 1.2, change closing_status to 'Never Opened'
       DECODE (gps.closing_status,
               'C', 'Closed',
               'F', 'Future',
               'N', 'Never Opened',
               'O', 'Open',
               'W', 'Pending Close',
               'P', 'Perm Closed',
               gps.closing_status)
          "Inventory Period Status",
       -- 'O' for open
       -- 'F' for future enterable
       -- 'C' for closed
       -- 'P' for permanently closed
       -- 'N' for never opened
       '' "Summarized Flag",
       -- Revision for version 1.1
       -- '' "OPM Period Status",
       'N/A' "Hierarchy Name"
  FROM gl_period_statuses gps,
       fnd_application fa,
       hr_organization_information hoi,
       hr_all_organization_units haou,
       gl_ledgers gl
 WHERE 1=1 and gps.application_id = fa.APPLICATION_ID
       AND gps.period_name = :P_PERIOD_NAME                   -- P_PERIOD_NAME
       AND fa.APPLICATION_SHORT_NAME = 'SQLAP'
       -- ===================================================================
       -- using the base tables to avoid the performance issues
       -- with org_organization_definitions and hr_operating_units
       -- ===================================================================
       AND gl.ledger_id = gps.ledger_id
       AND hoi.org_information_context = 'Operating Unit Information'
       AND hoi.organization_id = haou.organization_id -- this gets the operating unit id
       AND gl.ledger_id = TO_NUMBER (hoi.org_information3) -- this joins OU to GL
       -- Revision for version 1.5
       AND 'PAYABLES' =
              DECODE (UPPER ('&P_FUNCTIONAL_AREA'),
                      '%', 'PAYABLES',
                      NULL, 'PAYABLES',
                      UPPER ('&P_FUNCTIONAL_AREA'))         -- P_FUNCTIONAL_AREA
       AND (   (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION',
                           'Open Periods', 'O',
                           'Closed Periods', 'C',
                           'Periods Not Opened', 'N',
                           'All Period Statuses', 'C',
                           'X'                           --,gps.closing_status
                              ))
            OR (GPS.CLOSING_STATUS =
                   DECODE ('&P_REPORT_OPTION', 'Periods Not Opened', 'F', 'X'))
            OR (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION', 'All Period Statuses', 'O', 'X'))
            OR (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION', 'All Period Statuses', 'W', 'X')))
-- =====================================================================
-- Receivables
-- =====================================================================
UNION ALL
SELECT 'Receivables' "Functional Area",
       gps.PERIOD_NAME "Period Name",
       gl.name "Ledger",
       haou.name "Operating Unit",
       'N/A' "Org Code",
       'N/A' "Organization Name",
       -- Fix for version 1.2, change closing_status to 'Never Opened'
       DECODE (gps.closing_status,
               'C', 'Closed',
               'F', 'Future',
               'N', 'Never Opened',
               'O', 'Open',
               'P', 'Perm Closed',
               'W', 'Pending Close',
               gps.closing_status)
          "Inventory Period Status",
       -- 'O' for open
       -- 'F' for future enterable
       -- 'C' for closed
       -- 'P' for permanently closed
       -- 'N' for never opened
       '' "Summarized Flag",
       -- Revision for version 1.1
       -- '' "OPM Period Status",
       'N/A' "Hierarchy Name"
  FROM gl_period_statuses gps,
       fnd_application fa,
       hr_organization_information hoi,
       hr_all_organization_units haou,
       gl_ledgers gl
 WHERE 1=1 and gps.application_id = fa.APPLICATION_ID
       AND gps.period_name = :P_PERIOD_NAME                   -- P_PERIOD_NAME
       AND fa.APPLICATION_SHORT_NAME = 'AR'
       -- ===================================================================
       -- using the base tables to avoid the performance issues
       -- with org_organization_definitions and hr_operating_units
       -- ===================================================================
       AND gl.ledger_id = gps.ledger_id
       AND hoi.org_information_context = 'Operating Unit Information'
       AND hoi.organization_id = haou.organization_id -- this gets the operating unit id
       AND gl.ledger_id = TO_NUMBER (hoi.org_information3) -- this joins OU to GL
       AND 'RECEIVABLES' =
              DECODE (UPPER ('&P_FUNCTIONAL_AREA'),
                      '%', 'RECEIVABLES',
                      NULL, 'RECEIVABLES',
                      UPPER ('&P_FUNCTIONAL_AREA'))         -- P_FUNCTIONAL_AREA
       AND (   (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION',
                           'Open Periods', 'O',
                           'Closed Periods', 'C',
                           'Periods Not Opened', 'N',
                           'All Period Statuses', 'C',
                           'X'                           --,gps.closing_status
                              ))
            OR (GPS.CLOSING_STATUS =
                   DECODE ('&P_REPORT_OPTION', 'Periods Not Opened', 'F', 'X'))
            OR (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION', 'All Period Statuses', 'O', 'X'))
            OR (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION', 'All Period Statuses', 'W', 'X')))
-- =====================================================================
-- Purchasing
-- =====================================================================
UNION ALL
SELECT 'Purchasing' "Functional Area",
       gps.PERIOD_NAME "Period Name",
       gl.name "Ledger",
       haou.name "Operating Unit",
       'N/A' "Org Code",
       'N/A' "Organization Name",
       -- Fix for version 1.2, change closing_status to 'Never Opened'
       DECODE (gps.closing_status,
               'C', 'Closed',
               'F', 'Future',
               'N', 'Never Opened',
               'O', 'Open',
               'P', 'Perm Closed',
               'W', 'Pending Close',
               gps.closing_status)
          "Inventory Period Status",
       -- 'O' for open
       -- 'F' for future enterable
       -- 'C' for closed
       -- 'P' for permanently closed
       -- 'N' for never opened
       '' "Summarized Flag",
       -- Revision for version 1.1
       -- '' "OPM Period Status",
       'N/A' "Hierarchy Name"
  FROM gl_period_statuses gps,
       fnd_application fa,
       hr_organization_information hoi,
       hr_all_organization_units haou,
       gl_ledgers gl
 WHERE 1=1 and gps.application_id = fa.APPLICATION_ID
       AND gps.period_name = :P_PERIOD_NAME                   -- P_PERIOD_NAME
       AND fa.APPLICATION_SHORT_NAME = 'PO'
       -- ===================================================================
       -- using the base tables to avoid the performance issues
       -- with org_organization_definitions and hr_operating_units
       -- ===================================================================
       AND gl.ledger_id = gps.ledger_id
       AND hoi.org_information_context = 'Operating Unit Information'
       AND hoi.organization_id = haou.organization_id -- this gets the operating unit id
       AND gl.ledger_id = TO_NUMBER (hoi.org_information3) -- this joins OU to GL
       AND 'PURCHASING' =
              DECODE (UPPER ('&P_FUNCTIONAL_AREA'),
                      '%', 'PURCHASING',
                      NULL, 'PURCHASING',
                      UPPER ('&P_FUNCTIONAL_AREA'))         -- P_FUNCTIONAL_AREA
       AND (   (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION',
                           'Open Periods', 'O',
                           'Closed Periods', 'C',
                           'Periods Not Opened', 'N',
                           'All Period Statuses', 'C',
                           'X'                           --,gps.closing_status
                              ))
            OR (GPS.CLOSING_STATUS =
                   DECODE ('&P_REPORT_OPTION', 'Periods Not Opened', 'F', 'X'))
            OR (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION', 'All Period Statuses', 'O', 'X'))
            OR (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION', 'All Period Statuses', 'W', 'X')))
-- =====================================================================
-- Projects
-- =====================================================================
UNION ALL
SELECT 'Projects' "Functional Area",
       gps.PERIOD_NAME "Period Name",
       gl.name "Ledger",
       haou.name "Operating Unit",
       'N/A' "Org Code",
       'N/A' "Organization Name",
       -- Fix for version 1.2, change closing_status to 'Never Opened'
       DECODE (gps.closing_status,
               'C', 'Closed',
               'F', 'Future',
               'N', 'Never Opened',
               'O', 'Open',
               'P', 'Perm Closed',
               'W', 'Pending Close',
               gps.closing_status)
          "Inventory Period Status",
       -- 'O' for open
       -- 'F' for future enterable
       -- 'C' for closed
       -- 'P' for permanently closed
       -- 'N' for never opened
       '' "Summarized Flag",
       -- Revision for version 1.1
       -- '' "OPM Period Status",
       'N/A' "Hierarchy Name"
  FROM gl_period_statuses gps,
       fnd_application fa,
       hr_organization_information hoi,
       hr_all_organization_units haou,
       gl_ledgers gl
 WHERE 1=1 and gps.application_id = fa.APPLICATION_ID
       AND gps.period_name = :P_PERIOD_NAME                   -- P_PERIOD_NAME
       AND fa.APPLICATION_SHORT_NAME = 'IPA'
       -- ===================================================================
       -- using the base tables to avoid the performance issues
       -- with org_organization_definitions and hr_operating_units
       -- ===================================================================
       AND gl.ledger_id = gps.ledger_id
       AND hoi.org_information_context = 'Operating Unit Information'
       AND hoi.organization_id = haou.organization_id -- this gets the operating unit id
       AND gl.ledger_id = TO_NUMBER (hoi.org_information3) -- this joins OU to GL
       AND 'PROJECTS' =
              DECODE (UPPER ('&P_FUNCTIONAL_AREA'),
                      '%', 'PROJECTS',
                      NULL, 'PROJECTS',
                      UPPER ('&P_FUNCTIONAL_AREA'))         -- P_FUNCTIONAL_AREA
       AND (   (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION',
                           'Open Periods', 'O',
                           'Closed Periods', 'C',
                           'Periods Not Opened', 'N',
                           'All Period Statuses', 'C',
                           'X'                           --,gps.closing_status
                              ))
            OR (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION', 'Periods Not Opened', 'F', 'X'))
            OR (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION', 'All Period Statuses', 'O', 'X'))
            OR (gps.closing_status =
                   DECODE ('&P_REPORT_OPTION', 'All Period Statuses', 'W', 'X')))
ORDER BY 1,
         2,
         3,
         4,
         5
Parameter Name SQL text Validation
Ledger
gl.name = :ledger_name
LOV
Report Option
<parameter_value>
LOV
Functional Area
<parameter_value>
LOV
Period
 
LOV