1 PACKAGE BODY igs_ps_emp_cats_wl_pkg AS
2 /* $Header: IGSPI3KB.pls 120.0 2005/06/02 00:18:30 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_emp_cats_wl%ROWTYPE;
6 new_references igs_ps_emp_cats_wl%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_cal_cat_code IN VARCHAR2,
12 x_emp_cat_code IN VARCHAR2,
13 x_expected_wl_num IN NUMBER,
14 x_creation_date IN DATE,
15 x_created_by IN NUMBER,
16 x_last_update_date IN DATE,
17 x_last_updated_by IN NUMBER,
18 x_last_update_login IN NUMBER
19 ) AS
20 /*
21 || Created By : smvk
22 || Created On : 13-JUN-2003
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_ps_emp_cats_wl
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.cal_cat_code := x_cal_cat_code;
54 new_references.emp_cat_code := x_emp_cat_code;
55 new_references.expected_wl_num := x_expected_wl_num;
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_parent_existance AS
73 /*
74 || Created By : smvk
75 || Created On : 13-JUN-2003
76 || Purpose : Checks for the existance of Parent records.
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 (((old_references.cal_cat_code = new_references.cal_cat_code)) OR
85 ((new_references.cal_cat_code IS NULL))) THEN
86 NULL;
87 ELSIF NOT igs_ps_exp_wl_pkg.get_pk_for_validation (
88 new_references.cal_cat_code
89 ) THEN
90 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
91 igs_ge_msg_stack.add;
92 app_exception.raise_exception;
93 END IF;
94
95 END check_parent_existance;
96
97
98 FUNCTION get_pk_for_validation (
99 x_cal_cat_code IN VARCHAR2,
100 x_emp_cat_code IN VARCHAR2
101 ) RETURN BOOLEAN AS
102 /*
103 || Created By : smvk
104 || Created On : 13-JUN-2003
105 || Purpose : Validates the Primary Key of the table.
106 || Known limitations, enhancements or remarks :
107 || Change History :
108 || Who When What
109 || (reverse chronological order - newest change first)
110 */
111 CURSOR cur_rowid IS
112 SELECT rowid
113 FROM igs_ps_emp_cats_wl
114 WHERE cal_cat_code = x_cal_cat_code
115 AND emp_cat_code = x_emp_cat_code
116 FOR UPDATE NOWAIT;
117
118 lv_rowid cur_rowid%RowType;
119
120 BEGIN
121
122 OPEN cur_rowid;
123 FETCH cur_rowid INTO lv_rowid;
124 IF (cur_rowid%FOUND) THEN
125 CLOSE cur_rowid;
126 RETURN(TRUE);
127 ELSE
128 CLOSE cur_rowid;
129 RETURN(FALSE);
130 END IF;
131
132 END get_pk_for_validation;
133
134
135 PROCEDURE get_fk_igs_ps_exp_wl (
136 x_calendar_cat IN VARCHAR2
137 ) AS
138 /*
139 || Created By : smvk
140 || Created On : 13-JUN-2003
141 || Purpose : Validates the Foreign Keys for the table.
142 || Known limitations, enhancements or remarks :
143 || Change History :
144 || Who When What
145 || (reverse chronological order - newest change first)
146 */
147 CURSOR cur_rowid IS
148 SELECT rowid
149 FROM igs_ps_emp_cats_wl
150 WHERE ((cal_cat_code = x_calendar_cat));
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 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
161 igs_ge_msg_stack.add;
162 app_exception.raise_exception;
163 RETURN;
164 END IF;
165 CLOSE cur_rowid;
166
167 END get_fk_igs_ps_exp_wl;
168
169 PROCEDURE check_expected_wl_num
170 AS
171 /*
172 || Created By : sarakshi
173 || Created On : 14-Feb-2005
174 || Purpose : To verify that decreasing the value for expected_wl_num will have no impact on the assigned workload for any faculty.
175 ||
176 || Known limitations, enhancements or remarks :
177 || Change History :
178 || Who When What
179 || (reverse chronological order - newest change first)
180 */
181
182 CURSOR cur_igs_ps_fac_wl IS
183 SELECT cal_type,ci_sequence_number,person_id
184 FROM igs_ps_fac_wl;
185
186 l_n_exp_wl NUMBER;
187 l_n_tot_fac_wl NUMBER;
188 l_error BOOLEAN := FALSE;
189 BEGIN
190 IF new_references.expected_wl_num <> old_references.expected_wl_num AND new_references.expected_wl_num < old_references.expected_wl_num THEN
191 FOR cur_igs_ps_fac_wl_rec IN cur_igs_ps_fac_wl LOOP
192 IF igs_ps_gen_001.fac_exceed_exp_wl(cur_igs_ps_fac_wl_rec.cal_type,
193 cur_igs_ps_fac_wl_rec.ci_sequence_number,
194 cur_igs_ps_fac_wl_rec.person_id,
195 0,
196 l_n_tot_fac_wl,
197 l_n_exp_wl
198 ) THEN
199 l_error := TRUE;
200 EXIT;
201 END IF;
202 END LOOP;
203
204 IF l_error THEN
205 fnd_message.set_name('IGS','IGS_PS_DECREASE_NOT_EXP_WL');
206 igs_ge_msg_stack.add;
207 app_exception.raise_exception;
208 END IF;
209
210 END IF;
211 END check_expected_wl_num;
212
213 PROCEDURE before_dml (
214 p_action IN VARCHAR2,
215 x_rowid IN VARCHAR2,
216 x_cal_cat_code IN VARCHAR2,
217 x_emp_cat_code IN VARCHAR2,
218 x_expected_wl_num IN NUMBER,
219 x_creation_date IN DATE,
220 x_created_by IN NUMBER,
221 x_last_update_date IN DATE,
222 x_last_updated_by IN NUMBER,
223 x_last_update_login IN NUMBER
224 ) AS
225 /*
226 || Created By : smvk
227 || Created On : 13-JUN-2003
228 || Purpose : Initialises the columns, Checks Constraints, Calls the
229 || Trigger Handlers for the table, before any DML operation.
230 || Known limitations, enhancements or remarks :
231 || Change History :
232 || Who When What
233 || (reverse chronological order - newest change first)
234 */
235 BEGIN
236
237 set_column_values (
238 p_action,
239 x_rowid,
240 x_cal_cat_code,
241 x_emp_cat_code,
242 x_expected_wl_num,
243 x_creation_date,
244 x_created_by,
245 x_last_update_date,
246 x_last_updated_by,
247 x_last_update_login
248 );
249
250 IF (p_action = 'INSERT') THEN
251 -- Call all the procedures related to Before Insert.
252 IF ( get_pk_for_validation(
253 new_references.cal_cat_code,
254 new_references.emp_cat_code
255 )
256 ) THEN
257 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
258 igs_ge_msg_stack.add;
259 app_exception.raise_exception;
260 END IF;
261 check_parent_existance;
262 ELSIF (p_action = 'UPDATE') THEN
263 -- Call all the procedures related to Before Update.
264 check_parent_existance;
265 ELSIF (p_action = 'VALIDATE_INSERT') THEN
266 -- Call all the procedures related to Before Insert.
267 IF ( get_pk_for_validation (
268 new_references.cal_cat_code,
269 new_references.emp_cat_code
270 )
271 ) THEN
272 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
273 igs_ge_msg_stack.add;
274 app_exception.raise_exception;
275 END IF;
276 END IF;
277
278 END before_dml;
279
280
281 PROCEDURE insert_row (
282 x_rowid IN OUT NOCOPY VARCHAR2,
283 x_cal_cat_code IN VARCHAR2,
284 x_emp_cat_code IN VARCHAR2,
285 x_expected_wl_num IN NUMBER,
286 x_mode IN VARCHAR2
287 ) AS
288 /*
289 || Created By : smvk
290 || Created On : 13-JUN-2003
291 || Purpose : Handles the INSERT DML logic for the table.
292 || Known limitations, enhancements or remarks :
293 || Change History :
294 || Who When What
295 || (reverse chronological order - newest change first)
296 */
297
298 x_last_update_date DATE;
299 x_last_updated_by NUMBER;
300 x_last_update_login NUMBER;
301
302 BEGIN
303
304 x_last_update_date := SYSDATE;
305 IF (x_mode = 'I') THEN
306 x_last_updated_by := 1;
307 x_last_update_login := 0;
308 ELSIF (x_mode = 'R') THEN
309 x_last_updated_by := fnd_global.user_id;
310 IF (x_last_updated_by IS NULL) THEN
311 x_last_updated_by := -1;
312 END IF;
313 x_last_update_login := fnd_global.login_id;
314 IF (x_last_update_login IS NULL) THEN
315 x_last_update_login := -1;
316 END IF;
317 ELSE
318 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
319 fnd_message.set_token ('ROUTINE', 'igs_ps_emp_cats_wl_PKG.INSERT_ROW');
320 igs_ge_msg_stack.add;
321 app_exception.raise_exception;
322 END IF;
323
324 before_dml(
325 p_action => 'INSERT',
326 x_rowid => x_rowid,
327 x_cal_cat_code => x_cal_cat_code,
331 x_created_by => x_last_updated_by,
328 x_emp_cat_code => x_emp_cat_code,
329 x_expected_wl_num => x_expected_wl_num,
330 x_creation_date => x_last_update_date,
332 x_last_update_date => x_last_update_date,
333 x_last_updated_by => x_last_updated_by,
334 x_last_update_login => x_last_update_login
335 );
336
337 INSERT INTO igs_ps_emp_cats_wl (
338 cal_cat_code,
339 emp_cat_code,
340 expected_wl_num,
341 creation_date,
342 created_by,
343 last_update_date,
344 last_updated_by,
345 last_update_login
346 ) VALUES (
347 new_references.cal_cat_code,
348 new_references.emp_cat_code,
349 new_references.expected_wl_num,
350 x_last_update_date,
351 x_last_updated_by,
352 x_last_update_date,
353 x_last_updated_by,
354 x_last_update_login
355 ) RETURNING ROWID INTO x_rowid;
356
357 END insert_row;
358
359
360 PROCEDURE lock_row (
361 x_rowid IN VARCHAR2,
365 ) AS
362 x_cal_cat_code IN VARCHAR2,
363 x_emp_cat_code IN VARCHAR2,
364 x_expected_wl_num IN NUMBER
366 /*
367 || Created By : smvk
368 || Created On : 13-JUN-2003
369 || Purpose : Handles the LOCK mechanism for the table.
370 || Known limitations, enhancements or remarks :
371 || Change History :
372 || Who When What
373 || (reverse chronological order - newest change first)
374 */
375 CURSOR c1 IS
376 SELECT
377 expected_wl_num
378 FROM igs_ps_emp_cats_wl
379 WHERE rowid = x_rowid
380 FOR UPDATE NOWAIT;
381
382 tlinfo c1%ROWTYPE;
383
384 BEGIN
385
386 OPEN c1;
387 FETCH c1 INTO tlinfo;
388 IF (c1%notfound) THEN
389 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
390 igs_ge_msg_stack.add;
391 CLOSE c1;
392 app_exception.raise_exception;
393 RETURN;
394 END IF;
395 CLOSE c1;
396
397 IF (
398 (tlinfo.expected_wl_num = x_expected_wl_num)
399 ) THEN
400 NULL;
401 ELSE
402 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
403 igs_ge_msg_stack.add;
404 app_exception.raise_exception;
405 END IF;
406
407 RETURN;
408
409 END lock_row;
410
411
412 PROCEDURE update_row (
413 x_rowid IN VARCHAR2,
414 x_cal_cat_code IN VARCHAR2,
415 x_emp_cat_code IN VARCHAR2,
416 x_expected_wl_num IN NUMBER,
417 x_mode IN VARCHAR2
418 ) AS
419 /*
420 || Created By : smvk
421 || Created On : 13-JUN-2003
422 || Purpose : Handles the UPDATE DML logic for the table.
423 || Known limitations, enhancements or remarks :
424 || Change History :
425 || Who When What
426 || (reverse chronological order - newest change first)
427 */
428 x_last_update_date DATE ;
429 x_last_updated_by NUMBER;
430 x_last_update_login NUMBER;
431
432 BEGIN
433
434 x_last_update_date := SYSDATE;
435 IF (X_MODE = 'I') THEN
436 x_last_updated_by := 1;
437 x_last_update_login := 0;
438 ELSIF (x_mode = 'R') THEN
439 x_last_updated_by := fnd_global.user_id;
440 IF x_last_updated_by IS NULL THEN
441 x_last_updated_by := -1;
442 END IF;
443 x_last_update_login := fnd_global.login_id;
444 IF (x_last_update_login IS NULL) THEN
445 x_last_update_login := -1;
446 END IF;
447 ELSE
448 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
449 fnd_message.set_token ('ROUTINE', 'igs_ps_emp_cats_wl_PKG.UPDATE_ROW');
450 igs_ge_msg_stack.add;
451 app_exception.raise_exception;
452 END IF;
453
454 before_dml(
455 p_action => 'UPDATE',
456 x_rowid => x_rowid,
457 x_cal_cat_code => x_cal_cat_code,
458 x_emp_cat_code => x_emp_cat_code,
459 x_expected_wl_num => x_expected_wl_num,
460 x_creation_date => x_last_update_date,
461 x_created_by => x_last_updated_by,
462 x_last_update_date => x_last_update_date,
463 x_last_updated_by => x_last_updated_by,
464 x_last_update_login => x_last_update_login
465 );
466
467 UPDATE igs_ps_emp_cats_wl
468 SET
469 expected_wl_num = new_references.expected_wl_num,
470 last_update_date = x_last_update_date,
471 last_updated_by = x_last_updated_by,
472 last_update_login = x_last_update_login
473 WHERE rowid = x_rowid;
474
475 IF (SQL%NOTFOUND) THEN
476 RAISE NO_DATA_FOUND;
477 END IF;
478
479 --Added as a part of bug#4099575,should not allow to lower the value of expected_wl_num if it is disrupting the setting already done in assign/override faculty workload
480 check_expected_wl_num;
481
482 END update_row;
483
484
485 PROCEDURE add_row (
486 x_rowid IN OUT NOCOPY VARCHAR2,
487 x_cal_cat_code IN VARCHAR2,
488 x_emp_cat_code IN VARCHAR2,
489 x_expected_wl_num IN NUMBER,
490 x_mode IN VARCHAR2
491 ) AS
492 /*
493 || Created By : smvk
494 || Created On : 13-JUN-2003
495 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
496 || Known limitations, enhancements or remarks :
497 || Change History :
498 || Who When What
499 || (reverse chronological order - newest change first)
500 */
501 CURSOR c1 IS
502 SELECT rowid
503 FROM igs_ps_emp_cats_wl
504 WHERE cal_cat_code = x_cal_cat_code
505 AND emp_cat_code = x_emp_cat_code;
506
507 BEGIN
508
509 OPEN c1;
510 FETCH c1 INTO x_rowid;
511 IF (c1%NOTFOUND) THEN
512 CLOSE c1;
513
514 insert_row (
515 x_rowid,
516 x_cal_cat_code,
517 x_emp_cat_code,
518 x_expected_wl_num,
519 x_mode
520 );
521 RETURN;
522 END IF;
523 CLOSE c1;
524
525 update_row (
526 x_rowid,
527 x_cal_cat_code,
528 x_emp_cat_code,
529 x_expected_wl_num,
530 x_mode
531 );
532
533 END add_row;
534
535
536 PROCEDURE delete_row (
537 x_rowid IN VARCHAR2
538 ) AS
539 /*
540 || Created By : smvk
541 || Created On : 13-JUN-2003
542 || Purpose : Handles the DELETE DML logic for the table.
543 || Known limitations, enhancements or remarks :
544 || Change History :
545 || Who When What
546 || (reverse chronological order - newest change first)
547 */
548 BEGIN
549
550 before_dml (
551 p_action => 'DELETE',
552 x_rowid => x_rowid
553 );
554
555 DELETE FROM igs_ps_emp_cats_wl
556 WHERE rowid = x_rowid;
557
558 IF (SQL%NOTFOUND) THEN
559 RAISE NO_DATA_FOUND;
560 END IF;
561
562 END delete_row;
563
564
565 END igs_ps_emp_cats_wl_pkg;
[Home] [Help]