eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.WSH_TRIPS_ACTIONS dependencies on WSH_TRIP_STOPS

Line 95: from wsh_trip_stops st,

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: -- Bug 9002479 : Select '1' changed to Select 1

Line 113: from wsh_trip_stops st, wsh_delivery_legs dg, wsh_new_deliveries dl

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;

Line 289: from wsh_trip_stops

285: x_return_status OUT NOCOPY VARCHAR2) IS
286:
287: cursor get_stops (c_stop_id NUMBER) is
288: select stop_id
289: from wsh_trip_stops
290: where trip_id = p_trip_id
291: and stop_id = NVL(c_stop_id, stop_id)
292: and status_code IN ('OP','AR')
293: and nvl(SHIPMENTS_TYPE_FLAG, 'O') IN ('O', 'M') -- J Inbound Logistics jckwok

Line 298: from wsh_trip_stops t,

294: order by stop_sequence_number asc ;
295:
296: cursor get_pickup_stop is
297: select t.stop_id
298: from wsh_trip_stops t,
299: wsh_delivery_legs dg
300: where t.trip_id = p_trip_id
301: and dg.pick_up_stop_id = t.stop_id
302: and t.status_code IN ('OP','AR')

Line 317: wsh_trip_stops st,

313: CURSOR c_stop_del_status(p_stop_id NUMBER) IS
314: SELECT 1
315: FROM wsh_new_deliveries dl,
316: wsh_delivery_legs dg,
317: wsh_trip_stops st,
318: wsh_trips t
319: WHERE dl.delivery_id = dg.delivery_id AND
320: (dg.pick_up_stop_id = st.stop_id OR dg.drop_off_stop_id = st.stop_id) AND
321: st.trip_id = t.trip_id AND

Line 517: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_ACTIONS.Confirm_Stop',WSH_DEBUG_SV.C_PROC_LEVEL);

