[Home] [Help]
PACKAGE BODY: APPS.BSC_COLOR_PVT
Source
1 package body BSC_COLOR_PVT as
2 /* $Header: BSCVCOLB.pls 120.3.12000000.1 2007/07/17 07:44:36 appldev noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCVCOLB.pls |
10 | |
11 | Creation Date: |
12 | October 26, 2006 |
13 | |
14 | Creator: |
15 | Pradeep Pandey |
16 | |
17 | Description: Private Body version. |
18 | This package is to manage System level Color properties |
19 | and provide CRUD APIs for BSC_SYS_COLORS_B and related table |
20 | |
21 +======================================================================================+
22 */
23
24 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_COLOR_PVT';
25
26 PROCEDURE Retrieve_Color(
27 p_commit IN VARCHAR2:= FND_API.G_FALSE
28 ,p_Bsc_Color_Id IN NUMBER
29 ,p_Bsc_Color_SN IN VARCHAR2
30 ,x_Bsc_Color_Rec OUT NOCOPY BSC_COLOR_PUB.Bsc_Color_Rec
31 ,x_return_status OUT NOCOPY VARCHAR2
32 ,x_msg_count OUT NOCOPY NUMBER
33 ,x_msg_data OUT NOCOPY VARCHAR2
34 );
35
36 /************************************************************************************
37 ************************************************************************************/
38 PROCEDURE Create_Color(
39 p_commit IN VARCHAR2 := FND_API.G_FALSE
40 ,p_Bsc_Color_Rec IN BSC_COLOR_PUB.Bsc_Color_Rec
41 ,x_return_status OUT NOCOPY VARCHAR2
42 ,x_msg_count OUT NOCOPY NUMBER
43 ,x_msg_data OUT NOCOPY VARCHAR2
44 ) IS
45
46 l_Count NUMBER;
47 l_forecast_color NUMBER;
48 BEGIN
49
50 FND_MSG_PUB.Initialize;
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52
53 SAVEPOINT BscColorPvt_CrtColor;
54
55 IF(p_Bsc_Color_Rec.Color_id IS NOT NULL) THEN
56 SELECT COUNT(1) INTO l_Count
57 FROM BSC_SYS_COLORS_B
58 WHERE COLOR_ID = p_Bsc_Color_Rec.Color_id;
59
60 IF (l_Count > 0) THEN
61 FND_MESSAGE.SET_NAME('BSC','BSC_COLOR_ID_EXISTS');
62 FND_MESSAGE.SET_TOKEN('BSC_COLOR_ID', p_Bsc_Color_Rec.Color_Id);
63 FND_MSG_PUB.ADD;
64 RAISE FND_API.G_EXC_ERROR;
65 END IF;
66 ELSE
67 FND_MESSAGE.SET_NAME('BSC','BSC_NO_COLOR_ID');
68 FND_MESSAGE.SET_TOKEN('BSC_COLOR_ID', p_Bsc_Color_Rec.Color_Id);
69 FND_MSG_PUB.ADD;
70 RAISE FND_API.G_EXC_ERROR;
71 END IF;
72 IF (p_Bsc_Color_Rec.Short_Name IS NULL) THEN
73 FND_MESSAGE.SET_NAME('BSC','BSC_NO_COLOR_SN');
74 FND_MESSAGE.SET_TOKEN('BSC_COLOR_SN', p_Bsc_Color_Rec.short_name);
75 FND_MSG_PUB.ADD;
76 RAISE FND_API.G_EXC_ERROR;
77 END IF;
78 BSC_COLOR_PKG.INSERT_ROW(p_Bsc_Color_Rec.color_id
79 ,p_Bsc_Color_Rec.Short_Name
80 ,p_Bsc_Color_Rec.name
81 ,p_Bsc_Color_Rec.description
82 ,p_Bsc_Color_Rec.prototype_label
83 ,p_Bsc_Color_Rec.Perf_Sequence
84 ,p_Bsc_Color_Rec.color
85 ,p_Bsc_Color_Rec.User_Color
86 ,p_Bsc_Color_Rec.forecast_color
87 ,p_Bsc_Color_Rec.User_Forecast_Color
88 ,p_Bsc_Color_Rec.Numeric_Equivalent
89 ,p_Bsc_Color_Rec.User_Numeric_Equivalent
90 ,p_Bsc_Color_Rec.Image
91 ,p_Bsc_Color_Rec.Created_By
92 ,p_Bsc_Color_Rec.Last_Updated_By
93 ,p_Bsc_Color_Rec.Last_Update_Login
94 );
95 EXCEPTION
96 WHEN FND_API.G_EXC_ERROR THEN
97 IF (x_msg_data IS NULL) THEN
98 ROLLBACK TO BscColorPvt_CrtColor;
99 FND_MSG_PUB.Count_And_Get
100 ( p_encoded => FND_API.G_FALSE
101 , p_count => x_msg_count
102 , p_data => x_msg_data
103 );
104 END IF;
105 x_return_status := FND_API.G_RET_STS_ERROR;
106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
107 IF (x_msg_data IS NULL) THEN
108 FND_MSG_PUB.Count_And_Get
109 ( p_encoded => FND_API.G_FALSE
110 , p_count => x_msg_count
111 , p_data => x_msg_data
112 );
113 END IF;
114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
115 WHEN NO_DATA_FOUND THEN
116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
117 IF (x_msg_data IS NOT NULL) THEN
118 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Color with parameter x_Bsc_Kpi_Entity_Rec ';
119 ELSE
120 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Color with parameter x_Bsc_Kpi_Entity_Rec ';
121 END IF;
122 WHEN OTHERS THEN
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124 IF (x_msg_data IS NOT NULL) THEN
125 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Color with parameter x_Bsc_Kpi_Entity_Rec ';
126 ELSE
127 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Color with parameter x_Bsc_Kpi_Entity_Rec ';
128 END IF;
129 END Create_Color;
130
131 /************************************************************************************
132 ************************************************************************************/
133 -- API To be called from UI.
134 PROCEDURE Update_Color(
135 p_commit IN VARCHAR2 := FND_API.G_FALSE
136 ,p_Bsc_Color_Rec IN BSC_COLOR_PUB.Bsc_Color_Rec
137 ,x_return_status OUT NOCOPY VARCHAR2
138 ,x_msg_count OUT NOCOPY NUMBER
139 ,x_msg_data OUT NOCOPY VARCHAR2
140 ) IS
141 l_color_id NUMBER;
142 l_Bsc_Color_Rec BSC_COLOR_PUB.Bsc_Color_Rec;
143
144 l_user_id NUMBER;
145 l_login_id NUMBER;
146 BEGIN
147 FND_MSG_PUB.Initialize;
148 x_return_status := FND_API.G_RET_STS_SUCCESS;
149
150 SAVEPOINT BscColorPvt_UpdColor;
151 /*BSC_COLOR_PKG.UPDATE_ROW(p_Bsc_Color_Rec.color_id
152 ,p_Bsc_Color_Rec.Perf_Sequence
153 ,p_Bsc_Color_Rec.color
154 ,p_Bsc_Color_Rec.user_color
155 ,p_Bsc_Color_Rec.User_Forecast_Color
156 ,p_Bsc_Color_Rec.Numeric_Equivalent
157 ,p_Bsc_Color_Rec.User_Numeric_Equivalent
158 ,p_Bsc_Color_Rec.Image
159 ,p_Bsc_Color_Rec.Last_Updated_By
160 ,p_Bsc_Color_Rec.last_update_login);*/
161 Retrieve_Color(p_Bsc_Color_Id => p_Bsc_Color_Rec.color_id
162 , p_Bsc_Color_SN => p_Bsc_Color_Rec.short_name
163 ,x_Bsc_Color_Rec => l_Bsc_Color_Rec
164 ,x_return_status => x_return_status
165 ,x_msg_count => x_msg_count
166 ,x_msg_data => x_msg_data);
167 IF(x_return_status = FND_API.G_RET_STS_ERROR) THEN
168 RAISE FND_API.G_EXC_ERROR;
169 END IF;
170
171 /*IF(p_Bsc_Color_Rec.name IS NOT NULL) THEN
172 l_Bsc_Color_Rec.name := p_Bsc_Color_Rec.name;
173 END IF;
174 IF(p_Bsc_Color_Rec.description IS NOT NULL) THEN
175 l_Bsc_Color_Rec.description := p_Bsc_Color_Rec.description;
176 END IF;
177 IF(p_Bsc_Color_Rec.prototype_label IS NOT NULL) THEN
178 l_Bsc_Color_Rec.prototype_label := p_Bsc_Color_Rec.prototype_label;
179 END IF;*/
180 IF(p_Bsc_Color_Rec.perf_sequence IS NOT NULL) THEN
181 l_Bsc_Color_Rec.perf_sequence := p_Bsc_Color_Rec.perf_sequence;
182 END IF;
183 IF(p_Bsc_Color_Rec.color IS NOT NULL) THEN
184 l_Bsc_Color_Rec.color := p_Bsc_Color_Rec.color;
185 END IF;
186 IF(p_Bsc_Color_Rec.forecast_color IS NOT NULL) THEN
187 l_Bsc_Color_Rec.forecast_color := p_Bsc_Color_Rec.forecast_color;
188 END IF;
189 IF(p_Bsc_Color_Rec.user_forecast_color IS NOT NULL) THEN
190 l_Bsc_Color_Rec.user_forecast_color := p_Bsc_Color_Rec.user_forecast_color;
191 END IF;
192 IF(p_Bsc_Color_Rec.user_color IS NOT NULL) THEN
193 l_Bsc_Color_Rec.user_color := p_Bsc_Color_Rec.user_color;
194 END IF;
195 IF(p_Bsc_Color_Rec.user_numeric_equivalent IS NOT NULL) THEN
196 l_Bsc_Color_Rec.user_numeric_equivalent := p_Bsc_Color_Rec.user_numeric_equivalent;
197 END IF;
198 IF(p_Bsc_Color_Rec.numeric_equivalent IS NOT NULL) THEN
199 l_Bsc_Color_Rec.numeric_equivalent := p_Bsc_Color_Rec.numeric_equivalent;
200 END IF;
201
202 l_user_id := fnd_global.USER_ID;
203 l_login_id := fnd_global.LOGIN_ID;
204
205 BSC_COLOR_PKG.UPDATE_ROW(p_Color_Id => l_Bsc_Color_Rec.color_id
206 ,p_Perf_Sequence_Id => l_Bsc_Color_Rec.Perf_Sequence
207 ,p_System_Color => l_Bsc_Color_Rec.color
208 ,p_User_Color => l_Bsc_Color_Rec.user_color
209 ,p_User_Forecast_Color => l_Bsc_Color_Rec.User_Forecast_Color
210 ,p_Numeric_Equivalent => l_Bsc_Color_Rec.Numeric_Equivalent
211 ,p_User_Numeric_Equivalent=> l_Bsc_Color_Rec.User_Numeric_Equivalent
212 ,p_Image => l_Bsc_Color_Rec.Image
213 ,p_Last_Updated_By => nvl(l_Bsc_Color_Rec.Last_Updated_By, l_user_id)
214 ,p_Last_Update_Login => nvl(l_Bsc_Color_Rec.last_update_login, l_login_id));
215 EXCEPTION
216 WHEN OTHERS THEN
217 ROLLBACK TO BscColorPvt_UpdColor;
218 raise;
219 END Update_Color;
220 /************************************************************************************
221 ************************************************************************************/
222 PROCEDURE Delete_Color(
223 p_commit IN VARCHAR2:= FND_API.G_FALSE
224 ,p_Bsc_Color_Id IN NUMBER
225 ,p_Bsc_Color_SN IN NUMBER
226 ,x_return_status OUT NOCOPY VARCHAR2
227 ,x_msg_count OUT NOCOPY NUMBER
228 ,x_msg_data OUT NOCOPY VARCHAR2
229 ) IS
230 BEGIN
231 FND_MSG_PUB.Initialize;
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233
234 SAVEPOINT BscColorPvt_DelColor;
235
236 IF(p_Bsc_Color_Id IS NULL AND p_Bsc_Color_SN IS NULL) THEN
237 FND_MESSAGE.SET_NAME('BSC','BSC_NO_COLOR_ID_SN');
238 FND_MSG_PUB.ADD;
239 RAISE FND_API.G_EXC_ERROR;
240 END IF;
241 BSC_COLOR_PKG.DELETE_ROW(p_Bsc_Color_Id
242 ,p_Bsc_Color_SN);
243 EXCEPTION
244 WHEN OTHERS THEN
245 x_return_status := FND_API.G_RET_STS_ERROR;
246 ROLLBACK TO BscColorPvt_DelColor;
247 END Delete_Color;
248
249 /************************************************************************************
250 ************************************************************************************/
251 PROCEDURE Translate_Color(
252 p_commit IN VARCHAR2:= FND_API.G_FALSE
253 ,p_Bsc_Color_Rec IN BSC_COLOR_PUB.Bsc_Color_Rec
254 ,x_return_status OUT NOCOPY VARCHAR2
255 ,x_msg_count OUT NOCOPY NUMBER
256 ,x_msg_data OUT NOCOPY VARCHAR2
260 l_login_id NUMBER;
257 ) IS
258 l_Bsc_Color_Rec BSC_COLOR_PUB.Bsc_Color_Rec;
259 l_user_id NUMBER;
261 BEGIN
262 SAVEPOINT BscColorPvt_TrnsColor;
263
264 Retrieve_Color(p_Bsc_Color_Id => p_Bsc_Color_Rec.color_id
265 , p_Bsc_Color_SN => p_Bsc_Color_Rec.short_name
266 ,x_Bsc_Color_Rec => l_Bsc_Color_Rec
267 ,x_return_status => x_return_status
268 ,x_msg_count => x_msg_count
269 ,x_msg_data => x_msg_data);
270 IF(x_return_status = FND_API.G_RET_STS_ERROR) THEN
271 RAISE FND_API.G_EXC_ERROR;
272 END IF;
273
274 IF(p_Bsc_Color_Rec.name IS NOT NULL) THEN
275 l_Bsc_Color_Rec.name := p_Bsc_Color_Rec.name;
276 END IF;
277 IF(p_Bsc_Color_Rec.description IS NOT NULL) THEN
278 l_Bsc_Color_Rec.description := p_Bsc_Color_Rec.description;
279 END IF;
280 IF(p_Bsc_Color_Rec.prototype_label IS NOT NULL) THEN
281 l_Bsc_Color_Rec.prototype_label := p_Bsc_Color_Rec.prototype_label;
282 END IF;
283 IF(p_Bsc_Color_Rec.perf_sequence IS NOT NULL) THEN
284 l_Bsc_Color_Rec.perf_sequence := p_Bsc_Color_Rec.perf_sequence;
285 END IF;
286 IF(p_Bsc_Color_Rec.color IS NOT NULL) THEN
287 l_Bsc_Color_Rec.color := p_Bsc_Color_Rec.color;
288 END IF;
289 IF(p_Bsc_Color_Rec.forecast_color IS NOT NULL) THEN
290 l_Bsc_Color_Rec.forecast_color := p_Bsc_Color_Rec.forecast_color;
291 END IF;
292 IF(p_Bsc_Color_Rec.numeric_equivalent IS NOT NULL) THEN
293 l_Bsc_Color_Rec.numeric_equivalent := p_Bsc_Color_Rec.numeric_equivalent;
294 END IF;
295
296 IF(l_Bsc_Color_Rec.user_color IS NULL) THEN
297 l_Bsc_Color_Rec.user_color := l_Bsc_Color_Rec.color;
298 END IF;
299 IF(l_Bsc_Color_Rec.User_Numeric_Equivalent IS NULL) THEN
300 l_Bsc_Color_Rec.User_Numeric_Equivalent := l_Bsc_Color_Rec.numeric_equivalent;
301 END IF;
302 IF(l_Bsc_Color_Rec.User_Forecast_Color IS NULL) THEN
303 l_Bsc_Color_Rec.User_Forecast_Color := l_Bsc_Color_Rec.forecast_color;
304 END IF;
305
306
307 l_user_id := fnd_global.USER_ID;
308 l_login_id := fnd_global.LOGIN_ID;
309
310 BSC_COLOR_PKG.UPDATE_ROW(p_Color_Id => l_Bsc_Color_Rec.color_id
311 ,p_System_Color_Name => l_Bsc_Color_Rec.name
312 ,p_System_Color_Desc => l_Bsc_Color_Rec.description
313 ,p_prototype_label => l_Bsc_Color_Rec.prototype_label
314 ,p_Perf_Sequence_Id => l_Bsc_Color_Rec.Perf_Sequence
315 ,p_System_Color => l_Bsc_Color_Rec.color
316 ,p_User_Color => l_Bsc_Color_Rec.user_color
317 ,p_User_Forecast_Color => l_Bsc_Color_Rec.User_Forecast_Color
318 ,p_Numeric_Equivalent => l_Bsc_Color_Rec.Numeric_Equivalent
319 ,p_User_Numeric_Equivalent=> l_Bsc_Color_Rec.User_Numeric_Equivalent
320 ,p_Image => l_Bsc_Color_Rec.Image
321 ,p_Last_Updated_By => l_Bsc_Color_Rec.Last_Updated_By
322 ,p_Last_Update_Login => l_Bsc_Color_Rec.last_update_login);
323 EXCEPTION
324 WHEN OTHERS THEN
325 x_return_status := FND_API.G_RET_STS_ERROR;
326 ROLLBACK TO BscColorPvt_TrnsColor;
327 END Translate_Color;
328 /************************************************************************************
329 ************************************************************************************/
330 PROCEDURE Load_Translated_Color(
331 p_commit IN VARCHAR2:= FND_API.G_FALSE
332 ,p_Bsc_Color_Rec IN BSC_COLOR_PUB.Bsc_Color_Rec
333 ,x_return_status OUT NOCOPY VARCHAR2
334 ,x_msg_count OUT NOCOPY NUMBER
335 ,x_msg_data OUT NOCOPY VARCHAR2
336 ) IS
337 l_Bsc_Color_Rec BSC_COLOR_PUB.Bsc_Color_Rec;
338 l_user_id NUMBER;
339 l_login_id NUMBER;
340 BEGIN
341 FND_MSG_PUB.Initialize;
342 x_return_status := FND_API.G_RET_STS_SUCCESS;
343
344 SAVEPOINT BscColorPvt_LdTrnsColor;
345
346 Retrieve_Color(p_Bsc_Color_Id => p_Bsc_Color_Rec.color_id
347 , p_Bsc_Color_SN => p_Bsc_Color_Rec.short_name
348 ,x_Bsc_Color_Rec => l_Bsc_Color_Rec
349 ,x_return_status => x_return_status
350 ,x_msg_count => x_msg_count
351 ,x_msg_data => x_msg_data);
352 IF(x_return_status = FND_API.G_RET_STS_ERROR) THEN
353 RAISE FND_API.G_EXC_ERROR;
354 END IF;
355
356 IF(p_Bsc_Color_Rec.name IS NOT NULL) THEN
357 l_Bsc_Color_Rec.name := p_Bsc_Color_Rec.name;
358 END IF;
359 IF(p_Bsc_Color_Rec.description IS NOT NULL) THEN
360 l_Bsc_Color_Rec.description := p_Bsc_Color_Rec.description;
361 END IF;
362 IF(p_Bsc_Color_Rec.prototype_label IS NOT NULL) THEN
363 l_Bsc_Color_Rec.prototype_label := p_Bsc_Color_Rec.prototype_label;
364 END IF;
365 IF(p_Bsc_Color_Rec.perf_sequence IS NOT NULL) THEN
366 l_Bsc_Color_Rec.perf_sequence := p_Bsc_Color_Rec.perf_sequence;
367 END IF;
368 IF(p_Bsc_Color_Rec.color IS NOT NULL) THEN
369 l_Bsc_Color_Rec.color := p_Bsc_Color_Rec.color;
370 END IF;
371 IF(p_Bsc_Color_Rec.forecast_color IS NOT NULL) THEN
372 l_Bsc_Color_Rec.forecast_color := p_Bsc_Color_Rec.forecast_color;
373 END IF;
374 IF(p_Bsc_Color_Rec.numeric_equivalent IS NOT NULL) THEN
375 l_Bsc_Color_Rec.numeric_equivalent := p_Bsc_Color_Rec.numeric_equivalent;
376 END IF;
377
378 l_user_id := fnd_global.USER_ID;
379 l_login_id := fnd_global.LOGIN_ID;
380
381 UPDATE bsc_sys_colors_tl
382 SET name = l_Bsc_Color_Rec.name
386 ,last_update_date = l_Bsc_Color_Rec.last_update_date
383 ,description = l_Bsc_Color_Rec.description
384 ,prototype_label = l_Bsc_Color_Rec.prototype_label
385 ,source_lang = userenv('LANG')
387 ,last_updated_by = l_user_id
388 ,last_update_login= l_login_id
389 WHERE color_id= l_Bsc_Color_Rec.color_id
390 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
391
392 UPDATE bsc_sys_colors_b
393 SET perf_sequence = l_Bsc_Color_Rec.perf_sequence
394 ,color = l_Bsc_Color_Rec.color
395 ,forecast_color = l_Bsc_Color_Rec.forecast_color
396 ,numeric_equivalent = l_Bsc_Color_Rec.numeric_equivalent
397 ,last_update_date = l_Bsc_Color_Rec.last_update_date
398 ,last_updated_by = l_user_id
399 ,last_update_login = l_login_id
400 WHERE color_id= l_Bsc_Color_Rec.color_id;
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 x_return_status := FND_API.G_RET_STS_ERROR;
405 ROLLBACK TO BscColorPvt_LdTrnsColor;
406 END Load_Translated_Color;
407
408 /************************************************************************************
409 ************************************************************************************/
410 PROCEDURE Retrieve_Color(
411 p_commit IN VARCHAR2:= FND_API.G_FALSE
412 ,p_Bsc_Color_Id IN NUMBER
413 ,p_Bsc_Color_SN IN VARCHAR2
414 ,x_Bsc_Color_Rec OUT NOCOPY BSC_COLOR_PUB.Bsc_Color_Rec
415 ,x_return_status OUT NOCOPY VARCHAR2
416 ,x_msg_count OUT NOCOPY NUMBER
417 ,x_msg_data OUT NOCOPY VARCHAR2
418 ) IS
419 CURSOR c_sys_colors_id IS
420 SELECT color_id, short_name, name, description, prototype_label, perf_sequence,
421 color, user_color, forecast_color, user_forecast_color, numeric_equivalent, user_numeric_equivalent, last_update_date
422 FROM bsc_sys_colors_vl
423 WHERE color_id = p_Bsc_Color_Id;
424
425 CURSOR c_sys_colors_sn IS
426 SELECT color_id, short_name, name, description, prototype_label, perf_sequence,
427 color, user_color, forecast_color, user_forecast_color, numeric_equivalent, user_numeric_equivalent, last_update_date
428 FROM bsc_sys_colors_vl
429 WHERE short_name = p_Bsc_Color_SN;
430 BEGIN
431 FND_MSG_PUB.Initialize;
432 x_return_status := FND_API.G_RET_STS_SUCCESS;
433
434 SAVEPOINT BscColorPvt_RetColor;
435
436 IF(p_Bsc_Color_Id IS NULL AND p_Bsc_Color_SN IS NULL) THEN
437 FND_MESSAGE.SET_NAME('BSC','BSC_NO_COLOR_ID_SN');
438 FND_MSG_PUB.ADD;
439 RAISE FND_API.G_EXC_ERROR;
440 END IF;
441
442 IF(p_Bsc_Color_Id IS NOT NULL) THEN
443 OPEN c_sys_colors_id;
444 FETCH c_sys_colors_id
445 INTO x_Bsc_Color_Rec.color_id
446 ,x_Bsc_Color_Rec.short_name
447 ,x_Bsc_Color_Rec.name
448 ,x_Bsc_Color_Rec.description
449 ,x_Bsc_Color_Rec.prototype_label
450 ,x_Bsc_Color_Rec.perf_sequence
451 ,x_Bsc_Color_Rec.color
452 ,x_Bsc_Color_Rec.user_color
453 ,x_Bsc_Color_Rec.forecast_color
454 ,x_Bsc_Color_Rec.user_forecast_color
455 ,x_Bsc_Color_Rec.numeric_equivalent
456 ,x_Bsc_Color_Rec.user_numeric_equivalent
457 ,x_Bsc_Color_Rec.last_update_date;
458
459 IF c_sys_colors_id%ROWCOUNT = 0 THEN
460 x_return_status := FND_API.G_RET_STS_ERROR;
461 END IF;
462 CLOSE c_sys_colors_id;
463 ELSE
464 OPEN c_sys_colors_sn;
465 FETCH c_sys_colors_sn
466 INTO x_Bsc_Color_Rec.color_id
467 ,x_Bsc_Color_Rec.short_name
468 ,x_Bsc_Color_Rec.name
469 ,x_Bsc_Color_Rec.description
470 ,x_Bsc_Color_Rec.prototype_label
471 ,x_Bsc_Color_Rec.perf_sequence
472 ,x_Bsc_Color_Rec.color
473 ,x_Bsc_Color_Rec.user_color
474 ,x_Bsc_Color_Rec.forecast_color
475 ,x_Bsc_Color_Rec.user_forecast_color
476 ,x_Bsc_Color_Rec.numeric_equivalent
477 ,x_Bsc_Color_Rec.user_numeric_equivalent
478 ,x_Bsc_Color_Rec.last_update_date;
479
480 IF c_sys_colors_sn%ROWCOUNT = 0 THEN
481 x_return_status := FND_API.G_RET_STS_ERROR;
482 END IF;
483 CLOSE c_sys_colors_sn;
484 END IF;
485 EXCEPTION
486 WHEN OTHERS THEN
487 x_return_status := FND_API.G_RET_STS_ERROR;
488 ROLLBACK TO BscColorPvt_RetColor;
489 END Retrieve_Color;
490
491 /************************************************************************************
492 ************************************************************************************/
493 PROCEDURE Load_Color(
494 p_commit IN VARCHAR2:= FND_API.G_FALSE
495 ,p_Bsc_Color_Rec IN BSC_COLOR_PUB.Bsc_Color_Rec
496 ,x_return_status OUT NOCOPY VARCHAR2
497 ,x_msg_count OUT NOCOPY NUMBER
498 ,x_msg_data OUT NOCOPY VARCHAR2
499 ) IS
500 l_Bsc_Color_Rec BSC_COLOR_PUB.Bsc_Color_Rec;
501 l_user_id NUMBER;
502 l_login_id NUMBER;
503 l_count NUMBER;
504 BEGIN
505 FND_MSG_PUB.Initialize;
506 x_return_status := FND_API.G_RET_STS_SUCCESS;
507
508 SAVEPOINT BscColorPvt_LoadColor;
509
510 SELECT COUNT(1)
511 INTO l_count
512 FROM bsc_sys_colors_b
513 WHERE short_name = p_Bsc_Color_Rec.short_name;
514
515 IF (l_count > 0) THEN
516 Load_Translated_Color(p_Bsc_Color_Rec => p_Bsc_Color_Rec
517 ,x_return_status => x_return_status
518 ,x_msg_count => x_msg_count
519 ,x_msg_data => x_msg_data);
520
524 ELSE
521 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
522 RAISE FND_API.G_EXC_ERROR;
523 END IF;
525
526 l_Bsc_Color_Rec := p_Bsc_Color_Rec;
527
528 IF (l_Bsc_Color_Rec.user_color IS NULL) THEN
529 l_Bsc_Color_Rec.user_color := p_Bsc_Color_Rec.color;
530 END IF;
531 IF (l_Bsc_Color_Rec.user_forecast_color IS NULL) THEN
532 l_Bsc_Color_Rec.user_forecast_color := p_Bsc_Color_Rec.forecast_color;
533 END IF;
534 IF (l_Bsc_Color_Rec.user_numeric_equivalent IS NULL) THEN
535 l_Bsc_Color_Rec.user_numeric_equivalent := p_Bsc_Color_Rec.numeric_equivalent;
536 END IF;
537
538 Create_Color( p_Bsc_Color_Rec => l_Bsc_Color_Rec
539 ,x_return_status => x_return_status
540 ,x_msg_count => x_msg_count
541 ,x_msg_data => x_msg_data);
542
543 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
544 RAISE FND_API.G_EXC_ERROR;
545 END IF;
546 END IF;
547
548
549 EXCEPTION
550 WHEN OTHERS THEN
551 x_return_status := FND_API.G_RET_STS_ERROR;
552 ROLLBACK TO BscColorPvt_LoadColor;
553 END Load_Color;
554
555 END BSC_COLOR_PVT;