[Home] [Help]
209: /********************-- Cursor to get person ids of the newly hired employees
210: cursor get_pid_new (p_org_id number,p_date date) is
211: select distinct asg.person_id,paa.assignment_action_id
212: from per_all_assignments_f asg
213: ,pay_assignment_actions paa
214: ,pay_payroll_actions ppa
215: ,hr_soft_coding_keyflex hscl
216: ,per_periods_of_service pos
217: where asg.assignment_id = paa.assignment_id
232: cursor get_pid_term (p_org_id number,p_date date) is
233: --and trunc(pps.actual_termination_date,'MM') = p_date;
234: select distinct asg.person_id, paa.assignment_action_id
235: from per_all_assignments_f asg
236: ,pay_assignment_actions paa
237: ,pay_payroll_actions ppa
238: ,hr_soft_coding_keyflex hscl
239: ,per_periods_of_service pos
240: where asg.assignment_id = paa.assignment_id
262: /***Start of new code***/
263: -- Cursor to get person_ids,assignment_ids of newly joined emps
264: cursor get_pid_new (p_org_id number,p_date date) is
265: select distinct asg.person_id,asg.assignment_id from per_all_assignments_f asg
266: ,pay_assignment_actions paa
267: ,pay_payroll_actions ppa
268: ,hr_soft_coding_keyflex hscl
269: ,per_periods_of_service pos
270: where asg.assignment_id = paa.assignment_id
281: order by asg.person_id;
282: --Cursor to get assignment_action_id of new emps
283: cursor get_assact_id_new (p_org_id number,p_date date,p_person_id number) is
284: select paa.assignment_action_id from per_all_assignments_f asg
285: ,pay_assignment_actions paa
286: ,pay_payroll_actions ppa
287: ,hr_soft_coding_keyflex hscl
288: ,per_periods_of_service pos
289: where rownum < 2
306: cursor get_pid_term (p_org_id number,p_date date) is
307: --and trunc(pps.actual_termination_date,'MM') = p_date;
308: select /*+ INDEX(hscl, HR_SOFT_CODING_KEYFLEX_PK) */ distinct asg.person_id, asg.assignment_id
309: from per_all_assignments_f asg
310: ,pay_assignment_actions paa
311: ,pay_payroll_actions ppa
312: ,hr_soft_coding_keyflex hscl
313: ,per_periods_of_service pos
314: where asg.assignment_id = paa.assignment_id
348: -- Cursor to get assignment_action_ids of terminated emps
349: cursor get_assact_id_term (p_org_id number,p_date date,p_person_id number) is
350: select paa.assignment_action_id
351: from per_all_assignments_f asg
352: ,pay_assignment_actions paa
353: ,pay_payroll_actions ppa
354: ,hr_soft_coding_keyflex hscl
355: ,per_periods_of_service pos
356: where rownum < 2
1798: ,l_p_joiner_haz
1799: ,l_p_leaver_saudi_ann
1800: ,l_p_leaver_saudi_ann_haz
1801: ,l_p_leaver_haz
1802: FROM pay_assignment_actions paa
1803: ,pay_action_contexts ac1
1804: ,ff_contexts ct1
1805: ,pay_action_contexts ac2
1806: ,ff_contexts ct2
1924: ,l_c_joiner_haz
1925: ,l_c_leaver_saudi_ann
1926: ,l_c_leaver_saudi_ann_haz
1927: ,l_c_leaver_haz
1928: FROM pay_assignment_actions paa
1929: ,pay_action_contexts ac1
1930: ,ff_contexts ct1
1931: ,pay_action_contexts ac2
1932: ,ff_contexts ct2
1972: declare
1973: /******Code for fetching contribution of leavers******/
1974: cursor csr_get_leav_assact is
1975: select distinct paa.assignment_action_id, paf.person_id
1976: from pay_assignment_actions paa
1977: ,pay_payroll_actions ppa
1978: ,per_all_assignments_f paf
1979: ,per_periods_of_service pps
1980: ,hr_soft_coding_keyflex hscl
2000: rec_leav_assact csr_get_leav_assact%ROWTYPE;
2001: /******Code for fetching contribution of saudi leaver employees paying only annuities*****/
2002: cursor csr_saudi_l_ann_assact is
2003: select distinct paa.assignment_action_id, paf.person_id
2004: from pay_assignment_actions paa
2005: ,pay_payroll_actions ppa
2006: ,per_all_assignments_f paf
2007: ,per_periods_of_service pps
2008: ,hr_soft_coding_keyflex hscl
2028: rec_l_ann_assact csr_saudi_l_ann_assact%ROWTYPE;
2029: /******Code for fetching contribution of saudi joiner employees paying only annuities*****/
2030: cursor csr_saudi_j_ann_assact is
2031: select distinct paa.assignment_action_id, paf.person_id
2032: from pay_assignment_actions paa
2033: ,pay_payroll_actions ppa
2034: ,per_all_assignments_f paf
2035: ,per_periods_of_service pps
2036: ,hr_soft_coding_keyflex hscl
2838: --get defined_balance_id
2839: /*Cursor to pick up assignment actions*/
2840: cursor get_assact_id (l_assignment_id NUMBER, l_effective_date Date) is
2841: select paa.assignment_action_id
2842: from pay_assignment_actions paa
2843: ,per_all_assignments_f paf
2844: ,pay_payroll_actions ppa
2845: where paa.assignment_id = paf.assignment_id
2846: and paf.assignment_id = l_assignment_id