[Home] [Help]
313: ,paa.employment_category EMP_CAT
314: ,pap.SEX
315: FROM
316: PER_ALL_ASSIGNMENTS_F paa
317: ,HR_SOFT_CODING_KEYFLEX scl1
318: ,PER_ALL_PEOPLE_F pap
319: WHERE
320: paa.person_id = pap.person_id
321: and paa.business_group_id = g_business_group_id
369: CURSOR csr_person_local_unit(p_local_unit_id number) is
370: SELECT DISTINCT paaf.person_id,sex
371: FROM per_all_assignments_f paaf,
372: per_all_people_f papf,
373: hr_soft_coding_keyflex hsck
374: WHERE paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
375: AND hsck.segment2=to_char(p_local_unit_id)
376: AND papf.person_id=paaf.person_id
377: and papf.SEX in('F','M')
413: /* To Get the assignments within the accounting month period, which are new hire*/
414: CURSOR csr_new_assignments(p_month_start_date date, p_month_end_date date ) IS
415: SELECT paaf.assignment_id,paaf.EFFECTIVE_START_DATE,paaf.employment_category
416: FROM PER_ALL_ASSIGNMENTS_F paaf,
417: hr_soft_coding_keyflex hsck
418: WHERE paaf.EFFECTIVE_START_DATE BETWEEN p_month_start_date
419: AND p_month_end_date
420: AND hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
421: AND hsck.segment2=to_char(g_local_unit_id)
426: /* To Get the previous assignments for the current assignment*/
427: CURSOR csr_prev_assignments(p_assignment_id number, p_effecive_start_date date) IS
428: SELECT hsck.segment2
429: FROM per_all_assignments_f paaf,
430: hr_soft_coding_keyflex hsck
431: WHERE hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
432: AND paaf.assignment_id=p_assignment_id
433: AND paaf.EFFECTIVE_START_DATE=
434: (SELECT max(EFFECTIVE_START_DATE)
451: /* To Get the assignments within the accounting month period, which are ended in one Local Unit*/
452: CURSOR csr_end_assignments(p_month_start_date date, p_month_end_date date ) IS
453: SELECT paaf.assignment_id,paaf.EFFECTIVE_end_DATE,paaf.employment_category
454: FROM PER_ALL_ASSIGNMENTS_F paaf,
455: hr_soft_coding_keyflex hsck
456: WHERE paaf.effective_end_date BETWEEN p_month_start_date
457: AND p_month_end_date
458: AND hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
459: AND hsck.segment2=to_char(g_local_unit_id)
464: /* To Get the assignments within the accounting month period, which are terminated */
465: CURSOR csr_ter_assignments(p_month_start_date date, p_month_end_date date ) IS
466: /*SELECT paaf.assignment_id,paaf.EFFECTIVE_START_DATE,paaf.employment_category
467: FROM PER_ALL_ASSIGNMENTS_F paaf,
468: hr_soft_coding_keyflex hsck
469: WHERE paaf.effective_end_date BETWEEN p_month_start_date
470: AND p_month_end_date
471: AND hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
472: AND hsck.segment2=to_char(g_local_unit_id)
474: and paaf.ASSIGNMENT_STATUS_TYPE_ID = 3;*/
475: SELECT paaf.assignment_id,paaf.EFFECTIVE_START_DATE,paaf.employment_category
476: FROM
477: PER_ALL_ASSIGNMENTS_F paaf,
478: hr_soft_coding_keyflex hsck
479: WHERE
480: paaf.effective_start_date BETWEEN p_month_start_date
481: AND p_month_end_date
482: AND hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
489: /* To Get the next assignments for the current assignment*/
490: CURSOR csr_next_assignments(p_assignment_id number, p_effecive_end_date date) IS
491: SELECT hsck.segment2
492: FROM per_all_assignments_f paaf,
493: hr_soft_coding_keyflex hsck
494: WHERE hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
495: AND paaf.assignment_id=p_assignment_id
496: AND paaf.effective_end_date=
497: (SELECT min(effective_end_date)
2267: hr_organization_information hoi_le,
2268: hr_all_organization_units hou_lu,
2269: hr_organization_information hoi_lu,
2270: PER_ALL_ASSIGNMENTS_F paa
2271: ,HR_SOFT_CODING_KEYFLEX scl1
2272: ,PER_ALL_PEOPLE_F pap
2273: WHERE
2274: hoi_le.organization_id = hou_le.organization_id
2275: AND hou_le.name =p_legal_employer