[Home] [Help]
PACKAGE BODY: APPS.CE_BANK_PUB
Source
1 PACKAGE BODY ce_bank_pub AS
2 /*$Header: ceextbab.pls 120.27.12020000.3 2012/09/21 20:54:21 ckansara ship $ */
3
4 l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
5 -- l_DEBUG varchar2(1) := 'Y';
6
7 -- 12557352: Added check_mandatory_param
8 /*=======================================================================+
9 | PRIVATE FUNCTION check_mandatory_param |
10 | Check that value has been set for mandatory parameters |
11 | |
12 | DESCRIPTION |
13 | Get country of the bank given the bank party_id. |
14 | |
15 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
16 | |
17 | MODIFICATION HISTORY |
18 | 18-May-2011 vnetan Created. |
19 +======================================================================*/
20 PROCEDURE check_mandatory_param (
21 p_param_name IN VARCHAR2,
22 p_param_value IN VARCHAR2
23 ) AS
24 BEGIN
25 IF (p_param_value IS NULL)
26 THEN
27 fnd_message.set_name('CE', 'CE_API_REQUIRED_PARAM');
28 fnd_message.set_token('PARAM', p_param_name);
29 fnd_msg_pub.add;
30 IF l_DEBUG in ('Y', 'C') THEN
31 cep_standard.debug(p_param_name|| ' is a required parameter.');
32 END IF;
33 RAISE fnd_api.g_exc_error;
34
35 END IF;
36 END check_mandatory_param;
37
38 /*=======================================================================+
39 | PRIVATE FUNCTION get_country |
40 | |
41 | DESCRIPTION |
42 | Get country of the bank given the bank party_id. |
43 | |
44 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
45 | |
46 | MODIFICATION HISTORY |
47 | 07-SEP-2004 Xin Wang Created. |
48 +=======================================================================*/
49 FUNCTION get_country (
50 p_bank_id IN NUMBER,
51 x_return_status IN OUT NOCOPY VARCHAR2
52 ) RETURN VARCHAR2 IS
53 CURSOR c_country IS
54 SELECT org.home_country
55 FROM hz_organization_profiles org
56 WHERE org.party_id = p_bank_id
57 AND SYSDATE between TRUNC(effective_start_date)
58 and NVL(TRUNC(effective_end_date), SYSDATE+1);
59 l_country VARCHAR2(60);
60 BEGIN
61 -- initialize API return status to success.
62 x_return_status := fnd_api.g_ret_sts_success;
63
64 OPEN c_country;
65 FETCH c_country INTO l_country;
66 IF c_country%NOTFOUND THEN
67 fnd_message.set_name('CE', 'CE_API_NO_BANK');
68 fnd_msg_pub.add;
69 x_return_status := fnd_api.g_ret_sts_error;
70 END IF;
71 CLOSE c_country;
72
73 RETURN NVL(l_country, 'NULL');
74 END get_country;
75
76
77 /*=======================================================================+
78 | PRIVATE PROCEDURE find_bank_info |
79 | |
80 | DESCRIPTION |
81 | Get the party_id and country of the bank given a bank_branch_id. |
82 | |
83 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
84 | |
85 | MODIFICATION HISTORY |
86 | 08-SEP-2004 Xin Wang Created. |
87 +=======================================================================*/
88 PROCEDURE find_bank_info (
89 p_branch_id IN NUMBER,
90 x_return_status IN OUT NOCOPY VARCHAR2,
91 x_bank_id OUT NOCOPY NUMBER,
92 x_country_code OUT NOCOPY VARCHAR2,
93 x_bank_name OUT NOCOPY VARCHAR2,
94 x_bank_number OUT NOCOPY VARCHAR2
95 ) IS
96 CURSOR c_bank IS
97 SELECT hz_bank.party_id AS bank_id,
98 hz_bankorg.home_country,
99 hz_bank.party_name,
100 hz_bankorg.bank_or_branch_number
101 FROM hz_parties hz_bank,
102 hz_organization_profiles hz_bankorg,
103 hz_parties hz_branch,
104 hz_relationships hz_rel,
105 hz_code_assignments hz_bankCA,
106 hz_code_assignments hz_branchCA
107 WHERE hz_branchCA.owner_table_name = 'HZ_PARTIES'
108 AND hz_branchCA.owner_table_id = hz_branch.party_id
109 AND hz_branchCA.class_category = 'BANK_INSTITUTION_TYPE'
110 AND hz_branchCA.class_code = 'BANK_BRANCH'
111 AND NVL(hz_branchCA.STATUS, 'A') = 'A'
112 AND hz_bankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
113 AND hz_bankCA.CLASS_CODE = 'BANK'
114 AND hz_bankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
115 AND hz_bankCA.OWNER_TABLE_ID = hz_bank.PARTY_ID
116 AND NVL(hz_bankCA.STATUS, 'A') = 'A'
117 AND hz_rel.OBJECT_ID = hz_bank.PARTY_ID
118 And hz_branch.PARTY_ID = hz_rel.SUBJECT_ID
119 And hz_rel.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
120 And hz_rel.RELATIONSHIP_CODE = 'BRANCH_OF'
121 And hz_rel.STATUS = 'A'
122 And hz_rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
123 And hz_rel.SUBJECT_TYPE = 'ORGANIZATION'
124 And hz_rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
125 And hz_rel.OBJECT_TYPE = 'ORGANIZATION'
126 AND hz_bank.party_id = hz_bankorg.party_id
127 AND SYSDATE between TRUNC(hz_bankorg.effective_start_date)
128 and NVL(TRUNC(hz_bankorg.effective_end_date), SYSDATE+1)
129 AND hz_branch.party_id = p_branch_id;
130 BEGIN
131 -- initialize API return status to success.
132 x_return_status := fnd_api.g_ret_sts_success;
133
134 OPEN c_bank;
135 FETCH c_bank INTO x_bank_id, x_country_code, x_bank_name, x_bank_number;
136 IF c_bank%NOTFOUND THEN
137 fnd_message.set_name('CE', 'CE_API_NO_BANK');
138 fnd_msg_pub.add;
139 x_return_status := fnd_api.g_ret_sts_error;
140 END IF;
141 CLOSE c_bank;
142
143 END find_bank_info;
144
145
146 /*=======================================================================+
147 | PUBLIC PROCEDURE create_bank |
148 | |
149 | DESCRIPTION |
150 | Create a bank as a TCA organization party. |
151 | |
152 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
153 | hz_bank_pub.create_bank |
154 | |
155 | ARGUMENTS |
156 | IN: |
157 | p_init_msg_list Initialize message stack if it is set to |
158 | FND_API.G_TRUE. Default is fnd_api.g_false
159 | p_country_code Country code of the bank. |
160 | p_bank_name Bank name. |
161 | p_bank_number Bank number. |
162 | p_alternate_bank_name Alternate bank name. |
163 | p_short_bank_name Short bank name. |
164 | p_description Description. |
165 | p_tax_payer_id Tax payer ID. |
166 | p_tax_registration_number Tax registration number |
167 | IN/OUT: |
168 | OUT: |
169 | x_bank_id Party ID for the bank. |
170 | x_return_status Return status after the call. The status can |
171 | be FND_API.G_RET_STS_SUCCESS (success), |
172 | fnd_api.g_ret_sts_error (error), |
173 | fnd_api.g_ret_sts_unexp_error (unexpected |
174 | error). |
175 | x_msg_count Number of messages in message stack. |
176 | x_msg_data Message text if x_msg_count is 1. |
177 | MODIFICATION HISTORY |
178 | 25-AUG-2004 Xin Wang Created. |
179 +=======================================================================*/
180 PROCEDURE create_bank (
181 p_init_msg_list IN VARCHAR2:= fnd_api.g_false,
182 p_country_code IN VARCHAR2,
183 p_bank_name IN VARCHAR2,
184 p_bank_number IN VARCHAR2 DEFAULT NULL,
185 p_alternate_bank_name IN VARCHAR2 DEFAULT NULL,
186 p_short_bank_name IN VARCHAR2 DEFAULT NULL,
187 p_description IN VARCHAR2 DEFAULT NULL,
188 p_tax_payer_id IN VARCHAR2 DEFAULT NULL,
189 p_tax_registration_number IN VARCHAR2 DEFAULT NULL,
190 p_attribute_category IN VARCHAR2 DEFAULT NULL,
191 p_attribute1 IN VARCHAR2 DEFAULT NULL,
192 p_attribute2 IN VARCHAR2 DEFAULT NULL,
193 p_attribute3 IN VARCHAR2 DEFAULT NULL,
194 p_attribute4 IN VARCHAR2 DEFAULT NULL,
195 p_attribute5 IN VARCHAR2 DEFAULT NULL,
196 p_attribute6 IN VARCHAR2 DEFAULT NULL,
197 p_attribute7 IN VARCHAR2 DEFAULT NULL,
198 p_attribute8 IN VARCHAR2 DEFAULT NULL,
199 p_attribute9 IN VARCHAR2 DEFAULT NULL,
200 p_attribute10 IN VARCHAR2 DEFAULT NULL,
201 p_attribute11 IN VARCHAR2 DEFAULT NULL,
202 p_attribute12 IN VARCHAR2 DEFAULT NULL,
203 p_attribute13 IN VARCHAR2 DEFAULT NULL,
204 p_attribute14 IN VARCHAR2 DEFAULT NULL,
205 p_attribute15 IN VARCHAR2 DEFAULT NULL,
206 p_attribute16 IN VARCHAR2 DEFAULT NULL,
207 p_attribute17 IN VARCHAR2 DEFAULT NULL,
208 p_attribute18 IN VARCHAR2 DEFAULT NULL,
209 p_attribute19 IN VARCHAR2 DEFAULT NULL,
210 p_attribute20 IN VARCHAR2 DEFAULT NULL,
211 p_attribute21 IN VARCHAR2 DEFAULT NULL,
212 p_attribute22 IN VARCHAR2 DEFAULT NULL,
213 p_attribute23 IN VARCHAR2 DEFAULT NULL,
214 p_attribute24 IN VARCHAR2 DEFAULT NULL,
215 x_bank_id OUT NOCOPY NUMBER,
216 x_return_status OUT NOCOPY VARCHAR2,
217 x_msg_count OUT NOCOPY NUMBER,
218 x_msg_data OUT NOCOPY VARCHAR2
219 ) IS
220 l_bank_rec hz_bank_pub.bank_rec_type;
221 l_org_rec hz_party_v2pub.organization_rec_type;
222 l_party_rec hz_party_v2pub.party_rec_type;
223 l_party_number VARCHAR2(30);
224 l_profile_id NUMBER(15);
225 l_code_assignment_id NUMBER(15);
226 l_bank_number VARCHAR2(30);
227 l_gen_party_num VARCHAR2(1);
228 BEGIN
229 SAVEPOINT create_bank;
230
231 IF l_DEBUG in ('Y', 'C') THEN
232 cep_standard.debug('>>CE_EXT_BANK_ACCT_PUB.create_bank.');
233 END IF;
234
235 -- initialize message list
236 IF fnd_api.to_boolean(p_init_msg_list) THEN
237 fnd_msg_pub.initialize;
238 END IF;
239
240 -- initialize API return status to success.
241 x_return_status := fnd_api.g_ret_sts_success;
242
243 -- first check all required params
244 -- 12557352: Modified check by calling check_mandatory_param
245 check_mandatory_param('p_country_code',p_country_code);
246 check_mandatory_param('p_bank_name',p_bank_name);
247
248 -- country specific validation API call here
249 ce_validate_bankinfo.ce_validate_bank(p_country_code,
250 p_bank_number,
251 p_bank_name,
252 p_alternate_bank_name,
253 p_tax_payer_id,
254 null, -- bank_id
255 FND_API.G_FALSE, -- do not re-initialize msg stack
256 x_msg_count,
257 x_msg_data,
258 l_bank_number, -- reformated bank number
259 x_return_status);
260
261 -- raise an exception if country specific validations fail
262 IF x_return_status <> fnd_api.g_ret_sts_success THEN
263 RAISE fnd_api.g_exc_error;
264 END IF;
265
266 l_party_rec.attribute_category := p_attribute_category;
267 l_party_rec.attribute1 := p_attribute1;
268 l_party_rec.attribute2 := p_attribute2;
269 l_party_rec.attribute3 := p_attribute3;
270 l_party_rec.attribute4 := p_attribute4;
271 l_party_rec.attribute5 := p_attribute5;
272 l_party_rec.attribute6 := p_attribute6;
273 l_party_rec.attribute7 := p_attribute7;
274 l_party_rec.attribute8 := p_attribute8;
275 l_party_rec.attribute9 := p_attribute9;
276 l_party_rec.attribute10 := p_attribute10;
277 l_party_rec.attribute11 := p_attribute11;
278 l_party_rec.attribute12 := p_attribute12;
279 l_party_rec.attribute13 := p_attribute13;
280 l_party_rec.attribute14 := p_attribute14;
281 l_party_rec.attribute15 := p_attribute15;
282 l_party_rec.attribute16 := p_attribute16;
283 l_party_rec.attribute17 := p_attribute17;
284 l_party_rec.attribute18 := p_attribute18;
285 l_party_rec.attribute19 := p_attribute19;
286 l_party_rec.attribute20 := p_attribute20;
287 l_party_rec.attribute21 := p_attribute21;
288 l_party_rec.attribute22 := p_attribute22;
289 l_party_rec.attribute23 := p_attribute23;
290 l_party_rec.attribute24 := p_attribute24;
291
292 l_org_rec.organization_name := p_bank_name;
293 l_org_rec.organization_name_phonetic := p_alternate_bank_name;
294 l_org_rec.known_as := p_short_bank_name;
295 l_org_rec.mission_statement := p_description;
296 l_org_rec.jgzz_fiscal_code := p_tax_payer_id;
297 l_org_rec.tax_reference := p_tax_registration_number;
298 l_org_rec.created_by_module := 'CE';
299 l_org_rec.party_rec := l_party_rec;
300
301 l_bank_rec.bank_or_branch_number := l_bank_number;
302 l_bank_rec.country := p_country_code;
303 l_bank_rec.institution_type := 'BANK';
304 l_bank_rec.organization_rec := l_org_rec;
305
306 l_gen_party_num := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
307 if (l_gen_party_num = 'N') then
308 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', 'Y');
309 end if;
310
311 hz_bank_pub.create_bank(fnd_api.g_false, l_bank_rec, x_bank_id, l_party_number,
312 l_profile_id, l_code_assignment_id,
313 x_return_status, x_msg_count, x_msg_data);
314
315 if (l_gen_party_num = 'N') then
316 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', 'N');
317 end if;
318
319 -- raise an exception if error creating bank
320 IF x_return_status <> fnd_api.g_ret_sts_success THEN
321 RAISE fnd_api.g_exc_error;
322 END IF;
323
324 -- get message count and if count is 1, get message info.
325 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
326 p_count => x_msg_count,
327 p_data => x_msg_data);
328
329 IF l_DEBUG in ('Y', 'C') THEN
330 cep_standard.debug('<<CE_EXT_BANK_ACCT_PUB.create_bank.');
331 END IF;
332
333 EXCEPTION
334 WHEN fnd_api.g_exc_error THEN
335 ROLLBACK TO create_bank;
336 x_return_status := fnd_api.g_ret_sts_error;
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 IF l_DEBUG in ('Y', 'C') THEN
342 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
343 END IF;
344
345 WHEN fnd_api.g_exc_unexpected_error THEN
346 ROLLBACK TO create_bank;
347 x_return_status := fnd_api.g_ret_sts_unexp_error;
348 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
349 p_count => x_msg_count,
350 p_data => x_msg_data);
351
352 IF l_DEBUG in ('Y', 'C') THEN
353 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
354 END IF;
355
356 WHEN OTHERS THEN
357 ROLLBACK TO create_bank;
358 x_return_status := fnd_api.g_ret_sts_unexp_error;
359 fnd_message.set_name('CE', 'CE_API_OTHERS_EXCEP');
360 fnd_message.set_token('ERROR',SQLERRM);
361 fnd_msg_pub.add;
362 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
363 p_count => x_msg_count,
364 p_data => x_msg_data);
365
366 IF l_DEBUG in ('Y', 'C') THEN
367 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
368 END IF;
369
370 END create_bank;
371
372
373 /*=======================================================================+
374 | PUBLIC PROCEDURE update_bank |
375 | |
376 | DESCRIPTION |
377 | Update a bank organization. |
378 | |
379 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
380 | hz_bank_pub.update_bank |
381 | |
382 | ARGUMENTS |
383 | IN: |
384 | p_init_msg_list Initialize message stack if it is set to |
385 | FND_API.G_TRUE. Default is fnd_api.g_false
386 | p_bank_id Party ID of the bank to be updated. |
387 | p_bank_name Bank name. |
388 | p_bank_number Bank number. |
389 | p_alternate_bank_name Alternate bank name. |
390 | p_short_bank_name Short bank name. |
391 | p_description Description. |
392 | p_tax_payer_id Tax payer ID. |
393 | p_tax_registration_number Tax registration number |
394 | IN/OUT: |
395 | p_object_version_number Current object version number for the bank|
396 | OUT: |
397 | x_return_status Return status after the call. The status can |
398 | be FND_API.G_RET_STS_SUCCESS (success), |
399 | fnd_api.g_ret_sts_error (error), |
400 | fnd_api.g_ret_sts_unexp_error (unexpected |
401 | error). |
402 | x_msg_count Number of messages in message stack. |
403 | x_msg_data Message text if x_msg_count is 1. |
404 | MODIFICATION HISTORY |
405 | 25-AUG-2004 Xin Wang Created. |
406 | 05-MAR-2009 TALAPATI Added a new parameter p_country_validate |
407 | to enable or disable the country specific |
408 | validation.(Bug #8286747) |
409 +=======================================================================*/
410 PROCEDURE update_bank (
411 p_init_msg_list IN VARCHAR2:= fnd_api.g_false,
412 p_bank_id IN NUMBER,
413 p_bank_name IN VARCHAR2,
414 p_bank_number IN VARCHAR2 DEFAULT NULL,
415 p_alternate_bank_name IN VARCHAR2 DEFAULT NULL,
416 p_short_bank_name IN VARCHAR2 DEFAULT NULL,
417 p_description IN VARCHAR2 DEFAULT NULL,
418 p_tax_payer_id IN VARCHAR2 DEFAULT NULL,
419 p_tax_registration_number IN VARCHAR2 DEFAULT NULL,
420 p_attribute_category IN VARCHAR2 DEFAULT NULL,
421 p_attribute1 IN VARCHAR2 DEFAULT NULL,
422 p_attribute2 IN VARCHAR2 DEFAULT NULL,
423 p_attribute3 IN VARCHAR2 DEFAULT NULL,
424 p_attribute4 IN VARCHAR2 DEFAULT NULL,
425 p_attribute5 IN VARCHAR2 DEFAULT NULL,
426 p_attribute6 IN VARCHAR2 DEFAULT NULL,
427 p_attribute7 IN VARCHAR2 DEFAULT NULL,
428 p_attribute8 IN VARCHAR2 DEFAULT NULL,
429 p_attribute9 IN VARCHAR2 DEFAULT NULL,
430 p_attribute10 IN VARCHAR2 DEFAULT NULL,
431 p_attribute11 IN VARCHAR2 DEFAULT NULL,
432 p_attribute12 IN VARCHAR2 DEFAULT NULL,
433 p_attribute13 IN VARCHAR2 DEFAULT NULL,
434 p_attribute14 IN VARCHAR2 DEFAULT NULL,
435 p_attribute15 IN VARCHAR2 DEFAULT NULL,
436 p_attribute16 IN VARCHAR2 DEFAULT NULL,
437 p_attribute17 IN VARCHAR2 DEFAULT NULL,
438 p_attribute18 IN VARCHAR2 DEFAULT NULL,
439 p_attribute19 IN VARCHAR2 DEFAULT NULL,
440 p_attribute20 IN VARCHAR2 DEFAULT NULL,
441 p_attribute21 IN VARCHAR2 DEFAULT NULL,
442 p_attribute22 IN VARCHAR2 DEFAULT NULL,
443 p_attribute23 IN VARCHAR2 DEFAULT NULL,
444 p_attribute24 IN VARCHAR2 DEFAULT NULL,
445 p_country_validate IN VARCHAR2 DEFAULT 'Y',
446 p_object_version_number IN OUT NOCOPY NUMBER,
447 x_return_status OUT NOCOPY VARCHAR2,
448 x_msg_count OUT NOCOPY NUMBER,
449 x_msg_data OUT NOCOPY VARCHAR2
450 ) IS
451 l_bank_rec hz_bank_pub.bank_rec_type;
452 l_org_rec hz_party_v2pub.organization_rec_type;
453 l_party_rec hz_party_v2pub.party_rec_type;
454 l_profile_id NUMBER(15);
455 l_ca_object_version_number NUMBER(15);
456 l_country_code VARCHAR2(60);
457 l_bank_number VARCHAR2(30);
458 BEGIN
459 SAVEPOINT update_bank;
460
461 IF l_DEBUG in ('Y', 'C') THEN
462 cep_standard.debug('>>CE_EXT_BANK_ACCT_PUB.update_bank.');
463 END IF;
464
465 -- initialize message list
466 IF fnd_api.to_boolean(p_init_msg_list) THEN
467 fnd_msg_pub.initialize;
468 END IF;
469
470 -- initialize API return status to success.
471 x_return_status := fnd_api.g_ret_sts_success;
472
473 -- first check all required params
474 -- 12557352: Modified check by calling check_mandatory_param
475 check_mandatory_param('p_bank_id',p_bank_id);
476 check_mandatory_param('p_bank_name',p_bank_name);
477
478 l_country_code := get_country(p_bank_id, x_return_status);
479
480 -- raise an exception if bank is not found
481 IF x_return_status <> fnd_api.g_ret_sts_success THEN
482 RAISE fnd_api.g_exc_error;
483 END IF;
484
485 -- country specific validation API call here
486 ce_validate_bankinfo.ce_validate_bank(l_country_code,
487 p_bank_number,
488 p_bank_name,
489 p_alternate_bank_name,
490 p_tax_payer_id,
491 p_bank_id, -- bank_id
492 FND_API.G_FALSE, -- do not re-initialize msg stack
493 x_msg_count,
494 x_msg_data,
495 l_bank_number, -- reformated bank number
496 x_return_status);
497
498 -- Bug #8286747 The country specific validation errors are reported only if p_country_validate is set to 'Y'
499 -- raise an exception if country specific validations fail
500 IF x_return_status <> fnd_api.g_ret_sts_success and upper(p_country_validate) ='Y' THEN
501 RAISE fnd_api.g_exc_error;
502 END IF;
503
504 l_party_rec.party_id := p_bank_id;
505 l_party_rec.attribute_category := p_attribute_category;
506 l_party_rec.attribute1 := p_attribute1;
507 l_party_rec.attribute2 := p_attribute2;
508 l_party_rec.attribute3 := p_attribute3;
509 l_party_rec.attribute4 := p_attribute4;
510 l_party_rec.attribute5 := p_attribute5;
511 l_party_rec.attribute6 := p_attribute6;
512 l_party_rec.attribute7 := p_attribute7;
513 l_party_rec.attribute8 := p_attribute8;
514 l_party_rec.attribute9 := p_attribute9;
515 l_party_rec.attribute10 := p_attribute10;
516 l_party_rec.attribute11 := p_attribute11;
517 l_party_rec.attribute12 := p_attribute12;
518 l_party_rec.attribute13 := p_attribute13;
519 l_party_rec.attribute14 := p_attribute14;
520 l_party_rec.attribute15 := p_attribute15;
521 l_party_rec.attribute16 := p_attribute16;
522 l_party_rec.attribute17 := p_attribute17;
523 l_party_rec.attribute18 := p_attribute18;
524 l_party_rec.attribute19 := p_attribute19;
525 l_party_rec.attribute20 := p_attribute20;
526 l_party_rec.attribute21 := p_attribute21;
527 l_party_rec.attribute22 := p_attribute22;
528 l_party_rec.attribute23 := p_attribute23;
529 l_party_rec.attribute24 := p_attribute24;
530
531 l_org_rec.organization_name := p_bank_name;
532 l_org_rec.organization_name_phonetic := p_alternate_bank_name;
533 l_org_rec.known_as := p_short_bank_name;
534 l_org_rec.mission_statement := p_description;
535 l_org_rec.jgzz_fiscal_code := p_tax_payer_id;
536 l_org_rec.tax_reference := p_tax_registration_number;
537 -- 8400543: created_by_module should not be udpated.
538 -- l_org_rec.created_by_module := 'CE';
539 l_org_rec.party_rec := l_party_rec;
540
541 l_bank_rec.bank_or_branch_number := l_bank_number;
542 l_bank_rec.organization_rec := l_org_rec;
543
544 -- find the object_version_number of the code_assignment for the 'BANK' institution type
545 SELECT object_version_number
546 INTO l_ca_object_version_number
547 FROM hz_code_assignments
548 WHERE class_category= 'BANK_INSTITUTION_TYPE'
549 AND owner_table_name = 'HZ_PARTIES'
550 AND owner_table_id = p_bank_id
551 AND status = 'A';
552
553 hz_bank_pub.update_bank(fnd_api.g_false, l_bank_rec,
554 p_object_version_number, l_ca_object_version_number,
555 l_profile_id, x_return_status, x_msg_count, x_msg_data);
556
557 -- raise an exception if error updating a bank
558 IF x_return_status <> fnd_api.g_ret_sts_success THEN
559 RAISE fnd_api.g_exc_error;
560 END IF;
561
562 -- get message count and if count is 1, get message info.
563 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
564 p_count => x_msg_count,
565 p_data => x_msg_data);
566
567 IF l_DEBUG in ('Y', 'C') THEN
568 cep_standard.debug('<<CE_EXT_BANK_ACCT_PUB.update_bank.');
569 END IF;
570
571 EXCEPTION
572 WHEN fnd_api.g_exc_error THEN
573 ROLLBACK TO update_bank;
574 x_return_status := fnd_api.g_ret_sts_error;
575 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
576 p_count => x_msg_count,
577 p_data => x_msg_data);
578
579 IF l_DEBUG in ('Y', 'C') THEN
580 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
581 END IF;
582
583 WHEN fnd_api.g_exc_unexpected_error THEN
584 ROLLBACK TO update_bank;
585 x_return_status := fnd_api.g_ret_sts_unexp_error;
586 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
587 p_count => x_msg_count,
588 p_data => x_msg_data);
589
590 IF l_DEBUG in ('Y', 'C') THEN
591 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
592 END IF;
593
594 WHEN OTHERS THEN
595 ROLLBACK TO update_bank;
596 x_return_status := fnd_api.g_ret_sts_unexp_error;
597 fnd_message.set_name('CE', 'CE_API_OTHERS_EXCEP');
598 fnd_message.set_token('ERROR',SQLERRM);
599 fnd_msg_pub.add;
600 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
601 p_count => x_msg_count,
602 p_data => x_msg_data);
603
604 IF l_DEBUG in ('Y', 'C') THEN
605 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
606 END IF;
607
608 END update_bank;
609
610
611 /*=======================================================================+
612 | PUBLIC PROCEDURE set_bank_end_date |
613 | |
614 | DESCRIPTION |
615 | Set the end date of a bank. |
616 | |
617 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
618 | hz_bank_pub.update_bank |
619 | |
620 | ARGUMENTS |
621 | IN: |
622 | p_init_msg_list Initialize message stack if it is set to |
623 | FND_API.G_TRUE. Default is fnd_api.g_false
624 | p_bank_id Party ID of the bank to be updated. |
625 | p_end_date End date of the bank. |
626 | IN/OUT: |
627 | p_object_version_number Current object version number for the code|
628 | assignment for the bank institution type. |
629 | OUT: |
630 | x_return_status Return status after the call. The status can |
631 | be FND_API.G_RET_STS_SUCCESS (success), |
632 | fnd_api.g_ret_sts_error (error), |
633 | fnd_api.g_ret_sts_unexp_error (unexpected |
634 | error). |
635 | x_msg_count Number of messages in message stack. |
636 | x_msg_data Message text if x_msg_count is 1. |
637 | MODIFICATION HISTORY |
638 | 25-AUG-2004 Xin Wang Created. |
639 +=======================================================================*/
640 PROCEDURE set_bank_end_date (
641 p_init_msg_list IN VARCHAR2:= fnd_api.g_false,
642 p_bank_id IN NUMBER,
643 p_end_date IN DATE,
644 p_object_version_number IN OUT NOCOPY NUMBER,
645 x_return_status OUT NOCOPY VARCHAR2,
646 x_msg_count OUT NOCOPY NUMBER,
647 x_msg_data OUT NOCOPY VARCHAR2
648 ) IS
649 CURSOR c_bank IS
650 SELECT end_date_active
651 FROM hz_code_assignments
652 WHERE owner_table_name = 'HZ_PARTIES'
653 AND owner_table_id = p_bank_id
654 AND class_category = 'BANK_INSTITUTION_TYPE'
655 AND class_code = 'BANK';
656 CURSOR c_branch_id IS
657 SELECT subject_id
658 FROM hz_relationships
659 WHERE relationship_type = 'BANK_AND_BRANCH'
660 AND relationship_code = 'BRANCH_OF'
661 AND subject_table_name = 'HZ_PARTIES'
662 AND subject_type = 'ORGANIZATION'
663 AND object_table_name = 'HZ_PARTIES'
664 AND object_type = 'ORGANIZATION'
665 AND object_id = p_bank_id;
666 CURSOR c_branch (p_branch_id NUMBER) IS
667 SELECT end_date_active, object_version_number
668 FROM hz_code_assignments
669 WHERE owner_table_name = 'HZ_PARTIES'
670 AND owner_table_id = p_branch_id
671 AND class_category = 'BANK_INSTITUTION_TYPE'
672 AND class_code = 'BANK_BRANCH';
673 CURSOR c_bank_ovn IS
674 SELECT object_version_number
675 FROM hz_parties
676 WHERE party_id = p_bank_id;
677
678 l_bank_end DATE;
679 l_branch_end DATE;
680 l_bank_ovn NUMBER(15);
681 l_branch_ovn NUMBER(15);
682 l_bank_rec hz_bank_pub.bank_rec_type;
683 l_org_rec hz_party_v2pub.organization_rec_type;
684 l_party_rec hz_party_v2pub.party_rec_type;
685 l_profile_id NUMBER(15);
686 BEGIN
687 SAVEPOINT set_bank_end_date;
688
689 IF l_DEBUG in ('Y', 'C') THEN
690 cep_standard.debug('>>CE_EXT_BANK_ACCT_PUB.set_bank_end_date.');
691 END IF;
692
693 -- initialize message list
694 IF fnd_api.to_boolean(p_init_msg_list) THEN
695 fnd_msg_pub.initialize;
696 END IF;
697
698 -- initialize API return status to success.
699 x_return_status := fnd_api.g_ret_sts_success;
700
701 -- first check all required params
702 -- 12557352: Modified check by calling check_mandatory_param
703 check_mandatory_param('p_bank_id',p_bank_id);
704 check_mandatory_param('p_object_version_number',p_object_version_number);
705
706 -- if new end date is earlier than its old end date,
707 -- and is earlier than its children's end date
708 -- set children's end dates.
709 OPEN c_bank;
710 FETCH c_bank INTO l_bank_end;
711 IF c_bank%NOTFOUND THEN
712 fnd_message.set_name('CE', 'CE_API_NO_BANK');
713 fnd_msg_pub.add;
714 x_return_status := fnd_api.g_ret_sts_error;
715 CLOSE c_bank;
716 RAISE fnd_api.g_exc_error;
717 END IF;
718 CLOSE c_bank;
719
720 IF p_end_date IS NOT NULL AND p_end_date < NVL(l_bank_end, p_end_date+1) THEN
721 FOR branch_id_rec IN c_branch_id LOOP
722 OPEN c_branch(branch_id_rec.subject_id);
723 FETCH c_branch INTO l_branch_end, l_branch_ovn;
724 IF c_branch%NOTFOUND THEN
725 fnd_message.set_name('CE', 'CE_API_NO_BRANCH');
726 fnd_msg_pub.add;
727 x_return_status := fnd_api.g_ret_sts_error;
728 CLOSE c_branch;
729 RAISE fnd_api.g_exc_error;
730 END IF;
731 CLOSE c_branch;
732
733 IF p_end_date < NVL(l_branch_end, p_end_date+1) THEN
734 set_bank_branch_end_date (fnd_api.g_false,
735 branch_id_rec.subject_id,
736 p_end_date,
737 l_branch_ovn,
738 x_return_status,
739 x_msg_count,
740 x_msg_data);
741 IF x_return_status <> fnd_api.g_ret_sts_success THEN
742 RAISE fnd_api.g_exc_error;
743 END IF;
744 END IF;
745 END LOOP;
746 END IF;
747
748 -- update bank's end date
749 l_party_rec.party_id := p_bank_id;
750 l_org_rec.party_rec := l_party_rec;
751 l_bank_rec.organization_rec := l_org_rec;
752 l_bank_rec.inactive_date := p_end_date;
753
754 OPEN c_bank_ovn;
755 FETCH c_bank_ovn INTO l_bank_ovn;
756 IF c_bank_ovn%NOTFOUND THEN
757 fnd_message.set_name('CE', 'CE_API_NO_BANK');
758 fnd_msg_pub.add;
759 x_return_status := fnd_api.g_ret_sts_error;
760 CLOSE c_bank_ovn;
761 RAISE fnd_api.g_exc_error;
762 END IF;
763 CLOSE c_bank_ovn;
764
765 hz_bank_pub.update_bank(fnd_api.g_false, l_bank_rec,
766 l_bank_ovn, p_object_version_number,
767 l_profile_id, x_return_status, x_msg_count, x_msg_data);
768
769 IF x_return_status <> fnd_api.g_ret_sts_success THEN
770 RAISE fnd_api.g_exc_error;
771 END IF;
772
773 -- get message count and if count is 1, get message info.
774 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
775 p_count => x_msg_count,
776 p_data => x_msg_data);
777
778 IF l_DEBUG in ('Y', 'C') THEN
779 cep_standard.debug('<<CE_EXT_BANK_ACCT_PUB.set_bank_end_date.');
780 END IF;
781
782 EXCEPTION
783 WHEN fnd_api.g_exc_error THEN
784 ROLLBACK TO set_bank_end_date;
785 x_return_status := fnd_api.g_ret_sts_error;
786 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
787 p_count => x_msg_count,
788 p_data => x_msg_data);
789
790 IF l_DEBUG in ('Y', 'C') THEN
791 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
792 END IF;
793
794 WHEN fnd_api.g_exc_unexpected_error THEN
795 ROLLBACK TO set_bank_end_date;
796 x_return_status := fnd_api.g_ret_sts_unexp_error;
797 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
798 p_count => x_msg_count,
799 p_data => x_msg_data);
800
801 IF l_DEBUG in ('Y', 'C') THEN
802 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
803 END IF;
804
805 WHEN OTHERS THEN
806 ROLLBACK TO set_bank_end_date;
807 x_return_status := fnd_api.g_ret_sts_unexp_error;
808 fnd_message.set_name('CE', 'CE_API_OTHERS_EXCEP');
809 fnd_message.set_token('ERROR',SQLERRM);
810 fnd_msg_pub.add;
811 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
812 p_count => x_msg_count,
813 p_data => x_msg_data);
814
815 IF l_DEBUG in ('Y', 'C') THEN
816 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
817 END IF;
818
819 END set_bank_end_date;
820
821 /*=======================================================================+
822 | PUBLIC PROCEDURE check_bank_exist |
823 | |
824 | DESCRIPTION |
825 | Check whether a bank already exists, if so, return the bank ID. |
826 | |
827 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
828 | |
829 | ARGUMENTS |
830 | IN: |
831 | p_country_code Country code. |
832 | p_bank_name Bank name. |
833 | p_bank_number Bank number. |
834 | IN/OUT: |
835 | OUT: |
836 | x_bank_id Bank Party ID if bank exists, |
837 | null if bank does not exist. |
838 | x_end_date End date of the bank. |
839 | |
840 | MODIFICATION HISTORY |
841 | 25-AUG-2004 Xin Wang Created. |
842 +=======================================================================*/
843 PROCEDURE check_bank_exist(
844 p_country_code IN VARCHAR2,
845 p_bank_name IN VARCHAR2,
846 p_bank_number IN VARCHAR2,
847 x_bank_id OUT NOCOPY NUMBER,
848 x_end_date OUT NOCOPY DATE
849 ) IS
850 CURSOR c_bank_name IS
851 SELECT hz_hp.party_id,
852 hz_ca.end_date_active
853 FROM hz_parties hz_hp,
854 hz_organization_profiles hz_org,
855 hz_code_assignments hz_ca
856 WHERE hz_ca.owner_table_id = hz_hp.party_id
857 AND hz_hp.party_type = 'ORGANIZATION' -- Bug 8333484
858 AND NVL(hz_hp.status, 'A') = 'A' -- Bug 8333484
859 AND hz_ca.owner_table_name = 'HZ_PARTIES'
860 AND hz_ca.class_category = 'BANK_INSTITUTION_TYPE'
861 AND hz_ca.class_code = 'BANK'
862 AND hz_hp.PARTY_ID = hz_org.PARTY_ID
863 AND SYSDATE between TRUNC(hz_org.effective_start_date)
864 and NVL(TRUNC(hz_org.effective_end_date), SYSDATE+1)
865 AND hz_org.home_country = p_country_code
866 AND upper(hz_hp.party_name) = upper(p_bank_name);
867
868 CURSOR c_bank_num IS
869 SELECT hz_hp.party_id,
870 hz_ca.end_date_active
871 FROM hz_parties hz_hp,
872 hz_organization_profiles hz_hop,
873 hz_code_assignments hz_ca
874 WHERE hz_hp.party_id = hz_hop.party_id
875 AND hz_hp.party_type = 'ORGANIZATION' -- Bug 8333484
876 AND NVL(hz_hp.status, 'A') = 'A' -- Bug 8333484
877 AND SYSDATE between TRUNC(hz_hop.effective_start_date)
878 and NVL(TRUNC(hz_hop.effective_end_date), SYSDATE+1)
879 AND hz_ca.owner_table_id = hz_hp.party_id
880 AND hz_ca.owner_table_name = 'HZ_PARTIES'
881 AND hz_ca.class_category = 'BANK_INSTITUTION_TYPE'
882 AND hz_ca.class_code = 'BANK'
883 AND hz_hop.home_country = p_country_code -- Bug 8992915
884 AND hz_hop.bank_or_branch_number = p_bank_number;
885
886 BEGIN
887 IF l_DEBUG in ('Y', 'C') THEN
888 cep_standard.debug('>>CE_EXT_BANK_ACCT_PUB.check_bank_exist.');
889 END IF;
890
891 IF p_bank_name IS NOT NULL THEN
892 OPEN c_bank_name;
893 FETCH c_bank_name INTO x_bank_id, x_end_date;
894 IF c_bank_name%NOTFOUND THEN
895 x_bank_id := null;
896 x_end_date := null;
897 END IF;
898 CLOSE c_bank_name;
899 ELSIF p_bank_number IS NOT NULL THEN
900 OPEN c_bank_num;
901 FETCH c_bank_num INTO x_bank_id, x_end_date;
902 IF c_bank_num%NOTFOUND THEN
903 x_bank_id := null;
904 x_end_date := null;
905 END IF;
906 CLOSE c_bank_num;
907 ELSE
908 x_bank_id := null;
909 x_end_date := null;
910 END IF;
911
912 IF l_DEBUG in ('Y', 'C') THEN
913 cep_standard.debug('<<CE_EXT_BANK_ACCT_PUB.check_bank_exist.');
914 END IF;
915
916 EXCEPTION
917 WHEN OTHERS THEN
918 cep_standard.sql_error('CE_EXT_BANK_ACCT_PUB.check_bank_exist', sqlcode, sqlerrm);
919
920 END check_bank_exist;
921
922
923 /*=======================================================================+
924 | PUBLIC PROCEDURE create_bank_branch |
925 | |
926 | DESCRIPTION |
927 | Create a bank branch as a TCA organization party. |
928 | |
929 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
930 | hz_bank_pub.create_bank_branch |
931 | |
932 | ARGUMENTS |
933 | IN: |
934 | p_init_msg_list Initialize message stack if it is set to |
935 | FND_API.G_TRUE. Default is fnd_api.g_false
936 | p_bank_id Party ID of the bank that the branch |
937 | belongs. |
938 | p_branch_name Bank branch name. |
939 | p_branch_number Bank branch number. |
940 | p_branch_type Bank branch type. |
941 | p_alternate_branch_name Alternate bank branch name. |
942 | p_description Description. |
943 | p_bic BIC (Bank Identification Code). |
944 | p_eft_number EFT number. |
945 | p_rfc_identifier Regional Finance Center Identifier. |
946 | IN/OUT: |
947 | OUT: |
948 | x_branch_id Party ID for the bank branch. |
949 | x_return_status Return status after the call. The status can |
950 | be FND_API.G_RET_STS_SUCCESS (success), |
951 | fnd_api.g_ret_sts_error (error), |
952 | fnd_api.g_ret_sts_unexp_error (unexpected |
953 | error). |
954 | x_msg_count Number of messages in message stack. |
955 | x_msg_data Message text if x_msg_count is 1. |
956 | MODIFICATION HISTORY |
957 | 25-AUG-2004 Xin Wang Created. |
958 +=======================================================================*/
959 PROCEDURE create_bank_branch (
960 p_init_msg_list IN VARCHAR2:= fnd_api.g_false,
961 p_bank_id IN NUMBER,
962 p_branch_name IN VARCHAR2,
963 p_branch_number IN VARCHAR2 DEFAULT NULL,
964 p_branch_type IN VARCHAR2 DEFAULT NULL,
965 p_alternate_branch_name IN VARCHAR2 DEFAULT NULL,
966 p_description IN VARCHAR2 DEFAULT NULL,
967 p_bic IN VARCHAR2 DEFAULT NULL,
968 p_eft_number IN VARCHAR2 DEFAULT NULL,
969 p_rfc_identifier IN VARCHAR2 DEFAULT NULL,
970 p_attribute_category IN VARCHAR2 DEFAULT NULL,
971 p_attribute1 IN VARCHAR2 DEFAULT NULL,
972 p_attribute2 IN VARCHAR2 DEFAULT NULL,
973 p_attribute3 IN VARCHAR2 DEFAULT NULL,
974 p_attribute4 IN VARCHAR2 DEFAULT NULL,
975 p_attribute5 IN VARCHAR2 DEFAULT NULL,
976 p_attribute6 IN VARCHAR2 DEFAULT NULL,
977 p_attribute7 IN VARCHAR2 DEFAULT NULL,
978 p_attribute8 IN VARCHAR2 DEFAULT NULL,
979 p_attribute9 IN VARCHAR2 DEFAULT NULL,
980 p_attribute10 IN VARCHAR2 DEFAULT NULL,
981 p_attribute11 IN VARCHAR2 DEFAULT NULL,
982 p_attribute12 IN VARCHAR2 DEFAULT NULL,
983 p_attribute13 IN VARCHAR2 DEFAULT NULL,
984 p_attribute14 IN VARCHAR2 DEFAULT NULL,
985 p_attribute15 IN VARCHAR2 DEFAULT NULL,
986 p_attribute16 IN VARCHAR2 DEFAULT NULL,
987 p_attribute17 IN VARCHAR2 DEFAULT NULL,
988 p_attribute18 IN VARCHAR2 DEFAULT NULL,
989 p_attribute19 IN VARCHAR2 DEFAULT NULL,
990 p_attribute20 IN VARCHAR2 DEFAULT NULL,
991 p_attribute21 IN VARCHAR2 DEFAULT NULL,
992 p_attribute22 IN VARCHAR2 DEFAULT NULL,
993 p_attribute23 IN VARCHAR2 DEFAULT NULL,
994 p_attribute24 IN VARCHAR2 DEFAULT NULL,
995 x_branch_id OUT NOCOPY NUMBER,
996 x_return_status OUT NOCOPY VARCHAR2,
997 x_msg_count OUT NOCOPY NUMBER,
998 x_msg_data OUT NOCOPY VARCHAR2
999 ) IS
1000 CURSOR c_bank IS
1001 SELECT hz_p.party_name, hz_org.bank_or_branch_number
1002 FROM hz_parties hz_p,
1003 hz_organization_profiles hz_org
1004 WHERE hz_p.party_id = hz_org.party_id
1005 AND SYSDATE between TRUNC(hz_org.effective_start_date)
1006 and NVL(TRUNC(hz_org.effective_end_date), SYSDATE+1)
1007 AND hz_p.party_id = p_bank_id;
1008
1009 l_branch_rec hz_bank_pub.bank_rec_type;
1010 l_org_rec hz_party_v2pub.organization_rec_type;
1011 l_party_rec hz_party_v2pub.party_rec_type;
1012 l_party_number VARCHAR2(30);
1013 l_profile_id NUMBER(15);
1014 l_rel_id NUMBER(15);
1015 l_rel_party_id NUMBER(15);
1016 l_rel_party_number NUMBER(15);
1017 l_bch_code_assignment_id NUMBER(15);
1018 l_typ_code_assignment_id NUMBER(15);
1019 l_rfc_code_assignment_id NUMBER(15);
1020 l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
1021 l_eft_rec hz_contact_point_v2pub.eft_rec_type;
1022 l_contact_point_id NUMBER(15);
1023 l_country VARCHAR2(60);
1024 l_branch_number VARCHAR2(30);
1025 l_bank_name VARCHAR2(360);
1026 l_bank_number VARCHAR2(30);
1027 l_gen_party_num VARCHAR2(1);
1028 BEGIN
1029 SAVEPOINT create_bank_branch;
1030
1031 IF l_DEBUG in ('Y', 'C') THEN
1032 cep_standard.debug('>>CE_EXT_BANK_ACCT_PUB.create_bank_branch.');
1033 END IF;
1034
1035 -- initialize message list
1036 IF fnd_api.to_boolean(p_init_msg_list) THEN
1037 fnd_msg_pub.initialize;
1038 END IF;
1039
1040 -- initialize API return status to success.
1041 x_return_status := fnd_api.g_ret_sts_success;
1042
1043 -- first check all required params
1044 -- 12557352: Modified check by calling check_mandatory_param
1045 check_mandatory_param('p_bank_id',p_bank_id);
1046 check_mandatory_param('p_branch_name',p_branch_name);
1047
1048 l_country := get_country(p_bank_id, x_return_status);
1049
1050 -- raise an exception if bank is not found
1051 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1052 RAISE fnd_api.g_exc_error;
1053 END IF;
1054
1055 OPEN c_bank;
1056 FETCH c_bank INTO l_bank_name, l_bank_number;
1057 IF c_bank%NOTFOUND THEN
1058 fnd_message.set_name('CE', 'CE_API_NO_BANK');
1059 fnd_msg_pub.add;
1060 x_return_status := fnd_api.g_ret_sts_error;
1061 RAISE fnd_api.g_exc_error;
1062 END IF;
1063 CLOSE c_bank;
1064
1065 -- start bug 9918477
1066 /*
1067 PROCEDURE CE_VALIDATE_BIC(
1068 X_BIC_CODE IN VARCHAR2,
1069 P_INIT_MSG_LIST IN VARCHAR2,
1070 X_MSG_COUNT OUT NOCOPY NUMBER,
1071 X_MSG_DATA OUT NOCOPY VARCHAR2,
1072 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2);
1073 */
1074 IF ( p_bic IS NOT NULL ) THEN
1075 CE_VALIDATE_BANKINFO.CE_VALIDATE_BIC(
1076 p_bic,
1077 p_init_msg_list,
1078 x_msg_count,
1079 x_msg_data,
1080 x_return_status
1081 );
1082
1083 -- raise an exception if BIC validations fail
1084 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1085 RAISE fnd_api.g_exc_error;
1086 END IF;
1087 END IF;
1088 -- end bug 9918477
1089
1090 -- country specific validation API call here
1091 ce_validate_bankinfo.ce_validate_branch(
1092 X_COUNTRY_NAME => l_country,
1093 X_BANK_NUMBER => l_bank_number,
1094 X_BRANCH_NUMBER => p_branch_number,
1095 X_BANK_NAME => l_bank_name,
1096 X_BRANCH_NAME => p_branch_name,
1097 X_BRANCH_NAME_ALT => p_alternate_branch_name,
1098 X_BANK_ID => p_bank_id,
1099 X_BRANCH_ID => null, -- branch_id
1100 P_INIT_MSG_LIST => FND_API.G_FALSE, -- do not re-initialize msg stack
1101 X_MSG_COUNT => x_msg_count,
1102 X_MSG_DATA => x_msg_data,
1103 X_VALUE_OUT => l_branch_number, -- reformatted branch number
1104 X_RETURN_STATUS => x_return_status,
1105 X_ACCOUNT_CLASSIFICATION => null, -- 9218190 added
1106 X_BRANCH_TYPE => p_branch_type); -- 9218190 added
1107
1108
1109 -- raise an exception if country specific validations fail
1110 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1111 RAISE fnd_api.g_exc_error;
1112 END IF;
1113
1114 l_party_rec.attribute_category := p_attribute_category;
1115 l_party_rec.attribute1 := p_attribute1;
1116 l_party_rec.attribute2 := p_attribute2;
1117 l_party_rec.attribute3 := p_attribute3;
1118 l_party_rec.attribute4 := p_attribute4;
1119 l_party_rec.attribute5 := p_attribute5;
1120 l_party_rec.attribute6 := p_attribute6;
1121 l_party_rec.attribute7 := p_attribute7;
1122 l_party_rec.attribute8 := p_attribute8;
1123 l_party_rec.attribute9 := p_attribute9;
1124 l_party_rec.attribute10 := p_attribute10;
1125 l_party_rec.attribute11 := p_attribute11;
1126 l_party_rec.attribute12 := p_attribute12;
1127 l_party_rec.attribute13 := p_attribute13;
1128 l_party_rec.attribute14 := p_attribute14;
1129 l_party_rec.attribute15 := p_attribute15;
1130 l_party_rec.attribute16 := p_attribute16;
1131 l_party_rec.attribute17 := p_attribute17;
1132 l_party_rec.attribute18 := p_attribute18;
1133 l_party_rec.attribute19 := p_attribute19;
1134 l_party_rec.attribute20 := p_attribute20;
1135 l_party_rec.attribute21 := p_attribute21;
1136 l_party_rec.attribute22 := p_attribute22;
1137 l_party_rec.attribute23 := p_attribute23;
1138 l_party_rec.attribute24 := p_attribute24;
1139
1140 l_org_rec.organization_name := p_branch_name;
1141 l_org_rec.organization_name_phonetic := p_alternate_branch_name;
1142 l_org_rec.mission_statement := p_description;
1143 l_org_rec.created_by_module := 'CE';
1144 l_org_rec.party_rec := l_party_rec;
1145
1146 l_branch_rec.bank_or_branch_number := l_branch_number;
1147 l_branch_rec.branch_type := p_branch_type;
1148 l_branch_rec.rfc_code := p_rfc_identifier;
1149 l_branch_rec.institution_type := 'BANK_BRANCH';
1150 l_branch_rec.organization_rec := l_org_rec;
1151 l_branch_rec.country := l_country;
1152
1153 l_gen_party_num := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
1154 if (l_gen_party_num = 'N') then
1155 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', 'Y');
1156 end if;
1157
1158 hz_bank_pub.create_bank_branch(fnd_api.g_false, l_branch_rec, p_bank_id,
1159 x_branch_id, l_party_number,
1160 l_profile_id, l_rel_id,
1161 l_rel_party_id, l_rel_party_number,
1162 l_bch_code_assignment_id, l_typ_code_assignment_id,
1163 l_rfc_code_assignment_id,
1164 x_return_status, x_msg_count, x_msg_data);
1165
1166 if (l_gen_party_num = 'N') then
1167 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', 'N');
1168 end if;
1169
1170 -- raise an exception if the branch creation is unsuccessful
1171 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1172 RAISE fnd_api.g_exc_error;
1173 END IF;
1174
1175 IF p_bic IS NOT NULL OR p_eft_number IS NOT NULL THEN
1176 l_contact_point_rec.contact_point_type := 'EFT';
1177 l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
1178 l_contact_point_rec.owner_table_id := x_branch_id;
1179 l_contact_point_rec.created_by_module := 'CE';
1180
1181 l_eft_rec.eft_swift_code := p_bic;
1182 l_eft_rec.eft_user_number := p_eft_number;
1183
1184 hz_contact_point_v2pub.create_eft_contact_point
1185 (fnd_api.g_false, l_contact_point_rec, l_eft_rec,
1186 l_contact_point_id,
1187 x_return_status, x_msg_count, x_msg_data);
1188
1189 -- raise an exception if the branch creation is unsuccessful
1190 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1191 RAISE fnd_api.g_exc_error;
1192 END IF;
1193 END IF;
1194
1195 -- get message count and if count is 1, get message info.
1196 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1197 p_count => x_msg_count,
1198 p_data => x_msg_data);
1199
1200 IF l_DEBUG in ('Y', 'C') THEN
1201 cep_standard.debug('<<CE_EXT_BANK_ACCT_PUB.create_bank_branch.');
1202 END IF;
1203
1204 EXCEPTION
1205 WHEN fnd_api.g_exc_error THEN
1206 ROLLBACK TO create_bank_branch;
1207 x_return_status := fnd_api.g_ret_sts_error;
1208 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1209 p_count => x_msg_count,
1210 p_data => x_msg_data);
1211
1212 IF l_DEBUG in ('Y', 'C') THEN
1213 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
1214 END IF;
1215
1216 WHEN fnd_api.g_exc_unexpected_error THEN
1217 ROLLBACK TO create_bank_branch;
1218 x_return_status := fnd_api.g_ret_sts_unexp_error;
1219 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1220 p_count => x_msg_count,
1221 p_data => x_msg_data);
1222
1223 IF l_DEBUG in ('Y', 'C') THEN
1224 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
1225 END IF;
1226
1227 WHEN OTHERS THEN
1228 ROLLBACK TO create_bank_branch;
1229 x_return_status := fnd_api.g_ret_sts_unexp_error;
1230 fnd_message.set_name('CE', 'CE_API_OTHERS_EXCEP');
1231 fnd_message.set_token('ERROR',SQLERRM);
1232 fnd_msg_pub.add;
1233 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1234 p_count => x_msg_count,
1235 p_data => x_msg_data);
1236
1237 IF l_DEBUG in ('Y', 'C') THEN
1238 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
1239 END IF;
1240
1241 END create_bank_branch;
1242
1243
1244 /*=======================================================================+
1245 | PUBLIC PROCEDURE update_bank_branch |
1246 | |
1247 | DESCRIPTION |
1248 | Update a bank branch organization party in TCA. |
1249 | |
1250 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1251 | hz_bank_pub.update_bank_branch |
1252 | |
1253 | ARGUMENTS |
1254 | IN: |
1255 | p_init_msg_list Initialize message stack if it is set to |
1256 | FND_API.G_TRUE. Default is fnd_api.g_false
1257 | p_branch_id Party ID of the branch to be updated. |
1258 | p_branch_name Bank branch name. |
1259 | p_branch_number Bank branch number. |
1260 | p_branch_type Bank branch type. |
1261 | p_alternate_branch_name Alternate bank branch name. |
1262 | p_description Description. |
1263 | p_bic BIC (Bank Identification Code). |
1264 | p_eft_number EFT number. |
1265 | p_rfc_identifier RFC Identifier. |
1266 | IN/OUT: |
1267 | p_bch_object_version_number Current object version number for |
1268 | the bank branch. |
1269 | p_typ_object_version_number Current object version number for |
1270 | bank branch type code assignment. |
1271 | p_rfc_object_version_number Current object version number for |
1272 | RFC code assignment. |
1273 | OUT: |
1274 | x_return_status Return status after the call. The status can |
1275 | be FND_API.G_RET_STS_SUCCESS (success), |
1276 | fnd_api.g_ret_sts_error (error), |
1277 | fnd_api.g_ret_sts_unexp_error (unexpected |
1278 | error). |
1279 | x_msg_count Number of messages in message stack. |
1280 | x_msg_data Message text if x_msg_count is 1. |
1281 | MODIFICATION HISTORY |
1282 | 25-AUG-2004 Xin Wang Created. |
1283 | 05-MAR-2009 TALAPATI Added a new parameter p_country_validate |
1284 | to enable or disable the country specific |
1285 | validation. (Bug # 8286747) |
1286 +=======================================================================*/
1287 PROCEDURE update_bank_branch (
1288 p_init_msg_list IN VARCHAR2:= fnd_api.g_false,
1289 p_branch_id IN NUMBER,
1290 p_branch_name IN VARCHAR2,
1291 p_branch_number IN VARCHAR2 DEFAULT NULL,
1292 p_branch_type IN VARCHAR2,
1293 p_alternate_branch_name IN VARCHAR2 DEFAULT NULL,
1294 p_description IN VARCHAR2 DEFAULT NULL,
1295 p_bic IN VARCHAR2 DEFAULT NULL,
1296 p_eft_number IN VARCHAR2 DEFAULT NULL,
1297 p_rfc_identifier IN VARCHAR2 DEFAULT NULL,
1298 p_attribute_category IN VARCHAR2 DEFAULT NULL,
1299 p_attribute1 IN VARCHAR2 DEFAULT NULL,
1300 p_attribute2 IN VARCHAR2 DEFAULT NULL,
1301 p_attribute3 IN VARCHAR2 DEFAULT NULL,
1302 p_attribute4 IN VARCHAR2 DEFAULT NULL,
1303 p_attribute5 IN VARCHAR2 DEFAULT NULL,
1304 p_attribute6 IN VARCHAR2 DEFAULT NULL,
1305 p_attribute7 IN VARCHAR2 DEFAULT NULL,
1306 p_attribute8 IN VARCHAR2 DEFAULT NULL,
1307 p_attribute9 IN VARCHAR2 DEFAULT NULL,
1308 p_attribute10 IN VARCHAR2 DEFAULT NULL,
1309 p_attribute11 IN VARCHAR2 DEFAULT NULL,
1310 p_attribute12 IN VARCHAR2 DEFAULT NULL,
1311 p_attribute13 IN VARCHAR2 DEFAULT NULL,
1312 p_attribute14 IN VARCHAR2 DEFAULT NULL,
1313 p_attribute15 IN VARCHAR2 DEFAULT NULL,
1314 p_attribute16 IN VARCHAR2 DEFAULT NULL,
1315 p_attribute17 IN VARCHAR2 DEFAULT NULL,
1316 p_attribute18 IN VARCHAR2 DEFAULT NULL,
1317 p_attribute19 IN VARCHAR2 DEFAULT NULL,
1318 p_attribute20 IN VARCHAR2 DEFAULT NULL,
1319 p_attribute21 IN VARCHAR2 DEFAULT NULL,
1320 p_attribute22 IN VARCHAR2 DEFAULT NULL,
1321 p_attribute23 IN VARCHAR2 DEFAULT NULL,
1322 p_attribute24 IN VARCHAR2 DEFAULT NULL,
1323 p_country_validate IN VARCHAR2 DEFAULT 'Y',
1324 p_bch_object_version_number IN OUT NOCOPY NUMBER,
1325 p_typ_object_version_number IN OUT NOCOPY NUMBER,
1326 p_rfc_object_version_number IN OUT NOCOPY NUMBER,
1327 p_eft_object_version_number IN OUT NOCOPY NUMBER,
1328 x_return_status OUT NOCOPY VARCHAR2,
1329 x_msg_count OUT NOCOPY NUMBER,
1330 x_msg_data OUT NOCOPY VARCHAR2
1331 ) IS
1332 CURSOR c_eft IS
1333 SELECT contact_point_id
1334 FROM hz_contact_points
1335 WHERE contact_point_type = 'EFT'
1336 AND owner_table_name = 'HZ_PARTIES'
1337 AND owner_table_id = p_branch_id;
1338
1339 l_branch_rec hz_bank_pub.bank_rec_type;
1340 l_org_rec hz_party_v2pub.organization_rec_type;
1341 l_party_rec hz_party_v2pub.party_rec_type;
1342 l_party_number VARCHAR2(30);
1343 l_profile_id NUMBER(15);
1344 l_rel_id NUMBER(15);
1345 l_rel_party_id NUMBER(15);
1346 l_rel_party_number NUMBER(15);
1347 l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
1348 l_eft_rec hz_contact_point_v2pub.eft_rec_type;
1349 l_contact_point_id NUMBER(15);
1350 l_bank_id NUMBER(15);
1351 l_bank_name VARCHAR2(360);
1352 l_bank_number VARCHAR2(30);
1353 l_country VARCHAR2(60);
1354 l_branch_number VARCHAR2(30);
1355 BEGIN
1356 SAVEPOINT update_bank_branch;
1357
1358 IF l_DEBUG in ('Y', 'C') THEN
1359 cep_standard.debug('>>CE_EXT_BANK_ACCT_PUB.update_bank_branch.');
1360 END IF;
1361
1362 -- initialize message list
1363 IF fnd_api.to_boolean(p_init_msg_list) THEN
1364 fnd_msg_pub.initialize;
1365 END IF;
1366
1367 -- initialize API return status to success.
1368 x_return_status := fnd_api.g_ret_sts_success;
1369
1370 -- first check all required params
1371 -- 12557352: Modified check by calling check_mandatory_param
1372 check_mandatory_param('p_branch_id',p_branch_id);
1373 check_mandatory_param('p_branch_name',p_branch_name);
1374
1375 find_bank_info(p_branch_id, x_return_status, l_bank_id, l_country, l_bank_name, l_bank_number);
1376
1377 -- raise an exception if bank is not found
1378 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1379 RAISE fnd_api.g_exc_error;
1380 END IF;
1381
1382 -- start bug 9918477
1383 /*
1384 PROCEDURE CE_VALIDATE_BIC(
1385 X_BIC_CODE IN VARCHAR2,
1386 P_INIT_MSG_LIST IN VARCHAR2,
1387 X_MSG_COUNT OUT NOCOPY NUMBER,
1388 X_MSG_DATA OUT NOCOPY VARCHAR2,
1389 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2);
1390 */
1391 IF ( p_bic IS NOT NULL ) THEN
1392 CE_VALIDATE_BANKINFO.CE_VALIDATE_BIC(
1393 p_bic,
1394 p_init_msg_list,
1395 x_msg_count,
1396 x_msg_data,
1397 x_return_status
1398 );
1399
1400 -- raise an exception if BIC validations fail
1401 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1402 RAISE fnd_api.g_exc_error;
1403 END IF;
1404 END IF;
1405 -- end bug 9918477
1406
1407 -- country specific validation API call here
1408 ce_validate_bankinfo.ce_validate_branch(
1409 X_COUNTRY_NAME => l_country,
1410 X_BANK_NUMBER => l_bank_number,
1411 X_BRANCH_NUMBER => p_branch_number,
1412 X_BANK_NAME => l_bank_name,
1413 X_BRANCH_NAME => p_branch_name,
1414 X_BRANCH_NAME_ALT => p_alternate_branch_name,
1415 X_BANK_ID => l_bank_id,
1416 X_BRANCH_ID => p_branch_id,
1417 P_INIT_MSG_LIST => FND_API.G_FALSE, -- do not re-initialize msg stack
1418 X_MSG_COUNT => x_msg_count,
1419 X_MSG_DATA => x_msg_data,
1420 X_VALUE_OUT => l_branch_number, -- reformatted branch number
1421 X_RETURN_STATUS => x_return_status,
1422 X_ACCOUNT_CLASSIFICATION => NULL, -- 9218190 added
1423 X_BRANCH_TYPE => p_branch_type); -- 9218190 added
1424
1425 -- Bug #8286747 The country specific validation errors are reported only if p_country_validate is set to 'Y'
1426 -- raise an exception if country specific validations fail
1427 IF x_return_status <> fnd_api.g_ret_sts_success and upper(p_country_validate) ='Y' THEN
1428 RAISE fnd_api.g_exc_error;
1429 END IF;
1430
1431 l_party_rec.party_id := p_branch_id;
1432 l_party_rec.attribute_category := p_attribute_category;
1433 l_party_rec.attribute1 := p_attribute1;
1434 l_party_rec.attribute2 := p_attribute2;
1435 l_party_rec.attribute3 := p_attribute3;
1436 l_party_rec.attribute4 := p_attribute4;
1437 l_party_rec.attribute5 := p_attribute5;
1438 l_party_rec.attribute6 := p_attribute6;
1439 l_party_rec.attribute7 := p_attribute7;
1440 l_party_rec.attribute8 := p_attribute8;
1441 l_party_rec.attribute9 := p_attribute9;
1442 l_party_rec.attribute10 := p_attribute10;
1443 l_party_rec.attribute11 := p_attribute11;
1444 l_party_rec.attribute12 := p_attribute12;
1445 l_party_rec.attribute13 := p_attribute13;
1446 l_party_rec.attribute14 := p_attribute14;
1447 l_party_rec.attribute15 := p_attribute15;
1448 l_party_rec.attribute16 := p_attribute16;
1449 l_party_rec.attribute17 := p_attribute17;
1450 l_party_rec.attribute18 := p_attribute18;
1451 l_party_rec.attribute19 := p_attribute19;
1452 l_party_rec.attribute20 := p_attribute20;
1453 l_party_rec.attribute21 := p_attribute21;
1454 l_party_rec.attribute22 := p_attribute22;
1455 l_party_rec.attribute23 := p_attribute23;
1456 l_party_rec.attribute24 := p_attribute24;
1457
1458 l_org_rec.organization_name := p_branch_name;
1459 l_org_rec.organization_name_phonetic := p_alternate_branch_name;
1460 l_org_rec.mission_statement := p_description;
1461 --8400543: created_by_module should not be updated
1462 --l_org_rec.created_by_module := 'CE';
1463 l_org_rec.party_rec := l_party_rec;
1464
1465 l_branch_rec.bank_or_branch_number := p_branch_number;
1466 l_branch_rec.branch_type := p_branch_type;
1467 l_branch_rec.rfc_code := p_rfc_identifier;
1468 l_branch_rec.organization_rec := l_org_rec;
1469
1470 hz_bank_pub.update_bank_branch(fnd_api.g_false, l_branch_rec, null,
1471 l_rel_id,
1472 p_bch_object_version_number, p_typ_object_version_number,
1473 p_rfc_object_version_number,
1474 l_profile_id, l_rel_party_id, l_rel_party_number,
1475 x_return_status, x_msg_count, x_msg_data);
1476
1477 -- raise an exception if the branch creation is unsuccessful
1478 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1479 RAISE fnd_api.g_exc_error;
1480 END IF;
1481
1482 -- see whether this branch already have eft contact points
1483 OPEN c_eft;
1484 FETCH c_eft INTO l_contact_point_id;
1485
1486 IF c_eft%NOTFOUND THEN -- does not already have eft contact points
1487 IF p_bic IS NOT NULL OR p_eft_number IS NOT NULL THEN
1488 l_contact_point_rec.contact_point_type := 'EFT';
1489 l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
1490 l_contact_point_rec.owner_table_id := p_branch_id;
1491 l_contact_point_rec.created_by_module := 'CE';
1492
1493 l_eft_rec.eft_swift_code := p_bic;
1494 l_eft_rec.eft_user_number := p_eft_number;
1495
1496 hz_contact_point_v2pub.create_eft_contact_point
1497 (fnd_api.g_false, l_contact_point_rec, l_eft_rec,
1498 l_contact_point_id,
1499 x_return_status, x_msg_count, x_msg_data);
1500 END IF;
1501 ELSE -- already have, update
1502 l_contact_point_rec.contact_point_id := l_contact_point_id;
1503
1504 l_eft_rec.eft_swift_code := p_bic;
1505 l_eft_rec.eft_user_number := p_eft_number;
1506
1507 hz_contact_point_v2pub.update_eft_contact_point
1508 (fnd_api.g_false, l_contact_point_rec, l_eft_rec,
1509 p_eft_object_version_number,
1510 x_return_status, x_msg_count, x_msg_data);
1511 END IF;
1512
1513 -- raise an exception if the eft contact point creation/update is unsuccessful
1514 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1515 RAISE fnd_api.g_exc_error;
1516 END IF;
1517
1518 -- get message count and if count is 1, get message info.
1519 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1520 p_count => x_msg_count,
1521 p_data => x_msg_data);
1522
1523 IF l_DEBUG in ('Y', 'C') THEN
1524 cep_standard.debug('<<CE_EXT_BANK_ACCT_PUB.update_bank_branch.');
1525 END IF;
1526
1527 EXCEPTION
1528 WHEN fnd_api.g_exc_error THEN
1529 ROLLBACK TO update_bank_branch;
1530 x_return_status := fnd_api.g_ret_sts_error;
1531 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1532 p_count => x_msg_count,
1533 p_data => x_msg_data);
1534
1535 IF l_DEBUG in ('Y', 'C') THEN
1536 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
1537 END IF;
1538
1539 WHEN fnd_api.g_exc_unexpected_error THEN
1540 ROLLBACK TO update_bank_branch;
1541 x_return_status := fnd_api.g_ret_sts_unexp_error;
1542 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1543 p_count => x_msg_count,
1544 p_data => x_msg_data);
1545
1546 IF l_DEBUG in ('Y', 'C') THEN
1547 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
1548 END IF;
1549
1550 WHEN OTHERS THEN
1551 ROLLBACK TO update_bank_branch;
1552 x_return_status := fnd_api.g_ret_sts_unexp_error;
1553 fnd_message.set_name('CE', 'CE_API_OTHERS_EXCEP');
1554 fnd_message.set_token('ERROR',SQLERRM);
1555 fnd_msg_pub.add;
1556 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1557 p_count => x_msg_count,
1558 p_data => x_msg_data);
1559
1560 IF l_DEBUG in ('Y', 'C') THEN
1561 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
1562 END IF;
1563
1564 END update_bank_branch;
1565
1566
1567 /*======================================================================+
1568 | PUBLIC PROCEDURE set_bank_branch_end_date |
1569 | |
1570 | DESCRIPTION |
1571 | Set the end date of a bank branch. |
1572 | |
1573 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1574 | hz_bank_pub.update_bank |
1575 | |
1576 | ARGUMENTS |
1577 | IN: |
1578 | p_init_msg_list Initialize message stack if it is set to |
1579 | FND_API.G_TRUE. Default is fnd_api.g_false
1580 | p_branch_id Party ID of the branch to be inactivated.|
1581 | p_end_date Inactive date of the bank branch. |
1582 | IN/OUT: |
1583 | p_object_version_number Current object version number for the |
1584 | code assignment of the bank institution|
1585 | type for the bank branch. |
1586 | OUT: |
1587 | x_return_status Return status after the call. The status can |
1588 | be FND_API.G_RET_STS_SUCCESS (success), |
1589 | fnd_api.g_ret_sts_error (error), |
1590 | fnd_api.g_ret_sts_unexp_error (unexpected |
1591 | error). |
1592 | x_msg_count Number of messages in message stack. |
1593 | x_msg_data Message text if x_msg_count is 1. |
1594 | MODIFICATION HISTORY |
1595 | 25-AUG-2004 Xin Wang Created. |
1596 | 20-May-2011 vnetan 12557352: Bug fix for end-dating |
1597 | internal and external bank accounts |
1598 +=======================================================================*/
1599 PROCEDURE set_bank_branch_end_date (
1600 p_init_msg_list IN VARCHAR2:= fnd_api.g_false,
1601 p_branch_id IN NUMBER,
1602 p_end_date IN DATE,
1603 p_object_version_number IN OUT NOCOPY NUMBER,
1604 x_return_status OUT NOCOPY VARCHAR2,
1605 x_msg_count OUT NOCOPY NUMBER,
1606 x_msg_data OUT NOCOPY VARCHAR2
1607 ) IS
1608
1609 CURSOR c_bank (p_bank_id NUMBER) IS
1610 SELECT end_date_active, object_version_number
1611 FROM hz_code_assignments
1612 WHERE owner_table_name = 'HZ_PARTIES'
1613 AND owner_table_id = p_bank_id
1614 AND class_category = 'BANK_INSTITUTION_TYPE'
1615 AND class_code = 'BANK';
1616
1617 CURSOR c_bank_id IS
1618 SELECT object_id
1619 FROM hz_relationships
1620 WHERE relationship_type = 'BANK_AND_BRANCH'
1621 AND relationship_code = 'BRANCH_OF'
1622 AND subject_table_name = 'HZ_PARTIES'
1623 AND subject_type = 'ORGANIZATION'
1624 AND object_table_name = 'HZ_PARTIES'
1625 AND object_type = 'ORGANIZATION'
1626 AND subject_id = p_branch_id;
1627
1628 CURSOR c_branch_end IS
1629 SELECT end_date_active
1630 FROM hz_code_assignments
1631 WHERE owner_table_name = 'HZ_PARTIES'
1632 AND owner_table_id = p_branch_id
1633 AND class_category = 'BANK_INSTITUTION_TYPE'
1634 AND class_code = 'BANK_BRANCH';
1635
1636 CURSOR c_branch_party_ovn IS
1637 SELECT object_version_number
1638 FROM hz_parties
1639 WHERE party_id = p_branch_id;
1640
1641 -- 12557532: renamed cursor c_account => c_internal_accounts
1642 CURSOR c_internal_account IS
1643 SELECT bank_account_id, start_date, end_date, object_version_number
1644 FROM ce_bank_accounts
1645 WHERE bank_branch_id = p_branch_id;
1646
1647 -- 12557532: Added cursor c_external_account
1648 CURSOR c_external_account IS
1649 SELECT ext_bank_account_id, nvl(start_date,to_date('1900/01/01','YYYY/MM/DD')) start_date, end_date, object_version_number
1650 FROM iby_ext_bank_accounts
1651 WHERE branch_id = p_branch_id;
1652
1653 CURSOR c_rfc_ovn IS
1654 SELECT object_version_number
1655 FROM hz_code_assignments
1656 WHERE owner_table_name = 'HZ_PARTIES'
1657 AND owner_table_id = p_branch_id
1658 AND class_category = 'RFC_IDENTIFIER';
1659
1660 l_bank_id NUMBER(15);
1661 l_bank_end DATE;
1662 l_branch_end DATE;
1663 l_bank_ovn NUMBER(15);
1664 l_branch_party_ovn NUMBER(15);
1665 l_rfc_ovn NUMBER(15);
1666 l_branch_rec hz_bank_pub.bank_rec_type;
1667 l_org_rec hz_party_v2pub.organization_rec_type;
1668 l_party_rec hz_party_v2pub.party_rec_type;
1669 l_profile_id NUMBER(15);
1670 l_rel_id NUMBER(15);
1671 l_rel_party_id NUMBER(15);
1672 l_rel_party_number NUMBER(15);
1673 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1674 l_int_account_ovn NUMBER(15); -- 12557352: Added
1675 l_ext_account_ovn NUMBER(15); -- 12557352: Added
1676 BEGIN
1677 SAVEPOINT set_bank_branch_end_date;
1678
1679 IF l_DEBUG in ('Y', 'C') THEN
1680 cep_standard.debug('>>CE_EXT_BANK_ACCT_PUB.set_bank_branch_end_date.');
1681 END IF;
1682
1683 -- initialize message list
1684 IF fnd_api.to_boolean(p_init_msg_list) THEN
1685 fnd_msg_pub.initialize;
1686 END IF;
1687
1688 -- initialize API return status to success.
1689 x_return_status := fnd_api.g_ret_sts_success;
1690
1691 -- first check all required params
1692 -- 12557352: Modified check by calling check_mandatory_param
1693 check_mandatory_param('p_branch_id',p_branch_id);
1694 check_mandatory_param('p_object_version_number',p_object_version_number);
1695
1696 -- if the new end date is later than the old branch end date
1697 -- and is later than its bank's end date,
1698 -- set its bank's end date to the same date as well.
1699 -- this is the re-activation case
1700
1701 -- old branch end date
1702 OPEN c_branch_end;
1703 FETCH c_branch_end INTO l_branch_end;
1704 IF c_branch_end%NOTFOUND THEN
1705 fnd_message.set_name('CE', 'CE_API_NO_BRANCH');
1706 fnd_msg_pub.add;
1707 x_return_status := fnd_api.g_ret_sts_error;
1708 RAISE fnd_api.g_exc_error;
1709 END IF;
1710 CLOSE c_branch_end;
1711
1712 -- find bank_id of this branch's bank
1713 OPEN c_bank_id;
1714 FETCH c_bank_id INTO l_bank_id;
1715 IF c_bank_id%NOTFOUND THEN
1716 fnd_message.set_name('CE', 'CE_API_NO_BRANCH');
1717 fnd_msg_pub.add;
1718 x_return_status := fnd_api.g_ret_sts_error;
1719 RAISE fnd_api.g_exc_error;
1720 END IF;
1721 CLOSE c_bank_id;
1722
1723 -- bank's end date
1724 OPEN c_bank (l_bank_id);
1725 FETCH c_bank INTO l_bank_end, l_bank_ovn;
1726 IF c_bank%NOTFOUND THEN
1727 fnd_message.set_name('CE', 'CE_API_NO_BANK');
1728 fnd_msg_pub.add;
1729 x_return_status := fnd_api.g_ret_sts_error;
1730 RAISE fnd_api.g_exc_error;
1731 END IF;
1732 CLOSE c_bank;
1733
1734 IF (p_end_date IS NOT NULL AND p_end_date > NVL(l_bank_end, p_end_date))
1735 OR (p_end_date IS NULL AND l_bank_end IS NOT NULL)
1736 THEN
1737 set_bank_end_date (
1738 p_init_msg_list => fnd_api.g_false,
1739 p_bank_id => l_bank_id,
1740 p_end_date => p_end_date,
1741 p_object_version_number => l_bank_ovn,
1742 x_return_status => x_return_status,
1743 x_msg_count => x_msg_count,
1744 x_msg_data => x_msg_data);
1745
1746 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1747 RAISE fnd_api.g_exc_error;
1748 END IF;
1749 END IF;
1750
1751 -- if the new end date is earlier than the old branch end date
1752 -- and is earlier than its accounts' end date,
1753 -- set its accounts' end date to the same date as well.
1754 -- 12557352: Set the end dates for both internal and external accounts
1755 -- End date External accounts
1756 FOR ext_acct_rec IN c_external_account
1757 LOOP
1758 IF p_end_date IS NOT NULL AND p_end_date < NVL(ext_acct_rec.end_date, p_end_date + 1)
1759 THEN
1760 l_ext_account_ovn := ext_acct_rec.object_version_number;
1761
1762 iby_ext_bankacct_pub.set_ext_bank_acct_dates (
1763 p_api_version => 1.0,
1764 p_init_msg_list => fnd_api.g_false,
1765 p_acct_id => ext_acct_rec.ext_bank_account_id,
1766 p_start_date => ext_acct_rec.start_date,
1767 p_end_date => p_end_date,
1768 p_object_version_number => l_ext_account_ovn,
1769 x_return_status => x_return_status,
1770 x_msg_count => x_msg_count,
1771 x_msg_data => x_msg_data,
1772 x_response => l_response);
1773
1774 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1775 RAISE fnd_api.g_exc_error;
1776 END IF;
1777
1778 l_ext_account_ovn := NULL;
1779
1780 END IF;
1781 END LOOP;
1782
1783 -- End date Internal accounts
1784 FOR int_acct_rec IN c_internal_account
1785 LOOP
1786 IF p_end_date IS NOT NULL AND p_end_date < NVL(int_acct_rec.end_date, p_end_date + 1)
1787 THEN
1788 l_int_account_ovn := int_acct_rec.object_version_number;
1789
1790 set_bank_acct_end_date (
1791 p_init_msg_list => fnd_api.g_false,
1792 p_bank_acct_id => int_acct_rec.bank_account_id,
1793 p_end_date => p_end_date,
1794 p_object_version_number => l_int_account_ovn,
1795 x_return_status => x_return_status,
1796 x_msg_count => x_msg_count,
1797 x_msg_data => x_msg_data);
1798
1799 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1800 RAISE fnd_api.g_exc_error;
1801 END IF;
1802
1803 l_int_account_ovn := NULL;
1804
1805 END IF;
1806 END LOOP;
1807
1808 -- update branch's end date
1809 l_party_rec.party_id := p_branch_id;
1810 l_org_rec.party_rec := l_party_rec;
1811 l_branch_rec.organization_rec := l_org_rec;
1812 l_branch_rec.inactive_date := p_end_date;
1813
1814 OPEN c_branch_party_ovn;
1815 FETCH c_branch_party_ovn INTO l_branch_party_ovn;
1816 IF c_branch_party_ovn%NOTFOUND THEN
1817 fnd_message.set_name('CE', 'CE_API_NO_BRANCH');
1818 fnd_msg_pub.add;
1819 x_return_status := fnd_api.g_ret_sts_error;
1820 CLOSE c_branch_party_ovn;
1821 RAISE fnd_api.g_exc_error;
1822 END IF;
1823 CLOSE c_branch_party_ovn;
1824
1825 OPEN c_rfc_ovn;
1826 FETCH c_rfc_ovn INTO l_rfc_ovn;
1827 IF c_rfc_ovn%NOTFOUND THEN
1828 l_rfc_ovn := NULL;
1829 END IF;
1830 CLOSE c_rfc_ovn;
1831
1832 HZ_BANK_PUB.update_bank_branch(
1833 p_init_msg_list => fnd_api.g_false,
1834 p_bank_rec => l_branch_rec,
1835 p_bank_party_id => NULL,
1836 p_relationship_id => l_rel_id,
1837 p_pobject_version_number => l_branch_party_ovn,
1838 p_bbtobject_version_number => p_object_version_number,
1839 p_rfcobject_version_number => l_rfc_ovn,
1840 x_profile_id => l_profile_id,
1841 x_rel_party_id => l_rel_party_id,
1842 x_rel_party_number => l_rel_party_number,
1843 x_return_status => x_return_status,
1844 x_msg_count => x_msg_count,
1845 x_msg_data => x_msg_data);
1846
1847 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1848 RAISE fnd_api.g_exc_error;
1849 END IF;
1850
1851 -- get message count and if count is 1, get message info.
1852 fnd_msg_pub.count_and_get(
1853 p_encoded => fnd_api.g_false,
1854 p_count => x_msg_count,
1855 p_data => x_msg_data);
1856
1857 IF l_DEBUG in ('Y', 'C') THEN
1858 cep_standard.debug('<<CE_EXT_BANK_ACCT_PUB.set_bank_branch_end_date.');
1859 END IF;
1860
1861 EXCEPTION
1862 WHEN fnd_api.g_exc_error THEN
1863 ROLLBACK TO set_bank_branch_end_date;
1864 x_return_status := fnd_api.g_ret_sts_error;
1865 fnd_msg_pub.count_and_get(
1866 p_encoded => fnd_api.g_false,
1867 p_count => x_msg_count,
1868 p_data => x_msg_data);
1869
1870 IF l_DEBUG in ('Y', 'C') THEN
1871 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
1872 END IF;
1873
1874 WHEN fnd_api.g_exc_unexpected_error THEN
1875 ROLLBACK TO set_bank_branch_end_date;
1876 x_return_status := fnd_api.g_ret_sts_unexp_error;
1877 fnd_msg_pub.count_and_get(
1878 p_encoded => fnd_api.g_false,
1879 p_count => x_msg_count,
1880 p_data => x_msg_data);
1881
1882 IF l_DEBUG in ('Y', 'C') THEN
1883 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
1884 END IF;
1885
1886 WHEN OTHERS THEN
1887 ROLLBACK TO set_bank_branch_end_date;
1888 x_return_status := fnd_api.g_ret_sts_unexp_error;
1889 fnd_message.set_name('CE', 'CE_API_OTHERS_EXCEP');
1890 fnd_message.set_token('ERROR',SQLERRM);
1891 fnd_msg_pub.add;
1892 fnd_msg_pub.count_and_get(
1893 p_encoded => fnd_api.g_false,
1894 p_count => x_msg_count,
1895 p_data => x_msg_data);
1896
1897 IF l_DEBUG in ('Y', 'C') THEN
1898 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
1899 END IF;
1900 END set_bank_branch_end_date;
1901
1902
1903 /*=======================================================================+
1904 | PUBLIC PROCEDURE check_branch_exist |
1905 | |
1906 | DESCRIPTION |
1907 | Check whether a bank branch already exists. |
1908 | |
1909 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1910 | |
1911 | ARGUMENTS |
1912 | IN: |
1913 | p_bank_id Bank Party ID. |
1914 | p_branch_name Bank branch name. |
1915 | IN/OUT: |
1916 | OUT: |
1917 | x_branch_id Bank branch Party ID if branch exists, |
1918 | null if branch does not already exist. |
1919 | |
1920 | MODIFICATION HISTORY |
1921 | 25-AUG-2004 Xin Wang Created. |
1922 +=======================================================================*/
1923 PROCEDURE check_branch_exist(
1924 p_bank_id IN NUMBER,
1925 p_branch_name IN VARCHAR2,
1926 p_branch_number IN VARCHAR2,
1927 x_branch_id OUT NOCOPY NUMBER,
1928 x_end_date OUT NOCOPY DATE
1929 ) IS
1930 CURSOR c_branch_name IS
1931 SELECT hz_branch.party_id,
1932 hz_branchCA.end_date_active
1933 FROM hz_parties hz_branch,
1934 hz_relationships hz_rel,
1935 hz_code_assignments hz_branchCA
1936 WHERE hz_branchCA.owner_table_name = 'HZ_PARTIES'
1937 AND hz_branchCA.owner_table_id = hz_branch.party_id
1938 AND hz_branchCA.class_category = 'BANK_INSTITUTION_TYPE'
1939 AND hz_branchCA.class_code = 'BANK_BRANCH'
1940 AND NVL(hz_branchCA.STATUS, 'A') = 'A'
1941 AND hz_rel.OBJECT_ID = p_bank_id
1942 And hz_branch.PARTY_ID = hz_rel.SUBJECT_ID
1943 And hz_rel.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
1944 And hz_rel.RELATIONSHIP_CODE = 'BRANCH_OF'
1945 And hz_rel.STATUS = 'A'
1946 And hz_rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1947 And hz_rel.SUBJECT_TYPE = 'ORGANIZATION'
1948 And hz_rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1949 And hz_rel.OBJECT_TYPE = 'ORGANIZATION'
1950 AND upper(hz_branch.party_name) = upper(p_branch_name);
1951
1952 CURSOR c_branch_num IS
1953 SELECT hz_branch.party_id,
1954 hz_branchCA.end_date_active
1955 FROM hz_parties hz_branch,
1956 hz_organization_profiles hz_branchProf,
1957 hz_relationships hz_rel,
1958 hz_code_assignments hz_branchCA
1959 WHERE hz_branchCA.owner_table_name = 'HZ_PARTIES'
1960 AND hz_branchCA.owner_table_id = hz_branch.party_id
1961 AND hz_branchCA.class_category = 'BANK_INSTITUTION_TYPE'
1962 AND hz_branchCA.class_code = 'BANK_BRANCH'
1963 AND NVL(hz_branchCA.STATUS, 'A') = 'A'
1964 AND hz_rel.OBJECT_ID = p_bank_id
1965 And hz_branch.PARTY_ID = hz_rel.SUBJECT_ID
1966 And hz_rel.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
1967 And hz_rel.RELATIONSHIP_CODE = 'BRANCH_OF'
1968 And hz_rel.STATUS = 'A'
1969 And hz_rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1970 And hz_rel.SUBJECT_TYPE = 'ORGANIZATION'
1971 And hz_rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1972 And hz_rel.OBJECT_TYPE = 'ORGANIZATION'
1973 AND hz_branch.party_id = hz_branchProf.party_id
1974 AND SYSDATE between TRUNC(hz_branchProf.effective_start_date)
1975 and NVL(TRUNC(hz_branchProf.effective_end_date), SYSDATE+1)
1976 AND hz_branchProf.bank_or_branch_number = p_branch_number;
1977 BEGIN
1978 IF l_DEBUG in ('Y', 'C') THEN
1979 cep_standard.debug('>>CE_EXT_BANK_ACCT_PUB.check_branch_exist.');
1980 END IF;
1981
1982 IF p_branch_name IS NOT NULL THEN
1983 OPEN c_branch_name;
1984 FETCH c_branch_name INTO x_branch_id, x_end_date;
1985 IF c_branch_name%NOTFOUND THEN
1986 x_branch_id := null;
1987 x_end_date := null;
1988 END IF;
1989 CLOSE c_branch_name;
1990 ELSIF p_branch_number IS NOT NULL THEN
1991 OPEN c_branch_num;
1992 FETCH c_branch_num INTO x_branch_id, x_end_date;
1993 IF c_branch_num%NOTFOUND THEN
1994 x_branch_id := null;
1995 x_end_date := null;
1996 END IF;
1997 CLOSE c_branch_num;
1998 ELSE
1999 x_branch_id := null;
2000 x_end_date := null;
2001 END IF;
2002
2003 IF l_DEBUG in ('Y', 'C') THEN
2004 cep_standard.debug('<<CE_EXT_BANK_ACCT_PUB.check_branch_exist.');
2005 END IF;
2006
2007 EXCEPTION
2008 WHEN OTHERS THEN
2009 cep_standard.sql_error('CE_EXT_BANK_ACCT_PUB.check_branch_exist', sqlcode, sqlerrm);
2010
2011 END check_branch_exist;
2012
2013
2014 /*=======================================================================+
2015 | PUBLIC PROCEDURE create_bank_acct |
2016 | |
2017 | DESCRIPTION |
2018 | Create an internal or subsidiary bank account. |
2019 | |
2020 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2021 | |
2022 | ARGUMENTS |
2023 | IN: |
2024 | p_init_msg_list Initialize message stack if it is set to |
2025 | FND_API.G_TRUE. Default is fnd_api.g_false |
2026 | p_acct_rec Bank account record. |
2027 | IN/OUT: |
2028 | OUT: |
2029 | x_acct_id Bank account ID. |
2030 | x_return_status Return status after the call. The status can |
2031 | be FND_API.G_RET_STS_SUCCESS (success), |
2032 | fnd_api.g_ret_sts_error (error), |
2033 | fnd_api.g_ret_sts_unexp_error (unexpected |
2034 | error). |
2035 | x_msg_count Number of messages in message stack. |
2036 | x_msg_data Message text if x_msg_count is 1. |
2037 | MODIFICATION HISTORY |
2038 | 25-AUG-2004 Xin Wang Created. |
2039 +=======================================================================*/
2040 PROCEDURE create_bank_acct (
2041 p_init_msg_list IN VARCHAR2:= fnd_api.g_false,
2042 p_acct_rec IN BankAcct_rec_type,
2043 x_acct_id OUT NOCOPY NUMBER,
2044 x_return_status OUT NOCOPY VARCHAR2,
2045 x_msg_count OUT NOCOPY NUMBER,
2046 x_msg_data OUT NOCOPY VARCHAR2
2047 ) IS
2048 CURSOR c_acct_id IS
2049 SELECT CE_BANK_ACCOUNTS_S.nextval
2050 FROM sys.dual;
2051 CURSOR c_acct_rowid IS
2052 SELECT rowid
2053 FROM CE_BANK_ACCOUNTS
2054 WHERE bank_account_id = x_acct_id;
2055 CURSOR c_branch IS
2056 SELECT bank_or_branch_number
2057 FROM hz_organization_profiles
2058 WHERE SYSDATE between TRUNC(effective_start_date)
2059 and NVL(TRUNC(effective_end_date), SYSDATE+1)
2060 AND party_id = p_acct_rec.branch_id;
2061 /*
2062 CURSOR c_bank_id IS
2063 SELECT object_id
2064 FROM hz_relationships
2065 WHERE RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
2066 AND RELATIONSHIP_CODE = 'BRANCH_OF'
2067 AND subject_id = p_acct_rec.branch_id;
2068 */
2069 l_iban VARCHAR2(50) := null;
2070 l_alc VARCHAR2(30) := null;
2071 l_country VARCHAR2(60);
2072 l_bank_id NUMBER(15) := null;
2073 l_acct_rowid VARCHAR2(100);
2074 l_bank_name VARCHAR2(360) := null;
2075 l_bank_number VARCHAR2(30) := null;
2076 l_branch_number VARCHAR2(30) := null;
2077 l_account_number VARCHAR2(100) := null;
2078 X_ELECTRONIC_ACCT_NUM VARCHAR2(100) := null;
2079
2080 -- Bug 14350260 Start
2081 l_party_code xtr_parties_v.party_code%type;
2082 l_bank_code xtr_party_info.party_code%type;
2083 l_city ce_bank_branches_v.city%type;
2084 l_address_line1 ce_bank_branches_v.ADDRESS_LINE1%type;
2085 l_eft_swift_code ce_bank_branches_v.EFT_SWIFT_CODE%type;
2086 x_xtr_api_return_status VARCHAR2(10);
2087 x_xtr_api_msg_count NUMBER;
2088 x_xtr_api_msg_data VARCHAR2(10000);
2089
2090 -- Bug 14350260 End
2091
2092 BEGIN
2093 SAVEPOINT create_bank_acct;
2094
2095 IF l_DEBUG in ('Y', 'C') THEN
2096 cep_standard.debug('>>CE_BANK_PUB.create_bank_acct.');
2097 END IF;
2098
2099 -- initialize message list
2100 IF fnd_api.to_boolean(p_init_msg_list) THEN
2101 fnd_msg_pub.initialize;
2102 END IF;
2103
2104 -- initialize API return status to success.
2105 x_return_status := fnd_api.g_ret_sts_success;
2106
2107 -- first check all required params
2108 -- 12557352: Modified check by calling check_mandatory_param
2109 check_mandatory_param('p_acct_rec.branch_id',p_acct_rec.branch_id);
2110 check_mandatory_param('p_acct_rec.bank_account_name',p_acct_rec.bank_account_name);
2111 check_mandatory_param('p_acct_rec.bank_account_num',p_acct_rec.bank_account_num);
2112 check_mandatory_param('p_acct_rec.account_owner_org_id',p_acct_rec.account_owner_org_id);
2113 check_mandatory_param('p_acct_rec.account_classification',p_acct_rec.account_classification);
2114
2115 -- validate currency
2116 IF p_acct_rec.currency IS NOT NULL THEN
2117 CE_BANK_AND_ACCOUNT_VALIDATION.validate_currency(p_acct_rec.currency, x_return_status);
2118
2119 -- raise an exception if the validation is unsuccessful
2120 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2121 RAISE fnd_api.g_exc_error;
2122 END IF;
2123 END IF;
2124
2125 -- Bug 10183698
2126 -- validate account name
2127 /* CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_name(p_acct_rec.branch_id, p_acct_rec.bank_account_name,
2128 null, x_return_status);
2129
2130 -- raise an exception if the validation is unsuccessful
2131 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2132 RAISE fnd_api.g_exc_error;
2133 END IF; */
2134
2135
2136 -- validate iban
2137 IF p_acct_rec.iban IS NOT NULL THEN
2138 CE_BANK_AND_ACCOUNT_VALIDATION.validate_IBAN(p_acct_rec.iban, l_iban, x_return_status);
2139
2140 -- raise an exception if the validation is unsuccessful
2141 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2142 RAISE fnd_api.g_exc_error;
2143 END IF;
2144 END IF;
2145
2146 -- validate agency_location_code
2147 IF p_acct_rec.agency_location_code IS NOT NULL THEN
2148 CE_BANK_AND_ACCOUNT_VALIDATION.validate_alc(p_acct_rec.agency_location_code,
2149 FND_API.G_FALSE,
2150 x_msg_count,
2151 x_msg_data,
2152 l_alc,
2153 x_return_status);
2154
2155 -- raise an exception if the validation is unsuccessful
2156 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2157 RAISE fnd_api.g_exc_error;
2158 END IF;
2159 END IF;
2160
2161 -- find bank info
2162 IF p_acct_rec.branch_id IS NOT NULL THEN
2163 find_bank_info(p_acct_rec.branch_id, x_return_status, l_bank_id, l_country, l_bank_name, l_bank_number);
2164
2165 -- raise an exception if bank is not found
2166 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2167 RAISE fnd_api.g_exc_error;
2168 END IF;
2169
2170 -- find branch number
2171 OPEN c_branch;
2172 FETCH c_branch INTO l_branch_number;
2173 IF c_branch%NOTFOUND THEN
2174 fnd_message.set_name('CE', 'CE_API_NO_BRANCH');
2175 fnd_msg_pub.add;
2176 x_return_status := fnd_api.g_ret_sts_error;
2177 RAISE fnd_api.g_exc_error;
2178 END IF;
2179 CLOSE c_branch;
2180 END IF;
2181
2182 -- country specific validation API call here.
2183 -- do not perform country specific validations for subsidiary accounts
2184 IF p_acct_rec.account_classification <> 'SUBSIDIARY' THEN
2185 ce_validate_bankinfo.ce_validate_cd (l_country,
2186 p_acct_rec.check_digits,
2187 l_bank_number,
2188 l_branch_number,
2189 p_acct_rec.bank_account_num,
2190 FND_API.G_FALSE,
2191 x_msg_count,
2192 x_msg_data,
2193 x_return_status,
2194 'INTERNAL');
2195
2196 ce_validate_bankinfo.ce_validate_account (l_country,
2197 l_bank_number,
2198 l_branch_number,
2199 p_acct_rec.bank_account_num,
2200 l_bank_id,
2201 p_acct_rec.branch_id,
2202 null, -- account_id
2203 p_acct_rec.currency,
2204 p_acct_rec.acct_type,
2205 p_acct_rec.acct_suffix,
2206 p_acct_rec.secondary_account_reference,
2207 p_acct_rec.bank_account_name,
2208 FND_API.G_FALSE,
2209 x_msg_count,
2210 x_msg_data,
2211 l_account_number,
2212 x_return_status,
2213 'INTERNAL',
2214 p_acct_rec.check_digits,
2215 X_ELECTRONIC_ACCT_NUM
2216 );
2217
2218 -- raise an exception if the validation fails
2219 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2220 RAISE fnd_api.g_exc_error;
2221 END IF;
2222 ELSE -- set the account number for subsidiary accounts
2223 l_account_number := p_acct_rec.bank_account_num;
2224 END IF; -- subsidiary accounts
2225
2226 -- insert data into ce_bank_accounts table
2227 OPEN c_acct_id;
2228 FETCH c_acct_id INTO x_acct_id;
2229 CLOSE c_acct_id;
2230
2231 INSERT INTO CE_BANK_ACCOUNTS (
2232 BANK_ACCOUNT_ID,
2233 BANK_BRANCH_ID,
2234 BANK_ID,
2235 ACCOUNT_OWNER_PARTY_ID,
2236 ACCOUNT_OWNER_ORG_ID,
2237 ACCOUNT_CLASSIFICATION,
2238 BANK_ACCOUNT_NAME,
2239 BANK_ACCOUNT_NUM,
2240 CURRENCY_CODE,
2241 IBAN_NUMBER,
2242 CHECK_DIGITS,
2243 EFT_REQUESTER_IDENTIFIER,
2244 SECONDARY_ACCOUNT_REFERENCE,
2245 MULTI_CURRENCY_ALLOWED_FLAG,
2246 BANK_ACCOUNT_NAME_ALT,
2247 SHORT_ACCOUNT_NAME,
2248 BANK_ACCOUNT_TYPE,
2249 ACCOUNT_SUFFIX,
2250 DESCRIPTION_CODE1,
2251 DESCRIPTION_CODE2,
2252 DESCRIPTION,
2253 AGENCY_LOCATION_CODE,
2254 AP_USE_ALLOWED_FLAG,
2255 AR_USE_ALLOWED_FLAG,
2256 XTR_USE_ALLOWED_FLAG,
2257 PAY_USE_ALLOWED_FLAG,
2258 PAYMENT_MULTI_CURRENCY_FLAG,
2259 RECEIPT_MULTI_CURRENCY_FLAG,
2260 ZERO_AMOUNT_ALLOWED,
2261 MAX_OUTLAY,
2262 MAX_CHECK_AMOUNT,
2263 MIN_CHECK_AMOUNT,
2264 AP_AMOUNT_TOLERANCE,
2265 AR_AMOUNT_TOLERANCE,
2266 XTR_AMOUNT_TOLERANCE,
2267 PAY_AMOUNT_TOLERANCE,
2268 CE_AMOUNT_TOLERANCE,
2269 AP_PERCENT_TOLERANCE,
2270 AR_PERCENT_TOLERANCE,
2271 XTR_PERCENT_TOLERANCE,
2272 PAY_PERCENT_TOLERANCE,
2273 CE_PERCENT_TOLERANCE,
2274 START_DATE,
2275 END_DATE,
2276 ACCOUNT_HOLDER_NAME_ALT,
2277 ACCOUNT_HOLDER_NAME,
2278 CASHFLOW_DISPLAY_ORDER,
2279 POOLED_FLAG,
2280 MIN_TARGET_BALANCE,
2281 MAX_TARGET_BALANCE,
2282 EFT_USER_NUM,
2283 MASKED_ACCOUNT_NUM,
2284 MASKED_IBAN,
2285 INTEREST_SCHEDULE_ID,
2286 ASSET_CODE_COMBINATION_ID,
2287 CASH_CLEARING_CCID,
2288 BANK_CHARGES_CCID,
2289 BANK_ERRORS_CCID,
2290 CASHPOOL_MIN_PAYMENT_AMT,
2291 CASHPOOL_MIN_RECEIPT_AMT,
2292 CASHPOOL_ROUND_FACTOR,
2293 CASHPOOL_ROUND_RULE,
2294 ATTRIBUTE_CATEGORY,
2295 ATTRIBUTE1,
2296 ATTRIBUTE2,
2297 ATTRIBUTE3,
2298 ATTRIBUTE4,
2299 ATTRIBUTE5,
2300 ATTRIBUTE6,
2301 ATTRIBUTE7,
2302 ATTRIBUTE8,
2303 ATTRIBUTE9,
2304 ATTRIBUTE10,
2305 ATTRIBUTE11,
2306 ATTRIBUTE12,
2307 ATTRIBUTE13,
2308 ATTRIBUTE14,
2309 ATTRIBUTE15,
2310 LAST_UPDATE_DATE,
2311 LAST_UPDATED_BY,
2312 LAST_UPDATE_LOGIN,
2313 CREATION_DATE,
2314 CREATED_BY,
2315 OBJECT_VERSION_NUMBER,
2316 xtr_bank_account_reference)
2317 VALUES (
2318 x_acct_id,
2319 p_acct_rec.branch_id,
2320 l_bank_id,
2321 p_acct_rec.account_owner_party_id,
2322 p_acct_rec.account_owner_org_id,
2323 p_acct_rec.account_classification,
2324 p_acct_rec.bank_account_name,
2325 l_account_number,
2326 p_acct_rec.currency,
2327 l_iban,
2328 p_acct_rec.check_digits,
2329 p_acct_rec.eft_requester_id,
2330 p_acct_rec.secondary_account_reference,
2331 p_acct_rec.multi_currency_allowed_flag,
2332 p_acct_rec.alternate_acct_name,
2333 p_acct_rec.short_account_name,
2334 p_acct_rec.acct_type,
2335 p_acct_rec.acct_suffix,
2336 p_acct_rec.description_code1,
2337 p_acct_rec.description_code2,
2338 p_acct_rec.description,
2339 l_alc,
2340 p_acct_rec.ap_use_allowed_flag,
2341 p_acct_rec.ar_use_allowed_flag,
2342 p_acct_rec.xtr_use_allowed_flag,
2343 p_acct_rec.pay_use_allowed_flag,
2344 p_acct_rec.payment_multi_currency_flag,
2345 p_acct_rec.receipt_multi_currency_flag,
2346 p_acct_rec.zero_amount_allowed,
2347 p_acct_rec.max_outlay,
2348 p_acct_rec.max_check_amount,
2349 p_acct_rec.min_check_amount,
2350 p_acct_rec.ap_amount_tolerance,
2351 p_acct_rec.ar_amount_tolerance,
2352 p_acct_rec.xtr_amount_tolerance,
2353 p_acct_rec.pay_amount_tolerance,
2354 p_acct_rec.ce_amount_tolerance,
2355 p_acct_rec.ap_percent_tolerance,
2356 p_acct_rec.ar_percent_tolerance,
2357 p_acct_rec.xtr_percent_tolerance,
2358 p_acct_rec.pay_percent_tolerance,
2359 p_acct_rec.ce_percent_tolerance,
2360 p_acct_rec.start_date,
2361 p_acct_rec.end_date,
2362 p_acct_rec.account_holder_name_alt,
2363 p_acct_rec.account_holder_name,
2364 p_acct_rec.cashflow_display_order,
2365 p_acct_rec.pooled_flag,
2366 p_acct_rec.min_target_balance,
2367 p_acct_rec.max_target_balance,
2368 p_acct_rec.eft_user_num,
2369 p_acct_rec.masked_account_num,
2370 p_acct_rec.masked_iban,
2371 p_acct_rec.interest_schedule_id,
2372 p_acct_rec.asset_code_combination_id,
2373 p_acct_rec.cash_clearing_ccid,
2374 p_acct_rec.bank_charges_ccid,
2375 p_acct_rec.bank_errors_ccid,
2376 p_acct_rec.cashpool_min_payment_amt,
2377 p_acct_rec.cashpool_min_receipt_amt,
2378 p_acct_rec.cashpool_round_factor,
2379 p_acct_rec.cashpool_round_rule,
2380 p_acct_rec.attribute_category,
2381 p_acct_rec.attribute1,
2382 p_acct_rec.attribute2,
2383 p_acct_rec.attribute3,
2384 p_acct_rec.attribute4,
2385 p_acct_rec.attribute5,
2386 p_acct_rec.attribute6,
2387 p_acct_rec.attribute7,
2388 p_acct_rec.attribute8,
2389 p_acct_rec.attribute9,
2390 p_acct_rec.attribute10,
2391 p_acct_rec.attribute11,
2392 p_acct_rec.attribute12,
2393 p_acct_rec.attribute13,
2394 p_acct_rec.attribute14,
2395 p_acct_rec.attribute15,
2396 sysdate,
2397 NVL(FND_GLOBAL.user_id,-1),
2398 NVL(FND_GLOBAL.login_id, -1),
2399 sysdate,
2400 NVL(FND_GLOBAL.user_id,-1),
2401 1,
2402 p_acct_rec.xtr_bank_account_reference);
2403 OPEN c_acct_rowid;
2404 FETCH c_acct_rowid INTO l_acct_rowid;
2405 If (c_acct_rowid%NOTFOUND) then
2406 CLOSE c_acct_rowid;
2407 RAISE NO_DATA_FOUND;
2408 End If;
2409 CLOSE c_acct_rowid;
2410
2411 -- Bug 14350260 Start
2412 -- Added a call to xtr API to insert XTR part of the data.
2413 cep_standard.debug('Starting XTR API Call...');
2414
2415 cep_standard.debug('p_acct_rec.xtr_use_allowed_flag = ' || p_acct_rec.xtr_use_allowed_flag);
2416 if p_acct_rec.xtr_use_allowed_flag = 'Y' then
2417
2418 cep_standard.debug('p_acct_rec.account_owner_org_id = ' || p_acct_rec.account_owner_org_id);
2419
2420 select party_code
2421 into l_party_code
2422 from xtr_parties_v
2423 where legal_entity_id = p_acct_rec.account_owner_org_id
2424 and authorised = 'Y';
2425 cep_standard.debug('l_party_code = ' || l_party_code);
2426 cep_standard.debug('x_acct_id = ' || x_acct_id);
2427 cep_standard.debug('p_acct_rec.branch_id = ' || p_acct_rec.branch_id);
2428
2429 select party_code
2430 into l_bank_code
2431 from xtr_party_info
2432 where ce_bank_branch_id = p_acct_rec.branch_id;
2433 cep_standard.debug('l_bank_code = ' || l_bank_code);
2434
2435
2436
2437 select city, ADDRESS_LINE1, EFT_SWIFT_CODE
2438 into l_city, l_ADDRESS_LINE1, l_EFT_SWIFT_CODE
2439 from ce_bank_branches_v
2440 where BRANCH_PARTY_ID =p_acct_rec.branch_id;
2441 cep_standard.debug('l_city = ' || l_city);
2442 cep_standard.debug('l_ADDRESS_LINE1 = ' || l_ADDRESS_LINE1);
2443 cep_standard.debug('l_EFT_SWIFT_CODE = ' || l_EFT_SWIFT_CODE);
2444
2445 XTR_REPLICATE_BANK_ACCOUNTS_P.REPLICATE_BANK_ACCOUNTS(
2446 l_account_number, -- p_account_number
2447 NULL, -- p_authorised
2448 l_party_code, -- p_party_code
2449 'C', -- p_party_type
2450 l_bank_code, -- p_bank_code
2451 p_acct_rec.currency, -- p_currency
2452 p_acct_rec.bank_account_name, -- p_bank_short_code
2453 NULL, -- p_default_acct
2454 NULL, -- p_eft_script_name
2455 NULL, -- p_code_combination_id
2456 NULL, -- p_intereste_calculation_basis
2457 l_city, -- p_location
2458 NULL, -- p_portfolio_code
2459 NULL, -- p_primary_settlement_method
2460 l_ADDRESS_LINE1 , -- p_street
2461 NULL, -- p_year_calc_type
2462 l_EFT_SWIFT_CODE, -- p_swift_id
2463 p_acct_rec.attribute_category, -- p_attribute_category
2464 p_acct_rec.attribute1, -- p_attribute1
2465 p_acct_rec.attribute2, -- p_attribute2
2466 p_acct_rec.attribute3, -- p_attribute3
2467 p_acct_rec.attribute4, -- p_attribute4
2468 p_acct_rec.attribute5, -- p_attribute5
2469 p_acct_rec.attribute6, -- p_attribute6
2470 p_acct_rec.attribute7, -- p_attribute7
2471 p_acct_rec.attribute8, -- p_attribute8
2472 p_acct_rec.attribute9, -- p_attribute9
2473 p_acct_rec.attribute10, -- p_attribute10
2474 p_acct_rec.attribute11, -- p_attribute11
2475 p_acct_rec.attribute12, -- p_attribute12
2476 p_acct_rec.attribute13, -- p_attribute13
2477 p_acct_rec.attribute14, -- p_attribute14
2478 p_acct_rec.attribute15, -- p_attribute15
2479 NULL, -- p_pricing_model
2480 p_acct_rec.short_account_name, -- p_legal_account_name
2481 x_acct_id, -- p_ce_bank_account_id
2482 p_acct_rec.branch_id, -- p_bank_branch_id
2483 NULL, -- p_bank_acct_use_id
2484 'I', -- p_action_flag
2485 x_xtr_api_return_status, -- x_return_status OUT
2486 x_xtr_api_msg_count, -- x_msg_count OUT
2487 x_xtr_api_msg_data, -- x_msg_data OUT
2488 l_iban
2489 );
2490 cep_standard.debug('Call to XTR API XTR_REPLICATE_BANK_ACCOUNTS_P.REPLICATE_BANK_ACCOUNTS Sucessfully Completed...');
2491 if x_xtr_api_return_status<>FND_API.G_RET_STS_SUCCESS then
2492 cep_standard.debug('Call to XTR API XTR_REPLICATE_BANK_ACCOUNTS_P.REPLICATE_BANK_ACCOUNTS has failed...');
2493 RAISE fnd_api.g_exc_error;
2494 end if;
2495 end if;
2496 -- Bug 14350260 End
2497
2498 -- get message count and if count is 1, get message info.
2499 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2500 p_count => x_msg_count,
2501 p_data => x_msg_data);
2502
2503 IF l_DEBUG in ('Y', 'C') THEN
2504 cep_standard.debug('<<CE_BANK_PUB.create_bank_acct.');
2505 END IF;
2506
2507 EXCEPTION
2508 WHEN fnd_api.g_exc_error THEN
2509 ROLLBACK TO create_bank_acct;
2510 x_return_status := fnd_api.g_ret_sts_error;
2511 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2512 p_count => x_msg_count,
2513 p_data => x_msg_data);
2514
2515 IF l_DEBUG in ('Y', 'C') THEN
2516 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
2517 END IF;
2518
2519 WHEN fnd_api.g_exc_unexpected_error THEN
2520 ROLLBACK TO create_bank_acct;
2521 x_return_status := fnd_api.g_ret_sts_unexp_error;
2522 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2523 p_count => x_msg_count,
2524 p_data => x_msg_data);
2525
2526 IF l_DEBUG in ('Y', 'C') THEN
2527 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
2528 END IF;
2529
2530 WHEN OTHERS THEN
2531 ROLLBACK TO create_bank_acct;
2532 x_return_status := fnd_api.g_ret_sts_unexp_error;
2533 fnd_message.set_name('CE', 'CE_API_OTHERS_EXCEP');
2534 fnd_message.set_token('ERROR',SQLERRM);
2535 fnd_msg_pub.add;
2536 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2537 p_count => x_msg_count,
2538 p_data => x_msg_data);
2539
2540 IF l_DEBUG in ('Y', 'C') THEN
2541 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
2542 END IF;
2543
2544 END create_bank_acct;
2545
2546 /*=======================================================================+
2547 | PRIVATE FUNCTION get_masked_code |
2548 | |
2549 | DESCRIPTION |
2550 | Get the maked bank account number for the given bank account number |
2551 | |
2552 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2553 | |
2554 | MODIFICATION HISTORY |
2555 | 10-APR-2009 TALAPATI Created. |
2556 +=======================================================================*/
2557
2558 FUNCTION get_masked_code (
2559 p_bank_account_num IN VARCHAR2) RETURN VARCHAR2 IS
2560 l_maskChar VARCHAR2(100):= 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
2561 l_maskOption VARCHAR2(30);
2562 l_acctNumLen NUMBER;
2563 l_replaceMaskChar VARCHAR2(100);
2564 l_newMaskedValues VARCHAR2(100);
2565 BEGIN
2566
2567 l_maskoption:=fnd_profile.value('CE_MASK_INTERNAL_BANK_ACCT_NUM');
2568 if l_maskOption is null then
2569 l_maskOption:= 'LAST FOUR VISIBLE';
2570 end if;
2571 l_acctNumLen:= length(p_bank_account_num);
2572 if l_maskOption='LAST FOUR VISIBLE' and l_acctNumLen > 4 then
2573 l_replaceMaskChar:= substr(l_maskChar,1,l_acctNumLen-4);
2574 l_newMaskedValues:= l_replaceMaskChar||substr(p_bank_account_num,-4,4);
2575
2576 elsif l_maskOption='FIRST FOUR VISIBLE' and l_acctNumLen > 4 then
2577
2578 l_replaceMaskChar:= substr(l_maskChar,1,l_acctNumLen-4);
2579 l_newMaskedValues:= substr(p_bank_account_num,1,4)||l_replaceMaskChar;
2580 else
2581 l_newMaskedValues:= p_bank_account_num;
2582 end if;
2583 RETURN l_newMaskedValues;
2584 END get_masked_code;
2585
2586
2587 /*=======================================================================+
2588 | PUBLIC PROCEDURE update_bank_acct |
2589 | |
2590 | DESCRIPTION |
2591 | Update an internal or subsidiary bank account. |
2592 | |
2593 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2594 | |
2595 | ARGUMENTS |
2596 | IN: |
2597 | p_init_msg_list Initialize message stack if it is set to |
2598 | FND_API.G_TRUE. Default is fnd_api.g_false
2599 | p_acct_rec External bank account record. |
2600 | IN/OUT: |
2601 | p_object_version_number Current object version number for the |
2602 | bank account. |
2603 | OUT: |
2604 | x_return_status Return status after the call. The status can |
2605 | be FND_API.G_RET_STS_SUCCESS (success), |
2606 | fnd_api.g_ret_sts_error (error), |
2607 | fnd_api.g_ret_sts_unexp_error (unexpected |
2608 | error). |
2609 | x_msg_count Number of messages in message stack. |
2610 | x_msg_data Message text if x_msg_count is 1. |
2611 | MODIFICATION HISTORY |
2612 | 25-AUG-2004 Xin Wang Created. |
2613 | 10-APR-2009 TALAPATI Updated certain validation rules for bug |
2614 | #8407297 |
2615 | |
2616 +=======================================================================*/
2617 PROCEDURE update_bank_acct (
2618 p_init_msg_list IN VARCHAR2:= fnd_api.g_false,
2619 p_acct_rec IN BankAcct_rec_type,
2620 p_object_version_number IN OUT NOCOPY NUMBER,
2621 x_return_status OUT NOCOPY VARCHAR2,
2622 x_msg_count OUT NOCOPY NUMBER,
2623 x_msg_data OUT NOCOPY VARCHAR2
2624 ) IS
2625 CURSOR c_branch_id IS
2626 SELECT bank_branch_id
2627 FROM ce_bank_accounts
2628 WHERE bank_account_id = p_acct_rec.bank_account_id;
2629
2630 CURSOR c_branch_num(l_branch_id NUMBER) IS
2631 SELECT bank_or_branch_number
2632 FROM hz_organization_profiles
2633 WHERE SYSDATE between TRUNC(effective_start_date)
2634 and NVL(TRUNC(effective_end_date), SYSDATE+1)
2635 AND party_id = l_branch_id;
2636
2637 CURSOR c_ovn IS
2638 SELECT object_version_number
2639 FROM ce_bank_accounts
2640 WHERE bank_account_id = p_acct_rec.bank_account_id;
2641
2642 CURSOR c_bank_account IS
2643 SELECT *
2644 FROM ce_bank_accounts
2645 WHERE bank_account_id = p_acct_rec.bank_account_id;
2646
2647 l_iban VARCHAR2(50) := null;
2648 l_alc VARCHAR2(30) := null;
2649 l_country VARCHAR2(60);
2650 l_bank_name VARCHAR2(360);
2651 l_bank_number VARCHAR2(30);
2652 l_branch_number VARCHAR2(30);
2653 l_account_number VARCHAR2(100) := null;
2654 l_bank_id NUMBER(15);
2655 l_branch_id NUMBER(15);
2656 l_old_ovn NUMBER(15);
2657 X_ELECTRONIC_ACCT_NUM VARCHAR2(100) := null;
2658 --Bug 8407297
2659 l_bank_acct_rec_master CE_BANK_ACCOUNTS%ROWTYPE;
2660 l_ap_use_allowed_flag VARCHAR2(1);
2661 l_ar_use_allowed_flag VARCHAR2(1);
2662 l_xtr_use_allowed_flag VARCHAR2(1);
2663 l_pay_use_allowed_flag VARCHAR2(1);
2664 l_currency_code VARCHAR2(15);
2665 l_masked_bank_acct_num VARCHAR2(100);
2666
2667
2668 BEGIN
2669 SAVEPOINT update_bank_acct;
2670
2671 IF l_DEBUG in ('Y', 'C') THEN
2672 cep_standard.debug('>>CE_BANK_PUB.update_bank_acct.');
2673 END IF;
2674
2675 -- initialize message list
2676 IF fnd_api.to_boolean(p_init_msg_list) THEN
2677 fnd_msg_pub.initialize;
2678 END IF;
2679
2680 -- initialize API return status to success.
2681 x_return_status := fnd_api.g_ret_sts_success;
2682
2683 -- first check all required params
2684 -- 12557352: Modified check by calling check_mandatory_param
2685 check_mandatory_param('p_acct_rec.bank_account_id',p_acct_rec.bank_account_id);
2686 check_mandatory_param('p_acct_rec.bank_account_name',p_acct_rec.bank_account_name);
2687 check_mandatory_param('p_acct_rec.bank_account_num',p_acct_rec.bank_account_num);
2688 check_mandatory_param('p_acct_rec.account_classification',p_acct_rec.account_classification);
2689 check_mandatory_param('p_object_version_number',p_object_version_number);
2690
2691 --Bug 8407297
2692 OPEN c_bank_account;
2693 FETCH c_bank_account INTO l_bank_acct_rec_master;
2694 IF c_bank_account%NOTFOUND THEN
2695 fnd_message.set_name('CE', 'CE_API_NO_ACCOUNT');
2696 fnd_msg_pub.add;
2697 x_return_status := fnd_api.g_ret_sts_error;
2698 CLOSE c_bank_account;
2699 RAISE fnd_api.g_exc_error;
2700 END IF;
2701 CLOSE c_bank_account;
2702
2703
2704 if l_bank_acct_rec_master.ap_use_allowed_flag='N' then
2705 if p_acct_rec.ap_use_allowed_flag is not null and p_acct_rec.ap_use_allowed_flag='Y' then
2706 l_ap_use_allowed_flag:= p_acct_rec.ap_use_allowed_flag;
2707 else
2708 l_ap_use_allowed_flag:= l_bank_acct_rec_master.ap_use_allowed_flag;
2709 end if;
2710 else
2711 l_ap_use_allowed_flag:= l_bank_acct_rec_master.ap_use_allowed_flag;
2712 end if;
2713
2714 if l_bank_acct_rec_master.ar_use_allowed_flag='N' then
2715 if p_acct_rec.ar_use_allowed_flag is not null and p_acct_rec.ar_use_allowed_flag='Y' then
2716 l_ar_use_allowed_flag:= p_acct_rec.ar_use_allowed_flag;
2717 else
2718 l_ar_use_allowed_flag:= l_bank_acct_rec_master.ar_use_allowed_flag;
2719 end if;
2720 else
2721 l_ar_use_allowed_flag:= l_bank_acct_rec_master.ar_use_allowed_flag;
2722 end if;
2723
2724 if l_bank_acct_rec_master.xtr_use_allowed_flag='N' then
2725 if p_acct_rec.xtr_use_allowed_flag is not null and p_acct_rec.xtr_use_allowed_flag='Y' then
2726 l_xtr_use_allowed_flag:= p_acct_rec.xtr_use_allowed_flag;
2727 else
2728 l_xtr_use_allowed_flag:= l_bank_acct_rec_master.xtr_use_allowed_flag;
2729 end if;
2730 else
2731 l_xtr_use_allowed_flag:= l_bank_acct_rec_master.xtr_use_allowed_flag;
2732 end if;
2733
2734 if l_bank_acct_rec_master.pay_use_allowed_flag='N' then
2735 if p_acct_rec.pay_use_allowed_flag is not null and p_acct_rec.pay_use_allowed_flag='Y' then
2736 l_pay_use_allowed_flag:= p_acct_rec.pay_use_allowed_flag;
2737 else
2738 l_pay_use_allowed_flag:= l_bank_acct_rec_master.pay_use_allowed_flag;
2739 end if;
2740 else
2741 l_pay_use_allowed_flag:= l_bank_acct_rec_master.pay_use_allowed_flag;
2742 end if;
2743
2744 if p_acct_rec.multi_currency_allowed_flag is not null and p_acct_rec.multi_currency_allowed_flag='Y' then
2745 SELECT currency_code into l_currency_code
2746 FROM gl_ledger_le_v
2747 WHERE ledger_category_code = 'PRIMARY' and
2748 legal_entity_id = l_bank_acct_rec_master.account_owner_org_id;
2749
2750 -- Validate Multi-Currency Allowed or not bug 8407297
2751 if l_currency_code <> l_bank_acct_rec_master.currency_code then
2752 fnd_message.set_name('CE', 'CE_BANK_MULTICURRENCY');
2753 fnd_msg_pub.add;
2754 x_return_status := fnd_api.g_ret_sts_error;
2755 RAISE fnd_api.g_exc_error;
2756 end if;
2757 end if;
2758
2759
2760 -- validate currency
2761 --Bug 8407297 - Call to the Currency Validation not required as the Currency field is protected against update
2762 /*
2763 IF p_acct_rec.currency IS NOT NULL THEN
2764 CE_BANK_AND_ACCOUNT_VALIDATION.validate_currency(p_acct_rec.currency, x_return_status);
2765
2766 -- raise an exception if the validation is unsuccessful
2767 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2768 RAISE fnd_api.g_exc_error;
2769 END IF;
2770 END IF;
2771 */
2772 -- validate account name
2773 OPEN c_branch_id;
2774 FETCH c_branch_id INTO l_branch_id;
2775 IF c_branch_id%NOTFOUND THEN
2776 fnd_message.set_name('CE', 'CE_API_NO_ACCOUNT');
2777 fnd_msg_pub.add;
2778 x_return_status := fnd_api.g_ret_sts_error;
2779 CLOSE c_branch_id;
2780 RAISE fnd_api.g_exc_error;
2781 END IF;
2782 CLOSE c_branch_id;
2783 -- Bug 10261393
2784 /* CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_name(l_branch_id, p_acct_rec.bank_account_name,
2785 p_acct_rec.bank_account_id, x_return_status);
2786
2787 -- raise an exception if the validation is unsuccessful
2788 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2789 RAISE fnd_api.g_exc_error;
2790 END IF; */
2791
2792 -- validate iban
2793 IF p_acct_rec.iban IS NOT NULL THEN
2794 CE_BANK_AND_ACCOUNT_VALIDATION.validate_IBAN(p_acct_rec.iban, l_iban, x_return_status);
2795
2796 -- raise an exception if the validation is unsuccessful
2797 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2798 RAISE fnd_api.g_exc_error;
2799 END IF;
2800 END IF;
2801
2802 -- validate agency_location_code
2803 IF p_acct_rec.agency_location_code IS NOT NULL THEN
2804 CE_BANK_AND_ACCOUNT_VALIDATION.validate_alc(p_acct_rec.agency_location_code,
2805 FND_API.G_FALSE,
2806 x_msg_count,
2807 x_msg_data,
2808 l_alc,
2809 x_return_status);
2810
2811 -- raise an exception if the validation is unsuccessful
2812 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2813 RAISE fnd_api.g_exc_error;
2814 END IF;
2815 END IF;
2816
2817 -- find bank info
2818 IF l_branch_id IS NOT NULL THEN
2819 find_bank_info(l_branch_id, x_return_status, l_bank_id, l_country, l_bank_name, l_bank_number);
2820 -- raise an exception if bank is not found
2821 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2822 RAISE fnd_api.g_exc_error;
2823 END IF;
2824
2825 -- find branch number
2826 OPEN c_branch_num(l_branch_id);
2827 FETCH c_branch_num INTO l_branch_number;
2828 IF c_branch_num%NOTFOUND THEN
2829 fnd_message.set_name('CE', 'CE_API_NO_BRANCH');
2830 fnd_msg_pub.add;
2831 x_return_status := fnd_api.g_ret_sts_error;
2832 CLOSE c_branch_num;
2833 RAISE fnd_api.g_exc_error;
2834 END IF;
2835 CLOSE c_branch_num;
2836 END IF;
2837
2838 -- country specific validation API call here.
2839 -- do not perform country specific validations for subsidiary accounts
2840 IF p_acct_rec.account_classification <> 'SUBSIDIARY' THEN
2841 ce_validate_bankinfo.ce_validate_cd (l_country,
2842 p_acct_rec.check_digits,
2843 l_bank_number,
2844 l_branch_number,
2845 p_acct_rec.bank_account_num,
2846 FND_API.G_FALSE,
2847 x_msg_count,
2848 x_msg_data,
2849 x_return_status,
2850 'INTERNAL');
2851
2852 ce_validate_bankinfo.ce_validate_account (l_country,
2853 l_bank_number,
2854 l_branch_number,
2855 p_acct_rec.bank_account_num,
2856 l_bank_id,
2857 l_branch_id,
2858 p_acct_rec.bank_account_id,
2859 l_bank_acct_rec_master.currency_code, -- Bug 10261393
2860 p_acct_rec.acct_type,
2861 p_acct_rec.acct_suffix,
2862 p_acct_rec.secondary_account_reference,
2863 p_acct_rec.bank_account_name,
2864 FND_API.G_FALSE,
2865 x_msg_count,
2866 x_msg_data,
2867 l_account_number,
2868 x_return_status,
2869 'INTERNAL',
2870 p_acct_rec.check_digits,
2871 X_ELECTRONIC_ACCT_NUM);
2872
2873 -- raise an exception if the validation fails
2874 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2875 RAISE fnd_api.g_exc_error;
2876 END IF;
2877 ELSE -- set the account number for subsidiary accounts
2878 l_account_number := p_acct_rec.bank_account_num;
2879 END IF; -- subsidiary account
2880
2881 -- check object version number to make sure the record has not been updated
2882 OPEN c_ovn;
2883 FETCH c_ovn INTO l_old_ovn;
2884 IF c_ovn%NOTFOUND THEN
2885 fnd_message.set_name('CE', 'CE_API_NO_ACCOUNT');
2886 fnd_msg_pub.add;
2887 x_return_status := fnd_api.g_ret_sts_error;
2888 CLOSE c_ovn;
2889 RAISE fnd_api.g_exc_error;
2890 END IF;
2891 CLOSE c_ovn;
2892
2893 IF l_old_ovn > p_object_version_number THEN
2894 fnd_message.set_name('CE', 'CE_API_RECORD_CHANGED');
2895 fnd_message.set_token('TABLE', 'CE_BANK_ACCOUNTS');
2896 fnd_msg_pub.add;
2897 x_return_status := fnd_api.g_ret_sts_error;
2898 RAISE fnd_api.g_exc_error;
2899 END IF;
2900
2901 -- Bug 8407297 Get the Masked value of the Bank Account Number
2902 if p_acct_rec.masked_account_num is null then
2903 l_masked_bank_acct_num:= get_masked_code(l_account_number);
2904 else
2905 l_masked_bank_acct_num:= p_acct_rec.masked_account_num;
2906 end if;
2907
2908 -- update
2909 update ce_bank_accounts
2910 set bank_account_name = p_acct_rec.bank_account_name,
2911 bank_account_num = l_account_number,
2912 --currency_code = p_acct_rec.currency, --Bug 8407297
2913 iban_number = l_iban,
2914 check_digits = p_acct_rec.check_digits,
2915 eft_requester_identifier = p_acct_rec.eft_requester_id,
2916 secondary_account_reference = p_acct_rec.secondary_account_reference,
2917 multi_currency_allowed_flag = nvl(p_acct_rec.multi_currency_allowed_flag,l_bank_acct_rec_master.multi_currency_allowed_flag),
2918 bank_account_name_alt = p_acct_rec.alternate_acct_name,
2919 short_account_name = p_acct_rec.short_account_name,
2920 bank_account_type = p_acct_rec.acct_type,
2921 account_suffix = p_acct_rec.acct_suffix,
2922 description_code1 = p_acct_rec.description_code1,
2923 description_code2 = p_acct_rec.description_code2,
2924 description = p_acct_rec.description,
2925 agency_location_code = l_alc,
2926 ap_use_allowed_flag = l_ap_use_allowed_flag, --Bug 8407297
2927 ar_use_allowed_flag = l_ar_use_allowed_flag, --Bug 8407297
2928 xtr_use_allowed_flag = l_xtr_use_allowed_flag, --Bug 8407297
2929 pay_use_allowed_flag = l_pay_use_allowed_flag, --Bug 8407297
2930 payment_multi_currency_flag = p_acct_rec.payment_multi_currency_flag,
2931 receipt_multi_currency_flag = p_acct_rec.receipt_multi_currency_flag,
2932 zero_amount_allowed = nvl(p_acct_rec.zero_amount_allowed,l_bank_acct_rec_master.zero_amount_allowed),
2933 max_outlay = p_acct_rec.max_outlay,
2934 max_check_amount = p_acct_rec.max_check_amount,
2935 min_check_amount = p_acct_rec.min_check_amount,
2936 ap_amount_tolerance = nvl(p_acct_rec.ap_amount_tolerance,l_bank_acct_rec_master.ap_amount_tolerance),
2937 ar_amount_tolerance = nvl(p_acct_rec.ar_amount_tolerance,l_bank_acct_rec_master.ar_amount_tolerance),
2938 xtr_amount_tolerance = nvl(p_acct_rec.xtr_amount_tolerance,l_bank_acct_rec_master.xtr_amount_tolerance),
2939 pay_amount_tolerance = nvl(p_acct_rec.pay_amount_tolerance,l_bank_acct_rec_master.pay_amount_tolerance),
2940 ce_amount_tolerance = p_acct_rec.ce_amount_tolerance,
2941 ap_percent_tolerance = nvl(p_acct_rec.ap_percent_tolerance,l_bank_acct_rec_master.ap_percent_tolerance),
2942 ar_percent_tolerance = nvl(p_acct_rec.ar_percent_tolerance,l_bank_acct_rec_master.ar_percent_tolerance),
2943 xtr_percent_tolerance = nvl(p_acct_rec.xtr_percent_tolerance,l_bank_acct_rec_master.xtr_percent_tolerance),
2944 pay_percent_tolerance = p_acct_rec.pay_percent_tolerance,
2945 ce_percent_tolerance = p_acct_rec.ce_percent_tolerance,
2946 start_date = p_acct_rec.start_date,
2947 end_date = p_acct_rec.end_date,
2948 account_holder_name_alt = p_acct_rec.account_holder_name_alt,
2949 account_holder_name = p_acct_rec.account_holder_name,
2950 cashflow_display_order = p_acct_rec.cashflow_display_order,
2951 pooled_flag = nvl(p_acct_rec.pooled_flag,l_bank_acct_rec_master.pooled_flag),
2952 min_target_balance = p_acct_rec.min_target_balance,
2953 max_target_balance = p_acct_rec.max_target_balance,
2954 eft_user_num = p_acct_rec.eft_user_num,
2955 masked_account_num = l_masked_bank_acct_num,
2956 BANK_ACCOUNT_NUM_ELECTRONIC = l_account_number,
2957 masked_iban = p_acct_rec.masked_iban,
2958 interest_schedule_id = p_acct_rec.interest_schedule_id,
2959 asset_code_combination_id = nvl(p_acct_rec.asset_code_combination_id,l_bank_acct_rec_master.asset_code_combination_id),
2960 cash_clearing_ccid = p_acct_rec.cash_clearing_ccid,
2961 bank_charges_ccid = p_acct_rec.bank_charges_ccid,
2962 bank_errors_ccid = p_acct_rec.bank_errors_ccid,
2963 cashpool_min_payment_amt = p_acct_rec.cashpool_min_payment_amt,
2964 cashpool_min_receipt_amt = p_acct_rec.cashpool_min_receipt_amt,
2965 cashpool_round_factor = p_acct_rec.cashpool_round_factor,
2966 cashpool_round_rule = p_acct_rec.cashpool_round_rule,
2967 attribute_category = p_acct_rec.attribute_category,
2968 attribute1 = p_acct_rec.attribute1,
2969 attribute2 = p_acct_rec.attribute2,
2970 attribute3 = p_acct_rec.attribute3,
2971 attribute4 = p_acct_rec.attribute4,
2972 attribute5 = p_acct_rec.attribute5,
2973 attribute6 = p_acct_rec.attribute6,
2974 attribute7 = p_acct_rec.attribute7,
2975 attribute8 = p_acct_rec.attribute8,
2976 attribute9 = p_acct_rec.attribute9,
2977 attribute10 = p_acct_rec.attribute10,
2978 attribute11 = p_acct_rec.attribute11,
2979 attribute12 = p_acct_rec.attribute12,
2980 attribute13 = p_acct_rec.attribute13,
2981 attribute14 = p_acct_rec.attribute14,
2982 attribute15 = p_acct_rec.attribute15,
2983 last_update_date = sysdate,
2984 last_update_login = NVL(FND_GLOBAL.login_id,-1),
2985 last_updated_by = NVL(FND_GLOBAL.user_id,-1),
2986 object_version_number = l_old_ovn + 1,
2987 xtr_bank_account_reference = p_acct_rec.xtr_bank_account_reference
2988 WHERE bank_account_id = p_acct_rec.bank_account_id;
2989 IF (SQL%NOTFOUND) THEN
2990 fnd_message.set_name('CE', 'CE_API_NO_ACCOUNT');
2991 fnd_msg_pub.add;
2992 x_return_status := fnd_api.g_ret_sts_error;
2993 RAISE fnd_api.g_exc_error;
2994 END IF;
2995 p_object_version_number := l_old_ovn + 1;
2996
2997 -- get message count and if count is 1, get message info.
2998 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2999 p_count => x_msg_count,
3000 p_data => x_msg_data);
3001
3002 IF l_DEBUG in ('Y', 'C') THEN
3003 cep_standard.debug('<<CE_BANK_PUB.update_bank_acct.');
3004 END IF;
3005
3006 EXCEPTION
3007 WHEN fnd_api.g_exc_error THEN
3008 ROLLBACK TO update_bank_acct;
3009 x_return_status := fnd_api.g_ret_sts_error;
3010 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3011 p_count => x_msg_count,
3012 p_data => x_msg_data);
3013
3014 IF l_DEBUG in ('Y', 'C') THEN
3015 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
3016 END IF;
3017
3018 WHEN fnd_api.g_exc_unexpected_error THEN
3019 ROLLBACK TO update_bank_acct;
3020 x_return_status := fnd_api.g_ret_sts_unexp_error;
3021 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3022 p_count => x_msg_count,
3023 p_data => x_msg_data);
3024
3025 IF l_DEBUG in ('Y', 'C') THEN
3026 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
3027 END IF;
3028
3029 WHEN OTHERS THEN
3030 ROLLBACK TO udpate_bank_acct;
3031 x_return_status := fnd_api.g_ret_sts_unexp_error;
3032 fnd_message.set_name('CE', 'CE_API_OTHERS_EXCEP');
3033 fnd_message.set_token('ERROR',SQLERRM);
3034 fnd_msg_pub.add;
3035 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3036 p_count => x_msg_count,
3037 p_data => x_msg_data);
3038
3039 IF l_DEBUG in ('Y', 'C') THEN
3040 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
3041 END IF;
3042
3043 END update_bank_acct;
3044
3045
3046 PROCEDURE create_bank_acct_use (
3047 p_init_msg_list IN VARCHAR2:= fnd_api.g_false,
3048 p_acct_use_rec IN BankAcct_use_rec_type,
3049 x_acct_use_id OUT NOCOPY NUMBER,
3050 x_return_status OUT NOCOPY VARCHAR2,
3051 x_msg_count OUT NOCOPY NUMBER,
3052 x_msg_data OUT NOCOPY VARCHAR2
3053 ) IS
3054 CURSOR c_acct_use_id IS
3055 SELECT CE_BANK_ACCT_USES_S.nextval
3056 FROM sys.dual;
3057 CURSOR c_acct_use_rowid IS
3058 SELECT rowid
3059 FROM CE_BANK_ACCT_USES_ALL
3060 WHERE bank_acct_use_id = x_acct_use_id;
3061 CURSOR c_gl_ccid_rowid IS
3062 SELECT rowid
3063 FROM CE_GL_ACCOUNTS_CCID
3064 WHERE bank_acct_use_id = x_acct_use_id;
3065
3066 l_acct_use_rowid VARCHAR2(100);
3067 l_gl_ccid_rowid VARCHAR2(100);
3068 l_org_le_id NUMBER;
3069 BEGIN
3070 SAVEPOINT create_bank_acct_use;
3071
3072 IF l_DEBUG in ('Y', 'C') THEN
3073 cep_standard.debug('>>CE_BANK_PUB.create_bank_acct_use.');
3074 END IF;
3075
3076 -- initialize message list
3077 IF fnd_api.to_boolean(p_init_msg_list) THEN
3078 fnd_msg_pub.initialize;
3079 END IF;
3080
3081 -- initialize API return status to success.
3082 x_return_status := fnd_api.g_ret_sts_success;
3083
3084 -- first check all required params
3085 -- 12557352: Modified check by calling check_mandatory_param
3086 check_mandatory_param('p_acct_use_rec.bank_account_id',p_acct_use_rec.bank_account_id);
3087 check_mandatory_param('p_acct_use_rec.org_type',p_acct_use_rec.org_type);
3088 -- either legal_entity_id or org_id is required.
3089 -- error message will only show 'org_id is required'
3090 IF p_acct_use_rec.legal_entity_id IS NULL THEN
3091 check_mandatory_param('p_acct_use_rec.org_id',p_acct_use_rec.org_id);
3092 END IF;
3093
3094 -- GL Cash Account is required if the account use is Payables or Receivables
3095 IF ((p_acct_use_rec.ap_use_enable_flag = 'Y' or
3096 p_acct_use_rec.ar_use_enable_flag = 'Y') and
3097 p_acct_use_rec.asset_code_combination_id is null) THEN -- bug 7671686
3098 fnd_message.set_name('CE', 'CE_API_CASH_CCID_REQUIRED');
3099 fnd_msg_pub.add;
3100 x_return_status := fnd_api.g_ret_sts_error;
3101 RAISE fnd_api.g_exc_error;
3102 END IF;
3103
3104 -- validate account use
3105 CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_use
3106 (p_acct_use_rec.ap_use_enable_flag,
3107 p_acct_use_rec.ar_use_enable_flag,
3108 p_acct_use_rec.pay_use_enable_flag,
3109 p_acct_use_rec.xtr_use_enable_flag,
3110 x_return_status);
3111
3112 -- raise an exception if the validation is unsuccessful
3113 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3114 RAISE fnd_api.g_exc_error;
3115 END IF;
3116
3117 -- validate the combination of bank_account_id and org_id/legal_entity_id is unique
3118 IF p_acct_use_rec.org_type = 'LE' THEN
3119 l_org_le_id := p_acct_use_rec.legal_entity_id;
3120 ELSE
3121 l_org_le_id := p_acct_use_rec.org_id;
3122 END IF;
3123
3124 CE_BANK_AND_ACCOUNT_VALIDATION.validate_unique_org_access
3125 (l_org_le_id,
3126 p_acct_use_rec.bank_account_id,
3127 null,
3128 x_return_status);
3129 -- raise an exception if the validation is unsuccessful
3130 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3131 RAISE fnd_api.g_exc_error;
3132 END IF;
3133
3134 -- validate the org type matches with the account use
3135 CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_access_org
3136 (p_acct_use_rec.ap_use_enable_flag,
3137 p_acct_use_rec.ar_use_enable_flag,
3138 p_acct_use_rec.pay_use_enable_flag,
3139 p_acct_use_rec.xtr_use_enable_flag,
3140 p_acct_use_rec.org_type,
3141 l_org_le_id,
3142 x_return_status);
3143 -- raise an exception if the validation is unsuccessful
3144 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3145 RAISE fnd_api.g_exc_error;
3146 END IF;
3147
3148 -- validate default settlement
3149 CE_BANK_AND_ACCOUNT_VALIDATION.validate_def_settlement
3150 (p_acct_use_rec.bank_account_id,
3151 null,
3152 l_org_le_id,
3153 p_acct_use_rec.xtr_default_settlement_flag,
3154 p_acct_use_rec.ap_default_settlement_flag,
3155 FND_API.G_FALSE,
3156 x_msg_count,
3157 x_msg_data,
3158 x_return_status);
3159 -- raise an exception if the validation is unsuccessful
3160 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3161 RAISE fnd_api.g_exc_error;
3162 END IF;
3163
3164 -- insert data into ce_bank_accounts table
3165 OPEN c_acct_use_id;
3166 FETCH c_acct_use_id INTO x_acct_use_id;
3167 CLOSE c_acct_use_id;
3168
3169 INSERT INTO CE_BANK_ACCT_USES_ALL (
3170 BANK_ACCT_USE_ID,
3171 BANK_ACCOUNT_ID,
3172 PRIMARY_FLAG,
3173 ORG_ID,
3174 ORG_PARTY_ID,
3175 AP_USE_ENABLE_FLAG,
3176 AR_USE_ENABLE_FLAG,
3177 XTR_USE_ENABLE_FLAG,
3178 PAY_USE_ENABLE_FLAG,
3179 EDISC_RECEIVABLES_TRX_ID,
3180 UNEDISC_RECEIVABLES_TRX_ID,
3181 END_DATE,
3182 BR_STD_RECEIVABLES_TRX_ID,
3183 LEGAL_ENTITY_ID,
3184 INVESTMENT_LIMIT_CODE,
3185 FUNDING_LIMIT_CODE,
3186 AP_DEFAULT_SETTLEMENT_FLAG,
3187 XTR_DEFAULT_SETTLEMENT_FLAG,
3188 PAYROLL_BANK_ACCOUNT_ID,
3189 PRICING_MODEL,
3190 AUTHORIZED_FLAG,
3191 EFT_SCRIPT_NAME,
3192 DEFAULT_ACCOUNT_FLAG,
3193 PORTFOLIO_CODE,
3194 ATTRIBUTE_CATEGORY,
3195 ATTRIBUTE1,
3196 ATTRIBUTE2,
3197 ATTRIBUTE3,
3198 ATTRIBUTE4,
3199 ATTRIBUTE5,
3200 ATTRIBUTE6,
3201 ATTRIBUTE7,
3202 ATTRIBUTE8,
3203 ATTRIBUTE9,
3204 ATTRIBUTE10,
3205 ATTRIBUTE11,
3206 ATTRIBUTE12,
3207 ATTRIBUTE13,
3208 ATTRIBUTE14,
3209 ATTRIBUTE15,
3210 LAST_UPDATE_DATE,
3211 LAST_UPDATED_BY,
3212 LAST_UPDATE_LOGIN,
3213 CREATION_DATE,
3214 CREATED_BY,
3215 OBJECT_VERSION_NUMBER)
3216 VALUES (
3217 x_acct_use_id,
3218 p_acct_use_rec.bank_account_id,
3219 p_acct_use_rec.primary_flag,
3220 p_acct_use_rec.org_id,
3221 p_acct_use_rec.org_party_id,
3222 p_acct_use_rec.ap_use_enable_flag,
3223 p_acct_use_rec.ar_use_enable_flag,
3224 p_acct_use_rec.xtr_use_enable_flag,
3225 p_acct_use_rec.pay_use_enable_flag,
3226 p_acct_use_rec.edisc_receivables_trx_id,
3227 p_acct_use_rec.unedisc_receivables_trx_id,
3228 p_acct_use_rec.end_date,
3229 p_acct_use_rec.br_std_receivables_trx_id,
3230 p_acct_use_rec.legal_entity_id,
3231 p_acct_use_rec.investment_limit_code,
3232 p_acct_use_rec.funding_limit_code,
3233 p_acct_use_rec.ap_default_settlement_flag,
3234 p_acct_use_rec.xtr_default_settlement_flag,
3235 p_acct_use_rec.payroll_bank_account_id,
3236 p_acct_use_rec.pricing_model,
3237 p_acct_use_rec.authorized_flag,
3238 p_acct_use_rec.eft_script_name,
3239 p_acct_use_rec.default_account_flag,
3240 p_acct_use_rec.portfolio_code,
3241 p_acct_use_rec.attribute_category,
3242 p_acct_use_rec.attribute1,
3243 p_acct_use_rec.attribute2,
3244 p_acct_use_rec.attribute3,
3245 p_acct_use_rec.attribute4,
3246 p_acct_use_rec.attribute5,
3247 p_acct_use_rec.attribute6,
3248 p_acct_use_rec.attribute7,
3249 p_acct_use_rec.attribute8,
3250 p_acct_use_rec.attribute9,
3251 p_acct_use_rec.attribute10,
3252 p_acct_use_rec.attribute11,
3253 p_acct_use_rec.attribute12,
3254 p_acct_use_rec.attribute13,
3255 p_acct_use_rec.attribute14,
3256 p_acct_use_rec.attribute15,
3257 sysdate,
3258 NVL(FND_GLOBAL.user_id,-1),
3259 NVL(FND_GLOBAL.login_id, -1),
3260 sysdate,
3261 NVL(FND_GLOBAL.user_id,-1),
3262 1);
3263
3264 INSERT INTO CE_GL_ACCOUNTS_CCID (
3265 BANK_ACCT_USE_ID,
3266 ASSET_CODE_COMBINATION_ID,
3267 AP_ASSET_CCID,
3268 AR_ASSET_CCID,
3269 CASH_CLEARING_CCID,
3270 BANK_CHARGES_CCID,
3271 BANK_ERRORS_CCID,
3272 GAIN_CODE_COMBINATION_ID,
3273 LOSS_CODE_COMBINATION_ID,
3274 ON_ACCOUNT_CCID,
3275 UNAPPLIED_CCID,
3276 UNIDENTIFIED_CCID,
3277 FACTOR_CCID,
3278 RECEIPT_CLEARING_CCID,
3279 REMITTANCE_CCID,
3280 AR_SHORT_TERM_DEPOSIT_CCID,
3281 BR_SHORT_TERM_DEPOSIT_CCID,
3282 FUTURE_DATED_PAYMENT_CCID,
3283 BR_REMITTANCE_CCID,
3284 BR_FACTOR_CCID,
3285 BANK_INTEREST_EXPENSE_CCID,
3286 BANK_INTEREST_INCOME_CCID,
3287 XTR_ASSET_CCID,
3288 AR_BANK_CHARGES_CCID, -- 7437641
3289 LAST_UPDATE_DATE,
3290 LAST_UPDATED_BY,
3291 LAST_UPDATE_LOGIN,
3292 CREATION_DATE,
3293 CREATED_BY,
3294 OBJECT_VERSION_NUMBER)
3295 VALUES (
3296 x_acct_use_id,
3297 p_acct_use_rec.asset_code_combination_id,
3298 p_acct_use_rec.ap_asset_ccid,
3299 p_acct_use_rec.ar_asset_ccid,
3300 p_acct_use_rec.cash_clearing_ccid,
3301 p_acct_use_rec.bank_charges_ccid,
3302 p_acct_use_rec.bank_errors_ccid,
3303 p_acct_use_rec.gain_code_combination_id,
3304 p_acct_use_rec.loss_code_combination_id,
3305 p_acct_use_rec.on_account_ccid,
3306 p_acct_use_rec.unapplied_ccid,
3307 p_acct_use_rec.unidentified_ccid,
3308 p_acct_use_rec.factor_ccid,
3309 p_acct_use_rec.receipt_clearing_ccid,
3310 p_acct_use_rec.remittance_ccid,
3311 p_acct_use_rec.ar_short_term_deposit_ccid,
3312 p_acct_use_rec.br_short_term_deposit_ccid,
3313 p_acct_use_rec.future_dated_payment_ccid,
3314 p_acct_use_rec.br_remittance_ccid,
3315 p_acct_use_rec.br_factor_ccid,
3316 p_acct_use_rec.bank_interest_expense_ccid,
3317 p_acct_use_rec.bank_interest_income_ccid,
3318 p_acct_use_rec.xtr_asset_ccid,
3319 p_acct_use_rec.ar_bank_charges_ccid, -- 7437641
3320 sysdate,
3321 NVL(FND_GLOBAL.user_id,-1),
3322 NVL(FND_GLOBAL.login_id, -1),
3323 sysdate,
3324 NVL(FND_GLOBAL.user_id,-1),
3325 1);
3326
3327 OPEN c_acct_use_rowid;
3328 FETCH c_acct_use_rowid INTO l_acct_use_rowid;
3329 If (c_acct_use_rowid%NOTFOUND) then
3330 CLOSE c_acct_use_rowid;
3331 RAISE NO_DATA_FOUND;
3332 End If;
3333 CLOSE c_acct_use_rowid;
3334
3335 OPEN c_gl_ccid_rowid;
3336 FETCH c_gl_ccid_rowid INTO l_gl_ccid_rowid;
3337 If (c_gl_ccid_rowid%NOTFOUND) then
3338 CLOSE c_gl_ccid_rowid;
3339 RAISE NO_DATA_FOUND;
3340 End If;
3341 CLOSE c_gl_ccid_rowid;
3342
3343 -- get message count and if count is 1, get message info.
3344 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3345 p_count => x_msg_count,
3346 p_data => x_msg_data);
3347
3348 IF l_DEBUG in ('Y', 'C') THEN
3349 cep_standard.debug('<<CE_BANK_PUB.create_bank_acct_use.');
3350 END IF;
3351
3352 EXCEPTION
3353 WHEN fnd_api.g_exc_error THEN
3354 ROLLBACK TO create_bank_acct_use;
3355 x_return_status := fnd_api.g_ret_sts_error;
3356 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3357 p_count => x_msg_count,
3358 p_data => x_msg_data);
3359
3360 IF l_DEBUG in ('Y', 'C') THEN
3361 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
3362 END IF;
3363
3364 WHEN fnd_api.g_exc_unexpected_error THEN
3365 ROLLBACK TO create_bank_acct_use;
3366 x_return_status := fnd_api.g_ret_sts_unexp_error;
3367 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3368 p_count => x_msg_count,
3369 p_data => x_msg_data);
3370
3371 IF l_DEBUG in ('Y', 'C') THEN
3372 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
3373 END IF;
3374
3375 WHEN OTHERS THEN
3376 ROLLBACK TO create_bank_acct_use;
3377 x_return_status := fnd_api.g_ret_sts_unexp_error;
3378 fnd_message.set_name('CE', 'CE_API_OTHERS_EXCEP');
3379 fnd_message.set_token('ERROR',SQLERRM);
3380 fnd_msg_pub.add;
3381 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3382 p_count => x_msg_count,
3383 p_data => x_msg_data);
3384
3385 IF l_DEBUG in ('Y', 'C') THEN
3386 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
3387 END IF;
3388
3389
3390 END create_bank_acct_use;
3391
3392
3393 PROCEDURE update_bank_acct_use (
3394 p_init_msg_list IN VARCHAR2:= fnd_api.g_false,
3395 p_acct_use_rec IN BankAcct_use_rec_type,
3396 p_use_ovn IN OUT NOCOPY NUMBER,
3397 p_ccid_ovn IN OUT NOCOPY NUMBER,
3398 x_return_status OUT NOCOPY VARCHAR2,
3399 x_msg_count OUT NOCOPY NUMBER,
3400 x_msg_data OUT NOCOPY VARCHAR2
3401 ) IS
3402 CURSOR c_acct_org IS
3403 SELECT bank_account_id,
3404 org_id,
3405 legal_entity_id
3406 FROM ce_bank_acct_uses_all
3407 WHERE bank_acct_use_id = p_acct_use_rec.bank_acct_use_id;
3408
3409 CURSOR c_use_ovn IS
3410 SELECT object_version_number
3411 FROM ce_bank_acct_uses_all
3412 WHERE bank_acct_use_id = p_acct_use_rec.bank_acct_use_id;
3413
3414 CURSOR c_ccid_ovn IS
3415 SELECT object_version_number
3416 FROM ce_gl_accounts_ccid
3417 WHERE bank_acct_use_id = p_acct_use_rec.bank_acct_use_id;
3418
3419 l_acct_id NUMBER(15);
3420 l_org_id NUMBER(15);
3421 l_le_id NUMBER(15);
3422 l_org_le_id NUMBER(15);
3423 l_org_type VARCHAR2(8);
3424 l_old_use_ovn NUMBER(15);
3425 l_old_ccid_ovn NUMBER(15);
3426 BEGIN
3427 SAVEPOINT update_bank_acct_use;
3428
3429 IF l_DEBUG in ('Y', 'C') THEN
3430 cep_standard.debug('>>CE_BANK_PUB.update_bank_acct_use.');
3431 END IF;
3432
3433 -- initialize message list
3434 IF fnd_api.to_boolean(p_init_msg_list) THEN
3435 fnd_msg_pub.initialize;
3436 END IF;
3437
3438 -- initialize API return status to success.
3439 x_return_status := fnd_api.g_ret_sts_success;
3440
3441 -- bank_account_id and org_id/legal_entity_id cannot be updated
3442 -- find them using the bank_acct_use_id
3443 OPEN c_acct_org;
3444 FETCH c_acct_org INTO l_acct_id, l_org_id, l_le_id;
3445 IF c_acct_org%NOTFOUND THEN
3446 fnd_message.set_name('CE', 'CE_API_NO_ACCOUNT_USE');
3447 fnd_msg_pub.add;
3448 x_return_status := fnd_api.g_ret_sts_error;
3449 CLOSE c_acct_org;
3450 RAISE fnd_api.g_exc_error;
3451 END IF;
3452 CLOSE c_acct_org;
3453
3454 IF l_org_id is not null AND l_org_id <> -1 THEN -- Bug 9150022
3455 l_org_le_id := l_org_id;
3456 l_org_type := 'BGOU';
3457 ELSE
3458 l_org_le_id := l_le_id;
3459 l_org_type := 'LE';
3460 END IF;
3461
3462 -- first check all required params
3463 -- 12557352: Modified check by calling check_mandatory_param
3464 check_mandatory_param('p_acct_use_rec.bank_acct_use_id',p_acct_use_rec.bank_acct_use_id);
3465 check_mandatory_param('p_use_ovn',p_use_ovn);
3466 check_mandatory_param('p_ccid_ovn',p_ccid_ovn);
3467
3468 -- GL Cash Account is required if the account use is Payables or Receivables
3469 IF ((p_acct_use_rec.ap_use_enable_flag = 'Y' or
3470 p_acct_use_rec.ar_use_enable_flag = 'Y') and
3471 p_acct_use_rec.asset_code_combination_id is null) THEN -- bug 7671686
3472 fnd_message.set_name('CE', 'CE_API_CASH_CCID_REQUIRED');
3473 fnd_msg_pub.add;
3474 x_return_status := fnd_api.g_ret_sts_error;
3475 RAISE fnd_api.g_exc_error;
3476 END IF;
3477
3478 -- validate account use
3479 CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_use
3480 (p_acct_use_rec.ap_use_enable_flag,
3481 p_acct_use_rec.ar_use_enable_flag,
3482 p_acct_use_rec.pay_use_enable_flag,
3483 p_acct_use_rec.xtr_use_enable_flag,
3484 x_return_status);
3485
3486 -- raise an exception if the validation is unsuccessful
3487 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3488 RAISE fnd_api.g_exc_error;
3489 END IF;
3490
3491 -- validate the org type matches with the account use
3492 CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_access_org
3493 (p_acct_use_rec.ap_use_enable_flag,
3494 p_acct_use_rec.ar_use_enable_flag,
3495 p_acct_use_rec.pay_use_enable_flag,
3496 p_acct_use_rec.xtr_use_enable_flag,
3497 l_org_type,
3498 l_org_le_id,
3499 x_return_status);
3500 -- raise an exception if the validation is unsuccessful
3501 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3502 RAISE fnd_api.g_exc_error;
3503 END IF;
3504
3505 -- validate default settlement
3506 CE_BANK_AND_ACCOUNT_VALIDATION.validate_def_settlement
3507 (l_acct_id,
3508 p_acct_use_rec.bank_acct_use_id,
3509 l_org_le_id,
3510 p_acct_use_rec.xtr_default_settlement_flag,
3511 p_acct_use_rec.ap_default_settlement_flag,
3512 FND_API.G_FALSE,
3513 x_msg_count,
3514 x_msg_data,
3515 x_return_status);
3516 -- raise an exception if the validation is unsuccessful
3517 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3518 RAISE fnd_api.g_exc_error;
3519 END IF;
3520
3521 -- check object version number to make sure the record has not been updated
3522 OPEN c_use_ovn;
3523 FETCH c_use_ovn INTO l_old_use_ovn;
3524 IF c_use_ovn%NOTFOUND THEN
3525 fnd_message.set_name('CE', 'CE_API_NO_ACCOUNT_USE');
3526 fnd_msg_pub.add;
3527 x_return_status := fnd_api.g_ret_sts_error;
3528 CLOSE c_use_ovn;
3529 RAISE fnd_api.g_exc_error;
3530 END IF;
3531 CLOSE c_use_ovn;
3532
3533 IF l_old_use_ovn > p_use_ovn THEN
3534 fnd_message.set_name('CE', 'CE_API_RECORD_CHANGED');
3535 fnd_message.set_token('TABLE', 'CE_BANK_ACCT_USES_ALL');
3536 fnd_msg_pub.add;
3537 x_return_status := fnd_api.g_ret_sts_error;
3538 RAISE fnd_api.g_exc_error;
3539 END IF;
3540
3541 -- check object version number to make sure the record has not been updated
3542 OPEN c_ccid_ovn;
3543 FETCH c_ccid_ovn INTO l_old_ccid_ovn;
3544 CLOSE c_ccid_ovn;
3545
3546 IF l_old_ccid_ovn is not null AND l_old_ccid_ovn > p_ccid_ovn THEN
3547 fnd_message.set_name('CE', 'CE_API_RECORD_CHANGED');
3548 fnd_message.set_token('TABLE', 'CE_GL_ACCOUNTS_CCID');
3549 fnd_msg_pub.add;
3550 x_return_status := fnd_api.g_ret_sts_error;
3551 RAISE fnd_api.g_exc_error;
3552 END IF;
3553
3554 -- update
3555 update ce_bank_acct_uses_all
3556 set primary_flag = p_acct_use_rec.primary_flag,
3557 ap_use_enable_flag = p_acct_use_rec.ap_use_enable_flag,
3558 ar_use_enable_flag = p_acct_use_rec.ar_use_enable_flag,
3559 xtr_use_enable_flag = p_acct_use_rec.xtr_use_enable_flag,
3560 pay_use_enable_flag = p_acct_use_rec.pay_use_enable_flag,
3561 edisc_receivables_trx_id = p_acct_use_rec.edisc_receivables_trx_id,
3562 unedisc_receivables_trx_id = p_acct_use_rec.unedisc_receivables_trx_id,
3563 end_date = p_acct_use_rec.end_date,
3564 br_std_receivables_trx_id = p_acct_use_rec.br_std_receivables_trx_id,
3565 investment_limit_code = p_acct_use_rec.investment_limit_code,
3566 funding_limit_code = p_acct_use_rec.funding_limit_code,
3567 ap_default_settlement_flag = p_acct_use_rec.ap_default_settlement_flag,
3568 xtr_default_settlement_flag = p_acct_use_rec.xtr_default_settlement_flag,
3569 payroll_bank_account_id = p_acct_use_rec.payroll_bank_account_id,
3570 pricing_model = p_acct_use_rec.pricing_model,
3571 authorized_flag = p_acct_use_rec.authorized_flag,
3572 eft_script_name = p_acct_use_rec.eft_script_name,
3573 default_account_flag = p_acct_use_rec.default_account_flag,
3574 portfolio_code = p_acct_use_rec.portfolio_code,
3575 attribute_category = p_acct_use_rec.attribute_category,
3576 attribute1 = p_acct_use_rec.attribute1,
3577 attribute2 = p_acct_use_rec.attribute2,
3578 attribute3 = p_acct_use_rec.attribute3,
3579 attribute4 = p_acct_use_rec.attribute4,
3580 attribute5 = p_acct_use_rec.attribute5,
3581 attribute6 = p_acct_use_rec.attribute6,
3582 attribute7 = p_acct_use_rec.attribute7,
3583 attribute8 = p_acct_use_rec.attribute8,
3584 attribute9 = p_acct_use_rec.attribute9,
3585 attribute10 = p_acct_use_rec.attribute10,
3586 attribute11 = p_acct_use_rec.attribute11,
3587 attribute12 = p_acct_use_rec.attribute12,
3588 attribute13 = p_acct_use_rec.attribute13,
3589 attribute14 = p_acct_use_rec.attribute14,
3590 attribute15 = p_acct_use_rec.attribute15,
3591 last_update_date = sysdate,
3592 last_update_login = NVL(FND_GLOBAL.login_id,-1),
3593 last_updated_by = NVL(FND_GLOBAL.user_id,-1),
3594 object_version_number = l_old_use_ovn + 1
3595 WHERE bank_acct_use_id = p_acct_use_rec.bank_acct_use_id;
3596 IF (SQL%NOTFOUND) THEN
3597 fnd_message.set_name('CE', 'CE_API_NO_ACCOUNT_USE');
3598 fnd_msg_pub.add;
3599 x_return_status := fnd_api.g_ret_sts_error;
3600 RAISE fnd_api.g_exc_error;
3601 END IF;
3602 p_use_ovn := l_old_use_ovn + 1;
3603
3604 -- update ccid table
3605 IF l_old_ccid_ovn is not null THEN
3606 update ce_gl_accounts_ccid
3607 set asset_code_combination_id = p_acct_use_rec.asset_code_combination_id,
3608 ap_asset_ccid = p_acct_use_rec.ap_asset_ccid,
3609 ar_asset_ccid = p_acct_use_rec.ar_asset_ccid,
3610 cash_clearing_ccid = p_acct_use_rec.cash_clearing_ccid,
3611 bank_charges_ccid = p_acct_use_rec.bank_charges_ccid,
3612 bank_errors_ccid = p_acct_use_rec.bank_errors_ccid,
3613 gain_code_combination_id = p_acct_use_rec.gain_code_combination_id,
3614 loss_code_combination_id = p_acct_use_rec.loss_code_combination_id,
3615 on_account_ccid = p_acct_use_rec.on_account_ccid,
3616 unapplied_ccid = p_acct_use_rec.unapplied_ccid,
3617 unidentified_ccid = p_acct_use_rec.unidentified_ccid,
3618 factor_ccid = p_acct_use_rec.factor_ccid,
3619 receipt_clearing_ccid = p_acct_use_rec.receipt_clearing_ccid,
3620 remittance_ccid = p_acct_use_rec.remittance_ccid,
3621 ar_short_term_deposit_ccid = p_acct_use_rec.ar_short_term_deposit_ccid,
3622 br_short_term_deposit_ccid = p_acct_use_rec.br_short_term_deposit_ccid,
3623 future_dated_payment_ccid = p_acct_use_rec.future_dated_payment_ccid,
3624 br_remittance_ccid = p_acct_use_rec.br_remittance_ccid,
3625 br_factor_ccid = p_acct_use_rec.br_factor_ccid,
3626 bank_interest_expense_ccid = p_acct_use_rec.bank_interest_expense_ccid,
3627 bank_interest_income_ccid = p_acct_use_rec.bank_interest_income_ccid,
3628 xtr_asset_ccid = p_acct_use_rec.xtr_asset_ccid,
3629 ar_bank_charges_ccid = p_acct_use_rec.ar_bank_charges_ccid, -- 7437641
3630 last_update_date = sysdate,
3631 last_update_login = NVL(FND_GLOBAL.login_id,-1),
3632 last_updated_by = NVL(FND_GLOBAL.user_id,-1),
3633 object_version_number = l_old_ccid_ovn + 1
3634 WHERE bank_acct_use_id = p_acct_use_rec.bank_acct_use_id;
3635 p_ccid_ovn := l_old_ccid_ovn + 1;
3636 END IF;
3637
3638 -- get message count and if count is 1, get message info.
3639 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3640 p_count => x_msg_count,
3641 p_data => x_msg_data);
3642
3643 IF l_DEBUG in ('Y', 'C') THEN
3644 cep_standard.debug('<<CE_BANK_PUB.update_bank_acct_use.');
3645 END IF;
3646
3647 EXCEPTION
3648 WHEN fnd_api.g_exc_error THEN
3649 ROLLBACK TO update_bank_acct_use;
3650 x_return_status := fnd_api.g_ret_sts_error;
3651 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3652 p_count => x_msg_count,
3653 p_data => x_msg_data);
3654
3655 IF l_DEBUG in ('Y', 'C') THEN
3656 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
3657 END IF;
3658
3659 WHEN fnd_api.g_exc_unexpected_error THEN
3660 ROLLBACK TO update_bank_acct_use;
3661 x_return_status := fnd_api.g_ret_sts_unexp_error;
3662 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3663 p_count => x_msg_count,
3664 p_data => x_msg_data);
3665
3666 IF l_DEBUG in ('Y', 'C') THEN
3667 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
3668 END IF;
3669
3670 WHEN OTHERS THEN
3671 ROLLBACK TO udpate_bank_acct_use;
3672 x_return_status := fnd_api.g_ret_sts_unexp_error;
3673 fnd_message.set_name('CE', 'CE_API_OTHERS_EXCEP');
3674 fnd_message.set_token('ERROR',SQLERRM);
3675 fnd_msg_pub.add;
3676 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3677 p_count => x_msg_count,
3678 p_data => x_msg_data);
3679
3680 IF l_DEBUG in ('Y', 'C') THEN
3681 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
3682 END IF;
3683
3684 END update_bank_acct_use;
3685
3686 -- 12557352 : Added procedure
3687 /*=======================================================================+
3688 | PUBLIC PROCEDURE set_bank_acct_end_date |
3689 | |
3690 | DESCRIPTION |
3691 | Set the end date of an internal or subsidiary bank account. |
3692 | |
3693 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3694 | |
3695 | ARGUMENTS |
3696 | IN: |
3697 | p_init_msg_list Initialize message stack if it is set to |
3698 | FND_API.G_TRUE. Default is fnd_api.g_false|
3699 | p_bank_acct_id ID of the Internal Bank account to be |
3700 | inactivated. |
3701 | p_end_date Inactive date of the bank account |
3702 | |
3703 | |
3704 | IN/OUT: |
3705 | p_object_version_number Current object version number for the |
3706 | bank account. |
3707 | OUT: |
3708 | x_return_status Return status after the call. The status can |
3709 | be FND_API.G_RET_STS_SUCCESS (success), |
3710 | fnd_api.g_ret_sts_error (error), |
3711 | fnd_api.g_ret_sts_unexp_error (unexpected |
3712 | error). |
3713 | x_msg_count Number of messages in message stack. |
3714 | x_msg_data Message text if x_msg_count is 1. |
3715 | MODIFICATION HISTORY |
3716 | 18-MAY-2011 vnetan Created. |
3717 +=======================================================================*/
3718 PROCEDURE set_bank_acct_end_date (
3719 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3720 p_bank_acct_id IN NUMBER,
3721 p_end_date IN DATE,
3722 p_object_version_number IN OUT NOCOPY NUMBER,
3723 x_return_status OUT NOCOPY VARCHAR2,
3724 x_msg_count OUT NOCOPY NUMBER,
3725 x_msg_data OUT NOCOPY VARCHAR2
3726 ) AS
3727 l_old_ovn CE_BANK_ACCOUNTS.object_version_number%TYPE;
3728 BEGIN
3729 SAVEPOINT set_bank_acct_end_date;
3730
3731 IF l_DEBUG in ('Y', 'C') THEN
3732 cep_standard.debug('>>CE_EXT_BANK_ACCT_PUB.set_bank_acct_end_date.');
3733 END IF;
3734
3735 -- initialize message list
3736 IF fnd_api.to_boolean(p_init_msg_list) THEN
3737 fnd_msg_pub.initialize;
3738 END IF;
3739
3740 -- initialize API return status to success.
3741 x_return_status := fnd_api.g_ret_sts_success;
3742
3743 -- check mandatory parameters
3744 check_mandatory_param('p_bank_acct_id',p_bank_acct_id);
3745 check_mandatory_param('p_object_version_number',p_object_version_number);
3746
3747 -- check object version number to make sure the record has not been updated
3748 BEGIN
3749 SELECT object_version_number
3750 INTO l_old_ovn
3751 FROM ce_bank_accounts
3752 WHERE bank_account_id = p_bank_acct_id;
3753 EXCEPTION
3754 WHEN NO_DATA_FOUND THEN
3755 fnd_message.set_name('CE', 'CE_API_NO_ACCOUNT');
3756 fnd_msg_pub.add;
3757 x_return_status := fnd_api.g_ret_sts_error;
3758 RAISE fnd_api.g_exc_error;
3759 END;
3760
3761 IF l_old_ovn > p_object_version_number THEN
3762 fnd_message.set_name('CE', 'CE_API_RECORD_CHANGED');
3763 fnd_message.set_token('TABLE', 'CE_BANK_ACCOUNTS');
3764 fnd_msg_pub.add;
3765 x_return_status := fnd_api.g_ret_sts_error;
3766 RAISE fnd_api.g_exc_error;
3767 END IF;
3768
3769 -- set the end date of the account
3770 UPDATE ce_bank_accounts
3771 SET end_date = p_end_date,
3772 object_version_number = l_old_ovn + 1
3773 WHERE bank_account_id = p_bank_acct_id;
3774
3775 -- check to see row udpated
3776 IF (SQL%NOTFOUND) THEN
3777 fnd_message.set_name('CE', 'CE_API_NO_ACCOUNT');
3778 fnd_msg_pub.add;
3779 x_return_status := fnd_api.g_ret_sts_error;
3780 RAISE fnd_api.g_exc_error;
3781 END IF;
3782
3783 p_object_version_number := l_old_ovn + 1;
3784 -- get message count and if count is 1, get message info.
3785 fnd_msg_pub.count_and_get(
3786 p_encoded => fnd_api.g_false,
3787 p_count => x_msg_count,
3788 p_data => x_msg_data);
3789
3790 IF l_DEBUG in ('Y', 'C') THEN
3791 cep_standard.debug('<<CE_BANK_PUB.set_bank_acct_end_date.');
3792 END IF;
3793
3794 EXCEPTION
3795 WHEN fnd_api.g_exc_error THEN
3796 ROLLBACK TO set_bank_acct_end_date;
3797 x_return_status := fnd_api.g_ret_sts_error;
3798 fnd_msg_pub.count_and_get(
3799 p_encoded => fnd_api.g_false,
3800 p_count => x_msg_count,
3801 p_data => x_msg_data);
3802
3803 IF l_DEBUG in ('Y', 'C') THEN
3804 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
3805 END IF;
3806
3807 WHEN fnd_api.g_exc_unexpected_error THEN
3808 ROLLBACK TO set_bank_acct_end_date;
3809 x_return_status := fnd_api.g_ret_sts_unexp_error;
3810 fnd_msg_pub.count_and_get(
3811 p_encoded => fnd_api.g_false,
3812 p_count => x_msg_count,
3813 p_data => x_msg_data);
3814
3815 IF l_DEBUG in ('Y', 'C') THEN
3816 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
3817 END IF;
3818
3819 WHEN OTHERS THEN
3820 ROLLBACK TO set_bank_acct_end_date;
3821 x_return_status := fnd_api.g_ret_sts_unexp_error;
3822 fnd_message.set_name('CE', 'CE_API_OTHERS_EXCEP');
3823 fnd_message.set_token('ERROR',SQLERRM);
3824 fnd_msg_pub.add;
3825 fnd_msg_pub.count_and_get(
3826 p_encoded => fnd_api.g_false,
3827 p_count => x_msg_count,
3828 p_data => x_msg_data);
3829
3830 IF l_DEBUG in ('Y', 'C') THEN
3831 cep_standard.debug_msg_stack(x_msg_count, x_msg_data);
3832 END IF;
3833 END set_bank_acct_end_date;
3834
3835 END ce_bank_pub;