[Home] [Help]
1029: /* Get the details of the ETP Continuous Service Date and Pre/Post Days
1030: if Entered for the calculation. These details if provided by the user
1031: will be used in the ETP payments calculation. */
1032:
1033: cursor c_get_etp_information(c_assignment_id per_all_assignments_f.assignment_id%TYPE)
1034: is
1035: select peev.screen_entry_value,
1036: peev1.screen_entry_value,
1037: to_date(peev2.screen_entry_value,'YYYY/MM/DD HH24:MI:SS')
1342: -- Bug#3263690 - NGE calculation Enhancement.
1343: -- Function to calculate the Normal Gross Earnings for a given assignment
1344: --
1345: FUNCTION CALCULATE_TERM_ASG_NGE
1346: ( p_assignment_id in per_all_assignments_f.assignment_id%TYPE,
1347: p_business_group_id in hr_all_organization_units.organization_id%TYPE,
1348: p_date_earned in date,
1349: p_tax_unit_id in hr_all_organization_units.organization_id%TYPE,
1350: p_assignment_action_id IN number, /*Bug 4538463*/
1401: -- If there exists any LE changes, then it gets the max
1402: -- Assignment Action ID for the corresponding LE.
1403: -----------------------------------------------------------------------
1404: CURSOR c_get_prev_year_max_asg_act_id
1405: ( c_assignment_id in per_all_assignments_f.assignment_id%TYPE,
1406: c_business_group_id in hr_all_organization_units.organization_id%TYPE,
1407: c_fin_start_date in date,
1408: c_fin_end_date in date)
1409: IS
1417: (
1418: SELECT MAX(paa.action_sequence)
1419: FROM pay_assignment_actions paa,
1420: pay_payroll_actions ppa,
1421: per_all_assignments_f paaf
1422: WHERE ppa.business_group_id = c_business_group_id
1423: AND paaf.assignment_id = c_assignment_id
1424: AND paa.assignment_id = paaf.assignment_id
1425: AND ppa.payroll_action_id = paa.payroll_action_id
1431: ORDER BY date_earned desc;
1432:
1433: /*Bug 2610141 - Cursor introduced to give the maxmimum assignment action id of previous legal employer*/
1434: CURSOR c_get_pre_le_max_asg_act_id
1435: ( c_assignment_id in per_all_assignments_f.assignment_id%TYPE,
1436: c_business_group_id in hr_all_organization_units.organization_id%TYPE,
1437: c_fin_start_date in date,
1438: c_fin_end_date in date)
1439: IS
1447: (
1448: SELECT MAX(paa.action_sequence)
1449: FROM pay_assignment_actions paa,
1450: pay_payroll_actions ppa,
1451: per_all_assignments_f paaf
1452: WHERE ppa.business_group_id = c_business_group_id
1453: AND paaf.assignment_id = c_assignment_id
1454: AND paa.assignment_id = paaf.assignment_id
1455: AND ppa.payroll_action_id = paa.payroll_action_id
1503: pay_assignment_actions paa
1504: WHERE paa.assignment_action_id = p_assignment_action_id
1505: AND ppa.payroll_action_id = paa.payroll_action_id;
1506:
1507: CURSOR c_check_payroll_run (c_assignment_id in per_all_assignments_f.assignment_id%TYPE,
1508: c_business_group_id in hr_all_organization_units.organization_id%TYPE,
1509: c_start_date in date,
1510: c_end_date in date)
1511: IS
2131: -- RETURNS: Preservation Age
2132: -- IN : Assignment Id
2133: -- : Date Earned
2134: FUNCTION au_check_trans
2135: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
2136: p_date_earned IN DATE
2137: )
2138: RETURN VARCHAR2 IS
2139:
2228: group by peev.screen_entry_value;
2229:
2230: cursor csr_get_curr_atd is
2231: select pps.actual_termination_date
2232: from per_periods_of_service pps, per_all_assignments_f paaf
2233: where paaf.assignment_id = p_assignment_id
2234: and p_date_paid between paaf.effective_start_date and paaf.effective_end_date
2235: and paaf.period_of_service_id = pps.period_of_service_id ;
2236:
2581:
2582:
2583: cursor csr_get_curr_atd is
2584: select pps.actual_termination_date
2585: from per_periods_of_service pps, per_all_assignments_f paaf
2586: where paaf.assignment_id = p_assignment_id
2587: and p_date_paid between paaf.effective_start_date and paaf.effective_end_date
2588: and paaf.period_of_service_id = pps.period_of_service_id ;
2589:
3224: greatest(to_date(peev1.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'), ptp.start_date),
3225: 'Statutory Paid Parental Leave Payment' ,to_date(peev1.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'))),0)
3226: from
3227: per_time_periods ptp,
3228: per_all_assignments_f paa,
3229: pay_assignment_actions pac,
3230: pay_payroll_actions ppa,
3231: pay_element_entries_f pee,
3232: pay_element_entry_values_f peev,
3313: end get_days_ppl_suspended;
3314:
3315: /* bug 12583457 - to count ETP on Termination with Salary in Lieu of Notice input value entered */
3316: FUNCTION au_count_etps_sil
3317: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
3318: p_date_earned IN DATE
3319: )
3320: RETURN NUMBER IS
3321:
3386: return number is
3387:
3388: cursor csr_get_curr_atd is
3389: select pps.actual_termination_date
3390: from per_periods_of_service pps, per_all_assignments_f paaf
3391: where paaf.assignment_id = p_assignment_id
3392: and p_date_paid between paaf.effective_start_date and paaf.effective_end_date
3393: and paaf.period_of_service_id = pps.period_of_service_id ;
3394:
3710:
3711:
3712: cursor csr_get_curr_atd is
3713: select pps.actual_termination_date
3714: from per_periods_of_service pps, per_all_assignments_f paaf
3715: where paaf.assignment_id = p_assignment_id
3716: and p_date_paid between paaf.effective_start_date and paaf.effective_end_date
3717: and paaf.period_of_service_id = pps.period_of_service_id ;
3718:
3772:
3773: cursor csr_rehired_assignment (c_year_start_paid date) is
3774: select distinct paa.assignment_id, pps.actual_termination_date, nvl(pps.final_process_date, to_date('31/12/4712','dd/mm/yyyy')) final_process_date
3775: from per_periods_of_service pps
3776: ,per_all_assignments_f paa
3777: where pps.person_id = (
3778: select person_id
3779: from per_all_assignments_f paa1
3780: where paa1.assignment_id = p_assignment_id
3775: from per_periods_of_service pps
3776: ,per_all_assignments_f paa
3777: where pps.person_id = (
3778: select person_id
3779: from per_all_assignments_f paa1
3780: where paa1.assignment_id = p_assignment_id
3781: and rownum = 1)
3782: and paa.period_of_service_id = pps.period_of_service_id
3783: and nvl(pps.final_process_date, to_date('31/12/4712','dd/mm/yyyy')) >= c_year_start_paid