[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_COD_HISTORY_PKG
Source
1 PACKAGE BODY igf_gr_cod_history_pkg AS
2 /* $Header: IGFGI23B.pls 120.0 2005/06/02 17:59:27 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_gr_cod_history%ROWTYPE;
6 new_references igf_gr_cod_history%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_rfms_orig_hist_id IN NUMBER ,
12 x_origination_id IN VARCHAR2,
13 x_award_id IN NUMBER,
14 x_document_id_txt IN VARCHAR2,
15 x_base_id IN NUMBER,
16 x_fin_award_year IN VARCHAR2,
17 x_cps_trans_num IN VARCHAR2,
18 x_award_amt IN NUMBER,
19 x_coa_amt IN NUMBER,
20 x_low_tution_fee IN VARCHAR2,
21 x_incarc_flag IN VARCHAR2,
22 x_ver_status_code IN VARCHAR2,
23 x_enrollment_date IN DATE,
24 x_sec_efc_code IN VARCHAR2,
25 x_ytd_disb_amt IN NUMBER,
26 x_tot_elig_used IN NUMBER,
27 x_schd_pell_amt IN NUMBER,
28 x_neg_pend_amt IN NUMBER,
29 x_cps_verif_flag IN VARCHAR2,
30 x_high_cps_trans_num IN VARCHAR2,
31 x_note_message IN VARCHAR2,
32 x_full_resp_code IN VARCHAR2,
33 x_atd_entity_id_txt IN VARCHAR2,
34 x_rep_entity_id_txt IN VARCHAR2,
35 x_source_entity_id_txt IN VARCHAR2,
36 x_pell_status IN VARCHAR2,
37 x_pell_status_date IN DATE,
38 x_s_chg_ssn IN VARCHAR2,
39 x_driver_lic_state IN VARCHAR2,
40 x_driver_lic_number IN VARCHAR2,
41 x_s_chg_date_of_birth IN DATE,
42 x_first_name IN VARCHAR2,
43 x_middle_name IN VARCHAR2,
44 x_s_chg_last_name IN VARCHAR2,
45 x_s_date_of_birth IN DATE,
46 x_s_ssn IN VARCHAR2,
47 x_s_last_name IN VARCHAR2,
48 x_permt_addr_foreign_flag IN VARCHAR2,
49 x_addr_type_code IN VARCHAR2,
50 x_permt_addr_line_1 IN VARCHAR2,
51 x_permt_addr_line_2 IN VARCHAR2,
52 x_permt_addr_line_3 IN VARCHAR2,
53 x_permt_addr_city IN VARCHAR2,
54 x_permt_addr_state_code IN VARCHAR2,
55 x_permt_addr_post_code IN VARCHAR2,
56 x_permt_addr_county IN VARCHAR2,
57 x_permt_addr_country IN VARCHAR2,
58 x_phone_number_1 IN VARCHAR2,
59 x_phone_number_2 IN VARCHAR2,
60 x_phone_number_3 IN VARCHAR2,
61 x_email_address IN VARCHAR2,
62 x_citzn_status_code IN VARCHAR2,
63 x_creation_date IN DATE,
64 x_created_by IN NUMBER,
65 x_last_update_date IN DATE,
66 x_last_updated_by IN NUMBER,
67 x_last_update_login IN NUMBER
68 ) AS
69 /*
70 || Created By : [email protected]
71 || Created On : 02-NOV-2004
72 || Purpose : Initialises the Old and New references for the columns of the table.
73 || Known limitations, enhancements or remarks :
74 || Change History :
75 || Who When What
76 || (reverse chronological order - newest change first)
77 */
78
79 CURSOR cur_old_ref_values IS
80 SELECT *
81 FROM igf_gr_cod_history
82 WHERE rowid = x_rowid;
83
84 BEGIN
85
86 l_rowid := x_rowid;
87
88 -- Code for setting the Old and New Reference Values.
89 -- Populate Old Values.
90 OPEN cur_old_ref_values;
91 FETCH cur_old_ref_values INTO old_references;
92 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
93 CLOSE cur_old_ref_values;
94 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
95 igs_ge_msg_stack.add;
96 app_exception.raise_exception;
97 RETURN;
98 END IF;
99 CLOSE cur_old_ref_values;
100
101 -- Populate New Values.
102 new_references.rfms_orig_hist_id := x_rfms_orig_hist_id;
103 new_references.origination_id := x_origination_id;
104 new_references.award_id := x_award_id;
105 new_references.document_id_txt := x_document_id_txt;
106 new_references.base_id := x_base_id;
107 new_references.fin_award_year := x_fin_award_year;
108 new_references.cps_trans_num := x_cps_trans_num;
109 new_references.award_amt := x_award_amt;
110 new_references.coa_amt := x_coa_amt;
111 new_references.low_tution_fee := x_low_tution_fee;
112 new_references.incarc_flag := x_incarc_flag;
113 new_references.ver_status_code := x_ver_status_code;
114 new_references.enrollment_date := x_enrollment_date;
115 new_references.sec_efc_code := x_sec_efc_code;
116 new_references.ytd_disb_amt := x_ytd_disb_amt;
117 new_references.tot_elig_used := x_tot_elig_used;
118 new_references.schd_pell_amt := x_schd_pell_amt;
119 new_references.neg_pend_amt := x_neg_pend_amt;
120 new_references.cps_verif_flag := x_cps_verif_flag;
121 new_references.high_cps_trans_num := x_high_cps_trans_num;
122 new_references.note_message := x_note_message;
123 new_references.full_resp_code := x_full_resp_code;
124 new_references.atd_entity_id_txt := x_atd_entity_id_txt;
125 new_references.rep_entity_id_txt := x_rep_entity_id_txt;
126 new_references.source_entity_id_txt := x_source_entity_id_txt;
127 new_references.pell_status := x_pell_status;
128 new_references.pell_status_date := x_pell_status_date;
129 new_references.s_chg_ssn := x_s_chg_ssn;
130 new_references.driver_lic_state := x_driver_lic_state;
131 new_references.driver_lic_number := x_driver_lic_number;
132 new_references.s_chg_date_of_birth := x_s_chg_date_of_birth;
133 new_references.first_name := x_first_name;
134 new_references.middle_name := x_middle_name;
135 new_references.s_chg_last_name := x_s_chg_last_name;
136 new_references.s_date_of_birth := x_s_date_of_birth;
137 new_references.s_ssn := x_s_ssn;
138 new_references.s_last_name := x_s_last_name;
139 new_references.permt_addr_foreign_flag := x_permt_addr_foreign_flag;
140 new_references.addr_type_code := x_addr_type_code;
141 new_references.permt_addr_line_1 := x_permt_addr_line_1;
142 new_references.permt_addr_line_2 := x_permt_addr_line_2;
143 new_references.permt_addr_line_3 := x_permt_addr_line_3;
144 new_references.permt_addr_city := x_permt_addr_city;
145 new_references.permt_addr_state_code := x_permt_addr_state_code;
146 new_references.permt_addr_post_code := x_permt_addr_post_code;
147 new_references.permt_addr_county := x_permt_addr_county;
148 new_references.permt_addr_country := x_permt_addr_country;
149 new_references.phone_number_1 := x_phone_number_1;
150 new_references.phone_number_2 := x_phone_number_2;
151 new_references.phone_number_3 := x_phone_number_3;
152 new_references.email_address := x_email_address;
153 new_references.citzn_status_code := x_citzn_status_code;
154
155 IF (p_action = 'UPDATE') THEN
156 new_references.creation_date := old_references.creation_date;
157 new_references.created_by := old_references.created_by;
158 ELSE
159 new_references.creation_date := x_creation_date;
160 new_references.created_by := x_created_by;
161 END IF;
162
163 new_references.last_update_date := x_last_update_date;
164 new_references.last_updated_by := x_last_updated_by;
165 new_references.last_update_login := x_last_update_login;
166
167 END set_column_values;
168
169 PROCEDURE check_parent_existance AS
170 /*
171 || Created By : [email protected]
172 || Created On : 21-Oct-2004
173 || Purpose : Checks for the existance of Parent records.
174 || Known limitations, enhancements or remarks :
175 || Change History :
176 || Who When What
177 || (reverse chronological order - newest change first)
178 */
179 BEGIN
180
181 IF (((old_references.award_id = new_references.award_id)) OR
182 ((new_references.award_id IS NULL))) THEN
183 NULL;
184 ELSIF NOT igf_aw_award_pkg.get_pk_for_validation (
185 new_references.award_id
186 ) THEN
187 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
188 igs_ge_msg_stack.add;
189 app_exception.raise_exception;
190 END IF;
191
192 END check_parent_existance;
193
194 FUNCTION get_pk_for_validation (
195 x_rfms_orig_hist_id IN NUMBER
196 ) RETURN BOOLEAN AS
197 /*
198 || Created By : [email protected]
199 || Created On : 21-OCT-2004
200 || Purpose : Validates the Primary Key of the table.
201 || Known limitations, enhancements or remarks :
202 || Change History :
203 || Who When What
204 || (reverse chronological order - newest change first)
205 */
206 CURSOR cur_rowid IS
207 SELECT rowid
208 FROM igf_gr_cod_history
209 WHERE rfms_orig_hist_id = x_rfms_orig_hist_id
210 FOR UPDATE NOWAIT;
211
212 lv_rowid cur_rowid%RowType;
213
214 BEGIN
215
216 OPEN cur_rowid;
217 FETCH cur_rowid INTO lv_rowid;
218 IF (cur_rowid%FOUND) THEN
219 CLOSE cur_rowid;
220 RETURN(TRUE);
221 ELSE
222 CLOSE cur_rowid;
223 RETURN(FALSE);
224 END IF;
225
226 END get_pk_for_validation;
227
228
229
230 PROCEDURE before_dml (
231 p_action IN VARCHAR2,
232 x_rowid IN VARCHAR2,
233 x_rfms_orig_hist_id IN NUMBER ,
234 x_origination_id IN VARCHAR2,
235 x_award_id IN NUMBER,
236 x_document_id_txt IN VARCHAR2,
237 x_base_id IN NUMBER,
238 x_fin_award_year IN VARCHAR2,
239 x_cps_trans_num IN VARCHAR2,
240 x_award_amt IN NUMBER,
241 x_coa_amt IN NUMBER,
242 x_low_tution_fee IN VARCHAR2,
243 x_incarc_flag IN VARCHAR2,
244 x_ver_status_code IN VARCHAR2,
245 x_enrollment_date IN DATE,
246 x_sec_efc_code IN VARCHAR2,
247 x_ytd_disb_amt IN NUMBER,
248 x_tot_elig_used IN NUMBER,
249 x_schd_pell_amt IN NUMBER,
250 x_neg_pend_amt IN NUMBER,
251 x_cps_verif_flag IN VARCHAR2,
252 x_high_cps_trans_num IN VARCHAR2,
253 x_note_message IN VARCHAR2,
254 x_full_resp_code IN VARCHAR2,
255 x_atd_entity_id_txt IN VARCHAR2,
256 x_rep_entity_id_txt IN VARCHAR2,
257 x_source_entity_id_txt IN VARCHAR2,
258 x_pell_status IN VARCHAR2,
259 x_pell_status_date IN DATE,
260 x_s_chg_ssn IN VARCHAR2,
261 x_driver_lic_state IN VARCHAR2,
262 x_driver_lic_number IN VARCHAR2,
263 x_s_chg_date_of_birth IN DATE,
264 x_first_name IN VARCHAR2,
265 x_middle_name IN VARCHAR2,
266 x_s_chg_last_name IN VARCHAR2,
267 x_s_date_of_birth IN DATE,
268 x_s_ssn IN VARCHAR2,
269 x_s_last_name IN VARCHAR2,
270 x_permt_addr_foreign_flag IN VARCHAR2,
271 x_addr_type_code IN VARCHAR2,
272 x_permt_addr_line_1 IN VARCHAR2,
273 x_permt_addr_line_2 IN VARCHAR2,
274 x_permt_addr_line_3 IN VARCHAR2,
275 x_permt_addr_city IN VARCHAR2,
276 x_permt_addr_state_code IN VARCHAR2,
277 x_permt_addr_post_code IN VARCHAR2,
278 x_permt_addr_county IN VARCHAR2,
279 x_permt_addr_country IN VARCHAR2,
280 x_phone_number_1 IN VARCHAR2,
281 x_phone_number_2 IN VARCHAR2,
282 x_phone_number_3 IN VARCHAR2,
283 x_email_address IN VARCHAR2,
284 x_citzn_status_code IN VARCHAR2,
285 x_creation_date IN DATE,
286 x_created_by IN NUMBER,
287 x_last_update_date IN DATE,
288 x_last_updated_by IN NUMBER,
289 x_last_update_login IN NUMBER
290 ) AS
291 /*
292 || Created By : [email protected]
293 || Created On : 02-NOV-2004
294 || Purpose : Initialises the columns, Checks Constraints, Calls the
295 || Trigger Handlers for the table, before any DML operation.
296 || Known limitations, enhancements or remarks :
297 || Change History :
298 || Who When What
299 || (reverse chronological order - newest change first)
300 */
301 BEGIN
302
303 set_column_values (
304 p_action,
305 x_rowid,
306 x_rfms_orig_hist_id,
307 x_origination_id,
308 x_award_id,
309 x_document_id_txt,
310 x_base_id,
311 x_fin_award_year,
312 x_cps_trans_num,
313 x_award_amt,
314 x_coa_amt,
315 x_low_tution_fee,
316 x_incarc_flag,
317 x_ver_status_code,
318 x_enrollment_date,
319 x_sec_efc_code,
320 x_ytd_disb_amt,
321 x_tot_elig_used,
322 x_schd_pell_amt,
323 x_neg_pend_amt,
324 x_cps_verif_flag,
325 x_high_cps_trans_num,
326 x_note_message,
327 x_full_resp_code,
328 x_atd_entity_id_txt,
329 x_rep_entity_id_txt,
330 x_source_entity_id_txt,
331 x_pell_status,
332 x_pell_status_date,
333 x_s_chg_ssn,
334 x_driver_lic_state,
335 x_driver_lic_number,
336 x_s_chg_date_of_birth,
337 x_first_name,
338 x_middle_name,
339 x_s_chg_last_name,
340 x_s_date_of_birth,
341 x_s_ssn,
342 x_s_last_name,
343 x_permt_addr_foreign_flag,
344 x_addr_type_code,
345 x_permt_addr_line_1,
346 x_permt_addr_line_2,
347 x_permt_addr_line_3,
348 x_permt_addr_city,
349 x_permt_addr_state_code,
350 x_permt_addr_post_code,
351 x_permt_addr_county,
352 x_permt_addr_country,
353 x_phone_number_1,
354 x_phone_number_2,
355 x_phone_number_3,
356 x_email_address,
357 x_citzn_status_code,
358 x_creation_date,
359 x_created_by,
360 x_last_update_date,
361 x_last_updated_by,
362 x_last_update_login
363 );
364
365 IF (p_action = 'INSERT') THEN
366 -- Call all the procedures related to Before Insert.
367 IF ( get_pk_for_validation(
368 new_references.rfms_orig_hist_id
369 )
370 ) THEN
371 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
372 igs_ge_msg_stack.add;
373 app_exception.raise_exception;
374 END IF;
375 ELSIF (p_action = 'VALIDATE_INSERT') THEN
376 -- Call all the procedures related to Before Insert.
377 IF ( get_pk_for_validation (
378 new_references.rfms_orig_hist_id
379 )
380 ) THEN
381 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
382 igs_ge_msg_stack.add;
383 app_exception.raise_exception;
384 END IF;
385 END IF;
386
387 END before_dml;
388
389
390 PROCEDURE insert_row (
391 x_rowid IN OUT NOCOPY VARCHAR2,
392 x_rfms_orig_hist_id IN OUT NOCOPY NUMBER ,
393 x_origination_id IN VARCHAR2,
394 x_award_id IN NUMBER,
395 x_document_id_txt IN VARCHAR2,
396 x_base_id IN NUMBER,
397 x_fin_award_year IN VARCHAR2,
398 x_cps_trans_num IN VARCHAR2,
399 x_award_amt IN NUMBER,
400 x_coa_amt IN NUMBER,
401 x_low_tution_fee IN VARCHAR2,
402 x_incarc_flag IN VARCHAR2,
403 x_ver_status_code IN VARCHAR2,
404 x_enrollment_date IN DATE,
405 x_sec_efc_code IN VARCHAR2,
406 x_ytd_disb_amt IN NUMBER,
407 x_tot_elig_used IN NUMBER,
408 x_schd_pell_amt IN NUMBER,
409 x_neg_pend_amt IN NUMBER,
410 x_cps_verif_flag IN VARCHAR2,
411 x_high_cps_trans_num IN VARCHAR2,
412 x_note_message IN VARCHAR2,
413 x_full_resp_code IN VARCHAR2,
414 x_atd_entity_id_txt IN VARCHAR2,
415 x_rep_entity_id_txt IN VARCHAR2,
416 x_source_entity_id_txt IN VARCHAR2,
417 x_pell_status IN VARCHAR2,
418 x_pell_status_date IN DATE,
419 x_s_chg_ssn IN VARCHAR2,
420 x_driver_lic_state IN VARCHAR2,
421 x_driver_lic_number IN VARCHAR2,
422 x_s_chg_date_of_birth IN DATE,
423 x_first_name IN VARCHAR2,
424 x_middle_name IN VARCHAR2,
425 x_s_chg_last_name IN VARCHAR2,
426 x_s_date_of_birth IN DATE,
427 x_s_ssn IN VARCHAR2,
428 x_s_last_name IN VARCHAR2,
429 x_permt_addr_foreign_flag IN VARCHAR2,
430 x_addr_type_code IN VARCHAR2,
431 x_permt_addr_line_1 IN VARCHAR2,
432 x_permt_addr_line_2 IN VARCHAR2,
433 x_permt_addr_line_3 IN VARCHAR2,
434 x_permt_addr_city IN VARCHAR2,
435 x_permt_addr_state_code IN VARCHAR2,
436 x_permt_addr_post_code IN VARCHAR2,
437 x_permt_addr_county IN VARCHAR2,
438 x_permt_addr_country IN VARCHAR2,
439 x_phone_number_1 IN VARCHAR2,
440 x_phone_number_2 IN VARCHAR2,
441 x_phone_number_3 IN VARCHAR2,
442 x_email_address IN VARCHAR2,
443 x_citzn_status_code IN VARCHAR2,
444 x_mode IN VARCHAR2
445 ) AS
446 /*
447 || Created By : [email protected]
448 || Created On : 02-NOV-2004
449 || Purpose : Handles the INSERT DML logic for the table.
450 || Known limitations, enhancements or remarks :
451 || Change History :
452 || Who When What
453 || (reverse chronological order - newest change first)
454 */
455
456 x_last_update_date DATE;
457 x_last_updated_by NUMBER;
458 x_last_update_login NUMBER;
459 x_request_id NUMBER;
460 x_program_id NUMBER;
461 x_program_application_id NUMBER;
462 x_program_update_date DATE;
463
464 BEGIN
465
466 x_last_update_date := SYSDATE;
467 IF (x_mode = 'I') THEN
468 x_last_updated_by := 1;
469 x_last_update_login := 0;
470 ELSIF (x_mode = 'R') THEN
471 x_last_updated_by := fnd_global.user_id;
472 IF (x_last_updated_by IS NULL) THEN
473 x_last_updated_by := -1;
474 END IF;
475 x_last_update_login := fnd_global.login_id;
476 IF (x_last_update_login IS NULL) THEN
477 x_last_update_login := -1;
478 END IF;
479 x_request_id := fnd_global.conc_request_id;
480 x_program_id := fnd_global.conc_program_id;
481 x_program_application_id := fnd_global.prog_appl_id;
482
483 IF (x_request_id = -1) THEN
484 x_request_id := NULL;
485 x_program_id := NULL;
486 x_program_application_id := NULL;
487 x_program_update_date := NULL;
488 ELSE
489 x_program_update_date := SYSDATE;
490 END IF;
491 ELSE
492 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
493 fnd_message.set_token ('ROUTINE', 'IGF_GR_COD_HISTORY_PKG.INSERT_ROW');
494 igs_ge_msg_stack.add;
495 app_exception.raise_exception;
496 END IF;
497
498 SELECT igf_gr_cod_history_s.NEXTVAL INTO x_rfms_orig_hist_id FROM dual;
499 before_dml(
500 p_action => 'INSERT',
501 x_rowid => x_rowid,
502 x_rfms_orig_hist_id => x_rfms_orig_hist_id,
503 x_origination_id => x_origination_id,
504 x_award_id => x_award_id,
505 x_document_id_txt => x_document_id_txt,
506 x_base_id => x_base_id,
507 x_fin_award_year => x_fin_award_year,
508 x_cps_trans_num => x_cps_trans_num,
509 x_award_amt => x_award_amt,
510 x_coa_amt => x_coa_amt,
511 x_low_tution_fee => x_low_tution_fee,
512 x_incarc_flag => x_incarc_flag,
513 x_ver_status_code => x_ver_status_code,
514 x_enrollment_date => x_enrollment_date,
515 x_sec_efc_code => x_sec_efc_code,
516 x_ytd_disb_amt => x_ytd_disb_amt,
517 x_tot_elig_used => x_tot_elig_used,
518 x_schd_pell_amt => x_schd_pell_amt,
519 x_neg_pend_amt => x_neg_pend_amt,
520 x_cps_verif_flag => x_cps_verif_flag,
521 x_high_cps_trans_num => x_high_cps_trans_num,
522 x_note_message => x_note_message,
523 x_full_resp_code => x_full_resp_code,
524 x_atd_entity_id_txt => x_atd_entity_id_txt,
525 x_rep_entity_id_txt => x_rep_entity_id_txt,
526 x_source_entity_id_txt => x_source_entity_id_txt,
527 x_pell_status => x_pell_status,
528 x_pell_status_date => x_pell_status_date,
529 x_s_chg_ssn => x_s_chg_ssn,
530 x_driver_lic_state => x_driver_lic_state,
531 x_driver_lic_number => x_driver_lic_number,
532 x_s_chg_date_of_birth => x_s_chg_date_of_birth,
533 x_first_name => x_first_name,
534 x_middle_name => x_middle_name,
535 x_s_chg_last_name => x_s_chg_last_name,
536 x_s_date_of_birth => x_s_date_of_birth,
537 x_s_ssn => x_s_ssn,
538 x_s_last_name => x_s_last_name,
539 x_permt_addr_foreign_flag => x_permt_addr_foreign_flag,
540 x_addr_type_code => x_addr_type_code,
541 x_permt_addr_line_1 => x_permt_addr_line_1,
542 x_permt_addr_line_2 => x_permt_addr_line_2,
543 x_permt_addr_line_3 => x_permt_addr_line_3,
544 x_permt_addr_city => x_permt_addr_city,
545 x_permt_addr_state_code => x_permt_addr_state_code,
546 x_permt_addr_post_code => x_permt_addr_post_code,
547 x_permt_addr_county => x_permt_addr_county,
548 x_permt_addr_country => x_permt_addr_country,
549 x_phone_number_1 => x_phone_number_1,
550 x_phone_number_2 => x_phone_number_2,
551 x_phone_number_3 => x_phone_number_3,
552 x_email_address => x_email_address,
553 x_citzn_status_code => x_citzn_status_code,
554 x_creation_date => x_last_update_date,
555 x_created_by => x_last_updated_by,
556 x_last_update_date => x_last_update_date,
557 x_last_updated_by => x_last_updated_by,
558 x_last_update_login => x_last_update_login
559 );
560
561 INSERT INTO igf_gr_cod_history (
562 rfms_orig_hist_id,
563 origination_id,
564 award_id,
565 document_id_txt,
566 base_id,
567 fin_award_year,
568 cps_trans_num,
569 award_amt,
570 coa_amt,
571 low_tution_fee,
572 incarc_flag,
573 ver_status_code,
574 enrollment_date,
575 sec_efc_code,
576 ytd_disb_amt,
577 tot_elig_used,
578 schd_pell_amt,
579 neg_pend_amt,
580 cps_verif_flag,
581 high_cps_trans_num,
582 note_message,
583 full_resp_code,
584 atd_entity_id_txt,
585 rep_entity_id_txt,
586 source_entity_id_txt,
587 pell_status,
588 pell_status_date,
589 s_chg_ssn,
590 driver_lic_state,
591 driver_lic_number,
592 s_chg_date_of_birth,
593 first_name,
594 middle_name,
595 s_chg_last_name,
596 s_date_of_birth,
597 s_ssn,
598 s_last_name,
599 permt_addr_foreign_flag,
600 addr_type_code,
601 permt_addr_line_1,
602 permt_addr_line_2,
603 permt_addr_line_3,
604 permt_addr_city,
605 permt_addr_state_code,
606 permt_addr_post_code,
607 permt_addr_county,
608 permt_addr_country,
609 phone_number_1,
610 phone_number_2,
611 phone_number_3,
612 email_address,
613 citzn_status_code,
614 creation_date,
615 created_by,
616 last_update_date,
617 last_updated_by,
618 last_update_login,
619 request_id,
620 program_id,
621 program_application_id,
622 program_update_date
623 ) VALUES (
624 new_references.rfms_orig_hist_id,
625 new_references.origination_id,
626 new_references.award_id,
627 new_references.document_id_txt,
628 new_references.base_id,
629 new_references.fin_award_year,
630 new_references.cps_trans_num,
631 new_references.award_amt,
632 new_references.coa_amt,
633 new_references.low_tution_fee,
634 new_references.incarc_flag,
635 new_references.ver_status_code,
636 new_references.enrollment_date,
637 new_references.sec_efc_code,
638 new_references.ytd_disb_amt,
639 new_references.tot_elig_used,
640 new_references.schd_pell_amt,
641 new_references.neg_pend_amt,
642 new_references.cps_verif_flag,
643 new_references.high_cps_trans_num,
644 new_references.note_message,
645 new_references.full_resp_code,
646 new_references.atd_entity_id_txt,
647 new_references.rep_entity_id_txt,
648 new_references.source_entity_id_txt,
649 new_references.pell_status,
650 new_references.pell_status_date,
651 new_references.s_chg_ssn,
652 new_references.driver_lic_state,
653 new_references.driver_lic_number,
654 new_references.s_chg_date_of_birth,
655 new_references.first_name,
656 new_references.middle_name,
657 new_references.s_chg_last_name,
658 new_references.s_date_of_birth,
659 new_references.s_ssn,
660 new_references.s_last_name,
661 new_references.permt_addr_foreign_flag,
662 new_references.addr_type_code,
663 new_references.permt_addr_line_1,
664 new_references.permt_addr_line_2,
665 new_references.permt_addr_line_3,
666 new_references.permt_addr_city,
667 new_references.permt_addr_state_code,
668 new_references.permt_addr_post_code,
669 new_references.permt_addr_county,
670 new_references.permt_addr_country,
671 new_references.phone_number_1,
672 new_references.phone_number_2,
673 new_references.phone_number_3,
674 new_references.email_address,
675 new_references.citzn_status_code,
676 x_last_update_date,
677 x_last_updated_by,
678 x_last_update_date,
679 x_last_updated_by,
680 x_last_update_login ,
681 x_request_id,
682 x_program_id,
683 x_program_application_id,
684 x_program_update_date
685 ) RETURNING ROWID INTO x_rowid;
686
687 END insert_row;
688
689
690 PROCEDURE lock_row (
691 x_rowid IN VARCHAR2,
692 x_rfms_orig_hist_id IN NUMBER,
693 x_origination_id IN VARCHAR2,
694 x_award_id IN NUMBER,
695 x_document_id_txt IN VARCHAR2,
696 x_base_id IN NUMBER,
697 x_fin_award_year IN VARCHAR2,
698 x_cps_trans_num IN VARCHAR2,
699 x_award_amt IN NUMBER,
700 x_coa_amt IN NUMBER,
701 x_low_tution_fee IN VARCHAR2,
702 x_incarc_flag IN VARCHAR2,
703 x_ver_status_code IN VARCHAR2,
704 x_enrollment_date IN DATE,
705 x_sec_efc_code IN VARCHAR2,
706 x_ytd_disb_amt IN NUMBER,
707 x_tot_elig_used IN NUMBER,
708 x_schd_pell_amt IN NUMBER,
709 x_neg_pend_amt IN NUMBER,
710 x_cps_verif_flag IN VARCHAR2,
711 x_high_cps_trans_num IN VARCHAR2,
712 x_note_message IN VARCHAR2,
713 x_full_resp_code IN VARCHAR2,
714 x_atd_entity_id_txt IN VARCHAR2,
715 x_rep_entity_id_txt IN VARCHAR2,
716 x_source_entity_id_txt IN VARCHAR2,
717 x_pell_status IN VARCHAR2,
718 x_pell_status_date IN DATE,
719 x_s_chg_ssn IN VARCHAR2,
720 x_driver_lic_state IN VARCHAR2,
721 x_driver_lic_number IN VARCHAR2,
722 x_s_chg_date_of_birth IN DATE,
723 x_first_name IN VARCHAR2,
724 x_middle_name IN VARCHAR2,
725 x_s_chg_last_name IN VARCHAR2,
726 x_s_date_of_birth IN DATE,
727 x_s_ssn IN VARCHAR2,
728 x_s_last_name IN VARCHAR2,
729 x_permt_addr_foreign_flag IN VARCHAR2,
730 x_addr_type_code IN VARCHAR2,
731 x_permt_addr_line_1 IN VARCHAR2,
732 x_permt_addr_line_2 IN VARCHAR2,
733 x_permt_addr_line_3 IN VARCHAR2,
734 x_permt_addr_city IN VARCHAR2,
735 x_permt_addr_state_code IN VARCHAR2,
736 x_permt_addr_post_code IN VARCHAR2,
737 x_permt_addr_county IN VARCHAR2,
738 x_permt_addr_country IN VARCHAR2,
739 x_phone_number_1 IN VARCHAR2,
740 x_phone_number_2 IN VARCHAR2,
741 x_phone_number_3 IN VARCHAR2,
742 x_email_address IN VARCHAR2,
743 x_citzn_status_code IN VARCHAR2
744 ) AS
745 /*
746 || Created By : [email protected]
747 || Created On : 02-NOV-2004
748 || Purpose : Handles the LOCK mechanism for the table.
749 || Known limitations, enhancements or remarks :
750 || Change History :
751 || Who When What
752 || (reverse chronological order - newest change first)
753 */
754 CURSOR c1 IS
755 SELECT
756 rfms_orig_hist_id,
757 origination_id,
758 award_id,
759 document_id_txt,
760 base_id,
761 fin_award_year,
762 cps_trans_num,
763 award_amt,
764 coa_amt,
765 low_tution_fee,
766 incarc_flag,
767 ver_status_code,
768 enrollment_date,
769 sec_efc_code,
770 ytd_disb_amt,
771 tot_elig_used,
772 schd_pell_amt,
773 neg_pend_amt,
774 cps_verif_flag,
775 high_cps_trans_num,
776 note_message,
777 full_resp_code,
778 atd_entity_id_txt,
779 rep_entity_id_txt,
780 source_entity_id_txt,
781 pell_status,
782 pell_status_date,
783 s_chg_ssn,
784 driver_lic_state,
785 driver_lic_number,
786 s_chg_date_of_birth,
787 first_name,
788 middle_name,
789 s_chg_last_name,
790 s_date_of_birth,
791 s_ssn,
792 s_last_name,
793 permt_addr_foreign_flag,
794 addr_type_code,
795 permt_addr_line_1,
796 permt_addr_line_2,
797 permt_addr_line_3,
798 permt_addr_city,
799 permt_addr_state_code,
800 permt_addr_post_code,
801 permt_addr_county,
802 permt_addr_country,
803 phone_number_1,
804 phone_number_2,
805 phone_number_3,
806 email_address,
807 citzn_status_code
808 FROM igf_gr_cod_history
809 WHERE rowid = x_rowid
810 FOR UPDATE NOWAIT;
811
812 tlinfo c1%ROWTYPE;
813
814 BEGIN
815
816 OPEN c1;
817 FETCH c1 INTO tlinfo;
818 IF (c1%notfound) THEN
819 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
820 igs_ge_msg_stack.add;
821 CLOSE c1;
822 app_exception.raise_exception;
823 RETURN;
824 END IF;
825 CLOSE c1;
826
827 IF (
828 (tlinfo.rfms_orig_hist_id = x_rfms_orig_hist_id)
829 AND (tlinfo.origination_id = x_origination_id)
830 AND (tlinfo.award_id = x_award_id)
831 AND (tlinfo.document_id_txt = x_document_id_txt)
832 AND (tlinfo.base_id = x_base_id)
833 AND ((tlinfo.fin_award_year = x_fin_award_year) OR ((tlinfo.fin_award_year IS NULL) AND (X_fin_award_year IS NULL)))
834 AND ((tlinfo.cps_trans_num = x_cps_trans_num) OR ((tlinfo.cps_trans_num IS NULL) AND (X_cps_trans_num IS NULL)))
835 AND (tlinfo.award_amt = x_award_amt)
836 AND ((tlinfo.coa_amt = x_coa_amt) OR ((tlinfo.coa_amt IS NULL) AND (X_coa_amt IS NULL)))
837 AND ((tlinfo.low_tution_fee = x_low_tution_fee) OR ((tlinfo.low_tution_fee IS NULL) AND (X_low_tution_fee IS NULL)))
838 AND ((tlinfo.incarc_flag = x_incarc_flag) OR ((tlinfo.incarc_flag IS NULL) AND (X_incarc_flag IS NULL)))
839 AND ((tlinfo.ver_status_code = x_ver_status_code) OR ((tlinfo.ver_status_code IS NULL) AND (X_ver_status_code IS NULL)))
840 AND ((tlinfo.enrollment_date = x_enrollment_date) OR ((tlinfo.enrollment_date IS NULL) AND (X_enrollment_date IS NULL)))
841 AND ((tlinfo.sec_efc_code = x_sec_efc_code) OR ((tlinfo.sec_efc_code IS NULL) AND (X_sec_efc_code IS NULL)))
842 AND ((tlinfo.ytd_disb_amt = x_ytd_disb_amt) OR ((tlinfo.ytd_disb_amt IS NULL) AND (X_ytd_disb_amt IS NULL)))
843 AND ((tlinfo.tot_elig_used = x_tot_elig_used) OR ((tlinfo.tot_elig_used IS NULL) AND (X_tot_elig_used IS NULL)))
844 AND ((tlinfo.schd_pell_amt = x_schd_pell_amt) OR ((tlinfo.schd_pell_amt IS NULL) AND (X_schd_pell_amt IS NULL)))
845 AND ((tlinfo.neg_pend_amt = x_neg_pend_amt) OR ((tlinfo.neg_pend_amt IS NULL) AND (X_neg_pend_amt IS NULL)))
846 AND ((tlinfo.cps_verif_flag = x_cps_verif_flag) OR ((tlinfo.cps_verif_flag IS NULL) AND (X_cps_verif_flag IS NULL)))
847 AND ((tlinfo.high_cps_trans_num = x_high_cps_trans_num) OR ((tlinfo.high_cps_trans_num IS NULL) AND (X_high_cps_trans_num IS NULL)))
848 AND ((tlinfo.note_message = x_note_message) OR ((tlinfo.note_message IS NULL) AND (X_note_message IS NULL)))
849 AND ((tlinfo.full_resp_code = x_full_resp_code) OR ((tlinfo.full_resp_code IS NULL) AND (X_full_resp_code IS NULL)))
850 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)))
851 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)))
852 AND ((tlinfo.source_entity_id_txt = x_source_entity_id_txt) OR ((tlinfo.source_entity_id_txt IS NULL) AND (X_source_entity_id_txt IS NULL)))
853 AND ((tlinfo.pell_status = x_pell_status) OR ((tlinfo.pell_status IS NULL) AND (X_pell_status IS NULL)))
854 AND ((tlinfo.pell_status_date = x_pell_status_date) OR ((tlinfo.pell_status_date IS NULL) AND (X_pell_status_date IS NULL)))
855 AND ((tlinfo.s_chg_ssn = x_s_chg_ssn) OR ((tlinfo.s_chg_ssn IS NULL) AND (X_s_chg_ssn IS NULL)))
856 AND ((tlinfo.driver_lic_state = x_driver_lic_state) OR ((tlinfo.driver_lic_state IS NULL) AND (X_driver_lic_state IS NULL)))
857 AND ((tlinfo.driver_lic_number = x_driver_lic_number) OR ((tlinfo.driver_lic_number IS NULL) AND (X_driver_lic_number IS NULL)))
858 AND ((tlinfo.s_chg_date_of_birth = x_s_chg_date_of_birth) OR ((tlinfo.s_chg_date_of_birth IS NULL) AND (X_s_chg_date_of_birth IS NULL)))
859 AND ((tlinfo.first_name = x_first_name) OR ((tlinfo.first_name IS NULL) AND (X_first_name IS NULL)))
860 AND ((tlinfo.middle_name = x_middle_name) OR ((tlinfo.middle_name IS NULL) AND (X_middle_name IS NULL)))
861 AND ((tlinfo.s_chg_last_name = x_s_chg_last_name) OR ((tlinfo.s_chg_last_name IS NULL) AND (X_s_chg_last_name IS NULL)))
862 AND ((tlinfo.s_date_of_birth = x_s_date_of_birth) OR ((tlinfo.s_date_of_birth IS NULL) AND (X_s_date_of_birth IS NULL)))
863 AND ((tlinfo.s_ssn = x_s_ssn) OR ((tlinfo.s_ssn IS NULL) AND (X_s_ssn IS NULL)))
864 AND ((tlinfo.s_last_name = x_s_last_name) OR ((tlinfo.s_last_name IS NULL) AND (X_s_last_name IS NULL)))
865 AND ((tlinfo.permt_addr_foreign_flag = x_permt_addr_foreign_flag) OR ((tlinfo.permt_addr_foreign_flag IS NULL) AND (X_permt_addr_foreign_flag IS NULL)))
866 AND ((tlinfo.addr_type_code = x_addr_type_code) OR ((tlinfo.addr_type_code IS NULL) AND (X_addr_type_code IS NULL)))
867 AND ((tlinfo.permt_addr_line_1 = x_permt_addr_line_1) OR ((tlinfo.permt_addr_line_1 IS NULL) AND (X_permt_addr_line_1 IS NULL)))
868 AND ((tlinfo.permt_addr_line_2 = x_permt_addr_line_2) OR ((tlinfo.permt_addr_line_2 IS NULL) AND (X_permt_addr_line_2 IS NULL)))
869 AND ((tlinfo.permt_addr_line_3 = x_permt_addr_line_3) OR ((tlinfo.permt_addr_line_3 IS NULL) AND (X_permt_addr_line_3 IS NULL)))
870 AND ((tlinfo.permt_addr_city = x_permt_addr_city) OR ((tlinfo.permt_addr_city IS NULL) AND (X_permt_addr_city IS NULL)))
871 AND ((tlinfo.permt_addr_state_code = x_permt_addr_state_code) OR ((tlinfo.permt_addr_state_code IS NULL) AND (X_permt_addr_state_code IS NULL)))
872 AND ((tlinfo.permt_addr_post_code = x_permt_addr_post_code) OR ((tlinfo.permt_addr_post_code IS NULL) AND (X_permt_addr_post_code IS NULL)))
873 AND ((tlinfo.permt_addr_county = x_permt_addr_county) OR ((tlinfo.permt_addr_county IS NULL) AND (X_permt_addr_county IS NULL)))
874 AND ((tlinfo.permt_addr_country = x_permt_addr_country) OR ((tlinfo.permt_addr_country IS NULL) AND (X_permt_addr_country IS NULL)))
875 AND ((tlinfo.phone_number_1 = x_phone_number_1) OR ((tlinfo.phone_number_1 IS NULL) AND (X_phone_number_1 IS NULL)))
876 AND ((tlinfo.phone_number_2 = x_phone_number_2) OR ((tlinfo.phone_number_2 IS NULL) AND (X_phone_number_2 IS NULL)))
877 AND ((tlinfo.phone_number_3 = x_phone_number_3) OR ((tlinfo.phone_number_3 IS NULL) AND (X_phone_number_3 IS NULL)))
878 AND ((tlinfo.email_address = x_email_address) OR ((tlinfo.email_address IS NULL) AND (X_email_address IS NULL)))
879 AND ((tlinfo.citzn_status_code = x_citzn_status_code) OR ((tlinfo.citzn_status_code IS NULL) AND (X_citzn_status_code IS NULL)))
880 ) THEN
881 NULL;
882 ELSE
883 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
884 igs_ge_msg_stack.add;
885 app_exception.raise_exception;
886 END IF;
887
888 RETURN;
889
890 END lock_row;
891
892
893 PROCEDURE update_row (
894 x_rowid IN VARCHAR2,
895 x_rfms_orig_hist_id IN NUMBER,
896 x_origination_id IN VARCHAR2,
897 x_award_id IN NUMBER,
898 x_document_id_txt IN VARCHAR2,
899 x_base_id IN NUMBER,
900 x_fin_award_year IN VARCHAR2,
901 x_cps_trans_num IN VARCHAR2,
902 x_award_amt IN NUMBER,
903 x_coa_amt IN NUMBER,
904 x_low_tution_fee IN VARCHAR2,
905 x_incarc_flag IN VARCHAR2,
906 x_ver_status_code IN VARCHAR2,
907 x_enrollment_date IN DATE,
908 x_sec_efc_code IN VARCHAR2,
909 x_ytd_disb_amt IN NUMBER,
910 x_tot_elig_used IN NUMBER,
911 x_schd_pell_amt IN NUMBER,
912 x_neg_pend_amt IN NUMBER,
913 x_cps_verif_flag IN VARCHAR2,
914 x_high_cps_trans_num IN VARCHAR2,
915 x_note_message IN VARCHAR2,
916 x_full_resp_code IN VARCHAR2,
917 x_atd_entity_id_txt IN VARCHAR2,
918 x_rep_entity_id_txt IN VARCHAR2,
919 x_source_entity_id_txt IN VARCHAR2,
920 x_pell_status IN VARCHAR2,
921 x_pell_status_date IN DATE,
922 x_s_chg_ssn IN VARCHAR2,
923 x_driver_lic_state IN VARCHAR2,
924 x_driver_lic_number IN VARCHAR2,
925 x_s_chg_date_of_birth IN DATE,
926 x_first_name IN VARCHAR2,
927 x_middle_name IN VARCHAR2,
928 x_s_chg_last_name IN VARCHAR2,
929 x_s_date_of_birth IN DATE,
930 x_s_ssn IN VARCHAR2,
931 x_s_last_name IN VARCHAR2,
932 x_permt_addr_foreign_flag IN VARCHAR2,
933 x_addr_type_code IN VARCHAR2,
934 x_permt_addr_line_1 IN VARCHAR2,
935 x_permt_addr_line_2 IN VARCHAR2,
936 x_permt_addr_line_3 IN VARCHAR2,
937 x_permt_addr_city IN VARCHAR2,
938 x_permt_addr_state_code IN VARCHAR2,
939 x_permt_addr_post_code IN VARCHAR2,
940 x_permt_addr_county IN VARCHAR2,
941 x_permt_addr_country IN VARCHAR2,
942 x_phone_number_1 IN VARCHAR2,
943 x_phone_number_2 IN VARCHAR2,
944 x_phone_number_3 IN VARCHAR2,
945 x_email_address IN VARCHAR2,
946 x_citzn_status_code IN VARCHAR2,
947 x_mode IN VARCHAR2
948 ) AS
949 /*
950 || Created By : [email protected]
951 || Created On : 02-NOV-2004
952 || Purpose : Handles the UPDATE DML logic for the table.
953 || Known limitations, enhancements or remarks :
954 || Change History :
955 || Who When What
956 || (reverse chronological order - newest change first)
957 */
958 x_last_update_date DATE ;
959 x_last_updated_by NUMBER;
960 x_last_update_login NUMBER;
961 x_request_id NUMBER;
962 x_program_id NUMBER;
963 x_program_application_id NUMBER;
964 x_program_update_date DATE;
965
966 BEGIN
967
968 x_last_update_date := SYSDATE;
969 IF (X_MODE = 'I') THEN
970 x_last_updated_by := 1;
971 x_last_update_login := 0;
972 ELSIF (x_mode = 'R') THEN
973 x_last_updated_by := fnd_global.user_id;
974 IF x_last_updated_by IS NULL THEN
975 x_last_updated_by := -1;
976 END IF;
977 x_last_update_login := fnd_global.login_id;
978 IF (x_last_update_login IS NULL) THEN
979 x_last_update_login := -1;
980 END IF;
981 ELSE
982 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
983 fnd_message.set_token ('ROUTINE', 'IGF_GR_COD_HISTORY_PKG.UPDATE_ROW');
984 igs_ge_msg_stack.add;
985 app_exception.raise_exception;
986 END IF;
987
988 before_dml(
989 p_action => 'UPDATE',
990 x_rowid => x_rowid,
991 x_rfms_orig_hist_id => x_rfms_orig_hist_id,
992 x_origination_id => x_origination_id,
993 x_award_id => x_award_id,
994 x_document_id_txt => x_document_id_txt,
995 x_base_id => x_base_id,
996 x_fin_award_year => x_fin_award_year,
997 x_cps_trans_num => x_cps_trans_num,
998 x_award_amt => x_award_amt,
999 x_coa_amt => x_coa_amt,
1000 x_low_tution_fee => x_low_tution_fee,
1001 x_incarc_flag => x_incarc_flag,
1002 x_ver_status_code => x_ver_status_code,
1003 x_enrollment_date => x_enrollment_date,
1004 x_sec_efc_code => x_sec_efc_code,
1005 x_ytd_disb_amt => x_ytd_disb_amt,
1006 x_tot_elig_used => x_tot_elig_used,
1007 x_schd_pell_amt => x_schd_pell_amt,
1008 x_neg_pend_amt => x_neg_pend_amt,
1009 x_cps_verif_flag => x_cps_verif_flag,
1010 x_high_cps_trans_num => x_high_cps_trans_num,
1011 x_note_message => x_note_message,
1012 x_full_resp_code => x_full_resp_code,
1013 x_atd_entity_id_txt => x_atd_entity_id_txt,
1014 x_rep_entity_id_txt => x_rep_entity_id_txt,
1015 x_source_entity_id_txt => x_source_entity_id_txt,
1016 x_pell_status => x_pell_status,
1017 x_pell_status_date => x_pell_status_date,
1018 x_s_chg_ssn => x_s_chg_ssn,
1019 x_driver_lic_state => x_driver_lic_state,
1020 x_driver_lic_number => x_driver_lic_number,
1021 x_s_chg_date_of_birth => x_s_chg_date_of_birth,
1022 x_first_name => x_first_name,
1023 x_middle_name => x_middle_name,
1024 x_s_chg_last_name => x_s_chg_last_name,
1025 x_s_date_of_birth => x_s_date_of_birth,
1026 x_s_ssn => x_s_ssn,
1027 x_s_last_name => x_s_last_name,
1028 x_permt_addr_foreign_flag => x_permt_addr_foreign_flag,
1029 x_addr_type_code => x_addr_type_code,
1030 x_permt_addr_line_1 => x_permt_addr_line_1,
1031 x_permt_addr_line_2 => x_permt_addr_line_2,
1032 x_permt_addr_line_3 => x_permt_addr_line_3,
1033 x_permt_addr_city => x_permt_addr_city,
1034 x_permt_addr_state_code => x_permt_addr_state_code,
1035 x_permt_addr_post_code => x_permt_addr_post_code,
1036 x_permt_addr_county => x_permt_addr_county,
1037 x_permt_addr_country => x_permt_addr_country,
1038 x_phone_number_1 => x_phone_number_1,
1039 x_phone_number_2 => x_phone_number_2,
1040 x_phone_number_3 => x_phone_number_3,
1041 x_email_address => x_email_address,
1042 x_citzn_status_code => x_citzn_status_code,
1043 x_creation_date => x_last_update_date,
1044 x_created_by => x_last_updated_by,
1045 x_last_update_date => x_last_update_date,
1046 x_last_updated_by => x_last_updated_by,
1047 x_last_update_login => x_last_update_login
1048 );
1049
1050 IF (x_mode = 'R') THEN
1051 x_request_id := fnd_global.conc_request_id;
1052 x_program_id := fnd_global.conc_program_id;
1053 x_program_application_id := fnd_global.prog_appl_id;
1054 IF (x_request_id = -1) THEN
1055 x_request_id := old_references.request_id;
1056 x_program_id := old_references.program_id;
1057 x_program_application_id := old_references.program_application_id;
1058 x_program_update_date := old_references.program_update_date;
1059 ELSE
1060 x_program_update_date := SYSDATE;
1061 END IF;
1062 END IF;
1063
1064 UPDATE igf_gr_cod_history
1065 SET
1066 rfms_orig_hist_id = new_references.rfms_orig_hist_id,
1067 origination_id = new_references.origination_id,
1068 award_id = new_references.award_id,
1069 document_id_txt = new_references.document_id_txt,
1070 base_id = new_references.base_id,
1071 fin_award_year = new_references.fin_award_year,
1072 cps_trans_num = new_references.cps_trans_num,
1073 award_amt = new_references.award_amt,
1074 coa_amt = new_references.coa_amt,
1075 low_tution_fee = new_references.low_tution_fee,
1076 incarc_flag = new_references.incarc_flag,
1077 ver_status_code = new_references.ver_status_code,
1078 enrollment_date = new_references.enrollment_date,
1079 sec_efc_code = new_references.sec_efc_code,
1080 ytd_disb_amt = new_references.ytd_disb_amt,
1081 tot_elig_used = new_references.tot_elig_used,
1082 schd_pell_amt = new_references.schd_pell_amt,
1083 neg_pend_amt = new_references.neg_pend_amt,
1084 cps_verif_flag = new_references.cps_verif_flag,
1085 high_cps_trans_num = new_references.high_cps_trans_num,
1086 note_message = new_references.note_message,
1087 full_resp_code = new_references.full_resp_code,
1088 atd_entity_id_txt = new_references.atd_entity_id_txt,
1089 rep_entity_id_txt = new_references.rep_entity_id_txt,
1090 source_entity_id_txt = new_references.source_entity_id_txt,
1091 pell_status = new_references.pell_status,
1092 pell_status_date = new_references.pell_status_date,
1093 s_chg_ssn = new_references.s_chg_ssn,
1094 driver_lic_state = new_references.driver_lic_state,
1095 driver_lic_number = new_references.driver_lic_number,
1096 s_chg_date_of_birth = new_references.s_chg_date_of_birth,
1097 first_name = new_references.first_name,
1098 middle_name = new_references.middle_name,
1099 s_chg_last_name = new_references.s_chg_last_name,
1100 s_date_of_birth = new_references.s_date_of_birth,
1101 s_ssn = new_references.s_ssn,
1102 s_last_name = new_references.s_last_name,
1103 permt_addr_foreign_flag = new_references.permt_addr_foreign_flag,
1104 addr_type_code = new_references.addr_type_code,
1105 permt_addr_line_1 = new_references.permt_addr_line_1,
1106 permt_addr_line_2 = new_references.permt_addr_line_2,
1107 permt_addr_line_3 = new_references.permt_addr_line_3,
1108 permt_addr_city = new_references.permt_addr_city,
1109 permt_addr_state_code = new_references.permt_addr_state_code,
1110 permt_addr_post_code = new_references.permt_addr_post_code,
1111 permt_addr_county = new_references.permt_addr_county,
1112 permt_addr_country = new_references.permt_addr_country,
1113 phone_number_1 = new_references.phone_number_1,
1114 phone_number_2 = new_references.phone_number_2,
1115 phone_number_3 = new_references.phone_number_3,
1116 email_address = new_references.email_address,
1117 citzn_status_code = new_references.citzn_status_code,
1118 last_update_date = x_last_update_date,
1119 last_updated_by = x_last_updated_by,
1120 last_update_login = x_last_update_login ,
1121 request_id = x_request_id,
1122 program_id = x_program_id,
1123 program_application_id = x_program_application_id,
1124 program_update_date = x_program_update_date
1125 WHERE rowid = x_rowid;
1126
1127 IF (SQL%NOTFOUND) THEN
1128 RAISE NO_DATA_FOUND;
1129 END IF;
1130
1131 END update_row;
1132
1133
1134 PROCEDURE add_row (
1135 x_rowid IN OUT NOCOPY VARCHAR2,
1136 x_rfms_orig_hist_id IN OUT NOCOPY NUMBER,
1137 x_origination_id IN VARCHAR2,
1138 x_award_id IN NUMBER,
1139 x_document_id_txt IN VARCHAR2,
1140 x_base_id IN NUMBER,
1141 x_fin_award_year IN VARCHAR2,
1142 x_cps_trans_num IN VARCHAR2,
1143 x_award_amt IN NUMBER,
1144 x_coa_amt IN NUMBER,
1145 x_low_tution_fee IN VARCHAR2,
1146 x_incarc_flag IN VARCHAR2,
1147 x_ver_status_code IN VARCHAR2,
1148 x_enrollment_date IN DATE,
1149 x_sec_efc_code IN VARCHAR2,
1150 x_ytd_disb_amt IN NUMBER,
1151 x_tot_elig_used IN NUMBER,
1152 x_schd_pell_amt IN NUMBER,
1153 x_neg_pend_amt IN NUMBER,
1154 x_cps_verif_flag IN VARCHAR2,
1155 x_high_cps_trans_num IN VARCHAR2,
1156 x_note_message IN VARCHAR2,
1157 x_full_resp_code IN VARCHAR2,
1158 x_atd_entity_id_txt IN VARCHAR2,
1159 x_rep_entity_id_txt IN VARCHAR2,
1160 x_source_entity_id_txt IN VARCHAR2,
1161 x_pell_status IN VARCHAR2,
1162 x_pell_status_date IN DATE,
1163 x_s_chg_ssn IN VARCHAR2,
1164 x_driver_lic_state IN VARCHAR2,
1165 x_driver_lic_number IN VARCHAR2,
1166 x_s_chg_date_of_birth IN DATE,
1167 x_first_name IN VARCHAR2,
1168 x_middle_name IN VARCHAR2,
1169 x_s_chg_last_name IN VARCHAR2,
1170 x_s_date_of_birth IN DATE,
1171 x_s_ssn IN VARCHAR2,
1172 x_s_last_name IN VARCHAR2,
1173 x_permt_addr_foreign_flag IN VARCHAR2,
1174 x_addr_type_code IN VARCHAR2,
1175 x_permt_addr_line_1 IN VARCHAR2,
1176 x_permt_addr_line_2 IN VARCHAR2,
1177 x_permt_addr_line_3 IN VARCHAR2,
1178 x_permt_addr_city IN VARCHAR2,
1179 x_permt_addr_state_code IN VARCHAR2,
1180 x_permt_addr_post_code IN VARCHAR2,
1181 x_permt_addr_county IN VARCHAR2,
1182 x_permt_addr_country IN VARCHAR2,
1183 x_phone_number_1 IN VARCHAR2,
1184 x_phone_number_2 IN VARCHAR2,
1185 x_phone_number_3 IN VARCHAR2,
1186 x_email_address IN VARCHAR2,
1187 x_citzn_status_code IN VARCHAR2,
1188 x_mode IN VARCHAR2
1189 ) AS
1190 /*
1191 || Created By : [email protected]
1192 || Created On : 02-NOV-2004
1193 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1194 || Known limitations, enhancements or remarks :
1195 || Change History :
1196 || Who When What
1197 || (reverse chronological order - newest change first)
1198 */
1199 CURSOR c1 IS
1200 SELECT rowid
1201 FROM igf_gr_cod_history
1202 WHERE rfms_orig_hist_id = x_rfms_orig_hist_id;
1203
1204 BEGIN
1205
1206 OPEN c1;
1207 FETCH c1 INTO x_rowid;
1208 IF (c1%NOTFOUND) THEN
1209 CLOSE c1;
1210
1211 insert_row (
1212 x_rowid,
1213 x_rfms_orig_hist_id,
1214 x_origination_id,
1215 x_award_id,
1216 x_document_id_txt,
1217 x_base_id,
1218 x_fin_award_year,
1219 x_cps_trans_num,
1220 x_award_amt,
1221 x_coa_amt,
1222 x_low_tution_fee,
1223 x_incarc_flag,
1224 x_ver_status_code,
1225 x_enrollment_date,
1226 x_sec_efc_code,
1227 x_ytd_disb_amt,
1228 x_tot_elig_used,
1229 x_schd_pell_amt,
1230 x_neg_pend_amt,
1231 x_cps_verif_flag,
1232 x_high_cps_trans_num,
1233 x_note_message,
1234 x_full_resp_code,
1235 x_atd_entity_id_txt,
1236 x_rep_entity_id_txt,
1237 x_source_entity_id_txt,
1238 x_pell_status,
1239 x_pell_status_date,
1240 x_s_chg_ssn,
1241 x_driver_lic_state,
1242 x_driver_lic_number,
1243 x_s_chg_date_of_birth,
1244 x_first_name,
1245 x_middle_name,
1246 x_s_chg_last_name,
1247 x_s_date_of_birth,
1248 x_s_ssn,
1249 x_s_last_name,
1250 x_permt_addr_foreign_flag,
1251 x_addr_type_code,
1252 x_permt_addr_line_1,
1253 x_permt_addr_line_2,
1254 x_permt_addr_line_3,
1255 x_permt_addr_city,
1256 x_permt_addr_state_code,
1257 x_permt_addr_post_code,
1258 x_permt_addr_county,
1259 x_permt_addr_country,
1260 x_phone_number_1,
1261 x_phone_number_2,
1262 x_phone_number_3,
1263 x_email_address,
1264 x_citzn_status_code,
1265 x_mode
1266 );
1267 RETURN;
1268 END IF;
1269 CLOSE c1;
1270
1271 update_row (
1272 x_rowid,
1273 x_rfms_orig_hist_id,
1274 x_origination_id,
1275 x_award_id,
1276 x_document_id_txt,
1277 x_base_id,
1278 x_fin_award_year,
1279 x_cps_trans_num,
1280 x_award_amt,
1281 x_coa_amt,
1282 x_low_tution_fee,
1283 x_incarc_flag,
1284 x_ver_status_code,
1285 x_enrollment_date,
1286 x_sec_efc_code,
1287 x_ytd_disb_amt,
1288 x_tot_elig_used,
1289 x_schd_pell_amt,
1290 x_neg_pend_amt,
1291 x_cps_verif_flag,
1292 x_high_cps_trans_num,
1293 x_note_message,
1294 x_full_resp_code,
1295 x_atd_entity_id_txt,
1296 x_rep_entity_id_txt,
1297 x_source_entity_id_txt,
1298 x_pell_status,
1299 x_pell_status_date,
1300 x_s_chg_ssn,
1301 x_driver_lic_state,
1302 x_driver_lic_number,
1303 x_s_chg_date_of_birth,
1304 x_first_name,
1305 x_middle_name,
1306 x_s_chg_last_name,
1307 x_s_date_of_birth,
1308 x_s_ssn,
1309 x_s_last_name,
1310 x_permt_addr_foreign_flag,
1311 x_addr_type_code,
1312 x_permt_addr_line_1,
1313 x_permt_addr_line_2,
1314 x_permt_addr_line_3,
1315 x_permt_addr_city,
1316 x_permt_addr_state_code,
1317 x_permt_addr_post_code,
1318 x_permt_addr_county,
1319 x_permt_addr_country,
1320 x_phone_number_1,
1321 x_phone_number_2,
1322 x_phone_number_3,
1323 x_email_address,
1324 x_citzn_status_code,
1325 x_mode
1326 );
1327
1328 END add_row;
1329
1330
1331 PROCEDURE delete_row (
1332 x_rowid IN VARCHAR2
1333 ) AS
1334 /*
1335 || Created By : [email protected]
1336 || Created On : 02-NOV-2004
1337 || Purpose : Handles the DELETE DML logic for the table.
1338 || Known limitations, enhancements or remarks :
1339 || Change History :
1340 || Who When What
1341 || (reverse chronological order - newest change first)
1342 */
1343 BEGIN
1344
1345 before_dml (
1346 p_action => 'DELETE',
1347 x_rowid => x_rowid
1348 );
1349
1350 DELETE FROM igf_gr_cod_history
1351 WHERE rowid = x_rowid;
1352
1353 IF (SQL%NOTFOUND) THEN
1354 RAISE NO_DATA_FOUND;
1355 END IF;
1356
1357 END delete_row;
1358
1359
1360 END igf_gr_cod_history_pkg;