[Home] [Help]
208: where organization_id = p_org_id;
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
231: -- Cursor to get person ids of terminated employees
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
261: ORDER BY asg.person_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
280: and hscl.segment1 = to_char(p_org_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
305: -- Cursor to get person_ids,assignment_ids of terminated emps
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
347:
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
390: /*****end of new code*******/
391: --cursor to get job name for the newly hired employees
392: cursor get_job(p_assignment_id number,p_date date) is
393: select name
394: from per_jobs pj, per_all_assignments_f paf
395: where pj.job_id = paf.job_id
396: and paf.assignment_id = p_assignment_id
397: and trunc(p_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date;
398: --cursor to get the passport number of the newly hired employees
417: and trunc(p_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
418: --cursor to get GOSI number of the employees
419: cursor get_GOSI_number_new (p_person_id number) is
420: select segment2
421: from hr_soft_coding_keyflex hsc, per_all_assignments_f paf
422: where hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
423: and paf.person_id = p_person_id;
424: --cursor to get names of the terminated employees
425: cursor get_name_term (p_person_id number,p_date date) is
427: and trunc(p_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
428: --cursor to get GOSI numbers of the terminated employees
429: cursor get_GOSI_number_term(p_person_id number) is
430: select segment2
431: from hr_soft_coding_keyflex hsc, per_all_assignments_f paf
432: where hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
433: and paf.person_id = p_person_id;
434: --cursor to get the termination details of the terminated employees
435: cursor get_details_term (p_person_id number) is
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
1981: ,per_time_periods ptp
1982: ,pay_run_results prr
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
2009: ,per_time_periods ptp
2010: ,pay_run_results prr
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
2037: ,per_time_periods ptp
2038: ,pay_run_results prr
2650: per_information2,
2651: per_information10,
2652: last_name
2653: from per_all_people_f peo
2654: ,per_all_assignments_f paa
2655: where peo.person_id = paa.person_id
2656: and paa.assignment_id = l_assignment_id;
2657: --and l_effective_date between paa.effective_start_date and paa.effective_end_date
2658: --and l_effective_date between peo.effective_start_date and peo.effective_end_date;
2658: --and l_effective_date between peo.effective_start_date and peo.effective_end_date;
2659: /*Cursor to select assignments from a given GRE*/
2660: cursor csr_get_gre_assignments (l_employer_id number, l_business_group_id number, l_effective_date date,l_form_type varchar2) is
2661: select /*+ INDEX(hsck, HR_SOFT_CODING_KEYFLEX_PK) */ distinct assignment_id
2662: from per_all_assignments_f paa,
2663: hr_soft_coding_keyflex hsck,
2664: per_periods_of_service pos
2665: where hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
2666: and (nvl(hsck.segment3,'N') = 'Y' OR nvl(hsck.segment5,'N') = 'Y')
2683: /*Cursor to select assignments for a given assignment set*/
2684: cursor csr_get_assignment(l_assignment_set_id number, l_form_type varchar2) is
2685: select distinct has.assignment_id
2686: from hr_assignment_set_amendments has
2687: ,per_all_assignments_f paa
2688: ,per_periods_of_service pos
2689: ,hr_soft_coding_keyflex hsck
2690: where assignment_set_id = l_assignment_set_id
2691: and include_or_exclude = 'I'
2710: /* Cursor to select Employee GOSI Number */
2711: cursor get_emp_gosi (l_assignment_id NUMBER, l_effective_date Date) is
2712: select hscl.segment2
2713: from hr_soft_coding_keyflex hscl
2714: ,per_all_assignments_f paa
2715: where hscl.SOFT_CODING_KEYFLEX_ID = paa.soft_CODING_KEYFLEX_ID
2716: and paa.assignment_id = l_assignment_id
2717: and hscl.id_flex_num = 20;
2718: --and l_effective_date between paa.effective_start_date and paa.effective_end_date;
2722: ,hla.region_2
2723: ,hla.region_3
2724: from hr_locations_all hla
2725: ,hr_organization_units hou
2726: ,per_all_assignments_f paa
2727: where paa.organization_id = hou.organization_id
2728: and hou.location_id = hla.location_id
2729: and paa.assignment_id = l_assignment_id
2730: --and l_effective_date between paa.effective_start_date and paa.effective_end_date
2731: and paa.business_group_id = hou.business_group_id;
2732: /* Cursor to get person_id*/
2733: CURSOR get_person_id (l_assignment_id NUMBER) IS
2734: SELECT person_id
2735: FROM per_all_assignments_f
2736: WHERE assignment_id = l_assignment_id;
2737: /*Cursor to pick up employee primary address*/
2738: CURSOR get_town(l_person_id NUMBER, l_effective_date DATE) IS
2739: SELECT pa.town_or_city city,
2752: ,peo.sex
2753: ,peo.marital_status
2754: ,peo.date_of_birth
2755: from per_all_people_f peo
2756: ,per_all_assignments_f paa
2757: where peo.person_id = paa.person_id
2758: and paa.assignment_id = l_assignment_id;
2759: --and l_effective_date between paa.effective_start_date and paa.effective_end_date
2760: --and l_effective_date between peo.effective_start_date and peo.effective_end_date;
2761: /*Cursor to pick up Employee's Job Name*/
2762: cursor get_job (l_assignment_id NUMBER, l_effective_date Date) is
2763: select name
2764: from per_jobs pj,
2765: per_all_assignments_f paa
2766: where pj.job_id = paa.job_id
2767: and paa.business_group_id = pj.business_group_id
2768: and paa.assignment_id = l_assignment_id;
2769: --and l_effective_date between paa.effective_start_date and paa.effective_end_date;
2771: cursor get_work_location (l_assignment_id NUMBER) is
2772: select hlp.meaning --distinct TOWN_OR_CITY
2773: from hr_locations hl,
2774: hr_lookups hlp,
2775: per_all_assignments_f paa
2776: where hl.location_id = paa.location_id
2777: and paa.assignment_id = l_assignment_id
2778: and hlp.lookup_type = 'SA_CITY'
2779: and hlp.lookup_code = hl.TOWN_OR_CITY;
2808: select pps.date_start
2809: ,pps.actual_termination_date
2810: ,pps.leaving_reason
2811: from per_periods_of_service pps
2812: ,per_all_assignments_f paa
2813: where /*pps.person_id = paa.person_id*/
2814: pps.period_of_service_id = paa.period_of_service_id
2815: and paa.business_group_id = pps.business_group_id
2816: and paa.assignment_id = l_assignment_id;
2819: cursor get_hazards_date (l_assignment_id NUMBER, l_effective_date Date) is
2820: select hscl.segment4,
2821: hscl.segment6
2822: from hr_soft_coding_keyflex hscl
2823: ,per_all_assignments_f paa
2824: where hscl.SOFT_CODING_KEYFLEX_ID = paa.soft_CODING_KEYFLEX_ID
2825: and paa.assignment_id = l_assignment_id
2826: and hscl.id_flex_num = 20;
2827: --and l_effective_date between paa.effective_start_date and paa.effective_end_date;
2828: /*Cursor to get employer for the employee*/
2829: cursor get_employer (l_assignment_id NUMBER, l_effective_date Date) is
2830: select hscl.segment1
2831: from hr_soft_coding_keyflex hscl
2832: ,per_all_assignments_f paa
2833: where hscl.SOFT_CODING_KEYFLEX_ID = paa.soft_CODING_KEYFLEX_ID
2834: and paa.assignment_id = l_assignment_id
2835: and hscl.id_flex_num = 20;
2836: --and l_effective_date between paa.effective_start_date and paa.effective_end_date;
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
2847: and ppa.payroll_id = paf.payroll_id
2944: if p_assignment_set_id is not null then
2945: begin
2946: select 1 into i
2947: from hr_assignment_set_amendments haa
2948: ,per_all_assignments_f paa
2949: ,hr_soft_coding_keyflex hscl
2950: ,per_periods_of_service pos
2951: where assignment_set_id = p_assignment_set_id
2952: and include_or_exclude = 'I'
2978: else
2979: begin
2980: select distinct paa.assignment_id
2981: into t_emp(0).emp_id
2982: from per_all_assignments_f paa,
2983: per_periods_of_service pos,
2984: hr_soft_coding_keyflex hsck
2985: where paa.assignment_id = p_assignment_id
2986: --and p_effective_date between paa.effective_start_date and paa.effective_end_date
3427: BEGIN
3428: select distinct TOWN_OR_CITY
3429: into l_work_location
3430: from hr_locations hl,
3431: per_all_assignments_f paa
3432: where hl.location_id = paa.location_id
3433: And paa.assignment_id = t_emp(j).emp_id;
3434: exception
3435: WHEN OTHERS THEN NULL;