VIEW: APPS.MTL_PENDING_TXN_DETAILS_V

Source

View Text - Preformatted

SELECT msn.gen_object_id , 2 , mmtt.transaction_temp_id , mmtt.transaction_date , mp.organization_code , mmtt.organization_id , mmtt.subinventory_code , mmtt.locator_id , mtst.transaction_source_type_name , DECODE( mmtt.transaction_source_type_id ,13 ,mmtt.transaction_source_name , Inv_Object_Genealogy.getSource ( mmtt.organization_id , mmtt.transaction_source_type_id , mmtt.transaction_source_id ) ) , Inv_Object_Genealogy.getTradingPartner ( mmtt.organization_id , mmtt.transaction_source_type_id , mmtt.transaction_source_id , mmtt.trx_source_line_id , mmtt.transfer_organization ) , substr(ptv.project_name, 1, 30) , ptv.task_name , 1 , mmtt.transaction_uom , 1 , null , null , mmtt.created_by , mmtt.source_code , mmtt.source_line_id , mmtt.transaction_type_id , wlpn1.lpn_id , wlpn2.lpn_id , wlpn3.lpn_id , wlpn1.license_plate_number , wlpn2.license_plate_number , wlpn3.license_plate_number , mmtt.transaction_temp_id , null , null , mmtt.transaction_status FROM mtl_unit_transactions msnt , mtl_serial_numbers msn , mtl_material_transactions_temp mmtt , mtl_txn_source_types mtst , mtl_parameters mp , pjm_tasks_v ptv , wms_license_plate_numbers wlpn1 , wms_license_plate_numbers wlpn2 , wms_license_plate_numbers wlpn3 WHERE msn.serial_number = msnt.serial_number AND msn.inventory_item_id = msnt.inventory_item_id AND msnt.transaction_id = mmtt.transaction_temp_id AND mmtt.transaction_source_type_id = mtst.transaction_source_type_id AND mmtt.transaction_action_id not in (24,30) AND mmtt.organization_id = mp.organization_id AND mmtt.project_id = ptv.project_id(+) AND mmtt.task_id = ptv.task_id(+) AND mmtt.lpn_id = wlpn1.lpn_id(+) AND mmtt.transfer_lpn_id = wlpn2.lpn_id(+) AND mmtt.content_lpn_id = wlpn3.lpn_id(+) UNION ALL (SELECT mln.gen_object_id , 1 , mmtt.transaction_temp_id , mmtt.transaction_date , mp.organization_code , mmtt.organization_id , mmtt.subinventory_code , mmtt.locator_id , mtst.transaction_source_type_name , DECODE(mmtt.transaction_source_type_id ,13 ,mmtt.transaction_source_name , Inv_Object_Genealogy.getSource ( mmtt.organization_id , mmtt.transaction_source_type_id , mmtt.transaction_source_id ) ) , Inv_Object_Genealogy.getTradingPartner (mmtt.organization_id , mmtt.transaction_source_type_id , mmtt.transaction_source_id , mmtt.trx_source_line_id , mmtt.transfer_organization ) , substr(ptv.project_name, 1, 30) , ptv.task_name , mtlt.transaction_quantity , mmtt.transaction_uom , mtlt.primary_quantity , mtlt.secondary_quantity , mtlt.grade_code , mmtt.created_by , mmtt.source_code , mmtt.source_line_id , mmtt.transaction_type_id , wlpn1.lpn_id , wlpn2.lpn_id , wlpn3.lpn_id , wlpn1.license_plate_number , wlpn2.license_plate_number , wlpn3.license_plate_number , mmtt.transaction_temp_id , mmtt.vendor_lot_number , mtlt.lot_number , mmtt.transaction_status FROM mtl_transaction_lots_temp mtlt , mtl_lot_numbers mln , mtl_material_transactions_temp mmtt , mtl_txn_source_types mtst , mtl_parameters mp , pjm_tasks_v ptv , wms_license_plate_numbers wlpn1 , wms_license_plate_numbers wlpn2 , wms_license_plate_numbers wlpn3 WHERE mln.lot_number = mtlt.lot_number AND mln.inventory_item_id = mmtt.inventory_item_id AND mtlt.transaction_temp_id = mmtt.transaction_temp_id AND mmtt.transaction_source_type_id = mtst.transaction_source_type_id AND mmtt.transaction_action_id not in (24,30) AND mmtt.organization_id = mp.organization_id AND mmtt.project_id = ptv.project_id(+) AND mmtt.task_id = ptv.task_id(+) AND mmtt.lpn_id = wlpn1.lpn_id(+) AND mmtt.transfer_lpn_id = wlpn2.lpn_id(+) AND mmtt.content_lpn_id = wlpn3.lpn_id(+)) UNION ALL (SELECT msn.gen_object_id , 2 , mmtt.transaction_temp_id , mmtt.transaction_date , mp.organization_code , mmtt.organization_id , mmtt.subinventory_code , mmtt.locator_id , mtst.transaction_source_type_name , DECODE(mmtt.transaction_source_type_id ,13 ,mmtt.transaction_source_name , Inv_Object_Genealogy.getSource ( mmtt.organization_id , mmtt.transaction_source_type_id , mmtt.transaction_source_id ) ) , Inv_Object_Genealogy.getTradingPartner (mmtt.organization_id, mmtt.transaction_source_type_id, mmtt.transaction_source_id, mmtt.trx_source_line_id, mmtt.transfer_organization ) , substr(ptv.project_name, 1, 30) , ptv.task_name , 1 , mmtt.transaction_uom , 1 , null , null , mmtt.created_by , mmtt.source_code , mmtt.source_line_id , mmtt.transaction_type_id , wlpn1.lpn_id , wlpn2.lpn_id , wlpn3.lpn_id , wlpn1.license_plate_number , wlpn2.license_plate_number , wlpn3.license_plate_number , mmtt.transaction_temp_id , mmtt.vendor_lot_number , mtlt.lot_number , mmtt.transaction_status FROM mtl_unit_transactions msnt , mtl_serial_numbers msn , mtl_material_transactions_temp mmtt , mtl_txn_source_types mtst , mtl_parameters mp , pjm_tasks_v ptv , wms_license_plate_numbers wlpn1 , wms_license_plate_numbers wlpn2 , wms_license_plate_numbers wlpn3 , mtl_transaction_lots_temp mtlt WHERE msn.serial_number = msnt.serial_number AND mtlt.serial_transaction_temp_id = msnt.transaction_id AND mmtt.transaction_temp_id = mtlt.transaction_temp_id AND msn.inventory_item_id = msnt.inventory_item_id AND mmtt.transaction_source_type_id = mtst.transaction_source_type_id AND mmtt.transaction_action_id not in (24, 30) AND mmtt.organization_id = mp.organization_id AND mmtt.project_id = ptv.project_id(+) AND mmtt.task_id = ptv.task_id(+) AND mmtt.lpn_id = wlpn1.lpn_id(+) AND mmtt.transfer_lpn_id = wlpn2.lpn_id(+) AND mmtt.content_lpn_id = wlpn3.lpn_id(+))

