Period Status Report

Description
Categories: Cost Accounting - Other, OATUG Public, 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)
          "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
Ask a question
Parameter Name SQL text Validation
Ledger
gl.name=:ledger_name
LOV
Report Option
<parameter_value>
LOV
Functional Area
<parameter_value>
LOV
Period
 
LOV