1 PACKAGE BODY BSC_KPI_GROUP_PVT as
2 /* $Header: BSCVKGPB.pls 120.0 2005/06/01 16:20:37 appldev noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCVKGPB.pls |
10 | |
11 | Creation Date: |
12 | October 22, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | Private Body version. |
19 | This package Creates, Retrieves, Updates, Deletes |
20 | BSC Kpi Group information. |
21 | |
22 | History: |
23 | 04-MAR-2003 PAJOHRI MLS Bug #2721899 |
24 | 1. Modified Update Query for BSC_TAB_IND_GROUPS_TL. |
25 | 2. Changed nvl(<record_used>.Bsc_Language, userenv('LANG')) |
26 | to userenv('LANG') |
27 | 30-Oct-2003 ADEULGAO Fixed Bug#3208420, modified Delete_Kpi_Group to handle |
28 | Bsc_Tab_Id <> -1 condition. |
29 | |
30 | Nov-24 wcano fix bug 3267470 |
31 | 08-JAN-2004 krishan fixed for the bug 3357984 |
32 | 18-MAY-04 adrao Modified PL/SQL records and CRUD to accept SHORT_NAME |
33 +======================================================================================+
34
35
36 */
37 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_KPI_GROUP_PVT';
38 g_db_object varchar2(30) := null;
39
40 PROCEDURE Create_Kpi_Group(
41 p_commit IN VARCHAR2 := FND_API.G_FALSE
42 ,p_Bsc_Kpi_Group_Rec IN BSC_KPI_GROUP_PUB.Bsc_Kpi_Group_Rec
43 ,x_return_status OUT NOCOPY VARCHAR2
44 ,x_msg_count OUT NOCOPY NUMBER
45 ,x_msg_data OUT NOCOPY VARCHAR2
46 ) is
47
48 l_count NUMBER;
49
50 BEGIN
51 FND_MSG_PUB.Initialize;
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53 SAVEPOINT BSCKPIGrpPVT;
54
55 -- This procedure "Creates" a new Kpi Group, and also "Creates" an assignment of a KPI
56 -- group to a Scorecard.
57 -- If Tab id is -1 then new KPI Group, else a new assignment. If new assignment then check
58 -- both tab and groups exist.
59 IF p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id <> -1 THEN
60 -- Check that valid Kpi group id was entered.
61 IF p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id IS NOT NULL THEN
62 SELECT COUNT(1) INTO l_Count
63 FROM BSC_TAB_IND_GROUPS_B
64 WHERE IND_GROUP_ID = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id;
65
66 IF l_count = 0 THEN
67 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KGROUP_ID');
68 FND_MESSAGE.SET_TOKEN('BSC_KGROUP', p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id);
69 FND_MSG_PUB.ADD;
70 RAISE FND_API.G_EXC_ERROR;
71 END IF;
72 ELSE
73 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KGROUP_ID_ENTERED');
74 FND_MESSAGE.SET_TOKEN('BSC_KGROUP', p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id);
75 FND_MSG_PUB.ADD;
76 RAISE FND_API.G_EXC_ERROR;
77 END IF;
78
79 -- Check that valid Tab id was entered.
80 IF p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id IS NOT NULL THEN
81
82 SELECT COUNT(1) INTO l_Count
83 FROM BSC_TABS_B
84 WHERE TAB_ID = p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id;
85
86 IF l_count = 0 THEN
87 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_TAB_ID');
88 FND_MESSAGE.SET_TOKEN('BSC_TAB', p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id);
89 FND_MSG_PUB.ADD;
90 RAISE FND_API.G_EXC_ERROR;
91 END IF;
92 ELSE
93 FND_MESSAGE.SET_NAME('BSC','BSC_NO_TAB_ID_ENTERED');
94 FND_MESSAGE.SET_TOKEN('BSC_KGROUP', p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id);
95 FND_MSG_PUB.ADD;
96 RAISE FND_API.G_EXC_ERROR;
97 END IF;
98 ELSE -- New KPI Group to be added
99 IF p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Short_Name IS NOT NULL THEN
100
101 SELECT COUNT(1) INTO l_Count
102 FROM BSC_TAB_IND_GROUPS_B
103 WHERE SHORT_NAME = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Short_Name;
104
105 -- Need to review if this validation is required, since we are not deleting KPI Group
106 -- and it can have multiple rows
107 -- IF l_Count <> 0 THEN
108 -- FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
109 -- FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.GET_LOOKUP_VALUE('BSC_UI_BUILDER', 'MEASURE_SHORT_NAME'));
110 -- FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Short_Name );
111 -- FND_MSG_PUB.ADD;
112 -- RAISE FND_API.G_EXC_ERROR;
113 -- END IF;
114 END IF;
115 END IF;
116
117 g_db_object := 'BSC_TAB_IND_GROUPS_B';
118
119
120 INSERT INTO BSC_TAB_IND_GROUPS_B( TAB_ID
121 ,CSF_ID
122 ,IND_GROUP_ID
123 ,GROUP_TYPE
124 ,NAME_POSITION
125 ,NAME_JUSTIFICATION
126 ,LEFT_POSITION
127 ,TOP_POSITION
128 ,WIDTH
129 ,HEIGHT
130 ,SHORT_NAME)
131 VALUES( p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id
132 ,p_Bsc_Kpi_Group_Rec.Bsc_Csf_Id
133 ,p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id
134 ,p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Type
135 ,p_Bsc_Kpi_Group_Rec.Bsc_Name_Pos_In_Tab
136 ,p_Bsc_Kpi_Group_Rec.Bsc_Name_Justif_In_Tab
137 ,p_Bsc_Kpi_Group_Rec.Bsc_Left_Position_In_Tab
138 ,p_Bsc_Kpi_Group_Rec.Bsc_Top_Position_In_Tab
139 ,p_Bsc_Kpi_Group_Rec.Bsc_Group_Width
140 ,p_Bsc_Kpi_Group_Rec.Bsc_Group_Height
141 ,p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Short_Name);
142
143 g_db_object := 'BSC_TAB_IND_GROUPS_TL';
144
145 INSERT INTO BSC_TAB_IND_GROUPS_TL( TAB_ID
146 ,CSF_ID
147 ,IND_GROUP_ID
148 ,LANGUAGE
149 ,SOURCE_LANG
150 ,NAME
151 ,HELP
152 ) SELECT
153 p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id,
154 p_Bsc_Kpi_Group_Rec.Bsc_Csf_Id,
155 p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id,
156 L.LANGUAGE_CODE,
157 USERENV('LANG'),
158 p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Name,
159 p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Help
160 FROM FND_LANGUAGES L
161 WHERE L.INSTALLED_FLAG IN ('I', 'B')
162 AND NOT EXISTS
163 (SELECT NULL
164 FROM BSC_TAB_IND_GROUPS_TL T
165 WHERE T.tab_id = p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id
166 AND T.csf_id = p_Bsc_Kpi_Group_Rec.Bsc_Csf_Id
167 AND T.ind_group_id = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id
168 AND T.LANGUAGE = L.LANGUAGE_CODE);
169
170 IF (p_commit = FND_API.G_TRUE) THEN
171 COMMIT;
172 END IF;
173
174 EXCEPTION
175 WHEN FND_API.G_EXC_ERROR THEN
176 ROLLBACK TO BSCKPIGrpPVT;
177 FND_MSG_PUB.Count_And_Get
178 ( p_encoded => FND_API.G_FALSE
179 , p_count => x_msg_count
180 , p_data => x_msg_data
181 );
182 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
183 x_return_status := FND_API.G_RET_STS_ERROR;
184 RAISE;
185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
186 ROLLBACK TO BSCKPIGrpPVT;
187 FND_MSG_PUB.Count_And_Get
188 ( p_encoded => FND_API.G_FALSE
189 , p_count => x_msg_count
190 , p_data => x_msg_data
191 );
192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
193 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
194 RAISE;
195 WHEN NO_DATA_FOUND THEN
196 ROLLBACK TO BSCKPIGrpPVT;
197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198 if (SQLCODE = -01400) then
199 FND_MESSAGE.SET_NAME('BSC','BSC_TABLE_NULL_VALUE');
200 FND_MESSAGE.SET_TOKEN('BSC_OBJECT', 'BSC_KPI_GROUP_PVT.Create_Kpi_Group');
201 FND_MSG_PUB.ADD;
202 RAISE FND_API.G_EXC_ERROR;
203 end if;
204 IF (x_msg_data IS NOT NULL) THEN
205 x_msg_data := x_msg_data||' -> BSC_KPI_GROUP_PVT.Create_Kpi_Group ';
206 ELSE
207 x_msg_data := SQLERRM||' at BSC_KPI_GROUP_PVT.Create_Kpi_Group ';
208 END IF;
209 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
210 RAISE;
211 WHEN OTHERS THEN
212 ROLLBACK TO BSCKPIGrpPVT;
213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
214 IF (x_msg_data IS NOT NULL) THEN
215 x_msg_data := x_msg_data||' -> BSC_KPI_GROUP_PVT.Create_Kpi_Group ';
216 ELSE
217 x_msg_data := SQLERRM||' at BSC_KPI_GROUP_PVT.Create_Kpi_Group ';
218 END IF;
219 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
220 RAISE;
221 end Create_Kpi_Group;
222
223 /************************************************************************************
224 ************************************************************************************/
225
226 procedure Retrieve_Kpi_Group(
227 p_commit IN varchar2 := FND_API.G_FALSE
228 ,p_Bsc_Kpi_Group_Rec IN BSC_KPI_GROUP_PUB.Bsc_Kpi_Group_Rec
229 ,x_Bsc_Kpi_Group_Rec IN OUT NOCOPY BSC_KPI_GROUP_PUB.Bsc_Kpi_Group_Rec
230 ,x_return_status OUT NOCOPY VARCHAR2
231 ,x_msg_count OUT NOCOPY NUMBER
232 ,x_msg_data OUT NOCOPY VARCHAR2
233 ) is
234
235 begin
236 FND_MSG_PUB.Initialize;
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238
239 g_db_object := 'Retrieve_Kpi_Group';
240
241 SELECT DISTINCT A.TAB_ID
242 ,A.CSF_ID
243 ,A.GROUP_TYPE
244 ,A.NAME_POSITION
245 ,A.NAME_JUSTIFICATION
246 ,A.LEFT_POSITION
247 ,A.TOP_POSITION
248 ,A.WIDTH
249 ,A.HEIGHT
250 ,A.SHORT_NAME
251 ,B.NAME
252 ,B.HELP
253 ,B.SOURCE_LANG
254 INTO x_Bsc_Kpi_Group_Rec.Bsc_Tab_Id
255 ,x_Bsc_Kpi_Group_Rec.Bsc_Csf_Id
256 ,x_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Type
257 ,x_Bsc_Kpi_Group_Rec.Bsc_Name_Pos_In_Tab
258 ,x_Bsc_Kpi_Group_Rec.Bsc_Name_Justif_In_Tab
259 ,x_Bsc_Kpi_Group_Rec.Bsc_Left_Position_In_Tab
260 ,x_Bsc_Kpi_Group_Rec.Bsc_Top_Position_In_Tab
261 ,x_Bsc_Kpi_Group_Rec.Bsc_Group_Width
262 ,x_Bsc_Kpi_Group_Rec.Bsc_Group_Height
263 ,x_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Short_Name
264 ,x_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Name
265 ,x_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Help
266 ,x_Bsc_Kpi_Group_Rec.Bsc_Source_Language
267 FROM BSC_TAB_IND_GROUPS_B a
268 ,BSC_TAB_IND_GROUPS_TL b
269 WHERE a.ind_group_id = b.ind_group_id
270 AND b.ind_group_id = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id
271 AND b.language = USERENV('LANG')
272 AND a.tab_id = -1
273 AND a.tab_id = b.tab_id;
274
275 IF (p_commit = FND_API.G_TRUE) THEN
276 COMMIT;
277 END IF;
278
279 --BSC_DEBUG.PUT_LINE(' -- End BSC_KPI_GROUP_PVT.Retrieve_Kpi_Group' );
280
281
282 EXCEPTION
283 WHEN FND_API.G_EXC_ERROR THEN
284 FND_MSG_PUB.Count_And_Get
285 ( p_encoded => FND_API.G_FALSE
286 , p_count => x_msg_count
287 , p_data => x_msg_data
288 );
289 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
290 x_return_status := FND_API.G_RET_STS_ERROR;
291 RAISE;
292 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
293 FND_MSG_PUB.Count_And_Get
294 ( p_encoded => FND_API.G_FALSE
295 , p_count => x_msg_count
296 , p_data => x_msg_data
297 );
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
299 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
300 RAISE;
301 WHEN NO_DATA_FOUND THEN
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303 IF (x_msg_data IS NOT NULL) THEN
304 x_msg_data := x_msg_data||' -> BSC_KPI_GROUP_PVT.Retrieve_Kpi_Group ';
305 ELSE
306 x_msg_data := SQLERRM||' at BSC_KPI_GROUP_PVT.Retrieve_Kpi_Group ';
307 END IF;
308 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
309 RAISE;
310 WHEN OTHERS THEN
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 IF (x_msg_data IS NOT NULL) THEN
313 x_msg_data := x_msg_data||' -> BSC_KPI_GROUP_PVT.Retrieve_Kpi_Group ';
314 ELSE
315 x_msg_data := SQLERRM||' at BSC_KPI_GROUP_PVT.Retrieve_Kpi_Group ';
316 END IF;
317 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
318 RAISE;
319 end Retrieve_Kpi_Group;
320
321 /************************************************************************************
322 ************************************************************************************/
323
324 procedure Update_Kpi_Group(
325 p_commit IN varchar2 := FND_API.G_FALSE
326 ,p_Bsc_Kpi_Group_Rec IN BSC_KPI_GROUP_PUB.Bsc_Kpi_Group_Rec
327 ,x_return_status OUT NOCOPY varchar2
328 ,x_msg_count OUT NOCOPY number
329 ,x_msg_data OUT NOCOPY varchar2
330 ) is
331
332 l_Bsc_Kpi_Group_Rec BSC_KPI_GROUP_PUB.Bsc_Kpi_Group_Rec;
333
334 l_count number;
335 l_name_count number;
336 l_update_TL number := 0;
337
338 begin
339 FND_MSG_PUB.Initialize;
340 x_return_status := FND_API.G_RET_STS_SUCCESS;
341 SAVEPOINT BSCKPIUptPVT;
342 --BSC_DEBUG.PUT_LINE(' -- Begin BSC_KPI_GROUP_PVT.Update_Kpi_Group' );
343
344 -- Check that valid Kpi group id was entered.
345 if p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id is not null then
346
347 SELECT COUNT(1) INTO l_Count
348 FROM BSC_TAB_IND_GROUPS_B
349 WHERE ind_group_id = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id;
350
351 if l_count = 0 then
352 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KGROUP_ID');
353 FND_MESSAGE.SET_TOKEN('BSC_KGROUP', p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id);
354 FND_MSG_PUB.ADD;
355 RAISE FND_API.G_EXC_ERROR;
356 end if;
357 else
358 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KGROUP_ID_ENTERED');
359 FND_MESSAGE.SET_TOKEN('BSC_KGROUP', p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id);
360 FND_MSG_PUB.ADD;
361 RAISE FND_API.G_EXC_ERROR;
362 end if;
363
364 --BSC_DEBUG.PUT_LINE(' BSC_KPI_GROUP_PVT.Update_Kpi_Group - Flag 1' );
365
366 select count(ind_group_id)
367 into l_name_count
368 from BSC_TAB_IND_GROUPS_TL
369 where name = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Name
370 and ind_group_id <> p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id
371 and tab_id = -1;
372
373 --BSC_DEBUG.PUT_LINE(' BSC_KPI_GROUP_PVT.Update_Kpi_Group - Flag 2 ' );
374
375 if l_name_count <> 0 then
376 FND_MESSAGE.SET_NAME('BSC','BSC_KGROUP_NAME_EXISTS');
377 FND_MESSAGE.SET_TOKEN('BSC_KGROUP', p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Name);
378 FND_MSG_PUB.ADD;
379 RAISE FND_API.G_EXC_ERROR;
380 end if;
381
382 --BSC_DEBUG.PUT_LINE(' BSC_KPI_GROUP_PVT.Update_Kpi_Group - Flag 3 ' );
383
384 -- Check that valid Tab id was entered, only if tab id is not -1.
385 IF p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id <> -1 THEN
386 IF p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id IS NOT NULL THEN
387
388 SELECT COUNT(1) INTO l_Count
389 FROM BSC_TABS_B
390 WHERE TAB_ID = p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id;
391
392 if l_count = 0 then
393 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_TAB_ID');
394 FND_MESSAGE.SET_TOKEN('BSC_TAB', p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id);
395 FND_MSG_PUB.ADD;
396 RAISE FND_API.G_EXC_ERROR;
397 end if;
398 else
399 FND_MESSAGE.SET_NAME('BSC','BSC_NO_TAB_ID_ENTERED');
400 FND_MESSAGE.SET_TOKEN('BSC_KGROUP', p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id);
401 FND_MSG_PUB.ADD;
402 RAISE FND_API.G_EXC_ERROR;
403 end if;
404 end if;
405
406 -- Not all values will be passed. We need to make sure values not passed are not
407 -- changed by procedure, therefore we get what is there before we do any updates.
408 Retrieve_Kpi_Group( p_commit
409 ,p_Bsc_Kpi_Group_Rec
410 ,l_Bsc_Kpi_Group_Rec
411 ,x_return_status
412 ,x_msg_count
413 ,x_msg_data);
414
415 -- update LOCAL language ,source language and Kpi Group Id values with PASSED values.
416 l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id := p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id;
417 l_Bsc_Kpi_Group_Rec.Bsc_Language := p_Bsc_Kpi_Group_Rec.Bsc_Language;
418 l_Bsc_Kpi_Group_Rec.Bsc_Source_Language := p_Bsc_Kpi_Group_Rec.Bsc_Source_Language;
419 -- Copy PASSED Record values into LOCAL Record values for the PASSED Record values
420 -- which are NOT NULL.
421 if p_Bsc_Kpi_Group_Rec.Bsc_Csf_Id is not null then
422 l_Bsc_Kpi_Group_Rec.Bsc_Csf_Id := p_Bsc_Kpi_Group_Rec.Bsc_Csf_Id;
423 end if;
424 if p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id is not null then
425 l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id := p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id;
426 end if;
427 if p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Type is not null then
428 l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Type := p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Type;
429 end if;
430 if p_Bsc_Kpi_Group_Rec.Bsc_Name_Pos_In_Tab is not null then
431 l_Bsc_Kpi_Group_Rec.Bsc_Name_Pos_In_Tab := p_Bsc_Kpi_Group_Rec.Bsc_Name_Pos_In_Tab;
432 end if;
433 if p_Bsc_Kpi_Group_Rec.Bsc_Name_Justif_In_Tab is not null then
434 l_Bsc_Kpi_Group_Rec.Bsc_Name_Justif_In_Tab := p_Bsc_Kpi_Group_Rec.Bsc_Name_Justif_In_Tab;
435 end if;
436 if p_Bsc_Kpi_Group_Rec.Bsc_Left_Position_In_Tab is not null then
437 l_Bsc_Kpi_Group_Rec.Bsc_Left_Position_In_Tab := p_Bsc_Kpi_Group_Rec.Bsc_Left_Position_In_Tab;
438 end if;
439 if p_Bsc_Kpi_Group_Rec.Bsc_Top_Position_In_Tab is not null then
440 l_Bsc_Kpi_Group_Rec.Bsc_Top_Position_In_Tab := p_Bsc_Kpi_Group_Rec.Bsc_Top_Position_In_Tab;
441 end if;
442 if p_Bsc_Kpi_Group_Rec.Bsc_Group_Width is not null
443 and p_Bsc_Kpi_Group_Rec.Bsc_Group_Width > 150 then /*added to fixed bug 2650624 */
444 l_Bsc_Kpi_Group_Rec.Bsc_Group_Width := p_Bsc_Kpi_Group_Rec.Bsc_Group_Width;
445 end if;
446 if p_Bsc_Kpi_Group_Rec.Bsc_Group_Height is not null
447 and p_Bsc_Kpi_Group_Rec.Bsc_Group_Width > 150 then /*added to fixed bug 2650624 */
448 l_Bsc_Kpi_Group_Rec.Bsc_Group_Height := p_Bsc_Kpi_Group_Rec.Bsc_Group_Height;
449 end if;
450 if p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Name is not null then
451 l_update_TL := 1;
452 l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Name := p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Name;
453 end if;
454 if p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Help is not null then
455 l_update_TL := 1;
456 l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Help := p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Help;
457 end if;
458
459 -- Check to see if combination Tab Id and Kpi Group Id passed already exists. If it does not then
460 -- need to create entry for this group with this tab.
461 if p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id is not null and p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id <> -1 then
462 select count(*)
463 into l_count
464 from BSC_TAB_IND_GROUPS_B
465 where tab_id = p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id
466 and ind_group_id = l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id;
467 end if;
468 --BSC_DEBUG.PUT_LINE('l_count = ' || l_count );
469
470 if l_count = 0 then
471 l_Bsc_Kpi_Group_Rec.Bsc_Tab_Id := p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id;
472 Create_Kpi_Group( p_commit
473 ,l_Bsc_Kpi_Group_Rec
474 ,x_return_status
475 ,x_msg_count
476 ,x_msg_data);
477 end if;
478
479 update BSC_TAB_IND_GROUPS_B
480 set csf_id = l_Bsc_Kpi_Group_Rec.Bsc_Csf_Id
481 ,group_type = l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Type
482 ,name_position = l_Bsc_Kpi_Group_Rec.Bsc_Name_Pos_In_Tab
483 ,name_justification = l_Bsc_Kpi_Group_Rec.Bsc_Name_Justif_In_Tab
484 where ind_group_id = l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id;
485
486 if l_update_TL = 1 then
487 --BSC_DEBUG.PUT_LINE(' BSC_KPI_GROUP_PVT.Update_Kpi_Group - Flag 4.5 ' );
488 update BSC_TAB_IND_GROUPS_TL
489 set csf_id = l_Bsc_Kpi_Group_Rec.Bsc_Csf_Id
490 ,name = l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Name
491 ,help = l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Help
492 ,source_lang = userenv('LANG')
493 where ind_group_id = l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id
494 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
495 end if;
496
497 -- set the Tab Id to that passed.
498 l_Bsc_Kpi_Group_Rec.Bsc_Tab_Id := p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id;
499
500 -- The previous UPDATES are for the group in all tabs. The following
501 -- updates are applied to individual tabs.
502
503 update BSC_TAB_IND_GROUPS_B
504 set left_position = l_Bsc_Kpi_Group_Rec.Bsc_Left_Position_In_Tab
505 ,top_position = l_Bsc_Kpi_Group_Rec.Bsc_Top_Position_In_Tab
506 ,width = l_Bsc_Kpi_Group_Rec.Bsc_Group_Width
507 ,height = l_Bsc_Kpi_Group_Rec.Bsc_Group_Height
508 where ind_group_id = l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id
509 and (tab_id = l_Bsc_Kpi_Group_Rec.Bsc_Tab_Id or tab_id = -1);
510
511
512 if (p_commit = FND_API.G_TRUE) then
513 commit;
514 end if;
515
516 --BSC_DEBUG.PUT_LINE(' -- End BSC_KPI_GROUP_PVT.Update_Kpi_Group' );
517
518 EXCEPTION
519 WHEN FND_API.G_EXC_ERROR THEN
520 ROLLBACK TO BSCKPIUptPVT;
521 FND_MSG_PUB.Count_And_Get
522 ( p_encoded => FND_API.G_FALSE
523 , p_count => x_msg_count
524 , p_data => x_msg_data
525 );
526 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
527 x_return_status := FND_API.G_RET_STS_ERROR;
528 RAISE;
529 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
530 ROLLBACK TO BSCKPIUptPVT;
531 FND_MSG_PUB.Count_And_Get
532 ( p_encoded => FND_API.G_FALSE
533 , p_count => x_msg_count
534 , p_data => x_msg_data
535 );
536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
537 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
538 RAISE;
539 WHEN NO_DATA_FOUND THEN
540 ROLLBACK TO BSCKPIUptPVT;
541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
542 IF (x_msg_data IS NOT NULL) THEN
543 x_msg_data := x_msg_data||' -> BSC_KPI_GROUP_PVT.Update_Kpi_Group ';
544 ELSE
545 x_msg_data := SQLERRM||' at BSC_KPI_GROUP_PVT.Update_Kpi_Group ';
546 END IF;
547 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
548 RAISE;
549 WHEN OTHERS THEN
550 ROLLBACK TO BSCKPIUptPVT;
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
552 IF (x_msg_data IS NOT NULL) THEN
553 x_msg_data := x_msg_data||' -> BSC_KPI_GROUP_PVT.Update_Kpi_Group ';
554 ELSE
555 x_msg_data := SQLERRM||' at BSC_KPI_GROUP_PVT.Update_Kpi_Group ';
556 END IF;
557 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
558 RAISE;
559 end Update_Kpi_Group;
560
561 /************************************************************************************
562 ************************************************************************************/
563
564 procedure Delete_Kpi_Group(
565 p_commit IN VARCHAR2 := FND_API.G_FALSE
566 ,p_Bsc_Kpi_Group_Rec IN BSC_KPI_GROUP_PUB.Bsc_Kpi_Group_Rec
567 ,x_return_status OUT NOCOPY varchar2
568 ,x_msg_count OUT NOCOPY number
569 ,x_msg_data OUT NOCOPY varchar2
570 ) is
571
572 l_count number;
573 l_count_kpi number;
574
575 begin
576 FND_MSG_PUB.Initialize;
577 x_return_status := FND_API.G_RET_STS_SUCCESS;
578 SAVEPOINT BSCKPIDeletePVT;
579 SAVEPOINT BSCKPIDelPVT;
580 -- Check that Group id is valid.
581 if p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id is not null then
582
583 SELECT COUNT(1) INTO l_Count
584 FROM BSC_TAB_IND_GROUPS_B
585 WHERE IND_GROUP_ID = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id;
586
587 if l_count = 0 then
588 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KGROUP_ID');
589 FND_MESSAGE.SET_TOKEN('BSC_KGROUP', p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id);
590 FND_MSG_PUB.ADD;
591 RAISE FND_API.G_EXC_ERROR;
592 end if;
593 else
594 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KGROUP_ID_ENTERED');
595 FND_MESSAGE.SET_TOKEN('BSC_KGROUP', p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id);
596 FND_MSG_PUB.ADD;
597 RAISE FND_API.G_EXC_ERROR;
598 end if;
599
600 -- Need to determine whether deletion is global, or just from a Tab.
601
602 if ((p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id is not null) and (p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id <> -1)) then
603
604 select count(b.indicator)
605 into l_count_kpi
606 from bsc_kpis_b a, bsc_tab_indicators b
607 where a.ind_group_id = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id
608 and a.indicator = b.indicator
609 and b.tab_id = p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id;
610
611 if l_count_kpi = 0 then
612 delete from BSC_TAB_IND_GROUPS_B
613 where ind_group_id = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id
614 and tab_id = p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id;
615
616 delete from BSC_TAB_IND_GROUPS_TL
617 where ind_group_id = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id
618 and tab_id = p_Bsc_Kpi_Group_Rec.Bsc_Tab_Id;
619 else
620 FND_MSG_PUB.Initialize;
621 FND_MESSAGE.SET_NAME('BSC','BSC_CANNOT_DELETE_KGROUP');
622 FND_MESSAGE.SET_TOKEN('BSC_KGROUP', p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Name);
623 FND_MSG_PUB.ADD;
624 RAISE FND_API.G_EXC_ERROR;
625 end if;
626
627
628 else
629
630 -- Before deleting Kpi Group check that there are no KPIs assigned to it.
631 select count(indicator)
632 into l_count
633 from BSC_KPIS_B
634 where ind_group_id = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id
635 and PROTOTYPE_FLAG <> BSC_KPI_PUB.DELETE_KPI_FLAG; -- Added to fix bug 3267470
636
637 if l_count = 0 then
638 delete from BSC_TAB_IND_GROUPS_B
639 where ind_group_id = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id;
640
641 delete from BSC_TAB_IND_GROUPS_TL
642 where ind_group_id = p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id;
643 else
644 FND_MSG_PUB.Initialize;
645 FND_MESSAGE.SET_NAME('BSC','BSC_CANNOT_DELETE_KGROUP');
646 FND_MESSAGE.SET_TOKEN('BSC_KGROUP', p_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Name);
647 FND_MSG_PUB.ADD;
648 RAISE FND_API.G_EXC_ERROR;
649 end if;
650
651 end if;
652
653 if (p_commit = FND_API.G_TRUE) then
654 commit;
655 end if;
656
657
658 EXCEPTION
659 WHEN FND_API.G_EXC_ERROR THEN
660 ROLLBACK TO BSCKPIDeletePVT;
661 FND_MSG_PUB.Count_And_Get
662 ( p_encoded => FND_API.G_FALSE
663 , p_count => x_msg_count
664 , p_data => x_msg_data
665 );
666 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
667 x_return_status := FND_API.G_RET_STS_ERROR;
668 RAISE;
669 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
670 ROLLBACK TO BSCKPIDeletePVT;
671 FND_MSG_PUB.Count_And_Get
672 ( p_encoded => FND_API.G_FALSE
673 , p_count => x_msg_count
674 , p_data => x_msg_data
675 );
676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
677 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
678 RAISE;
679 WHEN NO_DATA_FOUND THEN
680 ROLLBACK TO BSCKPIDeletePVT;
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 IF (x_msg_data IS NOT NULL) THEN
683 x_msg_data := x_msg_data||' -> BSC_KPI_GROUP_PVT.Delete_Kpi_Group ';
684 ELSE
685 x_msg_data := SQLERRM||' at BSC_KPI_GROUP_PVT.Delete_Kpi_Group ';
686 END IF;
687 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
688 RAISE;
689 WHEN OTHERS THEN
690 ROLLBACK TO BSCKPIDeletePVT;
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
692 IF (x_msg_data IS NOT NULL) THEN
693 x_msg_data := x_msg_data||' -> BSC_KPI_GROUP_PVT.Delete_Kpi_Group ';
694 ELSE
695 x_msg_data := SQLERRM||' at BSC_KPI_GROUP_PVT.Delete_Kpi_Group ';
696 END IF;
697 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
698 RAISE;
699
700 end Delete_Kpi_Group;
701
702 /************************************************************************************
703 ************************************************************************************/
704
705 end BSC_KPI_GROUP_PVT;
[Home] [Help]