[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select status
from pay_upgrade_status s,
pay_upgrade_definitions d
where d.short_name = p_upgrade_short_name
and s.upgrade_definition_id = d.upgrade_definition_id
-- and s.business_group_id is null
and s.legislation_code = p_legislation_code;
select organization_id
from hr_organization_information
where org_information_context = 'Business Group Information'
and org_information9 = p_legislation_code
and rownum = 1;
hr_update_utility.setUpdateProcessing(
p_update_name => p_upgrade_short_name,
p_business_group_id => null,
p_legislation_code => p_legislation_code);
hr_update_utility.setUpdateComplete(
p_update_name => p_upgrade_short_name,
p_business_group_id => null,
p_legislation_code => p_legislation_code);
select 'TRUE'
from dual
where exists(
select null
from pay_jp_pre_tax ppt,
pay_assignment_actions paa
where paa.action_status = 'C'
and ppt.assignment_action_id = paa.assignment_action_id
and ppt.action_status = 'C'); */
select 'TRUE'
from pay_jp_pre_tax ppt,
pay_assignment_actions paa
where paa.action_status = 'C'
and ppt.assignment_action_id = paa.assignment_action_id
and ppt.action_status = 'C'
and rownum =1 ;
select 'TRUE'
from pay_action_information pai,
pay_assignment_actions paa
where paa.action_status = 'C'
and pai.action_information_category = 'JP_PRE_TAX_1'
and pai.action_context_type = 'AAP'
and pai.action_information1 = paa.assignment_action_id
and rownum =1 ;
select 'Y'
from dual
where exists(
select null
from pay_jp_pre_tax ppt,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and ppt.assignment_action_id = paa.assignment_action_id
and ppt.action_status = 'C');
select 'Y'
from pay_action_information pai,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and pai.action_information_category = 'JP_PRE_TAX_1'
and pai.action_context_type = 'AAP'
and pai.action_information1 = paa.assignment_action_id
and rownum = 1;
select pai.action_information29 disaster_tax_reduction,
pai.action_information_id,
paa.assignment_action_id
from pay_action_information pai,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and pai.action_information_category = 'JP_PRE_TAX_1'
and pai.action_context_type = 'AAP'
and pai.action_information1 = paa.assignment_action_id
for update of pai.action_information_id nowait;
select ppt.rowid,
paa.assignment_action_id,
ppt.disaster_tax_reduction
from pay_jp_pre_tax ppt,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and ppt.assignment_action_id = paa.assignment_action_id
and ppt.action_status = 'C'
for update of ppt.pre_tax_id nowait;
select creator_id
into l_defined_balance_id
from ff_user_entities
where user_entity_name = 'B_YEA_GRACE_ITX_ASG_RUN'
and legislation_code = 'JP'
and creator_type = 'B';
update pay_action_information
set action_information29 = l_balance --Disaster Tax Reduction
where action_information_id = l_assact.action_information_id;
update pay_jp_pre_tax
set disaster_tax_reduction = l_balance
where rowid = l_assact.rowid;
select 'TRUE'
from dual
where exists(
select null
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee
where pet.element_name = 'YEA_PREV_EMP_INFO'
and pet.legislation_code = 'JP'
and pel.element_type_id = pet.element_type_id
and pee.element_link_id = pel.element_link_id);
select 'Y'
from dual
where exists(
select null
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee
where pet.element_name = 'YEA_PREV_EMP_INFO'
and pet.legislation_code = 'JP'
and pel.element_type_id = pet.element_type_id
and pee.assignment_id = p_assignment_id
and pee.element_link_id = pel.element_link_id);
select employment_income,
si_prems,
mutual_aid_prem,
withholding_tax,
termination_date,
foreign_flag,
employer_address_kana,
employer_address,
employer_name_kana,
employer_name
from (
select /*+ ORDERED USE_NL(PEL PEE PEEV PIV) */
nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 1, peev.screen_entry_value))), 0) employment_income,
nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 2, peev.screen_entry_value))), 0) si_prems,
nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 3, peev.screen_entry_value))), 0) mutual_aid_prem,
nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 4, peev.screen_entry_value))), 0) withholding_tax,
fnd_date.canonical_to_date(min(decode(piv.display_sequence, 5, peev.screen_entry_value))) termination_date,
nvl(min(decode(piv.display_sequence, 6, peev.screen_entry_value)), 'N') foreign_flag,
min(decode(piv.display_sequence, 7, peev.screen_entry_value)) employer_address_kana,
min(decode(piv.display_sequence, 8, peev.screen_entry_value)) employer_address,
hr_jp_standard_pkg.to_hankaku(min(decode(piv.display_sequence, 9, peev.screen_entry_value))) employer_name_kana,
hr_jp_standard_pkg.to_hankaku(min(decode(piv.display_sequence, 10, peev.screen_entry_value))) employer_name
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_input_values_f piv
where pet.element_name = 'YEA_PREV_EMP_INFO'
and pet.legislation_code = 'JP'
and pel.element_type_id = pet.element_type_id
and pel.business_group_id + 0 = l_business_group_id
and pel.effective_start_date
between pet.effective_start_date and pet.effective_end_date
and pee.assignment_id = p_assignment_id
and pee.element_link_id = pel.element_link_id
and pee.effective_start_date
between pel.effective_start_date and pel.effective_end_date
and peev.element_entry_id = pee.element_entry_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
and piv.input_value_id = peev.input_value_id
and peev.effective_start_date
between piv.effective_start_date and pee.effective_end_date
group by
pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date
)
group by
employment_income,
si_prems,
mutual_aid_prem,
withholding_tax,
termination_date,
foreign_flag,
employer_address_kana,
employer_address,
employer_name_kana,
employer_name;
select previous_employer_id,
object_version_number,
employer_country,
employer_address,
end_date
from per_previous_employers
where person_id = p_person_id
and (
replace(upper(hr_jp_standard_pkg.upper_kana(hr_jp_standard_pkg.to_hankaku(employer_name))), ' ') =
replace(upper(hr_jp_standard_pkg.upper_kana(hr_jp_standard_pkg.to_hankaku(p_employer_name))), ' ')
)
and (
end_date = p_termination_date
or (end_date is null and p_termination_date is not null)
or (end_date is not null and p_termination_date is null)
)
and (
(p_foreign_flag = 'N' and nvl(employer_country, 'JP') = 'JP')
or
(p_foreign_flag = 'Y' and employer_country <> 'JP')
)
and rownum <= 1
for update of previous_employer_id;
select business_group_id,
person_id,
effective_start_date
into l_business_group_id,
l_person_id,
l_effective_date
from per_all_assignments_f asg
where assignment_id = p_assignment_id
and rownum <= 1;
select per.party_id,
-- bg.name,
per.full_name
into l_party_id,
-- l_bg_name,
l_full_name
from per_all_people_f per,
per_business_groups_perf bg
where per.person_id = l_person_id
and l_effective_date
between per.effective_start_date and per.effective_end_date
and bg.business_group_id = per.business_group_id;
-- Update is only for the following cases (note address is not checked).
-- 1) Employer Name for both EE and PEM are not null and not different.
-- 2) Termination Date is not different.
-- 3) Country is not different.
--
if csr_prev_employer%found then
hr_utility.trace('Updating PER_PREVIOUS_EMPLOYERS...');
hr_previous_employment_api.update_previous_employer(
P_EFFECTIVE_DATE => l_effective_date,
P_PREVIOUS_EMPLOYER_ID => l_prev_employer.previous_employer_id,
P_END_DATE => l_prev_employer.end_date,
P_EMPLOYER_ADDRESS => l_prev_employer.employer_address,
P_EMPLOYER_COUNTRY => l_prev_employer.employer_country,
P_PEM_INFORMATION_CATEGORY => 'JP',
P_PEM_INFORMATION1 => l_prev_job.employer_name_kana,
P_PEM_INFORMATION2 => l_prev_job.employer_address_kana,
P_PEM_INFORMATION3 => fnd_number.number_to_canonical(l_prev_job.employment_income),
P_PEM_INFORMATION4 => fnd_number.number_to_canonical(l_prev_job.si_prems),
P_PEM_INFORMATION5 => fnd_number.number_to_canonical(l_prev_job.mutual_aid_prem),
P_PEM_INFORMATION6 => fnd_number.number_to_canonical(l_prev_job.withholding_tax),
P_OBJECT_VERSION_NUMBER => l_prev_employer.object_version_number);
hr_utility.trace('Inserting PER_PREVIOUS_EMPLOYERS...');
select 'Y'
from dual
where exists(
select /*+ ORDERED
NO_MERGE(BG)
USE_NL(PEL PEE)
INDEX(PEL PAY_ELEMENT_LINKS_F_FK11)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_N4) */
null
from per_business_groups_perf bg,
pay_element_links_f pel,
pay_element_entries_f pee
where bg.legislation_code = p_legislation_code
and pel.business_group_id = bg.business_group_id
and pee.element_link_id = pel.element_link_id)
or exists(
select /*+ ORDERED
NO_MERGE(BG)
USE_NL(PPA PAA PRR)
INDEX(PPA PAY_PAYROLL_ACTIONS_FK1)
INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50)
INDEX(PRR PAY_RUN_RESULTS_N50) */
null
from per_business_groups_perf bg,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr
where bg.legislation_code = p_legislation_code
and ppa.business_group_id = bg.business_group_id
and paa.payroll_action_id = ppa.payroll_action_id
and prr.assignment_action_id = paa.assignment_action_id);
select 'Y'
from dual
where exists(
select /*+ ORDERED
USE_NL(PEE)
INDEX(PEL PAY_ELEMENT_LINKS_F_N7)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_N4) */
null
from pay_element_links_f pel,
pay_element_entries_f pee
where pel.element_type_id = p_element_type_id
and pee.element_link_id = pel.element_link_id)
or exists(
select null
from pay_run_results prr
where prr.element_type_id = p_element_type_id);
select 'Y'
from dual
where exists(
select null -- Removed the Hint Bug# 4674234
from pay_element_entries_f pee
where pee.assignment_id = p_assignment_id)
or exists(
select null
from pay_assignment_actions paa,
pay_run_results prr
where paa.assignment_id = p_assignment_id
and prr.assignment_action_id = paa.assignment_action_id);
select 'Y'
from dual
where exists(
select /*+ ORDERED
USE_NL(PEE)
INDEX(PEL PAY_ELEMENT_LINKS_F_N7)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
null
from pay_element_links_f pel,
pay_element_entries_f pee
where pel.element_type_id = p_element_type_id
and pee.assignment_id = p_assignment_id
and pee.element_link_id = pel.element_link_id)
or exists(
-- Removed Hint on PAA as per Bug# 4674234.
select /*+ ORDERED
USE_NL(PRR)
INDEX(PRR PAY_RUN_RESULTS_N50) */
null
from pay_assignment_actions paa,
pay_run_results prr
where paa.assignment_id = p_assignment_id
and prr.assignment_action_id = paa.assignment_action_id
and prr.element_type_id = p_element_type_id);
select pel.element_link_id,
piv.input_value_id,
min(pel.effective_start_date) effective_start_date,
max(pel.effective_end_date) effective_end_date
from pay_element_links_f pel,
pay_input_values_f piv
where pel.element_type_id = p_element_type_id
and piv.element_type_id = pel.element_type_id
and pel.effective_start_date
between piv.effective_start_date and piv.effective_end_date
and not exists(
select null
from pay_link_input_values_f pliv
where pliv.element_link_id = pel.element_link_id
and pliv.input_value_id = piv.input_value_id)
group by pel.element_link_id, piv.input_value_id;
insert into pay_element_entry_values_f(
ELEMENT_ENTRY_VALUE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
INPUT_VALUE_ID,
ELEMENT_ENTRY_ID,
SCREEN_ENTRY_VALUE)
select /*+ ORDERED
USE_NL(PEL PLIV PIV)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_N50)
INDEX(PEL PAY_ELEMENT_LINKS_F_PK)
INDEX(PLIV PAY_LINK_INPUT_VALUES_F_U50)
INDEX(PIV PAY_INPUT_VALUES_F_PK) */
pay_element_entry_values_s.nextval,
pee.effective_start_date,
pee.effective_end_date,
pliv.input_value_id,
pee.element_entry_id,
decode(piv.hot_default_flag, 'Y', null, nvl(pliv.default_value, piv.default_value))
from pay_element_entries_f pee,
pay_element_links_f pel,
pay_link_input_values_f pliv,
pay_input_values_f piv
where pee.assignment_id = p_assignment_id
and pel.element_link_id = pee.element_link_id
and pee.effective_start_date
between pel.effective_start_date and pel.effective_end_date
and pel.element_type_id = p_element_type_id
and pliv.element_link_id = pel.element_link_id
and pee.effective_start_date
between pliv.effective_start_date and pliv.effective_end_date
and piv.input_value_id = pliv.input_value_id
and pee.effective_start_date
between piv.effective_start_date and piv.effective_end_date
and not exists(
/* If required, add hint in the following sql. */
select null
from pay_element_entry_values_f peev
where peev.element_entry_id = pee.element_entry_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
and peev.input_value_id = piv.input_value_id);
hr_utility.trace(sql%rowcount || ' rows inserted into pay_element_entry_values_f');
select distinct
business_group_id
into l_business_group_id
from per_all_assignments_f
where assignment_id = p_assignment_id;
insert into pay_run_result_values(
INPUT_VALUE_ID,
RUN_RESULT_ID,
RESULT_VALUE)
select /*+ INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(PRR PAY_RUN_RESULTS_N50)
INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
INDEX(PIV PAY_INPUT_VALUES_F_N50) */
piv.input_value_id,
prr.run_result_id,
null
from pay_assignment_actions paa,
pay_run_results prr,
pay_payroll_actions ppa,
pay_input_values_f piv
where paa.assignment_id = p_assignment_id
and prr.assignment_action_id = paa.assignment_action_id
and prr.element_type_id = p_element_type_id
and ppa.payroll_action_id = paa.payroll_action_id
and piv.element_type_id = prr.element_type_id
and ppa.effective_date
between piv.effective_start_date and piv.effective_end_date
and not exists(
/* If required, add hint in the following sql. */
select null
from pay_run_result_values prrv
where prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = piv.input_value_id);
hr_utility.trace(sql%rowcount || ' rows inserted into pay_run_result_values');
select distinct
element_type_id
from pay_element_types_f
where element_name = p_element_name
and business_group_id is null
and legislation_code = 'JP';
select 'TRUE'
from pay_jp_pre_tax_old ppt,
pay_assignment_actions paa
where paa.action_status = 'C'
and ppt.assignment_action_id = paa.assignment_action_id
and ppt.assignment_action_id NOT IN (
select action_information1
from pay_action_information
where action_information_category = 'JP_PRE_TAX_1'
and action_context_type = 'AAP')
and rownum =1;
select 'Y'
from pay_jp_pre_tax_old ppt,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and ppt.assignment_action_id = paa.assignment_action_id
and ppt.assignment_action_id NOT IN (
select action_information1
from pay_action_information
where action_information_category = 'JP_PRE_TAX_1'
and action_context_type = 'AAP')
and rownum = 1;
select ppt.*, ppa.effective_date
from pay_jp_pre_tax_old ppt,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = p_assignment_id
and paa.assignment_action_id = ppt.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and not exists ( select 1
from pay_action_information pai
where ppt.assignment_action_id = pai.action_information1
and paa.assignment_id = pai.assignment_id
and pai.action_information_category = 'JP_PRE_TAX_1'
and pai.action_context_type = 'AAP'
)
for update of ppt.pre_tax_id;
select asg.person_id,
asg.period_of_service_id,
pds.date_start,
pds.leaving_reason,
pds.actual_termination_date,
asg.employment_category
into l_person_id,
l_period_of_service_id,
l_date_start,
l_leaving_reason,
l_actual_termination_date,
l_employment_category
from per_all_assignments_f asg,
per_periods_of_service pds
where asg.assignment_id = p_assignment_id
and l_effective_date between asg.effective_start_date and asg.effective_end_date
and pds.period_of_service_id = asg.period_of_service_id;
select payroll_action_id,
effective_date,
legislative_parameters
from pay_payroll_actions ppa
where ppa.action_type = 'X'
and ppa.report_type = 'JPTW'
and ppa.report_qualifier = 'JP'
and ppa.report_category = 'ARCHIVE'
-- and ppa.action_status <> 'P'
and pay_core_utils.get_parameter('INCLUDE_TERMINATED_FLAG', legislative_parameters) is null;
select null
from pay_action_information pai
where pai.action_context_id = ppa.payroll_action_id
and pai.action_context_type = 'PA'
and pai.action_information_category = 'JP_ITAX_PACT');
update pay_payroll_actions
set legislative_parameters = l_legislative_parameters
where payroll_action_id = l_pact.payroll_action_id;
select 'Y'
from dual
where exists(
select null
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type = 'X'
and ppa.report_type = 'JPTW'
and ppa.report_qualifier = 'JP'
and ppa.report_category = 'ARCHIVE'
and pay_core_utils.get_parameter('UPGRADE_FLAG', ppa.legislative_parameters) = 'Y');
select person.rowid person_rowid,
arch.rowid arch_rowid,
arch.action_information16 ||
arch.action_information17 ||
arch.action_information18 ||
arch.action_information19 ||
arch.action_information20 ITW_USER_DESC_KANJI,
arch.action_information21 ||
arch.action_information22 ||
arch.action_information23 ||
arch.action_information24 ||
arch.action_information25 WTM_USER_DESC,
other2.rowid other2_rowid,
other2.action_information13 ITW_OVERRIDE_FLAG,
other2.action_information14 WTM_OVERRIDE_FLAG,
other2.ACTION_INFORMATION23 ITW_SYSTEM_DESC1_KANJI,
other2.ACTION_INFORMATION15 ITW_SYSTEM_DESC2_KANJI_1,
other2.ACTION_INFORMATION16 ITW_SYSTEM_DESC2_KANJI_2,
other2.ACTION_INFORMATION19 WTM_SYSTEM_DESC_KANJI_1,
other2.ACTION_INFORMATION20 WTM_SYSTEM_DESC_KANJI_2,
other2.ACTION_INFORMATION21 WTM_SYSTEM_DESC_KANA_1,
other2.ACTION_INFORMATION22 WTM_SYSTEM_DESC_KANA_2
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_information person,
pay_action_information arch,
pay_action_information other2
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type = 'X'
and ppa.report_type = 'JPTW'
and ppa.report_qualifier = 'JP'
and ppa.report_category = 'ARCHIVE'
and pay_core_utils.get_parameter('UPGRADE_FLAG', ppa.legislative_parameters) = 'Y'
and person.action_context_id = paa.assignment_action_id
and person.action_context_type = 'AAP'
and person.action_information_category = 'JP_ITAX_PERSON'
and arch.action_context_id = person.action_context_id
and arch.action_context_type = 'AAP'
and arch.action_information_category = 'JP_ITAX_ARCH'
and arch.effective_date = person.effective_date
and other2.action_context_id = person.action_context_id
and other2.action_context_type = 'AAP'
and other2.action_information_category = 'JP_ITAX_OTHER2'
and other2.effective_date = person.effective_date
for update of
person.action_information_id,
other2.action_information_id nowait;
update pay_action_information
set action_information11 = to_canonical_date(action_information11),
action_information13 = to_canonical_date(action_information13),
action_information25 = to_canonical_date(action_information25),
-- action_information27 = 'O'
action_information27 = null
where rowid = l_rec.person_rowid;
update pay_action_information
set action_information12 = to_canonical_date(action_information12)
where rowid = l_rec.arch_rowid;
update pay_action_information
set ACTION_INFORMATION23 = l_rec.ITW_SYSTEM_DESC1_KANJI,
ACTION_INFORMATION15 = l_rec.ITW_SYSTEM_DESC2_KANJI_1,
ACTION_INFORMATION16 = l_rec.ITW_SYSTEM_DESC2_KANJI_2,
ACTION_INFORMATION19 = l_rec.WTM_SYSTEM_DESC_KANJI_1,
ACTION_INFORMATION20 = l_rec.WTM_SYSTEM_DESC_KANJI_2,
ACTION_INFORMATION21 = l_rec.WTM_SYSTEM_DESC_KANA_1,
ACTION_INFORMATION22 = l_rec.WTM_SYSTEM_DESC_KANA_2,
action_information25 = l_itw_user_desc_kanji1,
action_information26 = l_itw_user_desc_kanji2,
action_information27 = l_wtm_user_desc_kanji1,
action_information28 = l_wtm_user_desc_kanji2,
action_information29 = l_wtm_user_desc_kana1,
action_information30 = l_wtm_user_desc_kana2
where rowid = l_rec.other2_rowid;
l_valid_delete varchar2(1) := 'N';
p_valid_delete => l_valid_delete);
hr_utility.trace('l_valid_delete : '||l_valid_delete);
p_qualifier := l_valid_delete;
hr_jp_data_migration_pkg.insert_session(
p_effective_date => hr_jp_data_migration_pkg.g_mig_date);
select upgrade_definition_id,
upgrade_method,
upgrade_level,
legislation_code,
legislatively_enabled
from pay_upgrade_definitions
where short_name = p_upgrade_short_name;
select 'Y'
from pay_upgrade_legislations
where upgrade_definition_id = p_upgrade_definition_id
and legislation_code = p_legislation_code;
select status
from pay_upgrade_status
where upgrade_definition_id = p_upgrade_definition_id
and business_group_id is null
and legislation_code = p_legislation_code;
select organization_id
from hr_organization_information
where org_information_context = 'Business Group Information'
and org_information9 = p_legislation_code
and rownum <= 1;
select g.user_id,
g.responsibility_id,
g.responsibility_application_id
from fnd_responsibility r,
fnd_user_resp_groups g,
fnd_user u
where u.user_name = 'SYSADMIN'
and g.user_id = u.user_id
and g.security_group_id = 0
and r.application_id = g.responsibility_application_id
and r.responsibility_id = g.responsibility_id
and r.responsibility_key = 'SYSTEM_ADMINISTRATOR';
-- Make sure the data update has not been performed already.
-- pay_core_utils.get_upgrade_status cannot be used for
-- legislative upgrade which requires business_group_id as mandatory parameter.
-- There's possibility that business groups within "p_legislation_code" are not created yet at this point.
--
open csr_upgrade_status(l_upgrade_def.upgrade_definition_id);