Analysing Rejected Inventory Transactions

-- ---------------------------------------------------------------------------------
-- Purpose : Inventory Interface rejected Records for Analysis
-- Date      : 10.May.2009
-- Created by : Chaitanya
-- .....
-- ----------------------------------------------------------------------------------

SELECT mti.transaction_interface_id,
mti.source_code,
(select segment1 from mtl_system_items_b
where inventory_item_id = mti.inventory_item_id
and organization_id = mti.ORGANIZATION_ID ) Item,
(select decode(count(*),2,'Item Assigned','Not Assigned to One Org') from mtl_system_items_b
where inventory_item_id = mti.inventory_item_id
and organization_id in (mti.ORGANIZATION_ID, mti.TRANSFER_ORGANIZATION)) Item_assignment_STatus,
decode(sign(nvl((select transaction_quantity Availability from mtl_onhand_quantities
where inventory_item_id = mti.inventory_item_id
and organization_id = mti.organization_id
and subinventory_code = mti.SUBINVENTORY_CODE),0)+(mti.transaction_quantity)),-1,'Qty_Not_Available','Qty_Available') On_Hand_Availability_sign,
(select decode(count(*),1,'Y','N') Shipping_Network from MTL_SHIPPING_NETWORK_VIEW
where from_organization_id = mti.organization_id
and to_organization_id = mti.TRANSFER_ORGANIZATION) Shipping_Network,
(select organization_code from org_organization_definitions
where organization_id= mti.organization_id) from_org,
(select decode( sum(restrict_subinventories_code), 4,'Restriction N/A', 'Restrict_Subinv Enabled')
from mtl_system_items_b msi
where msi.inventory_item_id = mti.inventory_item_id
and organization_id in (mti.TRANSFER_ORGANIZATION,mti.organization_id)) Restrict_Subinv_Check,
(select decode( sum(restrict_locators_code), 4,'Restriction N/A', 'Restrict_Locator Enabled')
from mtl_system_items_b msi
where msi.inventory_item_id = mti.inventory_item_id
and organization_id in (mti.TRANSFER_ORGANIZATION,mti.organization_id)) Restrict_Locator_Check,
mti.subinventory_code from_Subinv,
(select organization_code from org_organization_definitions
where organization_id= mti.TRANSFER_ORGANIZATION) to_org,
mti.TRANSFER_ORGANIZATION,
mti.transfer_subinventory to_subinv,
mti.acct_period_id,
mti.transaction_date,
mti.ERROR_CODE,
mti.error_explanation
from mtl_transactions_interface mti
where process_flag = 3;

-- ---------------------------------------------------------------------------------
-- Purpose : To translate the oracle standard messages to English
-- Date : 22.May.2009
-- Created by : Chaitanya
-- .....
-- ----------------------------------------------------------------------------------


SELECT message_name,message_text from fnd_new_messages
where LANGUAGE_CODE = 'US'
and message_name in
(select message_name from fnd_new_messages
where message_text = 'Ugyldig overføringslageravdeling' --:ERROR_CODE
and LANGUAGE_CODE = 'N');

-- ---------------------------------------------------------------------------------