1 PACKAGE BODY igs_en_spl_perm_pkg AS
2 /* $Header: IGSEI53B.pls 120.3 2005/08/12 05:14:36 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_spl_perm%ROWTYPE;
6 new_references igs_en_spl_perm%ROWTYPE;
7
8 FUNCTION get_hz_pk_for_validation (
9 x_party_id IN NUMBER
10 ) RETURN BOOLEAN AS
11 /*
12 || Created By : pradhakr
13 || Created On : 29-JUN-2001
14 || Purpose : Validates the Primary Key of the table.
15 || This checks the primary coloumn AR table from IGS table.
16 || Known limitations, enhancements or remarks :
17 || Change History :
18 || Who When What
19 || (reverse chronological order - newest change first)
20 */
21 CURSOR cur_rowid IS
22 SELECT rowid
23 FROM hz_parties
24 WHERE party_id = x_party_id
25 FOR UPDATE NOWAIT;
26
27 lv_rowid cur_rowid%RowType;
28
29 BEGIN
30
31 OPEN cur_rowid;
32 FETCH cur_rowid INTO lv_rowid;
33 IF (cur_rowid%FOUND) THEN
34 CLOSE cur_rowid;
35 RETURN(TRUE);
36 ELSE
37 CLOSE cur_rowid;
38 RETURN(FALSE);
39 END IF;
40
41 END get_hz_pk_for_validation;
42
43 PROCEDURE set_column_values (
44 p_action IN VARCHAR2,
45 x_rowid IN VARCHAR2,
46 x_spl_perm_request_id IN NUMBER ,
47 x_student_person_id IN NUMBER ,
48 x_uoo_id IN NUMBER ,
49 x_date_submission IN DATE ,
50 x_audit_the_course IN VARCHAR2,
51 x_instructor_person_id IN NUMBER ,
52 x_approval_status IN VARCHAR2,
53 x_reason_for_request IN VARCHAR2,
54 x_instructor_more_info IN VARCHAR2,
55 x_instructor_deny_info IN VARCHAR2,
56 x_student_more_info IN VARCHAR2,
57 x_transaction_type IN VARCHAR2,
58 x_request_type IN VARCHAR2,
59 x_creation_date IN DATE ,
60 x_created_by IN NUMBER ,
61 x_last_update_date IN DATE ,
62 x_last_updated_by IN NUMBER ,
63 x_last_update_login IN NUMBER
64 ) AS
65 /*
66 || Created By : pradhakr
67 || Created On : 29-JUN-2001
68 || Purpose : Initialises the Old and New references for the columns of the table.
69 || Known limitations, enhancements or remarks :
70 || Change History :
71 || Who When What
72 || Nishikant 13JUN2002 -- some commented codes were present here and removed as per bug#2413811
73 || (reverse chronological order - newest change first)
74 */
75
76 CURSOR cur_old_ref_values IS
77 SELECT *
78 FROM IGS_EN_SPL_PERM
79 WHERE rowid = x_rowid;
80
81 /* ltranstype igs_en_spl_perm.transaction_type%TYPE;
82 lapprovstatus igs_en_spl_perm.approval_status%TYPE;*/
83
84 BEGIN
85
86 l_rowid := x_rowid;
87
88 -- Code for setting the Old and New Reference Values.
89 -- Populate Old Values.
90 OPEN cur_old_ref_values;
91 FETCH cur_old_ref_values INTO old_references;
92 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
93 CLOSE cur_old_ref_values;
94 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
95 igs_ge_msg_stack.add;
96 app_exception.raise_exception;
97 RETURN;
98 END IF;
99 CLOSE cur_old_ref_values;
100
101 --The following lines have code have been commented as this kind of
102 --validations may not be required now but may be at a Later point of time.
103 --Commented the Code as instructed by the DM.
104
105 --Based on Insert /Update we need to set the Transaction Type and Approval Status
106 --We have used another variable (lapprovstatus) for approval as we cannot assign
107 --a value to an Input Parameter.
108
109 -- some commented codes were present here and removed as per bug#2413811
110
111 -- Populate New Values.
112 new_references.spl_perm_request_id := x_spl_perm_request_id;
113 new_references.student_person_id := x_student_person_id;
114 new_references.uoo_id := x_uoo_id;
115 new_references.date_submission := x_date_submission;
116 new_references.audit_the_course := x_audit_the_course;
117 new_references.instructor_person_id := x_instructor_person_id;
118 new_references.approval_status := x_approval_status;
119 new_references.reason_for_request := x_reason_for_request;
120 new_references.instructor_more_info := x_instructor_more_info;
121 new_references.instructor_deny_info := x_instructor_deny_info;
122 new_references.student_more_info := x_student_more_info;
123 new_references.transaction_type := x_transaction_type;
124 new_references.request_type := x_request_type;
125 IF (p_action = 'UPDATE') THEN
126 new_references.creation_date := old_references.creation_date;
127 new_references.created_by := old_references.created_by;
128 ELSE
129 new_references.creation_date := x_creation_date;
130 new_references.created_by := x_created_by;
131 END IF;
132
133
134 new_references.last_update_date := x_last_update_date;
135 new_references.last_updated_by := x_last_updated_by;
136 new_references.last_update_login := x_last_update_login;
137
138 END set_column_values;
139
140
141 PROCEDURE beforerowinsertupdatedelete1(
142 p_inserting IN BOOLEAN,
143 p_updating IN BOOLEAN ,
144 p_deleting IN BOOLEAN
145 ) AS
146 /************************************************************************
147 Created By :
148 Date Created By :
149 Purpose :
150 Known limitations, enhancements or remarks:
151 Change History :
152 Who When What
153 (reverse chronological order - newest change first)
154 *************************************************************************/
155 l_message_name VARCHAR2(30);
156 l_return_type VARCHAR2(1);
157 l_spl_perm_request_id igs_en_spl_perm.spl_perm_request_id%TYPE;
158 l_date_submission igs_en_spl_perm.date_submission%TYPE;
159 l_audit_the_course igs_en_spl_perm.audit_the_course%TYPE;
160 l_approval_status igs_en_spl_perm.approval_status%TYPE;
161 l_reason_for_request igs_en_spl_perm.reason_for_request%TYPE;
162 l_instructor_more_info igs_en_spl_perm.instructor_more_info%TYPE;
163 l_instructor_deny_info igs_en_spl_perm.instructor_deny_info%TYPE;
164 l_student_more_info igs_en_spl_perm.student_more_info%TYPE;
165 l_transaction_type igs_en_spl_perm.transaction_type%TYPE;
166 l_rowid VARCHAR2(20);
167 l_spl_perm_request_h_id igs_en_spl_perm_h.spl_perm_request_h_id%TYPE;
168
169 CURSOR splh_cur IS
170 SELECT ROWID
171 FROM igs_en_spl_perm_h
172 WHERE spl_perm_request_id = old_references.spl_perm_request_id;
173
174
175 BEGIN
176
177
178 IF p_updating THEN
179
180 --Updating either Student More Information or Instructor More Information involves
181 --Concatenating the existing data with new data.
182
183 -- updating all the faculty and student comments first in the faculty
184 -- more info field, once that field is filled up then starting filling up
185 -- the student more info field, once that is also filled up then show
186 -- the error messages that the maximum field has been exceeded
187
188
189 -- it is assumed here that the faculty more information and the student more
190 -- information update cannot happen in a singled call to the update row
191
192 IF new_references.instructor_more_info IS NOT NULL AND
193 ( old_references.instructor_more_info IS NULL OR
194 new_references.instructor_more_info <> old_references.instructor_more_info ) THEN
195 IF old_references.student_more_info IS NOT NULL THEN
196 IF LENGTH(old_references.student_more_info || new_references.instructor_more_info ) <= 4000 THEN
197 new_references.student_more_info := old_references.student_more_info || new_references.instructor_more_info;
198 new_references.instructor_more_info := old_references.instructor_more_info;
199 ELSE
200 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FIELD_LENGTH_GREATER');
201 IGS_GE_MSG_STACK.ADD;
202 APP_EXCEPTION.RAISE_EXCEPTION;
203 END IF;
204 ELSE
205 IF LENGTH(old_references.instructor_more_info || new_references.instructor_more_info ) <= 4000 THEN
206 new_references.instructor_more_info := old_references.instructor_more_info|| new_references.instructor_more_info;
207 new_references.student_more_info := null;
208 ELSE
209 IF LENGTH(old_references.student_more_info || new_references.instructor_more_info ) <= 4000 THEN
210 new_references.student_more_info := old_references.student_more_info || new_references.instructor_more_info;
211 new_references.instructor_more_info := old_references.instructor_more_info;
212 ELSE
213 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FIELD_LENGTH_GREATER');
214 IGS_GE_MSG_STACK.ADD;
215 APP_EXCEPTION.RAISE_EXCEPTION;
216 END IF;
217 END IF;
218 END IF;
219
220 ELSIF new_references.student_more_info IS NOT NULL AND
221 ( new_references.student_more_info <> old_references.student_more_info
222 OR old_references.student_more_info IS NULL )THEN
223 IF old_references.student_more_info IS NOT NULL THEN
224 IF LENGTH(old_references.student_more_info || new_references.student_more_info ) <= 4000 THEN
225 new_references.student_more_info:= old_references.student_more_info || new_references.student_more_info;
226 new_references.instructor_more_info := old_references.instructor_more_info;
227 ELSE
228 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FIELD_LENGTH_GREATER');
229 IGS_GE_MSG_STACK.ADD;
230 APP_EXCEPTION.RAISE_EXCEPTION;
231 END IF;
232 ELSE
233 IF LENGTH(old_references.instructor_more_info || new_references.student_more_info ) <= 4000 THEN
234 new_references.instructor_more_info := old_references.instructor_more_info|| new_references.student_more_info;
235 new_references.student_more_info := null;
236 ELSE
237 IF LENGTH(old_references.student_more_info || new_references.student_more_info ) <= 4000 THEN
238 new_references.student_more_info := old_references.student_more_info || new_references.student_more_info;
239 new_references.instructor_more_info := old_references.instructor_more_info;
240 ELSE
241 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FIELD_LENGTH_GREATER');
242 IGS_GE_MSG_STACK.ADD;
243 APP_EXCEPTION.RAISE_EXCEPTION;
244 END IF;
245 END IF;
246 END IF;
247
248 END IF;
249
250
251 END IF;
252
253 -- Store IGS_EN_SPL_PERM Version History
254 IF p_updating THEN
255 IF old_references.spl_perm_request_id <> new_references.spl_perm_request_id OR
256 old_references.date_submission <> new_references.date_submission OR
257 old_references.audit_the_course <> new_references.audit_the_course OR
258 old_references.approval_status <> new_references.approval_status OR
259 old_references.reason_for_request <> new_references.reason_for_request OR
260 old_references.instructor_more_info <> new_references.instructor_more_info OR
261 old_references.instructor_deny_info <> new_references.instructor_deny_info OR
262 old_references.student_more_info <> new_references.student_more_info OR
263 old_references.transaction_type <> new_references.transaction_type THEN
264
265 SELECT
266 decode(old_references.spl_perm_request_id,new_references.spl_perm_request_id,
267 NULL,old_references.spl_perm_request_id),
268 decode(old_references.date_submission,new_references.date_submission,
269 NULL,old_references.date_submission),
270 decode(old_references.audit_the_course,new_references.audit_the_course,
271 NULL,old_references.audit_the_course),
272 decode(old_references.approval_status,new_references.approval_status,
273 NULL,old_references.approval_status),
274 decode(old_references.reason_for_request,new_references.reason_for_request,
275 NULL,old_references.reason_for_request),
276 decode(old_references.instructor_more_info,new_references.instructor_more_info,
277 NULL,old_references.instructor_more_info),
278 decode(old_references.instructor_deny_info,new_references.instructor_deny_info,
279 NULL,old_references.instructor_deny_info),
280 decode(old_references.student_more_info,new_references.student_more_info,
281 NULL,old_references.student_more_info),
282 decode(old_references.transaction_type,new_references.transaction_type,
283 NULL,old_references.transaction_type)
284
285 INTO
286 l_spl_perm_request_id,
287 l_date_submission,
288 l_audit_the_course,
289 l_approval_status,
290 l_reason_for_request,
291 l_instructor_more_info,
292 l_instructor_deny_info,
293 l_student_more_info,
294 l_transaction_type
295 FROM dual;
296
297 -- Create history record for update
298 igs_en_spl_perm_h_pkg.insert_row(
299 l_rowid,
300 l_spl_perm_request_h_id,
301 l_spl_perm_request_id,
302 l_date_submission,
303 l_audit_the_course,
304 l_approval_status ,
305 l_reason_for_request,
306 l_instructor_more_info,
307 l_instructor_deny_info,
308 l_student_more_info,
309 l_transaction_type,
310 old_references.last_update_date,
311 new_references.last_update_date,
312 old_references.last_updated_by
313 );
314
315 END IF;
316 END IF;
317
318 IF p_deleting THEN
319 BEGIN
320 FOR splh_rec IN splh_cur
321 LOOP
322 igs_en_spl_perm_h_pkg.delete_row(x_rowid => splh_rec.rowid);
323 END LOOP;
324 END;
325 END IF;
326
327 END beforerowinsertupdatedelete1;
328
329
330 PROCEDURE check_parent_existance AS
331 /*
332 || Created By : pradhakr
333 || Created On : 29-JUN-2001
334 || Purpose : Checks for the existance of Parent records.
335 || Known limitations, enhancements or remarks :
336 || Change History :
337 || Who When What
338 || (reverse chronological order - newest change first)
339 */
340 BEGIN
341
342 IF (((old_references.student_person_id = new_references.student_person_id)) OR
343 ((new_references.student_person_id IS NULL))) THEN
344 NULL;
345 ELSIF NOT get_hz_pk_for_validation (
346 new_references.student_person_id
347 ) THEN
348 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
349 igs_ge_msg_stack.add;
350 app_exception.raise_exception;
351 END IF;
352
353 IF (((old_references.uoo_id = new_references.uoo_id)) OR
354 ((new_references.uoo_id IS NULL))) THEN
355 NULL;
356 ELSIF NOT igs_ps_unit_ofr_opt_pkg.get_uk_For_validation (
357 new_references.uoo_id
358 ) THEN
359 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
360 igs_ge_msg_stack.add;
361 app_exception.raise_exception;
362 END IF;
363
364 IF (((old_references.instructor_person_id = new_references.instructor_person_id)) OR
365 ((new_references.instructor_person_id IS NULL))) THEN
366 NULL;
367 ELSIF NOT get_hz_pk_for_validation (
368 new_references.instructor_person_id
369 ) THEN
370 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
371 igs_ge_msg_stack.add;
372 app_exception.raise_exception;
373 END IF;
374
375 IF (((old_references.transaction_type =
376 new_references.transaction_type)) OR
377 ((new_references.transaction_type IS NULL))) THEN
378 NULL;
379 ELSE
380 IF NOT IGS_LOOKUPS_VIEW_Pkg.Get_PK_For_Validation ('SPL_PERM_TRANSCTION_TYPE',
381 new_references.transaction_type) THEN
382 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
383 Igs_Ge_Msg_Stack.Add;
384 App_Exception.Raise_Exception;
385 END IF;
386
387 END IF;
388
389
390 END check_parent_existance;
391
392
393 PROCEDURE check_child_existance IS
394 /*
395 || Created By : pradhakr
396 || Created On : 29-JUN-2001
397 || Purpose : Checks for the existance of Child records.
398 || Known limitations, enhancements or remarks :
399 || Change History :
400 || Who When What
401 || (reverse chronological order - newest change first)
402 */
403 BEGIN
404
405 igs_en_spl_perm_h_pkg.get_fk_igs_en_spl_perm (
406 old_references.spl_perm_request_id
407 );
408
409 END check_child_existance;
410
411
412 FUNCTION get_pk_for_validation (
413 x_spl_perm_request_id IN NUMBER
414 ) RETURN BOOLEAN AS
415 /*
416 || Created By : pradhakr
417 || Created On : 29-JUN-2001
418 || Purpose : Validates the Primary Key of the table.
419 || Known limitations, enhancements or remarks :
420 || Change History :
421 || Who When What
422 || (reverse chronological order - newest change first)
423 */
424 CURSOR cur_rowid IS
425 SELECT rowid
426 FROM igs_en_spl_perm
427 WHERE spl_perm_request_id = x_spl_perm_request_id
428 FOR UPDATE NOWAIT;
429
430 lv_rowid cur_rowid%RowType;
431
432 BEGIN
433
434 OPEN cur_rowid;
435 FETCH cur_rowid INTO lv_rowid;
436 IF (cur_rowid%FOUND) THEN
437 CLOSE cur_rowid;
438 RETURN(TRUE);
439 ELSE
440 CLOSE cur_rowid;
441 RETURN(FALSE);
442 END IF;
443
444 END get_pk_for_validation;
445
446
447 PROCEDURE get_fk_hz_parties (
448 x_party_id IN NUMBER
449 ) AS
450 /*
451 || Created By : pradhakr
452 || Created On : 29-JUN-2001
453 || Purpose : Validates the Foreign Keys for the table.
454 || Known limitations, enhancements or remarks :
455 || Change History :
456 || Who When What
457 || (reverse chronological order - newest change first)
458 */
459 CURSOR cur_rowid IS
460 SELECT rowid
461 FROM igs_en_spl_perm
462 WHERE ((student_person_id = x_party_id))
463 OR ((instructor_person_id = x_party_id));
464
465 lv_rowid cur_rowid%RowType;
466
467 BEGIN
468
469 OPEN cur_rowid;
470 FETCH cur_rowid INTO lv_rowid;
471 IF (cur_rowid%FOUND) THEN
472 CLOSE cur_rowid;
473 fnd_message.set_name ('IGS', 'IGS_EN_SPLP_HZ_FK');
474 igs_ge_msg_stack.add;
475 app_exception.raise_exception;
476 RETURN;
477 END IF;
478 CLOSE cur_rowid;
479
480 END get_fk_hz_parties;
481
482
483 PROCEDURE get_ufk_igs_ps_unit_ofr_opt (
484 x_uoo_id IN NUMBER
485 ) AS
486 /*
487 || Created By : pradhakr
488 || Created On : 29-JUN-2001
489 || Purpose : Validates the Foreign Keys for the table.
490 || Known limitations, enhancements or remarks :
491 || Change History :
492 || Who When What
493 || (reverse chronological order - newest change first)
494 */
495 CURSOR cur_rowid IS
496 SELECT rowid
497 FROM igs_en_spl_perm
498 WHERE ((uoo_id = x_uoo_id));
499
500 lv_rowid cur_rowid%RowType;
501
502 BEGIN
503
504 OPEN cur_rowid;
505 FETCH cur_rowid INTO lv_rowid;
506 IF (cur_rowid%FOUND) THEN
507 CLOSE cur_rowid;
508 fnd_message.set_name ('IGS', 'IGS_EN_SPLP_UOO_FK');
509 igs_ge_msg_stack.add;
510 app_exception.raise_exception;
511 RETURN;
512 END IF;
513 CLOSE cur_rowid;
514
515 END get_ufk_igs_ps_unit_ofr_opt;
516
517
518 PROCEDURE before_dml (
519 p_action IN VARCHAR2,
520 x_rowid IN VARCHAR2,
521 x_spl_perm_request_id IN NUMBER ,
522 x_student_person_id IN NUMBER ,
523 x_uoo_id IN NUMBER ,
524 x_date_submission IN DATE ,
525 x_audit_the_course IN VARCHAR2,
526 x_instructor_person_id IN NUMBER ,
527 x_approval_status IN VARCHAR2,
528 x_reason_for_request IN VARCHAR2,
529 x_instructor_more_info IN VARCHAR2,
530 x_instructor_deny_info IN VARCHAR2,
531 x_student_more_info IN VARCHAR2,
532 x_transaction_type IN VARCHAR2,
533 x_request_type IN VARCHAR2,
534 x_creation_date IN DATE ,
535 x_created_by IN NUMBER ,
536 x_last_update_date IN DATE ,
537 x_last_updated_by IN NUMBER ,
538 x_last_update_login IN NUMBER
539 ) AS
540 /*
541 || Created By : pradhakr
542 || Created On : 29-JUN-2001
543 || Purpose : Initialises the columns, Checks Constraints, Calls the
544 || Trigger Handlers for the table, before any DML operation.
545 || Known limitations, enhancements or remarks :
546 || Change History :
547 || Who When What
548 || (reverse chronological order - newest change first)
549 */
550
551
552 BEGIN
553
554 set_column_values (
555 p_action,
556 x_rowid,
557 x_spl_perm_request_id,
558 x_student_person_id,
559 x_uoo_id,
560 x_date_submission,
561 x_audit_the_course,
562 x_instructor_person_id,
563 x_approval_status,
564 x_reason_for_request,
565 x_instructor_more_info,
566 x_instructor_deny_info,
567 x_student_more_info,
568 x_transaction_type,
569 x_request_type,
570 x_creation_date,
571 x_created_by,
572 x_last_update_date,
573 x_last_updated_by,
574 x_last_update_login
575 );
576
577 IF (p_action = 'INSERT') THEN
578 -- Call all the procedures related to Before Insert.
579 IF ( get_pk_for_validation(
580 new_references.spl_perm_request_id
581 )
582 ) THEN
583 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
584 igs_ge_msg_stack.add;
585 app_exception.raise_exception;
586 END IF;
587 check_parent_existance;
588 ELSIF (p_action = 'UPDATE') THEN
589 -- Call all the procedures related to Before Update.
590 beforerowinsertupdatedelete1 ( p_inserting => FALSE,
591 p_updating => TRUE ,
592 p_deleting => FALSE);
593 check_parent_existance;
594 ELSIF (p_action = 'DELETE') THEN
595 -- Call all the procedures related to Before Delete.
596 beforerowinsertupdatedelete1 ( p_inserting => FALSE,
597 p_updating => FALSE ,
598 p_deleting => TRUE);
599 check_child_existance;
600 ELSIF (p_action = 'VALIDATE_INSERT') THEN
601 -- Call all the procedures related to Before Insert.
602 IF ( get_pk_for_validation (
603 new_references.spl_perm_request_id
604 )
605 ) THEN
606 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
607 igs_ge_msg_stack.add;
608 app_exception.raise_exception;
609 END IF;
610 ELSIF (p_action = 'VALIDATE_DELETE') THEN
611 check_child_existance;
612 END IF;
613
614 END before_dml;
615
616 PROCEDURE After_DML (
617 p_action IN VARCHAR2,
618 x_rowid IN VARCHAR2
619 ) AS
620 BEGIN
621
622 IF (p_action IN ('INSERT','UPDATE')) THEN
623
624 IF new_references.transaction_type IN ('STD_REQ','STD_AU_REQ','STD_MI') THEN
625 igs_en_workflow.inform_instruct_stdnt_petition( p_student_id => new_references.student_person_id,
626 p_instructor_id => new_references.instructor_person_id,
627 p_uoo_id => new_references.uoo_id,
628 p_date_submission => new_references.date_submission,
629 p_transaction_type => new_references.transaction_type,
630 p_request_type => new_references.request_type
631 );
632 ELSIF new_references.transaction_type IN ('INS_DENY','INS_MI','SPL_APRV','AUDIT_APRV') AND
633 new_references.transaction_type <> old_references.transaction_type THEN
634
635 igs_en_workflow.inform_stdnt_instruct_action( p_student_id => new_references.student_person_id,
636 p_instructor_id => new_references.instructor_person_id,
637 p_uoo_id => new_references.uoo_id,
638 p_approval_status => new_references.approval_status,
639 p_date_submission => new_references.date_submission,
640 p_request_type => new_references.request_type
641 );
642 END IF;
643
644 END IF;
645
646 END After_DML;
647
648
649 PROCEDURE insert_row (
650 x_rowid IN OUT NOCOPY VARCHAR2,
651 x_spl_perm_request_id IN OUT NOCOPY NUMBER,
652 x_student_person_id IN NUMBER,
653 x_uoo_id IN NUMBER,
654 x_date_submission IN DATE,
655 x_audit_the_course IN VARCHAR2,
656 x_instructor_person_id IN NUMBER,
657 x_approval_status IN VARCHAR2,
658 x_reason_for_request IN VARCHAR2,
659 x_instructor_more_info IN VARCHAR2,
660 x_instructor_deny_info IN VARCHAR2,
661 x_student_more_info IN VARCHAR2,
662 x_transaction_type IN VARCHAR2,
663 x_request_type IN VARCHAR2,
664 x_mode IN VARCHAR2
665 ) AS
666 /*
667 || Created By : pradhakr
668 || Created On : 29-JUN-2001
669 || Purpose : Handles the INSERT DML logic for the table.
670 || Known limitations, enhancements or remarks :
671 || Change History :
672 || Who When What
673 || (reverse chronological order - nbabyewest change first)
674 */
675 CURSOR c IS
676 SELECT rowid
677 FROM igs_en_spl_perm
678 WHERE spl_perm_request_id = x_spl_perm_request_id;
679
680 CURSOR c_spl_perm IS
681 SELECT rowid, approval_status, transaction_type FROM igs_en_spl_perm
682 WHERE student_person_id = x_student_person_id AND
683 uoo_id = x_uoo_id AND
684 request_type = x_request_type;
685
686 x_last_update_date DATE;
687 x_last_updated_by NUMBER;
688 x_last_update_login NUMBER;
689 l_perm_row c_spl_perm%ROWTYPE;
690 l_approval_status igs_en_spl_perm.approval_status%TYPE;
691 l_transaction_type igs_en_spl_perm.transaction_type%TYPE;
692
693
694 BEGIN
695
696 x_last_update_date := SYSDATE;
697 IF (x_mode = 'I') THEN
698 x_last_updated_by := 1;
699 x_last_update_login := 0;
700 ELSIF (x_mode = 'R') THEN
701 x_last_updated_by := fnd_global.user_id;
702 IF (x_last_updated_by IS NULL) THEN
703 x_last_updated_by := -1;
704 END IF;
705 x_last_update_login := fnd_global.login_id;
706 IF (x_last_update_login IS NULL) THEN
707 x_last_update_login := -1;
708 END IF;
709 ELSE
710 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
711 igs_ge_msg_stack.add;
712 app_exception.raise_exception;
713 END IF;
714
715 SELECT igs_en_spl_perm_s.NEXTVAL
716 INTO x_spl_perm_request_id
717 FROM dual;
718
719
720 OPEN c_spl_perm;
721 FETCH c_spl_perm INTO x_rowid,l_approval_status, l_transaction_type;
722 IF c_spl_perm%FOUND THEN
723 CLOSE c_spl_perm;
724 IF l_approval_status='W' and l_transaction_type='WITHDRAWN' THEN
725 update_row (
726 x_rowid,
727 x_spl_perm_request_id,
728 x_student_person_id,
729 x_uoo_id,
730 x_date_submission,
731 x_audit_the_course,
732 x_instructor_person_id,
733 x_approval_status,
734 x_reason_for_request,
735 x_instructor_more_info,
736 x_instructor_deny_info,
737 x_student_more_info,
738 x_transaction_type,
739 x_request_type,
740 x_mode
741 );
742 RETURN;
743 ELSE
744 fnd_message.set_name ('IGS', 'IGS_EN_REC_EXST_APRV_MORE');
745 igs_ge_msg_stack.add;
746 app_exception.raise_exception;
747 END IF;
748 ELSE
749 CLOSE c_spl_perm;
750 END IF;
751
752 before_dml(
753 p_action => 'INSERT',
754 x_rowid => x_rowid,
755 x_spl_perm_request_id => x_spl_perm_request_id,
756 x_student_person_id => x_student_person_id,
757 x_uoo_id => x_uoo_id,
758 x_date_submission => x_date_submission,
759 x_audit_the_course => x_audit_the_course,
760 x_instructor_person_id => x_instructor_person_id,
761 x_approval_status => x_approval_status,
762 x_reason_for_request => x_reason_for_request,
763 x_instructor_more_info => x_instructor_more_info,
764 x_instructor_deny_info => x_instructor_deny_info,
765 x_student_more_info => x_student_more_info,
766 x_transaction_type => x_transaction_type,
767 x_request_type => x_request_type,
768 x_creation_date => x_last_update_date,
769 x_created_by => x_last_updated_by,
770 x_last_update_date => x_last_update_date,
771 x_last_updated_by => x_last_updated_by,
772 x_last_update_login => x_last_update_login
773 );
774
775 INSERT INTO igs_en_spl_perm (
776 spl_perm_request_id,
777 student_person_id,
778 uoo_id,
779 date_submission,
780 audit_the_course,
781 instructor_person_id,
782 approval_status,
783 reason_for_request,
784 instructor_more_info,
785 instructor_deny_info,
786 student_more_info,
787 transaction_type,
788 request_type,
789 creation_date,
790 created_by,
791 last_update_date,
792 last_updated_by,
793 last_update_login
794 ) VALUES (
795 new_references.spl_perm_request_id,
796 new_references.student_person_id,
797 new_references.uoo_id,
798 new_references.date_submission,
799 new_references.audit_the_course,
800 new_references.instructor_person_id,
801 new_references.approval_status,
802 new_references.reason_for_request,
803 new_references.instructor_more_info,
804 new_references.instructor_deny_info,
805 new_references.student_more_info,
806 new_references.transaction_type,
807 new_references.request_type,
808 x_last_update_date,
809 x_last_updated_by,
810 x_last_update_date,
811 x_last_updated_by,
812 x_last_update_login
813 );
814
815 OPEN c;
816 FETCH c INTO x_rowid;
817 IF (c%NOTFOUND) THEN
818 CLOSE c;
819 RAISE NO_DATA_FOUND;
820 END IF;
821 CLOSE c;
822
823 After_DML (
824 p_action =>'INSERT',
825 x_rowid => X_ROWID
826 );
827
828 END insert_row;
829
830
831 PROCEDURE lock_row (
832 x_rowid IN VARCHAR2,
833 x_spl_perm_request_id IN NUMBER,
834 x_student_person_id IN NUMBER,
835 x_uoo_id IN NUMBER,
836 x_date_submission IN DATE,
837 x_audit_the_course IN VARCHAR2,
838 x_instructor_person_id IN NUMBER,
839 x_approval_status IN VARCHAR2,
840 x_reason_for_request IN VARCHAR2,
841 x_instructor_more_info IN VARCHAR2,
842 x_instructor_deny_info IN VARCHAR2,
843 x_student_more_info IN VARCHAR2,
844 x_transaction_type IN VARCHAR2,
845 x_request_type IN VARCHAR2
846 ) AS
847 /*
848 || Created By : pradhakr
849 || Created On : 29-JUN-2001
850 || Purpose : Handles the LOCK mechanism for the table.
851 || Known limitations, enhancements or remarks :
852 || Change History :
853 || Who When What
854 || (reverse chronological order - newest change first)
855 */
856 CURSOR c1 IS
857 SELECT
858 student_person_id,
859 uoo_id,
860 date_submission,
861 audit_the_course,
862 instructor_person_id,
863 approval_status,
864 reason_for_request,
865 instructor_more_info,
866 instructor_deny_info,
867 student_more_info,
868 transaction_type,
869 request_type
870 FROM igs_en_spl_perm
871 WHERE rowid = x_rowid
872 FOR UPDATE NOWAIT;
873
874 tlinfo c1%ROWTYPE;
875
876 BEGIN
877
878 OPEN c1;
879 FETCH c1 INTO tlinfo;
880 IF (c1%notfound) THEN
881 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
882 igs_ge_msg_stack.add;
883 CLOSE c1;
884 app_exception.raise_exception;
885 RETURN;
886 END IF;
887 CLOSE c1;
888
889 IF (
890 (tlinfo.student_person_id = x_student_person_id)
891 AND (tlinfo.uoo_id = x_uoo_id)
892 AND (tlinfo.date_submission = x_date_submission)
893 AND (tlinfo.audit_the_course = x_audit_the_course)
894 AND (tlinfo.instructor_person_id = x_instructor_person_id)
895 AND (tlinfo.approval_status = x_approval_status)
896 AND (tlinfo.reason_for_request = x_reason_for_request)
897 AND ((tlinfo.instructor_more_info = x_instructor_more_info) OR ((tlinfo.instructor_more_info IS NULL) AND (X_instructor_more_info IS NULL)))
898 AND ((tlinfo.instructor_deny_info = x_instructor_deny_info) OR ((tlinfo.instructor_deny_info IS NULL) AND (X_instructor_deny_info IS NULL)))
899 AND ((tlinfo.student_more_info = x_student_more_info) OR ((tlinfo.student_more_info IS NULL) AND (X_student_more_info IS NULL)))
900 AND ((tlinfo.transaction_type = x_transaction_type) OR ((tlinfo.transaction_type IS NULL) AND (X_transaction_type IS NULL)))
901 AND ((tlinfo.request_type = x_request_type) OR ((tlinfo.request_type IS NULL) AND (X_request_type IS NULL)))
902 ) THEN
903 NULL;
904 ELSE
905 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
906 igs_ge_msg_stack.add;
907 app_exception.raise_exception;
908 END IF;
909
910 RETURN;
911
912 END lock_row;
913
914
915 PROCEDURE update_row (
916 x_rowid IN VARCHAR2,
917 x_spl_perm_request_id IN NUMBER,
918 x_student_person_id IN NUMBER,
919 x_uoo_id IN NUMBER,
920 x_date_submission IN DATE,
921 x_audit_the_course IN VARCHAR2,
922 x_instructor_person_id IN NUMBER,
923 x_approval_status IN VARCHAR2,
924 x_reason_for_request IN VARCHAR2,
925 x_instructor_more_info IN VARCHAR2,
926 x_instructor_deny_info IN VARCHAR2,
927 x_student_more_info IN VARCHAR2,
928 x_transaction_type IN VARCHAR2,
929 x_request_type IN VARCHAR2,
930 x_mode IN VARCHAR2
931 ) AS
932 /*
933 || Created By : pradhakr
934 || Created On : 29-JUN-2001
935 || Purpose : Handles the UPDATE DML logic for the table.
936 || Known limitations, enhancements or remarks :
937 || Change History :
938 || Who When What
939 || (reverse chronological order - newest change first)
940 */
941 x_last_update_date DATE ;
942 x_last_updated_by NUMBER;
943 x_last_update_login NUMBER;
944
945
946 BEGIN
947
948 x_last_update_date := SYSDATE;
949 IF (X_MODE = 'I') THEN
950 x_last_updated_by := 1;
951 x_last_update_login := 0;
952 ELSIF (x_mode = 'R') THEN
953 x_last_updated_by := fnd_global.user_id;
954 IF x_last_updated_by IS NULL THEN
955 x_last_updated_by := -1;
956 END IF;
957 x_last_update_login := fnd_global.login_id;
958 IF (x_last_update_login IS NULL) THEN
959 x_last_update_login := -1;
960 END IF;
961 ELSE
962 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
963 igs_ge_msg_stack.add;
964 app_exception.raise_exception;
965 END IF;
966
967 before_dml(
968 p_action => 'UPDATE',
969 x_rowid => x_rowid,
970 x_spl_perm_request_id => x_spl_perm_request_id,
971 x_student_person_id => x_student_person_id,
972 x_uoo_id => x_uoo_id,
973 x_date_submission => x_date_submission,
974 x_audit_the_course => x_audit_the_course,
975 x_instructor_person_id => x_instructor_person_id,
976 x_approval_status => x_approval_status,
977 x_reason_for_request => x_reason_for_request,
978 x_instructor_more_info => x_instructor_more_info,
979 x_instructor_deny_info => x_instructor_deny_info,
980 x_student_more_info => x_student_more_info,
981 x_transaction_type => x_transaction_type,
982 x_request_type => x_request_type,
983 x_creation_date => x_last_update_date,
984 x_created_by => x_last_updated_by,
985 x_last_update_date => x_last_update_date,
986 x_last_updated_by => x_last_updated_by,
987 x_last_update_login => x_last_update_login
988 );
989
990
991 UPDATE igs_en_spl_perm
992 SET
993 student_person_id = new_references.student_person_id,
994 uoo_id = new_references.uoo_id,
995 date_submission = new_references.date_submission,
996 audit_the_course = new_references.audit_the_course,
997 instructor_person_id = new_references.instructor_person_id,
998 approval_status = new_references.approval_status,
999 reason_for_request = new_references.reason_for_request,
1000 instructor_more_info = new_references.instructor_more_info,
1001 instructor_deny_info = new_references.instructor_deny_info,
1002 student_more_info = new_references.student_more_info,
1003 transaction_type = new_references.transaction_type,
1004 request_type = new_references.request_type,
1005 last_update_date = x_last_update_date,
1006 last_updated_by = x_last_updated_by,
1007 last_update_login = x_last_update_login
1008 WHERE rowid = x_rowid;
1009
1010 IF (SQL%NOTFOUND) THEN
1011 RAISE NO_DATA_FOUND;
1012 END IF;
1013
1014 After_DML (
1015 p_action =>'UPDATE',
1016 x_rowid => X_ROWID
1017 );
1018
1019 END update_row;
1020
1021
1022 PROCEDURE add_row (
1023 x_rowid IN OUT NOCOPY VARCHAR2,
1024 x_spl_perm_request_id IN OUT NOCOPY NUMBER,
1025 x_student_person_id IN NUMBER,
1026 x_uoo_id IN NUMBER,
1027 x_date_submission IN DATE,
1028 x_audit_the_course IN VARCHAR2,
1029 x_instructor_person_id IN NUMBER,
1030 x_approval_status IN VARCHAR2,
1031 x_reason_for_request IN VARCHAR2,
1032 x_instructor_more_info IN VARCHAR2,
1033 x_instructor_deny_info IN VARCHAR2,
1034 x_student_more_info IN VARCHAR2,
1035 x_transaction_type IN VARCHAR2,
1036 x_request_type IN VARCHAR2,
1037 x_mode IN VARCHAR2
1038 ) AS
1039 /*
1040 || Created By : pradhakr
1041 || Created On : 29-JUN-2001
1042 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1043 || Known limitations, enhancements or remarks :
1044 || Change History :
1045 || Who When What
1046 || (reverse chronological order - newest change first)
1047 */
1048 CURSOR c1 IS
1049 SELECT rowid
1050 FROM igs_en_spl_perm
1051 WHERE spl_perm_request_id = x_spl_perm_request_id;
1052
1053 BEGIN
1054
1055 OPEN c1;
1056 FETCH c1 INTO x_rowid;
1057 IF (c1%NOTFOUND) THEN
1058 CLOSE c1;
1059
1060 insert_row (
1061 x_rowid,
1062 x_spl_perm_request_id,
1063 x_student_person_id,
1064 x_uoo_id,
1065 x_date_submission,
1066 x_audit_the_course,
1067 x_instructor_person_id,
1068 x_approval_status,
1069 x_reason_for_request,
1070 x_instructor_more_info,
1071 x_instructor_deny_info,
1072 x_student_more_info,
1073 x_transaction_type,
1074 x_request_type,
1075 x_mode
1076 );
1077 RETURN;
1078 END IF;
1079 CLOSE c1;
1080
1081 update_row (
1082 x_rowid,
1083 x_spl_perm_request_id,
1084 x_student_person_id,
1085 x_uoo_id,
1086 x_date_submission,
1087 x_audit_the_course,
1088 x_instructor_person_id,
1089 x_approval_status,
1090 x_reason_for_request,
1091 x_instructor_more_info,
1092 x_instructor_deny_info,
1093 x_student_more_info,
1094 x_transaction_type,
1095 x_request_type,
1096 x_mode
1097 );
1098
1099 END add_row;
1100
1101
1102 PROCEDURE delete_row (
1103 x_rowid IN VARCHAR2
1104 ) AS
1105 /*
1106 || Created By : pradhakr
1107 || Created On : 29-JUN-2001
1108 || Purpose : Handles the DELETE DML logic for the table.
1109 || Known limitations, enhancements or remarks :
1110 || Change History :
1111 || Who When What
1112 || (reverse chronological order - newest change first)
1113 */
1114 BEGIN
1115
1116 before_dml (
1117 p_action => 'DELETE',
1118 x_rowid => x_rowid
1119 );
1120
1121 DELETE FROM igs_en_spl_perm
1122 WHERE rowid = x_rowid;
1123
1124 IF (SQL%NOTFOUND) THEN
1125 RAISE NO_DATA_FOUND;
1126 END IF;
1127
1128 END delete_row;
1129
1130
1131 END igs_en_spl_perm_pkg;
[Home] [Help]