VIEW: APPS.WSH_EXCEPTIONS_DLVY_V

Source

View Text - Preformatted

SELECT xc.rowid, xc.exception_id, xc.logged_at_location_id, wlal.ui_location_code, xc.logging_entity, WSH_XC_UTIL.Get_Lookup_Meaning(xc.logging_entity, 'LOGGING_ENTITY'), xc.logging_entity_id, xc.exception_name, xc_df.description, flv.meaning, xc.exception_location_id, wlo.ui_location_code, mp.organization_id, mp.organization_code, xc.message, decode(xc.severity, 'HIGH', 'ERROR', 'MEDIUM', 'WARNING', 'LOW', 'INFO', xc.severity) severity, WSH_XC_UTIL.Get_Lookup_Meaning(decode(xc.severity, 'HIGH', 'ERROR', 'MEDIUM', 'WARNING', 'LOW', 'INFO', xc.severity), 'EXCEPTION_BEHAVIOR'), xc.manually_logged, decode(xc.status, 'MANUAL', 'OPEN', 'LOGGED', 'OPEN', 'IN_PROCESS', 'OPEN', 'ERROR', 'OPEN', 'NOT_HANDLED', 'NO_ACTION_REQUIRED', NULL, 'NO_ACTION_REQUIRED', xc.status) status, xc.trip_id, xc.trip_name, xc.trip_stop_id, xc.delivery_id assigned_delivery_id, xc.delivery_name, xc.delivery_detail_id, xc.delivery_assignment_id, xc.container_name, xc.inventory_item_id, xc.lot_number, xc.revision, xc.serial_number, xc.unit_of_measure, xc.quantity, xc.subinventory, xc.locator_id, xc.arrival_date, xc.departure_date, xc.error_message, xc.attribute_category, xc.attribute1, xc.attribute2, xc.attribute3, xc.attribute4, xc.attribute5, xc.attribute6, xc.attribute7, xc.attribute8, xc.attribute9, xc.attribute10, xc.attribute11, xc.attribute12, xc.attribute13, xc.attribute14, xc.attribute15, xc.creation_date, xc.created_by, xc.last_update_date, xc.last_updated_by, xc.last_update_login, xc.request_id, xc.program_application_id, xc.program_id, xc.program_update_date, xc.quantity2, xc.unit_of_measure2, null, null, null, xc.batch_id, xc.delivery_id delivery_id FROM wsh_exceptions xc, wsh_exception_definitions_tl xc_df, wsh_exception_definitions_b xb, fnd_lookup_values flv, wsh_locations wlo, wsh_locations wlal, hr_locations_all hla, mtl_parameters mp WHERE xc.exception_name = xc_df.exception_name (+) and xc.exception_location_id = wlo.wsh_location_id (+) and xc.logged_at_location_id = wlal.wsh_location_id (+) and wlo.source_location_id = hla.location_id (+) And hla.inventory_organization_id = mp.organization_id (+) and ( hla.inventory_organization_id is null or (hla.inventory_organization_id is not null and wlo.location_source_code='HR') ) and xc_df.exception_definition_id = xb.exception_definition_id and xc_df.LANGUAGE = userenv('LANG') and xb.exception_type = flv.lookup_code and flv.lookup_type = 'EXCEPTION_TYPE' and flv.Language = userenv('LANG') UNION SELECT xc.rowid, xc.exception_id, xc.logged_at_location_id, wlal.ui_location_code, xc.logging_entity, WSH_XC_UTIL.Get_Lookup_Meaning(xc.logging_entity, 'LOGGING_ENTITY'), xc.logging_entity_id, xc.exception_name, xc_df.description, flv.meaning, xc.exception_location_id, wlo.ui_location_code, mp.organization_id, mp.organization_code, xc.message, decode(xc.severity, 'HIGH', 'ERROR', 'MEDIUM', 'WARNING', 'LOW', 'INFO', xc.severity) severity, WSH_XC_UTIL.Get_Lookup_Meaning(decode(xc.severity, 'HIGH', 'ERROR', 'MEDIUM', 'WARNING', 'LOW', 'INFO', xc.severity), 'EXCEPTION_BEHAVIOR'), xc.manually_logged, decode(xc.status, 'MANUAL', 'OPEN', 'LOGGED', 'OPEN', 'IN_PROCESS', 'OPEN', 'ERROR', 'OPEN', 'NOT_HANDLED', 'NO_ACTION_REQUIRED', NULL, 'NO_ACTION_REQUIRED', xc.status) status, xc.trip_id, xc.trip_name, xc.trip_stop_id, wda.delivery_id assigned_delivery_id, xc.delivery_name, xc.delivery_detail_id, xc.delivery_assignment_id, xc.container_name, xc.inventory_item_id, xc.lot_number, xc.revision, xc.serial_number, xc.unit_of_measure, xc.quantity, xc.subinventory, xc.locator_id, xc.arrival_date, xc.departure_date, xc.error_message, xc.attribute_category, xc.attribute1, xc.attribute2, xc.attribute3, xc.attribute4, xc.attribute5, xc.attribute6, xc.attribute7, xc.attribute8, xc.attribute9, xc.attribute10, xc.attribute11, xc.attribute12, xc.attribute13, xc.attribute14, xc.attribute15, xc.creation_date, xc.created_by, xc.last_update_date, xc.last_updated_by, xc.last_update_login, xc.request_id, xc.program_application_id, xc.program_id, xc.program_update_date, xc.quantity2, xc.unit_of_measure2, null, null, null, xc.batch_id, xc.delivery_id delivery_id FROM wsh_exceptions xc, wsh_exception_definitions_tl xc_df, wsh_exception_definitions_b xb, fnd_lookup_values flv, wsh_locations wlo, wsh_locations wlal, hr_locations_all hla, mtl_parameters mp, wsh_delivery_assignments_v wda WHERE xc.exception_name = xc_df.exception_name (+) and xc.exception_location_id = wlo.wsh_location_id(+) and xc.logged_at_location_id = wlal.wsh_location_id(+) and wlo.source_location_id = hla.location_id (+) And hla.inventory_organization_id = mp.organization_id (+) and ( hla.inventory_organization_id is null or (hla.inventory_organization_id is not null and wlo.location_source_code='HR') ) and xc.delivery_detail_id = wda.delivery_detail_id and xc_df.exception_definition_id = xb.exception_definition_id and xc_df.LANGUAGE = userenv('LANG') and xb.exception_type = flv.lookup_code and flv.lookup_type = 'EXCEPTION_TYPE' and flv.Language = userenv('LANG')

