CAC ICP PII Material Account Summary
Description
Categories: Enginatics
Repository: Github
Repository: Github
Use this report to eliminate your internal profit in inventory (PII) at month-end. This report to sums up the material accounting entries for each item, organization, subinventory with the original amount, profit in inventory and net amounts. With the Show SLA Accounting parameter you can choose to use the Release 12 Subledger Accounting (Create Accounting) account setups by selecting Yes. And ...
more
Run
CAC ICP PII Material Account Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
with pii as -- =========================================================== -- Revision 1.29, Inline select for Profit in Inventory costs -- =========================================================== (select sum(nvl(cicd.item_cost, 0)) pii_item_cost, sum(nvl(cicd.item_cost,0)) * decode(sign('&p_sign_pii'),1,1,-1,-1,1) corrected_pii_item_cost, cicd.inventory_item_id, cicd.organization_id, cct.cost_type pii_cost_type from cst_item_cost_details cicd, bom_resources br, cst_cost_types cct, mtl_parameters mp where cicd.resource_id = br.resource_id and cicd.cost_type_id = cct.cost_type_id and mp.organization_id = cicd.organization_id and 3=3 -- p_org_code and 5=5 -- p_pii_cost_type, p_pii_sub_element &only_no_pii group by cicd.inventory_item_id, cicd.organization_id, cct.cost_type ) select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, acct_dist.organization_code Org_Code, -- Revision for version 1.8 haou2_from.name From_OU, haou2_to.name To_OU, nvl((select ml2.meaning from mfg_lookups ml2 where ml2.lookup_type = 'MTL_FOB_POINT' and ml2.lookup_code = acct_dist.fob_point),'') FOB_Point, acct_dist.ship_from_org Ship_From_Org, acct_dist.ship_to_org Ship_To_Org, -- Revision for version 1.25 -- ah.period_name Period_Name, oap.period_name Period_Name, -- End revision for version 1.25 &segment_columns msiv.concatenated_segments Item_Number, msiv.description Item_Description, -- Revision for version 1.6 and 1.19, add in item type fcl.meaning Item_Type, -- Revision for version 1.22 misv.inventory_item_status_code Item_Status, -- Revision for version 1.23 ml2.meaning Make_Buy_Code, -- Revision for version 1.12 and 1.24 &category_columns ml1.meaning Accounting_Line_Type, mtt.transaction_type_name Transaction_Type, -- Revision for version 1.13 mtst.transaction_source_type_name Transaction_Source, -- Revision for version 1.25 &p_show_subinventory &p_show_project &p_show_wip_job -- End revision for version 1.25 muomv.uom_code UOM_Code, sum(acct_dist.primary_quantity) Quantity, gl.currency_code Currency_Code, sum(acct_dist.Matl_Amount) Material_Amount, sum(acct_dist.Matl_Ovhd_Amount) Material_Overhead_Amount, sum(acct_dist.Resource_Amount) Resource_Amount, sum(acct_dist.OSP_Amount) Outside_Processing_Amount, sum(acct_dist.Overhead_Amount) Overhead_Amount, sum(acct_dist.mta_amount) Amount -- Revision for version 1.26 &p_show_pii from mtl_system_items_vl msiv, org_acct_periods oap, mtl_transaction_types mtt, -- Revision for version 1.21 mtl_units_of_measure_vl muomv, -- Revision for version 1.22 mtl_item_status_vl misv, -- Revision for version 1.13 mtl_txn_source_types mtst, -- Revision for version 1.29 pii, 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 hr_organization_information hoi_from, hr_all_organization_units_vl haou_from, -- inv_organization_id hr_all_organization_units_vl haou2_from, -- operating unit hr_organization_information hoi_to, hr_all_organization_units_vl haou_to, -- inv_organization_id hr_all_organization_units_vl haou2_to, -- operating unit gl_ledgers gl, -- Revision for version 1.19 fnd_common_lookups fcl, -- Item Type mfg_lookups ml1, -- Accounting Line Type mfg_lookups ml2, -- Planning Make Buy Code -- Revision for version 1.25 &subinventory_table &project_tables -- =========================================================== -- Revision 1.28, sum up the accounting transactions as an -- intermediary step. Needed for accurate quantities. -- =========================================================== (select acct_dist2.organization_code, acct_dist2.ship_from_org, acct_dist2.ship_to_org, acct_dist2.organization_id, acct_dist2.ship_from_org_id, acct_dist2.ship_to_org_id, acct_dist2.fob_point, acct_dist2.acct_period_id, &p_show_non_sla &p_show_sla acct_dist2.inventory_item_id, acct_dist2.transaction_source_type_id, acct_dist2.transaction_type_id, acct_dist2.accounting_line_type, &p_show_subinventory2 &p_show_project2 &p_show_wip_job2 sum(acct_dist2.primary_quantity) primary_quantity, sum(acct_dist2.Matl_Amount) Matl_Amount, sum(acct_dist2.Matl_Ovhd_Amount) Matl_Ovhd_Amount, sum(acct_dist2.Resource_Amount) Resource_Amount, sum(acct_dist2.OSP_Amount) OSP_Amount, sum(acct_dist2.Overhead_Amount) Overhead_Amount, sum(acct_dist2.mta_amount) mta_amount -- Revision for version 1.26 &p_show_pii2 from &mtl_sla_tables -- Revision for version 1.26 &pii_lookup_table -- =========================================================== -- Inline select for finding components with PII not issued to WIP -- =========================================================== -- get the corrected wip component issue status (select wro.organization_id, wro.wip_entity_id, wro.primary_item_id, 'Y' wip_pii_component_quantity, 5 transaction_source_type_id from (select wrosum.wip_entity_id, wrosum.organization_id, wrosum.primary_item_id, wrosum.inventory_item_id, sum(wrosum.quantity_issued) quantity_issued from (select wro.wip_entity_id, wro.organization_id, wdj.primary_item_id, wro.inventory_item_id, wro.quantity_issued from wip_requirement_operations wro, wip_discrete_jobs wdj, -- Revision for version 1.29 -- Only select components with PII -- cst_item_cost_details cicd, -- cst_cost_types cct, -- bom_resources br, pii cicd, -- End revision for version 1.29 mtl_parameters mp where wdj.wip_entity_id = wro.wip_entity_id and wdj.organization_id = wro.organization_id and wdj.organization_id = mp.organization_id -- Do not select phantom (6) or bulk (4) WIP supply types, typically not issued to WIP and wro.wip_supply_type not in (4,6) -- find jobs to highlight that were open during the report period or closed after the transaction to date and (wdj.date_closed is null -- the job is open and wdj.creation_date < :p_trx_date_to + 1 or -- the job is closed and ...the job was closed after the transaction from date wdj.date_closed is not null and wdj.date_closed >= :p_trx_date_to + 1 ) -- Only select components with PII and cicd.inventory_item_id = wro.inventory_item_id and cicd.organization_id = wro.organization_id -- Revision for version 1.29 -- and cicd.cost_type_id = cct.cost_type_id -- and cicd.resource_id = br.resource_id -- and cicd.item_cost <> 0 and cicd.pii_item_cost <> 0 -- End revision for version 1.29 and 3=3 -- p_org_code &only_no_pii union all select mmt.transaction_source_id, wro.organization_id, wdj.primary_item_id, mmt.inventory_item_id, decode(mmt.transaction_type_id, 35, mmt.primary_quantity, -- wip component issue 43, -1 * mmt.primary_quantity -- wip component return ) quantity_issued from mtl_material_transactions mmt, wip_discrete_jobs wdj, wip_requirement_operations wro, -- Revision for version 1.29 -- Only select components with PII -- cst_item_cost_details cicd, -- cst_cost_types cct, -- bom_resources br, pii cicd, -- End revision for version 1.29 mtl_parameters mp where mmt.transaction_source_type_id = 5 and mmt.transaction_source_id = wdj.wip_entity_id and mmt.organization_id = wdj.organization_id and mp.organization_id = wdj.organization_id -- Revision for version 1.29, join to wdj, not mmt, for performance and wro.wip_entity_id = wdj.wip_entity_id and wro.organization_id = wdj.organization_id -- End revision for version 1.29 and mmt.operation_seq_num = wro.operation_seq_num and mmt.transaction_date >= :p_trx_date_to + 1 -- Do not select phantom (6) or bulk (4) WIP supply types, typically not issued to WIP and wro.wip_supply_type not in (4,6) -- find jobs to highlight that were open during the report period or closed after the transaction to date and (wdj.date_closed is null -- the job is open and wdj.creation_date < :p_trx_date_to + 1 or -- the job is closed and ...the job was closed after the transaction from date wdj.date_closed is not null and wdj.date_closed >= :p_trx_date_to + 1 ) -- Only select components with PII and cicd.inventory_item_id = wro.inventory_item_id and cicd.organization_id = wro.organization_id -- Revision for version 1.29 and cicd.inventory_item_id = mmt.inventory_item_id and cicd.organization_id = mmt.organization_id -- and cicd.cost_type_id = cct.cost_type_id -- and cicd.resource_id = br.resource_id -- and cicd.item_cost <> 0 and cicd.pii_item_cost <> 0 -- End revision for version 1.29 and 3=3 -- p_org_code &only_no_pii ) wrosum group by wrosum.wip_entity_id, wrosum.organization_id, wrosum.primary_item_id, wrosum.inventory_item_id) wro -- Only select components with no issues to WIP where nvl(wro.quantity_issued,0) = 0 &only_no_pii group by wro.organization_id, wro.wip_entity_id, wro.primary_item_id, 'Y', -- wip_pii_component_quantity 5 --transaction_source_type_id ) wro_pii, -- End revision for version 1.26 -- ========================================================================== -- Use this inline table to fetch the material transactions -- ========================================================================== -- First join to non-WIP entries (select mp.organization_code, -- Fix for version 1.6 -- Revision for version 1.5 decode(mmt.transaction_action_id, 3, mp_mmt_org.organization_code, -- Direct Org Transfer, txn_id 3 9, mp_mmt_org.organization_code, -- Logical Intercompany Sales Issue, txn_id 11 10, mp_xfer_org.organization_code, -- Logical Intercompany Shipment Receipt, txn_id 10 12, mp_xfer_org.organization_code, -- Receive from intransit, Int Req Intr Rcpt, txn_id 12,61 13, mp_mmt_org.organization_code, -- Logical Intercompany Receipt Return, txn_id 13 15, mp_xfer_org.organization_code, -- Logical Intransit Receipt, txn_id 76 -- Revision for version 1.11 17, mp_xfer_org.organization_code, -- Logical Expense Requisition Receipt, txn_id 27 21, mp_mmt_org.organization_code, -- Intransit Shipment, Int Order Intr Ship, txn_id 21,62 22, mp_mmt_org.organization_code, -- Logical Intransit Shipment, tnx_id 60, 65 '') ship_from_org, decode(mmt.transaction_action_id, 3, mp_xfer_org.organization_code, -- Direct Org Transfer, txn_id 3 9, mp_xfer_org.organization_code, -- Logical Intercompany Sales Issue, txn_id 11 10, mp_mmt_org.organization_code, -- Logical Intercompany Shipment Receipt, txn_id 10 12, mp_mmt_org.organization_code, -- Receive from intransit, Int Req Intr Rcpt, txn_id 12,61 13, mp_xfer_org.organization_code, -- Logical Intercompany Receipt Return, txn_id 13 15, mp_mmt_org.organization_code, -- Logical Intransit Receipt, txn_id 76 -- Revision for version 1.11 17, mp_mmt_org.organization_code, -- Logical Expense Requisition Receipt, txn_id 27 21, mp_xfer_org.organization_code, -- Intransit Shipment, Int Order Intr Ship, txn_id 21,62 22, mp_xfer_org.organization_code, -- Logical Intransit Shipment, tnx_id 60, 65 '') ship_to_org, -- End revision for version 1.5 -- End revision for version 1.6 mp.organization_id organization_id, -- Revision for version 1.8 decode(mmt.transaction_action_id, 3, mp_mmt_org.organization_id, -- Direct Org Transfer, txn_id 3 9, mp_mmt_org.organization_id, -- Logical Intercompany Sales Issue, txn_id 11 10, mp_xfer_org.organization_id, -- Logical Intercompany Shipment Receipt, txn_id 10 12, mp_xfer_org.organization_id, -- Receive from intransit, Int Req Intr Rcpt, txn_id 12,61 13, mp_mmt_org.organization_id, -- Logical Intercompany Receipt Return, txn_id 13 15, mp_xfer_org.organization_id, -- Logical Intransit Receipt, txn_id 76 -- Revision for version 1.11 17, mp_xfer_org.organization_id, -- Logical Expense Requisition Receipt, txn_id 27 21, mp_mmt_org.organization_id, -- Intransit Shipment, Int Order Intr Ship, txn_id 21,62 22, mp_mmt_org.organization_id, -- Logical Intransit Shipment, tnx_id 60, 65 mta.organization_id) ship_from_org_id, decode(mmt.transaction_action_id, 3, mp_xfer_org.organization_id, -- Direct Org Transfer, txn_id 3 9, mp_xfer_org.organization_id, -- Logical Intercompany Sales Issue, txn_id 11 10, mp_mmt_org.organization_id, -- Logical Intercompany Shipment Receipt, txn_id 10 12, mp_mmt_org.organization_id, -- Receive from intransit, Int Req Intr Rcpt, txn_id 12,61 13, mp_xfer_org.organization_id, -- Logical Intercompany Receipt Return, txn_id 13 15, mp_mmt_org.organization_id, -- Logical Intransit Receipt, txn_id 76 -- Revision for version 1.11 17, mp_mmt_org.organization_id, -- Logical Expense Requisition Receipt, txn_id 27 21, mp_xfer_org.organization_id, -- Intransit Shipment, Int Order Intr Ship, txn_id 21,62 22, mp_xfer_org.organization_id, -- Logical Intransit Shipment, tnx_id 60, 65 mta.organization_id) ship_to_org_id, mmt.fob_point fob_point, -- End revision for version 1.8 mmt.acct_period_id acct_period_id, mta.reference_account reference_account, mta.inv_sub_ledger_id inv_sub_ledger_id, -- Revision for version 1.28 mmt.transaction_id, mmt.inventory_item_id, mmt.transaction_source_type_id, mmt.transaction_type_id, mta.accounting_line_type, -- Revision for version 1.15 decode(mta.accounting_line_type, 7, ml1.meaning, 14, ml1.meaning, 1, decode(mmt.transaction_action_id, 2, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), 3, decode(mmt.organization_id, mta.organization_id, mmt.subinventory_code, mmt.transfer_subinventory ), 21, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), 22, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), 28, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), mmt.subinventory_code -- Fix for version 1.6 -- ) ) ) subinventory_code, -- End revision for version 1.15 -- Revision for version 1.26 mmt.transaction_source_id, -- Revision for version 1.25 mmt.project_id, null wip_class, null class_type, null wip_type, null job, null fg_item, null fg_description, -- End revision for version 1.25 -- Revision for version 1.20 and 1.28, logic fix for the transaction quantity -- Use quantity adjusted for Standard Cost Updates, else use the primary qty. decode(mmt.transaction_action_id, 24, mmt.quantity_adjusted, mta.primary_quantity) primary_quantity, -- End revision for version 1.28 -- Revision for version 1.13 decode(mta.cost_element_id, 1, mta.base_transaction_value, 0) Matl_Amount, decode(mta.cost_element_id, 2, mta.base_transaction_value, 0) Matl_Ovhd_Amount, decode(mta.cost_element_id, 3, mta.base_transaction_value, 0) Resource_Amount, decode(mta.cost_element_id, 4, mta.base_transaction_value, 0) OSP_Amount, decode(mta.cost_element_id, 5, mta.base_transaction_value, 0) Overhead_Amount, -- End revision for version 1.13 mta.base_transaction_value mta_amount -- Revision for version 1.28 &p_show_pii3 from mtl_transaction_accounts mta, mtl_material_transactions mmt, mtl_parameters mp, -- Fix for version 1.6 -- Revision for version 1.5 -- mtl_parameters mp_owning_org, -- Owning Org -- End fix for version 1.6 mtl_parameters mp_xfer_org, -- Transfer Org mtl_parameters mp_mmt_org, -- MMT Org mfg_lookups ml1 -- Accounting Line Type -- End revision for version 1.5 -- ======================================================== -- Material transaction, date, org and item joins -- ======================================================== where mta.transaction_id = mmt.transaction_id and mp.organization_id = mta.organization_id -- Fix for version 1.6 and mp_xfer_org.organization_id = nvl(mmt.transfer_organization_id, mmt.organization_id) and mp_mmt_org.organization_id = mmt.organization_id -- End fix for version 1.6 -- to use the mmt index N1 by inventory_item_id, organization_id and date and mmt.inventory_item_id = mta.inventory_item_id and ml1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' and ml1.lookup_code = mta.accounting_line_type and 2=2 -- p_trx_date_from, p_trx_date_to and 3=3 -- p_org_code and 4=4 -- p_item_number &only_non_wip_sources1 union all -- Revision for version 1.25 -- Now join to WIP entries select mp.organization_code, null ship_from_org, null ship_to_org, mp.organization_id organization_id, -- Revision for version 1.8 mp.organization_id ship_from_org_id, mp.organization_id ship_to_org_id, mmt.fob_point fob_point, -- End revision for version 1.8 mmt.acct_period_id acct_period_id, mta.reference_account reference_account, mta.inv_sub_ledger_id inv_sub_ledger_id, -- Revision for version 1.28 mmt.transaction_id, mmt.inventory_item_id, mmt.transaction_source_type_id, mmt.transaction_type_id, mta.accounting_line_type, -- Revision for version 1.15 decode(mta.accounting_line_type, 7, ml1.meaning, 14, ml1.meaning, 1, decode(mmt.transaction_action_id, 2, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), 3, decode(mmt.organization_id, mta.organization_id, mmt.subinventory_code, mmt.transfer_subinventory ), 21, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), 22, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), 28, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), mmt.subinventory_code -- Fix for version 1.6 -- ) ) ) subinventory_code, -- End revision for version 1.15 -- Revision for version 1.26 mmt.transaction_source_id, -- Revision for version 1.25 mmt.project_id project_id, wac.class_code wip_class, ml3.meaning class_type, ml4.meaning wip_type, we.wip_entity_name job, msiv2.concatenated_segments fg_item, msiv2.description fg_description, -- End revision for version 1.25 -- Revision for version 1.20 and 1.28, logic fix for the transaction quantity -- Use quantity adjusted for Standard Cost Updates, else use the primary qty. decode(mmt.transaction_action_id, 24, mmt.quantity_adjusted, mta.primary_quantity) primary_quantity, -- End revision for version 1.28 -- Revision for version 1.13 decode(mta.cost_element_id, 1, mta.base_transaction_value, 0) Matl_Amount, decode(mta.cost_element_id, 2, mta.base_transaction_value, 0) Matl_Ovhd_Amount, decode(mta.cost_element_id, 3, mta.base_transaction_value, 0) Resource_Amount, decode(mta.cost_element_id, 4, mta.base_transaction_value, 0) OSP_Amount, decode(mta.cost_element_id, 5, mta.base_transaction_value, 0) Overhead_Amount, -- End revision for version 1.13 mta.base_transaction_value mta_amount -- Revision for version 1.28 &p_show_pii3 from mtl_transaction_accounts mta, mtl_material_transactions mmt, mtl_parameters mp, -- Revision for version 1.25 wip_entities we, wip_accounting_classes wac, wip_discrete_jobs wdj, wip_flow_schedules wfs, mtl_system_items_vl msiv2, mfg_lookups ml1, -- Accounting Line Type mfg_lookups ml3, -- Class Type mfg_lookups ml4 -- WIP Type -- End revision for version 1.25 -- ======================================================== -- Material Transaction, date, wip job, org and item joins -- ======================================================== where mta.transaction_id = mmt.transaction_id and mp.organization_id = mta.organization_id and mta.transaction_source_type_id = 5 -- This gets rid of the full table scan on wip_entities and wdj.wip_entity_id (+) = mta.transaction_source_id and wdj.organization_id (+) = mta.organization_id and wfs.wip_entity_id (+) = mta.transaction_source_id and wfs.organization_id (+) = mta.organization_id and we.wip_entity_id (+) = mta.transaction_source_id and wac.class_code = nvl(wdj.class_code, wfs.class_code) and wac.organization_id = mp.organization_id and we.primary_item_id = msiv2.inventory_item_id and we.organization_id = msiv2.organization_id and ml1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' and ml1.lookup_code = mta.accounting_line_type and ml3.lookup_type (+) = 'WIP_CLASS_TYPE' and ml3.lookup_code (+) = wac.class_type and ml4.lookup_type (+) = 'WIP_ENTITY' and ml4.lookup_code (+) = we.entity_type -- to use the mmt index N1 by inventory_item_id, organization_id and date and mmt.inventory_item_id = mta.inventory_item_id and 2=2 -- p_trx_date_from, p_trx_date_to and 3=3 -- p_org_code and 4=4 -- p_item_number &only_non_wip_sources2 -- End revision for version 1.25 ) acct_dist2 where acct_dist2.organization_id = wro_pii.organization_id (+) and acct_dist2.inventory_item_id = wro_pii.primary_item_id (+) and acct_dist2.transaction_source_id = wro_pii.wip_entity_id (+) and acct_dist2.transaction_source_type_id = wro_pii.transaction_source_type_id (+) -- Revision for version 1.26 &pii_table_joins &mtl_sla_table_joins group by acct_dist2.organization_code, acct_dist2.ship_from_org, acct_dist2.ship_to_org, acct_dist2.organization_id, acct_dist2.ship_from_org_id, acct_dist2.ship_to_org_id, acct_dist2.fob_point, acct_dist2.acct_period_id, &group_by_non_sla &group_by_sla acct_dist2.inventory_item_id, acct_dist2.transaction_source_type_id, acct_dist2.transaction_type_id, -- Revision for version 1.25 &group_by_subinventory2 &group_by_project2 &group_by_wip_job2 &group_by_pii2 acct_dist2.accounting_line_type ) acct_dist -- ======================================================== -- Material Transaction, org and i |