[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_PAYMENT_PROCESS_PKG
Source
1 PACKAGE BODY PAY_DK_PAYMENT_PROCESS_PKG as
2 /* $Header: pydkpaypr.pkb 120.27.12020000.2 2013/03/15 05:53:21 rsahai ship $ */
3
4 --Global parameters
5 g_package CONSTANT varchar2(33) := 'PAY_DK_PAYMENT_PROCESS_PKG.';
6
7 --12759416
8 FUNCTION DK_PBS_GET_ATP_TAX_INFO(
9 p_payroll_action_id IN NUMBER,
10 p_transfer_info_type IN VARCHAR2,
11 p_transfer_total_amount IN VARCHAR2,
12 p_transfer_info_disposal_date IN VARCHAR2,
13 p_transfer_payer_reg_no IN VARCHAR2,
14 p_transfer_payer_acct_no IN VARCHAR2,
15 p_transfer_ds_cvr_no IN VARCHAR2,
16 p_transfer_payer_cvr_no IN VARCHAR2,
17 p_transfer_pbs_no IN VARCHAR2,
18 p_transfer_pmnt_start_date IN VARCHAR2,
19 p_transfer_pmnt_end_date IN VARCHAR2,
20 p_action_information_category IN VARCHAR2
21 )RETURN VARCHAR2 IS
22
23 l_payroll_id Number;
24 l_year VARCHAR2(10);
25 l_effective_date date;
26 l_transfer_overforsels_type VARCHAR2(100) := '60';
27 l_QTR_START VARCHAR2(10);
28 l_QTR_END VARCHAR2(10);
29
30 CURSOR C_Payroll_id IS
31 Select payroll_id, effective_date
32 From Pay_Payroll_Actions
33 Where Payroll_Action_Id = p_payroll_action_id;
34
35 Cursor c_atp_tax_info(p_payroll_id NUMBER) IS
36 SELECT NVL(MAX(action_information2) + 1,'60')
37 FROM PAY_ACTION_INFORMATION
38 WHERE action_information_category = p_action_information_category
39 AND action_information1 = p_payroll_id
40 AND action_information3 = p_transfer_info_type
41 AND action_information4 = p_transfer_total_amount
42 AND action_information5 = p_transfer_info_disposal_date
43 AND action_information9 = p_transfer_payer_cvr_no
44 AND to_date(action_information11,'RRRRMMDD') BETWEEN TO_DATE(l_QTR_START,'RRRRMMDD') AND TO_DATE(l_QTR_END,'RRRRMMDD');
45
46 BEGIN
47
48 OPEN C_Payroll_id;
49 FETCH C_Payroll_id INTO l_payroll_id, l_effective_date;
50 CLOSE C_Payroll_id;
51
52 l_year := to_char(l_effective_date,'RRRR');
53
54 IF to_char(l_effective_date,'MM') IN ('01','02','03') THEN
55 l_QTR_START := l_year||'0101';
56 l_QTR_END := l_year||'0331';
57 ELSIF to_char(l_effective_date,'MM') IN ('04','05','06') THEN
58 l_QTR_START := l_year||'0401';
59 l_QTR_END := l_year||'0630';
60 ELSIF to_char(l_effective_date,'MM') IN ('07','08','09') THEN
61 l_QTR_START := l_year||'0701';
62 l_QTR_END := l_year||'0930';
63 ELSE
64 l_QTR_START := l_year||'1001';
65 l_QTR_END := l_year||'1231';
66 END IF;
67
68 OPEN c_atp_tax_info(l_payroll_id);
69 FETCH c_atp_tax_info INTO l_transfer_overforsels_type;
70 CLOSE c_atp_tax_info;
71
72
73 Return l_transfer_overforsels_type;
74
75 END DK_PBS_GET_ATP_TAX_INFO;
76 --12759416
77
78 --12759416
79 FUNCTION DK_PBS_ARCH_ATP_TAX_INFO(
80 p_payroll_action_id IN NUMBER,
81 p_transfer_overforsels_type IN VARCHAR2,
82 p_transfer_info_type IN VARCHAR2,
83 p_transfer_total_amount IN VARCHAR2,
84 p_transfer_info_disposal_date IN VARCHAR2,
85 p_transfer_payer_reg_no IN VARCHAR2,
86 p_transfer_payer_acct_no IN VARCHAR2,
87 p_transfer_ds_cvr_no IN VARCHAR2,
88 p_transfer_payer_cvr_no IN VARCHAR2,
89 p_transfer_pbs_no IN VARCHAR2,
90 p_transfer_pmnt_start_date IN VARCHAR2,
91 p_transfer_pmnt_end_date IN VARCHAR2,
92 p_action_information_category IN VARCHAR2
93 )RETURN NUMBER IS
94
95 l_action_info_id Number;
96 l_ovn Number;
97 l_payroll_id Number;
98
99
100 CURSOR C_Payroll_id IS
101 Select payroll_id
102 From Pay_Payroll_Actions
103 Where Payroll_Action_Id = p_payroll_action_id;
104
105 BEGIN
106
107 OPEN C_Payroll_id;
108 FETCH C_Payroll_id INTO l_payroll_id;
109 CLOSE C_Payroll_id;
110
111 pay_action_information_api.create_action_information
112 (
113 p_action_information_id=> l_action_info_id,
114 p_action_context_id=> p_payroll_action_id,
115 p_action_context_type=> 'PA',
116 p_object_version_number=> l_ovn,
117 p_action_information_category=> p_action_information_category,
118 p_action_information1=> l_payroll_id,
119 p_action_information2=> p_transfer_overforsels_type,
120 p_action_information3=> p_transfer_info_type,
121 p_action_information4=> p_transfer_total_amount,
122 p_action_information5=> p_transfer_info_disposal_date,
123 p_action_information6=> p_transfer_payer_reg_no,
124 p_action_information7=> p_transfer_payer_acct_no,
125 p_action_information8=> p_transfer_ds_cvr_no,
126 p_action_information9=> p_transfer_payer_cvr_no,
127 p_action_information10=>p_transfer_pbs_no,
128 p_action_information11=>p_transfer_pmnt_start_date,
129 p_action_information12=>p_transfer_pmnt_end_date
130 );
131
132 Return 0;
133
134 Exception WHEN OTHERS THEN
135 Return 1;
136
137 END DK_PBS_ARCH_ATP_TAX_INFO;
138 --12759416
139
140
141
142 /* Added for bug fix 8501177 */
143 FUNCTION get_Assignment_Action (
144 p_assignment_id NUMBER
145 )
146 RETURN NUMBER
147 AS
148 CURSOR csr_asg_act_id
149 IS
150 SELECT max(paa.ASSIGNMENT_ACTION_ID)
151 FROM pay_payroll_actions ppa
152 , pay_assignment_actions paa
153 WHERE paa.assignment_id = p_assignment_id
154 AND paa.payroll_action_id = ppa.payroll_action_id
155 AND paa.action_status IN ('C','S') -- 10229494
156 AND ppa.action_type IN('R','Q','I','B','V','P','U');
157
158 l_asg_action_id pay_assignment_actions.ASSIGNMENT_ACTION_ID%TYPE;
159 BEGIN
160 OPEN csr_asg_act_id;
161 FETCH csr_asg_act_id INTO l_asg_action_id;
162 CLOSE csr_asg_act_id;
163
164 RETURN l_asg_action_id;
165
166 END get_Assignment_Action;
167
168
169 FUNCTION get_defined_balance_id (
170 p_dimension_name VARCHAR2,
171 p_balance_name VARCHAR2
172 )
173 RETURN NUMBER
174 AS
175 CURSOR csr_defined_balance_id
176 IS
177 SELECT pdb.defined_balance_id
178 FROM pay_balance_dimensions pbd,
179 pay_balance_types pbt,
180 pay_defined_balances pdb
181 WHERE pbd.dimension_name = p_dimension_name
182 AND pbd.business_group_id IS NULL
183 AND pbd.legislation_code = 'DK'
184 AND pbt.balance_name = p_balance_name
185 AND pbt.business_group_id IS NULL
186 AND pbt.legislation_code = 'DK'
187 AND pdb.balance_type_id = pbt.balance_type_id
188 AND pdb.balance_dimension_id = pbd.balance_dimension_id
189 AND pdb.business_group_id IS NULL
190 AND pdb.legislation_code = 'DK';
191
192 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
193 BEGIN
194 OPEN csr_defined_balance_id;
195 FETCH csr_defined_balance_id INTO l_defined_balance_id;
196 CLOSE csr_defined_balance_id;
197 RETURN l_defined_balance_id;
198 END get_defined_balance_id;
199 /* Added for bug fix 8501177 */
200
201 -----------------------------------------------------------------------------
202 -- GET_PARAMETER used in SQL to decode legislative parameters
203 -----------------------------------------------------------------------------
204 FUNCTION get_parameter(
205 p_parameter_string IN VARCHAR2
206 ,p_token IN VARCHAR2
207 ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2
208 IS
209 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
210 l_start_pos NUMBER;
211 l_delimiter varchar2(1);
212 l_proc VARCHAR2(60);
213 BEGIN
214 l_delimiter :=' ';
215 l_proc := g_package||' get parameter ';
216
217 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
218 IF l_start_pos = 0 THEN
219 l_delimiter := '|';
220 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
221 end if;
222 IF l_start_pos <> 0 THEN
223 l_start_pos := l_start_pos + length(p_token||'=');
224 l_parameter := substr(p_parameter_string,
225 l_start_pos,
226 instr(p_parameter_string||' ',
227 ',',l_start_pos)
228 - l_start_pos);
229 IF p_segment_number IS NOT NULL THEN
230 l_parameter := ':'||l_parameter||':';
231 l_parameter := substr(l_parameter,
232 instr(l_parameter,':',1,p_segment_number)+1,
233 instr(l_parameter,':',1,p_segment_number+1) -1
234 - instr(l_parameter,':',1,p_segment_number));
235 END IF;
236 END IF;
237 RETURN l_parameter;
238 END get_parameter;
239
240 --12660243
241 -----------------------------------------------------------------------------
242 -- GET_PARAMETERS used in SQL to decode legislative parameters
243 -----------------------------------------------------------------------------
244 FUNCTION get_parameters(p_payroll_action_id NUMBER,
245 p_token IN VARCHAR2) RETURN VARCHAR2
246 IS
247 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
248 l_start_pos NUMBER;
249 l_delimiter varchar2(1);
250 l_proc VARCHAR2(60);
251
252 p_parameter_string pay_payroll_actions.legislative_parameters%TYPE;
253 p_segment_number NUMBER;
254
255 CURSOR C_leg_string
256 IS
257 SELECT legislative_parameters
258 FROM PAY_PAYROLL_ACTIONS
259 WHERE payroll_action_id = p_payroll_action_id;
260
261 BEGIN
262 l_delimiter :=' ';
263 l_proc := g_package||' get parameters ';
264
265 OPEN C_leg_string;
266 FETCH C_leg_string INTO p_parameter_string;
267 CLOSE C_leg_string;
268
269 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
270 IF l_start_pos = 0 THEN
271 l_delimiter := '|';
272 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
273 end if;
274 IF l_start_pos <> 0 THEN
275 l_start_pos := l_start_pos + length(p_token||'=');
276 l_parameter := substr(p_parameter_string,
277 l_start_pos,
278 instr(p_parameter_string||' ',
279 ',',l_start_pos)
280 - l_start_pos);
281 IF p_segment_number IS NOT NULL THEN
282 l_parameter := ':'||l_parameter||':';
283 l_parameter := substr(l_parameter,
284 instr(l_parameter,':',1,p_segment_number)+1,
285 instr(l_parameter,':',1,p_segment_number+1) -1
286 - instr(l_parameter,':',1,p_segment_number));
287 END IF;
288 END IF;
289
290 IF p_token in ('TAX_TRNF_900_ID', 'ATP_TRNF_800_ID')
291 THEN
292 l_parameter := NVL(l_parameter,60);
293 END IF;
294
295 RETURN l_parameter;
296 END get_parameters;
297 --12660243
298
299 -----------------------------------------------------------------------------
300 -- GET_LOOKUP_MEANING function used to get labels of items from a lookup
301 -----------------------------------------------------------------------------
302 FUNCTION get_lookup_meaning (p_lookup_type varchar2,p_lookup_code varchar2) RETURN VARCHAR2 IS
303 CURSOR csr_lookup IS
304 select meaning
305 from hr_lookups
306 where lookup_type = p_lookup_type
307 and lookup_code = p_lookup_code
308 and enabled_flag ='Y';
309 l_meaning hr_lookups.meaning%type;
310 BEGIN
311 OPEN csr_lookup;
312 FETCH csr_lookup INTO l_Meaning;
313 CLOSE csr_lookup;
314 RETURN l_meaning;
315 END get_lookup_meaning;
316
317 /* Added the following for Third Party Payments */
318
319 FUNCTION get_ass_action_context(p_assignment_id NUMBER) RETURN NUMBER IS
320 l_context NUMBER;
321
322 CURSOR get_context(p_assignment_id NUMBER) IS
323 SELECT max(paa.ASSIGNMENT_ACTION_ID)
324 FROM pay_payroll_actions ppa
325 , pay_assignment_actions paa
326 WHERE paa.assignment_id = p_assignment_id
327 AND paa.payroll_action_id = ppa.payroll_action_id
328 AND ppa.action_type IN('P','U');
329
330 BEGIN
331
332 OPEN get_context(p_assignment_id);
333 FETCH get_context INTO l_context;
334 CLOSE get_context;
335
336 RETURN l_context;
337
338 END get_ass_action_context;
339 --
340 --
341 FUNCTION get_date_earned_context(p_assignment_id NUMBER) RETURN DATE IS
342 l_context DATE;
343 /* Added nvl for bug 5879516 */
344 CURSOR get_context(p_assignment_id NUMBER) IS
345 SELECT max(ppa.DATE_EARNED)
346 FROM pay_payroll_actions ppa
347 , pay_assignment_actions paa
348 WHERE paa.assignment_id = p_assignment_id
349 AND paa.payroll_action_id = ppa.payroll_action_id
350 AND ppa.action_type IN('P','U');
351
352 /*Added for bug 5930673 */
353 Cursor get_alternate_date_earned (p_assignment_id NUMBER) IS
354 SELECT max(nvl(ppa.date_earned,ppar.date_earned))
355 FROM pay_payroll_actions ppa
356 , pay_assignment_actions paa
357 , pay_action_interlocks pail
358 , pay_payroll_actions ppar
359 , pay_assignment_actions paar
360 WHERE paa.assignment_id = p_assignment_id
361 AND paa.payroll_action_id = ppa.payroll_action_id
362 AND paar.assignment_action_id = pail.locked_action_id
363 AND pail.locking_action_id = paa.assignment_action_id
364 AND paar.payroll_action_id = ppar.payroll_action_id
365 AND ppa.action_type IN('P','U')
366 AND ppar.action_type IN('Q','R');
367
368 BEGIN
369
370 OPEN get_context(p_assignment_id);
371 FETCH get_context INTO l_context;
372 CLOSE get_context;
373 /*Added for bug 5930673 */
374 If l_context is null then
375 OPEN get_alternate_date_earned(p_assignment_id);
376 FETCH get_alternate_date_earned INTO l_context;
377 CLOSE get_alternate_date_earned;
378 End if;
379
380 RETURN l_context;
381
382 END get_date_earned_context;
383 --
384 --
385 /* Added p_org_id to function and modified dimension from _PAYMENTS to _PP_PAYMENTS for pension changes.
386 Also changed call to pay_balance_pkg.get_value */
387 FUNCTION get_prev_bal_paid(p_assignment_id NUMBER,p_org_id NUMBER, p_balance_name VARCHAR2) RETURN NUMBER IS
388 l_context1 NUMBER;
389 l_context2 NUMBER;
390 l_value NUMBER;
391
392 CURSOR get_ass_action_id(p_assignment_id NUMBER) IS
393 SELECT ppp.assignment_action_id
394 FROM pay_payroll_actions ppa
395 , pay_assignment_actions paa
396 , pay_action_interlocks pai
397 , pay_pre_payments ppp
398 WHERE paa.assignment_id = p_assignment_id
399 AND paa.payroll_action_id = ppa.payroll_action_id
400 AND ppa.action_type = 'M'
401 AND ppa.action_status = 'C'
402 AND paa.action_status = 'C'
403 AND paa.pre_payment_id = ppp.pre_payment_id
404 AND pai.locking_action_id = paa.assignment_action_id
405 AND pai.locked_action_id = ppp.assignment_action_id;
406
407
408 /* Modified for pension changes */
409 CURSOR get_def_bal_id(p_balance_name VARCHAR2) IS
410 SELECT pdb.defined_balance_id
411 FROM pay_defined_balances pdb
412 ,pay_balance_types pbt
413 ,pay_balance_dimensions pbd
414 WHERE pbt.legislation_code='DK'
415 AND pbt.balance_name = p_balance_name
416 AND pbd.legislation_code = 'DK'
417 --AND pbd.database_item_suffix = '_PAYMENTS'
418 AND pbd.database_item_suffix = '_PP_PAYMENTS'
419 AND pdb.balance_type_id = pbt.balance_type_id
420 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
421
422
423 BEGIN
424
425 OPEN get_ass_action_id(p_assignment_id);
426 FETCH get_ass_action_id INTO l_context1;
427 CLOSE get_ass_action_id;
428
429 OPEN get_def_bal_id(p_balance_name);
430 FETCH get_def_bal_id INTO l_context2;
431 CLOSE get_def_bal_id;
432
433 --l_value := pay_balance_pkg.get_value(l_context2,l_context1);
434 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);
435
436 RETURN l_value;
437
438 END get_prev_bal_paid;
439 --
440 --
441 /* FUNCTION get_prev_date_earned_context(p_assignment_id NUMBER) RETURN DATE IS
442 l_context DATE;
443
444 CURSOR get_context(p_assignment_id NUMBER) IS
445 SELECT ppa.date_earned
446 FROM pay_payroll_actions ppa
447 , pay_assignment_actions paa
448 , pay_action_interlocks pai
449 WHERE paa.assignment_id = p_assignment_id
450 AND paa.payroll_action_id = ppa.payroll_action_id
451 AND ppa.action_type = 'M'
452 AND pai.locking_action_id = paa.assignment_action_id
453 AND pai.locked_action_id = get_ass_action_context(p_assignment_id);
454
455 BEGIN
456
457 OPEN get_context(p_assignment_id);
458 FETCH get_context INTO l_context;
459 CLOSE get_context;
460
461 RETURN l_context;
462
463 END get_prev_date_earned_context; */
464 --
465 --
466 FUNCTION get_phy_record_no(p_person_id NUMBER, p_assignment_id NUMBER, p_pp_id VARCHAR2) RETURN NUMBER IS
467
468 /* Cursor for Record I 05 and I 04 re-written as parameters TRANSFER_PERSON_ID and TRANSFER_ASSIGNMENT_ID
469 not available in memory for the first fetch */
470
471 CURSOR get_is_record_05_details_local(p_person_id NUMBER) IS
472 SELECT '1'
473 FROM per_addresses pad
474 /* Modified for bug fix 4593682 */
475 , per_all_people_f pap
476 , pay_payroll_actions ppa
477 WHERE /*pad.person_id = p_person_id*/
478 pad.person_id (+)= pap.person_id
479 AND pad.primary_flag = 'Y' --9403004
480 AND ppa.effective_date BETWEEN nvl(pad.date_from,ppa.effective_date) AND nvl(pad.date_to,to_date('31-12-4712','dd-mm-rrrr')) --9403004
481 AND pap.person_id = p_person_id
482 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
483 /* Modified for bug fix 7664874 */
484 AND ppa.payroll_action_id=pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
485 AND ppa.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
486
487
488 /* Modified for Pension changes to restrict on Pension Provider*/
489 CURSOR get_is_record_04_details_local(p_assignment_id NUMBER,p_pp_id VARCHAR2) IS
490 SELECT '1'
491 FROM pay_run_results prr1
492 , pay_run_result_values prrv1
493 , pay_run_result_values prrv3
494 , pay_element_types_f pet1
495 , pay_input_values_f piv1
496 , pay_input_values_f piv3
497 , pay_run_results prr2
498 , pay_run_result_values prrv2
499 , pay_run_result_values prrv4
500 , pay_element_types_f pet2
501 , pay_input_values_f piv2
502 , pay_input_values_f piv4
503 , pay_assignment_actions paa
504 , pay_payroll_actions ppa
505 , pay_element_entries_f pee
506 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
507 AND prr1.ELEMENT_TYPE_ID = pet1.ELEMENT_TYPE_ID
508 AND prrv1.RUN_RESULT_ID = prr1.RUN_RESULT_ID
509 AND prrv3.RUN_RESULT_ID = prr1.RUN_RESULT_ID
510 AND pee.ELEMENT_ENTRY_ID = prr1.ELEMENT_ENTRY_ID
511 AND pet1.element_name = 'Retro Pension'
512 AND pet1.legislation_code ='DK'
513 AND piv1.ELEMENT_TYPE_ID = pet1.element_type_id
514 AND piv1.NAME ='Pay Value'
515 AND prrv1.input_value_id = piv1.input_value_id
516 AND piv3.ELEMENT_TYPE_ID = pet1.element_type_id
517 AND piv3.NAME ='Third Party Payee'
518 AND prrv3.input_value_id = piv3.input_value_id
519 AND prrv3.RESULT_VALUE = p_pp_id
520 AND prr2.ELEMENT_TYPE_ID =pet2.ELEMENT_TYPE_ID
521 AND prrv2.RUN_RESULT_ID = prr2.RUN_RESULT_ID
522 AND prrv4.RUN_RESULT_ID = prr2.RUN_RESULT_ID
523 AND prrv4.RESULT_VALUE = prrv3.RESULT_VALUE
524 AND pet2.element_name = 'Retro Employer Pension'
525 AND pet2.legislation_code ='DK'
526 AND piv2.ELEMENT_TYPE_ID = pet2.element_type_id
527 AND piv2.NAME ='Pay Value'
528 AND prrv2.input_value_id = piv2.input_value_id
529 AND piv4.ELEMENT_TYPE_ID = pet2.element_type_id
530 AND piv4.NAME ='Third Party Payee'
531 AND prrv4.input_value_id = piv4.input_value_id
532 AND prrv4.RESULT_VALUE = p_pp_id
533 AND prr1.assignment_action_id = paa.assignment_action_id
534 AND prr1.assignment_action_id=prr2.assignment_action_id
535 AND prr1.start_date = prr2.start_date
536 AND prr1.end_date = prr2.end_date
537 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
538 AND paa.assignment_id = p_assignment_id
539 AND ppa.effective_date BETWEEN pet1.effective_start_date and pet1.effective_end_date
540 AND ppa.effective_date BETWEEN pet2.effective_start_date and pet2.effective_end_date
541 AND ppa.effective_date BETWEEN piv1.effective_start_date and piv1.effective_end_date
542 AND ppa.effective_date BETWEEN piv2.effective_start_date and piv2.effective_end_date
543 AND ppa.effective_date BETWEEN piv3.effective_start_date and piv3.effective_end_date
544 AND ppa.effective_date BETWEEN piv4.effective_start_date and piv4.effective_end_date
545 AND ppa.effective_date BETWEEN pee.effective_start_date and pee.effective_end_date;
546
547
548 /* Cursors IS 01 to 03 also included and re-written as they too use TRANSFER_ASSIGNMENT_ID for bug fix 4567621 */
549
550 CURSOR get_is_record_01_details_local IS
551 SELECT '1'
552 FROM pay_payroll_actions ppa
553 , pay_assignment_actions paa
554 , pay_element_entries_f pee1
555 , pay_element_types_f pet
556 , pay_element_entries_f pee2
557 , hr_organization_units hou /*bug fix 4551283*/
558 /* Added for Pension changes */
559 , pay_input_values_f pivf
560 , pay_element_entry_values_f peev1
561 , pay_element_entry_values_f peev2
562 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
563 AND paa.payroll_action_id = ppa.payroll_action_id
564 AND pee1.assignment_id = paa.assignment_id
565 AND pet.element_name = 'Pension'
566 AND pet.legislation_code ='DK'
567 AND pee1.entry_type ='E'
568 AND pee1.element_type_id = pet.element_type_id
569 AND pee2.assignment_id = paa.assignment_id
570 AND pee2.entry_type ='E'
571 AND pee2.element_type_id = pet.element_type_id
572 /* Added for Pension changes -start */
573 AND pivf.element_type_id = pet.element_type_id
574 AND pivf.name= 'Third Party Payee'
575 AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
576 AND peev1.input_value_id = pivf.input_value_id
577 AND peev1.element_entry_id = pee1.element_entry_id
578 AND peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
579 AND peev2.input_value_id = pivf.input_value_id
580 AND peev2.element_entry_id = pee2.element_entry_id
581 AND peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
582 /* Added for Pension changes -end */
583 AND paa.assignment_id = p_assignment_id
584 AND ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
585 AND ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
586 AND pee1.effective_start_date >= ppa.start_date
587 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
588 AND hou.organization_id = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID') /*bug fix 4551283*/
589 AND ppa.effective_date BETWEEN hou.date_from AND nvl(hou.date_to, ppa.effective_date) /*bug fix 4551283*/
590 GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id,hou.name;
591
592
593
594 CURSOR get_is_record_02_details_local IS
595 SELECT '1'
596 FROM pay_payroll_actions ppa
597 , pay_assignment_actions paa
598 , pay_element_entries_f pee1
599 , pay_element_types_f pet
600 , pay_element_entries_f pee2
601 /* Added for Pension changes */
602 , pay_input_values_f pivf
603 , pay_element_entry_values_f peev1
604 , pay_element_entry_values_f peev2
605 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
606 AND paa.payroll_action_id = ppa.payroll_action_id
607 AND pee1.assignment_id = paa.assignment_id
608 AND pet.element_name = 'Pension'
609 AND pet.legislation_code ='DK'
610 AND pee1.entry_type ='E'
611 AND pee1.element_type_id = pet.element_type_id
612 AND pee2.assignment_id = paa.assignment_id
613 AND pee2.entry_type ='E'
614 AND pee2.element_type_id = pet.element_type_id
615 /* Added for Pension changes -start */
616 AND pivf.element_type_id = pet.element_type_id
617 AND pivf.name= 'Third Party Payee'
618 AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
619 AND peev1.input_value_id = pivf.input_value_id
620 AND peev1.element_entry_id = pee1.element_entry_id
621 AND peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
622 AND peev2.input_value_id = pivf.input_value_id
623 AND peev2.element_entry_id = pee2.element_entry_id
624 AND peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
625 /* Added for Pension changes -end */
626 AND paa.assignment_id = p_assignment_id
627 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
628 AND ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
629 AND ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
630 AND 1=2
631 GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id;
632
633
634 CURSOR get_is_record_03_details_local IS
635 SELECT '1'
636 FROM pay_payroll_actions ppa
637 , pay_assignment_actions paa
638 , pay_element_entries_f pee1
639 , pay_element_types_f pet
640 , pay_element_entries_f pee2
641 /* Added for Pension changes */
642 , pay_input_values_f pivf
643 , pay_element_entry_values_f peev1
644 , pay_element_entry_values_f peev2
645 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
646 AND paa.payroll_action_id = ppa.payroll_action_id
647 AND pee1.assignment_id = paa.assignment_id
648 AND pet.element_name = 'Pension'
649 AND pet.legislation_code ='DK'
650 AND pee1.entry_type ='E'
651 AND pee1.element_type_id = pet.element_type_id
652 AND pee2.assignment_id = paa.assignment_id
653 AND pee2.entry_type ='E'
654 AND pee2.element_type_id = pet.element_type_id
655 /* Added for Pension changes -start */
656 AND pivf.element_type_id = pet.element_type_id
657 AND pivf.name= 'Third Party Payee'
658 AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
659 AND peev1.input_value_id = pivf.input_value_id
660 AND peev1.element_entry_id = pee1.element_entry_id
661 AND peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
662 AND peev2.input_value_id = pivf.input_value_id
663 AND peev2.element_entry_id = pee2.element_entry_id
664 AND peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
665 /* Added for Pension changes -end */
666 AND pee2.effective_start_date < ppa.start_date
667 AND paa.assignment_id = p_assignment_id
668 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
669 AND ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
670 AND ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
671 AND 1=2
672 GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id;
673
674
675
676
677 l_count NUMBER;
678 l_count_04 NUMBER;
679 l_c01 get_is_record_01_details_local%ROWTYPE;
680 l_c02 get_is_record_02_details_local%ROWTYPE;
681 l_c03 get_is_record_03_details_local%ROWTYPE;
682 l_c04 get_is_record_04_details_local%ROWTYPE;
683 l_c05 get_is_record_05_details_local%ROWTYPE;
684
685 BEGIN
686
687 l_count :=0;
688 l_count_04 :=0;
689
690 OPEN get_is_record_01_details_local;
691 LOOP
692 FETCH get_is_record_01_details_local INTO l_c01;
693 IF get_is_record_01_details_local%FOUND THEN
694 l_count := l_count +1;
695 ELSE
696 EXIT;
697 END IF;
698 END LOOP;
699 CLOSE get_is_record_01_details_local;
700
701 OPEN get_is_record_02_details_local;
702 LOOP
703 FETCH get_is_record_02_details_local INTO l_c02;
704 IF get_is_record_02_details_local%FOUND THEN
705 l_count := l_count +1;
706 ELSE
707 EXIT;
708 END IF;
709 END LOOP;
710 CLOSE get_is_record_02_details_local;
711
712 OPEN get_is_record_03_details_local;
713 LOOP
714 FETCH get_is_record_03_details_local INTO l_c03;
715 IF get_is_record_03_details_local%FOUND THEN
716 l_count := l_count +1;
717 ELSE
718 EXIT;
719 END IF;
720 END LOOP;
721 CLOSE get_is_record_03_details_local;
722
723 /* Modified for Pension Changes */
724 OPEN get_is_record_04_details_local(p_assignment_id, p_pp_id);
725 LOOP
726 FETCH get_is_record_04_details_local INTO l_c04;
727 IF get_is_record_04_details_local%FOUND THEN
728 /* Added to rectify count for OSI04 */
729 l_count_04 := l_count_04 +1;
730 ELSE
731 EXIT;
732 END IF;
733 END LOOP;
734 CLOSE get_is_record_04_details_local;
735 /* Rectified count for OSI04 */
736 l_count := l_count + CEIL(l_count_04/3);
737
738 OPEN get_is_record_05_details_local(p_person_id);
739 LOOP
740 FETCH get_is_record_05_details_local INTO l_c05;
741 IF get_is_record_05_details_local%FOUND THEN
742 l_count := l_count +1;
743 ELSE
744 EXIT;
745 END IF;
746 END LOOP;
747 CLOSE get_is_record_05_details_local;
748
749 /* For bug fix 4567621 */
750 RETURN l_count+1 ;
751
752 END get_phy_record_no;
753
754 /* Added for bug fix 4563148 */
755 FUNCTION check_numeric(p_text VARCHAR2) RETURN NUMBER IS
756 l_return NUMBER;
757 l_convert NUMBER;
758 BEGIN
759 l_return := 0;
760 l_convert := to_number(p_text);
761 RETURN l_return;
762
763 EXCEPTION
764 WHEN value_error
765 THEN l_return := 1;
766 /* Added return here */
767 RETURN l_return;
768
769
770 END check_numeric;
771
772 FUNCTION get_pension_provider(p_org_name VARCHAR2) RETURN VARCHAR2 IS
773 l_org_id NUMBER;
774
775 CURSOR get_org_id( p_org_name VARCHAR2 ) IS
776 SELECT to_char(hou.organization_id)
777 FROM hr_organization_units hou
778 WHERE hou.name = p_org_name;
779
780 BEGIN
781
782 OPEN get_org_id(p_org_name);
783 FETCH get_org_id INTO l_org_id;
784 CLOSE get_org_id;
785
786 RETURN l_org_id;
787
788 END get_pension_provider;
789
790
791 /* Function to fetch EIT details from BG for Identification Codes */
792 FUNCTION get_ident_codes(p_bg_id IN NUMBER
793 ,p_effective_date IN DATE
794 ,p_tax_rc OUT NOCOPY VARCHAR2
795 ,p_amb_rc OUT NOCOPY VARCHAR2
796 ,p_sp_rc OUT NOCOPY VARCHAR2
797 ,p_hol_days_rc OUT NOCOPY VARCHAR2) RETURN NUMBER IS
798 l_return NUMBER;
799
800 /* Cursor to fetch the Business Group Details */
801 CURSOR csr_get_bg_details(p_business_group_id NUMBER, p_effective_date DATE) IS
802 SELECT hoi2.ORG_INFORMATION2 TAX_RC
803 ,hoi2.ORG_INFORMATION3 AMB_RC
804 ,hoi2.ORG_INFORMATION4 SP_RC
805 ,hoi2.ORG_INFORMATION5 HOL_DAYS_RC
806 FROM HR_ORGANIZATION_UNITS hou
807 , HR_ORGANIZATION_INFORMATION hoi1
808 , HR_ORGANIZATION_INFORMATION hoi2
809 WHERE hou.business_group_id = p_business_group_id
810 and hoi1.organization_id = hou.organization_id
811 and hoi1.organization_id = p_business_group_id
812 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
813 and hoi1.org_information1 = 'HR_BG'
814 and hoi1.ORG_INFORMATION2 = 'Y'
815 and hoi2.ORG_INFORMATION_CONTEXT='DK_IDENTIFICATION_CODES'
816 and hoi2.organization_id = hoi1.organization_id
817 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
818
819 rec_get_bg_details csr_get_bg_details%ROWTYPE;
820
821 BEGIN
822
823 l_return :=1;
824
825 OPEN csr_get_bg_details(p_bg_id, fnd_date.displaydt_to_date(p_effective_date));
826 FETCH csr_get_bg_details INTO rec_get_bg_details;
827 CLOSE csr_get_bg_details;
828
829 p_tax_rc := rec_get_bg_details.tax_rc;
830 p_amb_rc := rec_get_bg_details.amb_rc;
831 p_sp_rc := rec_get_bg_details.sp_rc;
832 p_hol_days_rc := rec_get_bg_details.hol_days_rc;
833
834 RETURN l_return;
835
836 END get_ident_codes;
837
838 /* Added to support multiple pensions for OSI02 for bug fix 5563150*/
839 FUNCTION get_pen_values(p_eff_date DATE,p_ele_type_id NUMBER, p_ee_id NUMBER, p_iv_name VARCHAR2) RETURN VARCHAR2 IS
840
841 CURSOR csr_get_pen_values(p_eff_date DATE, p_ee_id NUMBER, p_iv_id NUMBER) IS
842 SELECT nvl(screen_entry_value,0)
843 FROM pay_element_entry_values_f
844 WHERE element_entry_id = p_ee_id
845 AND input_value_id = p_iv_id
846 AND p_eff_date BETWEEN effective_start_date and effective_end_date ;
847
848 CURSOR csr_get_iv_id(p_ele_type_id NUMBER,p_iv_name VARCHAR2, p_eff_date DATE) IS
849 SELECT input_value_id
850 FROM pay_input_values_f
851 WHERE name = p_iv_name
852 AND element_type_id = p_ele_type_id
853 AND legislation_code ='DK'
854 AND p_eff_date BETWEEN effective_start_date and effective_end_date ;
855
856 l_iv_id NUMBER;
857 l_result_value VARCHAR2(80);
858
859 BEGIN
860 OPEN csr_get_iv_id(p_ele_type_id,p_iv_name,p_eff_date);
861 FETCH csr_get_iv_id INTO l_iv_id;
862 CLOSE csr_get_iv_id;
863
864 IF l_iv_id IS NOT NULL THEN
865 OPEN csr_get_pen_values(p_eff_date,p_ee_id,l_iv_id);
866 FETCH csr_get_pen_values INTO l_result_value;
867 CLOSE csr_get_pen_values;
868 END IF;
869
870 RETURN l_result_value;
871
872 END get_pen_values;
873
874
875 /* Added to support override for Use of Holiday Card for transfer to Holiday Bank for bug fix 5533140*/
876 FUNCTION get_use_hol_card(p_payroll_action_id NUMBER,p_date_earned DATE ) RETURN VARCHAR2
877 IS
878
879 l_value PER_TIME_PERIODS.PRD_INFORMATION2%TYPE;
880 l_payroll_id NUMBER;
881
882 /* Modified the cursor for bug 5533140*/
883
884 CURSOR get_value_from_ddf(p_payroll_id NUMBER , p_date_earned DATE) IS
885 SELECT PRL_INFORMATION1
886 FROM pay_payrolls_f ppf
887 WHERE PAYROLL_ID = p_payroll_id
888 AND p_date_earned BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE;
889
890 CURSOR get_payroll_id (p_payroll_action_id NUMBER ) IS
891 SELECT PAYROLL_ID
892 FROM PAY_PAYROLL_ACTIONS ppa
893 WHERE payroll_action_id = p_payroll_action_id;
894
895
896 BEGIN
897
898 OPEN get_payroll_id(p_payroll_action_id);
899 FETCH get_payroll_id INTO l_payroll_id;
900 CLOSE get_payroll_id;
901
902 OPEN get_value_from_ddf(l_payroll_id, p_date_earned);
903 FETCH get_value_from_ddf INTO l_value;
904 CLOSE get_value_from_ddf;
905
906 RETURN l_value;
907
908 END get_use_hol_card;
909
910 FUNCTION get_pay_period_per_year(p_payroll_action_id NUMBER,p_date_earned DATE ) RETURN NUMBER
911 IS
912 Cursor csr_pay_period (p_payroll_action_id NUMBER , p_date_earned DATE) is
913 Select
914 TPTYPE.number_per_fiscal_year
915 from
916 pay_payroll_actions PACTION
917 , per_time_periods TPERIOD
918 , per_time_period_types TPTYPE
919 where PACTION.payroll_action_id = p_payroll_action_id
920 and TPERIOD.payroll_id = PACTION.payroll_id
921 and p_date_earned between TPERIOD.start_date and TPERIOD.end_date
922 and TPTYPE.period_type = TPERIOD.period_type;
923
924 l_period_per_year NUMBER;
925 BEGIN
926 OPEN csr_pay_period(p_payroll_action_id, p_date_earned);
927 FETCH csr_pay_period INTO l_period_per_year;
928 CLOSE csr_pay_period;
929
930 Return l_period_per_year;
931
932 END get_pay_period_per_year;
933
934 --15985354
935 FUNCTION get_collective_agr_no(p_asg_id NUMBER, p_date_earned DATE, p_bg_id number ) RETURN VARCHAR2
936 IS
937
938 CURSOR CSR_COLL_AGR_NO (P_ASG_ID NUMBER , P_DATE_EARNED DATE, P_BG_ID NUMBER) IS
939 SELECT NAME
940 FROM
941 PER_COLLECTIVE_AGREEMENTS PCG,
942 PER_ALL_ASSIGNMENTS_F PAAF
943 WHERE
944 PAAF.COLLECTIVE_AGREEMENT_ID = PCG.COLLECTIVE_AGREEMENT_ID
945 AND PAAF.BUSINESS_GROUP_ID = PCG.BUSINESS_GROUP_ID
946 AND P_DATE_EARNED BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
947 AND P_DATE_EARNED BETWEEN PCG.START_DATE AND NVL(PCG.END_DATE, TO_DATE('31-12-4712','DD-MM-RRRR'))
948 AND PAAF.ASSIGNMENT_ID = P_ASG_ID
949 AND PAAF.BUSINESS_GROUP_ID = P_BG_ID;
950
951 l_name PER_COLLECTIVE_AGREEMENTS.NAME%TYPE;
952
953 BEGIN
954
955 OPEN CSR_COLL_AGR_NO(p_asg_id, p_date_earned, p_bg_id);
956 FETCH CSR_COLL_AGR_NO INTO l_name;
957 CLOSE CSR_COLL_AGR_NO;
958
959 Return NVL(l_name,' ');
960
961 END get_collective_agr_no;
962 --15985354
963
964 END PAY_DK_PAYMENT_PROCESS_PKG;