1 PACKAGE BODY XTR_REPLICATE_BANK_BRANCHES_P AS
2 /* | $Header: xtrrbkbb.pls 120.6 2005/07/29 08:01:36 badiredd noship $ | */
3 /**
4 * PROCEDURE update_bank_branches
5 *
6 * DESCRIPTION
7 * This procedure is called directly by CE to update
8 * the bank branch related data into XTR tables.
9 *
10 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
11 *
12 * ARGUMENTS
13 * IN:
14 *
15 * p_xtr_party_info_rec Record type of XTR_PARTY_INFO.
16 * This record type contains the Bank/Bank Branch
17 * related information about the bank attached with
18 * Bank Account.
19 * p_update_type To determine what parameters CE has updated
20 * IN/OUT:
21 *
22 * OUT:
23 * x_return_status Return status after the call. The
24 * status can be
25 * FND_API.G_RET_STS_SUCCESS - for success
26 * FND_API.G_RET_STS_ERR - for expected error
27 * FND_API.G_RET_STS_UNEXP_ERR - for unexpected error
28 * x_msg_count To return the number of error messages
29 * in stack
30 * x_msg_data To return the error message if
31 * x_msg_count = 1.
32 * NOTES
33 *
34 * MODIFICATION HISTORY
35 *
36 * 06-17-2005 Bhargav Adireddy o Created.
37 *
38 */
39
40 PROCEDURE UPDATE_BANK_BRANCHES
41 ( p_xtr_party_info_rec IN XTR_PARTY_INFO%ROWTYPE,
42 p_update_type IN NUMBER,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_msg_data OUT NOCOPY VARCHAR2
46 ) IS
47
48 l_check_branch VARCHAR2(10);
49
50 CURSOR c_check_branch IS
51 SELECT 'Y'
52 FROM XTR_PARTY_INFO
53 WHERE ce_bank_branch_id = p_xtr_party_info_rec.ce_bank_branch_id;
54
55 BEGIN
56 x_return_status := FND_API.G_RET_STS_SUCCESS;
57 x_msg_count := NULL;
58 FND_MSG_PUB.Initialize; -- Initializes the message list that stores the errors
59
60 OPEN c_check_branch;
61 FETCH c_check_branch INTO l_check_branch;
62
63 IF(c_check_branch%FOUND) THEN
64 CLOSE c_check_branch;
65 IF( CHK_BANK_BRANCH(p_xtr_party_info_rec.ce_bank_branch_id)) THEN
66 VALIDATE_BANK_BRANCH(p_xtr_party_info_rec,p_update_type,x_return_status);
67 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
68 MODIFY_BANK_BRANCH(p_xtr_party_info_rec,p_update_type,x_return_status);
69 END IF;
70 ELSE
71 x_return_status := FND_API.G_RET_STS_ERROR;
72 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_INV_PARAM','XTR_PARTY_INFO.ce_bank_branch_id');
73 END IF;
74 ELSE
75 CLOSE c_check_branch;
76 END IF;
77
78 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
79 ( p_count => x_msg_count ,
80 p_data => x_msg_data
81 );
82 --
83 EXCEPTION
84 WHEN others THEN
85 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
86 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
87 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
88 ( p_count => x_msg_count ,
89 p_data => x_msg_data
90 );
91 END UPDATE_BANK_BRANCHES;
92
93
94 /* This procedure is to override the previos procedure so that CE can pass the individual parameters */
95
96 PROCEDURE UPDATE_BANK_BRANCHES
97 ( p_ce_bank_branch_id IN XTR_PARTY_INFO.ce_bank_branch_id%TYPE,
98 p_short_name IN XTR_PARTY_INFO.short_name%TYPE,
99 p_full_name IN XTR_PARTY_INFO.full_name%TYPE,
100 p_swift_id IN XTR_PARTY_INFO.swift_id%TYPE,
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_msg_count OUT NOCOPY NUMBER,
103 x_msg_data OUT NOCOPY VARCHAR2
104 ) IS
105
106 l_xtr_party_info_rec XTR_PARTY_INFO%ROWTYPE;
107
108 BEGIN
109
110 l_xtr_party_info_rec.ce_bank_branch_id := p_ce_bank_branch_id;
111 l_xtr_party_info_rec.short_name := p_short_name;
112 l_xtr_party_info_rec.full_name := p_full_name;
113 l_xtr_party_info_rec.swift_id := p_swift_id;
114
115
116 UPDATE_BANK_BRANCHES( l_xtr_party_info_rec,1, x_return_status,
117 x_msg_count,
118 x_msg_data);
119
120 EXCEPTION
121 WHEN others THEN
122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
123 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
124 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
125 ( p_count => x_msg_count ,
126 p_data => x_msg_data
127 );
128 END UPDATE_BANK_BRANCHES;
129
130
131 PROCEDURE UPDATE_BANK_BRANCHES
132 ( p_ce_bank_branch_id IN XTR_PARTY_INFO.ce_bank_branch_id%TYPE,
133 p_address_2 IN XTR_PARTY_INFO.address_2%TYPE,
134 p_address_3 IN XTR_PARTY_INFO.address_3%TYPE,
135 p_address_4 IN XTR_PARTY_INFO.address_4%TYPE,
136 p_address_5 IN XTR_PARTY_INFO.address_5%TYPE,
137 p_country_code IN XTR_PARTY_INFO.country_code%TYPE,
138 p_state_code IN XTR_PARTY_INFO.state_code%TYPE,
139 x_return_status OUT NOCOPY VARCHAR2,
140 x_msg_count OUT NOCOPY NUMBER,
141 x_msg_data OUT NOCOPY VARCHAR2
142 ) IS
143
144 l_xtr_party_info_rec XTR_PARTY_INFO%ROWTYPE;
145
146 BEGIN
147
148 l_xtr_party_info_rec.ce_bank_branch_id := p_ce_bank_branch_id;
149 l_xtr_party_info_rec.address_2 := p_address_2;
150 l_xtr_party_info_rec.address_3 := p_address_3;
151 l_xtr_party_info_rec.address_4 := p_address_4;
152 l_xtr_party_info_rec.address_5 := p_address_5;
153 l_xtr_party_info_rec.country_code := p_country_code;
154 l_xtr_party_info_rec.state_code := p_state_code;
155
156 UPDATE_BANK_BRANCHES( l_xtr_party_info_rec,4, x_return_status,
157 x_msg_count,
158 x_msg_data);
159
160 EXCEPTION
161 WHEN others THEN
162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
163 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
164 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
165 ( p_count => x_msg_count ,
166 p_data => x_msg_data
167 );
168 END UPDATE_BANK_BRANCHES;
169
170
171 PROCEDURE UPDATE_BANK_BRANCHES
172 ( p_ce_bank_branch_id IN XTR_PARTY_INFO.ce_bank_branch_id%TYPE,
173 p_contact_name IN XTR_PARTY_INFO.contact_name%TYPE,
174 p_email_address IN XTR_PARTY_INFO.email_address%TYPE,
175 p_fax_number IN XTR_PARTY_INFO.fax_number%TYPE,
176 p_phone_number IN XTR_PARTY_INFO.phone_number%TYPE,
177 x_return_status OUT NOCOPY VARCHAR2,
178 x_msg_count OUT NOCOPY NUMBER,
179 x_msg_data OUT NOCOPY VARCHAR2
180 ) IS
181
182 l_xtr_party_info_rec XTR_PARTY_INFO%ROWTYPE;
183
184 BEGIN
185
186 l_xtr_party_info_rec.ce_bank_branch_id := p_ce_bank_branch_id;
187 l_xtr_party_info_rec.contact_name := p_contact_name;
188 l_xtr_party_info_rec.email_address := p_email_address;
189 l_xtr_party_info_rec.fax_number := p_fax_number;
190 l_xtr_party_info_rec.phone_number := p_phone_number;
191
192 UPDATE_BANK_BRANCHES( l_xtr_party_info_rec,3, x_return_status,
193 x_msg_count,
194 x_msg_data);
195
196 EXCEPTION
197 WHEN others THEN
198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
199 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
200 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
201 ( p_count => x_msg_count ,
202 p_data => x_msg_data
203 );
204 END UPDATE_BANK_BRANCHES;
205
206 PROCEDURE UPDATE_BANK_BRANCHES
207 ( p_ce_bank_branch_id IN XTR_PARTY_INFO.ce_bank_branch_id%TYPE,
208 p_address_2 IN XTR_PARTY_INFO.address_2%TYPE,
209 p_address_3 IN XTR_PARTY_INFO.address_3%TYPE,
210 p_address_4 IN XTR_PARTY_INFO.address_4%TYPE,
211 p_address_5 IN XTR_PARTY_INFO.address_5%TYPE,
212 p_p_address_1 IN XTR_PARTY_INFO.p_address_1%TYPE,
213 p_p_address_2 IN XTR_PARTY_INFO.p_address_2%TYPE,
214 p_p_address_3 IN XTR_PARTY_INFO.p_address_3%TYPE,
215 p_p_address_4 IN XTR_PARTY_INFO.p_address_4%TYPE,
216 p_state_code IN XTR_PARTY_INFO.state_code%TYPE,
217 p_swift_id IN XTR_PARTY_INFO.swift_id%TYPE,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2
221 ) IS
222
223 l_xtr_party_info_rec XTR_PARTY_INFO%ROWTYPE;
224
225 BEGIN
226
227 l_xtr_party_info_rec.ce_bank_branch_id := p_ce_bank_branch_id;
228 l_xtr_party_info_rec.address_2 := p_address_2;
229 l_xtr_party_info_rec.address_3 := p_address_3;
230 l_xtr_party_info_rec.address_4 := p_address_4;
231 l_xtr_party_info_rec.address_5 := p_address_5;
232 l_xtr_party_info_rec.p_address_1 := p_p_address_1;
233 l_xtr_party_info_rec.p_address_2 := p_p_address_2;
234 l_xtr_party_info_rec.p_address_3 := p_p_address_3;
235 l_xtr_party_info_rec.p_address_4 := p_p_address_4;
236 l_xtr_party_info_rec.state_code := p_state_code;
237 l_xtr_party_info_rec.swift_id := p_swift_id;
238
239 UPDATE_BANK_BRANCHES( l_xtr_party_info_rec,2, x_return_status,
240 x_msg_count,
241 x_msg_data);
242
243 EXCEPTION
244 WHEN others THEN
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
246 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
247 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
248 ( p_count => x_msg_count ,
249 p_data => x_msg_data
250 );
251 END UPDATE_BANK_BRANCHES;
252
253
254
255 /**
256 * PROCEDURE validate_bank_branch
257 *
258 * DESCRIPTION
259 * This procedure is used to validate the Bank/Bank Branch related data
260 * before it is inserted into XTR_PARTY_INFO. This procedure will perform the
261 * required validations and puts the corresponding error messages into list
262 *
263 *
264 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
265 *
266 * ARGUMENTS
267 * IN:
268 * p_xtr_party_info_rec Record type of XTR_PARTY_INFO.
269 * This record type contains the Bank/Bank Branch
270 * related information about the bank attached with
271 * Bank Account.
272 * p_update_type Parameter to determine what parmeters are updated by CE
273 * IN/OUT:
274 *
275 * OUT:
276 * x_return_status Return status after the call. The
277 * status can be
278 * FND_API.G_RET_STS_SUCCESS - for success
279 * FND_API.G_RET_STS_ERR - for expected error
280 * FND_API.G_RET_STS_UNEXP_ERR - for unexpected error
281 * NOTES
282 *
283 * MODIFICATION HISTORY
284 *
285 * 06-17-2005 Bhargav Adireddy o Created.
286 *
287 */
288
289 PROCEDURE VALIDATE_BANK_BRANCH
290 ( p_xtr_party_info_rec IN XTR_PARTY_INFO%ROWTYPE,
291 p_update_type IN NUMBER,
292 x_return_status IN OUT NOCOPY VARCHAR2
293 ) IS
294
295 BEGIN
296 -- Verifies if the ce_bank_branch_id in XTR_PARTY_INFO is passed as null
297 IF(p_xtr_party_info_rec.ce_bank_branch_id is null) THEN
298 x_return_status := FND_API.G_RET_STS_ERROR;
299 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_INV_PARAM','XTR_PARTY_INFO.ce_bank_branch_id');
300 END IF;
301
302 -- Verifies if the short_name in XTR_PARTY_INFO is passed as null
303 IF(p_xtr_party_info_rec.short_name is null and p_update_type = 1) THEN
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_INV_PARAM','XTR_PARTY_INFO.SHORT_NAME');
306 END IF;
307 -- Verifies if full_name in XTR_PARTY_INFO is passed as null
308 IF(p_xtr_party_info_rec.full_name is null and p_update_type = 1) THEN
309 x_return_status := FND_API.G_RET_STS_ERROR;
310 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_INV_PARAM','XTR_PARTY_INFO.FULL_NAME');
311 END IF;
312 -- Verifies if country_code in XTR_PARTY_INFO is passed as null
313 IF(p_xtr_party_info_rec.country_code is null and p_update_type = 4) THEN
314 x_return_status := FND_API.G_RET_STS_ERROR;
315 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_INV_PARAM','XTR_PARTY_INFO.COUNTRY');
316 END IF;
317 EXCEPTION
318 WHEN others THEN
319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
320 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
321 END VALIDATE_BANK_BRANCH;
322 /**
323 * PROCEDURE modify_bank_branch
324 *
325 * DESCRIPTION
326 * This procedure will update XTR_PARTY_INFO table with the
327 * Bank Branch data passed form CE.
328 *
329 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
330 *
331 * ARGUMENTS
332 * IN:
333 * p_xtr_party_info_rec Record type of XTR_PARTY_INFO.
334 * This record type contains the Bank/Bank Branch
335 * related information about the bank attached with
336 * Bank Account. related information.
337 * p_update_type Parameter to determine which parameters are updated by CE
338 * IN/OUT:
339 *
340 * OUT:
341 * x_return_status Return status after the call. The
342 * status can be
343 * FND_API.G_RET_STS_SUCCESS - for success
344 * FND_API.G_RET_STS_ERR - for expected error
345 * FND_API.G_RET_STS_UNEXP_ERR - for unexpected error
346 * NOTES
347 *
348 * MODIFICATION HISTORY
349 *
350 * 06-17-2005 Bhargav Adireddy o Created.
351 *
352 */
353
354
355 PROCEDURE MODIFY_BANK_BRANCH
356 ( p_xtr_party_info_rec IN XTR_PARTY_INFO%ROWTYPE,
357 p_update_type IN NUMBER,
358 x_return_status IN OUT NOCOPY VARCHAR2
359 ) IS
360 CURSOR c_chk_lock IS
361 SELECT ce_bank_branch_id
362 FROM XTR_PARTY_INFO
363 WHERE ce_bank_branch_id = p_xtr_party_info_rec.ce_bank_branch_id
364 FOR UPDATE NOWAIT;
365
366 l_ce_bank_branch_id XTR_PARTY_INFO.ce_bank_branch_id%TYPE;
367 BEGIN
368 OPEN c_chk_lock;
369 FETCH c_chk_lock INTO l_ce_bank_branch_id;
370 IF c_chk_lock%FOUND THEN
371 CLOSE c_chk_lock;
372
373 IF(p_update_type = 1) then
374 UPDATE XTR_PARTY_INFO
375 SET short_name = nvl(p_xtr_party_info_rec.short_name,short_name)
376 ,full_name = p_xtr_party_info_rec.full_name
377 ,updated_by = fnd_global.user_id
378 ,updated_on = sysdate
379 WHERE ce_bank_branch_id = l_ce_bank_branch_id;
380 ELSIF(p_update_type = 2) THEN
381 UPDATE XTR_PARTY_INFO
382 SET address_2 = p_xtr_party_info_rec.address_2
383 ,address_3 = p_xtr_party_info_rec.address_3
384 ,address_4 = p_xtr_party_info_rec.address_4
385 ,address_5 = p_xtr_party_info_rec.address_5
386 ,updated_by = fnd_global.user_id
387 ,updated_on = sysdate
388 ,p_address_1 = p_xtr_party_info_rec.p_address_1
389 ,p_address_2 = p_xtr_party_info_rec.p_address_2
390 ,p_address_3 = p_xtr_party_info_rec.p_address_3
391 ,p_address_4 = p_xtr_party_info_rec.p_address_4
392 ,state_code = p_xtr_party_info_rec.state_code
393 ,swift_id = p_xtr_party_info_rec.swift_id
394 WHERE ce_bank_branch_id = l_ce_bank_branch_id;
395
396 ELSIF(p_update_type = 3) THEN
397
398 UPDATE XTR_PARTY_INFO
399 SET contact_name = p_xtr_party_info_rec.contact_name
400 ,country_code = p_xtr_party_info_rec.country_code
401 ,email_address = p_xtr_party_info_rec.email_address
402 ,updated_by = fnd_global.user_id
403 ,updated_on = sysdate
404 ,fax_number = p_xtr_party_info_rec.fax_number
405 ,phone_number = p_xtr_party_info_rec.phone_number
406 WHERE ce_bank_branch_id = l_ce_bank_branch_id;
407
408 ELSIF(p_update_type = 4) THEN
409 UPDATE XTR_PARTY_INFO
410 SET address_2 = p_xtr_party_info_rec.address_2
411 ,address_3 = p_xtr_party_info_rec.address_3
412 ,address_4 = p_xtr_party_info_rec.address_4
413 ,address_5 = p_xtr_party_info_rec.address_5
414 ,updated_by = fnd_global.user_id
415 ,updated_on = sysdate
416 ,country_code = p_xtr_party_info_rec.country_code
417 ,state_code = p_xtr_party_info_rec.state_code
418 WHERE ce_bank_branch_id = l_ce_bank_branch_id;
419
420 END IF;
421
422 ELSE
423
424
425 CLOSE c_chk_lock;
426 x_return_status := FND_API.G_RET_STS_ERROR;
427 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_INV_PARAM','XTR_PARTY_INFO.ce_bank_branch_id');
428
429 END IF;
430
431 EXCEPTION
432 When app_exceptions.RECORD_LOCK_EXCEPTION then -- If the record is locked
433 if C_CHK_LOCK%ISOPEN then
434 close c_CHK_LOCK;
435 end if;
436 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('CHK_LOCK');
437 x_return_status := FND_API.G_RET_STS_ERROR;
438 WHEN others THEN
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
440 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
441
442 END MODIFY_BANK_BRANCH;
443 /**
444 * FUNCTION chk_bank_branch
445 *
446 * DESCRIPTION
447 * This Function will verify if a particular bank_branch_id exists in
448 * XTR_PARTY_INFO table with the Bank Branch ID passed form CE. This returns
449 * a BOOLEAN
450 *
451 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
452 * XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG()
453 * ARGUMENTS
454 * IN:
455 * p_ce_bank_branch_id This is type of CE_BANK_BRANCH_ID present in
456 * XTR_PARTY_INFO. CE will pass the Bank Branch
457 * id for which it is going to create an account.
458 * IN/OUT:
459 *
460 * OUT:
461 * This Function returns a Boolean. TRUE if the Bank Branch exists in
462 * XTR_PARTY_INFO and FALSE if the Bank Branch does not exist in
463 * XTR_PARTY_INFO
464 * NOTES
465 *
466 * MODIFICATION HISTORY
467 *
468 * 06-17-2005 Bhargav Adireddy o Created.
469 *
470 */
471
472
473 FUNCTION CHK_BANK_BRANCH
474 ( p_ce_bank_branch_id IN XTR_PARTY_INFO.CE_BANK_BRANCH_ID%TYPE)
475 RETURN BOOLEAN IS
476
477 CURSOR c_check_bank IS
478 SELECT authorised
479 FROM XTR_PARTY_INFO
480 WHERE ce_bank_branch_id = p_ce_bank_branch_id;
481
482 l_bank_authorised VARCHAR2(10) := 'N';
483
484 BEGIN
485 IF(p_ce_bank_branch_id IS NOT NULL) THEN
486 OPEN c_check_bank;
487 FETCH c_check_bank into l_bank_authorised;
488 CLOSE c_check_bank;
489
490 IF(nvl(l_bank_authorised,'$$$') = '$$$') THEN
491 RETURN(FALSE);
492 ELSE
493 IF(l_bank_authorised = 'Y') THEN
494 RETURN(TRUE);
495 ELSE
496 RETURN(FALSE);
497 END IF;
498 END IF;
499 ELSE
500 RETURN(FALSE);
501 END IF;
502
503 END CHK_BANK_BRANCH;
504 END XTR_REPLICATE_BANK_BRANCHES_P;
505
[Home] [Help]