[Home] [Help]
PACKAGE BODY: APPS.WSH_VENDOR_PARTY_MERGE_PKG
Source
1 PACKAGE BODY WSH_VENDOR_PARTY_MERGE_PKG AS
2 /* $Header: WSHVMRGB.pls 120.21 2006/02/22 04:14:45 pkaliyam noship $ */
3 --
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_VENDOR_PARTY_MERGE_PKG';
5 --
6 TYPE g_LocChangeRec IS RECORD
7 (
8 location_id NUMBER,
9 old_loc_code VARCHAR2(40),
10 new_loc_code VARCHAR2(40));
11 --
12 TYPE locChangeTab IS TABLE OF g_LocChangeRec INDEX BY BINARY_INTEGER;
13 g_LocChangeTab locChangeTab;
14 --
15
16 --
17 --
18 --========================================================================
19 -- PROCEDURE : InactivatePartySites
20 -- PARAMETERS:
21 -- P_party_id Merge from party ID
22 -- P_party_site_id Party Site ID
23 -- p_process_locations Determines whether Process_Locations()
24 -- API should be called or not.
25 -- p_to_id Merge To Party ID
26 -- p_to_vendor_id Merge To Vendor ID
27 -- X_return_status Return status
28 --
29 -- COMMENT :
30 -- This is a private procedure that is used to inactivate
31 -- party sites for a given party ID. For a given party ID,
32 -- it gets a list of party Sites and for each party site ID,
33 -- it looks for any delivery tied to that location. If there
34 -- are no such deliveries, it calls HZ API to set the status
35 -- of that particular party Site to 'I'.
36 --
37 -- If parameter p_process_locations is TRUE, then it also calls
38 -- Process_Locations() to transfer the old SF location from the
39 -- old vendor to the new vendor. Process_Locations() should be
40 -- called only when parameter p_party_site_id IS NOT NULL.
41 --
42 --========================================================================
43 PROCEDURE InactivatePartySites(p_party_id IN NUMBER,
44 p_party_site_id IN NUMBER DEFAULT NULL,
45 p_process_locations IN BOOLEAN DEFAULT FALSE,
46 p_to_id IN NUMBER,
47 p_to_vendor_id IN NUMBER,
48 x_return_status OUT NOCOPY VARCHAR2)
49 IS
50 --
51 CURSOR get_party_site_csr(p_party_id NUMBER, p_site_id NUMBER) IS
52 SELECT location_id,
53 hps.party_site_id,
54 hps.object_version_number
55 FROM hz_party_sites hps,
56 hz_party_site_uses hpsu
57 WHERE hps.party_id = p_party_id
58 AND hps.party_site_id = hpsu.party_site_id
59 AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM'
60 AND hpsu.status = 'A'
61 AND hps.party_site_id = NVL(p_site_id, hps.party_site_id);
62 --
63 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'InactivatePartySites';
64 l_debug_on BOOLEAN;
65 --
66 l_from_party_rec hz_party_site_v2pub.party_site_rec_type;
67 l_msg_data VARCHAR2(32767);
68 l_num_warnings NUMBER :=0;
69 l_num_errors NUMBER :=0;
70 l_object_version_number NUMBER;
71 l_msg VARCHAR2(32767);
72 l_query_count NUMBER :=0;
73 l_msg_count NUMBER ;
74 l_return_status VARCHAR2(1);
75 l_sql_code NUMBER;
76 l_sql_err VARCHAR2(32767);
77 --
78 BEGIN
79 --{
80 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
81 --
82 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
83 IF l_debug_on IS NULL THEN
84 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
85 END IF;
86 --
87 IF l_debug_on THEN
88 WSH_DEBUG_SV.push(l_module_name);
89 WSH_DEBUG_SV.log(l_module_name, 'p_party_id', p_party_id);
90 WSH_DEBUG_SV.log(l_module_name, 'p_party_site_id', p_party_site_id);
91 WSH_DEBUG_SV.log(l_module_name, 'p_process_locations', p_process_locations);
92 WSH_DEBUG_SV.log(l_module_name, 'p_to_id', p_to_id);
93 WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
94 END IF;
95 --
96 FOR get_party_site_rec IN get_party_site_csr(p_party_id => p_party_id,
97 p_site_id => p_party_site_id)
98 LOOP
99 --{
100 IF l_debug_on THEN
101 --
102 WSH_DEBUG_SV.logmsg(l_module_name, '----------------', WSH_DEBUG_SV.C_STMT_LEVEL);
103 WSH_DEBUG_SV.log(l_module_name,'LOCATION_ID' , get_party_site_rec.location_id);
104 WSH_DEBUG_SV.log(l_module_name,'PARTY_SITE_ID', get_party_site_rec.party_site_id);
105 WSH_DEBUG_SV.log(l_module_name,'OBJECT_VERSION_NUMBER', get_party_site_rec.object_version_number);
106 --
107 END IF;
108 --
109 -- Check if we have any deliveries tied to the
110 -- SF location associated with the old party ID
111 --
112 BEGIN
113 --
114 -- R12 Perf Bug 4949639 : Replace WND with WDD
115 -- since all we are checking for is existence of records
116 -- with a particular SF location ID
117 --
118 SELECT 1
119 INTO l_query_count
120 FROM wsh_delivery_details wdd,
121 wsh_locations wl
122 WHERE wdd.ship_from_location_id = wl.wsh_location_id
123 AND wl.source_location_id = get_party_site_rec.location_id
124 AND wdd.party_id = p_party_id
125 AND rownum=1;
126 --
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 l_query_count := 0;
130 END;
131 --
132 IF l_debug_on THEN
133 WSH_DEBUG_SV.log(l_module_name,'L_QUERY_COUNT', l_query_count);
134 END IF;
135 --
136 IF l_query_count = 0 AND p_party_site_id IS NOT NULL THEN
137 --{
138 -- Make party site inactive.
139 --
140 l_from_party_rec.party_site_id := get_party_site_rec.party_site_id ;
141 l_object_version_number := get_party_site_rec.object_version_number;
142 l_from_party_rec.status := 'I' ;
143 --
144 IF l_debug_on THEN
145 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE_USE',WSH_DEBUG_SV.C_PROC_LEVEL);
146 END IF;
147 --
148 hz_party_site_v2pub.update_party_site
149 (
150 p_party_site_rec => l_from_party_rec,
151 p_object_version_number => l_object_version_number,
152 x_return_status => l_return_status,
153 x_msg_count => l_msg_count,
154 x_msg_data => l_msg
155 );
156 --
157 IF l_debug_on THEN
158 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);
159 END IF;
160 --
161 wsh_util_core.api_post_call
162 (
163 p_return_status => l_return_status,
164 x_num_warnings => l_num_warnings,
165 x_num_errors => l_num_errors,
166 p_msg_data => l_msg
167 );
168 --
169 IF p_process_locations THEN
170 --{
171 IF l_debug_on THEN
172 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Process_Location API', WSH_DEBUG_SV.C_PROC_LEVEL);
173 END IF;
174 --
175 WSH_LOCATIONS_PKG.Process_Locations
176 (
177 p_location_type => 'EXTERNAL',
178 p_from_location => get_party_site_rec.location_id,
179 p_to_location => get_party_site_rec.location_id,
180 p_start_date => NULL,
181 p_end_date => NULL,
182 p_caller => 'PO',
183 x_return_status => l_return_status,
184 x_sqlcode => l_sql_code,
185 x_sqlerr => l_sql_err
186 );
187 --
188 IF l_debug_on THEN
189 WSH_DEBUG_SV.logmsg(l_module_name, 'After Process_Location API', WSH_DEBUG_SV.C_PROC_LEVEL);
190 WSH_DEBUG_SV.log(l_module_name, 'l_sql_code', l_sql_code);
191 WSH_DEBUG_SV.log(l_module_name, 'l_sql_err', l_sql_err);
192 WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
193 END IF;
194 --
195 WSH_UTIL_CORE.api_post_call
196 (
197 p_return_status => l_return_status,
198 x_num_warnings => l_num_warnings,
199 x_num_errors => l_num_errors
200 );
201 --}
202 END IF;
203 --}
204 ELSIF l_query_count = 0 THEN
205 --{
206 IF l_debug_on THEN
207 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Create_Site', WSH_DEBUG_SV.C_PROC_LEVEL);
208 END IF;
209 --
210 Create_Site
211 (
212 p_from_id => p_party_id,
213 p_to_id => p_to_id,
214 p_to_vendor_id => p_to_vendor_id,
215 p_delivery_id => NULL,
216 p_delivery_name => NULL,
217 p_location_id => get_party_site_rec.location_id,
218 x_return_Status => l_return_status
219 );
220 --
221 IF l_debug_on THEN
222 WSH_DEBUG_SV.logmsg(l_module_name, 'After calling create_Site', WSH_DEBUG_SV.C_PROC_LEVEL);
223 WSH_DEBUG_SV.log(l_module_name, 'Return Status', l_return_status);
224 END IF;
225 --
226 WSH_UTIL_CORE.api_post_call
227 (
228 p_return_status => l_return_status,
229 x_num_warnings => l_num_warnings,
230 x_num_errors => l_num_errors
231 );
232 --}
233 END IF;
234 --}
235 END LOOP;
236 --
237 IF l_num_errors > 0
238 THEN
239 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
240 ELSIF l_num_warnings > 0
241 THEN
242 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
243 ELSE
244 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
245 END IF;
246 --
247 IF l_debug_on THEN
248 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
249 WSH_DEBUG_SV.pop(l_module_name);
250 END IF;
251 --}
252 EXCEPTION
253 --
254 WHEN FND_API.G_EXC_ERROR THEN
255 --
256 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
257 --
258 IF l_debug_on THEN
259 --
260 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
261 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
262 --
263 END IF;
264 --
265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
266 --
267 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
268 --
269 IF l_debug_on THEN
270 --
271 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
272 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
273 --
274 END IF;
275 --
276 WHEN OTHERS THEN
277 --
278 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
279 wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.InactivatePartySites');
280 --
281 IF l_debug_on THEN
282 --
283 WSH_DEBUG_SV.log(l_module_name,'Unexpected error has occured. Oracle error message is ', SUBSTRB(SQLERRM,1,200));
284 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
285 --
286 END IF;
287 --
288 END InactivatePartySites;
289
290
291 --
292 --
293 --========================================================================
294 -- PROCEDURE : Create_Site
295 -- PARAMETERS:
296 -- P_from_id Merge from party ID
297 -- P_to_id Merge to party ID
298 -- P_to_vendor_id Merge to vendor ID
299 -- P_delivery_id Delivery ID
300 -- P_delivery_name Delivery Name
301 -- P_location_id SF Location ID
302 -- X_return_status Return status
303 --
304 -- COMMENT : This is a private procedure to create a new party site.
305 -- It also creates a corresponding party site use record and
306 -- calls Process_Locations() to update information in WSH
307 -- location tables.
308 --========================================================================
309 PROCEDURE Create_Site(
310 p_from_id IN NUMBER,
311 p_to_id IN NUMBER,
312 p_to_vendor_id IN NUMBER,
313 p_delivery_id IN NUMBER,
314 p_delivery_name IN VARCHAR2,
315 p_location_id IN NUMBER,
316 x_return_status OUT NOCOPY VARCHAR2
317 )
318 IS
319 --
320 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_SITE';
321 l_debug_on BOOLEAN;
322 --
323 CURSOR check_location_id IS
324 SELECT 'x', hps.party_site_id
325 FROM hz_party_sites hps,
326 hz_party_site_uses hpsu
327 WHERE hps.party_id = p_to_id
328 AND hps.location_id = p_location_id
329 AND hps.party_site_id = hpsu.party_site_id
330 AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM';
331 --
332 CURSOR check_location_code IS
333 SELECT substr(party_site_number, 1,
334 instr(party_site_number, '|')-1) location_code,
335 hps.party_site_id
336 FROM hz_party_sites hps,
337 hz_party_site_uses hpsu
338 WHERE hps.location_id = p_location_id
339 AND hps.party_id = p_from_id
340 AND hps.party_site_id = hpsu.party_site_id
341 AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM';
342 --
343 l_partySiteId NUMBER;
344 l_location_code VARCHAR2(40);
345 l_new_location_code VARCHAR2(40);
346 l_from_party_site_id NUMBER;
347 --
348 CURSOR chk_locn_csr (p_site_number IN VARCHAR2) IS
349 SELECT 'x'
350 FROM hz_party_sites hps,
351 hz_party_site_uses hpsu
352 WHERE hps.party_id = p_to_id
353 AND hps.party_site_number = p_site_number
354 AND hps.party_site_id = hpsu.party_site_id
355 AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM';
356 --
357 l_dummy VARCHAR2(1);
358 --
359 CURSOR Get_Contact_info ( p_party_id NUMBER,
360 p_party_site_id NUMBER ) IS
361 SELECT contact_person.party_name shipper_name,
362 phone_record.phone_number phone_number,
363 email_record.email_address email_address
364 FROM hz_party_sites hps,
365 hz_parties contact_person,
366 hz_org_contacts supplier_contact,
367 hz_contact_points phone_record,
368 hz_contact_points email_record,
369 hz_relationships hrel
370 WHERE hrel.subject_id = contact_person.party_id
371 AND hrel.subject_table_name = 'HZ_PARTIES'
372 AND hrel.subject_type = 'PERSON'
373 AND hrel.object_id = hps.party_id
374 AND hrel.object_table_name = 'HZ_PARTIES'
375 AND hrel.object_type = 'ORGANIZATION'
376 AND hrel.relationship_code = 'CONTACT_OF'
377 AND hrel.directional_flag = 'F'
378 AND supplier_contact.party_relationship_id =hrel.relationship_id
379 AND supplier_contact.party_site_id = hps.party_site_id
380 AND phone_record.owner_table_name(+) = 'HZ_PARTIES'
381 AND phone_record.owner_table_id(+) = hrel.party_id
382 AND phone_record.contact_point_type(+) = 'PHONE'
383 AND email_record.owner_table_name = 'HZ_PARTIES'
384 AND email_record.owner_table_id = hrel.party_id
385 AND email_record.contact_point_type = 'EMAIL'
386 AND hps.party_site_id =p_party_site_id
387 AND hps.party_id = p_party_id;
388 --
389 l_contact_rec get_contact_info%ROWTYPE;
390 --
391 CURSOR get_supplier_name (p_vendor_id NUMBER) IS
392 SELECT vendor_name
393 FROM po_vendors
394 WHERE vendor_id = p_vendor_id;
395 --
396 l_supplier_name varchar2(360);
397 l_return_status VARCHAR2(2);
398 l_exception_id NUMBER;
399 l_msg VARCHAR2(32767);
400 l_xc_msg_count NUMBER;
401 l_xc_msg_data VARCHAR2(2000);
402 l_site_number VARCHAR2(40);
403 l_to_party_site_id NUMBER;
404 l_loc_chg BOOLEAN;
405 l_location_id NUMBER;
406 l_msg_data VARCHAR2(32767);
407 l_num_warnings NUMBER :=0;
408 l_num_errors NUMBER :=0;
409 l_sql_code NUMBER;
410 l_sql_err VARCHAR2(32767);
411 l_party_site_use_id NUMBER;
412 l_LocationIdTbl WSH_LOCATIONS_PKG.ID_Tbl_Type;
413 l_count NUMBER;
414 --
415 BEGIN
416 --{
417 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
418 --
419 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
420 IF l_debug_on IS NULL THEN
421 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
422 END IF;
423 --
424 IF l_debug_on THEN
425 --
426 WSH_DEBUG_SV.push(l_module_name);
427 WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
428 WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
429 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
430 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME', p_delivery_name );
431 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
432 --
433 END IF;
434 --
435 l_dummy := NULL;
436 OPEN check_location_id;
437 FETCH check_location_id INTO l_dummy, l_partySiteId;
438 CLOSE check_location_id;
439 --
440 IF l_debug_on THEN
441 WSH_DEBUG_SV.log(l_module_name, 'l_dummy', l_dummy);
442 WSH_DEBUG_SV.log(l_module_name, 'l_partySiteId', l_partySiteId);
443 END IF;
444 --
445 IF l_dummy IS NOT NULL THEN
446 --{
447 IF p_from_id <> p_to_id THEN
448 --{
449 IF l_debug_on THEN
450 wsh_debug_sv.logmsg(l_module_name, 'Calling InactivatePartySites', WSH_DEBUG_SV.C_PROC_LEVEL);
451 END IF;
452 --
453 InactivatePartySites
454 (
455 p_party_id => p_from_id,
456 p_to_id => p_to_id,
457 p_party_site_id => l_partySiteId,
458 p_process_locations => TRUE,
459 p_to_vendor_id => p_to_vendor_id,
460 x_return_status => l_return_status
461 );
462 --
463 IF l_debug_on THEN
464 wsh_debug_sv.log(l_module_name, 'Return Status from inactivatePartySites', l_return_status);
465 END IF;
466 --
467 WSH_UTIL_CORE.api_post_call
468 (
469 p_return_status => l_return_status,
470 x_num_warnings => l_num_warnings,
471 x_num_errors => l_num_errors
472 );
473 --}
474 END IF;
475 --
476 IF p_delivery_id IS NOT NULL THEN
477 --{
478 FOR i IN g_LocChangeTab.FIRST..g_LocChangeTab.LAST LOOP
479 --{
480 IF g_LocChangeTab(i).location_id = p_location_id THEN
481 --{
482 IF l_debug_on THEN
483 WSH_DEBUG_SV.logmsg(l_module_name, 'Logging SF code change exception');
484 END IF;
485 --
486 fnd_message.set_name ( 'WSH', 'WSH_IB_SF_LOCN_CODE_CHG');
487 fnd_message.set_token( 'L_LOCATION_CODE' , g_LocChangeTab(i).old_loc_code);
488 fnd_message.set_token( 'L_NEW_LOCATION_CODE', g_LocChangeTab(i).new_loc_code);
489 fnd_message.set_token( 'DELIVERY_NAME' , p_delivery_name );
490 l_msg := FND_MESSAGE.GET;
491 WSH_UTIL_CORE.printMsg(l_msg);
492 --
493 IF l_debug_on THEN
494 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
495 END IF;
496 --
497 wsh_xc_util.log_exception (
498 p_api_version => 1.0,
499 p_exception_name => 'WSH_IB_SF_LOCN_CODE_CHG',
500 p_logging_entity => 'SHIPPER',
501 p_logging_entity_id => FND_GLOBAL.USER_ID,
502 x_return_status => l_return_status,
503 x_exception_id => l_exception_id,
504 x_msg_data => l_xc_msg_data,
505 x_msg_count => l_xc_msg_count,
506 p_message => substrb ( l_msg, 1, 2000 ),
507 p_delivery_id => p_delivery_id,
508 p_exception_location_id => p_location_id,
509 p_logged_at_location_id => p_location_id
510 );
511 --
512 IF l_debug_on THEN
513 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_XC_UTIL.LOG_EXCEPTION is ', l_return_status);
514 END IF;
515 --
516 wsh_util_core.api_post_call(
517 p_return_status => l_return_status,
518 x_num_warnings => l_num_warnings,
519 x_num_errors => l_num_errors,
520 p_msg_data => l_xc_msg_data
521 );
522 --
523 EXIT;
524 --}
525 END IF;
526 --}
527 END LOOP;
528 --}
529 END IF;
530 --
531 IF l_debug_on THEN
532 WSH_DEBUG_SV.pop(l_module_name);
533 END IF;
534 --
535 RETURN;
536 --}
537 END IF;
538 --
539 OPEN check_location_code ;
540 FETCH check_location_code INTO l_location_code, l_from_party_site_id;
541 CLOSE check_location_code;
542 --
543 IF l_debug_on THEN
544 --
545 WSH_DEBUG_SV.log(l_module_name,'cursor check_location_code : L_LOCATION_CODE', l_location_code);
546 WSH_DEBUG_SV.log(l_module_name,'cursor check_location_code : L_FROM_PARTY_SITE_ID ', l_from_party_site_id);
547 --
548 END IF;
549 --
550 OPEN get_contact_info(p_party_id => p_from_id,
551 p_party_site_id => l_from_party_site_id );
552 FETCH get_contact_info INTO l_contact_rec;
553 CLOSE get_contact_info;
554 --
555 IF l_debug_on THEN
556 --
557 WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.SHIPPER_NAME', l_contact_rec.shipper_name);
558 WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.PHONE_NUMBER', l_contact_rec.phone_number);
559 WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.EMAIL_ADDRESS',l_contact_rec.email_address);
560 --
561 END IF;
562 --
563 l_site_number := l_location_code || '|' || p_to_id;
564 --
565 IF l_debug_on THEN
566 WSH_DEBUG_SV.log(l_module_name,'L_SITE_NUMBER', l_site_number);
567 END IF;
568 --
569 -- Check if we have an entry in hz_party_sites with the same
570 -- party_site_number and merge TO vendor ID
571 --
572 l_dummy := NULL;
573 OPEN chk_locn_csr( p_site_number => l_site_number );
574 FETCH chk_locn_csr INTO l_dummy;
575 CLOSE chk_locn_csr;
576 --
577 IF l_debug_on THEN
578 wsh_debug_sv.log(l_module_name, 'After chk_loc_csr, l_dummy', l_dummy);
579 END IF;
580 --
581 IF l_dummy IS NOT NULL THEN
582 --{
583 IF l_debug_on THEN
584 wsh_debug_sv.log(l_module_name, 'Looping to create unique SF', l_dummy);
585 END IF;
586 --
587 l_loc_chg := TRUE;
588 --
589 FOR I in 1..999 LOOP
590 --{
591 l_dummy := null;
592 l_new_location_code := l_location_code || '-VM'|| lpad(I, 3, '0');
593 l_site_number := l_new_location_code || '|' || p_to_id;
594 --
595 OPEN chk_locn_csr(p_site_number => l_site_number);
596 FETCH chk_locn_csr INTO l_dummy;
597 CLOSE chk_locn_csr;
598 --
599 IF l_dummy IS NULL THEN
600 --
601 l_new_location_code := l_site_number;
602 EXIT;
603 --
604 END IF;
605 --}
606 END LOOP;
607 --
608 IF l_new_location_code is null THEN
609 --{
610 OPEN get_supplier_name ( p_vendor_id => p_to_vendor_id ) ;
611 FETCH get_supplier_name INTO l_supplier_name;
612 CLOSE get_supplier_name;
613 --
614 fnd_message.set_name ( 'WSH', 'WSH_IB_SF_LOCN_CODE_CONFLICT' );
615 fnd_message.set_token( 'LOC_CODE', l_location_code );
616 fnd_message.set_token( 'SUPPLIER_NAME', l_supplier_name );
617 l_msg := FND_MESSAGE.GET;
618 wsh_util_core.printMsg( l_msg );
619 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
620 --
621 IF l_debug_on THEN
622 WSH_DEBUG_SV.pop(l_module_name);
623 END IF;
624 --
625 RETURN;
626 --}
627 END IF;
628 --}
629 ELSE
630 l_new_location_code := l_site_number;
631 l_loc_chg := FALSE;
632 END IF;
633 --
634 IF l_debug_on THEN
635 --{
636 WSH_DEBUG_SV.log(l_module_name, 'l_new_location_code', l_new_location_code);
637 WSH_DEBUG_SV.log(l_module_name, 'l_loc_chg', l_loc_chg);
638 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SUPPLIER_PARTY.CREATE_HZ_PARTY_SITE',WSH_DEBUG_SV.C_PROC_LEVEL);
639 --}
640 END IF;
641 --
642 Wsh_supplier_party.create_hz_party_site(
643 P_party_id => p_to_id,
644 P_location_id => p_location_id,
645 P_location_code => l_new_location_code,
646 x_party_site_id => l_to_party_site_id,
647 x_return_status => l_return_status
648 );
649 --
650 IF l_debug_on THEN
651 --
652 WSH_DEBUG_SV.log(l_module_name, 'x_party_site_id', l_to_party_site_id);
653 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_SUPPLIER_PARTY.CREATE_HZ_PARTY_SITE is', l_return_status);
654 --
655 END IF;
656 --
657 wsh_util_core.api_post_call(
658 p_return_status => l_return_status,
659 x_num_warnings => l_num_warnings,
660 x_num_errors => l_num_errors
661 );
662 --
663 l_location_id := p_location_id;
664 --
665 -- Now create a party Site use record for the party site that we
666 -- just created above.
667 --
668 WSH_SUPPLIER_PARTY.Create_HZ_Party_Site_uses
669 (
670 P_party_site_id => l_to_party_site_id,
671 P_site_use_type => 'SUPPLIER_SHIP_FROM',
672 x_party_site_use_id => l_party_site_use_id,
673 x_return_status => l_return_status
674 );
675 --
676 IF l_debug_on THEN
677 wsh_debug_sv.log(l_module_name, 'l_return_status', l_return_status);
678 wsh_debug_sv.log(l_module_name, 'l_party_site_use_id', l_party_site_use_id);
679 END IF;
680 --
681 wsh_util_core.api_post_call(
682 p_return_status => l_return_status,
683 x_num_warnings => l_num_warnings,
684 x_num_errors => l_num_errors
685 );
686 --
687 l_LocationIdTbl.DELETE;
688 l_LocationIdTbl(l_LocationIdTbl.COUNT+1) := l_location_id;
689 --
690 WSH_LOCATIONS_PKG.Insert_Location_Owners
691 (
692 pLocationIdTbl => l_LocationIdTbl,
693 p_location_source_code => 'HZ',
694 x_return_status => l_return_status
695 );
696 --
697 wsh_util_core.api_post_call(
698 p_return_status => l_return_status,
699 x_num_warnings => l_num_warnings,
700 x_num_errors => l_num_errors
701 );
702 --
703 -- Inactivate party site for the old vendor
704 --
705 IF l_debug_on THEN
706 wsh_debug_sv.logmsg(l_module_name, 'Calling InactivatePartySites', WSH_DEBUG_SV.C_PROC_LEVEL);
707 END IF;
708 --
709 InactivatePartySites
710 (
711 p_party_id => p_from_id,
712 p_party_site_id => l_from_party_site_id,
713 p_process_locations => TRUE,
714 p_to_id => p_to_id,
715 p_to_vendor_id => p_to_vendor_id,
716 x_return_status => l_return_status
717 );
718 --
719 IF l_debug_on THEN
720 wsh_debug_sv.log(l_module_name, 'Return status from InactivatePartySite', l_return_status);
721 END IF;
722 --
723 wsh_util_core.api_post_call(
724 p_return_status => l_return_status,
725 x_num_warnings => l_num_warnings,
726 x_num_errors => l_num_errors
727 );
728 --
729 IF l_debug_on THEN
730 wsh_debug_sv.log(l_module_name, 'l_loc_chg', l_loc_chg);
731 END IF;
732 --
733 IF l_loc_chg AND p_delivery_id IS NOT NULL THEN
734 --{
735 -- Store this location ID in a global table, so for any other
736 -- delivery with this ID we log an exception
737 --
738 l_count := g_LocChangeTab.COUNT + 1;
739 g_LocChangeTab(l_count).location_id := p_location_id;
740 g_LocChangeTab(l_count).old_loc_code := l_location_code;
741 g_LocChangeTab(l_count).new_loc_code := l_new_location_code;
742 --
743 -- Log an exception against delivery, if the SF location changes
744 --
745 fnd_message.set_name ( 'WSH', 'WSH_IB_SF_LOCN_CODE_CHG' );
746 fnd_message.set_token( 'L_LOCATION_CODE' , l_location_code );
747 fnd_message.set_token( 'L_NEW_LOCATION_CODE', l_new_location_code );
748 fnd_message.set_token( 'DELIVERY_NAME' , p_delivery_name );
749 l_msg := FND_MESSAGE.GET;
750 WSH_UTIL_CORE.printMsg(l_msg);
751 --
752 IF l_debug_on THEN
753 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
754 END IF;
755 --
756 wsh_xc_util.log_exception (
757 p_api_version => 1.0,
758 p_exception_name => 'WSH_IB_SF_LOCN_CODE_CHG',
759 p_logging_entity => 'SHIPPER',
760 p_logging_entity_id => FND_GLOBAL.USER_ID,
761 x_return_status => l_return_status,
762 x_exception_id => l_exception_id,
763 x_msg_data => l_xc_msg_data,
764 x_msg_count => l_xc_msg_count,
765 p_message => substrb ( l_msg, 1, 2000 ),
766 p_delivery_id => p_delivery_id,
767 p_exception_location_id => p_location_id,
768 p_logged_at_location_id => p_location_id
769 );
770 --
771 IF l_debug_on THEN
772 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_XC_UTIL.LOG_EXCEPTION is ', l_return_status);
773 END IF;
774 --
775 wsh_util_core.api_post_call(
776 p_return_status => l_return_status,
777 x_num_warnings => l_num_warnings,
778 x_num_errors => l_num_errors,
779 p_msg_data => l_xc_msg_data
780 );
781
782 --}
783 END IF;
784 --
785 IF l_debug_on THEN
786 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SUPPLIER_PARTY.PROCESS_HZ_CONTACT',WSH_DEBUG_SV.C_PROC_LEVEL);
787 END IF;
788 --
789 Wsh_supplier_party.Process_HZ_contact(
790 p_party_id => p_to_id,
791 p_party_site_id => l_to_party_site_id,
792 p_person_name => l_contact_rec.shipper_name,
793 p_phone => l_contact_rec.phone_number,
794 p_email => l_contact_rec.email_address,
795 x_return_status => l_return_status
796 );
797 --
798 IF l_debug_on THEN
799 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_SUPPLIER_PARTY.PROCESS_HZ_CONTACT', l_return_status);
800 END IF;
801 --
802 wsh_util_core.api_post_call(
803 p_return_status => l_return_status,
804 x_num_warnings => l_num_warnings,
805 x_num_errors => l_num_errors
806 );
807 --
808 IF l_num_errors > 0 THEN
809 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
810 ELSIF l_num_warnings > 0 THEN
811 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
812 END IF;
813 --
814 IF l_debug_on THEN
815 WSH_DEBUG_SV.pop(l_module_name);
816 END IF;
817 --
818 EXCEPTION
819 --
820 WHEN FND_API.G_EXC_ERROR THEN
821 --
822 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
823 --
824 IF l_debug_on THEN
825 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
826 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
827 END IF;
828 --
829 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
830 --
831 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
832 --
833 IF l_debug_on THEN
834 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
835 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
836 END IF;
837 --
838 WHEN OTHERS THEN
839 --
840 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
841 wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Create_Site');
842 --
843 IF l_debug_on THEN
844 WSH_DEBUG_SV.log(l_module_name,'Unexpected error has occured. Oracle error message is ', SUBSTRB(SQLERRM,1,200));
845 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
846 END IF;
847 --}
848 END Create_Site;
849 --
850
851
852
853 --========================================================================
854 -- PROCEDURE : Update_New_Delivery
855 -- PARAMETERS:
856 -- P_from_iD Merge from vendor ID
857 -- P_to_id Merge to vendor ID
858 -- P_to_party_id Merge to party ID
859 -- P_from_party_id Merge from party ID
860 -- P_delivery_id Delivery ID
861 -- P_from_site_id Merge from site ID
862 -- P_old_delivery_id Previous delivery ID
863 -- P_temp_update_flag Flag to update the temp table or not
864 -- P_location_id Delivery SF Location id
865 --
866 -- COMMENT : This is a private procedure to update the delivery records
867 -- with new vendor_id for the vendor merge.
868 --========================================================================
869
870 PROCEDURE Update_New_Delivery (
871 p_from_id IN NUMBER,
872 p_to_id IN NUMBER,
873 p_to_party_id IN NUMBER,
874 p_from_party_id IN NUMBER,
875 p_delivery_id IN NUMBER,
876 p_from_site_id IN NUMBER,
877 p_old_delivery_id IN NUMBER,
878 p_temp_update_flag IN VARCHAR2,
879 p_location_id IN NUMBER,
880 x_return_status OUT NOCOPY VARCHAR2
881 ) IS
882 --
883 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_NEW_DELIVERY';
884 l_debug_on BOOLEAN;
885 --
886 l_return_status VARCHAR2(2);
887 l_dlvy_name VARCHAR2(30);
888 l_location_id NUMBER;
889 l_msg_data VARCHAR2(32767);
890 l_num_warnings NUMBER :=0;
891 l_num_errors NUMBER :=0;
892
893 --
894 BEGIN
895 --{
896 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
897 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
898 IF l_debug_on IS NULL THEN
899 --{
900 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
901 --}
902 END IF;
903
904 --
905 IF l_debug_on THEN
906 --{
907 WSH_DEBUG_SV.push(l_module_name);
908 WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
909 WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
910 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id );
911 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id );
912 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
913 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID', p_from_site_id );
914 WSH_DEBUG_SV.log(l_module_name,'P_OLD_DELIVERY_ID', p_old_delivery_id );
915 WSH_DEBUG_SV.log(l_module_name,'P_TEMP_UPDATE_FLAG', p_temp_update_flag );
916 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
917 --}
918 END IF;
919
920
921 IF (p_delivery_id IS NOT NULl) THEN
922 --{
923 UPDATE wsh_new_deliveries
924 SET vendor_id = p_to_id,
925 party_id = p_to_party_id,
926 last_update_date = sysdate,
927 last_updated_by = fnd_global.user_id,
928 last_update_login = fnd_global.login_id
929 WHERE delivery_id = p_delivery_id
930 RETURNING name INTO l_dlvy_name;
931 IF l_debug_on THEN
932 --{
933 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
934 --}
935 END IF;
936
937 ELSE
938
939 UPDATE wsh_new_deliveries
940 SET vendor_id = p_to_id,
941 party_id = p_to_party_id,
942 last_update_date = sysdate,
943 last_updated_by = fnd_global.user_id,
944 last_update_login = fnd_global.login_id
945 WHERE delivery_id
946 IN ( SELECT delivery_id
947 FROM wsh_delivery_assignments
948 WHERE delivery_detail_id
949 IN ( SELECT delivery_detail_id
950 FROM wsh_delivery_details
951 WHERE source_code = 'PO'
952 AND vendor_id = p_from_id
953 AND ship_from_site_id = p_from_site_id
954 AND source_header_id
955 IN (SELECT po_header_id
956 FROM po_headers_all
957 WHERE vendor_id = p_to_id
958 )
959 )
960 )
961 AND vendor_id = p_from_id;
962 IF l_debug_on THEN
963 --{
964 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
965 --}
966 END IF;
967 --}
968 END IF;
969
970
971 IF (p_temp_update_flag = 'Y') THEN
972 --{
973 UPDATE wsh_wms_sync_tmp
974 SET temp_col = 'Y',
975 parent_delivery_detail_id = p_delivery_id
976 WHERE delivery_id = p_old_delivery_id
977 AND operation_type = 'VENDOR_MRG';
978 ELSIF (p_temp_update_flag = 'N') THEN
979 UPDATE wsh_wms_sync_tmp
980 SET temp_col = 'Y'
981 WHERE delivery_id = p_old_delivery_id
982 AND operation_type = 'VENDOR_MRG';
983 --}
984 END IF;
985
986 IF l_debug_on THEN
987 --{
988 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);
989 --}
990 END IF;
991
992
993 IF p_location_id IS NOT NULL THEN
994 --{
995 SELECT source_location_id INTO l_location_id
996 FROM wsh_locations
997 WHERE wsh_location_id = p_location_id;
998
999 IF l_debug_on THEN
1000 --{
1001 WSH_DEBUG_SV.log(l_module_name, 'l_location_id', l_location_id);
1002 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);
1003 --}
1004 END IF;
1005
1006 create_site (
1007 p_from_id =>p_from_party_id,
1008 p_to_id =>p_to_party_id,
1009 p_to_vendor_id => p_to_id,
1010 p_delivery_id =>p_delivery_id,
1011 P_delivery_name =>l_dlvy_name,
1012 P_location_id =>l_location_id,
1013 x_return_status =>l_return_status
1014 );
1015 wsh_util_core.api_post_call(
1016 p_return_status => l_return_status,
1017 x_num_warnings => l_num_warnings,
1018 x_num_errors => l_num_errors
1019 );
1020 IF l_debug_on THEN
1021 --{
1022 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);
1023 --}
1024 END IF;
1025 --}
1026 END IF;
1027
1028 IF l_num_errors > 0 THEN
1029 --{
1030 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1031 ELSIF l_num_warnings > 0 THEN
1032 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1033 --}
1034 END IF;
1035 --
1036 IF l_debug_on THEN
1037 WSH_DEBUG_SV.pop(l_module_name);
1038 END IF;
1039
1040 EXCEPTION
1041 --
1042 WHEN FND_API.G_EXC_ERROR THEN
1043 --
1044 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1045 --
1046 IF l_debug_on THEN
1047 --
1048 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
1049 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1050 --
1051 END IF;
1052 --
1053 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1054 --
1055 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1056 --
1057 IF l_debug_on THEN
1058 --
1059 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1060 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1061 --
1062 END IF;
1063 --
1064 WHEN OTHERS THEN
1065 --
1066 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1067 wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Update_New_Delivery');
1068 --
1069 IF l_debug_on THEN
1070 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1071 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1072 END IF;
1073 --
1074
1075 --}
1076 END Update_New_Delivery;
1077
1078
1079 --
1080 --
1081 --========================================================================
1082 -- PROCEDURE : Update_Non_PO_Entities
1083 -- PARAMETERS:
1084 -- P_to_id Merge to vendor ID
1085 -- P_from_id Merge from vendor ID
1086 -- P_from_party_id Merge from party ID
1087 -- P_to_party_id Merge to party ID
1088 -- P_from_site_id Merge from vendor site ID
1089 -- P_to_site_id Merge to vendor site ID
1090 -- X_return_status Return status
1091 -- p_site_merge Site level Merge
1092 -- p_from_supplier_name Merge from Supplier Name
1093
1094 -- COMMENT : This is a procedure to Update for entities which are
1095 -- not dependent on the invoice/PO selection
1096 --========================================================================
1097 PROCEDURE Update_Non_PO_Entities(
1098 p_to_id IN NUMBER,
1099 p_from_id IN NUMBER,
1100 p_from_party_id IN NUMBER,
1101 p_to_party_id IN NUMBER,
1102 p_to_site_id IN NUMBER,
1103 p_from_site_id IN NUMBER,
1104 p_site_merge IN BOOLEAN,
1105 p_from_supplier_name IN VARCHAR2,
1106 x_return_status OUT NOCOPY VARCHAR2
1107 ) IS
1108
1109 l_return_status VARCHAR2(1);
1110 l_debug_on BOOLEAN;
1111 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_NON_PO_ENTITIES';
1112
1113
1114 CURSOR check_calendar IS
1115 SELECT calendar_type,
1116 calendar_assignment_id,
1117 vendor_site_id,
1118 association_type,
1119 freight_code
1120 FROM wsh_calendar_assignments a
1121 WHERE vendor_id = p_from_id
1122 AND vendor_site_id IS NULL;
1123
1124 CURSOR check_site_calendar IS
1125 SELECT a.calendar_type,
1126 a.calendar_assignment_id,
1127 a.vendor_site_id,
1128 a.association_type,
1129 a.freight_code,
1130 b.vendor_site_code,
1131 a.calendar_code
1132 FROM wsh_calendar_assignments a,
1133 po_vendor_sites_all b
1134 WHERE a.vendor_id = p_from_id
1135 AND a.vendor_site_id = p_from_site_id
1136 AND b.vendor_site_id = a.vendor_site_id;
1137
1138 CURSOR check_dup_assignment( p_vendor_id NUMBER,
1139 p_calendar_Type VARCHAR2,
1140 p_vendor_site_id NUMBER,
1141 p_association_type VARCHAR2,
1142 p_freight_code VARCHAR2 )
1143 IS
1144 SELECT 1
1145 FROM wsh_calendar_assignments
1146 WHERE vendor_id = p_vendor_id
1147 AND calendar_type=p_calendar_type
1148 AND nvl( vendor_site_id,-999999 ) = nvl( p_vendor_site_id,-999999 )
1149 AND association_type = p_association_type
1150 AND nvl( freight_code, '!!!' ) = nvl( p_freight_code, '!!!' );
1151 --
1152 l_dummy NUMBER;
1153 --
1154
1155 CURSOR get_party_site_csr(p_party_id NUMBER) IS
1156 SELECT location_id,
1157 hps.party_site_id,
1158 hps.object_version_number
1159 FROM hz_party_sites hps,
1160 hz_party_site_uses hpsu
1161 WHERE hps.party_id = p_party_id
1162 AND hps.party_site_id = hpsu.party_site_id
1163 AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM'
1164 AND hpsu.status = 'A';
1165
1166
1167 l_from_party_rec hz_party_site_v2pub.party_site_rec_type;
1168 l_msg_data VARCHAR2(32767);
1169 l_num_warnings NUMBER :=0;
1170 l_num_errors NUMBER :=0;
1171 l_object_version_number NUMBER;
1172 l_msg VARCHAR2(32767);
1173 l_query_count NUMBER :=0;
1174 l_msg_count NUMBER ;
1175 --
1176 CURSOR c_VendorLvlCalAsg(p_vendorID NUMBER,
1177 p_assnType VARCHAR2,
1178 p_caltype VARCHAR2) IS
1179 SELECT 1
1180 FROM wsh_calendar_assignments
1181 WHERE vendor_id = p_vendorID
1182 AND association_type = p_assnType
1183 AND calendar_type = p_calType
1184 AND vendor_site_id IS NULL;
1185 --
1186 v_VendorLvlCalAsg NUMBER := 0;
1187 l_CalAsgInfo WSH_CAL_ASG_PKG.CalAsgRecType;
1188 l_CalAsgId NUMBER;
1189 --
1190 BEGIN
1191
1192
1193 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1194 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1195
1196 IF l_debug_on IS NULL THEN
1197 --{
1198 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1199 --}
1200 END IF;
1201
1202
1203 IF l_debug_on THEN
1204 --{
1205 WSH_DEBUG_SV.push(l_module_name);
1206 WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1207 WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1208 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1209 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1210 WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1211 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1212 WSH_DEBUG_SV.log(l_module_name,'P_SITE_MERGE',p_site_merge);
1213 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SUPPLIER_NAME',p_from_supplier_name);
1214 --}
1215 END IF;
1216
1217 -- Update WSH_CARRIERS with the merge to vendor/vendor site
1218 UPDATE wsh_carriers
1219 SET supplier_id = p_to_id,
1220 supplier_site_id = p_to_site_id,
1221 last_update_date = sysdate,
1222 last_updated_by = fnd_global.user_id,
1223 last_update_login = fnd_global.login_id
1224 WHERE supplier_id = p_from_id
1225 AND supplier_site_id = p_from_site_id;
1226 --
1227 IF l_debug_on THEN
1228 --{
1229 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_carriers. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1230 --}
1231 END IF;
1232 --
1233 -- Update WSH_CARRIER_SITES with the merge to vendor site
1234 --
1235 UPDATE wsh_carrier_sites
1236 SET supplier_site_id = p_to_site_id,
1237 last_update_date = sysdate,
1238 last_updated_by = fnd_global.user_id,
1239 last_update_login = fnd_global.login_id
1240 WHERE supplier_site_id = p_from_site_id;
1241 --
1242 IF l_debug_on THEN
1243 --{
1244 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_carrier_sites. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1245 --}
1246 END IF;
1247 --
1248 WSH_PARTY_MERGE.Update_Entities_During_Merge
1249 (
1250 p_to_id => p_to_id,
1251 p_from_id => p_from_id,
1252 p_from_party_id => p_from_party_id ,
1253 p_to_party_id => p_to_party_id ,
1254 p_to_site_id => p_to_site_id,
1255 p_from_site_id => p_from_site_id,
1256 p_site_merge => p_site_merge,
1257 p_from_supplier_name => p_from_supplier_name,
1258 x_return_status => l_return_status
1259 );
1260 --
1261 wsh_util_core.api_post_call
1262 (
1263 p_return_status => l_return_status,
1264 x_num_warnings => l_num_warnings,
1265 x_num_errors => l_num_errors
1266 );
1267 --
1268 -- Now, update/delete vendor site level calendar assignments if any
1269 --
1270 FOR check_site_calendar_rec IN check_site_calendar
1271 LOOP
1272 --{
1273 IF l_debug_on THEN
1274 --{
1275 WSH_DEBUG_SV.logmsg(l_module_name, '-----------------------', WSH_DEBUG_SV.C_STMT_LEVEL);
1276 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);
1277 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);
1278 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);
1279 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);
1280 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);
1281 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);
1282 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);
1283 --}
1284 END IF;
1285 OPEN check_dup_assignment( p_vendor_id => p_to_id,
1286 p_calendar_Type => check_site_calendar_rec.calendar_type,
1287 p_vendor_site_id=> p_to_site_id,
1288 p_association_type => check_site_calendar_rec.association_type ,
1289 p_freight_code=> check_site_calendar_rec.freight_code );
1290 FETCH check_dup_assignment INTO l_dummy;
1291
1292 IF (check_dup_assignment%NOTFOUND) THEN
1293 --{
1294 -- Update vendor site level assignments
1295 UPDATE wsh_calendar_assignments
1296 SET vendor_id = p_to_id,
1297 vendor_site_id = p_to_site_id,
1298 last_update_date = sysdate,
1299 last_updated_by = fnd_global.user_id,
1300 last_update_login = fnd_global.login_id
1301 WHERE calendar_assignment_id = check_site_calendar_rec.calendar_assignment_id;
1302 --
1303 IF l_debug_on THEN
1304 --{
1305 WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID updated',
1306 check_site_calendar_rec.calendar_assignment_id);
1307 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_calendar_assignments. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1308 --}
1309 END IF;
1310 --
1311 -- Is there a vendor level calendar assignment ? If not, create one
1312 -- otherwise, the above entry will never show up in the form
1313 --
1314 OPEN c_VendorLvlCalAsg(p_vendorID => p_to_id,
1315 p_assnType => 'VENDOR',
1316 p_calType => check_site_calendar_rec.calendar_type);
1317 FETCH c_VendorLvlCalAsg INTO v_VendorLvlCalAsg;
1318 CLOSE c_VendorLvlCalAsg;
1319 --
1320 IF l_debug_on THEN
1321 WSH_DEBUG_SV.log(l_module_name, 'Vendor Lvl Cal. Asg Exists ? ', v_VendorLvlCalAsg);
1322 END IF;
1323 --
1324 IF v_VendorLvlCalAsg = 0 THEN
1325 --{
1326 --
1327 IF l_debug_on THEN
1328 WSH_DEBUG_SV.log(l_module_name, 'Creating vendor level Cal. Asg', p_to_id);
1329 END IF;
1330 --
1331 l_CalAsgInfo.CALENDAR_CODE := check_site_calendar_rec.calendar_code;
1332 l_CalAsgInfo.CALENDAR_TYPE := check_site_calendar_rec.calendar_type;
1333 l_CalAsgInfo.ENABLED_FLAG := 'Y';
1334 l_CalAsgInfo.ASSOCIATION_TYPE := 'VENDOR';
1335 l_CalAsgInfo.VENDOR_ID := p_to_id;
1336 --
1337 WSH_CAL_ASG_PKG.Create_Cal_Asg
1338 (
1339 p_api_version_number => 1.0,
1340 p_cal_asg_info => l_CalAsgInfo,
1341 x_return_status => l_return_status,
1342 x_msg_count => l_msg_count,
1343 x_msg_data => l_msg_data,
1344 x_Calendar_Aassignment_Id => l_CalAsgId
1345 );
1346 --
1347 IF l_debug_on THEN
1348 WSH_DEBUG_SV.log(l_module_name, 'Return status from CAL. API', l_return_status);
1349 WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID', l_CalAsgId);
1350 END IF;
1351 --
1352 wsh_util_core.api_post_call
1353 (
1354 p_return_status => l_return_status,
1355 x_num_warnings => l_num_warnings,
1356 x_num_errors => l_num_errors,
1357 p_msg_data => l_msg
1358 );
1359 --}
1360 END IF;
1361 --
1362
1363 --}
1364 ELSE
1365 --{
1366 DELETE wsh_calendar_assignments
1367 WHERE calendar_assignment_id = check_site_calendar_rec.calendar_assignment_id;
1368 IF l_debug_on THEN
1369 --{
1370 WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID deleted',
1371 check_site_calendar_rec.calendar_assignment_id);
1372 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);
1373 --}
1374 END IF;
1375
1376 IF check_site_calendar_rec.freight_code IS NULL THEN
1377 --{
1378 fnd_message.set_name ( 'WSH', 'WSH_IB_DEL_SP_SITE_CAL_ASGN' );
1379 ELSE
1380 fnd_message.set_name ( 'WSH', 'WSH_IB_DEL_SP_SITE_FC_CAL_ASGN' );
1381 fnd_message.set_token( 'FREIGHT_CODE' , check_site_calendar_rec.freight_code );
1382 --}
1383 END IF;
1384 fnd_message.set_token( 'SUPPLIER_NAME' , p_from_supplier_name );
1385 fnd_message.set_token( 'CAL_TYPE' , check_site_calendar_rec.calendar_type );
1386 fnd_message.set_token( 'SITE_CODE' , check_site_calendar_rec.vendor_site_code );
1387 l_msg := FND_MESSAGE.GET;
1388 wsh_util_core.printMsg( l_msg );
1389 --}
1390 END IF;--IF (check_dup_assignment%NOTFOUND)
1391
1392 CLOSE check_dup_assignment;
1393 --}
1394 END LOOP;-- FOR check_site_calendar_rec IN check_site_calendar
1395 --
1396 -- Inactive the party_site
1397 --
1398 IF NOT p_site_merge THEN
1399 --{
1400 IF l_debug_on THEN
1401 wsh_debug_sv.logmsg(l_module_name, 'Calling InactivatePartySites', WSH_DEBUG_SV.C_PROC_LEVEL);
1402 END IF;
1403 --
1404 InactivatePartySites
1405 (
1406 p_party_id => p_from_party_id,
1407 p_to_id => p_to_party_id,
1408 p_to_vendor_id => p_to_id,
1409 x_return_status => l_return_status
1410 );
1411 --
1412 IF l_debug_on THEN
1413 wsh_debug_sv.log(l_module_name, 'Return Status from InactivatePartySites', l_return_status);
1414 END IF;
1415 --
1416 wsh_util_core.api_post_call
1417 (
1418 p_return_status => l_return_status,
1419 x_num_warnings => l_num_warnings,
1420 x_num_errors => l_num_errors,
1421 p_msg_data => l_msg
1422 );
1423 --}
1424 END IF;
1425 --
1426 IF l_num_errors > 0 THEN
1427 --{
1428 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1429 ELSIF l_num_warnings > 0 THEN
1430 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1431 --}
1432 END IF;
1433 --
1434 IF l_debug_on THEN
1435 WSH_DEBUG_SV.pop(l_module_name);
1436 END IF;
1437 --
1438 EXCEPTION
1439 --
1440 WHEN FND_API.G_EXC_ERROR THEN
1441 --
1442 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1443 --
1444 IF l_debug_on THEN
1445 --
1446 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
1447 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1448 --
1449 END IF;
1450 --
1451 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1452 --
1453 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1454 --
1455 IF l_debug_on THEN
1456 --
1457 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1458 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1459 --
1460 END IF;
1461 --
1462 WHEN OTHERS THEN
1463 --
1464 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1465 wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Update_Non_PO_Entities');
1466 --
1467 IF l_debug_on THEN
1468 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1469 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1470 END IF;
1471 --
1472 END Update_Non_PO_Entities;
1473
1474
1475 --========================================================================
1476 -- PROCEDURE :Vendor_Merge
1477 -- PARAMETERS:
1478 -- P_from_id Merge from vendor ID
1479 -- P_to_id Merge to vendor ID
1480 -- P_from_party_id Merge from party ID
1481 -- P_to_party_id Merge to party ID
1482 -- P_from_site_id Merge from vendor site ID
1483 -- P_to_site_id Merge to vendor site ID
1484 -- p_calling_mode Either 'INVOICE' or 'PO'
1485 -- x_return_status Return status
1486 --
1487 -- COMMENT :
1488 -- This is the core WSH Vendor merge routine that is called from
1489 -- Vendor_Party_Merge() API.
1490 -- This procedure can be divided into two portions, merge validation and merge.
1491 -- In the first portion, it will determine if the vendor merge is allowed.
1492 -- In the second portion, it will update all the affected tables if merge is allowed
1493 --
1494 -- Parameter p_calling_mode indicates what updates to perform.
1495 -- 'INVOICE' ==> Update only non-PO entities
1496 -- 'PO' ==> Update PO related entities
1497 --========================================================================
1498 PROCEDURE Vendor_Merge (
1499 p_from_id IN NUMBER,
1500 p_to_id IN NUMBER,
1501 p_from_party_id IN NUMBER,
1502 p_to_party_id IN NUMBER,
1503 p_from_site_id IN NUMBER,
1504 p_to_site_id IN NUMBER,
1505 p_calling_mode IN VARCHAR2,
1506 x_return_status OUT NOCOPY VARCHAR2 ) IS
1507 --
1508 l_return_status VARCHAR2(1);
1509 l_debug_on BOOLEAN;
1510 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VENDOR_MERGE';
1511 --
1512 CURSOR check_vendor_active (p_vendor_id NUMBER) IS
1513 SELECT end_date_active,
1514 vendor_name
1515 FROM po_vendors
1516 WHERE vendor_id = p_vendor_id;
1517 --
1518 l_end_date_active DATE;
1519 l_supplier_name VARCHAR2(360);
1520 --
1521 /*
1522 * R12 Perf Bug 4949639 : Do not need this cursor any more
1523 * since we rely on the parameter p_calling_mode
1524 CURSOR check_option IS
1525 SELECT process
1526 FROM ap_duplicate_vendors_all
1527 WHERE vendor_id = p_to_id
1528 AND vendor_site_id = p_to_site_id
1529 AND duplicate_vendor_id = p_from_id
1530 AND duplicate_vendor_site_id = p_from_site_id;
1531 */
1532 --
1533 l_option VARCHAR2(25);
1534 --
1535 CURSOR check_po IS
1536 SELECT w.delivery_id,
1537 d.delivery_detail_id
1538 FROM po_headers_all p,
1539 wsh_delivery_details d,
1540 wsh_delivery_assignments w,
1541 Wsh_new_deliveries wnd
1542 WHERE p.vendor_id = p_to_id
1543 AND d.source_code = 'PO'
1544 AND p.po_header_id = d.source_header_id
1545 AND p.vendor_site_id = p_to_site_id
1546 AND d.vendor_id = p_from_id
1547 AND d.ship_From_site_id=p_from_site_id
1548 AND d.delivery_detail_id = w.delivery_detail_id
1549 AND w.delivery_id = wnd.delivery_id(+)
1550 AND nvl(w.type, 'S') IN ('S' ,'O');
1551 --
1552 l_delivery_list wsh_util_core.id_tab_type;
1553 l_dd_list wsh_util_core.id_tab_type;
1554 --
1555
1556 CURSOR find_delivery IS
1557 SELECT distinct delivery_id
1558 FROM wsh_wms_sync_tmp
1559 WHERE operation_type = 'VENDOR_MRG'
1560 AND temp_col IS NULL
1561 AND delivery_id IS NOT NULL;
1562 --
1563 TYPE l_dlvy_rec IS RECORD (delivery_id NUMBER);
1564 TYPE t_delivery_tbl IS TABLE OF l_dlvy_rec INDEX BY BINARY_INTEGER;
1565 l_delivery_rec_tbl t_delivery_tbl;
1566 l_delivery_rec l_dlvy_rec;
1567 --
1568
1569 CURSOR check_duplicate_vendor(p_del_id NUMBER) IS
1570 SELECT 'Y' ,
1571 wnd.initial_pickup_location_id,
1572 wnd.status_code,
1573 wnd.routing_response_id,
1574 wnd.name,
1575 wnd.ultimate_dropoff_location_id
1576 FROM wsh_new_deliveries wnd,
1577 wsh_delivery_details wdd,
1578 wsh_delivery_assignments wda
1579 WHERE wnd.delivery_id = p_del_id
1580 AND wnd.delivery_id = wda.delivery_id
1581 AND wdd.delivery_detail_id = wda.delivery_detail_id
1582 AND wdd.ship_from_site_id <> p_from_site_id
1583 AND wdd.ship_from_site_id <> p_to_site_id
1584 AND wdd.vendor_id = p_from_id
1585 AND nvl(wda.type,'S') IN ('S' , 'O')
1586 AND NOT EXISTS (SELECT 1
1587 FROM ap_duplicate_vendors_all
1588 WHERE process_flag IN ('S', 'D')
1589 AND process IN ('P','B')
1590 AND duplicate_vendor_id = wdd.vendor_id
1591 AND duplicate_vendor_site_id = wdd.ship_from_site_id
1592 AND vendor_id = p_to_id
1593 );
1594 --
1595 l_dup VARCHAR2(1) := 'N';
1596 l_location_id NUMBER;
1597 l_dlvy_status_code VARCHAR2(30);
1598 l_routing_response_id NUMBER;
1599 l_dlvy_name VARCHAR2(30);
1600 l_ult_dropoff_loc_id NUMBER;
1601 --
1602
1603 CURSOR check_temp (p_delivery_id NUMBER) IS
1604 SELECT parent_delivery_detail_id
1605 FROM wsh_wms_sync_tmp wwst,
1606 wsh_delivery_details wdd
1607 WHERE wwst.delivery_id = p_delivery_id
1608 AND wwst.temp_col IS NOT NULL
1609 AND operation_type = 'VENDOR_MRG'
1610 AND wdd.delivery_detail_id = wwst.delivery_detail_id
1611 AND wdd.vendor_id = p_to_id
1612 AND wwst.parent_delivery_detail_id IS NOT NULL;
1613 --
1614 l_temp NUMBER;
1615 --
1616
1617 CURSOR check_detail(p_delivery_id NUMBER) IS
1618 SELECT delivery_detail_id
1619 FROM wsh_wms_sync_tmp
1620 WHERE delivery_id = p_delivery_id
1621 AND operation_type = 'VENDOR_MRG'
1622 AND temp_col IS NULL;
1623 --
1624 l_delivery_detail_tbl wsh_util_core.id_tab_type;
1625 --
1626 CURSOR dlvy_rr_csr(p_delivery_id NUMBER) IS
1627 SELECT wdd.delivery_detail_id,
1628 wdd.routing_req_id,
1629 wdd.vendor_id,
1630 wth.receipt_number rr_number,
1631 wth.revision_number,
1632 wnd.ultimate_dropoff_location_id,
1633 wnd.name
1634 FROM wsh_delivery_details wdd,
1635 wsh_delivery_assignments wda ,
1636 wsh_inbound_txn_history wth,
1637 wsh_new_deliveries wnd
1638 WHERE wda.delivery_id = p_delivery_id
1639 AND nvl(wda.type,'S') IN ('S','O')
1640 AND wda.delivery_detail_Id = wdd.delivery_detail_id
1641 AND wdd.routing_req_id = wth.transaction_id
1642 AND wth.transaction_type='ROUTING_REQUEST'
1643 AND wdd.vendor_id <> wth.supplier_id
1644 AND wnd.delivery_id = wda.delivery_id
1645 ORDER BY routing_req_id;
1646 --
1647 TYPE l_delivery_rr_rec IS RECORD(
1648 delivery_detail_id NUMBER,
1649 routing_req_id NUMBER,
1650 vendor_id NUMBER,
1651 rr_number VARCHAR2(40),
1652 revision_number NUMBER,
1653 ult_dropoff_loc_id NUMBER,
1654 name VARCHAR2(30)
1655 );
1656 TYPE t_delivery_rr_type IS TABLE OF l_delivery_rr_rec INDEX BY BINARY_INTEGER;
1657 l_dlvy_rr_rec_tbl t_delivery_rr_type;
1658 l_dlvy_rr_rec l_delivery_rr_rec;
1659
1660 l_prev_old_rr_id NUMBER;
1661 l_new_rr_id NUMBER;
1662 l_new_rr_number VARCHAR2(40);
1663 --
1664
1665 CURSOR chk_rreq_csr(p_supplier_id NUMBER,
1666 p_rr_number VARCHAR2) IS
1667 SELECT transaction_id,
1668 revision_number,
1669 parent_shipment_header_id
1670 FROM wsh_inbound_txn_history
1671 WHERE supplier_id = p_supplier_id
1672 AND receipt_number = p_rr_number
1673 AND transaction_type='ROUTING_REQUEST'
1674 ORDER BY revision_number DESC;
1675 --
1676 l1_transaction_id NUMBER;
1677 l1_revision_number NUMBER;
1678 l1_parent_shipment_header_id NUMBER;
1679 --
1680
1681 l_delivery_cache_tbl wsh_util_core.id_tab_type;
1682 l_dlvy_rr_cache_tbl wsh_util_core.id_tab_type;
1683 l_dlvy_rr_tbl wsh_new_deliveries_pvt.Delivery_Attr_Tbl_Type;
1684 l_dlvy_tbl wsh_util_core.id_tab_type;
1685 l_chk_delivery_id NUMBER :=0;
1686 l_prev_new_rr_id NUMBER :=0;
1687 l_site_merge BOOLEAN;
1688 l_new_delivery_id NUMBER;
1689 j NUMBER := 0;
1690 i NUMBER;
1691 l_exception_id NUMBER;
1692 l_msg VARCHAR2(32767);
1693 l_msg_count NUMBER ;
1694 l_xc_msg_data VARCHAR2(2000);
1695 l_num_warnings NUMBER :=0;
1696 l_num_errors NUMBER :=0;
1697 l_tmp_rr_number VARCHAR2(40);
1698 l_txn_history_rec wsh_inbound_txn_history_pkg.ib_txn_history_rec_type;
1699 l_cache_index NUMBER;
1700 l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
1701 l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
1702 l_defaults_rec WSH_DELIVERIES_GRP.default_parameters_rectype;
1703 l_msg_data VARCHAR2(32767);
1704 l_xc_msg_count NUMBER;
1705 l_respIndex NUMBER;
1706 l_numRowsUpdated NUMBER;
1707 --
1708 BEGIN
1709 --{
1710 --
1711 SAVEPOINT WSH_Vendor_Merge;
1712 --
1713 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1714 wsh_util_core.g_call_fte_load_tender_api := FALSE;
1715 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1716
1717 IF l_debug_on IS NULL THEN
1718 --{
1719 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1720 --}
1721 END IF;
1722
1723
1724 IF l_debug_on THEN
1725 --{
1726 WSH_DEBUG_SV.push(l_module_name);
1727 WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1728 WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1729 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1730 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1731 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1732 WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1733 WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
1734 --}
1735 END IF;
1736
1737 wsh_util_core.enable_concurrent_log_print ;
1738
1739 -- find out if it is a vendor merge OR vendor site merge
1740 OPEN check_vendor_active(p_vendor_id => p_from_id);
1741 FETCH check_vendor_active INTO l_end_date_active, l_supplier_name;
1742
1743 IF check_vendor_active%NOTFOUND THEN
1744 --{
1745 CLOSE check_vendor_active;
1746 IF l_debug_on THEN
1747 --{
1748 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);
1749 --}
1750 END IF;
1751 fnd_message.set_name ( 'WSH', 'WSH_IB_VENDOR_NOT_EXISTS' );
1752 fnd_message.set_token( 'VENDOR_ID' , to_char(p_from_id) );
1753 l_msg := FND_MESSAGE.GET;
1754 wsh_util_core.printMsg( l_msg );
1755 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1756 --
1757 IF l_debug_on THEN
1758 WSH_DEBUG_SV.pop(l_module_name);
1759 END IF;
1760 --
1761 RETURN;
1762 --}
1763 END IF;
1764
1765 CLOSE check_vendor_active;
1766
1767 IF nvl(l_end_date_active,sysdate+1) <= sysdate THEN
1768 --{
1769 l_site_merge := false;
1770 ELSE
1771 l_site_merge := true;
1772 --}
1773 END IF;
1774
1775 IF l_debug_on THEN
1776 --{
1777 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);
1778 WSH_DEBUG_SV.log(l_module_name,'L_SITE_MERGE = ',l_site_merge, WSH_DEBUG_SV.C_STMT_LEVEL);
1779 --}
1780 END IF;
1781
1782 -- Update for entities which are not dependent on the invoice/PO selection
1783 IF l_debug_on THEN
1784 --{
1785 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);
1786 --}
1787 END IF;
1788 --
1789 -- Update non-PO entities irrespective of calling mode
1790 --
1791 Update_Non_PO_Entities(
1792 p_to_id => p_to_id,
1793 p_from_id => p_from_id,
1794 p_from_party_id => p_from_party_id ,
1795 p_to_party_id => p_to_party_id ,
1796 p_to_site_id => p_to_site_id,
1797 p_from_site_id => p_from_site_id,
1798 p_site_merge => l_site_merge,
1799 p_from_supplier_name => l_supplier_name,
1800 X_return_status => l_return_status
1801 );
1802 --
1803 wsh_util_core.api_post_call(
1804 p_return_status => l_return_status,
1805 x_num_warnings => l_num_warnings,
1806 x_num_errors => l_num_errors
1807 );
1808 --
1809 -- Update PO related entities only when the mode is PO
1810 --
1811 IF (p_calling_mode = 'PO') THEN
1812 --{
1813 -- Determine the invoice/PO selections
1814 --
1815 -- R12 Perf Bug 4949639 : Do not need this any more since we rely
1816 -- on the parameter p_calling_mode
1817 --
1818 --OPEN check_option;
1819 --FETCH check_option INTO l_option;
1820 --CLOSE check_option;
1821 --
1822 --IF l_debug_on THEN
1823 --WSH_DEBUG_SV.logmsg(l_module_name,'l_OPTION = ' || l_option, WSH_DEBUG_SV.C_STMT_LEVEL);
1824 --END IF;
1825 --
1826
1827 --IF (l_option= 'B' OR l_option = 'P') THEN
1828 --{
1829 --Find out all delivery detail lines impacted by the site merge AND insert the records into a temp table
1830 OPEN check_po;
1831 FETCH check_po BULK COLLECT INTO l_delivery_list, l_dd_list;
1832 CLOSE check_po;
1833 IF l_debug_on THEN
1834 --{
1835 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);
1836 --}
1837 END IF;
1838
1839 IF l_dd_list.COUNT > 0 THEN
1840 --{
1841 FORALL j IN l_dd_list.FIRST..l_dd_list.LAST
1842 INSERT INTO wsh_wms_sync_tmp
1843 ( delivery_detail_id,
1844 delivery_id,
1845 operation_type,
1846 creation_date )
1847 VALUES ( l_dd_list(j),
1848 l_delivery_list(j),
1849 'VENDOR_MRG',
1850 sysdate );
1851
1852 IF l_debug_on THEN
1853 --{
1854 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);
1855 --}
1856 END IF;
1857
1858 --
1859 FORALL j IN l_dd_list.FIRST..l_dd_list.LAST
1860 UPDATE wsh_delivery_details
1861 SET vendor_id = p_to_id,
1862 ship_from_site_id = p_to_site_id,
1863 party_id = p_to_party_id,
1864 last_update_date = sysdate,
1865 last_updated_by = fnd_global.user_id,
1866 last_update_login = fnd_global.login_id
1867 WHERE delivery_detail_id = l_dd_list(j);
1868 IF SQL%ROWCOUNT <> l_dd_list.count THEN
1869 --{
1870 IF l_debug_on THEN
1871 --{
1872 WSH_DEBUG_SV.log(l_module_name, 'Updated WDD records with vendor/vendor site', p_to_id || ' - ' || p_To_site_id);
1873 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);
1874 --}
1875 END IF;
1876 fnd_message.set_name ( 'WSH', 'WSH_IB_DLY_DET_UPDT_MISMATCH' );
1877 fnd_message.set_token( 'NUM_DETAILS_AFFECTED' , to_char(l_dd_list.count) );
1878 fnd_message.set_token( 'NUM_DETAILS_UPDATED' , to_char(SQL%ROWCOUNT) );
1879 l_msg := FND_MESSAGE.GET;
1880 wsh_util_core.printMsg( l_msg );
1881 --}
1882 END IF;
1883 IF l_debug_on THEN
1884 --{
1885 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1886 --}
1887 END IF;
1888
1889 --}
1890 END IF;--IF l_dd_list.COUNT > 0
1891 --
1892 IF p_from_id <> p_to_id THEN
1893 --{
1894 -- Determine how many deliveries that are impacted
1895 --
1896 OPEN find_delivery;
1897 FETCH find_delivery BULK COLLECT INTO l_delivery_rec_tbl;
1898 CLOSE find_delivery;
1899 --}
1900 END IF;
1901 --
1902 IF l_debug_on THEN
1903 WSH_DEBUG_SV.log(l_module_name,'Rows fetched from Cursor FIND_DELIVERY', l_delivery_rec_tbl.count);
1904 END IF;
1905
1906 IF l_delivery_rec_tbl.COUNT > 0 THEN
1907 --{
1908 FOR k IN l_delivery_rec_tbl.FIRST..l_delivery_rec_tbl.LAST
1909 LOOP
1910 --{
1911 --
1912 IF l_debug_on THEN
1913 wsh_debug_sv.logmsg(l_module_name, '**********************************', WSH_DEBUG_SV.C_STMT_LEVEL);
1914 wsh_debug_sv.log(l_module_name, 'Processing delivery', l_delivery_rec_tbl(k).delivery_id);
1915 wsh_debug_sv.logmsg(l_module_name, '**********************************', WSH_DEBUG_SV.C_STMT_LEVEL);
1916 END IF;
1917 --
1918 l_delivery_rec := l_delivery_rec_tbl(k);
1919 l_dup :=NULL;
1920 l_location_id :=NULL;
1921 l_dlvy_status_code :=NULL;
1922 l_routing_response_id :=NULL;
1923 OPEN check_duplicate_vendor( p_del_id => l_delivery_rec.delivery_id );
1924 FETCH check_duplicate_vendor INTO l_dup,
1925 l_location_id,
1926 l_dlvy_status_code,
1927 l_routing_response_id,
1928 l_dlvy_name,
1929 l_ult_dropoff_loc_id;
1930 CLOSE check_duplicate_vendor;
1931 IF l_debug_on THEN
1932 --{
1933 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DUP =' || l_dup, WSH_DEBUG_SV.C_STMT_LEVEL);
1934 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_LOCATION_ID =' || l_location_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1935 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);
1936 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);
1937 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_NAME =' || l_dlvy_name, WSH_DEBUG_SV.C_STMT_LEVEL);
1938 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);
1939 --}
1940 END IF;
1941
1942
1943 IF (l_dup = 'Y') THEN
1944 --{
1945 l_temp := NULL;
1946 OPEN check_temp( p_delivery_id => l_delivery_rec.delivery_id );
1947 FETCH check_temp INTO l_temp;
1948 CLOSE check_temp;
1949 IF l_debug_on THEN
1950 --{
1951 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_TEMP : L_TEMP =' || l_temp, WSH_DEBUG_SV.C_STMT_LEVEL);
1952 --}
1953 END IF;
1954
1955 IF ( l_temp IS NULL ) THEN
1956 --{
1957 -- Call Split_Delivery to split the delivery line
1958
1959 OPEN check_detail ( l_delivery_rec.delivery_id );
1960 FETCH check_detail BULK COLLECT INTO l_delivery_detail_tbl;
1961 CLOSE check_detail;
1962 IF l_debug_on THEN
1963 --{
1964 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);
1965 --}
1966 END IF;
1967
1968 IF l_debug_on THEN
1969 --{
1970 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.SPLIT_INBOUND_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
1971 --}
1972 END IF;
1973
1974 l_new_delivery_id := NULL;
1975 wsh_inbound_util_pkg.split_inbound_delivery(
1976 p_delivery_detail_id_tbl => l_delivery_detail_tbl,
1977 p_delivery_id => l_delivery_rec.delivery_id,
1978 x_delivery_id => l_new_delivery_id,
1979 x_return_status => l_return_status,
1980 p_caller => 'WSH_VENDOR_MERGE'
1981 );
1982 IF l_debug_on THEN
1983 --{
1984 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);
1985 WSH_DEBUG_SV.log(l_module_name, 'New Delivery ID', l_new_delivery_id);
1986 --}
1987 END IF;
1988
1989 wsh_util_core.api_post_call(
1990 p_return_status => l_return_status,
1991 x_num_warnings => l_num_warnings,
1992 x_num_errors => l_num_errors
1993 );
1994
1995 l_chk_delivery_id := l_new_delivery_id;
1996
1997 --Update WSH_NEW_DELIVERIES with the merge to vendor
1998 IF l_debug_on THEN
1999 --{
2000 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2001 --}
2002 END IF;
2003
2004 Update_new_delivery(
2005 p_from_id => p_from_id,
2006 p_to_id => p_to_id,
2007 p_to_party_id => p_to_party_id,
2008 p_from_party_id => p_from_party_id,
2009 p_delivery_id => l_new_delivery_id,
2010 p_from_site_id => p_from_site_id,
2011 p_old_delivery_id => l_delivery_rec.delivery_id,
2012 p_temp_update_flag => 'Y',
2013 p_location_id => l_location_id,
2014 x_return_status => l_return_status
2015 );
2016 IF l_debug_on THEN
2017 --{
2018 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);
2019 --}
2020 END IF;
2021
2022 wsh_util_core.api_post_call(
2023 p_return_status => l_return_status,
2024 x_num_warnings => l_num_warnings,
2025 x_num_errors => l_num_errors
2026 );
2027
2028 l_delivery_cache_tbl(l_new_delivery_id):= l_new_delivery_id;
2029 l_delivery_cache_tbl(l_delivery_rec.delivery_id) := l_delivery_rec.delivery_id;
2030
2031 IF l_dlvy_status_code ='OP' AND l_routing_response_id IS NOT NULL THEN
2032 --{
2033 l_dlvy_rr_cache_tbl(l_new_delivery_id) := l_new_delivery_id;
2034 --}
2035 END IF;
2036 --}
2037 ELSE --IF ( l_temp is NULL )
2038 --{
2039 OPEN check_detail( l_delivery_rec.delivery_id );
2040 FETCH check_detail BULK COLLECT INTO l_delivery_detail_tbl;
2041 CLOSE check_detail;
2042 IF l_debug_on THEN
2043 --{
2044 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);
2045 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.SPLIT_INBOUND_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2046 --}
2047 END IF;
2048
2049 Wsh_inbound_util_pkg.split_inbound_delivery(
2050 p_delivery_detail_id_tbl => l_delivery_detail_tbl,
2051 p_delivery_id => l_delivery_rec.delivery_id,
2052 x_delivery_id => l_temp,
2053 x_return_status => l_return_status,
2054 p_caller => 'WSH_VENDOR_MERGE'
2055 );
2056 IF l_debug_on THEN
2057 --{
2058 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);
2059 --}
2060 END IF;
2061
2062 wsh_util_core.api_post_call(
2063 p_return_status => l_return_status,
2064 x_num_warnings => l_num_warnings,
2065 x_num_errors => l_num_errors
2066 );
2067
2068 l_chk_delivery_id := l_temp;
2069 l_delivery_cache_tbl(l_temp) := l_temp;
2070 l_delivery_cache_tbl(l_delivery_rec.delivery_id) := l_delivery_rec.delivery_id;
2071
2072 IF l_dlvy_status_code ='OP' AND l_routing_response_id IS NOT NULL THEN
2073 --{
2074 l_dlvy_rr_cache_tbl(l_temp) := l_temp;
2075 --}
2076 END IF;
2077
2078 --}
2079 END IF;
2080 --}
2081 ELSE --l_dup<>'Y'
2082 --{
2083 -- Need to select all delivery level attributes again
2084 --
2085 SELECT initial_pickup_location_id, routing_response_id,
2086 name, status_code
2087 INTO l_location_id, l_routing_response_id, l_dlvy_name, l_dlvy_status_code
2088 FROM wsh_new_deliveries
2089 WHERE delivery_id = l_delivery_rec.delivery_id;
2090 --
2091 --Update WSH_NEW_DELIVERIES with the merge to vendor
2092 IF l_debug_on THEN
2093 --{
2094 --wsh_debug_sv.log(l_module_name, 'l_dup', l_dup);
2095 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2096 --}
2097 END IF;
2098
2099 Update_new_delivery(
2100 p_from_id => p_from_id,
2101 p_to_id => p_to_id,
2102 p_to_party_id => p_to_party_id,
2103 p_from_party_id => p_from_party_id,
2104 p_delivery_id => l_delivery_rec.delivery_id,
2105 p_from_site_id => p_from_site_id,
2106 p_old_delivery_id=> l_delivery_rec.delivery_id,
2107 p_temp_update_flag=> 'N',
2108 p_location_id =>l_location_id,
2109 x_return_status => l_return_status
2110 );
2111 IF l_debug_on THEN
2112 --{
2113 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);
2114 --}
2115 END IF;
2116
2117 wsh_util_core.api_post_call(
2118 p_return_status => l_return_status,
2119 x_num_warnings => l_num_warnings,
2120 x_num_errors => l_num_errors
2121 );
2122 --
2123 l_chk_delivery_id := l_delivery_rec.delivery_id;
2124 --
2125 IF l_routing_response_id IS NOT NULL THEN
2126 --{
2127 UPDATE wsh_inbound_txn_history
2128 SET supplier_id = p_to_id,
2129 last_update_date = sysdate,
2130 last_updated_by = fnd_global.user_id,
2131 last_update_login = fnd_global.login_id
2132 WHERE transaction_type = 'ROUTING_RESPONSE'
2133 AND shipment_header_id = l_delivery_rec.delivery_id
2134 AND supplier_id = p_from_id;
2135 IF l_debug_on THEN
2136 --{
2137 WSH_DEBUG_SV.log(l_module_name, 'Updated ROUTING_RESP record for', l_delivery_rec.delivery_id);
2138 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);
2139 --}
2140 END IF;
2141
2142 --}
2143 END IF;
2144
2145 --}
2146 END IF; -- if l_dup ='Y'
2147 --
2148 IF l_debug_on THEN
2149 wsh_debug_sv.log(l_module_name, 'Updating vendor info. on containers assigned to delivery', l_chk_delivery_id);
2150 END IF;
2151 --
2152 UPDATE wsh_delivery_details
2153 SET vendor_id = p_to_id,
2154 party_id = p_to_party_id,
2155 last_update_date = sysdate,
2156 last_updated_by = fnd_global.user_id,
2157 last_update_login = fnd_global.login_id
2158 WHERE container_flag = 'Y'
2159 AND vendor_id = p_from_id
2160 AND delivery_detail_id
2161 IN (
2162 SELECT delivery_detail_id
2163 FROM wsh_delivery_assignments
2164 WHERE nvl(type,'S') in ('S','O')
2165 AND delivery_id = l_chk_delivery_id
2166 );
2167 --
2168 IF l_debug_on THEN
2169 wsh_debug_sv.log(l_module_name, 'No. of container records updated', SQL%ROWCOUNT);
2170 END IF;
2171 --
2172 OPEN dlvy_rr_csr(l_chk_delivery_id);
2173 FETCH dlvy_rr_csr BULK COLLECT INTO l_dlvy_rr_rec_tbl;
2174 CLOSE dlvy_rr_csr;
2175 --
2176 IF l_debug_on THEN
2177 --{
2178 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);
2179 --}
2180 END IF;
2181
2182 IF l_dlvy_rr_rec_tbl.COUNT > 0 THEN
2183 --{
2184 FOR l in l_dlvy_rr_rec_tbl.FIRST..l_dlvy_rr_rec_tbl.LAST
2185 LOOP
2186 --{
2187 l_dlvy_rr_rec := l_dlvy_rr_rec_tbl(l);
2188 IF l_prev_old_rr_id = l_dlvy_rr_rec.routing_req_id THEN
2189 --{
2190 l_new_rr_id := l_prev_new_rr_id;
2191 ELSE
2192 l_new_rr_id := NULL;
2193 l_new_rr_number := NULL;
2194 l1_transaction_id := NULL;
2195 l1_revision_number := NULL;
2196 l1_parent_shipment_header_id := NULL;
2197 --
2198 OPEN chk_rreq_csr( p_supplier_id => l_dlvy_rr_rec.vendor_id,
2199 p_rr_number => l_dlvy_rr_Rec.rr_number );
2200 FETCH chk_rreq_csr INTO l1_transaction_id,
2201 l1_revision_number,
2202 l1_parent_shipment_header_id;
2203 CLOSE chk_rreq_csr;
2204 IF l_debug_on THEN
2205 --{
2206 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_TRANSACTION_ID =' || l1_transaction_id, WSH_DEBUG_SV.C_STMT_LEVEL);
2207 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_REVISION_NUMBER =' || l1_revision_number, WSH_DEBUG_SV.C_STMT_LEVEL);
2208 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);
2209 --}
2210 END IF;
2211
2212 IF l1_revision_number = l_dlvy_rr_rec.revision_number
2213 AND l1_parent_shipment_header_id = l_dlvy_rr_Rec.routing_req_id Then
2214 --{
2215 l_new_rr_id := l1_transaction_id;
2216 l_new_rr_number := l_dlvy_rr_Rec.rr_number;
2217 ELSIF l1_revision_number IS NOT NULL THEN
2218 --{
2219 FOR I IN 1..999
2220 LOOP
2221 --{
2222 l_tmp_rr_number := l_dlvy_rr_rec.rr_number || '-VM'|| lpad(I,3,'0');
2223
2224 l1_transaction_id := NULL;
2225 l1_revision_number:= NULL;
2226 l1_parent_shipment_header_id := NULL;
2227 --
2228 OPEN chk_rreq_csr( p_supplier_id => l_dlvy_rr_rec.vendor_id,
2229 p_rr_number => l_tmp_rr_number );
2230 FETCH chk_rreq_csr INTO l1_transaction_id,
2231 l1_revision_number,
2232 l1_parent_shipment_header_id;
2233 CLOSE chk_rreq_csr;
2234
2235 IF l1_revision_number = l_dlvy_rr_rec.revision_number
2236 AND l1_parent_shipment_header_id = l_dlvy_rr_Rec.routing_req_id THEN
2237 --{
2238 l_new_rr_id := l1_transaction_id;
2239 l_new_rr_number := l_tmp_rr_number;
2240 EXIT;
2241 --
2242 ELSIF l1_revision_number IS NULL
2243 AND l1_parent_shipment_header_id IS NULL THEN
2244 --
2245 l_new_rr_number := l_tmp_rr_number;
2246 EXIT;
2247 --
2248 --}
2249 END IF;
2250 --}
2251 END LOOP;--FOR I IN 1..999
2252
2253 IF l_new_rr_id IS NULL AND l_new_rr_number IS NULL THEN
2254 --{
2255 --
2256 --
2257 fnd_message.set_name ( 'WSH', 'WSH_IB_RR_NUMBER_CONFLICT' );
2258 fnd_message.set_token( 'SUPPLIER_NAME', l_supplier_name);
2259 l_msg := FND_MESSAGE.GET;
2260 wsh_util_core.printMsg( l_msg );
2261
2262 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2263 IF NOT(wsh_util_core.g_call_fte_load_tender_api) THEN
2264 --{
2265 wsh_util_core.Reset_stops_for_load_tender(
2266 p_reset_flags=>true,
2267 x_return_status=>l_return_status
2268 );
2269 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2270 --{
2271 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2272 --}
2273 END IF;
2274 --}
2275 END IF;
2276 --
2277 IF l_debug_on THEN
2278 WSH_DEBUG_SV.pop(l_module_name);
2279 END IF;
2280 --
2281 RETURN;
2282 --}
2283 END IF;
2284 --}
2285 ELSE --ELSIF l1_revision_number IS NOT NULL THEN
2286
2287 l_new_rr_number := l_dlvy_rr_Rec.rr_number;
2288
2289 --}
2290 END IF; --IF l1_revision_number = l_dlvy_rr_rec.revision_number
2291 -- AND l1_parent_shipment_header_id = l_dlvy_rr_Rec.routing_req_id
2292
2293
2294 IF l_new_rr_id IS NULL THEN
2295 --{
2296 IF l_debug_on THEN
2297 --{
2298 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_TXN_HISTORY_PKG.GET_TXN_HISTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
2299 --}
2300 END IF;
2301 WSH_INBOUND_TXN_HISTORY_PKG.get_txn_history(
2302 p_transaction_id => l_dlvy_rr_rec.routing_req_id,
2303 x_txn_history_rec => l_txn_history_rec,
2304 x_return_status => l_return_status
2305 );
2306 IF l_debug_on THEN
2307 --{
2308 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);
2309 --}
2310 END IF;
2311 wsh_util_core.api_post_call(
2312 p_return_status => l_return_status,
2313 x_num_warnings => l_num_warnings,
2314 x_num_errors => l_num_errors
2315 );
2316 --
2317 l_txn_history_Rec.receipt_number := l_new_rr_number;
2318 l_txn_history_Rec.parent_shipment_header_id := l_dlvy_rr_rec.routing_req_id;
2319 l_txn_history_rec.supplier_id := p_to_id;
2320 --
2321 IF l_debug_on THEN
2322 --{
2323 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_TXN_HISTORY_PKG.CREATE_TXN_HISTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
2324 --}
2325 END IF;
2326 WSH_INBOUND_TXN_HISTORY_PKG.create_txn_history(
2327 p_txn_history_rec => l_txn_history_rec,
2328 x_txn_id => l_new_rr_id,
2329 x_return_status => l_return_status
2330 );
2331 IF l_debug_on THEN
2332 --
2333 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_INBOUND_TXN_HISTORY_PKG.CREATE_TXN_HISTORY is ' , l_return_status);
2334 WSH_DEBUG_SV.log(l_module_name, 'New Txn ID', l_new_rr_id);
2335
2336 --
2337 END IF;
2338 wsh_util_core.api_post_call(
2339 p_return_status => l_return_status,
2340 x_num_warnings => l_num_warnings,
2341 x_num_errors => l_num_errors
2342 );
2343
2344 --
2345 --}
2346 END IF;--IF l_new_rr_id IS NULL
2347 --
2348 -- Log an exception against delivery, if the routing req number has changed
2349 --
2350 IF (l_new_rr_number <> l_dlvy_rr_rec.rr_number) THEN
2351 --{
2352 fnd_message.set_name ( 'WSH', 'WSH_IB_RR_NUMBER_CHG' );
2353 fnd_message.set_token('OLD_RR_NUMBER',to_char(l_dlvy_rr_rec.rr_number));
2354 fnd_message.set_token( 'NEW_RR_NUMBER', to_char( l_new_rr_number ) );
2355 fnd_message.set_token( 'DELIVERY_NAME', l_dlvy_rr_rec.name );
2356 l_msg := FND_MESSAGE.GET;
2357 --
2358 WSH_UTIL_CORE.printMsg(l_msg);
2359 --
2360 l_exception_id := NULL;
2361 --
2362 wsh_xc_util.log_exception (
2363 p_api_version => 1.0,
2364 p_exception_name => 'WSH_IB_RR_NUMBER_CHG',
2365 p_logging_entity => 'SHIPPER',
2366 p_logging_entity_id => FND_GLOBAL.USER_ID,
2367 x_return_status => l_return_status,
2368 x_exception_id => l_exception_id,
2369 x_msg_data => l_xc_msg_data,
2370 x_msg_count => l_xc_msg_count,
2371 p_message => substrb ( l_msg, 1, 2000 ),
2372 p_delivery_id => l_chk_delivery_id,
2373 p_exception_location_id =>l_dlvy_rr_rec.ult_dropoff_loc_id,
2374 p_logged_at_location_id =>l_dlvy_rr_rec.ult_dropoff_loc_id
2375 );
2376 --
2377 IF l_debug_on THEN
2378 --
2379 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);
2380 --
2381 END IF;
2382 --
2383 wsh_util_core.api_post_call(
2384 p_return_status => l_return_status,
2385 x_num_warnings => l_num_warnings,
2386 x_num_errors => l_num_errors,
2387 p_msg_data => l_xc_msg_data
2388 );
2389 --}
2390 END IF;
2391 --}
2392 END IF;--IF l_prev_old_rr_id = l_dlvy_rr_rec.routing_req_id
2393 --
2394 IF l_debug_on THEN
2395 wsh_debug_sv.log(l_module_name, 'WDD ID to update', l_dlvy_rr_rec.delivery_detail_id);
2396 wsh_debug_sv.log(l_module_name, 'Routing Req ID to update with', l_new_rr_id);
2397 END IF;
2398 --
2399 UPDATE wsh_delivery_details
2400 SET routing_req_Id = l_new_rr_id,
2401 last_update_date = sysdate,
2402 last_updated_by = fnd_global.user_id,
2403 last_update_login = fnd_global.login_id
2404 WHERE delivery_detail_id = l_dlvy_rr_rec.delivery_detail_id;
2405 --
2406 IF l_debug_on THEN
2407 --{
2408 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
2409 --}
2410 END IF;
2411
2412 l_prev_old_rr_id := l_dlvy_rr_rec.routing_req_id;
2413 l_prev_new_rr_id := l_new_rr_id;
2414
2415 --}
2416 END LOOP;--FOR l_dlvy_rr_rec in l_dlvy_rr_rec_tbl.FIRST..l_dlvy_rr_rec_tbl.LAST
2417 --}
2418 END IF;
2419 --}
2420 END LOOP;--FOR l_delivery_rec IN l_delivery_rec_tbl.FIRST..l_delivery_rec_tbl.LAST LOOP
2421 --}
2422 END IF; --IF l_delivery_rec_tbl.COUNT > 0
2423 --
2424 IF p_From_id <> p_to_id THEN
2425 --{
2426 -- Update WSH_INBOUND_TXN_HISTORY with the merge to vendor
2427 --
2428 UPDATE wsh_inbound_txn_history a
2429 SET supplier_id = p_to_id,
2430 last_update_date = sysdate,
2431 last_updated_by = fnd_global.user_id,
2432 last_update_login = fnd_global.login_id
2433 WHERE supplier_id = p_from_id
2434 AND transaction_type not in ('ROUTING_REQUEST','ROUTING_RESPONSE')
2435 AND exists (SELECT shipment_header_id
2436 FROM rcv_shipment_headers b
2437 WHERE b.shipment_header_id = a.shipment_header_id
2438 AND b.vendor_id = p_to_id
2439 );
2440 --
2441 l_numRowsUpdated := SQL%ROWCOUNT;
2442 --
2443 IF l_debug_on THEN
2444 --{
2445 WSH_DEBUG_SV.log(l_module_name, 'Updated ASN/RECEIPT records with vendor', p_to_id);
2446 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);
2447 --}
2448 END IF;
2449 --}
2450 END IF;
2451 --
2452 -- Convert l_dlvy_rr_cache_tbl to a new contiguous table (l_dlvy_rr_tbl)
2453 -- call WSH_DELIVERIES_GRP.delivery_action passing l_dlvy_rr_tbl
2454 -- AND action_code = 'GENERATE-ROUTING-RESPONSE'.
2455 -- l_dlvy_rr_tbl.delete;
2456 l_cache_index := l_dlvy_rr_cache_tbl.FIRST ;
2457 WHILE l_cache_index IS NOT NULL
2458 LOOP
2459 --{
2460 l_respIndex := l_dlvy_rr_tbl.COUNT+1;
2461 l_dlvy_rr_tbl(l_respIndex).delivery_id := l_dlvy_rr_cache_tbl( l_cache_index );
2462 --
2463 SELECT organization_id
2464 INTO l_dlvy_rr_tbl(l_respIndex).organization_id
2465 FROM wsh_new_deliveries
2466 WHERE delivery_id = l_dlvy_rr_tbl(l_respIndex).delivery_id;
2467 --
2468 l_cache_index := l_dlvy_rr_cache_tbl.Next( l_cache_index );
2469 --}
2470 END LOOP;
2471 --
2472 l_action_prms.action_code := 'GENERATE-ROUTING-RESPONSE' ;
2473 l_action_prms.caller := 'WSH_PUB';
2474 --
2475 IF l_debug_on THEN
2476 --{
2477 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERIES_GRP.DELIVERY_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
2478 WSH_DEBUG_SV.log(l_module_name, 'l_dlvy_rr_tbl.COUNT', l_dlvy_rr_tbl.COUNT);
2479 --}
2480 END IF;
2481 --
2482 IF l_dlvy_rr_tbl.COUNT > 0 THEN
2483 --{
2484 WSH_DELIVERIES_GRP.delivery_action(
2485 p_api_version_number => 1.0,
2486 p_init_msg_list => FND_API.G_FALSE,
2487 p_commit => FND_API.G_FALSE,
2488 p_action_prms => l_action_prms,
2489 p_rec_attr_tab => l_dlvy_rr_tbl,
2490 x_delivery_out_rec => l_delivery_out_rec,
2491 x_defaults_rec => l_defaults_rec,
2492 x_return_status => l_return_status,
2493 x_msg_count => l_msg_count,
2494 x_msg_data => l_msg
2495 );
2496 IF l_debug_on THEN
2497 --{
2498 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);
2499 --}
2500 END IF;
2501
2502 wsh_util_core.api_post_call(
2503 p_return_status => l_return_status,
2504 x_num_warnings => l_num_warnings,
2505 x_num_errors => l_num_errors,
2506 p_msg_data => l_msg
2507 );
2508 --}
2509 END IF;
2510
2511 --
2512 --Convert l_dlvy_cache_tbl into a contiguous table l_dlvy_tbl
2513 -- AND call WSH_WV_UTILS.delivery_weight_volume
2514 --l_dlvy_tbl.delete;
2515 l_cache_index := l_delivery_cache_tbl.FIRST ;
2516 WHILE l_cache_index IS NOT NULL
2517 LOOP
2518 --{
2519 l_dlvy_tbl( l_dlvy_tbl.count + 1 ) := l_delivery_cache_tbl( l_cache_index );
2520 l_cache_index := l_delivery_cache_tbl.Next( l_cache_index );
2521 --}
2522 END LOOP;
2523
2524 IF l_debug_on THEN
2525 --{
2526 WSH_DEBUG_SV.logmsg( l_module_name,'Calling program unit WSH_WV_UTILS.DELIVERY_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL );
2527 WSH_DEBUG_SV.log(l_module_name, 'l_dlvy_tbl.COUNT', l_dlvy_tbl.COUNT);
2528 FOR i IN 1..l_dlvy_tbl.COUNT LOOP
2529 wsh_debug_sv.log(l_module_name, 'l_dlvy_tbl(i)', l_dlvy_tbl(i));
2530 END LOOP;
2531 --
2532 --}
2533 END IF;
2534 --
2535 IF l_dlvy_tbl.COUNT > 0 THEN
2536 --{
2537 WSH_WV_UTILS.delivery_weight_volume(
2538 p_del_rows => l_dlvy_tbl,
2539 p_update_flag => 'Y',
2540 p_calc_wv_if_frozen => 'N',
2541 x_return_status => l_return_status
2542 );
2543 IF l_debug_on THEN
2544 --{
2545 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);
2546 --}
2547 END IF;
2548
2549 wsh_util_core.api_post_call(
2550 p_return_status => l_return_status,
2551 x_num_warnings => l_num_warnings,
2552 x_num_errors => l_num_errors
2553 );
2554 --}
2555 END IF;
2556
2557 /*
2558 FORALL I IN l_dlvy_tbl.first..l_dlvy_tbl.last
2559 UPDATE wsh_delivery_legs
2560 SET reprice_required = 'Y',
2561 last_update_date = sysdate,
2562 last_updated_by = fnd_global.user_id,
2563 last_update_login = fnd_global.login_id
2564 WHERE delivery_id = l_dlvy_tbl(i);
2565
2566 ELSIF (l_option = 'B' or l_option = 'I') THEN
2567
2568 --Update FTE_INVOICE_HEADERS with the merge to vendor/vendor site
2569 UPDATE fte_invoice_headers a
2570 SET supplier_id = p_to_id,
2571 supplier_site_id = p_to_site_id,
2572 last_update_date = sysdate,
2573 last_updated_by = fnd_global.user_id,
2574 last_update_login = fnd_global.login_id
2575 WHERE supplier_id = p_from_id
2576 AND supplier_site_id = p_from_site_id
2577 AND exists (SELECT 1
2578 FROM ap_invoices_all
2579 WHERE vendor_id = p_to_id
2580 AND vendor_site_id = p_to_site_id
2581 AND invoice_num = a.bill_number
2582 ); */
2583
2584 --}
2585 -- END IF; --IF (l_option= 'B' OR l_option = 'P')
2586
2587 --
2588 IF NOT(wsh_util_core.g_call_fte_load_tender_api) THEN
2589 --{
2590 wsh_util_core.Process_stops_for_load_tender(
2591 p_reset_flags=>true,
2592 x_return_status=>l_return_status);
2593 wsh_util_core.api_post_call(
2594 p_return_status => l_return_status,
2595 x_num_warnings => l_num_warnings,
2596 x_num_errors => l_num_errors
2597 );
2598 --}
2599 END IF;
2600 --}
2601 END IF;
2602 --
2603 IF l_num_errors > 0 THEN
2604 --{
2605 ROLLBACK TO WSH_Vendor_Merge;
2606 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2607 ELSIF l_num_warnings > 0 THEN
2608 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2609 --}
2610 END IF;
2611 --
2612 IF l_debug_on THEN
2613 WSH_DEBUG_SV.log(l_module_name, 'Final return status', x_return_status);
2614 WSH_DEBUG_SV.pop(l_module_name);
2615 END IF;
2616
2617 EXCEPTION
2618 --
2619 WHEN FND_API.G_EXC_ERROR THEN
2620 --
2621 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2622 ROLLBACK TO WSH_Vendor_Merge;
2623 --
2624 IF l_debug_on THEN
2625 --
2626 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
2627 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2628 --
2629 END IF;
2630 --
2631 IF NOT(wsh_util_core.g_call_fte_load_tender_api) THEN
2632 --
2633 wsh_util_core.Reset_stops_for_load_tender(
2634 p_reset_flags=>true,
2635 x_return_status=>l_return_status);
2636 --
2637 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2638 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2639 END IF;
2640 --
2641 END IF;
2642 --
2643 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2644 --
2645 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2646 ROLLBACK TO WSH_Vendor_Merge;
2647 --
2648 IF l_debug_on THEN
2649 --
2650 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2651 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2652 --
2653 END IF;
2654 --
2655 IF NOT(wsh_util_core.g_call_fte_load_tender_api) THEN
2656 --
2657 wsh_util_core.Reset_stops_for_load_tender(
2658 p_reset_flags=>true,
2659 x_return_status=>l_return_status);
2660 --
2661 END IF;
2662 --
2663 WHEN OTHERS THEN
2664 --
2665 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2666 ROLLBACK TO WSH_Vendor_Merge;
2667 wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Vendor_Merge');
2668 --
2669 IF l_debug_on THEN
2670 --
2671 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2672 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2673 --
2674 END IF;
2675 --
2676 IF NOT(wsh_util_core.g_call_fte_load_tender_api) THEN
2677 --
2678 wsh_util_core.Reset_stops_for_load_tender(
2679 p_reset_flags=>true,
2680 x_return_status=>l_return_status);
2681 --
2682 END IF;
2683 --
2684 --}
2685 END Vendor_Merge;
2686
2687
2688
2689
2690 --========================================================================
2691 -- PROCEDURE :Vendor_Party_Merge
2692 -- PARAMETERS:
2693 -- P_from_vendor_id Merge from vendor ID
2694 -- P_to_vendor_id Merge to vendor ID
2695 -- P_from_party_id Merge from party ID
2696 -- P_to_party_id Merge to party ID
2697 -- P_from_vendor_site_id Merge from vendor site ID
2698 -- P_to_vendor_site_id Merge to vendor site ID
2699 -- P_from_party_site_id Merge from party site ID
2700 -- P_to_party_site_id Merge to party site ID
2701 -- X_return_status Return status
2702 --
2703 -- COMMENTS
2704 -- This is the API that is called by APXINUPD.rdf. This in turn
2705 -- will call the core Vendor_Merge() procedure to
2706 -- perform all the necessary updates to WSH data.
2707 --
2708 -- HISTORY
2709 -- rlanka 7/27/2005 Created
2710 -- rlanka 8/09/2005 Added new parameter p_calling_mode
2711 --
2712 --========================================================================
2713
2714 PROCEDURE Vendor_Party_Merge
2715 (
2716 p_from_vendor_id IN NUMBER,
2717 p_to_vendor_id IN NUMBER,
2718 p_from_party_id IN NUMBER,
2719 p_to_party_id IN NUMBER,
2720 p_from_vendor_site_id IN NUMBER,
2721 p_to_vendor_site_id IN NUMBER,
2722 p_from_party_site_id IN NUMBER,
2723 p_to_partysite_id IN NUMBER,
2724 p_calling_mode IN VARCHAR2,
2725 x_return_status OUT NOCOPY VARCHAR2,
2726 x_msg_count OUT NOCOPY NUMBER,
2727 x_msg_data OUT NOCOPY VARCHAR2
2728 )
2729 IS
2730 --
2731 l_return_status VARCHAR2(1);
2732 l_debug_on BOOLEAN;
2733 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VENDOR_PARTY_MERGE';
2734 --
2735 l_fromPartyId NUMBER;
2736 l_toPartyId NUMBER;
2737 --
2738 -- Bug 4658824 : Use po_vendors, so we are isolated from any changes
2739 -- that AP makes to their data model
2740 CURSOR c_getParty(p_vendorId IN NUMBER) IS
2741 SELECT party_id
2742 FROM po_vendors
2743 WHERE vendor_id = p_vendorId;
2744 --
2745 BEGIN
2746 --{
2747 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2748 --
2749 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2750 IF l_debug_on IS NULL THEN
2751 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2752 END IF;
2753 --
2754 IF l_debug_on THEN
2755 --
2756 WSH_DEBUG_SV.push(l_module_name);
2757 WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_ID', p_from_vendor_id);
2758 WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_ID', p_to_vendor_id);
2759 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id);
2760 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id);
2761 WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_SITE_ID', p_from_vendor_site_id);
2762 WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_SITE_ID', p_to_vendor_site_id);
2763 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_SITE_ID', p_from_party_site_id);
2764 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTYSITE_ID', p_to_partysite_id);
2765 WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
2766 --
2767 END IF;
2768 --
2769 -- Clear up global table of location IDs
2770 --
2771 g_LocChangeTab.DELETE;
2772 --
2773 -- Bug 4658824 : Now AP passes us the party ID, so we derive
2774 -- it only if the input parameter is NULL.
2775 --
2776 IF p_to_party_id IS NULL THEN
2777 --
2778 OPEN c_getParty(p_to_vendor_id);
2779 FETCH c_getParty INTO l_toPartyId;
2780 IF (c_getParty%NOTFOUND) THEN
2781 Null;
2782 END IF;
2783 CLOSE c_getParty;
2784 --
2785 END IF;
2786 --
2787 IF p_from_party_id IS NULL THEN
2788 --
2789 OPEN c_getParty(p_from_vendor_id);
2790 FETCH c_getParty INTO l_fromPartyId;
2791 IF (c_getParty%NOTFOUND) THEN
2792 Null;
2793 END IF;
2794 CLOSE c_getParty;
2795 --
2796 END IF;
2797 --
2798 IF l_debug_on THEN
2799 WSH_DEBUG_SV.log(l_module_name, 'l_fromPartyId', l_fromPartyId);
2800 WSH_DEBUG_SV.log(l_module_name, 'l_toPartyId', l_toPartyId);
2801 END IF;
2802 --
2803 -- Now call the core Vendor Merge routine to update WSH data
2804 --
2805 WSH_VENDOR_PARTY_MERGE_PKG.Vendor_Merge
2806 (
2807 p_from_id => p_from_vendor_id,
2808 p_to_id => P_to_vendor_id,
2809 p_from_party_id => NVL(p_from_party_id, l_fromPartyId),
2810 p_to_party_id => NVL(p_to_party_id, l_toPartyId),
2811 p_from_site_id => p_from_vendor_site_id,
2812 p_to_site_id => p_to_vendor_site_id,
2813 p_calling_mode => p_calling_mode,
2814 x_return_status => l_return_status
2815 );
2816 --
2817 IF l_debug_on THEN
2818 WSH_DEBUG_SV.logmsg(l_module_name, 'After calling core vendor_merge API', WSH_DEBUG_SV.C_STMT_LEVEL);
2819 WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
2820 END IF;
2821 --
2822 -- For AP, we interpret 'W' as 'S' status.
2823 --
2824 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2825 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2826 ELSE
2827 x_return_status := l_return_status;
2828 END IF;
2829 --
2830 FND_MSG_PUB.Count_And_Get
2831 (
2832 p_count => x_msg_count,
2833 p_data => x_msg_data,
2834 p_encoded => FND_API.G_FALSE
2835 );
2836 --
2837 IF l_debug_on THEN
2838 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
2839 WSH_DEBUG_SV.pop(l_module_name);
2840 END IF;
2841 --}
2842 EXCEPTION
2843 --
2844 WHEN FND_API.G_EXC_ERROR THEN
2845 --
2846 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2847 --
2848 IF l_debug_on THEN
2849 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
2850 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2851 END IF;
2852 --
2853 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2854 --
2855 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2856 --
2857 IF l_debug_on THEN
2858 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2859 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2860 END IF;
2861 --
2862 WHEN OTHERS THEN
2863 --
2864 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2865 WSH_UTIL_CORE.ADD_MESSAGE(l_return_status, l_module_name);
2866 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_VENDOR_PARTY_MERGE_PKG.VENDOR_PARTY_MERGE',l_module_name);
2867 --
2868 IF l_debug_on THEN
2869 WSH_DEBUG_SV.log(l_module_name, 'Unexpected error', substrb(sqlerrm, 1, 200));
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 END Vendor_Party_Merge;
2875
2876
2877 END WSH_VENDOR_PARTY_MERGE_PKG;