1 PACKAGE BODY XTR_REPLICATE_BANK_ACCOUNTS_P AS
2 /* | $Header: xtrrbacb.pls 120.9.12000000.2 2007/08/14 04:45:41 srsampat ship $ | */
3 /**
4 * PROCEDURE replicate_bank_accounts
5 *
6 * DESCRIPTION
7 * This is the main procedure that is called by CE to replicate the
8 * Bank/Bank Branch and Bank account data created in CE into XTR tables.
9 *
10 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
11 *
12 * ARGUMENTS
13 * IN:
14 * p_xtr_party_info_rec Record type of XTR_PARTY_INFO.
15 * This record type contains the Bank/Bank Branch
16 * related information about the bank attached with
17 * Bank Account.
18 * p_xtr_bank_account_rec Record type of XTR_BANK_ACCOUNTS
19 * This record type contains the Bank Account
20 * related information.
21 * p_action_flag Indicates wether Bank account information needs
22 * to be inserted or updated.
23 * 'I' - Insert,'U' - Update
24 * IN/OUT:
25 *
26 * OUT:
27 * x_return_status Return status after the call. The
28 * status can be
29 * FND_API.G_RET_STS_SUCCESS - for success
30 * FND_API.G_RET_STS_ERR - for expected error
31 * FND_API.G_RET_STS_UNEXP_ERR - for unexpected error
32 * x_msg_count To return the number of error messages
33 * in stack
34 * x_msg_data To return the error message if
35 * x_msg_count = 1.
36 * NOTES
37 *
38 * MODIFICATION HISTORY
39 *
40 * 05-19-2005 Bhargav Adireddy o Created.
41 *
42 */
43
44
45 PROCEDURE REPLICATE_BANK_ACCOUNTS
46 ( p_xtr_bank_account_rec IN XTR_BANK_ACCOUNTS%ROWTYPE,
47 p_action_flag IN VARCHAR2,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2)
51
52 IS
53
54 CURSOR c_branch_name
55 IS
56 select full_name
57 from xtr_party_info
58 where party_code = p_xtr_bank_account_rec.bank_code;
59
60 l_bank_branch_name xtr_party_info.full_name%TYPE;
61
62 BEGIN
63
64 x_msg_count := NULL;
65 FND_MSG_PUB.Initialize; -- Initializes the message list that stores the errors
66
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68 --
69 -- Verifies if the party_code in xtr_party_info is not same as the bank_code in xtr_bank_accounts
70 IF ((NOT XTR_REPLICATE_BANK_BRANCHES_P.CHK_BANK_BRANCH(p_xtr_bank_account_rec.bank_branch_id))
71 ) THEN -- The Bank does not exist or not authorized in XTR
72
73 OPEN c_branch_name;
74 FETCH c_branch_name INTO l_bank_branch_name;
75 CLOSE c_branch_name;
76 x_return_status := FND_API.G_RET_STS_ERROR;
77 XTR_REPLICATE_BANK_ACCOUNTS_P.LOG_ERR_MSG('XTR_INV_BANK_BRANCH',l_bank_branch_name);
78
79 END IF;
80
81 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
82 IF(p_action_flag = 'I') THEN -- If user has created a new bank account in CE
83
84
85
86 INSERT_BANK_ACCOUNTS(p_xtr_bank_account_rec,x_return_status);
87
88
89 ELSIF(p_action_flag = 'U')THEN -- If user has updated an existing bank
90 -- account in CE
91
92 UPDATE_BANK_ACCOUNTS(p_xtr_bank_account_rec,x_return_status);
93
94
95 ELSE
96
97 x_return_status := FND_API.G_RET_STS_ERROR;
98 LOG_ERR_MSG('XTR_INV_PARAM','ACTION_FLAG');
99
100
101 END IF;
102
103
104 END IF;
105
106 IF xtr_risk_debug_pkg.g_Debug THEN
107 xtr_risk_debug_pkg.dpush('xtr_replicate_bank_accounts_P: '||'Replicate_Bank_Accounts');
108 xtr_risk_debug_pkg.dlog('Replicate_Bank_Accounts: ' || 'bank_acct_rec.ce_bank_account_id',p_xtr_bank_account_rec.ce_bank_account_id);
109 xtr_risk_debug_pkg.dlog('Replicate_Bank_Accounts: ' || 'p_action_flag',p_action_flag);
110 xtr_risk_debug_pkg.dlog('Replicate_Bank_Accounts: ' || 'x_return_status' , x_return_status);
111 xtr_risk_debug_pkg.dlog('Replicate_Bank_Accounts: ' || 'x_msg_count' , x_msg_count);
112 xtr_risk_debug_pkg.dpop('xtr_replicate_bank_accounts_P: '||'Replicate_Bank_Accounts');
113 END IF;
114 --
115
116 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
117 ( p_count => x_msg_count ,
118 p_data => x_msg_data
119 );
120 --
121 EXCEPTION
122 WHEN others THEN
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
125 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
126 ( p_count => x_msg_count ,
127 p_data => x_msg_data
128 );
129 END REPLICATE_BANK_ACCOUNTS;
130
131 /* This procedure is written so that CE can pass the individual parameters instead of ROW TYPE */
132 PROCEDURE REPLICATE_BANK_ACCOUNTS
133 (p_account_number IN XTR_BANK_ACCOUNTS.account_number%TYPE,
134 p_authorised IN XTR_BANK_ACCOUNTS.authorised%TYPE,
135 p_party_code IN XTR_BANK_ACCOUNTS.party_code%TYPE,
136 p_party_type IN XTR_BANK_ACCOUNTS.party_type%TYPE,
137 p_bank_code IN XTR_BANK_ACCOUNTS.bank_code%TYPE,
138 p_currency IN XTR_BANK_ACCOUNTS.currency%TYPE,
139 p_bank_short_code IN XTR_BANK_ACCOUNTS.bank_short_code%TYPE,
140 p_default_acct IN XTR_BANK_ACCOUNTS.default_acct%TYPE,
141 p_eft_script_name IN XTR_BANK_ACCOUNTS.eft_script_name%TYPE,
142 p_code_combination_id IN XTR_BANK_ACCOUNTS.code_combination_id%TYPE,
143 p_interest_calculation_basis IN XTR_BANK_ACCOUNTS.interest_calculation_basis%TYPE,
144 p_location IN XTR_BANK_ACCOUNTS.location%TYPE,
145 p_portfolio_code IN XTR_BANK_ACCOUNTS.portfolio_code%TYPE,
146 p_primary_settlement_method IN XTR_BANK_ACCOUNTS.primary_settlement_method%TYPE,
147 p_street IN XTR_BANK_ACCOUNTS.street%TYPE,
148 p_year_calc_type IN XTR_BANK_ACCOUNTS.year_calc_type%TYPE,
149 p_swift_id IN XTR_BANK_ACCOUNTS.swift_id%TYPE,
150 p_attribute_category IN XTR_BANK_ACCOUNTS.attribute_category%TYPE,
151 p_attribute1 IN XTR_BANK_ACCOUNTS.attribute1%TYPE,
152 p_attribute2 IN XTR_BANK_ACCOUNTS.attribute2%TYPE,
153 p_attribute3 IN XTR_BANK_ACCOUNTS.attribute3%TYPE,
154 p_attribute4 IN XTR_BANK_ACCOUNTS.attribute4%TYPE,
155 p_attribute5 IN XTR_BANK_ACCOUNTS.attribute5%TYPE,
156 p_attribute6 IN XTR_BANK_ACCOUNTS.attribute6%TYPE,
157 p_attribute7 IN XTR_BANK_ACCOUNTS.attribute7%TYPE,
158 p_attribute8 IN XTR_BANK_ACCOUNTS.attribute8%TYPE,
159 p_attribute9 IN XTR_BANK_ACCOUNTS.attribute9%TYPE,
160 p_attribute10 IN XTR_BANK_ACCOUNTS.attribute10%TYPE,
161 p_attribute11 IN XTR_BANK_ACCOUNTS.attribute11%TYPE,
162 p_attribute12 IN XTR_BANK_ACCOUNTS.attribute12%TYPE,
163 p_attribute13 IN XTR_BANK_ACCOUNTS.attribute13%TYPE,
164 p_attribute14 IN XTR_BANK_ACCOUNTS.attribute14%TYPE,
165 p_attribute15 IN XTR_BANK_ACCOUNTS.attribute15%TYPE,
166 p_pricing_model IN XTR_BANK_ACCOUNTS.pricing_model%TYPE,
167 p_legal_account_name IN XTR_BANK_ACCOUNTS.legal_account_name%TYPE,
168 p_ce_bank_account_id IN XTR_BANK_ACCOUNTS.ce_bank_account_id%TYPE,
169 p_bank_branch_id IN XTR_BANK_ACCOUNTS.bank_branch_id%TYPE,
170 p_bank_acct_use_id IN XTR_BANK_ACCOUNTS.bank_acct_use_id%TYPE,
171 p_action_flag IN VARCHAR2,
172 x_return_status OUT NOCOPY VARCHAR2,
173 x_msg_count OUT NOCOPY NUMBER,
174 x_msg_data OUT NOCOPY VARCHAR2
175 ) IS
176 l_xtr_bank_accounts_rec XTR_BANK_ACCOUNTS%ROWTYPE;
177
178 BEGIN
179
180 l_xtr_bank_accounts_rec.account_number := p_account_number;
181 l_xtr_bank_accounts_rec.authorised := p_authorised;
182 l_xtr_bank_accounts_rec.party_code := p_party_code;
183 l_xtr_bank_accounts_rec.party_type := p_party_type;
184 l_xtr_bank_accounts_rec.bank_code := p_bank_code;
185 l_xtr_bank_accounts_rec.currency := p_currency;
186 l_xtr_bank_accounts_rec.bank_short_code := p_bank_short_code;
187 l_xtr_bank_accounts_rec.default_acct := p_default_acct;
188 l_xtr_bank_accounts_rec.eft_script_name := p_eft_script_name;
189 l_xtr_bank_accounts_rec.code_combination_id := p_code_combination_id;
190 l_xtr_bank_accounts_rec.interest_calculation_basis := p_interest_calculation_basis;
191 l_xtr_bank_accounts_rec.location := p_location;
192 l_xtr_bank_accounts_rec.portfolio_code := p_portfolio_code;
193 l_xtr_bank_accounts_rec.primary_settlement_method := p_primary_settlement_method;
194 -- Bug 6119714 Start
195 l_xtr_bank_accounts_rec.street := SUBSTR(p_street,1,35);
196 -- Bug 6119714 end
197 l_xtr_bank_accounts_rec.year_calc_type := p_year_calc_type;
198 l_xtr_bank_accounts_rec.swift_id := p_swift_id;
199 l_xtr_bank_accounts_rec.attribute_category := p_attribute_category;
200 l_xtr_bank_accounts_rec.attribute1 := p_attribute1;
201 l_xtr_bank_accounts_rec.attribute2 := p_attribute2;
202 l_xtr_bank_accounts_rec.attribute3 := p_attribute3;
203 l_xtr_bank_accounts_rec.attribute4 := p_attribute4;
204 l_xtr_bank_accounts_rec.attribute5 := p_attribute5;
205 l_xtr_bank_accounts_rec.attribute6 := p_attribute6;
206 l_xtr_bank_accounts_rec.attribute7 := p_attribute7;
207 l_xtr_bank_accounts_rec.attribute8 := p_attribute8;
208 l_xtr_bank_accounts_rec.attribute9 := p_attribute9;
209 l_xtr_bank_accounts_rec.attribute10 := p_attribute10;
210 l_xtr_bank_accounts_rec.attribute11 := p_attribute11;
211 l_xtr_bank_accounts_rec.attribute12 := p_attribute12;
212 l_xtr_bank_accounts_rec.attribute13 := p_attribute13;
213 l_xtr_bank_accounts_rec.attribute14 := p_attribute14;
214 l_xtr_bank_accounts_rec.attribute15 := p_attribute15;
215 l_xtr_bank_accounts_rec.pricing_model := p_pricing_model;
216 l_xtr_bank_accounts_rec.legal_account_name := p_legal_account_name;
217 l_xtr_bank_accounts_rec.ce_bank_account_id := p_ce_bank_account_id;
218 l_xtr_bank_accounts_rec.bank_branch_id := p_bank_branch_id;
219 l_xtr_bank_accounts_rec.bank_acct_use_id := p_bank_acct_use_id;
220
221 REPLICATE_BANK_ACCOUNTS(l_xtr_bank_accounts_rec,p_action_flag,
222 x_return_status,x_msg_count,x_msg_data);
223
224 EXCEPTION
225 WHEN others THEN
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
228 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
229 ( p_count => x_msg_count ,
230 p_data => x_msg_data
231 );
232 END REPLICATE_BANK_ACCOUNTS;
233
234
235
236 /**
237 * PROCEDURE insert_bank_accounts
238 *
239 * DESCRIPTION
240 * This procedure is called in replicate_bank_accounts to insert
241 * the bank account related data into XTR tables. This procedure is
242 * called when p_action_flag = 'I'
243 *
244 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
245 *
246 * ARGUMENTS
247 * IN:
248 * p_xtr_bank_account_rec Record type of XTR_BANK_ACCOUNTS
249 * This record type contains the Bank Account
250 * related information.
251 * IN/OUT:
252 *
253 * OUT:
254 * x_return_status Return status after the call. The
255 * status can be
256 * FND_API.G_RET_STS_SUCCESS - for success
257 * FND_API.G_RET_STS_ERR - for expected error
258 * FND_API.G_RET_STS_UNEXP_ERR - for unexpected error
259 * NOTES
260 *
261 * MODIFICATION HISTORY
262 *
263 * 05-19-2005 Bhargav Adireddy o Created.
264 *
265 */
266
267
268 PROCEDURE INSERT_BANK_ACCOUNTS
269 ( p_xtr_bank_account_rec IN XTR_BANK_ACCOUNTS%ROWTYPE,
270 x_return_status OUT NOCOPY VARCHAR2
271 )
272
273 IS
274
275
276 BEGIN
277
278 --
279 VALIDATE_BANK_ACCOUNTS(p_xtr_bank_account_rec,x_return_status);
280
281 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
282 /* All validations for bank account are true */
283 MODIFY_BANK_ACCOUNTS(p_xtr_bank_account_rec,'I'
284 ,x_return_status);
285
286 END IF;
287
288
289
290 IF xtr_risk_debug_pkg.g_Debug THEN
291 xtr_risk_debug_pkg.dpush('xtr_replicate_bank_accounts_P: '||'Insert_Bank_Accounts');
292 xtr_risk_debug_pkg.dlog('Insert_Bank_Accounts: ' || 'bank_acct_rec.ce_bank_account_id',p_xtr_bank_account_rec.ce_bank_account_id);
293 xtr_risk_debug_pkg.dlog('Insert_Bank_Accounts: ' || 'x_return_status' , x_return_status);
294 xtr_risk_debug_pkg.dpop('xtr_replicate_bank_accounts_P: '||'Insert_Bank_Accounts');
295 END IF;
296
297
298 EXCEPTION
299 WHEN others THEN
300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
301 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
302 END INSERT_BANK_ACCOUNTS;
303
304 /**
305 * PROCEDURE update_bank_accounts
306 *
307 * DESCRIPTION
308 * This procedure is called in replicate_bank_accounts to update
309 * the bank account related data into XTR tables. This procedure is
310 * called when p_action_flag = 'I'
311 *
312 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
313 *
314 * ARGUMENTS
315 * IN:
316 *
317 * p_xtr_bank_account_rec Record type of XTR_BANK_ACCOUNTS
318 * This record type contains the Bank Account
319 * related information.
320 * IN/OUT:
321 *
322 * OUT:
323 * x_return_status Return status after the call. The
324 * status can be
325 * FND_API.G_RET_STS_SUCCESS - for success
326 * FND_API.G_RET_STS_ERR - for expected error
327 * FND_API.G_RET_STS_UNEXP_ERR - for unexpected error
328 * NOTES
329 *
330 * MODIFICATION HISTORY
331 *
332 * 05-19-2005 Bhargav Adireddy o Created.
333 *
334 */
335
336 PROCEDURE UPDATE_BANK_ACCOUNTS
337 ( p_xtr_bank_account_rec IN XTR_BANK_ACCOUNTS%ROWTYPE,
338 x_return_status OUT NOCOPY VARCHAR2
339 )
340 IS
341
342 BEGIN
343
344 -- This account already exists in XTR and it is being updated in CE
345 VALIDATE_BANK_ACCOUNTS(p_xtr_bank_account_rec
346 ,x_return_status);
347
348 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
349 /* All validations for bank account are true */
350 MODIFY_BANK_ACCOUNTS(p_xtr_bank_account_rec,'U'
351 ,x_return_status);
352
353 END IF;
354 IF xtr_risk_debug_pkg.g_Debug THEN
355 xtr_risk_debug_pkg.dpush('xtr_replicate_bank_accounts_P: '||'Update_Bank_Accounts');
356 xtr_risk_debug_pkg.dlog('Insert_Bank_Accounts: ' || 'bank_acct_rec.ce_bank_account_id',p_xtr_bank_account_rec.ce_bank_account_id);
357 xtr_risk_debug_pkg.dlog('Update_Bank_Accounts: ' || 'x_return_status', x_return_status);
358 xtr_risk_debug_pkg.dpop('xtr_replicate_bank_accounts_P: '||'Update_Bank_Accounts');
359 END IF;
360
361 EXCEPTION
362 WHEN others THEN
363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
364 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
365 END UPDATE_BANK_ACCOUNTS;
366
367
368 /**
369 * PROCEDURE validate_bank_accounts
370 *
371 * This procedure is used to validate the Bank account related data before
372 * it is inserted/updated into XTR_BANK_ACCOUNTS. This procedure will
376 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
373 * perform the required validations and puts the corresponding error
374 * messages into list
375 *
377 *
378 * ARGUMENTS
379 * IN:
380 * p_xtr_bank_account_rec Record type of XTR_BANK_ACCOUNTS
381 * This record type contains the Bank Account
382 * related information.
383 * IN/OUT:
384 *
385 * OUT:
386 * x_return_status Return status after the call. The
387 * status can be
388 * FND_API.G_RET_STS_SUCCESS - for success
389 * FND_API.G_RET_STS_ERR - for expected error
390 * FND_API.G_RET_STS_UNEXP_ERR - for unexpected error
391 * NOTES
392 *
393 * MODIFICATION HISTORY
394 *
395 * 05-19-2005 Bhargav Adireddy o Created.
396 *
397 */
398
399 PROCEDURE VALIDATE_BANK_ACCOUNTS
400 ( p_xtr_bank_account_rec IN XTR_BANK_ACCOUNTS%ROWTYPE,
401 x_return_status IN OUT NOCOPY VARCHAR2
402 )
403 IS
404 -- This cursor verifies if the code combination id passed is defined in
405 -- XTR_GL_REFERENCES_V for that company
406 CURSOR c_cc_id IS
407 SELECT 'Y'
408 FROM XTR_GL_REFERENCES_V
409 WHERE code_combination_id = p_xtr_bank_account_rec.code_combination_id
410 AND company_code = p_xtr_bank_account_rec.party_code;
411 -- This cursor verifies if the pricing model passed is authorized or not
412 cursor c_pm_authorized is
413 select authorized
414 from xtr_price_models_v
415 where deal_type = 'CA'
416 and code = p_xtr_bank_account_rec.pricing_model;
417 -- This cursor verifies if the party_code passed is
418 -- of a valid company or not
419 cursor c_valid_company is
420 select authorised,legal_entity_id
421 from xtr_parties_v
422 where party_code = p_xtr_bank_account_rec.party_code
423 and party_type = 'C';
424 -- This cursor Verfies if A default account already exists for this company/currency combination.
425 cursor c_default_acct is
426 select default_acct,ce_bank_account_id -- Modified Bug 4764437
427 from xtr_bank_accounts
428 where party_code = p_xtr_bank_account_rec.party_code
429 and currency = p_xtr_bank_account_rec.currency
430 and default_acct = 'Y';
431
432
433 l_cc_id VARCHAR2(2);
434 l_pm_authorized XTR_BANK_ACCOUNTS.PRICING_MODEL%TYPE;
435 l_valid_company VARCHAR2(2);
436 l_le_id XTR_PARTY_INFO.legal_entity_id%TYPE;
437 l_default_acct VARCHAR2(2);
438 l_chk_default XTR_BANK_ACCOUNTS.CE_BANK_ACCOUNT_ID%TYPE; -- Added Bug 4764437
439
440 -- Enter the procedure variables here. As shown below
441 --variable_name datatype NOT NULL DEFAULT default_value;
442 BEGIN
443 x_return_status := FND_API.G_RET_STS_SUCCESS;
444
445 -- Verifies if the ce_bank_account_id in XTR_BANK_ACCOUNTS is passed as null
446 IF(p_xtr_bank_account_rec.ce_bank_account_id is null) THEN
447 x_return_status := FND_API.G_RET_STS_ERROR;
448 LOG_ERR_MSG('XTR_INV_PARAM','XTR_BANK_ACCOUNTS.ce_bank_account_id');
449 END IF;
450 -- Verifies if the bank_branch_id in XTR_BANK_ACCOUNTS is passed as null
451 IF(p_xtr_bank_account_rec.bank_branch_id is null) THEN
452 x_return_status := FND_API.G_RET_STS_ERROR;
453 LOG_ERR_MSG('XTR_INV_PARAM','XTR_BANK_ACCOUNTS.BANK_BRANCH_ID');
454 END IF;
455 /* Removed the validation Bug 4582759
456 -- Verifies if the bank_acct_use_id in XTR_BANK_ACCOUNTS is passed as null
457 IF(p_xtr_bank_account_rec.bank_acct_use_id is null) THEN
458 x_return_status := FND_API.G_RET_STS_ERROR;
459 LOG_ERR_MSG('XTR_INV_PARAM','XTR_BANK_ACCOUNTS.BANK_ACCT_USE_ID');
460 END IF;
461 */
462 -- Verifies if bank_short_code in XTR_BANK_ACCOUNTS is passed as null
463 IF(p_xtr_bank_account_rec.bank_short_code is null) THEN
464 x_return_status := FND_API.G_RET_STS_ERROR;
465 LOG_ERR_MSG('XTR_ACCT_NAME_MANDATORY');
466 END IF;
467 -- Verifies if currency in XTR_BANK_ACCOUNTS is passed as null
468 IF(p_xtr_bank_account_rec.currency is null ) THEN
469 x_return_status := FND_API.G_RET_STS_ERROR;
470 LOG_ERR_MSG('XTR_CURRENCY_MANDATORY');
471 END IF;
472 -- Verifies if location in XTR_BANK_ACCOUNTS is passed as null
473 IF(p_xtr_bank_account_rec.location is null) THEN
474 x_return_status := FND_API.G_RET_STS_ERROR;
475 LOG_ERR_MSG('XTR_LOCATION_MANDATORY');
476 END IF;
477 -- Verifies if street in XTR_BANK_ACCOUNTS is passed as null
478 IF(p_xtr_bank_account_rec.street is null) THEN
479 x_return_status := FND_API.G_RET_STS_ERROR;
480 LOG_ERR_MSG('XTR_STREET_MANDATORY');
481 END IF;
482 -- Verifies if party_code in XTR_BANK_ACCOUNTS is passed as null
483 IF(p_xtr_bank_account_rec.party_code is null ) THEN
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 LOG_ERR_MSG('XTR_ACCT_OWNER_MANDATORY');
486 END IF;
487 -- Verifies if account_number in XTR_BANK_ACCOUNTS is passed as null
488 IF(p_xtr_bank_account_rec.account_number is null ) THEN
489 x_return_status := FND_API.G_RET_STS_ERROR;
490 LOG_ERR_MSG('XTR_ACCOUNT_MANDATORY_FIELD');
491 END IF;
492
493
494 OPEN c_pm_authorized;
495 FETCH c_pm_authorized INTO l_pm_authorized;
499 x_return_status := FND_API.G_RET_STS_ERROR;
496 CLOSE c_pm_authorized;
497 -- Verifies if pricing model in XTR_BANK_ACCOUNTS is authorized or not
498 IF(nvl(l_pm_authorized,'N') = 'N' AND p_xtr_bank_account_rec.pricing_model IS NOT NULL ) THEN
500 LOG_ERR_MSG('XTR_INV_TRS_PRICING_MODEL');
501 END IF;
502
503 OPEN c_cc_id;
504 FETCH c_cc_id INTO l_cc_id;
505 CLOSE c_cc_id;
506 -- Verifies if code combination id in XTR_BANK_ACCOUNTS is authorized or not
507 IF(nvl(l_cc_id,'N') = 'N' AND p_xtr_bank_account_rec.code_combination_id IS NOT NULL
508 AND p_xtr_bank_account_rec.party_code IS NOT NULL) THEN
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 LOG_ERR_MSG('XTR_INV_CC_ID');
511 END IF;
512
513 OPEN C_valid_company;
514 FETCH C_valid_company INTO l_valid_company,l_le_id;
515 CLOSE C_valid_company;
516 -- Verifies if company exists in XTR_PARTIES_V
517 IF(nvl(l_valid_company,'N') = 'N' and p_xtr_bank_account_rec.party_code IS NOT NULL) THEN
518 x_return_status := FND_API.G_RET_STS_ERROR;
519 LOG_ERR_MSG('XTR_INV_LE_COMP_CODE',l_le_id);
520 END IF;
521 OPEN C_default_acct;
522 FETCH C_default_acct INTO l_default_acct,l_chk_default;
523 CLOSE C_default_acct;
524 -- Verfies if A default account already exists for this company/currency combination.
525 IF((nvl(l_default_acct,'N') = 'Y') AND p_xtr_bank_account_rec.default_acct = 'Y'
526 AND (nvl(l_chk_default,-1) <> p_xtr_bank_account_rec.ce_bank_account_id) ) THEN -- Modified Bug 4764437
527 x_return_status := FND_API.G_RET_STS_ERROR;
528 LOG_ERR_MSG('XTR_DEFAULT_ACCT');
529 END IF;
530 -- Verifies if year calculated basis is '30/' and a day count basis type of Both Days for the same deal.
531 IF(substr(p_xtr_bank_account_rec.year_calc_type,1,2) = '30'
532 and p_xtr_bank_account_rec.day_count_type = 'B') THEN
533 x_return_status := FND_API.G_RET_STS_ERROR;
534 LOG_ERR_MSG('XTR_CHK_30_BOTH');
535 END IF;
536
537 IF xtr_risk_debug_pkg.g_Debug THEN
538 xtr_risk_debug_pkg.dpush('xtr_replicate_bank_accounts_P: '||'Validate_Bank_Accounts');
539 xtr_risk_debug_pkg.dlog('Validate_Bank_Accounts: ' || 'bank_acct_rec.ce_bank_account_id',p_xtr_bank_account_rec.ce_bank_account_id);
540 xtr_risk_debug_pkg.dlog('Validate_Bank_Accounts: ' || 'bank_acct_rec.bank_code',p_xtr_bank_account_rec.bank_code);
541 xtr_risk_debug_pkg.dlog('Validate_Bank_Accounts: ' || 'bank_acct_rec.bank_short_code',p_xtr_bank_account_rec.bank_short_code);
542 xtr_risk_debug_pkg.dlog('Validate_Bank_Accounts: ' || 'bank_acct_rec.currency',p_xtr_bank_account_rec.currency);
543 xtr_risk_debug_pkg.dlog('Validate_Bank_Accounts: ' || 'bank_acct_rec.location',p_xtr_bank_account_rec.location);
544 xtr_risk_debug_pkg.dlog('Validate_Bank_Accounts: ' || 'bank_acct_rec.street',p_xtr_bank_account_rec.street);
545 xtr_risk_debug_pkg.dlog('Validate_Bank_Accounts: ' || 'bank_acct_rec.party_code',p_xtr_bank_account_rec.party_code);
546 xtr_risk_debug_pkg.dlog('Validate_Bank_Accounts: ' || 'bank_acct_rec.year_calc_type',p_xtr_bank_account_rec.year_calc_type);
547 xtr_risk_debug_pkg.dlog('Validate_Bank_Accounts: ' || 'bank_acct_rec.account_number',p_xtr_bank_account_rec.account_number);
548 xtr_risk_debug_pkg.dlog('Validate_Bank_Accounts: ' || 'bank_acct_rec.pricing_model',p_xtr_bank_account_rec.pricing_model);
549 xtr_risk_debug_pkg.dlog('Validate_Bank_Accounts: ' || 'bank_acct_rec.default_acct',p_xtr_bank_account_rec.default_acct);
550 xtr_risk_debug_pkg.dlog('Validate_Bank_Accounts: ' || 'x_return_status', x_return_status);
551 xtr_risk_debug_pkg.dpop('xtr_replicate_bank_accounts_P: '||'Validate_Bank_Accounts');
552 END IF;
553
554 EXCEPTION
555 WHEN others THEN
556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
557 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
558 END VALIDATE_BANK_ACCOUNTS;
559
560
561 /**
562 * PROCEDURE modify_bank_accounts
563 *
564 * DESCRIPTION
565 * This procedure will insert/update XTR_BANK_ACCOUNTS table with the
566 * Bank account data passed form CE. It will insert if p_action_flag = 'I'
567 * and update if p_action_flag = 'U'
568 *
569 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
570 *
571 * ARGUMENTS
572 * IN:
573 * p_xtr_bank_account_rec Record type of XTR_BANK_ACCOUNTS
574 * This record type contains the Bank Account
575 * p_action_flag Indicates wether Bank account information needs
576 * to be inserted or updated.
577 * 'I' - Insert,'U' - Update related information.
578 * IN/OUT:
579 *
580 * OUT:
581 * x_return_status Return status after the call. The
582 * status can be
583 * FND_API.G_RET_STS_SUCCESS - for success
584 * FND_API.G_RET_STS_ERR - for expected error
585 * FND_API.G_RET_STS_UNEXP_ERR - for unexpected error
586 * NOTES
587 *
588 * MODIFICATION HISTORY
589 *
590 * 05-19-2005 Bhargav Adireddy o Created.
591 *
592 */
593
594
595 PROCEDURE MODIFY_BANK_ACCOUNTS
596 ( p_xtr_bank_account_rec IN XTR_BANK_ACCOUNTS%ROWTYPE,
597 p_action_flag IN VARCHAR2,
598 x_return_status IN OUT NOCOPY VARCHAR2
599 )
603 CURSOR c_chk_lock IS
600
601 IS
602 -- This cursor verifies if there is a lock on the row that user is trying to update
604 SELECT ce_bank_account_id
605 FROM XTR_BANK_ACCOUNTS
606 WHERE ce_bank_account_id = p_xtr_bank_account_rec.ce_bank_account_id
607 FOR UPDATE NOWAIT;
608 record_lock EXCEPTION;
609 l_ce_bank_account_id XTR_BANK_ACCOUNTS.ce_bank_account_id%TYPE;
610
611 BEGIN
612 -- Bug 5137819 Changed to insert the bank account in treasury while updation of treasury use
613 -- allowed flag in CE after the bank account is created for that bank branch.
614
615 OPEN c_chk_lock;
616 FETCH c_chk_lock INTO l_ce_bank_account_id;
617 close c_chk_lock ;
618
619 IF ( (p_action_flag = 'I') or (p_action_flag = 'U' and l_ce_bank_account_id Is Null )) THEN -- The bank account has to be inserted
620
621 INSERT INTO XTR_BANK_ACCOUNTS
622 (account_number
623 ,authorised
624 ,party_code
625 ,party_type
626 ,bank_code
627 ,currency
628 ,bank_short_code
629 ,default_acct
630 ,created_by
631 ,created_on
632 ,eft_script_name
633 ,code_combination_id
634 ,interest_calculation_basis
635 ,location
636 ,portfolio_code
637 ,primary_settlement_method
638 ,street
639 ,year_calc_type
640 ,swift_id
641 ,attribute_category
642 ,attribute1
643 ,attribute2
644 ,attribute3
645 ,attribute4
646 ,attribute5
647 ,attribute6
648 ,attribute7
649 ,attribute8
650 ,attribute9
651 ,attribute10
652 ,attribute11
653 ,attribute12
654 ,attribute13
655 ,attribute14
656 ,attribute15
657 ,pricing_model
658 ,legal_account_name
659 ,ce_bank_account_id
660 ,bank_branch_id
661 ,bank_acct_use_id
662 ,gl_company_code
663 )
664 VALUES
665 (p_xtr_bank_account_rec.account_number
666 ,'Y'
667 ,p_xtr_bank_account_rec.party_code
668 ,'C'
669 ,p_xtr_bank_account_rec.bank_code
670 ,p_xtr_bank_account_rec.currency
671 ,p_xtr_bank_account_rec.bank_short_code
672 ,p_xtr_bank_account_rec.default_acct
673 ,fnd_global.user_id
674 ,sysdate
675 ,p_xtr_bank_account_rec.eft_script_name
676 ,p_xtr_bank_account_rec.code_combination_id
677 ,p_xtr_bank_account_rec.interest_calculation_basis
678 ,p_xtr_bank_account_rec.location
679 ,p_xtr_bank_account_rec.portfolio_code
680 ,'E'
681 ,p_xtr_bank_account_rec.street
682 ,p_xtr_bank_account_rec.year_calc_type
683 ,p_xtr_bank_account_rec.swift_id
684 ,p_xtr_bank_account_rec.attribute_category
685 ,p_xtr_bank_account_rec.attribute1
686 ,p_xtr_bank_account_rec.attribute2
687 ,p_xtr_bank_account_rec.attribute3
688 ,p_xtr_bank_account_rec.attribute4
689 ,p_xtr_bank_account_rec.attribute5
690 ,p_xtr_bank_account_rec.attribute6
691 ,p_xtr_bank_account_rec.attribute7
692 ,p_xtr_bank_account_rec.attribute8
693 ,p_xtr_bank_account_rec.attribute9
694 ,p_xtr_bank_account_rec.attribute10
695 ,p_xtr_bank_account_rec.attribute11
696 ,p_xtr_bank_account_rec.attribute12
697 ,p_xtr_bank_account_rec.attribute13
698 ,p_xtr_bank_account_rec.attribute14
699 ,p_xtr_bank_account_rec.attribute15
700 ,p_xtr_bank_account_rec.pricing_model
701 ,p_xtr_bank_account_rec.legal_account_name
702 ,p_xtr_bank_account_rec.ce_bank_account_id
703 ,p_xtr_bank_account_rec.bank_branch_id
704 ,p_xtr_bank_account_rec.bank_acct_use_id
705 ,p_xtr_bank_account_rec.party_code
706 );
707
708 ELSIF (p_action_flag = 'U' and l_ce_bank_account_id Is Not Null ) THEN -- The bank account has to be updated
709
710 UPDATE XTR_BANK_ACCOUNTS
711 SET authorised = p_xtr_bank_account_rec.authorised
712 ,bank_code = p_xtr_bank_account_rec.bank_code
713 ,bank_short_code = p_xtr_bank_account_rec.bank_short_code
714 ,default_acct = p_xtr_bank_account_rec.default_acct
715 ,eft_script_name = p_xtr_bank_account_rec.eft_script_name
716 ,code_combination_id = p_xtr_bank_account_rec.code_combination_id
717 -- ,interest_calculation_basis = p_xtr_bank_account_rec.interest_calculation_basis Bug 5398434
718 ,location = p_xtr_bank_account_rec.location
719 ,portfolio_code = p_xtr_bank_account_rec.portfolio_code
720 ,street = p_xtr_bank_account_rec.street
721 ,updated_by = fnd_global.user_id
722 ,updated_on = sysdate
723 -- ,year_calc_type = p_xtr_bank_account_rec.year_calc_type Bug 5398434
724 ,swift_id = p_xtr_bank_account_rec.swift_id
725 ,attribute_category = p_xtr_bank_account_rec.attribute_category
726 ,attribute1 = p_xtr_bank_account_rec.attribute1
727 ,attribute2 = p_xtr_bank_account_rec.attribute2
728 ,attribute3 = p_xtr_bank_account_rec.attribute3
729 ,attribute4 = p_xtr_bank_account_rec.attribute4
730 ,attribute5 = p_xtr_bank_account_rec.attribute5
731 ,attribute6 = p_xtr_bank_account_rec.attribute6
732 ,attribute7 = p_xtr_bank_account_rec.attribute7
733 ,attribute8 = p_xtr_bank_account_rec.attribute8
734 ,attribute9 = p_xtr_bank_account_rec.attribute9
735 ,attribute10 = p_xtr_bank_account_rec.attribute10
736 ,attribute11 = p_xtr_bank_account_rec.attribute11
737 ,attribute12 = p_xtr_bank_account_rec.attribute12
738 ,attribute13 = p_xtr_bank_account_rec.attribute13
739 ,attribute14 = p_xtr_bank_account_rec.attribute14
740 ,attribute15 = p_xtr_bank_account_rec.attribute15
741 ,pricing_model = p_xtr_bank_account_rec.pricing_model
742 ,legal_account_name = p_xtr_bank_account_rec.legal_account_name
743 ,currency = p_xtr_bank_account_rec.currency
744 WHERE ce_bank_account_id = l_ce_bank_account_id; -- change this
745
746 END IF;
747
748
749
750 IF xtr_risk_debug_pkg.g_Debug THEN
751 xtr_risk_debug_pkg.dpush('xtr_replicate_bank_accounts_P: '||'Modify_Bank_Accounts');
752 xtr_risk_debug_pkg.dlog('Modify_Bank_Accounts: ' || 'bank_acct_rec.ce_bank_account_id',p_xtr_bank_account_rec.ce_bank_account_id);
753 xtr_risk_debug_pkg.dlog('Modify_Bank_Accounts: ' || 'p_action_flag', p_action_flag);
754 xtr_risk_debug_pkg.dlog('Modify_Bank_Accounts: ' || 'x_return_status', x_return_status);
755 xtr_risk_debug_pkg.dpop('xtr_replicate_bank_accounts_P: '||'Modify_Bank_Accounts');
756 END IF;
757
758 EXCEPTION
759 When app_exceptions.RECORD_LOCK_EXCEPTION then -- If the record is locked
760 if C_CHK_LOCK%ISOPEN then
761 close c_CHK_LOCK;
762 end if;
763 LOG_ERR_MSG('CHK_LOCK');
764 x_return_status := FND_API.G_RET_STS_ERROR;
765 --app_exceptions.RECORD_LOCK_EXCEPTION;
766 WHEN DUP_VAL_ON_INDEX then -- bug 4870353
767 x_return_status := FND_API.G_RET_STS_ERROR;
768 LOG_ERR_MSG('XTR_UNIQUE_ACCOUNT');
769 WHEN others THEN
770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
771 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
772 END MODIFY_BANK_ACCOUNTS;
773
774 /**
775 * PROCEDURE log_err_msg
776 *
777 * DESCRIPTION
778 * This procedure will attach the tokens with the error messages and puts
779 * all the error messages into a list. CE can extract the error messages
780 * from this list and show them to the user.
781 *
782 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
783 *
784 * ARGUMENTS
785 * IN:
786 * p_error_code This will pass the message_name
787 * which has to be put into the list.
788 * p_field_name This will pass the token which has to be
789 * attached with the error message.
790 * IN/OUT:
791 *
792 * OUT:
793 *
794 * NOTES
795 *
796 * MODIFICATION HISTORY
797 *
798 * 05-19-2005 Bhargav Adireddy o Created.
799 *
800 */
801
802 PROCEDURE LOG_ERR_MSG
803 ( p_error_code IN VARCHAR2,
804 p_field_name IN VARCHAR2 default null,
808 IS
805 p_field_name2 IN VARCHAR2 default null
806 )
807
809
810 CURSOR c_field_name(p_name VARCHAR2) IS
811 SELECT text
812 FROM xtr_sys_languages_vl
813 WHERE item_name = p_name
814 AND MODULE_NAME = 'XTRSECOM'
815 UNION
816 SELECT text
817 FROM xtr_sys_languages_vl
818 WHERE item_name = p_name
819 AND MODULE_NAME = 'XTRSECPY';
820
821
822
823 l_field_name xtr_sys_languages_vl.text%TYPE;
824
825 BEGIN
826
827 IF p_error_code = 'XTR_MANDATORY' THEN
828 OPEN c_field_name(p_field_name);
829 FETCH c_field_name INTO l_field_name;
830 CLOSE c_field_name;
831 FND_MESSAGE.Set_Name('XTR','XTR_MANDATORY_FIELD');
832 FND_MESSAGE.Set_Token('FIELD', l_field_name);
833 FND_MSG_PUB.Add; -- Adds the error messages to the list.
834
835 ELSIF p_error_code = 'XTR_INV_TRS_PRICING_MODEL' THEN
836
837 FND_MESSAGE.Set_Name('XTR','XTR_INV_TRS_PRICING_MODEL');
838 FND_MSG_PUB.Add; -- Adds the error messages to the list.
839
840 ELSIF p_error_code = 'XTR_INV_CC_ID' THEN
841
842 FND_MESSAGE.Set_Name('XTR','XTR_INV_CC_ID');
843 FND_MSG_PUB.Add; -- Adds the error messages to the list.
844
845 ELSIF p_error_code = 'XTR_UNIQUE_ACCOUNT' THEN -- bug 4870353
846
847 FND_MESSAGE.Set_Name('XTR','XTR_UNIQUE_ACCOUNT');
848 FND_MSG_PUB.Add;
849
850 ELSIF p_error_code = 'XTR_UNEXP_ERROR' THEN
851
852 FND_MESSAGE.Set_Name('XTR','XTR_UNEXP_ERROR');
853 FND_MESSAGE.Set_Token('SQLCODE', p_field_name);
854 --FND_MESSAGE.Set_Token('SQLSTATE', p_field_name2);
855 FND_MSG_PUB.Add; -- Adds the error messages to the list.
856
857 ELSIF p_error_code = 'XTR_INV_LE_COMP_CODE' THEN
858
859 FND_MESSAGE.Set_Name('XTR','XTR_INV_LE_COMP_CODE');
860 FND_MESSAGE.Set_Token('LEGAL_ENTITY', p_field_name);
861 FND_MSG_PUB.Add; -- Adds the error messages to the list.
862
863 ELSIF p_error_code = 'CHK_LOCK' THEN
864
865 FND_MESSAGE.Set_Name('XTR','XTR_1999');
866 FND_MSG_PUB.Add; -- Adds the error messages to the list.
867
868 ELSIF p_error_code = 'XTR_INV_PARAM' THEN
869
870 FND_MESSAGE.Set_Name('XTR','XTR_INV_PARAMETER');
871 FND_MESSAGE.Set_Token('FIELD', p_field_name);
872 FND_MSG_PUB.Add; -- Adds the error messages to the list.
873
874 ELSIF p_error_code = 'XTR_DEFAULT_ACCT' THEN
875
876 FND_MESSAGE.Set_Name('XTR','XTR_1676');
877 FND_MSG_PUB.Add; -- Adds the error messages to the list.
878
879 ELSIF p_error_code = 'XTR_CHK_30_BOTH' THEN
880
881 FND_MESSAGE.Set_Name('XTR','XTR_CHK_30_BOTH');
882 FND_MSG_PUB.Add; -- Adds the error messages to the list.
883
884 ELSIF p_error_code = 'XTR_INV_BANK_BRANCH' THEN
885
886 FND_MESSAGE.Set_Name('XTR','XTR_INV_BANK_BRANCH');
887 FND_MESSAGE.Set_Token('TCA_BANK_BRANCH_NAME', p_field_name);
888 FND_MSG_PUB.Add; -- Adds the error messages to the list.
889
890 ELSIF p_error_code = 'XTR_ACCT_NAME_MANDATORY' THEN
891
892 FND_MESSAGE.Set_Name('XTR','XTR_ACCT_NAME_MANDATORY');
893 FND_MSG_PUB.Add; -- Adds the error messages to the list.
894
895 ELSIF p_error_code = 'XTR_CURRENCY_MANDATORY' THEN
896
897 FND_MESSAGE.Set_Name('XTR','XTR_CURRENCY_MANDATORY');
898 FND_MSG_PUB.Add; -- Adds the error messages to the list.
899
900 ELSIF p_error_code = 'XTR_LOCATION_MANDATORY' THEN
901
902 FND_MESSAGE.Set_Name('XTR','XTR_LOCATION_MANDATORY');
903 FND_MSG_PUB.Add; -- Adds the error messages to the list.
904
905 ELSIF p_error_code = 'XTR_STREET_MANDATORY' THEN
906
907 FND_MESSAGE.Set_Name('XTR','XTR_STREET_MANDATORY');
908 FND_MSG_PUB.Add; -- Adds the error messages to the list.
909
910 ELSIF p_error_code = 'XTR_ACCT_OWNER_MANDATORY' THEN
911
912 FND_MESSAGE.Set_Name('XTR','XTR_ACCT_OWNER_MANDATORY');
913 FND_MSG_PUB.Add; -- Adds the error messages to the list.
914
915 ELSIF p_error_code = 'XTR_ACCOUNT_MANDATORY_FIELD' THEN
916
917 FND_MESSAGE.Set_Name('XTR','XTR_ACCOUNT_MANDATORY_FIELD');
918 FND_MSG_PUB.Add; -- Adds the error messages to the list.
919
920
921
922
923 END IF;
924
925 EXCEPTION
926 WHEN others THEN
927
928 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
929 END LOG_ERR_MSG;
930
931 /**
932 * PROCEDURE replicate_interest_schedules
933 *
934 * DESCRIPTION
935 * This is the main procedure that is called by CE to replicate the
936 * interest schedules data created in CE into XTR tables.
937 *
938 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
939 *
940 * ARGUMENTS
941 * IN:
942 * p_ce_bank_account_id This is the CE_BANK_ACCOUNT_ID in XTR_PARTY_INFO.
943 * CE will pass this parameter. This will tell us
944 * which Bank Account is attached with the interest
945 * schedule being updated.
946 * p_interest_rounding This is the ROUNDING_TYPE in XTR_BANK_ACCOUNTS.
947 * CE will pass this parameter. This will tell us
948 * what is the updated interest rounding.
949 * p_interest_includes This is the DAY_COUNT_TYPE in XTR_BANK_ACCOUNTS.
950 * CE will pass this parameter. This will tell us
951 * what is the updated Interest Includes.
952 * p_interest_calculation
953 * _basis This is the BASIS in XTR_BANK_ACCOUNTS.
954 * CE will pass this parameter. This will tell us
958 * what is the updated day count basis.
955 * what is the updated BASIS.
956 * p_day_count_basis This is the DAY_COUNT_BASIS in XTR_BANK_ACCOUNTS.
957 * CE will pass this parameter. This will tell us
959 * IN/OUT:
960 *
961 * OUT:
962 * x_return_status Return status after the call. The
963 * status can be
964 * FND_API.G_RET_STS_SUCCESS - for success
965 * FND_API.G_RET_STS_ERR - for expected error
966 * FND_API.G_RET_STS_UNEXP_ERR - for unexpected error
967 * x_msg_count To return the number of error messages
968 * in stack
969 * x_msg_data To return the error message if
970 * x_msg_count = 1.
971 * NOTES
972 *
973 * MODIFICATION HISTORY
974 *
975 * 05-19-2005 Bhargav Adireddy o Created.
976 *
977 */
978
979 PROCEDURE REPLICATE_INTEREST_SCHEDULES
980 ( p_ce_bank_account_id IN XTR_BANK_ACCOUNTS.ce_bank_account_id%TYPE,--Chnage this to bank_account_id
981 p_interest_rounding IN XTR_BANK_ACCOUNTS.ROUNDING_TYPE%TYPE,
982 p_interest_includes IN XTR_BANK_ACCOUNTS.DAY_COUNT_TYPE%TYPE,
983 p_interest_calculation_basis IN XTR_BANK_ACCOUNTS.INTEREST_CALCULATION_BASIS%TYPE,
984 p_day_count_basis IN XTR_BANK_ACCOUNTS.YEAR_CALC_TYPE%TYPE,
985 x_return_status OUT NOCOPY VARCHAR2,
986 x_msg_count OUT NOCOPY NUMBER,
987 x_msg_data OUT NOCOPY VARCHAR2
988 )
989
990 IS
991
992 CURSOR c_chk_lock_interest IS
993 SELECT ce_bank_account_id
994 FROM XTR_BANK_ACCOUNTS
995 WHERE ce_bank_account_id = p_ce_bank_account_id
996 FOR UPDATE NOWAIT;
997 l_ce_bank_account_id XTR_BANK_ACCOUNTS.ce_bank_account_id%TYPE;--change this
998
999 BEGIN
1000 x_msg_count := NULL;
1001 FND_MSG_PUB.Initialize; -- Initializes the message list that stores the errors
1002
1003 x_return_status := FND_API.G_RET_STS_SUCCESS;
1004 -- Verifies if the ce_bank_account_id in XTR_BANK_ACCOUNTS is passed as null
1005 IF(p_ce_bank_account_id is null) THEN
1006 x_return_status := FND_API.G_RET_STS_ERROR;
1007 LOG_ERR_MSG('XTR_INV_PARAM','XTR_BANK_ACCOUNTS.ce_bank_account_id');
1008 END IF;
1009 -- Verifies if year calculated basis is '30/' and a day count basis type of Both Days for the same deal.
1010 IF(substr(p_day_count_basis,1,2) = '30' and p_interest_includes = 'B') THEN
1011 x_return_status := FND_API.G_RET_STS_ERROR;
1012 LOG_ERR_MSG('XTR_CHK_30_BOTH');
1013 END IF;
1014 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1015 OPEN c_chk_lock_interest;
1016 FETCH c_chk_lock_interest INTO l_ce_bank_account_id;
1017 IF c_chk_lock_interest%FOUND THEN
1018 CLOSE c_chk_lock_interest;
1019 UPDATE XTR_BANK_ACCOUNTS
1020 SET rounding_type = p_interest_rounding
1021 ,day_count_type = p_interest_includes
1022 ,year_calc_type = p_day_count_basis,
1023 interest_calculation_basis = p_interest_calculation_basis
1024 WHERE ce_bank_account_id = l_ce_bank_account_id; -- change this
1025 -- Calling the Bank Balances API to replicate
1026 -- the interest includes and rounding in XTR_BANK_BALANCES
1027 IF(p_interest_rounding is not null and p_interest_includes is not null) THEN
1028 XTR_REPLICATE_BANK_BALANCES.UPDATE_ROUNDING_DAYCOUNT
1029 (p_ce_bank_account_id ,p_interest_rounding,p_interest_includes
1030 ,x_return_status);
1031 END IF;
1032 ELSE
1033
1034 CLOSE c_chk_lock_interest;
1035 x_return_status := FND_API.G_RET_STS_ERROR;
1036 LOG_ERR_MSG('XTR_INV_PARAM','XTR_BANK_ACCOUNTS.ce_bank_account_id');
1037
1038 END IF;
1039
1040 END IF;
1041 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
1042 ( p_count => x_msg_count ,
1043 p_data => x_msg_data
1044 );
1045 EXCEPTION
1046 When app_exceptions.RECORD_LOCK_EXCEPTION then -- If the record is locked
1047 if C_CHK_LOCK_INTEREST%ISOPEN then
1048 close c_CHK_LOCK_INTEREST;
1049 end if;
1050 LOG_ERR_MSG('CHK_LOCK');
1051 x_return_status := FND_API.G_RET_STS_ERROR;
1052 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
1053 ( p_count => x_msg_count ,
1054 p_data => x_msg_data
1055 );
1056 --app_exceptions.RECORD_LOCK_EXCEPTION;
1057 WHEN others THEN
1058 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1059 LOG_ERR_MSG('XTR_UNEXP_ERROR',SQLERRM(SQLCODE));
1060 FND_MSG_PUB.Count_And_Get -- Returns the error message if there is only 1 error
1061 ( p_count => x_msg_count ,
1062 p_data => x_msg_data
1063 );
1064
1065
1066 END REPLICATE_INTEREST_SCHEDULES;
1067
1068
1069 END XTR_REPLICATE_BANK_ACCOUNTS_P;
1070
[Home] [Help]