1 PACKAGE BODY igs_he_st_unt_vs_all_pkg AS
2 /* $Header: IGSWI24B.pls 120.1 2006/02/06 19:53:52 jbaber noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_st_unt_vs_all%ROWTYPE;
6 new_references igs_he_st_unt_vs_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_hesa_st_unt_vs_id IN NUMBER ,
12 x_org_id IN NUMBER ,
13 x_unit_cd IN VARCHAR2 ,
14 x_version_number IN NUMBER ,
15 x_prop_of_teaching_in_welsh IN NUMBER ,
16 x_credit_transfer_scheme IN VARCHAR2 ,
17 x_module_length IN NUMBER ,
18 x_proportion_of_fte IN NUMBER ,
19 x_location_cd IN VARCHAR2 ,
20 x_creation_date IN DATE ,
21 x_created_by IN NUMBER ,
22 x_last_update_date IN DATE ,
23 x_last_updated_by IN NUMBER ,
24 x_last_update_login IN NUMBER ,
25 x_exclude_flag IN VARCHAR2
26 ) AS
27 /*
28 || Created By : [email protected]
29 || Created On : 29-JAN-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 || sbaliga 8-apr-2002 Added a parameter x_location_cd as part of #2278825
35 || (reverse chronological order - newest change first)
36 */
37
38 CURSOR cur_old_ref_values IS
39 SELECT *
40 FROM IGS_HE_ST_UNT_VS_ALL
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.hesa_st_unt_vs_id := x_hesa_st_unt_vs_id;
62 new_references.org_id := x_org_id;
63 new_references.unit_cd := x_unit_cd;
64 new_references.version_number := x_version_number;
65 new_references.prop_of_teaching_in_welsh := x_prop_of_teaching_in_welsh;
66 new_references.credit_transfer_scheme := x_credit_transfer_scheme;
67 new_references.module_length := x_module_length;
68 new_references.proportion_of_fte := x_proportion_of_fte;
69 new_references.location_cd := x_location_cd;
70 new_references.exclude_flag := x_exclude_flag;
71
72 IF (p_action = 'UPDATE') THEN
73 new_references.creation_date := old_references.creation_date;
74 new_references.created_by := old_references.created_by;
75 ELSE
76 new_references.creation_date := x_creation_date;
77 new_references.created_by := x_created_by;
78 END IF;
79
80 new_references.last_update_date := x_last_update_date;
81 new_references.last_updated_by := x_last_updated_by;
82 new_references.last_update_login := x_last_update_login;
83
84 END set_column_values;
85
86
87 PROCEDURE check_uniqueness AS
88 /*
89 || Created By : [email protected]
90 || Created On : 29-JAN-2002
91 || Purpose : Handles the Unique Constraint logic defined for the columns.
92 || Known limitations, enhancements or remarks :
93 || Change History :
94 || Who When What
95 || (reverse chronological order - newest change first)
96 */
97 BEGIN
98
99 IF ( get_uk_for_validation (
100 new_references.unit_cd,
101 new_references.version_number
102 )
103 ) THEN
104 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
105 igs_ge_msg_stack.add;
106 app_exception.raise_exception;
107 END IF;
108
109 END check_uniqueness;
110
111
112 PROCEDURE check_parent_existance AS
113 /*
114 || Created By : [email protected]
115 || Created On : 29-JAN-2002
116 || Purpose : Checks for the existance of Parent records.
117 || Known limitations, enhancements or remarks :
118 || Change History :
119 || Who When What
120 || (reverse chronological order - newest change first)
121 */
122 BEGIN
123
124 IF (((old_references.unit_cd = new_references.unit_cd) AND
125 (old_references.version_number = new_references.version_number)) OR
126 ((new_references.unit_cd IS NULL) OR
127 (new_references.version_number IS NULL))) THEN
128 NULL;
129 ELSIF NOT igs_ps_unit_ver_pkg.get_pk_for_validation (
130 new_references.unit_cd,
131 new_references.version_number
132 ) THEN
133 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
134 igs_ge_msg_stack.add;
135 app_exception.raise_exception;
136 END IF;
137
138 END check_parent_existance;
139
140
141 FUNCTION get_pk_for_validation (
142 x_hesa_st_unt_vs_id IN NUMBER
143 ) RETURN BOOLEAN AS
144 /*
145 || Created By : [email protected]
146 || Created On : 29-JAN-2002
147 || Purpose : Validates the Primary Key of the table.
148 || Known limitations, enhancements or remarks :
149 || Change History :
150 || Who When What
151 || (reverse chronological order - newest change first)
152 */
153 CURSOR cur_rowid IS
154 SELECT rowid
155 FROM igs_he_st_unt_vs_all
156 WHERE hesa_st_unt_vs_id = x_hesa_st_unt_vs_id
157 FOR UPDATE NOWAIT;
158
159 lv_rowid cur_rowid%RowType;
160
161 BEGIN
162
163 OPEN cur_rowid;
164 FETCH cur_rowid INTO lv_rowid;
165 IF (cur_rowid%FOUND) THEN
166 CLOSE cur_rowid;
167 RETURN(TRUE);
168 ELSE
169 CLOSE cur_rowid;
170 RETURN(FALSE);
171 END IF;
172
173 END get_pk_for_validation;
174
175
176 FUNCTION get_uk_for_validation (
177 x_unit_cd IN VARCHAR2,
178 x_version_number IN NUMBER
179 ) RETURN BOOLEAN AS
180 /*
181 || Created By : [email protected]
182 || Created On : 29-JAN-2002
183 || Purpose : Validates the Unique Keys of the table.
184 || Known limitations, enhancements or remarks :
185 || Change History :
186 || Who When What
187 || (reverse chronological order - newest change first)
188 */
189 CURSOR cur_rowid IS
190 SELECT rowid
191 FROM igs_he_st_unt_vs_all
192 WHERE unit_cd = x_unit_cd
193 AND version_number = x_version_number
194 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
195
196 lv_rowid cur_rowid%RowType;
197
198 BEGIN
199
200 OPEN cur_rowid;
201 FETCH cur_rowid INTO lv_rowid;
202 IF (cur_rowid%FOUND) THEN
203 CLOSE cur_rowid;
204 RETURN (true);
205 ELSE
206 CLOSE cur_rowid;
207 RETURN(FALSE);
208 END IF;
209
210 END get_uk_for_validation ;
211
212
213 PROCEDURE get_fk_igs_ps_unit_ver_all (
214 x_unit_cd IN VARCHAR2,
215 x_version_number IN NUMBER
216 ) AS
217 /*
218 || Created By : [email protected]
219 || Created On : 29-JAN-2002
220 || Purpose : Validates the Foreign Keys for the table.
221 || Known limitations, enhancements or remarks :
222 || Change History :
223 || Who When What
224 || (reverse chronological order - newest change first)
225 */
226 CURSOR cur_rowid IS
227 SELECT rowid
228 FROM igs_he_st_unt_vs_all
229 WHERE ((unit_cd = x_unit_cd) AND
230 (version_number = x_version_number));
231
232 lv_rowid cur_rowid%RowType;
233
234 BEGIN
235
236 OPEN cur_rowid;
237 FETCH cur_rowid INTO lv_rowid;
238 IF (cur_rowid%FOUND) THEN
239 CLOSE cur_rowid;
240 fnd_message.set_name ('IGS', 'IGS_HE_HSUV_UV_FK');
241 igs_ge_msg_stack.add;
242 app_exception.raise_exception;
243 RETURN;
244 END IF;
245 CLOSE cur_rowid;
246
247 END get_fk_igs_ps_unit_ver_all;
248
249 PROCEDURE check_unit_attempt_exists AS
250 CURSOR cur_unit_attempt(cp_unit_cd igs_en_su_attempt.course_cd%TYPE,
251 cp_version igs_en_su_attempt.version_number%TYPE
252 ) IS
253 SELECT 'X' FROM igs_en_su_attempt
254 WHERE unit_cd = cp_unit_cd
255 AND version_number = cp_version;
256
257 l_unit_attempt VARCHAR2(1);
258
259 BEGIN
260 -- Check whether any SUA exists
261 OPEN cur_unit_attempt(new_references.unit_cd,
262 new_references.version_number);
263 FETCH cur_unit_attempt INTO l_unit_attempt;
264 IF cur_unit_attempt%FOUND THEN
265 CLOSE cur_unit_attempt;
266 fnd_message.set_name ('IGS', 'IGS_HE_CANT_DEL_SUA_EXIST');
267 igs_ge_msg_stack.add;
268 app_exception.raise_exception;
269 RETURN;
270 END IF;
271 CLOSE cur_unit_attempt;
272
273 END check_unit_attempt_exists;
274
275 PROCEDURE before_dml (
276 p_action IN VARCHAR2,
277 x_rowid IN VARCHAR2 ,
278 x_hesa_st_unt_vs_id IN NUMBER ,
279 x_org_id IN NUMBER ,
280 x_unit_cd IN VARCHAR2 ,
281 x_version_number IN NUMBER ,
282 x_prop_of_teaching_in_welsh IN NUMBER ,
283 x_credit_transfer_scheme IN VARCHAR2 ,
284 x_module_length IN NUMBER ,
285 x_proportion_of_fte IN NUMBER ,
286 x_location_cd IN VARCHAR2 ,
287 x_creation_date IN DATE ,
288 x_created_by IN NUMBER ,
289 x_last_update_date IN DATE ,
290 x_last_updated_by IN NUMBER ,
291 x_last_update_login IN NUMBER ,
292 x_exclude_flag IN VARCHAR2
293 ) AS
294 /*
295 || Created By : [email protected]
296 || Created On : 29-JAN-2002
297 || Purpose : Initialises the columns, Checks Constraints, Calls the
298 || Trigger Handlers for the table, before any DML operation.
299 || Known limitations, enhancements or remarks :
300 || Change History :
301 || Who When What
302 || sbaliga 8-Apr-2002 Added a parameter x_location_cd as aprt of #2278825
303 || (reverse chronological order - newest change first)
304 */
305 BEGIN
306
307 set_column_values (
308 p_action,
309 x_rowid,
310 x_hesa_st_unt_vs_id,
311 x_org_id,
312 x_unit_cd,
313 x_version_number,
314 x_prop_of_teaching_in_welsh,
315 x_credit_transfer_scheme,
316 x_module_length,
317 x_proportion_of_fte,
318 x_location_cd,
319 x_creation_date,
320 x_created_by,
321 x_last_update_date,
322 x_last_updated_by,
323 x_last_update_login,
324 x_exclude_flag
325 );
326
327 IF (p_action = 'INSERT') THEN
328 -- Call all the procedures related to Before Insert.
329 IF ( get_pk_for_validation(
330 new_references.hesa_st_unt_vs_id
331 )
332 ) THEN
333 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
334 igs_ge_msg_stack.add;
335 app_exception.raise_exception;
336 END IF;
337 check_uniqueness;
338 check_parent_existance;
339 ELSIF (p_action = 'UPDATE') THEN
340 -- Call all the procedures related to Before Update.
341 check_uniqueness;
342 check_parent_existance;
343 ELSIF (p_action = 'DELETE') THEN
344 check_unit_attempt_exists;
345 ELSIF (p_action = 'VALIDATE_INSERT') THEN
346 -- Call all the procedures related to Before Insert.
347 IF ( get_pk_for_validation (
348 new_references.hesa_st_unt_vs_id
349 )
350 ) THEN
351 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
352 igs_ge_msg_stack.add;
353 app_exception.raise_exception;
354 END IF;
355 check_uniqueness;
356 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
357 check_uniqueness;
358 ELSIF (p_action = 'VALIDATE_DELETE') THEN
359 check_unit_attempt_exists;
360 END IF;
361
362 END before_dml;
363
364
365 PROCEDURE insert_row (
366 x_rowid IN OUT NOCOPY VARCHAR2,
367 x_hesa_st_unt_vs_id IN OUT NOCOPY NUMBER,
368 x_org_id IN NUMBER,
369 x_unit_cd IN VARCHAR2,
370 x_version_number IN NUMBER,
371 x_prop_of_teaching_in_welsh IN NUMBER,
372 x_credit_transfer_scheme IN VARCHAR2,
373 x_module_length IN NUMBER,
374 x_proportion_of_fte IN NUMBER,
375 x_location_cd IN VARCHAR2,
376 x_mode IN VARCHAR2,
377 x_exclude_flag IN VARCHAR2
378 ) AS
379 /*
380 || Created By : [email protected]
381 || Created On : 29-JAN-2002
382 || Purpose : Handles the INSERT DML logic for the table.
383 || Known limitations, enhancements or remarks :
384 || Change History :
385 || Who When What
386 || sbaliga 8-Apr-2002 Added a parameter x_location_cd as part of #2278825
387 || smvk 13-Feb-2002 Call to igs_ge_gen_003.get_org_id
388 || w.r.t. SWCR006
389 || (reverse chronological order - newest change first)
390 */
391 CURSOR c IS
392 SELECT rowid
393 FROM igs_he_st_unt_vs_all
394 WHERE hesa_st_unt_vs_id = x_hesa_st_unt_vs_id;
395
396 x_last_update_date DATE;
397 x_last_updated_by NUMBER;
398 x_last_update_login NUMBER;
399
400 BEGIN
401
402 x_last_update_date := SYSDATE;
403 IF (x_mode = 'I') THEN
404 x_last_updated_by := 1;
405 x_last_update_login := 0;
406 ELSIF (x_mode = 'R') THEN
407 x_last_updated_by := fnd_global.user_id;
408 IF (x_last_updated_by IS NULL) THEN
409 x_last_updated_by := -1;
410 END IF;
411 x_last_update_login := fnd_global.login_id;
412 IF (x_last_update_login IS NULL) THEN
413 x_last_update_login := -1;
414 END IF;
415 ELSE
416 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
417 igs_ge_msg_stack.add;
418 app_exception.raise_exception;
419 END IF;
420
421 SELECT igs_he_st_unt_vs_all_s.NEXTVAL
422 INTO x_hesa_st_unt_vs_id
423 FROM dual;
424
425 before_dml(
426 p_action => 'INSERT',
427 x_rowid => x_rowid,
428 x_hesa_st_unt_vs_id => x_hesa_st_unt_vs_id,
429 x_org_id => igs_ge_gen_003.get_org_id,
430 x_unit_cd => x_unit_cd,
431 x_version_number => x_version_number,
432 x_prop_of_teaching_in_welsh => x_prop_of_teaching_in_welsh,
433 x_credit_transfer_scheme => x_credit_transfer_scheme,
434 x_module_length => x_module_length,
435 x_proportion_of_fte => x_proportion_of_fte,
436 x_location_cd => x_location_cd,
437 x_creation_date => x_last_update_date,
438 x_created_by => x_last_updated_by,
439 x_last_update_date => x_last_update_date,
440 x_last_updated_by => x_last_updated_by,
441 x_last_update_login => x_last_update_login,
442 x_exclude_flag => x_exclude_flag
443 );
444
445 INSERT INTO igs_he_st_unt_vs_all (
446 hesa_st_unt_vs_id,
447 org_id,
448 unit_cd,
449 version_number,
450 prop_of_teaching_in_welsh,
451 credit_transfer_scheme,
452 module_length,
453 proportion_of_fte,
454 location_cd,
455 creation_date,
456 created_by,
457 last_update_date,
458 last_updated_by,
459 last_update_login,
460 exclude_flag
461 ) VALUES (
462 new_references.hesa_st_unt_vs_id,
463 new_references.org_id,
464 new_references.unit_cd,
465 new_references.version_number,
466 new_references.prop_of_teaching_in_welsh,
467 new_references.credit_transfer_scheme,
468 new_references.module_length,
469 new_references.proportion_of_fte,
470 new_references.location_cd,
471 x_last_update_date,
472 x_last_updated_by,
473 x_last_update_date,
474 x_last_updated_by,
475 x_last_update_login,
476 new_references.exclude_flag
477 );
478
479 OPEN c;
480 FETCH c INTO x_rowid;
481 IF (c%NOTFOUND) THEN
482 CLOSE c;
483 RAISE NO_DATA_FOUND;
484 END IF;
485 CLOSE c;
486
487 END insert_row;
488
489
490 PROCEDURE lock_row (
491 x_rowid IN VARCHAR2,
492 x_hesa_st_unt_vs_id IN NUMBER,
493 x_org_id IN NUMBER,
494 x_unit_cd IN VARCHAR2,
495 x_version_number IN NUMBER,
496 x_prop_of_teaching_in_welsh IN NUMBER,
497 x_credit_transfer_scheme IN VARCHAR2,
498 x_module_length IN NUMBER,
499 x_proportion_of_fte IN NUMBER,
500 x_location_cd IN VARCHAR2,
501 x_exclude_flag IN VARCHAR2
502 ) AS
503 /*
504 || Created By : [email protected]
505 || Created On : 29-JAN-2002
506 || Purpose : Handles the LOCK mechanism for the table.
507 || Known limitations, enhancements or remarks :
508 || Change History :
509 || Who When What
510 || sbaliga 8-Apr-2002 Added a parameter x_location_cd as part of #2278825
511 || smvk 13-Feb-2002 Removed org_id from cursor declaration
512 || and conditional checking w.r.t.SWCR006
513 || (reverse chronological order - newest change first)
514 */
515 CURSOR c1 IS
516 SELECT
517 unit_cd,
518 version_number,
519 prop_of_teaching_in_welsh,
520 credit_transfer_scheme,
521 module_length,
522 proportion_of_fte,
523 location_cd,
524 exclude_flag
525 FROM igs_he_st_unt_vs_all
526 WHERE rowid = x_rowid
527 FOR UPDATE NOWAIT;
528
529 tlinfo c1%ROWTYPE;
530
531 BEGIN
532
533 OPEN c1;
534 FETCH c1 INTO tlinfo;
535 IF (c1%notfound) THEN
536 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
537 igs_ge_msg_stack.add;
538 CLOSE c1;
539 app_exception.raise_exception;
540 RETURN;
541 END IF;
542 CLOSE c1;
543
544 IF (
545 (tlinfo.unit_cd = x_unit_cd)
546 AND (tlinfo.version_number = x_version_number)
547 AND ((tlinfo.prop_of_teaching_in_welsh = x_prop_of_teaching_in_welsh) OR ((tlinfo.prop_of_teaching_in_welsh IS NULL) AND (X_prop_of_teaching_in_welsh IS NULL)))
548 AND ((tlinfo.credit_transfer_scheme = x_credit_transfer_scheme) OR ((tlinfo.credit_transfer_scheme IS NULL) AND (X_credit_transfer_scheme IS NULL)))
549 AND ((tlinfo.module_length = x_module_length) OR ((tlinfo.module_length IS NULL) AND (X_module_length IS NULL)))
550 AND ((tlinfo.proportion_of_fte = x_proportion_of_fte) OR ((tlinfo.proportion_of_fte IS NULL) AND (X_proportion_of_fte IS NULL)))
551 AND ((tlinfo.location_cd = x_location_cd) OR ((tlinfo.location_cd IS NULL) AND (X_location_cd IS NULL)))
552 AND ((tlinfo.exclude_flag = x_exclude_flag) OR ((tlinfo.exclude_flag IS NULL) AND (X_exclude_flag IS NULL)))
553 ) THEN
554 NULL;
555 ELSE
556 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
557 igs_ge_msg_stack.add;
558 app_exception.raise_exception;
559 END IF;
560
561 RETURN;
562
563 END lock_row;
564
565
566 PROCEDURE update_row (
567 x_rowid IN VARCHAR2,
568 x_hesa_st_unt_vs_id IN NUMBER,
569 x_org_id IN NUMBER,
570 x_unit_cd IN VARCHAR2,
571 x_version_number IN NUMBER,
572 x_prop_of_teaching_in_welsh IN NUMBER,
573 x_credit_transfer_scheme IN VARCHAR2,
574 x_module_length IN NUMBER,
575 x_proportion_of_fte IN NUMBER,
576 x_location_cd IN VARCHAR2,
577 x_mode IN VARCHAR2,
578 x_exclude_flag IN VARCHAR2
579 ) AS
580 /*
581 || Created By : [email protected]
582 || Created On : 29-JAN-2002
583 || Purpose : Handles the UPDATE DML logic for the table.
584 || Known limitations, enhancements or remarks :
585 || Change History :
586 || Who When What
587 || sbaliga 8-Apr-2002 Added parameter x_location_cd as part of #2278825
588 || smvk 13-Feb-2002 Call to igs_ge_gen_003.get_org_id
589 || w.r.t SWCR006
590 || (reverse chronological order - newest change first)
591 */
592 x_last_update_date DATE ;
593 x_last_updated_by NUMBER;
594 x_last_update_login NUMBER;
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 igs_ge_msg_stack.add;
614 app_exception.raise_exception;
615 END IF;
616
617 before_dml(
618 p_action => 'UPDATE',
619 x_rowid => x_rowid,
620 x_hesa_st_unt_vs_id => x_hesa_st_unt_vs_id,
621 x_org_id => igs_ge_gen_003.get_org_id,
622 x_unit_cd => x_unit_cd,
623 x_version_number => x_version_number,
624 x_prop_of_teaching_in_welsh => x_prop_of_teaching_in_welsh,
625 x_credit_transfer_scheme => x_credit_transfer_scheme,
626 x_module_length => x_module_length,
627 x_proportion_of_fte => x_proportion_of_fte,
628 x_location_cd => x_location_cd,
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 x_exclude_flag => x_exclude_flag
635 );
636
637 UPDATE igs_he_st_unt_vs_all
638 SET
639 unit_cd = new_references.unit_cd,
640 version_number = new_references.version_number,
641 prop_of_teaching_in_welsh = new_references.prop_of_teaching_in_welsh,
642 credit_transfer_scheme = new_references.credit_transfer_scheme,
643 module_length = new_references.module_length,
644 proportion_of_fte = new_references.proportion_of_fte,
645 location_cd = new_references.location_cd,
646 last_update_date = x_last_update_date,
647 last_updated_by = x_last_updated_by,
648 last_update_login = x_last_update_login,
649 exclude_flag = new_references.exclude_flag
650 WHERE rowid = x_rowid;
651
652 IF (SQL%NOTFOUND) THEN
653 RAISE NO_DATA_FOUND;
654 END IF;
655
656 END update_row;
657
658
659 PROCEDURE add_row (
660 x_rowid IN OUT NOCOPY VARCHAR2,
661 x_hesa_st_unt_vs_id IN OUT NOCOPY NUMBER,
662 x_org_id IN NUMBER,
663 x_unit_cd IN VARCHAR2,
664 x_version_number IN NUMBER,
665 x_prop_of_teaching_in_welsh IN NUMBER,
666 x_credit_transfer_scheme IN VARCHAR2,
667 x_module_length IN NUMBER,
668 x_proportion_of_fte IN NUMBER,
669 x_location_cd IN VARCHAR2,
670 x_mode IN VARCHAR2,
671 x_exclude_flag IN VARCHAR2
672 ) AS
673 /*
674 || Created By : [email protected]
675 || Created On : 29-JAN-2002
676 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
677 || Known limitations, enhancements or remarks :
678 || Change History :
679 || Who When What
680 ||sbaliga 8-Apr-2002 Added a parameter x_location_cd as part of #2278825
681 || (reverse chronological order - newest change first)
682 */
683 CURSOR c1 IS
684 SELECT rowid
685 FROM igs_he_st_unt_vs_all
686 WHERE hesa_st_unt_vs_id = x_hesa_st_unt_vs_id;
687
688 BEGIN
689
690 OPEN c1;
691 FETCH c1 INTO x_rowid;
692 IF (c1%NOTFOUND) THEN
693 CLOSE c1;
694
695 insert_row (
696 x_rowid,
697 x_hesa_st_unt_vs_id,
698 x_org_id,
699 x_unit_cd,
700 x_version_number,
701 x_prop_of_teaching_in_welsh,
702 x_credit_transfer_scheme,
703 x_module_length,
704 x_proportion_of_fte,
705 x_location_cd,
706 x_mode,
707 x_exclude_flag
708 );
709 RETURN;
710 END IF;
711 CLOSE c1;
712
713 update_row (
714 x_rowid,
715 x_hesa_st_unt_vs_id,
716 x_org_id,
717 x_unit_cd,
718 x_version_number,
719 x_prop_of_teaching_in_welsh,
720 x_credit_transfer_scheme,
721 x_module_length,
722 x_proportion_of_fte,
723 x_location_cd,
724 x_mode,
725 x_exclude_flag
726 );
727
728 END add_row;
729
730
731 PROCEDURE delete_row (
732 x_rowid IN VARCHAR2
733 ) AS
734 /*
735 || Created By : [email protected]
736 || Created On : 29-JAN-2002
737 || Purpose : Handles the DELETE DML logic for the table.
738 || Known limitations, enhancements or remarks :
739 || Change History :
740 || Who When What
741 || (reverse chronological order - newest change first)
742 */
743 BEGIN
744
745 before_dml (
746 p_action => 'DELETE',
747 x_rowid => x_rowid
748 );
749
750 DELETE FROM igs_he_st_unt_vs_all
751 WHERE rowid = x_rowid;
752
753 IF (SQL%NOTFOUND) THEN
754 RAISE NO_DATA_FOUND;
755 END IF;
756
757 END delete_row;
758
759
760 END igs_he_st_unt_vs_all_pkg;
[Home] [Help]