[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_PAYFILE_SEPA
Source
1 PACKAGE BODY PAY_NL_PAYFILE_SEPA as
2 /* $Header: pynlsepa.pkb 120.1.12020000.2 2012/11/09 11:58:29 sgmaram ship $ */
3 g_package varchar2(33) := ' PAY_NL_PAYFILE_SEPA.';
4 -- Global Variables
5 hr_formula_error EXCEPTION;
6 g_payee_details_formula_exists BOOLEAN := TRUE;
7 g_payee_details_formula_cached BOOLEAN := FALSE;
8 g_payee_details_formula_id ff_formulas_f.formula_id%TYPE;
9 g_payee_details_formula_name ff_formulas_f.formula_name%TYPE;
10 g_trans_desc_formula_exists BOOLEAN := TRUE;
11 g_trans_desc_formula_cached BOOLEAN := FALSE;
12 g_trans_desc_formula_id ff_formulas_f.formula_id%TYPE;
13 g_trans_desc_formula_name ff_formulas_f.formula_name%TYPE;
14 ------------------------------------------------------------------------------
15 -- Global Variables for over riding Oraganization Name
16 ------------------------------------------------------------------------------
17 g_org_details_formula_exists BOOLEAN := TRUE;
18 g_org_details_formula_cached BOOLEAN := FALSE;
19 g_org_details_formula_id ff_formulas_f.formula_id%TYPE;
20 g_org_details_formula_name ff_formulas_f.formula_name%TYPE;
21 -----------------------------------------------------------------------------
22 --
23 FUNCTION RAISE_WARNING(p_flag IN VARCHAR2, p_message IN VARCHAR2) return number is
24 l_status BOOLEAN;
25 BEGIN
26 IF p_flag = 'Y' THEN
27 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS
28 (
29 status => 'WARNING',
30 message => p_message
31 );
32 END IF;
33 return 1;
34 END raise_warning;
35 --
36 --
37 FUNCTION NL_GET_MESSAGE_IN_LOG(p_message IN VARCHAR2)
38 RETURN VARCHAR2 IS
39
40 BEGIN
41
42 fnd_file.put_line(FND_FILE.LOG,p_message);
43
44 RETURN '1';
45
46 END NL_GET_MESSAGE_IN_LOG;
47 --
48
49
50 FUNCTION get_employer_address
51 (p_org_id IN NUMBER,
52 p_bg_id IN NUMBER,
53 p_house_number IN OUT NOCOPY VARCHAR2,
54 p_house_no_add IN OUT NOCOPY VARCHAR2,
55 p_street_name IN OUT NOCOPY VARCHAR2,
56 p_line1 IN OUT NOCOPY VARCHAR2,
57 p_line2 IN OUT NOCOPY VARCHAR2,
58 p_line3 IN OUT NOCOPY VARCHAR2,
59 p_city IN OUT NOCOPY VARCHAR2,
60 p_country IN OUT NOCOPY VARCHAR2,
61 p_postal_code IN OUT NOCOPY VARCHAR2
62 )
63 RETURN NUMBER IS
64
65 CURSOR c_employer_address IS
66 SELECT
67 hlc.loc_information14 house_number
68 ,hlc.loc_information15 house_number_add
69 ,hlc.address_line_1 address_1
70 ,hlc.address_line_2 address_2
71 ,hlc.address_line_3 address_3
72 ,hlc.region_1 street_name
73 ,hlc.postal_code postcode
74 ,hr_general.decode_lookup('HR_NL_CITY',hlc.town_or_city) city
75 ,hlc.country country
76 FROM hr_locations hlc
77 ,hr_organization_units hou
78 WHERE hou.business_group_id = p_bg_id
79 AND hou.organization_id = p_org_id
80 AND hlc.location_id = hou.location_id;
81
82 BEGIN
83
84 OPEN c_employer_address;
85 FETCH c_employer_address INTO p_house_number,p_house_no_add,p_line1,p_line2,p_line3,p_street_name,p_postal_code,p_city,p_country;
86 CLOSE c_employer_address;
87
88 RETURN 1;
89
90 END get_employer_address;
91
92 FUNCTION get_payee_details(p_assignment_id IN NUMBER
93 ,p_business_group_id IN NUMBER
94 ,p_per_pay_method_id IN NUMBER
95 ,p_date_earned IN DATE
96 ,p_house_number OUT NOCOPY Varchar2
97 ,p_house_no_add OUT NOCOPY Varchar2
98 ,p_street_name OUT NOCOPY Varchar2
99 ,p_line1 OUT NOCOPY Varchar2
100 ,p_line2 OUT NOCOPY Varchar2
101 ,p_line3 OUT NOCOPY Varchar2
102 ,p_postal_code OUT NOCOPY Varchar2
103 ,p_city OUT NOCOPY Varchar2
104 ,p_country OUT NOCOPY Varchar2
105 ) RETURN VARCHAR2 IS
106 --
107 CURSOR csr_get_payee_type_id IS
108 SELECT payee_type,payee_id
109 FROM pay_personal_payment_methods_f ppm
110 WHERE ppm.assignment_id = p_assignment_id
111 AND ppm.personal_payment_method_id = p_per_pay_method_id
112 AND p_date_earned BETWEEN ppm.effective_start_date
113 AND ppm.effective_end_date
114 AND ppm.business_group_id = p_business_group_id
115 AND ppm.payee_id is NOT NULL;
116 --
117 cursor csr_org_count (p_org_id pay_personal_payment_methods_f.payee_id%TYPE) is
118 select count(*) from
119 hr_All_organization_units hou,
120 hr_organization_information hoi
121 where hou.organization_id = p_org_id
122 and hoi.organization_id = hou.organization_id
123 and hoi.org_information_context = 'CLASS'
124 and hoi.org_information1 = 'NL_PAYEE_OVERRIDE'
125 and hoi.org_information2='Y';
126 --
127 l_payee_type pay_personal_payment_methods_f.payee_type%TYPE;
128 l_payee_id pay_personal_payment_methods_f.payee_id%TYPE;
129 l_payee_name VARCHAR(35);
130 l_org_count NUMBER;
131 --
132 l_inputs ff_exec.inputs_t;
133 l_outputs ff_exec.outputs_t;
134 p_formula_exists BOOLEAN := TRUE;
135 p_formula_cached BOOLEAN := FALSE;
136 p_formula_id ff_formulas_f.formula_id%TYPE;
137 p_formula_name ff_formulas_f.formula_name%TYPE;
138 l_address_return VARCHAR2(100);
139 --
140
141 FUNCTION get_payee_address(p_payee_id IN NUMBER
142 ,p_payee_type IN VARCHAR2
143 ,p_effective_date IN DATE) RETURN VARCHAR2 AS
144 --
145 CURSOR csr_get_per_address_style(p_payee_id NUMBER) IS -- to get the address style
146 SELECT substr(style,1,35) style
147 FROM per_addresses pas
148 WHERE pas.person_id = p_payee_id
149 AND pas.primary_flag = 'Y'
150 AND p_effective_date BETWEEN pas.date_from
151 AND nvl(pas.date_to,to_date('31/12/4712','DD/MM/YYYY'));
152 --
153 CURSOR csr_get_per_address(p_payee_id NUMBER) IS -- to get the city when address style is Netherlands
154 SELECT pad.add_information13 house_number
155 ,pad.add_information14 house_no_add
156 ,pad.region_1 street_name
157 ,pad.address_line1 address_line1
158 ,pad.address_line2 address_line2
159 ,pad.address_line3 address_line3
160 ,pad.postal_code postcode
161 ,hr_general.decode_lookup('HR_NL_CITY',pad.town_or_city) city
162 ,pad.country country
163 FROM per_addresses pad
164 WHERE pad.person_id = p_payee_id
165 AND pad.primary_flag = 'Y'
166 AND p_effective_date BETWEEN pad.date_from
167 AND nvl(pad.date_to,to_date('31/12/4712','DD/MM/YYYY'));
168 --
169 CURSOR csr_get_org_address(p_payee_id NUMBER, p_bg_id NUMBER) IS
170 SELECT
171 hlc.loc_information14 house_number
172 ,hlc.loc_information15 house_number_add
173 ,hlc.address_line_1 address_1
174 ,hlc.address_line_2 address_2
175 ,hlc.address_line_3 address_3
176 ,hlc.region_1 street_name
177 ,hlc.postal_code postcode
178 ,hr_general.decode_lookup('HR_NL_CITY',hlc.town_or_city) city
179 ,hlc.country country
180 FROM hr_locations hlc
181 ,hr_organization_units hou
182 WHERE hou.business_group_id = p_bg_id
183 AND hou.organization_id = p_payee_id
184 AND hlc.location_id = hou.location_id;
185 --
186 l_addr_ret VARCHAR2(100);
187 l_payee_address_style VARCHAR2(35);
188
189 --
190 BEGIN
191 hr_utility.set_location('--In Payee Address ',30);
192 --
193 IF p_payee_type = 'P' THEN -- Person Address
194 OPEN csr_get_per_address(p_payee_id);
195 FETCH csr_get_per_address INTO p_house_number,p_house_no_add,p_street_name,p_line1,p_line2,p_line3,p_postal_code,p_city,p_country;
196 CLOSE csr_get_per_address;
197 ELSIF p_payee_type = 'O' THEN -- Organization Address
198 OPEN csr_get_org_address(p_payee_id, p_business_group_id);
199 FETCH csr_get_org_address INTO p_house_number,p_house_no_add,p_street_name,p_line1,p_line2,p_line3,p_postal_code,p_city,p_country;
200 CLOSE csr_get_org_address;
201 END IF;
202 --
203 hr_utility.set_location('--Leaving Payee Address ',30);
204 RETURN l_addr_ret;
205 --
206 END get_payee_address;
207
208 BEGIN
209 --
210 -- To be removed.
211 -- hr_utility.trace_on(null,'EFT');
212 -- hr_utility.set_location('--In Get Payee Details ',10);
213 g_payee_details_formula_name := 'NL_PAYEE_REPORTING_NAME';
214 g_org_details_formula_name := 'NL_ORG_PAYEE_REPORTING_NAME';
215 l_payee_name := ' ';
216 OPEN csr_get_payee_type_id;
217 FETCH csr_get_payee_type_id INTO l_payee_type,l_payee_id;
218 IF csr_get_payee_type_id%FOUND THEN
219 --
220 /* A check is made to see whether thsi organization having the Dutch
221 Payee Override Classification or not */
222 IF l_payee_type = 'O' THEN
223 OPEN csr_org_count(l_payee_id);
224 FETCH csr_org_count INTO l_org_count;
225 CLOSE csr_org_count;
226 IF l_org_count >0 THEN
227 --
228 IF g_org_details_formula_exists = TRUE THEN
229 IF g_org_details_formula_cached = FALSE THEN
230 cache_formula('NL_ORG_PAYEE_REPORTING_NAME',p_business_group_id,p_date_earned,p_formula_id,p_formula_exists,p_formula_cached);
231 g_org_details_formula_exists:=p_formula_exists;
232 g_org_details_formula_cached:=p_formula_cached;
233 g_org_details_formula_id:=p_formula_id;
234 END IF;
235 --
236 IF g_org_details_formula_exists THEN
237 --
238 l_inputs(1).name := 'ASSIGNMENT_ID';
239 l_inputs(1).value := p_assignment_id;
240 l_inputs(2).name := 'ORGANIZATION_ID';
241 l_inputs(2).value := l_payee_id;
242 l_inputs(3).name := 'DATE_EARNED';
243 l_inputs(3).value := fnd_date.date_to_canonical(p_date_earned);
244 l_inputs(4).name := 'BUSINESS_GROUP_ID';
245 l_inputs(4).value := p_business_group_id;
246 --
247 l_outputs(1).name := 'REPORTING_NAME';
248 --
249 run_formula(p_formula_id => g_org_details_formula_id,
250 p_effective_date => p_date_earned,
251 p_formula_name => g_org_details_formula_name,
252 p_inputs => l_inputs,
253 p_outputs => l_outputs);
254 --
255 l_payee_name := substr(l_outputs(1).value,1,35);
256
257 l_address_return := nvl(substr(get_payee_address(l_payee_id
258 , l_payee_type
259 , p_date_earned),1,35),' ');
260
261 CLOSE csr_get_payee_type_id;
262 RETURN l_payee_name;
263 END IF;
264
265 END IF;
266 END IF;
267
268 -- Get PAYE Name
269 --
270 l_payee_name:=substr(pay_org_payment_methods_pkg.payee_type(
271 l_payee_type
272 ,l_payee_id
273 ,p_date_earned),1,35); --
274 -- Get PAYE Address
275 --
276
277 l_address_return := nvl(substr(get_payee_address(l_payee_id
278 , l_payee_type
279 , p_date_earned),1,35),' ');
280 hr_utility.set_location('--In Core PAYEE Return ',11);
281 CLOSE csr_get_payee_type_id;
282 RETURN l_payee_name;
283
284 ELSIF l_payee_type = 'P' OR l_payee_type = 'p' THEN
285 --
286 IF g_payee_details_formula_exists = TRUE THEN
287
288 hr_utility.set_location('-- In the formula if ',111);
289
290
291 IF g_payee_details_formula_cached = FALSE THEN
292 cache_formula('NL_PAYEE_REPORTING_NAME',p_business_group_id,p_date_earned,p_formula_id,p_formula_exists,p_formula_cached);
293 g_payee_details_formula_exists:=p_formula_exists;
294 g_payee_details_formula_cached:=p_formula_cached;
295 g_payee_details_formula_id:=p_formula_id;
296 END IF;
297 --
298 IF g_payee_details_formula_exists THEN
299 --
300 --
301 l_inputs(1).name := 'ASSIGNMENT_ID';
302 l_inputs(1).value := p_assignment_id;
303 l_inputs(2).name := 'PERSON_ID';
304 l_inputs(2).value := l_payee_id;
305 l_inputs(3).name := 'DATE_EARNED';
306 l_inputs(3).value := fnd_date.date_to_canonical(p_date_earned);
307 l_inputs(4).name := 'BUSINESS_GROUP_ID';
308 l_inputs(4).value := p_business_group_id;
309 --
310 l_outputs(1).name := 'REPORTING_NAME';
311 --
312 run_formula(p_formula_id => g_payee_details_formula_id,
313 p_effective_date => p_date_earned,
314 p_formula_name => g_payee_details_formula_name,
315 p_inputs => l_inputs,
316 p_outputs => l_outputs);
317 --
318 l_payee_name := substr(l_outputs(1).value,1,35);
319
320 l_address_return := nvl(substr(get_payee_address(l_payee_id
321 , l_payee_type
322 , p_date_earned),1,35),' ');
323 CLOSE csr_get_payee_type_id;
324 return l_payee_name;
325
326 END IF;
327 END IF;
328
329 -- Get PAYE Name
330 -- hr_utility.set_location('--After the formula if ',115);
331
332 l_payee_name:=substr(pay_org_payment_methods_pkg.payee_type(
333 l_payee_type
334 ,l_payee_id
335 ,p_date_earned),1,35);
336
337 -- hr_utility.set_location('--l_payee name '||l_payee_name ,117);
338
339 -- Get PAYE Address
340 --
341 -- hr_utility.set_location('--In Formula Return ',11);
342 l_address_return := nvl(substr(get_payee_address(l_payee_id
343 , l_payee_type
344 , p_date_earned),1,35),' ');
345 -- hr_utility.set_location('--p_payee_address '||p_payee_address ,119);
346 CLOSE csr_get_payee_type_id;
347 RETURN l_payee_name;
348
349
350 END IF;
351 --
352 ELSE
353
354 IF g_payee_details_formula_exists = TRUE THEN
355
356
357 IF g_payee_details_formula_cached = FALSE THEN
358 cache_formula('NL_PAYEE_REPORTING_NAME',p_business_group_id,p_date_earned,p_formula_id,p_formula_exists,p_formula_cached);
359 g_payee_details_formula_exists:=p_formula_exists;
360 g_payee_details_formula_cached:=p_formula_cached;
361 g_payee_details_formula_id:=p_formula_id;
362 END IF;
363 --
364 IF g_payee_details_formula_exists THEN
365 --
366 --
367 l_inputs(1).name := 'ASSIGNMENT_ID';
368 l_inputs(1).value := p_assignment_id;
369 l_inputs(2).name := 'PERSON_ID';
370 l_inputs(2).value := l_payee_id;
371 l_inputs(3).name := 'DATE_EARNED';
372 l_inputs(3).value := fnd_date.date_to_canonical(p_date_earned);
373 l_inputs(4).name := 'BUSINESS_GROUP_ID';
374 l_inputs(4).value := p_business_group_id;
375 --
376 l_outputs(1).name := 'REPORTING_NAME';
377 --
378 run_formula(p_formula_id => g_payee_details_formula_id,
379 p_effective_date => p_date_earned,
380 p_formula_name => g_payee_details_formula_name,
381 p_inputs => l_inputs,
382 p_outputs => l_outputs);
383 --
384 l_payee_name := substr(l_outputs(1).value,1,35);
385
386 SELECT person_id INTO l_payee_id
387 FROM per_all_assignments_f paf
388 WHERE paf.assignment_id = p_assignment_id
389 AND p_date_earned BETWEEN paf.effective_start_date
390 AND paf.effective_end_date;
391
392 l_payee_type := 'P';
393
394 l_address_return := nvl(substr(get_payee_address(l_payee_id
395 , l_payee_type
396 , p_date_earned),1,35),' ');
397
398 CLOSE csr_get_payee_type_id;
399 return l_payee_name;
400
401 END IF;
402 END IF;
403
404 SELECT person_id INTO l_payee_id
405 FROM per_all_assignments_f paf
406 WHERE paf.assignment_id = p_assignment_id
407 AND p_date_earned BETWEEN paf.effective_start_date
408 AND paf.effective_end_date;
409
410 l_payee_type := 'P';
411
412 l_payee_name:= ' ';
413
414 l_payee_name:= nvl(l_payee_name,' ');
415
416 -- hr_utility.set_location('--l_payee name '||l_payee_name ,117);
417
418 -- Get PAYE Address
419 --
420 -- hr_utility.set_location('--In Formula Return ',11);
421 l_address_return := nvl(substr(get_payee_address(l_payee_id
422 , l_payee_type
423 , p_date_earned),1,35),' ');
424
425 CLOSE csr_get_payee_type_id;
426 return l_payee_name ;
427 END IF;
428
429 return l_payee_name ;
430 --
431 END get_payee_details;
432
433 PROCEDURE cache_formula(p_formula_name IN VARCHAR2
434 ,p_business_group_id IN NUMBER
435 ,p_effective_date IN DATE
436 ,p_formula_id IN OUT NOCOPY NUMBER
437 ,p_formula_exists IN OUT NOCOPY BOOLEAN
438 ,p_formula_cached IN OUT NOCOPY BOOLEAN
439 ) IS
440 --
441 CURSOR c_compiled_formula_exist IS
442 SELECT 'Y'
443 FROM ff_formulas_f ff
444 ,ff_compiled_info_f ffci
445 WHERE ff.formula_id = ffci.formula_id
446 AND ff.effective_start_date = ffci.effective_start_date
447 AND ff.effective_end_date = ffci.effective_end_date
448 AND ff.formula_id = p_formula_id
449 AND ff.business_group_id = p_business_group_id
450 AND p_effective_date BETWEEN ff.effective_start_date
451 AND ff.effective_end_date;
452 --
453 CURSOR c_get_formula(p_formula_name ff_formulas_f.formula_name%TYPE
454 ,p_effective_date DATE) IS
455 SELECT ff.formula_id
456 FROM ff_formulas_f ff
457 WHERE ff.formula_name = p_formula_name
458 AND ff.business_group_id = p_business_group_id
459 AND p_effective_date BETWEEN ff.effective_start_date
460 AND ff.effective_end_date;
461 --
462 l_test VARCHAR2(1);
463 BEGIN
464 --
465 IF p_formula_cached = FALSE THEN
466 --
467 --
468 OPEN c_get_formula(p_formula_name,p_effective_date);
469 FETCH c_get_formula INTO p_formula_id;
470 IF c_get_formula%FOUND THEN
471 OPEN c_compiled_formula_exist;
472 FETCH c_compiled_formula_exist INTO l_test;
473 IF c_compiled_formula_exist%NOTFOUND THEN
474 p_formula_cached := FALSE;
475 p_formula_exists := FALSE;
476 --
477 fnd_message.set_name('PAY','FFX03A_FORMULA_NOT_FOUND');
478 fnd_message.set_token('1', p_formula_name);
479 fnd_message.raise_error;
480 ELSE
481 p_formula_cached := TRUE;
482 p_formula_exists := TRUE;
483 END IF;
484 ELSE
485 p_formula_cached := FALSE;
486 p_formula_exists := FALSE;
487 END IF;
488 CLOSE c_get_formula;
489 END IF;
490 --
491 END cache_formula;
492 PROCEDURE run_formula(p_formula_id IN NUMBER
493 ,p_effective_date IN DATE
494 ,p_formula_name IN VARCHAR2
495 ,p_inputs IN ff_exec.inputs_t
496 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t) IS
497 l_inputs ff_exec.inputs_t;
498 l_outputs ff_exec.outputs_t;
499 BEGIN
500 hr_utility.set_location('--In Formula ',20);
501 --
502 -- Initialize the formula
503 --
504 ff_exec.init_formula(p_formula_id, p_effective_date , l_inputs, l_outputs);
505 --
506 -- Set up the input values
507 --
508 IF l_inputs.count > 0 and p_inputs.count > 0 THEN
509 FOR i IN l_inputs.first..l_inputs.last LOOP
510 FOR j IN p_inputs.first..p_inputs.last LOOP
511 IF l_inputs(i).name = p_inputs(j).name THEN
512 l_inputs(i).value := p_inputs(j).value;
513 exit;
514 END IF;
515 END LOOP;
516 END LOOP;
517 END IF;
518 --
519 -- Run the formula
520 --
521 ff_exec.run_formula(l_inputs,l_outputs);
522 --
523 -- Populate the output table
524 --
525 IF l_outputs.count > 0 and p_inputs.count > 0 then
526 FOR i IN l_outputs.first..l_outputs.last LOOP
527 FOR j IN p_outputs.first..p_outputs.last LOOP
528 IF l_outputs(i).name = p_outputs(j).name THEN
529 p_outputs(j).value := l_outputs(i).value;
530 exit;
531 END IF;
532 END LOOP;
533 END LOOP;
534 END IF;
535 hr_utility.set_location('--Leaving Formula ',21);
536 EXCEPTION
537 WHEN hr_formula_error THEN
538 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
539 fnd_message.set_token('1', p_formula_name);
540 fnd_message.raise_error;
541 WHEN OTHERS THEN
542 raise;
543 --
544 END run_formula;
545
546 FUNCTION GET_REPORT_TOTAL( p_report_total OUT NOCOPY NUMBER
547 ,p_report_count OUT NOCOPY NUMBER)
548 RETURN VARCHAR2
549 IS
550
551 CURSOR CSR_EMPLOYER IS
552 SELECT
553 hou.organization_id
554 ,pea.external_account_id
555 ,NVL(pea.SEGMENT10,' ')
556 ,NVL(pea.SEGMENT9,' ')
557 ,hoi.org_information4
558 from
559 hr_all_organization_units hou,
560 hr_organization_information hoi,
561 pay_org_payment_methods_f popmf,
562 PAY_ORG_PAY_METHOD_USAGES_F popmu,
563 PAY_ALL_PAYROLLS_F papf,
564 PAY_EXTERNAL_ACCOUNTS pea,
565 PAY_PAYROLL_ACTIONS ppa
566 where
567 ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID') and
568 hou.business_group_id = ppa.business_group_id and
569 hoi.org_information_context = 'NL_ORG_INFORMATION' and
570 hou.organization_id = hoi.organization_id and
571 popmu.org_payment_method_id = ppa.org_payment_method_id and
572 ppa.effective_date between popmu.effective_start_date and popmu.effective_end_date and
573 popmf.org_payment_method_id = popmu.org_payment_method_id and
574 popmf.business_group_id = hou.business_group_id and
575 ppa.effective_date between popmf.effective_start_date and popmf.effective_end_date and
576 papf.payroll_id = NVL(ppa.payroll_id,papf.payroll_id) and
577 papf.payroll_id = popmu.payroll_id and
578 papf.business_group_id = ppa.business_group_id and
579 papf.prl_information_category = 'NL' and
580 papf.prl_information1 = hou.organization_id and
581 ppa.effective_date between papf.effective_start_date and papf.effective_end_date and
582 pea.external_account_id = popmf.external_account_id and
583 --restricting employer who doesn't have any assignment to pay.
584 EXISTS
585 (
586 SELECT 1
587 FROM
588 pay_assignment_actions pas
589 ,pay_pre_payments ppp
590 ,per_all_assignments_f paaf
591 ,pay_all_payrolls_f ppf
592 WHERE pas.payroll_action_id = ppa.payroll_action_id
593 AND ppp.pre_payment_id = pas.pre_payment_id
594 AND ppp.value > 0
595 AND paaf.assignment_id = pas.assignment_id
596 AND ppf.payroll_id = paaf.payroll_id
597 AND ppf.prl_information1 = papf.prl_information1
598 AND ppf.prl_information_category = 'NL'
599 AND ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
600 AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
601 )
602 Group by
603 hou.organization_id
604 ,pea.external_account_id
605 ,NVL(pea.SEGMENT10,' ')
606 ,NVL(pea.SEGMENT9,' ')
607 ,hoi.org_information4;
608
609 CURSOR CSR_EMPLOYER_SUM(p_organization_id IN NUMBER) IS
610 SELECT SUM(employer_sum) er_sum, SUM(record_count) er_count
611 FROM
612 (SELECT
613 SUM(ppp.value*100) employer_sum, 1 record_count
614 FROM per_all_assignments_f paf
615 ,per_all_people_f pef
616 ,pay_all_payrolls_f ppf
617 ,pay_pre_payments ppp
618 ,pay_assignment_actions paa
619 ,pay_payroll_actions ppa
620 ,pay_personal_payment_methods_f ppmf
621 ,pay_external_accounts pea
622 WHERE paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
623 AND paa.pre_payment_id = ppp.pre_payment_id
624 AND paa.payroll_action_id = ppa.payroll_action_id
625 AND PPP.personal_payment_method_id = ppmf.personal_payment_method_id
626 AND paa.assignment_id = paf.assignment_id
627 AND paf.payroll_id = NVL(ppa.payroll_id,paf.payroll_id)
628 AND paf.person_id = pef.person_id
629 AND ppf.payroll_id = paf.payroll_id
630 AND ppa.effective_date BETWEEN ppf.effective_start_date
631 AND ppf.effective_end_date
632 AND ppf.prl_information_category = 'NL'
633 AND ppf.prl_information1 = p_organization_id
634 AND ppp.value > 0
635 AND pea.external_account_id = ppmf.external_account_id
636 AND ppa.effective_date BETWEEN paf.effective_start_date
637 AND paf.effective_end_date
638 AND ppa.effective_date BETWEEN pef.effective_start_date
639 AND pef.effective_end_date
640 AND ppa.effective_date BETWEEN ppmf.effective_start_date
641 AND ppmf.effective_end_date
642 GROUP BY
643 pef.person_id, pea.SEGMENT10, pea.SEGMENT9
644 );
645
646 BEGIN
647 p_report_total := 0;
648 p_report_count := 0;
649
650 FOR I in CSR_EMPLOYER
651 LOOP
652 FOR J IN CSR_EMPLOYER_SUM(I.organization_id)
653 LOOP
654 p_report_total := p_report_total + J.er_sum ;
655 p_report_count := p_report_count + J.er_count ;
656 END LOOP;
657 END LOOP;
658
659 RETURN 'Y';
660
661 END GET_REPORT_TOTAL;
662
663 --Fix for BUG 14779885
664 FUNCTION to_char_fm
665 (p_input_text IN varchar2, p_input_format IN varchar2) RETURN varchar2 IS
666 lv_return_text varchar2(30);
667 BEGIN
668 lv_return_text := to_char (p_input_text, p_input_format);
669 RETURN lv_return_text;
670 END to_char_fm;
671
672 END PAY_NL_PAYFILE_SEPA;