[Home] [Help]
115: --cursor to check if any apptmts within the current time window.
116: CURSOR current_schedule_cur IS
117: SELECT 1
118: FROM wms_dock_appointments_b wda, mtl_item_locations_kfv milk,
119: wsh_delivery_legs_ob_grp_v wdl, wsh_Trip_stops_ob_grp_v wts, mtl_item_locations_kfv milk1,wsh_trips wt
120: WHERE wda.dock_id = milk.inventory_location_id (+)
121: and wda.organization_id = milk.organization_id (+)
122: and wda.trip_stop = wdl.pick_up_stop_id (+)
123: and wdl.pick_up_stop_id = wts.stop_id (+)
136: --cursor to check if any apptmts missed to be used in the past.
137: CURSOR last_recent_schedule_cur IS
138: SELECT 1
139: FROM wms_dock_appointments_b wda, mtl_item_locations_kfv milk,
140: wsh_delivery_legs_ob_grp_v wdl,wsh_Trip_stops_ob_grp_v wts, mtl_item_locations_kfv milk1,wsh_trips wt
141: WHERE wda.dock_id = milk.inventory_location_id (+)
142: and wda.organization_id = milk.organization_id (+)
143: and wda.trip_stop = wdl.pick_up_stop_id (+)
144: and wdl.pick_up_stop_id = wts.stop_id (+)
186: , mtl_item_locations_kfv milk
187: , wsh_delivery_legs_ob_grp_v wdl
188: , wsh_Trip_stops_ob_grp_v wts
189: , mtl_item_locations_kfv milk1
190: ,wsh_trips wt
191: where wda.dock_id = milk.inventory_location_id (+)
192: and wda.organization_id = milk.organization_id (+)
193: and wda.trip_stop = wdl.pick_up_stop_id (+)
194: and wdl.pick_up_stop_id = wts.stop_id (+)
221: milk1.subinventory_code, milk1.concatenated_segments ,
222: WMS_SHIPPING_TRANSACTION_PUB.is_loaded(p_organization_id,wda.dock_id,'Y') is_loaded ,
223: wt.name trip_name , ( SYSDATE - wda.end_time ) last_schedule_time_diff
224: FROM wms_dock_appointments_b wda, mtl_item_locations_kfv milk, wsh_delivery_legs_ob_grp_v wdl,
225: wsh_Trip_stops_ob_grp_v wts, mtl_item_locations_kfv milk1,wsh_trips wt
226: WHERE wda.dock_id = milk.inventory_location_id (+)
227: and wda.organization_id = milk.organization_id (+)
228: and wda.trip_stop = wdl.pick_up_stop_id (+)
229: and wdl.pick_up_stop_id = wts.stop_id (+)
250: milk1.subinventory_code, milk1.concatenated_segments ,
251: WMS_SHIPPING_TRANSACTION_PUB.is_loaded(p_organization_id,wda.dock_id,'Y') is_loaded ,
252: wt.name trip_name , ( wda.start_time - SYSDATE) next_schedule_time_diff
253: FROM wms_dock_appointments_b wda, mtl_item_locations_kfv milk, wsh_delivery_legs_ob_grp_v wdl,
254: wsh_Trip_stops_ob_grp_v wts, mtl_item_locations_kfv milk1,wsh_trips wt
255: WHERE wda.dock_id = milk.inventory_location_id (+)
256: and wda.organization_id = milk.organization_id (+)
257: and wda.trip_stop = wdl.pick_up_stop_id (+)
258: and wdl.pick_up_stop_id = wts.stop_id (+)
1531: BEGIN
1532: select wt.name, milk.concatenated_segments
1533: into x_trip_name, x_loaded_dock_door
1534: from wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts,wms_dock_appointments_b wda,
1535: mtl_item_locations_kfv milk, wsh_trips_ob_grp_v wt
1536: where wdl.delivery_id = l_delivery_id
1537: and wdl.pick_up_stop_id = wts.stop_id
1538: and wda.trip_stop = wts.stop_id
1539: and wda.organization_id = p_organization_id
1575: fetch lpn_in_other_dock2 into x_loaded_dock_door;
1576: if lpn_in_other_dock2%FOUND then
1577: select name
1578: into x_trip_name
1579: from wsh_trips_ob_grp_v
1580: where trip_id = l_trip_id;
1581: x_result := 1;
1582: close lpn_in_other_dock2;
1583: close deliveries_in_lpn;
2667: ,x_msg_count OUT nocopy number) IS
2668:
2669: l_trip_id NUMBER := null;
2670: l_trip_name VARCHAR2(30);
2671: l_trip_info WSH_TRIPS_PUB.Trip_Pub_Rec_Type;
2672: BEGIN
2673: x_return_status := 'S';
2674:
2675: debug('p_delivery_id: ' || p_delivery_id
2707: END IF;
2708:
2709: debug('Trip id to be updated: ' || l_trip_id,'update_trip');
2710: debug('ship method code: ' || p_ship_method_code,'update_trip');
2711: debug('Calling WSH_TRIPS_PUB.create_update_trip','update_trip');
2712:
2713: l_trip_info.trip_id := l_trip_id;
2714: l_trip_info.last_update_date := SYSDATE;
2715: l_trip_info.last_updated_by :=FND_GLOBAL.USER_ID;
2715: l_trip_info.last_updated_by :=FND_GLOBAL.USER_ID;
2716: l_trip_info.last_update_login :=FND_GLOBAL.USER_ID;
2717: l_trip_info.ship_method_code :=p_ship_method_code;
2718:
2719: WSH_TRIPS_PUB.Create_Update_Trip
2720: (p_api_version_number => 1.0
2721: ,p_init_msg_list => FND_API.G_TRUE
2722: ,x_return_status => x_return_status
2723: ,x_msg_count => x_msg_count
2726: ,p_trip_info => l_trip_info
2727: ,x_trip_id => l_trip_id
2728: ,x_trip_name => l_trip_name);
2729:
2730: debug('return status from WSH_TRIPS_PUB.create_update_trip: ' || x_return_status,'update_trip');
2731:
2732: IF x_return_status NOT IN ('S','W') THEN
2733: debug('Shipping msg count: ' || x_msg_count,'update_trip');
2734: debug('Shipping error msg: ' || x_msg_data,'update_trip');
2768:
2769: l_trip_id NUMBER := null;
2770: l_trip_ship_method_code VARCHAR2(30) := null;
2771: l_trip_name VARCHAR2(30);
2772: l_trip_info WSH_TRIPS_PUB.Trip_Pub_Rec_Type;
2773:
2774: l_msg_count NUMBER;
2775: l_msg_data VARCHAR2(2000);
2776: l_summary_buffer VARCHAR2(2000);
2862: SELECT wts.trip_id,wt.ship_method_code
2863: INTO l_trip_id,l_trip_ship_method_code
2864: FROM wsh_delivery_legs_ob_grp_v wdl,
2865: wsh_trip_stops_ob_grp_v wts,
2866: wsh_trips_ob_grp_v wt
2867: WHERE wdl.delivery_id=p_delivery_id
2868: AND wdl.pick_up_stop_id=wts.stop_id
2869: AND wt.trip_id=wts.trip_id
2870: AND ROWNUM=1;
3944: SELECT wts.trip_id,
3945: wt.ship_method_code
3946: FROM wsh_delivery_legs_ob_grp_v wdl,
3947: wsh_trip_stops_ob_grp_v wts,
3948: wsh_trips_ob_grp_v wt
3949: WHERE wdl.delivery_id = l_delivery_id
3950: AND wdl.pick_up_stop_id = wts.stop_id
3951: AND wt.trip_id = wts.trip_id
3952: AND ROWNUM = 1;
4019: l_trip_name VARCHAR2(30);
4020: l_msg_data VARCHAR2(2000);
4021:
4022: l_trip_trip_id NUMBER;
4023: l_trip_ship_method_code wsh_trips_ob_grp_v.ship_method_code%type := null;
4024:
4025: BEGIN
4026: savepoint ship_confirm;
4027: