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