1 PACKAGE BODY IGS_AS_VAL_NESO AS
2 /* $Header: IGSAS26B.pls 115.6 2002/11/28 22:45:58 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 26-AUG-2001 Bug No. 1956374 .The function orgp_val_loc_closed removed
7 -------------------------------------------------------------------------------------------
8
9 -- Validate the insert of a IGS_AS_NON_ENR_STDOT record
10 FUNCTION ASSP_VAL_NESO_INS(
11 p_person_id IN NUMBER ,
12 p_course_cd IN VARCHAR2 ,
13 p_unit_cd IN VARCHAR2 ,
14 p_version_number IN NUMBER ,
15 p_mark IN NUMBER ,
16 p_grade IN VARCHAR2 ,
17 p_grading_schema_cd IN VARCHAR2,
18 p_gs_version_number IN NUMBER,
19 p_s_grade_creation_method_type IN VARCHAR2 ,
20 p_message_name OUT NOCOPY VARCHAR2 )
21 RETURN boolean IS
22 gv_other_detail VARCHAR2(255);
23 BEGIN
24 DECLARE
25 v_closed_ind IGS_LOOKUPS_VIEW.closed_ind%type;
26 s_unit_status IGS_PS_UNIT_STAT.s_unit_status%type;
27 v_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE;
28 CURSOR c_sgcmt IS
29 SELECT sgcmt.closed_ind
30 FROM IGS_LOOKUPS_VIEW sgcmt
31 WHERE sgcmt.LOOKUP_TYPE = p_s_grade_creation_method_type;
32 CURSOR c_uv IS
33 SELECT us.s_unit_status
34 FROM IGS_PS_UNIT_VER uv,
35 IGS_PS_UNIT_STAT us
36 WHERE uv.unit_cd = p_unit_cd AND
37 uv.version_number = p_version_number AND
38 us.UNIT_STATUS = uv.UNIT_STATUS;
39 CURSOR c_sca IS
40 SELECT sca.person_id
41 FROM IGS_EN_STDNT_PS_ATT sca
42 WHERE sca.person_id = p_person_id;
43 CURSOR c_sca2 IS
44 SELECT sca.person_id
45 FROM IGS_EN_STDNT_PS_ATT sca
46 WHERE sca.person_id = p_person_id AND
47 sca.course_cd = p_course_cd;
48 BEGIN
49 -- Validate the insert of a IGS_AS_NON_ENR_STDOT record.
50 -- Checks for:
51 -- cannot be added against a closed s_grade_creation_method_type
52 -- one of mark or grade must be set
53 -- cannot be added against IGS_PS_UNIT_VER with a system status of
54 -- other than 'ACTIVE'.
55 -- Person id must have at least one student course attempt of any status.
56 --- Set the default message number
57 p_message_name := null;
58 IF (p_mark IS NULL) AND (p_grade IS NULL) THEN
59 p_message_name := 'IGS_AS_MARK_OR_GRD_MUSTBE_SET';
60 RETURN FALSE;
61 END IF;
62 OPEN c_sgcmt;
63 FETCH c_sgcmt INTO v_closed_ind;
64 IF (c_sgcmt%FOUND) THEN
65 IF (v_closed_ind = 'Y') THEN
66 p_message_name := 'IGS_AS_CANNOT_ADD_OUTCOME';
67 CLOSE c_sgcmt;
68 RETURN FALSE;
69 END IF;
70 END IF;
71 CLOSE c_sgcmt;
72 OPEN c_uv;
73 FETCH c_uv INTO s_unit_status;
74 IF (c_uv%NOTFOUND) THEN
75 CLOSE c_uv;
76 RETURN TRUE;
77 ELSE
78 IF (s_unit_status <> 'ACTIVE') THEN
79 p_message_name := 'IGS_AS_CANNOT_ADD_NONENR';
80 CLOSE c_uv;
81 RETURN FALSE;
82 ELSE
83 CLOSE c_uv;
84 END IF;
85 END IF;
86 -- 4. Check that Person has a course attempt
87 OPEN c_sca;
88 FETCH c_sca INTO v_person_id;
89 IF (c_sca%NOTFOUND) THEN
90 CLOSE c_sca;
91 p_message_name := 'IGS_AS_PRSN_ONE_PRG_ATTEMPT';
92 RETURN FALSE;
93 END IF;
94 CLOSE c_sca;
95 -- 5. If the course code has been specified, check that
96 -- the student actually has a course attempt for the course.
97 IF (p_course_cd IS NOT NULL) THEN
98 OPEN c_sca2;
99 FETCH c_sca2 INTO v_person_id;
100 IF (c_sca2%NOTFOUND) THEN
101 CLOSE c_sca2;
102 p_message_name := 'IGS_AS_STUD_PRG_ATTEMPT';
103 RETURN FALSE;
104 END IF;
105 CLOSE c_sca2;
106 END IF;
107 -- 6. validate that the marks and grade are as per grd sch
108 IF NOT (IGS_AS_VAL_SUAO.ASSP_VAL_MARK_GRADE(
109 p_mark => p_mark,
110 p_grade => p_grade,
111 p_grading_schema_cd => p_grading_schema_cd,
112 p_version_number => p_gs_version_number,
113 p_message_name => p_message_name)) THEN
114 RETURN FALSE;
115 END IF;
116 RETURN TRUE;
117 END;
118 EXCEPTION
119 WHEN OTHERS THEN
120 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
121 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_NESO.assp_val_neso_ins');
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124 END assp_val_neso_ins;
125 END IGS_AS_VAL_NESO;
[Home] [Help]