[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(p.person_id)
INTO l_v_hires
FROM hr_all_organization_units b,
per_requisitions r,
per_all_vacancies v,
per_all_assignments_f a,
per_all_people_f p
WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date
AND p.effective_end_date
/* bug 2033292 */
AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
AND a.effective_end_date
/* bug 2033292 */
AND p.employee_number IS NOT NULL
AND p.person_id = a.person_id
AND a.vacancy_id = v.vacancy_id
AND v.name = p_vacancy
AND v.requisition_id = r.requisition_id
AND r.name = p_requisition
AND b.organization_id = v.business_group_id
AND b.organization_id = b.business_group_id
AND a.assignment_type = 'E'
AND b.name = p_business_group;
SELECT COUNT(p.person_id)
INTO l_v_offers
FROM hr_all_organization_units b,
per_assignment_status_types ast,
per_requisitions r,
per_all_vacancies v,
per_all_assignments_f a,
per_all_people_f p
WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date
AND p.effective_end_date
/* bug 2033292 */
AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
AND a.effective_end_date
/* bug 2033292 */
AND p.applicant_number IS NOT NULL
AND p.person_id = a.person_id
AND a.assignment_type = 'A'
AND a.assignment_status_type_id =
ast.assignment_status_type_id
AND ast.per_system_status = 'OFFER'
AND a.vacancy_id = v.vacancy_id
AND v.name = p_vacancy
AND v.requisition_id = r.requisition_id
AND r.name = p_requisition
AND b.organization_id = v.business_group_id
AND b.organization_id = b.business_group_id
AND b.name = p_business_group;
SELECT COUNT(p.person_id)
INTO l_ra_hires
FROM hr_all_organization_units b,
per_recruitment_activities r,
per_all_assignments_f a,
per_all_people_f p
WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date
AND p.effective_end_date
/* bug 2033292 */
AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
AND a.effective_end_date
/* bug 2033292 */
AND p.employee_number IS NOT NULL
AND p.person_id = a.person_id
AND a.recruitment_activity_id = r.recruitment_activity_id
AND r.name = p_rec_activity
AND b.organization_id = r.business_group_id
AND b.organization_id = b.business_group_id
AND a.assignment_type = 'E'
AND b.name = p_business_group;
SELECT COUNT(p.person_id)
INTO l_ra_offers
FROM hr_all_organization_units b,
per_assignment_status_types ast,
per_recruitment_activities r,
per_all_assignments_f a,
per_all_people_f p
WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date
AND p.effective_end_date
/* bug 2033292 */
AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
AND a.effective_end_date
/* bug 2033292 */
AND p.applicant_number IS NOT NULL
AND p.person_id = a.person_id
AND a.assignment_type = 'A'
AND a.assignment_status_type_id =
ast.assignment_status_type_id
AND ast.per_system_status = 'OFFER'
AND a.recruitment_activity_id = r.recruitment_activity_id
AND r.name = p_rec_activity
AND b.organization_id = r.business_group_id
AND b.organization_id = b.business_group_id
AND b.name = p_business_group;
SELECT COUNT(p.person_id)
INTO l_rav_hires
FROM hr_all_organization_units b,
per_all_vacancies v,
per_recruitment_activities r,
per_all_assignments_f a,
per_all_people_f p
WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date
AND p.effective_end_date
/* bug 2033292 */
AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
AND a.effective_end_date
/* bug 2033292 */
AND p.employee_number IS NOT NULL
AND p.person_id = a.person_id
AND a.vacancy_id = v.vacancy_id
AND a.recruitment_activity_id = r.recruitment_activity_id
AND v.name = p_vacancy
AND r.name = p_rec_activity
AND b.organization_id = v.business_group_id
AND b.organization_id = r.business_group_id
AND b.organization_id = b.business_group_id
AND a.assignment_type = 'E'
AND b.name = p_business_group;
SELECT COUNT(p.person_id)
INTO l_rav_offers
FROM hr_all_organization_units b,
per_assignment_status_types ast,
per_all_vacancies v,
per_recruitment_activities r,
per_all_assignments_f a,
per_all_people_f p
WHERE TRUNC(SYSDATE) BETWEEN p.effective_start_date
AND p.effective_end_date
/* bug 2033292 */
AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
AND a.effective_end_date
/* bug 2033292 */
AND p.applicant_number IS NOT NULL
AND p.person_id = a.person_id
AND a.assignment_type = 'A'
AND a.assignment_status_type_id =
ast.assignment_status_type_id
AND ast.per_system_status = 'OFFER'
AND a.recruitment_activity_id = r.recruitment_activity_id
AND a.vacancy_id = v.vacancy_id
AND r.name = p_rec_activity
AND v.name = p_vacancy
AND b.organization_id = r.business_group_id
AND b.organization_id = v.business_group_id
AND b.organization_id = b.business_group_id
AND b.name = p_business_group;
SELECT COUNT(person_id)
INTO l_emp_count
FROM per_all_assignments_f a
WHERE TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND a.assignment_type = 'E'
AND a.recruitment_activity_id = p_rec_act_id;
SELECT 'Y'
FROM dual
WHERE TRUNC(SYSDATE) BETWEEN p_date_from
AND NVL(p_date_to,SYSDATE);