513: END IF;
514:
515: --
516: IF l_debug_on THEN
517: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_ACTIONS.Confirm_Stop',WSH_DEBUG_SV.C_PROC_LEVEL);
518: END IF;
519: --
520: WSH_TRIP_STOPS_ACTIONS.Confirm_Stop (
521: p_stop_id => stop_rec.stop_id,

Line 520: WSH_TRIP_STOPS_ACTIONS.Confirm_Stop (

516: IF l_debug_on THEN
517: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_ACTIONS.Confirm_Stop',WSH_DEBUG_SV.C_PROC_LEVEL);
518: END IF;
519: --
520: WSH_TRIP_STOPS_ACTIONS.Confirm_Stop (
521: p_stop_id => stop_rec.stop_id,
522: p_action_flag => p_action_flag,
523: p_intransit_flag => p_intransit_flag,
524: p_close_flag => p_close_flag,

Line 721: from wsh_trip_stops

717: where trip_id = c_trip_id;
718:
719: cursor get_stops(c_trip_id IN NUMBER) is
720: select 'Y'
721: from wsh_trip_stops
722: where trip_id = c_trip_id
723: and status_code <> 'OP'
724: and rownum = 1;
725:

Line 919: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;

915: x_return_status OUT NOCOPY VARCHAR2) IS
916:
917: i BINARY_INTEGER;
918: /* H integration for Multi Leg */
919: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
920: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
921: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
922: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
923: l_return_status VARCHAR2(30);

Line 920: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;

916:
917: i BINARY_INTEGER;
918: /* H integration for Multi Leg */
919: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
920: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
921: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
922: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
923: l_return_status VARCHAR2(30);
924: l_num_warn NUMBER := 0;

Line 1200: FROM wsh_delivery_legs wdl, wsh_trip_stops wts

1196: --{
1197: --- TP release
1198: CURSOR c_getdels IS
1199: SELECT delivery_id
1200: FROM wsh_delivery_legs wdl, wsh_trip_stops wts
1201: WHERE wdl.pick_up_stop_id=wts.stop_id AND
1202: wts.trip_id=p_in_rec.trip_id;
1203:
1204:

Line 1206: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;

1202: wts.trip_id=p_in_rec.trip_id;
1203:
1204:
1205: /* H integration for Multi Leg */
1206: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
1207: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
1208: --
1209: --
1210: l_num_warnings NUMBER;

Line 1544: FROM wsh_trip_stops wts,

1540: --
1541: CURSOR dlvy_csr(p_trip_id NUMBER)
1542: IS
1543: SELECT wdl.delivery_id, wt.name
1544: FROM wsh_trip_stops wts,
1545: wsh_Delivery_legs wdl,
1546: wsh_new_deliveries wnd,
1547: wsh_trips wt
1548: WHERE wt.trip_id = p_trip_id

Line 2529: FROM wsh_trip_stops

2525: SELECT stop_id,
2526: planned_departure_date,
2527: planned_arrival_date,
2528: NVL(shipments_type_flag,'O') shipments_type_flag --J-IB-NPARIKH
2529: FROM wsh_trip_stops
2530: WHERE trip_id = p_trip_id
2531: ORDER BY stop_sequence_number; -- J-IB-NPARIKH
2532:
2533: l_delivery_id NUMBER;

Line 2559: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;

2555: l_good_dels WSH_UTIL_CORE.ID_TAB_TYPE;
2556:
2557: /* H integration for Multi Leg */
2558: l_num_warn NUMBER := 0;
2559: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
2560: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
2561: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
2562: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
2563: l_grp_trip_rec WSH_TRIPS_GRP.TRIP_PUB_REC_TYPE;

Line 2560: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;

2556:
2557: /* H integration for Multi Leg */
2558: l_num_warn NUMBER := 0;
2559: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
2560: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
2561: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
2562: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
2563: l_grp_trip_rec WSH_TRIPS_GRP.TRIP_PUB_REC_TYPE;
2564: --l_return_status VARCHAR2(30);

Line 2570: l_stop_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;

2566: --
2567: l_debug_on BOOLEAN;
2568: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTOCREATE_TRIP';
2569: --
2570: l_stop_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;
2571: l_stop_processed VARCHAR2(10);
2572: -- Bug 3413364, skip the delivery if it does not have both initial pickup date and ultimate dropoff date
2573: l_initial_pickup_date DATE;
2574: l_ultimate_dropoff_date DATE;

Line 2844: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);

2840: -- H integration
2841: -- Get pvt type record structure for stop
2842: --
2843: IF l_debug_on THEN
2844: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2845: END IF;
2846: --
2847: wsh_trip_stops_grp.get_stop_details_pvt
2848: (p_stop_id => st.stop_id,

Line 2847: wsh_trip_stops_grp.get_stop_details_pvt

2843: IF l_debug_on THEN
2844: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2845: END IF;
2846: --
2847: wsh_trip_stops_grp.get_stop_details_pvt
2848: (p_stop_id => st.stop_id,
2849: x_stop_rec => l_stop_rec,
2850: x_return_status => l_return_status);
2851: --

Line 2898: UPDATE wsh_trip_stops

2894: END IF;
2895: --
2896: -- End of H integration
2897: --
2898: UPDATE wsh_trip_stops
2899: SET planned_departure_date = nvl(planned_arrival_date, SYSDATE)
2900: WHERE stop_id = st.stop_id;
2901: --}
2902: END IF;

Line 2912: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);

2908: -- H integration
2909: -- Get pvt type record structure for stop
2910: --
2911: IF l_debug_on THEN
2912: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2913: END IF;
2914: --
2915: wsh_trip_stops_grp.get_stop_details_pvt
2916: (p_stop_id => st.stop_id,

Line 2915: wsh_trip_stops_grp.get_stop_details_pvt

2911: IF l_debug_on THEN
2912: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2913: END IF;
2914: --
2915: wsh_trip_stops_grp.get_stop_details_pvt
2916: (p_stop_id => st.stop_id,
2917: x_stop_rec => l_stop_rec,
2918: x_return_status => l_return_status);
2919: --

Line 2963: UPDATE wsh_trip_stops

2959: --}
2960: END IF;
2961: --
2962: -- End of H integration
2963: UPDATE wsh_trip_stops
2964: SET planned_arrival_date = nvl(planned_departure_date, SYSDATE)
2965: WHERE stop_id = st.stop_id;
2966: --
2967: -- DBI Project

Line 2968: -- Updating WSH_TRIP_STOPS.

2964: SET planned_arrival_date = nvl(planned_departure_date, SYSDATE)
2965: WHERE stop_id = st.stop_id;
2966: --
2967: -- DBI Project
2968: -- Updating WSH_TRIP_STOPS.
2969: -- Call DBI API after the Update.
2970: -- This API will also check for DBI Installed or not
2971: IF l_debug_on THEN
2972: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',st.stop_id);

Line 3156: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;

3152: --dummy tables for calling validate_constraint_mainper
3153: l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
3154: l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
3155: l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
3156: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
3157: l_cc_in_ids wsh_util_core.id_tab_type;
3158: l_cc_fail_ids wsh_util_core.id_tab_type;
3159:
3160: CURSOR del_cur(p_dlvy_id NUMBER) IS

Line 3974: FROM wsh_trip_stops st,

3970:
3971: CURSOR pickup_leg_check (l_delivery_id IN NUMBER) IS
3972: SELECT dg.delivery_leg_id,
3973: st.stop_id
3974: FROM wsh_trip_stops st,
3975: wsh_delivery_legs dg
3976: WHERE dg.delivery_id = l_delivery_id AND
3977: dg.pick_up_stop_id = st.stop_id AND
3978: st.stop_location_id = p_pickup_location_id;

Line 3983: FROM wsh_trip_stops st,

3979:
3980: CURSOR dropoff_leg_check (l_delivery_id IN NUMBER) IS
3981: SELECT dg.delivery_leg_id,
3982: st.stop_id
3983: FROM wsh_trip_stops st,
3984: wsh_delivery_legs dg
3985: WHERE dg.delivery_id = l_delivery_id AND
3986: dg.drop_off_stop_id = st.stop_id AND
3987: st.stop_location_id = p_dropoff_location_id;

Line 4003: l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;

3999: --
4000: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_ASSIGN_TRIP';
4001: --
4002: -- patchset J csun Stop Sequence Change
4003: l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;
4004: l_return_status VARCHAR2(1);
4005:
4006: BEGIN
4007:

Line 4249: FROM wsh_trip_stops

4245: l_debug_on BOOLEAN;
4246:
4247: CURSOR get_stop_location (l_trip_stop_id IN NUMBER) IS
4248: SELECT stop_location_id
4249: FROM wsh_trip_stops
4250: WHERE stop_id = l_trip_stop_id;
4251:
4252: CURSOR c_any_activity_exists(p_stop_id IN NUMBER) IS
4253: SELECT '1'

Line 4260: FROM wsh_trip_stops

4256: AND rownum=1;
4257:
4258: CURSOR c_get_stops IS
4259: SELECT stop_id, stop_location_id, status_code
4260: FROM wsh_trip_stops
4261: WHERE trip_id=p_trip_id
4262: ORDER BY stop_sequence_number;
4263:
4264: CURSOR delivery_info (del_id IN NUMBER) IS

Line 4317: FROM wsh_trip_stops ts1, -- get this stop's SSN

4313: p_stop_id IN NUMBER) RETURN NUMBER IS
4314: CURSOR c_stop_seqs IS
4315: -- get the SSNs of this stop and the next stop.
4316: SELECT ts2.stop_sequence_number
4317: FROM wsh_trip_stops ts1, -- get this stop's SSN
4318: wsh_trip_stops ts2 -- then look for SSN and next SSN
4319: WHERE ts1.stop_id = p_stop_id
4320: AND ts2.trip_id = p_trip_id
4321: AND ts2.stop_sequence_number >= ts1.stop_sequence_number

Line 4318: wsh_trip_stops ts2 -- then look for SSN and next SSN

4314: CURSOR c_stop_seqs IS
4315: -- get the SSNs of this stop and the next stop.
4316: SELECT ts2.stop_sequence_number
4317: FROM wsh_trip_stops ts1, -- get this stop's SSN
4318: wsh_trip_stops ts2 -- then look for SSN and next SSN
4319: WHERE ts1.stop_id = p_stop_id
4320: AND ts2.trip_id = p_trip_id
4321: AND ts2.stop_sequence_number >= ts1.stop_sequence_number
4322: AND rownum <= 2

Line 4481: UPDATE wsh_trip_stops

4477: IF l_debug_on THEN
4478: WSH_DEBUG_SV.logmsg(l_module_name,'Caller is FTE/TP changing pickup loc to be l_phys_trip_pickup_loc_id');
4479: END IF;
4480:
4481: UPDATE wsh_trip_stops
4482: SET stop_location_id= l_phys_trip_pickup_loc_id,
4483: physical_location_id=null,
4484: last_update_date = sysdate,
4485: last_updated_by = FND_GLOBAL.USER_ID

Line 4664: UPDATE wsh_trip_stops

4660: IF l_debug_on THEN
4661: WSH_DEBUG_SV.log(l_module_name,'changing stop location to internal location l_dropoff_location_id',l_dropoff_location_id);
4662: END IF;
4663:
4664: UPDATE wsh_trip_stops
4665: SET stop_location_id= l_dropoff_location_id,
4666: physical_location_id=l_trip_dropoff_loc_id,
4667: last_update_date = sysdate,
4668: last_updated_by = FND_GLOBAL.USER_ID

Line 4773: FROM wsh_trip_stops st,

4769: ) IS
4770:
4771: CURSOR get_pickup_stop (l_loc_id IN NUMBER, l_del_id IN NUMBER) IS
4772: SELECT st.stop_id
4773: FROM wsh_trip_stops st,
4774: wsh_delivery_legs dg
4775: WHERE st.stop_location_id = l_loc_id AND
4776: dg.pick_up_stop_id = st.stop_id AND
4777: dg.delivery_id = l_del_id;

Line 4781: FROM wsh_trip_stops st,

4777: dg.delivery_id = l_del_id;
4778:
4779: CURSOR get_dropoff_stop (l_loc_id IN NUMBER, l_del_id IN NUMBER) IS
4780: SELECT st.stop_id
4781: FROM wsh_trip_stops st,
4782: wsh_delivery_legs dg
4783: WHERE st.stop_location_id = l_loc_id AND
4784: dg.drop_off_stop_id = st.stop_id AND
4785: dg.delivery_id = l_del_id;

Line 4794: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;

4790:
4791: stop_not_found EXCEPTION;
4792: others EXCEPTION;
4793: /* H integration for Multi Leg */
4794: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
4795: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
4796: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
4797: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
4798: l_num_warn NUMBER := 0;

Line 4795: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;

4791: stop_not_found EXCEPTION;
4792: others EXCEPTION;
4793: /* H integration for Multi Leg */
4794: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
4795: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
4796: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
4797: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
4798: l_num_warn NUMBER := 0;
4799: l_num_err NUMBER := 0;

Line 4808: l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;

4804: --
4805: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ASSIGN_TRIP';
4806: --
4807: -- patchset J csun Stop Sequence Change
4808: l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;
4809:
4810: l_del_rows wsh_util_core.id_tab_type;
4811: l_pickup_stop_id NUMBER;
4812: l_dropoff_stop_id NUMBER;

Line 5068: WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS(

5064: -- SSN change
5065: -- Call Reset_Stop_Seq_Numbers only if mode = PAD
5066: IF l_stop_seq_mode = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_PAD THEN
5067: -- need to resequence the stops' SSNs and validate their dates.
5068: WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS(
5069: p_stop_details_rec => l_stop_details_rec,
5070: x_return_status => l_return_status );
5071:
5072: IF l_debug_on THEN

Line 5073: WSH_DEBUG_SV.log(l_module_name,'return status from WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS',l_return_status);

5069: p_stop_details_rec => l_stop_details_rec,
5070: x_return_status => l_return_status );
5071:
5072: IF l_debug_on THEN
5073: WSH_DEBUG_SV.log(l_module_name,'return status from WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS',l_return_status);
5074: END IF;
5075:
5076: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5077: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN

Line 5103: 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);

5099: p_caller => p_caller
5100: );
5101:
5102: IF l_debug_on THEN
5103: 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);
5104: END IF;
5105:
5106: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5107: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN

Line 5204: FROM wsh_trip_stops

5200: CURSOR c_get_stop_trip(c_stop_id IN NUMBER) IS
5201: SELECT trip_id,
5202: weight_uom_code,
5203: volume_uom_code
5204: FROM wsh_trip_stops
5205: WHERE stop_id = c_stop_id;
5206:
5207: CURSOR trip_vehicle_info (c_trip_id NUMBER) IS
5208: SELECT vehicle_item_id,

Line 5409: FROM wsh_trip_stops

