[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_CL_SETUP_PKG
Source
1 PACKAGE BODY igf_sl_cl_setup_pkg AS
2 /* $Header: IGFLI08B.pls 120.0 2005/06/01 14:00:57 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sl_cl_setup_all%ROWTYPE;
6 new_references igf_sl_cl_setup_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_clset_id IN NUMBER DEFAULT NULL,
12 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
13 x_ci_sequence_number IN NUMBER DEFAULT NULL,
14 x_borw_interest_ind IN VARCHAR2 DEFAULT NULL,
15 x_sch_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
16 x_lender_id IN VARCHAR2 DEFAULT NULL,
17 x_duns_lender_id IN VARCHAR2 DEFAULT NULL,
18 x_lend_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
19 x_guarantor_id IN VARCHAR2 DEFAULT NULL,
20 x_duns_guarnt_id IN VARCHAR2 DEFAULT NULL,
21 x_recipient_id IN VARCHAR2 DEFAULT NULL,
22 x_recipient_type IN VARCHAR2 DEFAULT NULL,
23 x_duns_recip_id IN VARCHAR2 DEFAULT NULL,
24 x_recip_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
25 x_est_orig_fee_perct IN NUMBER DEFAULT NULL,
26 x_est_guarnt_fee_perct IN NUMBER DEFAULT NULL,
27 x_hold_rel_ind IN VARCHAR2 DEFAULT NULL,
28 x_req_serial_loan_code IN VARCHAR2 DEFAULT NULL,
29 x_loan_award_method IN VARCHAR2 DEFAULT NULL,
30 x_prc_type_code IN VARCHAR2 DEFAULT NULL,
31 x_pnote_delivery_code IN VARCHAR2 DEFAULT NULL,
32 x_media_type IN VARCHAR2 DEFAULT NULL,
33 x_eft_authorization IN VARCHAR2 DEFAULT NULL,
34 x_auto_late_disb_ind IN VARCHAR2 DEFAULT NULL,
35 x_cl_version IN VARCHAR2 DEFAULT NULL,
36 x_creation_date IN DATE DEFAULT NULL,
37 x_created_by IN NUMBER DEFAULT NULL,
38 x_last_update_date IN DATE DEFAULT NULL,
39 x_last_updated_by IN NUMBER DEFAULT NULL,
40 x_last_update_login IN NUMBER DEFAULT NULL,
41 x_est_alt_orig_fee_perct IN NUMBER DEFAULT NULL,
42 x_est_alt_guarnt_fee_perct IN NUMBER DEFAULT NULL,
43 x_relationship_cd IN VARCHAR2 DEFAULT NULL,
44 x_default_flag IN VARCHAR2 DEFAULT NULL,
45 x_party_id IN NUMBER DEFAULT NULL,
46 x_plus_processing_type_code IN VARCHAR2 DEFAULT NULL,
47 x_fund_return_method_code IN VARCHAR2 DEFAULT NULL
48 ) AS
49 /*
50 || Created By : venagara
51 || Created On : 02-DEC-2000
52 || Purpose : Initialises the Old and New references for the columns of the table.
53 || Known limitations, enhancements or remarks :
54 || Change History :
55 || Who When What
56 || veramach 5-SEP-2003 Added x_relationship_code_txt,x_default_flag,x_party_id for the newly added columns
57 || (reverse chronological order - newest change first)
58 */
59
60 CURSOR cur_old_ref_values IS
61 SELECT *
62 FROM IGF_SL_CL_SETUP_ALL
63 WHERE rowid = x_rowid;
64
65 BEGIN
66
67 l_rowid := x_rowid;
68
69 -- Code for setting the Old and New Reference Values.
70 -- Populate Old Values.
71 OPEN cur_old_ref_values;
72 FETCH cur_old_ref_values INTO old_references;
73 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
74 CLOSE cur_old_ref_values;
75 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
76 igs_ge_msg_stack.add;
77 app_exception.raise_exception;
78 RETURN;
79 END IF;
80 CLOSE cur_old_ref_values;
81
82 -- Populate New Values.
83 new_references.clset_id := x_clset_id;
84 new_references.ci_cal_type := x_ci_cal_type;
85 new_references.ci_sequence_number := x_ci_sequence_number;
86 new_references.borw_interest_ind := x_borw_interest_ind;
87 new_references.est_orig_fee_perct := x_est_orig_fee_perct;
88 new_references.est_guarnt_fee_perct := x_est_guarnt_fee_perct;
89 new_references.hold_rel_ind := x_hold_rel_ind;
90 new_references.req_serial_loan_code := x_req_serial_loan_code;
91 new_references.prc_type_code := x_prc_type_code;
92 new_references.pnote_delivery_code := x_pnote_delivery_code;
93 new_references.eft_authorization := x_eft_authorization;
94 new_references.auto_late_disb_ind := x_auto_late_disb_ind;
95
96 IF (p_action = 'UPDATE') THEN
97 new_references.creation_date := old_references.creation_date;
98 new_references.created_by := old_references.created_by;
99 ELSE
100 new_references.creation_date := x_creation_date;
101 new_references.created_by := x_created_by;
102 END IF;
103
104 new_references.last_update_date := x_last_update_date;
105 new_references.last_updated_by := x_last_updated_by;
106 new_references.last_update_login := x_last_update_login;
107
108 -- brajendr: newly added bcoz of datamodel changes
109 new_references.est_alt_orig_fee_perct := x_est_alt_orig_fee_perct;
110 new_references.est_alt_guarnt_fee_perct := x_est_alt_guarnt_fee_perct;
111
112
113 new_references.relationship_cd := x_relationship_cd ;
114 new_references.default_flag := x_default_flag;
115 new_references.party_id := x_party_id;
116 new_references.cl_version :=x_cl_version;
117 new_references.plus_processing_type_code := x_plus_processing_type_code;
118 new_references.fund_return_method_code := x_fund_return_method_code;
119
120 END set_column_values;
121
122
123 PROCEDURE check_uniqueness AS
124 /*
125 || Created By : venagara
126 || Created On : 02-DEC-2000
127 || Purpose : Handles the Unique Constraint logic defined for the columns.
128 || Known limitations, enhancements or remarks :
129 || Change History :
130 || Who When What
131 || (reverse chronological order - newest change first)
132 */
133 BEGIN
134
135 IF ( get_uk_for_validation (
136 new_references.ci_cal_type,
137 new_references.ci_sequence_number,
138 new_references.relationship_cd ,
139 new_references.party_id
140 )
141 ) THEN
142 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
143 igs_ge_msg_stack.add;
144 app_exception.raise_exception;
145 END IF;
146
147 END check_uniqueness;
148
149
150 PROCEDURE check_parent_existance AS
151 /*
152 || Created By : venagara
153 || Created On : 02-DEC-2000
154 || Purpose : Checks for the existance of Parent records.
155 || Known limitations, enhancements or remarks :
156 || Change History :
157 || Who When What
158 ||veramach 5-SEP-2003 Added call to igf_sl_cl_recipient_pkg.get_uk1_for_validation
159 || (reverse chronological order - newest change first)
160 */
161 BEGIN
162 IF (((old_references.ci_cal_type = new_references.ci_cal_type) AND
163 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
164 ((new_references.ci_cal_type IS NULL) OR
165 (new_references.ci_sequence_number IS NULL))) THEN
166 NULL;
167 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
168 new_references.ci_cal_type,
169 new_references.ci_sequence_number
170 ) THEN
171 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
172 igs_ge_msg_stack.add;
173 app_exception.raise_exception;
174 END IF;
175 IF (((old_references.relationship_cd = new_references.relationship_cd )) OR
176 ((new_references.relationship_cd IS NULL))) THEN
177 NULL;
178 ELSIF NOT igf_sl_cl_recipient_pkg.get_uk1_for_validation (
179 new_references.relationship_cd
180 ) THEN
181 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
182 igs_ge_msg_stack.add;
183 app_exception.raise_exception;
184 END IF;
185
186 END check_parent_existance;
187
188
189 FUNCTION get_pk_for_validation (
190 x_clset_id IN NUMBER
191 ) RETURN BOOLEAN AS
192 /*
193 || Created By : venagara
194 || Created On : 02-DEC-2000
195 || Purpose : Validates the Primary Key of the table.
196 || Known limitations, enhancements or remarks :
197 || Change History :
198 || Who When What
199 || (reverse chronological order - newest change first)
200 */
201 CURSOR cur_rowid IS
202 SELECT rowid
203 FROM igf_sl_cl_setup_all
204 WHERE clset_id = x_clset_id
205 FOR UPDATE NOWAIT;
206
207 lv_rowid cur_rowid%RowType;
208
209 BEGIN
210
211 OPEN cur_rowid;
212 FETCH cur_rowid INTO lv_rowid;
213 IF (cur_rowid%FOUND) THEN
214 CLOSE cur_rowid;
215 RETURN(TRUE);
216 ELSE
217 CLOSE cur_rowid;
218 RETURN(FALSE);
219 END IF;
220
221 END get_pk_for_validation;
222
223
224 FUNCTION get_uk_for_validation (
225 x_ci_cal_type IN VARCHAR2,
226 x_ci_sequence_number IN NUMBER,
227 x_relationship_cd IN VARCHAR2,
228 x_party_id IN NUMBER
229 ) RETURN BOOLEAN AS
230 /*
231 || Created By : venagara
232 || Created On : 02-DEC-2000
233 || Purpose : Validates the Unique Keys of the table.
234 || Known limitations, enhancements or remarks :
235 || Change History :
236 || Who When What
237 || veramach 5-SEP-2003 Changed the signature of the function to use relationship_code_txt and party_id
238 || (reverse chronological order - newest change first)
239 */
240 CURSOR cur_rowid IS
241 SELECT rowid
242 FROM igf_sl_cl_setup_all
243 WHERE ci_cal_type = x_ci_cal_type
244 AND ci_sequence_number = x_ci_sequence_number
245 AND relationship_cd = x_relationship_cd
246 AND NVL(party_id,-100) = NVL(x_party_id,-100)
247 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
248
249 lv_rowid cur_rowid%RowType;
250
251 BEGIN
252
253 OPEN cur_rowid;
254 FETCH cur_rowid INTO lv_rowid;
255 IF (cur_rowid%FOUND) THEN
256 CLOSE cur_rowid;
257 RETURN (true);
258 ELSE
259 CLOSE cur_rowid;
260 RETURN(FALSE);
261 END IF;
262
263 END get_uk_for_validation ;
264
265
266 PROCEDURE get_fk_igs_ca_inst (
267 x_cal_type IN VARCHAR2,
268 x_sequence_number IN NUMBER
269 ) AS
270 /*
271 || Created By : venagara
272 || Created On : 02-DEC-2000
273 || Purpose : Validates the Foreign Keys for the table.
274 || Known limitations, enhancements or remarks :
275 || Change History :
276 || Who When What
277 || (reverse chronological order - newest change first)
278 */
279 CURSOR cur_rowid IS
280 SELECT rowid
281 FROM igf_sl_cl_setup_all
282 WHERE ((ci_cal_type = x_cal_type) AND
283 (ci_sequence_number = x_sequence_number));
284
285 lv_rowid cur_rowid%RowType;
286
287 BEGIN
288
289 OPEN cur_rowid;
290 FETCH cur_rowid INTO lv_rowid;
291 IF (cur_rowid%FOUND) THEN
292 CLOSE cur_rowid;
293 fnd_message.set_name ('IGF', 'IGF_SL_CLSET_CI_FK');
294 igs_ge_msg_stack.add;
295 app_exception.raise_exception;
296 RETURN;
297 END IF;
298 CLOSE cur_rowid;
299
300 END get_fk_igs_ca_inst;
301
302
303 PROCEDURE get_fk_igf_sl_cl_recipient (
304 x_relationship_cd IN VARCHAR2
305 ) AS
306 /*
307 || Created By : veramach
308 || Created On : 05-SEP-2003
309 || Purpose : Validates the Foreign Keys for the table.
310 || Known limitations, enhancements or remarks :
311 || Change History :
312 || Who When What
313 ||veramach 16-SEP-2003 1. Changed message name from IGF_SL_CL_RECIP_FK to IGF_SL_CLSET_FK
314 || (reverse chronological order - newest change first)
315 */
316 CURSOR cur_rowid IS
317 SELECT rowid
318 FROM igf_sl_cl_setup_all
319 WHERE ((relationship_cd = x_relationship_cd ));
320
321 lv_rowid cur_rowid%RowType;
322
323 BEGIN
324
325 OPEN cur_rowid;
326 FETCH cur_rowid INTO lv_rowid;
327 IF (cur_rowid%FOUND) THEN
328 CLOSE cur_rowid;
329 fnd_message.set_name ('IGF', 'IGF_SL_CLSET_RECIP_FK');
330 igs_ge_msg_stack.add;
331 app_exception.raise_exception;
332 RETURN;
333 END IF;
334 CLOSE cur_rowid;
335
336 END get_fk_igf_sl_cl_recipient;
337
338
339 PROCEDURE before_dml (
340 p_action IN VARCHAR2,
341 x_rowid IN VARCHAR2 DEFAULT NULL,
342 x_clset_id IN NUMBER DEFAULT NULL,
343 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
344 x_ci_sequence_number IN NUMBER DEFAULT NULL,
348 x_duns_lender_id IN VARCHAR2 DEFAULT NULL,
345 x_borw_interest_ind IN VARCHAR2 DEFAULT NULL,
346 x_sch_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
347 x_lender_id IN VARCHAR2 DEFAULT NULL,
349 x_lend_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
350 x_guarantor_id IN VARCHAR2 DEFAULT NULL,
351 x_duns_guarnt_id IN VARCHAR2 DEFAULT NULL,
352 x_recipient_id IN VARCHAR2 DEFAULT NULL,
353 x_recipient_type IN VARCHAR2 DEFAULT NULL,
354 x_duns_recip_id IN VARCHAR2 DEFAULT NULL,
355 x_recip_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
356 x_est_orig_fee_perct IN NUMBER DEFAULT NULL,
357 x_est_guarnt_fee_perct IN NUMBER DEFAULT NULL,
358 x_hold_rel_ind IN VARCHAR2 DEFAULT NULL,
359 x_req_serial_loan_code IN VARCHAR2 DEFAULT NULL,
360 x_loan_award_method IN VARCHAR2 DEFAULT NULL,
361 x_prc_type_code IN VARCHAR2 DEFAULT NULL,
362 x_pnote_delivery_code IN VARCHAR2 DEFAULT NULL,
363 x_media_type IN VARCHAR2 DEFAULT NULL,
364 x_eft_authorization IN VARCHAR2 DEFAULT NULL,
365 x_auto_late_disb_ind IN VARCHAR2 DEFAULT NULL,
366 x_cl_version IN VARCHAR2 DEFAULT NULL,
367 x_creation_date IN DATE DEFAULT NULL,
368 x_created_by IN NUMBER DEFAULT NULL,
369 x_last_update_date IN DATE DEFAULT NULL,
370 x_last_updated_by IN NUMBER DEFAULT NULL,
371 x_last_update_login IN NUMBER DEFAULT NULL,
372 x_est_alt_orig_fee_perct IN NUMBER DEFAULT NULL,
373 x_est_alt_guarnt_fee_perct IN NUMBER DEFAULT NULL,
374 x_relationship_cd IN VARCHAR2 DEFAULT NULL,
375 x_default_flag IN VARCHAR2 DEFAULT NULL,
376 x_party_id IN NUMBER DEFAULT NULL,
377 x_plus_processing_type_code IN VARCHAR2 DEFAULT NULL,
378 x_fund_return_method_code IN VARCHAR2 DEFAULT NULL
379 ) AS
380 /*
381 || Created By : venagara
382 || Created On : 02-DEC-2000
383 || Purpose : Initialises the columns, Checks Constraints, Calls the
384 || Trigger Handlers for the table, before any DML operation.
385 || Known limitations, enhancements or remarks :
386 || Change History :
387 || Who When What
388 || veramach 5-SEP-2003 Added x_relationship_code_txt,x_default_flag,x_party_id for the newly added columns
389 || (reverse chronological order - newest change first)
390 */
391 BEGIN
392
393 set_column_values (
394 p_action,
395 x_rowid,
396 x_clset_id,
397 x_ci_cal_type,
398 x_ci_sequence_number,
399 x_borw_interest_ind,
400 x_sch_non_ed_brc_id,
401 x_lender_id,
402 x_duns_lender_id,
403 x_lend_non_ed_brc_id,
404 x_guarantor_id,
405 x_duns_guarnt_id,
406 x_recipient_id,
407 x_recipient_type,
408 x_duns_recip_id,
409 x_recip_non_ed_brc_id,
410 x_est_orig_fee_perct,
411 x_est_guarnt_fee_perct,
412 x_hold_rel_ind,
413 x_req_serial_loan_code,
414 x_loan_award_method,
415 x_prc_type_code,
416 x_pnote_delivery_code,
417 x_media_type,
418 x_eft_authorization,
419 x_auto_late_disb_ind,
420 x_cl_version,
421 x_creation_date,
422 x_created_by,
423 x_last_update_date,
424 x_last_updated_by,
425 x_last_update_login,
426 x_est_alt_orig_fee_perct,
427 x_est_alt_guarnt_fee_perct,
428 x_relationship_cd ,
429 x_default_flag,
430 x_party_id,
431 x_plus_processing_type_code,
432 x_fund_return_method_code
433 );
434 IF (p_action = 'INSERT') THEN
435 -- Call all the procedures related to Before Insert.
436 IF ( get_pk_for_validation(
437 new_references.clset_id
438 )
439 ) THEN
440 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
441 igs_ge_msg_stack.add;
442 app_exception.raise_exception;
443 END IF;
444 check_uniqueness;
445 check_parent_existance;
446 ELSIF (p_action = 'UPDATE') THEN
447 -- Call all the procedures related to Before Update.
448 check_uniqueness;
449 check_parent_existance;
450 ELSIF (p_action = 'VALIDATE_INSERT') THEN
451 -- Call all the procedures related to Before Insert.
452 IF ( get_pk_for_validation (
453 new_references.clset_id
454 )
455 ) THEN
456 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
457 igs_ge_msg_stack.add;
458 app_exception.raise_exception;
459 END IF;
460 check_uniqueness;
461 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
462 check_uniqueness;
466
463 END IF;
464
465 END before_dml;
467
468 PROCEDURE insert_row (
469 x_rowid IN OUT NOCOPY VARCHAR2,
470 x_clset_id IN OUT NOCOPY NUMBER,
471 x_ci_cal_type IN VARCHAR2,
472 x_ci_sequence_number IN NUMBER,
473 x_borw_interest_ind IN VARCHAR2,
474 x_sch_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
475 x_lender_id IN VARCHAR2,
476 x_duns_lender_id IN VARCHAR2 DEFAULT NULL,
477 x_lend_non_ed_brc_id IN VARCHAR2,
478 x_guarantor_id IN VARCHAR2,
479 x_duns_guarnt_id IN VARCHAR2 DEFAULT NULL,
480 x_recipient_id IN VARCHAR2,
481 x_recipient_type IN VARCHAR2,
482 x_duns_recip_id IN VARCHAR2 DEFAULT NULL,
483 x_recip_non_ed_brc_id IN VARCHAR2,
484 x_est_orig_fee_perct IN NUMBER DEFAULT NULL,
485 x_est_guarnt_fee_perct IN NUMBER DEFAULT NULL,
486 x_hold_rel_ind IN VARCHAR2,
487 x_req_serial_loan_code IN VARCHAR2,
488 x_loan_award_method IN VARCHAR2,
489 x_prc_type_code IN VARCHAR2,
490 x_pnote_delivery_code IN VARCHAR2,
491 x_media_type IN VARCHAR2,
492 x_eft_authorization IN VARCHAR2,
493 x_auto_late_disb_ind IN VARCHAR2,
494 x_cl_version IN VARCHAR2 DEFAULT NULL,
495 x_mode IN VARCHAR2 DEFAULT 'R',
496 x_est_alt_orig_fee_perct IN NUMBER DEFAULT NULL,
497 x_est_alt_guarnt_fee_perct IN NUMBER DEFAULT NULL,
498 x_relationship_cd IN VARCHAR2 DEFAULT NULL,
499 x_default_flag IN VARCHAR2 DEFAULT NULL,
500 x_party_id IN NUMBER DEFAULT NULL,
501 x_plus_processing_type_code IN VARCHAR2 DEFAULT NULL,
502 x_fund_return_method_code IN VARCHAR2 DEFAULT NULL
503 ) AS
504 /*
505 || Created By : venagara
506 || Created On : 02-DEC-2000
507 || Purpose : Handles the INSERT DML logic for the table.
508 || Known limitations, enhancements or remarks :
509 || Change History :
510 || Who When What
511 || veramach 5-SEP-2003 Added x_relationship_code_txt,x_default_flag,x_party_id for the newly added columns
512 || (reverse chronological order - newest change first)
513 */
514 CURSOR c IS
515 SELECT rowid
516 FROM igf_sl_cl_setup_all
517 WHERE clset_id = x_clset_id;
518
519 x_last_update_date DATE;
520 x_last_updated_by NUMBER;
521 x_last_update_login NUMBER;
522 l_org_id igf_sl_cl_setup_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
523
524 BEGIN
525
526 x_last_update_date := SYSDATE;
527 IF (x_mode = 'I') THEN
528 x_last_updated_by := 1;
529 x_last_update_login := 0;
530 ELSIF (x_mode = 'R') THEN
531 x_last_updated_by := fnd_global.user_id;
532 IF (x_last_updated_by IS NULL) THEN
533 x_last_updated_by := -1;
534 END IF;
535 x_last_update_login := fnd_global.login_id;
536 IF (x_last_update_login IS NULL) THEN
537 x_last_update_login := -1;
538 END IF;
539 ELSE
540 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
541 igs_ge_msg_stack.add;
542 app_exception.raise_exception;
543 END IF;
544
545 SELECT igf_sl_cl_setup_s.nextval INTO x_clset_id FROM DUAL;
546
547
548 before_dml(
549 p_action => 'INSERT',
550 x_rowid => x_rowid,
551 x_clset_id => x_clset_id,
552 x_ci_cal_type => x_ci_cal_type,
553 x_ci_sequence_number => x_ci_sequence_number,
554 x_borw_interest_ind => x_borw_interest_ind,
555 x_sch_non_ed_brc_id => x_sch_non_ed_brc_id,
556 x_lender_id => x_lender_id,
557 x_duns_lender_id => x_duns_lender_id,
558 x_lend_non_ed_brc_id => x_lend_non_ed_brc_id,
559 x_guarantor_id => x_guarantor_id,
560 x_duns_guarnt_id => x_duns_guarnt_id,
561 x_recipient_id => x_recipient_id,
562 x_recipient_type => x_recipient_type,
563 x_duns_recip_id => x_duns_recip_id,
564 x_recip_non_ed_brc_id => x_recip_non_ed_brc_id,
565 x_est_orig_fee_perct => x_est_orig_fee_perct,
566 x_est_guarnt_fee_perct => x_est_guarnt_fee_perct,
567 x_hold_rel_ind => x_hold_rel_ind,
568 x_req_serial_loan_code => x_req_serial_loan_code,
569 x_loan_award_method => x_loan_award_method,
570 x_prc_type_code => x_prc_type_code,
571 x_pnote_delivery_code => x_pnote_delivery_code,
575 x_cl_version => x_cl_version,
572 x_media_type => x_media_type,
573 x_eft_authorization => x_eft_authorization,
574 x_auto_late_disb_ind => x_auto_late_disb_ind,
576 x_creation_date => x_last_update_date,
577 x_created_by => x_last_updated_by,
578 x_last_update_date => x_last_update_date,
579 x_last_updated_by => x_last_updated_by,
580 x_last_update_login => x_last_update_login,
581 x_est_alt_orig_fee_perct => x_est_alt_orig_fee_perct,
582 x_est_alt_guarnt_fee_perct => x_est_alt_guarnt_fee_perct,
583 x_relationship_cd => x_relationship_cd ,
584 x_default_flag => x_default_flag,
585 x_party_id => x_party_id,
586 x_plus_processing_type_code => x_plus_processing_type_code,
587 x_fund_return_method_code => x_fund_return_method_code
588 );
589
590
591 INSERT INTO igf_sl_cl_setup_all(
592 clset_id,
593 ci_cal_type,
594 ci_sequence_number,
595 borw_interest_ind,
596 est_orig_fee_perct,
597 est_guarnt_fee_perct,
598 hold_rel_ind,
599 req_serial_loan_code,
600 prc_type_code,
601 pnote_delivery_code,
602 eft_authorization,
603 auto_late_disb_ind,
604 creation_date,
605 created_by,
606 last_update_date,
607 last_updated_by,
608 last_update_login,
609 org_id,
610 est_alt_orig_fee_perct,
611 est_alt_guarnt_fee_perct,
612 relationship_cd ,
613 default_flag,
614 party_id,
615 cl_version,
616 plus_processing_type_code,
617 fund_return_method_code
618 ) VALUES (
619 new_references.clset_id,
620 new_references.ci_cal_type,
621 new_references.ci_sequence_number,
622 new_references.borw_interest_ind,
623 new_references.est_orig_fee_perct,
624 new_references.est_guarnt_fee_perct,
625 new_references.hold_rel_ind,
626 new_references.req_serial_loan_code,
627 new_references.prc_type_code,
628 new_references.pnote_delivery_code,
629 new_references.eft_authorization,
630 new_references.auto_late_disb_ind,
631 x_last_update_date,
632 x_last_updated_by,
633 x_last_update_date,
634 x_last_updated_by,
635 x_last_update_login ,
636 l_org_id,
637 new_references.est_alt_orig_fee_perct,
638 new_references.est_alt_guarnt_fee_perct,
639 new_references.relationship_cd ,
640 new_references.default_flag,
641 new_references.party_id,
642 new_references.cl_version,
643 new_references.plus_processing_type_code,
644 new_references.fund_return_method_code
645 );
646
647
648 OPEN c;
649 FETCH c INTO x_rowid;
650 IF (c%NOTFOUND) THEN
651 CLOSE c;
652 RAISE NO_DATA_FOUND;
653 END IF;
654 CLOSE c;
655
656 END insert_row;
657
658
659 PROCEDURE lock_row (
660 x_rowid IN VARCHAR2,
661 x_clset_id IN NUMBER,
662 x_ci_cal_type IN VARCHAR2,
663 x_ci_sequence_number IN NUMBER,
664 x_borw_interest_ind IN VARCHAR2,
665 x_sch_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
666 x_lender_id IN VARCHAR2,
667 x_duns_lender_id IN VARCHAR2 DEFAULT NULL,
668 x_lend_non_ed_brc_id IN VARCHAR2,
669 x_guarantor_id IN VARCHAR2,
670 x_duns_guarnt_id IN VARCHAR2 DEFAULT NULL,
671 x_recipient_id IN VARCHAR2,
672 x_recipient_type IN VARCHAR2,
673 x_duns_recip_id IN VARCHAR2 DEFAULT NULL,
674 x_recip_non_ed_brc_id IN VARCHAR2,
675 x_est_orig_fee_perct IN NUMBER DEFAULT NULL,
676 x_est_guarnt_fee_perct IN NUMBER DEFAULT NULL,
677 x_hold_rel_ind IN VARCHAR2,
678 x_req_serial_loan_code IN VARCHAR2,
679 x_loan_award_method IN VARCHAR2,
680 x_prc_type_code IN VARCHAR2,
681 x_pnote_delivery_code IN VARCHAR2,
682 x_media_type IN VARCHAR2,
683 x_eft_authorization IN VARCHAR2,
684 x_auto_late_disb_ind IN VARCHAR2,
685 x_cl_version IN VARCHAR2 DEFAULT NULL,
686 x_est_alt_orig_fee_perct IN NUMBER DEFAULT NULL,
687 x_est_alt_guarnt_fee_perct IN NUMBER DEFAULT NULL,
688 x_relationship_cd IN VARCHAR2 DEFAULT NULL,
689 x_default_flag IN VARCHAR2 DEFAULT NULL,
690 x_party_id IN NUMBER DEFAULT NULL,
691 x_plus_processing_type_code IN VARCHAR2 DEFAULT NULL,
692 x_fund_return_method_code IN VARCHAR2 DEFAULT NULL
693 ) AS
694 /*
698 || Known limitations, enhancements or remarks :
695 || Created By : venagara
696 || Created On : 02-DEC-2000
697 || Purpose : Handles the LOCK mechanism for the table.
699 || Change History :
700 || Who When What
701 || veramach 5-SEP-2003 Added x_relationship_code_txt,x_default_flag,x_party_id for the newly added columns
702 || masehgal 16-Jun-2003 # 2990040 FACR115
703 || Changes to lock row for cl_version
704 || (reverse chronological order - newest change first)
705 */
706 CURSOR c1 IS
707 SELECT
708 ci_cal_type,
709 ci_sequence_number,
710 borw_interest_ind,
711 est_orig_fee_perct,
712 est_guarnt_fee_perct,
713 hold_rel_ind,
714 req_serial_loan_code,
715 prc_type_code,
716 pnote_delivery_code,
717 eft_authorization,
718 auto_late_disb_ind,
719 org_id,
720 est_alt_orig_fee_perct,
721 est_alt_guarnt_fee_perct,
722 relationship_cd ,
723 default_flag,
724 party_id,
725 cl_version,
726 plus_processing_type_code,
727 fund_return_method_code
728 FROM igf_sl_cl_setup_all
729 WHERE rowid = x_rowid
730 FOR UPDATE NOWAIT;
731
732 tlinfo c1%ROWTYPE;
733
734 BEGIN
735
736 OPEN c1;
737 FETCH c1 INTO tlinfo;
738 IF (c1%notfound) THEN
739 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
740 igs_ge_msg_stack.add;
741 CLOSE c1;
742 app_exception.raise_exception;
743 RETURN;
744 END IF;
745 CLOSE c1;
746 IF (
747 (tlinfo.ci_cal_type = x_ci_cal_type)
748 AND (tlinfo.ci_sequence_number = x_ci_sequence_number)
749 AND ((tlinfo.borw_interest_ind = x_borw_interest_ind) OR ((tlinfo.borw_interest_ind IS NULL) AND (X_borw_interest_ind IS NULL)))
750 AND ((tlinfo.est_orig_fee_perct = x_est_orig_fee_perct) OR ((tlinfo.est_orig_fee_perct IS NULL) AND (x_est_orig_fee_perct IS NULL)))
751 AND ((tlinfo.est_guarnt_fee_perct = x_est_guarnt_fee_perct) OR ((tlinfo.est_guarnt_fee_perct IS NULL) AND (x_est_guarnt_fee_perct IS NULL)))
752 AND (tlinfo.hold_rel_ind = x_hold_rel_ind)
753 AND (tlinfo.req_serial_loan_code = x_req_serial_loan_code)
754 AND (tlinfo.prc_type_code = x_prc_type_code)
755 AND (tlinfo.pnote_delivery_code = x_pnote_delivery_code)
756 AND ((tlinfo.eft_authorization = x_eft_authorization) OR ((tlinfo.eft_authorization IS NULL) AND (X_eft_authorization IS NULL)))
757 AND ((tlinfo.auto_late_disb_ind = x_auto_late_disb_ind) OR ((tlinfo.auto_late_disb_ind IS NULL) AND (X_auto_late_disb_ind IS NULL)))
758 AND ((tlinfo.est_alt_orig_fee_perct = x_est_alt_orig_fee_perct) OR ((tlinfo.est_alt_orig_fee_perct IS NULL) AND (x_est_alt_orig_fee_perct IS NULL)))
759 AND ((tlinfo.est_alt_guarnt_fee_perct = x_est_alt_guarnt_fee_perct) OR ((tlinfo.est_alt_guarnt_fee_perct IS NULL) AND (x_est_alt_guarnt_fee_perct IS NULL)))
760 AND ((tlinfo.relationship_cd = x_relationship_cd ) OR ((tlinfo.relationship_cd IS NULL) AND (x_relationship_cd IS NULL)))
761 AND ((tlinfo.default_flag = x_default_flag) OR ((tlinfo.default_flag IS NULL) AND (x_default_flag IS NULL)))
762 AND ((tlinfo.party_id = x_party_id) OR ((tlinfo.party_id IS NULL) AND (x_party_id IS NULL)))
763 AND ((tlinfo.cl_version = x_cl_version) OR ((tlinfo.cl_version IS NULL) AND (x_cl_version IS NULL)))
764 AND ((tlinfo.plus_processing_type_code = x_plus_processing_type_code) OR ((tlinfo.plus_processing_type_code IS NULL) AND (x_plus_processing_type_code IS NULL)))
765 AND ((tlinfo.fund_return_method_code = x_fund_return_method_code) OR ((tlinfo.fund_return_method_code IS NULL) AND (x_fund_return_method_code IS NULL)))
766 ) THEN
767 NULL;
768 ELSE
769 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
770 igs_ge_msg_stack.add;
771 app_exception.raise_exception;
772 END IF;
773
774 RETURN;
775
776 END lock_row;
777
778
779 PROCEDURE update_row (
780 x_rowid IN VARCHAR2,
781 x_clset_id IN NUMBER,
782 x_ci_cal_type IN VARCHAR2,
783 x_ci_sequence_number IN NUMBER,
784 x_borw_interest_ind IN VARCHAR2,
785 x_sch_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
786 x_lender_id IN VARCHAR2,
787 x_duns_lender_id IN VARCHAR2 DEFAULT NULL,
788 x_lend_non_ed_brc_id IN VARCHAR2,
789 x_guarantor_id IN VARCHAR2,
790 x_duns_guarnt_id IN VARCHAR2 DEFAULT NULL,
791 x_recipient_id IN VARCHAR2,
792 x_recipient_type IN VARCHAR2,
793 x_duns_recip_id IN VARCHAR2 DEFAULT NULL,
794 x_recip_non_ed_brc_id IN VARCHAR2,
795 x_est_orig_fee_perct IN NUMBER DEFAULT NULL,
796 x_est_guarnt_fee_perct IN NUMBER DEFAULT NULL,
797 x_hold_rel_ind IN VARCHAR2,
798 x_req_serial_loan_code IN VARCHAR2,
799 x_loan_award_method IN VARCHAR2,
800 x_prc_type_code IN VARCHAR2,
804 x_auto_late_disb_ind IN VARCHAR2,
801 x_pnote_delivery_code IN VARCHAR2,
802 x_media_type IN VARCHAR2,
803 x_eft_authorization IN VARCHAR2,
805 x_cl_version IN VARCHAR2 DEFAULT NULL,
806 x_mode IN VARCHAR2 DEFAULT 'R',
807 x_est_alt_orig_fee_perct IN NUMBER DEFAULT NULL,
808 x_est_alt_guarnt_fee_perct IN NUMBER DEFAULT NULL,
809 x_relationship_cd IN VARCHAR2 DEFAULT NULL,
810 x_default_flag IN VARCHAR2 DEFAULT NULL,
811 x_party_id IN NUMBER DEFAULT NULL,
812 x_plus_processing_type_code IN VARCHAR2 DEFAULT NULL,
813 x_fund_return_method_code IN VARCHAR2 DEFAULT NULL
814 ) AS
815 /*
816 || Created By : venagara
817 || Created On : 02-DEC-2000
818 || Purpose : Handles the UPDATE DML logic for the table.
819 || Known limitations, enhancements or remarks :
820 || Change History :
821 || Who When What
822 || veramach 5-SEP-2003 Added x_relationship_code_txt,x_default_flag,x_party_id for the newly added columns
823 || (reverse chronological order - newest change first)
824 */
825 x_last_update_date DATE ;
826 x_last_updated_by NUMBER;
827 x_last_update_login NUMBER;
828
829 BEGIN
830
831 x_last_update_date := SYSDATE;
832 IF (X_MODE = 'I') THEN
833 x_last_updated_by := 1;
834 x_last_update_login := 0;
835 ELSIF (x_mode = 'R') THEN
836 x_last_updated_by := fnd_global.user_id;
837 IF x_last_updated_by IS NULL THEN
838 x_last_updated_by := -1;
839 END IF;
840 x_last_update_login := fnd_global.login_id;
841 IF (x_last_update_login IS NULL) THEN
842 x_last_update_login := -1;
843 END IF;
844 ELSE
845 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
846 igs_ge_msg_stack.add;
847 app_exception.raise_exception;
848 END IF;
849
850 before_dml(
851 p_action => 'UPDATE',
852 x_rowid => x_rowid,
853 x_clset_id => x_clset_id,
854 x_ci_cal_type => x_ci_cal_type,
855 x_ci_sequence_number => x_ci_sequence_number,
856 x_borw_interest_ind => x_borw_interest_ind,
857 x_sch_non_ed_brc_id => x_sch_non_ed_brc_id,
858 x_lender_id => x_lender_id,
859 x_duns_lender_id => x_duns_lender_id,
860 x_lend_non_ed_brc_id => x_lend_non_ed_brc_id,
861 x_guarantor_id => x_guarantor_id,
862 x_duns_guarnt_id => x_duns_guarnt_id,
863 x_recipient_id => x_recipient_id,
864 x_recipient_type => x_recipient_type,
865 x_duns_recip_id => x_duns_recip_id,
866 x_recip_non_ed_brc_id => x_recip_non_ed_brc_id,
867 x_est_orig_fee_perct => x_est_orig_fee_perct,
868 x_est_guarnt_fee_perct => x_est_guarnt_fee_perct,
869 x_hold_rel_ind => x_hold_rel_ind,
870 x_req_serial_loan_code => x_req_serial_loan_code,
871 x_loan_award_method => x_loan_award_method,
872 x_prc_type_code => x_prc_type_code,
873 x_pnote_delivery_code => x_pnote_delivery_code,
874 x_media_type => x_media_type,
875 x_eft_authorization => x_eft_authorization,
876 x_auto_late_disb_ind => x_auto_late_disb_ind,
877 x_cl_version => x_cl_version,
878 x_creation_date => x_last_update_date,
879 x_created_by => x_last_updated_by,
880 x_last_update_date => x_last_update_date,
881 x_last_updated_by => x_last_updated_by,
882 x_last_update_login => x_last_update_login,
883 x_est_alt_orig_fee_perct => x_est_alt_orig_fee_perct,
884 x_est_alt_guarnt_fee_perct => x_est_alt_guarnt_fee_perct,
885 x_relationship_cd => x_relationship_cd ,
886 x_default_flag => x_default_flag,
887 x_party_id => x_party_id,
888 x_plus_processing_type_code => x_plus_processing_type_code,
889 x_fund_return_method_code => x_fund_return_method_code
890 );
891
892 UPDATE igf_sl_cl_setup_all
893 SET
894 ci_cal_type = new_references.ci_cal_type,
895 ci_sequence_number = new_references.ci_sequence_number,
896 borw_interest_ind = new_references.borw_interest_ind,
897 est_orig_fee_perct = new_references.est_orig_fee_perct,
898 est_guarnt_fee_perct = new_references.est_guarnt_fee_perct,
899 hold_rel_ind = new_references.hold_rel_ind,
900 req_serial_loan_code = new_references.req_serial_loan_code,
901 prc_type_code = new_references.prc_type_code,
905 last_update_date = x_last_update_date,
902 pnote_delivery_code = new_references.pnote_delivery_code,
903 eft_authorization = new_references.eft_authorization,
904 auto_late_disb_ind = new_references.auto_late_disb_ind,
906 last_updated_by = x_last_updated_by,
907 last_update_login = x_last_update_login,
908 est_alt_orig_fee_perct = x_est_alt_orig_fee_perct,
909 est_alt_guarnt_fee_perct = x_est_alt_guarnt_fee_perct,
910 relationship_cd = x_relationship_cd,
911 default_flag = x_default_flag,
912 party_id = x_party_id,
913 cl_version = x_cl_version,
914 plus_processing_type_code = x_plus_processing_type_code,
915 fund_return_method_code = x_fund_return_method_code
916 WHERE rowid = x_rowid;
917
918 IF (SQL%NOTFOUND) THEN
919 RAISE NO_DATA_FOUND;
920 END IF;
921
922 END update_row;
923
924
925 PROCEDURE add_row (
926 x_rowid IN OUT NOCOPY VARCHAR2,
927 x_clset_id IN OUT NOCOPY NUMBER,
928 x_ci_cal_type IN VARCHAR2,
929 x_ci_sequence_number IN NUMBER,
930 x_borw_interest_ind IN VARCHAR2,
931 x_sch_non_ed_brc_id IN VARCHAR2,
932 x_lender_id IN VARCHAR2,
933 x_duns_lender_id IN VARCHAR2 DEFAULT NULL,
934 x_lend_non_ed_brc_id IN VARCHAR2,
935 x_guarantor_id IN VARCHAR2,
936 x_duns_guarnt_id IN VARCHAR2 DEFAULT NULL,
937 x_recipient_id IN VARCHAR2,
938 x_recipient_type IN VARCHAR2,
939 x_duns_recip_id IN VARCHAR2 DEFAULT NULL,
940 x_recip_non_ed_brc_id IN VARCHAR2,
941 x_est_orig_fee_perct IN NUMBER DEFAULT NULL,
942 x_est_guarnt_fee_perct IN NUMBER DEFAULT NULL,
943 x_hold_rel_ind IN VARCHAR2,
944 x_req_serial_loan_code IN VARCHAR2,
945 x_loan_award_method IN VARCHAR2,
946 x_prc_type_code IN VARCHAR2,
947 x_pnote_delivery_code IN VARCHAR2,
948 x_media_type IN VARCHAR2,
949 x_eft_authorization IN VARCHAR2,
950 x_auto_late_disb_ind IN VARCHAR2,
951 x_cl_version IN VARCHAR2 DEFAULT NULL,
952 x_mode IN VARCHAR2 DEFAULT 'R',
953 x_est_alt_orig_fee_perct IN NUMBER DEFAULT NULL,
954 x_est_alt_guarnt_fee_perct IN NUMBER DEFAULT NULL,
955 x_relationship_cd IN VARCHAR2 DEFAULT NULL,
956 x_default_flag IN VARCHAR2 DEFAULT NULL,
957 x_party_id IN NUMBER DEFAULT NULL,
958 x_plus_processing_type_code IN VARCHAR2 DEFAULT NULL,
959 x_fund_return_method_code IN VARCHAR2 DEFAULT NULL
960 ) AS
961 /*
962 || Created By : venagara
963 || Created On : 02-DEC-2000
964 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
965 || Known limitations, enhancements or remarks :
966 || Change History :
967 || Who When What
968 || veramach 5-SEP-2003 Added x_relationship_code_txt,x_default_flag,x_party_id for the newly added columns
969 || (reverse chronological order - newest change first)
970 */
971 CURSOR c1 IS
972 SELECT rowid
973 FROM igf_sl_cl_setup_all
974 WHERE clset_id = x_clset_id;
975
976 BEGIN
977
978 OPEN c1;
979 FETCH c1 INTO x_rowid;
980 IF (c1%NOTFOUND) THEN
981 CLOSE c1;
982
983 insert_row (
984 x_rowid,
985 x_clset_id,
986 x_ci_cal_type,
987 x_ci_sequence_number,
988 x_borw_interest_ind,
989 x_sch_non_ed_brc_id,
990 x_lender_id,
991 x_duns_lender_id,
992 x_lend_non_ed_brc_id,
993 x_guarantor_id,
994 x_duns_guarnt_id,
995 x_recipient_id,
996 x_recipient_type,
997 x_duns_recip_id,
998 x_recip_non_ed_brc_id,
999 x_est_orig_fee_perct,
1000 x_est_guarnt_fee_perct,
1001 x_hold_rel_ind,
1002 x_req_serial_loan_code,
1003 x_loan_award_method,
1004 x_prc_type_code,
1005 x_pnote_delivery_code,
1006 x_media_type,
1007 x_eft_authorization,
1008 x_auto_late_disb_ind,
1009 x_cl_version,
1010 x_mode,
1011 x_est_alt_orig_fee_perct,
1012 x_est_alt_guarnt_fee_perct,
1013 x_relationship_cd ,
1014 x_default_flag,
1015 x_party_id,
1016 x_plus_processing_type_code,
1017 x_fund_return_method_code
1018 );
1019 RETURN;
1020 END IF;
1021 CLOSE c1;
1022
1023 update_row (
1024 x_rowid,
1025 x_clset_id,
1026 x_ci_cal_type,
1027 x_ci_sequence_number,
1028 x_borw_interest_ind,
1029 x_sch_non_ed_brc_id,
1030 x_lender_id,
1031 x_duns_lender_id,
1032 x_lend_non_ed_brc_id,
1033 x_guarantor_id,
1034 x_duns_guarnt_id,
1035 x_recipient_id,
1036 x_recipient_type,
1037 x_duns_recip_id,
1038 x_recip_non_ed_brc_id,
1039 x_est_orig_fee_perct,
1040 x_est_guarnt_fee_perct,
1041 x_hold_rel_ind,
1042 x_req_serial_loan_code,
1043 x_loan_award_method,
1044 x_prc_type_code,
1045 x_pnote_delivery_code,
1046 x_media_type,
1047 x_eft_authorization,
1048 x_auto_late_disb_ind,
1049 x_cl_version,
1050 x_mode,
1051 x_est_alt_orig_fee_perct,
1052 x_est_alt_guarnt_fee_perct,
1053 x_relationship_cd ,
1054 x_default_flag,
1055 x_party_id,
1056 x_plus_processing_type_code,
1057 x_fund_return_method_code
1058 );
1059
1060 END add_row;
1061
1062
1063 PROCEDURE delete_row (
1064 x_rowid IN VARCHAR2
1065 ) AS
1066 /*
1067 || Created By : venagara
1068 || Created On : 02-DEC-2000
1069 || Purpose : Handles the DELETE DML logic for the table.
1070 || Known limitations, enhancements or remarks :
1071 || Change History :
1072 || Who When What
1073 || (reverse chronological order - newest change first)
1074 */
1075 BEGIN
1076
1077 before_dml (
1078 p_action => 'DELETE',
1079 x_rowid => x_rowid
1080 );
1081
1082 DELETE FROM igf_sl_cl_setup_all
1083 WHERE rowid = x_rowid;
1084
1085 IF (SQL%NOTFOUND) THEN
1086 RAISE NO_DATA_FOUND;
1087 END IF;
1088
1089 END delete_row;
1090
1091
1092 END igf_sl_cl_setup_pkg;