[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_RFMS_PKG
Source
1 PACKAGE BODY igf_gr_rfms_pkg AS
2 /* $Header: IGFGI05B.pls 120.0 2005/06/01 14:27:31 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_gr_rfms_all%ROWTYPE;
6 new_references igf_gr_rfms_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_origination_id IN VARCHAR2,
12 x_ci_cal_type IN VARCHAR2,
13 x_ci_sequence_number IN NUMBER ,
14 x_base_id IN NUMBER ,
15 x_award_id IN NUMBER ,
16 x_rfmb_id IN NUMBER ,
17 x_sys_orig_ssn IN VARCHAR2,
18 x_sys_orig_name_cd IN VARCHAR2,
19 x_transaction_num IN VARCHAR2,
20 x_efc IN NUMBER ,
21 x_ver_status_code IN VARCHAR2,
22 x_secondary_efc IN NUMBER ,
23 x_secondary_efc_cd IN VARCHAR2,
24 x_pell_amount IN NUMBER ,
25 x_enrollment_status IN VARCHAR2,
26 x_enrollment_dt IN DATE ,
27 x_coa_amount IN NUMBER ,
28 x_academic_calendar IN VARCHAR2,
29 x_payment_method IN VARCHAR2,
30 x_total_pymt_prds IN NUMBER ,
31 x_incrcd_fed_pell_rcp_cd IN VARCHAR2,
32 x_attending_campus_id IN VARCHAR2,
33 x_est_disb_dt1 IN DATE ,
34 x_orig_action_code IN VARCHAR2,
35 x_orig_status_dt IN DATE ,
36 x_orig_ed_use_flags IN VARCHAR2,
37 x_ft_pell_amount IN NUMBER ,
38 x_prev_accpt_efc IN NUMBER ,
39 x_prev_accpt_tran_no IN VARCHAR2,
40 x_prev_accpt_sec_efc_cd IN VARCHAR2,
41 x_prev_accpt_coa IN NUMBER ,
42 x_orig_reject_code IN VARCHAR2,
43 x_wk_inst_time_calc_pymt IN NUMBER ,
44 x_wk_int_time_prg_def_yr IN NUMBER ,
45 x_cr_clk_hrs_prds_sch_yr IN NUMBER ,
46 x_cr_clk_hrs_acad_yr IN NUMBER ,
47 x_inst_cross_ref_cd IN VARCHAR2,
48 x_low_tution_fee IN VARCHAR2,
49 x_rec_source IN VARCHAR2,
50 x_pending_amount IN NUMBER,
51 x_creation_date IN DATE ,
52 x_created_by IN NUMBER ,
53 x_last_update_date IN DATE ,
54 x_last_updated_by IN NUMBER ,
55 x_last_update_login IN NUMBER ,
56 x_birth_dt IN DATE ,
57 x_last_name IN VARCHAR2,
58 x_first_name IN VARCHAR2,
59 x_middle_name IN VARCHAR2,
60 x_current_ssn IN VARCHAR2,
61 x_legacy_record_flag IN VARCHAR2 DEFAULT NULL,
62 x_reporting_pell_cd IN VARCHAR2 DEFAULT NULL,
63 x_rep_entity_id_txt IN VARCHAR2,
64 x_atd_entity_id_txt IN VARCHAR2,
65 x_note_message IN VARCHAR2,
66 x_full_resp_code IN VARCHAR2,
67 x_document_id_txt IN VARCHAR2
68
69 ) AS
70 /*
71 || Created By : adhawan
72 || Created On : 15-JAN-2001
73 || Purpose : Initialises the Old and New references for the columns of the table.
74 || Known limitations, enhancements or remarks :
75 || Change History :
76 || Who When What
77 || (reverse chronological order - newest change first)
78 */
79
80 CURSOR cur_old_ref_values IS
81 SELECT *
82 FROM IGF_GR_RFMS_ALL
83 WHERE rowid = x_rowid;
84
85 BEGIN
86
87 l_rowid := x_rowid;
88
89 -- Code for setting the Old and New Reference Values.
90 -- Populate Old Values.
91 OPEN cur_old_ref_values;
92 FETCH cur_old_ref_values INTO old_references;
93 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
94 CLOSE cur_old_ref_values;
95 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
96 igs_ge_msg_stack.add;
97 app_exception.raise_exception;
98 RETURN;
99 END IF;
100 CLOSE cur_old_ref_values;
101
102 -- Populate New Values.
103 new_references.origination_id := x_origination_id;
104 new_references.ci_cal_type := x_ci_cal_type;
105 new_references.ci_sequence_number := x_ci_sequence_number;
106 new_references.base_id := x_base_id;
107 new_references.award_id := x_award_id;
108 new_references.rfmb_id := x_rfmb_id;
109 new_references.sys_orig_ssn := x_sys_orig_ssn;
110 new_references.sys_orig_name_cd := x_sys_orig_name_cd;
111 new_references.transaction_num := x_transaction_num;
112 new_references.efc := x_efc;
113 new_references.ver_status_code := x_ver_status_code;
114 new_references.secondary_efc := x_secondary_efc;
115 new_references.secondary_efc_cd := x_secondary_efc_cd;
116 new_references.pell_amount := x_pell_amount;
117 new_references.enrollment_status := x_enrollment_status;
118 new_references.enrollment_dt := x_enrollment_dt;
119 new_references.coa_amount := x_coa_amount;
120 new_references.academic_calendar := x_academic_calendar;
121 new_references.payment_method := x_payment_method;
122 new_references.total_pymt_prds := x_total_pymt_prds;
123 new_references.incrcd_fed_pell_rcp_cd := x_incrcd_fed_pell_rcp_cd;
124 new_references.attending_campus_id := x_attending_campus_id;
125 new_references.est_disb_dt1 := x_est_disb_dt1;
126 new_references.orig_action_code := x_orig_action_code;
127 new_references.orig_status_dt := x_orig_status_dt;
128 new_references.orig_ed_use_flags := x_orig_ed_use_flags;
129 new_references.ft_pell_amount := x_ft_pell_amount;
130 new_references.prev_accpt_efc := x_prev_accpt_efc;
131 new_references.prev_accpt_tran_no := x_prev_accpt_tran_no;
132 new_references.prev_accpt_sec_efc_cd := x_prev_accpt_sec_efc_cd;
133 new_references.prev_accpt_coa := x_prev_accpt_coa;
134 new_references.orig_reject_code := x_orig_reject_code;
135 new_references.wk_inst_time_calc_pymt := x_wk_inst_time_calc_pymt;
136 new_references.wk_int_time_prg_def_yr := x_wk_int_time_prg_def_yr;
137 new_references.cr_clk_hrs_prds_sch_yr := x_cr_clk_hrs_prds_sch_yr;
138 new_references.cr_clk_hrs_acad_yr := x_cr_clk_hrs_acad_yr;
139 new_references.inst_cross_ref_cd := x_inst_cross_ref_cd;
140 new_references.low_tution_fee := x_low_tution_fee;
141 new_references.rec_source := x_rec_source;
142 new_references.pending_amount := x_pending_amount;
143
144 IF (p_action = 'UPDATE') THEN
145 new_references.creation_date := old_references.creation_date;
146 new_references.created_by := old_references.created_by;
147 ELSE
148 new_references.creation_date := x_creation_date;
149 new_references.created_by := x_created_by;
150 END IF;
151
152 new_references.last_update_date := x_last_update_date;
153 new_references.last_updated_by := x_last_updated_by;
154 new_references.last_update_login := x_last_update_login;
155 new_references.birth_dt := x_birth_dt;
156 new_references.last_name := x_last_name;
157 new_references.first_name := x_first_name;
158 new_references.middle_name := x_middle_name;
159 new_references.current_ssn := x_current_ssn;
160 new_references.legacy_record_flag := x_legacy_record_flag;
161 new_references.reporting_pell_cd := x_reporting_pell_cd;
162 new_references.rep_entity_id_txt := x_rep_entity_id_txt;
163 new_references.atd_entity_id_txt := x_atd_entity_id_txt;
164 new_references.note_message := x_note_message;
165 new_references.full_resp_code := x_full_resp_code;
166 new_references.document_id_txt := x_document_id_txt;
167
168
169 END set_column_values;
170
171
172 PROCEDURE check_parent_existance AS
173 /*
174 || Created By : adhawan
175 || Created On : 15-JAN-2001
176 || Purpose : Checks for the existance of Parent records.
177 || Known limitations, enhancements or remarks :
178 || Change History :
179 || Who When What
180 || brajendr 21-Jul-2003 Added the check pk to awards table
181 || (reverse chronological order - newest change first)
182 */
183 BEGIN
184
185 IF (((old_references.rfmb_id = new_references.rfmb_id)) OR
186 ((new_references.rfmb_id IS NULL))) THEN
187 NULL;
188 ELSIF NOT igf_gr_rfms_batch_pkg.get_pk_for_validation (
189 new_references.rfmb_id
190 ) THEN
191 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
192 igs_ge_msg_stack.add;
193 app_exception.raise_exception;
194 END IF;
195
196 IF (((old_references.ci_cal_type = new_references.ci_cal_type) AND
197 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
198 ((new_references.ci_cal_type IS NULL) OR
199 (new_references.ci_sequence_number IS NULL))) THEN
200 NULL;
201 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
202 new_references.ci_cal_type,
203 new_references.ci_sequence_number
204 ) THEN
205 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
206 igs_ge_msg_stack.add;
207 app_exception.raise_exception;
208 END IF;
209
210 -- check for parent key existance for Awards table
211 IF (((old_references.award_id = new_references.award_id)) OR
212 ((new_references.award_id IS NULL))) THEN
213 NULL;
214 ELSIF NOT igf_aw_award_pkg.get_pk_for_validation (
215 new_references.award_id
216 ) THEN
217 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
218 igs_ge_msg_stack.add;
219 app_exception.raise_exception;
220 END IF;
221
222 -- check for parent key existance for FA Base Rec table
223 IF (((old_references.base_id = new_references.base_id)) OR
224 ((new_references.base_id IS NULL))) THEN
225 NULL;
226 ELSIF NOT igf_ap_fa_base_rec_pkg.get_pk_for_validation (
227 new_references.base_id
228 ) THEN
229 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
230 igs_ge_msg_stack.add;
231 app_exception.raise_exception;
232 END IF;
233
234 END check_parent_existance;
235
236
237 PROCEDURE check_child_existance IS
238 /*
239 || Created By : adhawan
240 || Created On : 15-JAN-2001
241 || Purpose : Checks for the existance of Child records.
242 || Known limitations, enhancements or remarks :
243 || Change History :
244 || Who When What
245 || (reverse chronological order - newest change first)
246 */
247 BEGIN
248
249 igf_gr_rfms_disb_pkg.get_fk_igf_gr_rfms (
250 old_references.origination_id
251 );
252
253 END check_child_existance;
254
255
256 FUNCTION get_pk_for_validation (
257 x_origination_id IN VARCHAR2
258 ) RETURN BOOLEAN AS
259 /*
260 || Created By : adhawan
261 || Created On : 15-JAN-2001
262 || Purpose : Validates the Primary Key of the table.
263 || Known limitations, enhancements or remarks :
264 || Change History :
265 || Who When What
266 || (reverse chronological order - newest change first)
267 */
268 CURSOR cur_rowid IS
269 SELECT rowid
270 FROM igf_gr_rfms_all
271 WHERE origination_id = x_origination_id
272 FOR UPDATE NOWAIT;
273
274 lv_rowid cur_rowid%RowType;
275
276 BEGIN
277
278 OPEN cur_rowid;
279 FETCH cur_rowid INTO lv_rowid;
280 IF (cur_rowid%FOUND) THEN
281 CLOSE cur_rowid;
282 RETURN(TRUE);
283 ELSE
284 CLOSE cur_rowid;
285 RETURN(FALSE);
286 END IF;
287
288 END get_pk_for_validation;
289
290
291 PROCEDURE get_fk_igf_gr_rfms_batch (
292 x_rfmb_id IN NUMBER
293 ) AS
294 /*
295 || Created By : adhawan
296 || Created On : 15-JAN-2001
297 || Purpose : Validates the Foreign Keys for the table.
298 || Known limitations, enhancements or remarks :
299 || Change History :
300 || Who When What
301 || (reverse chronological order - newest change first)
302 */
303 CURSOR cur_rowid IS
304 SELECT rowid
305 FROM igf_gr_rfms_all
306 WHERE ((rfmb_id = x_rfmb_id));
307
308 lv_rowid cur_rowid%RowType;
309
310 BEGIN
311
312 OPEN cur_rowid;
313 FETCH cur_rowid INTO lv_rowid;
314 IF (cur_rowid%FOUND) THEN
315 CLOSE cur_rowid;
316 fnd_message.set_name ('IGF', 'IGF_GR_RFMS_RFMB_FK');
317 igs_ge_msg_stack.add;
318 app_exception.raise_exception;
319 RETURN;
320 END IF;
321 CLOSE cur_rowid;
322
323 END get_fk_igf_gr_rfms_batch;
324
325
326 PROCEDURE get_fk_igs_ca_inst (
327 x_cal_type IN VARCHAR2,
328 x_sequence_number IN NUMBER
329 ) AS
330 /*
331 || Created By : adhawan
332 || Created On : 15-JAN-2001
333 || Purpose : Validates the Foreign Keys for the table.
334 || Known limitations, enhancements or remarks :
335 || Change History :
336 || Who When What
337 || (reverse chronological order - newest change first)
338 */
339 CURSOR cur_rowid IS
340 SELECT rowid
341 FROM igf_gr_rfms_all
342 WHERE ((ci_cal_type = x_cal_type) AND
343 (ci_sequence_number = x_sequence_number));
344
345 lv_rowid cur_rowid%RowType;
346
347 BEGIN
348
349 OPEN cur_rowid;
350 FETCH cur_rowid INTO lv_rowid;
351 IF (cur_rowid%FOUND) THEN
352 CLOSE cur_rowid;
353 fnd_message.set_name ('IGF', 'IGF_GR_RFMS_CI_FK');
354 igs_ge_msg_stack.add;
355 app_exception.raise_exception;
356 RETURN;
357 END IF;
358 CLOSE cur_rowid;
359
360 END get_fk_igs_ca_inst;
361
362
363 PROCEDURE get_fk_igf_aw_award (
364 x_award_id IN NUMBER
365 ) AS
366 /*
367 || Created By : brajendr
368 || Created On : 21-Jul-2003
369 || Purpose : Validates the Foreign Keys for the table.
370 || Known limitations, enhancements or remarks :
371 || Change History :
372 || Who When What
373 || (reverse chronological order - newest change first)
374 */
375 CURSOR cur_rowid IS
376 SELECT rowid
377 FROM igf_gr_rfms_all
378 WHERE ((award_id = x_award_id));
379
380 lv_rowid cur_rowid%RowType;
381
382 BEGIN
383
384 OPEN cur_rowid;
385 FETCH cur_rowid INTO lv_rowid;
386 IF (cur_rowid%FOUND) THEN
387 CLOSE cur_rowid;
388 fnd_message.set_name ('IGF', 'IGF_GR_RFMS_AWD_FK');
389 igs_ge_msg_stack.add;
390 app_exception.raise_exception;
391 RETURN;
392 END IF;
393 CLOSE cur_rowid;
394
395 END get_fk_igf_aw_award;
396
397
398 PROCEDURE get_fk_igf_ap_fa_base_rec (
399 x_base_id IN NUMBER
400 ) AS
401 /*
402 || Created By : brajendr
403 || Created On : 21-Jul-2003
404 || Purpose : Validates the Foreign Keys for the table.
405 || Known limitations, enhancements or remarks :
406 || Change History :
407 || Who When What
408 || (reverse chronological order - newest change first)
409 */
410 CURSOR cur_rowid IS
411 SELECT rowid
412 FROM igf_gr_rfms_all
413 WHERE ((base_id = x_base_id));
414
415 lv_rowid cur_rowid%RowType;
416
417 BEGIN
418
419 OPEN cur_rowid;
420 FETCH cur_rowid INTO lv_rowid;
421 IF (cur_rowid%FOUND) THEN
422 CLOSE cur_rowid;
423 fnd_message.set_name ('IGF', 'IGF_GR_RFMS_FA_DETAIL_FK');
424 igs_ge_msg_stack.add;
425 app_exception.raise_exception;
426 RETURN;
427 END IF;
428 CLOSE cur_rowid;
429
430 END get_fk_igf_ap_fa_base_rec;
431
432
433 PROCEDURE before_dml (
434 p_action IN VARCHAR2,
435 x_rowid IN VARCHAR2,
436 x_origination_id IN VARCHAR2,
437 x_ci_cal_type IN VARCHAR2,
438 x_ci_sequence_number IN NUMBER ,
439 x_base_id IN NUMBER ,
440 x_award_id IN NUMBER ,
441 x_rfmb_id IN NUMBER ,
442 x_sys_orig_ssn IN VARCHAR2,
443 x_sys_orig_name_cd IN VARCHAR2,
444 x_transaction_num IN VARCHAR2,
445 x_efc IN NUMBER ,
446 x_ver_status_code IN VARCHAR2,
447 x_secondary_efc IN NUMBER ,
448 x_secondary_efc_cd IN VARCHAR2,
449 x_pell_amount IN NUMBER ,
450 x_pell_profile IN VARCHAR2,
451 x_enrollment_status IN VARCHAR2,
452 x_enrollment_dt IN DATE ,
453 x_coa_amount IN NUMBER ,
454 x_academic_calendar IN VARCHAR2,
455 x_payment_method IN VARCHAR2,
456 x_total_pymt_prds IN NUMBER ,
457 x_incrcd_fed_pell_rcp_cd IN VARCHAR2,
458 x_attending_campus_id IN VARCHAR2,
459 x_est_disb_dt1 IN DATE ,
460 x_orig_action_code IN VARCHAR2,
461 x_orig_status_dt IN DATE ,
462 x_orig_ed_use_flags IN VARCHAR2,
463 x_ft_pell_amount IN NUMBER ,
464 x_prev_accpt_efc IN NUMBER ,
465 x_prev_accpt_tran_no IN VARCHAR2,
466 x_prev_accpt_sec_efc_cd IN VARCHAR2,
467 x_prev_accpt_coa IN NUMBER ,
468 x_orig_reject_code IN VARCHAR2,
469 x_wk_inst_time_calc_pymt IN NUMBER ,
470 x_wk_int_time_prg_def_yr IN NUMBER ,
471 x_cr_clk_hrs_prds_sch_yr IN NUMBER ,
472 x_cr_clk_hrs_acad_yr IN NUMBER ,
473 x_inst_cross_ref_cd IN VARCHAR2,
474 x_low_tution_fee IN VARCHAR2,
475 x_rec_source IN VARCHAR2,
476 x_pending_amount IN NUMBER,
477 x_creation_date IN DATE ,
478 x_created_by IN NUMBER ,
479 x_last_update_date IN DATE ,
480 x_last_updated_by IN NUMBER ,
481 x_last_update_login IN NUMBER ,
482 x_birth_dt IN DATE ,
483 x_last_name IN VARCHAR2,
484 x_first_name IN VARCHAR2,
485 x_middle_name IN VARCHAR2,
486 x_current_ssn IN VARCHAR2,
487 x_legacy_record_flag IN VARCHAR2,
488 x_reporting_pell_cd IN VARCHAR2,
489 x_rep_entity_id_txt IN VARCHAR2,
490 x_atd_entity_id_txt IN VARCHAR2,
491 x_note_message IN VARCHAR2,
492 x_full_resp_code IN VARCHAR2,
493 x_document_id_txt IN VARCHAR2
494
495
496 ) AS
497 /*
498 || Created By : adhawan
499 || Created On : 15-JAN-2001
500 || Purpose : Initialises the columns, Checks Constraints, Calls the
501 || Trigger Handlers for the table, before any DML operation.
502 || Known limitations, enhancements or remarks :
503 || Change History :
504 || Who When What
505 || (reverse chronological order - newest change first)
506 */
507 BEGIN
508
509 set_column_values (
510 p_action,
511 x_rowid,
512 x_origination_id,
513 x_ci_cal_type,
514 x_ci_sequence_number,
515 x_base_id,
516 x_award_id,
517 x_rfmb_id,
518 x_sys_orig_ssn,
519 x_sys_orig_name_cd,
520 x_transaction_num,
521 x_efc,
522 x_ver_status_code,
523 x_secondary_efc,
524 x_secondary_efc_cd,
525 x_pell_amount,
526 x_enrollment_status,
527 x_enrollment_dt,
528 x_coa_amount,
529 x_academic_calendar,
530 x_payment_method,
531 x_total_pymt_prds,
532 x_incrcd_fed_pell_rcp_cd,
533 x_attending_campus_id,
534 x_est_disb_dt1,
535 x_orig_action_code,
536 x_orig_status_dt,
537 x_orig_ed_use_flags,
538 x_ft_pell_amount,
539 x_prev_accpt_efc,
540 x_prev_accpt_tran_no,
541 x_prev_accpt_sec_efc_cd,
542 x_prev_accpt_coa,
543 x_orig_reject_code,
544 x_wk_inst_time_calc_pymt,
545 x_wk_int_time_prg_def_yr,
546 x_cr_clk_hrs_prds_sch_yr,
547 x_cr_clk_hrs_acad_yr,
548 x_inst_cross_ref_cd,
549 x_low_tution_fee,
550 x_rec_source,
551 x_pending_amount,
552 x_creation_date,
553 x_created_by,
554 x_last_update_date,
555 x_last_updated_by,
556 x_last_update_login,
557 x_birth_dt,
558 x_last_name,
559 x_first_name,
560 x_middle_name,
561 x_current_ssn,
562 x_legacy_record_flag,
563 x_reporting_pell_cd,
564 x_rep_entity_id_txt,
565 x_atd_entity_id_txt,
566 x_note_message,
567 x_full_resp_code,
568 x_document_id_txt
569
570 );
571
572 IF (p_action = 'INSERT') THEN
573 -- Call all the procedures related to Before Insert.
574 IF ( get_pk_for_validation(
575 new_references.origination_id
576 )
577 ) THEN
578 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
579 igs_ge_msg_stack.add;
580 app_exception.raise_exception;
581 END IF;
582 check_parent_existance;
583 ELSIF (p_action = 'UPDATE') THEN
584 -- Call all the procedures related to Before Update.
585 check_parent_existance;
586 ELSIF (p_action = 'DELETE') THEN
587 -- Call all the procedures related to Before Delete.
588 check_child_existance;
589 ELSIF (p_action = 'VALIDATE_INSERT') THEN
590 -- Call all the procedures related to Before Insert.
591 IF ( get_pk_for_validation (
592 new_references.origination_id
593 )
594 ) THEN
595 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
596 igs_ge_msg_stack.add;
597 app_exception.raise_exception;
598 END IF;
599 ELSIF (p_action = 'VALIDATE_DELETE') THEN
600 check_child_existance;
601 END IF;
602
603 END before_dml;
604
605
606 PROCEDURE insert_row (
607 x_rowid IN OUT NOCOPY VARCHAR2,
608 x_origination_id IN VARCHAR2,
609 x_ci_cal_type IN VARCHAR2,
610 x_ci_sequence_number IN NUMBER,
611 x_base_id IN NUMBER,
612 x_award_id IN NUMBER,
613 x_rfmb_id IN NUMBER,
614 x_sys_orig_ssn IN VARCHAR2,
615 x_sys_orig_name_cd IN VARCHAR2,
616 x_transaction_num IN VARCHAR2,
617 x_efc IN NUMBER,
618 x_ver_status_code IN VARCHAR2,
619 x_secondary_efc IN NUMBER,
620 x_secondary_efc_cd IN VARCHAR2,
621 x_pell_amount IN NUMBER,
622 x_pell_profile IN VARCHAR2,
623 x_enrollment_status IN VARCHAR2,
624 x_enrollment_dt IN DATE,
625 x_coa_amount IN NUMBER,
626 x_academic_calendar IN VARCHAR2,
627 x_payment_method IN VARCHAR2,
628 x_total_pymt_prds IN NUMBER,
629 x_incrcd_fed_pell_rcp_cd IN VARCHAR2,
630 x_attending_campus_id IN VARCHAR2,
631 x_est_disb_dt1 IN DATE,
632 x_orig_action_code IN VARCHAR2,
633 x_orig_status_dt IN DATE,
634 x_orig_ed_use_flags IN VARCHAR2,
635 x_ft_pell_amount IN NUMBER,
636 x_prev_accpt_efc IN NUMBER,
637 x_prev_accpt_tran_no IN VARCHAR2,
638 x_prev_accpt_sec_efc_cd IN VARCHAR2,
639 x_prev_accpt_coa IN NUMBER,
640 x_orig_reject_code IN VARCHAR2,
641 x_wk_inst_time_calc_pymt IN NUMBER,
642 x_wk_int_time_prg_def_yr IN NUMBER,
643 x_cr_clk_hrs_prds_sch_yr IN NUMBER,
644 x_cr_clk_hrs_acad_yr IN NUMBER,
645 x_inst_cross_ref_cd IN VARCHAR2,
646 x_low_tution_fee IN VARCHAR2,
647 x_rec_source IN VARCHAR2,
648 x_pending_amount IN NUMBER,
649 x_mode IN VARCHAR2,
650 x_birth_dt IN DATE,
651 x_last_name IN VARCHAR2,
652 x_first_name IN VARCHAR2,
653 x_middle_name IN VARCHAR2,
654 x_current_ssn IN VARCHAR2,
655 x_legacy_record_flag IN VARCHAR2,
656 x_reporting_pell_cd IN VARCHAR2,
657 x_rep_entity_id_txt IN VARCHAR2,
658 x_atd_entity_id_txt IN VARCHAR2,
659 x_note_message IN VARCHAR2,
660 x_full_resp_code IN VARCHAR2,
661 x_document_id_txt IN VARCHAR2
662
663 ) AS
664 /*
665 || Created By : adhawan
666 || Created On : 15-JAN-2001
667 || Purpose : Handles the INSERT DML logic for the table.
668 || Known limitations, enhancements or remarks :
669 || Change History :
670 || Who When What
671 || (reverse chronological order - newest change first)
672 */
673 CURSOR c IS
674 SELECT rowid
675 FROM igf_gr_rfms_all
676 WHERE origination_id = x_origination_id;
677
678 x_last_update_date DATE;
679 x_last_updated_by NUMBER;
680 x_last_update_login NUMBER;
681 x_request_id NUMBER;
682 x_program_id NUMBER;
683 x_program_application_id NUMBER;
684 x_program_update_date DATE;
685 l_org_id igf_gr_rfms_all.org_id%TYPE;
686
687 BEGIN
688
689 l_org_id := igf_aw_gen.get_org_id;
690
691 x_last_update_date := SYSDATE;
692 IF (x_mode = 'I') THEN
693 x_last_updated_by := 1;
694 x_last_update_login := 0;
695 ELSIF (x_mode = 'R') THEN
696 x_last_updated_by := fnd_global.user_id;
697 IF (x_last_updated_by IS NULL) THEN
698 x_last_updated_by := -1;
699 END IF;
700 x_last_update_login := fnd_global.login_id;
701 IF (x_last_update_login IS NULL) THEN
702 x_last_update_login := -1;
703 END IF;
704 x_request_id := fnd_global.conc_request_id;
705 x_program_id := fnd_global.conc_program_id;
706 x_program_application_id := fnd_global.prog_appl_id;
707
708 IF (x_request_id = -1) THEN
709 x_request_id := NULL;
710 x_program_id := NULL;
711 x_program_application_id := NULL;
712 x_program_update_date := NULL;
713 ELSE
714 x_program_update_date := SYSDATE;
715 END IF;
716 ELSE
717 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
718 igs_ge_msg_stack.add;
719 app_exception.raise_exception;
720 END IF;
721
722 before_dml(
723 p_action => 'INSERT',
724 x_rowid => x_rowid,
725 x_origination_id => x_origination_id,
726 x_ci_cal_type => x_ci_cal_type,
727 x_ci_sequence_number => x_ci_sequence_number,
728 x_base_id => x_base_id,
729 x_award_id => x_award_id,
730 x_rfmb_id => x_rfmb_id,
731 x_sys_orig_ssn => x_sys_orig_ssn,
732 x_sys_orig_name_cd => x_sys_orig_name_cd,
733 x_transaction_num => x_transaction_num,
734 x_efc => x_efc,
735 x_ver_status_code => x_ver_status_code,
736 x_secondary_efc => x_secondary_efc,
737 x_secondary_efc_cd => x_secondary_efc_cd,
738 x_pell_amount => x_pell_amount,
739 x_pell_profile => x_pell_profile,
740 x_enrollment_status => x_enrollment_status,
741 x_enrollment_dt => x_enrollment_dt,
742 x_coa_amount => x_coa_amount,
743 x_academic_calendar => x_academic_calendar,
744 x_payment_method => x_payment_method,
745 x_total_pymt_prds => x_total_pymt_prds,
746 x_incrcd_fed_pell_rcp_cd => x_incrcd_fed_pell_rcp_cd,
747 x_attending_campus_id => x_attending_campus_id,
748 x_est_disb_dt1 => x_est_disb_dt1,
749 x_orig_action_code => x_orig_action_code,
750 x_orig_status_dt => x_orig_status_dt,
751 x_orig_ed_use_flags => x_orig_ed_use_flags,
752 x_ft_pell_amount => x_ft_pell_amount,
753 x_prev_accpt_efc => x_prev_accpt_efc,
754 x_prev_accpt_tran_no => x_prev_accpt_tran_no,
755 x_prev_accpt_sec_efc_cd => x_prev_accpt_sec_efc_cd,
756 x_prev_accpt_coa => x_prev_accpt_coa,
757 x_orig_reject_code => x_orig_reject_code,
758 x_wk_inst_time_calc_pymt => x_wk_inst_time_calc_pymt,
759 x_wk_int_time_prg_def_yr => x_wk_int_time_prg_def_yr,
760 x_cr_clk_hrs_prds_sch_yr => x_cr_clk_hrs_prds_sch_yr,
761 x_cr_clk_hrs_acad_yr => x_cr_clk_hrs_acad_yr,
762 x_inst_cross_ref_cd => x_inst_cross_ref_cd,
763 x_low_tution_fee => x_low_tution_fee,
764 x_rec_source => x_rec_source,
765 x_pending_amount => x_pending_amount,
766 x_creation_date => x_last_update_date,
767 x_created_by => x_last_updated_by,
768 x_last_update_date => x_last_update_date,
769 x_last_updated_by => x_last_updated_by,
770 x_last_update_login => x_last_update_login,
771 x_birth_dt => x_birth_dt,
772 x_last_name => x_last_name,
773 x_first_name => x_first_name,
774 x_middle_name => x_middle_name,
775 x_current_ssn => x_current_ssn,
776 x_legacy_record_flag => x_legacy_record_flag,
777 x_reporting_pell_cd => x_reporting_pell_cd,
778 x_rep_entity_id_txt => x_rep_entity_id_txt,
779 x_atd_entity_id_txt => x_atd_entity_id_txt,
780 x_note_message => x_note_message ,
781 x_full_resp_code => x_full_resp_code,
782 x_document_id_txt => x_document_id_txt
783
784 );
785
786 INSERT INTO igf_gr_rfms_all (
787 origination_id,
788 ci_cal_type,
789 ci_sequence_number,
790 base_id,
791 award_id,
792 rfmb_id,
793 sys_orig_ssn,
794 sys_orig_name_cd,
795 transaction_num,
796 efc,
797 ver_status_code,
798 secondary_efc,
799 secondary_efc_cd,
800 pell_amount,
801 enrollment_status,
802 enrollment_dt,
803 coa_amount,
804 academic_calendar,
805 payment_method,
806 total_pymt_prds,
807 incrcd_fed_pell_rcp_cd,
808 attending_campus_id,
809 est_disb_dt1,
810 orig_action_code,
811 orig_status_dt,
812 orig_ed_use_flags,
813 ft_pell_amount,
814 prev_accpt_efc,
815 prev_accpt_tran_no,
816 prev_accpt_sec_efc_cd,
817 prev_accpt_coa,
818 orig_reject_code,
819 wk_inst_time_calc_pymt,
820 wk_int_time_prg_def_yr,
821 cr_clk_hrs_prds_sch_yr,
822 cr_clk_hrs_acad_yr,
823 inst_cross_ref_cd,
824 low_tution_fee,
825 rec_source,
826 pending_amount,
827 creation_date,
828 created_by,
829 last_update_date,
830 last_updated_by,
831 last_update_login,
832 request_id,
833 program_id,
834 program_application_id,
835 program_update_date,
836 org_id,
837 birth_dt,
838 last_name,
839 first_name,
840 middle_name,
841 current_ssn,
842 legacy_record_flag,
843 reporting_pell_cd,
844 rep_entity_id_txt,
845 atd_entity_id_txt,
846 note_message,
847 full_resp_code,
848 document_id_txt
849
850 ) VALUES (
851 new_references.origination_id,
852 new_references.ci_cal_type,
853 new_references.ci_sequence_number,
854 new_references.base_id,
855 new_references.award_id,
856 new_references.rfmb_id,
857 new_references.sys_orig_ssn,
858 new_references.sys_orig_name_cd,
859 new_references.transaction_num,
860 new_references.efc,
861 new_references.ver_status_code,
862 new_references.secondary_efc,
863 new_references.secondary_efc_cd,
864 new_references.pell_amount,
865 new_references.enrollment_status,
866 new_references.enrollment_dt,
867 new_references.coa_amount,
868 new_references.academic_calendar,
869 new_references.payment_method,
870 new_references.total_pymt_prds,
871 new_references.incrcd_fed_pell_rcp_cd,
872 new_references.attending_campus_id,
873 new_references.est_disb_dt1,
874 new_references.orig_action_code,
875 new_references.orig_status_dt,
876 new_references.orig_ed_use_flags,
877 new_references.ft_pell_amount,
878 new_references.prev_accpt_efc,
879 new_references.prev_accpt_tran_no,
880 new_references.prev_accpt_sec_efc_cd,
881 new_references.prev_accpt_coa,
882 new_references.orig_reject_code,
883 new_references.wk_inst_time_calc_pymt,
884 new_references.wk_int_time_prg_def_yr,
885 new_references.cr_clk_hrs_prds_sch_yr,
886 new_references.cr_clk_hrs_acad_yr,
887 new_references.inst_cross_ref_cd,
888 new_references.low_tution_fee,
889 new_references.rec_source,
890 new_references.pending_amount,
891 x_last_update_date,
892 x_last_updated_by,
893 x_last_update_date,
894 x_last_updated_by,
895 x_last_update_login ,
896 x_request_id,
897 x_program_id,
898 x_program_application_id,
899 x_program_update_date,
900 l_org_id,
901 new_references.birth_dt,
902 new_references.last_name,
903 new_references.first_name,
904 new_references.middle_name,
905 new_references.current_ssn,
906 new_references.legacy_record_flag,
907 new_references.reporting_pell_cd,
908 new_references.rep_entity_id_txt,
909 new_references.atd_entity_id_txt,
910 new_references.note_message,
911 new_references.full_resp_code,
912 new_references.document_id_txt
913
914 );
915
916 OPEN c;
917 FETCH c INTO x_rowid;
918 IF (c%NOTFOUND) THEN
919 CLOSE c;
920 RAISE NO_DATA_FOUND;
921 END IF;
922 CLOSE c;
923
924 END insert_row;
925
926
927 PROCEDURE lock_row (
928 x_rowid IN VARCHAR2,
929 x_origination_id IN VARCHAR2,
930 x_ci_cal_type IN VARCHAR2,
931 x_ci_sequence_number IN NUMBER,
932 x_base_id IN NUMBER,
933 x_award_id IN NUMBER,
934 x_rfmb_id IN NUMBER,
935 x_sys_orig_ssn IN VARCHAR2,
936 x_sys_orig_name_cd IN VARCHAR2,
937 x_transaction_num IN VARCHAR2,
938 x_efc IN NUMBER,
939 x_ver_status_code IN VARCHAR2,
940 x_secondary_efc IN NUMBER,
941 x_secondary_efc_cd IN VARCHAR2,
942 x_pell_amount IN NUMBER,
943 x_pell_profile IN VARCHAR2,
944 x_enrollment_status IN VARCHAR2,
945 x_enrollment_dt IN DATE,
946 x_coa_amount IN NUMBER,
947 x_academic_calendar IN VARCHAR2,
948 x_payment_method IN VARCHAR2,
949 x_total_pymt_prds IN NUMBER,
950 x_incrcd_fed_pell_rcp_cd IN VARCHAR2,
951 x_attending_campus_id IN VARCHAR2,
952 x_est_disb_dt1 IN DATE,
953 x_orig_action_code IN VARCHAR2,
954 x_orig_status_dt IN DATE,
955 x_orig_ed_use_flags IN VARCHAR2,
956 x_ft_pell_amount IN NUMBER,
957 x_prev_accpt_efc IN NUMBER,
958 x_prev_accpt_tran_no IN VARCHAR2,
959 x_prev_accpt_sec_efc_cd IN VARCHAR2,
960 x_prev_accpt_coa IN NUMBER,
961 x_orig_reject_code IN VARCHAR2,
962 x_wk_inst_time_calc_pymt IN NUMBER,
963 x_wk_int_time_prg_def_yr IN NUMBER,
964 x_cr_clk_hrs_prds_sch_yr IN NUMBER,
965 x_cr_clk_hrs_acad_yr IN NUMBER,
966 x_inst_cross_ref_cd IN VARCHAR2,
967 x_low_tution_fee IN VARCHAR2,
968 x_rec_source IN VARCHAR2,
969 x_pending_amount IN NUMBER,
970 x_birth_dt IN DATE,
971 x_last_name IN VARCHAR2,
972 x_first_name IN VARCHAR2,
973 x_middle_name IN VARCHAR2,
974 x_current_ssn IN VARCHAR2,
975 x_legacy_record_flag IN VARCHAR2,
976 x_reporting_pell_cd IN VARCHAR2,
977 x_rep_entity_id_txt IN VARCHAR2,
978 x_atd_entity_id_txt IN VARCHAR2,
979 x_note_message IN VARCHAR2,
980 x_full_resp_code IN VARCHAR2,
981 x_document_id_txt IN VARCHAR2
982
983
984 ) AS
985 /*
986 || Created By : adhawan
987 || Created On : 15-JAN-2001
988 || Purpose : Handles the LOCK mechanism for the table.
989 || Known limitations, enhancements or remarks :
990 || Change History :
991 || Who When What
992 || (reverse chronological order - newest change first)
993 */
994 CURSOR c1 IS
995 SELECT
996 ci_cal_type,
997 ci_sequence_number,
998 base_id,
999 award_id,
1000 rfmb_id,
1001 sys_orig_ssn,
1002 sys_orig_name_cd,
1003 transaction_num,
1004 efc,
1005 ver_status_code,
1006 secondary_efc,
1007 secondary_efc_cd,
1008 pell_amount,
1009 enrollment_status,
1010 enrollment_dt,
1011 coa_amount,
1012 academic_calendar,
1013 payment_method,
1014 total_pymt_prds,
1015 incrcd_fed_pell_rcp_cd,
1016 attending_campus_id,
1017 est_disb_dt1,
1018 orig_action_code,
1019 orig_status_dt,
1020 orig_ed_use_flags,
1021 ft_pell_amount,
1022 prev_accpt_efc,
1023 prev_accpt_tran_no,
1024 prev_accpt_sec_efc_cd,
1025 prev_accpt_coa,
1026 orig_reject_code,
1027 wk_inst_time_calc_pymt,
1028 wk_int_time_prg_def_yr,
1029 cr_clk_hrs_prds_sch_yr,
1030 cr_clk_hrs_acad_yr,
1031 inst_cross_ref_cd,
1032 low_tution_fee,
1033 rec_source,
1034 pending_amount,
1035 birth_dt,
1036 last_name,
1037 first_name,
1038 middle_name,
1039 current_ssn,
1040 legacy_record_flag,
1041 reporting_pell_cd,
1042 rep_entity_id_txt,
1043 atd_entity_id_txt,
1044 note_message,
1045 full_resp_code,
1046 document_id_txt
1047
1048 FROM igf_gr_rfms_all
1049 WHERE rowid = x_rowid
1050 FOR UPDATE NOWAIT;
1051
1052 tlinfo c1%ROWTYPE;
1053
1054 BEGIN
1055
1056 OPEN c1;
1057 FETCH c1 INTO tlinfo;
1058 IF (c1%notfound) THEN
1059 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1060 igs_ge_msg_stack.add;
1061 CLOSE c1;
1062 app_exception.raise_exception;
1063 RETURN;
1064 END IF;
1065 CLOSE c1;
1066
1067 IF (
1068 (tlinfo.ci_cal_type = x_ci_cal_type)
1069 AND (tlinfo.ci_sequence_number = x_ci_sequence_number)
1070 AND (tlinfo.base_id = x_base_id)
1071 AND (tlinfo.award_id = x_award_id)
1072 AND ((tlinfo.rfmb_id = x_rfmb_id) OR ((tlinfo.rfmb_id IS NULL) AND (X_rfmb_id IS NULL)))
1073 AND ((tlinfo.sys_orig_ssn = x_sys_orig_ssn) OR ((tlinfo.sys_orig_ssn IS NULL) AND (X_sys_orig_ssn IS NULL)))
1074 AND ((tlinfo.sys_orig_name_cd = x_sys_orig_name_cd) OR ((tlinfo.sys_orig_name_cd IS NULL) AND (X_sys_orig_name_cd IS NULL)))
1075 AND ((tlinfo.transaction_num = x_transaction_num) OR ((tlinfo.transaction_num IS NULL) AND (X_transaction_num IS NULL)))
1076 AND ((tlinfo.efc = x_efc) OR ((tlinfo.efc IS NULL) AND (X_efc IS NULL)))
1077 AND ((tlinfo.ver_status_code = x_ver_status_code) OR ((tlinfo.ver_status_code IS NULL) AND (X_ver_status_code IS NULL)))
1078 AND ((tlinfo.secondary_efc = x_secondary_efc) OR ((tlinfo.secondary_efc IS NULL) AND (X_secondary_efc IS NULL)))
1079 AND ((tlinfo.secondary_efc_cd = x_secondary_efc_cd) OR ((tlinfo.secondary_efc_cd IS NULL) AND (X_secondary_efc_cd IS NULL)))
1080 AND (tlinfo.pell_amount = x_pell_amount)
1081 AND ((tlinfo.enrollment_status = x_enrollment_status) OR ((tlinfo.enrollment_status IS NULL) AND (X_enrollment_status IS NULL)))
1082 AND ((tlinfo.enrollment_dt = x_enrollment_dt) OR ((tlinfo.enrollment_dt IS NULL) AND (X_enrollment_dt IS NULL)))
1083 AND ((tlinfo.coa_amount = x_coa_amount) OR ((tlinfo.coa_amount IS NULL) AND (X_coa_amount IS NULL)))
1084 AND ((tlinfo.academic_calendar = x_academic_calendar) OR ((tlinfo.academic_calendar IS NULL) AND (X_academic_calendar IS NULL)))
1085 AND ((tlinfo.payment_method = x_payment_method) OR ((tlinfo.payment_method IS NULL) AND (X_payment_method IS NULL)))
1086 AND ((tlinfo.total_pymt_prds = x_total_pymt_prds) OR ((tlinfo.total_pymt_prds IS NULL) AND (X_total_pymt_prds IS NULL)))
1087 AND ((tlinfo.incrcd_fed_pell_rcp_cd = x_incrcd_fed_pell_rcp_cd) OR ((tlinfo.incrcd_fed_pell_rcp_cd IS NULL) AND (X_incrcd_fed_pell_rcp_cd IS NULL)))
1088 AND ((tlinfo.attending_campus_id = x_attending_campus_id) OR ((tlinfo.attending_campus_id IS NULL) AND (X_attending_campus_id IS NULL)))
1089 AND ((tlinfo.est_disb_dt1 = x_est_disb_dt1) OR ((tlinfo.est_disb_dt1 IS NULL) AND (X_est_disb_dt1 IS NULL)))
1090 AND ((tlinfo.orig_action_code = x_orig_action_code) OR ((tlinfo.orig_action_code IS NULL) AND (X_orig_action_code IS NULL)))
1091 AND ((tlinfo.orig_status_dt = x_orig_status_dt) OR ((tlinfo.orig_status_dt IS NULL) AND (X_orig_status_dt IS NULL)))
1092 AND ((tlinfo.orig_ed_use_flags = x_orig_ed_use_flags) OR ((tlinfo.orig_ed_use_flags IS NULL) AND (X_orig_ed_use_flags IS NULL)))
1093 AND ((tlinfo.ft_pell_amount = x_ft_pell_amount) OR ((tlinfo.ft_pell_amount IS NULL) AND (X_ft_pell_amount IS NULL)))
1094 AND ((tlinfo.prev_accpt_efc = x_prev_accpt_efc) OR ((tlinfo.prev_accpt_efc IS NULL) AND (X_prev_accpt_efc IS NULL)))
1095 AND ((tlinfo.prev_accpt_tran_no = x_prev_accpt_tran_no) OR ((tlinfo.prev_accpt_tran_no IS NULL) AND (X_prev_accpt_tran_no IS NULL)))
1096 AND ((tlinfo.prev_accpt_sec_efc_cd = x_prev_accpt_sec_efc_cd) OR ((tlinfo.prev_accpt_sec_efc_cd IS NULL) AND (X_prev_accpt_sec_efc_cd IS NULL)))
1097 AND ((tlinfo.prev_accpt_coa = x_prev_accpt_coa) OR ((tlinfo.prev_accpt_coa IS NULL) AND (X_prev_accpt_coa IS NULL)))
1098 AND ((tlinfo.orig_reject_code = x_orig_reject_code) OR ((tlinfo.orig_reject_code IS NULL) AND (X_orig_reject_code IS NULL)))
1099 AND ((tlinfo.wk_inst_time_calc_pymt = x_wk_inst_time_calc_pymt) OR ((tlinfo.wk_inst_time_calc_pymt IS NULL) AND (X_wk_inst_time_calc_pymt IS NULL)))
1100 AND ((tlinfo.wk_int_time_prg_def_yr = x_wk_int_time_prg_def_yr) OR ((tlinfo.wk_int_time_prg_def_yr IS NULL) AND (X_wk_int_time_prg_def_yr IS NULL)))
1101 AND ((tlinfo.cr_clk_hrs_prds_sch_yr = x_cr_clk_hrs_prds_sch_yr) OR ((tlinfo.cr_clk_hrs_prds_sch_yr IS NULL) AND (X_cr_clk_hrs_prds_sch_yr IS NULL)))
1102 AND ((tlinfo.cr_clk_hrs_acad_yr = x_cr_clk_hrs_acad_yr) OR ((tlinfo.cr_clk_hrs_acad_yr IS NULL) AND (X_cr_clk_hrs_acad_yr IS NULL)))
1103 AND ((tlinfo.inst_cross_ref_cd = x_inst_cross_ref_cd) OR ((tlinfo.inst_cross_ref_cd IS NULL) AND (X_inst_cross_ref_cd IS NULL)))
1104 AND ((tlinfo.low_tution_fee = x_low_tution_fee) OR ((tlinfo.low_tution_fee IS NULL) AND (X_low_tution_fee IS NULL)))
1105 AND ((tlinfo.rec_source = x_rec_source) OR ((tlinfo.rec_source IS NULL) AND (X_rec_source IS NULL)))
1106 AND ((tlinfo.pending_amount = x_pending_amount) OR ((tlinfo.pending_amount IS NULL) AND (x_pending_amount IS NULL)))
1107 AND ((tlinfo.birth_dt = x_birth_dt) OR ((tlinfo.birth_dt IS NULL) AND (x_birth_dt IS NULL)))
1108 AND ((tlinfo.last_name = x_last_name) OR ((tlinfo.last_name IS NULL) AND (x_last_name IS NULL)))
1109 AND ((tlinfo.first_name = x_first_name) OR ((tlinfo.first_name IS NULL) AND (x_first_name IS NULL)))
1110 AND ((tlinfo.middle_name = x_middle_name) OR ((tlinfo.middle_name IS NULL) AND (x_middle_name IS NULL)))
1111 AND ((tlinfo.current_ssn = x_current_ssn) OR ((tlinfo.current_ssn IS NULL) AND (x_current_ssn IS NULL)))
1112 AND ((tlinfo.legacy_record_flag = x_legacy_record_flag) OR ((tlinfo.legacy_record_flag IS NULL) AND (x_legacy_record_flag IS NULL)))
1113 AND ((tlinfo.reporting_pell_cd = x_reporting_pell_cd) OR ((tlinfo.reporting_pell_cd IS NULL) AND (x_reporting_pell_cd IS NULL)))
1114 AND ((tlinfo.rep_entity_id_txt = x_rep_entity_id_txt) OR ((tlinfo.rep_entity_id_txt IS NULL) AND (x_rep_entity_id_txt IS NULL)))
1115 AND ((tlinfo.atd_entity_id_txt = x_atd_entity_id_txt) OR ((tlinfo.atd_entity_id_txt IS NULL) AND (x_atd_entity_id_txt IS NULL)))
1116 AND ((tlinfo.note_message = x_note_message) OR ((tlinfo.note_message IS NULL) AND (x_note_message IS NULL)))
1117 AND ((tlinfo.full_resp_code = x_full_resp_code) OR ((tlinfo.full_resp_code IS NULL) AND (x_full_resp_code IS NULL)))
1118 AND ((tlinfo.document_id_txt = x_document_id_txt) OR ((tlinfo.document_id_txt IS NULL) AND (x_document_id_txt IS NULL)))
1119
1120 ) THEN
1121 NULL;
1122 ELSE
1123 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1124 igs_ge_msg_stack.add;
1125 app_exception.raise_exception;
1126 END IF;
1127
1128 RETURN;
1129
1130 END lock_row;
1131
1132
1133 PROCEDURE update_row (
1134 x_rowid IN VARCHAR2,
1135 x_origination_id IN VARCHAR2,
1136 x_ci_cal_type IN VARCHAR2,
1137 x_ci_sequence_number IN NUMBER,
1138 x_base_id IN NUMBER,
1139 x_award_id IN NUMBER,
1140 x_rfmb_id IN NUMBER,
1141 x_sys_orig_ssn IN VARCHAR2,
1142 x_sys_orig_name_cd IN VARCHAR2,
1143 x_transaction_num IN VARCHAR2,
1144 x_efc IN NUMBER,
1145 x_ver_status_code IN VARCHAR2,
1146 x_secondary_efc IN NUMBER,
1147 x_secondary_efc_cd IN VARCHAR2,
1148 x_pell_amount IN NUMBER,
1149 x_pell_profile IN VARCHAR2,
1150 x_enrollment_status IN VARCHAR2,
1151 x_enrollment_dt IN DATE,
1152 x_coa_amount IN NUMBER,
1153 x_academic_calendar IN VARCHAR2,
1154 x_payment_method IN VARCHAR2,
1155 x_total_pymt_prds IN NUMBER,
1156 x_incrcd_fed_pell_rcp_cd IN VARCHAR2,
1157 x_attending_campus_id IN VARCHAR2,
1158 x_est_disb_dt1 IN DATE,
1159 x_orig_action_code IN VARCHAR2,
1160 x_orig_status_dt IN DATE,
1161 x_orig_ed_use_flags IN VARCHAR2,
1162 x_ft_pell_amount IN NUMBER,
1163 x_prev_accpt_efc IN NUMBER,
1164 x_prev_accpt_tran_no IN VARCHAR2,
1165 x_prev_accpt_sec_efc_cd IN VARCHAR2,
1166 x_prev_accpt_coa IN NUMBER,
1167 x_orig_reject_code IN VARCHAR2,
1168 x_wk_inst_time_calc_pymt IN NUMBER,
1169 x_wk_int_time_prg_def_yr IN NUMBER,
1170 x_cr_clk_hrs_prds_sch_yr IN NUMBER,
1171 x_cr_clk_hrs_acad_yr IN NUMBER,
1172 x_inst_cross_ref_cd IN VARCHAR2,
1173 x_low_tution_fee IN VARCHAR2,
1174 x_rec_source IN VARCHAR2,
1175 x_pending_amount IN NUMBER,
1176 x_mode IN VARCHAR2,
1177 x_birth_dt IN DATE,
1178 x_last_name IN VARCHAR2,
1179 x_first_name IN VARCHAR2,
1180 x_middle_name IN VARCHAR2,
1181 x_current_ssn IN VARCHAR2,
1182 x_legacy_record_flag IN VARCHAR2,
1183 x_reporting_pell_cd IN VARCHAR2,
1184 x_rep_entity_id_txt IN VARCHAR2,
1185 x_atd_entity_id_txt IN VARCHAR2,
1186 x_note_message IN VARCHAR2,
1187 x_full_resp_code IN VARCHAR2,
1188 x_document_id_txt IN VARCHAR2
1189
1190 ) AS
1191 /*
1192 || Created By : adhawan
1193 || Created On : 15-JAN-2001
1194 || Purpose : Handles the UPDATE DML logic for the table.
1195 || Known limitations, enhancements or remarks :
1196 || Change History :
1197 || Who When What
1198 || (reverse chronological order - newest change first)
1199 */
1200 x_last_update_date DATE ;
1201 x_last_updated_by NUMBER;
1202 x_last_update_login NUMBER;
1203 x_request_id NUMBER;
1204 x_program_id NUMBER;
1205 x_program_application_id NUMBER;
1206 x_program_update_date DATE;
1207
1208 BEGIN
1209
1210 x_last_update_date := SYSDATE;
1211 IF (X_MODE = 'I') THEN
1212 x_last_updated_by := 1;
1213 x_last_update_login := 0;
1214 ELSIF (x_mode = 'R') THEN
1215 x_last_updated_by := fnd_global.user_id;
1216 IF x_last_updated_by IS NULL THEN
1217 x_last_updated_by := -1;
1218 END IF;
1219 x_last_update_login := fnd_global.login_id;
1220 IF (x_last_update_login IS NULL) THEN
1221 x_last_update_login := -1;
1222 END IF;
1223 ELSE
1224 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1225 igs_ge_msg_stack.add;
1226 app_exception.raise_exception;
1227 END IF;
1228
1229 before_dml(
1230 p_action => 'UPDATE',
1231 x_rowid => x_rowid,
1232 x_origination_id => x_origination_id,
1233 x_ci_cal_type => x_ci_cal_type,
1234 x_ci_sequence_number => x_ci_sequence_number,
1235 x_base_id => x_base_id,
1236 x_award_id => x_award_id,
1237 x_rfmb_id => x_rfmb_id,
1238 x_sys_orig_ssn => x_sys_orig_ssn,
1239 x_sys_orig_name_cd => x_sys_orig_name_cd,
1240 x_transaction_num => x_transaction_num,
1241 x_efc => x_efc,
1242 x_ver_status_code => x_ver_status_code,
1243 x_secondary_efc => x_secondary_efc,
1244 x_secondary_efc_cd => x_secondary_efc_cd,
1245 x_pell_amount => x_pell_amount,
1246 x_pell_profile => x_pell_profile,
1247 x_enrollment_status => x_enrollment_status,
1248 x_enrollment_dt => x_enrollment_dt,
1249 x_coa_amount => x_coa_amount,
1250 x_academic_calendar => x_academic_calendar,
1251 x_payment_method => x_payment_method,
1252 x_total_pymt_prds => x_total_pymt_prds,
1253 x_incrcd_fed_pell_rcp_cd => x_incrcd_fed_pell_rcp_cd,
1254 x_attending_campus_id => x_attending_campus_id,
1255 x_est_disb_dt1 => x_est_disb_dt1,
1256 x_orig_action_code => x_orig_action_code,
1257 x_orig_status_dt => x_orig_status_dt,
1258 x_orig_ed_use_flags => x_orig_ed_use_flags,
1259 x_ft_pell_amount => x_ft_pell_amount,
1260 x_prev_accpt_efc => x_prev_accpt_efc,
1261 x_prev_accpt_tran_no => x_prev_accpt_tran_no,
1262 x_prev_accpt_sec_efc_cd => x_prev_accpt_sec_efc_cd,
1263 x_prev_accpt_coa => x_prev_accpt_coa,
1264 x_orig_reject_code => x_orig_reject_code,
1265 x_wk_inst_time_calc_pymt => x_wk_inst_time_calc_pymt,
1266 x_wk_int_time_prg_def_yr => x_wk_int_time_prg_def_yr,
1267 x_cr_clk_hrs_prds_sch_yr => x_cr_clk_hrs_prds_sch_yr,
1268 x_cr_clk_hrs_acad_yr => x_cr_clk_hrs_acad_yr,
1269 x_inst_cross_ref_cd => x_inst_cross_ref_cd,
1270 x_low_tution_fee => x_low_tution_fee,
1271 x_rec_source => x_rec_source,
1272 x_pending_amount => x_pending_amount,
1273 x_creation_date => x_last_update_date,
1274 x_created_by => x_last_updated_by,
1275 x_last_update_date => x_last_update_date,
1276 x_last_updated_by => x_last_updated_by,
1277 x_last_update_login => x_last_update_login,
1278 x_birth_dt => x_birth_dt,
1279 x_last_name => x_last_name,
1280 x_first_name => x_first_name,
1281 x_middle_name => x_middle_name,
1282 x_current_ssn => x_current_ssn,
1283 x_legacy_record_flag => x_legacy_record_flag,
1284 x_reporting_pell_cd => x_reporting_pell_cd,
1285 x_rep_entity_id_txt => x_rep_entity_id_txt,
1286 x_atd_entity_id_txt => x_atd_entity_id_txt,
1287 x_note_message => x_note_message,
1288 x_full_resp_code => x_full_resp_code,
1289 x_document_id_txt => x_document_id_txt
1290
1291 );
1292
1293 IF (x_mode = 'R') THEN
1294 x_request_id := fnd_global.conc_request_id;
1295 x_program_id := fnd_global.conc_program_id;
1296 x_program_application_id := fnd_global.prog_appl_id;
1297 IF (x_request_id = -1) THEN
1298 x_request_id := old_references.request_id;
1299 x_program_id := old_references.program_id;
1300 x_program_application_id := old_references.program_application_id;
1301 x_program_update_date := old_references.program_update_date;
1302 ELSE
1303 x_program_update_date := SYSDATE;
1304 END IF;
1305 END IF;
1306
1307 UPDATE igf_gr_rfms_all
1308 SET
1309 ci_cal_type = new_references.ci_cal_type,
1310 ci_sequence_number = new_references.ci_sequence_number,
1311 base_id = new_references.base_id,
1312 award_id = new_references.award_id,
1313 rfmb_id = new_references.rfmb_id,
1314 sys_orig_ssn = new_references.sys_orig_ssn,
1315 sys_orig_name_cd = new_references.sys_orig_name_cd,
1316 transaction_num = new_references.transaction_num,
1317 efc = new_references.efc,
1318 ver_status_code = new_references.ver_status_code,
1319 secondary_efc = new_references.secondary_efc,
1320 secondary_efc_cd = new_references.secondary_efc_cd,
1321 pell_amount = new_references.pell_amount,
1322 enrollment_status = new_references.enrollment_status,
1323 enrollment_dt = new_references.enrollment_dt,
1324 coa_amount = new_references.coa_amount,
1325 academic_calendar = new_references.academic_calendar,
1326 payment_method = new_references.payment_method,
1327 total_pymt_prds = new_references.total_pymt_prds,
1328 incrcd_fed_pell_rcp_cd = new_references.incrcd_fed_pell_rcp_cd,
1329 attending_campus_id = new_references.attending_campus_id,
1330 est_disb_dt1 = new_references.est_disb_dt1,
1331 orig_action_code = new_references.orig_action_code,
1332 orig_status_dt = new_references.orig_status_dt,
1333 orig_ed_use_flags = new_references.orig_ed_use_flags,
1334 ft_pell_amount = new_references.ft_pell_amount,
1335 prev_accpt_efc = new_references.prev_accpt_efc,
1336 prev_accpt_tran_no = new_references.prev_accpt_tran_no,
1337 prev_accpt_sec_efc_cd = new_references.prev_accpt_sec_efc_cd,
1338 prev_accpt_coa = new_references.prev_accpt_coa,
1339 orig_reject_code = new_references.orig_reject_code,
1340 wk_inst_time_calc_pymt = new_references.wk_inst_time_calc_pymt,
1341 wk_int_time_prg_def_yr = new_references.wk_int_time_prg_def_yr,
1342 cr_clk_hrs_prds_sch_yr = new_references.cr_clk_hrs_prds_sch_yr,
1343 cr_clk_hrs_acad_yr = new_references.cr_clk_hrs_acad_yr,
1344 inst_cross_ref_cd = new_references.inst_cross_ref_cd,
1345 low_tution_fee = new_references.low_tution_fee,
1346 rec_source = new_references.rec_source,
1347 pending_amount = new_references.pending_amount,
1348 last_update_date = x_last_update_date,
1349 last_updated_by = x_last_updated_by,
1350 last_update_login = x_last_update_login ,
1351 request_id = x_request_id,
1352 program_id = x_program_id,
1353 program_application_id = x_program_application_id,
1354 program_update_date = x_program_update_date,
1355 birth_dt = new_references.birth_dt,
1356 last_name = new_references.last_name,
1357 first_name = new_references.first_name,
1358 middle_name = new_references.middle_name,
1359 current_ssn = new_references.current_ssn,
1360 legacy_record_flag = new_references.legacy_record_flag,
1361 reporting_pell_cd = new_references.reporting_pell_cd,
1362 rep_entity_id_txt = new_references.rep_entity_id_txt,
1363 atd_entity_id_txt = new_references.atd_entity_id_txt,
1364 note_message = new_references.note_message,
1365 full_resp_code = new_references.full_resp_code,
1366 document_id_txt = new_references.document_id_txt
1367
1368 WHERE rowid = x_rowid;
1369
1370 IF (SQL%NOTFOUND) THEN
1371 RAISE NO_DATA_FOUND;
1372 END IF;
1373
1374 END update_row;
1375
1376
1377 PROCEDURE add_row (
1378 x_rowid IN OUT NOCOPY VARCHAR2,
1379 x_origination_id IN VARCHAR2,
1380 x_ci_cal_type IN VARCHAR2,
1381 x_ci_sequence_number IN NUMBER,
1382 x_base_id IN NUMBER,
1383 x_award_id IN NUMBER,
1384 x_rfmb_id IN NUMBER,
1385 x_sys_orig_ssn IN VARCHAR2,
1386 x_sys_orig_name_cd IN VARCHAR2,
1387 x_transaction_num IN VARCHAR2,
1388 x_efc IN NUMBER,
1389 x_ver_status_code IN VARCHAR2,
1390 x_secondary_efc IN NUMBER,
1391 x_secondary_efc_cd IN VARCHAR2,
1392 x_pell_amount IN NUMBER,
1393 x_pell_profile IN VARCHAR2,
1394 x_enrollment_status IN VARCHAR2,
1395 x_enrollment_dt IN DATE,
1396 x_coa_amount IN NUMBER,
1397 x_academic_calendar IN VARCHAR2,
1398 x_payment_method IN VARCHAR2,
1399 x_total_pymt_prds IN NUMBER,
1400 x_incrcd_fed_pell_rcp_cd IN VARCHAR2,
1401 x_attending_campus_id IN VARCHAR2,
1402 x_est_disb_dt1 IN DATE,
1403 x_orig_action_code IN VARCHAR2,
1404 x_orig_status_dt IN DATE,
1405 x_orig_ed_use_flags IN VARCHAR2,
1406 x_ft_pell_amount IN NUMBER,
1407 x_prev_accpt_efc IN NUMBER,
1408 x_prev_accpt_tran_no IN VARCHAR2,
1409 x_prev_accpt_sec_efc_cd IN VARCHAR2,
1410 x_prev_accpt_coa IN NUMBER,
1411 x_orig_reject_code IN VARCHAR2,
1412 x_wk_inst_time_calc_pymt IN NUMBER,
1413 x_wk_int_time_prg_def_yr IN NUMBER,
1414 x_cr_clk_hrs_prds_sch_yr IN NUMBER,
1415 x_cr_clk_hrs_acad_yr IN NUMBER,
1416 x_inst_cross_ref_cd IN VARCHAR2,
1417 x_low_tution_fee IN VARCHAR2,
1418 x_rec_source IN VARCHAR2,
1419 x_pending_amount IN NUMBER,
1420 x_mode IN VARCHAR2,
1421 x_birth_dt IN DATE,
1422 x_last_name IN VARCHAR2,
1423 x_first_name IN VARCHAR2,
1424 x_middle_name IN VARCHAR2,
1425 x_current_ssn IN VARCHAR2,
1426 x_legacy_record_flag IN VARCHAR2,
1427 x_reporting_pell_cd IN VARCHAR2,
1428 x_rep_entity_id_txt IN VARCHAR2,
1429 x_atd_entity_id_txt IN VARCHAR2,
1430 x_note_message IN VARCHAR2,
1431 x_full_resp_code IN VARCHAR2,
1432 x_document_id_txt IN VARCHAR2
1433
1434 ) AS
1435 /*
1436 || Created By : adhawan
1437 || Created On : 15-JAN-2001
1438 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1439 || Known limitations, enhancements or remarks :
1440 || Change History :
1441 || Who When What
1442 || (reverse chronological order - newest change first)
1443 */
1444 CURSOR c1 IS
1445 SELECT rowid
1446 FROM igf_gr_rfms_all
1447 WHERE origination_id = x_origination_id;
1448
1449 BEGIN
1450
1451 OPEN c1;
1452 FETCH c1 INTO x_rowid;
1453 IF (c1%NOTFOUND) THEN
1454 CLOSE c1;
1455
1456 insert_row (
1457 x_rowid,
1458 x_origination_id,
1459 x_ci_cal_type,
1460 x_ci_sequence_number,
1461 x_base_id,
1462 x_award_id,
1463 x_rfmb_id,
1464 x_sys_orig_ssn,
1465 x_sys_orig_name_cd,
1466 x_transaction_num,
1467 x_efc,
1468 x_ver_status_code,
1469 x_secondary_efc,
1470 x_secondary_efc_cd,
1471 x_pell_amount,
1472 x_pell_profile,
1473 x_enrollment_status,
1474 x_enrollment_dt,
1475 x_coa_amount,
1476 x_academic_calendar,
1477 x_payment_method,
1478 x_total_pymt_prds,
1479 x_incrcd_fed_pell_rcp_cd,
1480 x_attending_campus_id,
1481 x_est_disb_dt1,
1482 x_orig_action_code,
1483 x_orig_status_dt,
1484 x_orig_ed_use_flags,
1485 x_ft_pell_amount,
1486 x_prev_accpt_efc,
1487 x_prev_accpt_tran_no,
1488 x_prev_accpt_sec_efc_cd,
1489 x_prev_accpt_coa,
1490 x_orig_reject_code,
1491 x_wk_inst_time_calc_pymt,
1492 x_wk_int_time_prg_def_yr,
1493 x_cr_clk_hrs_prds_sch_yr,
1494 x_cr_clk_hrs_acad_yr,
1495 x_inst_cross_ref_cd,
1496 x_low_tution_fee,
1497 x_rec_source,
1498 x_pending_amount,
1499 x_mode,
1500 x_birth_dt,
1501 x_last_name,
1502 x_first_name,
1503 x_middle_name,
1504 x_current_ssn,
1505 x_legacy_record_flag,
1506 x_reporting_pell_cd,
1507 x_rep_entity_id_txt,
1508 x_atd_entity_id_txt,
1509 x_note_message,
1510 x_full_resp_code,
1511 x_document_id_txt
1512
1513 );
1514 RETURN;
1515 END IF;
1516 CLOSE c1;
1517
1518 update_row (
1519 x_rowid,
1520 x_origination_id,
1521 x_ci_cal_type,
1522 x_ci_sequence_number,
1523 x_base_id,
1524 x_award_id,
1525 x_rfmb_id,
1526 x_sys_orig_ssn,
1527 x_sys_orig_name_cd,
1528 x_transaction_num,
1529 x_efc,
1530 x_ver_status_code,
1531 x_secondary_efc,
1532 x_secondary_efc_cd,
1533 x_pell_amount,
1534 x_pell_profile,
1535 x_enrollment_status,
1536 x_enrollment_dt,
1537 x_coa_amount,
1538 x_academic_calendar,
1539 x_payment_method,
1540 x_total_pymt_prds,
1541 x_incrcd_fed_pell_rcp_cd,
1542 x_attending_campus_id,
1543 x_est_disb_dt1,
1544 x_orig_action_code,
1545 x_orig_status_dt,
1546 x_orig_ed_use_flags,
1547 x_ft_pell_amount,
1548 x_prev_accpt_efc,
1549 x_prev_accpt_tran_no,
1550 x_prev_accpt_sec_efc_cd,
1551 x_prev_accpt_coa,
1552 x_orig_reject_code,
1553 x_wk_inst_time_calc_pymt,
1554 x_wk_int_time_prg_def_yr,
1555 x_cr_clk_hrs_prds_sch_yr,
1556 x_cr_clk_hrs_acad_yr,
1557 x_inst_cross_ref_cd,
1558 x_low_tution_fee,
1559 x_rec_source,
1560 x_pending_amount,
1561 x_mode,
1562 x_birth_dt,
1563 x_last_name,
1564 x_first_name,
1565 x_middle_name,
1566 x_current_ssn,
1567 x_legacy_record_flag,
1568 x_reporting_pell_cd,
1569 x_rep_entity_id_txt,
1570 x_atd_entity_id_txt,
1571 x_note_message,
1572 x_full_resp_code,
1573 x_document_id_txt
1574
1575 );
1576
1577 END add_row;
1578
1579
1580 PROCEDURE delete_row (
1581 x_rowid IN VARCHAR2
1582 ) AS
1583 /*
1584 || Created By : adhawan
1585 || Created On : 15-JAN-2001
1586 || Purpose : Handles the DELETE DML logic for the table.
1587 || Known limitations, enhancements or remarks :
1588 || Change History :
1589 || Who When What
1590 || (reverse chronological order - newest change first)
1591 */
1592 BEGIN
1593
1594 before_dml (
1595 p_action => 'DELETE',
1596 x_rowid => x_rowid
1597 );
1598
1599 DELETE FROM igf_gr_rfms_all
1600 WHERE rowid = x_rowid;
1601
1602 IF (SQL%NOTFOUND) THEN
1603 RAISE NO_DATA_FOUND;
1604 END IF;
1605
1606 END delete_row;
1607
1608
1609 END igf_gr_rfms_pkg;