[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_FISAP_VI_H_PKG
Source
1 PACKAGE BODY igf_aw_fisap_vi_h_pkg AS
2 /* $Header: IGFWI43B.pls 115.5 2002/11/28 14:42:39 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_fisap_vi_h%ROWTYPE;
6 new_references igf_aw_fisap_vi_h%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_fisap_id IN NUMBER DEFAULT NULL,
12 x_category_id IN NUMBER DEFAULT NULL,
13 x_fisap_section IN VARCHAR2 DEFAULT NULL,
14 x_depend_stat IN VARCHAR2 DEFAULT NULL,
15 x_class_standing IN VARCHAR2 DEFAULT NULL,
16 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
17 x_ci_sequence_number IN NUMBER DEFAULT NULL,
18 x_rec_type IN VARCHAR2 DEFAULT NULL,
19 x_minvalue IN NUMBER DEFAULT NULL,
20 x_maxvalue IN NUMBER DEFAULT NULL,
21 x_fed_perkins_std_count IN NUMBER DEFAULT NULL,
22 x_fed_perkins_fund_total IN NUMBER DEFAULT NULL,
23 x_fseog_std_count IN NUMBER DEFAULT NULL,
24 x_fseog_fund_total IN NUMBER DEFAULT NULL,
25 x_fws_std_count IN NUMBER DEFAULT NULL,
26 x_fws_fund_total IN NUMBER DEFAULT NULL,
27 x_unduplicated_std_count IN NUMBER DEFAULT NULL,
28 x_creation_date IN DATE DEFAULT NULL,
29 x_created_by IN NUMBER DEFAULT NULL,
30 x_last_update_date IN DATE DEFAULT NULL,
31 x_last_updated_by IN NUMBER DEFAULT NULL,
32 x_last_update_login IN NUMBER DEFAULT NULL
33 ) AS
34 /*
35 || Created By : ssawhney
36 || Created On : 22-OCT-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 || (reverse chronological order - newest change first)
42 */
43
44 CURSOR cur_old_ref_values IS
45 SELECT *
46 FROM IGF_AW_FISAP_VI_H
47 WHERE rowid = x_rowid;
48
49 BEGIN
50
51 l_rowid := x_rowid;
52
53 -- Code for setting the Old and New Reference Values.
54 -- Populate Old Values.
55 OPEN cur_old_ref_values;
56 FETCH cur_old_ref_values INTO old_references;
57 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
58 CLOSE cur_old_ref_values;
59 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
60 igs_ge_msg_stack.add;
61 app_exception.raise_exception;
62 RETURN;
63 END IF;
64 CLOSE cur_old_ref_values;
65
66 -- Populate New Values.
67 new_references.fisap_id := x_fisap_id;
68 new_references.category_id := x_category_id;
69 new_references.fisap_section := x_fisap_section;
70 new_references.depend_stat := x_depend_stat;
71 new_references.class_standing := x_class_standing;
72 new_references.ci_cal_type := x_ci_cal_type;
73 new_references.ci_sequence_number := x_ci_sequence_number;
74 new_references.rec_type := x_rec_type;
75 new_references.minvalue := x_minvalue;
76 new_references.maxvalue := x_maxvalue;
77 new_references.fed_perkins_std_count := x_fed_perkins_std_count;
78 new_references.fed_perkins_fund_total := x_fed_perkins_fund_total;
79 new_references.fseog_std_count := x_fseog_std_count;
80 new_references.fseog_fund_total := x_fseog_fund_total;
81 new_references.fws_std_count := x_fws_std_count;
82 new_references.fws_fund_total := x_fws_fund_total;
83 new_references.unduplicated_std_count := x_unduplicated_std_count;
84
85 IF (p_action = 'UPDATE') THEN
86 new_references.creation_date := old_references.creation_date;
87 new_references.created_by := old_references.created_by;
88 ELSE
89 new_references.creation_date := x_creation_date;
90 new_references.created_by := x_created_by;
91 END IF;
92
93 new_references.last_update_date := x_last_update_date;
94 new_references.last_updated_by := x_last_updated_by;
95 new_references.last_update_login := x_last_update_login;
96
97 END set_column_values;
98
99
100 PROCEDURE check_parent_existance AS
101 /*
102 || Created By : ssawhney
103 || Created On : 22-OCT-2001
104 || Purpose : Checks for the existance of Parent records.
105 || Known limitations, enhancements or remarks :
106 || Change History :
107 || Who When What
108 || (reverse chronological order - newest change first)
109 */
110 BEGIN
111
112 IF (((old_references.category_id = new_references.category_id)) OR
113 ((new_references.category_id IS NULL))) THEN
114 NULL;
115 ELSIF NOT igf_aw_fisap_repset_pkg.get_pk_for_validation (
116 new_references.category_id
117 ) THEN
118 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
119 igs_ge_msg_stack.add;
120 app_exception.raise_exception;
121 END IF;
122
123 END check_parent_existance;
124
125
126 FUNCTION get_pk_for_validation (
127 x_fisap_id IN NUMBER
128 ) RETURN BOOLEAN AS
129 /*
130 || Created By : ssawhney
131 || Created On : 22-OCT-2001
132 || Purpose : Validates the Primary Key of the table.
133 || Known limitations, enhancements or remarks :
134 || Change History :
135 || Who When What
136 || (reverse chronological order - newest change first)
137 */
138 CURSOR cur_rowid IS
139 SELECT rowid
140 FROM igf_aw_fisap_vi_h
141 WHERE fisap_id = x_fisap_id
142 FOR UPDATE NOWAIT;
143
144 lv_rowid cur_rowid%RowType;
145
146 BEGIN
147
148 OPEN cur_rowid;
149 FETCH cur_rowid INTO lv_rowid;
150 IF (cur_rowid%FOUND) THEN
151 CLOSE cur_rowid;
152 RETURN(TRUE);
153 ELSE
154 CLOSE cur_rowid;
155 RETURN(FALSE);
156 END IF;
157
158 END get_pk_for_validation;
159
160
161 PROCEDURE get_fk_igf_aw_fisap_repset (
162 x_category_id IN NUMBER
163 ) AS
164 /*
165 || Created By : ssawhney
166 || Created On : 22-OCT-2001
167 || Purpose : Validates the Foreign Keys for the table.
168 || Known limitations, enhancements or remarks :
169 || Change History :
170 || Who When What
171 || (reverse chronological order - newest change first)
172 */
173 CURSOR cur_rowid IS
174 SELECT rowid
175 FROM igf_aw_fisap_vi_h
176 WHERE ((category_id = x_category_id));
177
178 lv_rowid cur_rowid%RowType;
179
180 BEGIN
181
182 OPEN cur_rowid;
183 FETCH cur_rowid INTO lv_rowid;
184 IF (cur_rowid%FOUND) THEN
185 CLOSE cur_rowid;
186 fnd_message.set_name ('IGF', 'IGF_AW_FISVIH_FISET_FK');
187 igs_ge_msg_stack.add;
188 app_exception.raise_exception;
189 RETURN;
190 END IF;
191 CLOSE cur_rowid;
192
193 END get_fk_igf_aw_fisap_repset;
194
195
196 PROCEDURE before_dml (
197 p_action IN VARCHAR2,
198 x_rowid IN VARCHAR2 DEFAULT NULL,
199 x_fisap_id IN NUMBER DEFAULT NULL,
200 x_category_id IN NUMBER DEFAULT NULL,
201 x_fisap_section IN VARCHAR2 DEFAULT NULL,
202 x_depend_stat IN VARCHAR2 DEFAULT NULL,
203 x_class_standing IN VARCHAR2 DEFAULT NULL,
204 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
205 x_ci_sequence_number IN NUMBER DEFAULT NULL,
206 x_rec_type IN VARCHAR2 DEFAULT NULL,
207 x_minvalue IN NUMBER DEFAULT NULL,
208 x_maxvalue IN NUMBER DEFAULT NULL,
209 x_fed_perkins_std_count IN NUMBER DEFAULT NULL,
210 x_fed_perkins_fund_total IN NUMBER DEFAULT NULL,
211 x_fseog_std_count IN NUMBER DEFAULT NULL,
212 x_fseog_fund_total IN NUMBER DEFAULT NULL,
213 x_fws_std_count IN NUMBER DEFAULT NULL,
214 x_fws_fund_total IN NUMBER DEFAULT NULL,
215 x_unduplicated_std_count IN NUMBER DEFAULT NULL,
216 x_creation_date IN DATE DEFAULT NULL,
217 x_created_by IN NUMBER DEFAULT NULL,
218 x_last_update_date IN DATE DEFAULT NULL,
219 x_last_updated_by IN NUMBER DEFAULT NULL,
220 x_last_update_login IN NUMBER DEFAULT NULL
221 ) AS
222 /*
223 || Created By : ssawhney
224 || Created On : 22-OCT-2001
225 || Purpose : Initialises the columns, Checks Constraints, Calls the
226 || Trigger Handlers for the table, before any DML operation.
227 || Known limitations, enhancements or remarks :
228 || Change History :
229 || Who When What
230 || (reverse chronological order - newest change first)
231 */
232 BEGIN
233
234 set_column_values (
235 p_action,
236 x_rowid,
237 x_fisap_id,
238 x_category_id,
239 x_fisap_section,
240 x_depend_stat,
241 x_class_standing,
242 x_ci_cal_type,
243 x_ci_sequence_number,
244 x_rec_type,
245 x_minvalue,
246 x_maxvalue,
247 x_fed_perkins_std_count,
248 x_fed_perkins_fund_total,
249 x_fseog_std_count,
250 x_fseog_fund_total,
251 x_fws_std_count,
252 x_fws_fund_total,
253 x_unduplicated_std_count,
254 x_creation_date,
255 x_created_by,
256 x_last_update_date,
257 x_last_updated_by,
258 x_last_update_login
259 );
260
261 IF (p_action = 'INSERT') THEN
262 -- Call all the procedures related to Before Insert.
263 IF ( get_pk_for_validation(
264 new_references.fisap_id
265 )
266 ) THEN
267 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
268 igs_ge_msg_stack.add;
269 app_exception.raise_exception;
270 END IF;
271 check_parent_existance;
272 ELSIF (p_action = 'UPDATE') THEN
273 -- Call all the procedures related to Before Update.
274 check_parent_existance;
275 ELSIF (p_action = 'VALIDATE_INSERT') THEN
276 -- Call all the procedures related to Before Insert.
277 IF ( get_pk_for_validation (
278 new_references.fisap_id
279 )
280 ) THEN
281 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
282 igs_ge_msg_stack.add;
283 app_exception.raise_exception;
284 END IF;
285 END IF;
286
287 END before_dml;
288
289
290 PROCEDURE insert_row (
291 x_rowid IN OUT NOCOPY VARCHAR2,
292 x_fisap_id IN OUT NOCOPY NUMBER,
293 x_category_id IN NUMBER,
294 x_fisap_section IN VARCHAR2,
295 x_depend_stat IN VARCHAR2,
296 x_class_standing IN VARCHAR2,
297 x_ci_cal_type IN VARCHAR2,
298 x_ci_sequence_number IN NUMBER,
299 x_rec_type IN VARCHAR2,
300 x_minvalue IN NUMBER,
301 x_maxvalue IN NUMBER,
302 x_fed_perkins_std_count IN NUMBER,
303 x_fed_perkins_fund_total IN NUMBER,
304 x_fseog_std_count IN NUMBER,
305 x_fseog_fund_total IN NUMBER,
306 x_fws_std_count IN NUMBER,
307 x_fws_fund_total IN NUMBER,
308 x_unduplicated_std_count IN NUMBER,
309 x_mode IN VARCHAR2 DEFAULT 'R'
310 ) AS
311 /*
312 || Created By : ssawhney
313 || Created On : 22-OCT-2001
314 || Purpose : Handles the INSERT DML logic for the table.
315 || Known limitations, enhancements or remarks :
316 || Change History :
317 || Who When What
318 || (reverse chronological order - newest change first)
319 */
320 CURSOR c IS
321 SELECT rowid
322 FROM igf_aw_fisap_vi_h
323 WHERE fisap_id = x_fisap_id;
324
325 x_last_update_date DATE;
326 x_last_updated_by NUMBER;
327 x_last_update_login NUMBER;
328
329 BEGIN
330
331 x_last_update_date := SYSDATE;
332 IF (x_mode = 'I') THEN
333 x_last_updated_by := 1;
334 x_last_update_login := 0;
335 ELSIF (x_mode = 'R') THEN
336 x_last_updated_by := fnd_global.user_id;
337 IF (x_last_updated_by IS NULL) THEN
338 x_last_updated_by := -1;
339 END IF;
340 x_last_update_login := fnd_global.login_id;
341 IF (x_last_update_login IS NULL) THEN
342 x_last_update_login := -1;
343 END IF;
344 ELSE
345 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
346 igs_ge_msg_stack.add;
347 app_exception.raise_exception;
348 END IF;
349
350 SELECT igf_aw_fisap_vi_h_s.NEXTVAL
351 INTO x_fisap_id
352 FROM dual;
353
354 before_dml(
355 p_action => 'INSERT',
356 x_rowid => x_rowid,
357 x_fisap_id => x_fisap_id,
358 x_category_id => x_category_id,
359 x_fisap_section => x_fisap_section,
360 x_depend_stat => x_depend_stat,
361 x_class_standing => x_class_standing,
362 x_ci_cal_type => x_ci_cal_type,
363 x_ci_sequence_number => x_ci_sequence_number,
364 x_rec_type => x_rec_type,
365 x_minvalue => x_minvalue,
366 x_maxvalue => x_maxvalue,
367 x_fed_perkins_std_count => x_fed_perkins_std_count,
368 x_fed_perkins_fund_total => x_fed_perkins_fund_total,
369 x_fseog_std_count => x_fseog_std_count,
370 x_fseog_fund_total => x_fseog_fund_total,
371 x_fws_std_count => x_fws_std_count,
372 x_fws_fund_total => x_fws_fund_total,
373 x_unduplicated_std_count => x_unduplicated_std_count,
374 x_creation_date => x_last_update_date,
375 x_created_by => x_last_updated_by,
379 );
376 x_last_update_date => x_last_update_date,
377 x_last_updated_by => x_last_updated_by,
378 x_last_update_login => x_last_update_login
380
381 INSERT INTO igf_aw_fisap_vi_h (
382 fisap_id,
383 category_id,
384 fisap_section,
385 depend_stat,
386 class_standing,
387 ci_cal_type,
388 ci_sequence_number,
389 rec_type,
390 minvalue,
391 maxvalue,
392 fed_perkins_std_count,
393 fed_perkins_fund_total,
394 fseog_std_count,
395 fseog_fund_total,
396 fws_std_count,
397 fws_fund_total,
398 unduplicated_std_count,
399 creation_date,
400 created_by,
401 last_update_date,
402 last_updated_by,
403 last_update_login
404 ) VALUES (
405 new_references.fisap_id,
406 new_references.category_id,
407 new_references.fisap_section,
408 new_references.depend_stat,
409 new_references.class_standing,
410 new_references.ci_cal_type,
411 new_references.ci_sequence_number,
412 new_references.rec_type,
413 new_references.minvalue,
414 new_references.maxvalue,
415 new_references.fed_perkins_std_count,
416 new_references.fed_perkins_fund_total,
417 new_references.fseog_std_count,
418 new_references.fseog_fund_total,
419 new_references.fws_std_count,
420 new_references.fws_fund_total,
421 new_references.unduplicated_std_count,
422 x_last_update_date,
423 x_last_updated_by,
424 x_last_update_date,
425 x_last_updated_by,
426 x_last_update_login
427 );
428
429 OPEN c;
430 FETCH c INTO x_rowid;
431 IF (c%NOTFOUND) THEN
432 CLOSE c;
433 RAISE NO_DATA_FOUND;
434 END IF;
435 CLOSE c;
436
437 END insert_row;
438
439
440 PROCEDURE lock_row (
441 x_rowid IN VARCHAR2,
442 x_fisap_id IN NUMBER,
443 x_category_id IN NUMBER,
444 x_fisap_section IN VARCHAR2,
445 x_depend_stat IN VARCHAR2,
446 x_class_standing IN VARCHAR2,
447 x_ci_cal_type IN VARCHAR2,
448 x_ci_sequence_number IN NUMBER,
449 x_rec_type IN VARCHAR2,
450 x_minvalue IN NUMBER,
451 x_maxvalue IN NUMBER,
452 x_fed_perkins_std_count IN NUMBER,
453 x_fed_perkins_fund_total IN NUMBER,
454 x_fseog_std_count IN NUMBER,
455 x_fseog_fund_total IN NUMBER,
456 x_fws_std_count IN NUMBER,
457 x_fws_fund_total IN NUMBER,
458 x_unduplicated_std_count IN NUMBER
459 ) AS
460 /*
461 || Created By : ssawhney
462 || Created On : 22-OCT-2001
463 || Purpose : Handles the LOCK mechanism for the table.
464 || Known limitations, enhancements or remarks :
465 || Change History :
466 || Who When What
467 || (reverse chronological order - newest change first)
468 */
469 CURSOR c1 IS
470 SELECT
471 category_id,
472 fisap_section,
473 depend_stat,
474 class_standing,
475 ci_cal_type,
476 ci_sequence_number,
477 rec_type,
478 minvalue,
479 maxvalue,
480 fed_perkins_std_count,
481 fed_perkins_fund_total,
482 fseog_std_count,
483 fseog_fund_total,
484 fws_std_count,
485 fws_fund_total,
486 unduplicated_std_count
487 FROM igf_aw_fisap_vi_h
488 WHERE rowid = x_rowid
489 FOR UPDATE NOWAIT;
490
491 tlinfo c1%ROWTYPE;
492
493 BEGIN
494
495 OPEN c1;
496 FETCH c1 INTO tlinfo;
497 IF (c1%notfound) THEN
498 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
499 igs_ge_msg_stack.add;
500 CLOSE c1;
501 app_exception.raise_exception;
502 RETURN;
503 END IF;
504 CLOSE c1;
505
506 IF (
507 ((tlinfo.category_id = x_category_id) OR ((tlinfo.category_id IS NULL) AND (X_category_id IS NULL)))
508 AND ((tlinfo.fisap_section = x_fisap_section) OR ((tlinfo.fisap_section IS NULL) AND (X_fisap_section IS NULL)))
509 AND ((tlinfo.depend_stat = x_depend_stat) OR ((tlinfo.depend_stat IS NULL) AND (X_depend_stat IS NULL)))
510 AND ((tlinfo.class_standing = x_class_standing) OR ((tlinfo.class_standing IS NULL) AND (X_class_standing IS NULL)))
511 AND (tlinfo.ci_cal_type = x_ci_cal_type)
512 AND (tlinfo.ci_sequence_number = x_ci_sequence_number)
513 AND (tlinfo.rec_type = x_rec_type)
514 AND ((tlinfo.minvalue = x_minvalue) OR ((tlinfo.minvalue IS NULL) AND (X_minvalue IS NULL)))
515 AND ((tlinfo.maxvalue = x_maxvalue) OR ((tlinfo.maxvalue IS NULL) AND (X_maxvalue IS NULL)))
519 AND ((tlinfo.fseog_fund_total = x_fseog_fund_total) OR ((tlinfo.fseog_fund_total IS NULL) AND (X_fseog_fund_total IS NULL)))
516 AND ((tlinfo.fed_perkins_std_count = x_fed_perkins_std_count) OR ((tlinfo.fed_perkins_std_count IS NULL) AND (X_fed_perkins_std_count IS NULL)))
517 AND ((tlinfo.fed_perkins_fund_total = x_fed_perkins_fund_total) OR ((tlinfo.fed_perkins_fund_total IS NULL) AND (X_fed_perkins_fund_total IS NULL)))
518 AND ((tlinfo.fseog_std_count = x_fseog_std_count) OR ((tlinfo.fseog_std_count IS NULL) AND (X_fseog_std_count IS NULL)))
520 AND ((tlinfo.fws_std_count = x_fws_std_count) OR ((tlinfo.fws_std_count IS NULL) AND (X_fws_std_count IS NULL)))
521 AND ((tlinfo.fws_fund_total = x_fws_fund_total) OR ((tlinfo.fws_fund_total IS NULL) AND (X_fws_fund_total IS NULL)))
522 AND ((tlinfo.unduplicated_std_count = x_unduplicated_std_count) OR ((tlinfo.unduplicated_std_count IS NULL) AND (X_unduplicated_std_count IS NULL)))
523 ) THEN
524 NULL;
525 ELSE
526 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
527 igs_ge_msg_stack.add;
528 app_exception.raise_exception;
529 END IF;
530
531 RETURN;
532
533 END lock_row;
534
535
536 PROCEDURE update_row (
537 x_rowid IN VARCHAR2,
538 x_fisap_id IN NUMBER,
539 x_category_id IN NUMBER,
540 x_fisap_section IN VARCHAR2,
541 x_depend_stat IN VARCHAR2,
542 x_class_standing IN VARCHAR2,
543 x_ci_cal_type IN VARCHAR2,
544 x_ci_sequence_number IN NUMBER,
545 x_rec_type IN VARCHAR2,
546 x_minvalue IN NUMBER,
547 x_maxvalue IN NUMBER,
548 x_fed_perkins_std_count IN NUMBER,
549 x_fed_perkins_fund_total IN NUMBER,
550 x_fseog_std_count IN NUMBER,
551 x_fseog_fund_total IN NUMBER,
552 x_fws_std_count IN NUMBER,
553 x_fws_fund_total IN NUMBER,
554 x_unduplicated_std_count IN NUMBER,
555 x_mode IN VARCHAR2 DEFAULT 'R'
556 ) AS
557 /*
558 || Created By : ssawhney
559 || Created On : 22-OCT-2001
560 || Purpose : Handles the UPDATE DML logic for the table.
561 || Known limitations, enhancements or remarks :
562 || Change History :
563 || Who When What
564 || (reverse chronological order - newest change first)
565 */
566 x_last_update_date DATE ;
567 x_last_updated_by NUMBER;
568 x_last_update_login NUMBER;
569
570 BEGIN
571
572 x_last_update_date := SYSDATE;
573 IF (X_MODE = 'I') THEN
574 x_last_updated_by := 1;
575 x_last_update_login := 0;
576 ELSIF (x_mode = 'R') THEN
577 x_last_updated_by := fnd_global.user_id;
578 IF x_last_updated_by IS NULL THEN
579 x_last_updated_by := -1;
580 END IF;
581 x_last_update_login := fnd_global.login_id;
582 IF (x_last_update_login IS NULL) THEN
583 x_last_update_login := -1;
584 END IF;
585 ELSE
586 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
587 igs_ge_msg_stack.add;
588 app_exception.raise_exception;
589 END IF;
590
591 before_dml(
592 p_action => 'UPDATE',
593 x_rowid => x_rowid,
594 x_fisap_id => x_fisap_id,
595 x_category_id => x_category_id,
596 x_fisap_section => x_fisap_section,
597 x_depend_stat => x_depend_stat,
598 x_class_standing => x_class_standing,
599 x_ci_cal_type => x_ci_cal_type,
600 x_ci_sequence_number => x_ci_sequence_number,
601 x_rec_type => x_rec_type,
602 x_minvalue => x_minvalue,
603 x_maxvalue => x_maxvalue,
604 x_fed_perkins_std_count => x_fed_perkins_std_count,
605 x_fed_perkins_fund_total => x_fed_perkins_fund_total,
606 x_fseog_std_count => x_fseog_std_count,
607 x_fseog_fund_total => x_fseog_fund_total,
608 x_fws_std_count => x_fws_std_count,
609 x_fws_fund_total => x_fws_fund_total,
610 x_unduplicated_std_count => x_unduplicated_std_count,
611 x_creation_date => x_last_update_date,
612 x_created_by => x_last_updated_by,
613 x_last_update_date => x_last_update_date,
614 x_last_updated_by => x_last_updated_by,
615 x_last_update_login => x_last_update_login
616 );
617
618 UPDATE igf_aw_fisap_vi_h
619 SET
620 category_id = new_references.category_id,
621 fisap_section = new_references.fisap_section,
622 depend_stat = new_references.depend_stat,
623 class_standing = new_references.class_standing,
627 minvalue = new_references.minvalue,
624 ci_cal_type = new_references.ci_cal_type,
625 ci_sequence_number = new_references.ci_sequence_number,
626 rec_type = new_references.rec_type,
628 maxvalue = new_references.maxvalue,
629 fed_perkins_std_count = new_references.fed_perkins_std_count,
630 fed_perkins_fund_total = new_references.fed_perkins_fund_total,
631 fseog_std_count = new_references.fseog_std_count,
632 fseog_fund_total = new_references.fseog_fund_total,
633 fws_std_count = new_references.fws_std_count,
634 fws_fund_total = new_references.fws_fund_total,
635 unduplicated_std_count = new_references.unduplicated_std_count,
636 last_update_date = x_last_update_date,
637 last_updated_by = x_last_updated_by,
638 last_update_login = x_last_update_login
639 WHERE rowid = x_rowid;
640
641 IF (SQL%NOTFOUND) THEN
642 RAISE NO_DATA_FOUND;
643 END IF;
644
645 END update_row;
646
647
648 PROCEDURE add_row (
649 x_rowid IN OUT NOCOPY VARCHAR2,
650 x_fisap_id IN OUT NOCOPY NUMBER,
651 x_category_id IN NUMBER,
652 x_fisap_section IN VARCHAR2,
653 x_depend_stat IN VARCHAR2,
654 x_class_standing IN VARCHAR2,
655 x_ci_cal_type IN VARCHAR2,
656 x_ci_sequence_number IN NUMBER,
657 x_rec_type IN VARCHAR2,
658 x_minvalue IN NUMBER,
659 x_maxvalue IN NUMBER,
660 x_fed_perkins_std_count IN NUMBER,
661 x_fed_perkins_fund_total IN NUMBER,
662 x_fseog_std_count IN NUMBER,
663 x_fseog_fund_total IN NUMBER,
664 x_fws_std_count IN NUMBER,
665 x_fws_fund_total IN NUMBER,
666 x_unduplicated_std_count IN NUMBER,
667 x_mode IN VARCHAR2 DEFAULT 'R'
668 ) AS
669 /*
670 || Created By : ssawhney
671 || Created On : 22-OCT-2001
672 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
673 || Known limitations, enhancements or remarks :
674 || Change History :
675 || Who When What
676 || (reverse chronological order - newest change first)
677 */
678 CURSOR c1 IS
679 SELECT rowid
680 FROM igf_aw_fisap_vi_h
681 WHERE fisap_id = x_fisap_id;
682
683 BEGIN
684
685 OPEN c1;
686 FETCH c1 INTO x_rowid;
687 IF (c1%NOTFOUND) THEN
688 CLOSE c1;
689
690 insert_row (
691 x_rowid,
692 x_fisap_id,
693 x_category_id,
694 x_fisap_section,
695 x_depend_stat,
696 x_class_standing,
697 x_ci_cal_type,
698 x_ci_sequence_number,
699 x_rec_type,
700 x_minvalue,
701 x_maxvalue,
702 x_fed_perkins_std_count,
703 x_fed_perkins_fund_total,
704 x_fseog_std_count,
705 x_fseog_fund_total,
706 x_fws_std_count,
707 x_fws_fund_total,
708 x_unduplicated_std_count,
709 x_mode
710 );
711 RETURN;
712 END IF;
713 CLOSE c1;
714
715 update_row (
716 x_rowid,
717 x_fisap_id,
718 x_category_id,
719 x_fisap_section,
720 x_depend_stat,
721 x_class_standing,
722 x_ci_cal_type,
723 x_ci_sequence_number,
724 x_rec_type,
725 x_minvalue,
726 x_maxvalue,
727 x_fed_perkins_std_count,
728 x_fed_perkins_fund_total,
729 x_fseog_std_count,
730 x_fseog_fund_total,
731 x_fws_std_count,
732 x_fws_fund_total,
733 x_unduplicated_std_count,
734 x_mode
735 );
736
737 END add_row;
738
739
740 PROCEDURE delete_row (
741 x_rowid IN VARCHAR2
742 ) AS
743 /*
744 || Created By : ssawhney
745 || Created On : 22-OCT-2001
746 || Purpose : Handles the DELETE DML logic for the table.
747 || Known limitations, enhancements or remarks :
748 || Change History :
749 || Who When What
750 || (reverse chronological order - newest change first)
751 */
752 BEGIN
753
754 before_dml (
755 p_action => 'DELETE',
756 x_rowid => x_rowid
757 );
758
759 DELETE FROM igf_aw_fisap_vi_h
760 WHERE rowid = x_rowid;
761
762 IF (SQL%NOTFOUND) THEN
763 RAISE NO_DATA_FOUND;
764 END IF;
765
766 END delete_row;
767
768
769 END igf_aw_fisap_vi_h_pkg;