1 PACKAGE PQP_NL_ABP_FUNCTIONS as
2 /* $Header: pqpnlabp.pkh 120.6 2007/07/03 10:04:17 rsahai noship $ */
3
4 g_proc_name varchar2(80) := 'pqp_nl_abp_functions.';
5
6 -- ----------------------------------------------------------------------------
7 -- |---------------------< Get_Valid_Start_Date >------------------------------|
8 -- ----------------------------------------------------------------------------
9 --
10 Function GET_VALID_START_DATE(
11 p_assignment_id IN NUMBER,
12 p_eff_date IN DATE,
13 p_error_status OUT NOCOPY CHAR,
14 p_error_message OUT NOCOPY VARCHAR2
15 )
16 Return DATE;
17
18 -- ----------------------------------------------------------------------------
19 -- |-------------------------< abp_proration >-------------------------------|
20 -- ----------------------------------------------------------------------------
21 --
22 function abp_proration
23 (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
24 ,p_date_earned in date
25 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
26 ,p_amount in number
27 ,p_payroll_period in varchar2
28 ,p_work_pattern in varchar2
29 ,p_conversion_rule in varchar2
30 ,p_prorated_amount out nocopy number
31 ,p_error_message out nocopy varchar2
32 ,p_payroll_period_prorate in varchar2
33 ,p_override_pension_days in NUMBER DEFAULT -9999
34 ) return NUMBER;
35
36 -- ----------------------------------------------------------------------------
37 -- |-----------------------< cre_ret_ent_ad >------------------------------|
38 -- ----------------------------------------------------------------------------
39 --
40 PROCEDURE cre_ret_ent_ad
41 ( p_assignment_extra_info_id_o IN NUMBER
42 ,p_assignment_id_o IN NUMBER
43 ,p_information_type_o IN VARCHAR2
44 ,p_aei_information1_o IN VARCHAR2
45 ,p_aei_information2_o IN VARCHAR2);
46 -- ----------------------------------------------------------------------------
47 -- |-----------------------< chk_dup_pt_row_ins >------------------------------|
48 -- ----------------------------------------------------------------------------
49 --
50 PROCEDURE chk_dup_pt_row_ins ( p_org_information_id IN number
51 ,p_org_information_context IN varchar2
52 ,p_organization_id IN number
53 ,p_org_information1 IN varchar2
54 ,p_org_information2 IN varchar2
55 ,p_org_information3 IN varchar2
56 ,p_org_information4 IN varchar2 default null
57 ,p_org_information5 IN varchar2 default null
58 ,p_org_information6 IN varchar2 default null
59 );
60
61 -- ----------------------------------------------------------------------------
62 -- |-----------------------< chk_dup_pt_row_upd >-----------------------------|
63 -- ----------------------------------------------------------------------------
64 --
65 PROCEDURE chk_dup_pt_row_upd ( p_org_information_id number
66 ,p_org_information_context varchar2
67 ,p_organization_id number
68 ,p_org_information1 varchar2
69 ,p_org_information2 varchar2
70 ,p_org_information3 varchar2
71 ,p_org_information4 varchar2 default null
72 ,p_org_information5 varchar2 default null
73 ,p_org_information6 varchar2 default null
74 ,p_org_information1_o varchar2
75 ,p_org_information2_o varchar2
76 ,p_org_information3_o varchar2
77 ,p_org_information4_o varchar2 default null
78 ,p_org_information5_o varchar2 default null
79 ,p_org_information6_o varchar2 default null
80 );
81
82 PROCEDURE chk_dup_pt_row ( p_org_information_id number
83 ,p_org_information_context varchar2
84 ,p_organization_id number
85 ,p_org_information1 varchar2
86 ,p_org_information2 varchar2
87 ,p_org_information3 varchar2
88 );
89
90 PROCEDURE gen_dynamic_formula ( p_si_tax_balances IN NUMBER
91 ,p_formula_string OUT NOCOPY varchar2
92 );
93
94 -- ----------------------------------------------------------------------------
95 -- |-------------------< chk_dup_asg_info_row_ins>-------------------------|
96 -- ----------------------------------------------------------------------------
97 --
98 PROCEDURE chk_dup_asg_info_row_ins (p_assignment_extra_info_id IN number
99 ,p_assignment_id IN number
100 ,p_information_type IN varchar2
101 ,p_aei_information1 IN varchar2
102 ,p_aei_information2 IN varchar2
103 ,p_aei_information3 IN varchar2
104 ,p_aei_information4 IN varchar2
105 ,p_aei_information5 IN varchar2
106 ,p_aei_information6 IN varchar2
107 ,p_aei_information7 IN varchar2
108 ,p_aei_information8 IN varchar2
109 ,p_aei_information9 IN varchar2
110 ,p_aei_information10 IN varchar2
111 ,p_aei_information11 IN varchar2
112 ,p_aei_information12 IN varchar2
113 ,p_aei_information13 IN varchar2
114 ,p_aei_information14 IN varchar2
115 ,p_aei_information15 IN varchar2
116 ,p_aei_information16 IN varchar2
117 ,p_aei_information20 IN varchar2
118 ,p_aei_information21 IN varchar2
119 ,p_aei_information22 IN varchar2
120 );
121
122 -- ----------------------------------------------------------------------------
123 -- |-------------------< chk_dup_asg_info_row_upd>-------------------------|
124 -- ----------------------------------------------------------------------------
125 --
126 PROCEDURE chk_dup_asg_info_row_upd (p_assignment_extra_info_id IN number
127 ,p_assignment_id IN number
128 ,p_information_type IN varchar2
129 ,p_aei_information1 IN varchar2
130 ,p_aei_information2 IN varchar2
131 ,p_aei_information3 IN varchar2
132 ,p_aei_information4 IN varchar2
133 ,p_aei_information5 IN varchar2
134 ,p_aei_information6 IN varchar2
135 ,p_aei_information7 IN varchar2
136 ,p_aei_information8 IN varchar2
137 ,p_aei_information9 IN varchar2
138 ,p_aei_information10 IN varchar2
139 ,p_aei_information11 IN varchar2
140 ,p_aei_information12 IN varchar2
141 ,p_aei_information13 IN varchar2
142 ,p_aei_information14 IN varchar2
143 ,p_aei_information15 IN varchar2
144 ,p_aei_information16 IN varchar2
145 ,p_aei_information20 IN varchar2
146 ,p_aei_information21 IN varchar2
147 ,p_aei_information22 IN varchar2
148 ,p_aei_information1_o IN varchar2
149 ,p_aei_information2_o IN varchar2
150 ,p_aei_information3_o IN varchar2
151 ,p_aei_information4_o IN varchar2
152 ,p_aei_information5_o IN varchar2
153 ,p_aei_information6_o IN varchar2
154 ,p_aei_information7_o IN varchar2
155 );
156
157 --
158 -- ------------------------------------------------------------------------
159 -- |------------------< get_abp_contribution >----------------------------|
160 -- ------------------------------------------------------------------------
161 --
162
163 FUNCTION get_abp_contribution
164 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
165 ,p_date_earned in date
166 ,p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
167 ,p_payroll_action_id IN NUMBER
168 ,p_pension_sub_cat in pqp_pension_types_f.pension_sub_category%TYPE
169 ,p_conversion_rule in pqp_pension_types_f.threshold_conversion_rule%TYPE
170 ,p_basis_method in pqp_pension_types_f.pension_basis_calc_method%TYPE
171 ,p_ee_contrib_type out NOCOPY number
172 ,p_ee_contrib_value out NOCOPY number
173 ,p_er_contrib_type out NOCOPY number
174 ,p_er_contrib_value out NOCOPY number
175 )
176 RETURN number;
177
178 --
179 -- ------------------------------------------------------------------------
180 -- |------------------< get_participation_date >----------------------------|
181 -- ------------------------------------------------------------------------
182 --
183
184 FUNCTION get_participation_date
185 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
186 ,p_date_earned in date
187 ,p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
188 ,p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
189 ,p_start_date out NOCOPY date
190 )
191 RETURN number;
192
193 --
194 -- ------------------------------------------------------------------------
195 -- |------------------< get_assignment_attribute >-------------------------|
196 -- ------------------------------------------------------------------------
197 --
198
199 FUNCTION get_assignment_attribute
200 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
201 ,p_date_earned in date
202 ,p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
203 ,p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
204 ,p_attrib_name in varchar2
205 ,p_attrib_value out NOCOPY varchar2
206 ,p_error_message out NOCOPY varchar2
207 )
208 RETURN number;
209
210
211 --
212 -- ------------------------------------------------------------------------
213 -- |------------------< get_participation_org >----------------------------|
214 -- ------------------------------------------------------------------------
215 --
216
217 PROCEDURE get_participation_org
218 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
219 ,p_date_earned in date
220 ,p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
221 ,p_asg_or_org out NOCOPY number
222 ,p_org_id out NOCOPY number
223 );
224
225 -- ----------------------------------------------------------------------------
226 -- |-----------------------< chk_dup_pp_row_ins >------------------------------|
227 -- ----------------------------------------------------------------------------
228 --
229 PROCEDURE chk_dup_pp_row_ins ( p_org_information_id IN number
230 ,p_org_information_context IN varchar2
231 ,p_organization_id IN number
232 ,p_org_information1 IN varchar2
233 ,p_org_information2 IN varchar2
234 ,p_org_information3 IN varchar2
235 );
236
237 -- ----------------------------------------------------------------------------
238 -- |-----------------------< chk_dup_pp_row_upd >------------------------------|
239 -- ----------------------------------------------------------------------------
240 --
241 PROCEDURE chk_dup_pp_row_upd ( p_org_information_id IN number
242 ,p_org_information_context IN varchar2
243 ,p_organization_id IN number
244 ,p_org_information1 IN varchar2
245 ,p_org_information2 IN varchar2
246 ,p_org_information3 IN varchar2
247 ,p_org_information1_o IN varchar2
248 ,p_org_information2_o IN varchar2
249 ,p_org_information3_o IN varchar2
250 );
251
252 --
253 -- ------------------------------------------------------------------------
254 -- |------------------< get_absence_adjustment >-------------------------|
255 -- ------------------------------------------------------------------------
256 --
257
258 FUNCTION get_absence_adjustment
259 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
260 ,p_date_earned in date
261 ,p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
262 ,p_dedn_amt in number
263 ,p_adjust_amt out NOCOPY number
264 ,p_error_message out NOCOPY varchar2
265 )
266 RETURN number;
267
268 --
269 -- ------------------------------------------------------------------------
270 -- |--------------------< get_proration_factor >---------------------------|
271 -- ------------------------------------------------------------------------
272 --
273
274 FUNCTION get_proration_factor
275 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
276 ,p_date_earned in date
277 ,p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
278 ,p_proration_factor out NOCOPY number
279 ,p_error_message out NOCOPY varchar2
280 )
281 RETURN number;
282 --
283 -- ------------------------------------------------------------------------
284 -- |--------------------< get_abp_calc_eff_dt >----------------------------|
285 -- ------------------------------------------------------------------------
286 --
287 FUNCTION get_abp_calc_eff_dt
288 (p_date_earned IN DATE
289 ,p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
290 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
291 ,p_effective_date OUT NOCOPY DATE
292 )
293 RETURN NUMBER;
294 --
295 -- ------------------------------------------------------------------------
296 -- |--------------------< get_proration_flag >----------------------------|
297 -- ------------------------------------------------------------------------
298 --
299 FUNCTION get_proration_flag
300 (p_date_earned IN DATE
301 ,p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
302 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
303 ,p_assignment_action_id IN per_all_assignments_f.assignment_id%TYPE
307 )
304 ,p_element_type_id IN pay_element_types_f.element_type_id%TYPE
305 ,p_start_date IN DATE
306 ,p_end_date IN DATE
308 RETURN VARCHAR2;
309 --
310 -- ------------------------------------------------------------------------
311 -- |------------------< get_eoy_bonus_percentage >-------------------------|
312 -- ------------------------------------------------------------------------
313 --
314 FUNCTION get_eoy_bonus_percentage
315 (p_date_earned IN DATE
316 ,p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
317 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
318 ,p_eoy_bonus_percentage OUT NOCOPY NUMBER
319 )
320 RETURN NUMBER;
321
322 -------------------------------------------------------------------------------
323 ---------------------------------< upd_chg_evt >-------------------------------
324 -- ----------------------------------------------------------------------------
325 -- This procedure updates the change event log registered with a
326 -- parameter that contains the ABP Reporting date. The date
327 -- is derived based on the approval of an ABP Pensions Notification.
328 -- All reporting to ABP for e.g. Rec 05 and other relevant records
329 -- are done based on this date. This is also to address certification
330 -- issues that have arised due to retrospective changes to various
331 -- changes.
332 --
333 PROCEDURE upd_chg_evt
334 (p_ext_chg_evt_log_id IN NUMBER
335 ,p_chg_evt_cd IN VARCHAR2
336 ,p_chg_eff_dt IN DATE
337 ,p_chg_user_id IN NUMBER
338 ,p_prmtr_01 IN VARCHAR2
339 ,p_prmtr_02 IN VARCHAR2
340 ,p_prmtr_03 IN VARCHAR2
341 ,p_prmtr_04 IN VARCHAR2
342 ,p_prmtr_05 IN VARCHAR2
343 ,p_prmtr_06 IN VARCHAR2
344 ,p_prmtr_07 IN VARCHAR2
345 ,p_prmtr_08 IN VARCHAR2
346 ,p_prmtr_09 IN VARCHAR2
347 ,p_prmtr_10 IN VARCHAR2
348 ,p_person_id IN NUMBER
349 ,p_business_group_id IN NUMBER
350 ,p_object_version_number IN NUMBER
351 ,p_effective_date IN DATE
352 ,p_chg_actl_dt IN DATE
353 ,p_new_val1 IN VARCHAR2
354 ,p_new_val2 IN VARCHAR2
355 ,p_new_val3 IN VARCHAR2
356 ,p_new_val4 IN VARCHAR2
357 ,p_new_val5 IN VARCHAR2
358 ,p_new_val6 IN VARCHAR2
359 ,p_old_val1 IN VARCHAR2
360 ,p_old_val2 IN VARCHAR2
361 ,p_old_val3 IN VARCHAR2
362 ,p_old_val4 IN VARCHAR2
363 ,p_old_val5 IN VARCHAR2
364 ,p_old_val6 IN VARCHAR2 ) ;
365
366 --
367 -------------------------------------------------------------------------------
368 -------------------------< get_abp_late_hire_indicator >-----------------------
369 -------------------------------------------------------------------------------
370 --
371 FUNCTION get_abp_late_hire_indicator
372 (p_payroll_action_id IN NUMBER)
373
374 RETURN NUMBER;
375 --
376 -------------------------------------------------------------------------------
377 -------------------------< Get_Retro_Addnal_Amt >------------------------------
378 -------------------------------------------------------------------------------
379 --
380 FUNCTION Get_Retro_Addnl_Amt
381 (p_bg_id IN number,
382 p_date_earned IN date,
383 p_asg_id IN number,
384 p_element_type_id IN Number,
385 p_payroll_id IN number,
386 p_contri_perc IN number,
387 p_sick_flag IN varchar2,
388 p_dedn_retro_amt out nocopy number,
389 p_ee_er_flag in varchar2
390 ) Return number;
391 --
392
393 END pqp_nl_abp_functions;
[Home] [Help]