VIEW: APPS.MST_PQ_EXCEP_DETAILS_V

Source

View Text - Preformatted

SELECT MP.PLAN_ID, ME.EXCEPTION_ID, MED.EXCEPTION_DETAIL_ID, MED.EXCEPTION_TYPE, MED.STATUS EXCEPTION_STATUS, ME.EXCEPTION_COUNT TOTAL_EXCEPTIONS, TO_CHAR(NULL) MODE_OF_TRANSPORT, MED.DELIVERY_ID, MED.CONTINUOUS_MOVE_ID, MED.FACILITY_ID, MED.CARRIER_ID, MED.CUSTOMER_ID, MED.SUPPLIER_ID FROM MST_PLANS MP, MST_EXCEPTIONS ME, MST_EXCEPTION_DETAILS MED WHERE MP.PLAN_ID = ME.PLAN_ID AND ME.PLAN_ID = MED.PLAN_ID AND ME.EXCEPTION_ID = MED.EXCEPTION_ID AND MED.TRIP_ID1 IS NULL union ALL SELECT MP.PLAN_ID, ME.EXCEPTION_ID, MED.EXCEPTION_DETAIL_ID, MED.EXCEPTION_TYPE, MED.STATUS EXCEPTION_STATUS, ME.EXCEPTION_COUNT TOTAL_EXCEPTIONS, MT.MODE_OF_TRANSPORT, MED.DELIVERY_ID, MT.CONTINUOUS_MOVE_ID, FTE.FACILITY_ID, MT.CARRIER_ID, MED.CUSTOMER_ID, MED.SUPPLIER_ID FROM MST_PLANS MP, MST_EXCEPTIONS ME, MST_TRIPS MT, MST_TRIP_STOPS MTS, FTE_LOCATION_PARAMETERS FTE, MST_EXCEPTION_DETAILS MED WHERE MP.PLAN_ID = ME.PLAN_ID AND ME.PLAN_ID = MED.PLAN_ID AND ME.EXCEPTION_ID = MED.EXCEPTION_ID AND MT.PLAN_ID = MED.PLAN_ID AND MT.TRIP_ID = MED.TRIP_ID1 AND MT.PLAN_ID = MTS.PLAN_ID AND MT.TRIP_ID = MTS.TRIP_ID AND MTS.STOP_LOCATION_ID = FTE.LOCATION_ID UNION ALL SELECT MP.PLAN_ID, ME.EXCEPTION_ID, MED.EXCEPTION_DETAIL_ID, MED.EXCEPTION_TYPE, MED.STATUS EXCEPTION_STATUS, ME.EXCEPTION_COUNT TOTAL_EXCEPTIONS, TO_CHAR(NULL) MODE_OF_TRANSPORT, MD.DELIVERY_ID, MED.CONTINUOUS_MOVE_ID, MED.FACILITY_ID, MED.CARRIER_ID, MD.CUSTOMER_ID, MD.SUPPLIER_ID FROM MST_PLANS MP, MST_EXCEPTIONS ME, MST_DELIVERIES MD, MST_EXCEPTION_DETAILS MED WHERE MP.PLAN_ID = ME.PLAN_ID AND ME.PLAN_ID = MED.PLAN_ID AND ME.EXCEPTION_ID = MED.EXCEPTION_ID AND MED.PLAN_ID = MD.PLAN_ID AND MED.DELIVERY_ID = MD.DELIVERY_ID UNION ALL SELECT MP.PLAN_ID, ME.EXCEPTION_ID, MED.EXCEPTION_DETAIL_ID, MED.EXCEPTION_TYPE, MED.STATUS EXCEPTION_STATUS, ME.EXCEPTION_COUNT TOTAL_EXCEPTIONS, TO_CHAR(NULL) MODE_OF_TRANSPORT, MED.DELIVERY_ID, MED.CONTINUOUS_MOVE_ID, MED.FACILITY_ID, MED.CARRIER_ID, DECODE(MED.EXCEPTION_TYPE, 803, MED.NUMBER1, TO_NUMBER(NULL)) CUSTOMER_ID, MED.SUPPLIER_ID FROM MST_PLANS MP, MST_EXCEPTIONS ME, MST_EXCEPTION_DETAILS MED WHERE MP.PLAN_ID = ME.PLAN_ID AND ME.PLAN_ID = MED.PLAN_ID AND ME.EXCEPTION_ID = MED.EXCEPTION_ID AND MED.TRIP_ID1 IS NULL

View Text - HTML Formatted

