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.INVFV_MATERIAL_TRANSACTIONS

Source

View Text - Preformatted


SELECT MMT.TRANSACTION_ID, MMT.TRANSACTION_TYPE_ID, MTT.TRANSACTION_TYPE_NAME, '_LA:MMT.TRANSACTION_ACTION_ID:MFG_LOOKUPS:MTL_TRANSACTION_ACTION:MEANING', MMT.TRANSACTION_SOURCE_TYPE_ID, MTS.TRANSACTION_SOURCE_TYPE_NAME, MMT.TRANSACTION_SOURCE_ID, INV_OBJECT_GENEALOGY.getSource(MMT.ORGANIZATION_ID, MMT.TRANSACTION_SOURCE_TYPE_ID, MMT.TRANSACTION_SOURCE_ID), decode(MMT.TRANSACTION_SOURCE_TYPE_ID,1,MMT.TRANSACTION_SOURCE_ID,TO_NUMBER(NULL)), decode(MMT.TRANSACTION_SOURCE_TYPE_ID,2,MMT.TRANSACTION_SOURCE_ID, 8,MMT.TRANSACTION_SOURCE_ID, 12,MMT.TRANSACTION_SOURCE_ID, TO_NUMBER(NULL)), decode(MMT.TRANSACTION_SOURCE_TYPE_ID,3,MMT.TRANSACTION_SOURCE_ID,TO_NUMBER(NULL)), decode(MMT.TRANSACTION_SOURCE_TYPE_ID,5,MMT.TRANSACTION_SOURCE_ID,TO_NUMBER(NULL)), decode(MMT.TRANSACTION_SOURCE_TYPE_ID,6,MMT.TRANSACTION_SOURCE_ID,TO_NUMBER(NULL)), decode(MMT.TRANSACTION_SOURCE_TYPE_ID,7,MMT.TRANSACTION_SOURCE_ID,TO_NUMBER(NULL)), decode(MMT.TRANSACTION_SOURCE_TYPE_ID,9,MMT.TRANSACTION_SOURCE_ID,TO_NUMBER(NULL)), decode(MMT.TRANSACTION_SOURCE_TYPE_ID,10,MMT.TRANSACTION_SOURCE_ID,TO_NUMBER(NULL)), decode(MMT.TRANSACTION_SOURCE_TYPE_ID,11,MMT.TRANSACTION_SOURCE_ID,TO_NUMBER(NULL)), MMT.TRANSACTION_SOURCE_NAME, MMT.TRANSACTION_DATE, MMT.TRANSACTION_QUANTITY, MMT.TRANSACTION_UOM, MMT.PRIMARY_QUANTITY, MSI.PRIMARY_UOM_CODE, MMT.INVENTORY_ITEM_ID, '_KF:INV:MSTK:MSI', MMT.ORGANIZATION_ID, HOU1.NAME, MMT.SUBINVENTORY_CODE, MMT.LOCATOR_ID, '_KF:INV:MTLL:MIL1', MMT.REVISION, MMT.TRANSFER_SUBINVENTORY, MMT.TRANSFER_LOCATOR_ID, '_KF:INV:MTLL:MIL2', MMT.TRANSFER_ORGANIZATION_ID, HOU2.NAME, MMT.TRANSFER_TRANSACTION_ID, MMT.TRANSACTION_SET_ID, MMT.SHIPMENT_NUMBER, MMT.WAYBILL_AIRBILL, MMT.FREIGHT_CODE, MMT.NUMBER_OF_CONTAINERS, MMT.REASON_ID, MTR.REASON_NAME, MMT.TRANSACTION_REFERENCE, '_LA:DECODE(MMT.COSTED_FLAG,NULL,1,''Y'',1,''N'',2,''E'',3) 	:MFG_LOOKUPS:INV_YES_NO_ERROR:MEANING', '_LA: DECODE (MMT.SOURCE_PROJECT_ID, NULL,  	DECODE(MMT.PROJECT_ID, NULL, NULL,    	DECODE(MMT.PM_COST_COLLECTED,NULL, 1,''Y'',  1,''N'',  2,''E'',  3, 1)	), 	DECODE( MMT.PM_COST_COLLECTED,NULL, 1,''Y'', 1,''N'', 2,''E'', 3, 1) )   	:MFG_LOOKUPS:INV_YES_NO_ERROR:MEANING', MMT.VENDOR_LOT_NUMBER, MMT.SOURCE_CODE, MMT.SOURCE_LINE_ID, MMT.RCV_TRANSACTION_ID, MMT.MOVE_TRANSACTION_ID, MMT.COMPLETION_TRANSACTION_ID, MMT.DEPARTMENT_ID, BMD.DEPARTMENT_CODE, MMT.OPERATION_SEQ_NUM, MMT.SOURCE_PROJECT_ID, PJM2.SEGMENT1, MMT.SOURCE_TASK_ID, PTS2.TASK_NUMBER, MMT.PROJECT_ID, PJM1.SEGMENT1, MMT.TASK_ID, PTS1.TASK_NUMBER, MMT.TO_PROJECT_ID, PJM3.SEGMENT1, MMT.TO_TASK_ID, PTS3.TASK_NUMBER, MMT.EXPENDITURE_TYPE, MMT.PA_EXPENDITURE_ORG_ID, HOU3.NAME, MMT.ERROR_CODE, MMT.ERROR_EXPLANATION, MMT.CURRENCY_CODE, MMT.OVERCOMPLETION_TRANSACTION_QTY, MMT.OVERCOMPLETION_TRANSACTION_QTY, MMT.OVERCOMPLETION_TRANSACTION_ID, MMT.COMMON_BOM_SEQ_ID, BOM2.ALTERNATE_BOM_DESIGNATOR, MMT.COMMON_ROUTING_SEQ_ID, RTG2.ALTERNATE_ROUTING_DESIGNATOR, MMT.ORG_COST_GROUP_ID, CCG.COST_GROUP, MMT.COST_TYPE_ID, CCT.COST_TYPE, decode(MMT.TRANSACTION_SOURCE_TYPE_ID,4,MMT.TRANSACTION_SOURCE_ID,TO_NUMBER(NULL)) FROM BOM_DEPARTMENTS BMD, MTL_TRANSACTION_REASONS MTR, PA_PROJECTS_ALL PJM1, PA_TASKS PTS1, PA_PROJECTS_ALL PJM2, PA_TASKS PTS2, PA_PROJECTS_ALL PJM3, PA_TASKS PTS3, HR_ALL_ORGANIZATION_UNITS HOU3, HR_ALL_ORGANIZATION_UNITS HOU2, HR_ALL_ORGANIZATION_UNITS HOU1, MTL_SYSTEM_ITEMS MSI, MTL_ITEM_LOCATIONS MIL1, MTL_ITEM_LOCATIONS MIL2, MTL_TXN_SOURCE_TYPES MTS, MTL_TRANSACTION_TYPES MTT, BOM_BILL_OF_MATERIALS BOM1, BOM_BILL_OF_MATERIALS BOM2, BOM_OPERATIONAL_ROUTINGS RTG1, BOM_OPERATIONAL_ROUTINGS RTG2, CST_COST_GROUPS CCG, CST_COST_TYPES CCT, MTL_MATERIAL_TRANSACTIONS MMT WHERE MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID AND MTS.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID AND MIL1.INVENTORY_LOCATION_ID (+) = MMT.LOCATOR_ID AND MIL2.INVENTORY_LOCATION_ID (+) = MMT.TRANSFER_LOCATOR_ID AND HOU1.ORGANIZATION_ID = MMT.ORGANIZATION_ID AND HOU2.ORGANIZATION_ID (+) = MMT.TRANSFER_ORGANIZATION_ID AND HOU3.ORGANIZATION_ID (+) = MMT.PA_EXPENDITURE_ORG_ID AND PJM1.PROJECT_ID (+) = MMT.PROJECT_ID AND PTS1.PROJECT_ID (+) = MMT.PROJECT_ID AND PTS1.TASK_ID (+) = MMT.TASK_ID AND PJM2.PROJECT_ID (+) = MMT.SOURCE_PROJECT_ID AND PTS2.PROJECT_ID (+) = MMT.SOURCE_PROJECT_ID AND PTS2.TASK_ID (+) = MMT.SOURCE_TASK_ID AND PJM3.PROJECT_ID (+) = MMT.TO_PROJECT_ID AND PTS3.PROJECT_ID (+) = MMT.TO_PROJECT_ID AND PTS3.TASK_ID (+) = MMT.TO_TASK_ID AND MTR.REASON_ID (+) = MMT.REASON_ID AND BMD.DEPARTMENT_ID (+) = MMT.DEPARTMENT_ID AND BOM1.COMMON_BILL_SEQUENCE_ID (+) = MMT.COMMON_BOM_SEQ_ID AND BOM2.BILL_SEQUENCE_ID (+) = BOM1.COMMON_BILL_SEQUENCE_ID AND RTG1.COMMON_ROUTING_SEQUENCE_ID (+) = MMT.COMMON_ROUTING_SEQ_ID AND RTG2.ROUTING_SEQUENCE_ID (+) = RTG1.COMMON_ROUTING_SEQUENCE_ID AND CCG.COST_GROUP_ID (+) = MMT.ORG_COST_GROUP_ID AND CCT.COST_TYPE_ID (+) = MMT.COST_TYPE_ID AND '_SEC:MMT.ORGANIZATION_ID' IS NOT NULL AND MMT.ORGANIZATION_ID = NVL (MMT.OWNING_ORGANIZATION_ID, MMT.ORGANIZATION_ID) AND NVL(MMT.OWNING_TP_TYPE, 2) = 2 AND NVL(MMT.LOGICAL_TRANSACTION,-99) <> 1 WITH READ ONLY

