[Home] [Help]
35: -- This function return NOMATCH If the value for HR: Local Nationality
36: -- Profile does not match with the person's nationality else it retuns MATCH.
37: ------------------------------------------------------------------------
38: function local_nationality_matches
39: (p_assignment_id IN per_all_assignments_f.assignment_id%type,
40: p_date_earned IN Date)
41: return varchar2
42: is
43: begin
43: begin
44: BEGIN
45: Select person_id
46: Into l_person_id
47: From PER_ALL_ASSIGNMENTS_F
48: Where ASSIGNMENT_ID = p_assignment_id
49: AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
50: EXCEPTION
51: WHEN no_data_found Then
259: FUNCTION chk_multiple_assignments(p_effective_date IN DATE
260: ,p_person_id IN NUMBER) RETURN VARCHAR2 AS
261: CURSOR get_multiple_assgts IS
262: SELECT count(DISTINCT paf.assignment_id)
263: FROM per_all_assignments_f paf
264: ,per_assignment_status_types pas
265: WHERE paf.assignment_type = 'E'
266: AND paf.PERSON_ID = p_person_id
267: AND p_effective_date between effective_start_date and effective_end_date
407: ------------------------------------------------------------------------
408: -- Function get_contract
409: ------------------------------------------------------------------------
410: FUNCTION get_contract
411: (p_assignment_id IN per_all_assignments_f.assignment_id%type,
412: p_date_earned IN Date)
413: RETURN VARCHAR2 IS
414: l_contract VARCHAR2(30);
415: BEGIN
416: BEGIN
417: SELECT cont.type
418: INTO l_contract
419: FROM per_contracts_f cont
420: ,per_all_assignments_f asg
421: WHERE asg.assignment_id = p_assignment_id
422: AND asg.contract_id = cont.contract_id
423: AND p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
424: AND p_date_earned BETWEEN cont.effective_start_date AND cont.effective_end_date;
432: ------------------------------------------------------------------------
433: -- Function get_contract_expiry_status
434: ------------------------------------------------------------------------
435: FUNCTION get_contract_expiry_status
436: (p_assignment_id IN per_all_assignments_f.assignment_id%type,
437: p_date_earned IN Date)
438: RETURN VARCHAR2 IS
439: l_contract VARCHAR2(30);
440: l_expiry_date DATE;
445:
446: SELECT cont.type, fnd_date.canonical_to_date(cont.ctr_information2)
447: INTO l_contract, l_expiry_date
448: FROM per_contracts_f cont
449: ,per_all_assignments_f asg
450: WHERE asg.assignment_id = p_assignment_id
451: AND asg.contract_id = cont.contract_id
452: AND p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
453: AND p_date_earned BETWEEN cont.effective_start_date AND cont.effective_end_date;
469: ------------------------------------------------------------------------
470: -- Function get_termination_initiator
471: ------------------------------------------------------------------------
472: function get_termination_initiator
473: (p_assignment_id IN per_all_assignments_f.assignment_id%type,
474: p_date_earned IN Date)
475: RETURN VARCHAR2 IS
476: l_leav_reason per_periods_of_service.leaving_reason%TYPE;
477: l_initiator VARCHAR2(20);
479: l_initiator :='EE';
480: BEGIN
481: SELECT pos.leaving_reason
482: INTO l_leav_reason
483: FROM per_all_assignments_f assign
484: ,per_periods_of_service pos
485: WHERE p_date_earned BETWEEN assign.effective_start_date AND assign.effective_end_date
486: AND assign.assignment_id = p_assignment_id
487: AND assign.period_of_service_id = pos.period_of_service_id;
516: ------------------------------------------------------------------------
517: -- Function user_gratuity_formula_exists
518: ------------------------------------------------------------------------
519: FUNCTION user_gratuity_formula_exists
520: (p_assignment_id IN per_all_assignments_f.assignment_id%type,
521: p_date_earned IN Date)
522: RETURN VARCHAR2 IS
523: cursor csr_get_formula_id is
524: select HOI2.org_information1
525: from hr_organization_units HOU
526: ,hr_organization_information HOI1
527: ,hr_organization_information HOI2
528: ,hr_soft_coding_keyflex HSCK
529: ,per_all_assignments_f PAA
530: where HOU.business_group_id = PAA.business_group_id
531: and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
532: to_date('4712/12/31','YYYY/MM/DD'))
533: and HOU.organization_id = HOI1.organization_id
631: from hr_organization_units HOU
632: ,hr_organization_information HOI1
633: ,hr_organization_information HOI2
634: ,hr_soft_coding_keyflex HSCK
635: ,per_all_assignments_f PAA
636: where HOU.business_group_id = PAA.business_group_id
637: and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
638: to_date('4712/12/31','YYYY/MM/DD'))
639: and HOU.organization_id = HOI1.organization_id
717: from hr_organization_units HOU
718: ,hr_organization_information HOI1
719: ,hr_organization_information HOI2
720: ,hr_soft_coding_keyflex HSCK
721: ,per_all_assignments_f PAA
722: where HOU.business_group_id = PAA.business_group_id
723: and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
724: to_date('4712/12/31','YYYY/MM/DD'))
725: and HOU.organization_id = HOI1.organization_id
803: SELECT SUM(paa.absence_days) --(NVL(paa.absence_days, (paa.DATE_END - paa.DATE_START))
804: INTO l_days
805: FROM per_absence_attendances paa
806: ,per_absence_attendance_types paat
807: ,per_all_assignments_f asg
808: WHERE paat.absence_category ='UL'
809: AND paat.business_group_id = paa.business_group_id
810: AND paat.business_group_id = p_business_group_id
811: AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
837: from hr_organization_units HOU
838: ,hr_organization_information HOI1
839: ,hr_organization_information HOI2
840: ,hr_soft_coding_keyflex HSCK
841: ,per_all_assignments_f PAA
842: where HOU.business_group_id = PAA.business_group_id
843: and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
844: to_date('4712/12/31','YYYY/MM/DD'))
845: and HOU.organization_id = HOI1.organization_id