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