1 PACKAGE BODY igs_ad_transcript_pkg AS
2 /* $Header: IGSAI82B.pls 120.2 2005/10/01 21:47:23 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_transcript%RowType;
5 new_references igs_ad_transcript%RowType;
6 l_transcript_id igs_ad_transcript.transcript_id%TYPE;
7
8 PROCEDURE check_uniqueness AS
9 /*
10 || Created By : [email protected]
11 || Created On : 29-OCT-2004
12 || Purpose : Handles the Unique Constraint logic defined for the columns.
13 || Known limitations, enhancements or remarks :
14 || Change History :
15 || Who When What
16 || (reverse chronological order - newest change first)
17 */
18 CURSOR check_issuedate(cp_education_id IN NUMBER,cp_date_of_issue IN DATE) IS
19 SELECT 'A' FROM IGS_AD_TRANSCRIPT_V TRANS WHERE EDUCATION_ID = cp_education_id
20 AND DATE_OF_ISSUE = cp_date_of_issue
21 AND (new_references.transcript_id IS NULL OR new_references.transcript_id <> trans.transcript_id);
22 l_temp VARCHAR2(1);
23
24 BEGIN
25 l_temp := NULL;
26 OPEN check_issuedate(new_references.education_id,new_references.date_of_issue);
27 FETCH check_issuedate INTO l_temp;
28 CLOSE check_issuedate;
29 IF l_temp IS NOT NULL THEN
30 Fnd_Message.Set_Name('IGS','IGS_AD_DUP_DATE_OF_ISSUE');
31 IGS_GE_MSG_STACK.ADD;
32 app_Exception.Raise_Exception;
33 END IF;
34
35 END check_uniqueness;
36
37 PROCEDURE Check_Status AS
38
39 /*
40 || Created By : jchin
41 || Created On : 29-SEP-2005
42 || Purpose : Check whether transcript is associated with an academic record
43 || which is INACTIVE If so, throw an error
44 || Known limitations, enhancements or remarks :
45 || Change History :
46 || Who When What
47 || (reverse chronological order - newest change first)
48 */
49 CURSOR check_status(cp_education_id IN NUMBER) IS
50 SELECT DISTINCT 1
51 FROM igs_ad_acad_history_v hist
52 WHERE hist.education_id = cp_education_id
53 AND hist.status = 'I';
54
55 l_temp NUMBER;
56
57 BEGIN
58
59 l_temp := NULL;
60
61 OPEN check_status(new_references.education_id);
62 FETCH check_status INTO l_temp;
63 CLOSE check_status;
64
65 IF l_temp IS NOT NULL THEN
66 Fnd_Message.Set_Name('IGS','IGS_AD_INACTIVE_ACAD_HIST');
67 IGS_GE_MSG_STACK.ADD;
68 app_Exception.Raise_Exception;
69 END IF;
70
71 END Check_Status;
72
73 PROCEDURE Set_Column_Values (
74 p_action IN VARCHAR2,
75 x_rowid IN VARCHAR2 DEFAULT NULL,
76 x_quintile_rank IN NUMBER DEFAULT NULL,
77 x_percentile_rank IN NUMBER DEFAULT NULL,
78 x_transcript_id IN NUMBER DEFAULT NULL,
79 x_education_id IN NUMBER DEFAULT NULL,
80 x_transcript_status IN VARCHAR2 DEFAULT NULL,
81 x_transcript_source IN NUMBER DEFAULT NULL,
82 x_date_of_receipt IN DATE DEFAULT NULL,
83 x_entered_gpa IN VARCHAR2 DEFAULT NULL,
84 x_entered_gs_id IN NUMBER DEFAULT NULL,
85 x_conv_gpa IN VARCHAR2 DEFAULT NULL,
86 x_conv_gs_id IN NUMBER DEFAULT NULL,
87 x_term_type IN VARCHAR2 DEFAULT NULL,
88 x_rank_in_class IN NUMBER DEFAULT NULL,
89 x_class_size IN NUMBER DEFAULT NULL,
90 x_approximate_rank IN VARCHAR2 DEFAULT NULL,
91 x_weighted_rank IN VARCHAR2 DEFAULT NULL,
92 x_decile_rank IN NUMBER DEFAULT NULL,
93 x_quartile_rank IN NUMBER DEFAULT NULL,
94 x_transcript_type IN VARCHAR2 DEFAULT NULL,
95 x_creation_date IN DATE DEFAULT NULL,
96 x_created_by IN NUMBER DEFAULT NULL,
97 x_last_update_date IN DATE DEFAULT NULL,
98 x_last_updated_by IN NUMBER DEFAULT NULL,
99 x_last_update_login IN NUMBER DEFAULT NULL ,
100 x_override IN VARCHAR2 DEFAULT NULL,
101 x_override_id IN NUMBER DEFAULT NULL,
102 x_override_date IN DATE DEFAULT NULL,
103 X_DATE_OF_ISSUE IN DATE DEFAULT NULL,
104 -- Added for new field Ravishar / 14-sep-2004
105 x_CORE_CURRICULUM_VALUE IN VARCHAR2 DEFAULT NULL
106 ) AS
107
108 /*************************************************************
109 Created By : Kamalakar N.
110 Date Created By : 15/May/2000
111 Purpose :
112 Know limitations, enhancements or remarks
113 Change History
114 Who When What
115 (reverse chronological order - newest change first)
116
117 vdixit.in 23-JULY-2001 Added new column transcript_type
118 to the tbh calls
119 rboddu 12-NOV-2001 Added new columns oevrride,
120 override_id and override_date.
121 End Bug No : 2097333
122 ***************************************************************/
123
124 CURSOR cur_old_ref_values IS
125 SELECT *
126 FROM IGS_AD_TRANSCRIPT
127 WHERE rowid = x_rowid;
128
129 BEGIN
130
131 l_rowid := x_rowid;
132
133 -- Code for setting the Old and New Reference Values.
134 -- Populate Old Values.
135 Open cur_old_ref_values;
136 Fetch cur_old_ref_values INTO old_references;
137 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
138 Close cur_old_ref_values;
139 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
140 IGS_GE_MSG_STACK.ADD;
141 App_Exception.Raise_Exception;
142 Return;
143 END IF;
144 Close cur_old_ref_values;
145
146 -- Populate New Values.
147 new_references.quintile_rank := x_quintile_rank;
148 new_references.percentile_rank := x_percentile_rank;
149 new_references.transcript_id := x_transcript_id;
150 new_references.education_id := x_education_id;
151 new_references.transcript_status := x_transcript_status;
152 new_references.transcript_source := x_transcript_source;
153 new_references.date_of_receipt := TRUNC(x_date_of_receipt);
154 new_references.entered_gpa := x_entered_gpa;
155 new_references.entered_gs_id := x_entered_gs_id;
156 new_references.conv_gpa := x_conv_gpa;
157 new_references.conv_gs_id := x_conv_gs_id;
158 new_references.term_type := x_term_type;
159 new_references.rank_in_class := x_rank_in_class;
160 new_references.class_size := x_class_size;
161 new_references.approximate_rank := x_approximate_rank;
162 new_references.weighted_rank := x_weighted_rank;
163 new_references.decile_rank := x_decile_rank;
164 new_references.quartile_rank := x_quartile_rank;
165 new_references.transcript_type := x_transcript_type;
166 new_references.override := x_override;
167 new_references.override_id := x_override_id;
168 new_references.override_date := TRUNC(x_override_Date);
169 new_references.date_of_issue := TRUNC(x_date_of_issue);
170 -- Added for new field Ravishar / 14-sep-2004
171 new_references.core_curriculum_value := x_core_curriculum_value;
172
173
174 IF (p_action = 'UPDATE') THEN
175 new_references.creation_date := old_references.creation_date;
176 new_references.created_by := old_references.created_by;
177 ELSE
178 new_references.creation_date := x_creation_date;
179 new_references.created_by := x_created_by;
180 END IF;
181 new_references.last_update_date := x_last_update_date;
182 new_references.last_updated_by := x_last_updated_by;
183 new_references.last_update_login := x_last_update_login;
184
185 END Set_Column_Values;
186
187 PROCEDURE Check_Constraints (
188 Column_Name IN VARCHAR2 DEFAULT NULL,
189 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
190 /*************************************************************
191 Created By : Kamalakar N.
192 Date Created By : 15/May/2000
193 Purpose :
194 Know limitations, enhancements or remarks
195 Change History
196 Who When What
197
198 (reverse chronological order - newest change first)
199 vdixit.in 23-JULY-2001 Added new column transcript_type
200 to the tbh calls
201 rboddu 12-NOV-2001 Added the new columns override,
202 override_id and override_date
203 Bug No : 2097333
204 kamohan 30-NOV-2001 Added the check for date of
205 issue column
206 ***************************************************************/
207
208 BEGIN
209
210 IF column_name IS NULL THEN
211 NULL;
212 ELSIF UPPER(column_name) = 'PERCENTILE_RANK' THEN
213 new_references.percentile_rank := IGS_GE_NUMBER.TO_NUM(column_value);
214 ELSIF UPPER(column_name) = 'QUARTILE_RANK' THEN
215 new_references.quartile_rank := IGS_GE_NUMBER.TO_NUM(column_value);
216 ELSIF UPPER(column_name) = 'QUINTILE_RANK' THEN
217 new_references.quintile_rank := IGS_GE_NUMBER.TO_NUM(column_value);
218 ELSIF UPPER(column_name) = 'DECILE_RANK' THEN
219 new_references.decile_rank := IGS_GE_NUMBER.TO_NUM(column_value);
220 ELSIF UPPER(column_name) = 'APPROXIMATE_RANK' THEN
221 new_references.approximate_rank := column_value;
222 ELSIF UPPER(column_name) = 'WEIGHTED_RANK' THEN
223 new_references.weighted_rank := column_value;
224 ELSIF UPPER(column_name) = 'RANK_IN_CLASS' THEN
225 new_references.rank_in_class := IGS_GE_NUMBER.TO_NUM(column_value);
226 ELSIF UPPER(column_name) = 'CLASS_SIZE' THEN
227 new_references.class_size := IGS_GE_NUMBER.TO_NUM(column_value);
228 ELSIF UPPER(column_name) = 'TRANSCRIPT_TYPE' THEN
229 new_references.transcript_type := column_value;
230 END IF;
231
232 -- The following code checks for check constraints on the Columns.
233 IF Upper(Column_Name) = 'PERCENTILE_RANK' OR
234 Column_Name IS NULL THEN
235 IF NOT ( (new_references.percentile_rank >= 0) AND (new_references.percentile_rank <= 100) ) THEN
236 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
237 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERCENTILE_RANK'));
238 IGS_GE_MSG_STACK.ADD;
239 App_Exception.Raise_Exception;
240 END IF;
241 END IF;
242
243 -- The following code checks for check constraints on the Columns.
244 IF Upper(Column_Name) = 'QUARTILE_RANK' OR
245 Column_Name IS NULL THEN
246 IF NOT ( (new_references.quartile_rank >0) AND (new_references.quartile_rank <= 4) ) THEN
247 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
248 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_QUARTILE_RANK'));
249 IGS_GE_MSG_STACK.ADD;
250 App_Exception.Raise_Exception;
251 END IF;
252 END IF;
253
254 -- The following code checks for check constraints on the Columns.
255 IF Upper(Column_Name) = 'QUINTILE_RANK' OR
256 Column_Name IS NULL THEN
257 IF NOT ( (new_references.quintile_rank > 0) AND (new_references.quintile_rank <= 5) ) THEN
258 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
259 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_QUINTILE_RANK'));
260 IGS_GE_MSG_STACK.ADD;
261 App_Exception.Raise_Exception;
262 END IF;
263 END IF;
264
265 -- The following code checks for check constraints on the Columns.
266 IF Upper(Column_Name) = 'DECILE_RANK' OR
267 Column_Name IS NULL THEN
268 IF NOT ( (new_references.decile_rank > 0) AND (new_references.decile_rank <= 10) ) THEN
269 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
270 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_DECILE_RANK'));
271 IGS_GE_MSG_STACK.ADD;
272 App_Exception.Raise_Exception;
273 END IF;
274 END IF;
275
276 -- The following code checks for check constraints on the Columns.
277 IF Upper(Column_Name) = 'APPROXIMATE_RANK' OR
278 Column_Name IS NULL THEN
279 IF NOT (new_references.approximate_rank in ('Y','N')) THEN
280 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
281 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPROXIMATE_RANK'));
282 IGS_GE_MSG_STACK.ADD;
283 App_Exception.Raise_Exception;
284 END IF;
285 END IF;
286
287 -- The following code checks for check constraints on the Columns.
288 IF Upper(Column_Name) = 'WEIGHTED_RANK' OR
289 Column_Name IS NULL THEN
290 IF NOT (new_references.weighted_rank in ('Y','N')) THEN
291 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
292 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_WEIGHTED_RANK'));
293 IGS_GE_MSG_STACK.ADD;
294 App_Exception.Raise_Exception;
295 END IF;
296 END IF;
297
298 -- The following code checks for check constraints on the Columns.
299 IF Upper(Column_Name) = 'RANK_IN_CLASS' OR
300 Column_Name IS NULL THEN
301 IF NOT (new_references.rank_in_class > 0) THEN
302 FND_MESSAGE.SET_NAME('IGS','IGS_SS_DSRD_RANK_NONEGATE');
303 IGS_GE_MSG_STACK.ADD;
304 App_Exception.Raise_Exception;
305 END IF;
306 END IF;
307
308 -- The following code checks for check constraints on the Columns.
309 IF Upper(Column_Name) = 'CLASS_SIZE' OR
310 Column_Name IS NULL THEN
311 IF NOT (new_references.class_size > 0) THEN
312 FND_MESSAGE.SET_NAME('IGS','IGS_AD_CLASS_SIZE');
313 IGS_GE_MSG_STACK.ADD;
314 App_Exception.Raise_Exception;
315 END IF;
316 END IF;
317
318 IF Upper(Column_Name) = 'TRANSCRIPT_TYPE' OR
319 Column_Name IS NULL THEN
320 IF NOT (new_references.transcript_type in ('OFFICIAL','UNOFFICIAL')) THEN
321 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
322 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TRANSCRIPT_TYPE'));
323 IGS_GE_MSG_STACK.ADD;
324 App_Exception.Raise_Exception;
325 END IF;
326 END IF;
327
328 -- The following code checks for check constraints on the Columns.
329 IF Upper(Column_Name) = 'OVERRIDE' OR
330 Column_Name IS NULL THEN
331 IF NOT (new_references.override in ('Y','N')) THEN
332 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
333 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_OVERRIDE'));
334 IGS_GE_MSG_STACK.ADD;
335 App_Exception.Raise_Exception;
336 END IF;
337 END IF;
338
339 -- The following code checks for check constraints on the Columns.
340 IF Upper(Column_Name) = 'DATE_OF_ISSUE' OR
341 Column_Name IS NULL THEN
342 IF (new_references.date_of_issue > SYSDATE ) THEN
343 Fnd_Message.Set_Name('IGS','IGS_AD_SYS_DATOFISSUE');
344 IGS_GE_MSG_STACK.ADD;
345 App_Exception.Raise_Exception;
346 END IF;
347 IF (new_references.date_of_issue > new_references.date_of_receipt ) THEN
348 Fnd_Message.Set_Name('IGS','IGS_AD_INV_DATOFISSUE');
349 IGS_GE_MSG_STACK.ADD;
350 app_Exception.Raise_Exception;
351 END IF;
352 END IF;
353 END Check_Constraints;
354
355 PROCEDURE Check_Parent_Existance AS
356 /*************************************************************
357 Created By : Kamalakar N.
358 Date Created By : 15/May/2000
359 Purpose :
360 Know limitations, enhancements or remarks
361 Change History
362 Who When What
363
364 (reverse chronological order - newest change first)
365 vdixit.in 23-JULY-2001 Added new column transcript_type
366 to the tbh calls
367
368 ***************************************************************/
369
370 BEGIN
371
372 IF (((old_references.education_id = new_references.education_id)) OR
373 ((new_references.education_id IS NULL))) THEN
374 NULL;
375
376 /* ELSIF NOT Igs_Ad_Acad_History_Pkg.Get_PK_For_Validation (
377 new_references.education_id
378 ) THEN
379 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
380 IGS_GE_MSG_STACK.ADD;
381 App_Exception.Raise_Exception;*/ -- not needed after TCA changes
382 END IF;
383
384 IF (((old_references.conv_gs_id = new_references.conv_gs_id)) OR
385 ((new_references.conv_gs_id IS NULL))) THEN
386 NULL;
387 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_uk2_For_Validation (
388 new_references.conv_gs_id,
389 'GRADING_SCALE_TYPES',
390 'N'
391 ) THEN
392 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
393 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_CONV_GRAD_SCALE'));
394 IGS_GE_MSG_STACK.ADD;
395 App_Exception.Raise_Exception;
396 END IF;
397
398 IF (((old_references.transcript_source = new_references.transcript_source)) OR
399 ((new_references.transcript_source IS NULL))) THEN
400 NULL;
401 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
402 new_references.transcript_source,
403 'TRANSCRIPT_SOURCE',
404 'N'
405 ) THEN
406 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
407 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TRANSCRIPT_SOURCE'));
408 IGS_GE_MSG_STACK.ADD;
409 App_Exception.Raise_Exception;
410 END IF;
411
412 IF (((old_references.entered_gs_id = new_references.entered_gs_id)) OR
413 ((new_references.entered_gs_id IS NULL))) THEN
414 NULL;
415 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_uk2_For_Validation (
416 new_references.entered_gs_id,
417 'GRADING_SCALE_TYPES',
418 'N'
419 ) THEN
420 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
421 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENTERED_GRAD_SCALE'));
422 IGS_GE_MSG_STACK.ADD;
423 App_Exception.Raise_Exception;
424 END IF;
425
426 IF (((old_references.term_type = new_references.term_type)) OR
427 ((new_references.term_type IS NULL))) THEN
428 NULL;
429 ELSIF NOT Igs_LookUps_View_Pkg.Get_PK_For_Validation (
430 'TERM_TYPE',
431 new_references.term_type
432 ) THEN
433 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
434 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TERM_TYPE'));
435 IGS_GE_MSG_STACK.ADD;
436 App_Exception.Raise_Exception;
437 END IF;
438
439 IF (((old_references.transcript_status = new_references.transcript_status)) OR
440 ((new_references.transcript_status IS NULL))) THEN
441 NULL;
442 ELSIF NOT Igs_LookUps_View_Pkg.Get_PK_For_Validation (
443 'TRANSCRIPT_STATUS',
444 new_references.transcript_status
445 ) THEN
446 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
447 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TRANSCRIPT_STATUS'));
448 IGS_GE_MSG_STACK.ADD;
449 App_Exception.Raise_Exception;
450 END IF;
451
452
453 IF (((old_references.transcript_type = new_references.transcript_type)) OR
454 ((new_references.transcript_type IS NULL))) THEN
455 NULL;
456 ELSIF NOT Igs_LookUps_View_Pkg.Get_PK_For_Validation (
457 'TRANSCRIPT_TYPE',
458 new_references.transcript_type
459 ) THEN
460 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
461 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TRANSCRIPT_TYPE'));
462 IGS_GE_MSG_STACK.ADD;
463 App_Exception.Raise_Exception;
464 END IF;
465
466 END Check_Parent_Existance;
467
468 PROCEDURE Check_Child_Existance IS
469 /*************************************************************
470 Created By : Kamalakar N.
471 Date Created By : 15/May/2000
472 Purpose :
473 Know limitations, enhancements or remarks
474 Change History
475 Who When What
476
477 (reverse chronological order - newest change first)
478 ***************************************************************/
479
480 BEGIN
481
482 Igs_Ad_Term_Details_Pkg.Get_FK_Igs_Ad_Transcript (
483 old_references.transcript_id
484 );
485
486 END Check_Child_Existance;
487
488 FUNCTION Get_PK_For_Validation (
489 x_transcript_id IN NUMBER
490 ) RETURN BOOLEAN AS
491
492 /*************************************************************
493 Created By : Kamalakar N.
494 Date Created By : 15/May/2000
495 Purpose :
496 Know limitations, enhancements or remarks
497 Change History
498 Who When What
499
500 (reverse chronological order - newest change first)
501 ***************************************************************/
502
503 CURSOR cur_rowid IS
504 SELECT rowid
505 FROM igs_ad_transcript
506 WHERE transcript_id = x_transcript_id
507 FOR UPDATE NOWAIT;
508
509 lv_rowid cur_rowid%RowType;
510
511 BEGIN
512
513 Open cur_rowid;
514 Fetch cur_rowid INTO lv_rowid;
515 IF (cur_rowid%FOUND) THEN
516 Close cur_rowid;
517 Return(TRUE);
518 ELSE
519 Close cur_rowid;
520 Return(FALSE);
521 END IF;
522 END Get_PK_For_Validation;
523
524 PROCEDURE Get_FK_Igs_Ad_Hz_Acad_History (
525 x_education_id IN NUMBER
526 ) AS
527
528 /*************************************************************
529 Created By : Kamalakar N.
530 Date Created By : 15/May/2000
531 Purpose :
532 Know limitations, enhancements or remarks
533 Change History
534 Who When What
535
536 (reverse chronological order - newest change first)
537 ***************************************************************/
538
539 CURSOR cur_rowid IS
540 SELECT rowid
541 FROM igs_ad_transcript
542 WHERE education_id = x_education_id ;
543
544 lv_rowid cur_rowid%RowType;
545
546 BEGIN
547
548 Open cur_rowid;
549 Fetch cur_rowid INTO lv_rowid;
550 IF (cur_rowid%FOUND) THEN
551 Close cur_rowid;
552 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATRN_AAHST_FK');
553 IGS_GE_MSG_STACK.ADD;
554 App_Exception.Raise_Exception;
555 Return;
556 END IF;
557 Close cur_rowid;
558
559 END Get_FK_Igs_Ad_Hz_Acad_History;
560
561
562 PROCEDURE Get_FK_Igs_Ad_Code_Classes (
563 x_code_id IN NUMBER
564 ) AS
565
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 cur_rowid IS
578 SELECT rowid
579 FROM igs_ad_transcript
580 WHERE conv_gs_id = x_code_id ;
581
582 lv_rowid cur_rowid%RowType;
583
584 CURSOR cur_rowid2 IS
585 SELECT rowid
586 FROM igs_ad_transcript
587 WHERE transcript_source = x_code_id ;
588
589 lv_rowid2 cur_rowid2%RowType;
590
591 CURSOR cur_rowid3 IS
592 SELECT rowid
593 FROM igs_ad_transcript
594 WHERE entered_gs_id = x_code_id ;
595
596 lv_rowid3 cur_rowid3%RowType;
597
598 BEGIN
599
600 Open cur_rowid;
601 Fetch cur_rowid INTO lv_rowid;
602 IF (cur_rowid%FOUND) THEN
603 Close cur_rowid;
604 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATRN_ACDC_FK1');
605 IGS_GE_MSG_STACK.ADD;
606 App_Exception.Raise_Exception;
607 Return;
608 END IF;
609 Close cur_rowid;
610 Open cur_rowid2;
611 Fetch cur_rowid2 INTO lv_rowid2;
612 IF (cur_rowid2%FOUND) THEN
613 Close cur_rowid2;
614 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATRN_ACDC_FK3');
615 IGS_GE_MSG_STACK.ADD;
616 App_Exception.Raise_Exception;
617 Return;
618 END IF;
619 Close cur_rowid2;
620 Open cur_rowid3;
621 Fetch cur_rowid3 INTO lv_rowid3;
622 IF (cur_rowid3%FOUND) THEN
623 Close cur_rowid3;
624 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATRN_ACDC_FK2');
625 IGS_GE_MSG_STACK.ADD;
626 App_Exception.Raise_Exception;
627 Return;
628 END IF;
629 Close cur_rowid3;
630 END Get_FK_Igs_Ad_Code_Classes;
631
632
633
634 PROCEDURE Before_DML (
635 p_action IN VARCHAR2,
636 x_rowid IN VARCHAR2 DEFAULT NULL,
637 x_quintile_rank IN NUMBER DEFAULT NULL,
638 x_percentile_rank IN NUMBER DEFAULT NULL,
639 x_transcript_id IN NUMBER DEFAULT NULL,
640 x_education_id IN NUMBER DEFAULT NULL,
641 x_transcript_status IN VARCHAR2 DEFAULT NULL,
642 x_transcript_source IN NUMBER DEFAULT NULL,
643 x_date_of_receipt IN DATE DEFAULT NULL,
644 x_entered_gpa IN VARCHAR2 DEFAULT NULL,
645 x_entered_gs_id IN NUMBER DEFAULT NULL,
646 x_conv_gpa IN VARCHAR2 DEFAULT NULL,
647 x_conv_gs_id IN NUMBER DEFAULT NULL,
648 x_term_type IN VARCHAR2 DEFAULT NULL,
649 x_rank_in_class IN NUMBER DEFAULT NULL,
650 x_class_size IN NUMBER DEFAULT NULL,
651 x_approximate_rank IN VARCHAR2 DEFAULT NULL,
652 x_weighted_rank IN VARCHAR2 DEFAULT NULL,
653 x_decile_rank IN NUMBER DEFAULT NULL,
654 x_quartile_rank IN NUMBER DEFAULT NULL,
655 x_transcript_type IN VARCHAR2 DEFAULT NULL,
656 x_creation_date IN DATE DEFAULT NULL,
657 x_created_by IN NUMBER DEFAULT NULL,
658 x_last_update_date IN DATE DEFAULT NULL,
659 x_last_updated_by IN NUMBER DEFAULT NULL,
660 x_last_update_login IN NUMBER DEFAULT NULL,
661 x_override IN VARCHAR2 DEFAULT NULL,
662 x_override_id IN NUMBER DEFAULT NULL,
663 x_override_date IN DATE DEFAULT NULL,
664 X_DATE_OF_ISSUE IN DATE DEFAULT NULL,
665 -- Added for new field Ravishar / 14-sep-2004
666 x_CORE_CURRICULUM_VALUE IN VARCHAR2 DEFAULT NULL
667
668 ) AS
669 /*************************************************************
670 Created By : Kamalakar N.
671 Date Created By : 15/May/2000
672 Purpose :
673 Know limitations, enhancements or remarks
674 Change History
675 Who When What
676
677 (reverse chronological order - newest change first)
678 vdixit.in 23-JULY-2001 Added new column transcript_type
679 to the tbh calls
680 rboddu 12-NOV-2001 Added the new columns override,
681 override_id and override_date
682 Bug No: 2097333
683 ***************************************************************/
684
685 BEGIN
686
687 Set_Column_Values (
688 p_action,
689 x_rowid,
690 x_quintile_rank,
691 x_percentile_rank,
692 x_transcript_id,
693 x_education_id,
694 x_transcript_status,
695 x_transcript_source,
696 x_date_of_receipt,
697 x_entered_gpa,
698 x_entered_gs_id,
699 x_conv_gpa,
700 x_conv_gs_id,
701 x_term_type,
702 x_rank_in_class,
703 x_class_size,
704 x_approximate_rank,
705 x_weighted_rank,
706 x_decile_rank,
707 x_quartile_rank,
708 x_transcript_type,
709 x_creation_date,
710 x_created_by,
711 x_last_update_date,
712 x_last_updated_by,
713 x_last_update_login,
714 x_override,
715 x_override_id,
716 x_override_date,
717 x_date_of_issue,
718 -- Added for new field Ravishar / 14-sep-2004
719 x_core_curriculum_value
720 );
721
722 IF (p_action = 'INSERT') THEN
723 -- Call all the procedures related to Before Insert.
724 Null;
725 IF Get_Pk_For_Validation(
726 new_references.transcript_id) THEN
727 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
728 IGS_GE_MSG_STACK.ADD;
729 App_Exception.Raise_Exception;
730 END IF;
731 Check_Constraints;
732 Check_Parent_Existance;
733 check_uniqueness;
734 Check_Status; --jchin Bug 4629226
735 ELSIF (p_action = 'UPDATE') THEN
736 -- Call all the procedures related to Before Update.
737 Null;
738 Check_Constraints;
739 Check_Parent_Existance;
740 check_uniqueness;
741 Check_Status; --jchin Bug 4629226
742 ELSIF (p_action = 'DELETE') THEN
743 -- Call all the procedures related to Before Delete.
744 Null;
745 Check_Child_Existance;
746 ELSIF (p_action = 'VALIDATE_INSERT') THEN
747 -- Call all the procedures related to Before Insert.
748 IF Get_PK_For_Validation (
749 new_references.transcript_id) THEN
750 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
751 IGS_GE_MSG_STACK.ADD;
752 App_Exception.Raise_Exception;
753 END IF;
754 Check_Constraints;
755 check_uniqueness;
756 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
757 Check_Constraints;
758 check_uniqueness;
759 ELSIF (p_action = 'VALIDATE_DELETE') THEN
760 Check_Child_Existance;
761 END IF;
762
763 END Before_DML;
764
765 PROCEDURE After_DML (
766 p_action IN VARCHAR2,
767 x_rowid IN VARCHAR2
768 ) IS
769 /*************************************************************
770 Created By : Kamalakar N.
771 Date Created By : 15/May/2000
772 Purpose :
773 Know limitations, enhancements or remarks
774 Change History
775 Who When What
776
777 (reverse chronological order - newest change first)
778 ***************************************************************/
779 CURSOR c_person_id( p_education_id hz_education.education_id%TYPE) IS
780 SELECT party_id person_id
781 FROM HZ_EDUCATION
782 WHERE education_id = p_education_id;
783 person_id_rec c_person_id%ROWTYPE;
784 BEGIN
785
786 l_rowid := x_rowid;
787
788 IF (p_action = 'INSERT') THEN
789 -- Call all the procedures related to After Insert.
790 IF IGS_AD_TRANSCRIPT_PKG.TRN_CRT_MODE = 'NI' THEN
791 OPEN c_person_id(new_references.education_id);
792 FETCH c_person_id INTO person_id_rec;
793 CLOSE c_person_id;
794 igs_ad_wf_001.transcript_entrd_event(
795 person_id_rec.person_id,
796 new_references.education_id,
797 l_transcript_id
798 );
799 END IF;
800
801 ELSIF (p_action = 'UPDATE') THEN
802 -- Call all the procedures related to After Update. desc
803 --Raise the buisness event
804 OPEN c_person_id(new_references.education_id);
805 FETCH c_person_id INTO person_id_rec;
806 CLOSE c_person_id;
807
808 igs_ad_wf_001.TRANSCRIPT_UPD_EVENT
809 (
810 P_TRANSCRIPT_ID => new_references.transcript_id,
811 P_EDUCATION_ID => new_references.education_id,
812 P_PERSON_ID => person_id_rec.person_id,
813 P_TRANSCRIPT_STATUS_OLD => old_references.transcript_status,
814 P_TRANSCRIPT_STATUS_NEW => new_references.transcript_status,
815 P_TRANSCRIPT_TYPE_OLD => old_references.transcript_type,
816 P_TRANSCRIPT_TYPE_NEW => new_references.transcript_type
817 );
818 ELSIF (p_action = 'DELETE') THEN
819 -- Call all the procedures related to After Delete.
820 Null;
821 END IF;
822
823 END After_DML;
824
825 procedure INSERT_ROW (
826 X_ROWID in out NOCOPY VARCHAR2,
827 x_QUINTILE_RANK IN NUMBER,
828 x_PERCENTILE_RANK IN NUMBER,
829 x_TRANSCRIPT_ID IN OUT NOCOPY NUMBER,
830 x_EDUCATION_ID IN NUMBER,
831 x_TRANSCRIPT_STATUS IN VARCHAR2,
832 x_TRANSCRIPT_SOURCE IN NUMBER,
833 x_DATE_OF_RECEIPT IN DATE,
834 x_ENTERED_GPA IN VARCHAR2,
835 x_ENTERED_GS_ID IN NUMBER,
836 x_CONV_GPA IN VARCHAR2,
837 x_CONV_GS_ID IN NUMBER,
838 x_TERM_TYPE IN VARCHAR2,
839 x_RANK_IN_CLASS IN NUMBER,
840 x_CLASS_SIZE IN NUMBER,
841 x_APPROXIMATE_RANK IN VARCHAR2,
842 x_WEIGHTED_RANK IN VARCHAR2,
843 x_DECILE_RANK IN NUMBER,
844 x_QUARTILE_RANK IN NUMBER,
845 x_TRANSCRIPT_TYPE IN VARCHAR2 DEFAULT NULL,
846 X_MODE in VARCHAR2 DEFAULT NULL,
847 x_OVERRIDE IN VARCHAR2 DEFAULT NULL,
848 x_OVERRIDE_ID IN NUMBER DEFAULT NULL,
849 x_OVERRIDE_DATE IN DATE DEFAULT NULL,
850 X_DATE_OF_ISSUE IN DATE DEFAULT NULL,
851 -- Added for new field Ravishar / 14-sep-2004
852 x_CORE_CURRICULUM_VALUE IN VARCHAR2 DEFAULT NULL
853
854 ) AS
855 /*************************************************************
856 Created By : Kamalakar N.
857 Date Created By : 15/May/2000
858 Purpose :
859 Know limitations, enhancements or remarks
860 Change History
861 Who When What
862
863 (reverse chronological order - newest change first)
864 vdixit.in 23-JULY-2001 Added new column transcript_type
865 to the tbh calls
866 rboddu 12-NOV-2001 Added the new columns override,
867 override_id and override_date
868 Bug No : 2097333
869 ***************************************************************/
870
871 cursor C is select ROWID from IGS_AD_TRANSCRIPT
872 where TRANSCRIPT_ID= X_TRANSCRIPT_ID
873 ;
874 X_LAST_UPDATE_DATE DATE ;
875 X_LAST_UPDATED_BY NUMBER ;
876 X_LAST_UPDATE_LOGIN NUMBER ;
877 X_REQUEST_ID NUMBER;
878 X_PROGRAM_ID NUMBER;
879 X_PROGRAM_APPLICATION_ID NUMBER;
880 X_PROGRAM_UPDATE_DATE DATE;
881
882 L_MODE VARCHAR2(1);
883 begin
884
885 L_MODE := NVL(X_MODE,'R');
886 X_LAST_UPDATE_DATE := SYSDATE;
887 if(L_MODE = 'I') then
888 X_LAST_UPDATED_BY := 1;
889 X_LAST_UPDATE_LOGIN := 0;
890 elsif (L_MODE IN ('R','S')) then
891 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
892 if X_LAST_UPDATED_BY is NULL then
893 X_LAST_UPDATED_BY := -1;
894 end if;
895 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
896 if X_LAST_UPDATE_LOGIN is NULL then
897 X_LAST_UPDATE_LOGIN := -1;
898 end if;
899 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
900 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
901 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
902 if (X_REQUEST_ID = -1) then
903 X_REQUEST_ID := NULL;
904 X_PROGRAM_ID := NULL;
905 X_PROGRAM_APPLICATION_ID := NULL;
906 X_PROGRAM_UPDATE_DATE := NULL;
907 else
908 X_PROGRAM_UPDATE_DATE := SYSDATE;
909 end if;
910 else
911 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
912 IGS_GE_MSG_STACK.ADD;
913 app_exception.raise_exception;
914 end if;
915 -- X_TRANSCRIPT_ID := -1;
916 Before_DML(
917 p_action=>'INSERT',
918 x_rowid=>X_ROWID,
919 x_quintile_rank=>X_QUINTILE_RANK,
920 x_percentile_rank=>X_PERCENTILE_RANK,
921 x_transcript_id=>X_TRANSCRIPT_ID,
922 x_education_id=>X_EDUCATION_ID,
923 x_transcript_status=>X_TRANSCRIPT_STATUS,
924 x_transcript_source=>X_TRANSCRIPT_SOURCE,
925 x_date_of_receipt=>X_DATE_OF_RECEIPT,
926 x_entered_gpa=>X_ENTERED_GPA,
927 x_entered_gs_id=>X_ENTERED_GS_ID,
928 x_conv_gpa=>X_CONV_GPA,
929 x_conv_gs_id=>X_CONV_GS_ID,
930 x_term_type=>X_TERM_TYPE,
931 x_rank_in_class=>X_RANK_IN_CLASS,
932 x_class_size=>X_CLASS_SIZE,
933 x_approximate_rank=>X_APPROXIMATE_RANK,
934 x_weighted_rank=>X_WEIGHTED_RANK,
935 x_decile_rank=>X_DECILE_RANK,
936 x_quartile_rank=>X_QUARTILE_RANK,
937 x_transcript_type=>X_TRANSCRIPT_TYPE,
938 x_creation_date=>X_LAST_UPDATE_DATE,
939 x_created_by=>X_LAST_UPDATED_BY,
940 x_last_update_date=>X_LAST_UPDATE_DATE,
941 x_last_updated_by=>X_LAST_UPDATED_BY,
942 x_last_update_login=>X_LAST_UPDATE_LOGIN,
943 x_override=>X_OVERRIDE,
944 x_override_id=>X_OVERRIDE_ID,
945 x_override_date=>X_OVERRIDE_DATE,
946 x_date_of_issue => x_date_of_issue,
947 -- Added for new field Ravishar / 14-sep-2004
948 x_core_curriculum_value => X_CORE_CURRICULUM_VALUE
949 );
950
951 IF X_TRANSCRIPT_ID IS NOT NULL THEN
952 L_TRANSCRIPT_ID:= X_TRANSCRIPT_ID;
953 ELSE
954 SELECT IGS_AD_TRANSCRIPT_S.NEXTVAL INTO L_TRANSCRIPT_ID FROM DUAL;
955 END IF;
956
957 IF (x_mode = 'S') THEN
958 igs_sc_gen_001.set_ctx('R');
959 END IF;
960 insert into IGS_AD_TRANSCRIPT (
961 QUINTILE_RANK
962 ,PERCENTILE_RANK
963 ,TRANSCRIPT_ID
964 ,EDUCATION_ID
965 ,TRANSCRIPT_STATUS
966 ,TRANSCRIPT_SOURCE
967 ,DATE_OF_RECEIPT
968 ,ENTERED_GPA
969 ,ENTERED_GS_ID
970 ,CONV_GPA
971 ,CONV_GS_ID
972 ,TERM_TYPE
973 ,RANK_IN_CLASS
974 ,CLASS_SIZE
975 ,APPROXIMATE_RANK
976 ,WEIGHTED_RANK
977 ,DECILE_RANK
978 ,QUARTILE_RANK
979 ,TRANSCRIPT_TYPE
980 ,OVERRIDE
981 ,OVERRIDE_ID
982 ,OVERRIDE_DATE
983 ,DATE_OF_ISSUE
984 ,CREATION_DATE
985 ,CREATED_BY
986 ,LAST_UPDATE_DATE
987 ,LAST_UPDATED_BY
988 ,LAST_UPDATE_LOGIN
989 ,REQUEST_ID
990 ,PROGRAM_ID
991 ,PROGRAM_APPLICATION_ID
992 ,PROGRAM_UPDATE_DATE,
993 -- Added for new field Ravishar / 14-sep-2004
994 core_curriculum_value
995
996 ) values (
997 NEW_REFERENCES.QUINTILE_RANK
998 ,NEW_REFERENCES.PERCENTILE_RANK
999 ,L_TRANSCRIPT_ID --IGS_AD_TRANSCRIPT_S.NEXTVAL
1000 ,NEW_REFERENCES.EDUCATION_ID
1001 ,NEW_REFERENCES.TRANSCRIPT_STATUS
1002 ,NEW_REFERENCES.TRANSCRIPT_SOURCE
1003 ,NEW_REFERENCES.DATE_OF_RECEIPT
1004 ,NEW_REFERENCES.ENTERED_GPA
1005 ,NEW_REFERENCES.ENTERED_GS_ID
1006 ,NEW_REFERENCES.CONV_GPA
1007 ,NEW_REFERENCES.CONV_GS_ID
1008 ,NEW_REFERENCES.TERM_TYPE
1009 ,NEW_REFERENCES.RANK_IN_CLASS
1010 ,NEW_REFERENCES.CLASS_SIZE
1011 ,NEW_REFERENCES.APPROXIMATE_RANK
1012 ,NEW_REFERENCES.WEIGHTED_RANK
1013 ,NEW_REFERENCES.DECILE_RANK
1014 ,NEW_REFERENCES.QUARTILE_RANK
1015 ,NEW_REFERENCES.TRANSCRIPT_TYPE
1016 ,NEW_REFERENCES.OVERRIDE
1017 ,NEW_REFERENCES.OVERRIDE_ID
1018 ,NEW_REFERENCES.OVERRIDE_DATE
1019 ,NEW_REFERENCES.DATE_OF_ISSUE
1020 ,X_LAST_UPDATE_DATE
1021 ,X_LAST_UPDATED_BY
1022 ,X_LAST_UPDATE_DATE
1023 ,X_LAST_UPDATED_BY
1024 ,X_LAST_UPDATE_LOGIN
1025 ,X_REQUEST_ID
1026 ,X_PROGRAM_ID
1027 ,X_PROGRAM_APPLICATION_ID
1028 ,X_PROGRAM_UPDATE_DATE
1029 -- Added for new field Ravishar / 14-sep-2004
1030 ,NEW_REFERENCES.CORE_CURRICULUM_VALUE
1031
1032 )RETURNING TRANSCRIPT_ID INTO X_TRANSCRIPT_ID;
1033 IF (x_mode = 'S') THEN
1034 igs_sc_gen_001.unset_ctx('R');
1035 END IF;
1036
1037 l_transcript_id := X_TRANSCRIPT_ID;
1038 open c;
1039 fetch c into X_ROWID;
1040 if (c%notfound) then
1041 close c;
1042 raise no_data_found;
1043 end if;
1044 close c;
1045 After_DML (
1046 p_action => 'INSERT' ,
1047 x_rowid => X_ROWID );
1048 EXCEPTION
1049 WHEN OTHERS THEN
1050 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1051 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1052 fnd_message.set_token ('ERR_CD', SQLCODE);
1053 igs_ge_msg_stack.add;
1054 igs_sc_gen_001.unset_ctx('R');
1055 app_exception.raise_exception;
1056 ELSE
1057 igs_sc_gen_001.unset_ctx('R');
1058 RAISE;
1059 END IF;
1060
1061 end INSERT_ROW;
1062 procedure LOCK_ROW (
1063 X_ROWID in VARCHAR2,
1064 x_QUINTILE_RANK IN NUMBER,
1065 x_PERCENTILE_RANK IN NUMBER,
1066 x_TRANSCRIPT_ID IN NUMBER,
1067 x_EDUCATION_ID IN NUMBER,
1068 x_TRANSCRIPT_STATUS IN VARCHAR2,
1069 x_TRANSCRIPT_SOURCE IN NUMBER,
1070 x_DATE_OF_RECEIPT IN DATE,
1071 x_ENTERED_GPA IN VARCHAR2,
1072 x_ENTERED_GS_ID IN NUMBER,
1073 x_CONV_GPA IN VARCHAR2,
1074 x_CONV_GS_ID IN NUMBER,
1075 x_TERM_TYPE IN VARCHAR2,
1076 x_RANK_IN_CLASS IN NUMBER,
1077 x_CLASS_SIZE IN NUMBER,
1078 x_APPROXIMATE_RANK IN VARCHAR2,
1079 x_WEIGHTED_RANK IN VARCHAR2,
1080 x_DECILE_RANK IN NUMBER,
1081 x_QUARTILE_RANK IN NUMBER ,
1082 x_TRANSCRIPT_TYPE IN VARCHAR2,
1083 x_OVERRIDE IN VARCHAR2 DEFAULT NULL,
1084 x_OVERRIDE_ID IN NUMBER DEFAULT NULL,
1085 x_OVERRIDE_DATE IN DATE DEFAULT NULL,
1086 X_DATE_OF_ISSUE IN DATE DEFAULT NULL,
1087 -- Added for new field Ravishar / 14-sep-2004
1088 x_CORE_CURRICULUM_VALUE IN VARCHAR2 DEFAULT NULL
1089 ) AS
1090 /*************************************************************
1091 Created By : Kamalakar N.
1092 Date Created By : 15/May/2000
1093 Purpose :
1094 Know limitations, enhancements or remarks
1095 Change History
1096 Who When What
1097
1098 (reverse chronological order - newest change first)
1099 vdixit.in 23-JULY-2001 Added new column transcript_type
1100 to the tbh calls
1101 rboddu 12-NOV-2001 Added the new columns override,
1102 override_id and override_date
1103 Bug No : 2097333
1104
1105 ***************************************************************/
1106
1107 cursor c1 is select
1108 QUINTILE_RANK
1109 , PERCENTILE_RANK
1110 , EDUCATION_ID
1111 , TRANSCRIPT_STATUS
1112 , TRANSCRIPT_SOURCE
1113 , DATE_OF_RECEIPT
1114 , ENTERED_GPA
1115 , ENTERED_GS_ID
1116 , CONV_GPA
1117 , CONV_GS_ID
1118 , TERM_TYPE
1119 , RANK_IN_CLASS
1120 , CLASS_SIZE
1121 , APPROXIMATE_RANK
1122 , WEIGHTED_RANK
1123 , DECILE_RANK
1124 , QUARTILE_RANK
1125 , TRANSCRIPT_TYPE
1126 , OVERRIDE
1127 , OVERRIDE_ID
1128 , OVERRIDE_DATE
1129 , DATE_OF_ISSUE
1130 -- Added for new field Ravishar / 14-sep-2004
1131 , CORE_CURRICULUM_VALUE
1132
1133 from IGS_AD_TRANSCRIPT
1134 where ROWID = X_ROWID
1135 for update nowait;
1136 tlinfo c1%rowtype;
1137 begin
1138 open c1;
1139 fetch c1 into tlinfo;
1140 if (c1%notfound) then
1141 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1142 IGS_GE_MSG_STACK.ADD;
1143 close c1;
1144 app_exception.raise_exception;
1145 return;
1146 end if;
1147 close c1;
1148 if ( ( (tlinfo.QUINTILE_RANK = X_QUINTILE_RANK)
1149 OR ((tlinfo.QUINTILE_RANK is null)
1150 AND (X_QUINTILE_RANK is null)))
1151 AND ((tlinfo.PERCENTILE_RANK = X_PERCENTILE_RANK)
1152 OR ((tlinfo.PERCENTILE_RANK is null)
1153 AND (X_PERCENTILE_RANK is null)))
1154 AND (tlinfo.EDUCATION_ID = X_EDUCATION_ID)
1155 AND (tlinfo.TRANSCRIPT_STATUS = X_TRANSCRIPT_STATUS)
1156 AND (tlinfo.TRANSCRIPT_SOURCE = X_TRANSCRIPT_SOURCE)
1157 AND ((TRUNC(tlinfo.DATE_OF_RECEIPT) = TRUNC(X_DATE_OF_RECEIPT))
1158 OR ((tlinfo.DATE_OF_RECEIPT is null)
1159 AND (X_DATE_OF_RECEIPT is null)))
1160 AND ((tlinfo.ENTERED_GPA = X_ENTERED_GPA)
1161 OR ((tlinfo.ENTERED_GPA is null)
1162 AND (X_ENTERED_GPA is null)))
1163 AND (tlinfo.ENTERED_GS_ID = X_ENTERED_GS_ID)
1164 AND ((tlinfo.CONV_GPA = X_CONV_GPA)
1165 OR ((tlinfo.CONV_GPA is null)
1166 AND (X_CONV_GPA is null)))
1167 AND (tlinfo.CONV_GS_ID = X_CONV_GS_ID)
1168 AND (tlinfo.TERM_TYPE = X_TERM_TYPE)
1169 AND ((tlinfo.RANK_IN_CLASS = X_RANK_IN_CLASS)
1170 OR ((tlinfo.RANK_IN_CLASS is null)
1171 AND (X_RANK_IN_CLASS is null)))
1172 AND ((tlinfo.CLASS_SIZE = X_CLASS_SIZE)
1173 OR ((tlinfo.CLASS_SIZE is null)
1174 AND (X_CLASS_SIZE is null)))
1175 AND ((tlinfo.APPROXIMATE_RANK = X_APPROXIMATE_RANK)
1176 OR ((tlinfo.APPROXIMATE_RANK is null)
1177 AND (X_APPROXIMATE_RANK is null)))
1178 AND ((tlinfo.WEIGHTED_RANK = X_WEIGHTED_RANK)
1179 OR ((tlinfo.WEIGHTED_RANK is null)
1180 AND (X_WEIGHTED_RANK is null)))
1181 AND ((tlinfo.DECILE_RANK = X_DECILE_RANK)
1182 OR ((tlinfo.DECILE_RANK is null)
1183 AND (X_DECILE_RANK is null)))
1184 AND ((tlinfo.QUARTILE_RANK = X_QUARTILE_RANK)
1185 OR ((tlinfo.QUARTILE_RANK is null)
1186 AND (X_QUARTILE_RANK is null)))
1187 AND (tlinfo.TRANSCRIPT_TYPE = X_TRANSCRIPT_TYPE)
1188 )
1189 AND ((tlinfo.OVERRIDE = X_OVERRIDE)
1190 OR ((tlinfo.OVERRIDE is null)
1191 AND (X_OVERRIDE is null)))
1192 AND ((tlinfo.OVERRIDE_ID = X_OVERRIDE_ID)
1193 OR ((tlinfo.OVERRIDE_ID is null)
1194 AND (X_OVERRIDE_ID is null)))
1195 AND ((TRUNC(tlinfo.OVERRIDE_DATE) = TRUNC(X_OVERRIDE_DATE))
1196 OR ((tlinfo.OVERRIDE_DATE is null)
1197 AND (X_OVERRIDE_DATE is null)))
1198 AND ((TRUNC(tlinfo.DATE_OF_ISSUE) = TRUNC(X_DATE_OF_ISSUE))
1199 OR ((tlinfo.DATE_OF_ISSUE is null)
1200 AND (X_DATE_OF_ISSUE is null)))
1201 -- Added for new field Ravishar / 14-sep-2004
1202 AND ((tlinfo.CORE_CURRICULUM_VALUE = X_CORE_CURRICULUM_VALUE)
1203 OR ((tlinfo.CORE_CURRICULUM_VALUE is null)
1204 AND (X_CORE_CURRICULUM_VALUE is null)))
1205 then null;
1206 else
1207 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1208 IGS_GE_MSG_STACK.ADD;
1209 app_exception.raise_exception;
1210 end if;
1211 return;
1212 end LOCK_ROW;
1213 Procedure UPDATE_ROW (
1214 X_ROWID in VARCHAR2,
1215 x_QUINTILE_RANK IN NUMBER,
1216 x_PERCENTILE_RANK IN NUMBER,
1217 x_TRANSCRIPT_ID IN NUMBER,
1218 x_EDUCATION_ID IN NUMBER,
1219 x_TRANSCRIPT_STATUS IN VARCHAR2,
1220 x_TRANSCRIPT_SOURCE IN NUMBER,
1221 x_DATE_OF_RECEIPT IN DATE,
1222 x_ENTERED_GPA IN VARCHAR2,
1223 x_ENTERED_GS_ID IN NUMBER,
1224 x_CONV_GPA IN VARCHAR2,
1225 x_CONV_GS_ID IN NUMBER,
1226 x_TERM_TYPE IN VARCHAR2,
1227 x_RANK_IN_CLASS IN NUMBER,
1228 x_CLASS_SIZE IN NUMBER,
1229 x_APPROXIMATE_RANK IN VARCHAR2,
1230 x_WEIGHTED_RANK IN VARCHAR2,
1231 x_DECILE_RANK IN NUMBER,
1232 x_QUARTILE_RANK IN NUMBER,
1233 x_TRANSCRIPT_TYPE IN VARCHAR2 DEFAULT NULL,
1234 X_MODE in VARCHAR2 DEFAULT NULL,
1235 x_OVERRIDE IN VARCHAR2 DEFAULT NULL,
1236 x_OVERRIDE_ID IN NUMBER DEFAULT NULL,
1237 x_OVERRIDE_DATE IN DATE DEFAULT NULL,
1238 X_DATE_OF_ISSUE IN DATE DEFAULT NULL,
1239 -- Added for new field Ravishar / 14-sep-2004
1240 x_CORE_CURRICULUM_VALUE IN VARCHAR2 DEFAULT NULL
1241 ) AS
1242 /*************************************************************
1243 Created By : Kamalakar N.
1244 Date Created By : 15/May/2000
1245 Purpose :
1246 Know limitations, enhancements or remarks
1247 Change History
1248 Who When What
1249
1250 (reverse chronological order - newest change first)
1251 vdixit.in 23-JULY-2001 Added new column transcript_type
1252 to the tbh calls
1253 rboddu 12-NOV-2001 Added the new columns override,
1254 override_id and override_date
1255 Bug No : 2097333
1256 ***************************************************************/
1257
1258 X_LAST_UPDATE_DATE DATE ;
1259 X_LAST_UPDATED_BY NUMBER ;
1260 X_LAST_UPDATE_LOGIN NUMBER ;
1261 X_REQUEST_ID NUMBER;
1262 X_PROGRAM_ID NUMBER;
1263 X_PROGRAM_APPLICATION_ID NUMBER;
1264 X_PROGRAM_UPDATE_DATE DATE;
1265 L_MODE VARCHAR2(1);
1266 begin
1267
1268 L_MODE := NVL(X_MODE, 'R');
1269
1270 X_LAST_UPDATE_DATE := SYSDATE;
1271 if(L_MODE = 'I') then
1272 X_LAST_UPDATED_BY := 1;
1273 X_LAST_UPDATE_LOGIN := 0;
1274 elsif (L_MODE IN ('R','S')) then
1275 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1276 if X_LAST_UPDATED_BY is NULL then
1277 X_LAST_UPDATED_BY := -1;
1278 end if;
1279 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1280 if X_LAST_UPDATE_LOGIN is NULL then
1281 X_LAST_UPDATE_LOGIN := -1;
1282 end if;
1283 else
1284 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1285 IGS_GE_MSG_STACK.ADD;
1286 app_exception.raise_exception;
1287 end if;
1288 Before_DML(
1289 p_action=>'UPDATE',
1290 x_rowid=>X_ROWID,
1291 x_quintile_rank=>X_QUINTILE_RANK,
1292 x_percentile_rank=>X_PERCENTILE_RANK,
1293 x_transcript_id=>X_TRANSCRIPT_ID,
1294 x_education_id=>X_EDUCATION_ID,
1295 x_transcript_status=>X_TRANSCRIPT_STATUS,
1296 x_transcript_source=>X_TRANSCRIPT_SOURCE,
1297 x_date_of_receipt=>X_DATE_OF_RECEIPT,
1298 x_entered_gpa=>X_ENTERED_GPA,
1299 x_entered_gs_id=>X_ENTERED_GS_ID,
1300 x_conv_gpa=>X_CONV_GPA,
1301 x_conv_gs_id=>X_CONV_GS_ID,
1302 x_term_type=>X_TERM_TYPE,
1303 x_rank_in_class=>X_RANK_IN_CLASS,
1304 x_class_size=>X_CLASS_SIZE,
1305 x_approximate_rank=>X_APPROXIMATE_RANK,
1306 x_weighted_rank=>X_WEIGHTED_RANK,
1307 x_decile_rank=>X_DECILE_RANK,
1308 x_quartile_rank=>X_QUARTILE_RANK,
1309 x_transcript_type=>X_TRANSCRIPT_TYPE,
1310 x_creation_date=>X_LAST_UPDATE_DATE,
1311 x_created_by=>X_LAST_UPDATED_BY,
1312 x_last_update_date=>X_LAST_UPDATE_DATE,
1313 x_last_updated_by=>X_LAST_UPDATED_BY,
1314 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1315 x_override=>X_OVERRIDE,
1316 x_override_id=>X_OVERRIDE_ID,
1317 x_override_date=>X_OVERRIDE_DATE,
1318 x_date_of_issue => x_date_of_issue,
1319 -- Added for new field Ravishar / 14-sep-2004
1320 x_core_curriculum_value => X_CORE_CURRICULUM_VALUE
1321 );
1322
1323 if (L_MODE IN ('R','S')) then
1324 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1325 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1326 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1327 if (X_REQUEST_ID = -1) then
1328 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1329 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1330 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1331 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1332 else
1333 X_PROGRAM_UPDATE_DATE := SYSDATE;
1334 end if;
1335 end if;
1336
1337 IF (x_mode = 'S') THEN
1338 igs_sc_gen_001.set_ctx('R');
1339 END IF;
1340 update IGS_AD_TRANSCRIPT set
1341 QUINTILE_RANK = NEW_REFERENCES.QUINTILE_RANK,
1342 PERCENTILE_RANK = NEW_REFERENCES.PERCENTILE_RANK,
1343 EDUCATION_ID = NEW_REFERENCES.EDUCATION_ID,
1344 TRANSCRIPT_STATUS = NEW_REFERENCES.TRANSCRIPT_STATUS,
1345 TRANSCRIPT_SOURCE = NEW_REFERENCES.TRANSCRIPT_SOURCE,
1346 DATE_OF_RECEIPT = NEW_REFERENCES.DATE_OF_RECEIPT,
1347 ENTERED_GPA = NEW_REFERENCES.ENTERED_GPA,
1348 ENTERED_GS_ID = NEW_REFERENCES.ENTERED_GS_ID,
1349 CONV_GPA = NEW_REFERENCES.CONV_GPA,
1350 CONV_GS_ID = NEW_REFERENCES.CONV_GS_ID,
1351 TERM_TYPE = NEW_REFERENCES.TERM_TYPE,
1352 RANK_IN_CLASS = NEW_REFERENCES.RANK_IN_CLASS,
1353 CLASS_SIZE = NEW_REFERENCES.CLASS_SIZE,
1354 APPROXIMATE_RANK = NEW_REFERENCES.APPROXIMATE_RANK,
1355 WEIGHTED_RANK = NEW_REFERENCES.WEIGHTED_RANK,
1356 DECILE_RANK = NEW_REFERENCES.DECILE_RANK,
1357 QUARTILE_RANK = NEW_REFERENCES.QUARTILE_RANK,
1358 TRANSCRIPT_TYPE = NEW_REFERENCES.TRANSCRIPT_TYPE,
1359 OVERRIDE = NEW_REFERENCES.OVERRIDE,
1360 OVERRIDE_ID = NEW_REFERENCES.OVERRIDE_ID,
1361 OVERRIDE_DATE = NEW_REFERENCES.OVERRIDE_DATE,
1362 DATE_OF_ISSUE = NEW_REFERENCES.DATE_OF_ISSUE,
1363 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1364 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1365 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1366 , REQUEST_ID = X_REQUEST_ID,
1367 PROGRAM_ID = X_PROGRAM_ID,
1368 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
1369 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
1370 -- Added for new field Ravishar / 14-sep-2004
1371 CORE_CURRICULUM_VALUE = X_CORE_CURRICULUM_VALUE
1372 where ROWID = X_ROWID;
1373 if (sql%notfound) then
1374 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1375 igs_ge_msg_stack.add;
1376 igs_sc_gen_001.unset_ctx('R');
1377 app_exception.raise_exception;
1378 end if;
1379 IF (x_mode = 'S') THEN
1380 igs_sc_gen_001.unset_ctx('R');
1381 END IF;
1382
1383
1384 After_DML (
1385 p_action => 'UPDATE' ,
1386 x_rowid => X_ROWID
1387 );
1388 EXCEPTION
1389 WHEN OTHERS THEN
1390 IF (SQLCODE = (-28115)) THEN
1391 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1392 fnd_message.set_token ('ERR_CD', SQLCODE);
1393 igs_ge_msg_stack.add;
1394 igs_sc_gen_001.unset_ctx('R');
1395 app_exception.raise_exception;
1396 ELSE
1397 igs_sc_gen_001.unset_ctx('R');
1398 RAISE;
1399 END IF;
1400
1401 end UPDATE_ROW;
1402 procedure ADD_ROW (
1403 X_ROWID in out NOCOPY VARCHAR2,
1404 x_QUINTILE_RANK IN NUMBER,
1405 x_PERCENTILE_RANK IN NUMBER,
1406 x_TRANSCRIPT_ID IN OUT NOCOPY NUMBER,
1407 x_EDUCATION_ID IN NUMBER,
1408 x_TRANSCRIPT_STATUS IN VARCHAR2,
1409 x_TRANSCRIPT_SOURCE IN NUMBER,
1410 x_DATE_OF_RECEIPT IN DATE,
1411 x_ENTERED_GPA IN VARCHAR2,
1412 x_ENTERED_GS_ID IN NUMBER,
1413 x_CONV_GPA IN VARCHAR2,
1414 x_CONV_GS_ID IN NUMBER,
1415 x_TERM_TYPE IN VARCHAR2,
1416 x_RANK_IN_CLASS IN NUMBER,
1417 x_CLASS_SIZE IN NUMBER,
1418 x_APPROXIMATE_RANK IN VARCHAR2,
1419 x_WEIGHTED_RANK IN VARCHAR2,
1420 x_DECILE_RANK IN NUMBER,
1421 x_QUARTILE_RANK IN NUMBER,
1422 x_TRANSCRIPT_TYPE IN VARCHAR2 DEFAULT NULL,
1423 X_MODE in VARCHAR2 DEFAULT NULL,
1424 x_OVERRIDE IN VARCHAR2 DEFAULT NULL,
1425 x_OVERRIDE_ID IN NUMBER DEFAULT NULL,
1426 X_OVERRIDE_DATE IN DATE DEFAULT NULL,
1427 X_DATE_OF_ISSUE IN DATE DEFAULT NULL,
1428 -- Added for new field Ravishar / 14-sep-2004
1429 X_CORE_CURRICULUM_VALUE IN VARCHAR2 DEFAULT NULL
1430
1431 ) AS
1432 /*************************************************************
1433 Created By : Kamalakar N.
1434 Date Created By : 15/May/2000
1435 Purpose :
1436 Know limitations, enhancements or remarks
1437 Change History
1438 Who When What
1439
1440 (reverse chronological order - newest change first)
1441 vdixit.in 23-JULY-2001 Added new column transcript_type
1442 to the tbh calls
1443 rboddu 10-NOV-2001 Added new columns override,
1444 override_id and override_Date
1445 Bug No: 2097333
1446
1447 ***************************************************************/
1448
1449 cursor c1 is select ROWID from IGS_AD_TRANSCRIPT
1450 where TRANSCRIPT_ID= X_TRANSCRIPT_ID
1451 ;
1452 L_MODE VARCHAR2(1);
1453 begin
1454 L_MODE := NVL(X_MODE, 'R');
1455
1456 open c1;
1457 fetch c1 into X_ROWID;
1458 if (c1%notfound) then
1459 close c1;
1460 INSERT_ROW (
1461 X_ROWID,
1462 X_QUINTILE_RANK,
1463 X_PERCENTILE_RANK,
1464 X_TRANSCRIPT_ID,
1465 X_EDUCATION_ID,
1466 X_TRANSCRIPT_STATUS,
1467 X_TRANSCRIPT_SOURCE,
1468 X_DATE_OF_RECEIPT,
1469 X_ENTERED_GPA,
1470 X_ENTERED_GS_ID,
1471 X_CONV_GPA,
1472 X_CONV_GS_ID,
1473 X_TERM_TYPE,
1474 X_RANK_IN_CLASS,
1475 X_CLASS_SIZE,
1476 X_APPROXIMATE_RANK,
1477 X_WEIGHTED_RANK,
1478 X_DECILE_RANK,
1479 X_QUARTILE_RANK,
1480 X_TRANSCRIPT_TYPE,
1481 L_MODE,
1482 X_OVERRIDE,
1483 X_OVERRIDE_ID,
1484 X_OVERRIDE_DATE,
1485 X_DATE_OF_ISSUE,
1486 -- Added for new field Ravishar / 14-sep-2004
1487 X_CORE_CURRICULUM_VALUE
1488 );
1489 return;
1490 end if;
1491 close c1;
1492 UPDATE_ROW (
1493 X_ROWID,
1494 X_QUINTILE_RANK,
1495 X_PERCENTILE_RANK,
1496 X_TRANSCRIPT_ID,
1497 X_EDUCATION_ID,
1498 X_TRANSCRIPT_STATUS,
1499 X_TRANSCRIPT_SOURCE,
1500 X_DATE_OF_RECEIPT,
1501 X_ENTERED_GPA,
1502 X_ENTERED_GS_ID,
1503 X_CONV_GPA,
1504 X_CONV_GS_ID,
1505 X_TERM_TYPE,
1506 X_RANK_IN_CLASS,
1507 X_CLASS_SIZE,
1508 X_APPROXIMATE_RANK,
1509 X_WEIGHTED_RANK,
1510 X_DECILE_RANK,
1511 X_QUARTILE_RANK,
1512 X_TRANSCRIPT_TYPE,
1513 L_MODE,
1514 X_OVERRIDE,
1515 X_OVERRIDE_ID,
1516 X_OVERRIDE_DATE,
1517 X_DATE_OF_ISSUE,
1518 -- Added for new field Ravishar / 14-sep-2004
1519 X_CORE_CURRICULUM_VALUE
1520 );
1521 end ADD_ROW;
1522 procedure DELETE_ROW (
1523 X_ROWID in VARCHAR2,
1524 x_mode IN VARCHAR2
1525 ) AS
1526 /*************************************************************
1527 Created By : Kamalakar N.
1528 Date Created By : 15/May/2000
1529 Purpose :
1530 Know limitations, enhancements or remarks
1531 Change History
1532 Who When What
1533
1534 (reverse chronological order - newest change first)
1535 ***************************************************************/
1536
1537 begin
1538 Before_DML (
1539 p_action => 'DELETE',
1540 x_rowid => X_ROWID
1541 );
1542 IF (x_mode = 'S') THEN
1543 igs_sc_gen_001.set_ctx('R');
1544 END IF;
1545 delete from IGS_AD_TRANSCRIPT
1546 where ROWID = X_ROWID;
1547 if (sql%notfound) then
1548 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1549 igs_ge_msg_stack.add;
1550 igs_sc_gen_001.unset_ctx('R');
1551 app_exception.raise_exception;
1552 end if;
1553 IF (x_mode = 'S') THEN
1554 igs_sc_gen_001.unset_ctx('R');
1555 END IF;
1556
1557 After_DML (
1558 p_action => 'DELETE',
1559 x_rowid => X_ROWID
1560 );
1561 end DELETE_ROW;
1562 END igs_ad_transcript_pkg;
[Home] [Help]