[Home] [Help]
30: RETURN BOOLEAN IS
31:
32: CURSOR Check_Last_Trip (l_delivery_id NUMBER) IS
33: SELECT s1.trip_id
34: FROM wsh_trip_stops s1,
35: wsh_delivery_legs dl1,
36: wsh_new_deliveries d1,
37: wsh_trip_stops s2,
38: wsh_delivery_legs dl2
33: SELECT s1.trip_id
34: FROM wsh_trip_stops s1,
35: wsh_delivery_legs dl1,
36: wsh_new_deliveries d1,
37: wsh_trip_stops s2,
38: wsh_delivery_legs dl2
39: WHERE d1.delivery_id <> l_delivery_id
40: AND s1.stop_id = dl1.pick_up_stop_id
41: AND d1.delivery_id = dl1.delivery_id
166: --cursor to check if any apptmts within the current time window.
167: CURSOR current_schedule_cur IS
168: SELECT 1
169: FROM wms_dock_appointments_b wda, mtl_item_locations_kfv milk,
170: wsh_delivery_legs_ob_grp_v wdl, wsh_Trip_stops_ob_grp_v wts, mtl_item_locations_kfv milk1,wsh_trips wt
171: WHERE wda.dock_id = milk.inventory_location_id (+)
172: and wda.organization_id = milk.organization_id (+)
173: and wda.trip_stop = wdl.pick_up_stop_id (+)
174: and wdl.pick_up_stop_id = wts.stop_id (+)
187: --cursor to check if any apptmts missed to be used in the past.
188: CURSOR last_recent_schedule_cur IS
189: SELECT 1
190: FROM wms_dock_appointments_b wda, mtl_item_locations_kfv milk,
191: wsh_delivery_legs_ob_grp_v wdl,wsh_Trip_stops_ob_grp_v wts, mtl_item_locations_kfv milk1,wsh_trips wt
192: WHERE wda.dock_id = milk.inventory_location_id (+)
193: and wda.organization_id = milk.organization_id (+)
194: and wda.trip_stop = wdl.pick_up_stop_id (+)
195: and wdl.pick_up_stop_id = wts.stop_id (+)
235: , wt.name trip_name
236: from wms_dock_appointments_b wda
237: , mtl_item_locations_kfv milk
238: , wsh_delivery_legs_ob_grp_v wdl
239: , wsh_Trip_stops_ob_grp_v wts
240: , mtl_item_locations_kfv milk1
241: ,wsh_trips wt
242: where wda.dock_id = milk.inventory_location_id (+)
243: and wda.organization_id = milk.organization_id (+)
272: milk1.subinventory_code, milk1.concatenated_segments ,
273: WMS_SHIPPING_TRANSACTION_PUB.is_loaded(p_organization_id,wda.dock_id,'Y') is_loaded ,
274: wt.name trip_name , ( SYSDATE - wda.end_time ) last_schedule_time_diff
275: FROM wms_dock_appointments_b wda, mtl_item_locations_kfv milk, wsh_delivery_legs_ob_grp_v wdl,
276: wsh_Trip_stops_ob_grp_v wts, mtl_item_locations_kfv milk1,wsh_trips wt
277: WHERE wda.dock_id = milk.inventory_location_id (+)
278: and wda.organization_id = milk.organization_id (+)
279: and wda.trip_stop = wdl.pick_up_stop_id (+)
280: and wdl.pick_up_stop_id = wts.stop_id (+)
301: milk1.subinventory_code, milk1.concatenated_segments ,
302: WMS_SHIPPING_TRANSACTION_PUB.is_loaded(p_organization_id,wda.dock_id,'Y') is_loaded ,
303: wt.name trip_name , ( wda.start_time - SYSDATE) next_schedule_time_diff
304: FROM wms_dock_appointments_b wda, mtl_item_locations_kfv milk, wsh_delivery_legs_ob_grp_v wdl,
305: wsh_Trip_stops_ob_grp_v wts, mtl_item_locations_kfv milk1,wsh_trips wt
306: WHERE wda.dock_id = milk.inventory_location_id (+)
307: and wda.organization_id = milk.organization_id (+)
308: and wda.trip_stop = wdl.pick_up_stop_id (+)
309: and wdl.pick_up_stop_id = wts.stop_id (+)
537: BEGIN
538: open x_delivery_lov FOR
539: select wdl.delivery_id
540: from wsh_delivery_legs_ob_grp_v wdl
541: , wsh_trip_stops_ob_grp_v pickup_stop
542: , wsh_trip_stops_ob_grp_v dropoff_stop
543: where wdl.pick_up_stop_id = pickup_stop.stop_id
544: and wdl.drop_off_stop_id = dropoff_stop.stop_id
545: and pickup_stop.trip_id = dropoff_stop.trip_id
538: open x_delivery_lov FOR
539: select wdl.delivery_id
540: from wsh_delivery_legs_ob_grp_v wdl
541: , wsh_trip_stops_ob_grp_v pickup_stop
542: , wsh_trip_stops_ob_grp_v dropoff_stop
543: where wdl.pick_up_stop_id = pickup_stop.stop_id
544: and wdl.drop_off_stop_id = dropoff_stop.stop_id
545: and pickup_stop.trip_id = dropoff_stop.trip_id
546: and pickup_stop.trip_id = p_trip_id;
583: open x_staging_lane_lov for
584: select distinct wda.staging_lane_id, milk.concatenated_segments
585: from wms_dock_appointments_b wda
586: , mtl_item_locations_kfv milk
587: , wsh_trip_Stops_ob_grp_v pickup_stop
588: where milk.inventory_location_id(+) = wda.staging_lane_id
589: and milk.organization_id(+) = wda.organization_id
590: and milk.organization_id = p_organization_id
591: and milk.subinventory_code = p_sub_code
616: select wdd.delivery_detail_id, wdd.lpn_id
617: from wsh_delivery_details_ob_grp_v wdd
618: , wsh_delivery_assignments_v wda
619: , wsh_delivery_legs_ob_grp_v wdl
620: , wsh_trip_stops_ob_grp_v pickup_stop
621: , wsh_trip_stops_ob_grp_v dropoff_stop
622: where wdd.delivery_detail_id = wda.delivery_detail_id
623: and wda.delivery_id = wdl.delivery_id
624: and wdl.pick_up_stop_id = pickup_stop.stop_id
617: from wsh_delivery_details_ob_grp_v wdd
618: , wsh_delivery_assignments_v wda
619: , wsh_delivery_legs_ob_grp_v wdl
620: , wsh_trip_stops_ob_grp_v pickup_stop
621: , wsh_trip_stops_ob_grp_v dropoff_stop
622: where wdd.delivery_detail_id = wda.delivery_detail_id
623: and wda.delivery_id = wdl.delivery_id
624: and wdl.pick_up_stop_id = pickup_stop.stop_id
625: and wdl.drop_off_Stop_id = dropoff_stop.stop_id
793: BEGIN
794: SELECT wts.trip_id
795: INTO l_trip_id
796: FROM wsh_delivery_legs_ob_grp_v wdl
797: , wsh_trip_stops_ob_grp_v wts
798: WHERE wdl.delivery_id = l_delivery_id
799: AND wdl.pick_up_stop_id = wts.stop_id;
800:
801: EXCEPTION
1508: BEGIN
1509: -- check the trip
1510: select wts.trip_id
1511: into l_trip_id
1512: from wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
1513: where wdl.delivery_id = l_delivery_id
1514: and wdl.pick_up_stop_id = wts.stop_id;
1515:
1516: select 1
1588: -- First check if this delivery have trip which is scheduled
1589: BEGIN
1590: select wt.name, milk.concatenated_segments
1591: into x_trip_name, x_loaded_dock_door
1592: from wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts,wms_dock_appointments_b wda,
1593: mtl_item_locations_kfv milk, wsh_trips_ob_grp_v wt
1594: where wdl.delivery_id = l_delivery_id
1595: and wdl.pick_up_stop_id = wts.stop_id
1596: and wda.trip_stop = wts.stop_id
1624: -- check the corresponding trip if any
1625: BEGIN
1626: select wts.trip_id
1627: into l_trip_id
1628: from wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
1629: where wdl.delivery_id = l_delivery_id
1630: and wdl.pick_up_stop_id = wts.stop_id;
1631:
1632: OPEN lpn_in_other_dock2(l_trip_id);
2751: begin
2752: SELECT wts.trip_id
2753: INTO l_trip_id
2754: FROM wsh_delivery_legs_ob_grp_v wdl,
2755: wsh_trip_stops_ob_grp_v wts
2756: WHERE wdl.delivery_id=p_delivery_id
2757: AND wdl.pick_up_stop_id=wts.stop_id
2758: AND ROWNUM=1;
2759: EXCEPTION
2922:
2923: SELECT wts.trip_id,wt.ship_method_code
2924: INTO l_trip_id,l_trip_ship_method_code
2925: FROM wsh_delivery_legs_ob_grp_v wdl,
2926: wsh_trip_stops_ob_grp_v wts,
2927: wsh_trips_ob_grp_v wt
2928: WHERE wdl.delivery_id=p_delivery_id
2929: AND wdl.pick_up_stop_id=wts.stop_id
2930: AND wt.trip_id=wts.trip_id
3022: select DISTINCT wlpn.license_plate_number,
3023: lpn.subinventory_code,
3024: milk.concatenated_segments,
3025: wnd.name
3026: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,wsh_trip_stops_ob_grp_v pickup_stop,
3027: wsh_delivery_legs_ob_grp_v wdl,wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
3028: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
3029: where wdd.delivery_detail_id = wda.delivery_detail_id
3030: and wdd.released_status = 'Y'
3060: and delivery_id is not null
3061: UNION
3062: select distinct wdl.delivery_id
3063: from wsh_delivery_legs_ob_grp_v wdl,wms_shipping_transaction_temp wstt,
3064: wsh_trip_stops_ob_grp_v wts
3065: where wdl.pick_up_stop_id = wts.stop_id
3066: and wts.trip_id = wstt.trip_id
3067: and wstt.dock_door_id = p_dock_door_id
3068: and wstt.organization_id = p_organization_id
3103: if (p_trip_id <> 0) then
3104: BEGIN
3105: select count(distinct lpn.outermost_lpn_id)
3106: into l_count
3107: from wsh_trip_stops_ob_grp_v pickup_stop
3108: ,wsh_delivery_legs_ob_grp_v wdl
3109: ,wsh_delivery_assignments_v wda
3110: ,wsh_delivery_details_ob_grp_v wdd
3111: ,wsh_delivery_details_ob_grp_v wdd2
3145: , milk.concatenated_segments
3146: , wnd.name
3147: from wms_license_plate_numbers lpn
3148: , mtl_item_locations_kfv milk
3149: , wsh_trip_stops_ob_grp_v pickup_stop
3150: , wsh_delivery_legs_ob_grp_v wdl
3151: , wsh_delivery_assignments_v wda
3152: , wsh_delivery_details_ob_grp_v wdd
3153: , wsh_delivery_details_ob_grp_v wdd2
3193: UNION
3194: select distinct wdl.delivery_id
3195: from wsh_delivery_legs_ob_grp_v wdl
3196: ,wms_shipping_transaction_temp wstt
3197: ,wsh_trip_stops_ob_grp_v wts
3198: where wdl.pick_up_stop_id = wts.stop_id
3199: and wts.trip_id = wstt.trip_id
3200: and wstt.dock_door_id = p_dock_door_id
3201: and wstt.organization_id = p_organization_id
3248: UNION
3249: select distinct wdl.delivery_id
3250: from wsh_delivery_legs_ob_grp_v wdl
3251: ,wms_shipping_transaction_temp wstt
3252: ,wsh_trip_stops_ob_grp_v wts
3253: where wdl.pick_up_stop_id = wts.stop_id
3254: and wts.trip_id = wstt.trip_id
3255: and wstt.dock_door_id = p_dock_door_id
3256: and wstt.organization_id = p_organization_id
3300: into l_count
3301: from wsh_delivery_details_ob_grp_v wdd
3302: , wsh_delivery_assignments_v wda
3303: , wsh_delivery_legs_ob_grp_v wdl
3304: , wsh_Trip_stops_ob_grp_v pickup_stop
3305: , mtl_system_items_kfv msik
3306: where
3307: wda.delivery_id = wdl.delivery_id
3308: AND wda.delivery_detail_id = wdd.delivery_detail_id
3334: from wsh_delivery_details_ob_grp_v wdd
3335: , wsh_delivery_assignments_v wda
3336: , wsh_new_deliveries_ob_grp_v wnd
3337: , wsh_delivery_legs_ob_grp_v wdl
3338: , wsh_trip_Stops_ob_grp_v pickup_stop
3339: , mtl_system_items_kfv msik
3340: where wnd.delivery_id = wda.delivery_id
3341: AND wda.delivery_id = wdl.delivery_id
3342: AND wda.delivery_detail_id = wdd.delivery_detail_id
3369: UNION
3370: select distinct wdl.delivery_id
3371: from wsh_delivery_legs_ob_grp_v wdl
3372: ,wms_shipping_transaction_temp wstt
3373: ,wsh_trip_stops_ob_grp_v wts
3374: where wdl.pick_up_stop_id = wts.stop_id
3375: and wts.trip_id = wstt.trip_id
3376: and wstt.dock_door_id = p_dock_door_id
3377: and wstt.organization_id = p_organization_id
3419: UNION
3420: select distinct wdl.delivery_id
3421: from wsh_delivery_legs_ob_grp_v wdl
3422: ,wms_shipping_transaction_temp wstt
3423: ,wsh_trip_stops_ob_grp_v wts
3424: where wdl.pick_up_stop_id = wts.stop_id
3425: and wts.trip_id = wstt.trip_id
3426: and wstt.dock_door_id = p_dock_door_id
3427: and wstt.organization_id = p_organization_id
4053:
4054: cursor pick_up_stops is
4055: select distinct wdl.pick_up_stop_id,wts.stop_sequence_number
4056: from wsh_delivery_legs_ob_grp_v wdl,wms_shipping_transaction_temp wstt,
4057: wsh_trip_stops_ob_grp_v wts
4058: where wdl.pick_up_stop_id = wts.stop_id
4059: and wts.trip_id = wstt.trip_id
4060: and wstt.dock_door_id = p_dock_door_id
4061: and wstt.organization_id = p_organization_id
4067: /* Bug 14395497 start
4068: cursor drop_off_stops(p_dock_appoint_flag varchar2) is
4069: select distinct wdl.drop_off_stop_id
4070: from wsh_delivery_legs wdl,wms_shipping_transaction_temp wstt,
4071: wsh_trip_stops wts
4072: where wdl.drop_off_stop_id = wts.stop_id
4073: and wts.trip_id = wstt.trip_id
4074: and wstt.dock_door_id = p_dock_door_id
4075: and wstt.organization_id = p_organization_id
4076: and wstt.dock_appoint_flag = p_dock_appoint_flag
4077: and nvl(direct_ship_flag,'N') = 'N';
4078: */
4079: --Bug 14395497: Order by stop_sequence_number asc should be added in cursor drop_off_stops,
4080: -- if the corresponding records of a Trip in wsh_trip_stops are not at the order by
4081: -- stop_sequence_number from samll to large, it will conflict with the order in the cursor
4082: -- prev_stop_csr in WSH_TRIP_STOPS_VALIDATIONS.Check_Stop_Close.
4083: -- Thus it caused parts of deliveres in the Trip still be in the status In_Transit after ship confirm.
4084: cursor drop_off_stops(p_dock_appoint_flag varchar2) is
4078: */
4079: --Bug 14395497: Order by stop_sequence_number asc should be added in cursor drop_off_stops,
4080: -- if the corresponding records of a Trip in wsh_trip_stops are not at the order by
4081: -- stop_sequence_number from samll to large, it will conflict with the order in the cursor
4082: -- prev_stop_csr in WSH_TRIP_STOPS_VALIDATIONS.Check_Stop_Close.
4083: -- Thus it caused parts of deliveres in the Trip still be in the status In_Transit after ship confirm.
4084: cursor drop_off_stops(p_dock_appoint_flag varchar2) is
4085: select wts.stop_id from wsh_trip_stops wts where exists
4086: (
4081: -- stop_sequence_number from samll to large, it will conflict with the order in the cursor
4082: -- prev_stop_csr in WSH_TRIP_STOPS_VALIDATIONS.Check_Stop_Close.
4083: -- Thus it caused parts of deliveres in the Trip still be in the status In_Transit after ship confirm.
4084: cursor drop_off_stops(p_dock_appoint_flag varchar2) is
4085: select wts.stop_id from wsh_trip_stops wts where exists
4086: (
4087: select wdl.drop_off_stop_id from
4088: wsh_delivery_legs wdl,
4089: (SELECT wstt.trip_id
4103: CURSOR c_get_trip_ship_method(l_delivery_id NUMBER)IS
4104: SELECT wts.trip_id,
4105: wt.ship_method_code
4106: FROM wsh_delivery_legs_ob_grp_v wdl,
4107: wsh_trip_stops_ob_grp_v wts,
4108: wsh_trips_ob_grp_v wt
4109: WHERE wdl.delivery_id = l_delivery_id
4110: AND wdl.pick_up_stop_id = wts.stop_id
4111: AND wt.trip_id = wts.trip_id
4640: IF (p_close_trip_flag = 'Y' OR (l_intransit_flag = 'Y' AND l_open_delivery_id IS NULL))
4641: AND (l_stop_rows.COUNT >= 1) THEN
4642: FOR i IN 1..l_stop_rows.COUNT LOOP
4643: IF l_debug = 1 THEN
4644: debug('Calling wsh_trip_stops_pub.stop_action','LPN_SHIP');
4645: debug('l_stop_rows(i) ' || l_stop_rows(i),'LPN_SHIP');
4646: END IF;
4647:
4648: --patchset J. Shipping API cleanup
4645: debug('l_stop_rows(i) ' || l_stop_rows(i),'LPN_SHIP');
4646: END IF;
4647:
4648: --patchset J. Shipping API cleanup
4649: wsh_trip_stops_pub.stop_action
4650: (p_api_version_number => 1.0,
4651: p_init_msg_list => G_TRUE,
4652: x_return_status => l_return_status,
4653: x_msg_count => l_msg_count,
4684: debug('Calling change status for the drop-off stops','LPN_SHIP');
4685: END IF;
4686:
4687: FOR i IN 1..l_drop_off_stops.COUNT LOOP
4688: wsh_trip_stops_pub.stop_action
4689: (p_api_version_number => 1.0,
4690: p_init_msg_list => G_TRUE,
4691: x_return_status => l_return_status,
4692: x_msg_count => l_msg_count,
4695: p_stop_id => l_drop_off_stops(i),
4696: p_actual_date => sysdate,
4697: p_defer_interface_flag => NVL(l_defer_interface_flag,'N')); --BUG10170155
4698: IF l_debug = 1 THEN
4699: debug('wsh_trip_stops_actions.change_status return status: ' || l_return_status,'LPN_SHIP');
4700: END IF;
4701:
4702: IF l_return_status NOT IN ('S','W') THEN
4703: IF l_debug = 1 THEN
4754: END IF;
4755:
4756: SELECT COUNT(DISTINCT wdl.pick_up_stop_id)
4757: INTO l_pick_up_count
4758: FROM wsh_trip_stops wts, wsh_delivery_legs wdl
4759: where wts.stop_id = wdl.pick_up_stop_id
4760: and wts.trip_id = p_trip_id;
4761:
4762: debug('l_pick_up_count: ' || l_pick_up_count,'LPN_SHIP');
4855:
4856: l_stop_rows(1) := p_trip_stop_id;
4857: --patchset J. Shipping API cleanup
4858: IF p_close_trip_flag = 'Y' OR l_intransit_flag = 'Y' THEN
4859: wsh_trip_stops_pub.stop_action
4860: (p_api_version_number => 1.0,
4861: p_init_msg_list => G_TRUE,
4862: x_return_status => l_return_status,
4863: x_msg_count => l_msg_count,
4888: debug('Call Shipping to close drop-off stops','LPN_SHIP');
4889: END IF;
4890:
4891: FOR i IN 1..l_drop_off_stops.COUNT LOOP
4892: wsh_trip_stops_pub.stop_action
4893: (p_api_version_number => 1.0,
4894: p_init_msg_list => G_TRUE,
4895: x_return_status => l_return_status,
4896: x_msg_count => l_msg_count,
4900: p_actual_date => sysdate,
4901: p_defer_interface_flag =>NVL(l_defer_interface_flag,'N')); --BUG10170155
4902:
4903: IF l_debug = 1 THEN
4904: debug('wsh_trip_stops_actions.change_status return status: ' || l_return_status,'LPN_SHIP');
4905: END IF;
4906: IF l_return_status NOT IN ('S','W') THEN
4907: IF l_debug = 1 THEN
4908: debug('Could not close the drop-off stops. Let the transaction go through','LPN_SHIP');
5763: FROM wsh_delivery_details_ob_grp_v wdd,
5764: wsh_delivery_assignments_v wda,
5765: wsh_new_deliveries_ob_grp_v wnd,
5766: wsh_delivery_legs_ob_grp_v wdl,
5767: wsh_trip_Stops_ob_grp_v pickup_stop,
5768: mtl_system_items_kfv msik
5769: WHERE wnd.delivery_id = wda.delivery_id
5770: AND wda.delivery_id = wdl.delivery_id
5771: AND wda.delivery_detail_id = wdd.delivery_detail_id
5796: FROM wsh_delivery_details_ob_grp_v wdd,
5797: wsh_delivery_assignments_v wda,
5798: wsh_new_deliveries_ob_grp_v wnd,
5799: wsh_delivery_legs_ob_grp_v wdl,
5800: wsh_trip_stops_ob_grp_v pickup_stop,
5801: mtl_system_items_kfv msik
5802: WHERE wnd.delivery_id = wda.delivery_id
5803: AND wda.delivery_id = wdl.delivery_id
5804: AND wda.delivery_detail_id = wdd.delivery_detail_id
5828: UNION
5829: SELECT DISTINCT wdl.delivery_id
5830: from wsh_delivery_legs_ob_grp_v wdl,
5831: wms_shipping_transaction_temp wstt,
5832: wsh_trip_stops_ob_grp_v wts
5833: WHERE wdl.pick_up_stop_id = wts.stop_id
5834: AND wts.trip_id = wstt.trip_id
5835: AND wstt.dock_door_id = p_dock_door_id
5836: AND wstt.organization_id = p_organization_id
5874: UNION
5875: SELECT DISTINCT wdl.delivery_id
5876: FROM wsh_delivery_legs_ob_grp_v wdl,
5877: wms_shipping_transaction_temp wstt,
5878: wsh_trip_stops_ob_grp_v wts
5879: WHERE wdl.pick_up_stop_id = wts.stop_id
5880: AND wts.trip_id = wstt.trip_id
5881: AND wstt.dock_door_id = p_dock_door_id
5882: AND wstt.organization_id = p_organization_id