[Home] [Help]
PACKAGE BODY: APPS.CE_BANK_AND_ACCOUNT_VALIDATION
Source
1 PACKAGE BODY CE_BANK_AND_ACCOUNT_VALIDATION AS
2 /*$Header: cebavalb.pls 120.22.12020000.5 2013/03/28 06:20:47 anushven ship $ */
3
4 l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
5 --l_DEBUG varchar2(1) := 'Y';
6
7 /*=======================================================================+
8 | PUBLIC FUNCTION ce_check_numeric |
9 | |
10 | DESCRIPTION |
11 | Check if a value is numeric |
12 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
13 | |
14 | ARGUMENTS |
15 | IN: |
16 | check_value |
17 | pos_from |
18 | pos_to |
19 | RETURN VALUE |
20 | 0 value return is numeric |
21 | 1 value return is alphanumeric |
22 +=======================================================================*/
23 FUNCTION ce_check_numeric(check_value VARCHAR2,
24 pos_from NUMBER,
25 pos_for NUMBER)
26 RETURN VARCHAR2
27 IS
28 num_check VARCHAR2(40);
29 BEGIN
30 num_check := '1';
31 num_check := nvl(
32 rtrim(
33 translate(substr(check_value,pos_from,pos_for),
34 '1234567890',
35 ' ')
36 ), '0'
37 );
38 RETURN(num_check);
39 END ce_check_numeric;
40
41
42 /*=======================================================================+
43 | PUBLIC PROCEDURE validate_bank |
44 | This procedure should be registered as the value of the profile |
45 | option 'HZ_BANK_VALIDATION_PROCEDURE' in fnd_profile_option_values |
46 | DESCRIPTION |
47 | Dynamic bound validation routine. This procedure is called by TCA |
48 | create_bank/update_bank API. |
52 | 2. Combination of Country and Short Bank Name is unique |
49 | |
50 | Validate: |
51 | 1. Combination of Country and Bank Name is unique |
53 | -- remove this 2nd validation due to upgrade changes |
54 | 3. Combination of Country and Bank Number is unique |
55 | |
56 | Bug 6642215/6742860: Validation changed to check combination of |
57 | bank name, number and country is unique. |
58 | |
59 | Bug 8572093 validation changes for 6642215 should only apply for |
60 | upgraded banks. For banks created in R12, original validations will |
61 | apply. |
62 | |
63 | Bug 14498341 - Rewritten procedure. The uniqueness validations must |
64 | check for the combination of: |
65 | - Bank Name |
66 | - Bank Number |
67 | - Country |
68 | |
69 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
70 | |
71 | ARGUMENTS |
72 | IN: |
73 | p_temp_id in HZ_ORG_PROFILE_VAL_GT table |
74 +=======================================================================*/
75 PROCEDURE validate_bank (p_temp_id IN NUMBER,
76 x_return_status IN OUT NOCOPY VARCHAR2) IS
77 l_bank_name_rowid VARCHAR2(1000);
78 l_short_name_rowid VARCHAR2(1000);
79 l_bank_number_rowid VARCHAR2(1000);
80 l_bank_name_exist VARCHAR2(1);
81 l_short_name_exist VARCHAR2(1);
82 l_bank_number_exist VARCHAR2(1);
83 l_bank_exist VARCHAR2(1);
84 l_country VARCHAR2(60);
85 l_bank_name VARCHAR2(360);
86 l_short_name VARCHAR2(240) := NULL;
87 l_short_name_null VARCHAR2(1) := 'N';
88 l_bank_number VARCHAR2(60) := NULL;
89
90 /* records in HZ_BANK_VAL_GT and hz_org_profile_val_gt share the same temp_id */
91
92 CURSOR c_country IS
93 SELECT country
94 FROM HZ_BANK_VAL_GT
95 WHERE temp_id = p_temp_id;
96
97 CURSOR c_bank_number IS
98 SELECT bank_or_branch_number
99 FROM HZ_BANK_VAL_GT
100 WHERE temp_id = p_temp_id;
101
102 CURSOR c_bank_name IS
103 SELECT organization_name
104 FROM hz_org_profile_val_gt
105 WHERE temp_id = p_temp_id;
106
107 -- Cursor to check for duplicate of combination of name-number-country.
108 CURSOR c_bank_name_number_uk IS
109 SELECT 'Y'
110 FROM hz_parties BankParty,
111 hz_organization_profiles BankOrgProfile,
112 hz_code_assignments BankCA
113 WHERE BankParty.PARTY_TYPE = 'ORGANIZATION'
114 AND BankParty.status = 'A'
115 AND BankParty.PARTY_ID = BankOrgProfile.PARTY_ID
116 AND SYSDATE between TRUNC(BankOrgProfile.effective_start_date)
117 and NVL(TRUNC(BankOrgProfile.effective_end_date), SYSDATE+1)
118 AND BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
119 AND BankCA.CLASS_CODE = 'BANK'
120 AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
121 AND NVL(BankCA.STATUS, 'A') = 'A'
122 AND BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
123 AND BankOrgProfile.home_country = l_country
124 AND nvl(BankOrgProfile.bank_or_branch_number,'--NULL--') = nvl(l_bank_number,'--NULL--')
125 AND upper(BankParty.party_name) = upper(l_bank_name)
126 AND BankParty.party_id <> p_temp_id;
127
128 BEGIN
129 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.validate_bank (+)');
130
131 -- Fetching the bank country
132 OPEN c_country;
133 FETCH c_country INTO l_country;
134
135 IF c_country%NOTFOUND THEN
136 -- Close the cursor and raise an error if the country could not be
137 -- found in the temp table HZ_BANK_VAL_GT.
138 CLOSE c_country;
139
140 fnd_message.set_name('CE', 'CE_TEMP_NOT_FOUND');
141 fnd_message.set_token('COLUMN', 'Country');
142 fnd_message.set_token('TABLE', 'HZ_BANK_VAL_GT');
143 fnd_msg_pub.add;
144 RAISE NO_DATA_FOUND;
145 END IF;
146 CLOSE c_country;
147
148 -- Fetching the bank name
149 OPEN c_bank_name;
150 FETCH c_bank_name INTO l_bank_name;
151 IF c_bank_name%NOTFOUND THEN
152 -- Close the cursor and raise an error if the org_name could not be
153 -- found in the temp table hz_org_profile_val_gt.
154 CLOSE c_bank_name;
155
156 fnd_message.set_name('CE', 'CE_TEMP_NOT_FOUND');
157 fnd_message.set_token('COLUMN', 'Organization_name');
158 fnd_message.set_token('TABLE', 'HZ_ORG_PROFILE_VAL_GT');
159 fnd_msg_pub.add;
160 RAISE NO_DATA_FOUND;
161 END IF;
162 CLOSE c_bank_name;
163
164 -- Fetching the bank number
168 l_bank_number := null;
165 OPEN c_bank_number;
166 FETCH c_bank_number INTO l_bank_number;
167 IF c_bank_number%NOTFOUND THEN
169 END IF;
170 CLOSE c_bank_number;
171
172 -- if bank number is not entered in the UI it is being
173 -- stored in the temp table as character with ascii value of
174 -- zero. to check for this correcting value to null
175 if ascii(l_bank_number) = 0 then
176 l_bank_number := null;
177 end if;
178
179 -- Check for Bank Name-Number-Country Uniqueness
180 OPEN c_bank_name_number_uk;
181 FETCH c_bank_name_number_uk INTO l_bank_exist;
182 CLOSE c_bank_name_number_uk;
183
184 -- If bank exists add message to error queue
185 -- 14242293: Modified error message
186 if l_bank_exist = 'Y' then
187 fnd_message.set_name('CE', 'CE_BANK_EXISTS');
188 fnd_msg_pub.add;
189 x_return_status := fnd_api.G_RET_STS_ERROR;
190 else
191 x_return_status := fnd_api.G_RET_STS_SUCCESS;
192 end if;
193 EXCEPTION
194 WHEN OTHERS THEN
195 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
196 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_BANK_AND_ACCOUNT_VALIDATION.validate_bank');
197 fnd_msg_pub.add;
198 RAISE;
199 END validate_bank;
200
201
202 /*=======================================================================+
203 | PUBLIC PROCEDURE validate_branch |
204 | This procedure should be registered as the value of the profile |
205 | option of 'HZ_BANK_BRANCH_VALIDATION_PROCEDURE' in |
206 | fnd_profile_option_values |
207 | |
208 | DESCRIPTION |
209 | Dynamic bound validation routine. |
210 | The validation 'the combination of country, bank name, and branch |
211 | name should be unique' is done in TCA's create_bank_branch API. |
212 | This API will call the country specific validation APIs |
213 | This procedure is called by TCA create_bank_branch/ |
214 | update_bank_branch API |
215 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
216 | |
217 | ARGUMENTS |
218 | IN: |
219 | p_temp_org_profile_id temp_id in HZ_ORG_PROFILE_VAL_GT table |
220 +=======================================================================*/
221 PROCEDURE validate_branch (p_temp_org_profile_id IN NUMBER,
222 x_return_status IN OUT NOCOPY VARCHAR2)
223 IS
224 BEGIN
225 NULL;
226 END validate_branch;
227
228 /*=======================================================================+
229 | PUBLIC PROCEDURE validate_org |
230 | |
231 | DESCRIPTION |
232 | Validate the org is a valid org in TCA/HR and satisfies |
233 | MO security profile |
234 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
235 | |
236 | ARGUMENTS |
237 | IN: |
238 | p_org_name name of org to be validated |
239 | p_org_classification HR_BG (Business Group) |
240 | OPERATING_UNIT |
241 | HR_LEGAL |
242 | p_security_profile_id |
243 +=======================================================================*/
244 /*
245 PROCEDURE validate_org (p_org_id IN NUMBER,
246 p_org_classification IN VARCHAR2,
247 p_security_profile_id IN NUMBER,
248 x_out OUT NUMBER) IS -- this param is only for testing needs to be taken out
249 l_org_list VARCHAR2(4000);
250 l_sub VARCHAR2(4000);
251 l_len NUMBER;
252 l_pos NUMBER;
253 l_org_id_str VARCHAR2(30);
254 l_found VARCHAR2(1);
255 BEGIN
256 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.validate_org (+)');
257 x_out := 1;
258 l_found := 'N';
259
260 l_org_list := MO_UTILS.get_org_list(p_security_profile_id, p_org_classification);
261 IF l_org_list = '' THEN
262 fnd_message.set_name('CE', 'CE_NO_ORG_FOR_MO_SP');
263 fnd_msg_pub.add;
267 l_sub := l_org_list;
264 return;
265 END IF;
266
268
269 WHILE ( l_sub <> '@') LOOP
270 l_len := LENGTH(l_sub);
271 l_sub := substr(l_sub, 2, l_len-1);
272 l_pos := instr(l_sub, '@');
273 l_org_id_str := substr(l_sub, 1, l_pos-1);
274 if (TO_NUMBER(l_org_id_str) = p_org_id) then
275 l_found := 'Y';
276 l_sub := '@';
277 x_out := 0;
278 else
279 l_sub := substr(l_sub, l_pos, l_len - l_pos);
280 END IF;
281 --l_sub := substr(l_sub, l_pos, l_len - l_pos);
282 END LOOP;
283
284 IF l_found = 'N' THEN
285 fnd_message.set_name('CE', 'CE_INVALID_ORG_NAME');
286 fnd_msg_pub.add;
287 END IF;
288
289 EXCEPTION
290 WHEN OTHERS THEN
291 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
292 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_BANK_AND_ACCOUNT_VALIDATION.validate_org');
293 fnd_msg_pub.add;
294 RAISE;
295 END validate_org;
296 */
297
298
299 /*=======================================================================+
300 | PUBLIC PROCEDURE validate_currency |
301 | |
302 | DESCRIPTION |
303 | Validate the currency_code is valid in FND_CURRENCIES |
304 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
305 | |
306 | ARGUMENTS |
307 | IN: |
308 | p_currency_code |
309 +=======================================================================*/
310 PROCEDURE validate_currency (p_currency_code IN VARCHAR2,
311 x_return_status IN OUT NOCOPY VARCHAR2) IS
312 CURSOR c_currency IS
313 SELECT 1
314 FROM fnd_currencies fc
315 WHERE fc.currency_code = p_currency_code;
316 l_dummy NUMBER := 0;
317 BEGIN
318 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.validate_currency (+)');
319
320 -- initialize API return status to success.
321 x_return_status := fnd_api.g_ret_sts_success;
322
323 OPEN c_currency;
324 FETCH c_currency INTO l_dummy;
325 IF c_currency%NOTFOUND THEN
326 fnd_message.set_name('CE', 'CE_BA_INVALID_CURRENCY');
327 fnd_msg_pub.add;
328 x_return_status := fnd_api.g_ret_sts_error;
329 END IF;
330 CLOSE c_currency;
331 END validate_currency;
332
333
334 /*=======================================================================+
335 | PUBLIC PROCEDURE validate_account_name |
336 | |
337 | DESCRIPTION |
338 | Validate the account_name is unique within a branch |
339 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
340 | |
341 | ARGUMENTS |
342 | IN: |
343 | p_branch_id |
344 | p_account_name
345 +=======================================================================*/
346 PROCEDURE validate_account_name (p_branch_id IN NUMBER,
347 p_account_name IN VARCHAR2,
348 p_account_id IN NUMBER,
349 x_return_status IN OUT NOCOPY VARCHAR2) IS
350 --l_rowid VARCHAR2(1000);
351 l_dummy VARCHAR2(1);
352 /*
353 CURSOR c_rowid IS
354 SELECT rowid
355 FROM ce_bank_accounts
356 WHERE bank_account_name = p_account_name
357 AND bank_branch_id = p_branch_id;
358 */
359 CURSOR c_acct_name IS
360 SELECT 'X'
361 FROM ce_bank_accounts
362 WHERE bank_branch_id = p_branch_id
363 AND bank_account_name = p_account_name
364 AND bank_account_id <> NVL(p_account_id, -1);
365 --AND (rowid <> l_rowid OR l_rowid IS NULL);
366
367 BEGIN
368 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_name (+)');
369
370 -- initialize API return status to success.
371 x_return_status := fnd_api.g_ret_sts_success;
372 /*
373 OPEN c_rowid;
374 FETCH c_rowid INTO l_rowid;
375 CLOSE c_rowid;
376 */
377 OPEN c_acct_name;
378 FETCH c_acct_name INTO l_dummy;
379 IF l_dummy = 'X' THEN
380 fnd_message.set_name('CE', 'CE_DUP_ACCT_NAME');
381 fnd_msg_pub.add;
382 x_return_status := fnd_api.g_ret_sts_error;
383 END IF;
384 CLOSE c_acct_name;
385
386 EXCEPTION
387 WHEN OTHERS THEN
388 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
392 END validate_account_name;
389 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_name');
390 fnd_msg_pub.add;
391
393
394 /*Bug 16507244 code change 1/2 start*/
395 /*=======================================================================+
396 | Private Procedure validate_iban_length |
397 | |
398 | DESCRIPTION |
399 | Validate IBAN length. |
400 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
401 | |
402 | ARGUMENTS |
403 | IN : Xi_country_code |
404 | Xi_len |
405 | IN OUT: Xo_return_status |
406 | |
407 +=======================================================================*/
408 PROCEDURE validate_iban_length(Xi_country_code IN VARCHAR2,
409 Xi_len IN NUMBER,
410 Xo_return_status IN OUT NOCOPY VARCHAR2)IS
414 l_country_name VARCHAR2(50);
411 l_valid_iban BOOLEAN;
412 l_country_chk_reqd BOOLEAN;
413 l_iban_len NUMBER;
415 BEGIN
416 cep_standard.debug('>>CE_BANK_ACCCOUNT_VALIDATION.validate_iban_length');
417 cep_standard.debug('Xi_country_code - '||Xi_country_code);
418 cep_standard.debug('IBAN length - '||Xi_len);
419 l_valid_iban := false;
420 l_country_chk_reqd := true;
421 xo_return_status := fnd_api.g_ret_sts_success;
422 l_iban_len := Xi_len;
423 IF Xi_country_code = 'AD' AND l_iban_len <> 24 THEN
424 l_iban_len := 24;
425 ELSIF Xi_country_code = 'AT' AND l_iban_len <> 20 THEN
426 l_iban_len := 20;
427 ELSIF Xi_country_code = 'BE' AND l_iban_len <> 16 THEN
428 l_iban_len := 16;
429 ELSIF Xi_country_code = 'BA' AND l_iban_len <> 20 THEN
430 l_iban_len := 20;
431 ELSIF Xi_country_code = 'BG' AND l_iban_len <> 22 THEN
432 l_iban_len := 22;
433 ELSIF Xi_country_code = 'CY' AND l_iban_len <> 28 THEN
434 l_iban_len := 28;
435 ELSIF Xi_country_code = 'DK' AND l_iban_len <> 18 THEN
436 l_iban_len := 18;
437 ELSIF Xi_country_code = 'EE' AND l_iban_len <> 20 THEN
438 l_iban_len := 20;
439 ELSIF Xi_country_code = 'FI' AND l_iban_len <> 18 THEN
440 l_iban_len := 18;
441 ELSIF Xi_country_code = 'FR' AND l_iban_len <> 27 THEN
442 l_iban_len := 27;
443 ELSIF Xi_country_code = 'DE' AND l_iban_len <> 22 THEN
444 l_iban_len := 22;
445 ELSIF Xi_country_code = 'GI' AND l_iban_len <> 23 THEN
446 l_iban_len := 23;
447 ELSIF Xi_country_code = 'GR' AND l_iban_len <> 27 THEN
448 l_iban_len := 27;
449 ELSIF Xi_country_code = 'HU' AND l_iban_len <> 28 THEN
450 l_iban_len := 28;
451 ELSIF Xi_country_code = 'IS' AND l_iban_len <> 26 THEN
452 l_iban_len := 26;
453 ELSIF Xi_country_code = 'IE' AND l_iban_len <> 22 THEN
454 l_iban_len := 22;
455 ELSIF Xi_country_code = 'IT' AND l_iban_len <> 27 THEN
456 l_iban_len := 27;
457 ELSIF Xi_country_code = 'LV' AND l_iban_len <> 21 THEN
458 l_iban_len := 21;
459 ELSIF Xi_country_code = 'LI' AND l_iban_len <> 21 THEN
460 l_iban_len := 21;
461 ELSIF Xi_country_code = 'LT' AND l_iban_len <> 20 THEN
462 l_iban_len := 20;
463 ELSIF Xi_country_code = 'LU' AND l_iban_len <> 20 THEN
464 l_iban_len := 20;
465 ELSIF Xi_country_code = 'MK' AND l_iban_len <> 19 THEN
466 l_iban_len := 19;
467 ELSIF Xi_country_code = 'MT' AND l_iban_len <> 31 THEN
468 l_iban_len := 31;
469 ELSIF Xi_country_code = 'MU' AND l_iban_len <> 30 THEN
470 l_iban_len := 30;
471 ELSIF Xi_country_code = 'ME' AND l_iban_len <> 22 THEN
472 l_iban_len := 22;
473 ELSIF Xi_country_code = 'NL' AND l_iban_len <> 18 THEN
474 l_iban_len := 18;
475 ELSIF Xi_country_code = 'PL' AND l_iban_len <> 28 THEN
476 l_iban_len := 28;
477 ELSIF Xi_country_code = 'PT' AND l_iban_len <> 25 THEN
478 l_iban_len := 25;
482 l_iban_len := 22;
479 ELSIF Xi_country_code = 'RO' AND l_iban_len <> 24 THEN
480 l_iban_len := 24;
481 ELSIF Xi_country_code = 'RS' AND l_iban_len <> 22 THEN
483 ELSIF Xi_country_code = 'SK' AND l_iban_len <> 24 THEN
484 l_iban_len := 24;
485 ELSIF Xi_country_code = 'SI' AND l_iban_len <> 19 THEN
486 l_iban_len := 19;
487 ELSIF Xi_country_code = 'ES' AND l_iban_len <> 24 THEN
488 l_iban_len := 24;
489 ELSIF Xi_country_code = 'SE' AND l_iban_len <> 24 THEN
490 l_iban_len := 24;
491 ELSIF Xi_country_code = 'CH' AND l_iban_len <> 21 THEN
492 l_iban_len := 21;
493 ELSIF Xi_country_code = 'TN' AND l_iban_len <> 24 THEN
494 l_iban_len := 24;
495 ELSIF Xi_country_code = 'TR' AND l_iban_len <> 26 THEN
496 l_iban_len := 26;
497 ELSIF Xi_country_code = 'GB' AND l_iban_len <> 22 THEN
498 l_iban_len := 22;
499 ELSIF Xi_country_code = 'HR' AND l_iban_len <> 21 THEN
500 l_iban_len := 21;
501 ELSIF Xi_country_code = 'CZ' AND l_iban_len <> 24 THEN
502 l_iban_len := 24;
503 ELSIF Xi_country_code = 'NO' AND l_iban_len <> 15 THEN
504 l_iban_len := 15;
505 ELSIF l_iban_len > 34 THEN
506 l_country_chk_reqd := false;
507 ELSE
508 l_valid_iban := true;
509 END IF;
510
511 IF (l_valid_iban) THEN
512 xo_return_status := fnd_api.g_ret_sts_success;
513 ELSE
514 xo_return_status := fnd_api.g_ret_sts_error;
515 IF (l_country_chk_reqd) THEN
516 BEGIN
517 SELECT territory_short_name
518 INTO l_country_name
519 FROM fnd_territories_vl
520 WHERE territory_code = Xi_country_code;
521 EXCEPTION
522 WHEN no_data_found THEN
523 l_country_name := Xi_country_code;
524 END;
525 fnd_message.set_name('CE', 'CE_INVALID_IBAN_LEN_EU');
526 fnd_message.set_token('LENGTH', l_iban_len);
527 fnd_message.set_token('COUNTRY_NAME', l_country_name);
528 cep_standard.debug('validate_IBAN: CE_INVALID_IBAN_LEN_EU');
529 ELSE
530 fnd_message.set_name('CE', 'CE_INVALID_IBAN_LEN'); -- Bug 8946879
531 cep_standard.debug('validate_IBAN: CE_INVALID_IBAN_LEN');
532 END IF;
533 fnd_msg_pub.add;
534 END IF;
535 cep_standard.debug('xo_return_status - '||xo_return_status);
536 cep_standard.debug('<<CE_BANK_ACCCOUNT_VALIDATION.validate_iban_length');
537 END validate_iban_length;
538 /*Bug 16507244 code change 1/2 end*/
539
540 /*=======================================================================+
541 | PUBLIC PROCEDURE validate_IBAN |
542 | |
543 | DESCRIPTION |
544 | Validate IBAN according to IBAN validation rules |
545 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
546 | |
547 | ARGUMENTS |
548 | IN: |
549 | p_IBAN |
550 | OUT: |
551 | p_IBAN_OUT |
552 +=======================================================================*/
553 PROCEDURE validate_IBAN (p_IBAN IN VARCHAR2,
554 p_IBAN_OUT OUT NOCOPY VARCHAR2,
555 x_return_status IN OUT NOCOPY VARCHAR2 ) IS
556 l_var VARCHAR2(40);
557 l_temp1 VARCHAR2(4);
558 l_temp2 VARCHAR2(40);
559 l_temp3 VARCHAR2(40);
560 l_temp4 VARCHAR2(100);
561 l_len NUMBER;
562 l_num NUMBER;
563 l_mod NUMBER;
564 l_str1_2 VARCHAR2(4);
565 l_str3_4 VARCHAR2(40);
566 new_IBAN VARCHAR2(100);
567 l_temp4_r VARCHAR2(100);
568 l_digit NUMBER;
569 l_checkSum NUMBER;
570 l_multiplier NUMBER;
571 l_product NUMBER; -- Bug 10363417
572 BEGIN
573 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.validate_IBAN (+)');
574 -- initialize API return status to success.
575 x_return_status := fnd_api.g_ret_sts_success;
576
577 -- step 1: remove spaces from the left and right only for p_IBAN
578 -- spaces in the middle are not removed
579
580 new_IBAN :=rtrim(ltrim(p_IBAN,' '),' ');
581 p_IBAN_OUT := null;
582
583 cep_standard.debug('p_IBAN='||p_IBAN);
584 cep_standard.debug('new_IBAN='||new_IBAN);
585 cep_standard.debug('p_IBAN_OUT='||p_IBAN_OUT);
586
587 l_var := '9';
588 l_len := LENGTH(new_IBAN);
589 --
590 -- step 2
591 -- bug 4350134
592 -- 1) The first 2 characters are letters
593 -- 2) The third and fourth characters are numbers
594 l_str1_2 := SUBSTR(new_IBAN, 1, 2);
595 l_str3_4 := upper(SUBSTR(new_IBAN, 3, 2));
596
597 cep_standard.debug('l_str1_2='||l_str1_2);
598 cep_standard.debug('l_str3_4='||l_str3_4);
599
600 IF (TRANSLATE(l_str1_2, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', '99999999999999999999999999') <> '99') THEN
601 fnd_message.set_name('CE', 'CE_IBAN_FIRST_2_CHAR'); -- Bug 8946879
602 fnd_msg_pub.add;
603 x_return_status := fnd_api.g_ret_sts_error;
604 cep_standard.debug('validate_IBAN: CE_IBAN_FIRST_2_CHAR l_str1_2= '||l_str1_2); -- Bug 8946879
605 END IF;
606
607 IF (TRANSLATE(l_str3_4, '0123456789', '9999999999') <> '99') THEN
608 fnd_message.set_name('CE', 'CE_IBAN_FIRST_34_CHAR'); -- Bug 8946879
609 fnd_msg_pub.add;
610 x_return_status := fnd_api.g_ret_sts_error;
611 cep_standard.debug('validate_IBAN: CE_IBAN_FIRST_34_CHAR l_str3_4= '||l_str3_4); -- Bug 8946879
612 END IF;
613 /*Bug 16507244 code change 2/2*/
614 --
615 -- step 3: IBAN length validation
616 --
617 IF (x_return_status = fnd_api.g_ret_sts_success) then
618 CE_BANK_AND_ACCOUNT_VALIDATION.validate_iban_length(Xi_country_code => l_str1_2,
619 Xi_len => l_len,
620 Xo_return_status => x_return_status);
621 END IF;
622
623 IF (x_return_status = fnd_api.g_ret_sts_success) then
624 IF TRANSLATE(new_IBAN, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
625 '999999999999999999999999999999999999')
626 <> RPAD(l_var, l_len, '9') THEN
627 fnd_message.set_name('CE', 'CE_INVALID_IBAN_FORMAT');
628 fnd_msg_pub.add;
629 x_return_status := fnd_api.g_ret_sts_error;
630 cep_standard.debug('validate_IBAN: CE_INVALID_IBAN_FORMAT');
631 ELSE
632 --
633 l_temp1 := SUBSTR(new_IBAN, 1, 4);
634 l_temp2 := SUBSTR(new_IBAN, 5, l_len);
635 l_temp3 := l_temp2||l_temp1;
636 --
637 l_temp4:= REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
638 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
639 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
640 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(l_temp3,
641 'A', '10'), 'B', '11'), 'C', '12'), 'D', '13'), 'E', '14'),
642 'F', '15'), 'G', '16'), 'H', '17'),'I', '18'), 'J', '19'),
643 'K', '20'), 'L', '21'), 'M', '22'), 'N', '23'), 'O', '24'),
644 'P', '25'), 'Q', '26'), 'R', '27'), 'S', '28'), 'T', '29'),
645 'U', '30'), 'V', '31'), 'W', '32'), 'X', '33'), 'Y', '34'),
646 'Z', '35');
647 --
648 l_num := LENGTH(l_temp4);
649 -- Bug 10363417
650 SELECT reverse(l_temp4) INTO l_temp4_r FROM dual;
651 l_checkSum:=0;
652 l_multiplier:=1;
653 l_product:=1;
654 FOR i IN 1..l_num
655 LOOP
656 l_digit := SUBSTR(l_temp4_r,i,1);
657 l_product := (l_multiplier*l_digit);
658 l_multiplier:= MOD((l_multiplier*10), 97);
659 l_checkSum:= (l_checkSum+l_product);
660 END LOOP;
661
662 -- Bug 10363417
663 l_mod := MOD(l_checkSum, 97);
664 --
665 IF l_mod <> 1 THEN
666 fnd_message.set_name('CE', 'CE_INVALID_IBAN_CHKSUM'); -- Bug 8946879
667 fnd_msg_pub.add;
668 x_return_status := fnd_api.g_ret_sts_error;
669 cep_standard.debug('validate_IBAN: CE_INVALID_IBAN_CHKSUM'); -- Bug 8946879
670 ELSE
671 -- IF (new_IBAN <> p_IBAN) then Bug 6658562
672 cep_standard.debug('new_IBAN <> p_IBAN');
673 p_IBAN_OUT := new_IBAN;
674 cep_standard.debug('p_IBAN='||p_IBAN);
675 cep_standard.debug('new_IBAN='||new_IBAN);
676 cep_standard.debug('p_IBAN_OUT='||p_IBAN_OUT);
677 -- END IF;
678 END IF;
679 END IF;
680 cep_standard.debug('end');
681 cep_standard.debug('p_IBAN='||p_IBAN);
682 cep_standard.debug('new_IBAN='||new_IBAN);
683 cep_standard.debug('p_IBAN_OUT='||p_IBAN_OUT);
684 END IF;
685 END validate_IBAN;
686
687 /*=======================================================================+
688 | PUBLIC PROCEDURE validate_account_use |
689 | |
690 | DESCRIPTION |
691 | Validate that at least one account use is selected for the |
692 | bank account |
693 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
694 | |
695 | ARGUMENTS |
696 | IN: |
697 | p_ap, p_ar, p_pay, p_xtr |
698 +=======================================================================*/
699 PROCEDURE validate_account_use(p_ap IN VARCHAR2,
700 p_ar IN VARCHAR2,
701 p_pay IN VARCHAR2,
702 p_xtr IN VARCHAR2,
703 x_return_status IN OUT NOCOPY VARCHAR2 ) IS
704
705
706 BEGIN
707 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_use (+)');
708 -- initialize API return status to success.
709 x_return_status := fnd_api.g_ret_sts_success;
710
711 cep_standard.debug('p_ap='||p_ap);
712 cep_standard.debug('p_ar='||p_ar);
713 cep_standard.debug('p_pay='||p_pay);
714 cep_standard.debug('p_xtr='||p_xtr);
715
716
717 IF (p_ap IS NULL OR p_ap = 'N') AND
718 (p_ar IS NULL OR p_ar = 'N') AND
719 (p_pay IS NULL OR p_pay = 'N') AND
720 (p_xtr IS NULL OR p_xtr = 'N') THEN
721 fnd_message.set_name('CE', 'CE_NO_ACCOUNT_USE');
722 fnd_msg_pub.add;
723 x_return_status := fnd_api.g_ret_sts_error;
724 END IF;
725
726
727 EXCEPTION
728 WHEN OTHERS THEN
729 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
730 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_use');
731 fnd_msg_pub.add;
732
733 END validate_account_use;
734
735 /*=======================================================================+
736 | PUBLIC PROCEDURE validate_short_account_name |
737 | |
738 | DESCRIPTION |
739 | Short Account Name is required when Xtr use is selected for the |
740 | bank account |
741 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
742 | |
743 | ARGUMENTS |
744 | IN: |
745 | p_short_account_name, p_xtr |
746 +=======================================================================*/
747 PROCEDURE validate_short_account_name(p_short_account_name IN VARCHAR2,
748 p_xtr IN VARCHAR2,
749 x_return_status IN OUT NOCOPY VARCHAR2 ) IS
750
751 BEGIN
752 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_use (+)');
753 -- initialize API return status to success.
754 x_return_status := fnd_api.g_ret_sts_success;
755 cep_standard.debug('p_short_account_name='||p_short_account_name);
759 IF (p_short_account_name IS NULL) AND (p_xtr IS NOT NULL) AND (p_xtr <> 'N') THEN
756 cep_standard.debug('p_xtr='||p_xtr);
757
758
760 fnd_message.set_name('CE', 'CE_SHORT_ACCOUNT_NAME_REQUIRED');
761 fnd_msg_pub.add;
762 x_return_status := fnd_api.g_ret_sts_error;
763 END IF;
764
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
769 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_BANK_AND_ACCOUNT_VALIDATION.validate_short_account_name');
770 fnd_msg_pub.add;
771
772 END validate_short_account_name;
773
774 /*=======================================================================+
775 | PUBLIC PROCEDURE validate_end_date |
776 | |
777 | DESCRIPTION |
778 | Validate that the end date is not earlier than the start date |
779 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
780 | |
781 | ARGUMENTS |
782 | IN: |
783 | p_start_date, p_end_date |
784 +=======================================================================*/
785 PROCEDURE validate_end_date(p_start_date IN DATE,
786 p_end_date IN DATE,
787 x_return_status IN OUT NOCOPY VARCHAR2) IS
788
789 BEGIN
790 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.validate_end_date (+)');
791 -- initialize API return status to success.
792 x_return_status := fnd_api.g_ret_sts_success;
793
794
795 IF (p_start_date is not NULL) AND (p_end_date is not NULL) THEN
796 IF p_start_date > p_end_date THEN
797 fnd_message.set_name('CE', 'CE_EARLY_END_DATE');
798 fnd_msg_pub.add;
799 x_return_status := fnd_api.g_ret_sts_error;
800 END IF;
801 END IF;
802
803
804 EXCEPTION
805 WHEN OTHERS THEN
806 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
807 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_BANK_AND_ACCOUNT_VALIDATION.validate_end_date');
808 fnd_msg_pub.add;
809 x_return_status := fnd_api.g_ret_sts_error;
810
811 END validate_end_date;
812
813 /*=======================================================================+
814 | PUBLIC FUNCTION Get_Emp_Name |
815 | |
816 | DESCRIPTION |
817 | Get Employee Name |
818 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
819 | |
820 | ARGUMENTS |
821 | IN: |
822 | p_emp_id |
823 +=======================================================================*/
824 FUNCTION Get_Emp_Name( p_emp_id NUMBER ) RETURN VARCHAR2 IS
825 l_return per_employees_x.full_name%TYPE;
826 BEGIN
827
828 BEGIN
829
830 SELECT full_name
831 INTO l_return
832 FROM per_employees_x
833 WHERE employee_id = p_emp_id;
834
835
836 EXCEPTION
837 WHEN no_data_found THEN
838 l_return := NULL;
839 END;
840
841 RETURN l_return;
842
843 END Get_Emp_Name;
844
845
846 /*=======================================================================+
847 | PUBLIC FUNCTION Get_Org_Type |
848 | |
849 | DESCRIPTION |
850 | Get Organization Type |
851 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
852 | |
853 | ARGUMENTS |
854 | IN: |
855 | p_org_id |
856 +=======================================================================*/
857 FUNCTION Get_Org_Type( p_org_id NUMBER ) RETURN VARCHAR2 IS
858 bg_org hr_lookups.meaning%TYPE;
859 le_org hr_lookups.meaning%TYPE;
860 ou_org hr_lookups.meaning%TYPE;
861 org_type1 VARCHAR2(240);
862 org_type VARCHAR2(240);
863
864 CURSOR bg IS
865 SELECT hl.meaning || ', '
866 FROM hr_organization_information oi,
867 hr_lookups hl
868 WHERE oi.org_information_context = 'CLASS'
869 AND oi.org_information1 = 'HR_BG'
870 and hl.lookup_type = 'ORG_CLASS'
871 AND hl.lookup_code = oi.org_information1
872 and oi.organization_id = p_org_id;
873
874 CURSOR le IS
875 SELECT hl.meaning || ', '
876 FROM hr_organization_information oi,
877 hr_lookups hl
878 WHERE oi.org_information_context = 'CLASS'
879 AND oi.org_information1 = 'HR_LEGAL'
880 and hl.lookup_type = 'ORG_CLASS'
881 AND hl.lookup_code = oi.org_information1
885 SELECT hl.meaning || ', '
882 and oi.organization_id = p_org_id;
883
884 CURSOR ou IS
886 FROM hr_organization_information oi,
887 hr_lookups hl
888 WHERE oi.org_information_context = 'CLASS'
889 AND oi.org_information1 = 'OPERATING_UNIT'
890 and hl.lookup_type = 'ORG_CLASS'
891 AND hl.lookup_code = oi.org_information1
892 and oi.organization_id = p_org_id;
893 --and MO_GLOBAL.CHECK_ACCESS(oi.ORGANIZATION_ID) = 'Y';
894
895 BEGIN
896
897 BEGIN
898 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.get_org_type (+)');
899
900 --/* 1/13/05 no bg
901 OPEN bg;
902
903 FETCH bg INTO bg_org;
904 cep_standard.debug('bg_org='||bg_org);
905 IF bg%NOTFOUND THEN
906 bg_org := null;
907 END IF;
908 CLOSE bg;
909 -- */
910
911 /* 3/24/05 no le
912 OPEN le;
913 FETCH le INTO le_org;
914 cep_standard.debug('le_org='||le_org);
915 IF le%NOTFOUND THEN
916 le_org := null;
917 END IF;
918 CLOSE le;
919 */
920 OPEN ou;
921 FETCH ou INTO ou_org;
922 cep_standard.debug('ou_org='||ou_org);
923 IF ou%NOTFOUND THEN
924 ou_org := null;
925 END IF;
926 CLOSE ou;
927
928 --org_type1 := bg_org || le_org || ou_org;
929 --org_type1 := le_org || ou_org;
930 org_type1 := bg_org || ou_org;
931
932 org_type := substr(org_type1,1,(length(org_type1)-2));
933
934 cep_standard.debug('org_type='||org_type);
935
936 EXCEPTION
937 WHEN no_data_found THEN
938 org_type := NULL;
939 END;
940
941
942 RETURN org_type;
943
944 EXCEPTION
945 WHEN OTHERS THEN
946 cep_standard.debug('EXCEPTION: Get_Org_Type');
947 RAISE;
948 END Get_Org_Type;
949
950
951 /*=======================================================================+
952 | PUBLIC FUNCTION Get_Org_Type_Code |
953 | |
954 | DESCRIPTION |
955 | Get Organization Type Code. Used in System Parameters. |
956 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
957 | |
958 | ARGUMENTS |
959 | IN: |
960 | p_org_id |
961 +=======================================================================*/
962 FUNCTION Get_Org_Type_Code( p_org_id NUMBER ) RETURN VARCHAR2 IS
963 org_type1 VARCHAR2(240);
964 org_type VARCHAR2(240);
965 bg_org VARCHAR2(50);
966 ou_org VARCHAR2(50);
967
968 CURSOR bg IS
969 SELECT 'BG'
970 FROM ce_security_profiles_v
971 WHERE organization_type = 'BUSINESS_GROUP'
972 AND organization_id = p_org_id;
973
974 CURSOR ou IS
975 SELECT 'OU'
976 FROM ce_security_profiles_v
977 WHERE organization_type = 'OPERATING_UNIT'
978 AND organization_id = p_org_id;
979
980 BEGIN
981 BEGIN
982 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.get_org_type_code (+)');
983
984 OPEN bg;
985
986 FETCH bg INTO bg_org;
987 cep_standard.debug('bg_org='||bg_org);
988 IF bg%NOTFOUND THEN
989 bg_org := null;
990 END IF;
991 CLOSE bg;
992
993 OPEN ou;
994 FETCH ou INTO ou_org;
995 cep_standard.debug('ou_org='||ou_org);
996 IF ou%NOTFOUND THEN
997 ou_org := null;
998 END IF;
999 CLOSE ou;
1000
1001 org_type := bg_org || ou_org;
1002
1003 --org_type := substr(org_type1,1,(length(org_type1)-2));
1004
1005 cep_standard.debug('org_type='||org_type);
1006
1007 EXCEPTION
1008 WHEN no_data_found THEN
1009 org_type := NULL;
1010 END;
1011
1012
1013 RETURN org_type;
1014
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 cep_standard.debug('EXCEPTION: Get_Org_Type_Code');
1018 RAISE;
1019 END Get_Org_Type_Code;
1020
1021
1022 /*=======================================================================+
1023 | PUBLIC PROCEDURE validate_account_access_org |
1024 | |
1025 | DESCRIPTION |
1026 | Validate that the account use is valid for the org |
1027 |
1028 | Validate organization use
1029 | Access Org | Org can be | Org cannot be
1030 | Classification | use in | use In
1031 | --------------------------------------------------------------
1032 | LE | XTR | AP, AR, PAY
1033 | BG | PAY | AR, AP, XTR
1034 | OU | AP, AR | PAY, XTR
1035 | BG and OU | AP, AR, PAY | XTR
1036 | |
1037 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1038 | |
1042 +=======================================================================*/
1039 | ARGUMENTS |
1040 | IN: |
1041 | p_ap, p_ar, p_pay, p_xtr, p_org_id |
1043 PROCEDURE validate_account_access_org(p_ap IN VARCHAR2,
1044 p_ar IN VARCHAR2,
1045 p_pay IN VARCHAR2,
1046 p_xtr IN VARCHAR2,
1047 p_org_type IN VARCHAR2,
1048 p_org_id IN NUMBER,
1049 x_return_status IN OUT NOCOPY VARCHAR2 ) IS
1050
1051 org_type VARCHAR2(240);
1052
1053 BEGIN
1054 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_use (+)');
1055 -- initialize API return status to success.
1056 x_return_status := fnd_api.g_ret_sts_success;
1057
1058 cep_standard.debug('p_ap='||p_ap);
1059 cep_standard.debug('p_ar='||p_ar);
1060 cep_standard.debug('p_pay='||p_pay);
1061 cep_standard.debug('p_xtr='||p_xtr);
1062 cep_standard.debug('p_org_id='||p_org_id);
1063
1064 /*// orgType: LE - Legal Entity
1065 // BG - Business Group
1066 // OU - Operating Unit
1067 // BGOU - Business Group and Operating Unit*/
1068
1069 IF (p_org_type = 'LE') AND
1070 (((p_ap IS not NULL) and (p_ap <>'N')) or
1071 ((p_ar IS not NULL) and (p_ar <>'N')) or
1072 ((p_pay IS not NULL) and (p_pay <>'N'))) THEN
1073 fnd_message.set_name('CE', 'CE_LE_ACCESS_ORG');
1074 fnd_msg_pub.add;
1075 x_return_status := fnd_api.g_ret_sts_error;
1076 ELSIF (p_org_type = 'BG') AND
1077 (((p_ap IS not NULL) and (p_ap <>'N')) or
1078 ((p_ar IS not NULL) and (p_ar <>'N')) or
1079 ((p_xtr IS not NULL) and (p_xtr <>'N'))) THEN
1080 fnd_message.set_name('CE', 'CE_BG_ACCESS_ORG');
1081 fnd_msg_pub.add;
1082 x_return_status := fnd_api.g_ret_sts_error;
1083 ELSIF (p_org_type = 'OU') AND
1084 (((p_xtr IS not NULL) and (p_xtr <>'N')) or
1085 ((p_pay IS not NULL) and (p_pay <>'N'))) THEN
1086 fnd_message.set_name('CE', 'CE_OU_ACCESS_ORG');
1087 fnd_msg_pub.add;
1088 x_return_status := fnd_api.g_ret_sts_error;
1089 ELSIF (p_org_type = 'BGOU') AND
1090 ((p_xtr IS not NULL) and (p_xtr <>'N')) THEN
1091 fnd_message.set_name('CE', 'CE_BGOU_ACCESS_ORG');
1092 fnd_msg_pub.add;
1093 x_return_status := fnd_api.g_ret_sts_error;
1094 END IF;
1095
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
1099 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_access_org');
1100 fnd_msg_pub.add;
1101
1102 END validate_account_access_org;
1103
1104 /*=======================================================================+
1105 | PUBLIC PROCEDURE VALIDATE_ALC |
1106 | |
1107 | DESCRIPTION |
1108 | Validate Agency Location Code |
1109 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1110 | |
1111 | ARGUMENTS |
1112 | IN: |
1113 | ALC_VALUE |
1114 | OUT |
1115 | x_msg_count |
1116 | x_msg_data |
1117 | X_VALUE_OUT |
1118 +=======================================================================*/
1119 PROCEDURE VALIDATE_ALC(ALC_VALUE in varchar2,
1120 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1121 x_msg_count OUT NOCOPY NUMBER,
1122 x_msg_data OUT NOCOPY VARCHAR2,
1123 X_VALUE_OUT OUT NOCOPY VARCHAR2,
1124 x_return_status IN OUT NOCOPY VARCHAR2 )
1125 AS
1126 numeric_alc varchar2(40);
1127 alc_value_len number;
1128 new_alc_value varchar2(40);
1129
1130 BEGIN
1131
1132 -- Initialize message list if p_init_msg_list is set to TRUE.
1133 IF FND_API.to_Boolean(p_init_msg_list) THEN
1134 FND_MSG_PUB.initialize;
1135 END IF;
1136 -- initialize API return status to success.
1137 x_return_status := fnd_api.g_ret_sts_success;
1138
1139
1140 IF (ALC_VALUE is not null) THEN
1141
1142 numeric_alc := ce_check_numeric(ALC_VALUE,1,length(ALC_VALUE));
1143 new_alc_value := replace(ALC_VALUE,' '); --remove spaces
1144 alc_value_len := length(new_alc_value);
1145
1146 IF numeric_alc = '0' then
1147 IF (alc_value_len < 9) then
1148 If (alc_value_len <> 8) then
1149 x_value_out := lpad(new_alc_value,8,0);
1150 end if;
1151 else
1152 FND_MESSAGE.set_name('CE','CE_ALC_VALUE_TOO_LONG');
1153 fnd_msg_pub.add;
1154 x_return_status := fnd_api.g_ret_sts_error;
1155 END if;
1156 ELSE
1157 FND_MESSAGE.set_name('CE','CE_ALC_NUMERIC_VALUE_ONLY');
1158 fnd_msg_pub.add;
1159 x_return_status := fnd_api.g_ret_sts_error;
1160 END IF;
1161
1162 END IF;
1163
1164 FND_MSG_PUB.Count_And_Get(
1168
1165 p_encoded => FND_API.G_FALSE,
1166 p_count => x_msg_count,
1167 p_data => x_msg_data);
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171
1172 FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
1173 fnd_message.set_token('PROCEDURE', 'CE_BANK_AND_ACCOUNT_VALIDATION.validate_alc');
1174 fnd_msg_pub.add;
1175 RAISE;
1176
1177 END VALIDATE_ALC;
1178
1179 /*=======================================================================+
1180 | PUBLIC PROCEDURE validate_country |
1181 | |
1182 | DESCRIPTION |
1183 | Check to see that the country specified is defined in |
1184 | territories. |
1185 | |
1186 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1187 | |
1188 | ARGUMENTS |
1189 | IN: |
1190 | p_country_code |
1191 | OUT |
1192 | x_return_status |
1193 | |
1194 | MODIFICATION HISTORY |
1195 | 27-NOV-2001 Xin Wang Created. |
1196 +=======================================================================*/
1197 PROCEDURE validate_country (
1198 p_country_code IN VARCHAR2,
1199 x_return_status IN OUT NOCOPY VARCHAR2
1200 ) IS
1201 CURSOR c_country IS
1202 SELECT 1
1203 FROM fnd_territories ft
1204 WHERE ft.territory_code = p_country_code;
1205 l_dummy NUMBER(1) := 0;
1206 BEGIN
1207 IF l_DEBUG in ('Y', 'C') THEN
1208 cep_standard.debug('>>CE_BANK_AND_ACCOUNT_VALIDATION.validate_country.');
1209 END IF;
1210
1211 OPEN c_country;
1212 FETCH c_country INTO l_dummy;
1213 IF c_country%NOTFOUND THEN
1214 fnd_message.set_name('CE', 'CE_API_INVALID_COUNTRY');
1215 fnd_msg_pub.add;
1216 x_return_status := fnd_api.g_ret_sts_error;
1217 END IF;
1218 CLOSE c_country;
1219
1220 IF l_DEBUG in ('Y', 'C') THEN
1221 cep_standard.debug('<<CE_BANK_AND_ACCOUNT_VALIDATION.validate_country.');
1222 END IF;
1223 END validate_country;
1224
1225 /*=======================================================================+
1226 | PUBLIC PROCEDURE validate_def_settlement |
1227 | |
1228 | DESCRIPTION |
1229 | Allow only one account per currency and account use (AP or XTR) |
1230 | to be flagged as the default settlement account for each LE or OU |
1231 | |
1232 | Possible combination: |
1233 | LE1, USD, AP USE, BANK ACCOUNT 1 |
1234 | LE1, USD, XTR USE, BANK ACCOUNT 2 |
1235 | OU1, USD, AP USE, BANK ACCOUNT 1 |
1236 | OU1, USD, XTR USE, BANK ACCOUNT 1 |
1237 | |
1238 | |
1239 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1240 | |
1241 | ARGUMENTS |
1242 | IN: |
1243 | p_bank_account_id - required |
1244 | p_bank_acct_use_id - required |
1245 | p_org_id - required |
1246 | p_ap_def_settlement, p_xtr_def_settlement, p_init_msg_list |
1247 | OUT |
1248 | x_return_status |
1249 | x_msg_count |
1250 | x_msg_data |
1251 | |
1252 | MODIFICATION HISTORY |
1253 | 21-DEC-2004 lkwan Created. |
1254 +=======================================================================*/
1255 PROCEDURE validate_def_settlement(
1256 p_bank_account_id IN number,
1257 p_bank_acct_use_id IN number,
1258 p_org_id IN number,
1259 p_ap_def_settlement in VARCHAR2,
1260 p_xtr_def_settlement in VARCHAR2,
1261 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1262 x_msg_count OUT NOCOPY NUMBER,
1263 x_msg_data OUT NOCOPY VARCHAR2,
1264 x_return_status IN OUT NOCOPY VARCHAR2) IS
1265 p_cur varchar2(5);
1266 p_ap_def number;
1267 p_xtr_def number;
1268 BEGIN
1269 cep_standard.debug('>>CE_BANK_AND_ACCOUNT_VALIDATION.validate_def_settlement');
1270 -- initialize API return status to success.
1274 IF FND_API.to_Boolean(p_init_msg_list) THEN
1271 x_return_status := fnd_api.g_ret_sts_success;
1272
1273 -- Initialize message list if p_init_msg_list is set to TRUE.
1275 FND_MSG_PUB.initialize;
1276 END IF;
1277 IF l_DEBUG in ('Y', 'C') THEN
1278 cep_standard.debug(' P_INIT_MSG_LIST: '|| P_INIT_MSG_LIST);
1279 END IF;
1280
1281 IF (p_bank_account_id is not null) then
1282 select currency_code into p_cur
1283 from ce_bank_accounts
1284 where bank_account_id = p_bank_account_id;
1285 IF (P_CUR IS not NULL) THEN
1286 --IF (p_bank_acct_use_id is null) then
1287 -- FND_MESSAGE.set_name('CE','CE_BANK_ACCT_USE_ID_REQUIRED');
1288 -- fnd_msg_pub.add;
1289 --ELSE
1290
1291 IF (p_ap_def_settlement = 'Y') THEN
1292 select count(*) into p_ap_def
1293 from ce_bank_accounts ba, ce_bank_acct_uses_all bau
1294 where ba.bank_account_id = bau.bank_account_id
1295 and ba.currency_code = p_cur
1296 and nvl(bau.org_id, bau.LEGAL_ENTITY_ID) = p_org_id
1297 and nvl(bau.AP_DEFAULT_SETTLEMENT_FLAG,'N') = 'Y'
1298 and bau.bank_acct_use_id <> nvl(p_bank_acct_use_id, bau.bank_acct_use_id);
1299 END IF;
1300 IF (p_xtr_def_settlement = 'Y') THEN
1301 select count(*) into p_xtr_def
1302 from ce_bank_accounts ba, ce_bank_acct_uses_all bau
1303 where ba.bank_account_id = bau.bank_account_id
1304 and ba.currency_code = p_cur
1305 and nvl(bau.org_id, bau.LEGAL_ENTITY_ID) = p_org_id
1306 and nvl(bau.XTR_DEFAULT_SETTLEMENT_FLAG,'N') = 'Y'
1307 and bau.bank_acct_use_id <> nvl(p_bank_acct_use_id, bau.bank_acct_use_id) ;
1308 END IF;
1309
1310 IF (p_ap_def > 0) THEN
1311 FND_MESSAGE.set_name('CE','CE_AP_SETTLEMENT_EXIST_ORG');
1312 fnd_msg_pub.add;
1313 END IF;
1314
1315 IF (p_xtr_def > 0) THEN
1316 FND_MESSAGE.set_name('CE','CE_XTR_SETTLEMENT_EXIST_ORG');
1317 fnd_msg_pub.add;
1318 END IF;
1319 --END IF; --p_bank_acct_use_id
1320 ELSE
1321 FND_MESSAGE.set_name('CE','CE_BANK_ACCOUNT_ID_INVALID');
1322 fnd_msg_pub.add;
1323 END IF;
1324
1325 ELSE
1326 FND_MESSAGE.set_name('CE','CE_BANK_ACCOUNT_ID_REQUIRED');
1327 fnd_msg_pub.add;
1328 END IF;
1329
1330 FND_MSG_PUB.Count_And_Get(
1331 p_encoded => FND_API.G_FALSE,
1332 p_count => x_msg_count,
1333 p_data => x_msg_data);
1334
1335 IF x_msg_count > 0 THEN
1336 x_return_status := fnd_api.g_ret_sts_error;
1337 END IF;
1338 --IF l_DEBUG in ('Y', 'C') THEN
1339 cep_standard.debug(' P_COUNT: '|| x_msg_count);
1340
1341
1342 cep_standard.debug('<<CE_BANK_AND_ACCOUNT_VALIDATION.validate_def_settlement');
1343 --END IF;
1344
1345 EXCEPTION
1346 WHEN OTHERS THEN
1347
1348 FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
1349 fnd_message.set_token('PROCEDURE', 'CE_BANK_AND_ACCOUNT_VALIDATION.validate_def_settlement');
1350 fnd_msg_pub.add;
1351 RAISE;
1352
1353 END validate_def_settlement;
1354
1355 /*=======================================================================+
1356 | PUBLIC FUNCTION get_masked_account_num |
1357 | |
1358 | DESCRIPTION |
1359 | Passing the bank_account_num and return the |
1360 | masked bank_account_num based on the profile option: |
1361 | CE: MASK INTERNAL BANK ACCOUNT NUMBERS |
1362 | (CE_MASK_INTERNAL_BANK_ACCT_NUM) |
1363 | |
1364 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1365 | |
1366 | ARGUMENTS |
1367 | IN: |
1368 | p_bank_account_num |
1369 | |
1370 | |
1371 | MODIFICATION HISTORY |
1372 | 23-NOV-2004 lkwan Created. |
1373 +=======================================================================*/
1374 /*
1375 FUNCTION get_masked_account_num (
1376 p_bank_account_num IN VARCHAR2,
1377 p_acct_class IN VARCHAR2
1378 ) RETURN VARCHAR2
1379 IS
1380 masked_account_num VARCHAR2(100);
1381 ba_num_length number;
1382 ba_num_masked_start number;
1383 mask_ba_num_option varchar2(1);
1384
1385 BEGIN
1386 IF p_acct_class is not null THEN
1387 IF p_acct_class in ( 'INTERNAL', 'BOTH') THEN
1388 mask_ba_num_option := NVL(FND_PROFILE.value('CE_MASK_INTERNAL_BANK_ACCT_NUM'), 'LAST FOUR VISIBLE');
1389 ELSIF p_acct_class = 'EXTERNAL' THEN
1390 mask_ba_num_option := NVL(FND_PROFILE.value('CE_MASK_EXTERNAL_BANK_ACCT_NUM'), 'LAST FOUR VISIBLE');
1391 END IF;
1392 END IF;
1393 IF p_bank_account_num is not null THEN
1394 ba_num_length := length(p_bank_account_num);
1395 ba_num_masked_start := (ba_num_length - 3);
1396
1397 IF mask_ba_num_option = 'LAST FOUR VISIBLE' THEN
1398 masked_account_num := substr(p_bank_account_num, ba_num_masked_start,ba_num_length );
1399 masked_account_num := lpad(masked_account_num, ba_num_length, 'X' );
1400 END IF;
1401
1402 END IF;
1403
1407 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
1404 RETURN(masked_account_num);
1405 EXCEPTION
1406 WHEN OTHERS THEN
1408 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_BANK_AND_ACCOUNT_VALIDATION.get_masked_account_num');
1409 fnd_msg_pub.add;
1410 RAISE;
1411 END get_masked_account_num;
1412 */
1413
1414 /*=======================================================================+
1415 | PUBLIC PROCEDURE validate_unique_org_access |
1416 | |
1417 | DESCRIPTION |
1418 | The combination or bank_account_id and org_id/legal_entity_id in |
1419 | in CE_BANK_ACCT_USES_ALL should be unique. |
1420 | |
1421 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1422 | |
1423 | ARGUMENTS |
1424 | IN: |
1425 | p_org_le_id |
1426 | p_bank_account_id |
1427 | OUT |
1428 | x_return_status |
1429 | |
1430 | MODIFICATION HISTORY |
1431 | 22-JUN-2005 Xin Wang Created. |
1432 +=======================================================================*/
1433 PROCEDURE validate_unique_org_access (
1434 p_org_le_id IN NUMBER,
1435 p_bank_account_id IN NUMBER,
1436 p_acct_use_id IN NUMBER,
1437 x_return_status IN OUT NOCOPY VARCHAR2
1438 ) IS
1439 l_dummy VARCHAR2(1);
1440 CURSOR c_acct_access IS
1441 SELECT 'X'
1442 FROM ce_bank_acct_uses_all
1443 WHERE bank_account_id = p_bank_account_id
1444 AND NVL(org_id, legal_entity_id) = p_org_le_id
1445 AND bank_acct_use_id <> NVL(p_acct_use_id, -1);
1446
1447 BEGIN
1448 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.validate_unique_org_access(+)');
1449
1450 -- initialize API return status to success.
1451 x_return_status := fnd_api.g_ret_sts_success;
1452
1453 OPEN c_acct_access;
1454 FETCH c_acct_access INTO l_dummy;
1455 IF l_dummy = 'X' THEN
1456 fnd_message.set_name('CE', 'CE_DUP_ACCT_ORG_ACCESS');
1457 fnd_msg_pub.add;
1458 x_return_status := fnd_api.g_ret_sts_error;
1459 END IF;
1460 CLOSE c_acct_access;
1461
1462 EXCEPTION
1463 WHEN OTHERS THEN
1464 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
1465 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_BANK_AND_ACCOUNT_VALIDATION.validate_unique_org_access');
1466 fnd_msg_pub.add;
1467
1468 END validate_unique_org_access;
1469
1470
1471 /*=======================================================================+
1472 | PUBLIC PROCEDURE get_pay_doc_cat |
1473 | |
1474 | DESCRIPTION |
1475 | Obtaining the correct document category will be a hierarchical |
1476 | approach: |
1477 | 1) payment document, |
1478 | 2) bank account use/payment method, |
1479 | 3) bank account use |
1480 | |
1481 | ARGUMENTS |
1482 | IN: |
1483 | P_PAYMENT_DOCUMENT_ID |
1484 | P_PAYMENT_METHOD_CODE |
1485 | P_BANK_ACCT_USE_ID |
1486 | OUT |
1487 | P_PAYMENT_DOC_CATEGORY_CODE ("-1" if no category code is defined) |
1488 | |
1489 | MODIFICATION HISTORY |
1490 | 21-FEB-2006 lkwan Created. |
1491 +=======================================================================*/
1492 PROCEDURE get_pay_doc_cat(
1493 P_PAYMENT_DOCUMENT_ID IN number,
1494 P_PAYMENT_METHOD_CODE IN VARCHAR2,
1495 P_BANK_ACCT_USE_ID IN number,
1496 P_PAYMENT_DOC_CATEGORY_CODE OUT NOCOPY VARCHAR2) IS
1497 p_pay_doc_count number;
1498 p_ap_doc_cat_count number;
1499
1500 BEGIN
1501 cep_standard.debug('>>CE_BANK_AND_ACCOUNT_VALIDATION.get_pay_doc_cat');
1502 cep_standard.debug('P_PAYMENT_DOCUMENT_ID=' ||P_PAYMENT_DOCUMENT_ID
1503 ||', P_PAYMENT_METHOD_CODE='||P_PAYMENT_METHOD_CODE
1504 ||', P_BANK_ACCT_USE_ID='||P_BANK_ACCT_USE_ID );
1505
1506 P_PAYMENT_DOC_CATEGORY_CODE := '-1';
1507
1508 IF (P_PAYMENT_DOCUMENT_ID is not null) THEN
1509 select count(*) into p_pay_doc_count
1510 from ce_payment_documents
1511 where PAYMENT_DOCUMENT_ID = P_PAYMENT_DOCUMENT_ID;
1512
1513 cep_standard.debug('p_pay_doc_count='||p_pay_doc_count );
1514
1515 if (p_pay_doc_count = 1) then
1516 select nvl(PAYMENT_DOC_CATEGORY, '-1')
1517 into P_PAYMENT_DOC_CATEGORY_CODE
1518 from ce_payment_documents
1519 where PAYMENT_DOCUMENT_ID = P_PAYMENT_DOCUMENT_ID;
1520 else
1521 P_PAYMENT_DOC_CATEGORY_CODE := '-1';
1522 end if;
1523 END IF;
1524
1525 cep_standard.debug('ce_payment_documents P_PAYMENT_DOC_CATEGORY_CODE='||P_PAYMENT_DOC_CATEGORY_CODE );
1526
1527 IF (P_PAYMENT_DOC_CATEGORY_CODE = '-1') THEN
1528 IF (P_PAYMENT_METHOD_CODE is not null and P_BANK_ACCT_USE_ID is not null) THEN
1529 select count(*) into p_ap_doc_cat_count
1530 from ce_ap_pm_doc_categories
1531 where BANK_ACCT_USE_ID = P_BANK_ACCT_USE_ID
1532 and PAYMENT_METHOD_CODE = P_PAYMENT_METHOD_CODE;
1533
1534 if (p_ap_doc_cat_count = 1) then
1535 select nvl(PAYMENT_DOC_CATEGORY, '-1')
1536 into P_PAYMENT_DOC_CATEGORY_CODE
1537 from ce_ap_pm_doc_categories
1538 where BANK_ACCT_USE_ID = P_BANK_ACCT_USE_ID
1539 and PAYMENT_METHOD_CODE = P_PAYMENT_METHOD_CODE;
1540 else
1541 P_PAYMENT_DOC_CATEGORY_CODE := '-1';
1542 end if;
1543
1544 cep_standard.debug('ce_ap_pm_doc_categories P_PAYMENT_DOC_CATEGORY_CODE='||P_PAYMENT_DOC_CATEGORY_CODE );
1545
1546 END IF;
1547 END IF;
1548
1549
1550 IF (P_PAYMENT_DOC_CATEGORY_CODE = '-1') THEN
1551 IF ( P_BANK_ACCT_USE_ID is not null) THEN
1552 select nvl(PAYMENT_DOC_CATEGORY, '-1')
1553 into P_PAYMENT_DOC_CATEGORY_CODE
1554 from ce_bank_acct_uses_all
1555 where BANK_ACCT_USE_ID = P_BANK_ACCT_USE_ID;
1556 cep_standard.debug('ce_bank_acct_uses_all P_PAYMENT_DOC_CATEGORY_CODE='||P_PAYMENT_DOC_CATEGORY_CODE );
1557 END IF;
1558 END IF;
1559
1560 cep_standard.debug('P_PAYMENT_DOC_CATEGORY_CODE='||P_PAYMENT_DOC_CATEGORY_CODE );
1561
1562 cep_standard.debug('<<CE_BANK_AND_ACCOUNT_VALIDATION.get_pay_doc_cat');
1563
1564 EXCEPTION
1565 WHEN NO_DATA_FOUND THEN
1566 cep_standard.debug('no data found');
1567 P_PAYMENT_DOC_CATEGORY_CODE := '-1';
1568 WHEN TOO_MANY_ROWS THEN
1569 cep_standard.debug('too_many_rows');
1570 P_PAYMENT_DOC_CATEGORY_CODE := '-1';
1571 WHEN OTHERS THEN
1572 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
1573 FND_MESSAGE.Set_Token('PROCEDURE', 'CE_BANK_AND_ACCOUNT_VALIDATION.get_pay_doc_cat');
1574 fnd_msg_pub.add;
1575
1576 END get_pay_doc_cat;
1577
1578 /*=======================================================================+
1579 | PUBLIC FUNCTION Get_Org_Type_Code_Isetup |
1580 | |
1581 | DESCRIPTION |
1582 | Get Organization Type Code. Used in System Parameters. |
1583 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1584 | |
1585 | ARGUMENTS |
1586 | IN: |
1587 | p_org_id |
1588 | Function added for the bug 7713625 |
1589 +=======================================================================*/
1590
1591 FUNCTION Get_Org_Type_Code_Isetup( p_org_id NUMBER ) RETURN VARCHAR2 IS
1592 org_type VARCHAR2(240);
1593 bg_org VARCHAR2(50);
1594 ou_org VARCHAR2(50);
1595
1596 CURSOR bg IS
1597 SELECT 'BG'
1598 FROM hr_organization_information oi,
1599 hr_lookups hl
1600 WHERE oi.org_information_context = 'CLASS'
1601 AND oi.org_information1 = 'HR_BG'
1602 and hl.lookup_type = 'ORG_CLASS'
1603 AND hl.lookup_code = oi.org_information1
1604 and oi.organization_id = p_org_id;
1605
1606 CURSOR ou IS
1607 SELECT 'OU'
1608 FROM hr_organization_information oi,
1609 hr_lookups hl
1610 WHERE oi.org_information_context = 'CLASS'
1611 AND oi.org_information1 = 'OPERATING_UNIT'
1612 and hl.lookup_type = 'ORG_CLASS'
1613 AND hl.lookup_code = oi.org_information1
1614 and oi.organization_id = p_org_id;
1615
1616 BEGIN
1617 BEGIN
1618 cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.get_org_type_code_isetup (+)');
1619
1620 OPEN bg;
1621
1622 FETCH bg INTO bg_org;
1623 cep_standard.debug('bg_org='||bg_org);
1624 IF bg%NOTFOUND THEN
1625 bg_org := null;
1626 END IF;
1627 CLOSE bg;
1628
1629 OPEN ou;
1630 FETCH ou INTO ou_org;
1631 cep_standard.debug('ou_org='||ou_org);
1632 IF ou%NOTFOUND THEN
1633 ou_org := null;
1634 END IF;
1635 CLOSE ou;
1636
1637 org_type := bg_org || ou_org;
1638
1639 cep_standard.debug('org_type='||org_type);
1640
1641 EXCEPTION
1642 WHEN no_data_found THEN
1643 org_type := NULL;
1644 END;
1645
1646
1647 RETURN org_type;
1648
1649 EXCEPTION
1650 WHEN OTHERS THEN
1651 cep_standard.debug('EXCEPTION: Get_Org_Type_Code_Isetup');
1652 RAISE;
1653 END Get_Org_Type_Code_Isetup;
1654
1655 END CE_BANK_AND_ACCOUNT_VALIDATION;