[Home] [Help]
90: IS
91: SELECT count(delivery_detail_id) FROM wsh_delivery_assignments
92: WHERE delivery_id in
93: (SELECT wdl.delivery_id
94: FROM wsh_delivery_legs wdl,
95: wsh_trip_stops wts1,
96: wsh_trip_stops wts2
97: WHERE wts1.trip_id = c_trip_id
98: AND wts2.trip_id = c_trip_id
151: )
152: )
153: AND wd.delivery_id in
154: (SELECT wdl.delivery_id
155: FROM wsh_delivery_legs wdl,
156: wsh_trip_stops wts1,
157: wsh_trip_stops wts2
158: WHERE wts1.trip_id = c_trip_id
159: AND wts2.trip_id = c_trip_id
186: AND c.manifesting_enabled_flag = 'Y'
187: AND b.carrier_manifesting_flag = 'Y'
188: AND a.delivery_id in
189: (SELECT wdl.delivery_id
190: FROM wsh_delivery_legs wdl,
191: wsh_trip_stops wts1,
192: wsh_trip_stops wts2
193: WHERE wts1.trip_id = c_trip_id
194: AND wts2.trip_id = c_trip_id
277: drop_off_stop_id,
278: reprice_required,
279: status_code,
280: parent_delivery_leg_id
281: FROM wsh_delivery_legs
282: WHERE delivery_id = c_dlv_id;
283: --
284: CURSOR c_leg_trip_det(c_delivery_leg_id NUMBER)
285: IS
295: wt.lane_id,
296: wt.schedule_id,
297: wt.load_tender_status
298: FROM wsh_trips wt,
299: wsh_delivery_legs wdl,
300: wsh_trip_stops wts1,
301: wsh_trip_stops wts2
302: WHERE wts1.stop_id = wdl.pick_up_stop_id
303: AND wts2.stop_id = wdl.drop_off_stop_id
344: --
345: CURSOR c_cnt_trip_legs(c_trip_id NUMBER)
346: IS
347: SELECT count(wdl.delivery_leg_id)
348: FROM wsh_delivery_legs wdl,
349: wsh_trip_stops wts1,
350: wsh_trip_stops wts2
351: WHERE wts1.trip_id = c_trip_id
352: AND wts2.trip_id = c_trip_id
354: AND wts2.stop_id = wdl.drop_off_stop_id;
355:
356: CURSOR c_count_reprice_reqd(c_trip_id IN NUMBER) IS
357: Select count(*)
358: from wsh_delivery_legs wdl, wsh_trip_stops wts1, wsh_trip_stops wts2,wsh_trips wt
359: where wdl.pick_up_stop_id = wts1.stop_id
360: and wdl.drop_off_stop_id = wts2.stop_id
361: and wdl.reprice_required = 'Y'
362: and wts1.trip_id = wt.trip_id
372: wdl.drop_off_stop_id,
373: wdl.reprice_required,
374: wdl.status_code,
375: wdl.parent_delivery_leg_id
376: FROM wsh_delivery_legs wdl,
377: wsh_trip_stops wts1,
378: wsh_trip_stops wts2
379: WHERE wts1.trip_id = c_trip_id
380: AND wts2.trip_id = c_trip_id
383:
384: CURSOR c_get_dleg_id (c_delivery_id NUMBER)
385: IS
386: SELECT delivery_leg_id
387: FROM wsh_delivery_legs
388: WHERE delivery_id = c_delivery_id;
389: --
390: -- Package global variables
391: --
3377: NULL, -- comm_category_id : FTE J estimate rate
3378: wda.type,
3379: wda.parent_delivery_id,
3380: wdl.parent_delivery_leg_id
3381: from wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_delivery_legs wdl,
3382: wsh_new_deliveries wd
3383: where wdd.delivery_detail_id = wda.delivery_detail_id
3384: and wda.delivery_id = wdl.delivery_id
3385: and wdl.delivery_id = wd.delivery_id
3458: NULL, -- comm_category_id : FTE J estimate rate
3459: wda.type,
3460: wda.parent_delivery_id,
3461: wdl.parent_delivery_leg_id
3462: from wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_delivery_legs wdl,
3463: wsh_new_deliveries wd, wsh_trips wt, wsh_trip_stops wts1, wsh_trip_stops wts2
3464: where wdd.delivery_detail_id = wda.delivery_detail_id
3465: and wda.delivery_id = wdl.delivery_id
3466: and wdl.delivery_id = wd.delivery_id
5188: FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'rate as in Pack I');
5189:
5190: -- lock delivery leg
5191: SELECT reprice_required INTO l_dummy
5192: FROM wsh_delivery_legs
5193: WHERE delivery_leg_id = l_dleg_id
5194: FOR UPDATE OF reprice_required NOWAIT;
5195:
5196: -- Always set the reprice flag for now
5193: WHERE delivery_leg_id = l_dleg_id
5194: FOR UPDATE OF reprice_required NOWAIT;
5195:
5196: -- Always set the reprice flag for now
5197: UPDATE wsh_delivery_legs
5198: SET reprice_required = 'Y'
5199: WHERE delivery_leg_id = l_dleg_id;
5200:
5201: l_prc_in_rec.api_version_number := 1.0;