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