1 PACKAGE BODY POP_CMERGE_REQ as
2 /* $Header: pocmer2b.pls 120.0 2005/06/01 19:48:34 appldev noship $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
8
9 /*--------------------------- PO_REQUISITION_LINES --------------------------*/
10
11 procedure PO_RL (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
12
13 CURSOR C1 IS
14 SELECT NULL
15 FROM PO_REQUISITION_LINES
16 WHERE source_type_code = 'INVENTORY'
20 from ra_customer_merges racm
17 and deliver_to_location_id in (select location_id
18 from po_location_associations
19 where customer_id in (select racm.duplicate_id
21 where racm.process_flag = 'N'
22 and racm.request_id = req_id
23 and racm.set_number = set_num
24 and racm.customer_id
25 <> racm.duplicate_id
26 and racm.customer_id in
27 (select distinct customer_id
28 from po_location_associations)))
29 FOR UPDATE NOWAIT;
30
31 /* Bug 2447478 START */
32
33 /* Bug 4009128: Commenting out following cursor statement. Adding columns
34 pla1.location_id, pla2.location_id this cursor so they don't have to
35 be retrieved in select statement later. */
36
37 /*
38 -- bug3648471
39 -- Rewrote merged_records as the original one did not return the records
40 -- expected
41
42 -- SQL What: Get all the requisitions that has the deliver to location
43 -- associated with a customer site to be merged, and the target
44 -- site being already associated with some other location
45 -- SQL Why: We will update the deliver to location information of the req
46 -- line, as the associated will be deleted later on.
47
48 CURSOR merged_records IS
49 SELECT m.CUSTOMER_MERGE_HEADER_ID,
50 yt.REQUISITION_LINE_ID
51 FROM PO_REQUISITION_LINES yt,
52 ra_customer_merges m,
53 po_location_associations pla
54 WHERE yt.source_type_code = 'INVENTORY'
55 AND yt.deliver_to_location_id = pla.location_id
56 AND pla.site_use_id = m.duplicate_site_id
57 AND m.process_flag = 'N'
58 AND m.request_id = req_id
59 AND m.set_number = set_num
60 AND EXISTS (SELECT null
61 FROM po_location_associations pla1
62 WHERE m.customer_site_id = pla1.site_use_id);
63 */
64
65 CURSOR merged_records IS
66 SELECT m.CUSTOMER_MERGE_HEADER_ID,
67 yt.REQUISITION_LINE_ID,
68 pla1.location_id, --new
69 pla2.location_id --old
70
71 FROM PO_REQUISITION_LINES yt, ra_customer_merges m,
72 po_location_associations pla1,po_location_associations pla2
73 WHERE yt.source_type_code = 'INVENTORY'
74 and yt.deliver_to_location_id = pla2.location_id
75 and pla2.site_use_id = m.duplicate_site_id
76 and m.customer_site_id = pla1.site_use_id
77 and m.process_flag = 'N'
78 and m.request_id = req_id
79 and m.set_number = set_num;
80
81 /* End Bug 4009128 */
82
83
84 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
85 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
86 INDEX BY BINARY_INTEGER;
87 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
88
89 TYPE REQUISITION_LINE_ID_LIST_TYPE IS TABLE OF
90 PO_REQUISITION_LINES.REQUISITION_LINE_ID%TYPE
91 INDEX BY BINARY_INTEGER;
92 PRIMARY_KEY_ID_LIST REQUISITION_LINE_ID_LIST_TYPE;
93
94 TYPE DELIVER_TO_LOC_ID_LIST_TYPE IS TABLE OF
95 PO_REQUISITION_LINES.DELIVER_TO_LOCATION_ID%TYPE
96 INDEX BY BINARY_INTEGER;
97 NUM_COL1_ORIG_LIST DELIVER_TO_LOC_ID_LIST_TYPE;
98 NUM_COL1_NEW_LIST DELIVER_TO_LOC_ID_LIST_TYPE;
99
100 l_deliver_to_location_id PO_REQUISITION_LINES.DELIVER_TO_LOCATION_ID%TYPE;
101 l_profile_val VARCHAR2(30);
102 l_last_fetch BOOLEAN := FALSE;
103 /* Bug 2447478 END */
104
105 /* Following vars used to hold values from merged_records cursor */
106 OLD_DELIVER_TO_LOC_LIST DELIVER_TO_LOC_ID_LIST_TYPE; --Bug 4009128
107 NEW_DELIVER_TO_LOC_LIST DELIVER_TO_LOC_ID_LIST_TYPE; --Bug 4009128
108
109 BEGIN
110
111 arp_message.set_line( 'POP_CMERGE_REQ.PO_RL()+' );
112
113 /*-----------------------------+
114 | PO_REQUISITION_LINES |
115 +-----------------------------*/
116
117 IF (process_mode = 'LOCK') then
118
119 /* try to lock the table first */
120
121 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
122 arp_message.set_token( 'TABLE_NAME', 'PO_REQUISITION_LINES', FALSE );
123 arp_message.flush;
124
125 OPEN C1;
126 CLOSE C1;
127
128 ELSE
129
130 /* Modify locations for those lines for which locations are going to change */
131
132 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
133 arp_message.set_token('TABLE_NAME', 'PO_REQUISITION_LINES', FALSE);
134 arp_message.set_line('Merging locations for merged customers/sites.');
135
136 /* Bug 2447478 START */
137 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
138 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
139
140 /* Bug 4009128 - commented below sql */
141 /* Get the value for deliver_to_location_id */
142 /*
143 BEGIN
144 select distinct pla1.location_id
145 into l_deliver_to_location_id
146 from po_location_associations pla1,
147 po_location_associations pla2,
148 ra_customer_merges racm,
149 po_requisition_lines yt
150 where yt.deliver_to_location_id = pla2.location_id
151 and pla2.site_use_id = racm.duplicate_site_id
155 and racm.set_number = set_num;
152 and racm.customer_site_id = pla1.site_use_id
153 and racm.process_flag = 'N'
154 and racm.request_id = req_id
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 null;
159 WHEN OTHERS THEN
160 null;
161 END;
162 */
163
164 /* Open the merged_records cursor */
165 open merged_records;
166
167 LOOP
168 /* Fetch the data into local variables */
169 /* Bug 4009128 - added fetch of old and new locations */
170 FETCH merged_records BULK COLLECT INTO
171 MERGE_HEADER_ID_LIST,
172 PRIMARY_KEY_ID_LIST,
173 NEW_DELIVER_TO_LOC_LIST,
174 OLD_DELIVER_TO_LOC_LIST;
175
176 /*
177 If there is no more records, set the flag to true to indicate
178 that we are done fetching all records.
179 */
180 IF merged_records%NOTFOUND THEN
181 l_last_fetch := TRUE;
182 END IF;
183
184 /*
185 If there is no more records to be fetched and
186 no data was fetched at all, then exit the procedure call
187 */
188 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
189 exit;
190 END IF;
191
192 /*
193 Store the value of the column to be updated to local
194 variables.
195 */
196 /* Bug 4009128 - comment below assignments */
197 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
198 /* NUM_COL1_ORIG_LIST(I) := l_deliver_to_location_id;
199 NUM_COL1_NEW_LIST(I) := NUM_COL1_ORIG_LIST(I); */
200
201 NUM_COL1_ORIG_LIST(I) := NEW_DELIVER_TO_LOC_LIST(I); --Bug 4009128
202 NUM_COL1_NEW_LIST(I) := NUM_COL1_ORIG_LIST(I); --Bug 4009128
203
204 END LOOP;
205
206 /*
207 If auditing has been enabled, insert the changed records
208 into the HZ_CUSTOMER_MERGE_LOG table.
209 */
210 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
211 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
212 INSERT INTO HZ_CUSTOMER_MERGE_LOG(
213 MERGE_LOG_ID,
214 TABLE_NAME,
215 MERGE_HEADER_ID,
216 PRIMARY_KEY_ID,
217 NUM_COL1_ORIG,
218 NUM_COL1_NEW,
219 ACTION_FLAG,
220 REQUEST_ID,
221 CREATED_BY,
222 CREATION_DATE,
223 LAST_UPDATE_LOGIN,
224 LAST_UPDATE_DATE,
225 LAST_UPDATED_BY)
226 VALUES(
227 HZ_CUSTOMER_MERGE_LOG_s.nextval,
228 'PO_REQUISITION_LINES',
229 MERGE_HEADER_ID_LIST(I),
230 PRIMARY_KEY_ID_LIST(I),
231 NUM_COL1_ORIG_LIST(I),
232 NUM_COL1_NEW_LIST(I),
233 'U',
234 req_id,
235 hz_utility_pub.CREATED_BY,
236 hz_utility_pub.CREATION_DATE,
237 hz_utility_pub.LAST_UPDATE_LOGIN,
238 hz_utility_pub.LAST_UPDATE_DATE,
239 hz_utility_pub.LAST_UPDATED_BY);
240 END IF;
241
242 /*
243 Update all corresponding deliver_to_location_id of
244 the affected Requisitions due to merged/changed accounts
245 */
246 /* Bug 4009128 - modified deliver_to_location assignment */
247 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
248 UPDATE PO_REQUISITION_LINES yt
249 SET -- DELIVER_TO_LOCATION_ID = l_deliver_to_location_id,
250 DELIVER_TO_LOCATION_ID = NEW_DELIVER_TO_LOC_LIST(I),
251 LAST_UPDATE_DATE = SYSDATE,
252 LAST_UPDATED_BY = arp_standard.profile.user_id,
253 LAST_UPDATE_LOGIN = arp_standard.profile.last_update_login,
254 REQUEST_ID = req_id,
255 PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id,
256 PROGRAM_ID = arp_standard.profile.program_id,
257 PROGRAM_UPDATE_DATE = SYSDATE
258 WHERE REQUISITION_LINE_ID = PRIMARY_KEY_ID_LIST(I);
259
260 g_count := g_count + SQL%ROWCOUNT;
261
262 IF l_last_fetch THEN
263 EXIT;
264 END IF;
265
266 END LOOP;
267
268 /* Close the cursor */
269 close merged_records;
270 /* Bug 2447478 END */
271
272 /* Number of rows updates */
273 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
274 arp_message.set_token('NUM_ROWS',to_char(g_count));
275
276 END IF;
277
278 arp_message.set_line( 'POP_CMERGE_REQ.PO_RL()-' );
279
280
281 EXCEPTION
282 when others then
283 arp_message.set_error( 'POP_CMERGE_REQ.PO_RL');
284 raise;
285
286 END;
287
288
289 /*------------------------ PO_LOCATION_ASSOCIATIONS ------------------------*/
290
291 PROCEDURE PO_LA (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
292
293 CURSOR C1 IS
294 SELECT NULL
295 FROM PO_LOCATION_ASSOCIATIONS
296 WHERE site_use_id in (select racm.duplicate_site_id
297 from ra_customer_merges racm
298 where racm.process_flag = 'N'
302
299 and racm.request_id = req_id
300 and racm.set_number = set_num)
301 FOR UPDATE NOWAIT;
303 /* Bug 2447478 START */
304 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
305 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
306 INDEX BY BINARY_INTEGER;
307 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
308
309 TYPE LOCATION_ID_LIST_TYPE IS TABLE OF
310 PO_LOCATION_ASSOCIATIONS.LOCATION_ID%TYPE
311 INDEX BY BINARY_INTEGER;
312 PRIMARY_KEY_ID_LIST LOCATION_ID_LIST_TYPE;
313
314 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
315 PO_LOCATION_ASSOCIATIONS.CUSTOMER_ID%TYPE
316 INDEX BY BINARY_INTEGER;
317 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
318 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
319
320 TYPE SITE_USE_ID_LIST_TYPE IS TABLE OF
321 PO_LOCATION_ASSOCIATIONS.SITE_USE_ID%TYPE
322 INDEX BY BINARY_INTEGER;
323 NUM_COL2_ORIG_LIST SITE_USE_ID_LIST_TYPE;
324 NUM_COL2_NEW_LIST SITE_USE_ID_LIST_TYPE;
325
326 TYPE ADDRESS_ID_LIST_TYPE IS TABLE OF
327 PO_LOCATION_ASSOCIATIONS.ADDRESS_ID%TYPE
328 INDEX BY BINARY_INTEGER;
329 NUM_COL3_ORIG_LIST ADDRESS_ID_LIST_TYPE;
330 NUM_COL3_NEW_LIST ADDRESS_ID_LIST_TYPE;
331
332 TYPE ORGANIZATION_ID_LIST_TYPE IS TABLE OF
333 PO_LOCATION_ASSOCIATIONS.ORGANIZATION_ID%TYPE
334 INDEX BY BINARY_INTEGER;
335 NUM_COL4_ORIG_LIST ORGANIZATION_ID_LIST_TYPE;
336 NUM_COL4_NEW_LIST ORGANIZATION_ID_LIST_TYPE;
337
338 TYPE ORG_ID_LIST_TYPE IS TABLE OF
339 PO_LOCATION_ASSOCIATIONS.ORG_ID%TYPE
340 INDEX BY BINARY_INTEGER;
341 NUM_COL5_ORIG_LIST ORG_ID_LIST_TYPE;
342 NUM_COL5_NEW_LIST ORG_ID_LIST_TYPE;
343
344 TYPE VENDOR_ID_LIST_TYPE IS TABLE OF
345 PO_LOCATION_ASSOCIATIONS.VENDOR_ID%TYPE
346 INDEX BY BINARY_INTEGER;
347 NUM_COL6_ORIG_LIST VENDOR_ID_LIST_TYPE;
348 NUM_COL6_NEW_LIST VENDOR_ID_LIST_TYPE;
349
350 TYPE VENDOR_SITE_ID_LIST_TYPE IS TABLE OF
351 PO_LOCATION_ASSOCIATIONS.VENDOR_SITE_ID%TYPE
352 INDEX BY BINARY_INTEGER;
353 NUM_COL7_ORIG_LIST VENDOR_SITE_ID_LIST_TYPE;
354 NUM_COL7_NEW_LIST VENDOR_SITE_ID_LIST_TYPE;
355
356 TYPE SUBINVENTORY_LIST_TYPE IS TABLE OF
357 PO_LOCATION_ASSOCIATIONS.SUBINVENTORY%TYPE
358 INDEX BY BINARY_INTEGER;
359 VCHAR_COL1_ORIG_LIST SUBINVENTORY_LIST_TYPE;
360 VCHAR_COL1_NEW_LIST SUBINVENTORY_LIST_TYPE;
361
362 TYPE ATTRIBUTE_CATEGORY_LIST_TYPE IS TABLE OF
363 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE_CATEGORY%TYPE
364 INDEX BY BINARY_INTEGER;
365 VCHAR_COL2_ORIG_LIST ATTRIBUTE_CATEGORY_LIST_TYPE;
366 VCHAR_COL2_NEW_LIST ATTRIBUTE_CATEGORY_LIST_TYPE;
367
368 TYPE ATTRIBUTE1_LIST_TYPE IS TABLE OF
369 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE1%TYPE
370 INDEX BY BINARY_INTEGER;
371 VCHAR_COL3_ORIG_LIST ATTRIBUTE1_LIST_TYPE;
372 VCHAR_COL3_NEW_LIST ATTRIBUTE1_LIST_TYPE;
373
374 TYPE ATTRIBUTE2_LIST_TYPE IS TABLE OF
375 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE2%TYPE
376 INDEX BY BINARY_INTEGER;
377 VCHAR_COL4_ORIG_LIST ATTRIBUTE2_LIST_TYPE;
378 VCHAR_COL4_NEW_LIST ATTRIBUTE2_LIST_TYPE;
379
380 TYPE ATTRIBUTE3_LIST_TYPE IS TABLE OF
381 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE3%TYPE
382 INDEX BY BINARY_INTEGER;
383 VCHAR_COL5_ORIG_LIST ATTRIBUTE3_LIST_TYPE;
384 VCHAR_COL5_NEW_LIST ATTRIBUTE3_LIST_TYPE;
385
386 TYPE ATTRIBUTE4_LIST_TYPE IS TABLE OF
387 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE4%TYPE
388 INDEX BY BINARY_INTEGER;
389 VCHAR_COL6_ORIG_LIST ATTRIBUTE4_LIST_TYPE;
390 VCHAR_COL6_NEW_LIST ATTRIBUTE4_LIST_TYPE;
391
392 TYPE ATTRIBUTE5_LIST_TYPE IS TABLE OF
393 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE5%TYPE
394 INDEX BY BINARY_INTEGER;
395 VCHAR_COL7_ORIG_LIST ATTRIBUTE5_LIST_TYPE;
396 VCHAR_COL7_NEW_LIST ATTRIBUTE5_LIST_TYPE;
397
398 TYPE ATTRIBUTE6_LIST_TYPE IS TABLE OF
399 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE6%TYPE
400 INDEX BY BINARY_INTEGER;
401 VCHAR_COL8_ORIG_LIST ATTRIBUTE6_LIST_TYPE;
402 VCHAR_COL8_NEW_LIST ATTRIBUTE6_LIST_TYPE;
403
404 TYPE ATTRIBUTE7_LIST_TYPE IS TABLE OF
405 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE7%TYPE
406 INDEX BY BINARY_INTEGER;
407 VCHAR_COL9_ORIG_LIST ATTRIBUTE7_LIST_TYPE;
408 VCHAR_COL9_NEW_LIST ATTRIBUTE7_LIST_TYPE;
409
410 TYPE ATTRIBUTE8_LIST_TYPE IS TABLE OF
411 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE8%TYPE
412 INDEX BY BINARY_INTEGER;
413 VCHAR_COL10_ORIG_LIST ATTRIBUTE8_LIST_TYPE;
414 VCHAR_COL10_NEW_LIST ATTRIBUTE8_LIST_TYPE;
415
416 TYPE ATTRIBUTE9_LIST_TYPE IS TABLE OF
417 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE9%TYPE
418 INDEX BY BINARY_INTEGER;
419 VCHAR_COL11_ORIG_LIST ATTRIBUTE9_LIST_TYPE;
420 VCHAR_COL11_NEW_LIST ATTRIBUTE9_LIST_TYPE;
421
422 TYPE ATTRIBUTE10_LIST_TYPE IS TABLE OF
423 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE10%TYPE
424 INDEX BY BINARY_INTEGER;
425 VCHAR_COL12_ORIG_LIST ATTRIBUTE10_LIST_TYPE;
426 VCHAR_COL12_NEW_LIST ATTRIBUTE10_LIST_TYPE;
427
428 TYPE ATTRIBUTE11_LIST_TYPE IS TABLE OF
429 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE11%TYPE
430 INDEX BY BINARY_INTEGER;
431 VCHAR_COL13_ORIG_LIST ATTRIBUTE11_LIST_TYPE;
432 VCHAR_COL13_NEW_LIST ATTRIBUTE11_LIST_TYPE;
433
434 TYPE ATTRIBUTE12_LIST_TYPE IS TABLE OF
435 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE12%TYPE
436 INDEX BY BINARY_INTEGER;
437 VCHAR_COL14_ORIG_LIST ATTRIBUTE12_LIST_TYPE;
438 VCHAR_COL14_NEW_LIST ATTRIBUTE12_LIST_TYPE;
439
440 TYPE ATTRIBUTE13_LIST_TYPE IS TABLE OF
444 VCHAR_COL15_NEW_LIST ATTRIBUTE13_LIST_TYPE;
441 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE13%TYPE
442 INDEX BY BINARY_INTEGER;
443 VCHAR_COL15_ORIG_LIST ATTRIBUTE13_LIST_TYPE;
445
446 TYPE ATTRIBUTE14_LIST_TYPE IS TABLE OF
447 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE14%TYPE
448 INDEX BY BINARY_INTEGER;
449 VCHAR_COL16_ORIG_LIST ATTRIBUTE14_LIST_TYPE;
450 VCHAR_COL16_NEW_LIST ATTRIBUTE14_LIST_TYPE;
451
452 TYPE ATTRIBUTE15_LIST_TYPE IS TABLE OF
453 PO_LOCATION_ASSOCIATIONS.ATTRIBUTE15%TYPE
454 INDEX BY BINARY_INTEGER;
455 VCHAR_COL17_ORIG_LIST ATTRIBUTE15_LIST_TYPE;
456 VCHAR_COL17_NEW_LIST ATTRIBUTE15_LIST_TYPE;
457
458 NEW_CUST_ID_LIST CUSTOMER_ID_LIST_TYPE; --Bug 4009128
459 NEW_CUST_SITE_ID_LIST SITE_USE_ID_LIST_TYPE; --Bug 4009128
460
461 -- bug3648471
462 -- Rewrote cursor deleted_records as the original one was not returning
463 -- the rows we want it to be.
464
465 -- SQL What: Get all the loc associations, if the loc association
466 -- satisfies the following criteria
467 -- 1) The customer site is being merged
468 -- 2) The target customer site already has an association
469 -- SQL Why: We will delete this association as the site is going away
470
471 CURSOR deleted_records IS
472 SELECT m.CUSTOMER_MERGE_HEADER_ID,
473 yt.LOCATION_ID,
474 m.CUSTOMER_ID,
475 yt.SITE_USE_ID,
476 yt.ADDRESS_ID,
477 yt.ORGANIZATION_ID,
478 yt.ORG_ID,
479 yt.VENDOR_ID,
480 yt.VENDOR_SITE_ID,
481 yt.SUBINVENTORY,
482 yt.ATTRIBUTE_CATEGORY,
483 yt.ATTRIBUTE1,
484 yt.ATTRIBUTE2,
485 yt.ATTRIBUTE3,
486 yt.ATTRIBUTE4,
487 yt.ATTRIBUTE5,
488 yt.ATTRIBUTE6,
489 yt.ATTRIBUTE7,
490 yt.ATTRIBUTE8,
491 yt.ATTRIBUTE9,
492 yt.ATTRIBUTE10,
493 yt.ATTRIBUTE11,
494 yt.ATTRIBUTE12,
495 yt.ATTRIBUTE13,
496 yt.ATTRIBUTE14,
497 yt.ATTRIBUTE15
498 FROM PO_LOCATION_ASSOCIATIONS yt,
499 ra_customer_merges m
500 WHERE yt.site_use_id = m.duplicate_site_id -- get the records that
501 -- need to be merged from
502 AND m.process_flag = 'N'
503 AND m.request_id = req_id
504 AND m.set_number = set_num
505 AND EXISTS (SELECT null
506 FROM po_location_associations lc
507 WHERE lc.site_use_id = m.customer_site_id);
508
509 /* Start Bug 4009128 - replaced the below cursor statement */
510
511 -- bug3648471
512 -- Rewrote cursor merged_records as the original one was not returning
513 -- the rows we want it to be.
514
515 -- SQL What: Get all the loc associations, if the loc association
516 -- has the customer site that is going to get merged, and
517 -- it has not been deleted yet (which means that the target
518 -- customer site doesn't have location association
519 -- SQL Why: We will update the association by replacing the old site
520 -- with the target site
521
522 /*
523 CURSOR merged_records IS
524 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID,
525 yt.LOCATION_ID
526 FROM PO_LOCATION_ASSOCIATIONS yt, ra_customer_merges m
527 WHERE yt.site_use_id = m.duplicate_site_id
528 AND m.process_flag = 'N'
529 AND m.request_id = req_id
530 AND m.set_number = set_num;
531 */
532
533 CURSOR merged_records IS
534 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID,
535 yt.LOCATION_ID,
536 m.customer_id,
537 m.customer_site_id
538 FROM PO_LOCATION_ASSOCIATIONS yt, ra_customer_merges m
539 where yt.customer_id = m.duplicate_id
540 and yt.site_use_id = m.duplicate_site_id
541 and m.process_flag = 'N'
542 and m.request_id = req_id
543 and m.set_number = set_num;
544 /* END Bug 4009128 */
545
546 l_customer_id ra_customer_merges.customer_id%TYPE;
547 l_site_use_id ra_customer_merges.customer_site_id%TYPE;
548 l_profile_val VARCHAR2(30);
549 l_last_fetch BOOLEAN := FALSE;
550 /* Bug 2447478 END */
551
552 BEGIN
553
554 arp_message.set_line( 'POP_CMERGE_REQ.PO_LA()+' );
555
556 /*-----------------------------+
557 | PO_LOCATION_ASSOCIATIONS |
558 +-----------------------------*/
559
560 IF (process_mode = 'LOCK') then
561
562 /* try to lock the table first */
563
564 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
565 arp_message.set_token( 'TABLE_NAME', 'PO_LOCATION_ASSOCIATIONS', FALSE );
566 arp_message.flush;
567
568 OPEN C1;
569 CLOSE C1;
570
571 ELSE
572
573 /* for merged customers/sites */
574
575 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
576 arp_message.set_token('TABLE_NAME', 'PO_LOCATION_ASSOCIATIONS', FALSE);
577 arp_message.set_line('Deleting merged customers/sites.');
578
579 /* Bug 2447478 START */
580 g_count := 0;
581 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
582 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
583
584 /* Open the deleted_records cursor */
585 open deleted_records;
586
590 MERGE_HEADER_ID_LIST,
587 LOOP
588 /* Fetch the data into local variables */
589 FETCH deleted_records BULK COLLECT INTO
591 PRIMARY_KEY_ID_LIST,
592 NUM_COL1_ORIG_LIST,
593 NUM_COL2_ORIG_LIST,
594 NUM_COL3_ORIG_LIST,
595 NUM_COL4_ORIG_LIST,
596 NUM_COL5_ORIG_LIST,
597 NUM_COL6_ORIG_LIST,
598 NUM_COL7_ORIG_LIST,
599 VCHAR_COL1_ORIG_LIST,
600 VCHAR_COL2_ORIG_LIST,
601 VCHAR_COL3_ORIG_LIST,
602 VCHAR_COL4_ORIG_LIST,
603 VCHAR_COL5_ORIG_LIST,
604 VCHAR_COL6_ORIG_LIST,
605 VCHAR_COL7_ORIG_LIST,
606 VCHAR_COL8_ORIG_LIST,
607 VCHAR_COL9_ORIG_LIST,
608 VCHAR_COL10_ORIG_LIST,
609 VCHAR_COL11_ORIG_LIST,
610 VCHAR_COL12_ORIG_LIST,
611 VCHAR_COL13_ORIG_LIST,
612 VCHAR_COL14_ORIG_LIST,
613 VCHAR_COL15_ORIG_LIST,
614 VCHAR_COL16_ORIG_LIST,
615 VCHAR_COL17_ORIG_LIST;
616
617 /*
618 If there is no more records, set the flag to true to indicate
619 that we are done fetching all records.
620 */
621 IF deleted_records%NOTFOUND THEN
622 l_last_fetch := TRUE;
623 END IF;
624
625 /*
626 If there is no more records to be fetched and
627 no data was fetched at all, then exit the procedure call
628 */
629 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
630 exit;
631 END IF;
632
633 /*
634 Store the value of the column to be updated to local
635 variables.
636 */
637 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
638 NUM_COL1_NEW_LIST(I) := NUM_COL1_ORIG_LIST(I);
639 NUM_COL2_NEW_LIST(I) := NUM_COL2_ORIG_LIST(I);
640 END LOOP;
641
642 /*
643 If auditing has been enabled, insert the changed records
644 into the HZ_CUSTOMER_MERGE_LOG table.
645 */
646 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
647 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
648 INSERT INTO HZ_CUSTOMER_MERGE_LOG(
649 MERGE_LOG_ID,
650 TABLE_NAME,
651 MERGE_HEADER_ID,
652 PRIMARY_KEY_ID,
653 DEL_COL1,
654 DEL_COL2,
655 DEL_COL3,
656 DEL_COL4,
657 DEL_COL5,
658 DEL_COL6,
659 DEL_COL7,
660 DEL_COL8,
661 DEL_COL9,
662 DEL_COL10,
663 DEL_COL11,
664 DEL_COL12,
665 DEL_COL13,
666 DEL_COL14,
667 DEL_COL15,
668 DEL_COL16,
669 DEL_COL17,
670 DEL_COL18,
671 DEL_COL19,
672 DEL_COL20,
673 DEL_COL21,
674 DEL_COL22,
675 DEL_COL23,
676 DEL_COL24,
677 ACTION_FLAG,
678 REQUEST_ID,
679 CREATED_BY,
680 CREATION_DATE,
681 LAST_UPDATE_LOGIN,
682 LAST_UPDATE_DATE,
683 LAST_UPDATED_BY)
684 VALUES(
685 HZ_CUSTOMER_MERGE_LOG_s.nextval,
686 'PO_LOCATION_ASSOCIATIONS',
687 MERGE_HEADER_ID_LIST(I),
688 PRIMARY_KEY_ID_LIST(I),
689 NUM_COL1_ORIG_LIST(I),
690 NUM_COL2_ORIG_LIST(I),
691 NUM_COL3_ORIG_LIST(I),
692 NUM_COL4_ORIG_LIST(I),
693 NUM_COL5_ORIG_LIST(I),
694 NUM_COL6_ORIG_LIST(I),
695 NUM_COL7_ORIG_LIST(I),
696 VCHAR_COL1_ORIG_LIST(I),
697 VCHAR_COL2_ORIG_LIST(I),
698 VCHAR_COL3_ORIG_LIST(I),
699 VCHAR_COL4_ORIG_LIST(I),
700 VCHAR_COL5_ORIG_LIST(I),
701 VCHAR_COL6_ORIG_LIST(I),
702 VCHAR_COL7_ORIG_LIST(I),
703 VCHAR_COL8_ORIG_LIST(I),
704 VCHAR_COL9_ORIG_LIST(I),
705 VCHAR_COL10_ORIG_LIST(I),
706 VCHAR_COL11_ORIG_LIST(I),
707 VCHAR_COL12_ORIG_LIST(I),
708 VCHAR_COL13_ORIG_LIST(I),
709 VCHAR_COL14_ORIG_LIST(I),
710 VCHAR_COL15_ORIG_LIST(I),
711 VCHAR_COL16_ORIG_LIST(I),
712 VCHAR_COL17_ORIG_LIST(I),
713 'D',
714 req_id,
715 hz_utility_pub.CREATED_BY,
716 hz_utility_pub.CREATION_DATE,
717 hz_utility_pub.LAST_UPDATE_LOGIN,
718 hz_utility_pub.LAST_UPDATE_DATE,
719 hz_utility_pub.LAST_UPDATED_BY);
720 END IF;
721
722 /*
723 Delete all old or redundant records from the affected
724 Requisitions due to merged/changed accounts
725 */
726 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
727 DELETE FROM PO_LOCATION_ASSOCIATIONS yt
731
728 WHERE LOCATION_ID = PRIMARY_KEY_ID_LIST(I);
729
730 g_count := g_count + SQL%ROWCOUNT;
732 IF l_last_fetch THEN
733 EXIT;
734 END IF;
735 END LOOP;
736
737 /* Close the cursor */
738 close deleted_records;
739
740 /* Bug 2447478 END */
741
742 /* Number of rows updates */
743 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
744 arp_message.set_token('NUM_ROWS', to_char(g_count)); -- Bug 2447478
745
746
747 /* for changed customers */
748
749 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
750 arp_message.set_token('TABLE_NAME', 'PO_LOCATION_ASSOCIATIONS', FALSE);
751 arp_message.set_line('Updating changed customers.');
752
753 /* Bug 2447478 START */
754 g_count := 0;
755
756 /* Start Bug 4009128 : commented the below code*/
757 /* Obtain the value for customer_id and site_use_id */
758 /*
759 BEGIN
760 select distinct racm.customer_id,
761 racm.customer_site_id
762 into l_customer_id,
763 l_site_use_id
764 from ra_customer_merges racm,
765 PO_LOCATION_ASSOCIATIONS yt
766 where yt.customer_id = racm.duplicate_id
767 and yt.site_use_id = racm.duplicate_site_id
768 and racm.process_flag = 'N'
769 and racm.request_id = req_id
770 and racm.set_number = set_num;
771 EXCEPTION
772 WHEN NO_DATA_FOUND THEN
773 null;
774 WHEN OTHERS THEN
775 null;
776 END;
777 */
778
779 /* Open the merged_records cursor */
780 open merged_records;
781
782 LOOP
783 /* Fetch the data into local variables */
784 /* Bug 4009128 - Fetch Ct id and Ct site id */
785 FETCH merged_records BULK COLLECT INTO
786 MERGE_HEADER_ID_LIST,
787 PRIMARY_KEY_ID_LIST,
788 NEW_CUST_ID_LIST,
789 NEW_CUST_SITE_ID_LIST;
790
791 /*
792 If there is no more records, set the flag to true to indicate
793 that we are done fetching all records.
794 */
795 IF merged_records%NOTFOUND THEN
796 l_last_fetch := TRUE;
797 END IF;
798
799 /*
800 If there is no more records to be fetched and
801 no data was fetched at all, then exit the procedure call
802 */
803 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
804 exit;
805 END IF;
806
807 /*
808 Store the value of the column to be updated to local
809 variables.
810 */
811 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
812
813 /*Bug 4009128 - commented below assignment */
814 /* NUM_COL1_ORIG_LIST(I) := l_customer_id;
815 NUM_COL2_ORIG_LIST(I) := l_site_use_id; */
816
817 NUM_COL1_ORIG_LIST(I) := NEW_CUST_ID_LIST(I); -- Bug 4009128
818 NUM_COL2_ORIG_LIST(I) := NEW_CUST_SITE_ID_LIST(I); -- Bug 4009128
819 NUM_COL1_NEW_LIST(I) := NUM_COL1_ORIG_LIST(I);
820 NUM_COL2_NEW_LIST(I) := NUM_COL2_ORIG_LIST(I);
821 END LOOP;
822
823 /*
824 If auditing has been enabled, insert the changed records
825 into the HZ_CUSTOMER_MERGE_LOG table.
826 */
827 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
828 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
829 INSERT INTO HZ_CUSTOMER_MERGE_LOG(
830 MERGE_LOG_ID,
831 TABLE_NAME,
832 MERGE_HEADER_ID,
833 PRIMARY_KEY_ID,
834 NUM_COL1_ORIG,
835 NUM_COL1_NEW,
836 NUM_COL2_ORIG,
837 NUM_COL2_NEW,
838 ACTION_FLAG,
839 REQUEST_ID,
840 CREATED_BY,
841 CREATION_DATE,
842 LAST_UPDATE_LOGIN,
843 LAST_UPDATE_DATE,
844 LAST_UPDATED_BY)
845 VALUES(
846 HZ_CUSTOMER_MERGE_LOG_s.nextval,
847 'PO_LOCATION_ASSOCIATIONS',
848 MERGE_HEADER_ID_LIST(I),
849 PRIMARY_KEY_ID_LIST(I),
850 NUM_COL1_ORIG_LIST(I),
851 NUM_COL1_NEW_LIST(I),
852 NUM_COL2_ORIG_LIST(I),
853 NUM_COL2_NEW_LIST(I),
854 'U',
855 req_id,
856 hz_utility_pub.CREATED_BY,
857 hz_utility_pub.CREATION_DATE,
858 hz_utility_pub.LAST_UPDATE_LOGIN,
859 hz_utility_pub.LAST_UPDATE_DATE,
860 hz_utility_pub.LAST_UPDATED_BY);
861 END IF;
862
863 /*
864 Update all corresponding customer_id and site_use_id
865 due to merged/changed accounts
866 */
867 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
868 UPDATE PO_LOCATION_ASSOCIATIONS yt
869 SET CUSTOMER_ID=NEW_CUST_ID_LIST(I), --Bug 4009128
870 SITE_USE_ID=NEW_CUST_SITE_ID_LIST(I), --Bug 4009128
871 LAST_UPDATE_DATE = SYSDATE,
872 last_updated_by = arp_standard.profile.user_id,
873 last_update_login = arp_standard.profile.last_update_login,
874 REQUEST_ID = req_id,
875 PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id,
876 PROGRAM_ID = arp_standard.profile.program_id,
877 PROGRAM_UPDATE_DATE = SYSDATE
878 WHERE LOCATION_ID = PRIMARY_KEY_ID_LIST(I);
879
880 g_count := g_count + SQL%ROWCOUNT;
881
882 IF l_last_fetch THEN
883 EXIT;
884 END IF;
885 END LOOP;
886
887 /* Close the cursor */
888 close merged_records;
889
890 /* Bug 2447478 END */
891
892 /* Number of rows updates */
893 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
894 arp_message.set_token('NUM_ROWS', to_char(g_count)); -- Bug 2447478
895
896 END IF;
897
898 arp_message.set_line( 'POP_CMERGE_REQ.PO_LA()-' );
899
900
901 EXCEPTION
902 when others then
903 arp_message.set_error( 'POP_CMERGE_REQ.PO_LA');
904 raise;
905 END;
906
907
908 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
909
910 PROCEDURE MERGE (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
911 BEGIN
912
913 arp_message.set_line( 'POP_CMERGE_REQ.MERGE()+' );
914
915 PO_RL( req_id, set_num, process_mode );
916 PO_LA( req_id, set_num, process_mode );
917
918 arp_message.set_line( 'POP_CMERGE_REQ.MERGE()-' );
919
920 END MERGE;
921 end POP_CMERGE_REQ;
[Home] [Help]