[Home] [Help]
111: -- 05 Nov 2001 VGSRINIV 2033033 Modified accrual_daily basis to get
112: -- accruals when assignment working hours
113: -- change more then once
114: -- 10 Oct 2002 PUCHIL 2595888 Changed line 3460 from varchar2(1) to
115: -- pay_payroll_actions.action_type%type
116: -- 01 Aug 2005 SNEKKALA 4259438 Modified Cursor c_get_curr_action_type
117: -- as part of performance fix
118: -----------------------------------------------------------------------------
119: -- private global declarations
1437: , per_absence_attendance_types aat
1438: , pay_element_entries_f ee
1439: , pay_run_results rr
1440: , pay_assignment_actions aa
1441: , pay_payroll_actions pa
1442: , per_time_periods tp
1443: where aat.absence_attendance_type_id = ab.absence_attendance_type_id
1444: and aat.absence_category in ('NZSL', 'NZVS')
1445: and ee.creator_type = 'A'
1640: /* Bug 2264070 Added a extra join for payroll_action_id */
1641: cursor get_pay_period_start_date(p_assignment_id in number) is
1642: SELECT TPERIOD.start_date,
1643: TPTYPE.number_per_fiscal_year
1644: FROM pay_payroll_actions PACTION,
1645: per_time_periods TPERIOD,
1646: per_time_period_types TPTYPE
1647: where PACTION.payroll_action_id =
1648: (select max(paa.payroll_action_id)
1646: per_time_period_types TPTYPE
1647: where PACTION.payroll_action_id =
1648: (select max(paa.payroll_action_id)
1649: from pay_assignment_actions paa,
1650: pay_payroll_actions ppa
1651: where paa.assignment_id = p_assignment_id
1652: and ppa.action_type in ('R','Q')
1653: and ppa.payroll_action_id = paa.payroll_action_id)
1654: and PACTION.payroll_id = TPERIOD.payroll_id
2034: e_missing_balance_type exception ;
2035: e_missing_accrual_plan exception ;
2036: e_missing_leave_in_advance exception ;
2037:
2038: /* Bug 2581490 - added join to pay_payroll_actions in subquery */
2039: cursor get_pay_period_start_date(p_assignment_id number) is
2040: SELECT TPERIOD.start_date,
2041: TPTYPE.number_per_fiscal_year
2042: FROM pay_payroll_actions PACTION,
2038: /* Bug 2581490 - added join to pay_payroll_actions in subquery */
2039: cursor get_pay_period_start_date(p_assignment_id number) is
2040: SELECT TPERIOD.start_date,
2041: TPTYPE.number_per_fiscal_year
2042: FROM pay_payroll_actions PACTION,
2043: per_time_periods TPERIOD,
2044: per_time_period_types TPTYPE
2045: where PACTION.payroll_action_id =
2046: (select max(paa.payroll_action_id)
2044: per_time_period_types TPTYPE
2045: where PACTION.payroll_action_id =
2046: (select max(paa.payroll_action_id)
2047: from pay_assignment_actions paa,
2048: pay_payroll_actions ppa
2049: where paa.assignment_id = p_assignment_id
2050: and ppa.action_type in ('R','Q')
2051: and ppa.payroll_action_id = paa.payroll_action_id)
2052: and PACTION.payroll_id = TPERIOD.payroll_id
2103: , pay_element_entries_f ee -- absence element entry
2104: , pay_run_results rr -- run result for absence -- element entry
2105: , per_absence_attendances ab -- absence record
2106: , pay_assignment_actions aa -- assignment action for -- absence element entry
2107: , pay_payroll_actions pa -- payroll action for -- absence element entry
2108: , per_time_periods tp
2109: , pay_run_results rr2 -- run result for Annual -- Leave Pay element type
2110: , pay_run_result_values rrv2 -- run result value for -- Annual Leave Pay element -- pay value ,
2111: , pay_element_types_f et2 -- Annual Leave Pay element
3546:
3547: cursor get_pay_period_start_date(p_assignment_id number) is
3548: SELECT TPERIOD.start_date,
3549: TPTYPE.number_per_fiscal_year
3550: FROM pay_payroll_actions PACTION,
3551: per_time_periods TPERIOD,
3552: per_time_period_types TPTYPE
3553: where PACTION.payroll_action_id =
3554: (select max(paa.payroll_action_id)
3552: per_time_period_types TPTYPE
3553: where PACTION.payroll_action_id =
3554: (select max(paa.payroll_action_id)
3555: from pay_assignment_actions paa,
3556: pay_payroll_actions ppa
3557: where paa.assignment_id = p_assignment_id
3558: and ppa.action_type in ('R','Q')
3559: and ppa.payroll_action_id = paa.payroll_action_id)
3560: and PACTION.payroll_id = TPERIOD.payroll_id
3593: /* Bug 4259438 : Modified cursor as part of performance */
3594: CURSOR c_get_curr_action_type(p_payroll_id NUMBER)
3595: IS
3596: SELECT action_type
3597: FROM pay_payroll_actions ppa
3598: , pay_payrolls_f ppf
3599: WHERE ppf.payroll_id = p_payroll_id
3600: AND ppa.payroll_id = ppf.payroll_id
3601: AND ppa.business_group_id = ppf.business_group_id
3604: AND ppa.action_type LIKE '%'
3605: AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
3606: ORDER BY PAYROLL_ACTION_ID DESC;
3607:
3608: -- Bug 2595888: Changed the datatype from varchar2(1) to pay_payroll_actions.action_type%type
3609: l_action_type pay_payroll_actions.action_type%type;
3610:
3611: begin
3612:
3605: AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
3606: ORDER BY PAYROLL_ACTION_ID DESC;
3607:
3608: -- Bug 2595888: Changed the datatype from varchar2(1) to pay_payroll_actions.action_type%type
3609: l_action_type pay_payroll_actions.action_type%type;
3610:
3611: begin
3612:
3613: open c_get_curr_action_type(p_payroll_id);
3636:
3637: cursor c_get_values(p_assignment_id number) is
3638: select max(ppa.effective_date)
3639: ,ppa.payroll_id
3640: from pay_payroll_actions ppa
3641: ,pay_assignment_actions pac
3642: where pac.assignment_id = p_assignment_id
3643: and pac.payroll_action_id = ppa.payroll_action_id
3644: and ppa.action_type = 'L'