VIEW: APPS.MST_FAC_HAN_CAP_EXCEED_LOAD_V

Source

View Text - Preformatted

SELECT mt.plan_id , mt.trip_id , mt.mode_of_transport , mt.trip_number , mts.stop_location_id , mts.stop_sequence_number , mts.planned_arrival_date , mts.planned_departure_date , substr(mst_wb_util.get_local_chardtzone(mts.stop_location_id, mts.planned_arrival_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mts.stop_location_id, mts.planned_departure_date),1,40) , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , substr(mst_wb_util.get_hr_min((mts.pln_loading_end_time - mts.pln_loading_start_time) * 24),1,20) , (mts.pln_loading_end_time - mts.pln_loading_start_time) * 24 , substr(mst_wb_util.get_hr_min((mts.pln_unloading_end_time - mts.pln_unloading_start_time) * 24),1,20) , (mts.pln_unloading_end_time - mts.pln_unloading_start_time) * 24 , (select nvl(sum(md.gross_weight),0) from mst_deliveries md , mst_delivery_legs mdl WHERE md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and (mdl.pick_up_stop_id = mts.stop_id or mdl.drop_off_stop_id = mts.stop_id)) , mp.weight_uom , (select nvl(sum(md.volume),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and (mdl.pick_up_stop_id = mts.stop_id or mdl.drop_off_stop_id = mts.stop_id)) , mp.volume_uom , (select nvl(sum(md.number_of_pallets),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and (mdl.pick_up_stop_id = mts.stop_id or mdl.drop_off_stop_id = mts.stop_id)) , (select count(mdd.delivery_detail_id) from mst_delivery_details mdd , mst_delivery_assignments mda , mst_delivery_legs mdl where mdd.plan_id = mda.plan_id and mdd.delivery_detail_id = mda.delivery_detail_id and mda.plan_id = mdl.plan_id and mda.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and (mdl.pick_up_stop_id = mts.stop_id or mdl.drop_off_stop_id = mts.stop_id)) , (select nvl(sum(md.number_of_pieces),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and (mdl.pick_up_stop_id = mts.stop_id or mdl.drop_off_stop_id = mts.stop_id)) from mst_plans mp , mst_trips mt , mst_trip_stops mts , wsh_locations wl1 , wsh_locations wl2 where mp.plan_id = mt.plan_id and mt.plan_id = mts.plan_id and mt.trip_id = mts.trip_id and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id

View Text - HTML Formatted

SELECT MT.PLAN_ID
, MT.TRIP_ID
, MT.MODE_OF_TRANSPORT
, MT.TRIP_NUMBER
, MTS.STOP_LOCATION_ID
, MTS.STOP_SEQUENCE_NUMBER
, MTS.PLANNED_ARRIVAL_DATE
, MTS.PLANNED_DEPARTURE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS.STOP_LOCATION_ID
, MTS.PLANNED_ARRIVAL_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS.STOP_LOCATION_ID
, MTS.PLANNED_DEPARTURE_DATE)
, 1
, 40)
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_HR_MIN((MTS.PLN_LOADING_END_TIME - MTS.PLN_LOADING_START_TIME) * 24)
, 1
, 20)
, (MTS.PLN_LOADING_END_TIME - MTS.PLN_LOADING_START_TIME) * 24
, SUBSTR(MST_WB_UTIL.GET_HR_MIN((MTS.PLN_UNLOADING_END_TIME - MTS.PLN_UNLOADING_START_TIME) * 24)
, 1
, 20)
, (MTS.PLN_UNLOADING_END_TIME - MTS.PLN_UNLOADING_START_TIME) * 24
, (SELECT NVL(SUM(MD.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND (MDL.PICK_UP_STOP_ID = MTS.STOP_ID OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID))
, MP.WEIGHT_UOM
, (SELECT NVL(SUM(MD.VOLUME)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND (MDL.PICK_UP_STOP_ID = MTS.STOP_ID OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID))
, MP.VOLUME_UOM
, (SELECT NVL(SUM(MD.NUMBER_OF_PALLETS)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND (MDL.PICK_UP_STOP_ID = MTS.STOP_ID OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID))
, (SELECT COUNT(MDD.DELIVERY_DETAIL_ID)
FROM MST_DELIVERY_DETAILS MDD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERY_LEGS MDL
WHERE MDD.PLAN_ID = MDA.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID
AND MDA.PLAN_ID = MDL.PLAN_ID
AND MDA.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND (MDL.PICK_UP_STOP_ID = MTS.STOP_ID OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID))
, (SELECT NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND (MDL.PICK_UP_STOP_ID = MTS.STOP_ID OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID))
FROM MST_PLANS MP
, MST_TRIPS MT
, MST_TRIP_STOPS MTS
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.PLAN_ID = MTS.PLAN_ID
AND MT.TRIP_ID = MTS.TRIP_ID
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID