[Home] [Help]
87: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
88: --
89: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
90: IF l_debug_on IS NULL THEN
91: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
92: END IF;
93: --
94: IF l_debug_on THEN
95: WSH_DEBUG_SV.push(l_module_name);
91: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
92: END IF;
93: --
94: IF l_debug_on THEN
95: WSH_DEBUG_SV.push(l_module_name);
96: WSH_DEBUG_SV.log(l_module_name, 'p_party_id', p_party_id);
97: WSH_DEBUG_SV.log(l_module_name, 'p_party_site_id', p_party_site_id);
98: WSH_DEBUG_SV.log(l_module_name, 'p_process_locations', p_process_locations);
99: WSH_DEBUG_SV.log(l_module_name, 'p_to_id', p_to_id);
92: END IF;
93: --
94: IF l_debug_on THEN
95: WSH_DEBUG_SV.push(l_module_name);
96: WSH_DEBUG_SV.log(l_module_name, 'p_party_id', p_party_id);
97: WSH_DEBUG_SV.log(l_module_name, 'p_party_site_id', p_party_site_id);
98: WSH_DEBUG_SV.log(l_module_name, 'p_process_locations', p_process_locations);
99: WSH_DEBUG_SV.log(l_module_name, 'p_to_id', p_to_id);
100: WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
93: --
94: IF l_debug_on THEN
95: WSH_DEBUG_SV.push(l_module_name);
96: WSH_DEBUG_SV.log(l_module_name, 'p_party_id', p_party_id);
97: WSH_DEBUG_SV.log(l_module_name, 'p_party_site_id', p_party_site_id);
98: WSH_DEBUG_SV.log(l_module_name, 'p_process_locations', p_process_locations);
99: WSH_DEBUG_SV.log(l_module_name, 'p_to_id', p_to_id);
100: WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
101: END IF;
94: IF l_debug_on THEN
95: WSH_DEBUG_SV.push(l_module_name);
96: WSH_DEBUG_SV.log(l_module_name, 'p_party_id', p_party_id);
97: WSH_DEBUG_SV.log(l_module_name, 'p_party_site_id', p_party_site_id);
98: WSH_DEBUG_SV.log(l_module_name, 'p_process_locations', p_process_locations);
99: WSH_DEBUG_SV.log(l_module_name, 'p_to_id', p_to_id);
100: WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
101: END IF;
102: --
95: WSH_DEBUG_SV.push(l_module_name);
96: WSH_DEBUG_SV.log(l_module_name, 'p_party_id', p_party_id);
97: WSH_DEBUG_SV.log(l_module_name, 'p_party_site_id', p_party_site_id);
98: WSH_DEBUG_SV.log(l_module_name, 'p_process_locations', p_process_locations);
99: WSH_DEBUG_SV.log(l_module_name, 'p_to_id', p_to_id);
100: WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
101: END IF;
102: --
103: FOR get_party_site_rec IN get_party_site_csr(p_party_id => p_party_id,
96: WSH_DEBUG_SV.log(l_module_name, 'p_party_id', p_party_id);
97: WSH_DEBUG_SV.log(l_module_name, 'p_party_site_id', p_party_site_id);
98: WSH_DEBUG_SV.log(l_module_name, 'p_process_locations', p_process_locations);
99: WSH_DEBUG_SV.log(l_module_name, 'p_to_id', p_to_id);
100: WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
101: END IF;
102: --
103: FOR get_party_site_rec IN get_party_site_csr(p_party_id => p_party_id,
104: p_site_id => p_party_site_id)
105: LOOP
106: --{
107: IF l_debug_on THEN
108: --
109: WSH_DEBUG_SV.logmsg(l_module_name, '----------------', WSH_DEBUG_SV.C_STMT_LEVEL);
110: WSH_DEBUG_SV.log(l_module_name,'LOCATION_ID' , get_party_site_rec.location_id);
111: WSH_DEBUG_SV.log(l_module_name,'PARTY_SITE_ID', get_party_site_rec.party_site_id);
112: WSH_DEBUG_SV.log(l_module_name,'OBJECT_VERSION_NUMBER', get_party_site_rec.object_version_number);
113: --
106: --{
107: IF l_debug_on THEN
108: --
109: WSH_DEBUG_SV.logmsg(l_module_name, '----------------', WSH_DEBUG_SV.C_STMT_LEVEL);
110: WSH_DEBUG_SV.log(l_module_name,'LOCATION_ID' , get_party_site_rec.location_id);
111: WSH_DEBUG_SV.log(l_module_name,'PARTY_SITE_ID', get_party_site_rec.party_site_id);
112: WSH_DEBUG_SV.log(l_module_name,'OBJECT_VERSION_NUMBER', get_party_site_rec.object_version_number);
113: --
114: END IF;
107: IF l_debug_on THEN
108: --
109: WSH_DEBUG_SV.logmsg(l_module_name, '----------------', WSH_DEBUG_SV.C_STMT_LEVEL);
110: WSH_DEBUG_SV.log(l_module_name,'LOCATION_ID' , get_party_site_rec.location_id);
111: WSH_DEBUG_SV.log(l_module_name,'PARTY_SITE_ID', get_party_site_rec.party_site_id);
112: WSH_DEBUG_SV.log(l_module_name,'OBJECT_VERSION_NUMBER', get_party_site_rec.object_version_number);
113: --
114: END IF;
115: --
108: --
109: WSH_DEBUG_SV.logmsg(l_module_name, '----------------', WSH_DEBUG_SV.C_STMT_LEVEL);
110: WSH_DEBUG_SV.log(l_module_name,'LOCATION_ID' , get_party_site_rec.location_id);
111: WSH_DEBUG_SV.log(l_module_name,'PARTY_SITE_ID', get_party_site_rec.party_site_id);
112: WSH_DEBUG_SV.log(l_module_name,'OBJECT_VERSION_NUMBER', get_party_site_rec.object_version_number);
113: --
114: END IF;
115: --
116: -- Check if we have any deliveries tied to the
136: l_query_count := 0;
137: END;
138: --
139: IF l_debug_on THEN
140: WSH_DEBUG_SV.log(l_module_name,'L_QUERY_COUNT', l_query_count);
141: END IF;
142: --
143: IF l_query_count = 0 AND p_party_site_id IS NOT NULL THEN
144: --{
148: l_object_version_number := get_party_site_rec.object_version_number;
149: l_from_party_rec.status := 'I' ;
150: --
151: IF l_debug_on THEN
152: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE_USE',WSH_DEBUG_SV.C_PROC_LEVEL);
153: END IF;
154: --
155: hz_party_site_v2pub.update_party_site
156: (
161: x_msg_data => l_msg
162: );
163: --
164: IF l_debug_on THEN
165: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
166: END IF;
167: --
168: wsh_util_core.api_post_call
169: (
175: --
176: IF p_process_locations THEN
177: --{
178: IF l_debug_on THEN
179: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Process_Location API', WSH_DEBUG_SV.C_PROC_LEVEL);
180: END IF;
181: --
182: WSH_LOCATIONS_PKG.Process_Locations
183: (
192: x_sqlerr => l_sql_err
193: );
194: --
195: IF l_debug_on THEN
196: WSH_DEBUG_SV.logmsg(l_module_name, 'After Process_Location API', WSH_DEBUG_SV.C_PROC_LEVEL);
197: WSH_DEBUG_SV.log(l_module_name, 'l_sql_code', l_sql_code);
198: WSH_DEBUG_SV.log(l_module_name, 'l_sql_err', l_sql_err);
199: WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
200: END IF;
193: );
194: --
195: IF l_debug_on THEN
196: WSH_DEBUG_SV.logmsg(l_module_name, 'After Process_Location API', WSH_DEBUG_SV.C_PROC_LEVEL);
197: WSH_DEBUG_SV.log(l_module_name, 'l_sql_code', l_sql_code);
198: WSH_DEBUG_SV.log(l_module_name, 'l_sql_err', l_sql_err);
199: WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
200: END IF;
201: --
194: --
195: IF l_debug_on THEN
196: WSH_DEBUG_SV.logmsg(l_module_name, 'After Process_Location API', WSH_DEBUG_SV.C_PROC_LEVEL);
197: WSH_DEBUG_SV.log(l_module_name, 'l_sql_code', l_sql_code);
198: WSH_DEBUG_SV.log(l_module_name, 'l_sql_err', l_sql_err);
199: WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
200: END IF;
201: --
202: WSH_UTIL_CORE.api_post_call
195: IF l_debug_on THEN
196: WSH_DEBUG_SV.logmsg(l_module_name, 'After Process_Location API', WSH_DEBUG_SV.C_PROC_LEVEL);
197: WSH_DEBUG_SV.log(l_module_name, 'l_sql_code', l_sql_code);
198: WSH_DEBUG_SV.log(l_module_name, 'l_sql_err', l_sql_err);
199: WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
200: END IF;
201: --
202: WSH_UTIL_CORE.api_post_call
203: (
210: --}
211: ELSIF l_query_count = 0 THEN
212: --{
213: IF l_debug_on THEN
214: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Create_Site', WSH_DEBUG_SV.C_PROC_LEVEL);
215: END IF;
216: --
217: Create_Site
218: (
225: x_return_Status => l_return_status
226: );
227: --
228: IF l_debug_on THEN
229: WSH_DEBUG_SV.logmsg(l_module_name, 'After calling create_Site', WSH_DEBUG_SV.C_PROC_LEVEL);
230: WSH_DEBUG_SV.log(l_module_name, 'Return Status', l_return_status);
231: END IF;
232: --
233: WSH_UTIL_CORE.api_post_call
226: );
227: --
228: IF l_debug_on THEN
229: WSH_DEBUG_SV.logmsg(l_module_name, 'After calling create_Site', WSH_DEBUG_SV.C_PROC_LEVEL);
230: WSH_DEBUG_SV.log(l_module_name, 'Return Status', l_return_status);
231: END IF;
232: --
233: WSH_UTIL_CORE.api_post_call
234: (
251: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
252: END IF;
253: --
254: IF l_debug_on THEN
255: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
256: WSH_DEBUG_SV.pop(l_module_name);
257: END IF;
258: --}
259: EXCEPTION
252: END IF;
253: --
254: IF l_debug_on THEN
255: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
256: WSH_DEBUG_SV.pop(l_module_name);
257: END IF;
258: --}
259: EXCEPTION
260: --
263: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
264: --
265: IF l_debug_on THEN
266: --
267: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
268: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
269: --
270: END IF;
271: --
264: --
265: IF l_debug_on THEN
266: --
267: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
268: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
269: --
270: END IF;
271: --
272: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
274: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
275: --
276: IF l_debug_on THEN
277: --
278: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
279: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
280: --
281: END IF;
282: --
275: --
276: IF l_debug_on THEN
277: --
278: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
279: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
280: --
281: END IF;
282: --
283: WHEN OTHERS THEN
286: wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.InactivatePartySites');
287: --
288: IF l_debug_on THEN
289: --
290: WSH_DEBUG_SV.log(l_module_name,'Unexpected error has occured. Oracle error message is ', SUBSTRB(SQLERRM,1,200));
291: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
292: --
293: END IF;
294: --
287: --
288: IF l_debug_on THEN
289: --
290: WSH_DEBUG_SV.log(l_module_name,'Unexpected error has occured. Oracle error message is ', SUBSTRB(SQLERRM,1,200));
291: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
292: --
293: END IF;
294: --
295: END InactivatePartySites;
424: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
425: --
426: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
427: IF l_debug_on IS NULL THEN
428: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
429: END IF;
430: --
431: IF l_debug_on THEN
432: --
429: END IF;
430: --
431: IF l_debug_on THEN
432: --
433: WSH_DEBUG_SV.push(l_module_name);
434: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
435: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
436: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
437: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME', p_delivery_name );
430: --
431: IF l_debug_on THEN
432: --
433: WSH_DEBUG_SV.push(l_module_name);
434: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
435: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
436: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
437: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME', p_delivery_name );
438: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
431: IF l_debug_on THEN
432: --
433: WSH_DEBUG_SV.push(l_module_name);
434: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
435: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
436: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
437: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME', p_delivery_name );
438: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
439: --
432: --
433: WSH_DEBUG_SV.push(l_module_name);
434: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
435: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
436: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
437: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME', p_delivery_name );
438: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
439: --
440: END IF;
433: WSH_DEBUG_SV.push(l_module_name);
434: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
435: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
436: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
437: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME', p_delivery_name );
438: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
439: --
440: END IF;
441: --
434: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
435: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
436: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
437: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME', p_delivery_name );
438: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
439: --
440: END IF;
441: --
442: l_dummy := NULL;
444: FETCH check_location_id INTO l_dummy, l_partySiteId;
445: CLOSE check_location_id;
446: --
447: IF l_debug_on THEN
448: WSH_DEBUG_SV.log(l_module_name, 'l_dummy', l_dummy);
449: WSH_DEBUG_SV.log(l_module_name, 'l_partySiteId', l_partySiteId);
450: END IF;
451: --
452: IF l_dummy IS NOT NULL THEN
445: CLOSE check_location_id;
446: --
447: IF l_debug_on THEN
448: WSH_DEBUG_SV.log(l_module_name, 'l_dummy', l_dummy);
449: WSH_DEBUG_SV.log(l_module_name, 'l_partySiteId', l_partySiteId);
450: END IF;
451: --
452: IF l_dummy IS NOT NULL THEN
453: --{
453: --{
454: IF p_from_id <> p_to_id THEN
455: --{
456: IF l_debug_on THEN
457: wsh_debug_sv.logmsg(l_module_name, 'Calling InactivatePartySites', WSH_DEBUG_SV.C_PROC_LEVEL);
458: END IF;
459: --
460: InactivatePartySites
461: (
467: x_return_status => l_return_status
468: );
469: --
470: IF l_debug_on THEN
471: wsh_debug_sv.log(l_module_name, 'Return Status from inactivatePartySites', l_return_status);
472: END IF;
473: --
474: WSH_UTIL_CORE.api_post_call
475: (
486: --{
487: IF g_LocChangeTab(i).location_id = p_location_id THEN
488: --{
489: IF l_debug_on THEN
490: WSH_DEBUG_SV.logmsg(l_module_name, 'Logging SF code change exception');
491: END IF;
492: --
493: fnd_message.set_name ( 'WSH', 'WSH_IB_SF_LOCN_CODE_CHG');
494: fnd_message.set_token( 'L_LOCATION_CODE' , g_LocChangeTab(i).old_loc_code);
497: l_msg := FND_MESSAGE.GET;
498: WSH_UTIL_CORE.printMsg(l_msg);
499: --
500: IF l_debug_on THEN
501: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
502: END IF;
503: --
504: wsh_xc_util.log_exception (
505: p_api_version => 1.0,
516: p_logged_at_location_id => p_location_id
517: );
518: --
519: IF l_debug_on THEN
520: WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_XC_UTIL.LOG_EXCEPTION is ', l_return_status);
521: END IF;
522: --
523: wsh_util_core.api_post_call(
524: p_return_status => l_return_status,
535: --}
536: END IF;
537: --
538: IF l_debug_on THEN
539: WSH_DEBUG_SV.pop(l_module_name);
540: END IF;
541: --
542: RETURN;
543: --}
548: CLOSE check_location_code;
549: --
550: IF l_debug_on THEN
551: --
552: WSH_DEBUG_SV.log(l_module_name,'cursor check_location_code : L_LOCATION_CODE', l_location_code);
553: WSH_DEBUG_SV.log(l_module_name,'cursor check_location_code : L_FROM_PARTY_SITE_ID ', l_from_party_site_id);
554: --
555: END IF;
556: --
549: --
550: IF l_debug_on THEN
551: --
552: WSH_DEBUG_SV.log(l_module_name,'cursor check_location_code : L_LOCATION_CODE', l_location_code);
553: WSH_DEBUG_SV.log(l_module_name,'cursor check_location_code : L_FROM_PARTY_SITE_ID ', l_from_party_site_id);
554: --
555: END IF;
556: --
557: OPEN get_contact_info(p_party_id => p_from_id,
560: CLOSE get_contact_info;
561: --
562: IF l_debug_on THEN
563: --
564: WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.SHIPPER_NAME', l_contact_rec.shipper_name);
565: WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.PHONE_NUMBER', l_contact_rec.phone_number);
566: WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.EMAIL_ADDRESS',l_contact_rec.email_address);
567: --
568: END IF;
561: --
562: IF l_debug_on THEN
563: --
564: WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.SHIPPER_NAME', l_contact_rec.shipper_name);
565: WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.PHONE_NUMBER', l_contact_rec.phone_number);
566: WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.EMAIL_ADDRESS',l_contact_rec.email_address);
567: --
568: END IF;
569: --
562: IF l_debug_on THEN
563: --
564: WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.SHIPPER_NAME', l_contact_rec.shipper_name);
565: WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.PHONE_NUMBER', l_contact_rec.phone_number);
566: WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.EMAIL_ADDRESS',l_contact_rec.email_address);
567: --
568: END IF;
569: --
570: l_site_number := l_location_code || '|' || p_to_id;
569: --
570: l_site_number := l_location_code || '|' || p_to_id;
571: --
572: IF l_debug_on THEN
573: WSH_DEBUG_SV.log(l_module_name,'L_SITE_NUMBER', l_site_number);
574: END IF;
575: --
576: -- Check if we have an entry in hz_party_sites with the same
577: -- party_site_number and merge TO vendor ID
581: FETCH chk_locn_csr INTO l_dummy;
582: CLOSE chk_locn_csr;
583: --
584: IF l_debug_on THEN
585: wsh_debug_sv.log(l_module_name, 'After chk_loc_csr, l_dummy', l_dummy);
586: END IF;
587: --
588: IF l_dummy IS NOT NULL THEN
589: --{
587: --
588: IF l_dummy IS NOT NULL THEN
589: --{
590: IF l_debug_on THEN
591: wsh_debug_sv.log(l_module_name, 'Looping to create unique SF', l_dummy);
592: END IF;
593: --
594: l_loc_chg := TRUE;
595: --
625: wsh_util_core.printMsg( l_msg );
626: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
627: --
628: IF l_debug_on THEN
629: WSH_DEBUG_SV.pop(l_module_name);
630: END IF;
631: --
632: RETURN;
633: --}
639: END IF;
640: --
641: IF l_debug_on THEN
642: --{
643: WSH_DEBUG_SV.log(l_module_name, 'l_new_location_code', l_new_location_code);
644: WSH_DEBUG_SV.log(l_module_name, 'l_loc_chg', l_loc_chg);
645: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SUPPLIER_PARTY.CREATE_HZ_PARTY_SITE',WSH_DEBUG_SV.C_PROC_LEVEL);
646: --}
647: END IF;
640: --
641: IF l_debug_on THEN
642: --{
643: WSH_DEBUG_SV.log(l_module_name, 'l_new_location_code', l_new_location_code);
644: WSH_DEBUG_SV.log(l_module_name, 'l_loc_chg', l_loc_chg);
645: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SUPPLIER_PARTY.CREATE_HZ_PARTY_SITE',WSH_DEBUG_SV.C_PROC_LEVEL);
646: --}
647: END IF;
648: --
641: IF l_debug_on THEN
642: --{
643: WSH_DEBUG_SV.log(l_module_name, 'l_new_location_code', l_new_location_code);
644: WSH_DEBUG_SV.log(l_module_name, 'l_loc_chg', l_loc_chg);
645: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SUPPLIER_PARTY.CREATE_HZ_PARTY_SITE',WSH_DEBUG_SV.C_PROC_LEVEL);
646: --}
647: END IF;
648: --
649: Wsh_supplier_party.create_hz_party_site(
655: );
656: --
657: IF l_debug_on THEN
658: --
659: WSH_DEBUG_SV.log(l_module_name, 'x_party_site_id', l_to_party_site_id);
660: WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_SUPPLIER_PARTY.CREATE_HZ_PARTY_SITE is', l_return_status);
661: --
662: END IF;
663: --
656: --
657: IF l_debug_on THEN
658: --
659: WSH_DEBUG_SV.log(l_module_name, 'x_party_site_id', l_to_party_site_id);
660: WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_SUPPLIER_PARTY.CREATE_HZ_PARTY_SITE is', l_return_status);
661: --
662: END IF;
663: --
664: wsh_util_core.api_post_call(
680: x_return_status => l_return_status
681: );
682: --
683: IF l_debug_on THEN
684: wsh_debug_sv.log(l_module_name, 'l_return_status', l_return_status);
685: wsh_debug_sv.log(l_module_name, 'l_party_site_use_id', l_party_site_use_id);
686: END IF;
687: --
688: wsh_util_core.api_post_call(
681: );
682: --
683: IF l_debug_on THEN
684: wsh_debug_sv.log(l_module_name, 'l_return_status', l_return_status);
685: wsh_debug_sv.log(l_module_name, 'l_party_site_use_id', l_party_site_use_id);
686: END IF;
687: --
688: wsh_util_core.api_post_call(
689: p_return_status => l_return_status,
709: --
710: -- Inactivate party site for the old vendor
711: --
712: IF l_debug_on THEN
713: wsh_debug_sv.logmsg(l_module_name, 'Calling InactivatePartySites', WSH_DEBUG_SV.C_PROC_LEVEL);
714: END IF;
715: --
716: InactivatePartySites
717: (
723: x_return_status => l_return_status
724: );
725: --
726: IF l_debug_on THEN
727: wsh_debug_sv.log(l_module_name, 'Return status from InactivatePartySite', l_return_status);
728: END IF;
729: --
730: wsh_util_core.api_post_call(
731: p_return_status => l_return_status,
733: x_num_errors => l_num_errors
734: );
735: --
736: IF l_debug_on THEN
737: wsh_debug_sv.log(l_module_name, 'l_loc_chg', l_loc_chg);
738: END IF;
739: --
740: IF l_loc_chg AND p_delivery_id IS NOT NULL THEN
741: --{
756: l_msg := FND_MESSAGE.GET;
757: WSH_UTIL_CORE.printMsg(l_msg);
758: --
759: IF l_debug_on THEN
760: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
761: END IF;
762: --
763: wsh_xc_util.log_exception (
764: p_api_version => 1.0,
775: p_logged_at_location_id => p_location_id
776: );
777: --
778: IF l_debug_on THEN
779: WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_XC_UTIL.LOG_EXCEPTION is ', l_return_status);
780: END IF;
781: --
782: wsh_util_core.api_post_call(
783: p_return_status => l_return_status,
789: --}
790: END IF;
791: --
792: IF l_debug_on THEN
793: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SUPPLIER_PARTY.PROCESS_HZ_CONTACT',WSH_DEBUG_SV.C_PROC_LEVEL);
794: END IF;
795: --
796: Wsh_supplier_party.Process_HZ_contact(
797: p_party_id => p_to_id,
802: x_return_status => l_return_status
803: );
804: --
805: IF l_debug_on THEN
806: WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_SUPPLIER_PARTY.PROCESS_HZ_CONTACT', l_return_status);
807: END IF;
808: --
809: wsh_util_core.api_post_call(
810: p_return_status => l_return_status,
818: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
819: END IF;
820: --
821: IF l_debug_on THEN
822: WSH_DEBUG_SV.pop(l_module_name);
823: END IF;
824: --
825: EXCEPTION
826: --
828: --
829: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
830: --
831: IF l_debug_on THEN
832: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
833: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
834: END IF;
835: --
836: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
829: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
830: --
831: IF l_debug_on THEN
832: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
833: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
834: END IF;
835: --
836: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
837: --
837: --
838: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
839: --
840: IF l_debug_on THEN
841: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
842: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
843: END IF;
844: --
845: WHEN OTHERS THEN
838: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
839: --
840: IF l_debug_on THEN
841: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
842: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
843: END IF;
844: --
845: WHEN OTHERS THEN
846: --
847: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
848: wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Create_Site');
849: --
850: IF l_debug_on THEN
851: WSH_DEBUG_SV.log(l_module_name,'Unexpected error has occured. Oracle error message is ', SUBSTRB(SQLERRM,1,200));
852: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
853: END IF;
854: --}
855: END Create_Site;
848: wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Create_Site');
849: --
850: IF l_debug_on THEN
851: WSH_DEBUG_SV.log(l_module_name,'Unexpected error has occured. Oracle error message is ', SUBSTRB(SQLERRM,1,200));
852: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
853: END IF;
854: --}
855: END Create_Site;
856: --
903: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
904: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
905: IF l_debug_on IS NULL THEN
906: --{
907: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
908: --}
909: END IF;
910:
911: --
910:
911: --
912: IF l_debug_on THEN
913: --{
914: WSH_DEBUG_SV.push(l_module_name);
915: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
916: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
917: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id );
918: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id );
911: --
912: IF l_debug_on THEN
913: --{
914: WSH_DEBUG_SV.push(l_module_name);
915: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
916: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
917: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id );
918: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id );
919: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
912: IF l_debug_on THEN
913: --{
914: WSH_DEBUG_SV.push(l_module_name);
915: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
916: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
917: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id );
918: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id );
919: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
920: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID', p_from_site_id );
913: --{
914: WSH_DEBUG_SV.push(l_module_name);
915: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
916: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
917: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id );
918: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id );
919: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
920: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID', p_from_site_id );
921: WSH_DEBUG_SV.log(l_module_name,'P_OLD_DELIVERY_ID', p_old_delivery_id );
914: WSH_DEBUG_SV.push(l_module_name);
915: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
916: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
917: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id );
918: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id );
919: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
920: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID', p_from_site_id );
921: WSH_DEBUG_SV.log(l_module_name,'P_OLD_DELIVERY_ID', p_old_delivery_id );
922: WSH_DEBUG_SV.log(l_module_name,'P_TEMP_UPDATE_FLAG', p_temp_update_flag );
915: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
916: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
917: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id );
918: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id );
919: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
920: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID', p_from_site_id );
921: WSH_DEBUG_SV.log(l_module_name,'P_OLD_DELIVERY_ID', p_old_delivery_id );
922: WSH_DEBUG_SV.log(l_module_name,'P_TEMP_UPDATE_FLAG', p_temp_update_flag );
923: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
916: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
917: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id );
918: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id );
919: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
920: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID', p_from_site_id );
921: WSH_DEBUG_SV.log(l_module_name,'P_OLD_DELIVERY_ID', p_old_delivery_id );
922: WSH_DEBUG_SV.log(l_module_name,'P_TEMP_UPDATE_FLAG', p_temp_update_flag );
923: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
924: --}
917: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id );
918: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id );
919: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
920: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID', p_from_site_id );
921: WSH_DEBUG_SV.log(l_module_name,'P_OLD_DELIVERY_ID', p_old_delivery_id );
922: WSH_DEBUG_SV.log(l_module_name,'P_TEMP_UPDATE_FLAG', p_temp_update_flag );
923: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
924: --}
925: END IF;
918: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id );
919: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
920: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID', p_from_site_id );
921: WSH_DEBUG_SV.log(l_module_name,'P_OLD_DELIVERY_ID', p_old_delivery_id );
922: WSH_DEBUG_SV.log(l_module_name,'P_TEMP_UPDATE_FLAG', p_temp_update_flag );
923: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
924: --}
925: END IF;
926:
919: WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
920: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID', p_from_site_id );
921: WSH_DEBUG_SV.log(l_module_name,'P_OLD_DELIVERY_ID', p_old_delivery_id );
922: WSH_DEBUG_SV.log(l_module_name,'P_TEMP_UPDATE_FLAG', p_temp_update_flag );
923: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
924: --}
925: END IF;
926:
927:
936: WHERE delivery_id = p_delivery_id
937: RETURNING name INTO l_dlvy_name;
938: IF l_debug_on THEN
939: --{
940: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
941: --}
942: END IF;
943:
944: ELSE
967: )
968: AND vendor_id = p_from_id;
969: IF l_debug_on THEN
970: --{
971: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
972: --}
973: END IF;
974: --}
975: END IF;
991: END IF;
992:
993: IF l_debug_on THEN
994: --{
995: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_wms_sync_tmp. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
996: --}
997: END IF;
998:
999:
1004: WHERE wsh_location_id = p_location_id;
1005:
1006: IF l_debug_on THEN
1007: --{
1008: WSH_DEBUG_SV.log(l_module_name, 'l_location_id', l_location_id);
1009: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.CREATE_SITE' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
1010: --}
1011: END IF;
1012:
1005:
1006: IF l_debug_on THEN
1007: --{
1008: WSH_DEBUG_SV.log(l_module_name, 'l_location_id', l_location_id);
1009: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.CREATE_SITE' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
1010: --}
1011: END IF;
1012:
1013: create_site (
1025: x_num_errors => l_num_errors
1026: );
1027: IF l_debug_on THEN
1028: --{
1029: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_VENDOR_PARTY_MERGE_PKG.CREATE_SITE is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
1030: --}
1031: END IF;
1032: --}
1033: END IF;
1040: --}
1041: END IF;
1042: --
1043: IF l_debug_on THEN
1044: WSH_DEBUG_SV.pop(l_module_name);
1045: END IF;
1046:
1047: EXCEPTION
1048: --
1051: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1052: --
1053: IF l_debug_on THEN
1054: --
1055: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
1056: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1057: --
1058: END IF;
1059: --
1052: --
1053: IF l_debug_on THEN
1054: --
1055: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
1056: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1057: --
1058: END IF;
1059: --
1060: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1062: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1063: --
1064: IF l_debug_on THEN
1065: --
1066: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1067: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1068: --
1069: END IF;
1070: --
1063: --
1064: IF l_debug_on THEN
1065: --
1066: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1067: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1068: --
1069: END IF;
1070: --
1071: WHEN OTHERS THEN
1073: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1074: wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Update_New_Delivery');
1075: --
1076: IF l_debug_on THEN
1077: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1078: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1079: END IF;
1080: --
1081:
1074: wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Update_New_Delivery');
1075: --
1076: IF l_debug_on THEN
1077: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1078: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1079: END IF;
1080: --
1081:
1082: --}
1201: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1202:
1203: IF l_debug_on IS NULL THEN
1204: --{
1205: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1206: --}
1207: END IF;
1208:
1209:
1208:
1209:
1210: IF l_debug_on THEN
1211: --{
1212: WSH_DEBUG_SV.push(l_module_name);
1213: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1214: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1215: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1216: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1209:
1210: IF l_debug_on THEN
1211: --{
1212: WSH_DEBUG_SV.push(l_module_name);
1213: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1214: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1215: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1216: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1217: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1210: IF l_debug_on THEN
1211: --{
1212: WSH_DEBUG_SV.push(l_module_name);
1213: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1214: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1215: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1216: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1217: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1218: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1211: --{
1212: WSH_DEBUG_SV.push(l_module_name);
1213: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1214: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1215: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1216: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1217: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1218: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1219: WSH_DEBUG_SV.log(l_module_name,'P_SITE_MERGE',p_site_merge);
1212: WSH_DEBUG_SV.push(l_module_name);
1213: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1214: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1215: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1216: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1217: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1218: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1219: WSH_DEBUG_SV.log(l_module_name,'P_SITE_MERGE',p_site_merge);
1220: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SUPPLIER_NAME',p_from_supplier_name);
1213: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1214: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1215: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1216: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1217: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1218: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1219: WSH_DEBUG_SV.log(l_module_name,'P_SITE_MERGE',p_site_merge);
1220: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SUPPLIER_NAME',p_from_supplier_name);
1221: --}
1214: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1215: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1216: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1217: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1218: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1219: WSH_DEBUG_SV.log(l_module_name,'P_SITE_MERGE',p_site_merge);
1220: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SUPPLIER_NAME',p_from_supplier_name);
1221: --}
1222: END IF;
1215: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1216: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1217: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1218: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1219: WSH_DEBUG_SV.log(l_module_name,'P_SITE_MERGE',p_site_merge);
1220: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SUPPLIER_NAME',p_from_supplier_name);
1221: --}
1222: END IF;
1223:
1216: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1217: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1218: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1219: WSH_DEBUG_SV.log(l_module_name,'P_SITE_MERGE',p_site_merge);
1220: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SUPPLIER_NAME',p_from_supplier_name);
1221: --}
1222: END IF;
1223:
1224: -- Update WSH_CARRIERS with the merge to vendor/vendor site
1232: AND supplier_site_id = p_from_site_id;
1233: --
1234: IF l_debug_on THEN
1235: --{
1236: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_carriers. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1237: --}
1238: END IF;
1239: --
1240: -- Update WSH_CARRIER_SITES with the merge to vendor site
1247: WHERE supplier_site_id = p_from_site_id;
1248: --
1249: IF l_debug_on THEN
1250: --{
1251: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_carrier_sites. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1252: --}
1253: END IF;
1254: --
1255: WSH_PARTY_MERGE.Update_Entities_During_Merge
1278: LOOP
1279: --{
1280: IF l_debug_on THEN
1281: --{
1282: WSH_DEBUG_SV.logmsg(l_module_name, '-----------------------', WSH_DEBUG_SV.C_STMT_LEVEL);
1283: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_TYPE = ' || check_site_calendar_rec.calendar_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1284: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_ASSIGNMENT_ID = ' || check_site_calendar_rec.calendar_assignment_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1285: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_ID = ' || check_site_calendar_rec.vendor_site_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1286: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.ASSOCIATION_TYPE = ' || check_site_calendar_rec.association_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1279: --{
1280: IF l_debug_on THEN
1281: --{
1282: WSH_DEBUG_SV.logmsg(l_module_name, '-----------------------', WSH_DEBUG_SV.C_STMT_LEVEL);
1283: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_TYPE = ' || check_site_calendar_rec.calendar_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1284: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_ASSIGNMENT_ID = ' || check_site_calendar_rec.calendar_assignment_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1285: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_ID = ' || check_site_calendar_rec.vendor_site_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1286: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.ASSOCIATION_TYPE = ' || check_site_calendar_rec.association_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1287: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.FREIGHT_CODE = ' || check_site_calendar_rec.freight_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1280: IF l_debug_on THEN
1281: --{
1282: WSH_DEBUG_SV.logmsg(l_module_name, '-----------------------', WSH_DEBUG_SV.C_STMT_LEVEL);
1283: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_TYPE = ' || check_site_calendar_rec.calendar_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1284: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_ASSIGNMENT_ID = ' || check_site_calendar_rec.calendar_assignment_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1285: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_ID = ' || check_site_calendar_rec.vendor_site_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1286: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.ASSOCIATION_TYPE = ' || check_site_calendar_rec.association_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1287: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.FREIGHT_CODE = ' || check_site_calendar_rec.freight_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1288: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_CODE = ' || check_site_calendar_rec.vendor_site_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1281: --{
1282: WSH_DEBUG_SV.logmsg(l_module_name, '-----------------------', WSH_DEBUG_SV.C_STMT_LEVEL);
1283: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_TYPE = ' || check_site_calendar_rec.calendar_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1284: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_ASSIGNMENT_ID = ' || check_site_calendar_rec.calendar_assignment_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1285: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_ID = ' || check_site_calendar_rec.vendor_site_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1286: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.ASSOCIATION_TYPE = ' || check_site_calendar_rec.association_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1287: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.FREIGHT_CODE = ' || check_site_calendar_rec.freight_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1288: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_CODE = ' || check_site_calendar_rec.vendor_site_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1289: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.calendar_CODE = ' || check_site_calendar_rec.calendar_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1282: WSH_DEBUG_SV.logmsg(l_module_name, '-----------------------', WSH_DEBUG_SV.C_STMT_LEVEL);
1283: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_TYPE = ' || check_site_calendar_rec.calendar_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1284: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_ASSIGNMENT_ID = ' || check_site_calendar_rec.calendar_assignment_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1285: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_ID = ' || check_site_calendar_rec.vendor_site_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1286: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.ASSOCIATION_TYPE = ' || check_site_calendar_rec.association_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1287: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.FREIGHT_CODE = ' || check_site_calendar_rec.freight_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1288: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_CODE = ' || check_site_calendar_rec.vendor_site_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1289: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.calendar_CODE = ' || check_site_calendar_rec.calendar_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1290: --}
1283: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_TYPE = ' || check_site_calendar_rec.calendar_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1284: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_ASSIGNMENT_ID = ' || check_site_calendar_rec.calendar_assignment_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1285: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_ID = ' || check_site_calendar_rec.vendor_site_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1286: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.ASSOCIATION_TYPE = ' || check_site_calendar_rec.association_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1287: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.FREIGHT_CODE = ' || check_site_calendar_rec.freight_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1288: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_CODE = ' || check_site_calendar_rec.vendor_site_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1289: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.calendar_CODE = ' || check_site_calendar_rec.calendar_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1290: --}
1291: END IF;
1284: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_ASSIGNMENT_ID = ' || check_site_calendar_rec.calendar_assignment_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1285: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_ID = ' || check_site_calendar_rec.vendor_site_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1286: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.ASSOCIATION_TYPE = ' || check_site_calendar_rec.association_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1287: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.FREIGHT_CODE = ' || check_site_calendar_rec.freight_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1288: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_CODE = ' || check_site_calendar_rec.vendor_site_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1289: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.calendar_CODE = ' || check_site_calendar_rec.calendar_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1290: --}
1291: END IF;
1292: OPEN check_dup_assignment( p_vendor_id => p_to_id,
1285: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_ID = ' || check_site_calendar_rec.vendor_site_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1286: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.ASSOCIATION_TYPE = ' || check_site_calendar_rec.association_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1287: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.FREIGHT_CODE = ' || check_site_calendar_rec.freight_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1288: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_CODE = ' || check_site_calendar_rec.vendor_site_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1289: WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.calendar_CODE = ' || check_site_calendar_rec.calendar_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1290: --}
1291: END IF;
1292: OPEN check_dup_assignment( p_vendor_id => p_to_id,
1293: p_calendar_Type => check_site_calendar_rec.calendar_type,
1308: WHERE calendar_assignment_id = check_site_calendar_rec.calendar_assignment_id;
1309: --
1310: IF l_debug_on THEN
1311: --{
1312: WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID updated',
1313: check_site_calendar_rec.calendar_assignment_id);
1314: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_calendar_assignments. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1315: --}
1316: END IF;
1310: IF l_debug_on THEN
1311: --{
1312: WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID updated',
1313: check_site_calendar_rec.calendar_assignment_id);
1314: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_calendar_assignments. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1315: --}
1316: END IF;
1317: --
1318: -- Is there a vendor level calendar assignment ? If not, create one
1324: FETCH c_VendorLvlCalAsg INTO v_VendorLvlCalAsg;
1325: CLOSE c_VendorLvlCalAsg;
1326: --
1327: IF l_debug_on THEN
1328: WSH_DEBUG_SV.log(l_module_name, 'Vendor Lvl Cal. Asg Exists ? ', v_VendorLvlCalAsg);
1329: END IF;
1330: --
1331: IF v_VendorLvlCalAsg = 0 THEN
1332: --{
1331: IF v_VendorLvlCalAsg = 0 THEN
1332: --{
1333: --
1334: IF l_debug_on THEN
1335: WSH_DEBUG_SV.log(l_module_name, 'Creating vendor level Cal. Asg', p_to_id);
1336: END IF;
1337: --
1338: l_CalAsgInfo.CALENDAR_CODE := check_site_calendar_rec.calendar_code;
1339: l_CalAsgInfo.CALENDAR_TYPE := check_site_calendar_rec.calendar_type;
1351: x_Calendar_Aassignment_Id => l_CalAsgId
1352: );
1353: --
1354: IF l_debug_on THEN
1355: WSH_DEBUG_SV.log(l_module_name, 'Return status from CAL. API', l_return_status);
1356: WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID', l_CalAsgId);
1357: END IF;
1358: --
1359: wsh_util_core.api_post_call
1352: );
1353: --
1354: IF l_debug_on THEN
1355: WSH_DEBUG_SV.log(l_module_name, 'Return status from CAL. API', l_return_status);
1356: WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID', l_CalAsgId);
1357: END IF;
1358: --
1359: wsh_util_core.api_post_call
1360: (
1373: DELETE wsh_calendar_assignments
1374: WHERE calendar_assignment_id = check_site_calendar_rec.calendar_assignment_id;
1375: IF l_debug_on THEN
1376: --{
1377: WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID deleted',
1378: check_site_calendar_rec.calendar_assignment_id);
1379: WSH_DEBUG_SV.logmsg(l_module_name,'Deleted record(s) from wsh_calendar_assignments. Number of Rows deleted is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1380: --}
1381: END IF;
1375: IF l_debug_on THEN
1376: --{
1377: WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID deleted',
1378: check_site_calendar_rec.calendar_assignment_id);
1379: WSH_DEBUG_SV.logmsg(l_module_name,'Deleted record(s) from wsh_calendar_assignments. Number of Rows deleted is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1380: --}
1381: END IF;
1382:
1383: IF check_site_calendar_rec.freight_code IS NULL THEN
1404: --
1405: IF NOT p_site_merge THEN
1406: --{
1407: IF l_debug_on THEN
1408: wsh_debug_sv.logmsg(l_module_name, 'Calling InactivatePartySites', WSH_DEBUG_SV.C_PROC_LEVEL);
1409: END IF;
1410: --
1411: InactivatePartySites
1412: (
1416: x_return_status => l_return_status
1417: );
1418: --
1419: IF l_debug_on THEN
1420: wsh_debug_sv.log(l_module_name, 'Return Status from InactivatePartySites', l_return_status);
1421: END IF;
1422: --
1423: wsh_util_core.api_post_call
1424: (
1438: --}
1439: END IF;
1440: --
1441: IF l_debug_on THEN
1442: WSH_DEBUG_SV.pop(l_module_name);
1443: END IF;
1444: --
1445: EXCEPTION
1446: --
1449: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1450: --
1451: IF l_debug_on THEN
1452: --
1453: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
1454: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1455: --
1456: END IF;
1457: --
1450: --
1451: IF l_debug_on THEN
1452: --
1453: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
1454: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1455: --
1456: END IF;
1457: --
1458: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1460: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1461: --
1462: IF l_debug_on THEN
1463: --
1464: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1465: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1466: --
1467: END IF;
1468: --
1461: --
1462: IF l_debug_on THEN
1463: --
1464: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1465: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1466: --
1467: END IF;
1468: --
1469: WHEN OTHERS THEN
1471: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1472: wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Update_Non_PO_Entities');
1473: --
1474: IF l_debug_on THEN
1475: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1476: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1477: END IF;
1478: --
1479: END Update_Non_PO_Entities;
1472: wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Update_Non_PO_Entities');
1473: --
1474: IF l_debug_on THEN
1475: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1476: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1477: END IF;
1478: --
1479: END Update_Non_PO_Entities;
1480:
1722: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1723:
1724: IF l_debug_on IS NULL THEN
1725: --{
1726: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1727: --}
1728: END IF;
1729:
1730:
1729:
1730:
1731: IF l_debug_on THEN
1732: --{
1733: WSH_DEBUG_SV.push(l_module_name);
1734: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1735: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1736: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1737: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1730:
1731: IF l_debug_on THEN
1732: --{
1733: WSH_DEBUG_SV.push(l_module_name);
1734: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1735: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1736: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1737: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1738: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1731: IF l_debug_on THEN
1732: --{
1733: WSH_DEBUG_SV.push(l_module_name);
1734: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1735: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1736: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1737: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1738: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1739: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1732: --{
1733: WSH_DEBUG_SV.push(l_module_name);
1734: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1735: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1736: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1737: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1738: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1739: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1740: WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
1733: WSH_DEBUG_SV.push(l_module_name);
1734: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1735: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1736: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1737: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1738: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1739: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1740: WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
1741: --}
1734: WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1735: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1736: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1737: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1738: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1739: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1740: WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
1741: --}
1742: END IF;
1735: WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1736: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1737: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1738: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1739: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1740: WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
1741: --}
1742: END IF;
1743:
1736: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1737: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1738: WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1739: WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1740: WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
1741: --}
1742: END IF;
1743:
1744: wsh_util_core.enable_concurrent_log_print ;
1751: --{
1752: CLOSE check_vendor_active;
1753: IF l_debug_on THEN
1754: --{
1755: WSH_DEBUG_SV.logmsg(l_module_name,'Error : No Record exists in PO_VENDOR for vendor id : ' || p_from_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1756: --}
1757: END IF;
1758: fnd_message.set_name ( 'WSH', 'WSH_IB_VENDOR_NOT_EXISTS' );
1759: fnd_message.set_token( 'VENDOR_ID' , to_char(p_from_id) );
1761: wsh_util_core.printMsg( l_msg );
1762: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1763: --
1764: IF l_debug_on THEN
1765: WSH_DEBUG_SV.pop(l_module_name);
1766: END IF;
1767: --
1768: RETURN;
1769: --}
1780: END IF;
1781:
1782: IF l_debug_on THEN
1783: --{
1784: WSH_DEBUG_SV.log(l_module_name,'End date for Vendor ID : ' || p_from_id || ' is ',l_end_date_active, WSH_DEBUG_SV.C_STMT_LEVEL);
1785: WSH_DEBUG_SV.log(l_module_name,'L_SITE_MERGE = ',l_site_merge, WSH_DEBUG_SV.C_STMT_LEVEL);
1786: --}
1787: END IF;
1788:
1781:
1782: IF l_debug_on THEN
1783: --{
1784: WSH_DEBUG_SV.log(l_module_name,'End date for Vendor ID : ' || p_from_id || ' is ',l_end_date_active, WSH_DEBUG_SV.C_STMT_LEVEL);
1785: WSH_DEBUG_SV.log(l_module_name,'L_SITE_MERGE = ',l_site_merge, WSH_DEBUG_SV.C_STMT_LEVEL);
1786: --}
1787: END IF;
1788:
1789: -- Update for entities which are not dependent on the invoice/PO selection
1788:
1789: -- Update for entities which are not dependent on the invoice/PO selection
1790: IF l_debug_on THEN
1791: --{
1792: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NON_PO_ENTITIES',WSH_DEBUG_SV.C_PROC_LEVEL);
1793: --}
1794: END IF;
1795: --
1796: -- Update non-PO entities irrespective of calling mode
1826: --FETCH check_option INTO l_option;
1827: --CLOSE check_option;
1828: --
1829: --IF l_debug_on THEN
1830: --WSH_DEBUG_SV.logmsg(l_module_name,'l_OPTION = ' || l_option, WSH_DEBUG_SV.C_STMT_LEVEL);
1831: --END IF;
1832: --
1833:
1834: --IF (l_option= 'B' OR l_option = 'P') THEN
1838: FETCH check_po BULK COLLECT INTO l_delivery_list, l_dd_list;
1839: CLOSE check_po;
1840: IF l_debug_on THEN
1841: --{
1842: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Rows fetched from Cursor CHECK_PO = ' || l_delivery_list.count, WSH_DEBUG_SV.C_STMT_LEVEL);
1843: --}
1844: END IF;
1845:
1846: IF l_dd_list.COUNT > 0 THEN
1857: sysdate );
1858:
1859: IF l_debug_on THEN
1860: --{
1861: WSH_DEBUG_SV.logmsg(l_module_name,'Inserted records into wsh_wms_sync_tmp. Number of Rows inserted is ' || l_dd_list.count, WSH_DEBUG_SV.C_STMT_LEVEL);
1862: --}
1863: END IF;
1864:
1865: --
1875: IF SQL%ROWCOUNT <> l_dd_list.count THEN
1876: --{
1877: IF l_debug_on THEN
1878: --{
1879: WSH_DEBUG_SV.log(l_module_name, 'Updated WDD records with vendor/vendor site', p_to_id || ' - ' || p_To_site_id);
1880: WSH_DEBUG_SV.logmsg( l_module_name,'Out of ' || l_dd_list.count || ' delivery details, only ' || sql%rowcount || ' were updated.', WSH_DEBUG_SV.C_STMT_LEVEL);
1881: --}
1882: END IF;
1883: fnd_message.set_name ( 'WSH', 'WSH_IB_DLY_DET_UPDT_MISMATCH' );
1876: --{
1877: IF l_debug_on THEN
1878: --{
1879: WSH_DEBUG_SV.log(l_module_name, 'Updated WDD records with vendor/vendor site', p_to_id || ' - ' || p_To_site_id);
1880: WSH_DEBUG_SV.logmsg( l_module_name,'Out of ' || l_dd_list.count || ' delivery details, only ' || sql%rowcount || ' were updated.', WSH_DEBUG_SV.C_STMT_LEVEL);
1881: --}
1882: END IF;
1883: fnd_message.set_name ( 'WSH', 'WSH_IB_DLY_DET_UPDT_MISMATCH' );
1884: fnd_message.set_token( 'NUM_DETAILS_AFFECTED' , to_char(l_dd_list.count) );
1888: --}
1889: END IF;
1890: IF l_debug_on THEN
1891: --{
1892: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1893: --}
1894: END IF;
1895:
1896: --}
1906: --}
1907: END IF;
1908: --
1909: IF l_debug_on THEN
1910: WSH_DEBUG_SV.log(l_module_name,'Rows fetched from Cursor FIND_DELIVERY', l_delivery_rec_tbl.count);
1911: END IF;
1912:
1913: IF l_delivery_rec_tbl.COUNT > 0 THEN
1914: --{
1916: LOOP
1917: --{
1918: --
1919: IF l_debug_on THEN
1920: wsh_debug_sv.logmsg(l_module_name, '**********************************', WSH_DEBUG_SV.C_STMT_LEVEL);
1921: wsh_debug_sv.log(l_module_name, 'Processing delivery', l_delivery_rec_tbl(k).delivery_id);
1922: wsh_debug_sv.logmsg(l_module_name, '**********************************', WSH_DEBUG_SV.C_STMT_LEVEL);
1923: END IF;
1924: --
1917: --{
1918: --
1919: IF l_debug_on THEN
1920: wsh_debug_sv.logmsg(l_module_name, '**********************************', WSH_DEBUG_SV.C_STMT_LEVEL);
1921: wsh_debug_sv.log(l_module_name, 'Processing delivery', l_delivery_rec_tbl(k).delivery_id);
1922: wsh_debug_sv.logmsg(l_module_name, '**********************************', WSH_DEBUG_SV.C_STMT_LEVEL);
1923: END IF;
1924: --
1925: l_delivery_rec := l_delivery_rec_tbl(k);
1918: --
1919: IF l_debug_on THEN
1920: wsh_debug_sv.logmsg(l_module_name, '**********************************', WSH_DEBUG_SV.C_STMT_LEVEL);
1921: wsh_debug_sv.log(l_module_name, 'Processing delivery', l_delivery_rec_tbl(k).delivery_id);
1922: wsh_debug_sv.logmsg(l_module_name, '**********************************', WSH_DEBUG_SV.C_STMT_LEVEL);
1923: END IF;
1924: --
1925: l_delivery_rec := l_delivery_rec_tbl(k);
1926: l_dup :=NULL;
1936: l_ult_dropoff_loc_id;
1937: CLOSE check_duplicate_vendor;
1938: IF l_debug_on THEN
1939: --{
1940: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DUP =' || l_dup, WSH_DEBUG_SV.C_STMT_LEVEL);
1941: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_LOCATION_ID =' || l_location_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1942: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_STATUS_CODE =' || l_dlvy_status_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1943: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ROUTING_RESPONSE_ID =' || l_routing_response_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1944: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_NAME =' || l_dlvy_name, WSH_DEBUG_SV.C_STMT_LEVEL);
1937: CLOSE check_duplicate_vendor;
1938: IF l_debug_on THEN
1939: --{
1940: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DUP =' || l_dup, WSH_DEBUG_SV.C_STMT_LEVEL);
1941: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_LOCATION_ID =' || l_location_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1942: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_STATUS_CODE =' || l_dlvy_status_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1943: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ROUTING_RESPONSE_ID =' || l_routing_response_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1944: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_NAME =' || l_dlvy_name, WSH_DEBUG_SV.C_STMT_LEVEL);
1945: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ULT_DROPOFF_LOC_ID =' || l_ult_dropoff_loc_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1938: IF l_debug_on THEN
1939: --{
1940: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DUP =' || l_dup, WSH_DEBUG_SV.C_STMT_LEVEL);
1941: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_LOCATION_ID =' || l_location_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1942: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_STATUS_CODE =' || l_dlvy_status_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1943: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ROUTING_RESPONSE_ID =' || l_routing_response_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1944: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_NAME =' || l_dlvy_name, WSH_DEBUG_SV.C_STMT_LEVEL);
1945: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ULT_DROPOFF_LOC_ID =' || l_ult_dropoff_loc_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1946: --}
1939: --{
1940: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DUP =' || l_dup, WSH_DEBUG_SV.C_STMT_LEVEL);
1941: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_LOCATION_ID =' || l_location_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1942: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_STATUS_CODE =' || l_dlvy_status_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1943: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ROUTING_RESPONSE_ID =' || l_routing_response_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1944: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_NAME =' || l_dlvy_name, WSH_DEBUG_SV.C_STMT_LEVEL);
1945: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ULT_DROPOFF_LOC_ID =' || l_ult_dropoff_loc_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1946: --}
1947: END IF;
1940: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DUP =' || l_dup, WSH_DEBUG_SV.C_STMT_LEVEL);
1941: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_LOCATION_ID =' || l_location_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1942: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_STATUS_CODE =' || l_dlvy_status_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1943: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ROUTING_RESPONSE_ID =' || l_routing_response_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1944: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_NAME =' || l_dlvy_name, WSH_DEBUG_SV.C_STMT_LEVEL);
1945: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ULT_DROPOFF_LOC_ID =' || l_ult_dropoff_loc_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1946: --}
1947: END IF;
1948:
1941: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_LOCATION_ID =' || l_location_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1942: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_STATUS_CODE =' || l_dlvy_status_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1943: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ROUTING_RESPONSE_ID =' || l_routing_response_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1944: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_NAME =' || l_dlvy_name, WSH_DEBUG_SV.C_STMT_LEVEL);
1945: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ULT_DROPOFF_LOC_ID =' || l_ult_dropoff_loc_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1946: --}
1947: END IF;
1948:
1949:
1954: FETCH check_temp INTO l_temp;
1955: CLOSE check_temp;
1956: IF l_debug_on THEN
1957: --{
1958: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_TEMP : L_TEMP =' || l_temp, WSH_DEBUG_SV.C_STMT_LEVEL);
1959: --}
1960: END IF;
1961:
1962: IF ( l_temp IS NULL ) THEN
1967: FETCH check_detail BULK COLLECT INTO l_delivery_detail_tbl;
1968: CLOSE check_detail;
1969: IF l_debug_on THEN
1970: --{
1971: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Rows fetched from Cursor CHECK_DETAIL = ' || l_delivery_detail_tbl.count, WSH_DEBUG_SV.C_STMT_LEVEL);
1972: --}
1973: END IF;
1974:
1975: IF l_debug_on THEN
1973: END IF;
1974:
1975: IF l_debug_on THEN
1976: --{
1977: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.SPLIT_INBOUND_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
1978: --}
1979: END IF;
1980:
1981: l_new_delivery_id := NULL;
1987: p_caller => 'WSH_VENDOR_MERGE'
1988: );
1989: IF l_debug_on THEN
1990: --{
1991: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_INBOUND_UTIL_PKG.SPLIT_INBOUND_DELIVERY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
1992: WSH_DEBUG_SV.log(l_module_name, 'New Delivery ID', l_new_delivery_id);
1993: --}
1994: END IF;
1995:
1988: );
1989: IF l_debug_on THEN
1990: --{
1991: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_INBOUND_UTIL_PKG.SPLIT_INBOUND_DELIVERY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
1992: WSH_DEBUG_SV.log(l_module_name, 'New Delivery ID', l_new_delivery_id);
1993: --}
1994: END IF;
1995:
1996: wsh_util_core.api_post_call(
2003:
2004: --Update WSH_NEW_DELIVERIES with the merge to vendor
2005: IF l_debug_on THEN
2006: --{
2007: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2008: --}
2009: END IF;
2010:
2011: Update_new_delivery(
2021: x_return_status => l_return_status
2022: );
2023: IF l_debug_on THEN
2024: --{
2025: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2026: --}
2027: END IF;
2028:
2029: wsh_util_core.api_post_call(
2047: FETCH check_detail BULK COLLECT INTO l_delivery_detail_tbl;
2048: CLOSE check_detail;
2049: IF l_debug_on THEN
2050: --{
2051: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Rows fetched from Cursor CHECK_DETAIL = ' || l_delivery_detail_tbl.count, WSH_DEBUG_SV.C_STMT_LEVEL);
2052: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.SPLIT_INBOUND_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2053: --}
2054: END IF;
2055:
2048: CLOSE check_detail;
2049: IF l_debug_on THEN
2050: --{
2051: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Rows fetched from Cursor CHECK_DETAIL = ' || l_delivery_detail_tbl.count, WSH_DEBUG_SV.C_STMT_LEVEL);
2052: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.SPLIT_INBOUND_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2053: --}
2054: END IF;
2055:
2056: Wsh_inbound_util_pkg.split_inbound_delivery(
2061: p_caller => 'WSH_VENDOR_MERGE'
2062: );
2063: IF l_debug_on THEN
2064: --{
2065: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_INBOUND_UTIL_PKG.SPLIT_INBOUND_DELIVERY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2066: --}
2067: END IF;
2068:
2069: wsh_util_core.api_post_call(
2097: --
2098: --Update WSH_NEW_DELIVERIES with the merge to vendor
2099: IF l_debug_on THEN
2100: --{
2101: --wsh_debug_sv.log(l_module_name, 'l_dup', l_dup);
2102: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2103: --}
2104: END IF;
2105:
2098: --Update WSH_NEW_DELIVERIES with the merge to vendor
2099: IF l_debug_on THEN
2100: --{
2101: --wsh_debug_sv.log(l_module_name, 'l_dup', l_dup);
2102: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2103: --}
2104: END IF;
2105:
2106: Update_new_delivery(
2116: x_return_status => l_return_status
2117: );
2118: IF l_debug_on THEN
2119: --{
2120: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2121: --}
2122: END IF;
2123:
2124: wsh_util_core.api_post_call(
2140: AND shipment_header_id = l_delivery_rec.delivery_id
2141: AND supplier_id = p_from_id;
2142: IF l_debug_on THEN
2143: --{
2144: WSH_DEBUG_SV.log(l_module_name, 'Updated ROUTING_RESP record for', l_delivery_rec.delivery_id);
2145: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_inbound_txn_history. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
2146: --}
2147: END IF;
2148:
2141: AND supplier_id = p_from_id;
2142: IF l_debug_on THEN
2143: --{
2144: WSH_DEBUG_SV.log(l_module_name, 'Updated ROUTING_RESP record for', l_delivery_rec.delivery_id);
2145: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_inbound_txn_history. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
2146: --}
2147: END IF;
2148:
2149: --}
2152: --}
2153: END IF; -- if l_dup ='Y'
2154: --
2155: IF l_debug_on THEN
2156: wsh_debug_sv.log(l_module_name, 'Updating vendor info. on containers assigned to delivery', l_chk_delivery_id);
2157: END IF;
2158: --
2159: UPDATE wsh_delivery_details
2160: SET vendor_id = p_to_id,
2172: AND delivery_id = l_chk_delivery_id
2173: );
2174: --
2175: IF l_debug_on THEN
2176: wsh_debug_sv.log(l_module_name, 'No. of container records updated', SQL%ROWCOUNT);
2177: END IF;
2178: --
2179: OPEN dlvy_rr_csr(l_chk_delivery_id);
2180: FETCH dlvy_rr_csr BULK COLLECT INTO l_dlvy_rr_rec_tbl;
2181: CLOSE dlvy_rr_csr;
2182: --
2183: IF l_debug_on THEN
2184: --{
2185: WSH_DEBUG_SV.logmsg(l_module_name,'Count of Rows fetched from Cursor DLVY_RR_CSR = ' || l_dlvy_rr_rec_tbl.count, WSH_DEBUG_SV.C_STMT_LEVEL);
2186: --}
2187: END IF;
2188:
2189: IF l_dlvy_rr_rec_tbl.COUNT > 0 THEN
2209: l1_parent_shipment_header_id;
2210: CLOSE chk_rreq_csr;
2211: IF l_debug_on THEN
2212: --{
2213: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_TRANSACTION_ID =' || l1_transaction_id, WSH_DEBUG_SV.C_STMT_LEVEL);
2214: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_REVISION_NUMBER =' || l1_revision_number, WSH_DEBUG_SV.C_STMT_LEVEL);
2215: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_PARENT_SHIPMENT_HEADER_ID =' || l1_parent_shipment_header_id, WSH_DEBUG_SV.C_STMT_LEVEL);
2216: --}
2217: END IF;
2210: CLOSE chk_rreq_csr;
2211: IF l_debug_on THEN
2212: --{
2213: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_TRANSACTION_ID =' || l1_transaction_id, WSH_DEBUG_SV.C_STMT_LEVEL);
2214: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_REVISION_NUMBER =' || l1_revision_number, WSH_DEBUG_SV.C_STMT_LEVEL);
2215: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_PARENT_SHIPMENT_HEADER_ID =' || l1_parent_shipment_header_id, WSH_DEBUG_SV.C_STMT_LEVEL);
2216: --}
2217: END IF;
2218:
2211: IF l_debug_on THEN
2212: --{
2213: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_TRANSACTION_ID =' || l1_transaction_id, WSH_DEBUG_SV.C_STMT_LEVEL);
2214: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_REVISION_NUMBER =' || l1_revision_number, WSH_DEBUG_SV.C_STMT_LEVEL);
2215: WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_PARENT_SHIPMENT_HEADER_ID =' || l1_parent_shipment_header_id, WSH_DEBUG_SV.C_STMT_LEVEL);
2216: --}
2217: END IF;
2218:
2219: IF l1_revision_number = l_dlvy_rr_rec.revision_number
2281: --}
2282: END IF;
2283: --
2284: IF l_debug_on THEN
2285: WSH_DEBUG_SV.pop(l_module_name);
2286: END IF;
2287: --
2288: RETURN;
2289: --}
2301: IF l_new_rr_id IS NULL THEN
2302: --{
2303: IF l_debug_on THEN
2304: --{
2305: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_TXN_HISTORY_PKG.GET_TXN_HISTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
2306: --}
2307: END IF;
2308: WSH_INBOUND_TXN_HISTORY_PKG.get_txn_history(
2309: p_transaction_id => l_dlvy_rr_rec.routing_req_id,
2311: x_return_status => l_return_status
2312: );
2313: IF l_debug_on THEN
2314: --{
2315: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_INBOUND_TXN_HISTORY_PKG.GET_TXN_HISTORY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2316: --}
2317: END IF;
2318: wsh_util_core.api_post_call(
2319: p_return_status => l_return_status,
2326: l_txn_history_rec.supplier_id := p_to_id;
2327: --
2328: IF l_debug_on THEN
2329: --{
2330: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_TXN_HISTORY_PKG.CREATE_TXN_HISTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
2331: --}
2332: END IF;
2333: WSH_INBOUND_TXN_HISTORY_PKG.create_txn_history(
2334: p_txn_history_rec => l_txn_history_rec,
2336: x_return_status => l_return_status
2337: );
2338: IF l_debug_on THEN
2339: --
2340: WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_INBOUND_TXN_HISTORY_PKG.CREATE_TXN_HISTORY is ' , l_return_status);
2341: WSH_DEBUG_SV.log(l_module_name, 'New Txn ID', l_new_rr_id);
2342:
2343: --
2344: END IF;
2337: );
2338: IF l_debug_on THEN
2339: --
2340: WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_INBOUND_TXN_HISTORY_PKG.CREATE_TXN_HISTORY is ' , l_return_status);
2341: WSH_DEBUG_SV.log(l_module_name, 'New Txn ID', l_new_rr_id);
2342:
2343: --
2344: END IF;
2345: wsh_util_core.api_post_call(
2382: );
2383: --
2384: IF l_debug_on THEN
2385: --
2386: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_XC_UTIL.LOG_EXCEPTION is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2387: --
2388: END IF;
2389: --
2390: wsh_util_core.api_post_call(
2398: --}
2399: END IF;--IF l_prev_old_rr_id = l_dlvy_rr_rec.routing_req_id
2400: --
2401: IF l_debug_on THEN
2402: wsh_debug_sv.log(l_module_name, 'WDD ID to update', l_dlvy_rr_rec.delivery_detail_id);
2403: wsh_debug_sv.log(l_module_name, 'Routing Req ID to update with', l_new_rr_id);
2404: END IF;
2405: --
2406: UPDATE wsh_delivery_details
2399: END IF;--IF l_prev_old_rr_id = l_dlvy_rr_rec.routing_req_id
2400: --
2401: IF l_debug_on THEN
2402: wsh_debug_sv.log(l_module_name, 'WDD ID to update', l_dlvy_rr_rec.delivery_detail_id);
2403: wsh_debug_sv.log(l_module_name, 'Routing Req ID to update with', l_new_rr_id);
2404: END IF;
2405: --
2406: UPDATE wsh_delivery_details
2407: SET routing_req_Id = l_new_rr_id,
2411: WHERE delivery_detail_id = l_dlvy_rr_rec.delivery_detail_id;
2412: --
2413: IF l_debug_on THEN
2414: --{
2415: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
2416: --}
2417: END IF;
2418:
2419: l_prev_old_rr_id := l_dlvy_rr_rec.routing_req_id;
2448: l_numRowsUpdated := SQL%ROWCOUNT;
2449: --
2450: IF l_debug_on THEN
2451: --{
2452: WSH_DEBUG_SV.log(l_module_name, 'Updated ASN/RECEIPT records with vendor', p_to_id);
2453: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_inbound_txn_history. Number of Rows updated is ' || l_numRowsUpdated, WSH_DEBUG_SV.C_STMT_LEVEL);
2454: --}
2455: END IF;
2456: --}
2449: --
2450: IF l_debug_on THEN
2451: --{
2452: WSH_DEBUG_SV.log(l_module_name, 'Updated ASN/RECEIPT records with vendor', p_to_id);
2453: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_inbound_txn_history. Number of Rows updated is ' || l_numRowsUpdated, WSH_DEBUG_SV.C_STMT_LEVEL);
2454: --}
2455: END IF;
2456: --}
2457: END IF;
2480: l_action_prms.caller := 'WSH_PUB';
2481: --
2482: IF l_debug_on THEN
2483: --{
2484: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERIES_GRP.DELIVERY_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
2485: WSH_DEBUG_SV.log(l_module_name, 'l_dlvy_rr_tbl.COUNT', l_dlvy_rr_tbl.COUNT);
2486: --}
2487: END IF;
2488: --
2481: --
2482: IF l_debug_on THEN
2483: --{
2484: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERIES_GRP.DELIVERY_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
2485: WSH_DEBUG_SV.log(l_module_name, 'l_dlvy_rr_tbl.COUNT', l_dlvy_rr_tbl.COUNT);
2486: --}
2487: END IF;
2488: --
2489: IF l_dlvy_rr_tbl.COUNT > 0 THEN
2501: x_msg_data => l_msg
2502: );
2503: IF l_debug_on THEN
2504: --{
2505: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_DELIVERIES_GRP.DELIVERY_ACTION is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2506: --}
2507: END IF;
2508:
2509: wsh_util_core.api_post_call(
2529: END LOOP;
2530:
2531: IF l_debug_on THEN
2532: --{
2533: WSH_DEBUG_SV.logmsg( l_module_name,'Calling program unit WSH_WV_UTILS.DELIVERY_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL );
2534: WSH_DEBUG_SV.log(l_module_name, 'l_dlvy_tbl.COUNT', l_dlvy_tbl.COUNT);
2535: FOR i IN 1..l_dlvy_tbl.COUNT LOOP
2536: wsh_debug_sv.log(l_module_name, 'l_dlvy_tbl(i)', l_dlvy_tbl(i));
2537: END LOOP;
2530:
2531: IF l_debug_on THEN
2532: --{
2533: WSH_DEBUG_SV.logmsg( l_module_name,'Calling program unit WSH_WV_UTILS.DELIVERY_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL );
2534: WSH_DEBUG_SV.log(l_module_name, 'l_dlvy_tbl.COUNT', l_dlvy_tbl.COUNT);
2535: FOR i IN 1..l_dlvy_tbl.COUNT LOOP
2536: wsh_debug_sv.log(l_module_name, 'l_dlvy_tbl(i)', l_dlvy_tbl(i));
2537: END LOOP;
2538: --
2532: --{
2533: WSH_DEBUG_SV.logmsg( l_module_name,'Calling program unit WSH_WV_UTILS.DELIVERY_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL );
2534: WSH_DEBUG_SV.log(l_module_name, 'l_dlvy_tbl.COUNT', l_dlvy_tbl.COUNT);
2535: FOR i IN 1..l_dlvy_tbl.COUNT LOOP
2536: wsh_debug_sv.log(l_module_name, 'l_dlvy_tbl(i)', l_dlvy_tbl(i));
2537: END LOOP;
2538: --
2539: --}
2540: END IF;
2548: x_return_status => l_return_status
2549: );
2550: IF l_debug_on THEN
2551: --{
2552: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_WV_UTILS.DELIVERY_WEIGHT_VOLUME is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2553: --}
2554: END IF;
2555:
2556: wsh_util_core.api_post_call(
2633:
2634: --End of Vendor Merge for RTV Transations
2635:
2636: IF l_debug_on THEN
2637: WSH_DEBUG_SV.log(l_module_name, 'Final return status', x_return_status);
2638: WSH_DEBUG_SV.pop(l_module_name);
2639: END IF;
2640:
2641: EXCEPTION
2634: --End of Vendor Merge for RTV Transations
2635:
2636: IF l_debug_on THEN
2637: WSH_DEBUG_SV.log(l_module_name, 'Final return status', x_return_status);
2638: WSH_DEBUG_SV.pop(l_module_name);
2639: END IF;
2640:
2641: EXCEPTION
2642: --
2646: ROLLBACK TO WSH_Vendor_Merge;
2647: --
2648: IF l_debug_on THEN
2649: --
2650: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
2651: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2652: --
2653: END IF;
2654: --
2647: --
2648: IF l_debug_on THEN
2649: --
2650: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
2651: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2652: --
2653: END IF;
2654: --
2655: IF NOT(wsh_util_core.g_call_fte_load_tender_api) THEN
2670: ROLLBACK TO WSH_Vendor_Merge;
2671: --
2672: IF l_debug_on THEN
2673: --
2674: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2675: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2676: --
2677: END IF;
2678: --
2671: --
2672: IF l_debug_on THEN
2673: --
2674: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2675: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2676: --
2677: END IF;
2678: --
2679: IF NOT(wsh_util_core.g_call_fte_load_tender_api) THEN
2691: wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Vendor_Merge');
2692: --
2693: IF l_debug_on THEN
2694: --
2695: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2696: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2697: --
2698: END IF;
2699: --
2692: --
2693: IF l_debug_on THEN
2694: --
2695: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2696: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2697: --
2698: END IF;
2699: --
2700: IF NOT(wsh_util_core.g_call_fte_load_tender_api) THEN
2773: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2774: --
2775: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2776: IF l_debug_on IS NULL THEN
2777: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2778: END IF;
2779: --
2780: IF l_debug_on THEN
2781: --
2778: END IF;
2779: --
2780: IF l_debug_on THEN
2781: --
2782: WSH_DEBUG_SV.push(l_module_name);
2783: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_ID', p_from_vendor_id);
2784: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_ID', p_to_vendor_id);
2785: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id);
2786: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id);
2779: --
2780: IF l_debug_on THEN
2781: --
2782: WSH_DEBUG_SV.push(l_module_name);
2783: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_ID', p_from_vendor_id);
2784: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_ID', p_to_vendor_id);
2785: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id);
2786: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id);
2787: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_SITE_ID', p_from_vendor_site_id);
2780: IF l_debug_on THEN
2781: --
2782: WSH_DEBUG_SV.push(l_module_name);
2783: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_ID', p_from_vendor_id);
2784: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_ID', p_to_vendor_id);
2785: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id);
2786: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id);
2787: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_SITE_ID', p_from_vendor_site_id);
2788: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_SITE_ID', p_to_vendor_site_id);
2781: --
2782: WSH_DEBUG_SV.push(l_module_name);
2783: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_ID', p_from_vendor_id);
2784: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_ID', p_to_vendor_id);
2785: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id);
2786: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id);
2787: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_SITE_ID', p_from_vendor_site_id);
2788: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_SITE_ID', p_to_vendor_site_id);
2789: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_SITE_ID', p_from_party_site_id);
2782: WSH_DEBUG_SV.push(l_module_name);
2783: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_ID', p_from_vendor_id);
2784: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_ID', p_to_vendor_id);
2785: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id);
2786: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id);
2787: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_SITE_ID', p_from_vendor_site_id);
2788: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_SITE_ID', p_to_vendor_site_id);
2789: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_SITE_ID', p_from_party_site_id);
2790: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTYSITE_ID', p_to_partysite_id);
2783: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_ID', p_from_vendor_id);
2784: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_ID', p_to_vendor_id);
2785: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id);
2786: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id);
2787: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_SITE_ID', p_from_vendor_site_id);
2788: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_SITE_ID', p_to_vendor_site_id);
2789: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_SITE_ID', p_from_party_site_id);
2790: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTYSITE_ID', p_to_partysite_id);
2791: WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
2784: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_ID', p_to_vendor_id);
2785: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id);
2786: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id);
2787: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_SITE_ID', p_from_vendor_site_id);
2788: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_SITE_ID', p_to_vendor_site_id);
2789: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_SITE_ID', p_from_party_site_id);
2790: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTYSITE_ID', p_to_partysite_id);
2791: WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
2792: --
2785: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id);
2786: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id);
2787: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_SITE_ID', p_from_vendor_site_id);
2788: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_SITE_ID', p_to_vendor_site_id);
2789: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_SITE_ID', p_from_party_site_id);
2790: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTYSITE_ID', p_to_partysite_id);
2791: WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
2792: --
2793: END IF;
2786: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id);
2787: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_SITE_ID', p_from_vendor_site_id);
2788: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_SITE_ID', p_to_vendor_site_id);
2789: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_SITE_ID', p_from_party_site_id);
2790: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTYSITE_ID', p_to_partysite_id);
2791: WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
2792: --
2793: END IF;
2794:
2787: WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_SITE_ID', p_from_vendor_site_id);
2788: WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_SITE_ID', p_to_vendor_site_id);
2789: WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_SITE_ID', p_from_party_site_id);
2790: WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTYSITE_ID', p_to_partysite_id);
2791: WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
2792: --
2793: END IF;
2794:
2795: -- RTV Changes {
2827: --
2828: END IF;
2829: --
2830: IF l_debug_on THEN
2831: WSH_DEBUG_SV.log(l_module_name, 'l_fromPartyId', l_fromPartyId);
2832: WSH_DEBUG_SV.log(l_module_name, 'l_toPartyId', l_toPartyId);
2833: END IF;
2834: --
2835: -- Now call the core Vendor Merge routine to update WSH data
2828: END IF;
2829: --
2830: IF l_debug_on THEN
2831: WSH_DEBUG_SV.log(l_module_name, 'l_fromPartyId', l_fromPartyId);
2832: WSH_DEBUG_SV.log(l_module_name, 'l_toPartyId', l_toPartyId);
2833: END IF;
2834: --
2835: -- Now call the core Vendor Merge routine to update WSH data
2836: --
2846: x_return_status => l_return_status
2847: );
2848: --
2849: IF l_debug_on THEN
2850: WSH_DEBUG_SV.logmsg(l_module_name, 'After calling core vendor_merge API', WSH_DEBUG_SV.C_STMT_LEVEL);
2851: WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
2852: END IF;
2853: --
2854: -- For AP, we interpret 'W' as 'S' status.
2847: );
2848: --
2849: IF l_debug_on THEN
2850: WSH_DEBUG_SV.logmsg(l_module_name, 'After calling core vendor_merge API', WSH_DEBUG_SV.C_STMT_LEVEL);
2851: WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
2852: END IF;
2853: --
2854: -- For AP, we interpret 'W' as 'S' status.
2855: --
2866: p_encoded => FND_API.G_FALSE
2867: );
2868: --
2869: IF l_debug_on THEN
2870: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
2871: WSH_DEBUG_SV.pop(l_module_name);
2872: END IF;
2873: --}
2874: EXCEPTION
2867: );
2868: --
2869: IF l_debug_on THEN
2870: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
2871: WSH_DEBUG_SV.pop(l_module_name);
2872: END IF;
2873: --}
2874: EXCEPTION
2875: -- RTV Changes {
2875: -- RTV Changes {
2876: WHEN RTV_Exception THEN
2877: --
2878: IF ( l_debug_on ) THEN
2879: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RTV_Exception');
2880: END IF;
2881: --
2882: RAISE;
2883: --
2887: --
2888: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2889: --
2890: IF l_debug_on THEN
2891: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
2892: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2893: END IF;
2894: --
2895: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2888: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2889: --
2890: IF l_debug_on THEN
2891: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
2892: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2893: END IF;
2894: --
2895: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2896: --
2896: --
2897: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2898: --
2899: IF l_debug_on THEN
2900: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2901: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2902: END IF;
2903: --
2904: WHEN OTHERS THEN
2897: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2898: --
2899: IF l_debug_on THEN
2900: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2901: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2902: END IF;
2903: --
2904: WHEN OTHERS THEN
2905: --
2907: WSH_UTIL_CORE.ADD_MESSAGE(l_return_status, l_module_name);
2908: WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_VENDOR_PARTY_MERGE_PKG.VENDOR_PARTY_MERGE',l_module_name);
2909: --
2910: IF l_debug_on THEN
2911: WSH_DEBUG_SV.log(l_module_name, 'Unexpected error', substrb(sqlerrm, 1, 200));
2912: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
2913: WSH_DEBUG_SV.pop(l_module_name);
2914: END IF;
2915: --
2908: WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_VENDOR_PARTY_MERGE_PKG.VENDOR_PARTY_MERGE',l_module_name);
2909: --
2910: IF l_debug_on THEN
2911: WSH_DEBUG_SV.log(l_module_name, 'Unexpected error', substrb(sqlerrm, 1, 200));
2912: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
2913: WSH_DEBUG_SV.pop(l_module_name);
2914: END IF;
2915: --
2916: END Vendor_Party_Merge;
2909: --
2910: IF l_debug_on THEN
2911: WSH_DEBUG_SV.log(l_module_name, 'Unexpected error', substrb(sqlerrm, 1, 200));
2912: WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
2913: WSH_DEBUG_SV.pop(l_module_name);
2914: END IF;
2915: --
2916: END Vendor_Party_Merge;
2917: FUNCTION getTimeStamp RETURN VARCHAR2
2964: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2965: --
2966: IF l_debug_on IS NULL
2967: THEN
2968: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2969: END IF;
2970: --
2971: IF l_debug_on THEN
2972: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Get_Delivery_Hash()+' || getTimeStamp );
2968: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2969: END IF;
2970: --
2971: IF l_debug_on THEN
2972: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Get_Delivery_Hash()+' || getTimeStamp );
2973: END IF;
2974: --
2975:
2976: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2995: WSH_UTIL_CORE.G_RET_STS_WARNING) )
2996: THEN
2997: --
2998: IF ( l_debug_on ) THEN
2999: WSH_DEBUG_SV.logmsg(l_module_name,'Error returned from API Create_Hash');
3000: END IF;
3001: --
3002: RAISE Update_Hash_Exp;
3003: END IF;
3013: END LOOP;
3014:
3015: --
3016: IF l_debug_on THEN
3017: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Get_Delivery_Hash()+' || getTimeStamp );
3018: END IF;
3019: --
3020: EXCEPTION
3021: WHEN Update_Hash_Exp THEN
3021: WHEN Update_Hash_Exp THEN
3022: x_return_status := l_return_status;
3023: --
3024: IF ( l_debug_on ) THEN
3025: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Get_Delivery_Hash()+ Update_Hash_Exp - ' || getTimeStamp );
3026: END IF;
3027: --
3028: WHEN OTHERS THEN
3029: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3028: WHEN OTHERS THEN
3029: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3030: --
3031: IF ( l_debug_on ) THEN
3032: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Get_Delivery_Hash()+ Others - ' || getTimeStamp );
3033: WSH_DEBUG_SV.logmsg(l_module_name,'Error Mesg : ' || SQLERRM );
3034: END IF;
3035: --
3036: END Get_Delivery_Hash;
3029: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3030: --
3031: IF ( l_debug_on ) THEN
3032: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Get_Delivery_Hash()+ Others - ' || getTimeStamp );
3033: WSH_DEBUG_SV.logmsg(l_module_name,'Error Mesg : ' || SQLERRM );
3034: END IF;
3035: --
3036: END Get_Delivery_Hash;
3037:
3083: --
3084: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3085: --
3086: IF l_debug_on IS NULL THEN
3087: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3088: END IF;
3089:
3090: --
3091: IF l_debug_on THEN
3088: END IF;
3089:
3090: --
3091: IF l_debug_on THEN
3092: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Weight_Volume()+' || getTimeStamp );
3093: END IF;
3094:
3095: IF ( p_entity_type in ( 'CONT', 'DEL-CONT' ) )
3096: THEN
3127: WSH_UTIL_CORE.G_RET_STS_WARNING) )
3128: THEN
3129: --
3130: IF ( l_debug_on ) THEN
3131: WSH_DEBUG_SV.logmsg(l_module_name,'API WSH_WV_UTILS.DD_WV_Post_Process returned error');
3132: END IF;
3133: --
3134: RAISE Weight_Volume_Exp;
3135: END IF;
3158: WSH_UTIL_CORE.G_RET_STS_WARNING) )
3159: THEN
3160: --
3161: IF ( l_debug_on ) THEN
3162: WSH_DEBUG_SV.logmsg(l_module_name,'API WSH_WV_UTILS.Del_WV_Post_Process returned error');
3163: END IF;
3164: --
3165: RAISE Weight_Volume_Exp;
3166: END IF;
3169: END IF;
3170:
3171: --
3172: IF ( l_debug_on ) THEN
3173: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Weight_Volume()+' || getTimeStamp );
3174: END IF;
3175: --
3176:
3177: EXCEPTION
3178: WHEN Weight_Volume_Exp THEN
3179: x_return_status := l_return_status;
3180: --
3181: IF ( l_debug_on ) THEN
3182: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Weight_Volume()+ Weight_Volume_Exp - ' || getTimeStamp );
3183: END IF;
3184: --
3185:
3186: WHEN OTHERS THEN
3186: WHEN OTHERS THEN
3187: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3188: --
3189: IF ( l_debug_on ) THEN
3190: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Weight_Volume()+ Others - ' || getTimeStamp);
3191: WSH_DEBUG_SV.logmsg(l_module_name,'Error Mesg : ' || sqlerrm );
3192: END IF;
3193: --
3194: END Adjust_Weight_Volume;
3187: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3188: --
3189: IF ( l_debug_on ) THEN
3190: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Weight_Volume()+ Others - ' || getTimeStamp);
3191: WSH_DEBUG_SV.logmsg(l_module_name,'Error Mesg : ' || sqlerrm );
3192: END IF;
3193: --
3194: END Adjust_Weight_Volume;
3195: -- ===============================================================================
3238: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3239: --
3240: IF l_debug_on IS NULL
3241: THEN
3242: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3243: END IF;
3244: --
3245: --
3246: IF l_debug_on THEN
3243: END IF;
3244: --
3245: --
3246: IF l_debug_on THEN
3247: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Parent_WV()+' || getTimeStamp );
3248: END IF;
3249: --
3250:
3251: -- Weight/Volume adjustments
3278:
3279: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3280: --
3281: IF ( l_debug_on ) THEN
3282: WSH_DEBUG_SV.logmsg(l_module_name,'API WSH_SHIPPING_PARAMS_PVT.Get returned error');
3283: END IF;
3284: --
3285: RAISE Weight_Volume_Exp;
3286: END IF;
3310: WSH_UTIL_CORE.G_RET_STS_WARNING) )
3311: THEN
3312: --
3313: IF ( l_debug_on ) THEN
3314: WSH_DEBUG_SV.logmsg(l_module_name,'API WSH_WV_UTILS.Adjust_Parent_WV returned error');
3315: END IF;
3316: --
3317: RAISE Weight_Volume_Exp;
3318: END IF;
3323: END LOOP;
3324:
3325: --
3326: IF ( l_debug_on ) THEN
3327: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Parent_WV()+' || getTimeStamp );
3328: END IF;
3329: --
3330:
3331: EXCEPTION
3332: WHEN Weight_Volume_Exp THEN
3333: x_return_status := l_return_status;
3334: --
3335: IF ( l_debug_on ) THEN
3336: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Parent_WV()+ Weight_Volume_Exp - ' || getTimeStamp );
3337: END IF;
3338: --
3339:
3340: WHEN OTHERS THEN
3340: WHEN OTHERS THEN
3341: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3342: --
3343: IF ( l_debug_on ) THEN
3344: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Parent_WV()+ Others - ' || getTimeStamp );
3345: WSH_DEBUG_SV.logmsg(l_module_name,'Error Mesg : ' || sqlerrm );
3346: END IF;
3347: --
3348: END Adjust_Parent_WV;
3341: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3342: --
3343: IF ( l_debug_on ) THEN
3344: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Parent_WV()+ Others - ' || getTimeStamp );
3345: WSH_DEBUG_SV.logmsg(l_module_name,'Error Mesg : ' || sqlerrm );
3346: END IF;
3347: --
3348: END Adjust_Parent_WV;
3349:
3593: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3594: --
3595:
3596: IF l_debug_on IS NULL THEN
3597: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3598: END IF;
3599: --
3600: IF l_debug_on THEN
3601: WSH_DEBUG_SV.push(l_module_name);
3597: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3598: END IF;
3599: --
3600: IF l_debug_on THEN
3601: WSH_DEBUG_SV.push(l_module_name);
3602: WSH_DEBUG_SV.log(l_module_name, 'p_from_vendor_id', p_from_vendor_id);
3603: WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
3604: WSH_DEBUG_SV.log(l_module_name, 'p_from_site_id', p_from_site_id);
3605: WSH_DEBUG_SV.log(l_module_name, 'p_to_site_id', p_to_site_id);
3598: END IF;
3599: --
3600: IF l_debug_on THEN
3601: WSH_DEBUG_SV.push(l_module_name);
3602: WSH_DEBUG_SV.log(l_module_name, 'p_from_vendor_id', p_from_vendor_id);
3603: WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
3604: WSH_DEBUG_SV.log(l_module_name, 'p_from_site_id', p_from_site_id);
3605: WSH_DEBUG_SV.log(l_module_name, 'p_to_site_id', p_to_site_id);
3606: END IF;
3599: --
3600: IF l_debug_on THEN
3601: WSH_DEBUG_SV.push(l_module_name);
3602: WSH_DEBUG_SV.log(l_module_name, 'p_from_vendor_id', p_from_vendor_id);
3603: WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
3604: WSH_DEBUG_SV.log(l_module_name, 'p_from_site_id', p_from_site_id);
3605: WSH_DEBUG_SV.log(l_module_name, 'p_to_site_id', p_to_site_id);
3606: END IF;
3607: --
3600: IF l_debug_on THEN
3601: WSH_DEBUG_SV.push(l_module_name);
3602: WSH_DEBUG_SV.log(l_module_name, 'p_from_vendor_id', p_from_vendor_id);
3603: WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
3604: WSH_DEBUG_SV.log(l_module_name, 'p_from_site_id', p_from_site_id);
3605: WSH_DEBUG_SV.log(l_module_name, 'p_to_site_id', p_to_site_id);
3606: END IF;
3607: --
3608: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3601: WSH_DEBUG_SV.push(l_module_name);
3602: WSH_DEBUG_SV.log(l_module_name, 'p_from_vendor_id', p_from_vendor_id);
3603: WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
3604: WSH_DEBUG_SV.log(l_module_name, 'p_from_site_id', p_from_site_id);
3605: WSH_DEBUG_SV.log(l_module_name, 'p_to_site_id', p_to_site_id);
3606: END IF;
3607: --
3608: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3609: OPEN get_loc_for_site ( p_from_vendor_id , p_from_site_id );
3610: FETCH get_loc_for_site INTO l_from_location_id, l_from_party_site_use_id;
3611: --
3612: IF get_loc_for_site%NOTFOUND THEN
3613: IF l_debug_on THEN
3614: WSH_DEBUG_SV.log(l_module_name, 'From vendor details not found' );
3615: CLOSE get_loc_for_site;
3616: raise FND_API.G_EXC_ERROR;
3617: END IF;
3618: END IF;
3622: FETCH get_loc_for_site INTO l_to_location_id, l_to_party_site_use_id;
3623: --
3624: IF get_loc_for_site%NOTFOUND THEN
3625: IF l_debug_on THEN
3626: WSH_DEBUG_SV.log(l_module_name, 'To vendor details not found');
3627: END IF;
3628: l_to_party_site_use_id := l_from_party_site_use_id;
3629: l_to_location_id := l_from_location_id;
3630: END IF;
3631: --
3632: CLOSE get_loc_for_site;
3633:
3634: IF l_debug_on THEN
3635: WSH_DEBUG_SV.logmsg(l_module_name,'Performing Shipping Vendor check');
3636: END IF;
3637: open c_check_vendor_wdd(p_from_vendor_id);
3638: fetch c_check_vendor_wdd into l_rec_exists;
3639: if (c_check_vendor_wdd%NOTFOUND) then
3637: open c_check_vendor_wdd(p_from_vendor_id);
3638: fetch c_check_vendor_wdd into l_rec_exists;
3639: if (c_check_vendor_wdd%NOTFOUND) then
3640: IF l_debug_on THEN
3641: WSH_DEBUG_SV.logmsg(l_module_name,' There exist no RTV delivery detail records in Shipping.');
3642: END IF;
3643: l_no_rtv_del_details := 1;
3644: end if;
3645: close c_check_vendor_wdd;
3647: open c_check_vendor_wnd(p_from_vendor_id);
3648: fetch c_check_vendor_wnd into l_rec_exists;
3649: if (c_check_vendor_wnd%NOTFOUND) then
3650: IF l_debug_on THEN
3651: WSH_DEBUG_SV.logmsg(l_module_name,' There exist no RTV delivery records in Shipping.');
3652: END IF;
3653: IF l_no_rtv_del_details = 1 THEN
3654: close c_check_vendor_wnd;
3655: WSH_DEBUG_SV.pop(l_module_name);
3651: WSH_DEBUG_SV.logmsg(l_module_name,' There exist no RTV delivery records in Shipping.');
3652: END IF;
3653: IF l_no_rtv_del_details = 1 THEN
3654: close c_check_vendor_wnd;
3655: WSH_DEBUG_SV.pop(l_module_name);
3656: RETURN;
3657: END IF ;
3658: end if;
3659: close c_check_vendor_wnd;
3658: end if;
3659: close c_check_vendor_wnd;
3660:
3661: IF l_debug_on THEN
3662: WSH_DEBUG_SV.log(l_module_name, 'l_from_location_id', l_from_location_id);
3663: WSH_DEBUG_SV.log(l_module_name, 'l_from_party_site_use_id', l_from_party_site_use_id);
3664: WSH_DEBUG_SV.log(l_module_name, 'l_to_location_id', l_to_location_id);
3665: WSH_DEBUG_SV.log(l_module_name, 'l_to_party_site_use_id', l_to_party_site_use_id);
3666: WSH_DEBUG_SV.logmsg(l_module_name, 'Start processing for Shipped Delivery Details');
3659: close c_check_vendor_wnd;
3660:
3661: IF l_debug_on THEN
3662: WSH_DEBUG_SV.log(l_module_name, 'l_from_location_id', l_from_location_id);
3663: WSH_DEBUG_SV.log(l_module_name, 'l_from_party_site_use_id', l_from_party_site_use_id);
3664: WSH_DEBUG_SV.log(l_module_name, 'l_to_location_id', l_to_location_id);
3665: WSH_DEBUG_SV.log(l_module_name, 'l_to_party_site_use_id', l_to_party_site_use_id);
3666: WSH_DEBUG_SV.logmsg(l_module_name, 'Start processing for Shipped Delivery Details');
3667: END IF;
3660:
3661: IF l_debug_on THEN
3662: WSH_DEBUG_SV.log(l_module_name, 'l_from_location_id', l_from_location_id);
3663: WSH_DEBUG_SV.log(l_module_name, 'l_from_party_site_use_id', l_from_party_site_use_id);
3664: WSH_DEBUG_SV.log(l_module_name, 'l_to_location_id', l_to_location_id);
3665: WSH_DEBUG_SV.log(l_module_name, 'l_to_party_site_use_id', l_to_party_site_use_id);
3666: WSH_DEBUG_SV.logmsg(l_module_name, 'Start processing for Shipped Delivery Details');
3667: END IF;
3668:
3661: IF l_debug_on THEN
3662: WSH_DEBUG_SV.log(l_module_name, 'l_from_location_id', l_from_location_id);
3663: WSH_DEBUG_SV.log(l_module_name, 'l_from_party_site_use_id', l_from_party_site_use_id);
3664: WSH_DEBUG_SV.log(l_module_name, 'l_to_location_id', l_to_location_id);
3665: WSH_DEBUG_SV.log(l_module_name, 'l_to_party_site_use_id', l_to_party_site_use_id);
3666: WSH_DEBUG_SV.logmsg(l_module_name, 'Start processing for Shipped Delivery Details');
3667: END IF;
3668:
3669: OPEN get_shipped_details(p_from_vendor_id, l_from_party_site_use_id, l_from_location_id);
3662: WSH_DEBUG_SV.log(l_module_name, 'l_from_location_id', l_from_location_id);
3663: WSH_DEBUG_SV.log(l_module_name, 'l_from_party_site_use_id', l_from_party_site_use_id);
3664: WSH_DEBUG_SV.log(l_module_name, 'l_to_location_id', l_to_location_id);
3665: WSH_DEBUG_SV.log(l_module_name, 'l_to_party_site_use_id', l_to_party_site_use_id);
3666: WSH_DEBUG_SV.logmsg(l_module_name, 'Start processing for Shipped Delivery Details');
3667: END IF;
3668:
3669: OPEN get_shipped_details(p_from_vendor_id, l_from_party_site_use_id, l_from_location_id);
3670: LOOP
3673:
3674: -- Updating using delivery_id will make sure that shippable line as
3675: -- well as container records in WDD will be updated.
3676: IF l_debug_on THEN
3677: WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id.count', l_delivery_id.count);
3678: END IF;
3679: FORALL I IN l_delivery_id.first..l_delivery_id.last
3680: UPDATE wsh_delivery_details
3681: SET customer_id = decode(customer_id,
3696: WHERE delivery_id = l_delivery_id(i) );
3697:
3698: l_sql_count := SQL%rowcount;
3699: IF l_debug_on THEN
3700: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
3701: END IF;
3702: EXIT WHEN l_delivery_id.count < G_LIMIT;
3703: END LOOP;
3704: CLOSE get_shipped_details;
3703: END LOOP;
3704: CLOSE get_shipped_details;
3705: l_delivery_id.delete;
3706: IF l_debug_on THEN
3707: WSH_DEBUG_SV.log(l_module_name, 'End of processing for Shipped Delivery Details');
3708: END IF;
3709:
3710: IF l_debug_on THEN
3711: WSH_DEBUG_SV.log(l_module_name, 'Start processing for Unshipped Delivery Details');
3707: WSH_DEBUG_SV.log(l_module_name, 'End of processing for Shipped Delivery Details');
3708: END IF;
3709:
3710: IF l_debug_on THEN
3711: WSH_DEBUG_SV.log(l_module_name, 'Start processing for Unshipped Delivery Details');
3712: END IF;
3713: OPEN Get_unshipped_details (p_from_vendor_id, l_from_party_site_use_id,
3714: l_from_location_id);
3715:
3723: EXIT WHEN l_wdd_rowid.count <= 0;
3724:
3725: -- Updating all unshipped delivery details.
3726: IF l_debug_on THEN
3727: WSH_DEBUG_SV.log(l_module_name, 'l_wdd_rowid.count', l_wdd_rowid.count);
3728: END IF;
3729:
3730: FORALL I IN l_wdd_rowid.first..l_wdd_rowid.last
3731: UPDATE wsh_delivery_details
3742: program_update_date = SYSDATE
3743: WHERE ROWID = l_wdd_rowid(i);
3744: l_sql_count := SQL%rowcount;
3745: IF l_debug_on THEN
3746: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
3747: END IF;
3748: -- Unpack delivery lines if it is packed but not yet assigned to delivery
3749:
3750: -- Start:
3748: -- Unpack delivery lines if it is packed but not yet assigned to delivery
3749:
3750: -- Start:
3751: IF l_debug_on THEN
3752: WSH_DEBUG_SV.log(l_module_name, 'Unpacking unshipped delivery details which are not yet assigned to a delivery');
3753: END IF;
3754:
3755: Adjust_Weight_Volume (
3756: p_entity_type => 'CONT',
3768: WSH_UTIL_CORE.G_RET_STS_WARNING) )
3769: THEN
3770: --
3771: IF ( l_debug_on ) THEN
3772: WSH_DEBUG_SV.logmsg(l_module_name,'Error returned from API Adjust_Weight_Volume');
3773: END IF;
3774: RAISE FND_API.G_EXC_ERROR;
3775: --
3776: END IF;
3789: AND delivery_id IS NULL;
3790:
3791: l_sql_count := SQL%rowcount;
3792: IF l_debug_on THEN
3793: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_assignments. Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
3794: END IF;
3795:
3796: Adjust_Parent_WV (
3797: p_entity_type => 'CONT',
3808: WSH_UTIL_CORE.G_RET_STS_WARNING) )
3809: THEN
3810: --
3811: IF ( l_debug_on ) THEN
3812: WSH_DEBUG_SV.logmsg(l_module_name,'Error returned from API Adjust_Parent_WV');
3813: END IF;
3814: RAISE FND_API.G_EXC_ERROR;
3815: --
3816: END IF;
3817:
3818: FOR I IN l_parent_delivery_detail_id.first..l_parent_delivery_detail_id.last LOOP
3819:
3820: IF l_debug_on THEN
3821: WSH_DEBUG_SV.log(l_module_name, 'Container Delivery_detail_id is ', nvl(l_parent_delivery_detail_id(i), NULL));
3822: WSH_DEBUG_SV.log(l_module_name, 'Delivery is ', nvl(l_delivery_id(i), NULL));
3823: END IF;
3824: IF l_parent_delivery_detail_id(i) IS NOT NULL AND l_delivery_id(i) IS NULL THEN
3825:
3818: FOR I IN l_parent_delivery_detail_id.first..l_parent_delivery_detail_id.last LOOP
3819:
3820: IF l_debug_on THEN
3821: WSH_DEBUG_SV.log(l_module_name, 'Container Delivery_detail_id is ', nvl(l_parent_delivery_detail_id(i), NULL));
3822: WSH_DEBUG_SV.log(l_module_name, 'Delivery is ', nvl(l_delivery_id(i), NULL));
3823: END IF;
3824: IF l_parent_delivery_detail_id(i) IS NOT NULL AND l_delivery_id(i) IS NULL THEN
3825:
3826: IF l_debug_on THEN
3823: END IF;
3824: IF l_parent_delivery_detail_id(i) IS NOT NULL AND l_delivery_id(i) IS NULL THEN
3825:
3826: IF l_debug_on THEN
3827: WSH_DEBUG_SV.log(l_module_name, 'Logging Exception against the container');
3828: END IF;
3829:
3830:
3831: l_message_name := 'WSH_VMRG_UNASSIGN_CONTAINER';
3864: WSH_UTIL_CORE.G_RET_STS_WARNING) )
3865: THEN
3866: --
3867: IF ( l_debug_on ) THEN
3868: WSH_DEBUG_SV.log(l_module_name,'Error returned from API WSH_XC_UTIL.Log_Exception');
3869: END IF;
3870: RAISE FND_API.G_EXC_ERROR;
3871: --
3872: END IF;
3873: END IF;
3874: END LOOP;
3875:
3876: IF l_debug_on THEN
3877: WSH_DEBUG_SV.log(l_module_name, 'End -Unpacking of delivery lines from containers, when they are not assigned to a delivery.');
3878: END IF;
3879:
3880: -- To Process deliveries and Stops, inserting into temp table
3881: -- Start
3880: -- To Process deliveries and Stops, inserting into temp table
3881: -- Start
3882:
3883: IF l_debug_on THEN
3884: WSH_DEBUG_SV.log(l_module_name, 'Inserting Deliveries and Trip Stops into temp table for processing');
3885: END IF;
3886: IF l_debug_on THEN
3887: WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id.count', l_delivery_id.count);
3888: END IF;
3883: IF l_debug_on THEN
3884: WSH_DEBUG_SV.log(l_module_name, 'Inserting Deliveries and Trip Stops into temp table for processing');
3885: END IF;
3886: IF l_debug_on THEN
3887: WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id.count', l_delivery_id.count);
3888: END IF;
3889:
3890: FORALL I IN l_delivery_id.first..l_delivery_id.last
3891: INSERT INTO wsh_tmp ( column1, column2, column3 )
3899: AND ( column2 = l_stop_id(i) OR l_stop_id(i) IS NULL ) );
3900:
3901: l_sql_count := SQL%rowcount;
3902: IF l_debug_on THEN
3903: WSH_DEBUG_SV.logmsg(l_module_name,'inserted into wsh_tmp. Number of Rows inserted is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
3904: END IF;
3905:
3906: -- To Process deliveries and Stops, inserting into temp table
3907: -- END
3928:
3929: --Unpack and unassign delivery lines from delivery.
3930:
3931: IF l_debug_on THEN
3932: WSH_DEBUG_SV.log(l_module_name, 'Unassigning delivery details from delivery');
3933: END IF;
3934:
3935: OPEN get_unassign_details(p_to_vendor_id, l_to_party_site_use_id, l_to_location_id);
3936: LOOP
3941: l_weight_uom,l_volume_uom
3942: LIMIT G_LIMIT;
3943: EXIT WHEN l_wda_rowid.count <= 0;
3944: IF l_debug_on THEN
3945: WSH_DEBUG_SV.log(l_module_name, 'l_wda_rowid.count', l_wda_rowid.count);
3946: END IF;
3947: IF l_debug_on THEN
3948: WSH_DEBUG_SV.log(l_module_name, 'updating weight before unassigning from delivery ');
3949: END IF;
3944: IF l_debug_on THEN
3945: WSH_DEBUG_SV.log(l_module_name, 'l_wda_rowid.count', l_wda_rowid.count);
3946: END IF;
3947: IF l_debug_on THEN
3948: WSH_DEBUG_SV.log(l_module_name, 'updating weight before unassigning from delivery ');
3949: END IF;
3950: Adjust_Weight_Volume (
3951: p_entity_type => 'DEL-CONT',
3952: p_delivery_detail => l_delivery_Detail_Id,
3962: WSH_UTIL_CORE.G_RET_STS_WARNING) )
3963: THEN
3964: --
3965: IF ( l_debug_on ) THEN
3966: WSH_DEBUG_SV.logmsg(l_module_name,'Error returned from API Adjust_Weight_Volume');
3967: END IF;
3968: RAISE FND_API.G_EXC_ERROR;
3969: --
3970: END IF;
3969: --
3970: END IF;
3971:
3972: IF l_debug_on THEN
3973: WSH_DEBUG_SV.log(l_module_name, 'l_wda_rowid.count', l_wda_rowid.count);
3974: END IF;
3975: FORALL I IN l_wda_rowid.FIRST..l_wda_rowid.LAST
3976: UPDATE wsh_delivery_assignments
3977: SET parent_delivery_detail_id = NULL,
3984: program_update_date = SYSDATE
3985: WHERE ROWID = l_wda_rowid(i);
3986: l_sql_count := SQL%rowcount;
3987: IF l_debug_on THEN
3988: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_assignments(unassign n unpack from del). Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
3989: END IF;
3990:
3991: Adjust_Parent_WV (
3992: p_entity_type => 'DEL-CONT',
4003: WSH_UTIL_CORE.G_RET_STS_WARNING) )
4004: THEN
4005: --
4006: IF ( l_debug_on ) THEN
4007: WSH_DEBUG_SV.log(l_module_name, 'Error returned from API Adjust_Parent_WV');
4008: END IF;
4009: RAISE FND_API.G_EXC_ERROR;
4010: --
4011: END IF;
4009: RAISE FND_API.G_EXC_ERROR;
4010: --
4011: END IF;
4012: IF l_debug_on THEN
4013: WSH_DEBUG_SV.log(l_module_name, 'Clearing the deliveries from temp table');
4014: END IF;
4015: --Delivery for which delivery lines are unassigned needs to be deleted
4016: --from wsh_tmp table
4017: FORALL I IN l_delivery_id.FIRST..l_delivery_id.LAST
4019: WHERE column1 = l_delivery_id(i);
4020:
4021: l_sql_count := SQL%rowcount;
4022: IF l_debug_on THEN
4023: WSH_DEBUG_SV.logmsg(l_module_name,'deleted from wsh_tmp. Number of Rows deleted is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
4024: END IF;
4025: --Log Exceptions 'WSH_VENDOR_MERGE_CHANGE' with message
4026: --'WSH_VMRG_UNASSIGN_DELIVERY' against delivery from which delivery lines
4027: --are un-assigned.
4029: FOR I IN l_wda_rowid.FIRST..l_wda_rowid.LAST LOOP
4030:
4031: IF l_parent_Delivery_Detail_Id(i) is not null THEN
4032: IF l_debug_on THEN
4033: WSH_DEBUG_SV.log(l_module_name, 'Logging Exception against the container');
4034: END IF;
4035:
4036:
4037: l_message_name := 'WSH_VMRG_UNASSIGN_CONTAINER';
4070: WSH_UTIL_CORE.G_RET_STS_WARNING) )
4071: THEN
4072: --
4073: IF ( l_debug_on ) THEN
4074: WSH_DEBUG_SV.log(l_module_name,'Error returned from API WSH_XC_UTIL.Log_Exception');
4075: END IF;
4076: RAISE FND_API.G_EXC_ERROR;
4077: --
4078: END IF;
4077: --
4078: END IF;
4079: END IF;
4080: IF l_debug_on THEN
4081: WSH_DEBUG_SV.log(l_module_name, 'Logging Exception against the container');
4082: END IF;
4083:
4084: FND_MESSAGE.Set_Name ('WSH', l_message_name );
4085: FND_MESSAGE.Set_Token ('PS1', l_from_party_site_use_id );
4116: WSH_UTIL_CORE.G_RET_STS_WARNING) )
4117: THEN
4118: --
4119: IF ( l_debug_on ) THEN
4120: WSH_DEBUG_SV.log(l_module_name,'Error returned from API WSH_XC_UTIL.Log_Exception');
4121: END IF;
4122: --
4123: RAISE FND_API.G_EXC_ERROR;
4124: END IF;
4146: --Updating un-shipped containers
4147: -- START
4148:
4149: IF l_debug_on THEN
4150: WSH_DEBUG_SV.log(l_module_name, 'Updating Customer details on unshipped containes');
4151: END IF;
4152:
4153: OPEN get_delivery_containers;
4154: LOOP
4154: LOOP
4155: FETCH get_delivery_containers BULK COLLECT INTO l_wdd_rowid LIMIT G_LIMIT;
4156: EXIT WHEN l_wdd_rowid.count <= 0;
4157: IF l_debug_on THEN
4158: WSH_DEBUG_SV.log(l_module_name, 'l_wdd_rowid.count', l_wdd_rowid.count);
4159: END IF;
4160: FORALL I IN l_wdd_rowid.FIRST..l_wdd_rowid.LAST
4161: UPDATE wsh_delivery_details
4162: SET customer_id = decode(customer_id,
4171: program_update_date = SYSDATE
4172: WHERE ROWID = l_wdd_rowid(i);
4173: l_sql_count := SQL%rowcount;
4174: IF l_debug_on THEN
4175: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details (Containers). Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
4176: END IF;
4177:
4178: EXIT WHEN l_wdd_rowid.count < G_LIMIT;
4179:
4202: WSH_UTIL_CORE.G_RET_STS_WARNING) )
4203: THEN
4204: --
4205: IF ( l_debug_on ) THEN
4206: WSH_DEBUG_SV.log(l_module_name,'Error returned from API Get_Delivery_Hash');
4207: END IF;
4208: --
4209: END IF;
4210: IF l_debug_on THEN
4207: END IF;
4208: --
4209: END IF;
4210: IF l_debug_on THEN
4211: WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id_tab.count', l_delivery_id_tab.count);
4212: END IF;
4213:
4214: FORALL I IN l_delivery_id_tab.first..l_delivery_id_tab.last
4215: UPDATE wsh_new_deliveries
4227: program_update_date = SYSDATE
4228: WHERE delivery_id = l_delivery_id_tab(i);
4229: l_sql_count := SQL%rowcount;
4230: IF l_debug_on THEN
4231: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries. Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
4232: END IF;
4233: -- Update deliveries
4234: -- END
4235:
4241: l_gross_weight, l_net_weight, l_volume
4242: LIMIT G_LIMIT;
4243: EXIT WHEN l_wdl_rowid.count <= 0;
4244: IF l_debug_on THEN
4245: WSH_DEBUG_SV.log(l_module_name, 'l_wdl_rowid.count', l_wdl_rowid.count);
4246: END IF;
4247: Adjust_Weight_Volume (
4248: p_entity_type => 'TRIP-STOP',
4249: p_delivery_detail => l_dummyIdTab,
4259: WSH_UTIL_CORE.G_RET_STS_WARNING) )
4260: THEN
4261: --
4262: IF ( l_debug_on ) THEN
4263: WSH_DEBUG_SV.log(l_module_name,'Error returned from API Adjust_Weight_Volume');
4264: END IF;
4265: --
4266: END IF;
4267: IF l_debug_on THEN
4264: END IF;
4265: --
4266: END IF;
4267: IF l_debug_on THEN
4268: WSH_DEBUG_SV.log(l_module_name, 'l_wdl_rowid.count', l_wdl_rowid.count);
4269: END IF;
4270: FORALL I IN l_wdl_rowid.FIRST..l_wdl_rowid.LAST
4271: DELETE FROM wsh_delivery_legs
4272: WHERE ROWID = l_wdl_rowid(i);
4272: WHERE ROWID = l_wdl_rowid(i);
4273:
4274: l_sql_count := SQL%rowcount;
4275: IF l_debug_on THEN
4276: WSH_DEBUG_SV.logmsg(l_module_name,'deleted from wsh_delivery_legs. Number of Rows deleted is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
4277: END IF;
4278:
4279: FORALL I IN l_tmp_rowid.FIRST..l_tmp_rowid.LAST
4280: DELETE FROM wsh_tmp
4281: WHERE ROWID = l_tmp_rowid(i);
4282:
4283: l_sql_count := SQL%rowcount;
4284: IF l_debug_on THEN
4285: WSH_DEBUG_SV.logmsg(l_module_name,'deleted from wsh_tmp. Number of Rows deleted is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
4286: END IF;
4287:
4288: -- Log Exceptions 'WSH_VENDOR_MERGE_CHANGE' with message
4289: -- 'WSH_VMRG_UNASSIGN_STOP' against stop from which delivery
4325: WSH_UTIL_CORE.G_RET_STS_WARNING) )
4326: THEN
4327: --
4328: IF ( l_debug_on ) THEN
4329: WSH_DEBUG_SV.log(l_module_name,'Error returned from API WSH_XC_UTIL.Log_Exception');
4330: END IF;
4331: --
4332: END IF;
4333: END LOOP;
4404: WSH_UTIL_CORE.G_RET_STS_WARNING) )
4405: THEN
4406: --
4407: IF ( l_debug_on ) THEN
4408: WSH_DEBUG_SV.log(l_module_name,'Error returned from API WSH_DELIVERY_AUTOCREATE.Create_Hash');
4409: RAISE FND_API.G_EXC_ERROR;
4410: END IF;
4411: --
4412: END IF;
4411: --
4412: END IF;
4413:
4414: IF l_debug_on THEN
4415: WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id.count', l_delivery_id.count);
4416: END IF;
4417:
4418: FORALL I IN l_delivery_id.first..l_delivery_id.last
4419: UPDATE wsh_new_deliveries
4432: WHERE delivery_id = l_delivery_id(i);
4433:
4434: l_sql_count := SQL%rowcount;
4435: IF l_debug_on THEN
4436: WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries(Empty deliveries). Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
4437: END IF;
4438:
4439: EXIT WHEN l_delivery_id.count < G_LIMIT;
4440: END LOOP;
4456: l_shipping_control.delete;
4457: l_party_id.delete;
4458: l_client_id.delete;
4459:
4460: WSH_DEBUG_SV.pop(l_module_name);
4461:
4462: EXCEPTION
4463: --
4464: WHEN FND_API.G_EXC_ERROR THEN
4495: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4496: --
4497: IF l_debug_on THEN
4498: --
4499: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
4500: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
4501: --
4502: END IF;
4503: --
4496: --
4497: IF l_debug_on THEN
4498: --
4499: WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
4500: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
4501: --
4502: END IF;
4503: --
4504: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4533: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4534: --
4535: IF l_debug_on THEN
4536: --
4537: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4538: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4539: --
4540: END IF;
4541: --
4534: --
4535: IF l_debug_on THEN
4536: --
4537: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4538: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4539: --
4540: END IF;
4541: --
4542: WHEN OTHERS THEN
4573: wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Merge_RTV_Transactions');
4574: --
4575: IF l_debug_on THEN
4576: --
4577: WSH_DEBUG_SV.log(l_module_name,'Unexpected error has occured. Oracle error message is ', SUBSTRB(SQLERRM, 1, 200));
4578: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:OTHERS');
4579: --
4580: END IF;
4581: --
4574: --
4575: IF l_debug_on THEN
4576: --
4577: WSH_DEBUG_SV.log(l_module_name,'Unexpected error has occured. Oracle error message is ', SUBSTRB(SQLERRM, 1, 200));
4578: WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:OTHERS');
4579: --
4580: END IF;
4581: --
4582: END Merge_RTV_Transactions;