APPS.HRI_EDW_FCT_RECRUITMENT dependencies on PER_ALL_ASSIGNMENTS_F

Line 75: FROM per_all_assignments_f asg

71: (v_csr_system_status VARCHAR2) IS
72: SELECT asg.effective_start_date
73: ,asg.change_reason
74: ,DECODE(next_asg.assignment_id, to_number(null), g_success, 1)
75: FROM per_all_assignments_f asg
76: ,per_assignment_status_types ast
77: ,per_all_assignments_f next_asg
78: WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
79: AND asg.assignment_id = p_assignment_id

Line 77: ,per_all_assignments_f next_asg

73: ,asg.change_reason
74: ,DECODE(next_asg.assignment_id, to_number(null), g_success, 1)
75: FROM per_all_assignments_f asg
76: ,per_assignment_status_types ast
77: ,per_all_assignments_f next_asg
78: WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
79: AND asg.assignment_id = p_assignment_id
80: AND ast.per_system_status = v_csr_system_status
81: AND next_asg.assignment_id (+) = asg.assignment_id

Line 167: per_all_assignments_f asg,

163: CURSOR smpl_hire_reason_cur IS
164: SELECT
165: asg.change_reason
166: FROM
167: per_all_assignments_f asg,
168: hr_lookups hrl
169: WHERE
170: asg.assignment_id = p_assignment_id
171: AND asg.change_reason = hrl.lookup_code

Line 180: per_all_assignments_f asg,

176: CURSOR prmry_hire_reason_cur IS
177: SELECT
178: asg.change_reason
179: FROM
180: per_all_assignments_f asg,
181: hr_lookups hrl
182: WHERE
183: asg.person_id = p_person_id
184: AND asg.effective_start_date = p_hire_date

Line 352: FROM per_all_assignments_f

348: /* Cursor returns 1 if the applicant assignment becomes an employee */
349: /* assignment */
350: CURSOR successful_asg_csr IS
351: SELECT 1
352: FROM per_all_assignments_f
353: WHERE assignment_id = p_assignment_id
354: AND effective_start_date > p_date_end
355: AND assignment_type = 'E';
356:

Line 361: FROM per_all_assignments_f prim

357: /* Cursor returns 1 if there is a date-tracked change to the primary */
358: /* assignment on the day after the application terminated */
359: CURSOR primary_success_csr IS
360: SELECT 1
361: FROM per_all_assignments_f prim
362: WHERE prim.person_id = p_person_id
363: AND prim.primary_flag = 'Y'
364: AND prim.effective_start_date = (p_date_end + 1);
365:

Line 449: FROM per_all_assignments_f asg -- Initial assignment record

445: NVL(asg.last_update_date,to_date('01-01-2000','DD-MM-YYYY')),
446: NVL(apl.last_update_date,to_date('01-01-2000','DD-MM-YYYY')))
447: last_update_date
448: ,asg.creation_date creation_date
449: FROM per_all_assignments_f asg -- Initial assignment record
450: ,per_applications apl -- Application for assignment
451: ,per_all_assignments_f emp_asg -- Pre-existing employee assignment
452: ,per_periods_of_service prev_pps -- Previously ended period of service
453: WHERE asg.assignment_type IN ('E','A')

Line 451: ,per_all_assignments_f emp_asg -- Pre-existing employee assignment

447: last_update_date
448: ,asg.creation_date creation_date
449: FROM per_all_assignments_f asg -- Initial assignment record
450: ,per_applications apl -- Application for assignment
451: ,per_all_assignments_f emp_asg -- Pre-existing employee assignment
452: ,per_periods_of_service prev_pps -- Previously ended period of service
453: WHERE asg.assignment_type IN ('E','A')
454: AND apl.application_id (+) = asg.application_id
455: AND asg.person_id = emp_asg.person_id (+)

Line 474: FROM per_all_assignments_f asg1

470: WHERE dummy.person_id = apl.person_id
471: AND dummy.date_start > prev_pps.date_start)
472: /* Filter out all but first assignment */
473: AND asg.effective_start_date = (SELECT MIN(asg1.effective_start_date)
474: FROM per_all_assignments_f asg1
475: WHERE asg1.assignment_id = asg.assignment_id);
476:
477: l_head NUMBER;
478: l_fte NUMBER;