[Home] [Help]
7: g_wms_installed WSH_UTIL_CORE.Column_Tab_Type;
8:
9: CURSOR C_IS_FIRST_LEG(p_trip_id in number, p_delivery_id in number, p_pickup_loc_id in number) is
10: select delivery_leg_id
11: from wsh_delivery_legs l, wsh_trip_stops s
12: where s.trip_id = p_trip_id
13: and l.delivery_id = p_delivery_id
14: and s.stop_id = l.pick_up_stop_id
15: and s.stop_location_id = p_pickup_loc_id;
44: CURSOR stop_exists (l_trip_stop_id IN NUMBER) IS
45: SELECT status_code ,
46: NVL(SHIPMENTS_TYPE_FLAG,'O') SHIPMENTS_TYPE_FLAG, -- J-IB-NPARIKH
47: stop_location_id
48: FROM wsh_trip_stops
49: WHERE stop_id = l_trip_stop_id;
50:
51: CURSOR delivery_info (del_id IN NUMBER) IS
52: SELECT initial_pickup_location_id,
81: CURSOR leg_exists ( del_id IN NUMBER) IS
82: SELECT dg.delivery_leg_id,
83: st1.stop_location_id,
84: st2.stop_location_id
85: FROM wsh_trip_stops st1,
86: wsh_trip_stops st2,
87: wsh_delivery_legs dg
88: WHERE st1.stop_id = dg.pick_up_stop_id AND
89: st2.stop_id = dg.drop_off_stop_id AND
82: SELECT dg.delivery_leg_id,
83: st1.stop_location_id,
84: st2.stop_location_id
85: FROM wsh_trip_stops st1,
86: wsh_trip_stops st2,
87: wsh_delivery_legs dg
88: WHERE st1.stop_id = dg.pick_up_stop_id AND
89: st2.stop_id = dg.drop_off_stop_id AND
90: st1.trip_id = p_trip_id AND
110: l_PLANNED_DEPARTURE_DATE DATE
111: ) IS
112: SELECT stop_id, stop_sequence_number,
113: NVL(SHIPMENTS_TYPE_FLAG,'O') SHIPMENTS_TYPE_FLAG -- J-IB-NPARIKH
114: FROM wsh_trip_stops
115: WHERE stop_location_id = l_location_id AND
116: trip_id = p_trip_id AND
117: status_code <> 'CL'
118: AND PLANNED_ARRIVAL_DATE = NVL(l_PLANNED_ARRIVAL_DATE,PLANNED_ARRIVAL_DATE)
122: CURSOR get_stop (l_location_id IN NUMBER, l_stop_sequence IN NUMBER
123: ) IS
124: SELECT stop_id, stop_sequence_number,
125: NVL(SHIPMENTS_TYPE_FLAG,'O') SHIPMENTS_TYPE_FLAG -- J-IB-NPARIKH
126: FROM wsh_trip_stops
127: WHERE stop_location_id = l_location_id AND
128: trip_id = p_trip_id AND
129: stop_sequence_number = nvl(l_stop_sequence,stop_sequence_number) AND
130: status_code <> 'CL'
136: WHERE dg.delivery_id = del_id;
137:
138: CURSOR get_sequence (l_stop_id NUMBER) IS
139: SELECT stop_sequence_number
140: FROM wsh_trip_stops
141: WHERE stop_id = l_stop_id;
142:
143: CURSOR c_check_dummystops(p_stop_id IN NUMBER, p_trip_id IN NUMBER) IS
144: SELECT 'Y'
141: WHERE stop_id = l_stop_id;
142:
143: CURSOR c_check_dummystops(p_stop_id IN NUMBER, p_trip_id IN NUMBER) IS
144: SELECT 'Y'
145: FROM wsh_trip_stops wts1, wsh_trip_stops wts2
146: WHERE wts1.trip_id=p_trip_id
147: AND wts2.trip_id=p_trip_id
148: AND wts1.stop_id<>wts2.stop_id
149: AND wts1.stop_id=p_stop_id
165: CURSOR c_get_prev_seq_wv(p_trip_id NUMBER, p_stop_seq NUMBER, p_wt_uom VARCHAR2, p_vol_uom VARCHAR2) IS
166: select wsh_wv_utils.convert_uom( WEIGHT_UOM_CODE, p_wt_uom, DEPARTURE_GROSS_WEIGHT, null) gross_weight,
167: wsh_wv_utils.convert_uom( WEIGHT_UOM_CODE, p_wt_uom, DEPARTURE_NET_WEIGHT, null) net_weight,
168: wsh_wv_utils.convert_uom( VOLUME_UOM_CODE, p_vol_uom, DEPARTURE_VOLUME, null) volume
169: from wsh_trip_stops
170: where trip_id = p_trip_id
171: and stop_sequence_number < p_stop_seq
172: order by stop_sequence_number desc;
173:
172: order by stop_sequence_number desc;
173:
174: CURSOR c_next_seq_exists (p_trip_id NUMBER, p_stop_seq NUMBER) IS
175: select 'x'
176: from wsh_trip_stops
177: where trip_id = p_trip_id
178: and stop_sequence_number > p_stop_seq;
179:
180: l_customer_id NUMBER;
209: l_count NUMBER := 0;
210:
211:
212: --2709662
213: l_trip_stop_info wsh_trip_stops_pvt.trip_stop_rec_type;
214: l_delivery_leg_info wsh_delivery_legs_pvt.delivery_leg_rec_type;
215: l_rowid VARCHAR2(30);
216: l_pickup_stop_id NUMBER;
217: l_dropoff_stop_id NUMBER;
255: l_dropoff_stop_seq NUMBER;
256: l_new_flag VARCHAR2(30);
257:
258: /* H integration for Multi Leg */
259: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
260: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
261: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
262: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
263: l_return_status VARCHAR2(30);
256: l_new_flag VARCHAR2(30);
257:
258: /* H integration for Multi Leg */
259: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
260: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
261: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
262: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
263: l_return_status VARCHAR2(30);
264:
287: l_routing_request_flag VARCHAR2(30);
288: l_chkStop_status_code VARCHAR2(30);
289: l_has_mixed_deliveries VARCHAR2(10);
290: l_stop_opened VARCHAR2(10);
291: l_stop_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;
292: l_leg_complete boolean;
293:
294: l_exception_name varchar2(30);
295: l_msg varchar2(2000);
809: --
810: -- Debug Statements
811: --
812: IF l_debug_on THEN
813: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_VALIDATIONS.GET_NEW_SEQUENCE_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
814: END IF;
815: --
816: WSH_TRIP_STOPS_VALIDATIONS.get_new_sequence_number
817: (x_stop_sequence_number => l_pickup_stop_seq,
812: IF l_debug_on THEN
813: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_VALIDATIONS.GET_NEW_SEQUENCE_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
814: END IF;
815: --
816: WSH_TRIP_STOPS_VALIDATIONS.get_new_sequence_number
817: (x_stop_sequence_number => l_pickup_stop_seq,
818: p_trip_id => p_trip_id,
819: p_status_code => 'OP',
820: p_stop_id => null, -- as of now no validations
943: --
944: -- Debug Statements
945: --
946: IF l_debug_on THEN
947: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.CREATE_TRIP_STOP',WSH_DEBUG_SV.C_PROC_LEVEL);
948: END IF;
949: --
950: --
951:
948: END IF;
949: --
950: --
951:
952: wsh_trip_stops_pvt.create_trip_stop(l_trip_stop_info,l_rowid,l_pickup_stop_id, x_return_status);
953:
954: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
955: RAISE e_InvalidDelExcep; -- Bug 5336308
956: END IF;
1002: --
1003: -- Debug Statements
1004: --
1005: IF l_debug_on THEN
1006: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1007: END IF;
1008: --
1009: wsh_trip_stops_grp.get_stop_details_pvt
1010: (p_stop_id => l_pickup_stop_id,
1005: IF l_debug_on THEN
1006: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1007: END IF;
1008: --
1009: wsh_trip_stops_grp.get_stop_details_pvt
1010: (p_stop_id => l_pickup_stop_id,
1011: x_stop_rec => l_stop_rec,
1012: x_return_status => l_return_status);
1013:
1067: -- J-IB-NPARIKH-{
1068: --
1069: --
1070: -- For an existing stop, calculate value of shipments type flag
1071: -- by calling WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1072: --
1073: IF l_debug_on THEN
1074: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
1075: END IF;
1070: -- For an existing stop, calculate value of shipments type flag
1071: -- by calling WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1072: --
1073: IF l_debug_on THEN
1074: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
1075: END IF;
1076: --
1077: WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1078: (
1073: IF l_debug_on THEN
1074: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
1075: END IF;
1076: --
1077: WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1078: (
1079: p_trip_id => p_trip_id,
1080: p_stop_id => l_pickup_stop_id,
1081: p_action => 'ASSIGN',
1093: --
1094: -- J-IB-NPARIKH-}
1095:
1096: /* End of H integration - call Multi Leg FTE */
1097: update wsh_trip_stops
1098: set planned_departure_date=least(nvl(l_pickup_date,sysdate),planned_departure_date),
1099: planned_arrival_date=least(nvl(l_pickup_date,sysdate),planned_arrival_date),
1100: shipments_type_flag = l_stop_shipments_type_flag , -- J-IB-NPARIKH
1101: last_update_date = SYSDATE, -- J-IB-NPARIKH
1104: where stop_id=l_pickup_stop_id;
1105:
1106: --
1107: -- DBI Project
1108: -- Updating WSH_TRIP_STOPS.
1109: -- Call DBI API after the Update.
1110: -- This API will also check for DBI Installed or not
1111: IF l_debug_on THEN
1112: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',l_pickup_stop_id);
1215: IF l_dropoff_stop_seq is NULL and l_dropoff_stop_id is NULL THEN
1216:
1217: IF l_pregen_seq = 'N' THEN --{
1218: IF l_debug_on THEN
1219: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_VALIDATIONS.GET_NEW_SEQUENCE_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
1220: END IF;
1221: --
1222:
1223: WSH_TRIP_STOPS_VALIDATIONS.get_new_sequence_number
1219: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_VALIDATIONS.GET_NEW_SEQUENCE_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
1220: END IF;
1221: --
1222:
1223: WSH_TRIP_STOPS_VALIDATIONS.get_new_sequence_number
1224: (x_stop_sequence_number => l_dropoff_stop_seq,
1225: p_trip_id => p_trip_id,
1226: p_status_code => 'OP',
1227: p_stop_id => null, -- as of now no validations
1366: --
1367: -- Debug Statements
1368: --
1369: IF l_debug_on THEN
1370: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.CREATE_TRIP_STOP',WSH_DEBUG_SV.C_PROC_LEVEL);
1371: END IF;
1372: --
1373: wsh_trip_stops_pvt.create_trip_stop(l_trip_stop_info,l_rowid,l_dropoff_stop_id, x_return_status);
1374:
1369: IF l_debug_on THEN
1370: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.CREATE_TRIP_STOP',WSH_DEBUG_SV.C_PROC_LEVEL);
1371: END IF;
1372: --
1373: wsh_trip_stops_pvt.create_trip_stop(l_trip_stop_info,l_rowid,l_dropoff_stop_id, x_return_status);
1374:
1375: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1376: RAISE e_InvalidDelExcep; -- Bug 5336308
1377: END IF;
1425: --
1426: -- Debug Statements
1427: --
1428: IF l_debug_on THEN
1429: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1430: END IF;
1431: --
1432: wsh_trip_stops_grp.get_stop_details_pvt
1433: (p_stop_id => l_dropoff_stop_id,
1428: IF l_debug_on THEN
1429: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1430: END IF;
1431: --
1432: wsh_trip_stops_grp.get_stop_details_pvt
1433: (p_stop_id => l_dropoff_stop_id,
1434: x_stop_rec => l_stop_rec,
1435: x_return_status => l_return_status);
1436:
1489: -- J-IB-NPARIKH-{
1490: --
1491: --
1492: -- For an existing stop, calculate value of shipments type flag
1493: -- by calling WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1494: --
1495: IF l_debug_on THEN
1496: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
1497: END IF;
1492: -- For an existing stop, calculate value of shipments type flag
1493: -- by calling WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1494: --
1495: IF l_debug_on THEN
1496: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
1497: END IF;
1498: --
1499: WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1500: (
1495: IF l_debug_on THEN
1496: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
1497: END IF;
1498: --
1499: WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1500: (
1501: p_trip_id => p_trip_id,
1502: p_stop_id => l_dropoff_stop_id,
1503: p_action => 'ASSIGN',
1515: --
1516: -- J-IB-NPARIKH-}
1517:
1518: /* End of H integration - call Multi Leg FTE */
1519: update wsh_trip_stops
1520: set shipments_type_flag = l_stop_shipments_type_flag , -- J-IB-NPARIKH
1521: last_update_date = SYSDATE,
1522: last_updated_by = FND_GLOBAL.USER_ID,
1523: last_update_login = FND_GLOBAL.LOGIN_ID
1624: END IF;
1625:
1626: --
1627: -- DBI Project
1628: -- Updating WSH_TRIP_STOPS.
1629: -- Call DBI API after the Update.
1630: -- This API will also check for DBI Installed or not
1631: IF l_debug_on THEN
1632: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',l_pickup_stop_id);
1672: END IF;
1673:
1674: --
1675: -- DBI Project
1676: -- Updating WSH_TRIP_STOPS.
1677: -- Call DBI API after the Update.
1678: -- This API will also check for DBI Installed or not
1679: IF l_debug_on THEN
1680: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',l_dropoff_stop_id);
1763: IF p_pickup_stop_id IS NOT NULL
1764: THEN
1765: --{
1766: IF l_debug_on THEN
1767: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
1768: END IF;
1769: --
1770: --
1771: -- For an existing stop, calculate value of shipments type flag
1768: END IF;
1769: --
1770: --
1771: -- For an existing stop, calculate value of shipments type flag
1772: -- by calling WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1773: --
1774: WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1775: (
1776: p_trip_id => p_trip_id,
1770: --
1771: -- For an existing stop, calculate value of shipments type flag
1772: -- by calling WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1773: --
1774: WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1775: (
1776: p_trip_id => p_trip_id,
1777: p_stop_id => p_pickup_stop_id,
1778: p_action => 'ASSIGN',
1788: x_num_errors => l_num_error
1789: );
1790:
1791: IF p_pickup_location_id IS NOT NULL THEN
1792: update wsh_trip_stops
1793: set planned_departure_date=p_pickup_dep_date,
1794: planned_arrival_date=p_pickup_arr_date,
1795: last_update_date = SYSDATE,
1796: last_updated_by = FND_GLOBAL.USER_ID,
1798: where stop_id=p_pickup_stop_id;
1799:
1800: --
1801: -- DBI Project
1802: -- Updating WSH_TRIP_STOPS.
1803: -- Call DBI API after the Update.
1804: -- This API will also check for DBI Installed or not
1805: IF l_debug_on THEN
1806: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',p_pickup_stop_id);
1836: IF p_dropoff_stop_id IS NOT NULL
1837: THEN
1838: --{
1839: IF l_debug_on THEN
1840: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
1841: END IF;
1842: --
1843: --
1844: -- For an existing stop, calculate value of shipments type flag
1841: END IF;
1842: --
1843: --
1844: -- For an existing stop, calculate value of shipments type flag
1845: -- by calling WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1846: --
1847: WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1848: (
1849: p_trip_id => p_trip_id,
1843: --
1844: -- For an existing stop, calculate value of shipments type flag
1845: -- by calling WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1846: --
1847: WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1848: (
1849: p_trip_id => p_trip_id,
1850: p_stop_id => p_dropoff_stop_id,
1851: p_action => 'ASSIGN',
1865: IF l_debug_on THEN
1866: WSH_DEBUG_SV.log(l_module_name,'l_do_stop_shipments_type_flag',l_do_stop_shipments_type_flag);
1867: END IF;
1868: IF p_dropoff_location_id IS NOT NULL THEN
1869: update wsh_trip_stops
1870: set planned_departure_date=p_dropoff_dep_date,
1871: planned_arrival_date=p_dropoff_arr_date,
1872: last_update_date = SYSDATE,
1873: last_updated_by = FND_GLOBAL.USER_ID,
1875: where stop_id=p_dropoff_stop_id;
1876:
1877: --
1878: -- DBI Project
1879: -- Updating WSH_TRIP_STOPS.
1880: -- Call DBI API after the Update.
1881: -- This API will also check for DBI Installed or not
1882: IF l_debug_on THEN
1883: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',p_dropoff_stop_id);
2022: --
2023: -- Debug Statements
2024: --
2025: IF l_debug_on THEN
2026: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2027: END IF;
2028: --
2029: wsh_trip_stops_grp.get_stop_details_pvt
2030: (p_stop_id => p_pickup_stop_id,
2025: IF l_debug_on THEN
2026: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2027: END IF;
2028: --
2029: wsh_trip_stops_grp.get_stop_details_pvt
2030: (p_stop_id => p_pickup_stop_id,
2031: x_stop_rec => l_stop_rec,
2032: x_return_status => l_return_status);
2033:
2083: --
2084: -- J-IB-NPARIKH-}
2085:
2086: /* End of H integration - call Multi Leg FTE */
2087: update wsh_trip_stops
2088: set shipments_type_flag = l_pu_stop_shipments_type_flag, -- J-IB-NPARIKH
2089: last_update_date = SYSDATE,
2090: last_updated_by = FND_GLOBAL.USER_ID,
2091: last_update_login = FND_GLOBAL.LOGIN_ID
2107: --
2108: -- Debug Statements
2109: --
2110: IF l_debug_on THEN
2111: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2112: END IF;
2113: --
2114: wsh_trip_stops_grp.get_stop_details_pvt
2115: (p_stop_id => p_dropoff_stop_id,
2110: IF l_debug_on THEN
2111: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2112: END IF;
2113: --
2114: wsh_trip_stops_grp.get_stop_details_pvt
2115: (p_stop_id => p_dropoff_stop_id,
2116: x_stop_rec => l_stop_rec,
2117: x_return_status => l_return_status);
2118:
2167: END IF;
2168: --
2169:
2170: /* End of H integration - call Multi Leg FTE */
2171: update wsh_trip_stops
2172: set shipments_type_flag = l_do_stop_shipments_type_flag, -- J-IB-NPARIKH
2173: last_update_date = SYSDATE,
2174: last_updated_by = FND_GLOBAL.USER_ID,
2175: last_update_login = FND_GLOBAL.LOGIN_ID
2236: WHERE trip_id = p_trip_id;
2237:
2238: IF (l_trip_shipments_type_flag_new <> 'M') THEN
2239:
2240: UPDATE WSH_TRIP_STOPS
2241: SET shipments_type_flag = l_trip_shipments_type_flag_new,
2242: last_update_date = SYSDATE,
2243: last_updated_by = FND_GLOBAL.USER_ID,
2244: last_update_login = FND_GLOBAL.LOGIN_ID
2880: FOR UPDATE NOWAIT;
2881:
2882: CURSOR del_trip_exists( p_del_id IN NUMBER) IS
2883: SELECT dg.delivery_leg_id, st1.stop_location_id
2884: FROM wsh_trip_stops st1,
2885: wsh_trip_stops st2,
2886: wsh_delivery_legs dg
2887: WHERE st1.stop_id = dg.pick_up_stop_id AND
2888: st2.stop_id = dg.drop_off_stop_id AND
2881:
2882: CURSOR del_trip_exists( p_del_id IN NUMBER) IS
2883: SELECT dg.delivery_leg_id, st1.stop_location_id
2884: FROM wsh_trip_stops st1,
2885: wsh_trip_stops st2,
2886: wsh_delivery_legs dg
2887: WHERE st1.stop_id = dg.pick_up_stop_id AND
2888: st2.stop_id = dg.drop_off_stop_id AND
2889: st1.trip_id = p_trip_id AND
2917:
2918: -- Bug 3875780
2919: CURSOR c_trip_stops(p_trip_id IN NUMBER) IS
2920: SELECT stop_id
2921: FROM wsh_trip_stops
2922: WHERE trip_id = p_trip_id;
2923:
2924:
2925: CURSOR c_trip_empty IS
2923:
2924:
2925: CURSOR c_trip_empty IS
2926: SELECT count(*)
2927: FROM wsh_trip_stops a,wsh_delivery_legs b
2928: WHERE a.stop_id = b.pick_up_stop_id
2929: AND a.trip_id = p_trip_id
2930: AND rownum = 1 ;
2931:
3013:
3014: SELECT 'Y'
3015: INTO l_del_for_update_load_seq
3016: FROM wsh_delivery_legs wdl,
3017: wsh_trip_stops wts,
3018: wsh_trips wt
3019: WHERE wdl.delivery_id = p_del_rows(i)
3020: AND wdl.drop_off_stop_id = wts.stop_id
3021: AND wts.trip_id = wt.trip_id
3591: trip.lane_id, trip.mode_of_transport
3592: FROM wsh_delivery_legs dlg,
3593: wsh_delivery_assignments_v da,
3594: wsh_trips trip,
3595: wsh_trip_stops st
3596: WHERE da.delivery_detail_id = c_delivery_detail_id AND
3597: da.delivery_id = dlg.delivery_id AND
3598: dlg.pick_up_stop_id = st.stop_id AND
3599: st.trip_id = trip.trip_id;
3601: cursor get_trip_from_del( c_delivery_id NUMBER) is
3602: SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3603: trip.lane_id, trip.mode_of_transport
3604: FROM wsh_delivery_legs dlg,
3605: wsh_trip_stops stop,
3606: wsh_trips trip
3607: WHERE dlg.delivery_id = c_delivery_id and
3608: dlg.pick_up_stop_id = stop.stop_id and
3609: stop.trip_id = trip.trip_id;
3611: cursor get_trip_from_leg( c_leg_id NUMBER) is
3612: SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3613: trip.lane_id, trip.mode_of_transport
3614: FROM wsh_delivery_legs dlg,
3615: wsh_trip_stops stop,
3616: wsh_trips trip
3617: WHERE dlg.delivery_leg_id = c_leg_id AND
3618: dlg.pick_up_stop_id = stop.stop_id AND
3619: stop.trip_id = trip.trip_id;
3621: --gets all legs for the trip the stop belongs to
3622: cursor get_trip_from_stop(c_stop_id NUMBER) is
3623: SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3624: trip.lane_id, trip.mode_of_transport
3625: FROM wsh_trip_stops stop,
3626: wsh_trips trip,
3627: wsh_delivery_legs dlg,
3628: wsh_trip_stops ts
3629: WHERE stop.stop_id = c_stop_id AND
3624: trip.lane_id, trip.mode_of_transport
3625: FROM wsh_trip_stops stop,
3626: wsh_trips trip,
3627: wsh_delivery_legs dlg,
3628: wsh_trip_stops ts
3629: WHERE stop.stop_id = c_stop_id AND
3630: stop.trip_id = trip.trip_id AND
3631: ts.trip_id=trip.trip_id AND
3632: dlg.pick_up_stop_id = ts.stop_id;
3633:
3634: cursor get_legs_from_trip( c_trip_id NUMBER) is
3635: SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3636: trip.lane_id, trip.mode_of_transport
3637: FROM wsh_trip_stops ts,
3638: wsh_delivery_legs dlg,
3639: wsh_trips trip
3640: WHERE ts.trip_id = c_trip_id AND
3641: dlg.pick_up_stop_id = ts.stop_id AND
3643:
3644: cursor legs_priced_cur( c_trip_id NUMBER) is
3645: select 1
3646: from wsh_freight_costs wfc,
3647: wsh_trip_stops wts,
3648: wsh_delivery_legs wdl
3649: where wts.trip_id = c_trip_id
3650: and wdl.pick_up_stop_id = wts.stop_id
3651: and wfc.delivery_leg_id = wdl.delivery_leg_id
4185: CURSOR C_TRIP_DEL_RATE(p_trip_id IN NUMBER, p_freight_terms_code_o IN VARCHAR2,
4186: p_freight_terms_code_i IN VARCHAR2, p_freight_terms_code_d VARCHAR2) IS
4187: select wnd.delivery_id
4188: FROM wsh_new_deliveries wnd,
4189: wsh_trip_stops wts,
4190: wsh_delivery_legs wdl
4191: WHERE wts.stop_id = wdl.pick_up_stop_id AND
4192: wts.trip_id = p_trip_id AND
4193: wdl.delivery_id = wnd.delivery_id AND
4196: and rownum = 1
4197: UNION
4198: select wnd.delivery_id
4199: FROM wsh_new_deliveries wnd,
4200: wsh_trip_stops wts,
4201: wsh_delivery_legs wdl
4202: WHERE wts.stop_id = wdl.pick_up_stop_id AND
4203: wts.trip_id = p_trip_id AND
4204: wdl.delivery_id = wnd.delivery_id AND
4207: and rownum = 1
4208: UNION
4209: select wnd.delivery_id
4210: FROM wsh_new_deliveries wnd,
4211: wsh_trip_stops wts,
4212: wsh_delivery_legs wdl
4213: WHERE wts.stop_id = wdl.pick_up_stop_id AND
4214: wts.trip_id = p_trip_id AND
4215: wdl.delivery_id = wnd.delivery_id AND
4220: CURSOR C_TRIP_DEL_NORATE(p_trip_id IN NUMBER, p_freight_terms_code_o IN VARCHAR2,
4221: p_freight_terms_code_i IN VARCHAR2, p_freight_terms_code_d VARCHAR2) IS
4222: select wnd.delivery_id
4223: FROM wsh_new_deliveries wnd,
4224: wsh_trip_stops wts,
4225: wsh_delivery_legs wdl
4226: WHERE wts.stop_id = wdl.pick_up_stop_id AND
4227: wts.trip_id = p_trip_id AND
4228: wdl.delivery_id = wnd.delivery_id AND
4231: and rownum = 1
4232: UNION
4233: select wnd.delivery_id
4234: FROM wsh_new_deliveries wnd,
4235: wsh_trip_stops wts,
4236: wsh_delivery_legs wdl
4237: WHERE wts.stop_id = wdl.pick_up_stop_id AND
4238: wts.trip_id = p_trip_id AND
4239: wdl.delivery_id = wnd.delivery_id AND
4242: and rownum = 1
4243: UNION
4244: select wnd.delivery_id
4245: FROM wsh_new_deliveries wnd,
4246: wsh_trip_stops wts,
4247: wsh_delivery_legs wdl
4248: WHERE wts.stop_id = wdl.pick_up_stop_id AND
4249: wts.trip_id = p_trip_id AND
4250: wdl.delivery_id = wnd.delivery_id AND