View Text - HTML Formatted

SELECT XC.ROWID
, XC.EXCEPTION_ID
, XC.LOGGED_AT_LOCATION_ID
, WLAL.UI_LOCATION_CODE
, XC.LOGGING_ENTITY
, WSH_XC_UTIL.GET_LOOKUP_MEANING(XC.LOGGING_ENTITY
, 'LOGGING_ENTITY')
, XC.LOGGING_ENTITY_ID
, XC.EXCEPTION_NAME
, XC_DF.DESCRIPTION
, FLV.MEANING
, XC.EXCEPTION_LOCATION_ID
, WLO.UI_LOCATION_CODE
, MP.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, XC.MESSAGE
, DECODE(XC.SEVERITY
, 'HIGH'
, 'ERROR'
, 'MEDIUM'
, 'WARNING'
, 'LOW'
, 'INFO'
, XC.SEVERITY) SEVERITY
, WSH_XC_UTIL.GET_LOOKUP_MEANING(DECODE(XC.SEVERITY
, 'HIGH'
, 'ERROR'
, 'MEDIUM'
, 'WARNING'
, 'LOW'
, 'INFO'
, XC.SEVERITY)
, 'EXCEPTION_BEHAVIOR')
, XC.MANUALLY_LOGGED
, DECODE(XC.STATUS
, 'MANUAL'
, 'OPEN'
, 'LOGGED'
, 'OPEN'
, 'IN_PROCESS'
, 'OPEN'
, 'ERROR'
, 'OPEN'
, 'NOT_HANDLED'
, 'NO_ACTION_REQUIRED'
, NULL
, 'NO_ACTION_REQUIRED'
, XC.STATUS) STATUS
, XC.TRIP_ID
, XC.TRIP_NAME
, XC.TRIP_STOP_ID
, XC.DELIVERY_ID ASSIGNED_DELIVERY_ID
, XC.DELIVERY_NAME
, XC.DELIVERY_DETAIL_ID
, XC.DELIVERY_ASSIGNMENT_ID
, XC.CONTAINER_NAME
, XC.INVENTORY_ITEM_ID
, XC.LOT_NUMBER
, XC.REVISION
, XC.SERIAL_NUMBER
, XC.UNIT_OF_MEASURE
, XC.QUANTITY
, XC.SUBINVENTORY
, XC.LOCATOR_ID
, XC.ARRIVAL_DATE
, XC.DEPARTURE_DATE
, XC.ERROR_MESSAGE
, XC.ATTRIBUTE_CATEGORY
, XC.ATTRIBUTE1
, XC.ATTRIBUTE2
, XC.ATTRIBUTE3
, XC.ATTRIBUTE4
, XC.ATTRIBUTE5
, XC.ATTRIBUTE6
, XC.ATTRIBUTE7
, XC.ATTRIBUTE8
, XC.ATTRIBUTE9
, XC.ATTRIBUTE10
, XC.ATTRIBUTE11
, XC.ATTRIBUTE12
, XC.ATTRIBUTE13
, XC.ATTRIBUTE14
, XC.ATTRIBUTE15
, XC.CREATION_DATE
, XC.CREATED_BY
, XC.LAST_UPDATE_DATE
, XC.LAST_UPDATED_BY
, XC.LAST_UPDATE_LOGIN
, XC.REQUEST_ID
, XC.PROGRAM_APPLICATION_ID
, XC.PROGRAM_ID
, XC.PROGRAM_UPDATE_DATE
, XC.QUANTITY2
, XC.UNIT_OF_MEASURE2
, NULL
, NULL
, NULL
, XC.BATCH_ID
, XC.DELIVERY_ID DELIVERY_ID
FROM WSH_EXCEPTIONS XC
, WSH_EXCEPTION_DEFINITIONS_TL XC_DF
, WSH_EXCEPTION_DEFINITIONS_B XB
, FND_LOOKUP_VALUES FLV
, WSH_LOCATIONS WLO
, WSH_LOCATIONS WLAL
, HR_LOCATIONS_ALL HLA
, MTL_PARAMETERS MP
WHERE XC.EXCEPTION_NAME = XC_DF.EXCEPTION_NAME (+)
AND XC.EXCEPTION_LOCATION_ID = WLO.WSH_LOCATION_ID (+)
AND XC.LOGGED_AT_LOCATION_ID = WLAL.WSH_LOCATION_ID (+)
AND WLO.SOURCE_LOCATION_ID = HLA.LOCATION_ID (+)
AND HLA.INVENTORY_ORGANIZATION_ID = MP.ORGANIZATION_ID (+)
AND ( HLA.INVENTORY_ORGANIZATION_ID IS NULL OR (HLA.INVENTORY_ORGANIZATION_ID IS NOT NULL
AND WLO.LOCATION_SOURCE_CODE='HR') )
AND XC_DF.EXCEPTION_DEFINITION_ID = XB.EXCEPTION_DEFINITION_ID
AND XC_DF.LANGUAGE = USERENV('LANG')
AND XB.EXCEPTION_TYPE = FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'EXCEPTION_TYPE'
AND FLV.LANGUAGE = USERENV('LANG') UNION SELECT XC.ROWID
, XC.EXCEPTION_ID
, XC.LOGGED_AT_LOCATION_ID
, WLAL.UI_LOCATION_CODE
, XC.LOGGING_ENTITY
, WSH_XC_UTIL.GET_LOOKUP_MEANING(XC.LOGGING_ENTITY
, 'LOGGING_ENTITY')
, XC.LOGGING_ENTITY_ID
, XC.EXCEPTION_NAME
, XC_DF.DESCRIPTION
, FLV.MEANING
, XC.EXCEPTION_LOCATION_ID
, WLO.UI_LOCATION_CODE
, MP.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, XC.MESSAGE
, DECODE(XC.SEVERITY
, 'HIGH'
, 'ERROR'
, 'MEDIUM'
, 'WARNING'
, 'LOW'
, 'INFO'
, XC.SEVERITY) SEVERITY
, WSH_XC_UTIL.GET_LOOKUP_MEANING(DECODE(XC.SEVERITY
, 'HIGH'
, 'ERROR'
, 'MEDIUM'
, 'WARNING'
, 'LOW'
, 'INFO'
, XC.SEVERITY)
, 'EXCEPTION_BEHAVIOR')
, XC.MANUALLY_LOGGED
, DECODE(XC.STATUS
, 'MANUAL'
, 'OPEN'
, 'LOGGED'
, 'OPEN'
, 'IN_PROCESS'
, 'OPEN'
, 'ERROR'
, 'OPEN'
, 'NOT_HANDLED'
, 'NO_ACTION_REQUIRED'
, NULL
, 'NO_ACTION_REQUIRED'
, XC.STATUS) STATUS
, XC.TRIP_ID
, XC.TRIP_NAME
, XC.TRIP_STOP_ID
, WDA.DELIVERY_ID ASSIGNED_DELIVERY_ID
, XC.DELIVERY_NAME
, XC.DELIVERY_DETAIL_ID
, XC.DELIVERY_ASSIGNMENT_ID
, XC.CONTAINER_NAME
, XC.INVENTORY_ITEM_ID
, XC.LOT_NUMBER
, XC.REVISION
, XC.SERIAL_NUMBER
, XC.UNIT_OF_MEASURE
, XC.QUANTITY
, XC.SUBINVENTORY
, XC.LOCATOR_ID
, XC.ARRIVAL_DATE
, XC.DEPARTURE_DATE
, XC.ERROR_MESSAGE
, XC.ATTRIBUTE_CATEGORY
, XC.ATTRIBUTE1
, XC.ATTRIBUTE2
, XC.ATTRIBUTE3
, XC.ATTRIBUTE4
, XC.ATTRIBUTE5
, XC.ATTRIBUTE6
, XC.ATTRIBUTE7
, XC.ATTRIBUTE8
, XC.ATTRIBUTE9
, XC.ATTRIBUTE10
, XC.ATTRIBUTE11
, XC.ATTRIBUTE12
, XC.ATTRIBUTE13
, XC.ATTRIBUTE14
, XC.ATTRIBUTE15
, XC.CREATION_DATE
, XC.CREATED_BY
, XC.LAST_UPDATE_DATE
, XC.LAST_UPDATED_BY
, XC.LAST_UPDATE_LOGIN
, XC.REQUEST_ID
, XC.PROGRAM_APPLICATION_ID
, XC.PROGRAM_ID
, XC.PROGRAM_UPDATE_DATE
, XC.QUANTITY2
, XC.UNIT_OF_MEASURE2
, NULL
, NULL
, NULL
, XC.BATCH_ID
, XC.DELIVERY_ID DELIVERY_ID
FROM WSH_EXCEPTIONS XC
, WSH_EXCEPTION_DEFINITIONS_TL XC_DF
, WSH_EXCEPTION_DEFINITIONS_B XB
, FND_LOOKUP_VALUES FLV
, WSH_LOCATIONS WLO
, WSH_LOCATIONS WLAL
, HR_LOCATIONS_ALL HLA
, MTL_PARAMETERS MP
, WSH_DELIVERY_ASSIGNMENTS_V WDA
WHERE XC.EXCEPTION_NAME = XC_DF.EXCEPTION_NAME (+)
AND XC.EXCEPTION_LOCATION_ID = WLO.WSH_LOCATION_ID(+)
AND XC.LOGGED_AT_LOCATION_ID = WLAL.WSH_LOCATION_ID(+)
AND WLO.SOURCE_LOCATION_ID = HLA.LOCATION_ID (+)
AND HLA.INVENTORY_ORGANIZATION_ID = MP.ORGANIZATION_ID (+)
AND ( HLA.INVENTORY_ORGANIZATION_ID IS NULL OR (HLA.INVENTORY_ORGANIZATION_ID IS NOT NULL
AND WLO.LOCATION_SOURCE_CODE='HR') )
AND XC.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND XC_DF.EXCEPTION_DEFINITION_ID = XB.EXCEPTION_DEFINITION_ID
AND XC_DF.LANGUAGE = USERENV('LANG')
AND XB.EXCEPTION_TYPE = FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'EXCEPTION_TYPE'
AND FLV.LANGUAGE = USERENV('LANG')