The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(posv.org_structure_version_id) org_structure_version_id
from per_org_structure_versions posv
where posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
and to_number(to_char(nvl(posv.date_from,sysdate),'YYYY')) <= P_REPORT_YEAR
and to_number(to_char(nvl(posv.date_to,sysdate),'YYYY')) >= P_REPORT_YEAR
order by posv.org_structure_version_id;
select distinct ose.organization_id_child organization_id
from per_org_structure_elements ose, per_org_structure_versions_v posv
where ose.org_structure_version_id = posv.ORG_STRUCTURE_VERSION_ID
and ose.BUSINESS_GROUP_ID = posv.BUSINESS_GROUP_ID
and posv.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
and posv.ORGANIZATION_STRUCTURE_ID = P_ORG_STRUCTURE_ID
and ose.organization_id_parent = P_TOP_ORGANIZATION_ID
UNION
select P_TOP_ORGANIZATION_ID organization_id
from dual
order by organization_id;
select distinct ose.organization_id_child organization_id
from per_org_structure_elements ose
where ose.org_structure_version_id +0 = P_ORG_STRUCTURE_VERSION_ID
and P_ROLL_UP = 'Y'
connect by prior ose.organization_id_child = ose.organization_id_parent
and ose.org_structure_version_id = P_ORG_STRUCTURE_VERSION_ID
start with ose.organization_id_parent = P_ORGANIZATION_ID
and ose.org_structure_version_id = P_ORG_STRUCTURE_VERSION_ID
UNION
select P_ORGANIZATION_ID organization_id
from dual
where P_ROLL_UP = 'Y'
UNION
select P_ORGANIZATION_ID organization_id
from dual
where P_ROLL_UP = 'N';
SELECT distinct POU.ORGANIZATION_ID organization_id
FROM PER_ORGANIZATION_UNITS POU
WHERE POU.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND POU.ORGANIZATION_ID = HR_NL_ORG_INFO.Check_Org_In_Region(POU.ORGANIZATION_ID,P_REGION)
AND TO_NUMBER(TO_CHAR(POU.DATE_FROM,'YYYY'))<= P_REPORT_YEAR
AND TO_NUMBER(TO_CHAR(NVL(POU.DATE_TO,SYSDATE),'YYYY')) >= P_REPORT_YEAR;
select distinct paf.assignment_id assignment_id
,paf.person_id
from per_all_assignments_f paf
where paf.organization_id = p_organization_id_child
and paf.business_group_id = P_BUSINESS_GROUP_ID
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
and to_number(to_char(nvl(paf.effective_start_date,sysdate),'YYYY')) <= P_REPORT_YEAR
and to_number(to_char(nvl(paf.effective_end_date,sysdate),'YYYY')) >= P_REPORT_YEAR;
select distinct hr_nl_calc_target_group.get_target_group(p_person_id,
to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY'))
allochth
,ppf1.per_information7 objection
from per_people_f ppf1
where ppf1.business_group_id = P_BUSINESS_GROUP_ID
and to_number(to_char(nvl(ppf1.effective_start_date,sysdate),'YYYY')) <= P_REPORT_YEAR
and to_number(to_char(nvl(ppf1.effective_end_date,sysdate),'YYYY')) >= P_REPORT_YEAR
and ppf1.person_id = P_PERSON_ID
and to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY') between ppf1.effective_start_date and ppf1.effective_end_date;
select distinct paf.assignment_id assignment_id
,paf.effective_start_date start_date
,paf.employment_category per_type
,hr_nl_calc_target_group.get_target_group(ppf.person_id,
to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY')) allochth
,ppf.per_information7 objection
from per_all_assignments_f paf
, per_people_f ppf
where paf.organization_id = p_organization_id_child
and paf.business_group_id = P_BUSINESS_GROUP_ID
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
and to_number(to_char(nvl(paf.effective_start_date,sysdate),'YYYY')) <= P_REPORT_YEAR
and nvl(paf.effective_end_date,sysdate) >= to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY')
and paf.person_id = ppf.person_id
and ppf.BUSINESS_GROUP_ID = paf.business_group_id
and to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY') between ppf.effective_start_date and ppf.effective_end_date;
select distinct pos.leaving_reason leaving_reason
,paf.job_id job
,paf.person_id
from per_periods_of_service pos
, per_assignments_f paf
where to_number(to_char(nvl(pos.date_start,sysdate),'YYYY')) <= P_REPORT_YEAR
and to_number(to_char(nvl(pos.actual_termination_date,sysdate+50000),'YYYY')) = P_REPORT_YEAR
and pos.period_of_service_id = paf.period_of_service_id
and paf.business_group_id = P_BUSINESS_GROUP_ID
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
and paf.organization_id = P_ORGANIZATION_ID_CHILD;
select count(1)
into l_last_acht_total
from per_all_assignments_f paf
, per_people_f ppf
where paf.organization_id = P_ORGANIZATION_ID
and paf.business_group_id = P_BUSINESS_GROUP_ID
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
and to_number(to_char(nvl(paf.effective_start_date,sysdate),'YYYY')) <= P_REPORT_YEAR - 1
and nvl(paf.effective_end_date,sysdate) >= to_date('31/12/' ||to_char(P_REPORT_YEAR - 1),'DD/MM/YYYY')
and paf.person_id = ppf.person_id
and ppf.BUSINESS_GROUP_ID = paf.business_group_id
and hr_nl_calc_target_group.get_target_group(ppf.person_id,
to_date('31/12/' ||to_char(P_REPORT_YEAR - 1),'DD/MM/YYYY')) = 'Y'
and to_date('31/12/' ||to_char(P_REPORT_YEAR - 1),'DD/MM/YYYY') between ppf.effective_start_date and ppf.effective_end_date;
select hr_nl_calc_target_group.get_target_group(ppf.person_id,
to_date('31/12/'||to_char(P_REPORT_YEAR),'DD/MM/YYYY')) allochth
into l_acht
from per_people_f ppf
where ppf.person_id = term_rec.person_id
and to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY') between ppf.effective_start_date and ppf.effective_end_date;