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