[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hjp1.parameter_value iv_mode,
hjp2.parameter_name iv_name,
hjp2.parameter_value iv_id_to,
hjp3.parameter_value iv_id_from
from hr_jp_parameters hjp1,
hr_jp_parameters hjp2,
hr_jp_parameters hjp3
where hjp1.owner = p_parameter_name
and hjp2.owner = hjp1.parameter_value
and hjp2.parameter_name = hjp1.parameter_name
and hjp3.owner(+) = 'IV_COPY_FROM'
and hjp3.parameter_name(+) = hjp2.parameter_name;
insert into pay_run_results (
RUN_RESULT_ID,
ELEMENT_TYPE_ID,
ASSIGNMENT_ACTION_ID,
ENTRY_TYPE,
SOURCE_ID,
SOURCE_TYPE,
STATUS)
select /*+ INDEX(PRR_FROM PAY_RUN_RESULTS_N1) */
pay_run_results_s.nextval,
l_element_type_id_to,
prr_from.assignment_action_id,
prr_from.entry_type,
prr_from.source_id,
prr_from.source_type,
prr_from.status
from pay_run_results prr_from
where prr_from.element_type_id = l_element_type_id_from
and not exists(
select /*+ INDEX(PRR_TO PAY_RUN_RESULTS_N50) */
NULL
from pay_run_results prr_to
where prr_to.assignment_action_id = prr_from.assignment_action_id
and prr_to.element_type_id = l_element_type_id_to);
insert into pay_run_result_values (
INPUT_VALUE_ID,
RUN_RESULT_ID,
RESULT_VALUE)
select /*+ ORDERED
INDEX(FROM_ELE_PRR PAY_RUN_RESULTS_N1)
INDEX(PRRV_FROM PAY_RUN_RESULT_VALUES_PK)
INDEX(TO_ELE_PRR PAY_RUN_RESULTS_N50)
USE_NL(prrv_from to_ele_prr) */
rec_related_iv.iv_id_to,
to_ele_prr.run_result_id,
prrv_from.result_value
from pay_run_results from_ele_prr,
pay_run_result_values prrv_from,
pay_run_results to_ele_prr
where from_ele_prr.element_type_id = l_element_type_id_from
and prrv_from.run_result_id = from_ele_prr.run_result_id
and prrv_from.input_value_id = rec_related_iv.iv_id_from
and to_ele_prr.assignment_action_id = from_ele_prr.assignment_action_id
and to_ele_prr.element_type_id = l_element_type_id_to
and not exists(
select NULL
from pay_run_result_values prrv_to
where prrv_to.run_result_id = to_ele_prr.run_result_id
and prrv_to.input_value_id = rec_related_iv.iv_id_to);
insert into pay_run_result_values (
INPUT_VALUE_ID,
RUN_RESULT_ID,
RESULT_VALUE)
select /*+ INDEX(TO_ELE_PRR PAY_RUN_RESULTS_N1) */
rec_related_iv.iv_id_to,
to_ele_prr.run_result_id,
NULL
from pay_run_results to_ele_prr
where to_ele_prr.element_type_id = l_element_type_id_to
and not exists(
select NULL
from pay_run_result_values prrv_to
where prrv_to.run_result_id = to_ele_prr.run_result_id
and prrv_to.input_value_id = rec_related_iv.iv_id_to);
select /*+ ORDERED
INDEX(PIV PAY_INPUT_VALUES_F_PK)
INDEX(PET PAY_ELEMENT_TYPES_F_PK)
INDEX(PRR PAY_RUN_RESULTS_N1) */
prr.run_result_id run_result_id
from pay_input_values_f piv,
pay_element_types_f pet,
pay_run_results prr
where piv.input_value_id = p_parameter_value
and pet.element_type_id = piv.element_type_id
and piv.effective_start_date
between pet.effective_start_date and pet.effective_end_date
and prr.element_type_id = pet.element_type_id
and not exists(
select /*+ INDEX(PRRV PAY_RUN_RESULT_VALUES_PK) */
NULL
from pay_run_result_values prrv
where prrv.run_result_id=prr.run_result_id
and prrv.input_value_id=l_input_value_id_to);
select /*+ ORDERED
INDEX(PIV PAY_INPUT_VALUES_F_PK)
INDEX(PET PAY_ELEMENT_TYPES_F_PK)
INDEX(PEL PAY_ELEMENT_LINKS_F_N7) */
pel.rowid row_id,
pel.element_link_id element_link_id,
pel.costable_type costable_type,
piv.name input_value_name,
piv.effective_start_date effective_start_date,
piv.effective_end_date effective_end_date,
piv.default_value default_value,
piv.max_value max_value,
piv.min_value min_value,
piv.warning_or_error warning_or_error
from pay_input_values_f piv,
pay_element_types_f pet,
pay_element_links_f pel
where piv.input_value_id = p_parameter_value
and pet.element_type_id = piv.element_type_id
and piv.effective_start_date
between pet.effective_start_date and pet.effective_end_date
and pel.element_type_id = pet.element_type_id
and pel.effective_start_date <= piv.effective_end_date
and pel.effective_end_date >= piv.effective_start_date;
select /*+ INDEX(PEE PAY_ELEMENT_ENTRIES_F_N4) */
pee.rowid row_id,
pee.element_entry_id element_entry_id
from pay_element_entries_f pee
where pee.element_link_id = p_element_link_id
and not exists(select /*+ INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) */
NULL
from pay_element_entry_values_f peev
where peev.element_entry_id = pee.element_entry_id
and peev.input_value_id = p_input_value_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date);
insert into pay_link_input_values_f
(LINK_INPUT_VALUE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ELEMENT_LINK_ID,
INPUT_VALUE_ID,
COSTED_FLAG,
DEFAULT_VALUE,
MAX_VALUE,
MIN_VALUE,
WARNING_OR_ERROR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
select PAY_LINK_INPUT_VALUES_S.nextval,
greatest(pel.effective_start_date,rec_element_link.effective_start_date),
least(pel.effective_end_date,rec_element_link.effective_end_date),
rec_element_link.element_link_id,
l_input_value_id_to,
l_costed_flag,
rec_element_link.default_value,
rec_element_link.max_value,
rec_element_link.min_value,
rec_element_link.warning_or_error,
pel.last_update_date,
pel.last_updated_by,
pel.last_update_login,
NULL,
pel.creation_date
from pay_element_links_f pel
where pel.rowid=rec_element_link.row_id
and not exists(
select null
from pay_link_input_values_f
where element_link_id = rec_element_link.element_link_id
and input_value_id = l_input_value_id_to);
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 pay_element_entry_values_s.nextval,
pee.effective_start_date,
pee.effective_end_date,
l_input_value_id_to,
pee.element_entry_id,
NULL
from pay_element_entries_f pee
where pee.rowid=rec_element_entry.row_id;
insert into pay_run_result_values (
INPUT_VALUE_ID,
RUN_RESULT_ID,
RESULT_VALUE)
values(l_input_value_id_to,
rec_run_result_id.run_result_id,
NULL);
l_delete_mode VARCHAR2(10);
select element_link_id
from pay_element_links_f
where element_type_id=l_element_type_id;
select pee.element_entry_id,
pee.effective_start_date
from pay_element_entries_f pee
where pee.element_link_id=p_element_link_id
and p_session_date
select pee.element_entry_id
from pay_element_entries_f pee
where pee.element_entry_id=p_element_entry_id
and p_session_date
between pee.effective_start_date and pee.effective_end_date;
l_delete_mode := 'ZAP';
l_delete_mode := 'DELETE';
hr_entry_api.delete_element_entry(l_delete_mode,l_target_date,rec_element_entry.element_entry_id);
select /*+ ORDERED
INDEX(PA PER_ASSIGNMENTS_F_PK) */
pbg.business_group_id bg_id,
pbg.name bg_name,
pa.assignment_number ass_num
from per_all_assignments_f pa,
per_business_groups_perf pbg
where pa.assignment_id = p_assignment_id
and p_effective_date
between pa.effective_start_date and pa.effective_end_date
and pbg.business_group_id = pa.business_group_id;
procedure insert_session(
p_effective_date in date)
is
--
l_rowid rowid;
select rowid
from fnd_sessions
where session_id = userenv('sessionid')
for update nowait;
insert into fnd_sessions(
session_id,
effective_date)
values(
userenv('sessionid'),
p_effective_date);
update fnd_sessions
set effective_date = p_effective_date
where rowid = l_rowid;
end insert_session;
procedure delete_session
is
begin
--
delete
from fnd_sessions
where session_id = userenv('sessionid');
end delete_session;
g_upd_mode := 'UPDATE';
p_valid_delete in out nocopy varchar2)
is
--
l_proc varchar2(80) := c_package||'val_mig_smr_assact';
l_valid_delete varchar2(1) := 'N';
select /*+ ORDERED
USE_NL(PLIV, PEE)
INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
pee.effective_start_date,
pee.updating_action_id
from pay_link_input_values_f pliv,
pay_element_entries_f pee
where pliv.input_value_id = c_smr_iv_id
and p_session_date
between pliv.effective_start_date and pliv.effective_end_date
and pee.element_link_id = pliv.element_link_id
and pee.assignment_id = p_assignment_id
and p_session_date
between pee.effective_start_date and pee.effective_end_date;
select /*+ ORDERED
USE_NL(PLIV, PEE)
INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
pee.effective_start_date
from pay_link_input_values_f pliv,
pay_element_entries_f pee
where pliv.input_value_id = c_smr_iv_id
and p_session_date
between pliv.effective_start_date and pliv.effective_end_date
and pee.element_link_id = pliv.element_link_id
and pee.assignment_id = p_assignment_id
and pee.effective_start_date > p_session_date;
if p_valid_delete = 'N' then
--
-- skip ee not exist.
-- skip already updated (manual update)
--
open csr_ee_esd;
l_valid_delete := 'Y';
l_valid_delete := 'N';
hr_utility.trace('skip manual upd : l_valid_delete : '||l_valid_delete);
if l_valid_delete = 'Y' then
--
open csr_ft_ee;
l_valid_delete := 'N';
hr_utility.trace('skip future entry : l_valid_delete : '||l_valid_delete);
if l_valid_delete = 'Y'
and g_upd_mode <> 'OVERRIDE' then
--
l_am_eev := pay_jp_balance_pkg.get_entry_value_char(
p_input_value_id => c_am_iv_id,
p_assignment_id => p_assignment_id,
p_effective_date => p_session_date);
l_valid_delete := 'N';
l_valid_delete := 'N';
hr_utility.trace('skip applied month in future : l_valid_delete : '||l_valid_delete);
if l_valid_delete = 'Y' then
--
l_mr_eev := pay_jp_balance_pkg.get_entry_value_char(
p_input_value_id => c_mr_iv_id,
p_assignment_id => p_assignment_id,
p_effective_date => p_session_date);
l_valid_delete := 'N';
l_valid_delete := 'N';
hr_utility.trace('skip mr is null or out range : l_valid_delete : '||l_valid_delete);
if l_valid_delete = 'Y' then
--
p_valid_delete := 'Y';
select /*+ ORDERED
USE_NL(PEL, PEE, PEEV)
INDEX(PEL PAY_ELEMENT_LINKS_F_N7)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51)
INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) */
pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date,
pee.object_version_number,
peev.input_value_id,
peev.screen_entry_value
from pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f peev
where pel.element_type_id = c_com_hi_smr_elm_id
and pel.business_group_id + 0 = p_business_group_id
and p_session_date
between pel.effective_start_date and pel.effective_end_date
and pee.assignment_id = p_assignment_id
and pee.element_link_id = pel.element_link_id
and p_session_date
between pee.effective_start_date and pee.effective_end_date
and pee.entry_type = 'E'
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
for update of peev.element_entry_value_id nowait;
pay_element_entry_api.update_element_entry(
p_validate => false,
p_effective_date => p_session_date,
p_business_group_id => p_business_group_id,
p_datetrack_update_mode => 'UPDATE',
p_element_entry_id => l_csr_entry.element_entry_id,
p_object_version_number => l_ovn,
p_input_value_id1 => c_am_iv_id,
p_input_value_id2 => c_smr_iv_id,
p_input_value_id3 => c_smr_o_iv_id,
p_input_value_id4 => c_at_iv_id,
p_input_value_id5 => c_mr_iv_id,
p_input_value_id6 => c_mr_o_iv_id,
p_entry_value1 => to_char(p_session_date,'YYYYMM'),
p_entry_value2 => l_exp_smr_eev,
p_entry_value3 => l_exp_smr_o_eev,
p_entry_value4 => 'O',
p_entry_value5 => l_exp_mr_eev,
p_entry_value6 => l_exp_mr_o_eev,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_update_warning => l_warning);
l_qualify_valid_update varchar2(1) := 'N';
select /*+ ORDERED
INDEX(PA PER_ASSIGNMENTS_F_FK1) */
pbg.business_group_id,
pbg.name bg_name,
pa.assignment_id,
pa.assignment_number
from per_business_groups_perf pbg,
per_all_assignments_f pa
where pbg.legislation_code = g_legislation_code
and pa.business_group_id = pbg.business_group_id
and pa.effective_start_date = (
select /*+ INDEX(PA2 PER_ASSIGNMENTS_F_PK) */
max(pa2.effective_start_date)
from per_all_assignments_f pa2
where pa2.assignment_id = pa.assignment_id);
g_upd_mode := 'UPDATE';
g_range_ass_hi_smr_tbl.delete;
g_qualify_hi_smr_ass_tbl.delete;
insert_session(g_mig_date);
p_valid_delete => g_range_ass_hi_smr_tbl(j).del_done);
if l_qualify_valid_update = 'N'
and g_range_ass_hi_smr_tbl(j).del_done = 'Y' then
l_qualify_valid_update := 'Y';
l_qualify_valid_update := 'Y';
if l_qualify_valid_update = 'Y' then
--
if g_range_ass_hi_smr_tbl.count > 0 then
--
for k in 0..g_range_ass_hi_smr_tbl.count - 1 loop
--
if g_range_ass_hi_smr_tbl(k).del_done = 'Y'
or g_skip_qualify = 'Y' then
--
g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).bg_id := g_range_ass_hi_smr_tbl(k).bg_id;
if l_qualify_valid_update = 'Y' then
--
if g_qualify_hi_smr_ass_tbl.count > 0 then
--
for m in 0..g_qualify_hi_smr_ass_tbl.count - 1 loop
--
mig_smr_assact(
p_business_group_id => g_qualify_hi_smr_ass_tbl(m).bg_id,
p_business_group_name => g_qualify_hi_smr_ass_tbl(m).bg_name,
p_assignment_id => g_qualify_hi_smr_ass_tbl(m).ass_id,
p_assignment_number => g_qualify_hi_smr_ass_tbl(m).ass_num,
p_session_date => g_mig_date,
p_hi_mr => g_qualify_hi_smr_ass_tbl(m).hi_mr);
delete_session;
if l_qualify_valid_update = 'Y' then
--
commit;
p_valid_update in out nocopy varchar2)
is
--
l_proc varchar2(80) := c_package||'val_mig_dep_assact';
l_valid_update varchar2(1) := 'N';
select /*+ ORDERED */
pp_c.person_id
from per_all_assignments_f pa,
per_contact_relationships pcr,
per_contact_extra_info_f pcei,
per_all_people_f pp_c
where pa.assignment_id = p_assignment_id
and p_session_date
between pa.effective_start_date and pa.effective_end_date
and pcr.person_id = pa.person_id
and pcei.contact_relationship_id = pcr.contact_relationship_id
and pcei.cei_information_category = 'JP_ITAX_DEPENDENT'
and p_session_date
between pcei.effective_start_date and pcei.effective_end_date
and pp_c.person_id = pcr.contact_person_id
and p_session_date
between pp_c.effective_start_date and pp_c.effective_end_date
and ((nvl(trunc(months_between(
to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
pp_c.date_of_birth) / 12),16) < 16
and nvl(pcei.cei_information8,'0') = '0')
or (nvl(trunc(months_between(
to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
pp_c.date_of_birth) / 12),0)
between 16 and 18
and nvl(pcei.cei_information8,'10') = '10'));
select /*+ ORDERED */
pp_c.person_id
from per_all_assignments_f pa,
per_contact_relationships pcr,
per_contact_extra_info_f pcei,
per_all_people_f pp_c
where pa.assignment_id = p_assignment_id
and p_session_date
between pa.effective_start_date and pa.effective_end_date
and pcr.person_id = pa.person_id
and pcei.contact_relationship_id = pcr.contact_relationship_id
and pcei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_EMP'
and p_session_date
between pcei.effective_start_date and pcei.effective_end_date
and pp_c.person_id = pcr.contact_person_id
and p_session_date
between pp_c.effective_start_date and pp_c.effective_end_date
and nvl(trunc(months_between(
to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
pp_c.date_of_birth) / 12),16) < 16;
select /*+ ORDERED */
pcei.contact_extra_info_id
from per_all_assignments_f pa,
per_contact_relationships pcr,
per_contact_extra_info_f pcei
where pa.assignment_id = p_assignment_id
and p_session_date
between pa.effective_start_date and pa.effective_end_date
and pcr.person_id = pa.person_id
and pcei.contact_relationship_id = pcr.contact_relationship_id
and pcei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_PAY'
and p_session_date
between pcei.effective_start_date and pcei.effective_end_date;
select /*+ ORDERED */
pcei.contact_extra_info_id
from per_all_assignments_f pa,
per_contact_relationships pcr,
per_contact_extra_info_f pcei,
per_all_people_f pp_c
where pa.assignment_id = p_assignment_id
and p_session_date
between pa.effective_start_date and pa.effective_end_date
and pcr.person_id = pa.person_id
and pcei.contact_relationship_id = pcr.contact_relationship_id
and pcei.cei_information_category = 'JP_ITAX_DEPENDENT'
and pcei.effective_start_date > p_session_date
and pp_c.person_id = pcr.contact_person_id
and p_session_date
between pp_c.effective_start_date and pp_c.effective_end_date
and nvl(trunc(months_between(
to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
pp_c.date_of_birth) / 12),16) < 16;
select /*+ ORDERED */
pcei.contact_extra_info_id
from per_all_assignments_f pa,
per_contact_relationships pcr,
per_contact_extra_info_f pcei,
per_all_people_f pp_c
where pa.assignment_id = p_assignment_id
and p_session_date
between pa.effective_start_date and pa.effective_end_date
and pcr.person_id = pa.person_id
and pcei.contact_relationship_id = pcr.contact_relationship_id
and pcei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_EMP'
and pcei.effective_start_date > p_session_date
and pp_c.person_id = pcr.contact_person_id
and p_session_date
between pp_c.effective_start_date and pp_c.effective_end_date
and nvl(trunc(months_between(
to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
pp_c.date_of_birth) / 12),16) < 16;
select /*+ ORDERED
USE_NL(PLIV, PEE)
INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
pee.effective_start_date
-- pee.updating_action_id
from pay_link_input_values_f pliv,
pay_element_entries_f pee
where pliv.input_value_id = c_sec_sal_iv_id
and p_session_date
between pliv.effective_start_date and pliv.effective_end_date
and pee.element_link_id = pliv.element_link_id
and pee.assignment_id = p_assignment_id
and p_session_date
between pee.effective_start_date and pee.effective_end_date;
select /*+ ORDERED
USE_NL(PLIV, PEE)
INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
pee.effective_start_date
from pay_link_input_values_f pliv,
pay_element_entries_f pee
where pliv.input_value_id = c_sec_sal_iv_id
and p_session_date
between pliv.effective_start_date and pliv.effective_end_date
and pee.element_link_id = pliv.element_link_id
and pee.assignment_id = p_assignment_id
and pee.effective_start_date > p_session_date;
if p_valid_update = 'N' then
--
-- set valid update when the at least one exist either cei target, cei oe target or ee target
--
-- target for cei update
--
open csr_cei_target;
l_valid_update := 'Y';
l_valid_update := 'Y';
l_valid_update := 'Y';
if l_valid_update = 'Y' then
--
open csr_ft_cei;
l_valid_update := 'N';
hr_utility.trace('skip future cei : l_valid_update : '||l_valid_update);
if l_valid_update = 'Y' then
--
open csr_ft_cei_oe;
l_valid_update := 'N';
hr_utility.trace('skip future cei : l_valid_update : '||l_valid_update);
if l_valid_update = 'Y' then
--
open csr_ee;
l_valid_update := 'N';
hr_utility.trace('skip manual upd entry : l_valid_update : '||l_valid_update);
if l_valid_update = 'Y' then
--
open csr_ft_ee;
l_valid_update := 'N';
hr_utility.trace('skip future entry : l_valid_update : '||l_valid_update);
if l_valid_update = 'Y' then
--
p_valid_update := 'Y';
select /*+ ORDERED */
pcei.contact_extra_info_id,
pcr.contact_relationship_id,
pcei.object_version_number,
pcei.cei_information8 dep_type,
pp_c.last_name||' '||pp_c.first_name c_full_name,
trunc(months_between(
to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
pp_c.date_of_birth) / 12) c_age
from per_all_assignments_f pa,
per_contact_relationships pcr,
per_contact_extra_info_f pcei,
per_all_people_f pp_c
where pa.assignment_id = p_assignment_id
and p_session_date
between pa.effective_start_date and pa.effective_end_date
and pcr.person_id = pa.person_id
and pcei.contact_relationship_id = pcr.contact_relationship_id
and pcei.cei_information_category = 'JP_ITAX_DEPENDENT'
and p_session_date
between pcei.effective_start_date and pcei.effective_end_date
and pp_c.person_id = pcr.contact_person_id
and p_session_date
between pp_c.effective_start_date and pp_c.effective_end_date
and ((nvl(trunc(months_between(
to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
pp_c.date_of_birth) / 12),16) < 16
and nvl(pcei.cei_information8,'0') = '0')
or (nvl(trunc(months_between(
to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
pp_c.date_of_birth) / 12),0)
between 16 and 18
and nvl(pcei.cei_information8,'10') = '10'))
for update of pcei.contact_extra_info_id nowait;
select /*+ ORDERED */
pcei.contact_extra_info_id,
pcr.contact_relationship_id,
pcei.object_version_number,
pp_c.last_name||' '||pp_c.first_name c_full_name,
trunc(months_between(
to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
pp_c.date_of_birth) / 12) c_age
from per_all_assignments_f pa,
per_contact_relationships pcr,
per_contact_extra_info_f pcei,
per_all_people_f pp_c
where pa.assignment_id = p_assignment_id
and p_session_date
between pa.effective_start_date and pa.effective_end_date
and pcr.person_id = pa.person_id
and pcei.contact_relationship_id = pcr.contact_relationship_id
and pcei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_EMP'
and p_session_date
between pcei.effective_start_date and pcei.effective_end_date
and pp_c.person_id = pcr.contact_person_id
and p_session_date
between pp_c.effective_start_date and pp_c.effective_end_date
and nvl(trunc(months_between(
to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
pp_c.date_of_birth) / 12),16) < 16
for update of pcei.contact_extra_info_id nowait;
select /*+ ORDERED */
pcei.contact_extra_info_id
from per_all_assignments_f pa,
per_contact_relationships pcr,
per_contact_extra_info_f pcei
where pa.assignment_id = p_assignment_id
and p_session_date
between pa.effective_start_date and pa.effective_end_date
and pcr.person_id = pa.person_id
and pcei.contact_relationship_id = pcr.contact_relationship_id
and pcei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_PAY'
and p_session_date
between pcei.effective_start_date and pcei.effective_end_date;
select /*+ ORDERED
USE_NL(PLIV, PEE, PEEV)
INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51)
INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) */
pee.element_entry_id,
pee.object_version_number,
peev.screen_entry_value sec_sal
from pay_link_input_values_f pliv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
where pliv.input_value_id = c_sec_sal_iv_id
and p_session_date
between pliv.effective_start_date and pliv.effective_end_date
and pee.element_link_id = pliv.element_link_id
and pee.assignment_id = p_assignment_id
and p_session_date
between pee.effective_start_date and pee.effective_end_date
and pee.entry_type = 'E'
and peev.input_value_id = pliv.input_value_id
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
for update of peev.element_entry_value_id nowait;
hr_contact_extra_info_api.update_contact_extra_info(
p_validate => false,
p_effective_date => p_session_date,
p_datetrack_update_mode => 'UPDATE',
p_contact_extra_info_id => l_csr_cei.contact_extra_info_id,
p_object_version_number => l_ovn,
p_cei_information8 => l_dep_type,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_session_date - 1,
p_datetrack_delete_mode => 'DELETE',
p_contact_extra_info_id => l_csr_cei_oe.contact_extra_info_id,
p_object_version_number => l_ovn,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
l_qualify_valid_update varchar2(1) := 'N';
select /*+ ORDERED
INDEX(PA PER_ASSIGNMENTS_F_FK1) */
pbg.business_group_id,
pbg.name bg_name,
pa.assignment_id,
pa.assignment_number
from per_business_groups_perf pbg,
per_all_assignments_f pa
where pbg.legislation_code = g_legislation_code
and pa.business_group_id = pbg.business_group_id
and pa.effective_start_date = (
select /*+ INDEX(PA2 PER_ASSIGNMENTS_F_PK) */
max(pa2.effective_start_date)
from per_all_assignments_f pa2
where pa2.assignment_id = pa.assignment_id);
g_range_ass_dep_tbl.delete;
g_qualify_dep_ass_tbl.delete;
insert_session(g_mig_date);
p_valid_update => g_range_ass_dep_tbl(j).upd_done);
if l_qualify_valid_update = 'N'
and g_range_ass_dep_tbl(j).upd_done = 'Y' then
l_qualify_valid_update := 'Y';
l_qualify_valid_update := 'Y';
if l_qualify_valid_update = 'Y' then
--
if g_range_ass_dep_tbl.count > 0 then
--
for k in 0..g_range_ass_dep_tbl.count - 1 loop
--
if g_range_ass_dep_tbl(k).upd_done = 'Y'
or g_skip_qualify = 'Y' then
--
g_qualify_dep_ass_tbl(l_qualify_ass_tbl_cnt).bg_id := g_range_ass_dep_tbl(k).bg_id;
if l_qualify_valid_update = 'Y' then
--
if g_qualify_dep_ass_tbl.count > 0 then
--
for m in 0..g_qualify_dep_ass_tbl.count - 1 loop
--
mig_dep_assact(
p_business_group_id => g_qualify_dep_ass_tbl(m).bg_id,
p_business_group_name => g_qualify_dep_ass_tbl(m).bg_name,
p_assignment_id => g_qualify_dep_ass_tbl(m).ass_id,
p_assignment_number => g_qualify_dep_ass_tbl(m).ass_num,
p_session_date => g_mig_date);
delete_session;
if l_qualify_valid_update = 'Y' then
--
commit;