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