[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Selects the first date on which the system status passed in is */
/* set on the given assignment, and whether or not the stage was */
/* successful (based on whether another assignment record exists with */
/* a different status and whether the overall application was successful */
CURSOR assign_csr
(v_csr_system_status VARCHAR2) IS
SELECT asg.effective_start_date
,asg.change_reason
,DECODE(next_asg.assignment_id, to_number(null), g_success, 1)
FROM per_all_assignments_f asg
,per_assignment_status_types ast
,per_all_assignments_f next_asg
WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
AND asg.assignment_id = p_assignment_id
AND ast.per_system_status = v_csr_system_status
AND next_asg.assignment_id (+) = asg.assignment_id
AND next_asg.effective_start_date (+) > asg.effective_start_date
AND next_asg.assignment_status_type_id (+) <> asg.assignment_status_type_id
ORDER BY asg.effective_start_date ASC;
/* Cursor selecting the end date of the period of employment immediately */
/* following the application */
CURSOR end_emp_cur IS
SELECT pps.actual_termination_date, pps.leaving_reason
FROM per_periods_of_service pps
WHERE person_id = p_person_id
AND (p_date_start BETWEEN date_start
AND actual_termination_date
OR p_date_start > date_start
AND actual_termination_date IS NULL);
SELECT
asg.change_reason
FROM
per_all_assignments_f asg,
hr_lookups hrl
WHERE
asg.assignment_id = p_assignment_id
AND asg.change_reason = hrl.lookup_code
AND hrl.lookup_type = 'EMP_ASSIGN_REASON'
AND asg.effective_start_date = p_hire_date;
SELECT
asg.change_reason
FROM
per_all_assignments_f asg,
hr_lookups hrl
WHERE
asg.person_id = p_person_id
AND asg.effective_start_date = p_hire_date
AND asg.change_reason = hrl.lookup_code
AND hrl.lookup_type = 'EMP_ASSIGN_REASON'
AND asg.primary_flag = 'Y';
SELECT 1
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND effective_start_date > p_date_end
AND assignment_type = 'E';
SELECT 1
FROM per_all_assignments_f prim
WHERE prim.person_id = p_person_id
AND prim.primary_flag = 'Y'
AND prim.effective_start_date = (p_date_end + 1);
/* Selects the first assignment record for each assignment */
CURSOR initial_assignment_cursor IS
SELECT asg.assignment_id assignment_id
,asg.person_id person_id
,asg.business_group_id business_group_id
,asg.assignment_type assignment_type
,asg.application_id application_id
,asg.effective_start_date assignment_start
,apl.date_end application_end
,apl.projected_hire_date planned_start_date
,apl.termination_reason termination_reason
,decode(prev_pps.date_start,
/* If a previous period of service doesn't exist then applicant is new hire */
to_date(NULL),'NEW_HIRE',
decode(SIGN(prev_pps.actual_termination_date - asg.effective_start_date),
/* If the latest previous period of service has a past actual termination */
/* date then the applicant was an ex_employee */
-1,'RE_HIRE',
decode(emp_asg.organization_id,
/* If the applicant is an employee and is applying within the */
/* same organization then assignment start else org transfer */
asg.organization_id,'ASG_START',
'ORG_TRANS')))
gain_type
,decode(apl.application_id,
/* If person is an employee type they were successful */
to_number(null),1,
decode(apl.date_end,
/* If the application has not ended it is neither successful nor unsuccessful */
to_date(null),0,
decode(apl.termination_reason,
/* If there is a termination reason it is not successful */
null,hri_edw_fct_recruitment.is_successful(
asg.assignment_id,
apl.person_id,
apl.date_end),
-1))) success_flag
,GREATEST(
NVL(asg.last_update_date,to_date('01-01-2000','DD-MM-YYYY')),
NVL(apl.last_update_date,to_date('01-01-2000','DD-MM-YYYY')))
last_update_date
,asg.creation_date creation_date
FROM per_all_assignments_f asg -- Initial assignment record
,per_applications apl -- Application for assignment
,per_all_assignments_f emp_asg -- Pre-existing employee assignment
,per_periods_of_service prev_pps -- Previously ended period of service
WHERE asg.assignment_type IN ('E','A')
AND apl.application_id (+) = asg.application_id
AND asg.person_id = emp_asg.person_id (+)
AND emp_asg.primary_flag (+) = 'Y'
/* 115.3 Added following line to filter out benefits assignments */
AND emp_asg.assignment_type (+) = 'E'
AND asg.effective_start_date - 1
BETWEEN emp_asg.effective_start_date (+) AND emp_asg.effective_end_date (+)
/* 115.6 If an employee assignment exists then only include applicant assignments */
AND (emp_asg.assignment_id IS NULL
OR (emp_asg.assignment_id IS NOT NULL AND asg.assignment_type = 'A'))
AND asg.person_id = prev_pps.person_id (+)
AND prev_pps.date_start (+) < asg.effective_start_date
/* If a previous period of service exists, restrict to the most recent */
AND NOT EXISTS
(SELECT 1
FROM per_periods_of_service dummy
WHERE dummy.person_id = apl.person_id
AND dummy.date_start > prev_pps.date_start)
/* Filter out all but first assignment */
AND asg.effective_start_date = (SELECT MIN(asg1.effective_start_date)
FROM per_all_assignments_f asg1
WHERE asg1.assignment_id = asg.assignment_id);
DELETE FROM hri_recruitment_stages;
INSERT INTO hri_recruitment_stages
( assignment_id
, assignment_start_date
, business_group_id
, assignment_type
, application_id
, person_id
, gain_type
, success
, application_date
, application_end_date
, planned_start_date
, application_reason
, application_success
, interview1_date
, interview1_reason
, interview1_success
, interview2_date
, interview2_reason
, interview2_success
, offer_date
, offer_reason
, offer_success
, accept_date
, accept_reason
, accept_success
, hire_date
, hire_reason
, termination_date
, termination_reason
, separation_date
, separation_reason
, last_update_date
, creation_date )
VALUES
( new_asg_rec.assignment_id
, new_asg_rec.assignment_start
, new_asg_rec.business_group_id
, new_asg_rec.assignment_type
, new_asg_rec.application_id
, new_asg_rec.person_id
, new_asg_rec.gain_type
, new_asg_rec.success_flag
, g_application_date
, new_asg_rec.application_end
, new_asg_rec.planned_start_date
, g_application_reason
, g_application_success
, g_interview1_date
, g_interview1_reason
, g_interview1_success
, g_interview2_date
, g_interview2_reason
, g_interview2_success
, g_offer_date
, g_offer_reason
, g_offer_success
, g_accept_date
, g_accept_reason
, g_accept_success
, l_hire_date
, l_hire_reason
, l_termination_date
, l_termination_reason
, g_separation_date
, g_separation_reason
, new_asg_rec.last_update_date
, new_asg_rec.creation_date );
SELECT instance_code INTO g_instance_fk
FROM edw_local_instance;