[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_NONIMG_FORM_PKG
Source
1 PACKAGE BODY igs_pe_nonimg_form_pkg AS
2 /* $Header: IGSNIA7B.pls 120.2 2006/02/17 06:56:03 gmaheswa ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_nonimg_form%ROWTYPE;
6 new_references igs_pe_nonimg_form%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_nonimg_form_id IN NUMBER,
12 x_person_id IN NUMBER,
13 x_print_form IN VARCHAR2,
14 x_form_effective_date IN DATE,
15 x_form_status IN VARCHAR2,
16 x_acad_term_length IN VARCHAR2,
17 x_tuition_amt IN NUMBER,
18 x_living_exp_amt IN NUMBER,
19 x_personal_funds_amt IN NUMBER,
20 x_issue_reason IN VARCHAR2,
21 x_commuter_ind IN VARCHAR2,
22 x_english_reqd IN VARCHAR2,
23 x_length_of_study IN VARCHAR2,
24 x_prgm_start_date IN DATE,
25 x_prgm_end_date IN DATE,
26 x_primary_major IN VARCHAR2,
27 x_education_level IN VARCHAR2,
28 x_educ_lvl_remarks IN VARCHAR2,
29 x_depdnt_exp_amt IN NUMBER,
30 x_other_exp_amt IN NUMBER,
31 x_other_exp_desc IN VARCHAR2,
32 x_school_funds_amt IN NUMBER,
33 x_school_funds_desc IN VARCHAR2,
34 x_other_funds_amt IN NUMBER,
35 x_other_funds_desc IN VARCHAR2,
36 x_empl_funds_amt IN NUMBER,
37 x_remarks IN VARCHAR2,
38 x_visa_type IN VARCHAR2,
39 x_curr_session_end_date IN DATE,
40 x_next_session_start_date IN DATE,
41 x_transfer_from_school IN VARCHAR2,
42 x_other_reason IN VARCHAR2,
43 x_last_reprint_date IN DATE,
44 x_reprint_reason IN VARCHAR2,
45 x_reprint_remarks IN VARCHAR2,
46 x_secondary_major IN VARCHAR2,
47 x_minor IN VARCHAR2,
48 x_english_reqd_met IN VARCHAR2,
49 x_not_reqd_reason IN VARCHAR2,
50 x_creation_date IN DATE,
51 x_created_by IN NUMBER,
52 x_last_update_date IN DATE,
53 x_last_updated_by IN NUMBER,
54 x_last_update_login IN NUMBER,
55 x_last_session_flag IN VARCHAR2,
56 x_adjudicated_flag IN VARCHAR2,
57 x_sevis_school_id IN NUMBER
58 ) AS
59 /*
60 || Created By : [email protected]
61 || Created On : 28-NOV-2002
62 || Purpose : Initialises the Old and New references for the columns of the table.
63 || Known limitations, enhancements or remarks :
64 || Change History :
65 || Who When What
66 || (reverse chronological order - newest change first)
67 */
68
69 CURSOR cur_old_ref_values IS
70 SELECT *
71 FROM igs_pe_nonimg_form
72 WHERE rowid = x_rowid;
73
74 BEGIN
75
76 l_rowid := x_rowid;
77
78 -- Code for setting the Old and New Reference Values.
79 -- Populate Old Values.
80 OPEN cur_old_ref_values;
81 FETCH cur_old_ref_values INTO old_references;
82 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
83 CLOSE cur_old_ref_values;
84 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
85 igs_ge_msg_stack.add;
86 app_exception.raise_exception;
87 RETURN;
88 END IF;
89 CLOSE cur_old_ref_values;
90
91 -- Populate New Values.
92 new_references.nonimg_form_id := x_nonimg_form_id;
93 new_references.person_id := x_person_id;
94 new_references.print_form := x_print_form;
95 new_references.form_effective_date := x_form_effective_date;
96 new_references.form_status := x_form_status;
97 new_references.acad_term_length := x_acad_term_length;
98 new_references.tuition_amt := x_tuition_amt;
99 new_references.living_exp_amt := x_living_exp_amt;
100 new_references.personal_funds_amt := x_personal_funds_amt;
101 new_references.issue_reason := x_issue_reason;
102 new_references.commuter_ind := x_commuter_ind;
103 new_references.english_reqd := x_english_reqd;
104 new_references.length_of_study := x_length_of_study;
105 new_references.prgm_start_date := x_prgm_start_date;
106 new_references.prgm_end_date := x_prgm_end_date;
107 new_references.primary_major := x_primary_major;
108 new_references.education_level := x_education_level;
109 new_references.educ_lvl_remarks := x_educ_lvl_remarks;
110 new_references.depdnt_exp_amt := x_depdnt_exp_amt;
111 new_references.other_exp_amt := x_other_exp_amt;
112 new_references.other_exp_desc := x_other_exp_desc;
113 new_references.school_funds_amt := x_school_funds_amt;
114 new_references.school_funds_desc := x_school_funds_desc;
115 new_references.other_funds_amt := x_other_funds_amt;
116 new_references.other_funds_desc := x_other_funds_desc;
117 new_references.empl_funds_amt := x_empl_funds_amt;
118 new_references.remarks := x_remarks;
119 new_references.visa_type := x_visa_type;
120 new_references.curr_session_end_date := x_curr_session_end_date;
121 new_references.next_session_start_date := x_next_session_start_date;
122 new_references.transfer_from_school := x_transfer_from_school;
123 new_references.other_reason := x_other_reason;
124 new_references.last_reprint_date := x_last_reprint_date;
125 new_references.reprint_reason := x_reprint_reason;
126 new_references.reprint_remarks := x_reprint_remarks;
127 new_references.secondary_major := x_secondary_major;
128 new_references.minor := x_minor;
129 new_references.english_reqd_met := x_english_reqd_met;
130 new_references.not_reqd_reason := x_not_reqd_reason;
131 new_references.last_session_flag := x_last_session_flag;
132 new_references.adjudicated_flag := x_adjudicated_flag;
133 new_references.SEVIS_SCHOOL_IDENTIFIER := x_sevis_school_id;
134
135 IF (p_action = 'UPDATE') THEN
136 new_references.creation_date := old_references.creation_date;
137 new_references.created_by := old_references.created_by;
138 ELSE
139 new_references.creation_date := x_creation_date;
140 new_references.created_by := x_created_by;
141 END IF;
142
143 new_references.last_update_date := x_last_update_date;
144 new_references.last_updated_by := x_last_updated_by;
145 new_references.last_update_login := x_last_update_login;
146
147 END set_column_values;
148
149
150 PROCEDURE afterrowinsertupdate(p_insert BOOLEAN ,p_update BOOLEAN) AS
151 /*************************************************************
152 Created By : masehgal.
153 Date Created By : 07/Dec/2002
154 Purpose :
155 Know limitations, enhancements or remarks
156 Change History
157 Who When What
158
159 (reverse chronological order - newest change first)
160 ***************************************************************/
161
162 CURSOR per_type(cp_system_type igs_pe_person_types.system_type%TYPE,cp_closed_ind igs_pe_person_types.closed_ind%TYPE)IS
163 SELECT person_type_code
164 FROM igs_pe_person_types
165 WHERE system_type = cp_system_type --'NONIMG_STUDENT'
166 AND closed_ind = cp_closed_ind; --'N';
167
168 CURSOR person_type(cp_system_type igs_pe_person_types.system_type%TYPE)IS
169 SELECT typ.rowid,typ.*
170 FROM igs_pe_typ_instances_all typ , igs_pe_person_types per_typ
171 WHERE per_typ.system_type = cp_system_type --'NONIMG_STUDENT'
172 AND typ.person_id = new_references.person_id
173 AND typ.person_type_code = per_typ.person_type_code
174 AND typ.end_date IS NULL ;
175
176 l_person_type person_type%ROWTYPE;
177 l_per_type per_type%ROWTYPE;
178 lv_rowid VARCHAR2(25);
179 l_type_instance_id NUMBER(15);
180
181 BEGIN
182 OPEN per_type('NONIMG_STUDENT','N');
183 FETCH per_type INTO l_per_type;
184 IF per_type%FOUND THEN
185 IF p_insert = TRUE THEN
186 igs_pe_typ_instances_pkg.insert_row
187 (
188 x_rowid => lv_rowid,
189 x_person_id => new_references.person_id,
190 x_course_cd => null,
191 x_type_instance_id => l_type_instance_id,
192 x_person_type_code => l_per_type.person_type_code,
193 x_cc_version_number => null,
194 x_funnel_status => null,
195 x_admission_appl_number => null,
196 x_nominated_course_cd => null,
197 x_ncc_version_number => null,
198 x_sequence_number => null,
199 x_start_date => new_references.form_effective_date,
200 x_end_date => null,
201 x_create_method => 'CREATE_NONIMG_STUDENT',
202 x_ended_by => null,
203 x_end_method => null,
204 x_org_id => null,
205 x_emplmnt_category_code => null
206 );
207
208 ELSIF p_update = TRUE THEN
209 IF (new_references.form_effective_date <> old_references.form_effective_date ) THEN
210 OPEN person_type('NONIMG_STUDENT');
211 FETCH person_type INTO l_person_type;
212 CLOSE person_type;
213 igs_pe_typ_instances_pkg.update_row
214 (
215 x_rowid => l_person_type.rowid,
216 x_person_id => l_person_type.person_id,
217 x_course_cd => l_person_type.course_cd,
218 x_type_instance_id => l_person_type.type_instance_id,
219 x_person_type_code => l_person_type.person_type_code,
220 x_cc_version_number => l_person_type.cc_version_number,
221 x_funnel_status => l_person_type.funnel_status,
222 x_admission_appl_number => l_person_type.admission_appl_number,
223 x_nominated_course_cd => l_person_type.nominated_course_cd,
224 x_ncc_version_number => l_person_type.ncc_version_number,
225 x_sequence_number => l_person_type.sequence_number,
226 x_start_date => new_references.form_effective_date,
227 x_end_date => l_person_type.end_date,
228 x_create_method => l_person_type.create_method,
229 x_ended_by => l_person_type.ended_by,
230 x_end_method => l_person_type.end_method,
231 x_emplmnt_category_code => l_person_type.emplmnt_category_code
232 );
233 END IF;
234 END IF;
235 CLOSE per_type;
236 END IF;
237 END afterrowinsertupdate;
238
239
240 PROCEDURE del_per_inst AS
241 /*************************************************************
242 Created By : masehgal.
243 Date Created By : 07/Dec/2002
244 Purpose :
245 Know limitations, enhancements or remarks
246 Change History
247 Who When What
248
249 (reverse chronological order - newest change first)
250 ***************************************************************/
251
252 CURSOR c_per_inst_rowids ( cp_person_id igs_pe_typ_instances_all.person_ID%TYPE,cp_create_method igs_pe_typ_instances_all.create_method%TYPE ) IS
253 SELECT rowid
254 FROM igs_pe_typ_instances_all
255 WHERE person_id = cp_person_id
256 AND create_method = cp_create_method --'CREATE_NONIMG_STUDENT'
257 AND end_method IS NULL ;
258
259 l_per_inst_rowid c_per_inst_rowids%ROWTYPE ; --igs_pe_typ_instances_all.row_id%TYPE;
260
261 BEGIN
262
263 FOR l_per_inst_rowid IN c_per_inst_rowids ( old_references.person_id,'CREATE_NONIMG_STUDENT')
264 LOOP
265
266 igs_pe_typ_instances_pkg.delete_row ( x_rowid => l_per_inst_rowid.rowid ) ;
267
268 END LOOP ;
269 END del_per_inst;
270
271
272 PROCEDURE check_child_existance AS
273 /*
274 || Created By : [email protected]
275 || Created On : 28-NOV-2002
276 || Purpose : Checks for the existance of Child records.
277 || Known limitations, enhancements or remarks :
278 || Change History :
279 || Who When What
280 || (reverse chronological order - newest change first)
281 */
282 BEGIN
283
284 igs_pe_nonimg_empl_pkg.get_fk_igs_pe_nonimg_form ( old_references.nonimg_form_id );
285
286 igs_pe_nonimg_stat_pkg.get_fk_igs_pe_nonimg_form ( old_references.nonimg_form_id );
287
288 END check_child_existance;
289
290
291 PROCEDURE check_parent_existance AS
292 /*************************************************************
293 Created By : masehgal.
294 Date Created By : 07/Dec/2002
295 Purpose :
296 Know limitations, enhancements or remarks
297 Change History
298 Who When What
299
300 (reverse chronological order - newest change first)
301 ***************************************************************/
302
303 BEGIN
304 IF (((old_references.person_id = new_references.person_id))
305 OR ((new_references.person_id IS NULL))) THEN
306 NULL;
307 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation ( new_references.person_id ) THEN
308 FND_MESSAGE.SET_NAME ('FND','FORM_RECORD_DELETED');
309 IGS_GE_MSG_STACK.ADD;
310 APP_EXCEPTION.RAISE_EXCEPTION;
311 END IF;
312
313 END check_parent_existance;
314
315
316 FUNCTION get_pk_for_validation ( x_nonimg_form_id IN NUMBER ) RETURN BOOLEAN AS
317 /*
318 || Created By : [email protected]
319 || Created On : 28-NOV-2002
320 || Purpose : Validates the Primary Key of the table.
321 || Known limitations, enhancements or remarks :
322 || Change History :
323 || Who When What
324 || (reverse chronological order - newest change first)
325 */
326 CURSOR cur_rowid IS
327 SELECT rowid
328 FROM igs_pe_nonimg_form
329 WHERE nonimg_form_id = x_nonimg_form_id
330 FOR UPDATE NOWAIT;
331
332 lv_rowid cur_rowid%RowType;
333
334 BEGIN
335
336 OPEN cur_rowid;
340 RETURN(TRUE);
337 FETCH cur_rowid INTO lv_rowid;
338 IF (cur_rowid%FOUND) THEN
339 CLOSE cur_rowid;
341 ELSE
342 CLOSE cur_rowid;
343 RETURN(FALSE);
344 END IF;
345
346 END get_pk_for_validation;
347
348
349 PROCEDURE before_dml (
350 p_action IN VARCHAR2,
351 x_rowid IN VARCHAR2,
352 x_nonimg_form_id IN NUMBER,
353 x_person_id IN NUMBER,
354 x_print_form IN VARCHAR2,
355 x_form_effective_date IN DATE,
356 x_form_status IN VARCHAR2,
357 x_acad_term_length IN VARCHAR2,
358 x_tuition_amt IN NUMBER,
359 x_living_exp_amt IN NUMBER,
360 x_personal_funds_amt IN NUMBER,
361 x_issue_reason IN VARCHAR2,
362 x_commuter_ind IN VARCHAR2,
363 x_english_reqd IN VARCHAR2,
364 x_length_of_study IN VARCHAR2,
365 x_prgm_start_date IN DATE,
366 x_prgm_end_date IN DATE,
367 x_primary_major IN VARCHAR2,
368 x_education_level IN VARCHAR2,
369 x_educ_lvl_remarks IN VARCHAR2,
370 x_depdnt_exp_amt IN NUMBER,
371 x_other_exp_amt IN NUMBER,
372 x_other_exp_desc IN VARCHAR2,
373 x_school_funds_amt IN NUMBER,
374 x_school_funds_desc IN VARCHAR2,
375 x_other_funds_amt IN NUMBER,
376 x_other_funds_desc IN VARCHAR2,
377 x_empl_funds_amt IN NUMBER,
378 x_remarks IN VARCHAR2,
379 x_visa_type IN VARCHAR2,
380 x_curr_session_end_date IN DATE,
381 x_next_session_start_date IN DATE,
382 x_transfer_from_school IN VARCHAR2,
383 x_other_reason IN VARCHAR2,
384 x_last_reprint_date IN DATE,
385 x_reprint_reason IN VARCHAR2,
386 x_reprint_remarks IN VARCHAR2,
387 x_secondary_major IN VARCHAR2,
388 x_minor IN VARCHAR2,
389 x_english_reqd_met IN VARCHAR2,
390 x_not_reqd_reason IN VARCHAR2,
391 x_creation_date IN DATE,
392 x_created_by IN NUMBER,
393 x_last_update_date IN DATE,
394 x_last_updated_by IN NUMBER,
395 x_last_update_login IN NUMBER,
396 x_last_session_flag IN VARCHAR2,
397 x_adjudicated_flag IN VARCHAR2,
398 x_sevis_school_id IN NUMBER
399 ) AS
400 /*
401 || Created By : [email protected]
402 || Created On : 28-NOV-2002
403 || Purpose : Initialises the columns, Checks Constraints, Calls the
404 || Trigger Handlers for the table, before any DML operation.
405 || Known limitations, enhancements or remarks :
406 || Change History :
407 || Who When What
408 || (reverse chronological order - newest change first)
409 */
410 BEGIN
411
412 set_column_values (
413 p_action,
414 x_rowid,
415 x_nonimg_form_id,
416 x_person_id,
417 x_print_form,
418 x_form_effective_date,
419 x_form_status,
420 x_acad_term_length,
421 x_tuition_amt,
422 x_living_exp_amt,
423 x_personal_funds_amt,
424 x_issue_reason,
425 x_commuter_ind,
426 x_english_reqd,
427 x_length_of_study,
428 x_prgm_start_date,
429 x_prgm_end_date,
430 x_primary_major,
431 x_education_level,
432 x_educ_lvl_remarks,
433 x_depdnt_exp_amt,
434 x_other_exp_amt,
435 x_other_exp_desc,
436 x_school_funds_amt,
437 x_school_funds_desc,
438 x_other_funds_amt,
439 x_other_funds_desc,
440 x_empl_funds_amt,
441 x_remarks,
442 x_visa_type,
443 x_curr_session_end_date,
444 x_next_session_start_date,
445 x_transfer_from_school,
446 x_other_reason,
447 x_last_reprint_date,
448 x_reprint_reason,
449 x_reprint_remarks,
450 x_secondary_major,
451 x_minor,
452 x_english_reqd_met,
453 x_not_reqd_reason,
454 x_creation_date,
455 x_created_by,
456 x_last_update_date,
457 x_last_updated_by,
458 x_last_update_login,
459 x_last_session_flag,
460 x_adjudicated_flag,
461 x_sevis_school_id
462 );
463
464 IF (p_action = 'INSERT') THEN
465 -- Call all the procedures related to Before Insert.
466 IF ( get_pk_for_validation( new_references.nonimg_form_id ) ) THEN
467 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
471 ELSIF (p_action = 'DELETE') THEN
468 igs_ge_msg_stack.add;
469 app_exception.raise_exception;
470 END IF;
472 -- Call all the procedures related to Before Delete.
473 check_child_existance;
474 ELSIF (p_action = 'VALIDATE_INSERT') THEN
475 -- Call all the procedures related to Before Insert.
476 IF ( get_pk_for_validation ( new_references.nonimg_form_id ) ) THEN
477 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
478 igs_ge_msg_stack.add;
479 app_exception.raise_exception;
480 END IF;
481 ELSIF (p_action = 'VALIDATE_DELETE') THEN
482 check_child_existance;
483 END IF;
484
485 END before_dml;
486
487
488 PROCEDURE insert_row (
489 x_rowid IN OUT NOCOPY VARCHAR2,
490 x_nonimg_form_id IN OUT NOCOPY NUMBER,
491 x_person_id IN NUMBER,
492 x_print_form IN VARCHAR2,
493 x_form_effective_date IN DATE,
494 x_form_status IN VARCHAR2,
495 x_acad_term_length IN VARCHAR2,
496 x_tuition_amt IN NUMBER,
497 x_living_exp_amt IN NUMBER,
498 x_personal_funds_amt IN NUMBER,
499 x_issue_reason IN VARCHAR2,
500 x_commuter_ind IN VARCHAR2,
501 x_english_reqd IN VARCHAR2,
502 x_length_of_study IN VARCHAR2,
503 x_prgm_start_date IN DATE,
504 x_prgm_end_date IN DATE,
505 x_primary_major IN VARCHAR2,
506 x_education_level IN VARCHAR2,
507 x_educ_lvl_remarks IN VARCHAR2,
508 x_depdnt_exp_amt IN NUMBER,
509 x_other_exp_amt IN NUMBER,
510 x_other_exp_desc IN VARCHAR2,
511 x_school_funds_amt IN NUMBER,
512 x_school_funds_desc IN VARCHAR2,
513 x_other_funds_amt IN NUMBER,
514 x_other_funds_desc IN VARCHAR2,
515 x_empl_funds_amt IN NUMBER,
516 x_remarks IN VARCHAR2,
517 x_visa_type IN VARCHAR2,
518 x_curr_session_end_date IN DATE,
519 x_next_session_start_date IN DATE,
520 x_transfer_from_school IN VARCHAR2,
521 x_other_reason IN VARCHAR2,
522 x_last_reprint_date IN DATE,
523 x_reprint_reason IN VARCHAR2,
524 x_reprint_remarks IN VARCHAR2,
525 x_secondary_major IN VARCHAR2,
526 x_minor IN VARCHAR2,
527 x_english_reqd_met IN VARCHAR2,
528 x_not_reqd_reason IN VARCHAR2,
529 x_mode IN VARCHAR2,
530 x_last_session_flag IN VARCHAR2,
531 x_adjudicated_flag IN VARCHAR2,
532 x_sevis_school_id IN NUMBER
533 ) AS
534 /*
535 || Created By : [email protected]
536 || Created On : 28-NOV-2002
537 || Purpose : Handles the INSERT DML logic for the table.
538 || Known limitations, enhancements or remarks :
539 || Change History :
540 || Who When What
541 || (reverse chronological order - newest change first)
542 */
543
544 x_last_update_date DATE;
545 x_last_updated_by NUMBER;
546 x_last_update_login NUMBER;
547
548 BEGIN
549
550 x_last_update_date := SYSDATE;
551 IF (x_mode = 'I') THEN
552 x_last_updated_by := 1;
553 x_last_update_login := 0;
554 ELSIF (X_MODE IN ('R', 'S')) THEN
555 x_last_updated_by := fnd_global.user_id;
556 IF (x_last_updated_by IS NULL) THEN
557 x_last_updated_by := -1;
558 END IF;
559 x_last_update_login := fnd_global.login_id;
560 IF (x_last_update_login IS NULL) THEN
561 x_last_update_login := -1;
562 END IF;
563 ELSE
564 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
565 igs_ge_msg_stack.add;
566 app_exception.raise_exception;
567 END IF;
568
569 before_dml(
570 p_action => 'INSERT',
571 x_rowid => x_rowid,
572 x_nonimg_form_id => x_nonimg_form_id,
573 x_person_id => x_person_id,
574 x_print_form => x_print_form,
575 x_form_effective_date => x_form_effective_date,
576 x_form_status => x_form_status,
577 x_acad_term_length => x_acad_term_length,
578 x_tuition_amt => x_tuition_amt,
579 x_living_exp_amt => x_living_exp_amt,
580 x_personal_funds_amt => x_personal_funds_amt,
581 x_issue_reason => x_issue_reason,
582 x_commuter_ind => x_commuter_ind,
583 x_english_reqd => x_english_reqd,
587 x_primary_major => x_primary_major,
584 x_length_of_study => x_length_of_study,
585 x_prgm_start_date => x_prgm_start_date,
586 x_prgm_end_date => x_prgm_end_date,
588 x_education_level => x_education_level,
589 x_educ_lvl_remarks => x_educ_lvl_remarks,
590 x_depdnt_exp_amt => x_depdnt_exp_amt,
591 x_other_exp_amt => x_other_exp_amt,
592 x_other_exp_desc => x_other_exp_desc,
593 x_school_funds_amt => x_school_funds_amt,
594 x_school_funds_desc => x_school_funds_desc,
595 x_other_funds_amt => x_other_funds_amt,
596 x_other_funds_desc => x_other_funds_desc,
597 x_empl_funds_amt => x_empl_funds_amt,
598 x_remarks => x_remarks,
599 x_visa_type => x_visa_type,
600 x_curr_session_end_date => x_curr_session_end_date,
601 x_next_session_start_date => x_next_session_start_date,
602 x_transfer_from_school => x_transfer_from_school,
603 x_other_reason => x_other_reason,
604 x_last_reprint_date => x_last_reprint_date,
605 x_reprint_reason => x_reprint_reason,
606 x_reprint_remarks => x_reprint_remarks,
607 x_secondary_major => x_secondary_major,
608 x_minor => x_minor,
609 x_english_reqd_met => x_english_reqd_met,
610 x_not_reqd_reason => x_not_reqd_reason,
611 x_creation_date => x_last_update_date,
612 x_created_by => x_last_updated_by,
613 x_last_update_date => x_last_update_date,
614 x_last_updated_by => x_last_updated_by,
615 x_last_update_login => x_last_update_login,
616 x_last_session_flag => x_last_session_flag,
617 x_adjudicated_flag => x_adjudicated_flag,
618 x_sevis_school_id => x_sevis_school_id
619 );
620
621 IF (x_mode = 'S') THEN
622 igs_sc_gen_001.set_ctx('R');
623 END IF;
624 INSERT INTO igs_pe_nonimg_form (
625 nonimg_form_id,
626 person_id,
627 print_form,
628 form_effective_date,
629 form_status,
630 acad_term_length,
631 tuition_amt,
632 living_exp_amt,
633 personal_funds_amt,
634 issue_reason,
635 commuter_ind,
636 english_reqd,
637 length_of_study,
638 prgm_start_date,
639 prgm_end_date,
640 primary_major,
641 education_level,
642 educ_lvl_remarks,
643 depdnt_exp_amt,
644 other_exp_amt,
645 other_exp_desc,
646 school_funds_amt,
647 school_funds_desc,
648 other_funds_amt,
649 other_funds_desc,
650 empl_funds_amt,
651 remarks,
652 visa_type,
653 curr_session_end_date,
654 next_session_start_date,
655 transfer_from_school,
656 other_reason,
657 last_reprint_date,
658 reprint_reason,
659 reprint_remarks,
660 secondary_major,
661 minor,
662 english_reqd_met,
663 not_reqd_reason,
664 creation_date,
665 created_by,
666 last_update_date,
667 last_updated_by,
668 last_update_login,
669 last_session_flag,
670 adjudicated_flag,
671 SEVIS_SCHOOL_IDENTIFIER
672 ) VALUES (
673 igs_pe_nonimg_form_s.NEXTVAL,
674 new_references.person_id,
675 new_references.print_form,
676 new_references.form_effective_date,
677 new_references.form_status,
678 new_references.acad_term_length,
679 new_references.tuition_amt,
680 new_references.living_exp_amt,
681 new_references.personal_funds_amt,
682 new_references.issue_reason,
683 new_references.commuter_ind,
684 new_references.english_reqd,
685 new_references.length_of_study,
686 new_references.prgm_start_date,
687 new_references.prgm_end_date,
688 new_references.primary_major,
689 new_references.education_level,
690 new_references.educ_lvl_remarks,
691 new_references.depdnt_exp_amt,
692 new_references.other_exp_amt,
693 new_references.other_exp_desc,
694 new_references.school_funds_amt,
695 new_references.school_funds_desc,
696 new_references.other_funds_amt,
697 new_references.other_funds_desc,
698 new_references.empl_funds_amt,
699 new_references.remarks,
700 new_references.visa_type,
701 new_references.curr_session_end_date,
702 new_references.next_session_start_date,
703 new_references.transfer_from_school,
704 new_references.other_reason,
705 new_references.last_reprint_date,
706 new_references.reprint_reason,
707 new_references.reprint_remarks,
708 new_references.secondary_major,
709 new_references.minor,
710 new_references.english_reqd_met,
711 new_references.not_reqd_reason,
715 x_last_updated_by,
712 x_last_update_date,
713 x_last_updated_by,
714 x_last_update_date,
716 x_last_update_login,
717 new_references.last_session_flag,
718 new_references.adjudicated_flag,
719 new_references.SEVIS_SCHOOL_IDENTIFIER
720 ) RETURNING ROWID, nonimg_form_id INTO x_rowid, x_nonimg_form_id;
721 IF (x_mode = 'S') THEN
722 igs_sc_gen_001.unset_ctx('R');
723 END IF;
724
725
726 afterrowinsertupdate(TRUE,FALSE);
727
728
729 EXCEPTION
730 WHEN OTHERS THEN
731 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
732 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
733 fnd_message.set_token ('ERR_CD', SQLCODE);
734 igs_ge_msg_stack.add;
735 igs_sc_gen_001.unset_ctx('R');
736 app_exception.raise_exception;
737 ELSE
738 igs_sc_gen_001.unset_ctx('R');
739 RAISE;
740 END IF;
741 END insert_row;
742
743
744 PROCEDURE lock_row (
745 x_rowid IN VARCHAR2,
746 x_nonimg_form_id IN NUMBER,
747 x_person_id IN NUMBER,
748 x_print_form IN VARCHAR2,
749 x_form_effective_date IN DATE,
750 x_form_status IN VARCHAR2,
751 x_acad_term_length IN VARCHAR2,
752 x_tuition_amt IN NUMBER,
753 x_living_exp_amt IN NUMBER,
754 x_personal_funds_amt IN NUMBER,
755 x_issue_reason IN VARCHAR2,
756 x_commuter_ind IN VARCHAR2,
757 x_english_reqd IN VARCHAR2,
758 x_length_of_study IN VARCHAR2,
759 x_prgm_start_date IN DATE,
760 x_prgm_end_date IN DATE,
761 x_primary_major IN VARCHAR2,
762 x_education_level IN VARCHAR2,
763 x_educ_lvl_remarks IN VARCHAR2,
764 x_depdnt_exp_amt IN NUMBER,
765 x_other_exp_amt IN NUMBER,
766 x_other_exp_desc IN VARCHAR2,
767 x_school_funds_amt IN NUMBER,
768 x_school_funds_desc IN VARCHAR2,
769 x_other_funds_amt IN NUMBER,
770 x_other_funds_desc IN VARCHAR2,
771 x_empl_funds_amt IN NUMBER,
772 x_remarks IN VARCHAR2,
773 x_visa_type IN VARCHAR2,
774 x_curr_session_end_date IN DATE,
775 x_next_session_start_date IN DATE,
776 x_transfer_from_school IN VARCHAR2,
777 x_other_reason IN VARCHAR2,
778 x_last_reprint_date IN DATE,
779 x_reprint_reason IN VARCHAR2,
780 x_reprint_remarks IN VARCHAR2,
781 x_secondary_major IN VARCHAR2,
782 x_minor IN VARCHAR2,
783 x_english_reqd_met IN VARCHAR2,
784 x_not_reqd_reason IN VARCHAR2,
785 x_last_session_flag IN VARCHAR2,
786 x_adjudicated_flag IN VARCHAR2,
787 x_sevis_school_id IN NUMBER
788 ) AS
789 /*
790 || Created By : [email protected]
791 || Created On : 28-NOV-2002
792 || Purpose : Handles the LOCK mechanism for the table.
793 || Known limitations, enhancements or remarks :
794 || Change History :
795 || Who When What
796 || (reverse chronological order - newest change first)
797 */
798 CURSOR c1 IS
799 SELECT
800 person_id,
801 print_form,
802 form_effective_date,
803 form_status,
804 acad_term_length,
805 tuition_amt,
806 living_exp_amt,
807 personal_funds_amt,
808 issue_reason,
809 commuter_ind,
810 english_reqd,
811 length_of_study,
812 prgm_start_date,
813 prgm_end_date,
814 primary_major,
815 education_level,
816 educ_lvl_remarks,
817 depdnt_exp_amt,
818 other_exp_amt,
819 other_exp_desc,
820 school_funds_amt,
821 school_funds_desc,
822 other_funds_amt,
823 other_funds_desc,
824 empl_funds_amt,
825 remarks,
826 visa_type,
827 curr_session_end_date,
828 next_session_start_date,
829 transfer_from_school,
830 other_reason,
831 last_reprint_date,
832 reprint_reason,
833 reprint_remarks,
834 secondary_major,
835 minor,
836 english_reqd_met,
837 not_reqd_reason,
838 last_session_flag,
839 adjudicated_flag,
840 SEVIS_SCHOOL_IDENTIFIER
841 FROM igs_pe_nonimg_form
842 WHERE rowid = x_rowid
843 FOR UPDATE NOWAIT;
844
845 tlinfo c1%ROWTYPE;
846
847 BEGIN
848
849 OPEN c1;
853 igs_ge_msg_stack.add;
850 FETCH c1 INTO tlinfo;
851 IF (c1%notfound) THEN
852 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
854 CLOSE c1;
855 app_exception.raise_exception;
856 RETURN;
857 END IF;
858 CLOSE c1;
859
860 IF (
861 (tlinfo.person_id = x_person_id)
862 AND (tlinfo.print_form = x_print_form)
863 AND (tlinfo.form_effective_date = x_form_effective_date)
864 AND (tlinfo.form_status = x_form_status)
865 AND (tlinfo.acad_term_length = x_acad_term_length)
866 AND (tlinfo.tuition_amt = x_tuition_amt)
867 AND (tlinfo.living_exp_amt = x_living_exp_amt)
868 AND (tlinfo.personal_funds_amt = x_personal_funds_amt)
869 AND (tlinfo.issue_reason = x_issue_reason)
870 AND ((tlinfo.commuter_ind = x_commuter_ind) OR ((tlinfo.commuter_ind IS NULL) AND (X_commuter_ind IS NULL)))
871 AND (tlinfo.english_reqd = x_english_reqd)
872 AND (tlinfo.length_of_study = x_length_of_study)
873 AND (tlinfo.prgm_start_date = x_prgm_start_date)
874 AND (tlinfo.prgm_end_date = x_prgm_end_date)
875 AND (tlinfo.primary_major = x_primary_major)
876 AND (tlinfo.education_level = x_education_level)
877 AND ((tlinfo.educ_lvl_remarks = x_educ_lvl_remarks) OR ((tlinfo.educ_lvl_remarks IS NULL) AND (X_educ_lvl_remarks IS NULL)))
878 AND ((tlinfo.depdnt_exp_amt = x_depdnt_exp_amt) OR ((tlinfo.depdnt_exp_amt IS NULL) AND (X_depdnt_exp_amt IS NULL)))
879 AND ((tlinfo.other_exp_amt = x_other_exp_amt) OR ((tlinfo.other_exp_amt IS NULL) AND (X_other_exp_amt IS NULL)))
880 AND ((tlinfo.other_exp_desc = x_other_exp_desc) OR ((tlinfo.other_exp_desc IS NULL) AND (X_other_exp_desc IS NULL)))
881 AND ((tlinfo.school_funds_amt = x_school_funds_amt) OR ((tlinfo.school_funds_amt IS NULL) AND (X_school_funds_amt IS NULL)))
882 AND ((tlinfo.school_funds_desc = x_school_funds_desc) OR ((tlinfo.school_funds_desc IS NULL) AND (X_school_funds_desc IS NULL)))
883 AND ((tlinfo.other_funds_amt = x_other_funds_amt) OR ((tlinfo.other_funds_amt IS NULL) AND (X_other_funds_amt IS NULL)))
884 AND ((tlinfo.other_funds_desc = x_other_funds_desc) OR ((tlinfo.other_funds_desc IS NULL) AND (X_other_funds_desc IS NULL)))
885 AND ((tlinfo.empl_funds_amt = x_empl_funds_amt) OR ((tlinfo.empl_funds_amt IS NULL) AND (X_empl_funds_amt IS NULL)))
886 AND ((tlinfo.remarks = x_remarks) OR ((tlinfo.remarks IS NULL) AND (X_remarks IS NULL)))
887 AND ((tlinfo.visa_type = x_visa_type))
888 AND ((tlinfo.curr_session_end_date = x_curr_session_end_date) OR ((tlinfo.curr_session_end_date IS NULL) AND (X_curr_session_end_date IS NULL)))
889 AND ((tlinfo.next_session_start_date = x_next_session_start_date) OR ((tlinfo.next_session_start_date IS NULL) AND (X_next_session_start_date IS NULL)))
890 AND ((tlinfo.transfer_from_school = x_transfer_from_school) OR ((tlinfo.transfer_from_school IS NULL) AND (X_transfer_from_school IS NULL)))
891 AND ((tlinfo.other_reason = x_other_reason) OR ((tlinfo.other_reason IS NULL) AND (X_other_reason IS NULL)))
892 AND ((tlinfo.last_reprint_date = x_last_reprint_date) OR ((tlinfo.last_reprint_date IS NULL) AND (X_last_reprint_date IS NULL)))
893 AND ((tlinfo.reprint_reason = x_reprint_reason) OR ((tlinfo.reprint_reason IS NULL) AND (X_reprint_reason IS NULL)))
894 AND ((tlinfo.reprint_remarks = x_reprint_remarks) OR ((tlinfo.reprint_remarks IS NULL) AND (X_reprint_remarks IS NULL)))
895 AND ((tlinfo.secondary_major = x_secondary_major) OR ((tlinfo.secondary_major IS NULL) AND (X_secondary_major IS NULL)))
896 AND ((tlinfo.minor = x_minor) OR ((tlinfo.minor IS NULL) AND (X_minor IS NULL)))
897 AND ((tlinfo.english_reqd_met = x_english_reqd_met) OR ((tlinfo.english_reqd_met IS NULL) AND (X_english_reqd_met IS NULL)))
898 AND ((tlinfo.not_reqd_reason = x_not_reqd_reason) OR ((tlinfo.not_reqd_reason IS NULL) AND (X_not_reqd_reason IS NULL)))
899 AND ((tlinfo.last_session_flag = x_last_session_flag) OR ((tlinfo.last_session_flag IS NULL) AND (X_last_session_flag IS NULL)))
900 AND ((tlinfo.adjudicated_flag = x_adjudicated_flag) OR ((tlinfo.adjudicated_flag IS NULL) AND (X_adjudicated_flag IS NULL)))
901 AND ((tlinfo.SEVIS_SCHOOL_IDENTIFIER = x_sevis_school_id) OR ((tlinfo.SEVIS_SCHOOL_IDENTIFIER IS NULL) AND (X_sevis_school_id IS NULL)))
902 ) THEN
903 NULL;
904 ELSE
905 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
906 igs_ge_msg_stack.add;
907 app_exception.raise_exception;
908 END IF;
909
910 RETURN;
911
912 END lock_row;
913
914
915 PROCEDURE update_row (
916 x_rowid IN VARCHAR2,
917 x_nonimg_form_id IN NUMBER,
918 x_person_id IN NUMBER,
919 x_print_form IN VARCHAR2,
920 x_form_effective_date IN DATE,
921 x_form_status IN VARCHAR2,
922 x_acad_term_length IN VARCHAR2,
923 x_tuition_amt IN NUMBER,
924 x_living_exp_amt IN NUMBER,
925 x_personal_funds_amt IN NUMBER,
926 x_issue_reason IN VARCHAR2,
927 x_commuter_ind IN VARCHAR2,
928 x_english_reqd IN VARCHAR2,
929 x_length_of_study IN VARCHAR2,
930 x_prgm_start_date IN DATE,
931 x_prgm_end_date IN DATE,
932 x_primary_major IN VARCHAR2,
933 x_education_level IN VARCHAR2,
937 x_other_exp_desc IN VARCHAR2,
934 x_educ_lvl_remarks IN VARCHAR2,
935 x_depdnt_exp_amt IN NUMBER,
936 x_other_exp_amt IN NUMBER,
938 x_school_funds_amt IN NUMBER,
939 x_school_funds_desc IN VARCHAR2,
940 x_other_funds_amt IN NUMBER,
941 x_other_funds_desc IN VARCHAR2,
942 x_empl_funds_amt IN NUMBER,
943 x_remarks IN VARCHAR2,
944 x_visa_type IN VARCHAR2,
945 x_curr_session_end_date IN DATE,
946 x_next_session_start_date IN DATE,
947 x_transfer_from_school IN VARCHAR2,
948 x_other_reason IN VARCHAR2,
949 x_last_reprint_date IN DATE,
950 x_reprint_reason IN VARCHAR2,
951 x_reprint_remarks IN VARCHAR2,
952 x_secondary_major IN VARCHAR2,
953 x_minor IN VARCHAR2,
954 x_english_reqd_met IN VARCHAR2,
955 x_not_reqd_reason IN VARCHAR2,
956 x_mode IN VARCHAR2,
957 x_last_session_flag IN VARCHAR2,
958 x_adjudicated_flag IN VARCHAR2,
959 x_sevis_school_id IN NUMBER
960 ) AS
961 /*
962 || Created By : [email protected]
963 || Created On : 28-NOV-2002
964 || Purpose : Handles the UPDATE DML logic for the table.
965 || Known limitations, enhancements or remarks :
966 || Change History :
967 || Who When What
968 || (reverse chronological order - newest change first)
969 */
970 x_last_update_date DATE ;
971 x_last_updated_by NUMBER;
972 x_last_update_login NUMBER;
973
974 BEGIN
975
976 x_last_update_date := SYSDATE;
977 IF (X_MODE = 'I') THEN
978 x_last_updated_by := 1;
979 x_last_update_login := 0;
980 ELSIF (X_MODE IN ('R', 'S')) THEN
981 x_last_updated_by := fnd_global.user_id;
982 IF x_last_updated_by IS NULL THEN
983 x_last_updated_by := -1;
984 END IF;
985 x_last_update_login := fnd_global.login_id;
986 IF (x_last_update_login IS NULL) THEN
987 x_last_update_login := -1;
988 END IF;
989 ELSE
990 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
991 igs_ge_msg_stack.add;
992 app_exception.raise_exception;
993 END IF;
994
995 before_dml(
996 p_action => 'UPDATE',
997 x_rowid => x_rowid,
998 x_nonimg_form_id => x_nonimg_form_id,
999 x_person_id => x_person_id,
1000 x_print_form => x_print_form,
1001 x_form_effective_date => x_form_effective_date,
1002 x_form_status => x_form_status,
1003 x_acad_term_length => x_acad_term_length,
1004 x_tuition_amt => x_tuition_amt,
1005 x_living_exp_amt => x_living_exp_amt,
1006 x_personal_funds_amt => x_personal_funds_amt,
1007 x_issue_reason => x_issue_reason,
1008 x_commuter_ind => x_commuter_ind,
1009 x_english_reqd => x_english_reqd,
1010 x_length_of_study => x_length_of_study,
1011 x_prgm_start_date => x_prgm_start_date,
1012 x_prgm_end_date => x_prgm_end_date,
1013 x_primary_major => x_primary_major,
1014 x_education_level => x_education_level,
1015 x_educ_lvl_remarks => x_educ_lvl_remarks,
1016 x_depdnt_exp_amt => x_depdnt_exp_amt,
1017 x_other_exp_amt => x_other_exp_amt,
1018 x_other_exp_desc => x_other_exp_desc,
1019 x_school_funds_amt => x_school_funds_amt,
1020 x_school_funds_desc => x_school_funds_desc,
1021 x_other_funds_amt => x_other_funds_amt,
1022 x_other_funds_desc => x_other_funds_desc,
1023 x_empl_funds_amt => x_empl_funds_amt,
1024 x_remarks => x_remarks,
1025 x_visa_type => x_visa_type,
1026 x_curr_session_end_date => x_curr_session_end_date,
1027 x_next_session_start_date => x_next_session_start_date,
1028 x_transfer_from_school => x_transfer_from_school,
1029 x_other_reason => x_other_reason,
1030 x_last_reprint_date => x_last_reprint_date,
1031 x_reprint_reason => x_reprint_reason,
1032 x_reprint_remarks => x_reprint_remarks,
1033 x_secondary_major => x_secondary_major,
1034 x_minor => x_minor,
1035 x_english_reqd_met => x_english_reqd_met,
1036 x_not_reqd_reason => x_not_reqd_reason,
1037 x_creation_date => x_last_update_date,
1038 x_created_by => x_last_updated_by,
1039 x_last_update_date => x_last_update_date,
1040 x_last_updated_by => x_last_updated_by,
1044 x_sevis_school_id => x_sevis_school_id
1041 x_last_update_login => x_last_update_login,
1042 x_last_session_flag => x_last_session_flag,
1043 x_adjudicated_flag => x_adjudicated_flag,
1045 );
1046
1047 IF (x_mode = 'S') THEN
1048 igs_sc_gen_001.set_ctx('R');
1049 END IF;
1050 UPDATE igs_pe_nonimg_form
1051 SET
1052 person_id = new_references.person_id,
1053 print_form = new_references.print_form,
1054 form_effective_date = new_references.form_effective_date,
1055 form_status = new_references.form_status,
1056 acad_term_length = new_references.acad_term_length,
1057 tuition_amt = new_references.tuition_amt,
1058 living_exp_amt = new_references.living_exp_amt,
1059 personal_funds_amt = new_references.personal_funds_amt,
1060 issue_reason = new_references.issue_reason,
1061 commuter_ind = new_references.commuter_ind,
1062 english_reqd = new_references.english_reqd,
1063 length_of_study = new_references.length_of_study,
1064 prgm_start_date = new_references.prgm_start_date,
1065 prgm_end_date = new_references.prgm_end_date,
1066 primary_major = new_references.primary_major,
1067 education_level = new_references.education_level,
1068 educ_lvl_remarks = new_references.educ_lvl_remarks,
1069 depdnt_exp_amt = new_references.depdnt_exp_amt,
1070 other_exp_amt = new_references.other_exp_amt,
1071 other_exp_desc = new_references.other_exp_desc,
1072 school_funds_amt = new_references.school_funds_amt,
1073 school_funds_desc = new_references.school_funds_desc,
1074 other_funds_amt = new_references.other_funds_amt,
1075 other_funds_desc = new_references.other_funds_desc,
1076 empl_funds_amt = new_references.empl_funds_amt,
1077 remarks = new_references.remarks,
1078 visa_type = new_references.visa_type,
1079 curr_session_end_date = new_references.curr_session_end_date,
1080 next_session_start_date = new_references.next_session_start_date,
1081 transfer_from_school = new_references.transfer_from_school,
1082 other_reason = new_references.other_reason,
1083 last_reprint_date = new_references.last_reprint_date,
1084 reprint_reason = new_references.reprint_reason,
1085 reprint_remarks = new_references.reprint_remarks,
1086 secondary_major = new_references.secondary_major,
1087 minor = new_references.minor,
1088 english_reqd_met = new_references.english_reqd_met,
1089 not_reqd_reason = new_references.not_reqd_reason,
1090 last_update_date = x_last_update_date,
1091 last_updated_by = x_last_updated_by,
1092 last_update_login = x_last_update_login,
1093 last_session_flag = x_last_session_flag,
1094 adjudicated_flag = new_references.adjudicated_flag,
1095 SEVIS_SCHOOL_IDENTIFIER = new_references.SEVIS_SCHOOL_IDENTIFIER
1096 WHERE rowid = x_rowid;
1097
1098 IF (SQL%NOTFOUND) THEN
1099 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1100 igs_ge_msg_stack.add;
1101 igs_sc_gen_001.unset_ctx('R');
1102 app_exception.raise_exception;
1103 END IF;
1104 IF (x_mode = 'S') THEN
1105 igs_sc_gen_001.unset_ctx('R');
1106 END IF;
1107
1108
1109 afterrowinsertupdate(FALSE,TRUE);
1110
1111
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 IF (SQLCODE = (-28115)) THEN
1115 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1116 fnd_message.set_token ('ERR_CD', SQLCODE);
1117 igs_ge_msg_stack.add;
1118 igs_sc_gen_001.unset_ctx('R');
1119 app_exception.raise_exception;
1120 ELSE
1121 igs_sc_gen_001.unset_ctx('R');
1122 RAISE;
1123 END IF;
1124 END update_row;
1125
1126
1127 PROCEDURE add_row (
1128 x_rowid IN OUT NOCOPY VARCHAR2,
1129 x_nonimg_form_id IN OUT NOCOPY NUMBER,
1130 x_person_id IN NUMBER,
1131 x_print_form IN VARCHAR2,
1132 x_form_effective_date IN DATE,
1133 x_form_status IN VARCHAR2,
1134 x_acad_term_length IN VARCHAR2,
1135 x_tuition_amt IN NUMBER,
1136 x_living_exp_amt IN NUMBER,
1137 x_personal_funds_amt IN NUMBER,
1138 x_issue_reason IN VARCHAR2,
1139 x_commuter_ind IN VARCHAR2,
1140 x_english_reqd IN VARCHAR2,
1141 x_length_of_study IN VARCHAR2,
1142 x_prgm_start_date IN DATE,
1143 x_prgm_end_date IN DATE,
1144 x_primary_major IN VARCHAR2,
1145 x_education_level IN VARCHAR2,
1146 x_educ_lvl_remarks IN VARCHAR2,
1147 x_depdnt_exp_amt IN NUMBER,
1148 x_other_exp_amt IN NUMBER,
1149 x_other_exp_desc IN VARCHAR2,
1150 x_school_funds_amt IN NUMBER,
1151 x_school_funds_desc IN VARCHAR2,
1152 x_other_funds_amt IN NUMBER,
1153 x_other_funds_desc IN VARCHAR2,
1154 x_empl_funds_amt IN NUMBER,
1155 x_remarks IN VARCHAR2,
1156 x_visa_type IN VARCHAR2,
1157 x_curr_session_end_date IN DATE,
1158 x_next_session_start_date IN DATE,
1159 x_transfer_from_school IN VARCHAR2,
1160 x_other_reason IN VARCHAR2,
1161 x_last_reprint_date IN DATE,
1162 x_reprint_reason IN VARCHAR2,
1163 x_reprint_remarks IN VARCHAR2,
1164 x_secondary_major IN VARCHAR2,
1165 x_minor IN VARCHAR2,
1166 x_english_reqd_met IN VARCHAR2,
1167 x_not_reqd_reason IN VARCHAR2,
1168 x_mode IN VARCHAR2,
1169 x_last_session_flag IN VARCHAR2,
1170 x_adjudicated_flag IN VARCHAR2,
1171 x_sevis_school_id IN NUMBER
1172 ) AS
1173 /*
1174 || Created By : [email protected]
1175 || Created On : 28-NOV-2002
1176 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1177 || Known limitations, enhancements or remarks :
1178 || Change History :
1179 || Who When What
1180 || (reverse chronological order - newest change first)
1181 */
1182 CURSOR c1 IS
1183 SELECT rowid
1184 FROM igs_pe_nonimg_form
1185 WHERE nonimg_form_id = x_nonimg_form_id;
1186
1187 BEGIN
1188
1192 CLOSE c1;
1189 OPEN c1;
1190 FETCH c1 INTO x_rowid;
1191 IF (c1%NOTFOUND) THEN
1193
1194 insert_row (
1195 x_rowid,
1196 x_nonimg_form_id,
1197 x_person_id,
1198 x_print_form,
1199 x_form_effective_date,
1200 x_form_status,
1201 x_acad_term_length,
1202 x_tuition_amt,
1203 x_living_exp_amt,
1204 x_personal_funds_amt,
1205 x_issue_reason,
1206 x_commuter_ind,
1207 x_english_reqd,
1208 x_length_of_study,
1209 x_prgm_start_date,
1210 x_prgm_end_date,
1211 x_primary_major,
1212 x_education_level,
1213 x_educ_lvl_remarks,
1214 x_depdnt_exp_amt,
1215 x_other_exp_amt,
1216 x_other_exp_desc,
1217 x_school_funds_amt,
1218 x_school_funds_desc,
1219 x_other_funds_amt,
1220 x_other_funds_desc,
1221 x_empl_funds_amt,
1222 x_remarks,
1223 x_visa_type,
1224 x_curr_session_end_date,
1225 x_next_session_start_date,
1226 x_transfer_from_school,
1227 x_other_reason,
1228 x_last_reprint_date,
1229 x_reprint_reason,
1230 x_reprint_remarks,
1231 x_secondary_major,
1232 x_minor,
1233 x_english_reqd_met,
1234 x_not_reqd_reason,
1235 x_mode,
1236 x_last_session_flag,
1237 x_adjudicated_flag,
1238 x_sevis_school_id
1239 );
1240 RETURN;
1241 END IF;
1242 CLOSE c1;
1243
1244 update_row (
1245 x_rowid,
1246 x_nonimg_form_id,
1247 x_person_id,
1248 x_print_form,
1249 x_form_effective_date,
1250 x_form_status,
1251 x_acad_term_length,
1252 x_tuition_amt,
1253 x_living_exp_amt,
1254 x_personal_funds_amt,
1255 x_issue_reason,
1256 x_commuter_ind,
1257 x_english_reqd,
1258 x_length_of_study,
1259 x_prgm_start_date,
1260 x_prgm_end_date,
1261 x_primary_major,
1262 x_education_level,
1263 x_educ_lvl_remarks,
1264 x_depdnt_exp_amt,
1265 x_other_exp_amt,
1266 x_other_exp_desc,
1267 x_school_funds_amt,
1268 x_school_funds_desc,
1269 x_other_funds_amt,
1270 x_other_funds_desc,
1271 x_empl_funds_amt,
1272 x_remarks,
1273 x_visa_type,
1274 x_curr_session_end_date,
1275 x_next_session_start_date,
1276 x_transfer_from_school,
1277 x_other_reason,
1278 x_last_reprint_date,
1279 x_reprint_reason,
1280 x_reprint_remarks,
1281 x_secondary_major,
1282 x_minor,
1283 x_english_reqd_met,
1284 x_not_reqd_reason,
1285 x_mode,
1286 x_last_session_flag,
1287 x_adjudicated_flag,
1288 x_sevis_school_id
1289 );
1290
1291 END add_row;
1292
1293
1294 PROCEDURE delete_row (
1295 x_rowid IN VARCHAR2,
1296 x_mode IN VARCHAR2
1297 ) AS
1298 /*
1299 || Created By : [email protected]
1300 || Created On : 28-NOV-2002
1301 || Purpose : Handles the DELETE DML logic for the table.
1302 || Known limitations, enhancements or remarks :
1303 || Change History :
1304 || Who When What
1305 || (reverse chronological order - newest change first)
1306 */
1307 BEGIN
1308
1309 before_dml (
1310 p_action => 'DELETE',
1311 x_rowid => x_rowid
1312 );
1313
1314 IF (x_mode = 'S') THEN
1315 igs_sc_gen_001.set_ctx('R');
1316 END IF;
1317 DELETE FROM igs_pe_nonimg_form
1318 WHERE rowid = x_rowid;
1319
1320 IF (SQL%NOTFOUND) THEN
1321 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1322 igs_ge_msg_stack.add;
1323 igs_sc_gen_001.unset_ctx('R');
1324 app_exception.raise_exception;
1325 END IF;
1326 IF (x_mode = 'S') THEN
1327 igs_sc_gen_001.unset_ctx('R');
1328 END IF;
1329
1330
1331 del_per_inst ;
1332
1333 END delete_row;
1334
1335
1336 END igs_pe_nonimg_form_pkg;