[Home] [Help]
PACKAGE BODY: APPS.CE_BANK_GROUPINGS
Source
1 PACKAGE BODY CE_BANK_GROUPINGS AS
2 /*$Header: cebugrpb.pls 120.3 2005/07/29 21:05:14 eliu noship $ */
3
4 TYPE Num15Tab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
5 TYPE Char1Tab IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
6 TYPE Char2Tab IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;
7 TYPE Char4Tab IS TABLE OF VARCHAR2(4) INDEX BY BINARY_INTEGER;
8 TYPE Char11Tab IS TABLE OF VARCHAR2(11) INDEX BY BINARY_INTEGER;
9 TYPE Char15Tab IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
10 TYPE Char20Tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
11 TYPE Char25Tab IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
12 TYPE Char30Tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
13 TYPE Char60Tab IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
14 TYPE Char80Tab IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
15 TYPE Char150Tab IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
16 TYPE Char240Tab IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
17 TYPE Char320Tab IS TABLE OF VARCHAR2(320) INDEX BY BINARY_INTEGER;
18 TYPE Char360Tab IS TABLE OF VARCHAR2(360) INDEX BY BINARY_INTEGER;
19
20 l_debug varchar2(1);
21
22 /*========================================================================+
23 | PUBLIC PROCEDURE |
24 | grouping |
25 | |
26 | DESCRIPTION |
27 | Main procedure of the bank grouping program. This program can group |
28 | bank data for BANK, BRANCH, or ACCOUNT level as requested. |
29 | |
30 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
31 | |
32 | ARGUMENTS |
33 | IN: |
34 | p_bank_entity_type Bank entity type for this program run. |
35 | p_display_debug Debug message flag (Y/N) |
36 | p_debug_path Debug path name if specified |
37 | p_debug_file Debug file name if specified |
38 +========================================================================*/
39 PROCEDURE grouping (errbuf OUT NOCOPY VARCHAR2,
40 retcode OUT NOCOPY NUMBER,
41 p_bank_entity_type VARCHAR2,
42 p_display_debug VARCHAR2,
43 p_debug_path VARCHAR2,
44 p_debug_file VARCHAR2) IS
45
46 -- Cursor to fetch bank records for grouping
47 cursor bank_cur is
48 select country, bank_or_branch_number, organization_name, ce_upgrade_id,
49 source_application_id, to_char(creation_date,'DD/MM/YYYY')
50 from ce_upg_bank_rec
51 where group_id is null
52 and bank_entity_type = p_bank_entity_type
53 order by country, bank_or_branch_number,
54 decode(source_application_id, 200, 1, 185, 2),
55 creation_date desc;
56
57 -- Cursor to fetch product specific attributes from secondary banks
58 cursor bank_sec is
59 select group_id, ce_upgrade_id, known_as
60 from ce_upg_bank_rec
61 where secondary_flag = 'Y'
62 and source_application_id <> 200
63 and bank_entity_type = p_bank_entity_type
64 order by group_id, source_application_id;
65
66 -- Cursor to fetch primary banks for validation
67 cursor bank_pri is
68 select ce_upgrade_id, country, bank_or_branch_number,
69 organization_name, jgzz_fiscal_code, organization_name_phonetic
70 from ce_upg_bank_rec
71 where bank_entity_type = p_bank_entity_type
72 and primary_flag = 'Y'
73 order by country, bank_or_branch_number, creation_date desc;
74
75 -- Cursor to fetch branch records for grouping
76 cursor branch_cur is
77 select bb.country, bb.bank_or_branch_number, bb.organization_name,
78 bb.ce_upgrade_id, bb.source_application_id,
79 to_char(bb.creation_date,'DD/MM/YYYY'), b.group_id
80 from ce_upg_bank_rec b, ce_upg_bank_rec bb
81 where b.ce_upgrade_id = bb.parent_upgrade_id
82 and bb.group_id is null
83 and b.bank_entity_type = 'BANK'
84 and bb.bank_entity_type = p_bank_entity_type
85 order by bb.country, b.group_id, bb.bank_or_branch_number,
86 decode(bb.source_application_id, 200, 1, 185, 2),
87 bb.creation_date desc;
88
89 -- Cursor to fetch product specific attributes from secondary branches
90 cursor branch_sec is
91 select group_id, ce_upgrade_id, known_as
92 from ce_upg_bank_rec
93 where secondary_flag = 'Y'
94 and source_application_id <> 200
95 and bank_entity_type = p_bank_entity_type
96 order by group_id, source_application_id;
97
98 -- Cursor to fetch primary branches for validation
99 cursor branch_pri is
100 select bb.ce_upgrade_id, bb.country, bb.bank_or_branch_number,
101 b.group_id, bb.organization_name_phonetic
102 from ce_upg_bank_rec b, ce_upg_bank_rec bb
103 where b.bank_entity_type = 'BANK'
104 and b.ce_upgrade_id = bb.parent_upgrade_id
105 and bb.bank_entity_type = p_bank_entity_type
106 and bb.primary_flag = 'Y'
107 order by b.group_id, bb.bank_or_branch_number, bb.creation_date desc;
108
109 -- Cursor to fetch account records for grouping
110 cursor acct_cur is
111 select bb.country, ba.bank_account_name, ba.bank_account_num,
112 ba.currency_code, ba.bank_account_type, ba.ce_upgrade_id,
113 ba.source_application_id, to_char(ba.creation_date,'DD/MM/YYYY'),
114 bb.group_id
115 from ce_upg_bank_rec bb, ce_upg_bank_accounts ba
116 where bb.ce_upgrade_id = ba.parent_upgrade_id
117 and ba.group_id is null
118 and bb.bank_entity_type = 'BRANCH'
119 order by bb.group_id, ba.bank_account_name, ba.bank_account_num,
120 ba.currency_code,
121 decode(bb.country, 'JP', ba.bank_account_type, 'X'),
122 decode(ba.source_application_id, 200, 1, 185, 2, 801, 3),
123 ba.creation_date desc;
124
125 -- Cursor to fetch product specific attributes from secondary accounts
126 cursor acct_sec is
127 select group_id, source_application_id, ce_upgrade_id,
128 start_date, legal_account_name, description, xtr_use_allowed_flag,
129 pay_use_allowed_flag, xtr_amount_tolerance, xtr_percent_tolerance,
130 pay_amount_tolerance, pay_percent_tolerance,
131 cashflow_display_order, target_balance
132 from ce_upg_bank_accounts
133 where secondary_acct_flag = 'Y'
134 and source_application_id <> 200
135 order by group_id, source_application_id;
136
137 -- Cursor to fetch primary accounts for validation
138 cursor acct_pri is
139 select ba.ce_upgrade_id, bb.country, b.bank_or_branch_number,
140 bb.bank_or_branch_number, ba.bank_account_num,
141 ba.secondary_account_reference, ba.bank_account_name,
142 ba.check_digits, bb.group_id, ba.bank_account_type,
143 ba.account_suffix
144 from ce_upg_bank_rec b, ce_upg_bank_rec bb, ce_upg_bank_accounts ba
145 where b.bank_entity_type = 'BANK'
146 and b.ce_upgrade_id = bb.parent_upgrade_id
147 and bb.bank_entity_type = 'BRANCH'
148 and bb.ce_upgrade_id = ba.parent_upgrade_id
149 and ba.primary_acct_flag = 'Y'
150 order by bb.group_id, ba.bank_account_name, ba.bank_account_num,
151 ba.creation_date desc;
152
153 -- Cursor to fetch account use records for grouping
154 cursor ba_use_cur is
155 select ba.group_id, bau.org_id, bau.legal_entity_id, bau.source_application_id,
156 bau.ce_upgrade_id, to_char(ba.creation_date,'DD/MM/YYYY'),
157 pay_use_enable_flag, payroll_bank_account_id
158 from ce_upg_bank_accounts ba, ce_upg_ba_uses_all bau
159 where ba.ce_upgrade_id = bau.parent_upgrade_id
160 and bau.group_id is null
161 order by ba.group_id, bau.org_id, bau.legal_entity_id,
162 decode(bau.source_application_id, 200, 1, 185, 2, 801, 3),
163 ba.creation_date desc;
164
165
166 c_country Char30Tab;
167 c_bank_or_branch_number Char60Tab;
168 c_org_name Char360Tab;
169 c_upg_id Num15Tab;
170 c_app_id Num15Tab;
171 c_creation_date Char11Tab;
172 c_group_id Num15Tab;
173 c_org_name_alt Char320Tab;
174 c_jgzz_fiscal_code Char20Tab;
175 c_status Char30Tab;
176 c_known_as Char240Tab;
177 c_start_date Char11Tab;
178 c_end_date Char11Tab;
179 c_acct_name Char80Tab;
180 c_acct_num Char30Tab;
181 c_currency Char15Tab;
182 c_acct_type Char25Tab;
183 c_legal_name Char30Tab;
184 c_bank_number Char60Tab;
185 c_description Char240Tab;
186 c_desc_code1 Char60Tab;
187 c_desc_code2 Char60Tab;
188 c_eft_requester Char25Tab;
189 c_acct_reference Char30Tab;
190 c_xtr_flag Char1Tab;
191 c_pay_flag Char1Tab;
192 c_xtr_amount Num15Tab;
193 c_xtr_percent Num15Tab;
194 c_pay_amount Num15Tab;
195 c_pay_percent Num15Tab;
196 c_acct_suffix Char30Tab;
197 c_comm_agreement Num15Tab;
198 c_cashflow_order Num15Tab;
199 c_target_balance Num15Tab;
200 c_cd Char30Tab;
201 c_org_id Num15Tab;
202 c_le_id Num15Tab;
203 c_pay_ba_id Num15Tab;
204
205 c VARCHAR2(3);
206 c_grouping_flag VARCHAR2(1);
207 c_commit_size NUMBER;
208 tot_count NUMBER;
209 curr_module VARCHAR2(150);
210
211 x_count NUMBER;
212 x_msgdata VARCHAR2(2000);
213 x_val_out VARCHAR2(2000);
214 x_msg_name VARCHAR2(2000);
215 p_msg_name VARCHAR2(2000);
216
217
218 BEGIN
219
220 -- Set debug flag and initial variable settings
221 c := '0';
222 l_debug := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
223 c_grouping_flag := 'N';
224 c_commit_size := 3000;
225 curr_module := 'CE_BAND_GROUPING';
226
227 c := '1';
228 if l_debug in ('Y', 'C') then
229 cep_standard.enable_debug(p_debug_path, p_debug_file);
230 cep_standard.debug('>>CE_BANK_GROUPINGS.grouping '||sysdate);
231 cep_standard.debug(' p_bank_entity_type = '|| p_bank_entity_type);
232 cep_standard.debug(' p_display_debug = '|| p_display_debug);
233 cep_standard.debug(' p_debug_path = '|| p_debug_path);
234 cep_standard.debug(' p_debug_file = '|| p_debug_file);
235 end if;
236
237 --
238 -- Check if the bank upgrade mode is ready for grouping
239 --
240 c := '2';
241 select 'Y'
242 into c_grouping_flag
243 from ce_bank_upgrade_modes
244 where source_product_name = 'ALL'
245 and decode(p_bank_entity_type, 'BRANCH', bank_upgrade_mode,
246 'ACCOUNT', branch_upgrade_mode,'GROUPED') in ('GROUPED','FROZEN')
247 and decode(p_bank_entity_type, 'BANK', bank_upgrade_mode,
248 'BRANCH', branch_upgrade_mode, account_upgrade_mode) = 'PRE_GROUP';
249
250 --
251 -- Start processing Grouping program
252 --
253 c := '4';
254 if (p_bank_entity_type = 'BANK') then
255 open bank_cur;
256 tot_count := 0;
257
258 c := '5';
259 loop
260 c_country.delete;
261 c_bank_or_branch_number.delete;
262 c_org_name.delete;
263 c_upg_id.delete;
264 c_app_id.delete;
265 c_creation_date.delete;
266
267 c := '6';
268 FETCH bank_cur BULK COLLECT INTO c_country, c_bank_or_branch_number,
269 c_org_name, c_upg_id, c_app_id, c_creation_date
270 LIMIT c_commit_size;
271
272 c := '7';
276 -- 1.1 Set primary record for records with bank number
273 if c_upg_id.count > 0 then
274
275 -- 1) Grouping bank records with bank number
277 c := '8';
278 forall i in c_upg_id.first..c_upg_id.last
279 update ce_upg_bank_rec b
280 set group_id = c_upg_id(i),
281 primary_flag = 'Y',
282 secondary_flag = 'N',
283 last_update_date = sysdate,
284 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
285 where bank_or_branch_number = c_bank_or_branch_number(i)
286 and bank_entity_type = p_bank_entity_type
287 and bank_or_branch_number is not null
288 and ce_upgrade_id = c_upg_id(i)
289 and not exists
290 (select null from ce_upg_bank_rec
291 where country = b.country
292 and bank_or_branch_number = b.bank_or_branch_number
293 and bank_entity_type = b.bank_entity_type
294 and primary_flag = 'Y'
295 union all
296 select null from ce_upg_bank_rec
297 where country = b.country
298 and bank_or_branch_number is null
299 and organization_name = b.organization_name
300 and bank_entity_type = b.bank_entity_type
301 and primary_flag = 'Y');
302
303 -- 1.2 Set secondary flag for records with bank number
304 c := '9';
305 forall i in c_upg_id.first..c_upg_id.last
306 update ce_upg_bank_rec b
307 set group_id = nvl(
308 (select group_id from ce_upg_bank_rec
309 where country = b.country
310 and bank_or_branch_number = b.bank_or_branch_number
311 and bank_entity_type = b.bank_entity_type
312 and source_application_id <> b.source_application_id
313 and primary_flag = 'Y'
314 and rownum = 1),
315 (select group_id from ce_upg_bank_rec
316 where country = b.country
317 and bank_or_branch_number is null
318 and organization_name = b.organization_name
319 and bank_entity_type = b.bank_entity_type
320 and source_application_id <> b.source_application_id
321 and primary_flag = 'Y'
322 and rownum = 1)),
323 primary_flag = 'N',
324 secondary_flag = 'Y',
325 last_update_date = sysdate,
326 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
327 where bank_or_branch_number = c_bank_or_branch_number(i)
328 and bank_or_branch_number is not null
329 and bank_entity_type = p_bank_entity_type
330 and ce_upgrade_id = c_upg_id(i)
331 and group_id is null
332 and not exists
333 (select null from ce_upg_bank_rec
334 where country = b.country
335 and bank_or_branch_number = b.bank_or_branch_number
336 and bank_entity_type = b.bank_entity_type
337 and source_application_id = b.source_application_id
338 and group_id is not null
339 union all
340 select null from ce_upg_bank_rec
341 where country = b.country
342 and bank_or_branch_number is null
343 and organization_name = b.organization_name
344 and bank_entity_type = b.bank_entity_type
345 and source_application_id = b.source_application_id
346 and group_id is not null)
347 and exists
348 (select null from ce_upg_bank_rec
349 where country = b.country
350 and bank_or_branch_number = b.bank_or_branch_number
351 and bank_entity_type = b.bank_entity_type
352 and primary_flag = 'Y'
353 union all
354 select null from ce_upg_bank_rec
355 where country = b.country
356 and bank_or_branch_number is null
357 and organization_name = b.organization_name
358 and bank_entity_type = b.bank_entity_type
359 and primary_flag = 'Y');
360
361 -- 1.3 Group all other records with bank number
362 c := '10';
363 forall i in c_upg_id.first..c_upg_id.last
364 update ce_upg_bank_rec b
365 set group_id = nvl(
366 (select group_id from ce_upg_bank_rec
367 where country = b.country
368 and bank_or_branch_number = b.bank_or_branch_number
369 and bank_entity_type = b.bank_entity_type
370 and primary_flag = 'Y'
371 and rownum = 1),
372 (select group_id from ce_upg_bank_rec
373 where country = b.country
374 and bank_or_branch_number is null
375 and organization_name = b.organization_name
376 and bank_entity_type = b.bank_entity_type
377 and primary_flag = 'Y'
378 and rownum = 1)),
379 primary_flag = 'N',
380 secondary_flag = 'N',
381 last_update_date = sysdate,
382 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
386 and ce_upgrade_id = c_upg_id(i)
383 where bank_or_branch_number = c_bank_or_branch_number(i)
384 and bank_or_branch_number is not null
385 and bank_entity_type = p_bank_entity_type
387 and group_id is null;
388
389 -- 2) Grouping bank reocrds with no bank number
390 -- 2.1 Set primary flag for records with no bank number
391 c := '11';
392 forall i in c_upg_id.first..c_upg_id.last
393 update ce_upg_bank_rec b
394 set group_id = c_upg_id(i),
395 primary_flag = 'Y',
396 secondary_flag = 'N',
397 last_update_date = sysdate,
398 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
399 where bank_or_branch_number is null
400 and bank_entity_type = p_bank_entity_type
401 and ce_upgrade_id = c_upg_id(i)
402 and not exists
403 (select null from ce_upg_bank_rec b2
404 where country = b.country
405 and organization_name = b.organization_name
406 and bank_entity_type = b.bank_entity_type
407 and primary_flag = 'Y');
408
409 -- 2.2 Set secondary flag for records with no bank number
410 c := '12';
411 forall i in c_upg_id.first..c_upg_id.last
412 update ce_upg_bank_rec b
413 set group_id =
414 (select group_id from ce_upg_bank_rec
415 where country = b.country
416 and organization_name = b.organization_name
417 and bank_entity_type = b.bank_entity_type
418 and source_application_id <> b.source_application_id
419 and primary_flag = 'Y'
420 and rownum = 1),
421 primary_flag = 'N',
422 secondary_flag = 'Y',
423 last_update_date = sysdate,
424 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
425 where bank_or_branch_number is null
426 and bank_entity_type = p_bank_entity_type
427 and ce_upgrade_id = c_upg_id(i)
428 and group_id is null
429 and organization_name = c_org_name(i)
430 --
431 and not exists
432 (select null from ce_upg_bank_rec b2
433 where country = b.country
434 and organization_name = b.organization_name
435 and bank_entity_type = b.bank_entity_type
436 and source_application_id = b.source_application_id
437 and group_id is not null)
438 --
439 and exists
440 (select null from ce_upg_bank_rec b2
441 where country = b.country
442 and organization_name = b.organization_name
443 and bank_entity_type = b.bank_entity_type
444 and primary_flag = 'Y');
445 /*
446 and not exists
447 (select null from ce_upg_bank_rec b3
448 where group_id = b2.group_id
449 and country = b.country
450 and organization_name = b.organization_name
451 and bank_entity_type = b.bank_entity_type
452 and source_application_id = b.source_application_id));
453 */
454 -- 2.3 Set all other records with no bank number
455 c := '13';
456 forall i in c_upg_id.first..c_upg_id.last
457 update ce_upg_bank_rec b
458 set group_id =
459 (select group_id from ce_upg_bank_rec
460 where country = b.country
461 and organization_name = b.organization_name
462 and bank_entity_type = b.bank_entity_type
463 and primary_flag = 'Y'
464 and rownum = 1),
465 primary_flag = 'N',
466 secondary_flag = 'N',
467 last_update_date = sysdate,
468 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
469 where bank_or_branch_number is null
470 and organization_name = c_org_name(i)
471 and bank_entity_type = p_bank_entity_type
472 and ce_upgrade_id = c_upg_id(i)
473 and group_id is null;
474
475 else
476 if l_debug in ('Y', 'C') then
477 cep_standard.debug('c_upg_id.count='||to_char(c_upg_id.count));
478 end if;
479 exit;
480 end if;
481 tot_count := sql%rowcount;
482
483 c := '14';
484 commit;
485 exit when bank_cur%notfound;
486 end loop;
487
488 c := '15';
489 close bank_cur;
490 if l_debug in ('Y', 'C') then
491 cep_standard.debug('Total bank count='||to_char(tot_count));
492 end if;
493
494 -- Populate secondary attributes to primary records
495 c := '16';
496 open bank_sec;
497 c_group_id.delete;
498 c_upg_id.delete;
499 c_known_as.delete;
500
501 c := '17';
502 FETCH bank_sec BULK COLLECT INTO c_group_id, c_upg_id, c_known_as;
503
504 c := '18';
505 if c_group_id.count > 0 then
506
507 c := '19';
508 forall i in c_group_id.first..c_group_id.last
509 update ce_upg_bank_rec
510 set known_as = nvl(c_known_as(i), known_as),
511 last_update_date = sysdate,
515 end if;
512 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
513 where ce_upgrade_id = c_group_id(i);
514
516
517 c := '20';
518 close bank_sec;
519
520 -- Validate primary records and populate validation errors if any
521 c := '21';
522 open bank_pri;
523 c_upg_id.delete;
524 c_country.delete;
525 c_bank_or_branch_number.delete;
526 c_org_name.delete;
527 c_jgzz_fiscal_code.delete;
528 c_org_name_alt.delete;
529 c_status.delete;
530
531 c := '22';
532 FETCH bank_pri BULK COLLECT INTO c_upg_id, c_country,
533 c_bank_or_branch_number, c_org_name, c_jgzz_fiscal_code,
534 c_org_name_alt;
535
536 -- Append bank number to bank name if two primary banks with different
537 -- bank number and same bank name under the same country.
538 -- (1) Copy bank records to ce_upga_bank_rec
539 c := '24';
540 forall i in c_upg_id.first..c_upg_id.last
541 insert into ce_upga_bank_rec
542 (party_id,
543 ce_upgrade_id,
544 parent_upgrade_id,
545 bank_entity_type,
546 upgrade_status,
547 primary_flag,
548 secondary_flag,
549 group_id,
550 bank_or_branch_number,
551 bank_code,
552 branch_code,
553 institution_type,
554 country,
555 branch_type,
556 rfc_code,
557 created_by_module,
558 organization_name,
559 organization_name_phonetic,
560 known_as,
561 jgzz_fiscal_code,
562 mission_statement,
563 attribute_category,
564 attribute1,
565 attribute2,
566 attribute3,
567 attribute4,
568 attribute5,
569 attribute6,
570 attribute7,
571 attribute8,
572 attribute9,
573 attribute10,
574 attribute11,
575 attribute12,
576 attribute13,
577 attribute14,
578 attribute15,
579 start_date_active,
580 end_date_active,
581 eft_user_num,
582 clearing_house_id,
583 creation_date,
584 created_by,
585 last_update_date,
586 last_updated_by,
587 last_update_login)
588 select
589 party_id,
590 ce_upgrade_id,
591 parent_upgrade_id,
592 bank_entity_type,
593 upgrade_status,
594 primary_flag,
595 secondary_flag,
596 group_id,
597 bank_or_branch_number,
598 bank_code,
599 branch_code,
600 institution_type,
601 country,
602 branch_type,
603 rfc_code,
604 curr_module,
605 organization_name,
606 organization_name_phonetic,
607 known_as,
608 jgzz_fiscal_code,
609 mission_statement,
610 attribute_category,
611 attribute1,
612 attribute2,
613 attribute3,
614 attribute4,
615 attribute5,
616 attribute6,
617 attribute7,
618 attribute8,
619 attribute9,
620 attribute10,
621 attribute11,
622 attribute12,
623 attribute13,
624 attribute14,
625 attribute15,
626 start_date_active,
627 end_date_active,
628 eft_user_num,
629 clearing_house_id,
630 creation_date,
631 created_by,
632 last_update_date,
633 last_updated_by,
634 last_update_login
635 from ce_upg_bank_rec b
636 where ce_upgrade_id = c_upg_id(i)
637 and exists
638 (select null from ce_upg_bank_rec b2
639 where b2.country = b.country
640 and b2.bank_or_branch_number <> b.bank_or_branch_number
641 and b2.organization_name = b.organization_name
642 and b2.bank_entity_type = b.bank_entity_type
643 and b2.primary_flag = 'Y'
644 and b2.creation_date >= b.creation_date);
645
646
647 -- (2) Update ce_upg_bank_rec to append bank number to bank name
648 c := '25';
649 forall i in c_upg_id.first..c_upg_id.last
650 update ce_upg_bank_rec b
651 set organization_name =
652 organization_name || ' ' || bank_or_branch_number,
653 last_update_date = sysdate,
654 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
655 where ce_upgrade_id = c_upg_id(i)
656 and exists
657 (select null from ce_upg_bank_rec b2
658 where b2.country = b.country
659 and b2.bank_or_branch_number <> b.bank_or_branch_number
660 and b2.organization_name = b.organization_name
661 and b2.bank_entity_type = b.bank_entity_type
662 and b2.primary_flag = 'Y'
663 and b2.creation_date >= b.creation_date);
664
665
666 c := '26';
667 for i in c_upg_id.first..c_upg_id.last
668 loop
669
670 fnd_msg_pub.initialize;
671 x_msg_name := NULL;
672
676 x_bank_number => c_bank_or_branch_number(i),
673 c := '27';
674 ce_validate_bankinfo_upg.ce_validate_bank
675 (x_country_name => c_country(i),
677 x_bank_name => c_org_name(i),
678 x_bank_name_alt => c_org_name_alt(i),
679 x_tax_payer_id => c_jgzz_fiscal_code(i),
680 x_validation_type => 'ALL',
681 p_init_msg_list => 'T',
682 x_msg_count => x_count,
683 x_msg_data => x_msgdata,
684 x_value_out => x_val_out,
685 x_message_name_all => x_msg_name);
686
687 if x_count > 0 then
688 c_status(i) := 'INVALID';
689 else
690 c_status(i) := 'NULL';
691 end if;
692 /*
693 begin
694 delete from ce_bank_upgrade_errors
695 where ce_upgrade_id = c_upg_id(i)
696 and bank_entity_type = 'BANK';
697 exception
698 when no_data_found then
699 null;
700 when too_many_rows then
701 null;
702 when others then
703 raise;
704 end;
705 */
706 c := '28';
707 FOR j IN 1..x_count LOOP
708
709 if (j=x_count) then
710 p_msg_name := x_msg_name;
711 x_msg_name := NULL;
712 else
713 p_msg_name := substr(x_msg_name, 1, instr(x_msg_name,',')-1);
714 x_msg_name := substr(x_msg_name, instr(x_msg_name,',')+1,
715 length(x_msg_name)-instr(x_msg_name,','));
716 end if;
717
718 if (p_msg_name is not null) then
719 insert into ce_bank_upgrade_errors
720 (ce_upgrade_id, bank_entity_type, key_error_flag, application_id,
721 message_name, creation_date, created_by, last_update_date,
722 last_updated_by)
723 values (c_upg_id(i), 'BANK', 'N', 260, p_msg_name,
724 sysdate, NVL(FND_GLOBAL.user_id,-1),
725 sysdate, NVL(FND_GLOBAL.user_id,-1));
726 end if;
727 END LOOP;
728 end loop;
729
730 -- Set upgrade status
731 c := '29';
732 forall i in c_upg_id.first..c_upg_id.last
733 update ce_upg_bank_rec
734 set upgrade_status = c_status(i),
735 last_update_date = sysdate,
736 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
737 where ce_upgrade_id = c_upg_id(i)
738 and c_status(i) = 'INVALID';
739
740 c := '30';
741 close bank_pri;
742
743 --
744 -- Update GROUPED status to ce_bank_upgrade_modes for 'BANK' level
745 --
746 c := '31';
747 begin
748 update ce_bank_upgrade_modes
749 set bank_upgrade_mode = 'GROUPED',
750 last_update_date = sysdate,
751 last_updated_by = nvl(FND_GLOBAL.user_id,-1);
752
753 exception
754 when too_many_rows then
755 null;
756 when others then
757 raise;
758 end;
759 c := '32';
760
761 elsif (p_bank_entity_type = 'BRANCH') then
762
763 c := '51';
764 open branch_cur;
765 tot_count := 0;
766
767 c := '52';
768 loop
769 c_country.delete;
770 c_bank_or_branch_number.delete;
771 c_org_name.delete;
772 c_upg_id.delete;
773 c_app_id.delete;
774 c_creation_date.delete;
775 c_group_id.delete;
776
777 c := '53';
778 FETCH branch_cur BULK COLLECT INTO c_country, c_bank_or_branch_number,
779 c_org_name, c_upg_id, c_app_id, c_creation_date, c_group_id
780 LIMIT c_commit_size;
781
782 c := '54';
783 if c_upg_id.count > 0 then
784
785 -- 3) Grouping branch records with branch number
786 -- 3.1 Set primary branch records with branch number
787 c := '55';
788 forall i in c_upg_id.first..c_upg_id.last
789 update ce_upg_bank_rec bb
790 set group_id = c_upg_id(i),
791 primary_flag = 'Y',
792 secondary_flag = 'N',
793 last_update_date = sysdate,
794 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
795 where ce_upgrade_id = c_upg_id(i)
796 and bank_or_branch_number is not null
797 and not exists
798 (select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
799 where b.ce_upgrade_id = bb2.parent_upgrade_id
800 and b.group_id = c_group_id(i)
801 and b.bank_entity_type = 'BANK'
802 and bb2.bank_or_branch_number = bb.bank_or_branch_number
803 and bb2.bank_entity_type = bb.bank_entity_type
804 and bb2.primary_flag = 'Y'
805 union all
806 select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
807 where b.ce_upgrade_id = bb2.parent_upgrade_id
808 and b.group_id = c_group_id(i)
809 and b.bank_entity_type = 'BANK'
810 and bb2.bank_or_branch_number is null
811 and bb2.organization_name = bb.organization_name
812 and bb2.bank_entity_type = bb.bank_entity_type
813 and bb2.primary_flag = 'Y');
814
815 -- 3.2 Set secondary flag for records with branch number
816 c := '56';
820 (select bb2.group_id
817 forall i in c_upg_id.first..c_upg_id.last
818 update ce_upg_bank_rec bb
819 set group_id = nvl(
821 from ce_upg_bank_rec b, ce_upg_bank_rec bb2
822 where b.group_id = c_group_id(i)
823 and b.country = bb.country
824 and b.bank_entity_type = 'BANK'
825 and bb2.parent_upgrade_id = b.ce_upgrade_id
826 and bb2.bank_or_branch_number = bb.bank_or_branch_number
827 and bb2.bank_entity_type = bb.bank_entity_type
828 and bb2.source_application_id <> bb.source_application_id
829 and bb2.primary_flag = 'Y'
830 and rownum = 1),
831 (select bb2.group_id
832 from ce_upg_bank_rec b, ce_upg_bank_rec bb2
833 where b.group_id = c_group_id(i)
834 and b.country = bb.country
835 and b.bank_entity_type = 'BANK'
836 and bb2.parent_upgrade_id = b.ce_upgrade_id
837 and bb2.bank_or_branch_number is null
838 and bb2.organization_name = bb.organization_name
839 and bb2.bank_entity_type = bb.bank_entity_type
840 and bb2.source_application_id <> bb.source_application_id
841 and bb2.primary_flag = 'Y'
842 and rownum = 1)),
843 primary_flag = 'N',
844 secondary_flag = 'Y',
845 last_update_date = sysdate,
846 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
847 where bank_or_branch_number = c_bank_or_branch_number(i)
848 and bank_or_branch_number is not null
849 and ce_upgrade_id = c_upg_id(i)
850 and bank_entity_type = p_bank_entity_type
851 and group_id is null
852 and not exists
853 (select null
854 from ce_upg_bank_rec b, ce_upg_bank_rec bb2
855 where b.country = bb.country
856 and b.bank_entity_type = 'BANK'
857 and b.group_id = c_group_id(i)
858 and b.ce_upgrade_id = bb2.parent_upgrade_id
859 and bb2.bank_or_branch_number = bb.bank_or_branch_number
860 and bb2.bank_entity_type = bb.bank_entity_type
861 and bb2.source_application_id = bb.source_application_id
862 and bb2.group_id is not null
863 union all
864 select null
865 from ce_upg_bank_rec b, ce_upg_bank_rec bb2
866 where b.country = bb.country
867 and b.bank_entity_type = 'BANK'
868 and b.group_id = c_group_id(i)
869 and b.ce_upgrade_id = bb2.parent_upgrade_id
870 and bb2.bank_or_branch_number is null
871 and bb2.organization_name = bb.organization_name
872 and bb2.bank_entity_type = bb.bank_entity_type
873 and bb2.source_application_id = bb.source_application_id
874 and bb2.group_id is not null)
875 and exists
876 (select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
877 where b.country = bb.country
878 and b.bank_entity_type = 'BANK'
879 and b.group_id = c_group_id(i)
880 and b.ce_upgrade_id = bb2.parent_upgrade_id
881 and bb2.bank_or_branch_number = bb.bank_or_branch_number
882 and bb2.bank_entity_type = bb.bank_entity_type
883 and bb2.primary_flag = 'Y'
884 union all
885 select null
886 from ce_upg_bank_rec b, ce_upg_bank_rec bb2
887 where b.country = bb.country
888 and b.bank_entity_type = 'BANK'
889 and b.group_id = c_group_id(i)
890 and b.ce_upgrade_id = bb2.parent_upgrade_id
891 and bb2.bank_or_branch_number is null
892 and bb2.organization_name = bb.organization_name
893 and bb2.bank_entity_type = bb.bank_entity_type
894 and bb2.primary_flag = 'Y');
895
896 -- 3.3 Group all other records with branch number
897 c := '57';
898 forall i in c_upg_id.first..c_upg_id.last
899 update ce_upg_bank_rec bb
900 set group_id = nvl(
901 (select bb2.group_id
902 from ce_upg_bank_rec b, ce_upg_bank_rec bb2
903 where b.country = bb.country
904 and b.group_id = c_group_id(i)
905 and b.bank_entity_type = 'BANK'
906 and b.ce_upgrade_id = bb2.parent_upgrade_id
907 and bb2.bank_or_branch_number = bb.bank_or_branch_number
908 and bb2.bank_entity_type = bb.bank_entity_type
909 and bb2.primary_flag = 'Y'
910 and rownum = 1),
911 (select bb2.group_id
912 from ce_upg_bank_rec b, ce_upg_bank_rec bb2
913 where b.country = bb.country
914 and b.group_id = c_group_id(i)
915 and b.bank_entity_type = 'BANK'
916 and b.ce_upgrade_id = bb2.parent_upgrade_id
917 and bb2.bank_or_branch_number is null
918 and bb2.organization_name = bb.organization_name
919 and bb2.bank_entity_type = bb.bank_entity_type
923 secondary_flag = 'N',
920 and bb2.primary_flag = 'Y'
921 and rownum = 1)),
922 primary_flag = 'N',
924 last_update_date = sysdate,
925 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
926 where bank_or_branch_number = c_bank_or_branch_number(i)
927 and bank_or_branch_number is not null
928 and ce_upgrade_id = c_upg_id(i)
929 and group_id is null;
930
931 -- 4) Grouping branch reocrds with no branch number
932 -- 4.1 Set primary flag for records with no branch number
933 c := '58';
934 forall i in c_upg_id.first..c_upg_id.last
935 update ce_upg_bank_rec bb
936 set group_id = c_upg_id(i),
937 primary_flag = 'Y',
938 secondary_flag = 'N',
939 last_update_date = sysdate,
940 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
941 where bank_or_branch_number is null
942 and ce_upgrade_id = c_upg_id(i)
943 and not exists
944 (select null
945 from ce_upg_bank_rec b, ce_upg_bank_rec bb2
946 where b.country = bb.country
947 and b.group_id = c_group_id(i)
948 and b.bank_entity_type = 'BANK'
949 and b.ce_upgrade_id = bb2.parent_upgrade_id
950 and bb2.organization_name = bb.organization_name
951 and bb2.bank_entity_type = bb.bank_entity_type
952 and bb2.primary_flag = 'Y');
953
954 -- 4.2 Set secondary flag for records with no branch number
955 c := '59';
956 forall i in c_upg_id.first..c_upg_id.last
957 update ce_upg_bank_rec bb
958 set group_id =
959 (select bb2.group_id
960 from ce_upg_bank_rec b, ce_upg_bank_rec bb2
961 where b.country = bb.country
962 and b.group_id = c_group_id(i)
963 and b.bank_entity_type = 'BANK'
964 and b.ce_upgrade_id = bb2.parent_upgrade_id
965 and bb2.organization_name = bb.organization_name
966 and bb2.source_application_id <> bb.source_application_id
967 and bb2.bank_entity_type = bb.bank_entity_type
968 and bb2.primary_flag = 'Y'
969 and rownum = 1),
970 primary_flag = 'N',
971 secondary_flag = 'Y',
972 last_update_date = sysdate,
973 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
974 where bank_or_branch_number is null
975 and ce_upgrade_id = c_upg_id(i)
976 and group_id is null
977 and organization_name = c_org_name(i)
978 and not exists
979 (select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
980 where b.country = bb.country
981 and b.group_id = c_group_id(i)
982 and b.bank_entity_type = 'BANK'
983 and b.ce_upgrade_id = bb2.parent_upgrade_id
984 and bb2.organization_name = bb.organization_name
985 and bb2.source_application_id = bb.source_application_id
986 and bb2.bank_entity_type = bb.bank_entity_type
987 and bb2.group_id is not null)
988 and exists
989 (select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
990 where b.country = bb.country
991 and b.group_id = c_group_id(i)
992 and b.bank_entity_type = 'BANK'
993 and b.ce_upgrade_id = bb2.parent_upgrade_id
994 and bb2.organization_name = bb.organization_name
995 and bb2.bank_entity_type = bb.bank_entity_type
996 and bb2.primary_flag = 'Y');
997
998 -- 4.3 Set all other records with no branch number
999 c := '60';
1000 forall i in c_upg_id.first..c_upg_id.last
1001 update ce_upg_bank_rec bb
1002 set group_id =
1003 (select bb2.group_id
1004 from ce_upg_bank_rec b, ce_upg_bank_rec bb2
1005 where b.country = bb.country
1006 and b.group_id = c_group_id(i)
1007 and b.bank_entity_type = 'BANK'
1008 and b.ce_upgrade_id = bb2.parent_upgrade_id
1009 and bb2.organization_name = bb.organization_name
1010 and bb2.bank_entity_type = bb.bank_entity_type
1011 and bb2.primary_flag = 'Y'
1012 and rownum = 1),
1013 primary_flag = 'N',
1014 secondary_flag = 'N',
1015 last_update_date = sysdate,
1016 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1017 where bank_or_branch_number is null
1018 and organization_name = c_org_name(i)
1019 and ce_upgrade_id = c_upg_id(i)
1020 and group_id is null;
1021
1022 else
1023 if l_debug in ('Y', 'C') then
1024 cep_standard.debug('c_upg_id.count='||to_char(c_upg_id.count));
1025 end if;
1026 exit;
1027 end if;
1028 tot_count := sql%rowcount;
1029
1030 c := '61';
1031 commit;
1032 exit when branch_cur%notfound;
1033 end loop;
1034
1035 c := '62';
1036 close branch_cur;
1040
1037 if l_debug in ('Y', 'C') then
1038 cep_standard.debug('Total branch count='||to_char(tot_count));
1039 end if;
1041 -- Populate secondary attributes to primary records
1042 c := '63';
1043 open branch_sec;
1044 c_group_id.delete;
1045 c_upg_id.delete;
1046 c_known_as.delete;
1047
1048 c := '64';
1049 FETCH branch_sec BULK COLLECT INTO c_group_id, c_upg_id, c_known_as;
1050
1051 c := '65';
1052 if c_group_id.count > 0 then
1053
1054 c := '66';
1055 forall i in c_group_id.first..c_group_id.last
1056 update ce_upg_bank_rec
1057 set known_as = nvl(c_known_as(i), known_as),
1058 last_update_date = sysdate,
1059 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1060 where ce_upgrade_id = c_group_id(i);
1061
1062 forall i in c_group_id.first..c_group_id.last
1063 insert into ce_upg_cont_point_rec
1064 (CE_UPGRADE_ID,
1065 BANK_ENTITY_TYPE,
1066 UPGRADE_STATUS,
1067 CONTACT_POINT_TYPE,
1068 PHONE_LINE_TYPE,
1069 OWNER_TABLE_NAME,
1070 OWNER_TABLE_ID,
1071 CREATED_BY_MODULE,
1072 PHONE_AREA_CODE,
1073 PHONE_NUMBER,
1074 ORG_PRIMARY_PHONE_FLAG,
1075 CREATION_DATE,
1076 CREATED_BY,
1077 LAST_UPDATE_DATE,
1078 LAST_UPDATED_BY)
1079 select
1080 c_group_id(i),
1081 BANK_ENTITY_TYPE,
1082 UPGRADE_STATUS,
1083 CONTACT_POINT_TYPE,
1084 PHONE_LINE_TYPE,
1085 OWNER_TABLE_NAME,
1086 OWNER_TABLE_ID,
1087 curr_module,
1088 PHONE_AREA_CODE,
1089 PHONE_NUMBER,
1090 ORG_PRIMARY_PHONE_FLAG,
1091 CREATION_DATE,
1092 CREATED_BY,
1093 LAST_UPDATE_DATE,
1094 LAST_UPDATED_BY
1095 from ce_upg_cont_point_rec p
1096 where ce_upgrade_id = c_upg_id(i)
1097 and CONTACT_POINT_TYPE = 'PHONE'
1098 and PHONE_LINE_TYPE in ('FAX', 'PAGER')
1099 and not exists
1100 (select null from ce_upg_cont_point_rec p2
1101 where p2.ce_upgrade_id = c_group_id(i)
1102 and bank_entity_type = p.BANK_ENTITY_TYPE
1103 and CONTACT_POINT_TYPE = p.CONTACT_POINT_TYPE
1104 and PHONE_LINE_TYPE = p.PHONE_LINE_TYPE);
1105
1106 forall i in c_group_id.first..c_group_id.last
1107 insert into ce_upg_cont_point_rec
1108 (CE_UPGRADE_ID,
1109 BANK_ENTITY_TYPE,
1110 UPGRADE_STATUS,
1111 CONTACT_POINT_TYPE,
1112 PHONE_LINE_TYPE,
1113 OWNER_TABLE_NAME,
1114 OWNER_TABLE_ID,
1115 CREATED_BY_MODULE,
1116 EMAIL_ADDRESS,
1117 CREATION_DATE,
1118 CREATED_BY,
1119 LAST_UPDATE_DATE,
1120 LAST_UPDATED_BY)
1121 select
1122 c_group_id(i),
1123 BANK_ENTITY_TYPE,
1124 UPGRADE_STATUS,
1125 CONTACT_POINT_TYPE,
1126 PHONE_LINE_TYPE,
1127 OWNER_TABLE_NAME,
1128 OWNER_TABLE_ID,
1129 curr_module,
1130 EMAIL_ADDRESS,
1131 CREATION_DATE,
1132 CREATED_BY,
1133 LAST_UPDATE_DATE,
1134 LAST_UPDATED_BY
1135 from ce_upg_cont_point_rec p
1136 where ce_upgrade_id = c_upg_id(i)
1137 and CONTACT_POINT_TYPE = 'EMAIL'
1138 and not exists
1139 (select null from ce_upg_cont_point_rec p2
1140 where p2.ce_upgrade_id = c_group_id(i)
1141 and bank_entity_type = p.BANK_ENTITY_TYPE
1142 and CONTACT_POINT_TYPE = p.CONTACT_POINT_TYPE);
1143
1144 forall i in c_group_id.first..c_group_id.last
1145 insert into ce_upg_loc_rec l
1146 (CE_UPGRADE_ID,
1147 BANK_ENTITY_TYPE,
1148 UPGRADE_STATUS,
1149 IDENTIFYING_ADDRESS_FLAG,
1150 COUNTRY,
1151 ADDRESS1,
1152 ADDRESS2,
1153 ADDRESS3,
1154 ADDRESS4,
1155 ADDRESS_STYLE,
1156 CITY,
1157 STATE,
1158 PROVINCE,
1159 COUNTY,
1160 POSTAL_CODE,
1161 ADDRESS_LINE_PHONETIC,
1162 CREATED_BY_MODULE,
1163 CREATION_DATE,
1164 CREATED_BY,
1165 LAST_UPDATE_DATE,
1166 LAST_UPDATED_BY)
1167 select
1168 c_group_id(i),
1169 BANK_ENTITY_TYPE,
1170 UPGRADE_STATUS,
1171 IDENTIFYING_ADDRESS_FLAG,
1172 COUNTRY,
1173 ADDRESS1,
1174 ADDRESS2,
1175 ADDRESS3,
1176 ADDRESS4,
1177 ADDRESS_STYLE,
1178 CITY,
1179 STATE,
1180 PROVINCE,
1181 COUNTY,
1182 POSTAL_CODE,
1183 ADDRESS_LINE_PHONETIC,
1184 curr_module,
1185 CREATION_DATE,
1186 CREATED_BY,
1187 LAST_UPDATE_DATE,
1188 LAST_UPDATED_BY
1189 from ce_upg_loc_rec l
1193 (select null from ce_upg_loc_rec
1190 where ce_upgrade_id = c_upg_id(i)
1191 and IDENTIFYING_ADDRESS_FLAG = 'N'
1192 and not exists
1194 where ce_upgrade_id = c_group_id(i)
1195 and bank_entity_type = l.BANK_ENTITY_TYPE
1196 and IDENTIFYING_ADDRESS_FLAG = l.IDENTIFYING_ADDRESS_FLAG);
1197
1198 end if;
1199
1200 c := '67';
1201 close branch_sec;
1202
1203 -- Validate primary records and populate validation errors if any
1204 c := '68';
1205 open branch_pri;
1206 c_upg_id.delete;
1207 c_country.delete;
1208 c_bank_or_branch_number.delete;
1209 c_group_id.delete;
1210 c_org_name_alt.delete;
1211 c_status.delete;
1212
1213 c := '69';
1214 FETCH branch_pri BULK COLLECT INTO c_upg_id, c_country,
1215 c_bank_or_branch_number, c_group_id, c_org_name_alt;
1216
1217 -- Append branch number to branch name if two primary branches with
1218 -- different branch number and same branch name under the same bank.
1219 -- (1) Copy branch records to ce_upga_bank_rec
1220 c := '71';
1221 forall i in c_upg_id.first..c_upg_id.last
1222 insert into ce_upga_bank_rec
1223 (party_id,
1224 ce_upgrade_id,
1225 parent_upgrade_id,
1226 bank_entity_type,
1227 upgrade_status,
1228 primary_flag,
1229 secondary_flag,
1230 group_id,
1231 bank_or_branch_number,
1232 bank_code,
1233 branch_code,
1234 institution_type,
1235 country,
1236 branch_type,
1237 rfc_code,
1238 created_by_module,
1239 organization_name,
1240 organization_name_phonetic,
1241 known_as,
1242 jgzz_fiscal_code,
1243 mission_statement,
1244 attribute_category,
1245 attribute1,
1246 attribute2,
1247 attribute3,
1248 attribute4,
1249 attribute5,
1250 attribute6,
1251 attribute7,
1252 attribute8,
1253 attribute9,
1254 attribute10,
1255 attribute11,
1256 attribute12,
1257 attribute13,
1258 attribute14,
1259 attribute15,
1260 start_date_active,
1261 end_date_active,
1262 eft_user_num,
1263 clearing_house_id,
1264 creation_date,
1265 created_by,
1266 last_update_date,
1267 last_updated_by,
1268 last_update_login)
1269 select
1270 party_id,
1271 ce_upgrade_id,
1272 parent_upgrade_id,
1273 bank_entity_type,
1274 upgrade_status,
1275 primary_flag,
1276 secondary_flag,
1277 group_id,
1278 bank_or_branch_number,
1279 bank_code,
1280 branch_code,
1281 institution_type,
1282 country,
1283 branch_type,
1284 rfc_code,
1285 curr_module,
1286 organization_name,
1287 organization_name_phonetic,
1288 known_as,
1289 jgzz_fiscal_code,
1290 mission_statement,
1291 attribute_category,
1292 attribute1,
1293 attribute2,
1294 attribute3,
1295 attribute4,
1296 attribute5,
1297 attribute6,
1298 attribute7,
1299 attribute8,
1300 attribute9,
1301 attribute10,
1302 attribute11,
1303 attribute12,
1304 attribute13,
1305 attribute14,
1306 attribute15,
1307 start_date_active,
1308 end_date_active,
1309 eft_user_num,
1310 clearing_house_id,
1311 creation_date,
1312 created_by,
1313 last_update_date,
1314 last_updated_by,
1315 last_update_login
1316 from ce_upg_bank_rec bb
1317 where ce_upgrade_id = c_upg_id(i)
1318 and exists
1319 (select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
1320 where b.ce_upgrade_id = bb2.parent_upgrade_id
1321 and b.bank_entity_type = 'BANK'
1322 and b.group_id = c_group_id(i)
1323 and bb2.bank_or_branch_number <> bb.bank_or_branch_number
1324 and bb2.organization_name = bb.organization_name
1325 and bb2.bank_entity_type = bb.bank_entity_type
1326 and bb2.primary_flag = 'Y'
1327 and bb2.creation_date >= bb.creation_date);
1328
1329
1330 -- (2) Update ce_upg_bank_rec to append branch number to branch name
1331 c := '72';
1332 forall i in c_upg_id.first..c_upg_id.last
1333 update ce_upg_bank_rec bb
1334 set organization_name =
1335 organization_name || ' ' || bank_or_branch_number,
1336 last_update_date = sysdate,
1337 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1338 where ce_upgrade_id = c_upg_id(i)
1339 and exists
1340 (select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
1341 where b.ce_upgrade_id = bb2.parent_upgrade_id
1342 and b.bank_entity_type = 'BANK'
1343 and b.group_id = c_group_id(i)
1347 and bb2.primary_flag = 'Y'
1344 and bb2.bank_or_branch_number <> bb.bank_or_branch_number
1345 and bb2.organization_name = bb.organization_name
1346 and bb2.bank_entity_type = bb.bank_entity_type
1348 and bb2.creation_date >= bb.creation_date);
1349
1350 c := '73';
1351 for i in c_upg_id.first..c_upg_id.last
1352 loop
1353
1354 fnd_msg_pub.initialize;
1355 x_msg_name := NULL;
1356
1357 c := '74';
1358 ce_validate_bankinfo_upg.ce_validate_branch
1359 (x_country_name => c_country(i),
1360 x_branch_number => c_bank_or_branch_number(i),
1361 x_branch_name_alt => c_org_name_alt(i),
1362 x_bank_id => c_group_id(i),
1363 x_validation_type => 'ALL',
1364 p_init_msg_list => 'T',
1365 x_msg_count => x_count,
1366 x_msg_data => x_msgdata,
1367 x_value_out => x_val_out,
1368 x_message_name_all => x_msg_name);
1369
1370 if x_count > 0 then
1371 c_status(i) := 'INVALID';
1372 else
1373 c_status(i) := 'NULL';
1374 end if;
1375 /*
1376 begin
1377 delete from ce_bank_upgrade_errors
1378 where ce_upgrade_id = c_upg_id(i)
1379 and bank_entity_type = 'BRANCH';
1380 exception
1381 when no_data_found then
1382 null;
1383 when too_many_rows then
1384 null;
1385 when others then
1386 raise;
1387 end;
1388 */
1389 c := '75';
1390 FOR j IN 1..x_count LOOP
1391
1392 if (j=x_count) then
1393 p_msg_name := x_msg_name;
1394 x_msg_name := NULL;
1395 else
1396 p_msg_name := substr(x_msg_name, 1, instr(x_msg_name,',')-1);
1397 x_msg_name := substr(x_msg_name, instr(x_msg_name,',')+1,
1398 length(x_msg_name)-instr(x_msg_name,','));
1399 end if;
1400
1401 c := '76';
1402 if (p_msg_name is not null) then
1403 insert into ce_bank_upgrade_errors
1404 (ce_upgrade_id, bank_entity_type, key_error_flag, application_id,
1405 message_name, creation_date, created_by, last_update_date,
1406 last_updated_by)
1407 values (c_upg_id(i), 'BRANCH', 'N', 260, p_msg_name,
1408 sysdate, NVL(FND_GLOBAL.user_id,-1),
1409 sysdate, NVL(FND_GLOBAL.user_id,-1));
1410 end if;
1411 END LOOP;
1412 end loop;
1413
1414 -- Set upgrade status
1415 c := '77';
1416 forall i in c_upg_id.first..c_upg_id.last
1417 update ce_upg_bank_rec
1418 set upgrade_status = c_status(i),
1419 last_update_date = sysdate,
1420 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1421 where ce_upgrade_id = c_upg_id(i)
1422 and c_status(i) = 'INVALID';
1423
1424 c := '78';
1425 close branch_pri;
1426
1427 --
1428 -- Update GROUPED status to ce_bank_upgrade_modes for 'BRANCH' level
1429 --
1430 c := '79';
1431 begin
1432 update ce_bank_upgrade_modes
1433 set branch_upgrade_mode = 'GROUPED',
1434 last_update_date = sysdate,
1435 last_updated_by = nvl(FND_GLOBAL.user_id,-1);
1436
1437 exception
1438 when too_many_rows then
1439 null;
1440 when others then
1441 raise;
1442 end;
1443 c := '80';
1444
1445 elsif (p_bank_entity_type = 'ACCOUNT') then
1446 c := '101';
1447 open acct_cur;
1448 tot_count := 0;
1449
1450 c := '102';
1451 loop
1452 c_country.delete;
1453 c_acct_name.delete;
1454 c_acct_num.delete;
1455 c_currency.delete;
1456 c_acct_type.delete;
1457 c_upg_id.delete;
1458 c_app_id.delete;
1459 c_creation_date.delete;
1460 c_group_id.delete;
1461
1462 c := '103';
1463 FETCH acct_cur BULK COLLECT INTO c_country, c_acct_name, c_acct_num,
1464 c_currency, c_acct_type, c_upg_id, c_app_id, c_creation_date,
1465 c_group_id
1466 LIMIT c_commit_size;
1467
1468 c := '104';
1469 if c_upg_id.count > 0 then
1470
1471 -- 5) Grouping account records
1472 -- 5.1 Set primary account records
1473 c := '105';
1474 forall i in c_upg_id.first..c_upg_id.last
1475 update ce_upg_bank_accounts ba
1476 set group_id = c_upg_id(i),
1477 primary_acct_flag = 'Y',
1478 secondary_acct_flag = 'N',
1479 last_update_date = sysdate,
1480 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1481 where ce_upgrade_id = c_upg_id(i)
1482 and not exists
1483 (select null from ce_upg_bank_rec bb, ce_upg_bank_accounts ba2
1484 where bb.ce_upgrade_id = ba2.parent_upgrade_id
1485 and bb.group_id = c_group_id(i)
1486 and bb.bank_entity_type = 'BRANCH'
1487 and ba2.bank_account_name = ba.bank_account_name
1488 and ba2.bank_account_num = ba.bank_account_num
1489 and ba2.currency_code = ba.currency_code
1490 and decode(bb.country, 'JP', ba2.bank_account_type, 'X') =
1494 -- 5.2 Set secondary flag for bank account records
1491 decode(bb.country, 'JP', ba.bank_account_type, 'X')
1492 and ba2.primary_acct_flag = 'Y');
1493
1495 c := '106';
1496 forall i in c_upg_id.first..c_upg_id.last
1497 update ce_upg_bank_accounts ba
1498 set group_id =
1499 (select ba2.group_id
1500 from ce_upg_bank_rec bb, ce_upg_bank_accounts ba2
1501 where bb.group_id = c_group_id(i)
1502 and bb.bank_entity_type = 'BANK'
1503 and ba2.parent_upgrade_id = bb.ce_upgrade_id
1504 and ba2.bank_account_name = ba.bank_account_name
1505 and ba2.bank_account_num = ba.bank_account_num
1506 and ba2.currency_code = ba.currency_code
1507 and decode(bb.country, 'JP', ba2.bank_account_type, 'X')
1508 = decode(bb.country, 'JP', ba.bank_account_type, 'X')
1509 and ba2.source_application_id <> ba.source_application_id
1510 and ba2.primary_acct_flag = 'Y'
1511 and rownum = 1),
1512 primary_acct_flag = 'N',
1513 secondary_acct_flag = 'Y',
1514 last_update_date = sysdate,
1515 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1516 where ce_upgrade_id = c_upg_id(i)
1517 and group_id is null
1518 and not exists
1519 (select null
1520 from ce_upg_bank_rec bb, ce_upg_bank_accounts ba2
1521 where bb.bank_entity_type = 'BRANCH'
1522 and bb.group_id = c_group_id(i)
1523 and bb.ce_upgrade_id = ba2.parent_upgrade_id
1524 and ba2.bank_account_name = ba.bank_account_name
1525 and ba2.bank_account_num = ba.bank_account_num
1526 and ba2.currency_code = ba.currency_code
1527 and decode(bb.country, 'JP', ba2.bank_account_type, 'X') =
1528 decode(bb.country, 'JP', ba.bank_account_type, 'X')
1529 and ba2.source_application_id = ba.source_application_id
1530 and ba2.group_id is not null)
1531 and exists
1532 (select null from ce_upg_bank_rec bb, ce_upg_bank_accounts ba2
1533 where bb.bank_entity_type = 'BRANCH'
1534 and bb.group_id = c_group_id(i)
1535 and bb.ce_upgrade_id = ba2.parent_upgrade_id
1536 and ba2.bank_account_name = ba.bank_account_name
1537 and ba2.bank_account_num = ba.bank_account_num
1538 and ba2.currency_code = ba.currency_code
1539 and decode(bb.country, 'JP', ba2.bank_account_type, 'X') =
1540 decode(bb.country, 'JP', ba.bank_account_type, 'X')
1541 and ba2.primary_acct_flag = 'Y');
1542
1543 -- 5.3 Group all other bank account records
1544 c := '107';
1545 forall i in c_upg_id.first..c_upg_id.last
1546 update ce_upg_bank_accounts ba
1547 set group_id =
1548 (select ba2.group_id
1549 from ce_upg_bank_rec bb, ce_upg_bank_accounts ba2
1550 where bb.group_id = c_group_id(i)
1551 and bb.bank_entity_type = 'BRANCH'
1552 and bb.ce_upgrade_id = ba2.parent_upgrade_id
1553 and ba2.bank_account_name = ba.bank_account_name
1554 and ba2.bank_account_num = ba.bank_account_num
1555 and ba2.currency_code = ba.currency_code
1556 and decode(bb.country, 'JP', ba2.bank_account_type, 'X')
1557 = decode(bb.country, 'JP', ba.bank_account_type, 'X')
1558 and ba2.primary_acct_flag = 'Y'
1559 and rownum = 1),
1560 primary_acct_flag = 'N',
1561 secondary_acct_flag = 'N',
1562 last_update_date = sysdate,
1563 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1564 where ce_upgrade_id = c_upg_id(i)
1565 and group_id is null;
1566
1567 c := '108';
1568
1569 else
1570 if l_debug in ('Y', 'C') then
1571 cep_standard.debug('c_upg_id.count='||to_char(c_upg_id.count));
1572 end if;
1573 exit;
1574 end if;
1575 tot_count := sql%rowcount;
1576
1577 c := '109';
1578 commit;
1579 exit when acct_cur%notfound;
1580 end loop;
1581
1582 c := '110';
1583 close acct_cur;
1584 if l_debug in ('Y', 'C') then
1585 cep_standard.debug('Total account count='||to_char(tot_count));
1586 end if;
1587
1588 -- Populate secondary attributes to primary records
1589 c := '111';
1590 open acct_sec;
1591 c_group_id.delete;
1592 c_app_id.delete;
1593 c_upg_id.delete;
1594 c_start_date.delete;
1595 c_legal_name.delete;
1596 c_description.delete;
1597 c_xtr_flag.delete;
1598 c_pay_flag.delete;
1599 c_xtr_amount.delete;
1600 c_xtr_percent.delete;
1601 c_pay_amount.delete;
1602 c_pay_percent.delete;
1603 c_cashflow_order.delete;
1604 c_target_balance.delete;
1605
1606 c := '112';
1607 FETCH acct_sec BULK COLLECT INTO c_group_id, c_app_id, c_upg_id,
1608 c_start_date, c_legal_name, c_description, c_xtr_flag,
1609 c_pay_flag, c_xtr_amount, c_xtr_percent, c_pay_amount, c_pay_percent,
1613 if c_group_id.count > 0 then
1610 c_cashflow_order, c_target_balance;
1611
1612 c := '113';
1614
1615 c := '114';
1616 forall i in c_group_id.first..c_group_id.last
1617 update ce_upg_bank_accounts
1618 set start_date = decode(c_app_id(i), 185, c_start_date(i)),
1619 legal_account_name = decode(c_app_id(i), 185, c_legal_name(i)),
1620 description = decode(source_application_id, 185, c_description(i)),
1621 xtr_use_allowed_flag = decode(c_app_id(i), 185,c_xtr_flag(i)),
1622 pay_use_allowed_flag = decode(c_app_id(i), 801,c_pay_flag(i)),
1623 xtr_amount_tolerance = decode(c_app_id(i), 185, c_xtr_amount(i)),
1624 xtr_percent_tolerance = decode(c_app_id(i), 185, c_xtr_percent(i)),
1625 pay_amount_tolerance = decode(c_app_id(i), 801, c_pay_amount(i)),
1626 pay_percent_tolerance = decode(c_app_id(i), 801, c_pay_percent(i)),
1627 cashflow_display_order = decode(c_app_id(i), 185,c_cashflow_order(i)),
1628 target_balance = decode(c_app_id(i), 185, c_target_balance(i)),
1629 last_update_date = sysdate,
1630 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1631 where ce_upgrade_id = c_group_id(i);
1632
1633 end if;
1634
1635 c := '115';
1636 close acct_sec;
1637
1638 -- Validate primary records and populate validation errors if any
1639 c := '116';
1640 open acct_pri;
1641 c_upg_id.delete;
1642 c_country.delete;
1643 c_bank_number.delete;
1644 c_bank_or_branch_number.delete;
1645 c_acct_num.delete;
1646 c_acct_reference.delete;
1647 c_acct_name.delete;
1648 c_cd.delete;
1649 c_group_id.delete;
1650 c_acct_type.delete;
1651 c_acct_suffix.delete;
1652 c_status.delete;
1653
1654 c := '117';
1655 FETCH acct_pri BULK COLLECT INTO c_upg_id, c_country, c_bank_number,
1656 c_bank_or_branch_number, c_acct_num, c_acct_reference, c_acct_name,
1657 c_cd, c_group_id, c_acct_type, c_acct_suffix;
1658
1659 c := '119';
1660 for i in c_upg_id.first..c_upg_id.last
1661 loop
1662
1663 c := '120';
1664 fnd_msg_pub.initialize;
1665 x_msg_name := NULL;
1666
1667 ce_validate_bankinfo_upg.ce_validate_account
1668 (x_country_name => c_country(i),
1669 x_bank_number => c_bank_number(i),
1670 x_branch_number => c_bank_or_branch_number(i),
1671 x_account_number => c_acct_num(i),
1672 x_account_type => c_acct_type(i),
1673 x_account_suffix => c_acct_suffix(i),
1674 x_secondary_account_reference => c_acct_reference(i),
1675 x_account_name => c_acct_name(i),
1676 x_cd => c_cd(i),
1677 x_validation_type => 'ALL',
1678 p_init_msg_list => 'T',
1679 x_msg_count => x_count,
1680 x_msg_data => x_msgdata,
1681 x_value_out => x_val_out,
1682 x_message_name_all => x_msg_name);
1683
1684 if x_count > 0 then
1685 c_status(i) := 'INVALID';
1686 else
1687 c_status(i) := 'NULL';
1688 end if;
1689 /*
1690 begin
1691 delete from ce_bank_upgrade_errors
1692 where ce_upgrade_id = c_upg_id(i)
1693 and bank_entity_type = 'ACCOUNT';
1694 exception
1695 when no_data_found then
1696 null;
1697 when too_many_rows then
1698 null;
1699 when others then
1700 raise;
1701 end;
1702 */
1703 c := '121';
1704 FOR j IN 1..x_count LOOP
1705
1706 if (j=x_count) then
1707 p_msg_name := x_msg_name;
1708 x_msg_name := NULL;
1709 else
1710 p_msg_name := substr(x_msg_name, 1, instr(x_msg_name,',')-1);
1711 x_msg_name := substr(x_msg_name, instr(x_msg_name,',')+1,
1712 length(x_msg_name)-instr(x_msg_name,','));
1713 end if;
1714
1715 if (p_msg_name is not null) then
1716 insert into ce_bank_upgrade_errors
1717 (ce_upgrade_id, bank_entity_type, key_error_flag, application_id,
1718 message_name, creation_date, created_by, last_update_date,
1719 last_updated_by)
1720 values (c_upg_id(i), 'ACCOUNT', 'N', 260, p_msg_name,
1721 sysdate, NVL(FND_GLOBAL.user_id,-1),
1722 sysdate, NVL(FND_GLOBAL.user_id,-1));
1723 end if;
1724 END LOOP;
1725 end loop;
1726
1727 -- Set upgrade status
1728 c := '122';
1729 forall i in c_upg_id.first..c_upg_id.last
1730 update ce_upg_bank_accounts
1731 set upgrade_status = c_status(i),
1732 last_update_date = sysdate,
1733 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1734 where ce_upgrade_id = c_upg_id(i)
1735 and c_status(i) = 'INVALID';
1736
1737 c := '123';
1738 close acct_pri;
1739
1740 c := '124';
1741 open ba_use_cur;
1742 tot_count := 0;
1743
1744 c := '125';
1745 loop
1746 c_upg_id.delete;
1747 c_app_id.delete;
1748 c_creation_date.delete;
1749 c_group_id.delete;
1750 c_org_id.delete;
1751 c_le_id.delete;
1752 c_pay_flag.delete;
1753 c_pay_ba_id.delete;
1754
1758 LIMIT c_commit_size;
1755 c := '126';
1756 FETCH ba_use_cur BULK COLLECT INTO c_group_id, c_org_id, c_le_id, c_app_id,
1757 c_upg_id, c_creation_date, c_pay_flag, c_pay_ba_id
1759
1760 c := '127';
1761 if c_upg_id.count > 0 then
1762
1763 -- 6) Grouping account use records
1764 -- 6.1 Set primary account records
1765 c := '128';
1766 forall i in c_upg_id.first..c_upg_id.last
1767 update ce_upg_ba_uses_all bau
1768 set group_id = c_upg_id(i),
1769 primary_acct_use_flag = 'Y',
1770 secondary_acct_use_flag = 'N',
1771 last_update_date = sysdate,
1772 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1773 where ce_upgrade_id = c_upg_id(i)
1774 and not exists
1775 (select null
1776 from ce_upg_bank_accounts ba, ce_upg_ba_uses_all bau2
1777 where ba.ce_upgrade_id = bau2.parent_upgrade_id
1778 and ba.group_id = c_group_id(i)
1779 and nvl(bau2.org_id, -1) = nvl(bau.org_id, -1)
1780 and nvl(bau2.legal_entity_id, -1) = nvl(bau.legal_entity_id, -1)
1781 and bau2.source_application_id = bau.source_application_id
1782 and bau2.primary_acct_use_flag = 'Y');
1783
1784 -- 6.2 Set secondary account records (dummy Payroll only)
1785 forall i in c_upg_id.first..c_upg_id.last
1786 update ce_upg_ba_uses_all bau
1787 set group_id =
1788 (select bau2.group_id
1789 from ce_upg_bank_accounts ba, ce_upg_ba_uses_all bau2
1790 where ba.ce_upgrade_id = bau2.parent_upgrade_id
1791 and ba.group_id = c_group_id(i)
1792 and bau2.org_id = bau.org_id
1793 and bau2.source_application_id = 200
1794 and bau2.primary_acct_use_flag = 'Y'),
1795 primary_acct_use_flag = 'N',
1796 secondary_acct_use_flag = 'Y',
1797 last_update_date = sysdate,
1798 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1799 where ce_upgrade_id = c_upg_id(i)
1800 and group_id is null
1801 and source_application_id = 801
1802 and not exists
1803 (select null
1804 from ce_upg_bank_accounts ba, ce_upg_ba_uses_all bau2
1805 where ba.ce_upgrade_id = bau2.parent_upgrade_id
1806 and ba.group_id = c_group_id(i)
1807 and bau2.org_id = bau.org_id
1808 and bau2.source_application_id = bau.source_application_id
1809 and bau2.group_id is not null)
1810 and exists
1811 (select null
1812 from ce_upg_bank_accounts ba, ce_upg_ba_uses_all bau2
1813 where ba.ce_upgrade_id = bau2.parent_upgrade_id
1814 and ba.group_id = c_group_id(i)
1815 and bau2.org_id = bau.org_id
1816 and bau2.primary_acct_use_flag = 'Y');
1817
1818 -- 6.3 Group all other bank account use records
1819 c := '129';
1820 forall i in c_upg_id.first..c_upg_id.last
1821 update ce_upg_ba_uses_all bau
1822 set group_id =
1823 (select bau2.group_id
1824 from ce_upg_bank_accounts ba, ce_upg_ba_uses_all bau2
1825 where ba.group_id = c_group_id(i)
1826 and ba.ce_upgrade_id = bau2.parent_upgrade_id
1827 and bau2.org_id = bau.org_id
1828 and bau2.source_application_id =
1829 bau.source_application_id
1830 and bau2.primary_flag = 'Y'
1831 and rownum = 1),
1832 primary_acct_use_flag = 'N',
1833 secondary_acct_use_flag = 'N',
1834 last_update_date = sysdate,
1835 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1836 where ce_upgrade_id = c_upg_id(i)
1837 and group_id is null;
1838
1839 c := '130';
1840 -- 6.4 Populate secondary attributes to primary records
1841 forall i in c_upg_id.first..c_upg_id.last
1842 update ce_upg_ba_uses_all bau
1843 set pay_use_enable_flag = c_pay_flag(i),
1844 payroll_bank_account_id = c_pay_ba_id(i),
1845 last_update_date = sysdate,
1846 last_updated_by = nvl(FND_GLOBAL.user_id,-1)
1847 where source_application_id = 200
1848 and primary_acct_use_flag = 'Y'
1849 and exists
1850 (select null
1851 from ce_upg_ba_uses_all bau2
1852 where bau2.group_id = bau.ce_upgrade_id
1853 and bau2.ce_upgrade_id = c_upg_id(i)
1854 and bau2.source_application_id = 801
1855 and bau2.secondary_acct_use_flag = 'Y');
1856
1857 else
1858 if l_debug in ('Y', 'C') then
1859 cep_standard.debug('c_upg_id.count='||to_char(c_upg_id.count));
1860 end if;
1861 exit;
1862 end if;
1863 tot_count := sql%rowcount;
1864
1865 c := '131';
1866 commit;
1867 exit when ba_use_cur%notfound;
1868 end loop;
1869
1870 c := '132';
1871 close ba_use_cur;
1872 if l_debug in ('Y', 'C') then
1873 cep_standard.debug('Total account count='||to_char(tot_count));
1874 end if;
1875
1876 --
1880 begin
1877 -- Update GROUPED status to ce_bank_upgrade_modes for 'ACCOUNT' level
1878 --
1879 c := '133';
1881 update ce_bank_upgrade_modes
1882 set account_upgrade_mode = 'GROUPED',
1883 last_update_date = sysdate,
1884 last_updated_by = nvl(FND_GLOBAL.user_id,-1);
1885
1886 exception
1887 when too_many_rows then
1888 null;
1889 when others then
1890 raise;
1891 end;
1892
1893 end if;
1894
1895 c := '134';
1896 if l_debug in ('Y', 'C') then
1897 cep_standard.debug('<<CE_BANK_GROUPINGS.grouping '||sysdate);
1898 cep_standard.disable_debug(p_display_debug);
1899 end if;
1900
1901 c := '135';
1902
1903 EXCEPTION
1904 WHEN NO_DATA_FOUND THEN
1905 if (c_grouping_flag = 'N') then
1906 cep_standard.debug('c='||c||', the '||p_bank_entity_type||
1907 ' upgrade mode is not ready for grouping.');
1908 end if;
1909 WHEN OTHERS THEN
1910 rollback;
1911 cep_standard.debug('sqlerrm='||sqlerrm);
1912 cep_standard.debug('EXCEPTION: CE_BANK_GROUPINGS.grouping c=' || c);
1913 RAISE;
1914
1915 END; /* grouping */
1916
1917 END CE_BANK_GROUPINGS;