1 PACKAGE BODY IGS_EN_VAL_DR AS
2 /* $Header: IGSEN33B.pls 120.1 2006/02/15 23:08:12 ctyagi noship $ */
3 --
4 -- Validate system default indicator anddiscont reason type.
5 FUNCTION enrp_val_dr_sysdflt(
6 p_s_discontin_reason_type IN VARCHAR2 ,
7 p_sys_dflt_ind IN VARCHAR2 DEFAULT 'N',
8 p_message_name OUT NOCOPY VARCHAR2)
9 RETURN BOOLEAN AS
10
11 BEGIN -- enrp_val_dr_sysdflt
12 -- This module validates that the IGS_EN_DCNT_REASONCD.sys_dflt_ind can
13 -- only be set to
14 -- 'Y' when IGS_EN_DCNT_REASONCD.s_discontinuation_reason_type is not set.
15 DECLARE
16 BEGIN
17 -- Set the default message number
18 p_message_name := null;
19 IF p_sys_dflt_ind = 'Y' THEN
20 IF p_s_discontin_reason_type IS NULL THEN
21 p_message_name := 'IGS_EN_SYSDFLT_IND_SET';
22 RETURN FALSE;
23 END IF;
24 END IF;
25 RETURN TRUE;
26 END;
27 EXCEPTION
28 WHEN OTHERS THEN
29 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
30 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_DR.enrp_val_dr_sysdflt');
31 IGS_GE_MSG_STACK.ADD;
32 App_Exception.Raise_Exception;
33
34 END enrp_val_dr_sysdflt;
35 --
36 -- Validate sys discontinuation reason code closed indicator
37 FUNCTION enrp_val_sdrt_closed(
38 p_s_discontin_reason_type IN VARCHAR2 ,
39 p_message_name OUT NOCOPY VARCHAR2)
40 RETURN BOOLEAN AS
41
42 BEGIN -- enrp_val_sdrt_closed
43 -- This module validates that s_discontinuation_reason_type is not closed.
44 DECLARE
45 v_closed_ind IGS_LOOKUPS_VIEW.closed_ind%TYPE;
46 CURSOR c_sdrt IS
47 SELECT closed_ind
48 FROM IGS_LOOKUPS_VIEW sdrt
49 WHERE lookup_type = 'DISCONTINUATION_REASON_TYPE' and
50 lookup_code = p_s_discontin_reason_type;
51 BEGIN
52 p_message_name := null;
53 OPEN c_sdrt;
54 FETCH c_sdrt INTO v_closed_ind;
55 IF (c_sdrt%FOUND) THEN
56 IF (v_closed_ind = 'Y') THEN
57 CLOSE c_sdrt;
58 p_message_name := 'IGS_EN_SYS_DISCONT_RESTYPE';
59 RETURN FALSE;
60 END IF;
61 END IF;
62 CLOSE c_sdrt;
63 RETURN TRUE;
64 EXCEPTION
65 WHEN OTHERS THEN
66 IF (c_sdrt%ISOPEN) THEN
67 CLOSE c_sdrt;
68 END IF;
69 RAISE;
70 END;
71 EXCEPTION
72 WHEN OTHERS THEN
73 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
74 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_DR.enrp_val_sdrt_closed');
75 IGS_GE_MSG_STACK.ADD;
76 App_Exception.Raise_Exception;
77
78 END enrp_val_sdrt_closed;
79 --
80 -- Validate system default indicator (at least one).
81 FUNCTION enrp_val_dr_sysdflt2(
82 p_s_discontin_reason_type IN VARCHAR2 ,
83 p_message_name OUT NOCOPY VARCHAR2)
84 RETURN BOOLEAN AS
85
86 BEGIN -- enrp_val_dr_sysdflt2
87 -- This module validates that the system default indicator has to be set
88 -- for at least one IGS_EN_DCNT_REASONCD mapped to a
89 -- s_discontinuation_reason_type. This routine will be called when all
90 -- records are posted to the database.
91 DECLARE
92 v_dr_count NUMBER;
93 CURSOR c_dr IS
94 SELECT COUNT(*)
95 FROM IGS_EN_DCNT_REASONCD dr
96 WHERE dr.s_discontinuation_reason_type = p_s_discontin_reason_type AND
97 dr.closed_ind = 'N' AND
98 dr.sys_dflt_ind = 'Y';
99 BEGIN
100 p_message_name := null;
101 IF p_s_discontin_reason_type IS NOT NULL THEN
102 OPEN c_dr;
103 FETCH c_dr INTO v_dr_count;
104 CLOSE c_dr;
105 IF v_dr_count = 0 THEN
106 p_message_name := 'IGS_EN_ONE_DISCONT_DEFNED';
107 RETURN FALSE;
108 END IF;
109 END IF;
110 RETURN TRUE;
111 EXCEPTION
112 WHEN OTHERS THEN
113 IF (c_dr%ISOPEN) THEN
114 CLOSE c_dr;
115 END IF;
116 RAISE;
117 END;
118 EXCEPTION
119 WHEN OTHERS THEN
120 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
121 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_DR.enrp_val_dr_sysdflt2');
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124
125 END enrp_val_dr_sysdflt2;
126 --
127 -- Validate system default indicator (>one).
128 FUNCTION enrp_val_dr_sysdflt1(
129 p_s_discontin_reason_type IN VARCHAR2 ,
130 p_message_name OUT NOCOPY VARCHAR2)
131 RETURN BOOLEAN AS
132
133 BEGIN -- enrp_val_dr_sysdflt1
134 -- This module validates that the system default indicator is not set
135 -- for more than one IGS_EN_DCNT_REASONCD maped to a
136 -- s_discontinuation_reason_type. This routine will be called when all
137 -- records are posted to the database.
138 DECLARE
139 v_dr_count NUMBER;
140 CURSOR c_dr IS
141 SELECT COUNT(*)
142 FROM IGS_EN_DCNT_REASONCD dr
143 WHERE dr.s_discontinuation_reason_type = p_s_discontin_reason_type AND
144 dr.closed_ind = 'N' AND
145 dr.sys_dflt_ind = 'Y';
146 BEGIN
147 p_message_name := null;
148 IF p_s_discontin_reason_type IS NOT NULL THEN
149 OPEN c_dr;
150 FETCH c_dr INTO v_dr_count;
151 CLOSE c_dr;
152 IF v_dr_count > 1 THEN
153 p_message_name := 'IGS_EN_ONE_DISCONT_REASONCD';
154 RETURN FALSE;
155 END IF;
156 END IF;
157 RETURN TRUE;
158 EXCEPTION
159 WHEN OTHERS THEN
160 IF (c_dr%ISOPEN) THEN
161 CLOSE c_dr;
162 END IF;
163 RAISE;
164 END;
165 EXCEPTION
166 WHEN OTHERS THEN
167 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
168 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_DR.enrp_val_dr_sysdflt1');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171
172 END enrp_val_dr_sysdflt1;
173
174 --
175 -- Validate the discontinuation reason code default.
176 --
177 FUNCTION enrp_val_dr_dflt(
178 p_message_name OUT NOCOPY VARCHAR2)
179 RETURN BOOLEAN AS
180 BEGIN
181 DECLARE
182 --
183 -- This function has been changed to fix Bug# 2053999
184 --
185 CURSOR cur_c1 IS
186 SELECT count(*) cnt
187 FROM igs_en_dcnt_reasoncd_v
188 WHERE dcnt_unit_ind = 'Y'
189 AND dflt_ind = 'Y'
190 AND closed_ind = 'N';
191 --modified cursor for perf bug 3712579
192 CURSOR cur_c2 IS
193 SELECT count(*) cnt
194 FROM igs_en_dcnt_reasoncd
195 WHERE dcnt_program_ind = 'Y'
196 AND dflt_ind = 'Y'
197 AND closed_ind = 'N';
198
199 l_unit_cnt NUMBER(8) := 0;
200 l_prgm_cnt NUMBER(8) := 0;
201 rec_cur_c1 cur_c1%ROWTYPE;
202 rec_cur_c2 cur_c2%ROWTYPE;
203
204 BEGIN
205
206 OPEN cur_c1;
207 FETCH cur_c1 INTO rec_cur_c1;
208 l_unit_cnt := rec_cur_c1.cnt;
209 CLOSE cur_c1;
210
211 IF l_unit_cnt = 0 THEN
212 --
213 -- If no records found then return message saying that no
214 -- Dafaulted value for unit.
215 --
216 p_message_name := 'IGS_EN_DFLT_DISC_RECD_UNIT';
217 RETURN FALSE;
218 ELSIF l_unit_cnt > 1 THEN
219 --
220 -- If more than one records found then return message saying
221 -- that there are more than one Dafaulted value for unit.
222 --
223 p_message_name := 'IGS_EN_MORE_REASON_CD_UNIT';
224 RETURN FALSE;
225 ELSE
226 --
227 -- If only one record found than do not return any message,
228 -- check defaulted value for Program.
229 --
230 p_message_name := null;
231 END IF;
232
233 OPEN cur_c2;
234 FETCH cur_c2 INTO rec_cur_c2;
235 l_prgm_cnt := rec_cur_c2.cnt;
236 CLOSE cur_c2;
237
238 IF l_prgm_cnt = 0 THEN
239 --
240 -- If no records found then return message saying that no
241 -- Dafaulted value for Program.
242 --
243 p_message_name := 'IGS_EN_DFLT_DISC_RECD_PRGM';
244 RETURN FALSE;
245 ELSIF l_prgm_cnt > 1 THEN
246 --
247 -- If more than one records found then return message saying
248 -- that there are more than one Dafaulted value for Program.
249 --
250 p_message_name := 'IGS_EN_MORE_REASON_CD_PRGM';
251 RETURN FALSE;
252 ELSE
253 --
254 -- If only one record found than do not set any message, and return TRUE
255 -- which indicates successful validation.
256 --
257 p_message_name := null;
258 RETURN TRUE;
259 END IF;
260
261
262 EXCEPTION
263 WHEN OTHERS THEN
264 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
265 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_DR.enrp_val_dr_dflt');
266 IGS_GE_MSG_STACK.ADD;
267 App_Exception.Raise_Exception;
268
269 END;
270 END enrp_val_dr_dflt;
271 END IGS_EN_VAL_DR;
[Home] [Help]