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