[Home] [Help]
232: substrb(pp.national_identifier,1,6) res_reg_num,
233: pp.national_identifier ni_num,
234: pp.employee_number emp_num
235: from per_kr_assignment_yea_info pkayi,
236: per_people_f pp,
237: per_periods_of_service ppos,
238: per_assignments_f pa
239: where pa.business_group_id + 0 = g_business_group_id
240: and pa.establishment_id = nvl(g_business_place_id,pa.establishment_id)
252: and pp.effective_end_date >= g_target_soy
253: /* fetch latest emp information */
254: and not exists(
255: select null
256: from per_people_f pp2
257: where pp2.person_id = pa.person_id
258: and pp2.effective_start_date <= g_target_eoy
259: and pp2.effective_end_date >= g_target_soy
260: and pp2.effective_start_date < pp.effective_start_date)
286: fl.file_data pdf_file
287: from per_kr_assignment_yea_info pkayi,
288: per_all_assignments_f pa,
289: per_periods_of_service ppos,
290: per_people_f pp,
291: fnd_lobs fl
292: where pkayi.assignment_yea_info_id = nvl(p_assignment_yea_info_id,pkayi.assignment_yea_info_id)
293: and pkayi.information_type = 'KR_YEA_ENTRY_STATUS'
294: and pkayi.ayi_information_category = 'KR_YEA_ENTRY_STATUS'
309: and pp.effective_end_date >= g_target_soy
310: /* fetch latest emp information */
311: and not exists(
312: select null
313: from per_people_f pp2
314: where pp2.person_id = pa.person_id
315: and pp2.effective_start_date <= g_target_eoy
316: and pp2.effective_end_date >= g_target_soy
317: and pp2.effective_start_date < pp.effective_start_date)
2829: 'SISTER','5',
2830: '6')) code
2831: from per_assignments_f pa,
2832: per_contact_relationships pcr,
2833: per_people_f pcr_pp
2834: where pa.assignment_id = p_assignment_id
2835: and g_target_eoy
2836: between pa.effective_start_date and pa.effective_end_date
2837: and pcr.person_id = pa.person_id
2839: and g_target_eoy
2840: between pcr_pp.effective_start_date and pcr_pp.effective_end_date
2841: UNION ALL
2842: select ppf.national_identifier, '0' code
2843: from per_people_f ppf
2844: ,per_assignments_f pa
2845: where pa.person_id = ppf.person_id
2846: and pa.assignment_id = p_assignment_id
2847: and g_target_eoy between pa.effective_start_date and pa.effective_end_date
2858: decode(nvl(cont_information11,
2859: decode(contact_type, 'S','3','P','1','B','5','SISTER','5' ,'A','4','C','4','R','4','O','4','T','4','6')),'1','4','5','5','4','3','3','2','7','3','2','4','6') code
2860: from per_assignments_f pa,
2861: per_contact_relationships pcr,
2862: per_people_f pcr_pp
2863: where pa.assignment_id = p_assignment_id
2864: and g_target_eoy between pa.effective_start_date and pa.effective_end_date
2865: and pcr.person_id = pa.person_id
2866: and pcr_pp.person_id = pcr.contact_person_id
2868: and pcr.cont_information1 ='Y'
2869: and pcr.cont_information2 = 'Y'
2870: UNION ALL
2871: select ppf.national_identifier, '0' code
2872: from per_people_f ppf
2873: ,per_assignments_f pa
2874: where pa.person_id = ppf.person_id
2875: and pa.assignment_id = p_assignment_id
2876: and g_target_eoy between pa.effective_start_date and pa.effective_end_date
2896: 'SISTER','5',
2897: '6')) code
2898: from per_assignments_f pa,
2899: per_contact_relationships pcr,
2900: per_people_f pcr_pp
2901: where pa.assignment_id = p_assignment_id
2902: and g_target_eoy
2903: between pa.effective_start_date and pa.effective_end_date
2904: and pa.assignment_type ='E'
7001: select pa.assignment_id,
7002: pp.last_name||pp.first_name emp_name,
7003: substrb(pp.national_identifier,1,6) res_reg_num,
7004: pp.employee_number emp_num
7005: from per_people_f pp,
7006: per_periods_of_service ppos,
7007: per_assignments_f pa,
7008: hr_organization_information hoi,
7009: per_business_groups_perf pbg
7028: and pp.effective_end_date >= g_target_soy
7029: /* fetch latest emp information */
7030: and not exists(
7031: select null
7032: from per_people_f pp2
7033: where pp2.person_id = pa.person_id
7034: and pp2.effective_start_date <= g_target_eoy
7035: and pp2.effective_end_date >= g_target_soy
7036: and pp2.effective_start_date < pp.effective_start_date)