[Home] [Help]
PACKAGE BODY: APPS.WSH_PARTY_MERGE
Source
1 PACKAGE BODY WSH_PARTY_MERGE as
2 /* $Header: WSHPAMRB.pls 120.12 2007/11/21 06:02:56 ueshanka noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_PARTY_MERGE';
4
5 --
6 -- R12 FP Bug 5075838
7 --
8 TYPE Shipping_Param_Tab IS TABLE OF WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ INDEX BY BINARY_INTEGER;
9
10 --
11 G_PARAM_INFO_TAB Shipping_Param_Tab;
12 G_WMS_ENABLED WSH_UTIL_CORE.Column_Tab_Type;
13 G_DELIVERY_ID WSH_UTIL_CORE.Id_Tab_Type;
14 G_FTE_INSTALLED VARCHAR2(10);
15 G_FETCH_LIMIT CONSTANT NUMBER := 10000;
16
17
18 --========================================================================
19 -- PROCEDURE :merge_carriers
20 -- PARAMETERS:
21 -- p_entity_name Name of Entity Being Merged
22 -- p_from_id Primary Key Id of the entity that is being merged
23 -- p_to_id The record under the 'To Parent' that is being merged
24 -- p_from_fk_id Foreign Key id of the Old Parent Record
25 -- p_to_fk_id Foreign Key id of the New Parent Record
26 -- p_parent_entity_name Name of Parent Entity
27 -- p_batch_id Id of the Batch
28 -- p_batch_party_id Id uniquely identifies the batch and party record that is being merged
29 -- x_return_status Returns the staus of call
30 --
31 -- COMMENT : Carriers cannot be merged.
32 --========================================================================
33 PROCEDURE merge_carriers(
34 p_entity_name IN VARCHAR2,
35 p_from_id IN NUMBER,
36 p_to_id IN OUT NOCOPY NUMBER,
37 p_from_fk_id IN NUMBER,
38 p_to_fk_id IN NUMBER,
39 p_parent_entity_name IN VARCHAR2,
40 p_batch_id IN NUMBER,
41 p_batch_party_id IN NUMBER,
42 x_return_status IN OUT NOCOPY VARCHAR2) IS
43
44 l_from_carrier_num NUMBER;
45
46 Cursor C_Carriers(p_carrier_id NUMBER) IS
47 SELECT 1
48 FROM wsh_carriers
49 WHERE carrier_id = p_carrier_id;
50 --
51 -- R12 Vendor Merge
52 --
53 CURSOR c_GetVendorId(p_partyId IN NUMBER) IS
54 SELECT vendor_id, vendor_name
55 FROM po_vendors
56 WHERE party_id = p_partyId;
57 --
58 l_fromVendorID NUMBER;
59 l_toVendorID NUMBER;
60 l_fromSupName VARCHAR2(360);
61 l_toSupName VARCHAR2(360);
62 l_return_Status VARCHAR2(1);
63 --
64 BEGIN
65
66 x_return_status := FND_API.G_RET_STS_ERROR;
67
68 IF (p_from_fk_id = p_to_fk_id) THEN
69 p_to_id := p_from_id;
70 RETURN;
71 END IF;
72
73 IF (p_from_fk_id <> p_to_fk_id) THEN
74 IF (p_parent_entity_name = 'HZ_PARTIES') THEN
75
76 OPEN C_Carriers(p_from_fk_id);
77 FETCH C_Carriers INTO l_from_carrier_num;
78 CLOSE C_Carriers;
79
80 IF ( l_from_carrier_num > 0 ) THEN
81 x_return_status := FND_API.G_RET_STS_ERROR;
82 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NO_MERGE');
83 FND_MSG_PUB.ADD;
84 RETURN;
85 END IF;
86 END IF;
87 END IF;
88 --
89 -- R12 Vendor Merge
90 --
91 -- (a) Derive vendor IDs for the from/to party IDs
92 -- (b) Check if both parties are Vendors
93 -- (c) If both from and to parties are vendors, then call
94 -- Update_Entities_During_Merge
95 -- (d) Else If fromVendor ID is not NULL, then
96 -- delete from wsh_calendar_assignments
97 --
98 OPEN c_GetVendorId(p_from_id);
99 FETCH c_GetVendorId INTO l_fromVendorID, l_fromSupName;
100 CLOSE c_GetVendorId;
101 --
102 OPEN c_GetVendorId(p_to_id);
103 FETCH c_GetVendorId INTO l_toVendorID, l_toSupName;
104 CLOSE c_GetVendorId;
105 --
106 IF l_fromVendorID IS NOT NULL AND l_toVendorID IS NOT NULL THEN
107 --
108 Update_Entities_During_Merge
109 (
110 p_to_id => l_toVendorID,
111 p_from_id => l_fromVendorID,
112 p_from_party_id => p_from_id ,
113 p_to_party_id => p_to_id ,
114 p_to_site_id => NULL,
115 p_from_site_id => NULL,
116 p_site_merge => FALSE,
117 p_from_supplier_name => l_fromSupName,
118 x_return_status => l_return_status
119 );
120 --
121 x_return_status := l_return_status;
122 --
123 ELSIF l_fromVendorID IS NOT NULL THEN
124 --
125 DELETE wsh_calendar_assignments
126 WHERE vendor_id = l_fromVendorID
127 AND vendor_site_id IS NULL;
128 --
129 END IF;
130 --
131 EXCEPTION WHEN OTHERS THEN
132 FND_MESSAGE.SET_NAME('AR','HZ_API_OTHERS_EXCEP');
133 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
134 FND_MSG_PUB.ADD;
135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
136
137 END merge_carriers;
138
139 --========================================================================
140 -- PROCEDURE :merge_carrier_sites
141 -- PARAMETERS:
142 -- p_entity_name Name of Entity Being Merged
143 -- p_from_id Primary Key Id of the entity that is being merged
144 -- p_to_id The record under the 'To Parent' that is being merged
145 -- p_from_fk_id Foreign Key id of the Old Parent Record
146 -- p_to_fk_id Foreign Key id of the New Parent Record
147 -- p_parent_entity_name Name of Parent Entity
148 -- p_batch_id Id of the Batch
149 -- p_batch_party_id Id uniquely identifies the batch and party record that is being merged
150 -- x_return_status Returns the staus of call
151 --
152 -- COMMENT : Carriers Sites cannot be merged.
153 --========================================================================
154 PROCEDURE merge_carrier_sites(
155 p_entity_name IN VARCHAR2,
156 p_from_id IN NUMBER,
157 p_to_id IN OUT NOCOPY NUMBER,
158 p_from_fk_id IN NUMBER,
159 p_to_fk_id IN NUMBER,
160 p_parent_entity_name IN VARCHAR2,
161 p_batch_id IN NUMBER,
162 p_batch_party_id IN NUMBER,
163 x_return_status IN OUT NOCOPY VARCHAR2) IS
164
165 Cursor C_Carrier_Sites(p_carrier_site_id NUMBER) IS
166 SELECT 1
167 FROM wsh_carrier_sites
168 WHERE carrier_site_id = p_carrier_site_id;
169
170 l_from_carrier_site_num NUMBER;
171
172 BEGIN
173
174 IF (p_from_fk_id = p_to_fk_id) THEN
175 p_to_id := p_from_id;
176 RETURN;
177 END IF;
178
179 IF (p_from_fk_id <> p_to_fk_id) THEN
180 IF (p_parent_entity_name = 'HZ_PARTY_SITES') THEN
181
182 OPEN C_Carrier_Sites(p_from_fk_id);
183 FETCH C_Carrier_Sites INTO l_from_carrier_site_num;
184 CLOSE C_Carrier_Sites;
185
186 IF ( l_from_carrier_site_num > 0 ) THEN
187 x_return_status := FND_API.G_RET_STS_ERROR;
188 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_SITE_NO_MERGE');
189 FND_MSG_PUB.ADD;
190 RETURN;
191 END IF;
192
193 END IF;
194 END IF;
195
196 EXCEPTION WHEN OTHERS THEN
197 FND_MESSAGE.SET_NAME('AR','HZ_API_OTHERS_EXCEP');
198 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
199 FND_MSG_PUB.ADD;
200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
201
202 END merge_carrier_sites;
203
204 --========================================================================
205 -- PROCEDURE :check_duplicate_rec
206 -- PARAMETERS:
207 -- p_from_pk Primary key id of the FROM record
208 -- p_to_party_id Owner party id of the TO record
209 -- x_dup_rec_pk Return -1 if duplicate record exists
210 -- x_return_status Returns the staus of call
211 --
212 -- COMMENT : Procedure to Check Duplicates
213 --========================================================================
214
215
216 PROCEDURE check_duplicate_rec(
217 p_from_pk IN NUMBER,
218 p_to_party_id IN NUMBER,
219 x_dup_rec_pk IN OUT NOCOPY NUMBER,
220 x_return_status IN OUT NOCOPY VARCHAR2)
221 IS
222
223 CURSOR c_check_duplicate(p_location_id IN NUMBER, p_party_id IN NUMBER) IS
224 SELECT location_owner_id
225 FROM wsh_location_owners
226 WHERE wsh_location_id = p_location_id
227 AND owner_party_id = p_party_id;
228
229 CURSOR c_populate_data(p_loc_owner_id IN NUMBER) IS
230 SELECT wsh_location_id
231 FROM wsh_location_owners
232 WHERE location_owner_id = p_loc_owner_id;
233
234 l_location_id NUMBER;
235 --l_owner_type NUMBER;
236 l_dup_rowid NUMBER;
237
238 BEGIN
239 x_return_status := FND_API.G_RET_STS_SUCCESS;
240
241
242 OPEN c_populate_data(p_from_pk);
243 FETCH c_populate_data INTO l_location_id;
244 CLOSE c_populate_data;
245
246 OPEN c_check_duplicate(l_location_id,p_to_party_id);
247 FETCH c_check_duplicate INTO l_dup_rowid;
248 IF (c_check_duplicate%NOTFOUND) THEN
249 x_dup_rec_pk := -1;
250 ELSE
251 x_dup_rec_pk := l_dup_rowid;
252 END IF;
253 CLOSE c_check_duplicate;
254
255 EXCEPTION
256 WHEN others THEN
257 IF c_populate_data%ISOPEN THEN
258 CLOSE c_populate_data;
259 END IF;
260 IF c_check_duplicate%ISOPEN THEN
261 CLOSE c_check_duplicate;
262 END IF;
263 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
264 END check_duplicate_rec;
265
266
267 --========================================================================
268 -- PROCEDURE :merge_party_locations
269 -- PARAMETERS:
270 -- p_entity_name Name of Entity Being Merged
271 -- p_from_id Primary Key Id of the entity that is being merged
272 -- p_to_id The record under the 'To Parent' that is being merged
273 -- p_from_fk_id Foreign Key id of the Old Parent Record
274 -- p_to_fk_id Foreign Key id of the New Parent Record
275 -- p_parent_entity_name Name of Parent Entity
276 -- p_batch_id Id of the Batch
277 -- p_batch_party_id Id uniquely identifies the batch and party record that is being merged
278 -- x_return_status Returns the staus of call
279 --
280 -- COMMENT : To merge locations for parties.-Parent Entity is HZ_PARTIES.
281 -- Owner Type can be either Supplier or Customer, Carriers cannot be merged
282 -- Updates OWNER_PARTY_ID and OWNER_TYPE in WSH_LOCATION_OWNERS
283 --========================================================================
284
285 PROCEDURE merge_party_locations(
286 p_entity_name IN VARCHAR2,
287 p_from_id IN NUMBER,
288 p_to_id IN OUT NOCOPY NUMBER,
289 p_from_fk_id IN NUMBER,
290 p_to_fk_id IN NUMBER,
291 p_parent_entity_name IN VARCHAR2,
292 p_batch_id IN NUMBER,
293 p_batch_party_id IN NUMBER,
294 x_return_status IN OUT NOCOPY VARCHAR2) IS
295
296 Cursor C_Owner_Type(p_party_id NUMBER) IS
297 SELECT owner_type
298 FROM wsh_location_owners
299 WHERE owner_party_id = p_party_id;
300
301 CURSOR check_party_carrier_supplier(c_party_id IN NUMBER) IS
302 SELECT 3
303 FROM wsh_carriers c
304 WHERE c.carrier_id = c_party_id
305 UNION ALL
306 SELECT 4
307 FROM hz_relationships r, po_vendors v
308 WHERE r.relationship_type = 'POS_VENDOR_PARTY' AND
309 r.subject_id = v.vendor_id AND
310 r.object_id = c_party_id;
311
312 CURSOR get_loc_owners_for_update(p_from_party_id IN NUMBER,p_from_id IN NUMBER) IS
313 SELECT owner_party_id,
314 owner_type,
315 last_update_date,
316 last_updated_by,
317 last_update_login
318 FROM wsh_location_owners
319 WHERE location_owner_id = p_from_id
320 AND owner_party_id = p_from_party_id
321 FOR UPDATE NOWAIT;
322
323 l_owner_type_from NUMBER;
324 l_owner_type_to NUMBER;
325 x_dup_pk NUMBER;
326 l_loc_owners_rec get_loc_owners_for_update%rowtype;
327
328 RESOURCE_BUSY EXCEPTION;
329 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
330
331 BEGIN
332 x_return_status := FND_API.G_RET_STS_SUCCESS;
333
334 /* If the parent has NOT change then nothing needs to be done
335 Set Merged To Id is same as Merged From Id
336 */
337 IF p_from_FK_id = p_to_FK_id THEN
338 p_to_id:=p_from_id;
339 return;
340 END IF;
341
342 /*Business Validations - Carriers cannot be merged */
343 OPEN C_Owner_Type(p_from_FK_id);
344 FETCH C_Owner_Type INTO l_owner_type_from;
345 CLOSE C_Owner_Type;
346
347 IF (l_owner_type_from=3) THEN
348 x_return_status := FND_API.G_RET_STS_ERROR;
349 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_SITE_NO_MERGE');
350 FND_MSG_PUB.ADD;
351 RETURN;
352 END IF;
353
354 l_owner_type_to := 2;
355
356 OPEN C_Owner_Type(p_to_FK_id);
357 FETCH C_Owner_Type INTO l_owner_type_to;
358
359 IF C_Owner_Type%NOTFOUND THEN
360 OPEN check_party_carrier_supplier(p_to_FK_id);
361 FETCH check_party_carrier_supplier INTO l_owner_type_to;
362 CLOSE check_party_carrier_supplier;
363
364 -- If party is carrier OR supplier,
365 -- l_owner_type will be 3 OR 4 ( <> 2)
366 -- Otherwise, value of l_owner_type will not change
367
368 IF (l_owner_type_to IS NULL) THEN
369 l_owner_type_to := 2;
370 END IF;
371 END IF;
372 CLOSE C_Owner_Type;
373 IF (l_owner_type_to=3) THEN
374 x_return_status := FND_API.G_RET_STS_ERROR;
375 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_SITE_NO_MERGE');
376 FND_MSG_PUB.ADD;
377 RETURN;
378 END IF;
379
380 check_duplicate_rec( p_from_pk => p_from_id,
381 p_to_party_id => p_To_fk_id,
382 x_dup_rec_pk => x_dup_pk,
383 x_return_status => x_return_status);
384
385 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
386
387 IF (x_dup_pk = -1) THEN
388 -- Duplicate row does not exist.
389
390 OPEN get_loc_owners_for_update( p_from_fk_id,p_from_id);
391 FETCH get_loc_owners_for_update INTO l_loc_owners_rec;
392
393 IF get_loc_owners_for_update%FOUND THEN
394 --No Wait will raise the exception
395
396 UPDATE wsh_location_owners
397 SET owner_party_id = p_to_fk_id,
398 owner_type = l_owner_type_to,
399 last_update_date = hz_utility_pub.last_update_date,
400 last_updated_by = hz_utility_pub.user_id,
401 last_update_login = hz_utility_pub.request_id
402 WHERE owner_party_id = p_from_fk_id
403 AND location_owner_id = p_from_id;
404
405 END IF;
406
407 IF get_loc_owners_for_update%ISOPEN THEN
408 CLOSE get_loc_owners_for_update;
409 END IF;
410
411 RETURN;
412 ELSE
413 -- duplicate row exists
414 p_to_id := x_dup_pk;
415 RETURN;
416 END IF;
417 ELSE
418 raise FND_API.G_EXC_UNEXPECTED_ERROR;
419 END IF;
420
421 EXCEPTION
422 WHEN RESOURCE_BUSY THEN
423 FND_MESSAGE.SET_NAME('WSH','WSH_NO_LOCK');
424 FND_MSG_PUB.ADD;
425 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
426 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
427 FND_MESSAGE.SET_NAME('AR','HZ_API_OTHERS_EXCEP');
428 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
429 FND_MSG_PUB.ADD;
430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
431 WHEN others THEN
432 IF C_Owner_Type%ISOPEN THEN
433 CLOSE C_Owner_Type;
434 END IF;
435 IF check_party_carrier_supplier%ISOPEN THEN
436 CLOSE check_party_carrier_supplier;
437 END IF;
438 IF get_loc_owners_for_update%ISOPEN THEN
439 CLOSE get_loc_owners_for_update;
440 END IF;
441 FND_MESSAGE.SET_NAME('AR','HZ_API_OTHERS_EXCEP');
442 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
443 FND_MSG_PUB.ADD;
444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
445 END merge_party_locations;
446
447 --========================================================================
448 -- PROCEDURE : Merge_supplier_sf_sites
449 -- PARAMETERS:
450 -- p_entity_name Name of registered table/entity
451 -- p_from_id Value of PK of the record being merged
452 -- x_to_id Value of the PK of the record to which this record is mapped
453 -- p_from_fk_id Value of the from ID (e.g. Party, Party Site, etc.) when merge is executed
454 -- p_to_fk_id Value of the to ID (e.g. Party, Party Site, etc.) when merge is executed
455 -- p_parent_entity_name Name of parent HZ table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
456 -- p_batch_id ID of the batch
457 -- p_batch_party_id ID of the batch and Party record
458 -- x_return_status Return status
459 --
460 -- COMMENT :
461 --========================================================================
462 Procedure Merge_supplier_sf_sites (
463 p_entity_name IN VARCHAR2,
464 p_from_id IN NUMBER,
465 x_to_id OUT NOCOPY NUMBER,
466 p_from_fk_id IN NUMBER,
467 p_to_fk_id IN NUMBER,
468 p_parent_entity_name IN VARCHAR2,
469 p_batch_id IN NUMBER,
470 p_batch_party_id IN NUMBER,
471 x_return_status OUT NOCOPY VARCHAR2 ) IS
472 --
473 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'merge_supplier_sf_sites';
474 l_debug_on BOOLEAN;
475 --
476 l_query_count NUMBER;
477 l_msg VARCHAR2(2000);
478 l_to_vendor_id NUMBER;
479 l_to_party_id NUMBER;
480 l_from_party_id NUMBER;
481 l_location_id NUMBER;
482 l_num_errors NUMBER := 0;
483 l_num_warnings NUMBER := 0;
484 l_Return_Status VARCHAR2(1);
485 --
486 CURSOR getPartyId(p_party_site_id IN NUMBER) IS
487 SELECT party_id
488 FROM hz_party_Sites
489 WHERE party_site_id = p_party_site_id;
490 --
491 CURSOR getVendorID(p_party_id IN NUMBER) IS
492 SELECT vendor_id
493 FROM po_vendors
494 WHERE party_id = p_party_id;
495 --
496 CURSOR getLocationID(p_party_site_id NUMBER) IS
497 SELECT location_id
498 FROM hz_party_sites
499 WHERE party_site_id = p_from_fk_id;
500 --
501 BEGIN
502 --{
503 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
504 --
505 IF l_debug_on IS NULL THEN
506 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
507 END IF;
508 --
509 IF l_debug_on THEN
510 WSH_DEBUG_SV.push(l_module_name);
511 WSH_DEBUG_SV.log(l_module_name, 'p_entity_name', p_entity_name);
512 WSH_DEBUG_SV.log(l_module_name, 'p_from_id', p_from_id);
513 WSH_DEBUG_SV.log(l_module_name, 'p_from_fk_id', p_from_fk_id);
514 WSH_DEBUG_SV.log(l_module_name, 'p_to_fk_id', p_to_fk_id);
515 WSH_DEBUG_SV.log(l_module_name, 'p_parent_entity_name', p_parent_entity_name);
516 WSH_DEBUG_SV.log(l_module_name, 'p_batch_id', p_batch_id);
517 WSH_DEBUG_SV.log(l_module_name, 'p_batch_party_id', p_batch_party_id);
518 END IF;
519 --
520 x_return_status := FND_API.G_RET_STS_SUCCESS;
521 --
522 IF p_from_FK_id = p_to_FK_id THEN
523 --
524 x_to_id:=p_from_id;
525 --
526 IF l_debug_on THEN
527 WSH_DEBUG_SV.logmsg(l_module_name, 'p_from_fk_id = p_to_fk_id', WSH_DEBUG_SV.C_STMT_LEVEL);
528 WSH_DEBUG_SV.log(l_module_name, 'x_to_id', x_to_id);
529 WSH_DEBUG_SV.pop(l_module_name);
530 END IF;
531 --
532 RETURN;
533 --
534 END IF;
535 --
536 IF p_from_FK_id <> p_to_FK_id THEN
537 --{
538 IF p_parent_entity_name = 'HZ_PARTY_SITES' THEN
539 --{
540 BEGIN
541 --
542 -- R12 Perf Bug 4949639 : Replace WND with WDD
543 -- since all we are looking for existence of records
544 -- with a particular SF location ID
545 --
546 SELECT 1
547 INTO l_query_count
548 FROM wsh_delivery_details wdd,
549 hz_party_Sites hps,
550 wsh_locations wl
551 WHERE hps.party_site_id = p_from_fk_id
552 AND wdd.ship_from_location_id = wl.wsh_location_id
553 AND hps.location_id = wl.source_location_id
554 AND wdd.party_id = hps.party_id
555 AND rownum =1;
556 EXCEPTION
557 WHEN NO_DATA_FOUND THEN
558 l_query_count := 0;
559 END;
560 --
561 IF l_debug_on THEN
562 WSH_DEBUG_SV.log(l_module_name, 'l_query_count', l_query_count);
563 END IF;
564 --
565 IF l_query_count > 0 THEN
566 --{
567 --Put an error messge on stack
568 fnd_message.set_name ( 'WSH', 'WSH_IB_SP_SHIP_SITE_NO_MERGE' );
569 wsh_util_core.add_message (WSH_UTIL_CORE.G_RET_STS_ERROR, l_Module_name);
570 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
571 --
572 IF l_debug_on THEN
573 WSH_DEBUG_SV.logmsg(l_module_name, 'Supplier SF sites cannot be merged', WSH_DEBUG_SV.C_STMT_LEVEL);
574 WSH_DEBUG_SV.log(l_module_name, 'x_Return_Status', x_Return_Status);
575 WSH_DEBUG_SV.pop(l_module_name);
576 END IF;
577 --
578 RETURN;
579 --}
580 ELSIF l_query_count = 0 THEN
581 --{
582 OPEN getPartyId(p_from_fk_id);
583 FETCH getPartyID INTO l_from_party_id;
584 CLOSE getPartyID;
585 --
586 IF l_debug_on THEN
587 WSH_DEBUG_SV.log(l_module_name, 'Merge From Party ID', l_from_party_id);
588 END IF;
589 --
590 OPEN getPartyID(p_to_fk_id);
591 FETCH getPartyID INTO l_to_party_id;
592 CLOSE getPartyID;
593 --
594 IF l_debug_on THEN
595 WSH_DEBUG_SV.log(l_module_name, 'Merge to Party ID', l_to_party_id);
596 END IF;
597 --
598 OPEN getVendorID(l_to_party_id);
599 FETCH getVendorID INTO l_to_vendor_id;
600 CLOSE getVendorID;
601 --
602 IF l_debug_on THEN
603 WSH_DEBUG_SV.log(l_module_name, 'Merge to Vendor ID', l_to_vendor_id);
604 END IF;
605 --
606 OPEN getLocationID(p_from_fk_id);
607 FETCH getLocationID INTO l_location_id;
608 CLOSE getLocationID;
609 --
610 IF l_debug_on THEN
611 WSH_DEBUG_SV.log(l_module_name, 'Location ID', l_location_id);
612 END IF;
613 --
614 IF l_from_party_id <> l_to_party_id THEN
615 --{
616 IF l_debug_on THEN
617 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling create_site', WSH_DEBUG_SV.C_PROC_LEVEL);
618 END IF;
619 --
620 WSH_VENDOR_PARTY_MERGE_PKG.Create_Site
621 (
622 p_from_id => l_from_party_id,
623 p_to_id => l_to_party_id,
624 p_to_vendor_id => l_to_vendor_id,
625 p_delivery_id => NULL,
626 p_delivery_name => NULL,
627 p_location_id => l_location_id,
628 x_return_status => l_return_status
629 );
630 --
631 IF l_debug_on THEN
632 WSH_DEBUG_SV.log(l_module_name, 'After calling Create_Site, return status', l_return_status);
633 END IF;
634 --
635 WSH_UTIL_CORE.api_post_call
636 (
637 p_return_status => l_return_status,
638 x_num_warnings => l_num_warnings,
639 x_num_errors => l_num_errors
640 );
641 --}
642 END IF;
643 --}
644 END IF;
645 --}
646 END IF ;
647 --}
648 END IF;
649 --
650 IF l_num_errors > 0 THEN
651 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
652 ELSIF l_num_warnings > 0 THEN
653 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
654 ELSE
655 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
656 END IF;
657 --
658 IF l_debug_on THEN
659 WSH_DEBUG_SV.log(l_module_name, 'x_return_Status', x_return_status);
660 WSH_DEBUG_SV.pop(l_module_name);
661 END IF;
662 --
663 EXCEPTION
664 WHEN OTHERS THEN
665 FND_MESSAGE.SET_NAME('AR','HZ_API_OTHERS_EXCEP');
666 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
667 FND_MSG_PUB.ADD;
668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
669 --
670 IF l_debug_on THEN
671 WSH_DEBUG_SV.log(l_module_name, 'In When Others, Error Msg is', SUBSTRB(SQLERRM, 1, 200));
672 WSH_DEBUG_SV.log(l_module_name, 'x_return_Status', x_return_status);
673 WSH_DEBUG_SV.pop(l_module_name);
674 END IF;
675 --
676
677 --}
678 END Merge_supplier_sf_sites;
679
680
681 --========================================================================
682 -- PROCEDURE : Update_Entities_During_merge
683 --
684 -- PARAMETERS:
685 --
686 -- p_to_id Merge To Vendor ID
687 -- p_from_id Merge From Vendor ID
688 -- p_from_party_id Merge From Party ID
689 -- p_to_party_id Merge To Party ID
690 -- p_to_site_id Merge To Site ID
691 -- p_from_site_id Merge From Site ID
692 -- p_site_merge Indicates whether this is a site merge
693 -- p_from_supplier_name Merge From Supplier Name
694 -- x_return_status Return status
695 --
696 --
697 -- COMMENT : This procedure is used to merge vendor level calendar assignments
698 -- during Party Merge and Vendor Merge.
699 --
700 --==========================================================================
701 PROCEDURE Update_Entities_during_Merge
702 (
703 p_to_id IN NUMBER,
704 p_from_id IN NUMBER,
705 p_from_party_id IN NUMBER,
706 p_to_party_id IN NUMBER,
707 p_to_site_id IN NUMBER,
708 p_from_site_id IN NUMBER,
709 p_site_merge IN BOOLEAN,
710 p_from_supplier_name IN VARCHAR2,
711 x_return_status OUT NOCOPY VARCHAR2
712 )
713 IS
714 --
715 CURSOR check_calendar IS
716 SELECT calendar_type,
717 calendar_assignment_id,
718 vendor_site_id,
719 association_type,
720 freight_code
721 FROM wsh_calendar_assignments a
722 WHERE vendor_id = p_from_id
723 AND vendor_site_id IS NULL;
724 --
725 CURSOR check_dup_assignment(p_vendor_id NUMBER,
726 p_calendar_Type VARCHAR2,
727 p_vendor_site_id NUMBER,
728 p_association_type VARCHAR2,
729 p_freight_code VARCHAR2 )
730 IS
731 SELECT 1
732 FROM wsh_calendar_assignments
733 WHERE vendor_id = p_vendor_id
734 AND calendar_type=p_calendar_type
735 AND nvl( vendor_site_id,-999999 ) = nvl( p_vendor_site_id,-999999 )
736 AND association_type = p_association_type
737 AND nvl( freight_code, '!!!' ) = nvl( p_freight_code, '!!!' );
738 --
739 l_debug_on BOOLEAN;
740 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.update_entities_during_merge';
741 --
742 l_msg VARCHAR2(32767);
743 l_dummy NUMBER;
744 --
745 BEGIN
746 --
747 WSH_UTIL_CORE.enable_concurrent_log_print;
748 --
749 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
750 --
751 IF l_debug_on IS NULL THEN
752 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
753 END IF;
754 --
755 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
756 --
757 IF l_debug_on THEN
758 WSH_DEBUG_SV.push(l_module_name);
759 WSH_DEBUG_SV.log(l_module_name,'P_TO_ID',p_to_id);
760 WSH_DEBUG_SV.log(l_module_name,'P_FROM_ID',p_from_id);
761 WSH_DEBUG_SV.log(l_module_name,'P_TO_PARTY_ID',p_to_party_id);
762 WSH_DEBUG_SV.log(l_module_name,'P_FROM_PARTY_ID',p_from_party_id);
763 WSH_DEBUG_SV.log(l_module_name,'P_TO_SITE_ID',p_to_site_id);
764 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SITE_ID',p_from_site_id);
765 WSH_DEBUG_SV.log(l_module_name,'P_SITE_MERGE',p_site_merge);
766 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SUPPLIER_NAME',p_from_supplier_name);
767 END IF;
768 --
769 IF NOT (p_site_merge) THEN
770 --{
771 UPDATE wsh_carriers
772 SET supplier_id = p_to_id,
773 last_update_date = sysdate,
774 last_updated_by = fnd_global.user_id,
775 last_update_login = fnd_global.login_id
776 WHERE supplier_id = p_from_id
777 AND supplier_site_id IS NULL;
778 --
779 IF l_debug_on THEN
780 wsh_debug_sv.log(l_module_name, 'No. of rows in WSH_carriers that were updated', SQL%ROWCOUNT);
781 END IF;
782 --
783 FOR check_calendar_rec IN check_calendar
784 LOOP
785 --{
786 IF l_debug_on THEN
787 --
788 WSH_DEBUG_SV.logmsg(l_module_name, '----------------------------', WSH_DEBUG_SV.C_STMT_LEVEL);
789 WSH_DEBUG_SV.log(l_module_name,'CHECK_CALENDAR_REC.CALENDAR_TYPE', check_calendar_rec.calendar_type);
790 WSH_DEBUG_SV.log(l_module_name,'CHECK_CALENDAR_REC.CALENDAR_ASSIGNMENT_ID',check_calendar_rec.calendar_assignment_id);
791 WSH_DEBUG_SV.log(l_module_name,'CHECK_CALENDAR_REC.VENDOR_SITE_ID', check_calendar_rec.vendor_site_id);
792 WSH_DEBUG_SV.log(l_module_name,'CHECK_CALENDAR_REC.ASSOCIATION_TYPE', check_calendar_rec.association_type);
793 WSH_DEBUG_SV.log(l_module_name,'CHECK_CALENDAR_REC.FREIGHT_CODE', check_calendar_rec.freight_code);
794 --
795 END IF;
796 --
797 OPEN check_dup_assignment
798 (
799 p_vendor_id => p_to_id,
800 p_calendar_Type => check_calendar_Rec.calendar_type,
801 p_vendor_site_id => check_calendar_rec.vendor_site_id,
802 p_association_type => check_calendar_rec.association_type ,
803 p_freight_code => check_calendar_rec.freight_code
804 );
805 FETCH check_dup_assignment INTO l_dummy;
806 --
807 IF (check_dup_assignment%NOTFOUND) THEN
808 --{
809 -- Update vendor level assignments
810 --
811 UPDATE wsh_calendar_assignments
812 SET vendor_id = p_to_id,
813 last_update_date = sysdate,
814 last_updated_by = fnd_global.user_id,
815 last_update_login = fnd_global.login_id
816 WHERE calendar_assignment_id = check_calendar_rec.calendar_assignment_id;
817 --
818 IF l_debug_on THEN
819 --
820 WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID updated', check_calendar_rec.calendar_assignment_id);
821 WSH_DEBUG_SV.log(l_module_name,'Number of Rows updated is', sql%rowcount);
822 --
823 END IF;
824 --}
825 ELSE
826 --{
827 --
828 DELETE wsh_calendar_assignments
829 WHERE calendar_assignment_id = check_calendar_rec.calendar_assignment_id;
830 --
831 IF l_debug_on THEN
832 --
833 WSH_DEBUG_SV.log(l_module_name, 'Deleted cal. assgn ID',
834 check_calendar_rec.calendar_assignment_id);
835 WSH_DEBUG_SV.log(l_module_name,'Number of Rows deleted is', sql%rowcount);
836 --
837 END IF;
838 --
839 IF check_calendar_rec.freight_code IS NULL THEN
840 fnd_message.set_name('WSH', 'WSH_IB_DEL_SP_CAL_ASGN' );
841 ELSE
842 fnd_message.set_name('WSH', 'WSH_IB_DEL_SP_FC_CAL_ASGN' );
843 fnd_message.set_token('FREIGHT_CODE', check_calendar_rec.freight_code);
844 END IF;
845 --
846 fnd_message.set_token('SUPPLIER_NAME' , p_from_supplier_name );
847 fnd_message.set_token('CAL_TYPE' , check_calendar_Rec.calendar_type );
848 l_msg := FND_MESSAGE.GET;
849 wsh_util_core.printMsg(l_msg);
850 --}
851 END IF;-- IF (check_dup_assignment %NOTFOUND)
852 --
853 CLOSE check_dup_assignment;
854 --}
855 END LOOP;-- FOR check_calendar_rec IN check_calendar
856 --}
857 END IF;
858 --
859 IF l_debug_on THEN
860 wsh_debug_sv.log(l_module_name, 'x_return_status', x_return_status);
861 wsh_debug_sv.pop(l_module_name);
862 END IF;
863 --
864 EXCEPTION
865 --
866 WHEN OTHERS THEN
867 --
868 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
869 wsh_util_core.default_handler('WSH_PARTY_MERGE.Update_Entities_during_merge');
870 IF l_debug_on THEN
871 --
872 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occurred.
873 occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
874 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
875 --
876 END IF;
877 --
878 END Update_Entities_During_Merge;
879
880 /* Start of comment for bug 5749968
881 --
882 -- Start : R12 FP Bug 5075838
883 --
884 -- ===============================================================================
885 -- PROCEDURE : ADJUST_WEIGHT_VOLUME
886 -- PARAMETERS :
887 -- p_entity_type CONT - While unassigning from Containers
888 -- DEL-CONT - While unassigning from Deliveries/LPN's
889 --
890 -- p_delivery_detail array of delivery detail id
891 -- p_parent_delivery_detail array of parent delivery detail id
892 -- p_delivery_id array of delivery id
893 -- p_delivery_leg_id array of delivery leg id
894 -- p_net_weight array of net weight
895 -- p_gross_weight array of gross weight
896 -- p_volume array of volume
897 -- p_inventory_item_id array inventory item id
898 -- p_organization_id array of organization id
899 -- p_weight_uom array of weight UOM code
900 -- p_volume_uom array of volume UOM code
901 -- x_return_status Returns the status of call
902 --
903 -- COMMENT :
904 -- API to decrement the delivery detail weight from LPN/Delivery while
905 -- unassigning delivery line from LPN/Delivery
906 -- Code is similar to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_From_Cont
907 -- when p_entity_type is 'CONT'
908 -- Code is similar to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_From_Delivery
909 -- when p_entity_type is 'DEL-CONT'
910 -- ===============================================================================
911 -- Bug 5606960# G-Log Changes: Removed Weight/Volume calculation while
912 -- unassigning delivery from Trip.
913
914 PROCEDURE Adjust_Weight_Volume (
915 p_entity_type IN VARCHAR2,
916 p_delivery_detail IN WSH_UTIL_CORE.Id_Tab_Type,
917 p_parent_delivery_detail IN WSH_UTIL_CORE.Id_Tab_Type,
918 p_delivery_id IN WSH_UTIL_CORE.Id_Tab_Type,
919 p_delivery_leg_id IN WSH_UTIL_CORE.Id_Tab_Type,
920 p_net_weight IN WSH_UTIL_CORE.Id_Tab_Type,
921 p_gross_weight IN WSH_UTIL_CORE.Id_Tab_Type,
922 p_volume IN WSH_UTIL_CORE.Id_Tab_Type,
923 x_return_status OUT NOCOPY VARCHAR2 )
924 IS
925 l_return_status VARCHAR2(10);
926 Weight_Volume_Exp EXCEPTION;
927
928 --
929 l_debug_on BOOLEAN;
930 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Adjust_Weight_Volume';
931 --
932 BEGIN
933
934 x_return_status := FND_API.G_RET_STS_SUCCESS;
935 --
936 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
937 --
938 IF l_debug_on IS NULL
939 THEN
940 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
941 END IF;
942 --
943 --
944 IF l_debug_on THEN
945 WSH_DEBUG_SV.push(l_module_name);
946 -- Printing the parameters passed to API
947 WSH_DEBUG_SV.log(l_module_name, 'p_entity_type', p_entity_type );
948 END IF;
949 --
950
951 -- Call Mark_Reprice_Reqired, only when Unassigning from delivery
952 -- and FTE is Installed
953 IF ( p_entity_type = 'DEL-CONT' and
954 G_FTE_INSTALLED = 'Y' )
955 THEN
956 -- { Mark Reprice
957 l_return_status := NULL;
958
959 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required (
960 p_entity_type => 'DELIVERY',
961 p_entity_ids => p_delivery_id,
962 x_return_status => l_return_status);
963
964 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
965 WSH_UTIL_CORE.G_RET_STS_WARNING) )
966 THEN
967 --
968 IF ( l_debug_on ) THEN
969 WSH_DEBUG_SV.logmsg(l_module_name, 'Error While Calling WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required');
970 WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
971 END IF;
972 --
973 RAISE Weight_Volume_Exp;
974 END IF;
975 -- } Mark Reprice
976 END IF;
977
978 IF ( p_entity_type in ( 'CONT', 'DEL-CONT' ) )
979 THEN
980 -- { Entity type
981 -- Weight/Volume adjustments
982 FOR wvCnt IN p_delivery_detail.FIRST..p_delivery_detail.LAST
983 LOOP
984 -- { W/V adjustment Loop
985 -- Call WV API, If
986 -- 1. CONT
987 -- When Unassigning from container(delivery detail is assigned to
988 -- container but not assigned to a delivery.
989 -- OR
990 -- 2. DEL-CONT
991 -- When Unassigning from delivery
992 IF ( ( p_entity_type = 'CONT' AND
993 p_delivery_id(wvCnt) IS NULL AND
994 p_parent_delivery_detail(wvCnt) IS NOT NULL ) OR
995 ( p_entity_type = 'DEL-CONT' ) )
996 THEN
997 l_return_status := NULL;
998
999 --
1000 IF l_debug_on THEN
1001 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_WV_UTILS.DD_WV_Post_Process', WSH_DEBUG_SV.C_PROC_LEVEL);
1002 END IF;
1003 --
1004
1005 WSH_WV_UTILS.DD_WV_Post_Process (
1006 p_delivery_detail_id => p_delivery_detail(wvCnt),
1007 p_diff_gross_wt => -1 * p_gross_weight(wvCnt),
1008 p_diff_net_wt => -1 * p_net_weight(wvCnt),
1009 p_diff_volume => -1 * p_volume(wvCnt),
1010 p_diff_fill_volume => -1 * p_volume(wvCnt),
1011 p_check_for_empty => 'Y',
1012 x_return_status => l_return_status );
1013
1014 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
1015 WSH_UTIL_CORE.G_RET_STS_WARNING) )
1016 THEN
1017 --
1018 IF l_debug_on THEN
1019 WSH_DEBUG_SV.logmsg(l_module_name, 'Error While Calling WSH_WV_UTILS.DD_WV_Post_Process : ' || l_return_status);
1020 END IF;
1021 --
1022 RAISE Weight_Volume_Exp;
1023 END IF;
1024 END IF;
1025 -- } W/V adjustment Loop
1026 END LOOP;
1027 -- } Entity Type
1028
1029 END IF;
1030
1031 --
1032 IF ( l_debug_on ) THEN
1033 WSH_DEBUG_SV.pop(l_module_name);
1034 END IF;
1035 --
1036
1037 EXCEPTION
1038 WHEN Weight_Volume_Exp THEN
1039 x_return_status := l_return_status;
1040 --
1041 IF l_debug_on THEN
1042 WSH_DEBUG_SV.log(l_module_name, 'Weight_Volume_Exp Exception occurred in Adjust_Weight_Volume');
1043 WSH_DEBUG_SV.pop(l_module_name);
1044 END IF;
1045 --
1046
1047 WHEN OTHERS THEN
1048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1049 --
1050 IF l_debug_on THEN
1051 WSH_DEBUG_SV.logmsg(l_module_name, 'Unexpected Error in Adjust_Weight_Volume');
1052 WSH_DEBUG_SV.log(l_module_name, 'Error Code', sqlcode);
1053 WSH_DEBUG_SV.log(l_module_name, 'Error Mesg', sqlerrm);
1054 WSH_DEBUG_SV.pop(l_module_name);
1055 END IF;
1056 --
1057 END Adjust_Weight_Volume;
1058 --
1059 --
1060 -- ===============================================================================
1061 -- PROCEDURE : ADJUST_PARENT_WV
1062 -- PARAMETERS :
1063 -- p_entity_type CONT - While unassigning from Containers
1064 -- DEL-CONT - While unassigning from Deliveries/LPN's
1065 -- p_delivery_detail array of delivery detail id
1066 -- p_parent_delivery_detail array of parent delivery detail id
1067 -- p_delivery_id array of delivery id
1068 -- p_inventory_item_id array inventory item id
1069 -- p_organization_id array of organization id
1070 -- p_weight_uom array of weight UOM code
1071 -- p_volume_uom array of volume UOM code
1072 -- x_return_status Returns the status of call
1073 --
1074 -- COMMENT :
1075 -- API to adjust the 'Fill Percent' if Percent Fill Basis is defined as Quantity.
1076 -- Code is similar to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_From_Cont
1077 -- when p_entity_type is 'CONT'
1078 -- Code is similar to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_From_Delivery
1079 -- when p_entity_type is 'DEL-CONT'
1080 -- ===============================================================================
1081 PROCEDURE Adjust_Parent_WV (
1082 p_entity_type IN VARCHAR2,
1083 p_delivery_detail IN WSH_UTIL_CORE.Id_Tab_Type,
1084 p_parent_delivery_detail IN WSH_UTIL_CORE.Id_Tab_Type,
1085 p_delivery_id IN WSH_UTIL_CORE.Id_Tab_Type,
1086 p_inventory_item_id IN WSH_UTIL_CORE.Id_Tab_Type,
1087 p_organization_id IN WSH_UTIL_CORE.Id_Tab_Type,
1088 p_weight_uom IN WSH_UTIL_CORE.Column_Tab_Type,
1089 p_volume_uom IN WSH_UTIL_CORE.Column_Tab_Type,
1090 x_return_status OUT NOCOPY VARCHAR2 )
1091 IS
1092 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
1093 l_return_status VARCHAR2(10);
1094
1095 Weight_Volume_Exp EXCEPTION;
1096
1097 --
1098 l_debug_on BOOLEAN;
1099 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || 'WSH_PARTY_MERGE' || '.' || 'Adjust_Parent_WV';
1100 --
1101 --
1102 BEGIN
1103
1104 x_return_status := FND_API.G_RET_STS_SUCCESS;
1105 --
1106 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1107 --
1108 IF l_debug_on IS NULL
1109 THEN
1110 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1111 END IF;
1112 --
1113 --
1114 IF l_debug_on THEN
1115 WSH_DEBUG_SV.push(l_module_name);
1116 -- Printing the parameters passed to API
1117 WSH_DEBUG_SV.log(l_module_name, 'p_entity_type', p_entity_type );
1118 END IF;
1119 --
1120
1121 -- Weight/Volume adjustments
1122 FOR wvCnt IN p_delivery_detail.FIRST..p_delivery_detail.LAST
1123 LOOP
1124 -- { W/V adjustment Loop
1125 -- Call WV API, If
1126 -- 1. CONT
1127 -- When Unassigning from container(i.e., delivery detail is assigned to
1128 -- container but not assigned to a delivery.
1129 -- OR
1130 -- 2. DEL-CONT
1131 -- When Unassigning from delivery
1132 IF ( ( p_entity_type = 'CONT' AND
1133 p_delivery_id(wvCnt) IS NULL AND
1134 p_parent_delivery_detail(wvCnt) IS NOT NULL ) OR
1135 ( p_entity_type = 'DEL-CONT' ) )
1136 THEN
1137 -- {
1138 l_return_status := NULL;
1139
1140 IF ( NOT G_PARAM_INFO_TAB.EXISTS(p_organization_id(wvCnt)) )
1141 THEN
1142 l_return_status := NULL;
1143
1144 --
1145 IF l_debug_on THEN
1146 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_SHIPPING_PARAMS_PVT.Get', WSH_DEBUG_SV.C_PROC_LEVEL);
1147 END IF;
1148 --
1149
1150 WSH_SHIPPING_PARAMS_PVT.Get(
1151 p_organization_id => p_organization_id(wvCnt),
1152 x_param_info => l_param_info,
1153 x_return_status => l_return_status);
1154
1155 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
1156 --
1157 IF ( l_debug_on ) THEN
1158 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_SHIPPING_PARAMS_PVT.Get returned '||l_return_status);
1159 END IF;
1160 --
1161 RAISE Weight_Volume_Exp;
1162 END IF;
1163
1164 G_PARAM_INFO_TAB(p_organization_id(wvCnt)) := l_param_info;
1165 END IF;
1166
1167 IF ( G_PARAM_INFO_TAB(p_organization_id(wvCnt)).Percent_Fill_Basis_Flag = 'Q' AND
1168 ( ( p_entity_type = 'DEL-CONT' AND p_parent_delivery_detail(wvCnt) IS NOT NULL ) OR
1169 ( p_entity_type = 'CONT' ) ) )
1170 THEN
1171 l_return_status := NULL;
1172
1173 --
1174 IF l_debug_on THEN
1175 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_WV_UTILS.Adjust_Parent_WV', WSH_DEBUG_SV.C_PROC_LEVEL);
1176 END IF;
1177 --
1178
1179 WSH_WV_UTILS.Adjust_Parent_WV(
1180 p_entity_type => 'CONTAINER',
1181 p_entity_id => p_parent_delivery_detail(wvCnt),
1182 p_gross_weight => 0,
1183 p_net_weight => 0,
1184 p_volume => 0,
1185 p_filled_volume => 0,
1186 p_wt_uom_code => p_weight_uom(wvCnt),
1187 p_vol_uom_code => p_volume_uom(wvCnt),
1188 p_inv_item_id => p_inventory_item_id(wvCnt),
1189 x_return_status => l_return_status);
1190
1191 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
1192 WSH_UTIL_CORE.G_RET_STS_WARNING) )
1193 THEN
1194 --
1195 IF l_debug_on THEN
1196 WSH_DEBUG_SV.logmsg(l_module_name, 'Error While Calling WSH_WV_UTILS.Adjust_Parent_WV : ' || l_return_status);
1197 END IF;
1198 --
1199 RAISE Weight_Volume_Exp;
1200 END IF;
1201 END IF;
1202 -- }
1203 END IF;
1204 -- } W/V adjustment Loop
1205 END LOOP;
1206
1207 --
1208 IF ( l_debug_on ) THEN
1209 WSH_DEBUG_SV.pop(l_module_name);
1210 END IF;
1211 --
1212
1213 EXCEPTION
1214 WHEN Weight_Volume_Exp THEN
1215 x_return_status := l_return_status;
1216 --
1217 IF l_debug_on THEN
1218 WSH_DEBUG_SV.log(l_module_name, 'Weight_Volume_Exp Exception occurred in Adjust_Parent_WV');
1219 WSH_DEBUG_SV.pop(l_module_name);
1220 END IF;
1221 --
1222
1223 WHEN OTHERS THEN
1224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1225 --
1226 IF l_debug_on THEN
1227 WSH_DEBUG_SV.logmsg(l_module_name, 'Unexpected Error in Adjust_Parent_WV');
1228 WSH_DEBUG_SV.log(l_module_name, 'Error Code', sqlcode);
1229 WSH_DEBUG_SV.log(l_module_name, 'Error Mesg', sqlerrm);
1230 WSH_DEBUG_SV.pop(l_module_name);
1231 END IF;
1232 --
1233 END Adjust_Parent_WV;
1234 End of comment for bug 5749968 */
1235 --
1236 --
1237 --
1238 -- PROCEDURE : GET_DELIVERY_HASH
1239 --
1240 -- DESCRIPTION :
1241 -- Get_Delivery_Hash generates new hash value and hash string for
1242 -- deliveries(from wsh_tmp table) which are to be updated with new
1243 -- Customer/Location ids
1244 --
1245 -- PARAMETERS :
1246 -- p_delivery_id => Delivery Id for which Hash String to be generated
1247 -- p_delivery_detail_id=> Delivery detail for which Hash String to be generated
1248 -- x_hash_string => Hash string generated
1249 -- x_hash_value => Hash value generatedds
1250 -- x_return_status => Return status of API
1251 -- Bug 5471560# Modified API for G-Log Changes
1252 PROCEDURE Get_Delivery_Hash (
1253 p_delivery_id IN NUMBER,
1254 p_delivery_detail_id IN NUMBER,
1255 x_hash_string OUT NOCOPY VARCHAR2,
1256 x_hash_value OUT NOCOPY NUMBER,
1257 x_return_status OUT NOCOPY VARCHAR2 )
1258 IS
1259
1260 l_grp_attr_tab_type WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
1261 l_action_code VARCHAR2(30);
1262 l_return_status VARCHAR2(1);
1263
1264
1265 Update_Hash_Exp EXCEPTION;
1266 --
1267 --
1268 l_debug_on BOOLEAN;
1269 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Delivery_Hash';
1270 --
1271 BEGIN
1272 --
1273 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1274 --
1275 IF l_debug_on IS NULL
1276 THEN
1277 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1278 END IF;
1279 --
1280 IF l_debug_on THEN
1281 WSH_DEBUG_SV.push(l_module_name);
1282 END IF;
1283 --
1284
1285 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1286
1287
1288
1289 IF ( NOT G_DELIVERY_ID.EXISTS(p_delivery_id) )
1290 THEN
1291 -- {
1292 -- Need to delete the pl/sql table since its a IN/OUT parameter
1293 -- Identified it while testing Party Merge Fix
1294 IF ( l_grp_attr_tab_type.EXISTS(1) ) THEN
1295 l_grp_attr_tab_type.DELETE(1);
1296 END IF;
1297
1298 l_grp_attr_tab_type(1).Entity_Type := 'DELIVERY_DETAIL';
1299 l_grp_attr_tab_type(1).Entity_Id := p_delivery_detail_id;
1300
1301 --
1302 IF l_debug_on THEN
1303 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_DELIVERY_AUTOCREATE.Create_Hash', WSH_DEBUG_SV.C_PROC_LEVEL);
1304 END IF;
1305 --
1306
1307 WSH_DELIVERY_AUTOCREATE.Create_Hash (
1308 p_grouping_attributes => l_grp_attr_tab_type,
1309 p_group_by_header => 'N',
1310 p_action_code => l_action_code,
1311 x_return_status => l_return_status );
1312
1313 --
1314
1315 --
1316
1317 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
1318 WSH_UTIL_CORE.G_RET_STS_WARNING) )
1319 THEN
1320 --
1321 IF ( l_debug_on ) THEN
1322 WSH_DEBUG_SV.logmsg(l_module_name, 'Error While Calling WSH_DELIVERY_AUTOCREATE.Create_Hash : ' || l_return_status);
1323 END IF;
1324 --
1325 RAISE Update_Hash_Exp;
1326 END IF;
1327
1328 x_hash_string := l_grp_attr_tab_type(1).l1_hash_string;
1329 x_hash_value := l_grp_attr_tab_type(1).l1_hash_value;
1330 G_DELIVERY_ID(p_delivery_id) := p_delivery_id;
1331 -- }
1332 END IF;
1333
1334
1335 --
1336 IF l_debug_on THEN
1337 WSH_DEBUG_SV.pop(l_module_name);
1338 END IF;
1339 --
1340 EXCEPTION
1341 WHEN Update_Hash_Exp THEN
1342 x_return_status := l_return_status;
1343 --
1344 IF ( l_debug_on ) THEN
1345 WSH_DEBUG_SV.log(l_module_name, 'Update_Hash_Exp Exception occurred in Get_Delivery_Hash');
1346 WSH_DEBUG_SV.pop(l_module_name);
1347 END IF;
1348 --
1349 WHEN OTHERS THEN
1350 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1351 --
1352 IF ( l_debug_on ) THEN
1353 WSH_DEBUG_SV.logmsg(l_module_name, 'Unexpected Error in Get_Delivery_Hash');
1354 WSH_DEBUG_SV.log(l_module_name, 'Error Code', sqlcode);
1355 WSH_DEBUG_SV.log(l_module_name, 'Error Mesg', sqlerrm);
1356 WSH_DEBUG_SV.pop(l_module_name);
1357 END IF;
1358 --
1359 END Get_Delivery_Hash;
1360 --
1361 --
1362 Procedure Check_Wms_Details (
1363 p_party_site_id IN NUMBER,
1364 p_location_id IN NUMBER,
1365 x_return_status OUT NOCOPY VARCHAR2 )
1366 IS
1367 CURSOR C1
1368 IS
1369 SELECT DISTINCT wdd.organization_id
1370 from wsh_delivery_details wdd,
1371 wsh_delivery_assignments_v wda,
1372 hz_cust_acct_sites_all ca,
1373 hz_cust_site_uses_all su
1374 where wda.parent_delivery_detail_id is not null
1375 and wda.delivery_id is null
1376 and wda.delivery_detail_id = wdd.delivery_detail_id
1377 and nvl(wdd.line_direction, 'O') in ( 'O', 'IO' )
1378 and wdd.container_flag = 'N'
1379 and wdd.released_status = 'Y'
1380 and wdd.ship_to_location_id = p_location_id
1381 and wdd.ship_to_site_use_id = su.site_use_id
1382 and su.cust_acct_site_id = ca.cust_acct_site_id
1383 and ca.party_site_id = p_party_site_id;
1384
1385 CURSOR C2
1386 IS
1387 SELECT DISTINCT wdd.organization_id
1388 from wsh_delivery_details wdd,
1389 wsh_delivery_assignments_v wda,
1390 hz_cust_acct_sites_all ca,
1391 hz_cust_site_uses_all su
1392 where wda.parent_delivery_detail_id is not null
1393 and wda.delivery_id is not null
1394 and wda.delivery_detail_id = wdd.delivery_detail_id
1395 and nvl(wdd.line_direction, 'O') in ( 'O', 'IO' )
1396 and wdd.container_flag = 'N'
1397 and wdd.released_status = 'Y'
1398 and wdd.ship_to_location_id = p_location_id
1399 and wdd.ship_to_site_use_id = su.site_use_id
1400 and su.cust_acct_site_id = ca.cust_acct_site_id
1401 and ca.party_site_id = p_party_site_id
1402 and exists
1403 ( select 'X'
1404 from wsh_delivery_details det,
1405 wsh_delivery_assignments_v asgn
1406 where det.ship_to_site_use_id <> wdd.ship_to_site_use_id
1407 and det.delivery_detail_id = asgn.delivery_detail_id
1408 and asgn.delivery_id = wda.delivery_id );
1409
1410 l_org_id_tab WSH_UTIL_CORE.Id_Tab_Type;
1411 Wms_Exception EXCEPTION;
1412 --
1413 l_debug_on BOOLEAN;
1414 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Check_Wms_Details';
1415 l_orgn_id NUMBER;
1416 --
1417 BEGIN
1418 --
1419 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1420 --
1421 IF l_debug_on IS NULL
1422 THEN
1423 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1424 END IF;
1425 --
1426 IF l_debug_on THEN
1427 WSH_DEBUG_SV.push(l_module_name);
1428 WSH_DEBUG_SV.logmsg(l_module_name, 'Party Site Id : ' || p_party_site_id || ', Location Id : ' || p_location_id);
1429 END IF;
1430 --
1431
1432 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1433
1434 OPEN C1;
1435 LOOP
1436 -- { Cursor Loop
1437 FETCH C1 BULK COLLECT INTO l_org_id_tab LIMIT G_FETCH_LIMIT;
1438
1439 IF ( l_org_id_tab.COUNT > 0 )
1440 THEN
1441 FOR orgCnt in l_org_id_tab.FIRST..l_org_id_tab.LAST
1442 LOOP
1443 IF ( NOT G_WMS_ENABLED.EXISTS(l_org_id_tab(orgCnt)) )
1444 THEN
1445 G_WMS_ENABLED(l_org_id_tab(orgCnt)) := WSH_UTIL_VALIDATE.Check_Wms_Org(l_org_id_tab(orgCnt));
1446 END IF;
1447
1448 IF ( G_WMS_ENABLED(l_org_id_tab(orgCnt)) = 'Y' )
1449 THEN
1450 --
1451 IF ( l_debug_on ) THEN
1452 WSH_DEBUG_SV.logmsg(l_module_name, 'There exists WMS records in shipping which are assigned to Containers for organization : ' || WSH_UTIL_CORE.Get_Org_Name(l_org_id_tab(orgCnt)) );
1453 END IF;
1454 --
1455 CLOSE C1;
1456 RAISE Wms_Exception;
1457 END IF;
1458 END LOOP;
1459 END IF;
1460 EXIT WHEN C1%NOTFOUND;
1461 -- } Cursor Loop
1462 END LOOP;
1463
1464 CLOSE C1;
1465
1466 OPEN C2;
1467 LOOP
1468 -- { Cursor Loop
1469 FETCH C2 BULK COLLECT INTO l_org_id_tab LIMIT G_FETCH_LIMIT;
1470
1471 IF ( l_org_id_tab.COUNT > 0 )
1472 THEN
1473 FOR orgCnt in l_org_id_tab.FIRST..l_org_id_tab.LAST
1474 LOOP
1475 IF ( NOT G_WMS_ENABLED.EXISTS(l_org_id_tab(orgCnt)) )
1476 THEN
1477 G_WMS_ENABLED(l_org_id_tab(orgCnt)) := WSH_UTIL_VALIDATE.Check_Wms_Org(l_org_id_tab(orgCnt));
1478 END IF;
1479
1480 IF ( G_WMS_ENABLED(l_org_id_tab(orgCnt)) = 'Y' )
1481 THEN
1482 --
1483 IF ( l_debug_on ) THEN
1484 WSH_DEBUG_SV.logmsg(l_module_name, 'There exists WMS records in shipping which are assigned to Containers and Deliveries for organization : ' || WSH_UTIL_CORE.Get_Org_Name(l_org_id_tab(orgCnt)) );
1485 END IF;
1486 --
1487 CLOSE C2;
1488 RAISE Wms_Exception;
1489 END IF;
1490 END LOOP;
1491 END IF;
1492 EXIT WHEN C2%NOTFOUND;
1493 -- } Cursor Loop
1494 END LOOP;
1495
1496 CLOSE C2;
1497
1498 --
1499 IF l_debug_on THEN
1500 WSH_DEBUG_SV.pop(l_module_name);
1501 END IF;
1502 --
1503 EXCEPTION
1504 WHEN Wms_Exception THEN
1505 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1506 --
1507 IF ( l_debug_on ) THEN
1508 WSH_DEBUG_SV.pop(l_module_name);
1509 END IF;
1510 --
1511 IF ( C1%ISOPEN ) THEN
1512 CLOSE C1;
1513 END IF;
1514 --
1515 IF ( C2%ISOPEN ) THEN
1516 CLOSE C2;
1517 END IF;
1518 --
1519 IF l_debug_on THEN
1520 WSH_DEBUG_SV.logmsg(l_module_name, 'WMS_Exception occurred');
1521 WSH_DEBUG_SV.pop(l_module_name);
1522 END IF;
1523 --
1524 WHEN OTHERS THEN
1525 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1526 --
1527 IF ( l_debug_on ) THEN
1528 WSH_DEBUG_SV.logmsg(l_module_name, 'Unexpected Error in Check_Wms_Details');
1529 WSH_DEBUG_SV.log(l_module_name, 'Error Code', sqlcode);
1530 WSH_DEBUG_SV.log(l_module_name, 'Error Mesg', sqlerrm);
1531 WSH_DEBUG_SV.pop(l_module_name);
1532 END IF;
1533 --
1534 IF ( C1%ISOPEN ) THEN
1535 CLOSE C1;
1536 END IF;
1537
1538 IF ( C2%ISOPEN ) THEN
1539 CLOSE C2;
1540 END IF;
1541 END Check_Wms_Details;
1542
1543
1544 --
1545 --
1546 -- Merge Locations API created for party merge
1547 -- ============================================================================
1548 -- PROCEDURE : MERGE_LOCATION
1549 -- PARAMETERS :
1550 -- p_entity_name Name of Entity Being Merged
1551 -- p_from_id Primary Key Id of the entity that is being merged
1552 -- p_to_id The record under the 'To Parent' that is being
1553 -- merged
1554 -- p_from_fk_id Foreign Key id of the Old Parent Record
1555 -- p_to_fk_id Foreign Key id of the New Parent Record
1556 -- p_parent_entity_name Name of Parent Entity
1557 -- p_batch_id Id of the Batch
1558 -- p_batch_party_id Id uniquely identifies the batch and party record
1559 -- that is being merged
1560 -- x_return_status Returns the status of call
1561 --
1562 -- COMMENT :
1563 -- To update locations in Wsh_Delivery_Details, Wsh_New_Deliveries,
1564 -- Wsh_Trip_Stops tables for Unshipped delivery lines during party
1565 -- merge. Also updates Wsh_Picking_Rules tables during party merge.
1566 -- ============================================================================
1567 PROCEDURE merge_location(
1568 p_entity_name IN VARCHAR2,
1569 p_from_id IN NUMBER,
1570 p_to_id IN OUT NOCOPY NUMBER,
1571 p_from_fk_id IN NUMBER,
1572 p_to_fk_id IN NUMBER,
1573 p_parent_entity_name IN VARCHAR2,
1574 p_batch_id IN NUMBER,
1575 p_batch_party_id IN NUMBER,
1576 x_return_status IN OUT NOCOPY VARCHAR2)
1577 IS
1578
1579 -- Cursor to fetch the location id from Hz_Party_Sites table.
1580 CURSOR Get_Location_Id ( p_party_site_id NUMBER )
1581 IS
1582 SELECT Party_Id, Party_Site_Id, Location_Id
1583 FROM HZ_PARTY_SITES HPS
1584 WHERE PARTY_SITE_ID = p_party_site_id;
1585
1586 -- Fetches delivery details with new party sites and old locations.
1587 -- Old locations to be updated with new locations in Shipping
1588 -- tables for records fetched by Cursor.
1589 -- Cursor fetches only delivery details which as deliver_to_site_use_id
1590 -- same as ship_to_site_use_id or deliver_to_site_use_id is NULL since
1591 -- we populate deliver_to_location_id same as ship_to_location id if
1592 -- deliver_to_site_use_id is NULL.
1593 -- Cursor does not select container records
1594 -- Added following condition for Bug 4247177
1595 -- "AND NVL(WTS.Stop_Location_Id, WDD.Ship_To_Location_Id) = WDD.Ship_To_Location_Id"
1596 -- Above condition will eliminate drop off stop with different stop location, if multiple
1597 -- legs are present in a delivery.
1598 CURSOR Get_Wsh_Details ( p_party_site_id NUMBER, p_location_id NUMBER )
1599 IS
1600 SELECT Wdd.Rowid Del_Detail_Rowid, Wnd.RowId Delivery_RowId, Wts.Rowid Stop_RowId,
1601 Wnd.Delivery_Id Delivery_Id, Wts.Stop_Id Stop_Id,
1602 Wdd.Delivery_Detail_Id Delivery_Detail_Id,
1603 Wda.Parent_Delivery_Detail_Id Parent_Delivery_Detail_Id,
1604 Wdd.Net_Weight, Wdd.Gross_Weight, Wdd.Volume,
1605 Wdd.Weight_Uom_code, Wdd.Volume_Uom_Code, Wdd.Inventory_Item_Id,
1606 Wdd.Organization_Id, WDA.Rowid Wda_Rowid
1607 FROM WSH_DELIVERY_DETAILS WDD,
1608 WSH_DELIVERY_ASSIGNMENTS_V WDA,
1609 WSH_NEW_DELIVERIES WND,
1610 WSH_DELIVERY_LEGS WDL,
1611 WSH_TRIP_STOPS WTS,
1612 HZ_CUST_ACCT_SITES_ALL CA,
1613 HZ_CUST_SITE_USES_ALL SU
1614 WHERE SU.Cust_Acct_Site_Id = CA.Cust_Acct_Site_Id
1615 AND CA.Party_Site_Id = p_party_site_id
1616 AND WDD.Container_Flag = 'N'
1617 AND NVL(WDD.Line_Direction, 'O') in ( 'O', 'IO' )
1618 AND WDD.Ship_To_Location_id = p_location_id
1619 AND WDD.Ship_To_Site_Use_Id = SU.Site_Use_Id
1620 AND WDD.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
1621 AND WDA.Delivery_Detail_Id = WDD.Delivery_Detail_Id
1622 AND NVL(WND.Status_Code, 'OP') IN ( 'OP', 'CO' )
1623 AND WND.Delivery_Id (+) = WDA.Delivery_Id
1624 AND WDL.Delivery_Id (+) = WND.Delivery_Id
1625 AND NVL(WTS.Stop_Location_Id, WDD.Ship_To_Location_Id) = WDD.Ship_To_Location_Id
1626 AND NVL(WTS.Status_Code, 'OP') = 'OP'
1627 AND WTS.Stop_Id (+) = WDL.Drop_Off_Stop_Id
1628 FOR UPDATE of Wdd.Delivery_Detail_Id, Wnd.Delivery_Id, Wts.Stop_Id NOWAIT;
1629
1630 CURSOR Get_Wsh_Unassign_Details ( p_party_site_id NUMBER, p_location_id NUMBER )
1631 IS
1632 SELECT Wda.rowid Del_Assignments_Rowid, Wda.Delivery_id,
1633 Wdd.Delivery_Detail_Id Delivery_Detail_Id,
1634 Wda.Parent_Delivery_Detail_Id Parent_Delivery_Detail_Id,
1635 Wdd.Net_Weight, Wdd.Gross_Weight, Wdd.Volume,
1636 Wdd.Weight_Uom_code, Wdd.Volume_Uom_Code, Wdd.Inventory_Item_Id,
1637 Wdd.Organization_Id, Wnd.Name Delivery_Name,
1638 Wdd.Move_Order_Line_Id, Wdd.Released_Status
1639 FROM WSH_DELIVERY_DETAILS WDD,
1640 WSH_DELIVERY_ASSIGNMENTS_V WDA,
1641 WSH_NEW_DELIVERIES WND,
1642 HZ_CUST_ACCT_SITES_ALL CA,
1643 HZ_CUST_SITE_USES_ALL SU
1644 WHERE SU.Cust_Acct_Site_Id = CA.Cust_Acct_Site_Id
1645 AND CA.Party_Site_Id = p_party_site_id
1646 AND WDD.Container_Flag = 'N'
1647 AND NVL(WDD.Line_Direction, 'O') in ( 'O', 'IO' )
1648 AND WDD.Ship_To_Location_id = p_location_id
1649 AND WDD.Ship_To_Site_Use_Id = SU.Site_Use_Id
1650 AND WDD.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
1651 AND WND.Ultimate_Dropoff_Location_Id <> p_location_id
1652 AND WDA.Delivery_Detail_Id = WDD.Delivery_Detail_Id
1653 AND WND.Status_Code IN ( 'OP', 'CO' )
1654 AND WDA.delivery_id is not null
1655 AND WND.Delivery_Id = WDA.Delivery_Id
1656 AND exists (
1657 SELECT 'x'
1658 FROM WSH_DELIVERY_ASSIGNMENTS_V WDA1,
1659 WSH_DELIVERY_DETAILS WDD1
1660 WHERE WDD1.DELIVERY_DETAIL_ID = WDA1.DELIVERY_DETAIL_ID
1661 AND WDD1.Container_Flag = 'N'
1662 AND WDA1.Delivery_Id = WND.Delivery_Id
1663 AND WDD1.Ship_To_Location_id = WND.Ultimate_Dropoff_Location_Id)
1664 FOR UPDATE OF Wda.Delivery_Detail_Id, Wnd.Delivery_Id NOWAIT;
1665
1666 CURSOR Get_Delivery_Containers
1667 IS
1668 SELECT Wdd.Rowid
1669 FROM Wsh_Delivery_Details Wdd,
1670 Wsh_Delivery_Assignments_V Wda,
1671 Wsh_Tmp Tmp
1672 WHERE Wdd.container_flag = 'Y'
1673 AND NVL(WDD.Line_Direction, 'O') in ( 'O', 'IO' )
1674 AND Wdd.delivery_detail_id = Wda.Delivery_Detail_Id
1675 AND Wda.Delivery_Id = Tmp.Column1
1676 FOR UPDATE OF Wdd.Delivery_Detail_id NOWAIT;
1677
1678 --Bug 5606960# G-Log Changes: Included Wnd.organization_id in query
1679 CURSOR Get_Del_Unassign_From_Stop (
1680 p_to_location_id NUMBER,
1681 p_from_location_id NUMBER )
1682 IS
1683 SELECT Wdl.Delivery_Id, Wts.Stop_Id, Wts.Trip_Id,
1684 Wdl.Delivery_Leg_Id, Wnd.organization_id,
1685 Wnd.Net_Weight, Wnd.Gross_Weight, Wnd.Volume,
1686 Wdl.Rowid, Tmp.Rowid
1687 FROM Wsh_Trip_Stops Wts,
1688 Wsh_New_Deliveries Wnd,
1689 Wsh_Delivery_Legs Wdl,
1690 Wsh_Tmp Tmp
1691 WHERE Wnd.Ultimate_DropOff_Location_Id = p_to_location_id
1692 AND nvl(Wnd.Shipment_Direction, 'O') in ( 'O', 'IO' )
1693 AND Wnd.Delivery_Id = Wdl.Delivery_Id
1694 AND Wts.Stop_Location_Id = p_from_location_id
1695 AND Wts.Stop_Id = Wdl.Drop_Off_Stop_Id
1696 AND Wdl.Delivery_Id = Tmp.Column1
1697 AND exists (
1698 SELECT 'x'
1699 FROM Wsh_New_Deliveries Del,
1700 Wsh_Delivery_Legs Legs
1701 WHERE Del.Ultimate_Dropoff_Location_Id <> p_to_location_id
1702 AND Del.Delivery_Id = Legs.Delivery_Id
1703 AND Legs.Drop_Off_Stop_Id = Wdl.Drop_Off_Stop_Id )
1704 FOR UPDATE OF Wdl.Delivery_Leg_Id NOWAIT;
1705
1706
1707 -- Cursor fetches delivery details which has deliver_to_site_use_id
1708 -- different from ship_to_site_use_id.
1709 CURSOR Get_Deliver_Loc_Details ( p_party_site_id NUMBER, p_location_id NUMBER )
1710 IS
1711 SELECT WDD.Rowid
1712 FROM WSH_DELIVERY_DETAILS WDD,
1713 HZ_CUST_ACCT_SITES_ALL CA,
1714 HZ_CUST_SITE_USES_ALL SU
1715 WHERE SU.Cust_Acct_Site_Id = CA.Cust_Acct_Site_Id
1716 AND CA.Party_Site_Id = p_party_site_id
1717 AND NVL(WDD.Line_Direction, 'O') in ( 'O', 'IO' )
1718 AND WDD.Deliver_To_Location_id = p_location_id
1719 AND WDD.Deliver_To_Site_Use_Id = SU.Site_Use_Id
1720 AND WDD.Deliver_To_Site_Use_Id <> WDD.Ship_To_Site_Use_Id
1721 AND WDD.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
1722 FOR UPDATE of Wdd.Delivery_Detail_Id NOWAIT;
1723
1724 -- Cursor to update deliver_to_location for container records.
1725 CURSOR Get_Del_Loc_Cont_Details ( p_party_site_id NUMBER, p_location_id NUMBER )
1726 IS
1727 SELECT WDA.Parent_Delivery_Detail_Id
1728 FROM WSH_DELIVERY_ASSIGNMENTS_V WDA
1729 WHERE WDA.Parent_Delivery_Detail_Id IS NOT NULL
1730 CONNECT BY PRIOR WDA.Parent_Delivery_Detail_Id = WDA.Delivery_Detail_Id
1731 START WITH wda.delivery_detail_id IN
1732 ( SELECT WDD.Delivery_Detail_Id
1733 FROM WSH_DELIVERY_DETAILS WDD,
1734 HZ_CUST_ACCT_SITES_ALL CA,
1735 HZ_CUST_SITE_USES_ALL SU
1736 WHERE SU.Cust_Acct_Site_Id = CA.Cust_Acct_Site_Id
1737 AND CA.Party_Site_Id = p_party_site_id
1738 AND WDD.Container_Flag = 'N'
1739 AND NVL(WDD.Line_Direction, 'O') in ( 'O', 'IO' )
1740 AND WDD.Deliver_To_Location_id = p_location_id
1741 AND WDD.Deliver_To_Site_Use_Id = SU.Site_Use_Id
1742 AND WDD.Deliver_To_Site_Use_Id <> WDD.Ship_To_Site_Use_Id
1743 AND WDD.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' ) )
1744 FOR UPDATE OF Wda.Delivery_Detail_Id;
1745
1746 CURSOR Get_Grouping_Id is
1747 SELECT Wsh_Delivery_Group_S.NEXTVAL
1748 FROM Dual;
1749
1750 -- Bug 5606960# Cursor added for G-Log Changes
1751 CURSOR Get_Tmp_Deliveries
1752 IS
1753 SELECT wnd.delivery_id, wnd.rowid, to_number(wt.Column3) delivery_detail_id
1754 FROM Wsh_Tmp wt,
1755 Wsh_New_Deliveries Wnd
1756 WHERE Wnd.Delivery_Id = to_number(Wt.Column1);
1757
1758 -- Create Pl/Sql table type for storing RowIds
1759 TYPE Rowid_Tab_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1760 l_to_location_id NUMBER;
1761 l_to_party_id NUMBER;
1762 l_to_party_site_id NUMBER;
1763 l_from_party_id NUMBER;
1764 l_from_party_site_id NUMBER;
1765 l_from_location_id NUMBER;
1766 l_tmp_cnt NUMBER;
1767 l_msg_count NUMBER;
1768 l_carton_grouping_id NUMBER;
1769 l_exception_id NUMBER;
1770 l_msg_data VARCHAR2(32767);
1771 l_message_name VARCHAR2(50);
1772 l_message_text VARCHAR2(32767);
1773 l_return_status VARCHAR2(10);
1774
1775 l_del_detail_rowid_tab Rowid_Tab_Type;
1776 l_del_assignments_rowid_tab Rowid_Tab_Type;
1777 l_delivery_rowid_tab Rowid_Tab_Type;
1778 l_stop_rowid_tab Rowid_Tab_Type;
1779 l_legs_rowid_tab Rowid_Tab_Type;
1780 l_tmp_rowid_tab Rowid_Tab_Type;
1781
1782 l_delivery_name_tab WSH_UTIL_CORE.Column_Tab_Type;
1783 l_weight_uom_tab WSH_UTIL_CORE.Column_Tab_Type;
1784 l_volume_uom_tab WSH_UTIL_CORE.Column_Tab_Type;
1785 l_released_status_tab WSH_UTIL_CORE.Column_Tab_Type;
1786
1787 l_del_detail_id_tab WSH_UTIL_CORE.Id_Tab_Type;
1788 l_parent_del_detail_id_tab WSH_UTIL_CORE.Id_Tab_Type;
1789 l_delivery_id_tab WSH_UTIL_CORE.Id_Tab_Type;
1790 l_delivery_leg_id_tab WSH_UTIL_CORE.Id_Tab_Type;
1791 l_stop_id_tab WSH_UTIL_CORE.Id_Tab_Type;
1792 l_trip_id_tab WSH_UTIL_CORE.Id_Tab_Type;
1793 l_net_weight_tab WSH_UTIL_CORE.Id_Tab_Type;
1794 l_gross_weight_tab WSH_UTIL_CORE.Id_Tab_Type;
1795 l_volume_tab WSH_UTIL_CORE.Id_Tab_Type;
1796 l_organization_id_tab WSH_UTIL_CORE.Id_Tab_Type;
1797 l_inventory_item_id_tab WSH_UTIL_CORE.Id_Tab_Type;
1798 l_move_order_line_id_tab WSH_UTIL_CORE.Id_Tab_Type;
1799
1800 --Bug 5606960# Variable(s) added for G-Log changes
1801 l_delivery_rec WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
1802 l_hash_string VARCHAR2(100);
1803 l_hash_value NUMBER;
1804 l_del_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
1805 l_del_attrs WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1806 l_del_action_rec WSH_DELIVERIES_GRP.delivery_action_out_rec_type;
1807 l_del_defaults WSH_DELIVERIES_GRP.default_parameters_rectype;
1808
1809 l_loc_rec WSH_MAP_LOCATION_REGION_PKG.loc_rec_type;
1810
1811 Merge_Location_Exp EXCEPTION;
1812
1813 --
1814 l_debug_on BOOLEAN;
1815 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MERGE_LOCATION';
1816 --
1817
1818 BEGIN
1819
1820 x_return_status := FND_API.G_RET_STS_SUCCESS;
1821 --
1822 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1823 --
1824 IF l_debug_on IS NULL
1825 THEN
1826 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1827 END IF;
1828 --
1829
1830 --
1831 IF l_debug_on THEN
1832 WSH_DEBUG_SV.push(l_module_name);
1833
1834 -- Printing the parameters passed to API
1835 WSH_DEBUG_SV.log(l_module_name,'WSH_PARTY_MERGE.merge_location()+', TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
1836 WSH_DEBUG_SV.log(l_module_name, 'p_entity_name', p_entity_name);
1837 WSH_DEBUG_SV.log(l_module_name, 'p_from_id', p_from_id);
1838 WSH_DEBUG_SV.log(l_module_name, 'p_to_id', p_to_id);
1839 WSH_DEBUG_SV.log(l_module_name, 'p_from_fk_id', p_from_fk_id);
1840 WSH_DEBUG_SV.log(l_module_name, 'p_to_fk_id', p_to_fk_id);
1841 WSH_DEBUG_SV.log(l_module_name, 'p_parent_entity_name', p_parent_entity_name);
1842 WSH_DEBUG_SV.log(l_module_name, 'p_batch_id', p_batch_id);
1843 WSH_DEBUG_SV.log(l_module_name, 'p_batch_party_id', p_batch_party_id);
1844 END IF;
1845 --
1846
1847 -- Get To_Location_Id which is to be updated in Shipping tables.
1848 OPEN Get_Location_Id ( p_to_fk_id );
1849 FETCH Get_Location_Id INTO l_to_party_id, l_to_party_site_id, l_to_location_id;
1850 IF Get_Location_Id%NOTFOUND
1851 THEN -- {
1852 -- Error Handling Part
1853 IF l_debug_on THEN
1854 WSH_DEBUG_SV.log(l_module_name, 'Invaild To Party Site Id', p_to_fk_id);
1855 END IF;
1856 --
1857 x_return_status := FND_API.G_RET_STS_ERROR;
1858 CLOSE Get_Location_Id;
1859 RAISE Merge_Location_Exp;
1860 END IF; -- }
1861 CLOSE Get_Location_Id;
1862
1863
1864 -- Get From_Location_Id from Hz_Party_Sites
1865 OPEN Get_Location_Id ( p_from_fk_id );
1866 FETCH Get_Location_Id INTO l_from_party_id, l_from_party_site_id, l_from_location_id;
1867 IF Get_Location_Id%NOTFOUND
1868 THEN -- {
1869 -- Error Handling Part
1870 IF l_debug_on THEN
1871 WSH_DEBUG_SV.log(l_module_name, 'Invalid From Party Site Id', p_from_fk_id);
1872 END IF;
1873 --
1874 x_return_status := FND_API.G_RET_STS_ERROR;
1875 CLOSE Get_Location_Id;
1876 RAISE Merge_Location_Exp;
1877 END IF; -- }
1878 CLOSE Get_Location_Id;
1879
1880 --
1881 IF l_debug_on THEN
1882 WSH_DEBUG_SV.log(l_module_name, 'To Party Id', l_to_party_id);
1883 WSH_DEBUG_SV.log(l_module_name, 'To Location Id', l_to_location_id);
1884 WSH_DEBUG_SV.log(l_module_name, 'From Party Id', l_from_party_id);
1885 WSH_DEBUG_SV.log(l_module_name, 'From Location Id', l_from_location_id);
1886 END IF;
1887 --
1888
1889 -- If FROM and TO Locations are same return Success.
1890 IF ( l_from_location_id = l_to_location_id )
1891 THEN
1892 x_return_status := FND_API.G_RET_STS_SUCCESS;
1893 --
1894 IF l_debug_on THEN
1895 WSH_DEBUG_SV.log(l_module_name, 'From and To Location IDs are the same');
1896 WSH_DEBUG_SV.pop(l_module_name);
1897 END IF;
1898 --
1899 RETURN;
1900 END IF;
1901
1902 -- Deleting records from from temp table before processing
1903 DELETE FROM WSH_TMP;
1904
1905
1906 --
1907 IF l_debug_on THEN
1908 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_MAP_LOCATION_REGION_PKG.Transfer_Location', WSH_DEBUG_SV.C_PROC_LEVEL);
1909 END IF;
1910 --
1911
1912 -- Check whether the To_Location exists in WSH_LOCATIONS table
1913 WSH_MAP_LOCATION_REGION_PKG.Transfer_Location(
1914 p_source_type => 'HZ',
1915 p_source_location_id => l_to_location_id,
1916 p_transfer_location => TRUE,
1917 p_online_region_mapping => FALSE,
1918 x_loc_rec => l_loc_rec,
1919 x_return_status => l_return_status );
1920
1921 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1922 THEN -- { Error Handling
1923 --
1924 IF l_debug_on THEN
1925 WSH_DEBUG_SV.logmsg(l_module_name, 'Error While Calling WSH_MAP_LOCATION_REGION_PKG.Transfer_Location : ' || l_return_status);
1926 END IF;
1927 --
1928 x_return_status := l_return_status;
1929 RAISE Merge_Location_Exp;
1930 END IF; -- } Error Handling
1931
1932 -- Check whether FTE is Installed
1933 IF ( G_FTE_INSTALLED IS NULL )
1934 THEN
1935 G_FTE_INSTALLED := WSH_UTIL_CORE.Fte_Is_Installed;
1936 END IF;
1937
1938 -- Check for WMS records
1939 l_return_status := NULL;
1940 --
1941 IF l_debug_on THEN
1942 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Check_Wms_Details', WSH_DEBUG_SV.C_PROC_LEVEL);
1943 END IF;
1944 --
1945
1946 WSH_PARTY_MERGE.Check_Wms_Details (
1947 p_party_site_id => l_to_party_site_id,
1948 p_location_id => l_from_location_id,
1949 x_return_status => l_return_status );
1950
1951 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1952 THEN
1953 -- { Error Handling
1954 --
1955 IF l_debug_on THEN
1956 WSH_DEBUG_SV.logmsg(l_module_name, 'Error While Calling WSH_PARTY_MERGE.Check_Wms_Details : ' || l_return_status);
1957 END IF;
1958 --
1959 x_return_status := l_return_status;
1960 RAISE Merge_Location_Exp;
1961 END IF; -- } Error Handling
1962
1963 --
1964 IF ( l_debug_on ) THEN
1965 WSH_DEBUG_SV.log(l_module_name, 'Starting updates on shipping tables', TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
1966 END IF;
1967 --
1968
1969 -- Get Shipping details based on ship_to_location for which the location
1970 -- has to be updated during Party Merge.
1971 -- Passing l_to_party_site_id, since party_site_id is already updated
1972 -- in HZ_CUST_ACCT_SITES_ALL API before calling our API.
1973 OPEN Get_Wsh_Details ( l_to_party_site_id, l_from_location_id );
1974
1975 LOOP -- {
1976 FETCH Get_Wsh_Details BULK COLLECT INTO
1977 l_del_detail_rowid_tab,
1978 l_delivery_rowid_tab,
1979 l_stop_rowid_tab,
1980 l_delivery_id_tab,
1981 l_stop_id_tab,
1982 l_del_detail_id_tab,
1983 l_parent_del_detail_id_tab,
1984 l_net_weight_tab,
1985 l_gross_weight_tab,
1986 l_volume_tab,
1987 l_weight_uom_tab,
1988 l_volume_uom_tab,
1989 l_inventory_item_id_tab,
1990 l_organization_id_tab,
1991 l_del_assignments_rowid_tab
1992 LIMIT G_FETCH_LIMIT;
1993
1994 --
1995 IF ( l_debug_on ) THEN
1996 WSH_DEBUG_SV.log(l_module_name, 'No of rows fetched from Get_Wsh_Details', l_del_detail_rowid_tab.COUNT);
1997 END IF;
1998 --
1999
2000 -- Update Ship_to_Location and Deliver_to_Location in Wsh_Delivery_Details
2001 -- table during party merge.
2002
2003 IF ( l_del_detail_rowid_tab.COUNT > 0 )
2004 THEN -- { WDD Update
2005 FORALL i IN l_del_detail_rowid_tab.FIRST..l_del_detail_rowid_tab.LAST
2006 UPDATE WSH_DELIVERY_DETAILS Wdd
2007 SET ship_to_location_id = l_to_location_id,
2008 deliver_to_location_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
2009 ship_to_site_use_id, l_to_location_id,
2010 deliver_to_location_id ),
2011 last_update_date = SYSDATE,
2012 last_updated_by = fnd_global.user_id,
2013 last_update_login = fnd_global.conc_login_id,
2014 program_application_id = fnd_global.prog_appl_id,
2015 program_id = fnd_global.conc_program_id,
2016 program_update_date = SYSDATE
2017 WHERE WDD.Rowid = l_del_detail_rowid_tab(i);
2018
2019 --
2020 IF l_debug_on THEN
2021 WSH_DEBUG_SV.logmsg(l_module_name, 'No of Rows Updated in WDD : '
2022 || sql%rowcount || ', Time : '
2023 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2024 END IF;
2025 --
2026 END IF; -- } WDD Update
2027
2028 IF ( l_del_assignments_rowid_tab.COUNT > 0 )
2029 THEN -- { WDA Update
2030
2031 l_return_status := NULL;
2032 l_delivery_leg_id_tab.delete;
2033
2034 /****
2035 FORALL i IN l_del_assignments_rowid_tab.FIRST..l_del_assignments_rowid_tab.LAST
2036 UPDATE WSH_DELIVERY_ASSIGNMENTS
2037 SET parent_delivery_detail_id = null,
2038 last_update_date = SYSDATE,
2039 last_updated_by = fnd_global.user_id,
2040 last_update_login = fnd_global.conc_login_id,
2041 program_application_id = fnd_global.prog_appl_id,
2042 program_id = fnd_global.conc_program_id,
2043 program_update_date = SYSDATE
2044 WHERE rowid = l_del_assignments_rowid_tab(i)
2045 AND Parent_Delivery_Detail_Id IS NOT NULL
2046 AND Delivery_Id IS NULL;
2047 ****/
2048
2049 --Start of fix for bug 5749968
2050 --
2051 IF l_debug_on THEN
2052 WSH_DEBUG_SV.logmsg(l_module_name, 'Before calling container unassign standard api: '
2053 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2054 END IF;
2055 --
2056
2057 FOR unassignCnt in l_del_detail_id_tab.FIRST..l_del_detail_id_tab.LAST
2058 LOOP
2059 --Unassign from container only if delivery detail is packed and
2060 --not assigned to delivery.
2061 IF ( l_parent_del_detail_id_tab(unassignCnt) IS NOT NULL AND
2062 l_delivery_id_tab(unassignCnt) IS NULL )
2063 THEN
2064 l_return_status := NULL;
2065 -- Calling Standard api to unassign detail from container
2066 WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_Cont (
2067 p_detail_id => l_del_detail_id_tab(unassignCnt),
2068 x_return_status => l_return_status );
2069
2070 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
2071 WSH_UTIL_CORE.G_RET_STS_WARNING) )
2072 THEN
2073 --
2074 IF l_debug_on THEN
2075 WSH_DEBUG_SV.logmsg(l_module_name, 'Unassign_Detail_from_Cont returned error : ' || l_return_status);
2076 WSH_DEBUG_SV.logmsg(l_module_name, 'Error while processing delivery detail : ' || l_del_detail_id_tab(unassignCnt) );
2077 END IF;
2078 --
2079 x_return_status := l_return_status;
2080 RAISE Merge_Location_Exp;
2081 END IF;
2082 END IF;
2083 END LOOP;
2084
2085 --
2086 IF l_debug_on THEN
2087 WSH_DEBUG_SV.logmsg(l_module_name, 'After calling container unassign standard api: '
2088 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2089 END IF;
2090 --
2091 --End of fix for bug 5749968
2092 END IF; -- } WDA Update
2093
2094 IF ( l_delivery_id_tab.COUNT > 0 ) THEN
2095 -- Inserting records in bulk into temp table for future reference during processing
2096 -- Dulplicate entries are avoided using NOT EXISTS condition
2097 FORALL i IN l_delivery_id_tab.FIRST..l_delivery_id_tab.LAST
2098 INSERT INTO wsh_tmp(column1, column2, column3)
2099 SELECT l_delivery_id_tab(i), l_stop_id_tab(i), l_del_detail_id_tab(i)
2100 FROM dual
2101 WHERE l_delivery_id_tab(i) is not null
2102 AND NOT EXISTS
2103 ( SELECT 'x'
2104 FROM Wsh_Tmp
2105 WHERE Column1 = l_delivery_id_tab(i)
2106 AND ( Column2 = l_stop_id_tab(i) OR l_stop_id_tab(i) IS NULL ) );
2107
2108 --
2109 IF ( l_debug_on ) THEN
2110 WSH_DEBUG_SV.logmsg(l_module_name, 'No of rows inserted into wsh_temp : '
2111 || sql%rowcount || ', Time : '
2112 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2113 END IF;
2114 --
2115 END IF;
2116
2117 -- Logging Exceptions for Delivery Details Unassigned from LPNs
2118 IF ( l_parent_del_detail_id_tab.COUNT > 0 )
2119 THEN -- { Log Exception
2120
2121 l_message_name := 'WSH_PMRG_UNASSIGN_CONTAINER';
2122
2123 FOR ExpCnt in l_parent_del_detail_id_tab.FIRST..l_parent_del_detail_id_tab.LAST
2124 LOOP -- { Loop for logging Expceptions
2125
2126 -- We should log exceptions only if Parent Delivery Detail IS NOT NULL
2127 -- and is not assigned to a delivery
2128 IF ( l_parent_del_detail_id_tab(ExpCnt) IS NOT NULL AND
2129 l_delivery_id_tab(ExpCnt) IS NULL )
2130 THEN -- {
2131 -- Setting the Messages
2132 FND_MESSAGE.Set_Name ('WSH', l_message_name );
2133 FND_MESSAGE.Set_Token ('PS1', p_from_fk_id );
2134 FND_MESSAGE.Set_Token ('PS2', p_to_fk_id );
2135 FND_MESSAGE.Set_Token ('DELIVERY_DETAIL_ID', l_del_detail_id_tab(ExpCnt) );
2136
2137 l_message_text := FND_MESSAGE.Get;
2138
2139 --
2140 IF l_debug_on THEN
2141 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_XC_UTIL.Log_Exception', WSH_DEBUG_SV.C_PROC_LEVEL);
2142 END IF;
2143 --
2144
2145 l_return_status := NULL;
2146 l_msg_count := NULL;
2147 l_msg_data := NULL;
2148 l_exception_id := NULL;
2149
2150 WSH_XC_UTIL.Log_Exception
2151 (
2152 p_api_version => 1.0,
2153 x_return_status => l_return_status,
2154 x_msg_count => l_msg_count,
2155 x_msg_data => l_msg_data,
2156 x_exception_id => l_exception_id,
2157 p_exception_location_id => l_to_location_id,
2158 p_logged_at_location_id => l_to_location_id,
2159 p_logging_entity => 'SHIPPER',
2160 p_logging_entity_id => Fnd_Global.user_id,
2161 p_exception_name => 'WSH_PARTY_MERGE_CHANGE',
2162 p_message => l_message_name,
2163 p_severity => 'LOW',
2164 p_manually_logged => 'N',
2165 p_delivery_detail_id => l_parent_del_detail_id_tab(ExpCnt),
2166 p_error_message => l_message_text
2167 );
2168
2169 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2170 THEN
2171 --
2172 IF l_debug_on THEN
2173 WSH_DEBUG_SV.logmsg(l_module_name, 'Error While Calling WSH_XC_UTIL.Log_Exception : ' || l_return_status);
2174 END IF;
2175 --
2176 x_return_status := l_return_status;
2177 RAISE Merge_Location_Exp;
2178 END IF;
2179
2180 END IF; -- }
2181 END LOOP; -- } Loop for logging Expceptions
2182
2183
2184 END IF; -- } Log Exception
2185
2186 EXIT WHEN Get_Wsh_Details%NOTFOUND;
2187 END LOOP; -- }
2188
2189 CLOSE Get_Wsh_Details;
2190
2191 OPEN Get_Wsh_Unassign_Details ( l_to_party_site_id, l_to_location_id );
2192
2193 LOOP -- {
2194 FETCH Get_Wsh_Unassign_Details BULK COLLECT INTO
2195 l_del_assignments_rowid_tab,
2196 l_delivery_id_tab,
2197 l_del_detail_id_tab,
2198 l_parent_del_detail_id_tab,
2199 l_net_weight_tab,
2200 l_gross_weight_tab,
2201 l_volume_tab,
2202 l_weight_uom_tab,
2203 l_volume_uom_tab,
2204 l_inventory_item_id_tab,
2205 l_organization_id_tab,
2206 l_delivery_name_tab,
2207 l_move_order_line_id_tab,
2208 l_released_status_tab
2209 LIMIT G_FETCH_LIMIT;
2210
2211 --
2212 IF ( l_debug_on ) THEN
2213 WSH_DEBUG_SV.log(l_module_name, 'No of rows fetched from Get_Wsh_Unassign_Details', l_del_assignments_rowid_tab.COUNT);
2214 END IF;
2215 --
2216
2217 IF ( l_del_assignments_rowid_tab.COUNT > 0 )
2218 THEN -- { WDA Update
2219
2220 l_return_status := NULL;
2221 l_delivery_leg_id_tab.delete;
2222
2223 /**
2224 -- Unassigning delivery details from delivery and LPN's
2225 FORALL i IN l_del_assignments_rowid_tab.FIRST..l_del_assignments_rowid_tab.LAST
2226 UPDATE WSH_DELIVERY_ASSIGNMENTS
2227 SET parent_delivery_detail_id = null,
2228 delivery_id = null,
2229 last_update_date = SYSDATE,
2230 last_updated_by = fnd_global.user_id,
2231 last_update_login = fnd_global.conc_login_id,
2232 program_application_id = fnd_global.prog_appl_id,
2233 program_id = fnd_global.conc_program_id,
2234 program_update_date = SYSDATE
2235 WHERE rowid = l_del_assignments_rowid_tab(i);
2236 ***/
2237
2238 --Start of fix for bug 5749968
2239 --
2240 IF l_debug_on THEN
2241 WSH_DEBUG_SV.logmsg(l_module_name, 'Before calling delivery unassign standard api: '
2242 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2243 END IF;
2244 --
2245
2246 FOR unassignDelCnt in l_del_detail_id_tab.FIRST..l_del_detail_id_tab.LAST
2247 LOOP
2248 l_return_status := NULL;
2249 -- Calling Standard api to unassign detail from delivery
2250 WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_delivery (
2251 p_detail_id => l_del_detail_id_tab(unassignDelCnt),
2252 x_return_status => l_return_status );
2253
2254 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
2255 WSH_UTIL_CORE.G_RET_STS_WARNING) )
2256 THEN
2257 --
2258 IF l_debug_on THEN
2259 WSH_DEBUG_SV.logmsg(l_module_name, 'Unassign_Detail_from_Delivery returned error : ' || l_return_status);
2260 WSH_DEBUG_SV.logmsg(l_module_name, 'Error while processing delivery detail : ' || l_del_detail_id_tab(unassignDelCnt) );
2261 END IF;
2262 --
2263 x_return_status := l_return_status;
2264 RAISE Merge_Location_Exp;
2265 END IF;
2266 END LOOP;
2267
2268 --
2269 IF l_debug_on THEN
2270 WSH_DEBUG_SV.logmsg(l_module_name, 'After calling delivery unassign standard api: '
2271 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2272 END IF;
2273 --
2274 --End of fix for bug 5749968
2275 END IF; -- } WDA Update
2276
2277
2278 IF ( l_delivery_id_tab.COUNT > 0 ) THEN
2279 FORALL i IN l_delivery_id_tab.FIRST..l_delivery_id_tab.LAST
2280 DELETE FROM wsh_tmp WHERE column1 = l_delivery_id_tab(i);
2281
2282 --
2283 IF l_debug_on THEN
2284 WSH_DEBUG_SV.logmsg(l_module_name, 'No of records deleted from wsh_tmp table after unassigning from delivery : '
2285 || sql%rowcount || ', Time : '
2286 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2287 END IF;
2288 --
2289 END IF;
2290 -- Logging Exceptions for Delivery Details Unassigned from Delivery
2291 IF ( l_delivery_id_tab.COUNT > 0 )
2292 THEN -- { Log Exception
2293 l_message_name := 'WSH_PMRG_UNASSIGN_DELIVERY';
2294
2295 FOR ExpCnt in l_delivery_id_tab.FIRST..l_delivery_id_tab.LAST
2296 LOOP -- { Loop for logging Expceptions
2297 -- Setting the Messages
2298 FND_MESSAGE.Set_Name ('WSH', l_message_name );
2299 FND_MESSAGE.Set_Token ('PS1', p_from_fk_id );
2300 FND_MESSAGE.Set_Token ('PS2', p_to_fk_id );
2301 FND_MESSAGE.Set_Token ('DELIVERY_DETAIL_ID', l_del_detail_id_tab(ExpCnt) );
2302
2303 l_message_text := FND_MESSAGE.Get;
2304
2305 --
2306 IF l_debug_on THEN
2307 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_XC_UTIL.Log_Exception', WSH_DEBUG_SV.C_PROC_LEVEL);
2308 END IF;
2309 --
2310
2311 l_return_status := NULL;
2312 l_msg_count := NULL;
2313 l_msg_data := NULL;
2314 l_exception_id := NULL;
2315
2316 WSH_XC_UTIL.log_exception
2317 (
2318 p_api_version => 1.0,
2319 x_return_status => l_return_status,
2320 x_msg_count => l_msg_count,
2321 x_msg_data => l_msg_data,
2322 x_exception_id => l_exception_id,
2323 p_exception_location_id => l_to_location_id,
2324 p_logged_at_location_id => l_to_location_id,
2325 p_logging_entity => 'SHIPPER',
2326 p_logging_entity_id => Fnd_Global.user_id,
2327 p_exception_name => 'WSH_PARTY_MERGE_CHANGE',
2328 p_message => l_message_name,
2329 p_severity => 'LOW',
2330 p_manually_logged => 'N',
2331 p_delivery_id => l_delivery_id_tab(ExpCnt),
2332 p_delivery_name => l_delivery_name_tab(ExpCnt),
2333 p_error_message => l_message_text
2334 );
2335
2336 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2337 THEN
2338 --
2339 IF l_debug_on THEN
2340 WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
2341 WSH_DEBUG_SV.logmsg(l_module_name, 'Error While Calling WSH_XC_UTIL.Log_Exception', WSH_DEBUG_SV.C_PROC_LEVEL);
2342 END IF;
2343 --
2344 x_return_status := l_return_status;
2345 RAISE Merge_Location_Exp;
2346 END IF;
2347
2348 END LOOP; -- } Loop for logging Expceptions
2349
2350 END IF; -- } Log Exception
2351
2352 EXIT WHEN Get_Wsh_Unassign_Details%NOTFOUND;
2353 END LOOP; -- }
2354
2355 CLOSE Get_Wsh_Unassign_Details;
2356
2357 SELECT COUNT(*)
2358 INTO l_tmp_cnt
2359 FROM WSH_TMP;
2360
2361 IF ( l_tmp_cnt > 0 )
2362 THEN -- { Temp Table Count
2363
2364 OPEN Get_Delivery_Containers;
2365
2366 LOOP
2367 -- { Updation of Container records
2368 FETCH Get_Delivery_Containers BULK COLLECT INTO
2369 l_del_detail_rowid_tab
2370 LIMIT G_FETCH_LIMIT;
2371
2372 IF ( l_del_detail_rowid_tab.COUNT > 0 )
2373 THEN
2374 FORALL updCnt IN l_del_detail_rowid_tab.FIRST..l_del_detail_rowid_tab.LAST
2375 UPDATE WSH_DELIVERY_DETAILS Wdd
2376 SET ship_to_location_id = l_to_location_id,
2377 deliver_to_location_id = decode(deliver_to_location_id,
2378 ship_to_location_id, l_to_location_id,
2379 deliver_to_location_id),
2380 last_update_date = SYSDATE,
2381 last_updated_by = fnd_global.user_id,
2382 last_update_login = fnd_global.conc_login_id,
2383 program_application_id = fnd_global.prog_appl_id,
2384 program_id = fnd_global.conc_program_id,
2385 program_update_date = SYSDATE
2386 WHERE Wdd.Rowid = l_del_detail_rowid_tab(updCnt);
2387
2388 --
2389 IF l_debug_on THEN
2390 WSH_DEBUG_SV.logmsg(l_module_name, 'No of Container records Updated in WDD : '
2391 || sql%rowcount || ', Time : '
2392 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2393 END IF;
2394 --
2395 END IF;
2396
2397 EXIT WHEN Get_Delivery_Containers%NOTFOUND;
2398 -- } Updation of Container records
2399 END LOOP;
2400
2401 CLOSE Get_Delivery_Containers;
2402
2403 --
2404 IF l_debug_on THEN
2405 WSH_DEBUG_SV.log(l_module_name, 'After Container records Update', TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
2406 END IF;
2407 --
2408
2409 FOR TmpDelRec IN Get_Tmp_Deliveries
2410 LOOP
2411 -- Bug 5606960# G-Log Changes: Calling API's to update delivery's location.
2412 --
2413 IF l_debug_on THEN
2414 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling API WSH_NEW_DELIVERIES_PVT.Table_To_Record', WSH_DEBUG_SV.C_PROC_LEVEL);
2415 END IF;
2416 --
2417
2418 l_return_status := NULL;
2419 WSH_NEW_DELIVERIES_PVT.Table_To_Record (
2420 p_delivery_id => TmpDelRec.Delivery_Id,
2421 x_delivery_rec => l_delivery_rec,
2422 x_return_status => l_return_status );
2423
2424 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2425 THEN
2426 --
2427 IF l_debug_on THEN
2428 WSH_DEBUG_SV.log(l_module_name, 'Error returned from WSH_NEW_DELIVERIES_PVT.Table_To_Record : ' || l_return_status);
2429 END IF;
2430 --
2431 Raise Merge_Location_Exp;
2432 END IF;
2433 l_return_status := NULL;
2434
2435 Get_Delivery_Hash (
2436 p_delivery_id => TmpDelRec.delivery_id,
2437 p_delivery_detail_id => TmpDelRec.delivery_detail_id,
2438 x_hash_string => l_hash_string,
2439 x_hash_value => l_hash_value,
2440 x_return_status => l_return_status );
2441
2442 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2443 THEN
2444 --
2445 IF l_debug_on THEN
2446 WSH_DEBUG_SV.log(l_module_name, 'Error returned from Get_Delivery_Hash : ' || l_return_status);
2447 END IF;
2448 --
2449 Raise Merge_Location_Exp;
2450 END IF;
2451
2452 l_delivery_rec.ultimate_dropoff_location_id := l_to_location_id;
2453 l_delivery_rec.hash_string := l_hash_string;
2454 l_delivery_rec.hash_value := l_hash_value;
2455 l_delivery_rec.last_update_date := SYSDATE;
2456 l_delivery_rec.last_updated_by := fnd_global.user_id;
2457 l_delivery_rec.last_update_login := fnd_global.conc_login_id;
2458 l_delivery_rec.program_application_id := fnd_global.prog_appl_id;
2459 l_delivery_rec.program_id := fnd_global.conc_program_id;
2460 l_delivery_rec.program_update_date := SYSDATE;
2461
2462 --
2463 IF l_debug_on THEN
2464 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling API WSH_NEW_DELIVERIES_PVT.Update_Delivery', WSH_DEBUG_SV.C_PROC_LEVEL);
2465 END IF;
2466 --
2467
2468 l_return_status := NULL;
2469 WSH_NEW_DELIVERIES_PVT.Update_Delivery(
2470 p_rowid => TmpDelRec.Rowid,
2471 p_delivery_info => l_delivery_rec,
2472 x_return_status => l_return_status );
2473
2474 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2475 THEN
2476 --
2477 IF l_debug_on THEN
2478 WSH_DEBUG_SV.log(l_module_name, 'Error returned from WSH_NEW_DELIVERIES_PVT.Update_Delivery : ' || l_return_status);
2479 END IF;
2480 --
2481 Raise Merge_Location_Exp;
2482 END IF;
2483 END LOOP;
2484
2485 -- Unassigns delivery from stop, if a stop has deliveries with
2486 -- different location.
2487 OPEN Get_Del_Unassign_From_Stop ( l_to_location_id, l_from_location_id );
2488
2489 LOOP
2490
2491 FETCH Get_Del_Unassign_From_Stop BULK COLLECT INTO
2492 l_delivery_id_tab,
2493 l_stop_id_tab,
2494 l_trip_id_tab,
2495 l_delivery_leg_id_tab,
2496 l_organization_id_tab,
2497 l_net_weight_tab,
2498 l_gross_weight_tab,
2499 l_volume_tab,
2500 l_legs_rowid_tab,
2501 l_tmp_rowid_tab
2502 LIMIT G_FETCH_LIMIT;
2503
2504 IF ( l_legs_rowid_tab.COUNT > 0 )
2505 THEN
2506
2507 -- Bug 5606960# G-Log Changes: Calling API to Unassign delivery from TRIP.
2508 FOR i in l_legs_rowid_tab.FIRST..l_legs_rowid_tab.LAST
2509 LOOP
2510 l_del_action_prms.caller := 'WSH_PUB';
2511 l_del_action_prms.action_code := 'UNASSIGN-TRIP';
2512 l_del_action_prms.trip_id := l_trip_id_tab(i);
2513
2514 l_del_attrs(1).delivery_id := l_delivery_id_tab(i);
2515 l_del_attrs(1).organization_id := l_organization_id_tab(i);
2516 l_return_status := NULL;
2517 --
2518 IF l_debug_on THEN
2519 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling API WSH_DELIVERIES_GRP.Delivery_Action', WSH_DEBUG_SV.C_PROC_LEVEL);
2520 END IF;
2521 --
2522
2523 WSH_DELIVERIES_GRP.Delivery_Action (
2524 p_api_version_number => 1.0,
2525 p_init_msg_list => FND_API.G_TRUE,
2526 p_commit => FND_API.G_FALSE,
2527 p_action_prms => l_del_action_prms,
2528 p_rec_attr_tab => l_del_attrs,
2529 x_delivery_out_rec => l_del_action_rec,
2530 x_defaults_rec => l_del_defaults,
2531 x_return_status => l_return_status,
2532 x_msg_count => l_msg_count,
2533 x_msg_data => l_msg_data );
2534
2535 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2536 THEN
2537 --
2538 IF l_debug_on THEN
2539 WSH_DEBUG_SV.log(l_module_name, 'Error returned from WSH_DELIVERIES_GRP.Delivery_Action : ' || l_return_status);
2540 END IF;
2541 --
2542 Raise Merge_Location_Exp;
2543 END IF;
2544 END LOOP;
2545 END IF;
2546
2547 IF ( l_tmp_rowid_tab.COUNT > 0 )
2548 THEN
2549 -- Delete the records from Wsh_Tmp table, so that location are not
2550 -- updated for stops which are assigned to deliveries with different
2551 -- locations after party merge.
2552 FORALL updCnt IN l_tmp_rowid_tab.FIRST..l_tmp_rowid_tab.LAST
2553 DELETE FROM Wsh_Tmp
2554 WHERE Rowid = l_tmp_rowid_tab(updCnt);
2555
2556 --
2557 IF l_debug_on THEN
2558 WSH_DEBUG_SV.logmsg(l_module_name, 'No of records deleted from Wsh_Tmp : '
2559 || sql%rowcount || ', Time : '
2560 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2561 END IF;
2562 --
2563 END IF;
2564
2565 EXIT WHEN Get_Del_Unassign_From_Stop%NOTFOUND;
2566 END LOOP; -- }
2567
2568 CLOSE Get_Del_Unassign_From_Stop;
2569
2570 -- Logging Exceptions for Deliveries Unassigned from Stop
2571 IF ( l_stop_id_tab.COUNT > 0 )
2572 THEN -- { Log Exception
2573 l_message_name := 'WSH_PMRG_UNASSIGN_STOP';
2574
2575 FOR ExpCnt in l_stop_id_tab.FIRST..l_stop_id_tab.LAST
2576 LOOP -- { Loop for logging Expceptions
2577 -- Setting the Messages
2578 FND_MESSAGE.Set_Name ('WSH', l_message_name );
2579 FND_MESSAGE.Set_Token ('PS1', p_from_fk_id );
2580 FND_MESSAGE.Set_Token ('PS2', p_to_fk_id );
2581 FND_MESSAGE.Set_Token ('DELIVERY_ID', l_delivery_id_tab(ExpCnt) );
2582
2583 l_message_text := FND_MESSAGE.Get;
2584
2585 --
2586 IF l_debug_on THEN
2587 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_XC_UTIL.Log_Exception', WSH_DEBUG_SV.C_PROC_LEVEL);
2588 END IF;
2589 --
2590
2591 l_return_status := NULL;
2592 l_msg_count := NULL;
2593 l_msg_data := NULL;
2594 l_exception_id := NULL;
2595
2596 WSH_XC_UTIL.log_exception
2597 (
2598 p_api_version => 1.0,
2599 x_return_status => l_return_status,
2600 x_msg_count => l_msg_count,
2601 x_msg_data => l_msg_data,
2602 x_exception_id => l_exception_id,
2603 p_exception_location_id => l_to_location_id,
2604 p_logged_at_location_id => l_to_location_id,
2605 p_logging_entity => 'SHIPPER',
2606 p_logging_entity_id => Fnd_Global.user_id,
2607 p_exception_name => 'WSH_PARTY_MERGE_CHANGE',
2608 p_message => l_message_name,
2609 p_severity => 'LOW',
2610 p_manually_logged => 'N',
2611 p_trip_id => l_trip_id_tab(ExpCnt),
2612 p_trip_stop_id => l_stop_id_tab(ExpCnt),
2613 p_error_message => l_message_text
2614 );
2615
2616 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2617 THEN
2618 --
2619 IF l_debug_on THEN
2620 WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
2621 WSH_DEBUG_SV.logmsg(l_module_name, 'Error While Calling WSH_XC_UTIL.Log_Exception', WSH_DEBUG_SV.C_PROC_LEVEL);
2622 END IF;
2623 --
2624 x_return_status := l_return_status;
2625 RAISE Merge_Location_Exp;
2626 END IF;
2627
2628 END LOOP; -- } Loop for logging Expceptions
2629 END IF; -- } Log Exception
2630
2631 UPDATE WSH_TRIP_STOPS Wts
2632 SET stop_location_id = l_to_location_id,
2633 last_update_date = SYSDATE,
2634 last_updated_by = fnd_global.user_id,
2635 last_update_login = fnd_global.conc_login_id,
2636 program_application_id = fnd_global.prog_appl_id,
2637 program_id = fnd_global.conc_program_id,
2638 program_update_date = SYSDATE
2639 WHERE Wts.Stop_Id in (
2640 SELECT Column2
2641 FROM WSH_TMP
2642 WHERE Column2 IS NOT NULL);
2643
2644 --
2645 IF l_debug_on THEN
2646 WSH_DEBUG_SV.logmsg(l_module_name, 'No of Stop records Updated in WTS : '
2647 || sql%rowcount || ', Time : '
2648 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2649 END IF;
2650 --
2651
2652 -- Deleting records from temp table
2653 DELETE FROM wsh_tmp;
2654
2655 --
2656 IF l_debug_on THEN
2657 WSH_DEBUG_SV.logmsg(l_module_name, 'No of records deleted from temp table : '
2658 || sql%rowcount || ', Time : '
2659 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2660 END IF;
2661 --
2662 END IF; -- } Temp Table Count
2663
2664 -- Get Shipping details based on deliver_to_location for which the location
2665 -- has to be updated during party merge.
2666 -- Passing l_to_party_site_id, since party_site_id is already updated
2667 -- in HZ_CUST_ACCT_SITES_ALL API before calling our API.
2668 OPEN Get_Deliver_Loc_Details ( l_to_party_site_id, l_from_location_id );
2669
2670 LOOP -- {
2671 FETCH Get_Deliver_Loc_Details BULK COLLECT INTO
2672 l_del_detail_rowid_tab LIMIT G_FETCH_LIMIT;
2673
2674 --
2675 IF ( l_debug_on ) THEN
2676 WSH_DEBUG_SV.log(l_module_name, 'No of Records Fetched from Get_Deliver_Loc_details', l_del_detail_rowid_tab.COUNT);
2677 END IF;
2678 --
2679
2680 -- Update Deliver to Locations in Wsh_Delivery_Details table during
2681 -- party merge.
2682
2683 IF ( l_del_detail_rowid_tab.COUNT > 0 )
2684 THEN -- { WDD Update
2685
2686 FORALL i IN l_del_detail_rowid_tab.FIRST..l_del_detail_rowid_tab.LAST
2687 UPDATE WSH_DELIVERY_DETAILS Wdd
2688 SET deliver_to_location_id = l_to_location_id,
2689 last_update_date = SYSDATE,
2690 last_updated_by = fnd_global.user_id,
2691 last_update_login = fnd_global.conc_login_id,
2692 program_application_id = fnd_global.prog_appl_id,
2693 program_id = fnd_global.conc_program_id,
2694 program_update_date = SYSDATE
2695 WHERE WDD.Rowid = l_del_detail_rowid_tab(i);
2696
2697 --
2698 IF l_debug_on THEN
2699 WSH_DEBUG_SV.logmsg(l_module_name, 'No of Rows Updated in WDD : '
2700 || sql%rowcount || ', Time : '
2701 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2702 END IF;
2703 --
2704 END IF; -- } WDD Update
2705
2706 EXIT WHEN Get_Deliver_Loc_Details%NOTFOUND;
2707 END LOOP; -- }
2708
2709 CLOSE Get_Deliver_Loc_Details;
2710
2711 -- Update deliver_to_location for container records in WDD table
2712 OPEN Get_Del_Loc_Cont_Details ( l_to_party_site_id, l_to_location_id );
2713
2714 LOOP -- {
2715 FETCH Get_Del_Loc_Cont_Details BULK COLLECT INTO
2716 l_del_detail_id_tab LIMIT G_FETCH_LIMIT;
2717
2718 --
2719 IF ( l_debug_on ) THEN
2720 WSH_DEBUG_SV.log(l_module_name, 'No of Records Fetched from Get_Del_Loc_Cont_Details', l_del_detail_id_tab.COUNT);
2721 END IF;
2722 --
2723
2724 -- Update Deliver to Locations in Wsh_Delivery_Details table during
2725 -- party merge for Container records.
2726 IF ( l_del_detail_id_tab.COUNT > 0 )
2727 THEN -- { WDD Cont Update
2728
2729 FORALL i IN l_del_detail_id_tab.FIRST..l_del_detail_id_tab.LAST
2730 UPDATE WSH_DELIVERY_DETAILS Wdd
2731 SET deliver_to_location_id = l_to_location_id,
2732 last_update_date = SYSDATE,
2733 last_updated_by = fnd_global.user_id,
2734 last_update_login = fnd_global.conc_login_id,
2735 program_application_id = fnd_global.prog_appl_id,
2736 program_id = fnd_global.conc_program_id,
2737 program_update_date = SYSDATE
2738 WHERE WDD.Delivery_Detail_Id = l_del_detail_id_tab(i)
2739 AND WDD.Container_Flag = 'Y'; -- To make sure that we are updating container records
2740
2741 --
2742 IF l_debug_on THEN
2743 WSH_DEBUG_SV.logmsg(l_module_name, 'No of Rows Updated in WDD Cont Del Loc : '
2744 || sql%rowcount || ', Time : '
2745 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2746 END IF;
2747 --
2748 END IF; -- } WDD Cont Update
2749
2750 EXIT WHEN Get_Del_Loc_Cont_Details%NOTFOUND;
2751 END LOOP; -- }
2752
2753 CLOSE Get_Del_Loc_Cont_Details;
2754
2755 --
2756 IF l_debug_on THEN
2757 WSH_DEBUG_SV.log(l_module_name, 'Before WPR Update', TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
2758 END IF;
2759 --
2760
2761 UPDATE WSH_PICKING_RULES WPR
2762 SET ship_to_location_id = l_to_location_id,
2763 last_update_date = SYSDATE,
2764 last_updated_by = fnd_global.user_id,
2765 last_update_login = fnd_global.conc_login_id,
2766 program_application_id = fnd_global.prog_appl_id,
2767 program_id = fnd_global.conc_program_id,
2768 program_update_date = SYSDATE
2769 WHERE ship_to_location_id = l_from_location_id
2770 AND EXISTS
2771 ( SELECT 'X'
2772 FROM HZ_CUST_ACCT_SITES_ALL CA
2773 WHERE CA.Party_Site_Id = l_to_party_site_id
2774 AND CA.Cust_Account_Id = WPR.Customer_Id );
2775
2776 --
2777 IF l_debug_on THEN
2778 WSH_DEBUG_SV.logmsg(l_module_name, 'No of rows updated in WPR : '
2779 || sql%rowcount || ', Time : '
2780 || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
2781 WSH_DEBUG_SV.pop(l_module_name);
2782 END IF ;
2783 --
2784 EXCEPTION
2785 --
2786 WHEN Merge_Location_Exp THEN
2787 --
2788 -- Close the Cursors if they are OPEN
2789 --
2790 IF ( Get_Location_Id %ISOPEN ) THEN
2791 CLOSE Get_Location_Id ;
2792 END IF;
2793
2794 IF ( Get_Wsh_Details%ISOPEN ) THEN
2795 CLOSE Get_Wsh_Details;
2796 END IF;
2797
2798 IF ( Get_Wsh_Unassign_Details%ISOPEN ) THEN
2799 CLOSE Get_Wsh_Unassign_Details;
2800 END IF;
2801
2802 IF ( Get_Delivery_Containers%ISOPEN ) THEN
2803 CLOSE Get_Delivery_Containers;
2804 END IF;
2805
2806 IF ( Get_Del_Unassign_From_Stop%ISOPEN ) THEN
2807 CLOSE Get_Del_Unassign_From_Stop;
2808 END IF;
2809
2810 IF ( Get_Deliver_Loc_Details%ISOPEN ) THEN
2811 CLOSE Get_Deliver_Loc_Details;
2812 END IF;
2813
2814 IF ( Get_Del_Loc_Cont_Details%ISOPEN ) THEN
2815 CLOSE Get_Del_Loc_Cont_Details;
2816 END IF;
2817
2818 --
2819 IF l_debug_on THEN
2820 WSH_DEBUG_SV.logmsg(l_module_name, 'Merge_Location_Exp occurred');
2821 WSH_DEBUG_SV.pop(l_module_name);
2822 END IF;
2823 --
2824
2825 WHEN OTHERS THEN
2826 FND_MESSAGE.SET_NAME('AR','HZ_API_OTHERS_EXCEP');
2827 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2828 FND_MSG_PUB.ADD;
2829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2830
2831 -- Close the Cursors if it is OPEN
2832 IF ( Get_Location_Id %ISOPEN ) THEN
2833 CLOSE Get_Location_Id ;
2834 END IF;
2835
2836 IF ( Get_Wsh_Details%ISOPEN ) THEN
2837 CLOSE Get_Wsh_Details;
2838 END IF;
2839
2840 IF ( Get_Wsh_Unassign_Details%ISOPEN ) THEN
2841 CLOSE Get_Wsh_Unassign_Details;
2842 END IF;
2843
2844 IF ( Get_Delivery_Containers%ISOPEN ) THEN
2845 CLOSE Get_Delivery_Containers;
2846 END IF;
2847
2848 IF ( Get_Del_Unassign_From_Stop%ISOPEN ) THEN
2849 CLOSE Get_Del_Unassign_From_Stop;
2850 END IF;
2851
2852 IF ( Get_Deliver_Loc_Details%ISOPEN ) THEN
2853 CLOSE Get_Deliver_Loc_Details;
2854 END IF;
2855
2856 IF ( Get_Del_Loc_Cont_Details%ISOPEN ) THEN
2857 CLOSE Get_Del_Loc_Cont_Details;
2858 END IF;
2859
2860 --
2861 IF l_debug_on THEN
2862 WSH_DEBUG_SV.logmsg(l_module_name, 'Unexpected Error......');
2863 WSH_DEBUG_SV.log(l_module_name, 'Error Code', sqlcode);
2864 WSH_DEBUG_SV.log(l_module_name, 'Error Mesg', sqlerrm);
2865 WSH_DEBUG_SV.pop(l_module_name);
2866 END IF;
2867 --
2868 END Merge_Location;
2869
2870 --
2871 -- End R12 FP Bug 5075838
2872 --
2873
2874 END WSH_PARTY_MERGE;