VIEW: APPS.MST_FAC_FAC_INCOMP_TRIP_V

Source

View Text - Preformatted

SELECT mt.plan_id , mt.trip_id , mt.carrier_id , mt.service_level , mt.mode_of_transport , mt.trip_number , wc.freight_code , wlk1.meaning , wlk2.meaning , msikfv.concatenated_segments , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , nvl ( mt.total_basic_transport_cost, 0 ) + nvl ( mt.total_accessorial_cost, 0 ) + nvl ( mt.total_handling_cost , 0 ) + nvl ( mt.total_layover_cost, 0 ) + nvl ( mt.total_load_unload_cost , 0 ) + nvl ( mt.total_stop_cost, 0 ) , nvl ( sum ( md.gross_weight ), 0 ) , mp.weight_uom , nvl ( sum ( md.volume ), 0 ) , mp.volume_uom , mp.currency_uom , nvl ( sum ( md.number_of_pallets ), 0 ) , nvl ( sum ( md.number_of_pieces ), 0 ) , nvl ( mst_wb_util.get_trip_orders ( mt.plan_id, mt.trip_id ), 0 ) from mst_trips mt , wsh_lookups wlk1 , wsh_lookups wlk2 , mtl_system_items_kfv msikfv , wsh_locations wl1 , wsh_locations wl2 , mst_plans mp , mst_deliveries md , fte_vehicle_types fvt , wsh_carriers wc , mst_delivery_legs mdl WHERE mt.plan_id = mdl.plan_id and mt.trip_id = mdl.trip_id and mt.plan_id = md.plan_id and mt.plan_id = mp.plan_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and mt.carrier_id = wc.carrier_id and wlk1.lookup_type = 'WSH_SERVICE_LEVELS' and mt.service_level = wlk1.lookup_code and wlk2.lookup_type = 'WSH_MODE_OF_TRANSPORT' and mt.mode_of_transport = wlk2.lookup_code and mt.vehicle_type_id = fvt.vehicle_type_id (+) and fvt.inventory_item_id = msikfv.inventory_item_id (+) and fvt.organization_id = msikfv.organization_id (+) and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id group by mt.plan_id , mt.trip_id , mt.carrier_id , mt.service_level , mt.mode_of_transport , mt.trip_number , wc.freight_code , wlk1.meaning , wlk2.meaning , msikfv.concatenated_segments , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , nvl ( mt.total_basic_transport_cost, 0 ) + nvl ( mt.total_accessorial_cost, 0 ) + nvl ( mt.total_handling_cost , 0 ) + nvl ( mt.total_layover_cost, 0 ) + nvl ( mt.total_load_unload_cost , 0 ) + nvl ( mt.total_stop_cost, 0 ) , mp.weight_uom , mp.volume_uom , mp.currency_uom , nvl ( mst_wb_util.get_trip_orders ( mt.plan_id, mt.trip_id ), 0 )

View Text - HTML Formatted

SELECT MT.PLAN_ID
, MT.TRIP_ID
, MT.CARRIER_ID
, MT.SERVICE_LEVEL
, MT.MODE_OF_TRANSPORT
, MT.TRIP_NUMBER
, WC.FREIGHT_CODE
, WLK1.MEANING
, WLK2.MEANING
, MSIKFV.CONCATENATED_SEGMENTS
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, NVL ( MT.TOTAL_BASIC_TRANSPORT_COST
, 0 ) + NVL ( MT.TOTAL_ACCESSORIAL_COST
, 0 ) + NVL ( MT.TOTAL_HANDLING_COST
, 0 ) + NVL ( MT.TOTAL_LAYOVER_COST
, 0 ) + NVL ( MT.TOTAL_LOAD_UNLOAD_COST
, 0 ) + NVL ( MT.TOTAL_STOP_COST
, 0 )
, NVL ( SUM ( MD.GROSS_WEIGHT )
, 0 )
, MP.WEIGHT_UOM
, NVL ( SUM ( MD.VOLUME )
, 0 )
, MP.VOLUME_UOM
, MP.CURRENCY_UOM
, NVL ( SUM ( MD.NUMBER_OF_PALLETS )
, 0 )
, NVL ( SUM ( MD.NUMBER_OF_PIECES )
, 0 )
, NVL ( MST_WB_UTIL.GET_TRIP_ORDERS ( MT.PLAN_ID
, MT.TRIP_ID )
, 0 )
FROM MST_TRIPS MT
, WSH_LOOKUPS WLK1
, WSH_LOOKUPS WLK2
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, MST_PLANS MP
, MST_DELIVERIES MD
, FTE_VEHICLE_TYPES FVT
, WSH_CARRIERS WC
, MST_DELIVERY_LEGS MDL
WHERE MT.PLAN_ID = MDL.PLAN_ID
AND MT.TRIP_ID = MDL.TRIP_ID
AND MT.PLAN_ID = MD.PLAN_ID
AND MT.PLAN_ID = MP.PLAN_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MT.CARRIER_ID = WC.CARRIER_ID
AND WLK1.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
AND MT.SERVICE_LEVEL = WLK1.LOOKUP_CODE
AND WLK2.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND MT.MODE_OF_TRANSPORT = WLK2.LOOKUP_CODE
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID (+)
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID (+)
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID (+)
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID GROUP BY MT.PLAN_ID
, MT.TRIP_ID
, MT.CARRIER_ID
, MT.SERVICE_LEVEL
, MT.MODE_OF_TRANSPORT
, MT.TRIP_NUMBER
, WC.FREIGHT_CODE
, WLK1.MEANING
, WLK2.MEANING
, MSIKFV.CONCATENATED_SEGMENTS
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, NVL ( MT.TOTAL_BASIC_TRANSPORT_COST
, 0 ) + NVL ( MT.TOTAL_ACCESSORIAL_COST
, 0 ) + NVL ( MT.TOTAL_HANDLING_COST
, 0 ) + NVL ( MT.TOTAL_LAYOVER_COST
, 0 ) + NVL ( MT.TOTAL_LOAD_UNLOAD_COST
, 0 ) + NVL ( MT.TOTAL_STOP_COST
, 0 )
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, MP.CURRENCY_UOM
, NVL ( MST_WB_UTIL.GET_TRIP_ORDERS ( MT.PLAN_ID
, MT.TRIP_ID )
, 0 )