[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.24 2011/01/21 09:08:07 skanduku ship $ */
3 --
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_VENDOR_PARTY_MERGE_PKG';
5 TYPE g_number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6 TYPE g_char_hash_string IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
7 TYPE g_char_tbl_type IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
8 G_DELIVERY_ID g_number_tbl_type;
9 G_LIMIT NUMBER := 5000;--Added for Vendor Merge of Shipping RTV Transactions
10 TYPE PARAM_INFO_TAB_TYPE IS TABLE OF WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ INDEX BY BINARY_INTEGER;
11 G_PARAM_INFO_TAB PARAM_INFO_TAB_TYPE;
12 --
13 TYPE g_LocChangeRec IS RECORD
14 (
15 location_id NUMBER,
16 old_loc_code VARCHAR2(40),
17 new_loc_code VARCHAR2(40));
18 --
19 TYPE locChangeTab IS TABLE OF g_LocChangeRec INDEX BY BINARY_INTEGER;
20 g_LocChangeTab locChangeTab;
21 --
22
23 --
24 --
25 --========================================================================
26 -- PROCEDURE : InactivatePartySites
27 -- PARAMETERS:
28 -- P_party_id Merge from party ID
29 -- P_party_site_id Party Site ID
30 -- p_process_locations Determines whether Process_Locations()
31 -- API should be called or not.
32 -- p_to_id Merge To Party ID
33 -- p_to_vendor_id Merge To Vendor ID
34 -- X_return_status Return status
35 --
36 -- COMMENT :
37 -- This is a private procedure that is used to inactivate
38 -- party sites for a given party ID. For a given party ID,
39 -- it gets a list of party Sites and for each party site ID,
40 -- it looks for any delivery tied to that location. If there
41 -- are no such deliveries, it calls HZ API to set the status
42 -- of that particular party Site to 'I'.
43 --
44 -- If parameter p_process_locations is TRUE, then it also calls
45 -- Process_Locations() to transfer the old SF location from the
46 -- old vendor to the new vendor. Process_Locations() should be
47 -- called only when parameter p_party_site_id IS NOT NULL.
48 --
49 --========================================================================
50 PROCEDURE InactivatePartySites(p_party_id IN NUMBER,
51 p_party_site_id IN NUMBER DEFAULT NULL,
52 p_process_locations IN BOOLEAN DEFAULT FALSE,
53 p_to_id IN NUMBER,
54 p_to_vendor_id IN NUMBER,
55 x_return_status OUT NOCOPY VARCHAR2)
56 IS
57 --
58 CURSOR get_party_site_csr(p_party_id NUMBER, p_site_id NUMBER) IS
59 SELECT location_id,
60 hps.party_site_id,
61 hps.object_version_number
62 FROM hz_party_sites hps,
63 hz_party_site_uses hpsu
64 WHERE hps.party_id = p_party_id
65 AND hps.party_site_id = hpsu.party_site_id
66 AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM'
67 AND hpsu.status = 'A'
68 AND hps.party_site_id = NVL(p_site_id, hps.party_site_id);
69 --
70 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'InactivatePartySites';
71 l_debug_on BOOLEAN;
72 --
73 l_from_party_rec hz_party_site_v2pub.party_site_rec_type;
74 l_msg_data VARCHAR2(32767);
75 l_num_warnings NUMBER :=0;
76 l_num_errors NUMBER :=0;
77 l_object_version_number NUMBER;
78 l_msg VARCHAR2(32767);
79 l_query_count NUMBER :=0;
80 l_msg_count NUMBER ;
81 l_return_status VARCHAR2(1);
82 l_sql_code NUMBER;
83 l_sql_err VARCHAR2(32767);
84 --
85 BEGIN
86 --{
87 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
88 --
89 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
90 IF l_debug_on IS NULL THEN
91 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
92 END IF;
93 --
94 IF l_debug_on THEN
95 WSH_DEBUG_SV.push(l_module_name);
96 WSH_DEBUG_SV.log(l_module_name, 'p_party_id', p_party_id);
97 WSH_DEBUG_SV.log(l_module_name, 'p_party_site_id', p_party_site_id);
98 WSH_DEBUG_SV.log(l_module_name, 'p_process_locations', p_process_locations);
99 WSH_DEBUG_SV.log(l_module_name, 'p_to_id', p_to_id);
100 WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
101 END IF;
102 --
103 FOR get_party_site_rec IN get_party_site_csr(p_party_id => p_party_id,
104 p_site_id => p_party_site_id)
105 LOOP
106 --{
107 IF l_debug_on THEN
108 --
109 WSH_DEBUG_SV.logmsg(l_module_name, '----------------', WSH_DEBUG_SV.C_STMT_LEVEL);
110 WSH_DEBUG_SV.log(l_module_name,'LOCATION_ID' , get_party_site_rec.location_id);
111 WSH_DEBUG_SV.log(l_module_name,'PARTY_SITE_ID', get_party_site_rec.party_site_id);
112 WSH_DEBUG_SV.log(l_module_name,'OBJECT_VERSION_NUMBER', get_party_site_rec.object_version_number);
113 --
114 END IF;
115 --
116 -- Check if we have any deliveries tied to the
117 -- SF location associated with the old party ID
118 --
119 BEGIN
120 --
121 -- R12 Perf Bug 4949639 : Replace WND with WDD
122 -- since all we are checking for is existence of records
123 -- with a particular SF location ID
124 --
125 SELECT 1
126 INTO l_query_count
127 FROM wsh_delivery_details wdd,
128 wsh_locations wl
129 WHERE wdd.ship_from_location_id = wl.wsh_location_id
130 AND wl.source_location_id = get_party_site_rec.location_id
131 AND wdd.party_id = p_party_id
132 AND rownum=1;
133 --
134 EXCEPTION
135 WHEN NO_DATA_FOUND THEN
136 l_query_count := 0;
137 END;
138 --
139 IF l_debug_on THEN
140 WSH_DEBUG_SV.log(l_module_name,'L_QUERY_COUNT', l_query_count);
141 END IF;
142 --
143 IF l_query_count = 0 AND p_party_site_id IS NOT NULL THEN
144 --{
145 -- Make party site inactive.
146 --
147 l_from_party_rec.party_site_id := get_party_site_rec.party_site_id ;
148 l_object_version_number := get_party_site_rec.object_version_number;
149 l_from_party_rec.status := 'I' ;
150 --
151 IF l_debug_on THEN
152 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE_USE',WSH_DEBUG_SV.C_PROC_LEVEL);
153 END IF;
154 --
155 hz_party_site_v2pub.update_party_site
156 (
157 p_party_site_rec => l_from_party_rec,
158 p_object_version_number => l_object_version_number,
159 x_return_status => l_return_status,
160 x_msg_count => l_msg_count,
161 x_msg_data => l_msg
162 );
163 --
164 IF l_debug_on THEN
165 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
166 END IF;
167 --
168 wsh_util_core.api_post_call
169 (
170 p_return_status => l_return_status,
171 x_num_warnings => l_num_warnings,
172 x_num_errors => l_num_errors,
173 p_msg_data => l_msg
174 );
175 --
176 IF p_process_locations THEN
177 --{
178 IF l_debug_on THEN
179 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Process_Location API', WSH_DEBUG_SV.C_PROC_LEVEL);
180 END IF;
181 --
182 WSH_LOCATIONS_PKG.Process_Locations
183 (
184 p_location_type => 'EXTERNAL',
185 p_from_location => get_party_site_rec.location_id,
186 p_to_location => get_party_site_rec.location_id,
187 p_start_date => NULL,
188 p_end_date => NULL,
189 p_caller => 'PO',
190 x_return_status => l_return_status,
191 x_sqlcode => l_sql_code,
192 x_sqlerr => l_sql_err
193 );
194 --
195 IF l_debug_on THEN
196 WSH_DEBUG_SV.logmsg(l_module_name, 'After Process_Location API', WSH_DEBUG_SV.C_PROC_LEVEL);
197 WSH_DEBUG_SV.log(l_module_name, 'l_sql_code', l_sql_code);
198 WSH_DEBUG_SV.log(l_module_name, 'l_sql_err', l_sql_err);
199 WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
200 END IF;
201 --
202 WSH_UTIL_CORE.api_post_call
203 (
204 p_return_status => l_return_status,
205 x_num_warnings => l_num_warnings,
206 x_num_errors => l_num_errors
207 );
208 --}
209 END IF;
210 --}
211 ELSIF l_query_count = 0 THEN
212 --{
213 IF l_debug_on THEN
214 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Create_Site', WSH_DEBUG_SV.C_PROC_LEVEL);
215 END IF;
216 --
217 Create_Site
218 (
219 p_from_id => p_party_id,
220 p_to_id => p_to_id,
221 p_to_vendor_id => p_to_vendor_id,
222 p_delivery_id => NULL,
223 p_delivery_name => NULL,
224 p_location_id => get_party_site_rec.location_id,
225 x_return_Status => l_return_status
226 );
227 --
228 IF l_debug_on THEN
229 WSH_DEBUG_SV.logmsg(l_module_name, 'After calling create_Site', WSH_DEBUG_SV.C_PROC_LEVEL);
230 WSH_DEBUG_SV.log(l_module_name, 'Return Status', l_return_status);
231 END IF;
232 --
233 WSH_UTIL_CORE.api_post_call
234 (
235 p_return_status => l_return_status,
236 x_num_warnings => l_num_warnings,
237 x_num_errors => l_num_errors
238 );
239 --}
240 END IF;
241 --}
242 END LOOP;
243 --
244 IF l_num_errors > 0
245 THEN
246 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
247 ELSIF l_num_warnings > 0
248 THEN
249 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
250 ELSE
251 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
252 END IF;
253 --
254 IF l_debug_on THEN
255 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
256 WSH_DEBUG_SV.pop(l_module_name);
257 END IF;
258 --}
259 EXCEPTION
260 --
261 WHEN FND_API.G_EXC_ERROR THEN
262 --
263 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
264 --
265 IF l_debug_on THEN
266 --
267 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
268 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
269 --
270 END IF;
271 --
272 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
273 --
274 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
275 --
276 IF l_debug_on THEN
277 --
278 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
279 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
280 --
281 END IF;
282 --
283 WHEN OTHERS THEN
284 --
285 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
286 wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.InactivatePartySites');
287 --
288 IF l_debug_on THEN
289 --
290 WSH_DEBUG_SV.log(l_module_name,'Unexpected error has occured. Oracle error message is ', SUBSTRB(SQLERRM,1,200));
291 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
292 --
293 END IF;
294 --
295 END InactivatePartySites;
296
297
298 --
299 --
300 --========================================================================
301 -- PROCEDURE : Create_Site
302 -- PARAMETERS:
303 -- P_from_id Merge from party ID
304 -- P_to_id Merge to party ID
305 -- P_to_vendor_id Merge to vendor ID
306 -- P_delivery_id Delivery ID
307 -- P_delivery_name Delivery Name
308 -- P_location_id SF Location ID
309 -- X_return_status Return status
310 --
311 -- COMMENT : This is a private procedure to create a new party site.
312 -- It also creates a corresponding party site use record and
313 -- calls Process_Locations() to update information in WSH
314 -- location tables.
315 --========================================================================
316 PROCEDURE Create_Site(
317 p_from_id IN NUMBER,
318 p_to_id IN NUMBER,
319 p_to_vendor_id IN NUMBER,
320 p_delivery_id IN NUMBER,
321 p_delivery_name IN VARCHAR2,
322 p_location_id IN NUMBER,
323 x_return_status OUT NOCOPY VARCHAR2
324 )
325 IS
326 --
327 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_SITE';
328 l_debug_on BOOLEAN;
329 --
330 CURSOR check_location_id IS
331 SELECT 'x', hps.party_site_id
332 FROM hz_party_sites hps,
333 hz_party_site_uses hpsu
334 WHERE hps.party_id = p_to_id
335 AND hps.location_id = p_location_id
336 AND hps.party_site_id = hpsu.party_site_id
337 AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM';
338 --
339 CURSOR check_location_code IS
340 SELECT substr(party_site_number, 1,
341 instr(party_site_number, '|')-1) location_code,
342 hps.party_site_id
343 FROM hz_party_sites hps,
344 hz_party_site_uses hpsu
345 WHERE hps.location_id = p_location_id
346 AND hps.party_id = p_from_id
347 AND hps.party_site_id = hpsu.party_site_id
348 AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM';
349 --
350 l_partySiteId NUMBER;
351 l_location_code VARCHAR2(40);
352 l_new_location_code VARCHAR2(40);
353 l_from_party_site_id NUMBER;
354 --
355 CURSOR chk_locn_csr (p_site_number IN VARCHAR2) IS
356 SELECT 'x'
357 FROM hz_party_sites hps,
358 hz_party_site_uses hpsu
359 WHERE hps.party_id = p_to_id
360 AND hps.party_site_number = p_site_number
361 AND hps.party_site_id = hpsu.party_site_id
362 AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM';
363 --
364 l_dummy VARCHAR2(1);
365 --
366 CURSOR Get_Contact_info ( p_party_id NUMBER,
367 p_party_site_id NUMBER ) IS
368 SELECT contact_person.party_name shipper_name,
369 phone_record.phone_number phone_number,
370 email_record.email_address email_address
371 FROM hz_party_sites hps,
372 hz_parties contact_person,
373 hz_org_contacts supplier_contact,
374 hz_contact_points phone_record,
375 hz_contact_points email_record,
376 hz_relationships hrel
377 WHERE hrel.subject_id = contact_person.party_id
378 AND hrel.subject_table_name = 'HZ_PARTIES'
379 AND hrel.subject_type = 'PERSON'
380 AND hrel.object_id = hps.party_id
381 AND hrel.object_table_name = 'HZ_PARTIES'
382 AND hrel.object_type = 'ORGANIZATION'
383 AND hrel.relationship_code = 'CONTACT_OF'
384 AND hrel.directional_flag = 'F'
385 AND supplier_contact.party_relationship_id =hrel.relationship_id
386 AND supplier_contact.party_site_id = hps.party_site_id
387 AND phone_record.owner_table_name(+) = 'HZ_PARTIES'
388 AND phone_record.owner_table_id(+) = hrel.party_id
389 AND phone_record.contact_point_type(+) = 'PHONE'
390 AND email_record.owner_table_name = 'HZ_PARTIES'
391 AND email_record.owner_table_id = hrel.party_id
392 AND email_record.contact_point_type = 'EMAIL'
393 AND hps.party_site_id =p_party_site_id
394 AND hps.party_id = p_party_id;
395 --
396 l_contact_rec get_contact_info%ROWTYPE;
397 --
398 CURSOR get_supplier_name (p_vendor_id NUMBER) IS
399 SELECT vendor_name
400 FROM po_vendors
401 WHERE vendor_id = p_vendor_id;
402 --
403 l_supplier_name varchar2(360);
404 l_return_status VARCHAR2(2);
405 l_exception_id NUMBER;
406 l_msg VARCHAR2(32767);
407 l_xc_msg_count NUMBER;
408 l_xc_msg_data VARCHAR2(2000);
409 l_site_number VARCHAR2(40);
410 l_to_party_site_id NUMBER;
411 l_loc_chg BOOLEAN;
412 l_location_id NUMBER;
413 l_msg_data VARCHAR2(32767);
414 l_num_warnings NUMBER :=0;
415 l_num_errors NUMBER :=0;
416 l_sql_code NUMBER;
417 l_sql_err VARCHAR2(32767);
418 l_party_site_use_id NUMBER;
419 l_LocationIdTbl WSH_LOCATIONS_PKG.ID_Tbl_Type;
420 l_count NUMBER;
421 --
422 BEGIN
423 --{
424 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
425 --
426 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
427 IF l_debug_on IS NULL THEN
428 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
429 END IF;
430 --
431 IF l_debug_on THEN
432 --
433 WSH_DEBUG_SV.push(l_module_name);
434 WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
435 WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
436 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
437 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME', p_delivery_name );
438 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
439 --
440 END IF;
441 --
442 l_dummy := NULL;
443 OPEN check_location_id;
444 FETCH check_location_id INTO l_dummy, l_partySiteId;
445 CLOSE check_location_id;
446 --
447 IF l_debug_on THEN
448 WSH_DEBUG_SV.log(l_module_name, 'l_dummy', l_dummy);
449 WSH_DEBUG_SV.log(l_module_name, 'l_partySiteId', l_partySiteId);
450 END IF;
451 --
452 IF l_dummy IS NOT NULL THEN
453 --{
454 IF p_from_id <> p_to_id THEN
455 --{
456 IF l_debug_on THEN
457 wsh_debug_sv.logmsg(l_module_name, 'Calling InactivatePartySites', WSH_DEBUG_SV.C_PROC_LEVEL);
458 END IF;
459 --
460 InactivatePartySites
461 (
462 p_party_id => p_from_id,
463 p_to_id => p_to_id,
464 p_party_site_id => l_partySiteId,
465 p_process_locations => TRUE,
466 p_to_vendor_id => p_to_vendor_id,
467 x_return_status => l_return_status
468 );
469 --
470 IF l_debug_on THEN
471 wsh_debug_sv.log(l_module_name, 'Return Status from inactivatePartySites', l_return_status);
472 END IF;
473 --
474 WSH_UTIL_CORE.api_post_call
475 (
476 p_return_status => l_return_status,
477 x_num_warnings => l_num_warnings,
478 x_num_errors => l_num_errors
479 );
480 --}
481 END IF;
482 --
483 IF p_delivery_id IS NOT NULL THEN
484 --{
485 FOR i IN g_LocChangeTab.FIRST..g_LocChangeTab.LAST LOOP
486 --{
487 IF g_LocChangeTab(i).location_id = p_location_id THEN
488 --{
489 IF l_debug_on THEN
490 WSH_DEBUG_SV.logmsg(l_module_name, 'Logging SF code change exception');
491 END IF;
492 --
493 fnd_message.set_name ( 'WSH', 'WSH_IB_SF_LOCN_CODE_CHG');
494 fnd_message.set_token( 'L_LOCATION_CODE' , g_LocChangeTab(i).old_loc_code);
495 fnd_message.set_token( 'L_NEW_LOCATION_CODE', g_LocChangeTab(i).new_loc_code);
496 fnd_message.set_token( 'DELIVERY_NAME' , p_delivery_name );
497 l_msg := FND_MESSAGE.GET;
498 WSH_UTIL_CORE.printMsg(l_msg);
499 --
500 IF l_debug_on THEN
501 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
502 END IF;
503 --
504 wsh_xc_util.log_exception (
505 p_api_version => 1.0,
506 p_exception_name => 'WSH_IB_SF_LOCN_CODE_CHG',
507 p_logging_entity => 'SHIPPER',
508 p_logging_entity_id => FND_GLOBAL.USER_ID,
509 x_return_status => l_return_status,
510 x_exception_id => l_exception_id,
511 x_msg_data => l_xc_msg_data,
512 x_msg_count => l_xc_msg_count,
513 p_message => substrb ( l_msg, 1, 2000 ),
514 p_delivery_id => p_delivery_id,
515 p_exception_location_id => p_location_id,
516 p_logged_at_location_id => p_location_id
517 );
518 --
519 IF l_debug_on THEN
520 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_XC_UTIL.LOG_EXCEPTION is ', l_return_status);
521 END IF;
522 --
523 wsh_util_core.api_post_call(
524 p_return_status => l_return_status,
525 x_num_warnings => l_num_warnings,
526 x_num_errors => l_num_errors,
527 p_msg_data => l_xc_msg_data
528 );
529 --
530 EXIT;
531 --}
532 END IF;
533 --}
534 END LOOP;
535 --}
536 END IF;
537 --
538 IF l_debug_on THEN
539 WSH_DEBUG_SV.pop(l_module_name);
540 END IF;
541 --
542 RETURN;
543 --}
544 END IF;
545 --
546 OPEN check_location_code ;
547 FETCH check_location_code INTO l_location_code, l_from_party_site_id;
548 CLOSE check_location_code;
549 --
550 IF l_debug_on THEN
551 --
552 WSH_DEBUG_SV.log(l_module_name,'cursor check_location_code : L_LOCATION_CODE', l_location_code);
553 WSH_DEBUG_SV.log(l_module_name,'cursor check_location_code : L_FROM_PARTY_SITE_ID ', l_from_party_site_id);
554 --
555 END IF;
556 --
557 OPEN get_contact_info(p_party_id => p_from_id,
558 p_party_site_id => l_from_party_site_id );
559 FETCH get_contact_info INTO l_contact_rec;
560 CLOSE get_contact_info;
561 --
562 IF l_debug_on THEN
563 --
564 WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.SHIPPER_NAME', l_contact_rec.shipper_name);
565 WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.PHONE_NUMBER', l_contact_rec.phone_number);
566 WSH_DEBUG_SV.log(l_module_name,'cursor get_contact_info : L_CONTACT_REC.EMAIL_ADDRESS',l_contact_rec.email_address);
567 --
568 END IF;
569 --
570 l_site_number := l_location_code || '|' || p_to_id;
571 --
572 IF l_debug_on THEN
573 WSH_DEBUG_SV.log(l_module_name,'L_SITE_NUMBER', l_site_number);
574 END IF;
575 --
576 -- Check if we have an entry in hz_party_sites with the same
577 -- party_site_number and merge TO vendor ID
578 --
579 l_dummy := NULL;
580 OPEN chk_locn_csr( p_site_number => l_site_number );
581 FETCH chk_locn_csr INTO l_dummy;
582 CLOSE chk_locn_csr;
583 --
584 IF l_debug_on THEN
585 wsh_debug_sv.log(l_module_name, 'After chk_loc_csr, l_dummy', l_dummy);
586 END IF;
587 --
588 IF l_dummy IS NOT NULL THEN
589 --{
590 IF l_debug_on THEN
591 wsh_debug_sv.log(l_module_name, 'Looping to create unique SF', l_dummy);
592 END IF;
593 --
594 l_loc_chg := TRUE;
595 --
596 FOR I in 1..999 LOOP
597 --{
598 l_dummy := null;
599 l_new_location_code := l_location_code || '-VM'|| lpad(I, 3, '0');
600 l_site_number := l_new_location_code || '|' || p_to_id;
601 --
602 OPEN chk_locn_csr(p_site_number => l_site_number);
603 FETCH chk_locn_csr INTO l_dummy;
604 CLOSE chk_locn_csr;
605 --
606 IF l_dummy IS NULL THEN
607 --
608 l_new_location_code := l_site_number;
609 EXIT;
610 --
611 END IF;
612 --}
613 END LOOP;
614 --
615 IF l_new_location_code is null THEN
616 --{
617 OPEN get_supplier_name ( p_vendor_id => p_to_vendor_id ) ;
618 FETCH get_supplier_name INTO l_supplier_name;
619 CLOSE get_supplier_name;
620 --
621 fnd_message.set_name ( 'WSH', 'WSH_IB_SF_LOCN_CODE_CONFLICT' );
622 fnd_message.set_token( 'LOC_CODE', l_location_code );
623 fnd_message.set_token( 'SUPPLIER_NAME', l_supplier_name );
624 l_msg := FND_MESSAGE.GET;
625 wsh_util_core.printMsg( l_msg );
626 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
627 --
628 IF l_debug_on THEN
629 WSH_DEBUG_SV.pop(l_module_name);
630 END IF;
631 --
632 RETURN;
633 --}
634 END IF;
635 --}
636 ELSE
637 l_new_location_code := l_site_number;
638 l_loc_chg := FALSE;
639 END IF;
640 --
641 IF l_debug_on THEN
642 --{
643 WSH_DEBUG_SV.log(l_module_name, 'l_new_location_code', l_new_location_code);
644 WSH_DEBUG_SV.log(l_module_name, 'l_loc_chg', l_loc_chg);
645 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SUPPLIER_PARTY.CREATE_HZ_PARTY_SITE',WSH_DEBUG_SV.C_PROC_LEVEL);
646 --}
647 END IF;
648 --
649 Wsh_supplier_party.create_hz_party_site(
650 P_party_id => p_to_id,
651 P_location_id => p_location_id,
652 P_location_code => l_new_location_code,
653 x_party_site_id => l_to_party_site_id,
654 x_return_status => l_return_status
655 );
656 --
657 IF l_debug_on THEN
658 --
659 WSH_DEBUG_SV.log(l_module_name, 'x_party_site_id', l_to_party_site_id);
660 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_SUPPLIER_PARTY.CREATE_HZ_PARTY_SITE is', l_return_status);
661 --
662 END IF;
663 --
664 wsh_util_core.api_post_call(
665 p_return_status => l_return_status,
666 x_num_warnings => l_num_warnings,
667 x_num_errors => l_num_errors
668 );
669 --
670 l_location_id := p_location_id;
671 --
672 -- Now create a party Site use record for the party site that we
673 -- just created above.
674 --
675 WSH_SUPPLIER_PARTY.Create_HZ_Party_Site_uses
676 (
677 P_party_site_id => l_to_party_site_id,
678 P_site_use_type => 'SUPPLIER_SHIP_FROM',
679 x_party_site_use_id => l_party_site_use_id,
680 x_return_status => l_return_status
681 );
682 --
683 IF l_debug_on THEN
684 wsh_debug_sv.log(l_module_name, 'l_return_status', l_return_status);
685 wsh_debug_sv.log(l_module_name, 'l_party_site_use_id', l_party_site_use_id);
686 END IF;
687 --
688 wsh_util_core.api_post_call(
689 p_return_status => l_return_status,
690 x_num_warnings => l_num_warnings,
691 x_num_errors => l_num_errors
692 );
693 --
694 l_LocationIdTbl.DELETE;
695 l_LocationIdTbl(l_LocationIdTbl.COUNT+1) := l_location_id;
696 --
697 WSH_LOCATIONS_PKG.Insert_Location_Owners
698 (
699 pLocationIdTbl => l_LocationIdTbl,
700 p_location_source_code => 'HZ',
701 x_return_status => l_return_status
702 );
703 --
704 wsh_util_core.api_post_call(
705 p_return_status => l_return_status,
706 x_num_warnings => l_num_warnings,
707 x_num_errors => l_num_errors
708 );
709 --
710 -- Inactivate party site for the old vendor
711 --
712 IF l_debug_on THEN
713 wsh_debug_sv.logmsg(l_module_name, 'Calling InactivatePartySites', WSH_DEBUG_SV.C_PROC_LEVEL);
714 END IF;
715 --
716 InactivatePartySites
717 (
718 p_party_id => p_from_id,
719 p_party_site_id => l_from_party_site_id,
720 p_process_locations => TRUE,
721 p_to_id => p_to_id,
722 p_to_vendor_id => p_to_vendor_id,
723 x_return_status => l_return_status
724 );
725 --
726 IF l_debug_on THEN
727 wsh_debug_sv.log(l_module_name, 'Return status from InactivatePartySite', l_return_status);
728 END IF;
729 --
730 wsh_util_core.api_post_call(
731 p_return_status => l_return_status,
732 x_num_warnings => l_num_warnings,
733 x_num_errors => l_num_errors
734 );
735 --
736 IF l_debug_on THEN
737 wsh_debug_sv.log(l_module_name, 'l_loc_chg', l_loc_chg);
738 END IF;
739 --
740 IF l_loc_chg AND p_delivery_id IS NOT NULL THEN
741 --{
742 -- Store this location ID in a global table, so for any other
743 -- delivery with this ID we log an exception
744 --
745 l_count := g_LocChangeTab.COUNT + 1;
746 g_LocChangeTab(l_count).location_id := p_location_id;
747 g_LocChangeTab(l_count).old_loc_code := l_location_code;
748 g_LocChangeTab(l_count).new_loc_code := l_new_location_code;
749 --
750 -- Log an exception against delivery, if the SF location changes
751 --
752 fnd_message.set_name ( 'WSH', 'WSH_IB_SF_LOCN_CODE_CHG' );
753 fnd_message.set_token( 'L_LOCATION_CODE' , l_location_code );
754 fnd_message.set_token( 'L_NEW_LOCATION_CODE', l_new_location_code );
755 fnd_message.set_token( 'DELIVERY_NAME' , p_delivery_name );
756 l_msg := FND_MESSAGE.GET;
757 WSH_UTIL_CORE.printMsg(l_msg);
758 --
759 IF l_debug_on THEN
760 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
761 END IF;
762 --
763 wsh_xc_util.log_exception (
764 p_api_version => 1.0,
765 p_exception_name => 'WSH_IB_SF_LOCN_CODE_CHG',
766 p_logging_entity => 'SHIPPER',
767 p_logging_entity_id => FND_GLOBAL.USER_ID,
768 x_return_status => l_return_status,
769 x_exception_id => l_exception_id,
770 x_msg_data => l_xc_msg_data,
771 x_msg_count => l_xc_msg_count,
772 p_message => substrb ( l_msg, 1, 2000 ),
773 p_delivery_id => p_delivery_id,
774 p_exception_location_id => p_location_id,
775 p_logged_at_location_id => p_location_id
776 );
777 --
778 IF l_debug_on THEN
779 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_XC_UTIL.LOG_EXCEPTION is ', l_return_status);
780 END IF;
781 --
782 wsh_util_core.api_post_call(
783 p_return_status => l_return_status,
784 x_num_warnings => l_num_warnings,
785 x_num_errors => l_num_errors,
786 p_msg_data => l_xc_msg_data
787 );
788
789 --}
790 END IF;
791 --
792 IF l_debug_on THEN
793 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SUPPLIER_PARTY.PROCESS_HZ_CONTACT',WSH_DEBUG_SV.C_PROC_LEVEL);
794 END IF;
795 --
796 Wsh_supplier_party.Process_HZ_contact(
797 p_party_id => p_to_id,
798 p_party_site_id => l_to_party_site_id,
799 p_person_name => l_contact_rec.shipper_name,
800 p_phone => l_contact_rec.phone_number,
801 p_email => l_contact_rec.email_address,
802 x_return_status => l_return_status
803 );
804 --
805 IF l_debug_on THEN
806 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_SUPPLIER_PARTY.PROCESS_HZ_CONTACT', l_return_status);
807 END IF;
808 --
809 wsh_util_core.api_post_call(
810 p_return_status => l_return_status,
811 x_num_warnings => l_num_warnings,
812 x_num_errors => l_num_errors
813 );
814 --
815 IF l_num_errors > 0 THEN
816 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
817 ELSIF l_num_warnings > 0 THEN
818 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
819 END IF;
820 --
821 IF l_debug_on THEN
822 WSH_DEBUG_SV.pop(l_module_name);
823 END IF;
824 --
825 EXCEPTION
826 --
827 WHEN FND_API.G_EXC_ERROR THEN
828 --
829 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
830 --
831 IF l_debug_on THEN
832 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
833 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
834 END IF;
835 --
836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
837 --
838 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
839 --
840 IF l_debug_on THEN
841 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
842 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
843 END IF;
844 --
845 WHEN OTHERS THEN
846 --
847 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
848 wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Create_Site');
849 --
850 IF l_debug_on THEN
851 WSH_DEBUG_SV.log(l_module_name,'Unexpected error has occured. Oracle error message is ', SUBSTRB(SQLERRM,1,200));
852 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
853 END IF;
854 --}
855 END Create_Site;
856 --
857
858
859
860 --========================================================================
861 -- PROCEDURE : Update_New_Delivery
862 -- PARAMETERS:
863 -- P_from_iD Merge from vendor ID
864 -- P_to_id Merge to vendor ID
865 -- P_to_party_id Merge to party ID
866 -- P_from_party_id Merge from party ID
867 -- P_delivery_id Delivery ID
868 -- P_from_site_id Merge from site ID
869 -- P_old_delivery_id Previous delivery ID
870 -- P_temp_update_flag Flag to update the temp table or not
871 -- P_location_id Delivery SF Location id
872 --
873 -- COMMENT : This is a private procedure to update the delivery records
874 -- with new vendor_id for the vendor merge.
875 --========================================================================
876
877 PROCEDURE Update_New_Delivery (
878 p_from_id IN NUMBER,
879 p_to_id IN NUMBER,
880 p_to_party_id IN NUMBER,
881 p_from_party_id IN NUMBER,
882 p_delivery_id IN NUMBER,
883 p_from_site_id IN NUMBER,
884 p_old_delivery_id IN NUMBER,
885 p_temp_update_flag IN VARCHAR2,
886 p_location_id IN NUMBER,
887 x_return_status OUT NOCOPY VARCHAR2
888 ) IS
889 --
890 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_NEW_DELIVERY';
891 l_debug_on BOOLEAN;
892 --
893 l_return_status VARCHAR2(2);
894 l_dlvy_name VARCHAR2(30);
895 l_location_id NUMBER;
896 l_msg_data VARCHAR2(32767);
897 l_num_warnings NUMBER :=0;
898 l_num_errors NUMBER :=0;
899
900 --
901 BEGIN
902 --{
903 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
904 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
905 IF l_debug_on IS NULL THEN
906 --{
907 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
908 --}
909 END IF;
910
911 --
912 IF l_debug_on THEN
913 --{
914 WSH_DEBUG_SV.push(l_module_name);
915 WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID', p_from_id );
916 WSH_DEBUG_SV.log(l_module_name,'P_TO_ID', p_to_id );
917 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id );
918 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id );
919 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID', p_delivery_id );
920 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID', p_from_site_id );
921 WSH_DEBUG_SV.log(l_module_name,'P_OLD_DELIVERY_ID', p_old_delivery_id );
922 WSH_DEBUG_SV.log(l_module_name,'P_TEMP_UPDATE_FLAG', p_temp_update_flag );
923 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID', p_location_id );
924 --}
925 END IF;
926
927
928 IF (p_delivery_id IS NOT NULl) THEN
929 --{
930 UPDATE wsh_new_deliveries
931 SET vendor_id = p_to_id,
932 party_id = p_to_party_id,
933 last_update_date = sysdate,
934 last_updated_by = fnd_global.user_id,
935 last_update_login = fnd_global.login_id
936 WHERE delivery_id = p_delivery_id
937 RETURNING name INTO l_dlvy_name;
938 IF l_debug_on THEN
939 --{
940 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
941 --}
942 END IF;
943
944 ELSE
945
946 UPDATE wsh_new_deliveries
947 SET vendor_id = p_to_id,
948 party_id = p_to_party_id,
949 last_update_date = sysdate,
950 last_updated_by = fnd_global.user_id,
951 last_update_login = fnd_global.login_id
952 WHERE delivery_id
953 IN ( SELECT delivery_id
954 FROM wsh_delivery_assignments
955 WHERE delivery_detail_id
956 IN ( SELECT delivery_detail_id
957 FROM wsh_delivery_details
958 WHERE source_code = 'PO'
959 AND vendor_id = p_from_id
960 AND ship_from_site_id = p_from_site_id
961 AND source_header_id
962 IN (SELECT po_header_id
963 FROM po_headers_all
964 WHERE vendor_id = p_to_id
965 )
966 )
967 )
968 AND vendor_id = p_from_id;
969 IF l_debug_on THEN
970 --{
971 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
972 --}
973 END IF;
974 --}
975 END IF;
976
977
978 IF (p_temp_update_flag = 'Y') THEN
979 --{
980 UPDATE wsh_wms_sync_tmp
981 SET temp_col = 'Y',
982 parent_delivery_detail_id = p_delivery_id
983 WHERE delivery_id = p_old_delivery_id
984 AND operation_type = 'VENDOR_MRG';
985 ELSIF (p_temp_update_flag = 'N') THEN
986 UPDATE wsh_wms_sync_tmp
987 SET temp_col = 'Y'
988 WHERE delivery_id = p_old_delivery_id
989 AND operation_type = 'VENDOR_MRG';
990 --}
991 END IF;
992
993 IF l_debug_on THEN
994 --{
995 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_wms_sync_tmp. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
996 --}
997 END IF;
998
999
1000 IF p_location_id IS NOT NULL THEN
1001 --{
1002 SELECT source_location_id INTO l_location_id
1003 FROM wsh_locations
1004 WHERE wsh_location_id = p_location_id;
1005
1006 IF l_debug_on THEN
1007 --{
1008 WSH_DEBUG_SV.log(l_module_name, 'l_location_id', l_location_id);
1009 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.CREATE_SITE' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
1010 --}
1011 END IF;
1012
1013 create_site (
1014 p_from_id =>p_from_party_id,
1015 p_to_id =>p_to_party_id,
1016 p_to_vendor_id => p_to_id,
1017 p_delivery_id =>p_delivery_id,
1018 P_delivery_name =>l_dlvy_name,
1019 P_location_id =>l_location_id,
1020 x_return_status =>l_return_status
1021 );
1022 wsh_util_core.api_post_call(
1023 p_return_status => l_return_status,
1024 x_num_warnings => l_num_warnings,
1025 x_num_errors => l_num_errors
1026 );
1027 IF l_debug_on THEN
1028 --{
1029 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_VENDOR_PARTY_MERGE_PKG.CREATE_SITE is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
1030 --}
1031 END IF;
1032 --}
1033 END IF;
1034
1035 IF l_num_errors > 0 THEN
1036 --{
1037 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1038 ELSIF l_num_warnings > 0 THEN
1039 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1040 --}
1041 END IF;
1042 --
1043 IF l_debug_on THEN
1044 WSH_DEBUG_SV.pop(l_module_name);
1045 END IF;
1046
1047 EXCEPTION
1048 --
1049 WHEN FND_API.G_EXC_ERROR THEN
1050 --
1051 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1052 --
1053 IF l_debug_on THEN
1054 --
1055 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
1056 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1057 --
1058 END IF;
1059 --
1060 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1061 --
1062 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1063 --
1064 IF l_debug_on THEN
1065 --
1066 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1067 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1068 --
1069 END IF;
1070 --
1071 WHEN OTHERS THEN
1072 --
1073 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1074 wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Update_New_Delivery');
1075 --
1076 IF l_debug_on THEN
1077 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1078 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1079 END IF;
1080 --
1081
1082 --}
1083 END Update_New_Delivery;
1084
1085
1086 --
1087 --
1088 --========================================================================
1089 -- PROCEDURE : Update_Non_PO_Entities
1090 -- PARAMETERS:
1091 -- P_to_id Merge to vendor ID
1092 -- P_from_id Merge from vendor ID
1093 -- P_from_party_id Merge from party ID
1094 -- P_to_party_id Merge to party ID
1095 -- P_from_site_id Merge from vendor site ID
1096 -- P_to_site_id Merge to vendor site ID
1097 -- X_return_status Return status
1098 -- p_site_merge Site level Merge
1099 -- p_from_supplier_name Merge from Supplier Name
1100
1101 -- COMMENT : This is a procedure to Update for entities which are
1102 -- not dependent on the invoice/PO selection
1103 --========================================================================
1104 PROCEDURE Update_Non_PO_Entities(
1105 p_to_id IN NUMBER,
1106 p_from_id IN NUMBER,
1107 p_from_party_id IN NUMBER,
1108 p_to_party_id IN NUMBER,
1109 p_to_site_id IN NUMBER,
1110 p_from_site_id IN NUMBER,
1111 p_site_merge IN BOOLEAN,
1112 p_from_supplier_name IN VARCHAR2,
1113 x_return_status OUT NOCOPY VARCHAR2
1114 ) IS
1115
1116 l_return_status VARCHAR2(1);
1117 l_debug_on BOOLEAN;
1118 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_NON_PO_ENTITIES';
1119
1120
1121 CURSOR check_calendar IS
1122 SELECT calendar_type,
1123 calendar_assignment_id,
1124 vendor_site_id,
1125 association_type,
1126 freight_code
1127 FROM wsh_calendar_assignments a
1128 WHERE vendor_id = p_from_id
1129 AND vendor_site_id IS NULL;
1130
1131 CURSOR check_site_calendar IS
1132 SELECT a.calendar_type,
1133 a.calendar_assignment_id,
1134 a.vendor_site_id,
1135 a.association_type,
1136 a.freight_code,
1137 b.vendor_site_code,
1138 a.calendar_code
1139 FROM wsh_calendar_assignments a,
1140 po_vendor_sites_all b
1141 WHERE a.vendor_id = p_from_id
1142 AND a.vendor_site_id = p_from_site_id
1143 AND b.vendor_site_id = a.vendor_site_id;
1144
1145 CURSOR check_dup_assignment( p_vendor_id NUMBER,
1146 p_calendar_Type VARCHAR2,
1147 p_vendor_site_id NUMBER,
1148 p_association_type VARCHAR2,
1149 p_freight_code VARCHAR2 )
1150 IS
1151 SELECT 1
1152 FROM wsh_calendar_assignments
1153 WHERE vendor_id = p_vendor_id
1154 AND calendar_type=p_calendar_type
1155 AND nvl( vendor_site_id,-999999 ) = nvl( p_vendor_site_id,-999999 )
1156 AND association_type = p_association_type
1157 AND nvl( freight_code, '!!!' ) = nvl( p_freight_code, '!!!' );
1158 --
1159 l_dummy NUMBER;
1160 --
1161
1162 CURSOR get_party_site_csr(p_party_id NUMBER) IS
1163 SELECT location_id,
1164 hps.party_site_id,
1165 hps.object_version_number
1166 FROM hz_party_sites hps,
1167 hz_party_site_uses hpsu
1168 WHERE hps.party_id = p_party_id
1169 AND hps.party_site_id = hpsu.party_site_id
1170 AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM'
1171 AND hpsu.status = 'A';
1172
1173
1174 l_from_party_rec hz_party_site_v2pub.party_site_rec_type;
1175 l_msg_data VARCHAR2(32767);
1176 l_num_warnings NUMBER :=0;
1177 l_num_errors NUMBER :=0;
1178 l_object_version_number NUMBER;
1179 l_msg VARCHAR2(32767);
1180 l_query_count NUMBER :=0;
1181 l_msg_count NUMBER ;
1182 --
1183 CURSOR c_VendorLvlCalAsg(p_vendorID NUMBER,
1184 p_assnType VARCHAR2,
1185 p_caltype VARCHAR2) IS
1186 SELECT 1
1187 FROM wsh_calendar_assignments
1188 WHERE vendor_id = p_vendorID
1189 AND association_type = p_assnType
1190 AND calendar_type = p_calType
1191 AND vendor_site_id IS NULL;
1192 --
1193 v_VendorLvlCalAsg NUMBER := 0;
1194 l_CalAsgInfo WSH_CAL_ASG_PKG.CalAsgRecType;
1195 l_CalAsgId NUMBER;
1196 --
1197 BEGIN
1198
1199
1200 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1201 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1202
1203 IF l_debug_on IS NULL THEN
1204 --{
1205 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1206 --}
1207 END IF;
1208
1209
1210 IF l_debug_on THEN
1211 --{
1212 WSH_DEBUG_SV.push(l_module_name);
1213 WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1214 WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1215 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1216 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1217 WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1218 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1219 WSH_DEBUG_SV.log(l_module_name,'P_SITE_MERGE',p_site_merge);
1220 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SUPPLIER_NAME',p_from_supplier_name);
1221 --}
1222 END IF;
1223
1224 -- Update WSH_CARRIERS with the merge to vendor/vendor site
1225 UPDATE wsh_carriers
1226 SET supplier_id = p_to_id,
1227 supplier_site_id = p_to_site_id,
1228 last_update_date = sysdate,
1229 last_updated_by = fnd_global.user_id,
1230 last_update_login = fnd_global.login_id
1231 WHERE supplier_id = p_from_id
1232 AND supplier_site_id = p_from_site_id;
1233 --
1234 IF l_debug_on THEN
1235 --{
1236 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_carriers. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1237 --}
1238 END IF;
1239 --
1240 -- Update WSH_CARRIER_SITES with the merge to vendor site
1241 --
1242 UPDATE wsh_carrier_sites
1243 SET supplier_site_id = p_to_site_id,
1244 last_update_date = sysdate,
1245 last_updated_by = fnd_global.user_id,
1246 last_update_login = fnd_global.login_id
1247 WHERE supplier_site_id = p_from_site_id;
1248 --
1249 IF l_debug_on THEN
1250 --{
1251 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_carrier_sites. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1252 --}
1253 END IF;
1254 --
1255 WSH_PARTY_MERGE.Update_Entities_During_Merge
1256 (
1257 p_to_id => p_to_id,
1258 p_from_id => p_from_id,
1259 p_from_party_id => p_from_party_id ,
1260 p_to_party_id => p_to_party_id ,
1261 p_to_site_id => p_to_site_id,
1262 p_from_site_id => p_from_site_id,
1263 p_site_merge => p_site_merge,
1264 p_from_supplier_name => p_from_supplier_name,
1265 x_return_status => l_return_status
1266 );
1267 --
1268 wsh_util_core.api_post_call
1269 (
1270 p_return_status => l_return_status,
1271 x_num_warnings => l_num_warnings,
1272 x_num_errors => l_num_errors
1273 );
1274 --
1275 -- Now, update/delete vendor site level calendar assignments if any
1276 --
1277 FOR check_site_calendar_rec IN check_site_calendar
1278 LOOP
1279 --{
1280 IF l_debug_on THEN
1281 --{
1282 WSH_DEBUG_SV.logmsg(l_module_name, '-----------------------', WSH_DEBUG_SV.C_STMT_LEVEL);
1283 WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_TYPE = ' || check_site_calendar_rec.calendar_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1284 WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.CALENDAR_ASSIGNMENT_ID = ' || check_site_calendar_rec.calendar_assignment_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1285 WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_ID = ' || check_site_calendar_rec.vendor_site_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1286 WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.ASSOCIATION_TYPE = ' || check_site_calendar_rec.association_type, WSH_DEBUG_SV.C_STMT_LEVEL);
1287 WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.FREIGHT_CODE = ' || check_site_calendar_rec.freight_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1288 WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.VENDOR_SITE_CODE = ' || check_site_calendar_rec.vendor_site_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1289 WSH_DEBUG_SV.logmsg(l_module_name,'CHECK_SITE_CALENDAR_REC.calendar_CODE = ' || check_site_calendar_rec.calendar_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1290 --}
1291 END IF;
1292 OPEN check_dup_assignment( p_vendor_id => p_to_id,
1293 p_calendar_Type => check_site_calendar_rec.calendar_type,
1294 p_vendor_site_id=> p_to_site_id,
1295 p_association_type => check_site_calendar_rec.association_type ,
1296 p_freight_code=> check_site_calendar_rec.freight_code );
1297 FETCH check_dup_assignment INTO l_dummy;
1298
1299 IF (check_dup_assignment%NOTFOUND) THEN
1300 --{
1301 -- Update vendor site level assignments
1302 UPDATE wsh_calendar_assignments
1303 SET vendor_id = p_to_id,
1304 vendor_site_id = p_to_site_id,
1305 last_update_date = sysdate,
1306 last_updated_by = fnd_global.user_id,
1307 last_update_login = fnd_global.login_id
1308 WHERE calendar_assignment_id = check_site_calendar_rec.calendar_assignment_id;
1309 --
1310 IF l_debug_on THEN
1311 --{
1312 WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID updated',
1313 check_site_calendar_rec.calendar_assignment_id);
1314 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_calendar_assignments. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1315 --}
1316 END IF;
1317 --
1318 -- Is there a vendor level calendar assignment ? If not, create one
1319 -- otherwise, the above entry will never show up in the form
1320 --
1321 OPEN c_VendorLvlCalAsg(p_vendorID => p_to_id,
1322 p_assnType => 'VENDOR',
1323 p_calType => check_site_calendar_rec.calendar_type);
1324 FETCH c_VendorLvlCalAsg INTO v_VendorLvlCalAsg;
1325 CLOSE c_VendorLvlCalAsg;
1326 --
1327 IF l_debug_on THEN
1328 WSH_DEBUG_SV.log(l_module_name, 'Vendor Lvl Cal. Asg Exists ? ', v_VendorLvlCalAsg);
1329 END IF;
1330 --
1331 IF v_VendorLvlCalAsg = 0 THEN
1332 --{
1333 --
1334 IF l_debug_on THEN
1335 WSH_DEBUG_SV.log(l_module_name, 'Creating vendor level Cal. Asg', p_to_id);
1336 END IF;
1337 --
1338 l_CalAsgInfo.CALENDAR_CODE := check_site_calendar_rec.calendar_code;
1339 l_CalAsgInfo.CALENDAR_TYPE := check_site_calendar_rec.calendar_type;
1340 l_CalAsgInfo.ENABLED_FLAG := 'Y';
1341 l_CalAsgInfo.ASSOCIATION_TYPE := 'VENDOR';
1342 l_CalAsgInfo.VENDOR_ID := p_to_id;
1343 --
1344 WSH_CAL_ASG_PKG.Create_Cal_Asg
1345 (
1346 p_api_version_number => 1.0,
1347 p_cal_asg_info => l_CalAsgInfo,
1348 x_return_status => l_return_status,
1349 x_msg_count => l_msg_count,
1350 x_msg_data => l_msg_data,
1351 x_Calendar_Aassignment_Id => l_CalAsgId
1352 );
1353 --
1354 IF l_debug_on THEN
1355 WSH_DEBUG_SV.log(l_module_name, 'Return status from CAL. API', l_return_status);
1356 WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID', l_CalAsgId);
1357 END IF;
1358 --
1359 wsh_util_core.api_post_call
1360 (
1361 p_return_status => l_return_status,
1362 x_num_warnings => l_num_warnings,
1363 x_num_errors => l_num_errors,
1364 p_msg_data => l_msg
1365 );
1366 --}
1367 END IF;
1368 --
1369
1370 --}
1371 ELSE
1372 --{
1373 DELETE wsh_calendar_assignments
1374 WHERE calendar_assignment_id = check_site_calendar_rec.calendar_assignment_id;
1375 IF l_debug_on THEN
1376 --{
1377 WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID deleted',
1378 check_site_calendar_rec.calendar_assignment_id);
1379 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted record(s) from wsh_calendar_assignments. Number of Rows deleted is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1380 --}
1381 END IF;
1382
1383 IF check_site_calendar_rec.freight_code IS NULL THEN
1384 --{
1385 fnd_message.set_name ( 'WSH', 'WSH_IB_DEL_SP_SITE_CAL_ASGN' );
1386 ELSE
1387 fnd_message.set_name ( 'WSH', 'WSH_IB_DEL_SP_SITE_FC_CAL_ASGN' );
1388 fnd_message.set_token( 'FREIGHT_CODE' , check_site_calendar_rec.freight_code );
1389 --}
1390 END IF;
1391 fnd_message.set_token( 'SUPPLIER_NAME' , p_from_supplier_name );
1392 fnd_message.set_token( 'CAL_TYPE' , check_site_calendar_rec.calendar_type );
1393 fnd_message.set_token( 'SITE_CODE' , check_site_calendar_rec.vendor_site_code );
1394 l_msg := FND_MESSAGE.GET;
1395 wsh_util_core.printMsg( l_msg );
1396 --}
1397 END IF;--IF (check_dup_assignment%NOTFOUND)
1398
1399 CLOSE check_dup_assignment;
1400 --}
1401 END LOOP;-- FOR check_site_calendar_rec IN check_site_calendar
1402 --
1403 -- Inactive the party_site
1404 --
1405 IF NOT p_site_merge THEN
1406 --{
1407 IF l_debug_on THEN
1408 wsh_debug_sv.logmsg(l_module_name, 'Calling InactivatePartySites', WSH_DEBUG_SV.C_PROC_LEVEL);
1409 END IF;
1410 --
1411 InactivatePartySites
1412 (
1413 p_party_id => p_from_party_id,
1414 p_to_id => p_to_party_id,
1415 p_to_vendor_id => p_to_id,
1416 x_return_status => l_return_status
1417 );
1418 --
1419 IF l_debug_on THEN
1420 wsh_debug_sv.log(l_module_name, 'Return Status from InactivatePartySites', l_return_status);
1421 END IF;
1422 --
1423 wsh_util_core.api_post_call
1424 (
1425 p_return_status => l_return_status,
1426 x_num_warnings => l_num_warnings,
1427 x_num_errors => l_num_errors,
1428 p_msg_data => l_msg
1429 );
1430 --}
1431 END IF;
1432 --
1433 IF l_num_errors > 0 THEN
1434 --{
1435 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1436 ELSIF l_num_warnings > 0 THEN
1437 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1438 --}
1439 END IF;
1440 --
1441 IF l_debug_on THEN
1442 WSH_DEBUG_SV.pop(l_module_name);
1443 END IF;
1444 --
1445 EXCEPTION
1446 --
1447 WHEN FND_API.G_EXC_ERROR THEN
1448 --
1449 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1450 --
1451 IF l_debug_on THEN
1452 --
1453 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
1454 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1455 --
1456 END IF;
1457 --
1458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1459 --
1460 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1461 --
1462 IF l_debug_on THEN
1463 --
1464 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1465 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1466 --
1467 END IF;
1468 --
1469 WHEN OTHERS THEN
1470 --
1471 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1472 wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Update_Non_PO_Entities');
1473 --
1474 IF l_debug_on THEN
1475 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1476 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1477 END IF;
1478 --
1479 END Update_Non_PO_Entities;
1480
1481
1482 --========================================================================
1483 -- PROCEDURE :Vendor_Merge
1484 -- PARAMETERS:
1485 -- P_from_id Merge from vendor ID
1486 -- P_to_id Merge to vendor ID
1487 -- P_from_party_id Merge from party ID
1488 -- P_to_party_id Merge to party ID
1489 -- P_from_site_id Merge from vendor site ID
1490 -- P_to_site_id Merge to vendor site ID
1491 -- p_calling_mode Either 'INVOICE' or 'PO'
1492 -- x_return_status Return status
1493 --
1494 -- COMMENT :
1495 -- This is the core WSH Vendor merge routine that is called from
1496 -- Vendor_Party_Merge() API.
1497 -- This procedure can be divided into two portions, merge validation and merge.
1498 -- In the first portion, it will determine if the vendor merge is allowed.
1499 -- In the second portion, it will update all the affected tables if merge is allowed
1500 --
1501 -- Parameter p_calling_mode indicates what updates to perform.
1502 -- 'INVOICE' ==> Update only non-PO entities
1503 -- 'PO' ==> Update PO related entities
1504 --========================================================================
1505 PROCEDURE Vendor_Merge (
1506 p_from_id IN NUMBER,
1507 p_to_id IN NUMBER,
1508 p_from_party_id IN NUMBER,
1509 p_to_party_id IN NUMBER,
1510 p_from_site_id IN NUMBER,
1511 p_to_site_id IN NUMBER,
1512 p_calling_mode IN VARCHAR2,
1513 x_return_status OUT NOCOPY VARCHAR2 ) IS
1514 --
1515 l_return_status VARCHAR2(1);
1516 l_debug_on BOOLEAN;
1517 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VENDOR_MERGE';
1518 --
1519 CURSOR check_vendor_active (p_vendor_id NUMBER) IS
1520 SELECT end_date_active,
1521 vendor_name
1522 FROM po_vendors
1523 WHERE vendor_id = p_vendor_id;
1524 --
1525 l_end_date_active DATE;
1526 l_supplier_name VARCHAR2(360);
1527 --
1528 /*
1529 * R12 Perf Bug 4949639 : Do not need this cursor any more
1530 * since we rely on the parameter p_calling_mode
1531 CURSOR check_option IS
1532 SELECT process
1533 FROM ap_duplicate_vendors_all
1534 WHERE vendor_id = p_to_id
1535 AND vendor_site_id = p_to_site_id
1536 AND duplicate_vendor_id = p_from_id
1537 AND duplicate_vendor_site_id = p_from_site_id;
1538 */
1539 --
1540 l_option VARCHAR2(25);
1541 --
1542 CURSOR check_po IS
1543 SELECT w.delivery_id,
1544 d.delivery_detail_id
1545 FROM po_headers_all p,
1546 wsh_delivery_details d,
1547 wsh_delivery_assignments w,
1548 Wsh_new_deliveries wnd
1549 WHERE p.vendor_id = p_to_id
1550 AND d.source_code = 'PO'
1551 AND p.po_header_id = d.source_header_id
1552 AND p.vendor_site_id = p_to_site_id
1553 AND d.vendor_id = p_from_id
1554 AND d.ship_From_site_id=p_from_site_id
1555 AND d.delivery_detail_id = w.delivery_detail_id
1556 AND w.delivery_id = wnd.delivery_id(+)
1557 AND nvl(w.type, 'S') IN ('S' ,'O');
1558 --
1559 l_delivery_list wsh_util_core.id_tab_type;
1560 l_dd_list wsh_util_core.id_tab_type;
1561 --
1562
1563 CURSOR find_delivery IS
1564 SELECT distinct delivery_id
1565 FROM wsh_wms_sync_tmp
1566 WHERE operation_type = 'VENDOR_MRG'
1567 AND temp_col IS NULL
1568 AND delivery_id IS NOT NULL;
1569 --
1570 TYPE l_dlvy_rec IS RECORD (delivery_id NUMBER);
1571 TYPE t_delivery_tbl IS TABLE OF l_dlvy_rec INDEX BY BINARY_INTEGER;
1572 l_delivery_rec_tbl t_delivery_tbl;
1573 l_delivery_rec l_dlvy_rec;
1574 --
1575
1576 CURSOR check_duplicate_vendor(p_del_id NUMBER) IS
1577 SELECT 'Y' ,
1578 wnd.initial_pickup_location_id,
1579 wnd.status_code,
1580 wnd.routing_response_id,
1581 wnd.name,
1582 wnd.ultimate_dropoff_location_id
1583 FROM wsh_new_deliveries wnd,
1584 wsh_delivery_details wdd,
1585 wsh_delivery_assignments wda
1586 WHERE wnd.delivery_id = p_del_id
1587 AND wnd.delivery_id = wda.delivery_id
1588 AND wdd.delivery_detail_id = wda.delivery_detail_id
1589 AND wdd.ship_from_site_id <> p_from_site_id
1590 AND wdd.ship_from_site_id <> p_to_site_id
1591 AND wdd.vendor_id = p_from_id
1592 AND nvl(wda.type,'S') IN ('S' , 'O')
1593 AND NOT EXISTS (SELECT 1
1594 FROM ap_duplicate_vendors_all
1595 WHERE process_flag IN ('S', 'D')
1596 AND process IN ('P','B')
1597 AND duplicate_vendor_id = wdd.vendor_id
1598 AND duplicate_vendor_site_id = wdd.ship_from_site_id
1599 AND vendor_id = p_to_id
1600 );
1601 --
1602 l_dup VARCHAR2(1) := 'N';
1603 l_location_id NUMBER;
1604 l_dlvy_status_code VARCHAR2(30);
1605 l_routing_response_id NUMBER;
1606 l_dlvy_name VARCHAR2(30);
1607 l_ult_dropoff_loc_id NUMBER;
1608 --
1609
1610 CURSOR check_temp (p_delivery_id NUMBER) IS
1611 SELECT parent_delivery_detail_id
1612 FROM wsh_wms_sync_tmp wwst,
1613 wsh_delivery_details wdd
1614 WHERE wwst.delivery_id = p_delivery_id
1615 AND wwst.temp_col IS NOT NULL
1616 AND operation_type = 'VENDOR_MRG'
1617 AND wdd.delivery_detail_id = wwst.delivery_detail_id
1618 AND wdd.vendor_id = p_to_id
1619 AND wwst.parent_delivery_detail_id IS NOT NULL;
1620 --
1621 l_temp NUMBER;
1622 --
1623
1624 CURSOR check_detail(p_delivery_id NUMBER) IS
1625 SELECT delivery_detail_id
1626 FROM wsh_wms_sync_tmp
1627 WHERE delivery_id = p_delivery_id
1628 AND operation_type = 'VENDOR_MRG'
1629 AND temp_col IS NULL;
1630 --
1631 l_delivery_detail_tbl wsh_util_core.id_tab_type;
1632 --
1633 CURSOR dlvy_rr_csr(p_delivery_id NUMBER) IS
1634 SELECT wdd.delivery_detail_id,
1635 wdd.routing_req_id,
1636 wdd.vendor_id,
1637 wth.receipt_number rr_number,
1638 wth.revision_number,
1639 wnd.ultimate_dropoff_location_id,
1640 wnd.name
1641 FROM wsh_delivery_details wdd,
1642 wsh_delivery_assignments wda ,
1643 wsh_inbound_txn_history wth,
1644 wsh_new_deliveries wnd
1645 WHERE wda.delivery_id = p_delivery_id
1646 AND nvl(wda.type,'S') IN ('S','O')
1647 AND wda.delivery_detail_Id = wdd.delivery_detail_id
1648 AND wdd.routing_req_id = wth.transaction_id
1649 AND wth.transaction_type='ROUTING_REQUEST'
1650 AND wdd.vendor_id <> wth.supplier_id
1651 AND wnd.delivery_id = wda.delivery_id
1652 ORDER BY routing_req_id;
1653 --
1654 TYPE l_delivery_rr_rec IS RECORD(
1655 delivery_detail_id NUMBER,
1656 routing_req_id NUMBER,
1657 vendor_id NUMBER,
1658 rr_number VARCHAR2(40),
1659 revision_number NUMBER,
1660 ult_dropoff_loc_id NUMBER,
1661 name VARCHAR2(30)
1662 );
1663 TYPE t_delivery_rr_type IS TABLE OF l_delivery_rr_rec INDEX BY BINARY_INTEGER;
1664 l_dlvy_rr_rec_tbl t_delivery_rr_type;
1665 l_dlvy_rr_rec l_delivery_rr_rec;
1666
1667 l_prev_old_rr_id NUMBER;
1668 l_new_rr_id NUMBER;
1669 l_new_rr_number VARCHAR2(40);
1670 --
1671
1672 CURSOR chk_rreq_csr(p_supplier_id NUMBER,
1673 p_rr_number VARCHAR2) IS
1674 SELECT transaction_id,
1675 revision_number,
1676 parent_shipment_header_id
1677 FROM wsh_inbound_txn_history
1678 WHERE supplier_id = p_supplier_id
1679 AND receipt_number = p_rr_number
1680 AND transaction_type='ROUTING_REQUEST'
1681 ORDER BY revision_number DESC;
1682 --
1683 l1_transaction_id NUMBER;
1684 l1_revision_number NUMBER;
1685 l1_parent_shipment_header_id NUMBER;
1686 --
1687
1688 l_delivery_cache_tbl wsh_util_core.id_tab_type;
1689 l_dlvy_rr_cache_tbl wsh_util_core.id_tab_type;
1690 l_dlvy_rr_tbl wsh_new_deliveries_pvt.Delivery_Attr_Tbl_Type;
1691 l_dlvy_tbl wsh_util_core.id_tab_type;
1692 l_chk_delivery_id NUMBER :=0;
1693 l_prev_new_rr_id NUMBER :=0;
1694 l_site_merge BOOLEAN;
1695 l_new_delivery_id NUMBER;
1696 j NUMBER := 0;
1697 i NUMBER;
1698 l_exception_id NUMBER;
1699 l_msg VARCHAR2(32767);
1700 l_msg_count NUMBER ;
1701 l_xc_msg_data VARCHAR2(2000);
1702 l_num_warnings NUMBER :=0;
1703 l_num_errors NUMBER :=0;
1704 l_tmp_rr_number VARCHAR2(40);
1705 l_txn_history_rec wsh_inbound_txn_history_pkg.ib_txn_history_rec_type;
1706 l_cache_index NUMBER;
1707 l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
1708 l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
1709 l_defaults_rec WSH_DELIVERIES_GRP.default_parameters_rectype;
1710 l_msg_data VARCHAR2(32767);
1711 l_xc_msg_count NUMBER;
1712 l_respIndex NUMBER;
1713 l_numRowsUpdated NUMBER;
1714 --
1715 BEGIN
1716 --{
1717 --
1718 SAVEPOINT WSH_Vendor_Merge;
1719 --
1720 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1721 wsh_util_core.g_call_fte_load_tender_api := FALSE;
1722 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1723
1724 IF l_debug_on IS NULL THEN
1725 --{
1726 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1727 --}
1728 END IF;
1729
1730
1731 IF l_debug_on THEN
1732 --{
1733 WSH_DEBUG_SV.push(l_module_name);
1734 WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
1735 WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
1736 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
1737 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
1738 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
1739 WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
1740 WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
1741 --}
1742 END IF;
1743
1744 wsh_util_core.enable_concurrent_log_print ;
1745
1746 -- find out if it is a vendor merge OR vendor site merge
1747 OPEN check_vendor_active(p_vendor_id => p_from_id);
1748 FETCH check_vendor_active INTO l_end_date_active, l_supplier_name;
1749
1750 IF check_vendor_active%NOTFOUND THEN
1751 --{
1752 CLOSE check_vendor_active;
1753 IF l_debug_on THEN
1754 --{
1755 WSH_DEBUG_SV.logmsg(l_module_name,'Error : No Record exists in PO_VENDOR for vendor id : ' || p_from_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1756 --}
1757 END IF;
1758 fnd_message.set_name ( 'WSH', 'WSH_IB_VENDOR_NOT_EXISTS' );
1759 fnd_message.set_token( 'VENDOR_ID' , to_char(p_from_id) );
1760 l_msg := FND_MESSAGE.GET;
1761 wsh_util_core.printMsg( l_msg );
1762 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1763 --
1764 IF l_debug_on THEN
1765 WSH_DEBUG_SV.pop(l_module_name);
1766 END IF;
1767 --
1768 RETURN;
1769 --}
1770 END IF;
1771
1772 CLOSE check_vendor_active;
1773
1774 IF nvl(l_end_date_active,sysdate+1) <= sysdate THEN
1775 --{
1776 l_site_merge := false;
1777 ELSE
1778 l_site_merge := true;
1779 --}
1780 END IF;
1781
1782 IF l_debug_on THEN
1783 --{
1784 WSH_DEBUG_SV.log(l_module_name,'End date for Vendor ID : ' || p_from_id || ' is ',l_end_date_active, WSH_DEBUG_SV.C_STMT_LEVEL);
1785 WSH_DEBUG_SV.log(l_module_name,'L_SITE_MERGE = ',l_site_merge, WSH_DEBUG_SV.C_STMT_LEVEL);
1786 --}
1787 END IF;
1788
1789 -- Update for entities which are not dependent on the invoice/PO selection
1790 IF l_debug_on THEN
1791 --{
1792 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NON_PO_ENTITIES',WSH_DEBUG_SV.C_PROC_LEVEL);
1793 --}
1794 END IF;
1795 --
1796 -- Update non-PO entities irrespective of calling mode
1797 --
1798 Update_Non_PO_Entities(
1799 p_to_id => p_to_id,
1800 p_from_id => p_from_id,
1801 p_from_party_id => p_from_party_id ,
1802 p_to_party_id => p_to_party_id ,
1803 p_to_site_id => p_to_site_id,
1804 p_from_site_id => p_from_site_id,
1805 p_site_merge => l_site_merge,
1806 p_from_supplier_name => l_supplier_name,
1807 X_return_status => l_return_status
1808 );
1809 --
1810 wsh_util_core.api_post_call(
1811 p_return_status => l_return_status,
1812 x_num_warnings => l_num_warnings,
1813 x_num_errors => l_num_errors
1814 );
1815 --
1816 -- Update PO related entities only when the mode is PO
1817 --
1818 IF (p_calling_mode = 'PO') THEN
1819 --{
1820 -- Determine the invoice/PO selections
1821 --
1822 -- R12 Perf Bug 4949639 : Do not need this any more since we rely
1823 -- on the parameter p_calling_mode
1824 --
1825 --OPEN check_option;
1826 --FETCH check_option INTO l_option;
1827 --CLOSE check_option;
1828 --
1829 --IF l_debug_on THEN
1830 --WSH_DEBUG_SV.logmsg(l_module_name,'l_OPTION = ' || l_option, WSH_DEBUG_SV.C_STMT_LEVEL);
1831 --END IF;
1832 --
1833
1834 --IF (l_option= 'B' OR l_option = 'P') THEN
1835 --{
1836 --Find out all delivery detail lines impacted by the site merge AND insert the records into a temp table
1837 OPEN check_po;
1838 FETCH check_po BULK COLLECT INTO l_delivery_list, l_dd_list;
1839 CLOSE check_po;
1840 IF l_debug_on THEN
1841 --{
1842 WSH_DEBUG_SV.logmsg(l_module_name,'Count of Rows fetched from Cursor CHECK_PO = ' || l_delivery_list.count, WSH_DEBUG_SV.C_STMT_LEVEL);
1843 --}
1844 END IF;
1845
1846 IF l_dd_list.COUNT > 0 THEN
1847 --{
1848 FORALL j IN l_dd_list.FIRST..l_dd_list.LAST
1849 INSERT INTO wsh_wms_sync_tmp
1850 ( delivery_detail_id,
1851 delivery_id,
1852 operation_type,
1853 creation_date )
1854 VALUES ( l_dd_list(j),
1855 l_delivery_list(j),
1856 'VENDOR_MRG',
1857 sysdate );
1858
1859 IF l_debug_on THEN
1860 --{
1861 WSH_DEBUG_SV.logmsg(l_module_name,'Inserted records into wsh_wms_sync_tmp. Number of Rows inserted is ' || l_dd_list.count, WSH_DEBUG_SV.C_STMT_LEVEL);
1862 --}
1863 END IF;
1864
1865 --
1866 FORALL j IN l_dd_list.FIRST..l_dd_list.LAST
1867 UPDATE wsh_delivery_details
1868 SET vendor_id = p_to_id,
1869 ship_from_site_id = p_to_site_id,
1870 party_id = p_to_party_id,
1871 last_update_date = sysdate,
1872 last_updated_by = fnd_global.user_id,
1873 last_update_login = fnd_global.login_id
1874 WHERE delivery_detail_id = l_dd_list(j);
1875 IF SQL%ROWCOUNT <> l_dd_list.count THEN
1876 --{
1877 IF l_debug_on THEN
1878 --{
1879 WSH_DEBUG_SV.log(l_module_name, 'Updated WDD records with vendor/vendor site', p_to_id || ' - ' || p_To_site_id);
1880 WSH_DEBUG_SV.logmsg( l_module_name,'Out of ' || l_dd_list.count || ' delivery details, only ' || sql%rowcount || ' were updated.', WSH_DEBUG_SV.C_STMT_LEVEL);
1881 --}
1882 END IF;
1883 fnd_message.set_name ( 'WSH', 'WSH_IB_DLY_DET_UPDT_MISMATCH' );
1884 fnd_message.set_token( 'NUM_DETAILS_AFFECTED' , to_char(l_dd_list.count) );
1885 fnd_message.set_token( 'NUM_DETAILS_UPDATED' , to_char(SQL%ROWCOUNT) );
1886 l_msg := FND_MESSAGE.GET;
1887 wsh_util_core.printMsg( l_msg );
1888 --}
1889 END IF;
1890 IF l_debug_on THEN
1891 --{
1892 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
1893 --}
1894 END IF;
1895
1896 --}
1897 END IF;--IF l_dd_list.COUNT > 0
1898 --
1899 IF p_from_id <> p_to_id THEN
1900 --{
1901 -- Determine how many deliveries that are impacted
1902 --
1903 OPEN find_delivery;
1904 FETCH find_delivery BULK COLLECT INTO l_delivery_rec_tbl;
1905 CLOSE find_delivery;
1906 --}
1907 END IF;
1908 --
1909 IF l_debug_on THEN
1910 WSH_DEBUG_SV.log(l_module_name,'Rows fetched from Cursor FIND_DELIVERY', l_delivery_rec_tbl.count);
1911 END IF;
1912
1913 IF l_delivery_rec_tbl.COUNT > 0 THEN
1914 --{
1915 FOR k IN l_delivery_rec_tbl.FIRST..l_delivery_rec_tbl.LAST
1916 LOOP
1917 --{
1918 --
1919 IF l_debug_on THEN
1920 wsh_debug_sv.logmsg(l_module_name, '**********************************', WSH_DEBUG_SV.C_STMT_LEVEL);
1921 wsh_debug_sv.log(l_module_name, 'Processing delivery', l_delivery_rec_tbl(k).delivery_id);
1922 wsh_debug_sv.logmsg(l_module_name, '**********************************', WSH_DEBUG_SV.C_STMT_LEVEL);
1923 END IF;
1924 --
1925 l_delivery_rec := l_delivery_rec_tbl(k);
1926 l_dup :=NULL;
1927 l_location_id :=NULL;
1928 l_dlvy_status_code :=NULL;
1929 l_routing_response_id :=NULL;
1930 OPEN check_duplicate_vendor( p_del_id => l_delivery_rec.delivery_id );
1931 FETCH check_duplicate_vendor INTO l_dup,
1932 l_location_id,
1933 l_dlvy_status_code,
1934 l_routing_response_id,
1935 l_dlvy_name,
1936 l_ult_dropoff_loc_id;
1937 CLOSE check_duplicate_vendor;
1938 IF l_debug_on THEN
1939 --{
1940 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DUP =' || l_dup, WSH_DEBUG_SV.C_STMT_LEVEL);
1941 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_LOCATION_ID =' || l_location_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1942 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_STATUS_CODE =' || l_dlvy_status_code, WSH_DEBUG_SV.C_STMT_LEVEL);
1943 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ROUTING_RESPONSE_ID =' || l_routing_response_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1944 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_DLVY_NAME =' || l_dlvy_name, WSH_DEBUG_SV.C_STMT_LEVEL);
1945 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_DUPLICATE_VENDOR : L_ULT_DROPOFF_LOC_ID =' || l_ult_dropoff_loc_id, WSH_DEBUG_SV.C_STMT_LEVEL);
1946 --}
1947 END IF;
1948
1949
1950 IF (l_dup = 'Y') THEN
1951 --{
1952 l_temp := NULL;
1953 OPEN check_temp( p_delivery_id => l_delivery_rec.delivery_id );
1954 FETCH check_temp INTO l_temp;
1955 CLOSE check_temp;
1956 IF l_debug_on THEN
1957 --{
1958 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHECK_TEMP : L_TEMP =' || l_temp, WSH_DEBUG_SV.C_STMT_LEVEL);
1959 --}
1960 END IF;
1961
1962 IF ( l_temp IS NULL ) THEN
1963 --{
1964 -- Call Split_Delivery to split the delivery line
1965
1966 OPEN check_detail ( l_delivery_rec.delivery_id );
1967 FETCH check_detail BULK COLLECT INTO l_delivery_detail_tbl;
1968 CLOSE check_detail;
1969 IF l_debug_on THEN
1970 --{
1971 WSH_DEBUG_SV.logmsg(l_module_name,'Count of Rows fetched from Cursor CHECK_DETAIL = ' || l_delivery_detail_tbl.count, WSH_DEBUG_SV.C_STMT_LEVEL);
1972 --}
1973 END IF;
1974
1975 IF l_debug_on THEN
1976 --{
1977 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.SPLIT_INBOUND_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
1978 --}
1979 END IF;
1980
1981 l_new_delivery_id := NULL;
1982 wsh_inbound_util_pkg.split_inbound_delivery(
1983 p_delivery_detail_id_tbl => l_delivery_detail_tbl,
1984 p_delivery_id => l_delivery_rec.delivery_id,
1985 x_delivery_id => l_new_delivery_id,
1986 x_return_status => l_return_status,
1987 p_caller => 'WSH_VENDOR_MERGE'
1988 );
1989 IF l_debug_on THEN
1990 --{
1991 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_INBOUND_UTIL_PKG.SPLIT_INBOUND_DELIVERY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
1992 WSH_DEBUG_SV.log(l_module_name, 'New Delivery ID', l_new_delivery_id);
1993 --}
1994 END IF;
1995
1996 wsh_util_core.api_post_call(
1997 p_return_status => l_return_status,
1998 x_num_warnings => l_num_warnings,
1999 x_num_errors => l_num_errors
2000 );
2001
2002 l_chk_delivery_id := l_new_delivery_id;
2003
2004 --Update WSH_NEW_DELIVERIES with the merge to vendor
2005 IF l_debug_on THEN
2006 --{
2007 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2008 --}
2009 END IF;
2010
2011 Update_new_delivery(
2012 p_from_id => p_from_id,
2013 p_to_id => p_to_id,
2014 p_to_party_id => p_to_party_id,
2015 p_from_party_id => p_from_party_id,
2016 p_delivery_id => l_new_delivery_id,
2017 p_from_site_id => p_from_site_id,
2018 p_old_delivery_id => l_delivery_rec.delivery_id,
2019 p_temp_update_flag => 'Y',
2020 p_location_id => l_location_id,
2021 x_return_status => l_return_status
2022 );
2023 IF l_debug_on THEN
2024 --{
2025 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2026 --}
2027 END IF;
2028
2029 wsh_util_core.api_post_call(
2030 p_return_status => l_return_status,
2031 x_num_warnings => l_num_warnings,
2032 x_num_errors => l_num_errors
2033 );
2034
2035 l_delivery_cache_tbl(l_new_delivery_id):= l_new_delivery_id;
2036 l_delivery_cache_tbl(l_delivery_rec.delivery_id) := l_delivery_rec.delivery_id;
2037
2038 IF l_dlvy_status_code ='OP' AND l_routing_response_id IS NOT NULL THEN
2039 --{
2040 l_dlvy_rr_cache_tbl(l_new_delivery_id) := l_new_delivery_id;
2041 --}
2042 END IF;
2043 --}
2044 ELSE --IF ( l_temp is NULL )
2045 --{
2046 OPEN check_detail( l_delivery_rec.delivery_id );
2047 FETCH check_detail BULK COLLECT INTO l_delivery_detail_tbl;
2048 CLOSE check_detail;
2049 IF l_debug_on THEN
2050 --{
2051 WSH_DEBUG_SV.logmsg(l_module_name,'Count of Rows fetched from Cursor CHECK_DETAIL = ' || l_delivery_detail_tbl.count, WSH_DEBUG_SV.C_STMT_LEVEL);
2052 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.SPLIT_INBOUND_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2053 --}
2054 END IF;
2055
2056 Wsh_inbound_util_pkg.split_inbound_delivery(
2057 p_delivery_detail_id_tbl => l_delivery_detail_tbl,
2058 p_delivery_id => l_delivery_rec.delivery_id,
2059 x_delivery_id => l_temp,
2060 x_return_status => l_return_status,
2061 p_caller => 'WSH_VENDOR_MERGE'
2062 );
2063 IF l_debug_on THEN
2064 --{
2065 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_INBOUND_UTIL_PKG.SPLIT_INBOUND_DELIVERY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2066 --}
2067 END IF;
2068
2069 wsh_util_core.api_post_call(
2070 p_return_status => l_return_status,
2071 x_num_warnings => l_num_warnings,
2072 x_num_errors => l_num_errors
2073 );
2074
2075 l_chk_delivery_id := l_temp;
2076 l_delivery_cache_tbl(l_temp) := l_temp;
2077 l_delivery_cache_tbl(l_delivery_rec.delivery_id) := l_delivery_rec.delivery_id;
2078
2079 IF l_dlvy_status_code ='OP' AND l_routing_response_id IS NOT NULL THEN
2080 --{
2081 l_dlvy_rr_cache_tbl(l_temp) := l_temp;
2082 --}
2083 END IF;
2084
2085 --}
2086 END IF;
2087 --}
2088 ELSE --l_dup<>'Y'
2089 --{
2090 -- Need to select all delivery level attributes again
2091 --
2092 SELECT initial_pickup_location_id, routing_response_id,
2093 name, status_code
2094 INTO l_location_id, l_routing_response_id, l_dlvy_name, l_dlvy_status_code
2095 FROM wsh_new_deliveries
2096 WHERE delivery_id = l_delivery_rec.delivery_id;
2097 --
2098 --Update WSH_NEW_DELIVERIES with the merge to vendor
2099 IF l_debug_on THEN
2100 --{
2101 --wsh_debug_sv.log(l_module_name, 'l_dup', l_dup);
2102 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2103 --}
2104 END IF;
2105
2106 Update_new_delivery(
2107 p_from_id => p_from_id,
2108 p_to_id => p_to_id,
2109 p_to_party_id => p_to_party_id,
2110 p_from_party_id => p_from_party_id,
2111 p_delivery_id => l_delivery_rec.delivery_id,
2112 p_from_site_id => p_from_site_id,
2113 p_old_delivery_id=> l_delivery_rec.delivery_id,
2114 p_temp_update_flag=> 'N',
2115 p_location_id =>l_location_id,
2116 x_return_status => l_return_status
2117 );
2118 IF l_debug_on THEN
2119 --{
2120 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2121 --}
2122 END IF;
2123
2124 wsh_util_core.api_post_call(
2125 p_return_status => l_return_status,
2126 x_num_warnings => l_num_warnings,
2127 x_num_errors => l_num_errors
2128 );
2129 --
2130 l_chk_delivery_id := l_delivery_rec.delivery_id;
2131 --
2132 IF l_routing_response_id IS NOT NULL THEN
2133 --{
2134 UPDATE wsh_inbound_txn_history
2135 SET supplier_id = p_to_id,
2136 last_update_date = sysdate,
2137 last_updated_by = fnd_global.user_id,
2138 last_update_login = fnd_global.login_id
2139 WHERE transaction_type = 'ROUTING_RESPONSE'
2140 AND shipment_header_id = l_delivery_rec.delivery_id
2141 AND supplier_id = p_from_id;
2142 IF l_debug_on THEN
2143 --{
2144 WSH_DEBUG_SV.log(l_module_name, 'Updated ROUTING_RESP record for', l_delivery_rec.delivery_id);
2145 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_inbound_txn_history. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
2146 --}
2147 END IF;
2148
2149 --}
2150 END IF;
2151
2152 --}
2153 END IF; -- if l_dup ='Y'
2154 --
2155 IF l_debug_on THEN
2156 wsh_debug_sv.log(l_module_name, 'Updating vendor info. on containers assigned to delivery', l_chk_delivery_id);
2157 END IF;
2158 --
2159 UPDATE wsh_delivery_details
2160 SET vendor_id = p_to_id,
2161 party_id = p_to_party_id,
2162 last_update_date = sysdate,
2163 last_updated_by = fnd_global.user_id,
2164 last_update_login = fnd_global.login_id
2165 WHERE container_flag = 'Y'
2166 AND vendor_id = p_from_id
2167 AND delivery_detail_id
2168 IN (
2169 SELECT delivery_detail_id
2170 FROM wsh_delivery_assignments
2171 WHERE nvl(type,'S') in ('S','O')
2172 AND delivery_id = l_chk_delivery_id
2173 );
2174 --
2175 IF l_debug_on THEN
2176 wsh_debug_sv.log(l_module_name, 'No. of container records updated', SQL%ROWCOUNT);
2177 END IF;
2178 --
2179 OPEN dlvy_rr_csr(l_chk_delivery_id);
2180 FETCH dlvy_rr_csr BULK COLLECT INTO l_dlvy_rr_rec_tbl;
2181 CLOSE dlvy_rr_csr;
2182 --
2183 IF l_debug_on THEN
2184 --{
2185 WSH_DEBUG_SV.logmsg(l_module_name,'Count of Rows fetched from Cursor DLVY_RR_CSR = ' || l_dlvy_rr_rec_tbl.count, WSH_DEBUG_SV.C_STMT_LEVEL);
2186 --}
2187 END IF;
2188
2189 IF l_dlvy_rr_rec_tbl.COUNT > 0 THEN
2190 --{
2191 FOR l in l_dlvy_rr_rec_tbl.FIRST..l_dlvy_rr_rec_tbl.LAST
2192 LOOP
2193 --{
2194 l_dlvy_rr_rec := l_dlvy_rr_rec_tbl(l);
2195 IF l_prev_old_rr_id = l_dlvy_rr_rec.routing_req_id THEN
2196 --{
2197 l_new_rr_id := l_prev_new_rr_id;
2198 ELSE
2199 l_new_rr_id := NULL;
2200 l_new_rr_number := NULL;
2201 l1_transaction_id := NULL;
2202 l1_revision_number := NULL;
2203 l1_parent_shipment_header_id := NULL;
2204 --
2205 OPEN chk_rreq_csr( p_supplier_id => l_dlvy_rr_rec.vendor_id,
2206 p_rr_number => l_dlvy_rr_Rec.rr_number );
2207 FETCH chk_rreq_csr INTO l1_transaction_id,
2208 l1_revision_number,
2209 l1_parent_shipment_header_id;
2210 CLOSE chk_rreq_csr;
2211 IF l_debug_on THEN
2212 --{
2213 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_TRANSACTION_ID =' || l1_transaction_id, WSH_DEBUG_SV.C_STMT_LEVEL);
2214 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_REVISION_NUMBER =' || l1_revision_number, WSH_DEBUG_SV.C_STMT_LEVEL);
2215 WSH_DEBUG_SV.logmsg(l_module_name,'Cursor CHK_RREQ_CSR : L1_PARENT_SHIPMENT_HEADER_ID =' || l1_parent_shipment_header_id, WSH_DEBUG_SV.C_STMT_LEVEL);
2216 --}
2217 END IF;
2218
2219 IF l1_revision_number = l_dlvy_rr_rec.revision_number
2220 AND l1_parent_shipment_header_id = l_dlvy_rr_Rec.routing_req_id Then
2221 --{
2222 l_new_rr_id := l1_transaction_id;
2223 l_new_rr_number := l_dlvy_rr_Rec.rr_number;
2224 ELSIF l1_revision_number IS NOT NULL THEN
2225 --{
2226 FOR I IN 1..999
2227 LOOP
2228 --{
2229 l_tmp_rr_number := l_dlvy_rr_rec.rr_number || '-VM'|| lpad(I,3,'0');
2230
2231 l1_transaction_id := NULL;
2232 l1_revision_number:= NULL;
2233 l1_parent_shipment_header_id := NULL;
2234 --
2235 OPEN chk_rreq_csr( p_supplier_id => l_dlvy_rr_rec.vendor_id,
2236 p_rr_number => l_tmp_rr_number );
2237 FETCH chk_rreq_csr INTO l1_transaction_id,
2238 l1_revision_number,
2239 l1_parent_shipment_header_id;
2240 CLOSE chk_rreq_csr;
2241
2242 IF l1_revision_number = l_dlvy_rr_rec.revision_number
2243 AND l1_parent_shipment_header_id = l_dlvy_rr_Rec.routing_req_id THEN
2244 --{
2245 l_new_rr_id := l1_transaction_id;
2246 l_new_rr_number := l_tmp_rr_number;
2247 EXIT;
2248 --
2249 ELSIF l1_revision_number IS NULL
2250 AND l1_parent_shipment_header_id IS NULL THEN
2251 --
2252 l_new_rr_number := l_tmp_rr_number;
2253 EXIT;
2254 --
2255 --}
2256 END IF;
2257 --}
2258 END LOOP;--FOR I IN 1..999
2259
2260 IF l_new_rr_id IS NULL AND l_new_rr_number IS NULL THEN
2261 --{
2262 --
2263 --
2264 fnd_message.set_name ( 'WSH', 'WSH_IB_RR_NUMBER_CONFLICT' );
2265 fnd_message.set_token( 'SUPPLIER_NAME', l_supplier_name);
2266 l_msg := FND_MESSAGE.GET;
2267 wsh_util_core.printMsg( l_msg );
2268
2269 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2270 IF NOT(wsh_util_core.g_call_fte_load_tender_api) THEN
2271 --{
2272 wsh_util_core.Reset_stops_for_load_tender(
2273 p_reset_flags=>true,
2274 x_return_status=>l_return_status
2275 );
2276 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2277 --{
2278 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2279 --}
2280 END IF;
2281 --}
2282 END IF;
2283 --
2284 IF l_debug_on THEN
2285 WSH_DEBUG_SV.pop(l_module_name);
2286 END IF;
2287 --
2288 RETURN;
2289 --}
2290 END IF;
2291 --}
2292 ELSE --ELSIF l1_revision_number IS NOT NULL THEN
2293
2294 l_new_rr_number := l_dlvy_rr_Rec.rr_number;
2295
2296 --}
2297 END IF; --IF l1_revision_number = l_dlvy_rr_rec.revision_number
2298 -- AND l1_parent_shipment_header_id = l_dlvy_rr_Rec.routing_req_id
2299
2300
2301 IF l_new_rr_id IS NULL THEN
2302 --{
2303 IF l_debug_on THEN
2304 --{
2305 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_TXN_HISTORY_PKG.GET_TXN_HISTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
2306 --}
2307 END IF;
2308 WSH_INBOUND_TXN_HISTORY_PKG.get_txn_history(
2309 p_transaction_id => l_dlvy_rr_rec.routing_req_id,
2310 x_txn_history_rec => l_txn_history_rec,
2311 x_return_status => l_return_status
2312 );
2313 IF l_debug_on THEN
2314 --{
2315 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_INBOUND_TXN_HISTORY_PKG.GET_TXN_HISTORY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2316 --}
2317 END IF;
2318 wsh_util_core.api_post_call(
2319 p_return_status => l_return_status,
2320 x_num_warnings => l_num_warnings,
2321 x_num_errors => l_num_errors
2322 );
2323 --
2324 l_txn_history_Rec.receipt_number := l_new_rr_number;
2325 l_txn_history_Rec.parent_shipment_header_id := l_dlvy_rr_rec.routing_req_id;
2326 l_txn_history_rec.supplier_id := p_to_id;
2327 --
2328 IF l_debug_on THEN
2329 --{
2330 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_TXN_HISTORY_PKG.CREATE_TXN_HISTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
2331 --}
2332 END IF;
2333 WSH_INBOUND_TXN_HISTORY_PKG.create_txn_history(
2334 p_txn_history_rec => l_txn_history_rec,
2335 x_txn_id => l_new_rr_id,
2336 x_return_status => l_return_status
2337 );
2338 IF l_debug_on THEN
2339 --
2340 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_INBOUND_TXN_HISTORY_PKG.CREATE_TXN_HISTORY is ' , l_return_status);
2341 WSH_DEBUG_SV.log(l_module_name, 'New Txn ID', l_new_rr_id);
2342
2343 --
2344 END IF;
2345 wsh_util_core.api_post_call(
2346 p_return_status => l_return_status,
2347 x_num_warnings => l_num_warnings,
2348 x_num_errors => l_num_errors
2349 );
2350
2351 --
2352 --}
2353 END IF;--IF l_new_rr_id IS NULL
2354 --
2355 -- Log an exception against delivery, if the routing req number has changed
2356 --
2357 IF (l_new_rr_number <> l_dlvy_rr_rec.rr_number) THEN
2358 --{
2359 fnd_message.set_name ( 'WSH', 'WSH_IB_RR_NUMBER_CHG' );
2360 fnd_message.set_token('OLD_RR_NUMBER',to_char(l_dlvy_rr_rec.rr_number));
2361 fnd_message.set_token( 'NEW_RR_NUMBER', to_char( l_new_rr_number ) );
2362 fnd_message.set_token( 'DELIVERY_NAME', l_dlvy_rr_rec.name );
2363 l_msg := FND_MESSAGE.GET;
2364 --
2365 WSH_UTIL_CORE.printMsg(l_msg);
2366 --
2367 l_exception_id := NULL;
2368 --
2369 wsh_xc_util.log_exception (
2370 p_api_version => 1.0,
2371 p_exception_name => 'WSH_IB_RR_NUMBER_CHG',
2372 p_logging_entity => 'SHIPPER',
2373 p_logging_entity_id => FND_GLOBAL.USER_ID,
2374 x_return_status => l_return_status,
2375 x_exception_id => l_exception_id,
2376 x_msg_data => l_xc_msg_data,
2377 x_msg_count => l_xc_msg_count,
2378 p_message => substrb ( l_msg, 1, 2000 ),
2379 p_delivery_id => l_chk_delivery_id,
2380 p_exception_location_id =>l_dlvy_rr_rec.ult_dropoff_loc_id,
2381 p_logged_at_location_id =>l_dlvy_rr_rec.ult_dropoff_loc_id
2382 );
2383 --
2384 IF l_debug_on THEN
2385 --
2386 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_XC_UTIL.LOG_EXCEPTION is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2387 --
2388 END IF;
2389 --
2390 wsh_util_core.api_post_call(
2391 p_return_status => l_return_status,
2392 x_num_warnings => l_num_warnings,
2393 x_num_errors => l_num_errors,
2394 p_msg_data => l_xc_msg_data
2395 );
2396 --}
2397 END IF;
2398 --}
2399 END IF;--IF l_prev_old_rr_id = l_dlvy_rr_rec.routing_req_id
2400 --
2401 IF l_debug_on THEN
2402 wsh_debug_sv.log(l_module_name, 'WDD ID to update', l_dlvy_rr_rec.delivery_detail_id);
2403 wsh_debug_sv.log(l_module_name, 'Routing Req ID to update with', l_new_rr_id);
2404 END IF;
2405 --
2406 UPDATE wsh_delivery_details
2407 SET routing_req_Id = l_new_rr_id,
2408 last_update_date = sysdate,
2409 last_updated_by = fnd_global.user_id,
2410 last_update_login = fnd_global.login_id
2411 WHERE delivery_detail_id = l_dlvy_rr_rec.delivery_detail_id;
2412 --
2413 IF l_debug_on THEN
2414 --{
2415 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
2416 --}
2417 END IF;
2418
2419 l_prev_old_rr_id := l_dlvy_rr_rec.routing_req_id;
2420 l_prev_new_rr_id := l_new_rr_id;
2421
2422 --}
2423 END LOOP;--FOR l_dlvy_rr_rec in l_dlvy_rr_rec_tbl.FIRST..l_dlvy_rr_rec_tbl.LAST
2424 --}
2425 END IF;
2426 --}
2427 END LOOP;--FOR l_delivery_rec IN l_delivery_rec_tbl.FIRST..l_delivery_rec_tbl.LAST LOOP
2428 --}
2429 END IF; --IF l_delivery_rec_tbl.COUNT > 0
2430 --
2431 IF p_From_id <> p_to_id THEN
2432 --{
2433 -- Update WSH_INBOUND_TXN_HISTORY with the merge to vendor
2434 --
2435 UPDATE wsh_inbound_txn_history a
2436 SET supplier_id = p_to_id,
2437 last_update_date = sysdate,
2438 last_updated_by = fnd_global.user_id,
2439 last_update_login = fnd_global.login_id
2440 WHERE supplier_id = p_from_id
2441 AND transaction_type not in ('ROUTING_REQUEST','ROUTING_RESPONSE')
2442 AND exists (SELECT shipment_header_id
2443 FROM rcv_shipment_headers b
2444 WHERE b.shipment_header_id = a.shipment_header_id
2445 AND b.vendor_id = p_to_id
2446 );
2447 --
2448 l_numRowsUpdated := SQL%ROWCOUNT;
2449 --
2450 IF l_debug_on THEN
2451 --{
2452 WSH_DEBUG_SV.log(l_module_name, 'Updated ASN/RECEIPT records with vendor', p_to_id);
2453 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_inbound_txn_history. Number of Rows updated is ' || l_numRowsUpdated, WSH_DEBUG_SV.C_STMT_LEVEL);
2454 --}
2455 END IF;
2456 --}
2457 END IF;
2458 --
2459 -- Convert l_dlvy_rr_cache_tbl to a new contiguous table (l_dlvy_rr_tbl)
2460 -- call WSH_DELIVERIES_GRP.delivery_action passing l_dlvy_rr_tbl
2461 -- AND action_code = 'GENERATE-ROUTING-RESPONSE'.
2462 -- l_dlvy_rr_tbl.delete;
2463 l_cache_index := l_dlvy_rr_cache_tbl.FIRST ;
2464 WHILE l_cache_index IS NOT NULL
2465 LOOP
2466 --{
2467 l_respIndex := l_dlvy_rr_tbl.COUNT+1;
2468 l_dlvy_rr_tbl(l_respIndex).delivery_id := l_dlvy_rr_cache_tbl( l_cache_index );
2469 --
2470 SELECT organization_id
2471 INTO l_dlvy_rr_tbl(l_respIndex).organization_id
2472 FROM wsh_new_deliveries
2473 WHERE delivery_id = l_dlvy_rr_tbl(l_respIndex).delivery_id;
2474 --
2475 l_cache_index := l_dlvy_rr_cache_tbl.Next( l_cache_index );
2476 --}
2477 END LOOP;
2478 --
2479 l_action_prms.action_code := 'GENERATE-ROUTING-RESPONSE' ;
2480 l_action_prms.caller := 'WSH_PUB';
2481 --
2482 IF l_debug_on THEN
2483 --{
2484 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERIES_GRP.DELIVERY_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
2485 WSH_DEBUG_SV.log(l_module_name, 'l_dlvy_rr_tbl.COUNT', l_dlvy_rr_tbl.COUNT);
2486 --}
2487 END IF;
2488 --
2489 IF l_dlvy_rr_tbl.COUNT > 0 THEN
2490 --{
2491 WSH_DELIVERIES_GRP.delivery_action(
2492 p_api_version_number => 1.0,
2493 p_init_msg_list => FND_API.G_FALSE,
2494 p_commit => FND_API.G_FALSE,
2495 p_action_prms => l_action_prms,
2496 p_rec_attr_tab => l_dlvy_rr_tbl,
2497 x_delivery_out_rec => l_delivery_out_rec,
2498 x_defaults_rec => l_defaults_rec,
2499 x_return_status => l_return_status,
2500 x_msg_count => l_msg_count,
2501 x_msg_data => l_msg
2502 );
2503 IF l_debug_on THEN
2504 --{
2505 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_DELIVERIES_GRP.DELIVERY_ACTION is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2506 --}
2507 END IF;
2508
2509 wsh_util_core.api_post_call(
2510 p_return_status => l_return_status,
2511 x_num_warnings => l_num_warnings,
2512 x_num_errors => l_num_errors,
2513 p_msg_data => l_msg
2514 );
2515 --}
2516 END IF;
2517
2518 --
2519 --Convert l_dlvy_cache_tbl into a contiguous table l_dlvy_tbl
2520 -- AND call WSH_WV_UTILS.delivery_weight_volume
2521 --l_dlvy_tbl.delete;
2522 l_cache_index := l_delivery_cache_tbl.FIRST ;
2523 WHILE l_cache_index IS NOT NULL
2524 LOOP
2525 --{
2526 l_dlvy_tbl( l_dlvy_tbl.count + 1 ) := l_delivery_cache_tbl( l_cache_index );
2527 l_cache_index := l_delivery_cache_tbl.Next( l_cache_index );
2528 --}
2529 END LOOP;
2530
2531 IF l_debug_on THEN
2532 --{
2533 WSH_DEBUG_SV.logmsg( l_module_name,'Calling program unit WSH_WV_UTILS.DELIVERY_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL );
2534 WSH_DEBUG_SV.log(l_module_name, 'l_dlvy_tbl.COUNT', l_dlvy_tbl.COUNT);
2535 FOR i IN 1..l_dlvy_tbl.COUNT LOOP
2536 wsh_debug_sv.log(l_module_name, 'l_dlvy_tbl(i)', l_dlvy_tbl(i));
2537 END LOOP;
2538 --
2539 --}
2540 END IF;
2541 --
2542 IF l_dlvy_tbl.COUNT > 0 THEN
2543 --{
2544 WSH_WV_UTILS.delivery_weight_volume(
2545 p_del_rows => l_dlvy_tbl,
2546 p_update_flag => 'Y',
2547 p_calc_wv_if_frozen => 'N',
2548 x_return_status => l_return_status
2549 );
2550 IF l_debug_on THEN
2551 --{
2552 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_WV_UTILS.DELIVERY_WEIGHT_VOLUME is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2553 --}
2554 END IF;
2555
2556 wsh_util_core.api_post_call(
2557 p_return_status => l_return_status,
2558 x_num_warnings => l_num_warnings,
2559 x_num_errors => l_num_errors
2560 );
2561 --}
2562 END IF;
2563
2564 /*
2565 FORALL I IN l_dlvy_tbl.first..l_dlvy_tbl.last
2566 UPDATE wsh_delivery_legs
2567 SET reprice_required = 'Y',
2568 last_update_date = sysdate,
2569 last_updated_by = fnd_global.user_id,
2570 last_update_login = fnd_global.login_id
2571 WHERE delivery_id = l_dlvy_tbl(i);
2572
2573 ELSIF (l_option = 'B' or l_option = 'I') THEN
2574
2575 --Update FTE_INVOICE_HEADERS with the merge to vendor/vendor site
2576 UPDATE fte_invoice_headers a
2577 SET supplier_id = p_to_id,
2578 supplier_site_id = p_to_site_id,
2579 last_update_date = sysdate,
2580 last_updated_by = fnd_global.user_id,
2581 last_update_login = fnd_global.login_id
2582 WHERE supplier_id = p_from_id
2583 AND supplier_site_id = p_from_site_id
2584 AND exists (SELECT 1
2585 FROM ap_invoices_all
2586 WHERE vendor_id = p_to_id
2587 AND vendor_site_id = p_to_site_id
2588 AND invoice_num = a.bill_number
2589 ); */
2590
2591 --}
2592 -- END IF; --IF (l_option= 'B' OR l_option = 'P')
2593
2594 --
2595 IF NOT(wsh_util_core.g_call_fte_load_tender_api) THEN
2596 --{
2597 wsh_util_core.Process_stops_for_load_tender(
2598 p_reset_flags=>true,
2599 x_return_status=>l_return_status);
2600 wsh_util_core.api_post_call(
2601 p_return_status => l_return_status,
2602 x_num_warnings => l_num_warnings,
2603 x_num_errors => l_num_errors
2604 );
2605 --}
2606 END IF;
2607 --}
2608 END IF;
2609 --
2610 IF l_num_errors > 0 THEN
2611 --{
2612 ROLLBACK TO WSH_Vendor_Merge;
2613 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2614 ELSIF l_num_warnings > 0 THEN
2615 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2616 --}
2617 END IF;
2618 --
2619 --Vendor Merge for RTV Transations
2620
2621 Merge_RTV_Transactions(
2622 p_from_vendor_id => p_from_id,
2623 p_to_vendor_id => p_to_id ,
2624 p_from_site_id => p_from_site_id,
2625 p_to_site_id => p_to_site_id ,
2626 x_return_status => x_return_status );
2627
2628 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
2629 --{
2630 ROLLBACK TO WSH_Vendor_Merge;
2631 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2632 END IF;
2633
2634 --End of Vendor Merge for RTV Transations
2635
2636 IF l_debug_on THEN
2637 WSH_DEBUG_SV.log(l_module_name, 'Final return status', x_return_status);
2638 WSH_DEBUG_SV.pop(l_module_name);
2639 END IF;
2640
2641 EXCEPTION
2642 --
2643 WHEN FND_API.G_EXC_ERROR THEN
2644 --
2645 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2646 ROLLBACK TO WSH_Vendor_Merge;
2647 --
2648 IF l_debug_on THEN
2649 --
2650 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
2651 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2652 --
2653 END IF;
2654 --
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 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2662 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2663 END IF;
2664 --
2665 END IF;
2666 --
2667 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2668 --
2669 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2670 ROLLBACK TO WSH_Vendor_Merge;
2671 --
2672 IF l_debug_on THEN
2673 --
2674 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2675 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2676 --
2677 END IF;
2678 --
2679 IF NOT(wsh_util_core.g_call_fte_load_tender_api) THEN
2680 --
2681 wsh_util_core.Reset_stops_for_load_tender(
2682 p_reset_flags=>true,
2683 x_return_status=>l_return_status);
2684 --
2685 END IF;
2686 --
2687 WHEN OTHERS THEN
2688 --
2689 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2690 ROLLBACK TO WSH_Vendor_Merge;
2691 wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Vendor_Merge');
2692 --
2693 IF l_debug_on THEN
2694 --
2695 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2696 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2697 --
2698 END IF;
2699 --
2700 IF NOT(wsh_util_core.g_call_fte_load_tender_api) THEN
2701 --
2702 wsh_util_core.Reset_stops_for_load_tender(
2703 p_reset_flags=>true,
2704 x_return_status=>l_return_status);
2705 --
2706 END IF;
2707 --
2708 --}
2709 END Vendor_Merge;
2710
2711
2712
2713
2714 --========================================================================
2715 -- PROCEDURE :Vendor_Party_Merge
2716 -- PARAMETERS:
2717 -- P_from_vendor_id Merge from vendor ID
2718 -- P_to_vendor_id Merge to vendor ID
2719 -- P_from_party_id Merge from party ID
2720 -- P_to_party_id Merge to party ID
2721 -- P_from_vendor_site_id Merge from vendor site ID
2722 -- P_to_vendor_site_id Merge to vendor site ID
2723 -- P_from_party_site_id Merge from party site ID
2724 -- P_to_party_site_id Merge to party site ID
2725 -- X_return_status Return status
2726 --
2727 -- COMMENTS
2728 -- This is the API that is called by APXINUPD.rdf. This in turn
2729 -- will call the core Vendor_Merge() procedure to
2730 -- perform all the necessary updates to WSH data.
2731 --
2732 -- HISTORY
2733 -- rlanka 7/27/2005 Created
2734 -- rlanka 8/09/2005 Added new parameter p_calling_mode
2735 --
2736 --========================================================================
2737
2738 PROCEDURE Vendor_Party_Merge
2739 (
2740 p_from_vendor_id IN NUMBER,
2741 p_to_vendor_id IN NUMBER,
2742 p_from_party_id IN NUMBER,
2743 p_to_party_id IN NUMBER,
2744 p_from_vendor_site_id IN NUMBER,
2745 p_to_vendor_site_id IN NUMBER,
2746 p_from_party_site_id IN NUMBER,
2747 p_to_partysite_id IN NUMBER,
2748 p_calling_mode IN VARCHAR2,
2749 x_return_status OUT NOCOPY VARCHAR2,
2750 x_msg_count OUT NOCOPY NUMBER,
2751 x_msg_data OUT NOCOPY VARCHAR2
2752 )
2753 IS
2754 --
2755 l_return_status VARCHAR2(1);
2756 l_debug_on BOOLEAN;
2757 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VENDOR_PARTY_MERGE';
2758 --
2759 l_fromPartyId NUMBER;
2760 l_toPartyId NUMBER;
2761 --
2762 -- Bug 4658824 : Use po_vendors, so we are isolated from any changes
2763 -- that AP makes to their data model
2764 CURSOR c_getParty(p_vendorId IN NUMBER) IS
2765 SELECT party_id
2766 FROM po_vendors
2767 WHERE vendor_id = p_vendorId;
2768
2769 RTV_Exception exception;
2770 l_rec_exists varchar2(1);
2771 BEGIN
2772 --{
2773 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2774 --
2775 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2776 IF l_debug_on IS NULL THEN
2777 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2778 END IF;
2779 --
2780 IF l_debug_on THEN
2781 --
2782 WSH_DEBUG_SV.push(l_module_name);
2783 WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_ID', p_from_vendor_id);
2784 WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_ID', p_to_vendor_id);
2785 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID', p_from_party_id);
2786 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID', p_to_party_id);
2787 WSH_DEBUG_SV.log(l_module_name,'P_FROM_VENDOR_SITE_ID', p_from_vendor_site_id);
2788 WSH_DEBUG_SV.log(l_module_name,'P_TO_VENDOR_SITE_ID', p_to_vendor_site_id);
2789 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_SITE_ID', p_from_party_site_id);
2790 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTYSITE_ID', p_to_partysite_id);
2791 WSH_DEBUG_SV.log(l_module_name,'P_CALLING_MODE', p_calling_mode);
2792 --
2793 END IF;
2794
2795 -- RTV Changes {
2796 -- Vendor/Supplier Merge of Shipping RTV Transactions :Start of changes
2797 -- Removed code that restricts vendor merge of supplier for which there exist RTV transactions in WDD.
2798 --}
2799
2800 --
2801 -- Clear up global table of location IDs
2802 --
2803 g_LocChangeTab.DELETE;
2804 --
2805 -- Bug 4658824 : Now AP passes us the party ID, so we derive
2806 -- it only if the input parameter is NULL.
2807 --
2808 IF p_to_party_id IS NULL THEN
2809 --
2810 OPEN c_getParty(p_to_vendor_id);
2811 FETCH c_getParty INTO l_toPartyId;
2812 IF (c_getParty%NOTFOUND) THEN
2813 Null;
2814 END IF;
2815 CLOSE c_getParty;
2816 --
2817 END IF;
2818 --
2819 IF p_from_party_id IS NULL THEN
2820 --
2821 OPEN c_getParty(p_from_vendor_id);
2822 FETCH c_getParty INTO l_fromPartyId;
2823 IF (c_getParty%NOTFOUND) THEN
2824 Null;
2825 END IF;
2826 CLOSE c_getParty;
2827 --
2828 END IF;
2829 --
2830 IF l_debug_on THEN
2831 WSH_DEBUG_SV.log(l_module_name, 'l_fromPartyId', l_fromPartyId);
2832 WSH_DEBUG_SV.log(l_module_name, 'l_toPartyId', l_toPartyId);
2833 END IF;
2834 --
2835 -- Now call the core Vendor Merge routine to update WSH data
2836 --
2837 WSH_VENDOR_PARTY_MERGE_PKG.Vendor_Merge
2838 (
2839 p_from_id => p_from_vendor_id,
2840 p_to_id => P_to_vendor_id,
2841 p_from_party_id => NVL(p_from_party_id, l_fromPartyId),
2842 p_to_party_id => NVL(p_to_party_id, l_toPartyId),
2843 p_from_site_id => p_from_vendor_site_id,
2844 p_to_site_id => p_to_vendor_site_id,
2845 p_calling_mode => p_calling_mode,
2846 x_return_status => l_return_status
2847 );
2848 --
2849 IF l_debug_on THEN
2850 WSH_DEBUG_SV.logmsg(l_module_name, 'After calling core vendor_merge API', WSH_DEBUG_SV.C_STMT_LEVEL);
2851 WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
2852 END IF;
2853 --
2854 -- For AP, we interpret 'W' as 'S' status.
2855 --
2856 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2857 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2858 ELSE
2859 x_return_status := l_return_status;
2860 END IF;
2861 --
2862 FND_MSG_PUB.Count_And_Get
2863 (
2864 p_count => x_msg_count,
2865 p_data => x_msg_data,
2866 p_encoded => FND_API.G_FALSE
2867 );
2868 --
2869 IF l_debug_on THEN
2870 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
2871 WSH_DEBUG_SV.pop(l_module_name);
2872 END IF;
2873 --}
2874 EXCEPTION
2875 -- RTV Changes {
2876 WHEN RTV_Exception THEN
2877 --
2878 IF ( l_debug_on ) THEN
2879 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RTV_Exception');
2880 END IF;
2881 --
2882 RAISE;
2883 --
2884 --}
2885 --
2886 WHEN FND_API.G_EXC_ERROR THEN
2887 --
2888 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2889 --
2890 IF l_debug_on THEN
2891 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
2892 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2893 END IF;
2894 --
2895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2896 --
2897 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2898 --
2899 IF l_debug_on THEN
2900 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2901 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2902 END IF;
2903 --
2904 WHEN OTHERS THEN
2905 --
2906 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2907 WSH_UTIL_CORE.ADD_MESSAGE(l_return_status, l_module_name);
2908 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_VENDOR_PARTY_MERGE_PKG.VENDOR_PARTY_MERGE',l_module_name);
2909 --
2910 IF l_debug_on THEN
2911 WSH_DEBUG_SV.log(l_module_name, 'Unexpected error', substrb(sqlerrm, 1, 200));
2912 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
2913 WSH_DEBUG_SV.pop(l_module_name);
2914 END IF;
2915 --
2916 END Vendor_Party_Merge;
2917 FUNCTION getTimeStamp RETURN VARCHAR2
2918 IS
2919 BEGIN
2920 RETURN TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS');
2921 EXCEPTION
2922 WHEN OTHERS THEN RAISE;
2923 END getTimeStamp;
2924
2925 -----------------------------------------------------------------------------------------
2926 --
2927 -- PROCEDURE : GET_DELIVERY_HASH
2928 --
2929 -- DESCRIPTION :
2930 -- Get_Delivery_Hash generates new hash value and hash string for
2931 -- deliveries(from wsh_tmp table) which are to be updated with new
2932 -- Customer/Location ids
2933 --
2934 -- PARAMETERS :
2935 -- x_hash_string_tab => Contains array of Hash String for deliveries
2936 -- x_hash_value_tab => Contains array of Hash String for deliveries
2937 -- x_delivery_id_tab => Contains array of delivery ids
2938 -- x_return_status => Return status of API
2939 -----------------------------------------------------------------------------------------
2940
2941 PROCEDURE Get_Delivery_Hash (
2942 x_hash_string_tab OUT NOCOPY g_char_hash_string,
2943 x_hash_value_tab OUT NOCOPY g_number_tbl_type,
2944 x_delivery_id_tab OUT NOCOPY g_number_tbl_type,
2945 x_return_status OUT NOCOPY VARCHAR2 )
2946 IS
2947 CURSOR Get_Tmp_Deliveries
2948 IS
2949 SELECT to_number(Column1) delivery_id, Column3
2950 FROM Wsh_Tmp;
2951
2952 l_grp_attr_tab_type WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
2953 l_action_code VARCHAR2(30);
2954 l_return_status VARCHAR2(1);
2955 l_hash_count NUMBER;
2956 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Delivery_Hash';
2957 Update_Hash_Exp EXCEPTION;
2958
2959 --
2960 l_debug_on BOOLEAN;
2961 --
2962 BEGIN
2963 --
2964 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2965 --
2966 IF l_debug_on IS NULL
2967 THEN
2968 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2969 END IF;
2970 --
2971 IF l_debug_on THEN
2972 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Get_Delivery_Hash()+' || getTimeStamp );
2973 END IF;
2974 --
2975
2976 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2977 l_hash_count := 0;
2978
2979 FOR i IN Get_Tmp_Deliveries
2980 LOOP
2981 -- {
2982 IF ( NOT G_DELIVERY_ID.EXISTS(i.delivery_id) )
2983 THEN
2984 -- {
2985 l_grp_attr_tab_type(1).Entity_Type := 'DELIVERY_DETAIL';
2986 l_grp_attr_tab_type(1).Entity_Id := i.Column3;
2987
2988 WSH_DELIVERY_AUTOCREATE.Create_Hash (
2989 p_grouping_attributes => l_grp_attr_tab_type,
2990 p_group_by_header => 'N',
2991 p_action_code => l_action_code,
2992 x_return_status => l_return_status );
2993
2994 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
2995 WSH_UTIL_CORE.G_RET_STS_WARNING) )
2996 THEN
2997 --
2998 IF ( l_debug_on ) THEN
2999 WSH_DEBUG_SV.logmsg(l_module_name,'Error returned from API Create_Hash');
3000 END IF;
3001 --
3002 RAISE Update_Hash_Exp;
3003 END IF;
3004
3005 l_hash_count := l_hash_count + 1;
3006 x_hash_string_tab(l_hash_count) := l_grp_attr_tab_type(1).l1_hash_string;
3007 x_hash_value_tab(l_hash_count) := l_grp_attr_tab_type(1).l1_hash_value;
3008 x_delivery_id_tab(l_hash_count) := i.delivery_id;
3009 G_DELIVERY_ID(i.delivery_id) := i.delivery_id;
3010 -- }
3011 END IF;
3012 -- }
3013 END LOOP;
3014
3015 --
3016 IF l_debug_on THEN
3017 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Get_Delivery_Hash()+' || getTimeStamp );
3018 END IF;
3019 --
3020 EXCEPTION
3021 WHEN Update_Hash_Exp THEN
3022 x_return_status := l_return_status;
3023 --
3024 IF ( l_debug_on ) THEN
3025 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Get_Delivery_Hash()+ Update_Hash_Exp - ' || getTimeStamp );
3026 END IF;
3027 --
3028 WHEN OTHERS THEN
3029 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3030 --
3031 IF ( l_debug_on ) THEN
3032 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Get_Delivery_Hash()+ Others - ' || getTimeStamp );
3033 WSH_DEBUG_SV.logmsg(l_module_name,'Error Mesg : ' || SQLERRM );
3034 END IF;
3035 --
3036 END Get_Delivery_Hash;
3037
3038 --
3039 -----------------------------------------------------------------------------------------
3040 -- PROCEDURE : ADJUST_WEIGHT_VOLUME
3041 -- PARAMETERS :
3042 -- p_entity_type CONT - While unassigning from Containers
3043 -- DEL-CONT - While unassigning from Deliveries/LPN's
3044 -- TRIP-STOP - While unassigning from Stop's
3045 -- p_delivery_detail array of delivery detail id
3046 -- p_parent_delivery_detail array of parent delivery detail id
3047 -- p_delivery_id array of delivery id
3048 -- p_delivery_leg_id array of delivery leg id
3049 -- p_net_weight array of net weight
3050 -- p_gross_weight array of gross weight
3051 -- p_volume array of volume
3052 -- x_return_status Returns the status of call
3053 --
3054 -- COMMENT :
3055 -- Code is similar to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_From_Cont
3056 -- when p_entity_type is 'CONT'
3057 -- Code is similar to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_From_Delivery
3058 -- when p_entity_type is 'DEL-CONT'
3059 -----------------------------------------------------------------------------------------
3060
3061 PROCEDURE Adjust_Weight_Volume (
3062 p_entity_type IN VARCHAR2,
3063 p_delivery_detail IN g_number_tbl_type,
3064 p_parent_delivery_detail IN g_number_tbl_type,
3065 p_delivery_id IN g_number_tbl_type,
3066 p_delivery_leg_id IN g_number_tbl_type,
3067 p_net_weight IN g_number_tbl_type,
3068 p_gross_weight IN g_number_tbl_type,
3069 p_volume IN g_number_tbl_type,
3070 x_return_status OUT NOCOPY VARCHAR2 )
3071 IS
3072 l_del_tab WSH_UTIL_CORE.Id_Tab_Type;
3073 l_return_status VARCHAR2(10);
3074
3075 Weight_Volume_Exp EXCEPTION;
3076 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Adjust_Weight_Volume';
3077 --
3078 l_debug_on BOOLEAN;
3079 --
3080 BEGIN
3081
3082 x_return_status := FND_API.G_RET_STS_SUCCESS;
3083 --
3084 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3085 --
3086 IF l_debug_on IS NULL THEN
3087 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3088 END IF;
3089
3090 --
3091 IF l_debug_on THEN
3092 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Weight_Volume()+' || getTimeStamp );
3093 END IF;
3094
3095 IF ( p_entity_type in ( 'CONT', 'DEL-CONT' ) )
3096 THEN
3097 -- { Entity type
3098 -- Weight/Volume adjustments
3099 FOR wvCnt IN p_delivery_detail.FIRST..p_delivery_detail.LAST
3100 LOOP
3101 -- { W/V adjustment Loop
3102 -- Call WV API, If
3103 -- 1. CONT
3104 -- When Unassigning from container(i.e., delivery detail is assigned to
3105 -- container but not assigned to a delivery.
3106 -- OR
3107 -- 2. DEL-CONT
3108 -- When Unassigning from delivery
3109 IF ( ( p_entity_type = 'CONT' AND
3110 p_delivery_id(wvCnt) IS NULL AND
3111 p_parent_delivery_detail(wvCnt) IS NOT NULL ) OR
3112 ( p_entity_type = 'DEL-CONT' ) )
3113 THEN
3114 -- {
3115 l_return_status := NULL;
3116
3117 WSH_WV_UTILS.DD_WV_Post_Process (
3118 p_delivery_detail_id => p_delivery_detail(wvCnt),
3119 p_diff_gross_wt => -1 * p_gross_weight(wvCnt),
3120 p_diff_net_wt => -1 * p_net_weight(wvCnt),
3121 p_diff_volume => -1 * p_volume(wvCnt),
3122 p_diff_fill_volume => -1 * p_volume(wvCnt),
3123 p_check_for_empty => 'Y',
3124 x_return_status => l_return_status );
3125
3126 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
3127 WSH_UTIL_CORE.G_RET_STS_WARNING) )
3128 THEN
3129 --
3130 IF ( l_debug_on ) THEN
3131 WSH_DEBUG_SV.logmsg(l_module_name,'API WSH_WV_UTILS.DD_WV_Post_Process returned error');
3132 END IF;
3133 --
3134 RAISE Weight_Volume_Exp;
3135 END IF;
3136 -- }
3137 END IF;
3138 -- } W/V adjustment Loop
3139 END LOOP;
3140 -- } Entity Type
3141 ELSIF ( p_entity_type = 'TRIP-STOP' )
3142 THEN
3143 -- Calling WV API, when unassigning delivery from a trip
3144 FOR wvCnt IN p_delivery_id.FIRST..p_delivery_id.LAST
3145 LOOP
3146 l_return_status := NULL;
3147
3148 WSH_WV_UTILS.Del_WV_Post_Process(
3149 p_delivery_id => p_delivery_id(wvCnt),
3150 p_diff_gross_wt => -1 * p_gross_weight(wvCnt),
3151 p_diff_net_wt => -1 * p_net_weight(wvCnt),
3152 p_diff_volume => -1 * p_volume(wvCnt),
3153 p_check_for_empty => 'Y',
3154 p_leg_id => p_delivery_leg_id(wvCnt),
3155 x_return_status => l_return_status);
3156
3157 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
3158 WSH_UTIL_CORE.G_RET_STS_WARNING) )
3159 THEN
3160 --
3161 IF ( l_debug_on ) THEN
3162 WSH_DEBUG_SV.logmsg(l_module_name,'API WSH_WV_UTILS.Del_WV_Post_Process returned error');
3163 END IF;
3164 --
3165 RAISE Weight_Volume_Exp;
3166 END IF;
3167
3168 END LOOP;
3169 END IF;
3170
3171 --
3172 IF ( l_debug_on ) THEN
3173 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Weight_Volume()+' || getTimeStamp );
3174 END IF;
3175 --
3176
3177 EXCEPTION
3178 WHEN Weight_Volume_Exp THEN
3179 x_return_status := l_return_status;
3180 --
3181 IF ( l_debug_on ) THEN
3182 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Weight_Volume()+ Weight_Volume_Exp - ' || getTimeStamp );
3183 END IF;
3184 --
3185
3186 WHEN OTHERS THEN
3187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3188 --
3189 IF ( l_debug_on ) THEN
3190 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Weight_Volume()+ Others - ' || getTimeStamp);
3191 WSH_DEBUG_SV.logmsg(l_module_name,'Error Mesg : ' || sqlerrm );
3192 END IF;
3193 --
3194 END Adjust_Weight_Volume;
3195 -- ===============================================================================
3196 -- PROCEDURE : ADJUST_PARENT_WV
3197 -- PARAMETERS :
3198 -- p_entity_type CONT - While unassigning from Containers
3199 -- DEL-CONT - While unassigning from Deliveries/LPN's
3200 -- p_delivery_detail array of delivery detail id
3201 -- p_parent_delivery_detail array of parent delivery detail id
3202 -- p_delivery_id array of delivery id
3203 -- p_inventory_item_id array inventory item id
3204 -- p_organization_id array of organization id
3205 -- p_weight_uom array of weight UOM code
3206 -- p_volume_uom array of volume UOM code
3207 -- x_return_status Returns the status of call
3208 --
3209 -- COMMENT :
3210 -- Code is similar to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_From_Cont
3211 -- when p_entity_type is 'CONT'
3212 -- Code is similar to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_From_Delivery
3213 -- when p_entity_type is 'DEL-CONT'
3214 -- ===============================================================================
3215 PROCEDURE Adjust_Parent_WV (
3216 p_entity_type IN VARCHAR2,
3217 p_delivery_detail IN g_number_tbl_type,
3218 p_parent_delivery_detail IN g_number_tbl_type,
3219 p_delivery_id IN g_number_tbl_type,
3220 p_inventory_item_id IN g_number_tbl_type,
3221 p_organization_id IN g_number_tbl_type,
3222 p_weight_uom IN g_char_tbl_type,
3223 p_volume_uom IN g_char_tbl_type,
3224 x_return_status OUT NOCOPY VARCHAR2 )
3225 IS
3226 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
3227 l_return_status VARCHAR2(10);
3228
3229 Weight_Volume_Exp EXCEPTION;
3230 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Adjust_Parent_WV';
3231 --
3232 l_debug_on BOOLEAN;
3233 --
3234 BEGIN
3235
3236 x_return_status := FND_API.G_RET_STS_SUCCESS;
3237 --
3238 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3239 --
3240 IF l_debug_on IS NULL
3241 THEN
3242 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3243 END IF;
3244 --
3245 --
3246 IF l_debug_on THEN
3247 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Parent_WV()+' || getTimeStamp );
3248 END IF;
3249 --
3250
3251 -- Weight/Volume adjustments
3252 FOR wvCnt IN p_delivery_detail.FIRST..p_delivery_detail.LAST
3253 LOOP
3254 -- { W/V adjustment Loop
3255 -- Call WV API, If
3256 -- 1. CONT
3257 -- When Unassigning from container(i.e., delivery detail is assigned to
3258 -- container but not assigned to a delivery.
3259 -- OR
3260 -- 2. DEL-CONT
3261 -- When Unassigning from delivery
3262 IF ( ( p_entity_type = 'CONT' AND
3263 p_delivery_id(wvCnt) IS NULL AND
3264 p_parent_delivery_detail(wvCnt) IS NOT NULL ) OR
3265 ( p_entity_type = 'DEL-CONT' ) )
3266 THEN
3267 -- {
3268 l_return_status := NULL;
3269
3270 IF ( NOT G_PARAM_INFO_TAB.EXISTS(p_organization_id(wvCnt)) )
3271 THEN
3272 l_return_status := NULL;
3273
3274 WSH_SHIPPING_PARAMS_PVT.Get(
3275 p_organization_id => p_organization_id(wvCnt),
3276 x_param_info => l_param_info,
3277 x_return_status => l_return_status);
3278
3279 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3280 --
3281 IF ( l_debug_on ) THEN
3282 WSH_DEBUG_SV.logmsg(l_module_name,'API WSH_SHIPPING_PARAMS_PVT.Get returned error');
3283 END IF;
3284 --
3285 RAISE Weight_Volume_Exp;
3286 END IF;
3287
3288 G_PARAM_INFO_TAB(p_organization_id(wvCnt)) := l_param_info;
3289 END IF;
3290
3291 IF ( G_PARAM_INFO_TAB(p_organization_id(wvCnt)).Percent_Fill_Basis_Flag = 'Q' AND
3292 ( ( p_entity_type = 'DEL-CONT' AND p_parent_delivery_detail(wvCnt) IS NOT NULL ) OR
3293 ( p_entity_type = 'CONT' ) ) )
3294 THEN
3295 l_return_status := NULL;
3296
3297 WSH_WV_UTILS.Adjust_Parent_WV(
3298 p_entity_type => 'CONTAINER',
3299 p_entity_id => p_parent_delivery_detail(wvCnt),
3300 p_gross_weight => 0,
3301 p_net_weight => 0,
3302 p_volume => 0,
3303 p_filled_volume => 0,
3304 p_wt_uom_code => p_weight_uom(wvCnt),
3305 p_vol_uom_code => p_volume_uom(wvCnt),
3306 p_inv_item_id => p_inventory_item_id(wvCnt),
3307 x_return_status => l_return_status);
3308
3309 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
3310 WSH_UTIL_CORE.G_RET_STS_WARNING) )
3311 THEN
3312 --
3313 IF ( l_debug_on ) THEN
3314 WSH_DEBUG_SV.logmsg(l_module_name,'API WSH_WV_UTILS.Adjust_Parent_WV returned error');
3315 END IF;
3316 --
3317 RAISE Weight_Volume_Exp;
3318 END IF;
3319 END IF;
3320 -- }
3321 END IF;
3322 -- } W/V adjustment Loop
3323 END LOOP;
3324
3325 --
3326 IF ( l_debug_on ) THEN
3327 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Parent_WV()+' || getTimeStamp );
3328 END IF;
3329 --
3330
3331 EXCEPTION
3332 WHEN Weight_Volume_Exp THEN
3333 x_return_status := l_return_status;
3334 --
3335 IF ( l_debug_on ) THEN
3336 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Parent_WV()+ Weight_Volume_Exp - ' || getTimeStamp );
3337 END IF;
3338 --
3339
3340 WHEN OTHERS THEN
3341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3342 --
3343 IF ( l_debug_on ) THEN
3344 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Adjust_Parent_WV()+ Others - ' || getTimeStamp );
3345 WSH_DEBUG_SV.logmsg(l_module_name,'Error Mesg : ' || sqlerrm );
3346 END IF;
3347 --
3348 END Adjust_Parent_WV;
3349
3350 --========================================================================
3351 -- PROCEDURE : Merge_RTV_Transactions
3352 -- PARAMETERS:
3353 -- P_from_vendor_id Merge from Vendor Id
3354 -- P_to_vendor_id Merge to Vendor Id
3355 -- P_from_site_id Merge from vendor site ID
3356 -- P_to_site_id Merge to vendor site ID
3357 -- X_return_status Return status
3358 --
3359 -- COMMENT : This is the procedure tp merge Shipping RTV Transactions
3360 -- during vendor merge.
3361 -- This will be called from Vendor_Merge() API
3362 --========================================================================
3363
3364 PROCEDURE Merge_RTV_Transactions (
3365 p_from_vendor_id IN NUMBER,
3366 p_to_vendor_id IN NUMBER,
3367 p_from_site_id IN NUMBER,
3368 p_to_site_id IN NUMBER,
3369 x_return_status OUT NOCOPY VARCHAR2 )
3370 IS
3371
3372 CURSOR get_loc_for_site ( c_vendor_id NUMBER, c_vendor_site NUMBER) IS
3373 SELECT pvs.location_id, hzp.party_site_use_id
3374 FROM po_vendors pov,
3375 po_vendor_sites pvs,
3376 hz_locations hz,
3377 hz_party_site_uses hzp
3378 WHERE pov.vendor_id = pvs.vendor_id
3379 AND pvs.location_id = hz.location_id
3380 AND pov.vendor_id = c_vendor_id
3381 AND (c_vendor_site is null or pvs.vendor_site_id = c_vendor_site)
3382 AND hzp.party_site_id = pvs.party_site_id
3383 AND hzp.site_use_type = 'PURCHASING';
3384
3385 CURSOR get_unshipped_details ( c_from_vendor_id NUMBER,
3386 c_party_site_id NUMBER,
3387 c_location_id NUMBER ) IS
3388 SELECT wdd.rowid,wda.rowid, wdd.delivery_detail_id,
3389 wdd.inventory_item_id,wdd.organization_id,
3390 wdd.gross_weight, wdd.net_weight, wdd.volume,
3391 wdd.weight_uom_code,wdd.volume_uom_code,
3392 wda.parent_delivery_detail_id, wda.delivery_id, wts.stop_id
3393 FROM wsh_delivery_assignments wda,
3394 wsh_delivery_details wdd,
3395 wsh_new_deliveries wnd,
3396 wsh_delivery_legs wdl,
3397 wsh_trip_stops wts
3398 WHERE wts.stop_id (+) = wdl.drop_off_stop_id
3399 AND wdl.delivery_id (+) = wnd.delivery_id
3400 AND nvl(wnd.status_code, 'OP') = 'OP'
3401 AND wnd.delivery_id (+) = wda.delivery_id
3402 AND wda.delivery_detail_id = wdd.delivery_detail_id
3403 AND wdd.source_code = 'RTV'
3404 AND wdd.released_status = 'X'
3405 AND nvl(wdd.consignee_flag, 'C') = 'V'
3406 AND wdd.customer_id = c_from_vendor_id
3407 AND wdd.ship_to_site_use_id = c_party_site_id
3408 AND wdd.ship_to_location_id = c_location_id
3409 FOR UPDATE OF Wdd.Delivery_Detail_Id, Wda.Delivery_Detail_Id,
3410 Wnd.Delivery_Id, Wts.Stop_Id NOWAIT;
3411
3412 CURSOR get_shipped_details ( c_from_vendor_id NUMBER,
3413 c_party_site_id NUMBER,
3414 c_location_id NUMBER ) IS
3415 SELECT wda.delivery_id
3416 FROM wsh_delivery_assignments wda,
3417 wsh_delivery_details wdd,
3418 wsh_new_deliveries wnd
3419 WHERE wnd.status_code IN ( 'CO', 'IT', 'CL' )
3420 AND wnd.delivery_id = wda.delivery_id
3421 AND wda.delivery_detail_id = wdd.delivery_detail_id
3422 AND wdd.source_code = 'RTV'
3423 AND wdd.released_status = 'C'
3424 AND nvl(wdd.consignee_flag, 'C') = 'V'
3425 AND wdd.customer_id = c_from_vendor_id
3426 AND wdd.ship_to_site_use_id = c_party_site_id
3427 AND wdd.ship_to_location_id = c_location_id
3428 FOR UPDATE OF WDD.DELIVERY_DETAIL_ID NOWAIT;
3429
3430 CURSOR get_unassign_details ( c_to_vendor_id NUMBER,
3431 c_to_party_site_use_id NUMBER,
3432 c_to_location_id NUMBER ) IS
3433 SELECT wda.rowid, wda.delivery_id, wnd.name,wda.delivery_detail_id,
3434 wda.parent_delivery_detail_id,
3435 wdd.inventory_item_id,wdd.organization_id,
3436 wdd.gross_weight, wdd.net_weight, wdd.volume,
3437 wdd.weight_uom_code,wdd.volume_uom_code
3438 FROM wsh_delivery_assignments wda,
3439 wsh_delivery_details wdd,
3440 wsh_new_deliveries wnd,
3441 wsh_tmp tmp
3442 WHERE wdd.container_flag = 'N'
3443 AND wdd.source_code = 'RTV'
3444 AND wdd.released_status = 'X'
3445 AND nvl(wdd.consignee_flag, 'C') = 'V'
3446 AND wdd.customer_id = c_to_vendor_id
3447 AND wdd.ship_to_site_use_id = c_to_party_site_use_id
3448 AND wdd.ship_to_location_id = c_to_location_id
3449 AND wdd.delivery_detail_id = wda.delivery_detail_id
3450 AND wda.delivery_id = wnd.delivery_id
3451 AND wnd.ultimate_dropoff_location_id <> c_to_location_id
3452 AND wnd.status_code = 'OP'
3453 AND wnd.delivery_id = tmp.column1
3454 AND EXISTS
3455 ( SELECT 'x'
3456 FROM wsh_delivery_assignments assgn,
3457 Wsh_delivery_details det
3458 WHERE det.container_flag = 'N'
3459 AND det.delivery_detail_id = assgn.delivery_detail_id
3460 AND assgn.delivery_id = wnd.delivery_id
3461 AND det.ship_to_location_id = wnd.ultimate_dropoff_location_id )
3462 FOR UPDATE OF WDA.DELIVERY_DETAIL_ID NOWAIT;
3463
3464 CURSOR get_delivery_containers IS
3465 SELECT wdd.rowid
3466 FROM wsh_delivery_details wdd,
3467 wsh_delivery_assignments wda,
3468 wsh_tmp tmp
3469 WHERE wdd.container_flag = 'Y'
3470 AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
3471 AND wda.parent_delivery_detail_id IS NOT NULL
3472 AND wda.delivery_id = tmp.column1
3473 FOR UPDATE OF wdd.delivery_detail_id NOWAIT;
3474
3475 CURSOR get_unassign_stops ( c_to_location_id NUMBER) IS
3476 SELECT wdl.rowid, tmp.rowid, wnd.delivery_id, wdl.delivery_leg_id, wts.trip_id,
3477 wts.stop_id, wnd.gross_weight, wnd.net_weight, wnd.volume
3478 FROM wsh_delivery_legs wdl,
3479 wsh_new_deliveries wnd,
3480 Wsh_trip_stops wts,
3481 wsh_tmp tmp
3482 WHERE wnd.ultimate_dropoff_location_id = c_to_location_id
3483 AND wts.stop_id = wdl.drop_off_stop_id
3484 AND wdl.delivery_id = wnd.delivery_id
3485 AND wnd.delivery_id = tmp.column1
3486 AND EXISTS
3487 ( SELECT 'x'
3488 FROM wsh_new_deliveries del,
3489 wsh_delivery_legs legs
3490 WHERE del.ultimate_dropoff_location_id <> c_to_location_id
3491 AND del.delivery_id = legs.delivery_id
3492 AND legs.drop_off_stop_id = wdl.drop_off_stop_id );
3493
3494 CURSOR get_empty_deliveries ( c_vendor_id NUMBER,
3495 c_location_id NUMBER ) IS
3496 SELECT wnd.delivery_id,wnd.initial_pickup_location_id,wnd.customer_id,
3497 wnd.intmed_ship_to_location_id,wnd.fob_code,wnd.freight_terms_code,
3498 wnd.ship_method_code,wnd.carrier_id,wnd.source_header_id,wnd.organization_id,
3499 wnd.initial_pickup_date,wnd.ultimate_dropoff_date,wnd.ignore_for_planning,
3500 wnd.shipment_direction,wnd.shipping_control,wnd.party_id,wnd.client_id
3501 FROM wsh_new_deliveries wnd
3502 WHERE nvl(Wnd.Customer_Id, c_vendor_id) = c_vendor_id
3503 AND nvl(wnd.consignee_flag, 'C') = 'V'
3504 AND Wnd.Ultimate_Dropoff_Location_Id = c_location_id
3505 AND Wnd.Status_Code = 'OP'
3506 AND NOT EXISTS
3507 ( SELECT 'x'
3508 FROM wsh_delivery_assignments wda
3509 WHERE wda.delivery_id = wnd.delivery_id )
3510 FOR UPDATE NOWAIT;
3511
3512 CURSOR c_check_vendor_wdd (c_vendor_id NUMBER) IS
3513 select 'x'
3514 from dual
3515 where exists (
3516 select 1
3517 from wsh_delivery_details
3518 where consignee_flag = 'V'
3519 and customer_id = c_vendor_id);
3520
3521 CURSOR c_check_vendor_wnd (c_vendor_id NUMBER) IS
3522 select 'x'
3523 from dual
3524 where exists (
3525 select 1
3526 from wsh_new_deliveries
3527 where consignee_flag = 'V'
3528 and customer_id = c_vendor_id);
3529
3530 l_from_location_id NUMBER;
3531 l_to_location_id NUMBER;
3532 l_from_party_site_use_id NUMBER;
3533 l_to_party_site_use_id NUMBER;
3534
3535 l_delivery_detail_id g_number_tbl_type;
3536 l_delivery_leg_id g_number_tbl_type;
3537 l_trip_id g_number_tbl_type;
3538 l_trip_stop_id g_number_tbl_type;
3539 l_delivery_name g_char_tbl_type;
3540 l_wnd_customer_id g_number_tbl_type;
3541 l_parent_delivery_detail_id g_number_tbl_type;
3542 l_inventory_item_id g_number_tbl_type;
3543 l_organization_id g_number_tbl_type;
3544 l_stop_id g_number_tbl_type;
3545 l_net_weight g_number_tbl_type;
3546 l_gross_weight g_number_tbl_type;
3547 l_volume g_number_tbl_type;
3548 l_weight_uom g_char_tbl_type;
3549 l_volume_uom g_char_tbl_type;
3550 l_wdd_rowid wsh_util_core.COLUMN_TAB_TYPE;
3551 l_wda_rowid wsh_util_core.COLUMN_TAB_TYPE;
3552 l_wdl_rowid wsh_util_core.COLUMN_TAB_TYPE;
3553 l_tmp_rowid wsh_util_core.COLUMN_TAB_TYPE;
3554 l_wnd_rowid wsh_util_core.COLUMN_TAB_TYPE;
3555 l_delivery_id g_number_tbl_type;
3556 l_initial_pickup_location_id g_number_tbl_type;
3557 l_customer_id g_number_tbl_type;
3558 l_intmed_ship_to_location_id g_number_tbl_type;
3559 l_fob_code g_char_tbl_type;
3560 l_freight_terms_code g_char_tbl_type;
3561 l_ship_method_code g_char_tbl_type;
3562 l_carrier_id g_number_tbl_type;
3563 l_source_header_id g_number_tbl_type;
3564 l_initial_pickup_date wsh_util_core.date_tab_type;
3565 l_ultimate_dropoff_date wsh_util_core.date_tab_type;
3566 l_ignore_for_planning g_char_tbl_type;
3567 l_shipment_direction g_char_tbl_type;
3568 l_shipping_control g_char_tbl_type;
3569 l_party_id g_number_tbl_type;
3570 l_client_id g_number_tbl_type;
3571
3572 l_grp_attr_tab WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
3573 l_dummyIdTab g_number_tbl_type;
3574 l_rec_exists varchar2(1);
3575 l_no_rtv_del_details NUMBER := 0;
3576 l_exception_id NUMBER;
3577 l_msg_count NUMBER;
3578 l_msg_data VARCHAR2(32767);
3579 l_return_status VARCHAR2(10);
3580 l_message_text VARCHAR2(32767);
3581 l_message_name VARCHAR2(50);
3582 l_tmp_cnt NUMBER;
3583 l_hash_value_tab g_number_tbl_type;
3584 l_hash_string_tab g_char_hash_string;
3585 l_delivery_id_tab g_number_tbl_type;
3586 l_sql_count NUMBER;
3587
3588 l_debug_on BOOLEAN;
3589 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Merge_RTV_Transactions';
3590
3591 BEGIN
3592
3593 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3594 --
3595
3596 IF l_debug_on IS NULL THEN
3597 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3598 END IF;
3599 --
3600 IF l_debug_on THEN
3601 WSH_DEBUG_SV.push(l_module_name);
3602 WSH_DEBUG_SV.log(l_module_name, 'p_from_vendor_id', p_from_vendor_id);
3603 WSH_DEBUG_SV.log(l_module_name, 'p_to_vendor_id', p_to_vendor_id);
3604 WSH_DEBUG_SV.log(l_module_name, 'p_from_site_id', p_from_site_id);
3605 WSH_DEBUG_SV.log(l_module_name, 'p_to_site_id', p_to_site_id);
3606 END IF;
3607 --
3608 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3609 OPEN get_loc_for_site ( p_from_vendor_id , p_from_site_id );
3610 FETCH get_loc_for_site INTO l_from_location_id, l_from_party_site_use_id;
3611 --
3612 IF get_loc_for_site%NOTFOUND THEN
3613 IF l_debug_on THEN
3614 WSH_DEBUG_SV.log(l_module_name, 'From vendor details not found' );
3615 CLOSE get_loc_for_site;
3616 raise FND_API.G_EXC_ERROR;
3617 END IF;
3618 END IF;
3619 --
3620 CLOSE get_loc_for_site;
3621 OPEN get_loc_for_site ( p_to_vendor_id , p_to_site_id );
3622 FETCH get_loc_for_site INTO l_to_location_id, l_to_party_site_use_id;
3623 --
3624 IF get_loc_for_site%NOTFOUND THEN
3625 IF l_debug_on THEN
3626 WSH_DEBUG_SV.log(l_module_name, 'To vendor details not found');
3627 END IF;
3628 l_to_party_site_use_id := l_from_party_site_use_id;
3629 l_to_location_id := l_from_location_id;
3630 END IF;
3631 --
3632 CLOSE get_loc_for_site;
3633
3634 IF l_debug_on THEN
3635 WSH_DEBUG_SV.logmsg(l_module_name,'Performing Shipping Vendor check');
3636 END IF;
3637 open c_check_vendor_wdd(p_from_vendor_id);
3638 fetch c_check_vendor_wdd into l_rec_exists;
3639 if (c_check_vendor_wdd%NOTFOUND) then
3640 IF l_debug_on THEN
3641 WSH_DEBUG_SV.logmsg(l_module_name,' There exist no RTV delivery detail records in Shipping.');
3642 END IF;
3643 l_no_rtv_del_details := 1;
3644 end if;
3645 close c_check_vendor_wdd;
3646
3647 open c_check_vendor_wnd(p_from_vendor_id);
3648 fetch c_check_vendor_wnd into l_rec_exists;
3649 if (c_check_vendor_wnd%NOTFOUND) then
3650 IF l_debug_on THEN
3651 WSH_DEBUG_SV.logmsg(l_module_name,' There exist no RTV delivery records in Shipping.');
3652 END IF;
3653 IF l_no_rtv_del_details = 1 THEN
3654 close c_check_vendor_wnd;
3655 WSH_DEBUG_SV.pop(l_module_name);
3656 RETURN;
3657 END IF ;
3658 end if;
3659 close c_check_vendor_wnd;
3660
3661 IF l_debug_on THEN
3662 WSH_DEBUG_SV.log(l_module_name, 'l_from_location_id', l_from_location_id);
3663 WSH_DEBUG_SV.log(l_module_name, 'l_from_party_site_use_id', l_from_party_site_use_id);
3664 WSH_DEBUG_SV.log(l_module_name, 'l_to_location_id', l_to_location_id);
3665 WSH_DEBUG_SV.log(l_module_name, 'l_to_party_site_use_id', l_to_party_site_use_id);
3666 WSH_DEBUG_SV.logmsg(l_module_name, 'Start processing for Shipped Delivery Details');
3667 END IF;
3668
3669 OPEN get_shipped_details(p_from_vendor_id, l_from_party_site_use_id, l_from_location_id);
3670 LOOP
3671 FETCH get_shipped_details BULK COLLECT INTO l_delivery_id LIMIT G_LIMIT;
3672 EXIT WHEN l_delivery_id.count <= 0;
3673
3674 -- Updating using delivery_id will make sure that shippable line as
3675 -- well as container records in WDD will be updated.
3676 IF l_debug_on THEN
3677 WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id.count', l_delivery_id.count);
3678 END IF;
3679 FORALL I IN l_delivery_id.first..l_delivery_id.last
3680 UPDATE wsh_delivery_details
3681 SET customer_id = decode(customer_id,
3682 p_from_vendor_id, p_to_vendor_id,
3683 customer_id),
3684 ship_to_site_use_id = decode(container_flag,
3685 'N', l_to_party_site_use_id,
3686 ship_to_site_use_id),
3687 last_update_date = SYSDATE,
3688 last_updated_by = fnd_global.user_id,
3689 last_update_login = fnd_global.conc_login_id,
3690 program_application_id = fnd_global.prog_appl_id,
3691 program_id = fnd_global.conc_program_id,
3692 program_update_date = SYSDATE
3693 WHERE delivery_detail_id IN
3694 ( SELECT wda.delivery_detail_id
3695 FROM wsh_delivery_assignments wda
3696 WHERE delivery_id = l_delivery_id(i) );
3697
3698 l_sql_count := SQL%rowcount;
3699 IF l_debug_on THEN
3700 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
3701 END IF;
3702 EXIT WHEN l_delivery_id.count < G_LIMIT;
3703 END LOOP;
3704 CLOSE get_shipped_details;
3705 l_delivery_id.delete;
3706 IF l_debug_on THEN
3707 WSH_DEBUG_SV.log(l_module_name, 'End of processing for Shipped Delivery Details');
3708 END IF;
3709
3710 IF l_debug_on THEN
3711 WSH_DEBUG_SV.log(l_module_name, 'Start processing for Unshipped Delivery Details');
3712 END IF;
3713 OPEN Get_unshipped_details (p_from_vendor_id, l_from_party_site_use_id,
3714 l_from_location_id);
3715
3716 LOOP
3717 FETCH get_unshipped_details
3718 BULK COLLECT INTO l_wdd_rowid,l_wda_rowid, l_delivery_detail_id ,
3719 l_inventory_Item_Id,l_organization_id,
3720 l_gross_weight, l_net_weight, l_volume,l_weight_uom,
3721 l_volume_uom, l_parent_delivery_detail_id ,
3722 l_delivery_id, l_stop_id LIMIT G_LIMIT;
3723 EXIT WHEN l_wdd_rowid.count <= 0;
3724
3725 -- Updating all unshipped delivery details.
3726 IF l_debug_on THEN
3727 WSH_DEBUG_SV.log(l_module_name, 'l_wdd_rowid.count', l_wdd_rowid.count);
3728 END IF;
3729
3730 FORALL I IN l_wdd_rowid.first..l_wdd_rowid.last
3731 UPDATE wsh_delivery_details
3732 SET customer_id = decode(customer_id,
3733 p_from_vendor_id, p_to_vendor_id,
3734 customer_id),
3735 ship_to_site_use_id = l_to_party_site_use_id,
3736 ship_to_location_id = l_to_location_id,
3737 last_update_date = SYSDATE,
3738 last_updated_by = fnd_global.user_id,
3739 last_update_login = fnd_global.conc_login_id,
3740 program_application_id = fnd_global.prog_appl_id,
3741 program_id = fnd_global.conc_program_id,
3742 program_update_date = SYSDATE
3743 WHERE ROWID = l_wdd_rowid(i);
3744 l_sql_count := SQL%rowcount;
3745 IF l_debug_on THEN
3746 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
3747 END IF;
3748 -- Unpack delivery lines if it is packed but not yet assigned to delivery
3749
3750 -- Start:
3751 IF l_debug_on THEN
3752 WSH_DEBUG_SV.log(l_module_name, 'Unpacking unshipped delivery details which are not yet assigned to a delivery');
3753 END IF;
3754
3755 Adjust_Weight_Volume (
3756 p_entity_type => 'CONT',
3757 p_delivery_detail =>l_delivery_detail_id,
3758 p_parent_delivery_detail => l_parent_delivery_detail_id,
3759 p_delivery_id => l_delivery_id,
3760 p_delivery_leg_id => l_dummyIdTab,
3761 p_net_weight =>l_net_weight,
3762 p_gross_weight =>l_gross_weight,
3763 p_volume =>l_volume,
3764 x_return_status =>l_return_status );
3765
3766
3767 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
3768 WSH_UTIL_CORE.G_RET_STS_WARNING) )
3769 THEN
3770 --
3771 IF ( l_debug_on ) THEN
3772 WSH_DEBUG_SV.logmsg(l_module_name,'Error returned from API Adjust_Weight_Volume');
3773 END IF;
3774 RAISE FND_API.G_EXC_ERROR;
3775 --
3776 END IF;
3777
3778 FORALL I IN l_wda_rowid.first..l_wda_rowid.last
3779 UPDATE wsh_delivery_assignments
3780 SET parent_delivery_detail_id = NULL,
3781 last_update_date = SYSDATE,
3782 last_updated_by = fnd_global.user_id,
3783 last_update_login = fnd_global.conc_login_id,
3784 program_application_id = fnd_global.prog_appl_id,
3785 program_id = fnd_global.conc_program_id,
3786 program_update_date = SYSDATE
3787 WHERE ROWID = l_wda_rowid(i)
3788 AND parent_delivery_detail_id IS NOT NULL
3789 AND delivery_id IS NULL;
3790
3791 l_sql_count := SQL%rowcount;
3792 IF l_debug_on THEN
3793 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_assignments. Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
3794 END IF;
3795
3796 Adjust_Parent_WV (
3797 p_entity_type => 'CONT',
3798 p_delivery_detail => l_delivery_detail_id,
3799 p_parent_delivery_detail => l_parent_delivery_detail_id,
3800 p_delivery_id => l_delivery_id,
3801 p_inventory_item_id => l_inventory_Item_Id,
3802 p_organization_id => l_organization_Id,
3803 p_weight_uom => l_weight_uom,
3804 p_volume_uom => l_volume_uom,
3805 x_return_status => l_return_status );
3806
3807 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
3808 WSH_UTIL_CORE.G_RET_STS_WARNING) )
3809 THEN
3810 --
3811 IF ( l_debug_on ) THEN
3812 WSH_DEBUG_SV.logmsg(l_module_name,'Error returned from API Adjust_Parent_WV');
3813 END IF;
3814 RAISE FND_API.G_EXC_ERROR;
3815 --
3816 END IF;
3817
3818 FOR I IN l_parent_delivery_detail_id.first..l_parent_delivery_detail_id.last LOOP
3819
3820 IF l_debug_on THEN
3821 WSH_DEBUG_SV.log(l_module_name, 'Container Delivery_detail_id is ', nvl(l_parent_delivery_detail_id(i), NULL));
3822 WSH_DEBUG_SV.log(l_module_name, 'Delivery is ', nvl(l_delivery_id(i), NULL));
3823 END IF;
3824 IF l_parent_delivery_detail_id(i) IS NOT NULL AND l_delivery_id(i) IS NULL THEN
3825
3826 IF l_debug_on THEN
3827 WSH_DEBUG_SV.log(l_module_name, 'Logging Exception against the container');
3828 END IF;
3829
3830
3831 l_message_name := 'WSH_VMRG_UNASSIGN_CONTAINER';
3832 FND_MESSAGE.Set_Name ('WSH', l_message_name );
3833 FND_MESSAGE.Set_Token ('PS1', l_from_party_site_use_id );
3834 FND_MESSAGE.Set_Token ('PS2', l_to_party_site_use_id );
3835 FND_MESSAGE.Set_Token ('DELIVERY_DETAIL_ID', l_delivery_detail_id(i) );
3836
3837 l_message_text := FND_MESSAGE.Get;
3838
3839 l_return_status := NULL;
3840 l_msg_count := NULL;
3841 l_msg_data := NULL;
3842 l_exception_id := NULL;
3843
3844 WSH_XC_UTIL.Log_Exception
3845 (
3846 p_api_version => 1.0,
3847 x_return_status => l_return_status,
3848 x_msg_count => l_msg_count,
3849 x_msg_data => l_msg_data,
3850 x_exception_id => l_exception_id,
3851 p_exception_location_id => l_from_location_id,
3852 p_logged_at_location_id => l_to_location_id,
3853 p_logging_entity => 'SHIPPER',
3854 p_logging_entity_id => Fnd_Global.user_id,
3855 p_exception_name => 'WSH_VENDOR_MERGE_CHANGE',
3856 p_message => l_message_text,
3857 p_severity => 'LOW',
3858 p_manually_logged => 'N',
3859 p_delivery_detail_id => l_parent_delivery_detail_id(i),
3860 p_error_message => l_message_text
3861 );
3862
3863 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
3864 WSH_UTIL_CORE.G_RET_STS_WARNING) )
3865 THEN
3866 --
3867 IF ( l_debug_on ) THEN
3868 WSH_DEBUG_SV.log(l_module_name,'Error returned from API WSH_XC_UTIL.Log_Exception');
3869 END IF;
3870 RAISE FND_API.G_EXC_ERROR;
3871 --
3872 END IF;
3873 END IF;
3874 END LOOP;
3875
3876 IF l_debug_on THEN
3877 WSH_DEBUG_SV.log(l_module_name, 'End -Unpacking of delivery lines from containers, when they are not assigned to a delivery.');
3878 END IF;
3879
3880 -- To Process deliveries and Stops, inserting into temp table
3881 -- Start
3882
3883 IF l_debug_on THEN
3884 WSH_DEBUG_SV.log(l_module_name, 'Inserting Deliveries and Trip Stops into temp table for processing');
3885 END IF;
3886 IF l_debug_on THEN
3887 WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id.count', l_delivery_id.count);
3888 END IF;
3889
3890 FORALL I IN l_delivery_id.first..l_delivery_id.last
3891 INSERT INTO wsh_tmp ( column1, column2, column3 )
3892 SELECT l_delivery_id(i), l_stop_id(i), l_delivery_detail_id(i)
3893 FROM dual
3894 WHERE l_delivery_id(i) IS NOT NULL
3895 AND NOT EXISTS
3896 ( SELECT 'x'
3897 FROM wsh_tmp
3898 WHERE column1 = l_delivery_id(i)
3899 AND ( column2 = l_stop_id(i) OR l_stop_id(i) IS NULL ) );
3900
3901 l_sql_count := SQL%rowcount;
3902 IF l_debug_on THEN
3903 WSH_DEBUG_SV.logmsg(l_module_name,'inserted into wsh_tmp. Number of Rows inserted is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
3904 END IF;
3905
3906 -- To Process deliveries and Stops, inserting into temp table
3907 -- END
3908
3909 EXIT WHEN l_wdd_rowid.count < G_LIMIT;
3910
3911 END LOOP;
3912
3913 CLOSE get_unshipped_details;
3914 l_wdd_rowid.delete;
3915 l_delivery_detail_id.delete ;
3916 l_gross_weight.delete;
3917 l_net_weight.delete;
3918 l_volume.delete;
3919 l_parent_delivery_detail_id.delete;
3920 l_delivery_id.delete;
3921 l_stop_id.delete;
3922 l_wda_rowid.delete;
3923 l_inventory_item_id.delete;
3924 l_organization_id.delete;
3925 l_weight_uom.delete;
3926 l_volume_uom.delete;
3927
3928
3929 --Unpack and unassign delivery lines from delivery.
3930
3931 IF l_debug_on THEN
3932 WSH_DEBUG_SV.log(l_module_name, 'Unassigning delivery details from delivery');
3933 END IF;
3934
3935 OPEN get_unassign_details(p_to_vendor_id, l_to_party_site_use_id, l_to_location_id);
3936 LOOP
3937 FETCH get_unassign_details
3938 BULK COLLECT INTO l_wda_rowid, l_delivery_id, l_delivery_name,
3939 l_delivery_Detail_Id, l_parent_delivery_detail_id,l_inventory_item_id,
3940 l_organization_id,l_gross_weight, l_net_weight, l_volume,
3941 l_weight_uom,l_volume_uom
3942 LIMIT G_LIMIT;
3943 EXIT WHEN l_wda_rowid.count <= 0;
3944 IF l_debug_on THEN
3945 WSH_DEBUG_SV.log(l_module_name, 'l_wda_rowid.count', l_wda_rowid.count);
3946 END IF;
3947 IF l_debug_on THEN
3948 WSH_DEBUG_SV.log(l_module_name, 'updating weight before unassigning from delivery ');
3949 END IF;
3950 Adjust_Weight_Volume (
3951 p_entity_type => 'DEL-CONT',
3952 p_delivery_detail => l_delivery_Detail_Id,
3953 p_parent_delivery_detail => l_parent_Delivery_Detail_Id,
3954 p_delivery_id => l_delivery_Id,
3955 p_delivery_leg_id => l_dummyIdTab,
3956 p_net_weight => l_net_Weight,
3957 p_gross_weight => l_gross_Weight,
3958 p_volume => l_volume,
3959 x_return_status => l_return_status );
3960
3961 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
3962 WSH_UTIL_CORE.G_RET_STS_WARNING) )
3963 THEN
3964 --
3965 IF ( l_debug_on ) THEN
3966 WSH_DEBUG_SV.logmsg(l_module_name,'Error returned from API Adjust_Weight_Volume');
3967 END IF;
3968 RAISE FND_API.G_EXC_ERROR;
3969 --
3970 END IF;
3971
3972 IF l_debug_on THEN
3973 WSH_DEBUG_SV.log(l_module_name, 'l_wda_rowid.count', l_wda_rowid.count);
3974 END IF;
3975 FORALL I IN l_wda_rowid.FIRST..l_wda_rowid.LAST
3976 UPDATE wsh_delivery_assignments
3977 SET parent_delivery_detail_id = NULL,
3978 delivery_id = NULL,
3979 last_update_date = SYSDATE,
3980 last_updated_by = fnd_global.user_id,
3981 last_update_login = fnd_global.conc_login_id,
3982 program_application_id = fnd_global.prog_appl_id,
3983 program_id = fnd_global.conc_program_id,
3984 program_update_date = SYSDATE
3985 WHERE ROWID = l_wda_rowid(i);
3986 l_sql_count := SQL%rowcount;
3987 IF l_debug_on THEN
3988 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_assignments(unassign n unpack from del). Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
3989 END IF;
3990
3991 Adjust_Parent_WV (
3992 p_entity_type => 'DEL-CONT',
3993 p_delivery_detail => l_delivery_Detail_Id,
3994 p_parent_delivery_detail => l_parent_Delivery_Detail_Id,
3995 p_delivery_id => l_delivery_Id,
3996 p_inventory_item_id => l_inventory_Item_Id,
3997 p_organization_id => l_organization_Id,
3998 p_weight_uom => l_weight_Uom,
3999 p_volume_uom => l_volume_Uom,
4000 x_return_status => l_return_status );
4001
4002 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
4003 WSH_UTIL_CORE.G_RET_STS_WARNING) )
4004 THEN
4005 --
4006 IF ( l_debug_on ) THEN
4007 WSH_DEBUG_SV.log(l_module_name, 'Error returned from API Adjust_Parent_WV');
4008 END IF;
4009 RAISE FND_API.G_EXC_ERROR;
4010 --
4011 END IF;
4012 IF l_debug_on THEN
4013 WSH_DEBUG_SV.log(l_module_name, 'Clearing the deliveries from temp table');
4014 END IF;
4015 --Delivery for which delivery lines are unassigned needs to be deleted
4016 --from wsh_tmp table
4017 FORALL I IN l_delivery_id.FIRST..l_delivery_id.LAST
4018 DELETE FROM wsh_tmp
4019 WHERE column1 = l_delivery_id(i);
4020
4021 l_sql_count := SQL%rowcount;
4022 IF l_debug_on THEN
4023 WSH_DEBUG_SV.logmsg(l_module_name,'deleted from wsh_tmp. Number of Rows deleted is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
4024 END IF;
4025 --Log Exceptions 'WSH_VENDOR_MERGE_CHANGE' with message
4026 --'WSH_VMRG_UNASSIGN_DELIVERY' against delivery from which delivery lines
4027 --are un-assigned.
4028 l_message_name := 'WSH_VMRG_UNASSIGN_DELIVERY';
4029 FOR I IN l_wda_rowid.FIRST..l_wda_rowid.LAST LOOP
4030
4031 IF l_parent_Delivery_Detail_Id(i) is not null THEN
4032 IF l_debug_on THEN
4033 WSH_DEBUG_SV.log(l_module_name, 'Logging Exception against the container');
4034 END IF;
4035
4036
4037 l_message_name := 'WSH_VMRG_UNASSIGN_CONTAINER';
4038 FND_MESSAGE.Set_Name ('WSH', l_message_name );
4039 FND_MESSAGE.Set_Token ('PS1', l_from_party_site_use_id );
4040 FND_MESSAGE.Set_Token ('PS2', l_to_party_site_use_id );
4041 FND_MESSAGE.Set_Token ('DELIVERY_DETAIL_ID', l_delivery_detail_id(i) );
4042
4043 l_message_text := FND_MESSAGE.Get;
4044
4045 l_return_status := NULL;
4046 l_msg_count := NULL;
4047 l_msg_data := NULL;
4048 l_exception_id := NULL;
4049
4050 WSH_XC_UTIL.Log_Exception
4051 (
4052 p_api_version => 1.0,
4053 x_return_status => l_return_status,
4054 x_msg_count => l_msg_count,
4055 x_msg_data => l_msg_data,
4056 x_exception_id => l_exception_id,
4057 p_exception_location_id => l_from_location_id,
4058 p_logged_at_location_id => l_to_location_id,
4059 p_logging_entity => 'SHIPPER',
4060 p_logging_entity_id => Fnd_Global.user_id,
4061 p_exception_name => 'WSH_VENDOR_MERGE_CHANGE',
4062 p_message => l_message_text,
4063 p_severity => 'LOW',
4064 p_manually_logged => 'N',
4065 p_delivery_detail_id => l_parent_delivery_detail_id(i),
4066 p_error_message => l_message_text
4067 );
4068
4069 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
4070 WSH_UTIL_CORE.G_RET_STS_WARNING) )
4071 THEN
4072 --
4073 IF ( l_debug_on ) THEN
4074 WSH_DEBUG_SV.log(l_module_name,'Error returned from API WSH_XC_UTIL.Log_Exception');
4075 END IF;
4076 RAISE FND_API.G_EXC_ERROR;
4077 --
4078 END IF;
4079 END IF;
4080 IF l_debug_on THEN
4081 WSH_DEBUG_SV.log(l_module_name, 'Logging Exception against the container');
4082 END IF;
4083
4084 FND_MESSAGE.Set_Name ('WSH', l_message_name );
4085 FND_MESSAGE.Set_Token ('PS1', l_from_party_site_use_id );
4086 FND_MESSAGE.Set_Token ('PS2', l_to_party_site_use_id );
4087 FND_MESSAGE.Set_Token ('DELIVERY_DETAIL_ID', l_delivery_Detail_Id(i) );
4088
4089 l_message_text := FND_MESSAGE.Get;
4090
4091 l_return_status := NULL;
4092 l_msg_count := NULL;
4093 l_msg_data := NULL;
4094 l_exception_id := NULL;
4095
4096 WSH_XC_UTIL.Log_Exception
4097 (
4098 p_api_version => 1.0,
4099 x_return_status => l_return_status,
4100 x_msg_count => l_msg_count,
4101 x_msg_data => l_msg_data,
4102 x_exception_id => l_exception_id,
4103 p_exception_location_id => l_from_location_id,
4104 p_logged_at_location_id => l_to_location_id,
4105 p_logging_entity => 'SHIPPER',
4106 p_logging_entity_id => Fnd_Global.user_id,
4107 p_exception_name => 'WSH_VENDOR_MERGE_CHANGE',
4108 p_message => l_message_text,
4109 p_severity => 'LOW',
4110 p_manually_logged => 'N',
4111 p_delivery_id => l_delivery_id(i),
4112 p_delivery_name => l_delivery_name(i),
4113 p_error_message => l_message_text
4114 );
4115 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
4116 WSH_UTIL_CORE.G_RET_STS_WARNING) )
4117 THEN
4118 --
4119 IF ( l_debug_on ) THEN
4120 WSH_DEBUG_SV.log(l_module_name,'Error returned from API WSH_XC_UTIL.Log_Exception');
4121 END IF;
4122 --
4123 RAISE FND_API.G_EXC_ERROR;
4124 END IF;
4125 END LOOP;
4126 EXIT WHEN l_wda_rowid.count < G_LIMIT;
4127 END LOOP;
4128 CLOSE get_unassign_details;
4129
4130 l_delivery_detail_id.delete ;
4131 l_gross_weight.delete;
4132 l_net_weight.delete;
4133 l_volume.delete;
4134 l_parent_delivery_detail_id.delete;
4135 l_delivery_id.delete;
4136 l_wda_rowid.delete;
4137 l_delivery_name.delete;
4138 l_inventory_item_id.delete;
4139 l_organization_id.delete;
4140 l_weight_uom.delete;
4141 l_volume_uom.delete;
4142
4143 --Unpack and unassign delivery lines from delivery.
4144 -- END
4145
4146 --Updating un-shipped containers
4147 -- START
4148
4149 IF l_debug_on THEN
4150 WSH_DEBUG_SV.log(l_module_name, 'Updating Customer details on unshipped containes');
4151 END IF;
4152
4153 OPEN get_delivery_containers;
4154 LOOP
4155 FETCH get_delivery_containers BULK COLLECT INTO l_wdd_rowid LIMIT G_LIMIT;
4156 EXIT WHEN l_wdd_rowid.count <= 0;
4157 IF l_debug_on THEN
4158 WSH_DEBUG_SV.log(l_module_name, 'l_wdd_rowid.count', l_wdd_rowid.count);
4159 END IF;
4160 FORALL I IN l_wdd_rowid.FIRST..l_wdd_rowid.LAST
4161 UPDATE wsh_delivery_details
4162 SET customer_id = decode(customer_id,
4163 p_from_vendor_id, p_to_vendor_id,
4164 customer_id),
4165 ship_to_location_id = l_to_location_id,
4166 last_update_date = SYSDATE,
4167 last_updated_by = fnd_global.user_id,
4168 last_update_login = fnd_global.conc_login_id,
4169 program_application_id = fnd_global.prog_appl_id,
4170 program_id = fnd_global.conc_program_id,
4171 program_update_date = SYSDATE
4172 WHERE ROWID = l_wdd_rowid(i);
4173 l_sql_count := SQL%rowcount;
4174 IF l_debug_on THEN
4175 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details (Containers). Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
4176 END IF;
4177
4178 EXIT WHEN l_wdd_rowid.count < G_LIMIT;
4179
4180 END LOOP;
4181 CLOSE get_delivery_containers;
4182 l_delivery_id.delete;
4183 l_delivery_detail_id.delete;
4184 l_wdd_rowid.delete;
4185
4186 --Updating un-shipped containers
4187 --END
4188
4189 --Update deliveries
4190 -- START
4191 l_hash_value_tab.DELETE;
4192 l_hash_string_tab.DELETE;
4193 l_delivery_id_tab.DELETE;
4194 l_return_status := NULL;
4195
4196 Get_Delivery_Hash (
4197 x_hash_string_tab => l_hash_string_tab,
4198 x_hash_value_tab => l_hash_value_tab,
4199 x_delivery_id_tab => l_delivery_id_tab,
4200 x_return_status => l_return_status );
4201 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
4202 WSH_UTIL_CORE.G_RET_STS_WARNING) )
4203 THEN
4204 --
4205 IF ( l_debug_on ) THEN
4206 WSH_DEBUG_SV.log(l_module_name,'Error returned from API Get_Delivery_Hash');
4207 END IF;
4208 --
4209 END IF;
4210 IF l_debug_on THEN
4211 WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id_tab.count', l_delivery_id_tab.count);
4212 END IF;
4213
4214 FORALL I IN l_delivery_id_tab.first..l_delivery_id_tab.last
4215 UPDATE wsh_new_deliveries
4216 SET customer_id = decode(customer_id,
4217 p_from_vendor_id, p_to_vendor_id,
4218 customer_id ),
4219 ultimate_dropoff_location_id = l_to_location_id,
4220 hash_value = l_hash_value_tab(i),
4221 hash_string = l_hash_string_tab(i),
4222 last_update_date = SYSDATE,
4223 last_updated_by = fnd_global.user_id,
4224 last_update_login = fnd_global.conc_login_id,
4225 program_application_id = fnd_global.prog_appl_id,
4226 program_id = fnd_global.conc_program_id,
4227 program_update_date = SYSDATE
4228 WHERE delivery_id = l_delivery_id_tab(i);
4229 l_sql_count := SQL%rowcount;
4230 IF l_debug_on THEN
4231 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries. Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
4232 END IF;
4233 -- Update deliveries
4234 -- END
4235
4236 OPEN get_unassign_stops(l_to_location_id);
4237 LOOP
4238 FETCH get_unassign_stops BULK COLLECT INTO l_wdl_rowid, l_tmp_rowid,
4239 l_delivery_id, l_delivery_leg_id,
4240 l_trip_id, l_trip_stop_id,
4241 l_gross_weight, l_net_weight, l_volume
4242 LIMIT G_LIMIT;
4243 EXIT WHEN l_wdl_rowid.count <= 0;
4244 IF l_debug_on THEN
4245 WSH_DEBUG_SV.log(l_module_name, 'l_wdl_rowid.count', l_wdl_rowid.count);
4246 END IF;
4247 Adjust_Weight_Volume (
4248 p_entity_type => 'TRIP-STOP',
4249 p_delivery_detail => l_dummyIdTab,
4250 p_parent_delivery_detail => l_dummyIdTab,
4251 p_delivery_id => l_delivery_id,
4252 p_delivery_leg_id => l_delivery_Leg_Id,
4253 p_net_weight => l_net_weight,
4254 p_gross_weight => l_gross_weight,
4255 p_volume => l_volume,
4256 x_return_status => l_return_status );
4257
4258 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
4259 WSH_UTIL_CORE.G_RET_STS_WARNING) )
4260 THEN
4261 --
4262 IF ( l_debug_on ) THEN
4263 WSH_DEBUG_SV.log(l_module_name,'Error returned from API Adjust_Weight_Volume');
4264 END IF;
4265 --
4266 END IF;
4267 IF l_debug_on THEN
4268 WSH_DEBUG_SV.log(l_module_name, 'l_wdl_rowid.count', l_wdl_rowid.count);
4269 END IF;
4270 FORALL I IN l_wdl_rowid.FIRST..l_wdl_rowid.LAST
4271 DELETE FROM wsh_delivery_legs
4272 WHERE ROWID = l_wdl_rowid(i);
4273
4274 l_sql_count := SQL%rowcount;
4275 IF l_debug_on THEN
4276 WSH_DEBUG_SV.logmsg(l_module_name,'deleted from wsh_delivery_legs. Number of Rows deleted is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
4277 END IF;
4278
4279 FORALL I IN l_tmp_rowid.FIRST..l_tmp_rowid.LAST
4280 DELETE FROM wsh_tmp
4281 WHERE ROWID = l_tmp_rowid(i);
4282
4283 l_sql_count := SQL%rowcount;
4284 IF l_debug_on THEN
4285 WSH_DEBUG_SV.logmsg(l_module_name,'deleted from wsh_tmp. Number of Rows deleted is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
4286 END IF;
4287
4288 -- Log Exceptions 'WSH_VENDOR_MERGE_CHANGE' with message
4289 -- 'WSH_VMRG_UNASSIGN_STOP' against stop from which delivery
4290 --are un-assigned.
4291 l_message_name := 'WSH_VMRG_UNASSIGN_STOP';
4292 FOR I IN l_wdl_rowid.FIRST..l_wdl_rowid.LAST LOOP
4293 FND_MESSAGE.Set_Name ('WSH', l_message_name );
4294 FND_MESSAGE.Set_Token ('PS1', l_from_party_site_use_id );
4295 FND_MESSAGE.Set_Token ('PS2', l_to_party_site_use_id );
4296 FND_MESSAGE.Set_Token ('DELIVERY_ID', l_delivery_id(i) );
4297
4298 l_message_text := FND_MESSAGE.Get;
4299
4300 l_return_status := NULL;
4301 l_msg_count := NULL;
4302 l_msg_data := NULL;
4303 l_exception_id := NULL;
4304
4305 WSH_XC_UTIL.Log_Exception
4306 (
4307 p_api_version => 1.0,
4308 x_return_status => l_return_status,
4309 x_msg_count => l_msg_count,
4310 x_msg_data => l_msg_data,
4311 x_exception_id => l_exception_id,
4312 p_exception_location_id => l_from_location_id,
4313 p_logged_at_location_id => l_to_location_id,
4314 p_logging_entity => 'SHIPPER',
4315 p_logging_entity_id => Fnd_Global.user_id,
4316 p_exception_name => 'WSH_VENDOR_MERGE_CHANGE',
4317 p_message => l_message_text,
4318 p_severity => 'LOW',
4319 p_manually_logged => 'N',
4320 p_trip_id => l_trip_id(i),
4321 p_trip_stop_id => l_trip_stop_id(i),
4322 p_error_message => l_message_text
4323 );
4324 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
4325 WSH_UTIL_CORE.G_RET_STS_WARNING) )
4326 THEN
4327 --
4328 IF ( l_debug_on ) THEN
4329 WSH_DEBUG_SV.log(l_module_name,'Error returned from API WSH_XC_UTIL.Log_Exception');
4330 END IF;
4331 --
4332 END IF;
4333 END LOOP;
4334 EXIT WHEN l_wdl_rowid.count < G_LIMIT;
4335 END LOOP;
4336 CLOSE get_unassign_stops;
4337 --Unassign delivery from Trip Stop
4338 -- END
4339 l_wdl_rowid.delete;
4340 l_tmp_rowid.delete;
4341 l_delivery_id.delete;
4342 l_delivery_leg_id.delete;
4343 l_trip_id.delete;
4344 l_trip_stop_id.delete;
4345 l_gross_weight.delete;
4346 l_net_weight.delete;
4347 l_volume.delete;
4348
4349 UPDATE wsh_trip_stops
4350 SET stop_location_id = l_to_location_id,
4351 last_update_date = SYSDATE,
4352 last_updated_by = fnd_global.user_id,
4353 last_update_login = fnd_global.conc_login_id,
4354 program_application_id = fnd_global.prog_appl_id,
4355 program_id = fnd_global.conc_program_id,
4356 program_update_date = SYSDATE
4357 WHERE Stop_Id in (
4358 SELECT Column2
4359 FROM WSH_TMP
4360 WHERE Column2 IS NOT NULL );
4361
4362 --Delete from Wsh_Tmp Table
4363 DELETE FROM Wsh_Tmp;
4364
4365 -- Updating Empty Deliveries
4366
4367 OPEN get_empty_deliveries(p_from_vendor_id, l_from_location_id);
4368 LOOP
4369 FETCH get_empty_deliveries
4370 BULK COLLECT INTO l_delivery_id,l_initial_pickup_location_id,l_customer_id,
4371 l_intmed_ship_to_location_id,l_fob_code,l_freight_terms_code,
4372 l_ship_method_code,l_carrier_id,l_source_header_id,l_organization_id,
4373 l_initial_pickup_date,l_ultimate_dropoff_date,l_ignore_for_planning,
4374 l_shipment_direction,l_shipping_control,l_party_id,l_client_id
4375 LIMIT G_LIMIT;
4376 EXIT WHEN l_delivery_id.count = 0;
4377 FOR I IN l_delivery_id.first..l_delivery_id.last LOOP
4378 l_grp_attr_tab(i).ship_to_location_id := l_to_location_id;
4379 l_grp_attr_tab(i).ship_from_location_id := l_initial_pickup_location_id(i);
4380 l_grp_attr_tab(i).customer_id := p_to_vendor_id;
4381 l_grp_attr_tab(i).intmed_ship_to_location_id := l_intmed_ship_to_location_id(i);
4382 l_grp_attr_tab(i).fob_code := l_fob_code(i);
4383 l_grp_attr_tab(i).freight_terms_code := l_freight_terms_code(i);
4384 l_grp_attr_tab(i).ship_method_code := l_ship_method_code(i);
4385 l_grp_attr_tab(i).carrier_id := l_carrier_id(i);
4386 l_grp_attr_tab(i).source_header_id := l_source_header_id(i);
4387 l_grp_attr_tab(i).organization_id := l_organization_id(i);
4388 l_grp_attr_tab(i).date_scheduled := l_initial_pickup_date(i);
4389 l_grp_attr_tab(i).date_requested := l_ultimate_dropoff_date(i);
4390 l_grp_attr_tab(i).ignore_for_planning := l_ignore_for_planning(i);
4391 l_grp_attr_tab(i).line_direction := l_shipment_direction(i);
4392 l_grp_attr_tab(i).shipping_control := l_shipping_control(i);
4393 l_grp_attr_tab(i).party_id := l_party_id(i);
4394 l_grp_attr_tab(i).client_id := l_client_id(i);
4395 l_grp_attr_tab(i).consignee_flag := 'V';
4396
4397 END LOOP;
4398 WSH_DELIVERY_AUTOCREATE.Create_Hash(
4399 p_grouping_attributes => l_grp_attr_tab,
4400 p_group_by_header => 'N',
4401 p_action_code => NULL,
4402 x_return_status => x_return_status);
4403 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
4404 WSH_UTIL_CORE.G_RET_STS_WARNING) )
4405 THEN
4406 --
4407 IF ( l_debug_on ) THEN
4408 WSH_DEBUG_SV.log(l_module_name,'Error returned from API WSH_DELIVERY_AUTOCREATE.Create_Hash');
4409 RAISE FND_API.G_EXC_ERROR;
4410 END IF;
4411 --
4412 END IF;
4413
4414 IF l_debug_on THEN
4415 WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id.count', l_delivery_id.count);
4416 END IF;
4417
4418 FORALL I IN l_delivery_id.first..l_delivery_id.last
4419 UPDATE wsh_new_deliveries
4420 SET customer_id = decode(customer_id,
4421 p_from_vendor_id, p_to_vendor_id,
4422 customer_id ),
4423 ultimate_dropoff_location_id = l_to_location_id,
4424 hash_value = l_grp_attr_tab(i).l1_hash_value,
4425 hash_string = l_grp_attr_tab(i).l1_hash_string,
4426 last_update_date = SYSDATE,
4427 last_updated_by = fnd_global.user_id,
4428 last_update_login = fnd_global.conc_login_id,
4429 program_application_id = fnd_global.prog_appl_id,
4430 program_id = fnd_global.conc_program_id,
4431 program_update_date = SYSDATE
4432 WHERE delivery_id = l_delivery_id(i);
4433
4434 l_sql_count := SQL%rowcount;
4435 IF l_debug_on THEN
4436 WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries(Empty deliveries). Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
4437 END IF;
4438
4439 EXIT WHEN l_delivery_id.count < G_LIMIT;
4440 END LOOP;
4441
4442 l_delivery_id.delete;
4443 l_initial_pickup_location_id.delete;
4444 l_customer_id.delete;
4445 l_intmed_ship_to_location_id.delete;
4446 l_fob_code.delete;
4447 l_freight_terms_code.delete;
4448 l_ship_method_code.delete;
4449 l_carrier_id.delete;
4450 l_source_header_id.delete;
4451 l_organization_id.delete;
4452 l_initial_pickup_date.delete;
4453 l_ultimate_dropoff_date.delete;
4454 l_ignore_for_planning.delete;
4455 l_shipment_direction.delete;
4456 l_shipping_control.delete;
4457 l_party_id.delete;
4458 l_client_id.delete;
4459
4460 WSH_DEBUG_SV.pop(l_module_name);
4461
4462 EXCEPTION
4463 --
4464 WHEN FND_API.G_EXC_ERROR THEN
4465
4466 IF get_loc_for_site%ISOPEN THEN
4467 CLOSE get_loc_for_site;
4468 END IF;
4469 IF c_check_vendor_wdd%ISOPEN THEN
4470 CLOSE c_check_vendor_wdd;
4471 END IF;
4472 IF c_check_vendor_wnd%ISOPEN THEN
4473 CLOSE c_check_vendor_wnd;
4474 END IF;
4475 IF get_unshipped_details%ISOPEN THEN
4476 CLOSE get_unshipped_details;
4477 END IF;
4478 IF get_shipped_details%ISOPEN THEN
4479 CLOSE get_shipped_details;
4480 END IF;
4481 IF get_unassign_details%ISOPEN THEN
4482 CLOSE get_unassign_details;
4483 END IF;
4484 IF get_delivery_containers%ISOPEN THEN
4485 CLOSE get_delivery_containers;
4486 END IF;
4487 IF get_unassign_stops%ISOPEN THEN
4488 CLOSE get_unassign_stops;
4489 END IF;
4490 IF get_empty_deliveries%ISOPEN THEN
4491 CLOSE get_empty_deliveries;
4492 END IF;
4493
4494 --
4495 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4496 --
4497 IF l_debug_on THEN
4498 --
4499 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_EXCEP_LEVEL);
4500 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
4501 --
4502 END IF;
4503 --
4504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4505 IF get_loc_for_site%ISOPEN THEN
4506 CLOSE get_loc_for_site;
4507 END IF;
4508 IF c_check_vendor_wdd%ISOPEN THEN
4509 CLOSE c_check_vendor_wdd;
4510 END IF;
4511 IF c_check_vendor_wnd%ISOPEN THEN
4512 CLOSE c_check_vendor_wnd;
4513 END IF;
4514 IF get_unshipped_details%ISOPEN THEN
4515 CLOSE get_unshipped_details;
4516 END IF;
4517 IF get_shipped_details%ISOPEN THEN
4518 CLOSE get_shipped_details;
4519 END IF;
4520 IF get_unassign_details%ISOPEN THEN
4521 CLOSE get_unassign_details;
4522 END IF;
4523 IF get_delivery_containers%ISOPEN THEN
4524 CLOSE get_delivery_containers;
4525 END IF;
4526 IF get_unassign_stops%ISOPEN THEN
4527 CLOSE get_unassign_stops;
4528 END IF;
4529 IF get_empty_deliveries%ISOPEN THEN
4530 CLOSE get_empty_deliveries;
4531 END IF;
4532 --
4533 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4534 --
4535 IF l_debug_on THEN
4536 --
4537 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4538 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4539 --
4540 END IF;
4541 --
4542 WHEN OTHERS THEN
4543 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4544 IF get_loc_for_site%ISOPEN THEN
4545 CLOSE get_loc_for_site;
4546 END IF;
4547 IF c_check_vendor_wdd%ISOPEN THEN
4548 CLOSE c_check_vendor_wdd;
4549 END IF;
4550 IF c_check_vendor_wnd%ISOPEN THEN
4551 CLOSE c_check_vendor_wnd;
4552 END IF;
4553 IF get_unshipped_details%ISOPEN THEN
4554 CLOSE get_unshipped_details;
4555 END IF;
4556 IF get_shipped_details%ISOPEN THEN
4557 CLOSE get_shipped_details;
4558 END IF;
4559 IF get_unassign_details%ISOPEN THEN
4560 CLOSE get_unassign_details;
4561 END IF;
4562 IF get_delivery_containers%ISOPEN THEN
4563 CLOSE get_delivery_containers;
4564 END IF;
4565 IF get_unassign_stops%ISOPEN THEN
4566 CLOSE get_unassign_stops;
4567 END IF;
4568 IF get_empty_deliveries%ISOPEN THEN
4569 CLOSE get_empty_deliveries;
4570 END IF;
4571 --
4572 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4573 wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Merge_RTV_Transactions');
4574 --
4575 IF l_debug_on THEN
4576 --
4577 WSH_DEBUG_SV.log(l_module_name,'Unexpected error has occured. Oracle error message is ', SUBSTRB(SQLERRM, 1, 200));
4578 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:OTHERS');
4579 --
4580 END IF;
4581 --
4582 END Merge_RTV_Transactions;
4583
4584 END WSH_VENDOR_PARTY_MERGE_PKG;