[Home] [Help]
247: --This cursor determines termination date of an assignment.
248: CURSOR c_termination_check
249: IS
250: SELECT NVL(pos.actual_termination_date,(fnd_date.string_to_date('31-12-4712','DD-MM-YYYY')))
251: FROM per_all_assignments_f asg
252: ,per_periods_of_service pos
253: WHERE asg.person_id = pos.person_id
254: AND asg.assignment_id = p_assignment_id
255: AND asg.business_group_id = pos.business_group_id
260: --This cursor determines the GRE/Legal Entity as on the end of financial year.
261: CURSOR c_gre_id
262: IS
263: SELECT 1
264: FROM per_all_assignments_f asg
265: ,hr_soft_coding_keyflex scl
266: WHERE asg.assignment_id = p_assignment_id
267: AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
268: AND scl.segment1 = TO_CHAR(g_gre_id)
274: --the complete period starting from g_start_date and ending on g_end_date.
275: CURSOR c_gre_count
276: IS
277: SELECT COUNT(DISTINCT scl.segment1)
278: FROM per_all_assignments_f asg
279: ,hr_soft_coding_keyflex scl
280: WHERE asg.assignment_id = p_assignment_id
281: AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
282: AND ( asg.effective_start_date BETWEEN g_start_date AND g_end_date
288: --employee in a GRE in a given financial year.
289: CURSOR c_gre_employee
290: IS
291: SELECT 1
292: FROM per_all_assignments_f asg
293: ,hr_soft_coding_keyflex scl
294: WHERE asg.assignment_id = p_assignment_id
295: AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
296: AND scl.segment1 = TO_CHAR(g_gre_id)
575: ,pep.title title
576: ,fnd_date.date_to_canonical(pep.date_of_birth) dob
577: ,pep.sex gender
578: ,pep.per_information7 residential_status
579: FROM per_all_assignments_f asg
580: ,hr_soft_coding_keyflex scl
581: ,per_all_people_f pep
582: WHERE asg.assignment_id = p_assignment_id
583: AND pep.person_id = asg.person_id
641: AND phone_type = DECODE(phone_type,'H1','H1','M')
642: AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'))
643: ORDER BY phone_type ASC;
644:
645: l_emp_no per_all_assignments_f.assignment_number%TYPE;
646: l_person_id per_all_people_f.person_id%TYPE;
647: l_dob VARCHAR2(30);
648: l_pan per_all_people_f.per_information4%TYPE;
649: l_residential_status per_all_people_f.per_information7%TYPE;
2462:
2463: CURSOR c_rep_address(p_person_id NUMBER)
2464: IS
2465: SELECT hou.location_id rep_location
2466: FROM per_all_assignments_f asg
2467: ,hr_organization_units hou
2468: WHERE asg.person_id = p_person_id
2469: AND asg.primary_flag = 'Y'
2470: AND asg.business_group_id = g_bg_id
2631: IS
2632: SELECT paa.assignment_id
2633: ,paa.payroll_action_id
2634: FROM pay_assignment_actions paa
2635: ,per_all_assignments_f paf
2636: WHERE paa.assignment_action_id = p_assignment_action_id
2637: AND paa.assignment_id = paf.assignment_id
2638: AND ROWNUM =1;
2639:
2641: IS
2642: SELECT GREATEST(asg.effective_start_date,g_start_date) start_date
2643: ,LEAST(asg.effective_end_date,g_end_date) end_date
2644: ,scl.segment1
2645: FROM per_all_assignments_f asg
2646: ,hr_soft_coding_keyflex scl
2647: ,pay_assignment_actions paa
2648: WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
2649: AND paa.assignment_action_id = p_assignment_action_id
2721: --This cursor determines termination date of an assignment.
2722: CURSOR c_termination_check(p_assignment_id NUMBER)
2723: IS
2724: SELECT NVL(pos.actual_termination_date,(fnd_date.string_to_date('31-12-4712','DD-MM-YYYY')))
2725: FROM per_all_assignments_f asg
2726: ,per_periods_of_service pos
2727: WHERE asg.person_id = pos.person_id
2728: AND asg.assignment_id = p_assignment_id
2729: AND asg.business_group_id = pos.business_group_id