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