1 PACKAGE BODY igs_ad_term_unitdtls_pkg AS
2 /* $Header: IGSAI84B.pls 120.2 2005/10/01 21:47:44 appldev ship $ */
3 PROCEDURE update_term_tab(x_term_id IN NUMBER);
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_term_unitdtls%RowType;
6 new_references igs_ad_term_unitdtls%RowType;
7
8
9 PROCEDURE Check_Status
10 AS
11 /*************************************************************
12 Created By : jchin
13 Date Created By : 29-Sep-2005
14 Purpose : Check if associated academic record is INACTIVE and throw
15 an error if it is
16 Know limitations, enhancements or remarks
17 Change History
18 Who When What
19 (reverse chronological order - newest change first)
20 ***************************************************************/
21
22 CURSOR check_status(cp_term_details_id IN NUMBER) IS
23 SELECT DISTINCT 1
24 FROM igs_ad_acad_history_v hist, igs_ad_transcript_v trans, igs_ad_term_details_v term
25 WHERE hist.education_id = trans.education_id
26 AND trans.transcript_id = term.transcript_id
27 AND term.term_details_id = cp_term_details_id
28 AND hist.status = 'I';
29
30 l_temp NUMBER;
31
32 BEGIN
33
34 l_temp := null;
35
36 OPEN check_status(new_references.term_details_id);
37 FETCH check_status INTO l_temp;
38 CLOSE check_status;
39
40 IF l_temp IS NOT NULL THEN
41
42 Fnd_message.Set_Name('IGS', 'IGS_AD_INACTIVE_ACAD_HIST');
43 IGS_GE_MSG_STACK.ADD;
44 app_exception.Raise_Exception;
45
46 END IF;
47
48 END Check_Status;
49
50
51 PROCEDURE Set_Column_Values (
52 p_action IN VARCHAR2,
53 x_rowid IN VARCHAR2 DEFAULT NULL,
54 x_unit_details_id IN NUMBER DEFAULT NULL,
55 x_term_details_id IN NUMBER DEFAULT NULL,
56 x_unit IN VARCHAR2 DEFAULT NULL,
57 x_unit_difficulty IN NUMBER DEFAULT NULL,
58 x_unit_name IN VARCHAR2 DEFAULT NULL,
59 x_cp_attempted IN NUMBER DEFAULT NULL,
60 x_cp_earned IN NUMBER DEFAULT NULL,
61 x_grade IN VARCHAR2 DEFAULT NULL,
62 x_unit_grade_points IN NUMBER DEFAULT NULL,
63 x_deg_aud_detail_id IN NUMBER DEFAULT NULL,
64 x_creation_date IN DATE DEFAULT NULL,
65 x_created_by IN NUMBER DEFAULT NULL,
66 x_last_update_date IN DATE DEFAULT NULL,
67 x_last_updated_by IN NUMBER DEFAULT NULL,
68 x_last_update_login IN NUMBER DEFAULT NULL
69 ) AS
70
71 /*************************************************************
72 Created By : Kamalakar N.
73 Date Created By : 15/May/2000
74 Purpose :
75 Know limitations, enhancements or remarks
76 Change History
77 Who When What
78
79 (reverse chronological order - newest change first)
80 ***************************************************************/
81
82 CURSOR cur_old_ref_values IS
83 SELECT *
84 FROM IGS_AD_TERM_UNITDTLS
85 WHERE rowid = x_rowid;
86
87 BEGIN
88
89 l_rowid := x_rowid;
90
91 -- Code for setting the Old and New Reference Values.
92 -- Populate Old Values.
93 Open cur_old_ref_values;
94 Fetch cur_old_ref_values INTO old_references;
95 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
96 Close cur_old_ref_values;
97 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
98 IGS_GE_MSG_STACK.ADD;
99 App_Exception.Raise_Exception;
100 Return;
101 END IF;
102 Close cur_old_ref_values;
103
104 -- Populate New Values.
105 new_references.unit_details_id := x_unit_details_id;
106 new_references.term_details_id := x_term_details_id;
107 new_references.unit := x_unit;
108 new_references.unit_difficulty := x_unit_difficulty;
109 new_references.unit_name := x_unit_name;
110 new_references.cp_attempted := x_cp_attempted;
111 new_references.cp_earned := x_cp_earned;
112 new_references.grade := x_grade;
113 new_references.unit_grade_points := x_unit_grade_points;
114 new_references.deg_aud_detail_id := x_deg_aud_detail_id;
115 IF (p_action = 'UPDATE') THEN
116 new_references.creation_date := old_references.creation_date;
117 new_references.created_by := old_references.created_by;
118 ELSE
119 new_references.creation_date := x_creation_date;
120 new_references.created_by := x_created_by;
121 END IF;
122 new_references.last_update_date := x_last_update_date;
123 new_references.last_updated_by := x_last_updated_by;
124 new_references.last_update_login := x_last_update_login;
125
126 END Set_Column_Values;
127
128 PROCEDURE Check_Constraints (
129 Column_Name IN VARCHAR2 DEFAULT NULL,
130 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
131 /*************************************************************
132 Created By : Kamalakar N.
133 Date Created By : 15/May/2000
134 Purpose :
135 Know limitations, enhancements or remarks
136 Change History
137 Who When What
138 rboddu 10-DEC-2002 modified for bug 2623180
139 (reverse chronological order - newest change first)
140 ***************************************************************/
141
142 BEGIN
143
144 IF column_name IS NULL THEN
145 NULL;
146 ELSIF UPPER(column_name) = 'CP_ATTEMPTED' THEN
147 new_references.cp_attempted := IGS_GE_NUMBER.TO_NUM(column_value);
148 ELSIF UPPER(column_name) = 'CP_EARNED' THEN
149 new_references.cp_earned := IGS_GE_NUMBER.TO_NUM(column_value);
150 ELSIF UPPER(column_name) = 'UNIT_GRADE_POINTS' THEN
151 new_references.unit_grade_points := IGS_GE_NUMBER.TO_NUM(column_value);
152 NULL;
153 END IF;
154
155
156
157 -- The following code checks for check constraints on the Columns.
158
159 -- Bug: 2623180. changed >0 to >=0 in the following credit point comparison checks
160 IF Upper(Column_Name) = 'CP_ATTEMPTED' OR
161 Column_Name IS NULL THEN
162 IF NOT (new_references.cp_attempted >= 0) THEN
163 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
164 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_CP_ATTEMPTED'));
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168 END IF;
169
170 -- The following code checks for check constraints on the Columns.
171 IF Upper(Column_Name) = 'CP_EARNED' OR
172 Column_Name IS NULL THEN
173 IF NOT (new_references.cp_earned >= 0) THEN
174 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
175 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_CP_EARNED'));
176 IGS_GE_MSG_STACK.ADD;
177 App_Exception.Raise_Exception;
178 END IF;
179 END IF;
180
181 -- The following code checks for check constraints on the Columns.
182 IF Upper(Column_Name) = 'UNIT_GRADE_POINTS' OR
183 Column_Name IS NULL THEN
184 IF NOT (new_references.unit_grade_points >= 0) THEN
185 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
186 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_GRADE_POINT'));
187 IGS_GE_MSG_STACK.ADD;
188 App_Exception.Raise_Exception;
189 END IF;
190 END IF;
191 END Check_Constraints;
192
193
194
195 PROCEDURE Check_Uniqueness AS
196 /*************************************************************
197 Created By : amuthu
198 Date Created On : 16-May-2000
199 Purpose :
200 Know limitations, enhancements or remarks
201 Change History
202 Who When What
203
204 (reverse chronological order - newest change first)
205 ***************************************************************/
206 Begin
207 IF Get_Uk_for_Validation(
208 new_references.term_details_id,
209 new_references.unit
210 ) THEN
211 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
212 IGS_GE_MSG_STACK.ADD;
213 app_exception.raise_exception;
214 END IF;
215
216 END Check_Uniqueness ;
217
218 PROCEDURE Check_Parent_Existance AS
219 /*************************************************************
220 Created By : Kamalakar N.
221 Date Created By : 15/May/2000
222 Purpose :
223 Know limitations, enhancements or remarks
224 Change History
225 Who When What
226
227 (reverse chronological order - newest change first)
228 ***************************************************************/
229
230 BEGIN
231
232 IF (((old_references.unit_difficulty = new_references.unit_difficulty)) OR
233 ((new_references.unit_difficulty IS NULL))) THEN
234 NULL;
235 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_uk2_For_Validation (
236 new_references.unit_difficulty ,
237 'UNIT_DIFFICULTY',
238 'N'
239 ) THEN
240 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
241 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_UNIT_DIFFICULTY'));
242 IGS_GE_MSG_STACK.ADD;
243 App_Exception.Raise_Exception;
244 END IF;
245
246 IF (((old_references.term_details_id = new_references.term_details_id)) OR
247 ((new_references.term_details_id IS NULL))) THEN
248 NULL;
249 ELSIF NOT Igs_Ad_Term_Details_Pkg.Get_PK_For_Validation (
250 new_references.term_details_id
251 ) THEN
252 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
253 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TERM_DETAILS'));
254 IGS_GE_MSG_STACK.ADD;
255 App_Exception.Raise_Exception;
256 END IF;
257
258 END Check_Parent_Existance;
259
260 FUNCTION Get_PK_For_Validation (
261 x_unit_details_id IN NUMBER
262 ) RETURN BOOLEAN AS
263
264 /*************************************************************
265 Created By : Kamalakar N.
266 Date Created By : 15/May/2000
267 Purpose :
268 Know limitations, enhancements or remarks
269 Change History
270 Who When What
271
272 (reverse chronological order - newest change first)
273 ***************************************************************/
274
275 CURSOR cur_rowid IS
276 SELECT rowid
277 FROM igs_ad_term_unitdtls
278 WHERE unit_details_id = x_unit_details_id
279 FOR UPDATE NOWAIT;
280
281 lv_rowid cur_rowid%RowType;
282
283 BEGIN
284
285 Open cur_rowid;
286 Fetch cur_rowid INTO lv_rowid;
287 IF (cur_rowid%FOUND) THEN
288 Close cur_rowid;
289 Return(TRUE);
290 ELSE
291 Close cur_rowid;
292 Return(FALSE);
293 END IF;
294 END Get_PK_For_Validation;
295
296 FUNCTION Get_UK_For_Validation (
297 x_term_details_id IN NUMBER,
298 x_unit IN VARCHAR2
299 ) RETURN BOOLEAN AS
300
301 /*************************************************************
302 Created By : amuthu
303 Date Created On : 16-May-2000
304 Purpose :
305 Know limitations, enhancements or remarks
306 Change History
307 Who When What
308
309 (reverse chronological order - newest change first)
310 ***************************************************************/
311
312 CURSOR cur_rowid IS
313 SELECT rowid
314 FROM igs_ad_term_unitdtls
315 WHERE unit = x_unit
316 AND term_details_id = x_term_details_id and ((l_rowid is null) or (rowid <> l_rowid))
317
318 ;
319 lv_rowid cur_rowid%RowType;
320
321 BEGIN
322
323 Open cur_rowid;
324 Fetch cur_rowid INTO lv_rowid;
325 IF (cur_rowid%FOUND) THEN
326 Close cur_rowid;
327 return (true);
328 ELSE
329 close cur_rowid;
330 return(false);
331 END IF;
332 END Get_UK_For_Validation ;
333
334 PROCEDURE Get_FK_Igs_Ad_Code_Classes (
335 x_code_id IN NUMBER
336 ) AS
337
338 /*************************************************************
339 Created By : Kamalakar N.
340 Date Created By : 15/May/2000
341 Purpose :
342 Know limitations, enhancements or remarks
343 Change History
344 Who When What
345
346 (reverse chronological order - newest change first)
347 ***************************************************************/
348
349 CURSOR cur_rowid IS
350 SELECT rowid
351 FROM igs_ad_term_unitdtls
352 WHERE unit_difficulty = x_code_id ;
353
354 lv_rowid cur_rowid%RowType;
355
356 BEGIN
357
358 Open cur_rowid;
359 Fetch cur_rowid INTO lv_rowid;
360 IF (cur_rowid%FOUND) THEN
361 Close cur_rowid;
362 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATUD_ACDC_FK');
363 IGS_GE_MSG_STACK.ADD;
364 App_Exception.Raise_Exception;
365 Return;
366 END IF;
367 Close cur_rowid;
368
369 END Get_FK_Igs_Ad_Code_Classes;
370
371 PROCEDURE Get_FK_Igs_Ad_Term_Details (
372 x_term_details_id IN NUMBER
373 ) AS
374
375 /*************************************************************
376 Created By : Kamalakar N.
377 Date Created By : 15/May/2000
378 Purpose :
379 Know limitations, enhancements or remarks
380 Change History
381 Who When What
382
383 (reverse chronological order - newest change first)
384 ***************************************************************/
385
386 CURSOR cur_rowid IS
387 SELECT rowid
388 FROM igs_ad_term_unitdtls
389 WHERE term_details_id = x_term_details_id ;
390
391 lv_rowid cur_rowid%RowType;
392
393 BEGIN
394
395 Open cur_rowid;
396 Fetch cur_rowid INTO lv_rowid;
397 IF (cur_rowid%FOUND) THEN
398 Close cur_rowid;
399 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATUD_ATD_FK');
400 IGS_GE_MSG_STACK.ADD;
401 App_Exception.Raise_Exception;
402 Return;
403 END IF;
404 Close cur_rowid;
405
406 END Get_FK_Igs_Ad_Term_Details;
407
408 PROCEDURE Check_Child_Existance AS
409 BEGIN
410 -- Next part of code has been added as per bug# 2401170
411 -- Start of new code.
412 IGS_AV_STND_UNIT_PKG.GET_FK_IGS_AD_TERM_UNITDTLS (
413 old_references.unit_details_id
414 );
415 IGS_AV_STND_UNIT_LVL_PKG.GET_FK_IGS_AD_TERM_UNITDTLS (
416 old_references.unit_details_id
417 );
418 -- End of new code. Bug# 2401170
419 END Check_Child_Existance;
420
421
422 PROCEDURE Before_DML (
423 p_action IN VARCHAR2,
424 x_rowid IN VARCHAR2 DEFAULT NULL,
425 x_unit_details_id IN NUMBER DEFAULT NULL,
426 x_term_details_id IN NUMBER DEFAULT NULL,
427 x_unit IN VARCHAR2 DEFAULT NULL,
428 x_unit_difficulty IN NUMBER DEFAULT NULL,
429 x_unit_name IN VARCHAR2 DEFAULT NULL,
430 x_cp_attempted IN NUMBER DEFAULT NULL,
431 x_cp_earned IN NUMBER DEFAULT NULL,
432 x_grade IN VARCHAR2 DEFAULT NULL,
433 x_unit_grade_points IN NUMBER DEFAULT NULL,
434 x_deg_aud_detail_id IN NUMBER DEFAULT NULL,
435 x_creation_date IN DATE DEFAULT NULL,
436 x_created_by IN NUMBER DEFAULT NULL,
437 x_last_update_date IN DATE DEFAULT NULL,
438 x_last_updated_by IN NUMBER DEFAULT NULL,
439 x_last_update_login IN NUMBER DEFAULT NULL
440 ) AS
441 /*************************************************************
442 Created By : Kamalakar N.
443 Date Created By : 15/May/2000
444 Purpose :
445 Know limitations, enhancements or remarks
446 Change History
447 Who When What
448
449 (reverse chronological order - newest change first)
450 ***************************************************************/
451
452 BEGIN
453
454 Set_Column_Values (
455 p_action,
456 x_rowid,
457 x_unit_details_id,
458 x_term_details_id,
459 x_unit,
460 x_unit_difficulty,
461 x_unit_name,
462 x_cp_attempted,
463 x_cp_earned,
464 x_grade,
465 x_unit_grade_points,
466 x_deg_aud_detail_id,
467 x_creation_date,
468 x_created_by,
469 x_last_update_date,
470 x_last_updated_by,
471 x_last_update_login
472 );
473
474 IF (p_action = 'INSERT') THEN
475 -- Call all the procedures related to Before Insert.
476 Null;
477 IF Get_Pk_For_Validation(
478 new_references.unit_details_id) THEN
479 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
480 IGS_GE_MSG_STACK.ADD;
481 App_Exception.Raise_Exception;
482 END IF;
483 Check_Constraints;
484 Check_Uniqueness;
485 Check_Parent_Existance;
486 Check_Status; --jchin Bug 4629226
487 ELSIF (p_action = 'UPDATE') THEN
488 -- Call all the procedures related to Before Update.
489 Null;
490 Check_Constraints;
491 Check_Uniqueness;
492 Check_Parent_Existance;
493 Check_Status; --jchin Bug 4629226
494 ELSIF (p_action = 'DELETE') THEN
495 -- Call all the procedures related to Before Delete.
496 Check_Child_Existance;
497
498
499 ELSIF (p_action = 'VALIDATE_INSERT') THEN
500 -- Call all the procedures related to Before Insert.
501 IF Get_PK_For_Validation (
502 new_references.unit_details_id) THEN
503 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
504 IGS_GE_MSG_STACK.ADD;
505 App_Exception.Raise_Exception;
506 END IF;
507 Check_Constraints;
508 Check_Uniqueness;
509 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
510 Check_Constraints;
511 Check_Uniqueness;
512 ELSIF (p_action = 'VALIDATE_DELETE') THEN
513 Check_Child_Existance;
514
515 END IF;
516
517 END Before_DML;
518
519 PROCEDURE After_DML (
520 p_action IN VARCHAR2,
521 x_rowid IN VARCHAR2
522 ) IS
523 /*************************************************************
524 Created By : Kamalakar N.
525 Date Created By : 15/May/2000
526 Purpose :
527 Know limitations, enhancements or remarks
528 Change History
529 Who When What
530
531 (reverse chronological order - newest change first)
532 ***************************************************************/
533
534 BEGIN
535
536 l_rowid := x_rowid;
537
538 IF (p_action = 'INSERT') THEN
539 -- Call all the procedures related to After Insert.
540 update_term_tab( x_term_id => NEW_REFERENCES.TERM_DETAILS_ID );
541 ELSIF (p_action = 'UPDATE') THEN
542 -- Call all the procedures related to After Update.
543 update_term_tab( x_term_id => NEW_REFERENCES.TERM_DETAILS_ID );
544 ELSIF (p_action = 'DELETE') THEN
545 -- Call all the procedures related to After Delete.
546 update_term_tab( x_term_id => OLD_REFERENCES.TERM_DETAILS_ID );
547 END IF;
548
549 l_rowid:=NULL;
550 END After_DML;
551
552 procedure INSERT_ROW (
553 X_ROWID in out NOCOPY VARCHAR2,
554 x_UNIT_DETAILS_ID IN OUT NOCOPY NUMBER,
555 x_TERM_DETAILS_ID IN NUMBER,
556 x_UNIT IN VARCHAR2,
557 x_UNIT_DIFFICULTY IN NUMBER,
558 x_UNIT_NAME IN VARCHAR2,
559 x_CP_ATTEMPTED IN NUMBER,
560 x_CP_EARNED IN NUMBER,
561 x_GRADE IN VARCHAR2,
562 x_UNIT_GRADE_POINTS IN NUMBER,
563 x_DEG_AUD_DETAIL_ID IN NUMBER DEFAULT NULL,
564 X_MODE in VARCHAR2 default 'R'
565 ) AS
566 /*************************************************************
567 Created By : Kamalakar N.
568 Date Created By : 15/May/2000
569 Purpose :
570 Know limitations, enhancements or remarks
571 Change History
572 Who When What
573
574 (reverse chronological order - newest change first)
575 ***************************************************************/
576
577 cursor C is select ROWID from IGS_AD_TERM_UNITDTLS
578 where UNIT_DETAILS_ID= X_UNIT_DETAILS_ID
579 ;
580 X_LAST_UPDATE_DATE DATE ;
581 X_LAST_UPDATED_BY NUMBER ;
582 X_LAST_UPDATE_LOGIN NUMBER ;
583 X_REQUEST_ID NUMBER;
584 X_PROGRAM_ID NUMBER;
585 X_PROGRAM_APPLICATION_ID NUMBER;
586 X_PROGRAM_UPDATE_DATE DATE;
587 begin
588 X_LAST_UPDATE_DATE := SYSDATE;
589 if(X_MODE = 'I') then
590 X_LAST_UPDATED_BY := 1;
591 X_LAST_UPDATE_LOGIN := 0;
592 elsif (X_MODE IN ('R', 'S')) then
593 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
594 if X_LAST_UPDATED_BY is NULL then
595 X_LAST_UPDATED_BY := -1;
596 end if;
597 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
598 if X_LAST_UPDATE_LOGIN is NULL then
599 X_LAST_UPDATE_LOGIN := -1;
600 end if;
601 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
602 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
603 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
604 if (X_REQUEST_ID = -1) then
605 X_REQUEST_ID := NULL;
606 X_PROGRAM_ID := NULL;
607 X_PROGRAM_APPLICATION_ID := NULL;
608 X_PROGRAM_UPDATE_DATE := NULL;
609 else
610 X_PROGRAM_UPDATE_DATE := SYSDATE;
611 end if;
612 else
613 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
614 IGS_GE_MSG_STACK.ADD;
615 app_exception.raise_exception;
616 end if;
617
618 X_UNIT_DETAILS_ID := -1;
619 Before_DML(
620 p_action=>'INSERT',
621 x_rowid=>X_ROWID,
622 x_unit_details_id=>X_UNIT_DETAILS_ID,
623 x_term_details_id=>X_TERM_DETAILS_ID,
624 x_unit=>X_UNIT,
625 x_unit_difficulty=>X_UNIT_DIFFICULTY,
626 x_unit_name=>X_UNIT_NAME,
627 x_cp_attempted=>X_CP_ATTEMPTED,
628 x_cp_earned=>X_CP_EARNED,
629 x_grade=>X_GRADE,
630 x_unit_grade_points=>X_UNIT_GRADE_POINTS,
631 x_deg_aud_detail_id => X_DEG_AUD_DETAIL_ID,
632 x_creation_date=>X_LAST_UPDATE_DATE,
633 x_created_by=>X_LAST_UPDATED_BY,
634 x_last_update_date=>X_LAST_UPDATE_DATE,
635 x_last_updated_by=>X_LAST_UPDATED_BY,
636 x_last_update_login=>X_LAST_UPDATE_LOGIN);
637
638 IF (x_mode = 'S') THEN
639 igs_sc_gen_001.set_ctx('R');
640 END IF;
641 insert into IGS_AD_TERM_UNITDTLS (
642 UNIT_DETAILS_ID
643 ,TERM_DETAILS_ID
644 ,UNIT
645 ,UNIT_DIFFICULTY
646 ,UNIT_NAME
647 ,CP_ATTEMPTED
648 ,CP_EARNED
649 ,GRADE
650 ,UNIT_GRADE_POINTS
651 ,DEG_AUD_DETAIL_ID
652 ,CREATION_DATE
653 ,CREATED_BY
654 ,LAST_UPDATE_DATE
655 ,LAST_UPDATED_BY
656 ,LAST_UPDATE_LOGIN
657 ,REQUEST_ID
658 ,PROGRAM_ID
659 ,PROGRAM_APPLICATION_ID
660 ,PROGRAM_UPDATE_DATE
661 ) values (
662 IGS_AD_TERM_UNITDTLS_S.NEXTVAL
663 ,NEW_REFERENCES.TERM_DETAILS_ID
664 ,NEW_REFERENCES.UNIT
665 ,NEW_REFERENCES.UNIT_DIFFICULTY
666 ,NEW_REFERENCES.UNIT_NAME
667 ,NEW_REFERENCES.CP_ATTEMPTED
668 ,NEW_REFERENCES.CP_EARNED
669 ,NEW_REFERENCES.GRADE
670 ,NEW_REFERENCES.UNIT_GRADE_POINTS
671 ,NEW_REFERENCES.DEG_AUD_DETAIL_ID
672 ,X_LAST_UPDATE_DATE
673 ,X_LAST_UPDATED_BY
674 ,X_LAST_UPDATE_DATE
675 ,X_LAST_UPDATED_BY
676 ,X_LAST_UPDATE_LOGIN
677 ,X_REQUEST_ID
678 ,X_PROGRAM_ID
679 ,X_PROGRAM_APPLICATION_ID
680 ,X_PROGRAM_UPDATE_DATE
681 )RETURNING UNIT_DETAILS_ID INTO X_UNIT_DETAILS_ID ;
682 IF (x_mode = 'S') THEN
683 igs_sc_gen_001.unset_ctx('R');
684 END IF;
685
686 open c;
687 fetch c into X_ROWID;
688 if (c%notfound) then
689 close c;
690 raise no_data_found;
691 end if;
692 close c;
693 After_DML (
694 p_action => 'INSERT' ,
695 x_rowid => X_ROWID );
696 EXCEPTION
697 WHEN OTHERS THEN
698 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
699 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
700 fnd_message.set_token ('ERR_CD', SQLCODE);
701 igs_ge_msg_stack.add;
702 igs_sc_gen_001.unset_ctx('R');
703 app_exception.raise_exception;
704 ELSE
705 igs_sc_gen_001.unset_ctx('R');
706 RAISE;
707 END IF;
708
709 end INSERT_ROW;
710 procedure LOCK_ROW (
711 X_ROWID in VARCHAR2,
712 x_UNIT_DETAILS_ID IN NUMBER,
713 x_TERM_DETAILS_ID IN NUMBER,
714 x_UNIT IN VARCHAR2,
715 x_UNIT_DIFFICULTY IN NUMBER,
716 x_UNIT_NAME IN VARCHAR2,
717 x_CP_ATTEMPTED IN NUMBER,
718 x_CP_EARNED IN NUMBER,
719 x_GRADE IN VARCHAR2,
720 x_UNIT_GRADE_POINTS IN NUMBER,
721 x_DEG_AUD_DETAIL_ID IN NUMBER DEFAULT NULL) AS
722 /*************************************************************
723 Created By : Kamalakar N.
724 Date Created By : 15/May/2000
725 Purpose :
726 Know limitations, enhancements or remarks
727 Change History
728 Who When What
729
730 (reverse chronological order - newest change first)
731 ***************************************************************/
732
733 cursor c1 is select
734 TERM_DETAILS_ID
735 , UNIT
736 , UNIT_DIFFICULTY
737 , UNIT_NAME
738 , CP_ATTEMPTED
739 , CP_EARNED
740 , GRADE
741 , UNIT_GRADE_POINTS
742 , DEG_AUD_DETAIL_ID
743 from IGS_AD_TERM_UNITDTLS
744 where ROWID = X_ROWID
745 for update nowait;
746 tlinfo c1%rowtype;
747 begin
748 open c1;
749 fetch c1 into tlinfo;
750 if (c1%notfound) then
751 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
752 IGS_GE_MSG_STACK.ADD;
753 close c1;
754 app_exception.raise_exception;
755 return;
756 end if;
757 close c1;
758 if ( ( tlinfo.TERM_DETAILS_ID = X_TERM_DETAILS_ID)
759 AND (tlinfo.UNIT = X_UNIT)
760 AND (tlinfo.UNIT_DIFFICULTY = X_UNIT_DIFFICULTY)
761 AND (tlinfo.UNIT_NAME = X_UNIT_NAME)
762 AND ((tlinfo.CP_ATTEMPTED = X_CP_ATTEMPTED)
763 OR ((tlinfo.CP_ATTEMPTED is null)
764 AND (X_CP_ATTEMPTED is null)))
765 AND ((tlinfo.CP_EARNED = X_CP_EARNED)
766 OR ((tlinfo.CP_EARNED is null)
767 AND (X_CP_EARNED is null)))
768 AND ((tlinfo.GRADE = X_GRADE)
769 OR ((tlinfo.GRADE is null)
770 AND (X_GRADE is null)))
771 AND ((tlinfo.UNIT_GRADE_POINTS = X_UNIT_GRADE_POINTS)
772 OR ((tlinfo.UNIT_GRADE_POINTS is null)
773 AND (X_UNIT_GRADE_POINTS is null)))
774 AND ((tlinfo.DEG_AUD_DETAIL_ID = X_DEG_AUD_DETAIL_ID)
775 OR ((tlinfo.DEG_AUD_DETAIL_ID is null)
776 AND (X_DEG_AUD_DETAIL_ID is null)))
777 ) then
778 null;
779 else
780 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
781 IGS_GE_MSG_STACK.ADD;
782 app_exception.raise_exception;
783 end if;
784 return;
785 end LOCK_ROW;
786 Procedure UPDATE_ROW (
787 X_ROWID in VARCHAR2,
788 x_UNIT_DETAILS_ID IN NUMBER,
789 x_TERM_DETAILS_ID IN NUMBER,
790 x_UNIT IN VARCHAR2,
791 x_UNIT_DIFFICULTY IN NUMBER,
792 x_UNIT_NAME IN VARCHAR2,
793 x_CP_ATTEMPTED IN NUMBER,
794 x_CP_EARNED IN NUMBER,
795 x_GRADE IN VARCHAR2,
796 x_UNIT_GRADE_POINTS IN NUMBER,
797 x_DEG_AUD_DETAIL_ID IN NUMBER DEFAULT NULL,
798 X_MODE in VARCHAR2 default 'R'
799 ) AS
800 /*************************************************************
801 Created By : Kamalakar N.
802 Date Created By : 15/May/2000
803 Purpose :
804 Know limitations, enhancements or remarks
805 Change History
806 Who When What
807
808 (reverse chronological order - newest change first)
809 ***************************************************************/
810
811 X_LAST_UPDATE_DATE DATE ;
812 X_LAST_UPDATED_BY NUMBER ;
813 X_LAST_UPDATE_LOGIN NUMBER ;
814 X_REQUEST_ID NUMBER;
815 X_PROGRAM_ID NUMBER;
816 X_PROGRAM_APPLICATION_ID NUMBER;
817 X_PROGRAM_UPDATE_DATE DATE;
818 begin
819 X_LAST_UPDATE_DATE := SYSDATE;
820 if(X_MODE = 'I') then
821 X_LAST_UPDATED_BY := 1;
822 X_LAST_UPDATE_LOGIN := 0;
823 elsif (X_MODE IN ('R', 'S')) then
824 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
825 if X_LAST_UPDATED_BY is NULL then
826 X_LAST_UPDATED_BY := -1;
827 end if;
828 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
829 if X_LAST_UPDATE_LOGIN is NULL then
830 X_LAST_UPDATE_LOGIN := -1;
831 end if;
832 else
833 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
834 IGS_GE_MSG_STACK.ADD;
835 app_exception.raise_exception;
836 end if;
837 Before_DML(
838 p_action=>'UPDATE',
839 x_rowid=>X_ROWID,
840 x_unit_details_id=>X_UNIT_DETAILS_ID,
841 x_term_details_id=>X_TERM_DETAILS_ID,
842 x_unit=>X_UNIT,
843 x_unit_difficulty=>X_UNIT_DIFFICULTY,
844 x_unit_name=>X_UNIT_NAME,
845 x_cp_attempted=>X_CP_ATTEMPTED,
846 x_cp_earned=>X_CP_EARNED,
847 x_grade=>X_GRADE,
848 x_unit_grade_points=>X_UNIT_GRADE_POINTS,
849 x_deg_aud_detail_id => X_DEG_AUD_DETAIL_ID,
850 x_creation_date=>X_LAST_UPDATE_DATE,
851 x_created_by=>X_LAST_UPDATED_BY,
852 x_last_update_date=>X_LAST_UPDATE_DATE,
853 x_last_updated_by=>X_LAST_UPDATED_BY,
854 x_last_update_login=>X_LAST_UPDATE_LOGIN);
855
856 if (X_MODE IN ('R', 'S')) then
857 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
858 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
859 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
860 if (X_REQUEST_ID = -1) then
861 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
862 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
863 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
864 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
865 else
866 X_PROGRAM_UPDATE_DATE := SYSDATE;
867 end if;
868 end if;
869
870 IF (x_mode = 'S') THEN
871 igs_sc_gen_001.set_ctx('R');
872 END IF;
873 UPDATE IGS_AD_TERM_UNITDTLS SET
874 TERM_DETAILS_ID = NEW_REFERENCES.TERM_DETAILS_ID,
875 UNIT = NEW_REFERENCES.UNIT,
876 UNIT_DIFFICULTY = NEW_REFERENCES.UNIT_DIFFICULTY,
877 UNIT_NAME = NEW_REFERENCES.UNIT_NAME,
878 CP_ATTEMPTED = NEW_REFERENCES.CP_ATTEMPTED,
879 CP_EARNED = NEW_REFERENCES.CP_EARNED,
880 GRADE = NEW_REFERENCES.GRADE,
881 UNIT_GRADE_POINTS = NEW_REFERENCES.UNIT_GRADE_POINTS,
882 DEG_AUD_DETAIL_ID = NEW_REFERENCES.DEG_AUD_DETAIL_ID,
883 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
884 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
885 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
886 REQUEST_ID = X_REQUEST_ID,
887 PROGRAM_ID = X_PROGRAM_ID,
888 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
889 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
890 where ROWID = X_ROWID;
891 if (sql%notfound) then
892 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
893 igs_ge_msg_stack.add;
894 igs_sc_gen_001.unset_ctx('R');
895 app_exception.raise_exception;
896 end if;
897 IF (x_mode = 'S') THEN
898 igs_sc_gen_001.unset_ctx('R');
899 END IF;
900
901 After_DML (
902 p_action => 'UPDATE' ,
903 x_rowid => X_ROWID
904 );
905 EXCEPTION
906 WHEN OTHERS THEN
907 IF (SQLCODE = (-28115)) THEN
908 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
909 fnd_message.set_token ('ERR_CD', SQLCODE);
910 igs_ge_msg_stack.add;
911 igs_sc_gen_001.unset_ctx('R');
912 app_exception.raise_exception;
913 ELSE
914 igs_sc_gen_001.unset_ctx('R');
915 RAISE;
916 END IF;
917
918 end UPDATE_ROW;
919 procedure ADD_ROW (
920 X_ROWID in out NOCOPY VARCHAR2,
921 x_UNIT_DETAILS_ID IN OUT NOCOPY NUMBER,
922 x_TERM_DETAILS_ID IN NUMBER,
923 x_UNIT IN VARCHAR2,
924 x_UNIT_DIFFICULTY IN NUMBER,
925 x_UNIT_NAME IN VARCHAR2,
926 x_CP_ATTEMPTED IN NUMBER,
927 x_CP_EARNED IN NUMBER,
928 x_GRADE IN VARCHAR2,
929 x_UNIT_GRADE_POINTS IN NUMBER,
930 x_DEG_AUD_DETAIL_ID IN NUMBER DEFAULT NULL,
931 X_MODE in VARCHAR2 default 'R'
932 ) AS
933 /*************************************************************
934 Created By : Kamalakar N.
935 Date Created By : 15/May/2000
936 Purpose :
937 Know limitations, enhancements or remarks
938 Change History
939 Who When What
940
941 (reverse chronological order - newest change first)
942 ***************************************************************/
943
944 cursor c1 is select ROWID from IGS_AD_TERM_UNITDTLS
945 where UNIT_DETAILS_ID= X_UNIT_DETAILS_ID
946 ;
947 begin
948 open c1;
949 fetch c1 into X_ROWID;
950 if (c1%notfound) then
951 close c1;
952 INSERT_ROW (
953 X_ROWID,
954 X_UNIT_DETAILS_ID,
955 X_TERM_DETAILS_ID,
956 X_UNIT,
957 X_UNIT_DIFFICULTY,
958 X_UNIT_NAME,
959 X_CP_ATTEMPTED,
960 X_CP_EARNED,
961 X_GRADE,
962 X_UNIT_GRADE_POINTS,
963 X_DEG_AUD_DETAIL_ID,
964 X_MODE );
965 return;
966 end if;
967 close c1;
968 UPDATE_ROW (
969 X_ROWID,
970 X_UNIT_DETAILS_ID,
971 X_TERM_DETAILS_ID,
972 X_UNIT,
973 X_UNIT_DIFFICULTY,
974 X_UNIT_NAME,
975 X_CP_ATTEMPTED,
976 X_CP_EARNED,
977 X_GRADE,
978 X_UNIT_GRADE_POINTS,
979 X_DEG_AUD_DETAIL_ID,
980 X_MODE );
981 end ADD_ROW;
982 procedure DELETE_ROW (
983 X_ROWID in VARCHAR2,
984 x_mode IN VARCHAR2
985 ) AS
986 /*************************************************************
987 Created By : Kamalakar N.
988 Date Created By : 15/May/2000
989 Purpose :
990 Know limitations, enhancements or remarks
991 Change History
992 Who When What
993
994 (reverse chronological order - newest change first)
995 ***************************************************************/
996
997 begin
998 Before_DML (
999 p_action => 'DELETE',
1000 x_rowid => X_ROWID
1001 );
1002 IF (x_mode = 'S') THEN
1003 igs_sc_gen_001.set_ctx('R');
1004 END IF;
1005 delete from IGS_AD_TERM_UNITDTLS
1006 where ROWID = X_ROWID;
1007 if (sql%notfound) then
1008 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1009 igs_ge_msg_stack.add;
1010 igs_sc_gen_001.unset_ctx('R');
1011 app_exception.raise_exception;
1012 end if;
1013 IF (x_mode = 'S') THEN
1014 igs_sc_gen_001.unset_ctx('R');
1015 END IF;
1016
1017 After_DML (
1018 p_action => 'DELETE',
1019 x_rowid => X_ROWID
1020 );
1021 end DELETE_ROW;
1022
1023 PROCEDURE update_term_tab(x_term_id IN NUMBER)
1024 AS
1025 /*************************************************************
1026 Created By : TRAY
1027 Date Created By : 18-JUN-2003
1028 Purpose : For updating term details table, build 2864699
1029 Know limitations, enhancements or remarks
1030 Change History
1031 Who When What
1032 akadam 31-jul-2003 Bug No:3003149 the calculation in for loop was not checking NULL values
1033 (reverse chronological order - newest change first)
1034 ***************************************************************/
1035 CURSOR c_get_data IS
1036 SELECT SUM(NVL(cp_attempted,0)) tcpa, SUM(NVL(cp_earned,0)) tcpe,SUM(NVL(unit_grade_points,0)) tugp
1037 FROM igs_ad_term_unitdtls
1038 WHERE term_details_id = x_term_id
1039 GROUP BY term_details_id;
1040
1041 l_cp_attempted_total igs_ad_term_details.total_cp_attempted%TYPE;
1042 l_cp_earned_total igs_ad_term_details.total_cp_earned%TYPE;
1043 l_unit_grade_points_total igs_ad_term_details.total_unit_gp%TYPE;
1044
1045 BEGIN
1046
1047 OPEN c_get_data;
1048 FETCH c_get_data INTO l_cp_attempted_total,l_cp_earned_total,l_unit_grade_points_total;
1049 CLOSE c_get_data;
1050
1051 UPDATE igs_ad_term_details SET total_cp_attempted=l_cp_attempted_total
1052 ,total_cp_earned=l_cp_earned_total
1053 ,total_unit_gp=l_unit_grade_points_total
1054 WHERE term_details_id = x_term_id ;
1055 END update_term_tab;
1056
1057
1058
1059
1060 END igs_ad_term_unitdtls_pkg;
[Home] [Help]