[Home] [Help]
PACKAGE BODY: APPS.POA_PORTAL_SUP_RISK_IND
Source
1 PACKAGE BODY POA_PORTAL_SUP_RISK_IND AS
2 /* $Header: poapsrdb.pls 115.3 2002/01/24 16:19:02 pkm ship $ */
3
4 g_target_level_id number := NULL;
5 g_initialized_target BOOLEAN := FALSE;
6 g_target_short_namet VARCHAR2(30) := 'POA_PORTAL_SUP_TOTAL_CAT';
7 g_target_short_namep VARCHAR2(30) := 'POA_PORTAL_SUP_PRICE_CAT';
8 g_target_short_nameq VARCHAR2(30) := 'POA_PORTAL_SUP_QUALITY_CAT';
9 g_target_short_named VARCHAR2(30) := 'POA_PORTAL_SUP_DELIVERY_CAT';
10 g_target_short_names VARCHAR2(30) := 'POA_PORTAL_SUP_SERVICE_CAT';
11 g_plan_id VARCHAR2(30) := 'STANDARD';
12 g_Dim1_Level_Value_ID number := 1;
13
14
15 g_range1_lowd number;
16 g_range1_highd number;
17 g_range2_lowd number;
18 g_range2_highd number;
19 g_range3_lowd number;
20 g_range3_highd number;
21
22 g_range1_lows number;
23 g_range1_highs number;
24 g_range2_lows number;
25 g_range2_highs number;
26 g_range3_lows number;
27 g_range3_highs number;
28
29 g_range1_lowq number;
30 g_range1_highq number;
31 g_range2_lowq number;
32 g_range2_highq number;
33 g_range3_lowq number;
34 g_range3_highq number;
35
36 g_range1_lowp number;
37 g_range1_highp number;
38 g_range2_lowp number;
39 g_range2_highp number;
40 g_range3_lowp number;
41 g_range3_highp number;
42
43 g_range1_lowt number;
44 g_range1_hight number;
45 g_range2_lowt number;
46 g_range2_hight number;
47 g_range3_lowt number;
48 g_range3_hight number;
49
50 PROCEDURE init_categories IS
51
52 x_no_data_found EXCEPTION;
53 x_null_value EXCEPTION;
54
55 vTargetRec1 BIS_TARGET_PUB.Target_Rec_Type;
56 vRstatus1 varchar2(1);
57 vErrorTbl1 BIS_UTILITIES_PUB.Error_Tbl_Type;
58 vTargetRec2 BIS_TARGET_PUB.Target_Rec_Type;
59 vRstatus2 varchar2(1);
60 vErrorTbl2 BIS_UTILITIES_PUB.Error_Tbl_Type;
61 vTargetRec3 BIS_TARGET_PUB.Target_Rec_Type;
62 vRstatus3 varchar2(1);
63 vErrorTbl3 BIS_UTILITIES_PUB.Error_Tbl_Type;
64 vTargetRec4 BIS_TARGET_PUB.Target_Rec_Type;
65 vRstatus4 varchar2(1);
66 vErrorTbl4 BIS_UTILITIES_PUB.Error_Tbl_Type;
67 vTargetRec5 BIS_TARGET_PUB.Target_Rec_Type;
68 vRstatus5 varchar2(1);
69 vErrorTbl5 BIS_UTILITIES_PUB.Error_Tbl_Type;
70
71 cursor c1 is
72 select target_level_id from
73 bisfv_target_levels
74 where measure_short_name = g_target_short_namet;
75
76 cursor c2 is
77 select target_level_id from
78 bisfv_target_levels
79 where measure_short_name = g_target_short_namep;
80
81 cursor c3 is
82 select target_level_id from
83 bisfv_target_levels
84 where measure_short_name = g_target_short_nameq;
85
86 cursor c4 is
87 select target_level_id from
88 bisfv_target_levels
89 where measure_short_name = g_target_short_named;
90
91 cursor c5 is
92 select target_level_id from
93 bisfv_target_levels
94 where measure_short_name = g_target_short_names;
95
96 BEGIN
97 OPEN c1;
98 FETCH c1 into g_target_level_id;
99
100 IF c1%NOTFOUND THEN
101 CLOSE c1;
102 RAISE x_no_data_found;
103 END IF;
104
105 CLOSE c1;
106
107 vTargetRec1.Target_Level_ID := g_target_level_id;
108 vTargetRec1.Plan_Short_name := g_plan_id;
109 vTargetRec1.Dim1_Level_Value_ID := g_Dim1_Level_Value_ID;
110 BIS_TARGET_PUB.Retrieve_Target(p_api_version => 1.0,
111 p_Target_Rec => vTargetRec1,
112 p_all_info => FND_API.G_FALSE,
113 x_Target_Rec => vTargetRec1,
114 x_return_status => vRstatus1,
115 x_error_Tbl => vErrorTbl1);
116
117 if vRstatus1 = FND_API.G_RET_STS_ERROR then
118 RAISE x_null_value;
119 else
120 g_range1_lowt := vTargetRec1.Range1_low;
121 g_range1_hight := vTargetRec1.Range1_high;
122 g_range2_lowt := vTargetRec1.Range2_low;
123 g_range2_hight := vTargetRec1.Range2_high;
124 g_range3_lowt := vTargetRec1.Range3_low;
125 g_range3_hight := vTargetRec1.Range3_high;
126 end if;
127
128 OPEN c2;
129 FETCH c2 into g_target_level_id;
130
131 IF c2%NOTFOUND THEN
132 CLOSE c2;
133 RAISE x_no_data_found;
134 END IF;
135
136 CLOSE c2;
137
138 vTargetRec2.Target_Level_ID := g_target_level_id;
139 vTargetRec2.Plan_Short_name := g_plan_id;
140 vTargetRec2.Dim1_Level_Value_ID := g_Dim1_Level_Value_ID;
141 BIS_TARGET_PUB.Retrieve_Target(p_api_version => 1.0,
142 p_Target_Rec => vTargetRec2,
143 p_all_info => FND_API.G_FALSE,
144 x_Target_Rec => vTargetRec2,
145 x_return_status => vRstatus2,
146 x_error_Tbl => vErrorTbl2);
147
148 if vRstatus2 = FND_API.G_RET_STS_ERROR then
149 RAISE x_null_value;
150 else
151 g_range1_lowp := vTargetRec2.Range1_low;
152 g_range1_highp := vTargetRec2.Range1_high;
153 g_range2_lowp := vTargetRec2.Range2_low;
154 g_range2_highp := vTargetRec2.Range2_high;
155 g_range3_lowp := vTargetRec2.Range3_low;
156 g_range3_highp := vTargetRec2.Range3_high;
157 end if;
158
159 OPEN c3;
160 FETCH c3 into g_target_level_id;
161
162 IF c3%NOTFOUND THEN
163 CLOSE c3;
164 RAISE x_no_data_found;
165 END IF;
166
167 CLOSE c3;
168
169 vTargetRec3.Target_Level_ID := g_target_level_id;
170 vTargetRec3.Plan_Short_name := g_plan_id;
171 vTargetRec3.Dim1_Level_Value_ID := g_Dim1_Level_Value_ID;
172 BIS_TARGET_PUB.Retrieve_Target(p_api_version => 1.0,
173 p_Target_Rec => vTargetRec3,
174 p_all_info => FND_API.G_FALSE,
175 x_Target_Rec => vTargetRec3,
176 x_return_status => vRstatus3,
177 x_error_Tbl => vErrorTbl3);
178
179 if vRstatus3 = FND_API.G_RET_STS_ERROR then
180 RAISE x_null_value;
181 else
182 g_range1_lowq := vTargetRec3.Range1_low;
183 g_range1_highq := vTargetRec3.Range1_high;
184 g_range2_lowq := vTargetRec3.Range2_low;
185 g_range2_highq := vTargetRec3.Range2_high;
186 g_range3_lowq := vTargetRec3.Range3_low;
187 g_range3_highq := vTargetRec3.Range3_high;
188 end if;
189
190 OPEN c4;
191 FETCH c4 into g_target_level_id;
192
193 IF c4%NOTFOUND THEN
194 CLOSE c4;
195 RAISE x_no_data_found;
196 END IF;
197
198 CLOSE c4;
199
200 vTargetRec4.Target_Level_ID := g_target_level_id;
201 vTargetRec4.Plan_Short_name := g_plan_id;
202 vTargetRec4.Dim1_Level_Value_ID := g_Dim1_Level_Value_ID;
203 BIS_TARGET_PUB.Retrieve_Target(p_api_version => 1.0,
204 p_Target_Rec => vTargetRec4,
205 p_all_info => FND_API.G_FALSE,
206 x_Target_Rec => vTargetRec4,
207 x_return_status => vRstatus4,
208 x_error_Tbl => vErrorTbl4);
209
210 if vRstatus4 = FND_API.G_RET_STS_ERROR then
211 RAISE x_null_value;
212 else
213 g_range1_lowd := vTargetRec4.Range1_low;
214 g_range1_highd := vTargetRec4.Range1_high;
215 g_range2_lowd := vTargetRec4.Range2_low;
216 g_range2_highd := vTargetRec4.Range2_high;
217 g_range3_lowd := vTargetRec4.Range3_low;
218 g_range3_highd := vTargetRec4.Range3_high;
219 end if;
220
221 OPEN c5;
222 FETCH c5 into g_target_level_id;
223
224 IF c5%NOTFOUND THEN
225 CLOSE c5;
226 RAISE x_no_data_found;
227 END IF;
228
229 CLOSE c5;
230
231 vTargetRec5.Target_Level_ID := g_target_level_id;
232 vTargetRec5.Plan_Short_name := g_plan_id;
233 vTargetRec5.Dim1_Level_Value_ID := g_Dim1_Level_Value_ID;
234 BIS_TARGET_PUB.Retrieve_Target(p_api_version => 1.0,
235 p_Target_Rec => vTargetRec5,
236 p_all_info => FND_API.G_FALSE,
237 x_Target_Rec => vTargetRec5,
238 x_return_status => vRstatus5,
239 x_error_Tbl => vErrorTbl5);
240
241 if vRstatus5 = FND_API.G_RET_STS_ERROR then
242 RAISE x_null_value;
243 else
244 g_range1_lows := vTargetRec5.Range1_low;
245 g_range1_highs := vTargetRec5.Range1_high;
246 g_range2_lows := vTargetRec5.Range2_low;
247 g_range2_highs := vTargetRec5.Range2_high;
248 g_range3_lows := vTargetRec5.Range3_low;
249 g_range3_highs := vTargetRec5.Range3_high;
250 end if;
251
252 g_initialized_target := TRUE;
253
254 exception
255 when x_no_data_found then
256 raise_application_error(-20000, 'No data found');
257 when x_null_value then
258 raise_application_error(-20001, 'Null Values' || g_target_level_id ||
259 g_plan_id);
260 when others then
261 if c1%ISOPEN then
262 close c1;
263 end if;
264 raise_application_error(-20002, 'Other Error');
265 end init_categories;
266
267
268 FUNCTION get_target_level_id return number IS
269 BEGIN
270 IF (NOT g_initialized_target) THEN
271 init_categories;
272 END IF;
273 RETURN(g_target_level_id);
274 END get_target_level_id;
275
276
277 FUNCTION get_range1_low(Id IN Number) return number IS
278 BEGIN
279 IF (NOT g_initialized_target) THEN
280 init_categories;
281 END IF;
282
283 if (id = 1) THEN
284 RETURN(g_range1_lowt);
285 ELSIF (id = 2) THEN
286 RETURN(g_range1_lowp);
287 ELSIF (id = 3) THEN
288 RETURN(g_range1_lowq);
289 ELSIF (id = 4) THEN
290 RETURN(g_range1_lowd);
291 ELSIF (id = 5) THEN
292 RETURN(g_range1_lows);
293 END IF;
294
295 RETURN(g_range1_lowt);
296
297 END get_range1_low;
298
299 FUNCTION get_range1_high(Id IN Number) return number IS
300 BEGIN
301 IF (NOT g_initialized_target) THEN
302 init_categories;
303 END IF;
304
305 IF (id = 1) THEN
306 RETURN(g_range1_hight);
307 ELSIF (id =2) THEN
308 RETURN(g_range1_highp);
309 ELSIF (id = 3) THEN
310 RETURN(g_range1_highq);
311 ELSIF (id = 4) THEN
312 RETURN(g_range1_highd);
313 ELSIF (id = 5) THEN
314 RETURN(g_range1_highs);
315 END IF;
316
317 RETURN(g_range1_hight);
318
319 END get_range1_high;
320
321 FUNCTION get_range2_low(Id IN Number) return number IS
322 BEGIN
323 IF (NOT g_initialized_target) THEN
324 init_categories;
325 END IF;
326 IF (id = 1) THEN
327 RETURN(g_range2_lowt);
328 ELSIF (id = 2) THEN
329 RETURN(g_range2_lowp);
330 ELSIF (id = 3) THEN
331 RETURN(g_range2_lowq);
332 ELSIF (id = 4) THEN
333 RETURN(g_range2_lowd);
334 ELSIF (id = 5) THEN
335 RETURN(g_range2_lows);
336 END IF;
337
338 RETURN(g_range2_lowt);
339 END get_range2_low;
340
341 FUNCTION get_range2_high(Id IN Number) return number IS
342 BEGIN
343 IF (NOT g_initialized_target) THEN
344 init_categories;
345 END IF;
346 IF (id = 1) THEN
347 RETURN(g_range2_hight);
348 ELSIF (id = 2) THEN
349 RETURN(g_range2_highp);
350 ELSIF (id = 3) THEN
351 RETURN(g_range2_highq);
352 ELSIF (id = 4) THEN
353 RETURN(g_range2_highd);
354 ELSIF (id = 5) THEN
355 RETURN(g_range2_highs);
356 END IF;
357
358 RETURN(g_range2_hight);
362 BEGIN
359 END get_range2_high;
360
361 FUNCTION get_range3_low(Id IN Number) return number IS
363 IF (NOT g_initialized_target) THEN
364 init_categories;
365 END IF;
366 IF (id = 1) THEN
367 RETURN(g_range3_lowt);
368 ELSIF (id = 2) THEN
369 RETURN(g_range3_lowp);
370 ELSIF (id = 3) THEN
371 RETURN(g_range3_lowq);
372 ELSIF (id = 4) THEN
373 RETURN(g_range3_lowd);
374 ELSIF (id = 5) THEN
375 RETURN(g_range3_lows);
376 END IF;
377
378 RETURN(g_range3_lowt);
379 END get_range3_low;
380
381 FUNCTION get_range3_high(Id IN Number) return number IS
382 BEGIN
383 IF (NOT g_initialized_target) THEN
384 init_categories;
385 END IF;
386 IF (id =1) THEN
387 RETURN(g_range3_hight);
388 ELSIF (id = 2) THEN
389 RETURN(g_range3_highp);
390 ELSIF (id = 3) THEN
391 RETURN(g_range3_highq);
392 ELSIF (id = 4) THEN
393 RETURN(g_range3_highd);
394 ELSIF (id = 5) THEN
395 RETURN(g_range3_highs);
396 END IF;
397
398 RETURN(g_range3_hight);
399 END get_range3_high;
400
401 end;
402