[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_PAYFILE_SEPA
Source
1 PACKAGE BODY PAY_IE_PAYFILE_SEPA as
2 /* $Header: pyiesepa.pkb 120.1 2011/01/04 13:21:31 abraghun noship $ */
3
4 g_package varchar2(33) := ' PAY_IE_PAYFILE_SEPA.';
5
6 FUNCTION get_parameter(
7 p_parameter_string IN VARCHAR2
8 ,p_token IN VARCHAR2
9 ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2
10 IS
11 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
12 l_start_pos NUMBER;
13 l_delimiter varchar2(1):=' ';
14 l_proc VARCHAR2(60):= g_package||' get parameter ';
15 BEGIN
16 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
17 IF l_start_pos = 0 THEN
18 l_delimiter := '|';
19 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
20 end if;
21 IF l_start_pos <> 0 THEN
22 l_start_pos := l_start_pos + length(p_token||'=');
23 l_parameter := substr(p_parameter_string,
24 l_start_pos,
25 instr(p_parameter_string||' ',
26 ',',l_start_pos)
27 - l_start_pos);
28 IF p_segment_number IS NOT NULL THEN
29 l_parameter := ':'||l_parameter||':';
30 l_parameter := substr(l_parameter,
31 instr(l_parameter,':',1,p_segment_number)+1,
32 instr(l_parameter,':',1,p_segment_number+1) -1
33 - instr(l_parameter,':',1,p_segment_number));
34 END IF;
35 END IF;
36 RETURN l_parameter;
37 END get_parameter;
38
39 --------------------------------------------------------------------------------+
40 -- Range cursor returns the ids of the assignments to be archived
41 --------------------------------------------------------------------------------+
42 PROCEDURE range_code(
43 p_payroll_action_id IN NUMBER,
44 p_sqlstr OUT NOCOPY VARCHAR2)
45 IS
46
47 l_proc_name VARCHAR2(100) := g_package || 'range_code';
48
49 BEGIN
50
51 hr_utility.set_location(l_proc_name, 10);
52
53
54 p_sqlstr := 'SELECT distinct asg.person_id
55 FROM per_periods_of_service pos,
56 per_assignments_f asg,
57 pay_payroll_actions ppa
58 WHERE ppa.payroll_action_id = :payroll_action_id
59 AND pos.person_id = asg.person_id
60 AND pos.period_of_service_id = asg.period_of_service_id
61 AND pos.business_group_id = ppa.business_group_id
62 AND asg.business_group_id = ppa.business_group_id
63 ORDER BY asg.person_id';
64 hr_utility.set_location(l_proc_name, 20);
65
66 END range_code;
67
68 --------------------------------------------------------------------------------+
69 -- Creates assignment action id for all the valid person id's in
70 -- the range selected by the Range code.
71 --------------------------------------------------------------------------------+
72 PROCEDURE assignment_action_code(
73 p_payroll_action_id IN NUMBER,
74 p_start_person_id IN NUMBER,
75 p_end_person_id IN NUMBER,
76 p_chunk_number IN NUMBER)
77 IS
78
79 l_proc_name VARCHAR2(100) := g_package || 'assignment_action_code';
80
81
82 CURSOR csr_asg(p_payroll_action_id NUMBER,
83 p_start_person_id NUMBER,
84 p_end_person_id NUMBER,
85 p_payroll_id NUMBER,
86 p_consolidation_id NUMBER,
87 p_assignment_set_id NUMBER,
88 p_person_id NUMBER) IS
89 SELECT act.assignment_action_id,
90 act.assignment_id,
91 ppp.pre_payment_id
92 FROM pay_assignment_actions act,
93 per_all_assignments_f asg,
94 pay_payroll_actions pa2,
95 pay_payroll_actions pa1,
96 pay_pre_payments ppp,
97 pay_org_payment_methods_f OPM,
98 pay_payment_types PPT,
99 per_all_people_f pap
100 WHERE pa1.payroll_action_id = p_payroll_action_id
101 AND pa2.consolidation_set_id = p_consolidation_id
102 AND pa2.payroll_id = NVL(p_payroll_id,pa2.payroll_id)
103 AND pa2.effective_date <= pa1.effective_date
104 AND pa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
105 AND act.payroll_action_id = pa2.payroll_action_id
106 AND act.action_status IN ('C','S') --10225372
107 AND asg.assignment_id = act.assignment_id
108 AND pa1.business_group_id = asg.business_group_id
109 AND pa1.effective_date between asg.effective_start_date and asg.effective_end_date
110 AND pa1.effective_date between pap.effective_start_date and pap.effective_end_date
111 AND pap.person_id = asg.person_id
112 AND pap.person_id between p_start_person_id and p_end_person_id
113 AND ppp.assignment_action_id = act.assignment_action_id
114 AND ppp.org_payment_method_id = opm.org_payment_method_id
115 AND opm.payment_type_id = ppt.payment_type_id
116 AND ppt.territory_code = 'IE'
117 AND ppt.payment_type_name = 'SEPA'
118 AND pap.person_id = NVL(p_person_id,pap.person_id)
119 AND (p_assignment_set_id IS NULL
120 OR EXISTS ( SELECT ''
121 FROM hr_assignment_set_amendments hr_asg
122 WHERE hr_asg.assignment_set_id = p_assignment_set_id
123 AND hr_asg.assignment_id = asg.assignment_id
124 ))
125 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
126 FROM pay_action_interlocks pai1,
127 pay_assignment_actions act2,
128 pay_payroll_actions appa
129 WHERE pai1.locked_action_id = act.assignment_action_id
130 AND act2.assignment_action_id = pai1.locking_action_id
131 AND act2.payroll_action_id = appa.payroll_action_id
132 AND appa.action_type = 'X'
133 AND appa.report_type = 'SEPA');
134
135
136 l_payroll_id VARCHAR2(15):=NULL;
137 l_consolidation_set VARCHAR2(15):=NULL;
138 l_locking_action_id VARCHAR2(15):=NULL;
139 l_assignment_set_id VARCHAR2(15):=NULL;
140 l_person_id VARCHAR2(15):=NULL;
141
142 BEGIN
143
144 --hr_utility.trace_on(NULL,'VV');
145 hr_utility.set_location(l_proc_name, 10);
146
147 pay_ie_archive_detail_pkg.get_parameters (
148 p_payroll_action_id => p_payroll_action_id
149 , p_token_name => 'PAYROLL_ID'
150 , p_token_value => l_payroll_id);
151
152 pay_ie_archive_detail_pkg.get_parameters (
153 p_payroll_action_id => p_payroll_action_id
154 , p_token_name => 'CONSOLIDATION_SET_ID'
155 , p_token_value => l_consolidation_set);
156
157 pay_ie_archive_detail_pkg.get_parameters (
158 p_payroll_action_id => p_payroll_action_id
159 , p_token_name => 'ASSIGNMENT_SET_ID'
160 , p_token_value => l_assignment_set_id);
161
162 pay_ie_archive_detail_pkg.get_parameters (
163 p_payroll_action_id => p_payroll_action_id
164 , p_token_name => 'PERSON_ID'
165 , p_token_value => l_person_id);
166
167
168 hr_utility.set_location(l_proc_name, 20);
169
170 FOR rec_asg IN csr_asg(p_payroll_action_id
171 ,p_start_person_id
172 ,p_end_person_id
173 ,l_payroll_id
174 ,l_consolidation_set
175 ,l_assignment_set_id
176 ,l_person_id) LOOP
177
178 SELECT pay_assignment_actions_s.nextval
179 INTO l_locking_action_id
180 FROM dual;
181
182
183 hr_nonrun_asact.insact(lockingactid => l_locking_action_id,
184 assignid => rec_asg.assignment_id,
185 pactid => p_payroll_action_id,
186 chunk => p_chunk_number,
187 greid => NULL,
188 prepayid => rec_asg.pre_payment_id,
189 status => 'U');
190
191
192 --
193 -- insert the lock on the run action.
194 --
195
196 hr_nonrun_asact.insint(l_locking_action_id
197 , rec_asg.assignment_action_id);
198 --
199
200 END LOOP;
201 hr_utility.set_location(l_proc_name, 40);
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 hr_utility.set_location('Error in assignment action code ',100);
206 RAISE;
207 END assignment_action_code;
208
209
210 /*--------------------------------------*/
211 /* To Fetch the employer address
212 /*--------------------------------------*/
213
214 FUNCTION get_employer_address
215 (p_org_id IN NUMBER,
216 p_bg_id IN NUMBER,
217 p_line1 IN OUT NOCOPY VARCHAR2,
218 p_line2 IN OUT NOCOPY VARCHAR2,
219 p_line3 IN OUT NOCOPY VARCHAR2,
220 p_country IN OUT NOCOPY VARCHAR2,
221 p_postal_code IN OUT NOCOPY VARCHAR2,
222 p_geo_code IN OUT NOCOPY VARCHAR2
223 )
224 RETURN NUMBER IS
225
226 CURSOR c_employer_address IS
227 SELECT hlc.address_line_1 address_1
228 ,hlc.address_line_2 address_2
229 ,hlc.address_line_3 address_3
230 ,hlc.country country
231 ,hlc.postal_code postcode
232 ,hlc.region_2 geo_code
233 FROM hr_locations hlc
234 ,hr_organization_units hou
235 WHERE hou.business_group_id = p_bg_id
236 AND hou.organization_id = p_org_id
237 AND hlc.location_id = hou.location_id;
238
239 BEGIN
240
241 OPEN c_employer_address;
242 FETCH c_employer_address INTO p_line1,p_line2,p_line3,p_country,p_postal_code,p_geo_code;
243 CLOSE c_employer_address;
244
245 RETURN 1;
246
247 END get_employer_address;
248
249
250 /*--------------------------------------*/
251 /* To Fetch the Payee details
252 /*--------------------------------------*/
253
254
255 FUNCTION get_payee_details(p_assignment_id IN NUMBER
256 ,p_business_group_id IN NUMBER
257 ,p_per_pay_method_id IN NUMBER
258 ,p_date_earned IN DATE
259 ,p_line1 OUT NOCOPY Varchar2
260 ,p_line2 OUT NOCOPY Varchar2
261 ,p_line3 OUT NOCOPY Varchar2
262 ,p_country OUT NOCOPY Varchar2
263 ,p_postal_code OUT NOCOPY Varchar2
264 ,p_geo_code OUT NOCOPY Varchar2
265 ) RETURN VARCHAR2 IS
266 --
267
268 CURSOR csr_get_payee_type_id
269 IS
270 SELECT payee_type,payee_id
271 FROM pay_personal_payment_methods_f ppm
272 WHERE ppm.assignment_id = p_assignment_id
273 AND ppm.personal_payment_method_id = p_per_pay_method_id
274 AND p_date_earned BETWEEN ppm.effective_start_date AND ppm.effective_end_date
275 AND ppm.business_group_id = p_business_group_id
276 AND ppm.payee_id is NOT NULL;
277 --
278 l_payee_type pay_personal_payment_methods_f.payee_type%TYPE;
279 l_payee_id pay_personal_payment_methods_f.payee_id%TYPE;
280 l_payee_name VARCHAR(35);
281 l_org_count NUMBER;
282 --
283
284 l_address_return VARCHAR2(100);
285 --
286
287 FUNCTION get_payee_address(p_payee_id IN NUMBER
288 ,p_payee_type IN VARCHAR2
289 ,p_effective_date IN DATE) RETURN VARCHAR2 AS
290
291 --
292
293 CURSOR csr_get_per_address(p_payee_id NUMBER)
294 IS -- to get the city when address style is Netherlands
295 SELECT pad.address_line1 address_line1
296 ,pad.address_line2 address_line2
297 ,pad.address_line3 address_line3
298 ,pad.country country
299 ,pad.postal_code postcode
300 ,pad.region_2 geo_code
301 FROM per_addresses pad
302 WHERE pad.person_id = p_payee_id
303 AND pad.primary_flag = 'Y'
304 AND p_effective_date BETWEEN pad.date_from
305 AND nvl(pad.date_to,to_date('31/12/4712','DD/MM/YYYY'));
306 --
307 CURSOR csr_get_org_address(p_payee_id NUMBER, p_bg_id NUMBER)
308 IS
309 SELECT hlc.address_line_1 address_1
310 ,hlc.address_line_2 address_2
311 ,hlc.address_line_3 address_3
312 ,hlc.country country
313 ,hlc.postal_code postcode
314 ,hlc.region_2 geo_code
315 FROM hr_locations hlc
316 ,hr_organization_units hou
317 WHERE hou.business_group_id = p_bg_id
318 AND hou.organization_id = p_payee_id
319 AND hlc.location_id = hou.location_id;
320 --
321
322
323 l_addr_ret VARCHAR2(100);
324 l_payee_address_style VARCHAR2(35);
325
326 --
327 BEGIN
328
329 hr_utility.set_location('--In Payee Address ',30);
330 --
331
332 IF p_payee_type = 'P'
333 THEN -- Person Address
334
335 OPEN csr_get_per_address(p_payee_id);
336 FETCH csr_get_per_address INTO p_line1,p_line2,p_line3,p_country,p_postal_code,p_geo_code;
337 CLOSE csr_get_per_address;
338
339 ELSIF p_payee_type = 'O'
340 THEN -- Organization Address
341
342 OPEN csr_get_org_address(p_payee_id, p_business_group_id);
343 FETCH csr_get_org_address INTO p_line1,p_line2,p_line3,p_country,p_postal_code,p_geo_code;
344 CLOSE csr_get_org_address;
345 END IF;
346 --
347 hr_utility.set_location('--Leaving Payee Address ',30);
348 RETURN l_addr_ret;
349 --
350 END get_payee_address;
351
352
353 BEGIN
354 --
355
356 l_payee_name := ' ';
357
358 OPEN csr_get_payee_type_id;
359 FETCH csr_get_payee_type_id INTO l_payee_type,l_payee_id;
360
361 IF csr_get_payee_type_id%FOUND
362 THEN
363
364 IF l_payee_type = 'O'
365 THEN
366
367 l_address_return := nvl(substr(get_payee_address(l_payee_id
368 , l_payee_type
369 , p_date_earned),1,35),' ');
370 -- Get PAYE Name
371 --
372 l_payee_name:=substr(pay_org_payment_methods_pkg.payee_type(
373 l_payee_type
374 ,l_payee_id
375 ,p_date_earned),1,35); --
376
377 hr_utility.set_location('--In Core PAYEE Return ',11);
378 CLOSE csr_get_payee_type_id;
379 RETURN l_payee_name;
380
381 ELSIF l_payee_type = 'P' OR l_payee_type = 'p'
382 THEN
383
384 l_address_return := nvl(substr(get_payee_address(l_payee_id
385 , l_payee_type
386 , p_date_earned),1,35),' ');
387
388 l_payee_name:=substr(pay_org_payment_methods_pkg.payee_type(
389 l_payee_type
390 ,l_payee_id
391 ,p_date_earned),1,35);
392 CLOSE csr_get_payee_type_id;
393 return l_payee_name;
394
395 END IF;
396 ELSE
397
398 SELECT person_id
399 INTO l_payee_id
400 FROM per_all_assignments_f paf
401 WHERE paf.assignment_id = p_assignment_id
402 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
403
404 l_payee_type := 'P';
405 l_payee_name:= ' ';
406
407 -- Get PAYE Address
408 --
409 l_address_return := nvl(substr(get_payee_address(l_payee_id
410 , l_payee_type
411 , p_date_earned),1,35),' ');
412
413 CLOSE csr_get_payee_type_id;
414 return l_payee_name ;
415 END IF;
416
417 END get_payee_details;
418
419 FUNCTION GET_REPORT_TOTAL( p_report_total OUT NOCOPY NUMBER
420 ,p_report_count OUT NOCOPY NUMBER)
421 RETURN VARCHAR2
422 IS
423
424
425 /* This cursor is not required for IE */
426 /* CURSOR CSR_EMPLOYER
427 IS
428 SELECT hou.organization_id
429 ,pea.external_account_id
430 ,NVL(pea.SEGMENT10,' ')
431 ,NVL(pea.SEGMENT9,' ')
432 ,hoi.org_information4
433 from hr_all_organization_units hou,
434 hr_organization_information hoi,
435 pay_org_payment_methods_f popmf,
436 PAY_ORG_PAY_METHOD_USAGES_F popmu,
437 PAY_ALL_PAYROLLS_F papf,
438 PAY_EXTERNAL_ACCOUNTS pea,
439 PAY_PAYROLL_ACTIONS ppa,
440 hr_soft_coding_keyflex hsck
441 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
442 and hou.business_group_id = ppa.business_group_id
443 and hoi.org_information_context(+) = 'IE_PAYPATH_INFORMATION'
444 and hou.organization_id = hoi.organization_id
445 and popmu.org_payment_method_id = ppa.org_payment_method_id
446 and ppa.effective_date between popmu.effective_start_date and popmu.effective_end_date
447 and popmf.org_payment_method_id = popmu.org_payment_method_id
448 and popmf.business_group_id = hou.business_group_id
449 and ppa.effective_date between popmf.effective_start_date and popmf.effective_end_date
450 and papf.payroll_id = NVL(ppa.payroll_id,papf.payroll_id)
451 and papf.payroll_id = popmu.payroll_id
452 and papf.business_group_id = ppa.business_group_id
453 and papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
454 and hsck.segment4 = hou.organization_id
455 and ppa.effective_date between papf.effective_start_date and papf.effective_end_date
456 and pea.external_account_id = popmf.external_account_id and
457 --restricting employer who doesn't have any assignment to pay.
458 EXISTS
459 (SELECT 1
460 FROM pay_assignment_actions pas
461 ,pay_pre_payments ppp
462 ,per_all_assignments_f paaf
463 ,pay_all_payrolls_f ppf
464 ,hr_soft_coding_keyflex hsck1
465 WHERE pas.payroll_action_id = ppa.payroll_action_id
466 AND ppp.pre_payment_id = pas.pre_payment_id
467 AND ppp.value > 0
468 AND paaf.assignment_id = pas.assignment_id
469 AND ppf.payroll_id = paaf.payroll_id
470 and ppf.soft_coding_keyflex_id = hsck1.soft_coding_keyflex_id
471 and hsck1.segment4 = hou.organization_id
472 AND ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
473 AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
474 )
475 Group by hou.organization_id
476 ,pea.external_account_id
477 ,NVL(pea.SEGMENT10,' ')
478 ,NVL(pea.SEGMENT9,' ')
479 ,hoi.org_information4; */
480
481
482 CURSOR CSR_EMPLOYER_SUM
483 IS
484 SELECT SUM(employer_sum) er_sum,
485 SUM(record_count) er_count
486 FROM (SELECT SUM(ppp.value*100) employer_sum,
487 1 record_count
488 FROM per_all_assignments_f paf
489 ,per_all_people_f pef
490 ,pay_all_payrolls_f ppf
491 ,pay_pre_payments ppp
492 ,pay_assignment_actions paa
493 ,pay_payroll_actions ppa
494 ,pay_personal_payment_methods_f ppmf
495 ,pay_external_accounts pea
496 WHERE paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
497 AND paa.pre_payment_id = ppp.pre_payment_id
498 AND paa.payroll_action_id = ppa.payroll_action_id
499 AND PPP.personal_payment_method_id = ppmf.personal_payment_method_id
500 AND paa.assignment_id = paf.assignment_id
501 AND paf.payroll_id = NVL(ppa.payroll_id,paf.payroll_id)
502 AND paf.person_id = pef.person_id
503 AND ppf.payroll_id = paf.payroll_id
504 AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
505 --AND ppf.prl_information_category = 'NL'
506 --AND ppf.prl_information1 = p_organization_id
507 --and ppf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
508 --and hsck.segment4 = p_organization_id
509 AND ppp.value > 0
510 AND pea.external_account_id = ppmf.external_account_id
511 AND ppa.effective_date BETWEEN paf.effective_start_date
512 AND paf.effective_end_date
513 AND ppa.effective_date BETWEEN pef.effective_start_date
514 AND pef.effective_end_date
515 AND ppa.effective_date BETWEEN ppmf.effective_start_date
516 AND ppmf.effective_end_date
517 GROUP BY pef.person_id,
518 pea.SEGMENT7,
519 pea.SEGMENT6,
520 ppmf.ORG_PAYMENT_METHOD_ID
521 );
522
523 BEGIN
524 p_report_total := 0;
525 p_report_count := 0;
526
527 --FOR I in CSR_EMPLOYER
528 --LOOP
529 FOR J IN CSR_EMPLOYER_SUM --(I.organization_id)
530 LOOP
531 p_report_total := p_report_total + J.er_sum ;
532 p_report_count := p_report_count + J.er_count ;
533 END LOOP;
534 --END LOOP;
535
536 RETURN 'Y';
537
538 END GET_REPORT_TOTAL;
539
540 FUNCTION BIC(p_bic_code IN VARCHAR2
541 ,p_bic_meaning OUT NOCOPY VARCHAR2)
542 RETURN VARCHAR2 IS
543
544 CURSOR c_bic_meaning
545 IS
546 SELECT MEANING
547 FROM
548 FND_LOOKUP_VALUES
549 where
550 LOOKUP_TYPE = 'HR_IE_BIC_CODES'
551 and ENABLED_FLAG = 'Y'
552 and LOOKUP_CODE = p_bic_code
553 and LANGUAGE = 'US';
554
555 BEGIN
556 p_bic_meaning := ' ';
557
558 OPEN c_bic_meaning;
559 FETCH c_bic_meaning INTO p_bic_meaning;
560 CLOSE c_bic_meaning;
561
562 RETURN 'Y';
563
564 END BIC;
565
566 END PAY_IE_PAYFILE_SEPA;