[Home] [Help]
PACKAGE BODY: APPS.LNS_LOAN_PUB
Source
1 PACKAGE BODY LNS_LOAN_PUB as
2 /* $Header: LNS_LOAN_PUBP_B.pls 120.22.12020000.2 2013/02/11 20:00:37 scherkas ship $ */
3
4 /*========================================================================
5 | PUBLIC PROCEDURE SELECT_WF_PROCESS
6 |
7 | DESCRIPTION
8 | This process selects the process to run.
9 |
10 | PSEUDO CODE/LOGIC
11 |
12 | PARAMETERS
13 | P_PARAM1 IN Standard in parameter
14 | X_PARAM2 OUT NOCOPY Standard out parameter
15 |
16 | KNOWN ISSUES
17 | None
18 |
19 | NOTES
20 |
21 | MODIFICATION HISTORY
22 | Date Author Description of Changes
23 | 17-Jan-2006 GBELLARY Created
24 | 17-Apr-2007 MBOLLI Modified- Bug#5923205
25 |
26 *=======================================================================*/
27
28
29 /*=======================================================================+
30 | Package Global Constants
31 +=======================================================================*/
32 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_LOAN_PUB';
33 G_LOG_ENABLED varchar2(5);
34 G_MSG_LEVEL NUMBER;
35 g_org_id number;
36 g_errors_rec Loan_create_errors_type := Loan_create_errors_type();
37 g_error_count number := 0;
38
39 CURSOR Csr_Product_values (p_product_id IN NUMBER) IS
40 SELECT loan_type.loan_type_id loan_type_id
41 ,loan_type.loan_class_code loan_class_code
42 ,loan_type.loan_type_name loan_type_name
43 ,loan_type.multiple_funding_flag multiple_funding_flag
44 ,loan_type.open_to_term_flag open_to_term_flag
45 ,loan_type.credit_review_flag credit_review_flag
46 ,loan_product.loan_product_id loan_product_id
47 ,loan_product.loan_product_name loan_product_name
48 ,loan_product.loan_term loan_term
49 ,loan_product.loan_term_period loan_term_period
50 ,loan_product.max_loan_term max_loan_term
51 ,loan_product.max_loan_term_period max_loan_term_period
52 ,loan_product.loan_currency loan_currency
53 ,loan_product.requested_amount requested_amount
54 ,loan_product.max_requested_amount max_requested_amount
55 ,loan_product.index_rate_id index_rate_id
56 ,loan_product.rate_type rate_type
57 ,loan_product.spread spread
58 ,loan_product.floor_rate floor_rate
59 ,loan_product.ceiling_rate ceiling_rate
60 ,loan_product.interest_compounding_freq interest_compounding_freq
61 ,loan_product.loan_payment_frequency loan_payment_frequency
62 ,loan_product.loan_subtype loan_subtype
63 ,loan_product.collateral_percent collateral_percent
64 ,loan_product.allow_interest_only_flag allow_interest_only_flag
65 ,loan_product.reamortize_over_payment reamortize_over_payment
66 ,loan_product.org_id org_id
67 ,loan_product.legal_entity_id legal_entity_id
68 ,loan_product.rate_change_frequency rate_change_frequency
69 ,loan_type.payment_application_order payment_application_order
70 ,loan_type.pmt_appl_order_scope pmt_appl_order_scope
71 ,loan_product.open_floor_rate open_floor_rate
72 ,loan_product.open_ceiling_rate open_ceiling_rate
73 ,loan_product.reamortize_under_payment reamortize_under_payment
74 ,loan_product.percent_increase percent_increase
75 ,loan_product.percent_increase_life percent_increase_life
76 ,loan_product.open_percent_increase open_percent_increase
77 ,loan_product.open_percent_increase_life open_percent_increase_life
78 ,loan_product.open_spread open_spread
79 ,loan_product.credit_review_type credit_review_type
80 ,loan_product.guarantor_review_type guarantor_review_type
81 ,loan_product.party_type party_type
82 ,loan_product.open_loan_term open_loan_term
83 ,loan_product.open_loan_term_period open_loan_term_period
84 ,loan_product.open_max_loan_term open_max_loan_term
85 ,loan_product.open_max_loan_term_period open_max_loan_term_period
86 ,loan_product.interest_calculation_method CALCULATION_METHOD
87 ,loan_product.day_count_method day_count_method
88 ,loan_product.FORGIVENESS_FLAG
89 ,loan_product.FORGIVENESS_PERCENT
90 ,loan_product.PAYMENT_CALC_METHOD
91 ,loan_product.PENAL_INT_RATE
92 ,loan_product.PENAL_INT_GRACE_DAYS
93 ,loan_product.CALC_ADD_INT_UNPAID_PRIN
94 ,loan_product.CALC_ADD_INT_UNPAID_INT
95 ,loan_product.PRINCIPAL_PAYMENT_FREQUENCY
96 ,loan_product.REAMORTIZE_ON_FUNDING
97 ,loan_product.ATTRIBUTE_CATEGORY
98 ,loan_product.ATTRIBUTE1
99 ,loan_product.ATTRIBUTE2
100 ,loan_product.ATTRIBUTE3
101 ,loan_product.ATTRIBUTE4
102 ,loan_product.ATTRIBUTE5
103 ,loan_product.ATTRIBUTE6
104 ,loan_product.ATTRIBUTE7
105 ,loan_product.ATTRIBUTE8
106 ,loan_product.ATTRIBUTE9
107 ,loan_product.ATTRIBUTE10
108 ,loan_product.ATTRIBUTE11
109 ,loan_product.ATTRIBUTE12
110 ,loan_product.ATTRIBUTE13
111 ,loan_product.ATTRIBUTE14
112 ,loan_product.ATTRIBUTE15
113 ,loan_product.ATTRIBUTE16
114 ,loan_product.ATTRIBUTE17
115 ,loan_product.ATTRIBUTE18
116 ,loan_product.ATTRIBUTE19
117 ,loan_product.ATTRIBUTE20
118 ,loan_product.CUSTOM_SCHED_DATA
119 ,loan_product.CUSTOM_CALC_METHOD
120 FROM lns_loan_types loan_type
121 ,lns_loan_products loan_product
122 WHERE loan_product.loan_product_id = p_product_id
123 AND loan_type.loan_type_id = loan_product.loan_type_id
124 AND loan_type.status = 'COMPLETE'
125 AND loan_type.start_date_active <= sysdate
126 AND (loan_type.end_date_active is null OR
127 loan_type.end_date_active >= sysdate)
128 AND loan_product.status = 'COMPLETE'
129 AND loan_product.start_date_active <= sysdate
130 AND (loan_product.end_date_active is null OR
131 loan_product.end_date_active >= sysdate);
132
133 l_product_rec Csr_Product_values%ROWTYPE;
134
135 CURSOR Csr_override_flags (p_product_id IN NUMBER) IS
136 SELECT LEGAL_ENTITY_ID_TBL.flag LEGAL_ENTITY_ID_OVR
137 ,LOAN_TERM_TBL.flag LOAN_TERM_OVR
138 ,LOAN_TERM_PERIOD_TBL.flag LOAN_TERM_PERIOD_OVR
139 ,INDEX_RATE_ID_TBL.flag INDEX_RATE_ID_OVR
140 ,RATE_TYPE_TBL.flag RATE_TYPE_OVR
141 ,INTEREST_COMPOUNDING_FREQ_TBL.flag INTEREST_COMPOUNDING_FREQ_OVR
142 ,OPEN_SPREAD_TBL.flag OPEN_SPREAD_OVR
143 ,OPEN_FLOOR_RATE_TBL.flag OPEN_FLOOR_RATE_OVR
144 ,OPEN_CEILING_RATE_TBL.flag OPEN_CEILING_RATE_OVR
145 ,OPEN_PERCENT_INCREASE_TBL.flag OPEN_PERCENT_INCREASE_OVR
146 ,OPEN_PERCENT_INCREASE_LIFE_TBL.flag OPEN_PERCENT_INCREASE_LIFE_OVR
147 ,SPREAD_TBL.flag SPREAD_OVR
148 ,FLOOR_RATE_TBL.flag FLOOR_RATE_OVR
149 ,CEILING_RATE_TBL.flag CEILING_RATE_OVR
150 ,PERCENT_INCREASE_TBL.flag PERCENT_INCREASE_OVR
151 ,PERCENT_INCREASE_LIFE_TBL.flag PERCENT_INCREASE_LIFE_OVR
152 ,LOAN_PAYMENT_FREQUENCY_TBL.flag LOAN_PAYMENT_FREQ_OVR
153 ,LOAN_SUBTYPE_TBL.flag LOAN_SUBTYPE_OVR
154 ,REAMORTIZE_OVER_PAYMENT_TBL.flag REAMORTIZE_OVER_PAYMENT_OVR
155 ,DAY_COUNT_METHOD_TBL.flag DAY_COUNT_METHOD_OVR
156 ,CALCULATION_METHOD_TBL.flag CALCULATION_METHOD_OVR
157 ,RATE_CHANGE_FREQUENCY_TBL.flag RATE_CHANGE_FREQUENCY_OVR
158 ,COLLATERAL_PERCENT_TBL.flag COLLATERAL_PERCENT_OVR
159 ,FORGIVENESS_FLAG_TBL.flag FORGIVENESS_FLAG_OVR
160 ,FORGIVENESS_PERCENT_TBL.flag FORGIVENESS_PERCENT_OVR
161 ,PAYMENT_CALC_METHOD_TBL.flag PAYMENT_CALC_METHOD_OVR
162 ,PRIN_PAYMENT_FREQUENCY_TBL.flag PRIN_PAYMENT_FREQUENCY_OVR
163 ,PENAL_INT_RATE_TBL.flag PENAL_INT_RATE_OVR
164 ,PENAL_INT_GRACE_DAYS_TBL.flag PENAL_INT_GRACE_DAYS_OVR
165 ,CALC_ADD_INT_UNPAID_PRIN_TBL.flag CALC_ADD_INT_UNPAID_PRIN_OVR
166 ,CALC_ADD_INT_UNPAID_INT_TBL.flag CALC_ADD_INT_UNPAID_INT_OVR
167 ,REAMORTIZE_ON_FUNDING_TBL.flag REAMORTIZE_ON_FUNDING_OVR
168 ,CUSTOM_CALC_METHOD_TBL.flag CUSTOM_CALC_METHOD_OVR
169 FROM (SELECT DECODE(COUNT(*),0,'Y','N') flag
170 FROM LNS_LOAN_PRODUCT_FLAGS
171 WHERE loan_product_id = p_product_id
172 AND READONLY_COLUMN_NAME = 'LEGAL_ENTITY_ID'
173 ) LEGAL_ENTITY_ID_TBL,
174 (SELECT DECODE(COUNT(*),0,'Y','N') flag
175 FROM LNS_LOAN_PRODUCT_FLAGS
176 WHERE loan_product_id = p_product_id
177 AND READONLY_COLUMN_NAME = 'LOAN_TERM'
178 ) LOAN_TERM_TBL,
179 (SELECT DECODE(COUNT(*),0,'Y','N') flag
180 FROM LNS_LOAN_PRODUCT_FLAGS
181 WHERE loan_product_id = p_product_id
182 AND READONLY_COLUMN_NAME = 'LOAN_TERM_PERIOD'
183 ) LOAN_TERM_PERIOD_TBL,
184 (SELECT DECODE(COUNT(*),0,'Y','N') flag
185 FROM LNS_LOAN_PRODUCT_FLAGS
186 WHERE loan_product_id = p_product_id
187 AND READONLY_COLUMN_NAME = 'INDEX_RATE_ID'
188 ) INDEX_RATE_ID_TBL,
189 (SELECT DECODE(COUNT(*),0,'Y','N') flag
190 FROM LNS_LOAN_PRODUCT_FLAGS
191 WHERE loan_product_id = p_product_id
192 AND READONLY_COLUMN_NAME = 'RATE_TYPE'
193 ) RATE_TYPE_TBL,
194 (SELECT DECODE(COUNT(*),0,'Y','N') flag
195 FROM LNS_LOAN_PRODUCT_FLAGS
196 WHERE loan_product_id = p_product_id
197 AND READONLY_COLUMN_NAME = 'INTEREST_COMPOUNDING_FREQ'
198 ) INTEREST_COMPOUNDING_FREQ_TBL,
199 (SELECT DECODE(COUNT(*),0,'Y','N') flag
200 FROM LNS_LOAN_PRODUCT_FLAGS
201 WHERE loan_product_id = p_product_id
202 AND READONLY_COLUMN_NAME = 'OPEN_SPREAD'
203 ) OPEN_SPREAD_TBL,
204 (SELECT DECODE(COUNT(*),0,'Y','N') flag
205 FROM LNS_LOAN_PRODUCT_FLAGS
206 WHERE loan_product_id = p_product_id
207 AND READONLY_COLUMN_NAME = 'OPEN_FLOOR_RATE'
208 ) OPEN_FLOOR_RATE_TBL,
209 (SELECT DECODE(COUNT(*),0,'Y','N') flag
210 FROM LNS_LOAN_PRODUCT_FLAGS
211 WHERE loan_product_id = p_product_id
212 AND READONLY_COLUMN_NAME = 'OPEN_CEILING_RATE'
213 ) OPEN_CEILING_RATE_TBL,
214 (SELECT DECODE(COUNT(*),0,'Y','N') flag
215 FROM LNS_LOAN_PRODUCT_FLAGS
216 WHERE loan_product_id = p_product_id
217 AND READONLY_COLUMN_NAME = 'OPEN_PERCENT_INCREASE'
218 ) OPEN_PERCENT_INCREASE_TBL,
219 (SELECT DECODE(COUNT(*),0,'Y','N') flag
220 FROM LNS_LOAN_PRODUCT_FLAGS
221 WHERE loan_product_id = p_product_id
222 AND READONLY_COLUMN_NAME = 'OPEN_PERCENT_INCREASE_LIFE'
223 ) OPEN_PERCENT_INCREASE_LIFE_TBL,
224 (SELECT DECODE(COUNT(*),0,'Y','N') flag
225 FROM LNS_LOAN_PRODUCT_FLAGS
226 WHERE loan_product_id = p_product_id
227 AND READONLY_COLUMN_NAME = 'SPREAD'
228 ) SPREAD_TBL,
229 (SELECT DECODE(COUNT(*),0,'Y','N') flag
230 FROM LNS_LOAN_PRODUCT_FLAGS
231 WHERE loan_product_id = p_product_id
232 AND READONLY_COLUMN_NAME = 'FLOOR_RATE'
233 ) FLOOR_RATE_TBL,
234 (SELECT DECODE(COUNT(*),0,'Y','N') flag
235 FROM LNS_LOAN_PRODUCT_FLAGS
236 WHERE loan_product_id = p_product_id
237 AND READONLY_COLUMN_NAME = 'CEILING_RATE'
238 ) CEILING_RATE_TBL,
239 (SELECT DECODE(COUNT(*),0,'Y','N') flag
240 FROM LNS_LOAN_PRODUCT_FLAGS
241 WHERE loan_product_id = p_product_id
242 AND READONLY_COLUMN_NAME = 'PERCENT_INCREASE'
243 ) PERCENT_INCREASE_TBL,
244 (SELECT DECODE(COUNT(*),0,'Y','N') flag
245 FROM LNS_LOAN_PRODUCT_FLAGS
246 WHERE loan_product_id = p_product_id
247 AND READONLY_COLUMN_NAME = 'PERCENT_INCREASE_LIFE'
248 ) PERCENT_INCREASE_LIFE_TBL,
249 (SELECT DECODE(COUNT(*),0,'Y','N') flag
250 FROM LNS_LOAN_PRODUCT_FLAGS
251 WHERE loan_product_id = p_product_id
252 AND READONLY_COLUMN_NAME = 'LOAN_PAYMENT_FREQUENCY'
253 ) LOAN_PAYMENT_FREQUENCY_TBL,
254 (SELECT DECODE(COUNT(*),0,'Y','N') flag
255 FROM LNS_LOAN_PRODUCT_FLAGS
256 WHERE loan_product_id = p_product_id
257 AND READONLY_COLUMN_NAME = 'LOAN_SUBTYPE'
258 ) LOAN_SUBTYPE_TBL,
259 (SELECT DECODE(COUNT(*),0,'Y','N') flag
260 FROM LNS_LOAN_PRODUCT_FLAGS
261 WHERE loan_product_id = p_product_id
262 AND READONLY_COLUMN_NAME = 'REAMORTIZE_OVER_PAYMENT'
263 ) REAMORTIZE_OVER_PAYMENT_TBL,
264 (SELECT DECODE(COUNT(*),0,'Y','N') flag
265 FROM LNS_LOAN_PRODUCT_FLAGS
266 WHERE loan_product_id = p_product_id
267 AND READONLY_COLUMN_NAME = 'DAY_COUNT_METHOD'
268 ) DAY_COUNT_METHOD_TBL,
269 (SELECT DECODE(COUNT(*),0,'Y','N') flag
270 FROM LNS_LOAN_PRODUCT_FLAGS
271 WHERE loan_product_id = p_product_id
272 AND READONLY_COLUMN_NAME = 'INTEREST_CALCULATION_METHOD'
273 ) CALCULATION_METHOD_TBL,
274 (SELECT DECODE(COUNT(*),0,'Y','N') flag
275 FROM LNS_LOAN_PRODUCT_FLAGS
276 WHERE loan_product_id = p_product_id
277 AND READONLY_COLUMN_NAME = 'RATE_CHANGE_FREQUENCY'
278 ) RATE_CHANGE_FREQUENCY_TBL,
279 (SELECT DECODE(COUNT(*),0,'Y','N') flag
280 FROM LNS_LOAN_PRODUCT_FLAGS
281 WHERE loan_product_id = p_product_id
282 AND READONLY_COLUMN_NAME = 'COLLATERAL_PERCENT'
283 ) COLLATERAL_PERCENT_TBL,
284 (SELECT DECODE(COUNT(*),0,'Y','N') flag
285 FROM LNS_LOAN_PRODUCT_FLAGS
286 WHERE loan_product_id = p_product_id
287 AND READONLY_COLUMN_NAME = 'FORGIVENESS_FLAG'
288 ) FORGIVENESS_FLAG_TBL,
289 (SELECT DECODE(COUNT(*),0,'Y','N') flag
290 FROM LNS_LOAN_PRODUCT_FLAGS
291 WHERE loan_product_id = p_product_id
292 AND READONLY_COLUMN_NAME = 'FORGIVENESS_PERCENT'
293 ) FORGIVENESS_PERCENT_TBL,
294 (SELECT DECODE(COUNT(*),0,'Y','N') flag
295 FROM LNS_LOAN_PRODUCT_FLAGS
296 WHERE loan_product_id = p_product_id
297 AND READONLY_COLUMN_NAME = 'PAYMENT_CALC_METHOD'
298 ) PAYMENT_CALC_METHOD_TBL,
299 (SELECT DECODE(COUNT(*),0,'Y','N') flag
300 FROM LNS_LOAN_PRODUCT_FLAGS
301 WHERE loan_product_id = p_product_id
302 AND READONLY_COLUMN_NAME = 'PRINCIPAL_PAYMENT_FREQUENCY'
303 ) PRIN_PAYMENT_FREQUENCY_TBL,
304 (SELECT DECODE(COUNT(*),0,'Y','N') flag
305 FROM LNS_LOAN_PRODUCT_FLAGS
306 WHERE loan_product_id = p_product_id
307 AND READONLY_COLUMN_NAME = 'PENAL_INT_RATE'
308 ) PENAL_INT_RATE_TBL,
309 (SELECT DECODE(COUNT(*),0,'Y','N') flag
310 FROM LNS_LOAN_PRODUCT_FLAGS
311 WHERE loan_product_id = p_product_id
312 AND READONLY_COLUMN_NAME = 'PENAL_INT_GRACE_DAYS'
313 ) PENAL_INT_GRACE_DAYS_TBL,
314 (SELECT DECODE(COUNT(*),0,'Y','N') flag
315 FROM LNS_LOAN_PRODUCT_FLAGS
316 WHERE loan_product_id = p_product_id
317 AND READONLY_COLUMN_NAME = 'CALC_ADD_INT_UNPAID_PRIN'
318 ) CALC_ADD_INT_UNPAID_PRIN_TBL,
319 (SELECT DECODE(COUNT(*),0,'Y','N') flag
320 FROM LNS_LOAN_PRODUCT_FLAGS
321 WHERE loan_product_id = p_product_id
322 AND READONLY_COLUMN_NAME = 'CALC_ADD_INT_UNPAID_INT'
323 ) CALC_ADD_INT_UNPAID_INT_TBL,
324 (SELECT DECODE(COUNT(*),0,'Y','N') flag
325 FROM LNS_LOAN_PRODUCT_FLAGS
326 WHERE loan_product_id = p_product_id
327 AND READONLY_COLUMN_NAME = 'REAMORTIZE_ON_FUNDING'
328 ) REAMORTIZE_ON_FUNDING_TBL,
329 (SELECT DECODE(COUNT(*),0,'Y','N') flag
330 FROM LNS_LOAN_PRODUCT_FLAGS
331 WHERE loan_product_id = p_product_id
332 AND READONLY_COLUMN_NAME = 'CUSTOM_CALC_METHOD'
333 ) CUSTOM_CALC_METHOD_TBL;
334
335 l_override_rec Csr_override_flags%ROWTYPE;
336
337 PROCEDURE validate_loan_header_details(P_Loan_Details_Rec IN OUT NOCOPY LNS_LOAN_PUB.Loan_Details_Rec_Type
338 ,p_loan_cust_sched_tbl IN OUT NOCOPY LNS_LOAN_PUB.loan_cust_sched_tbl_type
339 ,x_return_status OUT nocopy VARCHAR2
340 ,x_msg_count OUT nocopy NUMBER
341 ,x_msg_data OUT nocopy VARCHAR2);
342
343 PROCEDURE validate_participants(p_loan_part_tbl IN OUT NOCOPY LNS_LOAN_PUB.LOAN_PART_TBL_TYPE
344 ,x_return_status OUT nocopy VARCHAR2
345 ,x_msg_count OUT nocopy NUMBER
346 ,x_msg_data OUT nocopy VARCHAR2);
347
348 PROCEDURE validate_loan_lines(P_Loan_Details_Rec IN OUT NOCOPY LNS_LOAN_PUB.Loan_Details_Rec_Type
349 ,p_loan_lines_tbl IN OUT nocopy LNS_LOAN_PUB.loan_lines_tbl_type
350 ,x_return_status OUT nocopy VARCHAR2
351 ,x_msg_count OUT nocopy NUMBER
352 ,x_msg_data OUT nocopy VARCHAR2);
353
354 PROCEDURE validate_rate_sched(P_Loan_Details_Rec IN OUT NOCOPY LNS_LOAN_PUB.Loan_Details_Rec_Type
355 ,p_loan_rates_tbl IN OUT nocopy LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE
356 ,p_phase IN VARCHAR2
357 ,x_return_status OUT nocopy VARCHAR2
358 ,x_msg_count OUT nocopy NUMBER
359 ,x_msg_data OUT nocopy VARCHAR2);
360
361 procedure synchRateSchedule(p_rate_tbl IN OUT NOCOPY LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE, p_num_installments in number);
362
363 PROCEDURE validate_disbursements(P_Loan_Details_Rec IN OUT nocopy LNS_LOAN_PUB.Loan_Details_Rec_Type
364 ,P_DISB_TBL IN OUT NOCOPY LNS_LOAN_PUB.Loan_Disb_Tbl_Type
365 , x_return_status OUT nocopy VARCHAR2
366 , x_msg_count OUT nocopy NUMBER
367 , x_msg_data OUT nocopy VARCHAR2);
368
369 /*========================================================================
370 | PRIVATE PROCEDURE LogMessage
371 |
372 | DESCRIPTION
373 | This procedure logs debug messages to db and to CM log
374 |
375 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
376 |
377 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
378 | None
379 |
380 | PARAMETERS
381 | p_msg_level IN Debug msg level
382 | p_msg IN Debug msg itself
383 |
384 | KNOWN ISSUES
385 | None
386 |
387 |
388 | NOTES
389 | Any interesting aspect of the code in the package body which needs
390 | to be stated.
391 |
392 | MODIFICATION HISTORY
393 | Date Author Description of Changes
394 | 17-Jan-2006 GBELLARY Created
395 |
396 *=======================================================================*/
397 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
398 IS
399 BEGIN
400 if (p_msg_level >= G_MSG_LEVEL) then
401
402 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
403
404 end if;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
409 END;
410
411
412
413 /*========================================================================
414 | PRIVATE PROCEDURE LogErrors
415 |
416 | DESCRIPTION
417 | This procedure logs debug messages to db and to CM log
418 |
419 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
420 |
421 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
422 | None
423 |
424 | PARAMETERS
425 | p_msg_level IN Debug msg level
426 | p_msg IN Debug msg itself
427 |
428 | KNOWN ISSUES
429 | None
430 |
431 |
432 | NOTES
433 | This procedure builds the error message and stores it (alongwith
434 | other columns in LNS_LOAN_API_ERRORS_GT) in g_errors_rec.
435 |
436 | MODIFICATION HISTORY
437 | Date Author Description of Changes
438 | 17-Jan-2006 GBELLARY Created
439 |
440 *=======================================================================*/
441 Procedure LogErrors( p_message_name IN VARCHAR2
442 ,p_line_number IN NUMBER DEFAULT NULL
443 ,p_token1 IN VARCHAR2 DEFAULT NULL
444 ,p_token2 IN VARCHAR2 DEFAULT NULL
445 ,p_token3 IN VARCHAR2 DEFAULT NULL)
446 IS
447 l_text LNS_LOAN_API_ERRORS_GT.MESSAGE_TEXT%TYPE;
448
449 BEGIN
450
451 fnd_message.set_name('LNS', p_message_name);
452
453 if p_token1 is NOT NULL THEN
454 fnd_message.set_token('TOKEN1',p_token1);
455 end if;
456
457 IF p_token2 is NOT NULL THEN
458 fnd_message.set_token('TOKEN2',p_token2);
459 END IF;
460
461 IF p_token3 is NOT NULL THEN
462 fnd_message.set_token('TOKEN3',p_token3);
463 END IF;
464
465 FND_MSG_PUB.Add;
466 l_text := substrb(fnd_message.get,1,2000);
467 g_error_count := g_error_count+1;
468 g_errors_rec.extend(1);
469 g_errors_rec(g_error_count).ERROR_NUMBER := g_error_count;
470 g_errors_rec(g_error_count).MESSAGE_NAME := p_message_name;
471 g_errors_rec(g_error_count).MESSAGE_TEXT := l_text;
472 g_errors_rec(g_error_count).LINE_NUMBER := p_line_number;
473 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || p_message_name || '(' || p_token1 || ',' || p_token2 || ',' || p_token3 || ') - ' || l_text);
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
478 END;
479
480
481
482
483 PROCEDURE validate_loan_header_details(P_Loan_Details_Rec IN OUT NOCOPY LNS_LOAN_PUB.Loan_Details_Rec_Type
484 ,p_loan_cust_sched_tbl IN OUT NOCOPY LNS_LOAN_PUB.loan_cust_sched_tbl_type
485 ,x_return_status OUT nocopy VARCHAR2
486 ,x_msg_count OUT nocopy NUMBER
487 ,x_msg_data OUT nocopy VARCHAR2)
488 IS
489
490 l_api_name constant VARCHAR2(30) := 'VALIDATE_LOAN_HEADER_DETAILS';
491 l_dummy VARCHAR2(30);
492 l_exchange_required VARCHAR2(1);
493 l_functional_currency gl_sets_of_books.currency_code%TYPE;
494 l_borrower_valid boolean;
495 l_cust_acct_valid boolean;
496 l_contact_rel_party_id lns_loan_headers_all.contact_rel_party_id%TYPE;
497 l_contact_pers_party_id lns_loan_headers_all.contact_pers_party_id%TYPE;
498 l_valid_contact_rel VARCHAR2(1) := 'N';
499 l_valid_contact_pers VARCHAR2(1) := 'N';
500
501 BEGIN
502 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' +');
503
504 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Loan_Details_Rec:');
505 LogMessage(FND_LOG.LEVEL_STATEMENT, 'product_id = ' || P_Loan_Details_Rec.product_id);
506 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_number = ' || P_Loan_Details_Rec.loan_number);
507 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_description = ' || P_Loan_Details_Rec.loan_description);
508 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_assigned_to = ' || P_Loan_Details_Rec.loan_assigned_to);
509 LogMessage(FND_LOG.LEVEL_STATEMENT, 'legal_entity_id = ' || P_Loan_Details_Rec.legal_entity_id);
510 LogMessage(FND_LOG.LEVEL_STATEMENT, 'requested_amount = ' || P_Loan_Details_Rec.requested_amount);
511 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_application_date = ' || P_Loan_Details_Rec.loan_application_date);
512 LogMessage(FND_LOG.LEVEL_STATEMENT, 'exchange_rate_type = ' || P_Loan_Details_Rec.exchange_rate_type);
513 LogMessage(FND_LOG.LEVEL_STATEMENT, 'exchange_rate = ' || P_Loan_Details_Rec.exchange_rate);
514 LogMessage(FND_LOG.LEVEL_STATEMENT, 'exchange_date = ' || P_Loan_Details_Rec.exchange_date);
515 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_purpose_code = ' || P_Loan_Details_Rec.loan_purpose_code);
516 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_subtype = ' || P_Loan_Details_Rec.loan_subtype);
517 LogMessage(FND_LOG.LEVEL_STATEMENT, 'credit_review_flag = ' || P_Loan_Details_Rec.credit_review_flag);
518 LogMessage(FND_LOG.LEVEL_STATEMENT, 'trx_type_id = ' || P_Loan_Details_Rec.trx_type_id);
519 LogMessage(FND_LOG.LEVEL_STATEMENT, 'collateral_percent = ' || P_Loan_Details_Rec.collateral_percent);
520 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUSTOM_PAYMENTS_FLAG = ' || P_Loan_Details_Rec.CUSTOM_PAYMENTS_FLAG);
521 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FORGIVENESS_FLAG = ' || P_Loan_Details_Rec.FORGIVENESS_FLAG);
522 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FORGIVENESS_PERCENT = ' || P_Loan_Details_Rec.FORGIVENESS_PERCENT);
523
524 LogMessage(FND_LOG.LEVEL_STATEMENT, 'primary_borrower_party_id = ' || P_Loan_Details_Rec.primary_borrower_party_id);
525 LogMessage(FND_LOG.LEVEL_STATEMENT, 'cust_account_id = ' || P_Loan_Details_Rec.cust_account_id);
526 LogMessage(FND_LOG.LEVEL_STATEMENT, 'bill_to_acct_site_id = ' || P_Loan_Details_Rec.bill_to_acct_site_id);
527 LogMessage(FND_LOG.LEVEL_STATEMENT, 'contact_rel_party_id = ' || P_Loan_Details_Rec.contact_rel_party_id);
528 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CONTACT_PERS_PARTY_ID = ' || P_Loan_Details_Rec.CONTACT_PERS_PARTY_ID);
529
530 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RATE_TYPE = ' || P_Loan_Details_Rec.RATE_TYPE);
531 LogMessage(FND_LOG.LEVEL_STATEMENT, 'index_rate_id = ' || P_Loan_Details_Rec.index_rate_id);
532 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DAY_COUNT_METHOD = ' || P_Loan_Details_Rec.DAY_COUNT_METHOD);
533 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_payment_frequency = ' || P_Loan_Details_Rec.loan_payment_frequency);
534 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CALCULATION_METHOD = ' || P_Loan_Details_Rec.CALCULATION_METHOD);
535 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INTEREST_COMPOUNDING_FREQ = ' || P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ);
536 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_CALC_METHOD = ' || P_Loan_Details_Rec.PAYMENT_CALC_METHOD);
537 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUSTOM_CALC_METHOD = ' || P_Loan_Details_Rec.CUSTOM_CALC_METHOD);
538 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ORIG_PAY_CALC_METHOD = ' || P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD);
539 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PENAL_INT_RATE = ' || P_Loan_Details_Rec.PENAL_INT_RATE);
540 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PENAL_INT_GRACE_DAYS = ' || P_Loan_Details_Rec.PENAL_INT_GRACE_DAYS);
541 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOCK_DATE = ' || P_Loan_Details_Rec.LOCK_DATE);
542 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOCK_EXP_DATE = ' || P_Loan_Details_Rec.LOCK_EXP_DATE);
543
544 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REAMORTIZE_OVER_PAYMENT = ' || P_Loan_Details_Rec.REAMORTIZE_OVER_PAYMENT);
545 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DELINQUENCY_THRESHOLD_AMOUNT = ' || P_Loan_Details_Rec.DELINQUENCY_THRESHOLD_AMOUNT);
546 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DEFAULT_THRESHOLD_AMOUNT = ' || P_Loan_Details_Rec.DEFAULT_THRESHOLD_AMOUNT);
547 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_APPLICATION_ORDER = ' || P_Loan_Details_Rec.PAYMENT_APPLICATION_ORDER);
548
549 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_term = ' || P_Loan_Details_Rec.loan_term);
550 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_term_period = ' || P_Loan_Details_Rec.loan_term_period);
551 LogMessage(FND_LOG.LEVEL_STATEMENT, 'balloon_payment_type = ' || P_Loan_Details_Rec.balloon_payment_type);
552 LogMessage(FND_LOG.LEVEL_STATEMENT, 'balloon_payment_amount = ' || P_Loan_Details_Rec.balloon_payment_amount);
553 LogMessage(FND_LOG.LEVEL_STATEMENT, 'balloon_term = ' || P_Loan_Details_Rec.balloon_term);
554 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_start_date = ' || P_Loan_Details_Rec.loan_start_date);
555 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FIRST_PAYMENT_DATE = ' || P_Loan_Details_Rec.FIRST_PAYMENT_DATE);
556 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIN_FIRST_PAY_DATE = ' || P_Loan_Details_Rec.PRIN_FIRST_PAY_DATE);
557 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIN_PAYMENT_FREQUENCY = ' || P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY);
558 LogMessage(FND_LOG.LEVEL_STATEMENT, 'floor_rate = ' || P_Loan_Details_Rec.floor_rate);
559 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ceiling_rate = ' || P_Loan_Details_Rec.ceiling_rate);
560 LogMessage(FND_LOG.LEVEL_STATEMENT, 'percent_increase = ' || P_Loan_Details_Rec.percent_increase);
561 LogMessage(FND_LOG.LEVEL_STATEMENT, 'percent_increase_life = ' || P_Loan_Details_Rec.percent_increase_life);
562
563 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_loan_term = ' || P_Loan_Details_Rec.open_loan_term);
564 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_loan_term_period = ' || P_Loan_Details_Rec.open_loan_term_period);
565 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_loan_start_date = ' || P_Loan_Details_Rec.open_loan_start_date);
566 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_to_term_event = ' || P_Loan_Details_Rec.open_to_term_event);
567 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_floor_rate = ' || P_Loan_Details_Rec.open_floor_rate);
568 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_ceiling_rate = ' || P_Loan_Details_Rec.open_ceiling_rate);
569 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_percent_increase = ' || P_Loan_Details_Rec.open_percent_increase);
570 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_percent_increase_life = ' || P_Loan_Details_Rec.open_percent_increase_life);
571
572 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE_CATEGORY = ' || P_Loan_Details_Rec.ATTRIBUTE_CATEGORY);
573 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE1 = ' || P_Loan_Details_Rec.ATTRIBUTE1);
574 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE2 = ' || P_Loan_Details_Rec.ATTRIBUTE2);
575 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE3 = ' || P_Loan_Details_Rec.ATTRIBUTE3);
576 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE4 = ' || P_Loan_Details_Rec.ATTRIBUTE4);
577 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE5 = ' || P_Loan_Details_Rec.ATTRIBUTE5);
578 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE6 = ' || P_Loan_Details_Rec.ATTRIBUTE6);
579 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE7 = ' || P_Loan_Details_Rec.ATTRIBUTE7);
580 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE8 = ' || P_Loan_Details_Rec.ATTRIBUTE8);
581 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE9 = ' || P_Loan_Details_Rec.ATTRIBUTE9);
582 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE10 = ' || P_Loan_Details_Rec.ATTRIBUTE10);
583 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE11 = ' || P_Loan_Details_Rec.ATTRIBUTE11);
584 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE12 = ' || P_Loan_Details_Rec.ATTRIBUTE12);
585 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE13 = ' || P_Loan_Details_Rec.ATTRIBUTE13);
586 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE14 = ' || P_Loan_Details_Rec.ATTRIBUTE14);
587 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE15 = ' || P_Loan_Details_Rec.ATTRIBUTE15);
588 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE16 = ' || P_Loan_Details_Rec.ATTRIBUTE16);
589 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE17 = ' || P_Loan_Details_Rec.ATTRIBUTE17);
590 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE18 = ' || P_Loan_Details_Rec.ATTRIBUTE18);
591 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE19 = ' || P_Loan_Details_Rec.ATTRIBUTE19);
592 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE20 = ' || P_Loan_Details_Rec.ATTRIBUTE20);
593
594 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating loan_assigned_to...');
595
596 -- Validate loan_assigned_to
597 IF P_Loan_Details_Rec.loan_assigned_to IS NULL THEN
598 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
599 ,p_token1=>'P_Loan_Details_Rec.loan_assigned_to');
600 ELSE
601 BEGIN
602 SELECT 'Y'
603 INTO l_dummy
604 FROM jtf_rs_resource_extns res
605 WHERE res.resource_id = P_Loan_Details_Rec.loan_assigned_to
606 AND res.category = 'EMPLOYEE'
607 AND res.start_date_active <= SYSDATE
608 AND (res.end_date_active is null or res.end_date_active >= SYSDATE);
609 EXCEPTION
610 WHEN NO_DATA_FOUND THEN
611 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
612 ,p_token1=>'P_Loan_Details_Rec.loan_assigned_to'
613 ,p_token2=>P_Loan_Details_Rec.loan_assigned_to);
614 END;
615 END IF;
616
617 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating legal_entity_id...');
618
619 -- Validate Legal Entity Id
620 P_Loan_Details_Rec.legal_entity_id :=
621 CASE l_override_rec.legal_entity_id_ovr
622 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.legal_entity_id, l_product_rec.legal_entity_id)
623 ELSE l_product_rec.legal_entity_id
624 END;
625
626 IF P_Loan_Details_Rec.legal_entity_id IS NULL THEN
627 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
628 ,p_token1=>'P_Loan_Details_Rec.legal_entity_id');
629 ELSIF P_Loan_Details_Rec.legal_entity_id IS NOT NULL THEN
630 BEGIN
631 SELECT 'Y'
632 INTO l_dummy
633 FROM xle_entity_profiles xep
634 WHERE xep.legal_entity_id = P_Loan_Details_Rec.legal_entity_id;
635 EXCEPTION
636 WHEN NO_DATA_FOUND THEN
637 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
638 ,p_token1=>'P_Loan_Details_Rec.legal_entity_id'
639 ,p_token2=>P_Loan_Details_Rec.legal_entity_id);
640 END;
641 END IF;
642
643 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating requested_amount...');
644
645 -- Validate Requested Amount
646 P_Loan_Details_Rec.requested_amount :=
647 CASE l_product_rec.loan_class_code
648 WHEN 'DIRECT' THEN nvl(P_Loan_Details_Rec.requested_amount, l_product_rec.requested_amount)
649 WHEN 'ERS' THEN 0
650 END;
651
652 IF l_product_rec.loan_class_code <> 'ERS' AND
653 (P_Loan_Details_Rec.requested_amount is null OR
654 P_Loan_Details_Rec.requested_amount = 0 OR
655 P_Loan_Details_Rec.requested_amount < nvl(l_product_rec.requested_amount, P_Loan_Details_Rec.requested_amount) OR
656 P_Loan_Details_Rec.requested_amount > nvl(l_product_rec.max_requested_amount, P_Loan_Details_Rec.requested_amount))
657 THEN
658 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_BETWEEN'
659 ,p_token1=>'P_Loan_Details_Rec.requested_amount'
660 ,p_token2=>l_product_rec.max_requested_amount
661 ,p_token3=>l_product_rec.requested_amount);
662 END IF;
663
664 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating loan_purpose_code...');
665
666 -- Validate Loan Purpose Code
667 if P_Loan_Details_Rec.loan_purpose_code is null then
668 P_Loan_Details_Rec.loan_purpose_code := 'OTHER';
669 end if;
670
671 IF P_Loan_Details_Rec.loan_purpose_code IS NOT NULL THEN
672 BEGIN
673 SELECT 'Y'
674 INTO l_dummy
675 FROM lns_lookups llk
676 WHERE llk.lookup_code = P_Loan_Details_Rec.loan_purpose_code
677 AND llk.lookup_type = 'LOAN_PURPOSE';
678 EXCEPTION
679 WHEN NO_DATA_FOUND THEN
680 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
681 ,p_token1=>'P_Loan_Details_Rec.loan_purpose_code'
682 ,p_token2=>P_Loan_Details_Rec.loan_purpose_code);
683 END;
684 END IF;
685
686 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating credit_review_flag...');
687
688 if P_Loan_Details_Rec.credit_review_flag is null then
689 P_Loan_Details_Rec.credit_review_flag := l_product_rec.credit_review_flag;
690 end if;
691
692 -- Validate Credit Review Flag
693 IF P_Loan_Details_Rec.credit_review_flag IS NOT NULL
694 AND P_Loan_Details_Rec.credit_review_flag NOT IN ('Y','N')
695 THEN
696 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
697 ,p_token1=>'P_Loan_Details_Rec.credit_review_flag'
698 ,p_token2=>P_Loan_Details_Rec.credit_review_flag);
699 END IF;
700
701 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating currency...');
702
703 -- Validate Currency
704 SELECT glsob.currency_code
705 INTO l_functional_currency
706 FROM gl_sets_of_books glsob,
707 lns_system_options_all lso
708 WHERE glsob.set_of_books_id = lso.set_of_books_id
709 AND lso.org_id = l_product_rec.org_id;
710
711 IF l_functional_currency <> l_product_rec.loan_currency THEN
712 l_exchange_required := 'Y';
713 END IF;
714
715 IF l_exchange_required = 'Y' THEN
716
717 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating exchange_rate_type and exchange_date...');
718 IF P_Loan_Details_Rec.exchange_rate_type IS NULL THEN
719 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
720 ,p_token1=>'P_Loan_Details_Rec.exchange_rate_type');
721 ELSE
722 BEGIN
723 SELECT 'Y'
724 INTO l_dummy
725 FROM gl_daily_conversion_types gdct
726 WHERE gdct.conversion_type = P_Loan_Details_Rec.exchange_rate_type;
727
728 EXCEPTION
729 WHEN no_data_found THEN
730 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
731 ,p_token1=>'P_Loan_Details_Rec.exchange_rate_type'
732 ,p_token2=>P_Loan_Details_Rec.exchange_rate_type);
733 END;
734 END IF;
735
736 IF P_Loan_Details_Rec.exchange_date IS NULL THEN
737 LogErrors(p_message_name => 'LNS_LCREATE_NULL_VALUE', p_token1 => 'P_Loan_Details_Rec.exchange_date');
738 END IF;
739
740 IF UPPER(P_Loan_Details_Rec.exchange_rate_type) = 'USER' THEN
741 IF P_Loan_Details_Rec.exchange_rate IS NULL THEN
742 LogErrors(p_message_name => 'LNS_LCREATE_NULL_VALUE', p_token1 => 'P_Loan_Details_Rec.exchange_rate');
743 END IF;
744 ELSE
745 P_Loan_Details_Rec.exchange_rate := LNS_UTILITY_PUB.CONVERTRATE(l_functional_currency
746 ,l_product_rec.loan_currency
747 ,P_Loan_Details_Rec.exchange_date
748 ,P_Loan_Details_Rec.exchange_rate_type
749 );
750 IF P_Loan_Details_Rec.exchange_rate IS NULL THEN
751 LogErrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
752 , p_token1 => 'P_Loan_Details_Rec.exchange_date'
753 , p_token2 => P_Loan_Details_Rec.exchange_rate);
754
755 END IF;
756 END IF; -- End of exchange_rate_type is equal to USER
757 END IF; -- End of l_exchange_required
758
759 -- Validate Trx Type Id
760 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating trx_type_id...');
761
762 -- If ERS and value is null log error
763 IF P_Loan_Details_Rec.trx_type_id IS NULL AND l_product_rec.loan_class_code = 'ERS' THEN
764 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
765 ,p_token1=>'P_Loan_Details_Rec.trx_type_id');
766 ELSIF P_Loan_Details_Rec.trx_type_id IS NOT NULL AND l_product_rec.loan_class_code = 'ERS' THEN
767 BEGIN
768 SELECT 'Y'
769 INTO l_dummy
770 FROM ar_lookups alk
771 ,RA_CUST_TRX_TYPES_ALL rtyp
772 WHERE rtyp.CUST_TRX_TYPE_ID = P_Loan_Details_Rec.trx_type_id
773 AND alk.lookup_code = rtyp.type
774 AND alk.lookup_type = 'INV/CM';
775 EXCEPTION
776 WHEN NO_DATA_FOUND THEN
777 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
778 ,p_token1=>'P_Loan_Details_Rec.trx_type_id'
779 ,p_token2=>P_Loan_Details_Rec.trx_type_id);
780 END;
781 END IF;
782
783 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating loan_subtype...');
784
785 P_Loan_Details_Rec.loan_subtype :=
786 CASE l_override_rec.loan_subtype_ovr
787 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.loan_subtype, l_product_rec.loan_subtype)
788 ELSE l_product_rec.loan_subtype
789 END;
790
791 -- Validate Loan Sub Type
792 IF P_Loan_Details_Rec.loan_subtype IS NOT NULL THEN
793 BEGIN
794 SELECT 'Y'
795 INTO l_dummy
796 FROM lns_lookups llk
797 WHERE llk.lookup_code = P_Loan_Details_Rec.loan_subtype
798 AND llk.lookup_type = 'LOAN_SUBTYPE';
799 EXCEPTION
800 WHEN NO_DATA_FOUND THEN
801 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
802 ,p_token1=>'P_Loan_Details_Rec.loan_subtype'
803 ,p_token2=>P_Loan_Details_Rec.loan_subtype);
804 END;
805 END IF;
806
807 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating collateral_percent...');
808
809 P_Loan_Details_Rec.collateral_percent :=
810 CASE l_override_rec.COLLATERAL_PERCENT_OVR
811 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.collateral_percent, l_product_rec.collateral_percent)
812 ELSE l_product_rec.collateral_percent
813 END;
814
815 -- Validate Collateral Percent
816 IF (P_Loan_Details_Rec.loan_subtype = 'SECURED' AND P_Loan_Details_Rec.collateral_percent IS NULL) THEN
817 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
818 ,p_token1=>'P_Loan_Details_Rec.collateral_percent');
819 ELSIF P_Loan_Details_Rec.loan_subtype = 'SECURED' AND
820 (P_Loan_Details_Rec.collateral_percent <= 0 OR P_Loan_Details_Rec.collateral_percent > 100)
821 THEN
822 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_COLLPERC');
823 END IF;
824
825 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating loan_term_period...');
826
827 -- Validate Loan Term Period
828 if P_Loan_Details_Rec.loan_term_period is null then
829 P_Loan_Details_Rec.loan_term_period := l_product_rec.loan_term_period;
830 end if;
831
832 IF P_Loan_Details_Rec.loan_term_period IS NULL THEN
833 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
834 ,p_token1=>'P_Loan_Details_Rec.loan_term_period');
835 ELSE
836 BEGIN
837 SELECT 'Y'
838 INTO l_dummy
839 FROM lns_lookups llk
840 WHERE llk.lookup_code = P_Loan_Details_Rec.loan_term_period
841 AND llk.lookup_type = 'PERIOD';
842 EXCEPTION
843 WHEN NO_DATA_FOUND THEN
844 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
845 ,p_token1=>'P_Loan_Details_Rec.loan_term_period'
846 ,p_token2=>P_Loan_Details_Rec.loan_term_period);
847 END;
848 END IF;
849
850 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating loan_term...');
851
852 -- Validate Loan Term
853 if P_Loan_Details_Rec.loan_term is null then
854 P_Loan_Details_Rec.loan_term := l_product_rec.loan_term;
855 end if;
856
857 IF P_Loan_Details_Rec.loan_term is NOT NULL THEN
858 IF ((P_Loan_Details_Rec.loan_term > 999) OR
859 (P_Loan_Details_Rec.loan_term < 0) OR
860 (round(P_Loan_Details_Rec.loan_term) <> P_Loan_Details_Rec.loan_term))
861 THEN
862 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_BETWEEN'
863 ,p_token1=>'P_Loan_Details_Rec.loan_term = '||P_Loan_Details_Rec.loan_term||' should be a whole number and '
864 ,p_token2=>' 0 '
865 ,p_token3=>' 999 '
866 );
867 END IF;
868 ELSIF P_Loan_Details_Rec.loan_term is NOT NULL
869 AND P_Loan_Details_Rec.loan_term_period is NOT NULL
870 AND l_product_rec.max_loan_term is NOT NULL
871 AND ((LNS_FIN_UTILS.intervalsinperiod(P_Loan_Details_Rec.loan_term
872 ,P_Loan_Details_Rec.loan_term_period
873 ,'DAYS')
874 < LNS_FIN_UTILS.intervalsinperiod(l_product_rec.loan_term
875 ,l_product_rec.loan_term_period
876 ,'DAYS')) OR
877 (LNS_FIN_UTILS.intervalsinperiod(P_Loan_Details_Rec.loan_term
878 ,P_Loan_Details_Rec.loan_term_period
879 ,'DAYS')
880 > LNS_FIN_UTILS.intervalsinperiod(l_product_rec.max_loan_term
881 ,l_product_rec.max_loan_term_period
882 ,'DAYS')))
883 THEN
884 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_BETWEEN'
885 ,p_token1 => '(P_Loan_Details_Rec.loan_term P_Loan_Details_Rec.loan_term_period)'
886 ,p_token2=>l_product_rec.loan_term || ' ' || l_product_rec.loan_term_period
887 ,p_token3=>l_product_rec.max_loan_term || ' ' || l_product_rec.max_loan_term_period);
888 ELSIF P_Loan_Details_Rec.loan_term is NOT NULL
889 AND P_Loan_Details_Rec.loan_term_period is NOT NULL
890 AND l_product_rec.max_loan_term is NULL
891 AND (LNS_FIN_UTILS.intervalsinperiod(P_Loan_Details_Rec.loan_term
892 ,P_Loan_Details_Rec.loan_term_period
893 ,'DAYS')
894 < LNS_FIN_UTILS.intervalsinperiod(l_product_rec.loan_term
895 ,l_product_rec.loan_term_period
896 ,'DAYS'))
897 THEN
898 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_GREATER'
899 ,p_token1=>'(P_Loan_Details_Rec.loan_term P_Loan_Details_Rec.loan_term_period)'
900 ,p_token2=>l_product_rec.loan_term || ' ' || l_product_rec.loan_term_period);
901 ELSIF P_Loan_Details_Rec.loan_term is NULL
902 THEN
903 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
904 ,p_token1=>'P_Loan_Details_Rec.loan_term');
905 END IF;
906
907 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating balloon_payment_type...');
908
909 -- Validate Payment Type
910 if P_Loan_Details_Rec.balloon_payment_type is null then
911 P_Loan_Details_Rec.balloon_payment_type := 'TERM';
912 end if;
913
914 BEGIN
915 SELECT 'Y'
916 INTO l_dummy
917 FROM lns_lookups llk
918 WHERE llk.lookup_code = P_Loan_Details_Rec.balloon_payment_type
919 AND llk.lookup_type = 'BALLOON_PAYMENT_TYPE';
920 EXCEPTION
921 WHEN NO_DATA_FOUND THEN
922 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
923 ,p_token1=>'P_Loan_Details_Rec.balloon_payment_type'
924 ,p_token2=>P_Loan_Details_Rec.balloon_payment_type);
925 END;
926
927 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating balloon_term/balloon_payment_amount...');
928
929 -- Validate Balloon Payment Amount / Balloon Term
930 if P_Loan_Details_Rec.balloon_payment_amount is null then
931 P_Loan_Details_Rec.balloon_payment_amount := 0;
932 end if;
933
934 if P_Loan_Details_Rec.balloon_term is null then
935 P_Loan_Details_Rec.balloon_term := P_Loan_Details_Rec.loan_term;
936 end if;
937
938 IF (P_Loan_Details_Rec.balloon_payment_type = 'TERM' AND P_Loan_Details_Rec.balloon_term IS NULL) THEN
939 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
940 ,p_token1=>'P_Loan_Details_Rec.balloon_term');
941
942 ELSIF (P_Loan_Details_Rec.balloon_payment_type = 'TERM' AND P_Loan_Details_Rec.balloon_term IS NOT NULL) THEN
943 IF ((P_Loan_Details_Rec.balloon_term > 999) OR
944 (P_Loan_Details_Rec.balloon_term < 0) OR
945 (round(P_Loan_Details_Rec.balloon_term) <> P_Loan_Details_Rec.balloon_term))
946 THEN
947 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_BETWEEN'
948 ,p_token1=>'P_Loan_Details_Rec.balloon_term = '||P_Loan_Details_Rec.balloon_term||' should be a whole number and '
949 ,p_token2=>' 0 '
950 ,p_token3=>' 999 '
951 );
952 ELSIF (P_Loan_Details_Rec.loan_term > P_Loan_Details_Rec.balloon_term) THEN
953 LogErrors(p_message_name=>'LNS_LOAN_TERM_INVALID'
954 ,p_token1=>'P_Loan_Details_Rec.loan_term = '||P_Loan_Details_Rec.loan_term
955 ,p_token2=>'P_Loan_Details_Rec.balloon_term = '||P_Loan_Details_Rec.balloon_term);
956 END IF;
957 ELSIF (P_Loan_Details_Rec.balloon_payment_type = 'AMOUNT' AND P_Loan_Details_Rec.balloon_payment_amount IS NULL) THEN
958 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
959 ,p_token1=>'P_Loan_Details_Rec.balloon_payment_amount');
960
961 ELSIF (P_Loan_Details_Rec.balloon_payment_type = 'AMOUNT' AND P_Loan_Details_Rec.balloon_payment_amount IS NOT NULL) THEN
962
963 IF(P_Loan_Details_Rec.balloon_payment_amount > P_Loan_Details_Rec.requested_amount) THEN
964 LogErrors(p_message_name=>'LNS_BALLOON_AMOUNT_INVALID'
965 ,p_token1=>'P_Loan_Details_Rec.balloon_payment_amount = '||P_Loan_Details_Rec.balloon_payment_amount
966 ,p_token2=>'P_Loan_Details_Rec.requested_amount = '||P_Loan_Details_Rec.requested_amount);
967 END IF;
968 END IF;
969
970 l_borrower_valid := FALSE;
971 l_cust_acct_valid := FALSE;
972
973 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating primary_borrower_party_id...');
974
975 -- Validate primary_borrower_party_id
976 IF P_Loan_Details_Rec.primary_borrower_party_id IS NULL THEN
977 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
978 ,p_token1=>'P_Loan_Details_Rec.primary_borrower_party_id');
979 ELSE
980 BEGIN
981 SELECT 'Y'
982 INTO l_dummy
983 FROM hz_parties hzp
984 WHERE hzp.party_id = P_Loan_Details_Rec.primary_borrower_party_id
985 AND hzp.party_type = l_product_rec.party_type
986 AND hzp.status = 'A';
987 l_borrower_valid := TRUE;
988 EXCEPTION
989 WHEN NO_DATA_FOUND THEN
990 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
991 ,p_token1=>'P_Loan_Details_Rec.primary_borrower_party_id'
992 ,p_token2=>P_Loan_Details_Rec.primary_borrower_party_id);
993 END;
994 END IF;
995
996 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating cust_account_id...');
997
998 -- Validate cust_account_id
999 IF l_borrower_valid THEN
1000
1001 IF P_Loan_Details_Rec.cust_account_id IS NULL THEN
1002 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
1003 ,p_token1=>'P_Loan_Details_Rec.cust_account_id');
1004 ELSE
1005 BEGIN
1006 SELECT 'Y'
1007 INTO l_dummy
1008 FROM hz_cust_accounts_all hzca
1009 WHERE hzca.cust_account_id = P_Loan_Details_Rec.cust_account_id
1010 AND hzca.party_id = P_Loan_Details_Rec.primary_borrower_party_id
1011 AND hzca.status = 'A';
1012 l_cust_acct_valid := TRUE;
1013 EXCEPTION
1014 WHEN NO_DATA_FOUND THEN
1015 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1016 ,p_token1=>'P_Loan_Details_Rec.cust_account_id'
1017 ,p_token2=>P_Loan_Details_Rec.cust_account_id);
1018 END;
1019 END IF;
1020
1021 END IF;
1022
1023 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating bill_to_acct_site_id...');
1024
1025 -- Validate bill_to_acct_site_id
1026 IF l_cust_acct_valid THEN
1027
1028 IF P_Loan_Details_Rec.bill_to_acct_site_id IS NULL THEN
1029 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
1030 ,p_token1=>'P_Loan_Details_Rec.bill_to_acct_site_id');
1031 ELSE
1032 BEGIN
1033 SELECT 'Y'
1034 INTO l_dummy
1035 FROM hz_cust_accounts_all account,
1036 hz_cust_site_uses acc_site_use,
1037 hz_cust_acct_sites_all acct_site
1038 WHERE account.cust_account_id = acct_site.cust_account_id
1039 AND acc_site_use.cust_acct_site_id = acct_site.cust_acct_site_id
1040 AND acc_site_use.site_use_code = 'BILL_TO'
1041 AND acct_site.cust_acct_site_id = P_Loan_Details_Rec.bill_to_acct_site_id
1042 AND acct_site.cust_account_id = P_Loan_Details_Rec.cust_account_id
1043 AND acc_site_use.status = 'A';
1044 EXCEPTION
1045 WHEN NO_DATA_FOUND THEN
1046 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1047 ,p_token1=>'P_Loan_Details_Rec.bill_to_acct_site_id'
1048 ,p_token2=>P_Loan_Details_Rec.bill_to_acct_site_id);
1049 END;
1050 END IF;
1051
1052 END IF;
1053
1054 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating contact_rel_party_id...');
1055
1056 IF l_borrower_valid THEN
1057
1058 IF P_Loan_Details_Rec.contact_rel_party_id IS NOT NULL THEN
1059 BEGIN
1060 SELECT
1061 p.party_id INTO l_contact_pers_party_id
1062 FROM
1063 hz_relationships rel,
1064 hz_parties p,
1065 hz_parties o,
1066 hz_parties rel_party,
1067 ar_lookups lkup,
1068 hz_relationship_types rel_type,
1069 hz_contact_points con_phone
1070 WHERE o.party_type = 'ORGANIZATION'
1071 AND p.party_type = 'PERSON'
1072 AND rel.subject_id = p.party_id
1073 AND rel.object_id = o.party_id
1074 AND rel.relationship_code = rel_type.forward_rel_code
1075 AND rel_type.create_party_flag = 'Y'
1076 AND rel_type.subject_type = 'PERSON'
1077 AND rel_type.object_type = 'ORGANIZATION'
1078 AND rel.relationship_type = rel_type.relationship_type
1079 AND rel_type.role = lkup.lookup_code
1080 AND lkup.lookup_type = 'HZ_RELATIONSHIP_ROLE'
1081 AND rel.party_id = rel_party.party_id
1082 AND rel.status = 'A'
1083 AND rel_party.status = 'A'
1084 AND p.status = 'A'
1085 AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
1086 AND con_phone.owner_table_id(+) = rel.party_id
1087 AND con_phone.primary_flag(+) = 'Y'
1088 AND con_phone.status(+) = 'A'
1089 AND con_phone.contact_point_type(+) = 'PHONE'
1090 AND rel.party_id = P_Loan_Details_Rec.contact_rel_party_id;
1091 l_valid_contact_rel := 'Y';
1092 EXCEPTION
1093 WHEN no_data_found THEN
1094 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
1095 p_token1 => 'L_LOAN_DTL_REC.contact_rel_party_id',
1096 p_token2 => P_Loan_Details_Rec.contact_rel_party_id);
1097 END;
1098 END IF;
1099
1100 IF l_valid_contact_rel = 'Y' THEN
1101 IF P_Loan_Details_Rec.contact_pers_party_id IS NULL THEN
1102 P_Loan_Details_Rec.contact_pers_party_id := l_contact_pers_party_id;
1103 l_valid_contact_pers := 'Y';
1104 ELSIF l_contact_pers_party_id = P_Loan_Details_Rec.contact_pers_party_id THEN
1105 l_valid_contact_pers := 'Y';
1106 END IF;
1107 END IF;
1108
1109 IF ((P_Loan_Details_Rec.contact_pers_party_id IS NOT NULL) AND (l_valid_contact_pers <> 'Y')) THEN
1110 BEGIN
1111 SELECT
1112 rel.party_id INTO l_contact_rel_party_id
1113 FROM
1114 hz_relationships rel,
1115 hz_parties p,
1116 hz_parties o,
1117 hz_parties rel_party,
1118 ar_lookups lkup,
1119 hz_relationship_types rel_type,
1120 hz_contact_points con_phone
1121 WHERE o.party_type = 'ORGANIZATION'
1122 AND p.party_type = 'PERSON'
1123 AND rel.subject_id = p.party_id
1124 AND rel.object_id = o.party_id
1125 AND rel.relationship_code = rel_type.forward_rel_code
1126 AND rel_type.create_party_flag = 'Y'
1127 AND rel_type.subject_type = 'PERSON'
1128 AND rel_type.object_type = 'ORGANIZATION'
1129 AND rel.relationship_type = rel_type.relationship_type
1130 AND rel_type.role = lkup.lookup_code
1131 AND lkup.lookup_type = 'HZ_RELATIONSHIP_ROLE'
1132 AND rel.party_id = rel_party.party_id
1133 AND rel.status = 'A'
1134 AND rel_party.status = 'A'
1135 AND p.status = 'A'
1136 AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
1137 AND con_phone.owner_table_id(+) = rel.party_id
1138 AND con_phone.primary_flag(+) = 'Y'
1139 AND con_phone.status(+) = 'A'
1140 AND con_phone.contact_point_type(+) = 'PHONE'
1141 AND p.party_id = P_Loan_Details_Rec.contact_pers_party_id;
1142 l_valid_contact_pers := 'Y';
1143 EXCEPTION
1144 WHEN no_data_found THEN
1145 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
1146 p_token1 => 'P_Loan_Details_Rec.contact_pers_party_id',
1147 p_token2 => P_Loan_Details_Rec.contact_pers_party_id);
1148 END;
1149 END IF;
1150
1151 IF l_valid_contact_pers = 'Y' THEN
1152 IF P_Loan_Details_Rec.contact_rel_party_id IS NULL THEN
1153 l_contact_rel_party_id := P_Loan_Details_Rec.contact_rel_party_id;
1154 l_valid_contact_rel := 'Y';
1155 ELSIF l_contact_rel_party_id = P_Loan_Details_Rec.contact_rel_party_id THEN
1156 l_valid_contact_rel := 'Y';
1157 END IF;
1158 END IF;
1159
1160 -- Only if bothe Contact (rel and Person) are not NULL and individually both are correct but incorrect when combined
1161 IF (((P_Loan_Details_Rec.contact_rel_party_id IS NOT NULL) AND (P_Loan_Details_Rec.contact_pers_party_id IS NOT NULL)) AND
1162 ((l_valid_contact_rel <> 'Y') OR (l_valid_contact_pers <> 'Y'))) THEN
1163 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
1164 p_token1 => 'P_Loan_Details_Rec.contact_pers_party_id',
1165 p_token2 => P_Loan_Details_Rec.contact_pers_party_id);
1166 END IF;
1167
1168 END IF; -- If the l_borrower_valid
1169
1170 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating RATE_TYPE...');
1171
1172 P_Loan_Details_Rec.RATE_TYPE :=
1173 CASE l_override_rec.RATE_TYPE_OVR
1174 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.RATE_TYPE, l_product_rec.RATE_TYPE)
1175 ELSE l_product_rec.RATE_TYPE
1176 END;
1177
1178 IF P_Loan_Details_Rec.RATE_TYPE IS NOT NULL THEN
1179 BEGIN
1180 SELECT 'Y'
1181 INTO l_dummy
1182 FROM lns_lookups llk
1183 WHERE llk.lookup_code = P_Loan_Details_Rec.RATE_TYPE
1184 AND llk.lookup_type = 'RATE_TYPE'
1185 AND enabled_flag = 'Y';
1186
1187 EXCEPTION
1188 WHEN no_data_found THEN
1189 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
1190 p_token1 => 'P_Loan_Details_Rec.rate_type and ',
1191 p_token2 => P_Loan_Details_Rec.rate_type);
1192 END;
1193 END IF;
1194
1195 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating index_rate_id...');
1196
1197 -- Validate Index Rate Id
1198 P_Loan_Details_Rec.index_rate_id :=
1199 CASE l_override_rec.index_rate_id_ovr
1200 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.index_rate_id, l_product_rec.index_rate_id)
1201 ELSE l_product_rec.index_rate_id
1202 END;
1203
1204 IF P_Loan_Details_Rec.index_rate_id IS NOT NULL THEN
1205 BEGIN
1206 SELECT 'Y'
1207 INTO l_dummy
1208 FROM lns_int_rate_headers lirh
1209 WHERE lirh.interest_rate_id = P_Loan_Details_Rec.index_rate_id;
1210 EXCEPTION
1211 WHEN NO_DATA_FOUND THEN
1212 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1213 ,p_token1=>'P_Loan_Details_Rec.index_rate_id'
1214 ,p_token2=>P_Loan_Details_Rec.index_rate_id);
1215 END;
1216 END IF;
1217
1218 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating loan_payment_frequency...');
1219
1220 -- Validate loan_payment_frequency
1221 P_Loan_Details_Rec.loan_payment_frequency :=
1222 CASE l_override_rec.loan_payment_freq_ovr
1223 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.loan_payment_frequency, l_product_rec.loan_payment_frequency)
1224 ELSE l_product_rec.loan_payment_frequency
1225 END;
1226
1227 IF P_Loan_Details_Rec.loan_payment_frequency IS NOT NULL THEN
1228 BEGIN
1229 SELECT 'Y'
1230 INTO l_dummy
1231 FROM lns_lookups llk
1232 WHERE llk.lookup_code = P_Loan_Details_Rec.loan_payment_frequency
1233 AND llk.lookup_type = 'FREQUENCY';
1234 EXCEPTION
1235 WHEN NO_DATA_FOUND THEN
1236 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1237 ,p_token1=>'P_Loan_Details_Rec.loan_payment_frequency'
1238 ,p_token2=>P_Loan_Details_Rec.loan_payment_frequency);
1239 END;
1240 END IF;
1241
1242 LogMessage(fnd_log.level_statement, 'Validating Day Count Method...');
1243
1244 -- Validate Day Count Method
1245 P_Loan_Details_Rec.day_count_method :=
1246 CASE l_override_rec.day_count_method_ovr
1247 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.day_count_method, l_product_rec.day_count_method)
1248 ELSE l_product_rec.day_count_method
1249 END;
1250
1251 IF P_Loan_Details_Rec.day_count_method IS NOT NULL THEN
1252 BEGIN
1253 SELECT 'Y'
1254 INTO l_dummy
1255 FROM lns_lookups llk
1256 WHERE llk.lookup_code = P_Loan_Details_Rec.day_count_method
1257 AND llk.lookup_type = 'DAY_COUNT_METHOD'
1258 AND enabled_flag = 'Y';
1259 EXCEPTION
1260 WHEN no_data_found THEN
1261 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1262 ,p_token1=>'P_Loan_Details_Rec.day_count_method'
1263 ,p_token2=>P_Loan_Details_Rec.day_count_method);
1264 END;
1265 END IF;
1266
1267 logmessage(fnd_log.level_statement, 'Validating PAYMENT_CALC_METHOD');
1268
1269 P_Loan_Details_Rec.PAYMENT_CALC_METHOD :=
1270 CASE l_override_rec.PAYMENT_CALC_METHOD_ovr
1271 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.PAYMENT_CALC_METHOD, l_product_rec.PAYMENT_CALC_METHOD)
1272 ELSE l_product_rec.PAYMENT_CALC_METHOD
1273 END;
1274
1275 IF P_Loan_Details_Rec.PAYMENT_CALC_METHOD IS NULL THEN
1276 P_Loan_Details_Rec.PAYMENT_CALC_METHOD := 'EQUAL_PAYMENT';
1277 logmessage(fnd_log.level_statement, 'Defaulting PAYMENT_CALC_METHOD to ' || P_Loan_Details_Rec.PAYMENT_CALC_METHOD);
1278 ELSE
1279 BEGIN
1280 SELECT 'Y'
1281 INTO l_dummy
1282 FROM lns_lookups llk
1283 WHERE llk.lookup_code = P_Loan_Details_Rec.PAYMENT_CALC_METHOD
1284 AND llk.lookup_type = 'PAYMENT_CALCULATION_METHOD';
1285
1286 EXCEPTION
1287 WHEN no_data_found THEN
1288 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1289 ,p_token1=>'P_Loan_Details_Rec.PAYMENT_CALC_METHOD'
1290 ,p_token2=>P_Loan_Details_Rec.PAYMENT_CALC_METHOD);
1291 END;
1292 END IF;
1293
1294 IF P_Loan_Details_Rec.PAYMENT_CALC_METHOD = 'CUSTOM' THEN
1295 P_Loan_Details_Rec.custom_payments_flag := 'Y';
1296 END IF;
1297
1298 LogMessage(fnd_log.level_statement, 'Validating CALCULATION_METHOD...');
1299
1300 -- Validate CALCULATION_METHOD
1301 P_Loan_Details_Rec.CALCULATION_METHOD :=
1302 CASE l_override_rec.calculation_method_ovr
1303 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.CALCULATION_METHOD, l_product_rec.CALCULATION_METHOD)
1304 ELSE l_product_rec.CALCULATION_METHOD
1305 END;
1306
1307 IF P_Loan_Details_Rec.CALCULATION_METHOD IS NOT NULL THEN
1308 BEGIN
1309 SELECT 'Y'
1310 INTO l_dummy
1311 FROM lns_lookups llk
1312 WHERE llk.lookup_code = P_Loan_Details_Rec.CALCULATION_METHOD
1313 AND llk.lookup_type = 'INTEREST_CALCULATION_METHOD';
1314 EXCEPTION
1315 WHEN no_data_found THEN
1316 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1317 ,p_token1=>'P_Loan_Details_Rec.CALCULATION_METHOD'
1318 ,p_token2=>P_Loan_Details_Rec.CALCULATION_METHOD);
1319 END;
1320 END IF;
1321
1322 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating INTEREST_COMPOUNDING_FREQ...');
1323
1324 -- Validate INTEREST_COMPOUNDING_FREQ
1325 P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ :=
1326 CASE l_override_rec.INTEREST_COMPOUNDING_FREQ_OVR
1327 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ, l_product_rec.INTEREST_COMPOUNDING_FREQ)
1328 ELSE l_product_rec.INTEREST_COMPOUNDING_FREQ
1329 END;
1330
1331 if P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ is null then
1332 P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ := P_Loan_Details_Rec.loan_payment_frequency;
1333 end if;
1334
1335 IF P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ IS NOT NULL THEN
1336 BEGIN
1337 SELECT 'Y'
1338 INTO l_dummy
1339 FROM lns_lookups llk
1340 WHERE llk.lookup_code = P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ
1341 AND llk.lookup_type = 'FREQUENCY';
1342 EXCEPTION
1343 WHEN NO_DATA_FOUND THEN
1344 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1345 ,p_token1=>'P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ'
1346 ,p_token2=>P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ);
1347 END;
1348 END IF;
1349
1350 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating custom_payments_flag...');
1351
1352 IF P_Loan_Details_Rec.custom_payments_flag is null then
1353 P_Loan_Details_Rec.custom_payments_flag := 'N';
1354 end if;
1355
1356 if P_Loan_Details_Rec.custom_payments_flag <> 'Y' and P_Loan_Details_Rec.custom_payments_flag <> 'N' then
1357 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1358 ,p_token1=>'P_Loan_Details_Rec.custom_payments_flag'
1359 ,p_token2=>P_Loan_Details_Rec.custom_payments_flag);
1360 end if;
1361
1362 IF P_Loan_Details_Rec.custom_payments_flag = 'Y' THEN
1363 /*
1364 IF p_loan_cust_sched_tbl.COUNT = 0 THEN
1365 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1366 ,p_token1=>'p_loan_cust_sched_tbl.COUNT'
1367 ,p_token2=>p_loan_cust_sched_tbl.COUNT);
1368 END IF;
1369 */
1370 logmessage(fnd_log.level_statement, 'Validating CUSTOM_CALC_METHOD...');
1371
1372 IF P_Loan_Details_Rec.PAYMENT_CALC_METHOD = 'CUSTOM' THEN
1373 P_Loan_Details_Rec.CUSTOM_CALC_METHOD :=
1374 CASE l_override_rec.CUSTOM_CALC_METHOD_ovr
1375 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.CUSTOM_CALC_METHOD, l_product_rec.CUSTOM_CALC_METHOD)
1376 ELSE l_product_rec.CUSTOM_CALC_METHOD
1377 END;
1378 END IF;
1379
1380 IF P_Loan_Details_Rec.CUSTOM_CALC_METHOD is null THEN
1381 P_Loan_Details_Rec.CUSTOM_CALC_METHOD := 'NONE';
1382 logmessage(fnd_log.level_statement, 'Defaulting CUSTOM_CALC_METHOD to ' || P_Loan_Details_Rec.CUSTOM_CALC_METHOD);
1383 ELSE
1384 BEGIN
1385 SELECT 'Y'
1386 INTO l_dummy
1387 FROM lns_lookups llk
1388 WHERE llk.lookup_code = P_Loan_Details_Rec.CUSTOM_CALC_METHOD
1389 AND llk.lookup_type = 'CUSTOM_CALCULATION_METHOD';
1390
1391 EXCEPTION
1392 WHEN no_data_found THEN
1393 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1394 ,p_token1=>'P_Loan_Details_Rec.CUSTOM_CALC_METHOD'
1395 ,p_token2=>P_Loan_Details_Rec.CUSTOM_CALC_METHOD);
1396 END;
1397 END IF;
1398
1399 logmessage(fnd_log.level_statement, 'Validating ORIG_PAY_CALC_METHOD');
1400
1401 IF P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD IS NULL THEN
1402 P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD := P_Loan_Details_Rec.PAYMENT_CALC_METHOD;
1403 logmessage(fnd_log.level_statement, 'Defaulting ORIG_PAY_CALC_METHOD to ' || P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD);
1404 ELSE
1405 BEGIN
1406 SELECT 'Y'
1407 INTO l_dummy
1408 FROM lns_lookups llk
1409 WHERE llk.lookup_code = P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD
1410 AND llk.lookup_type = 'PAYMENT_CALCULATION_METHOD';
1411
1412 EXCEPTION
1413 WHEN no_data_found THEN
1414 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1415 ,p_token1=>'P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD'
1416 ,p_token2=>P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD);
1417 END;
1418 END IF;
1419 ELSE
1420 P_Loan_Details_Rec.CUSTOM_CALC_METHOD := null;
1421 P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD := null;
1422 END IF;
1423
1424 IF P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD = 'CUSTOM' THEN
1425 P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD := null;
1426 logmessage(fnd_log.level_statement, 'Defaulting ORIG_PAY_CALC_METHOD to ' || P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD);
1427 END IF;
1428
1429 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating PENAL_INT_RATE...');
1430
1431 P_Loan_Details_Rec.PENAL_INT_RATE :=
1432 CASE l_override_rec.PENAL_INT_RATE_OVR
1433 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.PENAL_INT_RATE, l_product_rec.PENAL_INT_RATE)
1434 ELSE l_product_rec.PENAL_INT_RATE
1435 END;
1436
1437 IF P_Loan_Details_Rec.PENAL_INT_RATE is null or P_Loan_Details_Rec.PENAL_INT_RATE < 0 THEN
1438 P_Loan_Details_Rec.PENAL_INT_RATE := 0;
1439 logmessage(fnd_log.level_statement, 'Defaulting PENAL_INT_RATE to ' || P_Loan_Details_Rec.PENAL_INT_RATE);
1440 END IF;
1441
1442 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating PENAL_INT_GRACE_DAYS...');
1443
1444 P_Loan_Details_Rec.PENAL_INT_GRACE_DAYS :=
1445 CASE l_override_rec.PENAL_INT_GRACE_DAYS_OVR
1446 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.PENAL_INT_GRACE_DAYS, l_product_rec.PENAL_INT_GRACE_DAYS)
1447 ELSE l_product_rec.PENAL_INT_GRACE_DAYS
1448 END;
1449
1450 IF P_Loan_Details_Rec.PENAL_INT_GRACE_DAYS is null or P_Loan_Details_Rec.PENAL_INT_GRACE_DAYS < 0 THEN
1451 P_Loan_Details_Rec.PENAL_INT_GRACE_DAYS := 0;
1452 logmessage(fnd_log.level_statement, 'Defaulting PENAL_INT_GRACE_DAYS to ' || P_Loan_Details_Rec.PENAL_INT_GRACE_DAYS);
1453 END IF;
1454
1455 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating CALC_ADD_INT_UNPAID_PRIN...');
1456
1457 P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_PRIN :=
1458 CASE l_override_rec.CALC_ADD_INT_UNPAID_PRIN_OVR
1459 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_PRIN, l_product_rec.CALC_ADD_INT_UNPAID_PRIN)
1460 ELSE l_product_rec.CALC_ADD_INT_UNPAID_PRIN
1461 END;
1462
1463 IF P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_PRIN is null THEN
1464 P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_PRIN := 'N';
1465 logmessage(fnd_log.level_statement, 'Defaulting CALC_ADD_INT_UNPAID_PRIN to ' || P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_PRIN);
1466 END IF;
1467
1468 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating CALC_ADD_INT_UNPAID_INT...');
1469
1470 P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_INT :=
1471 CASE l_override_rec.CALC_ADD_INT_UNPAID_INT_OVR
1472 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_INT, l_product_rec.CALC_ADD_INT_UNPAID_INT)
1473 ELSE l_product_rec.CALC_ADD_INT_UNPAID_INT
1474 END;
1475
1476 IF P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_INT is null THEN
1477 P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_INT := 'N';
1478 logmessage(fnd_log.level_statement, 'Defaulting CALC_ADD_INT_UNPAID_INT to ' || P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_INT);
1479 END IF;
1480
1481 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating FORGIVENESS_FLAG...');
1482
1483 P_Loan_Details_Rec.FORGIVENESS_FLAG :=
1484 CASE l_override_rec.FORGIVENESS_FLAG_OVR
1485 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.FORGIVENESS_FLAG, l_product_rec.FORGIVENESS_FLAG)
1486 ELSE l_product_rec.FORGIVENESS_FLAG
1487 END;
1488
1489 IF P_Loan_Details_Rec.FORGIVENESS_FLAG IS NOT NULL THEN
1490 if P_Loan_Details_Rec.FORGIVENESS_FLAG <> 'Y' and P_Loan_Details_Rec.FORGIVENESS_FLAG <> 'N' then
1491 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1492 ,p_token1=>'P_Loan_Details_Rec.FORGIVENESS_FLAG'
1493 ,p_token2=>P_Loan_Details_Rec.FORGIVENESS_FLAG);
1494 end if;
1495 END IF;
1496
1497 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating FORGIVENESS_PERCENT...');
1498
1499 P_Loan_Details_Rec.FORGIVENESS_PERCENT :=
1500 CASE l_override_rec.FORGIVENESS_PERCENT_OVR
1501 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.FORGIVENESS_PERCENT, l_product_rec.FORGIVENESS_PERCENT)
1502 ELSE l_product_rec.FORGIVENESS_PERCENT
1503 END;
1504
1505 IF P_Loan_Details_Rec.FORGIVENESS_PERCENT IS NOT NULL THEN
1506 if P_Loan_Details_Rec.FORGIVENESS_PERCENT < 0 or P_Loan_Details_Rec.FORGIVENESS_PERCENT > 100 then
1507 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1508 ,p_token1=>'P_Loan_Details_Rec.FORGIVENESS_PERCENT'
1509 ,p_token2=>P_Loan_Details_Rec.FORGIVENESS_PERCENT);
1510 end if;
1511 END IF;
1512
1513 P_Loan_Details_Rec.reamortize_over_payment :=
1514 CASE l_override_rec.REAMORTIZE_OVER_PAYMENT_OVR
1515 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.reamortize_over_payment, l_product_rec.reamortize_over_payment)
1516 ELSE l_product_rec.reamortize_over_payment
1517 END;
1518
1519 if l_product_rec.multiple_funding_flag = 'Y' then
1520
1521 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating open_loan_term_period...');
1522
1523 -- Validate Open Loan Term Period
1524 if P_Loan_Details_Rec.open_loan_term_period is null THEN
1525 P_Loan_Details_Rec.open_loan_term_period := l_product_rec.open_loan_term_period;
1526 end if;
1527
1528 IF P_Loan_Details_Rec.open_loan_term_period IS NULL THEN
1529 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
1530 ,p_token1=>'P_Loan_Details_Rec.open_loan_term_period');
1531 ELSE
1532 BEGIN
1533 SELECT 'Y'
1534 INTO l_dummy
1535 FROM lns_lookups llk
1536 WHERE llk.lookup_code = P_Loan_Details_Rec.open_loan_term_period
1537 AND llk.lookup_type = 'PERIOD';
1538 EXCEPTION
1539 WHEN NO_DATA_FOUND THEN
1540 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1541 ,p_token1=>'P_Loan_Details_Rec.open_loan_term_period'
1542 ,p_token2=>P_Loan_Details_Rec.open_loan_term_period);
1543 END;
1544
1545 END IF;
1546
1547 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating open_loan_term...');
1548
1549 -- Validate Open Loan Term
1550 IF P_Loan_Details_Rec.open_loan_term is NOT NULL THEN
1551 IF ((P_Loan_Details_Rec.open_loan_term > 999 ) OR
1552 (P_Loan_Details_Rec.open_loan_term < 0) OR
1553 (round(P_Loan_Details_Rec.open_loan_term) <> P_Loan_Details_Rec.open_loan_term))
1554 THEN
1555 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_BETWEEN'
1556 ,p_token1=>'P_Loan_Details_Rec.open_loan_term = '||P_Loan_Details_Rec.open_loan_term||' should be a whole number and '
1557 ,p_token2=>' 0 '
1558 ,p_token3=>' 999 '
1559 );
1560 END IF;
1561 ELSIF P_Loan_Details_Rec.open_loan_term is NOT NULL
1562 AND P_Loan_Details_Rec.open_loan_term_period is NOT NULL
1563 AND l_product_rec.open_max_loan_term is NOT NULL
1564 AND ((LNS_FIN_UTILS.intervalsinperiod(P_Loan_Details_Rec.open_loan_term
1565 ,P_Loan_Details_Rec.open_loan_term_period
1566 ,'DAYS')
1567 < LNS_FIN_UTILS.intervalsinperiod(l_product_rec.open_loan_term
1568 ,l_product_rec.open_loan_term_period
1569 ,'DAYS')) OR
1570 (LNS_FIN_UTILS.intervalsinperiod(P_Loan_Details_Rec.open_loan_term
1571 ,P_Loan_Details_Rec.open_loan_term_period
1572 ,'DAYS')
1573 > LNS_FIN_UTILS.intervalsinperiod(l_product_rec.open_max_loan_term
1574 ,l_product_rec.open_max_loan_term_period
1575 ,'DAYS')))
1576 THEN
1577 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_BETWEEN'
1578 ,p_token1=>'(P_Loan_Details_Rec.open_loan_term P_Loan_Details_Rec.open_loan_term_period)'
1579 ,p_token2=>l_product_rec.open_loan_term || ' ' || l_product_rec.open_loan_term_period
1580 ,p_token3=>l_product_rec.open_max_loan_term || ' ' || l_product_rec.open_max_loan_term_period);
1581 ELSIF P_Loan_Details_Rec.open_loan_term is NOT NULL
1582 AND P_Loan_Details_Rec.open_loan_term_period is NOT NULL
1583 AND l_product_rec.open_max_loan_term is NULL
1584 AND (LNS_FIN_UTILS.intervalsinperiod(P_Loan_Details_Rec.open_loan_term
1585 ,P_Loan_Details_Rec.open_loan_term_period
1586 ,'DAYS')
1587 < LNS_FIN_UTILS.intervalsinperiod(l_product_rec.open_loan_term
1588 ,l_product_rec.open_loan_term_period
1589 ,'DAYS'))
1590 THEN
1591 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_GREATER'
1592 ,p_token1=>'(P_Loan_Details_Rec.open_loan_term P_Loan_Details_Rec.open_loan_term_period)'
1593 ,p_token2=>l_product_rec.open_loan_term || ' ' || l_product_rec.open_loan_term_period);
1594 ELSIF P_Loan_Details_Rec.open_loan_term is NULL THEN
1595 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
1596 ,p_token1=>'P_Loan_Details_Rec.open_loan_term');
1597 END IF;
1598
1599 LogMessage(fnd_log.level_statement, 'Validating Loan Open Start Date');
1600
1601 IF P_Loan_Details_Rec.open_loan_start_date IS NULL THEN
1602 P_Loan_Details_Rec.open_loan_start_date := sysdate;
1603 END IF;
1604
1605 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating open_to_term_event...');
1606
1607 -- Validate Open To Term Event
1608 if P_Loan_Details_Rec.open_to_term_event is null then
1609 P_Loan_Details_Rec.open_to_term_event := 'AUTO_FINAL_DISBURSEMENT';
1610 end if;
1611
1612 IF P_Loan_Details_Rec.open_to_term_event IS NOT NULL THEN
1613 BEGIN
1614 SELECT 'Y'
1615 INTO l_dummy
1616 FROM lns_lookups llk
1617 WHERE llk.lookup_code = P_Loan_Details_Rec.open_to_term_event
1618 AND llk.lookup_type = 'OPEN_TO_TERM_EVENT';
1619 EXCEPTION
1620 WHEN NO_DATA_FOUND THEN
1621 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1622 ,p_token1=>'P_Loan_Details_Rec.open_to_term_event'
1623 ,p_token2=>P_Loan_Details_Rec.open_to_term_event);
1624 END;
1625 END IF;
1626
1627 P_Loan_Details_Rec.open_first_payment_date :=
1628 lns_fin_utils.getNextDate(p_date => P_Loan_Details_Rec.open_loan_start_date
1629 ,p_interval_type => P_Loan_Details_Rec.loan_payment_frequency
1630 ,p_direction => 1);
1631
1632 P_Loan_Details_Rec.open_maturity_date :=
1633 lns_fin_utils.getMaturityDate(
1634 p_term => P_Loan_Details_Rec.open_loan_term,
1635 p_term_period => P_Loan_Details_Rec.open_loan_term_period,
1636 p_frequency => P_Loan_Details_Rec.loan_payment_frequency,
1637 p_start_date => P_Loan_Details_Rec.open_loan_start_date);
1638
1639 P_Loan_Details_Rec.open_payment_frequency := P_Loan_Details_Rec.loan_payment_frequency;
1640
1641 P_Loan_Details_Rec.open_floor_rate :=
1642 CASE l_override_rec.OPEN_FLOOR_RATE_OVR
1643 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.open_floor_rate, l_product_rec.open_floor_rate)
1644 ELSE l_product_rec.open_floor_rate
1645 END;
1646
1647 P_Loan_Details_Rec.open_ceiling_rate :=
1648 CASE l_override_rec.OPEN_CEILING_RATE_OVR
1649 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.open_ceiling_rate, l_product_rec.open_ceiling_rate)
1650 ELSE l_product_rec.open_ceiling_rate
1651 END;
1652
1653 P_Loan_Details_Rec.open_percent_increase :=
1654 CASE l_override_rec.OPEN_PERCENT_INCREASE_OVR
1655 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.open_percent_increase, l_product_rec.open_percent_increase)
1656 ELSE l_product_rec.open_percent_increase
1657 END;
1658
1659 P_Loan_Details_Rec.open_percent_increase_life :=
1660 CASE l_override_rec.OPEN_PERCENT_INCREASE_LIFE_OVR
1661 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.open_percent_increase_life, l_product_rec.open_percent_increase_life)
1662 ELSE l_product_rec.open_percent_increase_life
1663 END;
1664
1665 P_Loan_Details_Rec.loan_start_date := P_Loan_Details_Rec.open_maturity_date;
1666
1667 LogMessage(fnd_log.level_statement, 'Validating Loan Application Date');
1668
1669 IF P_Loan_Details_Rec.loan_application_date IS NULL THEN
1670 P_Loan_Details_Rec.loan_application_date := P_Loan_Details_Rec.open_loan_start_date;
1671 END IF;
1672
1673 ELSE
1674
1675 P_Loan_Details_Rec.open_loan_term_period := null;
1676 P_Loan_Details_Rec.open_loan_term := null;
1677 P_Loan_Details_Rec.open_loan_start_date := null;
1678 P_Loan_Details_Rec.open_to_term_event := null;
1679 P_Loan_Details_Rec.open_first_payment_date := null;
1680 P_Loan_Details_Rec.open_maturity_date := null;
1681 P_Loan_Details_Rec.open_payment_frequency := null;
1682 P_Loan_Details_Rec.open_floor_rate := null;
1683 P_Loan_Details_Rec.open_ceiling_rate := null;
1684 P_Loan_Details_Rec.open_percent_increase := null;
1685 P_Loan_Details_Rec.open_percent_increase_life := null;
1686
1687 LogMessage(fnd_log.level_statement, 'Validating Loan Start Date');
1688
1689 IF P_Loan_Details_Rec.loan_start_date IS NULL THEN
1690 P_Loan_Details_Rec.loan_start_date := sysdate;
1691 END IF;
1692
1693 LogMessage(fnd_log.level_statement, 'Validating Loan Application Date');
1694
1695 IF P_Loan_Details_Rec.loan_application_date IS NULL THEN
1696 P_Loan_Details_Rec.loan_application_date := P_Loan_Details_Rec.loan_start_date;
1697 END IF;
1698
1699 END IF;
1700
1701 P_Loan_Details_Rec.maturity_date :=
1702 lns_fin_utils.getMaturityDate(
1703 p_term => P_Loan_Details_Rec.loan_term,
1704 p_term_period => P_Loan_Details_Rec.loan_term_period,
1705 p_frequency => P_Loan_Details_Rec.loan_payment_frequency,
1706 p_start_date => P_Loan_Details_Rec.loan_start_date
1707 );
1708
1709 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating first_payment_date...');
1710
1711 IF P_Loan_Details_Rec.first_payment_date IS NOT NULL THEN
1712 IF (P_Loan_Details_Rec.first_payment_date < P_Loan_Details_Rec.loan_start_date) THEN
1713 LogErrors(p_message_name => 'LNS_PAYMENT_START_DATE_ERROR2');
1714 ELSIF (P_Loan_Details_Rec.first_payment_date > P_Loan_Details_Rec.maturity_date) THEN
1715 LogErrors(p_message_name => 'LNS_PAYMENT_START_DATE_ERROR1');
1716 END IF;
1717 ELSE
1718 P_Loan_Details_Rec.first_payment_date := lns_fin_utils.getNextDate(p_date=> P_Loan_Details_Rec.loan_start_date
1719 ,p_interval_type => P_Loan_Details_Rec.loan_payment_frequency
1720 ,p_direction => 1);
1721 END IF;
1722
1723 IF P_Loan_Details_Rec.PAYMENT_CALC_METHOD = 'SEPARATE_SCHEDULES' THEN
1724
1725 logmessage(fnd_log.level_statement, 'Validating PRIN_FIRST_PAY_DATE');
1726
1727 IF P_Loan_Details_Rec.PRIN_FIRST_PAY_DATE IS NULL THEN
1728 P_Loan_Details_Rec.PRIN_FIRST_PAY_DATE := P_Loan_Details_Rec.first_payment_date;
1729 logmessage(fnd_log.level_statement, 'Defaulting PRIN_FIRST_PAY_DATE to ' || P_Loan_Details_Rec.PRIN_FIRST_PAY_DATE);
1730 ELSE
1731 IF (P_Loan_Details_Rec.PRIN_FIRST_PAY_DATE < P_Loan_Details_Rec.loan_start_date) THEN
1732 logerrors(p_message_name => 'LNS_PAYMENT_START_DATE_ERROR2');
1733 ELSIF (P_Loan_Details_Rec.PRIN_FIRST_PAY_DATE > P_Loan_Details_Rec.maturity_date) THEN
1734 logerrors(p_message_name => 'LNS_PAYMENT_START_DATE_ERROR1');
1735 END IF;
1736 END IF;
1737
1738 logmessage(fnd_log.level_statement, 'Validating PRIN_PAYMENT_FREQUENCY');
1739
1740 P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY :=
1741 CASE l_override_rec.PRIN_PAYMENT_FREQUENCY_OVR
1742 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY, l_product_rec.PRINCIPAL_PAYMENT_FREQUENCY)
1743 ELSE l_product_rec.PRINCIPAL_PAYMENT_FREQUENCY
1744 END;
1745
1746 IF P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY IS NULL THEN
1747 P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY := P_Loan_Details_Rec.loan_payment_frequency;
1748 logmessage(fnd_log.level_statement, 'Defaulting PRIN_PAYMENT_FREQUENCY to ' || P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY);
1749 ELSE
1750 BEGIN
1751 SELECT 'Y'
1752 INTO l_dummy
1753 FROM lns_lookups llk
1754 WHERE llk.lookup_code = P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY
1755 AND llk.lookup_type = 'FREQUENCY';
1756
1757 EXCEPTION
1758 WHEN no_data_found THEN
1759 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1760 ,p_token1=>'P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY'
1761 ,p_token2=>P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY);
1762 END;
1763 END IF;
1764
1765 END IF;
1766
1767 P_Loan_Details_Rec.floor_rate :=
1768 CASE l_override_rec.FLOOR_RATE_OVR
1769 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.floor_rate, l_product_rec.floor_rate)
1770 ELSE l_product_rec.floor_rate
1771 END;
1772
1773 P_Loan_Details_Rec.ceiling_rate :=
1774 CASE l_override_rec.CEILING_RATE_OVR
1775 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.ceiling_rate, l_product_rec.ceiling_rate)
1776 ELSE l_product_rec.ceiling_rate
1777 END;
1778
1779 P_Loan_Details_Rec.percent_increase :=
1780 CASE l_override_rec.PERCENT_INCREASE_OVR
1781 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.percent_increase, l_product_rec.percent_increase)
1782 ELSE l_product_rec.percent_increase
1783 END;
1784
1785 P_Loan_Details_Rec.percent_increase_life :=
1786 CASE l_override_rec.PERCENT_INCREASE_LIFE_OVR
1787 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.percent_increase_life, l_product_rec.percent_increase_life)
1788 ELSE l_product_rec.percent_increase_life
1789 END;
1790
1791 logmessage(fnd_log.level_statement, 'Validating REAMORTIZE_ON_FUNDING');
1792
1793 P_Loan_Details_Rec.REAMORTIZE_ON_FUNDING :=
1794 CASE l_override_rec.REAMORTIZE_ON_FUNDING_OVR
1795 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.REAMORTIZE_ON_FUNDING, l_product_rec.REAMORTIZE_ON_FUNDING)
1796 ELSE l_product_rec.REAMORTIZE_ON_FUNDING
1797 END;
1798
1799 IF P_Loan_Details_Rec.REAMORTIZE_ON_FUNDING IS NOT NULL THEN
1800 BEGIN
1801 SELECT 'Y'
1802 INTO l_dummy
1803 FROM lns_lookups llk
1804 WHERE llk.lookup_code = P_Loan_Details_Rec.REAMORTIZE_ON_FUNDING
1805 AND llk.lookup_type = 'REAMORTIZE_ON_FUNDING_OPTIONS';
1806 EXCEPTION
1807 WHEN NO_DATA_FOUND THEN
1808 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1809 ,p_token1=>'P_Loan_Details_Rec.REAMORTIZE_ON_FUNDING'
1810 ,p_token2=>P_Loan_Details_Rec.REAMORTIZE_ON_FUNDING);
1811 END;
1812 END IF;
1813
1814 if P_Loan_Details_Rec.ATTRIBUTE_CATEGORY is null and l_product_rec.attribute_category is not null then
1815 logmessage(fnd_log.level_statement, 'Copying product attributes');
1816 P_Loan_Details_Rec.attribute_category := l_product_rec.attribute_category;
1817 P_Loan_Details_Rec.attribute1 := l_product_rec.attribute1;
1818 P_Loan_Details_Rec.attribute2 := l_product_rec.attribute2;
1819 P_Loan_Details_Rec.attribute3 := l_product_rec.attribute3;
1820 P_Loan_Details_Rec.attribute4 := l_product_rec.attribute4;
1821 P_Loan_Details_Rec.attribute5 := l_product_rec.attribute5;
1822 P_Loan_Details_Rec.attribute6 := l_product_rec.attribute6;
1823 P_Loan_Details_Rec.attribute7 := l_product_rec.attribute7;
1824 P_Loan_Details_Rec.attribute8 := l_product_rec.attribute8;
1825 P_Loan_Details_Rec.attribute9 := l_product_rec.attribute9;
1826 P_Loan_Details_Rec.attribute10 := l_product_rec.attribute10;
1827 P_Loan_Details_Rec.attribute11 := l_product_rec.attribute11;
1828 P_Loan_Details_Rec.attribute12 := l_product_rec.attribute12;
1829 P_Loan_Details_Rec.attribute13 := l_product_rec.attribute13;
1830 P_Loan_Details_Rec.attribute14 := l_product_rec.attribute14;
1831 P_Loan_Details_Rec.attribute15 := l_product_rec.attribute15;
1832 P_Loan_Details_Rec.attribute16 := l_product_rec.attribute16;
1833 P_Loan_Details_Rec.attribute17 := l_product_rec.attribute17;
1834 P_Loan_Details_Rec.attribute18 := l_product_rec.attribute18;
1835 P_Loan_Details_Rec.attribute19 := l_product_rec.attribute19;
1836 P_Loan_Details_Rec.attribute20 := l_product_rec.attribute20;
1837 end if;
1838
1839 IF g_error_count > 0 THEN
1840 RAISE fnd_api.g_exc_error;
1841 END IF;
1842
1843 -- END OF BODY OF API
1844 x_return_status := fnd_api.g_ret_sts_success;
1845
1846 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' -');
1847
1848 EXCEPTION
1849 WHEN fnd_api.g_exc_error THEN
1850 x_return_status := fnd_api.g_ret_sts_error;
1851 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1852 WHEN fnd_api.g_exc_unexpected_error THEN
1853 x_return_status := fnd_api.g_ret_sts_unexp_error;
1854 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1855 WHEN others THEN
1856 x_return_status := fnd_api.g_ret_sts_unexp_error;
1857 logerrors('Exception at '||g_pkg_name || '.' || l_api_name ||' is '||sqlerrm);
1858 x_msg_count := 1;
1859 x_msg_data := sqlerrm;
1860
1861 END validate_loan_header_details;
1862
1863
1864
1865
1866 PROCEDURE validate_participants(p_loan_part_tbl IN OUT NOCOPY LNS_LOAN_PUB.loan_part_tbl_type
1867 ,x_return_status OUT nocopy VARCHAR2
1868 ,x_msg_count OUT nocopy NUMBER
1869 ,x_msg_data OUT nocopy VARCHAR2)
1870 IS
1871 l_api_name constant VARCHAR2(30) := 'VALIDATE_PARTICIPANTS';
1872 l_dummy VARCHAR2(1);
1873 l_borrower_valid boolean;
1874 l_cust_acct_valid boolean;
1875 l_contact_rel_party_id lns_loan_headers_all.contact_rel_party_id%TYPE;
1876 l_contact_pers_party_id lns_loan_headers_all.contact_pers_party_id%TYPE;
1877 l_valid_contact_rel VARCHAR2(1) := 'N';
1878 l_valid_contact_pers VARCHAR2(1) := 'N';
1879
1880 BEGIN
1881 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' +');
1882
1883 FOR l_count IN 1 .. p_loan_part_tbl.COUNT
1884 LOOP
1885
1886 l_borrower_valid := FALSE;
1887 l_cust_acct_valid := FALSE;
1888
1889 logmessage(fnd_log.level_statement, 'Participant ' || l_count);
1890 logmessage(fnd_log.level_statement, 'HZ_PARTY_ID = ' || p_loan_part_tbl(l_count).HZ_PARTY_ID);
1891 logmessage(fnd_log.level_statement, 'LOAN_PARTICIPANT_TYPE = ' || p_loan_part_tbl(l_count).LOAN_PARTICIPANT_TYPE);
1892 logmessage(fnd_log.level_statement, 'START_DATE_ACTIVE = ' || p_loan_part_tbl(l_count).START_DATE_ACTIVE);
1893 logmessage(fnd_log.level_statement, 'END_DATE_ACTIVE = ' || p_loan_part_tbl(l_count).END_DATE_ACTIVE);
1894 logmessage(fnd_log.level_statement, 'CUST_ACCOUNT_ID = ' || p_loan_part_tbl(l_count).CUST_ACCOUNT_ID);
1895 logmessage(fnd_log.level_statement, 'BILL_TO_ACCT_SITE_ID = ' || p_loan_part_tbl(l_count).BILL_TO_ACCT_SITE_ID);
1896 logmessage(fnd_log.level_statement, 'CONTACT_PERS_PARTY_ID = ' || p_loan_part_tbl(l_count).CONTACT_PERS_PARTY_ID);
1897 logmessage(fnd_log.level_statement, 'CONTACT_REL_PARTY_ID = ' || p_loan_part_tbl(l_count).CONTACT_REL_PARTY_ID);
1898
1899 IF p_loan_part_tbl(l_count).loan_participant_type IS NULL THEN
1900 LogErrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
1901 p_token1 => 'P_LOAN_PART_TBL(l_count).loan_participant_type');
1902 ELSE
1903 BEGIN
1904 SELECT 'Y'
1905 INTO l_dummy
1906 FROM lns_lookups llk
1907 WHERE llk.lookup_code = p_loan_part_tbl(l_count).loan_participant_type
1908 AND llk.lookup_type = 'LNS_PARTICIPANT_TYPE'
1909 AND enabled_flag = 'Y'
1910 AND lookup_code <> 'PRIMARY_BORROWER';
1911 EXCEPTION
1912 WHEN no_data_found THEN
1913 LogErrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
1914 , p_token1 => 'P_LOAN_PART_TBL(l_count).loan_participant_type'
1915 , p_token2 => p_loan_part_tbl(l_count).loan_participant_type);
1916 END;
1917 END IF;
1918
1919 LogMessage(fnd_log.level_statement, 'Validating Participant table PartyId');
1920
1921 -- Validate Particpant HZ_PARTY_ID
1922 IF p_loan_part_tbl(l_count).hz_party_id IS NULL THEN
1923 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
1924 ,p_token1=>'P_LOAN_PART_TBL(l_count).hz_party_id');
1925 ELSE
1926 BEGIN
1927 SELECT 'Y'
1928 INTO l_dummy
1929 FROM hz_parties hzp
1930 WHERE hzp.party_id = p_loan_part_tbl(l_count).hz_party_id
1931 AND hzp.status = 'A';
1932 l_borrower_valid := TRUE;
1933 EXCEPTION
1934 WHEN NO_DATA_FOUND THEN
1935 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1936 , p_token1 => 'P_LOAN_PART_TBL(l_count).hz_party_id'
1937 , p_token2 => p_loan_part_tbl(l_count).hz_party_id);
1938 END;
1939 END IF;
1940
1941 logmessage(fnd_log.level_statement, 'Validating Participant table Customer Account Id');
1942
1943 -- Validate cust_account_id
1944 IF l_borrower_valid THEN
1945
1946 IF p_loan_part_tbl(l_count).cust_account_id IS NULL THEN
1947 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
1948 ,p_token1=>'P_Loan_Details_Rec.cust_account_id');
1949 ELSE
1950 BEGIN
1951 SELECT 'Y'
1952 INTO l_dummy
1953 FROM hz_cust_accounts_all hzca
1954 WHERE hzca.cust_account_id = p_loan_part_tbl(l_count).cust_account_id
1955 AND hzca.party_id = p_loan_part_tbl(l_count).hz_party_id
1956 AND hzca.status = 'A';
1957 l_cust_acct_valid := TRUE;
1958 EXCEPTION
1959 WHEN NO_DATA_FOUND THEN
1960 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1961 , p_token1 => 'P_LOAN_PART_TBL(l_count).cust_account_id'
1962 , p_token2 => p_loan_part_tbl(l_count).cust_account_id);
1963 END;
1964 END IF;
1965
1966 END IF;
1967
1968 logmessage(fnd_log.level_statement, 'Validating Participant table Bill To Account Site ID');
1969
1970 -- Validate bill_to_acct_site_id
1971 IF l_cust_acct_valid THEN
1972
1973 IF p_loan_part_tbl(l_count).bill_to_acct_site_id IS NULL THEN
1974 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
1975 ,p_token1=>'P_LOAN_PART_TBL(l_count).bill_to_acct_site_id');
1976 ELSE
1977 BEGIN
1978 SELECT 'Y'
1979 INTO l_dummy
1980 FROM hz_cust_accounts_all account,
1981 hz_cust_site_uses acc_site_use,
1982 hz_cust_acct_sites_all acct_site
1983 WHERE account.cust_account_id = acct_site.cust_account_id
1984 AND acc_site_use.cust_acct_site_id = acct_site.cust_acct_site_id
1985 AND acc_site_use.site_use_code = 'BILL_TO'
1986 AND acct_site.cust_acct_site_id = p_loan_part_tbl(l_count).bill_to_acct_site_id
1987 AND acct_site.cust_account_id = p_loan_part_tbl(l_count).cust_account_id
1988 AND acc_site_use.status = 'A';
1989 EXCEPTION
1990 WHEN NO_DATA_FOUND THEN
1991 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1992 ,p_token1=>'P_LOAN_PART_TBL(l_count).bill_to_acct_site_id'
1993 ,p_token2=>p_loan_part_tbl(l_count).bill_to_acct_site_id);
1994 END;
1995 END IF;
1996
1997 END IF;
1998
1999 logmessage(fnd_log.level_statement, 'Validating Participant table Contact Person Party ID');
2000
2001 IF l_borrower_valid THEN
2002
2003 IF P_LOAN_PART_TBL(l_count).contact_rel_party_id IS NOT NULL THEN
2004 BEGIN
2005 SELECT
2006 p.party_id INTO l_contact_pers_party_id
2007 FROM
2008 hz_relationships rel,
2009 hz_parties p,
2010 hz_parties o,
2011 hz_parties rel_party,
2012 ar_lookups lkup,
2013 hz_relationship_types rel_type,
2014 hz_contact_points con_phone
2015 WHERE o.party_type = 'ORGANIZATION'
2016 AND p.party_type = 'PERSON'
2017 AND rel.subject_id = p.party_id
2018 AND rel.object_id = o.party_id
2019 AND rel.relationship_code = rel_type.forward_rel_code
2020 AND rel_type.create_party_flag = 'Y'
2021 AND rel_type.subject_type = 'PERSON'
2022 AND rel_type.object_type = 'ORGANIZATION'
2023 AND rel.relationship_type = rel_type.relationship_type
2024 AND rel_type.role = lkup.lookup_code
2025 AND lkup.lookup_type = 'HZ_RELATIONSHIP_ROLE'
2026 AND rel.party_id = rel_party.party_id
2027 AND rel.status = 'A'
2028 AND rel_party.status = 'A'
2029 AND p.status = 'A'
2030 AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
2031 AND con_phone.owner_table_id(+) = rel.party_id
2032 AND con_phone.primary_flag(+) = 'Y'
2033 AND con_phone.status(+) = 'A'
2034 AND con_phone.contact_point_type(+) = 'PHONE'
2035 AND rel.party_id = P_LOAN_PART_TBL(l_count).contact_rel_party_id;
2036 l_valid_contact_rel := 'Y';
2037 EXCEPTION
2038 WHEN no_data_found THEN
2039 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
2040 p_token1 => 'P_LOAN_PART_TBL(l_count).contact_rel_party_id',
2041 p_token2 => P_LOAN_PART_TBL(l_count).contact_rel_party_id);
2042 END;
2043 END IF;
2044
2045 IF l_valid_contact_rel = 'Y' THEN
2046 IF P_LOAN_PART_TBL(l_count).contact_pers_party_id IS NULL THEN
2047 P_LOAN_PART_TBL(l_count).contact_pers_party_id := l_contact_pers_party_id;
2048 l_valid_contact_pers := 'Y';
2049 ELSIF l_contact_pers_party_id = P_LOAN_PART_TBL(l_count).contact_pers_party_id THEN
2050 l_valid_contact_pers := 'Y';
2051 END IF;
2052 END IF;
2053
2054 IF ((P_LOAN_PART_TBL(l_count).contact_pers_party_id IS NOT NULL) AND (l_valid_contact_pers <> 'Y')) THEN
2055 BEGIN
2056 SELECT
2057 rel.party_id INTO l_contact_rel_party_id
2058 FROM
2059 hz_relationships rel,
2060 hz_parties p,
2061 hz_parties o,
2062 hz_parties rel_party,
2063 ar_lookups lkup,
2064 hz_relationship_types rel_type,
2065 hz_contact_points con_phone
2066 WHERE o.party_type = 'ORGANIZATION'
2067 AND p.party_type = 'PERSON'
2068 AND rel.subject_id = p.party_id
2069 AND rel.object_id = o.party_id
2070 AND rel.relationship_code = rel_type.forward_rel_code
2071 AND rel_type.create_party_flag = 'Y'
2072 AND rel_type.subject_type = 'PERSON'
2073 AND rel_type.object_type = 'ORGANIZATION'
2074 AND rel.relationship_type = rel_type.relationship_type
2075 AND rel_type.role = lkup.lookup_code
2076 AND lkup.lookup_type = 'HZ_RELATIONSHIP_ROLE'
2077 AND rel.party_id = rel_party.party_id
2078 AND rel.status = 'A'
2079 AND rel_party.status = 'A'
2080 AND p.status = 'A'
2081 AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
2082 AND con_phone.owner_table_id(+) = rel.party_id
2083 AND con_phone.primary_flag(+) = 'Y'
2084 AND con_phone.status(+) = 'A'
2085 AND con_phone.contact_point_type(+) = 'PHONE'
2086 AND p.party_id = P_LOAN_PART_TBL(l_count).contact_pers_party_id;
2087 l_valid_contact_pers := 'Y';
2088 EXCEPTION
2089 WHEN no_data_found THEN
2090 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
2091 p_token1 => 'P_LOAN_PART_TBL(l_count).contact_pers_party_id',
2092 p_token2 => P_LOAN_PART_TBL(l_count).contact_pers_party_id);
2093 END;
2094 END IF;
2095
2096 IF l_valid_contact_pers = 'Y' THEN
2097 IF P_LOAN_PART_TBL(l_count).contact_rel_party_id IS NULL THEN
2098 l_contact_rel_party_id := P_LOAN_PART_TBL(l_count).contact_rel_party_id;
2099 l_valid_contact_rel := 'Y';
2100 ELSIF l_contact_rel_party_id = P_LOAN_PART_TBL(l_count).contact_rel_party_id THEN
2101 l_valid_contact_rel := 'Y';
2102 END IF;
2103 END IF;
2104
2105 -- Only if bothe Contact (rel and Person) are not NULL and individually both are correct but incorrect when combined
2106 IF (((P_LOAN_PART_TBL(l_count).contact_rel_party_id IS NOT NULL) AND (P_LOAN_PART_TBL(l_count).contact_pers_party_id IS NOT NULL)) AND ((l_valid_contact_rel <> 'Y') OR (l_valid_contact_pers <> 'Y'))) THEN
2107 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
2108 p_token1 => 'P_LOAN_PART_TBL(l_count).contact_pers_party_id and ',
2109 p_token2 => P_LOAN_PART_TBL(l_count).contact_pers_party_id);
2110 END IF;
2111
2112 END IF; -- If the l_borrower_valid
2113
2114 END LOOP;
2115
2116 IF g_error_count > 0 THEN
2117 RAISE fnd_api.g_exc_error;
2118 END IF;
2119
2120 -- END OF BODY OF API
2121 x_return_status := fnd_api.g_ret_sts_success;
2122 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' -');
2123
2124 EXCEPTION
2125 WHEN fnd_api.g_exc_error THEN
2126 x_return_status := fnd_api.g_ret_sts_error;
2127 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2128 WHEN fnd_api.g_exc_unexpected_error THEN
2129 x_return_status := fnd_api.g_ret_sts_unexp_error;
2130 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2131 WHEN others THEN
2132 x_return_status := fnd_api.g_ret_sts_unexp_error;
2133 logerrors('Exception at '||g_pkg_name || '.' || l_api_name ||' is '||sqlerrm);
2134 x_msg_count := 1;
2135 x_msg_data := sqlerrm;
2136
2137 END validate_participants;
2138
2139
2140
2141
2142 PROCEDURE validate_loan_lines(P_Loan_Details_Rec IN OUT NOCOPY LNS_LOAN_PUB.Loan_Details_Rec_Type
2143 ,p_loan_lines_tbl IN OUT nocopy LNS_LOAN_PUB.loan_lines_tbl_type
2144 ,x_return_status OUT nocopy VARCHAR2
2145 ,x_msg_count OUT nocopy NUMBER
2146 ,x_msg_data OUT nocopy VARCHAR2)
2147 IS
2148 l_api_name constant VARCHAR2(30) := 'VALIDATE_LOAN_LINES';
2149 l_dummy VARCHAR2(1);
2150 BEGIN
2151
2152 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' +');
2153
2154 FOR l_count IN 1 .. p_loan_lines_tbl.COUNT
2155 LOOP
2156
2157 logmessage(fnd_log.level_statement, 'Validating Loan Line Amount...');
2158
2159 IF p_loan_lines_tbl(l_count).requested_amount IS NULL THEN
2160 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
2161 p_token1 => 'P_LOAN_LINES_TBL(l_count).requested_amount');
2162 ELSIF p_loan_lines_tbl(l_count).requested_amount <= 0 THEN
2163 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
2164 , p_token1 => 'P_LOAN_LINES_TBL(l_count).amount'
2165 , p_token2 => p_loan_lines_tbl(l_count).requested_amount);
2166 END IF;
2167
2168 if p_loan_lines_tbl(l_count).payment_schedule_id <> -1 then -- let go imported loan
2169
2170 BEGIN
2171 logmessage(fnd_log.level_statement, 'Validating payment_schedule_id...');
2172
2173 select pmt_sch.customer_trx_id
2174 ,pmt_sch.trx_number
2175 ,pmt_sch.amount_due_remaining
2176 ,pmt_sch.terms_sequence_number
2177 INTO p_loan_lines_tbl(l_count).customer_trx_id
2178 ,p_loan_lines_tbl(l_count).REFERENCE_NUMBER
2179 ,p_loan_lines_tbl(l_count).remaining_balance
2180 ,p_loan_lines_tbl(l_count).installment_number
2181 FROM
2182 ar_payment_schedules_all pmt_sch,
2183 hz_cust_accounts account,
2184 RA_CUST_TRX_TYPES trx_type,
2185 ar_lookups trx_type_lkup,
2186 ar_lookups trx_class_lkup
2187 WHERE
2188 pmt_sch.class in ('INV','DM') and
2189 pmt_sch.status = 'OP' and
2190 pmt_sch.amount_due_remaining > 0 and
2191 pmt_sch.cust_trx_type_id = trx_type.CUST_TRX_TYPE_ID and
2192 trx_type_lkup.lookup_type = 'INV/CM' and
2193 trx_type_lkup.lookup_code = trx_type.type and
2194 trx_class_lkup.lookup_type = 'INV/CM' and
2195 trx_class_lkup.lookup_code = pmt_sch.class and
2196 pmt_sch.customer_id = account.cust_account_id and
2197 account.party_id = P_Loan_Details_Rec.primary_borrower_party_id and
2198 pmt_sch.INVOICE_CURRENCY_CODE = l_product_rec.loan_currency and
2199 pmt_sch.payment_schedule_id = p_loan_lines_tbl(l_count).payment_schedule_id;
2200 EXCEPTION
2201 WHEN NO_DATA_FOUND THEN
2202 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
2203 ,p_token1=>'p_loan_lines_tbl(l_count).payment_schedule_id'
2204 ,p_token2=>p_loan_lines_tbl(l_count).payment_schedule_id);
2205 END;
2206
2207 IF p_loan_lines_tbl(l_count).requested_amount > p_loan_lines_tbl(l_count).remaining_balance THEN
2208
2209 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
2210 ,p_line_number => p_loan_lines_tbl(l_count).line_number
2211 ,p_token1=>'p_loan_lines_tbl(l_count).requested_amount'
2212 ,p_token2=>p_loan_lines_tbl(l_count).requested_amount);
2213 END IF;
2214 else
2215 p_loan_lines_tbl(l_count).payment_schedule_id := to_number('-'||l_count);
2216 end if;
2217
2218 if p_loan_lines_tbl(l_count).customer_trx_id is null then
2219 p_loan_lines_tbl(l_count).customer_trx_id := to_number('-'||l_count);
2220 end if;
2221
2222 if p_loan_lines_tbl(l_count).remaining_balance is null then
2223 p_loan_lines_tbl(l_count).remaining_balance := 0;
2224 end if;
2225
2226 if p_loan_lines_tbl(l_count).installment_number is null then
2227 p_loan_lines_tbl(l_count).installment_number := 1;
2228 end if;
2229
2230 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Loan Line ' || l_count);
2231 LogMessage(FND_LOG.LEVEL_STATEMENT, 'line_number = ' || p_loan_lines_tbl(l_count).line_number);
2232 LogMessage(FND_LOG.LEVEL_STATEMENT, 'requested_amount = ' || p_loan_lines_tbl(l_count).requested_amount);
2233 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REFERENCE_DESCRIPTION = ' || p_loan_lines_tbl(l_count).REFERENCE_DESCRIPTION);
2234 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REFERENCE_NUMBER = ' || p_loan_lines_tbl(l_count).REFERENCE_NUMBER);
2235 LogMessage(FND_LOG.LEVEL_STATEMENT, 'payment_schedule_id = ' || p_loan_lines_tbl(l_count).payment_schedule_id);
2236 LogMessage(FND_LOG.LEVEL_STATEMENT, 'customer_trx_id = ' || p_loan_lines_tbl(l_count).customer_trx_id);
2237 LogMessage(FND_LOG.LEVEL_STATEMENT, 'remaining_balance = ' || p_loan_lines_tbl(l_count).remaining_balance);
2238 LogMessage(FND_LOG.LEVEL_STATEMENT, 'installment_number = ' || p_loan_lines_tbl(l_count).installment_number);
2239
2240 END LOOP;
2241
2242 IF g_error_count > 0 THEN
2243 RAISE fnd_api.g_exc_error;
2244 END IF;
2245
2246 -- END OF BODY OF API
2247 x_return_status := fnd_api.g_ret_sts_success;
2248 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' -');
2249
2250 EXCEPTION
2251 WHEN fnd_api.g_exc_error THEN
2252 x_return_status := fnd_api.g_ret_sts_error;
2253 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2254 WHEN fnd_api.g_exc_unexpected_error THEN
2255 x_return_status := fnd_api.g_ret_sts_unexp_error;
2256 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2257 WHEN others THEN
2258 x_return_status := fnd_api.g_ret_sts_unexp_error;
2259 logerrors('Exception at '||g_pkg_name || '.' || l_api_name ||' is '||sqlerrm);
2260 x_msg_count := 1;
2261 x_msg_data := sqlerrm;
2262
2263 END;
2264
2265
2266
2267 PROCEDURE validate_rate_sched(P_Loan_Details_Rec IN OUT NOCOPY LNS_LOAN_PUB.Loan_Details_Rec_Type
2268 ,p_loan_rates_tbl IN OUT nocopy LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE
2269 ,p_phase IN VARCHAR2
2270 ,x_return_status OUT nocopy VARCHAR2
2271 ,x_msg_count OUT nocopy NUMBER
2272 ,x_msg_data OUT nocopy VARCHAR2)
2273 IS
2274 l_api_name constant VARCHAR2(30) := 'validate_rate_sched';
2275 l_return_status VARCHAR2(1);
2276 l_dummy VARCHAR2(1);
2277 l_num_temp NUMBER;
2278 l_current_int_rate NUMBER;
2279 l_spread NUMBER;
2280 l_floor_rate NUMBER;
2281 l_ceiling_rate NUMBER;
2282 l_index_date DATE;
2283
2284 BEGIN
2285 -- Initialize API return status to success
2286 l_return_status := fnd_api.g_ret_sts_success;
2287
2288 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' +');
2289
2290 if p_phase = 'TERM' then
2291 l_floor_rate := P_Loan_Details_Rec.floor_rate;
2292 l_ceiling_rate := P_Loan_Details_Rec.ceiling_rate;
2293 l_index_date := P_Loan_Details_Rec.loan_start_date;
2294 else
2295 l_floor_rate := P_Loan_Details_Rec.open_floor_rate;
2296 l_ceiling_rate := P_Loan_Details_Rec.open_ceiling_rate;
2297 l_index_date := P_Loan_Details_Rec.open_loan_start_date;
2298 end if;
2299
2300 if p_loan_rates_tbl.COUNT = 0 then
2301 p_loan_rates_tbl(1).INDEX_DATE := l_index_date;
2302 p_loan_rates_tbl(1).BEGIN_INSTALLMENT_NUMBER := 1;
2303 p_loan_rates_tbl(1).END_INSTALLMENT_NUMBER := -1;
2304 end if;
2305
2306 logmessage(fnd_log.level_unexpected, 'Validating Rate Schedule...');
2307 FOR l_count IN 1 .. p_loan_rates_tbl.COUNT
2308 LOOP
2309
2310 logmessage(fnd_log.level_statement, 'Rate Record ' || l_count);
2311 logmessage(fnd_log.level_statement, 'INDEX_RATE = ' || p_loan_rates_tbl(l_count).INDEX_RATE);
2312 logmessage(fnd_log.level_statement, 'SPREAD = ' || p_loan_rates_tbl(l_count).SPREAD);
2313 logmessage(fnd_log.level_statement, 'INDEX_DATE = ' || p_loan_rates_tbl(l_count).INDEX_DATE);
2314 logmessage(fnd_log.level_statement, 'BEGIN_INSTALLMENT_NUMBER = ' || p_loan_rates_tbl(l_count).BEGIN_INSTALLMENT_NUMBER);
2315 logmessage(fnd_log.level_statement, 'END_INSTALLMENT_NUMBER = ' || p_loan_rates_tbl(l_count).END_INSTALLMENT_NUMBER);
2316 logmessage(fnd_log.level_statement, 'INTEREST_ONLY_FLAG = ' || p_loan_rates_tbl(l_count).INTEREST_ONLY_FLAG);
2317
2318 logmessage(fnd_log.level_statement, 'Validating Index Date');
2319
2320 IF (p_loan_rates_tbl(l_count).index_date IS NOT NULL AND P_Loan_Details_Rec.INDEX_RATE_ID IS NOT NULL) THEN
2321
2322 BEGIN
2323 SELECT line.interest_rate
2324 INTO l_num_temp
2325 FROM lns_int_rate_headers hdr,
2326 lns_int_rate_lines line
2327 WHERE hdr.interest_rate_id = line.interest_rate_id
2328 AND(p_loan_rates_tbl(l_count).index_date BETWEEN line.start_date_active
2329 AND line.end_date_active)
2330 AND hdr.interest_rate_id = P_Loan_Details_Rec.INDEX_RATE_ID;
2331
2332 p_loan_rates_tbl(l_count).index_rate := l_num_temp;
2333
2334 EXCEPTION
2335 WHEN others THEN
2336 logmessage(fnd_log.level_procedure, 'Index Rate incorrect');
2337 END;
2338 END IF;
2339
2340 logmessage(fnd_log.level_statement, 'Validating Index Rate');
2341
2342 IF p_loan_rates_tbl(l_count).index_rate IS NULL THEN
2343 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
2344 p_token1 => 'p_loan_rates_tbl(' || l_count || ').index_rate');
2345 END IF;
2346
2347 logmessage(fnd_log.level_statement, 'Validating spread');
2348
2349 if p_phase = 'TERM' then
2350 p_loan_rates_tbl(l_Count).spread :=
2351 CASE l_override_rec.spread_ovr
2352 WHEN 'Y' THEN nvl(p_loan_rates_tbl(l_Count).spread, l_product_rec.spread)
2353 ELSE l_product_rec.spread
2354 END;
2355 else
2356 p_loan_rates_tbl(l_Count).spread :=
2357 CASE l_override_rec.OPEN_SPREAD_OVR
2358 WHEN 'Y' THEN nvl(p_loan_rates_tbl(l_Count).spread, l_product_rec.open_spread)
2359 ELSE l_product_rec.open_spread
2360 END;
2361 end if;
2362
2363 IF p_loan_rates_tbl(l_count).spread IS NULL THEN
2364 p_loan_rates_tbl(l_count).spread := 0;
2365 END IF;
2366
2367 logmessage(fnd_log.level_statement, 'Validating current_int_rate');
2368
2369 l_current_int_rate := p_loan_rates_tbl(l_count).index_rate + p_loan_rates_tbl(l_count).spread;
2370
2371 IF (l_current_int_rate < nvl(l_floor_rate, l_current_int_rate)) OR
2372 (l_current_int_rate > nvl(l_ceiling_rate, l_current_int_rate)) OR
2373 (l_current_int_rate < 0) OR (l_current_int_rate > 100)
2374 THEN
2375 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
2376 p_token1 => 'p_loan_rates_tbl(l_count).index_rate',
2377 p_token2 => p_loan_rates_tbl(l_count).index_rate);
2378 END IF;
2379
2380 logmessage(fnd_log.level_statement, 'Validating Interest Only Flag');
2381
2382 IF p_loan_rates_tbl(l_count).interest_only_flag IS NOT NULL THEN
2383 IF p_loan_rates_tbl(l_count).interest_only_flag <> 'Y' AND
2384 p_loan_rates_tbl(l_count).interest_only_flag <> 'N'
2385 THEN
2386 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
2387 p_token1 => 'p_loan_rates_tbl(l_count).interest_only_flag',
2388 p_token2 => p_loan_rates_tbl(l_count).interest_only_flag);
2389 END IF;
2390 ELSE
2391 p_loan_rates_tbl(l_count).interest_only_flag := 'N';
2392 END IF;
2393
2394 if l_product_rec.allow_interest_only_flag = 'N' and p_loan_rates_tbl(l_count).interest_only_flag = 'Y' then
2395 p_loan_rates_tbl(l_count).interest_only_flag := 'N';
2396 end if;
2397
2398 END LOOP;
2399
2400 IF g_error_count > 0 THEN
2401 RAISE fnd_api.g_exc_error;
2402 END IF;
2403
2404 -- END OF BODY OF API
2405 x_return_status := fnd_api.g_ret_sts_success;
2406
2407 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' -');
2408
2409 EXCEPTION
2410 WHEN fnd_api.g_exc_error THEN
2411 x_return_status := fnd_api.g_ret_sts_error;
2412 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2413 WHEN fnd_api.g_exc_unexpected_error THEN
2414 x_return_status := fnd_api.g_ret_sts_unexp_error;
2415 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2416 WHEN others THEN
2417 x_return_status := fnd_api.g_ret_sts_unexp_error;
2418 logerrors('Exception at '||g_pkg_name || '.' || l_api_name ||' is '||sqlerrm);
2419 x_msg_count := 1;
2420 x_msg_data := sqlerrm;
2421
2422 END;
2423
2424
2425
2426
2427 /*
2428 This procedure synchs rate schedule with new number of installments in memory only, no changes to db
2429 */
2430 procedure synchRateSchedule(p_rate_tbl IN OUT NOCOPY LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE, p_num_installments in number)
2431
2432 is
2433
2434 /*-----------------------------------------------------------------------+
2435 | Local Variable Declarations and initializations |
2436 +-----------------------------------------------------------------------*/
2437
2438 l_RATE_ID number;
2439 l_BEGIN_INSTALLMENT number;
2440 l_END_INSTALLMENT number;
2441 i number;
2442 l_rate_tbl LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE;
2443
2444 /*-----------------------------------------------------------------------+
2445 | Cursor Declarations |
2446 +-----------------------------------------------------------------------*/
2447
2448 begin
2449
2450 logmessage(fnd_log.level_statement, 'Synching rate schedule...');
2451 logmessage(fnd_log.level_statement, 'p_num_installments: ' || p_num_installments);
2452
2453 l_rate_tbl := p_rate_tbl;
2454
2455 -- finding right rate row and update it
2456 for i in REVERSE 1..l_rate_tbl.count loop
2457
2458 l_BEGIN_INSTALLMENT := l_rate_tbl(i).BEGIN_INSTALLMENT_NUMBER;
2459 l_END_INSTALLMENT := l_rate_tbl(i).END_INSTALLMENT_NUMBER;
2460
2461 logmessage(fnd_log.level_statement, i || ': ' || l_BEGIN_INSTALLMENT || ' - ' || l_END_INSTALLMENT);
2462
2463 if p_num_installments > l_END_INSTALLMENT then
2464
2465 logmessage(fnd_log.level_statement, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || p_num_installments);
2466 l_rate_tbl(i).END_INSTALLMENT_NUMBER := p_num_installments;
2467
2468 exit;
2469
2470 elsif p_num_installments >= l_BEGIN_INSTALLMENT and p_num_installments <= l_END_INSTALLMENT then
2471
2472 logmessage(fnd_log.level_statement, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || p_num_installments);
2473 l_rate_tbl(i).END_INSTALLMENT_NUMBER := p_num_installments;
2474
2475 exit;
2476
2477 elsif p_num_installments < l_BEGIN_INSTALLMENT then
2478
2479 logmessage(fnd_log.level_statement, 'Deleting this row');
2480 l_rate_tbl.delete(i);
2481
2482 end if;
2483
2484 END LOOP;
2485
2486 p_rate_tbl := l_rate_tbl;
2487 logmessage(fnd_log.level_statement, 'Done synching');
2488
2489 end;
2490
2491
2492
2493 PROCEDURE create_FEE_ASSIGNMENT(P_LOAN_ID IN NUMBER
2494 ,x_return_status OUT nocopy VARCHAR2
2495 ,x_msg_count OUT nocopy NUMBER
2496 ,x_msg_data OUT nocopy VARCHAR2)
2497 IS
2498
2499 CURSOR loan_prod_fee ( c_loan_id NUMBER ) IS
2500 select LNS_FEE_ASSIGNMENTS_S.NEXTVAL FEE_ASSIGNMENT_ID,
2501 LnsLoanHeaders.LOAN_ID,
2502 LnsFees.FEE_ID,
2503 --decode(LnsFees.RATE_TYPE,'VARIABLE', lns_fee_engine.calculateFee(LnsFees.FEE_ID,LnsLoanHeaders.LOAN_ID) ,LnsFees.FEE) FEE,
2504 LnsFees.FEE,
2505 LnsFees.FEE_TYPE,
2506 LnsFees.FEE_BASIS,
2507 LnsFees.NUMBER_GRACE_DAYS,
2508 LnsFees.COLLECTED_THIRD_PARTY_FLAG,
2509 LnsFees.RATE_TYPE,
2510 decode(LnsFees.BILLING_OPTION,'ORIGINATION',0,
2511 'SUBMIT_FOR_APPROVAL',0,
2512 'TERM_CONVERSION',0,
2513 'BILL_WITH_INSTALLMENT',1,
2514 (decode(LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(LnsLoanHeaders.LOAN_ID) ,
2515 -1 , 0 , LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(LnsLoanHeaders.LOAN_ID)) + 1 )
2516 ) BEGIN_INSTALLMENT_NUMBER,
2517 decode(LnsFees.BILLING_OPTION,'ORIGINATION',0,
2518 'SUBMIT_FOR_APPROVAL',0,
2519 'TERM_CONVERSION',0,
2520 'BILL_WITH_INSTALLMENT',1,
2521 lns_fin_utils.getnumberinstallments(LnsLoanHeaders.LOAN_ID)) END_INSTALLMENT_NUMBER,
2522
2523 NULL NUMBER_OF_PAYMENTS,
2524 LnsFees.BILLING_OPTION,
2525 NULL CREATED_BY,
2526 NULL CREATION_DATE,
2527 NULL LAST_UPDATED_BY,
2528 NULL LAST_UPDATE_DATE,
2529 NULL LAST_UPDATE_LOGIN,
2530 1 OBJECT_VERSION_NUMBER,
2531 sysdate START_DATE_ACTIVE,
2532 NULL END_DATE_ACTIVE,
2533 NULL DISB_HEADER_ID,
2534 LnsLoanProductLines.MANDATORY_FLAG,
2535 NULL OPEN_PHASE_FLAG,
2536 NULL PHASE,
2537 LnsFees.CUSTOM_PROCEDURE
2538 FROM LNS_FEES LnsFees ,
2539 LNS_LOAN_HEADERS LnsLoanHeaders ,
2540 LNS_LOAN_PRODUCT_LINES LnsLoanProductLines
2541 WHERE LnsLoanHeaders.LOAN_ID = c_loan_id
2542 AND LnsLoanHeaders.PRODUCT_ID = LnsLoanProductLines.LOAN_PRODUCT_ID
2543 AND LnsLoanProductLines.LOAN_PRODUCT_LINE_TYPE = 'FEE'
2544 AND LnsLoanProductLines.LINE_REFERENCE_ID = LnsFees.FEE_ID ;
2545
2546 CURSOR fee_account_lines ( c_fee_id NUMBER ) IS
2547 SELECT LINE_TYPE, ACCOUNT_NAME, CODE_COMBINATION_ID, ACCOUNT_TYPE, DISTRIBUTION_PERCENT, DISTRIBUTION_TYPE
2548 FROM LNS_DEFAULT_DISTRIBS
2549 WHERE ACCOUNT_NAME = 'FEE_RECEIVABLE' OR FEE_ID = c_fee_id ;
2550
2551 CURSOR current_loan_status ( c_loan_id NUMBER ) IS
2552 SELECT LOAN_STATUS , CURRENT_PHASE
2553 FROM LNS_LOAN_HEADERS LnsLoanHeaders
2554 WHERE LnsLoanHeaders.LOAN_ID = c_loan_id ;
2555
2556
2557 CURSOR loan_fee_exists ( c_loan_id NUMBER ) IS
2558 SELECT 'Y'
2559 FROM DUAL
2560 WHERE
2561 EXISTS
2562 (SELECT NULL FROM LNS_FEE_ASSIGNMENTS LnsFeeAssignments
2563 WHERE LnsFeeAssignments.LOAN_ID = c_loan_id)
2564 OR EXISTS
2565 (SELECT NULL FROM LNS_LOAN_HISTORIES_H
2566 WHERE TABLE_NAME = 'LNS_FEE_ASSIGNMENTS' AND LOAN_ID = c_loan_id) ;
2567
2568 l_fee_assignment_rec LNS_FEE_ASSIGNMENT_PUB.fee_assignment_rec_type ;
2569 l_fee_assignment_id NUMBER ;
2570 l_loan_status LNS_LOAN_HEADERS.LOAN_STATUS%TYPE ;
2571 l_loan_current_phase LNS_LOAN_HEADERS.CURRENT_PHASE%TYPE ;
2572 l_loan_fee_exists VARCHAR2(1) ;
2573
2574 l_line_type LNS_DEFAULT_DISTRIBS.LINE_TYPE%TYPE ;
2575 l_account_name LNS_DEFAULT_DISTRIBS.ACCOUNT_NAME%TYPE ;
2576 l_code_combination_id LNS_DEFAULT_DISTRIBS.CODE_COMBINATION_ID%TYPE ;
2577 l_account_type LNS_DEFAULT_DISTRIBS.ACCOUNT_TYPE%TYPE ;
2578 l_distribution_percent LNS_DEFAULT_DISTRIBS.DISTRIBUTION_PERCENT%TYPE ;
2579 l_distribution_type LNS_DEFAULT_DISTRIBS.DISTRIBUTION_TYPE%TYPE ;
2580
2581 l_return_status VARCHAR2(1) ;
2582 l_msg_count NUMBER;
2583 l_msg_data VARCHAR2(32767);
2584 l_api_name constant VARCHAR2(30) := 'create_FEE_ASSIGNMENT';
2585
2586 BEGIN
2587
2588 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' +');
2589
2590 logmessage(fnd_log.level_statement, 'Before opening cursor current_loan_status');
2591 OPEN current_loan_status(P_LOAN_ID) ;
2592 FETCH current_loan_status INTO l_loan_status ,l_loan_Current_phase ;
2593
2594 logmessage(fnd_log.level_statement, 'l_loan_status = ' || l_loan_status);
2595 logmessage(fnd_log.level_statement, 'l_loan_Current_phase = ' || l_loan_Current_phase);
2596
2597 /* If the loan current phase is not open or loan status is not Incomplete for Term loan , no fees assignment required */
2598 IF( NOT ( ( l_loan_status='INCOMPLETE' AND l_loan_current_phase = 'TERM' ) OR ( l_loan_current_phase = 'OPEN' ) ) ) THEN
2599 RETURN;
2600 END IF;
2601
2602 logmessage(fnd_log.level_statement, 'Before opening cursor loan_fee_exists');
2603 OPEN loan_fee_exists(P_LOAN_ID) ;
2604 FETCH loan_fee_exists INTO l_loan_fee_exists ;
2605
2606 logmessage(fnd_log.level_statement, 'l_loan_fee_exists = ' || l_loan_fee_exists);
2607
2608 /* If the loan fee count is not zero and there are already fees assigned to loan, no fees assignment required */
2609 IF( l_loan_fee_exists = 'Y' ) THEN
2610 RETURN;
2611 END IF;
2612
2613 logmessage(fnd_log.level_statement, 'Before opening cursor loan_prod_fee');
2614 OPEN loan_prod_fee(P_LOAN_ID) ;
2615
2616 LOOP
2617
2618 FETCH loan_prod_fee INTO l_fee_assignment_rec;
2619 EXIT WHEN loan_prod_fee%NOTFOUND;
2620
2621 l_fee_assignment_id := l_fee_assignment_rec.fee_assignment_id ;
2622
2623 logmessage(fnd_log.level_statement, 'l_fee_assignment_id = ' || l_fee_assignment_id);
2624 logmessage(fnd_log.level_statement, 'Before call to do_create_FEE_ASSIGNMENT proc for fee ' || l_fee_assignment_rec.FEE_ID);
2625
2626 IF (l_loan_Current_phase = 'OPEN'
2627 AND ( (l_fee_assignment_rec.FEE_TYPE = 'EVENT_ORIGINATION')
2628 OR ( l_fee_assignment_rec.FEE_TYPE = 'EVENT_FUNDING')
2629 )
2630 ) THEN
2631
2632 l_fee_assignment_rec.phase := 'OPEN';
2633 ELSE
2634 l_fee_assignment_rec.phase := 'TERM';
2635 END IF;
2636
2637 LNS_FEE_ASSIGNMENT_PUB.create_fee_assignment('T',
2638 l_fee_assignment_rec,
2639 l_fee_assignment_id,
2640 l_return_status,
2641 l_msg_count,
2642 l_msg_data);
2643
2644 logmessage(fnd_log.level_statement, 'l_return_status = ' || l_return_status);
2645 IF l_return_status <> 'S' THEN
2646 RAISE FND_API.G_EXC_ERROR;
2647 END IF;
2648
2649 logmessage(fnd_log.level_statement, 'Before opening cursor fee_account_lines');
2650 OPEN fee_account_lines(l_fee_assignment_rec.fee_id) ;
2651
2652 LOOP
2653
2654 FETCH fee_account_lines INTO l_line_type, l_account_name, l_code_combination_id, l_account_type, l_distribution_percent, l_distribution_type;
2655 EXIT WHEN fee_account_lines%NOTFOUND ;
2656
2657 logmessage(fnd_log.level_statement, 'l_line_type = ' || l_line_type);
2658 logmessage(fnd_log.level_statement, 'l_account_name = ' || l_account_name);
2659 logmessage(fnd_log.level_statement, 'l_code_combination_id = ' || l_code_combination_id);
2660 logmessage(fnd_log.level_statement, 'l_account_type = ' || l_account_type);
2661 logmessage(fnd_log.level_statement, 'l_distribution_percent = ' || l_distribution_percent);
2662 logmessage(fnd_log.level_statement, 'l_distribution_type = ' || l_distribution_type);
2663
2664 logmessage(fnd_log.level_statement, 'Inserting into lns_distributions...');
2665 Insert into lns_distributions
2666 (DISTRIBUTION_ID
2667 ,LOAN_ID
2668 ,LINE_TYPE
2669 ,ACCOUNT_NAME
2670 ,CODE_COMBINATION_ID
2671 ,ACCOUNT_TYPE
2672 ,DISTRIBUTION_PERCENT
2673 ,DISTRIBUTION_TYPE
2674 ,FEE_ID
2675 ,CREATION_DATE
2676 ,CREATED_BY
2677 ,LAST_UPDATE_DATE
2678 ,LAST_UPDATED_BY
2679 ,OBJECT_VERSION_NUMBER )
2680 values
2681 (LNS_DISTRIBUTIONS_S.nextval
2682 ,p_loan_id
2683 ,l_line_type
2684 ,l_account_name
2685 ,l_code_combination_id
2686 ,l_account_type
2687 ,l_distribution_percent
2688 ,l_distribution_type
2689 ,l_fee_assignment_rec.fee_id
2690 ,lns_utility_pub.creation_date
2691 ,lns_utility_pub.created_by
2692 ,lns_utility_pub.last_update_date
2693 ,lns_utility_pub.last_updated_by
2694 ,1) ;
2695 logmessage(fnd_log.level_statement, 'Done');
2696
2697 END LOOP ;
2698
2699 CLOSE fee_account_lines ;
2700
2701 END LOOP ;
2702
2703 x_return_status := fnd_api.g_ret_sts_success;
2704 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' -');
2705
2706 EXCEPTION
2707 WHEN others THEN
2708 x_return_status := fnd_api.g_ret_sts_unexp_error;
2709 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2710 END create_FEE_ASSIGNMENT;
2711
2712
2713
2714
2715 PROCEDURE validate_disbursements(P_Loan_Details_Rec IN OUT nocopy LNS_LOAN_PUB.loan_details_rec_type
2716 ,P_DISB_TBL IN OUT NOCOPY LNS_LOAN_PUB.Loan_Disb_Tbl_Type
2717 , x_return_status OUT nocopy VARCHAR2
2718 , x_msg_count OUT nocopy NUMBER
2719 , x_msg_data OUT nocopy VARCHAR2)
2720 IS
2721 l_api_name constant VARCHAR2(30) := 'validate_disbursements';
2722 l_dummy VARCHAR2(30);
2723
2724 BEGIN
2725 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' +');
2726
2727 FOR i IN 1 .. P_DISB_TBL.COUNT LOOP
2728
2729 logmessage(fnd_log.level_statement, 'Disbursement record ' || i);
2730 logmessage(fnd_log.level_statement, 'ACTIVITY_CODE = ' || P_DISB_TBL(i).ACTIVITY_CODE);
2731 logmessage(fnd_log.level_statement, 'DESCRIPTION = ' || P_DISB_TBL(i).DESCRIPTION);
2732 logmessage(fnd_log.level_statement, 'AMOUNT = ' || P_DISB_TBL(i).AMOUNT);
2733 logmessage(fnd_log.level_statement, 'DUE_DATE = ' || P_DISB_TBL(i).DUE_DATE);
2734 logmessage(fnd_log.level_statement, 'PAYEE_PARTY_ID = ' || P_DISB_TBL(i).PAYEE_PARTY_ID);
2735 logmessage(fnd_log.level_statement, 'BANK_ACCOUNT_ID = ' || P_DISB_TBL(i).BANK_ACCOUNT_ID);
2736 logmessage(fnd_log.level_statement, 'PAYMENT_METHOD_CODE = ' || P_DISB_TBL(i).PAYMENT_METHOD_CODE);
2737
2738 if P_DISB_TBL(i).ACTIVITY_CODE is null and P_DISB_TBL(i).DESCRIPTION is null then
2739 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
2740 p_token1 => 'P_DISB_TBL(' || i || ').ACTIVITY_CODE, P_DISB_TBL(' || i || ').DESCRIPTION');
2741 end if;
2742
2743 if P_DISB_TBL(i).AMOUNT is null then
2744 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
2745 p_token1 => 'P_DISB_TBL(' || i || ').AMOUNT');
2746 end if;
2747
2748 if P_DISB_TBL(i).AMOUNT < 0 or P_DISB_TBL(i).AMOUNT > P_Loan_Details_Rec.requested_amount then
2749 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
2750 , p_token1 => 'P_DISB_TBL(' || i || ').AMOUNT'
2751 , p_token2 => P_DISB_TBL(i).AMOUNT);
2752 end if;
2753
2754 if P_DISB_TBL(i).DUE_DATE is null then
2755 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
2756 p_token1 => 'P_DISB_TBL(' || i || ').DUE_DATE');
2757 end if;
2758
2759 if P_DISB_TBL(i).DUE_DATE < P_Loan_Details_Rec.loan_start_date or
2760 P_DISB_TBL(i).DUE_DATE >= P_Loan_Details_Rec.maturity_date
2761 then
2762 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
2763 , p_token1 => 'P_DISB_TBL(' || i || ').DUE_DATE'
2764 , p_token2 => P_DISB_TBL(i).DUE_DATE);
2765 end if;
2766
2767 if P_DISB_TBL(i).PAYEE_PARTY_ID is null then
2768 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
2769 p_token1 => 'P_DISB_TBL(' || i || ').PAYEE_PARTY_ID');
2770 else
2771 BEGIN
2772 SELECT 'Y'
2773 INTO l_dummy
2774 FROM hz_parties hzp
2775 WHERE hzp.party_id = P_DISB_TBL(i).PAYEE_PARTY_ID
2776 AND hzp.status = 'A';
2777 EXCEPTION
2778 WHEN NO_DATA_FOUND THEN
2779 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
2780 ,p_token1=>'P_DISB_TBL(' || i || ').PAYEE_PARTY_ID'
2781 ,p_token2=>P_DISB_TBL(i).PAYEE_PARTY_ID);
2782 END;
2783 end if;
2784
2785 END LOOP;
2786
2787 IF g_error_count > 0 THEN
2788 RAISE fnd_api.g_exc_error;
2789 END IF;
2790
2791 -- END OF BODY OF API
2792 x_return_status := fnd_api.g_ret_sts_success;
2793
2794 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' -');
2795
2796 EXCEPTION
2797 WHEN fnd_api.g_exc_error THEN
2798 x_return_status := fnd_api.g_ret_sts_error;
2799 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2800 WHEN fnd_api.g_exc_unexpected_error THEN
2801 x_return_status := fnd_api.g_ret_sts_unexp_error;
2802 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2803 WHEN others THEN
2804 x_return_status := fnd_api.g_ret_sts_unexp_error;
2805 logerrors('Exception at '||g_pkg_name || '.' || l_api_name ||' is '||sqlerrm);
2806 x_msg_count := 1;
2807 x_msg_data := sqlerrm;
2808
2809 END;
2810
2811
2812
2813 PROCEDURE CREATE_LOAN(
2814 P_API_VERSION IN NUMBER,
2815 P_INIT_MSG_LIST IN VARCHAR2,
2816 P_COMMIT IN VARCHAR2,
2817 P_VALIDATION_LEVEL IN NUMBER,
2818 P_Loan_Details_Rec IN OUT NOCOPY LNS_LOAN_PUB.Loan_Details_Rec_Type, -- mandatory
2819 P_Loan_Lines_Tbl IN OUT NOCOPY LNS_LOAN_PUB.Loan_Lines_Tbl_Type, --only for ERS loan
2820 P_DISB_TBL IN OUT NOCOPY LNS_LOAN_PUB.Loan_Disb_Tbl_Type, -- only for direct loans
2821 P_LOAN_PART_TBL IN OUT NOCOPY LNS_LOAN_PUB.LOAN_PART_TBL_TYPE, -- optional
2822 P_OPEN_RATES_TBL IN OUT NOCOPY LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE, -- optional
2823 P_TERM_RATES_TBL IN OUT NOCOPY LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE, -- optional
2824 p_loan_cust_sched_tbl IN OUT NOCOPY LNS_LOAN_PUB.loan_cust_sched_tbl_type, -- optional
2825 P_Application_id IN NUMBER,
2826 P_Created_by_module IN VARCHAR2,
2827 X_LOAN_ID OUT NOCOPY NUMBER,
2828 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2829 X_MSG_COUNT OUT NOCOPY NUMBER,
2830 X_MSG_DATA OUT NOCOPY VARCHAR2)
2831 IS
2832 /*-----------------------------------------------------------------------+
2833 | Local Variable Declarations and initializations |
2834 +-----------------------------------------------------------------------*/
2835
2836 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_LOAN';
2837 l_api_version CONSTANT NUMBER := 1.0;
2838
2839 product_not_found EXCEPTION;
2840 error_while_insert EXCEPTION;
2841 l_generate_loan_number VARCHAR2(1);
2842 l_loan_id number(15);
2843 l_loan_number VARCHAR2(60);
2844 l_header_insert_success VARCHAR2(1);
2845 l_participant_id NUMBER(15);
2846 l_part_insert_success VARCHAR2(1);
2847 l_term_id NUMBER(15);
2848 l_term_insert_success VARCHAR2(1);
2849 l_object_version_number NUMBER(15);
2850 l_ers_requested_amount NUMBER;
2851 l_term_update_success VARCHAR2(1);
2852 l_count NUMBER;
2853 l_return_status VARCHAR2(1);
2854 l_msg_count NUMBER;
2855 l_msg_data VARCHAR2(32767);
2856 l_end_installment_number NUMBER;
2857 l_pay_in_arrears_bool boolean;
2858 l_prin_pay_in_arrears_bool boolean;
2859
2860 l_loan_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
2861 l_term_rec LNS_TERMS_PUB.loan_term_rec_type;
2862 l_rate_open_rec lns_rate_schedules%ROWTYPE;
2863 l_participant_rec LNS_PARTICIPANTS_PUB.loan_participant_rec_type;
2864 l_custom_tbl lns_custom_pub.custom_tbl;
2865 l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2866 l_DISB_HEADER_REC LNS_FUNDING_PUB.LNS_DISB_HEADERS_REC;
2867 l_DISB_LINE_REC LNS_FUNDING_PUB.LNS_DISB_LINES_REC;
2868 l_CUSTOM_SET_REC LNS_CUSTOM_PUB.custom_settings_type;
2869 l_open_freq_schedule_tbl LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL;
2870 l_freq_schedule_tbl LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL;
2871 l_prin_freq_schedule_tbl LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL;
2872 l_int_freq_schedule_tbl LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL;
2873
2874 /*-----------------------------------------------------------------------+
2875 | Cursor Declarations |
2876 +-----------------------------------------------------------------------*/
2877
2878 BEGIN
2879
2880 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2881
2882 SAVEPOINT CREATE_LOAN;
2883
2884 -- Initialize message list if p_init_msg_list is set to TRUE
2885 IF fnd_api.to_boolean(p_init_msg_list) THEN
2886 fnd_msg_pub.initialize;
2887 END IF;
2888
2889 -- Initialize Collections and Variables
2890 g_errors_rec.delete;
2891 g_error_count := 0;
2892
2893 -- Get the product values
2894 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating product_id...');
2895 IF P_Loan_Details_Rec.product_id is NULL THEN
2896 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
2897 ,p_token1=>'P_Loan_Details_Rec.product_id');
2898 -- If Product not found dont bother with further processing
2899 raise product_not_found;
2900 ELSE
2901 OPEN Csr_Product_values(P_Loan_Details_Rec.product_id);
2902 FETCH Csr_Product_values
2903 INTO l_product_rec;
2904 IF Csr_Product_values%NOTFOUND THEN
2905 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
2906 ,p_token1=>'P_Loan_Details_Rec.product_id'
2907 ,p_token2=>P_Loan_Details_Rec.product_id);
2908 CLOSE Csr_Product_values;
2909 raise product_not_found;
2910 END IF;
2911 CLOSE Csr_Product_values;
2912
2913 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2914 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Product defaults:');
2915 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_type_id = ' || l_product_rec.loan_type_id);
2916 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_class_code = ' || l_product_rec.loan_class_code);
2917 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_type_name = ' || l_product_rec.loan_type_name);
2918 LogMessage(FND_LOG.LEVEL_STATEMENT, 'multiple_funding_flag = ' || l_product_rec.multiple_funding_flag);
2919 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_to_term_flag = ' || l_product_rec.open_to_term_flag);
2920 LogMessage(FND_LOG.LEVEL_STATEMENT, 'credit_review_flag = ' || l_product_rec.credit_review_flag);
2921 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_product_id = ' || l_product_rec.loan_product_id);
2922 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_product_name = ' || l_product_rec.loan_product_name);
2923 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_term = ' || l_product_rec.loan_term);
2924 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_term_period = ' || l_product_rec.loan_term_period);
2925 LogMessage(FND_LOG.LEVEL_STATEMENT, 'max_loan_term = ' || l_product_rec.max_loan_term);
2926 LogMessage(FND_LOG.LEVEL_STATEMENT, 'max_loan_term_period = ' || l_product_rec.max_loan_term_period);
2927 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_currency = ' || l_product_rec.loan_currency);
2928 LogMessage(FND_LOG.LEVEL_STATEMENT, 'requested_amount = ' || l_product_rec.requested_amount);
2929 LogMessage(FND_LOG.LEVEL_STATEMENT, 'max_requested_amount = ' || l_product_rec.max_requested_amount);
2930 LogMessage(FND_LOG.LEVEL_STATEMENT, 'index_rate_id = ' || l_product_rec.index_rate_id);
2931 LogMessage(FND_LOG.LEVEL_STATEMENT, 'rate_type = ' || l_product_rec.rate_type);
2932 LogMessage(FND_LOG.LEVEL_STATEMENT, 'spread = ' || l_product_rec.spread);
2933 LogMessage(FND_LOG.LEVEL_STATEMENT, 'floor_rate = ' || l_product_rec.floor_rate);
2934 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ceiling_rate = ' || l_product_rec.ceiling_rate);
2935 LogMessage(FND_LOG.LEVEL_STATEMENT, 'interest_compounding_freq = ' || l_product_rec.interest_compounding_freq);
2936 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_payment_frequency = ' || l_product_rec.loan_payment_frequency);
2937 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_subtype = ' || l_product_rec.loan_subtype);
2938 LogMessage(FND_LOG.LEVEL_STATEMENT, 'collateral_percent = ' || l_product_rec.collateral_percent);
2939 LogMessage(FND_LOG.LEVEL_STATEMENT, 'allow_interest_only_flag = ' || l_product_rec.allow_interest_only_flag);
2940 LogMessage(FND_LOG.LEVEL_STATEMENT, 'reamortize_over_payment = ' || l_product_rec.reamortize_over_payment);
2941 LogMessage(FND_LOG.LEVEL_STATEMENT, 'org_id = ' || l_product_rec.org_id);
2942 LogMessage(FND_LOG.LEVEL_STATEMENT, 'legal_entity_id = ' || l_product_rec.legal_entity_id);
2943 LogMessage(FND_LOG.LEVEL_STATEMENT, 'rate_change_frequency = ' || l_product_rec.rate_change_frequency);
2944 LogMessage(FND_LOG.LEVEL_STATEMENT, 'payment_application_order = ' || l_product_rec.payment_application_order);
2945 LogMessage(FND_LOG.LEVEL_STATEMENT, 'pmt_appl_order_scope = ' || l_product_rec.pmt_appl_order_scope);
2946 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_floor_rate = ' || l_product_rec.open_floor_rate);
2947 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_ceiling_rate = ' || l_product_rec.open_ceiling_rate);
2948 LogMessage(FND_LOG.LEVEL_STATEMENT, 'reamortize_under_payment = ' || l_product_rec.reamortize_under_payment);
2949 LogMessage(FND_LOG.LEVEL_STATEMENT, 'percent_increase = ' || l_product_rec.percent_increase);
2950 LogMessage(FND_LOG.LEVEL_STATEMENT, 'percent_increase_life = ' || l_product_rec.percent_increase_life);
2951 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_percent_increase = ' || l_product_rec.open_percent_increase);
2952 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_percent_increase_life = ' || l_product_rec.open_percent_increase_life);
2953 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_spread = ' || l_product_rec.open_spread);
2954 LogMessage(FND_LOG.LEVEL_STATEMENT, 'credit_review_type = ' || l_product_rec.credit_review_type);
2955 LogMessage(FND_LOG.LEVEL_STATEMENT, 'guarantor_review_type = ' || l_product_rec.guarantor_review_type);
2956 LogMessage(FND_LOG.LEVEL_STATEMENT, 'party_type = ' || l_product_rec.party_type);
2957 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_loan_term = ' || l_product_rec.open_loan_term);
2958 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_loan_term_period = ' || l_product_rec.open_loan_term_period);
2959 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_max_loan_term = ' || l_product_rec.open_max_loan_term);
2960 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_max_loan_term_period = ' || l_product_rec.open_max_loan_term_period);
2961 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CALCULATION_METHOD = ' || l_product_rec.CALCULATION_METHOD);
2962 LogMessage(FND_LOG.LEVEL_STATEMENT, 'day_count_method = ' || l_product_rec.day_count_method);
2963 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FORGIVENESS_FLAG = ' || l_product_rec.FORGIVENESS_FLAG);
2964 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FORGIVENESS_PERCENT = ' || l_product_rec.FORGIVENESS_PERCENT);
2965 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_CALC_METHOD = ' || l_product_rec.PAYMENT_CALC_METHOD);
2966 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PENAL_INT_RATE = ' || l_product_rec.PENAL_INT_RATE);
2967 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PENAL_INT_GRACE_DAYS = ' || l_product_rec.PENAL_INT_GRACE_DAYS);
2968 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CALC_ADD_INT_UNPAID_PRIN = ' || l_product_rec.CALC_ADD_INT_UNPAID_PRIN);
2969 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CALC_ADD_INT_UNPAID_INT = ' || l_product_rec.CALC_ADD_INT_UNPAID_INT);
2970 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRINCIPAL_PAYMENT_FREQUENCY = ' || l_product_rec.PRINCIPAL_PAYMENT_FREQUENCY);
2971 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REAMORTIZE_ON_FUNDING = ' || l_product_rec.REAMORTIZE_ON_FUNDING);
2972 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE_CATEGORY = ' || l_product_rec.ATTRIBUTE_CATEGORY);
2973 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE1 = ' || l_product_rec.ATTRIBUTE1);
2974 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE2 = ' || l_product_rec.ATTRIBUTE2);
2975 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE3 = ' || l_product_rec.ATTRIBUTE3);
2976 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE4 = ' || l_product_rec.ATTRIBUTE4);
2977 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE5 = ' || l_product_rec.ATTRIBUTE5);
2978 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE6 = ' || l_product_rec.ATTRIBUTE6);
2979 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE7 = ' || l_product_rec.ATTRIBUTE7);
2980 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE8 = ' || l_product_rec.ATTRIBUTE8);
2981 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE9 = ' || l_product_rec.ATTRIBUTE9);
2982 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE10 = ' || l_product_rec.ATTRIBUTE10);
2983 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE11 = ' || l_product_rec.ATTRIBUTE11);
2984 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE12 = ' || l_product_rec.ATTRIBUTE12);
2985 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE13 = ' || l_product_rec.ATTRIBUTE13);
2986 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE14 = ' || l_product_rec.ATTRIBUTE14);
2987 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE15 = ' || l_product_rec.ATTRIBUTE15);
2988 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE16 = ' || l_product_rec.ATTRIBUTE16);
2989 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE17 = ' || l_product_rec.ATTRIBUTE17);
2990 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE18 = ' || l_product_rec.ATTRIBUTE18);
2991 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE19 = ' || l_product_rec.ATTRIBUTE19);
2992 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ATTRIBUTE20 = ' || l_product_rec.ATTRIBUTE20);
2993 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUSTOM_SCHED_DATA = ' || l_product_rec.CUSTOM_SCHED_DATA);
2994 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUSTOM_CALC_METHOD = ' || l_product_rec.CUSTOM_CALC_METHOD);
2995
2996 END IF;
2997
2998 -- Get Override Allowed Flags
2999 OPEN Csr_override_flags(P_Loan_Details_Rec.product_id);
3000 FETCH Csr_override_flags
3001 into l_override_rec;
3002 CLOSE Csr_override_flags;
3003
3004 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
3005 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Override flags:');
3006 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LEGAL_ENTITY_ID_OVR = ' || l_override_rec.LEGAL_ENTITY_ID_OVR);
3007 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_TERM_OVR = ' || l_override_rec.LOAN_TERM_OVR);
3008 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_TERM_PERIOD_OVR = ' || l_override_rec.LOAN_TERM_PERIOD_OVR);
3009 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INDEX_RATE_ID_OVR = ' || l_override_rec.INDEX_RATE_ID_OVR);
3010 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RATE_TYPE_OVR = ' || l_override_rec.RATE_TYPE_OVR);
3011 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INTEREST_COMPOUNDING_FREQ_OVR = ' || l_override_rec.INTEREST_COMPOUNDING_FREQ_OVR);
3012 LogMessage(FND_LOG.LEVEL_STATEMENT, 'OPEN_SPREAD_OVR = ' || l_override_rec.OPEN_SPREAD_OVR);
3013 LogMessage(FND_LOG.LEVEL_STATEMENT, 'OPEN_FLOOR_RATE_OVR = ' || l_override_rec.OPEN_FLOOR_RATE_OVR);
3014 LogMessage(FND_LOG.LEVEL_STATEMENT, 'OPEN_CEILING_RATE_OVR = ' || l_override_rec.OPEN_CEILING_RATE_OVR);
3015 LogMessage(FND_LOG.LEVEL_STATEMENT, 'OPEN_PERCENT_INCREASE_OVR = ' || l_override_rec.OPEN_PERCENT_INCREASE_OVR);
3016 LogMessage(FND_LOG.LEVEL_STATEMENT, 'OPEN_PERCENT_INCREASE_LIFE_OVR = ' || l_override_rec.OPEN_PERCENT_INCREASE_LIFE_OVR);
3017 LogMessage(FND_LOG.LEVEL_STATEMENT, 'SPREAD_OVR = ' || l_override_rec.SPREAD_OVR);
3018 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FLOOR_RATE_OVR = ' || l_override_rec.FLOOR_RATE_OVR);
3019 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CEILING_RATE_OVR = ' || l_override_rec.CEILING_RATE_OVR);
3020 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PERCENT_INCREASE_OVR = ' || l_override_rec.PERCENT_INCREASE_OVR);
3021 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PERCENT_INCREASE_LIFE_OVR = ' || l_override_rec.PERCENT_INCREASE_LIFE_OVR);
3022 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_PAYMENT_FREQ_OVR = ' || l_override_rec.LOAN_PAYMENT_FREQ_OVR);
3023 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_SUBTYPE_OVR = ' || l_override_rec.LOAN_SUBTYPE_OVR);
3024 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REAMORTIZE_OVER_PAYMENT_OVR = ' || l_override_rec.REAMORTIZE_OVER_PAYMENT_OVR);
3025 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DAY_COUNT_METHOD_OVR = ' || l_override_rec.DAY_COUNT_METHOD_OVR);
3026 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CALCULATION_METHOD_OVR = ' || l_override_rec.CALCULATION_METHOD_OVR);
3027 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RATE_CHANGE_FREQUENCY_OVR = ' || l_override_rec.RATE_CHANGE_FREQUENCY_OVR);
3028 LogMessage(FND_LOG.LEVEL_STATEMENT, 'COLLATERAL_PERCENT_OVR = ' || l_override_rec.COLLATERAL_PERCENT_OVR);
3029 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FORGIVENESS_FLAG_OVR = ' || l_override_rec.FORGIVENESS_FLAG_OVR);
3030 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FORGIVENESS_PERCENT_OVR = ' || l_override_rec.FORGIVENESS_PERCENT_OVR);
3031 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_CALC_METHOD_OVR = ' || l_override_rec.PAYMENT_CALC_METHOD_OVR);
3032 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PENAL_INT_RATE_OVR = ' || l_override_rec.PENAL_INT_RATE_OVR);
3033 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PENAL_INT_GRACE_DAYS_OVR = ' || l_override_rec.PENAL_INT_GRACE_DAYS_OVR);
3034 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CALC_ADD_INT_UNPAID_PRIN_OVR = ' || l_override_rec.CALC_ADD_INT_UNPAID_PRIN_OVR);
3035 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CALC_ADD_INT_UNPAID_INT_OVR = ' || l_override_rec.CALC_ADD_INT_UNPAID_INT_OVR);
3036 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REAMORTIZE_ON_FUNDING_OVR = ' || l_override_rec.REAMORTIZE_ON_FUNDING_OVR);
3037 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUSTOM_CALC_METHOD_OVR = ' || l_override_rec.CUSTOM_CALC_METHOD_OVR);
3038
3039 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating P_Created_by_module...');
3040 -- Validate P_Created_by_module
3041 IF P_Created_by_module IS NULL THEN
3042 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
3043 ,p_token1=>'P_Created_by_module');
3044 END IF;
3045
3046 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating P_Application_id...');
3047 -- Validate P_Application_id
3048 IF P_Application_id IS NULL THEN
3049 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
3050 ,p_token1=>'P_Application_id');
3051 END IF;
3052
3053 IF g_error_count > 0 THEN
3054 RAISE fnd_api.g_exc_error;
3055 END IF;
3056
3057 -- validating loan details
3058 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
3059 validate_loan_header_details(P_Loan_Details_Rec
3060 ,p_loan_cust_sched_tbl
3061 ,l_return_status
3062 ,l_msg_count
3063 ,l_msg_data);
3064
3065 IF l_return_status <> 'S' THEN
3066 logmessage(fnd_log.level_unexpected, 'Validation failed in module - validate_loan_header_details()');
3067 RAISE fnd_api.g_exc_error;
3068 END IF;
3069
3070 -- validating loan participants
3071 IF p_loan_part_tbl.COUNT > 0 THEN
3072 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
3073 validate_participants(p_loan_part_tbl
3074 , l_return_status
3075 , l_msg_count
3076 , l_msg_data);
3077 IF l_return_status <> 'S' THEN
3078 logmessage(fnd_log.level_unexpected, 'Validation failed in module - validate_participants()');
3079 RAISE fnd_api.g_exc_error;
3080 END IF;
3081 END IF;
3082
3083 -- add PRIMARY_BORROWER to participants table
3084 l_count := p_loan_part_tbl.count + 1;
3085
3086 p_loan_part_tbl(l_count).HZ_PARTY_ID := P_Loan_Details_Rec.primary_borrower_party_id;
3087 p_loan_part_tbl(l_count).LOAN_PARTICIPANT_TYPE := 'PRIMARY_BORROWER';
3088 p_loan_part_tbl(l_count).START_DATE_ACTIVE := P_Loan_Details_Rec.loan_start_date;
3089 p_loan_part_tbl(l_count).END_DATE_ACTIVE := null;
3090 p_loan_part_tbl(l_count).CUST_ACCOUNT_ID := P_Loan_Details_Rec.cust_account_id;
3091 p_loan_part_tbl(l_count).BILL_TO_ACCT_SITE_ID := P_Loan_Details_Rec.bill_to_acct_site_id;
3092 p_loan_part_tbl(l_count).CONTACT_PERS_PARTY_ID := P_Loan_Details_Rec.contact_pers_party_id;
3093 p_loan_part_tbl(l_count).CONTACT_REL_PARTY_ID := P_Loan_Details_Rec.contact_rel_party_id;
3094
3095 IF l_product_rec.loan_class_code = 'ERS' THEN
3096 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
3097 IF p_loan_lines_tbl.COUNT > 0 THEN
3098 validate_loan_lines(P_Loan_Details_Rec
3099 ,p_loan_lines_tbl
3100 ,l_return_status
3101 ,l_msg_count
3102 ,l_msg_data);
3103 IF l_return_status <> 'S' THEN
3104 logmessage(fnd_log.level_unexpected, 'Validation failed in module - validate_loan_lines()');
3105 RAISE fnd_api.g_exc_error;
3106 END IF;
3107 END IF;
3108 ELSE
3109 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
3110 IF p_loan_lines_tbl.COUNT > 0 THEN
3111 validate_disbursements(P_Loan_Details_Rec
3112 ,P_DISB_TBL
3113 ,l_return_status
3114 ,l_msg_count
3115 ,l_msg_data);
3116 IF l_return_status <> 'S' THEN
3117 logmessage(fnd_log.level_unexpected, 'Validation failed in module - validate_disbursements()');
3118 RAISE fnd_api.g_exc_error;
3119 END IF;
3120 END IF;
3121 END IF;
3122
3123 if l_product_rec.multiple_funding_flag = 'Y' then
3124 -- validate open rate schedule
3125 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
3126 validate_rate_sched(P_Loan_Details_Rec
3127 ,p_open_rates_tbl
3128 ,'OPEN'
3129 ,l_return_status
3130 ,l_msg_count
3131 ,l_msg_data);
3132 IF l_return_status <> 'S' THEN
3133 logmessage(fnd_log.level_unexpected, 'Validation failed in module - open validate_rate_sched()');
3134 RAISE fnd_api.g_exc_error;
3135 END IF;
3136 end if;
3137
3138 -- validate term rate schedule
3139 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
3140 validate_rate_sched(P_Loan_Details_Rec
3141 ,p_term_rates_tbl
3142 ,'TERM'
3143 ,l_return_status
3144 ,l_msg_count
3145 ,l_msg_data);
3146 IF l_return_status <> 'S' THEN
3147 logmessage(fnd_log.level_unexpected, 'Validation failed in module - term validate_rate_sched()');
3148 RAISE fnd_api.g_exc_error;
3149 END IF;
3150
3151
3152 -- loan record
3153 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Setting l_loan_rec record...');
3154
3155 select LNS_LOAN_HEADERS_S.nextval
3156 into l_loan_rec.loan_id
3157 from dual;
3158 l_loan_rec.product_id := P_Loan_Details_Rec.product_id;
3159
3160 -- Validate Loan Number
3161 -- If Profile not set to Autogenerate make Loan Number Mandatory.
3162 l_generate_loan_number := fnd_profile.value('LNS_GENERATE_LOAN_NUMBER');
3163 IF l_generate_loan_number IS NULL THEN
3164 l_generate_loan_number := 'N';
3165 END IF;
3166 IF P_Loan_Details_Rec.loan_number is NULL AND l_generate_loan_number = 'N' THEN
3167 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
3168 ,p_token1=>'P_Loan_Details_Rec.loan_number');
3169 END IF;
3170 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_generate_loan_number = ' || l_generate_loan_number);
3171
3172 IF l_generate_loan_number = 'Y' THEN
3173 select 'L' || LNS_LOAN_NUMBER_S.nextval
3174 into P_Loan_Details_Rec.loan_number
3175 from dual;
3176 END IF;
3177 l_loan_rec.loan_number := P_Loan_Details_Rec.loan_number;
3178 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_loan_rec.loan_number = ' || l_loan_rec.loan_number);
3179
3180 l_loan_rec.loan_description := P_Loan_Details_Rec.loan_description;
3181 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_description = ' || l_loan_rec.loan_description);
3182
3183 l_loan_rec.requested_amount := P_Loan_Details_Rec.requested_amount;
3184 LogMessage(FND_LOG.LEVEL_STATEMENT, 'requested_amount = ' || l_loan_rec.requested_amount);
3185
3186 l_loan_rec.loan_start_date := P_Loan_Details_Rec.loan_start_date;
3187 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_start_date = ' || l_loan_rec.loan_start_date);
3188
3189 l_loan_rec.loan_term := P_Loan_Details_Rec.loan_term;
3190 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_term = ' || l_loan_rec.loan_term);
3191
3192 l_loan_rec.loan_term_period := P_Loan_Details_Rec.loan_term_period;
3193 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_term_period = ' || l_loan_rec.loan_term_period);
3194
3195 l_loan_rec.balloon_payment_type := P_Loan_Details_Rec.balloon_payment_type;
3196 LogMessage(FND_LOG.LEVEL_STATEMENT, 'balloon_payment_type = ' || l_loan_rec.balloon_payment_type);
3197
3198 l_loan_rec.balloon_payment_amount := P_Loan_Details_Rec.balloon_payment_amount;
3199 LogMessage(FND_LOG.LEVEL_STATEMENT, 'balloon_payment_amount = ' || l_loan_rec.balloon_payment_amount);
3200
3201 l_loan_rec.amortized_term := P_Loan_Details_Rec.balloon_term;
3202 LogMessage(FND_LOG.LEVEL_STATEMENT, 'amortized_term = ' || l_loan_rec.amortized_term);
3203
3204 l_loan_rec.amortized_term_period := P_Loan_Details_Rec.loan_term_period;
3205 LogMessage(FND_LOG.LEVEL_STATEMENT, 'amortized_term_period = ' || l_loan_rec.amortized_term_period);
3206
3207 l_loan_rec.loan_maturity_date := P_Loan_Details_Rec.maturity_date;
3208 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_maturity_date = ' || l_loan_rec.loan_maturity_date);
3209
3210 l_loan_rec.legal_entity_id := P_Loan_Details_Rec.legal_entity_id;
3211 LogMessage(FND_LOG.LEVEL_STATEMENT, 'legal_entity_id = ' || l_loan_rec.legal_entity_id);
3212
3213 P_Loan_Details_Rec.org_id := l_product_rec.org_id;
3214 l_loan_rec.org_id := P_Loan_Details_Rec.org_id;
3215 LogMessage(FND_LOG.LEVEL_STATEMENT, 'org_id = ' || l_loan_rec.org_id);
3216
3217 P_Loan_Details_Rec.loan_type_id := l_product_rec.loan_type_id;
3218 l_loan_rec.loan_type_id := P_Loan_Details_Rec.loan_type_id;
3219 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_type_id = ' || l_loan_rec.loan_type_id);
3220
3221 P_Loan_Details_Rec.loan_class_code := l_product_rec.loan_class_code;
3222 l_loan_rec.loan_class_code := P_Loan_Details_Rec.loan_class_code;
3223 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_class_code = ' || l_loan_rec.loan_class_code);
3224
3225 l_loan_rec.loan_subtype := P_Loan_Details_Rec.loan_subtype;
3226 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_subtype = ' || l_loan_rec.loan_subtype);
3227
3228 l_loan_rec.loan_application_date := P_Loan_Details_Rec.loan_application_date;
3229 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_application_date = ' || l_loan_rec.loan_application_date);
3230
3231 l_loan_rec.gl_date := P_Loan_Details_Rec.loan_start_date;
3232 LogMessage(FND_LOG.LEVEL_STATEMENT, 'gl_date = ' || l_loan_rec.gl_date);
3233
3234 l_loan_rec.loan_assigned_to := P_Loan_Details_Rec.loan_assigned_to;
3235 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_assigned_to = ' || l_loan_rec.loan_assigned_to);
3236
3237 l_loan_rec.loan_status := 'INCOMPLETE';
3238 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_status = ' || l_loan_rec.loan_status);
3239
3240 l_loan_rec.party_type := l_product_rec.party_type;
3241 LogMessage(FND_LOG.LEVEL_STATEMENT, 'party_type = ' || l_loan_rec.party_type);
3242
3243 l_loan_rec.primary_borrower_id := P_Loan_Details_Rec.primary_borrower_party_id;
3244 LogMessage(FND_LOG.LEVEL_STATEMENT, 'primary_borrower_id = ' || l_loan_rec.primary_borrower_id);
3245
3246 l_loan_rec.cust_account_id := P_Loan_Details_Rec.cust_account_id;
3247 LogMessage(FND_LOG.LEVEL_STATEMENT, 'cust_account_id = ' || l_loan_rec.cust_account_id);
3248
3249 l_loan_rec.bill_to_acct_site_id := P_Loan_Details_Rec.bill_to_acct_site_id;
3250 LogMessage(FND_LOG.LEVEL_STATEMENT, 'bill_to_acct_site_id = ' || l_loan_rec.bill_to_acct_site_id);
3251
3252 l_loan_rec.contact_pers_party_id := P_Loan_Details_Rec.contact_pers_party_id;
3253 LogMessage(FND_LOG.LEVEL_STATEMENT, 'contact_pers_party_id = ' || l_loan_rec.contact_pers_party_id);
3254
3255 l_loan_rec.contact_rel_party_id := P_Loan_Details_Rec.contact_rel_party_id;
3256 LogMessage(FND_LOG.LEVEL_STATEMENT, 'contact_rel_party_id = ' || l_loan_rec.contact_rel_party_id);
3257
3258 l_loan_rec.credit_review_flag := P_Loan_Details_Rec.credit_review_flag;
3259 LogMessage(FND_LOG.LEVEL_STATEMENT, 'credit_review_flag = ' || l_loan_rec.credit_review_flag);
3260
3261 l_loan_rec.loan_purpose_code := P_Loan_Details_Rec.loan_purpose_code;
3262 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_purpose_code = ' || l_loan_rec.loan_purpose_code);
3263
3264 l_loan_rec.collateral_percent := P_Loan_Details_Rec.collateral_percent;
3265 LogMessage(FND_LOG.LEVEL_STATEMENT, 'collateral_percent = ' || l_loan_rec.collateral_percent);
3266
3267 l_loan_rec.reference_type_id :=
3268 CASE l_product_rec.loan_class_code
3269 WHEN 'ERS' THEN P_Loan_Details_Rec.trx_type_id
3270 ELSE NULL
3271 END;
3272 LogMessage(FND_LOG.LEVEL_STATEMENT, 'reference_type_id = ' || l_loan_rec.reference_type_id);
3273
3274 l_loan_rec.current_phase :=
3275 CASE l_loan_rec.multiple_funding_flag
3276 WHEN 'Y' THEN 'OPEN'
3277 ELSE 'TERM'
3278 END;
3279 LogMessage(FND_LOG.LEVEL_STATEMENT, 'current_phase = ' || l_loan_rec.current_phase);
3280
3281 P_Loan_Details_Rec.loan_currency := l_product_rec.loan_currency;
3282 l_loan_rec.loan_currency := P_Loan_Details_Rec.loan_currency;
3283 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_currency = ' || l_loan_rec.loan_currency);
3284
3285 l_loan_rec.exchange_rate_type := P_Loan_Details_Rec.exchange_rate_type;
3286 LogMessage(FND_LOG.LEVEL_STATEMENT, 'exchange_rate_type = ' || l_loan_rec.exchange_rate_type);
3287
3288 l_loan_rec.exchange_date := P_Loan_Details_Rec.exchange_date;
3289 LogMessage(FND_LOG.LEVEL_STATEMENT, 'exchange_date = ' || l_loan_rec.exchange_date);
3290
3291 l_loan_rec.exchange_rate := P_Loan_Details_Rec.exchange_rate;
3292 LogMessage(FND_LOG.LEVEL_STATEMENT, 'exchange_rate = ' || l_loan_rec.exchange_rate);
3293
3294 l_loan_rec.FORGIVENESS_FLAG := P_Loan_Details_Rec.FORGIVENESS_FLAG;
3295 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FORGIVENESS_FLAG = ' || l_loan_rec.FORGIVENESS_FLAG);
3296
3297 l_loan_rec.FORGIVENESS_PERCENT := P_Loan_Details_Rec.FORGIVENESS_PERCENT;
3298 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FORGIVENESS_PERCENT = ' || l_loan_rec.FORGIVENESS_PERCENT);
3299
3300 l_loan_rec.multiple_funding_flag := nvl(l_product_rec.multiple_funding_flag, 'N');
3301 LogMessage(FND_LOG.LEVEL_STATEMENT, 'multiple_funding_flag = ' || l_loan_rec.multiple_funding_flag);
3302
3303 l_loan_rec.open_loan_start_date := P_Loan_Details_Rec.open_loan_start_date;
3304 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_loan_start_date = ' || l_loan_rec.open_loan_start_date);
3305
3306 l_loan_rec.open_loan_term := P_Loan_Details_Rec.open_loan_term;
3307 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_loan_term = ' || l_loan_rec.open_loan_term);
3308
3309 l_loan_rec.open_loan_term_period := P_Loan_Details_Rec.open_loan_term_period;
3310 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_loan_term_period = ' || l_loan_rec.open_loan_term_period);
3311
3312 l_loan_rec.open_maturity_date := P_Loan_Details_Rec.open_maturity_date;
3313 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_maturity_date = ' || l_loan_rec.open_maturity_date);
3314
3315 l_loan_rec.open_to_term_flag := nvl(l_product_rec.open_to_term_flag, 'N');
3316 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_to_term_flag = ' || l_loan_rec.open_to_term_flag);
3317
3318 l_loan_rec.open_to_term_event := P_Loan_Details_Rec.open_to_term_event;
3319 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_to_term_event = ' || l_loan_rec.open_to_term_event);
3320
3321 l_loan_rec.created_by_module := P_created_by_module;
3322 LogMessage(FND_LOG.LEVEL_STATEMENT, 'created_by_module = ' || l_loan_rec.created_by_module);
3323
3324 l_loan_rec.application_id := P_application_id;
3325 LogMessage(FND_LOG.LEVEL_STATEMENT, 'application_id = ' || l_loan_rec.application_id);
3326
3327 l_loan_rec.CUSTOM_PAYMENTS_FLAG := P_Loan_Details_Rec.CUSTOM_PAYMENTS_FLAG;
3328 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUSTOM_PAYMENTS_FLAG = ' || l_loan_rec.CUSTOM_PAYMENTS_FLAG);
3329
3330 l_loan_rec.attribute_category := P_Loan_Details_Rec.attribute_category;
3331 l_loan_rec.attribute1 := P_Loan_Details_Rec.attribute1;
3332 l_loan_rec.attribute2 := P_Loan_Details_Rec.attribute2;
3333 l_loan_rec.attribute3 := P_Loan_Details_Rec.attribute3;
3334 l_loan_rec.attribute4 := P_Loan_Details_Rec.attribute4;
3335 l_loan_rec.attribute5 := P_Loan_Details_Rec.attribute5;
3336 l_loan_rec.attribute6 := P_Loan_Details_Rec.attribute6;
3337 l_loan_rec.attribute7 := P_Loan_Details_Rec.attribute7;
3338 l_loan_rec.attribute8 := P_Loan_Details_Rec.attribute8;
3339 l_loan_rec.attribute9 := P_Loan_Details_Rec.attribute9;
3340 l_loan_rec.attribute10 := P_Loan_Details_Rec.attribute10;
3341 l_loan_rec.attribute11 := P_Loan_Details_Rec.attribute11;
3342 l_loan_rec.attribute12 := P_Loan_Details_Rec.attribute12;
3343 l_loan_rec.attribute13 := P_Loan_Details_Rec.attribute13;
3344 l_loan_rec.attribute14 := P_Loan_Details_Rec.attribute14;
3345 l_loan_rec.attribute15 := P_Loan_Details_Rec.attribute15;
3346 l_loan_rec.attribute16 := P_Loan_Details_Rec.attribute16;
3347 l_loan_rec.attribute17 := P_Loan_Details_Rec.attribute17;
3348 l_loan_rec.attribute18 := P_Loan_Details_Rec.attribute18;
3349 l_loan_rec.attribute19 := P_Loan_Details_Rec.attribute19;
3350 l_loan_rec.attribute20 := P_Loan_Details_Rec.attribute20;
3351
3352
3353 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Setting l_term_rec record...');
3354
3355 -- term record
3356 l_term_rec.loan_id := l_loan_rec.loan_id;
3357
3358 select LNS_TERMS_S.nextval
3359 into l_term_rec.term_id
3360 from dual;
3361 LogMessage(FND_LOG.LEVEL_STATEMENT, 'term_id = ' || l_term_rec.term_id);
3362
3363 l_term_rec.rate_type := P_Loan_Details_Rec.RATE_TYPE;
3364 LogMessage(FND_LOG.LEVEL_STATEMENT, 'rate_type = ' || l_term_rec.rate_type);
3365
3366 l_term_rec.index_rate_id := P_Loan_Details_Rec.index_rate_id;
3367 LogMessage(FND_LOG.LEVEL_STATEMENT, 'index_rate_id = ' || l_term_rec.index_rate_id);
3368
3369 l_term_rec.calculation_method := P_Loan_Details_Rec.CALCULATION_METHOD;
3370 LogMessage(FND_LOG.LEVEL_STATEMENT, 'calculation_method = ' || l_term_rec.calculation_method);
3371
3372 l_term_rec.day_count_method := P_Loan_Details_Rec.day_count_method;
3373 LogMessage(FND_LOG.LEVEL_STATEMENT, 'day_count_method = ' || l_term_rec.day_count_method);
3374
3375 l_term_rec.loan_payment_frequency := P_Loan_Details_Rec.loan_payment_frequency;
3376 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_payment_frequency = ' || l_term_rec.loan_payment_frequency);
3377
3378 l_term_rec.PAYMENT_CALC_METHOD := P_Loan_Details_Rec.PAYMENT_CALC_METHOD;
3379 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_CALC_METHOD = ' || l_term_rec.PAYMENT_CALC_METHOD);
3380
3381 l_term_rec.CUSTOM_CALC_METHOD := P_Loan_Details_Rec.CUSTOM_CALC_METHOD;
3382 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUSTOM_CALC_METHOD = ' || l_term_rec.CUSTOM_CALC_METHOD);
3383
3384 l_term_rec.amortization_frequency := l_term_rec.loan_payment_frequency;
3385 LogMessage(FND_LOG.LEVEL_STATEMENT, 'amortization_frequency = ' || l_term_rec.amortization_frequency);
3386
3387 l_term_rec.interest_compounding_freq := P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ;
3388 LogMessage(FND_LOG.LEVEL_STATEMENT, 'interest_compounding_freq = ' || l_term_rec.interest_compounding_freq);
3389
3390 l_term_rec.first_payment_date := P_Loan_Details_Rec.first_payment_date;
3391 LogMessage(FND_LOG.LEVEL_STATEMENT, 'first_payment_date = ' || l_term_rec.first_payment_date);
3392
3393 l_term_rec.PRIN_FIRST_PAY_DATE := P_Loan_Details_Rec.PRIN_FIRST_PAY_DATE;
3394 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIN_FIRST_PAY_DATE = ' || l_term_rec.PRIN_FIRST_PAY_DATE);
3395
3396 l_term_rec.PRIN_PAYMENT_FREQUENCY := P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY;
3397 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIN_PAYMENT_FREQUENCY = ' || l_term_rec.PRIN_PAYMENT_FREQUENCY);
3398
3399 l_term_rec.floor_rate := P_Loan_Details_Rec.floor_rate;
3400 LogMessage(FND_LOG.LEVEL_STATEMENT, 'floor_rate = ' || l_term_rec.floor_rate);
3401
3402 l_term_rec.ceiling_rate := P_Loan_Details_Rec.ceiling_rate;
3403 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ceiling_rate = ' || l_term_rec.ceiling_rate);
3404
3405 l_term_rec.percent_increase := P_Loan_Details_Rec.percent_increase;
3406 LogMessage(FND_LOG.LEVEL_STATEMENT, 'percent_increase = ' || l_term_rec.percent_increase);
3407
3408 l_term_rec.percent_increase_life := P_Loan_Details_Rec.percent_increase_life;
3409 LogMessage(FND_LOG.LEVEL_STATEMENT, 'percent_increase_life = ' || l_term_rec.percent_increase_life);
3410
3411 l_term_rec.open_first_payment_date := P_Loan_Details_Rec.open_first_payment_date;
3412 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_first_payment_date = ' || l_term_rec.open_first_payment_date);
3413
3414 l_term_rec.open_payment_frequency := P_Loan_Details_Rec.open_payment_frequency;
3415 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_payment_frequency = ' || l_term_rec.open_payment_frequency);
3416
3417 l_term_rec.open_floor_rate := P_Loan_Details_Rec.open_floor_rate;
3418 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_floor_rate = ' || l_term_rec.open_floor_rate);
3419
3420 l_term_rec.open_ceiling_rate := P_Loan_Details_Rec.open_ceiling_rate;
3421 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_ceiling_rate = ' || l_term_rec.open_ceiling_rate);
3422
3423 l_term_rec.open_percent_increase := P_Loan_Details_Rec.open_percent_increase;
3424 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_percent_increase = ' || l_term_rec.open_percent_increase);
3425
3426 l_term_rec.open_percent_increase_life := P_Loan_Details_Rec.open_percent_increase_life;
3427 LogMessage(FND_LOG.LEVEL_STATEMENT, 'open_percent_increase_life = ' || l_term_rec.open_percent_increase_life);
3428
3429 l_term_rec.based_on_balance := 'PRIN';
3430
3431 l_term_rec.reamortize_over_payment := P_Loan_Details_Rec.reamortize_over_payment;
3432 LogMessage(FND_LOG.LEVEL_STATEMENT, 'reamortize_over_payment = ' || l_term_rec.reamortize_over_payment);
3433
3434 l_term_rec.reamortize_under_payment := l_product_rec.reamortize_under_payment;
3435 LogMessage(FND_LOG.LEVEL_STATEMENT, 'reamortize_under_payment = ' || l_term_rec.reamortize_under_payment);
3436
3437 l_term_rec.DELINQUENCY_THRESHOLD_AMOUNT := P_Loan_Details_Rec.DELINQUENCY_THRESHOLD_AMOUNT;
3438 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DELINQUENCY_THRESHOLD_AMOUNT = ' || l_term_rec.DELINQUENCY_THRESHOLD_AMOUNT);
3439
3440 l_term_rec.DEFAULT_THRESHOLD_AMOUNT := P_Loan_Details_Rec.DEFAULT_THRESHOLD_AMOUNT;
3441 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DEFAULT_THRESHOLD_AMOUNT = ' || l_term_rec.DEFAULT_THRESHOLD_AMOUNT);
3442
3443 l_term_rec.payment_application_order := nvl(P_Loan_Details_Rec.payment_application_order, l_product_rec.payment_application_order);
3444 LogMessage(FND_LOG.LEVEL_STATEMENT, 'payment_application_order = ' || l_term_rec.payment_application_order);
3445
3446 l_term_rec.pmt_appl_order_scope := nvl(P_Loan_Details_Rec.pmt_appl_order_scope, l_product_rec.pmt_appl_order_scope);
3447 LogMessage(FND_LOG.LEVEL_STATEMENT, 'pmt_appl_order_scope = ' || l_term_rec.pmt_appl_order_scope);
3448
3449 l_term_rec.LOCK_IN_DATE := P_Loan_Details_Rec.LOCK_DATE;
3450 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOCK_IN_DATE = ' || l_term_rec.LOCK_IN_DATE);
3451
3452 l_term_rec.LOCK_TO_DATE := P_Loan_Details_Rec.LOCK_EXP_DATE;
3453 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOCK_TO_DATE = ' || l_term_rec.LOCK_TO_DATE);
3454
3455 l_term_rec.PENAL_INT_RATE := P_Loan_Details_Rec.PENAL_INT_RATE;
3456 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PENAL_INT_RATE = ' || l_term_rec.PENAL_INT_RATE);
3457
3458 l_term_rec.PENAL_INT_GRACE_DAYS := P_Loan_Details_Rec.PENAL_INT_GRACE_DAYS;
3459 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PENAL_INT_GRACE_DAYS = ' || l_term_rec.PENAL_INT_GRACE_DAYS);
3460
3461 l_term_rec.CALC_ADD_INT_UNPAID_PRIN := P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_PRIN;
3462 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CALC_ADD_INT_UNPAID_PRIN = ' || l_term_rec.CALC_ADD_INT_UNPAID_PRIN);
3463
3464 l_term_rec.CALC_ADD_INT_UNPAID_INT := P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_INT;
3465 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CALC_ADD_INT_UNPAID_INT = ' || l_term_rec.CALC_ADD_INT_UNPAID_INT);
3466
3467 l_term_rec.REAMORTIZE_ON_FUNDING := P_Loan_Details_Rec.REAMORTIZE_ON_FUNDING;
3468 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REAMORTIZE_ON_FUNDING = ' || l_term_rec.REAMORTIZE_ON_FUNDING);
3469
3470 if l_product_rec.multiple_funding_flag = 'Y' then
3471 /*
3472 if trunc(P_Loan_Details_Rec.open_first_payment_date) <> trunc(P_Loan_Details_Rec.open_loan_start_date) then
3473 l_pay_in_arrears_bool := true;
3474 else
3475 l_pay_in_arrears_bool := false;
3476 end if;
3477
3478 l_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
3479 p_loan_start_date => P_Loan_Details_Rec.open_loan_start_date
3480 ,p_loan_maturity_date => P_Loan_Details_Rec.open_maturity_date
3481 ,p_first_pay_date => P_Loan_Details_Rec.open_first_payment_date
3482 ,p_num_intervals => null
3483 ,p_interval_type => P_Loan_Details_Rec.loan_payment_frequency
3484 ,p_pay_in_arrears => l_pay_in_arrears_bool);
3485 */
3486 l_open_freq_schedule_tbl(1).COMPONENT := 'PRIN_INT';
3487 l_open_freq_schedule_tbl(1).PERIOD_BEGIN_DATE := P_Loan_Details_Rec.open_first_payment_date;
3488 l_open_freq_schedule_tbl(1).FREQUENCY := P_Loan_Details_Rec.loan_payment_frequency;
3489
3490 l_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
3491 p_loan_start_date => P_Loan_Details_Rec.open_loan_start_date,
3492 p_loan_maturity_date => P_Loan_Details_Rec.open_maturity_date,
3493 p_freq_schedule_tbl => l_open_freq_schedule_tbl);
3494
3495 l_end_installment_number := l_payment_tbl.count;
3496 logmessage(fnd_log.level_statement, 'Open l_end_installment_number = ' || l_end_installment_number);
3497
3498 logmessage(fnd_log.level_statement, 'Synchronizing Open Rate Schedule...');
3499 synchRateSchedule(p_open_rates_tbl, l_end_installment_number);
3500
3501 end if;
3502
3503 IF P_Loan_Details_Rec.custom_payments_flag = 'Y' THEN
3504
3505 IF P_Loan_Details_Rec.PAYMENT_CALC_METHOD = 'CUSTOM' and p_loan_cust_sched_tbl.COUNT = 0 THEN
3506
3507 LNS_CUSTOM_PUB.parseClob(
3508 P_API_VERSION => 1.0,
3509 P_INIT_MSG_LIST => FND_API.G_TRUE,
3510 P_COMMIT => FND_API.G_FALSE,
3511 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3512 P_CLOB => l_product_rec.CUSTOM_SCHED_DATA,
3513 P_RETAIN_DATA => 'N',
3514 X_CUSTOM_SET_REC => l_CUSTOM_SET_REC,
3515 X_CUSTOM_TBL => l_custom_tbl,
3516 x_return_status => l_return_status,
3517 x_msg_count => l_msg_count,
3518 x_msg_data => l_msg_data);
3519
3520 IF l_return_status <> 'S' THEN
3521 RAISE fnd_api.g_exc_error;
3522 END IF;
3523
3524 l_end_installment_number := l_custom_tbl.COUNT;
3525
3526 ELSE
3527 l_end_installment_number := p_loan_cust_sched_tbl.COUNT;
3528 END IF;
3529
3530 ELSE
3531
3532 IF P_Loan_Details_Rec.PAYMENT_CALC_METHOD = 'SEPARATE_SCHEDULES' THEN
3533 /*
3534 if trunc(P_Loan_Details_Rec.first_payment_date) <> trunc(P_Loan_Details_Rec.loan_start_date) then
3535 l_pay_in_arrears_bool := true;
3536 else
3537 l_pay_in_arrears_bool := false;
3538 end if;
3539
3540 if trunc(P_Loan_Details_Rec.PRIN_FIRST_PAY_DATE) <> trunc(P_Loan_Details_Rec.loan_start_date) then
3541 l_prin_pay_in_arrears_bool := true;
3542 else
3543 l_prin_pay_in_arrears_bool := false;
3544 end if;
3545
3546 l_payment_tbl := LNS_FIN_UTILS.buildSIPPaymentSchedule(
3547 p_loan_start_date => P_Loan_Details_Rec.loan_start_date
3548 ,p_loan_maturity_date => P_Loan_Details_Rec.maturity_date
3549 ,p_int_first_pay_date => P_Loan_Details_Rec.first_payment_date
3550 ,p_int_num_intervals => null
3551 ,p_int_interval_type => P_Loan_Details_Rec.loan_payment_frequency
3552 ,p_int_pay_in_arrears => l_pay_in_arrears_bool
3553 ,p_prin_first_pay_date => P_Loan_Details_Rec.PRIN_FIRST_PAY_DATE
3554 ,p_prin_num_intervals => null
3555 ,p_prin_interval_type => P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY
3556 ,p_prin_pay_in_arrears => l_prin_pay_in_arrears_bool);
3557 */
3558 l_int_freq_schedule_tbl(1).COMPONENT := 'INT';
3559 l_int_freq_schedule_tbl(1).PERIOD_BEGIN_DATE := P_Loan_Details_Rec.first_payment_date;
3560 l_int_freq_schedule_tbl(1).FREQUENCY := P_Loan_Details_Rec.loan_payment_frequency;
3561
3562 l_prin_freq_schedule_tbl(1).COMPONENT := 'PRIN';
3563 l_prin_freq_schedule_tbl(1).PERIOD_BEGIN_DATE := P_Loan_Details_Rec.PRIN_FIRST_PAY_DATE;
3564 l_prin_freq_schedule_tbl(1).FREQUENCY := P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY;
3565
3566 l_payment_tbl := LNS_FIN_UTILS.buildSIPPaymentSchedule(
3567 p_loan_start_date => P_Loan_Details_Rec.loan_start_date,
3568 p_loan_maturity_date => P_Loan_Details_Rec.maturity_date,
3569 p_prin_freq_schedule_tbl => l_prin_freq_schedule_tbl,
3570 p_int_freq_schedule_tbl => l_int_freq_schedule_tbl);
3571
3572 l_end_installment_number := l_payment_tbl.count;
3573
3574 ELSE
3575 /*
3576 if trunc(P_Loan_Details_Rec.first_payment_date) <> trunc(P_Loan_Details_Rec.loan_start_date) then
3577 l_pay_in_arrears_bool := true;
3578 else
3579 l_pay_in_arrears_bool := false;
3580 end if;
3581
3582 l_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
3583 p_loan_start_date => P_Loan_Details_Rec.loan_start_date
3584 ,p_loan_maturity_date => P_Loan_Details_Rec.maturity_date
3585 ,p_first_pay_date => P_Loan_Details_Rec.first_payment_date
3586 ,p_num_intervals => null
3587 ,p_interval_type => P_Loan_Details_Rec.loan_payment_frequency
3588 ,p_pay_in_arrears => l_pay_in_arrears_bool);
3589 */
3590 l_freq_schedule_tbl(1).COMPONENT := 'PRIN_INT';
3591 l_freq_schedule_tbl(1).PERIOD_BEGIN_DATE := P_Loan_Details_Rec.first_payment_date;
3592 l_freq_schedule_tbl(1).FREQUENCY := P_Loan_Details_Rec.loan_payment_frequency;
3593
3594 l_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
3595 p_loan_start_date => P_Loan_Details_Rec.loan_start_date,
3596 p_loan_maturity_date => P_Loan_Details_Rec.maturity_date,
3597 p_freq_schedule_tbl => l_freq_schedule_tbl);
3598
3599 l_end_installment_number := l_payment_tbl.count;
3600
3601 END IF;
3602 END IF;
3603
3604 if l_end_installment_number is null or l_end_installment_number = 0 then
3605 l_end_installment_number := 1;
3606 end if;
3607
3608 logmessage(fnd_log.level_statement, 'Term l_end_installment_number = ' || l_end_installment_number);
3609 logmessage(fnd_log.level_statement, 'Synchronizing Term Rate Schedule...');
3610 synchRateSchedule(p_term_rates_tbl, l_end_installment_number);
3611
3612 IF g_error_count > 0 THEN
3613 RAISE fnd_api.g_exc_error;
3614 END IF;
3615
3616 -- Create Loan Header
3617 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_LOAN_HEADER_PUB.create_loan...');
3618 LNS_LOAN_HEADER_PUB.create_loan (
3619 p_init_msg_list => 'T'
3620 ,p_loan_header_rec => l_loan_rec
3621 ,x_loan_id => l_loan_id
3622 ,x_loan_number => l_loan_number
3623 ,x_return_status => l_header_insert_success
3624 ,x_msg_count => l_msg_count
3625 ,x_msg_data => l_msg_data
3626 );
3627 IF l_header_insert_success <> 'S' THEN
3628 LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_HEADER'
3629 ,p_token1 => l_msg_data);
3630 END IF;
3631
3632 IF g_error_count > 0 THEN
3633 RAISE fnd_api.g_exc_error;
3634 END IF;
3635
3636 logmessage(fnd_log.level_unexpected, 'Loan object created successfully');
3637 logmessage(fnd_log.level_unexpected, 'New loan_id: ' || l_loan_id);
3638
3639 -- Create participant records
3640 FOR l_count IN 1 .. p_loan_part_tbl.COUNT
3641 LOOP
3642 l_participant_rec.loan_id := l_loan_rec.loan_id;
3643 l_participant_rec.hz_party_id := p_loan_part_tbl(l_count).hz_party_id;
3644 l_participant_rec.loan_participant_type := p_loan_part_tbl(l_count).loan_participant_type;
3645 l_participant_rec.start_date_active := p_loan_part_tbl(l_count).start_date_active;
3646 l_participant_rec.end_date_active := p_loan_part_tbl(l_count).end_date_active;
3647 l_participant_rec.cust_account_id := p_loan_part_tbl(l_count).cust_account_id;
3648 l_participant_rec.bill_to_acct_site_id := p_loan_part_tbl(l_count).bill_to_acct_site_id;
3649 l_participant_rec.contact_rel_party_id := p_loan_part_tbl(l_count).contact_rel_party_id;
3650 l_participant_rec.contact_pers_party_id := p_loan_part_tbl(l_count).contact_pers_party_id;
3651
3652 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_PARTICIPANTS_PUB.createParticipant...');
3653 LNS_PARTICIPANTS_PUB.createParticipant (
3654 p_init_msg_list => 'T'
3655 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
3656 ,p_loan_participant_rec => l_participant_rec
3657 ,x_participant_id => l_participant_id
3658 ,x_return_status => l_part_insert_success
3659 ,x_msg_count => l_msg_count
3660 ,x_msg_data => l_msg_data
3661 );
3662 IF l_part_insert_success <> 'S' THEN
3663 LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_PART'
3664 ,p_token1 =>l_msg_data);
3665 ELSE
3666 logmessage(fnd_log.level_statement, 'Participant object created successfully');
3667 logmessage(fnd_log.level_statement, 'New participant_id: ' || l_participant_id);
3668 END IF;
3669 END LOOP;
3670
3671 IF g_error_count > 0 THEN
3672 RAISE fnd_api.g_exc_error;
3673 END IF;
3674
3675 logmessage(fnd_log.level_unexpected, 'All participant objects created successfully');
3676
3677 -- Create Term Record
3678 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_TERMS_PUB.create_term...');
3679 LNS_TERMS_PUB.create_term (
3680 p_init_msg_list => 'T'
3681 ,p_loan_term_rec => l_term_rec
3682 ,x_term_id => l_term_id
3683 ,x_return_status => l_term_insert_success
3684 ,x_msg_count => l_msg_count
3685 ,x_msg_data => l_msg_data
3686 );
3687 IF l_term_insert_success <> 'S' THEN
3688 LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_TERM'
3689 ,p_token1 =>l_msg_data);
3690 END IF;
3691
3692 IF g_error_count > 0 THEN
3693 RAISE fnd_api.g_exc_error;
3694 END IF;
3695
3696 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Loan term object created successfully');
3697
3698 -- Create LNS_FREQ_SCHEDULES records
3699 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting into LNS_FREQ_SCHEDULES...');
3700 BEGIN
3701 if l_open_freq_schedule_tbl is not null then
3702 insert into LNS_FREQ_SCHEDULES
3703 (FREQ_SCHEDULE_ID,
3704 LOAN_ID,
3705 PHASE,
3706 COMPONENT,
3707 FREQUENCY_BEGIN_DATE,
3708 FREQUENCY,
3709 OBJECT_VERSION_NUMBER,
3710 CREATION_DATE,
3711 CREATED_BY,
3712 LAST_UPDATE_DATE,
3713 LAST_UPDATED_BY,
3714 LAST_UPDATE_LOGIN)
3715 values
3716 (LNS_FREQ_SCHEDULES_S.nextval,
3717 l_loan_rec.loan_id,
3718 'OPEN',
3719 'PRIN_INT',
3720 l_open_freq_schedule_tbl(1).PERIOD_BEGIN_DATE,
3721 l_open_freq_schedule_tbl(1).FREQUENCY,
3722 1,
3723 sysdate,
3724 lns_utility_pub.created_by,
3725 sysdate,
3726 lns_utility_pub.last_updated_by,
3727 lns_utility_pub.LAST_UPDATE_LOGIN);
3728 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Inserted Open phase record');
3729 end if;
3730
3731 -- insert term phase record(s)
3732 if P_Loan_Details_Rec.PAYMENT_CALC_METHOD = 'SEPARATE_SCHEDULES' then
3733 -- insert 2 records if its SEPARATE_SCHEDULES
3734 insert into LNS_FREQ_SCHEDULES
3735 (FREQ_SCHEDULE_ID,
3736 LOAN_ID,
3737 PHASE,
3738 COMPONENT,
3739 FREQUENCY_BEGIN_DATE,
3740 FREQUENCY,
3741 OBJECT_VERSION_NUMBER,
3742 CREATION_DATE,
3743 CREATED_BY,
3744 LAST_UPDATE_DATE,
3745 LAST_UPDATED_BY,
3746 LAST_UPDATE_LOGIN)
3747 values
3748 (LNS_FREQ_SCHEDULES_S.nextval,
3749 l_loan_rec.loan_id,
3750 'TERM',
3751 'INT',
3752 l_int_freq_schedule_tbl(1).PERIOD_BEGIN_DATE,
3753 l_int_freq_schedule_tbl(1).FREQUENCY,
3754 1,
3755 sysdate,
3756 lns_utility_pub.created_by,
3757 sysdate,
3758 lns_utility_pub.last_updated_by,
3759 lns_utility_pub.LAST_UPDATE_LOGIN);
3760 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Inserted INT record');
3761
3762 insert into LNS_FREQ_SCHEDULES
3763 (FREQ_SCHEDULE_ID,
3764 LOAN_ID,
3765 PHASE,
3766 COMPONENT,
3767 FREQUENCY_BEGIN_DATE,
3768 FREQUENCY,
3769 OBJECT_VERSION_NUMBER,
3770 CREATION_DATE,
3771 CREATED_BY,
3772 LAST_UPDATE_DATE,
3773 LAST_UPDATED_BY,
3774 LAST_UPDATE_LOGIN)
3775 values
3776 (LNS_FREQ_SCHEDULES_S.nextval,
3777 l_loan_id,
3778 'TERM',
3779 'PRIN',
3780 l_prin_freq_schedule_tbl(1).PERIOD_BEGIN_DATE,
3781 l_prin_freq_schedule_tbl(1).FREQUENCY,
3782 1,
3783 sysdate,
3784 lns_utility_pub.created_by,
3785 sysdate,
3786 lns_utility_pub.last_updated_by,
3787 lns_utility_pub.LAST_UPDATE_LOGIN);
3788 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Inserted PRIN record');
3789 else
3790 -- insert 1 record if its others
3791 insert into LNS_FREQ_SCHEDULES
3792 (FREQ_SCHEDULE_ID,
3793 LOAN_ID,
3794 PHASE,
3795 COMPONENT,
3796 FREQUENCY_BEGIN_DATE,
3797 FREQUENCY,
3798 OBJECT_VERSION_NUMBER,
3799 CREATION_DATE,
3800 CREATED_BY,
3801 LAST_UPDATE_DATE,
3802 LAST_UPDATED_BY,
3803 LAST_UPDATE_LOGIN)
3804 values
3805 (LNS_FREQ_SCHEDULES_S.nextval,
3806 l_loan_id,
3807 'TERM',
3808 'PRIN_INT',
3809 l_freq_schedule_tbl(1).PERIOD_BEGIN_DATE,
3810 l_freq_schedule_tbl(1).FREQUENCY,
3811 1,
3812 sysdate,
3813 lns_utility_pub.created_by,
3814 sysdate,
3815 lns_utility_pub.last_updated_by,
3816 lns_utility_pub.LAST_UPDATE_LOGIN);
3817 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Inserted PRIN_INT record');
3818 end if;
3819 EXCEPTION
3820 WHEN OTHERS THEN
3821 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
3822 LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_TERMRATE'
3823 ,p_token1 => sqlerrm);
3824 END;
3825
3826 IF g_error_count > 0 THEN
3827 RAISE fnd_api.g_exc_error;
3828 END IF;
3829
3830 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Frequency schedule created successfully');
3831
3832 -- Create Term Rate Schedule Records
3833 FOR l_count IN 1 .. P_TERM_RATES_TBL.COUNT
3834 LOOP
3835 BEGIN
3836 insert into LNS_RATE_SCHEDULES
3837 (RATE_ID
3838 ,TERM_ID
3839 ,INDEX_RATE
3840 ,SPREAD
3841 ,CURRENT_INTEREST_RATE
3842 ,START_DATE_ACTIVE
3843 ,END_DATE_ACTIVE
3844 ,CREATED_BY
3845 ,CREATION_DATE
3846 ,LAST_UPDATED_BY
3847 ,LAST_UPDATE_DATE
3848 ,LAST_UPDATE_LOGIN
3849 ,OBJECT_VERSION_NUMBER
3850 ,INDEX_DATE
3851 ,BEGIN_INSTALLMENT_NUMBER
3852 ,END_INSTALLMENT_NUMBER
3853 ,INTEREST_ONLY_FLAG
3854 ,PHASE
3855 ,FLOATING_FLAG)
3856 VALUES
3857 (LNS_RATE_SCHEDULES_S.nextval
3858 ,l_term_rec.term_id
3859 ,P_TERM_RATES_TBL(l_Count).INDEX_RATE
3860 ,nvl(P_TERM_RATES_TBL(l_Count).SPREAD, 0)
3861 ,(P_TERM_RATES_TBL(l_Count).INDEX_RATE + nvl(P_TERM_RATES_TBL(l_Count).SPREAD, 0))
3862 ,sysdate
3863 ,null
3864 ,lns_utility_pub.created_by
3865 ,sysdate
3866 ,lns_utility_pub.last_updated_by
3867 ,sysdate
3868 ,lns_utility_pub.LAST_UPDATE_LOGIN
3869 ,1
3870 ,P_TERM_RATES_TBL(l_Count).INDEX_DATE
3871 ,P_TERM_RATES_TBL(l_Count).BEGIN_INSTALLMENT_NUMBER
3872 ,P_TERM_RATES_TBL(l_Count).END_INSTALLMENT_NUMBER
3873 ,P_TERM_RATES_TBL(l_Count).INTEREST_ONLY_FLAG
3874 ,'TERM'
3875 ,null);
3876 EXCEPTION
3877 WHEN OTHERS THEN
3878 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
3879 LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_TERMRATE'
3880 ,p_token1 => sqlerrm);
3881 END;
3882 END LOOP;
3883
3884 IF g_error_count > 0 THEN
3885 RAISE fnd_api.g_exc_error;
3886 END IF;
3887
3888 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Term rate schedule created successfully');
3889
3890 -- Create Open Rate Schedule Records (if needed)
3891 IF l_product_rec.multiple_funding_flag = 'Y' THEN
3892 FOR l_count IN 1 .. P_OPEN_RATES_TBL.COUNT
3893 LOOP
3894 BEGIN
3895 insert into LNS_RATE_SCHEDULES
3896 (RATE_ID
3897 ,TERM_ID
3898 ,INDEX_RATE
3899 ,SPREAD
3900 ,CURRENT_INTEREST_RATE
3901 ,START_DATE_ACTIVE
3902 ,END_DATE_ACTIVE
3903 ,CREATED_BY
3904 ,CREATION_DATE
3905 ,LAST_UPDATED_BY
3906 ,LAST_UPDATE_DATE
3907 ,LAST_UPDATE_LOGIN
3908 ,OBJECT_VERSION_NUMBER
3909 ,INDEX_DATE
3910 ,BEGIN_INSTALLMENT_NUMBER
3911 ,END_INSTALLMENT_NUMBER
3912 ,INTEREST_ONLY_FLAG
3913 ,PHASE
3914 ,FLOATING_FLAG)
3915 VALUES
3916 (LNS_RATE_SCHEDULES_S.nextval
3917 ,l_term_rec.term_id
3918 ,P_OPEN_RATES_TBL(l_Count).INDEX_RATE
3919 ,nvl(P_OPEN_RATES_TBL(l_Count).SPREAD, 0)
3920 ,(P_OPEN_RATES_TBL(l_Count).INDEX_RATE + nvl(P_OPEN_RATES_TBL(l_Count).SPREAD, 0))
3921 ,sysdate
3922 ,null
3923 ,lns_utility_pub.created_by
3924 ,sysdate
3925 ,lns_utility_pub.last_updated_by
3926 ,sysdate
3927 ,lns_utility_pub.LAST_UPDATE_LOGIN
3928 ,1
3929 ,P_OPEN_RATES_TBL(l_Count).INDEX_DATE
3930 ,P_OPEN_RATES_TBL(l_Count).BEGIN_INSTALLMENT_NUMBER
3931 ,P_OPEN_RATES_TBL(l_Count).END_INSTALLMENT_NUMBER
3932 ,P_OPEN_RATES_TBL(l_Count).INTEREST_ONLY_FLAG
3933 ,'OPEN'
3934 ,null);
3935 EXCEPTION
3936 WHEN OTHERS THEN
3937 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
3938 LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_TERMRATE'
3939 ,p_token1 => sqlerrm);
3940 END;
3941 END LOOP;
3942
3943 IF g_error_count > 0 THEN
3944 RAISE fnd_api.g_exc_error;
3945 END IF;
3946
3947 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Open rate schedule created successfully');
3948
3949 END IF;
3950
3951 -- Default fees from the product
3952 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling create_FEE_ASSIGNMENT...');
3953 create_FEE_ASSIGNMENT(P_LOAN_ID => l_loan_rec.loan_id
3954 ,x_return_status => l_return_status
3955 ,x_msg_count => l_msg_count
3956 ,x_msg_data => l_msg_data);
3957 IF l_return_status <> 'S' THEN
3958 RAISE fnd_api.g_exc_error;
3959 END IF;
3960
3961 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Default fees created successfully');
3962
3963 -- Default Product Conditions
3964 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_COND_ASSIGNMENT_PUB.create_LP_COND_ASSIGNMENT...');
3965 LNS_COND_ASSIGNMENT_PUB.create_LP_COND_ASSIGNMENT(l_loan_rec.loan_id);
3966
3967 IF g_error_count > 0 THEN
3968 RAISE fnd_api.g_exc_error;
3969 END IF;
3970
3971 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Default conditions created successfully');
3972
3973 -- Create loan lines records for ERS loan or disbursements for direct loan
3974 IF l_product_rec.loan_class_code = 'ERS' THEN
3975
3976 l_ers_requested_amount := 0;
3977 IF P_Loan_Lines_Tbl.COUNT <> 0 THEN
3978 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting records into LNS_LOAN_LINES...');
3979
3980 FOR l_count IN 1 .. p_loan_lines_tbl.COUNT LOOP
3981
3982 INSERT INTO LNS_LOAN_LINES
3983 (
3984 LOAN_LINE_ID
3985 ,LOAN_ID
3986 ,LAST_UPDATE_DATE
3987 ,LAST_UPDATED_BY
3988 ,CREATION_DATE
3989 ,CREATED_BY
3990 ,OBJECT_VERSION_NUMBER
3991 ,REFERENCE_TYPE
3992 ,REFERENCE_ID
3993 ,REFERENCE_NUMBER
3994 ,REFERENCE_DESCRIPTION
3995 ,REFERENCE_AMOUNT
3996 ,REQUESTED_AMOUNT
3997 ,PAYMENT_SCHEDULE_ID
3998 ,INSTALLMENT_NUMBER
3999 )
4000 VALUES
4001 (
4002 LNS_LOAN_LINE_S.nextval
4003 ,l_loan_rec.loan_id
4004 ,sysdate
4005 ,lns_utility_pub.created_by
4006 ,sysdate
4007 ,lns_utility_pub.created_by
4008 ,1
4009 ,'RECEIVABLE'
4010 ,p_loan_lines_tbl(l_count).customer_trx_id --v_customer_trx_id(i)
4011 ,p_loan_lines_tbl(l_count).REFERENCE_NUMBER --v_reference_number(i)
4012 ,p_loan_lines_tbl(l_count).REFERENCE_DESCRIPTION --v_DESCRIPTION(i)
4013 ,p_loan_lines_tbl(l_count).remaining_balance --v_remaining_balance(i)
4014 ,p_loan_lines_tbl(l_count).requested_amount --v_requested_amount(i)
4015 ,p_loan_lines_tbl(l_count).payment_schedule_id --v_PAYMENT_SCHEDULE_ID(i)
4016 ,p_loan_lines_tbl(l_count).installment_number --v_installment_number(i)
4017 );
4018
4019 l_ers_requested_amount := l_ers_requested_amount + p_loan_lines_tbl(l_count).requested_amount;
4020
4021 END LOOP;
4022 logmessage(fnd_log.LEVEL_UNEXPECTED, 'All loan lines created successfully');
4023
4024 ELSIF P_Loan_Lines_Tbl.COUNT = 0 THEN
4025 l_ers_requested_amount := LNS_LOAN_LINE_PUB.GET_RULES_DERIVED_ERS_AMOUNT(
4026 p_loan_id => l_loan_rec.loan_id
4027 ,p_primary_borrower_id => l_loan_rec.primary_borrower_id
4028 ,p_currency_code => l_loan_rec.loan_currency
4029 ,p_org_id => l_loan_rec.org_id
4030 ,p_loan_product_id => P_Loan_Details_Rec.product_id
4031 );
4032 logmessage(fnd_log.LEVEL_UNEXPECTED, 'All loan lines inherited successfully');
4033 END IF;
4034
4035 IF l_ers_requested_amount = 0 THEN
4036 LogErrors(p_message_name=>'LNS_LCREATE_ERR_LINE_DERIVE');
4037 ELSE
4038 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating lns_loan_headers_all with requested_amount = ' || l_ers_requested_amount);
4039 UPDATE lns_loan_headers_all
4040 SET requested_amount = l_ers_requested_amount
4041 ,object_version_number = 2
4042 WHERE loan_id = l_loan_rec.loan_id;
4043 END IF;
4044
4045 ELSE
4046
4047 if P_Created_by_module <> 'LNS_IMPORT_LOAN_PUB.IMPORT_LOAN' then
4048
4049 if P_DISB_TBL.count > 0 then
4050
4051 FOR l_count IN 1 .. P_DISB_TBL.COUNT LOOP
4052
4053 -- create disb header
4054 select lns_disb_headers_s.NEXTVAL into l_DISB_HEADER_REC.DISB_HEADER_ID from dual;
4055
4056 l_DISB_HEADER_REC.LOAN_ID := l_loan_rec.loan_id;
4057 l_DISB_HEADER_REC.HEADER_AMOUNT := P_DISB_TBL(l_count).AMOUNT;
4058 l_DISB_HEADER_REC.PAYMENT_REQUEST_DATE := P_DISB_TBL(l_count).DUE_DATE;
4059 l_DISB_HEADER_REC.OBJECT_VERSION_NUMBER := 1;
4060 l_DISB_HEADER_REC.PHASE := 'TERM';
4061 l_DISB_HEADER_REC.ACTIVITY_CODE := P_DISB_TBL(l_count).ACTIVITY_CODE;
4062 l_DISB_HEADER_REC.DESCRIPTION := P_DISB_TBL(l_count).DESCRIPTION;
4063
4064 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling INSERT_DISB_HEADER...');
4065 LNS_FUNDING_PUB.INSERT_DISB_HEADER(
4066 P_API_VERSION => 1.0,
4067 P_INIT_MSG_LIST => FND_API.G_TRUE,
4068 P_COMMIT => FND_API.G_FALSE,
4069 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
4070 P_DISB_HEADER_REC => l_DISB_HEADER_REC,
4071 X_RETURN_STATUS => l_return_status,
4072 X_MSG_COUNT => l_msg_count,
4073 X_MSG_DATA => l_msg_data);
4074
4075 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
4076 IF l_return_status <> 'S' THEN
4077 logmessage(fnd_log.level_unexpected, 'Call to INSERT_DISB_HEADER failed');
4078 RAISE fnd_api.g_exc_error;
4079 END IF;
4080
4081 -- create disb line
4082 select lns_disb_lines_s.NEXTVAL into l_DISB_LINE_REC.DISB_LINE_ID from dual;
4083
4084 l_DISB_LINE_REC.DISB_HEADER_ID := l_DISB_HEADER_REC.DISB_HEADER_ID;
4085 l_DISB_LINE_REC.DISB_LINE_NUMBER := 1;
4086 l_DISB_LINE_REC.LINE_AMOUNT := P_DISB_TBL(l_count).AMOUNT;
4087 l_DISB_LINE_REC.LINE_PERCENT := 100;
4088 l_DISB_LINE_REC.PAYEE_PARTY_ID := P_DISB_TBL(l_count).PAYEE_PARTY_ID;
4089 l_DISB_LINE_REC.BANK_ACCOUNT_ID := P_DISB_TBL(l_count).BANK_ACCOUNT_ID;
4090 l_DISB_LINE_REC.PAYMENT_METHOD_CODE := P_DISB_TBL(l_count).PAYMENT_METHOD_CODE;
4091 l_DISB_LINE_REC.OBJECT_VERSION_NUMBER := 1;
4092
4093 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling INSERT_DISB_LINE...');
4094 LNS_FUNDING_PUB.INSERT_DISB_LINE(
4095 P_API_VERSION => 1.0,
4096 P_INIT_MSG_LIST => FND_API.G_TRUE,
4097 P_COMMIT => FND_API.G_FALSE,
4098 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
4099 P_DISB_LINE_REC => l_DISB_LINE_REC,
4100 X_RETURN_STATUS => l_return_status,
4101 X_MSG_COUNT => l_msg_count,
4102 X_MSG_DATA => l_msg_data);
4103
4104 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
4105 IF l_return_status <> 'S' THEN
4106 logmessage(fnd_log.level_unexpected, 'Call to INSERT_DISB_LINE failed');
4107 RAISE fnd_api.g_exc_error;
4108 END IF;
4109
4110 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_COND_ASSIGNMENT_PUB.DEFAULT_COND_ASSIGNMENTS...');
4111 LNS_COND_ASSIGNMENT_PUB.DEFAULT_COND_ASSIGNMENTS(
4112 P_API_VERSION => 1.0,
4113 P_INIT_MSG_LIST => FND_API.G_TRUE,
4114 P_COMMIT => FND_API.G_FALSE,
4115 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
4116 P_LOAN_ID => l_loan_rec.loan_id,
4117 P_OWNER_OBJECT_ID => l_DISB_HEADER_REC.DISB_HEADER_ID,
4118 P_CONDITION_TYPE => 'DISBURSEMENT',
4119 X_RETURN_STATUS => L_RETURN_STATUS,
4120 X_MSG_COUNT => L_MSG_COUNT,
4121 X_MSG_DATA => L_MSG_DATA);
4122
4123 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
4124 IF l_return_status <> 'S' THEN
4125 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Call to LNS_COND_ASSIGNMENT_PUB.DEFAULT_COND_ASSIGNMENTS failed');
4126 RAISE FND_API.G_EXC_ERROR;
4127 END IF;
4128
4129 END LOOP;
4130
4131 else
4132 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_FUNDING_PUB.DEFAULT_PROD_DISBURSEMENTS...');
4133 LNS_FUNDING_PUB.DEFAULT_PROD_DISBURSEMENTS(l_loan_rec.loan_id);
4134 end if;
4135
4136 end if;
4137
4138 END IF;
4139
4140 IF g_error_count > 0 THEN
4141 RAISE fnd_api.g_exc_error;
4142 END IF;
4143
4144 -- Create custom schedule if needed
4145 IF P_Loan_Details_Rec.custom_payments_flag = 'Y' THEN
4146
4147 IF P_Loan_Details_Rec.PAYMENT_CALC_METHOD = 'CUSTOM' and p_loan_cust_sched_tbl.COUNT = 0 THEN
4148
4149 logmessage(fnd_log.level_statement, 'Calling LNS_CUSTOM_PUB.customizeSchedule...');
4150 l_custom_tbl.delete;
4151 LNS_CUSTOM_PUB.customizeSchedule(
4152 P_API_VERSION => 1.0,
4153 P_INIT_MSG_LIST => FND_API.G_TRUE,
4154 P_COMMIT => FND_API.G_FALSE,
4155 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
4156 P_LOAN_ID => l_loan_id,
4157 P_CLOB => l_product_rec.CUSTOM_SCHED_DATA,
4158 X_CUSTOM_SET_REC => l_CUSTOM_SET_REC,
4159 X_CUSTOM_TBL => l_custom_tbl,
4160 x_return_status => l_return_status,
4161 x_msg_count => l_msg_count,
4162 x_msg_data => l_msg_data);
4163
4164 IF l_return_status <> 'S' THEN
4165 RAISE fnd_api.g_exc_error;
4166 END IF;
4167
4168 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Custom schedule cusomized successfully');
4169
4170 ELSE
4171
4172 logmessage(fnd_log.level_statement, 'Creating custom schedule...');
4173
4174 /* Looping thru custom schedule and build table */
4175 FOR l_count IN 1 .. p_loan_cust_sched_tbl.COUNT
4176 LOOP
4177
4178 l_custom_tbl(l_count).due_date := p_loan_cust_sched_tbl(l_count).due_date;
4179 l_custom_tbl(l_count).RELATIVE_DATE := p_loan_cust_sched_tbl(l_count).RELATIVE_DATE;
4180 l_custom_tbl(l_count).principal_amount := p_loan_cust_sched_tbl(l_count).principal_amount;
4181 l_custom_tbl(l_count).PRINCIPAL_PERCENT := p_loan_cust_sched_tbl(l_count).PRINCIPAL_PERCENT;
4182 l_custom_tbl(l_count).interest_amount := p_loan_cust_sched_tbl(l_count).interest_amount;
4183 l_custom_tbl(l_count).LOCK_PRIN := p_loan_cust_sched_tbl(l_count).LOCK_PRIN;
4184 l_custom_tbl(l_count).LOCK_INT := p_loan_cust_sched_tbl(l_count).LOCK_INT;
4185 l_custom_tbl(l_count).ACTION := 'I';
4186
4187 END LOOP;
4188
4189 l_CUSTOM_SET_REC.AMORT_METHOD := l_term_rec.CUSTOM_CALC_METHOD;
4190
4191 -- added for bug 6961781
4192 LNS_CUSTOM_PUB.saveCustomSchedule(
4193 P_API_VERSION => 1.0,
4194 P_INIT_MSG_LIST => FND_API.G_TRUE,
4195 P_COMMIT => FND_API.G_FALSE,
4196 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
4197 P_LOAN_ID => l_loan_id,
4198 P_BASED_ON_TERMS => 'ORIGINAL',
4199 P_USE_RETAINED_DATA => 'N',
4200 P_CUSTOM_SET_REC => l_CUSTOM_SET_REC,
4201 P_CUSTOM_TBL => l_custom_tbl,
4202 x_return_status => l_return_status,
4203 x_msg_count => l_msg_count,
4204 x_msg_data => l_msg_data);
4205
4206 IF l_return_status <> 'S' THEN
4207 RAISE fnd_api.g_exc_error;
4208 END IF;
4209
4210 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Custom schedule created successfully');
4211
4212 END IF;
4213
4214 -- copy custom schedule data back
4215 p_loan_cust_sched_tbl.delete;
4216 FOR i IN 1 .. l_custom_tbl.count LOOP
4217
4218 p_loan_cust_sched_tbl(i).PAYMENT_NUMBER := l_custom_tbl(i).PAYMENT_NUMBER;
4219 p_loan_cust_sched_tbl(i).DUE_DATE := l_custom_tbl(i).DUE_DATE;
4220 p_loan_cust_sched_tbl(i).RELATIVE_DATE := l_custom_tbl(i).RELATIVE_DATE;
4221 p_loan_cust_sched_tbl(i).PRINCIPAL_AMOUNT := l_custom_tbl(i).PRINCIPAL_AMOUNT;
4222 p_loan_cust_sched_tbl(i).PRINCIPAL_PERCENT := l_custom_tbl(i).PRINCIPAL_PERCENT;
4223 p_loan_cust_sched_tbl(i).INTEREST_AMOUNT := l_custom_tbl(i).INTEREST_AMOUNT;
4224 p_loan_cust_sched_tbl(i).LOCK_PRIN := l_custom_tbl(i).LOCK_PRIN;
4225 p_loan_cust_sched_tbl(i).LOCK_INT := l_custom_tbl(i).LOCK_INT;
4226
4227 END LOOP;
4228
4229 END IF;
4230
4231 -- Update rate schedule if rate type is floating
4232 if P_Loan_Details_Rec.RATE_TYPE = 'FLOATING' and P_Created_by_module <> 'LNS_IMPORT_LOAN_PUB.IMPORT_LOAN' then
4233
4234 logmessage(fnd_log.level_statement, 'Updating floating rate schedule...');
4235 LNS_INDEX_RATES_PUB.UPDATE_LOAN_FLOATING_RATE(
4236 P_API_VERSION => 1.0,
4237 P_INIT_MSG_LIST => FND_API.G_TRUE,
4238 P_COMMIT => FND_API.G_FALSE,
4239 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
4240 P_LOAN_ID => l_loan_rec.loan_id,
4241 x_return_status => l_return_status,
4242 x_msg_count => l_msg_count,
4243 x_msg_data => l_msg_data);
4244
4245 IF l_return_status <> 'S' THEN
4246 RAISE fnd_api.g_exc_error;
4247 END IF;
4248
4249 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Floating rate schedule updated successfully');
4250
4251 end if;
4252
4253 -- Set delinquency and default amounts
4254 IF P_Loan_Details_Rec.delinquency_threshold_amount IS NULL or P_Loan_Details_Rec.DEFAULT_THRESHOLD_AMOUNT IS NULL THEN
4255
4256 l_object_version_number := 1;
4257 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_TERMS_PUB.default_delinquency_amount...');
4258 LNS_TERMS_PUB.default_delinquency_amount (
4259 p_term_id => l_term_id
4260 ,p_loan_id => l_loan_rec.loan_id
4261 ,p_object_version_number=> l_object_version_number
4262 ,x_return_status => l_term_update_success
4263 ,x_msg_count => l_msg_count
4264 ,x_msg_data => l_msg_data
4265 );
4266 IF l_term_update_success <> 'S' THEN
4267 LogErrors(p_message_name=>'LNS_LCREATE_UPD_ERR_TERM'
4268 ,p_token1 =>l_msg_data);
4269 END IF;
4270
4271 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Default delinquency amount updated successfully');
4272
4273 END IF;
4274
4275 IF g_error_count > 0 THEN
4276 RAISE fnd_api.g_exc_error;
4277 END IF;
4278
4279 IF p_commit = fnd_api.g_true THEN
4280 COMMIT WORK;
4281 logmessage(fnd_log.level_statement, 'Commited');
4282 END IF;
4283
4284 X_RETURN_STATUS := 'S';
4285 X_MSG_COUNT := 0;
4286 X_LOAN_ID := l_loan_id;
4287 LogMessage(FND_LOG.LEVEL_STATEMENT, 'New loan_id = ' || X_LOAN_ID);
4288
4289 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
4290
4291 EXCEPTION
4292 WHEN product_not_found THEN
4293 ROLLBACK TO CREATE_LOAN;
4294 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
4295 X_RETURN_STATUS := 'E';
4296 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4297 WHEN error_while_insert THEN
4298 ROLLBACK TO CREATE_LOAN;
4299 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
4300 X_RETURN_STATUS := 'E';
4301 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4302 WHEN OTHERS THEN
4303 ROLLBACK TO CREATE_LOAN;
4304 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
4305 X_RETURN_STATUS := 'E';
4306 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4307 END CREATE_LOAN;
4308
4309
4310
4311 BEGIN
4312 G_LOG_ENABLED := 'N';
4313 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
4314
4315 /* getting msg logging info */
4316 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
4317 if (G_LOG_ENABLED = 'N') then
4318 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
4319 else
4320 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
4321 end if;
4322
4323 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
4324 END LNS_LOAN_PUB;