[Home] [Help]
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,
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:
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
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),
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,
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
1493:
1494: -- 5.2 Set secondary flag for bank account records
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)
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
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)
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
1613: if c_group_id.count > 0 then
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)),
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)
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)
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
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
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');
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 =