eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CST_OPI_MMT_MTA_V

Source

View Text - Preformatted


SELECT mmt.transaction_id , mmt.transaction_date , mta.organization_id , mmt.subinventory_code , mmt.inventory_item_id , mmt.locator_id , mmt.transaction_type_id , mmt.transaction_action_id , mmt.transaction_source_type_id , mmt.transaction_source_id , mmt.transaction_source_name , mmt.reason_id , mmt.transfer_organization_id , mmt.transfer_subinventory , mmt.transfer_locator_id , mmt.fob_point , mmt.primary_quantity , mmt.transfer_cost , SUM(mta.base_transaction_value) actual_cost FROM mtl_material_transactions MMT, mtl_transaction_accounts MTA WHERE MTA.accounting_line_type = 1 AND ( MMT.primary_quantity = NVL(MTA.primary_quantity, MMT.primary_quantity) OR MMT.transaction_action_id in (1,24)) AND MMT.transaction_id = MTA.transaction_id AND MMT.transaction_type_id <> 25 group by mmt.transaction_id , mmt.transaction_date , mta.organization_id , mmt.subinventory_code , mmt.inventory_item_id , mmt.locator_id , mmt.transaction_type_id , mmt.transaction_action_id , mmt.transaction_source_type_id , mmt.transaction_source_id , mmt.transaction_source_name , mmt.reason_id , mmt.transfer_organization_id , mmt.transfer_subinventory , mmt.transfer_locator_id , mmt.fob_point , mmt.primary_quantity , mmt.transfer_cost UNION ALL select mmt.transaction_id , mmt.transaction_date , mta.organization_id , mmt.subinventory_code , mmt.inventory_item_id , mmt.locator_id , mmt.transaction_type_id , mmt.transaction_action_id , mmt.transaction_source_type_id , mmt.transaction_source_id , mmt.transaction_source_name , mmt.reason_id , mmt.transfer_organization_id , mmt.transfer_subinventory , mmt.transfer_locator_id , mmt.fob_point , mmt.primary_quantity , mmt.transfer_cost , SUM(mta.base_transaction_value) actual_cost FROM mtl_material_transactions MMT, mtl_transaction_accounts MTA WHERE MTA.accounting_line_type = 1 AND MMT.primary_quantity = MTA.primary_quantity AND MMT.transfer_transaction_id = MTA.transaction_id AND MMT.transaction_action_id in (2,3,5,28,55) group by mmt.transaction_id , mmt.transaction_date , mta.organization_id , mmt.subinventory_code , mmt.inventory_item_id , mmt.locator_id , mmt.transaction_type_id , mmt.transaction_action_id , mmt.transaction_source_type_id , mmt.transaction_source_id , mmt.transaction_source_name , mmt.reason_id , mmt.transfer_organization_id , mmt.transfer_subinventory , mmt.transfer_locator_id , mmt.fob_point , mmt.primary_quantity , mmt.transfer_cost UNION ALL select mmt.transaction_id , mmt.transaction_date , mmt.organization_id , mmt.subinventory_code , mmt.inventory_item_id , mmt.locator_id , mmt.transaction_type_id , mmt.transaction_action_id , mmt.transaction_source_type_id , mmt.transaction_source_id , mmt.transaction_source_name , mmt.reason_id , mmt.transfer_organization_id , mmt.transfer_subinventory , mmt.transfer_locator_id , mmt.fob_point , mmt.primary_quantity , mmt.transfer_cost , null actual_cost FROM mtl_material_transactions MMT, mtl_parameters PAR WHERE mmt.organization_id=par.organization_id AND par.process_enabled_flag = 'N' AND mmt.costed_flag IS NULL AND mmt.transaction_type_id not in (73,25,26,90, 91, 92, 55, 56, 57, 58, 87, 88, 89) AND mmt.organization_id = NVL(mmt.owning_organization_id, mmt.organization_id) AND NVL(mmt.owning_tp_type, 2) = 2 AND NOT EXISTS (SELECT 1 from mtl_transaction_accounts mta where mta.transaction_id = mmt.transaction_id and mta.accounting_line_type = 1 UNION ALL SELECT 1 from mtl_transaction_accounts mta where mta.transaction_id = mmt.transfer_transaction_id and mta.accounting_line_type = 1)