View Text - HTML Formatted

SELECT MSN.GEN_OBJECT_ID
, 2
, MMTT.TRANSACTION_TEMP_ID
, MMTT.TRANSACTION_DATE
, MP.ORGANIZATION_CODE
, MMTT.ORGANIZATION_ID
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, DECODE( MMTT.TRANSACTION_SOURCE_TYPE_ID
, 13
, MMTT.TRANSACTION_SOURCE_NAME
, INV_OBJECT_GENEALOGY.GETSOURCE ( MMTT.ORGANIZATION_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_SOURCE_ID ) )
, INV_OBJECT_GENEALOGY.GETTRADINGPARTNER ( MMTT.ORGANIZATION_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_SOURCE_ID
, MMTT.TRX_SOURCE_LINE_ID
, MMTT.TRANSFER_ORGANIZATION )
, SUBSTR(PTV.PROJECT_NAME
, 1
, 30)
, PTV.TASK_NAME
, 1
, MMTT.TRANSACTION_UOM
, 1
, NULL
, NULL
, MMTT.CREATED_BY
, MMTT.SOURCE_CODE
, MMTT.SOURCE_LINE_ID
, MMTT.TRANSACTION_TYPE_ID
, WLPN1.LPN_ID
, WLPN2.LPN_ID
, WLPN3.LPN_ID
, WLPN1.LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER
, WLPN3.LICENSE_PLATE_NUMBER
, MMTT.TRANSACTION_TEMP_ID
, NULL
, NULL
, MMTT.TRANSACTION_STATUS
FROM MTL_UNIT_TRANSACTIONS MSNT
, MTL_SERIAL_NUMBERS MSN
, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_TXN_SOURCE_TYPES MTST
, MTL_PARAMETERS MP
, PJM_TASKS_V PTV
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
, WMS_LICENSE_PLATE_NUMBERS WLPN3
WHERE MSN.SERIAL_NUMBER = MSNT.SERIAL_NUMBER
AND MSN.INVENTORY_ITEM_ID = MSNT.INVENTORY_ITEM_ID
AND MSNT.TRANSACTION_ID = MMTT.TRANSACTION_TEMP_ID
AND MMTT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID
AND MMTT.TRANSACTION_ACTION_ID NOT IN (24
, 30)
AND MMTT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMTT.PROJECT_ID = PTV.PROJECT_ID(+)
AND MMTT.TASK_ID = PTV.TASK_ID(+)
AND MMTT.LPN_ID = WLPN1.LPN_ID(+)
AND MMTT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)
AND MMTT.CONTENT_LPN_ID = WLPN3.LPN_ID(+) UNION ALL (SELECT MLN.GEN_OBJECT_ID
, 1
, MMTT.TRANSACTION_TEMP_ID
, MMTT.TRANSACTION_DATE
, MP.ORGANIZATION_CODE
, MMTT.ORGANIZATION_ID
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, DECODE(MMTT.TRANSACTION_SOURCE_TYPE_ID
, 13
, MMTT.TRANSACTION_SOURCE_NAME
, INV_OBJECT_GENEALOGY.GETSOURCE ( MMTT.ORGANIZATION_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_SOURCE_ID ) )
, INV_OBJECT_GENEALOGY.GETTRADINGPARTNER (MMTT.ORGANIZATION_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_SOURCE_ID
, MMTT.TRX_SOURCE_LINE_ID
, MMTT.TRANSFER_ORGANIZATION )
, SUBSTR(PTV.PROJECT_NAME
, 1
, 30)
, PTV.TASK_NAME
, MTLT.TRANSACTION_QUANTITY
, MMTT.TRANSACTION_UOM
, MTLT.PRIMARY_QUANTITY
, MTLT.SECONDARY_QUANTITY
, MTLT.GRADE_CODE
, MMTT.CREATED_BY
, MMTT.SOURCE_CODE
, MMTT.SOURCE_LINE_ID
, MMTT.TRANSACTION_TYPE_ID
, WLPN1.LPN_ID
, WLPN2.LPN_ID
, WLPN3.LPN_ID
, WLPN1.LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER
, WLPN3.LICENSE_PLATE_NUMBER
, MMTT.TRANSACTION_TEMP_ID
, MMTT.VENDOR_LOT_NUMBER
, MTLT.LOT_NUMBER
, MMTT.TRANSACTION_STATUS
FROM MTL_TRANSACTION_LOTS_TEMP MTLT
, MTL_LOT_NUMBERS MLN
, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_TXN_SOURCE_TYPES MTST
, MTL_PARAMETERS MP
, PJM_TASKS_V PTV
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
, WMS_LICENSE_PLATE_NUMBERS WLPN3
WHERE MLN.LOT_NUMBER = MTLT.LOT_NUMBER
AND MLN.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MTLT.TRANSACTION_TEMP_ID = MMTT.TRANSACTION_TEMP_ID
AND MMTT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID
AND MMTT.TRANSACTION_ACTION_ID NOT IN (24
, 30)
AND MMTT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMTT.PROJECT_ID = PTV.PROJECT_ID(+)
AND MMTT.TASK_ID = PTV.TASK_ID(+)
AND MMTT.LPN_ID = WLPN1.LPN_ID(+)
AND MMTT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)
AND MMTT.CONTENT_LPN_ID = WLPN3.LPN_ID(+)) UNION ALL (SELECT MSN.GEN_OBJECT_ID
, 2
, MMTT.TRANSACTION_TEMP_ID
, MMTT.TRANSACTION_DATE
, MP.ORGANIZATION_CODE
, MMTT.ORGANIZATION_ID
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, DECODE(MMTT.TRANSACTION_SOURCE_TYPE_ID
, 13
, MMTT.TRANSACTION_SOURCE_NAME
, INV_OBJECT_GENEALOGY.GETSOURCE ( MMTT.ORGANIZATION_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_SOURCE_ID ) )
, INV_OBJECT_GENEALOGY.GETTRADINGPARTNER (MMTT.ORGANIZATION_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_SOURCE_ID
, MMTT.TRX_SOURCE_LINE_ID
, MMTT.TRANSFER_ORGANIZATION )
, SUBSTR(PTV.PROJECT_NAME
, 1
, 30)
, PTV.TASK_NAME
, 1
, MMTT.TRANSACTION_UOM
, 1
, NULL
, NULL
, MMTT.CREATED_BY
, MMTT.SOURCE_CODE
, MMTT.SOURCE_LINE_ID
, MMTT.TRANSACTION_TYPE_ID
, WLPN1.LPN_ID
, WLPN2.LPN_ID
, WLPN3.LPN_ID
, WLPN1.LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER
, WLPN3.LICENSE_PLATE_NUMBER
, MMTT.TRANSACTION_TEMP_ID
, MMTT.VENDOR_LOT_NUMBER
, MTLT.LOT_NUMBER
, MMTT.TRANSACTION_STATUS
FROM MTL_UNIT_TRANSACTIONS MSNT
, MTL_SERIAL_NUMBERS MSN
, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_TXN_SOURCE_TYPES MTST
, MTL_PARAMETERS MP
, PJM_TASKS_V PTV
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
, WMS_LICENSE_PLATE_NUMBERS WLPN3
, MTL_TRANSACTION_LOTS_TEMP MTLT
WHERE MSN.SERIAL_NUMBER = MSNT.SERIAL_NUMBER
AND MTLT.SERIAL_TRANSACTION_TEMP_ID = MSNT.TRANSACTION_ID
AND MMTT.TRANSACTION_TEMP_ID = MTLT.TRANSACTION_TEMP_ID
AND MSN.INVENTORY_ITEM_ID = MSNT.INVENTORY_ITEM_ID
AND MMTT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID
AND MMTT.TRANSACTION_ACTION_ID NOT IN (24
, 30)
AND MMTT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMTT.PROJECT_ID = PTV.PROJECT_ID(+)
AND MMTT.TASK_ID = PTV.TASK_ID(+)
AND MMTT.LPN_ID = WLPN1.LPN_ID(+)
AND MMTT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)
AND MMTT.CONTENT_LPN_ID = WLPN3.LPN_ID(+))