1 PACKAGE BODY igs_as_gpc_aca_stndg_pkg AS
2 /* $Header: IGSDI52B.pls 115.5 2002/11/28 23:23:44 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_as_gpc_aca_stndg%ROWTYPE;
6 new_references igs_as_gpc_aca_stndg%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_gpc_aca_stndg_id IN NUMBER DEFAULT NULL,
12 x_grading_period_cd IN VARCHAR2 DEFAULT NULL,
13 x_progression_status 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 : cdcruz
22 || Created On : 25-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_ACA_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_aca_stndg_id := x_gpc_aca_stndg_id;
54 new_references.grading_period_cd := x_grading_period_cd;
55 new_references.progression_status := x_progression_status;
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 : cdcruz
75 || Created On : 25-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.progression_status
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 FUNCTION get_pk_for_validation (
98 x_gpc_aca_stndg_id IN NUMBER
99 ) RETURN BOOLEAN AS
100 /*
101 || Created By : cdcruz
102 || Created On : 25-JUL-2001
103 || Purpose : Validates the Primary Key of the table.
104 || Known limitations, enhancements or remarks :
105 || Change History :
106 || Who When What
107 || (reverse chronological order - newest change first)
108 */
109 CURSOR cur_rowid IS
110 SELECT rowid
111 FROM igs_as_gpc_aca_stndg
112 WHERE gpc_aca_stndg_id = x_gpc_aca_stndg_id
113 FOR UPDATE NOWAIT;
114
115 lv_rowid cur_rowid%RowType;
116
117 BEGIN
118
119 OPEN cur_rowid;
120 FETCH cur_rowid INTO lv_rowid;
121 IF (cur_rowid%FOUND) THEN
122 CLOSE cur_rowid;
123 RETURN(TRUE);
124 ELSE
125 CLOSE cur_rowid;
126 RETURN(FALSE);
127 END IF;
128
129 END get_pk_for_validation;
130
131
132 FUNCTION get_uk_for_validation (
133 x_grading_period_cd IN VARCHAR2,
134 x_progression_status IN VARCHAR2
135 ) RETURN BOOLEAN AS
136 /*
137 || Created By : cdcruz
138 || Created On : 25-JUL-2001
139 || Purpose : Validates the Unique Keys of the table.
140 || Known limitations, enhancements or remarks :
141 || Change History :
142 || Who When What
143 || (reverse chronological order - newest change first)
144 */
145 CURSOR cur_rowid IS
146 SELECT rowid
147 FROM igs_as_gpc_aca_stndg
148 WHERE grading_period_cd = x_grading_period_cd
149 AND progression_status = x_progression_status
150 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
151
152 lv_rowid cur_rowid%RowType;
153
154 BEGIN
155
156 OPEN cur_rowid;
157 FETCH cur_rowid INTO lv_rowid;
158 IF (cur_rowid%FOUND) THEN
159 CLOSE cur_rowid;
160 RETURN (true);
161 ELSE
162 CLOSE cur_rowid;
163 RETURN(FALSE);
164 END IF;
165
166 END get_uk_for_validation ;
167
168 PROCEDURE Check_Parent_Existance AS
169 BEGIN
170
171 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation ('AS_GRADING_PERIOD',
172 new_references.grading_period_cd ) THEN
173
174 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
175 IGS_GE_MSG_STACK.ADD ;
176 APP_EXCEPTION.RAISE_EXCEPTION ;
177
178 END IF;
179
180 END Check_Parent_Existance ;
181
182
183 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
184 x_grading_period_cd IN VARCHAR2
185 ) IS
186
187 CURSOR cur_rowid IS
188 SELECT rowid
189 FROM
190 igs_as_gpc_aca_stndg
191 WHERE
192 grading_period_cd = x_grading_period_cd ;
193
194 lv_rowid cur_rowid%RowType ;
195
196 BEGIN
197
198 Open cur_rowid ;
199 FETCH cur_rowid INTO lv_rowid ;
200 IF cur_rowid%FOUND THEN
201
202
203 Fnd_Message.Set_Name ('IGS','IGS_AS_GSD_LVAL_FK');
204 IGS_GE_MSG_STACK.ADD ;
205
206 CLOSE cur_rowid ;
207
208 APP_EXCEPTION.RAISE_EXCEPTION ;
209
210 END IF;
211
212 CLOSE cur_rowid ;
213
214 END GET_FK_IGS_LOOKUPS_VIEW ;
215
216
217 PROCEDURE before_dml (
218 p_action IN VARCHAR2,
219 x_rowid IN VARCHAR2 DEFAULT NULL,
220 x_gpc_aca_stndg_id IN NUMBER DEFAULT NULL,
221 x_grading_period_cd IN VARCHAR2 DEFAULT NULL,
222 x_progression_status IN VARCHAR2 DEFAULT NULL,
223 x_creation_date IN DATE DEFAULT NULL,
224 x_created_by IN NUMBER DEFAULT NULL,
225 x_last_update_date IN DATE DEFAULT NULL,
226 x_last_updated_by IN NUMBER DEFAULT NULL,
227 x_last_update_login IN NUMBER DEFAULT NULL
228 ) AS
229 /*
230 || Created By : cdcruz
231 || Created On : 25-JUL-2001
232 || Purpose : Initialises the columns, Checks Constraints, Calls the
233 || Trigger Handlers for the table, before any DML operation.
234 || Known limitations, enhancements or remarks :
235 || Change History :
236 || Who When What
237 || (reverse chronological order - newest change first)
238 */
239 BEGIN
240
241 set_column_values (
242 p_action,
243 x_rowid,
244 x_gpc_aca_stndg_id,
245 x_grading_period_cd,
246 x_progression_status,
247 x_creation_date,
248 x_created_by,
249 x_last_update_date,
250 x_last_updated_by,
251 x_last_update_login
252 );
253
254 IF (p_action = 'INSERT') THEN
255 -- Call all the procedures related to Before Insert.
256 IF ( get_pk_for_validation(
257 new_references.gpc_aca_stndg_id
258 )
259 ) THEN
260 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
261 igs_ge_msg_stack.add;
262 app_exception.raise_exception;
263 END IF;
264 check_uniqueness;
265
266 Check_Parent_Existance ;
267
268 ELSIF (p_action = 'UPDATE') THEN
269 -- Call all the procedures related to Before Update.
270 check_uniqueness;
274 new_references.gpc_aca_stndg_id
271 ELSIF (p_action = 'VALIDATE_INSERT') THEN
272 -- Call all the procedures related to Before Insert.
273 IF ( get_pk_for_validation (
275 )
276 ) THEN
277 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
278 igs_ge_msg_stack.add;
279 app_exception.raise_exception;
280 END IF;
281 check_uniqueness;
282 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
283 check_uniqueness;
284 Check_Parent_Existance ;
285 END IF;
286
287 END before_dml;
288
289
290 PROCEDURE insert_row (
291 x_rowid IN OUT NOCOPY VARCHAR2,
292 x_gpc_aca_stndg_id IN OUT NOCOPY NUMBER,
293 x_grading_period_cd IN VARCHAR2,
294 x_progression_status IN VARCHAR2,
295 x_mode IN VARCHAR2 DEFAULT 'R'
296 ) AS
297 /*
298 || Created By : cdcruz
299 || Created On : 25-JUL-2001
300 || Purpose : Handles the INSERT DML logic for the table.
301 || Known limitations, enhancements or remarks :
302 || Change History :
303 || Who When What
304 || (reverse chronological order - newest change first)
305 */
306 CURSOR c IS
307 SELECT rowid
308 FROM igs_as_gpc_aca_stndg
309 WHERE gpc_aca_stndg_id = x_gpc_aca_stndg_id;
310
311 x_last_update_date DATE;
312 x_last_updated_by NUMBER;
313 x_last_update_login NUMBER;
314
315 BEGIN
316
317 x_last_update_date := SYSDATE;
318 IF (x_mode = 'I') THEN
319 x_last_updated_by := 1;
320 x_last_update_login := 0;
321 ELSIF (x_mode = 'R') THEN
322 x_last_updated_by := fnd_global.user_id;
323 IF (x_last_updated_by IS NULL) THEN
324 x_last_updated_by := -1;
325 END IF;
326 x_last_update_login := fnd_global.login_id;
327 IF (x_last_update_login IS NULL) THEN
328 x_last_update_login := -1;
329 END IF;
330 ELSE
331 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
332 igs_ge_msg_stack.add;
333 app_exception.raise_exception;
334 END IF;
335
336 SELECT igs_as_gpc_aca_stndg_s.NEXTVAL
337 INTO x_gpc_aca_stndg_id
338 FROM dual;
339
340 before_dml(
341 p_action => 'INSERT',
342 x_rowid => x_rowid,
343 x_gpc_aca_stndg_id => x_gpc_aca_stndg_id,
344 x_grading_period_cd => x_grading_period_cd,
345 x_progression_status => x_progression_status,
346 x_creation_date => x_last_update_date,
347 x_created_by => x_last_updated_by,
348 x_last_update_date => x_last_update_date,
349 x_last_updated_by => x_last_updated_by,
350 x_last_update_login => x_last_update_login
351 );
352
353 INSERT INTO igs_as_gpc_aca_stndg (
354 gpc_aca_stndg_id,
355 grading_period_cd,
356 progression_status,
357 creation_date,
358 created_by,
359 last_update_date,
360 last_updated_by,
361 last_update_login
362 ) VALUES (
363 new_references.gpc_aca_stndg_id,
364 new_references.grading_period_cd,
365 new_references.progression_status,
366 x_last_update_date,
367 x_last_updated_by,
368 x_last_update_date,
369 x_last_updated_by,
370 x_last_update_login
371 );
372
373 OPEN c;
374 FETCH c INTO x_rowid;
375 IF (c%NOTFOUND) THEN
376 CLOSE c;
377 RAISE NO_DATA_FOUND;
378 END IF;
379 CLOSE c;
380
381 END insert_row;
382
383
384 PROCEDURE lock_row (
385 x_rowid IN VARCHAR2,
386 x_gpc_aca_stndg_id IN NUMBER,
387 x_grading_period_cd IN VARCHAR2,
388 x_progression_status IN VARCHAR2
389 ) AS
390 /*
391 || Created By : cdcruz
392 || Created On : 25-JUL-2001
393 || Purpose : Handles the LOCK mechanism for the table.
394 || Known limitations, enhancements or remarks :
395 || Change History :
396 || Who When What
397 || (reverse chronological order - newest change first)
398 */
399 CURSOR c1 IS
400 SELECT
401 grading_period_cd,
402 progression_status
403 FROM igs_as_gpc_aca_stndg
404 WHERE rowid = x_rowid
405 FOR UPDATE NOWAIT;
406
407 tlinfo c1%ROWTYPE;
408
409 BEGIN
410
411 OPEN c1;
412 FETCH c1 INTO tlinfo;
413 IF (c1%notfound) THEN
414 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
415 igs_ge_msg_stack.add;
416 CLOSE c1;
417 app_exception.raise_exception;
418 RETURN;
419 END IF;
420 CLOSE c1;
421
422 IF (
423 (tlinfo.grading_period_cd = x_grading_period_cd)
424 AND (tlinfo.progression_status = x_progression_status)
425 ) THEN
426 NULL;
427 ELSE
428 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
429 igs_ge_msg_stack.add;
430 app_exception.raise_exception;
431 END IF;
432
433 RETURN;
434
435 END lock_row;
436
437
438 PROCEDURE update_row (
439 x_rowid IN VARCHAR2,
440 x_gpc_aca_stndg_id IN NUMBER,
441 x_grading_period_cd IN VARCHAR2,
442 x_progression_status IN VARCHAR2,
443 x_mode IN VARCHAR2 DEFAULT 'R'
444 ) AS
445 /*
446 || Created By : cdcruz
447 || Created On : 25-JUL-2001
448 || Purpose : Handles the UPDATE DML logic for the table.
449 || Known limitations, enhancements or remarks :
450 || Change History :
451 || Who When What
452 || (reverse chronological order - newest change first)
453 */
454 x_last_update_date DATE ;
455 x_last_updated_by NUMBER;
456 x_last_update_login NUMBER;
457
458 BEGIN
459
460 x_last_update_date := SYSDATE;
461 IF (X_MODE = 'I') THEN
462 x_last_updated_by := 1;
463 x_last_update_login := 0;
464 ELSIF (x_mode = 'R') THEN
465 x_last_updated_by := fnd_global.user_id;
466 IF x_last_updated_by IS NULL THEN
467 x_last_updated_by := -1;
468 END IF;
469 x_last_update_login := fnd_global.login_id;
470 IF (x_last_update_login IS NULL) THEN
471 x_last_update_login := -1;
472 END IF;
473 ELSE
474 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
475 igs_ge_msg_stack.add;
476 app_exception.raise_exception;
477 END IF;
478
479 before_dml(
480 p_action => 'UPDATE',
481 x_rowid => x_rowid,
482 x_gpc_aca_stndg_id => x_gpc_aca_stndg_id,
483 x_grading_period_cd => x_grading_period_cd,
484 x_progression_status => x_progression_status,
485 x_creation_date => x_last_update_date,
486 x_created_by => x_last_updated_by,
487 x_last_update_date => x_last_update_date,
488 x_last_updated_by => x_last_updated_by,
489 x_last_update_login => x_last_update_login
490 );
491
492 UPDATE igs_as_gpc_aca_stndg
493 SET
494 grading_period_cd = new_references.grading_period_cd,
495 progression_status = new_references.progression_status,
496 last_update_date = x_last_update_date,
497 last_updated_by = x_last_updated_by,
498 last_update_login = x_last_update_login
499 WHERE rowid = x_rowid;
500
501 IF (SQL%NOTFOUND) THEN
502 RAISE NO_DATA_FOUND;
503 END IF;
504
505 END update_row;
506
507
508 PROCEDURE add_row (
509 x_rowid IN OUT NOCOPY VARCHAR2,
510 x_gpc_aca_stndg_id IN OUT NOCOPY NUMBER,
511 x_grading_period_cd IN VARCHAR2,
512 x_progression_status IN VARCHAR2,
513 x_mode IN VARCHAR2 DEFAULT 'R'
514 ) AS
515 /*
516 || Created By : cdcruz
517 || Created On : 25-JUL-2001
518 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
519 || Known limitations, enhancements or remarks :
520 || Change History :
521 || Who When What
522 || (reverse chronological order - newest change first)
523 */
524 CURSOR c1 IS
525 SELECT rowid
526 FROM igs_as_gpc_aca_stndg
527 WHERE gpc_aca_stndg_id = x_gpc_aca_stndg_id;
528
529 BEGIN
530
531 OPEN c1;
532 FETCH c1 INTO x_rowid;
533 IF (c1%NOTFOUND) THEN
534 CLOSE c1;
535
536 insert_row (
537 x_rowid,
538 x_gpc_aca_stndg_id,
539 x_grading_period_cd,
540 x_progression_status,
541 x_mode
542 );
543 RETURN;
544 END IF;
545 CLOSE c1;
546
547 update_row (
548 x_rowid,
549 x_gpc_aca_stndg_id,
550 x_grading_period_cd,
551 x_progression_status,
552 x_mode
553 );
554
555 END add_row;
556
557
558 PROCEDURE delete_row (
559 x_rowid IN VARCHAR2
560 ) AS
561 /*
562 || Created By : cdcruz
563 || Created On : 25-JUL-2001
564 || Purpose : Handles the DELETE DML logic for the table.
565 || Known limitations, enhancements or remarks :
566 || Change History :
567 || Who When What
568 || (reverse chronological order - newest change first)
569 */
570 BEGIN
571
572 before_dml (
573 p_action => 'DELETE',
574 x_rowid => x_rowid
575 );
576
577 DELETE FROM igs_as_gpc_aca_stndg
578 WHERE rowid = x_rowid;
579
580 IF (SQL%NOTFOUND) THEN
581 RAISE NO_DATA_FOUND;
582 END IF;
583
584 END delete_row;
585
586
587 END igs_as_gpc_aca_stndg_pkg;
[Home] [Help]