[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_HZ_ACAD_HIST_PKG
Source
1 PACKAGE BODY igs_ad_hz_acad_hist_pkg AS
2 /* $Header: IGSAIB7B.pls 120.1 2005/06/28 04:24:21 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_hz_acad_hist%ROWTYPE;
6 new_references igs_ad_hz_acad_hist%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_recalc_total_cp_attempted IN NUMBER DEFAULT NULL,
12 x_recalc_total_cp_earned IN NUMBER DEFAULT NULL,
13 x_recalc_total_unit_gp IN NUMBER DEFAULT NULL,
14 x_recalc_tot_gpa_uts_attempted IN NUMBER DEFAULT NULL,
15 x_recalc_inst_gpa IN VARCHAR2 DEFAULT NULL,
16 x_recalc_grading_scale_id IN NUMBER DEFAULT NULL,
17 x_selfrep_total_cp_attempted IN NUMBER DEFAULT NULL,
18 x_selfrep_total_cp_earned IN NUMBER DEFAULT NULL,
19 x_selfrep_total_unit_gp IN NUMBER DEFAULT NULL,
20 x_selfrep_tot_gpa_uts_attemp IN NUMBER DEFAULT NULL,
21 x_selfrep_inst_gpa IN VARCHAR2 DEFAULT NULL,
22 x_selfrep_grading_scale_id IN NUMBER DEFAULT NULL,
23 x_selfrep_weighted_gpa IN VARCHAR2 DEFAULT NULL,
24 x_selfrep_rank_in_class IN NUMBER DEFAULT NULL,
25 x_selfrep_weighed_rank IN VARCHAR2 DEFAULT NULL,
26 x_selfrep_class_size IN NUMBER DEFAULT NULL,
27 x_attribute_category IN VARCHAR2 DEFAULT NULL,
28 x_attribute1 IN VARCHAR2 DEFAULT NULL,
29 x_attribute2 IN VARCHAR2 DEFAULT NULL,
30 x_attribute3 IN VARCHAR2 DEFAULT NULL,
31 x_attribute4 IN VARCHAR2 DEFAULT NULL,
32 x_attribute5 IN VARCHAR2 DEFAULT NULL,
33 x_attribute6 IN VARCHAR2 DEFAULT NULL,
34 x_attribute7 IN VARCHAR2 DEFAULT NULL,
35 x_attribute8 IN VARCHAR2 DEFAULT NULL,
36 x_attribute9 IN VARCHAR2 DEFAULT NULL,
37 x_attribute10 IN VARCHAR2 DEFAULT NULL,
38 x_attribute11 IN VARCHAR2 DEFAULT NULL,
39 x_attribute12 IN VARCHAR2 DEFAULT NULL,
40 x_attribute13 IN VARCHAR2 DEFAULT NULL,
41 x_attribute14 IN VARCHAR2 DEFAULT NULL,
42 x_attribute15 IN VARCHAR2 DEFAULT NULL,
43 x_attribute16 IN VARCHAR2 DEFAULT NULL,
44 x_attribute17 IN VARCHAR2 DEFAULT NULL,
45 x_attribute18 IN VARCHAR2 DEFAULT NULL,
46 x_attribute19 IN VARCHAR2 DEFAULT NULL,
47 x_attribute20 IN VARCHAR2 DEFAULT NULL,
48 x_hz_acad_hist_id IN NUMBER DEFAULT NULL,
49 x_education_id IN NUMBER DEFAULT NULL,
50 x_current_inst IN VARCHAR2 DEFAULT NULL,
51 x_degree_attempted IN VARCHAR2 DEFAULT NULL,
52 x_comments IN VARCHAR2 DEFAULT NULL,
53 x_planned_completion_date IN DATE DEFAULT NULL,
54 x_transcript_required IN VARCHAR2 DEFAULT NULL,
55 x_creation_date IN DATE DEFAULT NULL,
56 x_created_by IN NUMBER DEFAULT NULL,
57 x_last_update_date IN DATE DEFAULT NULL,
58 x_last_updated_by IN NUMBER DEFAULT NULL,
59 x_last_update_login IN NUMBER DEFAULT NULL
60 ) AS
61 /*
62 || Created By : [email protected]
63 || Created On : 29-AUG-2000
64 || Purpose : Initialises the Old and New references for the columns of the table.
65 || Known limitations, enhancements or remarks :
66 || Change History :
67 || Who When What
68 || (reverse chronological order - newest change first)
69 || vdixit.in 23-JULY-2001 Added new column transcript_required
70 || to the tbh calls
71
72 */
73
74 CURSOR cur_old_ref_values IS
75 SELECT *
76 FROM IGS_AD_HZ_ACAD_HIST
77 WHERE rowid = x_rowid;
78
79 BEGIN
80
81 l_rowid := x_rowid;
82
83 -- Code for setting the Old and New Reference Values.
84 -- Populate Old Values.
85 OPEN cur_old_ref_values;
86 FETCH cur_old_ref_values INTO old_references;
87 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
88 CLOSE cur_old_ref_values;
89 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
90 igs_ge_msg_stack.add;
91 app_exception.raise_exception;
92 RETURN;
93 END IF;
94 CLOSE cur_old_ref_values;
95
96 -- Populate New Values.
97 new_references.recalc_total_cp_attempted := x_recalc_total_cp_attempted;
98 new_references.recalc_total_cp_earned := x_recalc_total_cp_earned;
99 new_references.recalc_total_unit_gp := x_recalc_total_unit_gp;
100 new_references.recalc_tot_gpa_uts_attempted := x_recalc_tot_gpa_uts_attempted;
101 new_references.recalc_inst_gpa := x_recalc_inst_gpa;
102 new_references.recalc_grading_scale_id := x_recalc_grading_scale_id;
103 new_references.selfrep_total_cp_attempted := x_selfrep_total_cp_attempted;
104 new_references.selfrep_total_cp_earned := x_selfrep_total_cp_earned;
105 new_references.selfrep_total_unit_gp := x_selfrep_total_unit_gp;
106 new_references.selfrep_tot_gpa_uts_attemp := x_selfrep_tot_gpa_uts_attemp;
107 new_references.selfrep_inst_gpa := x_selfrep_inst_gpa;
108 new_references.selfrep_grading_scale_id := x_selfrep_grading_scale_id;
109 new_references.selfrep_weighted_gpa := x_selfrep_weighted_gpa;
110 new_references.selfrep_rank_in_class := x_selfrep_rank_in_class;
111 new_references.selfrep_weighed_rank := x_selfrep_weighed_rank;
112 new_references.selfrep_class_size := x_selfrep_class_size;
113 new_references.attribute_category := x_attribute_category;
114 new_references.attribute1 := x_attribute1;
115 new_references.attribute2 := x_attribute2;
116 new_references.attribute3 := x_attribute3;
117 new_references.attribute4 := x_attribute4;
118 new_references.attribute5 := x_attribute5;
119 new_references.attribute6 := x_attribute6;
120 new_references.attribute7 := x_attribute7;
121 new_references.attribute8 := x_attribute8;
122 new_references.attribute9 := x_attribute9;
123 new_references.attribute10 := x_attribute10;
124 new_references.attribute11 := x_attribute11;
125 new_references.attribute12 := x_attribute12;
126 new_references.attribute13 := x_attribute13;
127 new_references.attribute14 := x_attribute14;
128 new_references.attribute15 := x_attribute15;
129 new_references.attribute16 := x_attribute16;
130 new_references.attribute17 := x_attribute17;
131 new_references.attribute18 := x_attribute18;
132 new_references.attribute19 := x_attribute19;
133 new_references.attribute20 := x_attribute20;
134 new_references.hz_acad_hist_id := x_hz_acad_hist_id;
135 new_references.education_id := x_education_id;
136 new_references.current_inst := x_current_inst;
137 new_references.degree_attempted := x_degree_attempted;
138 new_references.comments := x_comments;
139 new_references.planned_completion_date := TRUNC(x_planned_completion_date);
140 new_references.transcript_required := x_transcript_required;
141
142 IF (p_action = 'UPDATE') THEN
143 new_references.creation_date := old_references.creation_date;
144 new_references.created_by := old_references.created_by;
145 ELSE
146 new_references.creation_date := x_creation_date;
147 new_references.created_by := x_created_by;
148 END IF;
149
150 new_references.last_update_date := x_last_update_date;
151 new_references.last_updated_by := x_last_updated_by;
152 new_references.last_update_login := x_last_update_login;
153
154 END set_column_values;
155
156
157 PROCEDURE Check_Constraints (
158 Column_Name IN VARCHAR2 DEFAULT NULL,
159 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
160 /*************************************************************
161 Created By : Veereshwar Dixit
162 Date Created By : 24-July-2001
163 Purpose :
164 Know limitations, enhancements or remarks
165 Change History
166 Who When What
167
168 (reverse chronological order - newest change first)
169 vdixit.in 23-JULY-2001 Added check constraint for the
170 new column transcript_required
171
172 ***************************************************************/
173
174 BEGIN
175
176 IF column_name IS NULL THEN
177 NULL;
178 ELSIF UPPER(column_name) = 'TRANSCRIPT_REQUIRED' THEN
179 new_references.transcript_required := column_value;
180 END IF;
181
182
183 /*IF Upper(Column_Name) = 'TRANSCRIPT_REQUIRED' OR
184 Column_Name IS NULL THEN
185 IF NOT (new_references.transcript_required in ('Y','N')) THEN
186 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
187 IGS_GE_MSG_STACK.ADD;
188 App_Exception.Raise_Exception;
189 END IF;
190 END IF;*/
191
192 END Check_Constraints;
193 PROCEDURE check_parent_existance AS
194 CURSOR cur_rowid IS
195 SELECT rowid
196 FROM hz_education
197 WHERE education_id = new_references.education_id ;
198 lv_rowid cur_rowid%RowType;
199 /*
200 || Created By : [email protected]
201 || Created On : 29-AUG-2000
202 || Purpose : Checks for the existance of Parent records.
203 || Known limitations, enhancements or remarks :
204 || Change History :
205 || Who When What
206 || (reverse chronological order - newest change first)
207 || vdixit.in 23-JULY-2001 Added new column transcript_required
208 || to the tbh calls
209 */
210 BEGIN
211
212 IF (((old_references.education_id = new_references.education_id)) OR
213 ((new_references.education_id IS NULL))) THEN
214 NULL;
215 ELSE
216 Open cur_rowid;
217 Fetch cur_rowid INTO lv_rowid;
218 IF (cur_rowid%NOTFOUND) THEN
219 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
220 IGS_GE_MSG_STACK.ADD;
221 App_Exception.Raise_Exception;
222 END IF;
223 Close cur_rowid;
224
225 END IF;
226
227 END check_parent_existance;
228
229 FUNCTION get_pk_for_validation (
230 x_hz_acad_hist_id IN NUMBER
231 ) RETURN BOOLEAN AS
232 /*
233 || Created By : [email protected]
234 || Created On : 29-AUG-2000
235 || Purpose : Validates the Primary Key of the table.
236 || Known limitations, enhancements or remarks :
237 || Change History :
238 || Who When What
239 || (reverse chronological order - newest change first)
240 || vdixit.in 23-JULY-2001 Added new column transcript_required
241 || to the tbh calls
242 */
243 CURSOR cur_rowid IS
244 SELECT rowid
245 FROM igs_ad_hz_acad_hist
246 WHERE hz_acad_hist_id = x_hz_acad_hist_id
247 FOR UPDATE NOWAIT;
248
249 lv_rowid cur_rowid%RowType;
250
251 BEGIN
252
253 OPEN cur_rowid;
254 FETCH cur_rowid INTO lv_rowid;
255 IF (cur_rowid%FOUND) THEN
256 CLOSE cur_rowid;
257 RETURN(TRUE);
258 ELSE
259 CLOSE cur_rowid;
260 RETURN(FALSE);
261 END IF;
262
263 END get_pk_for_validation;
264
265 PROCEDURE get_fk_hz_education (
266 x_education_id IN NUMBER
267 ) AS
268 /*
269 || Created By : [email protected]
270 || Created On : 29-AUG-2000
271 || Purpose : Validates the Foreign Keys for the table.
272 || Known limitations, enhancements or remarks :
273 || Change History :
274 || Who When What
275 || (reverse chronological order - newest change first)
276 */
277 CURSOR cur_rowid IS
278 SELECT rowid
279 FROM igs_ad_hz_acad_hist
280 WHERE ((education_id = x_education_id));
281
282 lv_rowid cur_rowid%RowType;
283
284 BEGIN
285
286 OPEN cur_rowid;
287 FETCH cur_rowid INTO lv_rowid;
288 IF (cur_rowid%FOUND) THEN
289 CLOSE cur_rowid;
290 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
291 igs_ge_msg_stack.add;
292 app_exception.raise_exception;
293 RETURN;
294 END IF;
295 CLOSE cur_rowid;
296
297 END get_fk_hz_education;
298
299
300 PROCEDURE before_dml (
301 p_action IN VARCHAR2,
302 x_rowid IN VARCHAR2 DEFAULT NULL,
303 x_recalc_total_cp_attempted IN NUMBER DEFAULT NULL,
304 x_recalc_total_cp_earned IN NUMBER DEFAULT NULL,
308 x_recalc_grading_scale_id IN NUMBER DEFAULT NULL,
305 x_recalc_total_unit_gp IN NUMBER DEFAULT NULL,
306 x_recalc_tot_gpa_uts_attempted IN NUMBER DEFAULT NULL,
307 x_recalc_inst_gpa IN VARCHAR2 DEFAULT NULL,
309 x_selfrep_total_cp_attempted IN NUMBER DEFAULT NULL,
310 x_selfrep_total_cp_earned IN NUMBER DEFAULT NULL,
311 x_selfrep_total_unit_gp IN NUMBER DEFAULT NULL,
312 x_selfrep_tot_gpa_uts_attemp IN NUMBER DEFAULT NULL,
313 x_selfrep_inst_gpa IN VARCHAR2 DEFAULT NULL,
314 x_selfrep_grading_scale_id IN NUMBER DEFAULT NULL,
315 x_selfrep_weighted_gpa IN VARCHAR2 DEFAULT NULL,
316 x_selfrep_rank_in_class IN NUMBER DEFAULT NULL,
317 x_selfrep_weighed_rank IN VARCHAR2 DEFAULT NULL,
318 x_selfrep_class_size IN NUMBER DEFAULT NULL,
319 x_attribute_category IN VARCHAR2 DEFAULT NULL,
320 x_attribute1 IN VARCHAR2 DEFAULT NULL,
321 x_attribute2 IN VARCHAR2 DEFAULT NULL,
322 x_attribute3 IN VARCHAR2 DEFAULT NULL,
323 x_attribute4 IN VARCHAR2 DEFAULT NULL,
324 x_attribute5 IN VARCHAR2 DEFAULT NULL,
325 x_attribute6 IN VARCHAR2 DEFAULT NULL,
326 x_attribute7 IN VARCHAR2 DEFAULT NULL,
327 x_attribute8 IN VARCHAR2 DEFAULT NULL,
328 x_attribute9 IN VARCHAR2 DEFAULT NULL,
329 x_attribute10 IN VARCHAR2 DEFAULT NULL,
330 x_attribute11 IN VARCHAR2 DEFAULT NULL,
331 x_attribute12 IN VARCHAR2 DEFAULT NULL,
332 x_attribute13 IN VARCHAR2 DEFAULT NULL,
333 x_attribute14 IN VARCHAR2 DEFAULT NULL,
334 x_attribute15 IN VARCHAR2 DEFAULT NULL,
335 x_attribute16 IN VARCHAR2 DEFAULT NULL,
336 x_attribute17 IN VARCHAR2 DEFAULT NULL,
337 x_attribute18 IN VARCHAR2 DEFAULT NULL,
338 x_attribute19 IN VARCHAR2 DEFAULT NULL,
339 x_attribute20 IN VARCHAR2 DEFAULT NULL,
340 x_hz_acad_hist_id IN NUMBER DEFAULT NULL,
341 x_education_id IN NUMBER DEFAULT NULL,
342 x_current_inst IN VARCHAR2 DEFAULT NULL,
343 x_degree_attempted IN VARCHAR2 ,
344 x_program_type_attempted IN VARCHAR2 ,
345 x_comments IN VARCHAR2 DEFAULT NULL,
346 x_planned_completion_date IN DATE DEFAULT NULL,
347 x_transcript_required IN VARCHAR2 DEFAULT NULL,
348 x_creation_date IN DATE DEFAULT NULL,
349 x_created_by IN NUMBER DEFAULT NULL,
350 x_last_update_date IN DATE DEFAULT NULL,
351 x_last_updated_by IN NUMBER DEFAULT NULL,
352 x_last_update_login IN NUMBER DEFAULT NULL
353 ) AS
354 /*
355 || Created By : [email protected]
356 || Created On : 29-AUG-2000
357 || Purpose : Initialises the columns, Checks Constraints, Calls the
358 || Trigger Handlers for the table, before any DML operation.
359 || Known limitations, enhancements or remarks :
360 || Change History :
361 || Who When What
362 || (reverse chronological order - newest change first)
363 || vdixit.in 23-JULY-2001 Added new column transcript_required
364 || to the tbh calls
365 */
366 BEGIN
367
368 set_column_values (
369 p_action,
370 x_rowid,
371 x_recalc_total_cp_attempted,
372 x_recalc_total_cp_earned,
373 x_recalc_total_unit_gp,
374 x_recalc_tot_gpa_uts_attempted,
375 x_recalc_inst_gpa,
376 x_recalc_grading_scale_id,
377 x_selfrep_total_cp_attempted,
378 x_selfrep_total_cp_earned,
379 x_selfrep_total_unit_gp,
380 x_selfrep_tot_gpa_uts_attemp,
381 x_selfrep_inst_gpa,
382 x_selfrep_grading_scale_id,
383 x_selfrep_weighted_gpa,
384 x_selfrep_rank_in_class,
385 x_selfrep_weighed_rank,
386 x_selfrep_class_size,
387 x_attribute_category,
388 x_attribute1,
389 x_attribute2,
390 x_attribute3,
391 x_attribute4,
392 x_attribute5,
393 x_attribute6,
394 x_attribute7,
395 x_attribute8,
396 x_attribute9,
397 x_attribute10,
398 x_attribute11,
399 x_attribute12,
400 x_attribute13,
401 x_attribute14,
402 x_attribute15,
403 x_attribute16,
404 x_attribute17,
405 x_attribute18,
406 x_attribute19,
407 x_attribute20,
408 x_hz_acad_hist_id,
409 x_education_id,
410 x_current_inst,
411 x_degree_attempted,
412 x_comments,
413 x_planned_completion_date,
417 x_last_update_date,
414 x_transcript_required,
415 x_creation_date,
416 x_created_by,
418 x_last_updated_by,
419 x_last_update_login
420 );
421
422 IF (p_action = 'INSERT') THEN
423 -- Call all the procedures related to Before Insert.
424 IF ( get_pk_for_validation(
425 new_references.hz_acad_hist_id
426 )
427 ) THEN
428 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
429 igs_ge_msg_stack.add;
430 app_exception.raise_exception;
431 END IF;
432 Check_Constraints;
433 check_parent_existance;
434 ELSIF (p_action = 'UPDATE') THEN
435 -- Call all the procedures related to Before Update.
436 Check_Constraints;
437 check_parent_existance;
438 ELSIF (p_action = 'DELETE') THEN
439 NULL;
440 -- Call all the procedures related to Before Delete.
441 -- check_child_existance;
442 ELSIF (p_action = 'VALIDATE_INSERT') THEN
443 -- Call all the procedures related to Before Insert.
444 IF ( get_pk_for_validation (
445 new_references.hz_acad_hist_id
446 )
447 ) THEN
448 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
449 igs_ge_msg_stack.add;
450 app_exception.raise_exception;
451 END IF;
452 Check_Constraints;
453 END IF;
454
455 END before_dml;
456
457
458 PROCEDURE insert_row (
459 x_rowid IN OUT NOCOPY VARCHAR2,
460 x_recalc_total_cp_attempted IN NUMBER,
461 x_recalc_total_cp_earned IN NUMBER,
462 x_recalc_total_unit_gp IN NUMBER,
463 x_recalc_tot_gpa_uts_attempted IN NUMBER,
464 x_recalc_inst_gpa IN VARCHAR2,
465 x_recalc_grading_scale_id IN NUMBER,
466 x_selfrep_total_cp_attempted IN NUMBER,
467 x_selfrep_total_cp_earned IN NUMBER,
468 x_selfrep_total_unit_gp IN NUMBER,
469 x_selfrep_tot_gpa_uts_attemp IN NUMBER,
470 x_selfrep_inst_gpa IN VARCHAR2,
471 x_selfrep_grading_scale_id IN NUMBER,
472 x_selfrep_weighted_gpa IN VARCHAR2,
473 x_selfrep_rank_in_class IN NUMBER,
474 x_selfrep_weighed_rank IN VARCHAR2,
475 x_selfrep_class_size IN NUMBER DEFAULT NULL,
476 x_attribute_category IN VARCHAR2,
477 x_attribute1 IN VARCHAR2,
478 x_attribute2 IN VARCHAR2,
479 x_attribute3 IN VARCHAR2,
480 x_attribute4 IN VARCHAR2,
481 x_attribute5 IN VARCHAR2,
482 x_attribute6 IN VARCHAR2,
483 x_attribute7 IN VARCHAR2,
484 x_attribute8 IN VARCHAR2,
485 x_attribute9 IN VARCHAR2,
486 x_attribute10 IN VARCHAR2,
487 x_attribute11 IN VARCHAR2,
488 x_attribute12 IN VARCHAR2,
489 x_attribute13 IN VARCHAR2,
490 x_attribute14 IN VARCHAR2,
491 x_attribute15 IN VARCHAR2,
492 x_attribute16 IN VARCHAR2,
493 x_attribute17 IN VARCHAR2,
494 x_attribute18 IN VARCHAR2,
495 x_attribute19 IN VARCHAR2,
496 x_attribute20 IN VARCHAR2,
497 x_hz_acad_hist_id IN OUT NOCOPY NUMBER,
498 x_education_id IN NUMBER,
499 x_current_inst IN VARCHAR2,
500 x_degree_attempted IN VARCHAR2 ,
501 x_program_type_attempted IN VARCHAR2 ,
502 x_comments IN VARCHAR2,
503 x_planned_completion_date IN DATE,
504 x_transcript_required IN VARCHAR2 DEFAULT NULL,
505 x_mode IN VARCHAR2
506 ) AS
507 /*
508 || Created By : [email protected]
509 || Created On : 29-AUG-2000
510 || Purpose : Handles the INSERT DML logic for the table.
511 || Known limitations, enhancements or remarks :
512 || Change History :
513 || Who When What
514 || (reverse chronological order - newest change first)
515 || vdixit.in 23-JULY-2001 Added new column transcript_required
516 || to the tbh calls
517 */
518 CURSOR c IS
519 SELECT rowid
520 FROM igs_ad_hz_acad_hist
521 WHERE hz_acad_hist_id = x_hz_acad_hist_id;
522
523 x_last_update_date DATE;
524 x_last_updated_by NUMBER;
525 x_last_update_login NUMBER;
526 x_request_id NUMBER;
527 x_program_id NUMBER;
528 x_program_application_id NUMBER;
529 x_program_update_date DATE;
530 l_mode VARCHAR2(1);
531 BEGIN
532
533 l_mode := NVL(x_mode,'R');
534
535 x_last_update_date := SYSDATE;
536 IF (l_mode = 'I') THEN
537 x_last_updated_by := 1;
538 x_last_update_login := 0;
539 ELSIF (l_mode = 'R') THEN
540 x_last_updated_by := fnd_global.user_id;
544 x_last_update_login := fnd_global.login_id;
541 IF (x_last_updated_by IS NULL) THEN
542 x_last_updated_by := -1;
543 END IF;
545 IF (x_last_update_login IS NULL) THEN
546 x_last_update_login := -1;
547 END IF;
548 x_request_id:=FND_GLOBAL.CONC_REQUEST_ID;
549 x_program_id:=FND_GLOBAL.CONC_PROGRAM_ID;
550 x_program_application_id:=FND_GLOBAL.PROG_APPL_ID;
551 IF (x_request_id = -1 ) THEN
552 x_request_id:=NULL;
553 x_program_id:=NULL;
554 x_program_application_id:=NULL;
555 x_program_update_date:=NULL;
556 ELSE
557 x_program_update_date:=SYSDATE;
558 END IF;
559 ELSE
560 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
561 igs_ge_msg_stack.add;
562 app_exception.raise_exception;
563 END IF;
564
565 X_HZ_ACAD_HIST_ID := -1;
566 before_dml(
567 p_action => 'INSERT',
568 x_rowid => x_rowid,
569 x_recalc_total_cp_attempted => x_recalc_total_cp_attempted,
570 x_recalc_total_cp_earned => x_recalc_total_cp_earned,
571 x_recalc_total_unit_gp => x_recalc_total_unit_gp,
572 x_recalc_tot_gpa_uts_attempted => x_recalc_tot_gpa_uts_attempted,
573 x_recalc_inst_gpa => x_recalc_inst_gpa,
574 x_recalc_grading_scale_id => x_recalc_grading_scale_id,
575 x_selfrep_total_cp_attempted => x_selfrep_total_cp_attempted,
576 x_selfrep_total_cp_earned => x_selfrep_total_cp_earned,
577 x_selfrep_total_unit_gp => x_selfrep_total_unit_gp,
578 x_selfrep_tot_gpa_uts_attemp => x_selfrep_tot_gpa_uts_attemp,
579 x_selfrep_inst_gpa => x_selfrep_inst_gpa,
580 x_selfrep_grading_scale_id => x_selfrep_grading_scale_id,
581 x_selfrep_weighted_gpa => x_selfrep_weighted_gpa,
582 x_selfrep_rank_in_class => x_selfrep_rank_in_class,
583 x_selfrep_weighed_rank => x_selfrep_weighed_rank,
584 x_selfrep_class_size => x_selfrep_class_size,
585 x_attribute_category => x_attribute_category,
586 x_attribute1 => x_attribute1,
587 x_attribute2 => x_attribute2,
588 x_attribute3 => x_attribute3,
589 x_attribute4 => x_attribute4,
590 x_attribute5 => x_attribute5,
591 x_attribute6 => x_attribute6,
592 x_attribute7 => x_attribute7,
593 x_attribute8 => x_attribute8,
594 x_attribute9 => x_attribute9,
595 x_attribute10 => x_attribute10,
596 x_attribute11 => x_attribute11,
597 x_attribute12 => x_attribute12,
598 x_attribute13 => x_attribute13,
599 x_attribute14 => x_attribute14,
600 x_attribute15 => x_attribute15,
601 x_attribute16 => x_attribute16,
602 x_attribute17 => x_attribute17,
603 x_attribute18 => x_attribute18,
604 x_attribute19 => x_attribute19,
605 x_attribute20 => x_attribute20,
606 x_hz_acad_hist_id => x_hz_acad_hist_id,
607 x_education_id => x_education_id,
608 x_current_inst => x_current_inst,
609 x_degree_attempted => x_degree_attempted,
610 x_comments => x_comments,
611 x_planned_completion_date => x_planned_completion_date,
612 x_transcript_required => x_transcript_required,
613 x_creation_date => x_last_update_date,
614 x_created_by => x_last_updated_by,
615 x_last_update_date => x_last_update_date,
616 x_last_updated_by => x_last_updated_by,
617 x_last_update_login => x_last_update_login
618 );
619
620
621 IF (x_mode = 'S') THEN
622 igs_sc_gen_001.set_ctx('R');
623 END IF;
624 INSERT INTO igs_ad_hz_acad_hist (
625 recalc_total_cp_attempted,
626 recalc_total_cp_earned,
627 recalc_total_unit_gp,
628 recalc_tot_gpa_uts_attempted,
629 recalc_inst_gpa,
630 recalc_grading_scale_id,
631 selfrep_total_cp_attempted,
632 selfrep_total_cp_earned,
633 selfrep_total_unit_gp,
634 selfrep_tot_gpa_uts_attemp,
635 selfrep_inst_gpa,
636 selfrep_grading_scale_id,
637 selfrep_weighted_gpa,
638 selfrep_rank_in_class,
639 selfrep_weighed_rank,
640 selfrep_class_size,
641 attribute_category,
642 attribute1,
643 attribute2,
644 attribute3,
645 attribute4,
646 attribute5,
647 attribute6,
648 attribute7,
649 attribute8,
650 attribute9,
651 attribute10,
652 attribute11,
653 attribute12,
654 attribute13,
655 attribute14,
656 attribute15,
660 attribute19,
657 attribute16,
658 attribute17,
659 attribute18,
661 attribute20,
662 hz_acad_hist_id,
663 education_id,
664 current_inst,
665 degree_attempted,
666 comments,
667 planned_completion_date,
668 transcript_required,
669 creation_date,
670 created_by,
671 last_update_date,
672 last_updated_by,
673 last_update_login,
674 request_id,
675 program_application_id,
676 program_update_date,
677 program_id
678 ) VALUES (
679 new_references.recalc_total_cp_attempted,
680 new_references.recalc_total_cp_earned,
681 new_references.recalc_total_unit_gp,
682 new_references.recalc_tot_gpa_uts_attempted,
683 new_references.recalc_inst_gpa,
684 new_references.recalc_grading_scale_id,
685 new_references.selfrep_total_cp_attempted,
686 new_references.selfrep_total_cp_earned,
687 new_references.selfrep_total_unit_gp,
688 new_references.selfrep_tot_gpa_uts_attemp,
689 new_references.selfrep_inst_gpa,
690 new_references.selfrep_grading_scale_id,
691 new_references.selfrep_weighted_gpa,
692 new_references.selfrep_rank_in_class,
693 new_references.selfrep_weighed_rank,
694 new_references.selfrep_class_size,
695 new_references.attribute_category,
696 new_references.attribute1,
697 new_references.attribute2,
698 new_references.attribute3,
699 new_references.attribute4,
700 new_references.attribute5,
701 new_references.attribute6,
702 new_references.attribute7,
703 new_references.attribute8,
704 new_references.attribute9,
705 new_references.attribute10,
706 new_references.attribute11,
707 new_references.attribute12,
708 new_references.attribute13,
709 new_references.attribute14,
710 new_references.attribute15,
711 new_references.attribute16,
712 new_references.attribute17,
713 new_references.attribute18,
714 new_references.attribute19,
715 new_references.attribute20,
716 IGS_AD_HZ_ACAD_HIST_S.NEXTVAL,
717 new_references.education_id,
718 new_references.current_inst,
719 new_references.degree_attempted,
720 new_references.comments,
721 new_references.planned_completion_date,
722 new_references.transcript_required,
723 x_last_update_date,
724 x_last_updated_by,
725 x_last_update_date,
726 x_last_updated_by,
727 x_last_update_login,
728 x_request_id,
729 x_program_application_id,
730 x_program_update_date,
731 x_program_id
732 )RETURNING HZ_ACAD_HIST_ID INTO X_HZ_ACAD_HIST_ID;
733 IF (x_mode = 'S') THEN
734 igs_sc_gen_001.unset_ctx('R');
735 END IF;
736
737
738 OPEN c;
739 FETCH c INTO x_rowid;
740 IF (c%NOTFOUND) THEN
741 CLOSE c;
742 RAISE NO_DATA_FOUND;
743 END IF;
744 CLOSE c;
745
746
747 EXCEPTION
748 WHEN OTHERS THEN
749 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
750 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
751 fnd_message.set_token ('ERR_CD', SQLCODE);
752 igs_ge_msg_stack.add;
753 igs_sc_gen_001.unset_ctx('R');
754 app_exception.raise_exception;
755 ELSE
756 igs_sc_gen_001.unset_ctx('R');
757 RAISE;
758 END IF;
759 END insert_row;
760
761
762 PROCEDURE lock_row (
763 x_rowid IN VARCHAR2,
764 x_recalc_total_cp_attempted IN NUMBER,
765 x_recalc_total_cp_earned IN NUMBER,
766 x_recalc_total_unit_gp IN NUMBER,
767 x_recalc_tot_gpa_uts_attempted IN NUMBER,
768 x_recalc_inst_gpa IN VARCHAR2,
769 x_recalc_grading_scale_id IN NUMBER,
770 x_selfrep_total_cp_attempted IN NUMBER,
771 x_selfrep_total_cp_earned IN NUMBER,
772 x_selfrep_total_unit_gp IN NUMBER,
773 x_selfrep_tot_gpa_uts_attemp IN NUMBER,
774 x_selfrep_inst_gpa IN VARCHAR2,
775 x_selfrep_grading_scale_id IN NUMBER,
776 x_selfrep_weighted_gpa IN VARCHAR2,
777 x_selfrep_rank_in_class IN NUMBER,
778 x_selfrep_weighed_rank IN VARCHAR2,
779 x_selfrep_class_size IN NUMBER DEFAULT NULL,
780 x_attribute_category IN VARCHAR2,
781 x_attribute1 IN VARCHAR2,
782 x_attribute2 IN VARCHAR2,
783 x_attribute3 IN VARCHAR2,
784 x_attribute4 IN VARCHAR2,
785 x_attribute5 IN VARCHAR2,
786 x_attribute6 IN VARCHAR2,
787 x_attribute7 IN VARCHAR2,
788 x_attribute8 IN VARCHAR2,
789 x_attribute9 IN VARCHAR2,
790 x_attribute10 IN VARCHAR2,
791 x_attribute11 IN VARCHAR2,
795 x_attribute15 IN VARCHAR2,
792 x_attribute12 IN VARCHAR2,
793 x_attribute13 IN VARCHAR2,
794 x_attribute14 IN VARCHAR2,
796 x_attribute16 IN VARCHAR2,
797 x_attribute17 IN VARCHAR2,
798 x_attribute18 IN VARCHAR2,
799 x_attribute19 IN VARCHAR2,
800 x_attribute20 IN VARCHAR2,
801 x_hz_acad_hist_id IN NUMBER,
802 x_education_id IN NUMBER,
803 x_current_inst IN VARCHAR2,
804 x_degree_attempted IN VARCHAR2 ,
805 x_program_type_attempted IN VARCHAR2 ,
806 x_comments IN VARCHAR2,
807 x_planned_completion_date IN DATE,
808 x_transcript_required IN VARCHAR2 DEFAULT NULL
809 ) AS
810 /*
811 || Created By : [email protected]
812 || Created On : 29-AUG-2000
813 || Purpose : Handles the LOCK mechanism for the table.
814 || Known limitations, enhancements or remarks :
815 || Change History :
816 || Who When What
817 || (reverse chronological order - newest change first)
818 || vdixit.in 23-JULY-2001 Added new column transcript_required
819 || to the tbh calls
820 */
821 CURSOR c1 IS
822 SELECT
823 recalc_total_cp_attempted,
824 recalc_total_cp_earned,
825 recalc_total_unit_gp,
826 recalc_tot_gpa_uts_attempted,
827 recalc_inst_gpa,
828 recalc_grading_scale_id,
829 selfrep_total_cp_attempted,
830 selfrep_total_cp_earned,
831 selfrep_total_unit_gp,
832 selfrep_tot_gpa_uts_attemp,
833 selfrep_inst_gpa,
834 selfrep_grading_scale_id,
835 selfrep_weighted_gpa,
836 selfrep_rank_in_class,
837 selfrep_weighed_rank,
838 selfrep_class_size,
839 attribute_category,
840 attribute1,
841 attribute2,
842 attribute3,
843 attribute4,
844 attribute5,
845 attribute6,
846 attribute7,
847 attribute8,
848 attribute9,
849 attribute10,
850 attribute11,
851 attribute12,
852 attribute13,
853 attribute14,
854 attribute15,
855 attribute16,
856 attribute17,
857 attribute18,
858 attribute19,
859 attribute20,
860 hz_acad_hist_id,
861 education_id,
862 current_inst,
863 degree_attempted,
864 comments,
865 planned_completion_date,
866 transcript_required
867 FROM igs_ad_hz_acad_hist
868 WHERE rowid = x_rowid
869 FOR UPDATE NOWAIT;
870
871 tlinfo c1%ROWTYPE;
872
873 BEGIN
874
875 OPEN c1;
876 FETCH c1 INTO tlinfo;
877 IF (c1%notfound) THEN
878 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
879 igs_ge_msg_stack.add;
880 CLOSE c1;
881 app_exception.raise_exception;
882 RETURN;
883 END IF;
884 CLOSE c1;
885
886 IF (
887 ((tlinfo.recalc_total_cp_attempted = x_recalc_total_cp_attempted) OR ((tlinfo.recalc_total_cp_attempted IS NULL) AND (X_recalc_total_cp_attempted IS NULL)))
888 AND ((tlinfo.recalc_total_cp_earned = x_recalc_total_cp_earned) OR ((tlinfo.recalc_total_cp_earned IS NULL) AND (X_recalc_total_cp_earned IS NULL)))
889 AND ((tlinfo.recalc_total_unit_gp = x_recalc_total_unit_gp) OR ((tlinfo.recalc_total_unit_gp IS NULL) AND (X_recalc_total_unit_gp IS NULL)))
890 AND ((tlinfo.recalc_tot_gpa_uts_attempted = x_recalc_tot_gpa_uts_attempted) OR ((tlinfo.recalc_tot_gpa_uts_attempted IS NULL) AND (X_recalc_tot_gpa_uts_attempted IS NULL)))
891 AND ((tlinfo.recalc_inst_gpa = x_recalc_inst_gpa) OR ((tlinfo.recalc_inst_gpa IS NULL) AND (X_recalc_inst_gpa IS NULL)))
892 AND ((tlinfo.recalc_grading_scale_id = x_recalc_grading_scale_id) OR ((tlinfo.recalc_grading_scale_id IS NULL) AND (X_recalc_grading_scale_id IS NULL)))
893 AND ((tlinfo.selfrep_total_cp_attempted = x_selfrep_total_cp_attempted) OR ((tlinfo.selfrep_total_cp_attempted IS NULL) AND (X_selfrep_total_cp_attempted IS NULL)))
894 AND ((tlinfo.selfrep_total_cp_earned = x_selfrep_total_cp_earned) OR ((tlinfo.selfrep_total_cp_earned IS NULL) AND (X_selfrep_total_cp_earned IS NULL)))
895 AND ((tlinfo.selfrep_total_unit_gp = x_selfrep_total_unit_gp) OR ((tlinfo.selfrep_total_unit_gp IS NULL) AND (X_selfrep_total_unit_gp IS NULL)))
896 AND ((tlinfo.selfrep_tot_gpa_uts_attemp = x_selfrep_tot_gpa_uts_attemp ) OR ((tlinfo.selfrep_tot_gpa_uts_attemp IS NULL) AND ( x_selfrep_tot_gpa_uts_attemp IS NULL)))
897 AND ((tlinfo.selfrep_inst_gpa = x_selfrep_inst_gpa) OR ((tlinfo.selfrep_inst_gpa IS NULL) AND (X_selfrep_inst_gpa IS NULL)))
898 AND ((tlinfo.selfrep_grading_scale_id = x_selfrep_grading_scale_id) OR ((tlinfo.selfrep_grading_scale_id IS NULL) AND (X_selfrep_grading_scale_id IS NULL)))
899 AND ((tlinfo.selfrep_weighted_gpa = x_selfrep_weighted_gpa) OR ((tlinfo.selfrep_weighted_gpa IS NULL) AND (X_selfrep_weighted_gpa IS NULL)))
900 AND ((tlinfo.selfrep_rank_in_class = x_selfrep_rank_in_class) OR ((tlinfo.selfrep_rank_in_class IS NULL) AND (X_selfrep_rank_in_class IS NULL)))
904 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
901 AND ((tlinfo.selfrep_weighed_rank = x_selfrep_weighed_rank) OR ((tlinfo.selfrep_weighed_rank IS NULL) AND (X_selfrep_weighed_rank IS NULL)))
902 AND ((tlinfo.selfrep_class_size = x_selfrep_class_size) OR ((tlinfo.selfrep_class_size IS NULL) AND (X_selfrep_class_size IS NULL)))
903 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
905 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
906 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
907 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
908 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
909 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
910 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
911 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
912 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
913 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
914 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
915 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
916 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
917 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
918 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
919 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
920 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
921 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
922 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
923 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
924 AND (tlinfo.hz_acad_hist_id = x_hz_acad_hist_id)
925 AND (tlinfo.education_id = x_education_id)
926 AND (tlinfo.current_inst = x_current_inst)
927 AND ((tlinfo.degree_attempted = x_degree_attempted) OR ((tlinfo.degree_attempted IS NULL) AND (X_degree_attempted IS NULL)))
928 AND ((tlinfo.comments = x_comments) OR ((tlinfo.comments IS NULL) AND (X_comments IS NULL)))
929 AND ((TRUNC(tlinfo.planned_completion_date) = TRUNC(x_planned_completion_date)) OR ((tlinfo.planned_completion_date IS NULL) AND (X_planned_completion_date IS NULL)))
930 AND ((tlinfo.transcript_required = x_transcript_required) OR ((tlinfo.transcript_required IS NULL) AND (X_transcript_required IS NULL)))
931 ) THEN
932 NULL;
933 ELSE
934 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
935 igs_ge_msg_stack.add;
936 app_exception.raise_exception;
937 END IF;
938
939 RETURN;
940
941 END lock_row;
942
943
944 PROCEDURE update_row (
945 x_rowid IN VARCHAR2,
946 x_recalc_total_cp_attempted IN NUMBER,
947 x_recalc_total_cp_earned IN NUMBER,
948 x_recalc_total_unit_gp IN NUMBER,
949 x_recalc_tot_gpa_uts_attempted IN NUMBER,
950 x_recalc_inst_gpa IN VARCHAR2,
951 x_recalc_grading_scale_id IN NUMBER,
952 x_selfrep_total_cp_attempted IN NUMBER,
953 x_selfrep_total_cp_earned IN NUMBER,
954 x_selfrep_total_unit_gp IN NUMBER,
955 x_selfrep_tot_gpa_uts_attemp IN NUMBER,
956 x_selfrep_inst_gpa IN VARCHAR2,
957 x_selfrep_grading_scale_id IN NUMBER,
958 x_selfrep_weighted_gpa IN VARCHAR2,
959 x_selfrep_rank_in_class IN NUMBER,
960 x_selfrep_weighed_rank IN VARCHAR2,
961 x_selfrep_class_size IN NUMBER DEFAULT NULL,
962 x_attribute_category IN VARCHAR2,
963 x_attribute1 IN VARCHAR2,
964 x_attribute2 IN VARCHAR2,
965 x_attribute3 IN VARCHAR2,
966 x_attribute4 IN VARCHAR2,
967 x_attribute5 IN VARCHAR2,
968 x_attribute6 IN VARCHAR2,
969 x_attribute7 IN VARCHAR2,
970 x_attribute8 IN VARCHAR2,
971 x_attribute9 IN VARCHAR2,
972 x_attribute10 IN VARCHAR2,
973 x_attribute11 IN VARCHAR2,
974 x_attribute12 IN VARCHAR2,
975 x_attribute13 IN VARCHAR2,
976 x_attribute14 IN VARCHAR2,
977 x_attribute15 IN VARCHAR2,
978 x_attribute16 IN VARCHAR2,
982 x_attribute20 IN VARCHAR2,
979 x_attribute17 IN VARCHAR2,
980 x_attribute18 IN VARCHAR2,
981 x_attribute19 IN VARCHAR2,
983 x_hz_acad_hist_id IN NUMBER,
984 x_education_id IN NUMBER,
985 x_current_inst IN VARCHAR2,
986 x_degree_attempted IN VARCHAR2,
987 x_program_type_attempted IN VARCHAR2 ,
988 x_comments IN VARCHAR2,
989 x_planned_completion_date IN DATE,
990 x_transcript_required IN VARCHAR2 DEFAULT NULL,
991 x_mode IN VARCHAR2
992 ) AS
993 /*
994 || Created By : [email protected]
995 || Created On : 29-AUG-2000
996 || Purpose : Handles the UPDATE DML logic for the table.
997 || Known limitations, enhancements or remarks :
998 || Change History :
999 || Who When What
1000 || (reverse chronological order - newest change first)
1001 || vdixit.in 23-JULY-2001 Added new column transcript_required
1002 || to the tbh calls
1003 */
1004 x_last_update_date DATE ;
1005 x_last_updated_by NUMBER;
1006 x_last_update_login NUMBER;
1007 x_request_id NUMBER;
1008 x_program_id NUMBER;
1009 x_program_application_id NUMBER;
1010 x_program_update_date DATE;
1011 l_mode VARCHAR2(1);
1012 BEGIN
1013
1014 l_mode := NVL(x_mode,'R');
1015
1016 x_last_update_date := SYSDATE;
1017 IF (l_mode = 'I') THEN
1018 x_last_updated_by := 1;
1019 x_last_update_login := 0;
1020 ELSIF (l_mode = 'R') THEN
1021 x_last_updated_by := fnd_global.user_id;
1022 IF x_last_updated_by IS NULL THEN
1023 x_last_updated_by := -1;
1024 END IF;
1025 x_last_update_login := fnd_global.login_id;
1026 IF (x_last_update_login IS NULL) THEN
1027 x_last_update_login := -1;
1028 END IF;
1029 x_request_id:=FND_GLOBAL.CONC_REQUEST_ID;
1030 x_program_id:=FND_GLOBAL.CONC_PROGRAM_ID;
1031 x_program_application_id:=FND_GLOBAL.PROG_APPL_ID;
1032 IF (x_request_id = -1 ) THEN
1033 x_request_id:=NULL;
1034 x_program_id:=NULL;
1035 x_program_application_id:=NULL;
1036 x_program_update_date:=NULL;
1037 ELSE
1038 x_program_update_date:=SYSDATE;
1039 END IF;
1040 ELSE
1041 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1042 igs_ge_msg_stack.add;
1043 app_exception.raise_exception;
1044 END IF;
1045
1046 before_dml(
1047 p_action => 'UPDATE',
1048 x_rowid => x_rowid,
1049 x_recalc_total_cp_attempted => x_recalc_total_cp_attempted,
1050 x_recalc_total_cp_earned => x_recalc_total_cp_earned,
1051 x_recalc_total_unit_gp => x_recalc_total_unit_gp,
1052 x_recalc_tot_gpa_uts_attempted => x_recalc_tot_gpa_uts_attempted,
1053 x_recalc_inst_gpa => x_recalc_inst_gpa,
1054 x_recalc_grading_scale_id => x_recalc_grading_scale_id,
1055 x_selfrep_total_cp_attempted => x_selfrep_total_cp_attempted,
1056 x_selfrep_total_cp_earned => x_selfrep_total_cp_earned,
1057 x_selfrep_total_unit_gp => x_selfrep_total_unit_gp,
1058 x_selfrep_tot_gpa_uts_attemp => x_selfrep_tot_gpa_uts_attemp,
1059 x_selfrep_inst_gpa => x_selfrep_inst_gpa,
1060 x_selfrep_grading_scale_id => x_selfrep_grading_scale_id,
1061 x_selfrep_weighted_gpa => x_selfrep_weighted_gpa,
1062 x_selfrep_rank_in_class => x_selfrep_rank_in_class,
1063 x_selfrep_weighed_rank => x_selfrep_weighed_rank,
1064 x_selfrep_class_size => x_selfrep_class_size,
1065 x_attribute_category => x_attribute_category,
1066 x_attribute1 => x_attribute1,
1067 x_attribute2 => x_attribute2,
1068 x_attribute3 => x_attribute3,
1069 x_attribute4 => x_attribute4,
1070 x_attribute5 => x_attribute5,
1071 x_attribute6 => x_attribute6,
1072 x_attribute7 => x_attribute7,
1073 x_attribute8 => x_attribute8,
1074 x_attribute9 => x_attribute9,
1075 x_attribute10 => x_attribute10,
1076 x_attribute11 => x_attribute11,
1077 x_attribute12 => x_attribute12,
1078 x_attribute13 => x_attribute13,
1079 x_attribute14 => x_attribute14,
1080 x_attribute15 => x_attribute15,
1081 x_attribute16 => x_attribute16,
1082 x_attribute17 => x_attribute17,
1083 x_attribute18 => x_attribute18,
1084 x_attribute19 => x_attribute19,
1085 x_attribute20 => x_attribute20,
1086 x_hz_acad_hist_id => x_hz_acad_hist_id,
1090 x_comments => x_comments,
1087 x_education_id => x_education_id,
1088 x_current_inst => x_current_inst,
1089 x_degree_attempted => x_degree_attempted,
1091 x_planned_completion_date => x_planned_completion_date,
1092 x_transcript_required => x_transcript_required,
1093 x_creation_date => x_last_update_date,
1094 x_created_by => x_last_updated_by,
1095 x_last_update_date => x_last_update_date,
1096 x_last_updated_by => x_last_updated_by,
1097 x_last_update_login => x_last_update_login
1098 );
1099
1100 IF (x_mode = 'S') THEN
1101 igs_sc_gen_001.set_ctx('R');
1102 END IF;
1103 UPDATE igs_ad_hz_acad_hist
1104 SET
1105 recalc_total_cp_attempted = new_references.recalc_total_cp_attempted,
1106 recalc_total_cp_earned = new_references.recalc_total_cp_earned,
1107 recalc_total_unit_gp = new_references.recalc_total_unit_gp,
1108 recalc_tot_gpa_uts_attempted = new_references.recalc_tot_gpa_uts_attempted,
1109 recalc_inst_gpa = new_references.recalc_inst_gpa,
1110 recalc_grading_scale_id = new_references.recalc_grading_scale_id,
1111 selfrep_total_cp_attempted = new_references.selfrep_total_cp_attempted,
1112 selfrep_total_cp_earned = new_references.selfrep_total_cp_earned,
1113 selfrep_total_unit_gp = new_references.selfrep_total_unit_gp,
1114 selfrep_tot_gpa_uts_attemp = new_references.selfrep_tot_gpa_uts_attemp,
1115 selfrep_inst_gpa = new_references.selfrep_inst_gpa,
1116 selfrep_grading_scale_id = new_references.selfrep_grading_scale_id,
1117 selfrep_weighted_gpa = new_references.selfrep_weighted_gpa,
1118 selfrep_rank_in_class = new_references.selfrep_rank_in_class,
1119 selfrep_weighed_rank = new_references.selfrep_weighed_rank,
1120 selfrep_class_size = new_references.selfrep_class_size,
1121 attribute_category = new_references.attribute_category,
1122 attribute1 = new_references.attribute1,
1123 attribute2 = new_references.attribute2,
1124 attribute3 = new_references.attribute3,
1125 attribute4 = new_references.attribute4,
1126 attribute5 = new_references.attribute5,
1127 attribute6 = new_references.attribute6,
1128 attribute7 = new_references.attribute7,
1129 attribute8 = new_references.attribute8,
1130 attribute9 = new_references.attribute9,
1131 attribute10 = new_references.attribute10,
1132 attribute11 = new_references.attribute11,
1133 attribute12 = new_references.attribute12,
1134 attribute13 = new_references.attribute13,
1135 attribute14 = new_references.attribute14,
1136 attribute15 = new_references.attribute15,
1137 attribute16 = new_references.attribute16,
1138 attribute17 = new_references.attribute17,
1139 attribute18 = new_references.attribute18,
1140 attribute19 = new_references.attribute19,
1141 attribute20 = new_references.attribute20,
1142 hz_acad_hist_id = new_references.hz_acad_hist_id,
1143 education_id = new_references.education_id,
1144 current_inst = new_references.current_inst,
1145 degree_attempted = new_references.degree_attempted,
1146 comments = new_references.comments,
1147 planned_completion_date = new_references.planned_completion_date,
1148 transcript_required = new_references.transcript_required,
1149 last_update_date = x_last_update_date,
1150 last_updated_by = x_last_updated_by,
1151 last_update_login = x_last_update_login,
1152 request_id = x_request_id,
1153 program_application_id = x_program_application_id,
1154 program_update_date = x_program_update_date,
1155 program_id = x_program_id
1156 WHERE rowid = x_rowid;
1157
1158 IF (SQL%NOTFOUND) THEN
1159 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1160 igs_ge_msg_stack.add;
1161 igs_sc_gen_001.unset_ctx('R');
1162 app_exception.raise_exception;
1163 END IF;
1164 IF (x_mode = 'S') THEN
1165 igs_sc_gen_001.unset_ctx('R');
1166 END IF;
1167
1168
1169
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 IF (SQLCODE = (-28115)) THEN
1173 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1174 fnd_message.set_token ('ERR_CD', SQLCODE);
1175 igs_ge_msg_stack.add;
1176 igs_sc_gen_001.unset_ctx('R');
1177 app_exception.raise_exception;
1178 ELSE
1179 igs_sc_gen_001.unset_ctx('R');
1180 RAISE;
1181 END IF;
1182 END update_row;
1183
1184
1185 PROCEDURE add_row (
1186 x_rowid IN OUT NOCOPY VARCHAR2,
1187 x_recalc_total_cp_attempted IN NUMBER,
1188 x_recalc_total_cp_earned IN NUMBER,
1189 x_recalc_total_unit_gp IN NUMBER,
1190 x_recalc_tot_gpa_uts_attempted IN NUMBER,
1191 x_recalc_inst_gpa IN VARCHAR2,
1192 x_recalc_grading_scale_id IN NUMBER,
1193 x_selfrep_total_cp_attempted IN NUMBER,
1194 x_selfrep_total_cp_earned IN NUMBER,
1195 x_selfrep_total_unit_gp IN NUMBER,
1196 x_selfrep_tot_gpa_uts_attemp IN NUMBER,
1197 x_selfrep_inst_gpa IN VARCHAR2,
1198 x_selfrep_grading_scale_id IN NUMBER,
1199 x_selfrep_weighted_gpa IN VARCHAR2,
1200 x_selfrep_rank_in_class IN NUMBER,
1201 x_selfrep_weighed_rank IN VARCHAR2,
1202 x_selfrep_class_size IN NUMBER DEFAULT NULL,
1203 x_attribute_category IN VARCHAR2,
1204 x_attribute1 IN VARCHAR2,
1205 x_attribute2 IN VARCHAR2,
1206 x_attribute3 IN VARCHAR2,
1207 x_attribute4 IN VARCHAR2,
1208 x_attribute5 IN VARCHAR2,
1209 x_attribute6 IN VARCHAR2,
1210 x_attribute7 IN VARCHAR2,
1211 x_attribute8 IN VARCHAR2,
1212 x_attribute9 IN VARCHAR2,
1213 x_attribute10 IN VARCHAR2,
1214 x_attribute11 IN VARCHAR2,
1215 x_attribute12 IN VARCHAR2,
1216 x_attribute13 IN VARCHAR2,
1217 x_attribute14 IN VARCHAR2,
1218 x_attribute15 IN VARCHAR2,
1219 x_attribute16 IN VARCHAR2,
1220 x_attribute17 IN VARCHAR2,
1221 x_attribute18 IN VARCHAR2,
1222 x_attribute19 IN VARCHAR2,
1223 x_attribute20 IN VARCHAR2,
1224 x_hz_acad_hist_id IN OUT NOCOPY NUMBER,
1225 x_education_id IN NUMBER,
1226 x_current_inst IN VARCHAR2,
1227 x_degree_attempted IN VARCHAR2,
1228 x_program_type_attempted IN VARCHAR2 ,
1229 x_comments IN VARCHAR2,
1230 x_planned_completion_date IN DATE,
1231 x_transcript_required IN VARCHAR2 DEFAULT NULL,
1232 x_mode IN VARCHAR2
1233 ) AS
1234 /*
1235 || Created By : [email protected]
1236 || Created On : 29-AUG-2000
1237 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1238 || Known limitations, enhancements or remarks :
1239 || Change History :
1240 || Who When What
1241 || (reverse chronological order - newest change first)
1242 || vdixit.in 23-JULY-2001 Added new column transcript_required
1243 || to the tbh calls
1244 */
1245 CURSOR c1 IS
1246 SELECT rowid
1247 FROM igs_ad_hz_acad_hist
1248 WHERE hz_acad_hist_id = x_hz_acad_hist_id;
1249 l_mode VARCHAR2(1);
1250 BEGIN
1251
1252 l_mode := NVL(x_mode,'R');
1253
1254 OPEN c1;
1255 FETCH c1 INTO x_rowid;
1256 IF (c1%NOTFOUND) THEN
1257 CLOSE c1;
1258
1259 insert_row (
1260 x_rowid => x_rowid,
1261 x_recalc_total_cp_attempted => x_recalc_total_cp_attempted,
1262 x_recalc_total_cp_earned => x_recalc_total_cp_earned,
1263 x_recalc_total_unit_gp => x_recalc_total_unit_gp,
1264 x_recalc_tot_gpa_uts_attempted => x_recalc_tot_gpa_uts_attempted,
1265 x_recalc_inst_gpa => x_recalc_inst_gpa,
1266 x_recalc_grading_scale_id => x_recalc_grading_scale_id,
1267 x_selfrep_total_cp_attempted => x_selfrep_total_cp_attempted,
1268 x_selfrep_total_cp_earned => x_selfrep_total_cp_earned,
1269 x_selfrep_total_unit_gp => x_selfrep_total_unit_gp,
1270 x_selfrep_tot_gpa_uts_attemp => x_selfrep_tot_gpa_uts_attemp,
1271 x_selfrep_inst_gpa => x_selfrep_inst_gpa,
1272 x_selfrep_grading_scale_id => x_selfrep_grading_scale_id,
1273 x_selfrep_weighted_gpa => x_selfrep_weighted_gpa,
1274 x_selfrep_rank_in_class => x_selfrep_rank_in_class,
1275 x_selfrep_weighed_rank => x_selfrep_weighed_rank,
1276 x_selfrep_class_size => x_selfrep_class_size,
1277 x_attribute_category => x_attribute_category,
1278 x_attribute1 => x_attribute1,
1279 x_attribute2 => x_attribute2,
1280 x_attribute3 => x_attribute3,
1281 x_attribute4 => x_attribute4,
1282 x_attribute5 => x_attribute5,
1283 x_attribute6 => x_attribute6,
1284 x_attribute7 => x_attribute7,
1285 x_attribute8 => x_attribute8,
1286 x_attribute9 => x_attribute9,
1287 x_attribute10 => x_attribute10,
1288 x_attribute11 => x_attribute11,
1289 x_attribute12 => x_attribute12,
1290 x_attribute13 => x_attribute13,
1291 x_attribute14 => x_attribute14,
1292 x_attribute15 => x_attribute15,
1293 x_attribute16 => x_attribute16,
1294 x_attribute17 => x_attribute17,
1295 x_attribute18 => x_attribute18,
1296 x_attribute19 => x_attribute19,
1297 x_attribute20 => x_attribute20,
1298 x_hz_acad_hist_id => x_hz_acad_hist_id,
1299 x_education_id => x_education_id,
1300 x_current_inst => x_current_inst,
1301 x_degree_attempted => x_degree_attempted,
1302 x_comments => x_comments,
1303 x_planned_completion_date => x_planned_completion_date,
1304 x_transcript_required => x_transcript_required,
1305 x_mode => l_mode
1306 );
1307 RETURN;
1308 END IF;
1309 CLOSE c1;
1310
1311 update_row (
1312 x_rowid => x_rowid,
1313 x_recalc_total_cp_attempted => x_recalc_total_cp_attempted,
1314 x_recalc_total_cp_earned => x_recalc_total_cp_earned,
1315 x_recalc_total_unit_gp => x_recalc_total_unit_gp,
1316 x_recalc_tot_gpa_uts_attempted => x_recalc_tot_gpa_uts_attempted,
1317 x_recalc_inst_gpa => x_recalc_inst_gpa,
1318 x_recalc_grading_scale_id => x_recalc_grading_scale_id,
1319 x_selfrep_total_cp_attempted => x_selfrep_total_cp_attempted,
1320 x_selfrep_total_cp_earned => x_selfrep_total_cp_earned,
1321 x_selfrep_total_unit_gp => x_selfrep_total_unit_gp,
1322 x_selfrep_tot_gpa_uts_attemp => x_selfrep_tot_gpa_uts_attemp,
1323 x_selfrep_inst_gpa => x_selfrep_inst_gpa,
1324 x_selfrep_grading_scale_id => x_selfrep_grading_scale_id,
1325 x_selfrep_weighted_gpa => x_selfrep_weighted_gpa,
1326 x_selfrep_rank_in_class => x_selfrep_rank_in_class,
1327 x_selfrep_weighed_rank => x_selfrep_weighed_rank,
1328 x_selfrep_class_size => x_selfrep_class_size,
1329 x_attribute_category => x_attribute_category,
1330 x_attribute1 => x_attribute1,
1331 x_attribute2 => x_attribute2,
1332 x_attribute3 => x_attribute3,
1333 x_attribute4 => x_attribute4,
1334 x_attribute5 => x_attribute5,
1335 x_attribute6 => x_attribute6,
1336 x_attribute7 => x_attribute7,
1337 x_attribute8 => x_attribute8,
1338 x_attribute9 => x_attribute9,
1339 x_attribute10 => x_attribute10,
1340 x_attribute11 => x_attribute11,
1341 x_attribute12 => x_attribute12,
1342 x_attribute13 => x_attribute13,
1343 x_attribute14 => x_attribute14,
1344 x_attribute15 => x_attribute15,
1345 x_attribute16 => x_attribute16,
1346 x_attribute17 => x_attribute17,
1347 x_attribute18 => x_attribute18,
1348 x_attribute19 => x_attribute19,
1349 x_attribute20 => x_attribute20,
1350 x_hz_acad_hist_id => x_hz_acad_hist_id,
1351 x_education_id => x_education_id,
1352 x_current_inst => x_current_inst,
1353 x_degree_attempted => x_degree_attempted,
1354 x_comments => x_comments,
1355 x_planned_completion_date => x_planned_completion_date,
1356 x_transcript_required => x_transcript_required,
1357 x_mode => l_mode
1358 );
1359
1360 END add_row;
1361
1362
1363 PROCEDURE delete_row (
1364 x_rowid IN VARCHAR2,
1365 x_mode IN VARCHAR2
1366 ) AS
1367 /*
1368 || Created By : [email protected]
1369 || Created On : 29-AUG-2000
1370 || Purpose : Handles the DELETE DML logic for the table.
1371 || Known limitations, enhancements or remarks :
1372 || Change History :
1373 || Who When What
1374 || (reverse chronological order - newest change first)
1375 */
1376 BEGIN
1377
1378 before_dml (
1379 p_action => 'DELETE',
1380 x_rowid => x_rowid
1381 );
1382
1383 IF (x_mode = 'S') THEN
1384 igs_sc_gen_001.set_ctx('R');
1385 END IF;
1386 DELETE FROM igs_ad_hz_acad_hist
1387 WHERE rowid = x_rowid;
1388
1389 IF (SQL%NOTFOUND) THEN
1390 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1391 igs_ge_msg_stack.add;
1392 igs_sc_gen_001.unset_ctx('R');
1393 app_exception.raise_exception;
1394 END IF;
1395 IF (x_mode = 'S') THEN
1396 igs_sc_gen_001.unset_ctx('R');
1397 END IF;
1398
1399
1400 END delete_row;
1401
1402
1403 END igs_ad_hz_acad_hist_pkg;