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