[Home] [Help]
220: ,wts1.trip_id --2767767
221: FROM wsh_new_deliveries_ob_grp_v wnd,
222: -- 2767767
223: (SELECT wdl.delivery_id,wts.trip_id
224: FROM wsh_delivery_legs_ob_grp_v wdl,
225: wsh_trip_stops_ob_grp_v wts
226: WHERE wdl.delivery_id=p_delivery_id
227: AND wdl.pick_up_stop_id=wts.stop_id
228: AND ROWNUM=1) wts1 -- end 2767767
234: -- then trip id should be updated in wstt because user may try to close the trip from mobile.
235: BEGIN
236: SELECT wts.trip_id,wt.name
237: INTO l_trip_id,l_trip_name
238: FROM wsh_delivery_legs_ob_grp_v wdl,
239: wsh_trip_stops_ob_grp_v wts,
240: wsh_trips_ob_grp_v wt
241: WHERE wdl.delivery_id=p_delivery_id
242: AND wdl.pick_up_stop_id=wts.stop_id
373: ELSE
374: SELECT wdi.SEQUENCE_NUMBER
375: INTO l_BOL
376: FROM wsh_document_instances wdi
377: ,wsh_delivery_legs_ob_grp_v wdl
378: WHERE wdl.delivery_id = p_delivery_id
379: AND wdi.entity_id = wdl.delivery_leg_id
380: AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
381: AND rownum < 2;
376: FROM wsh_document_instances wdi
377: ,wsh_delivery_legs_ob_grp_v wdl
378: WHERE wdl.delivery_id = p_delivery_id
379: AND wdi.entity_id = wdl.delivery_leg_id
380: AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
381: AND rownum < 2;
382: -- Right now datamodel sugget that WDI - WDL >- WND. So for 1 delivery multiple delivery leg
383: -- multiple BOL. Desktop Application does not support this still I would like to show one BOL
384: END IF;
911: -- if delivery is assigned to trip update of ship method is not allowed
912: BEGIN
913: SELECT distinct wts.trip_id
914: INTO l_trip_id
915: FROM wsh_delivery_legs_ob_grp_v wdl,
916: wsh_trip_stops_ob_grp_v wts
917: WHERE wdl.delivery_id=p_delivery_id
918: AND wdl.pick_up_stop_id=wts.stop_id
919: AND rownum=1;
1147:
1148: CURSOR c_Get_Trip(v_del_id NUMBER) IS
1149: select wt.trip_id , wt.carrier_id, wt.ship_method_code, wt.mode_of_transport,
1150: wt.tp_plan_name -- glog proj
1151: from wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt
1152: where wdl.pick_up_stop_id=wts.stop_id
1153: and wdl.delivery_id=v_del_id
1154: and wts.trip_id=wt.trip_id;
1155:
1629: LOOP
1630: BEGIN
1631: SELECT wts.trip_id,wt.name
1632: INTO l_trip_id,l_trip_name
1633: FROM wsh_delivery_legs_ob_grp_v wdl,
1634: wsh_trip_stops_ob_grp_v wts,
1635: wsh_trips_ob_grp_v wt
1636: WHERE wdl.delivery_id=l_deliveries.delivery_id
1637: AND wdl.pick_up_stop_id=wts.stop_id
1805: -- Add the following code in case user created Trip manually from desktop
1806: BEGIN
1807: SELECT wts.trip_id
1808: INTO l_trip_id
1809: FROM wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
1810: WHERE wdl.delivery_id = p_delivery_id --l_delivery_id /* bug 2741857 */
1811: and wdl.pick_up_stop_id = wts.stop_id;
1812:
1813: IF l_trip_id IS NOT NULL THEN
2152:
2153:
2154: SELECT stop_id
2155: INTO l_stop_id
2156: FROM WSH_TRIP_STOPS_OB_GRP_V wts, WSH_DELIVERY_LEGS_OB_GRP_V wdl
2157: -- WHERE wts.trip_id = p_trip_id
2158: WHERE wts.trip_id=l_trip_id
2159: AND wts.stop_id = wdl.pick_up_stop_id
2160: AND ROWNUM <2;
2308: , p_ship_method_code IN VARCHAR2 DEFAULT NULL
2309: ) IS
2310: CURSOR stop_cur IS
2311: SELECT wdl.pick_up_stop_id pick_up_stop_id
2312: FROM wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
2313: WHERE wts.trip_id = p_trip_id
2314: AND wdl.pick_up_stop_id = wts.stop_id;
2315:
2316: CURSOR del_cur IS
2314: AND wdl.pick_up_stop_id = wts.stop_id;
2315:
2316: CURSOR del_cur IS
2317: SELECT delivery_id
2318: FROM wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
2319: WHERE wts.trip_id = p_trip_id
2320: AND wdl.pick_up_stop_id = wts.stop_id;
2321:
2322: l_del_cur del_cur%ROWTYPE;
2715: END IF;
2716:
2717: SELECT stop_id
2718: INTO l_stop_id
2719: FROM wsh_trip_stops_ob_grp_v wts, wsh_delivery_legs_ob_grp_v wdl
2720: WHERE wts.trip_id = p_trip_id
2721: AND wts.stop_id = wdl.pick_up_stop_id
2722: AND ROWNUM < 2;
2723: