CAC ICP PII Material Account Detail
Description
Categories: Enginatics
Repository: Github
Repository: Github
Report to get the Material accounting distributions, in detail, for each item, organization and individual transaction. Including profit in inventory amounts based on your PII cost type. With the Show SLA Accounting parameter you can choose to use the Release 12 Subledger Accounting (Create Accounting) account setups by selecting Yes. And if you have not modified your SLA accounting rules, sele ...
more
Run
CAC ICP PII Material Account Detail 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 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 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 ) ----------------main query starts here-------------- 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.27 acct_dist.document_order_num Document_Number, decode(acct_dist.transaction_source_type_id, 2, acct_dist.order_type, -- Sales order 8, acct_dist.order_type, -- Internal order 12, acct_dist.order_type, -- RMA 101, acct_dist.order_type, -- Internal RMA null) Order_Type, br.resource_code Sub_Element, acct_dist.transaction_id Transaction_Id, acct_dist.transfer_transaction_id Transfer_Transaction_Id, acct_dist.parent_transaction_id Parent_Transaction_Id, acct_dist.transaction_date Transaction_Date, acct_dist.creation_date Creation_Date, fu.user_name Created_By, acct_dist.transaction_reference Transaction_Comments, -- End revision for version 1.27 -- Revision for version 1.25 msub.secondary_inventory_name Subinventory, msub.description Subinventory_Description, -- Revision for version 1.27 acct_dist.transfer_subinventory Transfer_Subinventory, ccg.cost_group Cost_Group, ccg_xfer.cost_group Transfer_Cost_Group, -- End revision for version 1.27 &p_show_project &p_show_wip_job muomv.uom_code UOM_Code, acct_dist.primary_quantity Quantity, -- Revision for version 1.27 cce.cost_element Cost_Element, gl.currency_code Currency_Code, acct_dist.Matl_Amount Material_Amount, acct_dist.Matl_Ovhd_Amount Material_Overhead_Amount, acct_dist.Resource_Amount Resource_Amount, acct_dist.OSP_Amount Outside_Processing_Amount, acct_dist.Overhead_Amount Overhead_Amount, -- Revision for version 1.27 round(acct_dist.mta_amount / decode(acct_dist.primary_quantity, 0, 1, acct_dist.primary_quantity),5) Item_Cost, -- End revision for version 1.27 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, -- Revision for version 1.27 mtl_secondary_inventories msub, mtl_transaction_reasons mtr, cst_cost_groups ccg, cst_cost_groups ccg_xfer, cst_cost_elements cce, bom_resources br, fnd_user fu, -- End revision for version 1.27 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 versions 1.26 and 1.27 &pii_lookup_table &project_tables &mtl_sla_tables -- Revision for version 1.26 -- =========================================================== -- 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, 'N' 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 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 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 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 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, 'N', -- 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 -- ========================================================================== ( select mp.organization_code 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, mmt.inventory_item_id, mmt.transaction_source_type_id, -- Revision for version 1.27 &s |