1 PACKAGE BODY igs_as_gpc_pe_id_grp_pkg AS
2 /* $Header: IGSDI55B.pls 115.7 2003/02/18 09:14:54 npalanis ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_as_gpc_pe_id_grp%ROWTYPE;
6 new_references igs_as_gpc_pe_id_grp%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_gpc_pe_grp_id IN NUMBER DEFAULT NULL,
12 x_grading_period_cd IN VARCHAR2 DEFAULT NULL,
13 x_group_id IN NUMBER 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_PE_ID_GRP
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_pe_grp_id := x_gpc_pe_grp_id;
54 new_references.grading_period_cd := x_grading_period_cd;
55 new_references.group_id := x_group_id;
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.group_id
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 : cdcruz
100 || Created On : 25-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.group_id = new_references.group_id)) OR
110 ((new_references.group_id IS NULL))) THEN
111 NULL;
112 ELSIF NOT igs_pe_persid_group_pkg.get_pk_for_validation (
113 new_references.group_id
114 ) THEN
115 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
116 igs_ge_msg_stack.add;
117 app_exception.raise_exception;
118 ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation ('AS_GRADING_PERIOD',
119 new_references.grading_period_cd ) THEN
120
121 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
122 IGS_GE_MSG_STACK.ADD ;
123 APP_EXCEPTION.RAISE_EXCEPTION ;
124
125
126 END IF;
127
128 END check_parent_existance;
129
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_GPG_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_pe_grp_id IN NUMBER
166 ) RETURN BOOLEAN AS
167 /*
168 || Created By : cdcruz
169 || Created On : 25-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_pe_id_grp
179 WHERE gpc_pe_grp_id = x_gpc_pe_grp_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_group_id IN NUMBER
202 ) RETURN BOOLEAN AS
203 /*
204 || Created By : cdcruz
205 || Created On : 25-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_pe_id_grp
215 WHERE grading_period_cd = x_grading_period_cd
216 AND group_id = x_group_id
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 PROCEDURE BeforeInsertUpdate(p_inserting BOOLEAN , p_updating BOOLEAN) AS
236 p_message_name VARCHAR2(30);
237 BEGIN
238 IF ( p_inserting = TRUE OR (p_updating = TRUE AND new_references.group_id <> old_references.group_id ) ) THEN
239 IF NOT IGS_PE_PERSID_GROUP_PKG.val_persid_group(new_references.group_id,p_message_name) THEN
240 Fnd_Message.Set_Name('IGS', p_message_name);
241 IGS_GE_MSG_STACK.ADD;
242 App_Exception.Raise_Exception;
243 END IF;
244 END IF;
245 END BeforeInsertUpdate;
246
247 PROCEDURE before_dml (
248 p_action IN VARCHAR2,
249 x_rowid IN VARCHAR2 DEFAULT NULL,
250 x_gpc_pe_grp_id IN NUMBER DEFAULT NULL,
251 x_grading_period_cd IN VARCHAR2 DEFAULT NULL,
252 x_group_id IN NUMBER DEFAULT NULL,
253 x_creation_date IN DATE DEFAULT NULL,
254 x_created_by IN NUMBER DEFAULT NULL,
255 x_last_update_date IN DATE DEFAULT NULL,
256 x_last_updated_by IN NUMBER DEFAULT NULL,
257 x_last_update_login IN NUMBER DEFAULT NULL
258 ) AS
259 /*
260 || Created By : cdcruz
261 || Created On : 25-JUL-2001
262 || Purpose : Initialises the columns, Checks Constraints, Calls the
263 || Trigger Handlers for the table, before any DML operation.
264 || Known limitations, enhancements or remarks :
265 || Change History :
266 || Who When What
267 || (reverse chronological order - newest change first)
268 */
269 BEGIN
270
271 set_column_values (
272 p_action,
273 x_rowid,
274 x_gpc_pe_grp_id,
275 x_grading_period_cd,
276 x_group_id,
277 x_creation_date,
278 x_created_by,
279 x_last_update_date,
280 x_last_updated_by,
281 x_last_update_login
282 );
283
284 IF (p_action = 'INSERT') THEN
285 -- Call all the procedures related to Before Insert.
286 BeforeInsertUpdate(TRUE,FALSE);
287 IF ( get_pk_for_validation(
288 new_references.gpc_pe_grp_id
289 )
290 ) THEN
291 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
292 igs_ge_msg_stack.add;
293 app_exception.raise_exception;
294 END IF;
295 check_uniqueness;
296 check_parent_existance;
297 ELSIF (p_action = 'UPDATE') THEN
298 -- Call all the procedures related to Before Update.
299 BeforeInsertUpdate(FALSE,TRUE);
300 check_uniqueness;
301 check_parent_existance;
302 ELSIF (p_action = 'VALIDATE_INSERT') THEN
303 -- Call all the procedures related to Before Insert.
304 IF ( get_pk_for_validation (
305 new_references.gpc_pe_grp_id
306 )
307 ) THEN
308 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
309 igs_ge_msg_stack.add;
310 app_exception.raise_exception;
311 END IF;
312 check_uniqueness;
313 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
314 check_uniqueness;
315 END IF;
316
317 END before_dml;
318
319
320 PROCEDURE insert_row (
321 x_rowid IN OUT NOCOPY VARCHAR2,
322 x_gpc_pe_grp_id IN OUT NOCOPY NUMBER,
323 x_grading_period_cd IN VARCHAR2,
324 x_group_id IN NUMBER,
325 x_mode IN VARCHAR2 DEFAULT 'R'
326 ) AS
327 /*
328 || Created By : cdcruz
329 || Created On : 25-JUL-2001
330 || Purpose : Handles the INSERT DML logic for the table.
331 || Known limitations, enhancements or remarks :
332 || Change History :
336 CURSOR c IS
333 || Who When What
334 || (reverse chronological order - newest change first)
335 */
337 SELECT rowid
338 FROM igs_as_gpc_pe_id_grp
339 WHERE gpc_pe_grp_id = x_gpc_pe_grp_id;
340
341 x_last_update_date DATE;
342 x_last_updated_by NUMBER;
343 x_last_update_login NUMBER;
344
345 BEGIN
346
347 x_last_update_date := SYSDATE;
348 IF (x_mode = 'I') THEN
349 x_last_updated_by := 1;
350 x_last_update_login := 0;
351 ELSIF (x_mode = 'R') THEN
352 x_last_updated_by := fnd_global.user_id;
353 IF (x_last_updated_by IS NULL) THEN
354 x_last_updated_by := -1;
355 END IF;
356 x_last_update_login := fnd_global.login_id;
357 IF (x_last_update_login IS NULL) THEN
358 x_last_update_login := -1;
359 END IF;
360 ELSE
361 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
362 igs_ge_msg_stack.add;
363 app_exception.raise_exception;
364 END IF;
365
366 SELECT igs_as_gpc_pe_id_grp_s.NEXTVAL
367 INTO x_gpc_pe_grp_id
368 FROM dual;
369
370 before_dml(
371 p_action => 'INSERT',
372 x_rowid => x_rowid,
373 x_gpc_pe_grp_id => x_gpc_pe_grp_id,
374 x_grading_period_cd => x_grading_period_cd,
375 x_group_id => x_group_id,
376 x_creation_date => x_last_update_date,
377 x_created_by => x_last_updated_by,
378 x_last_update_date => x_last_update_date,
379 x_last_updated_by => x_last_updated_by,
380 x_last_update_login => x_last_update_login
381 );
382
383 INSERT INTO igs_as_gpc_pe_id_grp (
384 gpc_pe_grp_id,
385 grading_period_cd,
386 group_id,
387 creation_date,
388 created_by,
389 last_update_date,
390 last_updated_by,
391 last_update_login
392 ) VALUES (
393 new_references.gpc_pe_grp_id,
394 new_references.grading_period_cd,
395 new_references.group_id,
396 x_last_update_date,
397 x_last_updated_by,
398 x_last_update_date,
399 x_last_updated_by,
400 x_last_update_login
401 );
402
403 OPEN c;
404 FETCH c INTO x_rowid;
405 IF (c%NOTFOUND) THEN
406 CLOSE c;
407 RAISE NO_DATA_FOUND;
408 END IF;
409 CLOSE c;
410
411 END insert_row;
412
413
414 PROCEDURE lock_row (
415 x_rowid IN VARCHAR2,
416 x_gpc_pe_grp_id IN NUMBER,
417 x_grading_period_cd IN VARCHAR2,
418 x_group_id IN NUMBER
419 ) AS
420 /*
421 || Created By : cdcruz
422 || Created On : 25-JUL-2001
423 || Purpose : Handles the LOCK mechanism for the table.
424 || Known limitations, enhancements or remarks :
425 || Change History :
426 || Who When What
427 || (reverse chronological order - newest change first)
428 */
429 CURSOR c1 IS
430 SELECT
431 grading_period_cd,
432 group_id
433 FROM igs_as_gpc_pe_id_grp
434 WHERE rowid = x_rowid
435 FOR UPDATE NOWAIT;
436
437 tlinfo c1%ROWTYPE;
438
439 BEGIN
440
441 OPEN c1;
442 FETCH c1 INTO tlinfo;
443 IF (c1%notfound) THEN
444 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
445 igs_ge_msg_stack.add;
446 CLOSE c1;
447 app_exception.raise_exception;
448 RETURN;
449 END IF;
450 CLOSE c1;
451
452 IF (
453 (tlinfo.grading_period_cd = x_grading_period_cd)
454 AND (tlinfo.group_id = x_group_id)
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_pe_grp_id IN NUMBER,
471 x_grading_period_cd IN VARCHAR2,
472 x_group_id IN NUMBER,
473 x_mode IN VARCHAR2 DEFAULT 'R'
474 ) AS
475 /*
476 || Created By : cdcruz
477 || Created On : 25-JUL-2001
478 || Purpose : Handles the UPDATE DML logic for the table.
479 || Known limitations, enhancements or remarks :
480 || Change History :
481 || Who When What
482 || (reverse chronological order - newest change first)
483 */
484 x_last_update_date DATE ;
485 x_last_updated_by NUMBER;
486 x_last_update_login NUMBER;
487
488 BEGIN
489
490 x_last_update_date := SYSDATE;
491 IF (X_MODE = 'I') THEN
492 x_last_updated_by := 1;
493 x_last_update_login := 0;
494 ELSIF (x_mode = 'R') THEN
495 x_last_updated_by := fnd_global.user_id;
496 IF x_last_updated_by IS NULL THEN
497 x_last_updated_by := -1;
498 END IF;
499 x_last_update_login := fnd_global.login_id;
500 IF (x_last_update_login IS NULL) THEN
501 x_last_update_login := -1;
502 END IF;
503 ELSE
504 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
505 igs_ge_msg_stack.add;
506 app_exception.raise_exception;
507 END IF;
508
509 before_dml(
510 p_action => 'UPDATE',
511 x_rowid => x_rowid,
512 x_gpc_pe_grp_id => x_gpc_pe_grp_id,
513 x_grading_period_cd => x_grading_period_cd,
514 x_group_id => x_group_id,
515 x_creation_date => x_last_update_date,
516 x_created_by => x_last_updated_by,
517 x_last_update_date => x_last_update_date,
518 x_last_updated_by => x_last_updated_by,
519 x_last_update_login => x_last_update_login
520 );
521
522 UPDATE igs_as_gpc_pe_id_grp
523 SET
524 grading_period_cd = new_references.grading_period_cd,
525 group_id = new_references.group_id,
526 last_update_date = x_last_update_date,
527 last_updated_by = x_last_updated_by,
528 last_update_login = x_last_update_login
529 WHERE rowid = x_rowid;
530
531 IF (SQL%NOTFOUND) THEN
532 RAISE NO_DATA_FOUND;
533 END IF;
534
535 END update_row;
536
537
538 PROCEDURE add_row (
539 x_rowid IN OUT NOCOPY VARCHAR2,
540 x_gpc_pe_grp_id IN OUT NOCOPY NUMBER,
541 x_grading_period_cd IN VARCHAR2,
542 x_group_id IN NUMBER,
543 x_mode IN VARCHAR2 DEFAULT 'R'
544 ) AS
545 /*
546 || Created By : cdcruz
547 || Created On : 25-JUL-2001
548 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
549 || Known limitations, enhancements or remarks :
550 || Change History :
551 || Who When What
552 || (reverse chronological order - newest change first)
553 */
554 CURSOR c1 IS
555 SELECT rowid
556 FROM igs_as_gpc_pe_id_grp
557 WHERE gpc_pe_grp_id = x_gpc_pe_grp_id;
558
559 BEGIN
560
561 OPEN c1;
562 FETCH c1 INTO x_rowid;
563 IF (c1%NOTFOUND) THEN
564 CLOSE c1;
565
566 insert_row (
567 x_rowid,
568 x_gpc_pe_grp_id,
569 x_grading_period_cd,
570 x_group_id,
571 x_mode
572 );
573 RETURN;
574 END IF;
575 CLOSE c1;
576
577 update_row (
578 x_rowid,
579 x_gpc_pe_grp_id,
580 x_grading_period_cd,
581 x_group_id,
582 x_mode
583 );
584
585 END add_row;
586
587
588 PROCEDURE delete_row (
589 x_rowid IN VARCHAR2
590 ) AS
591 /*
592 || Created By : cdcruz
593 || Created On : 25-JUL-2001
594 || Purpose : Handles the DELETE DML logic for the table.
595 || Known limitations, enhancements or remarks :
596 || Change History :
597 || Who When What
598 || (reverse chronological order - newest change first)
599 */
600 BEGIN
601
602 before_dml (
603 p_action => 'DELETE',
604 x_rowid => x_rowid
605 );
606
607 DELETE FROM igs_as_gpc_pe_id_grp
608 WHERE rowid = x_rowid;
609
610 IF (SQL%NOTFOUND) THEN
611 RAISE NO_DATA_FOUND;
612 END IF;
613
614 END delete_row;
615
616
617 END igs_as_gpc_pe_id_grp_pkg;
[Home] [Help]