select nvl(gl.short_name, gl.name) Ledger,
haou2.name Operating_Unit,
mp.organization_code Org_Code,
oap.period_name Period_Name,
mtst.transaction_source_type_name Transaction_Source,
mtt.transaction_type_name Transaction_Type,
-- Revision for v1.8
(select we.wip_entity_name
from wip_entities we
where we.wip_entity_id = mmt.transaction_source_id
and mmt.transaction_source_type_id = 5) WIP_Job,
(select ml.meaning
from mfg_lookups ml, wip_discrete_jobs wdj
where wdj.wip_entity_id = mmt.transaction_source_id
and ml.lookup_type = 'WIP_JOB_STATUS'
and ml.lookup_code = wdj.status_type
and mmt.transaction_source_type_id = 5) Job_Status,
-- End revision for v1.8
mmt.transaction_id Transaction_Id,
-- Revision for version 1.10
mmt.transfer_transaction_id Transfer_Transaction_Id,
mmt.transaction_date Transaction_Date,
mmt.creation_date Creation_Date,
msiv.concatenated_segments Item_Number,
msiv.description Item_Description,
&category_columns
fcl.meaning Item_Type,
fl1.meaning Allow_Costs,
fl2.meaning Inventory_Asset,
fl3.meaning Material_Transaction_Enabled,
-- Revision for version 1.11
msub.secondary_inventory_name Subinventory_Code,
msub.description Subinventory_Description,
-- End revision for version 1.11
muomv.uom_code UOM_Code,
decode(mmt.transaction_type_id,
24, mmt.quantity_adjusted,
mmt.primary_quantity) Primary_or_Adjusted_Quantity,
-- Revision for v1.8
mmt.transaction_cost Transaction_Cost,
decode(mmt.transaction_type_id,
24, mmt.transaction_cost,
mmt.new_cost) Item_Cost,
round(sum(decode(mmt.transaction_type_id,
24, mmt.quantity_adjusted,
mmt.primary_quantity) *
decode(mmt.transaction_type_id,
24, mmt.transaction_cost,
mmt.new_cost)
),2) Extended_Inventory_Amount,
(select cct.cost_type
from cst_cost_types cct
where cct.cost_type_id = mp.primary_cost_method) Cost_Method,
(select cic.item_cost cic
from cst_item_costs cic
where cic.inventory_item_id = mmt.inventory_item_id
and cic.organization_id = nvl(mmt.transfer_organization_id, mmt.organization_id)
and cic.cost_type_id = mp.primary_cost_method) Current_Item_Cost,
mmt.error_code Error_Code,
mmt.error_explanation Error_Explanation
from mtl_material_transactions mmt,
-- Revision for version 1.11
mtl_secondary_inventories msub,
mtl_transaction_types mtt,
mtl_txn_source_types mtst,
mtl_system_items_vl msiv,
mtl_units_of_measure_vl muomv,
org_acct_periods oap,
mtl_parameters mp,
fnd_lookups fl1, -- allow costs, YES_NO
fnd_lookups fl2, -- inventory_asset_flag, YES_NO
fnd_lookups fl3, -- mtl_transactions_enabled_flag, YES_NO
fnd_common_lookups fcl, -- Item Type
hr_organization_information hoi,
hr_all_organization_units_vl haou, -- inv_organization_id
hr_all_organization_units_vl haou2, -- operating unit
gl_ledgers gl
-- ========================================================
-- Material Transaction, org and item joins
-- ========================================================
where mmt.transaction_type_id = mtt.transaction_type_id
and mmt.organization_id = msiv.organization_id
and mmt.inventory_item_id = msiv.inventory_item_id
and msiv.primary_uom_code = muomv.uom_code
and mmt.transaction_source_type_id = mtst.transaction_source_type_id
and mp.organization_id = msiv.organization_id
and oap.acct_period_id = mmt.acct_period_id
and fl1.lookup_type = 'YES_NO'
and fl1.lookup_code = msiv.costing_enabled_flag
and fl2.lookup_type = 'YES_NO'
and fl2.lookup_code = msiv.inventory_asset_flag
and fl3.lookup_type = 'YES_NO'
and fl3.lookup_code = msiv.mtl_transactions_enabled_flag
and fcl.lookup_type (+) = 'ITEM_TYPE'
and fcl.lookup_code (+) = msiv.item_type
-- ===================================================================
-- 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 = mp.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
-- ========================================================
-- Find missing accounting entries
-- ========================================================
-- Only get costed material transactions
and mmt.costed_flag is null
-- Revision for version 1.7, put 'Only Costed Items' as a parameter
-- Ignore the items which are not an inventory asset
-- and mmt.new_cost is not null
-- and msiv.inventory_asset_flag = 'Y'
-- End revision for version 1.7
-- Ignore the half of the Pick Transactions, Move Order Transfers, Subinventory Transfers and Direct Transfers which are never costed.
-- The material accounting entries are only on the initial issue from the subinventory, not on the receipt back in.
-- and (mmt.transaction_action_id not in (2,3,28) and mmt.primary_quantity > 0)
and mmt.transaction_id not in
(select mmt2.transaction_id
from mtl_material_transactions mmt2
where mmt2.transaction_id = mmt.transaction_id
and mmt.primary_quantity > 0
and mmt.transaction_action_id in (2,3,28)
)
and not exists
(select 'x'
from mtl_transaction_accounts mta
where mmt.transaction_id = mta.transaction_id)
-- Revision for version 1.11, ignore expense subinventories
and mmt.subinventory_code = msub.secondary_inventory_name (+)
and mmt.organization_id = msub.organization_id (+)
and (msub.asset_inventory = 1 -- Yes
or
-- PO receipts into an expense subinventory
(msub.asset_inventory = 2 and mmt.transaction_source_type_id = 1)
)
and mp.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
-- End revision for version 1.11
and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
and haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
and 1=1 -- p_trx_date_from, p_trx_date_to, p_org_code, p_operating_unit, p_ledger
group by
nvl(gl.short_name, gl.name), -- Ledger
haou2.name, -- Operating Unit
mp.organization_code,
oap.period_name,
mtst.transaction_source_type_name,
mtt.transaction_type_name,
-- Revision for v1.8
mmt.transaction_source_id,
mmt.transaction_source_type_id,
-- End revision for v1.8
mmt.transaction_id,
-- Revision for version 1.10
mmt.transfer_transaction_id,
mmt.transaction_date,
mmt.creation_date,
msiv.concatenated_segments,
msiv.description,
msiv.inventory_item_id,
msiv.organization_id,
mmt.inventory_item_id,
nvl(mmt.transfer_organization_id, mmt.organization_id),
mmt.cost_update_id,
mp.primary_cost_method,
fcl.meaning, -- Item Type
fl1.meaning, -- Allow Costs
fl2.meaning, -- Inventory Asset
fl3.meaning, -- Mtl Trx Enabled
-- Revision for version 1.11
msub.secondary_inventory_name,
msub.description, -- Subinventory_Description
-- End revision for version 1.11
muomv.uom_code, -- UOM Code,
decode(mmt.transaction_type_id,
24, mmt.quantity_adjusted,
mmt.primary_quantity), -- Primary_or_Adjusted_Quantity
-- Revision for v1.8
mmt.transaction_cost,
decode(mmt.transaction_type_id,
24, mmt.transaction_cost,
mmt.new_cost), -- Unit_Cost
mmt.error_code,
mmt.error_explanation
having abs(round(sum(decode(mmt.transaction_type_id,
24, mmt.quantity_adjusted,
mmt.primary_quantity) *
decode(mmt.transaction_type_id,
24, mmt.transaction_cost,
mmt.new_cost)
)
,2)) >= :p_minimum_amount -- Extended_Material_Amount
order by 1,2,3,4,6,8 |