[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_SETUP_PKG
Source
1 PACKAGE BODY igf_sl_dl_setup_pkg AS
2 /* $Header: IGFLI07B.pls 120.0 2005/06/01 15:12:03 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sl_dl_setup_all%ROWTYPE;
6 new_references igf_sl_dl_setup_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_dlset_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_school_id IN VARCHAR2 DEFAULT NULL,
15 x_orig_fee_perct_stafford IN NUMBER DEFAULT NULL,
16 x_orig_fee_perct_plus IN NUMBER DEFAULT NULL,
17 x_int_rebate IN NUMBER DEFAULT NULL,
18 x_pnote_print_ind IN VARCHAR2 DEFAULT NULL,
19 x_pnote_print_copies IN NUMBER DEFAULT NULL,
20 x_acc_note_for_disb IN VARCHAR2 DEFAULT NULL,
21 x_affirmation_reqd IN VARCHAR2 DEFAULT NULL,
22 x_interview_reqd IN VARCHAR2 DEFAULT NULL,
23 x_disclosure_print_ind IN VARCHAR2 DEFAULT NULL,
24 x_special_school IN VARCHAR2 DEFAULT NULL,
25 x_dl_version IN VARCHAR2 DEFAULT NULL,
26 x_creation_date IN DATE DEFAULT NULL,
27 x_created_by IN NUMBER DEFAULT NULL,
28 x_last_update_date IN DATE DEFAULT NULL,
29 x_last_updated_by IN NUMBER DEFAULT NULL,
30 x_last_update_login IN NUMBER DEFAULT NULL,
31 x_response_option_code IN VARCHAR2 DEFAULT NULL,
32 x_funding_method IN VARCHAR2 DEFAULT NULL
33 ) AS
34 /*
35 || Created By : venagara
36 || Created On : 31-JAN-2001
37 || Purpose : Initialises the Old and New references for the columns of the table.
38 || Known limitations, enhancements or remarks :
39 || Change History :
40 || Who When What
41 || ugummall 15-OCT-2003 Bug # 3102439. FA 126 Multiple FA Offices
42 || school_id is being obsoleted.
43 || (reverse chronological order - newest change first)
44 */
45
46 CURSOR cur_old_ref_values IS
47 SELECT *
48 FROM IGF_SL_DL_SETUP_ALL
49 WHERE rowid = x_rowid;
50
51 BEGIN
52
53 l_rowid := x_rowid;
54
55 -- Code for setting the Old and New Reference Values.
56 -- Populate Old Values.
57 OPEN cur_old_ref_values;
58 FETCH cur_old_ref_values INTO old_references;
59 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
60 CLOSE cur_old_ref_values;
61 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
62 igs_ge_msg_stack.add;
63 app_exception.raise_exception;
64 RETURN;
65 END IF;
66 CLOSE cur_old_ref_values;
67
68 -- Populate New Values.
69 new_references.dlset_id := x_dlset_id;
70 new_references.ci_cal_type := x_ci_cal_type;
71 new_references.ci_sequence_number := x_ci_sequence_number;
72 new_references.school_id := NULL;
73 new_references.orig_fee_perct_stafford := x_orig_fee_perct_stafford;
74 new_references.orig_fee_perct_plus := x_orig_fee_perct_plus;
75 new_references.int_rebate := x_int_rebate;
76 new_references.pnote_print_ind := x_pnote_print_ind;
77 new_references.pnote_print_copies := x_pnote_print_copies;
78 new_references.acc_note_for_disb := x_acc_note_for_disb;
79 new_references.affirmation_reqd := NULL;
80 new_references.interview_reqd := x_interview_reqd;
81 new_references.disclosure_print_ind := x_disclosure_print_ind;
82 new_references.special_school := x_special_school;
83 new_references.dl_version := x_dl_version;
84 new_references.response_option_code := x_response_option_code;
85 new_references.funding_method := x_funding_method;
86
87 IF (p_action = 'UPDATE') THEN
88 new_references.creation_date := old_references.creation_date;
89 new_references.created_by := old_references.created_by;
90 ELSE
91 new_references.creation_date := x_creation_date;
92 new_references.created_by := x_created_by;
93 END IF;
94
95 new_references.last_update_date := x_last_update_date;
96 new_references.last_updated_by := x_last_updated_by;
97 new_references.last_update_login := x_last_update_login;
98
99 END set_column_values;
100
101
102 PROCEDURE check_uniqueness AS
103 /*
104 || Created By : venagara
105 || Created On : 31-JAN-2001
106 || Purpose : Handles the Unique Constraint logic defined for the columns.
107 || Known limitations, enhancements or remarks :
108 || Change History :
109 || Who When What
110 || (reverse chronological order - newest change first)
111 */
112 BEGIN
113
114 IF ( get_uk_for_validation (
115 new_references.ci_cal_type,
116 new_references.ci_sequence_number
117 )
118 ) THEN
119 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
120 igs_ge_msg_stack.add;
121 app_exception.raise_exception;
122 END IF;
123
124 END check_uniqueness;
125
126
127 PROCEDURE check_parent_existance AS
128 /*
129 || Created By : venagara
130 || Created On : 31-JAN-2001
131 || Purpose : Checks for the existance of Parent records.
132 || Known limitations, enhancements or remarks :
133 || Change History :
134 || Who When What
135 || (reverse chronological order - newest change first)
136 */
137 BEGIN
138
139 IF (((old_references.ci_cal_type = new_references.ci_cal_type) AND
140 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
141 ((new_references.ci_cal_type IS NULL) OR
142 (new_references.ci_sequence_number IS NULL))) THEN
143 NULL;
144 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
145 new_references.ci_cal_type,
146 new_references.ci_sequence_number
147 ) THEN
148 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
149 igs_ge_msg_stack.add;
150 app_exception.raise_exception;
151 END IF;
152
153 END check_parent_existance;
154
155
156 FUNCTION get_pk_for_validation (
157 x_dlset_id IN NUMBER
158 ) RETURN BOOLEAN AS
159 /*
160 || Created By : venagara
161 || Created On : 31-JAN-2001
162 || Purpose : Validates the Primary Key of the table.
163 || Known limitations, enhancements or remarks :
164 || Change History :
165 || Who When What
166 || (reverse chronological order - newest change first)
167 */
168 CURSOR cur_rowid IS
169 SELECT rowid
170 FROM igf_sl_dl_setup_all
171 WHERE dlset_id = x_dlset_id
172 FOR UPDATE NOWAIT;
173
174 lv_rowid cur_rowid%RowType;
175
176 BEGIN
177
178 OPEN cur_rowid;
179 FETCH cur_rowid INTO lv_rowid;
180 IF (cur_rowid%FOUND) THEN
181 CLOSE cur_rowid;
182 RETURN(TRUE);
183 ELSE
184 CLOSE cur_rowid;
185 RETURN(FALSE);
186 END IF;
187
188 END get_pk_for_validation;
189
190
191 FUNCTION get_uk_for_validation (
192 x_ci_cal_type IN VARCHAR2,
193 x_ci_sequence_number IN NUMBER
194 ) RETURN BOOLEAN AS
195 /*
196 || Created By : venagara
197 || Created On : 31-JAN-2001
198 || Purpose : Validates the Unique Keys of the table.
199 || Known limitations, enhancements or remarks :
200 || Change History :
201 || Who When What
202 || (reverse chronological order - newest change first)
203 */
204 CURSOR cur_rowid IS
205 SELECT rowid
206 FROM igf_sl_dl_setup_all
207 WHERE ci_cal_type = x_ci_cal_type
208 AND ci_sequence_number = x_ci_sequence_number
209 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
210
211 lv_rowid cur_rowid%RowType;
212
213 BEGIN
214
215 OPEN cur_rowid;
216 FETCH cur_rowid INTO lv_rowid;
217 IF (cur_rowid%FOUND) THEN
218 CLOSE cur_rowid;
219 RETURN (true);
220 ELSE
221 CLOSE cur_rowid;
222 RETURN(FALSE);
223 END IF;
224
225 END get_uk_for_validation ;
226
227
228 PROCEDURE get_fk_igs_ca_inst (
229 x_cal_type IN VARCHAR2,
230 x_sequence_number IN NUMBER
231 ) AS
232 /*
233 || Created By : venagara
234 || Created On : 31-JAN-2001
235 || Purpose : Validates the Foreign Keys for the table.
236 || Known limitations, enhancements or remarks :
237 || Change History :
238 || Who When What
239 || (reverse chronological order - newest change first)
240 */
241 CURSOR cur_rowid IS
242 SELECT rowid
243 FROM igf_sl_dl_setup_all
244 WHERE ((ci_cal_type = x_cal_type) AND
245 (ci_sequence_number = x_sequence_number));
246
247 lv_rowid cur_rowid%RowType;
248
249 BEGIN
250
251 OPEN cur_rowid;
252 FETCH cur_rowid INTO lv_rowid;
253 IF (cur_rowid%FOUND) THEN
254 CLOSE cur_rowid;
255 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
256 igs_ge_msg_stack.add;
257 app_exception.raise_exception;
258 RETURN;
259 END IF;
260 CLOSE cur_rowid;
261
262 END get_fk_igs_ca_inst;
263
264
265 PROCEDURE before_dml (
266 p_action IN VARCHAR2,
267 x_rowid IN VARCHAR2 DEFAULT NULL,
268 x_dlset_id IN NUMBER DEFAULT NULL,
269 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
270 x_ci_sequence_number IN NUMBER DEFAULT NULL,
271 x_school_id IN VARCHAR2 DEFAULT NULL,
272 x_orig_fee_perct_stafford IN NUMBER DEFAULT NULL,
273 x_orig_fee_perct_plus IN NUMBER DEFAULT NULL,
274 x_int_rebate IN NUMBER DEFAULT NULL,
275 x_pnote_print_ind IN VARCHAR2 DEFAULT NULL,
276 x_pnote_print_copies IN NUMBER DEFAULT NULL,
277 x_acc_note_for_disb IN VARCHAR2 DEFAULT NULL,
278 x_affirmation_reqd IN VARCHAR2 DEFAULT NULL,
279 x_interview_reqd IN VARCHAR2 DEFAULT NULL,
280 x_disclosure_print_ind IN VARCHAR2 DEFAULT NULL,
281 x_special_school IN VARCHAR2 DEFAULT NULL,
282 x_dl_version IN VARCHAR2 DEFAULT NULL,
283 x_creation_date IN DATE DEFAULT NULL,
284 x_created_by IN NUMBER DEFAULT NULL,
285 x_last_update_date IN DATE DEFAULT NULL,
286 x_last_updated_by IN NUMBER DEFAULT NULL,
287 x_last_update_login IN NUMBER DEFAULT NULL,
288 x_response_option_code IN VARCHAR2 DEFAULT NULL,
289 x_funding_method IN VARCHAR2 DEFAULT NULL
290
291 ) AS
292 /*
293 || Created By : venagara
294 || Created On : 31-JAN-2001
295 || Purpose : Initialises the columns, Checks Constraints, Calls the
296 || Trigger Handlers for the table, before any DML operation.
297 || Known limitations, enhancements or remarks :
298 || Change History :
299 || Who When What
300 || ugummall 15-OCT-2003 Bug # 3102439. FA 126 Multiple FA Offices
301 || school_id is being obsoleted.
302 || (reverse chronological order - newest change first)
303 */
304 BEGIN
305
306 set_column_values (
307 p_action,
308 x_rowid,
309 x_dlset_id,
310 x_ci_cal_type,
311 x_ci_sequence_number,
312 NULL, /* school_id is being obsoleted w.r.t FA 126 Bug # 3102439 */
313 x_orig_fee_perct_stafford,
314 x_orig_fee_perct_plus,
315 x_int_rebate,
316 x_pnote_print_ind,
317 x_pnote_print_copies,
318 x_acc_note_for_disb,
319 NULL,
320 x_interview_reqd,
321 x_disclosure_print_ind,
322 x_special_school,
323 x_dl_version,
324 x_creation_date,
325 x_created_by,
326 x_last_update_date,
327 x_last_updated_by,
328 x_last_update_login,
329 x_response_option_code,
330 x_funding_method
331
332 );
333
334 IF (p_action = 'INSERT') THEN
335 -- Call all the procedures related to Before Insert.
336 IF ( get_pk_for_validation(
337 new_references.dlset_id
338 )
339 ) THEN
340 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
341 igs_ge_msg_stack.add;
342 app_exception.raise_exception;
343 END IF;
344 check_uniqueness;
345 check_parent_existance;
346 ELSIF (p_action = 'UPDATE') THEN
347 -- Call all the procedures related to Before Update.
348 check_uniqueness;
349 check_parent_existance;
350 ELSIF (p_action = 'VALIDATE_INSERT') THEN
351 -- Call all the procedures related to Before Insert.
352 IF ( get_pk_for_validation (
353 new_references.dlset_id
354 )
355 ) THEN
356 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
357 igs_ge_msg_stack.add;
358 app_exception.raise_exception;
359 END IF;
360 check_uniqueness;
361 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
362 check_uniqueness;
363 END IF;
364
365 END before_dml;
366
367
368 PROCEDURE insert_row (
369 x_rowid IN OUT NOCOPY VARCHAR2,
370 x_dlset_id IN OUT NOCOPY NUMBER,
371 x_ci_cal_type IN VARCHAR2,
372 x_ci_sequence_number IN NUMBER,
373 x_school_id IN VARCHAR2 DEFAULT NULL,
374 x_orig_fee_perct_stafford IN NUMBER,
375 x_orig_fee_perct_plus IN NUMBER,
376 x_int_rebate IN NUMBER,
377 x_pnote_print_ind IN VARCHAR2,
378 x_pnote_print_copies IN NUMBER,
379 x_acc_note_for_disb IN VARCHAR2,
380 x_affirmation_reqd IN VARCHAR2 DEFAULT NULL,
381 x_interview_reqd IN VARCHAR2,
382 x_disclosure_print_ind IN VARCHAR2,
383 x_special_school IN VARCHAR2,
384 x_dl_version IN VARCHAR2,
385 x_mode IN VARCHAR2 DEFAULT 'R',
386 x_response_option_code IN VARCHAR2,
387 x_funding_method IN VARCHAR2
388
389 ) AS
390 /*
394 || Known limitations, enhancements or remarks :
391 || Created By : venagara
392 || Created On : 31-JAN-2001
393 || Purpose : Handles the INSERT DML logic for the table.
395 || Change History :
396 || Who When What
397 || ugummall 15-OCT-2003 Bug # 3102439. FA 126 Multiple FA Offices
398 || school_id is being obsoleted.
399 || (reverse chronological order - newest change first)
400 */
401 CURSOR c IS
402 SELECT rowid
403 FROM igf_sl_dl_setup_all
404 WHERE dlset_id = x_dlset_id;
405
406 x_last_update_date DATE;
407 x_last_updated_by NUMBER;
408 x_last_update_login NUMBER;
409 l_org_id igf_sl_dl_setup_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
410
411 BEGIN
412
413 x_last_update_date := SYSDATE;
414 IF (x_mode = 'I') THEN
415 x_last_updated_by := 1;
416 x_last_update_login := 0;
417 ELSIF (x_mode = 'R') THEN
418 x_last_updated_by := fnd_global.user_id;
419 IF (x_last_updated_by IS NULL) THEN
420 x_last_updated_by := -1;
421 END IF;
422 x_last_update_login := fnd_global.login_id;
423 IF (x_last_update_login IS NULL) THEN
424 x_last_update_login := -1;
425 END IF;
426 ELSE
427 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
428 igs_ge_msg_stack.add;
429 app_exception.raise_exception;
430 END IF;
431
432 SELECT igf_sl_dl_setup_s.nextval INTO x_dlset_id FROM DUAL;
433 before_dml(
434 p_action => 'INSERT',
435 x_rowid => x_rowid,
436 x_dlset_id => x_dlset_id,
437 x_ci_cal_type => x_ci_cal_type,
438 x_ci_sequence_number => x_ci_sequence_number,
439 x_school_id => NULL,
440 x_orig_fee_perct_stafford => x_orig_fee_perct_stafford,
441 x_orig_fee_perct_plus => x_orig_fee_perct_plus,
442 x_int_rebate => x_int_rebate,
443 x_pnote_print_ind => x_pnote_print_ind,
444 x_pnote_print_copies => x_pnote_print_copies,
445 x_acc_note_for_disb => x_acc_note_for_disb,
446 x_affirmation_reqd => NULL,
447 x_interview_reqd => x_interview_reqd,
448 x_disclosure_print_ind => x_disclosure_print_ind,
449 x_special_school => x_special_school,
450 x_dl_version => x_dl_version,
451 x_creation_date => x_last_update_date,
452 x_created_by => x_last_updated_by,
453 x_last_update_date => x_last_update_date,
454 x_last_updated_by => x_last_updated_by,
455 x_last_update_login => x_last_update_login,
456 x_response_option_code => x_response_option_code,
457 x_funding_method => x_funding_method
458
459 );
460
461 INSERT INTO igf_sl_dl_setup_all(
462 dlset_id,
463 ci_cal_type,
464 ci_sequence_number,
465 school_id,
466 orig_fee_perct_stafford,
467 orig_fee_perct_plus,
468 int_rebate,
469 pnote_print_ind,
470 pnote_print_copies,
471 acc_note_for_disb,
472 affirmation_reqd,
473 interview_reqd,
474 disclosure_print_ind,
475 special_school,
476 dl_version,
477 creation_date,
478 created_by,
479 last_update_date,
480 last_updated_by,
481 last_update_login,
482 org_id,
483 response_option_code,
484 funding_method
485
486 ) VALUES (
487 new_references.dlset_id,
488 new_references.ci_cal_type,
489 new_references.ci_sequence_number,
490 NULL, /* school_id is being obsoleted. Bug # 3102439. FA 126 Multiple FA Offices */
491 new_references.orig_fee_perct_stafford,
492 new_references.orig_fee_perct_plus,
493 new_references.int_rebate,
494 new_references.pnote_print_ind,
495 new_references.pnote_print_copies,
496 new_references.acc_note_for_disb,
497 NULL,
498 new_references.interview_reqd,
499 new_references.disclosure_print_ind,
500 new_references.special_school,
501 new_references.dl_version,
502 x_last_update_date,
503 x_last_updated_by,
504 x_last_update_date,
505 x_last_updated_by,
506 x_last_update_login,
507 l_org_id ,
508 new_references.response_option_code,
509 new_references.funding_method
510
511 );
512
513 OPEN c;
514 FETCH c INTO x_rowid;
515 IF (c%NOTFOUND) THEN
516 CLOSE c;
517 RAISE NO_DATA_FOUND;
518 END IF;
519 CLOSE c;
520
521 END insert_row;
522
523
524 PROCEDURE lock_row (
525 x_rowid IN VARCHAR2,
526 x_dlset_id IN NUMBER,
527 x_ci_cal_type IN VARCHAR2,
531 x_orig_fee_perct_plus IN NUMBER,
528 x_ci_sequence_number IN NUMBER,
529 x_school_id IN VARCHAR2 DEFAULT NULL,
530 x_orig_fee_perct_stafford IN NUMBER,
532 x_int_rebate IN NUMBER,
533 x_pnote_print_ind IN VARCHAR2,
534 x_pnote_print_copies IN NUMBER,
535 x_acc_note_for_disb IN VARCHAR2,
536 x_affirmation_reqd IN VARCHAR2 DEFAULT NULL,
537 x_interview_reqd IN VARCHAR2,
538 x_disclosure_print_ind IN VARCHAR2,
539 x_special_school IN VARCHAR2,
540 x_dl_version IN VARCHAR2,
541 x_response_option_code IN VARCHAR2,
542 x_funding_method IN VARCHAR2
543
544
545 ) AS
546 /*
547 || Created By : venagara
548 || Created On : 31-JAN-2001
549 || Purpose : Handles the LOCK mechanism for the table.
550 || Known limitations, enhancements or remarks :
551 || Change History :
552 || Who When What
553 || ugummall 15-OCT-2003 Bug # 3102439. FA 126 Multiple FA Offices
554 || school_id is being obsoleted.
555 || masehgal 16-Jun-2003 # 2990040 FACR115
556 || Changes to lock row for Dl_version
557 || (reverse chronological order - newest change first)
558 */
559 CURSOR c1 IS
560 SELECT
561 ci_cal_type,
562 ci_sequence_number,
563 orig_fee_perct_stafford,
564 orig_fee_perct_plus,
565 int_rebate,
566 pnote_print_ind,
567 pnote_print_copies,
568 acc_note_for_disb,
569 interview_reqd,
570 disclosure_print_ind,
571 special_school,
572 dl_version,
573 org_id,
574 response_option_code,
575 funding_method
576
577 FROM igf_sl_dl_setup_all
578 WHERE rowid = x_rowid
579 FOR UPDATE NOWAIT;
580
581 tlinfo c1%ROWTYPE;
582
583 BEGIN
584
585 OPEN c1;
586 FETCH c1 INTO tlinfo;
587 IF (c1%notfound) THEN
588 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
589 igs_ge_msg_stack.add;
590 CLOSE c1;
591 app_exception.raise_exception;
592 RETURN;
593 END IF;
594 CLOSE c1;
595
596 IF (
597 (tlinfo.ci_cal_type = x_ci_cal_type)
598 AND (tlinfo.ci_sequence_number = x_ci_sequence_number)
599 AND (tlinfo.orig_fee_perct_stafford = x_orig_fee_perct_stafford)
600 AND (tlinfo.orig_fee_perct_plus = x_orig_fee_perct_plus)
601 AND (tlinfo.int_rebate = x_int_rebate)
602 AND (tlinfo.pnote_print_ind = x_pnote_print_ind)
603 AND (tlinfo.pnote_print_copies = x_pnote_print_copies)
604 AND ((tlinfo.acc_note_for_disb = x_acc_note_for_disb) OR ((tlinfo.acc_note_for_disb IS NULL) AND (X_acc_note_for_disb IS NULL)))
605 AND ((tlinfo.interview_reqd = x_interview_reqd) OR ((tlinfo.interview_reqd IS NULL) AND (X_interview_reqd IS NULL)))
606 AND ((tlinfo.disclosure_print_ind = x_disclosure_print_ind) OR ((tlinfo.disclosure_print_ind IS NULL) AND (X_disclosure_print_ind IS NULL)))
607 AND ((tlinfo.special_school = x_special_school) OR ((tlinfo.special_school IS NULL) AND (X_special_school IS NULL)))
608 AND ((tlinfo.dl_version = x_dl_version) OR ((tlinfo.dl_version IS NULL) AND (X_dl_version IS NULL)))
609 AND ((tlinfo.response_option_code = x_response_option_code) OR ((tlinfo.response_option_code IS NULL) AND (X_response_option_code IS NULL)))
610 AND ((tlinfo.funding_method = x_funding_method) OR ((tlinfo.funding_method IS NULL) AND (X_funding_method IS NULL)))
611
612 ) THEN
613 NULL;
614 ELSE
615 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
616 igs_ge_msg_stack.add;
617 app_exception.raise_exception;
618 END IF;
619
620 RETURN;
621
622 END lock_row;
623
624
625 PROCEDURE update_row (
626 x_rowid IN VARCHAR2,
627 x_dlset_id IN NUMBER,
628 x_ci_cal_type IN VARCHAR2,
629 x_ci_sequence_number IN NUMBER,
630 x_school_id IN VARCHAR2 DEFAULT NULL,
631 x_orig_fee_perct_stafford IN NUMBER,
632 x_orig_fee_perct_plus IN NUMBER,
633 x_int_rebate IN NUMBER,
634 x_pnote_print_ind IN VARCHAR2,
635 x_pnote_print_copies IN NUMBER,
636 x_acc_note_for_disb IN VARCHAR2,
637 x_affirmation_reqd IN VARCHAR2 DEFAULT NULL,
638 x_interview_reqd IN VARCHAR2,
639 x_disclosure_print_ind IN VARCHAR2,
640 x_special_school IN VARCHAR2,
641 x_dl_version IN VARCHAR2,
642 x_mode IN VARCHAR2 DEFAULT 'R',
643 x_response_option_code IN VARCHAR2,
647 /*
644 x_funding_method IN VARCHAR2
645
646 ) AS
648 || Created By : venagara
649 || Created On : 31-JAN-2001
650 || Purpose : Handles the UPDATE DML logic for the table.
651 || Known limitations, enhancements or remarks :
652 || Change History :
653 || Who When What
654 || ugummall 15-OCT-2003 Bug # 3102439. FA 126 Multiple FA Offices
655 || school_id is being obsoleted.
656 || (reverse chronological order - newest change first)
657 */
658 x_last_update_date DATE ;
659 x_last_updated_by NUMBER;
660 x_last_update_login NUMBER;
661
662 BEGIN
663
664 x_last_update_date := SYSDATE;
665 IF (X_MODE = 'I') THEN
666 x_last_updated_by := 1;
667 x_last_update_login := 0;
668 ELSIF (x_mode = 'R') THEN
669 x_last_updated_by := fnd_global.user_id;
670 IF x_last_updated_by IS NULL THEN
671 x_last_updated_by := -1;
672 END IF;
673 x_last_update_login := fnd_global.login_id;
674 IF (x_last_update_login IS NULL) THEN
675 x_last_update_login := -1;
676 END IF;
677 ELSE
678 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
679 igs_ge_msg_stack.add;
680 app_exception.raise_exception;
681 END IF;
682
683 before_dml(
684 p_action => 'UPDATE',
685 x_rowid => x_rowid,
686 x_dlset_id => x_dlset_id,
687 x_ci_cal_type => x_ci_cal_type,
688 x_ci_sequence_number => x_ci_sequence_number,
689 x_school_id => NULL,
690 x_orig_fee_perct_stafford => x_orig_fee_perct_stafford,
691 x_orig_fee_perct_plus => x_orig_fee_perct_plus,
692 x_int_rebate => x_int_rebate,
693 x_pnote_print_ind => x_pnote_print_ind,
694 x_pnote_print_copies => x_pnote_print_copies,
695 x_acc_note_for_disb => x_acc_note_for_disb,
696 x_affirmation_reqd => NULL,
697 x_interview_reqd => x_interview_reqd,
698 x_disclosure_print_ind => x_disclosure_print_ind,
699 x_special_school => x_special_school,
700 x_dl_version => x_dl_version,
701 x_creation_date => x_last_update_date,
702 x_created_by => x_last_updated_by,
703 x_last_update_date => x_last_update_date,
704 x_last_updated_by => x_last_updated_by,
705 x_last_update_login => x_last_update_login,
706 x_response_option_code => x_response_option_code,
707 x_funding_method => x_funding_method
708
709 );
710
711 UPDATE igf_sl_dl_setup_all
712 SET
713 ci_cal_type = new_references.ci_cal_type,
714 ci_sequence_number = new_references.ci_sequence_number,
715 school_id = NULL,
716 orig_fee_perct_stafford = new_references.orig_fee_perct_stafford,
717 orig_fee_perct_plus = new_references.orig_fee_perct_plus,
718 int_rebate = new_references.int_rebate,
719 pnote_print_ind = new_references.pnote_print_ind,
720 pnote_print_copies = new_references.pnote_print_copies,
721 acc_note_for_disb = new_references.acc_note_for_disb,
722 affirmation_reqd = NULL,
723 interview_reqd = new_references.interview_reqd,
724 disclosure_print_ind = new_references.disclosure_print_ind,
725 special_school = new_references.special_school,
726 dl_version = new_references.dl_version,
727 last_update_date = x_last_update_date,
728 last_updated_by = x_last_updated_by,
729 last_update_login = x_last_update_login,
730 response_option_code = new_references.response_option_code,
731 funding_method = new_references.funding_method
732
733
734 WHERE rowid = x_rowid;
735
736 IF (SQL%NOTFOUND) THEN
737 RAISE NO_DATA_FOUND;
738 END IF;
739
740 END update_row;
741
742
743 PROCEDURE add_row (
744 x_rowid IN OUT NOCOPY VARCHAR2,
745 x_dlset_id IN OUT NOCOPY NUMBER,
746 x_ci_cal_type IN VARCHAR2,
747 x_ci_sequence_number IN NUMBER,
748 x_school_id IN VARCHAR2 DEFAULT NULL,
749 x_orig_fee_perct_stafford IN NUMBER,
750 x_orig_fee_perct_plus IN NUMBER,
751 x_int_rebate IN NUMBER,
752 x_pnote_print_ind IN VARCHAR2,
753 x_pnote_print_copies IN NUMBER,
754 x_acc_note_for_disb IN VARCHAR2,
755 x_affirmation_reqd IN VARCHAR2 DEFAULT NULL,
756 x_interview_reqd IN VARCHAR2,
757 x_disclosure_print_ind IN VARCHAR2,
758 x_special_school IN VARCHAR2,
759 x_dl_version IN VARCHAR2,
760 x_mode IN VARCHAR2 DEFAULT 'R',
761 x_response_option_code IN VARCHAR2,
762 x_funding_method IN VARCHAR2
763
764 ) AS
765 /*
766 || Created By : venagara
767 || Created On : 31-JAN-2001
768 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
769 || Known limitations, enhancements or remarks :
770 || Change History :
771 || Who When What
772 || ugummall 15-OCT-2003 Bug # 3102439. FA 126 Multiple FA Offices
773 || school_id is being obsoleted.
774 || (reverse chronological order - newest change first)
775 */
776 CURSOR c1 IS
777 SELECT rowid
778 FROM igf_sl_dl_setup_all
779 WHERE dlset_id = x_dlset_id;
780
781 BEGIN
782
783 OPEN c1;
784 FETCH c1 INTO x_rowid;
785 IF (c1%NOTFOUND) THEN
786 CLOSE c1;
787
788 insert_row (
789 x_rowid,
790 x_dlset_id,
791 x_ci_cal_type,
792 x_ci_sequence_number,
793 NULL, /* school_id is being obsoleted. Bug # 3102439. FA 126 Multiple FA Offices */
794 x_orig_fee_perct_stafford,
795 x_orig_fee_perct_plus,
796 x_int_rebate,
797 x_pnote_print_ind,
798 x_pnote_print_copies,
799 x_acc_note_for_disb,
800 NULL,
801 x_interview_reqd,
802 x_disclosure_print_ind,
803 x_special_school,
804 x_dl_version,
805 x_mode,
806 x_response_option_code,
807 x_funding_method
808
809 );
810 RETURN;
811 END IF;
812 CLOSE c1;
813
814 update_row (
815 x_rowid,
816 x_dlset_id,
817 x_ci_cal_type,
818 x_ci_sequence_number,
819 NULL, /* school_id is being obsoleted. Bug # 3102439. FA 126 Multiple FA Offices */
820 x_orig_fee_perct_stafford,
821 x_orig_fee_perct_plus,
822 x_int_rebate,
823 x_pnote_print_ind,
824 x_pnote_print_copies,
825 x_acc_note_for_disb,
826 NULL,
827 x_interview_reqd,
828 x_disclosure_print_ind,
829 x_special_school,
830 x_dl_version,
831 x_mode,
832 x_response_option_code,
833 x_funding_method
834
835 );
836
837 END add_row;
838
839
840 PROCEDURE delete_row (
841 x_rowid IN VARCHAR2
842 ) AS
843 /*
844 || Created By : venagara
845 || Created On : 31-JAN-2001
846 || Purpose : Handles the DELETE DML logic for the table.
847 || Known limitations, enhancements or remarks :
848 || Change History :
849 || Who When What
850 || (reverse chronological order - newest change first)
851 */
852 BEGIN
853
854 before_dml (
855 p_action => 'DELETE',
856 x_rowid => x_rowid
857 );
858
859 DELETE FROM igf_sl_dl_setup_all
860 WHERE rowid = x_rowid;
861
862 IF (SQL%NOTFOUND) THEN
863 RAISE NO_DATA_FOUND;
864 END IF;
865
866 END delete_row;
867
868
869 END igf_sl_dl_setup_pkg;