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