CAC Material Account Summary

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to get the Material accounting distributions, in summary, for each item, organization and subinventory. 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 t ...  Report to get the Material accounting distributions, in summary, for each item, organization and subinventory. 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. With parameters to also limit the report size. Use Show Subinventories to display or not display the subinventory information. Use Show Projects to display or not display the project number and name 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". Also note this report version shows the latest item status and make buy codes, even if you run the report for prior accounting periods.

Note: this report has identical code and logic as the CAC ICP PII Material Account Summary report, however, 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 number and name. Enter Yes or No, use to limit the report size (mandatory).
Show Subinventories: display the subinventory code and description. Enter Yes or No, use to limit the report size (mandatory).
Show WIP: display the WIP job or flow schedule information (WIP class, class type, WIP job, description, assembly number and assembly description). 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).
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- 2024 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.27 07 Oct 2022 Douglas Volz Correction for period name joins for interorg transactions.
-- | 1.28 16 Oct 2022 Douglas Volz Correction for quantity calculations and PII logic.
-- | 1.30 13 Jun 2024 Douglas Volz Removed tabs, reinstalled parameters, including org access restrictions.
-- +=============================================================================+*/   more

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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.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 for version 1.26
        -- ===========================================================
        -- 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
                -- fnd_lookups fl1,
                -- ===========================================================
                -- 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 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
                                 -- and        1 = 2 -- invalidate this select statement if PII is not selected                          
                                 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,
                        '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 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    4=4                             -- p_item_number
                 &only_non_wip_sources1
                 -- and        mta.transaction_source_type_id  <> 5
                 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_tr
Parameter Name SQL text Validation
Transaction Date From 
mta.transaction_date >= :p_trx_date_from and
mmt.transaction_date >= :p_trx_date_from
 Date
Transaction Date To 
mta.transaction_date < :p_trx_date_to + 1 and
mmt.transaction_date < :p_trx_date_to +1
 Date
Show SLA Accounting 
al.code_combination_id,
 LOV Oracle
Show Subinventory 
msub.secondary_inventory_name Subinventory,
 msub.description Subinventory_Description,
 LOV Oracle
Show Projects 
pp.segment1 Project_Number,
 pp.name Project_Name,
 LOV Oracle
Show WIP Job 
acct_dist.wip_class WIP_Class,
 acct_dist.class_type Class_Type,
 acct_dist.wip_type WIP_Type,
 acct_dist.job WIP_Job,
 acct_dist.fg_item Assembly_Number,
 acct_dist.fg_description Assembly_Description,
 LOV Oracle
Category Set 1 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and acct_dist.organization_id=mic.organization_id and acct_dist.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv,
fnd_id_flex_segments_vl fifsv
where
mcsv.category_set_name=:category_set_name1 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and acct_dist.organization_id=mic.organization_id and acct_dist.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name1
 LOV
Category Set 2 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and acct_dist.organization_id=mic.organization_id and acct_dist.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv,
fnd_id_flex_segments_vl fifsv
where
mcsv.category_set_name=:category_set_name2 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and acct_dist.organization_id=mic.organization_id and acct_dist.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name2
 LOV
Item Number 
mmt.inventory_item_id           = (select msiv.inventory_item_id from mtl_system_items_vl msiv where msiv.organization_id = mta.organization_id and msiv.concatenated_segments = :p_item_number)
 LOV
Organization Code 
mp.organization_code            = :p_org_code
 LOV
Operating Unit 
haou2.name = :p_operating_unit
 LOV
Ledger 
gl.name = :p_ledger
 LOV
Download
   
Related Blitz Reports
CAC Material Account Detail CAC ICP PII Material Account Detail CAC ICP PII Material Account Summary CAC ICP PII WIP Material Usage Variance CAC Manufacturing Variance Summary CAC Recost Cost of Goods Sold CAC Deferred COGS Out-of-Balance CAC WIP Material Usage Variance CAC WIP Material Usage with Configuration and Lot Variances
Blitz Report™

Blitz Report™ provides multiple benefits: