VIEW: APPS.MTL_MATERIAL_TXNS_VAL_V

Source

View Text - Preformatted

SELECT M.ROWID ROW_ID , M.TRANSACTION_ID , M.LAST_UPDATE_DATE , M.LAST_UPDATED_BY , M.CREATION_DATE , M.CREATED_BY , M.LAST_UPDATE_LOGIN , M.REQUEST_ID , M.PROGRAM_APPLICATION_ID , M.PROGRAM_ID , M.PROGRAM_UPDATE_DATE , M.INVENTORY_ITEM_ID , M.REVISION , M.ORGANIZATION_ID , M.SUBINVENTORY_CODE SUBINVENTORY , M.LOCATOR_ID , M.TRANSACTION_TYPE_ID , MTT.TRANSACTION_TYPE_NAME , M.TRANSACTION_ACTION_ID , ML1.MEANING TRANSACTION_ACTION , M.COSTED_FLAG , ML2.MEANING COSTED_FLAG_1 , ML2.LOOKUP_CODE COSTED_LOOKUP_CODE , M.PM_COST_COLLECTED , ML3.MEANING PM_COST_COLLECTED_1 , ML3.LOOKUP_CODE PM_COST_COLLECTED_LOOKUP_CODE , M.TRANSACTION_SOURCE_TYPE_ID , MTST.TRANSACTION_SOURCE_TYPE_NAME , M.TRANSACTION_SOURCE_ID , M.TRANSACTION_SOURCE_NAME TRANSACTION_SOURCE_NAME_DB , M.TRANSACTION_QUANTITY , M.TRANSACTION_UOM , M.PRIMARY_QUANTITY , M.TRANSACTION_DATE , M.TRANSACTION_REFERENCE , MTR.REASON_NAME , M.EMPLOYEE_CODE , M.DEPARTMENT_ID , BM.DEPARTMENT_CODE , M.OPERATION_SEQ_NUM , M.MASTER_SCHEDULE_UPDATE_CODE , M.RECEIVING_DOCUMENT , M.TRANSFER_TRANSACTION_ID , M.TRANSACTION_SET_ID , M.RCV_TRANSACTION_ID , M.MOVE_TRANSACTION_ID , M.COMPLETION_TRANSACTION_ID , M.SOURCE_CODE , M.SOURCE_LINE_ID , M.VENDOR_LOT_NUMBER , M.TRANSFER_ORGANIZATION_ID , HRT.NAME TRANSFER_ORGANIZATION_NAME , M.TRANSFER_SUBINVENTORY , M.TRANSFER_LOCATOR_ID , M.SHIPMENT_NUMBER , M.WAYBILL_AIRBILL , M.FREIGHT_CODE , M.NUMBER_OF_CONTAINERS , M.ATTRIBUTE_CATEGORY , M.ATTRIBUTE1 , M.ATTRIBUTE2 , M.ATTRIBUTE3 , M.ATTRIBUTE4 , M.ATTRIBUTE5 , M.ATTRIBUTE6 , M.ATTRIBUTE7 , M.ATTRIBUTE8 , M.ATTRIBUTE9 , M.ATTRIBUTE10 , M.ATTRIBUTE11 , M.ATTRIBUTE12 , M.ATTRIBUTE13 , M.ATTRIBUTE14 , M.ATTRIBUTE15 , ISEG.CONCATENATED_SEGMENTS ITEM_ORD , ISEG.PRIMARY_UOM_CODE , ISEG.DESCRIPTION , M.SOURCE_PROJECT_ID , M.SOURCE_TASK_ID , M.PROJECT_ID , M.TO_PROJECT_ID , M.TASK_ID , M.TO_TASK_ID , M.EXPENDITURE_TYPE , M.PA_EXPENDITURE_ORG_ID , M.ERROR_CODE , M.ERROR_EXPLANATION , M.OVERCOMPLETION_TRANSACTION_QTY , M.OVERCOMPLETION_TRANSACTION_ID , M.PM_COST_COLLECTOR_GROUP_ID , M.MOVE_ORDER_LINE_ID , M.PICK_SLIP_NUMBER , M.TASK_GROUP_ID , M.COST_GROUP_ID , M.TRANSFER_COST_GROUP_ID ,M.LPN_ID ,M.TRANSFER_LPN_ID ,LPN1.LICENSE_PLATE_NUMBER LPN ,LPN2.LICENSE_PLATE_NUMBER TRANSFER_LPN ,CG.COST_GROUP COST_GROUP_NAME ,CG2.COST_GROUP TRANSFER_COST_GROUP_NAME , M.PICK_STRATEGY_ID , M.PICK_RULE_ID , M.PUT_AWAY_STRATEGY_ID , M.PUT_AWAY_RULE_ID , M.CONTENT_LPN_ID , LPN3.LICENSE_PLATE_NUMBER CONTENT_LPN FROM MTL_MATERIAL_TRANSACTIONS M, MTL_TRANSACTION_TYPES MTT, MFG_LOOKUPS ML1, MFG_LOOKUPS ML2, MFG_LOOKUPS ML3, MTL_TXN_SOURCE_TYPES MTST, BOM_DEPARTMENTS BM, MTL_SYSTEM_ITEMS_VL ISEG, MTL_TRANSACTION_REASONS MTR, HR_ORG_UNITS_NO_JOIN HRC, HR_ALL_ORGANIZATION_UNITS_TL HRT, CST_COST_GROUPS CG, CST_COST_GROUPS CG2, WMS_LICENSE_PLATE_NUMBERS LPN1, WMS_LICENSE_PLATE_NUMBERS LPN2, WMS_LICENSE_PLATE_NUMBERS LPN3 WHERE M.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID AND M.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID AND ML1.LOOKUP_TYPE= 'MTL_TRANSACTION_ACTION' AND ML1.LOOKUP_CODE = M.TRANSACTION_ACTION_ID AND ML2.LOOKUP_TYPE = 'INV_YES_NO_ERROR' AND ML2.LOOKUP_CODE =DECODE (M.COSTED_FLAG, NULL, 1, 'Y', 1, 'N', 2, 'E', 3) AND ML3.LOOKUP_TYPE = 'INV_YES_NO_ERROR_NA' AND ML3.LOOKUP_CODE =DECODE (M.PM_COST_COLLECTED, NULL, DECODE(M.PM_COST_COLLECTOR_GROUP_ID,NULL,4,1), 'Y', 1, 'N', 2, 'E', 3) AND M.REASON_ID = MTR.REASON_ID(+) AND M.DEPARTMENT_ID = BM.DEPARTMENT_ID(+) AND M.ORGANIZATION_ID = BM.ORGANIZATION_ID(+) AND HRC.ORGANIZATION_ID = HRT.ORGANIZATION_ID(+) AND DECODE(HRT.ORGANIZATION_ID, NULL, '1', HRT.LANGUAGE) = DECODE(HRT.ORGANIZATION_ID, NULL, '1', USERENV('LANG')) AND M.TRANSFER_ORGANIZATION_ID = HRC.ORGANIZATION_ID(+) AND M.INVENTORY_ITEM_ID = ISEG.INVENTORY_ITEM_ID AND M.ORGANIZATION_ID = ISEG.ORGANIZATION_ID AND M.COST_GROUP_ID = CG.COST_GROUP_ID(+) AND M.TRANSFER_COST_GROUP_ID =CG2.COST_GROUP_ID(+) AND M.LPN_ID = LPN1.LPN_ID(+) AND M.TRANSFER_LPN_ID = LPN2.LPN_ID(+) AND M.CONTENT_LPN_ID = LPN3.LPN_ID(+)