View Text - HTML Formatted

SELECT MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MTA.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.INVENTORY_ITEM_ID
, MMT.LOCATOR_ID
, MMT.TRANSACTION_TYPE_ID
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.REASON_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MMT.FOB_POINT
, MMT.PRIMARY_QUANTITY
, MMT.TRANSFER_COST
, SUM(MTA.BASE_TRANSACTION_VALUE) ACTUAL_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_ACCOUNTS MTA
WHERE MTA.ACCOUNTING_LINE_TYPE = 1
AND ( MMT.PRIMARY_QUANTITY = NVL(MTA.PRIMARY_QUANTITY
, MMT.PRIMARY_QUANTITY) OR MMT.TRANSACTION_ACTION_ID IN (1
, 24))
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.TRANSACTION_TYPE_ID <> 25 GROUP BY MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MTA.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.INVENTORY_ITEM_ID
, MMT.LOCATOR_ID
, MMT.TRANSACTION_TYPE_ID
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.REASON_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MMT.FOB_POINT
, MMT.PRIMARY_QUANTITY
, MMT.TRANSFER_COST UNION ALL SELECT MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MTA.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.INVENTORY_ITEM_ID
, MMT.LOCATOR_ID
, MMT.TRANSACTION_TYPE_ID
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.REASON_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MMT.FOB_POINT
, MMT.PRIMARY_QUANTITY
, MMT.TRANSFER_COST
, SUM(MTA.BASE_TRANSACTION_VALUE) ACTUAL_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_ACCOUNTS MTA
WHERE MTA.ACCOUNTING_LINE_TYPE = 1
AND MMT.PRIMARY_QUANTITY = MTA.PRIMARY_QUANTITY
AND MMT.TRANSFER_TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.TRANSACTION_ACTION_ID IN (2
, 3
, 5
, 28
, 55) GROUP BY MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MTA.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.INVENTORY_ITEM_ID
, MMT.LOCATOR_ID
, MMT.TRANSACTION_TYPE_ID
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.REASON_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MMT.FOB_POINT
, MMT.PRIMARY_QUANTITY
, MMT.TRANSFER_COST UNION ALL SELECT MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MMT.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.INVENTORY_ITEM_ID
, MMT.LOCATOR_ID
, MMT.TRANSACTION_TYPE_ID
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.REASON_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MMT.FOB_POINT
, MMT.PRIMARY_QUANTITY
, MMT.TRANSFER_COST
, NULL ACTUAL_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_PARAMETERS PAR
WHERE MMT.ORGANIZATION_ID=PAR.ORGANIZATION_ID
AND PAR.PROCESS_ENABLED_FLAG = 'N'
AND MMT.COSTED_FLAG IS NULL
AND MMT.TRANSACTION_TYPE_ID NOT IN (73
, 25
, 26
, 90
, 91
, 92
, 55
, 56
, 57
, 58
, 87
, 88
, 89)
AND MMT.ORGANIZATION_ID = NVL(MMT.OWNING_ORGANIZATION_ID
, MMT.ORGANIZATION_ID)
AND NVL(MMT.OWNING_TP_TYPE
, 2) = 2
AND NOT EXISTS (SELECT 1
FROM MTL_TRANSACTION_ACCOUNTS MTA
WHERE MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
AND MTA.ACCOUNTING_LINE_TYPE = 1 UNION ALL SELECT 1
FROM MTL_TRANSACTION_ACCOUNTS MTA
WHERE MTA.TRANSACTION_ID = MMT.TRANSFER_TRANSACTION_ID
AND MTA.ACCOUNTING_LINE_TYPE = 1)