[Home] [Help]
PACKAGE BODY: APPS.PAY_EXA_SHD
Source
1 PACKAGE BODY pay_exa_shd AS
2 /* $Header: pyexarhi.pkb 115.13 2003/09/26 06:48:50 tvankayl ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_exa_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 function return_api_dml_status return boolean is
14 --
15 l_proc varchar2(72) := g_package||'return_api_dml_status';
16 --
17 begin
18 hr_utility.set_location('Entering:'||l_proc, 5);
19 --
20 Return(nvl(g_api_dml, false));
21 --
22 hr_utility.set_location(' Leaving:'||l_proc, 10);
23 end return_api_dml_status;
24 --
25 -- ----------------------------------------------------------------------------
26 -- |---------------------------< constraint_error >---------------------------|
27 -- ----------------------------------------------------------------------------
28 procedure constraint_error(
29 p_constraint_name in all_constraints.constraint_name%TYPE
30 ) is
31 --
32 l_proc varchar2(72) := g_package||'constraint_error';
33 --
34 begin
35 hr_utility.set_location('Entering:'||l_proc, 5);
36 --
37 If (p_constraint_name = 'PAY_EXTERNAL_ACCOUNTS_PK') Then
38 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
39 hr_utility.set_message_token('PROCEDURE', l_proc);
40 hr_utility.set_message_token('STEP','5');
41 hr_utility.raise_error;
42 Else
43 -- [start of change: 40.5, Dave Harris]
44 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
45 hr_utility.set_message_token('PROCEDURE', l_proc);
46 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
47 hr_utility.raise_error;
48 -- [ end of change: 40.5, Dave Harris]
49 End If;
50 --
51 hr_utility.set_location(' Leaving:'||l_proc, 10);
52 end constraint_error;
53 --
54 -- ----------------------------------------------------------------------------
55 -- |-----------------------------< api_updating >-----------------------------|
56 -- ----------------------------------------------------------------------------
57 function api_updating(
58 p_external_account_id in number
59 ,p_object_version_number in number
60 )
61 return boolean is
62 --
63 -- cursor selects the 'current' row from the HR schema
64 --
65 cursor C_Sel1 is
66 SELECT external_account_id,
67 territory_code,
68 prenote_date,
69 id_flex_num,
70 summary_flag,
71 enabled_flag,
72 start_date_active,
73 end_date_active,
74 segment1,
75 segment2,
76 segment3,
77 segment4,
78 segment5,
79 segment6,
80 segment7,
81 segment8,
82 segment9,
83 segment10,
84 segment11,
85 segment12,
86 segment13,
87 segment14,
88 segment15,
89 segment16,
90 segment17,
91 segment18,
92 segment19,
93 segment20,
94 segment21,
95 segment22,
96 segment23,
97 segment24,
98 segment25,
99 segment26,
100 segment27,
101 segment28,
102 segment29,
103 segment30,
104 object_version_number
105 FROM PAY_EXTERNAL_ACCOUNTS
106 WHERE external_account_id = p_external_account_id
107 ;
108 --
109 l_proc varchar2(72) := g_package||'api_updating';
110 l_fct_ret boolean;
111 --
112 Begin
113 hr_utility.set_location('Entering:'||l_proc, 5);
114 --
115 -- external account id null, must be I'ing, return false
116 --
117 If (p_external_account_id is null and
118 p_object_version_number is null) Then
119 l_fct_ret := false;
120 --
121 -- external account id NOT null, must be U'ing
122 --
123 Else
124 If (p_external_account_id = g_old_rec.external_account_id and
125 p_object_version_number = g_old_rec.object_version_number) Then
126 hr_utility.set_location(l_proc, 10);
127 --
128 -- g_old_rec is current
129 --
130 l_fct_ret := true;
131 Else
132 --
133 -- select the current row into g_old_rec
134 --
135 Open C_Sel1;
136 Fetch C_Sel1 Into g_old_rec;
137
138 If C_Sel1%notfound Then
139 Close C_Sel1;
140 --
141 -- the primary key is invalid therefore we must error
142 --
143 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
144 hr_utility.raise_error;
145 End If;
146
147 Close C_Sel1;
148
149 --
150 -- if the object version number just selected into g_old_rec does
151 -- not match the object version number passed in,
152 -- raise error
153 --
154 If (p_object_version_number <> g_old_rec.object_version_number) Then
155 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
156 hr_utility.raise_error;
157 End If;
158
159 hr_utility.set_location(l_proc, 15);
160 l_fct_ret := true;
161 End If;
162 End If;
163
164 hr_utility.set_location(' Leaving:'||l_proc, 20);
165 Return (l_fct_ret);
166 End api_updating;
167 --
168 -- ----------------------------------------------------------------------------
169 -- |---------------------------------< lck >----------------------------------|
170 -- ----------------------------------------------------------------------------
171 procedure lck(
172 p_external_account_id in number
173 ,p_object_version_number in number
174 ) is
175 --
176 -- cursor selects the 'current' row from the HR schema
177 --
178 Cursor C_Sel1 is
179 select
180 external_account_id,
181 territory_code,
182 prenote_date,
183 id_flex_num,
184 summary_flag,
185 enabled_flag,
186 start_date_active,
187 end_date_active,
188 segment1,
189 segment2,
190 segment3,
191 segment4,
192 segment5,
193 segment6,
194 segment7,
195 segment8,
196 segment9,
197 segment10,
198 segment11,
199 segment12,
200 segment13,
201 segment14,
202 segment15,
203 segment16,
204 segment17,
205 segment18,
206 segment19,
207 segment20,
208 segment21,
209 segment22,
210 segment23,
211 segment24,
212 segment25,
213 segment26,
214 segment27,
215 segment28,
216 segment29,
217 segment30,
218 object_version_number
219 from pay_external_accounts
220 where external_account_id = p_external_account_id
221 for update nowait;
222 --
223 l_proc varchar2(72) := g_package||'lck';
224 --
225 begin
226 hr_utility.set_location('Entering:'||l_proc, 5);
227 --
228 -- Add any mandatory argument checking here:
229 -- Example:
230 -- hr_api.mandatory_arg_error
231 -- (p_api_name => l_proc,
232 -- p_argument => 'object_version_number',
233 -- p_argument_value => p_object_version_number);
234 --
235 Open C_Sel1;
236 Fetch C_Sel1 Into g_old_rec;
237 If C_Sel1%notfound then
238 Close C_Sel1;
239 --
240 -- The primary key is invalid therefore we must error
241 --
242 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
243 hr_utility.raise_error;
244 End If;
245 Close C_Sel1;
246 If (p_object_version_number <> g_old_rec.object_version_number) Then
247 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
248 hr_utility.raise_error;
249 End If;
250 --
251 hr_utility.set_location(' Leaving:'||l_proc, 10);
252 --
253 -- We need to trap the ORA LOCK exception
254 --
255 Exception
256 When HR_Api.Object_Locked then
257 --
258 -- The object is locked therefore we need to supply a meaningful
259 -- error message.
260 --
261 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
262 hr_utility.set_message_token('TABLE_NAME', 'pay_external_accounts');
263 hr_utility.raise_error;
264 end lck;
265 --
266 -- ----------------------------------------------------------------------------
267 -- |-----------------------------< convert_args >-----------------------------|
268 -- ----------------------------------------------------------------------------
269 function convert_args(
270 p_external_account_id in number
271 ,p_territory_code in varchar2
272 ,p_prenote_date in date
273 ,p_id_flex_num in number
274 ,p_summary_flag in varchar2
275 ,p_enabled_flag in varchar2
276 ,p_start_date_active in date
277 ,p_end_date_active in date
278 ,p_segment1 in varchar2
279 ,p_segment2 in varchar2
280 ,p_segment3 in varchar2
281 ,p_segment4 in varchar2
282 ,p_segment5 in varchar2
283 ,p_segment6 in varchar2
284 ,p_segment7 in varchar2
285 ,p_segment8 in varchar2
286 ,p_segment9 in varchar2
287 ,p_segment10 in varchar2
288 ,p_segment11 in varchar2
289 ,p_segment12 in varchar2
290 ,p_segment13 in varchar2
291 ,p_segment14 in varchar2
292 ,p_segment15 in varchar2
293 ,p_segment16 in varchar2
294 ,p_segment17 in varchar2
295 ,p_segment18 in varchar2
296 ,p_segment19 in varchar2
297 ,p_segment20 in varchar2
298 ,p_segment21 in varchar2
299 ,p_segment22 in varchar2
300 ,p_segment23 in varchar2
301 ,p_segment24 in varchar2
302 ,p_segment25 in varchar2
303 ,p_segment26 in varchar2
304 ,p_segment27 in varchar2
305 ,p_segment28 in varchar2
306 ,p_segment29 in varchar2
307 ,p_segment30 in varchar2
308 ,p_object_version_number in number
309 )
310 return g_rec_type is
311 --
312 l_rec g_rec_type;
313 l_proc varchar2(72) := g_package||'convert_args';
314 --
315 begin
316 hr_utility.set_location('Entering:'||l_proc, 5);
317 --
318 -- convert arguments into local l_rec structure
319 --
320 l_rec.external_account_id := p_external_account_id;
321 l_rec.territory_code := p_territory_code;
322 l_rec.prenote_date := p_prenote_date;
323 l_rec.id_flex_num := p_id_flex_num;
324 l_rec.summary_flag := p_summary_flag;
325 l_rec.enabled_flag := p_enabled_flag;
326 l_rec.start_date_active := p_start_date_active;
327 l_rec.end_date_active := p_end_date_active;
328 l_rec.segment1 := p_segment1;
329 l_rec.segment2 := p_segment2;
330 l_rec.segment3 := p_segment3;
331 l_rec.segment4 := p_segment4;
332 l_rec.segment5 := p_segment5;
333 l_rec.segment6 := p_segment6;
334 l_rec.segment7 := p_segment7;
335 l_rec.segment8 := p_segment8;
336 l_rec.segment9 := p_segment9;
337 l_rec.segment10 := p_segment10;
338 l_rec.segment11 := p_segment11;
339 l_rec.segment12 := p_segment12;
340 l_rec.segment13 := p_segment13;
341 l_rec.segment14 := p_segment14;
342 l_rec.segment15 := p_segment15;
343 l_rec.segment16 := p_segment16;
344 l_rec.segment17 := p_segment17;
345 l_rec.segment18 := p_segment18;
346 l_rec.segment19 := p_segment19;
347 l_rec.segment20 := p_segment20;
348 l_rec.segment21 := p_segment21;
349 l_rec.segment22 := p_segment22;
350 l_rec.segment23 := p_segment23;
351 l_rec.segment24 := p_segment24;
352 l_rec.segment25 := p_segment25;
353 l_rec.segment26 := p_segment26;
354 l_rec.segment27 := p_segment27;
355 l_rec.segment28 := p_segment28;
356 l_rec.segment29 := p_segment29;
357 l_rec.segment30 := p_segment30;
358 l_rec.object_version_number := p_object_version_number;
359 --
360 -- return the plsql record structure
361 --
362 hr_utility.set_location(' Leaving:'||l_proc, 10);
363 Return(l_rec);
364 --
365 end convert_args;
366 --
367 -- ----------------------------------------------------------------------------
368 -- |-----------------------------< keyflex_comb >-----------------------------|
369 -- ----------------------------------------------------------------------------
370 procedure keyflex_comb(
371 p_dml_mode in varchar2 default null
372 ,p_business_group_id in number
373 ,p_appl_short_name in fnd_application.application_short_name%TYPE
374 ,p_territory_code in varchar2 default null
375 ,p_flex_code in fnd_id_flex_segments.id_flex_code%TYPE
376 ,p_segment1 in varchar2 default null
377 ,p_segment2 in varchar2 default null
378 ,p_segment3 in varchar2 default null
379 ,p_segment4 in varchar2 default null
380 ,p_segment5 in varchar2 default null
381 ,p_segment6 in varchar2 default null
382 ,p_segment7 in varchar2 default null
383 ,p_segment8 in varchar2 default null
384 ,p_segment9 in varchar2 default null
385 ,p_segment10 in varchar2 default null
386 ,p_segment11 in varchar2 default null
387 ,p_segment12 in varchar2 default null
391 ,p_segment16 in varchar2 default null
388 ,p_segment13 in varchar2 default null
389 ,p_segment14 in varchar2 default null
390 ,p_segment15 in varchar2 default null
392 ,p_segment17 in varchar2 default null
393 ,p_segment18 in varchar2 default null
394 ,p_segment19 in varchar2 default null
395 ,p_segment20 in varchar2 default null
396 ,p_segment21 in varchar2 default null
397 ,p_segment22 in varchar2 default null
398 ,p_segment23 in varchar2 default null
399 ,p_segment24 in varchar2 default null
400 ,p_segment25 in varchar2 default null
401 ,p_segment26 in varchar2 default null
402 ,p_segment27 in varchar2 default null
403 ,p_segment28 in varchar2 default null
404 ,p_segment29 in varchar2 default null
405 ,p_segment30 in varchar2 default null
406 ,p_concat_segments_in in varchar2 default null
407 ,p_ccid in out nocopy number
408 ,p_concat_segments_out out nocopy varchar2
409 ) is
410 --
411 l_proc varchar2(72) := g_package||'keyflex_comb';
412 --
413 l_id_flex_num pay_external_accounts.id_flex_num%type;
414 --
415 -- the cursor orgsel selects the valid id_flex_num (external account kf)
416 -- for the specified business group
417 --
418 cursor csr_id_flex_num is
419 SELECT fnd_number.canonical_to_number(plr.rule_mode)
420 FROM PAY_LEGISLATION_RULES plr,
421 PER_BUSINESS_GROUPS pbg
422 WHERE plr.rule_type = 'E'
423 and p_territory_code is null
424 and plr.legislation_code = pbg.legislation_code
425 and pbg.business_group_id = p_business_group_id
426 Union
427 SELECT fnd_number.canonical_to_number(plr.rule_mode)
428 FROM PAY_LEGISLATION_RULES plr
429 WHERE plr.rule_type = 'E'
430 and p_territory_code is not null
431 and plr.legislation_code = p_territory_code;
432
433 --
434 begin
435 --
436 hr_utility.set_location('Entering:'||l_proc, 5);
437 --
438 hr_utility.set_location(l_proc, 10);
439 open csr_id_flex_num;
440 fetch csr_id_flex_num into l_id_flex_num;
441 --
442 if csr_id_flex_num%notfound then
443 close csr_id_flex_num;
444 --
445 -- the flex structure has not been found therefore we must error
446 --
447 hr_utility.set_message(801, 'HR_7471_FLEX_PEA_INVALID_ID');
448 hr_utility.raise_error;
449 end if;
450 close csr_id_flex_num;
451 hr_utility.set_location(l_proc, 20);
452 --
453 hr_utility.trace('| l_id_flex_num>' || l_id_flex_num || '<');
454 --
455 -- do not want trigger to maintain object version number,
456 -- will be done explicitly later by api
457 --
458 pay_exa_shd.g_api_dml := true; -- set the api dml status
459 --
460 begin
461 if p_dml_mode = 'INSERT' then
462 hr_utility.trace('| doing insert interface processing');
463 --
464 -- ins_or_sel_keyflex_comb() does either an I or U, therefore
465 -- l_external_account_id always has a value,
466 -- nb. p_concat_segments, if specified, will take precedence over
467 -- p_segment1 ... 30
468 --
469 hr_kflex_utility.ins_or_sel_keyflex_comb(
470 p_appl_short_name => 'PAY',
471 p_flex_code => 'BANK',
472 p_flex_num => l_id_flex_num,
473 p_segment1 => p_segment1,
474 p_segment2 => p_segment2,
475 p_segment3 => p_segment3,
476 p_segment4 => p_segment4,
477 p_segment5 => p_segment5,
478 p_segment6 => p_segment6,
479 p_segment7 => p_segment7,
480 p_segment8 => p_segment8,
481 p_segment9 => p_segment9,
482 p_segment10 => p_segment10,
483 p_segment11 => p_segment11,
484 p_segment12 => p_segment12,
485 p_segment13 => p_segment13,
486 p_segment14 => p_segment14,
487 p_segment15 => p_segment15,
488 p_segment16 => p_segment16,
489 p_segment17 => p_segment17,
490 p_segment18 => p_segment18,
491 p_segment19 => p_segment19,
492 p_segment20 => p_segment20,
493 p_segment21 => p_segment21,
494 p_segment22 => p_segment22,
495 p_segment23 => p_segment23,
496 p_segment24 => p_segment24,
497 p_segment25 => p_segment25,
498 p_segment26 => p_segment26,
499 p_segment27 => p_segment27,
500 p_segment28 => p_segment28,
501 p_segment29 => p_segment29,
502 p_segment30 => p_segment30,
503 p_concat_segments_in => p_concat_segments_in,
504 --
505 -- code combination id only passed in
506 --
507 p_ccid => p_ccid,
511 elsif p_dml_mode = 'UPDATE' then
508 p_concat_segments_out => p_concat_segments_out
509 );
510 --
512 hr_utility.trace('| doing update interface processing');
513 --
514 -- U of kff details for a given entity,
515 -- ccid is used to build up a plsql table of segment values,
516 -- this table is compared against IN parameters: segments 1 ... 30,
517 -- create a new table which may contain U'ed segment values,
518 -- then call check_segment_combination(), this will create a
519 -- new combination row if required
520 --
521 hr_kflex_utility.upd_or_sel_keyflex_comb(
522 p_appl_short_name => 'PAY',
523 p_flex_code => 'BANK',
524 p_flex_num => l_id_flex_num,
525 p_segment1 => p_segment1,
526 p_segment2 => p_segment2,
527 p_segment3 => p_segment3,
528 p_segment4 => p_segment4,
529 p_segment5 => p_segment5,
530 p_segment6 => p_segment6,
531 p_segment7 => p_segment7,
532 p_segment8 => p_segment8,
533 p_segment9 => p_segment9,
534 p_segment10 => p_segment10,
535 p_segment11 => p_segment11,
536 p_segment12 => p_segment12,
537 p_segment13 => p_segment13,
538 p_segment14 => p_segment14,
539 p_segment15 => p_segment15,
540 p_segment16 => p_segment16,
541 p_segment17 => p_segment17,
542 p_segment18 => p_segment18,
543 p_segment19 => p_segment19,
544 p_segment20 => p_segment20,
545 p_segment21 => p_segment21,
546 p_segment22 => p_segment22,
547 p_segment23 => p_segment23,
548 p_segment24 => p_segment24,
549 p_segment25 => p_segment25,
550 p_segment26 => p_segment26,
551 p_segment27 => p_segment27,
552 p_segment28 => p_segment28,
553 p_segment29 => p_segment29,
554 p_segment30 => p_segment30,
555 p_concat_segments_in => p_concat_segments_in,
556 --
557 -- code combination id only passed in/out
558 --
559 p_ccid => p_ccid,
560 p_concat_segments_out => p_concat_segments_out
561 );
562 --
563 else
564 null;
565 --
566 -- stub - error, invalid mode
567 --
568 end if;
569 exception
570 when app_exception.application_exception then
571 hr_message.provide_error;
572 hr_utility.trace('*****' || p_territory_code);
573 hr_utility.trace('*****' || hr_message.last_message_name);
574 hr_utility.trace('*****' || hr_message.get_token_value('COLUMN'));
575
576 --
577 -- if any validation fails on a segment, the exception
578 -- HR_FLEX_VALUE_INVALID is thrown,
579 -- therefore still need to check the type of failure
580 -- so the appropriate message, if it exists, can be passed
581 -- to the client
582 --
583
584 --
585 -- us segment 1
586 --
587 if p_territory_code = 'US' then
588 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
589 if hr_message.get_token_value('COLUMN') = 'SEGMENT1' then
590 if (length(p_segment1) > 60) then
591 hr_utility.set_message(801, 'HR_51458_EXA_US_ACCT_NAME_LONG');
592 hr_utility.raise_error;
593 end if;
594 end if;
595 end if;
596 end if;
597
598 --
599 -- us segment 2
600 --
604 if (length(p_segment1) > 80) then
601 if p_territory_code = 'US' then
602 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
603 if hr_message.get_token_value('COLUMN') = 'SEGMENT2' then
605 hr_utility.set_message(801, 'HR_51459_EXA_US_ACCT_TYPE_LONG');
606 hr_utility.raise_error;
607 else
608 declare
609 cursor fnd_com_look is
610 select null
611 from fnd_common_lookups
612 where lookup_type = 'US_ACCOUNT_TYPE'
613 and application_id = 800
614 and lookup_code = p_segment2;
615 l_dummy number;
616 begin
617 --
618 -- ensure that the p_segment2 is valid and exists
619 --
620 open fnd_com_look;
621 fetch fnd_com_look into l_dummy;
622 if fnd_com_look%notfound then
623 close fnd_com_look;
624 hr_utility.set_message(801,
625 'HR_51460_EXA_US_ACC_TYP_UNKNOW');
626 hr_utility.raise_error;
627 end if;
628 close fnd_com_look;
629 end; -- end of anonymous block
630 end if;
631 end if;
632 end if;
633 end if;
634
635 --
636 -- us segment 3
637 --
638 if p_territory_code = 'US' then
639 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
640 if hr_message.get_token_value('COLUMN') = 'SEGMENT3' then
641 if (length(p_segment1) > 60) then
642 hr_utility.set_message(801, 'HR_51461_EXA_US_ACCT_NO_LONG');
643 hr_utility.raise_error;
644 end if;
645 end if;
646 end if;
647 end if;
648
649 --
650 -- us segment 4
651 --
652 if p_territory_code = 'US' then
653 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
654 if hr_message.get_token_value('COLUMN') = 'SEGMENT4' then
655 if (length(p_segment4) > 9) then
656 hr_utility.set_message(801, 'HR_51462_EXA_US_TRAN_CODE_LONG');
657 hr_utility.raise_error;
658 end if;
659 end if;
660 end if;
661 end if;
662
663 --
664 -- us segment 5
665 --
666 if p_territory_code = 'US' then
667 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
668 if hr_message.get_token_value('COLUMN') = 'SEGMENT5' then
669 if (length(p_segment5) > 60) then
670 hr_utility.set_message(801, 'HR_51463_EXA_US_BANK_NAME_LONG');
671 hr_utility.raise_error;
672 end if;
673 end if;
674 end if;
675 end if;
676
677 --
678 -- us segment 6
679 --
680 if p_territory_code = 'US' then
681 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
682 if hr_message.get_token_value('COLUMN') = 'SEGMENT6' then
683 if (length(p_segment6) > 60) then
684 hr_utility.set_message(801, 'HR_51464_EXA_US_BANK_BRAN_LONG');
685 hr_utility.raise_error;
686 end if;
687 end if;
688 end if;
689 end if;
690
691 --
692 -- stub - have not included us segment checks 7 to 30 as the tokens
693 -- ARG_NAME and ARG_VALUE are populated dynamically
694 --
695
696 --------------------------------------------------------------------------------
697
698 --
699 -- gb segment 1
700 --
701 if p_territory_code = 'GB' then
702 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
703 if hr_message.get_token_value('COLUMN') = 'SEGMENT1' then
704 if (length(p_segment1) > 30) then
705 hr_utility.set_message(801, 'HR_51416_EXA_BANK_NAME_LONG');
706 hr_utility.raise_error;
707 else
708 declare
709 cursor hlsel is
710 select null
711 from hr_lookups
712 where lookup_type = 'GB_BANKS'
713 and lookup_code = p_segment1;
714 l_dummy number;
715 begin
716 --
717 -- ensure that the p_segment1 is valid and exists
718 --
719 open hlsel;
720 fetch hlsel into l_dummy;
721 if hlsel%notfound then
722 close hlsel;
723 hr_utility.set_message(801,
724 'HR_51417_EXA_BANK_NAME_UNKNOWN');
725 hr_utility.raise_error;
726 end if;
727 close hlsel;
728 end; -- end of anonymous block
729 end if;
730 end if;
731 end if;
732 end if;
733
734 --
735 -- gb segment 2
736 --
737 if p_territory_code = 'GB' then
741 hr_utility.set_message(801, 'HR_51418_EXA_BANK_BRANCH_LONG');
738 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
739 if hr_message.get_token_value('COLUMN') = 'SEGMENT2' then
740 if (length(p_segment1) > 35) then
742 hr_utility.raise_error;
743 end if;
744 end if;
745 end if;
746 end if;
747
748 --
749 -- gb segment 3
750 --
751 if p_territory_code = 'GB' then
752 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
753 if hr_message.get_token_value('COLUMN') = 'SEGMENT3' then
754 --
755 -- ensure that the length is 6
756 --
757 if (length(p_segment3) <> 6) then
758 hr_utility.set_message(801, 'HR_51419_EXA_SORT_CODE_LENGTH');
759 hr_utility.raise_error;
760 --
761 -- ensure that p_segment3 is +ve
762 --
763 elsif (to_number(p_segment3) < 0) then
764 hr_utility.set_message(801, 'HR_51420_EXA_SORT_CODE_POSITVE');
765 hr_utility.raise_error;
766 end if;
767 end if;
768 end if;
769 end if;
770
771 --
772 -- gb segment 4
773 --
774 if p_territory_code = 'GB' then
775 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
776 if hr_message.get_token_value('COLUMN') = 'SEGMENT4' then
777 --
778 -- ensure that the length is 8
779 --
780 if (length(p_segment4) <> 8) then
781 hr_utility.set_message(801, 'HR_51421_EXA_ACCOUNT_NO_LONG');
782 hr_utility.raise_error;
783 --
784 -- ensure that p_segment4 is +ve
785 --
786 elsif (to_number(p_segment4) < 0) then
787 hr_utility.set_message(801, 'HR_51422_EXA_ACCT_NO_POSITIVE');
788 hr_utility.raise_error;
789 end if;
790 end if;
791 end if;
792 end if;
793
794 --
795 -- gb segment 5
796 --
797 if p_territory_code = 'GB' then
798 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
799 if hr_message.get_token_value('COLUMN') = 'SEGMENT5' then
800 --
801 -- ensure that the length does not exceed 18
802 --
803 if (length(p_segment5) > 18) then
804 hr_utility.set_message(801, 'HR_51423_EXA_ACCOUNT_NAME_LONG');
805 hr_utility.raise_error;
806 --
807 -- ensure that the p_segment5 is in an upperformat format
808 --
809 elsif (p_segment5 <> upper(p_segment5)) then
810 hr_utility.set_message(801, 'HR_51424_EXA_ACCOUNT_NAME_CASE');
811 hr_utility.raise_error;
812 end if;
813 end if;
814 end if;
815 end if;
816
817 --
818 -- gb segment 6
819 --
820 if p_territory_code = 'GB' then
821 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
822 if hr_message.get_token_value('COLUMN') = 'SEGMENT6' then
823 --
824 -- ensure that the length does not exceed 1
825 --
826 if (length(p_segment6) > 1) then
827 hr_utility.set_message(801, 'HR_51425_EXA_ACCOUNT_TYPE_LONG');
828 hr_utility.raise_error;
829 --
830 -- ensure that p_segment4 is in the range of: 0 -> 5
831 --
832 elsif (to_number(p_segment6) < 0 or
833 to_number(p_segment6) > 5) then
834 hr_utility.set_message(801, 'HR_51426_EXA_ACCT_TYPE_RANGE');
835 hr_utility.raise_error;
836 end if;
837 end if;
838 end if;
839 end if;
840
841 --
842 -- gb segment 7
843 --
844 if p_territory_code = 'GB' then
845 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
846 if hr_message.get_token_value('COLUMN') = 'SEGMENT7' then
847 --
848 -- ensure that the length does not exceed 18
849 --
850 if (length(p_segment7) > 18) then
851 hr_utility.set_message(801, 'HR_51427_EXA_BS_ACCT_NO_LONG');
852 hr_utility.raise_error;
853 --
854 -- ensure that the p_segment7 is in an uppercase format
855 --
856 elsif (p_segment7 <> upper(p_segment7)) then
857 hr_utility.set_message(801, 'HR_51428_EXA_BS_ACCT_NO_CASE');
858 hr_utility.raise_error;
859 end if;
860 end if;
861 end if;
862 end if;
863
864 --
865 -- gb segment 8
866 --
867 if p_territory_code = 'GB' then
868 if hr_message.last_message_name = 'HR_FLEX_VALUE_INVALID' then
869 if hr_message.get_token_value('COLUMN') = 'SEGMENT8' then
870 --
871 -- ensure that the length does not exceed 20
872 --
873 if (length(p_segment8) > 20) then
874 hr_utility.set_message(801, 'HR_51429_EXA_BANK_LOC_LONG');
875 hr_utility.raise_error;
876 else
877 declare
878 l_exists varchar2(80);
879 cursor csr_chk_hr_lookups is
880 select null
881 from hr_lookups
882 where LOOKUP_TYPE = 'GB_COUNTRY'
883 and lookup_code = p_segment8;
884 begin
885 --
886 -- ensure that the p_segment8 exists in hr_lookups where
887 -- lookup_type = 'GB_COUNTRY'
888 --
889 open csr_chk_hr_lookups;
890 fetch csr_chk_hr_lookups into l_exists;
891 if csr_chk_hr_lookups%notfound then
892 close csr_chk_hr_lookups;
893 hr_utility.set_message(801,
894 'HR_51430_EXA_BANK_LOC_UNKNOWN');
895 hr_utility.raise_error;
896 end if;
897 close csr_chk_hr_lookups;
898 end; -- end of anonymous block
899 end if;
900 end if;
901 end if;
902 end if;
903
904 --
905 -- stub - have not included us segment checks 9 to 30 as the tokens
906 -- ARG_NAME and ARG_VALUE are populated dynamically
907 --
908
909 --
910 -- do not trap any other errors
911 --
912 raise;
913 end; -- end of anonymous block
914 --
915 pay_exa_shd.g_api_dml := false; -- set the api dml status
916 --
917 hr_utility.set_location(' Leaving:'||l_proc, 100);
918 --
919 end keyflex_comb;
920 END pay_exa_shd;