5405: departure_net_weight,
5406: departure_volume,
5407: nvl(shipments_type_flag,'O'),
5408: nvl(wv_frozen_flag,'Y')
5409: FROM wsh_trip_stops
5410: WHERE trip_id = l_trip_id AND
5411: nvl(planned_departure_date, nvl(p_start_departure_date, FND_API.G_MISS_DATE)) >= nvl(p_start_departure_date, FND_API.G_MISS_DATE)
5412: ORDER BY stop_sequence_number;
5413:

Line 5417: FROM wsh_trip_stops

5413:
5414:
5415: CURSOR lock_stop(c_stop_id NUMBER) IS
5416: SELECT stop_id
5417: FROM wsh_trip_stops
5418: WHERE stop_id = c_stop_id
5419: FOR UPDATE NOWAIT;
5420:
5421:

Line 5429: FROM wsh_trip_stops

5425: departure_net_weight,
5426: departure_volume,
5427: weight_uom_code,
5428: volume_uom_code
5429: FROM wsh_trip_stops
5430: WHERE trip_id = l_trip_id AND
5431: 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
5432: rownum = 1
5433: ORDER BY stop_sequence_number DESC;

Line 5437: FROM wsh_trip_stops

5433: ORDER BY stop_sequence_number DESC;
5434:
5435: CURSOR last_stop (l_trip_id NUMBER) IS
5436: SELECT stop_id
5437: FROM wsh_trip_stops
5438: WHERE trip_id = l_trip_id
5439: AND stop_sequence_number = ( SELECT MAX(wts.stop_sequence_number)
5440: FROM wsh_trip_stops wts
5441: WHERE wts.trip_id = l_trip_id );

Line 5440: FROM wsh_trip_stops wts

5436: SELECT stop_id
5437: FROM wsh_trip_stops
5438: WHERE trip_id = l_trip_id
5439: AND stop_sequence_number = ( SELECT MAX(wts.stop_sequence_number)
5440: FROM wsh_trip_stops wts
5441: WHERE wts.trip_id = l_trip_id );
5442:
5443: CURSOR pickup_deliveries (l_stop_id IN NUMBER) IS
5444: SELECT dl.delivery_id d_id,

Line 5451: FROM wsh_trip_stops t,

5447: dl.gross_weight,
5448: dl.net_weight,
5449: dl.volume,
5450: dl.organization_id
5451: FROM wsh_trip_stops t,
5452: wsh_delivery_legs dg,
5453: wsh_new_deliveries dl
5454: WHERE t.stop_id = l_stop_id AND
5455: dg.pick_up_stop_id = t.stop_id AND

Line 5462: FROM wsh_trip_stops t,

5458:
5459: CURSOR dropoff_deliveries (l_stop_id NUMBER) IS
5460: SELECT dl.delivery_id d_id,
5461: dl.organization_id
5462: FROM wsh_trip_stops t,
5463: wsh_delivery_legs dg,
5464: wsh_new_deliveries dl
5465: WHERE t.stop_id = l_stop_id AND
5466: dg.drop_off_stop_id = t.stop_id AND

Line 5548: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;

5544: g_return_status VARCHAR2(1);
5545: l_locked_stop_id NUMBER := 0;
5546: others EXCEPTION;
5547: /* H integration for Multi Leg */
5548: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
5549: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
5550:
5551: --R12 MDC
5552: l_total_pick_up_weight NUMBER := 0;

Line 5726: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);

5722: --
5723: -- Debug Statements
5724: --
5725: IF l_debug_on THEN
5726: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5727: END IF;
5728: --
5729: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_start_stop_id,p_caller));
5730: -- bug 2732503 , need a warning message only when FTE is installed

Line 5729: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_start_stop_id,p_caller));

5725: IF l_debug_on THEN
5726: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5727: END IF;
5728: --
5729: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_start_stop_id,p_caller));
5730: -- bug 2732503 , need a warning message only when FTE is installed
5731: -- still will go to wt_vol_error
5732: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5733: wsh_util_core.add_message(x_return_status);

Line 5795: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);

5791: --
5792: -- Debug Statements
5793: --
5794: IF l_debug_on THEN
5795: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5796: END IF;
5797: --
5798: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
5799: -- bug 2732503 , need a warning message only when FTE is installed

Line 5798: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));

