[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select country, bank_or_branch_number, organization_name, ce_upgrade_id,
source_application_id, to_char(creation_date,'DD/MM/YYYY')
from ce_upg_bank_rec
where group_id is null
and bank_entity_type = p_bank_entity_type
order by country, bank_or_branch_number,
decode(source_application_id, 200, 1, 185, 2),
creation_date desc;
select group_id, ce_upgrade_id, known_as
from ce_upg_bank_rec
where secondary_flag = 'Y'
and source_application_id <> 200
and bank_entity_type = p_bank_entity_type
order by group_id, source_application_id;
select ce_upgrade_id, country, bank_or_branch_number,
organization_name, jgzz_fiscal_code, organization_name_phonetic
from ce_upg_bank_rec
where bank_entity_type = p_bank_entity_type
and primary_flag = 'Y'
order by country, bank_or_branch_number, creation_date desc;
select bb.country, bb.bank_or_branch_number, bb.organization_name,
bb.ce_upgrade_id, bb.source_application_id,
to_char(bb.creation_date,'DD/MM/YYYY'), b.group_id
from ce_upg_bank_rec b, ce_upg_bank_rec bb
where b.ce_upgrade_id = bb.parent_upgrade_id
and bb.group_id is null
and b.bank_entity_type = 'BANK'
and bb.bank_entity_type = p_bank_entity_type
order by bb.country, b.group_id, bb.bank_or_branch_number,
decode(bb.source_application_id, 200, 1, 185, 2),
bb.creation_date desc;
select group_id, ce_upgrade_id, known_as
from ce_upg_bank_rec
where secondary_flag = 'Y'
and source_application_id <> 200
and bank_entity_type = p_bank_entity_type
order by group_id, source_application_id;
select bb.ce_upgrade_id, bb.country, bb.bank_or_branch_number,
b.group_id, bb.organization_name_phonetic
from ce_upg_bank_rec b, ce_upg_bank_rec bb
where b.bank_entity_type = 'BANK'
and b.ce_upgrade_id = bb.parent_upgrade_id
and bb.bank_entity_type = p_bank_entity_type
and bb.primary_flag = 'Y'
order by b.group_id, bb.bank_or_branch_number, bb.creation_date desc;
select bb.country, ba.bank_account_name, ba.bank_account_num,
ba.currency_code, ba.bank_account_type, ba.ce_upgrade_id,
ba.source_application_id, to_char(ba.creation_date,'DD/MM/YYYY'),
bb.group_id
from ce_upg_bank_rec bb, ce_upg_bank_accounts ba
where bb.ce_upgrade_id = ba.parent_upgrade_id
and ba.group_id is null
and bb.bank_entity_type = 'BRANCH'
order by bb.group_id, ba.bank_account_name, ba.bank_account_num,
ba.currency_code,
decode(bb.country, 'JP', ba.bank_account_type, 'X'),
decode(ba.source_application_id, 200, 1, 185, 2, 801, 3),
ba.creation_date desc;
select group_id, source_application_id, ce_upgrade_id,
start_date, legal_account_name, description, xtr_use_allowed_flag,
pay_use_allowed_flag, xtr_amount_tolerance, xtr_percent_tolerance,
pay_amount_tolerance, pay_percent_tolerance,
cashflow_display_order, target_balance
from ce_upg_bank_accounts
where secondary_acct_flag = 'Y'
and source_application_id <> 200
order by group_id, source_application_id;
select ba.ce_upgrade_id, bb.country, b.bank_or_branch_number,
bb.bank_or_branch_number, ba.bank_account_num,
ba.secondary_account_reference, ba.bank_account_name,
ba.check_digits, bb.group_id, ba.bank_account_type,
ba.account_suffix
from ce_upg_bank_rec b, ce_upg_bank_rec bb, ce_upg_bank_accounts ba
where b.bank_entity_type = 'BANK'
and b.ce_upgrade_id = bb.parent_upgrade_id
and bb.bank_entity_type = 'BRANCH'
and bb.ce_upgrade_id = ba.parent_upgrade_id
and ba.primary_acct_flag = 'Y'
order by bb.group_id, ba.bank_account_name, ba.bank_account_num,
ba.creation_date desc;
select ba.group_id, bau.org_id, bau.legal_entity_id, bau.source_application_id,
bau.ce_upgrade_id, to_char(ba.creation_date,'DD/MM/YYYY'),
pay_use_enable_flag, payroll_bank_account_id
from ce_upg_bank_accounts ba, ce_upg_ba_uses_all bau
where ba.ce_upgrade_id = bau.parent_upgrade_id
and bau.group_id is null
order by ba.group_id, bau.org_id, bau.legal_entity_id,
decode(bau.source_application_id, 200, 1, 185, 2, 801, 3),
ba.creation_date desc;
select 'Y'
into c_grouping_flag
from ce_bank_upgrade_modes
where source_product_name = 'ALL'
and decode(p_bank_entity_type, 'BRANCH', bank_upgrade_mode,
'ACCOUNT', branch_upgrade_mode,'GROUPED') in ('GROUPED','FROZEN')
and decode(p_bank_entity_type, 'BANK', bank_upgrade_mode,
'BRANCH', branch_upgrade_mode, account_upgrade_mode) = 'PRE_GROUP';
c_country.delete;
c_bank_or_branch_number.delete;
c_org_name.delete;
c_upg_id.delete;
c_app_id.delete;
c_creation_date.delete;
update ce_upg_bank_rec b
set group_id = c_upg_id(i),
primary_flag = 'Y',
secondary_flag = 'N',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where bank_or_branch_number = c_bank_or_branch_number(i)
and bank_entity_type = p_bank_entity_type
and bank_or_branch_number is not null
and ce_upgrade_id = c_upg_id(i)
and not exists
(select null from ce_upg_bank_rec
where country = b.country
and bank_or_branch_number = b.bank_or_branch_number
and bank_entity_type = b.bank_entity_type
and primary_flag = 'Y'
union all
select null from ce_upg_bank_rec
where country = b.country
and bank_or_branch_number is null
and organization_name = b.organization_name
and bank_entity_type = b.bank_entity_type
and primary_flag = 'Y');
update ce_upg_bank_rec b
set group_id = nvl(
(select group_id from ce_upg_bank_rec
where country = b.country
and bank_or_branch_number = b.bank_or_branch_number
and bank_entity_type = b.bank_entity_type
and source_application_id <> b.source_application_id
and primary_flag = 'Y'
and rownum = 1),
(select group_id from ce_upg_bank_rec
where country = b.country
and bank_or_branch_number is null
and organization_name = b.organization_name
and bank_entity_type = b.bank_entity_type
and source_application_id <> b.source_application_id
and primary_flag = 'Y'
and rownum = 1)),
primary_flag = 'N',
secondary_flag = 'Y',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where bank_or_branch_number = c_bank_or_branch_number(i)
and bank_or_branch_number is not null
and bank_entity_type = p_bank_entity_type
and ce_upgrade_id = c_upg_id(i)
and group_id is null
and not exists
(select null from ce_upg_bank_rec
where country = b.country
and bank_or_branch_number = b.bank_or_branch_number
and bank_entity_type = b.bank_entity_type
and source_application_id = b.source_application_id
and group_id is not null
union all
select null from ce_upg_bank_rec
where country = b.country
and bank_or_branch_number is null
and organization_name = b.organization_name
and bank_entity_type = b.bank_entity_type
and source_application_id = b.source_application_id
and group_id is not null)
and exists
(select null from ce_upg_bank_rec
where country = b.country
and bank_or_branch_number = b.bank_or_branch_number
and bank_entity_type = b.bank_entity_type
and primary_flag = 'Y'
union all
select null from ce_upg_bank_rec
where country = b.country
and bank_or_branch_number is null
and organization_name = b.organization_name
and bank_entity_type = b.bank_entity_type
and primary_flag = 'Y');
update ce_upg_bank_rec b
set group_id = nvl(
(select group_id from ce_upg_bank_rec
where country = b.country
and bank_or_branch_number = b.bank_or_branch_number
and bank_entity_type = b.bank_entity_type
and primary_flag = 'Y'
and rownum = 1),
(select group_id from ce_upg_bank_rec
where country = b.country
and bank_or_branch_number is null
and organization_name = b.organization_name
and bank_entity_type = b.bank_entity_type
and primary_flag = 'Y'
and rownum = 1)),
primary_flag = 'N',
secondary_flag = 'N',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where bank_or_branch_number = c_bank_or_branch_number(i)
and bank_or_branch_number is not null
and bank_entity_type = p_bank_entity_type
and ce_upgrade_id = c_upg_id(i)
and group_id is null;
update ce_upg_bank_rec b
set group_id = c_upg_id(i),
primary_flag = 'Y',
secondary_flag = 'N',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where bank_or_branch_number is null
and bank_entity_type = p_bank_entity_type
and ce_upgrade_id = c_upg_id(i)
and not exists
(select null from ce_upg_bank_rec b2
where country = b.country
and organization_name = b.organization_name
and bank_entity_type = b.bank_entity_type
and primary_flag = 'Y');
update ce_upg_bank_rec b
set group_id =
(select group_id from ce_upg_bank_rec
where country = b.country
and organization_name = b.organization_name
and bank_entity_type = b.bank_entity_type
and source_application_id <> b.source_application_id
and primary_flag = 'Y'
and rownum = 1),
primary_flag = 'N',
secondary_flag = 'Y',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where bank_or_branch_number is null
and bank_entity_type = p_bank_entity_type
and ce_upgrade_id = c_upg_id(i)
and group_id is null
and organization_name = c_org_name(i)
--
and not exists
(select null from ce_upg_bank_rec b2
where country = b.country
and organization_name = b.organization_name
and bank_entity_type = b.bank_entity_type
and source_application_id = b.source_application_id
and group_id is not null)
--
and exists
(select null from ce_upg_bank_rec b2
where country = b.country
and organization_name = b.organization_name
and bank_entity_type = b.bank_entity_type
and primary_flag = 'Y');
(select null from ce_upg_bank_rec b3
where group_id = b2.group_id
and country = b.country
and organization_name = b.organization_name
and bank_entity_type = b.bank_entity_type
and source_application_id = b.source_application_id));
update ce_upg_bank_rec b
set group_id =
(select group_id from ce_upg_bank_rec
where country = b.country
and organization_name = b.organization_name
and bank_entity_type = b.bank_entity_type
and primary_flag = 'Y'
and rownum = 1),
primary_flag = 'N',
secondary_flag = 'N',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where bank_or_branch_number is null
and organization_name = c_org_name(i)
and bank_entity_type = p_bank_entity_type
and ce_upgrade_id = c_upg_id(i)
and group_id is null;
c_group_id.delete;
c_upg_id.delete;
c_known_as.delete;
update ce_upg_bank_rec
set known_as = nvl(c_known_as(i), known_as),
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_group_id(i);
c_upg_id.delete;
c_country.delete;
c_bank_or_branch_number.delete;
c_org_name.delete;
c_jgzz_fiscal_code.delete;
c_org_name_alt.delete;
c_status.delete;
insert into ce_upga_bank_rec
(party_id,
ce_upgrade_id,
parent_upgrade_id,
bank_entity_type,
upgrade_status,
primary_flag,
secondary_flag,
group_id,
bank_or_branch_number,
bank_code,
branch_code,
institution_type,
country,
branch_type,
rfc_code,
created_by_module,
organization_name,
organization_name_phonetic,
known_as,
jgzz_fiscal_code,
mission_statement,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
start_date_active,
end_date_active,
eft_user_num,
clearing_house_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
select
party_id,
ce_upgrade_id,
parent_upgrade_id,
bank_entity_type,
upgrade_status,
primary_flag,
secondary_flag,
group_id,
bank_or_branch_number,
bank_code,
branch_code,
institution_type,
country,
branch_type,
rfc_code,
curr_module,
organization_name,
organization_name_phonetic,
known_as,
jgzz_fiscal_code,
mission_statement,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
start_date_active,
end_date_active,
eft_user_num,
clearing_house_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
from ce_upg_bank_rec b
where ce_upgrade_id = c_upg_id(i)
and exists
(select null from ce_upg_bank_rec b2
where b2.country = b.country
and b2.bank_or_branch_number <> b.bank_or_branch_number
and b2.organization_name = b.organization_name
and b2.bank_entity_type = b.bank_entity_type
and b2.primary_flag = 'Y'
and b2.creation_date >= b.creation_date);
update ce_upg_bank_rec b
set organization_name =
organization_name || ' ' || bank_or_branch_number,
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_upg_id(i)
and exists
(select null from ce_upg_bank_rec b2
where b2.country = b.country
and b2.bank_or_branch_number <> b.bank_or_branch_number
and b2.organization_name = b.organization_name
and b2.bank_entity_type = b.bank_entity_type
and b2.primary_flag = 'Y'
and b2.creation_date >= b.creation_date);
delete from ce_bank_upgrade_errors
where ce_upgrade_id = c_upg_id(i)
and bank_entity_type = 'BANK';
insert into ce_bank_upgrade_errors
(ce_upgrade_id, bank_entity_type, key_error_flag, application_id,
message_name, creation_date, created_by, last_update_date,
last_updated_by)
values (c_upg_id(i), 'BANK', 'N', 260, p_msg_name,
sysdate, NVL(FND_GLOBAL.user_id,-1),
sysdate, NVL(FND_GLOBAL.user_id,-1));
update ce_upg_bank_rec
set upgrade_status = c_status(i),
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_upg_id(i)
and c_status(i) = 'INVALID';
update ce_bank_upgrade_modes
set bank_upgrade_mode = 'GROUPED',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1);
c_country.delete;
c_bank_or_branch_number.delete;
c_org_name.delete;
c_upg_id.delete;
c_app_id.delete;
c_creation_date.delete;
c_group_id.delete;
update ce_upg_bank_rec bb
set group_id = c_upg_id(i),
primary_flag = 'Y',
secondary_flag = 'N',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_upg_id(i)
and bank_or_branch_number is not null
and not exists
(select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.ce_upgrade_id = bb2.parent_upgrade_id
and b.group_id = c_group_id(i)
and b.bank_entity_type = 'BANK'
and bb2.bank_or_branch_number = bb.bank_or_branch_number
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.primary_flag = 'Y'
union all
select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.ce_upgrade_id = bb2.parent_upgrade_id
and b.group_id = c_group_id(i)
and b.bank_entity_type = 'BANK'
and bb2.bank_or_branch_number is null
and bb2.organization_name = bb.organization_name
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.primary_flag = 'Y');
update ce_upg_bank_rec bb
set group_id = nvl(
(select bb2.group_id
from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.group_id = c_group_id(i)
and b.country = bb.country
and b.bank_entity_type = 'BANK'
and bb2.parent_upgrade_id = b.ce_upgrade_id
and bb2.bank_or_branch_number = bb.bank_or_branch_number
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.source_application_id <> bb.source_application_id
and bb2.primary_flag = 'Y'
and rownum = 1),
(select bb2.group_id
from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.group_id = c_group_id(i)
and b.country = bb.country
and b.bank_entity_type = 'BANK'
and bb2.parent_upgrade_id = b.ce_upgrade_id
and bb2.bank_or_branch_number is null
and bb2.organization_name = bb.organization_name
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.source_application_id <> bb.source_application_id
and bb2.primary_flag = 'Y'
and rownum = 1)),
primary_flag = 'N',
secondary_flag = 'Y',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where bank_or_branch_number = c_bank_or_branch_number(i)
and bank_or_branch_number is not null
and ce_upgrade_id = c_upg_id(i)
and bank_entity_type = p_bank_entity_type
and group_id is null
and not exists
(select null
from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.country = bb.country
and b.bank_entity_type = 'BANK'
and b.group_id = c_group_id(i)
and b.ce_upgrade_id = bb2.parent_upgrade_id
and bb2.bank_or_branch_number = bb.bank_or_branch_number
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.source_application_id = bb.source_application_id
and bb2.group_id is not null
union all
select null
from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.country = bb.country
and b.bank_entity_type = 'BANK'
and b.group_id = c_group_id(i)
and b.ce_upgrade_id = bb2.parent_upgrade_id
and bb2.bank_or_branch_number is null
and bb2.organization_name = bb.organization_name
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.source_application_id = bb.source_application_id
and bb2.group_id is not null)
and exists
(select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.country = bb.country
and b.bank_entity_type = 'BANK'
and b.group_id = c_group_id(i)
and b.ce_upgrade_id = bb2.parent_upgrade_id
and bb2.bank_or_branch_number = bb.bank_or_branch_number
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.primary_flag = 'Y'
union all
select null
from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.country = bb.country
and b.bank_entity_type = 'BANK'
and b.group_id = c_group_id(i)
and b.ce_upgrade_id = bb2.parent_upgrade_id
and bb2.bank_or_branch_number is null
and bb2.organization_name = bb.organization_name
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.primary_flag = 'Y');
update ce_upg_bank_rec bb
set group_id = nvl(
(select bb2.group_id
from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.country = bb.country
and b.group_id = c_group_id(i)
and b.bank_entity_type = 'BANK'
and b.ce_upgrade_id = bb2.parent_upgrade_id
and bb2.bank_or_branch_number = bb.bank_or_branch_number
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.primary_flag = 'Y'
and rownum = 1),
(select bb2.group_id
from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.country = bb.country
and b.group_id = c_group_id(i)
and b.bank_entity_type = 'BANK'
and b.ce_upgrade_id = bb2.parent_upgrade_id
and bb2.bank_or_branch_number is null
and bb2.organization_name = bb.organization_name
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.primary_flag = 'Y'
and rownum = 1)),
primary_flag = 'N',
secondary_flag = 'N',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where bank_or_branch_number = c_bank_or_branch_number(i)
and bank_or_branch_number is not null
and ce_upgrade_id = c_upg_id(i)
and group_id is null;
update ce_upg_bank_rec bb
set group_id = c_upg_id(i),
primary_flag = 'Y',
secondary_flag = 'N',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where bank_or_branch_number is null
and ce_upgrade_id = c_upg_id(i)
and not exists
(select null
from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.country = bb.country
and b.group_id = c_group_id(i)
and b.bank_entity_type = 'BANK'
and b.ce_upgrade_id = bb2.parent_upgrade_id
and bb2.organization_name = bb.organization_name
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.primary_flag = 'Y');
update ce_upg_bank_rec bb
set group_id =
(select bb2.group_id
from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.country = bb.country
and b.group_id = c_group_id(i)
and b.bank_entity_type = 'BANK'
and b.ce_upgrade_id = bb2.parent_upgrade_id
and bb2.organization_name = bb.organization_name
and bb2.source_application_id <> bb.source_application_id
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.primary_flag = 'Y'
and rownum = 1),
primary_flag = 'N',
secondary_flag = 'Y',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where bank_or_branch_number is null
and ce_upgrade_id = c_upg_id(i)
and group_id is null
and organization_name = c_org_name(i)
and not exists
(select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.country = bb.country
and b.group_id = c_group_id(i)
and b.bank_entity_type = 'BANK'
and b.ce_upgrade_id = bb2.parent_upgrade_id
and bb2.organization_name = bb.organization_name
and bb2.source_application_id = bb.source_application_id
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.group_id is not null)
and exists
(select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.country = bb.country
and b.group_id = c_group_id(i)
and b.bank_entity_type = 'BANK'
and b.ce_upgrade_id = bb2.parent_upgrade_id
and bb2.organization_name = bb.organization_name
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.primary_flag = 'Y');
update ce_upg_bank_rec bb
set group_id =
(select bb2.group_id
from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.country = bb.country
and b.group_id = c_group_id(i)
and b.bank_entity_type = 'BANK'
and b.ce_upgrade_id = bb2.parent_upgrade_id
and bb2.organization_name = bb.organization_name
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.primary_flag = 'Y'
and rownum = 1),
primary_flag = 'N',
secondary_flag = 'N',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where bank_or_branch_number is null
and organization_name = c_org_name(i)
and ce_upgrade_id = c_upg_id(i)
and group_id is null;
c_group_id.delete;
c_upg_id.delete;
c_known_as.delete;
update ce_upg_bank_rec
set known_as = nvl(c_known_as(i), known_as),
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_group_id(i);
insert into ce_upg_cont_point_rec
(CE_UPGRADE_ID,
BANK_ENTITY_TYPE,
UPGRADE_STATUS,
CONTACT_POINT_TYPE,
PHONE_LINE_TYPE,
OWNER_TABLE_NAME,
OWNER_TABLE_ID,
CREATED_BY_MODULE,
PHONE_AREA_CODE,
PHONE_NUMBER,
ORG_PRIMARY_PHONE_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
select
c_group_id(i),
BANK_ENTITY_TYPE,
UPGRADE_STATUS,
CONTACT_POINT_TYPE,
PHONE_LINE_TYPE,
OWNER_TABLE_NAME,
OWNER_TABLE_ID,
curr_module,
PHONE_AREA_CODE,
PHONE_NUMBER,
ORG_PRIMARY_PHONE_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
from ce_upg_cont_point_rec p
where ce_upgrade_id = c_upg_id(i)
and CONTACT_POINT_TYPE = 'PHONE'
and PHONE_LINE_TYPE in ('FAX', 'PAGER')
and not exists
(select null from ce_upg_cont_point_rec p2
where p2.ce_upgrade_id = c_group_id(i)
and bank_entity_type = p.BANK_ENTITY_TYPE
and CONTACT_POINT_TYPE = p.CONTACT_POINT_TYPE
and PHONE_LINE_TYPE = p.PHONE_LINE_TYPE);
insert into ce_upg_cont_point_rec
(CE_UPGRADE_ID,
BANK_ENTITY_TYPE,
UPGRADE_STATUS,
CONTACT_POINT_TYPE,
PHONE_LINE_TYPE,
OWNER_TABLE_NAME,
OWNER_TABLE_ID,
CREATED_BY_MODULE,
EMAIL_ADDRESS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
select
c_group_id(i),
BANK_ENTITY_TYPE,
UPGRADE_STATUS,
CONTACT_POINT_TYPE,
PHONE_LINE_TYPE,
OWNER_TABLE_NAME,
OWNER_TABLE_ID,
curr_module,
EMAIL_ADDRESS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
from ce_upg_cont_point_rec p
where ce_upgrade_id = c_upg_id(i)
and CONTACT_POINT_TYPE = 'EMAIL'
and not exists
(select null from ce_upg_cont_point_rec p2
where p2.ce_upgrade_id = c_group_id(i)
and bank_entity_type = p.BANK_ENTITY_TYPE
and CONTACT_POINT_TYPE = p.CONTACT_POINT_TYPE);
insert into ce_upg_loc_rec l
(CE_UPGRADE_ID,
BANK_ENTITY_TYPE,
UPGRADE_STATUS,
IDENTIFYING_ADDRESS_FLAG,
COUNTRY,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
ADDRESS_STYLE,
CITY,
STATE,
PROVINCE,
COUNTY,
POSTAL_CODE,
ADDRESS_LINE_PHONETIC,
CREATED_BY_MODULE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
select
c_group_id(i),
BANK_ENTITY_TYPE,
UPGRADE_STATUS,
IDENTIFYING_ADDRESS_FLAG,
COUNTRY,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
ADDRESS_STYLE,
CITY,
STATE,
PROVINCE,
COUNTY,
POSTAL_CODE,
ADDRESS_LINE_PHONETIC,
curr_module,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
from ce_upg_loc_rec l
where ce_upgrade_id = c_upg_id(i)
and IDENTIFYING_ADDRESS_FLAG = 'N'
and not exists
(select null from ce_upg_loc_rec
where ce_upgrade_id = c_group_id(i)
and bank_entity_type = l.BANK_ENTITY_TYPE
and IDENTIFYING_ADDRESS_FLAG = l.IDENTIFYING_ADDRESS_FLAG);
c_upg_id.delete;
c_country.delete;
c_bank_or_branch_number.delete;
c_group_id.delete;
c_org_name_alt.delete;
c_status.delete;
insert into ce_upga_bank_rec
(party_id,
ce_upgrade_id,
parent_upgrade_id,
bank_entity_type,
upgrade_status,
primary_flag,
secondary_flag,
group_id,
bank_or_branch_number,
bank_code,
branch_code,
institution_type,
country,
branch_type,
rfc_code,
created_by_module,
organization_name,
organization_name_phonetic,
known_as,
jgzz_fiscal_code,
mission_statement,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
start_date_active,
end_date_active,
eft_user_num,
clearing_house_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
select
party_id,
ce_upgrade_id,
parent_upgrade_id,
bank_entity_type,
upgrade_status,
primary_flag,
secondary_flag,
group_id,
bank_or_branch_number,
bank_code,
branch_code,
institution_type,
country,
branch_type,
rfc_code,
curr_module,
organization_name,
organization_name_phonetic,
known_as,
jgzz_fiscal_code,
mission_statement,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
start_date_active,
end_date_active,
eft_user_num,
clearing_house_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
from ce_upg_bank_rec bb
where ce_upgrade_id = c_upg_id(i)
and exists
(select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.ce_upgrade_id = bb2.parent_upgrade_id
and b.bank_entity_type = 'BANK'
and b.group_id = c_group_id(i)
and bb2.bank_or_branch_number <> bb.bank_or_branch_number
and bb2.organization_name = bb.organization_name
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.primary_flag = 'Y'
and bb2.creation_date >= bb.creation_date);
update ce_upg_bank_rec bb
set organization_name =
organization_name || ' ' || bank_or_branch_number,
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_upg_id(i)
and exists
(select null from ce_upg_bank_rec b, ce_upg_bank_rec bb2
where b.ce_upgrade_id = bb2.parent_upgrade_id
and b.bank_entity_type = 'BANK'
and b.group_id = c_group_id(i)
and bb2.bank_or_branch_number <> bb.bank_or_branch_number
and bb2.organization_name = bb.organization_name
and bb2.bank_entity_type = bb.bank_entity_type
and bb2.primary_flag = 'Y'
and bb2.creation_date >= bb.creation_date);
delete from ce_bank_upgrade_errors
where ce_upgrade_id = c_upg_id(i)
and bank_entity_type = 'BRANCH';
insert into ce_bank_upgrade_errors
(ce_upgrade_id, bank_entity_type, key_error_flag, application_id,
message_name, creation_date, created_by, last_update_date,
last_updated_by)
values (c_upg_id(i), 'BRANCH', 'N', 260, p_msg_name,
sysdate, NVL(FND_GLOBAL.user_id,-1),
sysdate, NVL(FND_GLOBAL.user_id,-1));
update ce_upg_bank_rec
set upgrade_status = c_status(i),
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_upg_id(i)
and c_status(i) = 'INVALID';
update ce_bank_upgrade_modes
set branch_upgrade_mode = 'GROUPED',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1);
c_country.delete;
c_acct_name.delete;
c_acct_num.delete;
c_currency.delete;
c_acct_type.delete;
c_upg_id.delete;
c_app_id.delete;
c_creation_date.delete;
c_group_id.delete;
update ce_upg_bank_accounts ba
set group_id = c_upg_id(i),
primary_acct_flag = 'Y',
secondary_acct_flag = 'N',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_upg_id(i)
and not exists
(select null from ce_upg_bank_rec bb, ce_upg_bank_accounts ba2
where bb.ce_upgrade_id = ba2.parent_upgrade_id
and bb.group_id = c_group_id(i)
and bb.bank_entity_type = 'BRANCH'
and ba2.bank_account_name = ba.bank_account_name
and ba2.bank_account_num = ba.bank_account_num
and ba2.currency_code = ba.currency_code
and decode(bb.country, 'JP', ba2.bank_account_type, 'X') =
decode(bb.country, 'JP', ba.bank_account_type, 'X')
and ba2.primary_acct_flag = 'Y');
update ce_upg_bank_accounts ba
set group_id =
(select ba2.group_id
from ce_upg_bank_rec bb, ce_upg_bank_accounts ba2
where bb.group_id = c_group_id(i)
and bb.bank_entity_type = 'BANK'
and ba2.parent_upgrade_id = bb.ce_upgrade_id
and ba2.bank_account_name = ba.bank_account_name
and ba2.bank_account_num = ba.bank_account_num
and ba2.currency_code = ba.currency_code
and decode(bb.country, 'JP', ba2.bank_account_type, 'X')
= decode(bb.country, 'JP', ba.bank_account_type, 'X')
and ba2.source_application_id <> ba.source_application_id
and ba2.primary_acct_flag = 'Y'
and rownum = 1),
primary_acct_flag = 'N',
secondary_acct_flag = 'Y',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_upg_id(i)
and group_id is null
and not exists
(select null
from ce_upg_bank_rec bb, ce_upg_bank_accounts ba2
where bb.bank_entity_type = 'BRANCH'
and bb.group_id = c_group_id(i)
and bb.ce_upgrade_id = ba2.parent_upgrade_id
and ba2.bank_account_name = ba.bank_account_name
and ba2.bank_account_num = ba.bank_account_num
and ba2.currency_code = ba.currency_code
and decode(bb.country, 'JP', ba2.bank_account_type, 'X') =
decode(bb.country, 'JP', ba.bank_account_type, 'X')
and ba2.source_application_id = ba.source_application_id
and ba2.group_id is not null)
and exists
(select null from ce_upg_bank_rec bb, ce_upg_bank_accounts ba2
where bb.bank_entity_type = 'BRANCH'
and bb.group_id = c_group_id(i)
and bb.ce_upgrade_id = ba2.parent_upgrade_id
and ba2.bank_account_name = ba.bank_account_name
and ba2.bank_account_num = ba.bank_account_num
and ba2.currency_code = ba.currency_code
and decode(bb.country, 'JP', ba2.bank_account_type, 'X') =
decode(bb.country, 'JP', ba.bank_account_type, 'X')
and ba2.primary_acct_flag = 'Y');
update ce_upg_bank_accounts ba
set group_id =
(select ba2.group_id
from ce_upg_bank_rec bb, ce_upg_bank_accounts ba2
where bb.group_id = c_group_id(i)
and bb.bank_entity_type = 'BRANCH'
and bb.ce_upgrade_id = ba2.parent_upgrade_id
and ba2.bank_account_name = ba.bank_account_name
and ba2.bank_account_num = ba.bank_account_num
and ba2.currency_code = ba.currency_code
and decode(bb.country, 'JP', ba2.bank_account_type, 'X')
= decode(bb.country, 'JP', ba.bank_account_type, 'X')
and ba2.primary_acct_flag = 'Y'
and rownum = 1),
primary_acct_flag = 'N',
secondary_acct_flag = 'N',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_upg_id(i)
and group_id is null;
c_group_id.delete;
c_app_id.delete;
c_upg_id.delete;
c_start_date.delete;
c_legal_name.delete;
c_description.delete;
c_xtr_flag.delete;
c_pay_flag.delete;
c_xtr_amount.delete;
c_xtr_percent.delete;
c_pay_amount.delete;
c_pay_percent.delete;
c_cashflow_order.delete;
c_target_balance.delete;
update ce_upg_bank_accounts
set start_date = decode(c_app_id(i), 185, c_start_date(i)),
legal_account_name = decode(c_app_id(i), 185, c_legal_name(i)),
description = decode(source_application_id, 185, c_description(i)),
xtr_use_allowed_flag = decode(c_app_id(i), 185,c_xtr_flag(i)),
pay_use_allowed_flag = decode(c_app_id(i), 801,c_pay_flag(i)),
xtr_amount_tolerance = decode(c_app_id(i), 185, c_xtr_amount(i)),
xtr_percent_tolerance = decode(c_app_id(i), 185, c_xtr_percent(i)),
pay_amount_tolerance = decode(c_app_id(i), 801, c_pay_amount(i)),
pay_percent_tolerance = decode(c_app_id(i), 801, c_pay_percent(i)),
cashflow_display_order = decode(c_app_id(i), 185,c_cashflow_order(i)),
target_balance = decode(c_app_id(i), 185, c_target_balance(i)),
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_group_id(i);
c_upg_id.delete;
c_country.delete;
c_bank_number.delete;
c_bank_or_branch_number.delete;
c_acct_num.delete;
c_acct_reference.delete;
c_acct_name.delete;
c_cd.delete;
c_group_id.delete;
c_acct_type.delete;
c_acct_suffix.delete;
c_status.delete;
delete from ce_bank_upgrade_errors
where ce_upgrade_id = c_upg_id(i)
and bank_entity_type = 'ACCOUNT';
insert into ce_bank_upgrade_errors
(ce_upgrade_id, bank_entity_type, key_error_flag, application_id,
message_name, creation_date, created_by, last_update_date,
last_updated_by)
values (c_upg_id(i), 'ACCOUNT', 'N', 260, p_msg_name,
sysdate, NVL(FND_GLOBAL.user_id,-1),
sysdate, NVL(FND_GLOBAL.user_id,-1));
update ce_upg_bank_accounts
set upgrade_status = c_status(i),
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_upg_id(i)
and c_status(i) = 'INVALID';
c_upg_id.delete;
c_app_id.delete;
c_creation_date.delete;
c_group_id.delete;
c_org_id.delete;
c_le_id.delete;
c_pay_flag.delete;
c_pay_ba_id.delete;
update ce_upg_ba_uses_all bau
set group_id = c_upg_id(i),
primary_acct_use_flag = 'Y',
secondary_acct_use_flag = 'N',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_upg_id(i)
and not exists
(select null
from ce_upg_bank_accounts ba, ce_upg_ba_uses_all bau2
where ba.ce_upgrade_id = bau2.parent_upgrade_id
and ba.group_id = c_group_id(i)
and nvl(bau2.org_id, -1) = nvl(bau.org_id, -1)
and nvl(bau2.legal_entity_id, -1) = nvl(bau.legal_entity_id, -1)
and bau2.source_application_id = bau.source_application_id
and bau2.primary_acct_use_flag = 'Y');
update ce_upg_ba_uses_all bau
set group_id =
(select bau2.group_id
from ce_upg_bank_accounts ba, ce_upg_ba_uses_all bau2
where ba.ce_upgrade_id = bau2.parent_upgrade_id
and ba.group_id = c_group_id(i)
and bau2.org_id = bau.org_id
and bau2.source_application_id = 200
and bau2.primary_acct_use_flag = 'Y'),
primary_acct_use_flag = 'N',
secondary_acct_use_flag = 'Y',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_upg_id(i)
and group_id is null
and source_application_id = 801
and not exists
(select null
from ce_upg_bank_accounts ba, ce_upg_ba_uses_all bau2
where ba.ce_upgrade_id = bau2.parent_upgrade_id
and ba.group_id = c_group_id(i)
and bau2.org_id = bau.org_id
and bau2.source_application_id = bau.source_application_id
and bau2.group_id is not null)
and exists
(select null
from ce_upg_bank_accounts ba, ce_upg_ba_uses_all bau2
where ba.ce_upgrade_id = bau2.parent_upgrade_id
and ba.group_id = c_group_id(i)
and bau2.org_id = bau.org_id
and bau2.primary_acct_use_flag = 'Y');
update ce_upg_ba_uses_all bau
set group_id =
(select bau2.group_id
from ce_upg_bank_accounts ba, ce_upg_ba_uses_all bau2
where ba.group_id = c_group_id(i)
and ba.ce_upgrade_id = bau2.parent_upgrade_id
and bau2.org_id = bau.org_id
and bau2.source_application_id =
bau.source_application_id
and bau2.primary_flag = 'Y'
and rownum = 1),
primary_acct_use_flag = 'N',
secondary_acct_use_flag = 'N',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where ce_upgrade_id = c_upg_id(i)
and group_id is null;
update ce_upg_ba_uses_all bau
set pay_use_enable_flag = c_pay_flag(i),
payroll_bank_account_id = c_pay_ba_id(i),
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1)
where source_application_id = 200
and primary_acct_use_flag = 'Y'
and exists
(select null
from ce_upg_ba_uses_all bau2
where bau2.group_id = bau.ce_upgrade_id
and bau2.ce_upgrade_id = c_upg_id(i)
and bau2.source_application_id = 801
and bau2.secondary_acct_use_flag = 'Y');
update ce_bank_upgrade_modes
set account_upgrade_mode = 'GROUPED',
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.user_id,-1);