[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)
576: ,fnd_date.date_to_canonical(pep.date_of_birth) dob
577: ,pep.sex gender
578: ,pep.per_information7 residential_status
579: ,pep.email_address emailAddr
580: FROM per_all_assignments_f asg
581: ,hr_soft_coding_keyflex scl
582: ,per_all_people_f pep
583: WHERE asg.assignment_id = p_assignment_id
584: AND pep.person_id = asg.person_id
642: AND phone_type = DECODE(phone_type,'H1','H1','M')
643: AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'))
644: ORDER BY phone_type ASC;
645:
646: l_emp_no per_all_assignments_f.assignment_number%TYPE;
647: l_person_id per_all_people_f.person_id%TYPE;
648: l_dob VARCHAR2(30);
649: l_pan per_all_people_f.per_information4%TYPE;
650: l_residential_status per_all_people_f.per_information7%TYPE;
2528:
2529: CURSOR c_rep_address(p_person_id NUMBER)
2530: IS
2531: SELECT hou.location_id rep_location
2532: FROM per_all_assignments_f asg
2533: ,hr_organization_units hou
2534: WHERE asg.person_id = p_person_id
2535: AND asg.primary_flag = 'Y'
2536: AND asg.business_group_id = g_bg_id
2726: IS
2727: SELECT paa.assignment_id
2728: ,paa.payroll_action_id
2729: FROM pay_assignment_actions paa
2730: ,per_all_assignments_f paf
2731: WHERE paa.assignment_action_id = p_assignment_action_id
2732: AND paa.assignment_id = paf.assignment_id
2733: AND ROWNUM =1;
2734:
2736: IS
2737: SELECT GREATEST(asg.effective_start_date,g_start_date) start_date
2738: ,LEAST(asg.effective_end_date,g_end_date) end_date
2739: ,scl.segment1
2740: FROM per_all_assignments_f asg
2741: ,hr_soft_coding_keyflex scl
2742: ,pay_assignment_actions paa
2743: WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
2744: AND paa.assignment_action_id = p_assignment_action_id
2816: --This cursor determines termination date of an assignment.
2817: CURSOR c_termination_check(p_assignment_id NUMBER)
2818: IS
2819: SELECT NVL(pos.actual_termination_date,(fnd_date.string_to_date('31-12-4712','DD-MM-YYYY')))
2820: FROM per_all_assignments_f asg
2821: ,per_periods_of_service pos
2822: WHERE asg.person_id = pos.person_id
2823: AND asg.assignment_id = p_assignment_id
2824: AND asg.business_group_id = pos.business_group_id