[Home] [Help]
PACKAGE BODY: APPS.PNP_CMERGE
Source
1 PACKAGE BODY PNP_CMERGE AS
2 /* $Header: PNCMERGB.pls 115.9 2004/05/07 03:51:12 kkhegde ship $ */
3
4 ---------------------------------
5 -- Private Variable(s)
6 ---------------------------------
7 PROCEDURE merge ( req_id number,
8 set_num number,
9 process_mode varchar2
10 ) is
11
12 /* Lock leases */
13 CURSOR leases IS
14 SELECT lease_id
15 FROM pn_leases
16 WHERE customer_id IN (SELECT racm.duplicate_id
17 FROM ra_customer_merges racm
18 WHERE racm.process_flag = 'N'
19 AND racm.request_id = req_id
20 AND racm.set_number = set_num
21 )
22 FOR UPDATE NOWAIT;
23 /* Lock Tenancies */
24 CURSOR tenencies IS
25 SELECT tenancy_id
26 FROM pn_tenancies
27 WHERE customer_id IN (SELECT racm.duplicate_id
28 FROM ra_customer_merges racm
29 WHERE racm.process_flag = 'N'
30 AND racm.request_id = req_id
31 AND racm.set_number = set_num
32 )
33 FOR UPDATE NOWAIT;
34 /* Lock Tenancies History */
35 CURSOR tenencies_history IS
36 SELECT tenancy_history_id
37 FROM pn_tenancies_history
38 WHERE customer_id IN (SELECT racm.duplicate_id
39 FROM ra_customer_merges racm
40 WHERE racm.process_flag = 'N'
41 AND racm.request_id = req_id
42 AND racm.set_number = set_num
43 )
44 FOR UPDATE NOWAIT;
45 /* Lock Term Templates */
46 CURSOR term_templates IS
47 SELECT term_template_id
48 FROM pn_term_templates
49 WHERE customer_id IN (SELECT racm.duplicate_id
50 FROM ra_customer_merges racm
51 WHERE racm.process_flag = 'N'
52 AND racm.request_id = req_id
53 AND racm.set_number = set_num
54 )
55 FOR UPDATE NOWAIT;
56 /* Lock Terms */
57 CURSOR pmt_terms IS
58 SELECT payment_term_id
59 FROM pn_payment_terms
60 WHERE customer_id IN (SELECT racm.duplicate_id
61 FROM ra_customer_merges racm
62 WHERE racm.process_flag = 'N'
63 AND racm.request_id = req_id
64 AND racm.set_number = set_num
65 )
66 FOR UPDATE NOWAIT;
67 /* Lock Items */
68 CURSOR pmt_items IS
69 SELECT payment_item_id
70 FROM pn_payment_items
71 WHERE customer_id IN (SELECT racm.duplicate_id
72 FROM ra_customer_merges racm
73 WHERE racm.process_flag = 'N'
74 AND racm.request_id = req_id
75 AND racm.set_number = set_num
76 )
77 FOR UPDATE NOWAIT;
78 /* Lock Recovery Agreements */
79 CURSOR rec_agreements IS
80 SELECT rec_agreement_id
81 FROM pn_rec_agreements
82 WHERE customer_id IN (SELECT racm.duplicate_id
83 FROM ra_customer_merges racm
84 WHERE racm.process_flag = 'N'
85 AND racm.request_id = req_id
86 AND racm.set_number = set_num
87 )
88 FOR UPDATE NOWAIT;
89 /* Lock Area Class Line Details */
90 CURSOR rec_arcl_dtlln IS
91 SELECT area_class_dtl_id
92 FROM pn_rec_arcl_dtlln
93 WHERE cust_account_id IN
94 (SELECT racm.duplicate_id
95 FROM ra_customer_merges racm
96 WHERE racm.process_flag = 'N'
97 AND racm.request_id = req_id
98 AND racm.set_number = set_num
99 )
100 FOR UPDATE NOWAIT;
101 /* Lock Expense Class Line Details */
102 CURSOR rec_expcl_dtlln IS
103 SELECT expense_class_dtl_id
104 FROM pn_rec_expcl_dtlln
105 WHERE cust_account_id IN
106 (SELECT racm.duplicate_id
107 FROM ra_customer_merges racm
108 WHERE racm.process_flag = 'N'
109 AND racm.request_id = req_id
110 AND racm.set_number = set_num
111 )
112 FOR UPDATE NOWAIT;
113 /* Lock Recovery Period Lines */
114 CURSOR rec_period_lines IS
115 SELECT rec_period_lines_id
116 FROM pn_rec_period_lines
117 WHERE cust_account_id IN
118 (SELECT racm.duplicate_id
119 FROM ra_customer_merges racm
120 WHERE racm.process_flag = 'N'
121 AND racm.request_id = req_id
122 AND racm.set_number = set_num
123 )
124 FOR UPDATE NOWAIT;
125 /* Lock Customer Assignments */
126 CURSOR cust_assignments IS
127 SELECT cust_space_assign_id
128 FROM pn_space_assign_cust
129 WHERE cust_account_id IN
130 (SELECT racm.duplicate_id
131 FROM ra_customer_merges racm
132 WHERE racm.process_flag = 'N'
133 AND racm.request_id = req_id
134 AND racm.set_number = set_num
135 )
136 FOR UPDATE NOWAIT;
137
138 BEGIN
139
140 arp_message.set_line ( 'PNP_CMERGE.MERGE()+');
141
142 IF( process_mode = 'LOCK' ) THEN
143
144 /* Lock leases */
145 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
146 arp_message.set_token( 'TABLE_NAME', 'PN_LEASES', FALSE );
147
148 OPEN leases;
149 CLOSE leases;
150
151 /* Lock Tenancies */
152 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
153 arp_message.set_token( 'TABLE_NAME', 'PN_TENANCIES', FALSE );
154
155 OPEN tenencies;
156 CLOSE tenencies;
157
158 /* Lock Tenancies History */
159 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
160 arp_message.set_token( 'TABLE_NAME', 'PN_TENANCIES_HISTORY', FALSE );
161
162 OPEN tenencies_history;
163 CLOSE tenencies_history;
164
165 /* Lock Term Templates */
166 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
167 arp_message.set_token( 'TABLE_NAME', 'PN_TERM_TEMPLATES', FALSE );
168
169 OPEN term_templates;
170 CLOSE term_templates;
171
172 /* Lock Terms */
173 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
174 arp_message.set_token( 'TABLE_NAME', 'PN_PAYMENT_TERMS', FALSE );
175
176 OPEN pmt_terms;
177 CLOSE pmt_terms;
178
179 /* Lock Items */
180 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
181 arp_message.set_token( 'TABLE_NAME', 'PN_PAYMENT_ITEMS', FALSE );
182
183 OPEN pmt_items;
184 CLOSE pmt_items;
185
186 /* Lock Recovery Agreements */
187 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
188 arp_message.set_token( 'TABLE_NAME', 'PN_REC_AGREEMENTS', FALSE );
189
190 OPEN rec_agreements;
191 CLOSE rec_agreements;
192
193 /* Lock Area Class Line Details */
194 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
195 arp_message.set_token( 'TABLE_NAME', 'PN_REC_ARCL_DTLLN', FALSE );
196
197 OPEN rec_arcl_dtlln;
198 CLOSE rec_arcl_dtlln;
199
200 /* Lock Expense Class Line Details */
201 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
202 arp_message.set_token( 'TABLE_NAME', 'PN_REC_EXPCL_DTLLN', FALSE );
203
204 OPEN rec_expcl_dtlln;
205 CLOSE rec_expcl_dtlln;
206
207 /* Lock Recovery Period Lines */
208 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
209 arp_message.set_token( 'TABLE_NAME', 'PN_REC_PERIOD_LINES', FALSE );
210
211 OPEN rec_period_lines;
212 CLOSE rec_period_lines;
213
214 /* Lock Customer Assignments */
215 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
216 arp_message.set_token( 'TABLE_NAME', 'PN_SPACE_ASSIGN_CUST', FALSE );
217
218 OPEN cust_assignments;
219 CLOSE cust_assignments;
220
221 ELSE
222
223 update_leases ( req_id => req_id,
224 set_num => set_num,
225 process_mode => process_mode
226 );
227
228 update_tenancies ( req_id => req_id,
229 set_num => set_num,
230 process_mode => process_mode
231 );
232
233 update_tenancies_history ( req_id => req_id,
234 set_num => set_num,
235 process_mode => process_mode
236 );
237
238 update_term_templates ( req_id => req_id,
239 set_num => set_num,
240 process_mode => process_mode
241 );
242
243 update_payment_terms ( req_id => req_id,
244 set_num => set_num,
245 process_mode => process_mode
246 );
247
248 update_payment_items ( req_id => req_id,
249 set_num => set_num,
250 process_mode => process_mode
251 );
252
253 update_rec_agreements ( req_id => req_id,
254 set_num => set_num,
255 process_mode => process_mode
256 );
257
258 update_rec_arcl_dtln ( req_id => req_id,
259 set_num => set_num,
260 process_mode => process_mode
261 );
262
263 update_rec_expcl_dtln ( req_id => req_id,
264 set_num => set_num,
265 process_mode => process_mode
266 );
267
268 update_rec_period_lines ( req_id => req_id,
269 set_num => set_num,
270 process_mode => process_mode
271 );
272
273 update_space_assign_cust ( req_id => req_id,
274 set_num => set_num,
275 process_mode => process_mode
276 );
277
278 END IF;
279
280 arp_message.set_line ( 'PNP_CMERGE.MERGE()-');
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 arp_message.set_error( 'PNP_CMERGE.MERGE');
285 RAISE;
286
287 END MERGE;
288
289 /*===========================================================================+
290 | PROCEDURE
291 | update_leases
292 |
293 | DESCRIPTION
294 | Account merge procedure for the table, pn_leases
295 | Column updated Corresponding HZ table.column
296 | -------------- --------------------------------
297 | customer_id HZ_CUST_ACCOUNTS.cust_account_id
298 |
299 | SCOPE - PRIVATE
300 |
301 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
302 |
303 | ARGUMENTS : IN: req_id, set_num, process_mode
304 |
305 | NOTES :
306 |
307 | MODIFICATION HISTORY
308 |
309 | 29-apr-2004 Perl Script Created
310 | 29-apr-2004 Kiran Finalised
311 +===========================================================================*/
312
313 PROCEDURE update_leases (req_id NUMBER,
314 set_num NUMBER,
315 process_mode VARCHAR2) IS
316
317 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
318 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
319 INDEX BY BINARY_INTEGER;
320 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
321
322 TYPE LEASE_ID_LIST_TYPE IS TABLE OF
323 PN_LEASES.LEASE_ID%TYPE
324 INDEX BY BINARY_INTEGER;
325 PRIMARY_KEY_ID_LIST LEASE_ID_LIST_TYPE;
326
327 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
328 PN_LEASES.CUSTOMER_ID%TYPE
329 INDEX BY BINARY_INTEGER;
330 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
331 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
332
333 l_profile_val VARCHAR2(30);
334 CURSOR merged_records IS
335 SELECT DISTINCT m.CUSTOMER_MERGE_HEADER_ID
336 ,yt.LEASE_ID
337 ,yt.CUSTOMER_ID
338 FROM PN_LEASES yt
339 , RA_CUSTOMER_MERGES m
340 WHERE yt.CUSTOMER_ID = m.DUPLICATE_ID
341 AND m.process_flag = 'N'
342 AND m.request_id = req_id
343 AND m.set_number = set_num;
344
345 l_last_fetch BOOLEAN;
346 l_count NUMBER;
347
348 BEGIN
349 /* init variables */
350 l_last_fetch := FALSE;
351 l_count := 0;
352
353 IF process_mode='LOCK' THEN
354 NULL;
355 ELSE
356 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
357 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','PN_LEASES',FALSE);
358 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
359 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
360
361 OPEN merged_records;
362
363 LOOP
364
365 FETCH merged_records BULK COLLECT INTO
366 MERGE_HEADER_ID_LIST
367 , PRIMARY_KEY_ID_LIST
368 , NUM_COL1_ORIG_LIST
369 LIMIT 1000;
370
371 IF merged_records%NOTFOUND THEN
372 l_last_fetch := TRUE;
373 END IF;
374
375 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
376 exit;
377 END IF;
378
379 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
380 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
381 END LOOP;
382
383 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
384 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
385 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
386 MERGE_LOG_ID,
387 TABLE_NAME,
388 MERGE_HEADER_ID,
389 PRIMARY_KEY_ID,
390 NUM_COL1_ORIG,
391 NUM_COL1_NEW,
392 ACTION_FLAG,
393 REQUEST_ID,
394 CREATED_BY,
395 CREATION_DATE,
396 LAST_UPDATE_LOGIN,
397 LAST_UPDATE_DATE,
398 LAST_UPDATED_BY
399 ) VALUES (
400 HZ_CUSTOMER_MERGE_LOG_s.NEXTVAL,
401 'PN_LEASES',
402 MERGE_HEADER_ID_LIST(I),
403 PRIMARY_KEY_ID_LIST(I),
404 NUM_COL1_ORIG_LIST(I),
405 NUM_COL1_NEW_LIST(I),
406 'U',
407 req_id,
408 hz_utility_pub.CREATED_BY,
409 hz_utility_pub.CREATION_DATE,
410 hz_utility_pub.LAST_UPDATE_LOGIN,
411 hz_utility_pub.LAST_UPDATE_DATE,
412 hz_utility_pub.LAST_UPDATED_BY
413 );
414
415 END IF;
416
417 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
418 UPDATE PN_LEASES yt SET
419 CUSTOMER_ID = NUM_COL1_NEW_LIST(I)
420 , LAST_UPDATE_DATE = SYSDATE
421 , last_updated_by = arp_standard.profile.user_id
422 , last_update_login= arp_standard.profile.last_update_login
423 WHERE LEASE_ID=PRIMARY_KEY_ID_LIST(I);
424
425 l_count := l_count + MERGE_HEADER_ID_LIST.COUNT;
426
427 IF l_last_fetch THEN
428 EXIT;
429 END IF;
430
431 END LOOP;
432
433 arp_message.set_name('AR','AR_ROWS_UPDATED');
434 arp_message.set_token('NUM_ROWS',to_char(l_count));
435
436 END IF;
437 EXCEPTION
438 WHEN OTHERS THEN
439 arp_message.set_line( 'update_leases');
440 RAISE;
441 END update_leases;
442
443 /*===========================================================================+
444 | PROCEDURE
445 | update_tenancies
446 |
447 | DESCRIPTION
448 | Account merge procedure for the table, pn_tenencies
449 | Column updated Corresponding HZ table.column
450 | -------------------- -----------------------------
451 | customer_id HZ_CUST_ACCOUNTS.cust_account_id
452 | customer_site_use_id HZ_CUST_SITE_USES.site_use_id
453 |
454 | SCOPE - PRIVATE
455 |
456 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
457 |
458 | ARGUMENTS : IN: req_id, set_num, process_mode
459 |
460 | NOTES :
461 |
462 | MODIFICATION HISTORY
463 |
464 | 29-apr-2004 Perl Script Created
465 | 29-apr-2004 Kiran Finalised
466 +===========================================================================*/
467
468 PROCEDURE update_tenancies (req_id NUMBER,
469 set_num NUMBER,
470 process_mode VARCHAR2) IS
471
472 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
473 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
474 INDEX BY BINARY_INTEGER;
475 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
476
477 TYPE TENANCY_ID_LIST_TYPE IS TABLE OF
478 PN_TENANCIES.TENANCY_ID%TYPE
479 INDEX BY BINARY_INTEGER;
480 PRIMARY_KEY_ID_LIST TENANCY_ID_LIST_TYPE;
481
482 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
483 PN_TENANCIES.CUSTOMER_ID%TYPE
484 INDEX BY BINARY_INTEGER;
485 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
486 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
487
488 TYPE CUSTOMER_SITE_USE_ID_LIST_TYPE IS TABLE OF
489 PN_TENANCIES.CUSTOMER_SITE_USE_ID%TYPE
490 INDEX BY BINARY_INTEGER;
491 NUM_COL2_ORIG_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
492 NUM_COL2_NEW_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
493
494 l_profile_val VARCHAR2(30);
495 CURSOR merged_records IS
496 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
497 ,yt.TENANCY_ID
498 ,yt.CUSTOMER_ID
499 ,yt.CUSTOMER_SITE_USE_ID
500 FROM PN_TENANCIES yt
501 ,RA_CUSTOMER_MERGES m
502 WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID
503 OR yt.CUSTOMER_SITE_USE_ID = m.DUPLICATE_SITE_ID)
504 AND m.process_flag = 'N'
505 AND m.request_id = req_id
506 AND m.set_number = set_num;
507
508 l_last_fetch BOOLEAN;
509 l_count NUMBER;
510
511 BEGIN
512 /* init variables */
513 l_last_fetch := FALSE;
514 l_count := 0;
515
516 IF process_mode='LOCK' THEN
517 NULL;
518 ELSE
519 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
520 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','PN_TENANCIES',FALSE);
521 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
522 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
523
524 OPEN merged_records;
525
526 LOOP
527
528 FETCH merged_records BULK COLLECT INTO
529 MERGE_HEADER_ID_LIST
530 , PRIMARY_KEY_ID_LIST
531 , NUM_COL1_ORIG_LIST
532 , NUM_COL2_ORIG_LIST
533 LIMIT 1000;
534
535 IF merged_records%NOTFOUND THEN
536 l_last_fetch := TRUE;
537 END IF;
538
539 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
540 EXIT;
541 END IF;
542
543 FOR I IN 1..MERGE_HEADER_ID_LIST.COUNT LOOP
544 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
545 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
546 END LOOP;
547
548 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
549 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
550 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
551 MERGE_LOG_ID,
552 TABLE_NAME,
553 MERGE_HEADER_ID,
554 PRIMARY_KEY_ID,
555 NUM_COL1_ORIG,
556 NUM_COL1_NEW,
557 NUM_COL2_ORIG,
558 NUM_COL2_NEW,
559 ACTION_FLAG,
560 REQUEST_ID,
561 CREATED_BY,
562 CREATION_DATE,
563 LAST_UPDATE_LOGIN,
564 LAST_UPDATE_DATE,
565 LAST_UPDATED_BY
566 ) VALUES (
567 HZ_CUSTOMER_MERGE_LOG_s.NEXTVAL,
568 'PN_TENANCIES',
569 MERGE_HEADER_ID_LIST(I),
570 PRIMARY_KEY_ID_LIST(I),
571 NUM_COL1_ORIG_LIST(I),
572 NUM_COL1_NEW_LIST(I),
573 NUM_COL2_ORIG_LIST(I),
574 NUM_COL2_NEW_LIST(I),
575 'U',
576 req_id,
577 hz_utility_pub.CREATED_BY,
578 hz_utility_pub.CREATION_DATE,
579 hz_utility_pub.LAST_UPDATE_LOGIN,
580 hz_utility_pub.LAST_UPDATE_DATE,
581 hz_utility_pub.LAST_UPDATED_BY
582 );
583
584 END IF;
585
586 FORALL I IN 1..MERGE_HEADER_ID_LIST.COUNT
587 UPDATE PN_TENANCIES yt SET
588 CUSTOMER_ID = NUM_COL1_NEW_LIST(I)
589 , CUSTOMER_SITE_USE_ID= NUM_COL2_NEW_LIST(I)
590 , LAST_UPDATE_DATE = SYSDATE
591 , last_updated_by = arp_standard.profile.user_id
592 , last_update_login = arp_standard.profile.last_update_login
593 WHERE TENANCY_ID=PRIMARY_KEY_ID_LIST(I);
594
595 l_count := l_count + MERGE_HEADER_ID_LIST.COUNT;
596
597 IF l_last_fetch THEN
598 EXIT;
599 END IF;
600
601 END LOOP;
602
603 arp_message.set_name('AR','AR_ROWS_UPDATED');
604 arp_message.set_token('NUM_ROWS',to_char(l_count));
605
606 END IF;
607
608 EXCEPTION
609 WHEN OTHERS THEN
610 arp_message.set_line( 'update_tenancies');
611 RAISE;
612 END update_tenancies;
613
614 /*===========================================================================+
615 | PROCEDURE
616 | update_tenancies_history
617 |
618 | DESCRIPTION
619 | Account merge procedure for the table, pn_tenancies_history
620 | Column updated Corresponding HZ table.column
621 | -------------------- -----------------------------
622 | customer_id HZ_CUST_ACCOUNTS.cust_account_id
623 | customer_site_use_id HZ_CUST_SITE_USES.site_use_id
624 |
625 | SCOPE - PRIVATE
626 |
627 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
628 |
629 | ARGUMENTS : IN: req_id, set_num, process_mode
630 |
631 | NOTES :
632 |
633 | MODIFICATION HISTORY
634 |
635 | 29-apr-2004 Perl Script Created
636 | 29-apr-2004 Kiran Finalised
637 +===========================================================================*/
638
639 PROCEDURE update_tenancies_history (req_id NUMBER,
640 set_num NUMBER,
641 process_mode VARCHAR2) IS
642
643 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
644 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
645 INDEX BY BINARY_INTEGER;
646 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
647
648 TYPE TENANCY_HISTORY_ID_LIST_TYPE IS TABLE OF
649 PN_TENANCIES_HISTORY.TENANCY_HISTORY_ID%TYPE
650 INDEX BY BINARY_INTEGER;
651 PRIMARY_KEY_ID_LIST TENANCY_HISTORY_ID_LIST_TYPE;
652
653 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
654 PN_TENANCIES_HISTORY.CUSTOMER_ID%TYPE
655 INDEX BY BINARY_INTEGER;
656 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
657 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
658
659 TYPE CUSTOMER_SITE_USE_ID_LIST_TYPE IS TABLE OF
660 PN_TENANCIES_HISTORY.CUSTOMER_SITE_USE_ID%TYPE
661 INDEX BY BINARY_INTEGER;
662 NUM_COL2_ORIG_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
663 NUM_COL2_NEW_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
664
665 l_profile_val VARCHAR2(30);
666 CURSOR merged_records IS
667 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
668 ,yt.TENANCY_HISTORY_ID
669 ,yt.CUSTOMER_ID
670 ,yt.CUSTOMER_SITE_USE_ID
671 FROM PN_TENANCIES_HISTORY yt,
672 RA_CUSTOMER_MERGES m
673 WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID
674 OR yt.CUSTOMER_SITE_USE_ID = m.DUPLICATE_SITE_ID)
675 AND m.process_flag = 'N'
676 AND m.request_id = req_id
677 AND m.set_number = set_num;
678
679 l_last_fetch BOOLEAN;
680 l_count NUMBER;
681
682 BEGIN
683 /* init variables */
684 l_last_fetch := FALSE;
685 l_count := 0;
686
687 IF process_mode='LOCK' THEN
688 NULL;
689 ELSE
690 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
691 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','PN_TENANCIES_HISTORY',FALSE);
692 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
693 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
694
695 OPEN merged_records;
696
697 LOOP
698
699 FETCH merged_records BULK COLLECT INTO
700 MERGE_HEADER_ID_LIST
701 , PRIMARY_KEY_ID_LIST
702 , NUM_COL1_ORIG_LIST
703 , NUM_COL2_ORIG_LIST
704 LIMIT 1000;
705
706 IF merged_records%NOTFOUND THEN
707 l_last_fetch := TRUE;
708 END IF;
709
710 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
711 EXIT;
712 END IF;
713
714 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
715 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
716 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
717 END LOOP;
718
719 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
720 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
721 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
722 MERGE_LOG_ID,
723 TABLE_NAME,
724 MERGE_HEADER_ID,
725 PRIMARY_KEY_ID,
726 NUM_COL1_ORIG,
727 NUM_COL1_NEW,
728 NUM_COL2_ORIG,
729 NUM_COL2_NEW,
730 ACTION_FLAG,
731 REQUEST_ID,
732 CREATED_BY,
733 CREATION_DATE,
734 LAST_UPDATE_LOGIN,
735 LAST_UPDATE_DATE,
736 LAST_UPDATED_BY
737 ) VALUES (
738 HZ_CUSTOMER_MERGE_LOG_s.NEXTVAL,
739 'PN_TENANCIES_HISTORY',
740 MERGE_HEADER_ID_LIST(I),
741 PRIMARY_KEY_ID_LIST(I),
742 NUM_COL1_ORIG_LIST(I),
743 NUM_COL1_NEW_LIST(I),
744 NUM_COL2_ORIG_LIST(I),
745 NUM_COL2_NEW_LIST(I),
746 'U',
747 req_id,
748 hz_utility_pub.CREATED_BY,
749 hz_utility_pub.CREATION_DATE,
750 hz_utility_pub.LAST_UPDATE_LOGIN,
751 hz_utility_pub.LAST_UPDATE_DATE,
752 hz_utility_pub.LAST_UPDATED_BY
753 );
754
755 END IF;
756
757 FORALL I IN 1..MERGE_HEADER_ID_LIST.COUNT
758 UPDATE PN_TENANCIES_HISTORY yt SET
759 CUSTOMER_ID = NUM_COL1_NEW_LIST(I)
760 , CUSTOMER_SITE_USE_ID= NUM_COL2_NEW_LIST(I)
761 , LAST_UPDATE_DATE = SYSDATE
762 , last_updated_by = arp_standard.profile.user_id
763 , last_update_login = arp_standard.profile.last_update_login
764 WHERE TENANCY_HISTORY_ID = PRIMARY_KEY_ID_LIST(I);
765
766 l_count := l_count + MERGE_HEADER_ID_LIST.COUNT;
767
768 IF l_last_fetch THEN
769 EXIT;
770 END IF;
771
772 END LOOP;
773
774 arp_message.set_name('AR','AR_ROWS_UPDATED');
775 arp_message.set_token('NUM_ROWS',to_char(l_count));
776 END IF;
777 EXCEPTION
778 WHEN OTHERS THEN
779 arp_message.set_line( 'update_tenancies_history');
780 RAISE;
781 END update_tenancies_history;
782
783 /*===========================================================================+
784 | PROCEDURE
785 | update_term_templates
786 |
787 | DESCRIPTION
788 | Account merge procedure for the table, pn_term_templates
789 | Column updated Corresponding HZ table.column
790 | -------------------- -----------------------------
791 | customer_id HZ_CUST_ACCOUNTS.cust_account_id
792 | customer_site_use_id HZ_CUST_SITE_USES.site_use_id
793 | cust_ship_site_id HZ_CUST_SITE_USES.site_use_id
794 |
795 | SCOPE - PRIVATE
796 |
797 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
798 |
799 | ARGUMENTS : IN: req_id, set_num, process_mode
800 |
801 | NOTES :
802 |
803 | MODIFICATION HISTORY
804 |
805 | 29-apr-2004 Perl Script Created
806 | 29-apr-2004 Kiran Finalised
807 +===========================================================================*/
808
809 PROCEDURE update_term_templates (req_id NUMBER,
810 set_num NUMBER,
811 process_mode VARCHAR2) IS
812
813 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
814 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
815 INDEX BY BINARY_INTEGER;
816 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
817
818 TYPE TERM_TEMPLATE_ID_LIST_TYPE IS TABLE OF
819 PN_TERM_TEMPLATES.TERM_TEMPLATE_ID%TYPE
820 INDEX BY BINARY_INTEGER;
821 PRIMARY_KEY_ID_LIST TERM_TEMPLATE_ID_LIST_TYPE;
822
823 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
824 PN_TERM_TEMPLATES.CUSTOMER_ID%TYPE
825 INDEX BY BINARY_INTEGER;
826 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
827 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
828
829 TYPE CUSTOMER_SITE_USE_ID_LIST_TYPE IS TABLE OF
830 PN_TERM_TEMPLATES.CUSTOMER_SITE_USE_ID%TYPE
831 INDEX BY BINARY_INTEGER;
832 NUM_COL2_ORIG_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
833 NUM_COL2_NEW_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
834
835 TYPE CUST_SHIP_SITE_ID_LIST_TYPE IS TABLE OF
836 PN_TERM_TEMPLATES.CUST_SHIP_SITE_ID%TYPE
837 INDEX BY BINARY_INTEGER;
838 NUM_COL3_ORIG_LIST CUST_SHIP_SITE_ID_LIST_TYPE;
839 NUM_COL3_NEW_LIST CUST_SHIP_SITE_ID_LIST_TYPE;
840
841 l_profile_val VARCHAR2(30);
842
843 CURSOR merged_records IS
844 SELECT DISTINCT m.CUSTOMER_MERGE_HEADER_ID
845 ,yt.TERM_TEMPLATE_ID
846 ,yt.CUSTOMER_ID
847 ,yt.CUSTOMER_SITE_USE_ID
848 ,yt.CUST_SHIP_SITE_ID
849 FROM PN_TERM_TEMPLATES yt,
850 RA_CUSTOMER_MERGES m
851 WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID
852 OR yt.CUSTOMER_SITE_USE_ID = m.DUPLICATE_SITE_ID
853 OR yt.CUST_SHIP_SITE_ID = m.DUPLICATE_SITE_ID)
854 AND m.process_flag = 'N'
855 AND m.request_id = req_id
856 AND m.set_number = set_num;
857
858 l_last_fetch BOOLEAN;
859 l_count NUMBER;
860
861 BEGIN
862 /* init variables */
863 l_last_fetch := FALSE;
864 l_count := 0;
865
866 IF process_mode='LOCK' THEN
867 NULL;
868 ELSE
869 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
870 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','PN_TERM_TEMPLATES',FALSE);
871 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
872 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
873
874 OPEN merged_records;
875
876 LOOP
877 FETCH merged_records BULK COLLECT INTO
878 MERGE_HEADER_ID_LIST
879 , PRIMARY_KEY_ID_LIST
880 , NUM_COL1_ORIG_LIST
881 , NUM_COL2_ORIG_LIST
882 , NUM_COL3_ORIG_LIST
883 LIMIT 1000;
884
885 IF merged_records%NOTFOUND THEN
886 l_last_fetch := TRUE;
887 END IF;
888
889 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
890 EXIT;
891 END IF;
892
893 FOR I IN 1..MERGE_HEADER_ID_LIST.COUNT LOOP
894 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
895 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
896 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
897 END LOOP;
898
899 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
900 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
901 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
902 MERGE_LOG_ID,
903 TABLE_NAME,
904 MERGE_HEADER_ID,
905 PRIMARY_KEY_ID,
906 NUM_COL1_ORIG,
907 NUM_COL1_NEW,
908 NUM_COL2_ORIG,
909 NUM_COL2_NEW,
910 NUM_COL3_ORIG,
911 NUM_COL3_NEW,
912 ACTION_FLAG,
913 REQUEST_ID,
914 CREATED_BY,
915 CREATION_DATE,
916 LAST_UPDATE_LOGIN,
917 LAST_UPDATE_DATE,
918 LAST_UPDATED_BY
919 ) VALUES (
920 HZ_CUSTOMER_MERGE_LOG_s.NEXTVAL,
921 'PN_TERM_TEMPLATES',
922 MERGE_HEADER_ID_LIST(I),
923 PRIMARY_KEY_ID_LIST(I),
924 NUM_COL1_ORIG_LIST(I),
925 NUM_COL1_NEW_LIST(I),
926 NUM_COL2_ORIG_LIST(I),
927 NUM_COL2_NEW_LIST(I),
928 NUM_COL3_ORIG_LIST(I),
929 NUM_COL3_NEW_LIST(I),
930 'U',
931 req_id,
932 hz_utility_pub.CREATED_BY,
933 hz_utility_pub.CREATION_DATE,
934 hz_utility_pub.LAST_UPDATE_LOGIN,
935 hz_utility_pub.LAST_UPDATE_DATE,
936 hz_utility_pub.LAST_UPDATED_BY
937 );
938
939 END IF;
940
941 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
942 UPDATE PN_TERM_TEMPLATES yt SET
943 CUSTOMER_ID = NUM_COL1_NEW_LIST(I)
944 , CUSTOMER_SITE_USE_ID= NUM_COL2_NEW_LIST(I)
945 , CUST_SHIP_SITE_ID = NUM_COL3_NEW_LIST(I)
946 , LAST_UPDATE_DATE = SYSDATE
947 , last_updated_by = arp_standard.profile.user_id
948 , last_update_login = arp_standard.profile.last_update_login
949 WHERE TERM_TEMPLATE_ID = PRIMARY_KEY_ID_LIST(I);
950
951 l_count := l_count + MERGE_HEADER_ID_LIST.COUNT;
952
953 IF l_last_fetch THEN
954 EXIT;
955 END IF;
956
957 END LOOP;
958
959 arp_message.set_name('AR','AR_ROWS_UPDATED');
960 arp_message.set_token('NUM_ROWS',to_char(l_count));
961 END IF;
962 EXCEPTION
963 WHEN OTHERS THEN
964 arp_message.set_line( 'update_term_templates');
965 RAISE;
966 END update_term_templates;
967
968 /*===========================================================================+
969 | PROCEDURE
970 | update_payment_terms
971 |
972 | DESCRIPTION
973 | Account merge procedure for the table, pn_payment_terms_all
974 | Column updated Corresponding HZ table.column
975 | -------------------- -----------------------------
976 | customer_id HZ_CUST_ACCOUNTS.cust_account_id
977 | customer_site_use_id HZ_CUST_SITE_USES.site_use_id
978 | cust_ship_site_id HZ_CUST_SITE_USES.site_use_id
979 |
980 | SCOPE - PRIVATE
981 |
982 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
983 |
984 | ARGUMENTS : IN: req_id, set_num, process_mode
985 |
986 | OUT: none
987 |
988 | NOTES :
989 |
990 | MODIFICATION HISTORY
991 |
992 | 18-FEB-2003 Perl Script Created
993 | 18-FEB-2003 Kiran Finalised
994 | 29-apr-2004 Kiran Added code to update ship_site_id
995 +===========================================================================*/
996
997 PROCEDURE update_payment_terms (req_id NUMBER,
998 set_num NUMBER,
999 process_mode VARCHAR2) IS
1000
1001 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1002 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1003 INDEX BY BINARY_INTEGER;
1004 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1005
1006 TYPE PAYMENT_TERM_ID_LIST_TYPE IS TABLE OF
1007 PN_PAYMENT_TERMS.PAYMENT_TERM_ID%TYPE
1008 INDEX BY BINARY_INTEGER;
1009 PRIMARY_KEY_ID_LIST PAYMENT_TERM_ID_LIST_TYPE;
1010
1011 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
1012 PN_PAYMENT_TERMS.CUSTOMER_ID%TYPE
1013 INDEX BY BINARY_INTEGER;
1014 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
1015 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
1016
1017 TYPE CUSTOMER_SITE_USE_ID_LIST_TYPE IS TABLE OF
1018 PN_PAYMENT_TERMS.CUSTOMER_SITE_USE_ID%TYPE
1019 INDEX BY BINARY_INTEGER;
1020 NUM_COL2_ORIG_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
1021 NUM_COL2_NEW_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
1022
1023 TYPE CUST_SHIP_SITE_ID_LIST_TYPE IS TABLE OF
1024 PN_PAYMENT_TERMS.CUST_SHIP_SITE_ID%TYPE
1025 INDEX BY BINARY_INTEGER;
1026 NUM_COL3_ORIG_LIST CUST_SHIP_SITE_ID_LIST_TYPE;
1027 NUM_COL3_NEW_LIST CUST_SHIP_SITE_ID_LIST_TYPE;
1028
1029 l_profile_val VARCHAR2(30);
1030
1031 CURSOR merged_records IS
1032 SELECT DISTINCT m.CUSTOMER_MERGE_HEADER_ID
1033 ,yt.PAYMENT_TERM_ID
1034 ,yt.CUSTOMER_ID
1035 ,yt.CUSTOMER_SITE_USE_ID
1036 ,yt.CUST_SHIP_SITE_ID
1037 FROM PN_PAYMENT_TERMS yt,
1038 RA_CUSTOMER_MERGES m
1039 WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID
1040 OR yt.CUSTOMER_SITE_USE_ID = m.DUPLICATE_SITE_ID
1041 OR yt.CUST_SHIP_SITE_ID = m.DUPLICATE_SITE_ID)
1042 AND m.process_flag = 'N'
1043 AND m.request_id = req_id
1044 AND m.set_number = set_num;
1045
1046 l_last_fetch BOOLEAN;
1047 l_count NUMBER;
1048 BEGIN
1049 /* init variables */
1050 l_last_fetch := FALSE;
1051 l_count := 0;
1052
1053 IF process_mode='LOCK' THEN
1054 NULL;
1055 ELSE
1056 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1057 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','PN_PAYMENT_TERMS',FALSE);
1058 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1059 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1060
1061 OPEN merged_records;
1062
1063 LOOP
1064 FETCH merged_records BULK COLLECT INTO
1065 MERGE_HEADER_ID_LIST
1066 , PRIMARY_KEY_ID_LIST
1067 , NUM_COL1_ORIG_LIST
1068 , NUM_COL2_ORIG_LIST
1069 , NUM_COL3_ORIG_LIST
1070 LIMIT 1000;
1071
1072 IF merged_records%NOTFOUND THEN
1073 l_last_fetch := TRUE;
1074 END IF;
1075
1076 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1077 EXIT;
1078 END IF;
1079
1080 FOR I IN 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1081 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1082 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1083 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
1084 END LOOP;
1085
1086 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1087 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1088 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1089 MERGE_LOG_ID,
1090 TABLE_NAME,
1091 MERGE_HEADER_ID,
1092 PRIMARY_KEY_ID,
1093 NUM_COL1_ORIG,
1094 NUM_COL1_NEW,
1095 NUM_COL2_ORIG,
1096 NUM_COL2_NEW,
1097 NUM_COL3_ORIG,
1098 NUM_COL3_NEW,
1099 ACTION_FLAG,
1100 REQUEST_ID,
1101 CREATED_BY,
1102 CREATION_DATE,
1103 LAST_UPDATE_LOGIN,
1104 LAST_UPDATE_DATE,
1105 LAST_UPDATED_BY
1106 ) VALUES (
1107 HZ_CUSTOMER_MERGE_LOG_s.NEXTVAL,
1108 'PN_PAYMENT_TERMS',
1109 MERGE_HEADER_ID_LIST(I),
1110 PRIMARY_KEY_ID_LIST(I),
1111 NUM_COL1_ORIG_LIST(I),
1112 NUM_COL1_NEW_LIST(I),
1113 NUM_COL2_ORIG_LIST(I),
1114 NUM_COL2_NEW_LIST(I),
1115 NUM_COL3_ORIG_LIST(I),
1116 NUM_COL3_NEW_LIST(I),
1117 'U',
1118 req_id,
1119 hz_utility_pub.CREATED_BY,
1120 hz_utility_pub.CREATION_DATE,
1121 hz_utility_pub.LAST_UPDATE_LOGIN,
1122 hz_utility_pub.LAST_UPDATE_DATE,
1123 hz_utility_pub.LAST_UPDATED_BY
1124 );
1125
1126 END IF;
1127
1128 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1129 UPDATE PN_PAYMENT_TERMS yt SET
1130 CUSTOMER_ID = NUM_COL1_NEW_LIST(I)
1131 , CUSTOMER_SITE_USE_ID= NUM_COL2_NEW_LIST(I)
1132 , CUST_SHIP_SITE_ID = NUM_COL3_NEW_LIST(I)
1133 , LAST_UPDATE_DATE = SYSDATE
1134 , last_updated_by = arp_standard.profile.user_id
1135 , last_update_login = arp_standard.profile.last_update_login
1136 WHERE PAYMENT_TERM_ID=PRIMARY_KEY_ID_LIST(I);
1137
1138 l_count := l_count + MERGE_HEADER_ID_LIST.COUNT;
1139
1140 IF l_last_fetch THEN
1141 EXIT;
1142 END IF;
1143
1144 END LOOP;
1145
1146 arp_message.set_name('AR','AR_ROWS_UPDATED');
1147 arp_message.set_token('NUM_ROWS',to_char(l_count));
1148 END IF;
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 arp_message.set_line( 'update_payment_terms');
1152 RAISE;
1153 END update_payment_terms;
1154
1155 /*===========================================================================+
1156 | PROCEDURE
1157 | update_payment_items
1158 |
1159 | DESCRIPTION
1160 | Account merge procedure for the table, pn_payment_items_all
1161 | Column updated Corresponding HZ table.column
1162 | -------------------- -----------------------------
1163 | customer_id HZ_CUST_ACCOUNTS.cust_account_id
1164 | customer_site_use_id HZ_CUST_SITE_USES.site_use_id
1165 | cust_ship_site_id HZ_CUST_SITE_USES.site_use_id
1166 |
1167 | SCOPE - PRIVATE
1168 |
1169 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1170 |
1171 | ARGUMENTS : IN: req_id, set_num, process_mode
1172 |
1173 | NOTES :
1174 |
1175 | MODIFICATION HISTORY
1176 |
1177 | 18-feb-2003 Perl Script Created
1178 | 18-feb-2003 Kiran Hegde Finalised
1179 | 29-apr-2004 Kiran Added code to update ship_site_id
1180 +===========================================================================*/
1181
1182 PROCEDURE update_payment_items (req_id NUMBER,
1183 set_num NUMBER,
1184 process_mode VARCHAR2) IS
1185
1186 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1187 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1188 INDEX BY BINARY_INTEGER;
1189 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1190
1191 TYPE PAYMENT_ITEM_ID_LIST_TYPE IS TABLE OF
1192 PN_PAYMENT_ITEMS.PAYMENT_ITEM_ID%TYPE
1193 INDEX BY BINARY_INTEGER;
1194 PRIMARY_KEY_ID_LIST PAYMENT_ITEM_ID_LIST_TYPE;
1195
1196 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
1197 PN_PAYMENT_ITEMS.CUSTOMER_ID%TYPE
1198 INDEX BY BINARY_INTEGER;
1199 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
1200 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
1201
1202 TYPE CUSTOMER_SITE_USE_ID_LIST_TYPE IS TABLE OF
1203 PN_PAYMENT_ITEMS.CUSTOMER_SITE_USE_ID%TYPE
1204 INDEX BY BINARY_INTEGER;
1205 NUM_COL2_ORIG_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
1206 NUM_COL2_NEW_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
1207
1208 TYPE CUST_SHIP_SITE_ID_LIST_TYPE IS TABLE OF
1209 PN_PAYMENT_ITEMS.CUST_SHIP_SITE_ID%TYPE
1210 INDEX BY BINARY_INTEGER;
1211 NUM_COL3_ORIG_LIST CUST_SHIP_SITE_ID_LIST_TYPE;
1212 NUM_COL3_NEW_LIST CUST_SHIP_SITE_ID_LIST_TYPE;
1213
1214 l_profile_val VARCHAR2(30);
1215 CURSOR merged_records IS
1216 SELECT DISTINCT m.CUSTOMER_MERGE_HEADER_ID
1217 ,yt.PAYMENT_ITEM_ID
1218 ,yt.CUSTOMER_ID
1219 ,yt.CUSTOMER_SITE_USE_ID
1220 ,yt.CUST_SHIP_SITE_ID
1221 FROM PN_PAYMENT_ITEMS yt
1222 ,RA_CUSTOMER_MERGES m
1223 WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID
1224 OR yt.CUSTOMER_SITE_USE_ID = m.DUPLICATE_SITE_ID
1225 OR yt.CUST_SHIP_SITE_ID = m.DUPLICATE_SITE_ID )
1226 AND m.process_flag = 'N'
1227 AND m.request_id = req_id
1228 AND m.set_number = set_num;
1229
1230 l_last_fetch BOOLEAN;
1231 l_count NUMBER;
1232
1233 BEGIN
1234 /* init variables */
1235 l_last_fetch := FALSE;
1236 l_count := 0;
1237
1238 IF process_mode='LOCK' THEN
1239 NULL;
1240 ELSE
1241 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1242 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','PN_PAYMENT_ITEMS',FALSE);
1243 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1244 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1245
1246 OPEN merged_records;
1247
1248 LOOP
1249
1250 FETCH merged_records BULK COLLECT INTO
1251 MERGE_HEADER_ID_LIST
1252 , PRIMARY_KEY_ID_LIST
1253 , NUM_COL1_ORIG_LIST
1254 , NUM_COL2_ORIG_LIST
1255 , NUM_COL3_ORIG_LIST
1256 LIMIT 1000;
1257
1258 IF merged_records%NOTFOUND THEN
1259 l_last_fetch := TRUE;
1260 END IF;
1261
1262 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1263 EXIT;
1264 END IF;
1265
1266 FOR I IN 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1267 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1268 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1269 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
1270 END LOOP;
1271
1272 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1273 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1274 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1275 MERGE_LOG_ID,
1276 TABLE_NAME,
1277 MERGE_HEADER_ID,
1278 PRIMARY_KEY_ID,
1279 NUM_COL1_ORIG,
1280 NUM_COL1_NEW,
1281 NUM_COL2_ORIG,
1282 NUM_COL2_NEW,
1283 NUM_COL3_ORIG,
1284 NUM_COL3_NEW,
1285 ACTION_FLAG,
1286 REQUEST_ID,
1287 CREATED_BY,
1288 CREATION_DATE,
1289 LAST_UPDATE_LOGIN,
1290 LAST_UPDATE_DATE,
1291 LAST_UPDATED_BY
1292 ) VALUES (
1293 HZ_CUSTOMER_MERGE_LOG_s.NEXTVAL,
1294 'PN_PAYMENT_ITEMS',
1295 MERGE_HEADER_ID_LIST(I),
1296 PRIMARY_KEY_ID_LIST(I),
1297 NUM_COL1_ORIG_LIST(I),
1298 NUM_COL1_NEW_LIST(I),
1299 NUM_COL2_ORIG_LIST(I),
1300 NUM_COL2_NEW_LIST(I),
1301 NUM_COL3_ORIG_LIST(I),
1302 NUM_COL3_NEW_LIST(I),
1303 'U',
1304 req_id,
1305 hz_utility_pub.CREATED_BY,
1306 hz_utility_pub.CREATION_DATE,
1307 hz_utility_pub.LAST_UPDATE_LOGIN,
1308 hz_utility_pub.LAST_UPDATE_DATE,
1309 hz_utility_pub.LAST_UPDATED_BY
1310 );
1311
1312 END IF;
1313
1314 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1315 UPDATE PN_PAYMENT_ITEMS yt SET
1316 CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
1317 , CUSTOMER_SITE_USE_ID=NUM_COL2_NEW_LIST(I)
1318 , CUST_SHIP_SITE_ID=NUM_COL3_NEW_LIST(I)
1319 , LAST_UPDATE_DATE=SYSDATE
1320 , last_updated_by=arp_standard.profile.user_id
1321 , last_update_login=arp_standard.profile.last_update_login
1322 WHERE PAYMENT_ITEM_ID=PRIMARY_KEY_ID_LIST(I);
1323
1324 l_count := l_count + MERGE_HEADER_ID_LIST.COUNT;
1325
1326 IF l_last_fetch THEN
1327 EXIT;
1328 END IF;
1329
1330 END LOOP;
1331
1332 arp_message.set_name('AR','AR_ROWS_UPDATED');
1333 arp_message.set_token('NUM_ROWS',to_char(l_count));
1334 END IF;
1335 EXCEPTION
1336 WHEN OTHERS THEN
1337 arp_message.set_line( 'update_payment_items');
1338 RAISE;
1339 END update_payment_items;
1340
1341 /*===========================================================================+
1342 | PROCEDURE
1343 | update_rec_agreements
1344 |
1345 | DESCRIPTION
1346 | Account merge procedure for the table, pn_rec_agreements
1347 | Column updated Corresponding HZ table.column
1348 | -------------------- -----------------------------
1349 | customer_id HZ_CUST_ACCOUNTS.cust_account_id
1350 | cust_site_id HZ_CUST_SITE_USES.site_use_id
1351 |
1352 | SCOPE - PRIVATE
1353 |
1354 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1355 |
1356 | ARGUMENTS : IN: req_id, set_num, process_mode
1357 |
1358 | NOTES :
1359 |
1360 | MODIFICATION HISTORY
1361 |
1362 | 29-apr-2004 Perl Script Created
1363 | 29-apr-2004 Kiran Finalised
1364 +===========================================================================*/
1365
1366 PROCEDURE update_rec_agreements (req_id NUMBER,
1367 set_num NUMBER,
1368 process_mode VARCHAR2) IS
1369
1370 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1371 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1372 INDEX BY BINARY_INTEGER;
1373 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1374
1375 TYPE REC_AGREEMENT_ID_LIST_TYPE IS TABLE OF
1376 PN_REC_AGREEMENTS.REC_AGREEMENT_ID%TYPE
1377 INDEX BY BINARY_INTEGER;
1378 PRIMARY_KEY_ID_LIST REC_AGREEMENT_ID_LIST_TYPE;
1379
1380 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
1381 PN_REC_AGREEMENTS.CUSTOMER_ID%TYPE
1382 INDEX BY BINARY_INTEGER;
1383 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
1384 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
1385
1386 TYPE CUST_SITE_ID_LIST_TYPE IS TABLE OF
1387 PN_REC_AGREEMENTS.CUST_SITE_ID%TYPE
1388 INDEX BY BINARY_INTEGER;
1389 NUM_COL2_ORIG_LIST CUST_SITE_ID_LIST_TYPE;
1390 NUM_COL2_NEW_LIST CUST_SITE_ID_LIST_TYPE;
1391
1392 l_profile_val VARCHAR2(30);
1393 CURSOR merged_records IS
1394 SELECT DISTINCT m.CUSTOMER_MERGE_HEADER_ID
1395 ,yt.REC_AGREEMENT_ID
1396 ,yt.CUSTOMER_ID
1397 ,yt.CUST_SITE_ID
1398 FROM PN_REC_AGREEMENTS yt
1399 ,RA_CUSTOMER_MERGES m
1400 WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID
1401 OR yt.CUST_SITE_ID = m.DUPLICATE_SITE_ID)
1402 AND m.process_flag = 'N'
1403 AND m.request_id = req_id
1404 AND m.set_number = set_num;
1405
1406 l_last_fetch BOOLEAN;
1407 l_count NUMBER;
1408 BEGIN
1409 /* init variables */
1410 l_last_fetch := FALSE;
1411 l_count := 0;
1412
1413 IF process_mode='LOCK' THEN
1414 NULL;
1415 ELSE
1416 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1417 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','PN_REC_AGREEMENTS',FALSE);
1418 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1419 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1420
1421 OPEN merged_records;
1422
1423 LOOP
1424 FETCH merged_records BULK COLLECT INTO
1425 MERGE_HEADER_ID_LIST
1426 , PRIMARY_KEY_ID_LIST
1427 , NUM_COL1_ORIG_LIST
1428 , NUM_COL2_ORIG_LIST
1429 LIMIT 1000;
1430
1431 IF merged_records%NOTFOUND THEN
1432 l_last_fetch := TRUE;
1433 END IF;
1434
1435 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1436 EXIT;
1437 END IF;
1438
1439 FOR I IN 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1440 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1441 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1442 END LOOP;
1443
1444 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1445 FORALL I IN 1..MERGE_HEADER_ID_LIST.COUNT
1446 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1447 MERGE_LOG_ID,
1448 TABLE_NAME,
1449 MERGE_HEADER_ID,
1450 PRIMARY_KEY_ID,
1451 NUM_COL1_ORIG,
1452 NUM_COL1_NEW,
1453 NUM_COL2_ORIG,
1454 NUM_COL2_NEW,
1455 ACTION_FLAG,
1456 REQUEST_ID,
1457 CREATED_BY,
1458 CREATION_DATE,
1459 LAST_UPDATE_LOGIN,
1460 LAST_UPDATE_DATE,
1461 LAST_UPDATED_BY
1462 ) VALUES (
1463 HZ_CUSTOMER_MERGE_LOG_s.NEXTVAL,
1464 'PN_REC_AGREEMENTS',
1465 MERGE_HEADER_ID_LIST(I),
1466 PRIMARY_KEY_ID_LIST(I),
1467 NUM_COL1_ORIG_LIST(I),
1468 NUM_COL1_NEW_LIST(I),
1469 NUM_COL2_ORIG_LIST(I),
1470 NUM_COL2_NEW_LIST(I),
1471 'U',
1472 req_id,
1473 hz_utility_pub.CREATED_BY,
1474 hz_utility_pub.CREATION_DATE,
1475 hz_utility_pub.LAST_UPDATE_LOGIN,
1476 hz_utility_pub.LAST_UPDATE_DATE,
1477 hz_utility_pub.LAST_UPDATED_BY
1478 );
1479
1480 END IF;
1481
1482 FORALL I IN 1..MERGE_HEADER_ID_LIST.COUNT
1483 UPDATE PN_REC_AGREEMENTS yt SET
1484 CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
1485 , CUST_SITE_ID=NUM_COL2_NEW_LIST(I)
1486 , LAST_UPDATE_DATE=SYSDATE
1487 , last_updated_by=arp_standard.profile.user_id
1488 , last_update_login=arp_standard.profile.last_update_login
1489 WHERE REC_AGREEMENT_ID=PRIMARY_KEY_ID_LIST(I);
1490
1491 l_count := l_count + MERGE_HEADER_ID_LIST.COUNT;
1492
1493 IF l_last_fetch THEN
1494 EXIT;
1495 END IF;
1496
1497 END LOOP;
1498
1499 arp_message.set_name('AR','AR_ROWS_UPDATED');
1500 arp_message.set_token('NUM_ROWS',to_char(l_count));
1501 END IF;
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 arp_message.set_line( 'update_rec_agreements');
1505 RAISE;
1506 END update_rec_agreements;
1507
1508 /*===========================================================================+
1509 | PROCEDURE
1510 | update_rec_arcl_dtln
1511 |
1512 | DESCRIPTION
1513 | Account merge procedure for the table, pn_rec_arcl_dtln
1514 | Column updated Corresponding HZ table.column
1515 | -------------------- -----------------------------
1516 | cust_account_id HZ_CUST_ACCOUNTS.cust_account_id
1517 |
1518 | SCOPE - PRIVATE
1519 |
1520 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1521 |
1522 | ARGUMENTS : IN: req_id, set_num, process_mode
1523 |
1524 | NOTES :
1525 |
1526 | MODIFICATION HISTORY
1527 |
1528 | 29-apr-2004 Perl Script Created
1529 | 29-apr-2004 Kiran Finalised
1530 +===========================================================================*/
1531
1532 PROCEDURE update_rec_arcl_dtln (req_id NUMBER,
1533 set_num NUMBER,
1534 process_mode VARCHAR2) IS
1535
1536 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1537 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1538 INDEX BY BINARY_INTEGER;
1539 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1540
1541 TYPE AREA_CLASS_DTL_ID_LIST_TYPE IS TABLE OF
1542 PN_REC_ARCL_DTLLN.AREA_CLASS_DTL_ID%TYPE
1543 INDEX BY BINARY_INTEGER;
1544 PRIMARY_KEY_ID_LIST AREA_CLASS_DTL_ID_LIST_TYPE;
1545
1546 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
1547 PN_REC_ARCL_DTLLN.CUST_ACCOUNT_ID%TYPE
1548 INDEX BY BINARY_INTEGER;
1549 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
1550 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
1551
1552 l_profile_val VARCHAR2(30);
1553
1554 CURSOR merged_records IS
1555 SELECT DISTINCT m.CUSTOMER_MERGE_HEADER_ID
1556 ,yt.AREA_CLASS_DTL_ID
1557 ,yt.CUST_ACCOUNT_ID
1558 FROM PN_REC_ARCL_DTLLN yt
1559 ,RA_CUSTOMER_MERGES m
1560 WHERE yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
1561 AND m.process_flag = 'N'
1562 AND m.request_id = req_id
1563 AND m.set_number = set_num;
1564
1565 l_last_fetch BOOLEAN;
1566 l_count NUMBER;
1567 BEGIN
1568 /* init variables */
1569 l_last_fetch := FALSE;
1570 l_count := 0;
1571
1572 IF process_mode='LOCK' THEN
1573 NULL;
1574 ELSE
1575 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1576 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','PN_REC_ARCL_DTLLN',FALSE);
1577 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1578 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1579
1580 OPEN merged_records;
1581
1582 LOOP
1583
1584 FETCH merged_records BULK COLLECT INTO
1585 MERGE_HEADER_ID_LIST
1586 , PRIMARY_KEY_ID_LIST
1587 , NUM_COL1_ORIG_LIST
1588 LIMIT 1000;
1589
1590 IF merged_records%NOTFOUND THEN
1591 l_last_fetch := TRUE;
1592 END IF;
1593
1594 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1595 EXIT;
1596 END IF;
1597
1598 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1599 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1600 END LOOP;
1601
1602 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1603 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1604 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1605 MERGE_LOG_ID,
1606 TABLE_NAME,
1607 MERGE_HEADER_ID,
1608 PRIMARY_KEY_ID,
1609 NUM_COL1_ORIG,
1610 NUM_COL1_NEW,
1611 ACTION_FLAG,
1612 REQUEST_ID,
1613 CREATED_BY,
1614 CREATION_DATE,
1615 LAST_UPDATE_LOGIN,
1616 LAST_UPDATE_DATE,
1617 LAST_UPDATED_BY
1618 ) VALUES (
1619 HZ_CUSTOMER_MERGE_LOG_s.nextval,
1620 'PN_REC_ARCL_DTLLN',
1621 MERGE_HEADER_ID_LIST(I),
1622 PRIMARY_KEY_ID_LIST(I),
1623 NUM_COL1_ORIG_LIST(I),
1624 NUM_COL1_NEW_LIST(I),
1625 'U',
1626 req_id,
1627 hz_utility_pub.CREATED_BY,
1628 hz_utility_pub.CREATION_DATE,
1629 hz_utility_pub.LAST_UPDATE_LOGIN,
1630 hz_utility_pub.LAST_UPDATE_DATE,
1631 hz_utility_pub.LAST_UPDATED_BY
1632 );
1633
1634 END IF;
1635
1636 FORALL I IN 1..MERGE_HEADER_ID_LIST.COUNT
1637 UPDATE PN_REC_ARCL_DTLLN yt SET
1638 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
1639 , LAST_UPDATE_DATE=SYSDATE
1640 , last_updated_by=arp_standard.profile.user_id
1641 , last_update_login=arp_standard.profile.last_update_login
1642 WHERE AREA_CLASS_DTL_ID=PRIMARY_KEY_ID_LIST(I);
1643
1644 l_count := l_count + MERGE_HEADER_ID_LIST.COUNT;
1645 IF l_last_fetch THEN
1646 EXIT;
1647 END IF;
1648
1649 END LOOP;
1650
1651 arp_message.set_name('AR','AR_ROWS_UPDATED');
1652 arp_message.set_token('NUM_ROWS',to_char(l_count));
1653 END IF;
1654 EXCEPTION
1655 WHEN OTHERS THEN
1656 arp_message.set_line( 'update_rec_arcl_dtln');
1657 RAISE;
1658 END update_rec_arcl_dtln;
1659
1660 /*===========================================================================+
1661 | PROCEDURE
1662 | update_rec_expcl_dtln
1663 |
1664 | DESCRIPTION
1665 | Account merge procedure for the table, pn_rec_expcl_dtln
1666 | Column updated Corresponding HZ table.column
1667 | -------------------- -----------------------------
1668 | cust_account_id HZ_CUST_ACCOUNTS.cust_account_id
1669 |
1670 | SCOPE - PRIVATE
1671 |
1672 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1673 |
1674 | ARGUMENTS : IN: req_id, set_num, process_mode
1675 |
1676 | NOTES :
1677 |
1678 | MODIFICATION HISTORY
1679 |
1680 | 29-apr-2004 Perl Script Created
1681 | 29-apr-2004 Kiran Finalised
1682 +===========================================================================*/
1683
1684 PROCEDURE update_rec_expcl_dtln (req_id NUMBER,
1685 set_num NUMBER,
1686 process_mode VARCHAR2) IS
1687
1688 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1689 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1690 INDEX BY BINARY_INTEGER;
1691 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1692
1693 TYPE EXP_CLS_LINE_ID_LIST_TYPE IS TABLE OF
1694 PN_REC_EXPCL_DTLLN.EXPENSE_CLASS_LINE_ID%TYPE
1695 INDEX BY BINARY_INTEGER;
1696 PRIMARY_KEY_ID_LIST EXP_CLS_LINE_ID_LIST_TYPE;
1697
1698 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
1699 PN_REC_EXPCL_DTLLN.CUST_ACCOUNT_ID%TYPE
1700 INDEX BY BINARY_INTEGER;
1701 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
1702 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
1703
1704 l_profile_val VARCHAR2(30);
1705 CURSOR merged_records IS
1706 SELECT DISTINCT m.CUSTOMER_MERGE_HEADER_ID
1707 ,yt.EXPENSE_CLASS_LINE_ID
1708 ,yt.CUST_ACCOUNT_ID
1709 FROM PN_REC_EXPCL_DTLLN yt
1710 ,RA_CUSTOMER_MERGES M
1711 WHERE yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
1712 AND m.process_flag = 'N'
1713 AND m.request_id = req_id
1714 AND m.set_number = set_num;
1715
1716 l_last_fetch BOOLEAN;
1717 l_count NUMBER;
1718 BEGIN
1719 /* init variables */
1720 l_last_fetch := FALSE;
1721 l_count := 0;
1722
1723 IF process_mode='LOCK' THEN
1724 NULL;
1725 ELSE
1726 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1727 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','PN_REC_EXPCL_DTLLN',FALSE);
1728 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1729 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1730
1731 OPEN merged_records;
1732
1733 LOOP
1734
1735 FETCH merged_records BULK COLLECT INTO
1736 MERGE_HEADER_ID_LIST
1737 , PRIMARY_KEY_ID_LIST
1738 , NUM_COL1_ORIG_LIST
1739 LIMIT 1000;
1740
1741 IF merged_records%NOTFOUND THEN
1742 l_last_fetch := TRUE;
1743 END IF;
1744
1745 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1746 EXIT;
1747 END IF;
1748
1749 FOR I IN 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1750 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1751 END LOOP;
1752
1753 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1754 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1755 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1756 MERGE_LOG_ID,
1757 TABLE_NAME,
1758 MERGE_HEADER_ID,
1759 PRIMARY_KEY_ID,
1760 NUM_COL1_ORIG,
1761 NUM_COL1_NEW,
1762 ACTION_FLAG,
1763 REQUEST_ID,
1764 CREATED_BY,
1765 CREATION_DATE,
1766 LAST_UPDATE_LOGIN,
1767 LAST_UPDATE_DATE,
1768 LAST_UPDATED_BY
1769 ) VALUES (
1770 HZ_CUSTOMER_MERGE_LOG_s.nextval,
1771 'PN_REC_EXPCL_DTLLN',
1772 MERGE_HEADER_ID_LIST(I),
1773 PRIMARY_KEY_ID_LIST(I),
1774 NUM_COL1_ORIG_LIST(I),
1775 NUM_COL1_NEW_LIST(I),
1776 'U',
1777 req_id,
1778 hz_utility_pub.CREATED_BY,
1779 hz_utility_pub.CREATION_DATE,
1780 hz_utility_pub.LAST_UPDATE_LOGIN,
1781 hz_utility_pub.LAST_UPDATE_DATE,
1782 hz_utility_pub.LAST_UPDATED_BY
1783 );
1784
1785 END IF;
1786
1787 FORALL I IN 1..MERGE_HEADER_ID_LIST.COUNT
1788 UPDATE PN_REC_EXPCL_DTLLN yt SET
1789 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
1790 , LAST_UPDATE_DATE=SYSDATE
1791 , last_updated_by=arp_standard.profile.user_id
1792 , last_update_login=arp_standard.profile.last_update_login
1793 WHERE EXPENSE_CLASS_LINE_ID=PRIMARY_KEY_ID_LIST(I);
1794
1795 l_count := l_count + MERGE_HEADER_ID_LIST.COUNT;
1796 IF l_last_fetch THEN
1797 EXIT;
1798 END IF;
1799 END LOOP;
1800
1801 arp_message.set_name('AR','AR_ROWS_UPDATED');
1802 arp_message.set_token('NUM_ROWS',to_char(l_count));
1803 END IF;
1804 EXCEPTION
1805 WHEN OTHERS THEN
1806 arp_message.set_line( 'update_rec_expcl_dtln');
1807 RAISE;
1808 END update_rec_expcl_dtln;
1809
1810 /*===========================================================================+
1811 | PROCEDURE
1812 | update_rec_period_lines
1813 |
1814 | DESCRIPTION
1815 | Account merge procedure for the table, pn_rec_period_lines
1816 | Column updated Corresponding HZ table.column
1817 | -------------------- -----------------------------
1818 | cust_account_id HZ_CUST_ACCOUNTS.cust_account_id
1819 |
1820 | SCOPE - PRIVATE
1821 |
1822 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1823 |
1824 | ARGUMENTS : IN: req_id, set_num, process_mode
1825 |
1826 | NOTES :
1827 |
1828 | MODIFICATION HISTORY
1829 |
1830 | 29-apr-2004 Perl Script Created
1831 | 29-apr-2004 Kiran Finalised
1832 +===========================================================================*/
1833
1834 PROCEDURE update_rec_period_lines (req_id NUMBER,
1835 set_num NUMBER,
1836 process_mode VARCHAR2) IS
1837
1838 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1839 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1840 INDEX BY BINARY_INTEGER;
1841 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1842
1843 TYPE REC_PERIOD_LINES_ID_LIST_TYPE IS TABLE OF
1844 PN_REC_PERIOD_LINES.REC_PERIOD_LINES_ID%TYPE
1845 INDEX BY BINARY_INTEGER;
1846 PRIMARY_KEY_ID_LIST REC_PERIOD_LINES_ID_LIST_TYPE;
1847
1848 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
1849 PN_REC_PERIOD_LINES.CUST_ACCOUNT_ID%TYPE
1850 INDEX BY BINARY_INTEGER;
1851 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
1852 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
1853
1854 l_profile_val VARCHAR2(30);
1855 CURSOR merged_records IS
1856 SELECT DISTINCT m.CUSTOMER_MERGE_HEADER_ID
1857 ,yt.REC_PERIOD_LINES_ID
1858 ,yt.CUST_ACCOUNT_ID
1859 FROM PN_REC_PERIOD_LINES yt
1860 ,RA_CUSTOMER_MERGES m
1861 WHERE yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
1862 AND m.process_flag = 'N'
1863 AND m.request_id = req_id
1864 AND m.set_number = set_num;
1865
1866 l_last_fetch BOOLEAN;
1867 l_count NUMBER;
1868 BEGIN
1869 /* init variables */
1870 l_last_fetch := FALSE;
1871 l_count := 0;
1872
1873 IF process_mode='LOCK' THEN
1874 NULL;
1875 ELSE
1876 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1877 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','PN_REC_PERIOD_LINES',FALSE);
1878 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1879 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1880
1881 OPEN merged_records;
1882
1883 LOOP
1884 FETCH merged_records BULK COLLECT INTO
1885 MERGE_HEADER_ID_LIST
1886 , PRIMARY_KEY_ID_LIST
1887 , NUM_COL1_ORIG_LIST
1888 LIMIT 1000;
1889
1890 IF merged_records%NOTFOUND THEN
1891 l_last_fetch := TRUE;
1892 END IF;
1893
1894 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1895 EXIT;
1896 END IF;
1897
1898 FOR I IN 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1899 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1900 END LOOP;
1901
1902 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1903 FORALL I IN 1..MERGE_HEADER_ID_LIST.COUNT
1904 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1905 MERGE_LOG_ID,
1906 TABLE_NAME,
1907 MERGE_HEADER_ID,
1908 PRIMARY_KEY_ID,
1909 NUM_COL1_ORIG,
1910 NUM_COL1_NEW,
1911 ACTION_FLAG,
1912 REQUEST_ID,
1913 CREATED_BY,
1914 CREATION_DATE,
1915 LAST_UPDATE_LOGIN,
1916 LAST_UPDATE_DATE,
1917 LAST_UPDATED_BY
1918 ) VALUES (
1919 HZ_CUSTOMER_MERGE_LOG_s.nextval,
1920 'PN_REC_PERIOD_LINES',
1921 MERGE_HEADER_ID_LIST(I),
1922 PRIMARY_KEY_ID_LIST(I),
1923 NUM_COL1_ORIG_LIST(I),
1924 NUM_COL1_NEW_LIST(I),
1925 'U',
1926 req_id,
1927 hz_utility_pub.CREATED_BY,
1928 hz_utility_pub.CREATION_DATE,
1929 hz_utility_pub.LAST_UPDATE_LOGIN,
1930 hz_utility_pub.LAST_UPDATE_DATE,
1931 hz_utility_pub.LAST_UPDATED_BY
1932 );
1933
1934 END IF;
1935
1936 FORALL I IN 1..MERGE_HEADER_ID_LIST.COUNT
1937 UPDATE PN_REC_PERIOD_LINES yt SET
1938 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
1939 , LAST_UPDATE_DATE=SYSDATE
1940 , last_updated_by=arp_standard.profile.user_id
1941 , last_update_login=arp_standard.profile.last_update_login
1942 WHERE REC_PERIOD_LINES_ID=PRIMARY_KEY_ID_LIST(I);
1943
1944 l_count := l_count + MERGE_HEADER_ID_LIST.COUNT;
1945 IF l_last_fetch THEN
1946 EXIT;
1947 END IF;
1948 END LOOP;
1949
1950 arp_message.set_name('AR','AR_ROWS_UPDATED');
1951 arp_message.set_token('NUM_ROWS',to_char(l_count));
1952 END IF;
1953 EXCEPTION
1954 WHEN OTHERS THEN
1955 arp_message.set_line( 'update_rec_period_lines');
1956 RAISE;
1957 END update_rec_period_lines;
1958
1959 /*===========================================================================+
1960 | PROCEDURE
1961 | update_space_assign_cust
1962 |
1963 | DESCRIPTION
1964 | Account merge procedure for the table, pn_space_assign_cust_all
1965 | Column updated Corresponding HZ table.column
1966 | -------------------- -----------------------------
1967 | cust_account_id HZ_CUST_ACCOUNTS.cust_account_id
1968 | site_use_id HZ_CUST_SITE_USES.site_use_id
1969 |
1970 | SCOPE - PRIVATE
1971 |
1972 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1973 |
1974 | ARGUMENTS : IN: req_id, set_num, process_mode
1975 |
1976 | NOTES :
1977 |
1978 | MODIFICATION HISTORY
1979 |
1980 | 18-feb-2003 Perl Script Created
1981 | 18-feb-2003 Kiran Hegde Finalised
1982 +===========================================================================*/
1983
1984 PROCEDURE update_space_assign_cust (
1985 req_id NUMBER,
1986 set_num NUMBER,
1987 process_mode VARCHAR2) IS
1988
1989 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1990 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1991 INDEX BY BINARY_INTEGER;
1992 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1993
1994 TYPE cust_space_assign_id_LIST_TYPE IS TABLE OF
1995 PN_SPACE_ASSIGN_CUST.cust_space_assign_id%TYPE
1996 INDEX BY BINARY_INTEGER;
1997 PRIMARY_KEY_ID_LIST cust_space_assign_id_LIST_TYPE;
1998
1999 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
2000 PN_SPACE_ASSIGN_CUST.CUST_ACCOUNT_ID%TYPE
2001 INDEX BY BINARY_INTEGER;
2002 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
2003 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
2004
2005 TYPE SITE_USE_ID_LIST_TYPE IS TABLE OF
2006 PN_SPACE_ASSIGN_CUST.SITE_USE_ID%TYPE
2007 INDEX BY BINARY_INTEGER;
2008 NUM_COL2_ORIG_LIST SITE_USE_ID_LIST_TYPE;
2009 NUM_COL2_NEW_LIST SITE_USE_ID_LIST_TYPE;
2010
2011 l_profile_val VARCHAR2(30);
2012 CURSOR merged_records IS
2013 SELECT DISTINCT
2014 m.CUSTOMER_MERGE_HEADER_ID
2015 ,yt.cust_space_assign_id
2016 ,yt.CUST_ACCOUNT_ID
2017 ,yt.SITE_USE_ID
2018 FROM PN_SPACE_ASSIGN_CUST yt
2019 ,RA_CUSTOMER_MERGES m
2020 WHERE ( yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
2021 OR yt.SITE_USE_ID = m.DUPLICATE_SITE_ID )
2022 AND m.process_flag = 'N'
2023 AND m.request_id = req_id
2024 AND m.set_number = set_num;
2025
2026 l_last_fetch BOOLEAN;
2027 l_count NUMBER;
2028
2029 BEGIN
2030 /* init variables */
2031 l_last_fetch := FALSE;
2032 l_count := 0;
2033
2034 IF process_mode='LOCK' THEN
2035 NULL;
2036 ELSE
2037 arp_message.set_name('AR','AR_UPDATING_TABLE');
2038 arp_message.set_token('TABLE_NAME','PN_SPACE_ASSIGN_CUST',FALSE);
2039 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
2040 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
2041
2042 OPEN merged_records;
2043
2044 LOOP
2045
2046 FETCH merged_records BULK COLLECT INTO
2047 MERGE_HEADER_ID_LIST
2048 ,PRIMARY_KEY_ID_LIST
2049 ,NUM_COL1_ORIG_LIST
2050 ,NUM_COL2_ORIG_LIST
2051 LIMIT 1000;
2052
2053 IF merged_records%NOTFOUND THEN
2054 l_last_fetch := TRUE;
2055 END IF;
2056
2057 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch THEN
2058 EXIT;
2059 END IF;
2060
2061 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
2062 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
2063 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
2064 END LOOP;
2065
2066 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2067 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
2068 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
2069 MERGE_LOG_ID,
2070 TABLE_NAME,
2071 MERGE_HEADER_ID,
2072 PRIMARY_KEY_ID,
2073 NUM_COL1_ORIG,
2074 NUM_COL1_NEW,
2075 NUM_COL2_ORIG,
2076 NUM_COL2_NEW,
2077 ACTION_FLAG,
2078 REQUEST_ID,
2079 CREATED_BY,
2080 CREATION_DATE,
2081 LAST_UPDATE_LOGIN,
2082 LAST_UPDATE_DATE,
2083 LAST_UPDATED_BY
2084 ) VALUES (
2085 HZ_CUSTOMER_MERGE_LOG_s.NEXTVAL,
2086 'PN_SPACE_ASSIGN_CUST',
2087 MERGE_HEADER_ID_LIST(I),
2088 PRIMARY_KEY_ID_LIST(I),
2089 NUM_COL1_ORIG_LIST(I),
2090 NUM_COL1_NEW_LIST(I),
2091 NUM_COL2_ORIG_LIST(I),
2092 NUM_COL2_NEW_LIST(I),
2093 'U',
2094 req_id,
2095 hz_utility_pub.CREATED_BY,
2096 hz_utility_pub.CREATION_DATE,
2097 hz_utility_pub.LAST_UPDATE_LOGIN,
2098 hz_utility_pub.LAST_UPDATE_DATE,
2099 hz_utility_pub.LAST_UPDATED_BY
2100 );
2101
2102 END IF;
2103
2104 FORALL I IN 1..MERGE_HEADER_ID_LIST.COUNT
2105 UPDATE PN_SPACE_ASSIGN_CUST yt SET
2106 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
2107 ,SITE_USE_ID=NUM_COL2_NEW_LIST(I)
2108 ,LAST_UPDATE_DATE=SYSDATE
2109 ,last_updated_by=arp_standard.profile.user_id
2110 ,last_update_login=arp_standard.profile.last_update_login
2111 WHERE cust_space_assign_id=PRIMARY_KEY_ID_LIST(I);
2112
2113 l_count := l_count + MERGE_HEADER_ID_LIST.COUNT;
2114
2115 IF l_last_fetch THEN
2116 EXIT;
2117 END IF;
2118 END LOOP;
2119
2120 arp_message.set_name('AR','AR_ROWS_UPDATED');
2121 arp_message.set_token('NUM_ROWS',to_char(l_count));
2122 END IF;
2123 EXCEPTION
2124 WHEN OTHERS THEN
2125 arp_message.set_line( 'update_space_assign_cust');
2126 RAISE;
2127 END update_space_assign_cust;
2128
2129 END PNP_CMERGE;