1 PACKAGE BODY igf_sl_loans_pkg AS
2 /* $Header: IGFLI09B.pls 120.3 2006/08/03 12:36:04 tsailaja noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sl_loans_all%ROWTYPE;
6 new_references igf_sl_loans_all%ROWTYPE;
7 g_v_called_from VARCHAR2(30);
8
9 PROCEDURE set_column_values (
10 p_action IN VARCHAR2 ,
11 x_rowid IN VARCHAR2 ,
12 x_loan_id IN NUMBER ,
13 x_award_id IN NUMBER ,
14 x_seq_num IN NUMBER ,
15 x_loan_number IN VARCHAR2 ,
16 x_loan_per_begin_date IN DATE ,
17 x_loan_per_end_date IN DATE ,
18 x_loan_status IN VARCHAR2 ,
19 x_loan_status_date IN DATE ,
20 x_loan_chg_status IN VARCHAR2 ,
21 x_loan_chg_status_date IN DATE ,
22 x_active IN VARCHAR2 ,
23 x_active_date IN DATE ,
24 x_borw_detrm_code IN VARCHAR2 ,
25 -- x_loan_status_desc IN VARCHAR2 ,
26 -- x_loan_chg_status_desc IN VARCHAR2 ,
27 x_legacy_record_flag IN VARCHAR2 ,
28 x_creation_date IN DATE ,
29 x_created_by IN NUMBER ,
30 x_last_update_date IN DATE ,
31 x_last_updated_by IN NUMBER ,
32 x_last_update_login IN NUMBER ,
33 x_external_loan_id_txt IN VARCHAR2
34 ) AS
35 /*
36 || Created By : venagara
37 || Created On : 02-DEC-2000
38 || Purpose : Initialises the Old and New references for the columns of the table.
39 || Known limitations, enhancements or remarks :
40 || Change History :
41 || Who When What
42 || smadathi 14-oct-2004 Bug 3416936.Added new column as per TD.
43 || agairola 15-Mar-2002 Added the code for the new column
44 || for the Borrower Determination
45 || (reverse chronological order - newest change first)
46 */
47
48 CURSOR cur_old_ref_values IS
49 SELECT *
50 FROM IGF_SL_LOANS_ALL
51 WHERE rowid = x_rowid;
52
53 BEGIN
54
55 l_rowid := x_rowid;
56
57 -- Code for setting the Old and New Reference Values.
58 -- Populate Old Values.
59 OPEN cur_old_ref_values;
60 FETCH cur_old_ref_values INTO old_references;
61 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
62 CLOSE cur_old_ref_values;
63 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
64 IGS_GE_MSG_STACK.ADD;
65 APP_EXCEPTION.RAISE_EXCEPTION;
66 RETURN;
67 END IF;
68 CLOSE cur_old_ref_values;
69
70 -- Populate New Values.
71 new_references.loan_id := x_loan_id;
72 new_references.award_id := x_award_id;
73 new_references.seq_num := x_seq_num;
74 new_references.loan_number := x_loan_number;
75 new_references.loan_per_begin_date := x_loan_per_begin_date;
76 new_references.loan_per_end_date := x_loan_per_end_date;
77 new_references.loan_status := x_loan_status;
78 new_references.loan_status_date := x_loan_status_date;
79 new_references.loan_chg_status := x_loan_chg_status;
80 new_references.loan_chg_status_date := x_loan_chg_status_date;
81 new_references.active := x_active;
82 new_references.active_date := x_active_date;
83 new_references.borw_detrm_code := x_borw_detrm_code;
84 new_references.legacy_record_flag := x_legacy_record_flag;
85 -- new_references.loan_status_desc := x_loan_status_desc ;
86 -- new_references.loan_chg_status_desc := x_loan_chg_status_desc ;
87
88 IF (p_action = 'UPDATE') THEN
89 new_references.creation_date := old_references.creation_date;
90 new_references.created_by := old_references.created_by;
91 ELSE
92 new_references.creation_date := x_creation_date;
93 new_references.created_by := x_created_by;
94 END IF;
95
96 new_references.last_update_date := x_last_update_date;
97 new_references.last_updated_by := x_last_updated_by;
98 new_references.last_update_login := x_last_update_login;
99 new_references.external_loan_id_txt := x_external_loan_id_txt;
100 END set_column_values;
101
102
103 PROCEDURE check_uniqueness AS
104 /*
105 || Created By : venagara
106 || Created On : 02-DEC-2000
107 || Purpose : Handles the Unique Constraint logic defined for the columns.
108 || Known limitations, enhancements or remarks :
109 || Change History :
110 || Who When What
111 || (reverse chronological order - newest change first)
112 || agairola 15-Mar-2002 Added the code for the new column
113 || for the Borrower Determination
114 */
115 BEGIN
116
117 IF ( get_uk_for_validation ( new_references.loan_number )) THEN
118 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
119 IGS_GE_MSG_STACK.ADD;
120 APP_EXCEPTION.RAISE_EXCEPTION;
121 END IF;
122
123 IF ( get_uk2_for_validation ( new_references.award_id ) ) THEN
124 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
125 IGS_GE_MSG_STACK.ADD;
126 APP_EXCEPTION.RAISE_EXCEPTION;
127 END IF;
128
129 END check_uniqueness;
130
131
132 PROCEDURE check_parent_existance AS
133 /*
134 || Created By : venagara
135 || Created On : 02-DEC-2000
136 || Purpose : Checks for the existance of Parent records.
137 || Known limitations, enhancements or remarks :
138 || Change History :
139 || Who When What
140 || (reverse chronological order - newest change first)
141 || agairola 15-Mar-2002 Added the code for the new column
142 || for the Borrower Determination
143 */
144 BEGIN
145
146 IF (((old_references.award_id = new_references.award_id)) OR
147 ((new_references.award_id IS NULL))) THEN
148 NULL;
149 ELSIF NOT igf_aw_award_pkg.get_pk_for_validation ( new_references.award_id ) THEN
150 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
151 IGS_GE_MSG_STACK.ADD;
152 APP_EXCEPTION.RAISE_EXCEPTION;
153 END IF;
154
155 IF (((old_references.borw_detrm_code = new_references.borw_detrm_code)) OR
156 ((new_references.borw_detrm_code IS NULL))) THEN
157 NULL;
158 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation( 'IGS_FI_BORW_DETRM', new_references.borw_detrm_code ) THEN
159 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
160 IGS_GE_MSG_STACK.ADD;
161 APP_EXCEPTION.RAISE_EXCEPTION;
162 END IF;
163 END check_parent_existance;
164
165
166 PROCEDURE check_child_existance IS
167 /*
168 || Created By : venagara
169 || Created On : 02-DEC-2000
170 || Purpose : Checks for the existance of Child records.
171 || Known limitations, enhancements or remarks :
172 || Change History :
173 || Who When What
174 || (reverse chronological order - newest change first)
175 || smadathi 14-OCT-2004 Enh. Bug 3416936. Added call to
176 || igf_sl_clchsn_dtls_pkg.get_ufk_igf_sl_loans.
177 || agairola 15-Mar-2002 Added the code for the new column
178 || for the Borrower Determination
179 */
180 BEGIN
181
182 igf_sl_alt_borw_pkg.get_fk_igf_sl_loans ( old_references.loan_id );
183
184 igf_sl_dl_manifest_pkg.get_fk_igf_sl_loans ( old_references.loan_id );
185
186 igf_sl_dl_pnote_p_p_pkg.get_fk_igf_sl_loans ( old_references.loan_id );
187
188 igf_sl_dl_pnote_s_p_pkg.get_fk_igf_sl_loans ( old_references.loan_id );
189
190 igf_sl_lor_pkg.get_fk_igf_sl_loans ( old_references.loan_id );
191
192 igf_sl_clchsn_dtls_pkg.get_ufk_igf_sl_loans(x_loan_number => old_references.loan_number);
193
194 END check_child_existance;
195
196
197 FUNCTION get_pk_for_validation (
198 x_loan_id IN NUMBER
199 ) RETURN BOOLEAN AS
200 /*
201 || Created By : venagara
202 || Created On : 02-DEC-2000
203 || Purpose : Validates the Primary Key of the table.
204 || Known limitations, enhancements or remarks :
205 || Change History :
206 || Who When What
207 || (reverse chronological order - newest change first)
208 || agairola 15-Mar-2002 Added the code for the new column
209 || for the Borrower Determination
210 */
211 CURSOR cur_rowid IS
212 SELECT rowid
213 FROM igf_sl_loans_all
214 WHERE loan_id = x_loan_id
215 FOR UPDATE NOWAIT;
216
217 lv_rowid cur_rowid%ROWTYPE;
218
219 BEGIN
220
221 OPEN cur_rowid;
222 FETCH cur_rowid INTO lv_rowid;
223 IF (cur_rowid%FOUND) THEN
224 CLOSE cur_rowid;
225 RETURN(TRUE);
226 ELSE
227 CLOSE cur_rowid;
228 RETURN(FALSE);
229 END IF;
230
231 END get_pk_for_validation;
232
233
234 FUNCTION get_uk_for_validation ( x_loan_number IN VARCHAR2 )
235 RETURN BOOLEAN AS
236 /*
237 || Created By : venagara
238 || Created On : 02-DEC-2000
239 || Purpose : Validates the Unique Keys of the table.
240 || Known limitations, enhancements or remarks :
241 || Change History :
242 || Who When What
243 || (reverse chronological order - newest change first)
244 || agairola 15-Mar-2002 Added the code for the new column
245 || for the Borrower Determination
246 */
247 CURSOR cur_rowid IS
248 SELECT rowid
249 FROM igf_sl_loans_all
250 WHERE loan_number = x_loan_number
251 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
252
253 lv_rowid cur_rowid%ROWTYPE;
254
255 BEGIN
256
257 OPEN cur_rowid;
258 FETCH cur_rowid INTO lv_rowid;
259 IF (cur_rowid%FOUND) THEN
260 CLOSE cur_rowid;
261 RETURN (true);
262 ELSE
263 CLOSE cur_rowid;
264 RETURN(FALSE);
265 END IF;
266
267 END get_uk_for_validation ;
268
269
270 FUNCTION get_uk2_for_validation (
271 x_award_id IN NUMBER
272 ) RETURN BOOLEAN AS
273 /*
274 || Created By : venagara
275 || Created On : 02-DEC-2000
276 || Purpose : Validates the Unique Keys of the table.
277 || Known limitations, enhancements or remarks :
278 || Change History :
279 || Who When What
280 || (reverse chronological order - newest change first)
281 || agairola 15-Mar-2002 Added the code for the new column
282 || for the Borrower Determination
283 */
284 CURSOR cur_rowid IS
285 SELECT rowid
286 FROM igf_sl_loans_all
287 WHERE award_id = x_award_id
288 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
289
290 lv_rowid cur_rowid%ROWTYPE;
291
292 BEGIN
293
294 OPEN cur_rowid;
295 FETCH cur_rowid INTO lv_rowid;
296 IF (cur_rowid%FOUND) THEN
297 CLOSE cur_rowid;
298 RETURN (true);
299 ELSE
300 CLOSE cur_rowid;
301 RETURN(FALSE);
302 END IF;
303
304 END get_uk2_for_validation ;
305
306
307 PROCEDURE get_fk_igf_aw_award ( x_award_id IN NUMBER ) AS
308 /*
309 || Created By : venagara
310 || Created On : 02-DEC-2000
311 || Purpose : Validates the Foreign Keys for the table.
312 || Known limitations, enhancements or remarks :
313 || Change History :
314 || Who When What
315 || (reverse chronological order - newest change first)
316 || agairola 15-Mar-2002 Added the code for the new column
317 || for the Borrower Determination
318 */
319 CURSOR cur_rowid IS
320 SELECT rowid
321 FROM igf_sl_loans_all
322 WHERE ((award_id = x_award_id));
323
324 lv_rowid cur_rowid%ROWTYPE;
325
326 BEGIN
327
328 OPEN cur_rowid;
329 FETCH cur_rowid INTO lv_rowid;
330 IF (cur_rowid%FOUND) THEN
331 CLOSE cur_rowid;
332 FND_MESSAGE.SET_NAME ('IGF', 'IGF_SL_LAR_AWD_FK');
333 IGS_GE_MSG_STACK.ADD;
334 APP_EXCEPTION.RAISE_EXCEPTION;
335 RETURN;
336 END IF;
337 CLOSE cur_rowid;
338
339 END get_fk_igf_aw_award;
340
341
342 PROCEDURE AfterRowInsertUpdateDelete1(
343 p_inserting IN BOOLEAN ,
344 p_updating IN BOOLEAN ,
345 p_deleting IN BOOLEAN
346 ) AS
347 /*-----------------------------------------------------------------
348 || Created By : Sanil Madathil
349 || Created On : 14-Oct-2004
350 || Purpose :
351 || Known limitations, enhancements or remarks :
352 || Change History :
353 || Who When What
354 || rajagupt 02-Mar-2006 FA 161 and FA 162 - Whenever the loan / loan change status goes to 'Ready to Send'
355 || all the disbursements which are in 'Sent' status should move to 'Ready to Send'
356 || FA 161 - Bug 5006587 - Also Update mode triggers loan updation, so it is necessary to include in g_v_called_from
357 || g_v_called_from IN ('IGFSL005','UPDATE_MODE') is needed so that change records are created from both the form and update mode
358 || (reverse chronological order - newest change first)
359 --------------------------------------------------------------------*/
360 -- Cursor to pick All disb change records in SENT status.
361 CURSOR cur_sent_disb_chg_records(cp_award_id igf_aw_db_chg_dtls.award_id%TYPE) IS
362 SELECT dbchgdtls.ROWID, dbchgdtls.*
363 FROM IGF_AW_DB_CHG_DTLS dbchgdtls
364 WHERE dbchgdtls.award_id = cp_award_id
365 AND dbchgdtls.disb_status = 'S';
366
367 -- bvisvana - FA 161 - Bug 5091652..Cursor to pick all the change records for the loan
368 CURSOR cur_loan_chg_records(cp_award_id igf_sl_clchsn_dtls.award_id%TYPE) IS
369 SELECT loan_chg_dtls.ROWID, loan_chg_dtls.*
370 FROM igf_sl_clchsn_dtls loan_chg_dtls
371 WHERE loan_chg_dtls.award_id = cp_award_id;
372
373 l_v_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE;
374 l_v_message_name fnd_new_messages.message_name%TYPE;
375 l_b_return_status BOOLEAN;
376 BEGIN
377 IF p_updating THEN
378 l_v_fed_fund_code := igf_sl_gen.get_fed_fund_code (p_n_award_id => new_references.award_id,
379 p_v_message_name => l_v_message_name
380 );
381 IF l_v_message_name IS NOT NULL THEN
382 fnd_message.set_name ('IGS',l_v_message_name);
383 igs_ge_msg_stack.add;
384 app_exception.raise_exception;
385 END IF;
386 -- bvisvana - FA 161 - Bug 5091643 and 5006587
387 --tsailaja - FA 163 --Bug 5337555
388 IF l_v_fed_fund_code IN ('FLS','FLU','FLP','ALT','GPLUSFL') THEN
389 IF g_v_called_from IN ('IGFSL005','UPDATE_MODE') THEN
390 IF ((new_references.loan_per_begin_date <> old_references.loan_per_begin_date) OR
391 (new_references.loan_per_end_date <> old_references.loan_per_end_date))
392 THEN
393 -- invoke the procedure to create change record in igf_sl_clchsn_dtls table
394 igf_sl_cl_create_chg.create_loan_chg_rec
395 (
396 p_new_loan_rec => new_references,
397 p_b_return_status => l_b_return_status,
398 p_v_message_name => l_v_message_name
399 );
400 -- if the above call out returns false and error message is returned,
401 -- add the message to the error stack and error message text should be displayed
402 -- in the calling form
403 IF (NOT (l_b_return_status) AND l_v_message_name IS NOT NULL )
404 THEN
405 -- substring of the out bound parameter l_v_message_name is carried
406 -- out since it can expect either IGS OR IGF message
407 fnd_message.set_name(SUBSTR(l_v_message_name,1,3),l_v_message_name);
408 igf_sl_cl_chg_prc.parse_tokens(
409 p_t_message_tokens => igf_sl_cl_chg_prc.g_message_tokens);
410
411 igs_ge_msg_stack.add;
412 app_exception.raise_exception;
413 END IF;
414 END IF;
415 END IF;
416 END IF;
417 -- tsailaja -FA 163 Bug 5337555
418 -- FA 161 and FA 162 Changes
419 -- FA 161 Bug 5006587 - Included UPDATE_MODE in g_v_called_from
420 IF ((l_v_fed_fund_code IN ('DLS','DLU','DLP') AND g_v_called_from IN ('IGFSL005')) OR
421 (l_v_fed_fund_code IN ('FLS','FLU','FLP','ALT','GPLUSFL') AND g_v_called_from IN ('IGFSL005','UPDATE_MODE')))
422 THEN
423 -- If either loan status or loan change status CHANGED to "Ready to Send"
424 -- then update all disbursement change records in "Sent" status to "Ready to Send"
425 IF (new_references.loan_status = 'G' AND old_references.loan_status <> 'G') OR
426 (NVL(new_references.loan_chg_status, '*') = 'G' AND NVL(old_references.loan_chg_status,'*') <> 'G')
427 THEN
428 -- update disbursement change records(which are in "Sent" ONLY) status to "Ready to Send"
429 FOR rec IN cur_sent_disb_chg_records(new_references.award_id) LOOP
430 igf_aw_db_chg_dtls_pkg.update_row (
431 x_rowid => rec.ROWID,
432 x_award_id => rec.award_id,
433 x_disb_num => rec.disb_num,
434 x_disb_seq_num => rec.disb_seq_num,
435 x_disb_accepted_amt => rec.disb_accepted_amt,
436 x_orig_fee_amt => rec.orig_fee_amt,
437 x_disb_net_amt => rec.disb_net_amt,
438 x_disb_date => rec.disb_date,
439 x_disb_activity => rec.disb_activity,
440 x_disb_status => 'G',
441 x_disb_status_date => TRUNC(SYSDATE),
442 x_disb_rel_flag => rec.disb_rel_flag,
443 x_first_disb_flag => rec.first_disb_flag,
444 x_interest_rebate_amt => rec.interest_rebate_amt,
445 x_disb_conf_flag => rec.disb_conf_flag,
446 x_pymnt_prd_start_date => rec.pymnt_prd_start_date,
447 x_note_message => rec.note_message,
448 x_batch_id_txt => rec.batch_id_txt,
449 x_ack_date => rec.ack_date,
450 x_booking_id_txt => rec.booking_id_txt,
451 x_booking_date => rec.booking_date,
452 x_mode => 'R'
453 );
454 END LOOP;
455 -- tsailaja -FA 163 Bug 5337555
456 -- bvisvana - Bug 5091652
457 -- If either loan status or loan change status CHANGED to "Ready to Send"
458 -- then update all change change records in "Sent" status to "Ready to Send".Change records apply only for FFELP
459 IF (l_v_fed_fund_code IN ('FLS','FLU','FLP','ALT','GPLUSFL') AND g_v_called_from IN ('IGFSL005','UPDATE_MODE')) THEN
460 FOR rec_c_igf_sl_clchsn_dtls IN cur_loan_chg_records(new_references.award_id) LOOP
461 igf_sl_clchsn_dtls_pkg.update_row (
462 x_rowid => rec_c_igf_sl_clchsn_dtls.ROWID ,
463 x_clchgsnd_id => rec_c_igf_sl_clchsn_dtls.clchgsnd_id ,
464 x_award_id => rec_c_igf_sl_clchsn_dtls.award_id ,
465 x_loan_number_txt => rec_c_igf_sl_clchsn_dtls.loan_number_txt ,
466 x_cl_version_code => rec_c_igf_sl_clchsn_dtls.cl_version_code ,
467 x_change_field_code => rec_c_igf_sl_clchsn_dtls.change_field_code ,
468 x_change_record_type_txt => rec_c_igf_sl_clchsn_dtls.change_record_type_txt ,
469 x_change_code_txt => rec_c_igf_sl_clchsn_dtls.change_code_txt ,
470 x_status_code => 'R' ,
471 x_status_date => rec_c_igf_sl_clchsn_dtls.status_date ,
472 x_response_status_code => rec_c_igf_sl_clchsn_dtls.response_status_code ,
473 x_old_value_txt => rec_c_igf_sl_clchsn_dtls.old_value_txt ,
474 x_new_value_txt => rec_c_igf_sl_clchsn_dtls.new_value_txt ,
475 x_old_date => rec_c_igf_sl_clchsn_dtls.old_date ,
476 x_new_date => rec_c_igf_sl_clchsn_dtls.new_date ,
477 x_old_amt => rec_c_igf_sl_clchsn_dtls.old_amt ,
478 x_new_amt => rec_c_igf_sl_clchsn_dtls.new_amt ,
479 x_disbursement_number => rec_c_igf_sl_clchsn_dtls.disbursement_number ,
480 x_disbursement_date => rec_c_igf_sl_clchsn_dtls.disbursement_date ,
481 x_change_issue_code => rec_c_igf_sl_clchsn_dtls.change_issue_code ,
482 x_disbursement_cancel_date => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_date ,
483 x_disbursement_cancel_amt => rec_c_igf_sl_clchsn_dtls.disbursement_cancel_amt ,
484 x_disbursement_revised_amt => rec_c_igf_sl_clchsn_dtls.disbursement_revised_amt ,
485 x_disbursement_revised_date => rec_c_igf_sl_clchsn_dtls.disbursement_revised_date ,
486 x_disbursement_reissue_code => rec_c_igf_sl_clchsn_dtls.disbursement_reissue_code ,
487 x_disbursement_reinst_code => rec_c_igf_sl_clchsn_dtls.disbursement_reinst_code ,
488 x_disbursement_return_amt => rec_c_igf_sl_clchsn_dtls.disbursement_return_amt ,
489 x_disbursement_return_date => rec_c_igf_sl_clchsn_dtls.disbursement_return_date ,
490 x_disbursement_return_code => rec_c_igf_sl_clchsn_dtls.disbursement_return_code ,
491 x_post_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_amt ,
492 x_post_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_date ,
493 x_post_with_disb_return_code => rec_c_igf_sl_clchsn_dtls.post_with_disb_return_code ,
494 x_prev_with_disb_return_amt => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_amt ,
495 x_prev_with_disb_return_date => rec_c_igf_sl_clchsn_dtls.prev_with_disb_return_date ,
496 x_school_use_txt => rec_c_igf_sl_clchsn_dtls.school_use_txt ,
497 x_lender_use_txt => rec_c_igf_sl_clchsn_dtls.lender_use_txt ,
498 x_guarantor_use_txt => rec_c_igf_sl_clchsn_dtls.guarantor_use_txt ,
499 x_validation_edit_txt => NULL ,
500 x_send_record_txt => rec_c_igf_sl_clchsn_dtls.send_record_txt ,
501 x_mode => 'R'
502 );
503 END LOOP; -- end of "FOR rec_c_igf_sl_clchsn_dtls" ....
504 END IF; -- end of "IF (l_v_fed_fund_code IN" ....
505 END IF;
506 END IF; -- IF DLP OR DLU or DLS OR FLS or FLP ......
507 END IF; -- IF p_updating
508 END AfterRowInsertUpdateDelete1;
509
510 PROCEDURE before_dml (
511 p_action IN VARCHAR2 ,
512 x_rowid IN VARCHAR2 ,
513 x_loan_id IN NUMBER ,
514 x_award_id IN NUMBER ,
515 x_seq_num IN NUMBER ,
516 x_loan_number IN VARCHAR2 ,
517 x_loan_per_begin_date IN DATE ,
518 x_loan_per_end_date IN DATE ,
519 x_loan_status IN VARCHAR2 ,
520 x_loan_status_date IN DATE ,
521 x_loan_chg_status IN VARCHAR2 ,
522 x_loan_chg_status_date IN DATE ,
523 x_active IN VARCHAR2 ,
524 x_active_date IN DATE ,
525 x_borw_detrm_code IN VARCHAR2 ,
526 x_legacy_record_flag IN VARCHAR2 ,
527 x_creation_date IN DATE ,
528 x_created_by IN NUMBER ,
529 x_last_update_date IN DATE ,
530 x_last_updated_by IN NUMBER ,
531 x_last_update_login IN NUMBER ,
532 x_external_loan_id_txt IN VARCHAR2
533 ) AS
534 /*
535 || Created By : venagara
536 || Created On : 02-DEC-2000
537 || Purpose : Initialises the columns, Checks Constraints, Calls the
538 || Trigger Handlers for the table, before any DML operation.
539 || Known limitations, enhancements or remarks :
540 || Change History :
541 || Who When What
542 || (reverse chronological order - newest change first)
543 || smadathi 14-oct-2004 Bug 3416936.Added new column as per TD.
544 || agairola 15-Mar-2002 Added the code for the new column
545 || for the Borrower Determination
546 */
547 BEGIN
548
549 set_column_values (
550 p_action,
551 x_rowid,
552 x_loan_id,
553 x_award_id,
554 x_seq_num,
555 x_loan_number,
556 x_loan_per_begin_date,
557 x_loan_per_end_date,
558 x_loan_status,
559 x_loan_status_date,
560 x_loan_chg_status,
561 x_loan_chg_status_date,
562 x_active,
563 x_active_date,
564 x_borw_detrm_code,
565 x_legacy_record_flag,
566 x_creation_date,
567 x_created_by,
568 x_last_update_date,
569 x_last_updated_by,
570 x_last_update_login,
571 x_external_loan_id_txt
572 );
573
574 IF (p_action = 'INSERT') THEN
575 -- Call all the procedures related to Before Insert.
576 IF ( get_pk_for_validation( new_references.loan_id ) ) THEN
577 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
578 IGS_GE_MSG_STACK.ADD;
579 APP_EXCEPTION.RAISE_EXCEPTION;
580 END IF;
581 check_uniqueness;
582 check_parent_existance;
583 ELSIF (p_action = 'UPDATE') THEN
584 -- Call all the procedures related to Before Update.
585 check_uniqueness;
586 check_parent_existance;
587 ELSIF (p_action = 'DELETE') THEN
588 -- Call all the procedures related to Before Delete.
589 check_child_existance;
590 ELSIF (p_action = 'VALIDATE_INSERT') THEN
591 -- Call all the procedures related to Before Insert.
592 IF ( get_pk_for_validation ( new_references.loan_id ) ) THEN
593 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
594 IGS_GE_MSG_STACK.ADD;
595 APP_EXCEPTION.RAISE_EXCEPTION;
596 END IF;
597 check_uniqueness;
598 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
599 check_uniqueness;
600 ELSIF (p_action = 'VALIDATE_DELETE') THEN
601 check_child_existance;
602 END IF;
603
604 END before_dml;
605
606 PROCEDURE after_dml (
607 p_action IN VARCHAR2,
608 x_rowid IN VARCHAR2
609 ) AS
610 /*-----------------------------------------------------------------
611 || Created By : Sanil Madathil
612 || Created On : 14 October 2004
613 || Purpose : Invoke the proceduers related to after update
614 || Known limitations, enhancements or remarks :
615 || Change History :
616 || Who When What
617 || (reverse chronological order - newest change first)
618 --------------------------------------------------------------------*/
619 BEGIN
620 l_rowid := x_rowid;
621 l_rowid := NULL;
622 IF (p_action = 'UPDATE') THEN
623 -- Call all the procedures related to After Update.
624 AfterRowInsertUpdateDelete1
625 (
626 p_inserting => FALSE,
627 p_updating => TRUE ,
628 p_deleting => FALSE
629 );
630 END IF;
631 END after_dml;
632
633 PROCEDURE insert_row (
634 x_rowid IN OUT NOCOPY VARCHAR2,
635 x_loan_id IN OUT NOCOPY NUMBER,
636 x_award_id IN NUMBER,
637 x_seq_num IN NUMBER,
638 x_loan_number IN VARCHAR2,
639 x_loan_per_begin_date IN DATE,
640 x_loan_per_end_date IN DATE,
641 x_loan_status IN VARCHAR2,
642 x_loan_status_date IN DATE,
643 x_loan_chg_status IN VARCHAR2,
644 x_loan_chg_status_date IN DATE,
645 x_active IN VARCHAR2,
646 x_active_date IN DATE,
647 x_borw_detrm_code IN VARCHAR2 ,
648 x_mode IN VARCHAR2 ,
649 x_legacy_record_flag IN VARCHAR2 ,
650 x_external_loan_id_txt IN VARCHAR2
651 ) AS
652 /*
653 || Created By : venagara
654 || Created On : 02-DEC-2000
655 || Purpose : Handles the INSERT DML logic for the table.
656 || Known limitations, enhancements or remarks :
657 || Change History :
658 || Who When What
659 || (reverse chronological order - newest change first)
660 || smadathi 14-oct-2004 Bug 3416936.Added new column as per TD.
661 || agairola 15-Mar-2002 Added the code for the new column
662 || for the Borrower Determination
663 */
664 CURSOR c IS
665 SELECT rowid
666 FROM igf_sl_loans_all
667 WHERE loan_id = x_loan_id;
668
669 x_last_update_date DATE;
670 x_last_updated_by NUMBER;
671 x_last_update_login NUMBER;
672 x_request_id NUMBER;
673 x_program_id NUMBER;
674 x_program_application_id NUMBER;
675 x_program_update_date DATE;
676 l_org_id igf_sl_loans_all.org_id%TYPE := igf_aw_gen.get_org_id;
677
678 BEGIN
679
680 x_last_update_date := SYSDATE;
681 IF (x_mode = 'I') THEN
682 x_last_updated_by := 1;
683 x_last_update_login := 0;
684 ELSIF (x_mode = 'R') THEN
685 x_last_updated_by := fnd_global.user_id;
686 IF (x_last_updated_by IS NULL) THEN
687 x_last_updated_by := -1;
688 END IF;
689 x_last_update_login := fnd_global.login_id;
690 IF (x_last_update_login IS NULL) THEN
691 x_last_update_login := -1;
692 END IF;
693 x_request_id := fnd_global.conc_request_id;
694 x_program_id := fnd_global.conc_program_id;
695 x_program_application_id := fnd_global.prog_appl_id;
696
697 IF (x_request_id = -1) THEN
698 x_request_id := NULL;
699 x_program_id := NULL;
700 x_program_application_id := NULL;
701 x_program_update_date := NULL;
702 ELSE
703 x_program_update_date := SYSDATE;
704 END IF;
705 ELSE
706 FND_MESSAGE.SET_NAME ('FND', 'SYSTEM-INVALID ARGS');
707 IGS_GE_MSG_STACK.ADD;
708 APP_EXCEPTION.RAISE_EXCEPTION;
709 END IF;
710
711 SELECT igf_sl_loans_s.nextval INTO x_loan_id FROM DUAL;
712
713 before_dml(
714 p_action => 'INSERT',
715 x_rowid => x_rowid,
716 x_loan_id => x_loan_id,
717 x_award_id => x_award_id,
718 x_seq_num => x_seq_num,
719 x_loan_number => x_loan_number,
720 x_loan_per_begin_date => x_loan_per_begin_date,
721 x_loan_per_end_date => x_loan_per_end_date,
722 x_loan_status => x_loan_status,
723 x_loan_status_date => x_loan_status_date,
724 x_loan_chg_status => x_loan_chg_status,
725 x_loan_chg_status_date => x_loan_chg_status_date,
726 x_active => x_active,
727 x_active_date => x_active_date,
728 x_borw_detrm_code => x_borw_detrm_code,
729 x_legacy_record_flag => x_legacy_record_flag,
730 x_creation_date => x_last_update_date,
731 x_created_by => x_last_updated_by,
732 x_last_update_date => x_last_update_date,
733 x_last_updated_by => x_last_updated_by,
734 x_last_update_login => x_last_update_login ,
735 x_external_loan_id_txt => x_external_loan_id_txt
736 );
737
738 INSERT INTO igf_sl_loans_all(
739 loan_id,
740 award_id,
741 seq_num,
742 loan_number,
743 loan_per_begin_date,
744 loan_per_end_date,
745 loan_status,
746 loan_status_date,
747 loan_chg_status,
748 loan_chg_status_date,
749 active,
750 active_date,
751 borw_detrm_code,
752 legacy_record_flag,
753 creation_date,
754 created_by,
755 last_update_date,
756 last_updated_by,
757 last_update_login,
758 request_id,
759 program_id,
760 program_application_id,
761 program_update_date ,
762 org_id,
763 external_loan_id_txt
764 ) VALUES (
765 new_references.loan_id,
766 new_references.award_id,
767 new_references.seq_num,
768 new_references.loan_number,
769 new_references.loan_per_begin_date,
770 new_references.loan_per_end_date,
771 new_references.loan_status,
772 new_references.loan_status_date,
773 new_references.loan_chg_status,
774 new_references.loan_chg_status_date,
775 new_references.active,
776 new_references.active_date,
777 new_references.borw_detrm_code,
778 new_references.legacy_record_flag,
779 x_last_update_date,
780 x_last_updated_by,
781 x_last_update_date,
782 x_last_updated_by,
783 x_last_update_login ,
784 x_request_id,
785 x_program_id,
786 x_program_application_id,
787 x_program_update_date,
788 l_org_id ,
789 new_references.external_loan_id_txt
790 );
791
792 OPEN c;
793 FETCH c INTO x_rowid;
794 IF (c%NOTFOUND) THEN
795 CLOSE c;
796 RAISE NO_DATA_FOUND;
797 END IF;
798 CLOSE c;
799
800 END insert_row;
801
802
803 PROCEDURE lock_row (
804 x_rowid IN VARCHAR2,
805 x_loan_id IN NUMBER,
806 x_award_id IN NUMBER,
807 x_seq_num IN NUMBER,
808 x_loan_number IN VARCHAR2,
809 x_loan_per_begin_date IN DATE,
810 x_loan_per_end_date IN DATE,
811 x_loan_status IN VARCHAR2,
812 x_loan_status_date IN DATE,
813 x_loan_chg_status IN VARCHAR2,
814 x_loan_chg_status_date IN DATE,
815 x_active IN VARCHAR2,
816 x_active_date IN DATE,
817 x_borw_detrm_code IN VARCHAR2,
818 x_legacy_record_flag IN VARCHAR2,
819 x_external_loan_id_txt IN VARCHAR2
820 ) AS
821 /*
822 || Created By : venagara
823 || Created On : 02-DEC-2000
824 || Purpose : Handles the LOCK mechanism for the table.
825 || Known limitations, enhancements or remarks :
826 || Change History :
827 || Who When What
828 || (reverse chronological order - newest change first)
829 || mnade 21-Jan-2005 Bug 4124891 Added TRUNC in date comparison.
830 || smadathi 14-oct-2004 Bug 3416936.Added new column as per TD.
831 || agairola 15-Mar-2002 Added the code for the new column
832 || for the Borrower Determination
833 */
834 CURSOR c1 IS
835 SELECT award_id,
836 seq_num,
837 loan_number,
838 loan_per_begin_date,
839 loan_per_end_date,
840 loan_status,
841 loan_status_date,
842 loan_chg_status,
843 loan_chg_status_date,
844 active,
845 active_date,
846 borw_detrm_code,
847 legacy_record_flag,
848 org_id,
849 external_loan_id_txt
850 FROM igf_sl_loans_all
851 WHERE rowid = x_rowid
852 FOR UPDATE NOWAIT;
853
854 tlinfo c1%ROWTYPE;
855
856 BEGIN
857
858 OPEN c1;
859 FETCH c1 INTO tlinfo;
860 IF (c1%notfound) THEN
861 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
862 IGS_GE_MSG_STACK.ADD;
863 CLOSE c1;
864 APP_EXCEPTION.RAISE_EXCEPTION;
865 RETURN;
866 END IF;
867 CLOSE c1;
868
869 IF (
870 (tlinfo.award_id = x_award_id)
871 AND ((tlinfo.seq_num = x_seq_num) OR ((tlinfo.seq_num IS NULL) AND (X_seq_num IS NULL)))
872 AND ((tlinfo.loan_number = x_loan_number) OR ((tlinfo.loan_number IS NULL) AND (X_loan_number IS NULL)))
873 AND (TRUNC(tlinfo.loan_per_begin_date) = TRUNC(x_loan_per_begin_date))
874 AND (TRUNC(tlinfo.loan_per_end_date) = TRUNC(x_loan_per_end_date))
875 AND (tlinfo.loan_status = x_loan_status)
876 AND ((TRUNC(tlinfo.loan_status_date) = TRUNC(x_loan_status_date)) OR ((tlinfo.loan_status_date IS NULL) AND (X_loan_status_date IS NULL)))
877 AND ((tlinfo.loan_chg_status = x_loan_chg_status) OR ((tlinfo.loan_chg_status IS NULL) AND (X_loan_chg_status IS NULL)))
878 AND ((TRUNC(tlinfo.loan_chg_status_date) = TRUNC(x_loan_chg_status_date)) OR ((tlinfo.loan_chg_status_date IS NULL) AND (X_loan_chg_status_date IS NULL)))
879 AND (tlinfo.active = x_active)
880 AND (TRUNC(tlinfo.active_date) = TRUNC(x_active_date))
881 AND ((tlinfo.borw_detrm_code = x_borw_detrm_code) OR
882 ((tlinfo.borw_detrm_code IS NULL) AND (x_borw_detrm_code IS NULL)))
883 AND ((tlinfo.legacy_record_flag = x_legacy_record_flag) OR
884 ((tlinfo.legacy_record_flag IS NULL) AND (x_legacy_record_flag IS NULL)))
885 AND ((tlinfo.external_loan_id_txt = x_external_loan_id_txt) OR ((tlinfo.external_loan_id_txt IS NULL) AND (x_external_loan_id_txt IS NULL)))
886 ) THEN
887 NULL;
888 ELSE
889 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
890 IGS_GE_MSG_STACK.ADD;
891 APP_EXCEPTION.RAISE_EXCEPTION;
892 END IF;
893
894 RETURN;
895
896 END lock_row;
897
898
899 PROCEDURE update_row (
900 x_rowid IN VARCHAR2,
901 x_loan_id IN NUMBER,
902 x_award_id IN NUMBER,
903 x_seq_num IN NUMBER,
904 x_loan_number IN VARCHAR2,
905 x_loan_per_begin_date IN DATE,
906 x_loan_per_end_date IN DATE,
907 x_loan_status IN VARCHAR2,
908 x_loan_status_date IN DATE,
909 x_loan_chg_status IN VARCHAR2,
910 x_loan_chg_status_date IN DATE,
911 x_active IN VARCHAR2,
912 x_active_date IN DATE,
913 x_borw_detrm_code IN VARCHAR2 ,
914 x_mode IN VARCHAR2 ,
915 x_legacy_record_flag IN VARCHAR2 ,
916 x_external_loan_id_txt IN VARCHAR2 ,
917 x_called_from IN VARCHAR2
918 ) AS
919 /*
920 || Created By : venagara
921 || Created On : 02-DEC-2000
922 || Purpose : Handles the UPDATE DML logic for the table.
923 || Known limitations, enhancements or remarks :
924 || Change History :
925 || Who When What
926 || (reverse chronological order - newest change first)
927 || smadathi 14-oct-2004 Bug 3416936.Added new column as per TD.
928 || agairola 15-Mar-2002 Added the code for the new column
929 || for the Borrower Determination
930 */
931 x_last_update_date DATE ;
932 x_last_updated_by NUMBER;
933 x_last_update_login NUMBER;
934 x_request_id NUMBER;
935 x_program_id NUMBER;
936 x_program_application_id NUMBER;
937 x_program_update_date DATE;
938
939 BEGIN
940
941 x_last_update_date := SYSDATE;
942 IF (X_MODE = 'I') THEN
943 x_last_updated_by := 1;
944 x_last_update_login := 0;
945 ELSIF (x_mode = 'R') THEN
946 x_last_updated_by := fnd_global.user_id;
947 IF x_last_updated_by IS NULL THEN
948 x_last_updated_by := -1;
949 END IF;
950 x_last_update_login := fnd_global.login_id;
951 IF (x_last_update_login IS NULL) THEN
952 x_last_update_login := -1;
953 END IF;
954 ELSE
955 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
956 IGS_GE_MSG_STACK.ADD;
957 APP_EXCEPTION.RAISE_EXCEPTION;
958 END IF;
959
960 before_dml(
961 p_action => 'UPDATE',
962 x_rowid => x_rowid,
963 x_loan_id => x_loan_id,
964 x_award_id => x_award_id,
965 x_seq_num => x_seq_num,
966 x_loan_number => x_loan_number,
967 x_loan_per_begin_date => x_loan_per_begin_date,
968 x_loan_per_end_date => x_loan_per_end_date,
969 x_loan_status => x_loan_status,
970 x_loan_status_date => x_loan_status_date,
971 x_loan_chg_status => x_loan_chg_status,
972 x_loan_chg_status_date => x_loan_chg_status_date,
973 x_active => x_active,
974 x_active_date => x_active_date,
975 x_borw_detrm_code => x_borw_detrm_code,
976 x_legacy_record_flag => x_legacy_record_flag,
977 x_creation_date => x_last_update_date,
978 x_created_by => x_last_updated_by,
979 x_last_update_date => x_last_update_date,
980 x_last_updated_by => x_last_updated_by,
981 x_last_update_login => x_last_update_login ,
982 x_external_loan_id_txt => x_external_loan_id_txt
983 );
984
985 IF (x_mode = 'R') THEN
986 x_request_id := fnd_global.conc_request_id;
987 x_program_id := fnd_global.conc_program_id;
988 x_program_application_id := fnd_global.prog_appl_id;
989 IF (x_request_id = -1) THEN
990 x_request_id := old_references.request_id;
991 x_program_id := old_references.program_id;
992 x_program_application_id := old_references.program_application_id;
993 x_program_update_date := old_references.program_update_date;
994 ELSE
995 x_program_update_date := SYSDATE;
996 END IF;
997 END IF;
998
999 UPDATE igf_sl_loans_all
1000 SET
1001 award_id = new_references.award_id,
1002 seq_num = new_references.seq_num,
1003 loan_number = new_references.loan_number,
1004 loan_per_begin_date = new_references.loan_per_begin_date,
1005 loan_per_end_date = new_references.loan_per_end_date,
1006 loan_status = new_references.loan_status,
1007 loan_status_date = new_references.loan_status_date,
1008 loan_chg_status = new_references.loan_chg_status,
1009 loan_chg_status_date = new_references.loan_chg_status_date,
1010 active = new_references.active,
1011 active_date = new_references.active_date,
1012 borw_detrm_code = new_references.borw_detrm_code,
1013 legacy_record_flag = new_references.legacy_record_flag,
1014 last_update_date = x_last_update_date,
1015 last_updated_by = x_last_updated_by,
1016 last_update_login = x_last_update_login ,
1017 request_id = x_request_id,
1018 program_id = x_program_id,
1019 program_application_id = x_program_application_id,
1020 program_update_date = x_program_update_date,
1021 external_loan_id_txt = new_references.external_loan_id_txt
1022 WHERE rowid = x_rowid;
1023
1024 IF (SQL%NOTFOUND) THEN
1025 RAISE NO_DATA_FOUND;
1026 END IF;
1027 g_v_called_from := x_called_from;
1028 after_dml(
1029 p_action =>'UPDATE',
1030 x_rowid => x_rowid
1031 );
1032
1033 END update_row;
1034
1035
1036 PROCEDURE add_row (
1037 x_rowid IN OUT NOCOPY VARCHAR2,
1038 x_loan_id IN OUT NOCOPY NUMBER,
1039 x_award_id IN NUMBER,
1040 x_seq_num IN NUMBER,
1041 x_loan_number IN VARCHAR2,
1042 x_loan_per_begin_date IN DATE,
1043 x_loan_per_end_date IN DATE,
1044 x_loan_status IN VARCHAR2,
1045 x_loan_status_date IN DATE,
1046 x_loan_chg_status IN VARCHAR2,
1047 x_loan_chg_status_date IN DATE,
1048 x_active IN VARCHAR2,
1049 x_active_date IN DATE,
1050 x_borw_detrm_code IN VARCHAR2 ,
1051 x_mode IN VARCHAR2 ,
1052 x_legacy_record_flag IN VARCHAR2,
1053 x_external_loan_id_txt IN VARCHAR2
1054 ) AS
1055 /*
1056 || Created By : venagara
1057 || Created On : 02-DEC-2000
1058 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1059 || Known limitations, enhancements or remarks :
1060 || Change History :
1061 || Who When What
1062 || (reverse chronological order - newest change first)
1063 || smadathi 14-oct-2004 Bug 3416936.Added new column as per TD.
1064 || agairola 15-Mar-2002 Added the code for the new column
1065 || for the Borrower Determination
1066 */
1067 CURSOR c1 IS
1068 SELECT rowid
1069 FROM igf_sl_loans_all
1070 WHERE loan_id = x_loan_id;
1071
1072 BEGIN
1073
1074 OPEN c1;
1075 FETCH c1 INTO x_rowid;
1076 IF (c1%NOTFOUND) THEN
1077 CLOSE c1;
1078
1079 insert_row (
1080 x_rowid,
1081 x_loan_id,
1082 x_award_id,
1083 x_seq_num,
1084 x_loan_number,
1085 x_loan_per_begin_date,
1086 x_loan_per_end_date,
1087 x_loan_status,
1088 x_loan_status_date,
1089 x_loan_chg_status,
1090 x_loan_chg_status_date,
1091 x_active,
1092 x_active_date,
1093 x_borw_detrm_code,
1094 x_mode ,
1095 x_legacy_record_flag,
1096 x_external_loan_id_txt
1097 );
1098 RETURN;
1099 END IF;
1100 CLOSE c1;
1101
1102 update_row (
1103 x_rowid,
1104 x_loan_id,
1105 x_award_id,
1106 x_seq_num,
1107 x_loan_number,
1108 x_loan_per_begin_date,
1109 x_loan_per_end_date,
1110 x_loan_status,
1111 x_loan_status_date,
1112 x_loan_chg_status,
1113 x_loan_chg_status_date,
1114 x_active,
1115 x_active_date,
1116 x_borw_detrm_code,
1117 x_mode ,
1118 x_legacy_record_flag,
1119 x_external_loan_id_txt
1120 );
1121
1122 END add_row;
1123
1124
1125 PROCEDURE delete_row (
1126 x_rowid IN VARCHAR2
1127 ) AS
1128 /*
1129 || Created By : venagara
1130 || Created On : 02-DEC-2000
1131 || Purpose : Handles the DELETE DML logic for the table.
1132 || Known limitations, enhancements or remarks :
1133 || Change History :
1134 || Who When What
1135 || (reverse chronological order - newest change first)
1136 */
1137 BEGIN
1138
1139 before_dml ( p_action => 'DELETE', x_rowid => x_rowid );
1140
1141 DELETE FROM igf_sl_loans_all
1142 WHERE rowid = x_rowid;
1143
1144 IF (SQL%NOTFOUND) THEN
1145 RAISE NO_DATA_FOUND;
1146 END IF;
1147
1148 END delete_row;
1149
1150
1151 END igf_sl_loans_pkg;
[Home] [Help]