CAC Inventory and Intransit Value (Period-End)
Description
Categories: Enginatics, Toolkit - Cost Accounting
Repository: Github Columns: Ledger, Operating Unit, Org Code, Period Name, Company, Account, Cost Centre, Product, Item Number, Item Description ...
Repository: Github Columns: Ledger, Operating Unit, Org Code, Period Name, Company, Account, Cost Centre, Product, Item Number, Item Description ...
Report showing amount of inventory at the end of the month. If you enter a cost type this report uses the item costs from the cost type; if you leave the cost type blank it uses the item costs from the month-end snapshot. In either case this report uses the month-end quantities, based on the entered period name.
Note: if you enter a cost type this report uses the item costs from the cost ... more
Note: if you enter a cost type this report uses the item costs from the cost ... more
select nvl(gl.short_name, gl.name) Ledger, -- ======================================================================= -- The first select gets the period-end quantities from the subinventories -- ======================================================================= haou2.name Operating_Unit, mp.organization_code Org_Code, oap.period_name Period_Name, &segment_columns msiv.concatenated_segments Item_Number, msiv.description Item_Description, -- Revision for version 1.13 fcl.meaning Item_Type, -- Revision for version 1.14 and 1.16 misv.inventory_item_status_code_tl Item_Status, &category_columns gl.currency_code Curr_Code, round((nvl(cpcs.rollback_value,0)) / decode(nvl(cpcs.rollback_quantity,0), 0, 1, nvl(cpcs.rollback_quantity,0)),5) Item_Cost, cpcs.subinventory_code Subinventory_or_Intransit, -- Revision for version 1.16 muomv.uom_code UOM_Code, round(sum(nvl(cpcs.rollback_quantity,0)),3) Onhand_Quantity, round(sum(nvl(cpcs.rollback_value,0)),2) Onhand_Value from cst_period_close_summary cpcs, org_acct_periods oap, mtl_parameters mp, -- Revision for version 1.16 mtl_system_items_vl msiv, mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, -- End Revision for version 1.16 mtl_secondary_inventories msub, gl_code_combinations gcc, -- subinventory accounts hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, fnd_common_lookups fcl -- =========================================== -- Inventory accounting period joins -- =========================================== where oap.acct_period_id = cpcs.acct_period_id and oap.organization_id = mp.organization_id -- ======================================================================== -- Subinventory, mtl parameter, item master and period close snapshot joins -- ======================================================================== and msub.secondary_inventory_name = cpcs.subinventory_code and msub.organization_id = cpcs.organization_id and mp.organization_id = cpcs.organization_id -- Revision for version 1.16 and mp.organization_id = msiv.organization_id and msiv.organization_id = cpcs.organization_id and msiv.inventory_item_id = cpcs.inventory_item_id and msiv.primary_uom_code = muomv.uom_code and msiv.inventory_item_status_code = misv.inventory_item_status_code -- End for revision for version 1.16 -- =========================================== -- Accounting code combination joins -- =========================================== and msub.material_account = gcc.code_combination_id (+) -- =========================================== -- Organization joins to the HR org model -- =========================================== 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 1=1 -- p_period_name, p_item_number, p_org_code, p_operating_unit, p_ledger -- =========================================== -- For Item_Type -- =========================================== and fcl.lookup_code (+) = msiv.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' -- End revision for version 1.13 -- =========================================== -- limit the rows returned-don't get zero rows -- =========================================== and nvl(cpcs.rollback_quantity,0) <> 0 -- Don't report expense subinventories or expense items and msub.asset_inventory <> 2 and msiv.inventory_asset_flag = 'Y' -- =========================================== -- Revision for version 1.12 -- Run this query if the Cost Type parameter -- is null, to get the snapshot inventory value. -- =========================================== and decode('&p_cost_type', -- p_cost_type null, 'use snapshot values', 'do not use snapshot values') = 'use snapshot values' group by nvl(gl.short_name, gl.name), haou2.name, mp.organization_code, oap.period_name, &segment_columns_grp msiv.concatenated_segments, msiv.description, -- Revision for version 1.13 fcl.meaning, -- Revision for version 1.14 and 1.16 misv.inventory_item_status_code_tl, gl.currency_code, round((nvl(cpcs.rollback_value,0)) / decode(nvl(cpcs.rollback_quantity,0), 0, 1, nvl(cpcs.rollback_quantity,0)),5), msiv.inventory_item_id, msiv.organization_id, cpcs.subinventory_code, -- Revision for version 1.16 muomv.uom_code union all -- ======================================================================= -- The second select gets the period-end quantities from intransit -- ======================================================================= select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, oap.period_name Period_Name, &segment_columns msiv.concatenated_segments Item_Number, msiv.description Item_Description, -- Revision for version 1.13 fcl.meaning Item_Type, -- Revision for version 1.14 and 1.16 misv.inventory_item_status_code_tl Item_Status, &category_columns gl.currency_code Curr_Code, round((nvl(cpcs.rollback_intransit_value,0)) / decode(nvl(cpcs.rollback_quantity,0), 0, 1, nvl(cpcs.rollback_quantity,0)),5) Item_Cost, ml.meaning Subinventory_or_Intransit, muomv.uom_code UOM_Code, round(sum(nvl(cpcs.rollback_quantity,0)),3) Quantity, round(sum(nvl(cpcs.rollback_intransit_value,0)),2) Onhand_Value -- p_pii_cost_type from cst_period_close_summary cpcs, org_acct_periods oap, mtl_parameters mp, -- Revision for version 1.16 mtl_system_items_vl msiv, mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, mfg_lookups ml, -- End Revision for version 1.16 gl_code_combinations gcc, -- subinventory accounts hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, fnd_common_lookups fcl, -- Revision for version 1.14 -- Revision for version 1.10 -- If the Inventory Parameters is missing the Intransit Account -- Need to get the Intransit Account from the Shipping Network (select ic.code_combination_id, ic.organization_id from (select gcc.code_combination_id, mip.to_organization_id organization_id from mtl_interorg_parameters mip, mtl_parameters mp, gl_code_combinations gcc where mip.fob_point = 1 -- shipment and mp.organization_id = mip.to_organization_id and mp.intransit_inv_account is null and gcc.code_combination_id (+) = mip.intransit_inv_account union all select gcc.code_combination_id, mip.from_organization_id organization_id from mtl_interorg_parameters mip, mtl_parameters mp, gl_code_combinations gcc where mip.fob_point = 2 -- receipt and mp.organization_id = mip.from_organization_id and mp.intransit_inv_account is null and gcc.code_combination_id (+) = mip.intransit_inv_account union all select gcc.code_combination_id, mp.organization_id organization_id from mtl_parameters mp, gl_code_combinations gcc where mp.intransit_inv_account is not null and gcc.code_combination_id (+) = mp.intransit_inv_account ) ic group by ic.code_combination_id, ic.organization_id ) interco -- End revision for version 1.0 -- =========================================== -- Inventory accounting period joins -- =========================================== where oap.acct_period_id = cpcs.acct_period_id and oap.organization_id = mp.organization_id -- ======================================================================== -- Subinventory, mtl parameter, item master and period close snapshot joins -- ======================================================================== and cpcs.subinventory_code is null and mp.organization_id = cpcs.organization_id -- Revision for version 1.16 and mp.organization_id = msiv.organization_id and msiv.organization_id = cpcs.organization_id and msiv.inventory_item_id = cpcs.inventory_item_id and msiv.primary_uom_code = muomv.uom_code and msiv.inventory_item_status_code = misv.inventory_item_status_code -- End for revision for version 1.16 -- =========================================== -- Accounting code combination joins -- =========================================== -- Revision for version 1.9 -- and mp.intransit_inv_account = gcc.code_combination_id -- Revision for version 1.14 -- -- Use the material account as a default for segments 1,3,4,5,6 -- -- and mp.material_account = gcc.code_combination_id -- Revision for version 1.14 and interco.code_combination_id = gcc.code_combination_id (+) and interco.organization_id = mp.organization_id -- =========================================== -- Organization joins to the HR org model -- =========================================== 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 1=1 -- p_period_name, p_item_number, p_org_code, p_operating_unit, p_ledger -- =========================================== -- For Item_Type -- Revision for version 1.13 -- =========================================== and fcl.lookup_code (+) = msiv.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' -- Revision for version 1.16 and ml.lookup_code = 3 and ml.lookup_type = 'MSC_CALENDAR_TYPE' -- =========================================== -- limit the rows returned-don't get zero rows -- =========================================== and nvl(cpcs.rollback_quantity,0) <> 0 -- =========================================== -- Revision for version 1.12 -- Run this query if the Cost Type parameter -- is null, to get the snapshot inventory value. -- =========================================== and decode('&p_cost_type', -- p_cost_type null, 'use snapshot values', 'do not use snapshot values') = 'use snapshot values' group by nvl(gl.short_name, gl.name), haou2.name, mp.organization_code, oap.period_name, &segment_columns_grp msiv.concatenated_segments, msiv.description, -- Revision for version 1.13 fcl.meaning, -- Revision for version 1.14 and 1.16 misv.inventory_item_status_code_tl, gl.currency_code, round(nvl(cpcs.rollback_intransit_value,0) / decode(nvl(cpcs.rollback_quantity,0), 0, 1, nvl(cpcs.rollback_quantity,0)),5), msiv.inventory_item_id, msiv.organization_id, -- Revision for version 1.16 ml.meaning, muomv.uom_code union all -- ======================================================================= -- Revision for version 1.12 -- Get the inventory values from the entered Cost Type -- but use the quantities from the month-end snapshot -- ======================================================================= -- ======================================================================= -- The first select gets the period-end quantities from the subinventories -- ======================================================================= select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, oap.period_name Period_Name, &segment_columns msiv.concatenated_segments Item_Number, msiv.description Item_Description, fcl.meaning Item_Type, -- Revision for version 1.14 and 1.16 misv.inventory_item_status_code_tl Item_Status, &category_columns gl.currency_code Curr_Code, round(nvl(cic.item_cost,0),5) Item_Cost, cpcs.subinventory_code Subinventory_or_Intransit, -- Revision for version 1.16 muomv.uom_code UOM_Code, round(sum(nvl(cpcs.rollback_quantity,0)),3) Onhand_Quantity, -- Use the Cost Type Costs instead of the rollback_value round(sum(nvl(cpcs.rollback_quantity,0) * nvl(cic.item_cost,0)),2) Onhand_Value from cst_period_close_summary cpcs, org_acct_periods oap, mtl_parameters mp, -- Revision for version 1.16 mtl_system_items_vl msiv, mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, -- End Revision for version 1.16 mtl_secondary_inventories msub, -- Revision for version 1.12 cst_cost_types cct, cst_item_costs cic, -- End revision for version 1.12 gl_code_combinations gcc, -- subinventory accounts hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, -- Revision for version 1.13 fnd_common_lookups fcl -- =========================================== -- Inventory accounting period joins -- =========================================== where oap.acct_period_id = cpcs.acct_period_id and oap.organization_id = mp.organization_id -- ======================================================================== -- Subinventory, mtl parameter, item master and period close snapshot joins -- ======================================================================== and msub.secondary_inventory_name = cpcs.subinventory_code and msub.organization_id = cpcs.organization_id and mp.organization_id = cpcs.organization_id -- Revision for version 1.16 and mp.organization_id = msiv.organization_id and msiv.organization_id = cpcs.organization_id and msiv.inventory_item_id = cpcs.inventory_item_id and msiv.primary_uom_code = muomv.uom_code and msiv.inventory_item_status_code = misv.inventory_item_status_code -- End for revision for version 1.16 -- =========================================== -- Accounting code combination joins -- =========================================== and msub.material_account = gcc.code_combination_id (+) -- =========================================== -- Cost Type Joins -- Revision for version 1.12 -- =========================================== and 6=6 -- p_cost_type and cct.cost_type_id = cic.cost_type_id and cic.organization_id = msiv.organization_id and cic.inventory_item_id = msiv.inventory_item_id -- =========================================== -- Organization joins to the HR org model -- =========================================== 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 1=1 -- p_period_name, p_item_number, p_org_code, p_operating_unit, p_ledger -- =========================================== -- For Item_Type -- =========================================== and fcl.lookup_code (+) = msiv.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' -- End revision for version 1.13 -- =========================================== -- limit the rows returned-don't get zero rows -- =========================================== and nvl(cpcs.rollback_quantity,0) <> 0 -- Don't report expense subinventories or expense items and msub.asset_inventory <> 2 and msiv.inventory_asset_flag = 'Y' -- =========================================== -- Revision for version 1.12 -- Run this query if the Cost Type parameter -- is not null, use the Cost Type Costs -- to get the reported inventory value. -- =========================================== and decode('&p_cost_type', null, 'do not use snapshot values', 'use cost type values') = 'use cost type values' group by nvl(gl.short_name, gl.name), haou2.name, mp.organization_code, oap.period_name, &segment_columns_grp msiv.concatenated_segments, msiv.description, fcl.meaning, -- Revision for version 1.14 and 1.16 misv.inventory_item_status_code_tl, gl.currency_code, round((nvl(cpcs.rollback_value,0)) / decode(nvl(cpcs.rollback_quantity,0), 0, 1, nvl(cpcs.rollback_quantity,0)),5), msiv.inventory_item_id, msiv.organization_id, cpcs.subinventory_code, -- Revision for version 1.16 muomv.uom_code, -- Revision for version 1.12 -- Added for valuing based on the item costs cic.item_cost union all -- ======================================================================= -- The second select gets the period-end quantities from intransit -- and the item costs from the entered cost type. -- ======================================================================= select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, oap.period_name Period_Name, &segment_columns msiv.concatenated_segments Item_Number, msiv.description Item_Description, -- Revision for version 1.13 fcl.meaning Item_Type, -- Revision for version 1.14 and 1.16 misv.inventory_item_status_code_tl Item_Status, &category_columns gl.currency_code Curr_Code, round(nvl(cic.item_cost,0),5) Item_Cost, -- Revision for version 1.16 ml.meaning Subinventory_or_Intransit, muomv.uom_code UOM_Code, round(sum(nvl(cpcs.rollback_quantity,0)),3) Quantity, -- Use the item costs from the Cost Type -- to value the Intransit inventory -- sum(nvl(cpcs.rollback_intransit_value,0)) Onhand_Value, round(sum(nvl(cic.item_cost,0) * nvl(cpcs.rollback_quantity,0)),2) Onhand_Value from cst_period_close_summary cpcs, org_acct_periods oap, mtl_parameters mp, -- Revision for version 1.16 mtl_system_items_vl msiv, mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, mfg_lookups ml, -- End Revision for version 1.16 -- Revision for version 1.12 cst_cost_types cct, cst_item_costs cic, -- End revision for version 1.12 gl_code_combinations gcc, -- subinventory accounts hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, fnd_common_lookups fcl, -- Revision for version 1.10 -- Need to get the Intransit Account from the Shipping Network -- as the inventory parameters Intransit Account is not always populated (select ic.code_combination_id, ic.organization_id from (select gcc.code_combination_id, mip.to_organization_id organization_id from mtl_interorg_parameters mip, mtl_parameters mp, gl_code_combinations gcc where mip.fob_point = 1 -- shipment and mp.organization_id = mip.to_organization_id and gcc.code_combination_id (+) = mip.intransit_inv_account union all select gcc.code_combination_id, mip.from_organization_id organization_id from mtl_interorg_parameters mip, mtl_parameters mp, gl_code_combinations gcc where mip.fob_point = 2 -- receipt and mp.organization_id = mip.from_organization_id and gcc.code_combination_id (+) = mip.intransit_inv_account ) ic group by ic.code_combination_id, ic.organization_id ) interco -- End revision for version 1.0 -- =========================================== -- Inventory accounting period joins -- =========================================== where oap.acct_period_id = cpcs.acct_period_id and oap.organization_id = mp.organization_id -- ======================================================================== -- Subinventory, mtl parameter, item master and period close snapshot joins -- ======================================================================== and cpcs.subinventory_code is null and mp.organization_id = cpcs.organization_id -- Revision for version 1.16 and mp.organization_id = msiv.organization_id and msiv.organization_id = cpcs.organization_id and msiv.inventory_item_id = cpcs.inventory_item_id and msiv.primary_uom_code = muomv.uom_code and msiv.inventory_item_status_code = misv.inventory_item_status_code -- End for revision for version 1.16 -- =========================================== -- Accounting code combination joins -- =========================================== -- Revision for version 1.9 -- and mp.intransit_inv_account = gcc.code_combination_id --- Revision for version 1.14 -- -- Use the material account as a default for segments 1,3,4,5,6 -- and mp.material_account = gcc.code_combination_id -- Revision for version 1.14 and interco.code_combination_id = gcc.code_combination_id (+) and interco.organization_id = mp.organization_id -- =========================================== -- Cost Type Joins -- Revision for version 1.12 -- =========================================== and 6=6 -- p_cost_type and cct.cost_type_id = cic.cost_type_id and cic.organization_id = msiv.organization_id and cic.inventory_item_id = msiv.inventory_item_id -- =========================================== -- Organization joins to the HR org model -- =========================================== 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 1=1 -- p_period_name, p_item_number, p_org_code, p_operating_unit, p_ledger -- =========================================== -- For Item_Type -- =========================================== and fcl.lookup_code (+) = msiv.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' -- Revision for version 1.16 and ml.lookup_code = 3 and ml.lookup_type = 'MSC_CALENDAR_TYPE' -- =========================================== -- limit the rows returned-don't get zero rows -- =========================================== and nvl(cpcs.rollback_quantity,0) <> 0 -- =========================================== -- Revision for version 1.12 -- Run this query if the Cost Type parameter -- is not null, use the Cost Type Costs -- to get the reported inventory value. -- =========================================== and decode('&p_cost_type', null, 'do not use snapshot values', 'use cost type values') = 'use cost type values' group by nvl(gl.short_name, gl.name), haou2.name, mp.organization_code, oap.period_name, &segment_columns_grp msiv.concatenated_segments, msiv.description, fcl.meaning, -- Revision for version 1.14 and 1.16 misv.inventory_item_status_code_tl, gl.currency_code, round(nvl(cpcs.rollback_intransit_value,0) / decode(nvl(cpcs.rollback_quantity,0), 0, 1, nvl(cpcs.rollback_quantity,0)),5), msiv.inventory_item_id, msiv.organization_id, -- Revision for version 1.16 ml.meaning, muomv.uom_code, -- Revision for version 1.12 -- Added for valuing based on the item costs cic.item_cost -- Order by Ledger, Operating_Unit, Org_Code, Accounts, Item_Number, Subinventory order by 1,2,3,5,6,7,8,9,10,11,12,15 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Chart of Accounts |
| LOV | |
Ledger |
| LOV | |
Category Set 1 |
| LOV | |
Category Set 2 |
| LOV | |
Cost Type |
| LOV | |
Item Number |
| LOV | |
Period Name (Closed) |
| LOV | |
Operating Unit |
| LOV | |
Organization Code |
| LOV | |
Chart of Accounts |
| ||
Cost Type |
|