1 PACKAGE BODY pay_ca_direct_deposit_pkg AS
2 /* $Header: pycatapd.pkb 120.2 2005/10/06 11:43:49 mmukherj noship $ */
3
4 FUNCTION get_file_creation_number(p_originator_id varchar2
5 ,p_fin_institution varchar2
6 ,p_override_fcn varchar2)
7 RETURN varchar2 IS
8 --
9 l_new_fcn varchar2(4);
10 l_error varchar2(10);
11 l_var number := 1;
12 l_fcn_exists number := 0;
13 l_max_seq number;
14 l_next_seq number;
15 l_orig_id varchar2(15) := 0;
16 l_fcn_rows number;
17 l_ct_fcn_rows number;
18 l_min_seq number;
19 l_override_fcn number := 0;
20 l_test_fcn varchar2(4);
21 --
22 -- Cursor to see if Originator ID already exists in FCN table
23 --
24 CURSOR originator_exists(p_originator_id varchar2) IS
25 select 1
26 from pay_ca_file_creation_numbers
27 where originator_id = p_originator_id;
28 --
29 -- Cursor to find the max number of rows allowed in FCN table for a particular
30 -- Financial Institution.
31 --
32 CURSOR get_num_fcns_allowed(p_fin_institution varchar2) IS
33 select information_value
34 from pay_ca_legislation_info
35 where information_type = 'CA_DD_FCN_ROWS'
36 and lookup_code = p_fin_institution;
37 --
38 -- Cursor to count number of rows currently in FCN table for a given Originator
39 -- ID
40 --
41 CURSOR count_fcn_rows(p_originator_id varchar2) IS
42 select count(*)
43 from pay_ca_file_creation_numbers
44 where originator_id = p_originator_id;
45 --
46 -- Cursor to find the maximum sequence_number for a particular Originator ID
47 --
48 CURSOR get_max_sequence(l_orig_id varchar2) is
49 select max(sequence_number)
50 from pay_ca_file_creation_numbers
51 where originator_id = l_orig_id;
52 --
53 -- Cursor to find the maximum sequence_number for a particular Originator ID
54 -- where the file_creation_number is composed of digits only
55 --
56 CURSOR digits_only_max_sequence(l_orig_id varchar2) is
57 select max(sequence_number)
58 from pay_ca_file_creation_numbers
59 where originator_id = l_orig_id
60 and file_creation_number =
61 translate(file_creation_number,
62 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz?><~!@#$%^*()_-+=/\|}{][":;.,`''',
63 ' ');
64 --
65 -- Cursor to find the test FCN for a given Financial Institution
66 --
67 CURSOR get_test_fcn(p_fin_institution varchar2) IS
68 select information_value
69 from pay_ca_legislation_info
70 where information_type = 'CA_DD_TEST'
71 and lookup_code = p_fin_institution;
72 --
73 -- Cursor to find the next FCN for a given Originator ID and seqence number
74 --
75 CURSOR get_new_fcn(l_var number, l_orig_id varchar2, l_seq_num number) is
76 select fnd_number.canonical_to_number(file_creation_number) + l_var
77 from pay_ca_file_creation_numbers
78 where originator_id = l_orig_id
79 and sequence_number = l_seq_num;
80
81 -- Cursor to find the existing FCNs for a given Originator ID
82 --
83 CURSOR current_fcns(l_orig_id varchar2, l_fcn varchar2) is
84 select 1
85 from pay_ca_file_creation_numbers
86 where originator_id = l_orig_id
87 and file_creation_number = l_fcn;
88 --
89 -- Cursor to get the next sequence number
90 --
91 CURSOR get_nextval is
92 select pay_ca_file_creation_numbers_s.nextval
93 from dual;
94 --
95 -- Cursor to get the min sequence_number for a given Originator ID
96 --
97 CURSOR get_min_sequence(p_originator_id varchar2) IS
98 select min(sequence_number)
99 from pay_ca_file_creation_numbers
100 where originator_id = p_originator_id;
101 --
102 -- Cursor to find if Override FCN already exists in FCN table
103 --
104 CURSOR override_fcn_exists(p_originator_id varchar2
105 ,p_override_fcn varchar2) is
106 select 1
107 from pay_ca_file_creation_numbers
108 where originator_id = p_originator_id
109 and file_creation_number = p_override_fcn;
110 --
111 -- Procedure to insert and commit a new row in pay_ca_file_creation_numbers
112 --
113 PROCEDURE insert_new_fcn(p_originator_id varchar2
114 ,p_sequence_number number
115 ,p_new_fcn varchar2) is
116 BEGIN
117 --
118 insert into
119 pay_ca_file_creation_numbers
120 (originator_id ,
121 sequence_number,
122 file_creation_number)
123 values (p_originator_id,
124 p_sequence_number,
125 lpad(p_new_fcn,4,'0'));
126 --
127 --commit;
128 end;
129 --
130 -- Procedure to delete and commit oldest FCN in the FCN table for a given
131 -- Originator ID
132 --
133 PROCEDURE delete_oldest_fcn(p_originator_id varchar2
134 ,p_sequence_number number) IS
135 BEGIN
136 --
137 delete from pay_ca_file_creation_numbers
138 where sequence_number = p_sequence_number
139 and originator_id = p_originator_id;
140 --
141 --commit;
142 end;
143 --
144 -- Function that actually gets the next available fcn
145 --
146 FUNCTION get_actual_fcn(p_originator_id varchar2) return varchar2 IS
147 --
148 BEGIN
149 -- Get the max sequence number for a given Originator ID
150 -- where the file creation number is made up from digits
151 -- only
152 open digits_only_max_sequence(p_originator_id);
153 fetch digits_only_max_sequence into l_max_seq;
154
155 if digits_only_max_sequence%FOUND and
156 l_max_seq is not NULL then
157 --
158 -- Get the potential new FCN
159 --
160 open get_new_fcn(l_var, p_originator_id, l_max_seq);
161 fetch get_new_fcn into l_new_fcn;
162 close get_new_fcn;
163 -- dbms_output.put_line('1st new fcn is '||l_new_fcn);
164 --
165 -- Check if the potential FCN already exists
166 --
167 open current_fcns(p_originator_id, l_new_fcn);
168 fetch current_fcns into l_fcn_exists;
169 close current_fcns;
170 -- dbms_output.put_line('l_exists is '||l_fcn_exists);
171 --
172 -- If the potential FCN does already exist then keep adding 1 to the number
173 -- until it does not exist. This will be the new FCN.
174 --
175 if l_fcn_exists = 1 then
176 --
177 loop
178 l_var := l_var + 1;
179 l_fcn_exists := 0;
180 open get_new_fcn(l_var, p_originator_id, l_max_seq);
181 fetch get_new_fcn into l_new_fcn;
182 close get_new_fcn;
183 --
184 -- dbms_output.put_line('lnewfcn is '||l_new_fcn);
185 --
186 open current_fcns(p_originator_id, l_new_fcn);
187 fetch current_fcns into l_fcn_exists;
188 --
189 -- dbms_output.put_line('l_fcn_exists is '||l_fcn_exists);
190 --
191 exit when current_fcns%NOTFOUND;
192 --
193 close current_fcns;
194 end loop;
195 else
196 -- dbms_output.put_line('else new fcn is '||l_new_fcn);
197 null;
198 end if;
199 else
200 l_new_fcn := '0001';
201 end if;
202
203 close digits_only_max_sequence;
204
205 -- dbms_output.put_line('l_new_fcn is '||l_new_fcn);
206 --
207 -- Insert new row into pay_ca_file_creation_numbers, get the
208 -- sequence.nextval first.
209 --
210
211 open get_nextval;
212 fetch get_nextval into l_next_seq;
213 close get_nextval;
214 --
215 insert_new_fcn(p_originator_id
216 ,l_next_seq
217 ,lpad(l_new_fcn,4,'0'));
218 --
219 open get_max_sequence(p_originator_id);
220 fetch get_max_sequence into l_max_seq;
221 close get_max_sequence;
222 --
223 select file_creation_number
224 into l_new_fcn
225 from pay_ca_file_creation_numbers
226 where sequence_number = l_max_seq
227 and originator_id = p_originator_id;
228 --
229 RETURN l_new_fcn;
230 END;
231 --
232 BEGIN -- Main function get_file_creation_number
233 --
234 -- Does the Originator ID passed in already exist in fcn table? 1 = Exists
235 -- 0 = Not exists
236 --
237 open originator_exists(p_originator_id);
238 fetch originator_exists into l_orig_id;
239 close originator_exists;
240 --
241 IF l_orig_id = 1 then
242 --
243 -- Has an Override FCN been entered?
244 --
245 IF p_override_fcn is null THEN
246 --
247 -- Check if number of rows in the FCN table is at the max for a particular
248 -- Financial Institution.
249 -- l_fcn_rows = number allowed, l_ct_fcn_rows = actual number of rows
250 --
251 open get_num_fcns_allowed(p_fin_institution);
252 fetch get_num_fcns_allowed into l_fcn_rows;
253 close get_num_fcns_allowed;
254 --
255 open count_fcn_rows(p_originator_id);
256 fetch count_fcn_rows into l_ct_fcn_rows;
257 close count_fcn_rows;
258 --
259 IF l_ct_fcn_rows < l_fcn_rows THEN
260 --
261 -- Insert the next row as normal
262 --
263 l_new_fcn := get_actual_fcn(p_originator_id);
264 --
265 RETURN l_new_fcn;
266 --
267 ELSE -- number of rows in FCN table is => than rows allowed, delete
268 -- the row with the min sequence_number for the given Originator ID
269 -- then insert the new row.
270 --
271 open get_min_sequence(p_originator_id);
272 fetch get_min_sequence into l_min_seq;
273 close get_min_sequence;
274 --
275 delete_oldest_fcn(p_originator_id, l_min_seq);
276 --
277 -- NEED TO INSERT ROW HERE
278 --
279 l_new_fcn := get_actual_fcn(p_originator_id);
280 --
281 RETURN l_new_fcn;
282 --
283 END IF;
284 --
285 ELSE -- Override FCN is not null
286 --
287 -- Is the Override FCN the test FCN for a given fin institution?
288 --
289 open get_test_fcn(p_fin_institution);
290 fetch get_test_fcn into l_test_fcn;
291 close get_test_fcn;
292 --
293 IF p_override_fcn = l_test_fcn THEN
294 --
295 l_new_fcn := p_override_fcn;
296 --
297 RETURN l_new_fcn;
298 --
299 -- ELSIF
300 --
301 -- If the Override FCN is not equal to the TEST FCN, and it is not
302 -- between 0001 and 9999 then an invalid valud has been passed in
303 -- for the Override FCN, so terminate the process and return a
304 -- message.
305 --
306 -- (p_override_fcn <> l_test_fcn
307 -- and (p_override_fcn < '0001'
308 -- or p_override_fcn > '9999')) THEN
309 --
310 -- NEED TO RAISE AN ERROR THAT CAN BE RAISED IN FAST FORMULA
311 --
312 -- l_new_fcn := '1.2';
313 --
314 -- RETURN l_new_fcn;
315 --
316 ELSE
317 --
318 -- Is the Override FCN already in the FCN table?
319 --
320 -- dbms_output.put_line('1st l_override_fcn is '||l_override_fcn);
321 --
322 open override_fcn_exists(p_originator_id, p_override_fcn);
323 fetch override_fcn_exists into l_override_fcn;
324 close override_fcn_exists;
325 -- select 1
326 -- into l_override_fcn
327 -- from pay_ca_file_creation_numbers
328 -- where originator_id = p_originator_id
329 -- and file_creation_number = p_override_fcn;
330 --
331 -- dbms_output.put_line('l_override_fcn is '||l_override_fcn);
332 --
333 IF l_override_fcn = 1 THEN -- i.e does exist
334 --
335 -- NEED TO RAISE AN ERROR THAT CAN BE RAISED IN FAST FORMULA
336 hr_utility.trace('magtape must be terminated as FCN already exists');
337 -- dbms_output.put_line('magtape must be terminated as FCN already exists');
338 l_new_fcn := 1.1;
339 RETURN l_new_fcn;
340 ELSE -- Override does not exist on FCN table
341 --
342 -- Check if number of rows in the FCN table is at the max for a particular
343 -- Financial Institution.
344 -- l_fcn_rows = number allowed, l_ct_fcn_rows = actual number of rows
345 --
346 open get_num_fcns_allowed(p_fin_institution);
347 fetch get_num_fcns_allowed into l_fcn_rows;
348 close get_num_fcns_allowed;
349 --
350 open count_fcn_rows(p_originator_id);
351 fetch count_fcn_rows into l_ct_fcn_rows;
352 close count_fcn_rows;
353 --
354 IF l_ct_fcn_rows < l_fcn_rows THEN
355 --
356 -- Insert the next row as normal
357 --
358 --l_new_fcn := get_actual_fcn(p_originator_id);
359 open get_nextval;
360 fetch get_nextval into l_next_seq;
361 close get_nextval;
362 --
363 insert_new_fcn(p_originator_id
364 ,l_next_seq
365 ,p_override_fcn);
366 --
367 open get_max_sequence(p_originator_id);
368 fetch get_max_sequence into l_max_seq;
369 close get_max_sequence;
370 --
371 select file_creation_number
372 into l_new_fcn
373 from pay_ca_file_creation_numbers
374 where sequence_number = l_max_seq
375 and originator_id = p_originator_id;
376 --
377 --
378 RETURN l_new_fcn;
379 --
380 ELSE -- Number of rows in FCN table is => than rows allowed, delete
381 -- the row with the min sequence_number for the given Originator ID
382 -- then insert the new row.
383 --
384 open get_min_sequence(p_originator_id);
388 delete_oldest_fcn(p_originator_id, l_min_seq);
385 fetch get_min_sequence into l_min_seq;
386 close get_min_sequence;
387 --
389 --
390 --l_new_fcn := get_actual_fcn(p_originator_id);
391 open get_nextval;
392 fetch get_nextval into l_next_seq;
393 close get_nextval;
394 --
395 insert_new_fcn(p_originator_id
396 ,l_next_seq
397 ,p_override_fcn);
398 --
399 open get_max_sequence(p_originator_id);
400 fetch get_max_sequence into l_max_seq;
401 close get_max_sequence;
402 --
403 select file_creation_number
404 into l_new_fcn
405 from pay_ca_file_creation_numbers
406 where sequence_number = l_max_seq
407 and originator_id = p_originator_id;
408 --
409 RETURN l_new_fcn;
410 --
411 END IF; -- Num of rows in FCN table < or => row allowed
412 --
413 END IF; -- Override FCN is in the table already??
414 --
415 END IF; -- Is override FCN the test fcn?
416 --
417 END IF; -- Overrid is/not null
418 --
419 ELSE -- originator id not in FCN table
420 --
421 -- Has an Override FCN been entered?
422 --
423 IF p_override_fcn is null THEN
424 --
425 open get_nextval;
426 fetch get_nextval into l_next_seq;
427 close get_nextval;
428 --
429 insert_new_fcn(p_originator_id
430 ,l_next_seq
431 ,'0001');
432 --
433 open get_max_sequence(p_originator_id);
434 fetch get_max_sequence into l_max_seq;
435 close get_max_sequence;
436 --
437 select file_creation_number
438 into l_new_fcn
439 from pay_ca_file_creation_numbers
440 where sequence_number = l_max_seq
441 and originator_id = p_originator_id;
442 --
443 RETURN l_new_fcn;
444 --
445 ELSE -- Override FCN is not null NB. as it is a new Originator Id in the
446 -- FCN table then don't need to check if the override FCN is already
447 -- in the FCN, just insert first row with the override FCN value.
448 --
449 -- Is the Override FCN the test FCN for a given fin institution?
450 --
451 open get_test_fcn(p_fin_institution);
452 fetch get_test_fcn into l_test_fcn;
453 close get_test_fcn;
454 --
455 IF p_override_fcn = l_test_fcn THEN
456 --
457 l_new_fcn := p_override_fcn;
458 --
459 RETURN l_new_fcn;
460 --
461 -- ELSIF
462 --
463 -- If the Override FCN is not equal to the TEST FCN, and it is not
464 -- between 0001 and 9999 then an invalid valud has been passed in
465 -- for the Override FCN, so terminate the process and return a
466 -- message.
467 --
468 -- (p_override_fcn <> l_test_fcn
469 -- and (p_override_fcn < '0001'
470 -- or p_override_fcn > '9999')) THEN
471 --
472 -- NEED TO RAISE AN ERROR THAT CAN BE RAISED IN FAST FORMULA
473 --
474 -- l_new_fcn := '1.2';
475 --
476 -- RETURN l_new_fcn;
477 --
478 ELSE
479 --
480 open get_nextval;
481 fetch get_nextval into l_next_seq;
482 close get_nextval;
483 --
484 insert_new_fcn(p_originator_id
485 ,l_next_seq
486 ,p_override_fcn);
487 --
488 open get_max_sequence(p_originator_id);
489 fetch get_max_sequence into l_max_seq;
490 close get_max_sequence;
491 --
492 select file_creation_number
493 into l_new_fcn
494 from pay_ca_file_creation_numbers
495 where sequence_number = l_max_seq
496 and originator_id = p_originator_id;
497 --
498 RETURN l_new_fcn;
499 --
500 END IF; -- Is override FCN the test fcn?
501 --
502 END IF; -- Is Override FCN null/not null?
503 END IF;
504 --
505 END get_file_creation_number;
506
507
508 /* New Function to generate the FCN value, fix for bug#2790271 */
509 FUNCTION get_dd_file_creation_number(p_org_payment_method_id number,
510 p_fin_institution varchar2,
511 p_override_fcn varchar2,
512 p_pact_id number,
513 p_business_group_id number)
514 RETURN varchar2 IS
515 l_new_fcn varchar2(4);
516 l_error varchar2(10);
517 l_var number := 1;
518 l_fcn_exists number := 0;
519 l_orig_id varchar2(15) := 0;
520 l_override_fcn number := 0;
521 l_test_fcn varchar2(4);
522 l_max_pact_id number;
523 l_fcn_rows number;
524
525 --
526 -- Cursor to find the max number of rows allowed in FCN table
527 -- for a particular Financial Institution.
528 --
529 CURSOR get_num_fcns_allowed(cp_fin_institution varchar2) IS
530 select TO_NUMBER(information_value)
531 from pay_ca_legislation_info
532 where information_type = 'CA_DD_FCN_ROWS'
533 and lookup_code = cp_fin_institution;
534
535
539 --
536 -- Cursor to find the maximum payroll_action_id for a particular
537 -- Payment method (Originator ID is unique within each payment method)
538 -- where the file_creation_number is composed of digits only
540 CURSOR digits_only_max_sequence(cp_org_pmt_method_id number,cp_bg_id number) is
541 select max(payroll_action_id)
542 from pay_payroll_actions
543 where business_group_id = cp_bg_id
544 and action_type = 'M'
545 and org_payment_method_id = cp_org_pmt_method_id
546 and attribute1 is not null;
547
548
549 -- Cursor to find the next FCN for a given Payment Method ID ,
550 -- max payroll_action_id and business_group_id
551 --
552 CURSOR get_new_fcn(l_var number, cp_org_pmt_method_id number,
553 cp_max_pact_id number, cp_bg_id number) is
554 select translate(attribute1,
555 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz?><~!@#$%^*()_-+=/\|}{][":;.,`''',' ') + l_var
556 from pay_payroll_actions
557 where business_group_id = cp_bg_id
558 and action_type = 'M'
559 and org_payment_method_id = cp_org_pmt_method_id
560 and payroll_action_id = cp_max_pact_id;
561
562
563 -- Cursor to find whether the newly generated FCN exists
564 -- in the last N records for a given Payment Method ID
565 --
566 CURSOR current_fcns(cp_org_pmt_method_id number, l_fcn varchar2,
567 cp_bg_id number, cp_no_fcn_rows number) is
568 select 1
569 from pay_payroll_actions
570 where business_group_id = cp_bg_id
571 and action_type = 'M'
572 and org_payment_method_id = cp_org_pmt_method_id
573 and attribute1 = l_fcn
574 and rownum <= cp_no_fcn_rows
575 order by payroll_action_id desc;
576
577
578 -- Cursor to find if Override FCN already exists in last N records
579 --
580 CURSOR override_fcn_exists(cp_org_pmt_method_id number
581 ,p_override_fcn varchar2
582 ,cp_bg_id number
583 ,cp_no_fcn_rows number) is
584 select 1
585 from pay_payroll_actions
586 where business_group_id = cp_bg_id
587 and action_type = 'M'
588 and org_payment_method_id = cp_org_pmt_method_id
589 and attribute1 = p_override_fcn
590 and rownum <= cp_no_fcn_rows
591 order by payroll_action_id desc;
592
593
594 -- Cursor to check whether the Override FCN is a test FCN or not
595 CURSOR get_test_fcn(p_fin_institution varchar2) IS
596 select information_value
597 from pay_ca_legislation_info
598 where information_type = 'CA_DD_TEST'
599 and lookup_code = p_fin_institution;
600
601 -- Procedure to stamp the FCN number in pay_payroll_actions table
602 --
603 PROCEDURE insert_new_fcn(p_org_pmt_method_id number
604 ,p_new_fcn varchar2
605 ,p_pact_id number) is
606 BEGIN
607 --
608
609 update pay_payroll_actions
610 set attribute1 = lpad(p_new_fcn,4,'0')
611 where payroll_action_id = p_pact_id
612 and org_payment_method_id = p_org_pmt_method_id;
613 --
614 --commit;
615 end;
616
617
618 -- Function that actually gets the next available fcn
619 --
620 FUNCTION get_actual_dd_fcn(p_org_pmt_method_id number,
621 p_bg_id number,
622 p_no_fcn_rows number) return varchar2 IS
623
624 BEGIN
625 -- Get the max payroll_action_id for a given Payment_Method_ID and
626 -- Business_group_id where the file creation number is made up from
627 -- digits only
628
629 open digits_only_max_sequence(p_org_pmt_method_id, p_bg_id);
630 fetch digits_only_max_sequence into l_max_pact_id;
631 hr_utility.trace('l_max_pact_id: '||to_char(l_max_pact_id));
632
633 if digits_only_max_sequence%FOUND and
634 l_max_pact_id is not NULL then
635
636 -- Get the potential new FCN
637
638 hr_utility.trace('Digits_only_max_sequence found ');
639 open get_new_fcn(l_var, p_org_pmt_method_id, l_max_pact_id,
640 p_bg_id);
641 fetch get_new_fcn into l_new_fcn;
642 close get_new_fcn;
643 l_new_fcn := lpad(l_new_fcn,4,'0');
644 hr_utility.trace('1st new fcn l_new_fcn: '||l_new_fcn);
645 -- dbms_output.put_line('1st new fcn is '||l_new_fcn);
646
647 -- Check if the potential FCN already exists in last N records
648
649 open current_fcns(p_org_pmt_method_id, l_new_fcn,
650 p_bg_id,p_no_fcn_rows);
651 fetch current_fcns into l_fcn_exists;
652 close current_fcns;
653 hr_utility.trace('l_fcn_exists: '||to_char(l_fcn_exists));
654 -- dbms_output.put_line('l_exists is '||l_fcn_exists);
655 --
656 -- If the potential FCN does already exist in last N records
657 -- then keep adding 1 to the number
658 -- until it does not exist. This will be the new FCN.
659 --
660 if l_fcn_exists = 1 then
661
662 hr_utility.trace('l_fcn_exists is satisfied');
663 loop
664 l_var := l_var + 1;
665 l_fcn_exists := 0;
669 close get_new_fcn;
666 open get_new_fcn(l_var, p_org_pmt_method_id,
667 l_max_pact_id, p_bg_id);
668 fetch get_new_fcn into l_new_fcn;
670 l_new_fcn := lpad(l_new_fcn,4,'0');
671
672 hr_utility.trace('In the loop l_new_fcn: '||l_new_fcn);
673 -- dbms_output.put_line('lnewfcn is
674 -- '||l_new_fcn);
675
676 open current_fcns(p_org_pmt_method_id, l_new_fcn,
677 p_bg_id,p_no_fcn_rows);
678 fetch current_fcns into l_fcn_exists;
679
680 -- dbms_output.put_line('l_fcn_exists is
681 -- '||l_fcn_exists);
682
683 hr_utility.trace('In the loop l_fcn_exists: '||l_fcn_exists);
684 exit when current_fcns%NOTFOUND;
685
686 close current_fcns;
687 end loop;
688
689 else
690 -- dbms_output.put_line('else new fcn is
691 -- '||l_new_fcn);
692 null;
693 end if;
694 else
695
696 l_new_fcn := '0001';
697
698 end if;
699
700 close digits_only_max_sequence;
701
702 -- dbms_output.put_line('l_new_fcn is '||l_new_fcn);
703 --
704 -- Stamp New FCN record in pay_payroll_actions table
705
706 l_new_fcn := lpad(l_new_fcn,4,'0');
707 hr_utility.trace('Final FCN Generated, before Update the l_new_fcn:'||l_new_fcn);
708 insert_new_fcn(p_org_pmt_method_id,l_new_fcn,p_pact_id);
709
710 RETURN l_new_fcn;
711 END; --get_actual_dd_fcn
712
713
714 BEGIN -- Main function get_dd_file_creation_number
715 /* hr_utility.trace_on('Y','TESTFCN'); */
716 hr_utility.trace('Start of Get_dd_file_Creation_Number function');
717 --
718 -- Does any DD exist for the given Payment Method in
719 -- pay_payroll_actions table?
720 -- l_max_pact_id is not null then Exists
721 -- l_max_pact_id is null then Not exists
722
723 hr_utility.trace('p_business_group_id :'||to_char(p_business_group_id));
724 hr_utility.trace('p_org_payment_method_id :'||to_char(p_org_payment_method_id));
725 hr_utility.trace('p_fin_institution :'||p_fin_institution);
726 hr_utility.trace('p_override_fcn : '||p_override_fcn);
727 hr_utility.trace('p_pact_id :'||to_char(p_pact_id));
728
729 open digits_only_max_sequence(p_org_payment_method_id,
730 p_business_group_id);
731 fetch digits_only_max_sequence into l_max_pact_id;
732 close digits_only_max_sequence;
733
734 hr_utility.trace('l_max_pact_id :'||to_char(l_max_pact_id));
735 -- Check how many max FCN rows allowed for a particular
736 -- Financial Institution.
737 --
738 open get_num_fcns_allowed(p_fin_institution);
739 fetch get_num_fcns_allowed into l_fcn_rows;
740
741 if get_num_fcns_allowed%NOTFOUND then
742 l_fcn_rows := 50;
743 end if;
744 close get_num_fcns_allowed;
745 hr_utility.trace('l_fcn_rows :'||to_char(l_fcn_rows));
746 --
747
748 hr_utility.trace('p_override_fcn : '||p_override_fcn);
749 /* check if there exists atleast one DD for the given
750 payment method, Similar to Originator Id exists or not */
751
752 IF l_max_pact_id is not null then
753
754 -- Has an Override FCN been entered?
755 IF p_override_fcn is null THEN
756
757 l_new_fcn := get_actual_dd_fcn(p_org_payment_method_id,
758 p_business_group_id,
759 l_fcn_rows);
760
761 hr_utility.trace('p_override_fcn is null');
762 RETURN l_new_fcn;
763
764
765 ELSE -- Override FCN is not null
766
767 -- Is the Override FCN the test FCN for a given fin
768 -- institution?
769
770 open get_test_fcn(p_fin_institution);
771 fetch get_test_fcn into l_test_fcn;
772 close get_test_fcn;
773
774 IF p_override_fcn = l_test_fcn THEN
775
776 l_new_fcn := p_override_fcn;
777
778 /* Update Pay_Payroll_actions table with FCN value */
779
780 hr_utility.trace('l_new_fcn final:'||l_new_fcn);
781 insert_new_fcn(p_org_payment_method_id,l_new_fcn,p_pact_id);
782 RETURN l_new_fcn;
783
784 ELSE
785
786 -- Is the Override FCN already in the FCN table?
787 -- dbms_output.put_line('1st l_override_fcn is
788 -- '||l_override_fcn);
789
790 open override_fcn_exists(p_org_payment_method_id,
791 p_override_fcn,
792 p_business_group_id, l_fcn_rows);
793 fetch override_fcn_exists into l_override_fcn;
797 -- dbms_output.put_line('l_override_fcn is
794 close override_fcn_exists;
795
796 hr_utility.trace('l_override_fcn :'||l_override_fcn);
798 -- '||l_override_fcn);
799
800 IF l_override_fcn = 1 THEN -- i.e does exist
801
802 -- NEED TO RAISE AN ERROR THAT CAN BE RAISED IN FAST
803 -- FORMULA and XML Magtape formats
804 hr_utility.trace('magtape must be terminated as FCN already exists');
805 -- dbms_output.put_line('magtape must be terminated as
806 -- FCN already exists');
807 l_new_fcn := '1.1';
808 RETURN l_new_fcn;
809
810 ELSE -- Override does not exist on FCN table
811
812 -- Insert the next row as normal
813 insert_new_fcn(p_org_payment_method_id,p_override_fcn,
814 p_pact_id);
815
816 RETURN p_override_fcn;
817
818 END IF; -- Override FCN is in the table already??
819
820 END IF; -- Is override FCN the test fcn?
821
822 END IF; -- Override FCN is/not null
823
824 ELSE -- originator id not in Payroll Actions table
825
826 -- Has an Override FCN been entered?
827
828 IF p_override_fcn is null THEN
829
830 l_new_fcn := '0001';
831 insert_new_fcn(p_org_payment_method_id ,l_new_fcn,p_pact_id);
832 hr_utility.trace('l_new_fcn final:'||l_new_fcn);
833
834 RETURN l_new_fcn;
835
836 ELSE -- Override FCN is not null NB. as it is a new
837 -- Originator Id in the FCN table then don't
838 -- need to check if the override FCN is already
839 -- in the FCN, just insert first row with the override
840 -- FCN value.
841
842 -- Is the Override FCN the test FCN for a given fin
843 -- institution?
844
845 open get_test_fcn(p_fin_institution);
846 fetch get_test_fcn into l_test_fcn;
847 close get_test_fcn;
848
849 IF p_override_fcn = l_test_fcn THEN
850
851 l_new_fcn := p_override_fcn;
852
853 /* Update Pay_Payroll_actions table with
854 FCN value */
855 insert_new_fcn(p_org_payment_method_id,l_new_fcn,
856 p_pact_id);
857
858 hr_utility.trace('l_new_fcn final:'||l_new_fcn);
859 RETURN l_new_fcn;
860
861 ELSE
862
863 insert_new_fcn(p_org_payment_method_id,p_override_fcn,
864 p_pact_id);
865 hr_utility.trace('l_new_fcn final:'||l_new_fcn);
866 RETURN p_override_fcn;
867
868 END IF; -- Is override FCN the test fcn?
869
870 END IF; -- Is Override FCN null/not null?
871 END IF;
872
873 /* hr_utility.trace_off; */
874 END get_dd_file_creation_number;
875
876 FUNCTION convert_uppercase(p_input_string varchar2)
877 RETURN varchar2 IS
878 --
879 l_output_string varchar2(2000);
880
881 -- converts the french accented characters to American English
882 -- in uppercase, used for direct deposit mag tape data
883 cursor c_uppercase(cp_input_string varchar2) is
884 select
885 replace(
886 replace(
887 replace(
888 replace(
889 replace(
890 replace(
891 replace(
892 replace(
893 replace(
894 replace(
895 replace(
896 replace(
897 replace(
898 replace(
899 replace(convert(upper(cp_input_string),'UTF8'),
900 utl_raw.cast_to_varchar2(hextoraw('C380')),'A'
901 ),
902 utl_raw.cast_to_varchar2(hextoraw('C38A')),'E'
903 ),
904 utl_raw.cast_to_varchar2(hextoraw('C387')),'C'
905 ),
906 utl_raw.cast_to_varchar2(hextoraw('C389')),'E'
907 ),
908 utl_raw.cast_to_varchar2(hextoraw('C39C')),'U'
909 ),
910 utl_raw.cast_to_varchar2(hextoraw('C399')),'U'
911 ),
912 utl_raw.cast_to_varchar2(hextoraw('C39B')),'U'
913 ),
914 utl_raw.cast_to_varchar2(hextoraw('C394')),'O'
915 ),
916 utl_raw.cast_to_varchar2(hextoraw('C38F')),'I'
917 ),
918 utl_raw.cast_to_varchar2(hextoraw('C38E')),'I'
919 ),
920 utl_raw.cast_to_varchar2(hextoraw('C388')),'E'
921 ),
922 utl_raw.cast_to_varchar2(hextoraw('C38B')),'E'
923 ),
924 utl_raw.cast_to_varchar2(hextoraw('C382')),'A'
925 ),
926 utl_raw.cast_to_varchar2(hextoraw('C592')),'OE'
927 ),
928 utl_raw.cast_to_varchar2(hextoraw('C386')),'AE'
929 )
930 from dual;
931
932
933 begin
934
938 l_output_string := p_input_string;
935 open c_uppercase(p_input_string);
936 fetch c_uppercase into l_output_string;
937 if c_uppercase%NOTFOUND then
939 end if;
940 close c_uppercase;
941
942 return l_output_string;
943
944 end convert_uppercase;
945
946
947 END pay_ca_direct_deposit_pkg;
[Home] [Help]