SELECT MP.PLAN_ID
, ME.EXCEPTION_ID
, MED.EXCEPTION_DETAIL_ID
, MED.EXCEPTION_TYPE
, MED.STATUS EXCEPTION_STATUS
, ME.EXCEPTION_COUNT TOTAL_EXCEPTIONS
, TO_CHAR(NULL) MODE_OF_TRANSPORT
, MED.DELIVERY_ID
, MED.CONTINUOUS_MOVE_ID
, MED.FACILITY_ID
, MED.CARRIER_ID
, MED.CUSTOMER_ID
, MED.SUPPLIER_ID
FROM MST_PLANS MP
, MST_EXCEPTIONS ME
, MST_EXCEPTION_DETAILS MED
WHERE MP.PLAN_ID = ME.PLAN_ID
AND ME.PLAN_ID = MED.PLAN_ID
AND ME.EXCEPTION_ID = MED.EXCEPTION_ID
AND MED.TRIP_ID1 IS NULL UNION ALL SELECT MP.PLAN_ID
, ME.EXCEPTION_ID
, MED.EXCEPTION_DETAIL_ID
, MED.EXCEPTION_TYPE
, MED.STATUS EXCEPTION_STATUS
, ME.EXCEPTION_COUNT TOTAL_EXCEPTIONS
, MT.MODE_OF_TRANSPORT
, MED.DELIVERY_ID
, MT.CONTINUOUS_MOVE_ID
, FTE.FACILITY_ID
, MT.CARRIER_ID
, MED.CUSTOMER_ID
, MED.SUPPLIER_ID
FROM MST_PLANS MP
, MST_EXCEPTIONS ME
, MST_TRIPS MT
, MST_TRIP_STOPS MTS
, FTE_LOCATION_PARAMETERS FTE
, MST_EXCEPTION_DETAILS MED
WHERE MP.PLAN_ID = ME.PLAN_ID
AND ME.PLAN_ID = MED.PLAN_ID
AND ME.EXCEPTION_ID = MED.EXCEPTION_ID
AND MT.PLAN_ID = MED.PLAN_ID
AND MT.TRIP_ID = MED.TRIP_ID1
AND MT.PLAN_ID = MTS.PLAN_ID
AND MT.TRIP_ID = MTS.TRIP_ID
AND MTS.STOP_LOCATION_ID = FTE.LOCATION_ID UNION ALL SELECT MP.PLAN_ID
, ME.EXCEPTION_ID
, MED.EXCEPTION_DETAIL_ID
, MED.EXCEPTION_TYPE
, MED.STATUS EXCEPTION_STATUS
, ME.EXCEPTION_COUNT TOTAL_EXCEPTIONS
, TO_CHAR(NULL) MODE_OF_TRANSPORT
, MD.DELIVERY_ID
, MED.CONTINUOUS_MOVE_ID
, MED.FACILITY_ID
, MED.CARRIER_ID
, MD.CUSTOMER_ID
, MD.SUPPLIER_ID
FROM MST_PLANS MP
, MST_EXCEPTIONS ME
, MST_DELIVERIES MD
, MST_EXCEPTION_DETAILS MED
WHERE MP.PLAN_ID = ME.PLAN_ID
AND ME.PLAN_ID = MED.PLAN_ID
AND ME.EXCEPTION_ID = MED.EXCEPTION_ID
AND MED.PLAN_ID = MD.PLAN_ID
AND MED.DELIVERY_ID = MD.DELIVERY_ID UNION ALL SELECT MP.PLAN_ID
, ME.EXCEPTION_ID
, MED.EXCEPTION_DETAIL_ID
, MED.EXCEPTION_TYPE
, MED.STATUS EXCEPTION_STATUS
, ME.EXCEPTION_COUNT TOTAL_EXCEPTIONS
, TO_CHAR(NULL) MODE_OF_TRANSPORT
, MED.DELIVERY_ID
, MED.CONTINUOUS_MOVE_ID
, MED.FACILITY_ID
, MED.CARRIER_ID
, DECODE(MED.EXCEPTION_TYPE
, 803
, MED.NUMBER1
, TO_NUMBER(NULL)) CUSTOMER_ID
, MED.SUPPLIER_ID
FROM MST_PLANS MP
, MST_EXCEPTIONS ME
, MST_EXCEPTION_DETAILS MED
WHERE MP.PLAN_ID = ME.PLAN_ID
AND ME.PLAN_ID = MED.PLAN_ID
AND ME.EXCEPTION_ID = MED.EXCEPTION_ID
AND MED.TRIP_ID1 IS NULL