[Home] [Help]
91: x_return_Status OUT NOCOPY varchar2 ) IS
92:
93: cursor get_pickup_stop (c_trip_id number) is
94: select distinct st.stop_id, st.stop_location_id
95: from wsh_trip_stops st,
96: wsh_delivery_legs dg
97: where st.trip_id = c_trip_id
98: and dg.pick_up_stop_id = st.stop_id;
99:
109: and dl.delivery_id = dg.delivery_id );
110:
111: cursor get_org_id (c_trip_id number) is
112: select distinct dl.organization_id
113: from wsh_trip_stops st, wsh_delivery_legs dg, wsh_new_deliveries dl
114: where st.trip_id = c_trip_id
115: and dg.delivery_id = dl.delivery_id
116: and st.stop_location_id = dl.initial_pickup_location_id
117: and st.stop_id = dg.pick_up_stop_id;
283: x_return_status OUT NOCOPY VARCHAR2) IS
284:
285: cursor get_stops (c_stop_id NUMBER) is
286: select stop_id
287: from wsh_trip_stops
288: where trip_id = p_trip_id
289: and stop_id = NVL(c_stop_id, stop_id)
290: and status_code IN ('OP','AR')
291: and nvl(SHIPMENTS_TYPE_FLAG, 'O') IN ('O', 'M') -- J Inbound Logistics jckwok
292: order by stop_sequence_number asc ;
293:
294: cursor get_pickup_stop is
295: select t.stop_id
296: from wsh_trip_stops t,
297: wsh_delivery_legs dg
298: where t.trip_id = p_trip_id
299: and dg.pick_up_stop_id = t.stop_id
300: and t.status_code IN ('OP','AR')
311: CURSOR c_stop_del_status(p_stop_id NUMBER) IS
312: SELECT 1
313: FROM wsh_new_deliveries dl,
314: wsh_delivery_legs dg,
315: wsh_trip_stops st,
316: wsh_trips t
317: WHERE dl.delivery_id = dg.delivery_id AND
318: (dg.pick_up_stop_id = st.stop_id OR dg.drop_off_stop_id = st.stop_id) AND
319: st.trip_id = t.trip_id AND
511: END IF;
512:
513: --
514: IF l_debug_on THEN
515: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_ACTIONS.Confirm_Stop',WSH_DEBUG_SV.C_PROC_LEVEL);
516: END IF;
517: --
518: WSH_TRIP_STOPS_ACTIONS.Confirm_Stop (
519: p_stop_id => stop_rec.stop_id,
514: IF l_debug_on THEN
515: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_ACTIONS.Confirm_Stop',WSH_DEBUG_SV.C_PROC_LEVEL);
516: END IF;
517: --
518: WSH_TRIP_STOPS_ACTIONS.Confirm_Stop (
519: p_stop_id => stop_rec.stop_id,
520: p_action_flag => p_action_flag,
521: p_intransit_flag => p_intransit_flag,
522: p_close_flag => p_close_flag,
715: where trip_id = c_trip_id;
716:
717: cursor get_stops(c_trip_id IN NUMBER) is
718: select 'Y'
719: from wsh_trip_stops
720: where trip_id = c_trip_id
721: and status_code <> 'OP'
722: and rownum = 1;
723:
913: x_return_status OUT NOCOPY VARCHAR2) IS
914:
915: i BINARY_INTEGER;
916: /* H integration for Multi Leg */
917: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
918: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
919: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
920: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
921: l_return_status VARCHAR2(30);
914:
915: i BINARY_INTEGER;
916: /* H integration for Multi Leg */
917: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
918: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
919: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
920: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
921: l_return_status VARCHAR2(30);
922: l_num_warn NUMBER := 0;
1194: --{
1195: --- TP release
1196: CURSOR c_getdels IS
1197: SELECT delivery_id
1198: FROM wsh_delivery_legs wdl, wsh_trip_stops wts
1199: WHERE wdl.pick_up_stop_id=wts.stop_id AND
1200: wts.trip_id=p_in_rec.trip_id;
1201:
1202:
1200: wts.trip_id=p_in_rec.trip_id;
1201:
1202:
1203: /* H integration for Multi Leg */
1204: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
1205: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
1206: --
1207: --
1208: l_num_warnings NUMBER;
1538: --
1539: CURSOR dlvy_csr(p_trip_id NUMBER)
1540: IS
1541: SELECT wdl.delivery_id, wt.name
1542: FROM wsh_trip_stops wts,
1543: wsh_Delivery_legs wdl,
1544: wsh_new_deliveries wnd,
1545: wsh_trips wt
1546: WHERE wt.trip_id = p_trip_id
2523: SELECT stop_id,
2524: planned_departure_date,
2525: planned_arrival_date,
2526: NVL(shipments_type_flag,'O') shipments_type_flag --J-IB-NPARIKH
2527: FROM wsh_trip_stops
2528: WHERE trip_id = p_trip_id
2529: ORDER BY stop_sequence_number; -- J-IB-NPARIKH
2530:
2531: l_delivery_id NUMBER;
2553: l_good_dels WSH_UTIL_CORE.ID_TAB_TYPE;
2554:
2555: /* H integration for Multi Leg */
2556: l_num_warn NUMBER := 0;
2557: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
2558: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
2559: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
2560: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
2561: l_grp_trip_rec WSH_TRIPS_GRP.TRIP_PUB_REC_TYPE;
2554:
2555: /* H integration for Multi Leg */
2556: l_num_warn NUMBER := 0;
2557: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
2558: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
2559: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
2560: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
2561: l_grp_trip_rec WSH_TRIPS_GRP.TRIP_PUB_REC_TYPE;
2562: --l_return_status VARCHAR2(30);
2564: --
2565: l_debug_on BOOLEAN;
2566: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTOCREATE_TRIP';
2567: --
2568: l_stop_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;
2569: l_stop_processed VARCHAR2(10);
2570: -- Bug 3413364, skip the delivery if it does not have both initial pickup date and ultimate dropoff date
2571: l_initial_pickup_date DATE;
2572: l_ultimate_dropoff_date DATE;
2838: -- H integration
2839: -- Get pvt type record structure for stop
2840: --
2841: IF l_debug_on THEN
2842: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2843: END IF;
2844: --
2845: wsh_trip_stops_grp.get_stop_details_pvt
2846: (p_stop_id => st.stop_id,
2841: IF l_debug_on THEN
2842: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2843: END IF;
2844: --
2845: wsh_trip_stops_grp.get_stop_details_pvt
2846: (p_stop_id => st.stop_id,
2847: x_stop_rec => l_stop_rec,
2848: x_return_status => l_return_status);
2849: --
2892: END IF;
2893: --
2894: -- End of H integration
2895: --
2896: UPDATE wsh_trip_stops
2897: SET planned_departure_date = nvl(planned_arrival_date, SYSDATE)
2898: WHERE stop_id = st.stop_id;
2899: --}
2900: END IF;
2906: -- H integration
2907: -- Get pvt type record structure for stop
2908: --
2909: IF l_debug_on THEN
2910: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2911: END IF;
2912: --
2913: wsh_trip_stops_grp.get_stop_details_pvt
2914: (p_stop_id => st.stop_id,
2909: IF l_debug_on THEN
2910: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2911: END IF;
2912: --
2913: wsh_trip_stops_grp.get_stop_details_pvt
2914: (p_stop_id => st.stop_id,
2915: x_stop_rec => l_stop_rec,
2916: x_return_status => l_return_status);
2917: --
2957: --}
2958: END IF;
2959: --
2960: -- End of H integration
2961: UPDATE wsh_trip_stops
2962: SET planned_arrival_date = nvl(planned_departure_date, SYSDATE)
2963: WHERE stop_id = st.stop_id;
2964: --
2965: -- DBI Project
2962: SET planned_arrival_date = nvl(planned_departure_date, SYSDATE)
2963: WHERE stop_id = st.stop_id;
2964: --
2965: -- DBI Project
2966: -- Updating WSH_TRIP_STOPS.
2967: -- Call DBI API after the Update.
2968: -- This API will also check for DBI Installed or not
2969: IF l_debug_on THEN
2970: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',st.stop_id);
3150: --dummy tables for calling validate_constraint_mainper
3151: l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
3152: l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
3153: l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
3154: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
3155: l_cc_in_ids wsh_util_core.id_tab_type;
3156: l_cc_fail_ids wsh_util_core.id_tab_type;
3157:
3158: CURSOR del_cur(p_dlvy_id NUMBER) IS
3968:
3969: CURSOR pickup_leg_check (l_delivery_id IN NUMBER) IS
3970: SELECT dg.delivery_leg_id,
3971: st.stop_id
3972: FROM wsh_trip_stops st,
3973: wsh_delivery_legs dg
3974: WHERE dg.delivery_id = l_delivery_id AND
3975: dg.pick_up_stop_id = st.stop_id AND
3976: st.stop_location_id = p_pickup_location_id;
3977:
3978: CURSOR dropoff_leg_check (l_delivery_id IN NUMBER) IS
3979: SELECT dg.delivery_leg_id,
3980: st.stop_id
3981: FROM wsh_trip_stops st,
3982: wsh_delivery_legs dg
3983: WHERE dg.delivery_id = l_delivery_id AND
3984: dg.drop_off_stop_id = st.stop_id AND
3985: st.stop_location_id = p_dropoff_location_id;
3997: --
3998: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_ASSIGN_TRIP';
3999: --
4000: -- patchset J csun Stop Sequence Change
4001: l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;
4002: l_return_status VARCHAR2(1);
4003:
4004: BEGIN
4005:
4243: l_debug_on BOOLEAN;
4244:
4245: CURSOR get_stop_location (l_trip_stop_id IN NUMBER) IS
4246: SELECT stop_location_id
4247: FROM wsh_trip_stops
4248: WHERE stop_id = l_trip_stop_id;
4249:
4250: CURSOR c_any_activity_exists(p_stop_id IN NUMBER) IS
4251: SELECT '1'
4254: AND rownum=1;
4255:
4256: CURSOR c_get_stops IS
4257: SELECT stop_id, stop_location_id, status_code
4258: FROM wsh_trip_stops
4259: WHERE trip_id=p_trip_id
4260: ORDER BY stop_sequence_number;
4261:
4262: CURSOR delivery_info (del_id IN NUMBER) IS
4311: p_stop_id IN NUMBER) RETURN NUMBER IS
4312: CURSOR c_stop_seqs IS
4313: -- get the SSNs of this stop and the next stop.
4314: SELECT ts2.stop_sequence_number
4315: FROM wsh_trip_stops ts1, -- get this stop's SSN
4316: wsh_trip_stops ts2 -- then look for SSN and next SSN
4317: WHERE ts1.stop_id = p_stop_id
4318: AND ts2.trip_id = p_trip_id
4319: AND ts2.stop_sequence_number >= ts1.stop_sequence_number
4312: CURSOR c_stop_seqs IS
4313: -- get the SSNs of this stop and the next stop.
4314: SELECT ts2.stop_sequence_number
4315: FROM wsh_trip_stops ts1, -- get this stop's SSN
4316: wsh_trip_stops ts2 -- then look for SSN and next SSN
4317: WHERE ts1.stop_id = p_stop_id
4318: AND ts2.trip_id = p_trip_id
4319: AND ts2.stop_sequence_number >= ts1.stop_sequence_number
4320: AND rownum <= 2
4475: IF l_debug_on THEN
4476: WSH_DEBUG_SV.logmsg(l_module_name,'Caller is FTE/TP changing pickup loc to be l_phys_trip_pickup_loc_id');
4477: END IF;
4478:
4479: UPDATE wsh_trip_stops
4480: SET stop_location_id= l_phys_trip_pickup_loc_id,
4481: physical_location_id=null,
4482: last_update_date = sysdate,
4483: last_updated_by = FND_GLOBAL.USER_ID
4658: IF l_debug_on THEN
4659: WSH_DEBUG_SV.log(l_module_name,'changing stop location to internal location l_dropoff_location_id',l_dropoff_location_id);
4660: END IF;
4661:
4662: UPDATE wsh_trip_stops
4663: SET stop_location_id= l_dropoff_location_id,
4664: physical_location_id=l_trip_dropoff_loc_id,
4665: last_update_date = sysdate,
4666: last_updated_by = FND_GLOBAL.USER_ID
4767: ) IS
4768:
4769: CURSOR get_pickup_stop (l_loc_id IN NUMBER, l_del_id IN NUMBER) IS
4770: SELECT st.stop_id
4771: FROM wsh_trip_stops st,
4772: wsh_delivery_legs dg
4773: WHERE st.stop_location_id = l_loc_id AND
4774: dg.pick_up_stop_id = st.stop_id AND
4775: dg.delivery_id = l_del_id;
4775: dg.delivery_id = l_del_id;
4776:
4777: CURSOR get_dropoff_stop (l_loc_id IN NUMBER, l_del_id IN NUMBER) IS
4778: SELECT st.stop_id
4779: FROM wsh_trip_stops st,
4780: wsh_delivery_legs dg
4781: WHERE st.stop_location_id = l_loc_id AND
4782: dg.drop_off_stop_id = st.stop_id AND
4783: dg.delivery_id = l_del_id;
4788:
4789: stop_not_found EXCEPTION;
4790: others EXCEPTION;
4791: /* H integration for Multi Leg */
4792: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
4793: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
4794: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
4795: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
4796: l_num_warn NUMBER := 0;
4789: stop_not_found EXCEPTION;
4790: others EXCEPTION;
4791: /* H integration for Multi Leg */
4792: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
4793: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
4794: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
4795: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
4796: l_num_warn NUMBER := 0;
4797: l_num_err NUMBER := 0;
4802: --
4803: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ASSIGN_TRIP';
4804: --
4805: -- patchset J csun Stop Sequence Change
4806: l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;
4807:
4808: l_del_rows wsh_util_core.id_tab_type;
4809: l_pickup_stop_id NUMBER;
4810: l_dropoff_stop_id NUMBER;
5062: -- SSN change
5063: -- Call Reset_Stop_Seq_Numbers only if mode = PAD
5064: IF l_stop_seq_mode = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_PAD THEN
5065: -- need to resequence the stops' SSNs and validate their dates.
5066: WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS(
5067: p_stop_details_rec => l_stop_details_rec,
5068: x_return_status => l_return_status );
5069:
5070: IF l_debug_on THEN
5067: p_stop_details_rec => l_stop_details_rec,
5068: x_return_status => l_return_status );
5069:
5070: IF l_debug_on THEN
5071: WSH_DEBUG_SV.log(l_module_name,'return status from WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS',l_return_status);
5072: END IF;
5073:
5074: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5075: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5097: p_caller => p_caller
5098: );
5099:
5100: IF l_debug_on THEN
5101: wsh_debug_sv.log(l_module_name,'Return Status From WSH_TRIP_STOPS_VALIDATIONS.Validate_Stop_Dates for trip '||p_trip_id ,l_return_status);
5102: END IF;
5103:
5104: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5105: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5198: CURSOR c_get_stop_trip(c_stop_id IN NUMBER) IS
5199: SELECT trip_id,
5200: weight_uom_code,
5201: volume_uom_code
5202: FROM wsh_trip_stops
5203: WHERE stop_id = c_stop_id;
5204:
5205: CURSOR trip_vehicle_info (c_trip_id NUMBER) IS
5206: SELECT vehicle_item_id,
5403: departure_net_weight,
5404: departure_volume,
5405: nvl(shipments_type_flag,'O'),
5406: nvl(wv_frozen_flag,'Y')
5407: FROM wsh_trip_stops
5408: WHERE trip_id = l_trip_id AND
5409: nvl(planned_departure_date, nvl(p_start_departure_date, FND_API.G_MISS_DATE)) >= nvl(p_start_departure_date, FND_API.G_MISS_DATE)
5410: ORDER BY stop_sequence_number;
5411:
5411:
5412:
5413: CURSOR lock_stop(c_stop_id NUMBER) IS
5414: SELECT stop_id
5415: FROM wsh_trip_stops
5416: WHERE stop_id = c_stop_id
5417: FOR UPDATE NOWAIT;
5418:
5419:
5423: departure_net_weight,
5424: departure_volume,
5425: weight_uom_code,
5426: volume_uom_code
5427: FROM wsh_trip_stops
5428: WHERE trip_id = l_trip_id AND
5429: nvl(planned_departure_date, nvl(p_start_departure_date, FND_API.G_MISS_DATE)) <= nvl(p_start_departure_date, FND_API.G_MISS_DATE) AND
5430: rownum = 1
5431: ORDER BY stop_sequence_number DESC;
5431: ORDER BY stop_sequence_number DESC;
5432:
5433: CURSOR last_stop (l_trip_id NUMBER) IS
5434: SELECT stop_id
5435: FROM wsh_trip_stops
5436: WHERE trip_id = l_trip_id
5437: AND stop_sequence_number = ( SELECT MAX(wts.stop_sequence_number)
5438: FROM wsh_trip_stops wts
5439: WHERE wts.trip_id = l_trip_id );
5434: SELECT stop_id
5435: FROM wsh_trip_stops
5436: WHERE trip_id = l_trip_id
5437: AND stop_sequence_number = ( SELECT MAX(wts.stop_sequence_number)
5438: FROM wsh_trip_stops wts
5439: WHERE wts.trip_id = l_trip_id );
5440:
5441: CURSOR pickup_deliveries (l_stop_id IN NUMBER) IS
5442: SELECT dl.delivery_id d_id,
5445: dl.gross_weight,
5446: dl.net_weight,
5447: dl.volume,
5448: dl.organization_id
5449: FROM wsh_trip_stops t,
5450: wsh_delivery_legs dg,
5451: wsh_new_deliveries dl
5452: WHERE t.stop_id = l_stop_id AND
5453: dg.pick_up_stop_id = t.stop_id AND
5456:
5457: CURSOR dropoff_deliveries (l_stop_id NUMBER) IS
5458: SELECT dl.delivery_id d_id,
5459: dl.organization_id
5460: FROM wsh_trip_stops t,
5461: wsh_delivery_legs dg,
5462: wsh_new_deliveries dl
5463: WHERE t.stop_id = l_stop_id AND
5464: dg.drop_off_stop_id = t.stop_id AND
5542: g_return_status VARCHAR2(1);
5543: l_locked_stop_id NUMBER := 0;
5544: others EXCEPTION;
5545: /* H integration for Multi Leg */
5546: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
5547: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
5548:
5549: --R12 MDC
5550: l_total_pick_up_weight NUMBER := 0;
5716: --
5717: -- Debug Statements
5718: --
5719: IF l_debug_on THEN
5720: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5721: END IF;
5722: --
5723: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_start_stop_id,p_caller));
5724: -- bug 2732503 , need a warning message only when FTE is installed
5719: IF l_debug_on THEN
5720: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5721: END IF;
5722: --
5723: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_start_stop_id,p_caller));
5724: -- bug 2732503 , need a warning message only when FTE is installed
5725: -- still will go to wt_vol_error
5726: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5727: wsh_util_core.add_message(x_return_status);
5785: --
5786: -- Debug Statements
5787: --
5788: IF l_debug_on THEN
5789: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5790: END IF;
5791: --
5792: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
5793: -- bug 2732503 , need a warning message only when FTE is installed
5788: IF l_debug_on THEN
5789: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5790: END IF;
5791: --
5792: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
5793: -- bug 2732503 , need a warning message only when FTE is installed
5794: -- still will go to wt_vol_error
5795: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5796: wsh_util_core.add_message(x_return_status);
6075: --
6076: -- Debug Statements
6077: --
6078: IF l_debug_on THEN
6079: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6080: END IF;
6081: --
6082: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
6083: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6078: IF l_debug_on THEN
6079: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6080: END IF;
6081: --
6082: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
6083: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6084: wsh_util_core.add_message(x_return_status);
6085: l_stop_num_warn := l_stop_num_warn + 1;
6086: END IF;
6151: WSH_DEBUG_SV.log(l_module_name,'l_total_drop_off_volume', l_total_drop_off_volume);
6152: END IF;
6153:
6154:
6155: UPDATE wsh_trip_stops
6156: SET pick_up_weight = l_total_pick_up_weight,
6157: drop_off_weight = l_total_drop_off_weight,
6158: pick_up_volume = l_total_pick_up_volume,
6159: drop_off_volume = l_total_drop_off_volume
6168:
6169: --R12 MDC
6170: --UPDATE existing weight/volume columns only if frozen flag is Not Y
6171: IF NOT (p_calc_wv_if_frozen = 'N' and l_wv_frozen_flag = 'Y') THEN
6172: UPDATE wsh_trip_stops
6173: SET departure_gross_weight = l_total_gross_weight,
6174: departure_net_weight = l_total_net_weight,
6175: departure_volume = l_total_volume,
6176: departure_fill_percent = l_stop_fill_percent,
6325: SELECT stop_id,
6326: stop_sequence_number,
6327: planned_arrival_date,
6328: planned_departure_date
6329: FROM wsh_trip_stops
6330: WHERE trip_id = p_trip_id
6331: ORDER BY stop_sequence_number;
6332:
6333: l_old_arrival_date DATE;
6484:
6485: -- bug 2374603: we cannot unassign from PLANNED trips
6486: --J can assign from planned trip
6487: stmt_str := 'select st.trip_id, count(*) '||
6488: 'from wsh_trip_stops st, wsh_delivery_legs dg, wsh_trips tr '||
6489: 'where dg.pick_up_stop_id = st.stop_id AND '||
6490: 'tr.trip_id = st.trip_id AND tr.planned_flag IN (''N'',''Y'') AND ' ||
6491: 'dg.delivery_id IN (' || del_str || ') ' ||
6492: 'group by st.trip_id '||
6542: WHERE trip_id = p_trip_id;
6543:
6544:
6545: cursor get_consol_child_deliveries(p_trip_id in number, p_delivery_id in number) is
6546: select l.delivery_id from wsh_delivery_legs l, wsh_trip_stops s
6547: where l.delivery_id = p_delivery_id
6548: and l.parent_delivery_leg_id is not null
6549: and l.pick_up_stop_id = s.stop_id
6550: and s.trip_id = p_trip_id;
6795: p_volume IN NUMBER,
6796: p_fill_percent IN NUMBER,
6797: x_return_status OUT NOCOPY VARCHAR2) IS
6798:
6799: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
6800: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
6801: l_return_status VARCHAR2(1);
6802:
6803: l_debug_on BOOLEAN;
6821: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6822:
6823: -- Get pvt type record structure for stop
6824: IF l_debug_on THEN
6825: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
6826: END IF;
6827: --
6828: wsh_trip_stops_grp.get_stop_details_pvt(
6829: p_stop_id => p_stop_id,
6824: IF l_debug_on THEN
6825: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
6826: END IF;
6827: --
6828: wsh_trip_stops_grp.get_stop_details_pvt(
6829: p_stop_id => p_stop_id,
6830: x_stop_rec => l_stop_rec,
6831: x_return_status => l_return_status);
6832:
6903: IS
6904:
6905: CURSOR lock_stop(c_stop_id NUMBER) IS
6906: SELECT stop_id
6907: FROM wsh_trip_stops
6908: WHERE stop_id = c_stop_id
6909: FOR UPDATE NOWAIT;
6910:
6911: CURSOR c_trip_stops (c_trip_id NUMBER )IS
6915: wst.planned_arrival_date,
6916: wst.planned_departure_date,
6917: wst.actual_arrival_date,
6918: wst.actual_departure_date
6919: FROM wsh_trip_stops wst,
6920: wsh_trips wtp
6921: WHERE wtp.status_code in ('OP', 'IT')
6922: AND wtp.trip_id = c_trip_id
6923: AND wtp.trip_id = wst.trip_id
7029: BEGIN
7030: -- lock the trip stop
7031: OPEN lock_stop(l_stop_rec.stop_id);
7032: FETCH lock_stop INTO l_locked_stop_id;
7033: UPDATE wsh_trip_stops
7034: SET planned_arrival_date = l_stop_rec.planned_arrival_date,
7035: planned_departure_date = l_stop_rec.planned_departure_date,
7036: last_update_date = SYSDATE,
7037: last_updated_by = FND_GLOBAL.user_id,
7038: last_update_login = FND_GLOBAL.login_id
7039: WHERE stop_id = l_stop_rec.stop_id;
7040:
7041: -- DBI Project
7042: -- Updating WSH_TRIP_STOPS.
7043: -- Call DBI API after the Update.
7044: -- This API will also check for DBI Installed or not
7045: IF l_debug_on THEN
7046: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',l_stop_rec.stop_id);
7398: 0 -- linked to a non-existent physical stop
7399: ),
7400: 1, 'N',
7401: 'Y') BREAK_LINK_FLAG
7402: FROM wsh_trip_stops wts,
7403: wsh_trip_stops pts
7404: WHERE wts.trip_id = c_trip_id
7405: and wts.physical_stop_id = pts.stop_id(+)
7406: ORDER BY
7399: ),
7400: 1, 'N',
7401: 'Y') BREAK_LINK_FLAG
7402: FROM wsh_trip_stops wts,
7403: wsh_trip_stops pts
7404: WHERE wts.trip_id = c_trip_id
7405: and wts.physical_stop_id = pts.stop_id(+)
7406: ORDER BY
7407: -- we need to order by PAD;
7515: 0 -- linked to a non-existent physical stop
7516: ),
7517: 1, 'N',
7518: 'Y') BREAK_LINK_FLAG
7519: FROM wsh_trip_stops wts,
7520: wsh_trip_stops pts
7521: WHERE wts.trip_id = c_trip_id
7522: and wts.physical_stop_id = pts.stop_id(+)
7523: ORDER BY
7516: ),
7517: 1, 'N',
7518: 'Y') BREAK_LINK_FLAG
7519: FROM wsh_trip_stops wts,
7520: wsh_trip_stops pts
7521: WHERE wts.trip_id = c_trip_id
7522: and wts.physical_stop_id = pts.stop_id(+)
7523: ORDER BY
7524: -- we need to order by SSN;
7575: l_update_flag BOOLEAN;
7576: l_push_date_allowed BOOLEAN;
7577: l_push_ssn_allowed BOOLEAN;
7578: l_return_status VARCHAR2(1);
7579: l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;
7580: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Handle_Internal_Stops';
7581: stop_locked EXCEPTION;
7582: get_physical_loc_err EXCEPTION;
7583: reset_stop_seq_number_error EXCEPTION;
7890: IF l_debug_on THEN
7891: WSH_DEBUG_SV.log(l_module_name,'updating stops ' , l_getstops_stop_id.count);
7892: END IF;
7893: FORALL j IN l_getstops_stop_id.first..l_getstops_stop_id.last
7894: UPDATE WSH_TRIP_STOPS
7895: SET
7896: PHYSICAL_LOCATION_ID = l_getstops_phys_loc_id(j),
7897: PHYSICAL_STOP_ID = l_getstops_phys_stop_id(j),
7898: PLANNED_ARRIVAL_DATE = l_getstops_pl_arr_date(j),
7906: WSH_DEBUG_SV.log(l_module_name,'done update' , l_getstops_stop_id.count);
7907: END IF;
7908: --
7909: -- DBI Project
7910: -- Updating WSH_TRIP_STOPS.
7911: -- Call DBI API after the Update.
7912: -- This API will also check for DBI Installed or not
7913: IF l_debug_on THEN
7914: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop count -',l_getstops_stop_id.count);
7941: l_stop_details_rec.trip_id := p_trip_ids(i);
7942: --l_stop_details_rec.stop_id := NULL;
7943: --l_stop_details_rec.stop_sequence_number := NULL;
7944:
7945: WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS(
7946: p_stop_details_rec => l_stop_details_rec,
7947: x_return_status => l_return_status);
7948:
7949: IF l_debug_on THEN
7946: p_stop_details_rec => l_stop_details_rec,
7947: x_return_status => l_return_status);
7948:
7949: IF l_debug_on THEN
7950: wsh_debug_sv.log(l_module_name,'Return Status From WWSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS for trip '||p_trip_ids(i) ,l_return_status);
7951: END IF;
7952:
7953: IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7954: raise reset_stop_seq_number_error;
7960: p_trip_id => p_trip_ids(i),
7961: x_return_status => l_return_status);
7962:
7963: IF l_debug_on THEN
7964: wsh_debug_sv.log(l_module_name,'Return Status From WSH_TRIP_STOPS_VALIDATIONS.Validate_Stop_Dates for trip '||p_trip_ids(i) ,l_return_status);
7965: END IF;
7966:
7967: IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7968: raise validate_stop_date_error;
8128: , wts.pick_up_weight
8129: , wts.weight_uom_code
8130: , wts.pick_up_volume
8131: , wts.volume_uom_code
8132: FROM wsh_trip_stops wts
8133: WHERE wts.trip_id = c_tripid
8134: order by wts.STOP_SEQUENCE_NUMBER;
8135:
8136: CURSOR c_get_firststop_dlvy(c_stop_id IN NUMBER) IS
8138: , wnd.CUSTOMER_ID
8139: , wnd.ORGANIZATION_ID
8140: FROM wsh_new_deliveries wnd,
8141: wsh_delivery_legs wdl,
8142: wsh_trip_stops wts1
8143: WHERE wnd.delivery_id = wdl.delivery_id
8144: AND wdl.pick_up_stop_id = wts1.stop_id
8145: AND wnd.initial_pickup_location_id = wts1.stop_location_id
8146: AND nvl(wnd.shipping_control,'BUYER') <> 'SUPPLIER'
8151: , wnd.CUSTOMER_ID
8152: , wnd.ORGANIZATION_ID
8153: FROM wsh_new_deliveries wnd,
8154: wsh_delivery_legs wdl,
8155: wsh_trip_stops wts1
8156: WHERE wnd.delivery_id = wdl.delivery_id
8157: AND wdl.drop_off_stop_id = wts1.stop_id
8158: AND wnd.ultimate_dropoff_location_id = wts1.stop_location_id
8159: AND nvl(wnd.shipping_control,'BUYER') <> 'SUPPLIER'
8892: x_return_status OUT NOCOPY VARCHAR2) IS
8893:
8894: cursor c_get_trip_deliveries(p_trip_id in number) is
8895: select d.delivery_id, d.delivery_type
8896: from wsh_new_deliveries d, wsh_trip_stops s, wsh_delivery_legs l
8897: where d.delivery_id = l.delivery_id
8898: and l.pick_up_stop_id = s.stop_id
8899: and s.trip_id = p_trip_id
8900: order by d.delivery_type;