CAC Inventory Out-of-Balance

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to show any differences in the period end snapshot that is created when you close the inventory periods. This represents any differences between the cumulative inventory accounting entries and the onhand valuation of the subinventories and intransit stock locations.

Parameters:
===========
Period Name (Closed): the closed inventory accounting period you wish to report (mandat ...  Report to show any differences in the period end snapshot that is created when you close the inventory periods. This represents any differences between the cumulative inventory accounting entries and the onhand valuation of the subinventories and intransit stock locations.

Parameters:
===========
Period Name (Closed): the closed inventory accounting period you wish to report (mandatory).
Minimum Value Difference: the minimum difference to report, defaulted to a value of one. To see all differences enter a value of zero (mandatory).
Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional).
Category Set 2: any item category you wish, typically the Inventory category set (optional).
Item Number: enter the specific item number(s) you wish to report (optional).
Organization Code: enter the specific inventory organization(s) you wish to report (optional).
Operating Unit: enter the specific operating unit(s) you wish to report (optional).
Ledger: enter the specific ledger(s) you wish to report (optional).

//* +=============================================================================+
-- | Copyright 2006-2024 Douglas Volz Consulting, Inc.
-- | Permission to use this code is granted provided the original author is
-- | acknowledged. No warranties, express or otherwise is included in this permission.
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Program Name: xxx_inv_snapshot_diff_rept.sql
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 02 APR 2006 Douglas Volz Initial Coding
-- | 1.14 19 Nov 2015 Douglas Volz Commented out the Cost Group information. Not Consistent.
-- | 1.15 17 Jul 2018 Douglas Volz Now report G/L short name.
-- | 1.16 06 Jan 2020 Douglas Volz Added Org Code and Operating Unit parameters.
-- | 1.17 30 Apr 2020 Douglas Volz Changed to multi-language views for the item
-- | master, inventory orgs and operating units.
-- | 1.18 18 May 2020 Douglas Volz Added language for item status.
-- | 1.19 14 Jun 2024 Douglas Volz Remove tabs, reinstall parameters and org access controls.
-- +=============================================================================+*/
   more 
select  nvl(gl.short_name, gl.name) Ledger,
-- ==============================================
-- This first select gets the subinventory values
-- ==============================================
        haou2.name Operating_Unit,
        mp.organization_code Org_Code,
        -- Revision for version 1.14
        -- (select ccg.cost_group
        --  from   cst_cost_groups ccg
        --  where  ccg.cost_group_id  = cpcs.cost_group_id) Cost_Group,
        -- End revision for version 1.14
        oap.period_name Period_Name,
        &segment_columns
        msiv.concatenated_segments Item_Number,
        msiv.description Item_Description,
        -- Revision for version 1.12
        fcl.meaning Item_Type,
        -- Revision for version 1.18
        misv.inventory_item_status_code_tl Item_Status,
&category_columns
        -- Revision for version 1.18
        nvl(cpcs.subinventory_code, ml.meaning) Subinventory_or_Intransit,
        muomv.uom_code UOM_Code,
        round(sum(nvl(cpcs.rollback_quantity, 0)),3) Quantity,
        round(sum(nvl(cpcs.rollback_value, 0)),2) Onhand_Value,
        round(sum(nvl(cpcs.accounted_value, 0)),2) Accounted_Value,
        round(sum((nvl(cpcs.onhand_value_discrepancy, 0)) + (nvl(cpcs.intransit_value_discrepancy, 0))),2) Difference
        -- End revision for version 1.18
from    cst_period_close_summary cpcs,
        org_acct_periods oap,
        mtl_parameters mp,
        mtl_system_items_vl msiv,
        -- Revision for version 1.18
        mtl_item_status_vl misv,
        mtl_units_of_measure_vl muomv,
        mfg_lookups ml,
        -- End revision for version 1.18
        -- Revision for version 1.13
        -- Get valuation accounts based on Costing_Method and Cost Group Accounting
        (-- Standard Costing, no Cost Group Accounting
         select msub.organization_id,
                msub.secondary_inventory_name,
                msub.material_account
         from   mtl_secondary_inventories msub,
                mtl_parameters mp
         where  msub.organization_id            = mp.organization_id
         and    nvl(mp.cost_group_accounting,2) = 2 -- No
         and    mp.primary_cost_method          = 1 -- Standard Costing
         union all
         -- Not Standard Costing, no Cost Group Accounting
         select msub.organization_id,
                msub.secondary_inventory_name,
                mp.material_account
         from   mtl_secondary_inventories msub,
                mtl_parameters mp
         where  msub.organization_id            = mp.organization_id
         and    nvl(mp.cost_group_accounting,2) = 2 -- No
         and    mp.primary_cost_method <> 1 -- not Standard Average Costing
         union all
         -- With Cost Group Accounting
         select msub.organization_id,
                msub.secondary_inventory_name,
                ccga.material_account
         from   mtl_secondary_inventories msub,
                cst_cost_group_accounts ccga,
                mtl_parameters mp
         where  msub.organization_id            = mp.organization_id
         and    mp.cost_group_accounting        = 1 -- Yes
         and    ccga.cost_group_id              = msub.default_cost_group_id
         union all
         -- Cost Group Accounting but the Subinventory Cost Group Id is null
         select msub.organization_id,
                msub.secondary_inventory_name,
                ccga.material_account
         from   mtl_secondary_inventories msub,
                cst_cost_group_accounts ccga,
                mtl_parameters mp
         where  msub.organization_id = mp.organization_id
         and    mp.cost_group_accounting        = 1 -- Yes
         and    msub.default_cost_group_id is null
         and    ccga.cost_group_id              = mp.default_cost_group_id
        ) msub,
        gl_code_combinations gcc,
        hr_organization_information hoi,
        hr_all_organization_units_vl haou, -- inv_organization_id
        hr_all_organization_units_vl haou2, -- operating unit
        gl_ledgers gl,
        -- Revsion for version 1.12
        fnd_common_lookups fcl
where   oap.acct_period_id              = cpcs.acct_period_id
and     oap.organization_id             = mp.organization_id
and     cpcs.subinventory_code          = msub.secondary_inventory_name
and     cpcs.organization_id            = msub.organization_id
and     mp.organization_id              = cpcs.organization_id
and     msiv.organization_id            = cpcs.organization_id
and     msiv.inventory_item_id          = cpcs.inventory_item_id
-- Revision for version 1.18
and     misv.inventory_item_status_code = msiv.inventory_item_status_code
and     msiv.primary_uom_code           = muomv.uom_code
and     ml.lookup_type                  = 'CST_ACCOUNTING_LINE_TYPE'
and     ml.lookup_code                  = 14 -- Intransit Inventory
and     msub.material_account           = gcc.code_combination_id (+)
-- End revision for version 1.18
-- ===================================================================
-- using the base tables to avoid the performance issues
-- with org_organization_definitions and hr_operating_units
-- ===================================================================
-- avoid selecting disabled inventory organizations
and     sysdate < nvl(haou.date_to, sysdate + 1)
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
and     gl.ledger_id                    = to_number(hoi.org_information1) -- get the ledger_id
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and     1=1                             -- p_period_name, p_item_number, p_org_code, p_operating_unit, p_ledger
-- ===================================================================
-- Lookup Values, added for revision 1.12
-- ===================================================================
and     fcl.lookup_code (+)             = msiv.item_type
and     fcl.lookup_type (+)             = 'ITEM_TYPE'
group by
        nvl(gl.short_name, gl.name),
        haou2.name,
        mp.organization_code,
        -- Revision for version 1.14
        -- cpcs.cost_group_id,
        oap.period_name,
        &segment_columns2
        msiv.concatenated_segments,
        msiv.description,
        -- Revision for version 1.12
        fcl.meaning,
        -- Revision for version 1.18
        misv.inventory_item_status_code_tl,
        nvl(cpcs.subinventory_code, ml.meaning),
        muomv.uom_code,
        -- End revision for version 1.18
        -- Needed for inline select
        msiv.inventory_item_id,
        msiv.organization_id
having abs(sum(nvl(cpcs.onhand_value_discrepancy, 0) +
               nvl(cpcs.intransit_value_discrepancy, 0))) >= :p_min_value_diff        -- p_min_value_diff
-- ==============================================
-- This second select gets the intransit values
-- ==============================================
union all
select  nvl(gl.short_name, gl.name) Ledger,
        haou2.name Operating_Unit,
        mp.organization_code Org_Code,
        -- Revision for version 1.14
        -- (select ccg.cost_group
        --  from   cst_cost_groups ccg
        --  where  ccg.cost_group_id  = cpcs.cost_group_id) Cost_Group,
        -- End revision for version 1.14
        oap.period_name Period_Name,
        &segment_columns
        msiv.concatenated_segments Item_Number,
        msiv.description Item_Description,
        -- Revision for version 1.12
        fcl.meaning Item_Type,
        -- Revision for version 1.18
        misv.inventory_item_status_code_tl Item_Status,
&category_columns
        -- End revision for version 1.12
        -- Revision for version 1.18
        nvl(cpcs.subinventory_code, ml.meaning) Subinventory_or_Intransit,
        muomv.uom_code UOM_Code,
        round(sum(nvl(cpcs.rollback_quantity, 0)),3) Quantity,
        round(sum(nvl(cpcs.rollback_value, 0)),2) Onhand_Value,
        round(sum(nvl(cpcs.accounted_value, 0)),2) Accounted_Value,
        round(sum((nvl(cpcs.onhand_value_discrepancy, 0)) + (nvl(cpcs.intransit_value_discrepancy, 0))),2) Difference
        -- End revision for version 1.18
