[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_NON_ENR_STDOT_PKG
Source
1 package body IGS_AS_NON_ENR_STDOT_PKG AS
2 /* $Header: IGSDI17B.pls 115.7 2002/11/28 23:14:38 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AS_NON_ENR_STDOT_ALL%RowType;
5 new_references IGS_AS_NON_ENR_STDOT_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 X_org_id IN NUMBER DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_unit_cd IN VARCHAR2 DEFAULT NULL,
13 x_version_number IN NUMBER DEFAULT NULL,
14 x_cal_type IN VARCHAR2 DEFAULT NULL,
15 x_ci_sequence_number IN NUMBER DEFAULT NULL,
16 x_course_cd IN VARCHAR2 DEFAULT NULL,
17 x_location_cd IN VARCHAR2 DEFAULT NULL,
18 x_unit_mode IN VARCHAR2 DEFAULT NULL,
19 x_unit_class IN VARCHAR2 DEFAULT NULL,
20 x_s_grade_creation_method_type IN VARCHAR2 DEFAULT NULL,
21 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
22 x_gs_version_number IN NUMBER DEFAULT NULL,
23 x_grade IN VARCHAR2 DEFAULT NULL,
24 x_mark IN NUMBER DEFAULT NULL,
25 x_resolved_ind IN VARCHAR2 DEFAULT NULL,
26 x_comments 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
34 CURSOR cur_old_ref_values IS
35 SELECT *
36 FROM IGS_AS_NON_ENR_STDOT_ALL
37 WHERE rowid = x_rowid;
38
39 BEGIN
40
41 l_rowid := x_rowid;
42
43 -- Code for setting the Old and New Reference Values.
44 -- Populate Old Values.
45 Open cur_old_ref_values;
46 Fetch cur_old_ref_values INTO old_references;
47 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
48 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
49 IGS_GE_MSG_STACK.ADD;
50 Close cur_old_ref_values;
51 APP_EXCEPTION.RAISE_EXCEPTION;
52
53 Return;
54 END IF;
55 Close cur_old_ref_values;
56
57 -- Populate New Values.
58 new_references.org_id := x_org_id;
59 new_references.person_id := x_person_id;
60 new_references.unit_cd := x_unit_cd;
61 new_references.version_number := x_version_number;
62 new_references.cal_type := x_cal_type;
63 new_references.ci_sequence_number := x_ci_sequence_number;
64 new_references.course_cd := x_course_cd;
65 new_references.location_cd := x_location_cd;
66 new_references.unit_mode := x_unit_mode;
67 new_references.unit_class := x_unit_class;
68 new_references.s_grade_creation_method_type := X_S_GRADE_CREATION_METHOD_TYPE;
69 new_references.grading_schema_cd := x_grading_schema_cd;
70 new_references.gs_version_number := x_gs_version_number;
71 new_references.grade := x_grade;
72 new_references.mark := x_mark;
73 new_references.resolved_ind := x_resolved_ind;
74 new_references.comments := x_comments;
75 IF (p_action = 'UPDATE') THEN
76 new_references.creation_date := old_references.creation_date;
77 new_references.created_by := old_references.created_by;
78 ELSE
79 new_references.creation_date := x_creation_date;
80 new_references.created_by := x_created_by;
81 END IF;
82 new_references.last_update_date := x_last_update_date;
83 new_references.last_updated_by := x_last_updated_by;
84 new_references.last_update_login := x_last_update_login;
85
86 END Set_Column_Values;
87
88 PROCEDURE BeforeRowInsertUpdate1(
89 p_inserting IN BOOLEAN DEFAULT FALSE,
90 p_updating IN BOOLEAN DEFAULT FALSE,
91 p_deleting IN BOOLEAN DEFAULT FALSE
92 ) AS
93 v_message_name VARCHAR2(30);
94 BEGIN
95 IF p_inserting OR p_updating THEN
96 IF IGS_AS_VAL_NESO.assp_val_neso_ins(
97 new_references.person_id,
98 new_references.course_cd,
99 new_references.unit_cd,
100 new_references.version_number,
101 new_references.mark,
102 new_references.grade,
103 new_references.grading_schema_cd,
104 new_references.gs_version_number,
105 new_references.s_grade_creation_method_type,
106 v_message_name) = FALSE THEN
107 FND_MESSAGE.SET_NAME('IGS',v_message_name);
108 IGS_GE_MSG_STACK.ADD;
109 APP_EXCEPTION.RAISE_EXCEPTION;
110 END IF;
111 END IF;
112 IF p_inserting OR
113 (p_updating AND old_references.location_cd <> new_references.location_cd AND
114 new_references.location_cd IS NOT NULL) THEN
115 IF IGS_AS_VAL_ELS.ORGP_VAL_LOC_CLOSED(
116 new_references.location_cd,
117 v_message_name) = FALSE THEN
118 FND_MESSAGE.SET_NAME('IGS',v_message_name);
119 IGS_GE_MSG_STACK.ADD;
120 APP_EXCEPTION.RAISE_EXCEPTION;
121 END IF;
122 END IF;
123 IF p_inserting OR
124 (p_updating AND old_references.unit_mode <> new_references.unit_mode AND
125 new_references.unit_mode IS NOT NULL) THEN
126 -- As part of the bug# 1956374 changed to the below call from IGS_AS_VAL_NESO.crsp_val_um_closed
127 IF IGS_AS_VAL_UAI.crsp_val_um_closed(
128 new_references.unit_mode,
129 v_message_name) = FALSE THEN
130 FND_MESSAGE.SET_NAME('IGS',v_message_name);
131 IGS_GE_MSG_STACK.ADD;
132 APP_EXCEPTION.RAISE_EXCEPTION;
133 END IF;
134 END IF;
135 IF p_inserting OR
136 (p_updating AND old_references.unit_class <> new_references.unit_class AND
137 new_references.unit_class IS NOT NULL) THEN
138
139 -- As part of the bug# 1956374 changed to the below call from IGS_AS_VAL_NESO.crsp_val_ucl_closed
140 IF IGS_AS_VAL_UAI.crsp_val_ucl_closed(
141 new_references.unit_mode,
142 v_message_name) = FALSE THEN
143 FND_MESSAGE.SET_NAME('IGS',v_message_name);
144 IGS_GE_MSG_STACK.ADD;
145 APP_EXCEPTION.RAISE_EXCEPTION;
146 END IF;
147 END IF;
148
149
150 END BeforeRowInsertUpdate1;
151
152
153 PROCEDURE Check_Parent_Existance AS
154 BEGIN
155
156 IF (((old_references.grading_schema_cd = new_references.grading_schema_cd) AND
157 (old_references.gs_version_number = new_references.gs_version_number) AND
158 (old_references.grade = new_references.grade)) OR
159 ((new_references.grading_schema_cd IS NULL) OR
160 (new_references.gs_version_number IS NULL) OR
161 (new_references.grade IS NULL))) THEN
162 NULL;
163 ELSIF NOT IGS_AS_GRD_SCH_GRADE_PKG.Get_PK_For_Validation (
164 new_references.grading_schema_cd,
165 new_references.gs_version_number,
166 new_references.grade ) THEN
167 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
168 IGS_GE_MSG_STACK.ADD;
169 APP_EXCEPTION.RAISE_EXCEPTION;
170 END IF;
171
172 IF (((old_references.person_id = new_references.person_id)) OR
173 ((new_references.person_id IS NULL))) THEN
174 NULL;
175 ELSIF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
176 new_references.person_id
177 ) THEN
178 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
179 IGS_GE_MSG_STACK.ADD;
180 APP_EXCEPTION.RAISE_EXCEPTION;
181 END IF;
182
183 IF (((old_references.s_grade_creation_method_type = new_references.s_grade_creation_method_type)) OR
184 ((new_references.s_grade_creation_method_type IS NULL))) THEN
185 NULL;
186 END IF;
187
188 IF (((old_references.unit_cd = new_references.unit_cd) OR
189 (old_references.version_number = new_references.version_number) OR
190 (old_references.cal_type = new_references.cal_type) OR
191 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
192 ((new_references.unit_cd IS NULL) OR
193 (new_references.version_number IS NULL) OR
194 (new_references.cal_type IS NULL) OR
195 (new_references.ci_sequence_number IS NULL))) THEN
196 NULL;
197 ELSIF NOT IGS_PS_UNIT_OFR_PAT_PKG.Get_PK_For_Validation (
198 new_references.unit_cd,
199 new_references.version_number,
200 new_references.cal_type,
201 new_references.ci_sequence_number
202 ) THEN
203 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
204 IGS_GE_MSG_STACK.ADD;
205 APP_EXCEPTION.RAISE_EXCEPTION;
206 END IF;
207
208 IF (((old_references.course_cd = new_references.course_cd)) OR
209 ((new_references.course_cd IS NULL))) THEN
210 NULL;
211 ELSif not iGS_PS_COURSE_PKG.Get_PK_For_Validation (
212 new_references.course_cd
213 )THEN
214 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
215 IGS_GE_MSG_STACK.ADD;
216 END IF;
217
218 END Check_Parent_Existance;
219
220 FUNCTION Get_PK_For_Validation (
221 x_person_id IN NUMBER,
222 x_unit_cd IN VARCHAR2,
223 x_version_number IN NUMBER,
224 x_cal_type IN VARCHAR2,
225 x_ci_sequence_number IN NUMBER
226 ) RETURN BOOLEAN AS
227
228 CURSOR cur_rowid IS
229 SELECT rowid
230 FROM IGS_AS_NON_ENR_STDOT_ALL
231 WHERE person_id = x_person_id
232 AND unit_cd = x_unit_cd
233 AND version_number = x_version_number
234 AND cal_type = x_cal_type
235 AND ci_sequence_number = x_ci_sequence_number
236 FOR UPDATE NOWAIT;
237
238 lv_rowid cur_rowid%RowType;
239
240 BEGIN
241
242 Open cur_rowid;
243 Fetch cur_rowid INTO lv_rowid;
244 IF (cur_rowid%FOUND) THEN
245 Close cur_rowid;
246 Return (TRUE);
247 ELSE
248 Close cur_rowid;
249 Return (FALSE);
250 END IF;
251
252 END Get_PK_For_Validation;
253
254 PROCEDURE GET_FK_IGS_AS_GRD_SCH_GRADE (
255 x_grading_schema_cd IN VARCHAR2,
256 x_version_number IN NUMBER,
257 x_grade IN VARCHAR2
258 ) AS
259
260 CURSOR cur_rowid IS
261 SELECT rowid
262 FROM IGS_AS_NON_ENR_STDOT_ALL
263 WHERE grading_schema_cd = x_grading_schema_cd
264 AND gs_version_number = x_version_number
265 AND grade = x_grade ;
266
267 lv_rowid cur_rowid%RowType;
268
269 BEGIN
270
271 Open cur_rowid;
272 Fetch cur_rowid INTO lv_rowid;
273 IF (cur_rowid%FOUND) THEN
274 Fnd_Message.Set_Name ('IGS', 'IGS_AS_NESO_GSG_FK');
275 IGS_GE_MSG_STACK.ADD;
276 Close cur_rowid;
277 APP_EXCEPTION.RAISE_EXCEPTION;
278
279 Return;
280 END IF;
281 Close cur_rowid;
282
283 END GET_FK_IGS_AS_GRD_SCH_GRADE;
284
285 PROCEDURE GET_FK_IGS_PE_PERSON (
286 x_person_id IN NUMBER
287 ) AS
288
289 CURSOR cur_rowid IS
290 SELECT rowid
291 FROM IGS_AS_NON_ENR_STDOT_ALL
292 WHERE person_id = x_person_id ;
293
294 lv_rowid cur_rowid%RowType;
295
296 BEGIN
297
298 Open cur_rowid;
299 Fetch cur_rowid INTO lv_rowid;
300 IF (cur_rowid%FOUND) THEN
301 Fnd_Message.Set_Name ('IGS', 'IGS_AS_NESO_PE_FK');
302 IGS_GE_MSG_STACK.ADD;
303 Close cur_rowid;
304 APP_EXCEPTION.RAISE_EXCEPTION;
305
306 Return;
307 END IF;
308 Close cur_rowid;
309
310 END GET_FK_IGS_PE_PERSON;
311
312 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW(
313 x_s_grade_creation_method_type IN VARCHAR2
314 ) AS
315
316 CURSOR cur_rowid IS
317 SELECT rowid
318 FROM IGS_AS_NON_ENR_STDOT_ALL
319 WHERE s_grade_creation_method_type = x_s_grade_creation_method_type ;
320
321 lv_rowid cur_rowid%RowType;
322
323 BEGIN
324
325 Open cur_rowid;
326 Fetch cur_rowid INTO lv_rowid;
327 IF (cur_rowid%FOUND) THEN
328 Fnd_Message.Set_Name ('IGS', 'IGS_AS_NESO_SLV_FK');
329 IGS_GE_MSG_STACK.ADD;
330 Close cur_rowid;
331 APP_EXCEPTION.RAISE_EXCEPTION;
332
333 Return;
334 END IF;
335 Close cur_rowid;
336
337 END GET_FK_IGS_LOOKUPS_VIEW;
338
339 PROCEDURE GET_FK_IGS_PS_UNIT_OFR_PAT (
340 x_unit_cd IN VARCHAR2,
341 x_version_number IN NUMBER,
342 x_cal_type IN VARCHAR2,
343 x_ci_sequence_number IN NUMBER
344 ) AS
345
346 CURSOR cur_rowid IS
347 SELECT rowid
348 FROM IGS_AS_NON_ENR_STDOT_ALL
349 WHERE unit_cd = x_unit_cd
350 AND version_number = x_version_number
351 AND cal_type = x_cal_type
352 AND ci_sequence_number = x_ci_sequence_number ;
353
354 lv_rowid cur_rowid%RowType;
355
356 BEGIN
357
358 Open cur_rowid;
359 Fetch cur_rowid INTO lv_rowid;
360 IF (cur_rowid%FOUND) THEN
361 Fnd_Message.Set_Name ('IGS', 'IGS_AS_NESO_UOP_FK');
362 IGS_GE_MSG_STACK.ADD;
363 Close cur_rowid;
364 APP_EXCEPTION.RAISE_EXCEPTION;
365
366 Return;
367 END IF;
368 Close cur_rowid;
369
370 END GET_FK_IGS_PS_UNIT_OFR_PAT;
371
372 PROCEDURE GET_FK_IGS_PS_COURSE (
373 x_course_cd IN VARCHAR2
374 ) AS
375
376 CURSOR cur_rowid IS
377 SELECT rowid
378 FROM IGS_AS_NON_ENR_STDOT_ALL
379 WHERE course_cd = x_course_cd ;
380
381 lv_rowid cur_rowid%RowType;
382
383 BEGIN
384
385 Open cur_rowid;
386 Fetch cur_rowid INTO lv_rowid;
387 IF (cur_rowid%FOUND) THEN
388 Fnd_Message.Set_Name ('IGS', 'IGS_AS_NESO_CRS_FK');
389 IGS_GE_MSG_STACK.ADD;
390 Close cur_rowid;
391 APP_EXCEPTION.RAISE_EXCEPTION;
392
393 Return;
394 END IF;
395 Close cur_rowid;
396
397 END GET_FK_IGS_PS_COURSE;
398
399 PROCEDURE Before_DML (
400 p_action IN VARCHAR2,
401 x_rowid IN VARCHAR2 DEFAULT NULL,
402 x_org_id IN NUMBER DEFAULT NULL,
403 x_person_id IN NUMBER DEFAULT NULL,
404 x_unit_cd IN VARCHAR2 DEFAULT NULL,
405 x_version_number IN NUMBER DEFAULT NULL,
406 x_cal_type IN VARCHAR2 DEFAULT NULL,
407 x_ci_sequence_number IN NUMBER DEFAULT NULL,
408 x_course_cd IN VARCHAR2 DEFAULT NULL,
409 x_location_cd IN VARCHAR2 DEFAULT NULL,
410 x_unit_mode IN VARCHAR2 DEFAULT NULL,
411 x_unit_class IN VARCHAR2 DEFAULT NULL,
412 x_s_grade_creation_method_type IN VARCHAR2 DEFAULT NULL,
413 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
414 x_gs_version_number IN NUMBER DEFAULT NULL,
415 x_grade IN VARCHAR2 DEFAULT NULL,
416 x_mark IN NUMBER DEFAULT NULL,
417 x_resolved_ind IN VARCHAR2 DEFAULT NULL,
418 x_comments IN VARCHAR2 DEFAULT NULL,
419 x_creation_date IN DATE DEFAULT NULL,
420 x_created_by IN NUMBER DEFAULT NULL,
421 x_last_update_date IN DATE DEFAULT NULL,
422 x_last_updated_by IN NUMBER DEFAULT NULL,
423 x_last_update_login IN NUMBER DEFAULT NULL
424 ) AS
425 BEGIN
426
427 Set_Column_Values (
428 p_action,
429 x_rowid,
430 x_org_id,
431 x_person_id,
432 x_unit_cd,
433 x_version_number,
434 x_cal_type,
435 x_ci_sequence_number,
436 x_course_cd,
437 x_location_cd,
438 x_unit_mode,
439 x_unit_class,
440 X_S_GRADE_CREATION_METHOD_TYPE,
441 x_grading_schema_cd,
442 x_gs_version_number,
443 x_grade,
444 x_mark,
445 x_resolved_ind,
446 x_comments,
447 x_creation_date,
448 x_created_by,
449 x_last_update_date,
450 x_last_updated_by,
451 x_last_update_login
452 );
453
454 IF (p_action = 'INSERT') THEN
455 -- Call all the procedures related to Before Insert.
456 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
457
458 IF Get_PK_For_Validation (
459 NEW_REFERENCES.person_id ,
460 NEW_REFERENCES.unit_cd ,
461 NEW_REFERENCES.version_number,
462 NEW_REFERENCES.cal_type ,
463 NEW_REFERENCES.ci_sequence_number
464 ) THEN
465 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
466 IGS_GE_MSG_STACK.ADD;
467 APP_EXCEPTION.RAISE_EXCEPTION;
468 END IF;
469
470 Check_Constraints;
471
472 Check_Parent_Existance;
473 ELSIF (p_action = 'UPDATE') THEN
474 -- Call all the procedures related to Before Update.
475 BeforeRowInsertUpdate1 ( p_updating => TRUE );
476 Check_Constraints;
477 Check_Parent_Existance;
478
479 ELSIF (p_action = 'VALIDATE_INSERT') THEN
480 IF Get_PK_For_Validation (
481 NEW_REFERENCES.person_id ,
482 NEW_REFERENCES.unit_cd ,
483 NEW_REFERENCES.version_number,
484 NEW_REFERENCES.cal_type ,
485 NEW_REFERENCES.ci_sequence_number
486 ) THEN
487 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
488 IGS_GE_MSG_STACK.ADD;
489 APP_EXCEPTION.RAISE_EXCEPTION;
490 END IF;
491
492 Check_Constraints;
493 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
494
495 Check_Constraints;
496 END IF;
497
498
499
500 END Before_DML;
501
502
503
504
505 procedure INSERT_ROW (
506 X_ROWID in out NOCOPY VARCHAR2,
507 X_ORG_ID in NUMBER,
508 X_PERSON_ID in NUMBER,
509 X_UNIT_CD in VARCHAR2,
510 X_VERSION_NUMBER in NUMBER,
511 X_CAL_TYPE in VARCHAR2,
512 X_CI_SEQUENCE_NUMBER in NUMBER,
513 X_COURSE_CD in VARCHAR2,
514 X_LOCATION_CD in VARCHAR2,
515 X_UNIT_MODE in VARCHAR2,
516 X_UNIT_CLASS in VARCHAR2,
517 X_s_grade_creation_method_type in VARCHAR2,
518 X_GRADING_SCHEMA_CD in VARCHAR2,
519 X_GS_VERSION_NUMBER in NUMBER,
520 X_GRADE in VARCHAR2,
521 X_MARK in NUMBER,
522 X_RESOLVED_IND in VARCHAR2,
523 X_COMMENTS in VARCHAR2,
524 X_MODE in VARCHAR2 default 'R'
525 ) AS
526 cursor C is select ROWID from IGS_AS_NON_ENR_STDOT_ALL
527 where PERSON_ID = X_PERSON_ID
528 and UNIT_CD = X_UNIT_CD
529 and VERSION_NUMBER = X_VERSION_NUMBER
530 and CAL_TYPE = X_CAL_TYPE
531 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER;
532 X_LAST_UPDATE_DATE DATE;
533 X_LAST_UPDATED_BY NUMBER;
534 X_LAST_UPDATE_LOGIN NUMBER;
535 X_REQUEST_ID NUMBER;
536 X_PROGRAM_ID NUMBER;
537 X_PROGRAM_APPLICATION_ID NUMBER;
538 X_PROGRAM_UPDATE_DATE DATE;
539 begin
540 X_LAST_UPDATE_DATE := SYSDATE;
541 if(X_MODE = 'I') then
542 X_LAST_UPDATED_BY := 1;
543 X_LAST_UPDATE_LOGIN := 0;
544 elsif (X_MODE = 'R') then
545 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
546 if X_LAST_UPDATED_BY is NULL then
547 X_LAST_UPDATED_BY := -1;
548 end if;
549 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
550 if X_LAST_UPDATE_LOGIN is NULL then
551 X_LAST_UPDATE_LOGIN := -1;
552 end if;
553 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
554 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
555
556 X_PROGRAM_APPLICATION_ID :=
557 FND_GLOBAL.PROG_APPL_ID;
558 if (X_REQUEST_ID = -1) then
559 X_REQUEST_ID := NULL;
560 X_PROGRAM_ID := NULL;
561 X_PROGRAM_APPLICATION_ID := NULL;
562 X_PROGRAM_UPDATE_DATE := NULL;
563 else
564 X_PROGRAM_UPDATE_DATE := SYSDATE;
565 end if;
566 else
567 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
568 IGS_GE_MSG_STACK.ADD;
569 APP_EXCEPTION.RAISE_EXCEPTION;
570 end if;
571
572 Before_DML(
573 p_action=>'INSERT',
574 x_rowid=>X_ROWID,
575 x_org_id => igs_ge_gen_003.get_org_id,
576 x_cal_type=>X_CAL_TYPE,
577 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
578 x_comments=>X_COMMENTS,
579 x_course_cd=>X_COURSE_CD,
580 x_grade=>X_GRADE,
581 x_grading_schema_cd=>X_GRADING_SCHEMA_CD,
582 x_gs_version_number=>X_GS_VERSION_NUMBER,
583 x_location_cd=>X_LOCATION_CD,
584 x_mark=>X_MARK,
585 x_person_id=>X_PERSON_ID,
586 x_resolved_ind=> NVL(X_RESOLVED_IND,'N'),
587 X_S_GRADE_CREATION_METHOD_TYPE=>X_S_GRADE_CREATION_METHOD_TYPE,
588 x_unit_cd=>X_UNIT_CD,
589 x_unit_class=>X_UNIT_CLASS,
590 x_unit_mode=>X_UNIT_MODE,
591 x_version_number=>X_VERSION_NUMBER,
592 x_creation_date=>X_LAST_UPDATE_DATE,
593 x_created_by=>X_LAST_UPDATED_BY,
594 x_last_update_date=>X_LAST_UPDATE_DATE,
595 x_last_updated_by=>X_LAST_UPDATED_BY,
596 x_last_update_login=>X_LAST_UPDATE_LOGIN
597 );
598
599 insert into IGS_AS_NON_ENR_STDOT_ALL (
600 ORG_ID,
601 PERSON_ID,
602 UNIT_CD,
603 VERSION_NUMBER,
604 CAL_TYPE,
605 CI_SEQUENCE_NUMBER,
606 COURSE_CD,
607 LOCATION_CD,
608 UNIT_MODE,
609 UNIT_CLASS,
610 S_GRADE_CREATION_METHOD_TYPE,
611 GRADING_SCHEMA_CD,
612 GS_VERSION_NUMBER,
613 GRADE,
614 MARK,
615 RESOLVED_IND,
616 COMMENTS,
617 CREATION_DATE,
618 CREATED_BY,
619 LAST_UPDATE_DATE,
620 LAST_UPDATED_BY,
621 LAST_UPDATE_LOGIN,
622 REQUEST_ID,
623 PROGRAM_ID,
624 PROGRAM_APPLICATION_ID,
625 PROGRAM_UPDATE_DATE
626 ) values (
627 NEW_REFERENCES.ORG_ID,
628 NEW_REFERENCES.PERSON_ID,
629 NEW_REFERENCES.UNIT_CD,
630 NEW_REFERENCES.VERSION_NUMBER,
631 NEW_REFERENCES.CAL_TYPE,
632 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
633 NEW_REFERENCES.COURSE_CD,
634 NEW_REFERENCES.LOCATION_CD,
635 NEW_REFERENCES.UNIT_MODE,
636 NEW_REFERENCES.UNIT_CLASS,
637 NEW_REFERENCES.S_GRADE_CREATION_METHOD_TYPE,
638 NEW_REFERENCES.GRADING_SCHEMA_CD,
639 NEW_REFERENCES.GS_VERSION_NUMBER,
640 NEW_REFERENCES.GRADE,
641 NEW_REFERENCES.MARK,
642 NEW_REFERENCES.RESOLVED_IND,
643 NEW_REFERENCES.COMMENTS,
644 X_LAST_UPDATE_DATE,
645 X_LAST_UPDATED_BY,
646 X_LAST_UPDATE_DATE,
647 X_LAST_UPDATED_BY,
648 X_LAST_UPDATE_LOGIN,
649 X_REQUEST_ID,
650 X_PROGRAM_ID,
651 X_PROGRAM_APPLICATION_ID,
652 X_PROGRAM_UPDATE_DATE
653 );
654 open c;
655 fetch c into X_ROWID;
656 if (c%notfound) then
657 close c;
658 raise no_data_found;
659 end if;
660 close c;
661
662
663 end INSERT_ROW;
664
665 procedure LOCK_ROW (
666 X_ROWID in VARCHAR2,
667 X_PERSON_ID in NUMBER,
668 X_UNIT_CD in VARCHAR2,
669 X_VERSION_NUMBER in NUMBER,
670 X_CAL_TYPE in VARCHAR2,
671 X_CI_SEQUENCE_NUMBER in NUMBER,
672 X_COURSE_CD in VARCHAR2,
673 X_LOCATION_CD in VARCHAR2,
674 X_UNIT_MODE in VARCHAR2,
675 X_UNIT_CLASS in VARCHAR2,
676 X_s_grade_creation_method_type in VARCHAR2,
677 X_GRADING_SCHEMA_CD in VARCHAR2,
678 X_GS_VERSION_NUMBER in NUMBER,
679 X_GRADE in VARCHAR2,
680 X_MARK in NUMBER,
681 X_RESOLVED_IND in VARCHAR2,
682 X_COMMENTS in VARCHAR2
683 ) AS
684 cursor c1 is select
685 COURSE_CD,
686 LOCATION_CD,
687 UNIT_MODE,
688 UNIT_CLASS,
689 S_GRADE_CREATION_METHOD_TYPE,
690 GRADING_SCHEMA_CD,
691 GS_VERSION_NUMBER,
692 GRADE,
693 MARK,
694 RESOLVED_IND,
695 COMMENTS
696 from IGS_AS_NON_ENR_STDOT_ALL
697 where ROWID = X_ROWID for update nowait;
698 tlinfo c1%rowtype;
699
700 begin
701 open c1;
702 fetch c1 into tlinfo;
703 if (c1%notfound) then
704 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
705 IGS_GE_MSG_STACK.ADD;
706 APP_EXCEPTION.RAISE_EXCEPTION;
707 close c1;
708 return;
709 end if;
710 close c1;
711
712 if ( ((tlinfo.COURSE_CD = X_COURSE_CD)
713 OR ((tlinfo.COURSE_CD is null)
714 AND (X_COURSE_CD is null)))
715 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
716 OR ((tlinfo.LOCATION_CD is null)
717 AND (X_LOCATION_CD is null)))
718 AND ((tlinfo.UNIT_MODE = X_UNIT_MODE)
719 OR ((tlinfo.UNIT_MODE is null)
720 AND (X_UNIT_MODE is null)))
721 AND ((tlinfo.UNIT_CLASS = X_UNIT_CLASS)
722 OR ((tlinfo.UNIT_CLASS is null)
723 AND (X_UNIT_CLASS is null)))
724 AND (tlinfo.S_GRADE_CREATION_METHOD_TYPE = X_S_GRADE_CREATION_METHOD_TYPE)
725 AND ((tlinfo.GRADING_SCHEMA_CD = X_GRADING_SCHEMA_CD)
726 OR ((tlinfo.GRADING_SCHEMA_CD is null)
727 AND (X_GRADING_SCHEMA_CD is null)))
728 AND ((tlinfo.GS_VERSION_NUMBER = X_GS_VERSION_NUMBER)
729 OR ((tlinfo.GS_VERSION_NUMBER is null)
730 AND (X_GS_VERSION_NUMBER is null)))
731 AND ((tlinfo.GRADE = X_GRADE)
732 OR ((tlinfo.GRADE is null)
733 AND (X_GRADE is null)))
734 AND ((tlinfo.MARK = X_MARK)
735 OR ((tlinfo.MARK is null)
736 AND (X_MARK is null)))
737 AND ((tlinfo.RESOLVED_IND = X_RESOLVED_IND)
738 OR ((tlinfo.RESOLVED_IND is null)
739 AND (X_RESOLVED_IND is null)))
740 AND ((tlinfo.COMMENTS = X_COMMENTS)
741 OR ((tlinfo.COMMENTS is null)
742 AND (X_COMMENTS is null)))
743 ) then
744 null;
745 else
746 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
747 IGS_GE_MSG_STACK.ADD;
748 APP_EXCEPTION.RAISE_EXCEPTION;
749 end if;
750 return;
751 end LOCK_ROW;
752
753 procedure UPDATE_ROW (
754 X_ROWID in VARCHAR2,
755 X_PERSON_ID in NUMBER,
756 X_UNIT_CD in VARCHAR2,
757 X_VERSION_NUMBER in NUMBER,
758 X_CAL_TYPE in VARCHAR2,
759 X_CI_SEQUENCE_NUMBER in NUMBER,
760 X_COURSE_CD in VARCHAR2,
761 X_LOCATION_CD in VARCHAR2,
762 X_UNIT_MODE in VARCHAR2,
763 X_UNIT_CLASS in VARCHAR2,
764 X_s_grade_creation_method_type in VARCHAR2,
765 X_GRADING_SCHEMA_CD in VARCHAR2,
766 X_GS_VERSION_NUMBER in NUMBER,
767 X_GRADE in VARCHAR2,
768 X_MARK in NUMBER,
769 X_RESOLVED_IND in VARCHAR2,
770 X_COMMENTS in VARCHAR2,
771 X_MODE in VARCHAR2 default 'R'
772 ) AS
773 X_LAST_UPDATE_DATE DATE;
774 X_LAST_UPDATED_BY NUMBER;
775 X_LAST_UPDATE_LOGIN NUMBER;
776 X_REQUEST_ID NUMBER;
777 X_PROGRAM_ID NUMBER;
778 X_PROGRAM_APPLICATION_ID NUMBER;
779 X_PROGRAM_UPDATE_DATE DATE;
780
781 begin
782 X_LAST_UPDATE_DATE := SYSDATE;
783 if(X_MODE = 'I') then
784 X_LAST_UPDATED_BY := 1;
785 X_LAST_UPDATE_LOGIN := 0;
786 elsif (X_MODE = 'R') then
787 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
788 if X_LAST_UPDATED_BY is NULL then
789 X_LAST_UPDATED_BY := -1;
790 end if;
791 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
792 if X_LAST_UPDATE_LOGIN is NULL then
793 X_LAST_UPDATE_LOGIN := -1;
794 end if;
795 else
796 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
797 IGS_GE_MSG_STACK.ADD;
798 APP_EXCEPTION.RAISE_EXCEPTION;
799 end if;
800
801 Before_DML(
802 p_action=>'UPDATE',
803 x_rowid=>X_ROWID,
804 x_cal_type=>X_CAL_TYPE,
805 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
806 x_comments=>X_COMMENTS,
807 x_course_cd=>X_COURSE_CD,
808 x_grade=>X_GRADE,
809 x_grading_schema_cd=>X_GRADING_SCHEMA_CD,
810 x_gs_version_number=>X_GS_VERSION_NUMBER,
811 x_location_cd=>X_LOCATION_CD,
812 x_mark=>X_MARK,
813 x_person_id=>X_PERSON_ID,
814 x_resolved_ind=>X_RESOLVED_IND,
815 X_S_GRADE_CREATION_METHOD_TYPE=>X_S_GRADE_CREATION_METHOD_TYPE,
816 x_unit_cd=>X_UNIT_CD,
817 x_unit_class=>X_UNIT_CLASS,
818 x_unit_mode=>X_UNIT_MODE,
819 x_version_number=>X_VERSION_NUMBER,
820 x_creation_date=>X_LAST_UPDATE_DATE,
821 x_created_by=>X_LAST_UPDATED_BY,
822 x_last_update_date=>X_LAST_UPDATE_DATE,
823 x_last_updated_by=>X_LAST_UPDATED_BY,
824 x_last_update_login=>X_LAST_UPDATE_LOGIN
825 );
826
827 if (X_MODE = 'R') then
828 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
829 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
830 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
831 if (X_REQUEST_ID = -1) then
832 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
833 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
834 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
835 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
836 else
837 X_PROGRAM_UPDATE_DATE := SYSDATE;
838 end if;
839 end if;
840
841
842 update IGS_AS_NON_ENR_STDOT_ALL set
843 COURSE_CD = NEW_REFERENCES.COURSE_CD,
844 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
845 UNIT_MODE = NEW_REFERENCES.UNIT_MODE,
846 UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
847 S_GRADE_CREATION_METHOD_TYPE = NEW_REFERENCES.S_GRADE_CREATION_METHOD_TYPE,
848 GRADING_SCHEMA_CD = NEW_REFERENCES.GRADING_SCHEMA_CD,
849 GS_VERSION_NUMBER = NEW_REFERENCES.GS_VERSION_NUMBER,
850 GRADE = NEW_REFERENCES.GRADE,
851 MARK = NEW_REFERENCES.MARK,
852 RESOLVED_IND = NEW_REFERENCES.RESOLVED_IND,
853 COMMENTS = NEW_REFERENCES.COMMENTS,
854 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
855 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
856 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
857 REQUEST_ID = X_REQUEST_ID,
858 PROGRAM_ID = X_PROGRAM_ID,
859 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
860 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
861 where ROWID = X_ROWID;
862
863 if (sql%notfound) then
864 raise no_data_found;
865 end if;
866
867 end UPDATE_ROW;
868
869 procedure ADD_ROW (
870 X_ROWID in out NOCOPY VARCHAR2,
871 X_ORG_ID in NUMBER,
872 X_PERSON_ID in NUMBER,
873 X_UNIT_CD in VARCHAR2,
874 X_VERSION_NUMBER in NUMBER,
875 X_CAL_TYPE in VARCHAR2,
876 X_CI_SEQUENCE_NUMBER in NUMBER,
877 X_COURSE_CD in VARCHAR2,
878 X_LOCATION_CD in VARCHAR2,
879 X_UNIT_MODE in VARCHAR2,
880 X_UNIT_CLASS in VARCHAR2,
881 X_s_grade_creation_method_type in VARCHAR2,
882 X_GRADING_SCHEMA_CD in VARCHAR2,
883 X_GS_VERSION_NUMBER in NUMBER,
884 X_GRADE in VARCHAR2,
885 X_MARK in NUMBER,
886 X_RESOLVED_IND in VARCHAR2,
887 X_COMMENTS in VARCHAR2,
888 X_MODE in VARCHAR2 default 'R'
889 ) AS
890 cursor c1 is select rowid from IGS_AS_NON_ENR_STDOT_ALL
891 where PERSON_ID = X_PERSON_ID
892 and UNIT_CD = X_UNIT_CD
893 and VERSION_NUMBER = X_VERSION_NUMBER
894 and CAL_TYPE = X_CAL_TYPE
895 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
896 ;
897 begin
898 open c1;
899 fetch c1 into X_ROWID;
900 if (c1%notfound) then
901 close c1;
902 INSERT_ROW (
903 X_ROWID,
904 X_ORG_ID,
905 X_PERSON_ID,
906 X_UNIT_CD,
907 X_VERSION_NUMBER,
908 X_CAL_TYPE,
909 X_CI_SEQUENCE_NUMBER,
910 X_COURSE_CD,
911 X_LOCATION_CD,
912 X_UNIT_MODE,
913 X_UNIT_CLASS,
914 X_S_GRADE_CREATION_METHOD_TYPE,
915 X_GRADING_SCHEMA_CD,
916 X_GS_VERSION_NUMBER,
917 X_GRADE,
918 X_MARK,
919 X_RESOLVED_IND,
920 X_COMMENTS,
921 X_MODE);
922 return;
923 end if;
924 close c1;
925 UPDATE_ROW (
926 X_ROWID,
927 X_PERSON_ID,
928 X_UNIT_CD,
929 X_VERSION_NUMBER,
930 X_CAL_TYPE,
931 X_CI_SEQUENCE_NUMBER,
932 X_COURSE_CD,
933 X_LOCATION_CD,
934 X_UNIT_MODE,
935 X_UNIT_CLASS,
936 X_S_GRADE_CREATION_METHOD_TYPE,
937 X_GRADING_SCHEMA_CD,
938 X_GS_VERSION_NUMBER,
939 X_GRADE,
940 X_MARK,
941 X_RESOLVED_IND,
942 X_COMMENTS,
943 X_MODE);
944 end ADD_ROW;
945
946 procedure DELETE_ROW (
947 X_ROWID in VARCHAR2) AS
948 begin
949 Before_DML(
950 p_action => 'DELETE',
951 x_rowid => X_ROWID
952 );
953 delete from IGS_AS_NON_ENR_STDOT_ALL
954 where ROWID = X_ROWID;
955 if (sql%notfound) then
956 raise no_data_found;
957 end if;
958
959 end DELETE_ROW;
960
961 PROCEDURE Check_Constraints (
962 Column_Name IN VARCHAR2 DEFAULT NULL,
963 Column_Value IN VARCHAR2 DEFAULT NULL
964 )
965 AS
966 BEGIN
967
968 IF column_name is null then
969 NULL;
970 ELSIF upper(Column_name) = 'CAL_TYPE' then
971 new_references.CAL_TYPE := column_value;
972 ELSIF upper(Column_name) = 'COURSE_CD' then
973 new_references.COURSE_CD := column_value;
974 ELSIF upper(Column_name) = 'GRADE' then
975 new_references.GRADE := column_value;
976 ELSIF upper(Column_name) = 'GRADING_SCHEMA_CD' then
977 new_references.GRADING_SCHEMA_CD := column_value;
978 ELSIF upper(Column_name) = 'LOCATION_CD' then
979 new_references.LOCATION_CD := column_value;
980 ELSIF upper(Column_name) = 'S_GRADE_CREATION_METHOD_TYPE' then
981 new_references.S_GRADE_CREATION_METHOD_TYPE := column_value;
982 ELSIF upper(Column_name) = 'UNIT_CD' then
983 new_references.UNIT_CD := column_value;
984 ELSIF upper(Column_name) = 'UNIT_CLASS' then
985 new_references.UNIT_CLASS := column_value;
986 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
987 new_references.CI_SEQUENCE_NUMBER := IGS_GE_NUMBER.TO_NUM(column_value);
988 ELSIF upper(Column_name) = 'MARK' then
989 new_references.MARK := IGS_GE_NUMBER.TO_NUM(column_value);
990 ELSIF upper(Column_name) = 'RESOLVED_IND' then
991 new_references.RESOLVED_IND := column_value;
992 END IF;
993
994 IF upper(column_name) = 'CAL_TYPE' OR
995 column_name is null Then
996 IF new_references.CAL_TYPE <> UPPER(new_references.CAL_TYPE) Then
997 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
998 IGS_GE_MSG_STACK.ADD;
999 APP_EXCEPTION.RAISE_EXCEPTION;
1000 END IF;
1001 END IF;
1002 IF upper(column_name) = 'COURSE_CD' OR
1003 column_name is null Then
1004 IF new_references.COURSE_CD <> UPPER(new_references.COURSE_CD) Then
1005 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1006 IGS_GE_MSG_STACK.ADD;
1007 APP_EXCEPTION.RAISE_EXCEPTION;
1008 END IF;
1009 END IF;
1010 IF upper(column_name) = 'GRADE' OR
1011 column_name is null Then
1012 IF new_references.GRADE <> UPPER(new_references.GRADE) Then
1013 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1014 IGS_GE_MSG_STACK.ADD;
1015 APP_EXCEPTION.RAISE_EXCEPTION;
1016 END IF;
1017 END IF;
1018 IF upper(column_name) = 'GRADING_SCHEMA_CD' OR
1019 column_name is null Then
1020 IF new_references.GRADING_SCHEMA_CD <> UPPER(new_references.GRADING_SCHEMA_CD) Then
1021 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1022 IGS_GE_MSG_STACK.ADD;
1023 APP_EXCEPTION.RAISE_EXCEPTION;
1024 END IF;
1025 END IF;
1026 IF upper(column_name) = 'LOCATION_CD' OR
1027 column_name is null Then
1028 IF new_references.LOCATION_CD <> UPPER(new_references.LOCATION_CD) Then
1029 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1030 IGS_GE_MSG_STACK.ADD;
1031 APP_EXCEPTION.RAISE_EXCEPTION;
1032 END IF;
1033 END IF;
1034 IF upper(column_name) = 'S_GRADE_CREATION_METHOD_TYPE' OR
1035 column_name is null Then
1036 IF new_references.S_GRADE_CREATION_METHOD_TYPE <> UPPER(new_references.S_GRADE_CREATION_METHOD_TYPE) Then
1037 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1038 IGS_GE_MSG_STACK.ADD;
1039 APP_EXCEPTION.RAISE_EXCEPTION;
1040 END IF;
1041 END IF;
1042 IF upper(column_name) = 'UNIT_CD' OR
1043 column_name is null Then
1044 IF new_references.UNIT_CD <> UPPER(new_references.UNIT_CD) Then
1045 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1046 IGS_GE_MSG_STACK.ADD;
1047 APP_EXCEPTION.RAISE_EXCEPTION;
1048 END IF;
1049 END IF;
1050 IF upper(column_name) = 'UNIT_CLASS' OR
1051 column_name is null Then
1052 IF new_references.UNIT_CLASS <> UPPER(new_references.UNIT_CLASS) Then
1053 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1054 IGS_GE_MSG_STACK.ADD;
1055 APP_EXCEPTION.RAISE_EXCEPTION;
1056 END IF;
1057 END IF;
1058
1059
1060 IF upper(column_name) = 'CI_SEQUENCE_NUMBER' OR
1061 column_name is null Then
1062 IF new_references.CI_SEQUENCE_NUMBER < 1 OR new_references.CI_SEQUENCE_NUMBER > 999999 Then
1063 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1064 IGS_GE_MSG_STACK.ADD;
1065 APP_EXCEPTION.RAISE_EXCEPTION;
1066 END IF;
1067 END IF;
1068
1069
1070 IF upper(column_name) = 'MARK' OR
1071 column_name is null Then
1072 IF new_references.MARK < 0 OR new_references.MARK > 999 Then
1073 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1074 IGS_GE_MSG_STACK.ADD;
1075 APP_EXCEPTION.RAISE_EXCEPTION;
1076 END IF;
1077 END IF;
1078
1079
1080 IF upper(column_name) = 'RESOLVED_IND' OR
1081 column_name is null Then
1082 IF new_references.RESOLVED_IND NOT IN ('Y','N') Then
1083 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1084 IGS_GE_MSG_STACK.ADD;
1085 APP_EXCEPTION.RAISE_EXCEPTION;
1086 END IF;
1087 END IF;
1088 END Check_Constraints;
1089
1090
1091
1092 end IGS_AS_NON_ENR_STDOT_PKG;