1 Package pqp_pension_functions as
2 /* $Header: pqppenff.pkh 120.4.12010000.1 2008/07/28 11:21:09 appldev ship $ */
3
4 type t_pension_info is table of pqp_pension_types_f%rowtype
5 index by Binary_Integer;
6
7 TYPE r_tax_si_rec IS RECORD
8 ( tax_si_code varchar2(20)
9 ,reduction_order number);
10
11 TYPE t_tax_si_tbl IS TABLE OF r_tax_si_rec
12 INDEX BY BINARY_INTEGER;
13
14 g_pension_rec t_pension_info;
15 g_proc_name varchar2(80) := 'PQP_Pension_functions.';
16
17 -- ----------------------------------------------------------------------------
18 -- |---------------------< get_pension_type_details >--------------------------|
19 -- ----------------------------------------------------------------------------
20 --
21 function get_pension_type_details
22 (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
23 ,p_date_earned in date
24 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
25 ,p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
26 ,p_legislation_code in pqp_pension_types_f.legislation_code%TYPE
27 ,p_column_name in varchar2
28 ,p_column_value out nocopy varchar2
29 ,p_error_message out nocopy varchar2
30
31 ) return NUMBER;
32
33 -- ----------------------------------------------------------------------------
34 -- |-------------------------< prorate_amount >-------------------------------|
35 -- ----------------------------------------------------------------------------
36 --
37 function prorate_amount
38 (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
39 ,p_date_earned in date
40 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
41 ,p_amount in number
42 ,p_payroll_period in varchar2
43 ,p_work_pattern in varchar2
44 ,p_conversion_rule in varchar2
45 ,p_prorated_amount out nocopy number
46 ,p_error_message out nocopy varchar2
47 ,p_payroll_period_prorate in varchar2
48 ,p_override_pension_days in NUMBER DEFAULT -9999
49 ) return NUMBER;
50
51
52
53
54 -- ----------------------------------------------------------------------------
55 -- |--------------------------< get_run_year >--------------------------------|
56 -- ----------------------------------------------------------------------------
57 --
58 function get_run_year
59 (p_date_earned in date
60 ,p_error_message out nocopy varchar2
61 ) return NUMBER;
62
63 -- ----------------------------------------------------------------------------
64 -- |-----------------------< get_abp_pension_salary >-----------------------------|
65 -- ----------------------------------------------------------------------------
66 --
67
68 function get_abp_pension_salary
69 (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
70 ,p_date_earned in date
71 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
72 ,p_payroll_id in pay_payroll_actions.payroll_id%TYPE
73 ,p_period_start_date in date
74 ,p_period_end_date in date
75 ,p_scale_salary in number
76 ,p_scale_salary_h in number
77 ,p_ft_rec_bonus in number
78 ,p_ft_rec_bonus_h in number
79 ,p_pt_rec_bonus in number
80 ,p_pt_rec_bonus_h in number
81 ,p_ft_eoy_bonus in number
82 ,p_ft_eoy_bonus_h in number
83 ,p_pt_eoy_bonus in number
84 ,p_pt_eoy_bonus_h in number
85 ,p_salary_balance_value out nocopy number
86 ,p_error_message out nocopy varchar2
87 ,p_oht_correction out nocopy varchar2
88 ,p_scale_salary_eoy_bonus in number
89 ,p_ft_rec_bonus_eoy_bonus in number
90 ,p_pt_rec_bonus_eoy_bonus in number
91 ,p_error_message1 out nocopy varchar2
92 ,p_error_message2 out nocopy varchar2
93 ,p_late_hire_indicator in number
94 ) return number;
95
96 -- ----------------------------------------------------------------------------
97 -- |-----------------------< get_pension_salary >-----------------------------|
98 -- ----------------------------------------------------------------------------
99 --
100 function get_pension_salary
101 (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
102 ,p_date_earned in date
103 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
104 ,p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
105 ,p_salary_balance_name in varchar2
106 ,p_payroll_period in varchar2
107 ,p_salary_balance_value out nocopy number
108 ,p_error_message out nocopy varchar2
109 ,p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE DEFAULT -99
110 ) return NUMBER;
111
112
113 -- ----------------------------------------------------------------------------
114 -- |----------------------< get_pension_type_eligibility >--------------------|
115 -- ----------------------------------------------------------------------------
116 --
117 function get_pension_type_eligibility
118 (p_business_group_id in number
119 ,p_date_earned in date
120 ,p_assignment_id in number
121 ,p_pension_type_id in number
122 ,p_eligibility_flag out nocopy varchar2
123 ,p_error_message out nocopy varchar2
124 ) return NUMBER;
125
126
127 -- ----------------------------------------------------------------------------
128 -- |----------------------< get_pension_threshold_ratio >--------------------|
129 -- ----------------------------------------------------------------------------
130 --
131 function get_pension_threshold_ratio
132 (p_date_earned in date
133 ,p_assignment_id in number
134 ,p_business_group_id in number
135 ,p_assignment_action_id in number
136 ) return NUMBER;
137
138
139 -- ----------------------------------------------------------------------------
140 -- |----------------------< gen_dynamic_formula >-----------------------------|
141 -- ----------------------------------------------------------------------------
142 PROCEDURE gen_dynamic_formula ( p_pension_type_id IN NUMBER
143 ,p_effective_date IN DATE
144 ,p_formula_string OUT NOCOPY varchar2
145 );
146
147 -- ----------------------------------------------------------------------------
148 -- |----------------------< gen_dynamic_sav_formula >-----------------------------|
149 -- ----------------------------------------------------------------------------
150 PROCEDURE gen_dynamic_sav_formula ( p_pension_type_id IN NUMBER
151 ,p_effective_date IN DATE
152 ,p_formula_string OUT NOCOPY varchar2
153 );
154
155 -- ------------------------------------------------------------------
156 -- |----------------------< get_bonus >-----------------------------|
157 -- ------------------------------------------------------------------
158
159 FUNCTION get_bonus
160 ( p_date_earned in date
161 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
162 ,p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
163 ,p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
164 ,p_pay_period_salary in number
165 ,p_pay_period in varchar2
166 ,p_work_pattern in varchar2
167 ,p_conversion_rule in varchar2
168 ,p_bonus_amount out NOCOPY number
169 ,p_error_message out NOCOPY varchar2
170 )
171 RETURN number;
172
173 -- ------------------------------------------------------------------
174 -- |----------------------< is_number >-----------------------------|
175 -- ------------------------------------------------------------------
176
177 FUNCTION is_number
178 (
179 p_data_value IN OUT NOCOPY varchar2
180 )
181 RETURN NUMBER;
182
183 -- ------------------------------------------------------------------
184 -- |-----------------< get_addnl_savings_amt >-----------------------|
185 -- ------------------------------------------------------------------
186
187 FUNCTION get_addnl_savings_amt
188 (
189 p_assignment_id in number
190 ,p_date_earned in date
191 ,p_business_group_id in number
192 ,p_payroll_id in number
193 ,p_pension_type_id in number
194 ,p_payroll_period_number in number
195 ,p_additional_amount out NOCOPY number
196 ,p_error_message out NOCOPY varchar2
197 )
198 RETURN NUMBER;
199
200 -- ----------------------------------------------------------------------------
201 -- |---------------------< get_abp_entry_value >--------------------------|
202 -- ----------------------------------------------------------------------------
203 --
204 function get_abp_entry_value
205 (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
206 ,p_date_earned in date
207 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
208 ,p_element_type_id in number
209 ,p_input_value_name in varchar2
210 ) return NUMBER;
211
212 -- ----------------------------------------------------------------------------
213 -- |-----------------------< get_avg_part_time_perc >-------------------------|
214 -- ----------------------------------------------------------------------------
215 --
216 function get_avg_part_time_perc
217 (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
218 ,p_date_earned in date
219 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
220 ,p_assignment_action_id IN NUMBER
221 ,p_period_start_date in DATE
222 ,p_period_end_date in DATE
223 ,p_avg_part_time_perc OUT NOCOPY NUMBER
224 ,p_error_message OUT NOCOPY VARCHAR2)
225 return NUMBER;
226
227 -- ----------------------------------------------------------------------------
228 -- |-----------------------< get_avg_part_time_perc >-------------------------|
229 -- ----------------------------------------------------------------------------
230 FUNCTION get_hook_part_time_perc (p_assignment_id IN NUMBER
231 ,p_date_earned IN DATE
232 ,p_business_group_id IN NUMBER
233 ,p_assignment_action_id IN NUMBER)
234 RETURN NUMBER;
235
236 -- ----------------------------------------------------------------------------
237 -- |--------------------< GET_REPORTING_PART_TIME_PERC>----------------------|
238 -- ----------------------------------------------------------------------------
239 FUNCTION GET_REPORTING_PART_TIME_PERC (p_assignment_id IN NUMBER
240 ,p_date_earned IN DATE
241 ,p_business_group_id IN NUMBER
242 ,p_assignment_action_id IN NUMBER)
243 RETURN NUMBER;
244
245 -- ----------------------------------------------------------------------------
246 -- |---------------------------< GET_VERSION_ID>------------------------------|
247 -- ----------------------------------------------------------------------------
248 FUNCTION GET_VERSION_ID(p_business_group_id IN NUMBER
249 ,p_date_earned IN DATE)
250 RETURN NUMBER;
251
252 --
253 -- ----------------------------------------------------------------------------
254 -- |---------------------< get_pay_period_age >-------------------------------|
255 -- ----------------------------------------------------------------------------
256 --
257 FUNCTION get_pay_period_age
258 (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
259 ,p_date_earned IN DATE
260 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
261 ,p_period_start_date IN DATE
262 ) RETURN NUMBER;
263
264 --
265 -- ----------------------------------------------------------------------------
266 -- |--------------------------< get_bal_val >---------------------------------|
267 -- ----------------------------------------------------------------------------
268 --
269 FUNCTION get_bal_val
270 (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
271 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
272 ,p_effective_date IN DATE
273 ,p_balance_name IN VARCHAR2
274 ,p_dimension_name IN VARCHAR2)
275 RETURN NUMBER;
276
277 END pqp_pension_functions;
278 --