5794: IF l_debug_on THEN
5795: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5796: END IF;
5797: --
5798: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
5799: -- bug 2732503 , need a warning message only when FTE is installed
5800: -- still will go to wt_vol_error
5801: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5802: wsh_util_core.add_message(x_return_status);

Line 6090: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);

6086: --
6087: -- Debug Statements
6088: --
6089: IF l_debug_on THEN
6090: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6091: END IF;
6092: --
6093: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
6094: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;

Line 6093: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));

6089: IF l_debug_on THEN
6090: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6091: END IF;
6092: --
6093: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
6094: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6095: wsh_util_core.add_message(x_return_status);
6096: l_stop_num_warn := l_stop_num_warn + 1;
6097: END IF;

Line 6166: UPDATE wsh_trip_stops

6162: WSH_DEBUG_SV.log(l_module_name,'l_total_drop_off_volume', l_total_drop_off_volume);
6163: END IF;
6164:
6165:
6166: UPDATE wsh_trip_stops
6167: SET pick_up_weight = l_total_pick_up_weight,
6168: drop_off_weight = l_total_drop_off_weight,
6169: pick_up_volume = l_total_pick_up_volume,
6170: drop_off_volume = l_total_drop_off_volume

Line 6183: UPDATE wsh_trip_stops

6179:
6180: --R12 MDC
6181: --UPDATE existing weight/volume columns only if frozen flag is Not Y
6182: IF NOT (p_calc_wv_if_frozen = 'N' and l_wv_frozen_flag = 'Y') THEN
6183: UPDATE wsh_trip_stops
6184: SET departure_gross_weight = l_total_gross_weight,
6185: departure_net_weight = l_total_net_weight,
6186: departure_volume = l_total_volume,
6187: departure_fill_percent = l_stop_fill_percent,

Line 6340: FROM wsh_trip_stops

6336: SELECT stop_id,
6337: stop_sequence_number,
6338: planned_arrival_date,
6339: planned_departure_date
6340: FROM wsh_trip_stops
6341: WHERE trip_id = p_trip_id
6342: ORDER BY stop_sequence_number;
6343:
6344: l_old_arrival_date DATE;

Line 6499: 'from wsh_trip_stops st, wsh_delivery_legs dg, wsh_trips tr '||

6495:
6496: -- bug 2374603: we cannot unassign from PLANNED trips
6497: --J can assign from planned trip
6498: stmt_str := 'select st.trip_id, count(*) '||
6499: 'from wsh_trip_stops st, wsh_delivery_legs dg, wsh_trips tr '||
6500: 'where dg.pick_up_stop_id = st.stop_id AND '||
6501: 'tr.trip_id = st.trip_id AND tr.planned_flag IN (''N'',''Y'') AND ' ||
6502: 'dg.delivery_id IN (' || del_str || ') ' ||
6503: 'group by st.trip_id '||

Line 6557: select l.delivery_id from wsh_delivery_legs l, wsh_trip_stops s

6553: WHERE trip_id = p_trip_id;
6554:
6555:
6556: cursor get_consol_child_deliveries(p_trip_id in number, p_delivery_id in number) is
6557: select l.delivery_id from wsh_delivery_legs l, wsh_trip_stops s
6558: where l.delivery_id = p_delivery_id
6559: and l.parent_delivery_leg_id is not null
6560: and l.pick_up_stop_id = s.stop_id
6561: and s.trip_id = p_trip_id;

Line 6810: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;

6806: p_volume IN NUMBER,
6807: p_fill_percent IN NUMBER,
6808: x_return_status OUT NOCOPY VARCHAR2) IS
6809:
6810: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
6811: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
6812: l_return_status VARCHAR2(1);
6813:
6814: l_debug_on BOOLEAN;

Line 6836: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);

