Material Accounts Distribution Report (detail version)

Description
Categories: Exclude, Volz
/* +=============================================================================+ -- | Copyright 2009-2014 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. | -- +======================================================...  /* +=============================================================================+
-- | Copyright 2009-2014 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. |
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz (doug@volzconsulting.com)
-- |
-- | Program Name: XXX_XLA_MTL_ACCTS_DETAIL_REPT.sql
-- |
-- | Parameters:
-- | P_OPERATING_UNIT -- operating unit parameter
-- | P_TRX_DATE_FROM -- starting transaction date
-- | P_TRX_DATE_TO -- ending transaction date
-- |
-- | Description:
-- | Report to get the material accounting entries by account, transaction type
-- | and accounting line type. A group by is used to add up entries across cost
-- | cost elements.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 06 Nov 2009 Douglas Volz Initial Coding
-- | 1.1 11 Nov 2009 Douglas Volz Added Org Code and transaction ID
-- | 1.2 12 Nov 2009 Douglas Volz Added item and description
-- | 1.3 06 Jan 2010 Douglas Volz Made dates a parameter
-- | 1.4 12 Jan 2010 Douglas Volz Added quantity and unit cost columns
-- +=============================================================================+*/
   more
select
gl.name "Ledger",
haou2.name "Operating Unit",
mp.organization_code "Org Code",
oap.period_name "Period Name",
gcc1.code_combination_id "CCID",
ml.meaning "Acct Line Type",
mtt.transaction_type_name "Txn Name",
mta.transaction_id "Txn Id",
gcc1.segment1 "Co",
gcc1.segment2 "Cost Ctr",
gcc1.segment3 "Acct",
gcc1.segment4 "Sub-Acct",
gcc1.segment5 "Prod Grp",
gcc1.segment6 "Loc",
gcc1.segment7 "InterCo",
gcc1.segment8 "Future1",
gcc1.segment9 "Future2",
msi.segment1 "Item",
msi.description "Description",
decode(mta.accounting_line_type, 7, 'WIP', 14, '', 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
)
) "Subinv",
mta.primary_quantity "Qty",
sum(nvl(al.accounted_dr,0) - nvl(al.accounted_cr,0)) /
decode((mta.primary_quantity), 0,1,(mta.primary_quantity)) "Unit Cost",
sum(nvl(al.accounted_dr,0) - nvl(al.accounted_cr,0)) "Amount"
from
mtl_transaction_accounts mta,
mtl_material_transactions mmt,
mtl_transaction_types mtt,
mtl_system_items_b msi,
org_acct_periods oap,
gl_code_combinations gcc1,
mtl_parameters mp,
mfg_lookups ml,
hr_organization_information hoi,
hr_all_organization_units haou, -- inv_organization_id
hr_all_organization_units haou2, -- operating unit
gl_ledgers gl,
xla.xla_transaction_entities ent,
xla_events xe,
xla_distribution_links xdl,
xla_ae_headers ah,
xla_ae_lines al
-- ========================================================
-- material transaction, org and item joins
-- ========================================================
where 1=1
and mta.transaction_id = mmt.transaction_id
and mmt.transaction_type_id = mtt.transaction_type_id
and mta.organization_id = msi.organization_id
and mta.inventory_item_id = msi.inventory_item_id
and mp.organization_id = msi.organization_id
-- ========================================================
-- inventory org accounting period joins
-- ========================================================
and oap.period_name = ah.period_name
and oap.organization_id = mta.organization_id
-- ========================================================
-- version 1.3, added lookup values to see more detail
-- ========================================================
and ml.lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
and ml.lookup_code = mta.accounting_line_type
-- ========================================================
-- material transaction date and accounting code joins
-- ========================================================
-- ========================================================
-- using the base tables to avoid the performance issues
-- with org_organization_definitions and hr_operating_units
-- ========================================================
and hoi.org_information_context = 'Accounting Information'
and hoi.organization_id = mta.organization_id
and hoi.organization_id = haou.organization_id -- this gets the organization name
and haou2.organization_id = to_number(hoi.org_information3)
-- this gets the operating unit id
and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id
-- ========================================================
-- sla table joins to get the exact account numbers
-- ========================================================
and ent.entity_code = 'MTL_ACCOUNTING_EVENTS'
and ent.application_id = 707
and xe.application_id = ent.application_id
and xe.event_id = xdl.event_id
and ah.entity_id = ent.entity_id
and ah.ledger_id = ent.ledger_id
and ah.application_id = al.application_id
and ah.application_id = 707
and ah.event_id = xe.event_id
and ah.ae_header_id = al.ae_header_id
and al.application_id = ent.application_id
and al.ledger_id = ah.ledger_id
and al.ae_header_id = xdl.ae_header_id
and al.ae_line_num = xdl.ae_line_num
and xdl.application_id = ent.application_id
and xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id
and gcc1.code_combination_id = al.code_combination_id
-- ==========================================================
group by
gl.name,
haou2.name,
mp.organization_code,
oap.period_name,
gcc1.code_combination_id,
ml.meaning,
mtt.transaction_type_name,
mta.transaction_id,
gcc1.segment1,
gcc1.segment2,
gcc1.segment3,
gcc1.segment4,
gcc1.segment5,
gcc1.segment6,
gcc1.segment7,
gcc1.segment8,
gcc1.segment9,
msi.segment1,
msi.description,
decode(mta.accounting_line_type, 7, 'WIP', 14, '', 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
)
),
mta.primary_quantity
order by 1,2
Parameter Name SQL text Validation
Transaction Date To
mta.transaction_date < :P_TRX_TO
Date
Transaction Date From
mta.transaction_date >= :P_TRX_FROM
Date