1 PACKAGE BODY igs_as_gpc_programs_pkg AS
2 /* $Header: IGSDI53B.pls 115.6 2002/11/28 23:24:01 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_as_gpc_programs%ROWTYPE;
6 new_references igs_as_gpc_programs%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_gpc_program_id IN NUMBER DEFAULT NULL,
12 x_grading_period_cd IN VARCHAR2 DEFAULT NULL,
13 x_course_cd IN VARCHAR2 DEFAULT NULL,
14 x_course_version_number IN NUMBER DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21 /*
22 || Created By : cdcruz
23 || Created On : 25-JUL-2001
24 || Purpose : Initialises the Old and New references for the columns of the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_AS_GPC_PROGRAMS
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 OPEN cur_old_ref_values;
43 FETCH cur_old_ref_values INTO old_references;
44 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45 CLOSE cur_old_ref_values;
46 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47 igs_ge_msg_stack.add;
48 app_exception.raise_exception;
49 RETURN;
50 END IF;
51 CLOSE cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.gpc_program_id := x_gpc_program_id;
55 new_references.grading_period_cd := x_grading_period_cd;
56 new_references.course_cd := x_course_cd;
57 new_references.course_version_number := x_course_version_number;
58
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END set_column_values;
72
73
74 PROCEDURE check_uniqueness AS
75 /*
76 || Created By : cdcruz
77 || Created On : 25-JUL-2001
78 || Purpose : Handles the Unique Constraint logic defined for the columns.
79 || Known limitations, enhancements or remarks :
80 || Change History :
81 || Who When What
82 || (reverse chronological order - newest change first)
83 */
84 BEGIN
85
86 IF ( get_uk_for_validation (
87 new_references.grading_period_cd,
88 new_references.course_cd,
89 new_references.course_version_number
90 )
91 ) THEN
92 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
93 igs_ge_msg_stack.add;
94 app_exception.raise_exception;
95 END IF;
96
97 END check_uniqueness;
98
99
100 PROCEDURE check_parent_existance AS
101 /*
102 || Created By : cdcruz
103 || Created On : 25-JUL-2001
104 || Purpose : Checks for the existance of Parent records.
105 || Known limitations, enhancements or remarks :
106 || Change History :
107 || Who When What
108 || (reverse chronological order - newest change first)
109 */
110 BEGIN
111
112 IF (((old_references.course_cd = new_references.course_cd) AND
113 (old_references.course_version_number = new_references.course_version_number)) OR
114 ((new_references.course_cd IS NULL) OR
115 (new_references.course_version_number IS NULL))) THEN
116 NULL;
117 ELSIF NOT igs_ps_ver_pkg.get_pk_for_validation (
118 new_references.course_cd,
119 new_references.course_version_number
120 ) THEN
121 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
122 igs_ge_msg_stack.add;
123 app_exception.raise_exception;
124
125
126 ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation ('AS_GRADING_PERIOD',
127 new_references.grading_period_cd ) THEN
128
129 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
130 IGS_GE_MSG_STACK.ADD ;
131 APP_EXCEPTION.RAISE_EXCEPTION ;
132
133
134 END IF;
135
136 END check_parent_existance;
137
138
139 FUNCTION get_pk_for_validation (
140 x_gpc_program_id IN NUMBER
141 ) RETURN BOOLEAN AS
142 /*
143 || Created By : cdcruz
144 || Created On : 25-JUL-2001
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_as_gpc_programs
154 WHERE gpc_program_id = x_gpc_program_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 FUNCTION get_uk_for_validation (
175 x_grading_period_cd IN VARCHAR2,
176 x_course_cd IN VARCHAR2,
177 x_course_version_number IN NUMBER
178 ) RETURN BOOLEAN AS
179 /*
180 || Created By : cdcruz
181 || Created On : 25-JUL-2001
182 || Purpose : Validates the Unique Keys of the table.
183 || Known limitations, enhancements or remarks :
184 || Change History :
185 || Who When What
186 || (reverse chronological order - newest change first)
187 */
188 CURSOR cur_rowid IS
189 SELECT rowid
190 FROM igs_as_gpc_programs
191 WHERE grading_period_cd = x_grading_period_cd
192 AND course_cd = x_course_cd
193 AND course_version_number = x_course_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_ver (
214 x_course_cd IN VARCHAR2,
215 x_version_number IN NUMBER
216 ) AS
217 /*
218 || Created By : cdcruz
219 || Created On : 25-JUL-2001
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_as_gpc_programs
229 WHERE ((course_cd = x_course_cd) AND
230 (course_version_number = x_version_number));
231
232 lv_rowid cur_rowid%RowType;
233
234 BEGIN
235
236
237 OPEN cur_rowid;
238 FETCH cur_rowid INTO lv_rowid;
239 IF cur_rowid%FOUND THEN
240 CLOSE cur_rowid;
241 fnd_message.set_name ('IGS', 'IGS_AS_GRP_CVR_FK');
242 igs_ge_msg_stack.add;
243 app_exception.raise_exception;
244 RETURN;
245 END IF;
246 CLOSE cur_rowid;
247
248 END get_fk_igs_ps_ver;
249
250 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
251 x_grading_period_cd IN VARCHAR2
252 ) IS
253
254 CURSOR cur_rowid IS
255 SELECT rowid
256 FROM
257 igs_as_gpc_programs
258 WHERE
259 grading_period_cd = x_grading_period_cd ;
260
261 lv_rowid cur_rowid%RowType ;
262
263 BEGIN
264
265 Open cur_rowid ;
266 FETCH cur_rowid INTO lv_rowid ;
267 IF cur_rowid%FOUND THEN
268
269
270 Fnd_Message.Set_Name ('IGS','IGS_AS_GRP_LVAL_FK');
271 IGS_GE_MSG_STACK.ADD ;
272
273 CLOSE cur_rowid ;
274
275 APP_EXCEPTION.RAISE_EXCEPTION ;
276
277 END IF;
278
279 CLOSE cur_rowid ;
280
281 END GET_FK_IGS_LOOKUPS_VIEW ;
282
283 PROCEDURE before_dml (
284 p_action IN VARCHAR2,
285 x_rowid IN VARCHAR2 DEFAULT NULL,
286 x_gpc_program_id IN NUMBER DEFAULT NULL,
287 x_grading_period_cd IN VARCHAR2 DEFAULT NULL,
288 x_course_cd IN VARCHAR2 DEFAULT NULL,
289 x_course_version_number IN NUMBER DEFAULT NULL,
290 x_creation_date IN DATE DEFAULT NULL,
291 x_created_by IN NUMBER DEFAULT NULL,
292 x_last_update_date IN DATE DEFAULT NULL,
293 x_last_updated_by IN NUMBER DEFAULT NULL,
294 x_last_update_login IN NUMBER DEFAULT NULL
295 ) AS
296 /*
297 || Created By : cdcruz
298 || Created On : 25-JUL-2001
299 || Purpose : Initialises the columns, Checks Constraints, Calls the
300 || Trigger Handlers for the table, before any DML operation.
301 || Known limitations, enhancements or remarks :
302 || Change History :
303 || Who When What
304 || (reverse chronological order - newest change first)
305 */
306 BEGIN
307
308 set_column_values (
309 p_action,
310 x_rowid,
311 x_gpc_program_id,
312 x_grading_period_cd,
313 x_course_cd,
314 x_course_version_number,
315 x_creation_date,
316 x_created_by,
317 x_last_update_date,
318 x_last_updated_by,
319 x_last_update_login
320 );
321
322 IF (p_action = 'INSERT') THEN
323 -- Call all the procedures related to Before Insert.
324 IF ( get_pk_for_validation(
325 new_references.gpc_program_id
326 )
327 ) THEN
328 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
329 igs_ge_msg_stack.add;
330 app_exception.raise_exception;
331 END IF;
332 check_uniqueness;
333 check_parent_existance;
334 ELSIF (p_action = 'UPDATE') THEN
335 -- Call all the procedures related to Before Update.
336 check_uniqueness;
337 check_parent_existance;
338 ELSIF (p_action = 'VALIDATE_INSERT') THEN
339 -- Call all the procedures related to Before Insert.
340 IF ( get_pk_for_validation (
341 new_references.gpc_program_id
342 )
343 ) THEN
344 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
345 igs_ge_msg_stack.add;
346 app_exception.raise_exception;
347 END IF;
348 check_uniqueness;
349 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
350 check_uniqueness;
351 END IF;
352
353 END before_dml;
354
355
356 PROCEDURE insert_row (
357 x_rowid IN OUT NOCOPY VARCHAR2,
358 x_gpc_program_id IN OUT NOCOPY NUMBER,
359 x_grading_period_cd IN VARCHAR2,
360 x_course_cd IN VARCHAR2,
361 x_course_version_number IN NUMBER,
362 x_mode IN VARCHAR2 DEFAULT 'R'
363 ) AS
364 /*
365 || Created By : cdcruz
366 || Created On : 25-JUL-2001
367 || Purpose : Handles the INSERT DML logic for the table.
368 || Known limitations, enhancements or remarks :
369 || Change History :
370 || Who When What
371 || (reverse chronological order - newest change first)
372 */
373 CURSOR c IS
374 SELECT rowid
375 FROM igs_as_gpc_programs
376 WHERE gpc_program_id = x_gpc_program_id;
377
378 x_last_update_date DATE;
379 x_last_updated_by NUMBER;
380 x_last_update_login NUMBER;
381
382 BEGIN
383
384 x_last_update_date := SYSDATE;
385 IF (x_mode = 'I') THEN
386 x_last_updated_by := 1;
387 x_last_update_login := 0;
388 ELSIF (x_mode = 'R') THEN
389 x_last_updated_by := fnd_global.user_id;
390 IF (x_last_updated_by IS NULL) THEN
391 x_last_updated_by := -1;
392 END IF;
393 x_last_update_login := fnd_global.login_id;
394 IF (x_last_update_login IS NULL) THEN
395 x_last_update_login := -1;
396 END IF;
397 ELSE
398 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
399 igs_ge_msg_stack.add;
400 app_exception.raise_exception;
401 END IF;
402
403 SELECT igs_as_gpc_programs_s.NEXTVAL
404 INTO x_gpc_program_id
405 FROM dual;
406
407 before_dml(
408 p_action => 'INSERT',
409 x_rowid => x_rowid,
410 x_gpc_program_id => x_gpc_program_id,
411 x_grading_period_cd => x_grading_period_cd,
412 x_course_cd => x_course_cd,
413 x_course_version_number => x_course_version_number,
414 x_creation_date => x_last_update_date,
415 x_created_by => x_last_updated_by,
416 x_last_update_date => x_last_update_date,
417 x_last_updated_by => x_last_updated_by,
418 x_last_update_login => x_last_update_login
419 );
420
421 INSERT INTO igs_as_gpc_programs (
422 gpc_program_id,
423 grading_period_cd,
424 course_cd,
425 course_version_number,
426 creation_date,
427 created_by,
428 last_update_date,
429 last_updated_by,
430 last_update_login
431 ) VALUES (
432 new_references.gpc_program_id,
433 new_references.grading_period_cd,
434 new_references.course_cd,
435 new_references.course_version_number,
436 x_last_update_date,
437 x_last_updated_by,
438 x_last_update_date,
439 x_last_updated_by,
440 x_last_update_login
441 );
442
443 OPEN c;
444 FETCH c INTO x_rowid;
445 IF (c%NOTFOUND) THEN
446 CLOSE c;
447 RAISE NO_DATA_FOUND;
448 END IF;
449 CLOSE c;
450
451 END insert_row;
452
453
454 PROCEDURE lock_row (
455 x_rowid IN VARCHAR2,
456 x_gpc_program_id IN NUMBER,
457 x_grading_period_cd IN VARCHAR2,
458 x_course_cd IN VARCHAR2,
459 x_course_version_number IN NUMBER
460 ) AS
461 /*
462 || Created By : cdcruz
463 || Created On : 25-JUL-2001
464 || Purpose : Handles the LOCK mechanism for the table.
465 || Known limitations, enhancements or remarks :
466 || Change History :
467 || Who When What
468 || (reverse chronological order - newest change first)
469 */
470 CURSOR c1 IS
471 SELECT
472 grading_period_cd,
473 course_cd,
474 course_version_number
475 FROM igs_as_gpc_programs
476 WHERE rowid = x_rowid
477 FOR UPDATE NOWAIT;
478
479 tlinfo c1%ROWTYPE;
480
481 BEGIN
482
483 OPEN c1;
484 FETCH c1 INTO tlinfo;
485 IF (c1%notfound) THEN
486 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
487 igs_ge_msg_stack.add;
488 CLOSE c1;
489 app_exception.raise_exception;
490 RETURN;
491 END IF;
492 CLOSE c1;
493
494 IF (
495 (tlinfo.grading_period_cd = x_grading_period_cd)
496 AND (tlinfo.course_cd = x_course_cd)
497 AND (tlinfo.course_version_number = x_course_version_number)
498 ) THEN
499 NULL;
500 ELSE
501 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
502 igs_ge_msg_stack.add;
503 app_exception.raise_exception;
504 END IF;
505
506 RETURN;
507
508 END lock_row;
509
510
511 PROCEDURE update_row (
512 x_rowid IN VARCHAR2,
513 x_gpc_program_id IN NUMBER,
514 x_grading_period_cd IN VARCHAR2,
515 x_course_cd IN VARCHAR2,
516 x_course_version_number IN NUMBER,
517 x_mode IN VARCHAR2 DEFAULT 'R'
518 ) AS
519 /*
520 || Created By : cdcruz
521 || Created On : 25-JUL-2001
522 || Purpose : Handles the UPDATE DML logic for the table.
523 || Known limitations, enhancements or remarks :
524 || Change History :
525 || Who When What
526 || (reverse chronological order - newest change first)
527 */
528 x_last_update_date DATE ;
529 x_last_updated_by NUMBER;
530 x_last_update_login NUMBER;
531
532 BEGIN
533
534 x_last_update_date := SYSDATE;
535 IF (X_MODE = 'I') THEN
536 x_last_updated_by := 1;
537 x_last_update_login := 0;
538 ELSIF (x_mode = 'R') THEN
539 x_last_updated_by := fnd_global.user_id;
540 IF x_last_updated_by IS NULL THEN
541 x_last_updated_by := -1;
542 END IF;
543 x_last_update_login := fnd_global.login_id;
544 IF (x_last_update_login IS NULL) THEN
545 x_last_update_login := -1;
546 END IF;
547 ELSE
548 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
549 igs_ge_msg_stack.add;
550 app_exception.raise_exception;
551 END IF;
552
553 before_dml(
554 p_action => 'UPDATE',
555 x_rowid => x_rowid,
556 x_gpc_program_id => x_gpc_program_id,
557 x_grading_period_cd => x_grading_period_cd,
558 x_course_cd => x_course_cd,
559 x_course_version_number => x_course_version_number,
560 x_creation_date => x_last_update_date,
561 x_created_by => x_last_updated_by,
562 x_last_update_date => x_last_update_date,
563 x_last_updated_by => x_last_updated_by,
564 x_last_update_login => x_last_update_login
565 );
566
567 UPDATE igs_as_gpc_programs
568 SET
569 grading_period_cd = new_references.grading_period_cd,
570 course_cd = new_references.course_cd,
571 course_version_number = new_references.course_version_number,
572 last_update_date = x_last_update_date,
573 last_updated_by = x_last_updated_by,
574 last_update_login = x_last_update_login
575 WHERE rowid = x_rowid;
576
577 IF (SQL%NOTFOUND) THEN
578 RAISE NO_DATA_FOUND;
579 END IF;
580
581 END update_row;
582
583
584 PROCEDURE add_row (
585 x_rowid IN OUT NOCOPY VARCHAR2,
586 x_gpc_program_id IN OUT NOCOPY NUMBER,
587 x_grading_period_cd IN VARCHAR2,
588 x_course_cd IN VARCHAR2,
589 x_course_version_number IN NUMBER,
590 x_mode IN VARCHAR2 DEFAULT 'R'
591 ) AS
592 /*
593 || Created By : cdcruz
594 || Created On : 25-JUL-2001
595 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
596 || Known limitations, enhancements or remarks :
597 || Change History :
598 || Who When What
599 || (reverse chronological order - newest change first)
600 */
601 CURSOR c1 IS
602 SELECT rowid
603 FROM igs_as_gpc_programs
604 WHERE gpc_program_id = x_gpc_program_id;
605
606 BEGIN
607
608 OPEN c1;
609 FETCH c1 INTO x_rowid;
610 IF (c1%NOTFOUND) THEN
611 CLOSE c1;
612
613 insert_row (
614 x_rowid,
615 x_gpc_program_id,
616 x_grading_period_cd,
617 x_course_cd,
618 x_course_version_number,
619 x_mode
620 );
621 RETURN;
622 END IF;
623 CLOSE c1;
624
625 update_row (
626 x_rowid,
627 x_gpc_program_id,
628 x_grading_period_cd,
629 x_course_cd,
630 x_course_version_number,
631 x_mode
632 );
633
634 END add_row;
635
636
637 PROCEDURE delete_row (
638 x_rowid IN VARCHAR2
639 ) AS
640 /*
641 || Created By : cdcruz
642 || Created On : 25-JUL-2001
643 || Purpose : Handles the DELETE DML logic for the table.
644 || Known limitations, enhancements or remarks :
645 || Change History :
646 || Who When What
647 || (reverse chronological order - newest change first)
648 */
649 BEGIN
650
651 before_dml (
652 p_action => 'DELETE',
653 x_rowid => x_rowid
654 );
655
656 DELETE FROM igs_as_gpc_programs
657 WHERE rowid = x_rowid;
658
659 IF (SQL%NOTFOUND) THEN
660 RAISE NO_DATA_FOUND;
661 END IF;
662
663 END delete_row;
664
665
666 END igs_as_gpc_programs_pkg;
[Home] [Help]