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