[Home] [Help]
32: srch_filtrd srch_filtered;
33:
34: -- temporary record for ease of access
35: p_bus_group_id number default null;
36: p_emplmt_category per_assignments_f2.employee_category%type default null;
37: p_empl_category per_assignments_f2.EMPLOYMENT_CATEGORY%type default null;
38:
39: -- temporary record for ease of access
40: -- cursor to fetch the person records
33:
34: -- temporary record for ease of access
35: p_bus_group_id number default null;
36: p_emplmt_category per_assignments_f2.employee_category%type default null;
37: p_empl_category per_assignments_f2.EMPLOYMENT_CATEGORY%type default null;
38:
39: -- temporary record for ease of access
40: -- cursor to fetch the person records
41: cursor csr_person_record(p_person_id number,p_business_group_id number,p_eff_date date) is
412: and nvl(end_date_active,p_eff_Date)
413: and enabled_flag = 'Y') EMPLOYEE_CATEGORY_DESC
414: , WORK_AT_HOME
415: , JOB_POST_SOURCE_NAME
416: , '' POSTING_CONTENT_ID -- column not exist in PER_ASSIGNMENTS_F2
417: , to_char(PERIOD_OF_PLACEMENT_DATE_START,'YYYY-MM-DD')
418: , VENDOR_ID
419: , VENDOR_EMPLOYEE_NUMBER
420: , VENDOR_ASSIGNMENT_NUMBER
419: , VENDOR_EMPLOYEE_NUMBER
420: , VENDOR_ASSIGNMENT_NUMBER
421: , ASSIGNMENT_CATEGORY
422: , PROJECT_TITLE
423: , '' APPLICANT_RANK -- column not exist in PER_ASSIGNMENTS_F2
424: , VENDOR_SITE_ID
425: , PO_HEADER_ID
426: , PO_LINE_ID
427: , PROJECTED_ASSIGNMENT_END
427: , PROJECTED_ASSIGNMENT_END
428: , GRADE_LADDER_PGM_ID
429: , '' GRADE_LADDER_PGM_NAME
430: , SUPERVISOR_ASSIGNMENT_ID
431: FROM PER_ASSIGNMENTS_F2 paaf,
432: PER_ASSIGNMENT_STATUS_TYPES ST ,
433: PER_ASSIGNMENT_STATUS_TYPES_TL STTL
434: WHERE paaf.person_id = p_person_id
435: AND paaf.business_group_id = p_business_group_id
1137: , AEI_INFORMATION28
1138: , AEI_INFORMATION29
1139: , AEI_INFORMATION30
1140: FROM PER_ASSIGNMENT_EXTRA_INFO
1141: WHERE assignment_id in (SELECT assignment_id from PER_ASSIGNMENTS_F
1142: WHERE person_id = p_person_id
1143: and business_group_id = p_business_group_id
1144: and p_eff_date between effective_start_date and nvl(effective_end_date,to_date('31/12/4712','Dd/MM/YYYY')));
1145:
1432: papf.assignment_status_type_id,
1433: (SELECT USER_STATUS FROM PER_ASSIGNMENT_STATUS_TYPES_TL
1434: WHERE ASSIGNMENT_STATUS_TYPE_ID = papf.assignment_status_type_id
1435: AND LANGUAGE = USERENV('LANG')) REPORTEE_ASG_STATUS_DESC
1436: FROM per_assignments_f papf , per_people_f ppf
1437: WHERE
1438: p_eff_date BETWEEN papf.effective_start_date AND papf.effective_end_date
1439: and p_eff_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1440: and papf.business_group_id = P_BUSINESS_GROUP_ID
1953: ,paa.assignment_action_id
1954: , paf.location_id
1955: from pay_payroll_actions ppa
1956: ,pay_assignment_actions paa
1957: ,per_assignments_f paf
1958: ,per_people_f ppf
1959: ,hr_lookups hl
1960: where ppa.action_type = 'X'
1961: and ppa.action_status = 'C'
2505: ELSE
2506: open srch_filtrd for
2507:
2508: select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
2509: from per_people_f ppf ,per_person_types ppt ,per_assignments_f paaf
2510: where nvl(p_srch_criteria(i).p_person_id, ppf.person_id) = ppf.person_id
2511: and ppf.person_id between nvl(p_srch_criteria(i).p_start_person_id, ppf.person_id)
2512: and nvl(p_srch_criteria(i).p_end_person_id, ppf.person_id)
2513: and ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)