1 PACKAGE BODY PAY_DK_PAYMENT_PROCESS_PKG as
2 /* $Header: pydkpaypr.pkb 120.8.12000000.5 2007/03/27 11:41:43 nprasath noship $ */
3
4 --Global parameters
5 g_package CONSTANT varchar2(33) := 'PAY_DK_PAYMENT_PROCESS_PKG.';
6
7
8
9 -----------------------------------------------------------------------------
10 -- GET_PARAMETER used in SQL to decode legislative parameters
11 -----------------------------------------------------------------------------
12 FUNCTION get_parameter(
13 p_parameter_string IN VARCHAR2
14 ,p_token IN VARCHAR2
15 ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2
16 IS
17 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
18 l_start_pos NUMBER;
19 l_delimiter varchar2(1);
20 l_proc VARCHAR2(60);
21 BEGIN
22 l_delimiter :=' ';
23 l_proc := g_package||' get parameter ';
24
25 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
26 IF l_start_pos = 0 THEN
27 l_delimiter := '|';
28 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
29 end if;
30 IF l_start_pos <> 0 THEN
31 l_start_pos := l_start_pos + length(p_token||'=');
32 l_parameter := substr(p_parameter_string,
33 l_start_pos,
34 instr(p_parameter_string||' ',
35 ',',l_start_pos)
36 - l_start_pos);
37 IF p_segment_number IS NOT NULL THEN
38 l_parameter := ':'||l_parameter||':';
39 l_parameter := substr(l_parameter,
40 instr(l_parameter,':',1,p_segment_number)+1,
41 instr(l_parameter,':',1,p_segment_number+1) -1
42 - instr(l_parameter,':',1,p_segment_number));
43 END IF;
44 END IF;
45 RETURN l_parameter;
46 END get_parameter;
47
48 -----------------------------------------------------------------------------
49 -- GET_LOOKUP_MEANING function used to get labels of items from a lookup
50 -----------------------------------------------------------------------------
51 FUNCTION get_lookup_meaning (p_lookup_type varchar2,p_lookup_code varchar2) RETURN VARCHAR2 IS
52 CURSOR csr_lookup IS
53 select meaning
54 from hr_lookups
55 where lookup_type = p_lookup_type
56 and lookup_code = p_lookup_code
57 and enabled_flag ='Y';
58 l_meaning hr_lookups.meaning%type;
59 BEGIN
60 OPEN csr_lookup;
61 FETCH csr_lookup INTO l_Meaning;
62 CLOSE csr_lookup;
63 RETURN l_meaning;
64 END get_lookup_meaning;
65
66 /* Added the following for Third Party Payments */
67
68 FUNCTION get_ass_action_context(p_assignment_id NUMBER) RETURN NUMBER IS
69 l_context NUMBER;
70
71 CURSOR get_context(p_assignment_id NUMBER) IS
72 SELECT max(paa.ASSIGNMENT_ACTION_ID)
73 FROM pay_payroll_actions ppa
74 , pay_assignment_actions paa
75 WHERE paa.assignment_id = p_assignment_id
76 AND paa.payroll_action_id = ppa.payroll_action_id
77 AND ppa.action_type IN('P','U');
78
79 BEGIN
80
81 OPEN get_context(p_assignment_id);
82 FETCH get_context INTO l_context;
83 CLOSE get_context;
84
85 RETURN l_context;
86
87 END get_ass_action_context;
88 --
89 --
90 FUNCTION get_date_earned_context(p_assignment_id NUMBER) RETURN DATE IS
91 l_context DATE;
92 /* Added nvl for bug 5879516 */
93 CURSOR get_context(p_assignment_id NUMBER) IS
94 SELECT max(ppa.DATE_EARNED)
95 FROM pay_payroll_actions ppa
96 , pay_assignment_actions paa
97 WHERE paa.assignment_id = p_assignment_id
98 AND paa.payroll_action_id = ppa.payroll_action_id
99 AND ppa.action_type IN('P','U');
100
101 /*Added for bug 5930673 */
102 Cursor get_alternate_date_earned (p_assignment_id NUMBER) IS
103 SELECT max(nvl(ppa.date_earned,ppar.date_earned))
104 FROM pay_payroll_actions ppa
105 , pay_assignment_actions paa
106 , pay_action_interlocks pail
107 , pay_payroll_actions ppar
108 , pay_assignment_actions paar
109 WHERE paa.assignment_id = p_assignment_id
110 AND paa.payroll_action_id = ppa.payroll_action_id
111 AND paar.assignment_action_id = pail.locked_action_id
112 AND pail.locking_action_id = paa.assignment_action_id
113 AND paar.payroll_action_id = ppar.payroll_action_id
114 AND ppa.action_type IN('P','U')
115 AND ppar.action_type IN('Q','R');
116
117 BEGIN
118
119 OPEN get_context(p_assignment_id);
120 FETCH get_context INTO l_context;
121 CLOSE get_context;
122 /*Added for bug 5930673 */
123 If l_context is null then
124 OPEN get_alternate_date_earned(p_assignment_id);
125 FETCH get_alternate_date_earned INTO l_context;
126 CLOSE get_alternate_date_earned;
127 End if;
128
129 RETURN l_context;
130
131 END get_date_earned_context;
132 --
133 --
134 /* Added p_org_id to function and modified dimension from _PAYMENTS to _PP_PAYMENTS for pension changes.
135 Also changed call to pay_balance_pkg.get_value */
136 FUNCTION get_prev_bal_paid(p_assignment_id NUMBER,p_org_id NUMBER, p_balance_name VARCHAR2) RETURN NUMBER IS
137 l_context1 NUMBER;
138 l_context2 NUMBER;
139 l_value NUMBER;
140
141 CURSOR get_ass_action_id(p_assignment_id NUMBER) IS
142 SELECT ppp.assignment_action_id
143 FROM pay_payroll_actions ppa
144 , pay_assignment_actions paa
145 , pay_action_interlocks pai
146 , pay_pre_payments ppp
147 WHERE paa.assignment_id = p_assignment_id
148 AND paa.payroll_action_id = ppa.payroll_action_id
149 AND ppa.action_type = 'M'
150 AND ppa.action_status = 'C'
151 AND paa.action_status = 'C'
152 AND paa.pre_payment_id = ppp.pre_payment_id
153 AND pai.locking_action_id = paa.assignment_action_id
154 AND pai.locked_action_id = ppp.assignment_action_id;
155
156
157 /* Modified for pension changes */
158 CURSOR get_def_bal_id(p_balance_name VARCHAR2) IS
159 SELECT pdb.defined_balance_id
160 FROM pay_defined_balances pdb
161 ,pay_balance_types pbt
162 ,pay_balance_dimensions pbd
163 WHERE pbt.legislation_code='DK'
164 AND pbt.balance_name = p_balance_name
165 AND pbd.legislation_code = 'DK'
166 --AND pbd.database_item_suffix = '_PAYMENTS'
167 AND pbd.database_item_suffix = '_PP_PAYMENTS'
168 AND pdb.balance_type_id = pbt.balance_type_id
169 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
170
171
172 BEGIN
173
174 OPEN get_ass_action_id(p_assignment_id);
175 FETCH get_ass_action_id INTO l_context1;
176 CLOSE get_ass_action_id;
177
178 OPEN get_def_bal_id(p_balance_name);
179 FETCH get_def_bal_id INTO l_context2;
180 CLOSE get_def_bal_id;
181
182 --l_value := pay_balance_pkg.get_value(l_context2,l_context1);
183 l_value := pay_balance_pkg.get_value(l_context2,l_context1,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,p_org_id);
184
185 RETURN l_value;
186
187 END get_prev_bal_paid;
188 --
189 --
190 /* FUNCTION get_prev_date_earned_context(p_assignment_id NUMBER) RETURN DATE IS
191 l_context DATE;
192
193 CURSOR get_context(p_assignment_id NUMBER) IS
194 SELECT ppa.date_earned
195 FROM pay_payroll_actions ppa
196 , pay_assignment_actions paa
197 , pay_action_interlocks pai
198 WHERE paa.assignment_id = p_assignment_id
199 AND paa.payroll_action_id = ppa.payroll_action_id
200 AND ppa.action_type = 'M'
201 AND pai.locking_action_id = paa.assignment_action_id
202 AND pai.locked_action_id = get_ass_action_context(p_assignment_id);
203
204 BEGIN
205
206 OPEN get_context(p_assignment_id);
207 FETCH get_context INTO l_context;
208 CLOSE get_context;
209
210 RETURN l_context;
211
212 END get_prev_date_earned_context; */
213 --
214 --
215 FUNCTION get_phy_record_no(p_person_id NUMBER, p_assignment_id NUMBER, p_pp_id VARCHAR2) RETURN NUMBER IS
216
217 /* Cursor for Record I 05 and I 04 re-written as parameters TRANSFER_PERSON_ID and TRANSFER_ASSIGNMENT_ID
218 not available in memory for the first fetch */
219
220 CURSOR get_is_record_05_details_local(p_person_id NUMBER) IS
221 SELECT '1'
222 FROM per_addresses pad
223 /* Modified for bug fix 4593682 */
224 , per_all_people_f pap
225 WHERE /*pad.person_id = p_person_id*/
226 pad.person_id (+)= pap.person_id
227 AND pap.person_id = p_person_id
228 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900);
229
230
231 /* Modified for Pension changes to restrict on Pension Provider*/
232 CURSOR get_is_record_04_details_local(p_assignment_id NUMBER,p_pp_id VARCHAR2) IS
233 SELECT '1'
234 FROM pay_run_results prr1
235 , pay_run_result_values prrv1
236 , pay_run_result_values prrv3
237 , pay_element_types_f pet1
238 , pay_input_values_f piv1
239 , pay_input_values_f piv3
240 , pay_run_results prr2
241 , pay_run_result_values prrv2
242 , pay_run_result_values prrv4
243 , pay_element_types_f pet2
244 , pay_input_values_f piv2
245 , pay_input_values_f piv4
246 , pay_assignment_actions paa
247 , pay_payroll_actions ppa
248 , pay_element_entries_f pee
249 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
250 AND prr1.ELEMENT_TYPE_ID = pet1.ELEMENT_TYPE_ID
251 AND prrv1.RUN_RESULT_ID = prr1.RUN_RESULT_ID
252 AND prrv3.RUN_RESULT_ID = prr1.RUN_RESULT_ID
253 AND pee.ELEMENT_ENTRY_ID = prr1.ELEMENT_ENTRY_ID
254 AND pet1.element_name = 'Retro Pension'
255 AND pet1.legislation_code ='DK'
256 AND piv1.ELEMENT_TYPE_ID = pet1.element_type_id
257 AND piv1.NAME ='Pay Value'
258 AND prrv1.input_value_id = piv1.input_value_id
259 AND piv3.ELEMENT_TYPE_ID = pet1.element_type_id
260 AND piv3.NAME ='Third Party Payee'
261 AND prrv3.input_value_id = piv3.input_value_id
262 AND prrv3.RESULT_VALUE = p_pp_id
263 AND prr2.ELEMENT_TYPE_ID =pet2.ELEMENT_TYPE_ID
264 AND prrv2.RUN_RESULT_ID = prr2.RUN_RESULT_ID
265 AND prrv4.RUN_RESULT_ID = prr2.RUN_RESULT_ID
266 AND prrv4.RESULT_VALUE = prrv3.RESULT_VALUE
267 AND pet2.element_name = 'Retro Employer Pension'
268 AND pet2.legislation_code ='DK'
269 AND piv2.ELEMENT_TYPE_ID = pet2.element_type_id
270 AND piv2.NAME ='Pay Value'
271 AND prrv2.input_value_id = piv2.input_value_id
272 AND piv4.ELEMENT_TYPE_ID = pet2.element_type_id
273 AND piv4.NAME ='Third Party Payee'
274 AND prrv4.input_value_id = piv4.input_value_id
275 AND prrv4.RESULT_VALUE = p_pp_id
276 AND prr1.assignment_action_id = paa.assignment_action_id
277 AND prr1.assignment_action_id=prr2.assignment_action_id
278 AND prr1.start_date = prr2.start_date
279 AND prr1.end_date = prr2.end_date
280 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
281 AND paa.assignment_id = p_assignment_id
282 AND ppa.effective_date BETWEEN pet1.effective_start_date and pet1.effective_end_date
283 AND ppa.effective_date BETWEEN pet2.effective_start_date and pet2.effective_end_date
284 AND ppa.effective_date BETWEEN piv1.effective_start_date and piv1.effective_end_date
285 AND ppa.effective_date BETWEEN piv2.effective_start_date and piv2.effective_end_date
286 AND ppa.effective_date BETWEEN piv3.effective_start_date and piv3.effective_end_date
287 AND ppa.effective_date BETWEEN piv4.effective_start_date and piv4.effective_end_date
288 AND ppa.effective_date BETWEEN pee.effective_start_date and pee.effective_end_date;
289
290
291 /* Cursors IS 01 to 03 also included and re-written as they too use TRANSFER_ASSIGNMENT_ID for bug fix 4567621 */
292
293 CURSOR get_is_record_01_details_local IS
294 SELECT '1'
295 FROM pay_payroll_actions ppa
296 , pay_assignment_actions paa
297 , pay_element_entries_f pee1
298 , pay_element_types_f pet
299 , pay_element_entries_f pee2
300 , hr_organization_units hou /*bug fix 4551283*/
301 /* Added for Pension changes */
302 , pay_input_values_f pivf
303 , pay_element_entry_values_f peev1
304 , pay_element_entry_values_f peev2
305 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
306 AND paa.payroll_action_id = ppa.payroll_action_id
307 AND pee1.assignment_id = paa.assignment_id
308 AND pet.element_name = 'Pension'
309 AND pet.legislation_code ='DK'
310 AND pee1.entry_type ='E'
311 AND pee1.element_type_id = pet.element_type_id
312 AND pee2.assignment_id = paa.assignment_id
313 AND pee2.entry_type ='E'
314 AND pee2.element_type_id = pet.element_type_id
315 /* Added for Pension changes -start */
316 AND pivf.element_type_id = pet.element_type_id
317 AND pivf.name= 'Third Party Payee'
318 AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
319 AND peev1.input_value_id = pivf.input_value_id
320 AND peev1.element_entry_id = pee1.element_entry_id
321 AND peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
322 AND peev2.input_value_id = pivf.input_value_id
323 AND peev2.element_entry_id = pee2.element_entry_id
324 AND peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
325 /* Added for Pension changes -end */
326 AND paa.assignment_id = p_assignment_id
327 AND ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
328 AND ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
329 AND pee1.effective_start_date >= ppa.start_date
330 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
331 AND hou.organization_id = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID') /*bug fix 4551283*/
332 AND ppa.effective_date BETWEEN hou.date_from AND nvl(hou.date_to, ppa.effective_date) /*bug fix 4551283*/
333 GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id,hou.name;
334
335
336
337 CURSOR get_is_record_02_details_local IS
338 SELECT '1'
339 FROM pay_payroll_actions ppa
340 , pay_assignment_actions paa
341 , pay_element_entries_f pee1
342 , pay_element_types_f pet
343 , pay_element_entries_f pee2
344 /* Added for Pension changes */
345 , pay_input_values_f pivf
346 , pay_element_entry_values_f peev1
347 , pay_element_entry_values_f peev2
348 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
349 AND paa.payroll_action_id = ppa.payroll_action_id
350 AND pee1.assignment_id = paa.assignment_id
351 AND pet.element_name = 'Pension'
352 AND pet.legislation_code ='DK'
353 AND pee1.entry_type ='E'
354 AND pee1.element_type_id = pet.element_type_id
355 AND pee2.assignment_id = paa.assignment_id
356 AND pee2.entry_type ='E'
357 AND pee2.element_type_id = pet.element_type_id
358 /* Added for Pension changes -start */
359 AND pivf.element_type_id = pet.element_type_id
360 AND pivf.name= 'Third Party Payee'
361 AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
362 AND peev1.input_value_id = pivf.input_value_id
363 AND peev1.element_entry_id = pee1.element_entry_id
364 AND peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
365 AND peev2.input_value_id = pivf.input_value_id
366 AND peev2.element_entry_id = pee2.element_entry_id
367 AND peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
368 /* Added for Pension changes -end */
369 AND paa.assignment_id = p_assignment_id
370 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
371 AND ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
372 AND ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
373 GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id;
374
375
376 CURSOR get_is_record_03_details_local IS
377 SELECT '1'
378 FROM pay_payroll_actions ppa
379 , pay_assignment_actions paa
380 , pay_element_entries_f pee1
381 , pay_element_types_f pet
382 , pay_element_entries_f pee2
383 /* Added for Pension changes */
384 , pay_input_values_f pivf
385 , pay_element_entry_values_f peev1
386 , pay_element_entry_values_f peev2
387 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
388 AND paa.payroll_action_id = ppa.payroll_action_id
389 AND pee1.assignment_id = paa.assignment_id
390 AND pet.element_name = 'Pension'
391 AND pet.legislation_code ='DK'
392 AND pee1.entry_type ='E'
393 AND pee1.element_type_id = pet.element_type_id
394 AND pee2.assignment_id = paa.assignment_id
395 AND pee2.entry_type ='E'
396 AND pee2.element_type_id = pet.element_type_id
397 /* Added for Pension changes -start */
398 AND pivf.element_type_id = pet.element_type_id
399 AND pivf.name= 'Third Party Payee'
400 AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
401 AND peev1.input_value_id = pivf.input_value_id
402 AND peev1.element_entry_id = pee1.element_entry_id
403 AND peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
404 AND peev2.input_value_id = pivf.input_value_id
405 AND peev2.element_entry_id = pee2.element_entry_id
406 AND peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
407 /* Added for Pension changes -end */
408 AND pee2.effective_start_date < ppa.start_date
409 AND paa.assignment_id = p_assignment_id
410 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
411 AND ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
412 AND ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
413 GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id;
414
415
416
417
418 l_count NUMBER;
419 l_count_04 NUMBER;
420 l_c01 get_is_record_01_details_local%ROWTYPE;
421 l_c02 get_is_record_02_details_local%ROWTYPE;
422 l_c03 get_is_record_03_details_local%ROWTYPE;
423 l_c04 get_is_record_04_details_local%ROWTYPE;
424 l_c05 get_is_record_05_details_local%ROWTYPE;
425
426 BEGIN
427
428 l_count :=0;
429 l_count_04 :=0;
430
431 OPEN get_is_record_01_details_local;
432 LOOP
433 FETCH get_is_record_01_details_local INTO l_c01;
434 IF get_is_record_01_details_local%FOUND THEN
435 l_count := l_count +1;
436 ELSE
437 EXIT;
438 END IF;
439 END LOOP;
440 CLOSE get_is_record_01_details_local;
441
442 OPEN get_is_record_02_details_local;
443 LOOP
444 FETCH get_is_record_02_details_local INTO l_c02;
445 IF get_is_record_02_details_local%FOUND THEN
446 l_count := l_count +1;
447 ELSE
448 EXIT;
449 END IF;
450 END LOOP;
451 CLOSE get_is_record_02_details_local;
452
453 OPEN get_is_record_03_details_local;
454 LOOP
455 FETCH get_is_record_03_details_local INTO l_c03;
456 IF get_is_record_03_details_local%FOUND THEN
457 l_count := l_count +1;
458 ELSE
459 EXIT;
460 END IF;
461 END LOOP;
462 CLOSE get_is_record_03_details_local;
463
464 /* Modified for Pension Changes */
465 OPEN get_is_record_04_details_local(p_assignment_id, p_pp_id);
466 LOOP
467 FETCH get_is_record_04_details_local INTO l_c04;
468 IF get_is_record_04_details_local%FOUND THEN
469 /* Added to rectify count for OSI04 */
470 l_count_04 := l_count_04 +1;
471 ELSE
472 EXIT;
473 END IF;
474 END LOOP;
475 CLOSE get_is_record_04_details_local;
476 /* Rectified count for OSI04 */
477 l_count := l_count + CEIL(l_count_04/3);
478
479 OPEN get_is_record_05_details_local(p_person_id);
480 LOOP
481 FETCH get_is_record_05_details_local INTO l_c05;
482 IF get_is_record_05_details_local%FOUND THEN
483 l_count := l_count +1;
484 ELSE
485 EXIT;
486 END IF;
487 END LOOP;
488 CLOSE get_is_record_05_details_local;
489
490 /* For bug fix 4567621 */
491 RETURN l_count+1 ;
492
493 END get_phy_record_no;
494
495 /* Added for bug fix 4563148 */
496 FUNCTION check_numeric(p_text VARCHAR2) RETURN NUMBER IS
497 l_return NUMBER;
498 l_convert NUMBER;
499 BEGIN
500 l_return := 0;
501 l_convert := to_number(p_text);
502 RETURN l_return;
503
504 EXCEPTION
505 WHEN value_error
506 THEN l_return := 1;
507 /* Added return here */
508 RETURN l_return;
509
510
511 END check_numeric;
512
513 FUNCTION get_pension_provider(p_org_name VARCHAR2) RETURN VARCHAR2 IS
514 l_org_id NUMBER;
515
516 CURSOR get_org_id( p_org_name VARCHAR2 ) IS
517 SELECT to_char(hou.organization_id)
518 FROM hr_organization_units hou
519 WHERE hou.name = p_org_name;
520
521 BEGIN
522
523 OPEN get_org_id(p_org_name);
524 FETCH get_org_id INTO l_org_id;
525 CLOSE get_org_id;
526
527 RETURN l_org_id;
528
529 END get_pension_provider;
530
531
532 /* Function to fetch EIT details from BG for Identification Codes */
533 FUNCTION get_ident_codes(p_bg_id IN NUMBER
534 ,p_effective_date IN DATE
535 ,p_tax_rc OUT NOCOPY VARCHAR2
536 ,p_amb_rc OUT NOCOPY VARCHAR2
537 ,p_sp_rc OUT NOCOPY VARCHAR2
538 ,p_hol_days_rc OUT NOCOPY VARCHAR2) RETURN NUMBER IS
539 l_return NUMBER;
540
541 /* Cursor to fetch the Business Group Details */
542 CURSOR csr_get_bg_details(p_business_group_id NUMBER, p_effective_date DATE) IS
543 SELECT hoi2.ORG_INFORMATION2 TAX_RC
544 ,hoi2.ORG_INFORMATION3 AMB_RC
545 ,hoi2.ORG_INFORMATION4 SP_RC
546 ,hoi2.ORG_INFORMATION5 HOL_DAYS_RC
547 FROM HR_ORGANIZATION_UNITS hou
548 , HR_ORGANIZATION_INFORMATION hoi1
549 , HR_ORGANIZATION_INFORMATION hoi2
550 WHERE hou.business_group_id = p_business_group_id
551 and hoi1.organization_id = hou.organization_id
552 and hoi1.organization_id = p_business_group_id
553 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
554 and hoi1.org_information1 = 'HR_BG'
555 and hoi1.ORG_INFORMATION2 = 'Y'
556 and hoi2.ORG_INFORMATION_CONTEXT='DK_IDENTIFICATION_CODES'
557 and hoi2.organization_id = hoi1.organization_id
558 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
559
560 rec_get_bg_details csr_get_bg_details%ROWTYPE;
561
562 BEGIN
563
564 l_return :=1;
565
566 OPEN csr_get_bg_details(p_bg_id, fnd_date.displaydt_to_date(p_effective_date));
567 FETCH csr_get_bg_details INTO rec_get_bg_details;
568 CLOSE csr_get_bg_details;
569
570 p_tax_rc := rec_get_bg_details.tax_rc;
571 p_amb_rc := rec_get_bg_details.amb_rc;
572 p_sp_rc := rec_get_bg_details.sp_rc;
573 p_hol_days_rc := rec_get_bg_details.hol_days_rc;
574
575 RETURN l_return;
576
577 END get_ident_codes;
578
579 /* Added to support multiple pensions for OSI02 for bug fix 5563150*/
580 FUNCTION get_pen_values(p_eff_date DATE,p_ele_type_id NUMBER, p_ee_id NUMBER, p_iv_name VARCHAR2) RETURN VARCHAR2 IS
581
582 CURSOR csr_get_pen_values(p_eff_date DATE, p_ee_id NUMBER, p_iv_id NUMBER) IS
583 SELECT nvl(screen_entry_value,0)
584 FROM pay_element_entry_values_f
585 WHERE element_entry_id = p_ee_id
586 AND input_value_id = p_iv_id
587 AND p_eff_date BETWEEN effective_start_date and effective_end_date ;
588
589 CURSOR csr_get_iv_id(p_ele_type_id NUMBER,p_iv_name VARCHAR2, p_eff_date DATE) IS
590 SELECT input_value_id
591 FROM pay_input_values_f
592 WHERE name = p_iv_name
593 AND element_type_id = p_ele_type_id
594 AND legislation_code ='DK'
595 AND p_eff_date BETWEEN effective_start_date and effective_end_date ;
596
597 l_iv_id NUMBER;
598 l_result_value VARCHAR2(80);
599
600 BEGIN
601 OPEN csr_get_iv_id(p_ele_type_id,p_iv_name,p_eff_date);
602 FETCH csr_get_iv_id INTO l_iv_id;
603 CLOSE csr_get_iv_id;
604
605 IF l_iv_id IS NOT NULL THEN
606 OPEN csr_get_pen_values(p_eff_date,p_ee_id,l_iv_id);
607 FETCH csr_get_pen_values INTO l_result_value;
608 CLOSE csr_get_pen_values;
609 END IF;
610
611 RETURN l_result_value;
612
613 END get_pen_values;
614
615
616 /* Added to support override for Use of Holiday Card for transfer to Holiday Bank for bug fix 5533140*/
617 FUNCTION get_use_hol_card(p_payroll_action_id NUMBER,p_date_earned DATE ) RETURN VARCHAR2
618 IS
619
620 l_value PER_TIME_PERIODS.PRD_INFORMATION2%TYPE;
621 l_payroll_id NUMBER;
622
623 /* Modified the cursor for bug 5533140*/
624
625 CURSOR get_value_from_ddf(p_payroll_id NUMBER , p_date_earned DATE) IS
626 SELECT PRL_INFORMATION1
627 FROM pay_payrolls_f ppf
628 WHERE PAYROLL_ID = p_payroll_id
629 AND p_date_earned BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE;
630
631 CURSOR get_payroll_id (p_payroll_action_id NUMBER ) IS
632 SELECT PAYROLL_ID
633 FROM PAY_PAYROLL_ACTIONS ppa
634 WHERE payroll_action_id = p_payroll_action_id;
635
636
637 BEGIN
638
639 OPEN get_payroll_id(p_payroll_action_id);
640 FETCH get_payroll_id INTO l_payroll_id;
641 CLOSE get_payroll_id;
642
643 OPEN get_value_from_ddf(l_payroll_id, p_date_earned);
644 FETCH get_value_from_ddf INTO l_value;
645 CLOSE get_value_from_ddf;
646
647 RETURN l_value;
648
649 END get_use_hol_card;
650
651 FUNCTION get_pay_period_per_year(p_payroll_action_id NUMBER,p_date_earned DATE ) RETURN NUMBER
652 IS
653 Cursor csr_pay_period (p_payroll_action_id NUMBER , p_date_earned DATE) is
654 Select
655 TPTYPE.number_per_fiscal_year
656 from
657 pay_payroll_actions PACTION
658 , per_time_periods TPERIOD
659 , per_time_period_types TPTYPE
660 where PACTION.payroll_action_id = p_payroll_action_id
661 and TPERIOD.payroll_id = PACTION.payroll_id
662 and p_date_earned between TPERIOD.start_date and TPERIOD.end_date
663 and TPTYPE.period_type = TPERIOD.period_type;
664
665 l_period_per_year NUMBER;
666 BEGIN
667 OPEN csr_pay_period(p_payroll_action_id, p_date_earned);
668 FETCH csr_pay_period INTO l_period_per_year;
669 CLOSE csr_pay_period;
670
671 Return l_period_per_year;
672
673 END get_pay_period_per_year;
674
675 END PAY_DK_PAYMENT_PROCESS_PKG;
[Home] [Help]