6832: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6833:
6834: -- Get pvt type record structure for stop
6835: IF l_debug_on THEN
6836: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
6837: END IF;
6838: --
6839: wsh_trip_stops_grp.get_stop_details_pvt(
6840: p_stop_id => p_stop_id,

Line 6839: wsh_trip_stops_grp.get_stop_details_pvt(

6835: IF l_debug_on THEN
6836: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
6837: END IF;
6838: --
6839: wsh_trip_stops_grp.get_stop_details_pvt(
6840: p_stop_id => p_stop_id,
6841: x_stop_rec => l_stop_rec,
6842: x_return_status => l_return_status);
6843:

Line 6918: FROM wsh_trip_stops

6914: IS
6915:
6916: CURSOR lock_stop(c_stop_id NUMBER) IS
6917: SELECT stop_id
6918: FROM wsh_trip_stops
6919: WHERE stop_id = c_stop_id
6920: FOR UPDATE NOWAIT;
6921:
6922: CURSOR c_trip_stops (c_trip_id NUMBER )IS

Line 6930: FROM wsh_trip_stops wst,

6926: wst.planned_arrival_date,
6927: wst.planned_departure_date,
6928: wst.actual_arrival_date,
6929: wst.actual_departure_date
6930: FROM wsh_trip_stops wst,
6931: wsh_trips wtp
6932: WHERE wtp.status_code in ('OP', 'IT')
6933: AND wtp.trip_id = c_trip_id
6934: AND wtp.trip_id = wst.trip_id

Line 7044: UPDATE wsh_trip_stops

7040: BEGIN
7041: -- lock the trip stop
7042: OPEN lock_stop(l_stop_rec.stop_id);
7043: FETCH lock_stop INTO l_locked_stop_id;
7044: UPDATE wsh_trip_stops
7045: SET planned_arrival_date = l_stop_rec.planned_arrival_date,
7046: planned_departure_date = l_stop_rec.planned_departure_date,
7047: last_update_date = SYSDATE,
7048: last_updated_by = FND_GLOBAL.user_id,

Line 7053: -- Updating WSH_TRIP_STOPS.

7049: last_update_login = FND_GLOBAL.login_id
7050: WHERE stop_id = l_stop_rec.stop_id;
7051:
7052: -- DBI Project
7053: -- Updating WSH_TRIP_STOPS.
7054: -- Call DBI API after the Update.
7055: -- This API will also check for DBI Installed or not
7056: IF l_debug_on THEN
7057: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',l_stop_rec.stop_id);

Line 7413: FROM wsh_trip_stops wts,

7409: 0 -- linked to a non-existent physical stop
7410: ),
7411: 1, 'N',
7412: 'Y') BREAK_LINK_FLAG
7413: FROM wsh_trip_stops wts,
7414: wsh_trip_stops pts
7415: WHERE wts.trip_id = c_trip_id
7416: and wts.physical_stop_id = pts.stop_id(+)
7417: ORDER BY

Line 7414: wsh_trip_stops pts

7410: ),
7411: 1, 'N',
7412: 'Y') BREAK_LINK_FLAG
7413: FROM wsh_trip_stops wts,
7414: wsh_trip_stops pts
7415: WHERE wts.trip_id = c_trip_id
7416: and wts.physical_stop_id = pts.stop_id(+)
7417: ORDER BY
7418: -- we need to order by PAD;

Line 7530: FROM wsh_trip_stops wts,

7526: 0 -- linked to a non-existent physical stop
7527: ),
7528: 1, 'N',
7529: 'Y') BREAK_LINK_FLAG
7530: FROM wsh_trip_stops wts,
7531: wsh_trip_stops pts
7532: WHERE wts.trip_id = c_trip_id
7533: and wts.physical_stop_id = pts.stop_id(+)
7534: ORDER BY

Line 7531: wsh_trip_stops pts

7527: ),
7528: 1, 'N',
7529: 'Y') BREAK_LINK_FLAG
7530: FROM wsh_trip_stops wts,
7531: wsh_trip_stops pts
7532: WHERE wts.trip_id = c_trip_id
7533: and wts.physical_stop_id = pts.stop_id(+)
7534: ORDER BY
7535: -- we need to order by SSN;

Line 7590: l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;

7586: l_update_flag BOOLEAN;
7587: l_push_date_allowed BOOLEAN;
7588: l_push_ssn_allowed BOOLEAN;
7589: l_return_status VARCHAR2(1);
7590: l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;
7591: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Handle_Internal_Stops';
7592: stop_locked EXCEPTION;
7593: get_physical_loc_err EXCEPTION;
7594: reset_stop_seq_number_error EXCEPTION;

Line 7905: UPDATE WSH_TRIP_STOPS

