CAC ICP PII 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 profit in 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: Profit in inventory is abbreviated as PII or sometimes as ICP - ...
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) Gross_Item_Cost, -- Revision for version 1.1 round(nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),5) PII_Item_Cost, -- p_pii_cost_type round((nvl(cpcs.rollback_value,0)) / decode(nvl(cpcs.rollback_quantity,0), 0, 1, -- Revision for version 1.13, PII is a negative value nvl(cpcs.rollback_quantity,0)),5) + -- nvl(cpcs.rollback_quantity,0)),5) - -- Revision for version 1.1 round(nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),5) Net_Item_Cost, -- p_pii_cost_type 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, round(sum(nvl(cpcs.rollback_quantity,0)) * -- Revision for version 1.1 nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),2) PII_Onhand_Value, -- p_pii_cost_type round(sum(nvl(cpcs.rollback_value,0)),2) + -- Revision for version 1.1 -- Revision for version 1.13, PII already a negative value round(sum(nvl(cpcs.rollback_quantity,0)) * nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),2) Net_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, -- 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) Gross_Item_Cost, -- Revision for version 1.1 round(nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),5) PII_Item_Cost, -- p_pii_cost_type round((nvl(cpcs.rollback_intransit_value,0)) / decode(nvl(cpcs.rollback_quantity,0), 0, 1, -- Revision for version 1.13, PII is a negative value nvl(cpcs.rollback_quantity,0)),5) - -- Revision for version 1.1 round(nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),5) Net_Item_Cost, -- p_pii_cost_type -- Revision for version 1.16 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, round(sum(nvl(cpcs.rollback_quantity,0)) * -- Revision for version 1.1 nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),2) PII_Onhand_Value, -- p_pii_cost_type round(sum(nvl(cpcs.rollback_intransit_value,0)),2) + -- Revision for version 1.1 -- Revision for version 1.13, PII is a negative value round(sum(nvl(cpcs.rollback_quantity,0)) * nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),2) Net_Onhand_Value -- p_pii_cost_type -- 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) Gross_Item_Cost, -- Revision for version 1.1 round(nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),5) PII_Item_Cost, -- p_pii_cost_type -- Revision for version 1.13, PII is negative round(nvl(cic.item_cost,0),5) + -- Revision for version 1.1 round(nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),5) Net_Item_Cost, -- p_pii_cost_type 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 -- sum(nvl(cpcs.rollback_value,0)) Onhand_Value, round(sum(nvl(cpcs.rollback_quantity,0) * nvl(cic.item_cost,0)),2) Onhand_Value, round(sum(nvl(cpcs.rollback_quantity,0)) * -- Revision for version 1.1 nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),2) PII_Onhand_Value, -- p_pii_cost_type -- Use the Cost Type Costs instead of the rollback_value -- sum(nvl(cpcs.rollback_value,0)) + round(sum(nvl(cpcs.rollback_quantity,0) * nvl(cic.item_cost,0)),2) + -- Revision for version 1.1 -- Revision for version 1.13, PII is negative round(sum(nvl(cpcs.rollback_quantity,0)) * nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),2) Net_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, -- 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 -- Revision for version 1.9 -- fnd_lookup_values flv 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, -- Get the item costs from the item costs table -- round((nvl(cpcs.rollback_intransit_value,0)) / -- decode(nvl(cpcs.rollback_quantity,0), 0, 1, -- nvl(cpcs.rollback_quantity,0)),5) Gross_Item_Cost, round(nvl(cic.item_cost,0),5) Gross_Item_Cost, -- Revision for version 1.1 round(nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),5) PII_Item_Cost, -- p_pii_cost_type -- Get the item costs from the item costs table -- round((nvl(cpcs.rollback_intransit_value,0)) / -- decode(nvl(cpcs.rollback_quantity,0), 0, 1, -- Revision for version 1.13 -- Using a negative PII amount round(nvl(cic.item_cost,0),5) + -- Revision for version 1.1 round(nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),5) Net_Item_Cost, -- p_pii_cost_type -- 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, round(sum(nvl(cpcs.rollback_quantity,0)) * -- Revision for version 1.1 nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),2) PII_Onhand_Value, -- p_pii_cost_type -- Use the item costs from the Cost Type -- to value the Intransit inventory -- sum(nvl(cpcs.rollback_intransit_value,0)) + round(sum(nvl(cic.item_cost,0) * nvl(cpcs.rollback_quantity,0)),2) + -- Revision for version 1.1 -- Revision for version 1.13 -- PII item costs are negative round(sum(nvl(cpcs.rollback_quantity,0)) * nvl((select sum(nvl(cicd.item_cost,0)) from bom.cst_item_cost_details cicd, bom.cst_cost_types cct, bom.bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and 5=5 -- p_pii_resource_code and cct.cost_type_id = cicd.cost_type_id and 4=4),0),2) Net_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 -- 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 |