1 PACKAGE BODY IGS_PR_VAL_SOPC AS
2 /* $Header: IGSPR12B.pls 115.5 2002/11/29 02:46:45 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 27-AUG-2001 Bug No. 1956374 .The function PRGP_VAL_APPEAL_DA removed
7 --smadathi 27-AUG-2001 Bug No. 1956374 .The function PRGP_VAL_CAUSE_DA removed
8 --smadathi 26-AUG-2001 Bug No. 1956374 .The function PRGP_VAL_DA_CLOSED removed
9 -------------------------------------------------------------------------------------------
10 -- Validate the appeal indicator being set
11 FUNCTION prgp_val_sopc_apl(
12 p_org_unit_cd IN VARCHAR2 ,
13 p_ou_start_dt IN DATE ,
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_sopc_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_sopca IS
24 SELECT 'X'
25 FROM IGS_PR_S_OU_PRG_CAL
26 WHERE org_unit_cd = p_org_unit_cd
27 AND ou_start_dt = p_ou_start_dt
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_sopca;
34 FETCH c_sopca INTO v_dummy;
35 IF c_sopca%FOUND THEN
36 p_message_name := 'IGS_PR_APPEAL_NOT_AVAILABLE';
37 CLOSE c_sopca;
38 RETURN FALSE;
39 END IF;
40 CLOSE c_sopca;
41 END IF;
42 RETURN TRUE;
43 EXCEPTION
44 WHEN OTHERS THEN
45 IF c_sopca%ISOPEN THEN
46 CLOSE c_sopca;
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_SOPC.PRGP_VAL_SOPC_APL');
54 IGS_GE_MSG_STACK.ADD;
55 App_Exception.Raise_Exception;
56 END prgp_val_sopc_apl;
57 --
58 -- Validate the show cause indicator being set
59 FUNCTION prgp_val_sopc_cause(
60 p_org_unit_cd IN VARCHAR2 ,
61 p_ou_start_dt IN DATE ,
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_sopc_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_sopca IS
72 SELECT 'X'
73 FROM IGS_PR_S_OU_PRG_CAL
74 WHERE org_unit_cd = p_org_unit_cd
75 AND ou_start_dt = p_ou_start_dt
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_sopca;
82 FETCH c_sopca INTO v_dummy;
83 IF c_sopca%FOUND THEN
84 p_message_name := 'IGS_PR_SHOW_CAUSE_NOT_AVAILAB';
85 CLOSE c_sopca;
86 RETURN FALSE;
87 END IF;
88 CLOSE c_sopca;
89 END IF;
90 RETURN TRUE;
91 EXCEPTION
92 WHEN OTHERS THEN
93 IF c_sopca%ISOPEN THEN
94 CLOSE c_sopca;
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_SOPC.PRGP_VAL_SOPC_CAUSE');
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104 END prgp_val_sopc_cause;
105 --
106 -- Validate the {s_ou_conf,s_crv_conf}.appeal_ind
107 FUNCTION prgp_val_appeal_ind(
108 p_appeal_ind IN VARCHAR2 ,
109 p_message_name OUT NOCOPY VARCHAR2 )
110 RETURN BOOLEAN AS
111 gv_other_detail VARCHAR2(255);
112 BEGIN -- prgp_val_appeal_ind
113 -- Validate the {s_ou_conf,s_crv_conf}.appeal_ind, checking for:
114 -- * Cannot be set if parent s_prg_conf.appeal_ind is N
115 DECLARE
116 v_appeal_ind IGS_PR_S_PRG_CONF.appeal_ind%TYPE;
117 CURSOR c_sprgc IS
118 SELECT sprgc.appeal_ind
119 FROM IGS_PR_S_PRG_CONF sprgc
120 WHERE sprgc.s_control_num = 1;
121 BEGIN
122 -- Set the default message name
123 p_message_name := Null;
124 OPEN c_sprgc;
125 FETCH c_sprgc INTO v_appeal_ind;
126 IF c_sprgc%FOUND THEN
127 CLOSE c_sprgc;
128 IF v_appeal_ind = 'N' AND
129 p_appeal_ind = 'Y' THEN
130 p_message_name := 'IGS_PR_CANT_SET_APPEAL_IND';
131 RETURN FALSE;
132 END IF;
133 ELSE
134 CLOSE c_sprgc;
135 END IF;
136 RETURN TRUE;
137 EXCEPTION
138 WHEN OTHERS THEN
139 IF c_sprgc%ISOPEN THEN
140 CLOSE c_sprgc;
141 END IF;
142 RAISE;
143 END;
144 EXCEPTION
145 WHEN OTHERS THEN
146 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
147 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SOPC.PRGP_VAL_APPEAL_IND');
148 IGS_GE_MSG_STACK.ADD;
149 App_Exception.Raise_Exception;
150 END prgp_val_appeal_ind;
151 --
152 -- Validate the {s_ou_conf,s_crv_conf}.show_cause_ind.
153 FUNCTION prgp_val_cause_ind(
154 p_show_cause_ind IN VARCHAR2 ,
155 p_message_name OUT NOCOPY VARCHAR2 )
156 RETURN BOOLEAN AS
157 gv_other_detail VARCHAR2(255);
158 BEGIN -- prgp_val_cause_ind
159 -- Validate the {s_ou_conf,s_crv_conf}.show_cause_ind, checking for:
160 -- * Cannot be set if parent s_prg_conf.show_cause_ind is N
161 DECLARE
162 v_show_cause_ind IGS_PR_S_PRG_CONF.show_cause_ind%TYPE;
163 CURSOR c_sprgc IS
164 SELECT sprgc.show_cause_ind
165 FROM IGS_PR_S_PRG_CONF sprgc
166 WHERE sprgc.s_control_num = 1;
167 BEGIN
168 -- Set the default message name
169 p_message_name := Null;
170 OPEN c_sprgc;
171 FETCH c_sprgc INTO v_show_cause_ind;
172 IF c_sprgc%FOUND THEN
173 CLOSE c_sprgc;
174 IF v_show_cause_ind = 'N' AND
175 p_show_cause_ind = 'Y' THEN
176 p_message_name := 'IGS_PR_CANT_SET_SHOW_CAUS_IND';
177 RETURN FALSE;
178 END IF;
179 ELSE
180 CLOSE c_sprgc;
181 END IF;
182 RETURN TRUE;
183 EXCEPTION
184 WHEN OTHERS THEN
185 IF c_sprgc%ISOPEN THEN
186 CLOSE c_sprgc;
187 END IF;
188 RAISE;
189 END;
190 EXCEPTION
191 WHEN OTHERS THEN
192 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
193 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SOPC.PRGP_VAL_CAUSE_IND');
194 IGS_GE_MSG_STACK.ADD;
195 App_Exception.Raise_Exception;
196 END prgp_val_cause_ind;
197 --
198 --
199 -- Validate that the IGS_OR_UNIT is active.
200 FUNCTION prgp_val_ou_active(
201 p_org_unit_cd IN VARCHAR2 ,
202 p_start_dt IN DATE ,
203 p_message_name OUT NOCOPY VARCHAR2 )
204 RETURN BOOLEAN AS
205 gv_other_detail VARCHAR2(255);
206 BEGIN -- prgp_val_ou_active
207 -- Validate that the IGS_OR_UNIT is active.
208 DECLARE
209 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
210 v_s_org_status IGS_OR_STATUS.s_org_status%TYPE;
211 CURSOR c_os_ou IS
212 SELECT os.s_org_status
213 FROM IGS_OR_UNIT ou,
214 IGS_OR_STATUS os
215 WHERE ou.org_unit_cd = p_org_unit_cd AND
216 ou.start_dt = p_start_dt AND
217 os.org_status = ou.org_status;
218 BEGIN
219 -- Set the default message name
220 p_message_name := Null;
221 IF p_org_unit_cd IS NULL OR
222 p_start_dt IS NULL THEN
223 RETURN TRUE;
224 END IF;
225 OPEN c_os_ou;
226 FETCH c_os_ou INTO v_s_org_status;
227 IF c_os_ou%FOUND THEN
228 CLOSE c_os_ou;
229 If v_s_org_status <> cst_active THEN
230 p_message_name := 'IGS_PR_ORG_UNIT_MUST_BE_ACTIV';
231 RETURN FALSE;
232 END IF;
233 ELSE
234 CLOSE c_os_ou;
235 END IF;
236 RETURN TRUE;
237 EXCEPTION
238 WHEN OTHERS THEN
239 IF c_os_ou%ISOPEN THEN
240 CLOSE c_os_ou;
241 END IF;
242 RAISE;
243 END;
244 END prgp_val_ou_active;
245 END IGS_PR_VAL_SOPC;
[Home] [Help]