[Home] [Help]
PACKAGE BODY: APPS.LNS_PARTICIPANTS_PUB
Source
1 PACKAGE BODY LNS_PARTICIPANTS_PUB AS
2 /* $Header: LNS_PART_PUBP_B.pls 120.19.12010000.1 2008/07/29 09:12:19 appldev ship $ */
3 G_DEBUG_COUNT CONSTANT NUMBER := 0;
4 G_DEBUG CONSTANT BOOLEAN := FALSE;
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_PARTICIPANTS_PUB';
6
7 --------------------------------------------------
8 -- Procedure for logging debug messages
9 --------------------------------------------------
10 procedure logMessage(log_level in number
11 ,module in varchar2
12 ,message in varchar2)
13 is
14
15 begin
16
17 IF log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
18 FND_LOG.STRING(log_level, module, message);
19 END IF;
20
21 end;
22
23 --------------------------------------------------
24 -- declaration of private procedures and functions
25 --------------------------------------------------
26
27 Procedure createCreditRequest(p_loan_participant_rec IN OUT NOCOPY loan_participant_rec_type,
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_msg_count OUT NOCOPY NUMBER,
30 x_msg_data OUT NOCOPY VARCHAR2);
31
32 Procedure denormalizePrimaryInfo(p_loan_participant_rec IN loan_participant_rec_type,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2);
36
37 PROCEDURE do_create_participant (
38 p_loan_participant_rec IN OUT NOCOPY loan_participant_rec_type,
39 x_participant_id OUT NOCOPY NUMBER,
40 x_return_status IN OUT NOCOPY VARCHAR2
41 );
42
43 PROCEDURE getDefaultPrimaryContact(p_loan_participant_rec IN OUT NOCOPY loan_participant_rec_type,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2
47 );
48
49 --------------------------------------------------
50 -- end declaration of private procedures and functions
51 --------------------------------------------------
52
53 procedure validateParticipant(p_loan_participant_rec IN loan_participant_rec_type,
54 p_mode IN VARCHAR2,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2)
58 is
59
60 l_participant_id NUMBER;
61 l_LOAN_ID NUMBER;
62 l_hz_party_id NUMBER;
63 l_loan_participant_type VARCHAR2(30);
64 l_cust_account_id NUMBER;
65 l_start_date DATE;
66 l_end_date DATE;
67
68 l_msg_count NUMBER;
69 l_msg_data VARCHAR2(2000);
70 l_return_Status VARCHAR2(1);
71
72 l_primary varchar2(30);
73 l_api_name CONSTANT varchar2(25) := 'validateParticipant';
74 l_valid_acct_site_party VARCHAR2(1);
75
76 -- validation for bill_to_site_use
77 CURSOR c_site_uses(p_primary_borrower_id NUMBER, p_cust_account_id NUMBER, p_bill_to_acct_site_id number) IS
78 select 'Y' valid_acct_site_party
79 from
80 hz_parties party,
81 hz_party_sites site,
82 hz_cust_accounts acct,
83 hz_cust_acct_sites acct_site,
84 hz_cust_site_uses acc_site_use
85 where party.party_id = p_primary_borrower_id
86 and acct.party_id = party.party_id
87 and acct.cust_account_id = p_cust_account_id
88 and acct_site.cust_account_id = acct.cust_account_id
89 and acct_site.cust_acct_site_id = p_bill_to_acct_site_id
90 and acc_site_use.cust_acct_site_id = acct_site.cust_acct_site_id
91 and acct_site.party_site_id = site.party_site_id
92 and acc_site_use.site_use_code = 'BILL_TO'
93 and acc_site_use.status = 'A'
94 and site.status = 'A'
95 and acct.status = 'A'
96 and party.status = 'A';
97 --and sysdate between nvl(site.start_date_active,sysdate) and nvl(site.end_date_active,sysdate);
98
99 l_last_api_called varchar2(500); --Store the last api that was called before exception
100
101 BEGIN
102
103 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
104 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_mode: ' || p_mode);
105
106
107 l_last_api_called := 'validateParticipant';
108 l_participant_id := p_loan_participant_rec.participant_id;
109 l_LOAN_ID := p_loan_participant_rec.loan_id;
110 l_hz_party_id := p_loan_participant_rec.hz_party_id;
111 l_loan_participant_type := p_loan_participant_rec.LOAN_PARTICIPANT_TYPE;
112 l_cust_account_id := p_loan_participant_rec.cust_account_id;
113 l_start_date := p_loan_participant_rec.start_date_active;
114 l_end_date := p_loan_participant_rec.end_date_active;
115
116 --SAVEPOINT validateParticipant;
117
118 --dbms_output.put_line('validate');
119 -- Initialize API return status to SUCCESS
120 x_return_status := FND_API.G_RET_STS_SUCCESS;
121
122 IF (p_mode = 'CREATE') THEN
123
124 -- validate loan_id
125 IF l_LOAN_ID IS NOT NULL AND
126 l_LOAN_ID <> FND_API.G_MISS_NUM
127 THEN
128
129 -- check to see if primary_borrower. if so then CUST_ACCOUNT_ID,bill_to_acct_site_id and PARTY_ID MUST
130 -- be valid combination with valid BILL_TO purpose
131 if (l_loan_participant_type = 'PRIMARY_BORROWER' AND p_mode = 'CREATE') then
132
133 BEGIN
134 OPEN c_site_uses(p_loan_participant_rec.hz_party_id, p_loan_participant_rec.cust_account_id, p_loan_participant_rec.bill_to_acct_site_id);
135 FETCH c_site_uses INTO l_valid_acct_site_party;
136 CLOSE c_site_uses;
137
138 --dbms_output.put_line('Site use number is ' || l_num_acct_site_uses);
139 if l_valid_acct_site_party <> 'Y' then
140 FND_MESSAGE.SET_NAME('LNS', 'LNS_BORROWER_NO_BILL_TO');
141 FND_MSG_PUB.ADD;
142 RAISE FND_API.G_EXC_ERROR;
143 end if;
144 exception
145 when NO_DATA_FOUND then
146 FND_MESSAGE.SET_NAME('LNS', 'LNS_BORROWER_NO_BILL_TO');
147 FND_MSG_PUB.ADD;
148 RAISE FND_API.G_EXC_ERROR;
149 End;
150
151 end if; --end if (l_loan_participant_type = 'PRIMARY_BORROWER') then
152
153 LNS_UTILITY_PUB.VALIDATE_ANY_ID(P_API_VERSION => 1.0,
154 P_INIT_MSG_LIST => 'F',
155 X_MSG_COUNT => l_msg_count,
156 X_MSG_DATA => l_msg_data,
157 X_RETURN_STATUS => l_return_status,
158 P_COL_ID => l_loan_id,
159 P_COL_NAME => 'LOAN_ID',
160 P_TABLE_NAME => 'LNS_LOAN_HEADERS_ALL');
161 --dbms_output.put_line('output');
162
163 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
164 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
165 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
166 FND_MESSAGE.SET_TOKEN('VALUE', l_loan_id);
167 FND_MSG_PUB.ADD;
168 RAISE FND_API.G_EXC_ERROR;
169 end if;
170
171 END IF;
172
173 -- validate hz_party_id
174 IF l_hz_party_id IS NOT NULL AND
175 l_hz_party_id <> FND_API.G_MISS_NUM
176 THEN
177
178 LNS_UTILITY_PUB.VALIDATE_ANY_ID(P_API_VERSION => 1.0,
179 P_INIT_MSG_LIST => 'F',
180 X_MSG_COUNT => l_msg_count,
181 X_MSG_DATA => l_msg_data,
182 X_RETURN_STATUS => l_return_status,
183 P_COL_ID => l_hz_party_id,
184 P_COL_NAME => 'PARTY_ID',
185 P_TABLE_NAME => 'HZ_PARTIES');
186 --dbms_output.put_line('output2');
187
188 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
189 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
190 FND_MESSAGE.SET_TOKEN('PARAMETER', 'HZ_PARTY_ID');
191 FND_MESSAGE.SET_TOKEN('VALUE', l_hz_party_id);
192 FND_MSG_PUB.ADD;
193 RAISE FND_API.G_EXC_ERROR;
194 end if;
195
196 END IF;
197
198
199 -- validate l_loan_participant_type
200 IF l_loan_participant_type IS NOT NULL AND
201 l_loan_participant_type <> FND_API.G_MISS_CHAR
202 THEN
203 LNS_UTILITY_PUB.VALIDATE_LOOKUP_CODE(P_API_VERSION => 1.0,
204 P_INIT_MSG_LIST => 'F',
205 X_MSG_COUNT => l_msg_count,
206 X_MSG_DATA => l_msg_data,
207 X_RETURN_STATUS => l_return_status,
208 P_LOOKUP_TYPE => 'LNS_PARTICIPANT_TYPE',
209 P_LOOKUP_CODE => l_loan_participant_type,
210 P_LOOKUP_VIEW => 'LNS_LOOKUPS');
211 --dbms_output.put_line('output3');
212
213 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
214 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
215 FND_MESSAGE.SET_TOKEN('PARAMETER', 'PARTICIPANT_TYPE');
216 FND_MESSAGE.SET_TOKEN('VALUE', l_loan_participant_type);
217 FND_MSG_PUB.ADD;
218 RAISE FND_API.G_EXC_ERROR;
219 end if;
220
221 END IF;
222
223 IF l_cust_account_id IS NOT NULL AND
224 l_cust_account_id <> FND_API.G_MISS_NUM
225 THEN
226
227 LNS_UTILITY_PUB.VALIDATE_ANY_ID(P_API_VERSION => 1.0,
228 P_INIT_MSG_LIST => 'F',
229 X_MSG_COUNT => l_msg_count,
230 X_MSG_DATA => l_msg_data,
231 X_RETURN_STATUS => l_return_status,
232 P_COL_ID => l_cust_account_id,
233 P_COL_NAME => 'CUST_ACCOUNT_ID',
234 P_TABLE_NAME => 'HZ_CUST_ACCOUNTS');
235 --dbms_output.put_line('output5');
236
237 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
238 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
239 FND_MESSAGE.SET_TOKEN('PARAMETER', 'CUST_ACCOUNT_ID');
240 FND_MESSAGE.SET_TOKEN('VALUE', l_cust_account_id);
241 FND_MSG_PUB.ADD;
242 RAISE FND_API.G_EXC_ERROR;
243 end if;
244
245 END IF;
246
247 END IF; -- end IF (p_mode = 'CREATE') THEN
248
249 if l_start_date is not null then
250 if l_end_date is not null then
251 if trunc(l_start_date) > trunc(l_end_date) then
252 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_ACTIVE_DATE');
253 FND_MSG_PUB.ADD;
254 RAISE FND_API.G_EXC_ERROR;
255 end if;
256 end if;
257 end if;
258
259
260 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
261 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'create participant validation passed OK');
262 END IF;
263
264 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
265
266 EXCEPTION
267 WHEN FND_API.G_EXC_ERROR THEN
268 --ROLLBACK TO validateParticipant;
269 x_return_status := FND_API.G_RET_STS_ERROR;
270 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
271
272 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
273 --ROLLBACK TO validateParticipant;
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
275 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
276
277 WHEN OTHERS THEN
278 --ROLLBACK TO Validate_any_id_PVT;
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
280 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
281
282 END validateParticipant;
283
284 PROCEDURE do_create_participant(p_loan_participant_rec IN OUT NOCOPY loan_participant_rec_type,
285 x_participant_id OUT NOCOPY NUMBER,
286 x_return_status IN OUT NOCOPY VARCHAR2)
287 IS
288 l_dummy VARCHAR2(1);
289 l_msg_count NUMBER;
290 l_msg_data VARCHAR2(2000);
291 l_return_Status VARCHAR2(1);
292 l_api_name CONSTANT VARCHAR2(30) := 'do_create_participant';
293 BEGIN
294 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
295
296 -- initialize API return status to success.
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298
299
300 lns_participants_pkg.INSERT_ROW(
301 x_PARTICIPANT_ID => p_loan_participant_rec.PARTICIPANT_ID
302 ,P_LOAN_ID => p_loan_participant_rec.LOAN_ID
303 ,P_HZ_PARTY_ID => p_loan_participant_rec.HZ_PARTY_ID
304 ,P_LOAN_PARTICIPANT_TYPE=> p_loan_participant_rec.LOAN_PARTICIPANT_TYPE
305 ,P_START_DATE_ACTIVE => p_loan_participant_rec.START_DATE_ACTIVE
306 ,P_END_DATE_ACTIVE => p_loan_participant_rec.END_DATE_ACTIVE
307 ,P_CUST_ACCOUNT_ID => p_loan_participant_rec.CUST_ACCOUNT_ID
308 ,P_BILL_TO_ACCT_SITE_ID => p_loan_participant_rec.BILL_TO_ACCT_SITE_ID
309 ,P_OBJECT_VERSION_NUMBER => p_loan_participant_rec.OBJECT_VERSION_NUMBER
310 ,P_ATTRIBUTE_CATEGORY => p_loan_participant_rec.ATTRIBUTE_CATEGORY
311 ,P_ATTRIBUTE1 => p_loan_participant_rec.ATTRIBUTE1
312 ,P_ATTRIBUTE2 => p_loan_participant_rec.ATTRIBUTE2
313 ,P_ATTRIBUTE3 => p_loan_participant_rec.ATTRIBUTE3
314 ,P_ATTRIBUTE4 => p_loan_participant_rec.ATTRIBUTE4
315 ,P_ATTRIBUTE5 => p_loan_participant_rec.ATTRIBUTE5
316 ,P_ATTRIBUTE6 => p_loan_participant_rec.ATTRIBUTE6
317 ,P_ATTRIBUTE7 => p_loan_participant_rec.ATTRIBUTE7
318 ,P_ATTRIBUTE8 => p_loan_participant_rec.ATTRIBUTE8
319 ,P_ATTRIBUTE9 => p_loan_participant_rec.ATTRIBUTE9
320 ,P_ATTRIBUTE10 => p_loan_participant_rec.ATTRIBUTE10
321 ,P_ATTRIBUTE11 => p_loan_participant_rec.ATTRIBUTE11
322 ,P_ATTRIBUTE12 => p_loan_participant_rec.ATTRIBUTE12
323 ,P_ATTRIBUTE13 => p_loan_participant_rec.ATTRIBUTE13
324 ,P_ATTRIBUTE14 => p_loan_participant_rec.ATTRIBUTE14
325 ,P_ATTRIBUTE15 => p_loan_participant_rec.ATTRIBUTE15
326 ,P_ATTRIBUTE16 => p_loan_participant_rec.ATTRIBUTE16
327 ,P_ATTRIBUTE17 => p_loan_participant_rec.ATTRIBUTE17
328 ,P_ATTRIBUTE18 => p_loan_participant_rec.ATTRIBUTE18
329 ,P_ATTRIBUTE19 => p_loan_participant_rec.ATTRIBUTE19
330 ,P_ATTRIBUTE20 => p_loan_participant_rec.ATTRIBUTE20
331 ,P_CONTACT_REL_PARTY_ID => p_loan_participant_rec.CONTACT_REL_PARTY_ID
332 ,P_CONTACT_PERS_PARTY_ID => p_loan_participant_rec.CONTACT_PERS_PARTY_ID
333 ,P_CREDIT_REQUEST_ID => p_loan_participant_rec.CREDIT_REQUEST_ID
334 ,P_CASE_FOLDER_ID => p_loan_participant_rec.CASE_FOLDER_ID
335 ,p_REVIEW_TYPE => p_loan_participant_rec.REVIEW_TYPE
336 ,p_CREDIT_CLASSIFICATION => p_loan_participant_rec.CREDIT_CLASSIFICATION
337 );
338 --dbms_output.put_line('after tblH');
339 x_participant_id := p_loan_participant_rec.PARTICIPANT_ID;
340
341 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
342 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_participant procedure: After call to lns_participants_pkg.Insert_Row');
343 END IF;
344 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
345
346 END do_create_participant;
347
348
349 PROCEDURE createParticipant(p_init_msg_list IN VARCHAR2,
350 p_validation_level IN NUMBER,
351 p_loan_participant_rec IN loan_participant_rec_type,
352 x_participant_id OUT NOCOPY NUMBER,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_count OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2)
356 IS
357 l_init_msg_list VARCHAR2(1);
358 l_dummy VARCHAR2(1);
359 l_msg_count NUMBER;
360 l_msg_data VARCHAR2(2000);
361 l_return_Status VARCHAR2(1);
362 l_loan_participant_rec loan_participant_rec_type;
363
364 l_participant_id NUMBER;
365 l_loan_id NUMBER;
366 l_hz_party_id NUMBER;
367 l_dup_party_count NUMBER;
368
369 l_party_type varchar2(30);
370 l_loan_party_type varchar2(30);
371 l_loan_credit_review_flag varchar2(1);
372 l_borrower_review_type varchar2(30);
373 l_guarantor_review_type varchar2(30);
374 l_loan_status varchar2(30);
375 l_loan_sec_status varchar2(30);
376
377 CURSOR CheckDupParty(p_loan_id number, p_party_id number) IS
378 select count(1) from lns_participants
379 where loan_id = p_loan_id and
380 hz_party_id = p_party_id;
381
382 CURSOR c_get_credit_info(p_loan_id number) IS
383 select loan.party_type, loan.credit_review_flag, prod.credit_review_type,
384 prod.guarantor_review_type, loan.loan_status, loan.secondary_status
385 from lns_loan_products_all prod, lns_loan_headers_all loan
386 where loan.loan_id = p_loan_id
387 and prod.loan_product_id = loan.product_id;
388
389 CURSOR c_get_party_type(p_party_id number) IS
390 select party_type
391 from hz_parties
392 where party_id = p_party_id;
393
394 l_last_api_called varchar2(500); --Store the last api that was called before exception
395 l_api_name CONSTANT VARCHAR2(30) := 'createParticipant';
396
397
398 BEGIN
399
400 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
401
402 l_init_msg_list := p_init_msg_list;
403 if (l_init_msg_list is null) then
404 l_init_msg_list := FND_API.G_FALSE;
405 end if;
406
407 l_last_api_called := 'LNS_PARTICIPANTS_PUB.createParticipant';
408 l_participant_id := p_loan_participant_rec.participant_id;
409 l_loan_id := p_loan_participant_rec.loan_id;
410 l_hz_party_id := p_loan_participant_rec.hz_party_id;
411 l_loan_participant_rec := p_loan_participant_rec;
412
413 SAVEPOINT createParticipant;
414
415 -- initialize message list if l_init_msg_list is set to TRUE.
416 IF FND_API.to_Boolean(l_init_msg_list) THEN
417 FND_MSG_PUB.initialize;
418 END IF;
419 -- Get the default values of ContactPerson PartyId and ContactRel PartyId
420 getDefaultPrimaryContact(p_loan_participant_rec => l_loan_participant_rec,
421 x_return_status => l_return_status,
422 x_msg_count => l_msg_count,
423 x_msg_data => l_msg_data
424 );
425
426
427 open c_get_credit_info(l_loan_id);
428 fetch c_get_credit_info into l_loan_party_type,l_loan_credit_review_flag,l_borrower_review_type,l_guarantor_review_type,l_loan_status,l_loan_sec_status;
429 close c_get_credit_info;
430
431 IF (l_loan_participant_rec.LOAN_PARTICIPANT_TYPE = 'GUARANTOR') THEN
432 l_loan_participant_rec.REVIEW_TYPE := nvl(l_guarantor_review_type,'LNS');
433 ELSE
434 l_loan_participant_rec.REVIEW_TYPE := nvl(l_borrower_review_type,'LNS');
435 END IF;
436
437 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN
438
439 open c_get_party_type(l_hz_party_id);
440 fetch c_get_party_type into l_party_type;
441 close c_get_party_type;
442
443 if (l_party_type <> l_loan_party_type OR l_party_type NOT IN ('ORGANIZATION','PERSON')) then
444 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_PARTY_TYPE');
445 FND_MSG_PUB.ADD;
446 RAISE FND_API.G_EXC_ERROR;
447 end if;
448
449 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
450 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In createParticipant: Before call to validateParticipant');
451 END IF;
452 l_last_api_called := 'LNS_PARTICIPANTS_PUB.validateParticipant';
453 validateParticipant(p_loan_participant_rec => p_loan_participant_rec,
454 p_mode => 'CREATE',
455 x_return_status => l_return_status,
456 x_msg_count => l_msg_count,
457 x_msg_data => l_msg_data);
458 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
459 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In createParticipant: After call to validateParticipant');
460 END IF;
461
462 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
464 end if;
465
466 END IF; --end IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN
467
468 --validate duplicate entry for same party
469 l_dup_party_count := 0;
470 OPEN CheckDupParty(l_loan_id,l_hz_party_id);
471 FETCH CheckDupParty INTO l_dup_party_count;
472 IF (l_dup_party_count > 0) THEN
473 FND_MESSAGE.SET_NAME('LNS', 'LNS_PARTICIPANT_DUP_PARTY');
474 FND_MSG_PUB.ADD;
475 RAISE FND_API.G_EXC_ERROR;
476 END IF;
477
478 if (l_loan_credit_review_flag = 'Y' and l_loan_status = 'INCOMPLETE' and (l_loan_sec_status is null OR l_loan_sec_status NOT IN ('IN_CREDIT_REVIEW','CREDIT_REVIEW_COMPLETE'))) then
479 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling createCreditRequest');
480 l_last_api_called := 'LNS_PARTICIPANTS_PUB.createCreditRequest';
481 createCreditRequest(p_loan_participant_rec => l_loan_participant_rec,
482 x_return_status => l_return_status,
483 x_msg_count => l_msg_count,
484 x_msg_data => l_msg_data);
485 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
487 end if;
488 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After calling createCreditRequest');
489
490 else
491 --default the credit_request_id to -1 for the financial data component
492 l_loan_participant_rec.credit_request_id := -1;
493 end if; -- end if (l_loan_credit_review_flag = 'Y' and l_loan_status = 'INCOMPLETE' and (l_loan_sec_status is null OR l_loan_sec_status NOT IN ('IN_CREDIT_REVIEW','CREDIT_REVIEW_COMPLETE'))) then
494
495 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
496 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_create_participant procedure');
497 END IF;
498
499 -- call to business logic.
500 l_last_api_called := 'LNS_PARTICIPANTS_PUB.do_create_participant';
501 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling do_create_participant');
502 do_create_participant(
503 l_loan_participant_rec,
504 x_participant_id,
505 x_return_status
506 );
507
508 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After call do_create_participant');
509
510 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling denormalizePrimaryInfo');
511 denormalizePrimaryInfo(p_loan_participant_rec => l_loan_participant_rec,
512 x_return_status => l_return_status,
513 x_msg_count => l_msg_count,
514 x_msg_data => l_msg_data);
515 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After calling denormalizePrimaryInfo');
516
517 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
518
519 Exception
520 WHEN FND_API.G_EXC_ERROR THEN
521 x_return_status := FND_API.G_RET_STS_ERROR;
522 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
523 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
524 FND_MSG_PUB.ADD;
525 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
526 ROLLBACK TO createParticipant;
527
528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
529 x_return_status := FND_API.G_RET_STS_ERROR;
530 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
531 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
532 FND_MSG_PUB.ADD;
533 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
534 ROLLBACK TO createParticipant;
535
536 WHEN OTHERS THEN
537 x_return_status := FND_API.G_RET_STS_ERROR;
538 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
539 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
540 FND_MSG_PUB.ADD;
541 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
542 ROLLBACK TO createParticipant;
543
544 END createParticipant;
545
546 PROCEDURE updateParticipant(p_init_msg_list IN VARCHAR2,
547 p_validation_level IN NUMBER,
548 p_loan_participant_rec IN loan_participant_rec_type,
549 x_object_version_number IN OUT NOCOPY NUMBER,
550 x_return_status OUT NOCOPY VARCHAR2,
551 x_msg_count OUT NOCOPY NUMBER,
552 x_msg_data OUT NOCOPY VARCHAR2)
553 is
554 l_init_msg_list VARCHAR2(1);
555 l_rowid ROWID;
556 l_msg_count NUMBER;
557 l_msg_data VARCHAR2(2000);
558 l_return_Status VARCHAR2(1);
559 l_object_version NUMBER;
560 l_loan_participant_rec loan_participant_rec_type;
561 l_last_api_called varchar2(500); --Store the last api that was called before exception
562 l_api_name CONSTANT VARCHAR2(30) := 'updateParticipant';
563 BEGIN
564 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
565
566 l_init_msg_list := p_init_msg_list;
567 if (l_init_msg_list is null) then
568 l_init_msg_list := FND_API.G_FALSE;
569 end if;
570
571 l_last_api_called := 'LNS_PARTICIPANTS_PUB.updateParticipant';
572 l_loan_participant_rec := p_loan_participant_rec;
573 savepoint updateParticipant;
574
575 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
576 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin update participant');
577 END IF;
578
579 -- initialize message list if l_init_msg_list is set to TRUE.
580 IF FND_API.to_Boolean(l_init_msg_list) THEN
581 FND_MSG_PUB.initialize;
582 END IF;
583
584
585 -- Get the default values of ContactPerson PartyId and ContactRel PartyId
586 getDefaultPrimaryContact(p_loan_participant_rec => l_loan_participant_rec,
587 x_return_status => l_return_status,
588 x_msg_count => l_msg_count,
589 x_msg_data => l_msg_data
590 );
591
592
593
594 -- check whether record has been updated by another user. If not, lock it.
595 BEGIN
596 SELECT OBJECT_VERSION_NUMBER,
597 ROWID
598 INTO l_object_version,
599 l_rowid
600 FROM LNS_PARTICIPANTS
601 WHERE PARTICIPANT_ID = p_loan_participant_rec.participant_id
602 FOR UPDATE OF PARTICIPANT_ID NOWAIT;
603
604 IF NOT
605 (
606 (x_object_version_number IS NULL AND l_object_version IS NULL)
607 OR
608 (x_object_version_number IS NOT NULL AND
609 l_object_version IS NOT NULL AND
610 x_object_version_number = l_object_version
611 )
612 )
613 THEN
614 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_RECORD_CHANGED');
615 FND_MESSAGE.SET_TOKEN('TABLE', 'lns_participants');
616 FND_MSG_PUB.ADD;
617 RAISE FND_API.G_EXC_ERROR;
618 END IF;
619
620 x_object_version_number := nvl(x_object_version_number, 1) + 1;
621
622 EXCEPTION WHEN NO_DATA_FOUND THEN
623 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_NO_RECORD');
624 FND_MESSAGE.SET_TOKEN('RECORD', 'loan participant');
625 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_loan_participant_rec.participant_id), 'null'));
626 FND_MSG_PUB.ADD;
627 RAISE FND_API.G_EXC_ERROR;
628 END;
629
630 if (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
631
632 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'Before calling validateParticipant');
633 l_last_api_called := 'validateParticipant';
634 validateParticipant(p_loan_participant_rec => p_loan_participant_rec,
635 p_mode => 'UPDATE',
636 x_return_status => l_return_status,
637 x_msg_count => l_msg_count,
638 x_msg_data => l_msg_data);
639
640 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
642 end if;
643 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After calling validateParticipant');
644
645 end if; -- end if (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
646
647
648 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'Before calling LNS_PARTICIPANTS_PKG.Update_Row');
649 l_last_api_called := 'LNS_PARTICIPANTS_PKG.Update_Row';
650 LNS_PARTICIPANTS_PKG.Update_Row(x_rowid => l_rowid
651 ,p_PARTICIPANT_ID => l_loan_participant_rec.PARTICIPANT_ID
652 ,p_LOAN_ID => l_loan_participant_rec.LOAN_ID
653 ,p_HZ_PARTY_ID => l_loan_participant_rec.HZ_PARTY_ID
654 ,p_LOAN_PARTICIPANT_TYPE => l_loan_participant_rec.LOAN_PARTICIPANT_TYPE
655 ,p_START_DATE_ACTIVE => l_loan_participant_rec.START_DATE_ACTIVE
656 ,p_END_DATE_ACTIVE => l_loan_participant_rec.END_DATE_ACTIVE
657 ,p_CUST_ACCOUNT_ID => l_loan_participant_rec.CUST_ACCOUNT_ID
658 ,p_BILL_TO_ACCT_SITE_ID => l_loan_participant_rec.BILL_TO_ACCT_SITE_ID
659 ,p_OBJECT_VERSION_NUMBER => x_object_version_number
660 ,p_ATTRIBUTE_CATEGORY => l_loan_participant_rec.ATTRIBUTE_CATEGORY
661 ,p_ATTRIBUTE1 => l_loan_participant_rec.ATTRIBUTE1
662 ,p_ATTRIBUTE2 => l_loan_participant_rec.ATTRIBUTE2
663 ,p_ATTRIBUTE3 => l_loan_participant_rec.ATTRIBUTE3
664 ,p_ATTRIBUTE4 => l_loan_participant_rec.ATTRIBUTE4
665 ,p_ATTRIBUTE5 => l_loan_participant_rec.ATTRIBUTE5
666 ,p_ATTRIBUTE6 => l_loan_participant_rec.ATTRIBUTE6
667 ,p_ATTRIBUTE7 => l_loan_participant_rec.ATTRIBUTE7
668 ,p_ATTRIBUTE8 => l_loan_participant_rec.ATTRIBUTE8
669 ,p_ATTRIBUTE9 => l_loan_participant_rec.ATTRIBUTE9
670 ,p_ATTRIBUTE10 => l_loan_participant_rec.ATTRIBUTE10
671 ,p_ATTRIBUTE11 => l_loan_participant_rec.ATTRIBUTE11
672 ,p_ATTRIBUTE12 => l_loan_participant_rec.ATTRIBUTE12
673 ,p_ATTRIBUTE13 => l_loan_participant_rec.ATTRIBUTE13
674 ,p_ATTRIBUTE14 => l_loan_participant_rec.ATTRIBUTE14
675 ,p_ATTRIBUTE15 => l_loan_participant_rec.ATTRIBUTE15
676 ,p_ATTRIBUTE16 => l_loan_participant_rec.ATTRIBUTE16
677 ,p_ATTRIBUTE17 => l_loan_participant_rec.ATTRIBUTE17
678 ,p_ATTRIBUTE18 => l_loan_participant_rec.ATTRIBUTE18
679 ,p_ATTRIBUTE19 => l_loan_participant_rec.ATTRIBUTE19
680 ,p_ATTRIBUTE20 => l_loan_participant_rec.ATTRIBUTE20
681 ,p_CONTACT_REL_PARTY_ID => l_loan_participant_rec.CONTACT_REL_PARTY_ID
682 ,p_CONTACT_PERS_PARTY_ID => l_loan_participant_rec.CONTACT_PERS_PARTY_ID
683 ,p_CREDIT_REQUEST_ID => l_loan_participant_rec.CREDIT_REQUEST_ID
684 ,p_CASE_FOLDER_ID => l_loan_participant_rec.CASE_FOLDER_ID
685 ,p_REVIEW_TYPE => l_loan_participant_rec.REVIEW_TYPE
686 ,p_CREDIT_CLASSIFICATION => l_loan_participant_rec.CREDIT_CLASSIFICATION
687 );
688 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'After calling LNS_PARTICIPANTS_PKG.Update_Row');
689
690 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling denormalizePrimaryInfo');
691 denormalizePrimaryInfo(p_loan_participant_rec => l_loan_participant_rec,
692 x_return_status => l_return_status,
693 x_msg_count => l_msg_count,
694 x_msg_data => l_msg_data);
695 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After calling denormalizePrimaryInfo');
696
697 x_return_status := FND_API.G_RET_STS_SUCCESS;
698
699 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
700
701 Exception
702 WHEN FND_API.G_EXC_ERROR THEN
703 x_return_status := FND_API.G_RET_STS_ERROR;
704 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
705 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
706 FND_MSG_PUB.ADD;
707 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
708 ROLLBACK TO updateParticipant;
709
710 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
711 x_return_status := FND_API.G_RET_STS_ERROR;
712 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
713 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
714 FND_MSG_PUB.ADD;
715 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
716 ROLLBACK TO updateParticipant;
717
718 WHEN OTHERS THEN
719 x_return_status := FND_API.G_RET_STS_ERROR;
720 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
721 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
722 FND_MSG_PUB.ADD;
723 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
724 ROLLBACK TO updateParticipant;
725
726 END updateParticipant;
727
728 ----------------------------------------------------------------
729 --This procedure changes resubmit/appeal the credit request that has been created
730 --for the primary borrower after the case folder has been closed and when loan secondary status is CREDIT_REVIEW_COMPLETE
731 --and changes the loan secondary status to null
732 ----------------------------------------------------------------
733 PROCEDURE createAppealCreditRequest(p_loan_id IN NUMBER,
734 x_return_status OUT NOCOPY VARCHAR2,
735 x_msg_count OUT NOCOPY NUMBER,
736 x_msg_data OUT NOCOPY VARCHAR2)
737 IS
738 l_return_status VARCHAR2(1);
739 l_msg_count NUMBER;
740 l_msg_data VARCHAR2(32767);
741 l_loan_participant_rec loan_participant_rec_type;
742 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
743 l_loan_id NUMBER;
744 l_object_version NUMBER;
745 l_old_credit_request_id NUMBER;
746 l_new_credit_request_id NUMBER;
747 l_last_api_called varchar2(500); --Store the last api that was called before exception
748 l_api_name CONSTANT VARCHAR2(30) := 'createAppealCreditRequest';
749
750 CURSOR c_get_primary_borrower(pLoanId Number) IS
751 select
752 participant_id, loan_id, LOAN_PARTICIPANT_TYPE, hz_party_id, credit_request_id,
753 case_folder_id, object_version_number,review_type,credit_classification
754 from lns_participants
755 where loan_id = pLoanId
756 and LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER'
757 and end_date_active is null;
758
759 CURSOR C_GET_LOAN_OVN(pLoanId Number) IS
760 SELECT OBJECT_VERSION_NUMBER
761 FROM
762 LNS_LOAN_HEADERS_ALL
763 WHERE LOAN_ID = pLoanId;
764
765 BEGIN
766 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
767
768 l_last_api_called := 'LNS_PARTICIPANTS_PUB.createAppealCreditRequest';
769 -- Standard Start of API savepoint
770 SAVEPOINT createAppealCreditRequest;
771 x_return_status := FND_API.G_RET_STS_SUCCESS;
772 l_loan_id := p_loan_id;
773
774 open c_get_primary_borrower(l_loan_id);
775 fetch c_get_primary_borrower into l_loan_participant_rec.participant_id, l_loan_participant_rec.loan_id,
776 l_loan_participant_rec.loan_participant_type,
777 l_loan_participant_rec.hz_party_id, l_loan_participant_rec.credit_request_id,
778 l_loan_participant_rec.case_folder_id, l_loan_participant_rec.object_version_number,
779 l_loan_participant_rec.review_type, l_loan_participant_rec.credit_classification;
780 close c_get_primary_borrower;
781
782 l_old_credit_request_id := l_loan_participant_rec.credit_request_id;
783 if (l_old_credit_request_id is null) then
784 return;
785 end if;
786
787 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling ');
788 l_last_api_called := 'LNS_PARTICIPANTS_PUB.createCreditRequest';
789 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling '|| l_last_api_called);
790 createCreditRequest(p_loan_participant_rec => l_loan_participant_rec,
791 x_return_status => l_return_status,
792 x_msg_count => l_msg_count,
793 x_msg_data => l_msg_data);
794
795 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
796 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
797 end if;
798 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After calling '|| l_last_api_called);
799
800 l_new_credit_request_id := l_loan_participant_rec.credit_request_id;
801 if (l_old_credit_request_id = l_new_credit_request_id) then
802 --new credit request has not been submitted!
803 return;
804 end if;
805
806 l_last_api_called := 'LNS_PARTICIPANTS_PUB.updateParticipant';
807 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling '|| l_last_api_called);
808 l_object_version := l_loan_participant_rec.object_version_number;
809 updateParticipant(p_init_msg_list => 'T',
810 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
811 p_loan_participant_rec => l_loan_participant_rec,
812 x_object_version_number => l_object_version,
813 x_return_status => l_return_status,
814 x_msg_count => l_msg_count,
815 x_msg_data => l_msg_data);
816
817 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
818 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
819 end if;
820 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After calling '|| l_last_api_called);
821
822 l_loan_header_rec.loan_id := l_loan_id;
823 l_loan_header_rec.secondary_status := FND_API.G_MISS_CHAR;
824 open C_GET_LOAN_OVN(l_loan_id);
825 fetch C_GET_LOAN_OVN into l_object_version;
826 close C_GET_LOAN_OVN;
827 l_last_api_called := 'lns_loan_header_pub.update_loan';
828 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling '|| l_last_api_called);
829 lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_FALSE
830 ,P_LOAN_HEADER_REC => l_loan_header_rec
831 ,P_OBJECT_VERSION_NUMBER => l_object_version
832 ,X_RETURN_STATUS => x_return_status
833 ,X_MSG_COUNT => x_msg_count
834 ,X_MSG_DATA => x_msg_data);
835
836 x_return_status := l_return_status;
837 x_msg_count := l_msg_count;
838 x_msg_data := l_msg_data;
839 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
841 end if;
842 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After calling '|| l_last_api_called);
843
844 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
845
846
847 EXCEPTION
848 WHEN FND_API.G_EXC_ERROR THEN
849 x_return_status := FND_API.G_RET_STS_ERROR;
850 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
851 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
852 FND_MSG_PUB.ADD;
853 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
854 ROLLBACK TO createAppealCreditRequest;
855
856 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
857 x_return_status := FND_API.G_RET_STS_ERROR;
858 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
859 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
860 FND_MSG_PUB.ADD;
861 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
862 ROLLBACK TO createAppealCreditRequest;
863
864 WHEN OTHERS THEN
865 x_return_status := FND_API.G_RET_STS_ERROR;
866 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
867 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
868 FND_MSG_PUB.ADD;
869 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
870 ROLLBACK TO createAppealCreditRequest;
871
872 END createAppealCreditRequest;
873
874 /*
875 || Overview: If credit_review_flag = 'Y' in LNS_LOAN_HEADERS for this loan
876 || create a new credit request for Primary Borrower and Guarantor
877 || If valid Credit Request already exists, then create a RESUBMISSION credit request for Primary Borrower
878 ||
879 || Parameter: p_loan_participant_rec => participant details
880 */
881 Procedure createCreditRequest(p_loan_participant_rec IN OUT NOCOPY loan_participant_rec_type,
882 x_return_status OUT NOCOPY VARCHAR2,
883 x_msg_count OUT NOCOPY NUMBER,
884 x_msg_data OUT NOCOPY VARCHAR2)
885 IS
886 l_api_name CONSTANT VARCHAR2(30) := 'createCreditRequest';
887 l_participant_type varchar2(30);
888 l_return_status VARCHAR2(1);
889 l_msg_count NUMBER;
890 l_msg_data VARCHAR2(1000);
891 l_loan_id number;
892 l_org_id number;
893 l_credit_review_flag varchar2(1);
894 l_loan_number varchar2(20);
895 l_requested_amount number;
896 l_loan_currency varchar2(30);
897 l_loan_description varchar2(250);
898 l_credit_request_id number;
899 l_parent_credit_request_id number;
900 l_guarantor_application_number varchar2(30);
901 l_credit_request_type varchar2(30); --CREDIT_APP or RESUBMISSION
902 l_last_api_called varchar2(500); --Store the last api that was called before exception
903 l_loan_status varchar2(30);
904 l_loan_sec_status varchar2(30);
905 l_review_type varchar2(30);
906 l_credit_classification varchar2(30);
907
908 CURSOR c_get_loan_info(p_loan_id NUMBER) IS
909 select org_id,credit_review_flag,loan_number,requested_amount,loan_currency,loan_description,loan_status,secondary_status
910 from lns_loan_headers_all
911 where loan_id = p_loan_id;
912
913 BEGIN
914
915 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
916
917 l_last_api_called := 'LNS_PARTICIPANTS_PUB.createCreditRequest';
918 l_participant_type := p_loan_participant_rec.LOAN_PARTICIPANT_TYPE;
919 l_loan_id := p_loan_participant_rec.loan_id;
920 l_credit_request_id := null;
921 l_review_type := p_loan_participant_rec.REVIEW_TYPE;
922 l_credit_classification := p_loan_participant_rec.CREDIT_CLASSIFICATION;
923
924 -- Standard Start of API savepoint
925 SAVEPOINT createCreditRequest;
926 x_return_status := FND_API.G_RET_STS_SUCCESS;
927
928 open c_get_loan_info(l_loan_id);
929 fetch c_get_loan_info into l_org_id,l_credit_review_flag,l_loan_number,l_requested_amount,l_loan_currency,l_loan_description,l_loan_status,l_loan_sec_status;
930 close c_get_loan_info;
931
932 if (l_credit_review_flag <> 'Y') then
933 return;
934 end if;
935
936 if (l_participant_type = 'PRIMARY_BORROWER') then
937
938 if (p_loan_participant_rec.credit_request_id is not null) then
939 l_credit_request_type := 'RESUBMISSION';
940 l_parent_credit_request_id := p_loan_participant_rec.credit_request_id;
941 else
942 l_credit_request_type := 'CREDIT_APP';
943 l_parent_credit_request_id := null;
944 end if;
945
946 if (l_credit_request_type = 'RESUBMISSION' AND (l_loan_status <> 'INCOMPLETE' OR l_loan_sec_status <> 'CREDIT_REVIEW_COMPLETE')) then
947 --cannot create appeal credit request
948 return;
949 end if;
950
951 --need to call credit request api
952 --if (AR_CMGT_CREDIT_REQUEST_API.IS_CREDIT_MANAGEMENT_INSTALLED = true) then
953
954 l_last_api_called := 'AR_CMGT_CREDIT_REQUEST_API.CREATE_CREDIT_REQUEST';
955 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling '|| l_last_api_called);
956 AR_CMGT_CREDIT_REQUEST_API.CREATE_CREDIT_REQUEST(
957 P_API_VERSION => 1.0
958 ,P_INIT_MSG_LIST => FND_API.G_FALSE
959 ,P_COMMIT => FND_API.G_FALSE
960 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
961 ,X_RETURN_STATUS => l_return_status
962 ,X_MSG_COUNT => l_msg_count
963 ,X_MSG_DATA => l_msg_data
964 ,P_APPLICATION_NUMBER => l_loan_number
965 ,P_APPLICATION_DATE => sysdate
966 ,P_REQUESTOR_TYPE => 'EMPLOYEE'
967 ,P_REQUESTOR_ID => FND_GLOBAL.EMPLOYEE_ID
968 ,P_REVIEW_TYPE => l_review_type
969 ,P_CREDIT_CLASSIFICATION => l_credit_classification
970 ,P_REQUESTED_AMOUNT => l_requested_amount
971 ,P_REQUESTED_CURRENCY => l_loan_currency
972 ,P_TRX_AMOUNT => l_requested_amount
973 ,P_TRX_CURRENCY => l_loan_currency
974 ,P_CREDIT_TYPE => 'TERM'
975 ,P_TERM_LENGTH => null
976 ,P_CREDIT_CHECK_RULE_ID => null
977 ,P_CREDIT_REQUEST_STATUS => 'SAVE'
978 ,P_PARTY_ID => p_loan_participant_rec.hz_party_id
979 ,P_CUST_ACCOUNT_ID => null
980 ,P_CUST_ACCT_SITE_ID => null
981 ,P_SITE_USE_ID => null
982 ,P_CONTACT_PARTY_ID => null
983 ,P_NOTES => null
984 ,P_SOURCE_ORG_ID => l_org_id --MO_GLOBAL.GET_CURRENT_ORG_ID()
985 ,P_SOURCE_USER_ID => LNS_UTILITY_PUB.CREATED_BY
986 ,P_SOURCE_RESP_ID => FND_GLOBAL.RESP_ID
987 ,P_SOURCE_APPLN_ID => 206
988 ,P_SOURCE_SECURITY_GROUP_ID => FND_GLOBAL.SECURITY_GROUP_ID
989 ,P_SOURCE_NAME => 'LNS'
990 ,P_SOURCE_COLUMN1 => l_loan_id
991 ,P_SOURCE_COLUMN2 => l_loan_number
992 ,P_SOURCE_COLUMN3 => l_loan_description
993 ,P_CREDIT_REQUEST_ID => l_credit_request_id
994 ,P_REVIEW_CYCLE => null
995 ,P_CASE_FOLDER_NUMBER => null
996 ,P_SCORE_MODEL_ID => null
997 ,P_PARENT_CREDIT_REQUEST_ID => l_parent_credit_request_id
998 ,P_CREDIT_REQUEST_TYPE => l_credit_request_type
999 );
1000 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After calling '|| l_last_api_called);
1001
1002 x_return_status := l_return_status;
1003 x_msg_count := l_msg_count;
1004 x_msg_data := l_msg_data;
1005 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1006 FND_MESSAGE.Set_Name('LNS', 'LNS_CREDIT_REQUEST_API_FAILED');
1007 FND_MSG_PUB.Add;
1008 RAISE FND_API.G_EXC_ERROR;
1009 else
1010 p_loan_participant_rec.CREDIT_REQUEST_ID := l_credit_request_id;
1011 if (l_credit_request_type = 'RESUBMISSION') then
1012 --set case_folder_id to null
1013 p_loan_participant_rec.case_folder_id := FND_API.G_MISS_NUM;
1014 end if;
1015 end if;
1016
1017 elsif (l_participant_type = 'COBORROWER') then
1018
1019 if (p_loan_participant_rec.credit_request_id is null) then
1020
1021 --update with parent credit request id
1022 select credit_request_id into l_parent_credit_request_id
1023 from lns_participants
1024 where loan_id = l_loan_id
1025 and loan_participant_type = 'PRIMARY_BORROWER'
1026 and end_date_active is null;
1027
1028 if (l_parent_credit_request_id is not null) then
1029 p_loan_participant_rec.CREDIT_REQUEST_ID := l_parent_credit_request_id;
1030 end if;
1031
1032 end if;
1033
1034 elsif (l_participant_type = 'GUARANTOR') then
1035 if (p_loan_participant_rec.credit_request_id is null) then
1036
1037 --need to pass parent credit request id
1038 select credit_request_id into l_parent_credit_request_id
1039 from lns_participants
1040 where loan_id = l_loan_id
1041 and loan_participant_type = 'PRIMARY_BORROWER'
1042 and end_date_active is null;
1043
1044 if (l_parent_credit_request_id is not null) then
1045
1046 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1047 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In checkPrimary: Before calling OCM_GUARANTOR_PUB.CREATE_GUARANTOR_CREDITREQUEST api');
1048 END IF;
1049
1050 --Create Credit Request for Guarantor
1051 l_last_api_called := 'OCM_GUARANTOR_PUB.CREATE_GUARANTOR_CREDITREQUEST';
1052 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling '|| l_last_api_called);
1053 l_guarantor_application_number := l_loan_number;
1054 OCM_GUARANTOR_PUB.CREATE_GUARANTOR_CREDITREQUEST(
1055 P_API_VERSION => 1.0
1056 ,P_INIT_MSG_LIST => FND_API.G_FALSE
1057 ,P_COMMIT => FND_API.G_FALSE
1058 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
1059 ,X_RETURN_STATUS => l_return_status
1060 ,X_MSG_COUNT => l_msg_count
1061 ,X_MSG_DATA => l_msg_data
1062 ,X_GUARANTOR_CREDIT_REQUEST_ID => l_credit_request_id
1063 ,X_GUARANTOR_APPLICATION_NUMBER => l_guarantor_application_number
1064 ,P_PARTY_ID => p_loan_participant_rec.hz_party_id
1065 ,P_CONTACT_PARTY_ID => null
1066 ,P_PARENT_CREDIT_REQUEST_ID => l_parent_credit_request_id
1067 ,P_CURRENCY => l_loan_currency
1068 ,P_GUARANTED_AMOUNT => l_requested_amount
1069 ,P_FUNDING_AVAILABLE_FROM => null
1070 ,P_FUNDING_AVAILABLE_TO => null
1071 ,P_CASE_FOLDER_ID => null
1072 ,P_NOTES => null
1073 ,P_CREDIT_CLASSIFICATION => l_credit_classification
1074 ,P_REVIEW_TYPE => l_review_type
1075 ,P_REQUESTOR_ID => FND_GLOBAL.EMPLOYEE_ID
1076 ,P_SOURCE_ORG_ID => l_org_id --MO_GLOBAL.GET_CURRENT_ORG_ID()
1077 ,P_SOURCE_USER_ID => LNS_UTILITY_PUB.CREATED_BY
1078 ,P_SOURCE_RESP_ID => FND_GLOBAL.RESP_ID
1079 ,P_SOURCE_APPLN_ID => 206
1080 ,P_SOURCE_SECURITY_GROUP_ID => FND_GLOBAL.SECURITY_GROUP_ID
1081 ,P_SOURCE_NAME => 'LNS'
1082 ,P_SOURCE_COLUMN1 => l_loan_id
1083 ,P_SOURCE_COLUMN2 => l_loan_number
1084 ,P_SOURCE_COLUMN3 => l_loan_description
1085 ,P_CREDIT_REQUEST_STATUS => 'SAVE'
1086 ,P_REVIEW_CYCLE => null
1087 ,P_CASE_FOLDER_NUMBER => null
1088 ,P_SCORE_MODEL_ID => null
1089 ,P_ASSET_CLASS_CODE => null
1090 ,P_ASSET_TYPE_CODE => null
1091 ,P_DESCRIPTION => null
1092 ,P_QUANTITY => null
1093 ,P_UOM_CODE => null
1094 ,P_REFERENCE_TYPE => null
1095 ,P_APPRAISER => null
1096 ,P_APPRAISER_PHONE => null
1097 ,P_VALUATION => null
1098 ,P_VALUATION_METHOD_CODE => null
1099 ,P_VALUATION_DATE => null
1100 ,P_ACQUISITION_DATE => null
1101 ,P_ASSET_IDENTIFIER => null
1102 );
1103 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After calling '|| l_last_api_called);
1104
1105
1106 x_return_status := l_return_status;
1107 x_msg_count := l_msg_count;
1108 x_msg_data := l_msg_data;
1109 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1110 FND_MESSAGE.Set_Name('LNS', 'LNS_G_CRDT_REQ_API_FAILED');
1111 FND_MSG_PUB.Add;
1112 RAISE FND_API.G_EXC_ERROR;
1113 else
1114 p_loan_participant_rec.CREDIT_REQUEST_ID := l_credit_request_id;
1115 end if;
1116
1117 end if; --if (l_parent_credit_request_id is not null) then
1118
1119 end if; -- if (p_loan_participant_rec.credit_request_id is null) then
1120
1121 end if; --if (l_participant_type = 'PRIMARY_BORROWER') then
1122
1123 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1124
1125 EXCEPTION
1126 WHEN FND_API.G_EXC_ERROR THEN
1127 x_return_status := FND_API.G_RET_STS_ERROR;
1128 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1129 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
1130 FND_MSG_PUB.ADD;
1131 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1132 ROLLBACK TO createCreditRequest;
1133
1134 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1135 x_return_status := FND_API.G_RET_STS_ERROR;
1136 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1137 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
1138 FND_MSG_PUB.ADD;
1139 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1140 ROLLBACK TO createCreditRequest;
1141
1142 WHEN OTHERS THEN
1143 x_return_status := FND_API.G_RET_STS_ERROR;
1144 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1145 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
1146 FND_MSG_PUB.ADD;
1147 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1148 ROLLBACK TO createCreditRequest;
1149
1150 END createCreditRequest;
1151
1152 ----------------------------------------------------------------
1153 --This procedure updates the loan header with denormalized information
1154 --from lns_participants for the primary borrower
1155 ----------------------------------------------------------------
1156 Procedure denormalizePrimaryInfo(p_loan_participant_rec IN loan_participant_rec_type,
1157 x_return_status OUT NOCOPY VARCHAR2,
1158 x_msg_count OUT NOCOPY NUMBER,
1159 x_msg_data OUT NOCOPY VARCHAR2)
1160 IS
1161 l_participant_type varchar2(30);
1162 l_return_status VARCHAR2(1);
1163 l_msg_count NUMBER;
1164 l_msg_data VARCHAR2(32767);
1165 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
1166 l_object_version NUMBER;
1167 l_loan_id number;
1168 l_last_api_called varchar2(500); --Store the last api that was called before exception
1169 l_api_name CONSTANT VARCHAR2(30) := 'denormalizePrimaryInfo';
1170
1171 CURSOR C_GET_LOAN_OVN(pLoanId Number) IS
1172 SELECT OBJECT_VERSION_NUMBER
1173 FROM
1174 LNS_LOAN_HEADERS_ALL
1175 WHERE LOAN_ID = pLoanId;
1176
1177 BEGIN
1178 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1179 l_last_api_called := 'LNS_PARTICIPANTS_PUB.denormalizePrimaryInfo';
1180 l_participant_type := p_loan_participant_rec.LOAN_PARTICIPANT_TYPE;
1181 l_loan_id := p_loan_participant_rec.loan_id;
1182
1183 -- Standard Start of API savepoint
1184 SAVEPOINT denormalizePrimaryInfo;
1185
1186 -- Initialize API return status to SUCCESS
1187 x_return_status := FND_API.G_RET_STS_SUCCESS;
1188
1189 if (l_participant_type = 'PRIMARY_BORROWER') then
1190
1191 l_loan_header_rec.loan_id := p_loan_participant_rec.loan_id;
1192 l_loan_header_rec.cust_account_id := p_loan_participant_rec.cust_account_id;
1193 l_loan_header_rec.BILL_TO_ACCT_SITE_ID := p_loan_participant_rec.BILL_TO_ACCT_SITE_ID;
1194 l_loan_header_rec.primary_borrower_id := p_loan_participant_rec.hz_party_id;
1195 l_loan_header_rec.CONTACT_REL_PARTY_ID := p_loan_participant_rec.CONTACT_REL_PARTY_ID;
1196 l_loan_header_rec.CONTACT_PERS_PARTY_ID := p_loan_participant_rec.CONTACT_PERS_PARTY_ID;
1197 open C_GET_LOAN_OVN(l_loan_id);
1198 fetch C_GET_LOAN_OVN into l_object_version;
1199 close C_GET_LOAN_OVN;
1200 l_last_api_called := 'lns_loan_header_pub.update_loan';
1201 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling '|| l_last_api_called);
1202 lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_FALSE
1203 ,P_LOAN_HEADER_REC => l_loan_header_rec
1204 ,P_OBJECT_VERSION_NUMBER => l_object_version
1205 ,X_RETURN_STATUS => x_return_status
1206 ,X_MSG_COUNT => x_msg_count
1207 ,X_MSG_DATA => x_msg_data);
1208 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After calling '|| l_last_api_called);
1209 x_return_status := l_return_status;
1210 x_msg_count := l_msg_count;
1211 x_msg_data := l_msg_data;
1212 end if; -- end if (l_participant_type = 'PRIMARY_BORROWER') then
1213 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1214 EXCEPTION
1215 WHEN FND_API.G_EXC_ERROR THEN
1216 x_return_status := FND_API.G_RET_STS_ERROR;
1217 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1218 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
1219 FND_MSG_PUB.ADD;
1220 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1221 ROLLBACK TO denormalizePrimaryInfo;
1222
1223 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1224 x_return_status := FND_API.G_RET_STS_ERROR;
1225 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1226 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
1227 FND_MSG_PUB.ADD;
1228 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1229 ROLLBACK TO denormalizePrimaryInfo;
1230
1231 WHEN OTHERS THEN
1232 x_return_status := FND_API.G_RET_STS_ERROR;
1233 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1234 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
1235 FND_MSG_PUB.ADD;
1236 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1237 ROLLBACK TO denormalizePrimaryInfo;
1238 End denormalizePrimaryInfo;
1239
1240 ----------------------------------------------------------------
1241 --This procedure changes all credit requests that have been created
1242 --for the loan participants in SAVE status to SUBMIT status
1243 --and changes the loan secondary status to IN_CREDIT_REVIEW
1244 ----------------------------------------------------------------
1245 PROCEDURE submitCreditRequest(p_loan_id IN NUMBER,
1246 x_return_status OUT NOCOPY VARCHAR2,
1247 x_msg_count OUT NOCOPY NUMBER,
1248 x_msg_data OUT NOCOPY VARCHAR2)
1249 IS
1250 l_api_name CONSTANT VARCHAR2(30) := 'submitCreditRequest';
1251 l_last_api_called varchar2(500); --Store the last api that was called before exception
1252
1253 CURSOR C_GET_PRIMARY_CREQ_ID(pLoanId Number) IS
1254 SELECT CREDIT_REQUEST_ID
1255 FROM
1256 LNS_PARTICIPANTS
1257 WHERE LOAN_ID = pLoanId
1258 AND LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER'
1259 AND END_DATE_ACTIVE IS NULL;
1260
1261 CURSOR C_GET_LOAN_OVN(pLoanId Number) IS
1262 SELECT OBJECT_VERSION_NUMBER
1263 FROM
1264 LNS_LOAN_HEADERS_ALL
1265 WHERE LOAN_ID = pLoanId;
1266
1267 l_primary_credit_request_id number;
1268 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
1269 l_object_version number;
1270
1271 BEGIN
1272 l_last_api_called := 'submitCreditRequest';
1273 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1274
1275 x_return_status := FND_API.G_RET_STS_ERROR;
1276
1277 open C_GET_PRIMARY_CREQ_ID(p_loan_id);
1278 fetch C_GET_PRIMARY_CREQ_ID into l_primary_credit_request_id;
1279 close C_GET_PRIMARY_CREQ_ID;
1280
1281 if (l_primary_credit_request_id is not null) then
1282
1283 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1284 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In submitCreditRequest: Before calling OCM_CREDIT_REQUEST_UPDATE_PUB.update_credit_request_status api');
1285 END IF;
1286 l_last_api_called := 'OCM_CREDIT_REQUEST_UPDATE_PUB.update_credit_request_status';
1287 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling '|| l_last_api_called);
1288 OCM_CREDIT_REQUEST_UPDATE_PUB.update_credit_request_status (
1289 p_api_version => 1.0,
1290 p_init_msg_list => FND_API.G_TRUE,
1291 p_commit => FND_API.G_TRUE,
1292 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1293 x_return_status => x_return_status,
1294 x_msg_count => x_msg_count,
1295 x_msg_data => x_msg_data,
1296 p_credit_request_id => l_primary_credit_request_id,
1297 p_credit_request_status => 'SUBMIT');
1298 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After calling '|| l_last_api_called);
1299
1300
1301 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1302 l_loan_header_rec.loan_id := p_loan_id;
1303 l_loan_header_rec.secondary_status := 'IN_CREDIT_REVIEW';
1304 open C_GET_LOAN_OVN(p_loan_id);
1305 fetch C_GET_LOAN_OVN into l_object_version;
1306 close C_GET_LOAN_OVN;
1307
1308 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1309 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In submitCreditRequest: Before calling lns_loan_header_pub.update_loan api');
1310 END IF;
1311
1312 l_last_api_called := 'lns_loan_header_pub.update_loan';
1313 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling '|| l_last_api_called);
1314 lns_loan_header_pub.update_loan(
1315 p_init_msg_list => FND_API.G_TRUE
1316 ,P_LOAN_HEADER_REC => l_loan_header_rec
1317 ,P_OBJECT_VERSION_NUMBER => l_object_version
1318 ,X_RETURN_STATUS => x_return_status
1319 ,X_MSG_COUNT => x_msg_count
1320 ,X_MSG_DATA => x_msg_data);
1321 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After calling '|| l_last_api_called);
1322
1323 COMMIT;
1324
1325 else
1326
1327 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1328 FND_MESSAGE.Set_Name('LNS', 'LNS_CRDT_REQ_SUBMIT_API_FAILED');
1329 FND_MSG_PUB.Add;
1330 RAISE FND_API.G_EXC_ERROR;
1331
1332 end if;
1333
1334 else
1335
1336 FND_MESSAGE.Set_Name('LNS', 'LNS_CREDIT_REQUEST_NOT_FOUND');
1337 FND_MSG_PUB.Add;
1338 RAISE FND_API.G_EXC_ERROR;
1339
1340 end if;
1341 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1342 END submitCreditRequest;
1343
1344
1345 ----------------------------------------------------------------
1346 -- This is function updates lns_participants with the case_folder_id
1347 -- if credit management case folder has been submitted with recommendations
1348 -- for the loan application that submitted credit request
1349 -- This is called from workflow business event from credit management
1350 -- and also from the approval page UI if the loan is currently IN_CREDIT_REVIEW secondary status
1351 -- This function returns 'Y' for Successful update, 'N' for update failure/error and 'I' for invalid condition
1352 -- 'I' is returned when the loan has already changed status and case_folder_id has already been updated before
1353 ----------------------------------------------------------------
1354 FUNCTION CASE_FOLDER_UPDATE(p_loan_id IN NUMBER) RETURN VARCHAR2
1355 IS
1356
1357 l_return_flag VARCHAR2(1);
1358 l_secondary_status VARCHAR2(30);
1359 l_case_folder_count Number;
1360 l_user_id NUMBER;
1361 l_login_id NUMBER;
1362 l_date DATE;
1363 l_api_name CONSTANT VARCHAR2(30) := 'CASE_FOLDER_UPDATE';
1364
1365 CURSOR check_loan_sec_status(pLoanId Number) is
1366 select secondary_status
1367 from lns_loan_headers_all
1368 where loan_id = pLoanId;
1369
1370 CURSOR check_case_folder_complete(pLoanId Number) is
1371 select count(case.case_folder_id)
1372 from ar_cmgt_case_folders case, lns_participants part
1373 where case.credit_request_id = part.credit_request_id and
1374 part.loan_id = p_loan_id and
1375 part.loan_participant_type = 'PRIMARY_BORROWER' and
1376 part.end_date_active is null and
1377 case.type = 'CASE' and
1378 case.status = 'CLOSED';
1379
1380 CURSOR get_case_folder_info(pLoanId Number) is
1381 SELECT case_folder.case_folder_id,case_folder.credit_classification,part.participant_id
1382 FROM ar_cmgt_case_folders case_folder,lns_participants part
1383 WHERE case_folder.credit_request_id = part.credit_request_id
1384 AND part.loan_id = pLoanId
1385 AND type = 'CASE'
1386 AND status = 'CLOSED';
1387
1388 BEGIN
1389 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1390
1391 l_return_flag := 'N';
1392
1393 open check_loan_sec_status(p_loan_id);
1394 fetch check_loan_sec_status into l_secondary_status;
1395 close check_loan_sec_status;
1396
1397 open check_case_folder_complete(p_loan_id);
1398 fetch check_case_folder_complete into l_case_folder_count;
1399 close check_case_folder_complete;
1400 if (l_case_folder_count IS NULL) then
1401 l_case_folder_count := 0;
1402 end if;
1403
1404 if (l_secondary_status <> 'IN_CREDIT_REVIEW' OR l_case_folder_count <= 0) then
1405 l_return_flag := 'I';
1406 return l_return_flag;
1407 end if;
1408
1409 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1410 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE: Before calling update for lns_participants to update case_folder_id');
1411 END IF;
1412
1413 l_user_id := LNS_UTILITY_PUB.created_by;
1414 l_login_id := LNS_UTILITY_PUB.last_update_login;
1415 l_date := sysdate;
1416
1417 FOR case_folder_rec IN get_case_folder_info(p_loan_id) LOOP
1418 UPDATE LNS_PARTICIPANTS
1419 SET CASE_FOLDER_ID = case_folder_rec.case_folder_id,
1420 CREDIT_CLASSIFICATION = case_folder_rec.credit_classification,
1421 LAST_UPDATED_BY = l_user_id,
1422 LAST_UPDATE_LOGIN = l_login_id,
1423 LAST_UPDATE_DATE = l_date,
1424 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
1425 WHERE PARTICIPANT_ID = case_folder_rec.participant_id;
1426 END LOOP;
1427
1428 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1429 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE: After calling update for lns_participants to update case_folder_id');
1430 END IF;
1431
1432 --Call to record history
1433 LNS_LOAN_HISTORY_PUB.log_record_pre(
1434 p_id => p_loan_id,
1435 p_primary_key_name => 'LOAN_ID',
1436 p_table_name => 'LNS_LOAN_HEADERS_ALL'
1437 );
1438
1439 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1440 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE: Before calling update for lns_loan_headers_all to update new secondary status');
1441 END IF;
1442
1443 UPDATE LNS_LOAN_HEADERS_ALL
1444 SET SECONDARY_STATUS = 'CREDIT_REVIEW_COMPLETE',
1445 LAST_UPDATED_BY = l_user_id,
1446 LAST_UPDATE_LOGIN = l_login_id,
1447 LAST_UPDATE_DATE = l_date,
1448 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
1449 WHERE LOAN_ID = p_loan_id
1450 AND SECONDARY_STATUS = 'IN_CREDIT_REVIEW'
1451 AND EXISTS (SELECT case_folder_id
1452 FROM LNS_PARTICIPANTS
1453 WHERE loan_id = LNS_LOAN_HEADERS_ALL.loan_id and hz_party_id = LNS_LOAN_HEADERS_ALL.primary_borrower_id);
1454
1455 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1456 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE: After calling update for lns_loan_headers_all to update new secondary status');
1457 END IF;
1458
1459 --Call to record history
1460 LNS_LOAN_HISTORY_PUB.log_record_post(
1461 p_id => p_loan_id,
1462 p_primary_key_name => 'LOAN_ID',
1463 p_table_name => 'LNS_LOAN_HEADERS_ALL',
1464 p_loan_id => p_loan_id
1465 );
1466
1467 COMMIT WORK;
1468
1469 open check_loan_sec_status(p_loan_id);
1470 fetch check_loan_sec_status into l_secondary_status;
1471 close check_loan_sec_status;
1472
1473 if (l_secondary_status = 'CREDIT_REVIEW_COMPLETE') then
1474 l_return_flag := 'Y';
1475 end if;
1476
1477 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1478
1479 return l_return_flag;
1480
1481 EXCEPTION
1482 WHEN OTHERS THEN
1483
1484 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1485 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE: Unexpected ERROR in the function call. SQLERRM is: ' || SQLERRM);
1486 END IF;
1487
1488 return l_return_flag;
1489
1490 END CASE_FOLDER_UPDATE;
1491
1492 ----------------------------------------------------------------
1493 --This is rule function, that is subscribed to the Oracle Workflow
1494 -- Business Event CreditRequest.Recommendation.implement
1495 --to implement recomendations of the AR CRedit Management Review
1496 ----------------------------------------------------------------
1497 FUNCTION OCM_WORKFLOW_CREDIT_RECO_EVENT(p_subscription_guid IN RAW,
1498 p_event IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2
1499 IS
1500 l_key VARCHAR2(240);
1501 l_source_name VARCHAR2(30);
1502 l_source_column1 VARCHAR2(150);
1503 l_source_column2 VARCHAR2(150);
1504 l_source_column3 VARCHAR2(150);
1505 l_party_id NUMBER;
1506 l_cust_account_id NUMBER;
1507 l_site_use_id NUMBER;
1508 l_msg_count NUMBER;
1509 l_msg_data VARCHAR2(2000);
1510 l_return_status VARCHAR2(30);
1511 l_credit_request_id NUMBER;
1512 l_loan_id NUMBER;
1513 l_org_id NUMBER;
1514 l_wf_return_status VARCHAR2(30);
1515 l_user_id NUMBER;
1516 l_resp_id NUMBER;
1517 l_resp_appl_id NUMBER;
1518 l_security_group_id NUMBER;
1519 l_case_folder_id NUMBER;
1520 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
1521 l_object_version number;
1522 l_update_status_flag VARCHAR2(1);
1523
1524 CURSOR C_GET_LOAN_OVN(pLoanId Number) IS
1525 SELECT OBJECT_VERSION_NUMBER
1526 FROM
1527 LNS_LOAN_HEADERS_ALL
1528 WHERE LOAN_ID = pLoanId;
1529
1530 BEGIN
1531
1532 l_wf_return_status := 'SUCCESS';
1533
1534 l_key := p_event.GetEventKey();
1535 l_credit_request_id := p_event.GetValueForParameter('CREDIT_REQUEST_ID');
1536 l_source_name := p_event.GetValueForParameter('SOURCE_NAME');
1537 l_source_column1 := p_event.GetValueForParameter('SOURCE_COLUMN1');
1538 l_source_column2 := p_event.GetValueForParameter('SOURCE_COLUMN2');
1539 l_source_column3 := p_event.GetValueForParameter('SOURCE_COLUMN3');
1540 l_party_id := p_event.GetValueForParameter('PARTY_ID');
1541 l_cust_account_id := p_event.GetValueForParameter('CUST_ACCOUNT_ID');
1542 l_org_id := p_event.GetValueForParameter('ORG_ID');
1543 l_user_id := p_event.GetValueForParameter('USER_ID');
1544 l_resp_id := p_event.GetValueForParameter('RESP_ID');
1545 l_resp_appl_id := p_event.GetValueForParameter('RESP_APPL_ID');
1546 l_security_group_id := p_event.GetValueForParameter('SECURITY_GROUP_ID');
1547
1548 fnd_global.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id, l_security_group_id);
1549 MO_GLOBAL.INIT('LNS');
1550 MO_GLOBAL.set_policy_context('S',l_org_id);
1551
1552 IF (l_source_name = 'LNS') THEN
1553 l_loan_id := l_source_column1;
1554 l_update_status_flag := LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE(l_loan_id);
1555
1556 if (l_update_status_flag NOT IN ('I', 'Y')) then
1557 l_wf_return_status := 'ERROR';
1558 end if;
1559
1560 /*
1561 IF (l_credit_request_id IS NOT NULL) THEN
1562 SELECT CASE_FOLDER_ID INTO l_case_folder_id
1563 FROM
1564 AR_CMGT_CASE_FOLDERS
1565 WHERE CREDIT_REQUEST_ID = l_credit_request_id;
1566
1567 IF (l_case_folder_id IS NOT NULL) THEN
1568 UPDATE LNS_PARTICIPANTS
1569 SET CASE_FOLDER_ID = l_case_folder_id
1570 WHERE CREDIT_REQUEST_ID = l_credit_request_id;
1571
1572 l_loan_header_rec.loan_id := l_loan_id;
1573 l_loan_header_rec.secondary_status := 'CREDIT_REVIEW_COMPLETE';
1574 open C_GET_LOAN_OVN(l_loan_id);
1575 fetch C_GET_LOAN_OVN into l_object_version;
1576 close C_GET_LOAN_OVN;
1577
1578 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1579 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In OCM_WORKFLOW_CREDIT_RECO_EVENT: Before calling lns_loan_header_pub.update_loan api');
1580 END IF;
1581
1582 lns_loan_header_pub.update_loan(
1583 p_init_msg_list => FND_API.G_TRUE
1584 ,P_LOAN_HEADER_REC => l_loan_header_rec
1585 ,P_OBJECT_VERSION_NUMBER => l_object_version
1586 ,X_RETURN_STATUS => l_return_status
1587 ,X_MSG_COUNT => l_msg_count
1588 ,X_MSG_DATA => l_msg_data);
1589
1590 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1591 l_wf_return_status := 'ERROR';
1592 else
1593 COMMIT;
1594 end if;
1595
1596 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1597 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In OCM_WORKFLOW_CREDIT_RECO_EVENT: After calling lns_loan_header_pub.update_loan api: return_status:'||l_return_status);
1598 END IF;
1599
1600
1601 END IF; -- IF (l_case_folder_id IS NOT NULL) THEN
1602
1603 END IF; -- IF (l_credit_request_id IS NOT NULL) THEN
1604 */
1605 END IF; -- IF (l_source_name = 'LNS') THEN
1606
1607 RETURN l_wf_return_status;
1608
1609 EXCEPTION
1610 WHEN OTHERS THEN
1611 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
1612 FND_MSG_PUB.ADD;
1613 WF_CORE.CONTEXT(G_PKG_NAME,
1614 'OCM_WORKFLOW_CREDIT_RECO_EVENT',
1615 p_event.getEventName(),
1616 p_subscription_guid);
1617 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1618
1619 RETURN 'ERROR';
1620
1621 END OCM_WORKFLOW_CREDIT_RECO_EVENT;
1622
1623
1624 ----------------------------------------------------------------
1625 --This procedure
1626 --
1627 --
1628 ----------------------------------------------------------------
1629 PROCEDURE getDefaultPrimaryContact(p_loan_participant_rec IN OUT NOCOPY loan_participant_rec_type,
1630 x_return_status OUT NOCOPY VARCHAR2,
1631 x_msg_count OUT NOCOPY NUMBER,
1632 x_msg_data OUT NOCOPY VARCHAR2)
1633 IS
1634
1635 l_party_type varchar2(30);
1636 l_api_name CONSTANT varchar2(25) := 'getDefaultPrimaryContact';
1637
1638 CURSOR c_get_party_type(p_party_id number) IS
1639 select party_type
1640 from hz_parties
1641 where party_id = p_party_id;
1642
1643 BEGIN
1644
1645 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1646 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, p_loan_participant_rec.LOAN_PARTICIPANT_TYPE);
1647 IF p_loan_participant_rec.LOAN_PARTICIPANT_TYPE <> 'PRIMARY_BORROWER' THEN
1648 x_return_status := FND_API.G_RET_STS_ERROR;
1649 END IF;
1650
1651 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Contact Person '||p_loan_participant_rec.CONTACT_PERS_PARTY_ID||' and Rel PartyId is '||p_loan_participant_rec.CONTACT_REL_PARTY_ID);
1652
1653 IF (p_loan_participant_rec.CONTACT_PERS_PARTY_ID IS NULL or p_loan_participant_rec.CONTACT_PERS_PARTY_ID = FND_API.G_MISS_NUM) AND
1654 (p_loan_participant_rec.CONTACT_REL_PARTY_ID IS NULL OR p_loan_participant_rec.CONTACT_REL_PARTY_ID = FND_API.G_MISS_NUM) THEN
1655 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Contact Person and PartyId are NULL');
1656 p_loan_participant_rec.CONTACT_REL_PARTY_ID := p_loan_participant_rec.HZ_PARTY_ID;
1657
1658 open c_get_party_type(p_loan_participant_rec.HZ_PARTY_ID);
1659 fetch c_get_party_type into l_party_type;
1660 close c_get_party_type;
1661
1662 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Party Type is '||l_party_type ||' for '||p_loan_participant_rec.HZ_PARTY_ID);
1663 IF l_party_type = 'PERSON' THEN
1664 p_loan_participant_rec.CONTACT_PERS_PARTY_ID := p_loan_participant_rec.HZ_PARTY_ID;
1665 END IF;
1666 END IF;
1667 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1668
1669 END getDefaultPrimaryContact;
1670
1671
1672
1673 END LNS_PARTICIPANTS_PUB;