[Home] [Help]
PACKAGE BODY: APPS.JL_CMERGE
Source
1 PACKAGE BODY jl_cmerge AS
2 /* $Header: jlzzmrgb.pls 120.5 2005/07/14 00:02:13 rguerrer ship $ */
3
4 -----------------------Private Variables------------------------------------
5 g_count NUMBER :=0;
6 ----------------------------------------------------------------------------
7 ----------------------------------------------------------------------------
8 -- PROCEDURE --
9 -- jl_br_bnk_rtrn_upd --
10 -- --
11 -- DESCRIPTION --
12 -- Merge duplicate customer_id stored in column of table --
13 -- JL_BR_AR_BANK_RETURNS_ALL that refers to cust_account_id column of --
14 -- HZ_CUST_ACCOUNTS --
15 -- --
16 -- PURPOSE: --
17 -- Oracle Applications Rel 11.5 --
18 -- --
19 -- PARAMETERS: --
20 -- req_id --
21 -- set_num --
22 -- process_mode --
23 -- --
24 -- HISTORY: --
25 -- 06/08/01 Vidya Sidharthan Created --
26 ----------------------------------------------------------------------------
27 PROCEDURE jl_br_bank_rtrn_upd (
28 req_id NUMBER,
29 set_num NUMBER,
30 process_mode VARCHAR2) IS
31
32 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
33 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
34 INDEX BY BINARY_INTEGER;
35 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
36
37 TYPE RETURN_ID_LIST_TYPE IS TABLE OF
38 JL_BR_AR_BANK_RETURNS.RETURN_ID%TYPE
39 INDEX BY BINARY_INTEGER;
40 PRIMARY_KEY_ID_LIST RETURN_ID_LIST_TYPE;
41
42 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
43 JL_BR_AR_BANK_RETURNS.CUSTOMER_ID%TYPE
44 INDEX BY BINARY_INTEGER;
45 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
46 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
47
48
49 l_profile_val VARCHAR2(30);
50 CURSOR merged_records IS
51 SELECT distinct CUSTOMER_MERGE_HEADER_ID
52 ,RETURN_ID
53 ,yt.CUSTOMER_ID
54 FROM JL_BR_AR_BANK_RETURNS yt, ra_customer_merges m
55 WHERE (
56 yt.CUSTOMER_ID = m.DUPLICATE_ID
57 ) AND m.process_flag = 'N'
58 AND m.request_id = req_id
59 AND m.set_number = set_num;
60 l_last_fetch BOOLEAN := FALSE;
61 l_count NUMBER;
62 BEGIN
63 IF process_mode='LOCK' THEN
64 NULL;
65 ELSE
66 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
67 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','JL_BR_AR_BANK_RETURNS',FALSE);
68 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
69 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
70
71 open merged_records;
72 LOOP
73 FETCH merged_records BULK COLLECT INTO
74 MERGE_HEADER_ID_LIST
75 , PRIMARY_KEY_ID_LIST
76 , NUM_COL1_ORIG_LIST
77 LIMIT 1000;
78 IF merged_records%NOTFOUND THEN
79 l_last_fetch := TRUE;
80 END IF;
81 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
82 exit;
83 END IF;
84 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
85 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
86 END LOOP;
87 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
88 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
89 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
90 MERGE_LOG_ID,
91 TABLE_NAME,
92 MERGE_HEADER_ID,
93 PRIMARY_KEY_ID,
94 NUM_COL1_ORIG,
95 NUM_COL1_NEW,
96 ACTION_FLAG,
97 REQUEST_ID,
98 CREATED_BY,
99 CREATION_DATE,
100 LAST_UPDATE_LOGIN,
101 LAST_UPDATE_DATE,
102 LAST_UPDATED_BY
103 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
104 'JL_BR_AR_BANK_RETURNS',
105 MERGE_HEADER_ID_LIST(I),
106 PRIMARY_KEY_ID_LIST(I),
107 NUM_COL1_ORIG_LIST(I),
108 NUM_COL1_NEW_LIST(I),
109 'U',
110 req_id,
111 hz_utility_pub.CREATED_BY,
112 hz_utility_pub.CREATION_DATE,
113 hz_utility_pub.LAST_UPDATE_LOGIN,
114 hz_utility_pub.LAST_UPDATE_DATE,
115 hz_utility_pub.LAST_UPDATED_BY
116 );
117
118 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
119 UPDATE JL_BR_AR_BANK_RETURNS yt SET
120 CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
121 , LAST_UPDATE_DATE=SYSDATE
122 , last_updated_by=arp_standard.profile.user_id
123 , last_update_login=arp_standard.profile.last_update_login
124 WHERE RETURN_ID=PRIMARY_KEY_ID_LIST(I)
125 ;
126 l_count := l_count + SQL%ROWCOUNT;
127 IF l_last_fetch THEN
128 EXIT;
129 END IF;
130 END LOOP;
131
132 arp_message.set_name('AR','AR_ROWS_UPDATED');
133 arp_message.set_token('NUM_ROWS',to_char(l_count));
134 END IF;
135 EXCEPTION
136 WHEN OTHERS THEN
137 arp_message.set_line( 'jl_br_bank_rtrn_upd');
138 RAISE;
139 END jl_br_bank_rtrn_upd;
140
141
142
143 ----------------------------------------------------------------------------
144 -- PROCEDURE --
145 -- jl_br_occ_doc_upd --
146 -- --
147 -- DESCRIPTION --
148 -- Merge duplicate customer_id and site_use_id stored in column of --
149 -- JL_BR_AR_OCCURRENCE_DOCS_ALL that refers to site_use_id column of --
150 -- HZ_CUST_ACCT_SITES_ALL and cust_account_id of HZ_CUST_ACCOUNTS --
151 -- --
152 -- PURPOSE: --
153 -- Oracle Applications Rel 11.5 --
154 -- --
155 -- PARAMETERS: --
156 -- req_id --
157 -- set_num --
158 -- process_mode --
159 -- --
160 -- HISTORY: --
161 -- 06/08/01 Vidya Sidharthan Created --
162 ----------------------------------------------------------------------------
163 PROCEDURE jl_br_occ_doc_upd (
164 req_id NUMBER,
165 set_num NUMBER,
166 process_mode VARCHAR2) IS
167
168 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
169 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
170 INDEX BY BINARY_INTEGER;
171 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
172
173 TYPE OCCURRENCE_ID_LIST_TYPE IS TABLE OF
174 JL_BR_AR_OCCURRENCE_DOCS.OCCURRENCE_ID%TYPE
175 INDEX BY BINARY_INTEGER;
176 PRIMARY_KEY_ID_LIST OCCURRENCE_ID_LIST_TYPE;
177
178 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
179 JL_BR_AR_OCCURRENCE_DOCS.CUSTOMER_ID%TYPE
180 INDEX BY BINARY_INTEGER;
181 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
182 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
183
184 TYPE SITE_USE_ID_LIST_TYPE IS TABLE OF
185 JL_BR_AR_OCCURRENCE_DOCS.SITE_USE_ID%TYPE
186 INDEX BY BINARY_INTEGER;
187 NUM_COL2_ORIG_LIST SITE_USE_ID_LIST_TYPE;
188 NUM_COL2_NEW_LIST SITE_USE_ID_LIST_TYPE;
189
190 l_profile_val VARCHAR2(30);
191 CURSOR merged_records IS
192 SELECT distinct CUSTOMER_MERGE_HEADER_ID
193 ,OCCURRENCE_ID
194 ,yt.CUSTOMER_ID
195 ,yt.SITE_USE_ID
196 FROM JL_BR_AR_OCCURRENCE_DOCS yt, ra_customer_merges m
197 WHERE (
198 yt.CUSTOMER_ID = m.DUPLICATE_ID
199 OR yt.SITE_USE_ID = m.DUPLICATE_SITE_ID
200 ) AND m.process_flag = 'N'
201 AND m.request_id = req_id
202 AND m.set_number = set_num;
203 l_last_fetch BOOLEAN := FALSE;
204 l_count NUMBER;
205 BEGIN
206 IF process_mode='LOCK' THEN
207 NULL;
208 ELSE
209 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
210 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','JL_BR_AR_OCCURRENCE_DOCS',FALSE);
211 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
212 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
213
214 open merged_records;
215 LOOP
216 FETCH merged_records BULK COLLECT INTO
217 MERGE_HEADER_ID_LIST
218 , PRIMARY_KEY_ID_LIST
219 , NUM_COL1_ORIG_LIST
220 , NUM_COL2_ORIG_LIST
221 LIMIT 1000;
222 IF merged_records%NOTFOUND THEN
223 l_last_fetch := TRUE;
224 END IF;
225 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
226 exit;
227 END IF;
228 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
229 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
230 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
231 END LOOP;
232 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
233 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
234 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
235 MERGE_LOG_ID,
236 TABLE_NAME,
237 MERGE_HEADER_ID,
238 PRIMARY_KEY_ID,
239 NUM_COL1_ORIG,
240 NUM_COL1_NEW,
241 NUM_COL2_ORIG,
242 NUM_COL2_NEW,
243 ACTION_FLAG,
244 REQUEST_ID,
245 CREATED_BY,
246 CREATION_DATE,
247 LAST_UPDATE_LOGIN,
248 LAST_UPDATE_DATE,
249 LAST_UPDATED_BY
250 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
251 'JL_BR_AR_OCCURRENCE_DOCS',
252 MERGE_HEADER_ID_LIST(I),
253 PRIMARY_KEY_ID_LIST(I),
254 NUM_COL1_ORIG_LIST(I),
255 NUM_COL1_NEW_LIST(I),
256 NUM_COL2_ORIG_LIST(I),
257 NUM_COL2_NEW_LIST(I),
258 'U',
259 req_id,
260 hz_utility_pub.CREATED_BY,
261 hz_utility_pub.CREATION_DATE,
262 hz_utility_pub.LAST_UPDATE_LOGIN,
263 hz_utility_pub.LAST_UPDATE_DATE,
264 hz_utility_pub.LAST_UPDATED_BY
265 );
266 END IF;
267
268 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
269 UPDATE JL_BR_AR_OCCURRENCE_DOCS yt SET
270 CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
271 ,SITE_USE_ID=NUM_COL2_NEW_LIST(I)
272 , LAST_UPDATE_DATE=SYSDATE
273 , last_updated_by=arp_standard.profile.user_id
274 , last_update_login=arp_standard.profile.last_update_login
275 WHERE OCCURRENCE_ID=PRIMARY_KEY_ID_LIST(I)
276 ;
277 l_count := l_count + SQL%ROWCOUNT;
278 IF l_last_fetch THEN
279 EXIT;
280 END IF;
281 END LOOP;
282
283 arp_message.set_name('AR','AR_ROWS_UPDATED');
284 arp_message.set_token('NUM_ROWS',to_char(l_count));
285 END IF;
286 EXCEPTION
287 WHEN OTHERS THEN
288 arp_message.set_line( 'jl_br_occ_doc_upd');
289 RAISE;
290 END jl_br_occ_doc_upd;
291
292 ----------------------------------------------------------------------------
293 -- PROCEDURE --
294 -- --
295 -- jl_br_pay_sch_up
296 -- DESCRIPTION --
297 -- Merge duplicate site use id's stored in column of table --
298 -- JL_BR_AR_PAY_SCH_UPD that refers to Site_Use_id column of --
299 -- table HZ_CUST_ACCT_SITES_ALL --
300 -- --
301 -- PURPOSE: --
302 -- Oracle Applications Rel 11.5 --
303 -- --
304 -- PARAMETERS: --
305 -- req_id --
306 -- set_num --
307 -- process_mode --
308 -- --
309 -- HISTORY: --
310 -- 06/08/01 Vidya Sidharthan Created --
311 ----------------------------------------------------------------------------
312
313 PROCEDURE jl_br_pay_sch_upd (
314 req_id NUMBER,
315 set_num NUMBER,
316 process_mode VARCHAR2) IS
317
318 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
319 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
320 INDEX BY BINARY_INTEGER;
321 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
322
323 TYPE PAYMENT_SCHEDULE_ID_LIST_TYPE IS TABLE OF
324 JL_BR_AR_PAY_SCHED_AUX.PAYMENT_SCHEDULE_ID%TYPE
325 INDEX BY BINARY_INTEGER;
326 PRIMARY_KEY_ID_LIST PAYMENT_SCHEDULE_ID_LIST_TYPE;
327
328 TYPE CUSTOMER_SITE_USE_ID_LIST_TYPE IS TABLE OF
329 JL_BR_AR_PAY_SCHED_AUX.CUSTOMER_SITE_USE_ID%TYPE
330 INDEX BY BINARY_INTEGER;
331 NUM_COL1_ORIG_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
332 NUM_COL1_NEW_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
333
334 l_profile_val VARCHAR2(30);
335 CURSOR merged_records IS
336 SELECT distinct CUSTOMER_MERGE_HEADER_ID
337 ,PAYMENT_SCHEDULE_ID
338 ,yt.CUSTOMER_SITE_USE_ID
339 FROM JL_BR_AR_PAY_SCHED_AUX yt, ra_customer_merges m
340 WHERE (
341 yt.CUSTOMER_SITE_USE_ID = m.DUPLICATE_SITE_ID
342 ) AND m.process_flag = 'N'
343 AND m.request_id = req_id
344 AND m.set_number = set_num;
345 l_last_fetch BOOLEAN := FALSE;
346 l_count NUMBER;
347 BEGIN
348 IF process_mode='LOCK' THEN
349 NULL;
350 ELSE
351 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
352 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','JL_BR_AR_PAY_SCHED_AUX',FALSE);
353 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
354 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
355
356 open merged_records;
357 LOOP
358 FETCH merged_records BULK COLLECT INTO
359 MERGE_HEADER_ID_LIST
360 , PRIMARY_KEY_ID_LIST
361 , NUM_COL1_ORIG_LIST
362 LIMIT 1000;
363 IF merged_records%NOTFOUND THEN
364 l_last_fetch := TRUE;
365 END IF;
366 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
367 exit;
368 END IF;
369 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
370 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL1_ORIG_LIST(I));
371 END LOOP;
372 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
373 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
374 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
375 MERGE_LOG_ID,
376 TABLE_NAME,
377 MERGE_HEADER_ID,
378 PRIMARY_KEY_ID,
379 NUM_COL1_ORIG,
380 NUM_COL1_NEW,
381 ACTION_FLAG,
382 REQUEST_ID,
383 CREATED_BY,
384 CREATION_DATE,
385 LAST_UPDATE_LOGIN,
386 LAST_UPDATE_DATE,
387 LAST_UPDATED_BY
388 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
389 'JL_BR_AR_PAY_SCHED_AUX',
390 MERGE_HEADER_ID_LIST(I),
391 PRIMARY_KEY_ID_LIST(I),
392 NUM_COL1_ORIG_LIST(I),
393 NUM_COL1_NEW_LIST(I),
394 'U',
395 req_id,
396 hz_utility_pub.CREATED_BY,
397 hz_utility_pub.CREATION_DATE,
398 hz_utility_pub.LAST_UPDATE_LOGIN,
399 hz_utility_pub.LAST_UPDATE_DATE,
400 hz_utility_pub.LAST_UPDATED_BY
401 );
402
403 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
404 UPDATE JL_BR_AR_PAY_SCHED_AUX yt SET
405 CUSTOMER_SITE_USE_ID=NUM_COL1_NEW_LIST(I)
406 WHERE PAYMENT_SCHEDULE_ID=PRIMARY_KEY_ID_LIST(I)
407 ;
408 l_count := l_count + SQL%ROWCOUNT;
409 IF l_last_fetch THEN
410 EXIT;
411 END IF;
412 END LOOP;
413
414 arp_message.set_name('AR','AR_ROWS_UPDATED');
415 arp_message.set_token('NUM_ROWS',to_char(l_count));
416 END IF;
417 EXCEPTION
418 WHEN OTHERS THEN
419 arp_message.set_line( 'jl_br_pay_sch_upd');
420 RAISE;
421 END jl_br_pay_sch_upd;
422
423
424
425 ----------------------------------------------------------------------------
426 -- PROCEDURE --
427 -- jl_zz_tx_cus_cls_upd --
428 -- --
429 -- PURPOSE: --
430 -- Oracle Applications Rel 11.5 --
431 -- --
432 -- PARAMETERS: --
433 -- req_id --
434 -- set_num --
435 -- process_mode --
436 -- --
437 -- HISTORY: --
438 -- --
439 -- 06/09/01 Sudhir Sekuri Created --
440 -- 07/05/01 Sudhir Sekuri Replaced Update with Delete stmt. --
441 -- 08/23/02 Sudhir Sekuri Stubbed. Condition handled in form --
442 -- to filter merged customers. --
443 ----------------------------------------------------------------------------
444 PROCEDURE jl_zz_tx_cus_cls_upd (req_id NUMBER,
445 set_num NUMBER,
446 process_mode VARCHAR2)
447 IS
448
449 BEGIN
450 NULL;
451 END jl_zz_tx_cus_cls_upd;
452
453 ----------------------------------------------------------------------------
454 -- PROCEDURE --
455 -- jl_zz_tx_exc_cus_upd --
456 -- --
457 -- PURPOSE: --
458 -- Oracle Applications Rel 11.5 --
459 -- --
460 -- PARAMETERS: --
461 -- req_id --
462 -- set_num --
463 -- process_mode --
464 -- --
465 -- HISTORY: --
466 -- --
467 -- 06/09/01 Sudhir Sekuri Created --
468 -- 07/05/01 Sudhir Sekuri Replaced Update with Delete stmt. --
469 -- 08/23/02 Sudhir Sekuri Stubbed. Condition handled in form --
470 -- to filter merged customers. --
471 ----------------------------------------------------------------------------
472 PROCEDURE jl_zz_tx_exc_cus_upd (req_id NUMBER,
473 set_num NUMBER,
474 process_mode VARCHAR2)
475 IS
476
477 BEGIN
478 NULL;
479 END jl_zz_tx_exc_cus_upd;
480
481
482 ----------------------------------------------------------------------------
483 -- PROCEDURE --
484 -- jl_zz_tx_lgl_msg_upd --
485 -- --
486 -- PURPOSE: --
487 -- Oracle Applications Rel 11.5 --
488 -- --
489 -- PARAMETERS: --
490 -- req_id --
491 -- set_num --
492 -- process_mode --
493 -- --
494 -- HISTORY: --
495 -- --
496 -- 06/11/01 Sudhir Sekuri Created --
497 -- 07/05/01 Sudhir Sekuri Replaced Update with Delete stmt. --
498 -- 08/23/02 Sudhir Sekuri Stubbed. Condition handled in form --
499 -- to filter merged customers. --
500 ----------------------------------------------------------------------------
501 PROCEDURE jl_zz_tx_lgl_msg_upd (req_id NUMBER,
502 set_num NUMBER,
503 process_mode VARCHAR2)
504 IS
505
506 BEGIN
507 NULL;
508 END jl_zz_tx_lgl_msg_upd;
509
510
511 ----------------------------------------------------------------------------
512 -- PROCEDURE --
513 -- JL_BR_JOURNALS_UPD --
514 -- --
515 -- DESCRIPTION --
516 -- Merge duplicate customer_id stored in column of --
517 -- JL_BR_JOURNALS_ALL that refers to cust_account_id column of --
518 -- HZ_CUST_ACCOUNTS --
519 -- --
520 -- PURPOSE: --
521 -- Oracle Applications Rel 11.5 --
522 -- --
523 -- PARAMETERS: --
524 -- req_id --
525 -- set_num --
526 -- process_mode --
527 -- --
528 -- HISTORY: --
529 -- 06/26/01 Rafael Guerrero Created --
530 ----------------------------------------------------------------------------
531 PROCEDURE jl_br_journals_upd (req_id NUMBER,
532 set_num NUMBER,
533 process_mode VARCHAR2)
534 IS
535
536 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
537 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
538 INDEX BY BINARY_INTEGER;
539 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
540
541 TYPE application_id_LIST_TYPE IS TABLE OF
542 JL_BR_JOURNALS_ALL.application_id%TYPE
543 INDEX BY BINARY_INTEGER;
544 PRIMARY_KEY1_LIST application_id_LIST_TYPE;
545
546 TYPE set_of_books_id_LIST_TYPE IS TABLE OF
547 JL_BR_JOURNALS_ALL.set_of_books_id%TYPE
548 INDEX BY BINARY_INTEGER;
549 PRIMARY_KEY2_LIST set_of_books_id_LIST_TYPE;
550
551 TYPE code_combination_id_LIST_TYPE IS TABLE OF
552 JL_BR_JOURNALS_ALL.code_combination_id%TYPE
553 INDEX BY BINARY_INTEGER;
554 PRIMARY_KEY3_LIST code_combination_id_LIST_TYPE;
555
556 TYPE personnel_id_LIST_TYPE IS TABLE OF
557 JL_BR_JOURNALS_ALL.personnel_id%TYPE
558 INDEX BY BINARY_INTEGER;
559 PRIMARY_KEY4_LIST personnel_id_LIST_TYPE;
560 NUM_COL1_ORIG_LIST PERSONNEL_ID_LIST_TYPE;
561 NUM_COL1_NEW_LIST PERSONNEL_ID_LIST_TYPE;
562
563
564 TYPE accounting_date_LIST_TYPE IS TABLE OF
565 JL_BR_JOURNALS_ALL.accounting_date%TYPE
566 INDEX BY BINARY_INTEGER;
567 PRIMARY_KEY5_LIST accounting_date_LIST_TYPE;
568
569 TYPE trans_description_LIST_TYPE IS TABLE OF
570 JL_BR_JOURNALS_ALL.trans_description%TYPE
571 INDEX BY BINARY_INTEGER;
572 PRIMARY_KEY6_LIST trans_description_LIST_TYPE;
573
574
575 TYPE trans_id_LIST_TYPE IS TABLE OF
576 JL_BR_JOURNALS_ALL.trans_id%TYPE
577 INDEX BY BINARY_INTEGER;
578 PRIMARY_KEY7_LIST trans_id_LIST_TYPE;
579
580 TYPE installment_LIST_TYPE IS TABLE OF
581 JL_BR_JOURNALS_ALL.installment%TYPE
582 INDEX BY BINARY_INTEGER;
583 PRIMARY_KEY8_LIST installment_LIST_TYPE;
584
585 TYPE period_set_name_LIST_TYPE IS TABLE OF
586 JL_BR_JOURNALS_ALL.period_set_name%TYPE
587 INDEX BY BINARY_INTEGER;
588 PRIMARY_KEY9_LIST period_set_name_LIST_TYPE;
589
590
591 TYPE period_name_LIST_TYPE IS TABLE OF
592 JL_BR_JOURNALS_ALL.period_name%TYPE
593 INDEX BY BINARY_INTEGER;
594 PRIMARY_KEY10_LIST period_name_LIST_TYPE;
595
596 TYPE JOURNAL_BALANCE_FLAG_LIST_TYPE IS TABLE OF
597 JL_BR_JOURNALS_ALL.JOURNAL_BALANCE_FLAG%TYPE
598 INDEX BY BINARY_INTEGER;
599 VCHAR_COL1_ORIG_LIST JOURNAL_BALANCE_FLAG_LIST_TYPE;
600 VCHAR_COL1_NEW_LIST JOURNAL_BALANCE_FLAG_LIST_TYPE;
601
602 l_profile_val VARCHAR2(30);
603
604 CURSOR merged_records IS
605 SELECT distinct CUSTOMER_MERGE_HEADER_ID
606 ,application_id
607 ,set_of_books_id
608 ,code_combination_id
609 ,personnel_id
610 ,accounting_date
611 ,trans_description
612 ,trans_id
613 ,installment
614 ,period_set_name
615 ,period_name
616 ,personnel_id
617 ,journal_balance_flag
618 FROM JL_BR_JOURNALS_ALL yt, ra_customer_merges m
619 WHERE yt.PERSONNEL_ID = m.duplicate_id
620 AND m.process_flag = 'N'
621 AND m.request_id = req_id
622 AND m.set_number = set_num
623 AND yt.application_id=222;
624
625 -- replaced by cursor above
626 -- CURSOR c1 IS
627 -- SELECT SET_OF_BOOKS_ID
628 -- FROM jl_br_journals_all
629 -- WHERE (personnel_id) IN (SELECT unique m.duplicate_id
630 -- FROM ra_customer_merges m
631 -- WHERE m.process_flag = 'N'
632 -- AND m.request_id = req_id
633 -- AND m.set_number = set_num)
634 -- AND application_id=222
635 -- FOR UPDATE NOWAIT;
636
637
638 l_last_fetch BOOLEAN := FALSE;
639 l_count NUMBER;
640
641 BEGIN
642
643 arp_message.set_line ('JL_CMERGE.JL_BR_JOURNALS_UPD()+');
644
645 IF (process_mode = 'LOCK' ) THEN
646 arp_message.set_name ('AR', 'AR_LOCKING_TABLE');
647 arp_message.set_token ('TABLE_NAME','JL_BR_JOURNALS_ALL',FALSE);
648
649 -- open c1;
650 -- close c1;
651
652 open merged_records;
653 close merged_records;
654
655 ELSE
656
657
658 --customer level update--
659 /*
660 UPDATE jl_br_journals_all j
661 SET personnel_id = (SELECT distinct m.customer_id
662 FROM ra_customer_merges m
663 WHERE j.personnel_id = m.duplicate_id
664 AND m.process_flag = 'N'
665 AND m.request_id = req_id
666 AND m.set_number = set_num),
667 last_update_date = SYSDATE,
668 last_updated_by = arp_standard.profile.user_id,
669 last_update_login = arp_standard.profile.last_update_login,
670 JOURNAL_BALANCE_FLAG = 'N'
671 WHERE (personnel_id) IN (SELECT unique m.duplicate_id
672 FROM ra_customer_merges m
673 WHERE m.process_flag = 'N'
674 AND m.request_id = req_id
675 AND m.set_number = set_num)
676 AND application_id=222;
677 g_count := SQL%ROWCOUNT;
678 */
679
680 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
681 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','JL_BR_JOURNALS_ALL',FALSE);
682 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
683 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
684
685 l_count:=0;
686 open merged_records;
687
688 LOOP
689 FETCH merged_records BULK COLLECT INTO
690 MERGE_HEADER_ID_LIST
691 , PRIMARY_KEY1_LIST
692 , PRIMARY_KEY2_LIST
693 , PRIMARY_KEY3_LIST
694 , PRIMARY_KEY4_LIST
695 , PRIMARY_KEY5_LIST
696 , PRIMARY_KEY6_LIST
697 , PRIMARY_KEY7_LIST
698 , PRIMARY_KEY8_LIST
699 , PRIMARY_KEY9_LIST
700 , PRIMARY_KEY10_LIST
701 , NUM_COL1_ORIG_LIST
702 , VCHAR_COL1_ORIG_LIST
703 LIMIT 1000;
704
705 IF merged_records%NOTFOUND THEN
706 l_last_fetch := TRUE;
707 END IF;
708
709 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
710 exit;
711 END IF;
712
713 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
714
715 NUM_COL1_NEW_LIST(I) :=HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
716
717 -- Is defaulted to N because, the journals need to added to the balance of the customer
718 VCHAR_COL1_NEW_LIST(I) :='N';
719
720 END LOOP;
721
722 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
723 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
724 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
725 MERGE_LOG_ID,
726 TABLE_NAME,
727 MERGE_HEADER_ID,
728 PRIMARY_KEY_ID,
729 PRIMARY_KEY_ID1,
730 PRIMARY_KEY_ID2,
731 PRIMARY_KEY_ID3,
732 PRIMARY_KEY1,
733 PRIMARY_KEY2,
734 PRIMARY_KEY3,
735 PRIMARY_KEY4,
736 PRIMARY_KEY5,
737 PRIMARY_KEY6,
738 NUM_COL1_ORIG,
739 NUM_COL1_NEW,
740 VCHAR_COL1_ORIG,
741 VCHAR_COL1_NEW,
742 ACTION_FLAG,
743 REQUEST_ID,
744 CREATED_BY,
745 CREATION_DATE,
746 LAST_UPDATE_LOGIN,
747 LAST_UPDATE_DATE,
748 LAST_UPDATED_BY
749 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
750 'JL_BR_JOURNALS_ALL',
751 MERGE_HEADER_ID_LIST(I),
752 PRIMARY_KEY1_LIST(I),
753 PRIMARY_KEY2_LIST(I),
754 PRIMARY_KEY3_LIST(I),
755 PRIMARY_KEY4_LIST(I),
756 to_char( PRIMARY_KEY5_LIST(I)),
757 PRIMARY_KEY6_LIST(I),
758 PRIMARY_KEY7_LIST(I),
759 to_char( PRIMARY_KEY8_LIST(I)),
760 PRIMARY_KEY9_LIST(I),
761 PRIMARY_KEY10_LIST(I),
762 NUM_COL1_ORIG_LIST(I),
763 NUM_COL1_NEW_LIST(I),
764 VCHAR_COL1_ORIG_LIST(I),
765 VCHAR_COL1_NEW_LIST(I),
766 'U',
767 req_id,
768 hz_utility_pub.CREATED_BY,
769 hz_utility_pub.CREATION_DATE,
770 hz_utility_pub.LAST_UPDATE_LOGIN,
771 hz_utility_pub.LAST_UPDATE_DATE,
772 hz_utility_pub.LAST_UPDATED_BY
773 );
774
775 END IF;
776
777 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
778
779 UPDATE JL_BR_JOURNALS_ALL
780 SET
781 PERSONNEL_ID=NUM_COL1_NEW_LIST(I)
782 ,JOURNAL_BALANCE_FLAG=VCHAR_COL1_NEW_LIST(I)
783 ,last_update_date = SYSDATE
784 ,last_updated_by = arp_standard.profile.user_id
785 ,last_update_login = arp_standard.profile.last_update_login
786 WHERE personnel_id = NUM_COL1_ORIG_LIST(I)
787 AND application_id=222;
788
789 l_count := l_count + SQL%ROWCOUNT;
790 IF l_last_fetch THEN
791 EXIT;
792 END IF;
793 END LOOP;
794
795 arp_message.set_name ('AR','AR_ROWS_UPDATED');
796 arp_message.set_token ('NUM_ROWS', to_char(l_count) );
797
798 END IF ;
799 arp_message.set_line('JL_CMERGE.JL_BR_JOURNALS_UPD()-');
800
801 EXCEPTION
802 WHEN OTHERS THEN
803 arp_message.set_error ('JL_CMERGE.JL_BR_JOURNALS_UPD');
804 RAISE;
805 END jl_br_journals_upd;
806
807
808 ----------------------------------------------------------------------------
809 -- PROCEDURE --
810 -- JL_BR_BALANCES_UPD --
811 -- --
812 -- DESCRIPTION --
813 -- Merge duplicate customer_id stored in column of --
814 -- JL_BR_BALANCES_ALL that refers to cust_account_id column of --
815 -- HZ_CUST_ACCOUNTS --
816 -- --
817 -- PURPOSE: --
818 -- Oracle Applications Rel 11.5 --
819 -- --
820 -- PARAMETERS: --
821 -- req_id --
822 -- set_num --
823 -- process_mode --
824 -- --
825 -- HISTORY: --
826 -- 06/26/01 Rafael Guerrero Created --
827 ----------------------------------------------------------------------------
828
829 PROCEDURE jl_br_balances_upd (req_id NUMBER,
830 set_num NUMBER,
831 process_mode VARCHAR2)
832 IS
833
834 /*
835 CURSOR c1 IS
836 SELECT SET_OF_BOOKS_ID
837 FROM jl_br_balances_all
838 WHERE (personnel_id) IN (SELECT unique m.duplicate_id
839 FROM ra_customer_merges m
840 WHERE m.process_flag = 'N'
841 AND m.request_id = req_id
842 AND m.set_number = set_num)
843 AND application_id=222
844 FOR UPDATE NOWAIT;
845 */
846
847 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
848 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
849 INDEX BY BINARY_INTEGER;
850 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
851
852 TYPE application_id_LIST_TYPE IS TABLE OF
853 JL_BR_BALANCES_ALL.application_id%TYPE
854 INDEX BY BINARY_INTEGER;
855 PRIMARY_KEY1_LIST application_id_LIST_TYPE;
856
857 TYPE set_of_books_id_LIST_TYPE IS TABLE OF
858 JL_BR_BALANCES_ALL.set_of_books_id%TYPE
859 INDEX BY BINARY_INTEGER;
860 PRIMARY_KEY2_LIST set_of_books_id_LIST_TYPE;
861
862 TYPE period_set_name_LIST_TYPE IS TABLE OF
863 JL_BR_BALANCES_ALL.period_set_name%TYPE
864 INDEX BY BINARY_INTEGER;
865 PRIMARY_KEY3_LIST period_set_name_LIST_TYPE;
866
867 TYPE period_name_LIST_TYPE IS TABLE OF
868 JL_BR_BALANCES_ALL.period_name%TYPE
869 INDEX BY BINARY_INTEGER;
870 PRIMARY_KEY4_LIST period_name_LIST_TYPE;
871
872 TYPE code_combination_id_LIST_TYPE IS TABLE OF
873 JL_BR_BALANCES_ALL.code_combination_id%TYPE
874 INDEX BY BINARY_INTEGER;
875 PRIMARY_KEY5_LIST code_combination_id_LIST_TYPE;
876
877 TYPE personnel_id_LIST_TYPE IS TABLE OF
878 JL_BR_BALANCES_ALL.personnel_id%TYPE
879 INDEX BY BINARY_INTEGER;
880 PRIMARY_KEY6_LIST personnel_id_LIST_TYPE;
881
882 NUM_COL1_ORIG_LIST personnel_id_LIST_TYPE;
883 NUM_COL1_NEW_LIST personnel_id_LIST_TYPE;
884
885 TYPE ending_balance_LIST_TYPE IS TABLE OF
886 JL_BR_BALANCES_ALL.ending_balance%TYPE
887 INDEX BY BINARY_INTEGER;
888 NUM_COL2_ORIG_LIST ending_balance_LIST_TYPE;
889 NUM_COL2_NEW_LIST ending_balance_LIST_TYPE;
890
891 TYPE org_id_LIST_TYPE IS TABLE OF
892 JL_BR_BALANCES_ALL.org_id%TYPE
893 INDEX BY BINARY_INTEGER;
894 NUM_COL3_ORIG_LIST org_id_LIST_TYPE;
895 NUM_COL3_NEW_LIST org_id_LIST_TYPE;
896
897 TYPE ending_balance_sign_LIST_TYPE IS TABLE OF
898 JL_BR_BALANCES_ALL.ending_balance_sign%TYPE
899 INDEX BY BINARY_INTEGER;
900 NUM_COL4_ORIG_LIST ending_balance_sign_LIST_TYPE;
901 NUM_COL4_NEW_LIST ending_balance_sign_LIST_TYPE;
902
903 TYPE period_year_LIST_TYPE IS TABLE OF
904 JL_BR_BALANCES_ALL.period_year%TYPE
905 INDEX BY BINARY_INTEGER;
906 NUM_COL5_ORIG_LIST period_year_LIST_TYPE;
907 NUM_COL5_NEW_LIST period_year_LIST_TYPE;
908
909 TYPE period_num_LIST_TYPE IS TABLE OF
910 JL_BR_BALANCES_ALL.period_num%TYPE
911 INDEX BY BINARY_INTEGER;
912 NUM_COL6_ORIG_LIST period_num_LIST_TYPE;
913 NUM_COL6_NEW_LIST period_num_LIST_TYPE;
914
915 l_profile_val VARCHAR2(30);
916
917 CURSOR merged_records IS
918 SELECT distinct
919 m.CUSTOMER_MERGE_HEADER_ID
920 ,yt.application_id
921 ,yt.set_of_books_id
922 ,yt.period_set_name
923 ,yt.period_name
924 ,yt.code_combination_id
925 ,yt.personnel_id
926 ,yt.personnel_id
927 ,yt.ending_balance
928 ,yt.org_id
929 ,yt.ending_balance_sign
930 ,yt.period_year
931 ,yt.period_num
932 FROM JL_BR_BALANCES_ALL yt,
933 ra_customer_merges m
934 WHERE (yt.personnel_id = m.duplicate_id)
935 AND m.process_flag = 'N'
936 AND m.request_id = req_id
937 AND m.set_number = set_num
938 and yt.application_id =222;
939
940 l_last_fetch BOOLEAN := FALSE;
941 l_count NUMBER;
942
943 PROCEDURE BALANCES IS
944
945 /*------------------------------------------------------------*/
946 /*<<<<< Balance Building Routine >>>>>*/
947 /*------------------------------------------------------------*/
948
949 pl_period_num number;
950 pl_sob number;
951 pl_per varchar2(15);
952 pl_per_set varchar2(15);
953 pl_min_pyear number;
954 pl_max_pyear number;
955 pl_min_pnum number;
956 pl_max_pnum number;
957 pl_pyear number;
958 pl_pnum number;
959 pl_ccid number;
960 pl_sign varchar2(1);
961 pl_val number;
962 pl_user number;
963 pl_personnel_id number;
964
965 pl_curr_per_set varchar2(15);
966 pl_curr_sob number:=1;
967
968 x_org number;
969 x_profile_org number;
970
971 cursor c_bmb is
972 -- Summarize journals by period/account/customer
973 SELECT /*+ ORDERED */
974 jb.set_of_books_id sob,
975 jb.period_set_name perset,
976 gp.period_year pyear,
977 gp.period_num pnum,
978 jb.period_name per,
979 jb.code_combination_id ccid,
980 jb.personnel_id venid,
981 SUM(DECODE(jb.trans_value_sign,'D',-1*jb.trans_value,jb.trans_value)) bal,
982 jb.org_id org_id
983 FROM jl_br_journals jb,
984 gl_periods gp
985 WHERE application_id=222
986 AND journal_balance_flag='N'
987 AND jb.period_name = gp.period_name
988 AND jb.period_set_name = gp.period_set_name
989 AND jb.personnel_id = pl_personnel_id
990 GROUP BY jb.set_of_books_id,
991 jb.period_set_name,
992 gp.period_year,
993 gp.period_num,
994 jb.period_name,
995 jb.code_combination_id,
996 jb.personnel_id,
997 jb.org_id
998 ORDER BY jb.set_of_books_id, gp.period_year, gp.period_num;
999
1000 -- Retrieves all periods between max posted period and period being treated
1001 cursor c_per is
1002 select period_name pername,
1003 period_year peryear,
1004 period_num pernum
1005 from gl_periods
1006 where period_set_name = pl_per_set
1007 and (period_year = pl_max_pyear
1008 and period_num > pl_max_pnum)
1009 or (period_year > pl_max_pyear
1010 and period_year < pl_pyear)
1011 or (period_year = pl_pyear
1012 and period_num < pl_pnum)
1013 order by period_year, period_num;
1014
1015 r_per c_per%rowtype;
1016
1017 /* cursor c_org is
1018 Select unique org_id
1019 from jl_br_journals_all
1020 where application_id=222; */
1021 Cursor c_org is -- bug 3563804
1022 Select org_id
1023 from ar_system_parameters_all
1024 where global_attribute_category = 'JL.BR.ARXSYSPA.Additional Info';
1025
1026
1027 CURSOR c_customer IS
1028 SELECT unique m.customer_id
1029 FROM ra_customer_merges m
1030 WHERE m.process_flag = 'N'
1031 AND m.request_id = req_id
1032 AND m.set_number = set_num;
1033
1034 l_profile_val VARCHAR2(30);
1035
1036 BEGIN
1037 pl_curr_per_set:='<different>';
1038
1039 Open c_customer;
1040 Fetch c_customer into pl_personnel_id;
1041 close c_customer;
1042
1043
1044 x_profile_org:=fnd_profile.value ('ORG_ID');
1045
1046 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1047 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','JL_BR_BALANCES_ALL',FALSE);
1048 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1049
1050 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1051
1052 -- For each Brazilian organization, update the balances.
1053
1054 Open c_org;
1055
1056 Loop
1057 Fetch c_org into x_org;
1058 Exit when c_org%notfound;
1059
1060 Fnd_client_Info.set_org_context(x_org);
1061
1062 FOR r_bmb IN c_bmb LOOP
1063
1064 pl_user := FND_GLOBAL.user_id;
1065
1066 pl_sob := r_bmb.sob;
1067 pl_per_set := r_bmb.perset;
1068 pl_pyear := r_bmb.pyear;
1069 pl_pnum := r_bmb.pnum;
1070
1071
1072 ---------------------------------------
1073 -- Get Balance sign and Balance amount
1074 ---------------------------------------
1075
1076 pl_val :=ABS(r_bmb.bal);
1077 IF r_bmb.bal<0 THEN
1078 pl_sign:='D';
1079 ELSE
1080 pl_sign:='C';
1081 END IF;
1082
1083 /******************************************************************
1084 If per_set or sob are different, then query max pyear and pnum,
1085 else max pyear and pnum are equal to last value from cursor
1086 for pyear and pnum
1087 ******************************************************************/
1088
1089 if (pl_curr_per_set <> pl_per_set or pl_curr_sob <> pl_sob) then
1090
1091 ----------------------------------------------------------
1092 -- Get the maximum period_year existing in balances table
1093 -- this is the max year posted from AR to GL
1094 ----------------------------------------------------------
1095
1096 select nvl(max(period_year),0)
1097 into pl_max_pyear
1098 from jl_br_balances
1099 where application_id = 222
1100 and set_of_books_id = r_bmb.sob
1101 and period_set_name = r_bmb.perset;
1102
1103 ---------------------------------------------------------------
1104 -- Get the maximum period_number existing in balances table
1105 -- this is the max period posted from AR to GL in the max year
1106 -- this is also the last period posted
1107 ---------------------------------------------------------------
1108
1109 select nvl(max(period_num),0)
1110 into pl_max_pnum
1111 from jl_br_balances
1112 where application_id = 222
1113 and set_of_books_id = r_bmb.sob
1114 and period_set_name = r_bmb.perset
1115 and period_year = pl_max_pyear;
1116
1117
1118 ----------------------------------------------------------------------------
1119 -- Get the minimum period_year existing in balances table - this is the min
1120 -- year posted from AR to GL for the same account/customer
1121 ----------------------------------------------------------------------------
1122
1123 select nvl(min(period_year),0)
1124 into pl_min_pyear
1125 from jl_br_balances
1126 where application_id = 222
1127 and set_of_books_id = r_bmb.sob
1128 and period_set_name = r_bmb.perset
1129 and code_combination_id = r_bmb.ccid
1130 and personnel_id = r_bmb.venid;
1131
1132 -------------------------------------------------------------------------------
1133 -- Get the minimum period_number existing in balances table - this is the min
1134 -- period posted from AR to GL in the min year - this is also the first period
1135 -- posted for the same account/customer
1136 -------------------------------------------------------------------------------
1137
1138 SELECT NVL(MIN(period_num),0)
1139 INTO pl_min_pnum
1140 FROM jl_br_balances
1141 WHERE application_id = 222
1142 AND set_of_books_id = r_bmb.sob
1143 AND period_set_name = r_bmb.perset
1144 AND code_combination_id = r_bmb.ccid
1145 AND personnel_id = r_bmb.venid
1146 AND period_year = pl_min_pyear;
1147
1148
1149 -- Update pl_curr_per_set and pl_curr to do a query for pl_max_pyear, pl_max_pnum, pl_min_pyear and
1150 -- pl_min_num, just if per_set or sob changes
1151
1152 pl_curr_per_set := pl_per_set;
1153 pl_curr_sob := pl_sob;
1154
1155
1156 end if;
1157
1158 ---------------------------------------------------------------------------------
1159 -- If year being treated is greater than the max year posted in balances table
1160 -- or if year is the same but period being treated is greater than max period
1161 -- posted in balances table, then copy all balances of customer from last
1162 -- period posted in balances table to the new period being treated,
1163 -- later on this program this ending balance will be updated with amount being
1164 -- transferred
1165 ----------------------------------------------------------------------------------
1166
1167 IF r_bmb.pyear > pl_max_pyear OR
1168 (r_bmb.pyear = pl_max_pyear AND
1169 r_bmb.pnum > pl_max_pnum) THEN
1170
1171 BEGIN
1172
1173 INSERT INTO jl_br_balances
1174 (application_id,
1175 set_of_books_id,
1176 period_set_name,
1177 period_name,
1178 period_year,
1179 period_num,
1180 code_combination_id,
1181 personnel_id,
1182 ending_balance_sign,
1183 ending_balance,
1184 balance_error_flag,
1185 creation_date,
1186 created_by,
1187 last_update_date,
1188 last_updated_by,
1189 org_id)
1190
1191 SELECT
1192 222,
1193 r_bmb.sob,
1194 r_bmb.perset,
1195 r_bmb.per,
1196 r_bmb.pyear,
1197 r_bmb.pnum,
1198 code_combination_id,
1199 personnel_id,
1200 ending_balance_sign,
1201 ending_balance,
1202 '',
1203 sysdate,
1204 pl_user,
1205 sysdate,
1206 pl_user,
1207 r_bmb.org_id
1208 FROM jl_br_balances
1209 WHERE application_id = 222
1210 AND set_of_books_id = r_bmb.sob
1211 AND period_year = pl_max_pyear
1212 AND period_num = pl_max_pnum;
1213
1214 -----------------------------------------------------------------------------------
1215 -- When year being treated is equal to the max year already posted and period
1216 -- being treated is greater than the max period posted, then program inserts
1217 -- all these new periods between max period posted and period being treated so
1218 -- that balances table can store the right amounts for all periods
1219 -----------------------------------------------------------------------------------
1220
1221 IF r_bmb.pyear = pl_max_pyear THEN
1222
1223 INSERT INTO jl_br_balances
1224 (application_id,
1225 set_of_books_id,
1226 period_set_name,
1227 period_name,
1228 period_year,
1229 period_num,
1230 code_combination_id,
1231 personnel_id,
1232 ending_balance_sign,
1233 ending_balance,
1234 balance_error_flag,
1235 creation_date,
1236 last_update_date,
1237 last_updated_by,
1238 org_id)
1239 SELECT
1240 222,
1241 b.set_of_books_id,
1242 b.period_set_name,
1243 g.period_name,
1244 g.period_year,
1245 g.period_num,
1246 b.code_combination_id,
1247 b.personnel_id,
1248 b.ending_balance_sign,
1249 b.ending_balance,
1250 b.balance_error_flag,
1251 sysdate,
1252 sysdate,
1253 pl_user,
1254 b.org_id
1255 FROM jl_br_balances b,
1256 gl_periods g
1257 WHERE b.application_id = 222
1258 and b.period_set_name = g.period_set_name
1259 and b.period_year = g.period_year
1260 and b.period_year = pl_max_pyear
1261 and b.period_num = pl_max_pnum
1262 and g.period_num > pl_max_pnum
1263 and g.period_num < r_bmb.pnum;
1264
1265 ELSE
1266
1267 ---------------------------------------------------------------------------------
1268 -- Here program also creates new records in balances table for periods between
1269 -- max period already posted and new period being treated but for different
1270 -- years (when year being treated is greater than year already posted
1271 ---------------------------------------------------------------------------------
1272
1273 OPEN c_per;
1274 LOOP
1275 fetch c_per into r_per;
1276 exit when c_per%NOTFOUND;
1277
1278 BEGIN
1279
1280 INSERT INTO jl_br_balances
1281 (application_id,
1282 set_of_books_id,
1283 period_set_name,
1284 period_name,
1285 period_year,
1286 period_num,
1287 code_combination_id,
1288 personnel_id,
1289 ending_balance_sign,
1290 ending_balance,
1291 balance_error_flag,
1292 creation_date,
1293 last_update_date,
1294 last_updated_by,
1295 org_id)
1296 SELECT
1297 222,
1298 set_of_books_id,
1299 period_set_name,
1300 r_per.pername,
1301 r_per.peryear,
1302 r_per.pernum,
1303 code_combination_id,
1304 personnel_id,
1305 ending_balance_sign,
1306 ending_balance,
1307 balance_error_flag,
1308 sysdate,
1309 sysdate,
1310 pl_user,
1311 r_bmb.org_id
1312 FROM jl_br_balances
1313 WHERE application_id = 222
1314 and set_of_books_id = r_bmb.sob
1315 and period_set_name = r_bmb.perset
1316 and period_year = pl_max_pyear
1317 and period_num = pl_max_pnum;
1318
1319 EXCEPTION
1320 when dup_val_on_index then null;
1321 END;
1322
1323 END LOOP;
1324
1325 CLOSE c_per;
1326
1327 END IF;
1328
1329 EXCEPTION
1330 WHEN NO_DATA_FOUND THEN NULL; -- Will be treated as First Insert
1331 WHEN DUP_VAL_ON_INDEX THEN NULL;
1332
1333 END;
1334
1335 /**************************************************************
1336 After insert new lines to jl_br_balances to new periods,
1337 update pl_max_pyear and pl_max_pnum.
1338 ***************************************************************/
1339 pl_max_pyear := pl_pyear;
1340 pl_max_pnum := pl_pnum;
1341
1342 END IF;
1343
1344
1345 -------------------------------------------------------------------------------
1346 -- If year being treated is smaller than the min year posted in balances table
1347 -- or if year is the same but period being treated is smaller than min period
1348 -- posted in balances table, then create balance lines for next periods to
1349 -- the same account and the same customer till current period
1350 --------------------------------------------------------------------------------
1351
1352 IF r_bmb.pyear < pl_min_pyear OR
1353 (r_bmb.pyear = pl_min_pyear AND
1354 r_bmb.pnum < pl_min_pnum) THEN
1355
1356 BEGIN
1357 INSERT INTO jl_br_balances
1358 (application_id,
1359 set_of_books_id,
1360 period_set_name,
1361 period_name,
1362 period_year,
1363 period_num,
1364 code_combination_id,
1365 personnel_id,
1366 ending_balance_sign,
1367 ending_balance,
1368 creation_date,
1369 created_by,
1370 last_update_date,
1371 last_updated_by,
1372 org_id)
1373 SELECT
1374 222,
1375 r_bmb.sob,
1376 period_set_name,
1377 period_name,
1378 period_year,
1379 period_num,
1380 r_bmb.ccid,
1381 r_bmb.venid,
1382 'C',
1383 0,
1384 sysdate,
1385 pl_user,
1386 sysdate,
1387 pl_user,
1388 r_bmb.org_id
1389 FROM gl_periods
1390 WHERE period_set_name = r_bmb.perset
1391 AND (r_bmb.pyear = pl_min_pyear
1392 AND period_year = pl_min_pyear
1393 AND period_num >= r_bmb.pnum
1394 AND period_num < pl_min_pnum)
1395 OR (r_bmb.pyear < pl_min_pyear
1396 AND period_year = r_bmb.pyear
1397 AND period_num >= r_bmb.pnum)
1398 OR (r_bmb.pyear < pl_min_pyear
1399 AND period_year > r_bmb.pyear
1400 AND period_year < pl_min_pyear)
1401 OR (r_bmb.pyear < pl_min_pyear
1402 AND period_year = pl_min_pyear
1403 AND period_num < pl_min_pnum);
1404
1405 EXCEPTION
1406 WHEN DUP_VAL_ON_INDEX THEN NULL; -- Account/Customer has already been created
1407 WHEN NO_DATA_FOUND THEN NULL; -- No periods to treat
1408 END;
1409
1410 /**************************************************************
1411 After insert new lines to jl_br_balances to new periods,
1412 update pl_min_pyear and pl_min_pnum.
1413 ***************************************************************/
1414 pl_min_pyear := pl_pyear;
1415 pl_min_pnum := pl_pnum;
1416
1417 END IF;
1418
1419
1420 -------------------------------------------------------
1421 -- Update balances with trasactions amount transferred
1422 -- This is done for current and further on periods
1423 -------------------------------------------------------
1424
1425 UPDATE jl_br_balances
1426 SET ending_balance = ABS(ending_balance + r_bmb.bal),
1427 ending_balance_sign = decode(sign(ending_balance + r_bmb.bal),-1,'D','C'),
1428 balance_error_flag = '',
1429 last_update_date = sysdate,
1430 last_updated_by = pl_user,
1431 last_update_login = FND_GLOBAL.login_id
1432 WHERE application_id = 222
1433 and set_of_books_id = r_bmb.sob
1434 and period_set_name = r_bmb.perset
1435 and code_combination_id = r_bmb.ccid
1436 and personnel_id = r_bmb.venid
1437 and ((period_year = r_bmb.pyear
1438 and period_num >= r_bmb.pnum)
1439 or period_year > r_bmb.pyear);
1440
1441
1442 IF SQL%NOTFOUND THEN
1443 BEGIN
1444
1445 ---------------------------------------------------------------------------------
1446 -- First Insert of an account, customer or period.
1447 -- If it fails because of a duplication on index (balance record already exists
1448 -- for this account, customer or period), program will do nothing. because
1449 -- this situation was treated in previous update command, which treats the
1450 -- same account and the same customer not only for the same period but also
1451 -- for periods that come after the one being treated - this is the case for
1452 -- transactions being treated in previous periods
1453 ---------------------------------------------------------------------------------
1454
1455 INSERT INTO jl_br_balances
1456 (application_id,
1457 set_of_books_id,
1458 period_set_name,
1459 period_name,
1460 period_year,
1461 period_num,
1462 code_combination_id,
1463 personnel_id,
1464 ending_balance_sign,
1465 ending_balance,
1466 creation_date,
1467 last_update_date,
1468 last_updated_by,
1469 last_update_login,
1470 created_by,
1471 org_id)
1472 VALUES (
1473 222,
1474 r_bmb.sob,
1475 r_bmb.perset,
1476 r_bmb.per,
1477 r_bmb.pyear,
1478 r_bmb.pnum,
1479 r_bmb.ccid,
1480 r_bmb.venid,
1481 pl_sign,
1482 pl_val,
1483 sysdate,
1484 sysdate,
1485 pl_user,
1486 FND_GLOBAL.login_id,
1487 pl_user,
1488 r_bmb.org_id);
1489
1490 EXCEPTION
1491 WHEN DUP_VAL_ON_INDEX THEN NULL;
1492 END;
1493
1494 END IF;
1495
1496 -------------------------------------------------------
1497 -- Update journals, change journal_balance_flag to 'Y'
1498 -- meaning that those journals have been acummulated to
1499 -- balaces
1500 -------------------------------------------------------
1501
1502 UPDATE jl_br_journals
1503 SET journal_balance_flag = 'Y'
1504 WHERE application_id = 222
1505 AND set_of_books_id = r_bmb.sob
1506 AND code_combination_id = r_bmb.ccid
1507 AND personnel_id = r_bmb.venid
1508 AND period_set_name = r_bmb.perset
1509 AND period_name = r_bmb.per
1510 AND journal_balance_flag='N';
1511
1512 END LOOP;
1513
1514 END LOOP; -- Organization
1515
1516 CLOSE c_org;
1517
1518 fnd_client_Info.set_org_context(x_profile_org);
1519
1520 EXCEPTION
1521 WHEN OTHERS THEN
1522 Fnd_client_Info.set_org_context(x_profile_org);
1523
1524 END BALANCES;
1525
1526 BEGIN
1527
1528 arp_message.set_line ('JL_CMERGE.JL_BR_BALANCES_UPD()+');
1529
1530 IF (process_mode = 'LOCK' ) THEN
1531 arp_message.set_name ('AR', 'AR_LOCKING_TABLE');
1532 arp_message.set_token ('TABLE_NAME','JL_BR_BALANCES_ALL',FALSE);
1533
1534 open merged_records;
1535 close merged_records;
1536
1537 ELSE
1538 --customer level update--
1539
1540 /*
1541 delete jl_br_balances_all
1542 where (personnel_id) IN (SELECT unique m.duplicate_id
1543 FROM ra_customer_merges m
1544 WHERE m.process_flag = 'N'
1545 AND m.request_id = req_id
1546 AND m.set_number = set_num)
1547 AND application_id=222;
1548
1549 g_count := SQL%ROWCOUNT;
1550
1551 */
1552 -- Replaced with new logic that will generate log file.
1553
1554 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1555 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','JL_BR_BALANCES_ALL',FALSE);
1556 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1557 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1558
1559 l_count:=0;
1560
1561 open merged_records;
1562 LOOP
1563 FETCH merged_records BULK COLLECT INTO
1564 MERGE_HEADER_ID_LIST
1565 , PRIMARY_KEY1_LIST
1566 , PRIMARY_KEY2_LIST
1567 , PRIMARY_KEY3_LIST
1568 , PRIMARY_KEY4_LIST
1569 , PRIMARY_KEY5_LIST
1570 , PRIMARY_KEY6_LIST
1571 , NUM_COL1_ORIG_LIST
1572 , NUM_COL2_ORIG_LIST
1573 , NUM_COL3_ORIG_LIST
1574 , NUM_COL4_ORIG_LIST
1575 , NUM_COL5_ORIG_LIST
1576 , NUM_COL6_ORIG_LIST
1577 LIMIT 1000;
1578
1579 IF merged_records%NOTFOUND THEN
1580 l_last_fetch := TRUE;
1581 END IF;
1582 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1583 exit;
1584 END IF;
1585
1586 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1587 -- 5 stores the personnel id
1588 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1589
1590 END LOOP;
1591
1592
1593 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1594 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1595 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1596 MERGE_LOG_ID,
1597 TABLE_NAME,
1598 MERGE_HEADER_ID,
1599 PRIMARY_KEY1,
1600 PRIMARY_KEY2,
1601 PRIMARY_KEY3,
1602 PRIMARY_KEY4,
1603 PRIMARY_KEY5,
1604 PRIMARY_KEY6,
1605 DEL_COL1,
1606 DEL_COL2,
1607 DEL_COL3,
1608 DEL_COL4,
1609 DEL_COL5,
1610 DEL_COL6,
1611 DEL_COL7,
1612 DEL_COL8,
1613 DEL_COL9,
1614 DEL_COL10,
1615 DEL_COL11,
1616 ACTION_FLAG,
1617 REQUEST_ID,
1618 CREATED_BY,
1619 CREATION_DATE,
1620 LAST_UPDATE_LOGIN,
1621 LAST_UPDATE_DATE,
1622 LAST_UPDATED_BY
1623 ) VALUES (
1624 HZ_CUSTOMER_MERGE_LOG_s.nextval,
1625 'JL_BR_BALANCES_ALL',
1626 MERGE_HEADER_ID_LIST(I),
1627 PRIMARY_KEY1_LIST(I),
1628 PRIMARY_KEY2_LIST(I),
1629 PRIMARY_KEY3_LIST(I),
1630 PRIMARY_KEY4_LIST(I),
1631 PRIMARY_KEY5_LIST(I),
1632 PRIMARY_KEY6_LIST(I),
1633 PRIMARY_KEY1_LIST(I),
1634 PRIMARY_KEY2_LIST(I),
1635 PRIMARY_KEY3_LIST(I),
1636 PRIMARY_KEY4_LIST(I),
1637 PRIMARY_KEY5_LIST(I),
1638 PRIMARY_KEY6_LIST(I),
1639 NUM_COL2_ORIG_LIST(I),
1640 NUM_COL3_ORIG_LIST(I),
1641 NUM_COL4_ORIG_LIST(I),
1642 NUM_COL5_ORIG_LIST(I),
1643 NUM_COL6_ORIG_LIST(I),
1644 'D',
1645 req_id,
1646 hz_utility_pub.CREATED_BY,
1647 hz_utility_pub.CREATION_DATE,
1648 hz_utility_pub.LAST_UPDATE_LOGIN,
1649 hz_utility_pub.LAST_UPDATE_DATE,
1650 hz_utility_pub.LAST_UPDATED_BY
1651 );
1652
1653 END IF;
1654
1655 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1656 DELETE JL_BR_BALANCES_ALL
1657 WHERE application_id=PRIMARY_KEY1_LIST(I)
1658 AND set_of_books_id=PRIMARY_KEY2_LIST(I)
1659 AND period_set_name=PRIMARY_KEY3_LIST(I)
1660 AND period_name=PRIMARY_KEY4_LIST(I)
1661 AND code_combination_id=PRIMARY_KEY5_LIST(I)
1662 AND personnel_id=PRIMARY_KEY6_LIST(I);
1663
1664 l_count := l_count + SQL%ROWCOUNT;
1665 IF l_last_fetch THEN
1666 EXIT;
1667 END IF;
1668 END LOOP;
1669
1670 arp_message.set_name ('AR','AR_ROWS_UPDATED');
1671 arp_message.set_token ('NUM_ROWS', to_char(l_count) );
1672
1673 -- Call Procedure to Update Balances with the journals from the duplicated customer,
1674 -- now in the merged customer.
1675 Balances;
1676
1677 END IF ;
1678 arp_message.set_line('JL_CMERGE.JL_BR_BALANCES_UPD()-');
1679
1680 EXCEPTION
1681 WHEN OTHERS THEN
1682 arp_message.set_error ('JL_CMERGE.JL_BR_BALANCES_UPD');
1683 RAISE;
1684 END jl_br_balances_upd ;
1685
1686
1687
1688 ----------------------------------------------------------------------------
1689 -- PROCEDURE --
1690 -- merge --
1691 -- --
1692 -- DESCRIPTION --
1693 -- Public routine to make calls to update tables --
1694 -- --
1695 -- PURPOSE: --
1696 -- Oracle Applications Rel 11.5 --
1697 -- --
1698 -- PARAMETERS: --
1699 -- req_id --
1700 -- set_num --
1701 -- process_mode --
1702 -- --
1703 -- HISTORY: --
1704 -- 06/08/01 Vidya Sidharthan Created --
1705 ----------------------------------------------------------------------------
1706 PROCEDURE merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
1707 BEGIN
1708
1709 /************************************************************
1710 Validate the Application just for Brazil
1711 Check if the country installed is Brazil
1712 We could not use fnd_profile.value('JGZZ_COUNTRY_CODE')
1713 *************************************************************/
1714
1715 if fnd_profile.value('JGZZ_PRODUCT_CODE') <> 'JL' or fnd_profile.value('JGZZ_COUNTRY_CODE') <> 'BR' then
1716 return;
1717 end if;
1718
1719
1720 arp_message.set_line ('JL_CMERGE.MERGE()+');
1721 jl_br_bank_rtrn_upd (req_id, set_num, process_mode);
1722 jl_br_occ_doc_upd (req_id, set_num, process_mode);
1723 jl_br_pay_sch_upd (req_id,set_num,process_mode);
1724 jl_zz_tx_cus_cls_upd (req_id,set_num,process_mode);
1725 jl_zz_tx_exc_cus_upd (req_id,set_num,process_mode);
1726 jl_zz_tx_lgl_msg_upd (req_id,set_num,process_mode);
1727 jl_br_journals_upd (req_id,set_num,process_mode);
1728 jl_br_balances_upd (req_id,set_num,process_mode);
1729 arp_message.set_line ('JL_CMERGE.MERGE()-');
1730
1731 END merge;
1732 END jl_cmerge;