[Home] [Help]
PACKAGE BODY: APPS.LNS_LOAN_PUB
Source
1 PACKAGE BODY LNS_LOAN_PUB as
2 /* $Header: LNS_LOAN_PUBP_B.pls 120.11.12010000.2 2008/11/06 11:11:13 gparuchu 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_FUNDING_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 g_loan_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
39 --lns_loan_headers_all%ROWTYPE;
40 g_participants_rec LNS_PARTICIPANTS_PUB.loan_participant_rec_type;
41 g_term_rec LNS_TERMS_PUB.loan_term_rec_type;
42 g_participant_rec LNS_PARTICIPANTS_PUB.loan_participant_rec_type;
43 --lns_terms%ROWTYPE;
44 g_rate_term_rec lns_rate_schedules%ROWTYPE;
45 g_rate_open_rec lns_rate_schedules%ROWTYPE;
46 CURSOR Csr_Product_values (p_product_id IN NUMBER) IS
47 SELECT loan_type.loan_type_id loan_type_id
48 ,loan_type.loan_class_code loan_class_code
49 ,loan_type.loan_type_name loan_type_name
50 ,loan_type.multiple_funding_flag multiple_funding_flag
51 ,loan_type.open_to_term_flag open_to_term_flag
52 ,loan_type.credit_review_flag credit_review_flag
53 ,loan_product.loan_product_id loan_product_id
54 ,loan_product.loan_product_name loan_product_name
55 ,loan_product.loan_term loan_term
56 ,loan_product.loan_term_period loan_term_period
57 ,loan_product.max_loan_term max_loan_term
58 ,loan_product.max_loan_term_period max_loan_term_period
59 ,loan_product.loan_currency loan_currency
60 ,loan_product.requested_amount requested_amount
61 ,loan_product.max_requested_amount max_requested_amount
62 ,loan_product.index_rate_id index_rate_id
63 ,loan_product.rate_type rate_type
64 ,loan_product.spread spread
65 ,loan_product.floor_rate floor_rate
66 ,loan_product.ceiling_rate ceiling_rate
67 ,loan_product.interest_compounding_freq interest_compounding_freq
68 ,loan_product.loan_payment_frequency loan_payment_frequency
69 ,loan_product.loan_subtype loan_subtype
70 ,loan_product.collateral_percent collateral_percent
71 ,loan_product.allow_interest_only_flag allow_interest_only_flag
72 ,loan_product.reamortize_over_payment reamortize_over_payment
73 ,loan_product.org_id org_id
74 ,loan_product.legal_entity_id legal_entity_id
75 ,loan_product.rate_change_frequency rate_change_frequency
76 ,loan_type.payment_application_order payment_application_order
77 ,loan_type.pmt_appl_order_scope pmt_appl_order_scope
78 ,loan_product.open_floor_rate open_floor_rate
79 ,loan_product.open_ceiling_rate open_ceiling_rate
80 ,loan_product.reamortize_under_payment reamortize_under_payment
81 ,loan_product.percent_increase percent_increase
82 ,loan_product.percent_increase_life percent_increase_life
83 ,loan_product.first_percent_increase first_percent_increase
84 ,loan_product.open_percent_increase open_percent_increase
85 ,loan_product.open_percent_increase_life open_percent_increase_life
86 ,loan_product.open_first_percent_increase
87 open_first_percent_increase
88 ,loan_product.open_spread open_spread
89 ,loan_product.credit_review_type credit_review_type
90 ,loan_product.guarantor_review_type guarantor_review_type
91 ,loan_product.party_type party_type
92 ,loan_product.open_loan_term open_loan_term
93 ,loan_product.open_loan_term_period open_loan_term_period
94 ,loan_product.open_max_loan_term open_max_loan_term
95 ,loan_product.open_max_loan_term_period open_max_loan_term_period
96 ,loan_product.interest_calculation_method interest_calculation_method
97 ,loan_product.day_count_method day_count_method
98 FROM lns_loan_types loan_type
99 ,lns_loan_products loan_product
100 WHERE loan_product.loan_product_id = p_product_id
101 AND loan_type.loan_type_id = loan_product.loan_type_id
102 AND loan_type.status = 'COMPLETE'
103 AND loan_type.start_date_active <= sysdate
104 AND (loan_type.end_date_active is null OR
105 loan_type.end_date_active >= sysdate)
106 AND loan_product.status = 'COMPLETE'
107 AND loan_product.start_date_active <= sysdate
108 AND (loan_product.end_date_active is null OR
109 loan_product.end_date_active >= sysdate);
110 l_product_rec Csr_Product_values%ROWTYPE;
111 CURSOR Csr_override_flags (p_product_id IN NUMBER) IS
112 SELECT LEGAL_ENTITY_ID_TBL.flag LEGAL_ENTITY_ID_OVR
113 ,LOAN_TERM_TBL.flag LOAN_TERM_OVR
114 ,LOAN_TERM_PERIOD_TBL.flag LOAN_TERM_PERIOD_OVR
115 ,INDEX_RATE_ID_TBL.flag INDEX_RATE_ID_OVR
116 ,RATE_TYPE_TBL.flag RATE_TYPE_OVR
117 ,INTEREST_COMPOUNDING_FREQ_TBL.flag
118 INTEREST_COMPOUNDING_FREQ_OVR
119 ,OPEN_SPREAD_TBL.flag OPEN_SPREAD_OVR
120 ,OPEN_FLOOR_RATE_TBL.flag OPEN_FLOOR_RATE_OVR
121 ,OPEN_CEILING_RATE_TBL.flag OPEN_CEILING_RATE_OVR
122 ,OPEN_PERCENT_INCREASE_TBL.flag OPEN_PERCENT_INCREASE_OVR
123 ,OPEN_PERCENT_INCREASE_LIFE_TBL.flag
124 OPEN_PERCENT_INCREASE_LIFE_OVR
125 ,SPREAD_TBL.flag SPREAD_OVR
126 ,FLOOR_RATE_TBL.flag FLOOR_RATE_OVR
127 ,CEILING_RATE_TBL.flag CEILING_RATE_OVR
128 ,PERCENT_INCREASE_TBL.flag PERCENT_INCREASE_OVR
129 ,PERCENT_INCREASE_LIFE_TBL.flag PERCENT_INCREASE_LIFE_OVR
130 ,LOAN_PAYMENT_FREQUENCY_TBL.flag LOAN_PAYMENT_FREQ_OVR
131 ,LOAN_SUBTYPE_TBL.flag LOAN_SUBTYPE_OVR
132 ,REAMORTIZE_OVER_PAYMENT_TBL.flag REAMORTIZE_OVER_PAYMENT_OVR
133 FROM (SELECT DECODE(COUNT(*),0,'Y','N') flag
134 FROM LNS_LOAN_PRODUCT_FLAGS
135 WHERE loan_product_id = p_product_id
136 AND READONLY_COLUMN_NAME = 'LEGAL_ENTITY_ID'
137 ) LEGAL_ENTITY_ID_TBL,
138 (SELECT DECODE(COUNT(*),0,'Y','N') flag
139 FROM LNS_LOAN_PRODUCT_FLAGS
140 WHERE loan_product_id = p_product_id
141 AND READONLY_COLUMN_NAME = 'LOAN_TERM'
142 ) LOAN_TERM_TBL,
143 (SELECT DECODE(COUNT(*),0,'Y','N') flag
144 FROM LNS_LOAN_PRODUCT_FLAGS
145 WHERE loan_product_id = p_product_id
146 AND READONLY_COLUMN_NAME = 'LOAN_TERM_PERIOD'
147 ) LOAN_TERM_PERIOD_TBL,
148 (SELECT DECODE(COUNT(*),0,'Y','N') flag
149 FROM LNS_LOAN_PRODUCT_FLAGS
150 WHERE loan_product_id = p_product_id
151 AND READONLY_COLUMN_NAME = 'INDEX_RATE_ID'
152 ) INDEX_RATE_ID_TBL,
153 (SELECT DECODE(COUNT(*),0,'Y','N') flag
154 FROM LNS_LOAN_PRODUCT_FLAGS
155 WHERE loan_product_id = p_product_id
156 AND READONLY_COLUMN_NAME = 'RATE_TYPE'
157 ) RATE_TYPE_TBL,
158 (SELECT DECODE(COUNT(*),0,'Y','N') flag
159 FROM LNS_LOAN_PRODUCT_FLAGS
160 WHERE loan_product_id = p_product_id
161 AND READONLY_COLUMN_NAME = 'INTEREST_COMPOUNDING_FREQ'
162 ) INTEREST_COMPOUNDING_FREQ_TBL,
163 (SELECT DECODE(COUNT(*),0,'Y','N') flag
164 FROM LNS_LOAN_PRODUCT_FLAGS
165 WHERE loan_product_id = p_product_id
166 AND READONLY_COLUMN_NAME = 'OPEN_SPREAD'
167 ) OPEN_SPREAD_TBL,
168 (SELECT DECODE(COUNT(*),0,'Y','N') flag
169 FROM LNS_LOAN_PRODUCT_FLAGS
170 WHERE loan_product_id = p_product_id
171 AND READONLY_COLUMN_NAME = 'OPEN_FLOOR_RATE'
172 ) OPEN_FLOOR_RATE_TBL,
173 (SELECT DECODE(COUNT(*),0,'Y','N') flag
174 FROM LNS_LOAN_PRODUCT_FLAGS
175 WHERE loan_product_id = p_product_id
176 AND READONLY_COLUMN_NAME = 'OPEN_CEILING_RATE'
177 ) OPEN_CEILING_RATE_TBL,
178 (SELECT DECODE(COUNT(*),0,'Y','N') flag
179 FROM LNS_LOAN_PRODUCT_FLAGS
180 WHERE loan_product_id = p_product_id
181 AND READONLY_COLUMN_NAME = 'OPEN_PERCENT_INCREASE'
182 ) OPEN_PERCENT_INCREASE_TBL,
183 (SELECT DECODE(COUNT(*),0,'Y','N') flag
184 FROM LNS_LOAN_PRODUCT_FLAGS
185 WHERE loan_product_id = p_product_id
186 AND READONLY_COLUMN_NAME = 'OPEN_PERCENT_INCREASE_LIFE'
187 ) OPEN_PERCENT_INCREASE_LIFE_TBL,
188 (SELECT DECODE(COUNT(*),0,'Y','N') flag
189 FROM LNS_LOAN_PRODUCT_FLAGS
190 WHERE loan_product_id = p_product_id
191 AND READONLY_COLUMN_NAME = 'SPREAD'
192 ) SPREAD_TBL,
193 (SELECT DECODE(COUNT(*),0,'Y','N') flag
194 FROM LNS_LOAN_PRODUCT_FLAGS
195 WHERE loan_product_id = p_product_id
196 AND READONLY_COLUMN_NAME = 'FLOOR_RATE'
197 ) FLOOR_RATE_TBL,
198 (SELECT DECODE(COUNT(*),0,'Y','N') flag
199 FROM LNS_LOAN_PRODUCT_FLAGS
200 WHERE loan_product_id = p_product_id
201 AND READONLY_COLUMN_NAME = 'CEILING_RATE'
202 ) CEILING_RATE_TBL,
203 (SELECT DECODE(COUNT(*),0,'Y','N') flag
204 FROM LNS_LOAN_PRODUCT_FLAGS
205 WHERE loan_product_id = p_product_id
206 AND READONLY_COLUMN_NAME = 'PERCENT_INCREASE'
207 ) PERCENT_INCREASE_TBL,
208 (SELECT DECODE(COUNT(*),0,'Y','N') flag
209 FROM LNS_LOAN_PRODUCT_FLAGS
210 WHERE loan_product_id = p_product_id
211 AND READONLY_COLUMN_NAME = 'PERCENT_INCREASE_LIFE'
212 ) PERCENT_INCREASE_LIFE_TBL,
213 (SELECT DECODE(COUNT(*),0,'Y','N') flag
214 FROM LNS_LOAN_PRODUCT_FLAGS
215 WHERE loan_product_id = p_product_id
216 AND READONLY_COLUMN_NAME = 'LOAN_PAYMENT_FREQUENCY'
217 ) LOAN_PAYMENT_FREQUENCY_TBL,
218 (SELECT DECODE(COUNT(*),0,'Y','N') flag
219 FROM LNS_LOAN_PRODUCT_FLAGS
220 WHERE loan_product_id = p_product_id
221 AND READONLY_COLUMN_NAME = 'LOAN_SUBTYPE'
222 ) LOAN_SUBTYPE_TBL,
223 (SELECT DECODE(COUNT(*),0,'Y','N') flag
224 FROM LNS_LOAN_PRODUCT_FLAGS
225 WHERE loan_product_id = p_product_id
226 AND READONLY_COLUMN_NAME = 'REAMORTIZE_OVER_PAYMENT'
227 ) REAMORTIZE_OVER_PAYMENT_TBL;
228 l_override_rec Csr_override_flags%ROWTYPE;
229
230
231 /*========================================================================
232 | PRIVATE PROCEDURE LogMessage
233 |
234 | DESCRIPTION
235 | This procedure logs debug messages to db and to CM log
236 |
237 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
238 |
239 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
240 | None
241 |
242 | PARAMETERS
243 | p_msg_level IN Debug msg level
244 | p_msg IN Debug msg itself
245 |
246 | KNOWN ISSUES
247 | None
248 |
249 |
250 | NOTES
251 | Any interesting aspect of the code in the package body which needs
252 | to be stated.
253 |
254 | MODIFICATION HISTORY
255 | Date Author Description of Changes
256 | 17-Jan-2006 GBELLARY Created
257 |
258 *=======================================================================*/
259 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
260 IS
261 BEGIN
262 if (p_msg_level >= G_MSG_LEVEL) then
263
264 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
265
266 end if;
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
271 END;
272 /*========================================================================
273 | PRIVATE PROCEDURE LogErrors
274 |
275 | DESCRIPTION
276 | This procedure logs debug messages to db and to CM log
277 |
278 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
279 |
280 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
281 | None
282 |
283 | PARAMETERS
284 | p_msg_level IN Debug msg level
285 | p_msg IN Debug msg itself
286 |
287 | KNOWN ISSUES
288 | None
289 |
290 |
291 | NOTES
292 | This procedure builds the error message and stores it (alongwith
293 | other columns in LNS_LOAN_API_ERRORS_GT) in g_errors_rec.
294 |
295 | MODIFICATION HISTORY
296 | Date Author Description of Changes
297 | 17-Jan-2006 GBELLARY Created
298 |
299 *=======================================================================*/
300 Procedure LogErrors( p_message_name IN VARCHAR2
301 ,p_line_number IN NUMBER DEFAULT NULL
302 ,p_token1 IN VARCHAR2 DEFAULT NULL
303 ,p_token2 IN VARCHAR2 DEFAULT NULL
304 ,p_token3 IN VARCHAR2 DEFAULT NULL)
305 IS
306 l_text LNS_LOAN_API_ERRORS_GT.MESSAGE_TEXT%TYPE;
307 BEGIN
308 fnd_message.set_name('LNS', p_message_name);
309 if p_token1 is NOT NULL THEN
310 fnd_message.set_token('TOKEN1',p_token1);
311 end if;
312 IF p_token2 is NOT NULL THEN
313 fnd_message.set_token('TOKEN2',p_token2);
314 END IF;
315 IF p_token3 is NOT NULL THEN
316 fnd_message.set_token('TOKEN3',p_token3);
317 END IF;
318 l_text := substrb(fnd_message.get,1,2000);
319 g_error_count := g_error_count+1;
320 g_errors_rec.extend(1);
321 g_errors_rec(g_error_count).ERROR_NUMBER := g_error_count;
322 g_errors_rec(g_error_count).MESSAGE_NAME := p_message_name;
323 g_errors_rec(g_error_count).MESSAGE_TEXT := l_text;
324 g_errors_rec(g_error_count).LINE_NUMBER := p_line_number;
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
329 END;
330 /*========================================================================
331 | PRIVATE PROCEDURE InsertErrors
332 |
333 | DESCRIPTION
334 | This procedure logs debug messages to db and to CM log
335 |
336 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
337 |
338 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
339 | None
340 |
341 | PARAMETERS
342 | p_msg_level IN Debug msg level
343 | p_msg IN Debug msg itself
344 |
345 | KNOWN ISSUES
346 | None
347 |
348 |
349 | NOTES
350 | Any interesting aspect of the code in the package body which needs
351 | to be stated.
352 |
353 | MODIFICATION HISTORY
354 | Date Author Description of Changes
355 | 17-Jan-2006 GBELLARY Created
356 |
357 *=======================================================================*/
358 Procedure InsertErrors
359 IS
360 BEGIN
361 FORALL i in 1..g_errors_rec.COUNT
362 insert into LNS_LOAN_API_ERRORS_GT
363 VALUES
364 g_errors_rec(i);
365 EXCEPTION
366 WHEN OTHERS THEN
367 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
368 END;
369
370 PROCEDURE CREATE_LOAN(
371 P_API_VERSION IN NUMBER,
372 P_COMMIT IN VARCHAR2,
373 P_Loan_Details_Rec IN LNS_LOAN_PUB.Loan_Details_Rec_Type,
374 P_Loan_Lines_Rec IN LNS_LOAN_PUB.Loan_Lines_List_Type,
375 P_Application_id IN NUMBER,
376 P_Created_by_module IN VARCHAR2,
377 X_loan_id OUT NOCOPY NUMBER,
378 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
379 X_MSG_COUNT OUT NOCOPY NUMBER) IS
380 /*-----------------------------------------------------------------------+
381 | Local Variable Declarations and initializations |
382 +-----------------------------------------------------------------------*/
383
384 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_LOAN';
385 l_api_version CONSTANT NUMBER := 1.0;
386 type PAYMENT_SCHEDULE_ID_tab_t is table of lns_loan_lines.PAYMENT_SCHEDULE_ID%TYPE
387 index by pls_integer;
388 v_PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID_tab_t;
389
390 type customer_trx_id_tab_t is table of lns_loan_lines.reference_id%TYPE
391 index by pls_integer;
392 v_customer_trx_id customer_trx_id_tab_t;
393
394 type reference_number_tab_t is table of lns_loan_lines.reference_number%TYPE
395 index by pls_integer;
396 v_reference_number reference_number_tab_t;
397
398 type installment_number_tab_t is table of lns_loan_lines.installment_number%TYPE
399 index by pls_integer;
400 v_installment_number installment_number_tab_t;
401
402 type remaining_balance_tab_t is table of ar_payment_schedules_all.amount_due_remaining%TYPE
403 index by pls_integer;
404 v_remaining_balance remaining_balance_tab_t;
405 type requested_amount_tab_t is table of lns_loan_lines.requested_amount%TYPE
406 index by pls_integer;
407 v_requested_amount requested_amount_tab_t;
408 type DESCRIPTION_tab_t is table of lns_loan_lines.REFERENCE_DESCRIPTION%TYPE
409 index by pls_integer;
410 v_DESCRIPTION DESCRIPTION_tab_t;
411 type line_number_tab_t is table of NUMBER(15)
412 index by pls_integer;
413 v_line_number line_number_tab_t;
414 product_not_found EXCEPTION;
415 error_while_insert EXCEPTION;
416 l_dummy VARCHAR2(30);
417 l_borrower_valid BOOLEAN;
418 l_cust_acct_valid BOOLEAN;
419 l_generate_loan_number VARCHAR2(1);
420 l_total_installments lns_rate_schedules.end_installment_number%TYPE;
421 l_contact_person_party_id hz_relationships.SUBJECT_ID%TYPE;
422 l_loan_id number(15);
423 l_loan_number VARCHAR2(60);
424 l_header_insert_success VARCHAR2(1);
425 l_participant_id NUMBER(15);
426 l_part_insert_success VARCHAR2(1);
427 l_line_insert_success VARCHAR2(1);
428 l_term_id NUMBER(15);
429 l_term_insert_success VARCHAR2(1);
430 l_object_version_number NUMBER(15);
431 l_ers_requested_amount NUMBER;
432 l_term_update_success VARCHAR2(1);
433 l_message_count NUMBER(15);
434 l_message_data VARCHAR2(2000);
435
436
437
438 /*-----------------------------------------------------------------------+
439 | Cursor Declarations |
440 +-----------------------------------------------------------------------*/
441 BEGIN
442 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
443 -- Initialize flags
444 l_borrower_valid := FALSE;
445 l_cust_acct_valid := FALSE;
446
447 -- Initialize Collections and Variables
448 g_errors_rec.delete;
449 g_error_count := 0;
450 -- Get the product values
451 IF P_Loan_Details_Rec.product_id is NULL THEN
452 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
453 ,p_token1=>'P_Loan_Details_Rec.product_id');
454 -- If Product not found dont bother with further processing
455 raise product_not_found;
456 ELSE
457 OPEN Csr_Product_values(P_Loan_Details_Rec.product_id);
458 FETCH Csr_Product_values
459 INTO l_product_rec;
460 IF Csr_Product_values%NOTFOUND THEN
461 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
462 ,p_token1=>'P_Loan_Details_Rec.product_id'
463 ,p_token2=>P_Loan_Details_Rec.product_id);
464 CLOSE Csr_Product_values;
465 raise product_not_found;
466 END IF;
467 CLOSE Csr_Product_values;
468 END IF;
469 -- Get Override Allowed Flags
470 OPEN Csr_override_flags(P_Loan_Details_Rec.product_id);
471 FETCH Csr_override_flags
472 into l_override_rec;
473 CLOSE Csr_override_flags;
474
475 -- Validate loan_assigned_to
476 IF P_Loan_Details_Rec.loan_assigned_to IS NULL THEN
477 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
478 ,p_token1=>'P_Loan_Details_Rec.loan_assigned_to');
479 ELSE
480 BEGIN
481 SELECT 'Y'
482 INTO l_dummy
483 FROM jtf_rs_resource_extns res
484 WHERE res.resource_id = P_Loan_Details_Rec.loan_assigned_to
485 AND res.category = 'EMPLOYEE'
486 AND res.start_date_active <= SYSDATE
487 AND (res.end_date_active is null or res.end_date_active >= SYSDATE);
488 EXCEPTION
489 WHEN NO_DATA_FOUND THEN
490 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
491 ,p_token1=>'P_Loan_Details_Rec.loan_assigned_to'
492 ,p_token2=>P_Loan_Details_Rec.loan_assigned_to);
493 END;
494 END IF;
495 -- Validate Requested Amount
496 IF l_product_rec.loan_class_code <> 'ERS' AND
497 (P_Loan_Details_Rec.requested_amount < l_product_rec.requested_amount OR
498 P_Loan_Details_Rec.requested_amount > l_product_rec.max_requested_amount) THEN
499 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_BETWEEN'
500 ,p_token1=>'P_Loan_Details_Rec.requested_amount'
501 ,p_token2=>l_product_rec.max_requested_amount
502 ,p_token3=>l_product_rec.requested_amount);
503 END IF;
504 -- Validate Legal Entity Id
505 IF P_Loan_Details_Rec.legal_entity_id IS NULL
506 AND l_override_rec.legal_entity_id_ovr = 'Y' THEN
507 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
508 ,p_token1=>'P_Loan_Details_Rec.legal_entity_id');
509 ELSIF P_Loan_Details_Rec.legal_entity_id IS NOT NULL
510 AND l_override_rec.legal_entity_id_ovr = 'Y'
511 AND l_product_rec.legal_entity_id is NULL THEN
512 BEGIN
513 SELECT 'Y'
514 INTO l_dummy
515 FROM xle_entity_profiles xep
516 WHERE xep.legal_entity_id = P_Loan_Details_Rec.legal_entity_id;
517 EXCEPTION
518 WHEN NO_DATA_FOUND THEN
519 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
520 ,p_token1=>'P_Loan_Details_Rec.legal_entity_id'
521 ,p_token2=>P_Loan_Details_Rec.legal_entity_id);
522 END;
523 --ELSE null;
524 END IF;
525
526 -- Validate primary_borrower_party_id
527 IF P_Loan_Details_Rec.primary_borrower_party_id IS NULL THEN
528 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
529 ,p_token1=>'P_Loan_Details_Rec.primary_borrower_party_id');
530 ELSE
531 BEGIN
532 SELECT 'Y'
533 INTO l_dummy
534 FROM hz_parties hzp
535 WHERE hzp.party_id = P_Loan_Details_Rec.primary_borrower_party_id
536 AND hzp.party_type = l_product_rec.party_type
537 AND hzp.status = 'A';
538 l_borrower_valid := TRUE;
539 EXCEPTION
540 WHEN NO_DATA_FOUND THEN
541 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
542 ,p_token1=>'P_Loan_Details_Rec.primary_borrower_party_id'
543 ,p_token2=>P_Loan_Details_Rec.primary_borrower_party_id);
544 END;
545 END IF;
546 -- Validate cust_account_id
547 IF NOT(l_borrower_valid)
548 THEN null; -- If Borrower itself is invalid no use in validating this
549 ELSIF P_Loan_Details_Rec.cust_account_id IS NULL THEN
550 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
551 ,p_token1=>'P_Loan_Details_Rec.cust_account_id');
552 ELSE
553 BEGIN
554 SELECT 'Y'
555 INTO l_dummy
556 FROM hz_cust_accounts_all hzca
557 WHERE hzca.cust_account_id = P_Loan_Details_Rec.cust_account_id
558 AND hzca.party_id = P_Loan_Details_Rec.primary_borrower_party_id
559 AND hzca.status = 'A';
560 l_cust_acct_valid := TRUE;
561 EXCEPTION
562 WHEN NO_DATA_FOUND THEN
563 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
564 ,p_token1=>'P_Loan_Details_Rec.cust_account_id'
565 ,p_token2=>P_Loan_Details_Rec.cust_account_id);
566 END;
567 END IF;
568
569 -- Validate bill_to_acct_site_id
570 IF NOT(l_cust_acct_valid)
571 THEN null; -- If Cust acct itself is invalid no use in validating this
572 ELSIF P_Loan_Details_Rec.bill_to_acct_site_id IS NULL THEN
573 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
574 ,p_token1=>'P_Loan_Details_Rec.bill_to_acct_site_id');
575 ELSE
576 BEGIN
577 SELECT 'Y'
578 INTO l_dummy
579 FROM hz_cust_accounts_all account,
580 hz_cust_site_uses acc_site_use,
581 hz_cust_acct_sites_all acct_site
582 WHERE account.cust_account_id = acct_site.cust_account_id
583 AND acc_site_use.cust_acct_site_id = acct_site.cust_acct_site_id
584 AND acc_site_use.site_use_code = 'BILL_TO'
585 AND acct_site.cust_acct_site_id =
586 P_Loan_Details_Rec.bill_to_acct_site_id
587 AND acct_site.cust_account_id = P_Loan_Details_Rec.cust_account_id
588 AND acc_site_use.status = 'A';
589 EXCEPTION
590 WHEN NO_DATA_FOUND THEN
591 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
592 ,p_token1=>'P_Loan_Details_Rec.bill_to_acct_site_id'
593 ,p_token2=>P_Loan_Details_Rec.bill_to_acct_site_id);
594 END;
595 END IF;
596
597 -- Validate Contact Person Party Id
598 IF l_product_rec.party_type <> 'PERSON'
599 AND l_borrower_valid
600 AND P_Loan_Details_Rec.contact_rel_party_id IS NOT NULL THEN
601 BEGIN
602 SELECT rel.subject_id
603 INTO l_contact_person_party_id
604 FROM hz_relationships rel,
605 hz_parties rel_party,
606 ar_lookups lkup,
607 hz_relationship_types rel_type
608 WHERE rel.party_id = P_Loan_Details_Rec.contact_rel_party_id
609 AND rel.object_id = P_Loan_Details_Rec.primary_borrower_party_id
610 AND rel.relationship_code = rel_type.forward_rel_code
611 AND rel_type.create_party_flag = 'Y'
612 AND rel_type.subject_type = 'PERSON'
613 AND rel_type.object_type = l_product_rec.party_type
614 AND rel.relationship_type = rel_type.relationship_type
615 AND rel_type.role = lkup.lookup_code
616 AND lkup.lookup_type = 'HZ_RELATIONSHIP_ROLE'
617 AND rel.party_id = rel_party.party_id
618 AND rel.status = 'A'
619 AND rel_party.status = 'A';
620 EXCEPTION
621 WHEN NO_DATA_FOUND THEN
622 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
623 ,p_token1=>'P_Loan_Details_Rec.contact_rel_party_id'
624 ,p_token2=>P_Loan_Details_Rec.contact_rel_party_id);
625 END;
626 END IF;
627 -- Validate Loan Purpose Code
628 IF P_Loan_Details_Rec.loan_purpose_code IS NOT NULL THEN
629 BEGIN
630 SELECT 'Y'
631 INTO l_dummy
632 FROM lns_lookups llk
633 WHERE llk.lookup_code = P_Loan_Details_Rec.loan_purpose_code
634 AND llk.lookup_type = 'LOAN_PURPOSE';
635 EXCEPTION
636 WHEN NO_DATA_FOUND THEN
637 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
638 ,p_token1=>'P_Loan_Details_Rec.loan_purpose_code'
639 ,p_token2=>P_Loan_Details_Rec.loan_purpose_code);
640 END;
641 END IF;
642
643
644 -- Validate Credit Review Flag
645 IF P_Loan_Details_Rec.credit_review_flag IS NOT NULL
646 AND P_Loan_Details_Rec.credit_review_flag NOT IN ('Y','N') THEN
647 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
648 ,p_token1=>'P_Loan_Details_Rec.credit_review_flag'
649 ,p_token2=>P_Loan_Details_Rec.credit_review_flag);
650 END IF;
651
652 -- Validate Exchange Rate Type
653 IF P_Loan_Details_Rec.exchange_rate_type IS NOT NULL THEN
654 BEGIN
655 SELECT 'Y'
656 INTO l_dummy
657 FROM gl_daily_conversion_types gdct
658 WHERE gdct.conversion_type = P_Loan_Details_Rec.exchange_rate_type;
659 EXCEPTION
660 WHEN NO_DATA_FOUND THEN
661 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
662 ,p_token1=>'P_Loan_Details_Rec.exchange_rate_type'
663 ,p_token2=>P_Loan_Details_Rec.exchange_rate_type);
664 END;
665 END IF;
666
667 -- Validate Trx Type Id
668
669 -- If ERS and value is null log error
670 IF P_Loan_Details_Rec.trx_type_id IS NULL
671 AND l_product_rec.loan_class_code = 'ERS' THEN
672 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
673 ,p_token1=>'P_Loan_Details_Rec.trx_type_id');
674 ELSIF P_Loan_Details_Rec.trx_type_id IS NOT NULL
675 AND l_product_rec.loan_class_code = 'ERS' THEN
676 BEGIN
677 SELECT 'Y'
678 INTO l_dummy
679 FROM ar_lookups alk
680 ,RA_CUST_TRX_TYPES_ALL rtyp
681 WHERE rtyp.CUST_TRX_TYPE_ID = P_Loan_Details_Rec.trx_type_id
682 AND alk.lookup_code = rtyp.type
683 AND alk.lookup_type = 'INV/CM';
684 EXCEPTION
685 WHEN NO_DATA_FOUND THEN
686 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
687 ,p_token1=>'P_Loan_Details_Rec.trx_type_id'
688 ,p_token2=>P_Loan_Details_Rec.trx_type_id);
689 END;
690 END IF;
691
692 -- Validate Loan Term
693 IF P_Loan_Details_Rec.loan_term is NOT NULL THEN
694 IF ((P_Loan_Details_Rec.loan_term > 999) OR (P_Loan_Details_Rec.loan_term < 0) OR (round(P_Loan_Details_Rec.loan_term) <> P_Loan_Details_Rec.loan_term)) THEN
695 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_BETWEEN'
696 ,p_token1=>'P_Loan_Details_Rec.loan_term = '||P_Loan_Details_Rec.loan_term||' should be a whole number and '
697 ,p_token2=>' 0 '
698 ,p_token3=>' 999 '
699 );
700 END IF;
701 ELSIF P_Loan_Details_Rec.loan_term is NOT NULL
702 AND P_Loan_Details_Rec.loan_term_period is NOT NULL
703 AND l_product_rec.max_loan_term is NOT NULL
704 AND (l_product_rec.open_to_term_flag = 'Y'
705 OR l_product_rec.multiple_funding_flag = 'N')
706 AND ((LNS_FIN_UTILS.intervalsinperiod(P_Loan_Details_Rec.loan_term
707 ,P_Loan_Details_Rec.loan_term_period
708 ,'DAYS')
709 < LNS_FIN_UTILS.intervalsinperiod(l_product_rec.loan_term
710 ,l_product_rec.loan_term_period
711 ,'DAYS')) OR
712 (LNS_FIN_UTILS.intervalsinperiod(P_Loan_Details_Rec.loan_term
713 ,P_Loan_Details_Rec.loan_term_period
714 ,'DAYS')
715 > LNS_FIN_UTILS.intervalsinperiod(l_product_rec.max_loan_term
716 ,l_product_rec.max_loan_term_period
717 ,'DAYS'))) THEN
718 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_BETWEEN'
719 ,p_token1=>
720 '(P_Loan_Details_Rec.loan_term P_Loan_Details_Rec.loan_term_period)'
721 ,p_token2=>l_product_rec.loan_term || ' ' ||
722 l_product_rec.loan_term_period
723 ,p_token3=>l_product_rec.max_loan_term || ' ' ||
724 l_product_rec.max_loan_term_period);
725 ELSIF P_Loan_Details_Rec.loan_term is NOT NULL
726 AND P_Loan_Details_Rec.loan_term_period is NOT NULL
727 AND l_product_rec.max_loan_term is NULL
728 AND (l_product_rec.open_to_term_flag = 'Y'
729 OR l_product_rec.multiple_funding_flag = 'N')
730 AND (LNS_FIN_UTILS.intervalsinperiod(P_Loan_Details_Rec.loan_term
731 ,P_Loan_Details_Rec.loan_term_period
732 ,'DAYS')
733 < LNS_FIN_UTILS.intervalsinperiod(l_product_rec.loan_term
734 ,l_product_rec.loan_term_period
735 ,'DAYS')) THEN
736 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_GREATER'
737 ,p_token1=>
738 '(P_Loan_Details_Rec.loan_term P_Loan_Details_Rec.loan_term_period)'
739 ,p_token2=>l_product_rec.loan_term || ' ' ||
740 l_product_rec.loan_term_period);
741 ELSIF P_Loan_Details_Rec.loan_term is NULL
742 AND (l_product_rec.open_to_term_flag = 'Y'
743 OR l_product_rec.multiple_funding_flag = 'N') THEN
744 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
745 ,p_token1=>'P_Loan_Details_Rec.loan_term');
746 END IF;
747
748 -- Validate Loan Term Period
749 IF P_Loan_Details_Rec.loan_term_period IS NULL
750 AND l_override_rec.loan_term_period_ovr = 'Y'
751 AND l_product_rec.loan_term_period IS NULL
752 AND (l_product_rec.open_to_term_flag = 'Y'
753 OR l_product_rec.multiple_funding_flag = 'N') THEN
754 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
755 ,p_token1=>'P_Loan_Details_Rec.loan_term_period');
756 ELSIF P_Loan_Details_Rec.loan_term_period IS NOT NULL
757 AND l_override_rec.loan_term_period_ovr = 'Y' THEN
758 BEGIN
759 SELECT 'Y'
760 INTO l_dummy
761 FROM lns_lookups llk
762 WHERE llk.lookup_code = P_Loan_Details_Rec.loan_term_period
763 AND llk.lookup_type = 'PERIOD';
764 EXCEPTION
765 WHEN NO_DATA_FOUND THEN
766 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
767 ,p_token1=>'P_Loan_Details_Rec.loan_term_period'
768 ,p_token2=>P_Loan_Details_Rec.loan_term_period);
769 END;
770 END IF;
771
772 -- Validate Open Loan Term
773
774
775 IF P_Loan_Details_Rec.open_loan_term is NOT NULL THEN
776 IF ((P_Loan_Details_Rec.open_loan_term > 999 ) OR (P_Loan_Details_Rec.open_loan_term < 0) OR (round(P_Loan_Details_Rec.open_loan_term) <> P_Loan_Details_Rec.open_loan_term)) THEN
777 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_BETWEEN'
778 ,p_token1=>'P_Loan_Details_Rec.open_loan_term = '||P_Loan_Details_Rec.open_loan_term||' should be a whole number and '
779 ,p_token2=>' 0 '
780 ,p_token3=>' 999 '
781 );
782 END IF;
783 ELSIF P_Loan_Details_Rec.open_loan_term is NOT NULL
784 AND P_Loan_Details_Rec.open_loan_term_period is NOT NULL
785 AND l_product_rec.open_max_loan_term is NOT NULL
786 AND l_product_rec.multiple_funding_flag = 'Y'
787 AND ((LNS_FIN_UTILS.intervalsinperiod(P_Loan_Details_Rec.open_loan_term
788 ,P_Loan_Details_Rec.open_loan_term_period
789 ,'DAYS')
790 < LNS_FIN_UTILS.intervalsinperiod(l_product_rec.open_loan_term
791 ,l_product_rec.open_loan_term_period
792 ,'DAYS')) OR
793 (LNS_FIN_UTILS.intervalsinperiod(P_Loan_Details_Rec.open_loan_term
794 ,P_Loan_Details_Rec.open_loan_term_period
795 ,'DAYS')
796 > LNS_FIN_UTILS.intervalsinperiod(l_product_rec.open_max_loan_term
797 ,l_product_rec.open_max_loan_term_period
798 ,'DAYS'))) THEN
799 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_BETWEEN'
800 ,p_token1=>
801 '(P_Loan_Details_Rec.open_loan_term P_Loan_Details_Rec.open_loan_term_period)'
802 ,p_token2=>l_product_rec.open_loan_term || ' ' ||
803 l_product_rec.open_loan_term_period
804 ,p_token3=>l_product_rec.open_max_loan_term || ' ' ||
805 l_product_rec.open_max_loan_term_period);
806 ELSIF P_Loan_Details_Rec.open_loan_term is NOT NULL
807 AND P_Loan_Details_Rec.open_loan_term_period is NOT NULL
808 AND l_product_rec.open_max_loan_term is NULL
809 AND l_product_rec.multiple_funding_flag = 'Y'
810 AND (LNS_FIN_UTILS.intervalsinperiod(P_Loan_Details_Rec.open_loan_term
811 ,P_Loan_Details_Rec.open_loan_term_period
812 ,'DAYS')
813 < LNS_FIN_UTILS.intervalsinperiod(l_product_rec.open_loan_term
814 ,l_product_rec.open_loan_term_period
815 ,'DAYS')) THEN
816 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_GREATER'
817 ,p_token1=>
818 '(P_Loan_Details_Rec.open_loan_term P_Loan_Details_Rec.open_loan_term_period)'
819 ,p_token2=>l_product_rec.open_loan_term || ' ' ||
820 l_product_rec.open_loan_term_period);
821 ELSIF P_Loan_Details_Rec.open_loan_term is NULL
822 AND l_product_rec.multiple_funding_flag = 'Y' THEN
823 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
824 ,p_token1=>'P_Loan_Details_Rec.open_loan_term');
825
826 END IF;
827
828 -- Validate Open Loan Term Period
829 IF P_Loan_Details_Rec.open_loan_term_period IS NOT NULL
830 AND l_product_rec.multiple_funding_flag = 'Y' THEN
831 BEGIN
832 SELECT 'Y'
833 INTO l_dummy
834 FROM lns_lookups llk
835 WHERE llk.lookup_code = P_Loan_Details_Rec.open_loan_term_period
836 AND llk.lookup_type = 'PERIOD';
837 EXCEPTION
838 WHEN NO_DATA_FOUND THEN
839 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
840 ,p_token1=>'P_Loan_Details_Rec.open_loan_term_period'
841 ,p_token2=>P_Loan_Details_Rec.open_loan_term_period);
842 END;
843 END IF;
844
845 -- Validate Payment Type
846 IF (P_Loan_Details_Rec.balloon_payment_type IS NOT NULL AND
847 (l_product_rec.multiple_funding_flag = 'N' OR
848 l_product_rec.open_to_term_flag = 'Y')) THEN
849 BEGIN
850 SELECT 'Y'
851 INTO l_dummy
852 FROM lns_lookups llk
853 WHERE llk.lookup_code = P_Loan_Details_Rec.balloon_payment_type
854 AND llk.lookup_type = 'BALLOON_PAYMENT_TYPE';
855 EXCEPTION
856 WHEN NO_DATA_FOUND THEN
857 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
858 ,p_token1=>'P_Loan_Details_Rec.balloon_payment_type'
859 ,p_token2=>P_Loan_Details_Rec.balloon_payment_type);
860 END;
861 END IF;
862
863 -- Validate Balloon Payment Amount / Balloon Term
864 IF (l_product_rec.multiple_funding_flag = 'N' OR
865 l_product_rec.open_to_term_flag = 'Y') THEN
866
867 IF (nvl(P_Loan_Details_Rec.balloon_payment_type,'TERM') = 'TERM' AND
868 P_Loan_Details_Rec.balloon_term IS NULL) THEN
869 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
870 ,p_token1=>'P_Loan_Details_Rec.balloon_term');
871
872 ELSIF (nvl(P_Loan_Details_Rec.balloon_payment_type,'TERM') = 'TERM' AND
873 P_Loan_Details_Rec.balloon_term IS NOT NULL) THEN
874 IF ((P_Loan_Details_Rec.balloon_term > 999) OR (P_Loan_Details_Rec.balloon_term < 0) OR (round(P_Loan_Details_Rec.balloon_term) <> P_Loan_Details_Rec.balloon_term)) THEN
875 LogErrors(p_message_name=>'LNS_LCREATE_ATTR_NOT_BETWEEN'
876 ,p_token1=>'P_Loan_Details_Rec.balloon_term = '||P_Loan_Details_Rec.balloon_term||' should be a whole number and '
877 ,p_token2=>' 0 '
878 ,p_token3=>' 999 '
879 );
880 ELSIF(P_Loan_Details_Rec.loan_term > P_Loan_Details_Rec.balloon_term) THEN
881 LogErrors(p_message_name=>'LNS_LOAN_TERM_INVALID'
882 ,p_token1=>'P_Loan_Details_Rec.loan_term = '||P_Loan_Details_Rec.loan_term
883 ,p_token2=>'P_Loan_Details_Rec.balloon_term = '||P_Loan_Details_Rec.balloon_term);
884 END IF;
885 ELSIF (nvl(P_Loan_Details_Rec.balloon_payment_type,'TERM') = 'AMOUNT' AND
886 P_Loan_Details_Rec.balloon_payment_amount IS NULL) THEN
887 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
888 ,p_token1=>'P_Loan_Details_Rec.balloon_payment_amount');
889
890 ELSIF (nvl(P_Loan_Details_Rec.balloon_payment_type,'TERM') = 'AMOUNT' AND
891 P_Loan_Details_Rec.balloon_payment_amount IS NOT NULL) THEN
892
893 IF(P_Loan_Details_Rec.balloon_payment_amount > P_Loan_Details_Rec.requested_amount) THEN
894 LogErrors(p_message_name=>'LNS_BALLOON_AMOUNT_INVALID'
895 ,p_token1=>'P_Loan_Details_Rec.balloon_payment_amount = '||P_Loan_Details_Rec.balloon_payment_amount
896 ,p_token2=>'P_Loan_Details_Rec.requested_amount = '||P_Loan_Details_Rec.requested_amount);
897 END IF;
898 END IF;
899 END IF;
900
901 -- Validate Loan Sub Type
902
903 IF P_Loan_Details_Rec.loan_subtype IS NOT NULL
904 AND l_override_rec.loan_subtype_ovr = 'Y' THEN
905 BEGIN
906 SELECT 'Y'
907 INTO l_dummy
908 FROM lns_lookups llk
909 WHERE llk.lookup_code = P_Loan_Details_Rec.loan_subtype
910 AND llk.lookup_type = 'LOAN_SUBTYPE';
911 EXCEPTION
912 WHEN NO_DATA_FOUND THEN
913 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
914 ,p_token1=>'P_Loan_Details_Rec.loan_subtype'
915 ,p_token2=>P_Loan_Details_Rec.loan_subtype);
916 END;
917 END IF;
918
919 -- Validate Collateral Percent
920
921 IF ((P_Loan_Details_Rec.loan_subtype = 'SECURED' AND
922 l_override_rec.loan_subtype_ovr = 'Y') OR
923 (l_product_rec.loan_subtype = 'SECURED' AND
924 l_override_rec.loan_subtype_ovr = 'N'))
925 AND P_Loan_Details_Rec.collateral_percent IS NULL THEN
926 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
927 ,p_token1=>'P_Loan_Details_Rec.collateral_percent');
928 ELSIF P_Loan_Details_Rec.loan_subtype = 'SECURED'
929 AND (P_Loan_Details_Rec.collateral_percent <= 0 OR
930 P_Loan_Details_Rec.collateral_percent > 100) THEN
931 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_COLLPERC');
932 END IF;
933
934 -- Validate Index Rate Id
935 IF P_Loan_Details_Rec.index_rate_id IS NOT NULL
936 AND l_override_rec.index_rate_id_ovr = 'Y' THEN
937 BEGIN
938 SELECT 'Y'
939 INTO l_dummy
940 FROM lns_int_rate_headers lirh
941 WHERE lirh.interest_rate_id = P_Loan_Details_Rec.index_rate_id;
942 EXCEPTION
943 WHEN NO_DATA_FOUND THEN
944 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
945 ,p_token1=>'P_Loan_Details_Rec.index_rate_id'
946 ,p_token2=>P_Loan_Details_Rec.index_rate_id);
947 END;
948 END IF;
949
950 -- Validate Interest Compounding Frequency
951 IF P_Loan_Details_Rec.loan_payment_frequency IS NOT NULL
952 AND l_override_rec.loan_payment_freq_ovr = 'Y' THEN
953 BEGIN
954 SELECT 'Y'
955 INTO l_dummy
956 FROM lns_lookups llk
957 WHERE llk.lookup_code =
958 P_Loan_Details_Rec.loan_payment_frequency
959 AND llk.lookup_type = 'FREQUENCY';
960 EXCEPTION
961 WHEN NO_DATA_FOUND THEN
962 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
963 ,p_token1=>'P_Loan_Details_Rec.loan_payment_frequency'
964 ,p_token2=>P_Loan_Details_Rec.loan_payment_frequency);
965 END;
966 END IF;
967
968 -- Validate Open To Term Event
969 IF P_Loan_Details_Rec.open_to_term_event IS NOT NULL
970 AND l_product_rec.multiple_funding_flag = 'Y' THEN
971 BEGIN
972 SELECT 'Y'
973 INTO l_dummy
974 FROM lns_lookups llk
975 WHERE llk.lookup_code = P_Loan_Details_Rec.open_to_term_event
976 AND llk.lookup_type = 'OPEN_TO_TERM_EVENT';
977 EXCEPTION
978 WHEN NO_DATA_FOUND THEN
979 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
980 ,p_token1=>'P_Loan_Details_Rec.open_to_term_event'
981 ,p_token2=>P_Loan_Details_Rec.open_to_term_event);
982 END;
983 END IF;
984 -- Validate Loan Number
985 -- If Profile not set to Autogenerate make Loan Number Mandatory.
986 l_generate_loan_number := fnd_profile.value('LNS_GENERATE_LOAN_NUMBER');
987 IF l_generate_loan_number IS NULL THEN
988 l_generate_loan_number := 'N';
989 END IF;
990 IF P_Loan_Details_Rec.loan_number is NULL
991 AND l_generate_loan_number = 'N' THEN
992 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
993 ,p_token1=>'P_Loan_Details_Rec.loan_number');
994 END IF;
995 -- Validate Open Index Date
996 IF P_Loan_Details_Rec.open_index_date IS NULL
997 AND l_product_rec.multiple_funding_flag = 'Y' THEN
998 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
999 ,p_token1=>'P_Loan_Details_Rec.open_index_date');
1000 END IF;
1001
1002 -- Validate Term Index Date
1003 IF P_Loan_Details_Rec.term_index_date IS NULL THEN
1004 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
1005 ,p_token1=>'P_Loan_Details_Rec.term_index_date');
1006 END IF;
1007
1008 -- Validate Term Index Rate
1009 IF P_Loan_Details_Rec.term_index_rate IS NULL THEN
1010 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
1011 ,p_token1=>'P_Loan_Details_Rec.term_index_rate');
1012 END IF;
1013
1014 -- Validate Open Index Rate
1015 IF P_Loan_Details_Rec.open_index_rate IS NULL
1016 AND l_product_rec.multiple_funding_flag = 'Y' THEN
1017 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
1018 ,p_token1=>'P_Loan_Details_Rec.open_index_rate');
1019 END IF;
1020
1021 FOR i in 1..P_Loan_Lines_Rec.COUNT
1022 LOOP
1023 v_PAYMENT_SCHEDULE_ID(i) := P_Loan_Lines_Rec(i).PAYMENT_SCHEDULE_ID;
1024 v_requested_amount(i) := P_Loan_Lines_Rec(i).requested_amount;
1025 v_DESCRIPTION(i) := P_Loan_Lines_Rec(i).DESCRIPTION;
1026 v_line_number(i) := P_Loan_Lines_Rec(i).line_number;
1027 v_customer_trx_id(i) := null;
1028 v_remaining_balance(i) := null;
1029 v_installment_number(i) := null;
1030 v_reference_number(i) := null;
1031 END LOOP;
1032 IF l_borrower_valid and l_product_rec.loan_class_code = 'ERS'
1033 THEN
1034 FOR j in 1..v_PAYMENT_SCHEDULE_ID.COUNT
1035 LOOP
1036 BEGIN
1037 select pmt_sch.customer_trx_id
1038 ,pmt_sch.trx_number
1039 ,pmt_sch.amount_due_remaining
1040 ,pmt_sch.terms_sequence_number
1041 INTO v_customer_trx_id(j)
1042 ,v_reference_number(j)
1043 ,v_remaining_balance(j)
1044 ,v_installment_number(j)
1045 FROM
1046 ar_payment_schedules_all pmt_sch,
1047 hz_cust_accounts account,
1048 RA_CUST_TRX_TYPES trx_type,
1049 ar_lookups trx_type_lkup,
1050 ar_lookups trx_class_lkup
1051 WHERE
1052 pmt_sch.class in ('INV','DM') and
1053 pmt_sch.status = 'OP' and
1054 pmt_sch.amount_due_remaining > 0 and
1055 pmt_sch.cust_trx_type_id = trx_type.CUST_TRX_TYPE_ID and
1056 trx_type_lkup.lookup_type = 'INV/CM' and
1057 trx_type_lkup.lookup_code = trx_type.type and
1058 trx_class_lkup.lookup_type = 'INV/CM' and
1059 trx_class_lkup.lookup_code = pmt_sch.class and
1060 pmt_sch.customer_id = account.cust_account_id and
1061 account.party_id = P_Loan_Details_Rec.primary_borrower_party_id and
1062 pmt_sch.INVOICE_CURRENCY_CODE = l_product_rec.loan_currency and
1063 pmt_sch.payment_schedule_id = v_PAYMENT_SCHEDULE_ID(j);
1064
1065 IF nvl( v_requested_amount(j),0) > nvl(v_remaining_balance(j),0)
1066 OR nvl(v_requested_amount(j),1) <= 0 THEN
1067
1068 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1069 ,p_line_number => v_line_number(j)
1070 ,p_token1=>'P_Loan_Lines_Rec.requested_amount'
1071 ,p_token2=>v_requested_amount(j));
1072 END IF;
1073 IF v_requested_amount(j) IS NULL THEN
1074 v_requested_amount(j) := v_remaining_balance(j);
1075 END IF;
1076 l_ers_requested_amount := nvl(l_ers_requested_amount,0) +
1077 nvl(v_requested_amount(j),0);
1078 EXCEPTION
1079 WHEN no_data_found THEN
1080 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
1081 ,p_line_number => v_line_number(j)
1082 ,p_token1=>'P_Loan_Lines_Rec.payment_schedule_id'
1083 ,p_token2=>v_PAYMENT_SCHEDULE_ID(j));
1084 END;
1085 END LOOP;
1086 END IF;
1087 /* INITIALIZE AND DEFAULT VALUES TO BE INSERTED INTO HEADERS, LINES,
1088 PARTICIAPNTS, TERMS and RATESCHEDULES */
1089 IF g_error_count = 0 THEN
1090 -- Initialize all rowtype variables
1091 g_loan_rec := null;
1092 g_participants_rec := null;
1093 g_term_rec := null;
1094 g_rate_term_rec := null;
1095 g_rate_open_rec := null;
1096
1097 -- HEADERS
1098 -- LOAN_ID
1099 select LNS_LOAN_HEADERS_S.nextval
1100 into g_loan_rec.loan_id
1101 from dual;
1102 /* Not needed since LNS_LOAN_HEADER_PUB API will be used for inserting
1103 -- Who Columns
1104 g_loan_rec.last_update_date := sysdate;
1105 g_loan_rec.last_updated_by := -1;
1106 g_loan_rec.creation_date := sysdate;
1107 g_loan_rec.created_by := -1;
1108
1109 g_loan_rec.object_version_number := 1;
1110 */
1111 g_loan_rec.product_id := P_Loan_Details_Rec.product_id;
1112
1113 IF l_generate_loan_number = 'Y' THEN
1114 select 'L' || LNS_LOAN_NUMBER_S.nextval
1115 into g_loan_rec.loan_number
1116 from dual;
1117 END IF;
1118
1119 g_loan_rec.requested_amount :=
1120 CASE l_product_rec.loan_class_code
1121 WHEN 'DIRECT' THEN
1122 nvl(P_Loan_Details_Rec.requested_amount,
1123 l_product_rec.requested_amount)
1124 WHEN 'ERS' THEN 0
1125 END;
1126
1127
1128 g_loan_rec.balloon_payment_type :=
1129 CASE (l_product_rec.multiple_funding_flag = 'N' OR
1130 l_product_rec.open_to_term_flag = 'Y')
1131 WHEN TRUE THEN
1132 NVL(P_Loan_Details_Rec.balloon_payment_type,'TERM')
1133 ELSE null
1134 END;
1135 g_loan_rec.balloon_payment_amount :=
1136 CASE ((l_product_rec.multiple_funding_flag = 'N' OR
1137 l_product_rec.open_to_term_flag = 'Y') AND
1138 nvl(P_Loan_Details_Rec.balloon_payment_type,'TERM')
1139 = 'AMOUNT')
1140 WHEN TRUE THEN
1141 nvl(P_Loan_Details_Rec.balloon_payment_amount,0)
1142 ELSE null
1143 END;
1144
1145 g_loan_rec.multiple_funding_flag :=
1146 nvl(l_product_rec.multiple_funding_flag,'N');
1147 g_loan_rec.open_to_term_flag :=
1148 nvl(l_product_rec.open_to_term_flag,'N');
1149
1150 g_loan_rec.current_phase := CASE g_loan_rec.multiple_funding_flag
1151 WHEN 'Y' THEN 'OPEN'
1152 ELSE 'TERM'
1153 END;
1154 g_loan_rec.open_to_term_event := CASE g_loan_rec.open_to_term_flag
1155 WHEN 'Y' THEN 'AUTO_FINAL_DISBURSEMENT'
1156 ELSE 'NOT_APPLICABLE'
1157 END;
1158 g_loan_rec.credit_review_flag := nvl(nvl(P_Loan_Details_Rec.credit_review_flag
1159 ,l_product_rec.credit_review_flag)
1160 ,'N');
1161 g_loan_rec.loan_description := P_Loan_Details_Rec.loan_description;
1162 g_loan_rec.org_id := l_product_rec.org_id;
1163 g_loan_rec.loan_type_id := l_product_rec.loan_type_id;
1164 g_loan_rec.loan_class_code := l_product_rec.loan_class_code;
1165 g_loan_rec.legal_entity_id := CASE l_override_rec.legal_entity_id_ovr
1166 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.legal_entity_id,
1167 l_product_rec.legal_entity_id)
1168 ELSE l_product_rec.legal_entity_id
1169 END;
1170
1171 g_loan_rec.loan_term :=
1172 CASE ((l_override_rec.loan_term_ovr = 'Y') AND
1173 (l_product_rec.multiple_funding_flag = 'N' OR
1174 l_product_rec.open_to_term_flag = 'Y'))
1175 WHEN TRUE THEN
1176 nvl(P_Loan_Details_Rec.loan_term,
1177 l_product_rec.loan_term)
1178 ELSE null
1179 END;
1180
1181 g_loan_rec.loan_term_period :=
1182 CASE ((l_override_rec.loan_term_period_ovr = 'Y') AND
1183 (l_product_rec.multiple_funding_flag = 'N' OR
1184 l_product_rec.open_to_term_flag = 'Y'))
1185 WHEN TRUE THEN
1186 nvl(P_Loan_Details_Rec.loan_term_period,
1187 l_product_rec.loan_term_period)
1188 ELSE null
1189 END;
1190 g_loan_rec.amortized_term :=
1191 CASE (l_product_rec.multiple_funding_flag = 'N' OR
1192 l_product_rec.open_to_term_flag = 'Y')
1193 WHEN TRUE THEN
1194 P_Loan_Details_Rec.balloon_term
1195 ELSE null
1196 END;
1197 g_loan_rec.amortized_term_period :=
1198 CASE ((l_product_rec.multiple_funding_flag = 'N' OR
1199 l_product_rec.open_to_term_flag = 'Y') AND
1200 nvl(P_Loan_Details_Rec.balloon_payment_type,'TERM')
1201 = 'TERM')
1202 WHEN TRUE THEN
1203 P_Loan_Details_Rec.loan_term_period
1204 ELSE null
1205 END;
1206 g_loan_rec.loan_currency := l_product_rec.loan_currency;
1207 g_loan_rec.loan_subtype := CASE l_override_rec.loan_subtype_ovr
1208 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.loan_subtype,
1209 l_product_rec.loan_subtype)
1210 ELSE l_product_rec.loan_subtype
1211 END;
1212 g_loan_rec.collateral_percent := nvl(P_Loan_Details_Rec.collateral_percent,
1213 l_product_rec.collateral_percent);
1214 g_loan_rec.open_loan_term := CASE g_loan_rec.multiple_funding_flag
1215 WHEN 'Y' THEN
1216 nvl(P_Loan_Details_Rec.open_loan_term,
1217 l_product_rec.loan_term)
1218 ELSE null
1219 END;
1220 g_loan_rec.open_loan_term_period := CASE g_loan_rec.multiple_funding_flag
1221 WHEN 'Y' THEN
1222 nvl(P_Loan_Details_Rec.open_loan_term_period,
1223 l_product_rec.loan_term_period)
1224 ELSE null
1225 END;
1226 g_loan_rec.open_loan_start_date := CASE g_loan_rec.multiple_funding_flag
1227 WHEN 'Y' THEN
1228 nvl(P_Loan_Details_Rec.open_loan_start_date,
1229 sysdate)
1230 ELSE NULL
1231 END;
1232 g_loan_rec.loan_application_date :=
1233 nvl(P_Loan_Details_Rec.loan_application_date,
1234 sysdate);
1235 g_loan_rec.gl_date := sysdate;
1236 g_loan_rec.loan_status := 'INCOMPLETE';
1237 g_loan_rec.primary_borrower_id
1238 := P_Loan_Details_Rec.primary_borrower_party_id;
1239 g_loan_rec.cust_account_id := P_Loan_Details_Rec.cust_account_id;
1240 g_loan_rec.bill_to_acct_site_id := P_Loan_Details_Rec.bill_to_acct_site_id;
1241 g_loan_rec.contact_pers_party_id
1242 := l_contact_person_party_id;
1243 g_loan_rec.contact_rel_party_id
1244 := P_Loan_Details_Rec.contact_rel_party_id;
1245 g_loan_rec.loan_purpose_code
1246 := nvl(P_Loan_Details_Rec.loan_purpose_code,'OTHER');
1247 g_loan_rec.loan_assigned_to := P_Loan_Details_Rec.loan_assigned_to;
1248 g_loan_rec.created_by_module := P_created_by_module;
1249 g_loan_rec.application_id := P_application_id;
1250 g_loan_rec.party_type := l_product_rec.party_type;
1251 g_loan_rec.reference_type_id := CASE l_product_rec.loan_class_code
1252 WHEN 'ERS' THEN
1253 P_Loan_Details_Rec.trx_type_id
1254 ELSE NULL
1255 END;
1256
1257
1258 g_participant_rec.loan_id := g_loan_rec.loan_id;
1259 g_participant_rec.loan_participant_type := 'PRIMARY_BORROWER';
1260 g_participant_rec.hz_party_id := P_Loan_Details_Rec.primary_borrower_party_id;
1261 g_participant_rec.start_date_active := sysdate;
1262 g_participant_rec.cust_account_id := P_Loan_Details_Rec.cust_account_id;
1263 g_participant_rec.bill_to_acct_site_id := P_Loan_Details_Rec.bill_to_acct_site_id;
1264 g_participant_rec.bill_to_acct_site_id := P_Loan_Details_Rec.bill_to_acct_site_id;
1265 g_participant_rec.contact_pers_party_id := l_contact_person_party_id;
1266 g_participant_rec.contact_rel_party_id := P_Loan_Details_Rec.contact_rel_party_id;
1267
1268 g_term_rec.loan_id := g_loan_rec.loan_id;
1269
1270 select LNS_TERMS_S.nextval
1271 into g_term_rec.term_id
1272 from dual;
1273
1274 g_term_rec.rate_type := l_product_rec.rate_type;
1275 g_term_rec.index_rate_id := CASE l_override_rec.index_rate_id_ovr
1276 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.index_rate_id,
1277 l_product_rec.index_rate_id)
1278 ELSE l_product_rec.index_rate_id
1279 END;
1280 g_term_rec.calculation_method := l_product_rec.interest_calculation_method;
1281
1282 g_term_rec.day_count_method := l_product_rec.day_count_method;
1283 g_term_rec.floor_rate := l_product_rec.floor_rate;
1284 g_term_rec.ceiling_rate := l_product_rec.ceiling_rate;
1285 g_term_rec.loan_payment_frequency :=
1286 CASE l_override_rec.loan_payment_freq_ovr
1287 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.loan_payment_frequency,
1288 l_product_rec.loan_payment_frequency)
1289 ELSE l_product_rec.loan_payment_frequency
1290 END;
1291 /*Per Bug#3999953
1292 * *** RAVERMA 11/10/04 04:22 pm RESPONSE ***
1293 * FOR NOW
1294 * 4. payment_Frequency = amortization_frequency = interest_compounding_freq
1295 */
1296 g_term_rec.amortization_frequency := g_term_rec.loan_payment_frequency;
1297 g_term_rec.interest_compounding_freq := g_term_rec.loan_payment_frequency;
1298
1299
1300
1301 g_loan_rec.open_maturity_date := CASE l_product_rec.multiple_funding_flag
1302 WHEN 'Y' THEN
1303 lns_fin_utils.getMaturityDate(
1304 p_term => g_loan_rec.open_loan_term,
1305 p_term_period => g_loan_rec.open_loan_term_period,
1306 p_frequency => g_term_rec.amortization_frequency,
1307 p_start_date => g_loan_rec.open_loan_start_date
1308 )
1309 ELSE NULL
1310 END;
1311
1312 g_loan_rec.loan_start_date := CASE l_product_rec.multiple_funding_flag
1313 WHEN 'Y' THEN
1314 nvl(g_loan_rec.open_maturity_date,(nvl(P_Loan_Details_Rec.loan_start_date,sysdate)))
1315 ELSE
1316 nvl(P_Loan_Details_Rec.loan_start_date,sysdate)
1317 END;
1318
1319 g_loan_rec.loan_maturity_date :=
1320 lns_fin_utils.getMaturityDate(
1321 p_term => g_loan_rec.loan_term,
1322 p_term_period => g_loan_rec.loan_term_period,
1323 p_frequency => g_term_rec.amortization_frequency,
1324 p_start_date => g_loan_rec.loan_start_date
1325 );
1326
1327 g_term_rec.open_first_payment_date :=
1328 CASE l_product_rec.multiple_funding_flag
1329 WHEN 'N' THEN null
1330 ELSE lns_fin_utils.getNextDate(p_date=> g_loan_rec.loan_start_date
1331 ,p_interval_type =>
1332 g_term_rec.loan_payment_frequency
1333 ,p_direction => 1)
1334 END;
1335
1336 g_term_rec.open_payment_frequency :=
1337 CASE l_product_rec.multiple_funding_flag
1338 WHEN 'N' THEN null
1339 ELSE g_term_rec.loan_payment_frequency
1340 END;
1341 g_term_rec.reamortize_over_payment :=
1342 l_product_rec.reamortize_over_payment;
1343 g_term_rec.rate_change_frequency := l_product_rec.rate_change_frequency;
1344 g_term_rec.payment_application_order :=
1345 l_product_rec.payment_application_order;
1346 g_term_rec.pmt_appl_order_scope := l_product_rec.pmt_appl_order_scope;
1347 g_term_rec.open_floor_rate := CASE l_product_rec.multiple_funding_flag
1348 WHEN 'Y' THEN
1349 l_product_rec.open_floor_rate
1350 ELSE null
1351 END;
1352 g_term_rec.open_ceiling_rate := CASE l_product_rec.multiple_funding_flag
1353 WHEN 'Y' THEN
1354 l_product_rec.open_ceiling_rate
1355 ELSE null
1356 END;
1357 g_term_rec.reamortize_under_payment :=
1358 l_product_rec.reamortize_under_payment;
1359 g_term_rec.reamortize_under_payment :=
1360 l_product_rec.reamortize_under_payment;
1361 g_term_rec.percent_increase := l_product_rec.percent_increase;
1362 g_term_rec.percent_increase_life := l_product_rec.percent_increase_life;
1363 g_term_rec.first_percent_increase := l_product_rec.first_percent_increase;
1364 g_term_rec.open_percent_increase_life :=
1365 CASE l_product_rec.multiple_funding_flag
1366 WHEN 'Y' THEN
1367 l_product_rec.open_percent_increase_life
1368 ELSE null
1369 END;
1370 g_term_rec.open_first_percent_increase :=
1371 l_product_rec.open_first_percent_increase;
1372 g_term_rec.based_on_balance := 'PRIN';
1373 g_term_rec.payment_application_order := 'INT_PRIN_FEE';
1374 g_term_rec.open_index_date := CASE l_product_rec.multiple_funding_flag
1375 WHEN 'Y' THEN
1376 P_Loan_Details_Rec.open_index_date
1377 ELSE null
1378 END;
1379
1380 g_term_rec.term_index_date :=
1381 CASE (l_product_rec.multiple_funding_flag = 'N' OR
1382 l_product_rec.open_to_term_flag = 'Y')
1383 WHEN TRUE THEN
1384 P_Loan_Details_Rec.term_index_date
1385 ELSE null
1386 END;
1387
1388
1389
1390
1391 g_rate_term_rec.term_id := g_term_rec.term_id;
1392
1393 SELECT LNS_RATE_SCHEDULES_S.nextval
1394 INTO g_rate_term_rec.rate_id
1395 FROM dual;
1396
1397 g_rate_term_rec.index_rate := P_Loan_Details_Rec.term_index_rate;
1398 g_rate_term_rec.spread := CASE l_override_rec.spread_ovr
1399 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.term_spread,
1400 l_product_rec.spread)
1401 ELSE l_product_rec.spread
1402 END;
1403
1404 g_rate_term_rec.current_interest_rate := g_rate_term_rec.index_rate
1405 + nvl(g_rate_term_rec.spread,0);
1406 g_rate_term_rec.phase := 'TERM';
1407 g_rate_term_rec.interest_only_flag := 'N';
1408 g_rate_term_rec.floating_flag := 'N';
1409 g_rate_term_rec.begin_installment_number := 1;
1410 g_rate_term_rec.end_installment_number :=
1411 Lns_Fin_Utils.intervalsInPeriod(p_period_number=> g_loan_rec.loan_term
1412 ,p_period_type1 => g_loan_rec.loan_term_period
1413 ,p_period_type2 => g_term_rec.amortization_frequency);
1414 g_rate_term_rec.start_date_active := sysdate;
1415 -- Who Columns
1416 g_rate_term_rec.last_update_date := sysdate;
1417 g_rate_term_rec.last_updated_by := lns_utility_pub.created_by;
1418 g_rate_term_rec.creation_date := sysdate;
1419 g_rate_term_rec.created_by := lns_utility_pub.last_updated_by;
1420 g_rate_term_rec.object_version_number := 1;
1421
1422 IF l_product_rec.multiple_funding_flag = 'Y' THEN
1423
1424 g_rate_open_rec.term_id := g_term_rec.term_id;
1425
1426 SELECT LNS_RATE_SCHEDULES_S.nextval
1427 INTO g_rate_open_rec.rate_id
1428 FROM dual;
1429
1430 g_rate_open_rec.index_rate := P_Loan_Details_Rec.open_index_rate;
1431 g_rate_open_rec.spread := CASE l_override_rec.open_spread_ovr
1432 WHEN 'Y' THEN nvl(P_Loan_Details_Rec.open_spread,
1433 l_product_rec.open_spread)
1434 ELSE l_product_rec.open_spread
1435 END;
1436
1437 g_rate_open_rec.current_interest_rate := g_rate_open_rec.index_rate
1438 + nvl(g_rate_open_rec.spread,0);
1439 g_rate_open_rec.phase := 'OPEN';
1440 g_rate_open_rec.interest_only_flag := 'N';
1441 g_rate_open_rec.floating_flag := 'N';
1442 g_rate_open_rec.begin_installment_number := 1;
1443 g_rate_open_rec.end_installment_number :=
1444 Lns_Fin_Utils.intervalsInPeriod(p_period_number=> g_loan_rec.open_loan_term
1445 ,p_period_type1 => g_loan_rec.open_loan_term_period
1446 ,p_period_type2 => g_term_rec.amortization_frequency);
1447 g_rate_open_rec.start_date_active := sysdate;
1448
1449 -- Who Columns
1450 g_rate_open_rec.last_update_date := sysdate;
1451 g_rate_open_rec.last_updated_by := lns_utility_pub.last_updated_by;
1452 g_rate_open_rec.creation_date := sysdate;
1453 g_rate_open_rec.created_by := lns_utility_pub.created_by;
1454 g_rate_open_rec.object_version_number := 1;
1455
1456 END IF;
1457 END IF;
1458 -- Start Inserting into various tables if no errors are found so far.
1459 SAVEPOINT before_insert;
1460 IF g_error_count = 0 THEN
1461 -- Insert Header
1462 LNS_LOAN_HEADER_PUB .create_loan (
1463 p_init_msg_list => 'T'
1464 ,p_loan_header_rec => g_loan_rec
1465 ,x_loan_id => l_loan_id
1466 ,x_loan_number => l_loan_number
1467 ,x_return_status => l_header_insert_success
1468 ,x_msg_count => l_message_count
1469 ,x_msg_data => l_message_data
1470 );
1471 IF l_header_insert_success <> 'S' THEN
1472
1473 LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_HEADER'
1474 ,p_token1 => l_message_data);
1475
1476 rollback to SAVEPOINT before_insert;
1477 raise error_while_insert;
1478 END IF;
1479
1480 -- Insert Participant
1481 LNS_PARTICIPANTS_PUB.createParticipant (
1482 p_init_msg_list => 'T'
1483 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
1484 ,p_loan_participant_rec => g_participant_rec
1485 ,x_participant_id => l_participant_id
1486 ,x_return_status => l_part_insert_success
1487 ,x_msg_count => l_message_count
1488 ,x_msg_data => l_message_data
1489 );
1490 IF l_part_insert_success <> 'S' THEN
1491
1492 LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_PART'
1493 ,p_token1 =>l_message_data);
1494
1495 rollback to SAVEPOINT before_insert;
1496 raise error_while_insert;
1497 END IF;
1498
1499 -- Insert Lines if ERS and update Loan Header Record
1500 IF l_product_rec.loan_class_code = 'ERS' THEN
1501 IF P_Loan_Lines_Rec.COUNT <> 0 THEN
1502 FORALL i in v_customer_trx_id.FIRST..v_customer_trx_id.LAST
1503 INSERT INTO LNS_LOAN_LINES
1504 (
1505 LOAN_LINE_ID
1506 ,LOAN_ID
1507 ,LAST_UPDATE_DATE
1508 ,LAST_UPDATED_BY
1509 ,CREATION_DATE
1510 ,CREATED_BY
1511 ,OBJECT_VERSION_NUMBER
1512 ,REFERENCE_TYPE
1513 ,REFERENCE_ID
1514 ,REFERENCE_NUMBER
1515 ,REFERENCE_DESCRIPTION
1516 ,REFERENCE_AMOUNT
1517 ,REQUESTED_AMOUNT
1518 ,PAYMENT_SCHEDULE_ID
1519 ,INSTALLMENT_NUMBER
1520 )
1521 VALUES
1522 (
1523 LNS_LOAN_LINE_S.nextval
1524 ,g_loan_rec.loan_id
1525 ,sysdate
1526 ,lns_utility_pub.created_by
1527 ,sysdate
1528 ,lns_utility_pub.created_by
1529 ,1
1530 ,'RECEIVABLE'
1531 ,v_customer_trx_id(i)
1532 ,v_reference_number(i)
1533 ,v_DESCRIPTION(i)
1534 ,v_remaining_balance(i)
1535 ,v_requested_amount(i)
1536 ,v_PAYMENT_SCHEDULE_ID(i)
1537 ,v_installment_number(i)
1538 );
1539 ELSIF P_Loan_Lines_Rec.COUNT = 0 THEN
1540 l_ers_requested_amount :=
1541 LNS_LOAN_LINE_PUB.GET_RULES_DERIVED_ERS_AMOUNT(
1542 p_loan_id => g_loan_rec.loan_id
1543 ,p_primary_borrower_id => g_loan_rec.primary_borrower_id
1544 ,p_currency_code => g_loan_rec.loan_currency
1545 ,p_org_id => g_loan_rec.org_id
1546 ,p_loan_product_id => P_Loan_Details_Rec.product_id
1547 );
1548 IF l_ers_requested_amount = 0 THEN
1549 LogErrors(p_message_name=>'LNS_LCREATE_ERR_LINE_DERIVE');
1550 rollback to SAVEPOINT before_insert;
1551 raise error_while_insert;
1552 END IF;
1553 END IF;
1554 UPDATE lns_loan_headers_all
1555 SET requested_amount = l_ers_requested_amount
1556 ,object_version_number = 2
1557 WHERE loan_id = g_loan_rec.loan_id;
1558 END IF;
1559 -- Insert Term
1560 LNS_TERMS_PUB.create_term (
1561 p_init_msg_list => 'T'
1562 ,p_loan_term_rec => g_term_rec
1563 ,x_term_id => l_term_id
1564 ,x_return_status => l_term_insert_success
1565 ,x_msg_count => l_message_count
1566 ,x_msg_data => l_message_data
1567 );
1568 IF l_term_insert_success <> 'S' THEN
1569
1570 LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_TERM'
1571 ,p_token1 =>l_message_data);
1572
1573 rollback to SAVEPOINT before_insert;
1574 raise error_while_insert;
1575 END IF;
1576 --Insert Term Rate Schedule
1577 IF (l_product_rec.open_to_term_flag = 'Y'
1578 OR l_product_rec.multiple_funding_flag = 'N') THEN
1579 BEGIN
1580 insert into LNS_RATE_SCHEDULES
1581 (RATE_ID
1582 ,TERM_ID
1583 ,INDEX_RATE
1584 ,SPREAD
1585 ,CURRENT_INTEREST_RATE
1586 ,START_DATE_ACTIVE
1587 ,END_DATE_ACTIVE
1588 ,CREATED_BY
1589 ,CREATION_DATE
1590 ,LAST_UPDATED_BY
1591 ,LAST_UPDATE_DATE
1592 ,LAST_UPDATE_LOGIN
1593 ,OBJECT_VERSION_NUMBER
1594 ,INDEX_DATE
1595 ,BEGIN_INSTALLMENT_NUMBER
1596 ,END_INSTALLMENT_NUMBER
1597 ,INTEREST_ONLY_FLAG
1598 ,PHASE
1599 ,FLOATING_FLAG)
1600 VALUES
1601 (g_rate_term_rec.RATE_ID
1602 ,g_rate_term_rec.TERM_ID
1603 ,g_rate_term_rec.INDEX_RATE
1604 ,g_rate_term_rec.SPREAD
1605 ,g_rate_term_rec.CURRENT_INTEREST_RATE
1606 ,g_rate_term_rec.START_DATE_ACTIVE
1607 ,g_rate_term_rec.END_DATE_ACTIVE
1608 ,g_rate_term_rec.CREATED_BY
1609 ,g_rate_term_rec.CREATION_DATE
1610 ,g_rate_term_rec.LAST_UPDATED_BY
1611 ,g_rate_term_rec.LAST_UPDATE_DATE
1612 ,g_rate_term_rec.LAST_UPDATE_LOGIN
1613 ,g_rate_term_rec.OBJECT_VERSION_NUMBER
1614 ,g_rate_term_rec.INDEX_DATE
1615 ,g_rate_term_rec.BEGIN_INSTALLMENT_NUMBER
1616 ,g_rate_term_rec.END_INSTALLMENT_NUMBER
1617 ,g_rate_term_rec.INTEREST_ONLY_FLAG
1618 ,g_rate_term_rec.PHASE
1619 ,g_rate_term_rec.FLOATING_FLAG);
1620 EXCEPTION
1621 WHEN OTHERS THEN
1622 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
1623 LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_TERMRATE'
1624 ,p_token1 => sqlerrm);
1625 rollback to SAVEPOINT before_insert;
1626 raise error_while_insert;
1627 END;
1628 END IF;
1629
1630 --Insert Open Rate Schedule
1631 IF ( l_product_rec.multiple_funding_flag = 'Y') THEN
1632 BEGIN
1633 insert into LNS_RATE_SCHEDULES
1634 (RATE_ID
1635 ,TERM_ID
1636 ,INDEX_RATE
1637 ,SPREAD
1638 ,CURRENT_INTEREST_RATE
1639 ,START_DATE_ACTIVE
1640 ,END_DATE_ACTIVE
1641 ,CREATED_BY
1642 ,CREATION_DATE
1643 ,LAST_UPDATED_BY
1644 ,LAST_UPDATE_DATE
1645 ,LAST_UPDATE_LOGIN
1646 ,OBJECT_VERSION_NUMBER
1647 ,INDEX_DATE
1648 ,BEGIN_INSTALLMENT_NUMBER
1649 ,END_INSTALLMENT_NUMBER
1650 ,INTEREST_ONLY_FLAG
1651 ,PHASE
1652 ,FLOATING_FLAG)
1653 VALUES
1654 (g_rate_open_rec.RATE_ID
1655 ,g_rate_open_rec.TERM_ID
1656 ,g_rate_open_rec.INDEX_RATE
1657 ,g_rate_open_rec.SPREAD
1658 ,g_rate_open_rec.CURRENT_INTEREST_RATE
1659 ,g_rate_open_rec.START_DATE_ACTIVE
1660 ,g_rate_open_rec.END_DATE_ACTIVE
1661 ,g_rate_open_rec.CREATED_BY
1662 ,g_rate_open_rec.CREATION_DATE
1663 ,g_rate_open_rec.LAST_UPDATED_BY
1664 ,g_rate_open_rec.LAST_UPDATE_DATE
1665 ,g_rate_open_rec.LAST_UPDATE_LOGIN
1666 ,g_rate_open_rec.OBJECT_VERSION_NUMBER
1667 ,g_rate_open_rec.INDEX_DATE
1668 ,g_rate_open_rec.BEGIN_INSTALLMENT_NUMBER
1669 ,g_rate_open_rec.END_INSTALLMENT_NUMBER
1670 ,g_rate_open_rec.INTEREST_ONLY_FLAG
1671 ,g_rate_open_rec.PHASE
1672 ,g_rate_open_rec.FLOATING_FLAG);
1673 EXCEPTION
1674 WHEN OTHERS THEN
1675 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
1676 LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_OPENRATE'
1677 ,p_token1 => sqlerrm);
1678 rollback to SAVEPOINT before_insert;
1679 raise error_while_insert;
1680 END;
1681 END IF;
1682
1683 l_object_version_number := 1;
1684 -- Update Delinquency amount in Term
1685 LNS_TERMS_PUB.default_delinquency_amount (
1686 p_term_id => l_term_id
1687 ,p_loan_id => g_loan_rec.loan_id
1688 ,p_object_version_number=> l_object_version_number
1689 ,x_return_status => l_term_update_success
1690 ,x_msg_count => l_message_count
1691 ,x_msg_data => l_message_data
1692 );
1693 IF l_term_update_success <> 'S' THEN
1694
1695 LogErrors(p_message_name=>'LNS_LCREATE_UPD_ERR_TERM'
1696 ,p_token1 =>l_message_data);
1697
1698 rollback to SAVEPOINT before_insert;
1699 raise error_while_insert;
1700 END IF;
1701 END IF;
1702 IF g_error_count > 0 THEN
1703 InsertErrors;
1704 X_RETURN_STATUS := 'E';
1705 X_MSG_COUNT := g_error_count;
1706 ELSE
1707 X_RETURN_STATUS := 'S';
1708 X_MSG_COUNT := 0;
1709 X_LOAN_ID := l_loan_id;
1710 END IF;
1711 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1712 EXCEPTION
1713 WHEN product_not_found THEN
1714 InsertErrors;
1715 X_RETURN_STATUS := 'E';
1716 X_MSG_COUNT := g_error_count;
1717 WHEN error_while_insert THEN
1718 InsertErrors;
1719 X_RETURN_STATUS := 'E';
1720 X_MSG_COUNT := g_error_count;
1721 WHEN OTHERS THEN
1722 InsertErrors;
1723 X_RETURN_STATUS := 'E';
1724 X_MSG_COUNT := g_error_count;
1725 raise;
1726 END CREATE_LOAN;
1727
1728 BEGIN
1729 G_LOG_ENABLED := 'N';
1730 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1731
1732 /* getting msg logging info */
1733 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
1734 if (G_LOG_ENABLED = 'N') then
1735 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1736 else
1737 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
1738 end if;
1739
1740 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1741 END LNS_LOAN_PUB;