VIEW: APPS.MST_DROP_TRAILER_VIO_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , med.exception_detail_id , mt.trip_id , mts.stop_id , mt.mode_of_transport , wc.carrier_id , flp.facility_id , ml3.meaning , med.customer_id , med.supplier_id , mt.trip_number , wc.freight_code , (select count(*) from mst_trip_stops mts1 WHERE mts1.plan_id = mt.plan_id and mts1.trip_id = mt.trip_id ) , decode(med.customer_id, null , decode(med.supplier_id, null , decode(med.organization_id, null , (select ml.meaning from mfg_lookups ml where ml.lookup_type = 'MST_STRING' and ml.lookup_code = 33 ) , (select ml.meaning from mfg_lookups ml where ml.lookup_type = 'MST_STRING' and ml.lookup_code = 7 ) ) , (select ml.meaning from mfg_lookups ml where ml.lookup_type = 'MST_STRING' and ml.lookup_code = 28 ) ) , (select ml.meaning from mfg_lookups ml where ml.lookup_type = 'MST_STRING' and ml.lookup_code = 27 ) ) , substr(mst_wb_util.get_name(mts.stop_location_id),1,80) , flp.facility_code , flp.description , mts.stop_sequence_number , wl.city , wl.state , wl.postal_code , wl.country , ml1.meaning , substr(mst_wb_util.get_workflow_status(med.plan_id, med.exception_detail_id),1,20) from mst_exception_details med , mst_trips mt , mst_trip_stops mts , wsh_carriers wc , fte_location_parameters flp , wsh_locations wl , mfg_lookups ml1 , mfg_lookups ml3 where med.status = ml1.lookup_code and ml1.lookup_type = 'MST_EXCEPTION_STATUS' and med.plan_id = mt.plan_id and med.trip_id1 = mt.trip_id and med.plan_id = mts.plan_id and med.stop_id1 = mts.stop_id and med.exception_type = ml3.lookup_code and ml3.lookup_type = 'MST_EXCEPTION_TYPE' and mt.carrier_id = wc.carrier_id and mts.stop_location_id = flp.location_id and mts.stop_location_id = wl.wsh_location_id

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MT.TRIP_ID
, MTS.STOP_ID
, MT.MODE_OF_TRANSPORT
, WC.CARRIER_ID
, FLP.FACILITY_ID
, ML3.MEANING
, MED.CUSTOMER_ID
, MED.SUPPLIER_ID
, MT.TRIP_NUMBER
, WC.FREIGHT_CODE
, (SELECT COUNT(*)
FROM MST_TRIP_STOPS MTS1
WHERE MTS1.PLAN_ID = MT.PLAN_ID
AND MTS1.TRIP_ID = MT.TRIP_ID )
, DECODE(MED.CUSTOMER_ID
, NULL
, DECODE(MED.SUPPLIER_ID
, NULL
, DECODE(MED.ORGANIZATION_ID
, NULL
, (SELECT ML.MEANING
FROM MFG_LOOKUPS ML
WHERE ML.LOOKUP_TYPE = 'MST_STRING'
AND ML.LOOKUP_CODE = 33 )
, (SELECT ML.MEANING
FROM MFG_LOOKUPS ML
WHERE ML.LOOKUP_TYPE = 'MST_STRING'
AND ML.LOOKUP_CODE = 7 ) )
, (SELECT ML.MEANING
FROM MFG_LOOKUPS ML
WHERE ML.LOOKUP_TYPE = 'MST_STRING'
AND ML.LOOKUP_CODE = 28 ) )
, (SELECT ML.MEANING
FROM MFG_LOOKUPS ML
WHERE ML.LOOKUP_TYPE = 'MST_STRING'
AND ML.LOOKUP_CODE = 27 ) )
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS.STOP_LOCATION_ID)
, 1
, 80)
, FLP.FACILITY_CODE
, FLP.DESCRIPTION
, MTS.STOP_SEQUENCE_NUMBER
, WL.CITY
, WL.STATE
, WL.POSTAL_CODE
, WL.COUNTRY
, ML1.MEANING
, SUBSTR(MST_WB_UTIL.GET_WORKFLOW_STATUS(MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID)
, 1
, 20)
FROM MST_EXCEPTION_DETAILS MED
, MST_TRIPS MT
, MST_TRIP_STOPS MTS
, WSH_CARRIERS WC
, FTE_LOCATION_PARAMETERS FLP
, WSH_LOCATIONS WL
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML3
WHERE MED.STATUS = ML1.LOOKUP_CODE
AND ML1.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.PLAN_ID = MT.PLAN_ID
AND MED.TRIP_ID1 = MT.TRIP_ID
AND MED.PLAN_ID = MTS.PLAN_ID
AND MED.STOP_ID1 = MTS.STOP_ID
AND MED.EXCEPTION_TYPE = ML3.LOOKUP_CODE
AND ML3.LOOKUP_TYPE = 'MST_EXCEPTION_TYPE'
AND MT.CARRIER_ID = WC.CARRIER_ID
AND MTS.STOP_LOCATION_ID = FLP.LOCATION_ID
AND MTS.STOP_LOCATION_ID = WL.WSH_LOCATION_ID