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