CAC Material Account Detail

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to get the Material accounting distributions, in detail, for each item, organization and individual transaction. Including Ship From and Ship To information for inter-org transfers. 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, se ...  Report to get the Material accounting distributions, in detail, for each item, organization and individual transaction. Including Ship From and Ship To information for inter-org transfers. 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, select No to allow this report to run a bit faster. Use Show Projects to display or not display the project information (project number, name, task and project cost collection status) and use Show WIP Job to display or not display the WIP job information (WIP class, class type, WIP job, description, assembly number and assembly description). For Discrete, Flow and Workorderless WIP (but not Repetitive Schedules). Both Flow and Workorderless show up as the WIP Type "Flow schedule". And to get all positive and negative amounts above a threshold value, use the Minimum Absolute Amount parameter.

Note: this report has identical code and logic as the CAC ICP PII Material Account Detail report, however, but with the use of hidden parameters, the PII (profit in inventory) features have been turned off.

Parameters:
===========
Transaction Date From: enter the starting transaction date (mandatory).
Transaction Date To: enter the ending transaction date (mandatory).
Show SLA Accounting: enter Yes to use the Subledger Accounting rules for your accounting information (mandatory). If you choose No the report uses the pre-Create Accounting entries.
Show Projects: display the project information. Enter Yes or No, use to limit the report size (mandatory).
Show WIP: display the WIP job or flow schedule information. Enter Yes or No, use to limit the report size (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).
Accounting Line Type: enter the accounting purpose or line type to report (optional).
Transaction Type: select the material transaction type, such as PO Receipt, Return to Vendor or Account Alias Issue (optional).
Transaction Source Type: select the material transaction source, such as Purchase Order or Account Alias (optional).
Transaction Id: enter the transaction number or identifier to report (optional).
Minimum Absolute Amount: enter the minimum debit or credit to report (optional). To see all accounting entries, enter zero (0).
Only Zero Amounts: use this parameter to find entries with a zero transaction amount (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 2009- 2022 Douglas Volz Consulting, Inc.
-- | All rights reserved.
-- | Permission to use this code is granted provided the original author is
-- | acknowledged. No warranties, express or otherwise is included in this permission.
-- +=============================================================================+
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 06 Nov 2009 Douglas Volz Initial Coding
-- | 1.28 15 Oct 2022 Douglas Volz Correction for quantity calculations and PII logic.
-- | 1.29 19 Oct 2022 Douglas Volz Performance improvements for PII item costs and WIP components.
-- | 1.30 21 Oct 2022 Douglas Volz Logic change for resolving Internal RMA order numbers.
-- +=============================================================================+*/   more
Run CAC 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
  &show_document_number
  nvl((select ottt.name