7901: IF l_debug_on THEN
7902: WSH_DEBUG_SV.log(l_module_name,'updating stops ' , l_getstops_stop_id.count);
7903: END IF;
7904: FORALL j IN l_getstops_stop_id.first..l_getstops_stop_id.last
7905: UPDATE WSH_TRIP_STOPS
7906: SET
7907: PHYSICAL_LOCATION_ID = l_getstops_phys_loc_id(j),
7908: PHYSICAL_STOP_ID = l_getstops_phys_stop_id(j),
7909: PLANNED_ARRIVAL_DATE = l_getstops_pl_arr_date(j),

Line 7921: -- Updating WSH_TRIP_STOPS.

7917: WSH_DEBUG_SV.log(l_module_name,'done update' , l_getstops_stop_id.count);
7918: END IF;
7919: --
7920: -- DBI Project
7921: -- Updating WSH_TRIP_STOPS.
7922: -- Call DBI API after the Update.
7923: -- This API will also check for DBI Installed or not
7924: IF l_debug_on THEN
7925: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop count -',l_getstops_stop_id.count);

Line 7956: WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS(

7952: l_stop_details_rec.trip_id := p_trip_ids(i);
7953: --l_stop_details_rec.stop_id := NULL;
7954: --l_stop_details_rec.stop_sequence_number := NULL;
7955:
7956: WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS(
7957: p_stop_details_rec => l_stop_details_rec,
7958: x_return_status => l_return_status);
7959:
7960: IF l_debug_on THEN

Line 7961: 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);

7957: p_stop_details_rec => l_stop_details_rec,
7958: x_return_status => l_return_status);
7959:
7960: IF l_debug_on THEN
7961: 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);
7962: END IF;
7963:
7964: IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7965: raise reset_stop_seq_number_error;

Line 7975: 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);

7971: p_trip_id => p_trip_ids(i),
7972: x_return_status => l_return_status);
7973:
7974: IF l_debug_on THEN
7975: 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);
7976: END IF;
7977:
7978: IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7979: raise validate_stop_date_error;

Line 8143: FROM wsh_trip_stops wts

8139: , wts.pick_up_weight
8140: , wts.weight_uom_code
8141: , wts.pick_up_volume
8142: , wts.volume_uom_code
8143: FROM wsh_trip_stops wts
8144: WHERE wts.trip_id = c_tripid
8145: order by wts.STOP_SEQUENCE_NUMBER;
8146:
8147: CURSOR c_get_firststop_dlvy(c_stop_id IN NUMBER) IS

Line 8153: wsh_trip_stops wts1

8149: , wnd.CUSTOMER_ID
8150: , wnd.ORGANIZATION_ID
8151: FROM wsh_new_deliveries wnd,
8152: wsh_delivery_legs wdl,
8153: wsh_trip_stops wts1
8154: WHERE wnd.delivery_id = wdl.delivery_id
8155: AND wdl.pick_up_stop_id = wts1.stop_id
8156: AND wnd.initial_pickup_location_id = wts1.stop_location_id
8157: AND nvl(wnd.shipping_control,'BUYER') <> 'SUPPLIER'

Line 8166: wsh_trip_stops wts1

8162: , wnd.CUSTOMER_ID
8163: , wnd.ORGANIZATION_ID
8164: FROM wsh_new_deliveries wnd,
8165: wsh_delivery_legs wdl,
8166: wsh_trip_stops wts1
8167: WHERE wnd.delivery_id = wdl.delivery_id
8168: AND wdl.drop_off_stop_id = wts1.stop_id
8169: AND wnd.ultimate_dropoff_location_id = wts1.stop_location_id
8170: AND nvl(wnd.shipping_control,'BUYER') <> 'SUPPLIER'

Line 8907: from wsh_new_deliveries d, wsh_trip_stops s, wsh_delivery_legs l

8903: x_return_status OUT NOCOPY VARCHAR2) IS
8904:
8905: cursor c_get_trip_deliveries(p_trip_id in number) is
8906: select d.delivery_id, d.delivery_type
8907: from wsh_new_deliveries d, wsh_trip_stops s, wsh_delivery_legs l
8908: where d.delivery_id = l.delivery_id
8909: and l.pick_up_stop_id = s.stop_id
8910: and s.trip_id = p_trip_id
8911: order by d.delivery_type;