[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_TP_CONTRIBUTION_REPORT
Source
1 PACKAGE BODY pqp_gb_tp_contribution_report AS
2 /* $Header: pqpgbtpcontrep.pkb 120.11.12020000.1 2013/03/11 11:20:00 sgmaram noship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4 g_package VARCHAR2(35) := 'PQP_GB_TP_CONTRIBUTION_REPORT.';
5 EOL VARCHAR2(5) := fnd_global.local_chr(10);
6 -------------------------------------------------------------------------------
7
8 --This function will find out whether the current run is retro or normal Payroll run
9 --
10 FUNCTION is_this_retro(
11 p_assignment_id NUMBER)
12 RETURN NUMBER
13 IS
14 A NUMBER;
15 BEGIN
16 hr_utility.set_location('Entering: is_this_retro ', 10);
17 hr_utility.set_location(' Assignment ID '||p_assignment_id , 20);
18 BEGIN
19 SELECT 0
20 INTO A
21 FROM PAY_PAYROLL_ACTIONS
22 WHERE PAYROLL_ACTION_ID =
23 (SELECT MAX(PAYROLL_ACTION_ID)
24 FROM PAY_ASSIGNMENT_ACTIONS
25 WHERE ASSIGNMENT_ID = p_assignment_id
26 )
27 AND ACTION_TYPE IN ('L','G','O');
28 hr_utility.set_location('**** This is retro ' , 30);
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 hr_utility.set_location('**** This is Normal Payroll ' , 40);
32 A := 1;
33 END;
34 RETURN A;
35 END IS_THIS_RETRO;
36 --
37 FUNCTION TPS_Pension_tier(
38 p_payroll_id NUMBER,
39 p_business_group_id NUMBER,
40 P_DATE_EARNED DATE,
41 p_assignment_id NUMBER,
42 p_assignment_action_id number,--To handle multiple payroll runs for the same time_period_id
43 p_rv_a IN OUT NOCOPY NUMBER,
44 p_rv_b IN OUT NOCOPY NUMBER,
45 p_rv_c IN OUT NOCOPY NUMBER)
46 RETURN NUMBER
47 IS
48 l_source_action_id NUMBER;
49 /* To fetch the seeded information element's type id */
50 CURSOR c_element_type_id
51 IS
52 SELECT pet.element_type_id
53 FROM pay_element_types_f pet
54 WHERE pet.legislation_code = 'GB'
55 AND P_DATE_EARNED BETWEEN pet.effective_start_date AND pet.effective_end_date
56 AND pet.element_name = 'Tiered Contributions Information';
57 /* To fetch the original payroll run's result values for Pension Tier */
58 CURSOR c_tier_prrv(l_element_type_id NUMBER)
59 IS
60 SELECT MAX(NVL(DECODE(PIVF.NAME,'Pension Tier A',prrv.result_value),0)) Pension_Tier_A,
61 MAX(NVL(DECODE(PIVF.NAME,'Pension Tier B',prrv.result_value),0)) Pension_Tier_B,
62 MAX(NVL(DECODE(PIVF.NAME,'Pension Tier C',prrv.result_value),0)) Pension_Tier_C
63 FROM pay_payroll_actions ppa ,
64 pay_assignment_actions paa ,
65 pay_run_results prr ,
66 pay_run_result_values prrv ,
67 pay_input_values_f pivf ,
68 PER_TIME_PERIODS PTP
69 WHERE ppa.business_group_id = p_business_group_id --Context
70 AND ppa.payroll_id = p_payroll_id --Context
71 AND PTP.PAYROLL_ID = ppa.payroll_id
72 AND PTP.END_DATE = P_DATE_EARNED --CONTEXT
73 AND ppa.time_period_id = PTP.time_period_id
74 AND ppa.action_type IN ('R','B','Q','I','V')
75 AND paa.payroll_action_id = ppa.payroll_action_id
76 AND paa.source_action_id IS NOT NULL
77 AND prr.assignment_action_id = paa.assignment_action_id
78 AND prr.element_type_id = l_element_type_id --Context
79 AND pivf.element_type_id = prr.element_type_id
80 AND paa.assignment_id = p_assignment_id
81 AND PIVF.NAME in ('Pension Tier A','Pension Tier B','Pension Tier C')
82 AND prrv.input_value_id = PIVF.Input_value_ID
83 AND prrv.run_result_id = prr.run_result_id
84 AND P_DATE_EARNED BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE ---CONTEXT
85 AND paa.source_action_id = l_source_action_id --To handle multiple payroll runs for the same time_period_id
86 ;
87
88 l_element_id NUMBER ;
89 l_count NUMBER ;
90
91
92 BEGIN
93 hr_utility.trace('Entering : TPS_Pension_tier');
94 select source_action_id into l_source_action_id from pay_assignment_actions where assignment_action_id =p_assignment_action_id;
95 hr_utility.trace('source_action_id :'|| l_source_action_id);
96
97 OPEN c_element_type_id;
98 FETCH c_element_type_id INTO l_element_id;
99 CLOSE c_element_type_id;
100 hr_utility.trace('p_business_group_id::'||p_business_group_id);
101 hr_utility.trace('p_payroll_id::'||p_payroll_id);
102 hr_utility.trace('P_DATE_EARNED::'||P_DATE_EARNED);
103 hr_utility.trace('p_element_type_id::'||l_element_id);
104 hr_utility.trace('p_assignment_id::'||p_assignment_id);
105 IF is_this_retro(p_assignment_id) = 0 THEN
106 --This is retro run, so fetch the original payroll run's result values
107 OPEN c_tier_prrv(l_element_id);
108 FETCH c_tier_prrv INTO p_rv_a,p_rv_b,p_rv_c;
109 p_rv_a := NVL(p_rv_a,0);
110 p_rv_b := NVL(p_rv_b,0);
111 p_rv_c := NVL(p_rv_c,0);
112 hr_utility.trace('After fetch Return Value - A:'||p_rv_a);
113 hr_utility.trace('After fetch Return Value - B:'||p_rv_b);
114 hr_utility.trace('After fetch Return Value - C:'||p_rv_c);
115 CLOSE c_tier_prrv;
116
117 END IF;
118
119 RETURN 1; -- 1 = Success
120 EXCEPTION
121 WHEN OTHERS THEN
122 hr_utility.trace('Error in the Function PQP_GB_TP_CONTRIBUTION_REPORT.TPS_PENSION_TIER.');
123 RETURN 0; -- 0 = Failure
124 END TPS_PENSION_TIER;
125
126 --
127
128 FUNCTION TPS_Pension_Input_Values(
129 p_payroll_id NUMBER,
130 p_business_group_id NUMBER,
131 P_DATE_EARNED DATE,
132 p_assignment_id NUMBER,
133 p_assignment_action_id number,--To handle multiple payroll runs for the same time_period_id
134 p_rv_a IN OUT NOCOPY NUMBER,
135 p_rv_b IN OUT NOCOPY NUMBER,
136 p_rv_c IN OUT NOCOPY NUMBER,
137 p_pp_a IN OUT NOCOPY NUMBER,
138 p_pp_b IN OUT NOCOPY NUMBER,
139 p_pp_c IN OUT NOCOPY NUMBER,
140 p_ee_a IN OUT NOCOPY NUMBER,
141 p_ee_b IN OUT NOCOPY NUMBER,
142 p_ee_c IN OUT NOCOPY NUMBER,
143 p_er_a IN OUT NOCOPY NUMBER,
144 p_er_b IN OUT NOCOPY NUMBER,
145 p_er_c IN OUT NOCOPY NUMBER,
146 p_af_a IN OUT NOCOPY NUMBER,
147 p_af_b IN OUT NOCOPY NUMBER,
148 p_af_c IN OUT NOCOPY NUMBER)
149 RETURN NUMBER
150 IS
151 l_source_action_id NUMBER;
152 /* To fetch the seeded information element's type id */
153 CURSOR c_element_type_id
154 IS
155 SELECT pet.element_type_id
156 FROM pay_element_types_f pet
157 WHERE pet.legislation_code = 'GB'
158 AND P_DATE_EARNED BETWEEN pet.effective_start_date AND pet.effective_end_date
159 AND pet.element_name = 'Tiered Contributions Information';
160 /* To fetch the original payroll run's result values for Pension Tier */
161 CURSOR c_tier_prrv(l_element_type_id NUMBER)
162 IS
163 SELECT SUM(NVL(DECODE(PIVF.NAME,'Pension Tier A',prrv.result_value),0)) Pension_Tier_A,
164 SUM(NVL(DECODE(PIVF.NAME,'Pension Tier B',prrv.result_value),0)) Pension_Tier_B,
165 SUM(NVL(DECODE(PIVF.NAME,'Pension Tier C',prrv.result_value),0)) Pension_Tier_C,
166 SUM(NVL(DECODE(PIVF.NAME,'Pensionable Pay A',prrv.result_value),0)) Pensionable_Pay_A,
167 SUM(NVL(DECODE(PIVF.NAME,'Pensionable Pay B',prrv.result_value),0)) Pensionable_Pay_B,
168 SUM(NVL(DECODE(PIVF.NAME,'Pensionable Pay C',prrv.result_value),0)) Pensionable_Pay_C,
169 SUM(NVL(DECODE(PIVF.NAME,'EE Contribution A',prrv.result_value),0)) EE_Contribution_A,
170 SUM(NVL(DECODE(PIVF.NAME,'EE Contribution B',prrv.result_value),0)) EE_Contribution_B,
171 SUM(NVL(DECODE(PIVF.NAME,'EE Contribution C',prrv.result_value),0)) EE_Contribution_C,
172 SUM(NVL(DECODE(PIVF.NAME,'ER Contribution A',prrv.result_value),0)) ER_Contribution_A,
173 SUM(NVL(DECODE(PIVF.NAME,'ER Contribution B',prrv.result_value),0)) ER_Contribution_B,
174 SUM(NVL(DECODE(PIVF.NAME,'ER Contribution C',prrv.result_value),0)) ER_Contribution_C,
175 SUM(NVL(DECODE(PIVF.NAME,'Annual FTE Sal A',prrv.result_value),0)) Annual_FTE_Sal_A,
176 SUM(NVL(DECODE(PIVF.NAME,'Annual FTE Sal B',prrv.result_value),0)) Annual_FTE_Sal_B,
177 SUM(NVL(DECODE(PIVF.NAME,'Annual FTE Sal C',prrv.result_value),0)) Annual_FTE_Sal_C
178 FROM pay_payroll_actions ppa ,
179 pay_assignment_actions paa ,
180 pay_run_results prr ,
181 pay_run_result_values prrv ,
182 pay_input_values_f pivf ,
183 PER_TIME_PERIODS PTP
184 WHERE ppa.business_group_id = p_business_group_id --Context
185 AND ppa.payroll_id = p_payroll_id --Context
186 AND PTP.PAYROLL_ID = ppa.payroll_id
187 AND PTP.END_DATE = P_DATE_EARNED --CONTEXT
188 AND ppa.time_period_id = PTP.time_period_id
189 AND ppa.action_type IN ('R','B','Q','I','V')
190 AND paa.payroll_action_id = ppa.payroll_action_id
191 AND paa.source_action_id IS NOT NULL
192 AND prr.assignment_action_id = paa.assignment_action_id
193 AND prr.element_type_id = l_element_type_id --Context
194 AND pivf.element_type_id = prr.element_type_id
195 AND paa.assignment_id = p_assignment_id
196 AND PIVF.NAME in ('Pension Tier A','Pension Tier B','Pension Tier C'
197 ,'Pensionable Pay A','Pensionable Pay B','Pensionable Pay C'
198 ,'EE Contribution A','EE Contribution B','EE Contribution C'
199 ,'ER Contribution A','ER Contribution B','ER Contribution C'
200 ,'Annual FTE Sal A','Annual FTE Sal B','Annual FTE Sal C')
201 AND prrv.input_value_id = PIVF.Input_value_ID
202 AND prrv.run_result_id = prr.run_result_id
203 AND P_DATE_EARNED BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE ---CONTEXT
204 AND paa.source_action_id = l_source_action_id --To handle multiple payroll runs for the same time_period_id
205 ;
206
207 l_element_id NUMBER ;
208 l_count NUMBER ;
209
210
211 BEGIN
212 hr_utility.trace('Entering : TPS_Pension_Input_values');
213 select source_action_id into l_source_action_id from pay_assignment_actions where assignment_action_id =p_assignment_action_id;
214 hr_utility.trace('source_action_id :'|| l_source_action_id);
215
216 OPEN c_element_type_id;
217 FETCH c_element_type_id INTO l_element_id;
218 CLOSE c_element_type_id;
219 hr_utility.trace('p_business_group_id::'||p_business_group_id);
220 hr_utility.trace('p_payroll_id::'||p_payroll_id);
221 hr_utility.trace('P_DATE_EARNED::'||P_DATE_EARNED);
222 hr_utility.trace('p_element_type_id::'||l_element_id);
223 hr_utility.trace('p_assignment_id::'||p_assignment_id);
224 IF is_this_retro(p_assignment_id) = 0 THEN
225 --This is retro run, so fetch the original payroll run's result values
226 OPEN c_tier_prrv(l_element_id);
227 FETCH c_tier_prrv INTO p_rv_a,p_rv_b,p_rv_c,p_pp_a,p_pp_b,p_pp_c,p_ee_a,p_ee_b,p_ee_c,p_er_a,p_er_b,p_er_c,p_af_a,p_af_b,p_af_c;
228 p_rv_a := NVL(p_rv_a,0);
229 p_rv_b := NVL(p_rv_b,0);
230 p_rv_c := NVL(p_rv_c,0);
231 p_pp_a := NVL(p_pp_a,0);
232 p_pp_b := NVL(p_pp_b,0);
233 p_pp_c := NVL(p_pp_c,0);
234 p_ee_a := NVL(p_ee_a,0);
235 p_ee_b := NVL(p_ee_b,0);
236 p_ee_c := NVL(p_ee_c,0);
237 p_er_a := NVL(p_er_a,0);
238 p_er_b := NVL(p_er_b,0);
239 p_er_c := NVL(p_er_c,0);
240 p_af_a := NVL(p_af_a,0);
241 p_af_b := NVL(p_af_b,0);
242 p_af_c := NVL(p_af_c,0);
243
244 hr_utility.trace('PENSION TIER');
245 hr_utility.trace('After fetch Return Value - A:'||p_rv_a);
246 hr_utility.trace('After fetch Return Value - B:'||p_rv_b);
247 hr_utility.trace('After fetch Return Value - C:'||p_rv_c);
248 hr_utility.trace('PENSION PAY');
249 hr_utility.trace('After fetch Return Value - A:'||p_pp_a);
250 hr_utility.trace('After fetch Return Value - B:'||p_pp_b);
251 hr_utility.trace('After fetch Return Value - C:'||p_pp_c);
252 hr_utility.trace('EMPLOYEE CONTRIBUTION');
253 hr_utility.trace('After fetch Return Value - A:'||p_ee_a);
254 hr_utility.trace('After fetch Return Value - B:'||p_ee_b);
255 hr_utility.trace('After fetch Return Value - C:'||p_ee_c);
256 hr_utility.trace('EMPLOYER CONTRIBUTION');
257 hr_utility.trace('After fetch Return Value - A:'||p_er_a);
258 hr_utility.trace('After fetch Return Value - B:'||p_er_b);
259 hr_utility.trace('After fetch Return Value - C:'||p_er_c);
260 hr_utility.trace('ANNUAL FTE SALARY');
261 hr_utility.trace('After fetch Return Value - A:'||p_af_a);
262 hr_utility.trace('After fetch Return Value - B:'||p_af_b);
263 hr_utility.trace('After fetch Return Value - C:'||p_af_c);
264 CLOSE c_tier_prrv;
265
266 END IF;
267
268 RETURN 1; -- 1 = Success
269 EXCEPTION
270 WHEN OTHERS THEN
271 hr_utility.trace('Error in the Function PQP_GB_TP_CONTRIBUTION_REPORT.TPS_PENSION_TIER.');
272 RETURN 0; -- 0 = Failure
273 END TPS_PENSION_INPUT_VALUES;
274 --
275
276 PROCEDURE generate( p_employer NUMBER
277 ,p_payroll NUMBER
278 ,p_period NUMBER
279 ,p_template_name VARCHAR2
280 ,p_xml OUT NOCOPY CLOB) IS
281
282 ---------------------
283 CURSOR csr_employer_details(p_bg_id NUMBER) IS
284 SELECT hou.name
285 FROM hr_organization_units hou
286 WHERE hou.organization_id = p_bg_id;
287 ---------------------
288 CURSOR csr_payroll_details(p_payroll_id NUMBER, p_effective_date DATE) IS
289 SELECT pay.payroll_name
290 FROM pay_payrolls_f pay
291 WHERE pay.payroll_id = p_payroll_id
292 AND p_effective_date BETWEEN pay.effective_start_date
293 AND pay.effective_end_date;
294 ---------------------
295 CURSOR csr_period_details(p_period_id NUMBER) IS
296 SELECT ptp.period_name
297 ,ptp.start_date
298 ,ptp.end_date
299 FROM per_time_periods ptp
300 WHERE ptp.time_period_id = p_period_id;
301 ---------------------
302 CURSOR csr_user_table_details(p_effective_date DATE) IS
303 SELECT max (decode (uc.user_column_name
304 ,'Pension Tier'
305 ,uci.value
306 ,0)) tier
307 ,max (decode (uc.user_column_name
308 ,'Employer Contribution Rate'
309 ,uci.value
310 ,0)) er
311 ,max (decode (uc.user_column_name
312 ,'Employee Contribution Rate'
313 ,uci.value
314 ,0)) ee
315 ,max (ur.row_low_range_or_name) low
316 ,max (ur.row_high_range) high
317 ,0 salary
318 ,0 er_contribution
319 ,0 ee_contribution
320 FROM pay_user_column_instances_f uci
321 ,pay_user_columns uc
322 ,pay_user_tables ut
323 ,pay_user_rows_f ur
324 WHERE uc.user_column_id = uci.user_column_id
325 AND uci.user_row_id = ur.user_row_id
326 AND uc.user_table_id = ut.user_table_id
327 AND ur.user_table_id = ut.user_table_id
328 AND ut.user_table_name = 'PQP_TPA_PENSIONABLE_PAY_RANGE'
329 AND ut.legislation_code = 'GB'
330 AND uc.legislation_code = 'GB'
331 AND ur.legislation_code = 'GB'
332 AND p_effective_date BETWEEN ur.effective_start_date
333 AND ur.effective_end_date
334 AND p_effective_date BETWEEN uci.effective_start_date
335 AND uci.effective_end_date
336 GROUP BY ur.user_row_id
337 ORDER BY tier;
338 ---------------------
339 CURSOR csr_element_type_details(p_effective_date DATE) IS
340 SELECT pet.element_type_id
341 FROM pay_element_types_f pet
342 WHERE pet.legislation_code = 'GB'
343 AND p_effective_date BETWEEN pet.effective_start_date
344 AND pet.effective_end_date
345 AND pet.element_name = 'Tiered Contributions Information';
346 ---------------------
347 CURSOR csr_input_value_details(p_element_type_id pay_element_types_f.element_type_id%TYPE
348 , p_effective_date DATE) IS
349 SELECT substr (name
350 ,length (name)
351 ,1) abc
352 ,max (decode (substr (name
353 ,1
354 ,length (name) - 2)
355 ,'Pensionable Pay'
356 ,piv.input_value_id
357 ,0)) pay
358 ,max (decode (substr (name
359 ,1
360 ,length (name) - 2)
361 ,'EE Contribution'
362 ,piv.input_value_id
363 ,0)) ee
364 ,max (decode (substr (name
365 ,1
366 ,length (name) - 2)
367 ,'ER Contribution'
368 ,piv.input_value_id
369 ,0)) er
370 ,max (decode (substr (name
371 ,1
372 ,length (name) - 2)
373 ,'Pension Tier'
374 ,piv.input_value_id
375 ,0)) tier
376 FROM pay_input_values_f piv
377 WHERE piv.element_type_id = p_element_type_id
378 AND piv.legislation_code = 'GB'
379 AND p_effective_date BETWEEN piv.effective_start_date
380 AND piv.effective_end_date
381 GROUP BY substr (name
382 ,length (name)
383 ,1);
384 ---------------------
385 -- Modified Cursor as part of fix for bug#14392137
386 CURSOR csr_employee_count(p_business_group_id pay_payroll_actions.business_group_id%TYPE
387 ,p_payroll_id pay_payroll_actions.payroll_id%TYPE
388 ,p_time_period_id pay_payroll_actions.time_period_id%TYPE
389 ,p_element_type_id pay_run_results.element_type_id%TYPE) IS
390 SELECT COUNT (DISTINCT paa.assignment_id) asg_count
391 FROM pay_payroll_actions ppa
392 ,pay_assignment_actions paa
393 ,pay_run_results prr
394 WHERE ppa.business_group_id = p_business_group_id
395 AND ppa.payroll_id = p_payroll_id
396 AND ppa.action_status = 'C'
397 AND ppa.time_period_id = p_time_period_id
398 AND ppa.action_type IN ('R','B','Q','I')
399 AND paa.payroll_action_id = ppa.payroll_action_id
400 AND paa.action_status = 'C'
401 AND paa.source_action_id IS NOT NULL
402 AND prr.assignment_action_id = paa.assignment_action_id
403 AND prr.element_type_id = p_element_type_id;
404 ---------------------
405 -- Created Cursor as part of fix for bug#14392137 for reversal count
406 CURSOR csr_employee_count_rev(p_business_group_id pay_payroll_actions.business_group_id%TYPE
407 ,p_payroll_id pay_payroll_actions.payroll_id%TYPE
408 ,p_time_period_id pay_payroll_actions.time_period_id%TYPE
409 ,p_element_type_id pay_run_results.element_type_id%TYPE) IS
410 SELECT COUNT (DISTINCT paa.assignment_id) asg_count
411 FROM pay_payroll_actions ppa
412 ,pay_assignment_actions paa
413 ,pay_run_results prr
414 WHERE ppa.business_group_id = p_business_group_id
415 AND ppa.payroll_id = p_payroll_id
416 AND ppa.action_status = 'C'
417 AND ppa.time_period_id = p_time_period_id
418 AND ppa.action_type IN ('V')
419 AND paa.payroll_action_id = ppa.payroll_action_id
420 AND paa.action_status = 'C'
421 AND prr.assignment_action_id = paa.assignment_action_id
422 AND prr.element_type_id = p_element_type_id;
423 ---------------------
424 -- Modified Cursor as part of fix for bug#14392137
425 CURSOR csr_run_results(p_business_group_id pay_payroll_actions.business_group_id%TYPE
426 ,p_payroll_id pay_payroll_actions.payroll_id%TYPE
427 ,p_time_period_id pay_payroll_actions.time_period_id%TYPE
428 ,p_element_type_id pay_run_results.element_type_id%TYPE
429 ,p_input_values csr_input_value_details%ROWTYPE) IS
430 SELECT abs(to_number(tier)) tier
431 ,sum (pay) pay
432 ,sum (ee) ee
433 ,sum (er) er
434 FROM
435 (
436 SELECT sum (decode (prrv.input_value_id
437 ,p_input_values.tier
438 ,prrv.result_value
439 ,0)) tier
440 ,sum (decode (prrv.input_value_id
441 ,p_input_values.pay
442 ,prrv.result_value
443 ,0)) pay
444 ,sum (decode (prrv.input_value_id
445 ,p_input_values.ee
446 ,prrv.result_value
447 ,0)) ee
448 ,sum (decode (prrv.input_value_id
449 ,p_input_values.er
450 ,prrv.result_value
451 ,0)) er
452 FROM pay_payroll_actions ppa
453 ,pay_assignment_actions paa
454 ,pay_run_results prr
455 ,pay_run_result_values prrv
456 WHERE ppa.business_group_id = p_business_group_id
457 AND ppa.payroll_id = p_payroll_id
458 AND ppa.action_status = 'C'
459 AND ppa.time_period_id = p_time_period_id
460 AND ppa.action_type IN ('R','B','Q','I','V')
461 AND paa.payroll_action_id = ppa.payroll_action_id
462 AND paa.action_status = 'C'
463 -- AND paa.source_action_id IS NOT NULL
464 AND prr.assignment_action_id = paa.assignment_action_id
465 AND prr.element_type_id = p_element_type_id
466 AND prrv.run_result_id = prr.run_result_id
467 GROUP BY prr.run_result_id
468 )
469 GROUP BY to_number(tier);
470 ---------------------
471 -- Created Cursor as part of fix for bug#14392137
472 CURSOR csr_run_results_retro(p_business_group_id pay_payroll_actions.business_group_id%TYPE
473 ,p_payroll_id pay_payroll_actions.payroll_id%TYPE
474 ,p_time_period_id pay_payroll_actions.time_period_id%TYPE
475 ,p_element_type_id pay_run_results.element_type_id%TYPE
476 ,p_input_values csr_input_value_details%ROWTYPE) IS
477 SELECT abs(to_number(tier)) tier
478 ,sum (pay) pay
479 ,sum (ee) ee
480 ,sum (er) er
481 FROM
482 (
483 SELECT sum (decode (prrv.input_value_id
484 ,p_input_values.tier
485 ,prrv.result_value
486 ,0)) tier
487 ,sum (decode (prrv.input_value_id
488 ,p_input_values.pay
489 ,prrv.result_value
490 ,0)) pay
491 ,sum (decode (prrv.input_value_id
492 ,p_input_values.ee
493 ,prrv.result_value
494 ,0)) ee
495 ,sum (decode (prrv.input_value_id
496 ,p_input_values.er
497 ,prrv.result_value
498 ,0)) er
499 FROM pay_run_results prr
500 ,pay_run_result_values prrv
501 WHERE prr.assignment_action_id IN
502 (
503 SELECT paa.ASSIGNMENT_ACTION_ID
504 FROM pay_element_entries_f peef
505 ,per_time_periods ptp
506 ,pay_assignment_actions paa
507 WHERE element_type_id = p_element_type_id
508 AND CREATOR_TYPE = 'RR'
509 AND ptp.TIME_PERIOD_ID = p_time_period_id
510 AND ptp.START_DATE = peef.effective_start_date
511 AND ptp.END_DATE = peef.effective_end_date
512 AND peef.SOURCE_ASG_ACTION_ID = paa.SOURCE_ACTION_ID
513 )
514 AND prrv.run_result_id = prr.run_result_id
515 GROUP BY prr.run_result_id
516 )
517 GROUP BY to_number(tier);
518 ---------------------
519 TYPE user_table IS TABLE OF csr_user_table_details%ROWTYPE INDEX BY BINARY_INTEGER;
520 TYPE input_values IS TABLE OF csr_input_value_details%ROWTYPE INDEX BY BINARY_INTEGER;
521
522 r_period_details csr_period_details%ROWTYPE;
523 r_payroll_details csr_payroll_details%ROWTYPE;
524 r_employer_details csr_employer_details%ROWTYPE;
525 r_element_type_details csr_element_type_details%ROWTYPE;
526 r_employee_count csr_employee_count%ROWTYPE;
527 r_employee_count_rev csr_employee_count_rev%ROWTYPE;
528 r_retro_employee_count NUMBER :=0;
529
530 data user_table;
531 data1 user_table;
532 ip input_values;
533 l_number BINARY_INTEGER := 1;
534 l_string VARCHAR2(32767) := NULL;
535 l_xml CLOB;
536
537 -------------------------------------------------------------------------------
538 BEGIN
539 IF g_debug THEN
540 hr_utility.set_location(' Entering Procedure GENERATE',1);
541 END IF;
542
543 OPEN csr_period_details(p_period);
544 FETCH csr_period_details INTO r_period_details;
545 CLOSE csr_period_details;
546
547 OPEN csr_payroll_details(p_payroll
548 ,r_period_details.end_date);
549 FETCH csr_payroll_details INTO r_payroll_details;
550 CLOSE csr_payroll_details;
551
552 OPEN csr_employer_details(p_employer);
553 FETCH csr_employer_details INTO r_employer_details;
554 CLOSE csr_employer_details;
555
556 OPEN csr_element_type_details(r_period_details.end_date);
557 FETCH csr_element_type_details INTO r_element_type_details;
558 CLOSE csr_element_type_details;
559
560 OPEN csr_employee_count(p_employer
561 ,p_payroll
562 ,p_period
563 ,r_element_type_details.element_type_id);
564 FETCH csr_employee_count INTO r_employee_count;
565 CLOSE csr_employee_count;
566
567 -- fix for bug#14392137
568 OPEN csr_employee_count_rev(p_employer
569 ,p_payroll
570 ,p_period
571 ,r_element_type_details.element_type_id);
572 FETCH csr_employee_count_rev INTO r_employee_count_rev;
573 CLOSE csr_employee_count_rev;
574
575 r_employee_count.asg_count := r_employee_count.asg_count-r_employee_count_rev.asg_count;
576 -- End of fix for bug#14392137
577
578 --Fetching count of Retro employees for the Bug: 14249708
579 SELECT COUNT(DISTINCT(paa.ASSIGNMENT_ID))
580 INTO r_retro_employee_count
581 FROM pay_element_entries_f peef
582 ,per_time_periods ptp
583 ,pay_assignment_actions paa
584 WHERE element_type_id = r_element_type_details.element_type_id
585 AND CREATOR_TYPE = 'RR'
586 AND ptp.TIME_PERIOD_ID = p_period
587 AND ptp.START_DATE = peef.effective_start_date
588 AND ptp.END_DATE = peef.effective_end_date
589 AND peef.SOURCE_ASG_ACTION_ID = paa.SOURCE_ACTION_ID;
590 --Fetching count of Retro employees for the Bug: 14249708
591
592 ip.DELETE;
593 FOR r_input_value_details IN csr_input_value_details(r_element_type_details.element_type_id
594 ,r_period_details.end_date)
595 LOOP
596 ip(l_number) := r_input_value_details;
597 l_number := l_number + 1;
598 END LOOP;
599
600 data.DELETE;
601 data1.DELETE;
602 FOR r_user_table_details IN csr_user_table_details(r_period_details.end_date)
603 LOOP
604 data(r_user_table_details.tier) := r_user_table_details;
605 data1(r_user_table_details.tier) := r_user_table_details;
606 END LOOP;
607
608 FOR i IN ip.FIRST..ip.LAST
609 LOOP
610
611 FOR r_run_results IN csr_run_results(p_employer
612 ,p_payroll
613 ,p_period
614 ,r_element_type_details.element_type_id
615 ,ip(i))
616 LOOP
617
618 IF data.EXISTS(r_run_results.tier) THEN
619
620 data(r_run_results.tier).salary := NVL(data(r_run_results.tier).salary,0) +
621 r_run_results.pay;
622 data(r_run_results.tier).er_contribution := NVL(data(r_run_results.tier).er_contribution,0) +
623 r_run_results.er;
624 data(r_run_results.tier).ee_contribution := NVL(data(r_run_results.tier).ee_contribution,0) +
625 r_run_results.ee;
626
627 END IF;
628
629 END LOOP;
630
631 -- Created as part of fix for bug#14392137
632 FOR r_run_results_retro IN csr_run_results_retro(p_employer
633 ,p_payroll
634 ,p_period
635 ,r_element_type_details.element_type_id
636 ,ip(i))
637 LOOP
638
639 IF data1.EXISTS(r_run_results_retro.tier) THEN
640
641 data1(r_run_results_retro.tier).salary := NVL(data1(r_run_results_retro.tier).salary,0) +
642 r_run_results_retro.pay;
643 data1(r_run_results_retro.tier).er_contribution := NVL(data1(r_run_results_retro.tier).er_contribution,0) +
644 r_run_results_retro.er;
645 data1(r_run_results_retro.tier).ee_contribution := NVL(data1(r_run_results_retro.tier).ee_contribution,0) +
646 r_run_results_retro.ee;
647 END IF;
648 -- End of Creation as part of fix for bug#14392137
649 END LOOP;
650 END LOOP;
651
652 -- Build XML Structure --
653 l_string := l_string || '<PQPGBTPCR>'||EOL;
654 l_string := l_string || '<ESTABLISHMENT_NAME>'||r_employer_details.name||'</ESTABLISHMENT_NAME>'||EOL;
655 l_string := l_string || '<PERIOD>'||r_period_details.period_name||' ('
656 || fnd_date.date_to_displaydate (r_period_details.start_date)|| ' - '
657 || fnd_date.date_to_displaydate (r_period_details.end_date)|| ')'
658 ||'</PERIOD>'||EOL;
659 l_string := l_string || '<PAYROLL_NAME>'||r_payroll_details.payroll_name||'</PAYROLL_NAME>'||EOL;
660 l_string := l_string || '<COUNT_CONTRIBUTING_EMPLOYEES>'||r_employee_count.asg_count
661 || '</COUNT_CONTRIBUTING_EMPLOYEES>'||EOL;
662 l_string := l_string || '<R_COUNT_CONTRIBUTING_EMPLOYEES>'||r_retro_employee_count
663 || '</R_COUNT_CONTRIBUTING_EMPLOYEES>'||EOL; --bug#14392137
664
665 FOR i IN data.FIRST..data.LAST
666 LOOP
667 l_string := l_string || '<G_TIER>'||EOL;
668 l_string := l_string || '<TIER>'||data(i).tier||'</TIER>'||EOL;
669 l_string := l_string || '<EMPLOYEE_PERCENTAGE>'||data(i).ee||'</EMPLOYEE_PERCENTAGE>'||EOL;
670 l_string := l_string || '<EMPLOYER_PERCENTAGE>'||data(i).er||'</EMPLOYER_PERCENTAGE>'||EOL;
671 l_string := l_string || '<SALARY_CONTRIBUTION>'||data(i).salary||'</SALARY_CONTRIBUTION>'||EOL;
672 l_string := l_string || '<EMPLOYERS_CONTIBUTION>'||data(i).er_contribution||'</EMPLOYERS_CONTIBUTION>'||EOL;
673 l_string := l_string || '<EMPLOYEES_CONTIBUTION>'||data(i).ee_contribution||'</EMPLOYEES_CONTIBUTION>'||EOL;
674 l_string := l_string || '</G_TIER>'||EOL;
675 END LOOP;
676
677 -- Added additional tags as part of fix: 14392137
678 FOR i IN data1.FIRST..data1.LAST
679 LOOP
680 l_string := l_string || '<G_R_TIER>'||EOL;
681 l_string := l_string || '<R_TIER>'||data1(i).tier||'</R_TIER>'||EOL;
682 l_string := l_string || '<R_EMPLOYEE_PERCENTAGE>'||data1(i).ee||'</R_EMPLOYEE_PERCENTAGE>'||EOL;
683 l_string := l_string || '<R_EMPLOYER_PERCENTAGE>'||data1(i).er||'</R_EMPLOYER_PERCENTAGE>'||EOL;
684 l_string := l_string || '<R_SALARY_CONTRIBUTION>'||data1(i).salary||'</R_SALARY_CONTRIBUTION>'||EOL;
685 l_string := l_string || '<R_EMPLOYERS_CONTIBUTION>'||data1(i).er_contribution||'</R_EMPLOYERS_CONTIBUTION>'||EOL;
686 l_string := l_string || '<R_EMPLOYEES_CONTIBUTION>'||data1(i).ee_contribution||'</R_EMPLOYEES_CONTIBUTION>'||EOL;
687 l_string := l_string || '</G_R_TIER>'||EOL;
688 END LOOP;
689 -- End of Adding additional tags as part of fix: 14392137
690 l_string := l_string || '</PQPGBTPCR>'||EOL;
691
692 /* Writing XML File */
693 dbms_lob.createtemporary(l_xml,FALSE,DBMS_LOB.CALL);
694 dbms_lob.open(l_xml,dbms_lob.lob_readwrite);
695 dbms_lob.writeAppend( l_xml, length(l_string), l_string);
696 p_xml := l_xml;
697 dbms_lob.freeTemporary(l_xml);
698
699 IF g_debug THEN
700 hr_utility.set_location(' Leaving Procedure GENERATE',2);
701 END IF;
702
703 EXCEPTION
704 WHEN others THEN
705 IF g_debug THEN
706 hr_utility.set_location('Error raised in GENERATE ',9);
707 hr_utility.trace('Error: '||sqlerrm);
708 END IF;
709 RAISE;
710 END generate;
711
712 -------------------------------------------------------------------------------
713 -------------------------------------------------------------------------------
714 /* The below function will be used as Formula function definition.
715 It takes all the necessary inputs and calculates the Annual FTE salary
716 for the assignment. Using this Annual FTE Salary it performs the UDT
717 lookup to identify the approriate Pension Tier. The return values are
718 the Pension tier, Employee and Employer Percentages and the calculated
719 Annual FTE Salary.
720 */
721 function supply_pension_bands ( p_assignment_id in number,
722 p_business_group_id in number,
723 p_date_worked in date,
724 /* below input params for salary rates */
725 p_sal_rate1 in varchar2,
726 p_sal_rate2 in varchar2,
727 p_sal_rate3 in varchar2,
728 /* below input params for allowance rates */
729 p_allow_rate1 in varchar2,
730 p_allow_rate2 in varchar2,
731 p_allow_rate3 in varchar2,
732 /* below params for out */
733 p_tier in out nocopy number,
734 p_ee_percentage in out nocopy number,
735 p_er_percentage in out nocopy number,
736 p_annual_fte_rate in out nocopy number
737 ) return number is
738 l_proc varchar2(100) := g_package||'.supply_pension_bands ';
739
740 l_annual_fte_rate number := 0;
741 l_band_ee_percent number := 0;
742 l_band_er_percent number := 0;
743 l_pension_tier number := 0;
744
745 begin
746
747 hr_utility.set_location('Entering '||l_proc,10);
748 hr_utility.set_location('p_assignment_id '||p_assignment_id,10);
749 hr_utility.set_location('p_business_group_id '||p_business_group_id,10);
750 hr_utility.set_location('p_date_worked '||p_date_worked,10);
751 hr_utility.set_location('p_sal_rate1 '||p_sal_rate1,10);
752 hr_utility.set_location('p_sal_rate2 '||p_sal_rate2,10);
753 hr_utility.set_location('p_sal_rate3 '||p_sal_rate3,10);
754 hr_utility.set_location('p_allow_rate1 '||p_allow_rate1,10);
755 hr_utility.set_location('p_allow_rate2 '||p_allow_rate2,10);
756 hr_utility.set_location('p_allow_rate3 '||p_allow_rate3,10);
757
758
759 hr_utility.set_location(l_proc||'Inside Claim element:'||p_date_worked, 30);
760
761 if trim(p_sal_rate1) is not null then
762 l_annual_fte_rate := l_annual_fte_rate +
763 pqp_rates_history_calc.get_historic_rate(
764 p_assignment_id => p_assignment_id,
765 p_rate_name => p_sal_rate1,
766 p_effective_date => p_date_worked,
767 p_time_dimension => 'A',
768 p_rate_type_or_element => 'R'
769 );
770 end if;
771
772 hr_utility.set_location(l_proc||'l_annual_fte_rate:'||l_annual_fte_rate, 31);
773
774 if trim(p_sal_rate2) is not null then
775 l_annual_fte_rate := l_annual_fte_rate +
776 pqp_rates_history_calc.get_historic_rate(
777 p_assignment_id => p_assignment_id,
778 p_rate_name => p_sal_rate2,
779 p_effective_date => p_date_worked,
780 p_time_dimension => 'A',
781 p_rate_type_or_element => 'R'
782 );
783 end if;
784
785 if trim(p_sal_rate3) is not null then
786 l_annual_fte_rate := l_annual_fte_rate +
787 pqp_rates_history_calc.get_historic_rate(
788 p_assignment_id => p_assignment_id,
789 p_rate_name => p_sal_rate3,
790 p_effective_date => p_date_worked,
791 p_time_dimension => 'A',
792 p_rate_type_or_element => 'R'
793 );
794 end if;
795
796 if trim(p_allow_rate1) is not null then
797 l_annual_fte_rate := l_annual_fte_rate +
798 pqp_rates_history_calc.get_historic_rate(
799 p_assignment_id => p_assignment_id,
800 p_rate_name => p_allow_rate1,
801 p_effective_date => p_date_worked,
802 p_time_dimension => 'A',
803 p_rate_type_or_element => 'R'
804 );
805 end if;
806
807 if trim(p_allow_rate2) is not null then
808 l_annual_fte_rate := l_annual_fte_rate +
809 pqp_rates_history_calc.get_historic_rate(
810 p_assignment_id => p_assignment_id,
811 p_rate_name => p_allow_rate2,
812 p_effective_date => p_date_worked,
813 p_time_dimension => 'A',
814 p_rate_type_or_element => 'R'
815 );
816 end if;
817
818 if trim(p_allow_rate3) is not null then
819 l_annual_fte_rate := l_annual_fte_rate +
820 pqp_rates_history_calc.get_historic_rate(
821 p_assignment_id => p_assignment_id,
822 p_rate_name => p_allow_rate3,
823 p_effective_date => p_date_worked,
824 p_time_dimension => 'A',
825 p_rate_type_or_element => 'R'
826 );
827 end if;
828
829 hr_utility.set_location(l_proc||'Annual FTE Rate:'||l_annual_fte_rate, 35);
830
831 l_band_ee_percent := hruserdt.get_table_value(
832 p_bus_group_id => p_business_group_id,
833 p_table_name => 'PQP_TPA_PENSIONABLE_PAY_RANGE',
834 p_col_name => 'Employee Contribution Rate',
835 p_row_value => to_char(l_annual_fte_rate),
836 p_effective_date => p_date_worked );
837
838 hr_utility.set_location(l_proc||'EE Percentage:'||l_band_ee_percent, 36);
839
840 l_band_er_percent := hruserdt.get_table_value(
841 p_bus_group_id => p_business_group_id,
842 p_table_name => 'PQP_TPA_PENSIONABLE_PAY_RANGE',
843 p_col_name => 'Employer Contribution Rate',
844 p_row_value => to_char(l_annual_fte_rate),
845 p_effective_date => p_date_worked );
846
847 hr_utility.set_location(l_proc||'ER Percentage:'||l_band_er_percent, 36);
848
849 l_pension_tier := hruserdt.get_table_value(
850 p_bus_group_id => p_business_group_id,
851 p_table_name => 'PQP_TPA_PENSIONABLE_PAY_RANGE',
852 p_col_name => 'Pension Tier',
853 p_row_value => to_char(l_annual_fte_rate),
854 p_effective_date => p_date_worked );
855
856 hr_utility.set_location(l_proc||'Pension Tier:'||l_pension_tier, 37);
857
858 p_annual_fte_rate := l_annual_fte_rate;
859 p_tier := l_pension_tier;
860 p_ee_percentage := l_band_ee_percent;
861 p_er_percentage := l_band_er_percent;
862
863 hr_utility.set_location('Leaving '||l_proc,100);
864 return 0;
865 exception
866 when others then
867 hr_utility.set_location('Exception '||l_proc||':'||sqlerrm,-100);
868 return -1;
869 end supply_pension_bands;
870 --------------------------------------------------------------------
871
872 END pqp_gb_tp_contribution_report;