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