1083:
1084: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1085: --
1086: IF l_debug_on IS NULL THEN
1087: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1088: END IF;
1089:
1090: IF l_debug_on THEN
1091: WSH_DEBUG_SV.push(l_module_name);
1087: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1088: END IF;
1089:
1090: IF l_debug_on THEN
1091: WSH_DEBUG_SV.push(l_module_name);
1092: WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1093: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1094: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1095: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1088: END IF;
1089:
1090: IF l_debug_on THEN
1091: WSH_DEBUG_SV.push(l_module_name);
1092: WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1093: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1094: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1095: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1096: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1089:
1090: IF l_debug_on THEN
1091: WSH_DEBUG_SV.push(l_module_name);
1092: WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1093: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1094: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1095: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1096: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1097: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1090: IF l_debug_on THEN
1091: WSH_DEBUG_SV.push(l_module_name);
1092: WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1093: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1094: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1095: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1096: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1097: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1098: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1091: WSH_DEBUG_SV.push(l_module_name);
1092: WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1093: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1094: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1095: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1096: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1097: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1098: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1092: WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1093: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1094: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1095: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1096: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1097: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1098: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1093: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1094: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1095: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1096: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1097: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1098: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1094: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1095: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1096: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1097: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1098: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1102: WSH_DEBUG_SV.logmsg(l_module_name, 'START PROCESSING...');
1095: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1096: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1097: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1098: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1102: WSH_DEBUG_SV.logmsg(l_module_name, 'START PROCESSING...');
1103: END IF;
1096: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1097: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1098: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1102: WSH_DEBUG_SV.logmsg(l_module_name, 'START PROCESSING...');
1103: END IF;
1104: --Bug#7491598(ER,defer planned shipment iface)
1097: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1098: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1102: WSH_DEBUG_SV.logmsg(l_module_name, 'START PROCESSING...');
1103: END IF;
1104: --Bug#7491598(ER,defer planned shipment iface)
1105: IF p_group_id IS NULL THEN
1098: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1102: WSH_DEBUG_SV.logmsg(l_module_name, 'START PROCESSING...');
1103: END IF;
1104: --Bug#7491598(ER,defer planned shipment iface)
1105: IF p_group_id IS NULL THEN
1106: --{
1122: --} group id is null
1123: END IF;
1124:
1125: IF l_debug_on THEN
1126: WSH_DEBUG_SV.log(l_module_name, 'l_del_pickup_date_low', l_del_pickup_date_low);
1127: WSH_DEBUG_SV.log(l_module_name, 'l_del_pickup_date_high', l_del_pickup_date_high);
1128: END IF;
1129:
1130:
1123: END IF;
1124:
1125: IF l_debug_on THEN
1126: WSH_DEBUG_SV.log(l_module_name, 'l_del_pickup_date_low', l_del_pickup_date_low);
1127: WSH_DEBUG_SV.log(l_module_name, 'l_del_pickup_date_high', l_del_pickup_date_high);
1128: END IF;
1129:
1130:
1131: -- --Bug#7491598(ER,defer planned shipment iface): Group Id check needs to be removed.
1137: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1138: errbuf := 'Exception occurred in Release_Planned_Shipment';
1139: retcode := '2';
1140: IF l_debug_on THEN
1141: WSH_DEBUG_SV.logmsg(l_module_name, 'Group Id cannot be Null');
1142: WSH_DEBUG_SV.pop(l_module_name);
1143: END IF;
1144: RETURN;
1145: END IF;
1138: errbuf := 'Exception occurred in Release_Planned_Shipment';
1139: retcode := '2';
1140: IF l_debug_on THEN
1141: WSH_DEBUG_SV.logmsg(l_module_name, 'Group Id cannot be Null');
1142: WSH_DEBUG_SV.pop(l_module_name);
1143: END IF;
1144: RETURN;
1145: END IF;
1146: */
1168: --=======================================================================
1169: -- 1. Get All Trips Information
1170: -- Loop to find trips for the group_id
1171: IF l_debug_on THEN
1172: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1173: WSH_DEBUG_SV.logmsg(l_module_name,'1. Gather Trips Interface data');
1174: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1175: END IF;
1176: -- Bug#7491598(ER,defer planned shipment iface)
1169: -- 1. Get All Trips Information
1170: -- Loop to find trips for the group_id
1171: IF l_debug_on THEN
1172: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1173: WSH_DEBUG_SV.logmsg(l_module_name,'1. Gather Trips Interface data');
1174: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1175: END IF;
1176: -- Bug#7491598(ER,defer planned shipment iface)
1177: IF (P_GROUP_ID is not null) THEN
1170: -- Loop to find trips for the group_id
1171: IF l_debug_on THEN
1172: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1173: WSH_DEBUG_SV.logmsg(l_module_name,'1. Gather Trips Interface data');
1174: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1175: END IF;
1176: -- Bug#7491598(ER,defer planned shipment iface)
1177: IF (P_GROUP_ID is not null) THEN
1178: OPEN c_tms_interface_trips;
1206: --l_trip_index := l_trip_info_rec.trip_interface_id;
1207: --l_trip_index := l_trip_index + 1;
1208: l_trip_index := l_trip_info_tab.count + 1;
1209: IF l_debug_on THEN
1210: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_trip_info_rec.trip_interface_id);
1211: END IF;
1212: --l_trip_info_tab(l_trip_info_tab.count + 1) := l_trip_info_rec;
1213: l_trip_info_tab(l_trip_index) := l_trip_info_rec;
1214:
1227: CLOSE c_tms_interface_trips_all;
1228: END IF;
1229: -- Bug#7491598(ER,defer planned shipment iface): end
1230: IF l_debug_on THEN
1231: WSH_DEBUG_SV.log(l_module_name,'Count Trip Info Tab:', l_trip_info_tab.count);
1232: END IF;
1233:
1234: --=======================================================================
1235: -- 2. Get All Trip Stops Information(order by trip ids)
1234: --=======================================================================
1235: -- 2. Get All Trip Stops Information(order by trip ids)
1236: -- Loop to find trip stops for the group_id
1237: IF l_debug_on THEN
1238: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1239: WSH_DEBUG_SV.logmsg(l_module_name,'2. Gather Trip Stops Interface data');
1240: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1241: END IF;
1242: -- --Bug#7491598(ER,defer planned shipment iface): Begin
1235: -- 2. Get All Trip Stops Information(order by trip ids)
1236: -- Loop to find trip stops for the group_id
1237: IF l_debug_on THEN
1238: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1239: WSH_DEBUG_SV.logmsg(l_module_name,'2. Gather Trip Stops Interface data');
1240: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1241: END IF;
1242: -- --Bug#7491598(ER,defer planned shipment iface): Begin
1243: IF (P_GROUP_ID is not null) THEN
1236: -- Loop to find trip stops for the group_id
1237: IF l_debug_on THEN
1238: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1239: WSH_DEBUG_SV.logmsg(l_module_name,'2. Gather Trip Stops Interface data');
1240: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1241: END IF;
1242: -- --Bug#7491598(ER,defer planned shipment iface): Begin
1243: IF (P_GROUP_ID is not null) THEN
1244: OPEN c_tms_interface_stops;
1268: INTO l_stop_info_rec;
1269: EXIT WHEN c_tms_interface_stops%NOTFOUND; */
1270: --l_stop_index := l_stop_index + 1;
1271: IF l_debug_on THEN
1272: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_stop_info_rec.trip_interface_id);
1273: WSH_DEBUG_SV.log(l_module_name,'Stop Interface id', l_stop_info_rec.stop_interface_id);
1274: WSH_DEBUG_SV.log(l_module_name,'Stop Location id', l_stop_info_rec.stop_location_id);
1275: WSH_DEBUG_SV.log(l_module_name,'Stop Sequence No.', l_stop_info_rec.stop_sequence_number);
1276: WSH_DEBUG_SV.log(l_module_name,'Timezone Code', l_stop_info_rec.timezone_code);
1269: EXIT WHEN c_tms_interface_stops%NOTFOUND; */
1270: --l_stop_index := l_stop_index + 1;
1271: IF l_debug_on THEN
1272: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_stop_info_rec.trip_interface_id);
1273: WSH_DEBUG_SV.log(l_module_name,'Stop Interface id', l_stop_info_rec.stop_interface_id);
1274: WSH_DEBUG_SV.log(l_module_name,'Stop Location id', l_stop_info_rec.stop_location_id);
1275: WSH_DEBUG_SV.log(l_module_name,'Stop Sequence No.', l_stop_info_rec.stop_sequence_number);
1276: WSH_DEBUG_SV.log(l_module_name,'Timezone Code', l_stop_info_rec.timezone_code);
1277: END IF;
1270: --l_stop_index := l_stop_index + 1;
1271: IF l_debug_on THEN
1272: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_stop_info_rec.trip_interface_id);
1273: WSH_DEBUG_SV.log(l_module_name,'Stop Interface id', l_stop_info_rec.stop_interface_id);
1274: WSH_DEBUG_SV.log(l_module_name,'Stop Location id', l_stop_info_rec.stop_location_id);
1275: WSH_DEBUG_SV.log(l_module_name,'Stop Sequence No.', l_stop_info_rec.stop_sequence_number);
1276: WSH_DEBUG_SV.log(l_module_name,'Timezone Code', l_stop_info_rec.timezone_code);
1277: END IF;
1278: l_stop_info_tab(l_stop_info_tab.count + 1) := l_stop_info_rec;
1271: IF l_debug_on THEN
1272: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_stop_info_rec.trip_interface_id);
1273: WSH_DEBUG_SV.log(l_module_name,'Stop Interface id', l_stop_info_rec.stop_interface_id);
1274: WSH_DEBUG_SV.log(l_module_name,'Stop Location id', l_stop_info_rec.stop_location_id);
1275: WSH_DEBUG_SV.log(l_module_name,'Stop Sequence No.', l_stop_info_rec.stop_sequence_number);
1276: WSH_DEBUG_SV.log(l_module_name,'Timezone Code', l_stop_info_rec.timezone_code);
1277: END IF;
1278: l_stop_info_tab(l_stop_info_tab.count + 1) := l_stop_info_rec;
1279:
1272: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_stop_info_rec.trip_interface_id);
1273: WSH_DEBUG_SV.log(l_module_name,'Stop Interface id', l_stop_info_rec.stop_interface_id);
1274: WSH_DEBUG_SV.log(l_module_name,'Stop Location id', l_stop_info_rec.stop_location_id);
1275: WSH_DEBUG_SV.log(l_module_name,'Stop Sequence No.', l_stop_info_rec.stop_sequence_number);
1276: WSH_DEBUG_SV.log(l_module_name,'Timezone Code', l_stop_info_rec.timezone_code);
1277: END IF;
1278: l_stop_info_tab(l_stop_info_tab.count + 1) := l_stop_info_rec;
1279:
1280: END LOOP;--}
1288: CLOSE c_tms_interface_stops_all;
1289: END IF;
1290:
1291: IF l_debug_on THEN
1292: WSH_DEBUG_SV.log(l_module_name,'Count Trip Stop Info Tab:', l_stop_info_tab.count);
1293: END IF;
1294:
1295: --=======================================================================
1296: -- 3. For each trip stop, get Delivery Leg information(order by trip ids)
1295: --=======================================================================
1296: -- 3. For each trip stop, get Delivery Leg information(order by trip ids)
1297: -- Loop to find delivery legs for the group_id
1298: IF l_debug_on THEN
1299: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1300: WSH_DEBUG_SV.logmsg(l_module_name,'3. Gather Delivery Legs Interface data');
1301: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1302: END IF;
1303: --Bug#7491598(ER,defer planned shipment iface): Begin
1296: -- 3. For each trip stop, get Delivery Leg information(order by trip ids)
1297: -- Loop to find delivery legs for the group_id
1298: IF l_debug_on THEN
1299: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1300: WSH_DEBUG_SV.logmsg(l_module_name,'3. Gather Delivery Legs Interface data');
1301: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1302: END IF;
1303: --Bug#7491598(ER,defer planned shipment iface): Begin
1304: IF (P_GROUP_ID is not null) THEN
1297: -- Loop to find delivery legs for the group_id
1298: IF l_debug_on THEN
1299: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1300: WSH_DEBUG_SV.logmsg(l_module_name,'3. Gather Delivery Legs Interface data');
1301: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1302: END IF;
1303: --Bug#7491598(ER,defer planned shipment iface): Begin
1304: IF (P_GROUP_ID is not null) THEN
1305: OPEN c_tms_interface_dlegs;
1331: EXIT WHEN c_tms_interface_dlegs%NOTFOUND; */
1332: l_dleg_index := l_dleg_info_rec.trip_interface_id;
1333: --l_dleg_index := l_dleg_index + 1;
1334: IF l_debug_on THEN
1335: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_dleg_info_rec.trip_interface_id);
1336: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1337: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1338: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1339: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1332: l_dleg_index := l_dleg_info_rec.trip_interface_id;
1333: --l_dleg_index := l_dleg_index + 1;
1334: IF l_debug_on THEN
1335: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_dleg_info_rec.trip_interface_id);
1336: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1337: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1338: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1339: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1340: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1333: --l_dleg_index := l_dleg_index + 1;
1334: IF l_debug_on THEN
1335: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_dleg_info_rec.trip_interface_id);
1336: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1337: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1338: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1339: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1340: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1341: WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1334: IF l_debug_on THEN
1335: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_dleg_info_rec.trip_interface_id);
1336: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1337: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1338: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1339: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1340: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1341: WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1342: WSH_DEBUG_SV.log(l_module_name,'OTM Version Number', l_dleg_info_rec.otm_tms_version_number);
1335: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_dleg_info_rec.trip_interface_id);
1336: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1337: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1338: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1339: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1340: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1341: WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1342: WSH_DEBUG_SV.log(l_module_name,'OTM Version Number', l_dleg_info_rec.otm_tms_version_number);
1343: END IF;
1336: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1337: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1338: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1339: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1340: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1341: WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1342: WSH_DEBUG_SV.log(l_module_name,'OTM Version Number', l_dleg_info_rec.otm_tms_version_number);
1343: END IF;
1344:
1337: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1338: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1339: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1340: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1341: WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1342: WSH_DEBUG_SV.log(l_module_name,'OTM Version Number', l_dleg_info_rec.otm_tms_version_number);
1343: END IF;
1344:
1345: l_dleg_info_tab(l_dleg_info_tab.count + 1) := l_dleg_info_rec;
1338: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1339: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1340: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1341: WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1342: WSH_DEBUG_SV.log(l_module_name,'OTM Version Number', l_dleg_info_rec.otm_tms_version_number);
1343: END IF;
1344:
1345: l_dleg_info_tab(l_dleg_info_tab.count + 1) := l_dleg_info_rec;
1346: END LOOP;--}
1355: CLOSE c_tms_interface_dlegs_all;
1356: END IF;
1357: --Bug#7491598(ER,defer planned shipment iface): end
1358: IF l_debug_on THEN
1359: WSH_DEBUG_SV.log(l_module_name,'Count Delivery Leg Info Tab:', l_dleg_info_tab.count);
1360: END IF;
1361:
1362: -- OTM R12 Clean up for all trips in a bulk delete outside the loop
1363:
1361:
1362: -- OTM R12 Clean up for all trips in a bulk delete outside the loop
1363:
1364: IF l_debug_on THEN
1365: WSH_DEBUG_SV.logmsg(l_module_name,'CLEAN UP EXISTING DATA FOR ALL TRIPS');
1366: END IF;
1367:
1368: -- Delete Records in Interface errors table and exceptions table
1369: -- for the associated entities. This will ensure there are no duplicate
1420: SAVEPOINT process_group;
1421: --=======================================================================
1422: -- 4. Loop thru all trips in l_trip_info_tab
1423: IF l_debug_on THEN
1424: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1425: WSH_DEBUG_SV.logmsg(l_module_name,'4. START LOOPING THROUGH EACH TRIP');
1426: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1427: END IF;
1428:
1421: --=======================================================================
1422: -- 4. Loop thru all trips in l_trip_info_tab
1423: IF l_debug_on THEN
1424: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1425: WSH_DEBUG_SV.logmsg(l_module_name,'4. START LOOPING THROUGH EACH TRIP');
1426: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1427: END IF;
1428:
1429: -- Trip count should be > 0
1422: -- 4. Loop thru all trips in l_trip_info_tab
1423: IF l_debug_on THEN
1424: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1425: WSH_DEBUG_SV.logmsg(l_module_name,'4. START LOOPING THROUGH EACH TRIP');
1426: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1427: END IF;
1428:
1429: -- Trip count should be > 0
1430: IF l_trip_info_tab.count > 0 THEN--{
1438:
1439: SAVEPOINT process_single_trip;
1440:
1441: IF l_debug_on THEN
1442: WSH_DEBUG_SV.logmsg(l_module_name,'Populate Delivery Legs Info in Local Table');
1443: END IF;
1444:
1445: -- Loop through l_dleg_info_tab to find the deliveries for current trip
1446: IF l_dleg_info_tab.COUNT > 0 THEN
1458: -- core APIs
1459: IF l_trip_info_tab(i).trip_id IS NOT NULL THEN--{
1460:
1461: IF l_debug_on THEN
1462: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1463: WSH_DEBUG_SV.logmsg(l_module_name,'4ai. LOCK TRIP:-'||l_trip_info_tab(i).trip_id);
1464: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1465: END IF;
1466:
1459: IF l_trip_info_tab(i).trip_id IS NOT NULL THEN--{
1460:
1461: IF l_debug_on THEN
1462: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1463: WSH_DEBUG_SV.logmsg(l_module_name,'4ai. LOCK TRIP:-'||l_trip_info_tab(i).trip_id);
1464: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1465: END IF;
1466:
1467: BEGIN
1460:
1461: IF l_debug_on THEN
1462: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1463: WSH_DEBUG_SV.logmsg(l_module_name,'4ai. LOCK TRIP:-'||l_trip_info_tab(i).trip_id);
1464: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1465: END IF;
1466:
1467: BEGIN
1468: -- Lock Trip
1474: FND_MESSAGE.SET_TOKEN('TRIP_NAME',WSH_TRIPS_PVT.get_name(l_trip_info_tab(i).trip_id));
1475: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1476: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1477: IF l_debug_on THEN
1478: WSH_DEBUG_SV.log(l_module_name, 'trip not locked: l_return_status', l_return_status);
1479: END IF;
1480: GOTO trip_error;
1481: END;
1482:
1482:
1483: -- 4a. Lock corresponding trip stops in EBS(for trip_id)
1484: -- Trip has to exist in EBS for locking this stops
1485: IF l_debug_on THEN
1486: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1487: WSH_DEBUG_SV.logmsg(l_module_name,'4aii. LOCK TRIP STOP');
1488: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1489: END IF;
1490: DECLARE
1483: -- 4a. Lock corresponding trip stops in EBS(for trip_id)
1484: -- Trip has to exist in EBS for locking this stops
1485: IF l_debug_on THEN
1486: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1487: WSH_DEBUG_SV.logmsg(l_module_name,'4aii. LOCK TRIP STOP');
1488: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1489: END IF;
1490: DECLARE
1491: l_stop_id NUMBER;
1484: -- Trip has to exist in EBS for locking this stops
1485: IF l_debug_on THEN
1486: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1487: WSH_DEBUG_SV.logmsg(l_module_name,'4aii. LOCK TRIP STOP');
1488: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1489: END IF;
1490: DECLARE
1491: l_stop_id NUMBER;
1492: BEGIN--{
1503: FND_MESSAGE.SET_TOKEN('STOP_NAME',WSH_TRIP_STOPS_PVT.get_name(l_stop_id,'FTE_TMS_INTEGRATION'));
1504: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1505: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1506: IF l_debug_on THEN
1507: WSH_DEBUG_SV.log(l_module_name, 'trip stop not locked: l_return_status', l_return_status);
1508: END IF;
1509: GOTO trip_error;
1510: END;--}
1511:
1513:
1514: -- 4b. Locking of Delivery and Delivery legs is required even when there is no trip in EBS
1515:
1516: IF l_debug_on THEN
1517: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1518: WSH_DEBUG_SV.logmsg(l_module_name,'4b. LOCK DELIVERY AND DELIVERY LEG');
1519: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1520: END IF;
1521: IF l_dleg_local_tab.count > 0 THEN
1514: -- 4b. Locking of Delivery and Delivery legs is required even when there is no trip in EBS
1515:
1516: IF l_debug_on THEN
1517: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1518: WSH_DEBUG_SV.logmsg(l_module_name,'4b. LOCK DELIVERY AND DELIVERY LEG');
1519: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1520: END IF;
1521: IF l_dleg_local_tab.count > 0 THEN
1522: FOR m in l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
1515:
1516: IF l_debug_on THEN
1517: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1518: WSH_DEBUG_SV.logmsg(l_module_name,'4b. LOCK DELIVERY AND DELIVERY LEG');
1519: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1520: END IF;
1521: IF l_dleg_local_tab.count > 0 THEN
1522: FOR m in l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
1523: -- Lock corresponding delivery legs in EBS
1535: FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',wsh_new_deliveries_pvt.get_name(l_dleg_local_tab(m).delivery_id));
1536: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1537: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1538: IF l_debug_on THEN
1539: WSH_DEBUG_SV.log(l_module_name, 'Delivery Leg not locked: l_return_status', l_return_status);
1540: END IF;
1541: GOTO trip_error;
1542: END;
1543:
1552: FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',wsh_new_deliveries_pvt.get_name(l_dleg_local_tab(m).delivery_id));
1553: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1554: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1555: IF l_debug_on THEN
1556: WSH_DEBUG_SV.log(l_module_name, 'Delivery not locked: l_return_status', l_return_status);
1557: END IF;
1558: GOTO trip_error;
1559: END;
1560: END IF;--}
1562: END IF;
1563: -- 4c. Process Trip
1564: -- Vehicle Derivation
1565: IF l_debug_on THEN
1566: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1567: WSH_DEBUG_SV.logmsg(l_module_name,'4c. PROCESS TRIP');
1568: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1569: END IF;
1570: DECLARE
1563: -- 4c. Process Trip
1564: -- Vehicle Derivation
1565: IF l_debug_on THEN
1566: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1567: WSH_DEBUG_SV.logmsg(l_module_name,'4c. PROCESS TRIP');
1568: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1569: END IF;
1570: DECLARE
1571:
1564: -- Vehicle Derivation
1565: IF l_debug_on THEN
1566: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1567: WSH_DEBUG_SV.logmsg(l_module_name,'4c. PROCESS TRIP');
1568: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1569: END IF;
1570: DECLARE
1571:
1572: l_rs VARCHAR2(1);
1583: BEGIN--{
1584:
1585: -- try to lock the trip interface records
1586: IF l_debug_on THEN
1587: WSH_DEBUG_SV.logmsg(l_module_name,'Locking the trip interface record:'||l_trip_info_tab(i).trip_interface_id);
1588: END IF;
1589: OPEN c_lock_trip_interface(l_trip_info_tab(i).trip_interface_id) ;
1590: FETCH c_lock_trip_interface INTO l_lock_rec;
1591: CLOSE c_lock_trip_interface;
1594: -- Derive the Vehicle Item Id based on the name
1595: IF l_trip_info_tab(i).vehicle_item_name IS NOT NULL
1596: AND l_trip_info_tab(i).interface_action_code = G_TMS_RELEASE_CODE THEN--{
1597: IF l_debug_on THEN
1598: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item Name:'||l_trip_info_tab(i).vehicle_item_name);
1599: END IF;
1600: -- Get the first stop location from l_stop_info_tab
1601: -- l_stop_info would be populated
1602: FOR i_vehicle in l_stop_info_tab.FIRST..l_stop_info_tab.LAST
1617: l_return_status := 'T';
1618: FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
1619: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1620: IF l_debug_on THEN
1621: WSH_DEBUG_SV.log(l_module_name, 'Invalid Stop Location, l_return_status:', l_return_status);
1622: END IF;
1623: GOTO trip_error;
1624: END;--}
1625:
1632: l_return_status := 'T';
1633: FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
1634: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1635: IF l_debug_on THEN
1636: WSH_DEBUG_SV.log(l_module_name, 'Unable to Derive Stop Location', l_return_status);
1637: END IF;
1638: GOTO trip_error;
1639: END IF;
1640:
1638: GOTO trip_error;
1639: END IF;
1640:
1641: IF l_debug_on THEN
1642: WSH_DEBUG_SV.logmsg(l_module_name,'Calling wsh_util_core.get_org_from_location:'||l_stop_location_id);
1643: END IF;
1644: --get_org_from_location
1645: WSH_UTIL_CORE.get_org_from_location(
1646: p_location_id => l_stop_location_id,
1647: x_organization_tab => l_organization_tab,
1648: x_return_status => l_rs);
1649:
1650: IF l_debug_on THEN
1651: WSH_DEBUG_SV.logmsg(l_module_name,'return status:'||l_rs);
1652: WSH_DEBUG_SV.logmsg(l_module_name,'Organizations Count:'||l_organization_tab.Count);
1653: END IF;
1654:
1655: --Bug 5931958. Added the OR condition l_organization_tab.COUNT = 0
1648: x_return_status => l_rs);
1649:
1650: IF l_debug_on THEN
1651: WSH_DEBUG_SV.logmsg(l_module_name,'return status:'||l_rs);
1652: WSH_DEBUG_SV.logmsg(l_module_name,'Organizations Count:'||l_organization_tab.Count);
1653: END IF;
1654:
1655: --Bug 5931958. Added the OR condition l_organization_tab.COUNT = 0
1656: IF (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) OR
1658: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1659: FND_MESSAGE.SET_NAME('WSH','WSH_LOC_ORG_UNDEFINED');
1660: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1661: IF l_debug_on THEN
1662: WSH_DEBUG_SV.log(l_module_name, 'Error: Failed to get Organization from stop location id ',
1663: l_stop_location_id );
1664: END IF;
1665: GOTO trip_error;
1666: END IF;
1667:
1668: -- Assign first record in organization table
1669: l_trip_info_tab(i).vehicle_organization_id := l_organization_tab(l_organization_tab.FIRST);
1670: IF l_debug_on THEN
1671: WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_EXTERNAL_INTERFACE_SV.Validate_Item');
1672: END IF;
1673:
1674: WSH_EXTERNAL_INTERFACE_SV.Validate_Item
1675: (p_concatenated_segments => l_trip_info_tab(i).vehicle_item_name,
1677: x_inventory_item_id => l_trip_info_tab(i).vehicle_item_id,
1678: x_return_status => l_rs);
1679:
1680: IF l_debug_on THEN
1681: WSH_DEBUG_SV.logmsg(l_module_name,'return status:'||l_rs);
1682: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1683: END IF;
1684:
1685: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1678: x_return_status => l_rs);
1679:
1680: IF l_debug_on THEN
1681: WSH_DEBUG_SV.logmsg(l_module_name,'return status:'||l_rs);
1682: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1683: END IF;
1684:
1685: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1686: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1689: FND_MESSAGE.SET_TOKEN('ORGANIZATION',
1690: WSH_UTIL_CORE.get_org_name (p_organization_id =>l_organization_tab(l_organization_tab.FIRST)));
1691: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1692: IF l_debug_on THEN
1693: WSH_DEBUG_SV.log(l_module_name, 'Validate Item Failed: l_return_status', l_return_status);
1694: END IF;
1695: GOTO trip_error;
1696: END IF;
1697:
1697:
1698: END IF;--}
1699:
1700: IF l_debug_on THEN
1701: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1702: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1703: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1704: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1705: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1698: END IF;--}
1699:
1700: IF l_debug_on THEN
1701: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1702: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1703: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1704: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1705: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1706: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1699:
1700: IF l_debug_on THEN
1701: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1702: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1703: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1704: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1705: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1706: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1707: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1700: IF l_debug_on THEN
1701: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1702: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1703: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1704: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1705: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1706: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1707: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1708: WSH_DEBUG_SV.logmsg(l_module_name,'Tp Plan Name:'||l_trip_info_tab(i).tp_plan_name);
1701: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1702: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1703: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1704: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1705: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1706: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1707: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1708: WSH_DEBUG_SV.logmsg(l_module_name,'Tp Plan Name:'||l_trip_info_tab(i).tp_plan_name);
1709: WSH_DEBUG_SV.logmsg(l_module_name,'Interface Action Code:'||l_trip_info_tab(i).interface_action_code);
1702: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1703: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1704: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1705: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1706: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1707: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1708: WSH_DEBUG_SV.logmsg(l_module_name,'Tp Plan Name:'||l_trip_info_tab(i).tp_plan_name);
1709: WSH_DEBUG_SV.logmsg(l_module_name,'Interface Action Code:'||l_trip_info_tab(i).interface_action_code);
1710: END IF;
1703: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1704: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1705: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1706: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1707: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1708: WSH_DEBUG_SV.logmsg(l_module_name,'Tp Plan Name:'||l_trip_info_tab(i).tp_plan_name);
1709: WSH_DEBUG_SV.logmsg(l_module_name,'Interface Action Code:'||l_trip_info_tab(i).interface_action_code);
1710: END IF;
1711:
1704: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1705: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1706: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1707: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1708: WSH_DEBUG_SV.logmsg(l_module_name,'Tp Plan Name:'||l_trip_info_tab(i).tp_plan_name);
1709: WSH_DEBUG_SV.logmsg(l_module_name,'Interface Action Code:'||l_trip_info_tab(i).interface_action_code);
1710: END IF;
1711:
1712: --
1705: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1706: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1707: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1708: WSH_DEBUG_SV.logmsg(l_module_name,'Tp Plan Name:'||l_trip_info_tab(i).tp_plan_name);
1709: WSH_DEBUG_SV.logmsg(l_module_name,'Interface Action Code:'||l_trip_info_tab(i).interface_action_code);
1710: END IF;
1711:
1712: --
1713: -- This logic would be only for G_TMS_DELETE_CODE
1738: x_delivery_id_tab => l_delivery_id_tab, -- dlvy ids
1739: x_return_status => l_rs);
1740:
1741: IF l_debug_on THEN
1742: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after find_deliveries_for_trip'||l_rs);
1743: END IF;
1744:
1745: -- Handle return status here !!!
1746: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1745: -- Handle return status here !!!
1746: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1747: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1748: IF l_debug_on THEN
1749: WSH_DEBUG_SV.log(l_module_name, 'Find Deliveries for Trip Failed,l_return_status:', l_return_status);
1750: END IF;
1751: GOTO trip_error;
1752: END IF;
1753:
1767: l_delivery_id_tab(del_rec);
1768: l_unassigned_dlvy_version_tab(l_unassigned_delivery_id_tab.count) :=
1769: l_delivery_tab(l_delivery_id_tab(del_rec)).tms_version_number;
1770: IF l_debug_on THEN
1771: WSH_DEBUG_SV.logmsg(l_module_name,'Status of Delivery '||
1772: l_delivery_id_tab(del_rec)||'-->'||l_delivery_tab(l_delivery_id_tab(del_rec)).status_code);
1773: END IF;
1774: END IF;
1775: END LOOP; --}
1773: END IF;
1774: END IF;
1775: END LOOP; --}
1776: IF l_debug_on THEN
1777: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
1778: WSH_DEBUG_SV.logmsg(l_module_name,'Overall Unassign Count:'||l_unassigned_delivery_id_tab.count);
1779: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Version Count:'||l_unassigned_dlvy_version_tab.count);
1780: END IF;
1781:
1774: END IF;
1775: END LOOP; --}
1776: IF l_debug_on THEN
1777: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
1778: WSH_DEBUG_SV.logmsg(l_module_name,'Overall Unassign Count:'||l_unassigned_delivery_id_tab.count);
1779: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Version Count:'||l_unassigned_dlvy_version_tab.count);
1780: END IF;
1781:
1782: l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
1775: END LOOP; --}
1776: IF l_debug_on THEN
1777: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
1778: WSH_DEBUG_SV.logmsg(l_module_name,'Overall Unassign Count:'||l_unassigned_delivery_id_tab.count);
1779: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Version Count:'||l_unassigned_dlvy_version_tab.count);
1780: END IF;
1781:
1782: l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
1783: l_del_action_prms.action_code := 'UNASSIGN-TRIP';
1795: x_msg_count => l_msg_count,
1796: x_msg_data => l_msg_data);
1797:
1798: IF l_debug_on THEN
1799: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Unassign Delivery'||l_rs);
1800: END IF;
1801: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1802: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1803: IF l_debug_on THEN
1800: END IF;
1801: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1802: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1803: IF l_debug_on THEN
1804: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery from Trip Failed: l_return_status', l_return_status);
1805: END IF;
1806: GOTO trip_error;
1807: END IF;
1808: END IF;--} -- delivery_id_tab.count
1872:
1873: l_in_rec.caller := 'FTE_TMS_INTEGRATION';
1874:
1875: IF l_debug_on THEN
1876: WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_TRIPS_GRP.Create_Update_Trip:'||l_in_rec.action_code);
1877: END IF;
1878:
1879: WSH_TRIPS_GRP.Create_Update_Trip(
1880: p_api_version_number => 1.0,
1890:
1891: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1892: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1893: IF l_debug_on THEN
1894: WSH_DEBUG_SV.log(l_module_name, 'Create Update Trip Failed: l_return_status', l_return_status);
1895: END IF;
1896: GOTO trip_error;
1897: END IF;
1898:
1904: l_trip_id := l_trip_info_tab(i).trip_id;
1905: END IF;
1906:
1907: IF l_debug_on THEN
1908: WSH_DEBUG_SV.logmsg(l_module_name,'Create_Update_Trip,return status:'||l_rs);
1909: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_trip_id);
1910: END IF;
1911:
1912: END;--} process trip
1905: END IF;
1906:
1907: IF l_debug_on THEN
1908: WSH_DEBUG_SV.logmsg(l_module_name,'Create_Update_Trip,return status:'||l_rs);
1909: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_trip_id);
1910: END IF;
1911:
1912: END;--} process trip
1913:
1914:
1915: -- 4d. Process Trip Stops
1916: -- populate stop UOM in case of creation
1917: IF l_debug_on THEN
1918: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1919: WSH_DEBUG_SV.logmsg(l_module_name,'4d. PROCESS TRIP STOPS');
1920: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1921: END IF;
1922:
1915: -- 4d. Process Trip Stops
1916: -- populate stop UOM in case of creation
1917: IF l_debug_on THEN
1918: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1919: WSH_DEBUG_SV.logmsg(l_module_name,'4d. PROCESS TRIP STOPS');
1920: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1921: END IF;
1922:
1923: DECLARE
1916: -- populate stop UOM in case of creation
1917: IF l_debug_on THEN
1918: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1919: WSH_DEBUG_SV.logmsg(l_module_name,'4d. PROCESS TRIP STOPS');
1920: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1921: END IF;
1922:
1923: DECLARE
1924: -- Find Deliveries associated to a stop
1972: l_return_status := 'T';
1973: FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
1974: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1975: IF l_debug_on THEN
1976: WSH_DEBUG_SV.log(l_module_name, 'Invalid Stop Location, l_return_status:', l_return_status);
1977: END IF;
1978: GOTO trip_error;
1979: END;--}
1980:
1987: l_return_status := 'T';-- special case, need to handle error a bit differently
1988: FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
1989: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1990: IF l_debug_on THEN
1991: WSH_DEBUG_SV.log(l_module_name, 'Invalid Stop Location, l_return_status:', l_return_status);
1992: END IF;
1993: GOTO trip_error;
1994: END IF;
1995:
2006: -- deliveries
2007: FOR rec in c_get_stops(l_trip_info_tab(i).trip_id)
2008: LOOP--{
2009: IF l_debug_on THEN
2010: WSH_DEBUG_SV.logmsg(l_module_name,'===== EBS STOPS =======');
2011: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2012: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2013: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2014: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2007: FOR rec in c_get_stops(l_trip_info_tab(i).trip_id)
2008: LOOP--{
2009: IF l_debug_on THEN
2010: WSH_DEBUG_SV.logmsg(l_module_name,'===== EBS STOPS =======');
2011: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2012: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2013: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2014: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2015: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Location id:'||rec.physical_location_id);
2008: LOOP--{
2009: IF l_debug_on THEN
2010: WSH_DEBUG_SV.logmsg(l_module_name,'===== EBS STOPS =======');
2011: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2012: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2013: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2014: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2015: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Location id:'||rec.physical_location_id);
2016: END IF;
2009: IF l_debug_on THEN
2010: WSH_DEBUG_SV.logmsg(l_module_name,'===== EBS STOPS =======');
2011: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2012: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2013: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2014: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2015: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Location id:'||rec.physical_location_id);
2016: END IF;
2017:
2010: WSH_DEBUG_SV.logmsg(l_module_name,'===== EBS STOPS =======');
2011: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2012: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2013: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2014: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2015: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Location id:'||rec.physical_location_id);
2016: END IF;
2017:
2018: l_stop_matches := 'N';
2011: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2012: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2013: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2014: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2015: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Location id:'||rec.physical_location_id);
2016: END IF;
2017:
2018: l_stop_matches := 'N';
2019: FOR stop_index in l_stop_local_tab.FIRST..l_stop_local_tab.LAST
2018: l_stop_matches := 'N';
2019: FOR stop_index in l_stop_local_tab.FIRST..l_stop_local_tab.LAST
2020: LOOP--{
2021: IF l_debug_on THEN
2022: WSH_DEBUG_SV.logmsg(l_module_name,'===== GC3 STOP DESCRIPTION =======');
2023: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||l_stop_local_tab(stop_index).stop_location_id);
2024: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||l_stop_local_tab(stop_index).stop_sequence_number);
2025: END IF;
2026:
2019: FOR stop_index in l_stop_local_tab.FIRST..l_stop_local_tab.LAST
2020: LOOP--{
2021: IF l_debug_on THEN
2022: WSH_DEBUG_SV.logmsg(l_module_name,'===== GC3 STOP DESCRIPTION =======');
2023: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||l_stop_local_tab(stop_index).stop_location_id);
2024: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||l_stop_local_tab(stop_index).stop_sequence_number);
2025: END IF;
2026:
2027: -- Compare the GC3 sent stop location + stop sequence number
2020: LOOP--{
2021: IF l_debug_on THEN
2022: WSH_DEBUG_SV.logmsg(l_module_name,'===== GC3 STOP DESCRIPTION =======');
2023: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||l_stop_local_tab(stop_index).stop_location_id);
2024: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||l_stop_local_tab(stop_index).stop_sequence_number);
2025: END IF;
2026:
2027: -- Compare the GC3 sent stop location + stop sequence number
2028: IF to_number(l_stop_local_tab(stop_index).stop_location_id) = rec.stop_location_id AND
2080: l_stop_local_tab(stop_index).attribute15 := rec.attribute15;
2081:
2082: -- End of Populating other fields
2083: IF l_debug_on THEN
2084: WSH_DEBUG_SV.logmsg(l_module_name,'Matching Stop found,id:'||rec.stop_id);
2085: END IF;
2086: EXIT; --from the l_stop_local_tab loop
2087: ELSE -- also check for Internal Location
2088:
2100: l_return_status := 'T';-- special case, need to handle error a bit differently
2101: FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
2102: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2103: IF l_debug_on THEN
2104: WSH_DEBUG_SV.log(l_module_name, 'Invalid Stop Location, l_return_status:', l_return_status);
2105: END IF;
2106: GOTO trip_error;
2107: END IF;
2108:
2106: GOTO trip_error;
2107: END IF;
2108:
2109: IF l_debug_on THEN
2110: WSH_DEBUG_SV.log(l_module_name, 'Physical Location:',l_physical_loc_id);
2111: END IF;
2112:
2113: -- Compare the GC3 sent stop location + stop sequence number
2114: IF to_number(l_stop_local_tab(stop_index).stop_location_id) = l_physical_loc_id AND
2166: l_stop_local_tab(stop_index).attribute15 := rec.attribute15;
2167: -- End of Populating other fields
2168:
2169: IF l_debug_on THEN
2170: WSH_DEBUG_SV.logmsg(l_module_name,'Matching Stop found,id:'||rec.stop_id);
2171: END IF;
2172: EXIT; --from the l_stop_local_tab loop
2173: END IF;--}
2174: END IF;--}
2196: -- are linked to 1 delivery, then donot launch unassign-trip twice
2197: l_is_duplicate := 'N';
2198:
2199: IF l_debug_on THEN
2200: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Dlvy Tab count:'||l_unassigned_delivery_id_tab.count);
2201: END IF;
2202: IF l_del_attrs.count > 0 THEN
2203: FOR duplicate in l_del_attrs.FIRST..l_del_attrs.LAST
2204: LOOP
2209: END LOOP;
2210: END IF;
2211:
2212: IF l_debug_on THEN
2213: WSH_DEBUG_SV.logmsg(l_module_name,'Is Duplicate:'||l_is_duplicate);
2214: END IF;
2215:
2216: -- Only increase the count for non-duplicate deliveries
2217: -- Cursor would have selected deliveries with dummy stops, Internal Location
2226:
2227: END IF;
2228:
2229: IF l_debug_on THEN
2230: WSH_DEBUG_SV.logmsg(l_module_name,'After populating l_del_attrs count:'||l_del_attrs.count);
2231: WSH_DEBUG_SV.logmsg(l_module_name,'After populating Unassign Dlvy Tab count:'||l_unassigned_delivery_id_tab.count);
2232: END IF;
2233:
2234: END LOOP;
2227: END IF;
2228:
2229: IF l_debug_on THEN
2230: WSH_DEBUG_SV.logmsg(l_module_name,'After populating l_del_attrs count:'||l_del_attrs.count);
2231: WSH_DEBUG_SV.logmsg(l_module_name,'After populating Unassign Dlvy Tab count:'||l_unassigned_delivery_id_tab.count);
2232: END IF;
2233:
2234: END LOOP;
2235:
2233:
2234: END LOOP;
2235:
2236: IF l_debug_on THEN
2237: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries associated to the stop:'||l_del_attrs.count);
2238: END IF;
2239:
2240: IF l_del_attrs.count > 0 THEN--{
2241: l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
2254: x_msg_count => l_msg_count,
2255: x_msg_data => l_msg_data);
2256:
2257: IF l_debug_on THEN
2258: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Unassign Delivery'||l_rs);
2259: END IF;
2260:
2261: -- Handle error here !!!, should these deliveries be deleted from unassign table list??
2262: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2261: -- Handle error here !!!, should these deliveries be deleted from unassign table list??
2262: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2263: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2264: IF l_debug_on THEN
2265: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery Failed: l_return_status', l_return_status);
2266: END IF;
2267: GOTO trip_error;
2268: END IF;
2269: END IF;--}
2287: x_msg_data => l_msg_data);
2288:
2289: -- Handle return status here, treat warning as success
2290: IF l_debug_on THEN
2291: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after deleting stop'||l_rs);
2292: END IF;
2293: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2294: -- go to trip_error
2295: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2293: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2294: -- go to trip_error
2295: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2296: IF l_debug_on THEN
2297: WSH_DEBUG_SV.log(l_module_name, 'Delete Trip Stop Failed: l_return_status', l_return_status);
2298: END IF;
2299: GOTO trip_error;
2300: END IF;
2301: END IF;--} -- if l_stop_matches = 'N'
2304:
2305: END IF;--} -- if l_trip_info_tab(i).trip_id is not null, trip pre-existed in EBS
2306:
2307: IF l_debug_on THEN
2308: WSH_DEBUG_SV.logmsg(l_module_name,'Before Internal Locations Check, Stop Local Table, Count'||l_stop_local_tab.count);
2309: END IF;
2310:
2311: -- Internal Locations Check here
2312: -- Call Process Internal Locations Here
2318:
2319: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2320: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2321: IF l_debug_on THEN
2322: WSH_DEBUG_SV.log(l_module_name, 'Process Internal Location, l_return_status:', l_return_status);
2323: END IF;
2324: GOTO trip_error;
2325: END IF;
2326: END IF;--}
2325: END IF;
2326: END IF;--}
2327:
2328: IF l_debug_on THEN
2329: WSH_DEBUG_SV.logmsg(l_module_name,'After Internal Locations Check,Stop Local Table, Count'||l_stop_local_tab.count);
2330: END IF;
2331:
2332: -- Loop across l_stop_local_tab for Create and Update stops
2333: -- Initialize l_stop_attrs
2364: --
2365: -- ECO 5101760
2366: -- Convert Stop level planned dates, based on the timezone code
2367: IF l_debug_on THEN
2368: WSH_DEBUG_SV.log(l_module_name,'OTM sent Planned Arrival Date',l_stop_local_tab(stop_rec).planned_arrival_date);
2369: WSH_DEBUG_SV.log(l_module_name,'OTM sent Planned Departure Date',l_stop_local_tab(stop_rec).planned_departure_date);
2370: END IF;
2371:
2372: get_server_time
2365: -- ECO 5101760
2366: -- Convert Stop level planned dates, based on the timezone code
2367: IF l_debug_on THEN
2368: WSH_DEBUG_SV.log(l_module_name,'OTM sent Planned Arrival Date',l_stop_local_tab(stop_rec).planned_arrival_date);
2369: WSH_DEBUG_SV.log(l_module_name,'OTM sent Planned Departure Date',l_stop_local_tab(stop_rec).planned_departure_date);
2370: END IF;
2371:
2372: get_server_time
2373: (p_source_time => l_stop_local_tab(stop_rec).planned_arrival_date,
2379:
2380: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2381: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2382: IF l_debug_on THEN
2383: WSH_DEBUG_SV.log(l_module_name, 'Get Server Time for Planned Arrival Date, l_return_status:', l_return_status);
2384: END IF;
2385: GOTO trip_error;
2386: END IF;
2387:
2395:
2396: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2397: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2398: IF l_debug_on THEN
2399: WSH_DEBUG_SV.log(l_module_name, 'Get Server Time for Planned Arrival Date, l_return_status:', l_return_status);
2400: END IF;
2401: GOTO trip_error;
2402: END IF;
2403:
2404: l_stop_attrs(l_stop_count).PLANNED_ARRIVAL_DATE := l_stop_local_tab(stop_rec).planned_arrival_date;
2405: l_stop_attrs(l_stop_count).PLANNED_DEPARTURE_DATE := l_stop_local_tab(stop_rec).planned_departure_date;
2406:
2407: IF l_debug_on THEN
2408: WSH_DEBUG_SV.log(l_module_name,'Converted Planned Arrival Date',l_stop_attrs(l_stop_count).PLANNED_ARRIVAL_DATE);
2409: WSH_DEBUG_SV.log(l_module_name,'Converted Planned Departure Date',l_stop_attrs(l_stop_count).PLANNED_departure_DATE);
2410: END IF;
2411: -- End of ECO 5101760
2412: --
2405: l_stop_attrs(l_stop_count).PLANNED_DEPARTURE_DATE := l_stop_local_tab(stop_rec).planned_departure_date;
2406:
2407: IF l_debug_on THEN
2408: WSH_DEBUG_SV.log(l_module_name,'Converted Planned Arrival Date',l_stop_attrs(l_stop_count).PLANNED_ARRIVAL_DATE);
2409: WSH_DEBUG_SV.log(l_module_name,'Converted Planned Departure Date',l_stop_attrs(l_stop_count).PLANNED_departure_DATE);
2410: END IF;
2411: -- End of ECO 5101760
2412: --
2413: l_stop_attrs(l_stop_count).DEPARTURE_GROSS_WEIGHT := nvl(l_stop_local_tab(stop_rec).departure_gross_weight,0);
2429: LOOP--{
2430: IF l_dleg_local_tab(dleg_rec).pick_up_stop_interface_id = l_stop_local_tab(stop_rec).stop_interface_id THEN
2431: -- For 1st delivery, get the organization id
2432: IF l_debug_on THEN
2433: WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_WV_UTILS.GET_DEFAULT_UOMS for pickup');
2434: END IF;
2435: --
2436: wsh_wv_utils.get_default_uoms
2437: (p_organization_id => l_dleg_local_tab(dleg_rec).organization_id,
2438: x_weight_uom_code => l_stop_local_tab(stop_rec).weight_uom_code,
2439: x_volume_uom_code => l_stop_local_tab(stop_rec).volume_uom_code,
2440: x_return_status => l_rs);
2441: IF l_debug_on THEN
2442: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_WV_UTILS.GET_DEFAULT_UOMS'||l_rs);
2443: END IF;
2444: -- Handle return Status error!!!
2445: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2446: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2444: -- Handle return Status error!!!
2445: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2446: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2447: IF l_debug_on THEN
2448: WSH_DEBUG_SV.log(l_module_name, 'Get_Default_UOM failed: l_return_status', l_return_status);
2449: END IF;
2450: GOTO trip_error;
2451: END IF;
2452:
2450: GOTO trip_error;
2451: END IF;
2452:
2453: IF l_debug_on THEN
2454: WSH_DEBUG_SV.logmsg(l_module_name,'WT UOM:'||l_stop_local_tab(stop_rec).weight_uom_code||'VOLUME UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2455: END IF;
2456: ELSE -- to find UOM for dropoff stops, which are not pickups
2457: IF l_dleg_local_tab(dleg_rec).drop_off_stop_interface_id = l_stop_local_tab(stop_rec).stop_interface_id THEN--{
2458: -- For 1st delivery, get the organization id
2456: ELSE -- to find UOM for dropoff stops, which are not pickups
2457: IF l_dleg_local_tab(dleg_rec).drop_off_stop_interface_id = l_stop_local_tab(stop_rec).stop_interface_id THEN--{
2458: -- For 1st delivery, get the organization id
2459: IF l_debug_on THEN
2460: WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_WV_UTILS.GET_DEFAULT_UOMS for dropoff');
2461: END IF;
2462: --
2463: wsh_wv_utils.get_default_uoms
2464: (p_organization_id => l_dleg_local_tab(dleg_rec).organization_id,
2465: x_weight_uom_code => l_stop_local_tab(stop_rec).weight_uom_code,
2466: x_volume_uom_code => l_stop_local_tab(stop_rec).volume_uom_code,
2467: x_return_status => l_rs);
2468: IF l_debug_on THEN
2469: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_WV_UTILS.GET_DEFAULT_UOMS'||l_rs);
2470: END IF;
2471: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2472: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2473: IF l_debug_on THEN
2470: END IF;
2471: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2472: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2473: IF l_debug_on THEN
2474: WSH_DEBUG_SV.log(l_module_name, 'Get_Default_UOM Failed: l_return_status', l_return_status);
2475: END IF;
2476: GOTO trip_error;
2477: END IF;
2478: -- Handle return Status error!!!
2477: END IF;
2478: -- Handle return Status error!!!
2479:
2480: IF l_debug_on THEN
2481: WSH_DEBUG_SV.logmsg(l_module_name,'WT UOM:'||l_stop_local_tab(stop_rec).weight_uom_code||'VOLUME UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2482: END IF;
2483:
2484: END IF; --}
2485: END IF;
2523: l_stop_attrs(l_stop_count).ATTRIBUTE14 := l_stop_local_tab(stop_rec).attribute14;
2524: l_stop_attrs(l_stop_count).ATTRIBUTE15 := l_stop_local_tab(stop_rec).attribute15;
2525:
2526: IF l_debug_on THEN
2527: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||l_stop_local_tab(stop_rec).stop_id);
2528: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2529: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2530: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2531: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2524: l_stop_attrs(l_stop_count).ATTRIBUTE15 := l_stop_local_tab(stop_rec).attribute15;
2525:
2526: IF l_debug_on THEN
2527: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||l_stop_local_tab(stop_rec).stop_id);
2528: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2529: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2530: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2531: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2532: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2525:
2526: IF l_debug_on THEN
2527: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||l_stop_local_tab(stop_rec).stop_id);
2528: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2529: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2530: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2531: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2532: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2533: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2526: IF l_debug_on THEN
2527: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||l_stop_local_tab(stop_rec).stop_id);
2528: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2529: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2530: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2531: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2532: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2533: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2534: WSH_DEBUG_SV.logmsg(l_module_name,'Before Calling Create_update_Stop,count:'||l_stop_attrs.count);
2527: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||l_stop_local_tab(stop_rec).stop_id);
2528: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2529: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2530: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2531: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2532: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2533: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2534: WSH_DEBUG_SV.logmsg(l_module_name,'Before Calling Create_update_Stop,count:'||l_stop_attrs.count);
2535: END IF;
2528: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2529: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2530: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2531: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2532: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2533: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2534: WSH_DEBUG_SV.logmsg(l_module_name,'Before Calling Create_update_Stop,count:'||l_stop_attrs.count);
2535: END IF;
2536:
2529: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2530: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2531: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2532: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2533: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2534: WSH_DEBUG_SV.logmsg(l_module_name,'Before Calling Create_update_Stop,count:'||l_stop_attrs.count);
2535: END IF;
2536:
2537: wsh_trip_stops_grp.Create_Update_Stop(
2530: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2531: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2532: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2533: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2534: WSH_DEBUG_SV.logmsg(l_module_name,'Before Calling Create_update_Stop,count:'||l_stop_attrs.count);
2535: END IF;
2536:
2537: wsh_trip_stops_grp.Create_Update_Stop(
2538: p_api_version_number => 1.0,
2550: -- Handle return status and error Here !!!
2551: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2552: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2553: IF l_debug_on THEN
2554: WSH_DEBUG_SV.log(l_module_name, 'Create Update Trip Stop Failed: l_return_status', l_return_status);
2555: END IF;
2556: GOTO trip_error;
2557: END IF;
2558:
2562: l_stop_local_tab(stop_rec).stop_id := l_stop_out_tab(l_stop_out_tab.FIRST).stop_id;
2563: END IF;
2564:
2565: IF l_debug_on THEN
2566: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Create_Update_stop'||l_rs);
2567: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id after Create_Update_stop'||l_stop_local_tab(stop_rec).stop_id);
2568: END IF;
2569:
2570: -- OTM Dock Door App Sched Proj
2563: END IF;
2564:
2565: IF l_debug_on THEN
2566: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Create_Update_stop'||l_rs);
2567: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id after Create_Update_stop'||l_stop_local_tab(stop_rec).stop_id);
2568: END IF;
2569:
2570: -- OTM Dock Door App Sched Proj
2571: DECLARE
2603: CLOSE c_dock_appt_rec;
2604: IF (l_organization_id IS NOT NULL) THEN --{
2605: IF (wsh_util_validate.check_wms_org(l_organization_id) = 'Y') THEN --{
2606: IF l_debug_on THEN
2607: WSH_DEBUG_SV.log(l_module_name,'Calling WSH_SHIPPING_PARAMS_PVT.GET',WSH_DEBUG_SV.C_PROC_LEVEL);
2608: END IF;
2609: WSH_SHIPPING_PARAMS_PVT.GET(p_organization_id => l_organization_id,
2610: x_param_info => l_shipping_parameters,
2611: x_return_status => l_rs);
2612:
2613: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2614: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2615: IF l_debug_on THEN
2616: WSH_DEBUG_SV.log(l_module_name, 'WSH_SHIPPING_PARAMS_PVT.GET Failed: l_return_status', l_return_status);
2617: END IF;
2618: GOTO trip_error;
2619: END IF;
2620:
2630:
2631: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2632: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2633: IF l_debug_on THEN
2634: WSH_DEBUG_SV.log(l_module_name, 'Get Server Time for Dock Door Appointment Start Time, l_return_status:', l_return_status);
2635: END IF;
2636: GOTO trip_error;
2637: END IF;
2638: END IF;
2647:
2648: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2649: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2650: IF l_debug_on THEN
2651: WSH_DEBUG_SV.log(l_module_name, 'Get Server Time for Dock Door Appointment End Time, l_return_status:', l_return_status);
2652: END IF;
2653: GOTO trip_error;
2654: END IF;
2655: END IF;
2662: l_dock_appt_tab(l_dock_appt_index).start_time := x_start_time;
2663: l_dock_appt_tab(l_dock_appt_index).end_time := x_end_time;
2664:
2665: IF l_debug_on THEN
2666: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').dock_name',l_dock_appt_tab(l_dock_appt_index).dock_name);
2667: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').stop_id',l_dock_appt_tab(l_dock_appt_index).trip_stop_id);
2668: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').organization_id',l_dock_appt_tab(l_dock_appt_index).organization_id);
2669: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').start_time',l_dock_appt_tab(l_dock_appt_index).start_time);
2670: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').end_time',l_dock_appt_tab(l_dock_appt_index).end_time);
2663: l_dock_appt_tab(l_dock_appt_index).end_time := x_end_time;
2664:
2665: IF l_debug_on THEN
2666: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').dock_name',l_dock_appt_tab(l_dock_appt_index).dock_name);
2667: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').stop_id',l_dock_appt_tab(l_dock_appt_index).trip_stop_id);
2668: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').organization_id',l_dock_appt_tab(l_dock_appt_index).organization_id);
2669: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').start_time',l_dock_appt_tab(l_dock_appt_index).start_time);
2670: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').end_time',l_dock_appt_tab(l_dock_appt_index).end_time);
2671: END IF;
2664:
2665: IF l_debug_on THEN
2666: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').dock_name',l_dock_appt_tab(l_dock_appt_index).dock_name);
2667: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').stop_id',l_dock_appt_tab(l_dock_appt_index).trip_stop_id);
2668: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').organization_id',l_dock_appt_tab(l_dock_appt_index).organization_id);
2669: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').start_time',l_dock_appt_tab(l_dock_appt_index).start_time);
2670: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').end_time',l_dock_appt_tab(l_dock_appt_index).end_time);
2671: END IF;
2672: END IF; --} Cheking dock_appt_schedling_flag
2665: IF l_debug_on THEN
2666: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').dock_name',l_dock_appt_tab(l_dock_appt_index).dock_name);
2667: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').stop_id',l_dock_appt_tab(l_dock_appt_index).trip_stop_id);
2668: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').organization_id',l_dock_appt_tab(l_dock_appt_index).organization_id);
2669: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').start_time',l_dock_appt_tab(l_dock_appt_index).start_time);
2670: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').end_time',l_dock_appt_tab(l_dock_appt_index).end_time);
2671: END IF;
2672: END IF; --} Cheking dock_appt_schedling_flag
2673: END IF; --} Checking WMS Org
2666: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').dock_name',l_dock_appt_tab(l_dock_appt_index).dock_name);
2667: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').stop_id',l_dock_appt_tab(l_dock_appt_index).trip_stop_id);
2668: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').organization_id',l_dock_appt_tab(l_dock_appt_index).organization_id);
2669: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').start_time',l_dock_appt_tab(l_dock_appt_index).start_time);
2670: WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').end_time',l_dock_appt_tab(l_dock_appt_index).end_time);
2671: END IF;
2672: END IF; --} Cheking dock_appt_schedling_flag
2673: END IF; --} Checking WMS Org
2674: END IF; --} Checking l_organization_id as NULL
2675: EXCEPTION
2676: WHEN OTHERS THEN
2677: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2678: IF l_debug_on THEN
2679: WSH_DEBUG_SV.log(l_module_name, 'Error while fetching Dock Door Appointment Scheduling Info.');
2680: END IF;
2681: IF (c_dock_appt_rec%ISOPEN) THEN
2682: CLOSE c_dock_appt_rec;
2683: END IF;
2693: BEGIN
2694: FOR st in c_1
2695: LOOP
2696: IF l_debug_on THEN
2697: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||st.stop_id);
2698: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||st.stop_location_id);
2699: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||st.stop_sequence_number);
2700: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||st.physical_stop_id);
2701: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Loc id:'||st.physical_location_id);
2694: FOR st in c_1
2695: LOOP
2696: IF l_debug_on THEN
2697: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||st.stop_id);
2698: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||st.stop_location_id);
2699: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||st.stop_sequence_number);
2700: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||st.physical_stop_id);
2701: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Loc id:'||st.physical_location_id);
2702: END IF;
2695: LOOP
2696: IF l_debug_on THEN
2697: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||st.stop_id);
2698: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||st.stop_location_id);
2699: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||st.stop_sequence_number);
2700: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||st.physical_stop_id);
2701: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Loc id:'||st.physical_location_id);
2702: END IF;
2703: END LOOP;
2696: IF l_debug_on THEN
2697: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||st.stop_id);
2698: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||st.stop_location_id);
2699: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||st.stop_sequence_number);
2700: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||st.physical_stop_id);
2701: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Loc id:'||st.physical_location_id);
2702: END IF;
2703: END LOOP;
2704: END;
2697: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||st.stop_id);
2698: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||st.stop_location_id);
2699: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||st.stop_sequence_number);
2700: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||st.physical_stop_id);
2701: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Loc id:'||st.physical_location_id);
2702: END IF;
2703: END LOOP;
2704: END;
2705:
2710:
2711:
2712: -- 4e. Process Deliveries
2713: IF l_debug_on THEN
2714: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2715: WSH_DEBUG_SV.logmsg(l_module_name,'4e. PROCESS DELIVERIES');
2716: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2717: END IF;
2718:
2711:
2712: -- 4e. Process Deliveries
2713: IF l_debug_on THEN
2714: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2715: WSH_DEBUG_SV.logmsg(l_module_name,'4e. PROCESS DELIVERIES');
2716: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2717: END IF;
2718:
2719: DECLARE
2712: -- 4e. Process Deliveries
2713: IF l_debug_on THEN
2714: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2715: WSH_DEBUG_SV.logmsg(l_module_name,'4e. PROCESS DELIVERIES');
2716: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2717: END IF;
2718:
2719: DECLARE
2720:
2755: IF ( NVL(p_latest_version,'Y') = 'N' AND l_dleg_local_tab(del_version).tms_interface_flag NOT IN ('UR','UP') ) THEN
2756: --{
2757: -- raise error
2758: IF l_debug_on THEN
2759: WSH_DEBUG_SV.logmsg(l_module_name,'EBS and OTM version number donot match');
2760: END IF;
2761: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_IB_NOT_UPD_VERSION_ERR');
2762: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2763: l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
2768: IF NVL(p_latest_version,'Y') = 'Y' THEN
2769: --{
2770: -- raise error
2771: IF l_debug_on THEN
2772: WSH_DEBUG_SV.logmsg(l_module_name,'EBS and OTM version number donot match');
2773: END IF;
2774: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_IB_VERSION_ERROR');
2775: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2776: l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
2786: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2787: -- go to trip_error
2788: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2789: IF l_debug_on THEN
2790: WSH_DEBUG_SV.log(l_module_name, 'Version Comparison failed,l_return_status:', l_return_status);
2791: END IF;
2792: GOTO trip_error;
2793: END IF;
2794: -- End of Bug 5134725
2804: x_delivery_id_tab => l_delivery_id_tab, -- dlvy ids
2805: x_return_status => l_rs);
2806:
2807: IF l_debug_on THEN
2808: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after find_deliveries_for_trip'||l_rs);
2809: END IF;
2810: -- Handle return status here !!!
2811: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2812: -- go to trip_error
2811: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2812: -- go to trip_error
2813: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2814: IF l_debug_on THEN
2815: WSH_DEBUG_SV.log(l_module_name, 'Find Deliveries for Trip Failed,l_return_status:', l_return_status);
2816: END IF;
2817: GOTO trip_error;
2818: END IF;
2819:
2823: IF l_delivery_id_tab.count > 0 THEN
2824: FOR del_rec IN l_delivery_id_tab.FIRST..l_delivery_id_tab.LAST
2825: LOOP--{
2826: IF l_debug_on THEN
2827: WSH_DEBUG_SV.log(l_module_name,'Delivery id:',l_delivery_id_tab(del_rec));
2828: END IF;
2829: l_del_matches := 'N';
2830: FOR del_intface_rec IN l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
2831: LOOP--{
2833: -- Compare the list
2834: IF (l_delivery_id_tab(del_rec) = l_dleg_local_tab(del_intface_rec).delivery_id) THEN
2835: l_del_matches := 'Y'; --matching delivery found
2836: IF l_debug_on THEN
2837: WSH_DEBUG_SV.log(l_module_name,'Match Delivery id:',l_dleg_local_tab(del_intface_rec).delivery_id);
2838: END IF;
2839: EXIT;
2840: END IF;
2841: END LOOP;--}
2844: -- This EBS delivery is still on the same GC3 trip
2845: -- Will compare if the stops/legs have changed for this assignment
2846: -- Delivery is on same trip, but new stop linking,then unassign and assign
2847: IF l_debug_on THEN
2848: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery:'||l_delivery_id_tab(del_rec)||' is already assigned to Trip:'||l_trip_info_tab(i).trip_id);
2849: END IF;
2850: ELSE -- l_del_matches = 'N'
2851: -- This EBS delivery is to be unassigned from the GC3 trip
2852: IF l_debug_on THEN
2849: END IF;
2850: ELSE -- l_del_matches = 'N'
2851: -- This EBS delivery is to be unassigned from the GC3 trip
2852: IF l_debug_on THEN
2853: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery:'||l_delivery_id_tab(del_rec)||' to be unassigned from Trip:'||l_trip_info_tab(i).trip_id);
2854: END IF;
2855: -- Populate l_del_attrs for call to delivery_action()
2856: l_del_attrs(l_del_attrs.count + 1).delivery_id := l_delivery_id_tab(del_rec);
2857: l_del_attrs(l_del_attrs.count).organization_id :=
2869: -- The above Unassignments can be grouped together, as they are for
2870: -- Deliveries from SAME TRIP.
2871: -- Bulk Unassignment of Deliveries From Trip
2872: IF l_debug_on THEN
2873: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
2874: END IF;
2875:
2876: IF l_del_attrs.count > 0 THEN--{
2877: l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
2890: x_msg_count => l_msg_count,
2891: x_msg_data => l_msg_data);
2892:
2893: IF l_debug_on THEN
2894: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Unassign Delivery'||l_rs);
2895: END IF;
2896: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2897: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2898: IF l_debug_on THEN
2895: END IF;
2896: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2897: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2898: IF l_debug_on THEN
2899: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery from Trip Failed: l_return_status', l_return_status);
2900: END IF;
2901: GOTO trip_error;
2902: END IF;
2903: END IF;--}
2917: x_unassign_id_tab => l_unassigned_delivery_id_tab,
2918: --x_unassign_ver_tab => l_unassigned_dlvy_version_tab,
2919: x_return_status => l_rs);
2920: IF l_debug_on THEN
2921: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Comparing Trips:'||l_rs);
2922: END IF;
2923: -- Handle return Status here !!!
2924: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2925: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2923: -- Handle return Status here !!!
2924: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2925: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2926: IF l_debug_on THEN
2927: WSH_DEBUG_SV.log(l_module_name, 'Compare Trip for Deliveries Failed: l_return_status', l_return_status);
2928: END IF;
2929: GOTO trip_error;
2930: END IF;
2931:
2929: GOTO trip_error;
2930: END IF;
2931:
2932: IF l_debug_on THEN
2933: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Delivery Tab Count:'||l_unassigned_delivery_id_tab.count);
2934: END IF;
2935:
2936: --
2937: -- ASSIGN ALL DELIVERIES in l_dleg_local_tab to l_trip_info_tab(i).trip_id
2951: -- identical for all deliveries in l_dleg_local_tab
2952: l_del_action_prms.trip_id := l_trip_id;
2953:
2954: IF l_debug_on THEN
2955: WSH_DEBUG_SV.log(l_module_name, 'DLEG LOCAL TAB COUNT:', l_dleg_local_tab.count);
2956: WSH_DEBUG_SV.log(l_module_name, 'DLVY ID TAB COUNT:', l_dlvy_id_tab.count);
2957: END IF;
2958:
2959: IF l_dleg_local_tab.count > 0 THEN--{
2952: l_del_action_prms.trip_id := l_trip_id;
2953:
2954: IF l_debug_on THEN
2955: WSH_DEBUG_SV.log(l_module_name, 'DLEG LOCAL TAB COUNT:', l_dleg_local_tab.count);
2956: WSH_DEBUG_SV.log(l_module_name, 'DLVY ID TAB COUNT:', l_dlvy_id_tab.count);
2957: END IF;
2958:
2959: IF l_dleg_local_tab.count > 0 THEN--{
2960: FOR assign_count IN l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
2959: IF l_dleg_local_tab.count > 0 THEN--{
2960: FOR assign_count IN l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
2961: LOOP--{
2962: IF l_debug_on THEN
2963: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery:'||l_dleg_local_tab(assign_count).delivery_id||' has processed flag of:'||l_dleg_local_tab(assign_count).processed_flag);
2964: END IF;
2965: -- No need to process the deliveries which still have the same assignment
2966: -- as before
2967: IF l_dleg_local_tab(assign_count).processed_flag = 'Y' THEN--{
2968: l_dlvy_id_tab(l_dlvy_id_tab.count + 1) := l_dleg_local_tab(assign_count).delivery_id;
2969: l_dlvy_version_tab(l_dlvy_id_tab.count) := l_dleg_local_tab(assign_count).tms_version_number;
2970: -- skip to next delivery
2971: IF l_debug_on THEN
2972: WSH_DEBUG_SV.log(l_module_name, 'Skip Delivery:', l_dleg_local_tab(assign_count).delivery_id);
2973: END IF;
2974: -- do not exit
2975:
2976: ELSE--} --{
2981: l_del_attrs(1).delivery_id := l_dleg_local_tab(assign_count).delivery_id;
2982: l_del_attrs(1).organization_id := l_dleg_local_tab(assign_count).organization_id;
2983:
2984: IF l_debug_on THEN
2985: WSH_DEBUG_SV.log(l_module_name, 'Process Delivery:',l_dleg_local_tab(assign_count).delivery_id);
2986: END IF;
2987:
2988: -- Need to find EBS stop id, GC3 sent planned dates
2989: -- Location and Sequence Number are part of dleg data structure
3023: END IF;
3024: END LOOP;
3025:
3026: IF l_debug_on THEN
3027: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3028: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Assign Delivery to Trip API');
3029: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3030: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3031: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3024: END LOOP;
3025:
3026: IF l_debug_on THEN
3027: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3028: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Assign Delivery to Trip API');
3029: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3030: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3031: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3032: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3025:
3026: IF l_debug_on THEN
3027: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3028: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Assign Delivery to Trip API');
3029: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3030: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3031: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3032: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3033: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3026: IF l_debug_on THEN
3027: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3028: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Assign Delivery to Trip API');
3029: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3030: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3031: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3032: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3033: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3034: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3027: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3028: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Assign Delivery to Trip API');
3029: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3030: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3031: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3032: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3033: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3034: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3035: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3028: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Assign Delivery to Trip API');
3029: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3030: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3031: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3032: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3033: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3034: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3035: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3029: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3030: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3031: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3032: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3033: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3034: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3035: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3037: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3030: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3031: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3032: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3033: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3034: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3035: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3037: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3031: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3032: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3033: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3034: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3035: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3037: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3032: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3033: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3034: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3035: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3037: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3033: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3034: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3035: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3037: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3034: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3035: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3037: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Dep Date:'||l_del_action_prms.dropoff_dep_date);
3035: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3037: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Dep Date:'||l_del_action_prms.dropoff_dep_date);
3043: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3037: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Dep Date:'||l_del_action_prms.dropoff_dep_date);
3043: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3044: END IF;
3037: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Dep Date:'||l_del_action_prms.dropoff_dep_date);
3043: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3044: END IF;
3045:
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Dep Date:'||l_del_action_prms.dropoff_dep_date);
3043: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3044: END IF;
3045:
3046: WSH_DELIVERIES_GRP.delivery_action(
3039: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Dep Date:'||l_del_action_prms.dropoff_dep_date);
3043: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3044: END IF;
3045:
3046: WSH_DELIVERIES_GRP.delivery_action(
3047: p_api_version_number => 1.0,
3055: x_msg_count => l_msg_count,
3056: x_msg_data => l_msg_data);
3057:
3058: IF l_debug_on THEN
3059: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Assign Delivery to Trip'||l_rs);
3060: END IF;
3061:
3062: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3063: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3061:
3062: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3063: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3064: IF l_debug_on THEN
3065: WSH_DEBUG_SV.log(l_module_name, 'Assign Delivery to Trip Failed: l_return_status', l_return_status);
3066: END IF;
3067: GOTO trip_error;
3068: ELSIF l_rs = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3069: l_dleg_local_tab(assign_count).processed_flag := 'Y';
3075: END LOOP;--}
3076: END IF;--}
3077:
3078: IF l_debug_on THEN
3079: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries successfully assigned to trip:'||l_dlvy_id_tab.count);
3080: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Delivery Count:'||l_unassigned_delivery_id_tab.count);
3081: END IF;
3082:
3083: -- Make sure the successfully assigned deliveries are removed from
3076: END IF;--}
3077:
3078: IF l_debug_on THEN
3079: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries successfully assigned to trip:'||l_dlvy_id_tab.count);
3080: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Delivery Count:'||l_unassigned_delivery_id_tab.count);
3081: END IF;
3082:
3083: -- Make sure the successfully assigned deliveries are removed from
3084: -- l_unassigned_delivery_id_tab.
3095: END LOOP;
3096: END LOOP;--}
3097:
3098: IF l_debug_on THEN
3099: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Dlvy Index Count:'||l_del_index.count);
3100: END IF;
3101:
3102: -- Actual Deletion from the Unassign Delivery Table
3103: IF l_del_index.count > 0 THEN
3109: END IF;
3110: END IF; --}
3111:
3112: IF l_debug_on THEN
3113: WSH_DEBUG_SV.logmsg(l_module_name,'After compare, Unassign Dlvy Count:'||l_unassigned_delivery_id_tab.count);
3114: WSH_DEBUG_SV.logmsg(l_module_name,'After compare, Unassign Version Count:'||l_unassigned_dlvy_version_tab.count);
3115: END IF;
3116:
3117: END;--} -- process delivery
3110: END IF; --}
3111:
3112: IF l_debug_on THEN
3113: WSH_DEBUG_SV.logmsg(l_module_name,'After compare, Unassign Dlvy Count:'||l_unassigned_delivery_id_tab.count);
3114: WSH_DEBUG_SV.logmsg(l_module_name,'After compare, Unassign Version Count:'||l_unassigned_dlvy_version_tab.count);
3115: END IF;
3116:
3117: END;--} -- process delivery
3118:
3123:
3124: -- 4g. Process Freight Costs for each delivery
3125: -- Still within the loop of l_trip_info_tab, so trip_interface_id is known
3126: IF l_debug_on THEN
3127: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3128: WSH_DEBUG_SV.logmsg(l_module_name,'4g. PROCESS FREIGHT COSTS');
3129: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3130: END IF;
3131:
3124: -- 4g. Process Freight Costs for each delivery
3125: -- Still within the loop of l_trip_info_tab, so trip_interface_id is known
3126: IF l_debug_on THEN
3127: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3128: WSH_DEBUG_SV.logmsg(l_module_name,'4g. PROCESS FREIGHT COSTS');
3129: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3130: END IF;
3131:
3132: DECLARE
3125: -- Still within the loop of l_trip_info_tab, so trip_interface_id is known
3126: IF l_debug_on THEN
3127: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3128: WSH_DEBUG_SV.logmsg(l_module_name,'4g. PROCESS FREIGHT COSTS');
3129: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3130: END IF;
3131:
3132: DECLARE
3133: -- Delivery id in wsh_freight_costs_interface would be populated
3163: INTO l_frcost_type_id;
3164: CLOSE c_get_frcost_type_id;
3165:
3166: IF l_debug_on THEN
3167: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Type Id:'||l_frcost_type_id);
3168: WSH_DEBUG_SV.logmsg(l_module_name, 'Count of Delivery Id:'||l_dlvy_id_tab.count);
3169: END IF;
3170: END IF;
3171:
3164: CLOSE c_get_frcost_type_id;
3165:
3166: IF l_debug_on THEN
3167: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Type Id:'||l_frcost_type_id);
3168: WSH_DEBUG_SV.logmsg(l_module_name, 'Count of Delivery Id:'||l_dlvy_id_tab.count);
3169: END IF;
3170: END IF;
3171:
3172: -- NEED TO DELETE THE EXISTING FREIGHT COST RECORDS FOR THE DELIVERY FOR THIS TYPE
3193: WHERE delivery_id = l_dlvy_id_tab(j)
3194: AND freight_cost_type_id = l_frcost_type_id;
3195:
3196: IF l_debug_on THEN
3197: WSH_DEBUG_SV.logmsg(l_module_name, 'After Deleting Freight Cost Records:');
3198: END IF;
3199:
3200: FOR p in l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
3201: LOOP--{
3200: FOR p in l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
3201: LOOP--{
3202: -- There can be multiple freight cost records for each delivery
3203: IF l_debug_on THEN
3204: WSH_DEBUG_SV.log(l_module_name,'Different Delivery id:',l_dlvy_id_tab(p));
3205: END IF;
3206:
3207: FOR rec in c_freight_int_cur(l_dlvy_id_tab(p))
3208: LOOP--{
3220: l_freight_info_tab(l_count).currency_code := rec.currency_code;
3221: l_freight_info_tab(l_count).delivery_id := rec.delivery_id;
3222:
3223: IF l_debug_on THEN
3224: WSH_DEBUG_SV.log(l_module_name,'Delivery id:',l_freight_info_tab(l_count).delivery_id);
3225: WSH_DEBUG_SV.log(l_module_name,'Unit Amount:',l_freight_info_tab(l_count).Unit_amount);
3226: WSH_DEBUG_SV.log(l_module_name,'Total Amount:',l_freight_info_tab(l_count).Total_amount);
3227: WSH_DEBUG_SV.log(l_module_name,'Currency Code:',l_freight_info_tab(l_count).currency_code);
3228: END IF;
3221: l_freight_info_tab(l_count).delivery_id := rec.delivery_id;
3222:
3223: IF l_debug_on THEN
3224: WSH_DEBUG_SV.log(l_module_name,'Delivery id:',l_freight_info_tab(l_count).delivery_id);
3225: WSH_DEBUG_SV.log(l_module_name,'Unit Amount:',l_freight_info_tab(l_count).Unit_amount);
3226: WSH_DEBUG_SV.log(l_module_name,'Total Amount:',l_freight_info_tab(l_count).Total_amount);
3227: WSH_DEBUG_SV.log(l_module_name,'Currency Code:',l_freight_info_tab(l_count).currency_code);
3228: END IF;
3229:
3222:
3223: IF l_debug_on THEN
3224: WSH_DEBUG_SV.log(l_module_name,'Delivery id:',l_freight_info_tab(l_count).delivery_id);
3225: WSH_DEBUG_SV.log(l_module_name,'Unit Amount:',l_freight_info_tab(l_count).Unit_amount);
3226: WSH_DEBUG_SV.log(l_module_name,'Total Amount:',l_freight_info_tab(l_count).Total_amount);
3227: WSH_DEBUG_SV.log(l_module_name,'Currency Code:',l_freight_info_tab(l_count).currency_code);
3228: END IF;
3229:
3230: -- OTM R12, start of Bug 5952842(5729276)
3223: IF l_debug_on THEN
3224: WSH_DEBUG_SV.log(l_module_name,'Delivery id:',l_freight_info_tab(l_count).delivery_id);
3225: WSH_DEBUG_SV.log(l_module_name,'Unit Amount:',l_freight_info_tab(l_count).Unit_amount);
3226: WSH_DEBUG_SV.log(l_module_name,'Total Amount:',l_freight_info_tab(l_count).Total_amount);
3227: WSH_DEBUG_SV.log(l_module_name,'Currency Code:',l_freight_info_tab(l_count).currency_code);
3228: END IF;
3229:
3230: -- OTM R12, start of Bug 5952842(5729276)
3231: IF l_conversion_type_code IS NULL THEN--{
3235:
3236: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3237: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3238: IF l_debug_on THEN
3239: WSH_DEBUG_SV.log(l_module_name, 'get_currency_conversion_type Failed');
3240: END IF;
3241: GOTO trip_error;
3242: END IF;
3243:
3246: -- OTM R12
3247: l_freight_info_tab(l_count).conversion_type_code := l_conversion_type_code;
3248:
3249: IF l_debug_on THEN
3250: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3251: END IF;
3252: -- OTM R12, end of Bug 5952842(5729276)
3253:
3254: -- OTM R12 Only if freight cost record has currency
3262: OPEN c_get_currency_code(l_trip_info_tab(i).trip_interface_id);
3263: FETCH c_get_currency_code INTO l_carrier_cur_code,l_carrier_id;
3264:
3265: IF l_debug_on THEN
3266: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3267: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier id:'||l_carrier_id);
3268: END IF;
3269:
3270: -- OTM R12 check if carrier has a non-null currency
3263: FETCH c_get_currency_code INTO l_carrier_cur_code,l_carrier_id;
3264:
3265: IF l_debug_on THEN
3266: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3267: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier id:'||l_carrier_id);
3268: END IF;
3269:
3270: -- OTM R12 check if carrier has a non-null currency
3271: IF c_get_currency_code%NOTFOUND THEN
3270: -- OTM R12 check if carrier has a non-null currency
3271: IF c_get_currency_code%NOTFOUND THEN
3272: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3273: IF l_debug_on THEN
3274: WSH_DEBUG_SV.log(l_module_name, 'Carrier not found ');
3275: END IF;
3276: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_ID_NOT_FOUND');
3277: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3278: CLOSE c_get_currency_code;
3283: x_carrier_id => l_carrier_id,
3284: x_return_status => l_rs);
3285:
3286: IF l_debug_on THEN
3287: WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_UTIL_VALIDATE.Validate_Carrier:',l_rs);
3288: END IF;
3289:
3290: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3291: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3289:
3290: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3291: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3292: IF l_debug_on THEN
3293: WSH_DEBUG_SV.log(l_module_name, 'Validate Carrier Failed: l_return_status', l_return_status);
3294: END IF;
3295: CLOSE c_get_currency_code;
3296: GOTO trip_error;
3297: END IF;
3300:
3301: IF l_carrier_cur_code IS NULL THEN
3302: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3303: IF l_debug_on THEN
3304: WSH_DEBUG_SV.log(l_module_name, 'Carrier Currency not defined ');
3305: END IF;
3306: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NO_CURRENCY');
3307: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3308: GOTO trip_error;
3322: x_return_status => l_rs,
3323: x_adjusted_amount => l_freight_info_tab(l_count).unit_amount); -- OTM R12
3324:
3325: IF l_debug_on THEN
3326: WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_UTIL_VALIDATE.Validate_Currency:',l_rs);
3327: END IF;
3328: -- Handle error here !!!
3329: -- ECO 5008405 handle here !!!
3330: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3329: -- ECO 5008405 handle here !!!
3330: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3331: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3332: IF l_debug_on THEN
3333: WSH_DEBUG_SV.log(l_module_name, 'Validate Currency Failed: l_return_status', l_return_status);
3334: END IF;
3335: GOTO trip_error;
3336: ELSE -- OTM R12
3337: IF l_debug_on THEN
3334: END IF;
3335: GOTO trip_error;
3336: ELSE -- OTM R12
3337: IF l_debug_on THEN
3338: WSH_DEBUG_SV.log(l_module_name, 'Validate Currency result: x_adjusted_amount : ', l_freight_info_tab(l_count).unit_amount);
3339: END IF;
3340: END IF;
3341: END IF;--}
3342:
3358: -- OTM R12 Why was 11510 using total_amount here
3359: -- validated unit_amount above
3360:
3361: IF l_debug_on THEN
3362: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling GL_CURRENCY_API.convert_uom:');
3363: WSH_DEBUG_SV.logmsg(l_module_name, 'Currency Code:'||l_freight_info_tab(l_count).currency_code);
3364: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3365: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3366: -- OTM R12 print unit here as total is not being used
3359: -- validated unit_amount above
3360:
3361: IF l_debug_on THEN
3362: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling GL_CURRENCY_API.convert_uom:');
3363: WSH_DEBUG_SV.logmsg(l_module_name, 'Currency Code:'||l_freight_info_tab(l_count).currency_code);
3364: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3365: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3366: -- OTM R12 print unit here as total is not being used
3367: --WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).total_amount);
3360:
3361: IF l_debug_on THEN
3362: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling GL_CURRENCY_API.convert_uom:');
3363: WSH_DEBUG_SV.logmsg(l_module_name, 'Currency Code:'||l_freight_info_tab(l_count).currency_code);
3364: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3365: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3366: -- OTM R12 print unit here as total is not being used
3367: --WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).total_amount);
3368: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).unit_amount);
3361: IF l_debug_on THEN
3362: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling GL_CURRENCY_API.convert_uom:');
3363: WSH_DEBUG_SV.logmsg(l_module_name, 'Currency Code:'||l_freight_info_tab(l_count).currency_code);
3364: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3365: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3366: -- OTM R12 print unit here as total is not being used
3367: --WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).total_amount);
3368: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).unit_amount);
3369: END IF;
3363: WSH_DEBUG_SV.logmsg(l_module_name, 'Currency Code:'||l_freight_info_tab(l_count).currency_code);
3364: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3365: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3366: -- OTM R12 print unit here as total is not being used
3367: --WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).total_amount);
3368: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).unit_amount);
3369: END IF;
3370:
3371: -- Convert API should be called only when the carrier has been validated above
3364: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3365: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3366: -- OTM R12 print unit here as total is not being used
3367: --WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).total_amount);
3368: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).unit_amount);
3369: END IF;
3370:
3371: -- Convert API should be called only when the carrier has been validated above
3372: DECLARE
3421: CLOSE c_get_user_conv_type;
3422: END IF;
3423:
3424: IF l_debug_on THEN
3425: WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred while getting the user currency conversion type');
3426: WSH_DEBUG_SV.log(l_module_name, 'l_currency_conversion_type', l_conversion_type_code);
3427: END IF;
3428: END;
3429: -- end of changes for Bug 5886042
3422: END IF;
3423:
3424: IF l_debug_on THEN
3425: WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred while getting the user currency conversion type');
3426: WSH_DEBUG_SV.log(l_module_name, 'l_currency_conversion_type', l_conversion_type_code);
3427: END IF;
3428: END;
3429: -- end of changes for Bug 5886042
3430:
3434: FND_MESSAGE.SET_TOKEN('CONV_TYPE',l_user_conv_type);-- Bug 5886042
3435: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3436: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3437: IF l_debug_on THEN
3438: WSH_DEBUG_SV.log(l_module_name, 'GL_CURRENCY_API.CONVERT_AMOUNT'
3439: ||' failed no_rate', l_return_status);
3440: END IF;
3441: GOTO trip_error;
3442: WHEN gl_currency_api.invalid_currency THEN
3454: CLOSE c_get_user_conv_type;
3455: END IF;
3456:
3457: IF l_debug_on THEN
3458: WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred while getting the user currency conversion type');
3459: WSH_DEBUG_SV.log(l_module_name, 'l_currency_conversion_type', l_conversion_type_code);
3460: END IF;
3461: END;
3462: -- end of changes for Bug 5886042
3455: END IF;
3456:
3457: IF l_debug_on THEN
3458: WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred while getting the user currency conversion type');
3459: WSH_DEBUG_SV.log(l_module_name, 'l_currency_conversion_type', l_conversion_type_code);
3460: END IF;
3461: END;
3462: -- end of changes for Bug 5886042
3463:
3467: FND_MESSAGE.SET_TOKEN('CONV_TYPE',l_user_conv_type);-- Bug 5886042
3468: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3469: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3470: IF l_debug_on THEN
3471: WSH_DEBUG_SV.log(l_module_name, 'GL_CURRENCY_API.CONVERT_AMOUNT'
3472: ||' failed invalid_currency', l_return_status);
3473: END IF;
3474: GOTO trip_error;
3475:
3476: -- OTM R12 end
3477: WHEN gl_currency_excp THEN
3478: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3479: IF l_debug_on THEN
3480: WSH_DEBUG_SV.log(l_module_name, 'GL_CURRENCY_API.CONVERT_AMOUNT returned NULL :', l_return_status);
3481: END IF;
3482: GOTO trip_error;
3483:
3484: WHEN OTHERS THEN
3483:
3484: WHEN OTHERS THEN
3485: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3486: IF l_debug_on THEN
3487: WSH_DEBUG_SV.log(l_module_name, 'GL_CURRENCY_API.CONVERT_AMOUNT failed:', l_return_status);
3488: END IF;
3489: GOTO trip_error;
3490: END;--}
3491:
3492: l_freight_info_tab(l_count).currency_code := l_carrier_cur_code;
3493: l_freight_info_tab(l_count).unit_amount := l_freight_info_tab(l_count).total_amount;
3494:
3495: IF l_debug_on THEN
3496: WSH_DEBUG_SV.logmsg(l_module_name, 'Converted Amount:'||l_freight_info_tab(l_count).total_amount);
3497: WSH_DEBUG_SV.logmsg(l_module_name, 'New Currency Code:'||l_freight_info_tab(l_count).currency_code);
3498: END IF;
3499:
3500: END IF;--}
3493: l_freight_info_tab(l_count).unit_amount := l_freight_info_tab(l_count).total_amount;
3494:
3495: IF l_debug_on THEN
3496: WSH_DEBUG_SV.logmsg(l_module_name, 'Converted Amount:'||l_freight_info_tab(l_count).total_amount);
3497: WSH_DEBUG_SV.logmsg(l_module_name, 'New Currency Code:'||l_freight_info_tab(l_count).currency_code);
3498: END IF;
3499:
3500: END IF;--}
3501:
3508: l_in_rec.action_code := 'CREATE';
3509: l_in_rec.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
3510:
3511: IF l_debug_on THEN
3512: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_FREIGHT_COSTS_GRP.Create_Update_Freight_Costs');
3513: END IF;
3514:
3515: WSH_FREIGHT_COSTS_GRP.Create_Update_Freight_Costs
3516: (p_api_version_number => 1.0,
3523: x_msg_count => l_msg_count,
3524: x_msg_data => l_msg_data);
3525:
3526: IF l_debug_on THEN
3527: WSH_DEBUG_SV.logmsg(l_module_name, 'Return Status after Create Freight Cost:'||l_rs);
3528: END IF;
3529:
3530: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3531: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3529:
3530: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3531: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3532: IF l_debug_on THEN
3533: WSH_DEBUG_SV.log(l_module_name, 'Create_Update_Freight_Costs Failed: l_return_status', l_return_status);
3534: END IF;
3535: EXIT; -- need to exit out of the parent loop also!!!
3536: -- OTM R12 will never reach this GOTO and GOTO not required as the immediate next section is trip_error
3537: --GOTO trip_error;
3550: END IF;
3551:
3552:
3553: IF l_debug_on THEN
3554: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Info Table Count:'||l_freight_info_tab.count);
3555: END IF;
3556: END; --}
3557:
3558: -- OTM Dock Door App Sched Proj
3585:
3586: BEGIN
3587: FOR crec in c_stops(l_trip_id) LOOP
3588: IF l_debug_on THEN
3589: WSH_DEBUG_SV.log(l_module_name, 'Locking Deliveries and Details for stop '||crec.stop_id);
3590: END IF;
3591:
3592: l_stop_id_rec := crec.stop_id;
3593: l_loading_sequence := l_loading_sequence + 10;
3598: FETCH c_lock_deliveries_and_details BULK COLLECT INTO l_del_tab, l_del_det_tab;
3599:
3600: IF l_del_tab.COUNT > 0 THEN
3601: IF l_debug_on THEN
3602: WSH_DEBUG_SV.log(l_module_name, 'Updating Deliveries and Details With Loading Sequence '||l_loading_sequence);
3603: END IF;
3604:
3605: FORALL i in l_del_tab.FIRST..l_del_tab.LAST
3606: UPDATE wsh_new_deliveries
3629: IF c_lock_deliveries_and_details%ISOPEN then
3630: CLOSE c_lock_deliveries_and_details;
3631: END IF;
3632: IF l_debug_on THEN
3633: WSH_DEBUG_SV.log(l_module_name, 'Unable to lock Deliveries/Details for drop off stop '||l_stop_id_rec);
3634: WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
3635: END IF;
3636: GOTO trip_error;
3637: END;
3630: CLOSE c_lock_deliveries_and_details;
3631: END IF;
3632: IF l_debug_on THEN
3633: WSH_DEBUG_SV.log(l_module_name, 'Unable to lock Deliveries/Details for drop off stop '||l_stop_id_rec);
3634: WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
3635: END IF;
3636: GOTO trip_error;
3637: END;
3638:
3644: l_message VARCHAR2(2000);
3645: BEGIN
3646: IF (l_dock_appt_tab.COUNT > 0) THEN --{
3647: IF l_debug_on THEN
3648: WSH_DEBUG_SV.log(l_module_name, 'Calling wms_dock_appointments_pub.OTM_Dock_Appointment',WSH_DEBUG_SV.C_PROC_LEVEL);
3649: END IF;
3650: -- Calling the WMS API with the Dock Door App Sched Info.
3651: wms_dock_appointments_pub.OTM_Dock_Appointment(p_dock_appt_tab => l_dock_appt_tab,
3652: x_return_status => l_rs,
3652: x_return_status => l_rs,
3653: x_msg_count => l_msg_count,
3654: x_msg_data => l_msg_data);
3655: IF l_debug_on THEN
3656: WSH_DEBUG_SV.log(l_module_name, 'wms_dock_appointments_pub.OTM_Dock_Appointment: Return Status', l_rs);
3657: END IF;
3658: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3659: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3660: FOR l_err_msg IN 1..l_msg_count LOOP
3660: FOR l_err_msg IN 1..l_msg_count LOOP
3661: l_message := fnd_msg_pub.get(l_err_msg,'F');
3662: l_message := replace(l_message,chr(0),' ');
3663: IF l_debug_on THEN
3664: WSH_DEBUG_SV.log(l_module_name, 'ERROR: '|| l_message);
3665: END IF;
3666: END LOOP;
3667: fnd_msg_pub.delete_msg();
3668: FND_MESSAGE.SET_NAME('WSH','WSH_DOCK_SCHED_ERROR');
3673: EXCEPTION
3674: WHEN OTHERS THEN
3675: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3676: IF l_debug_on THEN
3677: WSH_DEBUG_SV.log(l_module_name, 'Error while calling wms_dock_appointments_pub.OTM_Dock_Appointment' || SQLERRM);
3678: END IF;
3679: GOTO trip_error;
3680: END;
3681: --
3684: <
3685:
3686: -- 4j. Final Step : error logging, cleaning up interface table
3687: IF l_debug_on THEN
3688: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3689: WSH_DEBUG_SV.logmsg(l_module_name,'4h. DATA CLEANUP FOR THE TRIP');
3690: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3691: END IF;
3692:
3685:
3686: -- 4j. Final Step : error logging, cleaning up interface table
3687: IF l_debug_on THEN
3688: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3689: WSH_DEBUG_SV.logmsg(l_module_name,'4h. DATA CLEANUP FOR THE TRIP');
3690: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3691: END IF;
3692:
3693: DECLARE
3686: -- 4j. Final Step : error logging, cleaning up interface table
3687: IF l_debug_on THEN
3688: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3689: WSH_DEBUG_SV.logmsg(l_module_name,'4h. DATA CLEANUP FOR THE TRIP');
3690: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3691: END IF;
3692:
3693: DECLARE
3694: l_rs VARCHAR2(1);
3701:
3702: BEGIN--{
3703:
3704: IF l_debug_on THEN
3705: WSH_DEBUG_SV.logmsg(l_module_name,'Status here:'||l_return_status );
3706: END IF;
3707:
3708: -- Based on errors for each trip
3709: -- Status of T is for special case of Invalid Location for Trip Stop
3712: -- Interface tables
3713: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,'T') THEN --{
3714: --IF l_errors_tab.count > 0 THEN
3715: IF l_debug_on THEN
3716: WSH_DEBUG_SV.logmsg(l_module_name,'Rollback.....' );
3717: END IF;
3718:
3719: ROLLBACK TO process_single_trip;
3720:
3723: -- so no need to count and conditionally set error/warning
3724: l_completion_status := 'WARNING';
3725:
3726: IF l_debug_on THEN
3727: WSH_DEBUG_SV.logmsg(l_module_name,'Set Completion to WARNING');
3728: WSH_DEBUG_SV.logmsg(l_module_name,'Before Inserting in Interface error,count:'||l_dleg_local_tab.count );
3729: END IF;
3730:
3731: IF l_return_status = 'T' THEN--{
3724: l_completion_status := 'WARNING';
3725:
3726: IF l_debug_on THEN
3727: WSH_DEBUG_SV.logmsg(l_module_name,'Set Completion to WARNING');
3728: WSH_DEBUG_SV.logmsg(l_module_name,'Before Inserting in Interface error,count:'||l_dleg_local_tab.count );
3729: END IF;
3730:
3731: IF l_return_status = 'T' THEN--{
3732: IF l_debug_on THEN
3729: END IF;
3730:
3731: IF l_return_status = 'T' THEN--{
3732: IF l_debug_on THEN
3733: WSH_DEBUG_SV.logmsg(l_module_name,'Before Purging the Interface Data' );
3734: END IF;
3735:
3736: -- Need to purge the data in interface tables for invalid stop location
3737: -- Also, clean up existing Freight cost and Exceptions in EBS
3742: p_exception_action => 'PURGE',
3743: x_return_status => l_rs);
3744:
3745: IF l_debug_on THEN
3746: WSH_DEBUG_SV.logmsg(l_module_name,'After Purging the Interface Data:'||l_rs );
3747: END IF;
3748:
3749: -- OTM R12
3750: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3749: -- OTM R12
3750: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3751: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3752: IF l_debug_on THEN
3753: WSH_DEBUG_SV.log(l_module_name, 'purge_interface_data Failed');
3754: END IF;
3755: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3756: END IF;
3757: -- OTM R12
3774: x_errors_tab => l_errors_tab,
3775: x_return_status => l_rs);
3776:
3777: IF l_debug_on THEN
3778: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Stamp_Interface_error'||l_rs);
3779: END IF;
3780:
3781: -- OTM R12
3782: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3781: -- OTM R12
3782: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3783: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3784: IF l_debug_on THEN
3785: WSH_DEBUG_SV.log(l_module_name, 'stamp_interface_error Failed');
3786: END IF;
3787: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3788: END IF;
3789: -- OTM R12
3790:
3791: END IF;--} -- l_return_status = E
3792:
3793: IF l_debug_on THEN
3794: WSH_DEBUG_SV.logmsg(l_module_name,'Now Logging Error Exceptions' );
3795: END IF;
3796:
3797: -- Log error level exception for all deliveries, use l_dleg_local_tab
3798: -- to find deliveries which are on this trip
3824: p_exception_name => l_exception_name, -- 'WSH_OTM_SHIPMENT_ERROR',
3825: p_message => substrb(l_exception_message,1,2000),
3826: p_delivery_id => l_dleg_local_tab(rec).delivery_id);
3827: IF l_debug_on THEN
3828: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Log_exception'||l_rs);
3829: END IF;
3830:
3831: -- OTM R12
3832: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3831: -- OTM R12
3832: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3833: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3834: IF l_debug_on THEN
3835: WSH_DEBUG_SV.log(l_module_name, 'WSH_XC_UTIL.log_exception Failed');
3836: END IF;
3837: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3838: END IF;
3839: -- OTM R12
3844:
3845: ELSE -- l_return_status <> 'E' --} --{
3846:
3847: IF l_debug_on THEN
3848: WSH_DEBUG_SV.logmsg(l_module_name,'Success; Now Setting Interface flag to ANSWER RECEIVED' );
3849: END IF;
3850:
3851: -- l_dlvy_id_tab only has successfully processed deliveries
3852: -- l_dlvy_id_tab was used in freight_costs section also, make sure it is
3857: -- to the original value
3858: -- OTM R12 Use update_tms_interface_flag API
3859:
3860: IF l_debug_on THEN
3861: WSH_DEBUG_SV.logmsg(l_module_name,'Before Logging Information Only Exceptions' );
3862: END IF;
3863:
3864: -- All the records in l_dleg_local_tab should have been processed
3865: -- Count should be identical in l_dleg_local_tab and l_dlvy_id_tab
3888: p_action => 'CLOSED'
3889: );
3890:
3891: IF l_debug_on THEN
3892: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after WSH_XC_UTIL.Purge' || l_rs);
3893: END IF;
3894: -- OTM R12
3895: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3896: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3894: -- OTM R12
3895: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3896: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3897: IF l_debug_on THEN
3898: WSH_DEBUG_SV.log(l_module_name, 'WSH_XC_UTIL.Purge Failed');
3899: END IF;
3900: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3901: END IF;
3902:
3916:
3917: END IF;
3918:
3919: IF l_debug_on THEN
3920: WSH_DEBUG_SV.logmsg(l_module_name,'Before Purging the Interface Data' );
3921: END IF;
3922:
3923: -- Need to purge the data in interface tables after successful processing
3924: -- p_exception_action is 'CLOSE' to close specific exceptions logged for the
3930: p_exception_action => 'CLOSE', --'PURGE' is called by Arindam
3931: x_return_status => l_rs);
3932:
3933: IF l_debug_on THEN
3934: WSH_DEBUG_SV.logmsg(l_module_name,'After Purging the Interface Data:'||l_rs );
3935: END IF;
3936:
3937: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3938: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3936:
3937: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3938: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3939: IF l_debug_on THEN
3940: WSH_DEBUG_SV.log(l_module_name, 'purge_interface_data Failed');
3941: END IF;
3942: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3943: END IF;
3944:
3953: --=======================================================================
3954:
3955: -- 5. Final Step : error logging, cleaning up interface table
3956: IF l_debug_on THEN
3957: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3958: WSH_DEBUG_SV.logmsg(l_module_name,'5. UNASSIGN DELIVERIES FROM TRIP');
3959: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3960: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be Unassigned :'||l_unassigned_delivery_id_tab.count);
3961: WSH_DEBUG_SV.logmsg(l_module_name,'Version of Deliveries to be Unassigned :'||l_unassigned_dlvy_version_tab.count);
3954:
3955: -- 5. Final Step : error logging, cleaning up interface table
3956: IF l_debug_on THEN
3957: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3958: WSH_DEBUG_SV.logmsg(l_module_name,'5. UNASSIGN DELIVERIES FROM TRIP');
3959: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3960: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be Unassigned :'||l_unassigned_delivery_id_tab.count);
3961: WSH_DEBUG_SV.logmsg(l_module_name,'Version of Deliveries to be Unassigned :'||l_unassigned_dlvy_version_tab.count);
3962: END IF;
3955: -- 5. Final Step : error logging, cleaning up interface table
3956: IF l_debug_on THEN
3957: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3958: WSH_DEBUG_SV.logmsg(l_module_name,'5. UNASSIGN DELIVERIES FROM TRIP');
3959: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3960: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be Unassigned :'||l_unassigned_delivery_id_tab.count);
3961: WSH_DEBUG_SV.logmsg(l_module_name,'Version of Deliveries to be Unassigned :'||l_unassigned_dlvy_version_tab.count);
3962: END IF;
3963: IF l_unassigned_delivery_id_tab.count > 0 THEN--{
3956: IF l_debug_on THEN
3957: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3958: WSH_DEBUG_SV.logmsg(l_module_name,'5. UNASSIGN DELIVERIES FROM TRIP');
3959: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3960: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be Unassigned :'||l_unassigned_delivery_id_tab.count);
3961: WSH_DEBUG_SV.logmsg(l_module_name,'Version of Deliveries to be Unassigned :'||l_unassigned_dlvy_version_tab.count);
3962: END IF;
3963: IF l_unassigned_delivery_id_tab.count > 0 THEN--{
3964: l_unassigned_del_index := l_unassigned_delivery_id_tab.FIRST;
3957: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3958: WSH_DEBUG_SV.logmsg(l_module_name,'5. UNASSIGN DELIVERIES FROM TRIP');
3959: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3960: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be Unassigned :'||l_unassigned_delivery_id_tab.count);
3961: WSH_DEBUG_SV.logmsg(l_module_name,'Version of Deliveries to be Unassigned :'||l_unassigned_dlvy_version_tab.count);
3962: END IF;
3963: IF l_unassigned_delivery_id_tab.count > 0 THEN--{
3964: l_unassigned_del_index := l_unassigned_delivery_id_tab.FIRST;
3965: WHILE (l_unassigned_del_index IS NOT NULL )
3966: LOOP--{
3967: --glog proj, getting initial value of the delivery(ie. interface_flag)
3968: l_delivery_info_tab.DELETE;
3969: IF l_debug_on THEN
3970: WSH_DEBUG_SV.log(l_module_name, 'l_unassigned_delivery_id_tab('||l_unassigned_del_index||')='||
3971: l_unassigned_delivery_id_tab(l_unassigned_del_index));
3972:
3973: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD',WSH_DEBUG_SV.C_PROC_LEVEL);
3974: END IF;
3969: IF l_debug_on THEN
3970: WSH_DEBUG_SV.log(l_module_name, 'l_unassigned_delivery_id_tab('||l_unassigned_del_index||')='||
3971: l_unassigned_delivery_id_tab(l_unassigned_del_index));
3972:
3973: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD',WSH_DEBUG_SV.C_PROC_LEVEL);
3974: END IF;
3975:
3976: WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD(p_delivery_id => l_unassigned_delivery_id_tab(l_unassigned_del_index),
3977: x_delivery_rec => l_delivery_info,
3980: -- OTM R12
3981: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3982: --l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3983: IF l_debug_on THEN
3984: WSH_DEBUG_SV.log(l_module_name, 'WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD Failed');
3985: END IF;
3986: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3987: END IF;
3988: -- OTM R12
4007:
4008: END LOOP;--}
4009:
4010: IF l_debug_on THEN
4011: WSH_DEBUG_SV.log(l_module_name,'Delivery Info Tab Count:'||l_delivery_info_tab.count);
4012: WSH_DEBUG_SV.log(l_module_name,'New Interface Flag Tab Count:'||l_new_interface_flag_tab.count);
4013: END IF;
4014:
4015: END IF; --}
4008: END LOOP;--}
4009:
4010: IF l_debug_on THEN
4011: WSH_DEBUG_SV.log(l_module_name,'Delivery Info Tab Count:'||l_delivery_info_tab.count);
4012: WSH_DEBUG_SV.log(l_module_name,'New Interface Flag Tab Count:'||l_new_interface_flag_tab.count);
4013: END IF;
4014:
4015: END IF; --}
4016:
4021: p_tms_interface_flag_tab => l_upd_dlvy_tms_tab,
4022: x_return_status => l_return_status);
4023:
4024: IF l_debug_on THEN
4025: WSH_DEBUG_SV.log(l_module_name, 'update_tms_interface_flag : l_return_status : ', l_return_status);
4026: END IF;
4027: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4028: --l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4029: ROLLBACK TO process_group;
4031: --l_completion_status := 'ERROR';
4032: ELSE
4033:
4034: IF l_debug_on THEN
4035: WSH_DEBUG_SV.logmsg(l_module_name,'Now Committing for the group....');
4036: END IF;
4037:
4038: COMMIT;
4039:
4040: END IF;
4041: -- OTM R12
4042:
4043: IF l_debug_on THEN
4044: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4045: WSH_DEBUG_SV.logmsg(l_module_name,'Completion Status'||l_completion_status);
4046: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Error Count'||l_error_trips);
4047: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Success Count'||l_success_trips);
4048: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4041: -- OTM R12
4042:
4043: IF l_debug_on THEN
4044: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4045: WSH_DEBUG_SV.logmsg(l_module_name,'Completion Status'||l_completion_status);
4046: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Error Count'||l_error_trips);
4047: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Success Count'||l_success_trips);
4048: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4049: END IF;
4042:
4043: IF l_debug_on THEN
4044: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4045: WSH_DEBUG_SV.logmsg(l_module_name,'Completion Status'||l_completion_status);
4046: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Error Count'||l_error_trips);
4047: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Success Count'||l_success_trips);
4048: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4049: END IF;
4050:
4043: IF l_debug_on THEN
4044: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4045: WSH_DEBUG_SV.logmsg(l_module_name,'Completion Status'||l_completion_status);
4046: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Error Count'||l_error_trips);
4047: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Success Count'||l_success_trips);
4048: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4049: END IF;
4050:
4051: IF l_completion_status = 'WARNING' THEN
4044: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4045: WSH_DEBUG_SV.logmsg(l_module_name,'Completion Status'||l_completion_status);
4046: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Error Count'||l_error_trips);
4047: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Success Count'||l_success_trips);
4048: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4049: END IF;
4050:
4051: IF l_completion_status = 'WARNING' THEN
4052: errbuf := 'Atleast one trip was not Interfaced';
4065: -- OTM R12
4066: END IF;
4067:
4068: IF l_debug_on THEN
4069: WSH_DEBUG_SV.log(l_module_name,'ERRBUF',errbuf);
4070: WSH_DEBUG_SV.log(l_module_name,'RETCODE',retcode);
4071: WSH_DEBUG_SV.pop(l_module_name);
4072: END IF;
4073:
4066: END IF;
4067:
4068: IF l_debug_on THEN
4069: WSH_DEBUG_SV.log(l_module_name,'ERRBUF',errbuf);
4070: WSH_DEBUG_SV.log(l_module_name,'RETCODE',retcode);
4071: WSH_DEBUG_SV.pop(l_module_name);
4072: END IF;
4073:
4074: EXCEPTION
4067:
4068: IF l_debug_on THEN
4069: WSH_DEBUG_SV.log(l_module_name,'ERRBUF',errbuf);
4070: WSH_DEBUG_SV.log(l_module_name,'RETCODE',retcode);
4071: WSH_DEBUG_SV.pop(l_module_name);
4072: END IF;
4073:
4074: EXCEPTION
4075: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4075: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4076: ROLLBACK TO process_group;
4077: --
4078: IF l_debug_on THEN
4079: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4080: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4081: END IF;
4082: --
4083: -- Always concurrent request,no online option
4076: ROLLBACK TO process_group;
4077: --
4078: IF l_debug_on THEN
4079: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4080: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4081: END IF;
4082: --
4083: -- Always concurrent request,no online option
4084: l_ret_code := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
4158: WSH_UTIL_CORE.DEFAULT_HANDLER(
4159: 'WSH_TMS_RELEASE.RELEASE_PLANNED_SHIPMENT',
4160: l_module_name);
4161: IF l_debug_on THEN
4162: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4163: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4164: END IF;
4165: END release_planned_shipment;
4166:
4159: 'WSH_TMS_RELEASE.RELEASE_PLANNED_SHIPMENT',
4160: l_module_name);
4161: IF l_debug_on THEN
4162: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4163: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4164: END IF;
4165: END release_planned_shipment;
4166:
4167: --=========================================================================
4214:
4215: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4216: --
4217: IF l_debug_on IS NULL THEN
4218: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4219: END IF;
4220:
4221: IF l_debug_on THEN
4222: WSH_DEBUG_SV.push(l_module_name);
4218: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4219: END IF;
4220:
4221: IF l_debug_on THEN
4222: WSH_DEBUG_SV.push(l_module_name);
4223: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4224: WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', p_trip_interface_id);
4225: WSH_DEBUG_SV.log(l_module_name,'p_commit_flag:', p_commit_flag);
4226: WSH_DEBUG_SV.log(l_module_name,'p_exception_action:', p_exception_action);
4219: END IF;
4220:
4221: IF l_debug_on THEN
4222: WSH_DEBUG_SV.push(l_module_name);
4223: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4224: WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', p_trip_interface_id);
4225: WSH_DEBUG_SV.log(l_module_name,'p_commit_flag:', p_commit_flag);
4226: WSH_DEBUG_SV.log(l_module_name,'p_exception_action:', p_exception_action);
4227: END IF;
4220:
4221: IF l_debug_on THEN
4222: WSH_DEBUG_SV.push(l_module_name);
4223: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4224: WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', p_trip_interface_id);
4225: WSH_DEBUG_SV.log(l_module_name,'p_commit_flag:', p_commit_flag);
4226: WSH_DEBUG_SV.log(l_module_name,'p_exception_action:', p_exception_action);
4227: END IF;
4228:
4221: IF l_debug_on THEN
4222: WSH_DEBUG_SV.push(l_module_name);
4223: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4224: WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', p_trip_interface_id);
4225: WSH_DEBUG_SV.log(l_module_name,'p_commit_flag:', p_commit_flag);
4226: WSH_DEBUG_SV.log(l_module_name,'p_exception_action:', p_exception_action);
4227: END IF;
4228:
4229: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4222: WSH_DEBUG_SV.push(l_module_name);
4223: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4224: WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', p_trip_interface_id);
4225: WSH_DEBUG_SV.log(l_module_name,'p_commit_flag:', p_commit_flag);
4226: WSH_DEBUG_SV.log(l_module_name,'p_exception_action:', p_exception_action);
4227: END IF;
4228:
4229: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4230:
4240: l_trip_interface_id_tab(1) := p_trip_interface_id;
4241: END IF;
4242:
4243: IF l_debug_on THEN
4244: WSH_DEBUG_SV.log(l_module_name,'Count of Trip Interface Ids:', l_trip_interface_id_tab.count);
4245: END IF;
4246:
4247: IF l_debug_on THEN
4248: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Freight Costs Interface Data');
4244: WSH_DEBUG_SV.log(l_module_name,'Count of Trip Interface Ids:', l_trip_interface_id_tab.count);
4245: END IF;
4246:
4247: IF l_debug_on THEN
4248: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Freight Costs Interface Data');
4249: END IF;
4250:
4251: IF l_trip_interface_id_tab.count > 0 THEN--{
4252:
4250:
4251: IF l_trip_interface_id_tab.count > 0 THEN--{
4252:
4253: IF l_debug_on THEN
4254: WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', l_trip_interface_id_tab(l_trip_interface_id_tab.FIRST));
4255: END IF;
4256:
4257: -- Purge wsh_freight_cost_interface
4258: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4270:
4271: IF p_exception_action = 'PURGE' THEN--{
4272: -- Purge exceptions logged for the deliveries
4273: IF l_debug_on THEN
4274: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Exceptions');
4275: END IF;
4276: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4277: DELETE from WSH_EXCEPTIONS we
4278: WHERE we.delivery_id in (
4288:
4289: ELSIF p_exception_action = 'CLOSE' THEN
4290: -- Close exceptions logged for the deliveries
4291: IF l_debug_on THEN
4292: WSH_DEBUG_SV.logmsg(l_module_name,'Start Closing Exceptions');
4293: END IF;
4294: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4295: UPDATE WSH_EXCEPTIONS we
4296: SET status = 'CLOSED'
4311: -- table_name can be WSH_NEW_DEL_INTERFACE, WSH_TRIP_STOPS_INTERFACE,
4312: -- WSH_TRIPS_INTERFACE
4313:
4314: IF l_debug_on THEN
4315: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Interface Errors Data for delivery');
4316: END IF;
4317: -- Delete errors logged for deliveries
4318: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4319: Delete from wsh_interface_errors wie
4328: and wdli.interface_action_code = G_TMS_RELEASE_CODE)
4329: and wie.interface_action_code = G_TMS_RELEASE_CODE;
4330:
4331: IF l_debug_on THEN
4332: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Interface Errors Data for Trip Stops');
4333: END IF;
4334: -- Delete errors logged for trip stops
4335: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4336: Delete from wsh_interface_errors wie
4343: and wie.interface_action_code = G_TMS_RELEASE_CODE;
4344:
4345: -- Delete errors logged for trips
4346: IF l_debug_on THEN
4347: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Interface Errors Data for Trips');
4348: END IF;
4349: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4350: Delete from wsh_interface_errors wie
4351: where wie.interface_table_name = 'WSH_TRIPS_INTERFACE'
4353: and wie.interface_action_code = G_TMS_RELEASE_CODE;
4354:
4355: -- Purge wsh_new_del_interface
4356: IF l_debug_on THEN
4357: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Deliveries Interface Data');
4358: END IF;
4359: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4360: Delete from wsh_new_del_interface wndi
4361: where wndi.delivery_interface_id in (
4369: and wndi.interface_action_code = G_TMS_RELEASE_CODE;
4370:
4371: -- Purge wsh_del_legs_interface
4372: IF l_debug_on THEN
4373: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Delivery Legs Interface Data');
4374: END IF;
4375: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4376: Delete from wsh_del_legs_interface wdli
4377: where wdli.pick_up_stop_interface_id in (
4382: and wdli.interface_action_code = G_TMS_RELEASE_CODE;
4383:
4384: -- Purge wsh_trip_stops_interface
4385: IF l_debug_on THEN
4386: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Trip Stops Interface Data');
4387: END IF;
4388: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4389: Delete from wsh_trip_stops_interface wtsi
4390: where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
4393: -- Purge wsh_trips_interface
4394: -- (Trips have to be the last entity interface table purged
4395: -- because this is the only entity interface table having TP_PLAN_NAME.)
4396: IF l_debug_on THEN
4397: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Trips Interface Data');
4398: END IF;
4399: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4400: Delete from wsh_trips_interface wti
4401: where wti.trip_interface_id = l_trip_interface_id_tab(i)
4404: END IF;--}
4405:
4406: IF p_commit_flag = 'Y' THEN
4407: IF l_debug_on THEN
4408: WSH_DEBUG_SV.logmsg(l_module_name,'Committing....');
4409: END IF;
4410: commit;
4411: END IF;
4412:
4410: commit;
4411: END IF;
4412:
4413: IF l_debug_on THEN
4414: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4415: WSH_DEBUG_SV.pop(l_module_name);
4416: END IF;
4417:
4418: EXCEPTION
4411: END IF;
4412:
4413: IF l_debug_on THEN
4414: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4415: WSH_DEBUG_SV.pop(l_module_name);
4416: END IF;
4417:
4418: EXCEPTION
4419: WHEN OTHERS THEN
4425: WSH_UTIL_CORE.DEFAULT_HANDLER(
4426: 'WSH_TMS_RELEASE.PURGE_INTERFACE_DATA',
4427: l_module_name);
4428: IF l_debug_on THEN
4429: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4430: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4431: END IF;
4432:
4433: END purge_interface_data;
4426: 'WSH_TMS_RELEASE.PURGE_INTERFACE_DATA',
4427: l_module_name);
4428: IF l_debug_on THEN
4429: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4430: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4431: END IF;
4432:
4433: END purge_interface_data;
4434:
4508:
4509: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4510: --
4511: IF l_debug_on IS NULL THEN
4512: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4513: END IF;
4514:
4515: IF l_debug_on THEN
4516: WSH_DEBUG_SV.push(l_module_name);
4512: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4513: END IF;
4514:
4515: IF l_debug_on THEN
4516: WSH_DEBUG_SV.push(l_module_name);
4517: WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4518: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4519: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4520: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4513: END IF;
4514:
4515: IF l_debug_on THEN
4516: WSH_DEBUG_SV.push(l_module_name);
4517: WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4518: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4519: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4520: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4521: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4514:
4515: IF l_debug_on THEN
4516: WSH_DEBUG_SV.push(l_module_name);
4517: WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4518: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4519: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4520: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4521: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4522: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4515: IF l_debug_on THEN
4516: WSH_DEBUG_SV.push(l_module_name);
4517: WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4518: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4519: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4520: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4521: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4522: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4523: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4516: WSH_DEBUG_SV.push(l_module_name);
4517: WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4518: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4519: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4520: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4521: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4522: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4523: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4524: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4517: WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4518: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4519: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4520: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4521: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4522: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4523: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4524: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4525: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4518: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4519: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4520: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4521: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4522: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4523: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4524: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4525: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4526: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4519: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4520: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4521: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4522: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4523: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4524: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4525: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4526: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4527: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4520: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4521: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4522: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4523: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4524: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4525: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4526: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4527: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4528: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4521: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4522: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4523: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4524: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4525: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4526: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4527: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4528: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4529: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4522: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4523: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4524: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4525: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4526: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4527: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4528: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4529: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4523: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4524: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4525: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4526: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4527: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4528: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4529: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4531: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Input Deliveries:'||p_dleg_tab.count);
4524: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4525: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4526: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4527: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4528: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4529: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4531: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Input Deliveries:'||p_dleg_tab.count);
4532: END IF;
4525: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4526: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4527: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4528: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4529: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4531: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Input Deliveries:'||p_dleg_tab.count);
4532: END IF;
4533:
4526: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4527: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4528: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4529: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4531: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Input Deliveries:'||p_dleg_tab.count);
4532: END IF;
4533:
4534: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4527: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4528: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4529: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4531: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Input Deliveries:'||p_dleg_tab.count);
4532: END IF;
4533:
4534: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4535:
4571: END IF; --}
4572:
4573: c := FND_MSG_PUB.count_msg;
4574: IF l_debug_on THEN
4575: WSH_DEBUG_SV.log(l_module_name,'COUNT--',c);
4576: END IF;
4577:
4578: FOR i in 1..c LOOP--{
4579: --FOR i in REVERSE(c)..1 LOOP--{
4578: FOR i in 1..c LOOP--{
4579: --FOR i in REVERSE(c)..1 LOOP--{
4580:
4581: IF l_debug_on THEN
4582: WSH_DEBUG_SV.logmsg(l_module_name,'C:'||i);
4583: END IF;
4584:
4585: FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
4586: p_msg_index => i,
4588: p_msg_index_out => l_index_out);
4589:
4590: -- Concatenate the message
4591: IF l_debug_on THEN
4592: WSH_DEBUG_SV.logmsg(l_module_name,'l_buffer:'||l_buffer);
4593: END IF;
4594: l_message := l_message||l_buffer;
4595:
4596: -- l_index := l_index + 1;
4600:
4601: l_message_name := 'WSH_OTM_GENERIC';
4602:
4603: IF l_debug_on THEN
4604: WSH_DEBUG_SV.log(l_module_name,'l_index--',l_index);
4605: WSH_DEBUG_SV.logmsg(l_module_name,'l_message:'||substr(l_message,1,200));
4606: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,201,200));
4607: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,401,200));
4608: END IF;
4601: l_message_name := 'WSH_OTM_GENERIC';
4602:
4603: IF l_debug_on THEN
4604: WSH_DEBUG_SV.log(l_module_name,'l_index--',l_index);
4605: WSH_DEBUG_SV.logmsg(l_module_name,'l_message:'||substr(l_message,1,200));
4606: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,201,200));
4607: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,401,200));
4608: END IF;
4609:
4602:
4603: IF l_debug_on THEN
4604: WSH_DEBUG_SV.log(l_module_name,'l_index--',l_index);
4605: WSH_DEBUG_SV.logmsg(l_module_name,'l_message:'||substr(l_message,1,200));
4606: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,201,200));
4607: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,401,200));
4608: END IF;
4609:
4610:
4603: IF l_debug_on THEN
4604: WSH_DEBUG_SV.log(l_module_name,'l_index--',l_index);
4605: WSH_DEBUG_SV.logmsg(l_module_name,'l_message:'||substr(l_message,1,200));
4606: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,201,200));
4607: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,401,200));
4608: END IF;
4609:
4610:
4611: -- Populate the Delivery_interface_id
4645:
4646: END IF;--}
4647:
4648: IF l_debug_on THEN
4649: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4650: WSH_DEBUG_SV.pop(l_module_name);
4651: END IF;
4652:
4653: EXCEPTION
4646: END IF;--}
4647:
4648: IF l_debug_on THEN
4649: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4650: WSH_DEBUG_SV.pop(l_module_name);
4651: END IF;
4652:
4653: EXCEPTION
4654: WHEN OTHERS THEN
4656: WSH_UTIL_CORE.DEFAULT_HANDLER(
4657: 'WSH_TMS_RELEASE.STAMP_INTERFACE_ERROR',
4658: l_module_name);
4659: IF l_debug_on THEN
4660: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4661: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4662: END IF;
4663: END stamp_interface_error;
4664:
4657: 'WSH_TMS_RELEASE.STAMP_INTERFACE_ERROR',
4658: l_module_name);
4659: IF l_debug_on THEN
4660: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4661: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4662: END IF;
4663: END stamp_interface_error;
4664:
4665: --====================================================
4718:
4719: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4720: --
4721: IF l_debug_on IS NULL THEN
4722: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4723: END IF;
4724:
4725: IF l_debug_on THEN
4726: WSH_DEBUG_SV.push(l_module_name);
4722: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4723: END IF;
4724:
4725: IF l_debug_on THEN
4726: WSH_DEBUG_SV.push(l_module_name);
4727: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4728: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4729: END IF;
4730:
4723: END IF;
4724:
4725: IF l_debug_on THEN
4726: WSH_DEBUG_SV.push(l_module_name);
4727: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4728: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4729: END IF;
4730:
4731: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4724:
4725: IF l_debug_on THEN
4726: WSH_DEBUG_SV.push(l_module_name);
4727: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4728: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4729: END IF;
4730:
4731: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4732:
4754: END LOOP;
4755: END IF;--}
4756:
4757: IF l_debug_on THEN
4758: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_id_tab.count', x_delivery_id_tab.count);
4759: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_tab.count', x_delivery_tab.count);
4760: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4761: WSH_DEBUG_SV.pop(l_module_name);
4762: END IF;
4755: END IF;--}
4756:
4757: IF l_debug_on THEN
4758: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_id_tab.count', x_delivery_id_tab.count);
4759: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_tab.count', x_delivery_tab.count);
4760: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4761: WSH_DEBUG_SV.pop(l_module_name);
4762: END IF;
4763:
4756:
4757: IF l_debug_on THEN
4758: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_id_tab.count', x_delivery_id_tab.count);
4759: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_tab.count', x_delivery_tab.count);
4760: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4761: WSH_DEBUG_SV.pop(l_module_name);
4762: END IF;
4763:
4764: EXCEPTION
4757: IF l_debug_on THEN
4758: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_id_tab.count', x_delivery_id_tab.count);
4759: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_tab.count', x_delivery_tab.count);
4760: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4761: WSH_DEBUG_SV.pop(l_module_name);
4762: END IF;
4763:
4764: EXCEPTION
4765: WHEN OTHERS THEN
4773: WSH_UTIL_CORE.DEFAULT_HANDLER(
4774: 'WSH_TMS_RELEASE.FIND_DELIVERIES_FOR_TRIP',
4775: l_module_name);
4776: IF l_debug_on THEN
4777: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4778: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4779: END IF;
4780: END find_deliveries_for_trip;
4781:
4774: 'WSH_TMS_RELEASE.FIND_DELIVERIES_FOR_TRIP',
4775: l_module_name);
4776: IF l_debug_on THEN
4777: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4778: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4779: END IF;
4780: END find_deliveries_for_trip;
4781:
4782: --====================================================
4833:
4834: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4835: --
4836: IF l_debug_on IS NULL THEN
4837: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4838: END IF;
4839:
4840: IF l_debug_on THEN
4841: WSH_DEBUG_SV.push(l_module_name);
4837: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4838: END IF;
4839:
4840: IF l_debug_on THEN
4841: WSH_DEBUG_SV.push(l_module_name);
4842: WSH_DEBUG_SV.log(l_module_name,'Input Delivery Count:', p_dleg_tab.count);
4843: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4844: WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Count:', x_unassign_id_tab.count);
4845: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4838: END IF;
4839:
4840: IF l_debug_on THEN
4841: WSH_DEBUG_SV.push(l_module_name);
4842: WSH_DEBUG_SV.log(l_module_name,'Input Delivery Count:', p_dleg_tab.count);
4843: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4844: WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Count:', x_unassign_id_tab.count);
4845: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4846: END IF;
4839:
4840: IF l_debug_on THEN
4841: WSH_DEBUG_SV.push(l_module_name);
4842: WSH_DEBUG_SV.log(l_module_name,'Input Delivery Count:', p_dleg_tab.count);
4843: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4844: WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Count:', x_unassign_id_tab.count);
4845: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4846: END IF;
4847:
4840: IF l_debug_on THEN
4841: WSH_DEBUG_SV.push(l_module_name);
4842: WSH_DEBUG_SV.log(l_module_name,'Input Delivery Count:', p_dleg_tab.count);
4843: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4844: WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Count:', x_unassign_id_tab.count);
4845: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4846: END IF;
4847:
4848: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4841: WSH_DEBUG_SV.push(l_module_name);
4842: WSH_DEBUG_SV.log(l_module_name,'Input Delivery Count:', p_dleg_tab.count);
4843: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4844: WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Count:', x_unassign_id_tab.count);
4845: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4846: END IF;
4847:
4848: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4849:
4876: ) THEN--{
4877:
4878:
4879: IF l_debug_on THEN
4880: WSH_DEBUG_SV.logmsg(l_module_name, 'Need to UNASSIGN DELIVERY');
4881: END IF;
4882:
4883: -- Cannot do BULK UNASSIGNMENT HERE, as there can be multiple trips and the
4884: -- deliveries could be associated with different trips
4888: l_del_attrs(l_del_attrs.count + 1).delivery_id := p_dleg_tab(dlvy_count).delivery_id;
4889: l_del_attrs(l_del_attrs.count).organization_id := p_dleg_tab(dlvy_count).organization_id;
4890:
4891: IF l_debug_on THEN
4892: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
4893: END IF;
4894:
4895: l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
4896: l_del_action_prms.action_code := 'UNASSIGN-TRIP';
4908: x_msg_count => l_msg_count,
4909: x_msg_data => l_msg_data);
4910:
4911: IF l_debug_on THEN
4912: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Unassign Delivery'||l_rs);
4913: END IF;
4914: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4915: x_return_status := l_rs;
4916: EXIT;
4929: dleg_rec.dropoff_stop_sequence = p_dleg_tab(dlvy_count).dropoff_stop_sequence)
4930: ) THEN
4931:
4932: IF l_debug_on THEN
4933: WSH_DEBUG_SV.logmsg(l_module_name, 'DELIVERY IS ALREADY ASSIGNED TO TRIP');
4934: END IF;
4935: p_dleg_tab(dlvy_count).processed_flag := 'Y';
4936: END IF;--}
4937: END LOOP;--}
4938:
4939: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4940: x_return_status := l_rs;
4941: IF l_debug_on THEN
4942: WSH_DEBUG_SV.logmsg(l_module_name, 'Error Processing Delivery id:'||(p_dleg_tab(dlvy_count).delivery_id));
4943: END IF;
4944: EXIT;
4945: END IF;
4946: END LOOP;--}
4946: END LOOP;--}
4947: END IF;
4948:
4949: IF l_debug_on THEN
4950: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery Count:', x_unassign_id_tab.count);
4951: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4952: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4953: WSH_DEBUG_SV.pop(l_module_name);
4954: END IF;
4947: END IF;
4948:
4949: IF l_debug_on THEN
4950: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery Count:', x_unassign_id_tab.count);
4951: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4952: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4953: WSH_DEBUG_SV.pop(l_module_name);
4954: END IF;
4955:
4948:
4949: IF l_debug_on THEN
4950: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery Count:', x_unassign_id_tab.count);
4951: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4952: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4953: WSH_DEBUG_SV.pop(l_module_name);
4954: END IF;
4955:
4956: EXCEPTION
4949: IF l_debug_on THEN
4950: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery Count:', x_unassign_id_tab.count);
4951: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4952: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4953: WSH_DEBUG_SV.pop(l_module_name);
4954: END IF;
4955:
4956: EXCEPTION
4957: WHEN OTHERS THEN
4962: WSH_UTIL_CORE.DEFAULT_HANDLER(
4963: 'WSH_TMS_RELEASE.COMPARE_TRIP_FOR_DELIVERIES',
4964: l_module_name);
4965: IF l_debug_on THEN
4966: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4967: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4968: END IF;
4969:
4970: END compare_trip_for_deliveries;
4963: 'WSH_TMS_RELEASE.COMPARE_TRIP_FOR_DELIVERIES',
4964: l_module_name);
4965: IF l_debug_on THEN
4966: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4967: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4968: END IF;
4969:
4970: END compare_trip_for_deliveries;
4971:
4993:
4994: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4995: --
4996: IF l_debug_on IS NULL THEN
4997: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4998: END IF;
4999:
5000: IF l_debug_on THEN
5001: WSH_DEBUG_SV.push(l_module_name);
4997: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4998: END IF;
4999:
5000: IF l_debug_on THEN
5001: WSH_DEBUG_SV.push(l_module_name);
5002: WSH_DEBUG_SV.log(l_module_name,'Group Id:', p_group_id);
5003: END IF;
5004:
5005: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4998: END IF;
4999:
5000: IF l_debug_on THEN
5001: WSH_DEBUG_SV.push(l_module_name);
5002: WSH_DEBUG_SV.log(l_module_name,'Group Id:', p_group_id);
5003: END IF;
5004:
5005: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5006:
5010: argument1 => p_group_id);
5011: -- COMMIT;
5012:
5013: IF l_debug_on THEN
5014: WSH_DEBUG_SV.log(l_module_name, 'Request id:', x_request_id);
5015: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
5016: WSH_DEBUG_SV.pop(l_module_name);
5017: END IF;
5018:
5011: -- COMMIT;
5012:
5013: IF l_debug_on THEN
5014: WSH_DEBUG_SV.log(l_module_name, 'Request id:', x_request_id);
5015: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
5016: WSH_DEBUG_SV.pop(l_module_name);
5017: END IF;
5018:
5019: EXCEPTION
5012:
5013: IF l_debug_on THEN
5014: WSH_DEBUG_SV.log(l_module_name, 'Request id:', x_request_id);
5015: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
5016: WSH_DEBUG_SV.pop(l_module_name);
5017: END IF;
5018:
5019: EXCEPTION
5020: WHEN OTHERS THEN
5023: WSH_UTIL_CORE.DEFAULT_HANDLER(
5024: 'WSH_TMS_RELEASE.LAUNCH_RELEASE_REQUEST',
5025: l_module_name);
5026: IF l_debug_on THEN
5027: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5028: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5029: END IF;
5030:
5031: END launch_release_request;
5024: 'WSH_TMS_RELEASE.LAUNCH_RELEASE_REQUEST',
5025: l_module_name);
5026: IF l_debug_on THEN
5027: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5028: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5029: END IF;
5030:
5031: END launch_release_request;
5032:
5081:
5082: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5083: --
5084: IF l_debug_on IS NULL THEN
5085: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5086: END IF;
5087:
5088: IF l_debug_on THEN
5089: WSH_DEBUG_SV.push(l_module_name);
5085: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5086: END IF;
5087:
5088: IF l_debug_on THEN
5089: WSH_DEBUG_SV.push(l_module_name);
5090: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5091: WSH_DEBUG_SV.log(l_module_name,'Stop Tab Count:', x_stop_tab.count);
5092: END IF;
5093:
5086: END IF;
5087:
5088: IF l_debug_on THEN
5089: WSH_DEBUG_SV.push(l_module_name);
5090: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5091: WSH_DEBUG_SV.log(l_module_name,'Stop Tab Count:', x_stop_tab.count);
5092: END IF;
5093:
5094: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5087:
5088: IF l_debug_on THEN
5089: WSH_DEBUG_SV.push(l_module_name);
5090: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5091: WSH_DEBUG_SV.log(l_module_name,'Stop Tab Count:', x_stop_tab.count);
5092: END IF;
5093:
5094: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5095:
5099: FOR i IN l_stop_tab.FIRST..l_stop_tab.LAST
5100: LOOP--{
5101:
5102: IF l_debug_on THEN
5103: WSH_DEBUG_SV.logmsg(l_module_name, '=====================================');
5104: WSH_DEBUG_SV.logmsg(l_module_name, 'Stop Location:'||l_stop_tab(i).stop_location_id);
5105: END IF;
5106:
5107: IF x_delivery_tab.count = 1 THEN -- Single Delivery in the GC3 Trip--{
5100: LOOP--{
5101:
5102: IF l_debug_on THEN
5103: WSH_DEBUG_SV.logmsg(l_module_name, '=====================================');
5104: WSH_DEBUG_SV.logmsg(l_module_name, 'Stop Location:'||l_stop_tab(i).stop_location_id);
5105: END IF;
5106:
5107: IF x_delivery_tab.count = 1 THEN -- Single Delivery in the GC3 Trip--{
5108: -- Match Stop Location to Delivery Dropoff Location
5110: IF l_dropoff_location_id = to_number(l_stop_tab(i).stop_location_id) THEN--{
5111: -- no action required as Dropoff Location matches incoming stop
5112: --null;
5113: IF l_debug_on THEN
5114: WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Location Matches Stop Location');
5115: END IF;
5116: ELSIF l_dropoff_location_id <> to_number(l_stop_tab(i).stop_location_id) THEN--} --{
5117:
5118: IF l_debug_on THEN
5115: END IF;
5116: ELSIF l_dropoff_location_id <> to_number(l_stop_tab(i).stop_location_id) THEN--} --{
5117:
5118: IF l_debug_on THEN
5119: WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Location Does not match Stop Location');
5120: END IF;
5121:
5122: -- get the physical location corresponding to delivery
5123: -- dropoff location and match with Stop Location
5130: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
5131: THEN
5132: x_return_status := l_return_status;
5133: IF l_debug_on THEN
5134: WSH_DEBUG_SV.log(l_module_name, 'Conversion Failed,l_return_status', l_return_status);
5135: END IF;
5136: EXIT; -- exit out of the stop_tab loop
5137: END IF;
5138:
5136: EXIT; -- exit out of the stop_tab loop
5137: END IF;
5138:
5139: IF l_debug_on THEN
5140: WSH_DEBUG_SV.log(l_module_name, 'Delivery Physical Location Id:'||l_physical_loc_id);
5141: WSH_DEBUG_SV.log(l_module_name, 'Stop Location Id:'||l_stop_tab(i).stop_location_id);
5142: END IF;
5143:
5144: IF l_physical_loc_id IS NOT NULL AND
5137: END IF;
5138:
5139: IF l_debug_on THEN
5140: WSH_DEBUG_SV.log(l_module_name, 'Delivery Physical Location Id:'||l_physical_loc_id);
5141: WSH_DEBUG_SV.log(l_module_name, 'Stop Location Id:'||l_stop_tab(i).stop_location_id);
5142: END IF;
5143:
5144: IF l_physical_loc_id IS NOT NULL AND
5145: to_number(l_stop_tab(i).stop_location_id) = l_physical_loc_id THEN
5143:
5144: IF l_physical_loc_id IS NOT NULL AND
5145: to_number(l_stop_tab(i).stop_location_id) = l_physical_loc_id THEN
5146: IF l_debug_on THEN
5147: WSH_DEBUG_SV.log(l_module_name, 'Delivery Dropoff Dummy Location Matches Incoming Stop Location');
5148: END IF;
5149: -- As there is only 1 delivery involved, create the stop based on the
5150: -- dummy location. Delivery assignment is done based on this location.
5151: -- x_stop_tab matches l_stop_tab, same index
5167: l_dropoff_location_id := to_number(x_delivery_tab(j).ultimate_dropoff_location_id);
5168: IF l_dropoff_location_id = to_number(l_stop_tab(i).stop_location_id) THEN--{
5169: -- no action required as Dropoff Location matches incoming stop
5170: IF l_debug_on THEN
5171: WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Location Matches Stop Location for delivery:'||x_delivery_tab(j).delivery_id);
5172: END IF;
5173:
5174: ELSIF l_dropoff_location_id <> to_number(l_stop_tab(i).stop_location_id) THEN--} --{
5175:
5173:
5174: ELSIF l_dropoff_location_id <> to_number(l_stop_tab(i).stop_location_id) THEN--} --{
5175:
5176: IF l_debug_on THEN
5177: WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Location Does not match Stop Location for delivery:'||x_delivery_tab(j).delivery_id);
5178: END IF;
5179:
5180: -- get the physical location corresponding to delivery
5181: -- dropoff location and match with Stop Location
5188: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
5189: THEN
5190: x_return_status := l_return_status;
5191: IF l_debug_on THEN
5192: WSH_DEBUG_SV.log(l_module_name, 'Conversion Failed,l_return_status', l_return_status);
5193: END IF;
5194: EXIT; -- exit out of the stop_tab loop
5195: END IF;
5196:
5194: EXIT; -- exit out of the stop_tab loop
5195: END IF;
5196:
5197: IF l_debug_on THEN
5198: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery Physical Location Id:'||l_physical_loc_id);
5199: WSH_DEBUG_SV.logmsg(l_module_name, 'Stop Location Id:'||l_stop_tab(i).stop_location_id);
5200: END IF;
5201:
5202: -- The physical location for delivery dropoff location matches the incoming stop
5195: END IF;
5196:
5197: IF l_debug_on THEN
5198: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery Physical Location Id:'||l_physical_loc_id);
5199: WSH_DEBUG_SV.logmsg(l_module_name, 'Stop Location Id:'||l_stop_tab(i).stop_location_id);
5200: END IF;
5201:
5202: -- The physical location for delivery dropoff location matches the incoming stop
5203: -- location.
5203: -- location.
5204: IF l_physical_loc_id IS NOT NULL AND
5205: to_number(l_stop_tab(i).stop_location_id) = l_physical_loc_id THEN--{
5206: IF l_debug_on THEN
5207: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery Dropoff Dummy Location Matches Incoming Stop Location');
5208: END IF;
5209:
5210: l_update_stop := 'Y';
5211: -- As there are multiple deliveries, need to evaluate the pickup of those
5221: -- Cannot convert the Incoming Stop location to correspond to the dummy
5222: -- Need to create a new stop for the Dummy Location
5223: l_update_stop := 'N';
5224: IF l_debug_on THEN
5225: WSH_DEBUG_SV.logmsg(l_module_name,'Atleast one delivery uses this location as pickup, delivery_id:'||l_dlvy_tab(k).delivery_id);
5226: END IF;
5227: EXIT; -- out of this loop for delivery, once a match is found
5228: END IF;
5229: END LOOP;
5228: END IF;
5229: END LOOP;
5230:
5231: IF l_debug_on THEN
5232: WSH_DEBUG_SV.logmsg(l_module_name, 'L_UPDATE_STOP FLAG:'||l_update_stop);
5233: END IF;
5234:
5235: IF l_update_stop = 'Y' THEN--{
5236: -- Update the Stop Location for current stop to match delivery location
5276:
5277: END LOOP;--} -- across stop_tab Loop
5278:
5279: IF l_debug_on THEN
5280: WSH_DEBUG_SV.log(l_module_name,'New Dummy Stop Tab Count:', l_new_stop_tab.count);
5281: WSH_DEBUG_SV.log(l_module_name,'Input Stop Tab Count:', x_stop_tab.count);
5282: END IF;
5283:
5284: IF l_new_stop_tab.count > 0 THEN--{
5277: END LOOP;--} -- across stop_tab Loop
5278:
5279: IF l_debug_on THEN
5280: WSH_DEBUG_SV.log(l_module_name,'New Dummy Stop Tab Count:', l_new_stop_tab.count);
5281: WSH_DEBUG_SV.log(l_module_name,'Input Stop Tab Count:', x_stop_tab.count);
5282: END IF;
5283:
5284: IF l_new_stop_tab.count > 0 THEN--{
5285: FOR new_stop in l_new_stop_tab.FIRST..l_new_stop_tab.LAST
5293: END LOOP;
5294: END IF;--}
5295:
5296: IF l_debug_on THEN
5297: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5298: WSH_DEBUG_SV.log(l_module_name,'Output Stop Tab Count:', x_stop_tab.count);
5299: WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
5300: WSH_DEBUG_SV.pop(l_module_name);
5301: END IF;
5294: END IF;--}
5295:
5296: IF l_debug_on THEN
5297: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5298: WSH_DEBUG_SV.log(l_module_name,'Output Stop Tab Count:', x_stop_tab.count);
5299: WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
5300: WSH_DEBUG_SV.pop(l_module_name);
5301: END IF;
5302:
5295:
5296: IF l_debug_on THEN
5297: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5298: WSH_DEBUG_SV.log(l_module_name,'Output Stop Tab Count:', x_stop_tab.count);
5299: WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
5300: WSH_DEBUG_SV.pop(l_module_name);
5301: END IF;
5302:
5303: EXCEPTION
5296: IF l_debug_on THEN
5297: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5298: WSH_DEBUG_SV.log(l_module_name,'Output Stop Tab Count:', x_stop_tab.count);
5299: WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
5300: WSH_DEBUG_SV.pop(l_module_name);
5301: END IF;
5302:
5303: EXCEPTION
5304: WHEN OTHERS THEN
5306: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5307: WSH_UTIL_CORE.DEFAULT_HANDLER(
5308: 'WSH_TMS_RELEASE.PROCESS_INTERNAL_LOCATIONS',l_module_name);
5309: IF l_debug_on THEN
5310: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5311: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5312: END IF;
5313:
5314: END process_internal_locations;
5307: WSH_UTIL_CORE.DEFAULT_HANDLER(
5308: 'WSH_TMS_RELEASE.PROCESS_INTERNAL_LOCATIONS',l_module_name);
5309: IF l_debug_on THEN
5310: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5311: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5312: END IF;
5313:
5314: END process_internal_locations;
5315:
5334: l_source_tz_id NUMBER;
5335:
5336: invalid_timezone EXCEPTION;
5337:
5338: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
5339: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_server_time';
5340:
5341: BEGIN
5342: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5341: BEGIN
5342: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5343:
5344: IF l_debug_on THEN
5345: WSH_DEBUG_SV.push (l_module_name);
5346: WSH_DEBUG_SV.logmsg(l_module_name,'p_source_timezone_code : '||p_source_timezone_code);
5347: WSH_DEBUG_SV.logmsg(l_module_name,'p_source_time : '||to_char(p_source_time,'DD-MON-RRRR HH24:MI:SS'));
5348: END IF;
5349:
5342: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5343:
5344: IF l_debug_on THEN
5345: WSH_DEBUG_SV.push (l_module_name);
5346: WSH_DEBUG_SV.logmsg(l_module_name,'p_source_timezone_code : '||p_source_timezone_code);
5347: WSH_DEBUG_SV.logmsg(l_module_name,'p_source_time : '||to_char(p_source_time,'DD-MON-RRRR HH24:MI:SS'));
5348: END IF;
5349:
5350: IF p_source_timezone_code IS NULL THEN
5343:
5344: IF l_debug_on THEN
5345: WSH_DEBUG_SV.push (l_module_name);
5346: WSH_DEBUG_SV.logmsg(l_module_name,'p_source_timezone_code : '||p_source_timezone_code);
5347: WSH_DEBUG_SV.logmsg(l_module_name,'p_source_time : '||to_char(p_source_time,'DD-MON-RRRR HH24:MI:SS'));
5348: END IF;
5349:
5350: IF p_source_timezone_code IS NULL THEN
5351: RAISE invalid_timezone;
5360:
5361: l_server_tz_id := FND_PROFILE.value('SERVER_TIMEZONE_ID');
5362:
5363: IF l_debug_on THEN
5364: WSH_DEBUG_SV.logmsg(l_module_name,'Server tz id :'||l_server_tz_id);
5365: END IF;
5366:
5367: HZ_TIMEZONE_PUB.Get_Time(
5368: p_api_version => 1.0,
5375: x_msg_count => x_msg_count,
5376: x_msg_data => x_msg_data);
5377:
5378: IF l_debug_on THEN
5379: WSH_DEBUG_SV.logmsg(l_module_name,'Returning time : '|| x_server_time);
5380: WSH_DEBUG_SV.logmsg(l_module_name,'Returning x_return_status : '|| x_return_status);
5381: WSH_DEBUG_SV.pop(l_module_name);
5382: END IF;
5383:
5376: x_msg_data => x_msg_data);
5377:
5378: IF l_debug_on THEN
5379: WSH_DEBUG_SV.logmsg(l_module_name,'Returning time : '|| x_server_time);
5380: WSH_DEBUG_SV.logmsg(l_module_name,'Returning x_return_status : '|| x_return_status);
5381: WSH_DEBUG_SV.pop(l_module_name);
5382: END IF;
5383:
5384: EXCEPTION
5377:
5378: IF l_debug_on THEN
5379: WSH_DEBUG_SV.logmsg(l_module_name,'Returning time : '|| x_server_time);
5380: WSH_DEBUG_SV.logmsg(l_module_name,'Returning x_return_status : '|| x_return_status);
5381: WSH_DEBUG_SV.pop(l_module_name);
5382: END IF;
5383:
5384: EXCEPTION
5385: WHEN invalid_timezone THEN
5388: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5389: x_msg_data := 'Input Timezone is not a valid FND timezone code';
5390: --
5391: IF l_debug_on THEN
5392: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_timezone');
5393: END IF;
5394: --
5395:
5396: WHEN others THEN
5398: x_msg_data := 'Oracle error message is '|| SQLERRM;
5399: WSH_UTIL_CORE.default_handler('WSH_OTM_INBOUND_GRP.get_server_time');
5400: --
5401: IF l_debug_on THEN
5402: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5403: WSH_DEBUG_SV.logmsg(l_module_name,'-------------- END ----------------');
5404: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
5405: END IF;
5406: --
5399: WSH_UTIL_CORE.default_handler('WSH_OTM_INBOUND_GRP.get_server_time');
5400: --
5401: IF l_debug_on THEN
5402: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5403: WSH_DEBUG_SV.logmsg(l_module_name,'-------------- END ----------------');
5404: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
5405: END IF;
5406: --
5407: END get_server_time;
5400: --
5401: IF l_debug_on THEN
5402: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5403: WSH_DEBUG_SV.logmsg(l_module_name,'-------------- END ----------------');
5404: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
5405: END IF;
5406: --
5407: END get_server_time;
5408: