[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_SVS_AUTH_PKG
Source
1 PACKAGE BODY igs_en_svs_auth_pkg AS
2 /* $Header: IGSEI66B.pls 120.1 2006/05/02 01:42:09 amuthu noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_svs_auth%ROWTYPE;
6 new_references igs_en_svs_auth%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_sevis_authorization_code IN VARCHAR2,
12 x_start_dt IN DATE,
13 x_end_dt IN DATE,
14 x_comments IN VARCHAR2,
15 x_sevis_auth_id IN NUMBER,
16 x_sevis_authorization_no IN NUMBER,
17 x_person_id IN NUMBER,
18 x_cancel_flag 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 ) AS
25 /*
26 || Created By :
27 || Created On : 08-MAR-2006
28 || Purpose : Initialises the Old and New references for the columns of the table.
29 || Known limitations, enhancements or remarks :
30 || Change History :
31 || Who When What
32 || (reverse chronological order - newest change first)
33 */
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM igs_en_svs_auth
38 WHERE rowid = x_rowid;
39
40 CURSOR c_max_auth_no (cp_person_id HZ_PARTIES.PARTY_ID%TYPE) IS
41 SELECT SEVIS_AUTHORIZATION_NO
42 FROM igs_en_svs_auth esa1
43 WHERE person_id = cp_person_id
44 AND SEVIS_AUTHORIZATION_NO = (SELECT max(SEVIS_AUTHORIZATION_NO)
45 FROM igs_en_svs_auth esa2
46 WHERE esa2.person_id = esa1.person_id)
47 FOR UPDATE;
48
49
50 l_sevis_authorization_no igs_en_svs_auth.SEVIS_AUTHORIZATION_NO%TYPE;
51
52 BEGIN
53
54 l_rowid := x_rowid;
55
56 -- Code for setting the Old and New Reference Values.
57 -- Populate Old Values.
58 OPEN cur_old_ref_values;
59 FETCH cur_old_ref_values INTO old_references;
60 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
61 CLOSE cur_old_ref_values;
62 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
63 igs_ge_msg_stack.add;
64 app_exception.raise_exception;
65 RETURN;
66 END IF;
67 CLOSE cur_old_ref_values;
68
69 -- Populate New Values.
70 new_references.sevis_authorization_code := x_sevis_authorization_code;
71 new_references.start_dt := x_start_dt;
72 new_references.end_dt := x_end_dt;
73 new_references.comments := x_comments;
74 new_references.sevis_auth_id := x_sevis_auth_id;
75
76 IF p_action = 'INSERT' THEN
77 OPEN c_max_auth_no(x_person_id);
78 FETCH c_max_auth_no INTO l_sevis_authorization_no;
79 CLOSE c_max_auth_no;
80 new_references.sevis_authorization_no := NVL(l_sevis_authorization_no,0) + 1;
81 ELSE
82 new_references.sevis_authorization_no := x_sevis_authorization_no;
83 END IF;
84
85 new_references.person_id := x_person_id;
86 new_references.cancel_flag := x_cancel_flag;
87
88 IF (p_action = 'UPDATE') THEN
89 new_references.creation_date := old_references.creation_date;
90 new_references.created_by := old_references.created_by;
91 ELSE
92 new_references.creation_date := x_creation_date;
93 new_references.created_by := x_created_by;
94 END IF;
95
96 new_references.last_update_date := x_last_update_date;
97 new_references.last_updated_by := x_last_updated_by;
98 new_references.last_update_login := x_last_update_login;
99
100 END set_column_values;
101
102
103 PROCEDURE check_uniqueness AS
104 /*
105 || Created By :
106 || Created On : 08-MAR-2006
107 || Purpose : Handles the Unique Constraint logic defined for the columns.
108 || Known limitations, enhancements or remarks :
109 || Change History :
110 || Who When What
111 || (reverse chronological order - newest change first)
112 */
113 BEGIN
114 IF new_references.cancel_flag <> 'Y' THEN
115 IF ( get_uk_for_validation (
116 new_references.person_id,
117 new_references.sevis_authorization_code,
118 new_references.start_dt
119 )
120 ) THEN
121 fnd_message.set_name ('IGS', 'IGS_EN_SVS_AUTH_CD_REPEAT');
122 igs_ge_msg_stack.add;
123 app_exception.raise_exception;
124 END IF;
125 END IF;
126
127 END check_uniqueness;
128
129
130 PROCEDURE check_parent_existance AS
131 /*
132 || Created By : [email protected]
133 || Created On : 25-JUN-2001
134 || Purpose : Checks for the existance of Parent records.
135 || Known limitations, enhancements or remarks :
136 || Change History :
137 || Who When What
138 || (reverse chronological order - newest change first)
139 */
140 BEGIN
141
142 IF NOT igs_pe_person_pkg.get_pk_for_validation (
143 new_references.person_id
144 ) THEN
145 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
146 igs_ge_msg_stack.add;
147 app_exception.raise_exception;
148 END IF;
149
150 END check_parent_existance;
151
152 PROCEDURE check_child_existance AS
153 /*
154 || Created By :
155 || Created On : 08-MAR-2006
156 || Purpose : Checks for the existance of Child records.
157 || Known limitations, enhancements or remarks :
158 || Change History :
159 || Who When What
160 || (reverse chronological order - newest change first)
161 */
162 BEGIN
163
164 igs_en_svs_auth_cal_pkg.get_fk_igs_en_svs_auth (
165 old_references.sevis_auth_id
166 );
167
168 END check_child_existance;
169
170
171 FUNCTION get_pk_for_validation (
172 x_sevis_auth_id IN NUMBER
173 ) RETURN BOOLEAN AS
174 /*
175 || Created By :
176 || Created On : 08-MAR-2006
177 || Purpose : Validates the Primary Key of the table.
178 || Known limitations, enhancements or remarks :
179 || Change History :
180 || Who When What
181 || (reverse chronological order - newest change first)
182 */
183 CURSOR cur_rowid IS
184 SELECT rowid
185 FROM igs_en_svs_auth
186 WHERE sevis_auth_id = x_sevis_auth_id
187 FOR UPDATE NOWAIT;
188
189 lv_rowid cur_rowid%RowType;
190
191 BEGIN
192
193 OPEN cur_rowid;
194 FETCH cur_rowid INTO lv_rowid;
195 IF (cur_rowid%FOUND) THEN
196 CLOSE cur_rowid;
197 RETURN(TRUE);
198 ELSE
199 CLOSE cur_rowid;
200 RETURN(FALSE);
201 END IF;
202
203 END get_pk_for_validation;
204
205
206 FUNCTION get_uk_for_validation (
207 x_person_id IN NUMBER,
208 x_sevis_authorization_code IN VARCHAR2,
209 x_start_dt IN DATE
210 ) RETURN BOOLEAN AS
211 /*
212 || Created By :
213 || Created On : 08-MAR-2006
214 || Purpose : Validates the Unique Keys of the table.
215 || Known limitations, enhancements or remarks :
216 || Change History :
217 || Who When What
218 || (reverse chronological order - newest change first)
219 */
220 CURSOR cur_rowid IS
221 SELECT rowid
222 FROM igs_en_svs_auth
223 WHERE person_id = x_person_id
224 AND sevis_authorization_code = x_sevis_authorization_code
225 AND start_dt = x_start_dt
226 AND cancel_flag <> 'Y'
227 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
228
229 lv_rowid cur_rowid%RowType;
230
231 BEGIN
232
233 OPEN cur_rowid;
234 FETCH cur_rowid INTO lv_rowid;
235 IF (cur_rowid%FOUND) THEN
236 CLOSE cur_rowid;
237 RETURN (true);
238 ELSE
239 CLOSE cur_rowid;
240 RETURN(FALSE);
241 END IF;
242
243 END get_uk_for_validation ;
244
245 PROCEDURE beforeudpate1(
246 x_sevis_auth_id IN NUMBER
247 ) AS
248
249 CURSOR c_auth_cal IS
250 SELECT sac.rowid row_id
251 FROM IGS_EN_SVS_AUTH_CAL sac
252 WHERE sevis_auth_id = x_sevis_auth_id;
253
254
255 BEGIN
256
257 FOR c_auth_cal_rec in c_auth_cal LOOP
258
259 IGS_EN_SVS_AUTH_CAL_PKG.DELETE_ROW(c_auth_cal_rec.row_id);
260
261 END LOOP;
262
263
264 END beforeudpate1;
265
266 PROCEDURE before_dml (
267 p_action IN VARCHAR2,
268 x_rowid IN VARCHAR2,
269 x_sevis_authorization_code IN VARCHAR2,
270 x_start_dt IN DATE,
271 x_end_dt IN DATE,
272 x_comments IN VARCHAR2,
273 x_sevis_auth_id IN NUMBER,
274 x_sevis_authorization_no IN NUMBER,
275 x_person_id IN NUMBER,
276 x_cancel_flag IN VARCHAR2,
277 x_creation_date IN DATE,
278 x_created_by IN NUMBER,
279 x_last_update_date IN DATE,
280 x_last_updated_by IN NUMBER,
281 x_last_update_login IN NUMBER
282 ) AS
283 /*
284 || Created By :
285 || Created On : 08-MAR-2006
286 || Purpose : Initialises the columns, Checks Constraints, Calls the
287 || Trigger Handlers for the table, before any DML operation.
288 || Known limitations, enhancements or remarks :
289 || Change History :
290 || Who When What
291 || (reverse chronological order - newest change first)
292 */
293
294 l_no_of_months NUMBER;
295
296 BEGIN
297
298 set_column_values (
299 p_action,
300 x_rowid,
301 x_sevis_authorization_code,
302 x_start_dt,
303 x_end_dt,
304 x_comments,
305 x_sevis_auth_id,
306 x_sevis_authorization_no,
307 x_person_id,
308 x_cancel_flag,
309 x_creation_date,
310 x_created_by,
311 x_last_update_date,
312 x_last_updated_by,
313 x_last_update_login
314 );
315
316 IF (p_action = 'INSERT') THEN
317 -- Call all the procedures related to Before Insert.
318 IF ( get_pk_for_validation(
319 new_references.sevis_auth_id
320 )
321 ) THEN
322 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
323 igs_ge_msg_stack.add;
324 app_exception.raise_exception;
325 END IF;
326 check_uniqueness;
327 check_parent_existance;
328 ELSIF (p_action = 'UPDATE') THEN
329 -- Call all the procedures related to Before Update.
330 check_uniqueness;
331 check_parent_existance;
332 ELSIF (p_action = 'DELETE') THEN
333 -- Call all the procedures related to Before Delete.
334 check_child_existance;
335 ELSIF (p_action = 'VALIDATE_INSERT') THEN
336 -- Call all the procedures related to Before Insert.
337 IF ( get_pk_for_validation (
338 new_references.sevis_auth_id
339 )
340 ) THEN
341 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
342 igs_ge_msg_stack.add;
343 app_exception.raise_exception;
344 END IF;
345 check_uniqueness;
346 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
347 check_uniqueness;
348 ELSIF (p_action = 'VALIDATE_DELETE') THEN
349 check_child_existance;
350 END IF;
351
352 IF (p_action = 'INSERT' OR p_action = 'UPDATE') THEN
353 IF igs_en_sevis.is_auth_rec_duration_exceeds(new_references.person_id,
354 new_references.start_dt,
355 new_references.end_dt,
356 l_no_of_months) THEN
357 FND_MESSAGE.Set_name('IGS', 'IGS_EN_AUTH_PERIOD_VAL');
358 FND_MESSAGE.Set_token('N',l_no_of_months );
359 igs_ge_msg_stack.add;
360 app_exception.raise_exception;
361 END IF;
362 END IF;
363
364 IF (p_action = 'UPDATE') AND new_references.cancel_flag = 'Y'
365 AND new_references.cancel_flag <> old_references.cancel_flag THEN
366 beforeudpate1(new_references.sevis_auth_id);
367 END IF;
368
369 END before_dml;
370
371
372 PROCEDURE insert_row (
373 x_rowid IN OUT NOCOPY VARCHAR2,
374 x_sevis_authorization_code IN VARCHAR2,
375 x_start_dt IN DATE,
376 x_end_dt IN DATE,
377 x_comments IN VARCHAR2,
378 x_sevis_auth_id IN OUT NOCOPY NUMBER,
379 x_sevis_authorization_no IN OUT NOCOPY NUMBER,
380 x_person_id IN NUMBER,
381 x_cancel_flag IN VARCHAR2,
382 x_mode IN VARCHAR2
383 ) AS
384 /*
385 || Created By :
386 || Created On : 08-MAR-2006
387 || Purpose : Handles the INSERT DML logic for the table.
388 || Known limitations, enhancements or remarks :
389 || Change History :
390 || Who When What
391 || (reverse chronological order - newest change first)
392 */
393
394 x_last_update_date DATE;
395 x_last_updated_by NUMBER;
396 x_last_update_login NUMBER;
397 x_request_id NUMBER;
398 x_program_id NUMBER;
399 x_program_application_id NUMBER;
400 x_program_update_date DATE;
401
402 BEGIN
403
404 x_last_update_date := SYSDATE;
405 IF (x_mode = 'I') THEN
406 x_last_updated_by := 1;
407 x_last_update_login := 0;
408 ELSIF (x_mode = 'R') THEN
409 x_last_updated_by := fnd_global.user_id;
410 IF (x_last_updated_by IS NULL) THEN
411 x_last_updated_by := -1;
412 END IF;
413 x_last_update_login := fnd_global.login_id;
414 IF (x_last_update_login IS NULL) THEN
415 x_last_update_login := -1;
416 END IF;
417 x_request_id := fnd_global.conc_request_id;
418 x_program_id := fnd_global.conc_program_id;
419 x_program_application_id := fnd_global.prog_appl_id;
420
421 IF (x_request_id = -1) THEN
422 x_request_id := NULL;
423 x_program_id := NULL;
424 x_program_application_id := NULL;
425 x_program_update_date := NULL;
426 ELSE
427 x_program_update_date := SYSDATE;
428 END IF;
429 ELSE
430 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
431 fnd_message.set_token ('ROUTINE', 'IGS_EN_SVS_AUTH_PKG.INSERT_ROW');
432 igs_ge_msg_stack.add;
433 app_exception.raise_exception;
434 END IF;
435
436 x_sevis_auth_id := NULL;
437
438 before_dml(
439 p_action => 'INSERT',
440 x_rowid => x_rowid,
441 x_sevis_authorization_code => x_sevis_authorization_code,
442 x_start_dt => x_start_dt,
443 x_end_dt => x_end_dt,
444 x_comments => x_comments,
445 x_sevis_auth_id => x_sevis_auth_id,
446 x_sevis_authorization_no => x_sevis_authorization_no,
447 x_person_id => x_person_id,
448 x_cancel_flag => x_cancel_flag,
449 x_creation_date => x_last_update_date,
450 x_created_by => x_last_updated_by,
451 x_last_update_date => x_last_update_date,
452 x_last_updated_by => x_last_updated_by,
453 x_last_update_login => x_last_update_login
454 );
455
456 INSERT INTO igs_en_svs_auth (
457 sevis_authorization_code,
458 start_dt,
459 end_dt,
460 comments,
461 sevis_auth_id,
462 sevis_authorization_no,
463 person_id,
464 cancel_flag,
465 creation_date,
466 created_by,
467 last_update_date,
468 last_updated_by,
469 last_update_login,
470 request_id,
471 program_id,
472 program_application_id,
473 program_update_date
474 ) VALUES (
475 new_references.sevis_authorization_code,
476 new_references.start_dt,
477 new_references.end_dt,
478 new_references.comments,
479 igs_en_svs_auth_s.NEXTVAL,
480 new_references.sevis_authorization_no,
481 new_references.person_id,
482 new_references.cancel_flag,
483 x_last_update_date,
484 x_last_updated_by,
485 x_last_update_date,
486 x_last_updated_by,
487 x_last_update_login ,
488 x_request_id,
489 x_program_id,
490 x_program_application_id,
491 x_program_update_date
492 ) RETURNING ROWID, sevis_auth_id,sevis_authorization_no
493 INTO x_rowid, x_sevis_auth_id, x_sevis_authorization_no;
494
495 END insert_row;
496
497
498 PROCEDURE lock_row (
499 x_rowid IN VARCHAR2,
500 x_sevis_authorization_code IN VARCHAR2,
501 x_start_dt IN DATE,
502 x_end_dt IN DATE,
503 x_comments IN VARCHAR2,
504 x_sevis_auth_id IN NUMBER,
505 x_sevis_authorization_no IN NUMBER,
506 x_person_id IN NUMBER,
507 x_cancel_flag IN VARCHAR2
508 ) AS
509 /*
510 || Created By :
511 || Created On : 08-MAR-2006
512 || Purpose : Handles the LOCK mechanism for the table.
513 || Known limitations, enhancements or remarks :
514 || Change History :
515 || Who When What
516 || (reverse chronological order - newest change first)
517 */
518 CURSOR c1 IS
519 SELECT
520 sevis_authorization_code,
521 start_dt,
522 end_dt,
523 comments,
524 sevis_authorization_no,
525 person_id,
526 cancel_flag
527 FROM igs_en_svs_auth
528 WHERE rowid = x_rowid
529 FOR UPDATE NOWAIT;
530
531 tlinfo c1%ROWTYPE;
532
533 BEGIN
534
535 OPEN c1;
536 FETCH c1 INTO tlinfo;
537 IF (c1%notfound) THEN
538 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
539 igs_ge_msg_stack.add;
540 CLOSE c1;
541 app_exception.raise_exception;
542 RETURN;
543 END IF;
544 CLOSE c1;
545
546 IF (
547 (tlinfo.sevis_authorization_code = x_sevis_authorization_code)
548 AND (trunc(tlinfo.start_dt) = trunc(x_start_dt))
549 AND (trunc(tlinfo.end_dt) = trunc(x_end_dt))
550 AND ((tlinfo.comments = x_comments) OR ((tlinfo.comments IS NULL) AND (X_comments IS NULL)))
551 AND (tlinfo.sevis_authorization_no = x_sevis_authorization_no)
552 AND (tlinfo.person_id = x_person_id)
553 AND (tlinfo.cancel_flag = x_cancel_flag)
554 ) THEN
555 NULL;
556 ELSE
557 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
558 igs_ge_msg_stack.add;
559 app_exception.raise_exception;
560 END IF;
561
562 RETURN;
563
564 END lock_row;
565
566
567 PROCEDURE update_row (
568 x_rowid IN VARCHAR2,
569 x_sevis_authorization_code IN VARCHAR2,
570 x_start_dt IN DATE,
571 x_end_dt IN DATE,
572 x_comments IN VARCHAR2,
573 x_sevis_auth_id IN NUMBER,
574 x_sevis_authorization_no IN NUMBER,
575 x_person_id IN NUMBER,
576 x_cancel_flag IN VARCHAR2,
577 x_mode IN VARCHAR2
578 ) AS
579 /*
580 || Created By :
581 || Created On : 08-MAR-2006
582 || Purpose : Handles the UPDATE DML logic for the table.
583 || Known limitations, enhancements or remarks :
584 || Change History :
585 || Who When What
586 || (reverse chronological order - newest change first)
587 */
588 x_last_update_date DATE ;
589 x_last_updated_by NUMBER;
590 x_last_update_login NUMBER;
591 x_request_id NUMBER;
592 x_program_id NUMBER;
593 x_program_application_id NUMBER;
594 x_program_update_date DATE;
595
596 BEGIN
597
598 x_last_update_date := SYSDATE;
599 IF (X_MODE = 'I') THEN
600 x_last_updated_by := 1;
601 x_last_update_login := 0;
602 ELSIF (x_mode = 'R') THEN
603 x_last_updated_by := fnd_global.user_id;
604 IF x_last_updated_by IS NULL THEN
605 x_last_updated_by := -1;
606 END IF;
607 x_last_update_login := fnd_global.login_id;
608 IF (x_last_update_login IS NULL) THEN
609 x_last_update_login := -1;
610 END IF;
611 ELSE
612 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
613 fnd_message.set_token ('ROUTINE', 'IGS_EN_SVS_AUTH_PKG.UPDATE_ROW');
614 igs_ge_msg_stack.add;
615 app_exception.raise_exception;
616 END IF;
617
618 before_dml(
619 p_action => 'UPDATE',
620 x_rowid => x_rowid,
621 x_sevis_authorization_code => x_sevis_authorization_code,
622 x_start_dt => x_start_dt,
623 x_end_dt => x_end_dt,
624 x_comments => x_comments,
625 x_sevis_auth_id => x_sevis_auth_id,
626 x_sevis_authorization_no => x_sevis_authorization_no,
627 x_person_id => x_person_id,
628 x_cancel_flag => x_cancel_flag,
629 x_creation_date => x_last_update_date,
630 x_created_by => x_last_updated_by,
631 x_last_update_date => x_last_update_date,
632 x_last_updated_by => x_last_updated_by,
633 x_last_update_login => x_last_update_login
634 );
635
636 IF (x_mode = 'R') THEN
637 x_request_id := fnd_global.conc_request_id;
638 x_program_id := fnd_global.conc_program_id;
639 x_program_application_id := fnd_global.prog_appl_id;
640 IF (x_request_id = -1) THEN
641 x_request_id := old_references.request_id;
642 x_program_id := old_references.program_id;
643 x_program_application_id := old_references.program_application_id;
644 x_program_update_date := old_references.program_update_date;
645 ELSE
646 x_program_update_date := SYSDATE;
647 END IF;
648 END IF;
649
650 UPDATE igs_en_svs_auth
651 SET
652 sevis_authorization_code = new_references.sevis_authorization_code,
653 start_dt = new_references.start_dt,
654 end_dt = new_references.end_dt,
655 comments = new_references.comments,
656 sevis_authorization_no = new_references.sevis_authorization_no,
657 person_id = new_references.person_id,
658 cancel_flag = new_references.cancel_flag,
659 last_update_date = x_last_update_date,
660 last_updated_by = x_last_updated_by,
661 last_update_login = x_last_update_login ,
662 request_id = x_request_id,
663 program_id = x_program_id,
664 program_application_id = x_program_application_id,
665 program_update_date = x_program_update_date
666 WHERE rowid = x_rowid;
667
668 IF (SQL%NOTFOUND) THEN
669 RAISE NO_DATA_FOUND;
670 END IF;
671
672 END update_row;
673
674
675 PROCEDURE add_row (
676 x_rowid IN OUT NOCOPY VARCHAR2,
677 x_sevis_authorization_code IN VARCHAR2,
678 x_start_dt IN DATE,
679 x_end_dt IN DATE,
680 x_comments IN VARCHAR2,
681 x_sevis_auth_id IN OUT NOCOPY NUMBER,
682 x_sevis_authorization_no IN OUT NOCOPY NUMBER,
683 x_person_id IN NUMBER,
684 x_cancel_flag IN VARCHAR2,
685 x_mode IN VARCHAR2
686 ) AS
687 /*
688 || Created By :
689 || Created On : 08-MAR-2006
690 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
691 || Known limitations, enhancements or remarks :
692 || Change History :
693 || Who When What
694 || (reverse chronological order - newest change first)
695 */
696 CURSOR c1 IS
697 SELECT rowid
698 FROM igs_en_svs_auth
699 WHERE sevis_auth_id = x_sevis_auth_id;
700
701 BEGIN
702
703 OPEN c1;
704 FETCH c1 INTO x_rowid;
705 IF (c1%NOTFOUND) THEN
706 CLOSE c1;
707
708 insert_row (
709 x_rowid,
710 x_sevis_authorization_code,
711 x_start_dt,
712 x_end_dt,
713 x_comments,
714 x_sevis_auth_id,
715 x_sevis_authorization_no,
716 x_person_id,
717 x_cancel_flag,
718 x_mode
719 );
720 RETURN;
721 END IF;
722 CLOSE c1;
723
724 update_row (
725 x_rowid,
726 x_sevis_authorization_code,
727 x_start_dt,
728 x_end_dt,
729 x_comments,
730 x_sevis_auth_id,
731 x_sevis_authorization_no,
732 x_person_id,
733 x_cancel_flag,
734 x_mode
735 );
736
737 END add_row;
738
739
740 PROCEDURE delete_row (
741 x_rowid IN VARCHAR2
742 ) AS
743 /*
744 || Created By :
745 || Created On : 08-MAR-2006
746 || Purpose : Handles the DELETE DML logic for the table.
747 || Known limitations, enhancements or remarks :
748 || Change History :
749 || Who When What
750 || (reverse chronological order - newest change first)
751 */
752 BEGIN
753
754 before_dml (
755 p_action => 'DELETE',
756 x_rowid => x_rowid
757 );
758
759 DELETE FROM igs_en_svs_auth
760 WHERE rowid = x_rowid;
761
762 IF (SQL%NOTFOUND) THEN
763 RAISE NO_DATA_FOUND;
764 END IF;
765
766 END delete_row;
767
768
769 END igs_en_svs_auth_pkg;