DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_PE_EV_FORM_PKG

Source


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