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