[Home] [Help]
1090:
1091: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1092: --
1093: IF l_debug_on IS NULL THEN
1094: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1095: END IF;
1096:
1097: IF l_debug_on THEN
1098: WSH_DEBUG_SV.push(l_module_name);
1094: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1095: END IF;
1096:
1097: IF l_debug_on THEN
1098: WSH_DEBUG_SV.push(l_module_name);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1102: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1095: END IF;
1096:
1097: IF l_debug_on THEN
1098: WSH_DEBUG_SV.push(l_module_name);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1102: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1103: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1096:
1097: IF l_debug_on THEN
1098: WSH_DEBUG_SV.push(l_module_name);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1102: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1103: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1104: WSH_DEBUG_SV.log(l_module_name, 'p_client_id', p_client_id); -- Modified R12.1.1 LSP PROJECT
1097: IF l_debug_on THEN
1098: WSH_DEBUG_SV.push(l_module_name);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1102: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1103: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1104: WSH_DEBUG_SV.log(l_module_name, 'p_client_id', p_client_id); -- Modified R12.1.1 LSP PROJECT
1105: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1098: WSH_DEBUG_SV.push(l_module_name);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1102: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1103: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1104: WSH_DEBUG_SV.log(l_module_name, 'p_client_id', p_client_id); -- Modified R12.1.1 LSP PROJECT
1105: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1106: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1099: WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1102: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1103: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1104: WSH_DEBUG_SV.log(l_module_name, 'p_client_id', p_client_id); -- Modified R12.1.1 LSP PROJECT
1105: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1106: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1107: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1100: WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1101: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1102: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1103: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1104: WSH_DEBUG_SV.log(l_module_name, 'p_client_id', p_client_id); -- Modified R12.1.1 LSP PROJECT
1105: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1106: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1107: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1108: 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_tp_plan_low', p_tp_plan_low);
1102: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1103: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1104: WSH_DEBUG_SV.log(l_module_name, 'p_client_id', p_client_id); -- Modified R12.1.1 LSP PROJECT
1105: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1106: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1107: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1108: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1109: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1102: WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1103: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1104: WSH_DEBUG_SV.log(l_module_name, 'p_client_id', p_client_id); -- Modified R12.1.1 LSP PROJECT
1105: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1106: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1107: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1108: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1109: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1110: WSH_DEBUG_SV.logmsg(l_module_name, 'START PROCESSING...');
1103: WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1104: WSH_DEBUG_SV.log(l_module_name, 'p_client_id', p_client_id); -- Modified R12.1.1 LSP PROJECT
1105: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1106: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1107: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1108: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1109: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1110: WSH_DEBUG_SV.logmsg(l_module_name, 'START PROCESSING...');
1111: END IF;
1104: WSH_DEBUG_SV.log(l_module_name, 'p_client_id', p_client_id); -- Modified R12.1.1 LSP PROJECT
1105: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1106: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1107: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1108: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1109: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1110: WSH_DEBUG_SV.logmsg(l_module_name, 'START PROCESSING...');
1111: END IF;
1112: --Bug#7491598(ER,defer planned shipment iface)
1105: WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1106: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1107: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1108: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1109: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1110: WSH_DEBUG_SV.logmsg(l_module_name, 'START PROCESSING...');
1111: END IF;
1112: --Bug#7491598(ER,defer planned shipment iface)
1113: IF p_group_id IS NULL THEN
1106: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1107: WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1108: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1109: WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1110: WSH_DEBUG_SV.logmsg(l_module_name, 'START PROCESSING...');
1111: END IF;
1112: --Bug#7491598(ER,defer planned shipment iface)
1113: IF p_group_id IS NULL THEN
1114: --{
1130: --} group id is null
1131: END IF;
1132:
1133: IF l_debug_on THEN
1134: WSH_DEBUG_SV.log(l_module_name, 'l_del_pickup_date_low', l_del_pickup_date_low);
1135: WSH_DEBUG_SV.log(l_module_name, 'l_del_pickup_date_high', l_del_pickup_date_high);
1136: END IF;
1137:
1138:
1131: END IF;
1132:
1133: IF l_debug_on THEN
1134: WSH_DEBUG_SV.log(l_module_name, 'l_del_pickup_date_low', l_del_pickup_date_low);
1135: WSH_DEBUG_SV.log(l_module_name, 'l_del_pickup_date_high', l_del_pickup_date_high);
1136: END IF;
1137:
1138:
1139: -- --Bug#7491598(ER,defer planned shipment iface): Group Id check needs to be removed.
1145: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1146: errbuf := 'Exception occurred in Release_Planned_Shipment';
1147: retcode := '2';
1148: IF l_debug_on THEN
1149: WSH_DEBUG_SV.logmsg(l_module_name, 'Group Id cannot be Null');
1150: WSH_DEBUG_SV.pop(l_module_name);
1151: END IF;
1152: RETURN;
1153: END IF;
1146: errbuf := 'Exception occurred in Release_Planned_Shipment';
1147: retcode := '2';
1148: IF l_debug_on THEN
1149: WSH_DEBUG_SV.logmsg(l_module_name, 'Group Id cannot be Null');
1150: WSH_DEBUG_SV.pop(l_module_name);
1151: END IF;
1152: RETURN;
1153: END IF;
1154: */
1176: --=======================================================================
1177: -- 1. Get All Trips Information
1178: -- Loop to find trips for the group_id
1179: IF l_debug_on THEN
1180: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1181: WSH_DEBUG_SV.logmsg(l_module_name,'1. Gather Trips Interface data');
1182: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1183: END IF;
1184: -- Bug#7491598(ER,defer planned shipment iface)
1177: -- 1. Get All Trips Information
1178: -- Loop to find trips for the group_id
1179: IF l_debug_on THEN
1180: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1181: WSH_DEBUG_SV.logmsg(l_module_name,'1. Gather Trips Interface data');
1182: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1183: END IF;
1184: -- Bug#7491598(ER,defer planned shipment iface)
1185: IF (P_GROUP_ID is not null) THEN
1178: -- Loop to find trips for the group_id
1179: IF l_debug_on THEN
1180: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1181: WSH_DEBUG_SV.logmsg(l_module_name,'1. Gather Trips Interface data');
1182: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1183: END IF;
1184: -- Bug#7491598(ER,defer planned shipment iface)
1185: IF (P_GROUP_ID is not null) THEN
1186: OPEN c_tms_interface_trips;
1214: --l_trip_index := l_trip_info_rec.trip_interface_id;
1215: --l_trip_index := l_trip_index + 1;
1216: l_trip_index := l_trip_info_tab.count + 1;
1217: IF l_debug_on THEN
1218: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_trip_info_rec.trip_interface_id);
1219: END IF;
1220: --l_trip_info_tab(l_trip_info_tab.count + 1) := l_trip_info_rec;
1221: l_trip_info_tab(l_trip_index) := l_trip_info_rec;
1222:
1235: CLOSE c_tms_interface_trips_all;
1236: END IF;
1237: -- Bug#7491598(ER,defer planned shipment iface): end
1238: IF l_debug_on THEN
1239: WSH_DEBUG_SV.log(l_module_name,'Count Trip Info Tab:', l_trip_info_tab.count);
1240: END IF;
1241:
1242: --=======================================================================
1243: -- 2. Get All Trip Stops Information(order by trip ids)
1242: --=======================================================================
1243: -- 2. Get All Trip Stops Information(order by trip ids)
1244: -- Loop to find trip stops for the group_id
1245: IF l_debug_on THEN
1246: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1247: WSH_DEBUG_SV.logmsg(l_module_name,'2. Gather Trip Stops Interface data');
1248: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1249: END IF;
1250: -- --Bug#7491598(ER,defer planned shipment iface): Begin
1243: -- 2. Get All Trip Stops Information(order by trip ids)
1244: -- Loop to find trip stops for the group_id
1245: IF l_debug_on THEN
1246: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1247: WSH_DEBUG_SV.logmsg(l_module_name,'2. Gather Trip Stops Interface data');
1248: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1249: END IF;
1250: -- --Bug#7491598(ER,defer planned shipment iface): Begin
1251: IF (P_GROUP_ID is not null) THEN
1244: -- Loop to find trip stops for the group_id
1245: IF l_debug_on THEN
1246: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1247: WSH_DEBUG_SV.logmsg(l_module_name,'2. Gather Trip Stops Interface data');
1248: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1249: END IF;
1250: -- --Bug#7491598(ER,defer planned shipment iface): Begin
1251: IF (P_GROUP_ID is not null) THEN
1252: OPEN c_tms_interface_stops;
1276: INTO l_stop_info_rec;
1277: EXIT WHEN c_tms_interface_stops%NOTFOUND; */
1278: --l_stop_index := l_stop_index + 1;
1279: IF l_debug_on THEN
1280: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_stop_info_rec.trip_interface_id);
1281: WSH_DEBUG_SV.log(l_module_name,'Stop Interface id', l_stop_info_rec.stop_interface_id);
1282: WSH_DEBUG_SV.log(l_module_name,'Stop Location id', l_stop_info_rec.stop_location_id);
1283: WSH_DEBUG_SV.log(l_module_name,'Stop Sequence No.', l_stop_info_rec.stop_sequence_number);
1284: WSH_DEBUG_SV.log(l_module_name,'Timezone Code', l_stop_info_rec.timezone_code);
1277: EXIT WHEN c_tms_interface_stops%NOTFOUND; */
1278: --l_stop_index := l_stop_index + 1;
1279: IF l_debug_on THEN
1280: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_stop_info_rec.trip_interface_id);
1281: WSH_DEBUG_SV.log(l_module_name,'Stop Interface id', l_stop_info_rec.stop_interface_id);
1282: WSH_DEBUG_SV.log(l_module_name,'Stop Location id', l_stop_info_rec.stop_location_id);
1283: WSH_DEBUG_SV.log(l_module_name,'Stop Sequence No.', l_stop_info_rec.stop_sequence_number);
1284: WSH_DEBUG_SV.log(l_module_name,'Timezone Code', l_stop_info_rec.timezone_code);
1285: END IF;
1278: --l_stop_index := l_stop_index + 1;
1279: IF l_debug_on THEN
1280: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_stop_info_rec.trip_interface_id);
1281: WSH_DEBUG_SV.log(l_module_name,'Stop Interface id', l_stop_info_rec.stop_interface_id);
1282: WSH_DEBUG_SV.log(l_module_name,'Stop Location id', l_stop_info_rec.stop_location_id);
1283: WSH_DEBUG_SV.log(l_module_name,'Stop Sequence No.', l_stop_info_rec.stop_sequence_number);
1284: WSH_DEBUG_SV.log(l_module_name,'Timezone Code', l_stop_info_rec.timezone_code);
1285: END IF;
1286: l_stop_info_tab(l_stop_info_tab.count + 1) := l_stop_info_rec;
1279: IF l_debug_on THEN
1280: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_stop_info_rec.trip_interface_id);
1281: WSH_DEBUG_SV.log(l_module_name,'Stop Interface id', l_stop_info_rec.stop_interface_id);
1282: WSH_DEBUG_SV.log(l_module_name,'Stop Location id', l_stop_info_rec.stop_location_id);
1283: WSH_DEBUG_SV.log(l_module_name,'Stop Sequence No.', l_stop_info_rec.stop_sequence_number);
1284: WSH_DEBUG_SV.log(l_module_name,'Timezone Code', l_stop_info_rec.timezone_code);
1285: END IF;
1286: l_stop_info_tab(l_stop_info_tab.count + 1) := l_stop_info_rec;
1287:
1280: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_stop_info_rec.trip_interface_id);
1281: WSH_DEBUG_SV.log(l_module_name,'Stop Interface id', l_stop_info_rec.stop_interface_id);
1282: WSH_DEBUG_SV.log(l_module_name,'Stop Location id', l_stop_info_rec.stop_location_id);
1283: WSH_DEBUG_SV.log(l_module_name,'Stop Sequence No.', l_stop_info_rec.stop_sequence_number);
1284: WSH_DEBUG_SV.log(l_module_name,'Timezone Code', l_stop_info_rec.timezone_code);
1285: END IF;
1286: l_stop_info_tab(l_stop_info_tab.count + 1) := l_stop_info_rec;
1287:
1288: END LOOP;--}
1296: CLOSE c_tms_interface_stops_all;
1297: END IF;
1298:
1299: IF l_debug_on THEN
1300: WSH_DEBUG_SV.log(l_module_name,'Count Trip Stop Info Tab:', l_stop_info_tab.count);
1301: END IF;
1302:
1303: --=======================================================================
1304: -- 3. For each trip stop, get Delivery Leg information(order by trip ids)
1303: --=======================================================================
1304: -- 3. For each trip stop, get Delivery Leg information(order by trip ids)
1305: -- Loop to find delivery legs for the group_id
1306: IF l_debug_on THEN
1307: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1308: WSH_DEBUG_SV.logmsg(l_module_name,'3. Gather Delivery Legs Interface data');
1309: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1310: END IF;
1311: --Bug#7491598(ER,defer planned shipment iface): Begin
1304: -- 3. For each trip stop, get Delivery Leg information(order by trip ids)
1305: -- Loop to find delivery legs for the group_id
1306: IF l_debug_on THEN
1307: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1308: WSH_DEBUG_SV.logmsg(l_module_name,'3. Gather Delivery Legs Interface data');
1309: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1310: END IF;
1311: --Bug#7491598(ER,defer planned shipment iface): Begin
1312: IF (P_GROUP_ID is not null) THEN
1305: -- Loop to find delivery legs for the group_id
1306: IF l_debug_on THEN
1307: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1308: WSH_DEBUG_SV.logmsg(l_module_name,'3. Gather Delivery Legs Interface data');
1309: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1310: END IF;
1311: --Bug#7491598(ER,defer planned shipment iface): Begin
1312: IF (P_GROUP_ID is not null) THEN
1313: OPEN c_tms_interface_dlegs;
1339: EXIT WHEN c_tms_interface_dlegs%NOTFOUND; */
1340: l_dleg_index := l_dleg_info_rec.trip_interface_id;
1341: --l_dleg_index := l_dleg_index + 1;
1342: IF l_debug_on THEN
1343: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_dleg_info_rec.trip_interface_id);
1344: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1345: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1346: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1347: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1340: l_dleg_index := l_dleg_info_rec.trip_interface_id;
1341: --l_dleg_index := l_dleg_index + 1;
1342: IF l_debug_on THEN
1343: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_dleg_info_rec.trip_interface_id);
1344: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1345: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1346: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1347: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1348: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1341: --l_dleg_index := l_dleg_index + 1;
1342: IF l_debug_on THEN
1343: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_dleg_info_rec.trip_interface_id);
1344: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1345: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1346: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1347: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1348: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1349: WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1342: IF l_debug_on THEN
1343: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_dleg_info_rec.trip_interface_id);
1344: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1345: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1346: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1347: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1348: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1349: WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1350: WSH_DEBUG_SV.log(l_module_name,'OTM Version Number', l_dleg_info_rec.otm_tms_version_number);
1343: WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_dleg_info_rec.trip_interface_id);
1344: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1345: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1346: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1347: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1348: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1349: WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1350: WSH_DEBUG_SV.log(l_module_name,'OTM Version Number', l_dleg_info_rec.otm_tms_version_number);
1351: END IF;
1344: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1345: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1346: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1347: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1348: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1349: WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1350: WSH_DEBUG_SV.log(l_module_name,'OTM Version Number', l_dleg_info_rec.otm_tms_version_number);
1351: END IF;
1352:
1345: WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1346: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1347: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1348: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1349: WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1350: WSH_DEBUG_SV.log(l_module_name,'OTM Version Number', l_dleg_info_rec.otm_tms_version_number);
1351: END IF;
1352:
1353: l_dleg_info_tab(l_dleg_info_tab.count + 1) := l_dleg_info_rec;
1346: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1347: WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1348: WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1349: WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1350: WSH_DEBUG_SV.log(l_module_name,'OTM Version Number', l_dleg_info_rec.otm_tms_version_number);
1351: END IF;
1352:
1353: l_dleg_info_tab(l_dleg_info_tab.count + 1) := l_dleg_info_rec;
1354: END LOOP;--}
1363: CLOSE c_tms_interface_dlegs_all;
1364: END IF;
1365: --Bug#7491598(ER,defer planned shipment iface): end
1366: IF l_debug_on THEN
1367: WSH_DEBUG_SV.log(l_module_name,'Count Delivery Leg Info Tab:', l_dleg_info_tab.count);
1368: END IF;
1369:
1370: -- OTM R12 Clean up for all trips in a bulk delete outside the loop
1371:
1369:
1370: -- OTM R12 Clean up for all trips in a bulk delete outside the loop
1371:
1372: IF l_debug_on THEN
1373: WSH_DEBUG_SV.logmsg(l_module_name,'CLEAN UP EXISTING DATA FOR ALL TRIPS');
1374: END IF;
1375:
1376: -- Delete Records in Interface errors table and exceptions table
1377: -- for the associated entities. This will ensure there are no duplicate
1428: SAVEPOINT process_group;
1429: --=======================================================================
1430: -- 4. Loop thru all trips in l_trip_info_tab
1431: IF l_debug_on THEN
1432: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1433: WSH_DEBUG_SV.logmsg(l_module_name,'4. START LOOPING THROUGH EACH TRIP');
1434: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1435: END IF;
1436:
1429: --=======================================================================
1430: -- 4. Loop thru all trips in l_trip_info_tab
1431: IF l_debug_on THEN
1432: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1433: WSH_DEBUG_SV.logmsg(l_module_name,'4. START LOOPING THROUGH EACH TRIP');
1434: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1435: END IF;
1436:
1437: -- Trip count should be > 0
1430: -- 4. Loop thru all trips in l_trip_info_tab
1431: IF l_debug_on THEN
1432: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1433: WSH_DEBUG_SV.logmsg(l_module_name,'4. START LOOPING THROUGH EACH TRIP');
1434: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1435: END IF;
1436:
1437: -- Trip count should be > 0
1438: IF l_trip_info_tab.count > 0 THEN--{
1446:
1447: SAVEPOINT process_single_trip;
1448:
1449: IF l_debug_on THEN
1450: WSH_DEBUG_SV.logmsg(l_module_name,'Populate Delivery Legs Info in Local Table');
1451: END IF;
1452:
1453: -- Loop through l_dleg_info_tab to find the deliveries for current trip
1454: IF l_dleg_info_tab.COUNT > 0 THEN
1466: -- core APIs
1467: IF l_trip_info_tab(i).trip_id IS NOT NULL THEN--{
1468:
1469: IF l_debug_on THEN
1470: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1471: WSH_DEBUG_SV.logmsg(l_module_name,'4ai. LOCK TRIP:-'||l_trip_info_tab(i).trip_id);
1472: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1473: END IF;
1474:
1467: IF l_trip_info_tab(i).trip_id IS NOT NULL THEN--{
1468:
1469: IF l_debug_on THEN
1470: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1471: WSH_DEBUG_SV.logmsg(l_module_name,'4ai. LOCK TRIP:-'||l_trip_info_tab(i).trip_id);
1472: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1473: END IF;
1474:
1475: BEGIN
1468:
1469: IF l_debug_on THEN
1470: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1471: WSH_DEBUG_SV.logmsg(l_module_name,'4ai. LOCK TRIP:-'||l_trip_info_tab(i).trip_id);
1472: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1473: END IF;
1474:
1475: BEGIN
1476: -- Lock Trip
1482: FND_MESSAGE.SET_TOKEN('TRIP_NAME',WSH_TRIPS_PVT.get_name(l_trip_info_tab(i).trip_id));
1483: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1484: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1485: IF l_debug_on THEN
1486: WSH_DEBUG_SV.log(l_module_name, 'trip not locked: l_return_status', l_return_status);
1487: END IF;
1488: GOTO trip_error;
1489: END;
1490:
1490:
1491: -- 4a. Lock corresponding trip stops in EBS(for trip_id)
1492: -- Trip has to exist in EBS for locking this stops
1493: IF l_debug_on THEN
1494: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1495: WSH_DEBUG_SV.logmsg(l_module_name,'4aii. LOCK TRIP STOP');
1496: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1497: END IF;
1498: DECLARE
1491: -- 4a. Lock corresponding trip stops in EBS(for trip_id)
1492: -- Trip has to exist in EBS for locking this stops
1493: IF l_debug_on THEN
1494: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1495: WSH_DEBUG_SV.logmsg(l_module_name,'4aii. LOCK TRIP STOP');
1496: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1497: END IF;
1498: DECLARE
1499: l_stop_id NUMBER;
1492: -- Trip has to exist in EBS for locking this stops
1493: IF l_debug_on THEN
1494: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1495: WSH_DEBUG_SV.logmsg(l_module_name,'4aii. LOCK TRIP STOP');
1496: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1497: END IF;
1498: DECLARE
1499: l_stop_id NUMBER;
1500: BEGIN--{
1511: FND_MESSAGE.SET_TOKEN('STOP_NAME',WSH_TRIP_STOPS_PVT.get_name(l_stop_id,'FTE_TMS_INTEGRATION'));
1512: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1513: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1514: IF l_debug_on THEN
1515: WSH_DEBUG_SV.log(l_module_name, 'trip stop not locked: l_return_status', l_return_status);
1516: END IF;
1517: GOTO trip_error;
1518: END;--}
1519:
1521:
1522: -- 4b. Locking of Delivery and Delivery legs is required even when there is no trip in EBS
1523:
1524: IF l_debug_on THEN
1525: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1526: WSH_DEBUG_SV.logmsg(l_module_name,'4b. LOCK DELIVERY AND DELIVERY LEG');
1527: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1528: END IF;
1529: IF l_dleg_local_tab.count > 0 THEN
1522: -- 4b. Locking of Delivery and Delivery legs is required even when there is no trip in EBS
1523:
1524: IF l_debug_on THEN
1525: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1526: WSH_DEBUG_SV.logmsg(l_module_name,'4b. LOCK DELIVERY AND DELIVERY LEG');
1527: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1528: END IF;
1529: IF l_dleg_local_tab.count > 0 THEN
1530: FOR m in l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
1523:
1524: IF l_debug_on THEN
1525: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1526: WSH_DEBUG_SV.logmsg(l_module_name,'4b. LOCK DELIVERY AND DELIVERY LEG');
1527: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1528: END IF;
1529: IF l_dleg_local_tab.count > 0 THEN
1530: FOR m in l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
1531: -- Lock corresponding delivery legs in EBS
1543: FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',wsh_new_deliveries_pvt.get_name(l_dleg_local_tab(m).delivery_id));
1544: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1545: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1546: IF l_debug_on THEN
1547: WSH_DEBUG_SV.log(l_module_name, 'Delivery Leg not locked: l_return_status', l_return_status);
1548: END IF;
1549: GOTO trip_error;
1550: END;
1551:
1560: FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',wsh_new_deliveries_pvt.get_name(l_dleg_local_tab(m).delivery_id));
1561: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1562: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1563: IF l_debug_on THEN
1564: WSH_DEBUG_SV.log(l_module_name, 'Delivery not locked: l_return_status', l_return_status);
1565: END IF;
1566: GOTO trip_error;
1567: END;
1568: END IF;--}
1570: END IF;
1571: -- 4c. Process Trip
1572: -- Vehicle Derivation
1573: IF l_debug_on THEN
1574: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1575: WSH_DEBUG_SV.logmsg(l_module_name,'4c. PROCESS TRIP');
1576: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1577: END IF;
1578: DECLARE
1571: -- 4c. Process Trip
1572: -- Vehicle Derivation
1573: IF l_debug_on THEN
1574: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1575: WSH_DEBUG_SV.logmsg(l_module_name,'4c. PROCESS TRIP');
1576: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1577: END IF;
1578: DECLARE
1579:
1572: -- Vehicle Derivation
1573: IF l_debug_on THEN
1574: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1575: WSH_DEBUG_SV.logmsg(l_module_name,'4c. PROCESS TRIP');
1576: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1577: END IF;
1578: DECLARE
1579:
1580: l_rs VARCHAR2(1);
1591: BEGIN--{
1592:
1593: -- try to lock the trip interface records
1594: IF l_debug_on THEN
1595: WSH_DEBUG_SV.logmsg(l_module_name,'Locking the trip interface record:'||l_trip_info_tab(i).trip_interface_id);
1596: END IF;
1597: OPEN c_lock_trip_interface(l_trip_info_tab(i).trip_interface_id) ;
1598: FETCH c_lock_trip_interface INTO l_lock_rec;
1599: CLOSE c_lock_trip_interface;
1602: -- Derive the Vehicle Item Id based on the name
1603: IF l_trip_info_tab(i).vehicle_item_name IS NOT NULL
1604: AND l_trip_info_tab(i).interface_action_code = G_TMS_RELEASE_CODE THEN--{
1605: IF l_debug_on THEN
1606: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item Name:'||l_trip_info_tab(i).vehicle_item_name);
1607: END IF;
1608: -- Get the first stop location from l_stop_info_tab
1609: -- l_stop_info would be populated
1610: FOR i_vehicle in l_stop_info_tab.FIRST..l_stop_info_tab.LAST
1625: l_return_status := 'T';
1626: FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
1627: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1628: IF l_debug_on THEN
1629: WSH_DEBUG_SV.log(l_module_name, 'Invalid Stop Location, l_return_status:', l_return_status);
1630: END IF;
1631: GOTO trip_error;
1632: END;--}
1633:
1640: l_return_status := 'T';
1641: FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
1642: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1643: IF l_debug_on THEN
1644: WSH_DEBUG_SV.log(l_module_name, 'Unable to Derive Stop Location', l_return_status);
1645: END IF;
1646: GOTO trip_error;
1647: END IF;
1648:
1646: GOTO trip_error;
1647: END IF;
1648:
1649: IF l_debug_on THEN
1650: WSH_DEBUG_SV.logmsg(l_module_name,'Calling wsh_util_core.get_org_from_location:'||l_stop_location_id);
1651: END IF;
1652: --get_org_from_location
1653: WSH_UTIL_CORE.get_org_from_location(
1654: p_location_id => l_stop_location_id,
1655: x_organization_tab => l_organization_tab,
1656: x_return_status => l_rs);
1657:
1658: IF l_debug_on THEN
1659: WSH_DEBUG_SV.logmsg(l_module_name,'return status:'||l_rs);
1660: WSH_DEBUG_SV.logmsg(l_module_name,'Organizations Count:'||l_organization_tab.Count);
1661: END IF;
1662:
1663: --Bug 5931958. Added the OR condition l_organization_tab.COUNT = 0
1656: x_return_status => l_rs);
1657:
1658: IF l_debug_on THEN
1659: WSH_DEBUG_SV.logmsg(l_module_name,'return status:'||l_rs);
1660: WSH_DEBUG_SV.logmsg(l_module_name,'Organizations Count:'||l_organization_tab.Count);
1661: END IF;
1662:
1663: --Bug 5931958. Added the OR condition l_organization_tab.COUNT = 0
1664: IF (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) OR
1666: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1667: FND_MESSAGE.SET_NAME('WSH','WSH_LOC_ORG_UNDEFINED');
1668: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1669: IF l_debug_on THEN
1670: WSH_DEBUG_SV.log(l_module_name, 'Error: Failed to get Organization from stop location id ',
1671: l_stop_location_id );
1672: END IF;
1673: GOTO trip_error;
1674: END IF;
1675:
1676: -- Assign first record in organization table
1677: l_trip_info_tab(i).vehicle_organization_id := l_organization_tab(l_organization_tab.FIRST);
1678: IF l_debug_on THEN
1679: WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_EXTERNAL_INTERFACE_SV.Validate_Item');
1680: END IF;
1681:
1682: WSH_EXTERNAL_INTERFACE_SV.Validate_Item
1683: (p_concatenated_segments => l_trip_info_tab(i).vehicle_item_name,
1685: x_inventory_item_id => l_trip_info_tab(i).vehicle_item_id,
1686: x_return_status => l_rs);
1687:
1688: IF l_debug_on THEN
1689: WSH_DEBUG_SV.logmsg(l_module_name,'return status:'||l_rs);
1690: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1691: END IF;
1692:
1693: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1686: x_return_status => l_rs);
1687:
1688: IF l_debug_on THEN
1689: WSH_DEBUG_SV.logmsg(l_module_name,'return status:'||l_rs);
1690: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1691: END IF;
1692:
1693: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1694: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1697: FND_MESSAGE.SET_TOKEN('ORGANIZATION',
1698: WSH_UTIL_CORE.get_org_name (p_organization_id =>l_organization_tab(l_organization_tab.FIRST)));
1699: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1700: IF l_debug_on THEN
1701: WSH_DEBUG_SV.log(l_module_name, 'Validate Item Failed: l_return_status', l_return_status);
1702: END IF;
1703: GOTO trip_error;
1704: END IF;
1705:
1705:
1706: END IF;--}
1707:
1708: IF l_debug_on THEN
1709: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1710: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1711: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1712: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1713: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1706: END IF;--}
1707:
1708: IF l_debug_on THEN
1709: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1710: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1711: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1712: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1713: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1714: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1707:
1708: IF l_debug_on THEN
1709: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1710: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1711: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1712: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1713: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1714: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1715: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1708: IF l_debug_on THEN
1709: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1710: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1711: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1712: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1713: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1714: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1715: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1716: 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,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1710: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1711: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1712: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1713: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1714: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1715: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1716: WSH_DEBUG_SV.logmsg(l_module_name,'Tp Plan Name:'||l_trip_info_tab(i).tp_plan_name);
1717: WSH_DEBUG_SV.logmsg(l_module_name,'Interface Action Code:'||l_trip_info_tab(i).interface_action_code);
1710: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1711: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1712: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1713: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1714: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1715: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1716: WSH_DEBUG_SV.logmsg(l_module_name,'Tp Plan Name:'||l_trip_info_tab(i).tp_plan_name);
1717: WSH_DEBUG_SV.logmsg(l_module_name,'Interface Action Code:'||l_trip_info_tab(i).interface_action_code);
1718: END IF;
1711: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1712: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1713: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1714: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1715: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1716: WSH_DEBUG_SV.logmsg(l_module_name,'Tp Plan Name:'||l_trip_info_tab(i).tp_plan_name);
1717: WSH_DEBUG_SV.logmsg(l_module_name,'Interface Action Code:'||l_trip_info_tab(i).interface_action_code);
1718: END IF;
1719:
1712: WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1713: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1714: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1715: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1716: WSH_DEBUG_SV.logmsg(l_module_name,'Tp Plan Name:'||l_trip_info_tab(i).tp_plan_name);
1717: WSH_DEBUG_SV.logmsg(l_module_name,'Interface Action Code:'||l_trip_info_tab(i).interface_action_code);
1718: END IF;
1719:
1720: --
1713: WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1714: WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1715: WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1716: WSH_DEBUG_SV.logmsg(l_module_name,'Tp Plan Name:'||l_trip_info_tab(i).tp_plan_name);
1717: WSH_DEBUG_SV.logmsg(l_module_name,'Interface Action Code:'||l_trip_info_tab(i).interface_action_code);
1718: END IF;
1719:
1720: --
1721: -- This logic would be only for G_TMS_DELETE_CODE
1746: x_delivery_id_tab => l_delivery_id_tab, -- dlvy ids
1747: x_return_status => l_rs);
1748:
1749: IF l_debug_on THEN
1750: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after find_deliveries_for_trip'||l_rs);
1751: END IF;
1752:
1753: -- Handle return status here !!!
1754: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1753: -- Handle return status here !!!
1754: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1755: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1756: IF l_debug_on THEN
1757: WSH_DEBUG_SV.log(l_module_name, 'Find Deliveries for Trip Failed,l_return_status:', l_return_status);
1758: END IF;
1759: GOTO trip_error;
1760: END IF;
1761:
1775: l_delivery_id_tab(del_rec);
1776: l_unassigned_dlvy_version_tab(l_unassigned_delivery_id_tab.count) :=
1777: l_delivery_tab(l_delivery_id_tab(del_rec)).tms_version_number;
1778: IF l_debug_on THEN
1779: WSH_DEBUG_SV.logmsg(l_module_name,'Status of Delivery '||
1780: l_delivery_id_tab(del_rec)||'-->'||l_delivery_tab(l_delivery_id_tab(del_rec)).status_code);
1781: END IF;
1782: END IF;
1783: END LOOP; --}
1781: END IF;
1782: END IF;
1783: END LOOP; --}
1784: IF l_debug_on THEN
1785: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
1786: WSH_DEBUG_SV.logmsg(l_module_name,'Overall Unassign Count:'||l_unassigned_delivery_id_tab.count);
1787: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Version Count:'||l_unassigned_dlvy_version_tab.count);
1788: END IF;
1789:
1782: END IF;
1783: END LOOP; --}
1784: IF l_debug_on THEN
1785: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
1786: WSH_DEBUG_SV.logmsg(l_module_name,'Overall Unassign Count:'||l_unassigned_delivery_id_tab.count);
1787: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Version Count:'||l_unassigned_dlvy_version_tab.count);
1788: END IF;
1789:
1790: l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
1783: END LOOP; --}
1784: IF l_debug_on THEN
1785: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
1786: WSH_DEBUG_SV.logmsg(l_module_name,'Overall Unassign Count:'||l_unassigned_delivery_id_tab.count);
1787: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Version Count:'||l_unassigned_dlvy_version_tab.count);
1788: END IF;
1789:
1790: l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
1791: l_del_action_prms.action_code := 'UNASSIGN-TRIP';
1803: x_msg_count => l_msg_count,
1804: x_msg_data => l_msg_data);
1805:
1806: IF l_debug_on THEN
1807: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Unassign Delivery'||l_rs);
1808: END IF;
1809: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1810: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1811: IF l_debug_on THEN
1808: END IF;
1809: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1810: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1811: IF l_debug_on THEN
1812: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery from Trip Failed: l_return_status', l_return_status);
1813: END IF;
1814: GOTO trip_error;
1815: END IF;
1816: END IF;--} -- delivery_id_tab.count
1880:
1881: l_in_rec.caller := 'FTE_TMS_INTEGRATION';
1882:
1883: IF l_debug_on THEN
1884: WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_TRIPS_GRP.Create_Update_Trip:'||l_in_rec.action_code);
1885: END IF;
1886:
1887: WSH_TRIPS_GRP.Create_Update_Trip(
1888: p_api_version_number => 1.0,
1898:
1899: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1900: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1901: IF l_debug_on THEN
1902: WSH_DEBUG_SV.log(l_module_name, 'Create Update Trip Failed: l_return_status', l_return_status);
1903: END IF;
1904: GOTO trip_error;
1905: END IF;
1906:
1912: l_trip_id := l_trip_info_tab(i).trip_id;
1913: END IF;
1914:
1915: IF l_debug_on THEN
1916: WSH_DEBUG_SV.logmsg(l_module_name,'Create_Update_Trip,return status:'||l_rs);
1917: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_trip_id);
1918: END IF;
1919:
1920: END;--} process trip
1913: END IF;
1914:
1915: IF l_debug_on THEN
1916: WSH_DEBUG_SV.logmsg(l_module_name,'Create_Update_Trip,return status:'||l_rs);
1917: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_trip_id);
1918: END IF;
1919:
1920: END;--} process trip
1921:
1922:
1923: -- 4d. Process Trip Stops
1924: -- populate stop UOM in case of creation
1925: IF l_debug_on THEN
1926: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1927: WSH_DEBUG_SV.logmsg(l_module_name,'4d. PROCESS TRIP STOPS');
1928: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1929: END IF;
1930:
1923: -- 4d. Process Trip Stops
1924: -- populate stop UOM in case of creation
1925: IF l_debug_on THEN
1926: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1927: WSH_DEBUG_SV.logmsg(l_module_name,'4d. PROCESS TRIP STOPS');
1928: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1929: END IF;
1930:
1931: DECLARE
1924: -- populate stop UOM in case of creation
1925: IF l_debug_on THEN
1926: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1927: WSH_DEBUG_SV.logmsg(l_module_name,'4d. PROCESS TRIP STOPS');
1928: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1929: END IF;
1930:
1931: DECLARE
1932: -- Find Deliveries associated to a stop
1980: l_return_status := 'T';
1981: FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
1982: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1983: IF l_debug_on THEN
1984: WSH_DEBUG_SV.log(l_module_name, 'Invalid Stop Location, l_return_status:', l_return_status);
1985: END IF;
1986: GOTO trip_error;
1987: END;--}
1988:
1995: l_return_status := 'T';-- special case, need to handle error a bit differently
1996: FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
1997: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1998: IF l_debug_on THEN
1999: WSH_DEBUG_SV.log(l_module_name, 'Invalid Stop Location, l_return_status:', l_return_status);
2000: END IF;
2001: GOTO trip_error;
2002: END IF;
2003:
2014: -- deliveries
2015: FOR rec in c_get_stops(l_trip_info_tab(i).trip_id)
2016: LOOP--{
2017: IF l_debug_on THEN
2018: WSH_DEBUG_SV.logmsg(l_module_name,'===== EBS STOPS =======');
2019: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2020: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2021: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2022: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2015: FOR rec in c_get_stops(l_trip_info_tab(i).trip_id)
2016: LOOP--{
2017: IF l_debug_on THEN
2018: WSH_DEBUG_SV.logmsg(l_module_name,'===== EBS STOPS =======');
2019: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2020: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2021: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2022: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2023: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Location id:'||rec.physical_location_id);
2016: LOOP--{
2017: IF l_debug_on THEN
2018: WSH_DEBUG_SV.logmsg(l_module_name,'===== EBS STOPS =======');
2019: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2020: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2021: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2022: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2023: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Location id:'||rec.physical_location_id);
2024: END IF;
2017: IF l_debug_on THEN
2018: WSH_DEBUG_SV.logmsg(l_module_name,'===== EBS STOPS =======');
2019: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2020: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2021: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2022: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2023: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Location id:'||rec.physical_location_id);
2024: END IF;
2025:
2018: WSH_DEBUG_SV.logmsg(l_module_name,'===== EBS STOPS =======');
2019: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2020: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2021: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2022: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2023: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Location id:'||rec.physical_location_id);
2024: END IF;
2025:
2026: l_stop_matches := 'N';
2019: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2020: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2021: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2022: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2023: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Location id:'||rec.physical_location_id);
2024: END IF;
2025:
2026: l_stop_matches := 'N';
2027: FOR stop_index in l_stop_local_tab.FIRST..l_stop_local_tab.LAST
2026: l_stop_matches := 'N';
2027: FOR stop_index in l_stop_local_tab.FIRST..l_stop_local_tab.LAST
2028: LOOP--{
2029: IF l_debug_on THEN
2030: WSH_DEBUG_SV.logmsg(l_module_name,'===== GC3 STOP DESCRIPTION =======');
2031: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||l_stop_local_tab(stop_index).stop_location_id);
2032: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||l_stop_local_tab(stop_index).stop_sequence_number);
2033: END IF;
2034:
2027: FOR stop_index in l_stop_local_tab.FIRST..l_stop_local_tab.LAST
2028: LOOP--{
2029: IF l_debug_on THEN
2030: WSH_DEBUG_SV.logmsg(l_module_name,'===== GC3 STOP DESCRIPTION =======');
2031: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||l_stop_local_tab(stop_index).stop_location_id);
2032: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||l_stop_local_tab(stop_index).stop_sequence_number);
2033: END IF;
2034:
2035: -- Compare the GC3 sent stop location + stop sequence number
2028: LOOP--{
2029: IF l_debug_on THEN
2030: WSH_DEBUG_SV.logmsg(l_module_name,'===== GC3 STOP DESCRIPTION =======');
2031: WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||l_stop_local_tab(stop_index).stop_location_id);
2032: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||l_stop_local_tab(stop_index).stop_sequence_number);
2033: END IF;
2034:
2035: -- Compare the GC3 sent stop location + stop sequence number
2036: IF to_number(l_stop_local_tab(stop_index).stop_location_id) = rec.stop_location_id AND
2088: l_stop_local_tab(stop_index).attribute15 := rec.attribute15;
2089:
2090: -- End of Populating other fields
2091: IF l_debug_on THEN
2092: WSH_DEBUG_SV.logmsg(l_module_name,'Matching Stop found,id:'||rec.stop_id);
2093: END IF;
2094: EXIT; --from the l_stop_local_tab loop
2095: ELSE -- also check for Internal Location
2096:
2108: l_return_status := 'T';-- special case, need to handle error a bit differently
2109: FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
2110: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2111: IF l_debug_on THEN
2112: WSH_DEBUG_SV.log(l_module_name, 'Invalid Stop Location, l_return_status:', l_return_status);
2113: END IF;
2114: GOTO trip_error;
2115: END IF;
2116:
2114: GOTO trip_error;
2115: END IF;
2116:
2117: IF l_debug_on THEN
2118: WSH_DEBUG_SV.log(l_module_name, 'Physical Location:',l_physical_loc_id);
2119: END IF;
2120:
2121: -- Compare the GC3 sent stop location + stop sequence number
2122: IF to_number(l_stop_local_tab(stop_index).stop_location_id) = l_physical_loc_id AND
2174: l_stop_local_tab(stop_index).attribute15 := rec.attribute15;
2175: -- End of Populating other fields
2176:
2177: IF l_debug_on THEN
2178: WSH_DEBUG_SV.logmsg(l_module_name,'Matching Stop found,id:'||rec.stop_id);
2179: END IF;
2180: EXIT; --from the l_stop_local_tab loop
2181: END IF;--}
2182: END IF;--}
2204: -- are linked to 1 delivery, then donot launch unassign-trip twice
2205: l_is_duplicate := 'N';
2206:
2207: IF l_debug_on THEN
2208: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Dlvy Tab count:'||l_unassigned_delivery_id_tab.count);
2209: END IF;
2210: IF l_del_attrs.count > 0 THEN
2211: FOR duplicate in l_del_attrs.FIRST..l_del_attrs.LAST
2212: LOOP
2217: END LOOP;
2218: END IF;
2219:
2220: IF l_debug_on THEN
2221: WSH_DEBUG_SV.logmsg(l_module_name,'Is Duplicate:'||l_is_duplicate);
2222: END IF;
2223:
2224: -- Only increase the count for non-duplicate deliveries
2225: -- Cursor would have selected deliveries with dummy stops, Internal Location
2234:
2235: END IF;
2236:
2237: IF l_debug_on THEN
2238: WSH_DEBUG_SV.logmsg(l_module_name,'After populating l_del_attrs count:'||l_del_attrs.count);
2239: WSH_DEBUG_SV.logmsg(l_module_name,'After populating Unassign Dlvy Tab count:'||l_unassigned_delivery_id_tab.count);
2240: END IF;
2241:
2242: END LOOP;
2235: END IF;
2236:
2237: IF l_debug_on THEN
2238: WSH_DEBUG_SV.logmsg(l_module_name,'After populating l_del_attrs count:'||l_del_attrs.count);
2239: WSH_DEBUG_SV.logmsg(l_module_name,'After populating Unassign Dlvy Tab count:'||l_unassigned_delivery_id_tab.count);
2240: END IF;
2241:
2242: END LOOP;
2243:
2241:
2242: END LOOP;
2243:
2244: IF l_debug_on THEN
2245: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries associated to the stop:'||l_del_attrs.count);
2246: END IF;
2247:
2248: IF l_del_attrs.count > 0 THEN--{
2249: l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
2262: x_msg_count => l_msg_count,
2263: x_msg_data => l_msg_data);
2264:
2265: IF l_debug_on THEN
2266: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Unassign Delivery'||l_rs);
2267: END IF;
2268:
2269: -- Handle error here !!!, should these deliveries be deleted from unassign table list??
2270: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2269: -- Handle error here !!!, should these deliveries be deleted from unassign table list??
2270: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2271: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2272: IF l_debug_on THEN
2273: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery Failed: l_return_status', l_return_status);
2274: END IF;
2275: GOTO trip_error;
2276: END IF;
2277: END IF;--}
2295: x_msg_data => l_msg_data);
2296:
2297: -- Handle return status here, treat warning as success
2298: IF l_debug_on THEN
2299: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after deleting stop'||l_rs);
2300: END IF;
2301: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2302: -- go to trip_error
2303: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2301: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2302: -- go to trip_error
2303: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2304: IF l_debug_on THEN
2305: WSH_DEBUG_SV.log(l_module_name, 'Delete Trip Stop Failed: l_return_status', l_return_status);
2306: END IF;
2307: GOTO trip_error;
2308: END IF;
2309: END IF;--} -- if l_stop_matches = 'N'
2312:
2313: END IF;--} -- if l_trip_info_tab(i).trip_id is not null, trip pre-existed in EBS
2314:
2315: IF l_debug_on THEN
2316: WSH_DEBUG_SV.logmsg(l_module_name,'Before Internal Locations Check, Stop Local Table, Count'||l_stop_local_tab.count);
2317: END IF;
2318:
2319: -- Internal Locations Check here
2320: -- Call Process Internal Locations Here
2326:
2327: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2328: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2329: IF l_debug_on THEN
2330: WSH_DEBUG_SV.log(l_module_name, 'Process Internal Location, l_return_status:', l_return_status);
2331: END IF;
2332: GOTO trip_error;
2333: END IF;
2334: END IF;--}
2333: END IF;
2334: END IF;--}
2335:
2336: IF l_debug_on THEN
2337: WSH_DEBUG_SV.logmsg(l_module_name,'After Internal Locations Check,Stop Local Table, Count'||l_stop_local_tab.count);
2338: END IF;
2339:
2340: -- Loop across l_stop_local_tab for Create and Update stops
2341: -- Initialize l_stop_attrs
2372: --
2373: -- ECO 5101760
2374: -- Convert Stop level planned dates, based on the timezone code
2375: IF l_debug_on THEN
2376: WSH_DEBUG_SV.log(l_module_name,'OTM sent Planned Arrival Date',l_stop_local_tab(stop_rec).planned_arrival_date);
2377: WSH_DEBUG_SV.log(l_module_name,'OTM sent Planned Departure Date',l_stop_local_tab(stop_rec).planned_departure_date);
2378: END IF;
2379:
2380: get_server_time
2373: -- ECO 5101760
2374: -- Convert Stop level planned dates, based on the timezone code
2375: IF l_debug_on THEN
2376: WSH_DEBUG_SV.log(l_module_name,'OTM sent Planned Arrival Date',l_stop_local_tab(stop_rec).planned_arrival_date);
2377: WSH_DEBUG_SV.log(l_module_name,'OTM sent Planned Departure Date',l_stop_local_tab(stop_rec).planned_departure_date);
2378: END IF;
2379:
2380: get_server_time
2381: (p_source_time => l_stop_local_tab(stop_rec).planned_arrival_date,
2387:
2388: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2389: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2390: IF l_debug_on THEN
2391: WSH_DEBUG_SV.log(l_module_name, 'Get Server Time for Planned Arrival Date, l_return_status:', l_return_status);
2392: END IF;
2393: GOTO trip_error;
2394: END IF;
2395:
2403:
2404: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2405: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2406: IF l_debug_on THEN
2407: WSH_DEBUG_SV.log(l_module_name, 'Get Server Time for Planned Arrival Date, l_return_status:', l_return_status);
2408: END IF;
2409: GOTO trip_error;
2410: END IF;
2411:
2412: l_stop_attrs(l_stop_count).PLANNED_ARRIVAL_DATE := l_stop_local_tab(stop_rec).planned_arrival_date;
2413: l_stop_attrs(l_stop_count).PLANNED_DEPARTURE_DATE := l_stop_local_tab(stop_rec).planned_departure_date;
2414:
2415: IF l_debug_on THEN
2416: WSH_DEBUG_SV.log(l_module_name,'Converted Planned Arrival Date',l_stop_attrs(l_stop_count).PLANNED_ARRIVAL_DATE);
2417: WSH_DEBUG_SV.log(l_module_name,'Converted Planned Departure Date',l_stop_attrs(l_stop_count).PLANNED_departure_DATE);
2418: END IF;
2419: -- End of ECO 5101760
2420: --
2413: l_stop_attrs(l_stop_count).PLANNED_DEPARTURE_DATE := l_stop_local_tab(stop_rec).planned_departure_date;
2414:
2415: IF l_debug_on THEN
2416: WSH_DEBUG_SV.log(l_module_name,'Converted Planned Arrival Date',l_stop_attrs(l_stop_count).PLANNED_ARRIVAL_DATE);
2417: WSH_DEBUG_SV.log(l_module_name,'Converted Planned Departure Date',l_stop_attrs(l_stop_count).PLANNED_departure_DATE);
2418: END IF;
2419: -- End of ECO 5101760
2420: --
2421: l_stop_attrs(l_stop_count).DEPARTURE_GROSS_WEIGHT := nvl(l_stop_local_tab(stop_rec).departure_gross_weight,0);
2437: LOOP--{
2438: IF l_dleg_local_tab(dleg_rec).pick_up_stop_interface_id = l_stop_local_tab(stop_rec).stop_interface_id THEN
2439: -- For 1st delivery, get the organization id
2440: IF l_debug_on THEN
2441: WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_WV_UTILS.GET_DEFAULT_UOMS for pickup');
2442: END IF;
2443: --
2444: wsh_wv_utils.get_default_uoms
2445: (p_organization_id => l_dleg_local_tab(dleg_rec).organization_id,
2446: x_weight_uom_code => l_stop_local_tab(stop_rec).weight_uom_code,
2447: x_volume_uom_code => l_stop_local_tab(stop_rec).volume_uom_code,
2448: x_return_status => l_rs);
2449: IF l_debug_on THEN
2450: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_WV_UTILS.GET_DEFAULT_UOMS'||l_rs);
2451: END IF;
2452: -- Handle return Status error!!!
2453: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2454: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2452: -- Handle return Status error!!!
2453: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2454: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2455: IF l_debug_on THEN
2456: WSH_DEBUG_SV.log(l_module_name, 'Get_Default_UOM failed: l_return_status', l_return_status);
2457: END IF;
2458: GOTO trip_error;
2459: END IF;
2460:
2458: GOTO trip_error;
2459: END IF;
2460:
2461: IF l_debug_on THEN
2462: 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);
2463: END IF;
2464: ELSE -- to find UOM for dropoff stops, which are not pickups
2465: IF l_dleg_local_tab(dleg_rec).drop_off_stop_interface_id = l_stop_local_tab(stop_rec).stop_interface_id THEN--{
2466: -- For 1st delivery, get the organization id
2464: ELSE -- to find UOM for dropoff stops, which are not pickups
2465: IF l_dleg_local_tab(dleg_rec).drop_off_stop_interface_id = l_stop_local_tab(stop_rec).stop_interface_id THEN--{
2466: -- For 1st delivery, get the organization id
2467: IF l_debug_on THEN
2468: WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_WV_UTILS.GET_DEFAULT_UOMS for dropoff');
2469: END IF;
2470: --
2471: wsh_wv_utils.get_default_uoms
2472: (p_organization_id => l_dleg_local_tab(dleg_rec).organization_id,
2473: x_weight_uom_code => l_stop_local_tab(stop_rec).weight_uom_code,
2474: x_volume_uom_code => l_stop_local_tab(stop_rec).volume_uom_code,
2475: x_return_status => l_rs);
2476: IF l_debug_on THEN
2477: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_WV_UTILS.GET_DEFAULT_UOMS'||l_rs);
2478: END IF;
2479: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2480: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2481: IF l_debug_on THEN
2478: END IF;
2479: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2480: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2481: IF l_debug_on THEN
2482: WSH_DEBUG_SV.log(l_module_name, 'Get_Default_UOM Failed: l_return_status', l_return_status);
2483: END IF;
2484: GOTO trip_error;
2485: END IF;
2486: -- Handle return Status error!!!
2485: END IF;
2486: -- Handle return Status error!!!
2487:
2488: IF l_debug_on THEN
2489: 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);
2490: END IF;
2491:
2492: END IF; --}
2493: END IF;
2531: l_stop_attrs(l_stop_count).ATTRIBUTE14 := l_stop_local_tab(stop_rec).attribute14;
2532: l_stop_attrs(l_stop_count).ATTRIBUTE15 := l_stop_local_tab(stop_rec).attribute15;
2533:
2534: IF l_debug_on THEN
2535: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||l_stop_local_tab(stop_rec).stop_id);
2536: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2537: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2538: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2539: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2532: l_stop_attrs(l_stop_count).ATTRIBUTE15 := l_stop_local_tab(stop_rec).attribute15;
2533:
2534: IF l_debug_on THEN
2535: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||l_stop_local_tab(stop_rec).stop_id);
2536: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2537: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2538: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2539: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2540: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2533:
2534: IF l_debug_on THEN
2535: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||l_stop_local_tab(stop_rec).stop_id);
2536: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2537: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2538: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2539: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2540: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2541: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2534: IF l_debug_on THEN
2535: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||l_stop_local_tab(stop_rec).stop_id);
2536: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2537: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2538: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2539: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2540: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2541: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2542: WSH_DEBUG_SV.logmsg(l_module_name,'Before Calling Create_update_Stop,count:'||l_stop_attrs.count);
2535: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||l_stop_local_tab(stop_rec).stop_id);
2536: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2537: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2538: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2539: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2540: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2541: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2542: WSH_DEBUG_SV.logmsg(l_module_name,'Before Calling Create_update_Stop,count:'||l_stop_attrs.count);
2543: END IF;
2536: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2537: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2538: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2539: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2540: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2541: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2542: WSH_DEBUG_SV.logmsg(l_module_name,'Before Calling Create_update_Stop,count:'||l_stop_attrs.count);
2543: END IF;
2544:
2537: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2538: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2539: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2540: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2541: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2542: WSH_DEBUG_SV.logmsg(l_module_name,'Before Calling Create_update_Stop,count:'||l_stop_attrs.count);
2543: END IF;
2544:
2545: wsh_trip_stops_grp.Create_Update_Stop(
2538: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2539: WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2540: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2541: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2542: WSH_DEBUG_SV.logmsg(l_module_name,'Before Calling Create_update_Stop,count:'||l_stop_attrs.count);
2543: END IF;
2544:
2545: wsh_trip_stops_grp.Create_Update_Stop(
2546: p_api_version_number => 1.0,
2558: -- Handle return status and error Here !!!
2559: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2560: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2561: IF l_debug_on THEN
2562: WSH_DEBUG_SV.log(l_module_name, 'Create Update Trip Stop Failed: l_return_status', l_return_status);
2563: END IF;
2564: GOTO trip_error;
2565: END IF;
2566:
2570: l_stop_local_tab(stop_rec).stop_id := l_stop_out_tab(l_stop_out_tab.FIRST).stop_id;
2571: END IF;
2572:
2573: IF l_debug_on THEN
2574: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Create_Update_stop'||l_rs);
2575: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id after Create_Update_stop'||l_stop_local_tab(stop_rec).stop_id);
2576: END IF;
2577:
2578: -- OTM Dock Door App Sched Proj
2571: END IF;
2572:
2573: IF l_debug_on THEN
2574: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Create_Update_stop'||l_rs);
2575: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id after Create_Update_stop'||l_stop_local_tab(stop_rec).stop_id);
2576: END IF;
2577:
2578: -- OTM Dock Door App Sched Proj
2579: DECLARE
2611: CLOSE c_dock_appt_rec;
2612: IF (l_organization_id IS NOT NULL) THEN --{
2613: IF (wsh_util_validate.check_wms_org(l_organization_id) = 'Y') THEN --{
2614: IF l_debug_on THEN
2615: WSH_DEBUG_SV.log(l_module_name,'Calling WSH_SHIPPING_PARAMS_PVT.GET',WSH_DEBUG_SV.C_PROC_LEVEL);
2616: END IF;
2617: WSH_SHIPPING_PARAMS_PVT.GET(p_organization_id => l_organization_id,
2618: x_param_info => l_shipping_parameters,
2619: x_return_status => l_rs);
2620:
2621: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2622: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2623: IF l_debug_on THEN
2624: WSH_DEBUG_SV.log(l_module_name, 'WSH_SHIPPING_PARAMS_PVT.GET Failed: l_return_status', l_return_status);
2625: END IF;
2626: GOTO trip_error;
2627: END IF;
2628:
2638:
2639: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2640: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2641: IF l_debug_on THEN
2642: WSH_DEBUG_SV.log(l_module_name, 'Get Server Time for Dock Door Appointment Start Time, l_return_status:', l_return_status);
2643: END IF;
2644: GOTO trip_error;
2645: END IF;
2646: END IF;
2655:
2656: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2657: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2658: IF l_debug_on THEN
2659: WSH_DEBUG_SV.log(l_module_name, 'Get Server Time for Dock Door Appointment End Time, l_return_status:', l_return_status);
2660: END IF;
2661: GOTO trip_error;
2662: END IF;
2663: END IF;
2670: l_dock_appt_tab(l_dock_appt_index).start_time := x_start_time;
2671: l_dock_appt_tab(l_dock_appt_index).end_time := x_end_time;
2672:
2673: IF l_debug_on THEN
2674: 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);
2675: 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);
2676: 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);
2677: 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);
2678: 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: l_dock_appt_tab(l_dock_appt_index).end_time := x_end_time;
2672:
2673: IF l_debug_on THEN
2674: 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);
2675: 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);
2676: 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);
2677: 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);
2678: 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);
2679: END IF;
2672:
2673: IF l_debug_on THEN
2674: 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);
2675: 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);
2676: 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);
2677: 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);
2678: 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);
2679: END IF;
2680: END IF; --} Cheking dock_appt_schedling_flag
2673: IF l_debug_on THEN
2674: 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);
2675: 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);
2676: 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);
2677: 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);
2678: 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);
2679: END IF;
2680: END IF; --} Cheking dock_appt_schedling_flag
2681: END IF; --} Checking WMS Org
2674: 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);
2675: 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);
2676: 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);
2677: 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);
2678: 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);
2679: END IF;
2680: END IF; --} Cheking dock_appt_schedling_flag
2681: END IF; --} Checking WMS Org
2682: END IF; --} Checking l_organization_id as NULL
2683: EXCEPTION
2684: WHEN OTHERS THEN
2685: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2686: IF l_debug_on THEN
2687: WSH_DEBUG_SV.log(l_module_name, 'Error while fetching Dock Door Appointment Scheduling Info.');
2688: END IF;
2689: IF (c_dock_appt_rec%ISOPEN) THEN
2690: CLOSE c_dock_appt_rec;
2691: END IF;
2701: BEGIN
2702: FOR st in c_1
2703: LOOP
2704: IF l_debug_on THEN
2705: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||st.stop_id);
2706: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||st.stop_location_id);
2707: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||st.stop_sequence_number);
2708: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||st.physical_stop_id);
2709: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Loc id:'||st.physical_location_id);
2702: FOR st in c_1
2703: LOOP
2704: IF l_debug_on THEN
2705: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||st.stop_id);
2706: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||st.stop_location_id);
2707: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||st.stop_sequence_number);
2708: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||st.physical_stop_id);
2709: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Loc id:'||st.physical_location_id);
2710: END IF;
2703: LOOP
2704: IF l_debug_on THEN
2705: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||st.stop_id);
2706: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||st.stop_location_id);
2707: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||st.stop_sequence_number);
2708: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||st.physical_stop_id);
2709: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Loc id:'||st.physical_location_id);
2710: END IF;
2711: END LOOP;
2704: IF l_debug_on THEN
2705: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||st.stop_id);
2706: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||st.stop_location_id);
2707: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||st.stop_sequence_number);
2708: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||st.physical_stop_id);
2709: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Loc id:'||st.physical_location_id);
2710: END IF;
2711: END LOOP;
2712: END;
2705: WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||st.stop_id);
2706: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||st.stop_location_id);
2707: WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||st.stop_sequence_number);
2708: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||st.physical_stop_id);
2709: WSH_DEBUG_SV.logmsg(l_module_name,'Physical Loc id:'||st.physical_location_id);
2710: END IF;
2711: END LOOP;
2712: END;
2713:
2718:
2719:
2720: -- 4e. Process Deliveries
2721: IF l_debug_on THEN
2722: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2723: WSH_DEBUG_SV.logmsg(l_module_name,'4e. PROCESS DELIVERIES');
2724: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2725: END IF;
2726:
2719:
2720: -- 4e. Process Deliveries
2721: IF l_debug_on THEN
2722: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2723: WSH_DEBUG_SV.logmsg(l_module_name,'4e. PROCESS DELIVERIES');
2724: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2725: END IF;
2726:
2727: DECLARE
2720: -- 4e. Process Deliveries
2721: IF l_debug_on THEN
2722: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2723: WSH_DEBUG_SV.logmsg(l_module_name,'4e. PROCESS DELIVERIES');
2724: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2725: END IF;
2726:
2727: DECLARE
2728:
2763: IF ( NVL(p_latest_version,'Y') = 'N' AND l_dleg_local_tab(del_version).tms_interface_flag NOT IN ('UR','UP') ) THEN
2764: --{
2765: -- raise error
2766: IF l_debug_on THEN
2767: WSH_DEBUG_SV.logmsg(l_module_name,'EBS and OTM version number donot match');
2768: END IF;
2769: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_IB_NOT_UPD_VERSION_ERR');
2770: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2771: l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
2776: IF NVL(p_latest_version,'Y') = 'Y' THEN
2777: --{
2778: -- raise error
2779: IF l_debug_on THEN
2780: WSH_DEBUG_SV.logmsg(l_module_name,'EBS and OTM version number donot match');
2781: END IF;
2782: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_IB_VERSION_ERROR');
2783: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2784: l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
2794: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2795: -- go to trip_error
2796: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2797: IF l_debug_on THEN
2798: WSH_DEBUG_SV.log(l_module_name, 'Version Comparison failed,l_return_status:', l_return_status);
2799: END IF;
2800: GOTO trip_error;
2801: END IF;
2802: -- End of Bug 5134725
2812: x_delivery_id_tab => l_delivery_id_tab, -- dlvy ids
2813: x_return_status => l_rs);
2814:
2815: IF l_debug_on THEN
2816: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after find_deliveries_for_trip'||l_rs);
2817: END IF;
2818: -- Handle return status here !!!
2819: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2820: -- go to trip_error
2819: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2820: -- go to trip_error
2821: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2822: IF l_debug_on THEN
2823: WSH_DEBUG_SV.log(l_module_name, 'Find Deliveries for Trip Failed,l_return_status:', l_return_status);
2824: END IF;
2825: GOTO trip_error;
2826: END IF;
2827:
2831: IF l_delivery_id_tab.count > 0 THEN
2832: FOR del_rec IN l_delivery_id_tab.FIRST..l_delivery_id_tab.LAST
2833: LOOP--{
2834: IF l_debug_on THEN
2835: WSH_DEBUG_SV.log(l_module_name,'Delivery id:',l_delivery_id_tab(del_rec));
2836: END IF;
2837: l_del_matches := 'N';
2838: FOR del_intface_rec IN l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
2839: LOOP--{
2841: -- Compare the list
2842: IF (l_delivery_id_tab(del_rec) = l_dleg_local_tab(del_intface_rec).delivery_id) THEN
2843: l_del_matches := 'Y'; --matching delivery found
2844: IF l_debug_on THEN
2845: WSH_DEBUG_SV.log(l_module_name,'Match Delivery id:',l_dleg_local_tab(del_intface_rec).delivery_id);
2846: END IF;
2847: EXIT;
2848: END IF;
2849: END LOOP;--}
2852: -- This EBS delivery is still on the same GC3 trip
2853: -- Will compare if the stops/legs have changed for this assignment
2854: -- Delivery is on same trip, but new stop linking,then unassign and assign
2855: IF l_debug_on THEN
2856: 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);
2857: END IF;
2858: ELSE -- l_del_matches = 'N'
2859: -- This EBS delivery is to be unassigned from the GC3 trip
2860: IF l_debug_on THEN
2857: END IF;
2858: ELSE -- l_del_matches = 'N'
2859: -- This EBS delivery is to be unassigned from the GC3 trip
2860: IF l_debug_on THEN
2861: 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);
2862: END IF;
2863: -- Populate l_del_attrs for call to delivery_action()
2864: l_del_attrs(l_del_attrs.count + 1).delivery_id := l_delivery_id_tab(del_rec);
2865: l_del_attrs(l_del_attrs.count).organization_id :=
2877: -- The above Unassignments can be grouped together, as they are for
2878: -- Deliveries from SAME TRIP.
2879: -- Bulk Unassignment of Deliveries From Trip
2880: IF l_debug_on THEN
2881: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
2882: END IF;
2883:
2884: IF l_del_attrs.count > 0 THEN--{
2885: l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
2898: x_msg_count => l_msg_count,
2899: x_msg_data => l_msg_data);
2900:
2901: IF l_debug_on THEN
2902: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Unassign Delivery'||l_rs);
2903: END IF;
2904: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2905: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2906: IF l_debug_on THEN
2903: END IF;
2904: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2905: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2906: IF l_debug_on THEN
2907: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery from Trip Failed: l_return_status', l_return_status);
2908: END IF;
2909: GOTO trip_error;
2910: END IF;
2911: END IF;--}
2925: x_unassign_id_tab => l_unassigned_delivery_id_tab,
2926: --x_unassign_ver_tab => l_unassigned_dlvy_version_tab,
2927: x_return_status => l_rs);
2928: IF l_debug_on THEN
2929: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Comparing Trips:'||l_rs);
2930: END IF;
2931: -- Handle return Status here !!!
2932: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2933: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2931: -- Handle return Status here !!!
2932: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2933: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2934: IF l_debug_on THEN
2935: WSH_DEBUG_SV.log(l_module_name, 'Compare Trip for Deliveries Failed: l_return_status', l_return_status);
2936: END IF;
2937: GOTO trip_error;
2938: END IF;
2939:
2937: GOTO trip_error;
2938: END IF;
2939:
2940: IF l_debug_on THEN
2941: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Delivery Tab Count:'||l_unassigned_delivery_id_tab.count);
2942: END IF;
2943:
2944: --
2945: -- ASSIGN ALL DELIVERIES in l_dleg_local_tab to l_trip_info_tab(i).trip_id
2959: -- identical for all deliveries in l_dleg_local_tab
2960: l_del_action_prms.trip_id := l_trip_id;
2961:
2962: IF l_debug_on THEN
2963: WSH_DEBUG_SV.log(l_module_name, 'DLEG LOCAL TAB COUNT:', l_dleg_local_tab.count);
2964: WSH_DEBUG_SV.log(l_module_name, 'DLVY ID TAB COUNT:', l_dlvy_id_tab.count);
2965: END IF;
2966:
2967: IF l_dleg_local_tab.count > 0 THEN--{
2960: l_del_action_prms.trip_id := l_trip_id;
2961:
2962: IF l_debug_on THEN
2963: WSH_DEBUG_SV.log(l_module_name, 'DLEG LOCAL TAB COUNT:', l_dleg_local_tab.count);
2964: WSH_DEBUG_SV.log(l_module_name, 'DLVY ID TAB COUNT:', l_dlvy_id_tab.count);
2965: END IF;
2966:
2967: IF l_dleg_local_tab.count > 0 THEN--{
2968: FOR assign_count IN l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
2967: IF l_dleg_local_tab.count > 0 THEN--{
2968: FOR assign_count IN l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
2969: LOOP--{
2970: IF l_debug_on THEN
2971: 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);
2972: END IF;
2973: -- No need to process the deliveries which still have the same assignment
2974: -- as before
2975: IF l_dleg_local_tab(assign_count).processed_flag = 'Y' THEN--{
2976: l_dlvy_id_tab(l_dlvy_id_tab.count + 1) := l_dleg_local_tab(assign_count).delivery_id;
2977: l_dlvy_version_tab(l_dlvy_id_tab.count) := l_dleg_local_tab(assign_count).tms_version_number;
2978: -- skip to next delivery
2979: IF l_debug_on THEN
2980: WSH_DEBUG_SV.log(l_module_name, 'Skip Delivery:', l_dleg_local_tab(assign_count).delivery_id);
2981: END IF;
2982: -- do not exit
2983:
2984: ELSE--} --{
2989: l_del_attrs(1).delivery_id := l_dleg_local_tab(assign_count).delivery_id;
2990: l_del_attrs(1).organization_id := l_dleg_local_tab(assign_count).organization_id;
2991:
2992: IF l_debug_on THEN
2993: WSH_DEBUG_SV.log(l_module_name, 'Process Delivery:',l_dleg_local_tab(assign_count).delivery_id);
2994: END IF;
2995:
2996: -- Need to find EBS stop id, GC3 sent planned dates
2997: -- Location and Sequence Number are part of dleg data structure
3031: END IF;
3032: END LOOP;
3033:
3034: IF l_debug_on THEN
3035: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Assign Delivery to Trip API');
3037: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3032: END LOOP;
3033:
3034: IF l_debug_on THEN
3035: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Assign Delivery to Trip API');
3037: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3033:
3034: IF l_debug_on THEN
3035: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Assign Delivery to Trip API');
3037: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3034: IF l_debug_on THEN
3035: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Assign Delivery to Trip API');
3037: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3042: 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,'========================================');
3036: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Assign Delivery to Trip API');
3037: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3043: 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,'Calling Assign Delivery to Trip API');
3037: WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3043: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3044: 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,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3043: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3044: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3045: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3038: WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3039: WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3043: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3044: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3045: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3046: 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,'---------Pickup Stop information----------');
3040: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3043: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3044: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3045: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3046: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3047: 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,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3041: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3043: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3044: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3045: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3046: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3047: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3048: 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,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3042: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3043: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3044: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3045: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3046: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3047: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3048: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3049: 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,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3043: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3044: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3045: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3046: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3047: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3048: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3049: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3050: 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,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3044: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3045: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3046: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3047: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3048: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3049: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3050: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Dep Date:'||l_del_action_prms.dropoff_dep_date);
3051: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3044: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3045: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3046: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3047: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3048: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3049: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3050: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Dep Date:'||l_del_action_prms.dropoff_dep_date);
3051: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3052: END IF;
3045: WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3046: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3047: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3048: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3049: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3050: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Dep Date:'||l_del_action_prms.dropoff_dep_date);
3051: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3052: END IF;
3053:
3046: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3047: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3048: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3049: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3050: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Dep Date:'||l_del_action_prms.dropoff_dep_date);
3051: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3052: END IF;
3053:
3054: WSH_DELIVERIES_GRP.delivery_action(
3047: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3048: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3049: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3050: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Dep Date:'||l_del_action_prms.dropoff_dep_date);
3051: WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3052: END IF;
3053:
3054: WSH_DELIVERIES_GRP.delivery_action(
3055: p_api_version_number => 1.0,
3063: x_msg_count => l_msg_count,
3064: x_msg_data => l_msg_data);
3065:
3066: IF l_debug_on THEN
3067: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Assign Delivery to Trip'||l_rs);
3068: END IF;
3069:
3070: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3071: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3069:
3070: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3071: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3072: IF l_debug_on THEN
3073: WSH_DEBUG_SV.log(l_module_name, 'Assign Delivery to Trip Failed: l_return_status', l_return_status);
3074: END IF;
3075: GOTO trip_error;
3076: ELSIF l_rs = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3077: l_dleg_local_tab(assign_count).processed_flag := 'Y';
3083: END LOOP;--}
3084: END IF;--}
3085:
3086: IF l_debug_on THEN
3087: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries successfully assigned to trip:'||l_dlvy_id_tab.count);
3088: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Delivery Count:'||l_unassigned_delivery_id_tab.count);
3089: END IF;
3090:
3091: -- Make sure the successfully assigned deliveries are removed from
3084: END IF;--}
3085:
3086: IF l_debug_on THEN
3087: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries successfully assigned to trip:'||l_dlvy_id_tab.count);
3088: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Delivery Count:'||l_unassigned_delivery_id_tab.count);
3089: END IF;
3090:
3091: -- Make sure the successfully assigned deliveries are removed from
3092: -- l_unassigned_delivery_id_tab.
3103: END LOOP;
3104: END LOOP;--}
3105:
3106: IF l_debug_on THEN
3107: WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Dlvy Index Count:'||l_del_index.count);
3108: END IF;
3109:
3110: -- Actual Deletion from the Unassign Delivery Table
3111: IF l_del_index.count > 0 THEN
3117: END IF;
3118: END IF; --}
3119:
3120: IF l_debug_on THEN
3121: WSH_DEBUG_SV.logmsg(l_module_name,'After compare, Unassign Dlvy Count:'||l_unassigned_delivery_id_tab.count);
3122: WSH_DEBUG_SV.logmsg(l_module_name,'After compare, Unassign Version Count:'||l_unassigned_dlvy_version_tab.count);
3123: END IF;
3124:
3125: END;--} -- process delivery
3118: END IF; --}
3119:
3120: IF l_debug_on THEN
3121: WSH_DEBUG_SV.logmsg(l_module_name,'After compare, Unassign Dlvy Count:'||l_unassigned_delivery_id_tab.count);
3122: WSH_DEBUG_SV.logmsg(l_module_name,'After compare, Unassign Version Count:'||l_unassigned_dlvy_version_tab.count);
3123: END IF;
3124:
3125: END;--} -- process delivery
3126:
3131:
3132: -- 4g. Process Freight Costs for each delivery
3133: -- Still within the loop of l_trip_info_tab, so trip_interface_id is known
3134: IF l_debug_on THEN
3135: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3136: WSH_DEBUG_SV.logmsg(l_module_name,'4g. PROCESS FREIGHT COSTS');
3137: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3138: END IF;
3139:
3132: -- 4g. Process Freight Costs for each delivery
3133: -- Still within the loop of l_trip_info_tab, so trip_interface_id is known
3134: IF l_debug_on THEN
3135: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3136: WSH_DEBUG_SV.logmsg(l_module_name,'4g. PROCESS FREIGHT COSTS');
3137: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3138: END IF;
3139:
3140: DECLARE
3133: -- Still within the loop of l_trip_info_tab, so trip_interface_id is known
3134: IF l_debug_on THEN
3135: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3136: WSH_DEBUG_SV.logmsg(l_module_name,'4g. PROCESS FREIGHT COSTS');
3137: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3138: END IF;
3139:
3140: DECLARE
3141: -- Delivery id in wsh_freight_costs_interface would be populated
3171: INTO l_frcost_type_id;
3172: CLOSE c_get_frcost_type_id;
3173:
3174: IF l_debug_on THEN
3175: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Type Id:'||l_frcost_type_id);
3176: WSH_DEBUG_SV.logmsg(l_module_name, 'Count of Delivery Id:'||l_dlvy_id_tab.count);
3177: END IF;
3178: END IF;
3179:
3172: CLOSE c_get_frcost_type_id;
3173:
3174: IF l_debug_on THEN
3175: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Type Id:'||l_frcost_type_id);
3176: WSH_DEBUG_SV.logmsg(l_module_name, 'Count of Delivery Id:'||l_dlvy_id_tab.count);
3177: END IF;
3178: END IF;
3179:
3180: -- NEED TO DELETE THE EXISTING FREIGHT COST RECORDS FOR THE DELIVERY FOR THIS TYPE
3201: WHERE delivery_id = l_dlvy_id_tab(j)
3202: AND freight_cost_type_id = l_frcost_type_id;
3203:
3204: IF l_debug_on THEN
3205: WSH_DEBUG_SV.logmsg(l_module_name, 'After Deleting Freight Cost Records:');
3206: END IF;
3207:
3208: FOR p in l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
3209: LOOP--{
3208: FOR p in l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
3209: LOOP--{
3210: -- There can be multiple freight cost records for each delivery
3211: IF l_debug_on THEN
3212: WSH_DEBUG_SV.log(l_module_name,'Different Delivery id:',l_dlvy_id_tab(p));
3213: END IF;
3214:
3215: FOR rec in c_freight_int_cur(l_dlvy_id_tab(p))
3216: LOOP--{
3228: l_freight_info_tab(l_count).currency_code := rec.currency_code;
3229: l_freight_info_tab(l_count).delivery_id := rec.delivery_id;
3230:
3231: IF l_debug_on THEN
3232: WSH_DEBUG_SV.log(l_module_name,'Delivery id:',l_freight_info_tab(l_count).delivery_id);
3233: WSH_DEBUG_SV.log(l_module_name,'Unit Amount:',l_freight_info_tab(l_count).Unit_amount);
3234: WSH_DEBUG_SV.log(l_module_name,'Total Amount:',l_freight_info_tab(l_count).Total_amount);
3235: WSH_DEBUG_SV.log(l_module_name,'Currency Code:',l_freight_info_tab(l_count).currency_code);
3236: END IF;
3229: l_freight_info_tab(l_count).delivery_id := rec.delivery_id;
3230:
3231: IF l_debug_on THEN
3232: WSH_DEBUG_SV.log(l_module_name,'Delivery id:',l_freight_info_tab(l_count).delivery_id);
3233: WSH_DEBUG_SV.log(l_module_name,'Unit Amount:',l_freight_info_tab(l_count).Unit_amount);
3234: WSH_DEBUG_SV.log(l_module_name,'Total Amount:',l_freight_info_tab(l_count).Total_amount);
3235: WSH_DEBUG_SV.log(l_module_name,'Currency Code:',l_freight_info_tab(l_count).currency_code);
3236: END IF;
3237:
3230:
3231: IF l_debug_on THEN
3232: WSH_DEBUG_SV.log(l_module_name,'Delivery id:',l_freight_info_tab(l_count).delivery_id);
3233: WSH_DEBUG_SV.log(l_module_name,'Unit Amount:',l_freight_info_tab(l_count).Unit_amount);
3234: WSH_DEBUG_SV.log(l_module_name,'Total Amount:',l_freight_info_tab(l_count).Total_amount);
3235: WSH_DEBUG_SV.log(l_module_name,'Currency Code:',l_freight_info_tab(l_count).currency_code);
3236: END IF;
3237:
3238: -- OTM R12, start of Bug 5952842(5729276)
3231: IF l_debug_on THEN
3232: WSH_DEBUG_SV.log(l_module_name,'Delivery id:',l_freight_info_tab(l_count).delivery_id);
3233: WSH_DEBUG_SV.log(l_module_name,'Unit Amount:',l_freight_info_tab(l_count).Unit_amount);
3234: WSH_DEBUG_SV.log(l_module_name,'Total Amount:',l_freight_info_tab(l_count).Total_amount);
3235: WSH_DEBUG_SV.log(l_module_name,'Currency Code:',l_freight_info_tab(l_count).currency_code);
3236: END IF;
3237:
3238: -- OTM R12, start of Bug 5952842(5729276)
3239: IF l_conversion_type_code IS NULL THEN--{
3243:
3244: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3245: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3246: IF l_debug_on THEN
3247: WSH_DEBUG_SV.log(l_module_name, 'get_currency_conversion_type Failed');
3248: END IF;
3249: GOTO trip_error;
3250: END IF;
3251:
3254: -- OTM R12
3255: l_freight_info_tab(l_count).conversion_type_code := l_conversion_type_code;
3256:
3257: IF l_debug_on THEN
3258: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3259: END IF;
3260: -- OTM R12, end of Bug 5952842(5729276)
3261:
3262: -- OTM R12 Only if freight cost record has currency
3270: OPEN c_get_currency_code(l_trip_info_tab(i).trip_interface_id);
3271: FETCH c_get_currency_code INTO l_carrier_cur_code,l_carrier_id;
3272:
3273: IF l_debug_on THEN
3274: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3275: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier id:'||l_carrier_id);
3276: END IF;
3277:
3278: -- OTM R12 check if carrier has a non-null currency
3271: FETCH c_get_currency_code INTO l_carrier_cur_code,l_carrier_id;
3272:
3273: IF l_debug_on THEN
3274: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3275: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier id:'||l_carrier_id);
3276: END IF;
3277:
3278: -- OTM R12 check if carrier has a non-null currency
3279: IF c_get_currency_code%NOTFOUND THEN
3278: -- OTM R12 check if carrier has a non-null currency
3279: IF c_get_currency_code%NOTFOUND THEN
3280: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3281: IF l_debug_on THEN
3282: WSH_DEBUG_SV.log(l_module_name, 'Carrier not found ');
3283: END IF;
3284: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_ID_NOT_FOUND');
3285: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3286: CLOSE c_get_currency_code;
3291: x_carrier_id => l_carrier_id,
3292: x_return_status => l_rs);
3293:
3294: IF l_debug_on THEN
3295: WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_UTIL_VALIDATE.Validate_Carrier:',l_rs);
3296: END IF;
3297:
3298: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3299: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3297:
3298: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3299: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3300: IF l_debug_on THEN
3301: WSH_DEBUG_SV.log(l_module_name, 'Validate Carrier Failed: l_return_status', l_return_status);
3302: END IF;
3303: CLOSE c_get_currency_code;
3304: GOTO trip_error;
3305: END IF;
3308:
3309: IF l_carrier_cur_code IS NULL THEN
3310: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3311: IF l_debug_on THEN
3312: WSH_DEBUG_SV.log(l_module_name, 'Carrier Currency not defined ');
3313: END IF;
3314: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NO_CURRENCY');
3315: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3316: GOTO trip_error;
3330: x_return_status => l_rs,
3331: x_adjusted_amount => l_freight_info_tab(l_count).unit_amount); -- OTM R12
3332:
3333: IF l_debug_on THEN
3334: WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_UTIL_VALIDATE.Validate_Currency:',l_rs);
3335: END IF;
3336: -- Handle error here !!!
3337: -- ECO 5008405 handle here !!!
3338: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3337: -- ECO 5008405 handle here !!!
3338: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3339: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3340: IF l_debug_on THEN
3341: WSH_DEBUG_SV.log(l_module_name, 'Validate Currency Failed: l_return_status', l_return_status);
3342: END IF;
3343: GOTO trip_error;
3344: ELSE -- OTM R12
3345: IF l_debug_on THEN
3342: END IF;
3343: GOTO trip_error;
3344: ELSE -- OTM R12
3345: IF l_debug_on THEN
3346: WSH_DEBUG_SV.log(l_module_name, 'Validate Currency result: x_adjusted_amount : ', l_freight_info_tab(l_count).unit_amount);
3347: END IF;
3348: END IF;
3349: END IF;--}
3350:
3366: -- OTM R12 Why was 11510 using total_amount here
3367: -- validated unit_amount above
3368:
3369: IF l_debug_on THEN
3370: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling GL_CURRENCY_API.convert_uom:');
3371: WSH_DEBUG_SV.logmsg(l_module_name, 'Currency Code:'||l_freight_info_tab(l_count).currency_code);
3372: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3373: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3374: -- OTM R12 print unit here as total is not being used
3367: -- validated unit_amount above
3368:
3369: IF l_debug_on THEN
3370: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling GL_CURRENCY_API.convert_uom:');
3371: WSH_DEBUG_SV.logmsg(l_module_name, 'Currency Code:'||l_freight_info_tab(l_count).currency_code);
3372: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3373: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3374: -- OTM R12 print unit here as total is not being used
3375: --WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).total_amount);
3368:
3369: IF l_debug_on THEN
3370: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling GL_CURRENCY_API.convert_uom:');
3371: WSH_DEBUG_SV.logmsg(l_module_name, 'Currency Code:'||l_freight_info_tab(l_count).currency_code);
3372: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3373: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3374: -- OTM R12 print unit here as total is not being used
3375: --WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).total_amount);
3376: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).unit_amount);
3369: IF l_debug_on THEN
3370: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling GL_CURRENCY_API.convert_uom:');
3371: WSH_DEBUG_SV.logmsg(l_module_name, 'Currency Code:'||l_freight_info_tab(l_count).currency_code);
3372: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3373: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3374: -- OTM R12 print unit here as total is not being used
3375: --WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).total_amount);
3376: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).unit_amount);
3377: END IF;
3371: WSH_DEBUG_SV.logmsg(l_module_name, 'Currency Code:'||l_freight_info_tab(l_count).currency_code);
3372: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3373: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3374: -- OTM R12 print unit here as total is not being used
3375: --WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).total_amount);
3376: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).unit_amount);
3377: END IF;
3378:
3379: -- Convert API should be called only when the carrier has been validated above
3372: WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3373: WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3374: -- OTM R12 print unit here as total is not being used
3375: --WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).total_amount);
3376: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).unit_amount);
3377: END IF;
3378:
3379: -- Convert API should be called only when the carrier has been validated above
3380: DECLARE
3429: CLOSE c_get_user_conv_type;
3430: END IF;
3431:
3432: IF l_debug_on THEN
3433: WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred while getting the user currency conversion type');
3434: WSH_DEBUG_SV.log(l_module_name, 'l_currency_conversion_type', l_conversion_type_code);
3435: END IF;
3436: END;
3437: -- end of changes for Bug 5886042
3430: END IF;
3431:
3432: IF l_debug_on THEN
3433: WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred while getting the user currency conversion type');
3434: WSH_DEBUG_SV.log(l_module_name, 'l_currency_conversion_type', l_conversion_type_code);
3435: END IF;
3436: END;
3437: -- end of changes for Bug 5886042
3438:
3442: FND_MESSAGE.SET_TOKEN('CONV_TYPE',l_user_conv_type);-- Bug 5886042
3443: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3444: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3445: IF l_debug_on THEN
3446: WSH_DEBUG_SV.log(l_module_name, 'GL_CURRENCY_API.CONVERT_AMOUNT'
3447: ||' failed no_rate', l_return_status);
3448: END IF;
3449: GOTO trip_error;
3450: WHEN gl_currency_api.invalid_currency THEN
3462: CLOSE c_get_user_conv_type;
3463: END IF;
3464:
3465: IF l_debug_on THEN
3466: WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred while getting the user currency conversion type');
3467: WSH_DEBUG_SV.log(l_module_name, 'l_currency_conversion_type', l_conversion_type_code);
3468: END IF;
3469: END;
3470: -- end of changes for Bug 5886042
3463: END IF;
3464:
3465: IF l_debug_on THEN
3466: WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred while getting the user currency conversion type');
3467: WSH_DEBUG_SV.log(l_module_name, 'l_currency_conversion_type', l_conversion_type_code);
3468: END IF;
3469: END;
3470: -- end of changes for Bug 5886042
3471:
3475: FND_MESSAGE.SET_TOKEN('CONV_TYPE',l_user_conv_type);-- Bug 5886042
3476: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3477: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3478: IF l_debug_on THEN
3479: WSH_DEBUG_SV.log(l_module_name, 'GL_CURRENCY_API.CONVERT_AMOUNT'
3480: ||' failed invalid_currency', l_return_status);
3481: END IF;
3482: GOTO trip_error;
3483:
3484: -- OTM R12 end
3485: WHEN gl_currency_excp THEN
3486: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3487: IF l_debug_on THEN
3488: WSH_DEBUG_SV.log(l_module_name, 'GL_CURRENCY_API.CONVERT_AMOUNT returned NULL :', l_return_status);
3489: END IF;
3490: GOTO trip_error;
3491:
3492: WHEN OTHERS THEN
3491:
3492: WHEN OTHERS THEN
3493: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3494: IF l_debug_on THEN
3495: WSH_DEBUG_SV.log(l_module_name, 'GL_CURRENCY_API.CONVERT_AMOUNT failed:', l_return_status);
3496: END IF;
3497: GOTO trip_error;
3498: END;--}
3499:
3500: l_freight_info_tab(l_count).currency_code := l_carrier_cur_code;
3501: l_freight_info_tab(l_count).unit_amount := l_freight_info_tab(l_count).total_amount;
3502:
3503: IF l_debug_on THEN
3504: WSH_DEBUG_SV.logmsg(l_module_name, 'Converted Amount:'||l_freight_info_tab(l_count).total_amount);
3505: WSH_DEBUG_SV.logmsg(l_module_name, 'New Currency Code:'||l_freight_info_tab(l_count).currency_code);
3506: END IF;
3507:
3508: END IF;--}
3501: l_freight_info_tab(l_count).unit_amount := l_freight_info_tab(l_count).total_amount;
3502:
3503: IF l_debug_on THEN
3504: WSH_DEBUG_SV.logmsg(l_module_name, 'Converted Amount:'||l_freight_info_tab(l_count).total_amount);
3505: WSH_DEBUG_SV.logmsg(l_module_name, 'New Currency Code:'||l_freight_info_tab(l_count).currency_code);
3506: END IF;
3507:
3508: END IF;--}
3509:
3516: l_in_rec.action_code := 'CREATE';
3517: l_in_rec.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
3518:
3519: IF l_debug_on THEN
3520: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_FREIGHT_COSTS_GRP.Create_Update_Freight_Costs');
3521: END IF;
3522:
3523: WSH_FREIGHT_COSTS_GRP.Create_Update_Freight_Costs
3524: (p_api_version_number => 1.0,
3531: x_msg_count => l_msg_count,
3532: x_msg_data => l_msg_data);
3533:
3534: IF l_debug_on THEN
3535: WSH_DEBUG_SV.logmsg(l_module_name, 'Return Status after Create Freight Cost:'||l_rs);
3536: END IF;
3537:
3538: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3539: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3537:
3538: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3539: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3540: IF l_debug_on THEN
3541: WSH_DEBUG_SV.log(l_module_name, 'Create_Update_Freight_Costs Failed: l_return_status', l_return_status);
3542: END IF;
3543: EXIT; -- need to exit out of the parent loop also!!!
3544: -- OTM R12 will never reach this GOTO and GOTO not required as the immediate next section is trip_error
3545: --GOTO trip_error;
3558: END IF;
3559:
3560:
3561: IF l_debug_on THEN
3562: WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Info Table Count:'||l_freight_info_tab.count);
3563: END IF;
3564: END; --}
3565:
3566: -- OTM Dock Door App Sched Proj
3593:
3594: BEGIN
3595: FOR crec in c_stops(l_trip_id) LOOP
3596: IF l_debug_on THEN
3597: WSH_DEBUG_SV.log(l_module_name, 'Locking Deliveries and Details for stop '||crec.stop_id);
3598: END IF;
3599:
3600: l_stop_id_rec := crec.stop_id;
3601: l_loading_sequence := l_loading_sequence + 10;
3606: FETCH c_lock_deliveries_and_details BULK COLLECT INTO l_del_tab, l_del_det_tab;
3607:
3608: IF l_del_tab.COUNT > 0 THEN
3609: IF l_debug_on THEN
3610: WSH_DEBUG_SV.log(l_module_name, 'Updating Deliveries and Details With Loading Sequence '||l_loading_sequence);
3611: END IF;
3612:
3613: FORALL i in l_del_tab.FIRST..l_del_tab.LAST
3614: UPDATE wsh_new_deliveries
3637: IF c_lock_deliveries_and_details%ISOPEN then
3638: CLOSE c_lock_deliveries_and_details;
3639: END IF;
3640: IF l_debug_on THEN
3641: WSH_DEBUG_SV.log(l_module_name, 'Unable to lock Deliveries/Details for drop off stop '||l_stop_id_rec);
3642: WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
3643: END IF;
3644: GOTO trip_error;
3645: END;
3638: CLOSE c_lock_deliveries_and_details;
3639: END IF;
3640: IF l_debug_on THEN
3641: WSH_DEBUG_SV.log(l_module_name, 'Unable to lock Deliveries/Details for drop off stop '||l_stop_id_rec);
3642: WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
3643: END IF;
3644: GOTO trip_error;
3645: END;
3646:
3652: l_message VARCHAR2(2000);
3653: BEGIN
3654: IF (l_dock_appt_tab.COUNT > 0) THEN --{
3655: IF l_debug_on THEN
3656: WSH_DEBUG_SV.log(l_module_name, 'Calling wms_dock_appointments_pub.OTM_Dock_Appointment',WSH_DEBUG_SV.C_PROC_LEVEL);
3657: END IF;
3658: -- Calling the WMS API with the Dock Door App Sched Info.
3659: wms_dock_appointments_pub.OTM_Dock_Appointment(p_dock_appt_tab => l_dock_appt_tab,
3660: x_return_status => l_rs,
3660: x_return_status => l_rs,
3661: x_msg_count => l_msg_count,
3662: x_msg_data => l_msg_data);
3663: IF l_debug_on THEN
3664: WSH_DEBUG_SV.log(l_module_name, 'wms_dock_appointments_pub.OTM_Dock_Appointment: Return Status', l_rs);
3665: END IF;
3666: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3667: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3668: FOR l_err_msg IN 1..l_msg_count LOOP
3668: FOR l_err_msg IN 1..l_msg_count LOOP
3669: l_message := fnd_msg_pub.get(l_err_msg,'F');
3670: l_message := replace(l_message,chr(0),' ');
3671: IF l_debug_on THEN
3672: WSH_DEBUG_SV.log(l_module_name, 'ERROR: '|| l_message);
3673: END IF;
3674: END LOOP;
3675: fnd_msg_pub.delete_msg();
3676: FND_MESSAGE.SET_NAME('WSH','WSH_DOCK_SCHED_ERROR');
3681: EXCEPTION
3682: WHEN OTHERS THEN
3683: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3684: IF l_debug_on THEN
3685: WSH_DEBUG_SV.log(l_module_name, 'Error while calling wms_dock_appointments_pub.OTM_Dock_Appointment' || SQLERRM);
3686: END IF;
3687: GOTO trip_error;
3688: END;
3689: --
3692: <
3693:
3694: -- 4j. Final Step : error logging, cleaning up interface table
3695: IF l_debug_on THEN
3696: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3697: WSH_DEBUG_SV.logmsg(l_module_name,'4h. DATA CLEANUP FOR THE TRIP');
3698: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3699: END IF;
3700:
3693:
3694: -- 4j. Final Step : error logging, cleaning up interface table
3695: IF l_debug_on THEN
3696: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3697: WSH_DEBUG_SV.logmsg(l_module_name,'4h. DATA CLEANUP FOR THE TRIP');
3698: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3699: END IF;
3700:
3701: DECLARE
3694: -- 4j. Final Step : error logging, cleaning up interface table
3695: IF l_debug_on THEN
3696: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3697: WSH_DEBUG_SV.logmsg(l_module_name,'4h. DATA CLEANUP FOR THE TRIP');
3698: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3699: END IF;
3700:
3701: DECLARE
3702: l_rs VARCHAR2(1);
3709:
3710: BEGIN--{
3711:
3712: IF l_debug_on THEN
3713: WSH_DEBUG_SV.logmsg(l_module_name,'Status here:'||l_return_status );
3714: END IF;
3715:
3716: -- Based on errors for each trip
3717: -- Status of T is for special case of Invalid Location for Trip Stop
3720: -- Interface tables
3721: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,'T') THEN --{
3722: --IF l_errors_tab.count > 0 THEN
3723: IF l_debug_on THEN
3724: WSH_DEBUG_SV.logmsg(l_module_name,'Rollback.....' );
3725: END IF;
3726:
3727: ROLLBACK TO process_single_trip;
3728:
3731: -- so no need to count and conditionally set error/warning
3732: l_completion_status := 'WARNING';
3733:
3734: IF l_debug_on THEN
3735: WSH_DEBUG_SV.logmsg(l_module_name,'Set Completion to WARNING');
3736: WSH_DEBUG_SV.logmsg(l_module_name,'Before Inserting in Interface error,count:'||l_dleg_local_tab.count );
3737: END IF;
3738:
3739: IF l_return_status = 'T' THEN--{
3732: l_completion_status := 'WARNING';
3733:
3734: IF l_debug_on THEN
3735: WSH_DEBUG_SV.logmsg(l_module_name,'Set Completion to WARNING');
3736: WSH_DEBUG_SV.logmsg(l_module_name,'Before Inserting in Interface error,count:'||l_dleg_local_tab.count );
3737: END IF;
3738:
3739: IF l_return_status = 'T' THEN--{
3740: IF l_debug_on THEN
3737: END IF;
3738:
3739: IF l_return_status = 'T' THEN--{
3740: IF l_debug_on THEN
3741: WSH_DEBUG_SV.logmsg(l_module_name,'Before Purging the Interface Data' );
3742: END IF;
3743:
3744: -- Need to purge the data in interface tables for invalid stop location
3745: -- Also, clean up existing Freight cost and Exceptions in EBS
3750: p_exception_action => 'PURGE',
3751: x_return_status => l_rs);
3752:
3753: IF l_debug_on THEN
3754: WSH_DEBUG_SV.logmsg(l_module_name,'After Purging the Interface Data:'||l_rs );
3755: END IF;
3756:
3757: -- OTM R12
3758: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3757: -- OTM R12
3758: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3759: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3760: IF l_debug_on THEN
3761: WSH_DEBUG_SV.log(l_module_name, 'purge_interface_data Failed');
3762: END IF;
3763: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3764: END IF;
3765: -- OTM R12
3782: x_errors_tab => l_errors_tab,
3783: x_return_status => l_rs);
3784:
3785: IF l_debug_on THEN
3786: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Stamp_Interface_error'||l_rs);
3787: END IF;
3788:
3789: -- OTM R12
3790: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3789: -- OTM R12
3790: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3791: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3792: IF l_debug_on THEN
3793: WSH_DEBUG_SV.log(l_module_name, 'stamp_interface_error Failed');
3794: END IF;
3795: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3796: END IF;
3797: -- OTM R12
3798:
3799: END IF;--} -- l_return_status = E
3800:
3801: IF l_debug_on THEN
3802: WSH_DEBUG_SV.logmsg(l_module_name,'Now Logging Error Exceptions' );
3803: END IF;
3804:
3805: -- Log error level exception for all deliveries, use l_dleg_local_tab
3806: -- to find deliveries which are on this trip
3832: p_exception_name => l_exception_name, -- 'WSH_OTM_SHIPMENT_ERROR',
3833: p_message => substrb(l_exception_message,1,2000),
3834: p_delivery_id => l_dleg_local_tab(rec).delivery_id);
3835: IF l_debug_on THEN
3836: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Log_exception'||l_rs);
3837: END IF;
3838:
3839: -- OTM R12
3840: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3839: -- OTM R12
3840: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3841: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3842: IF l_debug_on THEN
3843: WSH_DEBUG_SV.log(l_module_name, 'WSH_XC_UTIL.log_exception Failed');
3844: END IF;
3845: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3846: END IF;
3847: -- OTM R12
3852:
3853: ELSE -- l_return_status <> 'E' --} --{
3854:
3855: IF l_debug_on THEN
3856: WSH_DEBUG_SV.logmsg(l_module_name,'Success; Now Setting Interface flag to ANSWER RECEIVED' );
3857: END IF;
3858:
3859: -- l_dlvy_id_tab only has successfully processed deliveries
3860: -- l_dlvy_id_tab was used in freight_costs section also, make sure it is
3865: -- to the original value
3866: -- OTM R12 Use update_tms_interface_flag API
3867:
3868: IF l_debug_on THEN
3869: WSH_DEBUG_SV.logmsg(l_module_name,'Before Logging Information Only Exceptions' );
3870: END IF;
3871:
3872: -- All the records in l_dleg_local_tab should have been processed
3873: -- Count should be identical in l_dleg_local_tab and l_dlvy_id_tab
3896: p_action => 'CLOSED'
3897: );
3898:
3899: IF l_debug_on THEN
3900: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after WSH_XC_UTIL.Purge' || l_rs);
3901: END IF;
3902: -- OTM R12
3903: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3904: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3902: -- OTM R12
3903: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3904: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3905: IF l_debug_on THEN
3906: WSH_DEBUG_SV.log(l_module_name, 'WSH_XC_UTIL.Purge Failed');
3907: END IF;
3908: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3909: END IF;
3910:
3924:
3925: END IF;
3926:
3927: IF l_debug_on THEN
3928: WSH_DEBUG_SV.logmsg(l_module_name,'Before Purging the Interface Data' );
3929: END IF;
3930:
3931: -- Need to purge the data in interface tables after successful processing
3932: -- p_exception_action is 'CLOSE' to close specific exceptions logged for the
3938: p_exception_action => 'CLOSE', --'PURGE' is called by Arindam
3939: x_return_status => l_rs);
3940:
3941: IF l_debug_on THEN
3942: WSH_DEBUG_SV.logmsg(l_module_name,'After Purging the Interface Data:'||l_rs );
3943: END IF;
3944:
3945: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3946: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3944:
3945: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3946: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3947: IF l_debug_on THEN
3948: WSH_DEBUG_SV.log(l_module_name, 'purge_interface_data Failed');
3949: END IF;
3950: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3951: END IF;
3952:
3961: --=======================================================================
3962:
3963: -- 5. Final Step : error logging, cleaning up interface table
3964: IF l_debug_on THEN
3965: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3966: WSH_DEBUG_SV.logmsg(l_module_name,'5. UNASSIGN DELIVERIES FROM TRIP');
3967: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3968: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be Unassigned :'||l_unassigned_delivery_id_tab.count);
3969: WSH_DEBUG_SV.logmsg(l_module_name,'Version of Deliveries to be Unassigned :'||l_unassigned_dlvy_version_tab.count);
3962:
3963: -- 5. Final Step : error logging, cleaning up interface table
3964: IF l_debug_on THEN
3965: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3966: WSH_DEBUG_SV.logmsg(l_module_name,'5. UNASSIGN DELIVERIES FROM TRIP');
3967: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3968: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be Unassigned :'||l_unassigned_delivery_id_tab.count);
3969: WSH_DEBUG_SV.logmsg(l_module_name,'Version of Deliveries to be Unassigned :'||l_unassigned_dlvy_version_tab.count);
3970: END IF;
3963: -- 5. Final Step : error logging, cleaning up interface table
3964: IF l_debug_on THEN
3965: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3966: WSH_DEBUG_SV.logmsg(l_module_name,'5. UNASSIGN DELIVERIES FROM TRIP');
3967: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3968: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be Unassigned :'||l_unassigned_delivery_id_tab.count);
3969: WSH_DEBUG_SV.logmsg(l_module_name,'Version of Deliveries to be Unassigned :'||l_unassigned_dlvy_version_tab.count);
3970: END IF;
3971: IF l_unassigned_delivery_id_tab.count > 0 THEN--{
3964: IF l_debug_on THEN
3965: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3966: WSH_DEBUG_SV.logmsg(l_module_name,'5. UNASSIGN DELIVERIES FROM TRIP');
3967: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3968: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be Unassigned :'||l_unassigned_delivery_id_tab.count);
3969: WSH_DEBUG_SV.logmsg(l_module_name,'Version of Deliveries to be Unassigned :'||l_unassigned_dlvy_version_tab.count);
3970: END IF;
3971: IF l_unassigned_delivery_id_tab.count > 0 THEN--{
3972: l_unassigned_del_index := l_unassigned_delivery_id_tab.FIRST;
3965: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3966: WSH_DEBUG_SV.logmsg(l_module_name,'5. UNASSIGN DELIVERIES FROM TRIP');
3967: WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3968: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be Unassigned :'||l_unassigned_delivery_id_tab.count);
3969: WSH_DEBUG_SV.logmsg(l_module_name,'Version of Deliveries to be Unassigned :'||l_unassigned_dlvy_version_tab.count);
3970: END IF;
3971: IF l_unassigned_delivery_id_tab.count > 0 THEN--{
3972: l_unassigned_del_index := l_unassigned_delivery_id_tab.FIRST;
3973: WHILE (l_unassigned_del_index IS NOT NULL )
3974: LOOP--{
3975: --glog proj, getting initial value of the delivery(ie. interface_flag)
3976: l_delivery_info_tab.DELETE;
3977: IF l_debug_on THEN
3978: WSH_DEBUG_SV.log(l_module_name, 'l_unassigned_delivery_id_tab('||l_unassigned_del_index||')='||
3979: l_unassigned_delivery_id_tab(l_unassigned_del_index));
3980:
3981: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD',WSH_DEBUG_SV.C_PROC_LEVEL);
3982: END IF;
3977: IF l_debug_on THEN
3978: WSH_DEBUG_SV.log(l_module_name, 'l_unassigned_delivery_id_tab('||l_unassigned_del_index||')='||
3979: l_unassigned_delivery_id_tab(l_unassigned_del_index));
3980:
3981: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD',WSH_DEBUG_SV.C_PROC_LEVEL);
3982: END IF;
3983:
3984: WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD(p_delivery_id => l_unassigned_delivery_id_tab(l_unassigned_del_index),
3985: x_delivery_rec => l_delivery_info,
3988: -- OTM R12
3989: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3990: --l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3991: IF l_debug_on THEN
3992: WSH_DEBUG_SV.log(l_module_name, 'WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD Failed');
3993: END IF;
3994: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3995: END IF;
3996: -- OTM R12
4015:
4016: END LOOP;--}
4017:
4018: IF l_debug_on THEN
4019: WSH_DEBUG_SV.log(l_module_name,'Delivery Info Tab Count:'||l_delivery_info_tab.count);
4020: WSH_DEBUG_SV.log(l_module_name,'New Interface Flag Tab Count:'||l_new_interface_flag_tab.count);
4021: END IF;
4022:
4023: END IF; --}
4016: END LOOP;--}
4017:
4018: IF l_debug_on THEN
4019: WSH_DEBUG_SV.log(l_module_name,'Delivery Info Tab Count:'||l_delivery_info_tab.count);
4020: WSH_DEBUG_SV.log(l_module_name,'New Interface Flag Tab Count:'||l_new_interface_flag_tab.count);
4021: END IF;
4022:
4023: END IF; --}
4024:
4029: p_tms_interface_flag_tab => l_upd_dlvy_tms_tab,
4030: x_return_status => l_return_status);
4031:
4032: IF l_debug_on THEN
4033: WSH_DEBUG_SV.log(l_module_name, 'update_tms_interface_flag : l_return_status : ', l_return_status);
4034: END IF;
4035: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4036: --l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4037: ROLLBACK TO process_group;
4039: --l_completion_status := 'ERROR';
4040: ELSE
4041:
4042: IF l_debug_on THEN
4043: WSH_DEBUG_SV.logmsg(l_module_name,'Now Committing for the group....');
4044: END IF;
4045:
4046: COMMIT;
4047:
4048: END IF;
4049: -- OTM R12
4050:
4051: IF l_debug_on THEN
4052: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4053: WSH_DEBUG_SV.logmsg(l_module_name,'Completion Status'||l_completion_status);
4054: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Error Count'||l_error_trips);
4055: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Success Count'||l_success_trips);
4056: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4049: -- OTM R12
4050:
4051: IF l_debug_on THEN
4052: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4053: WSH_DEBUG_SV.logmsg(l_module_name,'Completion Status'||l_completion_status);
4054: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Error Count'||l_error_trips);
4055: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Success Count'||l_success_trips);
4056: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4057: END IF;
4050:
4051: IF l_debug_on THEN
4052: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4053: WSH_DEBUG_SV.logmsg(l_module_name,'Completion Status'||l_completion_status);
4054: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Error Count'||l_error_trips);
4055: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Success Count'||l_success_trips);
4056: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4057: END IF;
4058:
4051: IF l_debug_on THEN
4052: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4053: WSH_DEBUG_SV.logmsg(l_module_name,'Completion Status'||l_completion_status);
4054: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Error Count'||l_error_trips);
4055: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Success Count'||l_success_trips);
4056: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4057: END IF;
4058:
4059: IF l_completion_status = 'WARNING' THEN
4052: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4053: WSH_DEBUG_SV.logmsg(l_module_name,'Completion Status'||l_completion_status);
4054: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Error Count'||l_error_trips);
4055: WSH_DEBUG_SV.logmsg(l_module_name,'Trip Success Count'||l_success_trips);
4056: WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4057: END IF;
4058:
4059: IF l_completion_status = 'WARNING' THEN
4060: errbuf := 'Atleast one trip was not Interfaced';
4073: -- OTM R12
4074: END IF;
4075:
4076: IF l_debug_on THEN
4077: WSH_DEBUG_SV.log(l_module_name,'ERRBUF',errbuf);
4078: WSH_DEBUG_SV.log(l_module_name,'RETCODE',retcode);
4079: WSH_DEBUG_SV.pop(l_module_name);
4080: END IF;
4081:
4074: END IF;
4075:
4076: IF l_debug_on THEN
4077: WSH_DEBUG_SV.log(l_module_name,'ERRBUF',errbuf);
4078: WSH_DEBUG_SV.log(l_module_name,'RETCODE',retcode);
4079: WSH_DEBUG_SV.pop(l_module_name);
4080: END IF;
4081:
4082: EXCEPTION
4075:
4076: IF l_debug_on THEN
4077: WSH_DEBUG_SV.log(l_module_name,'ERRBUF',errbuf);
4078: WSH_DEBUG_SV.log(l_module_name,'RETCODE',retcode);
4079: WSH_DEBUG_SV.pop(l_module_name);
4080: END IF;
4081:
4082: EXCEPTION
4083: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4083: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4084: ROLLBACK TO process_group;
4085: --
4086: IF l_debug_on THEN
4087: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4088: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4089: END IF;
4090: --
4091: -- Always concurrent request,no online option
4084: ROLLBACK TO process_group;
4085: --
4086: IF l_debug_on THEN
4087: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4088: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4089: END IF;
4090: --
4091: -- Always concurrent request,no online option
4092: l_ret_code := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
4166: WSH_UTIL_CORE.DEFAULT_HANDLER(
4167: 'WSH_TMS_RELEASE.RELEASE_PLANNED_SHIPMENT',
4168: l_module_name);
4169: IF l_debug_on THEN
4170: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4171: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4172: END IF;
4173: END release_planned_shipment;
4174:
4167: 'WSH_TMS_RELEASE.RELEASE_PLANNED_SHIPMENT',
4168: l_module_name);
4169: IF l_debug_on THEN
4170: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4171: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4172: END IF;
4173: END release_planned_shipment;
4174:
4175: --=========================================================================
4222:
4223: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4224: --
4225: IF l_debug_on IS NULL THEN
4226: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4227: END IF;
4228:
4229: IF l_debug_on THEN
4230: WSH_DEBUG_SV.push(l_module_name);
4226: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4227: END IF;
4228:
4229: IF l_debug_on THEN
4230: WSH_DEBUG_SV.push(l_module_name);
4231: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4232: WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', p_trip_interface_id);
4233: WSH_DEBUG_SV.log(l_module_name,'p_commit_flag:', p_commit_flag);
4234: WSH_DEBUG_SV.log(l_module_name,'p_exception_action:', p_exception_action);
4227: END IF;
4228:
4229: IF l_debug_on THEN
4230: WSH_DEBUG_SV.push(l_module_name);
4231: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4232: WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', p_trip_interface_id);
4233: WSH_DEBUG_SV.log(l_module_name,'p_commit_flag:', p_commit_flag);
4234: WSH_DEBUG_SV.log(l_module_name,'p_exception_action:', p_exception_action);
4235: END IF;
4228:
4229: IF l_debug_on THEN
4230: WSH_DEBUG_SV.push(l_module_name);
4231: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4232: WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', p_trip_interface_id);
4233: WSH_DEBUG_SV.log(l_module_name,'p_commit_flag:', p_commit_flag);
4234: WSH_DEBUG_SV.log(l_module_name,'p_exception_action:', p_exception_action);
4235: END IF;
4236:
4229: IF l_debug_on THEN
4230: WSH_DEBUG_SV.push(l_module_name);
4231: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4232: WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', p_trip_interface_id);
4233: WSH_DEBUG_SV.log(l_module_name,'p_commit_flag:', p_commit_flag);
4234: WSH_DEBUG_SV.log(l_module_name,'p_exception_action:', p_exception_action);
4235: END IF;
4236:
4237: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4230: WSH_DEBUG_SV.push(l_module_name);
4231: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4232: WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', p_trip_interface_id);
4233: WSH_DEBUG_SV.log(l_module_name,'p_commit_flag:', p_commit_flag);
4234: WSH_DEBUG_SV.log(l_module_name,'p_exception_action:', p_exception_action);
4235: END IF;
4236:
4237: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4238:
4248: l_trip_interface_id_tab(1) := p_trip_interface_id;
4249: END IF;
4250:
4251: IF l_debug_on THEN
4252: WSH_DEBUG_SV.log(l_module_name,'Count of Trip Interface Ids:', l_trip_interface_id_tab.count);
4253: END IF;
4254:
4255: IF l_debug_on THEN
4256: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Freight Costs Interface Data');
4252: WSH_DEBUG_SV.log(l_module_name,'Count of Trip Interface Ids:', l_trip_interface_id_tab.count);
4253: END IF;
4254:
4255: IF l_debug_on THEN
4256: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Freight Costs Interface Data');
4257: END IF;
4258:
4259: IF l_trip_interface_id_tab.count > 0 THEN--{
4260:
4258:
4259: IF l_trip_interface_id_tab.count > 0 THEN--{
4260:
4261: IF l_debug_on THEN
4262: WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', l_trip_interface_id_tab(l_trip_interface_id_tab.FIRST));
4263: END IF;
4264:
4265: -- Purge wsh_freight_cost_interface
4266: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4278:
4279: IF p_exception_action = 'PURGE' THEN--{
4280: -- Purge exceptions logged for the deliveries
4281: IF l_debug_on THEN
4282: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Exceptions');
4283: END IF;
4284: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4285: DELETE from WSH_EXCEPTIONS we
4286: WHERE we.delivery_id in (
4296:
4297: ELSIF p_exception_action = 'CLOSE' THEN
4298: -- Close exceptions logged for the deliveries
4299: IF l_debug_on THEN
4300: WSH_DEBUG_SV.logmsg(l_module_name,'Start Closing Exceptions');
4301: END IF;
4302: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4303: UPDATE WSH_EXCEPTIONS we
4304: SET status = 'CLOSED'
4319: -- table_name can be WSH_NEW_DEL_INTERFACE, WSH_TRIP_STOPS_INTERFACE,
4320: -- WSH_TRIPS_INTERFACE
4321:
4322: IF l_debug_on THEN
4323: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Interface Errors Data for delivery');
4324: END IF;
4325: -- Delete errors logged for deliveries
4326: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4327: Delete from wsh_interface_errors wie
4336: and wdli.interface_action_code = G_TMS_RELEASE_CODE)
4337: and wie.interface_action_code = G_TMS_RELEASE_CODE;
4338:
4339: IF l_debug_on THEN
4340: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Interface Errors Data for Trip Stops');
4341: END IF;
4342: -- Delete errors logged for trip stops
4343: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4344: Delete from wsh_interface_errors wie
4351: and wie.interface_action_code = G_TMS_RELEASE_CODE;
4352:
4353: -- Delete errors logged for trips
4354: IF l_debug_on THEN
4355: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Interface Errors Data for Trips');
4356: END IF;
4357: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4358: Delete from wsh_interface_errors wie
4359: where wie.interface_table_name = 'WSH_TRIPS_INTERFACE'
4361: and wie.interface_action_code = G_TMS_RELEASE_CODE;
4362:
4363: -- Purge wsh_new_del_interface
4364: IF l_debug_on THEN
4365: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Deliveries Interface Data');
4366: END IF;
4367: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4368: Delete from wsh_new_del_interface wndi
4369: where wndi.delivery_interface_id in (
4377: and wndi.interface_action_code = G_TMS_RELEASE_CODE;
4378:
4379: -- Purge wsh_del_legs_interface
4380: IF l_debug_on THEN
4381: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Delivery Legs Interface Data');
4382: END IF;
4383: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4384: Delete from wsh_del_legs_interface wdli
4385: where wdli.pick_up_stop_interface_id in (
4390: and wdli.interface_action_code = G_TMS_RELEASE_CODE;
4391:
4392: -- Purge wsh_trip_stops_interface
4393: IF l_debug_on THEN
4394: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Trip Stops Interface Data');
4395: END IF;
4396: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4397: Delete from wsh_trip_stops_interface wtsi
4398: where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
4401: -- Purge wsh_trips_interface
4402: -- (Trips have to be the last entity interface table purged
4403: -- because this is the only entity interface table having TP_PLAN_NAME.)
4404: IF l_debug_on THEN
4405: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Trips Interface Data');
4406: END IF;
4407: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4408: Delete from wsh_trips_interface wti
4409: where wti.trip_interface_id = l_trip_interface_id_tab(i)
4412: END IF;--}
4413:
4414: IF p_commit_flag = 'Y' THEN
4415: IF l_debug_on THEN
4416: WSH_DEBUG_SV.logmsg(l_module_name,'Committing....');
4417: END IF;
4418: commit;
4419: END IF;
4420:
4418: commit;
4419: END IF;
4420:
4421: IF l_debug_on THEN
4422: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4423: WSH_DEBUG_SV.pop(l_module_name);
4424: END IF;
4425:
4426: EXCEPTION
4419: END IF;
4420:
4421: IF l_debug_on THEN
4422: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4423: WSH_DEBUG_SV.pop(l_module_name);
4424: END IF;
4425:
4426: EXCEPTION
4427: WHEN OTHERS THEN
4433: WSH_UTIL_CORE.DEFAULT_HANDLER(
4434: 'WSH_TMS_RELEASE.PURGE_INTERFACE_DATA',
4435: l_module_name);
4436: IF l_debug_on THEN
4437: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4438: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4439: END IF;
4440:
4441: END purge_interface_data;
4434: 'WSH_TMS_RELEASE.PURGE_INTERFACE_DATA',
4435: l_module_name);
4436: IF l_debug_on THEN
4437: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4438: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4439: END IF;
4440:
4441: END purge_interface_data;
4442:
4516:
4517: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4518: --
4519: IF l_debug_on IS NULL THEN
4520: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4521: END IF;
4522:
4523: IF l_debug_on THEN
4524: WSH_DEBUG_SV.push(l_module_name);
4520: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4521: END IF;
4522:
4523: IF l_debug_on THEN
4524: WSH_DEBUG_SV.push(l_module_name);
4525: WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4526: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4527: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4528: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4521: END IF;
4522:
4523: IF l_debug_on THEN
4524: WSH_DEBUG_SV.push(l_module_name);
4525: WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4526: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4527: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4528: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4529: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4522:
4523: IF l_debug_on THEN
4524: WSH_DEBUG_SV.push(l_module_name);
4525: WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4526: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4527: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4528: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4529: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4523: IF l_debug_on THEN
4524: WSH_DEBUG_SV.push(l_module_name);
4525: WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4526: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4527: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4528: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4529: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4531: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4524: WSH_DEBUG_SV.push(l_module_name);
4525: WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4526: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4527: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4528: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4529: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4531: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4532: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4525: WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4526: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4527: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4528: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4529: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4531: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4532: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4533: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4526: WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4527: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4528: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4529: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4531: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4532: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4533: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4534: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4527: WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4528: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4529: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4531: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4532: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4533: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4534: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4535: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4528: WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4529: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4531: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4532: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4533: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4534: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4535: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4536: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4529: WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4531: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4532: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4533: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4534: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4535: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4536: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4537: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4530: WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4531: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4532: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4533: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4534: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4535: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4536: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4537: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4538: WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4531: WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4532: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4533: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4534: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4535: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4536: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4537: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4538: WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4539: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Input Deliveries:'||p_dleg_tab.count);
4532: WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4533: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4534: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4535: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4536: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4537: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4538: WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4539: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Input Deliveries:'||p_dleg_tab.count);
4540: END IF;
4533: WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4534: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4535: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4536: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4537: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4538: WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4539: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Input Deliveries:'||p_dleg_tab.count);
4540: END IF;
4541:
4534: WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4535: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4536: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4537: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4538: WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4539: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Input Deliveries:'||p_dleg_tab.count);
4540: END IF;
4541:
4542: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4535: WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4536: WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4537: WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4538: WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4539: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Input Deliveries:'||p_dleg_tab.count);
4540: END IF;
4541:
4542: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4543:
4579: END IF; --}
4580:
4581: c := FND_MSG_PUB.count_msg;
4582: IF l_debug_on THEN
4583: WSH_DEBUG_SV.log(l_module_name,'COUNT--',c);
4584: END IF;
4585:
4586: FOR i in 1..c LOOP--{
4587: --FOR i in REVERSE(c)..1 LOOP--{
4586: FOR i in 1..c LOOP--{
4587: --FOR i in REVERSE(c)..1 LOOP--{
4588:
4589: IF l_debug_on THEN
4590: WSH_DEBUG_SV.logmsg(l_module_name,'C:'||i);
4591: END IF;
4592:
4593: FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
4594: p_msg_index => i,
4596: p_msg_index_out => l_index_out);
4597:
4598: -- Concatenate the message
4599: IF l_debug_on THEN
4600: WSH_DEBUG_SV.logmsg(l_module_name,'l_buffer:'||l_buffer);
4601: END IF;
4602: l_message := l_message||l_buffer;
4603:
4604: -- l_index := l_index + 1;
4608:
4609: l_message_name := 'WSH_OTM_GENERIC';
4610:
4611: IF l_debug_on THEN
4612: WSH_DEBUG_SV.log(l_module_name,'l_index--',l_index);
4613: WSH_DEBUG_SV.logmsg(l_module_name,'l_message:'||substr(l_message,1,200));
4614: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,201,200));
4615: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,401,200));
4616: END IF;
4609: l_message_name := 'WSH_OTM_GENERIC';
4610:
4611: IF l_debug_on THEN
4612: WSH_DEBUG_SV.log(l_module_name,'l_index--',l_index);
4613: WSH_DEBUG_SV.logmsg(l_module_name,'l_message:'||substr(l_message,1,200));
4614: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,201,200));
4615: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,401,200));
4616: END IF;
4617:
4610:
4611: IF l_debug_on THEN
4612: WSH_DEBUG_SV.log(l_module_name,'l_index--',l_index);
4613: WSH_DEBUG_SV.logmsg(l_module_name,'l_message:'||substr(l_message,1,200));
4614: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,201,200));
4615: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,401,200));
4616: END IF;
4617:
4618:
4611: IF l_debug_on THEN
4612: WSH_DEBUG_SV.log(l_module_name,'l_index--',l_index);
4613: WSH_DEBUG_SV.logmsg(l_module_name,'l_message:'||substr(l_message,1,200));
4614: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,201,200));
4615: WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,401,200));
4616: END IF;
4617:
4618:
4619: -- Populate the Delivery_interface_id
4653:
4654: END IF;--}
4655:
4656: IF l_debug_on THEN
4657: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4658: WSH_DEBUG_SV.pop(l_module_name);
4659: END IF;
4660:
4661: EXCEPTION
4654: END IF;--}
4655:
4656: IF l_debug_on THEN
4657: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4658: WSH_DEBUG_SV.pop(l_module_name);
4659: END IF;
4660:
4661: EXCEPTION
4662: WHEN OTHERS THEN
4664: WSH_UTIL_CORE.DEFAULT_HANDLER(
4665: 'WSH_TMS_RELEASE.STAMP_INTERFACE_ERROR',
4666: l_module_name);
4667: IF l_debug_on THEN
4668: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4669: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4670: END IF;
4671: END stamp_interface_error;
4672:
4665: 'WSH_TMS_RELEASE.STAMP_INTERFACE_ERROR',
4666: l_module_name);
4667: IF l_debug_on THEN
4668: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4669: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4670: END IF;
4671: END stamp_interface_error;
4672:
4673: --====================================================
4726:
4727: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4728: --
4729: IF l_debug_on IS NULL THEN
4730: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4731: END IF;
4732:
4733: IF l_debug_on THEN
4734: WSH_DEBUG_SV.push(l_module_name);
4730: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4731: END IF;
4732:
4733: IF l_debug_on THEN
4734: WSH_DEBUG_SV.push(l_module_name);
4735: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4736: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4737: END IF;
4738:
4731: END IF;
4732:
4733: IF l_debug_on THEN
4734: WSH_DEBUG_SV.push(l_module_name);
4735: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4736: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4737: END IF;
4738:
4739: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4732:
4733: IF l_debug_on THEN
4734: WSH_DEBUG_SV.push(l_module_name);
4735: WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4736: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4737: END IF;
4738:
4739: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4740:
4762: END LOOP;
4763: END IF;--}
4764:
4765: IF l_debug_on THEN
4766: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_id_tab.count', x_delivery_id_tab.count);
4767: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_tab.count', x_delivery_tab.count);
4768: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4769: WSH_DEBUG_SV.pop(l_module_name);
4770: END IF;
4763: END IF;--}
4764:
4765: IF l_debug_on THEN
4766: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_id_tab.count', x_delivery_id_tab.count);
4767: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_tab.count', x_delivery_tab.count);
4768: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4769: WSH_DEBUG_SV.pop(l_module_name);
4770: END IF;
4771:
4764:
4765: IF l_debug_on THEN
4766: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_id_tab.count', x_delivery_id_tab.count);
4767: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_tab.count', x_delivery_tab.count);
4768: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4769: WSH_DEBUG_SV.pop(l_module_name);
4770: END IF;
4771:
4772: EXCEPTION
4765: IF l_debug_on THEN
4766: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_id_tab.count', x_delivery_id_tab.count);
4767: WSH_DEBUG_SV.log(l_module_name, 'x_delivery_tab.count', x_delivery_tab.count);
4768: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4769: WSH_DEBUG_SV.pop(l_module_name);
4770: END IF;
4771:
4772: EXCEPTION
4773: WHEN OTHERS THEN
4781: WSH_UTIL_CORE.DEFAULT_HANDLER(
4782: 'WSH_TMS_RELEASE.FIND_DELIVERIES_FOR_TRIP',
4783: l_module_name);
4784: IF l_debug_on THEN
4785: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4786: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4787: END IF;
4788: END find_deliveries_for_trip;
4789:
4782: 'WSH_TMS_RELEASE.FIND_DELIVERIES_FOR_TRIP',
4783: l_module_name);
4784: IF l_debug_on THEN
4785: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4786: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4787: END IF;
4788: END find_deliveries_for_trip;
4789:
4790: --====================================================
4841:
4842: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4843: --
4844: IF l_debug_on IS NULL THEN
4845: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4846: END IF;
4847:
4848: IF l_debug_on THEN
4849: WSH_DEBUG_SV.push(l_module_name);
4845: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4846: END IF;
4847:
4848: IF l_debug_on THEN
4849: WSH_DEBUG_SV.push(l_module_name);
4850: WSH_DEBUG_SV.log(l_module_name,'Input Delivery Count:', p_dleg_tab.count);
4851: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4852: WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Count:', x_unassign_id_tab.count);
4853: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4846: END IF;
4847:
4848: IF l_debug_on THEN
4849: WSH_DEBUG_SV.push(l_module_name);
4850: WSH_DEBUG_SV.log(l_module_name,'Input Delivery Count:', p_dleg_tab.count);
4851: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4852: WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Count:', x_unassign_id_tab.count);
4853: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4854: END IF;
4847:
4848: IF l_debug_on THEN
4849: WSH_DEBUG_SV.push(l_module_name);
4850: WSH_DEBUG_SV.log(l_module_name,'Input Delivery Count:', p_dleg_tab.count);
4851: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4852: WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Count:', x_unassign_id_tab.count);
4853: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4854: END IF;
4855:
4848: IF l_debug_on THEN
4849: WSH_DEBUG_SV.push(l_module_name);
4850: WSH_DEBUG_SV.log(l_module_name,'Input Delivery Count:', p_dleg_tab.count);
4851: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4852: WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Count:', x_unassign_id_tab.count);
4853: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4854: END IF;
4855:
4856: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4849: WSH_DEBUG_SV.push(l_module_name);
4850: WSH_DEBUG_SV.log(l_module_name,'Input Delivery Count:', p_dleg_tab.count);
4851: WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4852: WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Count:', x_unassign_id_tab.count);
4853: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4854: END IF;
4855:
4856: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4857:
4884: ) THEN--{
4885:
4886:
4887: IF l_debug_on THEN
4888: WSH_DEBUG_SV.logmsg(l_module_name, 'Need to UNASSIGN DELIVERY');
4889: END IF;
4890:
4891: -- Cannot do BULK UNASSIGNMENT HERE, as there can be multiple trips and the
4892: -- deliveries could be associated with different trips
4896: l_del_attrs(l_del_attrs.count + 1).delivery_id := p_dleg_tab(dlvy_count).delivery_id;
4897: l_del_attrs(l_del_attrs.count).organization_id := p_dleg_tab(dlvy_count).organization_id;
4898:
4899: IF l_debug_on THEN
4900: WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
4901: END IF;
4902:
4903: l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
4904: l_del_action_prms.action_code := 'UNASSIGN-TRIP';
4916: x_msg_count => l_msg_count,
4917: x_msg_data => l_msg_data);
4918:
4919: IF l_debug_on THEN
4920: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Unassign Delivery'||l_rs);
4921: END IF;
4922: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4923: x_return_status := l_rs;
4924: EXIT;
4937: dleg_rec.dropoff_stop_sequence = p_dleg_tab(dlvy_count).dropoff_stop_sequence)
4938: ) THEN
4939:
4940: IF l_debug_on THEN
4941: WSH_DEBUG_SV.logmsg(l_module_name, 'DELIVERY IS ALREADY ASSIGNED TO TRIP');
4942: END IF;
4943: p_dleg_tab(dlvy_count).processed_flag := 'Y';
4944: END IF;--}
4945: END LOOP;--}
4946:
4947: IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4948: x_return_status := l_rs;
4949: IF l_debug_on THEN
4950: WSH_DEBUG_SV.logmsg(l_module_name, 'Error Processing Delivery id:'||(p_dleg_tab(dlvy_count).delivery_id));
4951: END IF;
4952: EXIT;
4953: END IF;
4954: END LOOP;--}
4954: END LOOP;--}
4955: END IF;
4956:
4957: IF l_debug_on THEN
4958: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery Count:', x_unassign_id_tab.count);
4959: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4960: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4961: WSH_DEBUG_SV.pop(l_module_name);
4962: END IF;
4955: END IF;
4956:
4957: IF l_debug_on THEN
4958: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery Count:', x_unassign_id_tab.count);
4959: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4960: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4961: WSH_DEBUG_SV.pop(l_module_name);
4962: END IF;
4963:
4956:
4957: IF l_debug_on THEN
4958: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery Count:', x_unassign_id_tab.count);
4959: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4960: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4961: WSH_DEBUG_SV.pop(l_module_name);
4962: END IF;
4963:
4964: EXCEPTION
4957: IF l_debug_on THEN
4958: WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery Count:', x_unassign_id_tab.count);
4959: -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4960: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4961: WSH_DEBUG_SV.pop(l_module_name);
4962: END IF;
4963:
4964: EXCEPTION
4965: WHEN OTHERS THEN
4970: WSH_UTIL_CORE.DEFAULT_HANDLER(
4971: 'WSH_TMS_RELEASE.COMPARE_TRIP_FOR_DELIVERIES',
4972: l_module_name);
4973: IF l_debug_on THEN
4974: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4975: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4976: END IF;
4977:
4978: END compare_trip_for_deliveries;
4971: 'WSH_TMS_RELEASE.COMPARE_TRIP_FOR_DELIVERIES',
4972: l_module_name);
4973: IF l_debug_on THEN
4974: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4975: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4976: END IF;
4977:
4978: END compare_trip_for_deliveries;
4979:
5001:
5002: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5003: --
5004: IF l_debug_on IS NULL THEN
5005: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5006: END IF;
5007:
5008: IF l_debug_on THEN
5009: WSH_DEBUG_SV.push(l_module_name);
5005: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5006: END IF;
5007:
5008: IF l_debug_on THEN
5009: WSH_DEBUG_SV.push(l_module_name);
5010: WSH_DEBUG_SV.log(l_module_name,'Group Id:', p_group_id);
5011: END IF;
5012:
5013: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5006: END IF;
5007:
5008: IF l_debug_on THEN
5009: WSH_DEBUG_SV.push(l_module_name);
5010: WSH_DEBUG_SV.log(l_module_name,'Group Id:', p_group_id);
5011: END IF;
5012:
5013: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5014:
5018: argument1 => p_group_id);
5019: -- COMMIT;
5020:
5021: IF l_debug_on THEN
5022: WSH_DEBUG_SV.log(l_module_name, 'Request id:', x_request_id);
5023: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
5024: WSH_DEBUG_SV.pop(l_module_name);
5025: END IF;
5026:
5019: -- COMMIT;
5020:
5021: IF l_debug_on THEN
5022: WSH_DEBUG_SV.log(l_module_name, 'Request id:', x_request_id);
5023: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
5024: WSH_DEBUG_SV.pop(l_module_name);
5025: END IF;
5026:
5027: EXCEPTION
5020:
5021: IF l_debug_on THEN
5022: WSH_DEBUG_SV.log(l_module_name, 'Request id:', x_request_id);
5023: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
5024: WSH_DEBUG_SV.pop(l_module_name);
5025: END IF;
5026:
5027: EXCEPTION
5028: WHEN OTHERS THEN
5031: WSH_UTIL_CORE.DEFAULT_HANDLER(
5032: 'WSH_TMS_RELEASE.LAUNCH_RELEASE_REQUEST',
5033: l_module_name);
5034: IF l_debug_on THEN
5035: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5036: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5037: END IF;
5038:
5039: END launch_release_request;
5032: 'WSH_TMS_RELEASE.LAUNCH_RELEASE_REQUEST',
5033: l_module_name);
5034: IF l_debug_on THEN
5035: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5036: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5037: END IF;
5038:
5039: END launch_release_request;
5040:
5089:
5090: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5091: --
5092: IF l_debug_on IS NULL THEN
5093: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5094: END IF;
5095:
5096: IF l_debug_on THEN
5097: WSH_DEBUG_SV.push(l_module_name);
5093: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5094: END IF;
5095:
5096: IF l_debug_on THEN
5097: WSH_DEBUG_SV.push(l_module_name);
5098: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5099: WSH_DEBUG_SV.log(l_module_name,'Stop Tab Count:', x_stop_tab.count);
5100: END IF;
5101:
5094: END IF;
5095:
5096: IF l_debug_on THEN
5097: WSH_DEBUG_SV.push(l_module_name);
5098: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5099: WSH_DEBUG_SV.log(l_module_name,'Stop Tab Count:', x_stop_tab.count);
5100: END IF;
5101:
5102: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5095:
5096: IF l_debug_on THEN
5097: WSH_DEBUG_SV.push(l_module_name);
5098: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5099: WSH_DEBUG_SV.log(l_module_name,'Stop Tab Count:', x_stop_tab.count);
5100: END IF;
5101:
5102: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5103:
5107: FOR i IN l_stop_tab.FIRST..l_stop_tab.LAST
5108: LOOP--{
5109:
5110: IF l_debug_on THEN
5111: WSH_DEBUG_SV.logmsg(l_module_name, '=====================================');
5112: WSH_DEBUG_SV.logmsg(l_module_name, 'Stop Location:'||l_stop_tab(i).stop_location_id);
5113: END IF;
5114:
5115: IF x_delivery_tab.count = 1 THEN -- Single Delivery in the GC3 Trip--{
5108: LOOP--{
5109:
5110: IF l_debug_on THEN
5111: WSH_DEBUG_SV.logmsg(l_module_name, '=====================================');
5112: WSH_DEBUG_SV.logmsg(l_module_name, 'Stop Location:'||l_stop_tab(i).stop_location_id);
5113: END IF;
5114:
5115: IF x_delivery_tab.count = 1 THEN -- Single Delivery in the GC3 Trip--{
5116: -- Match Stop Location to Delivery Dropoff Location
5118: IF l_dropoff_location_id = to_number(l_stop_tab(i).stop_location_id) THEN--{
5119: -- no action required as Dropoff Location matches incoming stop
5120: --null;
5121: IF l_debug_on THEN
5122: WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Location Matches Stop Location');
5123: END IF;
5124: ELSIF l_dropoff_location_id <> to_number(l_stop_tab(i).stop_location_id) THEN--} --{
5125:
5126: IF l_debug_on THEN
5123: END IF;
5124: ELSIF l_dropoff_location_id <> to_number(l_stop_tab(i).stop_location_id) THEN--} --{
5125:
5126: IF l_debug_on THEN
5127: WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Location Does not match Stop Location');
5128: END IF;
5129:
5130: -- get the physical location corresponding to delivery
5131: -- dropoff location and match with Stop Location
5138: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
5139: THEN
5140: x_return_status := l_return_status;
5141: IF l_debug_on THEN
5142: WSH_DEBUG_SV.log(l_module_name, 'Conversion Failed,l_return_status', l_return_status);
5143: END IF;
5144: EXIT; -- exit out of the stop_tab loop
5145: END IF;
5146:
5144: EXIT; -- exit out of the stop_tab loop
5145: END IF;
5146:
5147: IF l_debug_on THEN
5148: WSH_DEBUG_SV.log(l_module_name, 'Delivery Physical Location Id:'||l_physical_loc_id);
5149: WSH_DEBUG_SV.log(l_module_name, 'Stop Location Id:'||l_stop_tab(i).stop_location_id);
5150: END IF;
5151:
5152: IF l_physical_loc_id IS NOT NULL AND
5145: END IF;
5146:
5147: IF l_debug_on THEN
5148: WSH_DEBUG_SV.log(l_module_name, 'Delivery Physical Location Id:'||l_physical_loc_id);
5149: WSH_DEBUG_SV.log(l_module_name, 'Stop Location Id:'||l_stop_tab(i).stop_location_id);
5150: END IF;
5151:
5152: IF l_physical_loc_id IS NOT NULL AND
5153: to_number(l_stop_tab(i).stop_location_id) = l_physical_loc_id THEN
5151:
5152: IF l_physical_loc_id IS NOT NULL AND
5153: to_number(l_stop_tab(i).stop_location_id) = l_physical_loc_id THEN
5154: IF l_debug_on THEN
5155: WSH_DEBUG_SV.log(l_module_name, 'Delivery Dropoff Dummy Location Matches Incoming Stop Location');
5156: END IF;
5157: -- As there is only 1 delivery involved, create the stop based on the
5158: -- dummy location. Delivery assignment is done based on this location.
5159: -- x_stop_tab matches l_stop_tab, same index
5175: l_dropoff_location_id := to_number(x_delivery_tab(j).ultimate_dropoff_location_id);
5176: IF l_dropoff_location_id = to_number(l_stop_tab(i).stop_location_id) THEN--{
5177: -- no action required as Dropoff Location matches incoming stop
5178: IF l_debug_on THEN
5179: WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Location Matches Stop Location for delivery:'||x_delivery_tab(j).delivery_id);
5180: END IF;
5181:
5182: ELSIF l_dropoff_location_id <> to_number(l_stop_tab(i).stop_location_id) THEN--} --{
5183:
5181:
5182: ELSIF l_dropoff_location_id <> to_number(l_stop_tab(i).stop_location_id) THEN--} --{
5183:
5184: IF l_debug_on THEN
5185: WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Location Does not match Stop Location for delivery:'||x_delivery_tab(j).delivery_id);
5186: END IF;
5187:
5188: -- get the physical location corresponding to delivery
5189: -- dropoff location and match with Stop Location
5196: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
5197: THEN
5198: x_return_status := l_return_status;
5199: IF l_debug_on THEN
5200: WSH_DEBUG_SV.log(l_module_name, 'Conversion Failed,l_return_status', l_return_status);
5201: END IF;
5202: EXIT; -- exit out of the stop_tab loop
5203: END IF;
5204:
5202: EXIT; -- exit out of the stop_tab loop
5203: END IF;
5204:
5205: IF l_debug_on THEN
5206: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery Physical Location Id:'||l_physical_loc_id);
5207: WSH_DEBUG_SV.logmsg(l_module_name, 'Stop Location Id:'||l_stop_tab(i).stop_location_id);
5208: END IF;
5209:
5210: -- The physical location for delivery dropoff location matches the incoming stop
5203: END IF;
5204:
5205: IF l_debug_on THEN
5206: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery Physical Location Id:'||l_physical_loc_id);
5207: WSH_DEBUG_SV.logmsg(l_module_name, 'Stop Location Id:'||l_stop_tab(i).stop_location_id);
5208: END IF;
5209:
5210: -- The physical location for delivery dropoff location matches the incoming stop
5211: -- location.
5211: -- location.
5212: IF l_physical_loc_id IS NOT NULL AND
5213: to_number(l_stop_tab(i).stop_location_id) = l_physical_loc_id THEN--{
5214: IF l_debug_on THEN
5215: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery Dropoff Dummy Location Matches Incoming Stop Location');
5216: END IF;
5217:
5218: l_update_stop := 'Y';
5219: -- As there are multiple deliveries, need to evaluate the pickup of those
5229: -- Cannot convert the Incoming Stop location to correspond to the dummy
5230: -- Need to create a new stop for the Dummy Location
5231: l_update_stop := 'N';
5232: IF l_debug_on THEN
5233: WSH_DEBUG_SV.logmsg(l_module_name,'Atleast one delivery uses this location as pickup, delivery_id:'||l_dlvy_tab(k).delivery_id);
5234: END IF;
5235: EXIT; -- out of this loop for delivery, once a match is found
5236: END IF;
5237: END LOOP;
5236: END IF;
5237: END LOOP;
5238:
5239: IF l_debug_on THEN
5240: WSH_DEBUG_SV.logmsg(l_module_name, 'L_UPDATE_STOP FLAG:'||l_update_stop);
5241: END IF;
5242:
5243: IF l_update_stop = 'Y' THEN--{
5244: -- Update the Stop Location for current stop to match delivery location
5284:
5285: END LOOP;--} -- across stop_tab Loop
5286:
5287: IF l_debug_on THEN
5288: WSH_DEBUG_SV.log(l_module_name,'New Dummy Stop Tab Count:', l_new_stop_tab.count);
5289: WSH_DEBUG_SV.log(l_module_name,'Input Stop Tab Count:', x_stop_tab.count);
5290: END IF;
5291:
5292: IF l_new_stop_tab.count > 0 THEN--{
5285: END LOOP;--} -- across stop_tab Loop
5286:
5287: IF l_debug_on THEN
5288: WSH_DEBUG_SV.log(l_module_name,'New Dummy Stop Tab Count:', l_new_stop_tab.count);
5289: WSH_DEBUG_SV.log(l_module_name,'Input Stop Tab Count:', x_stop_tab.count);
5290: END IF;
5291:
5292: IF l_new_stop_tab.count > 0 THEN--{
5293: FOR new_stop in l_new_stop_tab.FIRST..l_new_stop_tab.LAST
5301: END LOOP;
5302: END IF;--}
5303:
5304: IF l_debug_on THEN
5305: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5306: WSH_DEBUG_SV.log(l_module_name,'Output Stop Tab Count:', x_stop_tab.count);
5307: WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
5308: WSH_DEBUG_SV.pop(l_module_name);
5309: END IF;
5302: END IF;--}
5303:
5304: IF l_debug_on THEN
5305: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5306: WSH_DEBUG_SV.log(l_module_name,'Output Stop Tab Count:', x_stop_tab.count);
5307: WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
5308: WSH_DEBUG_SV.pop(l_module_name);
5309: END IF;
5310:
5303:
5304: IF l_debug_on THEN
5305: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5306: WSH_DEBUG_SV.log(l_module_name,'Output Stop Tab Count:', x_stop_tab.count);
5307: WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
5308: WSH_DEBUG_SV.pop(l_module_name);
5309: END IF;
5310:
5311: EXCEPTION
5304: IF l_debug_on THEN
5305: WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5306: WSH_DEBUG_SV.log(l_module_name,'Output Stop Tab Count:', x_stop_tab.count);
5307: WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
5308: WSH_DEBUG_SV.pop(l_module_name);
5309: END IF;
5310:
5311: EXCEPTION
5312: WHEN OTHERS THEN
5314: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5315: WSH_UTIL_CORE.DEFAULT_HANDLER(
5316: 'WSH_TMS_RELEASE.PROCESS_INTERNAL_LOCATIONS',l_module_name);
5317: IF l_debug_on THEN
5318: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5319: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5320: END IF;
5321:
5322: END process_internal_locations;
5315: WSH_UTIL_CORE.DEFAULT_HANDLER(
5316: 'WSH_TMS_RELEASE.PROCESS_INTERNAL_LOCATIONS',l_module_name);
5317: IF l_debug_on THEN
5318: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5319: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5320: END IF;
5321:
5322: END process_internal_locations;
5323:
5342: l_source_tz_id NUMBER;
5343:
5344: invalid_timezone EXCEPTION;
5345:
5346: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
5347: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_server_time';
5348:
5349: BEGIN
5350: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5349: BEGIN
5350: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5351:
5352: IF l_debug_on THEN
5353: WSH_DEBUG_SV.push (l_module_name);
5354: WSH_DEBUG_SV.logmsg(l_module_name,'p_source_timezone_code : '||p_source_timezone_code);
5355: WSH_DEBUG_SV.logmsg(l_module_name,'p_source_time : '||to_char(p_source_time,'DD-MON-RRRR HH24:MI:SS'));
5356: END IF;
5357:
5350: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5351:
5352: IF l_debug_on THEN
5353: WSH_DEBUG_SV.push (l_module_name);
5354: WSH_DEBUG_SV.logmsg(l_module_name,'p_source_timezone_code : '||p_source_timezone_code);
5355: WSH_DEBUG_SV.logmsg(l_module_name,'p_source_time : '||to_char(p_source_time,'DD-MON-RRRR HH24:MI:SS'));
5356: END IF;
5357:
5358: IF p_source_timezone_code IS NULL THEN
5351:
5352: IF l_debug_on THEN
5353: WSH_DEBUG_SV.push (l_module_name);
5354: WSH_DEBUG_SV.logmsg(l_module_name,'p_source_timezone_code : '||p_source_timezone_code);
5355: WSH_DEBUG_SV.logmsg(l_module_name,'p_source_time : '||to_char(p_source_time,'DD-MON-RRRR HH24:MI:SS'));
5356: END IF;
5357:
5358: IF p_source_timezone_code IS NULL THEN
5359: RAISE invalid_timezone;
5368:
5369: l_server_tz_id := FND_PROFILE.value('SERVER_TIMEZONE_ID');
5370:
5371: IF l_debug_on THEN
5372: WSH_DEBUG_SV.logmsg(l_module_name,'Server tz id :'||l_server_tz_id);
5373: END IF;
5374:
5375: HZ_TIMEZONE_PUB.Get_Time(
5376: p_api_version => 1.0,
5383: x_msg_count => x_msg_count,
5384: x_msg_data => x_msg_data);
5385:
5386: IF l_debug_on THEN
5387: WSH_DEBUG_SV.logmsg(l_module_name,'Returning time : '|| x_server_time);
5388: WSH_DEBUG_SV.logmsg(l_module_name,'Returning x_return_status : '|| x_return_status);
5389: WSH_DEBUG_SV.pop(l_module_name);
5390: END IF;
5391:
5384: x_msg_data => x_msg_data);
5385:
5386: IF l_debug_on THEN
5387: WSH_DEBUG_SV.logmsg(l_module_name,'Returning time : '|| x_server_time);
5388: WSH_DEBUG_SV.logmsg(l_module_name,'Returning x_return_status : '|| x_return_status);
5389: WSH_DEBUG_SV.pop(l_module_name);
5390: END IF;
5391:
5392: EXCEPTION
5385:
5386: IF l_debug_on THEN
5387: WSH_DEBUG_SV.logmsg(l_module_name,'Returning time : '|| x_server_time);
5388: WSH_DEBUG_SV.logmsg(l_module_name,'Returning x_return_status : '|| x_return_status);
5389: WSH_DEBUG_SV.pop(l_module_name);
5390: END IF;
5391:
5392: EXCEPTION
5393: WHEN invalid_timezone THEN
5396: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5397: x_msg_data := 'Input Timezone is not a valid FND timezone code';
5398: --
5399: IF l_debug_on THEN
5400: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_timezone');
5401: END IF;
5402: --
5403:
5404: WHEN others THEN
5406: x_msg_data := 'Oracle error message is '|| SQLERRM;
5407: WSH_UTIL_CORE.default_handler('WSH_OTM_INBOUND_GRP.get_server_time');
5408: --
5409: IF l_debug_on THEN
5410: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5411: WSH_DEBUG_SV.logmsg(l_module_name,'-------------- END ----------------');
5412: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
5413: END IF;
5414: --
5407: WSH_UTIL_CORE.default_handler('WSH_OTM_INBOUND_GRP.get_server_time');
5408: --
5409: IF l_debug_on THEN
5410: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5411: WSH_DEBUG_SV.logmsg(l_module_name,'-------------- END ----------------');
5412: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
5413: END IF;
5414: --
5415: END get_server_time;
5408: --
5409: IF l_debug_on THEN
5410: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5411: WSH_DEBUG_SV.logmsg(l_module_name,'-------------- END ----------------');
5412: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
5413: END IF;
5414: --
5415: END get_server_time;
5416: