[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_INS_GRD_ENTRY_PKG
Source
1 package body IGS_AS_INS_GRD_ENTRY_PKG AS
2 /* $Header: IGSDI22B.pls 115.3 2002/11/28 23:16:14 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AS_INS_GRD_ENTRY%RowType;
5 new_references IGS_AS_INS_GRD_ENTRY%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_keying_who IN VARCHAR2 DEFAULT NULL,
10 x_keying_time IN DATE DEFAULT NULL,
11 x_student_sequence IN NUMBER DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_name IN VARCHAR2 DEFAULT NULL,
14 x_course_cd IN VARCHAR2 DEFAULT NULL,
15 x_unit_cd IN VARCHAR2 DEFAULT NULL,
16 x_version_number IN NUMBER DEFAULT NULL,
17 x_cal_type IN VARCHAR2 DEFAULT NULL,
18 x_ci_sequence_number IN NUMBER DEFAULT NULL,
19 x_location_cd IN VARCHAR2 DEFAULT NULL,
20 x_unit_class IN VARCHAR2 DEFAULT NULL,
21 x_unit_attempt_status IN VARCHAR2 DEFAULT NULL,
22 x_mark IN NUMBER DEFAULT NULL,
23 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
24 x_gs_version_number IN NUMBER DEFAULT NULL,
25 x_grade IN VARCHAR2 DEFAULT NULL,
26 x_specified_grade_ind IN VARCHAR2 DEFAULT NULL,
27 x_creation_date IN DATE DEFAULT NULL,
28 x_created_by IN NUMBER DEFAULT NULL,
29 x_last_update_date IN DATE DEFAULT NULL,
30 x_last_updated_by IN NUMBER DEFAULT NULL,
31 x_last_update_login IN NUMBER DEFAULT NULL
32 ) AS
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_AS_INS_GRD_ENTRY
36 WHERE rowid = x_rowid;
37 BEGIN
38 l_rowid := x_rowid;
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 Open cur_old_ref_values;
42 Fetch cur_old_ref_values INTO old_references;
43 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
44 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
45 IGS_GE_MSG_STACK.ADD;
46 Close cur_old_ref_values;
47 APP_EXCEPTION.RAISE_EXCEPTION;
48
49 Return;
50 END IF;
51 Close cur_old_ref_values;
52 -- Populate New Values.
53 new_references.keying_who := x_keying_who;
54 new_references.keying_time := x_keying_time;
55 new_references.student_sequence := x_student_sequence;
56 new_references.person_id := x_person_id;
57 new_references.name := x_name;
58 new_references.course_cd := x_course_cd;
59 new_references.unit_cd := x_unit_cd;
60 new_references.version_number := x_version_number;
61 new_references.cal_type := x_cal_type;
62 new_references.ci_sequence_number := x_ci_sequence_number;
63 new_references.location_cd := x_location_cd;
64 new_references.unit_class := x_unit_class;
65 new_references.unit_attempt_status := x_unit_attempt_status;
66 new_references.mark := x_mark;
67 new_references.grading_schema_cd := x_grading_schema_cd;
68 new_references.gs_version_number := x_gs_version_number;
69 new_references.grade := x_grade;
70 new_references.specified_grade_ind := x_specified_grade_ind;
71 IF (p_action = 'UPDATE') THEN
72 new_references.creation_date := old_references.creation_date;
73 new_references.created_by := old_references.created_by;
74 ELSE
75 new_references.creation_date := x_creation_date;
76 new_references.created_by := x_created_by;
77 END IF;
78 new_references.last_update_date := x_last_update_date;
79 new_references.last_updated_by := x_last_updated_by;
80 new_references.last_update_login := x_last_update_login;
81 END Set_Column_Values;
82 PROCEDURE Check_Parent_Existance AS
83 BEGIN
84 IF (((old_references.grading_schema_cd = new_references.grading_schema_cd) AND
85 (old_references.gs_version_number = new_references.gs_version_number) AND
86 (old_references.grade = new_references.grade)) OR
87 ((new_references.grading_schema_cd IS NULL) OR
88 (new_references.gs_version_number IS NULL) OR
89 (new_references.grade IS NULL))) THEN
90 NULL;
91 ELSIF NOT IGS_AS_GRD_SCH_GRADE_PKG.Get_PK_For_Validation (
92 new_references.grading_schema_cd,
93 new_references.gs_version_number,
94 new_references.grade
95 ) THEN
96 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
97 IGS_GE_MSG_STACK.ADD;
98 APP_EXCEPTION.RAISE_EXCEPTION;
99 END IF;
100 END Check_Parent_Existance;
101 FUNCTION Get_PK_For_Validation (
102 x_keying_who IN VARCHAR2,
103 x_keying_time IN DATE,
104 x_student_sequence IN NUMBER
105 ) RETURN BOOLEAN AS
106 CURSOR cur_rowid IS
107 SELECT rowid
108 FROM IGS_AS_INS_GRD_ENTRY
109 WHERE keying_who = x_keying_who
110 AND keying_time = x_keying_time
111 AND student_sequence = x_student_sequence
112 FOR UPDATE NOWAIT;
113 lv_rowid cur_rowid%RowType;
114 BEGIN
115 Open cur_rowid;
116 Fetch cur_rowid INTO lv_rowid;
117 IF (cur_rowid%FOUND) THEN
118 Close cur_rowid;
119 Return (TRUE);
120 ELSE
121 Close cur_rowid;
122 Return (FALSE);
123 END IF;
124 END Get_PK_For_Validation;
125 PROCEDURE GET_FK_IGS_AS_GRD_SCH_GRADE (
126 x_grading_schema_cd IN VARCHAR2,
127 x_version_number IN NUMBER,
128 x_grade IN VARCHAR2
129 ) AS
130 CURSOR cur_rowid IS
131 SELECT rowid
132 FROM IGS_AS_INS_GRD_ENTRY
133 WHERE grading_schema_cd = x_grading_schema_cd
134 AND gs_version_number = x_version_number
135 AND grade = x_grade ;
136 lv_rowid cur_rowid%RowType;
137 BEGIN
138 Open cur_rowid;
139 Fetch cur_rowid INTO lv_rowid;
140 IF (cur_rowid%FOUND) THEN
141 Fnd_Message.Set_Name ('IGS', 'IGS_AS_GET_GSG_FK');
142 IGS_GE_MSG_STACK.ADD;
143 Close cur_rowid;
144 APP_EXCEPTION.RAISE_EXCEPTION;
145
146 Return;
147 END IF;
148 Close cur_rowid;
149 END GET_FK_IGS_AS_GRD_SCH_GRADE;
150 PROCEDURE Before_DML (
151 p_action IN VARCHAR2,
152 x_rowid IN VARCHAR2 DEFAULT NULL,
153 x_keying_who IN VARCHAR2 DEFAULT NULL,
154 x_keying_time IN DATE DEFAULT NULL,
155 x_student_sequence IN NUMBER DEFAULT NULL,
156 x_person_id IN NUMBER DEFAULT NULL,
157 x_name IN VARCHAR2 DEFAULT NULL,
158 x_course_cd IN VARCHAR2 DEFAULT NULL,
159 x_unit_cd IN VARCHAR2 DEFAULT NULL,
160 x_version_number IN NUMBER DEFAULT NULL,
161 x_cal_type IN VARCHAR2 DEFAULT NULL,
162 x_ci_sequence_number IN NUMBER DEFAULT NULL,
163 x_location_cd IN VARCHAR2 DEFAULT NULL,
164 x_unit_class IN VARCHAR2 DEFAULT NULL,
165 x_unit_attempt_status IN VARCHAR2 DEFAULT NULL,
166 x_mark IN NUMBER DEFAULT NULL,
167 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
168 x_gs_version_number IN NUMBER DEFAULT NULL,
169 x_grade IN VARCHAR2 DEFAULT NULL,
170 x_specified_grade_ind IN VARCHAR2 DEFAULT NULL,
171 x_creation_date IN DATE DEFAULT NULL,
172 x_created_by IN NUMBER DEFAULT NULL,
173 x_last_update_date IN DATE DEFAULT NULL,
174 x_last_updated_by IN NUMBER DEFAULT NULL,
175 x_last_update_login IN NUMBER DEFAULT NULL
176 ) AS
177 BEGIN
178 Set_Column_Values (
179 p_action,
180 x_rowid,
181 x_keying_who,
182 x_keying_time,
183 x_student_sequence,
184 x_person_id,
185 x_name,
186 x_course_cd,
187 x_unit_cd,
188 x_version_number,
189 x_cal_type,
190 x_ci_sequence_number,
191 x_location_cd,
192 x_unit_class,
193 x_unit_attempt_status,
194 x_mark,
195 x_grading_schema_cd,
196 x_gs_version_number,
197 x_grade,
198 x_specified_grade_ind,
199 x_creation_date,
200 x_created_by,
201 x_last_update_date,
202 x_last_updated_by,
203 x_last_update_login
204 );
205 IF (p_action = 'INSERT') THEN
206 -- Call all the procedures related to Before Insert.
207
208 IF Get_PK_For_Validation (
209 NEW_REFERENCES.keying_who ,
210 NEW_REFERENCES.keying_time ,
211 NEW_REFERENCES.student_sequence) THEN
212 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
213 IGS_GE_MSG_STACK.ADD;
214 APP_EXCEPTION.RAISE_EXCEPTION;
215 END IF;
216
217 Check_Constraints;
218
219 Check_Parent_Existance;
220 ELSIF (p_action = 'UPDATE') THEN
221 -- Call all the procedures related to Before Update.
222
223 Check_Constraints;
224 Check_Parent_Existance;
225
226
227 ELSIF (p_action = 'VALIDATE_INSERT') THEN
228 IF Get_PK_For_Validation (
229 NEW_REFERENCES.keying_who ,
230 NEW_REFERENCES.keying_time ,
231 NEW_REFERENCES.student_sequence) THEN
232 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
233 IGS_GE_MSG_STACK.ADD;
234 APP_EXCEPTION.RAISE_EXCEPTION;
235 END IF;
236
237 Check_Constraints;
238 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
239 Check_Constraints;
240
241 END IF;
242 END Before_DML;
243
244 procedure INSERT_ROW (
245 X_ROWID in out NOCOPY VARCHAR2,
246 X_KEYING_WHO in VARCHAR2,
247 X_KEYING_TIME in DATE,
248 X_STUDENT_SEQUENCE in NUMBER,
249 X_PERSON_ID in NUMBER,
250 X_NAME in VARCHAR2,
251 X_COURSE_CD in VARCHAR2,
252 X_UNIT_CD in VARCHAR2,
253 X_VERSION_NUMBER in NUMBER,
254 X_CAL_TYPE in VARCHAR2,
255 X_CI_SEQUENCE_NUMBER in NUMBER,
256 X_LOCATION_CD in VARCHAR2,
257 X_UNIT_CLASS in VARCHAR2,
258 X_UNIT_ATTEMPT_STATUS in VARCHAR2,
259 X_MARK in NUMBER,
260 X_GRADING_SCHEMA_CD in VARCHAR2,
261 X_GS_VERSION_NUMBER in NUMBER,
262 X_GRADE in VARCHAR2,
263 X_SPECIFIED_GRADE_IND in VARCHAR2,
264 X_MODE in VARCHAR2 default 'R'
265 ) AS
266 cursor C is select ROWID from IGS_AS_INS_GRD_ENTRY
267 where KEYING_WHO = X_KEYING_WHO
268 and KEYING_TIME = X_KEYING_TIME
269 and STUDENT_SEQUENCE = X_STUDENT_SEQUENCE;
270 X_LAST_UPDATE_DATE DATE;
271 X_LAST_UPDATED_BY NUMBER;
272 X_LAST_UPDATE_LOGIN NUMBER;
273 begin
274 X_LAST_UPDATE_DATE := SYSDATE;
275 if(X_MODE = 'I') then
276 X_LAST_UPDATED_BY := 1;
277 X_LAST_UPDATE_LOGIN := 0;
278 elsif (X_MODE = 'R') then
279 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
280 if X_LAST_UPDATED_BY is NULL then
281 X_LAST_UPDATED_BY := -1;
282 end if;
283 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
284 if X_LAST_UPDATE_LOGIN is NULL then
285 X_LAST_UPDATE_LOGIN := -1;
286 end if;
287 else
288 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
289 IGS_GE_MSG_STACK.ADD;
290 APP_EXCEPTION.RAISE_EXCEPTION;
291 end if;
292 Before_DML(
296 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
293 p_action=>'INSERT',
294 x_rowid=>X_ROWID,
295 x_cal_type=>X_CAL_TYPE,
297 x_course_cd=>X_COURSE_CD,
298 x_grade=>X_GRADE,
299 x_grading_schema_cd=>X_GRADING_SCHEMA_CD,
300 x_gs_version_number=>X_GS_VERSION_NUMBER,
301 x_keying_time=>X_KEYING_TIME,
302 x_keying_who=>X_KEYING_WHO,
303 x_location_cd=>X_LOCATION_CD,
304 x_mark=>X_MARK,
305 x_name=>X_NAME,
306 x_person_id=>X_PERSON_ID,
307 x_specified_grade_ind=> NVL(X_SPECIFIED_GRADE_IND,'N'),
308 x_student_sequence=>X_STUDENT_SEQUENCE,
309 x_unit_attempt_status=>X_UNIT_ATTEMPT_STATUS,
310 x_unit_cd=>X_UNIT_CD,
311 x_unit_class=>X_UNIT_CLASS,
312 x_version_number=>X_VERSION_NUMBER,
313 x_creation_date=>X_LAST_UPDATE_DATE,
314 x_created_by=>X_LAST_UPDATED_BY,
315 x_last_update_date=>X_LAST_UPDATE_DATE,
316 x_last_updated_by=>X_LAST_UPDATED_BY,
317 x_last_update_login=>X_LAST_UPDATE_LOGIN
318 );
319 insert into IGS_AS_INS_GRD_ENTRY (
320 KEYING_WHO,
321 KEYING_TIME,
322 STUDENT_SEQUENCE,
323 PERSON_ID,
324 NAME,
325 COURSE_CD,
326 UNIT_CD,
327 VERSION_NUMBER,
328 CAL_TYPE,
329 CI_SEQUENCE_NUMBER,
330 LOCATION_CD,
331 UNIT_CLASS,
332 UNIT_ATTEMPT_STATUS,
333 MARK,
334 GRADING_SCHEMA_CD,
335 GS_VERSION_NUMBER,
336 GRADE,
337 SPECIFIED_GRADE_IND,
338 CREATION_DATE,
339 CREATED_BY,
340 LAST_UPDATE_DATE,
341 LAST_UPDATED_BY,
342 LAST_UPDATE_LOGIN
343 ) values (
344 NEW_REFERENCES.KEYING_WHO,
345 NEW_REFERENCES.KEYING_TIME,
346 NEW_REFERENCES.STUDENT_SEQUENCE,
347 NEW_REFERENCES.PERSON_ID,
348 NEW_REFERENCES.NAME,
349 NEW_REFERENCES.COURSE_CD,
350 NEW_REFERENCES.UNIT_CD,
351 NEW_REFERENCES.VERSION_NUMBER,
352 NEW_REFERENCES.CAL_TYPE,
353 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
354 NEW_REFERENCES.LOCATION_CD,
355 NEW_REFERENCES.UNIT_CLASS,
356 NEW_REFERENCES.UNIT_ATTEMPT_STATUS,
357 NEW_REFERENCES.MARK,
358 NEW_REFERENCES.GRADING_SCHEMA_CD,
359 NEW_REFERENCES.GS_VERSION_NUMBER,
360 NEW_REFERENCES.GRADE,
361 NEW_REFERENCES.SPECIFIED_GRADE_IND,
362 X_LAST_UPDATE_DATE,
363 X_LAST_UPDATED_BY,
364 X_LAST_UPDATE_DATE,
365 X_LAST_UPDATED_BY,
366 X_LAST_UPDATE_LOGIN
367 );
368 open c;
369 fetch c into X_ROWID;
370 if (c%notfound) then
371 close c;
372 raise no_data_found;
373 end if;
374 close c;
375
376 end INSERT_ROW;
377 procedure LOCK_ROW (
378 X_ROWID in VARCHAR2,
379 X_KEYING_WHO in VARCHAR2,
380 X_KEYING_TIME in DATE,
381 X_STUDENT_SEQUENCE in NUMBER,
382 X_PERSON_ID in NUMBER,
383 X_NAME in VARCHAR2,
384 X_COURSE_CD in VARCHAR2,
385 X_UNIT_CD in VARCHAR2,
386 X_VERSION_NUMBER in NUMBER,
387 X_CAL_TYPE in VARCHAR2,
388 X_CI_SEQUENCE_NUMBER in NUMBER,
389 X_LOCATION_CD in VARCHAR2,
390 X_UNIT_CLASS in VARCHAR2,
391 X_UNIT_ATTEMPT_STATUS in VARCHAR2,
392 X_MARK in NUMBER,
393 X_GRADING_SCHEMA_CD in VARCHAR2,
394 X_GS_VERSION_NUMBER in NUMBER,
395 X_GRADE in VARCHAR2,
396 X_SPECIFIED_GRADE_IND in VARCHAR2
397 ) AS
398 cursor c1 is select
399 PERSON_ID,
400 NAME,
401 COURSE_CD,
402 UNIT_CD,
403 VERSION_NUMBER,
404 CAL_TYPE,
405 CI_SEQUENCE_NUMBER,
406 LOCATION_CD,
407 UNIT_CLASS,
408 UNIT_ATTEMPT_STATUS,
409 MARK,
410 GRADING_SCHEMA_CD,
411 GS_VERSION_NUMBER,
412 GRADE,
413 SPECIFIED_GRADE_IND
414 from IGS_AS_INS_GRD_ENTRY
418 open c1;
415 where ROWID = X_ROWID for update nowait;
416 tlinfo c1%rowtype;
417 begin
419 fetch c1 into tlinfo;
420 if (c1%notfound) then
421 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
422 IGS_GE_MSG_STACK.ADD;
423 APP_EXCEPTION.RAISE_EXCEPTION;
424 close c1;
425 return;
426 end if;
427 close c1;
428 if ( (tlinfo.PERSON_ID = X_PERSON_ID)
429 AND (tlinfo.NAME = X_NAME)
430 AND (tlinfo.COURSE_CD = X_COURSE_CD)
431 AND (tlinfo.UNIT_CD = X_UNIT_CD)
432 AND (tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
433 AND (tlinfo.CAL_TYPE = X_CAL_TYPE)
434 AND (tlinfo.CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER)
435 AND (tlinfo.LOCATION_CD = X_LOCATION_CD)
436 AND (tlinfo.UNIT_CLASS = X_UNIT_CLASS)
437 AND ((tlinfo.UNIT_ATTEMPT_STATUS = X_UNIT_ATTEMPT_STATUS)
438 OR ((tlinfo.UNIT_ATTEMPT_STATUS is null)
439 AND (X_UNIT_ATTEMPT_STATUS is null)))
440 AND ((tlinfo.MARK = X_MARK)
441 OR ((tlinfo.MARK is null)
442 AND (X_MARK is null)))
443 AND ((tlinfo.GRADING_SCHEMA_CD = X_GRADING_SCHEMA_CD)
444 OR ((tlinfo.GRADING_SCHEMA_CD is null)
445 AND (X_GRADING_SCHEMA_CD is null)))
446 AND ((tlinfo.GS_VERSION_NUMBER = X_GS_VERSION_NUMBER)
447 OR ((tlinfo.GS_VERSION_NUMBER is null)
448 AND (X_GS_VERSION_NUMBER is null)))
449 AND ((tlinfo.GRADE = X_GRADE)
450 OR ((tlinfo.GRADE is null)
451 AND (X_GRADE is null)))
452 AND (tlinfo.SPECIFIED_GRADE_IND = X_SPECIFIED_GRADE_IND)
453 ) then
454 null;
455 else
456 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
457 IGS_GE_MSG_STACK.ADD;
458 APP_EXCEPTION.RAISE_EXCEPTION;
459 end if;
460 return;
461 end LOCK_ROW;
462 procedure UPDATE_ROW (
463 X_ROWID in VARCHAR2,
464 X_KEYING_WHO in VARCHAR2,
465 X_KEYING_TIME in DATE,
466 X_STUDENT_SEQUENCE in NUMBER,
467 X_PERSON_ID in NUMBER,
468 X_NAME in VARCHAR2,
469 X_COURSE_CD in VARCHAR2,
470 X_UNIT_CD in VARCHAR2,
471 X_VERSION_NUMBER in NUMBER,
472 X_CAL_TYPE in VARCHAR2,
473 X_CI_SEQUENCE_NUMBER in NUMBER,
474 X_LOCATION_CD in VARCHAR2,
475 X_UNIT_CLASS in VARCHAR2,
476 X_UNIT_ATTEMPT_STATUS in VARCHAR2,
477 X_MARK in NUMBER,
478 X_GRADING_SCHEMA_CD in VARCHAR2,
479 X_GS_VERSION_NUMBER in NUMBER,
480 X_GRADE in VARCHAR2,
481 X_SPECIFIED_GRADE_IND in VARCHAR2,
482 X_MODE in VARCHAR2 default 'R'
483 ) AS
484 X_LAST_UPDATE_DATE DATE;
485 X_LAST_UPDATED_BY NUMBER;
486 X_LAST_UPDATE_LOGIN NUMBER;
487 begin
488 X_LAST_UPDATE_DATE := SYSDATE;
489 if(X_MODE = 'I') then
490 X_LAST_UPDATED_BY := 1;
491 X_LAST_UPDATE_LOGIN := 0;
492 elsif (X_MODE = 'R') then
493 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
494 if X_LAST_UPDATED_BY is NULL then
495 X_LAST_UPDATED_BY := -1;
496 end if;
497 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
498 if X_LAST_UPDATE_LOGIN is NULL then
499 X_LAST_UPDATE_LOGIN := -1;
500 end if;
501 else
502 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
503 IGS_GE_MSG_STACK.ADD;
504 APP_EXCEPTION.RAISE_EXCEPTION;
505 end if;
506 Before_DML(
507 p_action=>'UPDATE',
508 x_rowid=>X_ROWID,
509 x_cal_type=>X_CAL_TYPE,
510 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
511 x_course_cd=>X_COURSE_CD,
512 x_grade=>X_GRADE,
513 x_grading_schema_cd=>X_GRADING_SCHEMA_CD,
514 x_gs_version_number=>X_GS_VERSION_NUMBER,
515 x_keying_time=>X_KEYING_TIME,
516 x_keying_who=>X_KEYING_WHO,
517 x_location_cd=>X_LOCATION_CD,
518 x_mark=>X_MARK,
519 x_name=>X_NAME,
520 x_person_id=>X_PERSON_ID,
521 x_specified_grade_ind=>X_SPECIFIED_GRADE_IND,
522 x_student_sequence=>X_STUDENT_SEQUENCE,
523 x_unit_attempt_status=>X_UNIT_ATTEMPT_STATUS,
524 x_unit_cd=>X_UNIT_CD,
525 x_unit_class=>X_UNIT_CLASS,
526 x_version_number=>X_VERSION_NUMBER,
527 x_creation_date=>X_LAST_UPDATE_DATE,
528 x_created_by=>X_LAST_UPDATED_BY,
529 x_last_update_date=>X_LAST_UPDATE_DATE,
530 x_last_updated_by=>X_LAST_UPDATED_BY,
531 x_last_update_login=>X_LAST_UPDATE_LOGIN
532 );
533 update IGS_AS_INS_GRD_ENTRY set
534 PERSON_ID = NEW_REFERENCES.PERSON_ID,
535 NAME = NEW_REFERENCES.NAME,
536 COURSE_CD = NEW_REFERENCES.COURSE_CD,
537 UNIT_CD = NEW_REFERENCES.UNIT_CD,
538 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
539 CAL_TYPE = NEW_REFERENCES.CAL_TYPE,
540 CI_SEQUENCE_NUMBER = NEW_REFERENCES.CI_SEQUENCE_NUMBER,
541 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
542 UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
543 UNIT_ATTEMPT_STATUS = NEW_REFERENCES.UNIT_ATTEMPT_STATUS,
544 MARK = NEW_REFERENCES.MARK,
545 GRADING_SCHEMA_CD = NEW_REFERENCES.GRADING_SCHEMA_CD,
546 GS_VERSION_NUMBER = NEW_REFERENCES.GS_VERSION_NUMBER,
547 GRADE = NEW_REFERENCES.GRADE,
548 SPECIFIED_GRADE_IND = NEW_REFERENCES.SPECIFIED_GRADE_IND,
549 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
550 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
551 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
552 where ROWID = X_ROWID;
553 if (sql%notfound) then
554 raise no_data_found;
555 end if;
556
557 end UPDATE_ROW;
558 procedure ADD_ROW (
559 X_ROWID in out NOCOPY VARCHAR2,
560 X_KEYING_WHO in VARCHAR2,
561 X_KEYING_TIME in DATE,
562 X_STUDENT_SEQUENCE in NUMBER,
563 X_PERSON_ID in NUMBER,
564 X_NAME in VARCHAR2,
565 X_COURSE_CD in VARCHAR2,
566 X_UNIT_CD in VARCHAR2,
567 X_VERSION_NUMBER in NUMBER,
568 X_CAL_TYPE in VARCHAR2,
569 X_CI_SEQUENCE_NUMBER in NUMBER,
570 X_LOCATION_CD in VARCHAR2,
571 X_UNIT_CLASS in VARCHAR2,
572 X_UNIT_ATTEMPT_STATUS in VARCHAR2,
573 X_MARK in NUMBER,
574 X_GRADING_SCHEMA_CD in VARCHAR2,
575 X_GS_VERSION_NUMBER in NUMBER,
576 X_GRADE in VARCHAR2,
577 X_SPECIFIED_GRADE_IND in VARCHAR2,
578 X_MODE in VARCHAR2 default 'R'
579 ) AS
580 cursor c1 is select rowid from IGS_AS_INS_GRD_ENTRY
581 where KEYING_WHO = X_KEYING_WHO
582 and KEYING_TIME = X_KEYING_TIME
583 and STUDENT_SEQUENCE = X_STUDENT_SEQUENCE
584 ;
585 begin
586 open c1;
587 fetch c1 into X_ROWID;
588 if (c1%notfound) then
589 close c1;
590 INSERT_ROW (
591 X_ROWID,
592 X_KEYING_WHO,
593 X_KEYING_TIME,
594 X_STUDENT_SEQUENCE,
595 X_PERSON_ID,
596 X_NAME,
597 X_COURSE_CD,
598 X_UNIT_CD,
599 X_VERSION_NUMBER,
600 X_CAL_TYPE,
601 X_CI_SEQUENCE_NUMBER,
602 X_LOCATION_CD,
603 X_UNIT_CLASS,
604 X_UNIT_ATTEMPT_STATUS,
605 X_MARK,
606 X_GRADING_SCHEMA_CD,
607 X_GS_VERSION_NUMBER,
608 X_GRADE,
609 X_SPECIFIED_GRADE_IND,
610 X_MODE);
611 return;
612 end if;
613 close c1;
614 UPDATE_ROW (
615 X_ROWID,
616 X_KEYING_WHO,
617 X_KEYING_TIME,
618 X_STUDENT_SEQUENCE,
619 X_PERSON_ID,
620 X_NAME,
621 X_COURSE_CD,
622 X_UNIT_CD,
623 X_VERSION_NUMBER,
624 X_CAL_TYPE,
625 X_CI_SEQUENCE_NUMBER,
626 X_LOCATION_CD,
627 X_UNIT_CLASS,
628 X_UNIT_ATTEMPT_STATUS,
629 X_MARK,
630 X_GRADING_SCHEMA_CD,
631 X_GS_VERSION_NUMBER,
632 X_GRADE,
633 X_SPECIFIED_GRADE_IND,
634 X_MODE);
635 end ADD_ROW;
636 procedure DELETE_ROW (
637 X_ROWID in VARCHAR2) AS
638 begin
639 Before_DML(
640 p_action => 'DELETE',
641 x_rowid => X_ROWID
642 );
643 delete from IGS_AS_INS_GRD_ENTRY
644 where ROWID = X_ROWID;
645 if (sql%notfound) then
646 raise no_data_found;
647 end if;
648
649 end DELETE_ROW;
650
651 PROCEDURE Check_Constraints (
652 Column_Name IN VARCHAR2 DEFAULT NULL,
653 Column_Value IN VARCHAR2 DEFAULT NULL
654 )
655 AS
656 BEGIN
657 IF column_name is null then
658 NULL;
659 ELSIF upper(Column_name) = 'CAL_TYPE' then
660 new_references.CAL_TYPE := column_value;
661 ELSIF upper(Column_name) = 'COURSE_CD' then
662 new_references.COURSE_CD := column_value;
663 ELSIF upper(Column_name) = 'GRADE' then
664 new_references.GRADE := column_value;
665 ELSIF upper(Column_name) = 'GRADING_SCHEMA_CD' then
666 new_references.GRADING_SCHEMA_CD := column_value;
667 ELSIF upper(Column_name) = 'KEYING_WHO' then
668 new_references.KEYING_WHO := column_value;
669 ELSIF upper(Column_name) = 'LOCATION_CD' then
670 new_references.LOCATION_CD := column_value;
671 ELSIF upper(Column_name) = 'SPECIFIED_GRADE_IND' then
672 new_references.SPECIFIED_GRADE_IND := column_value;
673 ELSIF upper(Column_name) = 'UNIT_ATTEMPT_STATUS' then
674 new_references.UNIT_ATTEMPT_STATUS := column_value;
675 ELSIF upper(Column_name) = 'UNIT_CD' then
676 new_references.UNIT_CD := column_value;
677 ELSIF upper(Column_name) = 'UNIT_CLASS' then
678 new_references.UNIT_CLASS := column_value;
679 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
680 new_references.CI_SEQUENCE_NUMBER := igs_ge_number.to_num(column_value);
681
682 END IF ;
683
684 IF upper(column_name) = 'CAL_TYPE' OR
685 column_name is null Then
686 IF new_references.CAL_TYPE <> UPPER(new_references.CAL_TYPE) Then
687 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
688 IGS_GE_MSG_STACK.ADD;
689 APP_EXCEPTION.RAISE_EXCEPTION;
690 END IF;
691 END IF;
692 IF upper(column_name) = 'COURSE_CD' OR
693 column_name is null Then
694 IF new_references.COURSE_CD <> UPPER(new_references.COURSE_CD) Then
695 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
696 IGS_GE_MSG_STACK.ADD;
697 APP_EXCEPTION.RAISE_EXCEPTION;
698 END IF;
699 END IF;
700 IF upper(column_name) = 'GRADE' OR
701 column_name is null Then
702 IF new_references.GRADE <> UPPER(new_references.GRADE) Then
703 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
704 IGS_GE_MSG_STACK.ADD;
705 APP_EXCEPTION.RAISE_EXCEPTION;
706 END IF;
707 END IF;
708 IF upper(column_name) = 'GRADING_SCHEMA_CD' OR
712 IGS_GE_MSG_STACK.ADD;
709 column_name is null Then
710 IF new_references.GRADING_SCHEMA_CD <> UPPER(new_references.GRADING_SCHEMA_CD) Then
711 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
713 APP_EXCEPTION.RAISE_EXCEPTION;
714 END IF;
715 END IF;
716 IF upper(column_name) = 'KEYING_WHO' OR
717 column_name is null Then
718 IF new_references.KEYING_WHO <> UPPER(new_references.KEYING_WHO) Then
719 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
720 IGS_GE_MSG_STACK.ADD;
721 APP_EXCEPTION.RAISE_EXCEPTION;
722 END IF;
723 END IF;
724 IF upper(column_name) = 'LOCATION_CD' OR
725 column_name is null Then
726 IF new_references.LOCATION_CD <> UPPER(new_references.LOCATION_CD) Then
727 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
728 IGS_GE_MSG_STACK.ADD;
729 APP_EXCEPTION.RAISE_EXCEPTION;
730 END IF;
731 END IF;
732 IF upper(column_name) = 'SPECIFIED_GRADE_IND' OR
733 column_name is null Then
734 IF new_references.SPECIFIED_GRADE_IND <> UPPER(new_references.SPECIFIED_GRADE_IND) Then
735 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
736 IGS_GE_MSG_STACK.ADD;
737 APP_EXCEPTION.RAISE_EXCEPTION;
738 END IF;
739 END IF;
740 IF upper(column_name) = 'UNIT_ATTEMPT_STATUS' OR
741 column_name is null Then
742 IF new_references.UNIT_ATTEMPT_STATUS <> UPPER(new_references.UNIT_ATTEMPT_STATUS) Then
743 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
744 IGS_GE_MSG_STACK.ADD;
745 APP_EXCEPTION.RAISE_EXCEPTION;
746 END IF;
747 END IF;
748 IF upper(column_name) = 'UNIT_CD' OR
749 column_name is null Then
750 IF new_references.UNIT_CD <> UPPER(new_references.UNIT_CD) Then
751 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
752 IGS_GE_MSG_STACK.ADD;
753 APP_EXCEPTION.RAISE_EXCEPTION;
754 END IF;
755 END IF;
756 IF upper(column_name) = 'UNIT_CLASS' OR
757 column_name is null Then
758 IF new_references.UNIT_CLASS <> UPPER(new_references.UNIT_CLASS) Then
759 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
760 IGS_GE_MSG_STACK.ADD;
761 APP_EXCEPTION.RAISE_EXCEPTION;
762 END IF;
763 END IF;
764 IF upper(column_name) = 'CI_SEQUENCE_NUMBER' OR
765 column_name is null Then
766 IF new_references.CI_SEQUENCE_NUMBER < 1 OR new_references.CI_SEQUENCE_NUMBER > 999999 Then
767 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
768 IGS_GE_MSG_STACK.ADD;
769 APP_EXCEPTION.RAISE_EXCEPTION;
770 END IF;
771 END IF;
772
773 END Check_Constraints;
774 end IGS_AS_INS_GRD_ENTRY_PKG;