VIEW: APPS.MST_TRADE_PART_CAR_INCOMP_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , med.exception_detail_id , mt.trip_id , mt.mode_of_transport , mt.trip_number , substr(mst_wb_util.get_name(mt.origin_location_id),1,80) , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(mt.destination_location_id),1,80) , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , hzp.party_name , ml2.meaning , hzp.party_name , hzp.address1 , hzp.city , hzp.state , hzp.postal_code , hzp.country , wc.carrier_id , wc.freight_code , wlk.meaning , 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 , wsh_locations wl1 , wsh_locations wl2 , hz_parties hzp , hz_cust_accounts hzc, mfg_lookups ml1 , mfg_lookups ml2 , wsh_carriers wc , wsh_lookups wlk WHERE med.status = ml1.lookup_code and med.plan_id = mt.plan_id and med.trip_id1 = mt.trip_id and ml1.lookup_type = 'MST_EXCEPTION_STATUS' and decode(med.customer_id, null, 28, 27) = ml2.lookup_code and ml2.lookup_type = 'MST_STRING' and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id and decode(med.customer_id, null, med.supplier_id, med.customer_id) = hzc.cust_account_id and hzp.party_id = hzc.party_id and mt.mode_of_transport = wlk.lookup_code and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and med.carrier_id = wc.carrier_id and wl1.wsh_location_id = mt.origin_location_id and wl2.wsh_location_id = mt.destination_location_id

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MT.TRIP_ID
, MT.MODE_OF_TRANSPORT
, MT.TRIP_NUMBER
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.ORIGIN_LOCATION_ID)
, 1
, 80)
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.DESTINATION_LOCATION_ID)
, 1
, 80)
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, HZP.PARTY_NAME
, ML2.MEANING
, HZP.PARTY_NAME
, HZP.ADDRESS1
, HZP.CITY
, HZP.STATE
, HZP.POSTAL_CODE
, HZP.COUNTRY
, WC.CARRIER_ID
, WC.FREIGHT_CODE
, WLK.MEANING
, 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
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZC
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, WSH_CARRIERS WC
, WSH_LOOKUPS WLK
WHERE MED.STATUS = ML1.LOOKUP_CODE
AND MED.PLAN_ID = MT.PLAN_ID
AND MED.TRIP_ID1 = MT.TRIP_ID
AND ML1.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND DECODE(MED.CUSTOMER_ID
, NULL
, 28
, 27) = ML2.LOOKUP_CODE
AND ML2.LOOKUP_TYPE = 'MST_STRING'
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND DECODE(MED.CUSTOMER_ID
, NULL
, MED.SUPPLIER_ID
, MED.CUSTOMER_ID) = HZC.CUST_ACCOUNT_ID
AND HZP.PARTY_ID = HZC.PARTY_ID
AND MT.MODE_OF_TRANSPORT = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND MED.CARRIER_ID = WC.CARRIER_ID
AND WL1.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID
AND WL2.WSH_LOCATION_ID = MT.DESTINATION_LOCATION_ID