1 PACKAGE BODY IGS_PR_VAL_PRA AS
2 /* $Header: IGSPR04B.pls 115.5 2002/11/29 02:44:31 nsidana ship $ */
3 /*
4 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
5 || Removed program unit (PRGP_VAL_OU_ACTIVE) - from the spec and body. -- kdande
6 */
7 -------------------------------------------------------------------------------------------
8 --Change History:
9 --Who When What
10 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed procedure "crsp_val_cty_closed"
11 -------------------------------------------------------------------------------------------
12 --
13 -- bug id : 1956374
14 -- sjadhav , 28-aug-2001
15 -- removed FUNCTION enrp_val_att_closed
16 --
17 --
18 -- Validate the IGS_PR_RU_APPL record.
19 FUNCTION prgp_val_pra_rqrd(
20 p_s_relation_type IN VARCHAR2 ,
21 p_progression_rule_cd IN VARCHAR2 ,
22 p_rul_sequence_number IN NUMBER ,
23 p_ou_org_unit_cd IN VARCHAR2 ,
24 p_ou_start_dt IN DATE ,
25 p_course_type IN VARCHAR2 ,
26 p_crv_course_cd IN VARCHAR2 ,
27 p_crv_version_number IN NUMBER ,
28 p_sca_person_id IN NUMBER ,
29 p_sca_course_cd IN VARCHAR2 ,
30 p_pro_progression_rule_cat IN VARCHAR2 ,
31 p_pro_pra_sequence_number IN NUMBER ,
32 p_pro_sequence_number IN NUMBER ,
33 p_spo_person_id IN NUMBER ,
34 p_spo_course_cd IN VARCHAR2 ,
35 p_spo_sequence_number IN NUMBER ,
36 p_message_name OUT NOCOPY VARCHAR2 )
37 RETURN BOOLEAN AS
38 gv_other_detail VARCHAR2(255);
39 BEGIN -- prg_val_pra_rqrd
40 -- Check that the IGS_PR_RU_APPL record does not have both
41 -- progression_rule_cd and rul_sequence_number set and has the required
42 -- information entered for the s_relation_type.
43 DECLARE
44 BEGIN
45 IF p_s_relation_type IS NULL THEN
46 p_message_name := null;
47 RETURN TRUE;
48 END IF;
49 -- Check both progression_rule_cd and rul_sequence_number are not set
50 IF p_progression_rule_cd IS NOT NULL AND
51 p_rul_sequence_number IS NOT NULL THEN
52 p_message_name := 'IGS_PR_BTH_RUL_CD_SEQ_NO_NA';
53 RETURN FALSE;
54 END IF;
55 -- Check one of either progression_rule_cd and rul_sequence_number are set
56 IF p_progression_rule_cd IS NULL AND
57 p_rul_sequence_number IS NULL AND
58 p_s_relation_type <> 'PRGC' AND
59 p_s_relation_type <> 'PRR' THEN
60 p_message_name := 'IGS_PR_ENTER_PRG_RULE_SEQ_NO.';
61 RETURN FALSE;
62 END IF;
63 -- Check that the record has valid values for the s_relation_type specified.
64 IF p_s_relation_type = 'PRGC' THEN
65 IF p_progression_rule_cd IS NOT NULL OR
66 p_rul_sequence_number IS NOT NULL THEN
67 p_message_name := 'IGS_PR_CHK_WHEN_LNK_TO_PRG_RU';
68 RETURN FALSE;
69 END IF;
70 ELSIF p_s_relation_type = 'PRR' THEN
71 IF p_progression_rule_cd IS NULL OR
72 p_rul_sequence_number IS NOT NULL THEN
73 p_message_name := 'IGS_PR_RUL_MUST_BE_ENTERED';
74 RETURN FALSE;
75 END IF;
76 ELSIF p_s_relation_type = 'CTY' THEN
77 IF p_course_type IS NULL THEN
78 p_message_name := 'IGS_GE_MANDATORY_FLD';
79 RETURN FALSE;
80 END IF;
81 ELSIF p_s_relation_type = 'OU' THEN
82 IF p_ou_org_unit_cd IS NULL OR
83 p_ou_start_dt IS NULL THEN
84 p_message_name := 'IGS_PR_ORG_UNIT_ST_DT_NOT_NUL';
85 RETURN FALSE;
86 END IF;
87 ELSIF p_s_relation_type = 'CRV' THEN
88 IF p_crv_course_cd IS NULL OR
89 p_crv_version_number IS NULL THEN
90 p_message_name := 'IGS_GE_MANDATORY_FLD';
91 RETURN FALSE;
92 END IF;
93 ELSIF p_s_relation_type = 'SCA' THEN
94 IF p_sca_person_id IS NULL OR
95 p_sca_course_cd IS NULL THEN
96 p_message_name := 'IGS_PR_PERSID_COUR_CD_NOT_NUL';
97 RETURN FALSE;
98 END IF;
99 ELSIF p_s_relation_type = 'PRO' THEN
100 IF p_pro_progression_rule_cat IS NULL OR
101 p_pro_pra_sequence_number IS NULL OR
102 p_pro_sequence_number IS NULL THEN
103 p_message_name := 'IGS_PR_CHK_PRA_PRC_SEQ_NUMBER';
104 RETURN FALSE;
105 END IF;
106 ELSIF p_s_relation_type = 'SPO' THEN
107 IF p_spo_person_id IS NULL OR
108 p_spo_course_cd IS NULL OR
109 p_spo_sequence_number IS NULL THEN
110 p_message_name := 'IGS_PR_PERID_CC_SEQNO_NOT_NUL';
111 RETURN FALSE;
112 END IF;
113 END IF;
114 p_message_name := null;
115 RETURN TRUE;
116 END;
117 EXCEPTION
118 WHEN OTHERS THEN
119 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
120 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_PRA.PRGP_VAL_PRA_RQRD');
121 --IGS_GE_MSG_STACK.ADD;
122
123 END prgp_val_pra_rqrd;
124 --
125 -- Validate that the IGS_PR_RU_CAT is not closed.
126 FUNCTION prgp_val_prgc_closed(
127 p_progression_rule_cat IN VARCHAR2 ,
128 p_message_name OUT NOCOPY VARCHAR2 )
129 RETURN BOOLEAN AS
130 gv_other_detail VARCHAR2(255);
131 BEGIN -- prgp_val_prgc_closed
132 -- Validate the IGS_PR_RU_CAT is not closed
133 DECLARE
134 v_dummy VARCHAR2(1);
135 CURSOR c_prgc IS
136 SELECT 'X'
137 FROM IGS_PR_RU_CAT prgc
138 WHERE prgc.progression_rule_cat = p_progression_rule_cat AND
139 prgc.closed_ind = 'N';
140 BEGIN
141 IF p_progression_rule_cat IS NULL THEN
142 p_message_name := null;
143 RETURN TRUE;
144 END IF;
145 OPEN c_prgc;
146 FETCH c_prgc INTO v_dummy;
147 IF c_prgc%NOTFOUND THEN
148 CLOSE c_prgc;
149 p_message_name := 'IGS_PR_RULE_CAT_CLOSED';
150 RETURN FALSE;
151 END IF;
152 CLOSE c_prgc;
153 p_message_name := null;
154 RETURN TRUE;
155 EXCEPTION
156 WHEN OTHERS THEN
157 IF (c_prgc%ISOPEN) THEN
158 CLOSE c_prgc;
159 END IF;
160 RAISE;
161 END;
162 EXCEPTION
163 WHEN OTHERS THEN
164 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
165 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_PRA.PRGP_VAL_PRGC_CLOSED');
166 --IGS_GE_MSG_STACK.ADD;
167
168 END prgp_val_prgc_closed;
169 --
170 -- Validate that the IGS_PR_RULE is not closed.
171 FUNCTION prgp_val_prr_closed(
172 p_progression_rule_cd IN VARCHAR2 ,
173 p_message_name OUT NOCOPY VARCHAR2 )
174 RETURN BOOLEAN AS
175 gv_other_detail VARCHAR2(255);
176 BEGIN -- prgp_val_prr_closed
177 -- Validate the IGS_PR_RULE is not closed
178 DECLARE
179 v_dummy VARCHAR2(1);
180 CURSOR c_prr IS
181 SELECT 'X'
182 FROM IGS_PR_RULE prr
183 WHERE prr.progression_rule_cd = p_progression_rule_cd AND
184 prr.closed_ind = 'N';
185 BEGIN
186 IF p_progression_rule_cd IS NULL THEN
187 p_message_name := null;
188 RETURN TRUE;
189 END IF;
190 OPEN c_prr;
191 FETCH c_prr INTO v_dummy;
192 IF c_prr%NOTFOUND THEN
193 CLOSE c_prr;
194 p_message_name := 'IGS_PR_RULE_CLOSED';
195 RETURN FALSE;
196 END IF;
197 CLOSE c_prr;
198 p_message_name := null;
199 RETURN TRUE;
200 EXCEPTION
201 WHEN OTHERS THEN
202 IF (c_prr%ISOPEN) THEN
203 CLOSE c_prr;
204 END IF;
205 RAISE;
206 END;
207 EXCEPTION
208 WHEN OTHERS THEN
209 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
210 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_PRA.PRGP_VAL_PRR_CLOSED');
211 --IGS_GE_MSG_STACK.ADD;
212
213 END prgp_val_prr_closed;
214 --
215 -- Validate the IGS_PS_COURSE version is active.
216 FUNCTION crsp_val_crv_active(
217 p_course_cd IN IGS_PS_VER_ALL.course_cd%TYPE ,
218 p_version_number IN IGS_PS_VER_ALL.version_number%TYPE ,
219 p_message_name OUT NOCOPY VARCHAR2)
220 RETURN BOOLEAN AS
221 gv_other_detail VARCHAR2(255);
222 BEGIN -- crsp_val_crv_active
223 -- Validate the IGS_PS_VER is ACTIVE
224 DECLARE
225 cst_active CONSTANT IGS_PS_STAT.s_course_status%TYPE := 'ACTIVE';
226 v_dummy VARCHAR2(1);
227 CURSOR c_crv_crst IS
228 SELECT 'X'
229 FROM IGS_PS_VER cst,
230 IGS_PS_STAT crst
231 WHERE cst.course_cd = p_course_cd AND
232 cst.version_number = p_version_number AND
233 cst.course_status = crst.course_status AND
234 crst.s_course_status = cst_active;
235 BEGIN
236 IF p_course_cd IS NOT NULL AND
237 p_version_number IS NOT NULL THEN
238 OPEN c_crv_crst;
239 FETCH c_crv_crst INTO v_dummy;
240 IF (c_crv_crst%NOTFOUND) THEN
241 CLOSE c_crv_crst;
242 p_message_name := 'IGS_PS_CHG_CANNOT_BEMADE_PRG';
243 RETURN FALSE;
244 END IF;
245 CLOSE c_crv_crst;
246 END IF;
247 p_message_name := null;
248 RETURN TRUE;
249 EXCEPTION
250 WHEN OTHERS THEN
251 IF (c_crv_crst%ISOPEN) THEN
252 CLOSE c_crv_crst;
253 END IF;
254 RAISE;
255 END;
256 EXCEPTION
257 WHEN OTHERS THEN
258 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
259 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_PRA.CRSP_VAL_CRV_ACTIVE');
260 --IGS_GE_MSG_STACK.ADD;
261
262 END crsp_val_crv_active;
263 END IGS_PR_VAL_PRA;
[Home] [Help]