[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select posv.org_structure_version_id
,posv.date_from date_from
,nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY')) date_to
from per_org_structure_versions posv
where posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
and (P_REPORT_DATE_FROM between posv.date_from and
nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY'))
or posv.date_from between P_REPORT_DATE_FROM and P_REPORT_DATE_TO)
order by posv.org_structure_version_id
,posv.date_from
,posv.date_to;
select ose.org_structure_element_id
,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 ose.organization_id_parent = P_TOP_ORGANIZATION_ID
order by ose.organization_id_child;
select 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 paf.assignment_id
,paf.job_id
,paf.effective_start_date
,paf.assignment_type
from per_all_assignments_f paf
,per_assignment_status_types past
where paf.organization_id = P_ORGANIZATION_ID_CHILD
and P_EFFECTIVE_DATE between paf.effective_start_date
and paf.effective_end_date
and paf.assignment_status_type_id = past.assignment_status_type_id
and (
(P_WORKER_TYPE IN ('E','B')
and paf.assignment_type = 'E'
and (
(P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_ASSIGN')
OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_ASSIGN')
OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_ASSIGN',
'SUSP_ASSIGN'))
)
)
OR
(P_WORKER_TYPE IN ('C','B')
and paf.assignment_type = 'C'
and (
(P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_CWK')
OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_CWK_ASG')
OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_CWK',
'SUSP_CWK_ASG'))
)
)
);
select paf.assignment_id
,paf.job_id
,paf.effective_start_date
--bug 6124652 starts here
,paf.effective_end_date
--bug 6124652 ends here
,paf.assignment_type
from per_all_assignments_f paf
,per_assignment_status_types past
where paf.organization_id = P_ORGANIZATION_ID_CHILD
--bug 6124652 starts here
-- and P_EFFECTIVE_DATE between paf.effective_start_date
-- and paf.effective_end_date
and ((P_START_DATE <= paf.effective_start_date
and paf.effective_end_date <=P_END_DATE)
or( P_START_DATE between paf.effective_start_date
and paf.effective_end_date)
or( P_END_DATE between paf.effective_start_date
and paf.effective_end_date)
)
--bug 6124652 ends here
and paf.assignment_status_type_id = past.assignment_status_type_id
and (
(P_WORKER_TYPE IN ('E','B')
and paf.assignment_type = 'E'
and (
(P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_ASSIGN')
OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_ASSIGN')
OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_ASSIGN',
'SUSP_ASSIGN'))
)
)
OR
(P_WORKER_TYPE IN ('C','B')
and paf.assignment_type = 'C'
and (
(P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_CWK')
OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_CWK_ASG')
OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_CWK',
'SUSP_CWK_ASG'))
)
)
);
select paf.assignment_id
,paf.job_id
,paf.effective_start_date
,paf.assignment_type
from per_all_assignments_f paf
,per_assignment_status_types past
where paf.organization_id = P_ORGANIZATION_ID_CHILD
--Bug 6124652 starts here
-- and paf.effective_start_date <= P_DATE_FROM
and P_DATE_FROM between paf.effective_start_date
and paf.effective_end_date
--Bug 6124652 ends here
and paf.assignment_status_type_id = past.assignment_status_type_id
and P_DATE_TO <= (select max(paf1.effective_end_date)
from per_all_assignments_f paf1
,per_assignment_status_types past1
where paf.assignment_id = paf1.assignment_id
and paf1.assignment_status_type_id = past1.assignment_status_type_id
and past.per_system_status = past1.per_system_status)
and (
(P_WORKER_TYPE IN ('E','B')
and paf.assignment_type = 'E'
and (
(P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_ASSIGN')
OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_ASSIGN')
OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_ASSIGN',
'SUSP_ASSIGN'))
)
)
OR
(P_WORKER_TYPE IN ('C','B')
and paf.assignment_type = 'C'
and (
(P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_CWK')
OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_CWK_ASG')
OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_CWK',
'SUSP_CWK_ASG'))
)
)
);
select paf.assignment_id
,paf.job_id
,paf.effective_start_date
from per_all_assignments_f paf
,per_assignment_status_types past
where paf.organization_id = p_organization_id_child
-- and paf.business_group_id = P_BUSINESS_GROUP_ID
and paf.assignment_type = 'A'
and paf.effective_start_date between P_DATE_FROM and P_DATE_TO
and paf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'OFFER';
select paf.assignment_id
,paf.job_id
,paf.effective_start_date
from per_all_assignments_f paf,
per_assignment_status_types past
where paf.organization_id = p_organization_id_child
-- and paf.business_group_id = P_BUSINESS_GROUP_ID
and paf.assignment_type = 'A'
and paf.effective_start_date between P_DATE_FROM and P_DATE_TO
and paf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'ACCEPTED';
select vac.job_id,
vac.BUDGET_MEASUREMENT_VALUE,
vac.NUMBER_OF_OPENINGS
from per_vacancies vac
,per_requisitions req
where vac.organization_id = P_ORGANIZATION_ID_CHILD
--and vac.business_group_id = P_BUSINESS_GROUP_ID
and vac.BUDGET_MEASUREMENT_TYPE = P_BUDGET
and vac.REQUISITION_ID = req.REQUISITION_ID
and req.date_from < P_DATE_TO
and nvl(req.date_to,to_date('31/12/4712','DD/MM/YYYY'))
> P_DATE_TO;
select pos.leaving_reason
,pos.actual_termination_date
,paf.job_id
,paf.assignment_id
from per_periods_of_service pos
,per_all_assignments_f paf
where pos.date_start <= P_DATE_FROM
and pos.actual_termination_date is not null
and pos.actual_termination_date between P_DATE_FROM
and P_DATE_TO
and pos.period_of_service_id = paf.period_of_service_id
and paf.effective_end_date = pos.actual_termination_date
and paf.organization_id = P_ORGANIZATION_ID_CHILD
order by paf.assignment_id ;
select pos.leaving_reason
,pos.actual_termination_date
,paf.job_id
,paf.assignment_id
from per_periods_of_service pos
,per_all_assignments_f paf
where pos.date_start <= P_DATE_FROM
and pos.actual_termination_date is not null
and pos.actual_termination_date between
P_CUR_DATE_FROM and P_CUR_DATE_TO
and pos.period_of_service_id = paf.period_of_service_id
and paf.effective_end_date = pos.actual_termination_date
and paf.organization_id = P_ORGANIZATION_ID_CHILD
order by paf.assignment_id ;
select pop.termination_reason
,pop.actual_termination_date
,paf.job_id
,paf.assignment_id
from per_periods_of_placement pop
,per_all_assignments_f paf
where pop.date_start <= P_DATE_FROM
and pop.actual_termination_date is not null
and pop.actual_termination_date between P_DATE_FROM
and P_DATE_TO
and pop.date_start = paf.period_of_placement_date_start
and pop.person_id = paf.person_id
and paf.effective_end_date = pop.actual_termination_date
and paf.assignment_type = 'C'
and paf.organization_id = P_ORGANIZATION_ID_CHILD
order by paf.assignment_id;
select pop.termination_reason
,pop.actual_termination_date
,paf.job_id
,paf.assignment_id
from per_periods_of_placement pop
,per_all_assignments_f paf
where pop.date_start <= P_DATE_FROM
and pop.actual_termination_date is not null
and pop.actual_termination_date between P_CUR_DATE_FROM
and P_CUR_DATE_TO
and pop.date_start = paf.period_of_placement_date_start
and pop.person_id = paf.person_id
and paf.effective_end_date = pop.actual_termination_date
and paf.assignment_type = 'C'
and paf.organization_id = P_ORGANIZATION_ID_CHILD
order by paf.assignment_id;
select formula_id
from ff_formulas_f
where p_business_group_id = business_group_id+0
and trunc(sysdate) between effective_start_date and effective_end_date
and formula_name = 'HR_PERSON_TYPE'
and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Oracle Payroll');
select formula_id
from ff_formulas_f
where business_group_id+0 is null
and trunc(sysdate) between effective_start_date and effective_end_date
and formula_name = 'HR_PERSON_TYPE_TEMPLATE'
and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Oracle Payroll');
select formula_id
from ff_formulas_f
where business_group_id+0 = p_business_group_id
and trunc(sysdate) between effective_start_date and effective_end_date
and formula_name = 'HR_MOVE_TYPE'
and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Oracle Payroll');
select formula_id
from ff_formulas_f
where business_group_id+0 is null
and trunc(sysdate) between effective_start_date and effective_end_date
and formula_name = 'HR_MOVE_TYPE_TEMPLATE'
and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Oracle Payroll');
select formula_id
from ff_formulas_f
where p_business_group_id = business_group_id+0
and trunc(sysdate) between effective_start_date and effective_end_date
and formula_name = 'BUDGET_'||p_budget
and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Quickpaint');
select formula_id
from ff_formulas_f
where business_group_id+0 is null
and trunc(sysdate) between effective_start_date and effective_end_date
and formula_name = 'TEMPLATE_'||p_budget
and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Quickpaint');
select
pose.org_structure_version_id
,pose.org_structure_element_id
,pose.organization_id_child
,posv1.version_number
,greatest(posv1.date_from,P_REPORT_DATE_FROM) date_from
,least(nvl(posv1.date_to,to_date('31/12/4712','DD/MM/YYYY')),P_REPORT_DATE_TO) date_to
from per_org_structure_elements pose
,per_org_structure_versions posv1
where pose.organization_id_parent = P_TOP_ORGANIZATION_ID
and pose.org_structure_version_id = posv1.org_structure_version_id
and pose.org_structure_version_id in (
select posv.org_structure_version_id
from per_org_structure_versions posv
where posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
and (P_REPORT_DATE_FROM between posv.date_from and
nvl(posv.date_to, P_REPORT_DATE_TO)
or posv.date_from between P_REPORT_DATE_FROM and P_REPORT_DATE_TO))
order by pose.org_structure_version_id
,pose.organization_id_child;
select posv.org_structure_version_id
,1-(posv.org_structure_version_id+P_TOP_ORGANIZATION_ID)
org_structure_element_id
,P_TOP_ORGANIZATION_ID organization_id_child
,posv.version_number
,greatest(posv.date_from,P_REPORT_DATE_FROM) date_from
,least(nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY')),P_REPORT_DATE_TO) date_to
from per_org_structure_versions posv
where posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
and (P_REPORT_DATE_FROM between posv.date_from and
nvl(posv.date_to, P_REPORT_DATE_TO)
or posv.date_from between P_REPORT_DATE_FROM and P_REPORT_DATE_TO)
order by posv.org_structure_version_id;
select PARAMETER_VALUE
from pay_action_parameters
where PARAMETER_NAME = 'HR_HEADCOUNT_FOR_CWK';