[Home] [Help]
PACKAGE BODY: APPS.PAY_BANK_BRANCHES_PKG
Source
1 package body pay_bank_branches_pkg as
2 /* $Header: pybbr01t.pkb 115.10 2004/08/20 10:50:23 arashid noship $ */
3 g_gb_sort_code_len number := 6; -- GB sort code length.
4 g_gb_accno_len number := 8; -- GB account number length.
5 -------------------------< CHK_PRIMARY_KEY_ARGS >--------------------------
6 --
7 -- Name
8 -- CHK_PRIMARY_KEY_ARGS
9 --
10 procedure chk_primary_key_args
11 (p_legislation_code in varchar2
12 ,p_branch_code in varchar2
13 ) is
14 begin
15 --
16 -- p_branch_code and p_legislation_code must both be not NULL.
17 --
18 hr_api.mandatory_arg_error
19 (p_api_name => 'chk_primary_key_args'
20 ,p_argument => 'P_BRANCH_CODE'
21 ,p_argument_value => p_branch_code
22 );
23 --
24 hr_api.mandatory_arg_error
25 (p_api_name => 'chk_primary_key_args'
26 ,p_argument => 'P_LEGISLATION_CODE'
27 ,p_argument_value => p_legislation_code
28 );
29 end chk_primary_key_args;
30 ------------------------------< INSERT_ROW >--------------------------------
31 procedure insert_row
32 (p_branch_code in varchar2
33 ,p_legislation_code in varchar2
34 ,p_bank_code in varchar2
35 ,p_branch in varchar2
36 ,p_long_branch in varchar2 default null
37 ,p_extra_information1 in varchar2 default null
38 ,p_extra_information2 in varchar2 default null
39 ,p_extra_information3 in varchar2 default null
40 ,p_extra_information4 in varchar2 default null
41 ,p_extra_information5 in varchar2 default null
42 ,p_enabled_flag in varchar2 default 'Y'
43 ,p_start_date_active in date default hr_api.g_sot
44 ,p_end_date_active in date default hr_api.g_eot
45 ) is
46 l_dummy varchar2(32);
47 --
48 cursor csr_leg_code(p_legislation_code in varchar2) is
49 select null
50 from fnd_territories
51 where territory_code = p_legislation_code;
52 begin
53 --
54 -- Validate LEGISLATION_CODE.
55 --
56 open csr_leg_code(p_legislation_code => p_legislation_code);
57 fetch csr_leg_code into l_dummy;
58 if csr_leg_code%notfound then
59 close csr_leg_code;
60 fnd_message.set_name('PAY', 'PAY_50070_INVALID_LEG_CODE');
61 fnd_message.raise_error;
62 end if;
63 close csr_leg_code;
64
65 --
66 -- ENABLED_FLAG must be a code for lookup type YES_NO.
67 --
68 if p_enabled_flag is not null and
69 hr_api.not_exists_in_hr_lookups
70 (p_effective_date => trunc(sysdate)
71 ,p_lookup_type => 'YES_NO'
72 ,p_lookup_code => p_enabled_flag
73 )
74 then
75 fnd_message.set_name('PAY', 'HR_52966_INVALID_LOOKUP');
76 fnd_message.set_token('LOOKUP_TYPE', 'YES_NO');
77 fnd_message.set_token('COLUMN', 'P_ENABLED_FLAG');
78 fnd_message.raise_error;
79 end if;
80
81 insert into pay_bank_branches
82 (branch_code
83 ,legislation_code
84 ,bank_code
85 ,branch
86 ,long_branch
87 ,extra_information1
88 ,extra_information2
89 ,extra_information3
90 ,extra_information4
91 ,extra_information5
92 ,enabled_flag
93 ,start_date_active
94 ,end_date_active
95 )
96 values
97 (p_branch_code
98 ,p_legislation_code
99 ,p_bank_code
100 ,p_branch
101 ,p_long_branch
102 ,p_extra_information1
103 ,p_extra_information2
104 ,p_extra_information3
105 ,p_extra_information4
106 ,p_extra_information5
107 ,p_enabled_flag
108 ,p_start_date_active
109 ,p_end_date_active
110 );
111 end insert_row;
112 -------------------------------< LOCK_ROW >--------------------------------
113 procedure lock_row
114 (p_branch_code in varchar2
115 ,p_legislation_code in varchar2
116 ) is
117 cursor c1 is
118 select *
119 from pay_bank_branches
120 where legislation_code = p_legislation_code
121 and branch_code = p_branch_code
122 for update nowait
123 ;
124 --
125 row1 c1%rowtype;
126 l_debug boolean := hr_utility.debug_enabled;
127 begin
128 if l_debug then
129 hr_utility.set_location('Entering:pay_bank_branches.lock_row', 0);
130 end if;
131 --
132 chk_primary_key_args
133 (p_legislation_code => p_legislation_code
134 ,p_branch_code => p_branch_code
135 );
136 --
137 open c1;
138 fetch c1 into row1;
139 if c1%notfound then
140 if l_debug then
141 hr_utility.set_location('Leaving:pay_bank_branches.lock_row', 10);
142 end if;
143 --
144 close c1;
145 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
146 fnd_message.raise_error;
147 end if;
148 close c1;
149 --
150 if l_debug then
151 hr_utility.set_location('Leaving:pay_bank_branches.lock_row', 20);
152 end if;
153 end lock_row;
154 ------------------------------< UPDATE_ROW >-------------------------------
155 procedure update_row
156 (p_branch_code in varchar2
157 ,p_legislation_code in varchar2
158 ,p_bank_code in varchar2 default hr_api.g_varchar2
159 ,p_branch in varchar2 default hr_api.g_varchar2
160 ,p_long_branch in varchar2 default hr_api.g_varchar2
161 ,p_extra_information1 in varchar2 default hr_api.g_varchar2
162 ,p_extra_information2 in varchar2 default hr_api.g_varchar2
163 ,p_extra_information3 in varchar2 default hr_api.g_varchar2
164 ,p_extra_information4 in varchar2 default hr_api.g_varchar2
165 ,p_extra_information5 in varchar2 default hr_api.g_varchar2
166 ,p_enabled_flag in varchar2 default hr_api.g_varchar2
167 ,p_start_date_active in date default hr_api.g_date
168 ,p_end_date_active in date default hr_api.g_date
169 ) is
170 l_legislation_code pay_bank_branches.legislation_code%type;
171 l_branch_code pay_bank_branches.branch_code%type;
172 l_bank_code pay_bank_branches.bank_code%type;
173 l_branch pay_bank_branches.branch%type;
174 l_long_branch pay_bank_branches.long_branch%type;
175 l_extra_information1 pay_bank_branches.extra_information1%type;
176 l_extra_information2 pay_bank_branches.extra_information2%type;
177 l_extra_information3 pay_bank_branches.extra_information3%type;
178 l_extra_information4 pay_bank_branches.extra_information4%type;
179 l_extra_information5 pay_bank_branches.extra_information5%type;
180 l_enabled_flag pay_bank_branches.enabled_flag%type;
181 l_start_date_active pay_bank_branches.start_date_active%type;
182 l_end_date_active pay_bank_branches.end_date_active%type;
183 l_debug boolean := hr_utility.debug_enabled;
184 cursor c1 is
185 select legislation_code
186 , branch_code
187 , bank_code
188 , branch
189 , long_branch
190 , extra_information1
191 , extra_information2
192 , extra_information3
193 , extra_information4
194 , extra_information5
195 , enabled_flag
196 , start_date_active
197 , end_date_active
198 from pay_bank_branches
199 where branch_code = p_branch_code
200 and legislation_code = p_legislation_code
201 ;
202 --
203 procedure conv(nval in varchar2, oval in out nocopy varchar2) is
204 begin
205 if nval <> hr_api.g_varchar2 then
206 oval := nval;
207 end if;
208 end conv;
209 --
210 procedure conv(nval in date, oval in out nocopy date) is
211 begin
212 if nval <> hr_api.g_date then
213 oval := nval;
214 end if;
215 end conv;
216 begin
217 if l_debug then
218 hr_utility.set_location('Entering:pay_bank_branches.update_row', 0);
219 end if;
220 --
221 chk_primary_key_args
222 (p_legislation_code => p_legislation_code
223 ,p_branch_code => p_branch_code
224 );
225 --
226 open c1;
227 fetch c1
228 into l_legislation_code
229 , l_branch_code
230 , l_bank_code
231 , l_branch
232 , l_long_branch
233 , l_extra_information1
234 , l_extra_information2
235 , l_extra_information3
236 , l_extra_information4
237 , l_extra_information5
238 , l_enabled_flag
239 , l_start_date_active
240 , l_end_date_active
241 ;
242 if c1%notfound then
243 if l_debug then
244 hr_utility.set_location('Leaving:pay_bank_branches.update_row', 10);
245 end if;
246 --
247 close c1;
248 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
249 fnd_message.raise_error;
250 end if;
251 close c1;
252 --
253 conv(p_branch_code, l_branch_code);
254 conv(p_bank_code, l_bank_code);
255 conv(p_branch, l_branch);
256 conv(p_long_branch, l_long_branch);
257 conv(p_extra_information1, l_extra_information1);
258 conv(p_extra_information2, l_extra_information2);
259 conv(p_extra_information3, l_extra_information3);
260 conv(p_extra_information4, l_extra_information4);
261 conv(p_extra_information5, l_extra_information5);
262 conv(p_enabled_flag, l_enabled_flag);
263 conv(p_start_date_active, l_start_date_active);
264 conv(p_end_date_active, l_end_date_active);
265
266 --
267 -- ENABLED_FLAG must be a code for lookup type YES_NO.
268 --
269 if l_enabled_flag is not null and
270 hr_api.not_exists_in_hr_lookups
271 (p_effective_date => trunc(sysdate)
272 ,p_lookup_type => 'YES_NO'
273 ,p_lookup_code => l_enabled_flag
274 )
275 then
276 fnd_message.set_name('PAY', 'HR_52966_INVALID_LOOKUP');
277 fnd_message.set_token('LOOKUP_TYPE', 'YES_NO');
278 fnd_message.set_token('COLUMN', 'P_ENABLED_FLAG');
279 fnd_message.raise_error;
280 end if;
281
282 update pay_bank_branches
283 set bank_code = l_bank_code
284 , branch = l_branch
285 , long_branch = l_long_branch
286 , extra_information1 = l_extra_information1
287 , extra_information2 = l_extra_information2
288 , extra_information3 = l_extra_information3
289 , extra_information4 = l_extra_information4
290 , extra_information5 = l_extra_information5
291 , enabled_flag = l_enabled_flag
292 , start_date_active = l_start_date_active
293 , end_date_active = l_end_date_active
294 where branch_code = l_branch_code
295 and legislation_code = l_legislation_code
296 ;
297 --
298 if l_debug then
299 hr_utility.set_location('Leaving:pay_bank_branches.update_row', 20);
300 end if;
301 end update_row;
302 ------------------------------< DELETE_ROW >-------------------------------
303 procedure delete_row
304 (p_branch_code in varchar2
305 ,p_legislation_code in varchar2
306 ) is
307 l_debug boolean := hr_utility.debug_enabled;
308 begin
309 if l_debug then
310 hr_utility.set_location('Entering:pay_bank_branches.delete_row', 0);
311 end if;
312 --
313 chk_primary_key_args
314 (p_legislation_code => p_legislation_code
315 ,p_branch_code => p_branch_code
316 );
317 --
318 if l_debug then
319 hr_utility.set_location('pay_bank_branches.delete_row', 10);
320 end if;
321 --
322 delete
323 from pay_bank_branches
324 where legislation_code = p_legislation_code
325 and branch_code = p_branch_code
326 ;
327 --
328 if l_debug then
329 hr_utility.set_location('Leaving:pay_bank_branches.delete_row', 20);
330 end if;
331 end delete_row;
332 --------------------------< VALIDATE_GB_VALUES >--------------------------
333 --
334 -- Name
335 -- VALIDATE_GB_VALUES
336 --
337 -- Description
338 -- Carries out GB-specific validation of the bank values.
339 -- Only checks p_branch if p_insert is false.
340 --
341 procedure validate_gb_values
342 (p_sort_code in out nocopy varchar2
343 ,p_branch in varchar2
344 ,p_bank_code in varchar2
345 ,p_insert in boolean
346 ) is
347 l_sort_code_len number := g_gb_sort_code_len;
348 l_branch_len number := 35;
349 l_bs_acct_len number := 18;
350 l_proc varchar2(100) := 'pay_bank_branches_pkg.validate_gb_values';
351 l_temp_string varchar2(100);
352 l_debug boolean := hr_utility.debug_enabled;
353 begin
354 if l_debug then
355 hr_utility.set_location('Entering:' || l_proc, 0);
356 end if;
357 --
358 -- SORT_CODE must be 6 byte number string, left-padded with zeroes if
359 -- necessary. SORT_CODE is part of the primary key therefore only
360 -- validate upon insert.
361 --
362 if p_insert then
363 --
364 -- Length check.
365 --
366 if length(p_sort_code) > l_sort_code_len then
367 fnd_message.set_name('PAY', 'HR_51419_EXA_SORT_CODE_LENGTH');
368 fnd_message.raise_error;
369 end if;
370 --
371 -- The sort code must only contain digits (0-9).
372 --
373 l_temp_string := translate(p_sort_code, '0123456789','0000000000');
374 if l_temp_string <> lpad('0', length(p_sort_code), '0') then
375 fnd_message.set_name('PAY', 'PAY_51538_BAD_GB_SORT_CODE');
376 fnd_message.raise_error;
377 end if;
378 --
379 -- Left-pad with zeroes, if necessary.
380 --
381 if length(p_sort_code) < l_sort_code_len then
382 if l_debug then
383 hr_utility.set_location(l_proc, 10);
384 end if;
385 p_sort_code := lpad(p_sort_code, l_sort_code_len, 0);
386 end if;
387 end if;
388
389 --
390 -- BANK_CODE must be a code for lookup type GB_BANKS.
391 --
392 if p_insert and
393 hr_api.not_exists_in_hr_lookups
394 (p_effective_date => trunc(sysdate)
395 ,p_lookup_type => 'GB_BANKS'
396 ,p_lookup_code => p_bank_code
397 )
398 then
399 fnd_message.set_name('PAY', 'HR_52966_INVALID_LOOKUP');
400 fnd_message.set_token('LOOKUP_TYPE', 'GB_BANKS');
401 fnd_message.set_token('COLUMN', 'P_BANK_CODE');
402 fnd_message.raise_error;
403 end if;
404
405 --
406 -- BRANCH must be <= 35 bytes in length.
407 --
408 if lengthb(p_branch) > l_branch_len then
409 fnd_message.set_name('PAY', 'HR_51418_EXA_BANK_BRANCH_LONG');
410 fnd_message.raise_error;
411 end if;
412
413 if l_debug then
414 hr_utility.set_location('Leaving:' || l_proc, 40);
415 end if;
416 end validate_gb_values;
417 ----------------------------< INSERT_GB_ROW >------------------------------
418 procedure insert_gb_row
419 (p_sort_code in out nocopy varchar2
420 ,p_bank_code in varchar2
421 ,p_branch in varchar2
422 ,p_long_branch in varchar2 default null
423 ,p_building_society_acct in out nocopy varchar2
424 ,p_enabled_flag in varchar2 default 'Y'
425 ,p_start_date_active in date default hr_api.g_sot
429 validate_gb_values
426 ,p_end_date_active in date default hr_api.g_eot
427 ) is
428 begin
430 (p_sort_code => p_sort_code
431 ,p_branch => p_branch
432 ,p_bank_code => p_bank_code
433 ,p_insert => true
434 );
435 --
436 insert_row
437 (p_branch_code => p_sort_code
438 ,p_legislation_code => 'GB'
439 ,p_bank_code => p_bank_code
440 ,p_branch => p_branch
441 ,p_long_branch => p_long_branch
442 ,p_enabled_flag => p_enabled_flag
443 ,p_start_date_active => p_start_date_active
444 ,p_end_date_active => p_end_date_active
445 );
446 end insert_gb_row;
447 ----------------------------< UPDATE_GB_ROW >------------------------------
448 procedure update_gb_row
449 (p_sort_code in varchar2
450 ,p_branch in varchar2 default hr_api.g_varchar2
451 ,p_long_branch in varchar2 default hr_api.g_varchar2
452 ,p_building_society_acct in out nocopy varchar2
453 ,p_enabled_flag in varchar2 default hr_api.g_varchar2
454 ,p_start_date_active in date default hr_api.g_date
455 ,p_end_date_active in date default hr_api.g_date
456 ) is
457 l_sort_code varchar2(100);
458 -- Values to pass to VALIDATE_GB_VALUES.
459 l_branch pay_bank_branches.branch%type;
460 --
461 cursor csr_existing_row(p_sort_code in varchar2) is
462 select branch
463 from pay_bank_branches
464 where branch_code = p_sort_code
465 and legislation_code = 'GB'
466 ;
467 begin
468 --
469 -- Get the existing values for branch and building society account to
470 -- use in validation for the case where the passed in values are
471 -- hr_api.g_varchar2.
472 --
473 l_sort_code := lpad(p_sort_code, g_gb_sort_code_len, '0');
474 open csr_existing_row(p_sort_code => l_sort_code);
475 fetch csr_existing_row
476 into l_branch
477 ;
478 if csr_existing_row%found then
479 if p_branch <> hr_api.g_varchar2 then
480 l_branch := p_branch;
481 end if;
482 --
483 -- The existing row was not found. Allow the validation to succeed and
484 -- let the error get raised by VALIDATE_GB_VALUES.
485 --
486 else
487 l_branch := 'BRANCH';
488 end if;
489 close csr_existing_row;
490 --
491 validate_gb_values
492 (p_sort_code => l_sort_code
493 ,p_branch => l_branch
494 ,p_bank_code => null
495 ,p_insert => false
496 );
497 --
498 update_row
499 (p_branch_code => l_sort_code
500 ,p_legislation_code => 'GB'
501 ,p_branch => p_branch
502 ,p_long_branch => p_long_branch
503 ,p_enabled_flag => p_enabled_flag
504 ,p_start_date_active => p_start_date_active
505 ,p_end_date_active => p_end_date_active
506 );
507 end update_gb_row;
508 ------------------------< DISPLAY_TO_GB_ACCOUNT >--------------------------
509 procedure display_to_gb_account
510 (p_external_account_id in out nocopy number
511 ,p_object_version_number in out nocopy number
512 ,p_business_group_id in number
513 ,p_effective_date in date
514 ,p_account_name in varchar2
515 ,p_account_number in varchar2
516 ,p_sort_code in varchar2
517 ,p_building_society_acct in varchar2 default null
518 ,p_multi_message in boolean default false
519 ,p_return_status out nocopy varchar2
520 ,p_msg_count out nocopy number
521 ,p_msg_data out nocopy varchar2
522 ) is
523 cursor csr_account
524 (p_external_account_id in number
525 ,p_sort_code in varchar2
526 ,p_account_name in varchar2
527 ,p_account_number in varchar2
528 ,p_building_society_acct in varchar2
529 ) is
530 select null
531 from pay_external_accounts exa
532 where exa.external_account_id = p_external_account_id
533 and exa.segment3 = p_sort_code
534 and exa.segment4 = p_account_number
535 and exa.segment5 = p_account_name
536 and ((exa.segment7 is null and
537 p_building_society_acct is null) or
538 (exa.segment7 = p_building_society_acct))
539 ;
540 --
541 cursor csr_branch_info(p_sort_code in varchar2) is
542 select pbb.bank_code
543 , pbb.branch
544 , pbb.enabled_flag
545 , pbb.start_date_active
546 , pbb.end_date_active
547 from pay_bank_branches pbb
548 where pbb.legislation_code = 'GB'
549 and pbb.branch_code = p_sort_code
550 ;
551 --
552 l_debug boolean := hr_utility.debug_enabled;
553 l_enabled boolean;
554 l_changed boolean;
555 l_exists varchar2(1);
556 l_branch_info csr_branch_info%rowtype;
557 l_accno varchar2(2000);
558 l_sort_code varchar2(2000);
559 l_temp_string varchar2(2000);
560 l_proc varchar2(64) := 'pay_bank_branches_pkg.display_to_gb_account';
561 begin
562 if l_debug then
563 hr_utility.set_location('Entering:' || l_proc, 0);
564 end if;
565 --
566 -- Clear message table.
567 --
568 fnd_msg_pub.initialize;
569 --
570 -- Account number length check.
571 --
572 if length(p_account_number) > g_gb_accno_len then
573 fnd_message.set_name('PAY', 'HR_51421_EXA_ACCOUNT_NO_LONG');
574 fnd_message.raise_error;
575 end if;
576 --
577 -- The account number must only contain digits (0-9).
578 --
579 l_temp_string := translate(p_account_number, '0123456789','0000000000');
580 if l_temp_string <> lpad('0', length(p_account_number), '0') then
581 fnd_message.set_name('PAY', 'HR_51422_EXA_ACCT_NO_POSITIVE');
582 fnd_message.raise_error;
583 end if;
584 --
585 -- Check whether or not the external account row has changed.
586 --
587 l_sort_code := lpad(p_sort_code, g_gb_sort_code_len, '0');
588 l_accno := lpad(p_account_number, g_gb_accno_len, '0');
589 open csr_account
590 (p_external_account_id => p_external_account_id
591 ,p_sort_code => l_sort_code
592 ,p_account_name => upper(p_account_name)
593 ,p_account_number => l_accno
594 ,p_building_society_acct => upper(p_building_society_acct)
595 );
596 fetch csr_account into l_exists;
597 l_changed := csr_account%notfound;
598 close csr_account;
599 --
600 if l_debug then
601 hr_utility.set_location(l_proc, 5);
602 end if;
603 --
604 -- Fetch the branch information for this sort code if the external
605 -- account row has changed.
606 --
607 if l_changed then
608 if l_debug then
609 hr_utility.set_location(l_proc, 10);
610 end if;
611 --
612 open csr_branch_info(p_sort_code);
613 fetch csr_branch_info into l_branch_info;
614 --
615 -- Find out whether the sort code exists and is enabled.
616 --
617 if csr_branch_info%found then
618 l_enabled :=
619 nvl(upper(l_branch_info.enabled_flag), 'Y') = 'Y' and
620 p_effective_date between
621 nvl(l_branch_info.start_date_active, hr_api.g_sot) and
622 nvl(l_branch_info.end_date_active, hr_api.g_eot);
623 else
624 l_enabled := false;
625 end if;
626 close csr_branch_info;
627 --
628 if l_enabled then
629 if l_debug then
630 hr_utility.set_location(l_proc, 15);
631 end if;
632 --
633 -- Create the new account.
634 --
635 pay_exa_ins.ins_or_sel
636 (p_validate => false
637 ,p_territory_code => 'GB'
638 ,p_business_group_id => p_business_group_id
639 ,p_segment1 => l_branch_info.bank_code
640 ,p_segment2 => l_branch_info.branch
641 ,p_segment3 => p_sort_code
642 ,p_segment4 => p_account_number
643 ,p_segment5 => p_account_name
644 ,p_segment7 => p_building_society_acct
645 ,p_external_account_id => p_external_account_id
646 ,p_object_version_number => p_object_version_number
647 );
648 else
649 if l_debug then
650 hr_utility.set_location(l_proc, 20);
651 end if;
652 --
653 fnd_message.set_name('PAY', 'PAY_33100_INVALID_SORT_CODE');
654 fnd_message.set_token('SORT_CODE', p_sort_code);
655 fnd_message.raise_error;
656 end if;
657 end if;
658 --
659 p_return_status := fnd_api.G_RET_STS_SUCCESS;
660 --
661 if l_debug then
662 hr_utility.set_location('Leaving:' || l_proc, 25);
663 end if;
664 exception
665 when others then
666 if csr_account%isopen then
667 close csr_account;
668 end if;
669 --
670 if csr_branch_info%isopen then
671 close csr_branch_info;
672 end if;
673 --
674 -- Handle errors according to the specified mechanism.
675 --
676 if p_multi_message then
677 p_return_status := fnd_api.G_RET_STS_ERROR;
678 fnd_msg_pub.add;
679 fnd_msg_pub.count_and_get
680 (p_count => p_msg_count
681 ,p_data => p_msg_data
682 );
683 --
684 if l_debug then
685 hr_utility.set_location('Leaving:' || l_proc, 35);
686 end if;
687 else
688 --
689 if l_debug then
690 hr_utility.set_location('Leaving:' || l_proc, 40);
691 end if;
692 raise;
693 end if;
694 end display_to_gb_account;
695 --
696 end pay_bank_branches_pkg;