[Home] [Help]
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),
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;
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:
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
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;
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'
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,
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
275: -- 1) Grouping bank records with bank number
276: -- 1.1 Set primary record for 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,
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'
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
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
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
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
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
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
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'
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
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
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'
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
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,
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
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
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
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');
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
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
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'
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,
512: last_updated_by = nvl(FND_GLOBAL.user_id,-1)
513: where ce_upgrade_id = c_group_id(i);
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
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 =
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)
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
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)
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
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
814:
815: -- 3.2 Set secondary flag for records with branch number
816: c := '56';
817: forall i in c_upg_id.first..c_upg_id.last
818: update ce_upg_bank_rec bb
819: set group_id = nvl(
820: (select bb2.group_id
821: from ce_upg_bank_rec b, ce_upg_bank_rec bb2
822: where b.group_id = c_group_id(i)
817: forall i in c_upg_id.first..c_upg_id.last
818: update ce_upg_bank_rec bb
819: set group_id = nvl(
820: (select bb2.group_id
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
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
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
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
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
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
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
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
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
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,
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
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
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
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
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
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
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
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);
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
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
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 =
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)
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)
1344: and bb2.bank_or_branch_number <> bb.bank_or_branch_number
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)
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
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
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
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
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