View Text - HTML Formatted

SELECT MMT.TRANSACTION_ID
, MMT.TRANSACTION_TYPE_ID
, MTT.TRANSACTION_TYPE_NAME
, '_LA:MMT.TRANSACTION_ACTION_ID:MFG_LOOKUPS:MTL_TRANSACTION_ACTION:MEANING'
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MTS.TRANSACTION_SOURCE_TYPE_NAME
, MMT.TRANSACTION_SOURCE_ID
, INV_OBJECT_GENEALOGY.GETSOURCE(MMT.ORGANIZATION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID)
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 1
, MMT.TRANSACTION_SOURCE_ID
, TO_NUMBER(NULL))
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 2
, MMT.TRANSACTION_SOURCE_ID
, 8
, MMT.TRANSACTION_SOURCE_ID
, 12
, MMT.TRANSACTION_SOURCE_ID
, TO_NUMBER(NULL))
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 3
, MMT.TRANSACTION_SOURCE_ID
, TO_NUMBER(NULL))
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 5
, MMT.TRANSACTION_SOURCE_ID
, TO_NUMBER(NULL))
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 6
, MMT.TRANSACTION_SOURCE_ID
, TO_NUMBER(NULL))
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 7
, MMT.TRANSACTION_SOURCE_ID
, TO_NUMBER(NULL))
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 9
, MMT.TRANSACTION_SOURCE_ID
, TO_NUMBER(NULL))
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 10
, MMT.TRANSACTION_SOURCE_ID
, TO_NUMBER(NULL))
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 11
, MMT.TRANSACTION_SOURCE_ID
, TO_NUMBER(NULL))
, MMT.TRANSACTION_SOURCE_NAME
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_QUANTITY
, MMT.TRANSACTION_UOM
, MMT.PRIMARY_QUANTITY
, MSI.PRIMARY_UOM_CODE
, MMT.INVENTORY_ITEM_ID
, '_KF:INV:MSTK:MSI'
, MMT.ORGANIZATION_ID
, HOU1.NAME
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, '_KF:INV:MTLL:MIL1'
, MMT.REVISION
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, '_KF:INV:MTLL:MIL2'
, MMT.TRANSFER_ORGANIZATION_ID
, HOU2.NAME
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_SET_ID
, MMT.SHIPMENT_NUMBER
, MMT.WAYBILL_AIRBILL
, MMT.FREIGHT_CODE
, MMT.NUMBER_OF_CONTAINERS
, MMT.REASON_ID
, MTR.REASON_NAME
, MMT.TRANSACTION_REFERENCE
, '_LA:DECODE(MMT.COSTED_FLAG
, NULL
, 1
, ''Y''
, 1
, ''N''
, 2
, ''E''
, 3) :MFG_LOOKUPS:INV_YES_NO_ERROR:MEANING'
, '_LA: DECODE (MMT.SOURCE_PROJECT_ID
, NULL
, DECODE(MMT.PROJECT_ID
, NULL
, NULL
, DECODE(MMT.PM_COST_COLLECTED
, NULL
, 1
, ''Y''
, 1
, ''N''
, 2
, ''E''
, 3
, 1) )
, DECODE( MMT.PM_COST_COLLECTED
, NULL
, 1
, ''Y''
, 1
, ''N''
, 2
, ''E''
, 3
, 1) ) :MFG_LOOKUPS:INV_YES_NO_ERROR:MEANING'
, MMT.VENDOR_LOT_NUMBER
, MMT.SOURCE_CODE
, MMT.SOURCE_LINE_ID
, MMT.RCV_TRANSACTION_ID
, MMT.MOVE_TRANSACTION_ID
, MMT.COMPLETION_TRANSACTION_ID
, MMT.DEPARTMENT_ID
, BMD.DEPARTMENT_CODE
, MMT.OPERATION_SEQ_NUM
, MMT.SOURCE_PROJECT_ID
, PJM2.SEGMENT1
, MMT.SOURCE_TASK_ID
, PTS2.TASK_NUMBER
, MMT.PROJECT_ID
, PJM1.SEGMENT1
, MMT.TASK_ID
, PTS1.TASK_NUMBER
, MMT.TO_PROJECT_ID
, PJM3.SEGMENT1
, MMT.TO_TASK_ID
, PTS3.TASK_NUMBER
, MMT.EXPENDITURE_TYPE
, MMT.PA_EXPENDITURE_ORG_ID
, HOU3.NAME
, MMT.ERROR_CODE
, MMT.ERROR_EXPLANATION
, MMT.CURRENCY_CODE
, MMT.OVERCOMPLETION_TRANSACTION_QTY
, MMT.OVERCOMPLETION_TRANSACTION_QTY
, MMT.OVERCOMPLETION_TRANSACTION_ID
, MMT.COMMON_BOM_SEQ_ID
, BOM2.ALTERNATE_BOM_DESIGNATOR
, MMT.COMMON_ROUTING_SEQ_ID
, RTG2.ALTERNATE_ROUTING_DESIGNATOR
, MMT.ORG_COST_GROUP_ID
, CCG.COST_GROUP
, MMT.COST_TYPE_ID
, CCT.COST_TYPE
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 4
, MMT.TRANSACTION_SOURCE_ID
, TO_NUMBER(NULL))
FROM BOM_DEPARTMENTS BMD
, MTL_TRANSACTION_REASONS MTR
, PA_PROJECTS_ALL PJM1
, PA_TASKS PTS1
, PA_PROJECTS_ALL PJM2
, PA_TASKS PTS2
, PA_PROJECTS_ALL PJM3
, PA_TASKS PTS3
, HR_ALL_ORGANIZATION_UNITS HOU3
, HR_ALL_ORGANIZATION_UNITS HOU2
, HR_ALL_ORGANIZATION_UNITS HOU1
, MTL_SYSTEM_ITEMS MSI
, MTL_ITEM_LOCATIONS MIL1
, MTL_ITEM_LOCATIONS MIL2
, MTL_TXN_SOURCE_TYPES MTS
, MTL_TRANSACTION_TYPES MTT
, BOM_BILL_OF_MATERIALS BOM1
, BOM_BILL_OF_MATERIALS BOM2
, BOM_OPERATIONAL_ROUTINGS RTG1
, BOM_OPERATIONAL_ROUTINGS RTG2
, CST_COST_GROUPS CCG
, CST_COST_TYPES CCT
, MTL_MATERIAL_TRANSACTIONS MMT
WHERE MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTS.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MIL1.INVENTORY_LOCATION_ID (+) = MMT.LOCATOR_ID
AND MIL2.INVENTORY_LOCATION_ID (+) = MMT.TRANSFER_LOCATOR_ID
AND HOU1.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND HOU2.ORGANIZATION_ID (+) = MMT.TRANSFER_ORGANIZATION_ID
AND HOU3.ORGANIZATION_ID (+) = MMT.PA_EXPENDITURE_ORG_ID
AND PJM1.PROJECT_ID (+) = MMT.PROJECT_ID
AND PTS1.PROJECT_ID (+) = MMT.PROJECT_ID
AND PTS1.TASK_ID (+) = MMT.TASK_ID
AND PJM2.PROJECT_ID (+) = MMT.SOURCE_PROJECT_ID
AND PTS2.PROJECT_ID (+) = MMT.SOURCE_PROJECT_ID
AND PTS2.TASK_ID (+) = MMT.SOURCE_TASK_ID
AND PJM3.PROJECT_ID (+) = MMT.TO_PROJECT_ID
AND PTS3.PROJECT_ID (+) = MMT.TO_PROJECT_ID
AND PTS3.TASK_ID (+) = MMT.TO_TASK_ID
AND MTR.REASON_ID (+) = MMT.REASON_ID
AND BMD.DEPARTMENT_ID (+) = MMT.DEPARTMENT_ID
AND BOM1.COMMON_BILL_SEQUENCE_ID (+) = MMT.COMMON_BOM_SEQ_ID
AND BOM2.BILL_SEQUENCE_ID (+) = BOM1.COMMON_BILL_SEQUENCE_ID
AND RTG1.COMMON_ROUTING_SEQUENCE_ID (+) = MMT.COMMON_ROUTING_SEQ_ID
AND RTG2.ROUTING_SEQUENCE_ID (+) = RTG1.COMMON_ROUTING_SEQUENCE_ID
AND CCG.COST_GROUP_ID (+) = MMT.ORG_COST_GROUP_ID
AND CCT.COST_TYPE_ID (+) = MMT.COST_TYPE_ID
AND '_SEC:MMT.ORGANIZATION_ID' IS NOT NULL
AND MMT.ORGANIZATION_ID = NVL (MMT.OWNING_ORGANIZATION_ID
, MMT.ORGANIZATION_ID)
AND NVL(MMT.OWNING_TP_TYPE
, 2) = 2
AND NVL(MMT.LOGICAL_TRANSACTION
, -99) <> 1 WITH READ ONLY