VIEW: APPS.MST_ITEM_VEHICLE_INCOMP_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , med.exception_detail_id , med.trip_id1 , med.delivery_id , mt.carrier_id , mt.mode_of_transport , mt.trip_number , wc.freight_code , msikfv1.concatenated_segments , mt.trip_start_date , substr ( mst_wb_util.get_local_chardtzone ( mt.origin_location_id, mt.trip_start_date ), 1, 40 ) , msikfv2.concatenated_segments , msitl2.description , decode ( mdd.split_from_delivery_detail_id, null, mdd.sr_delivery_detail_id, mdd.delivery_detail_id ) , md.delivery_number , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , ml.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_carriers wc , mtl_system_items_kfv msikfv1 , fte_vehicle_types fvt , mst_delivery_details mdd , mtl_system_items_kfv msikfv2 , mtl_system_items_tl msitl2 , wsh_locations wl1 , wsh_locations wl2 , mfg_lookups ml , mst_deliveries md WHERE med.plan_id = mt.plan_id and med.trip_id1 = mt.trip_id and mt.carrier_id = wc.carrier_id and mt.vehicle_type_id = fvt.vehicle_type_id and msikfv1.inventory_item_id = fvt.inventory_item_id and msikfv1.organization_id = fvt.organization_id and med.plan_id = mdd.plan_id and med.delivery_detail_id = mdd.delivery_detail_id and mdd.inventory_item_id = msikfv2.inventory_item_id and mdd.organization_id = msikfv2.organization_id and mdd.inventory_item_id = msitl2.inventory_item_id and mdd.organization_id = msitl2.organization_id and msitl2.language = userenv('LANG') and mdd.ship_from_location_id = wl1.wsh_location_id and mdd.ship_to_location_id = wl2.wsh_location_id and med.plan_id = md.plan_id and med.delivery_id = md.delivery_id and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.status = ml.lookup_code

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.TRIP_ID1
, MED.DELIVERY_ID
, MT.CARRIER_ID
, MT.MODE_OF_TRANSPORT
, MT.TRIP_NUMBER
, WC.FREIGHT_CODE
, MSIKFV1.CONCATENATED_SEGMENTS
, MT.TRIP_START_DATE
, SUBSTR ( MST_WB_UTIL.GET_LOCAL_CHARDTZONE ( MT.ORIGIN_LOCATION_ID
, MT.TRIP_START_DATE )
, 1
, 40 )
, MSIKFV2.CONCATENATED_SEGMENTS
, MSITL2.DESCRIPTION
, DECODE ( MDD.SPLIT_FROM_DELIVERY_DETAIL_ID
, NULL
, MDD.SR_DELIVERY_DETAIL_ID
, MDD.DELIVERY_DETAIL_ID )
, MD.DELIVERY_NUMBER
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, ML.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_CARRIERS WC
, MTL_SYSTEM_ITEMS_KFV MSIKFV1
, FTE_VEHICLE_TYPES FVT
, MST_DELIVERY_DETAILS MDD
, MTL_SYSTEM_ITEMS_KFV MSIKFV2
, MTL_SYSTEM_ITEMS_TL MSITL2
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, MFG_LOOKUPS ML
, MST_DELIVERIES MD
WHERE MED.PLAN_ID = MT.PLAN_ID
AND MED.TRIP_ID1 = MT.TRIP_ID
AND MT.CARRIER_ID = WC.CARRIER_ID
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND MSIKFV1.INVENTORY_ITEM_ID = FVT.INVENTORY_ITEM_ID
AND MSIKFV1.ORGANIZATION_ID = FVT.ORGANIZATION_ID
AND MED.PLAN_ID = MDD.PLAN_ID
AND MED.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MDD.INVENTORY_ITEM_ID = MSIKFV2.INVENTORY_ITEM_ID
AND MDD.ORGANIZATION_ID = MSIKFV2.ORGANIZATION_ID
AND MDD.INVENTORY_ITEM_ID = MSITL2.INVENTORY_ITEM_ID
AND MDD.ORGANIZATION_ID = MSITL2.ORGANIZATION_ID
AND MSITL2.LANGUAGE = USERENV('LANG')
AND MDD.SHIP_FROM_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MED.PLAN_ID = MD.PLAN_ID
AND MED.DELIVERY_ID = MD.DELIVERY_ID
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.STATUS = ML.LOOKUP_CODE