1 PACKAGE BODY igs_ad_term_details_pkg AS
2 /* $Header: IGSAI83B.pls 120.2 2005/10/01 21:47:34 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_term_details%RowType;
5 new_references igs_ad_term_details%RowType;
6
7 PROCEDURE Check_Status AS
8 /*************************************************************
9 Created By : jchin
10 Date Created By : 2005/09/29
11 Purpose : To check whether the associated academic record is
12 INACTIVE and if so, throw an error
13 Know limitations, enhancements or remarks
14 Change History
15 Who When What
16
17 (reverse chronological order - newest change first)
18 ***************************************************************/
19
20 CURSOR check_status(cp_transcript_id IN NUMBER) IS
21 SELECT DISTINCT 1
22 FROM igs_ad_acad_history_v hist, igs_ad_transcript_v trans
23 WHERE hist.education_id = trans.education_id
24 AND trans.transcript_id = cp_transcript_id
25 AND hist.status = 'I';
26
27 l_temp NUMBER;
28
29 BEGIN
30
31 l_temp := null;
32
33 OPEN check_status(new_references.transcript_id);
34 FETCH check_status INTO l_temp;
35 CLOSE check_status;
36
37 IF l_temp IS NOT NULL THEN
38
39 Fnd_message.Set_Name('IGS', 'IGS_AD_INACTIVE_ACAD_HIST');
40 IGS_GE_MSG_STACK.ADD;
41 app_exception.Raise_Exception;
42
43 END IF;
44
45 END Check_Status;
46
47 PROCEDURE Set_Column_Values (
48 p_action IN VARCHAR2,
49 x_rowid IN VARCHAR2 DEFAULT NULL,
50 x_term_details_id IN NUMBER DEFAULT NULL,
51 x_transcript_id IN NUMBER DEFAULT NULL,
52 x_term IN VARCHAR2 DEFAULT NULL,
53 x_start_date IN DATE DEFAULT NULL,
54 x_end_date IN DATE DEFAULT NULL,
55 x_total_cp_attempted IN NUMBER DEFAULT NULL,
56 x_total_cp_earned IN NUMBER DEFAULT NULL,
57 x_total_unit_gp IN NUMBER DEFAULT NULL,
58 x_total_gpa_units IN NUMBER DEFAULT NULL,
59 x_gpa IN VARCHAR2 DEFAULT NULL,
60 x_creation_date IN DATE DEFAULT NULL,
61 x_created_by IN NUMBER DEFAULT NULL,
62 x_last_update_date IN DATE DEFAULT NULL,
63 x_last_updated_by IN NUMBER DEFAULT NULL,
64 x_last_update_login IN NUMBER DEFAULT NULL
65 ) AS
66
67 /*************************************************************
68 Created By : knaraset.in
69 Date Created By : 2000/05/16
70 Purpose :
71 Know limitations, enhancements or remarks
72 Change History
73 Who When What
74
75 (reverse chronological order - newest change first)
76 ***************************************************************/
77
78 CURSOR cur_old_ref_values IS
79 SELECT *
80 FROM IGS_AD_TERM_DETAILS
81 WHERE rowid = x_rowid;
82
83 BEGIN
84
85 l_rowid := x_rowid;
86
87 -- Code for setting the Old and New Reference Values.
88 -- Populate Old Values.
89 Open cur_old_ref_values;
90 Fetch cur_old_ref_values INTO old_references;
91 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
92 Close cur_old_ref_values;
93 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
94 IGS_GE_MSG_STACK.ADD;
95 App_Exception.Raise_Exception;
96 Return;
97 END IF;
98 Close cur_old_ref_values;
99
100 -- Populate New Values.
101 new_references.term_details_id := x_term_details_id;
102 new_references.transcript_id := x_transcript_id;
103 new_references.term := x_term;
104 new_references.start_date := TRUNC(x_start_date);
105 new_references.end_date := TRUNC(x_end_date);
106 new_references.total_cp_attempted := x_total_cp_attempted;
107 new_references.total_cp_earned := x_total_cp_earned;
108 new_references.total_unit_gp := x_total_unit_gp;
109 new_references.total_gpa_units := x_total_gpa_units;
110 new_references.gpa := x_gpa;
111 IF (p_action = 'UPDATE') THEN
112 new_references.creation_date := old_references.creation_date;
113 new_references.created_by := old_references.created_by;
114 ELSE
115 new_references.creation_date := x_creation_date;
116 new_references.created_by := x_created_by;
117 END IF;
118 new_references.last_update_date := x_last_update_date;
119 new_references.last_updated_by := x_last_updated_by;
120 new_references.last_update_login := x_last_update_login;
121
122 END Set_Column_Values;
123
124
125 PROCEDURE Check_Constraints (
126 Column_Name IN VARCHAR2 DEFAULT NULL,
127 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
128 /*************************************************************
129 Created By : knaraset.in
130 Date Created By : 2000/05/16
131 Purpose :
132 Know limitations, enhancements or remarks
133 Change History
134 Who When What
135
136 (reverse chronological order - newest change first)
137 ***************************************************************/
138
139 BEGIN
140
141 IF column_name IS NULL THEN
142 NULL;
143 ELSIF UPPER(column_name) = 'TOTAL_CP_EARNED' THEN
144 new_references.total_cp_earned := IGS_GE_NUMBER.TO_NUM(column_value);
145 ELSIF UPPER(column_name) = 'TOTAL_GPA_UNITS' THEN
146 new_references.total_gpa_units := IGS_GE_NUMBER.TO_NUM(column_value);
147 ELSIF UPPER(column_name) = 'TOTAL_UNIT_GP' THEN
148 new_references.total_unit_gp := IGS_GE_NUMBER.TO_NUM(column_value);
149 ELSIF UPPER(column_name) = 'START_DATE' THEN
150 new_references.start_date := IGS_GE_DATE.IGSDATE(column_value);
151 ELSIF UPPER(column_name) = 'END_DATE' THEN
152 new_references.end_date := IGS_GE_DATE.IGSDATE(column_value);
153 ELSIF UPPER(column_name) = 'TOTAL_CP_ATTEMPTED' THEN
154 new_references.total_cp_attempted := IGS_GE_NUMBER.TO_NUM(column_value);
155 NULL;
156 END IF;
157
158
159
160 -- The following code checks for check constraints on the Columns.
161 IF Upper(Column_Name) = 'TOTAL_CP_EARNED' OR
162 Column_Name IS NULL THEN
163 IF NOT (new_references.total_cp_earned >=0) THEN
164 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
165 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TOTAL_CP_EARNED'));
166 IGS_GE_MSG_STACK.ADD;
167 App_Exception.Raise_Exception;
168 END IF;
169 END IF;
170
171 -- The following code checks for check constraints on the Columns.
172 IF Upper(Column_Name) = 'TOTAL_GPA_UNITS' OR
173 Column_Name IS NULL THEN
174 IF NOT (new_references.total_gpa_units >=0) THEN
175 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
176 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TOTAL_GPA_UNITS'));
177 IGS_GE_MSG_STACK.ADD;
178 App_Exception.Raise_Exception;
179 END IF;
180 END IF;
181
182 -- The following code checks for check constraints on the Columns.
183 IF Upper(Column_Name) = 'TOTAL_UNIT_GP' OR
184 Column_Name IS NULL THEN
185 IF NOT (new_references.total_unit_gp >= 0) THEN
186 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
187 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TOTAL_UNIT_GP'));
188 IGS_GE_MSG_STACK.ADD;
189 App_Exception.Raise_Exception;
190 END IF;
191 END IF;
192
193 -- The following code checks for check constraints on the Columns.
194 IF Upper(Column_Name) = 'END_DATE' OR
195 Column_Name IS NULL THEN
196 IF ( NOT (new_references.end_date > new_references.start_date) OR new_references.start_date > SYSDATE ) THEN
197 FND_MESSAGE.SET_NAME('IGS','IGS_AD_ST_DT_ED_DT');
198 IGS_GE_MSG_STACK.ADD;
199 App_Exception.Raise_Exception;
200 END IF;
201 END IF;
202
203 -- The following code checks for check constraints on the Columns.
204 IF Upper(Column_Name) = 'TOTAL_CP_ATTEMPTED' OR
205 Column_Name IS NULL THEN
206 IF NOT (new_references.total_cp_attempted >=0) THEN
207 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
208 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TOTAL_CP_ATTEMPTED'));
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 END IF;
212 END IF;
213
214
215 END Check_Constraints;
216
217 PROCEDURE Check_Parent_Existance AS
218 /*************************************************************
219 Created By : knaraset.in
220 Date Created By : 2000/05/16
221 Purpose :
222 Know limitations, enhancements or remarks
223 Change History
224 Who When What
225
226 (reverse chronological order - newest change first)
227 ***************************************************************/
228
229 BEGIN
230
231 IF (((old_references.transcript_id = new_references.transcript_id)) OR
232 ((new_references.transcript_id IS NULL))) THEN
233 NULL;
234 ELSIF NOT Igs_Ad_Transcript_Pkg.Get_PK_For_Validation (
235 new_references.transcript_id
236 ) THEN
237 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
238 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TRANSCRIPT'));
239 IGS_GE_MSG_STACK.ADD;
240 App_Exception.Raise_Exception;
241 END IF;
242
243 END Check_Parent_Existance;
244
245 PROCEDURE Check_Child_Existance IS
246 /*************************************************************
247 Created By : knaraset.in
248 Date Created By : 2000/05/16
249 Purpose :
250 Know limitations, enhancements or remarks
251 Change History
252 Who When What
253
254 (reverse chronological order - newest change first)
255 ***************************************************************/
256
257 BEGIN
258
259 Igs_Ad_Term_Unitdtls_Pkg.Get_FK_Igs_Ad_Term_Details (
260 old_references.term_details_id
261 );
262
263 END Check_Child_Existance;
264
265 FUNCTION Get_PK_For_Validation (
266 x_term_details_id IN NUMBER
267 ) RETURN BOOLEAN AS
268
269 /*************************************************************
270 Created By : knaraset.in
271 Date Created By : 2000/05/16
272 Purpose :
273 Know limitations, enhancements or remarks
274 Change History
275 Who When What
276
277 (reverse chronological order - newest change first)
278 ***************************************************************/
279
280 CURSOR cur_rowid IS
281 SELECT rowid
282 FROM igs_ad_term_details
283 WHERE term_details_id = x_term_details_id
284 FOR UPDATE NOWAIT;
285
286 lv_rowid cur_rowid%RowType;
287
288 BEGIN
289
290 Open cur_rowid;
291 Fetch cur_rowid INTO lv_rowid;
292 IF (cur_rowid%FOUND) THEN
293 Close cur_rowid;
294 Return(TRUE);
295 ELSE
296 Close cur_rowid;
297 Return(FALSE);
298 END IF;
299 END Get_PK_For_Validation;
300
301 PROCEDURE Get_FK_Igs_Ad_Transcript (
302 x_transcript_id IN NUMBER
303 ) AS
304
305 /*************************************************************
306 Created By : knaraset.in
307 Date Created By : 2000/05/16
308 Purpose :
309 Know limitations, enhancements or remarks
310 Change History
311 Who When What
312
313 (reverse chronological order - newest change first)
314 ***************************************************************/
315
316 CURSOR cur_rowid IS
317 SELECT rowid
318 FROM igs_ad_term_details
319 WHERE transcript_id = x_transcript_id ;
320
321 lv_rowid cur_rowid%RowType;
322
323 BEGIN
324
325 Open cur_rowid;
326 Fetch cur_rowid INTO lv_rowid;
327 IF (cur_rowid%FOUND) THEN
328 Close cur_rowid;
329 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATD_ATRN_FK');
330 IGS_GE_MSG_STACK.ADD;
331 App_Exception.Raise_Exception;
332 Return;
333 END IF;
334 Close cur_rowid;
335
336 END Get_FK_Igs_Ad_Transcript;
337
338 PROCEDURE Before_DML (
339 p_action IN VARCHAR2,
340 x_rowid IN VARCHAR2 DEFAULT NULL,
341 x_term_details_id IN NUMBER DEFAULT NULL,
342 x_transcript_id IN NUMBER DEFAULT NULL,
343 x_term IN VARCHAR2 DEFAULT NULL,
344 x_start_date IN DATE DEFAULT NULL,
345 x_end_date IN DATE DEFAULT NULL,
346 x_total_cp_attempted IN NUMBER DEFAULT NULL,
347 x_total_cp_earned IN NUMBER DEFAULT NULL,
348 x_total_unit_gp IN NUMBER DEFAULT NULL,
349 x_total_gpa_units IN NUMBER DEFAULT NULL,
350 x_gpa IN VARCHAR2 DEFAULT NULL,
351 x_creation_date IN DATE DEFAULT NULL,
352 x_created_by IN NUMBER DEFAULT NULL,
353 x_last_update_date IN DATE DEFAULT NULL,
354 x_last_updated_by IN NUMBER DEFAULT NULL,
355 x_last_update_login IN NUMBER DEFAULT NULL
356 ) AS
357 /*************************************************************
358 Created By : knaraset.in
359 Date Created By : 2000/05/16
360 Purpose :
361 Know limitations, enhancements or remarks
362 Change History
363 Who When What
364
365 (reverse chronological order - newest change first)
366 ***************************************************************/
367
368 BEGIN
369
373 x_term_details_id,
370 Set_Column_Values (
371 p_action,
372 x_rowid,
374 x_transcript_id,
375 x_term,
376 x_start_date,
377 x_end_date,
378 x_total_cp_attempted,
379 x_total_cp_earned,
380 x_total_unit_gp,
381 x_total_gpa_units,
382 x_gpa,
383 x_creation_date,
384 x_created_by,
385 x_last_update_date,
386 x_last_updated_by,
387 x_last_update_login
388 );
389
390 IF (p_action = 'INSERT') THEN
391 -- Call all the procedures related to Before Insert.
392 Null;
393 IF Get_Pk_For_Validation(
394 new_references.term_details_id) THEN
395 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
396 IGS_GE_MSG_STACK.ADD;
397 App_Exception.Raise_Exception;
398 END IF;
399 Check_Constraints;
400 Check_Parent_Existance;
401 Check_Status; --jchin Bug 4629226
402 ELSIF (p_action = 'UPDATE') THEN
403 -- Call all the procedures related to Before Update.
404 Null;
405 Check_Constraints;
406 Check_Parent_Existance;
407 Check_Status; --jchin Bug 4629226
408 ELSIF (p_action = 'DELETE') THEN
409 -- Call all the procedures related to Before Delete.
410 Null;
411 Check_Child_Existance;
412 ELSIF (p_action = 'VALIDATE_INSERT') THEN
413 -- Call all the procedures related to Before Insert.
414 IF Get_PK_For_Validation (
415 new_references.term_details_id) THEN
416 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
417 IGS_GE_MSG_STACK.ADD;
418 App_Exception.Raise_Exception;
419 END IF;
420 Check_Constraints;
421 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
422 Check_Constraints;
423 ELSIF (p_action = 'VALIDATE_DELETE') THEN
424 Check_Child_Existance;
425 END IF;
426
427 END Before_DML;
428
429 PROCEDURE After_DML (
430 p_action IN VARCHAR2,
431 x_rowid IN VARCHAR2
432 ) IS
433 /*************************************************************
434 Created By : knaraset.in
435 Date Created By : 2000/05/16
436 Purpose :
437 Know limitations, enhancements or remarks
438 Change History
439 Who When What
440
441 (reverse chronological order - newest change first)
442 ***************************************************************/
443
444 BEGIN
445
446 l_rowid := x_rowid;
447
448 IF (p_action = 'INSERT') THEN
449 -- Call all the procedures related to After Insert.
450 Null;
451 ELSIF (p_action = 'UPDATE') THEN
452 -- Call all the procedures related to After Update.
453 Null;
454 ELSIF (p_action = 'DELETE') THEN
455 -- Call all the procedures related to After Delete.
456 Null;
457 END IF;
458
459 l_rowid:=NULL;
460 END After_DML;
461
462 procedure INSERT_ROW (
463 X_ROWID in out NOCOPY VARCHAR2,
464 x_TERM_DETAILS_ID IN OUT NOCOPY NUMBER,
465 x_TRANSCRIPT_ID IN NUMBER,
466 x_TERM IN VARCHAR2,
467 x_START_DATE IN DATE,
468 x_END_DATE IN DATE,
469 x_TOTAL_CP_ATTEMPTED IN NUMBER,
470 x_TOTAL_CP_EARNED IN NUMBER,
471 x_TOTAL_UNIT_GP IN NUMBER,
472 x_TOTAL_GPA_UNITS IN NUMBER,
473 x_GPA IN VARCHAR2,
474 X_MODE in VARCHAR2 default 'R'
475 ) AS
476 /*************************************************************
477 Created By : knaraset.in
478 Date Created By : 2000/05/16
479 Purpose :
480 Know limitations, enhancements or remarks
481 Change History
482 Who When What
483
484 (reverse chronological order - newest change first)
485 ***************************************************************/
486
487 cursor C is select ROWID from IGS_AD_TERM_DETAILS
488 where TERM_DETAILS_ID= X_TERM_DETAILS_ID
489 ;
490 X_LAST_UPDATE_DATE DATE ;
491 X_LAST_UPDATED_BY NUMBER ;
492 X_LAST_UPDATE_LOGIN NUMBER ;
493 X_REQUEST_ID NUMBER;
494 X_PROGRAM_ID NUMBER;
495 X_PROGRAM_APPLICATION_ID NUMBER;
496 X_PROGRAM_UPDATE_DATE DATE;
497 begin
498 X_LAST_UPDATE_DATE := SYSDATE;
499 if(X_MODE = 'I') then
500 X_LAST_UPDATED_BY := 1;
501 X_LAST_UPDATE_LOGIN := 0;
502 elsif (X_MODE IN ('R', 'S')) then
503 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
504 if X_LAST_UPDATED_BY is NULL then
505 X_LAST_UPDATED_BY := -1;
506 end if;
507 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
508 if X_LAST_UPDATE_LOGIN is NULL then
509 X_LAST_UPDATE_LOGIN := -1;
510 end if;
511 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
512 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
513 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
514 if (X_REQUEST_ID = -1) then
515 X_REQUEST_ID := NULL;
516 X_PROGRAM_ID := NULL;
520 X_PROGRAM_UPDATE_DATE := SYSDATE;
517 X_PROGRAM_APPLICATION_ID := NULL;
518 X_PROGRAM_UPDATE_DATE := NULL;
519 else
521 end if;
522 else
523 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
524 IGS_GE_MSG_STACK.ADD;
525 app_exception.raise_exception;
526 end if;
527
528 X_TERM_DETAILS_ID := -1;
529 Before_DML(
530 p_action=>'INSERT',
531 x_rowid=>X_ROWID,
532 x_term_details_id=>X_TERM_DETAILS_ID,
533 x_transcript_id=>X_TRANSCRIPT_ID,
534 x_term=>X_TERM,
535 x_start_date=>X_START_DATE,
536 x_end_date=>X_END_DATE,
537 x_total_cp_attempted=>X_TOTAL_CP_ATTEMPTED,
538 x_total_cp_earned=>X_TOTAL_CP_EARNED,
539 x_total_unit_gp=>X_TOTAL_UNIT_GP,
540 x_total_gpa_units=>X_TOTAL_GPA_UNITS,
541 x_gpa=>X_GPA,
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 IF (x_mode = 'S') THEN
548 igs_sc_gen_001.set_ctx('R');
549 END IF;
550 insert into IGS_AD_TERM_DETAILS (
551 TERM_DETAILS_ID
552 ,TRANSCRIPT_ID
553 ,TERM
554 ,START_DATE
555 ,END_DATE
556 ,TOTAL_CP_ATTEMPTED
557 ,TOTAL_CP_EARNED
558 ,TOTAL_UNIT_GP
559 ,TOTAL_GPA_UNITS
560 ,GPA
561 ,CREATION_DATE
562 ,CREATED_BY
563 ,LAST_UPDATE_DATE
564 ,LAST_UPDATED_BY
565 ,LAST_UPDATE_LOGIN
566 ,REQUEST_ID
567 ,PROGRAM_ID
568 ,PROGRAM_APPLICATION_ID
569 ,PROGRAM_UPDATE_DATE
570 ) values (
571 IGS_AD_TERM_DETAILS_S.NEXTVAL
572 ,NEW_REFERENCES.TRANSCRIPT_ID
573 ,NEW_REFERENCES.TERM
574 ,NEW_REFERENCES.START_DATE
575 ,NEW_REFERENCES.END_DATE
576 ,NEW_REFERENCES.TOTAL_CP_ATTEMPTED
577 ,NEW_REFERENCES.TOTAL_CP_EARNED
578 ,NEW_REFERENCES.TOTAL_UNIT_GP
579 ,NEW_REFERENCES.TOTAL_GPA_UNITS
580 ,NEW_REFERENCES.GPA
581 ,X_LAST_UPDATE_DATE
582 ,X_LAST_UPDATED_BY
583 ,X_LAST_UPDATE_DATE
584 ,X_LAST_UPDATED_BY
585 ,X_LAST_UPDATE_LOGIN
586 ,X_REQUEST_ID
587 ,X_PROGRAM_ID
588 ,X_PROGRAM_APPLICATION_ID
589 ,X_PROGRAM_UPDATE_DATE
590 )RETURNING TERM_DETAILS_ID INTO X_TERM_DETAILS_ID;
591 IF (x_mode = 'S') THEN
592 igs_sc_gen_001.unset_ctx('R');
593 END IF;
594
595 open c;
596 fetch c into X_ROWID;
597 if (c%notfound) then
598 close c;
599 raise no_data_found;
600 end if;
601 close c;
602 After_DML (
603 p_action => 'INSERT' ,
604 x_rowid => X_ROWID );
605 EXCEPTION
606 WHEN OTHERS THEN
607 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
608 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
609 fnd_message.set_token ('ERR_CD', SQLCODE);
610 igs_ge_msg_stack.add;
611 igs_sc_gen_001.unset_ctx('R');
612 app_exception.raise_exception;
613 ELSE
614 igs_sc_gen_001.unset_ctx('R');
615 RAISE;
616 END IF;
617
618 end INSERT_ROW;
619 procedure LOCK_ROW (
620 X_ROWID in VARCHAR2,
621 x_TERM_DETAILS_ID IN NUMBER,
622 x_TRANSCRIPT_ID IN NUMBER,
623 x_TERM IN VARCHAR2,
624 x_START_DATE IN DATE,
625 x_END_DATE IN DATE,
626 x_TOTAL_CP_ATTEMPTED IN NUMBER,
627 x_TOTAL_CP_EARNED IN NUMBER,
628 x_TOTAL_UNIT_GP IN NUMBER,
629 x_TOTAL_GPA_UNITS IN NUMBER,
630 x_GPA IN VARCHAR2 ) AS
631 /*************************************************************
632 Created By : knaraset.in
633 Date Created By : 2000/05/16
634 Purpose :
635 Know limitations, enhancements or remarks
636 Change History
637 Who When What
638
639 (reverse chronological order - newest change first)
640 ***************************************************************/
641
642 cursor c1 is select
643 TRANSCRIPT_ID
644 , TERM
645 , START_DATE
646 , END_DATE
647 , TOTAL_CP_ATTEMPTED
648 , TOTAL_CP_EARNED
649 , TOTAL_UNIT_GP
650 , TOTAL_GPA_UNITS
651 , GPA
652 from IGS_AD_TERM_DETAILS
653 where ROWID = X_ROWID
654 for update nowait;
655 tlinfo c1%rowtype;
656 begin
657 open c1;
658 fetch c1 into tlinfo;
659 if (c1%notfound) then
660 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
664 return;
661 IGS_GE_MSG_STACK.ADD;
662 close c1;
663 app_exception.raise_exception;
665 end if;
666 close c1;
667 if ( ( tlinfo.TRANSCRIPT_ID = X_TRANSCRIPT_ID)
668 AND (tlinfo.TERM = X_TERM)
669 AND (TRUNC(tlinfo.START_DATE) = TRUNC(X_START_DATE))
670 AND (TRUNC(tlinfo.END_DATE) = TRUNC(X_END_DATE))
671 AND ((tlinfo.TOTAL_CP_ATTEMPTED = X_TOTAL_CP_ATTEMPTED)
672 OR ((tlinfo.TOTAL_CP_ATTEMPTED is null)
673 AND (X_TOTAL_CP_ATTEMPTED is null)))
674 AND ((tlinfo.TOTAL_CP_EARNED = X_TOTAL_CP_EARNED)
675 OR ((tlinfo.TOTAL_CP_EARNED is null)
676 AND (X_TOTAL_CP_EARNED is null)))
677 AND ((tlinfo.TOTAL_UNIT_GP = X_TOTAL_UNIT_GP)
678 OR ((tlinfo.TOTAL_UNIT_GP is null)
679 AND (X_TOTAL_UNIT_GP is null)))
680 AND ((tlinfo.TOTAL_GPA_UNITS = X_TOTAL_GPA_UNITS)
681 OR ((tlinfo.TOTAL_GPA_UNITS is null)
682 AND (X_TOTAL_GPA_UNITS is null)))
683 AND ((tlinfo.GPA = X_GPA)
684 OR ((tlinfo.GPA is null)
685 AND (X_GPA is null)))
686 ) then
687 null;
688 else
689 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
690 IGS_GE_MSG_STACK.ADD;
691 app_exception.raise_exception;
692 end if;
693 return;
694 end LOCK_ROW;
695 Procedure UPDATE_ROW (
696 X_ROWID in VARCHAR2,
697 x_TERM_DETAILS_ID IN NUMBER,
698 x_TRANSCRIPT_ID IN NUMBER,
699 x_TERM IN VARCHAR2,
700 x_START_DATE IN DATE,
701 x_END_DATE IN DATE,
702 x_TOTAL_CP_ATTEMPTED IN NUMBER,
703 x_TOTAL_CP_EARNED IN NUMBER,
704 x_TOTAL_UNIT_GP IN NUMBER,
705 x_TOTAL_GPA_UNITS IN NUMBER,
706 x_GPA IN VARCHAR2,
707 X_MODE in VARCHAR2 default 'R'
708 ) AS
709 /*************************************************************
710 Created By : knaraset.in
711 Date Created By : 2000/05/16
712 Purpose :
713 Know limitations, enhancements or remarks
714 Change History
715 Who When What
716
717 (reverse chronological order - newest change first)
718 ***************************************************************/
719
720 X_LAST_UPDATE_DATE DATE ;
721 X_LAST_UPDATED_BY NUMBER ;
722 X_LAST_UPDATE_LOGIN NUMBER ;
723 X_REQUEST_ID NUMBER;
724 X_PROGRAM_ID NUMBER;
725 X_PROGRAM_APPLICATION_ID NUMBER;
726 X_PROGRAM_UPDATE_DATE DATE;
727 begin
728 X_LAST_UPDATE_DATE := SYSDATE;
729 if(X_MODE = 'I') then
730 X_LAST_UPDATED_BY := 1;
731 X_LAST_UPDATE_LOGIN := 0;
732 elsif (X_MODE IN ('R', 'S')) then
733 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
734 if X_LAST_UPDATED_BY is NULL then
735 X_LAST_UPDATED_BY := -1;
736 end if;
737 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
738 if X_LAST_UPDATE_LOGIN is NULL then
739 X_LAST_UPDATE_LOGIN := -1;
740 end if;
741 else
742 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
743 IGS_GE_MSG_STACK.ADD;
744 app_exception.raise_exception;
745 end if;
746 Before_DML(
747 p_action=>'UPDATE',
748 x_rowid=>X_ROWID,
749 x_term_details_id=>X_TERM_DETAILS_ID,
750 x_transcript_id=>X_TRANSCRIPT_ID,
751 x_term=>X_TERM,
752 x_start_date=>X_START_DATE,
753 x_end_date=>X_END_DATE,
754 x_total_cp_attempted=>X_TOTAL_CP_ATTEMPTED,
755 x_total_cp_earned=>X_TOTAL_CP_EARNED,
756 x_total_unit_gp=>X_TOTAL_UNIT_GP,
757 x_total_gpa_units=>X_TOTAL_GPA_UNITS,
758 x_gpa=>X_GPA,
759 x_creation_date=>X_LAST_UPDATE_DATE,
760 x_created_by=>X_LAST_UPDATED_BY,
761 x_last_update_date=>X_LAST_UPDATE_DATE,
762 x_last_updated_by=>X_LAST_UPDATED_BY,
763 x_last_update_login=>X_LAST_UPDATE_LOGIN);
764
765 if (X_MODE IN ('R', 'S')) then
766 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
767 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
768 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
769 if (X_REQUEST_ID = -1) then
770 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
771 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
772 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
773 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
774 else
775 X_PROGRAM_UPDATE_DATE := SYSDATE;
776 end if;
777 end if;
778
779 IF (x_mode = 'S') THEN
780 igs_sc_gen_001.set_ctx('R');
781 END IF;
782 update IGS_AD_TERM_DETAILS set
783 TRANSCRIPT_ID = NEW_REFERENCES.TRANSCRIPT_ID,
784 TERM = NEW_REFERENCES.TERM,
785 START_DATE = NEW_REFERENCES.START_DATE,
786 END_DATE = NEW_REFERENCES.END_DATE,
787 TOTAL_CP_ATTEMPTED = NEW_REFERENCES.TOTAL_CP_ATTEMPTED,
788 TOTAL_CP_EARNED = NEW_REFERENCES.TOTAL_CP_EARNED,
789 TOTAL_UNIT_GP = NEW_REFERENCES.TOTAL_UNIT_GP,
790 TOTAL_GPA_UNITS = NEW_REFERENCES.TOTAL_GPA_UNITS,
791 GPA = NEW_REFERENCES.GPA,
792 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
793 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
794 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
795 , REQUEST_ID = X_REQUEST_ID,
796 PROGRAM_ID = X_PROGRAM_ID,
797 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
798 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
799 where ROWID = X_ROWID;
800 if (sql%notfound) then
801 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
802 igs_ge_msg_stack.add;
803 igs_sc_gen_001.unset_ctx('R');
804 app_exception.raise_exception;
805 end if;
806 IF (x_mode = 'S') THEN
807 igs_sc_gen_001.unset_ctx('R');
808 END IF;
809
810
811 After_DML (
812 p_action => 'UPDATE' ,
813 x_rowid => X_ROWID
814 );
815 EXCEPTION
816 WHEN OTHERS THEN
817 IF (SQLCODE = (-28115)) THEN
818 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
819 fnd_message.set_token ('ERR_CD', SQLCODE);
820 igs_ge_msg_stack.add;
821 igs_sc_gen_001.unset_ctx('R');
822 app_exception.raise_exception;
823 ELSE
824 igs_sc_gen_001.unset_ctx('R');
825 RAISE;
826 END IF;
827
828 end UPDATE_ROW;
829 procedure ADD_ROW (
830 X_ROWID in out NOCOPY VARCHAR2,
831 x_TERM_DETAILS_ID IN OUT NOCOPY NUMBER,
832 x_TRANSCRIPT_ID IN NUMBER,
833 x_TERM IN VARCHAR2,
834 x_START_DATE IN DATE,
835 x_END_DATE IN DATE,
836 x_TOTAL_CP_ATTEMPTED IN NUMBER,
837 x_TOTAL_CP_EARNED IN NUMBER,
838 x_TOTAL_UNIT_GP IN NUMBER,
839 x_TOTAL_GPA_UNITS IN NUMBER,
840 x_GPA IN VARCHAR2,
841 X_MODE in VARCHAR2 default 'R'
842 ) AS
843 /*************************************************************
844 Created By : knaraset.in
845 Date Created By : 2000/05/16
846 Purpose :
847 Know limitations, enhancements or remarks
848 Change History
849 Who When What
850
851 (reverse chronological order - newest change first)
852 ***************************************************************/
853
854 cursor c1 is select ROWID from IGS_AD_TERM_DETAILS
855 where TERM_DETAILS_ID= X_TERM_DETAILS_ID
856 ;
857 begin
858 open c1;
859 fetch c1 into X_ROWID;
860 if (c1%notfound) then
861 close c1;
862 INSERT_ROW (
863 X_ROWID,
864 X_TERM_DETAILS_ID,
865 X_TRANSCRIPT_ID,
866 X_TERM,
867 X_START_DATE,
868 X_END_DATE,
869 X_TOTAL_CP_ATTEMPTED,
870 X_TOTAL_CP_EARNED,
871 X_TOTAL_UNIT_GP,
872 X_TOTAL_GPA_UNITS,
873 X_GPA,
874 X_MODE );
875 return;
876 end if;
877 close c1;
878 UPDATE_ROW (
879 X_ROWID,
880 X_TERM_DETAILS_ID,
881 X_TRANSCRIPT_ID,
882 X_TERM,
883 X_START_DATE,
884 X_END_DATE,
885 X_TOTAL_CP_ATTEMPTED,
886 X_TOTAL_CP_EARNED,
887 X_TOTAL_UNIT_GP,
888 X_TOTAL_GPA_UNITS,
889 X_GPA,
890 X_MODE );
891 end ADD_ROW;
892 procedure DELETE_ROW (
893 X_ROWID in VARCHAR2,
894 x_mode IN VARCHAR2
895 ) AS
896 /*************************************************************
897 Created By : knaraset.in
898 Date Created By : 2000/05/16
899 Purpose :
900 Know limitations, enhancements or remarks
901 Change History
902 Who When What
903
904 (reverse chronological order - newest change first)
905 ***************************************************************/
906
907 begin
908 Before_DML (
909 p_action => 'DELETE',
910 x_rowid => X_ROWID
911 );
912 IF (x_mode = 'S') THEN
913 igs_sc_gen_001.set_ctx('R');
914 END IF;
915 delete from IGS_AD_TERM_DETAILS
916 where ROWID = X_ROWID;
917 if (sql%notfound) then
918 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
919 igs_ge_msg_stack.add;
920 igs_sc_gen_001.unset_ctx('R');
921 app_exception.raise_exception;
922 end if;
923 IF (x_mode = 'S') THEN
924 igs_sc_gen_001.unset_ctx('R');
925 END IF;
926
927 After_DML (
928 p_action => 'DELETE',
929 x_rowid => X_ROWID
930 );
931 end DELETE_ROW;
932 END igs_ad_term_details_pkg;
[Home] [Help]