1 PACKAGE BODY IGS_AS_VAL_GSGT AS
2 /* $Header: IGSAS25B.pls 115.4 2002/11/28 22:45:45 nsidana ship $ */
3
4 --
5
6 -- Validate grade may not be translated against another grade in same ver
7 FUNCTION assp_val_gsgt_gs_gs(
8 p_grading_schema_cd IN VARCHAR2 ,
9 p_version_number IN NUMBER ,
10 p_to_grading_schema_cd IN VARCHAR2 ,
11 p_to_version_number IN NUMBER ,
12 p_message_name OUT NOCOPY varchar2 )
13 RETURN BOOLEAN IS
14 gv_other_detail VARCHAR2(255);
15 BEGIN -- assp_val_gsgt_gs_gs
16 -- This module validates that a grade may not be translated against another
17 -- grade within the same version of the grading schema. It also provides a
18 -- warning if it is translated against the same grading schema but a
19 -- different version number.
20 DECLARE
21 BEGIN
22 -- Set the default message number
23 p_message_name := null;
24 IF p_grading_schema_cd = p_to_grading_schema_cd THEN
25 IF p_version_number = p_to_version_number THEN
26 p_message_name := 'IGS_AS_GRD_NOT_TRANSLATED';
27 RETURN FALSE;
28 ELSE --version numbers are different
29 p_message_name := 'IGS_AS_TRANS_GRD_ANOTHER_GRD';
30 RETURN TRUE;
31 END IF;
32 END IF;
33 -- Return the default value
34 RETURN TRUE;
35 END;
36 EXCEPTION
37 WHEN OTHERS THEN
38 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
39 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GSGT.assp_val_gs_cur_fut');
40 IGS_GE_MSG_STACK.ADD;
41 App_Exception.Raise_Exception;
42 END assp_val_gsgt_gs_gs;
43 --
44 -- Validate grade may not be translated against more than 1 grade
45 FUNCTION assp_val_gsgt_multi(
46 p_grading_schema_cd IN VARCHAR2 ,
47 p_version_number IN NUMBER ,
48 p_grade IN VARCHAR2 ,
49 p_to_grading_schema_cd IN VARCHAR2 ,
50 p_to_version_number IN NUMBER ,
51 p_to_grade IN VARCHAR2 ,
52 p_message_name OUT NOCOPY varchar2 )
53 RETURN BOOLEAN IS
54 gv_other_detail VARCHAR2(255);
55 BEGIN -- assp_val_gsgt_multi
56 DECLARE
57 v_gsgt_exists VARCHAR2(1);
58 CURSOR c_gsgt IS
59 SELECT 'x'
60 FROM IGS_AS_GRD_SCH_TRN gsgt
61 WHERE gsgt.grading_schema_cd = p_grading_schema_cd AND
62 gsgt.version_number = p_version_number AND
63 gsgt.grade = p_grade AND
64 gsgt.to_grading_schema_cd = p_to_grading_schema_cd AND
65 gsgt.to_version_number = p_to_version_number AND
66 gsgt.to_grade <> p_to_grade;
67 BEGIN
68 -- Set the default message number
69 p_message_name := null;
70 -- Cursor handling
71 --ie; Do not validate against record being passed in
72 OPEN c_gsgt ;
73 FETCH c_gsgt INTO v_gsgt_exists;
74 IF c_gsgt %FOUND THEN
75 CLOSE c_gsgt;
76 p_message_name :='IGS_AS_GRD_MUSTBE_TRANSLATED';
77 RETURN FALSE;
78 END IF;
79 CLOSE c_gsgt ;
80 -- Return the default value
81 RETURN TRUE;
82 EXCEPTION
83 WHEN OTHERS THEN
84 IF c_gsgt %ISOPEN THEN
85 CLOSE c_gsgt;
86 END IF;
87 RAISE;
88 END;
89 EXCEPTION
90 WHEN OTHERS THEN
91 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
92 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GSGT.assp_val_gsgt_multi');
93 IGS_GE_MSG_STACK.ADD;
94 App_Exception.Raise_Exception;
95 END assp_val_gsgt_multi;
96 --
97 -- Validate rslt type for grade is same as rslt type for xlation grade
98 FUNCTION assp_val_gsgt_result(
99 p_grading_schema_cd IN VARCHAR2 ,
100 p_version_number IN NUMBER ,
101 p_grade IN VARCHAR2 ,
102 p_to_grading_schema_cd IN VARCHAR2 ,
103 p_to_version_number IN NUMBER ,
104 p_to_grade IN VARCHAR2 ,
105 p_message_name OUT NOCOPY varchar2 )
106 RETURN BOOLEAN IS
107 gv_other_detail VARCHAR2(255);
108 BEGIN -- assp_val_gsgt_result
109 DECLARE
110 v_gsg_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
111 v_gsg_s_result_type_to IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
112 BEGIN
113 -- Set the default message number
114 p_message_name := null;
115 --2. Main Body
116 v_gsg_s_result_type := IGS_AS_GEN_002.ASSP_GET_GSG_RESULT(
117 p_grading_schema_cd,
118 p_version_number,
119 p_grade);
120 v_gsg_s_result_type_to := IGS_AS_GEN_002.ASSP_GET_GSG_RESULT(
121 p_to_grading_schema_cd,
122 p_to_version_number,
123 p_to_grade);
124 IF v_gsg_s_result_type <> v_gsg_s_result_type_to THEN
125 p_message_name := 'IGS_AS_FROM_TO_GRADES_SYS';
126 RETURN FALSE;
127 END IF;
128 -- Return the default value
129 RETURN TRUE;
130 END;
131 EXCEPTION
132 WHEN OTHERS THEN
133 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
134 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GSGT.assp_val_gsgt_multi');
135 IGS_GE_MSG_STACK.ADD;
136 App_Exception.Raise_Exception;
137 END assp_val_gsgt_result;
138 --
139 -- Validate is SUAO exist when changing/deleting translations
140 FUNCTION assp_val_gsgt_suao(
141 p_grading_schema_cd IN VARCHAR2 ,
142 p_version_number IN NUMBER ,
143 p_grade IN VARCHAR2 ,
144 p_to_grading_schema_cd IN VARCHAR2 ,
145 p_to_version_number IN NUMBER ,
146 p_to_grade IN VARCHAR2 ,
147 p_message_name OUT NOCOPY varchar2 )
148 RETURN BOOLEAN IS
149 gv_other_detail VARCHAR2(255);
150 BEGIN -- assp_val_gsgt_suao
151 DECLARE
152 v_suao_exists VARCHAR2(1);
153 CURSOR c_suao IS
154 SELECT 'x'
155 FROM IGS_AS_SU_STMPTOUT suao
156 WHERE suao.grading_schema_cd = p_grading_schema_cd AND
157 suao.version_number = p_version_number AND
158 suao.grade = p_grade AND
159 suao.translated_grading_schema_cd = p_to_grading_schema_cd AND
160 suao.translated_version_number = p_to_version_number AND
161 suao.translated_grade = p_to_grade AND
162 suao.finalised_outcome_ind = 'Y';
163 BEGIN
164 -- Set the default message number
165 p_message_name := null;
166 --2. Main Body
167 OPEN c_suao;
168 FETCH c_suao INTO v_suao_exists;
169 IF c_suao%FOUND THEN
170 CLOSE c_suao;
171 p_message_name := 'IGS_AS_FINAL_SUA_OUTCOMES';
172 RETURN FALSE;
173 END IF;
174 CLOSE c_suao;
175 -- Return the default value
176 RETURN TRUE;
177 EXCEPTION
178 WHEN OTHERS THEN
179 IF c_suao%ISOPEN THEN
180 CLOSE c_suao;
181 END IF;
182 RAISE;
183 END;
184 EXCEPTION
185 WHEN OTHERS THEN
186 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
187 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GSGT.assp_val_gsgt_suao');
188 IGS_GE_MSG_STACK.ADD;
189 App_Exception.Raise_Exception;
190 END assp_val_gsgt_suao;
191 --
192 -- Routine to clear rowids saved in a PL/SQL TABLE from a prior commit.
193 END IGS_AS_VAL_GSGT;
[Home] [Help]