1 PACKAGE BODY IGS_PR_VAL_SCPC AS
2 /* $Header: IGSPR10B.pls 115.5 2002/11/29 02:46:08 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 27-AUG-2001 Bug No. 1956374 .The function prgp_val_appeal_ind removed
7 --smadathi 26-AUG-2001 Bug No. 1956374 .The function prgp_val_cause_ind removed
8 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed procedure "crsp_val_crv_active"
9 -------------------------------------------------------------------------------------------
10 -- Validate the appeal indicator being set
11 FUNCTION prgp_val_scpc_apl(
12 p_course_cd IN VARCHAR2 ,
13 p_version_number IN NUMBER ,
14 p_appeal_ind IN VARCHAR2 ,
15 p_message_name OUT NOCOPY VARCHAR2 )
16 RETURN BOOLEAN AS
17 gv_other_detail VARCHAR2(255);
18 BEGIN -- prgp_val_scpc_apl
19 -- Validate if appeal indicator is set to 'N' that no related
20 -- progression calendars have appeal lengths set.
21 DECLARE
22 v_dummy VARCHAR2(1);
23 CURSOR c_scpca IS
24 SELECT 'X'
25 FROM IGS_PR_S_CRV_PRG_CAL
26 WHERE course_cd = p_course_cd
27 AND version_number = p_version_number
28 AND appeal_length IS NOT NULL;
29 BEGIN
30 -- Set the default message name
31 p_message_name := Null;
32 IF p_appeal_ind = 'N' THEN
33 OPEN c_scpca;
34 FETCH c_scpca INTO v_dummy;
35 IF c_scpca%FOUND THEN
36 p_message_name := 'IGS_PR_APPEAL_NOT_AVAILABLE';
37 CLOSE c_scpca;
38 RETURN FALSE;
39 END IF;
40 CLOSE c_scpca;
41 END IF;
42 RETURN TRUE;
43 EXCEPTION
44 WHEN OTHERS THEN
45 IF c_scpca%ISOPEN THEN
46 CLOSE c_scpca;
47 END IF;
48 RAISE;
49 END;
50 EXCEPTION
51 WHEN OTHERS THEN
52 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
53 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SCPC.PRGP_VAL_SCPC_APL');
54 IGS_GE_MSG_STACK.ADD;
55 App_Exception.Raise_Exception;
56 END prgp_val_scpc_apl;
57 --
58 -- Validate the show cause indicator being set
59 FUNCTION prgp_val_scpc_cause(
60 p_course_cd IN VARCHAR2 ,
61 p_version_number IN NUMBER ,
62 p_show_cause_ind IN VARCHAR2 ,
63 p_message_name OUT NOCOPY VARCHAR2 )
64 RETURN BOOLEAN AS
65 gv_other_detail VARCHAR2(255);
66 BEGIN -- prgp_val_scpc_cause
67 -- Validate if show cause indicator is set to 'N' that no related
68 -- progression calendars have appeal lengths set.
69 DECLARE
70 v_dummy VARCHAR2(1);
71 CURSOR c_scpca IS
72 SELECT 'X'
73 FROM IGS_PR_S_CRV_PRG_CAL
74 WHERE course_cd = p_course_cd
75 AND version_number = p_version_number
76 AND show_cause_length IS NOT NULL;
77 BEGIN
78 -- Set the default message name
79 p_message_name := Null;
80 IF p_show_cause_ind = 'N' THEN
81 OPEN c_scpca;
82 FETCH c_scpca INTO v_dummy;
83 IF c_scpca%FOUND THEN
84 p_message_name := 'IGS_PR_SHOW_CAUSE_NOT_AVAILAB';
85 CLOSE c_scpca;
86 RETURN FALSE;
87 END IF;
88 CLOSE c_scpca;
89 END IF;
90 RETURN TRUE;
91 EXCEPTION
92 WHEN OTHERS THEN
93 IF c_scpca%ISOPEN THEN
94 CLOSE c_scpca;
95 END IF;
96 RAISE;
97 END;
98 EXCEPTION
99 WHEN OTHERS THEN
100 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
101 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SCPC.PRGP_VAL_SCPC_CAUSE');
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104 END prgp_val_scpc_cause;
105 --
106 -- Validate the appeal period length of the s_prg_cal record.
107 FUNCTION prgp_val_appeal_da(
108 p_appeal_ind IN VARCHAR2 ,
109 p_appeal_cutoff_dt_alias IN VARCHAR2 ,
110 p_message_name OUT NOCOPY VARCHAR2 )
111 RETURN BOOLEAN AS
112 gv_other_detail VARCHAR2(255);
113 BEGIN -- prgp_val_appeal_da
114 -- Validate the appeal indicator / date alias against one another ;
115 -- designed as a record level validation
116 DECLARE
117 BEGIN
118 -- Set the default message name
119 p_message_name := Null;
120 IF p_appeal_ind = 'Y' AND
121 p_appeal_cutoff_dt_alias IS NULL THEN
122 p_message_name := 'IGS_PR_SET_DA_IF_APPEAL';
123 RETURN FALSE;
124 END IF;
125 IF p_appeal_ind = 'N' AND
126 p_appeal_cutoff_dt_alias IS NOT NULL THEN
127 p_message_name := 'IGS_PR_CANT_SET_DA_IF_APP_NA';
128 RETURN FALSE;
129 END IF;
130 RETURN TRUE;
131 END;
132 EXCEPTION
133 WHEN OTHERS THEN
134 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
135 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SCPC.PRGP_VAL_APPEAL_DA');
136 IGS_GE_MSG_STACK.ADD;
137 App_Exception.Raise_Exception;
138 END prgp_val_appeal_da;
139 --
140 -- Validate the show cause period length of the s_prg_cal record.
141 FUNCTION prgp_val_cause_da(
142 p_show_cause_ind IN VARCHAR2 ,
143 p_show_cause_cutoff_dt_alias IN VARCHAR2 ,
144 p_message_name OUT NOCOPY VARCHAR2 )
145 RETURN BOOLEAN AS
146 gv_other_detail VARCHAR2(255);
147 BEGIN -- prgp_val_cause_da
148 -- Validate the show cause indicator / date alias against one another ;
149 -- designed as a record level validation.
150 DECLARE
151 BEGIN
152 -- Set the default message name
153 p_message_name := Null;
154 IF p_show_cause_ind = 'Y' AND
155 p_show_cause_cutoff_dt_alias IS NULL THEN
156 p_message_name := 'IGS_PR_DA_MUST_BE_SET';
157 RETURN FALSE;
158 END IF;
159 IF p_show_cause_ind = 'N' AND
160 p_show_cause_cutoff_dt_alias IS NOT NULL THEN
161 p_message_name := 'IGS_PR_DA_CANT_BE_SET';
162 RETURN FALSE;
163 END IF;
164 RETURN TRUE ;
165 END;
166 EXCEPTION
167 WHEN OTHERS THEN
168 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
169 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SCPC.PRGP_VAL_CAUSE_DA');
170 IGS_GE_MSG_STACK.ADD;
171 App_Exception.Raise_Exception;
172 END prgp_val_cause_da;
173 --
174 -- Validate that IGS_CA_DA.IGS_CA_DA is not closed.
175 FUNCTION prgp_val_da_closed(
176 p_dt_alias IN VARCHAR2 ,
177 p_message_name OUT NOCOPY VARCHAR2 )
178 RETURN BOOLEAN AS
179 gv_other_detail VARCHAR2(255);
180 BEGIN -- prgp_val_da_closed
181 -- Validate if IGS_CA_DA.IGS_CA_DA is closed.
182 DECLARE
183 v_closed_ind VARCHAR2(1);
184 CURSOR c_da IS
185 SELECT da.closed_ind
186 FROM IGS_CA_DA da
187 WHERE da.dt_alias = p_dt_alias;
188 BEGIN
189 -- Set the default message name
190 p_message_name := Null;
191 IF p_dt_alias IS NULL THEN
192 RETURN TRUE;
193 END IF;
194 OPEN c_da;
195 FETCH c_da INTO v_closed_ind;
196 IF c_da%FOUND THEN
197 CLOSE c_da;
198 IF v_closed_ind = 'Y' THEN
199 p_message_name := 'IGS_CA_DTALIAS_IS_CLOSED';
200 RETURN FALSE;
201 END IF;
202 ELSE
203 CLOSE c_da;
204 END IF;
205 RETURN TRUE;
206 EXCEPTION
207 WHEN OTHERS THEN
208 IF c_da%ISOPEN THEN
209 CLOSE c_da;
210 END IF;
211 END;
212 EXCEPTION
213 WHEN OTHERS THEN
214 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
215 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SCPC.PRGP_VAL_DA_CLOSED');
216 IGS_GE_MSG_STACK.ADD;
217 App_Exception.Raise_Exception;
218 END prgp_val_da_closed;
219 END IGS_PR_VAL_SCPC;