1 Package Body Pqp_UK_Union_Deduction AS
2 /* $Header: pqgbundf.pkb 115.7 2003/03/13 02:09:54 tmehra noship $ */
3
4
5 g_proc VARCHAR2(31):= 'pqp_uk_union_deduction.';
6 g_union_org_info_type VARCHAR2(30):= 'GB_TRADE_UNION_DETAILS';
7 g_union_ele_extra_info_type VARCHAR2(30):= 'PQP_UK_UNION_INFO';
8
9 /*=======================================================================
10 * GET_UK_UNION_ELE_EXTRA_INFO
11 *
12 * Formula Funtion, uses the context of element_type_id
13 *
14 * Extracts element type extra information for a give (union) element
15 * with an infomation type of 'PQP_UK_UNION_INFO'
16 *
17 *=======================================================================*/
18
19 Function get_uk_union_ele_extra_info
20 (p_element_type_id IN NUMBER -- Context
21 ,p_union_organization_id OUT NOCOPY NUMBER
22 ,p_union_level_balance_name OUT NOCOPY VARCHAR2
23 ,p_pension_rate_type_name OUT NOCOPY VARCHAR2
24 ,p_fund_list OUT NOCOPY VARCHAR2
25 ,p_ERROR_MESSAGE OUT NOCOPY VARCHAR2
26 )
27 Return Number
28
29 Is
30
31 l_proc VARCHAR2(61):= g_proc||'get_uk_union_ele_extra_info';
32 l_ret_vlu NUMBER(2):= 0;
33
34 -- The following curosor has been replaced for the performance fixes.
35 -- The view hr_lookups has been replaced with the base table fnd_lookups.
36 -- The restriction clause NVL(hrl.lookup_type,'PQP_RATE_TYPE') = 'PQP_RATE_TYPE'
37 -- and NVL(hrl.enabled_flag,'Y') = 'Y' have been eliminated by using an
38 -- In-line view on fnd_lookups.
39
40 /*
41 CURSOR csr_get_union_ele_extra_info IS
42 SELECT TO_NUMBER(eei.eei_information1) -- Union Organisation_ID
43 ,eei.eei_information2 -- Union Level Balance Name
44 ,hrl.meaning -- Pension Rate Type Name
45 ,eei.eei_information4 -- Union Funds Lookup Type - Fund List
46 FROM pay_element_types_f ele
47 ,pay_element_type_extra_info eei
48 ,hr_lookups hrl
49 ,fnd_sessions fnd
50 WHERE ele.element_type_id = p_element_type_id
51 AND eei.element_type_id = ele.element_type_id
52 AND eei.information_type = g_union_ele_extra_info_type
53 AND NVL(hrl.lookup_type,'PQP_RATE_TYPE') = 'PQP_RATE_TYPE'
54 AND NVL(hrl.enabled_flag,'Y') = 'Y'
55 AND hrl.lookup_code(+) = eei.eei_information3
56 AND fnd.effective_date BETWEEN ele.effective_start_date
57 AND ele.effective_end_date
58 AND fnd.session_id = USERENV('sessionid');
59
60 */
61
62 CURSOR csr_get_union_ele_extra_info IS
63 SELECT TO_NUMBER(eei.eei_information1) -- Union Organisation_ID
64 ,eei.eei_information2 -- Union Level Balance Name
65 ,hrl.meaning -- Pension Rate Type Name
66 ,eei.eei_information4 -- Union Funds Lookup Type - Fund List
67 FROM pay_element_types_f ele
68 ,pay_element_type_extra_info eei
69 ,fnd_sessions fnd
70 ,(SELECT *
71 FROM fnd_lookup_values
72 WHERE lookup_type = 'PQP_RATE_TYPE'
73 AND enabled_flag = 'Y') hrl
74 WHERE ele.element_type_id = p_element_type_id
75 AND eei.element_type_id = ele.element_type_id
76 AND eei.information_type = g_union_ele_extra_info_type
77 AND hrl.lookup_code(+) = eei.eei_information3
78 AND fnd.effective_date BETWEEN ele.effective_start_date
79 AND ele.effective_end_date
80 AND fnd.session_id = USERENV('sessionid');
81
82
83 BEGIN
84
85 hr_utility.set_location(' Entering: '||l_proc, 10);
86
87 OPEN csr_get_union_ele_extra_info;
88
89 FETCH csr_get_union_ele_extra_info
90 INTO p_union_organization_id
91 ,p_union_level_balance_name
92 ,p_pension_rate_type_name
93 ,p_fund_list;
94
95 IF csr_get_union_ele_extra_info%NOTFOUND THEN
96
97 l_ret_vlu := -1;
98 p_ERROR_MESSAGE :=
99 'Add any extra information type details that are missing from the union '||
100 'element and then retry the payroll run. If you continue to receive '||
101 'this message '||
102 --'when all extra information is correct '||
103 --'information is correct, '||
104 'then '||
105 --'the union organization may have been '||
106 --'deleted. If so, '||
107 'contact your support representative.';
108
109 END IF;
110
111 CLOSE csr_get_union_ele_extra_info;
112
113 hr_utility.set_location(' Leaving: '||l_proc, 20);
114
115 RETURN l_ret_vlu;
116
117 -- Added by tmehra for nocopy changes Feb'03
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 hr_utility.set_location('Entering excep:'||l_proc, 35);
122
123 p_union_organization_id := NULL;
124 p_union_level_balance_name := NULL;
125 p_pension_rate_type_name := NULL;
126 p_fund_list := NULL;
127 p_ERROR_MESSAGE := SQLERRM;
128
129 raise;
130
131 END get_uk_union_ele_extra_info;
132
133 /*=======================================================================
134 * GET_UK_UNION_ORG_INFO
135 *
136 * Formula Function
137 *
138 * Extracts Organization Information (type 'GB_TRADE_UNION_INFO') for a
139 * given Union type organization.
140 * This function will be used only by the existing elements. New element
141 * created using the template will be using the function
142 * get_uk_union_orginfo_fnddate.
143 *=======================================================================*/
144
145 --
146 FUNCTION get_uk_union_org_info
147 (p_union_organization_id IN NUMBER
148 ,p_union_rates_table_id OUT NOCOPY NUMBER
149 ,p_union_rates_table_name OUT NOCOPY VARCHAR2
150 ,p_union_rates_table_type OUT NOCOPY VARCHAR2
151 ,p_union_recalculation_date OUT NOCOPY VARCHAR2 --Returned 'DD-MON-YYYY'
152 ,p_ERROR_MESSAGE OUT NOCOPY VARCHAR2
153 )
154 RETURN NUMBER
155 IS
156
157 l_proc VARCHAR2(61):= g_proc||'get_uk_union_org_info';
158 l_ret_vlu NUMBER(2):= 0;
159
160 CURSOR csr_get_union_org_info IS
161 SELECT TO_NUMBER(hoi.org_information1) -- Rates Table ID
162 ,tbls.user_table_name -- Rates Table Name
163 ,tbls.range_or_match -- Rates Table Type 'R' or 'M'
164 ,to_char(fnd_date.canonical_to_date(hoi.org_information2),'DD-MON')||'-'|| -- Recalculation Date
165 DECODE( -- Compare the recalculation month to the effective month
166 SIGN( -- By checking the difference between
167 (
168 TO_CHAR(fnd_date.canonical_to_date(hoi.org_information2),'MM')
169 -1
170 ) -- The month of the recalculation date less 1
171 -
172 (
173 TO_CHAR(fnds.effective_date,'MM')
174 ) -- The month of the current effective date
175 )
176 ,-1 -- Recalculation month < than current month
177 , TO_CHAR(fnds.effective_date,'YYYY') -- use current year
178 -- Recalculation month >= than current month
179 ,TO_CHAR(fnds.effective_date-365,'YYYY') -- use previous year
180 )
181 FROM hr_organization_information hoi
182 ,pay_user_tables tbls
183 ,fnd_sessions fnds
184 WHERE hoi.organization_id = p_union_organization_id
185 AND hoi.org_information_context = g_union_org_info_type
186 AND tbls.user_table_id = TO_NUMBER(hoi.org_information1)
187 AND fnds.session_id = USERENV('sessionid');
188
189 BEGIN
190
191
192 hr_utility.set_location(' Entering: '||l_proc, 10);
193
194 OPEN csr_get_union_org_info;
195
196 FETCH csr_get_union_org_info
197 INTO p_union_rates_table_id
198 ,p_union_rates_table_name
199 ,p_union_rates_table_type
200 ,p_union_recalculation_date;
201
202 IF csr_get_union_org_info%NOTFOUND THEN
203
204 l_ret_vlu := -1;
205 p_ERROR_MESSAGE :=
206 'You must complete all the details for your trade union organization '||
207 'before you run this payroll';
208
209 END IF;
210
211 CLOSE csr_get_union_org_info;
212 hr_utility.set_location('Leaving: '||l_proc, 20);
213
214 RETURN l_ret_vlu;
215
216 -- Added by tmehra for nocopy changes Feb'03
217
218 EXCEPTION
219 WHEN OTHERS THEN
220 hr_utility.set_location('Entering excep:'||l_proc, 35);
221
222 p_union_rates_table_id := NULL;
223 p_union_rates_table_name := NULL;
224 p_union_rates_table_type := NULL;
225 p_union_recalculation_date := NULL;
226 p_ERROR_MESSAGE := SQLERRM;
227
228
229 raise;
230
231 END get_uk_union_org_info;
232
233 /*=======================================================================
234 * GET_UK_UNION_ORGINFO_FNDDATE
235 *
236 * Formula Function :
237 *
238 * Extracts Organization Information (type 'GB_TRADE_UNION_INFO') for a
239 * given Union type organization.This function return p_union_recalculation_date
240 * as a date field. This function will now be used for all Union elements created
241 * using the deducation template.
242 *=======================================================================*/
243
244 --
245 Function get_uk_union_orginfo_fnddate
246 (p_union_organization_id IN NUMBER
247 ,p_union_rates_table_id OUT NOCOPY NUMBER
248 ,p_union_rates_table_name OUT NOCOPY VARCHAR2
249 ,p_union_rates_table_type OUT NOCOPY VARCHAR2
250 ,p_union_recalculation_date OUT NOCOPY date --Returned fnd_canonical_date
251 ,p_ERROR_MESSAGE OUT NOCOPY VARCHAR2
252 )
253 Return Number
254 Is
255 l_proc VARCHAR2(61):= g_proc||'get_uk_union_org_info';
256 l_ret_vlu NUMBER(2):= 0;
257
258 Cursor Csr_Get_Union_Org_Info Is
259 Select To_Number(hoi.org_information1) -- Rates Table ID
260 ,tbls.user_table_name -- Rates Table Name
261 ,tbls.range_or_match -- Rates Table Type 'R' or 'M'
262 ,-- Recalculation Date
263 DECODE( -- Compare the recalculation month to the effective month
264 SIGN( -- By checking the difference between
265 (
266 TO_CHAR(fnd_date.canonical_to_date(hoi.org_information2),'MM')
267 -1
268 ) -- The month of the recalculation date less 1
269 -
270 (
271 TO_CHAR(fnds.effective_date,'MM')
272 ) -- The month of the current effective date
273 )
274 ,-1 -- Recalculation month < than current month
275 , TO_CHAR(fnds.effective_date,'YYYY') -- use current year
276 -- Recalculation month >= than current month
277 ,TO_CHAR(fnds.effective_date-365,'YYYY') -- use previous year
278 )
279 ||'/'||to_char(fnd_date.canonical_to_date(hoi.org_information2),'MM/DD')
280 FROM hr_organization_information hoi
281 ,pay_user_tables tbls
282 ,fnd_sessions fnds
283 WHERE hoi.organization_id = p_union_organization_id
284 AND hoi.org_information_context = g_union_org_info_type
285 AND tbls.user_table_id = TO_NUMBER(hoi.org_information1)
286 AND fnds.session_id = USERENV('sessionid');
287
288 l_recalculation_date varchar(15);
289
290 BEGIN
291
292
293 hr_utility.set_location(' Entering: '||l_proc, 10);
294
295 OPEN csr_get_union_org_info;
296
297 FETCH csr_get_union_org_info
298 INTO p_union_rates_table_id
299 ,p_union_rates_table_name
300 ,p_union_rates_table_type
301 ,l_recalculation_date;
302
303 p_union_recalculation_date := to_date(l_recalculation_date,'YYYY/MM/DD');
304
305 IF csr_get_union_org_info%NOTFOUND THEN
306
307 l_ret_vlu := -1;
308 p_ERROR_MESSAGE :=
309 'You must complete all the details for your trade union organization '||
310 'before you run this payroll';
311
312 END IF;
313
314 CLOSE csr_get_union_org_info;
315 hr_utility.set_location('Leaving: '||l_proc, 20);
316
317 RETURN l_ret_vlu;
318
319
320 -- Added by tmehra for nocopy changes Feb'03
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 hr_utility.set_location('Entering excep:'||l_proc, 35);
325
326 p_union_rates_table_id := NULL;
327 p_union_rates_table_name := NULL;
328 p_union_rates_table_type := NULL;
329 p_union_recalculation_date := NULL;
330 p_ERROR_MESSAGE := SQLERRM;
331
332 raise;
333
334
335 END get_uk_union_orginfo_fnddate;
336
337
338 FUNCTION chk_uk_union_fund_selected
339 (p_union_rates_column_name IN VARCHAR2
340 ,p_union_rates_table_name IN VARCHAR2
341 ,p_ERROR_MESSAGE IN OUT NOCOPY VARCHAR2
342 )
343 RETURN NUMBER
344 IS
345
346 l_proc VARCHAR2(61):= g_proc||'chk_uk_union_fund_selected';
347 l_ret_vlu NUMBER(2):= 0;
348
349 -- nocopy changes
350 l_error_message_nc VARCHAR2(200);
351
352 CURSOR csr_uk_union_fund_selected IS
353 SELECT NULL
354 FROM pay_user_columns cols
355 ,pay_user_tables tbls
356 WHERE tbls.user_table_name = p_union_rates_table_name
357 AND tbls.user_table_id = cols.user_table_id
358 AND cols.user_column_name = p_union_rates_column_name;
359
360 BEGIN
361
362 hr_utility.set_location(' Entering: '||l_proc, 10);
363
364 l_error_message_nc := p_error_message;
365
366 OPEN csr_uk_union_fund_selected;
367
368 FETCH csr_uk_union_fund_selected
369 INTO p_ERROR_MESSAGE;
370
371 IF csr_uk_union_fund_selected%NOTFOUND THEN
372
373 l_ret_vlu := -1;
374 -- p_ERROR_MESSAGE := 'Invalid input value for Fund_Selected.';
375 p_ERROR_MESSAGE :=
376 'Recreate the selected union fund taking care to use the original name. '||
377 'You must also recreate the Union Rates table for this fund '||
378 'with separate columns for Union Fund Weekly and Union Fund Monthly.';
379
380 END IF;
381
382 CLOSE csr_uk_union_fund_selected;
383 hr_utility.set_location('Leaving: '||l_proc, 20);
384
385 RETURN l_ret_vlu;
386
387 -- Added by tmehra for nocopy changes Feb'03
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 hr_utility.set_location('Entering excep:'||l_proc, 35);
392 p_error_message := l_error_message_nc;
393 raise;
394
395 END chk_uk_union_fund_selected;
396
397
398 FUNCTION get_uk_union_rates_table_row
399 (p_union_rates_table_name IN VARCHAR2
400 ,p_union_rates_row_value OUT NOCOPY VARCHAR2
401 ,p_ERROR_MESSAGE OUT NOCOPY VARCHAR2
402 )
403 RETURN NUMBER
404 IS
405
406 l_proc VARCHAR2(61):= g_proc||'get_uk_union_rates_table_row';
407 l_ret_vlu NUMBER(2):= 0;
408
409
410 CURSOR csr_uk_union_rates_table_row IS
411 SELECT urws.row_low_range_or_name
412 FROM pay_user_rows_f urws
413 ,pay_user_tables tbls
414 ,fnd_sessions fnd
415 WHERE tbls.user_table_name = p_union_rates_table_name
416 AND tbls.range_or_match = 'M'
417 AND urws.user_table_id = tbls.user_table_id
418 AND fnd.effective_date BETWEEN urws.effective_start_date
419 AND urws.effective_end_date
420 AND fnd.session_id = USERENV('sessionid');
421
422
423 BEGIN
424
425 hr_utility.set_location(' Entering: '||l_proc, 10);
426
427 OPEN csr_uk_union_rates_table_row;
428
429 FETCH csr_uk_union_rates_table_row
430 INTO p_union_rates_row_value;
431
432 IF csr_uk_union_rates_table_row%NOTFOUND THEN
433
434 l_ret_vlu := -1;
435 p_ERROR_MESSAGE :=
436 -- 'No rows were found for a given exact match union rates table.';
437 'Add the values for the flat rate union deductions to the '||
438 p_union_rates_table_name||' table.';
439 ELSE
440
441 /* Fetch one more to check for more than one row */
442
443 FETCH csr_uk_union_rates_table_row
444 INTO p_union_rates_row_value;
445
446 IF csr_uk_union_rates_table_row%FOUND THEN
447
448 l_ret_vlu := -1;
449 p_ERROR_MESSAGE :=
450 --ore than one effective row found for a given exact match union rates table.';
451 'Oracle Payroll cannot detect which flat rate deduction you want to apply. '||
452 'Edit the '||p_union_rates_table_name||' table so that it '||
453 'includes a single description of flat rate deductions.';
454
455 END IF;
456
457 END IF;
458
459 CLOSE csr_uk_union_rates_table_row;
460 hr_utility.set_location(' Leaving: '||l_proc, 20);
461
462 RETURN l_ret_vlu;
463
464 -- Added by tmehra for nocopy changes Feb'03
465
466 EXCEPTION
467 WHEN OTHERS THEN
468 hr_utility.set_location('Entering excep:'||l_proc, 35);
469 p_union_rates_row_value := NULL;
470 p_ERROR_MESSAGE := SQLERRM;
471 raise;
472
473 END get_uk_union_rates_table_row;
474
475
476 /*============================================================*/
477
478 FUNCTION get_uk_union_rates
479 (p_bus_group_id IN NUMBER -- Context
480 ,p_union_rates_table_name IN VARCHAR2
481 ,p_union_rates_column_name IN VARCHAR2
482 ,p_union_rates_row_value IN VARCHAR2
483 ,p_effective_date IN DATE
484 ,p_Union_Deduction_Value OUT NOCOPY NUMBER
485 ,p_ERROR_MESSAGE OUT NOCOPY VARCHAR2
486 )
487 RETURN NUMBER
488 IS
489
490 l_proc VARCHAR2(61):= g_proc||'get_uk_union_rates';
491
492 BEGIN
493
494 hr_utility.set_location(' Entering: '||l_proc, 10);
495
496 p_Union_Deduction_Value := hruserdt.get_table_value
497 (p_bus_group_id
498 ,p_union_rates_table_name
499 ,p_union_rates_column_name
500 ,p_union_rates_row_value
501 ,p_effective_date -- Default Sesn Date
502 );
503
504 hr_utility.set_location(' Leaving: '||l_proc, 20);
505
506 RETURN 0;
507 EXCEPTION
508
509 WHEN NO_DATA_FOUND THEN
510 p_Union_Deduction_Value := 0;
511 p_ERROR_MESSAGE :=
512 'Add the missing deduction rates to '||p_union_rates_table_name||
513 ' and retry the payroll run.';
514 RETURN -1;
515
516
517 WHEN TOO_MANY_ROWS THEN
518 p_Union_Deduction_Value := 0;
519 p_ERROR_MESSAGE :=
520 'Oracle Payroll cannot detect which union deduction you want to apply. '||
521 'If your deductions are based on salary bands, correct any overlapping '||
522 'bands, repeat your setup of the deductions element and then retry the '||
523 'payroll run.';
524 RETURN -1;
525
526
527 --WHEN OTHERS THEN
528 -- p_Union_Deduction_Value := 0;
529 -- hr_utility.set_message('8303','PQP_UNDTEST_RATESFUN_OTHERS');
530 -- hr_utility.raise_error;
531
532 -- Added by tmehra for nocopy changes Feb'03
533
534 WHEN OTHERS THEN
535
536 p_Union_Deduction_Value := NULL;
537 p_ERROR_MESSAGE := SQLERRM;
538
539 hr_utility.set_location('Entering excep:'||l_proc, 35);
540 raise;
541
542 END get_uk_union_rates;
543
544 /*============================================================*/
545
546 END pqp_uk_union_deduction;
[Home] [Help]