[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select /*+ ORDERED */
pec.classification_name
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_element_types_f pet,
pay_element_classifications pec
where paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and prr.assignment_action_id = paa.assignment_action_id
and pet.element_type_id = prr.element_type_id
and ppa.effective_date
between pet.effective_start_date and pet.effective_end_date
and pec.classification_id = pet.classification_id
and pec.classification_name like '%'||c_fuzzy_ele_class_trm||'%';
select parameter_value
from pay_action_parameters
where parameter_name = 'JP_DEBUG_EMPDET';
select /*+ ORDERED */
ppa.business_group_id,
ppa.effective_date,
ppa.legislative_parameters,
pbg.legislation_code
from pay_payroll_actions ppa,
per_business_groups_perf pbg
where ppa.payroll_action_id = p_payroll_action_id
and pbg.business_group_id = ppa.business_group_id;
select parameter_value
from pay_action_parameters
where parameter_name = 'RANGE_PERSON_ID';
select id_flex_num
from fnd_id_flex_structures
where application_id = 800
and id_flex_structure_code = 'JP_EDUC_BKGRD'
and id_flex_code = 'PEA';
select hoi.org_information4 proc_name
from hr_organization_information hoi
where hoi.organization_id = g_business_group_id
and hoi.org_information_context = 'JP_REPORTS_ADDITIONAL_INFO'
and hoi.org_information1 = 'JPEMPLDETAILSREPORT'
and hoi.org_information3 = 'ADDINFO'
and hoi.org_information4 is not null
and g_effective_date
between nvl(fnd_date.canonical_to_date(hoi.org_information5),hr_api.g_sot) and nvl(fnd_date.canonical_to_date(hoi.org_information6),g_effective_date);
'select distinct ppos.person_id
from pay_payroll_actions ppa,
per_people_f pp,
per_periods_of_service ppos
where ppa.payroll_action_id = :payroll_action_id
and pp.business_group_id = ppa.business_group_id + 0
and ppos.person_id = pp.person_id
and ppos.business_group_id + 0 = pp.business_group_id
and ppos.date_start <= fnd_date.canonical_to_date(''i_effective_date'')
order by
ppos.person_id';
select /*+ ORDERED */
ppr.person_id,
pa.assignment_id,
to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD') effective_date,
null include_or_exclude
from pay_population_ranges ppr,
per_periods_of_service ppos,
per_assignments_f pa
where ppr.payroll_action_id = p_payroll_action_id
and ppr.chunk_number = p_chunk_number
and ppos.person_id = ppr.person_id
and ppos.business_group_id + 0 = g_business_group_id
and ((nvl(g_inc_term_flag,'N') = 'N'
and least(nvl(ppos.actual_termination_date,g_effective_date + 1),g_effective_date + 1) > g_effective_date)
or (nvl(g_inc_term_flag,'N') = 'Y'
and ((g_term_date_from is null
and g_term_date_to is null)
or (ppos.actual_termination_date
between nvl(g_term_date_from,ppos.actual_termination_date) and nvl(g_term_date_to,ppos.actual_termination_date)))))
and pa.period_of_service_id = ppos.period_of_service_id
and pa.primary_flag = 'Y'
and to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
between pa.effective_start_date and pa.effective_end_date
and nvl(pa.location_id,-1) = nvl(g_location_id,nvl(pa.location_id,-1))
and (g_organization_id is null
or (g_organization_id is not null
and nvl(g_inc_org_hier_flag,'Y') = 'Y'
and pa.organization_id in (
select /*+ ORDERED */
distinct pose.organization_id_child
from per_organization_structures pos,
per_org_structure_versions posv,
per_org_structure_elements pose
where pos.business_group_id = decode(g_business_group_id,g_organization_id,-1,g_business_group_id)
and pos.primary_structure_flag = 'Y'
and posv.organization_structure_id = pos.organization_structure_id
and posv.date_from <= g_effective_date
and nvl(posv.date_to,hr_api.g_eot) >= g_effective_date
and not exists(
select null
from per_org_structure_versions posv2
where posv2.organization_structure_id = pos.organization_structure_id
and posv2.date_from <= g_effective_date
and nvl(posv2.date_to,hr_api.g_eot) >= g_effective_date
and posv2.version_number > posv.version_number)
and pose.org_structure_version_id = posv.org_structure_version_id
start with pose.organization_id_parent = g_organization_id
connect by prior pose.organization_id_child = pose.organization_id_parent
union
select hou.organization_id
from hr_all_organization_units hou
where hou.business_group_id = decode(g_business_group_id,g_organization_id,g_business_group_id,-1)
and hou.organization_id <> hou.business_group_id
union
select g_organization_id
from dual))
or (g_organization_id is not null
and nvl(g_inc_org_hier_flag,'Y') = 'N'
and pa.organization_id = g_organization_id))
order by ppr.person_id;
select /*+ ORDERED */
ppr.person_id,
pa.assignment_id,
to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD') effective_date,
hasa.include_or_exclude
from pay_population_ranges ppr,
per_periods_of_service ppos,
per_assignments_f pa,
hr_assignment_set_amendments hasa
where ppr.payroll_action_id = p_payroll_action_id
and ppr.chunk_number = p_chunk_number
and ppos.person_id = ppr.person_id
and ppos.business_group_id + 0 = g_business_group_id
and ((nvl(g_inc_term_flag,'N') = 'N'
and least(nvl(ppos.actual_termination_date,g_effective_date + 1),g_effective_date + 1) > g_effective_date)
or (nvl(g_inc_term_flag,'N') = 'Y'
and ((g_term_date_from is null
and g_term_date_to is null)
or (ppos.actual_termination_date
between nvl(g_term_date_from,ppos.actual_termination_date) and nvl(g_term_date_to,ppos.actual_termination_date)))))
and pa.period_of_service_id = ppos.period_of_service_id
and pa.primary_flag = 'Y'
and to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
between pa.effective_start_date and pa.effective_end_date
and nvl(pa.location_id,-1) = nvl(g_location_id,nvl(pa.location_id,-1))
and (g_organization_id is null
or (g_organization_id is not null
and nvl(g_inc_org_hier_flag,'Y') = 'Y'
and pa.organization_id in (
select /*+ ORDERED */
distinct pose.organization_id_child
from per_organization_structures pos,
per_org_structure_versions posv,
per_org_structure_elements pose
where pos.business_group_id = decode(g_business_group_id,g_organization_id,-1,g_business_group_id)
and pos.primary_structure_flag = 'Y'
and posv.organization_structure_id = pos.organization_structure_id
and posv.date_from <= g_effective_date
and nvl(posv.date_to,hr_api.g_eot) >= g_effective_date
and not exists(
select null
from per_org_structure_versions posv2
where posv2.organization_structure_id = pos.organization_structure_id
and posv2.date_from <= g_effective_date
and nvl(posv2.date_to,hr_api.g_eot) >= g_effective_date
and posv2.version_number > posv.version_number)
and pose.org_structure_version_id = posv.org_structure_version_id
start with pose.organization_id_parent = g_organization_id
connect by prior pose.organization_id_child = pose.organization_id_parent
union
select hou.organization_id
from hr_all_organization_units hou
where hou.business_group_id = decode(g_business_group_id,g_organization_id,g_business_group_id,-1)
and hou.organization_id <> hou.business_group_id
union
select g_organization_id
from dual))
or (g_organization_id is not null
and nvl(g_inc_org_hier_flag,'Y') = 'N'
and pa.organization_id = g_organization_id))
and to_number(decode(nvl(to_char(g_ass_set_formula_id),'-1'),'-1',
decode(nvl(g_ass_set_amendment_type,'X'),'I',hasa.assignment_id,pa.assignment_id),
pa.assignment_id)) = pa.assignment_id
and hasa.assignment_set_id (+) = g_assignment_set_id
and hasa.assignment_id (+) = pa.assignment_id
and nvl(hasa.include_or_exclude,'I') <> 'E'
order by ppr.person_id;
select /*+ ORDERED */
pp.person_id,
pa.assignment_id,
to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD') effective_date,
null include_or_exclude
from per_periods_of_service ppos,
per_assignments_f pa,
per_people_f pp
where ppos.person_id
between p_start_person_id and p_end_person_id
and ppos.business_group_id + 0 = g_business_group_id
and ((nvl(g_inc_term_flag,'N') = 'N'
and least(nvl(ppos.actual_termination_date,g_effective_date + 1),g_effective_date + 1) > g_effective_date)
or (nvl(g_inc_term_flag,'N') = 'Y'
and ((g_term_date_from is null
and g_term_date_to is null)
or (ppos.actual_termination_date
between nvl(g_term_date_from,ppos.actual_termination_date) and nvl(g_term_date_to,ppos.actual_termination_date)))))
and pa.period_of_service_id = ppos.period_of_service_id
and pa.primary_flag = 'Y'
and to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
between pa.effective_start_date and pa.effective_end_date
and nvl(pa.location_id,-1) = nvl(g_location_id,nvl(pa.location_id,-1))
and (g_organization_id is null
or (g_organization_id is not null
and nvl(g_inc_org_hier_flag,'Y') = 'Y'
and pa.organization_id in (
select /*+ ORDERED */
distinct pose.organization_id_child
from per_organization_structures pos,
per_org_structure_versions posv,
per_org_structure_elements pose
where pos.business_group_id = decode(g_business_group_id,g_organization_id,-1,g_business_group_id)
and pos.primary_structure_flag = 'Y'
and posv.organization_structure_id = pos.organization_structure_id
and posv.date_from <= g_effective_date
and nvl(posv.date_to,hr_api.g_eot) >= g_effective_date
and not exists(
select null
from per_org_structure_versions posv2
where posv2.organization_structure_id = pos.organization_structure_id
and posv2.date_from <= g_effective_date
and nvl(posv2.date_to,hr_api.g_eot) >= g_effective_date
and posv2.version_number > posv.version_number)
and pose.org_structure_version_id = posv.org_structure_version_id
start with pose.organization_id_parent = g_organization_id
connect by prior pose.organization_id_child = pose.organization_id_parent
union
select hou.organization_id
from hr_all_organization_units hou
where hou.business_group_id = decode(g_business_group_id,g_organization_id,g_business_group_id,-1)
and hou.organization_id <> hou.business_group_id
union
select g_organization_id
from dual))
or (g_organization_id is not null
and nvl(g_inc_org_hier_flag,'Y') = 'N'
and pa.organization_id = g_organization_id))
and pp.person_id = pa.person_id
and to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
between pp.effective_start_date and pp.effective_end_date
order by pp.person_id;
select /*+ ORDERED */
ppos.person_id,
pa.assignment_id,
to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD') effective_date,
hasa.include_or_exclude
from per_periods_of_service ppos,
per_assignments_f pa,
hr_assignment_set_amendments hasa,
per_people_f pp
where ppos.person_id
between p_start_person_id and p_end_person_id
and ppos.business_group_id + 0 = g_business_group_id
and ((nvl(g_inc_term_flag,'N') = 'N'
and least(nvl(ppos.actual_termination_date,g_effective_date + 1),g_effective_date + 1) > g_effective_date)
or (nvl(g_inc_term_flag,'N') = 'Y'
and ((g_term_date_from is null
and g_term_date_to is null)
or (ppos.actual_termination_date
between nvl(g_term_date_from,ppos.actual_termination_date) and nvl(g_term_date_to,ppos.actual_termination_date)))))
and pa.period_of_service_id = ppos.period_of_service_id
and pa.primary_flag = 'Y'
and to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
between pa.effective_start_date and pa.effective_end_date
and nvl(pa.location_id,-1) = nvl(g_location_id,nvl(pa.location_id,-1))
and (g_organization_id is null
or (g_organization_id is not null
and nvl(g_inc_org_hier_flag,'Y') = 'Y'
and pa.organization_id in (
select /*+ ORDERED */
distinct pose.organization_id_child
from per_organization_structures pos,
per_org_structure_versions posv,
per_org_structure_elements pose
where pos.business_group_id = decode(g_business_group_id,g_organization_id,-1,g_business_group_id)
and pos.primary_structure_flag = 'Y'
and posv.organization_structure_id = pos.organization_structure_id
and posv.date_from <= g_effective_date
and nvl(posv.date_to,hr_api.g_eot) >= g_effective_date
and not exists(
select null
from per_org_structure_versions posv2
where posv2.organization_structure_id = pos.organization_structure_id
and posv2.date_from <= g_effective_date
and nvl(posv2.date_to,hr_api.g_eot) >= g_effective_date
and posv2.version_number > posv.version_number)
and pose.org_structure_version_id = posv.org_structure_version_id
start with pose.organization_id_parent = g_organization_id
connect by prior pose.organization_id_child = pose.organization_id_parent
union
select hou.organization_id
from hr_all_organization_units hou
where hou.business_group_id = decode(g_business_group_id,g_organization_id,g_business_group_id,-1)
and hou.organization_id <> hou.business_group_id
union
select g_organization_id
from dual))
or (g_organization_id is not null
and nvl(g_inc_org_hier_flag,'Y') = 'N'
and pa.organization_id = g_organization_id))
and to_number(decode(nvl(to_char(g_ass_set_formula_id),'-1'),'-1',
decode(nvl(g_ass_set_amendment_type,'X'),'I',hasa.assignment_id,pa.assignment_id),
pa.assignment_id)) = pa.assignment_id
and hasa.assignment_set_id (+) = g_assignment_set_id
and hasa.assignment_id (+) = pa.assignment_id
and nvl(hasa.include_or_exclude,'I') <> 'E'
and pp.person_id = pa.person_id
and to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
between pp.effective_start_date and pp.effective_end_date
order by pp.person_id;
g_ass_tbl.delete;
g_ass_ind_tbl.delete;
g_per_ind_tbl.delete;
l_ass_id_tbl.delete;
select pay_assignment_actions_s.nextval
into l_assignment_action_id
from dual;
select /*+ ORDERED */
v.person_id,
v.effective_date_ass,
v.effective_date_proc,
v.employee_number,
v.last_name last_name_kana,
v.first_name first_name_kana,
v.per_information18 last_name_kanji,
v.per_information19 first_name_kanji,
v.date_of_birth,
v.sex,
pac.postal_code,
pac.address_line1 address1,
pac.address_line2 address2,
pac.address_line3 address3,
pac.region_1 address_kana1,
pac.region_2 address_kana2,
pac.region_3 address_kana3,
pac.town_or_city district_code,
pac.country, /* maybe not required */
pj.name job_name,
v.date_start hire_date,
v.date_of_death death_date,
v.actual_termination_date term_date,
v.leaving_reason term_reason,
pphh.phone_number phone_home,
pphm.phone_number phone_mobile,
pphw.phone_number phone_work
from (select /*+ ORDERED */
pp.person_id,
pp.employee_number,
pp.last_name,
pp.first_name,
pp.per_information18,
pp.per_information19,
pp.date_of_birth,
pp.sex,
pp.date_of_death,
pa.job_id,
ppos.date_start,
ppos.actual_termination_date,
ppos.final_process_date,
ppos.leaving_reason,
to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD') effective_date_ass,
to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.final_process_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.final_process_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD') effective_date_proc
from per_all_assignments_f pa,
per_periods_of_service ppos,
per_all_people_f pp
where pa.assignment_id = p_assignment_id
and ppos.period_of_service_id = pa.period_of_service_id
and to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
between pa.effective_start_date and pa.effective_end_date
and pp.person_id = pa.person_id
and to_date(decode(nvl(g_inc_term_flag,'N'),
'N',to_char(least(nvl(ppos.actual_termination_date,g_effective_date),g_effective_date),'YYYY/MM/DD'),
to_char(nvl(ppos.actual_termination_date,g_effective_date),'YYYY/MM/DD')),'YYYY/MM/DD')
between pp.effective_start_date and pp.effective_end_date) v,
per_jobs_vl pj,
per_addresses pac,
per_phones pphh,
per_phones pphm,
per_phones pphw
where pj.job_id (+) = v.job_id
and v.effective_date_ass
between pj.date_from (+) and nvl(pj.date_to(+), hr_api.g_eot)
and pac.person_id (+) = v.person_id
and pac.address_type (+) = 'JP_C'
and v.effective_date_ass
between pac.date_from (+) and nvl(pac.date_to(+), hr_api.g_eot)
and pphh.parent_id (+) = v.person_id
and pphh.phone_type (+) = 'H1'
and v.effective_date_ass
between pphh.date_from (+) and nvl(pphh.date_to(+), hr_api.g_eot)
and pphm.parent_id (+) = v.person_id
and pphm.phone_type (+) = 'M'
and v.effective_date_ass
between pphm.date_from (+) and nvl(pphm.date_to(+), hr_api.g_eot)
and pphw.parent_id (+) = v.person_id
and pphw.phone_type (+) = 'W1'
and v.effective_date_ass
between pphw.date_from (+) and nvl(pphw.date_to(+), hr_api.g_eot);
select /*+ ORDERED */
paa.assignment_action_id,
ppa.effective_date
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.effective_date <= l_effective_date_proc
and ppa.action_type in ('R','Q')
and nvl(ppa.element_set_id,-1) in (c_trm_ele_set_id,-1)
and decode(ppa.action_type,'R','TRM',nvl(per_jp_empdet_archive_pkg.get_fuzzy_proc_type(paa.assignment_action_id),'X')) = 'TRM'
and not exists(
select /*+ ORDERED */
null
from pay_action_interlocks pai_v,
pay_assignment_actions paa_v,
pay_payroll_actions ppa_v
where pai_v.locked_action_id = paa.assignment_action_id
and paa_v.assignment_action_id = pai_v.locking_action_id
and ppa_v.payroll_action_id = paa.payroll_action_id
and ppa_v.action_type = 'V')
order by paa.action_sequence desc;
select /*+ ORDERED */
pac.segment8 graduation_date, /* no date validation */
pac.segment3 school_name,
pac.segment4 school_name_kana,
pac.segment5 faculty,
pac.segment6 faculty_kana,
pac.segment7 department
from per_person_analyses ppa,
per_analysis_criteria pac
where ppa.person_id = l_person_id
and nvl(ppa.date_from,l_effective_date_ass) <= l_effective_date_ass
and ppa.id_flex_num = c_jp_pea_flex_num
and pac.analysis_criteria_id = ppa.analysis_criteria_id
and l_effective_date_ass
between nvl(pac.start_date_active,hr_api.g_sot) and nvl(pac.end_date_active,l_effective_date_ass)
and pac.enabled_flag = 'Y';
select /*+ ORDERED */
pqt.name qualification_type,
pq.title,
pq.grade_attained,
pq.start_date,
pq.end_date,
pq.status,
nvl(pea.establishment,pe.name) establishment,
pq.license_number
from per_qualifications_vl pq,
per_qualification_types_vl pqt,
per_establishment_attendances pea,
per_establishments pe
where pq.person_id = l_person_id
and nvl(pq.start_date,l_effective_date_ass) <= l_effective_date_ass
and pqt.qualification_type_id = pq.qualification_type_id
and pea.attendance_id (+) = pq.attendance_id
and pe.establishment_id (+) = pea.establishment_id;
select /*+ ORDERED */
std_v.assignment_id,
std_v.assignment_number,
std_v.effective_start_date,
etd_v.effective_end_date,
--to_date(decode(sign(hr_api.g_eot - etd_v.effective_end_date),1,to_char(etd_v.effective_end_date,'YYYY/MM/DD'),null),'YYYY/MM/DD') effective_end_date_d,
to_date(decode(sign(l_effective_date_ass - etd_v.effective_end_date),-1,null,to_char(etd_v.effective_end_date,'YYYY/MM/DD')),'YYYY/MM/DD') effective_end_date_d,
std_v.organization_id,
hou.name organization_name,
std_v.job_id,
pj.name job_name,
std_v.position_id,
hap.name position_name,
std_v.grade_id,
pg.name grade_name
from (select rownum std_rownum,
pa_std.assignment_id,
pa_std.assignment_number,
pa_std.organization_id,
pa_std.job_id,
pa_std.position_id,
pa_std.grade_id,
pa_std.effective_start_date,
pa_std.effective_end_date
from per_all_assignments_f pa_std
where pa_std.assignment_id = p_assignment_id
and pa_std.effective_start_date <= l_effective_date_ass
and (exists(
select null
from per_all_assignments_f pa_sp
where pa_sp.assignment_id = pa_std.assignment_id
and pa_sp.effective_start_date <= l_effective_date_ass
and (pa_sp.organization_id <> pa_std.organization_id
or nvl(pa_sp.job_id,-1) <> nvl(pa_std.job_id,-1)
or nvl(pa_sp.position_id,-1) <> nvl(pa_std.position_id,-1)
or nvl(pa_sp.grade_id,-1) <> nvl(pa_std.grade_id,-1))
and (pa_sp.effective_end_date + 1 = pa_std.effective_start_date
or pa_sp.effective_start_date = pa_std.effective_end_date + 1))
or not exists(
select null
from per_all_assignments_f pa_sm
where pa_sm.assignment_id = pa_std.assignment_id
and pa_sm.effective_start_date <= l_effective_date_ass
and pa_sm.effective_start_date < pa_std.effective_start_date))
and not exists(
select null
from per_all_assignments_f pa_se
where pa_se.assignment_id = pa_std.assignment_id
and pa_se.effective_start_date <= l_effective_date_ass
and pa_se.organization_id = pa_std.organization_id
and nvl(pa_se.job_id,-1) = nvl(pa_std.job_id,-1)
and nvl(pa_se.position_id,-1) = nvl(pa_std.position_id,-1)
and nvl(pa_se.grade_id,-1) = nvl(pa_std.grade_id,-1)
and pa_se.effective_end_date = pa_std.effective_start_date -1)
order by pa_std.effective_start_date) std_v,
(select rownum etd_rownum,
pa_etd.assignment_id,
pa_etd.organization_id,
pa_etd.job_id,
pa_etd.position_id,
pa_etd.grade_id,
pa_etd.effective_start_date,
pa_etd.effective_end_date
from per_all_assignments_f pa_etd
where pa_etd.assignment_id = p_assignment_id
and pa_etd.effective_start_date <= l_effective_date_ass
and (exists(
select null
from per_all_assignments_f pa_ep
where pa_ep.assignment_id = pa_etd.assignment_id
and pa_ep.effective_start_date <= l_effective_date_ass
and (pa_ep.organization_id <> pa_etd.organization_id
or nvl(pa_ep.job_id,-1) <> nvl(pa_etd.job_id,-1)
or nvl(pa_ep.position_id,-1) <> nvl(pa_etd.position_id,-1)
or nvl(pa_ep.grade_id,-1) <> nvl(pa_etd.grade_id,-1))
and (pa_ep.effective_end_date + 1 = pa_etd.effective_start_date
or pa_ep.effective_start_date = pa_etd.effective_end_date + 1))
or not exists(
select null
from per_all_assignments_f pa_em
where pa_em.assignment_id = pa_etd.assignment_id
and pa_em.effective_start_date <= l_effective_date_ass
and pa_em.effective_start_date > pa_etd.effective_start_date))
and not exists(
select null
from per_all_assignments_f pa_ee
where pa_ee.assignment_id = pa_etd.assignment_id
and pa_ee.effective_start_date <= l_effective_date_ass
and pa_ee.organization_id = pa_etd.organization_id
and nvl(pa_ee.job_id,-1) = nvl(pa_etd.job_id,-1)
and nvl(pa_ee.position_id,-1) = nvl(pa_etd.position_id,-1)
and nvl(pa_ee.grade_id,-1) = nvl(pa_etd.grade_id,-1)
and pa_ee.effective_start_date = pa_etd.effective_end_date + 1)
order by pa_etd.effective_start_date) etd_v,
hr_organization_units hou,
per_jobs_vl pj,
hr_all_positions_f_vl hap,
per_grades_vl pg
where etd_v.etd_rownum = std_v.std_rownum
and hou.organization_id = std_v.organization_id
and pj.job_id (+) = std_v.job_id
and hap.position_id (+) = std_v.position_id
and pg.grade_id (+) = std_v.grade_id;
select /*+ ORDERED */
std_v.assignment_id,
std_v.effective_start_date,
etd_v.effective_end_date,
--to_date(decode(sign(hr_api.g_eot - etd_v.effective_end_date),1,to_char(etd_v.effective_end_date,'YYYY/MM/DD'),null),'YYYY/MM/DD') effective_end_date_d,
to_date(decode(sign(l_effective_date_ass - etd_v.effective_end_date),-1,null,to_char(etd_v.effective_end_date,'YYYY/MM/DD')),'YYYY/MM/DD') effective_end_date_d,
std_v.organization_id,
hou.name organization_name,
std_v.job_id,
pj.name job_name,
std_v.position_id,
hap.name position_name
from (select rownum std_rownum,
pa_std.assignment_id,
pa_std.organization_id,
pa_std.job_id,
pa_std.position_id,
pa_std.effective_start_date,
pa_std.effective_end_date
from per_all_assignments_f pa_std
where pa_std.assignment_id = p_assignment_id
and pa_std.effective_start_date <= l_effective_date_ass
and (exists(
select null
from per_all_assignments_f pa_sp
where pa_sp.assignment_id = pa_std.assignment_id
and pa_sp.effective_start_date <= l_effective_date_ass
and (pa_sp.organization_id <> pa_std.organization_id
or nvl(pa_sp.job_id,-1) <> nvl(pa_std.job_id,-1)
or nvl(pa_sp.position_id,-1) <> nvl(pa_std.position_id,-1))
and (pa_sp.effective_end_date + 1 = pa_std.effective_start_date
or pa_sp.effective_start_date = pa_std.effective_end_date + 1))
or not exists(
select null
from per_all_assignments_f pa_sm
where pa_sm.assignment_id = pa_std.assignment_id
and pa_sm.effective_start_date <= l_effective_date_ass
and pa_sm.effective_start_date < pa_std.effective_start_date))
and not exists(
select null
from per_all_assignments_f pa_se
where pa_se.assignment_id = pa_std.assignment_id
and pa_se.effective_start_date <= l_effective_date_ass
and pa_se.organization_id = pa_std.organization_id
and nvl(pa_se.job_id,-1) = nvl(pa_std.job_id,-1)
and nvl(pa_se.position_id,-1) = nvl(pa_std.position_id,-1)
and pa_se.effective_end_date = pa_std.effective_start_date -1)
order by pa_std.effective_start_date) std_v,
(select rownum etd_rownum,
pa_etd.assignment_id,
pa_etd.organization_id,
pa_etd.job_id,
pa_etd.position_id,
pa_etd.effective_start_date,
pa_etd.effective_end_date
from per_all_assignments_f pa_etd
where pa_etd.assignment_id = p_assignment_id
and pa_etd.effective_start_date <= l_effective_date_ass
and (exists(
select null
from per_all_assignments_f pa_ep
where pa_ep.assignment_id = pa_etd.assignment_id
and pa_ep.effective_start_date <= l_effective_date_ass
and (pa_ep.organization_id <> pa_etd.organization_id
or nvl(pa_ep.job_id,-1) <> nvl(pa_etd.job_id,-1)
or nvl(pa_ep.position_id,-1) <> nvl(pa_etd.position_id,-1))
and (pa_ep.effective_end_date + 1 = pa_etd.effective_start_date
or pa_ep.effective_start_date = pa_etd.effective_end_date + 1))
or not exists(
select null
from per_all_assignments_f pa_em
where pa_em.assignment_id = pa_etd.assignment_id
and pa_em.effective_start_date <= l_effective_date_ass
and pa_em.effective_start_date > pa_etd.effective_start_date))
and not exists(
select null
from per_all_assignments_f pa_ee
where pa_ee.assignment_id = pa_etd.assignment_id
and pa_ee.effective_start_date <= l_effective_date_ass
and pa_ee.organization_id = pa_etd.organization_id
and nvl(pa_ee.job_id,-1) = nvl(pa_etd.job_id,-1)
and nvl(pa_ee.position_id,-1) = nvl(pa_etd.position_id,-1)
and pa_ee.effective_start_date = pa_etd.effective_end_date + 1)
order by pa_etd.effective_start_date) etd_v,
hr_organization_units hou,
per_jobs_vl pj,
hr_all_positions_f_vl hap
where etd_v.etd_rownum = std_v.std_rownum
and hou.organization_id = std_v.organization_id
and pj.job_id (+) = std_v.job_id
and hap.position_id (+) = std_v.position_id;
select ppos.date_start hire_date,
ppos.actual_termination_date term_date,
pa.effective_start_date,
pa.effective_end_date,
to_date(decode(sign(hr_api.g_eot - pa.effective_end_date),1,to_char(pa.effective_end_date,'YYYY/MM/DD'),null),'YYYY/MM/DD') effective_end_date_d,
pa.organization_id,
hou.name organization_name,
pa.job_id,
pj.name job_name
from per_periods_of_service ppos,
per_all_assignments_f pa,
hr_organization_units hou,
per_jobs_vl pj
where ppos.person_id = l_person_id
and ppos.date_start < l_hire_date
and ppos.actual_termination_date is not null
and pa.period_of_service_id = ppos.period_of_service_id
and ppos.actual_termination_date
between pa.effective_start_date and pa.effective_end_date
and hou.organization_id = pa.organization_id
and pj.job_id (+) = pa.job_id;
select ppe.employer_name,
ppe.start_date,
ppe.end_date,
ppj.job_name,
ppj.employment_category
from per_previous_employers ppe,
per_previous_jobs ppj
where ppe.person_id = l_person_id
and nvl(ppe.start_date,l_hire_date - 1) < l_hire_date
and ppj.previous_employer_id (+) = ppe.previous_employer_id
and nvl(ppj.start_date,l_hire_date - 1) < l_hire_date
and not exists(
select null
from per_previous_jobs ppj2
where ppj2.previous_employer_id = ppj.previous_employer_id
and ((nvl(ppj2.start_date,l_hire_date) = nvl(ppj.start_date,l_hire_date)
and nvl(ppj2.end_date,l_hire_date) = nvl(ppj.end_date,l_hire_date)
and ppj2.previous_job_id > ppj.previous_job_id)
or (nvl(ppj2.end_date,l_hire_date) > nvl(ppj.end_date,l_hire_date))
or (nvl(ppj2.end_date,l_hire_date) = nvl(ppj.end_date,l_hire_date)
and nvl(ppj2.start_date,l_hire_date) > nvl(ppj.start_date,l_hire_date))));
select /*+ ORDERED */
pp.last_name last_name_kana,
pp.first_name first_name_kana,
pp.per_information18 last_name_kanji,
pp.per_information19 first_name_kanji,
pp.date_of_birth,
trunc(months_between(nvl(pp.date_of_death,g_effective_date),pp.date_of_birth)/12) age,
pcr.contact_type,
pp.sex,
pcr.primary_contact_flag,
pcr.dependent_flag,
pcr.rltd_per_rsds_w_dsgntr_flag,
fnd_number.canonical_to_number(pcr.cont_information2) sequence,
pcr.cont_information3 household_head,
pcr.cont_information1 si_itax
from per_contact_relationships pcr,
per_all_people_f pp
where pcr.person_id = l_person_id
and l_effective_date_ass
between nvl(pcr.date_start,hr_api.g_sot) and nvl(pcr.date_end,l_effective_date_ass)
and pp.person_id = pcr.contact_person_id
and (l_effective_date_ass
between pp.effective_start_date and pp.effective_end_date
or (pp.effective_start_date = pp.start_date
and not exists(
select null
from per_all_people_f pp2
where pp2.person_id = pp.person_id
and l_effective_date_ass
between pp2.effective_start_date and pp2.effective_end_date)))
order by
decode(pcr.contact_type,'S',1,2),
pp.date_of_birth,
decode(pp.sex,'M',1,'F',2,3),
pp.last_name,
pp.first_name;
select assignment_id
into l_assignment_id
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;