[Home] [Help]
PACKAGE BODY: APPS.PAY_US_SOE_UTILS_PKG
Source
1 package body pay_us_soe_utils_pkg as
2 /* $Header: pyussoeu.pkb 115.3 2002/09/07 00:27:40 ekim ship $ */
3
4 /***************************************************************************
5 * Copyright (c)1999 Oracle Corporation, Redwood Shores, California, USA *
6 * All rights reserved. *
7 ****************************************************************************
8 * *
9 * File: pyussoeu.pkb *
10 * *
11 * Description: *
12 * *
13 * *
14 * *
15 * History *
16 * ----------------------------------------------------- *
17 * 26-JUN-1999 pganguly Created *
18 * *
19 * 03-oct-2001 115.1 tclewis modified cusror cur_base_salary *
20 * to use least *
21 * nvl(ppa.date_earned, ppa.effective_date) *
22 * actual termination date *
23 * 06-Sep-2002 115.2 ekim Changed to fnd_number.canonical_to_number *
24 * in cur_base_salary *
25 * 06-Sep-2002 115.3 ekim Added dbdrv command. *
26 ***************************************************************************/
27
28 function get_base_sal( p_assignment_action_id in number)
29 return number is
30 begin
31
32 declare
33
34 l_salary number;
35 l_term_date date;
36 l_date date;
37
38 cursor cur_get_date is
39 select
40 nvl(ppa.date_earned, ppa.effective_date)
41 from pay_payroll_actions ppa,
42 pay_assignment_actions paa
43 where paa.assignment_action_id = p_assignment_action_id
44 and ppa.payroll_action_id = paa.payroll_action_id;
45
46 cursor cur_actual_termination_date is
47 select
48 pos.actual_termination_date
49 from per_periods_of_service pos,
50 per_assignments_f paf,
51 pay_assignment_actions paa,
52 pay_payroll_actions ppa
53 where paf.assignment_id = paa.assignment_id
54 and ppa.payroll_action_id = paa.payroll_action_id
55 and paa.assignment_action_id = p_assignment_action_id
56 and l_date between paf.effective_start_date
57 and paf.effective_end_date
58 and paf.period_of_service_id = pos.period_of_service_id;
59
60 cursor cur_base_salary is
61 select
62 fnd_number.canonical_to_number(peev.screen_entry_value)
63 from
64 pay_element_entries_f pee,
65 pay_element_entry_values_f peev,
66 pay_input_values_f piv,
67 per_pay_bases ppb,
68 per_assignments_f paf,
69 pay_payroll_actions ppa,
70 pay_assignment_actions paa
71 where
72 least(nvl(ppa.date_earned, ppa.effective_date),
73 nvl(l_term_date, nvl(ppa.date_earned, ppa.effective_date)
74 )
75 )
76 between
77 pee.effective_start_date and pee.effective_end_date
78 and pee.element_entry_id = peev.element_entry_id
79 and pee.entry_type = 'E'
80 and pee.assignment_id = paf.assignment_id
81 and least(nvl(ppa.date_earned, ppa.effective_date),
82 nvl(l_term_date, nvl(ppa.date_earned, ppa.effective_date)
83 )
84 )
85 between
86 peev.effective_start_date and peev.effective_end_date
87 and peev.input_value_id+0 = piv.input_value_id
88 and least(nvl(ppa.date_earned, ppa.effective_date),
89 nvl(l_term_date, nvl(ppa.date_earned, ppa.effective_date)
90 )
91 )
92 between
93 piv.effective_start_date and piv.effective_end_date
94 and piv.input_value_id = ppb.input_value_id
95 and ppb.pay_basis_id = paf.pay_basis_id
96 and nvl(ppa.date_earned, ppa.effective_date) between
97 paf.effective_start_date and paf.effective_end_date
98 and paf.assignment_id = paa.assignment_id
99 and ppa.payroll_action_id = paa.payroll_action_id
100 and paa.assignment_action_id = p_assignment_action_id;
101
102 begin
103 open cur_get_date;
104 fetch cur_get_date
105 into l_date;
106 close cur_get_date;
107
108 open cur_actual_termination_date;
109 fetch cur_actual_termination_date
110 into l_term_date;
111 close cur_actual_termination_date;
112
113 open cur_base_salary;
114 fetch cur_base_salary
115 into l_salary;
116 close cur_base_salary;
117
118 return(l_salary);
119 end;
120 end get_base_sal;
121
122 end pay_us_soe_utils_pkg;