View Text - HTML Formatted

SELECT M.ROWID ROW_ID
, M.TRANSACTION_ID
, M.LAST_UPDATE_DATE
, M.LAST_UPDATED_BY
, M.CREATION_DATE
, M.CREATED_BY
, M.LAST_UPDATE_LOGIN
, M.REQUEST_ID
, M.PROGRAM_APPLICATION_ID
, M.PROGRAM_ID
, M.PROGRAM_UPDATE_DATE
, M.INVENTORY_ITEM_ID
, M.REVISION
, M.ORGANIZATION_ID
, M.SUBINVENTORY_CODE SUBINVENTORY
, M.LOCATOR_ID
, M.TRANSACTION_TYPE_ID
, MTT.TRANSACTION_TYPE_NAME
, M.TRANSACTION_ACTION_ID
, ML1.MEANING TRANSACTION_ACTION
, M.COSTED_FLAG
, ML2.MEANING COSTED_FLAG_1
, ML2.LOOKUP_CODE COSTED_LOOKUP_CODE
, M.PM_COST_COLLECTED
, ML3.MEANING PM_COST_COLLECTED_1
, ML3.LOOKUP_CODE PM_COST_COLLECTED_LOOKUP_CODE
, M.TRANSACTION_SOURCE_TYPE_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, M.TRANSACTION_SOURCE_ID
, M.TRANSACTION_SOURCE_NAME TRANSACTION_SOURCE_NAME_DB
, M.TRANSACTION_QUANTITY
, M.TRANSACTION_UOM
, M.PRIMARY_QUANTITY
, M.TRANSACTION_DATE
, M.TRANSACTION_REFERENCE
, MTR.REASON_NAME
, M.EMPLOYEE_CODE
, M.DEPARTMENT_ID
, BM.DEPARTMENT_CODE
, M.OPERATION_SEQ_NUM
, M.MASTER_SCHEDULE_UPDATE_CODE
, M.RECEIVING_DOCUMENT
, M.TRANSFER_TRANSACTION_ID
, M.TRANSACTION_SET_ID
, M.RCV_TRANSACTION_ID
, M.MOVE_TRANSACTION_ID
, M.COMPLETION_TRANSACTION_ID
, M.SOURCE_CODE
, M.SOURCE_LINE_ID
, M.VENDOR_LOT_NUMBER
, M.TRANSFER_ORGANIZATION_ID
, HRT.NAME TRANSFER_ORGANIZATION_NAME
, M.TRANSFER_SUBINVENTORY
, M.TRANSFER_LOCATOR_ID
, M.SHIPMENT_NUMBER
, M.WAYBILL_AIRBILL
, M.FREIGHT_CODE
, M.NUMBER_OF_CONTAINERS
, M.ATTRIBUTE_CATEGORY
, M.ATTRIBUTE1
, M.ATTRIBUTE2
, M.ATTRIBUTE3
, M.ATTRIBUTE4
, M.ATTRIBUTE5
, M.ATTRIBUTE6
, M.ATTRIBUTE7
, M.ATTRIBUTE8
, M.ATTRIBUTE9
, M.ATTRIBUTE10
, M.ATTRIBUTE11
, M.ATTRIBUTE12
, M.ATTRIBUTE13
, M.ATTRIBUTE14
, M.ATTRIBUTE15
, ISEG.CONCATENATED_SEGMENTS ITEM_ORD
, ISEG.PRIMARY_UOM_CODE
, ISEG.DESCRIPTION
, M.SOURCE_PROJECT_ID
, M.SOURCE_TASK_ID
, M.PROJECT_ID
, M.TO_PROJECT_ID
, M.TASK_ID
, M.TO_TASK_ID
, M.EXPENDITURE_TYPE
, M.PA_EXPENDITURE_ORG_ID
, M.ERROR_CODE
, M.ERROR_EXPLANATION
, M.OVERCOMPLETION_TRANSACTION_QTY
, M.OVERCOMPLETION_TRANSACTION_ID
, M.PM_COST_COLLECTOR_GROUP_ID
, M.MOVE_ORDER_LINE_ID
, M.PICK_SLIP_NUMBER
, M.TASK_GROUP_ID
, M.COST_GROUP_ID
, M.TRANSFER_COST_GROUP_ID
, M.LPN_ID
, M.TRANSFER_LPN_ID
, LPN1.LICENSE_PLATE_NUMBER LPN
, LPN2.LICENSE_PLATE_NUMBER TRANSFER_LPN
, CG.COST_GROUP COST_GROUP_NAME
, CG2.COST_GROUP TRANSFER_COST_GROUP_NAME
, M.PICK_STRATEGY_ID
, M.PICK_RULE_ID
, M.PUT_AWAY_STRATEGY_ID
, M.PUT_AWAY_RULE_ID
, M.CONTENT_LPN_ID
, LPN3.LICENSE_PLATE_NUMBER CONTENT_LPN
FROM MTL_MATERIAL_TRANSACTIONS M
, MTL_TRANSACTION_TYPES MTT
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MTL_TXN_SOURCE_TYPES MTST
, BOM_DEPARTMENTS BM
, MTL_SYSTEM_ITEMS_VL ISEG
, MTL_TRANSACTION_REASONS MTR
, HR_ORG_UNITS_NO_JOIN HRC
, HR_ALL_ORGANIZATION_UNITS_TL HRT
, CST_COST_GROUPS CG
, CST_COST_GROUPS CG2
, WMS_LICENSE_PLATE_NUMBERS LPN1
, WMS_LICENSE_PLATE_NUMBERS LPN2
, WMS_LICENSE_PLATE_NUMBERS LPN3
WHERE M.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND M.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID
AND ML1.LOOKUP_TYPE= 'MTL_TRANSACTION_ACTION'
AND ML1.LOOKUP_CODE = M.TRANSACTION_ACTION_ID
AND ML2.LOOKUP_TYPE = 'INV_YES_NO_ERROR'
AND ML2.LOOKUP_CODE =DECODE (M.COSTED_FLAG
, NULL
, 1
, 'Y'
, 1
, 'N'
, 2
, 'E'
, 3)
AND ML3.LOOKUP_TYPE = 'INV_YES_NO_ERROR_NA'
AND ML3.LOOKUP_CODE =DECODE (M.PM_COST_COLLECTED
, NULL
, DECODE(M.PM_COST_COLLECTOR_GROUP_ID
, NULL
, 4
, 1)
, 'Y'
, 1
, 'N'
, 2
, 'E'
, 3)
AND M.REASON_ID = MTR.REASON_ID(+)
AND M.DEPARTMENT_ID = BM.DEPARTMENT_ID(+)
AND M.ORGANIZATION_ID = BM.ORGANIZATION_ID(+)
AND HRC.ORGANIZATION_ID = HRT.ORGANIZATION_ID(+)
AND DECODE(HRT.ORGANIZATION_ID
, NULL
, '1'
, HRT.LANGUAGE) = DECODE(HRT.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND M.TRANSFER_ORGANIZATION_ID = HRC.ORGANIZATION_ID(+)
AND M.INVENTORY_ITEM_ID = ISEG.INVENTORY_ITEM_ID
AND M.ORGANIZATION_ID = ISEG.ORGANIZATION_ID
AND M.COST_GROUP_ID = CG.COST_GROUP_ID(+)
AND M.TRANSFER_COST_GROUP_ID =CG2.COST_GROUP_ID(+)
AND M.LPN_ID = LPN1.LPN_ID(+)
AND M.TRANSFER_LPN_ID = LPN2.LPN_ID(+)
AND M.CONTENT_LPN_ID = LPN3.LPN_ID(+)