1 PACKAGE BODY IGS_CO_VAL_CCI AS
2 /* $Header: IGSCO06B.pls 115.5 2002/11/28 23:04:09 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed procedure "corp_val_cort_closed"
7 -------------------------------------------------------------------------------------------
8
9 -- Validate that the correspondence type is eligible for the category
10 FUNCTION corp_val_cci_elgbl(
11 p_person_id IN NUMBER ,
12 p_course_cd IN VARCHAR2 ,
13 p_correspondence_cat IN VARCHAR2 ,
14 p_correspondence_type IN VARCHAR2 ,
15 p_job_name IN VARCHAR2 ,
16 p_output_num IN NUMBER ,
17 p_effective_dt IN DATE ,
18 p_message_name OUT NOCOPY varchar2 )
19 RETURN BOOLEAN AS
20
21 BEGIN
22 DECLARE
23 e_required_parameters EXCEPTION;
24 e_both_must_be_set EXCEPTION;
25 v_correspondence_cat IGS_EN_STDNT_PS_ATT.correspondence_cat%TYPE;
26 v_correspondence_type IGS_CO_TYPE_JO.correspondence_type%TYPE;
27 CURSOR c_sca IS
28 SELECT sca.correspondence_cat
29 FROM IGS_EN_STDNT_PS_ATT sca
30 WHERE sca.person_id = p_person_id AND
31 sca.course_cd = p_course_cd AND
32 sca.correspondence_cat = p_correspondence_cat;
33 CURSOR c_cortjo IS
34 SELECT cortjo.correspondence_type
35 FROM IGS_CO_TYPE_JO cortjo
36 WHERE cortjo.s_job_name = p_job_name AND
37 cortjo.output_num = p_output_num;
38 CURSOR c_sca2 IS
39 SELECT sca.correspondence_cat
40 FROM IGS_EN_STDNT_PS_ATT sca
41 WHERE sca.person_id = p_person_id AND
42 sca.course_cd = p_course_cd;
43 FUNCTION corpl_val_cci (
44 p_course_cd IN IGS_PS_COURSE.course_cd%TYPE,
45 p_correspondence_cat IN IGS_CO_CAT.correspondence_cat%TYPE,
46 p_correspondence_type IN IGS_CO_TYPE.correspondence_type%TYPE,
47 p_message_name OUT NOCOPY varchar2)
48 RETURN
49 BOOLEAN
50 AS
51 BEGIN
52 DECLARE
53 v_block_when_encumbered_ind
54 IGS_CO_CAT_ITM.block_when_encumbered_ind%TYPE;
55 CURSOR c_cci IS
56 SELECT cci.block_when_encumbered_ind
57 FROM IGS_CO_CAT_ITM cci
58 WHERE cci.correspondence_cat = p_correspondence_cat AND
59 cci.correspondence_type = p_correspondence_type AND
60 cci.logical_delete_dt IS NULL;
61 CURSOR c_cortjo IS
62 SELECT cortjo.correspondence_type
63 FROM IGS_CO_TYPE_JO cortjo
64 WHERE cortjo.s_job_name = p_job_name AND
65 cortjo.output_num = p_output_num AND
66 cortjo.correspondence_type IN (
67 SELECT cci.correspondence_type
68 FROM IGS_CO_CAT_ITM cci
69 WHERE cci.correspondence_type =
70 cortjo.correspondence_type AND
71 cci.logical_delete_dt IS NULL);
72 BEGIN
73 -- Check the item is valid for the corresponding category, and if so
74 -- that the person is not blocked by an encumbrance.
75 OPEN c_cci;
76 FETCH c_cci INTO v_block_when_encumbered_ind;
77 IF (c_cci%NOTFOUND) THEN
78 CLOSE c_cci;
79 IF (p_correspondence_type IS NOT NULL) THEN
80 p_message_name := 'IGS_CO_CORTYPE_NOTALLOCATED';
81 RETURN FALSE;
82 END IF;
83 END IF;
84 CLOSE c_cci;
85 IF (v_block_when_encumbered_ind = 'Y') THEN
86 IF (IGS_EN_VAL_ENCMB.enrp_val_blk_sys_cor(
87 p_person_id,
88 p_course_cd,
89 p_effective_dt,
90 p_message_name) = FALSE) THEN
91 RETURN FALSE;
92 END IF;
93 END IF;
94 p_message_name := Null;
95 RETURN TRUE;
96 END;
97 EXCEPTION
98 WHEN OTHERS THEN
99 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
100 Fnd_Message.Set_Token('NAME','IGS_CO_VAL_CCI.CORPL_VAL_CCI');
101 IGS_GE_MSG_STACK.ADD;
102 App_Exception.Raise_Exception;
103 END corpl_val_cci;
104 BEGIN
105 -- This module checks is a person is eligible for an item of correspondence.
106 -- 1. Check required parameters have been passed
107 IF (p_correspondence_cat IS NULL AND
108 p_correspondence_type IS NULL AND
109 p_job_name IS NULL AND
110 p_output_num IS NULL) THEN
111 RAISE e_required_parameters;
112 END IF;
113 -- To determine the correspondence type, it is either specified or
114 -- determined via the job producing it.
115 IF (p_correspondence_type IS NULL AND
116 (p_job_name IS NULL OR
117 p_output_num IS NULL)) THEN
118 RAISE e_both_must_be_set;
119 END IF;
120 -- 2. Depending upon the actual parameters passed, the correspondence
121 -- category and correspondence type can be derived in a number of
122 -- different ways.
123 IF (p_correspondence_cat IS NOT NULL) THEN
124 OPEN c_sca;
125 FETCH c_sca INTO v_correspondence_cat;
126 IF (c_sca%NOTFOUND) THEN
127 CLOSE c_sca;
128 p_message_name := 'IGS_CO_PRSN_NOTENR_CORCAT';
129 RETURN FALSE;
130 END IF;
131 CLOSE c_sca;
132 IF (p_correspondence_type IS NOT NULL) THEN
133 IF (corpl_val_cci(
134 p_course_cd,
135 p_correspondence_cat,
136 p_correspondence_type,
137 p_message_name) = FALSE) THEN
138 RETURN FALSE;
139 END IF;
140 ELSE
141 OPEN c_cortjo;
142 FETCH c_cortjo INTO v_correspondence_type;
143 IF (c_cortjo%NOTFOUND) THEN
144 CLOSE c_cortjo;
145 p_message_name := 'IGS_CO_SYSJOB_RCGN_CORTYPE';
146 RETURN FALSE;
147 END IF;
148 CLOSE c_cortjo;
149 FOR v_cortjo_rec IN c_cortjo LOOP
150 IF (corpl_val_cci(
151 p_course_cd,
152 p_correspondence_cat,
153 v_cortjo_rec.correspondence_type,
154 p_message_name) = FALSE) THEN
155 RETURN FALSE;
156 END IF;
157 END LOOP;
158 END IF;
159 ELSE
160 -- p_correspondence_cat IS NULL
161 OPEN c_sca2;
162 FETCH c_sca2 INTO v_correspondence_cat;
163 IF (c_sca2%NOTFOUND) THEN
164 CLOSE c_sca2;
165 p_message_name := 'IGS_CO_PRSN_NOTENR_CORCAT';
166 RETURN FALSE;
167 END IF;
168 CLOSE c_sca2;
169 IF (p_correspondence_type IS NOT NULL) THEN
170 IF (corpl_val_cci(
171 p_course_cd,
172 v_correspondence_cat,
173 p_correspondence_type,
174 p_message_name) = FALSE) THEN
175 RETURN FALSE;
176 END IF;
177 ELSE
178 -- Get the correspondence type when it is not specified.
179 OPEN c_cortjo;
180 FETCH c_cortjo INTO v_correspondence_type;
181 IF (c_cortjo%NOTFOUND) THEN
182 CLOSE c_cortjo;
183 p_message_name := 'IGS_CO_SYSJOB_RCGN_CORTYPE';
184 RETURN FALSE;
185 END IF;
186 CLOSE c_cortjo;
187 FOR v_cortjo_rec IN c_cortjo LOOP
188 IF (corpl_val_cci(
189 p_course_cd,
190 v_correspondence_cat,
191 v_cortjo_rec.correspondence_type,
192 p_message_name) = FALSE) THEN
193 RETURN FALSE;
194 END IF;
195 END LOOP;
196 END IF;
197 END IF;
198 p_message_name := Null;
199 RETURN TRUE;
200 EXCEPTION
201 WHEN e_required_parameters THEN
202 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
203 IGS_GE_MSG_STACK.ADD;
204 App_Exception.Raise_Exception;
205 WHEN e_both_must_be_set THEN
206 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
207 IGS_GE_MSG_STACK.ADD;
208 App_Exception.Raise_Exception;
209 WHEN OTHERS THEN
210 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
211 Fnd_Message.Set_Token('NAME','IGS_CO_VAL_CCI.CORPL_VAL_CCI_ELGBL');
212 IGS_GE_MSG_STACK.ADD;
213 App_Exception.Raise_Exception;
214 END;
215 END corp_val_cci_elgbl;
216 --
217 -- Validate for correspondence category item duplicates.
218 FUNCTION corp_val_cci_duplict(
219 p_correspondence_cat IN VARCHAR2 ,
220 p_correspondence_type IN VARCHAR2 ,
221 p_create_dt IN DATE ,
222 p_message_name OUT NOCOPY varchar2 )
223 RETURN BOOLEAN AS
224
225 BEGIN
226 DECLARE
227 v_closed_ind IGS_CO_TYPE.closed_ind%TYPE;
228 CURSOR c_cci(
229 cp_correspondence_cat IGS_CO_CAT_ITM.correspondence_cat%TYPE,
230 cp_correspondence_type IGS_CO_CAT_ITM.correspondence_type%TYPE,
231 cp_create_dt IGS_CO_CAT_ITM.create_dt%TYPE) IS
232 SELECT cci.correspondence_cat
233 FROM IGS_CO_CAT_ITM cci
234 WHERE cci.correspondence_cat = cp_correspondence_cat AND
235 cci.correspondence_type = cp_correspondence_type AND
236 cci.create_dt <> cp_create_dt AND
237 cci.logical_delete_dt IS NULL;
238 BEGIN
239 -- Validate the correspondence category item table to ensure that a
240 -- correspondence type may only be duplicated where the previous
241 -- entry has been logically deleted.
242 -- That is, the same correspondence type may be added multiple times
243 -- for a correspondence category but only one may have the deletion
244 -- date set to null.
245 p_message_name := Null;
246 FOR v_cci_rec IN c_cci(
247 p_correspondence_cat,
248 p_correspondence_type,
249 p_create_dt) LOOP
250 p_message_name := 'IGS_CO_DUPL_CORCAT_ITEM_EXIST';
251 RETURN FALSE;
252 END LOOP;
253 RETURN TRUE;
254
255 END;
256 END corp_val_cci_duplict;
257 END IGS_CO_VAL_CCI;
[Home] [Help]