VIEW: APPS.WSH_EXCEPTIONS_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, xc_df.exception_type_m, 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, tr.stop_location_id, tr.stop_sequence_number, wsl.ui_location_code, xc.batch_id, xc.delivery_id delivery_id FROM wsh_exceptions xc, wsh_trip_stops tr, wsh_exception_definitions_v xc_df, wsh_locations wlo, wsh_locations wlal, wsh_locations wsl, hr_locations_all hla, hr_organization_information hoi, mtl_parameters mp WHERE xc.trip_stop_id = tr.stop_id (+) and 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 tr.stop_location_id = wsl.wsh_location_id(+) and wlo.source_location_id = hla.location_id (+) And hla.inventory_organization_id = hoi.organization_id (+) and hoi.ORG_INFORMATION1 (+) = 'INV' AND hoi.ORG_INFORMATION2 (+) = 'Y' AND hoi.ORG_INFORMATION_CONTEXT (+) = 'CLASS' 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') )

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
, XC_DF.EXCEPTION_TYPE_M
, 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
, TR.STOP_LOCATION_ID
, TR.STOP_SEQUENCE_NUMBER
, WSL.UI_LOCATION_CODE
, XC.BATCH_ID
, XC.DELIVERY_ID DELIVERY_ID
FROM WSH_EXCEPTIONS XC
, WSH_TRIP_STOPS TR
, WSH_EXCEPTION_DEFINITIONS_V XC_DF
, WSH_LOCATIONS WLO
, WSH_LOCATIONS WLAL
, WSH_LOCATIONS WSL
, HR_LOCATIONS_ALL HLA
, HR_ORGANIZATION_INFORMATION HOI
, MTL_PARAMETERS MP
WHERE XC.TRIP_STOP_ID = TR.STOP_ID (+)
AND 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 TR.STOP_LOCATION_ID = WSL.WSH_LOCATION_ID(+)
AND WLO.SOURCE_LOCATION_ID = HLA.LOCATION_ID (+)
AND HLA.INVENTORY_ORGANIZATION_ID = HOI.ORGANIZATION_ID (+)
AND HOI.ORG_INFORMATION1 (+) = 'INV'
AND HOI.ORG_INFORMATION2 (+) = 'Y'
AND HOI.ORG_INFORMATION_CONTEXT (+) = 'CLASS'
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') )