[Home] [Help]
PACKAGE BODY: APPS.BSC_COLOR_RANGE_PVT
Source
1 PACKAGE BODY BSC_COLOR_RANGE_PVT AS
2 /* $Header: BSCVCRNB.pls 120.2.12000000.1 2007/07/17 07:44:39 appldev noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCVCRNB.pls |
10 | |
11 | Creation Date: |
12 | November 02, 2006 |
13 | |
14 | Creator: |
15 | Pradeep Pandey |
16 | |
17 | Description: Private Body version. |
18 | This package is to manage Range Properties properties |
19 | and provide CRUD APIs for BSC_SYS_COLOR_RANGES_B and related tbl|
20 | |
21 | 26-JUN-2007 ankgoel Bug#6132361 - Handled PL objectives |
22 +======================================================================================+
23 */
24
25 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_COLOR_RANGE_PVT';
26
27
28 /************************************************************************************
29 ************************************************************************************/
30 PROCEDURE Create_Color_Props (
31 p_commit IN VARCHAR2 := FND_API.G_FALSE
32 ,p_objective_id IN NUMBER
33 ,p_kpi_measure_id IN NUMBER
34 ,p_color_type IN VARCHAR2
35 ,p_color_range_id IN NUMBER
36 ,p_property_value IN VARCHAR2 := NULL
37 ,x_return_status OUT NOCOPY VARCHAR2
38 ,x_msg_count OUT NOCOPY NUMBER
39 ,x_msg_data OUT NOCOPY VARCHAR2
40 ) IS
41 l_user_id bsc_color_type_props.last_updated_by%TYPE;
42 l_count NUMBER;
43 BEGIN
44
45 FND_MSG_PUB.Initialize;
46 x_return_status := FND_API.G_RET_STS_SUCCESS;
47
48 SAVEPOINT BscColorRangePvt_CrtColorRng;
49
50 IF(p_objective_id IS NULL) THEN
51 FND_MESSAGE.SET_NAME('BSC','BSC_OBJECTIVE_ID_NULL');
52 FND_MSG_PUB.ADD;
53 RAISE FND_API.G_EXC_ERROR;
54 END IF;
55
56 IF (p_kpi_measure_id IS NULL) THEN
57 SELECT COUNT(1)
58 INTO l_count
59 FROM bsc_color_type_props
60 WHERE indicator = p_objective_id
61 AND kpi_measure_id IS NULL;
62 ELSE
63 SELECT COUNT(1)
64 INTO l_count
65 FROM bsc_color_type_props
66 WHERE indicator = p_objective_id
67 AND kpi_measure_id = p_kpi_measure_id
68 AND NVL(property_value, -1) = DECODE(p_property_value, NULL, -1, p_property_value);
69 END IF;
70
71 IF(l_count > 0 ) THEN
72 FND_MESSAGE.SET_NAME('BSC','BSC_RANGES_ALREADY_EXISTS');
73 FND_MSG_PUB.ADD;
74 RAISE FND_API.G_EXC_ERROR;
75 END IF;
76
77 l_user_id := FND_GLOBAL.USER_ID;
78
79 INSERT INTO bsc_color_type_props(indicator
80 ,kpi_measure_id
81 ,color_type
82 ,color_range_id
83 ,property_value
84 ,creation_date
85 ,created_by
86 ,last_update_date
87 ,last_updated_by
88 ,last_update_login)
89 VALUES(p_objective_id
90 ,p_kpi_measure_id
91 ,p_color_type
92 ,p_color_range_id
93 ,p_property_value
94 ,sysdate
95 ,l_user_id
96 ,sysdate
97 ,l_user_id
98 ,l_user_id);
99 EXCEPTION
100 WHEN FND_API.G_EXC_ERROR THEN
101 IF (x_msg_data IS NULL) THEN
102 ROLLBACK TO BscColorRangePvt_CrtColorRng;
103 FND_MSG_PUB.Count_And_Get
104 ( p_encoded => FND_API.G_FALSE
105 , p_count => x_msg_count
106 , p_data => x_msg_data
107 );
108 END IF;
109 x_return_status := FND_API.G_RET_STS_ERROR;
110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
111 IF (x_msg_data IS NULL) THEN
112 FND_MSG_PUB.Count_And_Get
113 ( p_encoded => FND_API.G_FALSE
114 , p_count => x_msg_count
115 , p_data => x_msg_data
116 );
117 END IF;
118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
119 WHEN NO_DATA_FOUND THEN
120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
121 IF (x_msg_data IS NOT NULL) THEN
122 x_msg_data := x_msg_data||' -> BSC_COLOR_RANGE_PVT.Create_Color_Props';
123 ELSE
124 x_msg_data := SQLERRM||' at BSC_COLOR_RANGE_PVT.Create_Color_Props';
125 END IF;
126 WHEN OTHERS THEN
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
128 IF (x_msg_data IS NOT NULL) THEN
129 x_msg_data := x_msg_data||' -> BSC_COLOR_RANGE_PVT.Create_Color_Props';
130 ELSE
131 x_msg_data := SQLERRM||' at BSC_COLOR_RANGE_PVT.Create_Color_Props';
132 END IF;
133 END Create_Color_Props;
134
135 /************************************************************************************
136 ************************************************************************************/
137 PROCEDURE Create_Color_Range (
138 p_commit IN VARCHAR2 := FND_API.G_FALSE
139 ,p_range_id IN NUMBER
140 ,p_Bsc_Kpi_Color_Range_Rec IN BSC_COLOR_RANGES_PUB.Bsc_Color_Range_Rec
141 ,p_user_id IN FND_USER.user_id%TYPE
142 ,x_return_status OUT NOCOPY VARCHAR2
143 ,x_msg_count OUT NOCOPY NUMBER
144 ,x_msg_data OUT NOCOPY VARCHAR2
145 ) IS
146 l_bsc_kpi_color_range_rec BSC_COLOR_RANGES_PUB.Bsc_Color_Range_Rec;
147 l_th_count NUMBER;
148 BEGIN
149
150 FND_MSG_PUB.Initialize;
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152
153 SAVEPOINT BscColorRangePvt_CrtColorRng;
154 l_bsc_kpi_color_range_rec := p_Bsc_Kpi_Color_Range_Rec;
155
156 IF(p_Bsc_Kpi_Color_Range_Rec IS NOT NULL) THEN
157 FOR l_th_count IN 1..p_Bsc_Kpi_Color_Range_Rec.COUNT LOOP
158 INSERT INTO bsc_color_ranges(color_range_id
159 ,color_range_sequence
160 ,low
161 ,high
162 ,color_id)
163 VALUES(p_range_id
164 ,p_Bsc_Kpi_Color_Range_Rec(l_th_count).color_range_sequence
165 ,p_Bsc_Kpi_Color_Range_Rec(l_th_count).low
166 ,p_Bsc_Kpi_Color_Range_Rec(l_th_count).high
167 ,p_Bsc_Kpi_Color_Range_Rec(l_th_count).color_id);
168 END LOOP;
169 END IF;
170 EXCEPTION
171 WHEN FND_API.G_EXC_ERROR THEN
172 IF (x_msg_data IS NULL) THEN
173 ROLLBACK TO BscColorRangePvt_CrtColorRng;
174 FND_MSG_PUB.Count_And_Get
175 ( p_encoded => FND_API.G_FALSE
176 , p_count => x_msg_count
177 , p_data => x_msg_data
178 );
179 END IF;
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
182 IF (x_msg_data IS NULL) THEN
183 FND_MSG_PUB.Count_And_Get
184 ( p_encoded => FND_API.G_FALSE
185 , p_count => x_msg_count
186 , p_data => x_msg_data
187 );
188 END IF;
189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
190 WHEN NO_DATA_FOUND THEN
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
192 IF (x_msg_data IS NOT NULL) THEN
193 x_msg_data := x_msg_data||' -> BSC_COLOR_RANGE_PVT.Create_Color_Range';
194 ELSE
195 x_msg_data := SQLERRM||' at BSC_COLOR_RANGE_PVT.Create_Color_Range';
196 END IF;
197 WHEN OTHERS THEN
198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
199 IF (x_msg_data IS NOT NULL) THEN
200 x_msg_data := x_msg_data||' -> BSC_COLOR_RANGE_PVT.Create_Color_Range';
201 ELSE
202 x_msg_data := SQLERRM||' at BSC_COLOR_RANGE_PVT.Create_Color_Range';
203 END IF;
204 END Create_Color_Range;
205 /************************************************************************************
206 ************************************************************************************/
207
208 PROCEDURE Delete_Color_Ranges (
209 p_commit IN VARCHAR2 := FND_API.G_FALSE
210 ,p_color_range_id IN NUMBER
211 ,x_return_status OUT NOCOPY VARCHAR2
212 ,x_msg_count OUT NOCOPY NUMBER
213 ,x_msg_data OUT NOCOPY VARCHAR2
214 ) IS
215 l_color_id NUMBER;
216 BEGIN
217 FND_MSG_PUB.Initialize;
218 x_return_status := FND_API.G_RET_STS_SUCCESS;
219
220 SAVEPOINT BscCRangePvt_DelColorRng;
221 -- Update by id or short_name
222 IF(p_color_range_id IS NULL) THEN
223 FND_MESSAGE.SET_NAME('BSC','BSC_RANGE_ID_NULL');
224 FND_MSG_PUB.ADD;
225 RAISE FND_API.G_EXC_ERROR;
226 END IF;
227
228 DELETE bsc_color_ranges
229 WHERE color_range_id = p_color_range_id;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 ROLLBACK TO BscCRangePvt_DelColorRng;
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235 IF (x_msg_data IS NULL) THEN
236 FND_MSG_PUB.Count_And_Get
237 ( p_encoded => FND_API.G_FALSE
238 ,p_count => x_msg_count
239 ,p_data => x_msg_data
240 );
241 END IF;
242 --raise;
243 END Delete_Color_Ranges;
244
245 /************************************************************************************
246 ************************************************************************************/
247 PROCEDURE Delete_Color_Prop_Ranges (
248 p_commit IN VARCHAR2:= FND_API.G_FALSE
249 ,p_color_range_id IN NUMBER
250 ,x_return_status OUT NOCOPY VARCHAR2
251 ,x_msg_count OUT NOCOPY NUMBER
252 ,x_msg_data OUT NOCOPY VARCHAR2
253 ) IS
254 BEGIN
255 FND_MSG_PUB.Initialize;
256 x_return_status := FND_API.G_RET_STS_SUCCESS;
257
258 SAVEPOINT BscCRangePvt_DelColorRngProp;
259
260 IF(p_color_range_id IS NULL) THEN
261 FND_MESSAGE.SET_NAME('BSC','BSC_RANGE_ID_NULL');
262 FND_MSG_PUB.ADD;
263 RAISE FND_API.G_EXC_ERROR;
264 END IF;
265
266 DELETE bsc_color_type_props
267 WHERE color_range_id = p_color_range_id;
268
269 Delete_Color_Ranges(p_color_range_id => p_color_range_id
270 ,x_return_status => x_return_status
271 ,x_msg_count => x_msg_count
272 ,x_msg_data => x_msg_data);
273
274 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
275 RAISE FND_API.G_EXC_ERROR;
276 END IF;
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 ROLLBACK TO BscCRangePvt_DelColorRngProp;
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
282 IF (x_msg_data IS NULL) THEN
283 FND_MSG_PUB.Count_And_Get
284 ( p_encoded => FND_API.G_FALSE
285 ,p_count => x_msg_count
286 ,p_data => x_msg_data
287 );
288 END IF;
289 END Delete_Color_Prop_Ranges;
290
291 END BSC_COLOR_RANGE_PVT;