CAC Inventory Periods Not Closed or Summarized
Description
Categories: Enginatics
Repository: Github
Repository: Github
Report to find all inventory accounting periods which are either still open or closed but not summarized. When you close the inventory accounting period, the Period Close Reconciliation Report creates a very useful month-end summary of your inventory quantities and balances, by item, subinventory or intransit, cost group and inventory organization. You can use this information to create a effici ...
more
Run
CAC Inventory Periods Not Closed or Summarized and other Oracle EBS reports with Blitz Report™ on our demo environment
select fav.application_name Functional_Area, nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, haou.name Organization_Name, oap.period_name Period_Name, oap.period_num Period_Number, oap.period_year Period_Year, ml.meaning Inventory_Period_Status, fl2.meaning Summarized_Flag, -- Revision for version 1.2 and 1.4 coalesce( (select max(hoh.organization_hierarchy_name) organization_hierarchy_name from hrfv_organization_hierarchies hoh where hoh.organization_hierarchy_name= '&p_hierarchy_name' and (mp.organization_id = hoh.child_organization_id or mp.organization_id = hoh.parent_organization_id) ), (select max(hoh.organization_hierarchy_name) organization_hierarchy_name from hrfv_organization_hierarchies hoh where regexp_like(hoh.organization_hierarchy_name,'&p_name_open|&p_name_close|&p_name_period','i') and (mp.organization_id = hoh.child_organization_id or mp.organization_id = hoh.parent_organization_id) ) ) Hierarchy_Name from org_acct_periods oap, mtl_parameters mp, mfg_lookups ml, hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, -- Revision for version 1.3 fnd_application_vl fav, fnd_lookups fl2 where mp.organization_id = oap.organization_id -- =================================================================== -- Show accounting periods which are open -- =================================================================== -- Report Option Logic -- If from an upgrade, the original R11i period close rows were never -- upgraded after Oracle put in the summarization feature (in 11.5.10) -- So earlier periods which existed before summarization have a null value and (oap.open_flag = 'Y' or nvl(oap.summarized_flag,'Y') = 'N' ) and fl2.lookup_type = 'YES_NO' and fl2.lookup_code = nvl(oap.summarized_flag, 'N') and ml.lookup_type = 'MTL_ACCT_PERIOD_STATUS' and ml.lookup_code = decode((oap.open_flag||'-'||nvl(oap.summarized_flag,'N')), 'N'||'-'||'N', 65, -- Closed not Summarized 'N'||'-'||'Y', 66, -- Closed 'P'||'-'||'N', 2, -- Processing 'P'||'-'||'Y', 2, -- Processing 'Y'||'-'||'N', 3, -- Open 'Y'||'-'||'Y', 3, -- Open 'N'||'-'||'E', 4, -- Error 'Y'||'-'||'E', 4, -- Error 3) -- Revision for version 1.3 and fav.application_id = 401 -- Inventory -- =================================================================== -- Using base tables to avoid 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 and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id -- avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) and gl.ledger_id = to_number(hoi.org_information1) -- this gets the ledger id and 1=1 -- p_ledger, p_operating_unit, p_org_code -- =================================================================== -- avoid selecting item master orgs and mp.master_organization_id <> mp.organization_id order by fav.application_name, -- Functional Area nvl(gl.short_name, gl.name), -- Ledger haou2.name, -- Operating Unit mp.organization_code, -- Org Code oap.period_year asc, -- Period Year oap.period_num asc, -- Period Number oap.period_name -- Period Name |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Hierarchy Name |
|
LOV | |
Organization Code |
|
LOV | |
Operating Unit |
|
LOV | |
Ledger |
|
LOV |