from    cst_period_close_summary cpcs,
        org_acct_periods oap,
        mtl_parameters mp,
        mtl_system_items_vl msiv,
        -- Revision for version 1.18
        mtl_item_status_vl misv,
        mtl_units_of_measure_vl muomv,
        mfg_lookups ml,
        -- End revision for version 1.18
        -- Fix for version 1.11
        -- mtl_interorg_parameters mip,
        gl_code_combinations gcc,
        hr_organization_information hoi,
        hr_all_organization_units_vl haou,  -- inv_organization_id
        hr_all_organization_units_vl haou2,  -- operating unit
        gl_ledgers gl,
        -- Revsion for version 1.12
        fnd_common_lookups fcl
where   oap.acct_period_id              = cpcs.acct_period_id
and     oap.organization_id             = mp.organization_id
-- Fix for version 1.11
-- and mp.organization_id               = mip.to_organization_id
-- End fix for version 1.11
and     cpcs.subinventory_code is null
and     mp.organization_id              = cpcs.organization_id
and     msiv.organization_id            = cpcs.organization_id
and     msiv.inventory_item_id          = cpcs.inventory_item_id
-- Revision for version 1.18
and     misv.inventory_item_status_code = msiv.inventory_item_status_code
and     msiv.primary_uom_code           = muomv.uom_code
and     ml.lookup_type                  = 'CST_ACCOUNTING_LINE_TYPE'
and     ml.lookup_code                  = 14 -- Intransit Inventory
-- End revision for version 1.18
-- Fix for version 1.11
-- and  mip.intransit_inv_account       = gcc.code_combination_id
and     mp.intransit_inv_account        = gcc.code_combination_id (+)
-- ===================================================================
-- using the base tables to avoid using org_organization_definitions
-- and hr_operating_units
-- ===================================================================
-- avoid selecting disabled inventory organizations
and     sysdate < nvl(haou.date_to, sysdate + 1)
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
and     gl.ledger_id                    = to_number(hoi.org_information1) -- get the ledger_id
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and     1=1                             -- p_period_name, p_item_number, p_org_code, p_operating_unit, p_ledger
-- ===================================================================
-- Lookup Values, added for revision 1.12
-- ===================================================================
and     fcl.lookup_code (+)             = msiv.item_type
and     fcl.lookup_type (+)             = 'ITEM_TYPE'
group by
        nvl(gl.short_name, gl.name),
        haou2.name,
        mp.organization_code,
        -- Revision for version 1.14
        -- cpcs.cost_group_id,
        oap.period_name,
        &segment_columns2
        msiv.concatenated_segments,
        msiv.description,
        -- Revision for version 1.12
        fcl.meaning,
        misv.inventory_item_status_code_tl,
        -- End for version 1.12
        -- Revision for version 1.18
        nvl(cpcs.subinventory_code, ml.meaning),
        muomv.uom_code,
        -- End revision for version 1.18
        -- Needed for inline select
        msiv.inventory_item_id,
        msiv.organization_id
having abs(sum(nvl(cpcs.onhand_value_discrepancy, 0) +
               nvl(cpcs.intransit_value_discrepancy, 0))) >= :p_min_value_diff        -- p_min_value_diff
-- Order by Ledger, Operating_Unit, Accounts, Item and Subinventory
order by 1,2,5,6,7,8,9,10,11,12,15
Parameter NameSQL textValidation
Period Name (Closed)
oap.period_name = :p_period_name
LOV
Minimum Value Difference
 
Number
Category Set 1
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
LOV
Category Set 2
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
LOV
Category Set 3
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
LOV
Item Number
msiv.concatenated_segments = :p_item_number
LOV
Organization Code
mp.organization_code = :p_org_code
LOV
Operating Unit
haou2.name = :p_operating_unit
LOV
Ledger
gl.name = :p_ledger
LOV
Download
Related Blitz Reports
CAC Inventory and Intransit Value (Period-End) - Discrete/OPM CAC Inventory Pending Cost Adjustment - No Currencies CAC Inventory Pending Cost Adjustment CAC ICP PII Inventory Pending Cost Adjustment CAC Inventory and Intransit Value (Period-End) CAC ICP PII Inventory and Intransit Value (Period-End) CAC Inventory Lot and Locator OPM Value (Period-End) CAC Manufacturing Variance CAC Cost Type Costs Not in Period Close Inventory Snapshot
Blitz Report™