[Home] [Help]
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 (+)
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 (+)
244: and wda.trip_stop = wdl.pick_up_stop_id (+)
245: and wdl.pick_up_stop_id = wts.stop_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 (+)
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
1597: and wda.organization_id = p_organization_id
1633: fetch lpn_in_other_dock2 into x_loaded_dock_door;
1634: if lpn_in_other_dock2%FOUND then
1635: select name
1636: into x_trip_name
1637: from wsh_trips_ob_grp_v
1638: where trip_id = l_trip_id;
1639: x_result := 1;
1640: close lpn_in_other_dock2;
1641: close deliveries_in_lpn;
2728: ,x_msg_count OUT nocopy number) IS
2729:
2730: l_trip_id NUMBER := null;
2731: l_trip_name VARCHAR2(30);
2732: l_trip_info WSH_TRIPS_PUB.Trip_Pub_Rec_Type;
2733: BEGIN
2734: x_return_status := 'S';
2735:
2736: debug('p_delivery_id: ' || p_delivery_id
2768: END IF;
2769:
2770: debug('Trip id to be updated: ' || l_trip_id,'update_trip');
2771: debug('ship method code: ' || p_ship_method_code,'update_trip');
2772: debug('Calling WSH_TRIPS_PUB.create_update_trip','update_trip');
2773:
2774: l_trip_info.trip_id := l_trip_id;
2775: l_trip_info.last_update_date := SYSDATE;
2776: l_trip_info.last_updated_by :=FND_GLOBAL.USER_ID;
2776: l_trip_info.last_updated_by :=FND_GLOBAL.USER_ID;
2777: l_trip_info.last_update_login :=FND_GLOBAL.USER_ID;
2778: l_trip_info.ship_method_code :=p_ship_method_code;
2779:
2780: WSH_TRIPS_PUB.Create_Update_Trip
2781: (p_api_version_number => 1.0
2782: ,p_init_msg_list => FND_API.G_TRUE
2783: ,x_return_status => x_return_status
2784: ,x_msg_count => x_msg_count
2787: ,p_trip_info => l_trip_info
2788: ,x_trip_id => l_trip_id
2789: ,x_trip_name => l_trip_name);
2790:
2791: debug('return status from WSH_TRIPS_PUB.create_update_trip: ' || x_return_status,'update_trip');
2792:
2793: IF x_return_status NOT IN ('S','W') THEN
2794: debug('Shipping msg count: ' || x_msg_count,'update_trip');
2795: debug('Shipping error msg: ' || x_msg_data,'update_trip');
2829:
2830: l_trip_id NUMBER := null;
2831: l_trip_ship_method_code VARCHAR2(30) := null;
2832: l_trip_name VARCHAR2(30);
2833: l_trip_info WSH_TRIPS_PUB.Trip_Pub_Rec_Type;
2834:
2835: l_msg_count NUMBER;
2836: l_msg_data VARCHAR2(2000);
2837: l_summary_buffer VARCHAR2(2000);
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
2931: AND ROWNUM=1;
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
4112: AND ROWNUM = 1;
4179: l_trip_name VARCHAR2(30);
4180: l_msg_data VARCHAR2(2000);
4181:
4182: l_trip_trip_id NUMBER;
4183: l_trip_ship_method_code wsh_trips_ob_grp_v.ship_method_code%type := null;
4184:
4185: BEGIN
4186: savepoint ship_confirm;
4187: