[Home] [Help]
PACKAGE BODY: APPS.IBY_EXT_BANKACCT_PUB
Source
1 PACKAGE BODY IBY_EXT_BANKACCT_PUB AS
2 /*$Header: ibyxbnkb.pls 120.82.12020000.5 2012/10/15 08:53:59 asarada ship $*/
3
4 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 --
7 -- Forward Declarations
8 --
9 FUNCTION get_country (
10 p_bank_id IN NUMBER,
11 x_return_status IN OUT NOCOPY VARCHAR2
12 ) RETURN VARCHAR2;
13
14 PROCEDURE find_bank_info (
15 p_branch_id IN NUMBER,
16 x_return_status IN OUT NOCOPY VARCHAR2,
17 x_bank_id OUT NOCOPY NUMBER,
18 x_country_code OUT NOCOPY VARCHAR2,
19 x_bank_name OUT NOCOPY VARCHAR2,
20 x_bank_number OUT NOCOPY VARCHAR2
21 );
22
23 PROCEDURE print_debuginfo(
24 p_message IN VARCHAR2,
25 p_prefix IN VARCHAR2 DEFAULT 'DEBUG',
26 p_msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
27 p_module IN VARCHAR2 DEFAULT G_DEBUG_MODULE
28 );
29
30 PROCEDURE check_mandatory(
31 p_field IN VARCHAR2,
32 p_value IN VARCHAR2
33 );
34
35 --
36 -- USE: Gets credit card mask settings
37 --
38 PROCEDURE Get_Mask_Settings
39 (x_mask_setting OUT NOCOPY iby_sys_security_options.ext_ba_mask_setting%TYPE,
40 x_unmask_len OUT NOCOPY iby_sys_security_options.ext_ba_unmask_len%TYPE
41 );
42
43
44 /*======================================================================
45 * APIs defined in this package
46 *
47 * 1. create_ext_bank
48 * 2. update_ext_bank
49 * 3. set_ext_bank_end_date
50 * 4. check_ext_bank_exist
51 * 5. create_bank_branch
52 * 6. update_bank_branch
53 * 7. set_bank_branch_end_date
54 * 8. check_ext_bank_branch_exist
55 * 9. create_ext_bank_acct
56 * 10. update_ext_bank_acct
57 * 11. set_ext_bank_acct_dates
58 * 12. check_ext_acct_exist
59 * 13. get_ext_bank_acct
60 * 14. get_ext_bank_acct
61 * 15. create_intermediary_acct
62 * 16. update_intermediary_acct
63 * 17. add_joint_acct_owner
64 * 18. set_joint_acct_owner_end_date
65 * 19. change_primary_acct_owner
66 * 20. check_bank_acct_owner
67 +====================================================================*/
68
69 -- 1. create_ext_bank
70 --
71 -- API name : create_ext_bank
72 -- Type : Public
73 -- Pre-reqs : None
74 -- Function : Creates an external bank
75 -- Current version : 1.0
76 -- Previous version: 1.0
77 -- Initial version : 1.0
78 --
79 PROCEDURE create_ext_bank (
80 p_api_version IN NUMBER,
81 p_init_msg_list IN VARCHAR2,
82 p_ext_bank_rec IN ExtBank_rec_type,
83 x_bank_id OUT NOCOPY NUMBER,
84 x_return_status OUT NOCOPY VARCHAR2,
85 x_msg_count OUT NOCOPY NUMBER,
86 x_msg_data OUT NOCOPY VARCHAR2,
87 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
88 ) IS
89
90 l_api_name CONSTANT VARCHAR2(30) := 'create_ext_bank';
91 l_api_version CONSTANT NUMBER := 1.0;
92 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
93 l_dup_bank_id number;
94 l_dup_end_date date;
95 BEGIN
96 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
97 print_debuginfo('Enter ' || l_module_name);
98
99 END IF;
100 SAVEPOINT create_bank_pub;
101
102 -- Standard call to check for call compatibility.
103 IF NOT FND_API.Compatible_API_Call(l_api_version,
104 p_api_version,
105 l_api_name,
106 G_PKG_NAME) THEN
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END IF;
109
110 -- Initialize message list if p_init_msg_list is set to TRUE.
111 IF FND_API.to_Boolean(p_init_msg_list) THEN
112 FND_MSG_PUB.initialize;
113 END IF;
114
115 -- Initialize API return status to success
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117
118 -- Start of API body
119
120 -- Parameter validations
121 check_mandatory('IBY_COUNTRY_CD_FIELD',p_ext_bank_rec.country_code);
122 check_mandatory('IBY_BANK_NAME_FIELD',p_ext_bank_rec.bank_name);
123
124 -- Other Needed Validations
125 -- 1. Country Specific validations: Perfomed by CE
126 -- 2. 3 generic Validations
127 -- i. Country and Bank Name combination must be unique
128 -- ii. Combination of Country and Short Bank Name must be unique
129 -- iii. Country and Bank Number must be unique
130 --
131 /*
132 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
133 print_debuginfo('Before Call to ''ce_bank_pub.check_bank_exists''');
134 END IF;
135 -- Call CE API to check bank exists
136
137
138
139 ce_bank_pub.check_bank_exist(p_ext_bank_rec.country_code,
140 p_ext_bank_rec.bank_name,
141 p_ext_bank_rec.bank_number,
142 l_dup_bank_id,
143 l_dup_end_date);
144
145 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
146 print_debuginfo('Duplicate Bank Id : ' || l_dup_bank_id);
147 END IF;
148 -- End of API body
149 */
150
151 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
152 print_debuginfo('Before Call to ''ce_bank_pub.create_bank''');
153 END IF;
154 -- Call CE API to create bank
155 ce_bank_pub.create_bank(p_init_msg_list,
156 p_ext_bank_rec.country_code,
157 p_ext_bank_rec.bank_name,
158 p_ext_bank_rec.bank_number,
159 p_ext_bank_rec.bank_alt_name,
160 p_ext_bank_rec.bank_short_name, -- p_short_bank_name
161 p_ext_bank_rec.description,
162 p_ext_bank_rec.tax_payer_id, -- p_tax_payer_id
163 p_ext_bank_rec.tax_registration_number, -- p_tax_registration_number
164 p_ext_bank_rec.attribute_category, -- p_attribute_category
165 p_ext_bank_rec.attribute1, -- p_attribute1
166 p_ext_bank_rec.attribute2, -- p_attribute2
167 p_ext_bank_rec.attribute3, -- p_attribute3
168 p_ext_bank_rec.attribute4, -- p_attribute4
169 p_ext_bank_rec.attribute5, -- p_attribute5
170 p_ext_bank_rec.attribute6, -- p_attribute6
171 p_ext_bank_rec.attribute7, -- p_attribute7
172 p_ext_bank_rec.attribute8, -- p_attribute8
173 p_ext_bank_rec.attribute9, -- p_attribute9
174 p_ext_bank_rec.attribute10, -- p_attribute10
175 p_ext_bank_rec.attribute11, -- p_attribute11
176 p_ext_bank_rec.attribute12, -- p_attribute12
177 p_ext_bank_rec.attribute13, -- p_attribute13
178 p_ext_bank_rec.attribute14, -- p_attribute14
179 p_ext_bank_rec.attribute15, -- p_attribute15
180 p_ext_bank_rec.attribute16, -- p_attribute16
181 p_ext_bank_rec.attribute17, -- p_attribute17
182 p_ext_bank_rec.attribute18, -- p_attribute18
183 p_ext_bank_rec.attribute19, -- p_attribute19
184 p_ext_bank_rec.attribute20, -- p_attribute20
185 p_ext_bank_rec.attribute21, -- p_attribute21
186 p_ext_bank_rec.attribute22, -- p_attribute22
187 p_ext_bank_rec.attribute23, -- p_attribute23
188 p_ext_bank_rec.attribute24, -- p_attribute24
189 x_bank_id, --x_bank_id
190 x_return_status, --x_return_status
191 x_msg_count, --x_msg_count
192 x_msg_data --x_msg_data
193 );
194 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
195 print_debuginfo('Ext Bank Id : ' || x_bank_id);
196 END IF;
197 -- End of API body
198
199 -- get message count and if count is 1, get message info.
200 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
201 p_count => x_msg_count,
202 p_data => x_msg_data);
203
204 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
205 print_debuginfo('RETURN ' || l_module_name);
206
207 END IF;
208 EXCEPTION
209 WHEN fnd_api.g_exc_error THEN
210 ROLLBACK TO create_bank_pub;
211 x_return_status := fnd_api.g_ret_sts_error;
212 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
213 print_debuginfo('Exception : ' || SQLERRM);
214 END IF;
215 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
216 p_count => x_msg_count,
217 p_data => x_msg_data);
218
219
220 WHEN fnd_api.g_exc_unexpected_error THEN
221 ROLLBACK TO create_bank_pub;
222 x_return_status := fnd_api.g_ret_sts_unexp_error;
223 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
224 print_debuginfo('Exception : ' || SQLERRM);
225 END IF;
226 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
227 p_count => x_msg_count,
228 p_data => x_msg_data);
229
230
231 WHEN OTHERS THEN
232 ROLLBACK TO create_bank_pub;
233 x_return_status := fnd_api.g_ret_sts_unexp_error;
234 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
235 print_debuginfo('Exception : ' || SQLERRM);
236 END IF;
237 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
238
239 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
240 p_count => x_msg_count,
241 p_data => x_msg_data);
242
243
244 END create_ext_bank;
245
246
247 -- 2. update_ext_bank
248 --
249 -- API name : update_ext_bank
250 -- Type : Public
251 -- Pre-reqs : None
252 -- Function : Updates the external bank
253 -- Current version : 1.0
254 -- Previous version: 1.0
255 -- Initial version : 1.0
256 --
257 PROCEDURE update_ext_bank (
258 p_api_version IN NUMBER,
259 p_init_msg_list IN VARCHAR2,
260 p_ext_bank_rec IN ExtBank_rec_type,
261 x_return_status OUT NOCOPY VARCHAR2,
262 x_msg_count OUT NOCOPY NUMBER,
263 x_msg_data OUT NOCOPY VARCHAR2,
264 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
265 )IS
266
267 l_api_name CONSTANT VARCHAR2(30) := 'update_bank';
268 l_api_version CONSTANT NUMBER := 1.0;
269 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
270
271 x_object_version_number NUMBER := p_ext_bank_rec.object_version_number;
272
273 BEGIN
274 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
275 print_debuginfo('ENTER ' || l_module_name);
276
277 END IF;
278 SAVEPOINT update_bank_pub;
279
280 -- Standard call to check for call compatibility.
281 IF NOT FND_API.Compatible_API_Call(l_api_version,
282 p_api_version,
283 l_api_name,
284 G_PKG_NAME) THEN
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286 END IF;
287
288 -- Initialize message list if p_init_msg_list is set to TRUE.
289 IF FND_API.to_Boolean(p_init_msg_list) THEN
290 FND_MSG_PUB.initialize;
291 END IF;
292
293 -- Initialize API return status to success
294 x_return_status := FND_API.G_RET_STS_SUCCESS;
295
296 -- Start of API body
297
298 -- Parameter validations
299 check_mandatory('IBY_BANK_ID_FIELD',p_ext_bank_rec.bank_id);
300 check_mandatory('IBY_BANK_NAME_FIELD',p_ext_bank_rec.bank_name);
301 check_mandatory('IBY_OBJ_VER_NUM',p_ext_bank_rec.object_version_number);
302
303 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
304 print_debuginfo('Calling CE API to update bank');
305
306 END IF;
307 ce_bank_pub.update_bank(p_init_msg_list => p_init_msg_list,
308 p_bank_id => p_ext_bank_rec.bank_id,
309 p_bank_name => p_ext_bank_rec.bank_name,
310 p_bank_number => p_ext_bank_rec.bank_number,
311 p_alternate_bank_name => p_ext_bank_rec.bank_alt_name,
312 p_short_bank_name => p_ext_bank_rec.bank_short_name, -- p_short_bank_name
313 p_description => p_ext_bank_rec.description,
314 p_tax_payer_id => p_ext_bank_rec.tax_payer_id, -- p_tax_payer_id
315 p_tax_registration_number => p_ext_bank_rec.tax_registration_number, -- p_tax_registration_number
316 p_attribute_category => p_ext_bank_rec.attribute_category, -- p_attribute_category
317 p_attribute1 => p_ext_bank_rec.attribute1, -- p_attribute1
318 p_attribute2 => p_ext_bank_rec.attribute2, -- p_attribute2
319 p_attribute3 => p_ext_bank_rec.attribute3, -- p_attribute3
320 p_attribute4 => p_ext_bank_rec.attribute4, -- p_attribute4
321 p_attribute5 => p_ext_bank_rec.attribute5, -- p_attribute5
322 p_attribute6 => p_ext_bank_rec.attribute6, -- p_attribute6
323 p_attribute7 => p_ext_bank_rec.attribute7, -- p_attribute7
324 p_attribute8 => p_ext_bank_rec.attribute8, -- p_attribute8
325 p_attribute9 => p_ext_bank_rec.attribute9, -- p_attribute9
326 p_attribute10 => p_ext_bank_rec.attribute10, -- p_attribute10
327 p_attribute11 => p_ext_bank_rec.attribute11, -- p_attribute11
328 p_attribute12 => p_ext_bank_rec.attribute12, -- p_attribute12
329 p_attribute13 => p_ext_bank_rec.attribute13, -- p_attribute13
330 p_attribute14 => p_ext_bank_rec.attribute14, -- p_attribute14
331 p_attribute15 => p_ext_bank_rec.attribute15, -- p_attribute15
332 p_attribute16 => p_ext_bank_rec.attribute16, -- p_attribute16
333 p_attribute17 => p_ext_bank_rec.attribute17, -- p_attribute17
334 p_attribute18 => p_ext_bank_rec.attribute18, -- p_attribute18
335 p_attribute19 => p_ext_bank_rec.attribute19, -- p_attribute19
336 p_attribute20 => p_ext_bank_rec.attribute20, -- p_attribute20
337 p_attribute21 => p_ext_bank_rec.attribute21, -- p_attribute21
338 p_attribute22 => p_ext_bank_rec.attribute22, -- p_attribute22
339 p_attribute23 => p_ext_bank_rec.attribute23, -- p_attribute23
340 p_attribute24 => p_ext_bank_rec.attribute24, -- p_attribute24
341 p_object_version_number => x_object_version_number,
342 x_return_status => x_return_status,
343 x_msg_count => x_msg_count,
344 x_msg_data => x_msg_data);
345
346 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
347 print_debuginfo('Returned from CE API');
348 END IF;
349 -- End of API body
350
351 -- get message count and if count is 1, get message info.
352 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
353 p_count => x_msg_count,
354 p_data => x_msg_data);
355
356 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
357 print_debuginfo('RETURN ' || l_module_name);
358
359 END IF;
360 EXCEPTION
361 WHEN fnd_api.g_exc_error THEN
362 ROLLBACK TO update_bank_pub;
363 x_return_status := fnd_api.g_ret_sts_error;
364 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
365 print_debuginfo('Exception : ' || SQLERRM);
366 END IF;
367 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
368 p_count => x_msg_count,
369 p_data => x_msg_data);
370
371
372 WHEN fnd_api.g_exc_unexpected_error THEN
373 ROLLBACK TO update_bank_pub;
374 x_return_status := fnd_api.g_ret_sts_unexp_error;
375 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
376 print_debuginfo('Exception : ' || SQLERRM);
377 END IF;
378 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
379 p_count => x_msg_count,
380 p_data => x_msg_data);
381
382
383 WHEN OTHERS THEN
384 ROLLBACK TO update_bank_pub;
385 x_return_status := fnd_api.g_ret_sts_unexp_error;
386 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
387 print_debuginfo('Exception : ' || SQLERRM);
388
389 END IF;
390 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
391 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
392 p_count => x_msg_count,
393 p_data => x_msg_data);
394
395
396 END update_ext_bank;
397
398
399 -- 3. set_bank_end_date
400 --
401 -- API name : set_bank_end_date
402 -- Type : Public
403 -- Pre-reqs : None
404 -- Function : Sets the bank end date
405 -- Current version : 1.0
406 -- Previous version: 1.0
407 -- Initial version : 1.0
408 --
409 PROCEDURE set_bank_end_date (
410 p_api_version IN NUMBER,
411 p_init_msg_list IN VARCHAR2,
412 p_bank_id IN NUMBER,
413 p_end_date IN DATE,
414 x_return_status OUT NOCOPY VARCHAR2,
415 x_msg_count OUT NOCOPY NUMBER,
416 x_msg_data OUT NOCOPY VARCHAR2,
417 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
418 ) IS
419
420 l_api_name CONSTANT VARCHAR2(30) := 'set_bank_end_date';
421 l_api_version CONSTANT NUMBER := 1.0;
422 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
423
424 l_object_version_number NUMBER;
425
426 CURSOR c_bank_ovn IS
427 SELECT object_version_number
428 FROM hz_parties
429 WHERE party_id = p_bank_id;
430
431 BEGIN
432
433 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
434 print_debuginfo('ENTER ' || l_module_name);
435
436 END IF;
437 SAVEPOINT set_bank_end_date_pub;
438
439 -- Standard call to check for call compatibility.
440 IF NOT FND_API.Compatible_API_Call(l_api_version,
441 p_api_version,
442 l_api_name,
443 G_PKG_NAME) THEN
444 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
445 END IF;
446
447 -- Initialize message list if p_init_msg_list is set to TRUE.
448 IF FND_API.to_Boolean(p_init_msg_list) THEN
449 FND_MSG_PUB.initialize;
450 END IF;
451
452 -- Initialize API return status to success
453 x_return_status := FND_API.G_RET_STS_SUCCESS;
454
455 -- Start of API body
456
457 -- Parameter validations
458 check_mandatory('IBY_BANK_ID_FIELD',p_bank_id);
459 check_mandatory('IBY_END_DATE' ,p_end_date);
460
461 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
462 print_debuginfo('Before Call to CE_BANK_PUB.set_bank_end_date ');
463
464 END IF;
465 -- Fetch Object Version Number for Bank Party
466 OPEN c_bank_ovn;
467 FETCH c_bank_ovn INTO l_object_version_number;
468 CLOSE c_bank_ovn;
469
470 ce_bank_pub.set_bank_end_date (
471 NULL,
472 p_bank_id,
473 p_end_date,
474 l_object_version_number,
475 x_return_status,
476 x_msg_count,
477 x_msg_data
478 );
479
480 -- End of API body
481
482 -- get message count and if count is 1, get message info.
483 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
484 p_count => x_msg_count,
485 p_data => x_msg_data);
486
487 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
488 print_debuginfo('RETURN ' || l_module_name);
489 END IF;
490 EXCEPTION
491 WHEN fnd_api.g_exc_error THEN
492 ROLLBACK TO set_bank_end_date_pub;
493 x_return_status := fnd_api.g_ret_sts_error;
494 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
495 print_debuginfo('Exception : ' || SQLERRM);
496 END IF;
497 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
498 p_count => x_msg_count,
499 p_data => x_msg_data);
500
501
502 WHEN fnd_api.g_exc_unexpected_error THEN
503 ROLLBACK TO set_bank_end_date_pub;
504 x_return_status := fnd_api.g_ret_sts_unexp_error;
505 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
506 print_debuginfo('Exception : ' || SQLERRM);
507 END IF;
508 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
509 p_count => x_msg_count,
510 p_data => x_msg_data);
511
512
513 WHEN OTHERS THEN
514 ROLLBACK TO set_bank_end_date_pub;
515 x_return_status := fnd_api.g_ret_sts_unexp_error;
516 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
517 print_debuginfo('Exception : ' || SQLERRM);
518 END IF;
519 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
520
521 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
522 p_count => x_msg_count,
523 p_data => x_msg_data);
524
525 END set_bank_end_date;
526
527
528 -- 4. check_bank_exist
529 --
530 -- API name : check_bank_exist
531 -- Type : Public
532 -- Pre-reqs : None
533 -- Function : Checks if the bank exists; bank name/number
534 -- and country code are used for identity
535 -- Current version : 1.0
536 -- Previous version: 1.0
537 -- Initial version : 1.0
538 --
539 PROCEDURE check_bank_exist (
540 p_api_version IN NUMBER,
541 p_init_msg_list IN VARCHAR2,
542 p_country_code IN VARCHAR2,
543 p_bank_name IN VARCHAR2,
544 p_bank_number IN VARCHAR2,
545 x_return_status OUT NOCOPY VARCHAR2,
546 x_msg_count OUT NOCOPY NUMBER,
547 x_msg_data OUT NOCOPY VARCHAR2,
548 x_bank_id OUT NOCOPY NUMBER,
549 x_end_date OUT NOCOPY DATE,
550 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
551 )IS
552
553 l_api_name CONSTANT VARCHAR2(30) := 'check_bank_exist';
554 l_api_version CONSTANT NUMBER := 1.0;
555 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
556
557 BEGIN
558
559 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
560 print_debuginfo('ENTER ' || l_module_name);
561
562 END IF;
563 -- Standard call to check for call compatibility.
564 IF NOT FND_API.Compatible_API_Call(l_api_version,
565 p_api_version,
566 l_api_name,
567 G_PKG_NAME) THEN
568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
569 END IF;
570
571 -- Initialize message list if p_init_msg_list is set to TRUE.
572 IF FND_API.to_Boolean(p_init_msg_list) THEN
573 FND_MSG_PUB.initialize;
574 END IF;
575
576 -- Initialize API return status to success
577 x_return_status := FND_API.G_RET_STS_SUCCESS;
578
579 -- Start of API body
580
581 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
582 print_debuginfo('Before Call to CE_BANK_PUB.check_bank_exist ');
583
584 END IF;
585 ce_bank_pub.check_bank_exist(
586 p_country_code,
587 p_bank_name,
588 p_bank_number,
589 x_bank_id,
590 x_end_date
591 );
592
593 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
594 print_debuginfo('Ext Bank Id : ' || x_bank_id);
595
596 END IF;
597 -- End of API body
598
599 -- get message count and if count is 1, get message info.
600 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
601 p_count => x_msg_count,
602 p_data => x_msg_data);
603
604 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
605 print_debuginfo('RETURN ' || l_module_name);
606 END IF;
607 EXCEPTION
608 WHEN fnd_api.g_exc_error THEN
609 x_return_status := fnd_api.g_ret_sts_error;
610 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
611 print_debuginfo('Exception : ' || SQLERRM);
612 END IF;
613 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
614 p_count => x_msg_count,
615 p_data => x_msg_data);
616
617
618 WHEN fnd_api.g_exc_unexpected_error THEN
619 x_return_status := fnd_api.g_ret_sts_unexp_error;
620 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
621 print_debuginfo('Exception : ' || SQLERRM);
622 END IF;
623 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
624 p_count => x_msg_count,
625 p_data => x_msg_data);
626
627
628 WHEN OTHERS THEN
629 x_return_status := fnd_api.g_ret_sts_unexp_error;
630 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
631 print_debuginfo('Exception : ' || SQLERRM);
632
633 END IF;
634 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
635
636 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
637 p_count => x_msg_count,
638 p_data => x_msg_data);
639
640 END check_bank_exist;
641
642
643 -- 5. create_ext_bank_branch
644 --
645 -- API name : create_ext_bank_branch
646 -- Type : Public
647 -- Pre-reqs : None
648 -- Function : Creates the external bank branch
649 -- Current version : 1.0
650 -- Previous version: 1.0
651 -- Initial version : 1.0
652 --
653 PROCEDURE create_ext_bank_branch (
654 p_api_version IN NUMBER,
655 p_init_msg_list IN VARCHAR2,
656 p_ext_bank_branch_rec IN ExtBankBranch_rec_type,
657 x_branch_id OUT NOCOPY NUMBER,
658 x_return_status OUT NOCOPY VARCHAR2,
659 x_msg_count OUT NOCOPY NUMBER,
660 x_msg_data OUT NOCOPY VARCHAR2,
661 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
662 ) IS
663
664 l_api_name CONSTANT VARCHAR2(30) := 'create_ext_bank_branch';
665 l_api_version CONSTANT NUMBER := 1.0;
666 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
667
668
669 BEGIN
670 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
671 print_debuginfo('ENTER ' || l_module_name);
672
673 END IF;
674 SAVEPOINT create_bank_branch_pub;
675
676 -- Standard call to check for call compatibility.
677 IF NOT FND_API.Compatible_API_Call(l_api_version,
678 p_api_version,
679 l_api_name,
680 G_PKG_NAME) THEN
681 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
682 END IF;
683
684 -- Initialize message list if p_init_msg_list is set to TRUE.
685 IF FND_API.to_Boolean(p_init_msg_list) THEN
686 FND_MSG_PUB.initialize;
687 END IF;
688
689 -- Initialize API return status to success
690 x_return_status := FND_API.G_RET_STS_SUCCESS;
691
692 -- Start of API body
693
694 -- Parameter validations
695 check_mandatory('IBY_BANK_PARTY_ID_FIELD',p_ext_bank_branch_rec.bank_party_id);
696 check_mandatory('IBY_BRANCH_NAME_FIELD',p_ext_bank_branch_rec.branch_name);
697 check_mandatory('IBY_BRANCH_TYPE_FIELD',p_ext_bank_branch_rec.branch_type);
698
699 -- Other Needed Validations
700 -- 1. Country Specific validations: Perfomed by CE
701 -- i. Bank Name, Branch Name and Country must be unique
702 -- for non-US and Germany Bank branches
703 -- ii. Bank Number, Branch Number and Country must be unique
704 -- for non-US and Germany bank branches
705 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
706 print_debuginfo('Before Call to ''ce_bank_pub.create_bank_branch''');
707 END IF;
708 -- Call CE API to create bank branch
709 ce_bank_pub.create_bank_branch(p_init_msg_list,
710 p_ext_bank_branch_rec.bank_party_id,
711 p_ext_bank_branch_rec.branch_name,
712 p_ext_bank_branch_rec.branch_number,
713 p_ext_bank_branch_rec.branch_type,
714 p_ext_bank_branch_rec.alternate_branch_name,
715 p_ext_bank_branch_rec.description,
716 p_ext_bank_branch_rec.bic,
717 p_ext_bank_branch_rec.eft_number, -- p_eft_number
718 p_ext_bank_branch_rec.rfc_identifier,
719 p_ext_bank_branch_rec.attribute_category, -- p_attribute_category
720 p_ext_bank_branch_rec.attribute1, -- p_attribute1
721 p_ext_bank_branch_rec.attribute2, -- p_attribute2
722 p_ext_bank_branch_rec.attribute3, -- p_attribute3
723 p_ext_bank_branch_rec.attribute4, -- p_attribute4
724 p_ext_bank_branch_rec.attribute5, -- p_attribute5
725 p_ext_bank_branch_rec.attribute6, -- p_attribute6
726 p_ext_bank_branch_rec.attribute7, -- p_attribute7
727 p_ext_bank_branch_rec.attribute8, -- p_attribute8
728 p_ext_bank_branch_rec.attribute9, -- p_attribute9
729 p_ext_bank_branch_rec.attribute10, -- p_attribute10
730 p_ext_bank_branch_rec.attribute11, -- p_attribute11
731 p_ext_bank_branch_rec.attribute12, -- p_attribute12
732 p_ext_bank_branch_rec.attribute13, -- p_attribute13
733 p_ext_bank_branch_rec.attribute14, -- p_attribute14
734 p_ext_bank_branch_rec.attribute15, -- p_attribute15
735 p_ext_bank_branch_rec.attribute16, -- p_attribute16
736 p_ext_bank_branch_rec.attribute17, -- p_attribute17
737 p_ext_bank_branch_rec.attribute18, -- p_attribute18
738 p_ext_bank_branch_rec.attribute19, -- p_attribute19
739 p_ext_bank_branch_rec.attribute20, -- p_attribute20
740 p_ext_bank_branch_rec.attribute21, -- p_attribute21
741 p_ext_bank_branch_rec.attribute22, -- p_attribute22
742 p_ext_bank_branch_rec.attribute23, -- p_attribute23
743 p_ext_bank_branch_rec.attribute24, -- p_attribute24
744 x_branch_id,
745 x_return_status,
746 x_msg_count,
747 x_msg_data);
748
749 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
750 print_debuginfo('Ext Bank Branch Id : ' || x_branch_id);
751 END IF;
752 -- End of API body
753
754 -- get message count and if count is 1, get message info.
755 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
756 p_count => x_msg_count,
757 p_data => x_msg_data);
758
759 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
760 print_debuginfo('RETURN ' || l_module_name);
761
762 END IF;
763 EXCEPTION
764 WHEN fnd_api.g_exc_error THEN
765 ROLLBACK TO create_bank_branch_pub;
766 x_return_status := fnd_api.g_ret_sts_error;
767 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
768 print_debuginfo('Exception : ' || SQLERRM);
769 END IF;
770 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
771 p_count => x_msg_count,
772 p_data => x_msg_data);
773
774
775 WHEN fnd_api.g_exc_unexpected_error THEN
776 ROLLBACK TO create_bank_branch_pub;
777 x_return_status := fnd_api.g_ret_sts_unexp_error;
778 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
779 print_debuginfo('Exception : ' || SQLERRM);
780 END IF;
781 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
782 p_count => x_msg_count,
783 p_data => x_msg_data);
784
785
786 WHEN OTHERS THEN
787 ROLLBACK TO create_bank_branch_pub;
788 x_return_status := fnd_api.g_ret_sts_unexp_error;
789 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
790 print_debuginfo('Exception : ' || SQLERRM);
791
792 END IF;
793 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
794 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
795 p_count => x_msg_count,
796 p_data => x_msg_data);
797
798 END create_ext_bank_branch;
799
800
801 -- 6. update_ext_bank_branch
802 --
803 -- API name : update_ext_bank_branch
804 -- Type : Public
805 -- Pre-reqs : None
806 -- Function : Updates the external bank branch
807 -- Current version : 1.0
808 -- Previous version: 1.0
809 -- Initial version : 1.0
810 --
811 PROCEDURE update_ext_bank_branch (
812 p_api_version IN NUMBER,
813 p_init_msg_list IN VARCHAR2,
814 p_ext_bank_branch_rec IN OUT NOCOPY ExtBankBranch_rec_type,
815 x_return_status OUT NOCOPY VARCHAR2,
816 x_msg_count OUT NOCOPY NUMBER,
817 x_msg_data OUT NOCOPY VARCHAR2,
818 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
819 ) IS
820
821 l_api_name CONSTANT VARCHAR2(30) := 'update_ext_bank_branch';
822 l_api_version CONSTANT NUMBER := 1.0;
823 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
824
825 l_rfc_identifier_ovn NUMBER(15,0);
826 l_eft_record_ovn NUMBER(15,0);
827
828 -- Picks up object version number of RFC identifier
829 CURSOR c_rfc_identifier_ovn(p_branch_id NUMBER) IS
830 SELECT object_version_number
831 FROM HZ_CODE_ASSIGNMENTS
832 WHERE class_category = 'RFC_IDENTIFIER'
833 AND owner_table_name = 'HZ_PARTIES'
834 AND owner_table_id = p_branch_id;
835
836 -- Picks up object version number of EFT Record
837 CURSOR c_eft_record_ovn(p_branch_id NUMBER) IS
838 SELECT object_version_number
839 FROM HZ_CODE_ASSIGNMENTS
840 WHERE class_category = 'EFT'
841 AND owner_table_name = 'HZ_PARTIES'
842 AND owner_table_id = p_branch_id;
843
844 BEGIN
845 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
846 print_debuginfo('ENTER ' || l_module_name);
847
848 END IF;
849 SAVEPOINT update_bank_branch_pub;
850
851 -- Standard call to check for call compatibility.
852 IF NOT FND_API.Compatible_API_Call(l_api_version,
853 p_api_version,
854 l_api_name,
855 G_PKG_NAME) THEN
856 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
857 END IF;
858
859 -- Initialize message list if p_init_msg_list is set to TRUE.
860 IF FND_API.to_Boolean(p_init_msg_list) THEN
861 FND_MSG_PUB.initialize;
862 END IF;
863
864 -- Initialize API return status to success
865 x_return_status := FND_API.G_RET_STS_SUCCESS;
866
867 -- Start of API Body
868
869 -- Parameter validations
870 check_mandatory('IBY_BRANCH_ID_FIELD', p_ext_bank_branch_rec.branch_party_id);
871 check_mandatory('IBY_BRANCH_NAME_FIELD', p_ext_bank_branch_rec.branch_name);
872 check_mandatory('IBY_BRANCH_TYPE_FIELD', p_ext_bank_branch_rec.branch_type);
873 check_mandatory('IBY_BNKBRN_REC_OBJ_VER', p_ext_bank_branch_rec.bch_object_version_number);
874 check_mandatory('IBY_BNKBRN_TYPE_OBJ_VER', p_ext_bank_branch_rec.typ_object_version_number);
875
876 -- Get the current RFC Identifier OVN
877 OPEN c_rfc_identifier_ovn(p_ext_bank_branch_rec.branch_party_id);
878 FETCH c_rfc_identifier_ovn INTO l_rfc_identifier_ovn;
879 CLOSE c_rfc_identifier_ovn;
880
881 -- Get the current EFT Identifier OVN
882 OPEN c_eft_record_ovn(p_ext_bank_branch_rec.branch_party_id);
883 FETCH c_eft_record_ovn INTO l_eft_record_ovn;
884 CLOSE c_eft_record_ovn;
885
886 -- Validate the input RFC Identifier OVN
887 -- if the current RFC Identifier OVN is not null
888 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
889 print_debuginfo('Input RFC Identifier OVN ' || p_ext_bank_branch_rec.rfc_object_version_number);
890 END IF;
891 IF (l_rfc_identifier_ovn IS NOT NULL) THEN
892 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
893 print_debuginfo('Current RFC Identifier OVN ' || l_rfc_identifier_ovn);
894 END IF;
895 IF (l_rfc_identifier_ovn <> p_ext_bank_branch_rec.rfc_object_version_number) THEN
896
897 fnd_message.set_name('IBY', 'IBY_DATA_VERSION_ERROR');
898
899 fnd_msg_pub.add;
900 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
901 print_debuginfo('Object Version Number mismatch');
902 END IF;
903 END IF;
904 ELSE
905 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
906 print_debuginfo('Current RFC Identifier OVN is NULL');
907 END IF;
908 END IF;
909
910 -- Validate the input EFT Identifier OVN
911 -- if the current EFT Identifier OVN is not null
912 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
913 print_debuginfo('Input RFC Identifier OVN ' || p_ext_bank_branch_rec.eft_object_version_number);
914 END IF;
915 IF (l_eft_record_ovn IS NOT NULL) THEN
916 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
917 print_debuginfo('Current RFC Identifier OVN ' || l_eft_record_ovn);
918 END IF;
919 IF (l_eft_record_ovn <> p_ext_bank_branch_rec.eft_object_version_number) THEN
920 fnd_message.set_name('IBY', 'IBY_DATA_VERSION_ERROR');
921 fnd_msg_pub.add;
922 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
923 print_debuginfo('Object Version Number mismatch');
924 END IF;
925 END IF;
926 ELSE
927 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
928 print_debuginfo('Current RFC Identifier OVN is NULL');
929 END IF;
930 END IF;
931
932 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
933 print_debuginfo('Calling CE API to update bank branch');
934
935 END IF;
936 ce_bank_pub.update_bank_branch(p_init_msg_list => p_init_msg_list,
937 p_branch_id => p_ext_bank_branch_rec.branch_party_id,
938 p_branch_name => p_ext_bank_branch_rec.branch_name,
939 p_branch_number => p_ext_bank_branch_rec.branch_number,
940 p_branch_type => p_ext_bank_branch_rec.branch_type,
941 p_alternate_branch_name => p_ext_bank_branch_rec.alternate_branch_name,
942 p_description => p_ext_bank_branch_rec.description,
943 p_bic => p_ext_bank_branch_rec.bic,
944 p_eft_number => p_ext_bank_branch_rec.eft_number, -- p_eft_number
945 p_rfc_identifier => p_ext_bank_branch_rec.rfc_identifier,
946 p_attribute_category => p_ext_bank_branch_rec.attribute_category, -- p_attribute_category
947 p_attribute1 => p_ext_bank_branch_rec.attribute1, -- p_attribute1
948 p_attribute2 => p_ext_bank_branch_rec.attribute2, -- p_attribute2
949 p_attribute3 => p_ext_bank_branch_rec.attribute3, -- p_attribute3
950 p_attribute4 => p_ext_bank_branch_rec.attribute4, -- p_attribute4
951 p_attribute5 => p_ext_bank_branch_rec.attribute5, -- p_attribute5
952 p_attribute6 => p_ext_bank_branch_rec.attribute6, -- p_attribute6
953 p_attribute7 => p_ext_bank_branch_rec.attribute7, -- p_attribute7
954 p_attribute8 => p_ext_bank_branch_rec.attribute8, -- p_attribute8
955 p_attribute9 => p_ext_bank_branch_rec.attribute9, -- p_attribute9
956 p_attribute10 => p_ext_bank_branch_rec.attribute10, -- p_attribute10
957 p_attribute11 => p_ext_bank_branch_rec.attribute11, -- p_attribute11
958 p_attribute12 => p_ext_bank_branch_rec.attribute12, -- p_attribute12
959 p_attribute13 => p_ext_bank_branch_rec.attribute13, -- p_attribute13
960 p_attribute14 => p_ext_bank_branch_rec.attribute14, -- p_attribute14
961 p_attribute15 => p_ext_bank_branch_rec.attribute15, -- p_attribute15
962 p_attribute16 => p_ext_bank_branch_rec.attribute16, -- p_attribute16
963 p_attribute17 => p_ext_bank_branch_rec.attribute17, -- p_attribute17
964 p_attribute18 => p_ext_bank_branch_rec.attribute18, -- p_attribute18
965 p_attribute19 => p_ext_bank_branch_rec.attribute19, -- p_attribute19
966 p_attribute20 => p_ext_bank_branch_rec.attribute20, -- p_attribute20
967 p_attribute21 => p_ext_bank_branch_rec.attribute21, -- p_attribute21
968 p_attribute22 => p_ext_bank_branch_rec.attribute22, -- p_attribute22
969 p_attribute23 => p_ext_bank_branch_rec.attribute23, -- p_attribute23
970 p_attribute24 => p_ext_bank_branch_rec.attribute24, -- p_attribute24
971 p_bch_object_version_number => p_ext_bank_branch_rec.bch_object_version_number,
972 p_typ_object_version_number => p_ext_bank_branch_rec.typ_object_version_number,
973 p_rfc_object_version_number => p_ext_bank_branch_rec.rfc_object_version_number,
974 p_eft_object_version_number => p_ext_bank_branch_rec.eft_object_version_number,
975 x_return_status => x_return_status,
976 x_msg_count => x_msg_count,
977 x_msg_data => x_msg_data);
978
979 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
980 print_debuginfo('Returned from CE API');
981 END IF;
982 -- End of API Body
983
984 -- get message count and if count is 1, get message info.
985 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
986 p_count => x_msg_count,
987 p_data => x_msg_data);
988
989 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
990 print_debuginfo('RETURN ' || l_module_name);
991
992 END IF;
993 EXCEPTION
994 WHEN fnd_api.g_exc_error THEN
995 ROLLBACK TO update_bank_branch_pub;
996 x_return_status := fnd_api.g_ret_sts_error;
997 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
998 print_debuginfo('Exception : ' || SQLERRM);
999 END IF;
1000 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1001 p_count => x_msg_count,
1002 p_data => x_msg_data);
1003
1004
1005 WHEN fnd_api.g_exc_unexpected_error THEN
1006 ROLLBACK TO update_bank_branch_pub;
1007 x_return_status := fnd_api.g_ret_sts_unexp_error;
1008 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1009 print_debuginfo('Exception : ' || SQLERRM);
1010 END IF;
1011 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1012 p_count => x_msg_count,
1013 p_data => x_msg_data);
1014
1015
1016 WHEN OTHERS THEN
1017 ROLLBACK TO update_bank_branch_pub;
1018 x_return_status := fnd_api.g_ret_sts_unexp_error;
1019 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1020 print_debuginfo('Exception : ' || SQLERRM);
1021
1022 END IF;
1023 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
1024 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1025 p_count => x_msg_count,
1026 p_data => x_msg_data);
1027
1028
1029 END update_ext_bank_branch;
1030
1031
1032 -- 7. set_ext_bank_branch_end_date
1033 --
1034 -- API name : set_ext_bank_branch_end_date
1035 -- Type : Public
1036 -- Pre-reqs : None
1037 -- Function : Sets the bank branch end date
1038 -- Current version : 1.0
1039 -- Previous version: 1.0
1040 -- Initial version : 1.0
1041 --
1042 PROCEDURE set_ext_bank_branch_end_date (
1043 p_api_version IN NUMBER,
1044 p_init_msg_list IN VARCHAR2,
1045 p_branch_id IN NUMBER,
1046 p_end_date IN DATE,
1047 x_return_status OUT NOCOPY VARCHAR2,
1048 x_msg_count OUT NOCOPY NUMBER,
1049 x_msg_data OUT NOCOPY VARCHAR2,
1050 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1051 ) IS
1052
1053 l_api_name CONSTANT VARCHAR2(30) := 'set_ext_bank_branch_end_date';
1054 l_api_version CONSTANT NUMBER := 1.0;
1055 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
1056
1057 l_object_version_number NUMBER;
1058
1059 CURSOR c_branch_party_ovn IS
1060 SELECT object_version_number
1061 FROM hz_parties
1062 WHERE party_id = p_branch_id;
1063
1064 BEGIN
1065
1066 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1067 print_debuginfo('ENTER ' || l_module_name);
1068
1069 END IF;
1070 SAVEPOINT set_ext_branch_end_date_pub;
1071
1072 -- Standard call to check for call compatibility.
1073 IF NOT FND_API.Compatible_API_Call(l_api_version,
1074 p_api_version,
1075 l_api_name,
1076 G_PKG_NAME) THEN
1077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1078 END IF;
1079
1080 -- Initialize message list if p_init_msg_list is set to TRUE.
1081 IF FND_API.to_Boolean(p_init_msg_list) THEN
1082 FND_MSG_PUB.initialize;
1083 END IF;
1084
1085 -- Initialize API return status to success
1086 x_return_status := FND_API.G_RET_STS_SUCCESS;
1087
1088 -- Start of API body
1089
1090 -- Parameter validations
1091 check_mandatory('IBY_BNKBRN_ID_FIELD',p_branch_id);
1092 check_mandatory('IBY_END_DATE',p_end_date);
1093
1094 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1095 print_debuginfo('Before Call to CE_BANK_PUB.set_bank_branch_end_date ');
1096
1097 END IF;
1098 -- Fetch Object Verion Number for branch Party
1099 OPEN c_branch_party_ovn;
1100 FETCH c_branch_party_ovn INTO l_object_version_number;
1101 CLOSE c_branch_party_ovn;
1102
1103 ce_bank_pub.set_bank_branch_end_date (
1104 NULL,
1105 p_branch_id,
1106 p_end_date,
1107 l_object_version_number,
1108 x_return_status,
1109 x_msg_count,
1110 x_msg_data
1111 );
1112
1113 -- End of API body
1114
1115 -- get message count and if count is 1, get message info.
1116 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1117 p_count => x_msg_count,
1118 p_data => x_msg_data);
1119
1120 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1121 print_debuginfo('RETURN ' || l_module_name);
1122 END IF;
1123 EXCEPTION
1124 WHEN fnd_api.g_exc_error THEN
1125 ROLLBACK TO set_ext_branch_end_date_pub;
1126 x_return_status := fnd_api.g_ret_sts_error;
1127 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1128 print_debuginfo('Exception : ' || SQLERRM);
1129 END IF;
1130 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1131 p_count => x_msg_count,
1132 p_data => x_msg_data);
1133
1134
1135 WHEN fnd_api.g_exc_unexpected_error THEN
1136 ROLLBACK TO set_ext_branch_end_date_pub;
1137 x_return_status := fnd_api.g_ret_sts_unexp_error;
1138 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1139 print_debuginfo('Exception : ' || SQLERRM);
1140 END IF;
1141 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1142 p_count => x_msg_count,
1143 p_data => x_msg_data);
1144
1145
1146 WHEN OTHERS THEN
1147 ROLLBACK TO set_ext_branch_end_date_pub;
1148 x_return_status := fnd_api.g_ret_sts_unexp_error;
1149 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1150 print_debuginfo('Exception : ' || SQLERRM);
1151
1152 END IF;
1153 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
1154 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1155 p_count => x_msg_count,
1156 p_data => x_msg_data);
1157
1158
1159
1160 END set_ext_bank_branch_end_date;
1161
1162
1163 -- 8. check_ext_bank_branch_exist
1164 --
1165 -- API name : check_ext_bank_branch_exist
1166 -- Type : Public
1167 -- Pre-reqs : None
1168 -- Function : Checks if the bank branch exists; branch name/number
1169 -- and bank id are used for identity
1170 -- Current version : 1.0
1171 -- Previous version: 1.0
1172 -- Initial version : 1.0
1173 --
1174 PROCEDURE check_ext_bank_branch_exist (
1175 p_api_version IN NUMBER,
1176 p_init_msg_list IN VARCHAR2,
1177 p_bank_id IN NUMBER,
1178 p_branch_name IN VARCHAR2,
1179 p_branch_number IN VARCHAR2,
1180 x_return_status OUT NOCOPY VARCHAR2,
1181 x_msg_count OUT NOCOPY NUMBER,
1182 x_msg_data OUT NOCOPY VARCHAR2,
1183 x_branch_id OUT NOCOPY NUMBER,
1184 x_end_date OUT NOCOPY DATE,
1185 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1186 ) IS
1187
1188 l_api_name CONSTANT VARCHAR2(30) := 'check_ext_bank_branch_exist';
1189 l_api_version CONSTANT NUMBER := 1.0;
1190 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
1191
1192 BEGIN
1193
1194 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1195 print_debuginfo('ENTER ' || l_module_name);
1196
1197 END IF;
1198 -- Standard call to check for call compatibility.
1199 IF NOT FND_API.Compatible_API_Call(l_api_version,
1200 p_api_version,
1201 l_api_name,
1202 G_PKG_NAME) THEN
1203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1204 END IF;
1205
1206 -- Initialize message list if p_init_msg_list is set to TRUE.
1207 IF FND_API.to_Boolean(p_init_msg_list) THEN
1208 FND_MSG_PUB.initialize;
1209 END IF;
1210
1211 -- Initialize API return status to success
1212 x_return_status := FND_API.G_RET_STS_SUCCESS;
1213
1214 -- Start of API body
1215
1216 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1217 print_debuginfo('Before Call to CE_BANK_PUB.check_branch_exist ');
1218
1219 END IF;
1220 ce_bank_pub.check_branch_exist(
1221 p_bank_id,
1222 p_branch_name,
1223 p_branch_number,
1224 x_branch_id,
1225 x_end_date
1226 );
1227
1228 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1229 print_debuginfo('Ext Bank Branch Id : ' || x_branch_id);
1230
1231 END IF;
1232 -- End of API body
1233
1234 -- get message count and if count is 1, get message info.
1235 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1236 p_count => x_msg_count,
1237 p_data => x_msg_data);
1238
1239 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1240 print_debuginfo('RETURN ' || l_module_name);
1241
1242 END IF;
1243 EXCEPTION
1244 WHEN fnd_api.g_exc_error THEN
1245 x_return_status := fnd_api.g_ret_sts_error;
1246 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1247 print_debuginfo('Exception : ' || SQLERRM);
1248 END IF;
1249 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1250 p_count => x_msg_count,
1251 p_data => x_msg_data);
1252
1253
1254 WHEN fnd_api.g_exc_unexpected_error THEN
1255 x_return_status := fnd_api.g_ret_sts_unexp_error;
1256 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1257 print_debuginfo('Exception : ' || SQLERRM);
1258 END IF;
1259 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1260 p_count => x_msg_count,
1261 p_data => x_msg_data);
1262
1263
1264 WHEN OTHERS THEN
1265 x_return_status := fnd_api.g_ret_sts_unexp_error;
1266 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1267 print_debuginfo('Exception : ' || SQLERRM);
1268 END IF;
1269 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
1270
1271 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1272 p_count => x_msg_count,
1273 p_data => x_msg_data);
1274
1275
1276 END check_ext_bank_branch_exist;
1277
1278
1279 -- 9. create_ext_bank_acct
1280 --
1281 -- API name : create_ext_bank_acct
1282 -- Type : Public
1283 -- Pre-reqs : None
1284 -- Function : Creates an external bank account
1285 -- Current version : 1.0
1286 -- Previous version: 1.0
1287 -- Initial version : 1.0
1288 --
1289 PROCEDURE create_ext_bank_acct (
1290 p_api_version IN NUMBER,
1291 p_init_msg_list IN VARCHAR2,
1292 p_ext_bank_acct_rec IN ExtBankAcct_rec_type,
1293 x_acct_id OUT NOCOPY NUMBER,
1294 x_return_status OUT NOCOPY VARCHAR2,
1295 x_msg_count OUT NOCOPY NUMBER,
1296 x_msg_data OUT NOCOPY VARCHAR2,
1297 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1298 ) IS
1299
1300 l_api_name CONSTANT VARCHAR2(30) := 'create_ext_bank_acct';
1301 l_api_version CONSTANT NUMBER := 1.0;
1302 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
1303
1304 l_iban VARCHAR2(50);
1305 l_country VARCHAR2(60);
1306 l_bank_id NUMBER(15) := null;
1307 l_acct_rowid VARCHAR2(100);
1308 l_bank_name VARCHAR2(360) := null;
1309 l_bank_number VARCHAR2(30) := null;
1310 l_branch_number VARCHAR2(30) := null;
1311 l_account_number VARCHAR2(100) := null;
1312 l_owner_id NUMBER(15);
1313 l_owner_rowid VARCHAR2(100);
1314 l_count NUMBER;
1315 l_joint_acct_owner_id NUMBER;
1316
1317 lx_mask_option iby_ext_bank_accounts.ba_mask_setting%TYPE;
1318 lx_unmask_len iby_ext_bank_accounts.ba_unmask_length%TYPE;
1319 l_masked_ba_num iby_ext_bank_accounts.masked_bank_account_num%TYPE;
1320 l_masked_iban iby_ext_bank_accounts.masked_iban%TYPE;
1321 l_ba_num_hash1 iby_ext_bank_accounts.bank_account_num_hash1%TYPE;
1322 l_ba_num_hash2 iby_ext_bank_accounts.bank_account_num_hash1%TYPE;
1323 l_iban_hash1 iby_ext_bank_accounts.iban_hash1%TYPE;
1324 l_iban_hash2 iby_ext_bank_accounts.iban_hash2%TYPE;
1325 l_dup_acct_id number;
1326 l_dup_start_date date;
1327 l_dup_end_date date;
1328 l_bank_account_num_electronic iby_ext_bank_accounts.bank_account_num_electronic%TYPE;
1329 l_party_id ap_suppliers.party_id%TYPE;
1330 l_supplier_name ap_suppliers.vendor_name%TYPE;
1331 l_supplier_number ap_suppliers.segment1%TYPE;
1332
1333 l_error_msg VARCHAR2(500);
1334 l_ret_stat VARCHAR2(1);
1335 l_org_id iby_external_payees_all.org_id%TYPE;
1336 l_org_name hr_operating_units.name%TYPE;
1337 -- picks up branch number
1338 CURSOR c_branch (p_branch_id NUMBER) IS
1339 SELECT bank_or_branch_number
1340 FROM hz_organization_profiles
1341 WHERE SYSDATE between TRUNC(effective_start_date)
1342 and NVL(TRUNC(effective_end_date), SYSDATE+1)
1343 AND party_id = p_branch_id;
1344
1345 CURSOR c_supplier(p_acct_id NUMBER) IS
1346 SELECT owners.account_owner_party_id
1347 FROM iby_pmt_instr_uses_all instrument,
1348 IBY_ACCOUNT_OWNERS owners,
1349 iby_external_payees_all payees
1350 WHERE
1351 owners.primary_flag = 'Y' AND
1352 owners.ext_bank_account_id = p_acct_id AND
1353 owners.ext_bank_account_id = instrument.instrument_id AND
1354 payees.ext_payee_id = instrument.ext_pmt_party_id AND
1355 payees.payee_party_id = owners.account_owner_party_id;
1356
1357
1358
1359 BEGIN
1360
1361 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1362 print_debuginfo('ENTER ' || l_module_name);
1363
1364 END IF;
1365 SAVEPOINT create_ext_bank_acct_pub;
1366
1367 -- Standard call to check for call compatibility.
1368 IF NOT FND_API.Compatible_API_Call(l_api_version,
1369 p_api_version,
1370 l_api_name,
1371 G_PKG_NAME) THEN
1372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1373 END IF;
1374
1375 -- Initialize message list if p_init_msg_list is set to TRUE.
1376 IF FND_API.to_Boolean(p_init_msg_list) THEN
1377 FND_MSG_PUB.initialize;
1378 END IF;
1379
1380 -- Initialize API return status to success
1381 x_return_status := FND_API.G_RET_STS_SUCCESS;
1382
1383 -- Start of API
1384
1385 -- Parameter validations
1386 --check_mandatory('Branch Id',p_ext_bank_acct_rec.branch_id);
1387 --check_mandatory('Bank Account Name',p_ext_bank_acct_rec.acct_name);
1388 check_mandatory('IBY_BANKACCT_NUM_FIELD',p_ext_bank_acct_rec.bank_account_num);
1389 check_mandatory('IBY_COUNTRY_CD_FIELD',p_ext_bank_acct_rec.country_code);
1390
1391 -- Bug# 8470581
1392 -- Owner Party Id is no more mandatory as we allow
1393 -- the creation of orphan bank accounts
1394 --check_mandatory('Account Owner Party Id',p_ext_bank_acct_rec.acct_owner_party_id);
1395
1396 --check_mandatory('Currency',p_ext_bank_acct_rec.currency);
1397
1398 -- Other Validations
1399 -- 1. Country Specific Validations
1400
1401 -- validate currency
1402 IF p_ext_bank_acct_rec.currency IS NOT NULL THEN
1403 CE_BANK_AND_ACCOUNT_VALIDATION.validate_currency(p_ext_bank_acct_rec.currency, x_return_status);
1404 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1405 print_debuginfo('Validated Currency');
1406 END IF;
1407 END IF;
1408
1409 -- validate iban
1410 IF p_ext_bank_acct_rec.iban IS NOT NULL THEN
1411 CE_BANK_AND_ACCOUNT_VALIDATION.validate_IBAN(p_ext_bank_acct_rec.iban, l_iban, x_return_status);
1412 IF not x_return_status=fnd_api.g_ret_sts_success
1413 THEN
1414 x_return_status := fnd_api.g_ret_sts_error;
1415 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1416 print_debuginfo('IBAN Validation Failed ');
1417 END IF;
1418 RAISE fnd_api.g_exc_error;
1419 ELSE
1420 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1421 print_debuginfo('IBAN Validation Successful');
1422 END IF;
1423 END IF;
1424 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1425 print_debuginfo('Validated IBAN Number');
1426 END IF;
1427 END IF;
1428
1429 -- find bank info
1430 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1431 print_debuginfo('The value for p_ext_bank_acct_rec.branch_id :' ||p_ext_bank_acct_rec.branch_id);
1432 END IF;
1433 IF p_ext_bank_acct_rec.branch_id IS NOT NULL THEN
1434 find_bank_info(p_ext_bank_acct_rec.branch_id, x_return_status, l_bank_id, l_country, l_bank_name, l_bank_number);
1435 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1436 print_debuginfo('Got Bank Info : '||l_bank_name);
1437 print_debuginfo('Got Country : '||l_country);
1438
1439 END IF;
1440 -- find branch number
1441 OPEN c_branch(p_ext_bank_acct_rec.branch_id);
1442 FETCH c_branch INTO l_branch_number;
1443 IF c_branch%NOTFOUND THEN
1444 fnd_message.set_name('IBY', 'IBY_API_NO_BRANCH');
1445 fnd_msg_pub.add;
1446 x_return_status := fnd_api.g_ret_sts_error;
1447 RAISE fnd_api.g_exc_error;
1448 ELSE
1449 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1450 print_debuginfo('Got Branch Number : '||l_branch_number);
1451 END IF;
1452 END IF;
1453 CLOSE c_branch;
1454
1455 END IF;
1456
1457 -- perform unique check for account
1458
1459 -- calling our own check bank account exists
1460
1461
1462 check_ext_acct_exist(
1463 p_api_version,
1464 p_init_msg_list,
1465 p_ext_bank_acct_rec,
1466 l_dup_acct_id,
1467 l_dup_start_date,
1468 l_dup_end_date,
1469 x_return_status,
1470 x_msg_count,
1471 x_msg_data,
1472 x_response
1473 );
1474
1475 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1476 print_debuginfo('Return status from check exist:'||x_return_status);
1477 print_debuginfo('Duplicate account id:'||l_dup_acct_id);
1478 END IF;
1479 IF ((not x_return_status = fnd_api.g_ret_sts_success) OR
1480 (not l_dup_acct_id is null)) THEN
1481 fnd_message.set_name('IBY', 'IBY_UNIQ_ACCOUNT');
1482 fnd_msg_pub.add;
1483 /*OPEN c_supplier(l_dup_acct_id);
1484 FETCH c_supplier INTO l_party_id;
1485 IF l_party_id IS NOT NULL THEN
1486 SELECT vendor_name, segment1 INTO l_supplier_name, l_supplier_number FROM ap_suppliers WHERE party_id = l_party_id;
1487 fnd_message.set_name('IBY', 'IBY_UNIQ_ACCOUNT_SUPPLIER');
1488 fnd_message.set_Token('SUPPLIER',l_supplier_name);
1489 fnd_message.set_Token('SUPPLIERNUMBER',l_supplier_number);
1490 fnd_msg_pub.add;
1491 END IF;
1492 CLOSE c_supplier;*/
1493 l_org_id := find_assignment_OU(l_dup_acct_id);
1494 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1495 print_debuginfo('l_org_id'||l_org_id);
1496 END IF;
1497 IF l_org_id <> -1 THEN
1498 select name into l_org_name from hr_operating_units where organization_id = l_org_id;
1499 fnd_message.set_name('IBY', 'IBY_UNIQ_ACCOUNT_OU');
1500 fnd_message.set_Token('OU', l_org_name);
1501 fnd_msg_pub.add;
1502 END IF;
1503 x_return_status := fnd_api.g_ret_sts_error;
1504 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1505 print_debuginfo('Error : Duplicate Bank Account');
1506 END IF;
1507 RAISE fnd_api.g_exc_error;
1508 END IF;
1509
1510 -- country specific validation API call here.
1511 -- delete the message as CE using message count for error
1512 fnd_msg_pub.delete_msg;
1513 x_msg_count:=0;
1514
1515 /*
1516 * Fix for bug 5413958:
1517 *
1518 * The country code is necessary to correctly validate
1519 * the external bank account.
1520 *
1521 * If the user has provided us the branch id, the country code
1522 * of the branch can be used.
1523 *
1524 * If we do not have a country code (because the user has not
1525 * provided the branch id), then use the country code specified
1526 * on the account record. In the UI, it is mandatory to specify
1527 * the country of the account, so we will always have the
1528 * country code on the account record.
1529 */
1530 IF (l_country IS NULL) THEN
1531
1532 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1533 print_debuginfo('Setting country code for bank account '
1534 || 'from the account record since branch id was not '
1535 || 'specified (country could not be derived from branch).'
1536 );
1537 END IF;
1538 l_country := p_ext_bank_acct_rec.country_code;
1539
1540 END IF;
1541
1542 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1543 print_debuginfo('Country code used for bank account '
1544 || 'validation: '
1545 || l_country
1546 );
1547 END IF;
1548 -- removed call to CE_VALIDATE_CD which fails because bank_number, branch_number are not passed -bug 6660595
1549 -- the call is deferred in update_ext_bank_acct() procedure
1550 -- Added call to CE_VALIDATE_CD when bank_number branch_number passed. For new bank/branch this validation will happen during update -- bug 9902829
1551 IF ((l_bank_number IS NOT NULL) AND (l_branch_number IS NOT NULL)) THEN
1552 CE_VALIDATE_BANKINFO.CE_VALIDATE_CD (l_country,
1553 p_ext_bank_acct_rec.check_digits,
1554 l_bank_number,
1555 l_branch_number,
1556 p_ext_bank_acct_rec.bank_account_num,
1557 FND_API.G_FALSE,
1558 x_msg_count,
1559 x_msg_data,
1560 x_return_status,
1561 'EXTERNAL');
1562 END IF;
1563
1564 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1565 print_debuginfo('Returned from Country Specific Check Digit Validations status:' || x_return_status);
1566 print_debuginfo('After Country Specific validations, l_masked_ba_num:'||
1567 l_masked_ba_num);
1568 END IF;
1569 IF not x_return_status=fnd_api.g_ret_sts_success THEN
1570 x_return_status := fnd_api.g_ret_sts_error;
1571 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1572 print_debuginfo('Account Validations Failed ');
1573 END IF;
1574 return;
1575 ELSE
1576 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1577 print_debuginfo('Account Validations Successful');
1578 END IF;
1579 END IF;
1580 /* Creating a new bank account*/
1581
1582 CE_VALIDATE_BANKINFO.UPD_ACCOUNT_VALIDATE (l_country,
1583 l_bank_number,
1584 l_branch_number,
1585 p_ext_bank_acct_rec.bank_account_num,
1586 l_bank_id,
1587 p_ext_bank_acct_rec.branch_id,
1588 null, -- account_id
1589 p_ext_bank_acct_rec.currency,
1590 p_ext_bank_acct_rec.acct_type,
1591 p_ext_bank_acct_rec.acct_suffix,
1592 null, -- p_secondary_acct_reference,
1593 p_ext_bank_acct_rec.bank_account_name,
1594 FND_API.G_FALSE,
1595 x_msg_count,
1596 x_msg_data,
1597 l_account_number,
1598 x_return_status,
1599 'EXTERNAL',
1600 null, --xcd
1601 l_bank_account_num_electronic);
1602
1603 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1604 print_debuginfo('Returned from Country Specific Account Validations');
1605
1606 END IF;
1607 IF not x_return_status=fnd_api.g_ret_sts_success THEN
1608 x_return_status := fnd_api.g_ret_sts_error;
1609 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1610 print_debuginfo('Account Validations Failed ');
1611 END IF;
1612 return;
1613 ELSE
1614 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1615 print_debuginfo('Account Validations Successful');
1616 END IF;
1617 END IF;
1618
1619 /* Bug - 9192335
1620 * Call Custom validations
1621 */
1622 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1623 print_debuginfo('Calling Custom Validations');
1624 END IF;
1625 IBY_ACCT_VAL_EXT_PUB.Validate_ext_bank_acct(p_ext_bank_acct_rec,l_ret_stat,l_error_msg);
1626 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1627 print_debuginfo('Return Status from Custom Validation::'||l_ret_stat);
1628 print_debuginfo('Error Message from Custom Validation::'||l_error_msg);
1629 END IF;
1630
1631 IF nvl(l_ret_stat,fnd_api.g_ret_sts_success) = fnd_api.g_ret_sts_error THEN
1632 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1633 print_debuginfo('Custom Validation Failed..');
1634 END IF;
1635 x_return_status := fnd_api.g_ret_sts_error;
1636 fnd_message.set_name('IBY', 'IBY_CUST_BANK_ACCT_VAL');
1637 fnd_message.set_Token('ERROR_MESSAGE',l_error_msg);
1638 fnd_msg_pub.add;
1639 RETURN;
1640 END IF;
1641
1642 Get_Mask_Settings(lx_mask_option,lx_unmask_len);
1643
1644 IF (NOT p_ext_bank_acct_rec.bank_account_num IS NULL) THEN
1645 l_masked_ba_num :=
1646 Mask_Bank_Number(nvl(l_account_number, p_ext_bank_acct_rec.bank_account_num),lx_mask_option,
1647 lx_unmask_len);
1648 l_ba_num_hash1 := iby_security_pkg.Get_Hash
1649 (nvl(l_account_number, p_ext_bank_acct_rec.bank_account_num),'F');
1650 l_ba_num_hash2 := iby_security_pkg.Get_Hash
1651 (nvl(l_account_number, p_ext_bank_acct_rec.bank_account_num),'T');
1652 END IF;
1653
1654 IF (NOT p_ext_bank_acct_rec.iban IS NULL) THEN
1655 l_masked_iban :=
1656 Mask_Bank_Number(p_ext_bank_acct_rec.iban,lx_mask_option,
1657 lx_unmask_len);
1658 l_iban_hash1 := iby_security_pkg.Get_Hash(p_ext_bank_acct_rec.iban,'F');
1659 l_iban_hash2 := iby_security_pkg.Get_Hash(p_ext_bank_acct_rec.iban,'T');
1660 END IF;
1661
1662 -- inserting the new account into IBY_EXT_BANK_ACCOUNTS
1663 INSERT INTO IBY_EXT_BANK_ACCOUNTS
1664 (
1665 EXT_BANK_ACCOUNT_ID,
1666 COUNTRY_CODE,
1667 BRANCH_ID,
1668 BANK_ID,
1669 BANK_ACCOUNT_NUM,
1670 BANK_ACCOUNT_NUM_HASH1,
1671 BANK_ACCOUNT_NUM_HASH2,
1672 MASKED_BANK_ACCOUNT_NUM,
1673 BA_MASK_SETTING,
1674 BA_UNMASK_LENGTH,
1675 CURRENCY_CODE,
1676 IBAN,
1677 IBAN_HASH1,
1678 IBAN_HASH2,
1679 MASKED_IBAN,
1680 CHECK_DIGITS,
1681 BANK_ACCOUNT_TYPE,
1682 ACCOUNT_CLASSIFICATION,
1683 ACCOUNT_SUFFIX,
1684 AGENCY_LOCATION_CODE,
1685 -- MULTI_CURRENCY_ALLOWED_FLAG,
1686 PAYMENT_FACTOR_FLAG,
1687 FOREIGN_PAYMENT_USE_FLAG,
1688 EXCHANGE_RATE_AGREEMENT_NUM,
1689 EXCHANGE_RATE_AGREEMENT_TYPE,
1690 EXCHANGE_RATE,
1691 HEDGING_CONTRACT_REFERENCE,
1692 -- STATUS,
1693 ATTRIBUTE_CATEGORY,
1694 ATTRIBUTE1,
1695 ATTRIBUTE2,
1696 ATTRIBUTE3,
1697 ATTRIBUTE4,
1698 ATTRIBUTE5,
1699 ATTRIBUTE6,
1700 ATTRIBUTE7,
1701 ATTRIBUTE8,
1702 ATTRIBUTE9,
1703 ATTRIBUTE10,
1704 ATTRIBUTE11,
1705 ATTRIBUTE12,
1706 ATTRIBUTE13,
1707 ATTRIBUTE14,
1708 ATTRIBUTE15,
1709 --REQUEST_ID,
1710 --PROGRAM_APPLICATION_ID,
1711 --PROGRAM_ID,
1712 --PROGRAM_UPDATE_DATE,
1713 START_DATE,
1714 END_DATE,
1715 CREATED_BY,
1716 CREATION_DATE,
1717 LAST_UPDATED_BY,
1718 LAST_UPDATE_DATE,
1719 LAST_UPDATE_LOGIN,
1720 OBJECT_VERSION_NUMBER,
1721 BANK_ACCOUNT_NAME,
1722 BANK_ACCOUNT_NAME_ALT,
1723 SHORT_ACCT_NAME,
1724 DESCRIPTION,
1725 ENCRYPTED,
1726 BANK_ACCOUNT_NUM_ELECTRONIC,
1727 SALT_VERSION,
1728 SECONDARY_ACCOUNT_REFERENCE,-- Bug 7408747
1729 CONTACT_NAME,
1730 CONTACT_PHONE,
1731 CONTACT_EMAIL,
1732 CONTACT_FAX
1733 )
1734 VALUES
1735 (
1736 IBY_EXT_BANK_ACCOUNTS_S.nextval, --EXT_BANK_ACCOUNT_ID,
1737 p_ext_bank_acct_rec.country_code, --COUNTRY_CODE,
1738 p_ext_bank_acct_rec.branch_id, --BRANCH_ID,
1739 p_ext_bank_acct_rec.bank_id, --BANK_ID,
1740 nvl(l_account_number, p_ext_bank_acct_rec.bank_account_num), --BANK_ACCOUNT_NUM,
1741 l_ba_num_hash1,
1742 l_ba_num_hash2,
1743 l_masked_ba_num,
1744 lx_mask_option,
1745 lx_unmask_len,
1746 p_ext_bank_acct_rec.currency, --CURRENCY_CODE,
1747 p_ext_bank_acct_rec.iban, --IBAN,
1748 l_iban_hash1,
1749 l_iban_hash2,
1750 l_masked_iban,
1751 p_ext_bank_acct_rec.check_digits, --CHECK_DIGITS,
1752 p_ext_bank_acct_rec.acct_type, --BANK_ACCOUNT_TYPE,
1753 'EXTERNAL', --ACCOUNT_CLASSIFICATION,
1754 p_ext_bank_acct_rec.acct_suffix, --ACCOUNT_SUFFIX,
1755 p_ext_bank_acct_rec.agency_location_code, --AGENCY_LOCATION_CODE,
1756 -- p_ext_bank_acct_rec.multi_currency_allowed_flag, --MULTI_CURRENCY_ALLOWED_FLAG,
1757 p_ext_bank_acct_rec.payment_factor_flag, --PAYMENT_FACTOR_FLAG,
1758 p_ext_bank_acct_rec.foreign_payment_use_flag, --FOREIGN_PAYMENT_USE_FLAG,
1759 p_ext_bank_acct_rec.exchange_rate_agreement_num, --EXCHANGE_RATE_AGREEMENT_NUM,
1760 p_ext_bank_acct_rec.exchange_rate_agreement_type, --EXCHANGE_RATE_AGREEMENT_TYPE,
1761 p_ext_bank_acct_rec.exchange_rate, --EXCHANGE_RATE,
1762 p_ext_bank_acct_rec.hedging_contract_reference, --HEDGING_CONTRACT_REFERENCE,
1763 -- p_ext_bank_acct_rec.status, --STATUS,
1764 p_ext_bank_acct_rec.attribute_category, --ATTRIBUTE_CATEGORY,
1765 p_ext_bank_acct_rec.attribute1, --ATTRIBUTE1,
1766 p_ext_bank_acct_rec.attribute2, --ATTRIBUTE2,
1767 p_ext_bank_acct_rec.attribute3, --ATTRIBUTE3
1768 p_ext_bank_acct_rec.attribute4, --ATTRIBUTE4,
1769 p_ext_bank_acct_rec.attribute5, --ATTRIBUTE5,
1770 p_ext_bank_acct_rec.attribute6, --ATTRIBUTE6,
1771 p_ext_bank_acct_rec.attribute7, --ATTRIBUTE7,
1772 p_ext_bank_acct_rec.attribute8, --ATTRIBUTE8,
1773 p_ext_bank_acct_rec.attribute9, --ATTRIBUTE9,
1774 p_ext_bank_acct_rec.attribute10, --ATTRIBUTE10,
1775 p_ext_bank_acct_rec.attribute11, --ATTRIBUTE11,
1776 p_ext_bank_acct_rec.attribute12, --ATTRIBUTE12,
1777 p_ext_bank_acct_rec.attribute13, --ATTRIBUTE13,
1778 p_ext_bank_acct_rec.attribute14, --ATTRIBUTE14,
1779 p_ext_bank_acct_rec.attribute15, --ATTRIBUTE15,
1780 --REQUEST_ID,
1781 --PROGRAM_APPLICATION_ID,
1782 --PROGRAM_ID,
1783 --PROGRAM_UPDATE_DATE,
1784 trunc(NVL(p_ext_bank_acct_rec.start_date, sysdate)), --START_DATE,
1785 trunc(p_ext_bank_acct_rec.end_date), --END_DATE,
1786 fnd_global.user_id, --CREATED_BY,
1787 sysdate, --CREATION_DATE,
1788 fnd_global.user_id, --LAST_UPDATED_BY,
1789 sysdate, --LAST_UPDATE_DATE,
1790 fnd_global.login_id, --LAST_UPDATE_LOGIN,
1791 1.0, --OBJECT_VERSION_NUMBER,
1792 p_ext_bank_acct_rec.bank_account_name, --BANK_ACCOUNT_NAME
1793 p_ext_bank_acct_rec.alternate_acct_name, --BANK_ACCOUNT_NAME_ALT
1794 p_ext_bank_acct_rec.short_acct_name, --SHORT_ACCT_NAME
1795 p_ext_bank_acct_rec.description, --DESCRIPTION
1796 'N', -- encrypted
1797 l_bank_account_num_electronic,
1798 iby_security_pkg.get_salt_version,
1799 p_ext_bank_acct_rec.secondary_account_reference, -- Bug 7408747
1800 p_ext_bank_acct_rec.contact_name,
1801 p_ext_bank_acct_rec.contact_phone,
1802 p_ext_bank_acct_rec.contact_email,
1803 p_ext_bank_acct_rec.contact_fax
1804 ) RETURNING EXT_BANK_ACCOUNT_ID INTO x_acct_id;
1805
1806 IF (SQL%FOUND) THEN
1807 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1808 print_debuginfo('New Row inserted in IBY_EXT_BANK_ACCOUNTS');
1809 END IF;
1810 ELSE
1811 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1812 print_debuginfo('Failed to insert in IBY_EXT_BANK_ACCOUNTS');
1813 END IF;
1814 RAISE fnd_api.g_exc_unexpected_error;
1815 END IF;
1816
1817 -- End of API
1818
1819 IF ((p_ext_bank_acct_rec.acct_owner_party_id IS NOT NULL) or
1820 (p_ext_bank_acct_rec.acct_owner_party_id <>-99)) THEN
1821
1822 -- Populate the primary Account owner if the Account Owner
1823 -- Party id is populated
1824 add_joint_account_owner(1.0,
1825 null,
1826 x_acct_id,
1827 p_ext_bank_acct_rec.acct_owner_party_id,
1828 l_joint_acct_owner_id,
1829 x_return_status,
1830 x_msg_count,
1831 x_msg_data,
1832 x_response);
1833
1834 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1835 print_debuginfo('Account Owner Id created : ' || l_joint_acct_owner_id);
1836
1837 END IF;
1838 -- Set the newly created Account Owner as Primary
1839 IF (l_joint_acct_owner_id IS NOT NULL) THEN
1840 change_primary_acct_owner (1.0,
1841 null,
1842 x_acct_id,
1843 p_ext_bank_acct_rec.acct_owner_party_id,
1844 x_return_status,
1845 x_msg_count,
1846 x_msg_data,
1847 x_response);
1848 END IF;
1849 END IF;
1850
1851 -- get message count and if count is 1, get message info.
1852 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1853 p_count => x_msg_count,
1854 p_data => x_msg_data);
1855
1856 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1857 print_debuginfo('RETURN ' || l_module_name);
1858
1859
1860 END IF;
1861 EXCEPTION
1862 WHEN fnd_api.g_exc_error THEN
1863 ROLLBACK TO create_ext_bank_acct_pub;
1864 x_return_status := fnd_api.g_ret_sts_error;
1865 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1866 print_debuginfo('Exception : ' || SQLERRM);
1867 END IF;
1868 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1869 p_count => x_msg_count,
1870 p_data => x_msg_data);
1871
1872
1873 WHEN fnd_api.g_exc_unexpected_error THEN
1874 ROLLBACK TO create_ext_bank_acct_pub;
1875 x_return_status := fnd_api.g_ret_sts_unexp_error;
1876 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1877 print_debuginfo('Exception : ' || SQLERRM);
1878 END IF;
1879 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1880 p_count => x_msg_count,
1881 p_data => x_msg_data);
1882
1883
1884 WHEN OTHERS THEN
1885 ROLLBACK TO create_ext_bank_acct_pub;
1886
1887 x_return_status := fnd_api.g_ret_sts_unexp_error;
1888 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
1889
1890 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1891 print_debuginfo('Exception : ' || SQLERRM);
1892 END IF;
1893 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1894 p_count => x_msg_count,
1895 p_data => x_msg_data);
1896
1897 END create_ext_bank_acct;
1898
1899
1900 --- Updated for the bug 6461487
1901 /* Over loaded procedure for using through API.
1902 This procedure is used to create the external bank account and
1903 assign the bank account at
1904 a. Supplier
1905 b. Supplier Site
1906 c. Address
1907 d. Address Operating Unit */
1908 PROCEDURE create_ext_bank_acct (
1909 p_api_version IN NUMBER,
1910 p_init_msg_list IN VARCHAR2,
1911 p_ext_bank_acct_rec IN ExtBankAcct_rec_type,
1912 p_association_level IN VARCHAR2,
1913 p_supplier_site_id IN NUMBER,
1914 p_party_site_id IN NUMBER,
1915 p_org_id IN NUMBER,
1916 p_org_type IN VARCHAR2 default NULL, --Bug7136876: new parameter
1917 x_acct_id OUT NOCOPY NUMBER,
1918 x_return_status OUT NOCOPY VARCHAR2,
1919 x_msg_count OUT NOCOPY NUMBER,
1920 x_msg_data OUT NOCOPY VARCHAR2,
1921 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1922 ) IS
1923 l_module CONSTANT VARCHAR2(40) := 'overloaded create_ext_bank_acct';
1924 l_insert_status BOOLEAN;
1925 l_assign_id NUMBER;
1926 l_rec IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_rec_type;
1927 l_assign IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
1928 l_payment_function CONSTANT VARCHAR2(30) := 'PAYABLES_DISB';
1929 l_instrument_type CONSTANT VARCHAR2(30) := 'BANKACCOUNT';
1930 l_party_site_id NUMBER;
1931 l_supp_site_id NUMBER;
1932 l_org_id NUMBER;
1933 l_org_type VARCHAR2(30);
1934 l_party_site_status VARCHAR2(1);
1935 L_INVALID_SUPPLIER_ID EXCEPTION;
1936 L_INVALID_PARTY_SITE EXCEPTION;
1937 L_INVALID_AO EXCEPTION;
1938 L_INVALID_ASSOCIATION_LEVEL EXCEPTION;
1939 l_association_level VARCHAR2(2) := Upper(p_association_level);
1940
1941 /* ADDED for Bug - 8209536 */
1942 l_country VARCHAR2(60);
1943 l_bank_id NUMBER(15) := null;
1944 l_bank_name VARCHAR2(360) := null;
1945 l_bank_number VARCHAR2(30) := null;
1946 l_branch_number VARCHAR2(30) := null;
1947
1948 -- picks up branch number
1949 CURSOR c_branch (p_branch_id NUMBER) IS
1950 SELECT bank_or_branch_number
1951 FROM hz_organization_profiles
1952 WHERE SYSDATE between TRUNC(effective_start_date)
1953 and NVL(TRUNC(effective_end_date), SYSDATE+1)
1954 AND party_id = p_branch_id;
1955
1956 BEGIN
1957 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1958 print_debuginfo('Enter '||l_module);
1959 END IF;
1960 SAVEPOINT create_ext_bank_acct_thru_api;
1961 IF(l_association_level <> 'S' AND
1962 l_association_level <> 'SS' AND
1963 l_association_level <> 'A' AND
1964 l_association_level <> 'AO') THEN
1965 RAISE L_INVALID_ASSOCIATION_LEVEL;
1966 END IF;
1967
1968 /* Bug 8209536
1969 * Country specific validation is inconsistent for UI and API
1970 *
1971 */
1972
1973 -- find bank info
1974 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1975 print_debuginfo('The value for p_ext_bank_acct_rec.branch_id :' ||p_ext_bank_acct_rec.branch_id);
1976 END IF;
1977 IF p_ext_bank_acct_rec.branch_id IS NOT NULL THEN
1978 find_bank_info(p_ext_bank_acct_rec.branch_id, x_return_status, l_bank_id, l_country, l_bank_name, l_bank_number);
1979 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1980 print_debuginfo('Got Bank Info : '||l_bank_name);
1981 print_debuginfo('Got Country : '||l_country);
1982
1983 END IF;
1984 -- find branch number
1985 OPEN c_branch(p_ext_bank_acct_rec.branch_id);
1986 FETCH c_branch INTO l_branch_number;
1987 IF c_branch%NOTFOUND THEN
1988 fnd_message.set_name('IBY', 'IBY_API_NO_BRANCH');
1989 fnd_msg_pub.add;
1990 x_return_status := fnd_api.g_ret_sts_error;
1991 RAISE fnd_api.g_exc_error;
1992 ELSE
1993 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1994 print_debuginfo('Got Branch Number : '||l_branch_number);
1995 END IF;
1996 END IF;
1997 CLOSE c_branch;
1998
1999 END IF;
2000
2001 -- bug 13330174, Added call to CE_VALIDATE_CD when bank_number & branch_number are passed.
2002 IF ((l_bank_number IS NOT NULL) AND (l_branch_number IS NOT NULL)) THEN
2003 CE_VALIDATE_BANKINFO.CE_VALIDATE_CD (l_country,
2004 p_ext_bank_acct_rec.check_digits,
2005 l_bank_number,
2006 l_branch_number,
2007 p_ext_bank_acct_rec.bank_account_num,
2008 FND_API.G_FALSE,
2009 x_msg_count,
2010 x_msg_data,
2011 x_return_status,
2012 'EXTERNAL');
2013 END IF;
2014
2015 IF(x_return_status<>fnd_api.g_ret_sts_success)
2016 THEN
2017 x_return_status := fnd_api.g_ret_sts_error;
2018 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2019 print_debuginfo('Account Validations Failed ');
2020 END IF;
2021 return;
2022
2023 END IF;
2024
2025 /* Creating a new bank account*/
2026 create_ext_bank_acct(p_api_version,
2027 p_init_msg_list,
2028 p_ext_bank_acct_rec,
2029 x_acct_id,
2030 x_return_status,
2031 x_msg_count,
2032 x_msg_data,
2033 x_response);
2034 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2035 print_debuginfo('Return Status after creating the bank account'||x_return_status);
2036 print_debuginfo('Account Id created'||x_acct_id);
2037
2038
2039 END IF;
2040 IF(l_association_level='SS')THEN
2041 IF(p_supplier_site_id IS NOT NULL) THEN
2042 BEGIN
2043 --select party_site_id, org_id, org_type
2044 -- INTO l_party_site_id, l_org_id, l_org_type
2045 -- from iby_external_payees_all
2046 --where payee_party_id=p_ext_bank_acct_rec.acct_owner_party_id AND
2047 -- PAYMENT_FUNCTION='PAYABLES_DISB' AND
2048 -- SUPPLIER_SITE_ID =p_supplier_site_id;
2049 select org_id, vendor_site_id
2050 INTO l_org_id, l_supp_site_id
2051 from ap_supplier_sites_all
2052 where vendor_site_id = p_supplier_site_id AND
2053 org_id = p_org_id;
2054 EXCEPTION
2055 WHEN OTHERS THEN
2056 RAISE L_INVALID_SUPPLIER_ID;
2057 END;
2058 IF(p_party_site_id IS NOT NULL) THEN
2059 SELECT status
2060 INTO l_party_site_status
2061 FROM HZ_PARTY_SITES
2062 WHERE party_site_id = p_party_site_id;
2063
2064 IF l_party_site_status = 'I' THEN
2065 RAISE L_INVALID_PARTY_SITE;
2066 END IF;
2067 END IF;
2068 l_rec.Party_Site_id :=p_party_site_id;
2069 l_rec.Supplier_Site_id:=p_supplier_site_id;
2070 l_rec.Org_Id:=l_org_id;
2071 l_rec.Org_Type:=p_org_type;
2072 ELSE
2073 RAISE L_INVALID_SUPPLIER_ID;
2074 END IF;
2075 ELSIF(l_association_level='A') THEN
2076 IF(p_party_site_id IS NOT NULL) THEN
2077 l_rec.Party_Site_id :=p_party_site_id;
2078 l_rec.Supplier_Site_id:=NULL;
2079 l_rec.Org_Id:=NULL;
2080 l_rec.Org_Type:=NULL;
2081 BEGIN
2082 SELECT status
2083 INTO l_party_site_status
2084 FROM HZ_PARTY_SITES
2085 WHERE party_site_id = p_party_site_id;
2086 EXCEPTION
2087 WHEN OTHERS THEN
2088 RAISE L_INVALID_PARTY_SITE;
2089 END;
2090 IF l_party_site_status = 'I' THEN
2091 RAISE L_INVALID_PARTY_SITE;
2092 END IF;
2093 ELSE
2094 RAISE L_INVALID_PARTY_SITE;
2095 END IF;
2096 ELSIF(l_association_level='AO') THEN
2097 IF(p_party_site_id IS NOT NULL AND p_org_id IS NOT NULL) THEN
2098 /*BEGIN
2099 select org_type
2100 INTO l_org_type
2101 from iby_external_payees_all
2102 where payee_party_id=p_ext_bank_acct_rec.acct_owner_party_id AND
2103 PAYMENT_FUNCTION='PAYABLES_DISB' AND
2104 PARTY_SITE_ID =p_party_site_id AND
2105 ORG_ID = p_org_id AND
2106 SUPPLIER_SITE_ID IS NOT NULL;
2107 EXCEPTION
2108 WHEN OTHERS THEN
2109 RAISE L_INVALID_AO;
2110 END;
2111 */
2112 print_debuginfo('Party site id, org id is not null ');
2113 ELSE
2114 RAISE L_INVALID_AO;
2115 END IF;
2116 l_rec.Party_Site_id :=p_party_site_id;
2117 l_rec.Org_Id:= p_org_id;
2118 l_rec.Org_Type:= p_org_type;
2119 l_rec.Supplier_Site_id:=NULL;
2120 ELSIF(l_association_level='S') THEN
2121 l_rec.Party_Site_id :=NULL;
2122 l_rec.Org_Id:= NULL;
2123 l_rec.Org_Type:= NULL;
2124 l_rec.Supplier_Site_id:=NULL;
2125 END IF;
2126 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2127 print_debuginfo('orgid :'||l_rec.Org_Id);
2128 print_debuginfo('org type :'||l_rec.Org_Type);
2129 print_debuginfo('party_site_id :'||l_rec.Party_Site_id);
2130 print_debuginfo('Supplier_site_id :'||l_rec.Supplier_Site_id);
2131
2132
2133 END IF;
2134 l_rec.Payment_Function :=l_payment_function;
2135 l_rec.Party_Id :=p_ext_bank_acct_rec.acct_owner_party_id;
2136 l_assign.Instrument.Instrument_Type := l_instrument_type;
2137 l_assign.Instrument.Instrument_Id := x_acct_id;
2138 IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment(
2139 p_api_version,
2140 NULL,
2141 NULL,
2142 x_return_status,
2143 x_msg_count,
2144 x_msg_data,
2145 l_rec,
2146 l_assign,
2147 l_assign_id,
2148 x_response);
2149
2150 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2151 print_debuginfo('Exit '||l_module);
2152 END IF;
2153 EXCEPTION
2154
2155 WHEN FND_API.G_EXC_ERROR THEN
2156 ROLLBACK TO create_ext_bank_acct_thru_api;
2157 x_return_status := fnd_api.g_ret_sts_error;
2158 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2159 print_debuginfo('Exception : ' || SQLERRM);
2160 END IF;
2161 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2162 p_count => x_msg_count,
2163 p_data => x_msg_data);
2164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2165 ROLLBACK TO create_ext_bank_acct_thru_api;
2166 x_return_status := fnd_api.g_ret_sts_unexp_error;
2167 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2168 print_debuginfo('Exception : ' || SQLERRM);
2169 END IF;
2170 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2171 p_count => x_msg_count,
2172 p_data => x_msg_data);
2173 WHEN L_INVALID_SUPPLIER_ID THEN
2174 ROLLBACK TO create_ext_bank_acct_thru_api;
2175 x_return_status := FND_API.G_RET_STS_ERROR ;
2176 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2177 print_debuginfo('Exception : invalid supplier id');
2178 END IF;
2179 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2180 p_count => x_msg_count,
2181 p_data => x_msg_data);
2182 WHEN L_INVALID_PARTY_SITE THEN
2183 ROLLBACK TO create_ext_bank_acct_thru_api;
2184 x_return_status := FND_API.G_RET_STS_ERROR ;
2185 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2186 print_debuginfo('Exception : invalid party site id');
2187 END IF;
2188 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2189 p_count => x_msg_count,
2190 p_data => x_msg_data);
2191 WHEN L_INVALID_AO THEN
2192 ROLLBACK TO create_ext_bank_acct_thru_api;
2193 x_return_status := FND_API.G_RET_STS_ERROR ;
2194 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2195 print_debuginfo('Exception : invalid combination of party site id and org id');
2196 END IF;
2197 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2198 p_count => x_msg_count,
2199 p_data => x_msg_data);
2200 WHEN L_INVALID_ASSOCIATION_LEVEL THEN
2201 ROLLBACK TO create_ext_bank_acct_thru_api;
2202 x_return_status := FND_API.G_RET_STS_ERROR ;
2203 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2204 print_debuginfo('Exception : invalid Association level');
2205 END IF;
2206 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2207 p_count => x_msg_count,
2208 p_data => x_msg_data);
2209 WHEN OTHERS THEN
2210 ROLLBACK TO create_ext_bank_acct_thru_api;
2211 x_return_status := fnd_api.g_ret_sts_unexp_error;
2212 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module, null);
2213 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2214 print_debuginfo('Exception : ' || SQLERRM);
2215 END IF;
2216 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2217 p_count => x_msg_count,
2218 p_data => x_msg_data);
2219
2220 END create_ext_bank_acct;
2221
2222
2223 -- 10. update_ext_bank_acct
2224 --
2225 -- API name : update_ext_bank_acct
2226 -- Type : Public
2227 -- Pre-reqs : None
2228 -- Function : Updates an external bank account
2229 -- Current version : 1.0
2230 -- Previous version: 1.0
2231 -- Initial version : 1.0
2232 --
2233 PROCEDURE update_ext_bank_acct (
2234 p_api_version IN NUMBER,
2235 p_init_msg_list IN VARCHAR2,
2236 p_ext_bank_acct_rec IN OUT NOCOPY ExtBankAcct_rec_type,
2237 x_return_status OUT NOCOPY VARCHAR2,
2238 x_msg_count OUT NOCOPY NUMBER,
2239 x_msg_data OUT NOCOPY VARCHAR2,
2240 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
2241 ) IS
2242
2243 l_api_name CONSTANT VARCHAR2(30) := 'update_ext_bank_acct';
2244 l_api_version CONSTANT NUMBER := 1.0;
2245 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
2246
2247 l_country VARCHAR2(60);
2248 l_old_ovn NUMBER(15);
2249 l_count NUMBER;
2250 l_bank_number VARCHAR2(30);
2251 l_bank_id NUMBER(15);
2252 l_branch_id NUMBER(15);
2253 l_bank_name VARCHAR2(360);
2254 l_branch_number VARCHAR2(30);
2255 l_acct_number iby_ext_bank_accounts.bank_account_num%TYPE;
2256 l_mask_option iby_ext_bank_accounts.ba_mask_setting%TYPE;
2257 l_unmask_len iby_ext_bank_accounts.ba_unmask_length%TYPE;
2258 l_masked_ba_num iby_ext_bank_accounts.masked_bank_account_num%TYPE;
2259 l_masked_iban iby_ext_bank_accounts.masked_iban%TYPE;
2260 l_ba_num_hash1 iby_ext_bank_accounts.bank_account_num_hash1%TYPE;
2261 l_ba_num_hash2 iby_ext_bank_accounts.bank_account_num_hash1%TYPE;
2262 l_iban_hash1 iby_ext_bank_accounts.iban_hash1%TYPE;
2263 l_iban_hash2 iby_ext_bank_accounts.iban_hash2%TYPE;
2264 l_iban iby_ext_bank_accounts.iban%TYPE;
2265 l_encrypted iby_ext_bank_accounts.encrypted%TYPE;
2266 l_ba_segment_id iby_ext_bank_accounts.ba_num_sec_segment_id%TYPE;
2267 l_ba_elec_segment_id iby_ext_bank_accounts.ba_num_elec_sec_segment_id%TYPE;
2268 l_iban_segment_id iby_ext_bank_accounts.iban_sec_segment_id%TYPE;
2269 l_bank_account_num_electronic iby_ext_bank_accounts.bank_account_num_electronic%TYPE;
2270 l_old_iban iby_ext_bank_accounts.iban%TYPE;
2271 l_old_masked_iban iby_ext_bank_accounts.masked_iban%TYPE;
2272 l_old_iban_hash1 iby_ext_bank_accounts.iban_hash1%TYPE;
2273 l_old_iban_hash2 iby_ext_bank_accounts.iban_hash2%TYPE;
2274 l_dup_acct_id number;
2275 l_dup_start_date date;
2276 l_dup_end_date date;
2277 l_party_id ap_suppliers.party_id%TYPE;
2278 l_supplier_name ap_suppliers.vendor_name%TYPE;
2279 l_supplier_number ap_suppliers.segment1%TYPE;
2280
2281 l_error_msg VARCHAR2(500);
2282 l_ret_stat VARCHAR2(1);
2283 l_org_id iby_external_payees_all.org_id%TYPE;
2284 l_org_name hr_operating_units.name%TYPE;
2285 -- Get Object Version Number
2286 CURSOR c_ovn (p_acct_id NUMBER) IS
2287 SELECT object_version_number,
2288 bank_account_num_hash1,
2289 bank_account_num_hash2,
2290 iban_hash1,
2291 iban_hash2,
2292 ba_mask_setting,
2293 ba_unmask_length,
2294 ba_num_sec_segment_id,
2295 iban_sec_segment_id,
2296 encrypted,
2297 iban,
2298 masked_iban,
2299 ba_num_elec_sec_segment_id --Changes in Bank Account Num should also consider Electronic BA Num
2300 FROM IBY_EXT_BANK_ACCOUNTS
2301 WHERE EXT_BANK_ACCOUNT_ID = p_acct_id;
2302
2303 -- checks if account already exists
2304 -- a duplicate bank account is one with the same account_number and
2305 -- and currency for the same bank and branch.
2306 -- checks if account already exists
2307
2308
2309 CURSOR uniq_check (p_account_num_hash1 VARCHAR2,
2310 p_account_num_hash2 VARCHAR2,
2311 p_currency VARCHAR2,
2312 p_bank_id NUMBER,
2313 p_branch_id NUMBER,
2314 p_bank_acct_id NUMBER) IS
2315 SELECT count(*)
2316 FROM IBY_EXT_BANK_ACCOUNTS_V
2317 WHERE
2318 (bank_acct_num_hash1 = p_account_num_hash1)
2319 AND (bank_acct_num_hash2 = p_account_num_hash2)
2320 -- AND (p_currency IS NULL OR CURRENCY_CODE = p_currency)
2321 AND (p_bank_id IS NULL and BANK_PARTY_ID is NULL) OR (BANK_PARTY_ID = p_bank_id)
2322 AND (p_branch_id IS NULL and BRANCH_PARTY_ID is NULL) OR (BRANCH_PARTY_ID = p_branch_id)
2323 AND EXT_BANK_ACCOUNT_ID<>p_bank_acct_id;
2324
2325 -- get bank and branch numbers
2326 CURSOR c_bank_branch (p_bank_id NUMBER, p_branch_id NUMBER)IS
2327 SELECT BANK_NUMBER, BRANCH_NUMBER
2328 FROM CE_BANK_BRANCHES_V
2329 WHERE BANK_PARTY_ID = p_bank_id
2330 AND BRANCH_PARTY_ID = p_branch_id;
2331
2332 CURSOR c_supplier(p_acct_id NUMBER) IS
2333 SELECT owners.account_owner_party_id
2334 FROM iby_pmt_instr_uses_all instrument,
2335 IBY_ACCOUNT_OWNERS owners,
2336 iby_external_payees_all payees
2337 WHERE
2338 owners.primary_flag = 'Y' AND
2339 owners.ext_bank_account_id = p_acct_id AND
2340 owners.ext_bank_account_id = instrument.instrument_id AND
2341 payees.ext_payee_id = instrument.ext_pmt_party_id AND
2342 payees.payee_party_id = owners.account_owner_party_id;
2343
2344
2345 BEGIN
2346
2347 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2348 print_debuginfo('ENTER ' || l_module_name);
2349 print_debuginfo('DEBUG- The value of account number :' || p_ext_bank_acct_rec.bank_account_num);
2350
2351 END IF;
2352 SAVEPOINT update_ext_bank_acct_pub;
2353
2354 -- Standard call to check for call compatibility.
2355 IF NOT FND_API.Compatible_API_Call(l_api_version,
2356 p_api_version,
2357 l_api_name,
2358 G_PKG_NAME) THEN
2359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2360 END IF;
2361
2362 -- Initialize message list if p_init_msg_list is set to TRUE.
2363 IF FND_API.to_Boolean(p_init_msg_list) THEN
2364 FND_MSG_PUB.initialize;
2365 END IF;
2366
2367 -- Initialize API return status to success
2368 x_return_status := FND_API.G_RET_STS_SUCCESS;
2369
2370 -- Start of API Body
2371
2372 -- Parameter validations
2373 check_mandatory('IBY_BANKACCT_ID_FIELD', p_ext_bank_acct_rec.bank_account_id);
2374 -- no need to check the bank account number for update case
2375 -- check_mandatory('Bank Account Number', p_ext_bank_acct_rec.bank_account_num);
2376 --check_mandatory('Currency', p_ext_bank_acct_rec.currency);
2377 check_mandatory('IBY_OBJ_VER_NUM', p_ext_bank_acct_rec.object_version_number);
2378
2379 -- check object version number to make sure the record has not been updated
2380 OPEN c_ovn(p_ext_bank_acct_rec.bank_account_id);
2381 FETCH c_ovn INTO l_old_ovn,
2382 l_ba_num_hash1,
2383 l_ba_num_hash2,
2384 l_old_iban_hash1,
2385 l_old_iban_hash2,
2386 l_mask_option,
2387 l_unmask_len,
2388 l_ba_segment_id,
2389 l_iban_segment_id,
2390 l_encrypted,
2391 l_old_iban,
2392 l_old_masked_iban,
2393 l_ba_elec_segment_id;
2394 IF c_ovn%NOTFOUND THEN
2395 fnd_message.set_name('IBY', 'IBY_API_NO_EXT_BANK_ACCT');
2396 fnd_msg_pub.add;
2397 x_return_status := fnd_api.g_ret_sts_error;
2398 CLOSE c_ovn;
2399 RAISE fnd_api.g_exc_error;
2400 END IF;
2401 CLOSE c_ovn;
2402 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2403 print_debuginfo('Current object_version_number Version Number ' || l_old_ovn);
2404
2405 END IF;
2406 IF l_old_ovn <> p_ext_bank_acct_rec.object_version_number THEN
2407 fnd_message.set_name('IBY', 'IBY_DATA_VERSION_ERROR');
2408 fnd_msg_pub.add;
2409 x_return_status := fnd_api.g_ret_sts_error;
2410 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2411 print_debuginfo('Error: Object Version Number Mismatch');
2412 END IF;
2413 RAISE fnd_api.g_exc_error;
2414 END IF;
2415 -- no need for unique check
2416 -- perform unique check for account
2417 -- passing in the ext bank account id to the query for unique check
2418 /*
2419
2420 OPEN uniq_check(p_ext_bank_acct_rec.bank_account_num,
2421 p_ext_bank_acct_rec.currency,
2422 p_ext_bank_acct_rec.bank_id,
2423 p_ext_bank_acct_rec.branch_id,
2424 p_ext_bank_acct_rec.bank_account_id);
2425 FETCH uniq_check into l_count;
2426 IF (l_count > 1) THEN
2427 fnd_message.set_name('IBY', 'IBY_UNIQ_ACCOUNT');
2428 fnd_msg_pub.add;
2429 x_return_status := fnd_api.g_ret_sts_error;
2430 RAISE fnd_api.g_exc_error;
2431 END IF;
2432 CLOSE uniq_check;
2433
2434
2435
2436 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2437 print_debuginfo('Return status from check exist:'||x_return_status);
2438 print_debuginfo('Duplicate account id:'||l_dup_acct_id);
2439 END IF;
2440 IF ((not x_return_status = fnd_api.g_ret_sts_success) OR
2441 (not l_dup_acct_id is null)) THEN
2442 fnd_message.set_name('IBY', 'IBY_UNIQ_ACCOUNT');
2443 fnd_msg_pub.add;
2444 x_return_status := fnd_api.g_ret_sts_error;
2445 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2446 print_debuginfo('Error : Duplicate Bank Account');
2447 END IF;
2448 RAISE fnd_api.g_exc_error;
2449 END IF;
2450
2451 */
2452
2453 -- get branch id
2454 -- Bug 5739075 : l_bank_number and l_branch_number were not populated
2455 -- resulting into CE API failure
2456
2457 OPEN c_bank_branch (p_ext_bank_acct_rec.bank_id,
2458 p_ext_bank_acct_rec.branch_id);
2459 FETCH c_bank_branch INTO l_bank_number, l_branch_number;
2460 CLOSE c_bank_branch;
2461
2462
2463 -- country specific validation API call here.
2464 l_country:=p_ext_bank_acct_rec.country_code;
2465
2466 -- handle the case user doesn't change bank account number
2467
2468 l_acct_number := p_ext_bank_acct_rec.bank_account_num;
2469
2470 /* Bug 14456128 Moved Bank Account comparision after Country level
2471 validations as the bank account number gets updated
2472 by CE applying custom validations and other validations
2473 */
2474
2475 /*begin
2476
2477 if (NOT l_acct_number IS NULL) then
2478 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2479 print_debuginfo('The value of Get_Hash(l_acct_number,F): '||
2480 iby_security_pkg.Get_Hash(l_acct_number,'F'));
2481 print_debuginfo('The value of Get_Hash(l_acct_number,T): '||
2482 iby_security_pkg.Get_Hash(l_acct_number,'T'));
2483 print_debuginfo('The value of l_ba_num_hash1: ' ||l_ba_num_hash1);
2484 print_debuginfo('The value of l_ba_num_hash1: '|| l_ba_num_hash2);
2485
2486 END IF;
2487 if ( (iby_security_pkg.Get_Hash(l_acct_number,'F') = l_ba_num_hash1)
2488 AND (iby_security_pkg.Get_Hash(l_acct_number,'T') = l_ba_num_hash2)
2489 )
2490 then
2491 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2492 print_debuginfo('User doesnt change the bank account number');
2493 END IF;
2494 l_acct_number:=null;
2495 l_ba_num_hash1:=null;
2496 l_ba_num_hash2:=null;
2497 l_masked_ba_num:=null;
2498 else
2499 l_ba_num_hash1 := iby_security_pkg.Get_Hash(l_acct_number,'F');
2500
2501 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2502 print_debuginfo('User has changed the bank account number');
2503 print_debuginfo('The value of l_masked_ba_num before masking:'||
2504 l_masked_ba_num);
2505 END IF;
2506 l_ba_num_hash2 := iby_security_pkg.Get_Hash(l_acct_number,'T');
2507 l_masked_ba_num :=
2508 Mask_Bank_Number(l_acct_number,l_mask_option,l_unmask_len);
2509 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2510 print_debuginfo('The value of l_masked_ba_num after masking:'||
2511 l_masked_ba_num);
2512 END IF;
2513 l_encrypted := 'N';
2514 DELETE FROM iby_security_segments
2515 WHERE sec_segment_id = l_ba_segment_id;
2516 end if;
2517 end if;
2518 exception
2519 when others then
2520 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2521 print_debuginfo('Unknown exception in bank account number compare');
2522 END IF;
2523 -- the bank account number is not provided by the user
2524 l_acct_number :=null;
2525
2526 end;*/
2527 /* Bug - 6935905
2528 The function validate_IBAN is returning the null value always for
2529 l_iban. Hence as we have that value, checking for return status
2530 and taking the existing value(p_ext_bank_acct_rec.iban).
2531
2532 logic for updating the iban is not handled for the case of changing
2533 the existing iban to null value. Hence got iban values and changed
2534 the logic.
2535 */
2536 /*
2537 l_iban :=p_ext_bank_acct_rec.iban;
2538 begin
2539 if (NOT l_iban IS NULL) then
2540 if ( (iby_security_pkg.Get_Hash(l_iban,'F') = l_iban_hash1)
2541 AND (iby_security_pkg.Get_Hash(l_iban,'T') = l_iban_hash2)
2542 )
2543 then
2544 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2545 print_debuginfo('User doesnt change the IBAN');
2546 END IF;
2547 l_iban:=null;
2548 l_iban_hash1:=null;
2549 l_iban_hash2:=null;
2550 l_masked_iban:=null;
2551 else
2552 CE_BANK_AND_ACCOUNT_VALIDATION.validate_IBAN
2553 (p_ext_bank_acct_rec.iban, l_iban, x_return_status);
2554 -- throw exception???
2555 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2556 print_debuginfo('Validated IBAN Number');
2557
2558 END IF;
2559 l_iban_hash1 := iby_security_pkg.Get_Hash(l_iban,'F');
2560 l_iban_hash2 := iby_security_pkg.Get_Hash(l_iban,'T');
2561 l_masked_iban := Mask_Bank_Number(l_iban,l_mask_option,l_unmask_len);
2562
2563 l_encrypted := 'N';
2564 DELETE FROM iby_security_segments
2565 WHERE sec_segment_id = l_iban_segment_id;
2566 end if;
2567 end if;
2568 exception
2569 when others then
2570
2571 -- the bank account number is not provided by the user
2572 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2573 print_debuginfo('Unknown exception in iban compare');
2574 END IF;
2575 l_iban :=null;
2576
2577 end;
2578 */
2579
2580 l_iban :=p_ext_bank_acct_rec.iban;
2581 begin
2582 if (Nvl(l_old_iban,'null')<> Nvl(l_iban,'null')) then
2583 IF(NOT l_iban IS NULL) THEN
2584 CE_BANK_AND_ACCOUNT_VALIDATION.validate_IBAN
2585 (p_ext_bank_acct_rec.iban, l_iban, x_return_status);
2586 -- throw exception???
2587 IF x_return_status=fnd_api.g_ret_sts_success THEN
2588 l_iban :=p_ext_bank_acct_rec.iban;
2589 ELSE
2590 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2591 print_debuginfo('IBAN Validation Failed ');
2592 END IF;
2593 RAISE fnd_api.g_exc_error;
2594 END IF;
2595 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2596 print_debuginfo('Validated IBAN Number');
2597 print_debuginfo(x_return_status);
2598 print_debuginfo(l_iban);
2599 END IF;
2600 l_iban_hash1 := iby_security_pkg.Get_Hash(l_iban,'F');
2601 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2602 print_debuginfo(l_iban_hash1);
2603 END IF;
2604 l_iban_hash2 := iby_security_pkg.Get_Hash(l_iban,'T');
2605 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2606 print_debuginfo(l_iban_hash2);
2607 END IF;
2608 l_masked_iban := Mask_Bank_Number(l_iban,l_mask_option,l_unmask_len);
2609 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2610 print_debuginfo(l_masked_iban);
2611 END IF;
2612 ELSE
2613 l_iban_hash1 :=NULL;
2614 l_iban_hash2 :=NULL;
2615 l_masked_iban :=NULL;
2616 END IF;
2617
2618 l_encrypted := 'N';
2619 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2620 print_debuginfo('before delete');
2621 END IF;
2622 DELETE FROM iby_security_segments
2623 WHERE sec_segment_id = l_iban_segment_id;
2624 /* Segment_id should be nulled out if there
2625 is any modification to IBAN*/
2626 l_iban_segment_id := null;
2627 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2628 print_debuginfo('After delete');
2629
2630 END IF;
2631 ELSE
2632 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2633 print_debuginfo('User doesnt change the IBAN');
2634 END IF;
2635 l_iban:=l_old_iban;
2636 l_iban_hash1:=l_old_iban_hash1;
2637 l_iban_hash2:=l_old_iban_hash2;
2638 l_masked_iban:=l_old_masked_iban;
2639 end if;
2640 exception
2641 when fnd_api.g_exc_error then
2642 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2643 print_debuginfo('Exception in iban compare');
2644 END IF;
2645 Raise fnd_api.g_exc_error;
2646 when others then
2647
2648 -- the bank account number is not provided by the user
2649 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2650 print_debuginfo('Unknown exception in iban compare');
2651 END IF;
2652 l_iban :=l_old_iban;
2653
2654 end;
2655
2656
2657
2658 -- calling our own check bank account exists
2659
2660 check_ext_acct_exist(
2661 p_api_version,
2662 p_init_msg_list,
2663 p_ext_bank_acct_rec,
2664 l_dup_acct_id,
2665 l_dup_start_date,
2666 l_dup_end_date,
2667 x_return_status,
2668 x_msg_count,
2669 x_msg_data,
2670 x_response
2671 );
2672
2673 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2674 print_debuginfo('Return status from check exist:'||x_return_status);
2675 print_debuginfo('Duplicate account id:'||l_dup_acct_id);
2676 END IF;
2677 IF ((not x_return_status = fnd_api.g_ret_sts_success) OR
2678 (not l_dup_acct_id is null)) THEN
2679 fnd_message.set_name('IBY', 'IBY_UNIQ_ACCOUNT');
2680 fnd_msg_pub.add;
2681 /*OPEN c_supplier(l_dup_acct_id);
2682 FETCH c_supplier INTO l_party_id;
2683 IF l_party_id IS NOT NULL THEN
2684 SELECT vendor_name, segment1 INTO l_supplier_name, l_supplier_number FROM ap_suppliers WHERE party_id = l_party_id;
2685 fnd_message.set_name('IBY', 'IBY_UNIQ_ACCOUNT_SUPPLIER');
2686 fnd_message.set_Token('SUPPLIER',l_supplier_name);
2687 fnd_message.set_Token('SUPPLIERNUMBER',l_supplier_number);
2688 fnd_msg_pub.add;
2689 END IF;
2690 CLOSE c_supplier;*/
2691 l_org_id := find_assignment_OU(l_dup_acct_id);
2692 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2693 print_debuginfo('l_org_id::'||l_org_id);
2694 END IF;
2695 IF l_org_id <> -1 THEN
2696 select name into l_org_name from hr_operating_units where organization_id = l_org_id;
2697 fnd_message.set_name('IBY', 'IBY_UNIQ_ACCOUNT_OU');
2698 fnd_message.set_Token('OU',l_org_name);
2699 fnd_msg_pub.add;
2700 END IF;
2701 x_return_status := fnd_api.g_ret_sts_error;
2702 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2703 print_debuginfo('Error : Duplicate Bank Account');
2704 END IF;
2705 RAISE fnd_api.g_exc_error;
2706 END IF;
2707
2708 -- country specific validation API call here.
2709 -- delete the message as CE using message count for error
2710 fnd_msg_pub.delete_msg;
2711 x_msg_count:=0;
2712
2713
2714 --Get_Mask_Settings(lx_mask_option,lx_unmask_len);
2715
2716
2717
2718 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2719 print_debuginfo('DEBUG-l_country : ' || l_country);
2720 print_debuginfo('DEBUG-l_bank_number : ' || l_bank_number);
2721 print_debuginfo('DEBUG-l_branch_number : ' || l_branch_number);
2722
2723 print_debuginfo('DEBUG-p_ext_bank_acct_rec.check_digits : ' || p_ext_bank_acct_rec.check_digits);
2724 print_debuginfo('DEBUG- The value of l_masked_ba_num:'||l_masked_ba_num);
2725 print_debuginfo('DEBUG- The value of account number passed to CE''s API:' || p_ext_bank_acct_rec.bank_account_num);
2726
2727 END IF;
2728
2729
2730 -- removed check for bank_id and branch_id being not null - bug5486957 [taken back in bug 5739075 ]
2731 CE_VALIDATE_BANKINFO.CE_VALIDATE_CD (l_country,
2732 p_ext_bank_acct_rec.check_digits,
2733 l_bank_number,
2734 l_branch_number,
2735 p_ext_bank_acct_rec.bank_account_num,
2736 FND_API.G_FALSE,
2737 x_msg_count,
2738 x_msg_data,
2739 x_return_status,
2740 'EXTERNAL');
2741
2742 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2743 print_debuginfo('Returned from Country Specific Check Digit Validations status:' || x_return_status);
2744 print_debuginfo('After Country Specific validations, l_masked_ba_num:'||
2745 l_masked_ba_num);
2746 END IF;
2747 IF not x_return_status=fnd_api.g_ret_sts_success THEN
2748
2749 x_return_status := fnd_api.g_ret_sts_error;
2750 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2751 print_debuginfo('Account Validations Failed ');
2752 END IF;
2753 return;
2754 ELSE
2755 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2756 print_debuginfo('Account Validations Successful');
2757 END IF;
2758 END IF;
2759
2760
2761 CE_VALIDATE_BANKINFO.UPD_ACCOUNT_VALIDATE ( l_country,
2762 l_bank_number,
2763 l_branch_number,
2764 p_ext_bank_acct_rec.bank_account_num,
2765 l_bank_id,
2766 l_branch_id,
2767 null, -- account_id
2768 p_ext_bank_acct_rec.currency,
2769 p_ext_bank_acct_rec.acct_type,
2770 p_ext_bank_acct_rec.acct_suffix,
2771 null, -- p_secondary_acct_reference,
2772 p_ext_bank_acct_rec.bank_account_name,
2773 FND_API.G_FALSE,
2774 x_msg_count,
2775 x_msg_data,
2776 l_acct_number,
2777 x_return_status,
2778 'EXTERNAL',
2779 null, --xcd
2780 l_bank_account_num_electronic);
2781
2782 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2783 print_debuginfo('After CEs and before masking ' || l_masked_ba_num);
2784 END IF;
2785
2786 IF (NOT p_ext_bank_acct_rec.bank_account_num IS NULL) THEN
2787
2788 begin
2789
2790 if (NOT l_acct_number IS NULL) then
2791 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2792 print_debuginfo('The value of Get_Hash(l_acct_number,F): '||
2793 iby_security_pkg.Get_Hash(l_acct_number,'F'));
2794 print_debuginfo('The value of Get_Hash(l_acct_number,T): '||
2795 iby_security_pkg.Get_Hash(l_acct_number,'T'));
2796 print_debuginfo('The value of l_ba_num_hash1: ' ||l_ba_num_hash1);
2797 print_debuginfo('The value of l_ba_num_hash1: '|| l_ba_num_hash2);
2798
2799 END IF;
2800 if ( (iby_security_pkg.Get_Hash(l_acct_number,'F') = l_ba_num_hash1)
2801 AND (iby_security_pkg.Get_Hash(l_acct_number,'T') = l_ba_num_hash2)
2802 )
2803 then
2804 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2805 print_debuginfo('User doesnt change the bank account number');
2806 END IF;
2807 l_acct_number:=null;
2808 l_ba_num_hash1:=null;
2809 l_ba_num_hash2:=null;
2810 l_masked_ba_num:=null;
2811 l_bank_account_num_electronic:=null; --Should not touch Elec Bank account num as well
2812 else
2813 l_ba_num_hash1 := iby_security_pkg.Get_Hash(l_acct_number,'F');
2814
2815 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2816 print_debuginfo('User has changed the bank account number');
2817 print_debuginfo('The value of l_masked_ba_num before masking:'||
2818 l_masked_ba_num);
2819 END IF;
2820 l_ba_num_hash2 := iby_security_pkg.Get_Hash(l_acct_number,'T');
2821 l_masked_ba_num :=
2822 Mask_Bank_Number(l_acct_number,l_mask_option,l_unmask_len);
2823 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2824 print_debuginfo('The value of l_masked_ba_num after masking:'||
2825 l_masked_ba_num);
2826 END IF;
2827 l_encrypted := 'N';
2828 DELETE FROM iby_security_segments
2829 WHERE sec_segment_id = l_ba_segment_id;
2830 /* Electronic Bank Account should also
2831 get updated where Bank Account num is
2832 updated there for segment_id for
2833 electronic Bank Account should also be
2834 deleted.*/
2835 DELETE FROM iby_security_segments
2836 WHERE sec_segment_id = l_ba_elec_segment_id;
2837 /* Segment_id should be nulled out if
2838 there is any modification BankAccount Num*/
2839 l_ba_segment_id := null;
2840 l_ba_elec_segment_id := null;
2841 end if;
2842 end if;
2843 exception
2844 when others then
2845 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2846 print_debuginfo('Unknown exception in bank account number compare');
2847 END IF;
2848 -- the bank account number is not provided by the user
2849 l_acct_number :=null;
2850 end;
2851 print_debuginfo('Re-masking the account number' );
2852 l_masked_ba_num :=
2853 Mask_Bank_Number(nvl(l_acct_number, p_ext_bank_acct_rec.bank_account_num),l_mask_option,
2854 l_unmask_len);
2855
2856 l_ba_num_hash1 := iby_security_pkg.Get_Hash
2857 (nvl(l_acct_number, p_ext_bank_acct_rec.bank_account_num),'F');
2858 l_ba_num_hash2 := iby_security_pkg.Get_Hash
2859 (nvl(l_acct_number, p_ext_bank_acct_rec.bank_account_num),'T');
2860
2861 END IF;
2862
2863 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2864 print_debuginfo('Returned from Account Validations' || x_return_status);
2865 print_debuginfo('After CE s and after masking l_masked_ba_num:'||l_masked_ba_num);
2866 END IF;
2867 IF not x_return_status=fnd_api.g_ret_sts_success THEN
2868 x_return_status := fnd_api.g_ret_sts_error;
2869 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2870 print_debuginfo('Account Validations Failed ');
2871 END IF;
2872 return;
2873 ELSE
2874 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2875 print_debuginfo('Account Validations Successful');
2876 END IF;
2877 END IF;
2878
2879 /* Bug :8244523
2880 * Negative bank id and branch Id's are getting updated to the table
2881 * iby_ext_bank_accounts.
2882 * Nulling out the bank Id and branch Id if they are negative.
2883 */
2884 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2885 print_debuginfo('BankID::'||p_ext_bank_acct_rec.bank_id);
2886 print_debuginfo('BranchID::'||p_ext_bank_acct_rec.branch_id);
2887 END IF;
2888 if(p_ext_bank_acct_rec.bank_id <0) then
2889 p_ext_bank_acct_rec.bank_id := null;
2890 end if;
2891 if(p_ext_bank_acct_rec.branch_id <0) then
2892 p_ext_bank_acct_rec.branch_id := null;
2893 end if;
2894
2895
2896 /* Bug - 9192335
2897 * Call Custom validations
2898 */
2899 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2900 print_debuginfo('Calling Custom Validations');
2901 END IF;
2902 IBY_ACCT_VAL_EXT_PUB.Validate_ext_bank_acct(p_ext_bank_acct_rec,l_ret_stat,l_error_msg);
2903 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2904 print_debuginfo('Return Status from Custom Validation::'||l_ret_stat);
2905 print_debuginfo('Error Message from Custom Validation::'||l_error_msg);
2906 END IF;
2907
2908 IF nvl(l_ret_stat,fnd_api.g_ret_sts_success) = fnd_api.g_ret_sts_error THEN
2909 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2910 print_debuginfo('Custom Validation Failed..');
2911 END IF;
2912 x_return_status := fnd_api.g_ret_sts_error;
2913 fnd_message.set_name('IBY', 'IBY_CUST_BANK_ACCT_VAL');
2914 fnd_message.set_Token('ERROR_MESSAGE',l_error_msg);
2915 fnd_msg_pub.add;
2916 RETURN;
2917 END IF;
2918
2919
2920 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2921 print_debuginfo('Before Update: using bank account');
2922
2923 END IF;
2924 -- Update Table IBY_EXT_BANK_ACCOUNTS
2925 UPDATE IBY_EXT_BANK_ACCOUNTS
2926 SET BANK_ACCOUNT_NUM = nvl(l_acct_number, BANK_ACCOUNT_NUM),
2927 COUNTRY_CODE=p_ext_bank_acct_rec.country_code,
2928 CURRENCY_CODE =p_ext_bank_acct_rec.currency,
2929 IBAN = DECODE(l_iban, FND_API.G_MISS_CHAR,NULL,l_iban),
2930 CHECK_DIGITS = p_ext_bank_acct_rec.check_digits,
2931 -- MULTI_CURRENCY_ALLOWED_FLAG = p_ext_bank_acct_rec.multi_currency_allowed_flag,
2932 BANK_ACCOUNT_TYPE =p_ext_bank_acct_rec.acct_type,
2933 ACCOUNT_SUFFIX = p_ext_bank_acct_rec.acct_suffix,
2934 AGENCY_LOCATION_CODE = p_ext_bank_acct_rec.agency_location_code,
2935 BANK_ID = p_ext_bank_acct_rec.bank_id,
2936 BRANCH_ID = p_ext_bank_acct_rec.branch_id,
2937 FOREIGN_PAYMENT_USE_FLAG=p_ext_bank_acct_rec.foreign_payment_use_flag ,
2938 PAYMENT_FACTOR_FLAG=p_ext_bank_acct_rec.payment_factor_flag,
2939 EXCHANGE_RATE_AGREEMENT_TYPE=p_ext_bank_acct_rec.exchange_rate_agreement_type,
2940 EXCHANGE_RATE_AGREEMENT_NUM=p_ext_bank_acct_rec.exchange_rate_agreement_num,
2941 EXCHANGE_RATE=p_ext_bank_acct_rec.exchange_rate,
2942 START_DATE=trunc(p_ext_bank_acct_rec.start_date),
2943 END_DATE=trunc(p_ext_bank_acct_rec.end_date),
2944 HEDGING_CONTRACT_REFERENCE= p_ext_bank_acct_rec.hedging_contract_reference,
2945 MASKED_BANK_ACCOUNT_NUM=nvl(l_masked_ba_num,MASKED_BANK_ACCOUNT_NUM),
2946 MASKED_IBAN=l_masked_iban,
2947 IBAN_HASH1=l_iban_hash1,
2948 IBAN_HASH2=l_iban_hash2,
2949 IBAN_SEC_SEGMENT_ID = l_iban_segment_id,
2950 BANK_ACCOUNT_NUM_HASH1=nvl(l_ba_num_hash1,BANK_ACCOUNT_NUM_HASH1),
2951 -- bug 7635964
2952 BANK_ACCOUNT_NUM_HASH2=nvl(l_ba_num_hash2,BANK_ACCOUNT_NUM_HASH2),
2953 ENCRYPTED = l_encrypted,
2954 BA_NUM_SEC_SEGMENT_ID = l_ba_segment_id,
2955 BANK_ACCOUNT_NUM_ELECTRONIC = nvl(l_bank_account_num_electronic,
2956 BANK_ACCOUNT_NUM_ELECTRONIC),
2957 BA_NUM_ELEC_SEC_SEGMENT_ID = l_ba_elec_segment_id, --Bug 14456128
2958 ATTRIBUTE_CATEGORY = p_ext_bank_acct_rec.attribute_category,
2959 ATTRIBUTE1 = p_ext_bank_acct_rec.attribute1,
2960 ATTRIBUTE2 = p_ext_bank_acct_rec.attribute2,
2961 ATTRIBUTE3 = p_ext_bank_acct_rec.attribute3,
2962 ATTRIBUTE4 = p_ext_bank_acct_rec.attribute4,
2963 ATTRIBUTE5 = p_ext_bank_acct_rec.attribute5,
2964 ATTRIBUTE6 = p_ext_bank_acct_rec.attribute6,
2965 ATTRIBUTE7 = p_ext_bank_acct_rec.attribute7,
2966 ATTRIBUTE8 = p_ext_bank_acct_rec.attribute8,
2967 ATTRIBUTE9 = p_ext_bank_acct_rec.attribute9,
2968 ATTRIBUTE10 = p_ext_bank_acct_rec.attribute10,
2969 ATTRIBUTE11 = p_ext_bank_acct_rec.attribute11,
2970 ATTRIBUTE12 = p_ext_bank_acct_rec.attribute12,
2971 ATTRIBUTE13 = p_ext_bank_acct_rec.attribute13,
2972 ATTRIBUTE14 = p_ext_bank_acct_rec.attribute14,
2973 ATTRIBUTE15 = p_ext_bank_acct_rec.attribute15,
2974 LAST_UPDATED_BY = fnd_global.user_id,
2975 LAST_UPDATE_DATE = sysdate,
2976 LAST_UPDATE_LOGIN = fnd_global.login_id,
2977 BANK_ACCOUNT_NAME = p_ext_bank_acct_rec.bank_account_name,
2978 BANK_ACCOUNT_NAME_ALT = p_ext_bank_acct_rec.alternate_acct_name,
2979 SHORT_ACCT_NAME = p_ext_bank_acct_rec.short_acct_name,
2980 DESCRIPTION =p_ext_bank_acct_rec.description,
2981 OBJECT_VERSION_NUMBER = p_ext_bank_acct_rec.object_version_number+1 ,
2982 SECONDARY_ACCOUNT_REFERENCE = p_ext_bank_acct_rec.secondary_account_reference, -- Bug 7408747
2983 CONTACT_NAME =p_ext_bank_acct_rec.contact_name,
2984 CONTACT_PHONE =p_ext_bank_acct_rec.contact_phone,
2985 CONTACT_EMAIL =p_ext_bank_acct_rec.contact_email,
2986 CONTACT_FAX =p_ext_bank_acct_rec.contact_fax
2987 WHERE EXT_BANK_ACCOUNT_ID = p_ext_bank_acct_rec.bank_account_id
2988 RETURNING OBJECT_VERSION_NUMBER INTO p_ext_bank_acct_rec.object_version_number;
2989
2990 -- End of API body
2991
2992 -- get message count and if count is 1, get message info.
2993 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2994 p_count => x_msg_count,
2995 p_data => x_msg_data);
2996
2997 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2998 print_debuginfo('RETURN ' || l_module_name);
2999
3000 END IF;
3001 EXCEPTION
3002 WHEN fnd_api.g_exc_error THEN
3003 ROLLBACK TO update_ext_bank_acct_pub;
3004 x_return_status := fnd_api.g_ret_sts_error;
3005 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3006 p_count => x_msg_count,
3007 p_data => x_msg_data);
3008
3009
3010 WHEN fnd_api.g_exc_unexpected_error THEN
3011 ROLLBACK TO update_ext_bank_acct_pub;
3012 x_return_status := fnd_api.g_ret_sts_unexp_error;
3013 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3014 p_count => x_msg_count,
3015 p_data => x_msg_data);
3016
3017
3018 WHEN OTHERS THEN
3019 ROLLBACK TO update_ext_bank_acct_pub;
3020 x_return_status := fnd_api.g_ret_sts_unexp_error;
3021 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
3022
3023 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3024 p_count => x_msg_count,
3025 p_data => x_msg_data);
3026
3027
3028 END update_ext_bank_acct;
3029
3030
3031
3032 -- 11. get_ext_bank_acct
3033 --
3034 -- API name : get_ext_bank_acct
3035 -- Type : Public
3036 -- Pre-reqs : None
3037 -- Function : Queries an external bank account
3038 -- Current version : 1.0
3039 -- Previous version: 1.0
3040 -- Initial version : 1.0
3041 --
3042 PROCEDURE get_ext_bank_acct (
3043 p_api_version IN NUMBER,
3044 p_init_msg_list IN VARCHAR2,
3045 p_bankacct_id IN NUMBER,
3046 x_return_status OUT NOCOPY VARCHAR2,
3047 x_msg_count OUT NOCOPY NUMBER,
3048 x_msg_data OUT NOCOPY VARCHAR2,
3049 x_bankacct OUT NOCOPY ExtBankAcct_rec_type,
3050 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
3051 ) IS
3052
3053 l_api_name CONSTANT VARCHAR2(30) := 'get_ext_bank_acct';
3054 l_api_version CONSTANT NUMBER := 1.0;
3055 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
3056
3057 CURSOR c_bank_account IS
3058 SELECT b.EXT_BANK_ACCOUNT_ID,
3059 b.COUNTRY_CODE,
3060 b.BRANCH_ID,
3061 b.BANK_ID,
3062 b.BANK_ACCOUNT_NUM,
3063 b.CURRENCY_CODE,
3064 b.IBAN,
3065 b.CHECK_DIGITS,
3066 b.BANK_ACCOUNT_TYPE,
3067 b.ACCOUNT_CLASSIFICATION,
3068 b.ACCOUNT_SUFFIX,
3069 b.AGENCY_LOCATION_CODE,
3070 -- b.MULTI_CURRENCY_ALLOWED_FLAG,
3071 b.PAYMENT_FACTOR_FLAG,
3072 b.FOREIGN_PAYMENT_USE_FLAG,
3073 b.EXCHANGE_RATE_AGREEMENT_NUM,
3074 b.EXCHANGE_RATE_AGREEMENT_TYPE,
3075 b.EXCHANGE_RATE,
3076 b.HEDGING_CONTRACT_REFERENCE,
3077 -- b.STATUS,
3078 b.ATTRIBUTE_CATEGORY,
3079 b.ATTRIBUTE1,
3080 b.ATTRIBUTE2,
3081 b.ATTRIBUTE3,
3082 b.ATTRIBUTE4,
3083 b.ATTRIBUTE5,
3084 b.ATTRIBUTE6,
3085 b.ATTRIBUTE7,
3086 b.ATTRIBUTE8,
3087 b.ATTRIBUTE9,
3088 b.ATTRIBUTE10,
3089 b.ATTRIBUTE11,
3090 b.ATTRIBUTE12,
3091 b.ATTRIBUTE13,
3092 b.ATTRIBUTE14,
3093 b.ATTRIBUTE15,
3094 b.REQUEST_ID,
3095 b.PROGRAM_APPLICATION_ID,
3096 b.PROGRAM_ID,
3097 b.PROGRAM_UPDATE_DATE,
3098 b.START_DATE,
3099 b.END_DATE,
3100 b.CREATED_BY,
3101 b.CREATION_DATE,
3102 b.LAST_UPDATED_BY,
3103 b.LAST_UPDATE_DATE,
3104 b.LAST_UPDATE_LOGIN,
3105 b.OBJECT_VERSION_NUMBER,
3106 null,
3107 b.BANK_ACCOUNT_NUM_HASH2,
3108 b.BANK_ACCOUNT_NUM_HASH1,
3109 b.MASKED_BANK_ACCOUNT_NUM,
3110 b.IBAN_HASH1,
3111 b.IBAN_HASH2,
3112 b.MASKED_IBAN,
3113 b.BA_MASK_SETTING,
3114 b.BA_UNMASK_LENGTH,
3115 b.ENCRYPTED,
3116 b.BANK_ACCOUNT_NAME,
3117 b.BANK_ACCOUNT_NAME_ALT,
3118 b.SHORT_ACCT_NAME,
3119 b.DESCRIPTION,
3120 b.SECONDARY_ACCOUNT_REFERENCE -- Bug 7408747
3121 FROM IBY_EXT_BANK_ACCOUNTS b
3122 WHERE b.EXT_BANK_ACCOUNT_ID = p_bankacct_id;
3123
3124 CURSOR c_acct_owner IS
3125 SELECT ACCOUNT_OWNER_PARTY_ID
3126 FROM IBY_ACCOUNT_OWNERS
3127 WHERE EXT_BANK_ACCOUNT_ID = p_bankacct_id
3128 AND PRIMARY_FLAG = 'Y';
3129
3130 l_bank_account_rec c_bank_account%ROWTYPE;
3131
3132 BEGIN
3133
3134 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3135 print_debuginfo('ENTER ' || l_module_name);
3136
3137 END IF;
3138 -- Standard call to check for call compatibility.
3139 IF NOT FND_API.Compatible_API_Call(l_api_version,
3140 p_api_version,
3141 l_api_name,
3142 G_PKG_NAME) THEN
3143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3144 END IF;
3145
3146 -- Initialize message list if p_init_msg_list is set to TRUE.
3147 IF FND_API.to_Boolean(p_init_msg_list) THEN
3148 FND_MSG_PUB.initialize;
3149 END IF;
3150
3151 -- Initialize API return status to success
3152 x_return_status := FND_API.G_RET_STS_SUCCESS;
3153
3154 -- Start of API body
3155
3156 check_mandatory('IBY_BANKACCT_ID_FIELD', p_bankacct_id);
3157
3158 -- Fext the Bank Account data
3159 OPEN c_bank_account;
3160 FETCH c_bank_account into l_bank_account_rec;
3161 CLOSE c_bank_account;
3162
3163 x_bankacct.bank_account_id := l_bank_account_rec.EXT_BANK_ACCOUNT_ID ;
3164 x_bankacct.country_code := l_bank_account_rec.COUNTRY_CODE;
3165 x_bankacct.branch_id := l_bank_account_rec.BRANCH_ID ;
3166 x_bankacct.bank_id := l_bank_account_rec.BANK_ID ;
3167 x_bankacct.bank_account_name := l_bank_account_rec.BANK_ACCOUNT_NAME ;
3168 x_bankacct.bank_account_num := l_bank_account_rec.MASKED_BANK_ACCOUNT_NUM;
3169 x_bankacct.currency := l_bank_account_rec.CURRENCY_CODE;
3170 x_bankacct.iban := l_bank_account_rec.MASKED_IBAN;
3171 x_bankacct.check_digits := l_bank_account_rec.CHECK_DIGITS ;
3172 x_bankacct.multi_currency_allowed_flag := null;
3173 --l_bank_account_rec.MULTI_CURRENCY_ALLOWED_FLAG ;
3174 x_bankacct.alternate_acct_name := l_bank_account_rec.BANK_ACCOUNT_NAME_ALT ;
3175 x_bankacct.short_acct_name := l_bank_account_rec.SHORT_ACCT_NAME ;
3176 x_bankacct.acct_type := l_bank_account_rec.BANK_ACCOUNT_TYPE;
3177 x_bankacct.acct_suffix := l_bank_account_rec.ACCOUNT_SUFFIX ;
3178 x_bankacct.description := l_bank_account_rec.DESCRIPTION ;
3179 x_bankacct.agency_location_code := l_bank_account_rec.AGENCY_LOCATION_CODE ;
3180 x_bankacct.foreign_payment_use_flag := l_bank_account_rec.FOREIGN_PAYMENT_USE_FLAG ;
3181 x_bankacct.exchange_rate_agreement_num := l_bank_account_rec.EXCHANGE_RATE_AGREEMENT_NUM ;
3182 x_bankacct.exchange_rate_agreement_type := l_bank_account_rec.EXCHANGE_RATE_AGREEMENT_TYPE ;
3183 x_bankacct.exchange_rate := l_bank_account_rec.EXCHANGE_RATE ;
3184 x_bankacct.payment_factor_flag := l_bank_account_rec.PAYMENT_FACTOR_FLAG ;
3185 x_bankacct.status := null;
3186 --l_bank_account_rec.STATUS ;
3187 x_bankacct.end_date := l_bank_account_rec.END_DATE ;
3188 x_bankacct.START_DATE := l_bank_account_rec.START_DATE ;
3189 x_bankacct.hedging_contract_reference := l_bank_account_rec.HEDGING_CONTRACT_REFERENCE ;
3190 x_bankacct.attribute_category := l_bank_account_rec.ATTRIBUTE_CATEGORY ;
3191 x_bankacct.attribute1 := l_bank_account_rec.ATTRIBUTE1 ;
3192 x_bankacct.attribute2 := l_bank_account_rec.ATTRIBUTE2 ;
3193 x_bankacct.attribute3 := l_bank_account_rec.ATTRIBUTE3 ;
3194 x_bankacct.attribute4 := l_bank_account_rec.ATTRIBUTE4 ;
3195 x_bankacct.attribute5 := l_bank_account_rec.ATTRIBUTE5 ;
3196 x_bankacct.attribute6 := l_bank_account_rec.ATTRIBUTE6 ;
3197 x_bankacct.attribute7 := l_bank_account_rec.ATTRIBUTE7 ;
3198 x_bankacct.attribute8 := l_bank_account_rec.ATTRIBUTE8 ;
3199 x_bankacct.attribute9 := l_bank_account_rec.ATTRIBUTE9 ;
3200 x_bankacct.attribute10 := l_bank_account_rec.ATTRIBUTE10 ;
3201 x_bankacct.attribute11 := l_bank_account_rec.ATTRIBUTE11 ;
3202 x_bankacct.attribute12 := l_bank_account_rec.ATTRIBUTE12 ;
3203 x_bankacct.attribute13 := l_bank_account_rec.ATTRIBUTE13 ;
3204 x_bankacct.attribute14 := l_bank_account_rec.ATTRIBUTE14 ;
3205 x_bankacct.attribute15 := l_bank_account_rec.ATTRIBUTE15 ;
3206 x_bankacct.object_version_number := l_bank_account_rec.OBJECT_VERSION_NUMBER ;
3207 x_bankacct.secondary_account_reference := l_bank_account_rec.SECONDARY_ACCOUNT_REFERENCE ; -- Bug 7408747
3208
3209 -- Fetch the Bank Account Owner data
3210 OPEN c_acct_owner;
3211 FETCH c_acct_owner into x_bankacct.acct_owner_party_id;
3212 CLOSE c_acct_owner;
3213
3214 -- End of API body
3215
3216 -- get message count and if count is 1, get message info.
3217 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3218 p_count => x_msg_count,
3219 p_data => x_msg_data);
3220
3221 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3222 print_debuginfo('RETURN ' || l_module_name);
3223 END IF;
3224 EXCEPTION
3225 WHEN fnd_api.g_exc_error THEN
3226 x_return_status := fnd_api.g_ret_sts_error;
3227 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3228 print_debuginfo('Exception : ' || SQLERRM);
3229 END IF;
3230 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3231 p_count => x_msg_count,
3232 p_data => x_msg_data);
3233
3234
3235 WHEN fnd_api.g_exc_unexpected_error THEN
3236 x_return_status := fnd_api.g_ret_sts_unexp_error;
3237 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3238 print_debuginfo('Exception : ' || SQLERRM);
3239 END IF;
3240 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3241 p_count => x_msg_count,
3242 p_data => x_msg_data);
3243
3244
3245 WHEN OTHERS THEN
3246 x_return_status := fnd_api.g_ret_sts_unexp_error;
3247 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3248 print_debuginfo('Exception : ' || SQLERRM);
3249 END IF;
3250 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
3251
3252 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3253 p_count => x_msg_count,
3254 p_data => x_msg_data);
3255
3256
3257 END get_ext_bank_acct;
3258
3259
3260 -- 12. get_ext_bank_acct
3261 --
3262 -- API name : get_ext_bank_acct
3263 -- Type : Public
3264 -- Pre-reqs : None
3265 -- Function : Queries an external bank account, decrypting secure
3266 -- fields
3267 -- Current version : 1.0
3268 -- Previous version: 1.0
3269 -- Initial version : 1.0
3270 --
3271 PROCEDURE get_ext_bank_acct (
3272 p_api_version IN NUMBER,
3273 p_init_msg_list IN VARCHAR2,
3274 p_bankacct_id IN NUMBER,
3275 p_sec_key IN VARCHAR2,
3276 x_return_status OUT NOCOPY VARCHAR2,
3277 x_msg_count OUT NOCOPY NUMBER,
3278 x_msg_data OUT NOCOPY VARCHAR2,
3279 x_bankacct OUT NOCOPY ExtBankAcct_rec_type,
3280 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
3281 )IS
3282
3283 l_api_name CONSTANT VARCHAR2(30) := 'get_ext_bank_acct';
3284 l_api_version CONSTANT NUMBER := 1.0;
3285 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
3286
3287 BEGIN
3288
3289 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3290 print_debuginfo('ENTER ' || l_module_name);
3291
3292 END IF;
3293 SAVEPOINT get_ext_bank_acct_pub;
3294
3295 -- Standard call to check for call compatibility.
3296 IF NOT FND_API.Compatible_API_Call(l_api_version,
3297 p_api_version,
3298 l_api_name,
3299 G_PKG_NAME) THEN
3300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3301 END IF;
3302
3303 -- Initialize message list if p_init_msg_list is set to TRUE.
3304 IF FND_API.to_Boolean(p_init_msg_list) THEN
3305 FND_MSG_PUB.initialize;
3306 END IF;
3307
3308 -- Initialize API return status to success
3309 x_return_status := FND_API.G_RET_STS_SUCCESS;
3310
3311 -- Start of API body
3312
3313 --
3314 -- Call the other get_ext_bank_acct for now
3315 -- TO DO: Use the security key
3316 get_ext_bank_acct (
3317 1.0,
3318 NULL,
3319 p_bankacct_id,
3320 x_return_status,
3321 x_msg_count,
3322 x_msg_data,
3323 x_bankacct,
3324 x_response
3325 );
3326
3327 -- End of API body
3328
3329 -- get message count and if count is 1, get message info.
3330 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3331 p_count => x_msg_count,
3332 p_data => x_msg_data);
3333
3334 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3335 print_debuginfo('RETURN ' || l_module_name);
3336 END IF;
3337 EXCEPTION
3338 WHEN fnd_api.g_exc_error THEN
3339 ROLLBACK TO get_ext_bank_acct_pub;
3340 x_return_status := fnd_api.g_ret_sts_error;
3341 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3342 print_debuginfo('Exception : ' || SQLERRM);
3343 END IF;
3344 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3345 p_count => x_msg_count,
3346 p_data => x_msg_data);
3347
3348
3349 WHEN fnd_api.g_exc_unexpected_error THEN
3350 ROLLBACK TO get_ext_bank_acct_pub;
3351 x_return_status := fnd_api.g_ret_sts_unexp_error;
3352 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3353 print_debuginfo('Exception : ' || SQLERRM);
3354 END IF;
3355 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3356 p_count => x_msg_count,
3357 p_data => x_msg_data);
3358
3359
3360 WHEN OTHERS THEN
3361 ROLLBACK TO get_ext_bank_acct_pub;
3362 x_return_status := fnd_api.g_ret_sts_unexp_error;
3363 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3364 print_debuginfo('Exception : ' || SQLERRM);
3365 END IF;
3366 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
3367
3368 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3369 p_count => x_msg_count,
3370 p_data => x_msg_data);
3371
3372
3373 END get_ext_bank_acct;
3374
3375
3376 -- 13. set_ext_bank_acct_dates
3377 --
3378 -- API name : set_ext_bank_acct_dates
3379 -- Type : Public
3380 -- Pre-reqs : None
3381 -- Function : Sets the bank account end dates
3382 -- Current version : 1.0
3383 -- Previous version: 1.0
3384 -- Initial version : 1.0
3385 --
3386 PROCEDURE set_ext_bank_acct_dates (
3387 p_api_version IN NUMBER,
3388 p_init_msg_list IN VARCHAR2,
3389 p_acct_id IN NUMBER,
3390 p_start_date IN DATE,
3391 p_end_date IN DATE,
3392 p_object_version_number IN OUT NOCOPY NUMBER,
3393 x_return_status OUT NOCOPY VARCHAR2,
3394 x_msg_count OUT NOCOPY NUMBER,
3395 x_msg_data OUT NOCOPY VARCHAR2,
3396 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
3397 ) IS
3398
3399 l_api_name CONSTANT VARCHAR2(30) := 'set_ext_bank_acct_dates';
3400 l_api_version CONSTANT NUMBER := 1.0;
3401 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
3402
3403 BEGIN
3404
3405 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3406 print_debuginfo('ENTER ' || l_module_name);
3407
3408 END IF;
3409 SAVEPOINT set_ext_bank_acct_dates_pub;
3410
3411 -- Standard call to check for call compatibility.
3412 IF NOT FND_API.Compatible_API_Call(l_api_version,
3413 p_api_version,
3414 l_api_name,
3415 G_PKG_NAME) THEN
3416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3417 END IF;
3418
3419 -- Initialize message list if p_init_msg_list is set to TRUE.
3420 IF FND_API.to_Boolean(p_init_msg_list) THEN
3421 FND_MSG_PUB.initialize;
3422 END IF;
3423
3424 -- Initialize API return status to success
3425 x_return_status := FND_API.G_RET_STS_SUCCESS;
3426
3427 -- Start of API body
3428
3429 -- Parameter validations
3430 check_mandatory('IBY_BANKACCT_ID_FIELD', p_acct_id);
3431 check_mandatory('IBY_START_DATE', p_start_date);
3432 -- End Date is optional
3433 --check_mandatory('End Date', p_end_date);
3434 check_mandatory('IBY_OBJ_VER_NUM', p_object_version_number);
3435
3436 IF (p_end_date IS NOT NULL AND p_start_date > p_end_date) THEN
3437 -- throw exception if start date
3438 -- exceeds end_date
3439 fnd_message.set_name('IBY', 'IBY_START_END_DATE_BAD');
3440 fnd_msg_pub.add;
3441 RAISE fnd_api.g_exc_error;
3442 END IF;
3443
3444 -- update bank account dates
3445 UPDATE IBY_EXT_BANK_ACCOUNTS
3446 SET START_DATE = p_start_date, END_DATE = NVL(p_end_date,END_DATE)
3447 WHERE EXT_BANK_ACCOUNT_ID = p_acct_id;
3448
3449 IF (SQL%NOTFOUND) THEN
3450 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3451 print_debuginfo('Warning: No matching Row found in IBY_EXT_BANK_ACCOUNTS');
3452 END IF;
3453 ELSE
3454 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3455 print_debuginfo('Set Ext Bank Account End Date as ' || p_end_date);
3456 END IF;
3457 END IF;
3458
3459 -- End of API body
3460
3461 -- get message count and if count is 1, get message info.
3462 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3463 p_count => x_msg_count,
3464 p_data => x_msg_data);
3465
3466 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3467 print_debuginfo('RETURN ' || l_module_name);
3468
3469 END IF;
3470 EXCEPTION
3471 WHEN fnd_api.g_exc_error THEN
3472 ROLLBACK TO set_ext_bank_acct_dates_pub;
3473 x_return_status := fnd_api.g_ret_sts_error;
3474 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3475 p_count => x_msg_count,
3476 p_data => x_msg_data);
3477
3478
3479 WHEN fnd_api.g_exc_unexpected_error THEN
3480 ROLLBACK TO set_ext_bank_acct_dates_pub;
3481 x_return_status := fnd_api.g_ret_sts_unexp_error;
3482 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3483 p_count => x_msg_count,
3484 p_data => x_msg_data);
3485
3486
3487 WHEN OTHERS THEN
3488 ROLLBACK TO set_ext_bank_acct_dates_pub;
3489 x_return_status := fnd_api.g_ret_sts_unexp_error;
3490 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
3491 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3492 p_count => x_msg_count,
3493 p_data => x_msg_data);
3494
3495
3496 END set_ext_bank_acct_dates;
3497
3498
3499 -- 14. check_ext_acct_exist
3500 --
3501 -- API name : check_ext_acct_exist
3502 -- Type : Public
3503 -- Pre-reqs : None
3504 -- Function : Checks if the external account exists; identity
3505 -- is determined by bank id, branch id, country and
3506 -- currency codes, bank account number and
3507 -- country specific attributes.
3508 -- Current version : 1.0
3509 -- Previous version: 1.0
3510 -- Initial version : 1.0
3511 --
3512 /* Modified to include country specific unique bank account
3513 validation. Bug:7501595
3514 gmaheswa removed mandatory branch id condition*/
3515 PROCEDURE check_ext_acct_exist(
3516 p_api_version IN NUMBER,
3517 p_init_msg_list IN VARCHAR2,
3518 p_ext_bank_acct_rec IN ExtBankAcct_rec_type,
3519 x_acct_id OUT NOCOPY NUMBER,
3520 x_start_date OUT NOCOPY DATE,
3521 x_end_date OUT NOCOPY DATE,
3522 x_return_status OUT NOCOPY VARCHAR2,
3523 x_msg_count OUT NOCOPY NUMBER,
3524 x_msg_data OUT NOCOPY VARCHAR2,
3525 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
3526 ) IS
3527
3528 l_ba_num_hash1 iby_ext_bank_accounts.bank_account_num_hash1%TYPE;
3529 l_ba_num_hash2 iby_ext_bank_accounts.bank_account_num_hash1%TYPE;
3530
3531 l_api_name CONSTANT VARCHAR2(30) := 'check_ext_acct_exist';
3532 l_api_version CONSTANT NUMBER := 1.0;
3533 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
3534
3535 -- Unique Check: the external bank already exists if
3536 -- the bank acount name matches or currency and bank
3537 -- account number matches.
3538
3539 -- For Readability different cursors are maintained for each type of
3540 -- validation
3541
3542 -- General Unique check
3543
3544 CURSOR uniq_check_generic ( p_account_num_hash1 VARCHAR2,
3545 p_account_num_hash2 VARCHAR2,
3546 p_currency VARCHAR2,
3547 p_bank_id NUMBER,
3548 p_branch_id NUMBER,
3549 p_country_code VARCHAR2,
3550 p_acct_id NUMBER) IS
3551
3552 SELECT EXT_BANK_ACCOUNT_ID,
3553 START_DATE,
3554 END_DATE
3555 FROM IBY_EXT_BANK_ACCOUNTS
3556 WHERE (BANK_ACCOUNT_NUM_HASH1= p_account_num_hash1)
3557 AND (BANK_ACCOUNT_NUM_HASH2= p_account_num_hash2)
3558 AND ((p_currency IS NULL and CURRENCY_CODE is NULL) OR (CURRENCY_CODE = p_currency))
3559 AND ((p_bank_id IS NULL AND BANK_ID is NULL) OR (BANK_ID = p_bank_id))
3560 AND ((p_branch_id IS NULL AND BRANCH_ID is NULL) OR (BRANCH_ID = p_branch_id))
3561 AND p_country_code=COUNTRY_CODE
3562 AND ((p_acct_id IS NULL) OR (EXT_BANK_ACCOUNT_ID <> p_acct_id));
3563
3564 -- Country specific unique check cursor
3565 -- where account type has to be considered
3566 CURSOR uniq_check_acctType ( p_account_num_hash1 VARCHAR2,
3567 p_account_num_hash2 VARCHAR2,
3568 p_currency VARCHAR2,
3569 p_bank_id NUMBER,
3570 p_branch_id NUMBER,
3571 p_country_code VARCHAR2,
3572 p_acct_type varchar2,
3573 p_acct_id NUMBER) IS
3574
3575 SELECT EXT_BANK_ACCOUNT_ID,
3576 START_DATE,
3577 END_DATE
3578 FROM IBY_EXT_BANK_ACCOUNTS
3579 WHERE (BANK_ACCOUNT_NUM_HASH1= p_account_num_hash1)
3580 AND (BANK_ACCOUNT_NUM_HASH2= p_account_num_hash2)
3581 AND ((p_currency IS NULL and CURRENCY_CODE is NULL) OR (CURRENCY_CODE = p_currency))
3582 AND ((p_bank_id IS NULL AND BANK_ID is NULL) OR (BANK_ID = p_bank_id))
3583 AND ((p_branch_id IS NULL AND BRANCH_ID is NULL) OR (BRANCH_ID = p_branch_id))
3584 AND p_country_code=COUNTRY_CODE
3585 AND ((p_acct_id IS NULL) OR (EXT_BANK_ACCOUNT_ID <> p_acct_id))
3586 AND ((p_acct_type is NULL and BANK_ACCOUNT_TYPE is NULL)OR (p_acct_type=BANK_ACCOUNT_TYPE));
3587
3588 -- Country specific unique check cursor
3589 -- where account suffix is to be considered
3590 CURSOR uniq_check_acctSuffix ( p_account_num_hash1 VARCHAR2,
3591 p_account_num_hash2 VARCHAR2,
3592 p_currency VARCHAR2,
3593 p_bank_id NUMBER,
3594 p_branch_id NUMBER,
3595 p_country_code VARCHAR2,
3596 p_acct_suffix varchar2,
3597 p_acct_id NUMBER) IS
3598
3599 SELECT EXT_BANK_ACCOUNT_ID,
3600 START_DATE,
3601 END_DATE
3602 FROM IBY_EXT_BANK_ACCOUNTS
3603 WHERE (BANK_ACCOUNT_NUM_HASH1= p_account_num_hash1)
3604 AND (BANK_ACCOUNT_NUM_HASH2= p_account_num_hash2)
3605 AND ((p_currency IS NULL and CURRENCY_CODE is NULL) OR (CURRENCY_CODE = p_currency))
3606 AND ((p_bank_id IS NULL AND BANK_ID is NULL) OR (BANK_ID = p_bank_id))
3607 AND ((p_branch_id IS NULL AND BRANCH_ID is NULL) OR (BRANCH_ID = p_branch_id))
3608 AND p_country_code=COUNTRY_CODE
3609 AND ((p_acct_id IS NULL) OR (EXT_BANK_ACCOUNT_ID <> p_acct_id))
3610 AND ((p_acct_suffix is NULL and ACCOUNT_SUFFIX is NULL)OR (p_acct_suffix=ACCOUNT_SUFFIX));
3611
3612 BEGIN
3613
3614 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3615 print_debuginfo('ENTER ' || l_module_name);
3616 print_debuginfo('bank_id:' || p_ext_bank_acct_rec.bank_id);
3617 print_debuginfo('branch_id:'|| p_ext_bank_acct_rec.branch_id);
3618 print_debuginfo('country_code:'|| p_ext_bank_acct_rec.country_code);
3619 print_debuginfo('currency:'|| p_ext_bank_acct_rec.currency);
3620 print_debuginfo('account_type:'|| p_ext_bank_acct_rec.acct_type);
3621 print_debuginfo('account_suffix:'|| p_ext_bank_acct_rec.acct_suffix);
3622 print_debuginfo('account_number:'|| p_ext_bank_acct_rec.bank_account_num);
3623 print_debuginfo('account_name:'|| p_ext_bank_acct_rec.bank_account_name);
3624 print_debuginfo('external_bank_account_id:'|| p_ext_bank_acct_rec.bank_account_id);
3625 print_debuginfo('bank_account_num_hash1:'|| p_ext_bank_acct_rec.bank_account_num_hash1);
3626 print_debuginfo('bank_account_num_hash2:'|| p_ext_bank_acct_rec.bank_account_num_hash2);
3627
3628
3629 END IF;
3630 -- Standard call to check for call compatibility.
3631 IF NOT FND_API.Compatible_API_Call(l_api_version,
3632 p_api_version,
3633 l_api_name,
3634 G_PKG_NAME) THEN
3635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3636 END IF;
3637
3638 -- Initialize message list if p_init_msg_list is set to TRUE.
3639 IF FND_API.to_Boolean(p_init_msg_list) THEN
3640 FND_MSG_PUB.initialize;
3641 END IF;
3642
3643 -- Initialize API return status to success
3644 x_return_status := FND_API.G_RET_STS_SUCCESS;
3645
3646 -- Parameter validations
3647
3648 IF (p_ext_bank_acct_rec.bank_account_name IS NULL) THEN
3649 check_mandatory('IBY_BANKACCT_NUM_FIELD',p_ext_bank_acct_rec.bank_account_num);
3650 END IF;
3651
3652 IF (p_ext_bank_acct_rec.bank_account_num_hash1 IS NULL AND p_ext_bank_acct_rec.bank_account_num_hash2 IS NULL) THEN
3653 l_ba_num_hash1 := iby_security_pkg.get_hash(p_ext_bank_acct_rec.bank_account_num,'F');
3654 l_ba_num_hash2 := iby_security_pkg.get_hash(p_ext_bank_acct_rec.bank_account_num,'T');
3655 ELSE
3656 l_ba_num_hash1 := p_ext_bank_acct_rec.bank_account_num_hash1;
3657 l_ba_num_hash2 := p_ext_bank_acct_rec.bank_account_num_hash2;
3658 END IF;
3659
3660 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3661 print_debuginfo('l_ba_num_hash1:' || l_ba_num_hash1);
3662 print_debuginfo('l_ba_num_hash2:' || l_ba_num_hash2);
3663 END IF;
3664
3665 -- Check if bank account exists
3666 -- For Japan, account type has to be considered for Unique check
3667 -- Bug No: 7501595
3668 IF (p_ext_bank_acct_rec.country_code = 'JP') THEN
3669 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3670 print_debuginfo('Inside country_code - JP');
3671 END IF;
3672 OPEN uniq_check_acctType(l_ba_num_hash1, l_ba_num_hash2, p_ext_bank_acct_rec.currency, p_ext_bank_acct_rec.bank_id,
3673 p_ext_bank_acct_rec.branch_id, p_ext_bank_acct_rec.country_code, p_ext_bank_acct_rec.acct_type,
3674 p_ext_bank_acct_rec.bank_account_id);
3675 FETCH uniq_check_acctType INTO x_acct_id,x_start_date,x_end_date;
3676 CLOSE uniq_check_acctType;
3677
3678 -- Check if bank account exists
3679 -- For Japan, account suffix has to be considered for Unique check
3680 -- Bug No: 7632304
3681 ELSIF (p_ext_bank_acct_rec.country_code = 'NZ') THEN
3682 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3683 print_debuginfo('Inside country_code - NZ');
3684 END IF;
3685 OPEN uniq_check_acctSuffix(l_ba_num_hash1, l_ba_num_hash2, p_ext_bank_acct_rec.currency, p_ext_bank_acct_rec.bank_id,
3686 p_ext_bank_acct_rec.branch_id, p_ext_bank_acct_rec.country_code, p_ext_bank_acct_rec.acct_suffix,
3687 p_ext_bank_acct_rec.bank_account_id);
3688 FETCH uniq_check_acctSuffix INTO x_acct_id,x_start_date,x_end_date;
3689 CLOSE uniq_check_acctSuffix;
3690 ELSE
3691 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3692 print_debuginfo('Inside generic check');
3693 END IF;
3694 OPEN uniq_check_generic(l_ba_num_hash1, l_ba_num_hash2, p_ext_bank_acct_rec.currency, p_ext_bank_acct_rec.bank_id,
3695 p_ext_bank_acct_rec.branch_id, p_ext_bank_acct_rec.country_code,
3696 p_ext_bank_acct_rec.bank_account_id);
3697 FETCH uniq_check_generic INTO x_acct_id,x_start_date,x_end_date;
3698 CLOSE uniq_check_generic;
3699 END IF;
3700
3701 IF (SQL%NOTFOUND) THEN
3702 -- fnd_message.set_name('IBY', 'IBY_EXT_ACCT_NOT_EXIST');
3703 -- fnd_msg_pub.add;
3704 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3705 print_debuginfo('External Account does not exist ');
3706 END IF;
3707 END IF;
3708
3709 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3710 print_debuginfo('x_acct_id:' || x_acct_id);
3711 print_debuginfo('x_start_date:' || x_start_date);
3712 print_debuginfo('x_end_date:' || x_end_date);
3713 END IF;
3714
3715 -- get message count and if count is 1, get message info.
3716 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3717 p_count => x_msg_count,
3718 p_data => x_msg_data);
3719
3720 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3721 print_debuginfo('RETURN ' || l_module_name);
3722
3723 END IF;
3724 EXCEPTION
3725 WHEN fnd_api.g_exc_error THEN
3726 x_return_status := fnd_api.g_ret_sts_error;
3727 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3728 print_debuginfo('Exception : ' || SQLERRM);
3729 END IF;
3730 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3731 p_count => x_msg_count,
3732 p_data => x_msg_data);
3733
3734
3735 WHEN fnd_api.g_exc_unexpected_error THEN
3736 x_return_status := fnd_api.g_ret_sts_unexp_error;
3737 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3738 print_debuginfo('Exception : ' || SQLERRM);
3739 END IF;
3740 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3741 p_count => x_msg_count,
3742 p_data => x_msg_data);
3743
3744
3745 WHEN OTHERS THEN
3746 x_return_status := fnd_api.g_ret_sts_unexp_error;
3747 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3748 print_debuginfo('Exception : ' || SQLERRM);
3749 END IF;
3750 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
3751
3752 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3753 p_count => x_msg_count,
3754 p_data => x_msg_data);
3755
3756
3757 END check_ext_acct_exist;
3758
3759
3760 -- 14. check_ext_acct_exist
3761 --
3762 -- API name : check_ext_acct_exist
3763 -- Type : Public
3764 -- Pre-reqs : None
3765 -- Function : Checks if the external account exists; identity
3766 -- is determined by bank id, branch id, country and
3767 -- currency codes
3768 --
3769 --
3770 -- Input parameters for the procedure is modified to facilitate
3771 -- country specific unique bank account validation. But this procedure
3772 -- is used by many external products. To ensure that no other code breaks
3773 -- due to this change, the original procedure is maintained as overloaded
3774 -- procedure. This procedure should be removed as soon as all the external
3775 -- products update their code to use the updated procedure.
3776 --
3777 -- Current version : 1.0
3778 -- Previous version: 1.0
3779 -- Initial version : 1.0
3780 --
3781 PROCEDURE check_ext_acct_exist(
3782 p_api_version IN NUMBER,
3783 p_init_msg_list IN VARCHAR2,
3784 p_bank_id IN varchar2,
3785 p_branch_id IN NUMBER,
3786 p_acct_number IN VARCHAR2,
3787 p_acct_name IN VARCHAR2,
3788 p_currency IN VARCHAR2,
3789 p_country_code IN VARCHAR2,
3790 x_acct_id OUT NOCOPY NUMBER,
3791 x_start_date OUT NOCOPY DATE,
3792 x_end_date OUT NOCOPY DATE,
3793 x_return_status OUT NOCOPY VARCHAR2,
3794 x_msg_count OUT NOCOPY NUMBER,
3795 x_msg_data OUT NOCOPY VARCHAR2,
3796 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
3797 ) IS
3798 l_api_name CONSTANT VARCHAR2(50) := 'check_ext_acct_exist (OVERLOADED)';
3799 l_api_version CONSTANT NUMBER := 1.0;
3800 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
3801 l_ext_bank_acct_rec ExtBankAcct_rec_type;
3802 BEGIN
3803 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3804 print_debuginfo('ENTER ' || l_module_name);
3805 END IF;
3806 l_ext_bank_acct_rec.bank_id := p_bank_id;
3807 l_ext_bank_acct_rec.branch_id := p_branch_id;
3808 l_ext_bank_acct_rec.bank_account_num := p_acct_number;
3809 l_ext_bank_acct_rec.bank_account_name := p_acct_name;
3810 l_ext_bank_acct_rec.currency := p_currency;
3811 l_ext_bank_acct_rec.country_code := p_country_code;
3812
3813 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3814 print_debuginfo('Before calling the base procedure ' || l_module_name);
3815
3816 END IF;
3817 check_ext_acct_exist(
3818 p_api_version,
3819 p_init_msg_list,
3820 l_ext_bank_acct_rec,
3821 x_acct_id,
3822 x_start_date,
3823 x_end_date,
3824 x_return_status,
3825 x_msg_count,
3826 x_msg_data,
3827 x_response
3828 );
3829
3830 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3831 print_debuginfo('EXIT ' || l_module_name);
3832
3833 END IF;
3834 END;
3835
3836 -- 15. create_intermediary_acct
3837 --
3838 -- API name : create_intermediary_acct
3839 -- Type : Public
3840 -- Pre-reqs : None
3841 -- Function : Creates an intermediary bank account
3842 -- Current version : 1.0
3843 -- Previous version: 1.0
3844 -- Initial version : 1.0
3845 --
3846 PROCEDURE create_intermediary_acct (
3847 p_api_version IN NUMBER,
3848 p_init_msg_list IN VARCHAR2,
3849 p_intermed_acct_rec IN IntermediaryAcct_rec_type,
3850 x_intermediary_acct_id OUT NOCOPY NUMBER,
3851 x_return_status OUT NOCOPY VARCHAR2,
3852 x_msg_count OUT NOCOPY NUMBER,
3853 x_msg_data OUT NOCOPY VARCHAR2,
3854 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
3855 ) IS
3856
3857 l_api_name CONSTANT VARCHAR2(30) := 'create_intermediary_acct';
3858 l_api_version CONSTANT NUMBER := 1.0;
3859 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
3860
3861
3862 BEGIN
3863 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3864 print_debuginfo('ENTER ' || l_module_name);
3865
3866 END IF;
3867 SAVEPOINT create_intermediary_acct_pub;
3868
3869 -- Standard call to check for call compatibility.
3870 IF NOT FND_API.Compatible_API_Call(l_api_version,
3871 p_api_version,
3872 l_api_name,
3873 G_PKG_NAME) THEN
3874 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3875 END IF;
3876
3877 -- Initialize message list if p_init_msg_list is set to TRUE.
3878 IF FND_API.to_Boolean(p_init_msg_list) THEN
3879 FND_MSG_PUB.initialize;
3880 END IF;
3881
3882 -- Initialize API return status to success
3883 x_return_status := FND_API.G_RET_STS_SUCCESS;
3884
3885 -- Parameter validations
3886 check_mandatory('IBY_BANKACCT_ID_FIELD', p_intermed_acct_rec.bank_account_id);
3887
3888 -- insert into IBY_INTERMEDIATE_ACCTS
3889
3890 INSERT INTO IBY_INTERMEDIARY_ACCTS(
3891 INTERMEDIARY_ACCT_ID,
3892 LAST_UPDATE_DATE,
3893 LAST_UPDATED_BY,
3894 LAST_UPDATE_LOGIN,
3895 CREATION_DATE,
3896 CREATED_BY,
3897 BANK_ACCT_ID,
3898 INTERNAL_BANK_ACCOUNT_ID,
3899 COUNTRY_CODE,
3900 BANK_NAME,
3901 CITY,
3902 BANK_CODE,
3903 BRANCH_NUMBER,
3904 BIC,
3905 ACCOUNT_NUMBER,
3906 CHECK_DIGITS,
3907 IBAN,
3908 COMMENTS,
3909 OBJECT_VERSION_NUMBER)
3910 VALUES (
3911 IBY_INTERMEDIARY_ACCTS_S.nextval,
3912 sysdate,
3913 NVL(FND_GLOBAL.user_id,-1),
3914 NVL(FND_GLOBAL.login_id, -1),
3915 sysdate,
3916 NVL(FND_GLOBAL.user_id,-1),
3917 p_intermed_acct_rec.bank_account_id,
3918 -99,
3919 p_intermed_acct_rec.country_code,
3920 p_intermed_acct_rec.bank_name,
3921 p_intermed_acct_rec.city,
3922 p_intermed_acct_rec.bank_code,
3923 p_intermed_acct_rec.branch_number,
3924 p_intermed_acct_rec.bic,
3925 p_intermed_acct_rec.account_number,
3926 p_intermed_acct_rec.check_digits,
3927 p_intermed_acct_rec.iban,
3928 p_intermed_acct_rec.comments,
3929 1)
3930 RETURNING INTERMEDIARY_ACCT_ID INTO x_intermediary_acct_id;
3931
3932
3933 -- get message count and if count is 1, get message info.
3934 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3935 p_count => x_msg_count,
3936 p_data => x_msg_data);
3937
3938 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3939 print_debuginfo('RETURN ' || l_module_name);
3940
3941
3942 END IF;
3943 EXCEPTION
3944 WHEN fnd_api.g_exc_error THEN
3945 ROLLBACK TO create_intermediary_acct_pub;
3946 x_return_status := fnd_api.g_ret_sts_error;
3947 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3948 p_count => x_msg_count,
3949 p_data => x_msg_data);
3950
3951
3952 WHEN fnd_api.g_exc_unexpected_error THEN
3953 ROLLBACK TO create_intermediary_acct_pub;
3954 x_return_status := fnd_api.g_ret_sts_unexp_error;
3955 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3956 p_count => x_msg_count,
3957 p_data => x_msg_data);
3958
3959
3960 WHEN OTHERS THEN
3961 ROLLBACK TO create_intermediary_acct_pub;
3962 x_return_status := fnd_api.g_ret_sts_unexp_error;
3963 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3964 print_debuginfo('Exception : ' || SQLERRM);
3965 END IF;
3966 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
3967
3968 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3969 p_count => x_msg_count,
3970 p_data => x_msg_data);
3971
3972
3973 END create_intermediary_acct;
3974
3975
3976
3977 -- 16. Update_Intermediary_Acct
3978 --
3979 -- API name : Update_Intermediary_Acct
3980 -- Type : Public
3981 -- Pre-reqs : None
3982 -- Function : Updates an intermediary bank account
3983 -- Current version : 1.0
3984 -- Previous version: 1.0
3985 -- Initial version : 1.0
3986 --
3987 PROCEDURE update_intermediary_acct (
3988 p_api_version IN NUMBER,
3989 p_init_msg_list IN VARCHAR2,
3990 p_intermed_acct_rec IN OUT NOCOPY IntermediaryAcct_rec_type,
3991 x_return_status OUT NOCOPY VARCHAR2,
3992 x_msg_count OUT NOCOPY NUMBER,
3993 x_msg_data OUT NOCOPY VARCHAR2,
3994 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
3995 ) IS
3996
3997 l_api_name CONSTANT VARCHAR2(30) := 'create_bank';
3998 l_api_version CONSTANT NUMBER := 1.0;
3999 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
4000
4001 -- Get Object Version Number
4002 CURSOR c_ovn IS
4003 SELECT object_version_number
4004 FROM IBY_INTERMEDIARY_ACCTS
4005 WHERE intermediary_acct_id = p_intermed_acct_rec.intermediary_acct_id;
4006
4007 l_old_ovn NUMBER(15);
4008
4009 BEGIN
4010
4011 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4012 print_debuginfo('ENTER ' || l_module_name);
4013
4014 END IF;
4015 SAVEPOINT update_intermediary_acct;
4016
4017 -- Standard call to check for call compatibility.
4018 IF NOT FND_API.Compatible_API_Call(l_api_version,
4019 p_api_version,
4020 l_api_name,
4021 G_PKG_NAME) THEN
4022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4023 END IF;
4024
4025 -- Initialize message list if p_init_msg_list is set to TRUE.
4026 IF FND_API.to_Boolean(p_init_msg_list) THEN
4027 FND_MSG_PUB.initialize;
4028 END IF;
4029
4030 -- Initialize API return status to success
4031 x_return_status := FND_API.G_RET_STS_SUCCESS;
4032
4033 --START of API
4034
4035 -- Parameter validations
4036 check_mandatory('IBY_INTERMEDIARYACCT_ID_FIELD', p_intermed_acct_rec.intermediary_acct_id);
4037 check_mandatory('IBY_OBJ_VER_NUM', p_intermed_acct_rec.object_version_number);
4038
4039 -- check object version number to make sure the record has not been updated
4040 OPEN c_ovn;
4041 FETCH c_ovn INTO l_old_ovn;
4042 IF c_ovn%NOTFOUND THEN
4043 fnd_message.set_name('IBY', 'IBY_API_NO_INTERMEDIARY_ACCT');
4044 fnd_msg_pub.add;
4045 x_return_status := fnd_api.g_ret_sts_error;
4046 CLOSE c_ovn;
4047 RAISE fnd_api.g_exc_error;
4048 END IF;
4049 CLOSE c_ovn;
4050
4051 IF l_old_ovn > p_intermed_acct_rec.object_version_number THEN
4052 fnd_message.set_name('IBY', 'IBY_DATA_VERSION_ERROR');
4053 fnd_msg_pub.add;
4054 x_return_status := fnd_api.g_ret_sts_error;
4055 RAISE fnd_api.g_exc_error;
4056 END IF;
4057
4058 -- update the table IBY_INTERMEDIARY_ACCOUNTS
4059 UPDATE IBY_INTERMEDIARY_ACCTS
4060 SET country_code = p_intermed_acct_rec.country_code,
4061 bank_name = p_intermed_acct_rec.bank_name,
4062 city = p_intermed_acct_rec.city,
4063 bank_code = p_intermed_acct_rec.bank_code,
4064 branch_number = p_intermed_acct_rec.branch_number,
4065 bic = p_intermed_acct_rec.bic,
4066 account_number = p_intermed_acct_rec.account_number,
4067 check_digits = p_intermed_acct_rec.check_digits,
4068 iban = p_intermed_acct_rec.iban,
4069 comments = p_intermed_acct_rec.comments,
4070 last_update_date = sysdate,
4071 last_update_login = NVL(FND_GLOBAL.login_id,-1),
4072 last_updated_by = NVL(FND_GLOBAL.user_id,-1),
4073 object_version_number = l_old_ovn + 1
4074 WHERE intermediary_acct_id = p_intermed_acct_rec.intermediary_acct_id
4075 RETURNING object_version_number INTO p_intermed_acct_rec.object_version_number;
4076
4077 IF (SQL%NOTFOUND) THEN
4078 fnd_message.set_name('IBY', 'IBY_API_NO_INTERMEDIARY_ACCT');
4079 fnd_msg_pub.add;
4080 x_return_status := fnd_api.g_ret_sts_error;
4081 RAISE fnd_api.g_exc_error;
4082 END IF;
4083
4084 -- END of API
4085
4086 -- get message count and if count is 1, get message info.
4087 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4088 p_count => x_msg_count,
4089 p_data => x_msg_data);
4090
4091 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4092 print_debuginfo('RETURN ' || l_module_name);
4093
4094 END IF;
4095 EXCEPTION
4096 WHEN fnd_api.g_exc_error THEN
4097 ROLLBACK TO update_intermediary_bank_acct;
4098 x_return_status := fnd_api.g_ret_sts_error;
4099 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4100 p_count => x_msg_count,
4101 p_data => x_msg_data);
4102
4103
4104 WHEN fnd_api.g_exc_unexpected_error THEN
4105 ROLLBACK TO update_intermediary_bank_acct;
4106 x_return_status := fnd_api.g_ret_sts_unexp_error;
4107 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4108 p_count => x_msg_count,
4109 p_data => x_msg_data);
4110
4111
4112 WHEN OTHERS THEN
4113 ROLLBACK TO update_intermediary_bank_acct;
4114 x_return_status := fnd_api.g_ret_sts_unexp_error;
4115 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
4116
4117 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4118 p_count => x_msg_count,
4119 p_data => x_msg_data);
4120
4121 END update_intermediary_acct;
4122
4123
4124 -- 17. add_joint_account_owner
4125 --
4126 -- API name : add_joint_account_owner
4127 -- Type : Public
4128 -- Pre-reqs : None
4129 -- Function : Associates another owner with a bank account
4130 -- Current version : 1.0
4131 -- Previous version: 1.0
4132 -- Initial version : 1.0
4133 --
4134 PROCEDURE add_joint_account_owner (
4135 p_api_version IN NUMBER,
4136 p_init_msg_list IN VARCHAR2,
4137 p_bank_account_id IN NUMBER,
4138 p_acct_owner_party_id IN NUMBER,
4139 x_joint_acct_owner_id OUT NOCOPY NUMBER,
4140 x_return_status OUT NOCOPY VARCHAR2,
4141 x_msg_count OUT NOCOPY NUMBER,
4142 x_msg_data OUT NOCOPY VARCHAR2,
4143 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
4144 ) IS
4145
4146 l_api_name CONSTANT VARCHAR2(30) := 'add_joint_account_owner';
4147 l_api_version CONSTANT NUMBER := 1.0;
4148 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
4149
4150 l_count NUMBER;
4151 l_primary_flag VARCHAR2(1) := 'N';
4152
4153 -- Unique Check: same party cannot be assigned the same bank account
4154 -- more than once.
4155 CURSOR uniq_check(p_party_id NUMBER, bank_account_id NUMBER)
4156 IS
4157 SELECT COUNT(*)
4158 FROM IBY_ACCOUNT_OWNERS
4159 WHERE ACCOUNT_OWNER_PARTY_ID = p_party_id
4160 AND EXT_BANK_ACCOUNT_ID = bank_account_id;
4161
4162 -- No of Active account owners
4163 CURSOR active_owners(bank_account_id NUMBER)
4164 IS
4165 SELECT COUNT(*)
4166 FROM IBY_ACCOUNT_OWNERS
4167 WHERE EXT_BANK_ACCOUNT_ID = bank_account_id;
4168 -- AND (end_date IS NULL OR end_date > sysdate);
4169
4170 BEGIN
4171
4172 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4173 print_debuginfo('ENTER ' || l_module_name);
4174
4175 END IF;
4176 SAVEPOINT add_joint_account_owner;
4177
4178 -- Standard call to check for call compatibility.
4179 IF NOT FND_API.Compatible_API_Call(l_api_version,
4180 p_api_version,
4181 l_api_name,
4182 G_PKG_NAME) THEN
4183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4184 END IF;
4185
4186 -- Initialize message list if p_init_msg_list is set to TRUE.
4187 IF FND_API.to_Boolean(p_init_msg_list) THEN
4188 FND_MSG_PUB.initialize;
4189 END IF;
4190
4191 -- Initialize API return status to success
4192 x_return_status := FND_API.G_RET_STS_SUCCESS;
4193
4194 -- Parameter validations
4195 check_mandatory('IBY_BANKACCT_ID_FIELD',p_bank_account_id);
4196 check_mandatory('IBY_ACCTOWNER_PARTYID_FIELD',p_acct_owner_party_id);
4197
4198 -- Check for uniqueness
4199 OPEN uniq_check(p_acct_owner_party_id, p_bank_account_id);
4200 FETCH uniq_check INTO l_count;
4201 CLOSE uniq_check;
4202
4203 IF (l_count > 0) THEN
4204 fnd_message.set_name('IBY', 'IBY_ACCT_OWNER_EXISTS');
4205 fnd_msg_pub.add;
4206 x_return_status := fnd_api.g_ret_sts_error;
4207 RETURN;
4208 END IF;
4209
4210 -- Check the no. of active owners
4211 OPEN active_owners(p_bank_account_id);
4212 FETCH active_owners INTO l_count;
4213 CLOSE active_owners;
4214
4215 -- Mark the owner as primary if it was
4216 -- an orphan account.
4217 IF (l_count <= 0) THEN
4218 l_primary_flag := 'Y';
4219 END IF;
4220
4221 -- Insert Row in IBY_ACCOUNT_OWNERS
4222 INSERT INTO IBY_ACCOUNT_OWNERS
4223 (
4224 ACCOUNT_OWNER_ID,
4225 EXT_BANK_ACCOUNT_ID,
4226 ACCOUNT_OWNER_PARTY_ID,
4227 END_DATE,
4228 PRIMARY_FLAG,
4229 CREATED_BY,
4230 CREATION_DATE,
4231 LAST_UPDATED_BY,
4232 LAST_UPDATE_DATE,
4233 LAST_UPDATE_LOGIN,
4234 OBJECT_VERSION_NUMBER
4235 )
4236 VALUES
4237 (
4238 IBY_ACCOUNT_OWNERS_S.NEXTVAL,
4239 p_bank_account_id,
4240 p_acct_owner_party_id,
4241 NULL,
4242 l_primary_flag,
4243 fnd_global.user_id,
4244 sysdate,
4245 fnd_global.user_id,
4246 sysdate,
4247 fnd_global.user_id,
4248 1.0
4249 ) RETURNING ACCOUNT_OWNER_ID INTO x_joint_acct_owner_id;
4250
4251 -- get message count and if count is 1, get message info.
4252 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4253 p_count => x_msg_count,
4254 p_data => x_msg_data);
4255
4256 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4257 print_debuginfo('RETURN ' || l_module_name);
4258
4259 END IF;
4260 EXCEPTION
4261 WHEN fnd_api.g_exc_error THEN
4262 ROLLBACK TO add_joint_account_owner;
4263 x_return_status := fnd_api.g_ret_sts_error;
4264 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4265 print_debuginfo('Exception : ' || SQLERRM);
4266 END IF;
4267 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4268 p_count => x_msg_count,
4269 p_data => x_msg_data);
4270
4271
4272 WHEN fnd_api.g_exc_unexpected_error THEN
4273 ROLLBACK TO add_joint_account_owner;
4274 x_return_status := fnd_api.g_ret_sts_unexp_error;
4275 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4276 print_debuginfo('Exception : ' || SQLERRM);
4277 END IF;
4278 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4279 p_count => x_msg_count,
4280 p_data => x_msg_data);
4281
4282
4283 WHEN OTHERS THEN
4284 ROLLBACK TO add_joint_account_owner;
4285 x_return_status := fnd_api.g_ret_sts_unexp_error;
4286 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4287 print_debuginfo('Exception : ' || SQLERRM);
4288 END IF;
4289 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
4290
4291 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4292 p_count => x_msg_count,
4293 p_data => x_msg_data);
4294
4295 END add_joint_account_owner;
4296
4297
4298 -- 18. set_joint_acct_owner_end_date
4299 --
4300 -- API name : set_joint_acct_owner_end_date
4301 -- Type : Public
4302 -- Pre-reqs : None
4303 -- Function : Sets the end data for a joint account owner
4304 -- Current version : 1.0
4305 -- Previous version: 1.0
4306 -- Initial version : 1.0
4307 --
4308 PROCEDURE set_joint_acct_owner_end_date (
4309 p_api_version IN NUMBER,
4310 p_init_msg_list IN VARCHAR2,
4311 p_acct_owner_id IN NUMBER,
4312 p_end_date IN DATE,
4313 p_object_version_number IN OUT NOCOPY NUMBER,
4314 x_return_status OUT NOCOPY VARCHAR2,
4315 x_msg_count OUT NOCOPY NUMBER,
4316 x_msg_data OUT NOCOPY VARCHAR2,
4317 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
4318 ) IS
4319
4320 l_api_name CONSTANT VARCHAR2(30) := 'set_joint_acct_owner_end_date';
4321 l_api_version CONSTANT NUMBER := 1.0;
4322 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
4323
4324
4325 BEGIN
4326
4327 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4328 print_debuginfo('ENTER ' || l_module_name);
4329
4330 END IF;
4331 SAVEPOINT set_acct_owner_end_date_pub;
4332
4333 -- Standard call to check for call compatibility.
4334 IF NOT FND_API.Compatible_API_Call(l_api_version,
4335 p_api_version,
4336 l_api_name,
4337 G_PKG_NAME) THEN
4338 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4339 END IF;
4340
4341 -- Initialize message list if p_init_msg_list is set to TRUE.
4342 IF FND_API.to_Boolean(p_init_msg_list) THEN
4343 FND_MSG_PUB.initialize;
4344 END IF;
4345
4346 -- Initialize API return status to success
4347 x_return_status := FND_API.G_RET_STS_SUCCESS;
4348
4349 -- Start of API body
4350 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4351 print_debuginfo('Object Version Number : ' || p_object_version_number);
4352
4353 END IF;
4354 -- Parameter validations
4355 check_mandatory('IBY_ACCTOWNER_ID_FIELD',p_acct_owner_id);
4356 check_mandatory('IBY_END_DATE',p_end_date);
4357 check_mandatory('IBY_OBJ_VER_NUM',p_object_version_number);
4358
4359 -- Set End Date to joint owners table
4360 UPDATE IBY_ACCOUNT_OWNERS
4361 SET END_DATE = p_end_date,
4362 OBJECT_VERSION_NUMBER = p_object_version_number + 1
4363 WHERE ACCOUNT_OWNER_ID = p_acct_owner_id
4364 AND OBJECT_VERSION_NUMBER = p_object_version_number
4365 RETURNING OBJECT_VERSION_NUMBER INTO p_object_version_number;
4366
4367 IF (SQL%NOTFOUND) THEN
4368 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4369 print_debuginfo('Warning : No Matching Rows found in IBY_ACCOUNT_OWNERS');
4370 END IF;
4371 END IF;
4372
4373 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4374 print_debuginfo('Updated Object Version Number : ' || p_object_version_number);
4375
4376 END IF;
4377 -- End of API body
4378
4379 -- get message count and if count is 1, get message info.
4380 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4381 p_count => x_msg_count,
4382 p_data => x_msg_data);
4383
4384 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4385 print_debuginfo('RETURN ' || l_module_name);
4386
4387 END IF;
4388 EXCEPTION
4389 WHEN fnd_api.g_exc_error THEN
4390 ROLLBACK TO set_acct_owner_end_date_pub;
4391 x_return_status := fnd_api.g_ret_sts_error;
4392 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4393 p_count => x_msg_count,
4394 p_data => x_msg_data);
4395
4396
4397 WHEN fnd_api.g_exc_unexpected_error THEN
4398 ROLLBACK TO set_acct_owner_end_date_pub;
4399 x_return_status := fnd_api.g_ret_sts_unexp_error;
4400 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4401 p_count => x_msg_count,
4402 p_data => x_msg_data);
4403
4404
4405 WHEN OTHERS THEN
4406 ROLLBACK TO set_acct_owner_end_date_pub;
4407 x_return_status := fnd_api.g_ret_sts_unexp_error;
4408 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
4409 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4410 p_count => x_msg_count,
4411 p_data => x_msg_data);
4412
4413
4414 END set_joint_acct_owner_end_date;
4415
4416
4417 -- 19. change_primary_acct_owner
4418 --
4419 -- API name : change_primary_acct_owner
4420 -- Type : Public
4421 -- Pre-reqs : None
4422 -- Function : Changes the
4423 -- Current version : 1.0
4424 -- Previous version: 1.0
4425 -- Initial version : 1.0
4426 --
4427 PROCEDURE change_primary_acct_owner (
4428 p_api_version IN NUMBER,
4429 p_init_msg_list IN VARCHAR2,
4430 p_bank_acct_id IN NUMBER,
4431 p_acct_owner_party_id IN NUMBER,
4432 x_return_status OUT NOCOPY VARCHAR2,
4433 x_msg_count OUT NOCOPY NUMBER,
4434 x_msg_data OUT NOCOPY VARCHAR2,
4435 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
4436 ) IS
4437
4438 l_api_name CONSTANT VARCHAR2(30) := 'change_primary_acct_owner';
4439 l_api_version CONSTANT NUMBER := 1.0;
4440 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
4441
4442 l_count PLS_INTEGER;
4443
4444 -- Pick up Current Primary Account for the Bank Account Id
4445 CURSOR c_account_owner(bank_account_id NUMBER)
4446 IS
4447 SELECT account_owner_id
4448 FROM IBY_ACCOUNT_OWNERS
4449 WHERE EXT_BANK_ACCOUNT_ID = bank_account_id
4450 AND primary_flag = 'Y';
4451
4452 BEGIN
4453
4454 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4455 print_debuginfo('ENTER ' || l_module_name);
4456
4457 END IF;
4458 SAVEPOINT change_primary_acct_owner_pub;
4459
4460 -- Standard call to check for call compatibility.
4461 IF NOT FND_API.Compatible_API_Call(l_api_version,
4462 p_api_version,
4463 l_api_name,
4464 G_PKG_NAME) THEN
4465 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4466 END IF;
4467
4468 -- Initialize message list if p_init_msg_list is set to TRUE.
4469 IF FND_API.to_Boolean(p_init_msg_list) THEN
4470 FND_MSG_PUB.initialize;
4471 END IF;
4472
4473 -- Initialize API return status to success
4474 x_return_status := FND_API.G_RET_STS_SUCCESS;
4475
4476 -- Start of API body
4477
4478 -- Parameter validations
4479 check_mandatory('IBY_BANKACCT_ID_FIELD',p_bank_acct_id);
4480 check_mandatory('IBY_ACCTOWNER_PARTYID_FIELD',p_acct_owner_party_id);
4481
4482 FOR l_account_owner_id IN c_account_owner(p_bank_acct_id)
4483 LOOP
4484 UPDATE IBY_ACCOUNT_OWNERS
4485 SET PRIMARY_FLAG = 'N'
4486 WHERE EXT_BANK_ACCOUNT_ID = p_bank_acct_id;
4487 END LOOP;
4488
4489 UPDATE IBY_ACCOUNT_OWNERS
4490 SET PRIMARY_FLAG = 'Y'
4491 WHERE ACCOUNT_OWNER_PARTY_ID = p_acct_owner_party_id
4492 AND EXT_BANK_ACCOUNT_ID = p_bank_acct_id;
4493
4494 l_count := SQL%ROWCOUNT;
4495 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4496 print_debuginfo('Rows affected : '||l_count);
4497
4498 END IF;
4499 IF (l_count = 1) THEN
4500 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4501 print_debuginfo('Primary Flag set for Account Owner '||p_acct_owner_party_id);
4502 END IF;
4503 ELSE
4504 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4505 print_debuginfo('Too many matching rows found');
4506 END IF;
4507 raise fnd_api.g_exc_unexpected_error;
4508 END IF;
4509
4510 -- End of API body
4511
4512 -- get message count and if count is 1, get message info.
4513 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4514 p_count => x_msg_count,
4515 p_data => x_msg_data);
4516
4517 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4518 print_debuginfo('RETURN ' || l_module_name);
4519 END IF;
4520 EXCEPTION
4521 WHEN fnd_api.g_exc_error THEN
4522 ROLLBACK TO change_primary_acct_owner_pub;
4523 x_return_status := fnd_api.g_ret_sts_error;
4524 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4525 print_debuginfo('Exception : ' || SQLERRM);
4526 END IF;
4527 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4528 p_count => x_msg_count,
4529 p_data => x_msg_data);
4530
4531
4532 WHEN fnd_api.g_exc_unexpected_error THEN
4533 ROLLBACK TO change_primary_acct_owner_pub;
4534 x_return_status := fnd_api.g_ret_sts_unexp_error;
4535 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4536 print_debuginfo('Exception : ' || SQLERRM);
4537 END IF;
4538 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4539 p_count => x_msg_count,
4540 p_data => x_msg_data);
4541
4542
4543 WHEN OTHERS THEN
4544 ROLLBACK TO change_primary_acct_owner_pub;
4545 x_return_status := fnd_api.g_ret_sts_unexp_error;
4546 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4547 print_debuginfo('Exception : ' || SQLERRM);
4548 END IF;
4549 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
4550
4551 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4552 p_count => x_msg_count,
4553 p_data => x_msg_data);
4554
4555 END change_primary_acct_owner;
4556
4557
4558
4559
4560 -- 20. check_bank_acct_owner
4561 --
4562 -- API name : check_bank_acct_owner
4563 -- Type : Public
4564 -- Pre-reqs : None
4565 -- Function : Changes the
4566 -- Current version : 1.0
4567 -- Previous version: 1.0
4568 -- Initial version : 1.0
4569 --
4570 PROCEDURE check_bank_acct_owner (
4571 p_api_version IN NUMBER,
4572 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
4573 p_bank_acct_id IN NUMBER,
4574 p_acct_owner_party_id IN NUMBER,
4575 x_return_status OUT NOCOPY VARCHAR2,
4576 x_msg_count OUT NOCOPY NUMBER,
4577 x_msg_data OUT NOCOPY VARCHAR2,
4578 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
4579 ) IS
4580 l_api_name CONSTANT VARCHAR2(30) := 'check_bank_acct_owner';
4581 l_api_version CONSTANT NUMBER := 1.0;
4582 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
4583 l_owner_id NUMBER;
4584
4585 BEGIN
4586
4587 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4588 print_debuginfo('ENTER ' || l_module_name);
4589
4590 END IF;
4591
4592 -- Standard call to check for call compatibility.
4593 IF NOT FND_API.Compatible_API_Call(l_api_version,
4594 p_api_version,
4595 l_api_name,
4596 G_PKG_NAME) THEN
4597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4598 END IF;
4599
4600 -- Initialize message list if p_init_msg_list is set to TRUE.
4601 IF FND_API.to_Boolean(p_init_msg_list) THEN
4602 FND_MSG_PUB.initialize;
4603 END IF;
4604
4605 -- Initialize API return status to success
4606 x_return_status := FND_API.G_RET_STS_SUCCESS;
4607
4608 -- Start of API body
4609
4610 -- Parameter validations
4611 check_mandatory('Bank Account Id',p_bank_acct_id);
4612 check_mandatory('Account Owner Party Id',p_acct_owner_party_id);
4613
4614 BEGIN
4615 SELECT account_owner_id
4616 INTO l_owner_id
4617 FROM IBY_ACCOUNT_OWNERS
4618 WHERE EXT_BANK_ACCOUNT_ID = p_bank_acct_id
4619 AND account_owner_party_id = p_acct_owner_party_id
4620 AND nvl(end_date, sysdate+1) > sysdate;
4621
4622 EXCEPTION WHEN No_Data_Found THEN
4623 x_return_status := fnd_api.g_ret_sts_error;
4624 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4625 print_debuginfo('Given Party is not the owner of the account');
4626 END IF;
4627 END;
4628
4629 -- End of API body
4630
4631 -- get message count and if count is 1, get message info.
4632 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4633 p_count => x_msg_count,
4634 p_data => x_msg_data);
4635
4636 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4637 print_debuginfo('RETURN ' || l_module_name);
4638 END IF;
4639 EXCEPTION
4640 WHEN fnd_api.g_exc_error THEN
4641 x_return_status := fnd_api.g_ret_sts_error;
4642 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4643 print_debuginfo('Exception : ' || SQLERRM);
4644 END IF;
4645 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4646 p_count => x_msg_count,
4647 p_data => x_msg_data);
4648 Raise;
4649
4650 WHEN fnd_api.g_exc_unexpected_error THEN
4651 x_return_status := fnd_api.g_ret_sts_unexp_error;
4652 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4653 print_debuginfo('Exception : ' || SQLERRM);
4654 END IF;
4655 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4656 p_count => x_msg_count,
4657 p_data => x_msg_data);
4658 Raise;
4659
4660 WHEN OTHERS THEN
4661 x_return_status := fnd_api.g_ret_sts_unexp_error;
4662 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4663 print_debuginfo('Exception : ' || SQLERRM);
4664 END IF;
4665 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_module_name, null);
4666
4667 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4668 p_count => x_msg_count,
4669 p_data => x_msg_data);
4670 Raise;
4671 END check_bank_acct_owner;
4672
4673
4674 /*=======================================================================+
4675 | PRIVATE FUNCTION get_country |
4676 | |
4677 | DESCRIPTION |
4678 | Get country of the bank given the bank party_id. |
4679 | |
4680 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
4681 | |
4682 | MODIFICATION HISTORY |
4683 | 07-SEP-2004 Xin Wang Created. |
4684 +=======================================================================*/
4685
4686 FUNCTION get_country (
4687 p_bank_id IN NUMBER,
4688 x_return_status IN OUT NOCOPY VARCHAR2
4689 ) RETURN VARCHAR2 IS
4690 CURSOR c_country IS
4691 SELECT hp.country
4692 FROM hz_parties hp
4693 WHERE hp.party_id = p_bank_id
4694 AND hp.status = 'A';
4695 l_country VARCHAR2(60);
4696 BEGIN
4697 -- initialize API return status to success.
4698 x_return_status := fnd_api.g_ret_sts_success;
4699
4700 OPEN c_country;
4701 FETCH c_country INTO l_country;
4702 IF c_country%NOTFOUND THEN
4703 fnd_message.set_name('IBY', 'IBY_API_NO_BANK');
4704 fnd_msg_pub.add;
4705 x_return_status := fnd_api.g_ret_sts_error;
4706 END IF;
4707 CLOSE c_country;
4708
4709 RETURN NVL(l_country, 'NULL');
4710 END get_country;
4711
4712
4713 /*=======================================================================+
4714 | PRIVATE PROCEDURE find_bank_info |
4715 | |
4716 | DESCRIPTION |
4717 | Get the party_id and country of the bank given a bank_branch_id. |
4718 | |
4719 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
4720 | |
4721 | MODIFICATION HISTORY |
4722 | 08-SEP-2004 Xin Wang Created. |
4723 +=======================================================================*/
4724
4725 PROCEDURE find_bank_info (
4726 p_branch_id IN NUMBER,
4727 x_return_status IN OUT NOCOPY VARCHAR2,
4728 x_bank_id OUT NOCOPY NUMBER,
4729 x_country_code OUT NOCOPY VARCHAR2,
4730 x_bank_name OUT NOCOPY VARCHAR2,
4731 x_bank_number OUT NOCOPY VARCHAR2
4732 ) IS
4733
4734 l_api_name CONSTANT VARCHAR2(30) := 'find_bank_info';
4735 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
4736
4737 CURSOR c_bank IS
4738 select ce_bank.bank_party_id as bank_id,
4739 ce_bank.bank_home_country as country,
4740 ce_bank.bank_name,
4741 ce_bank.bank_number
4742 from ce_bank_branches_v ce_bank
4743 where ce_bank.branch_party_id=p_branch_id;
4744
4745 /*
4746 SELECT hz_bank.party_id AS bank_id,
4747 hz_bank.country,
4748 hz_bank.party_name,
4749 hz_bankorg.bank_or_branch_number
4750 FROM hz_parties hz_bank,
4751 hz_organization_profiles hz_bankorg,
4752 hz_parties hz_branch,
4753 hz_relationships hz_rel,
4754 hz_code_assignments hz_bankCA,
4755 hz_code_assignments hz_branchCA
4756 WHERE hz_branchCA.owner_table_name = 'HZ_PARTIES'
4757 AND hz_branchCA.owner_table_id = hz_branch.party_id
4758 AND hz_branchCA.class_category = 'BANK_INSTITUTION_TYPE'
4759 AND hz_branchCA.class_code = 'BANK_BRANCH'
4760 AND NVL(hz_branchCA.STATUS, 'A') = 'A'
4761 AND hz_bankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
4762 AND hz_bankCA.CLASS_CODE = 'BANK'
4763 AND hz_bankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
4764 AND hz_bankCA.OWNER_TABLE_ID = hz_bank.PARTY_ID
4765 AND NVL(hz_bankCA.STATUS, 'A') = 'A'
4766 AND hz_rel.OBJECT_ID = hz_bank.PARTY_ID
4767 And hz_branch.PARTY_ID = hz_rel.SUBJECT_ID
4768 And hz_rel.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
4769 And hz_rel.RELATIONSHIP_CODE = 'BRANCH_OF'
4770 And hz_rel.STATUS = 'A'
4771 And hz_rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4772 And hz_rel.SUBJECT_TYPE = 'ORGANIZATION'
4773 And hz_rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4774 And hz_rel.OBJECT_TYPE = 'ORGANIZATION'
4775 AND hz_bank.party_id = hz_bankorg.party_id
4776 AND SYSDATE between TRUNC(hz_bankorg.effective_start_date)
4777 and NVL(TRUNC(hz_bankorg.effective_end_date), SYSDATE+1)
4778 AND hz_branch.party_id = p_branch_id;
4779 */
4780 BEGIN
4781 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4782 print_debuginfo('ENTER ' || l_module_name);
4783
4784 END IF;
4785 -- initialize API return status to success.
4786 x_return_status := fnd_api.g_ret_sts_success;
4787
4788 OPEN c_bank;
4789 FETCH c_bank INTO x_bank_id, x_country_code, x_bank_name, x_bank_number;
4790 IF c_bank%NOTFOUND THEN
4791 fnd_message.set_name('IBY', 'IBY_API_NO_BANK');
4792 fnd_msg_pub.add;
4793 x_return_status := fnd_api.g_ret_sts_error;
4794 END IF;
4795 CLOSE c_bank;
4796
4797 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4798 print_debuginfo('RETURN ' || l_module_name);
4799
4800 END IF;
4801 END find_bank_info;
4802
4803 /*=======================================================================+
4804 | PRIVATE PROCEDURE check_mandatory |
4805 | |
4806 | DESCRIPTION |
4807 | check for mandatory parameters. |
4808 | |
4809 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
4810 | |
4811 | MODIFICATION HISTORY |
4812 | 08-SEP-2004 nmukerje Created. |
4813 +=======================================================================*/
4814
4815 PROCEDURE check_mandatory(
4816 p_field IN VARCHAR2,
4817 p_value IN VARCHAR2
4818 ) IS
4819
4820 l_temp VARCHAR2(80);
4821
4822 CURSOR c_validate_currency (p_currency_code VARCHAR2) IS
4823 SELECT CURRENCY_CODE
4824 FROM FND_CURRENCIES
4825 WHERE CURRENCY_CODE = p_currency_code;
4826
4827 BEGIN
4828
4829 if (p_value is NULL) THEN
4830 fnd_message.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
4831 fnd_message.set_token('PARAM', fnd_message.get_string('IBY',p_field));
4832 fnd_msg_pub.add;
4833 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4834 print_debuginfo(fnd_message.get_string('IBY',p_field)|| ' is a required parameter.');
4835 END IF;
4836 RAISE fnd_api.g_exc_error;
4837 END IF;
4838
4839
4840 --Validate Currency
4841 IF (UPPER(p_field) = 'CURRENCY') THEN
4842 OPEN c_validate_currency(p_value);
4843 FETCH c_validate_currency INTO l_temp;
4844 CLOSE c_validate_currency;
4845
4846 IF (l_temp IS NULL) THEN
4847 fnd_message.set_name('IBY', 'IBY_INVALID_CURRENCY');
4848 fnd_message.set_token('CURRENCY_CODE', p_field);
4849 fnd_msg_pub.add;
4850 RAISE fnd_api.g_exc_error;
4851 END IF;
4852
4853 END IF;
4854 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4855 print_debuginfo('Checked mandatory field : ' || p_field || ' : ' || p_value);
4856 END IF;
4857 END check_mandatory;
4858
4859 /*=======================================================================+
4860 | PRIVATE PROCEDURE print_debuginfo |
4861 | |
4862 | DESCRIPTION |
4863 | Get the party_id and country of the bank given a bank_branch_id. |
4864 | |
4865 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
4866 | |
4867 | MODIFICATION HISTORY |
4868 | 08-SEP-2004 Nilanshu Mukerje Created. |
4869 +=======================================================================*/
4870 PROCEDURE print_debuginfo(
4871 p_message IN VARCHAR2,
4872 p_prefix IN VARCHAR2,
4873 p_msg_level IN NUMBER,
4874 p_module IN VARCHAR2
4875 ) IS
4876
4877 l_message VARCHAR2(4000);
4878 l_module VARCHAR2(255);
4879
4880 BEGIN
4881
4882 -- DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,55)||' : '||p_message, 0, 150));
4883 -- insert into ying_debug(log_time, text) values(sysdate, p_message);
4884
4885 -- Debug info.
4886 l_module :=SUBSTRB(p_module,1,255);
4887
4888 IF p_prefix IS NOT NULL THEN
4889 l_message :=SUBSTRB(p_prefix||'-'||p_message,1,4000);
4890 ELSE
4891 l_message :=SUBSTRB(p_message,1,4000);
4892 END IF;
4893 IF p_msg_level>=fnd_log.g_current_runtime_level THEN
4894
4895 FND_LOG.STRING(p_msg_level,l_module,l_message);
4896
4897 END IF;
4898
4899 END print_debuginfo;
4900
4901 --
4902 -- USE: Gets the bank account instrument encryption mode
4903 --
4904 FUNCTION Get_BA_Encrypt_Mode
4905 RETURN iby_sys_security_options.ext_ba_encryption_mode%TYPE
4906 IS
4907
4908 l_mode iby_sys_security_options.ext_ba_encryption_mode%TYPE;
4909
4910 CURSOR c_encrypt_mode
4911 IS
4912 SELECT ext_ba_encryption_mode
4913 FROM iby_sys_security_options;
4914
4915 BEGIN
4916 IF (c_encrypt_mode%ISOPEN) THEN CLOSE c_encrypt_mode; END IF;
4917
4918 OPEN c_encrypt_mode;
4919 FETCH c_encrypt_mode INTO l_mode;
4920 CLOSE c_encrypt_mode;
4921
4922 RETURN l_mode;
4923 END Get_BA_Encrypt_Mode;
4924
4925 FUNCTION Mask_Bank_Number( p_bank_number IN VARCHAR2 ) RETURN VARCHAR2
4926 IS
4927 lx_mask_option iby_ext_bank_accounts.ba_mask_setting%TYPE;
4928 lx_unmask_len iby_ext_bank_accounts.ba_unmask_length%TYPE;
4929 BEGIN
4930 Get_Mask_Settings(lx_mask_option,lx_unmask_len);
4931 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4932 print_debuginfo('The value of lx_mask_option :' ||lx_mask_option);
4933 print_debuginfo('The value of lx_unmask_len :'|| lx_unmask_len);
4934 END IF;
4935 RETURN Mask_Bank_Number(p_bank_number,lx_mask_option,lx_unmask_len);
4936 END Mask_Bank_Number;
4937
4938 FUNCTION Mask_Bank_Number
4939 (p_bank_number IN VARCHAR2,
4940 p_mask_option IN iby_ext_bank_accounts.ba_mask_setting%TYPE,
4941 p_unmask_len IN iby_ext_bank_accounts.ba_unmask_length%TYPE
4942 )
4943 RETURN VARCHAR2
4944 IS
4945 BEGIN
4946 RETURN iby_security_pkg.Mask_Data
4947 (p_bank_number,p_mask_option,p_unmask_len,G_MASK_CHARACTER);
4948 END Mask_Bank_Number;
4949
4950 PROCEDURE Compress_Bank_Number
4951 (p_bank_number IN VARCHAR2,
4952 p_mask_setting IN iby_sys_security_options.ext_ba_mask_setting%TYPE,
4953 p_unmask_len IN iby_sys_security_options.ext_ba_unmask_len%TYPE,
4954 x_compress_num OUT NOCOPY VARCHAR2,
4955 x_unmask_digits OUT NOCOPY VARCHAR2
4956 )
4957 IS
4958 l_prefix_index NUMBER;
4959 l_unmask_len NUMBER;
4960 l_substr_start NUMBER;
4961 l_substr_stop NUMBER;
4962 BEGIN
4963 x_unmask_digits :=
4964 iby_security_pkg.Get_Unmasked_Data
4965 (p_bank_number,p_mask_setting,p_unmask_len);
4966 l_unmask_len := NVL(LENGTH(x_unmask_digits),0);
4967
4968 -- all digits exposed; compressed number is trivial
4969 IF (l_unmask_len >= LENGTH(p_bank_number)) THEN
4970 x_compress_num := NULL;
4971 RETURN;
4972 END IF;
4973
4974 IF ( (p_mask_setting = iby_security_pkg.G_MASK_POSTFIX) )
4975 THEN
4976 l_substr_start := l_unmask_len + 1;
4977 ELSE
4978 l_substr_start := 1;
4979 END IF;
4980
4981 IF (p_mask_setting = iby_security_pkg.G_MASK_PREFIX)
4982 AND (p_unmask_len>0)
4983 THEN
4984 l_substr_stop := GREATEST(LENGTH(p_bank_number)-p_unmask_len,0);
4985 ELSE
4986 l_substr_stop := LENGTH(p_bank_number);
4987 END IF;
4988
4989 IF (l_substr_start < (l_substr_stop +1)) THEN
4990 x_compress_num := SUBSTR(p_bank_number,l_substr_start,
4991 l_substr_stop - l_substr_start + 1);
4992 ELSE
4993 x_compress_num := NULL;
4994 END IF;
4995 END Compress_Bank_Number;
4996
4997 FUNCTION Uncipher_Bank_Number
4998 (p_unmask_digits IN VARCHAR2,
4999 p_segment_id IN iby_security_segments.sec_segment_id%TYPE,
5000 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
5001 p_sub_key_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
5002 p_segment_cipher IN iby_security_segments.segment_cipher_text%TYPE,
5003 p_encoding IN iby_security_segments.encoding_scheme%TYPE,
5004 p_mask_option IN iby_ext_bank_accounts.ba_mask_setting%TYPE,
5005 p_unmask_len IN iby_ext_bank_accounts.ba_unmask_length%TYPE
5006 )
5007 RETURN VARCHAR2
5008 IS
5009 l_sub_key iby_sys_security_subkeys.subkey_cipher_text%TYPE;
5010 l_bank_segment iby_security_segments.segment_cipher_text%TYPE;
5011 l_bank_num VARCHAR2(200);
5012 BEGIN
5013
5014 IF (p_segment_id IS NULL) THEN RETURN p_unmask_digits; END IF;
5015
5016 -- [lmallick] - 11876396
5017 -- For extract debugging
5018 IF (iby_utility_pvt.debug_mode_for_extract_enabled = 'Y') THEN
5019 RETURN p_unmask_digits;
5020 END IF ;
5021
5022 -- uncipher the subkey
5023 l_sub_key := iby_security_pkg.get_sys_subkey(p_sys_key,p_sub_key_cipher);
5024
5025 -- uncipher the segment
5026 l_bank_segment :=
5027 dbms_obfuscation_toolkit.des3decrypt
5028 ( input => p_segment_cipher, key => l_sub_key,
5029 which => dbms_obfuscation_toolkit.ThreeKeyMode
5030 );
5031 l_bank_segment := IBY_SECURITY_PKG.PKCS5_UNPAD(l_bank_segment);
5032 l_bank_num := UTL_I18N.RAW_TO_CHAR(l_bank_segment,p_encoding);
5033
5034 IF (NOT p_unmask_digits IS NULL) THEN
5035 IF (p_mask_option = iby_security_pkg.G_MASK_POSTFIX) THEN
5036 l_bank_num := p_unmask_digits || l_bank_num;
5037 ELSIF (p_mask_option = iby_security_pkg.G_MASK_PREFIX) THEN
5038 l_bank_num := l_bank_num || p_unmask_digits;
5039 END IF;
5040 END IF;
5041
5042 RETURN l_bank_num;
5043 END Uncipher_Bank_Number;
5044
5045 PROCEDURE Remask_Accounts
5046 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
5047 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
5048 )
5049 IS
5050 lx_mask_option iby_ext_bank_accounts.ba_mask_setting%TYPE;
5051 lx_unmask_len iby_ext_bank_accounts.ba_unmask_length%TYPE;
5052 l_mode iby_sys_security_options.ext_ba_encryption_mode%TYPE;
5053 lx_key_error VARCHAR2(300);
5054
5055 l_ba_num iby_ext_bank_accounts.bank_account_num%TYPE;
5056 l_iban iby_ext_bank_accounts.iban%TYPE;
5057 lx_compress_num iby_ext_bank_accounts.bank_account_num%TYPE;
5058
5059 lx_ba_unmask_digits iby_ext_bank_accounts.bank_account_num%TYPE;
5060 l_ba_segment iby_security_segments.segment_cipher_text%TYPE;
5061 lx_ba_segment_id iby_ext_bank_accounts.ba_num_sec_segment_id%TYPE;
5062
5063 lx_iban_unmask_digits iby_ext_bank_accounts.iban%TYPE;
5064 l_iban_segment iby_security_segments.segment_cipher_text%TYPE;
5065 lx_iban_segment_id iby_ext_bank_accounts.ba_num_sec_segment_id%TYPE;
5066
5067 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.Remask_Accounts';
5068
5069 CURSOR c_ext_ba
5070 (ci_mask_option iby_ext_bank_accounts.ba_mask_setting%TYPE,
5071 ci_unmask_len iby_ext_bank_accounts.ba_unmask_length%TYPE
5072 )
5073 IS
5074 SELECT b.ext_bank_account_id, b.bank_account_num, b.iban,
5075 b.ba_num_sec_segment_id, bak.subkey_cipher_text ba_subkey_cipher,
5076 bas.segment_cipher_text ba_segment_cipher,
5077 bas.encoding_scheme ba_encoding,
5078 b.iban_sec_segment_id, ibk.subkey_cipher_text iban_subkey_cipher,
5079 ibs.segment_cipher_text iban_segment_cipher,
5080 ibs.encoding_scheme iban_encoding,
5081 b.ba_mask_setting, b.ba_unmask_length
5082 FROM iby_ext_bank_accounts b, iby_sys_security_subkeys bak,
5083 iby_sys_security_subkeys ibk, iby_security_segments bas,
5084 iby_security_segments ibs
5085 WHERE
5086 ( (NVL(ba_unmask_length,-1) <> ci_unmask_len) OR
5087 (NVL(ba_mask_setting,' ') <> ci_mask_option) )
5088 AND (b.ba_num_sec_segment_id = bas.sec_segment_id(+))
5089 AND (bas.sec_subkey_id = bak.sec_subkey_id(+))
5090 AND (b.iban_sec_segment_id = ibs.sec_segment_id(+))
5091 AND (ibs.sec_subkey_id = ibk.sec_subkey_id(+));
5092
5093 BEGIN
5094 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
5095
5096 IF (c_ext_ba%ISOPEN) THEN CLOSE c_ext_ba; END IF;
5097
5098 Get_Mask_Settings(lx_mask_option,lx_unmask_len);
5099
5100 iby_debug_pub.add('masking option:=' || lx_mask_option,
5101 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
5102 iby_debug_pub.add('unmask length:=' || lx_unmask_len,
5103 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
5104
5105 iby_security_pkg.Validate_Sys_Key(p_sys_key,lx_key_error);
5106 l_mode := Get_BA_Encrypt_Mode();
5107
5108 iby_debug_pub.add('sys key check:=' || lx_key_error,
5109 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
5110
5111 FOR ext_ba_rec IN c_ext_ba(lx_mask_option,lx_unmask_len) LOOP
5112 l_ba_num := NULL;
5113 l_iban := NULL;
5114 lx_compress_num := NULL;
5115 lx_ba_unmask_digits := NULL;
5116 lx_iban_unmask_digits := NULL;
5117 l_ba_segment := NULL;
5118 l_iban_segment := NULL;
5119 lx_ba_segment_id := NULL;
5120 lx_iban_segment_id := NULL;
5121
5122 iby_debug_pub.add('bank account:=' || ext_ba_rec.ext_bank_account_id,
5123 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
5124
5125 IF ( ((NOT ext_ba_rec.ba_num_sec_segment_id IS NULL) OR
5126 (NOT ext_ba_rec.iban_sec_segment_id IS NULL)
5127 ) AND (NOT lx_key_error IS NULL) )
5128 THEN
5129 raise_application_error(-20000,lx_key_error, FALSE);
5130 END IF;
5131
5132 l_ba_num := Uncipher_Bank_Number(ext_ba_rec.bank_account_num,
5133 ext_ba_rec.ba_num_sec_segment_id,
5134 p_sys_key,
5135 ext_ba_rec.ba_subkey_cipher,
5136 ext_ba_rec.ba_segment_cipher,
5137 ext_ba_rec.ba_encoding,
5138 ext_ba_rec.ba_mask_setting,
5139 ext_ba_rec.ba_unmask_length);
5140 l_iban := Uncipher_Bank_Number(ext_ba_rec.iban,
5141 ext_ba_rec.iban_sec_segment_id,
5142 p_sys_key,
5143 ext_ba_rec.iban_subkey_cipher,
5144 ext_ba_rec.iban_segment_cipher,
5145 ext_ba_rec.iban_encoding,
5146 ext_ba_rec.ba_mask_setting,
5147 ext_ba_rec.ba_unmask_length);
5148
5149 IF (NOT ext_ba_rec.ba_num_sec_segment_id IS NULL)
5150 OR (NOT ext_ba_rec.iban_sec_segment_id IS NULL)
5151 THEN
5152
5153 iby_debug_pub.add('encrypted bank account data',
5154 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
5155 Compress_Bank_Number
5156 (l_ba_num,lx_mask_option,lx_unmask_len,lx_compress_num,
5157 lx_ba_unmask_digits);
5158
5159 IF (NOT lx_compress_num IS NULL) THEN
5160 l_ba_segment :=
5161 UTL_I18N.STRING_TO_RAW
5162 (lx_compress_num,iby_security_pkg.G_ENCODING_UTF8_AL32);
5163 l_ba_segment := IBY_SECURITY_PKG.PKCS5_PAD(l_ba_segment);
5164
5165 IF (ext_ba_rec.ba_num_sec_segment_id IS NULL) THEN
5166 IBY_SECURITY_PKG.Create_Segment
5167 (FND_API.G_FALSE,l_ba_segment,
5168 iby_security_pkg.G_ENCODING_UTF8_AL32,
5169 p_sys_key,lx_ba_segment_id);
5170 ELSE
5171 lx_ba_segment_id := ext_ba_rec.ba_num_sec_segment_id;
5172 IBY_SECURITY_PKG.Update_Segment
5173 (FND_API.G_FALSE,lx_ba_segment_id,l_ba_segment,
5174 iby_security_pkg.G_ENCODING_UTF8_AL32,p_sys_key,
5175 ext_ba_rec.ba_subkey_cipher);
5176 END IF;
5177 ELSE
5178 DELETE FROM iby_security_segments
5179 WHERE sec_segment_id = ext_ba_rec.ba_num_sec_segment_id;
5180 END IF;
5181
5182 Compress_Bank_Number
5183 (l_iban,lx_mask_option,lx_unmask_len,lx_compress_num,
5184 lx_iban_unmask_digits);
5185
5186 IF (NOT lx_compress_num IS NULL) THEN
5187 l_iban_segment :=
5188 UTL_I18N.STRING_TO_RAW
5189 (lx_compress_num,iby_security_pkg.G_ENCODING_UTF8_AL32);
5190 l_iban_segment := IBY_SECURITY_PKG.PKCS5_PAD(l_iban_segment);
5191
5192 IF (ext_ba_rec.iban_sec_segment_id IS NULL) THEN
5193 IBY_SECURITY_PKG.Create_Segment
5194 (FND_API.G_FALSE,l_iban_segment,
5195 iby_security_pkg.G_ENCODING_UTF8_AL32,p_sys_key,
5196 lx_iban_segment_id);
5197 ELSE
5198 lx_iban_segment_id := ext_ba_rec.iban_sec_segment_id;
5199 IBY_SECURITY_PKG.Update_Segment
5200 (FND_API.G_FALSE,lx_iban_segment_id,l_iban_segment,
5201 iby_security_pkg.G_ENCODING_UTF8_AL32,p_sys_key,
5202 ext_ba_rec.iban_subkey_cipher);
5203 END IF;
5204 ELSE
5205 DELETE FROM iby_security_segments
5206 WHERE sec_segment_id = ext_ba_rec.iban_sec_segment_id;
5207 END IF;
5208
5209 ELSE
5210 iby_debug_pub.add('unencrypted bank account data',
5211 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
5212
5213 lx_ba_unmask_digits := l_ba_num;
5214 lx_iban_unmask_digits := l_iban;
5215
5216 DELETE FROM iby_security_segments
5217 WHERE sec_segment_id = ext_ba_rec.ba_num_sec_segment_id;
5218 DELETE FROM iby_security_segments
5219 WHERE sec_segment_id = ext_ba_rec.iban_sec_segment_id;
5220 END IF;
5221
5222 UPDATE iby_ext_bank_accounts
5223 SET
5224 bank_account_num = lx_ba_unmask_digits,
5225 ba_num_sec_segment_id = lx_ba_segment_id,
5226 iban = lx_iban_unmask_digits,
5227 iban_sec_segment_id = lx_iban_segment_id,
5228 masked_bank_account_num =
5229 Mask_Bank_Number(l_ba_num,lx_mask_option,lx_unmask_len),
5230 masked_iban = Mask_Bank_Number(l_iban,lx_mask_option,lx_unmask_len),
5231 ba_mask_setting = lx_mask_option,
5232 ba_unmask_length = lx_unmask_len,
5233 encrypted = DECODE(l_mode, iby_security_pkg.G_ENCRYPT_MODE_NONE,'N',
5234 'Y'
5235 ),
5236 object_version_number = object_version_number + 1,
5237 last_update_date = sysdate,
5238 last_updated_by = fnd_global.user_id,
5239 last_update_login = fnd_global.login_id
5240 WHERE (ext_bank_account_id = ext_ba_rec.ext_bank_account_id);
5241
5242 END LOOP;
5243
5244 IF FND_API.to_Boolean( p_commit ) THEN
5245 COMMIT;
5246 END IF;
5247
5248 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
5249 END Remask_Accounts;
5250
5251 PROCEDURE Encrypt_Accounts
5252 (p_commit IN VARCHAR2,
5253 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
5254 )
5255 IS
5256 l_mode iby_sys_security_options.ext_ba_encryption_mode%TYPE;
5257 lx_key_error VARCHAR2(300);
5258
5259 lx_compress_num iby_ext_bank_accounts.bank_account_num%TYPE;
5260
5261 lx_ba_unmask_digits iby_ext_bank_accounts.bank_account_num%TYPE;
5262 l_ba_segment iby_security_segments.segment_cipher_text%TYPE;
5263 lx_ba_segment_id iby_ext_bank_accounts.ba_num_sec_segment_id%TYPE;
5264
5265 lx_iban_unmask_digits iby_ext_bank_accounts.iban%TYPE;
5266 l_iban_segment iby_security_segments.segment_cipher_text%TYPE;
5267 lx_iban_segment_id iby_ext_bank_accounts.ba_num_sec_segment_id%TYPE;
5268
5269 lx_e_ba_unmask_digits iby_ext_bank_accounts.bank_account_num_electronic%TYPE;
5270 l_e_ba_segment iby_security_segments.segment_cipher_text%TYPE;
5271 lx_e_ba_segment_id iby_ext_bank_accounts.ba_num_elec_sec_segment_id%TYPE;
5272
5273 CURSOR c_ext_ba
5274 IS
5275 SELECT b.ext_bank_account_id, b.bank_account_num, b.iban,
5276 b.ba_num_sec_segment_id, b.iban_sec_segment_id,
5277 b.bank_account_num_electronic, b.ba_num_elec_sec_segment_id,
5278 b.ba_mask_setting, b.ba_unmask_length
5279 FROM iby_ext_bank_accounts b
5280 WHERE (NVL(b.encrypted,'N') = 'N');
5281
5282 BEGIN
5283
5284 l_mode := Get_BA_Encrypt_Mode();
5285 IF (l_mode = iby_security_pkg.G_ENCRYPT_MODE_NONE) THEN
5286 RETURN;
5287 END IF;
5288
5289 iby_security_pkg.Validate_Sys_Key(p_sys_key,lx_key_error);
5290 IF (NOT lx_key_error IS NULL) THEN
5291 raise_application_error(-20000,lx_key_error, FALSE);
5292 END IF;
5293
5294 FOR ext_ba_rec IN c_ext_ba LOOP
5295 lx_ba_unmask_digits := NULL;
5296 lx_iban_unmask_digits := NULL;
5297 lx_ba_segment_id := NULL;
5298 lx_iban_segment_id := NULL;
5299 lx_e_ba_unmask_digits := NULL;
5300 lx_e_ba_segment_id := NULL;
5301
5302 -- only one of bank acocunt number, IBAN number may be unencrypted
5303 -- thanks to update of only one of these values
5304
5305 IF (ext_ba_rec.ba_num_sec_segment_id IS NULL)
5306 AND (NOT ext_ba_rec.bank_account_num IS NULL)
5307 THEN
5308 Compress_Bank_Number
5309 (ext_ba_rec.bank_account_num,ext_ba_rec.ba_mask_setting,
5310 ext_ba_rec.ba_unmask_length,lx_compress_num,lx_ba_unmask_digits);
5311
5312 IF (NOT lx_compress_num IS NULL) THEN
5313 l_ba_segment :=
5314 UTL_I18N.STRING_TO_RAW
5315 (lx_compress_num,iby_security_pkg.G_ENCODING_UTF8_AL32);
5316 -- pad to unit 8 length
5317 l_ba_segment := IBY_SECURITY_PKG.PKCS5_PAD(l_ba_segment);
5318
5319 IBY_SECURITY_PKG.Create_Segment
5320 (FND_API.G_FALSE,l_ba_segment,iby_security_pkg.G_ENCODING_UTF8_AL32,
5321 p_sys_key,lx_ba_segment_id);
5322 END IF;
5323 ELSE
5324 lx_ba_unmask_digits := ext_ba_rec.bank_account_num;
5325 END IF;
5326
5327 IF (ext_ba_rec.iban_sec_segment_id IS NULL)
5328 AND (NOT ext_ba_rec.iban IS NULL)
5329 THEN
5330 Compress_Bank_Number
5331 (ext_ba_rec.iban,ext_ba_rec.ba_mask_setting,
5332 ext_ba_rec.ba_unmask_length,lx_compress_num,lx_iban_unmask_digits);
5333
5334 IF (NOT lx_compress_num IS NULL) THEN
5335 l_iban_segment :=
5336 UTL_I18N.STRING_TO_RAW
5337 (lx_compress_num,iby_security_pkg.G_ENCODING_UTF8_AL32);
5338 -- pad to unit 8 length
5339 l_iban_segment := IBY_SECURITY_PKG.PKCS5_PAD(l_iban_segment);
5340
5341 IBY_SECURITY_PKG.Create_Segment
5342 (FND_API.G_FALSE,l_iban_segment,
5343 iby_security_pkg.G_ENCODING_UTF8_AL32,p_sys_key,lx_iban_segment_id);
5344 END IF;
5345 ELSE
5346 lx_iban_unmask_digits := ext_ba_rec.iban;
5347 END IF;
5348
5349 IF (ext_ba_rec.ba_num_elec_sec_segment_id IS NULL)
5350 AND (NOT ext_ba_rec.bank_account_num_electronic IS NULL)
5351 THEN
5352 lx_e_ba_unmask_digits := NULL;
5353 l_e_ba_segment :=
5354 UTL_I18N.STRING_TO_RAW
5355 (ext_ba_rec.bank_account_num_electronic,
5356 iby_security_pkg.G_ENCODING_UTF8_AL32);
5357 -- pad to unit 8 length
5358 l_e_ba_segment := IBY_SECURITY_PKG.PKCS5_PAD(l_e_ba_segment);
5359
5360 IBY_SECURITY_PKG.Create_Segment
5361 (FND_API.G_FALSE,l_e_ba_segment,
5362 iby_security_pkg.G_ENCODING_UTF8_AL32,p_sys_key,lx_e_ba_segment_id);
5363 ELSE
5364 lx_e_ba_unmask_digits := ext_ba_rec.bank_account_num_electronic;
5365 END IF;
5366
5367 UPDATE iby_ext_bank_accounts
5368 SET
5369 bank_account_num = lx_ba_unmask_digits,
5370 iban = lx_iban_unmask_digits,
5371 ba_num_sec_segment_id = NVL(lx_ba_segment_id,ba_num_sec_segment_id),
5372 iban_sec_segment_id = NVL(lx_iban_segment_id,iban_sec_segment_id),
5373 bank_account_num_electronic = lx_e_ba_unmask_digits,
5374 ba_num_elec_sec_segment_id =
5375 NVL(lx_e_ba_segment_id,ba_num_elec_sec_segment_id),
5376 encrypted = 'Y'
5377 WHERE (ext_bank_account_id = ext_ba_rec.ext_bank_account_id);
5378 END LOOP;
5379
5380 IF FND_API.to_Boolean( p_commit ) THEN
5381 COMMIT;
5382 END IF;
5383 END Encrypt_Accounts;
5384
5385 PROCEDURE Decrypt_Accounts
5386 (p_commit IN VARCHAR2,
5387 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
5388 )
5389 IS
5390 l_mode iby_sys_security_options.ext_ba_encryption_mode%TYPE;
5391 l_subkey iby_sys_security_subkeys.subkey_cipher_text%TYPE;
5392 l_ba_num iby_ext_bank_accounts.bank_account_num%TYPE;
5393 l_iban iby_ext_bank_accounts.iban%TYPE;
5394 l_e_ba iby_ext_bank_accounts.bank_account_num_electronic%TYPE;
5395
5396 lx_key_error VARCHAR2(300);
5397
5398 CURSOR c_ext_ba
5399 IS
5400 SELECT b.ext_bank_account_id, b.bank_account_num, b.iban,
5401 b.bank_account_num_electronic,
5402 b.ba_num_sec_segment_id, bak.subkey_cipher_text ba_subkey_cipher,
5403 bas.segment_cipher_text ba_segment_cipher,
5404 bas.encoding_scheme ba_encoding,
5405 b.iban_sec_segment_id, ibk.subkey_cipher_text iban_subkey_cipher,
5406 ibs.segment_cipher_text iban_segment_cipher,
5407 ibs.encoding_scheme iban_encoding,
5408 b.ba_num_elec_sec_segment_id,
5409 ebk.subkey_cipher_text e_ba_subkey_cipher,
5410 ebs.segment_cipher_text e_ba_segment_cipher,
5411 ebs.encoding_scheme e_ba_encoding,
5412 b.ba_mask_setting, b.ba_unmask_length
5413 FROM iby_ext_bank_accounts b, iby_sys_security_subkeys bak,
5414 iby_sys_security_subkeys ebk, iby_sys_security_subkeys ibk,
5415 iby_security_segments bas, iby_security_segments ibs,
5416 iby_security_segments ebs
5417 WHERE
5418 ((NOT ba_num_sec_segment_id IS NULL) OR (NOT iban_sec_segment_id IS NULL))
5419 AND (b.ba_num_sec_segment_id = bas.sec_segment_id(+))
5420 AND (bas.sec_subkey_id = bak.sec_subkey_id(+))
5421 AND (b.iban_sec_segment_id = ibs.sec_segment_id(+))
5422 AND (ibs.sec_subkey_id = ibk.sec_subkey_id(+))
5423 AND (b.ba_num_elec_sec_segment_id = ebs.sec_segment_id(+))
5424 AND (ebs.sec_subkey_id = ebk.sec_subkey_id(+));
5425
5426 BEGIN
5427
5428 l_mode := Get_BA_Encrypt_Mode();
5429 IF (NOT (l_mode = iby_security_pkg.G_ENCRYPT_MODE_NONE)) THEN
5430 RETURN;
5431 END IF;
5432
5433 iby_security_pkg.Validate_Sys_Key(p_sys_key,lx_key_error);
5434
5435 FOR ext_ba_rec IN c_ext_ba LOOP
5436
5437 -- raise sys-key exception only if encrypted data exists
5438 IF (NOT lx_key_error IS NULL) THEN
5439 raise_application_error(-20000,lx_key_error, FALSE);
5440 END IF;
5441
5442 l_ba_num := NULL;
5443 l_iban := NULL;
5444 l_e_ba := NULL;
5445
5446 IF (NOT ext_ba_rec.ba_num_sec_segment_id IS NULL) THEN
5447 l_ba_num := Uncipher_Bank_Number(ext_ba_rec.bank_account_num,
5448 ext_ba_rec.ba_num_sec_segment_id,
5449 p_sys_key,
5450 ext_ba_rec.ba_subkey_cipher,
5451 ext_ba_rec.ba_segment_cipher,
5452 ext_ba_rec.ba_encoding,
5453 ext_ba_rec.ba_mask_setting,
5454 ext_ba_rec.ba_unmask_length);
5455 END IF;
5456
5457 IF (NOT ext_ba_rec.iban_sec_segment_id IS NULL) THEN
5458 l_iban := Uncipher_Bank_Number(ext_ba_rec.iban,
5459 ext_ba_rec.iban_sec_segment_id,
5460 p_sys_key,
5461 ext_ba_rec.iban_subkey_cipher,
5462 ext_ba_rec.iban_segment_cipher,
5463 ext_ba_rec.iban_encoding,
5464 ext_ba_rec.ba_mask_setting,
5465 ext_ba_rec.ba_unmask_length);
5466 END IF;
5467
5468
5469 IF (NOT ext_ba_rec.ba_num_elec_sec_segment_id IS NULL) THEN
5470 l_e_ba := Uncipher_Bank_Number(ext_ba_rec.bank_account_num_electronic,
5471 ext_ba_rec.ba_num_elec_sec_segment_id,
5472 p_sys_key,
5473 ext_ba_rec.e_ba_subkey_cipher,
5474 ext_ba_rec.e_ba_segment_cipher,
5475 ext_ba_rec.e_ba_encoding,
5476 ext_ba_rec.ba_mask_setting,
5477 ext_ba_rec.ba_unmask_length);
5478 END IF;
5479
5480 UPDATE iby_ext_bank_accounts
5481 SET
5482 bank_account_num = NVL(l_ba_num,bank_account_num),
5483 iban = NVL(l_iban,iban),
5484 bank_account_num_electronic = NVL(l_e_ba,bank_account_num_electronic),
5485 ba_num_sec_segment_id = NULL,
5486 iban_sec_segment_id = NULL,
5487 ba_num_elec_sec_segment_id = NULL,
5488 encrypted = 'N',
5489 object_version_number = object_version_number + 1,
5490 last_update_date = sysdate,
5491 last_updated_by = fnd_global.user_id,
5492 last_update_login = fnd_global.login_id
5493 WHERE (ext_bank_account_id = ext_ba_rec.ext_bank_account_id);
5494
5495 DELETE FROM iby_security_segments
5496 WHERE sec_segment_id = ext_ba_rec.ba_num_sec_segment_id;
5497 DELETE FROM iby_security_segments
5498 WHERE sec_segment_id = ext_ba_rec.iban_sec_segment_id;
5499 DELETE FROM iby_security_segments
5500 WHERE sec_segment_id = ext_ba_rec.ba_num_elec_sec_segment_id;
5501
5502 END LOOP;
5503
5504 IF FND_API.to_Boolean( p_commit ) THEN
5505 COMMIT;
5506 END IF;
5507 END Decrypt_Accounts;
5508
5509 --
5510 -- USE: Get bank account mask settings
5511 --
5512 PROCEDURE Get_Mask_Settings
5513 (x_mask_setting OUT NOCOPY iby_sys_security_options.ext_ba_mask_setting%TYPE,
5514 x_unmask_len OUT NOCOPY iby_sys_security_options.ext_ba_unmask_len%TYPE
5515 )
5516 IS
5517
5518 CURSOR c_mask_setting
5519 IS
5520 SELECT ext_ba_mask_setting, ext_ba_unmask_len
5521 FROM iby_sys_security_options;
5522
5523 BEGIN
5524 x_mask_setting := iby_security_pkg.G_MASK_PREFIX;
5525
5526 IF (c_mask_setting%ISOPEN) THEN CLOSE c_mask_setting; END IF;
5527
5528 OPEN c_mask_setting;
5529 FETCH c_mask_setting INTO x_mask_setting, x_unmask_len;
5530 CLOSE c_mask_setting;
5531
5532 IF (x_mask_setting IS NULL) THEN
5533 x_mask_setting := iby_security_pkg.G_MASK_PREFIX;
5534 END IF;
5535 IF (x_unmask_len IS NULL) THEN
5536 x_unmask_len := G_DEF_UNMASK_LENGTH;
5537 END IF;
5538 END Get_Mask_Settings;
5539
5540 --FSIO Code
5541 PROCEDURE vendor_id(p_party_id IN VARCHAR2,
5542 x_vendor_id OUT NOCOPY NUMBER) IS
5543 BEGIN
5544 SELECT vendor_id
5545 INTO x_vendor_id
5546 FROM ap_suppliers
5547 WHERE party_id = p_party_id
5548 AND rownum = 1;
5549
5550 EXCEPTION
5551 WHEN no_data_found THEN
5552 x_vendor_id := -999;
5553 WHEN others then
5554 x_vendor_id := -999;
5555 END vendor_id;
5556 --End of FSIO
5557
5558
5559 FUNCTION find_assignment_OU
5560 ( p_ext_bank_acct_id IN iby_ext_bank_accounts.ext_bank_account_id%TYPE
5561 )
5562 RETURN NUMBER IS
5563 l_org_id NUMBER :=-1;
5564 l_api_name CONSTANT VARCHAR2(30) := 'find_assignment_OU';
5565 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
5566 BEGIN
5567 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5568 print_debuginfo('ENTER ' || l_module_name);
5569 END IF;
5570 BEGIN
5571 SELECT org_id INTO l_org_id FROM iby_external_payees_all
5572 WHERE org_id IS NOT NULL AND org_type IS NOT null
5573 AND
5574 ext_payee_id IN(
5575 SELECT ext_pmt_party_id FROM IBY_PMT_INSTR_USES_ALL WHERE
5576 PAYMENT_FLOW = 'DISBURSEMENTS'
5577 AND INSTRUMENT_TYPE = 'BANKACCOUNT'
5578 AND payment_function = 'PAYABLES_DISB'
5579 AND INSTRUMENT_ID = p_ext_bank_acct_id )
5580 AND MO_GLOBAL.CHECK_ACCESS(org_id) = 'Y'
5581 AND ROWNUM=1;
5582 EXCEPTION
5583 WHEN no_data_found THEN
5584 BEGIN
5585 SELECT org_id INTO l_org_id FROM iby_external_payees_all
5586 WHERE org_id IS NOT NULL AND org_type IS NOT null
5587 AND
5588 ext_payee_id IN(
5589 SELECT ext_pmt_party_id FROM IBY_PMT_INSTR_USES_ALL WHERE
5590 PAYMENT_FLOW = 'DISBURSEMENTS'
5591 AND INSTRUMENT_TYPE = 'BANKACCOUNT'
5592 AND payment_function = 'PAYABLES_DISB'
5593 AND INSTRUMENT_ID = p_ext_bank_acct_id )
5594 AND ROWNUM=1;
5595 EXCEPTION
5596 WHEN no_data_found THEN
5597 l_org_id := -1;
5598 WHEN others THEN
5599 -- This is not fatal error. Hence swallowing..
5600 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5601 print_debuginfo('Non Fatal Exceptionn ' || l_module_name);
5602 END IF;
5603 NULL;
5604 END;
5605 WHEN others THEN
5606 -- This is not fatal error. Hence swallowing..
5607 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5608 print_debuginfo('Non Fatal Exception ' || l_module_name);
5609 END IF;
5610 NULL;
5611 END;
5612 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5613 print_debuginfo('Exit ' || l_module_name);
5614 END IF;
5615 RETURN l_org_id;
5616 END find_assignment_OU;
5617
5618 /* Bug 14456128 Introduced procedure Uncipher_Bank_Acct_Number for
5619 decrypting Bank account number */
5620 FUNCTION Uncipher_Bank_Acct_Number(
5621 p_ext_bank_acct_id IN iby_ext_bank_accounts.ext_bank_account_id%type,
5622 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE )
5623 RETURN VARCHAR2
5624 IS
5625 l_api_name CONSTANT VARCHAR2(30) := 'Uncipher_Bank_Acct_Number';
5626 l_api_version CONSTANT NUMBER := 1.0;
5627 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
5628 l_sub_key iby_sys_security_subkeys.subkey_cipher_text%TYPE;
5629 l_bank_segment iby_security_segments.segment_cipher_text%TYPE;
5630 l_bank_acct_num iby_ext_bank_accounts.bank_account_num%TYPE;
5631 l_segment_id iby_ext_bank_accounts.BA_NUM_SEC_SEGMENT_ID%type;
5632 l_sub_key_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
5633 l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
5634 l_encoding iby_security_segments.encoding_scheme%TYPE;
5635 l_mask_option iby_ext_bank_accounts.ba_mask_setting%TYPE;
5636 l_unmask_len iby_ext_bank_accounts.ba_unmask_length%TYPE;
5637 l_bank_num iby_ext_bank_accounts.bank_account_num%TYPE;
5638 l_err_code varchar2(100):=null;
5639 CURSOR c_cipher_data(p_ext_bank_account_id NUMBER)
5640 IS
5641 SELECT ba.bank_account_num,
5642 ba.BA_NUM_SEC_SEGMENT_ID,
5643 subkeys.subkey_cipher_text,
5644 segments.segment_cipher_text,
5645 segments.encoding_scheme,
5646 ba.ba_mask_setting,
5647 ba.ba_unmask_length
5648 FROM iby_ext_bank_accounts ba,
5649 iby_sys_security_subkeys subkeys,
5650 iby_security_segments segments
5651 WHERE ba.ext_bank_account_id = p_ext_bank_account_id
5652 AND ba.ba_num_sec_segment_id = segments.sec_segment_id (+)
5653 AND segments.sec_subkey_id = subkeys.sec_subkey_id(+);
5654 BEGIN
5655
5656 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5657 print_debuginfo('Enter ' || l_module_name);
5658 END IF;
5659 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5660 print_debuginfo(l_module_name||': Validating Sys Key');
5661 END IF;
5662 IBY_SECURITY_PKG.Validate_Sys_Key(p_sys_key,l_err_code);
5663 IF (l_err_code IS NOT NULL) THEN
5664 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5665 print_debuginfo(l_module_name||': Sys key Validation Failed');
5666 END IF;
5667 fnd_message.set_name('IBY', l_err_code);
5668 fnd_msg_pub.add;
5669 return null;
5670 END IF;
5671
5672 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5673 print_debuginfo(l_module_name||': Sys key Validation Passed');
5674 END IF;
5675 OPEN c_cipher_data (p_ext_bank_acct_id);
5676 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5677 print_debuginfo('Before c_cipher_data');
5678 END IF;
5679 FETCH c_cipher_data
5680 INTO l_bank_acct_num,
5681 l_segment_id,
5682 l_sub_key_cipher,
5683 l_segment_cipher,
5684 l_encoding,
5685 l_mask_option,
5686 l_unmask_len;
5687 CLOSE c_cipher_data;
5688 IF (l_segment_id IS NULL) THEN
5689 RETURN l_bank_acct_num;
5690 END IF;
5691 IF (iby_utility_pvt.debug_mode_for_extract_enabled = 'Y') THEN
5692 RETURN l_bank_acct_num;
5693 END IF ;
5694 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5695 print_debuginfo('uncipher the subkey');
5696 END IF;
5697 -- uncipher the subkey
5698 l_sub_key := iby_security_pkg.get_sys_subkey(p_sys_key,l_sub_key_cipher);
5699 -- uncipher the segment
5700 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5701 print_debuginfo('uncipher the segment');
5702 END IF;
5703 l_bank_segment := dbms_obfuscation_toolkit.des3decrypt ( input => l_segment_cipher, KEY => l_sub_key, which => dbms_obfuscation_toolkit.ThreeKeyMode );
5704 l_bank_segment := IBY_SECURITY_PKG.PKCS5_UNPAD(l_bank_segment);
5705 l_bank_num := UTL_I18N.RAW_TO_CHAR(l_bank_segment,l_encoding);
5706
5707 IF (NOT l_bank_acct_num IS NULL) THEN
5708 IF (l_mask_option = iby_security_pkg.G_MASK_POSTFIX) THEN
5709 l_bank_num := l_bank_acct_num || l_bank_num;
5710 ELSIF (l_mask_option = iby_security_pkg.G_MASK_PREFIX) THEN
5711 l_bank_num := l_bank_num || l_bank_acct_num;
5712 END IF;
5713 END IF;
5714 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5715 print_debuginfo('Exit ' || l_module_name);
5716 END IF;
5717 RETURN l_bank_num;
5718
5719 END Uncipher_Bank_Acct_Number;
5720
5721 END iby_ext_bankacct_pub;