eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_PE_NONIMG_STAT_PKG

Source


1 PACKAGE BODY igs_pe_nonimg_stat_pkg AS
2 /* $Header: IGSNIA9B.pls 120.2 2006/02/17 06:56:30 gmaheswa ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_pe_nonimg_stat%ROWTYPE;
6   new_references igs_pe_nonimg_stat%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_nonimg_stat_id                    IN     NUMBER,
12     x_nonimg_form_id                    IN     NUMBER,
13     x_action_date                       IN     DATE,
14     x_action_type                       IN     VARCHAR2,
15     x_prgm_start_date                   IN     DATE,
16     x_prgm_end_date                     IN     DATE,
17     x_remarks                           IN     VARCHAR2,
18     x_termination_reason                IN     VARCHAR2,
19     x_creation_date                     IN     DATE,
20     x_created_by                        IN     NUMBER,
21     x_last_update_date                  IN     DATE,
22     x_last_updated_by                   IN     NUMBER,
23     x_last_update_login                 IN     NUMBER,
24     x_print_flag			IN     VARCHAR2,
25     x_cancel_flag			IN     VARCHAR2
26   ) AS
27   /*
28   ||  Created By : [email protected]
29   ||  Created On : 28-NOV-2002
30   ||  Purpose : Initialises the Old and New references for the columns of the table.
31   ||  Known limitations, enhancements or remarks :
32   ||  Change History :
33   ||  Who             When            What
34   ||  (reverse chronological order - newest change first)
35   */
36 
37     CURSOR cur_old_ref_values IS
38       SELECT   *
39       FROM     igs_pe_nonimg_stat
40       WHERE    rowid = x_rowid;
41 
42   BEGIN
43 
44     l_rowid := x_rowid;
45 
46     -- Code for setting the Old and New Reference Values.
47     -- Populate Old Values.
48     OPEN cur_old_ref_values;
49     FETCH cur_old_ref_values INTO old_references;
50     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
51       CLOSE cur_old_ref_values;
52       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
53       igs_ge_msg_stack.add;
54       app_exception.raise_exception;
55       RETURN;
56     END IF;
57     CLOSE cur_old_ref_values;
58 
59     -- Populate New Values.
60     new_references.nonimg_stat_id                    := x_nonimg_stat_id;
61     new_references.nonimg_form_id                    := x_nonimg_form_id;
62     new_references.action_date                       := x_action_date;
63     new_references.action_type                       := x_action_type;
64     new_references.prgm_start_date                   := x_prgm_start_date;
65     new_references.prgm_end_date                     := x_prgm_end_date;
66     new_references.remarks                           := x_remarks;
67     new_references.termination_reason                := x_termination_reason;
68 
69     IF (p_action = 'UPDATE') THEN
70       new_references.creation_date                   := old_references.creation_date;
71       new_references.created_by                      := old_references.created_by;
72     ELSE
73       new_references.creation_date                   := x_creation_date;
74       new_references.created_by                      := x_created_by;
75     END IF;
76 
77     new_references.last_update_date                  := x_last_update_date;
78     new_references.last_updated_by                   := x_last_updated_by;
79     new_references.last_update_login                 := x_last_update_login;
80     new_references.print_flag	                     := x_print_flag;
81     new_references.cancel_flag	                     := x_cancel_flag;
82   END set_column_values;
83 
84 
85   PROCEDURE upd_form_stat( p_action_type  VARCHAR2) AS
86   /*************************************************************
87   Created By : masehgal.
88   Date Created By : 07/Dec/2002
89   Purpose :
90   Know limitations, enhancements or remarks
91   Change History
92   Who             When            What
93   kumma           08-JAN-2003     2739579, Added a parameter of visa_type to igs_pe_nonimg_form_pkg.update_row
94   (reverse chronological order - newest change first)
95   ***************************************************************/
96 
97   BEGIN
98        DECLARE
99 
100          CURSOR person_type(l_person_id  igs_pe_ev_form.person_id%TYPE) IS
101             SELECT typ.rowid,typ.*
102             FROM   igs_pe_typ_instances_all typ , igs_pe_person_types per_typ
103             WHERE  typ.person_id = l_person_id
104             AND    per_typ.system_type = 'NONIMG_STUDENT'
105             AND    typ.person_type_code = per_typ.person_type_code
106             AND    typ.end_date IS NULL ;
107 
108 
109          CURSOR c_get_form_values ( cp_nonimg_form_id     igs_pe_nonimg_form.nonimg_form_id%TYPE ) IS
110             SELECT form.rowid,form.*
111             FROM   igs_pe_nonimg_form form
112             WHERE  form.nonimg_form_id = cp_nonimg_form_id ;
113 
114             l_form         c_get_form_values%ROWTYPE;
115             l_form_status  igs_pe_nonimg_form.form_status%TYPE;
116             l_end_date     DATE;
117             l_end_method   igs_pe_typ_instances_all.end_method%TYPE;
118             lv_userid      NUMBER;
119             l_person_type  person_type%ROWTYPE;
120 
121 
122 
123        BEGIN
124           OPEN  c_get_form_values ( new_references.nonimg_form_id );
125           FETCH c_get_form_values INTO l_form;
126           CLOSE c_get_form_values;
127 
128           IF new_references.action_type = 'C' THEN
129              l_form_status := 'C';
130           ELSIF new_references.action_type = 'T' THEN
131              l_form_status := 'T';
132 	  ELSIF new_references.action_type = 'CP' THEN
133              l_form_status := 'E';
134           END IF;
135           igs_pe_nonimg_form_pkg.update_row (
136                             x_rowid                   => l_form.rowid ,
137                             x_nonimg_form_id          => l_form.nonimg_form_id ,
138                             x_person_id               => l_form.person_id ,
139                             x_print_form              => l_form.print_form ,
140                             x_form_effective_date     => l_form.form_effective_date ,
141                             x_form_status             => l_form_status ,
142                             x_acad_term_length        => l_form.acad_term_length,
143                             x_tuition_amt             => l_form.tuition_amt,
144                             x_living_exp_amt          => l_form.living_exp_amt,
145                             x_personal_funds_amt      => l_form.personal_funds_amt,
146                             x_issue_reason            => l_form.issue_reason,
147                             x_commuter_ind            => l_form.commuter_ind,
148                             x_english_reqd            => l_form.english_reqd,
149                             x_length_of_study         => l_form.length_of_study,
150                             x_prgm_start_date         => l_form.prgm_start_date,
151                             x_prgm_end_date           => l_form.prgm_end_date,
152                             x_primary_major           => l_form.primary_major,
153                             x_education_level         => l_form.education_level,
154                             x_educ_lvl_remarks        => l_form.educ_lvl_remarks,
155                             x_depdnt_exp_amt          => l_form.depdnt_exp_amt,
156                             x_other_exp_amt           => l_form.other_exp_amt,
157                             x_other_exp_desc          => l_form.other_exp_desc,
158                             x_school_funds_amt        => l_form.school_funds_amt,
159                             x_school_funds_desc       => l_form.school_funds_desc,
160                             x_other_funds_amt         => l_form.other_funds_amt,
161                             x_other_funds_desc        => l_form.other_funds_desc,
162                             x_empl_funds_amt          => l_form.empl_funds_amt,
163                             x_remarks                 => l_form.remarks,
164 			    x_visa_type               => l_form.visa_type,
165                             x_curr_session_end_date   => l_form.curr_session_end_date,
166                             x_next_session_start_date => l_form.next_session_start_date,
167                             x_transfer_from_school    => l_form.transfer_from_school,
168                             x_other_reason            => l_form.other_reason,
169                             x_last_reprint_date       => l_form.last_reprint_date,
170                             x_reprint_reason          => l_form.reprint_reason,
171                             x_reprint_remarks         => l_form.reprint_remarks,
172                             x_secondary_major         => l_form.secondary_major,
173                             x_minor                   => l_form.minor,
174                             x_english_reqd_met        => l_form.english_reqd_met,
175                             x_not_reqd_reason         => l_form.not_reqd_reason,
176                             x_mode                    => 'R',
177 			    x_last_session_flag       => l_form.last_session_flag,
178 			    x_adjudicated_flag        => l_form.adjudicated_flag,
179 			    x_sevis_school_id        =>  l_form.SEVIS_SCHOOL_IDENTIFIER
180                             );
181 
182           OPEN  person_type(l_form.person_id);
183           FETCH person_type INTO l_person_type;
184           CLOSE person_type;
185 
186           l_end_date   := new_references.action_date;
187 	       l_end_method := 'END_NONIMG_STUDENT';
188           lv_userid    := fnd_global.user_id;
189 
190                  igs_pe_typ_instances_pkg.update_row
191                  (
192                  x_rowid                        => l_person_type.rowid,
193                  x_person_id                    => l_person_type.person_id,
194                  x_course_cd                    => l_person_type.course_cd,
195                  x_type_instance_id             => l_person_type.type_instance_id,
196                  x_person_type_code             => l_person_type.person_type_code,
197                  x_cc_version_number            => l_person_type.cc_version_number,
198                  x_funnel_status                => l_person_type.funnel_status,
199                  x_admission_appl_number        => l_person_type.admission_appl_number,
200                  x_nominated_course_cd          => l_person_type.nominated_course_cd,
201                  x_ncc_version_number           => l_person_type.ncc_version_number,
202                  x_sequence_number              => l_person_type.sequence_number,
203                  x_start_date                   => l_form.form_effective_date,
204                  x_end_date                     => l_end_date ,
205                  x_create_method                => l_person_type.create_method,
206                  x_ended_by                     => lv_userid,
207                  x_end_method                   => l_end_method,
208                  x_emplmnt_category_code        => l_person_type.emplmnt_category_code
209                  ) ;
210 
211        END ;
212   END upd_form_stat;
213 
214 
215   PROCEDURE check_uniqueness AS
216   /*
217   ||  Created By : [email protected]
218   ||  Created On : 28-NOV-2002
219   ||  Purpose : Handles the Unique Constraint logic defined for the columns.
220   ||  Known limitations, enhancements or remarks :
221   ||  Change History :
222   ||  Who             When            What
223   ||  (reverse chronological order - newest change first)
224   */
225   BEGIN
226 
227     IF ( get_uk_for_validation ( new_references.nonimg_form_id,
228                                  new_references.action_type,
229                                  new_references.action_date
230                                ) ) THEN
231       fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
232       igs_ge_msg_stack.add;
233       app_exception.raise_exception;
234     END IF;
235 
236   END check_uniqueness;
237 
238 
239   PROCEDURE check_parent_existance AS
240   /*
241   ||  Created By : [email protected]
242   ||  Created On : 28-NOV-2002
243   ||  Purpose : Checks for the existance of Parent records.
244   ||  Known limitations, enhancements or remarks :
245   ||  Change History :
246   ||  Who             When            What
247   ||  (reverse chronological order - newest change first)
248   */
249   BEGIN
250 
251     IF (((old_references.nonimg_form_id = new_references.nonimg_form_id)) OR
252         ((new_references.nonimg_form_id IS NULL))) THEN
253       NULL;
254     ELSIF NOT igs_pe_nonimg_form_pkg.get_pk_for_validation ( new_references.nonimg_form_id ) THEN
255       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
256       igs_ge_msg_stack.add;
257       app_exception.raise_exception;
258     END IF;
259 
260   END check_parent_existance;
261 
262 
263   FUNCTION get_pk_for_validation ( x_nonimg_stat_id    IN     NUMBER   ) RETURN BOOLEAN AS
264   /*
265   ||  Created By : [email protected]
266   ||  Created On : 28-NOV-2002
267   ||  Purpose : Validates the Primary Key of the table.
268   ||  Known limitations, enhancements or remarks :
269   ||  Change History :
270   ||  Who             When            What
271   ||  (reverse chronological order - newest change first)
272   */
273     CURSOR cur_rowid IS
274       SELECT   rowid
275       FROM     igs_pe_nonimg_stat
276       WHERE    nonimg_stat_id = x_nonimg_stat_id
277       FOR UPDATE NOWAIT;
278 
279     lv_rowid cur_rowid%RowType;
280 
281   BEGIN
282 
283     OPEN cur_rowid;
284     FETCH cur_rowid INTO lv_rowid;
285     IF (cur_rowid%FOUND) THEN
286       CLOSE cur_rowid;
290       RETURN(FALSE);
287       RETURN(TRUE);
288     ELSE
289       CLOSE cur_rowid;
291     END IF;
292 
293   END get_pk_for_validation;
294 
295 
296   FUNCTION get_uk_for_validation ( x_nonimg_form_id                    IN     NUMBER,
297                                    x_action_type                       IN     VARCHAR2,
298                                    x_action_date                       IN     DATE
299                                  ) RETURN BOOLEAN AS
300   /*
301   ||  Created By : [email protected]
302   ||  Created On : 28-NOV-2002
303   ||  Purpose : Validates the Unique Keys of the table.
304   ||  Known limitations, enhancements or remarks :
305   ||  Change History :
306   ||  Who             When            What
307   ||  (reverse chronological order - newest change first)
308   */
309     CURSOR cur_rowid IS
310       SELECT   rowid
311       FROM     igs_pe_nonimg_stat
312       WHERE    nonimg_form_id = x_nonimg_form_id
313       AND      action_type = x_action_type
314       AND      action_date = x_action_date
315       AND      ((l_rowid IS NULL) OR (rowid <> l_rowid));
316 
317     lv_rowid cur_rowid%RowType;
318 
319   BEGIN
320 
321     OPEN cur_rowid;
322     FETCH cur_rowid INTO lv_rowid;
323     IF (cur_rowid%FOUND) THEN
324       CLOSE cur_rowid;
325         RETURN (true);
326         ELSE
327        CLOSE cur_rowid;
328       RETURN(FALSE);
329     END IF;
330 
331   END get_uk_for_validation ;
332 
333 
334   PROCEDURE get_fk_igs_pe_nonimg_form ( x_nonimg_form_id     IN     NUMBER  ) AS
335   /*
336   ||  Created By : [email protected]
337   ||  Created On : 28-NOV-2002
338   ||  Purpose : Validates the Foreign Keys for the table.
339   ||  Known limitations, enhancements or remarks :
340   ||  Change History :
341   ||  Who             When            What
342   ||  (reverse chronological order - newest change first)
343   */
344     CURSOR cur_rowid IS
345       SELECT   rowid
346       FROM     igs_pe_nonimg_stat
347       WHERE   ((nonimg_form_id = x_nonimg_form_id));
348 
349     lv_rowid cur_rowid%RowType;
350 
351   BEGIN
352 
353     OPEN cur_rowid;
354     FETCH cur_rowid INTO lv_rowid;
355     IF (cur_rowid%FOUND) THEN
356       CLOSE cur_rowid;
357       fnd_message.set_name ('IGS', 'IGS_PE_PENST_PENIF_FK');
358       igs_ge_msg_stack.add;
359       app_exception.raise_exception;
360       RETURN;
361     END IF;
362     CLOSE cur_rowid;
363 
364   END get_fk_igs_pe_nonimg_form;
365 
366 
367   PROCEDURE before_dml (
368     p_action                            IN     VARCHAR2,
369     x_rowid                             IN     VARCHAR2,
370     x_nonimg_stat_id                    IN     NUMBER,
371     x_nonimg_form_id                    IN     NUMBER,
372     x_action_date                       IN     DATE,
373     x_action_type                       IN     VARCHAR2,
374     x_prgm_start_date                   IN     DATE,
375     x_prgm_end_date                     IN     DATE,
376     x_remarks                           IN     VARCHAR2,
377     x_termination_reason                IN     VARCHAR2,
378     x_creation_date                     IN     DATE,
379     x_created_by                        IN     NUMBER,
380     x_last_update_date                  IN     DATE,
381     x_last_updated_by                   IN     NUMBER,
382     x_last_update_login                 IN     NUMBER,
383     x_print_flag			IN     VARCHAR2,
384     x_cancel_flag			IN     VARCHAR2
385   ) AS
386   /*
387   ||  Created By : [email protected]
388   ||  Created On : 28-NOV-2002
389   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
390   ||            Trigger Handlers for the table, before any DML operation.
391   ||  Known limitations, enhancements or remarks :
392   ||  Change History :
393   ||  Who             When            What
394   ||  (reverse chronological order - newest change first)
395   */
396   BEGIN
397 
398     set_column_values (
399       p_action,
400       x_rowid,
401       x_nonimg_stat_id,
402       x_nonimg_form_id,
403       x_action_date,
404       x_action_type,
405       x_prgm_start_date,
406       x_prgm_end_date,
407       x_remarks,
408       x_termination_reason,
409       x_creation_date,
410       x_created_by,
411       x_last_update_date,
412       x_last_updated_by,
413       x_last_update_login,
414       x_print_flag,
415       x_cancel_flag
416     );
417 
418     IF (p_action = 'INSERT') THEN
419       -- Call all the procedures related to Before Insert.
420       IF ( get_pk_for_validation( new_references.nonimg_stat_id ) ) THEN
421         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
422         igs_ge_msg_stack.add;
423         app_exception.raise_exception;
424       END IF;
425       check_uniqueness;
426       check_parent_existance;
427     ELSIF (p_action = 'UPDATE') THEN
428       -- Call all the procedures related to Before Update.
429       check_uniqueness;
430       check_parent_existance;
434         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
431     ELSIF (p_action = 'VALIDATE_INSERT') THEN
432       -- Call all the procedures related to Before Insert.
433       IF ( get_pk_for_validation ( new_references.nonimg_stat_id ) ) THEN
435         igs_ge_msg_stack.add;
436         app_exception.raise_exception;
437       END IF;
438       check_uniqueness;
439     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
440       check_uniqueness;
441     END IF;
442 
443   END before_dml;
444 
445 
446   PROCEDURE insert_row (
447     x_rowid                             IN OUT NOCOPY VARCHAR2,
448     x_nonimg_stat_id                    IN OUT NOCOPY NUMBER,
449     x_nonimg_form_id                    IN     NUMBER,
450     x_action_date                       IN     DATE,
451     x_action_type                       IN     VARCHAR2,
452     x_prgm_start_date                   IN     DATE,
453     x_prgm_end_date                     IN     DATE,
454     x_remarks                           IN     VARCHAR2,
455     x_termination_reason                IN     VARCHAR2,
456     x_mode                              IN     VARCHAR2,
457     x_print_flag                        IN     VARCHAR2,
458     x_cancel_flag                       IN     VARCHAR2
459   ) AS
460   /*
461   ||  Created By : [email protected]
462   ||  Created On : 28-NOV-2002
463   ||  Purpose : Handles the INSERT DML logic for the table.
464   ||  Known limitations, enhancements or remarks :
465   ||  Change History :
466   ||  Who             When            What
467   ||  (reverse chronological order - newest change first)
468   */
469 
470     x_last_update_date           DATE;
471     x_last_updated_by            NUMBER;
472     x_last_update_login          NUMBER;
473 
474   BEGIN
475 
476     x_last_update_date := SYSDATE;
477     IF (x_mode = 'I') THEN
478       x_last_updated_by := 1;
479       x_last_update_login := 0;
480     ELSIF (X_MODE IN ('R', 'S')) THEN
481       x_last_updated_by := fnd_global.user_id;
482       IF (x_last_updated_by IS NULL) THEN
483         x_last_updated_by := -1;
484       END IF;
485       x_last_update_login := fnd_global.login_id;
486       IF (x_last_update_login IS NULL) THEN
487         x_last_update_login := -1;
488       END IF;
489     ELSE
490       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
491       igs_ge_msg_stack.add;
492       app_exception.raise_exception;
493     END IF;
494 
495     before_dml(
496       p_action                            => 'INSERT',
497       x_rowid                             => x_rowid,
498       x_nonimg_stat_id                    => x_nonimg_stat_id,
499       x_nonimg_form_id                    => x_nonimg_form_id,
500       x_action_date                       => x_action_date,
501       x_action_type                       => x_action_type,
502       x_prgm_start_date                   => x_prgm_start_date,
503       x_prgm_end_date                     => x_prgm_end_date,
504       x_remarks                           => x_remarks,
505       x_termination_reason                => x_termination_reason,
506       x_creation_date                     => x_last_update_date,
507       x_created_by                        => x_last_updated_by,
508       x_last_update_date                  => x_last_update_date,
509       x_last_updated_by                   => x_last_updated_by,
510       x_last_update_login                 => x_last_update_login,
511       x_print_flag			  => x_print_flag,
512       x_cancel_flag			  => x_cancel_flag
513     );
514 
515      IF (x_mode = 'S') THEN
516     igs_sc_gen_001.set_ctx('R');
517   END IF;
518  INSERT INTO igs_pe_nonimg_stat (
519       nonimg_stat_id,
520       nonimg_form_id,
521       action_date,
522       action_type,
523       prgm_start_date,
524       prgm_end_date,
525       remarks,
526       termination_reason,
527       creation_date,
528       created_by,
529       last_update_date,
530       last_updated_by,
531       last_update_login,
532       print_flag,
533       cancel_flag
534     ) VALUES (
535       igs_pe_nonimg_stat_s.NEXTVAL,
536       new_references.nonimg_form_id,
537       new_references.action_date,
538       new_references.action_type,
539       new_references.prgm_start_date,
540       new_references.prgm_end_date,
541       new_references.remarks,
542       new_references.termination_reason,
543       x_last_update_date,
544       x_last_updated_by,
545       x_last_update_date,
546       x_last_updated_by,
547       x_last_update_login,
548       new_references.print_flag,
549       new_references.cancel_flag
550     ) RETURNING ROWID, nonimg_stat_id INTO x_rowid, x_nonimg_stat_id;
551  IF (x_mode = 'S') THEN
552     igs_sc_gen_001.unset_ctx('R');
553   END IF;
554 
555 
556     IF new_references.action_type IN ('C','T','CP') THEN
557        upd_form_stat (new_references.action_type)  ;
558     END IF ;
559 
560 
561 EXCEPTION
562   WHEN OTHERS THEN
563     IF (SQLCODE IN (-28115, -28113, -28111)) THEN
564       fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
565       fnd_message.set_token ('ERR_CD', SQLCODE);
566       igs_ge_msg_stack.add;
567       igs_sc_gen_001.unset_ctx('R');
568       app_exception.raise_exception;
569     ELSE
570       igs_sc_gen_001.unset_ctx('R');
571       RAISE;
572     END IF;
576   PROCEDURE lock_row (
573  END insert_row;
574 
575 
577     x_rowid                             IN     VARCHAR2,
578     x_nonimg_stat_id                    IN     NUMBER,
579     x_nonimg_form_id                    IN     NUMBER,
580     x_action_date                       IN     DATE,
581     x_action_type                       IN     VARCHAR2,
582     x_prgm_start_date                   IN     DATE,
583     x_prgm_end_date                     IN     DATE,
584     x_remarks                           IN     VARCHAR2,
585     x_termination_reason                IN     VARCHAR2,
586     x_print_flag			IN     VARCHAR2,
587     x_cancel_flag			IN     VARCHAR2
588   ) AS
589   /*
590   ||  Created By : [email protected]
591   ||  Created On : 28-NOV-2002
592   ||  Purpose : Handles the LOCK mechanism for the table.
593   ||  Known limitations, enhancements or remarks :
594   ||  Change History :
595   ||  Who             When            What
596   ||  (reverse chronological order - newest change first)
597   */
598     CURSOR c1 IS
599       SELECT
600         nonimg_form_id,
601         action_date,
602         action_type,
603         prgm_start_date,
604         prgm_end_date,
605         remarks,
606         termination_reason,
607 	print_flag,
608 	cancel_flag
609       FROM  igs_pe_nonimg_stat
610       WHERE rowid = x_rowid
611       FOR UPDATE NOWAIT;
612     tlinfo c1%ROWTYPE;
613 
614   BEGIN
615 
616     OPEN c1;
617     FETCH c1 INTO tlinfo;
618     IF (c1%notfound) THEN
619       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
620       igs_ge_msg_stack.add;
621       CLOSE c1;
622       app_exception.raise_exception;
623       RETURN;
624     END IF;
625     CLOSE c1;
626 
627     IF (
628         (tlinfo.nonimg_form_id = x_nonimg_form_id)
629         AND (tlinfo.action_date = x_action_date)
630         AND (tlinfo.action_type = x_action_type)
631         AND ((tlinfo.prgm_start_date = x_prgm_start_date) OR ((tlinfo.prgm_start_date IS NULL) AND (X_prgm_start_date IS NULL)))
632         AND ((tlinfo.prgm_end_date = x_prgm_end_date) OR ((tlinfo.prgm_end_date IS NULL) AND (X_prgm_end_date IS NULL)))
633         AND ((tlinfo.remarks = x_remarks) OR ((tlinfo.remarks IS NULL) AND (X_remarks IS NULL)))
634         AND ((tlinfo.termination_reason = x_termination_reason) OR ((tlinfo.termination_reason IS NULL) AND (X_termination_reason IS NULL)))
635         AND ((tlinfo.print_flag = x_print_flag) OR ((tlinfo.print_flag IS NULL) AND (X_print_flag IS NULL)))
636 	AND ((tlinfo.cancel_flag = x_cancel_flag) OR ((tlinfo.cancel_flag IS NULL) AND (X_cancel_flag IS NULL)))
637        ) THEN
638       NULL;
639     ELSE
640       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
641       igs_ge_msg_stack.add;
642       app_exception.raise_exception;
643     END IF;
644 
645     RETURN;
646 
647   END lock_row;
648 
649 
650   PROCEDURE update_row (
651     x_rowid                             IN     VARCHAR2,
652     x_nonimg_stat_id                    IN     NUMBER,
653     x_nonimg_form_id                    IN     NUMBER,
654     x_action_date                       IN     DATE,
655     x_action_type                       IN     VARCHAR2,
656     x_prgm_start_date                   IN     DATE,
657     x_prgm_end_date                     IN     DATE,
658     x_remarks                           IN     VARCHAR2,
659     x_termination_reason                IN     VARCHAR2,
660     x_mode                              IN     VARCHAR2 ,
661     x_print_flag                        IN     VARCHAR2,
662     x_cancel_flag                       IN     VARCHAR2
663   ) AS
664   /*
665   ||  Created By : [email protected]
666   ||  Created On : 28-NOV-2002
667   ||  Purpose : Handles the UPDATE DML logic for the table.
668   ||  Known limitations, enhancements or remarks :
669   ||  Change History :
670   ||  Who             When            What
671   ||  (reverse chronological order - newest change first)
672   */
673     x_last_update_date           DATE ;
674     x_last_updated_by            NUMBER;
675     x_last_update_login          NUMBER;
676 
677   BEGIN
678 
679     x_last_update_date := SYSDATE;
680     IF (X_MODE = 'I') THEN
681       x_last_updated_by := 1;
682       x_last_update_login := 0;
683     ELSIF (X_MODE IN ('R', 'S')) THEN
684       x_last_updated_by := fnd_global.user_id;
685       IF x_last_updated_by IS NULL THEN
686         x_last_updated_by := -1;
687       END IF;
688       x_last_update_login := fnd_global.login_id;
689       IF (x_last_update_login IS NULL) THEN
690         x_last_update_login := -1;
691       END IF;
692     ELSE
693       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
694       igs_ge_msg_stack.add;
695       app_exception.raise_exception;
696     END IF;
697 
698     before_dml(
699       p_action                            => 'UPDATE',
700       x_rowid                             => x_rowid,
701       x_nonimg_stat_id                    => x_nonimg_stat_id,
702       x_nonimg_form_id                    => x_nonimg_form_id,
703       x_action_date                       => x_action_date,
704       x_action_type                       => x_action_type,
705       x_prgm_start_date                   => x_prgm_start_date,
706       x_prgm_end_date                     => x_prgm_end_date,
707       x_remarks                           => x_remarks,
708       x_termination_reason                => x_termination_reason,
709       x_creation_date                     => x_last_update_date,
710       x_created_by                        => x_last_updated_by,
711       x_last_update_date                  => x_last_update_date,
712       x_last_updated_by                   => x_last_updated_by,
713       x_last_update_login                 => x_last_update_login,
714       x_print_flag			  => x_print_flag,
715       x_cancel_flag			  => x_cancel_flag
716     );
717 
718      IF (x_mode = 'S') THEN
719     igs_sc_gen_001.set_ctx('R');
720   END IF;
721  UPDATE igs_pe_nonimg_stat
722       SET
723         nonimg_form_id                    = new_references.nonimg_form_id,
724         action_date                       = new_references.action_date,
725         action_type                       = new_references.action_type,
726         prgm_start_date                   = new_references.prgm_start_date,
727         prgm_end_date                     = new_references.prgm_end_date,
728         remarks                           = new_references.remarks,
729         termination_reason                = new_references.termination_reason,
730         last_update_date                  = x_last_update_date,
731         last_updated_by                   = x_last_updated_by,
732         last_update_login                 = x_last_update_login ,
733 	print_flag			  = x_print_flag,
734 	cancel_flag			  = x_cancel_flag
735       WHERE rowid = x_rowid;
736 
737     IF (SQL%NOTFOUND) THEN
738      fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
739      igs_ge_msg_stack.add;
740      igs_sc_gen_001.unset_ctx('R');
741      app_exception.raise_exception;
742  END IF;
743  IF (x_mode = 'S') THEN
744     igs_sc_gen_001.unset_ctx('R');
745   END IF;
746 
747 
748 
749 EXCEPTION
750   WHEN OTHERS THEN
751     IF (SQLCODE = (-28115)) THEN
752       fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
753       fnd_message.set_token ('ERR_CD', SQLCODE);
754       igs_ge_msg_stack.add;
755       igs_sc_gen_001.unset_ctx('R');
756       app_exception.raise_exception;
757     ELSE
758       igs_sc_gen_001.unset_ctx('R');
759       RAISE;
760     END IF;
761  END update_row;
762 
763 
764   PROCEDURE add_row (
765     x_rowid                             IN OUT NOCOPY VARCHAR2,
766     x_nonimg_stat_id                    IN OUT NOCOPY NUMBER,
767     x_nonimg_form_id                    IN     NUMBER,
768     x_action_date                       IN     DATE,
769     x_action_type                       IN     VARCHAR2,
770     x_prgm_start_date                   IN     DATE,
771     x_prgm_end_date                     IN     DATE,
772     x_remarks                           IN     VARCHAR2,
773     x_termination_reason                IN     VARCHAR2,
774     x_mode                              IN     VARCHAR2 ,
775     x_print_flag			IN     VARCHAR2,
776     x_cancel_flag			IN     VARCHAR2
777   ) AS
778   /*
779   ||  Created By : [email protected]
780   ||  Created On : 28-NOV-2002
781   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
782   ||  Known limitations, enhancements or remarks :
783   ||  Change History :
784   ||  Who             When            What
785   ||  (reverse chronological order - newest change first)
786   */
787     CURSOR c1 IS
788       SELECT   rowid
789       FROM     igs_pe_nonimg_stat
790       WHERE    nonimg_stat_id    = x_nonimg_stat_id;
791 
792   BEGIN
793 
794     OPEN c1;
795     FETCH c1 INTO x_rowid;
796     IF (c1%NOTFOUND) THEN
797       CLOSE c1;
798 
799       insert_row (
800         x_rowid,
801         x_nonimg_stat_id,
802         x_nonimg_form_id,
803         x_action_date,
804         x_action_type,
805         x_prgm_start_date,
806         x_prgm_end_date,
807         x_remarks,
808         x_termination_reason,
809         x_mode ,
810 	x_print_flag,
811 	x_cancel_flag
812       );
813       RETURN;
814     END IF;
815     CLOSE c1;
816 
817     update_row (
818       x_rowid,
819       x_nonimg_stat_id,
820       x_nonimg_form_id,
821       x_action_date,
822       x_action_type,
823       x_prgm_start_date,
824       x_prgm_end_date,
825       x_remarks,
826       x_termination_reason,
827       x_mode ,
828       x_print_flag,
829       x_cancel_flag
830     );
831 
832   END add_row;
833 
834 
835   PROCEDURE delete_row ( x_rowid IN VARCHAR2,
836   x_mode IN VARCHAR2   ) AS
837   /*
838   ||  Created By : [email protected]
839   ||  Created On : 28-NOV-2002
840   ||  Purpose : Handles the DELETE DML logic for the table.
841   ||  Known limitations, enhancements or remarks :
842   ||  Change History :
843   ||  Who             When            What
844   ||  (reverse chronological order - newest change first)
845   */
846   BEGIN
847 
848     before_dml (
849       p_action => 'DELETE',
850       x_rowid => x_rowid
851     );
852 
853      IF (x_mode = 'S') THEN
854     igs_sc_gen_001.set_ctx('R');
855   END IF;
856  DELETE FROM igs_pe_nonimg_stat
857     WHERE rowid = x_rowid;
858 
859     IF (SQL%NOTFOUND) THEN
860      fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
861      igs_ge_msg_stack.add;
862      igs_sc_gen_001.unset_ctx('R');
863      app_exception.raise_exception;
864  END IF;
865  IF (x_mode = 'S') THEN
866     igs_sc_gen_001.unset_ctx('R');
867   END IF;
868 
869 
870   END delete_row;
871 
872 
873 END igs_pe_nonimg_stat_pkg;