[Home] [Help]
PACKAGE BODY: APPS.BIS_MEASURE_PVT
Source
1 PACKAGE BODY BIS_MEASURE_PVT AS
2 /* $Header: BISVMEAB.pls 120.3 2006/06/27 06:24:46 akoduri ship $ */
3 --
4 /*
5 REM +=======================================================================+
6 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
7 REM | All rights reserved. |
8 REM +=======================================================================+
9 REM | FILENAME |
10 REM | BISVMEAS.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Public API for creating and managing Performance Measurements
14 REM |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | 28-NOV-98 irchen Creation
19 REM | 15-OCT-2001 MAHRAO Fix for 1850860
20 REM | 26-JUL-2002 rchandra Fixed for enh 2440739 |
21 REM | 13-NOV-2002 SASHAIK Fixed for bug 2664898
22 REM | 23-JAN-03 sugopal For having different local variables for IN and OUT |
23 REM | parameters (bug#2758428) |
24 REM | 30-JAN-03 sugopal FND_API.G_MISS_xxx should not be used in |
25 REM | initialization or declaration (bug#2774644) |
26 REM | 13-FEB-03 sashaik 2784713 Measure update fix. |
27 REM | 11-APR-03 sugopal Not possible to update an existing value with null |
28 REM | in the Create/Update performance measures page - |
29 REM | modified UpdateMeasureRec for bug#2869324 |
30 REM | 23-APR-03 mdamle PMD - Measure Definer Support |
31 REM | 18-JUN-03 mdamle Fixed bug in Get_Measure_Id_From_Name |
32 REM | 24-JUN-2003 rchandra leap frog PMD Changes to verssion 115.68 |
33 REM | 24-JUN-2003 rchandra leap frog 115.70 to support dataset_id which |
34 REM | already has been coded for bug 3004651 |
35 REM | 26-JUN-2003 rchandra populated dataset_id into measure_rec in the |
36 REM | API retrieve_measure for bug 3004651 |
37 REM | 04-JUL-2003 arhegde bug# 2975949 If enable link is null, then insert/ |
38 REM | update 'N' instead |
39 REM | 14-JUL-2003 jxyu Fixed for bug#3037200. Remove the additional check |
40 REM | in Delete_Measure() for PMD |
41 REM | 18-JUL-2003 mdamle Added check for duplicate name in update_measure |
42 REM | Added check for enable_link |
43 REM | Added check for data source column |
44 REM | 01-Aug-2003 mdamle Bug#3055812 - Chk for duplicate name after trimming|
45 REM | 06-Aug-2003 mdamle Fixed isSourceColumnMappedAlready |
46 REM | 20-Aug-2003 mdamle Bug#3102928 - Added check for actual_data_source |
47 REM | 10-SEP-2003 mahrao Added check for matching dimensions in update_measure|
48 REM | procedure which will be called when measure is |
49 REM | uploaded through an ldt. |
50 REM | 25-SEP-2003 mdamle Bug#3160325 - Sync up measures for all installed |
51 REM | languages |
52 REM | 29-SEP-2003 adrao Bug#3160325 - Sync up measures for all installed |
53 REM | source languages |
54 REM | 12-NOV-2003 smargand added new function to determine whether the |
55 REM | given indicator is customized, |
56 REM | added enable column to the affected views, |
57 REM | 24-FEB-04 KYADAMAK Bug #3439942 space not allowed for PMF |
58 REM | Measures |
59 REM | 08-APR-04 ankgoel Modified for bug#3557236 |
60 REM | 27-MAY-04 ankgoel Modified for bug#3610655 |
61 REM | 03-JUN-04 ankgoel Modified for bug#3583357. Added procedure call |
62 REM | for re-sequencing dimensions in |
63 REM | bis_indicator_dimensions using the dim level |
64 REM | order in bis_target_levels. |
65 REM | 27-JUL-04 sawu Resolved WHO column info based on p_owner |
66 REM | 02-SEP-04 sawu Bug#3859267: added isColumnMappedAlready(), |
67 REM | isSourceColumnMappedAlready() and |
68 REM | isCompareColumnMappedAlready() |
69 REM | 13-SEP-04 sawu Bug#3852077: added api IsDimensionUpdatable() |
70 REM | 29-SEP-04 ankgoel Added WHO columns in Rec for Bug#3891748 |
71 REM | 01-OCT-04 ankgoel Bug#3922308 - The new and old set of dimensions |
72 REM | for a measure should be exactly same. Reverted |
73 REM | fix done for Bug#3852077. Also, got the dimensions|
74 REM | from existing measure record in case of re-order. |
75 REM | 20-Dec-04 sawu Bug#4045278: Modified update_measure to populate |
76 REM | last_update_date from l_Measure_Rec. Removed line |
77 REM | that updated last_update_date in api |
78 REM | Translate_Measure_by_lang to preserve lud integrity. |
79 REM | Overloaded create_measure() also. |
80 REM | 27-Dec-04 rpenneru Bug#4080204: Modifed Create_Measure(), |
81 REM | Update_Measure() to populate functional Area Name |
82 REM | to Measure extensions if the measure is uploaded. |
83 REM | 31-Jan-2005 rpenneru Modified for #4073262, BIS_MEASURES_EXTENSION_TL |
84 REM | Name and Description should not be updated, if the measure|
85 REM | is uploaded from BISPMFLD.lct |
86 REM | 09-FEB-04 skchoudh Enh#4141738 Added Functiona Area Combobox to MD |
87 REM | 21-MAR-05 ankagarw bug#4235732 - changing count(*) to count(1) |
88 REM | 22-APR-2005 akoduri Enhancement#3865711 -- Obsolete Seeded Objects |
89 REM | 03-MAY-2005 akoduri Enh #4268374 -- Weighted Average Measures |
90 REM | 19-MAY-2005 visuri GSCC Issues bug 4363854 |
91 REM | 19-JUL-2005 rpenneru bug#4447273- bis_measures_extension is corrupted|
92 REM | when the ldt file is not having the FA short name |
93 REM | 20-SEP-2005 akoduri bug#4607348 - Obsoletion of measures is not |
94 REM | changing the last_update_date and last_updated_by |
95 REM | 16-JUN-2006 akoduri bug#5286873 Error is not shown in data source |
96 REM | mapping page in non-US sessions |
97 REM +=======================================================================+
98 */
99
100 --
101 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_MEASURE_PVT';
102 --
103
104 PROCEDURE Update_Measure_Rec_Total -- 2664898
105 ( p_Measure_Rec_orig IN BIS_MEASURE_PUB.Measure_Rec_Type
106 , p_Measure_Rec_new IN BIS_MEASURE_PUB.Measure_Rec_Type
107 , x_Measure_Rec OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
108 );
109
110 PROCEDURE UpdateMeasureRecNoDim -- 2664898
111 ( p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
112 , p_Measure_Rec1 IN BIS_MEASURE_PUB.Measure_Rec_Type
113 , x_Measure_Rec OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
114 );
115
116 PROCEDURE Update_Measure_Rec_Dims -- 2664898
117 ( p_Measure_Rec_orig IN BIS_MEASURE_PUB.Measure_Rec_Type
118 , p_Measure_Rec_new IN BIS_MEASURE_PUB.Measure_Rec_Type
119 , x_Measure_Rec OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
120 );
121
122 PROCEDURE GetOriginalDimensions -- 3922308
123 ( p_Measure_Rec_Orig IN BIS_MEASURE_PUB.Measure_Rec_Type
124 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
125 , x_Measure_Rec_Overlap OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
126 );
127
128 FUNCTION get_dimension_ID -- 2664898
129 ( p_dim_id IN NUMBER )
130 RETURN NUMBER;
131
132 PROCEDURE ArrangeDimensions -- Added for 2784713
133 ( p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
134 , x_Measure_Rec OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
135 );
136
137 FUNCTION return_value_if_not_missing -- 2784713
138 ( p_number IN NUMBER )
139 RETURN NUMBER;
140
141 FUNCTION return_value_if_not_missing -- 2784713
142 ( p_varchar2 IN VARCHAR2 )
143 RETURN VARCHAR2;
144
145 FUNCTION isColumnMappedAlready(
146 p_region_code IN Ak_Region_Items.REGION_CODE%TYPE
147 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type
148 ,p_attribute_code IN Ak_Region_Items.ATTRIBUTE_CODE%Type
149 ,p_attribute_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type
150 ,x_measure_short_name OUT NOCOPY Bisbv_Performance_Measures.MEASURE_SHORT_NAME%TYPE
151 ,x_measure_name OUT NOCOPY Bisbv_Performance_Measures.MEASURE_NAME%TYPE
152 ) RETURN boolean;
153
154 FUNCTION isCompareColumnMappedAlready(
155 p_Measure_rec IN BIS_MEASURE_PUB.MEASURE_REC_TYPE
156 ,x_measure_name OUT NOCOPY Bisbv_Performance_Measures.MEASURE_NAME%TYPE
157 ) RETURN boolean;
158
159 -- mdamle 07/18/2003 - Check if measure is being mapped to a source
160 -- that's already mapped to another measure.
161 FUNCTION isSourceColumnMappedAlready(
162 p_Measure_rec IN BIS_MEASURE_PUB.MEASURE_REC_TYPE
163 ,x_measure_name OUT NOCOPY Bisbv_Performance_Measures.MEASURE_NAME%TYPE
164 ) RETURN boolean;
165
166
167 -- rpenneru 12/27/2004 for enh#4080204
168 PROCEDURE Load_Measure_Extension
169 ( p_api_version IN NUMBER
170 , p_commit IN VARCHAR2 := FND_API.G_FALSE
171 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
172 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
173 , x_return_status OUT NOCOPY VARCHAR2
174 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
175 );
176
177
178 FUNCTION Get_Measure_Id_From_Short_Name
179 ( p_measure_rec IN BIS_MEASURE_PUB.Measure_Rec_Type
180 ) RETURN NUMBER
181 IS
182 -- mdamle 08/01/2003 - Chk for duplicate name after trimming
183 -- mdamle 08/04/2003 - Added case insensitive check
184 cursor short_name_cursor IS
185 select indicator_id
186 from bis_indicators
187 where short_name = p_measure_rec.Measure_Short_name;
188 l_dummy number;
189 BEGIN
190
191 open short_name_cursor;
192 fetch short_name_cursor into l_dummy;
193 if (short_name_cursor%NOTFOUND) then
194 close short_name_cursor;
195 return NULL;
196 end if;
197 close short_name_cursor;
198
199 return l_dummy;
200
201 --EXCEPTION
202 -- WHEN OTHERS THEN
203 -- NULL;
204
205 END Get_Measure_Id_From_Short_Name;
206 -- Fix for 2309894 starts here
207 FUNCTION Get_Measure_Id_From_Name
208 ( p_measure_rec IN BIS_MEASURE_PUB.Measure_Rec_Type
209 ) RETURN NUMBER
210 IS
211 -- mdamle 07/18/2003 - Changed like to =
212 -- Values may have % sign in them
213 -- mdamle 08/01/2003 - Chk for duplicate name after trimming
214 -- mdamle 08/04/2003 - Added case insensitive check
215 cursor name_cursor IS
216 select indicator_id
217 from bis_indicators_vl
218 where name = p_measure_rec.Measure_name;
219
220 l_dummy number;
221 BEGIN
222
223 open name_cursor;
224 fetch name_cursor into l_dummy;
225 if (name_cursor%NOTFOUND) then
226 close name_cursor;
227 return NULL;
228 end if;
229 close name_cursor;
230
231 return l_dummy;
232
233 --EXCEPTION
234 -- WHEN OTHERS THEN
235 -- NULL;
236
237 END Get_Measure_Id_From_Name;
238 --
239
240 -- ankgoel: bug#3922308
241 -- Retrieves the original set of dimensions and populates the new measure rec
242 PROCEDURE GetOriginalDimensions (
243 p_Measure_Rec_Orig IN BIS_MEASURE_PUB.Measure_Rec_Type
244 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
245 , x_Measure_Rec_Overlap OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
246 )
247 IS
248 BEGIN
249 x_Measure_Rec_Overlap := p_Measure_Rec;
250
251 x_Measure_Rec_Overlap.Dimension1_ID := return_value_if_not_missing
252 ( p_number => p_Measure_Rec_Orig.Dimension1_ID );
253 x_Measure_Rec_Overlap.Dimension1_Short_Name := return_value_if_not_missing
254 ( p_varchar2 => p_Measure_Rec_Orig.Dimension1_Short_Name );
255 x_Measure_Rec_Overlap.Dimension1_Name := return_value_if_not_missing
256 ( p_varchar2 => p_Measure_Rec_Orig.Dimension1_Name );
257
258 x_Measure_Rec_Overlap.Dimension2_ID := return_value_if_not_missing
259 ( p_number => p_Measure_Rec_Orig.Dimension2_ID );
260 x_Measure_Rec_Overlap.Dimension2_Short_Name := return_value_if_not_missing
261 ( p_varchar2 => p_Measure_Rec_Orig.Dimension2_Short_Name );
262 x_Measure_Rec_Overlap.Dimension2_Name := return_value_if_not_missing
263 ( p_varchar2 => p_Measure_Rec_Orig.Dimension2_Name );
264
265 x_Measure_Rec_Overlap.Dimension3_ID := return_value_if_not_missing
266 ( p_number => p_Measure_Rec_Orig.Dimension3_ID );
267 x_Measure_Rec_Overlap.Dimension3_Short_Name := return_value_if_not_missing
268 ( p_varchar2 => p_Measure_Rec_Orig.Dimension3_Short_Name );
269 x_Measure_Rec_Overlap.Dimension3_Name := return_value_if_not_missing
270 ( p_varchar2 => p_Measure_Rec_Orig.Dimension3_Name );
271
272 x_Measure_Rec_Overlap.Dimension4_ID := return_value_if_not_missing
273 ( p_number => p_Measure_Rec_Orig.Dimension4_ID );
274 x_Measure_Rec_Overlap.Dimension4_Short_Name := return_value_if_not_missing
275 ( p_varchar2 => p_Measure_Rec_Orig.Dimension4_Short_Name );
276 x_Measure_Rec_Overlap.Dimension4_Name := return_value_if_not_missing
277 ( p_varchar2 => p_Measure_Rec_Orig.Dimension4_Name );
278
279 x_Measure_Rec_Overlap.Dimension5_ID := return_value_if_not_missing
280 ( p_number => p_Measure_Rec_Orig.Dimension5_ID );
281 x_Measure_Rec_Overlap.Dimension5_Short_Name := return_value_if_not_missing
282 ( p_varchar2 => p_Measure_Rec_Orig.Dimension5_Short_Name );
283 x_Measure_Rec_Overlap.Dimension5_Name := return_value_if_not_missing
284 ( p_varchar2 => p_Measure_Rec_Orig.Dimension5_Name );
285
286 x_Measure_Rec_Overlap.Dimension6_ID := return_value_if_not_missing
287 ( p_number => p_Measure_Rec_Orig.Dimension6_ID );
288 x_Measure_Rec_Overlap.Dimension6_Short_Name := return_value_if_not_missing
289 ( p_varchar2 => p_Measure_Rec_Orig.Dimension6_Short_Name );
290 x_Measure_Rec_Overlap.Dimension6_Name := return_value_if_not_missing
291 ( p_varchar2 => p_Measure_Rec_Orig.Dimension6_Name );
292
293 x_Measure_Rec_Overlap.Dimension7_ID := return_value_if_not_missing
294 ( p_number => p_Measure_Rec_Orig.Dimension7_ID );
295 x_Measure_Rec_Overlap.Dimension7_Short_Name := return_value_if_not_missing
296 ( p_varchar2 => p_Measure_Rec_Orig.Dimension7_Short_Name );
297 x_Measure_Rec_Overlap.Dimension7_Name := return_value_if_not_missing
298 ( p_varchar2 => p_Measure_Rec_Orig.Dimension7_Name );
299 EXCEPTION
300 WHEN OTHERS THEN
301 NULL;
302 END GetOriginalDimensions;
303 --
304
305 -- Fix for 2309894 ends here
306 Procedure Create_Indicator_Dimension
307 ( p_Measure_id number
308 , p_dimension_id number
309 , p_sequence_no number
310 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
311 , x_return_status OUT NOCOPY VARCHAR2
312 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
313 )
314 is
315 l_user_id number;
316 l_login_id number;
317 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
318 BEGIN
319
320 x_return_status := FND_API.G_RET_STS_SUCCESS;
321 if (BIS_UTILITIES_PUB.Value_Missing(p_dimension_id) = FND_API.G_TRUE) then
322 return;
323 end if;
324
325 l_user_id := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
326
327 l_login_id := fnd_global.LOGIN_ID;
328
329 insert into bis_indicator_dimensions
330 (
331 INDICATOR_ID
332 , Dimension_ID
333 , SEQUENCE_NO
334 , CREATION_DATE
335 , CREATED_BY
336 , LAST_UPDATE_DATE
337 , LAST_UPDATED_BY
338 , LAST_UPDATE_LOGIN
339 )
340 values
341 ( p_Measure_ID
342 , p_dimension_id
343 , p_sequence_no
344 , SYSDATE
345 , l_user_id
346 , SYSDATE
347 , l_user_id
348 , l_login_id
349 );
350
351 --commented RAISE
352 EXCEPTION
353 WHEN NO_DATA_FOUND THEN
354 x_return_status := FND_API.G_RET_STS_ERROR ;
355 --RAISE FND_API.G_EXC_ERROR;
356 when FND_API.G_EXC_ERROR then
357 x_return_status := FND_API.G_RET_STS_ERROR ;
358 --RAISE FND_API.G_EXC_ERROR;
359 when FND_API.G_EXC_UNEXPECTED_ERROR then
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
361 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
362 when others then
363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
364 --added last two params
365
366 l_error_tbl := x_error_tbl;
367 BIS_UTILITIES_PVT.Add_Error_Message
368 ( p_error_msg_id => SQLCODE
369 , p_error_description => SQLERRM
370 , p_error_proc_name => G_PKG_NAME||'.Create_Indicator_Dimension'
371 , p_error_table => l_error_tbl
372 , x_error_table => x_error_tbl
373 );
374 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
375
376 end Create_Indicator_Dimension;
377
378 PROCEDURE Create_Indicator_Dimensions
379 ( p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
380 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
381 , x_return_status OUT NOCOPY VARCHAR2
382 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
383 )
384 IS
385 l_id NUMBER := p_Measure_rec.Measure_Id;
386 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
387 BEGIN
388
389 x_return_status := FND_API.G_RET_STS_SUCCESS;
390
391 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension1_id)
392 = FND_API.G_FALSE
393 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension1_id)
394 = FND_API.G_FALSE) then
395
396 Create_Indicator_Dimension( l_id
397 , p_Measure_Rec.Dimension1_Id
398 , 1
399 , p_owner
400 , x_return_status
401 , x_error_tbl
402 );
403
404 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension2_id)
405 = FND_API.G_FALSE
406 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension2_id)
407 = FND_API.G_FALSE) then
408
409 Create_Indicator_Dimension( l_id
410 , p_Measure_Rec.Dimension2_Id
411 , 2
412 , p_owner
413 , x_return_status
414 , x_error_tbl
415 );
416
417 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension3_id)
418 = FND_API.G_FALSE
419 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension3_id)
420 = FND_API.G_FALSE) then
421 Create_Indicator_Dimension( l_id
422 , p_Measure_Rec.Dimension3_Id
423 , 3
424 , p_owner
425 , x_return_status
426 , x_error_tbl
427 );
428
429 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension4_id)
430 = FND_API.G_FALSE
431 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension4_id)
432 = FND_API.G_FALSE) then
433 Create_Indicator_Dimension( l_id
434 , p_Measure_Rec.Dimension4_Id
435 , 4
436 , p_owner
437 , x_return_status
438 , x_error_tbl
439 );
440
441 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension5_id)
442 = FND_API.G_FALSE
443 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension5_id)
444 = FND_API.G_FALSE) then
445 Create_Indicator_Dimension( l_id
446 , p_Measure_Rec.Dimension5_Id
447 , 5
448 , p_owner
449 , x_return_status
450 , x_error_tbl
451 );
452
453 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension6_id)
454 = FND_API.G_FALSE
455 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension6_id)
456 = FND_API.G_FALSE) then
457 Create_Indicator_Dimension( l_id
458 , p_Measure_Rec.Dimension6_Id
459 , 6
460 , p_owner
461 , x_return_status
462 , x_error_tbl
463 );
464
465 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension7_id)
466 = FND_API.G_FALSE
467 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension7_id)
468 = FND_API.G_FALSE) then
469 Create_Indicator_Dimension( l_id
470 , p_Measure_Rec.Dimension7_Id
471 , 7
472 , p_owner
473 , x_return_status
474 , x_error_tbl
475 );
476 end if;
477 end if;
478 end if;
479 end if;
480 end if;
481 end if;
482 end if;
483
484 --commented RAISE
485 EXCEPTION
486 WHEN NO_DATA_FOUND THEN
487 x_return_status := FND_API.G_RET_STS_ERROR ;
488 -- RAISE FND_API.G_EXC_ERROR;
489 when FND_API.G_EXC_ERROR then
490 x_return_status := FND_API.G_RET_STS_ERROR ;
491 -- RAISE FND_API.G_EXC_ERROR;
492 when FND_API.G_EXC_UNEXPECTED_ERROR then
493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
494 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
495 when others then
496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
497 --added last two params
498 l_error_tbl := x_error_tbl;
499 BIS_UTILITIES_PVT.Add_Error_Message
500 ( p_error_msg_id => SQLCODE
501 , p_error_description => SQLERRM
502 , p_error_proc_name => G_PKG_NAME||'.Create_Indicator_Dimension'
503 , p_error_table => l_error_tbl
504 , x_error_table => x_error_tbl
505 );
506 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
507
508 END Create_Indicator_Dimensions;
509
510 --Overload Create_Indicator_Dimensions so that old data model ldts can be uploaded using
511 --The latest lct file. The lct file can indirectly call this overloaded procedure
512 --by passing in Org and Time also
513 PROCEDURE Create_Indicator_Dimensions
514 ( p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
515 , p_Org_Dimension_ID IN NUMBER := BIS_UTILITIES_PUB.G_NULL_NUM
516 , p_Time_Dimension_ID IN NUMBER := BIS_UTILITIES_PUB.G_NULL_NUM
517 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
518 , x_return_status OUT NOCOPY VARCHAR2
519 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
520 )
521 IS
522 l_id NUMBER := p_Measure_rec.Measure_Id;
523 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
524 BEGIN
525
526 x_return_status := FND_API.G_RET_STS_SUCCESS;
527
528 if(IS_OLD_DATA_MODEL(p_Measure_Rec,p_Org_Dimension_ID,p_Time_Dimension_ID)) THEN
529
530 if (BIS_UTILITIES_PUB.Value_Missing(p_Org_Dimension_id)
531 = FND_API.G_FALSE
532 AND BIS_UTILITIES_PUB.Value_Null(p_Org_Dimension_id)
533 = FND_API.G_FALSE) then
534
535 Create_Indicator_Dimension( l_id
536 , p_Org_Dimension_Id
537 , 1
538 , p_owner
539 , x_return_status
540 , x_error_tbl
541 );
542 if (BIS_UTILITIES_PUB.Value_Missing(p_Time_Dimension_id)
543 = FND_API.G_FALSE
544 AND BIS_UTILITIES_PUB.Value_Null(p_Time_Dimension_id)
545 = FND_API.G_FALSE) then
546
547 Create_Indicator_Dimension( l_id
548 , p_Time_Dimension_Id
549 , 2
550 , p_owner
551 , x_return_status
552 , x_error_tbl
553 );
554
555 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension1_id)
556 = FND_API.G_FALSE
557 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension1_id)
558 = FND_API.G_FALSE) then
559
560 Create_Indicator_Dimension( l_id
561 , p_Measure_Rec.Dimension1_Id
562 , 3
563 , p_owner
564 , x_return_status
565 , x_error_tbl
566 );
567
568 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension2_id)
569 = FND_API.G_FALSE
570 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension2_id)
571 = FND_API.G_FALSE) then
572
573 Create_Indicator_Dimension( l_id
574 , p_Measure_Rec.Dimension2_Id
575 , 4
576 , p_owner
577 , x_return_status
578 , x_error_tbl
579 );
580
581 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension3_id)
582 = FND_API.G_FALSE
583 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension3_id)
584 = FND_API.G_FALSE) then
585 Create_Indicator_Dimension( l_id
586 , p_Measure_Rec.Dimension3_Id
587 , 5
588 , p_owner
589 , x_return_status
590 , x_error_tbl
591 );
592
593 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension4_id)
594 = FND_API.G_FALSE
595 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension4_id)
596 = FND_API.G_FALSE) then
597 Create_Indicator_Dimension( l_id
598 , p_Measure_Rec.Dimension4_Id
599 , 6
600 , p_owner
601 , x_return_status
602 , x_error_tbl
603 );
604
605 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension5_id)
606 = FND_API.G_FALSE
607 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension5_id)
608 = FND_API.G_FALSE) then
609 Create_Indicator_Dimension( l_id
610 , p_Measure_Rec.Dimension5_Id
611 , 7
612 , p_owner
613 , x_return_status
614 , x_error_tbl
615 );
616 end if;
617 end if;
618 end if;
619 end if;
620 end if;
621 end if;
622 end if;
623
624 ELSE
625
626 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension1_id)
627 = FND_API.G_FALSE
628 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension1_id)
629 = FND_API.G_FALSE) then
630
631 Create_Indicator_Dimension( l_id
632 , p_Measure_Rec.Dimension1_Id
633 , 1
634 , p_owner
635 , x_return_status
636 , x_error_tbl
637 );
638
639 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension2_id)
640 = FND_API.G_FALSE
641 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension2_id)
642 = FND_API.G_FALSE) then
643
644 Create_Indicator_Dimension( l_id
645 , p_Measure_Rec.Dimension2_Id
646 , 2
647 , p_owner
648 , x_return_status
649 , x_error_tbl
650 );
651
652 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension3_id)
653 = FND_API.G_FALSE
654 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension3_id)
655 = FND_API.G_FALSE) then
656 Create_Indicator_Dimension( l_id
657 , p_Measure_Rec.Dimension3_Id
658 , 3
659 ,p_owner
660 , x_return_status
661 , x_error_tbl
662 );
663
664 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension4_id)
665 = FND_API.G_FALSE
666 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension4_id)
667 = FND_API.G_FALSE) then
668 Create_Indicator_Dimension( l_id
669 , p_Measure_Rec.Dimension4_Id
670 , 4
671 , p_owner
672 , x_return_status
673 , x_error_tbl
674 );
675
676 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension5_id)
677 = FND_API.G_FALSE
678 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension5_id)
679 = FND_API.G_FALSE) then
680 Create_Indicator_Dimension( l_id
681 , p_Measure_Rec.Dimension5_Id
682 , 5
683 , p_owner
684 , x_return_status
685 , x_error_tbl
686 );
687
688 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension6_id)
689 = FND_API.G_FALSE
690 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension6_id)
691 = FND_API.G_FALSE) then
692 Create_Indicator_Dimension( l_id
693 , p_Measure_Rec.Dimension6_Id
694 , 6
695 , p_owner
696 , x_return_status
697 , x_error_tbl
698 );
699
700 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension7_id)
701 = FND_API.G_FALSE
702 AND BIS_UTILITIES_PUB.Value_Null(p_Measure_Rec.Dimension7_id)
703 = FND_API.G_FALSE) then
704 Create_Indicator_Dimension( l_id
705 , p_Measure_Rec.Dimension7_Id
706 , 7
707 , p_owner
708 , x_return_status
709 , x_error_tbl
710 );
711 end if;
712 end if;
713 end if;
714 end if;
715 end if;
716 end if;
717 end if;
718
719 END IF;
720 --commented RAISE
721 EXCEPTION
722 WHEN NO_DATA_FOUND THEN
723 x_return_status := FND_API.G_RET_STS_ERROR ;
724 -- RAISE FND_API.G_EXC_ERROR;
725 when FND_API.G_EXC_ERROR then
726 x_return_status := FND_API.G_RET_STS_ERROR ;
727 -- RAISE FND_API.G_EXC_ERROR;
728 when FND_API.G_EXC_UNEXPECTED_ERROR then
729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
730 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
731 when others then
732 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
733 --added last two params
734 l_error_tbl := x_error_tbl;
735 BIS_UTILITIES_PVT.Add_Error_Message
736 ( p_error_msg_id => SQLCODE
737 , p_error_description => SQLERRM
738 , p_error_proc_name => G_PKG_NAME||'.Create_Indicator_Dimension'
739 , p_error_table => l_error_tbl
740 , x_error_table => x_error_tbl
741 );
742 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
743
744 END Create_Indicator_Dimensions;
745
746 PROCEDURE SetNULL
747 ( p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
748 , x_Measure_Rec OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
749 )
750 IS
751 BEGIN
752
753 x_measure_rec.Measure_ID :=
754 BIS_UTILITIES_PVT.CheckMissNum(p_measure_rec.Measure_ID);
755 x_measure_rec.Measure_Short_Name :=
756 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Measure_Short_Name);
757 x_measure_rec.Measure_Name :=
758 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Measure_Name);
759 x_measure_rec.Description :=
760 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Description);
761 x_measure_rec.Dimension1_ID :=
762 BIS_UTILITIES_PVT.CheckMissNum(p_measure_rec.Dimension1_ID);
763 x_measure_rec.Dimension1_Short_Name :=
764 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension1_Short_Name);
765 x_measure_rec.Dimension1_Name :=
766 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension1_Name);
767 x_measure_rec.Dimension2_ID :=
768 BIS_UTILITIES_PVT.CheckMissNum(p_measure_rec.Dimension2_ID);
769 x_measure_rec.Dimension2_Short_Name :=
770 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension2_Short_Name);
771 x_measure_rec.Dimension2_Name :=
772 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension2_Name);
773 x_measure_rec.Dimension3_ID :=
774 BIS_UTILITIES_PVT.CheckMissNum(p_measure_rec.Dimension3_ID);
775 x_measure_rec.Dimension3_Short_Name :=
776 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension3_Short_Name);
777 x_measure_rec.Dimension3_Name :=
778 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension3_Name);
779 x_measure_rec.Dimension4_ID :=
780 BIS_UTILITIES_PVT.CheckMissNum(p_measure_rec.Dimension4_ID);
781 x_measure_rec.Dimension4_Short_Name :=
782 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension4_Short_Name);
783 x_measure_rec.Dimension4_Name :=
784 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension4_Name);
785 x_measure_rec.Dimension5_ID :=
786 BIS_UTILITIES_PVT.CheckMissNum(p_measure_rec.Dimension5_ID);
787 x_measure_rec.Dimension5_Short_Name :=
788 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension5_Short_Name);
789 x_measure_rec.Dimension5_Name :=
790 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension5_Name);
791 x_measure_rec.Dimension6_ID :=
792 BIS_UTILITIES_PVT.CheckMissNum(p_measure_rec.Dimension6_ID);
793 x_measure_rec.Dimension6_Short_Name :=
794 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension6_Short_Name);
795 x_measure_rec.Dimension6_Name :=
796 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension6_Name);
797 x_measure_rec.Dimension7_ID :=
798 BIS_UTILITIES_PVT.CheckMissNum(p_measure_rec.Dimension7_ID);
799 x_measure_rec.Dimension7_Short_Name :=
800 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension7_Short_Name);
801 x_measure_rec.Dimension7_Name :=
802 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dimension7_Name);
803 x_measure_rec.Unit_Of_Measure_Class :=
804 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Unit_Of_Measure_Class);
805 -- Fix for 1850860 starts here
806 x_measure_rec.Actual_Data_Source_Type :=
807 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Actual_Data_Source_Type);
808 x_measure_rec.Actual_Data_Source :=
809 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Actual_Data_Source);
810 x_measure_rec.Function_Name :=
811 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Function_Name);
812 x_measure_rec.Comparison_Source :=
813 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Comparison_Source);
814 x_measure_rec.Increase_In_Measure :=
815 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Increase_In_Measure);
816 -- Fix for 1850860 ends here
817 -- 2440739
818 x_measure_rec.Enable_Link :=
819 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Enable_Link);
820 --Enhancement 3865711
821 x_measure_rec.Obsolete :=
822 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Obsolete);
823 x_measure_rec.Measure_Type :=
824 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Measure_Type);
825 x_measure_rec.Application_Id :=
826 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Application_Id); --2465354
827
828 -- mdamle 4/23/2003 - PMD - Measure Definer
829 x_measure_rec.Dataset_id :=
830 BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Dataset_Id); --2465354
831
832 -- rpenneru 12/21/2004 - Functional Area Short Name
833 x_measure_rec.Func_Area_Short_Name := BIS_UTILITIES_PVT.CheckMissChar(p_measure_rec.Func_Area_Short_Name);
834
835 x_measure_rec.Created_By := BIS_UTILITIES_PVT.CheckMissNum(p_measure_rec.Created_By);
836 x_measure_rec.Creation_Date := BIS_UTILITIES_PVT.CheckMissDate(p_measure_rec.Creation_Date);
837 x_measure_rec.Last_Updated_By := BIS_UTILITIES_PVT.CheckMissNum(p_measure_rec.Last_Updated_By);
838 x_measure_rec.Last_Update_Date := BIS_UTILITIES_PVT.CheckMissDate(p_measure_rec.Last_Update_Date);
839 x_measure_rec.Last_Update_Login := BIS_UTILITIES_PVT.CheckMissNum(p_measure_rec.Last_Update_Login);
840
841 END SetNULL;
842
843 -- creates one Measure, with the dimensions sequenced in the order
844 -- they are passed in
845 --- redundant because of defaults in next overloaded signature
846 --Procedure Create_Measure
847 --( p_api_version IN NUMBER
848 --, p_commit IN VARCHAR2 := FND_API.G_FALSE
849 --, p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
850 --, p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
851 --, x_return_status OUT NOCOPY VARCHAR2
852 --, x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
853 --)
854 --IS
855 --BEGIN
856 --
857 -- Create_Measure
858 -- ( p_api_version => p_api_version
859 -- , p_commit => p_commit
860 -- , p_validation_level => p_validation_level
861 -- , p_Measure_Rec => p_Measure_Rec
862 -- , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
863 -- , x_return_status => x_return_status
864 -- , x_error_Tbl => x_error_Tbl
865 -- );
866 --
867 --EXCEPTION
868 -- when others then
869 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
870 -- -- added last two params
871 -- BIS_UTILITIES_PVT.Add_Error_Message
872 -- ( p_error_msg_id => SQLCODE
873 -- , p_error_description => SQLERRM
874 -- , p_error_proc_name => G_PKG_NAME||'.Create_Measure'
875 -- , p_error_table => x_error_tbl
876 -- , x_error_table => x_error_tbl
877 -- );
878 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
879 --
880 --END Create_Measure;
881
882 -- creates one Measure for the given owner,
883 -- with the dimensions sequenced in the order they are passed in
884 Procedure Create_Measure
885 ( p_api_version IN NUMBER
886 , p_commit IN VARCHAR2 := FND_API.G_FALSE
887 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
888 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
889 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
890 , x_return_status OUT NOCOPY VARCHAR2
891 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
892 )
893 IS
894 l_error_count number;
895 l_user_id number;
896 l_login_id number;
897 l_id number;
898 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
899 l_application_rec BIS_APPLICATION_PVT.Application_rec_type;
900 l_measure_id NUMBER;
901 l_own_appl VARCHAR2(100) := FND_API.G_FALSE ; --2465354
902 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
903 l_Mapped_measure bis_indicators_tl.NAME%TYPE;
904 l_Return_Status VARCHAR2(2000);
905
906 BEGIN
907
908 x_return_status := FND_API.G_RET_STS_SUCCESS;
909 SetNULL( p_Measure_Rec, l_Measure_Rec);
910 --trying to phase out NOCOPY this procedure--created two new procedures
911 --a call to value_id_conversion is already made in the public package
912 /*
913 BIS_MEASURE_PVT.Value_ID_Conversion
914 ( p_api_version => p_api_version
915 , p_Measure_Rec => l_Measure_Rec
916 , x_Measure_Rec => l_Measure_Rec
917 , x_return_status => x_return_status
918 , x_error_Tbl => x_error_tbl
919 );
920 */
921
922
923 -- dbms_output.put_line('PVT. val id conv: '||x_return_status );
924
925 --Added this check
926 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
927 RAISE FND_API.G_EXC_ERROR;
928 END IF;
929 --dbms_output.put_line('1'||'l_measure_rec.function_short_name');
930
931 Validate_Measure( p_api_version
932 , p_validation_level
933 , l_Measure_Rec
934 , p_owner
935 , x_return_status
936 , x_error_Tbl
937 );
938 --dbms_output.put_line('2'||'l_measure_rec.function_short_name');
939
940 --dbms_output.put_line('PVT. validate Measure: '||x_return_status );
941
942 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
943 --dbms_output.put_line('inside exception');
944 RAISE FND_API.G_EXC_ERROR;
945 END IF;
946 --
947 l_Measure_Id := Get_Measure_Id_From_Short_Name(p_measure_rec);
948
949 if (l_measure_id is NOT NULL) then
950 l_error_tbl := x_error_tbl;
951 BIS_UTILITIES_PVT.Add_Error_Message
952 ( p_error_msg_name => 'BIS_MEASURE_SHORT_NAME_UNIQUE'
953 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
954 , p_error_proc_name => G_PKG_NAME||'.Create_Measure'
955 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
956 , p_error_table => l_error_tbl
957 , x_error_table => x_error_tbl
958 );
959 RAISE FND_API.G_EXC_ERROR;
960 end if;
961 --
962
963 -- mdamle 07/18/2003 - Allow enable_link = Y only if function_name is not null
964 if (p_measure_rec.function_name is null and p_measure_rec.enable_link = 'Y') then
965 l_error_tbl := x_error_tbl;
966 BIS_UTILITIES_PVT.Add_Error_Message
967 ( p_error_msg_name => 'BIS_PMF_ENABLE_LINK_ERR'
968 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
969 , p_error_proc_name => G_PKG_NAME||'.Create_Measure'
970 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
971 , p_error_table => l_error_tbl
972 , x_error_table => x_error_tbl
973 );
974 RAISE FND_API.G_EXC_ERROR;
975 end if;
976
977 -- ankgoel: bug#3557236
978 IF (p_Measure_rec.is_validate <> FND_API.G_FALSE) THEN
979 -- mdamle 07/18/2003 - Check if measure is being mapped to a source
980 -- that's already mapped to another measure.
981 if isSourceColumnMappedAlready(p_Measure_rec, l_Mapped_measure) then
982 l_error_tbl := x_error_tbl;
983 BIS_UTILITIES_PVT.Add_Error_Message
984 ( p_error_msg_name => 'BIS_PMF_SOURCE_MAPPING_ERR'
985 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
986 , p_error_proc_name => G_PKG_NAME||'.Create_Measure'
987 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
988 , p_token1 => 'MEASURE'
989 , p_value1 => l_Mapped_measure
990 , p_error_table => l_error_tbl
991 , x_error_table => x_error_tbl
992 );
993
994 RAISE FND_API.G_EXC_ERROR;
995 end if;
996
997 --sawu: 9/2/04: need to validate compare-to column also for bug#3859267
998 if isCompareColumnMappedAlready(p_Measure_rec, l_Mapped_measure) then
999 l_error_tbl := x_error_tbl;
1000 BIS_UTILITIES_PVT.Add_Error_Message
1001 ( p_error_msg_name => 'BIS_PMF_COLUMN_MAPPING_ERR'
1002 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1003 , p_error_proc_name => G_PKG_NAME||'.Create_Measure'
1004 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1005 , p_token1 => 'MEASURE'
1006 , p_value1 => l_Mapped_measure
1007 , p_error_table => l_error_tbl
1008 , x_error_table => x_error_tbl
1009 );
1010
1011 RAISE FND_API.G_EXC_ERROR;
1012 end if;
1013 END IF;
1014
1015 -- ankgoel: bug#3891748 - Created_By will take precedence over Owner.
1016 -- Last_Updated_By can be different from Created_By while creating measures
1017 -- during sync-up
1018 IF (l_Measure_Rec.Created_By IS NULL) THEN
1019 l_Measure_Rec.Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1020 END IF;
1021 IF (l_Measure_Rec.Last_Updated_By IS NULL) THEN
1022 l_Measure_Rec.Last_Updated_By := l_Measure_Rec.Created_By;
1023 END IF;
1024 IF (l_Measure_Rec.Last_Update_Login IS NULL) THEN
1025 l_Measure_Rec.Last_Update_Login := fnd_global.LOGIN_ID;
1026 END IF;
1027 IF (l_Measure_Rec.Creation_Date IS NULL) THEN
1028 l_Measure_Rec.Creation_Date := sysdate;
1029 END IF;
1030 IF (l_Measure_Rec.Last_Update_Date IS NULL) THEN
1031 l_Measure_Rec.Last_Update_Date := sysdate;
1032 END IF;
1033
1034 --
1035 select bis_indicators_s.NextVal into l_id from dual;
1036 --dbms_output.put_line('function shortname:::'||l_measure_rec.function_short_name);
1037
1038
1039 insert into bis_indicators(
1040 INDICATOR_ID
1041 , SHORT_NAME
1042 , UOM_CLASS
1043 , ACTUAL_DATA_SOURCE_TYPE
1044 , ACTUAL_DATA_SOURCE
1045 , FUNCTION_NAME
1046 , COMPARISON_SOURCE
1047 , INCREASE_IN_MEASURE
1048 , ENABLE_LINK -- 2440739
1049 -- mdamle 4/23/2003 - PMD - Measure Definer
1050 , ENABLED -- #3031053
1051 , OBSOLETE --#3865711
1052 , MEASURE_TYPE
1053 , DATASET_ID
1054 , CREATION_DATE
1055 , CREATED_BY
1056 , LAST_UPDATE_DATE
1057 , LAST_UPDATED_BY
1058 , LAST_UPDATE_LOGIN
1059 )
1060 values
1061 ( l_id
1062 , l_Measure_Rec.Measure_Short_Name
1063 , l_Measure_Rec.Unit_Of_Measure_Class
1064 , l_Measure_Rec.Actual_Data_Source_Type
1065 , l_Measure_Rec.Actual_Data_Source
1066 , l_Measure_Rec.Function_Name
1067 , l_Measure_Rec.Comparison_Source
1068 , l_Measure_Rec.Increase_In_Measure
1069 , NVL(l_Measure_Rec.Enable_Link, 'N')
1070 -- mdamle 4/23/2003 - PMD - Measure Definer
1071 , l_Measure_Rec.enabled
1072 -- #3031053
1073 , l_Measure_Rec.Obsolete --3865711
1074 , l_Measure_Rec.Measure_Type
1075 , l_Measure_Rec.Dataset_id
1076 , l_Measure_Rec.Creation_Date
1077 , l_Measure_Rec.Created_By
1078 , l_Measure_Rec.Last_Update_Date
1079 , l_Measure_Rec.Last_Updated_By
1080 , l_Measure_Rec.Last_Update_Login
1081 );
1082
1083 insert into bis_INDICATORS_TL (
1084 INDICATOR_ID,
1085 LANGUAGE,
1086 NAME,
1087 DESCRIPTION,
1088 CREATION_DATE,
1089 CREATED_BY,
1090 LAST_UPDATE_DATE,
1091 LAST_UPDATED_BY,
1092 LAST_UPDATE_LOGIN,
1093 TRANSLATED,
1094 SOURCE_LANG
1095 ) select
1096 l_id
1097 , L.LANGUAGE_CODE
1098 , l_Measure_Rec.Measure_Name
1099 , l_Measure_Rec.Description
1100 , l_Measure_Rec.Creation_Date
1101 , l_Measure_Rec.Created_By
1102 , l_Measure_Rec.Last_Update_Date
1103 , l_Measure_Rec.Last_Updated_By
1104 , l_Measure_Rec.Last_Update_Login
1105 , 'Y'
1106 , userenv('LANG')
1107 from FND_LANGUAGES L
1108 where L.INSTALLED_FLAG in ('I', 'B')
1109 and not exists
1110 (select NULL
1111 from bis_INDICATORS_TL T
1112 where T.indicator_ID = l_id
1113 and T.LANGUAGE = L.LANGUAGE_CODE);
1114
1115 l_measure_rec.Measure_id := l_id;
1116 -- dbms_output.put_line('CREATE_MEASURE: '||l_id);
1117
1118 Create_Indicator_Dimensions(l_measure_Rec ,p_owner ,x_return_status ,x_error_Tbl);
1119 -- dbms_output.put_line('create measure dimension: '||x_return_status);
1120
1121 --2465354
1122 l_application_rec.Application_id := l_Measure_Rec.Application_Id;
1123 IF (NVL(l_application_rec.Application_id,-1) <> -1 ) THEN
1124 l_own_appl := FND_API.G_TRUE;
1125 END IF;
1126 --2465354
1127
1128 Create_Application_Measure
1129 ( p_api_version => p_api_version
1130 ,p_commit => p_commit
1131 ,p_Measure_rec => l_Measure_rec
1132 ,p_application_rec => l_application_rec
1133 ,p_owning_application => l_own_appl
1134 ,p_owner => p_owner
1135 ,x_return_status => x_return_status
1136 ,x_error_Tbl => x_error_Tbl
1137 );
1138
1139 -- dbms_output.put_line('create measure application: '||x_return_status);
1140 IF (p_Measure_Rec.Func_Area_Short_Name IS NOT NULL ) THEN
1141 Load_Measure_Extension
1142 ( p_api_version => p_api_version
1143 ,p_commit => p_commit
1144 , p_Measure_Rec => l_Measure_rec
1145 , p_owner => p_owner
1146 , x_return_status => l_Return_Status
1147 , x_error_Tbl => x_error_Tbl
1148 );
1149 IF (l_return_status IS NOT NULL AND l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1150 RAISE FND_API.G_EXC_ERROR;
1151 END IF;
1152 END IF;
1153
1154 if (p_commit = FND_API.G_TRUE) then
1155 COMMIT;
1156 end if;
1157
1158 --commented RAISE
1159 EXCEPTION
1160 WHEN NO_DATA_FOUND THEN
1161 x_return_status := FND_API.G_RET_STS_ERROR ;
1162 --RAISE FND_API.G_EXC_ERROR;
1163 when FND_API.G_EXC_ERROR then
1164 x_return_status := FND_API.G_RET_STS_ERROR ;
1165 -- RAISE FND_API.G_EXC_ERROR;
1166 when FND_API.G_EXC_UNEXPECTED_ERROR then
1167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1168 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1169 when others then
1170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1171 --added last two params
1172 l_error_tbl := x_error_tbl;
1173 BIS_UTILITIES_PVT.Add_Error_Message
1174 ( p_error_msg_id => SQLCODE
1175 , p_error_description => SQLERRM
1176 , p_error_proc_name => G_PKG_NAME||'.Create_Measure'
1177 , p_error_table => l_error_tbl
1178 , x_error_table => x_error_tbl
1179 );
1180 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1181
1182 END Create_Measure;
1183 --
1184 --
1185 -- creates one Measure for the given owner,
1186 -- with the dimensions sequenced in the order they are passed in
1187 --Overload Create_Measure so that old data model ldts can be uploaded using
1188 --The latest lct file. The lct file can call Load_Measure which calls this
1189 --by passing in Org and Time dimension short_names also
1190 Procedure Create_Measure
1191 ( p_api_version IN NUMBER
1192 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1193 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1194 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
1195 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
1196 , p_Org_Dimension_ID IN NUMBER
1197 , p_Time_Dimension_ID IN NUMBER
1198 , x_return_status OUT NOCOPY VARCHAR2
1199 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1200 )
1201 IS
1202 l_error_count number;
1203 l_user_id number;
1204 l_login_id number;
1205 l_id number;
1206 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
1207 l_application_rec BIS_APPLICATION_PVT.Application_rec_type;
1208 l_measure_id NUMBER;
1209 l_own_appl VARCHAR2(100) := FND_API.G_FALSE ; --2465354
1210 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1211 BEGIN
1212
1213 x_return_status := FND_API.G_RET_STS_SUCCESS;
1214 SetNULL( p_Measure_Rec, l_Measure_Rec);
1215
1216 --trying to phase out NOCOPY this procedure--created two new procedures
1217 --a call to value_id_conversion is already made in the public package
1218 /*
1219 BIS_MEASURE_PVT.Value_ID_Conversion
1220 ( p_api_version => p_api_version
1221 , p_Measure_Rec => l_Measure_Rec
1222 , x_Measure_Rec => l_Measure_Rec
1223 , x_return_status => x_return_status
1224 , x_error_Tbl => x_error_tbl
1225 );
1226 */
1227
1228
1229 -- dbms_output.put_line('PVT. val id conv: '||x_return_status );
1230
1231 --Added this check
1232 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1233 RAISE FND_API.G_EXC_ERROR;
1234 END IF;
1235
1236 Validate_Measure( p_api_version
1237 , p_validation_level
1238 , l_Measure_Rec
1239 , p_owner
1240 , x_return_status
1241 , x_error_Tbl
1242 );
1243
1244 -- dbms_output.put_line('PVT. validate Measure: '||x_return_status );
1245
1246 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1247 RAISE FND_API.G_EXC_ERROR;
1248 END IF;
1249
1250 --added this call to validate Org Dimension
1251 BIS_MEASURE_VALIDATE_PVT.Validate_Dimension_Id
1252 ( p_api_version => p_api_version
1253 , p_dimension_id => p_Org_Dimension_ID
1254 , p_dimension_short_name => FND_API.G_MISS_CHAR
1255 , x_return_status => x_return_status
1256 , x_error_Tbl => x_error_Tbl
1257 );
1258 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1259 RAISE FND_API.G_EXC_ERROR;
1260 END IF;
1261
1262 --added this call to validate Time Dimension
1263 BIS_MEASURE_VALIDATE_PVT.Validate_Dimension_Id
1264 ( p_api_version => p_api_version
1265 , p_dimension_id => p_Time_Dimension_ID
1266 , p_dimension_short_name => FND_API.G_MISS_CHAR
1267 , x_return_status => x_return_status
1268 , x_error_Tbl => x_error_Tbl
1269 );
1270 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1271 RAISE FND_API.G_EXC_ERROR;
1272 END IF;
1273
1274
1275 --
1276 l_Measure_Id := Get_Measure_Id_From_Short_Name(p_measure_rec);
1277
1278 if (l_measure_id is NOT NULL) then
1279 l_error_tbl := x_error_tbl;
1280 BIS_UTILITIES_PVT.Add_Error_Message
1281 ( p_error_msg_name => 'BIS_MEASURE_SHORT_NAME_UNIQUE'
1282 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1283 , p_error_proc_name => G_PKG_NAME||'.Create_Measure'
1284 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1285 , p_error_table => l_error_tbl
1286 , x_error_table => x_error_tbl
1287 );
1288 RAISE FND_API.G_EXC_ERROR;
1289 end if;
1290 --
1291
1292 -- ankgoel: bug#3891748 - Created_By will take precedence over Owner.
1293 -- Last_Updated_By can be different from Created_By while creating measures
1294 -- during sync-up
1295 IF (l_Measure_Rec.Created_By IS NULL) THEN
1296 l_Measure_Rec.Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1297 END IF;
1298 IF (l_Measure_Rec.Last_Updated_By IS NULL) THEN
1299 l_Measure_Rec.Last_Updated_By := l_Measure_Rec.Created_By;
1300 END IF;
1301 IF (l_Measure_Rec.Last_Update_Login IS NULL) THEN
1302 l_Measure_Rec.Last_Update_Login := fnd_global.LOGIN_ID;
1303 END IF;
1304 IF (l_Measure_Rec.Creation_Date IS NULL) THEN
1305 l_Measure_Rec.Creation_Date := sysdate;
1306 END IF;
1307 IF (l_Measure_Rec.Last_Update_Date IS NULL) THEN
1308 l_Measure_Rec.Last_Update_Date := sysdate;
1309 END IF;
1310
1311 --
1312 select bis_indicators_s.NextVal into l_id from dual;
1313
1314
1315 --Code commented as a fix for 2167619 starts here
1316 insert into bis_indicators(
1317 INDICATOR_ID
1318 , SHORT_NAME
1319 , UOM_CLASS
1320 , ACTUAL_DATA_SOURCE_TYPE
1321 , ACTUAL_DATA_SOURCE
1322 , FUNCTION_NAME
1323 , COMPARISON_SOURCE
1324 , INCREASE_IN_MEASURE
1325 , ENABLE_LINK
1326 -- mdamle 4/23/2003 - PMD - Measure Definer
1327 , ENABLED -- #3031053
1328 , OBSOLETE --3865711
1329 , MEASURE_TYPE
1330 , DATASET_ID
1331 , CREATION_DATE
1332 , CREATED_BY
1333 , LAST_UPDATE_DATE
1334 , LAST_UPDATED_BY
1335 , LAST_UPDATE_LOGIN
1336 )
1337 values
1338 ( l_id
1339 , l_Measure_Rec.Measure_Short_Name
1340 , l_Measure_Rec.Unit_Of_Measure_Class
1341 , l_Measure_Rec.Actual_Data_Source_Type
1342 , l_Measure_Rec.Actual_Data_Source
1343 , l_Measure_Rec.Function_Name
1344 , l_Measure_Rec.Comparison_Source
1345 , l_Measure_Rec.Increase_In_Measure
1346 , NVL(l_Measure_Rec.Enable_Link, 'N')
1347 -- mdamle 4/23/2003 - PMD - Measure Definer
1348 , l_Measure_Rec.enabled
1349 , l_Measure_Rec.Obsolete --3865711
1350 , l_Measure_Rec.Measure_Type
1351 , l_Measure_Rec.Dataset_id
1352 , l_Measure_Rec.Creation_Date
1353 , l_Measure_Rec.Created_By
1354 , l_Measure_Rec.Last_Update_Date
1355 , l_Measure_Rec.Last_Updated_By
1356 , l_Measure_Rec.Last_Update_Login
1357 );
1358 /*
1359 insert into bis_indicators(
1360 INDICATOR_ID
1361 , SHORT_NAME
1362 , UOM_CLASS
1363 , CREATION_DATE
1364 , CREATED_BY
1365 , LAST_UPDATE_DATE
1366 , LAST_UPDATED_BY
1367 , LAST_UPDATE_LOGIN
1368 )
1369 values
1370 ( l_id
1371 , l_Measure_Rec.Measure_Short_Name
1372 , l_Measure_Rec.Unit_Of_Measure_Class
1373 , SYSDATE
1374 , l_user_id
1375 , SYSDATE
1376 , l_user_id
1377 , l_login_id
1378 );
1379 */
1380 --Code commented as a fix for 2167619 ends here
1381 insert into bis_INDICATORS_TL (
1382 INDICATOR_ID,
1383 LANGUAGE,
1384 NAME,
1385 DESCRIPTION,
1386 CREATION_DATE,
1387 CREATED_BY,
1388 LAST_UPDATE_DATE,
1389 LAST_UPDATED_BY,
1390 LAST_UPDATE_LOGIN,
1391 TRANSLATED,
1392 SOURCE_LANG
1393 ) select
1394 l_id
1395 , L.LANGUAGE_CODE
1396 , l_Measure_Rec.Measure_Name
1397 , l_Measure_Rec.Description
1398 , l_Measure_Rec.Creation_Date
1399 , l_Measure_Rec.Created_By
1400 , l_Measure_Rec.Last_Update_Date
1401 , l_Measure_Rec.Last_Updated_By
1402 , l_Measure_Rec.Last_Update_Login
1403 , 'Y'
1404 , userenv('LANG')
1405 from FND_LANGUAGES L
1406 where L.INSTALLED_FLAG in ('I', 'B')
1407 and not exists
1408 (select NULL
1409 from bis_INDICATORS_TL T
1410 where T.indicator_ID = l_id
1411 and T.LANGUAGE = L.LANGUAGE_CODE);
1412
1413 l_measure_rec.Measure_id := l_id;
1414 -- dbms_output.put_line('CREATE_MEASURE: '||l_id);
1415
1416 --Changed the call to call the overloaded api with Org and Time
1417 Create_Indicator_Dimensions
1418 ( p_Measure_Rec =>l_measure_rec
1419 , p_Org_Dimension_ID => p_Org_Dimension_ID
1420 , p_Time_Dimension_ID => p_Time_Dimension_ID
1421 , p_owner => p_owner
1422 , x_return_status => x_return_status
1423 , x_error_tbl => x_error_tbl
1424 );
1425 -- dbms_output.put_line('create measure dimension: '||x_return_status);
1426
1427 --2465354
1428 l_application_rec.Application_id := l_Measure_Rec.Application_Id;
1429 IF (NVL(l_application_rec.Application_id,-1) <> -1 ) THEN
1430 l_own_appl := FND_API.G_TRUE ;
1431 END IF;
1432 --2465354
1433
1434 Create_Application_Measure
1435 ( p_api_version => p_api_version
1436 ,p_commit => p_commit
1437 ,p_Measure_rec => l_Measure_rec
1438 ,p_application_rec => l_application_rec
1439 ,p_owning_application => l_own_appl
1440 ,p_owner => p_owner
1441 ,x_return_status => x_return_status
1442 ,x_error_Tbl => x_error_Tbl
1443 );
1444
1445 -- dbms_output.put_line('create measure application: '||x_return_status);
1446
1447 if (p_commit = FND_API.G_TRUE) then
1448 COMMIT;
1449 end if;
1450
1451 --commented RAISE
1452 EXCEPTION
1453 WHEN NO_DATA_FOUND THEN
1454 x_return_status := FND_API.G_RET_STS_ERROR ;
1455 --RAISE FND_API.G_EXC_ERROR;
1456 when FND_API.G_EXC_ERROR then
1457 x_return_status := FND_API.G_RET_STS_ERROR ;
1458 -- RAISE FND_API.G_EXC_ERROR;
1459 when FND_API.G_EXC_UNEXPECTED_ERROR then
1460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1461 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1462 when others then
1463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1464 --added last two params
1465 l_error_tbl := x_error_tbl;
1466 BIS_UTILITIES_PVT.Add_Error_Message
1467 ( p_error_msg_id => SQLCODE
1468 , p_error_description => SQLERRM
1469 , p_error_proc_name => G_PKG_NAME||'.Create_Measure'
1470 , p_error_table => l_error_tbl
1471 , x_error_table => x_error_tbl
1472 );
1473 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1474
1475 END Create_Measure;
1476 --
1477 --
1478 --
1479 -- Gets All Performance Measures
1480 -- If information about the dimensions are not required, set all_info to
1481 -- FALSE
1482 Procedure Retrieve_Measures
1483 ( p_api_version IN NUMBER
1484 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
1485 , x_Measure_tbl OUT NOCOPY BIS_MEASURE_PUB.Measure_Tbl_Type
1486 , x_return_status OUT NOCOPY VARCHAR2
1487 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1488 )
1489 IS
1490
1491 l_measure_rec BIS_Measure_PUB.Measure_Rec_Type;
1492 l_dimension_tbl BIS_DIMENSION_PUB.Dimension_Tbl_Type;
1493 l_Measure_Rec_p BIS_Measure_PUB.Measure_Rec_Type;
1494
1495 cursor cr_all_measures is
1496 select measure_id, measure_short_name, measure_name
1497 from bisbv_performance_measures
1498 order by UPPER(MEASURE_NAME);
1499
1500 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1501 BEGIN
1502
1503 x_return_status := FND_API.G_RET_STS_SUCCESS;
1504
1505 for cr in cr_all_measures loop
1506
1507 l_measure_rec.measure_id := cr.measure_id;
1508 l_measure_rec.measure_short_name := cr.measure_short_name;
1509 l_measure_rec.measure_name := cr.measure_name;
1510
1511 l_Measure_Rec_p := l_Measure_Rec;
1512 BIS_MEASURE_PVT.Retrieve_Measure( p_api_version
1513 , l_Measure_Rec_p
1514 , p_all_info
1515 , l_Measure_Rec
1516 , x_return_status
1517 , x_error_Tbl
1518 );
1519
1520 x_measure_tbl(x_measure_tbl.count + 1) := l_measure_rec;
1521
1522 end loop;
1523
1524 IF cr_all_measures%ISOPEN THEN CLOSE cr_all_measures; END IF;
1525
1526 --commented RAISE
1527 EXCEPTION
1528 WHEN NO_DATA_FOUND THEN
1529 x_return_status := FND_API.G_RET_STS_ERROR ;
1530 IF cr_all_measures%ISOPEN THEN CLOSE cr_all_measures; END IF;
1531 --RAISE FND_API.G_EXC_ERROR;
1532 when FND_API.G_EXC_ERROR then
1533 x_return_status := FND_API.G_RET_STS_ERROR ;
1534 IF cr_all_measures%ISOPEN THEN CLOSE cr_all_measures; END IF;
1535 --RAISE FND_API.G_EXC_ERROR;
1536 when FND_API.G_EXC_UNEXPECTED_ERROR then
1537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1538 IF cr_all_measures%ISOPEN THEN CLOSE cr_all_measures; END IF;
1539 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1540 when others then
1541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1542 IF cr_all_measures%ISOPEN THEN CLOSE cr_all_measures; END IF;
1543 --added last two params
1544 l_error_tbl := x_error_tbl;
1545 BIS_UTILITIES_PVT.Add_Error_Message
1546 ( p_error_msg_id => SQLCODE
1547 , p_error_description => SQLERRM
1548 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Measures'
1549 , p_error_table => l_error_tbl
1550 , x_error_table => x_error_tbl
1551 );
1552 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1553
1554 END Retrieve_Measures;
1555 --
1556 --
1557 -- Gets Information for One Performance Measure
1558 -- If information about the dimension are not required, set all_info to FALSE.
1559 Procedure Retrieve_Measure
1560 ( p_api_version IN NUMBER
1561 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
1562 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
1563 , x_Measure_Rec IN OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
1564 , x_return_status OUT NOCOPY VARCHAR2
1565 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1566 )
1567 IS
1568 l_ID NUMBER;
1569 l_dimension_rec BIS_DIMENSION_PUB.DIMENSION_REC_TYPE;
1570 l_Dimension_Rec_p BIS_DIMENSION_PUB.DIMENSION_REC_TYPE;
1571 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1572 BEGIN
1573 x_return_status := FND_API.G_RET_STS_SUCCESS;
1574
1575 IF (p_Measure_Rec.Measure_Id IS NOT NULL) THEN
1576 Select Measure_id
1577 , Measure_short_name
1578 , Measure_name
1579 , description
1580 , actual_data_source_type
1581 , actual_data_source
1582 , Function_Name
1583 , Comparison_Source
1584 , Increase_In_Measure
1585 , Enable_Link
1586 , Enabled
1587 , Obsolete --3865711
1588 , Measure_Type
1589 , Dimension1_Id
1590 , Dimension2_Id
1591 , Dimension3_Id
1592 , Dimension4_Id
1593 , Dimension5_Id
1594 , Dimension6_Id
1595 , Dimension7_Id
1596 , Unit_of_Measure_Class
1597 , Dataset_Id
1598 into x_Measure_rec.Measure_id
1599 , x_Measure_rec.Measure_short_name
1600 , x_Measure_rec.Measure_name
1601 , x_Measure_rec.description
1602 , x_Measure_rec.Actual_Data_Source_Type
1603 , x_Measure_rec.Actual_Data_Source
1604 , x_Measure_rec.Function_Name
1605 , x_Measure_rec.Comparison_Source
1606 , x_Measure_rec.Increase_In_Measure
1607 , x_Measure_rec.Enable_Link
1608 , x_Measure_rec.Enabled
1609 , x_Measure_rec.Obsolete --3865711
1610 , x_Measure_rec.Measure_Type
1611 , x_Measure_rec.Dimension1_Id
1612 , x_Measure_rec.Dimension2_Id
1613 , x_Measure_rec.Dimension3_Id
1614 , x_Measure_rec.Dimension4_Id
1615 , x_Measure_rec.Dimension5_Id
1616 , x_Measure_rec.Dimension6_Id
1617 , x_Measure_rec.Dimension7_Id
1618 , x_Measure_rec.Unit_Of_Measure_Class
1619 , x_Measure_rec.Dataset_Id
1620 from bisbv_performance_measures
1621 where measure_id = p_Measure_Rec.Measure_ID;
1622 END IF;
1623 IF (p_Measure_Rec.Measure_Short_Name IS NOT NULL AND
1624 p_Measure_Rec.Measure_Id IS NULL) THEN
1625 Select Measure_id
1626 , Measure_short_name
1627 , Measure_name
1628 , description
1629 , actual_data_source_type
1630 , actual_data_source
1631 , Function_Name
1632 , Comparison_Source
1633 , Increase_In_Measure
1634 , Enable_Link
1635 , Enabled
1636 , Obsolete --3865711
1637 , Measure_Type
1638 , Dimension1_Id
1639 , Dimension2_Id
1640 , Dimension3_Id
1641 , Dimension4_Id
1642 , Dimension5_Id
1643 , Dimension6_Id
1644 , Dimension7_Id
1645 , Unit_of_Measure_Class
1646 , Dataset_Id
1647 into x_Measure_rec.Measure_id
1648 , x_Measure_rec.Measure_short_name
1649 , x_Measure_rec.Measure_name
1650 , x_Measure_rec.description
1651 , x_Measure_rec.Actual_Data_Source_Type
1652 , x_Measure_rec.Actual_Data_Source
1653 , x_Measure_rec.Function_Name
1654 , x_Measure_rec.Comparison_Source
1655 , x_Measure_rec.Increase_In_Measure
1656 , x_Measure_rec.Enable_Link
1657 , x_Measure_rec.Enabled
1658 , x_Measure_rec.Obsolete --3865711
1659 , x_Measure_rec.Measure_Type
1660 , x_Measure_rec.Dimension1_Id
1661 , x_Measure_rec.Dimension2_Id
1662 , x_Measure_rec.Dimension3_Id
1663 , x_Measure_rec.Dimension4_Id
1664 , x_Measure_rec.Dimension5_Id
1665 , x_Measure_rec.Dimension6_Id
1666 , x_Measure_rec.Dimension7_Id
1667 , x_Measure_rec.Unit_Of_Measure_Class
1668 , x_Measure_rec.Dataset_Id
1669 from bisbv_performance_measures
1670 where measure_short_name = p_Measure_Rec.Measure_Short_Name; -- bug fix -- mahesh
1671 END IF;
1672 IF p_all_info = FND_API.G_TRUE THEN
1673
1674 if (BIS_UTILITIES_PUB.Value_Not_Missing(x_Measure_rec.Dimension1_Id)
1675 = FND_API.G_TRUE AND
1676 BIS_UTILITIES_PUB.Value_Not_NULL(x_Measure_rec.Dimension1_Id)
1677 = FND_API.G_TRUE) THEN
1678 l_dimension_rec.dimension_id := x_Measure_rec.Dimension1_Id;
1679 l_Dimension_Rec_p := l_Dimension_Rec;
1680 BIS_DIMENSION_PVT.Retrieve_Dimension
1681 ( p_api_version
1682 , l_Dimension_Rec_p
1683 , l_Dimension_Rec
1684 , x_return_status
1685 , x_error_Tbl
1686 );
1687
1688 x_Measure_rec.Dimension1_Short_Name
1689 := l_dimension_rec.Dimension_Short_Name;
1690 x_Measure_rec.Dimension1_Name := l_dimension_rec.Dimension_Name;
1691 end if;
1692
1693 if (BIS_UTILITIES_PUB.Value_Not_Missing(x_Measure_rec.Dimension2_Id)
1694 = FND_API.G_TRUE AND
1695 x_return_status = FND_API.G_RET_STS_SUCCESS AND
1696 BIS_UTILITIES_PUB.Value_Not_NULL(x_Measure_rec.Dimension2_Id)
1697 = FND_API.G_TRUE) THEN
1698
1699 l_dimension_rec.dimension_id := x_Measure_rec.Dimension2_Id;
1700 l_Dimension_Rec_p := l_Dimension_Rec;
1701 BIS_DIMENSION_PVT.Retrieve_Dimension
1702 ( p_api_version
1703 , l_Dimension_Rec_p
1704 , l_Dimension_Rec
1705 , x_return_status
1706 , x_error_Tbl
1707 );
1708
1709 x_Measure_rec.Dimension2_Short_Name
1710 := l_dimension_rec.Dimension_Short_Name;
1711 x_Measure_rec.Dimension2_Name := l_dimension_rec.Dimension_Name;
1712 else
1713 return;
1714 end if;
1715
1716 if (BIS_UTILITIES_PUB.Value_Not_Missing(x_Measure_rec.Dimension3_Id)
1717 = FND_API.G_TRUE AND
1718 x_return_status = FND_API.G_RET_STS_SUCCESS AND
1719 BIS_UTILITIES_PUB.Value_Not_NULL(x_Measure_rec.Dimension3_Id)
1720 = FND_API.G_TRUE) THEN
1721
1722 l_dimension_rec.dimension_id := x_Measure_rec.Dimension3_Id;
1723 l_Dimension_Rec_p := l_Dimension_Rec;
1724 BIS_DIMENSION_PVT.Retrieve_Dimension
1725 ( p_api_version
1726 , l_Dimension_Rec_p
1727 , l_Dimension_Rec
1728 , x_return_status
1729 , x_error_Tbl
1730 );
1731
1732 x_Measure_rec.Dimension3_Short_Name
1733 := l_dimension_rec.Dimension_Short_Name;
1734 x_Measure_rec.Dimension3_Name := l_dimension_rec.Dimension_Name;
1735 else
1736 return;
1737 end if;
1738
1739 if (BIS_UTILITIES_PUB.Value_Not_Missing(x_Measure_rec.Dimension4_Id)
1740 = FND_API.G_TRUE AND
1741 x_return_status = FND_API.G_RET_STS_SUCCESS AND
1742 BIS_UTILITIES_PUB.Value_Not_NULL(x_Measure_rec.Dimension4_Id)
1743 = FND_API.G_TRUE) THEN
1744
1745 l_dimension_rec.dimension_id := x_Measure_rec.Dimension4_Id;
1746 l_Dimension_Rec_p := l_Dimension_Rec;
1747 BIS_DIMENSION_PVT.Retrieve_Dimension
1748 ( p_api_version
1749 , l_Dimension_Rec_p
1750 , l_Dimension_Rec
1751 , x_return_status
1752 , x_error_Tbl
1753 );
1754
1755 x_Measure_rec.Dimension4_Short_Name
1756 := l_dimension_rec.Dimension_Short_Name;
1757 x_Measure_rec.Dimension4_Name := l_dimension_rec.Dimension_Name;
1758 else
1759 return;
1760 end if;
1761
1762 if (BIS_UTILITIES_PUB.Value_Not_Missing(x_Measure_rec.Dimension5_Id)
1763 = FND_API.G_TRUE AND
1764 x_return_status = FND_API.G_RET_STS_SUCCESS AND
1765 BIS_UTILITIES_PUB.Value_Not_NULL(x_Measure_rec.Dimension5_Id)
1766 = FND_API.G_TRUE) THEN
1767
1768 l_dimension_rec.dimension_id := x_Measure_rec.Dimension5_Id;
1769 l_Dimension_Rec_p := l_Dimension_Rec;
1770 BIS_DIMENSION_PVT.Retrieve_Dimension
1771 ( p_api_version
1772 , l_Dimension_Rec_p
1773 , l_Dimension_Rec
1774 , x_return_status
1775 , x_error_Tbl
1776 );
1777
1778 x_Measure_rec.Dimension5_Short_Name
1779 := l_dimension_rec.Dimension_Short_Name;
1780 x_Measure_rec.Dimension5_Name := l_dimension_rec.Dimension_Name;
1781 else
1782 return;
1783 end if;
1784
1785 if (BIS_UTILITIES_PUB.Value_Not_Missing(x_Measure_rec.Dimension6_Id)
1786 = FND_API.G_TRUE AND
1787 x_return_status = FND_API.G_RET_STS_SUCCESS AND
1788 BIS_UTILITIES_PUB.Value_Not_NULL(x_Measure_rec.Dimension6_Id)
1789 = FND_API.G_TRUE) THEN
1790
1791 l_dimension_rec.dimension_id := x_Measure_rec.Dimension6_Id;
1792 l_Dimension_Rec_p := l_Dimension_Rec;
1793 BIS_DIMENSION_PVT.Retrieve_Dimension
1794 ( p_api_version
1795 , l_Dimension_Rec_p
1796 , l_Dimension_Rec
1797 , x_return_status
1798 , x_error_Tbl
1799 );
1800
1801 x_Measure_rec.Dimension6_Short_Name
1802 := l_dimension_rec.Dimension_Short_Name;
1803 x_Measure_rec.Dimension6_Name := l_dimension_rec.Dimension_Name;
1804 else
1805 return;
1806 end if;
1807
1808 if (BIS_UTILITIES_PUB.Value_Not_Missing(x_Measure_rec.Dimension7_Id)
1809 = FND_API.G_TRUE AND
1810 x_return_status = FND_API.G_RET_STS_SUCCESS AND
1811 BIS_UTILITIES_PUB.Value_Not_NULL(x_Measure_rec.Dimension7_Id)
1812 = FND_API.G_TRUE) THEN
1813
1814 l_dimension_rec.dimension_id := x_Measure_rec.Dimension7_Id;
1815 l_Dimension_Rec_p := l_Dimension_Rec;
1816 BIS_DIMENSION_PVT.Retrieve_Dimension
1817 ( p_api_version
1818 , l_Dimension_Rec_p
1819 , l_Dimension_Rec
1820 , x_return_status
1821 , x_error_Tbl
1822 );
1823
1824 x_Measure_rec.Dimension7_Short_Name
1825 := l_dimension_rec.Dimension_Short_Name;
1826 x_Measure_rec.Dimension7_Name := l_dimension_rec.Dimension_Name;
1827 else
1828 return;
1829 end if;
1830 end if;
1831
1832 --commented RAISE
1833 EXCEPTION
1834 WHEN NO_DATA_FOUND THEN
1835 x_return_status := FND_API.G_RET_STS_ERROR ;
1836 --added the error message
1837 l_error_tbl := x_error_tbl;
1838 BIS_UTILITIES_PVT.Add_Error_Message
1839 ( p_error_msg_name => 'BIS_INVALID_MEASURE_VALUE'
1840 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1841 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Measure'
1842 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1843 , p_error_table => l_error_tbl
1844 , x_error_table => x_error_tbl
1845 );
1846 -- RAISE FND_API.G_EXC_ERROR;
1847 when FND_API.G_EXC_ERROR then
1848 x_return_status := FND_API.G_RET_STS_ERROR ;
1849 -- RAISE FND_API.G_EXC_ERROR;
1850 when FND_API.G_EXC_UNEXPECTED_ERROR then
1851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1852 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1853 when others then
1854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1855 --added last two params
1856 l_error_tbl := x_error_tbl;
1857 BIS_UTILITIES_PVT.Add_Error_Message
1858 ( p_error_msg_id => SQLCODE
1859 , p_error_description => SQLERRM
1860 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Measure'
1861 , p_error_table => l_error_tbl
1862 , x_error_table => x_error_tbl
1863 );
1864 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1865
1866 END Retrieve_Measure;
1867 --
1868 Procedure Update_Indicator_Dimension
1869 ( p_Measure_id number
1870 , p_dimension_id number
1871 , p_sequence_no number
1872 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
1873 , x_return_status OUT NOCOPY VARCHAR2
1874 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1875 )
1876 is
1877 l_user_id number;
1878 l_login_id number;
1879 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1880 BEGIN
1881
1882 if (BIS_UTILITIES_PUB.Value_Missing(p_dimension_id) = FND_API.G_TRUE) then
1883 return;
1884 end if;
1885
1886 l_user_id := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1887 l_login_id := fnd_global.LOGIN_ID;
1888
1889 UPDATE bis_indicator_dimensions
1890 set
1891 Dimension_ID = p_dimension_id
1892 , CREATION_DATE = SYSDATE
1893 , CREATED_BY = l_user_id
1894 , LAST_UPDATE_DATE = SYSDATE
1895 , LAST_UPDATED_BY = l_user_id
1896 , LAST_UPDATE_LOGIN = l_login_id
1897 where INDICATOR_ID = p_Measure_ID
1898 AND SEQUENCE_NO = p_sequence_no;
1899
1900 --commented RAISE
1901 EXCEPTION
1902 WHEN NO_DATA_FOUND THEN
1903 x_return_status := FND_API.G_RET_STS_ERROR ;
1904 --RAISE FND_API.G_EXC_ERROR;
1905 when FND_API.G_EXC_ERROR then
1906 x_return_status := FND_API.G_RET_STS_ERROR ;
1907 -- RAISE FND_API.G_EXC_ERROR;
1908 when FND_API.G_EXC_UNEXPECTED_ERROR then
1909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1910 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1911 when others then
1912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1913 --added last two params
1914 l_error_tbl := x_error_tbl;
1915 BIS_UTILITIES_PVT.Add_Error_Message
1916 ( p_error_msg_id => SQLCODE
1917 , p_error_description => SQLERRM
1918 , p_error_proc_name => G_PKG_NAME||'.Update_Indicator_Dimension'
1919 , p_error_table => l_error_tbl
1920 , x_error_table => x_error_tbl
1921 );
1922 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1923
1924 end Update_Indicator_Dimension;
1925
1926 FUNCTION Dimension_Count(p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type)
1927 return NUMBER
1928 IS
1929 l_count NUMBER := 2;
1930 BEGIN
1931
1932 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension1_id)
1933 = FND_API.G_FALSE
1934 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension1_id)
1935 = FND_API.G_FALSE) then
1936 l_count := 3;
1937 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension2_id)
1938 = FND_API.G_FALSE
1939 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension2_id)
1940 = FND_API.G_FALSE) then
1941 l_count := 4;
1942 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension3_id)
1943 = FND_API.G_FALSE
1944 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension3_id)
1945 = FND_API.G_FALSE) then
1946 l_count := 5;
1947 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension4_id)
1948 = FND_API.G_FALSE
1949 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension4_id)
1950 = FND_API.G_FALSE) then
1951 l_count := 6;
1952 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension5_id)
1953 = FND_API.G_FALSE
1954 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension5_id)
1955 = FND_API.G_FALSE) then
1956 l_count := 7;
1957 end if;
1958 end if;
1959 end if;
1960 end if;
1961 end if;
1962
1963 return l_count;
1964 END Dimension_Count;
1965
1966 --OverLoad Dimension Count so that when loader tries to update a measure
1967 --with an old ldt file, it will not fail
1968 FUNCTION Dimension_Count
1969 (p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
1970 ,p_Org_Dimension_Id IN NUMBER
1971 ,p_Time_Dimension_Id IN NUMBER
1972 )
1973 return NUMBER
1974 IS
1975 l_count NUMBER;
1976 BEGIN
1977
1978 if(IS_OLD_DATA_MODEL(p_Measure_Rec,p_Org_Dimension_Id,p_Time_Dimension_Id)) then
1979 if (BIS_UTILITIES_PUB.Value_Missing(p_Org_Dimension_id)
1980 = FND_API.G_FALSE
1981 AND BIS_UTILITIES_PUB.Value_Null(p_Org_Dimension_id)
1982 = FND_API.G_FALSE) then
1983 l_count := 1;
1984 if (BIS_UTILITIES_PUB.Value_Missing(p_Time_Dimension_id)
1985 = FND_API.G_FALSE
1986 AND BIS_UTILITIES_PUB.Value_Null(p_Time_Dimension_id)
1987 = FND_API.G_FALSE) then
1988 l_count := 2;
1989 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension1_id)
1990 = FND_API.G_FALSE
1991 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension1_id)
1992 = FND_API.G_FALSE) then
1993 l_count := 3;
1994 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension2_id)
1995 = FND_API.G_FALSE
1996 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension2_id)
1997 = FND_API.G_FALSE) then
1998 l_count := 4;
1999 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension3_id)
2000 = FND_API.G_FALSE
2001 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension3_id)
2002 = FND_API.G_FALSE) then
2003 l_count := 5;
2004 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension4_id)
2005 = FND_API.G_FALSE
2006 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension4_id)
2007 = FND_API.G_FALSE) then
2008 l_count := 6;
2009 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension5_id)
2010 = FND_API.G_FALSE
2011 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension5_id)
2012 = FND_API.G_FALSE) then
2013 l_count := 7;
2014 end if;
2015 end if;
2016 end if;
2017 end if;
2018 end if;
2019 end if;
2020 end if;
2021
2022 else
2023 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension1_id)
2024 = FND_API.G_FALSE
2025 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension1_id)
2026 = FND_API.G_FALSE) then
2027 l_count := 1;
2028 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension2_id)
2029 = FND_API.G_FALSE
2030 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension2_id)
2031 = FND_API.G_FALSE) then
2032 l_count := 2;
2033 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension3_id)
2034 = FND_API.G_FALSE
2035 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension3_id)
2036 = FND_API.G_FALSE) then
2037 l_count := 3;
2038 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension4_id)
2039 = FND_API.G_FALSE
2040 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension4_id)
2041 = FND_API.G_FALSE) then
2042 l_count := 4;
2043 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension5_id)
2044 = FND_API.G_FALSE
2045 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension5_id)
2046 = FND_API.G_FALSE) then
2047 l_count := 5;
2048 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension6_id)
2049 = FND_API.G_FALSE
2050 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension6_id)
2051 = FND_API.G_FALSE) then
2052 l_count := 6;
2053 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Rec.Dimension7_id)
2054 = FND_API.G_FALSE
2055 AND BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Dimension7_id)
2056 = FND_API.G_FALSE) then
2057 l_count := 7;
2058 end if;
2059 end if;
2060 end if;
2061 end if;
2062 end if;
2063 end if;
2064 end if;
2065
2066 end if;
2067
2068 return l_count;
2069 END Dimension_Count;
2070
2071 PROCEDURE UpdateMeasureRec -- Changed for 2784713
2072 ( p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
2073 , p_Measure_Rec1 IN BIS_MEASURE_PUB.Measure_Rec_Type
2074 , x_Measure_Rec OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
2075 )
2076 IS
2077
2078 BEGIN
2079
2080 x_measure_rec := p_Measure_Rec1;
2081
2082 x_measure_rec.Measure_Short_Name := p_measure_rec.Measure_Short_Name;
2083 x_measure_rec.Measure_Name := p_measure_rec.Measure_Name;
2084 x_measure_rec.Description := p_measure_rec.Description;
2085 x_measure_rec.Actual_Data_Source_Type := p_measure_rec.Actual_Data_Source_Type;
2086 x_measure_rec.Actual_Data_Source := p_measure_rec.Actual_Data_Source;
2087 x_measure_rec.Function_Name := p_measure_rec.Function_Name;
2088 x_measure_rec.Comparison_Source := p_measure_rec.Comparison_Source;
2089 x_measure_rec.Increase_In_Measure := p_measure_rec.Increase_In_Measure;
2090 x_measure_rec.Enable_Link := p_measure_rec.Enable_Link;
2091 x_measure_rec.Enabled := p_measure_rec.Enabled;
2092 x_measure_rec.Obsolete := p_measure_rec.Obsolete; --3865711
2093 x_measure_rec.Measure_Type := p_measure_rec.Measure_Type;
2094 x_measure_rec.Application_Id := p_measure_rec.Application_Id;
2095 x_measure_rec.Unit_Of_Measure_Class := p_measure_rec.Unit_Of_Measure_Class;
2096
2097
2098 x_measure_rec.Dimension1_ID := return_value_if_not_missing
2099 ( p_number => p_measure_rec.Dimension1_ID );
2100
2101 x_measure_rec.Dimension1_Short_Name := return_value_if_not_missing
2102 ( p_varchar2 => p_measure_rec.Dimension1_Short_Name );
2103
2104 x_measure_rec.Dimension1_Name := return_value_if_not_missing
2105 ( p_varchar2 => p_measure_rec.Dimension1_Name );
2106
2107
2108
2109 x_measure_rec.Dimension2_ID := return_value_if_not_missing
2110 ( p_number => p_measure_rec.Dimension2_ID );
2111
2112 x_measure_rec.Dimension2_Short_Name := return_value_if_not_missing
2113 ( p_varchar2 => p_measure_rec.Dimension2_Short_Name );
2114
2115 x_measure_rec.Dimension2_Name := return_value_if_not_missing
2116 ( p_varchar2 => p_measure_rec.Dimension2_Name );
2117
2118
2119
2120 x_measure_rec.Dimension3_ID := return_value_if_not_missing
2121 ( p_number => p_measure_rec.Dimension3_ID );
2122
2123 x_measure_rec.Dimension3_Short_Name := return_value_if_not_missing
2124 ( p_varchar2 => p_measure_rec.Dimension3_Short_Name );
2125
2126 x_measure_rec.Dimension3_Name := return_value_if_not_missing
2127 ( p_varchar2 => p_measure_rec.Dimension3_Name );
2128
2129
2130
2131 x_measure_rec.Dimension4_ID := return_value_if_not_missing
2132 ( p_number => p_measure_rec.Dimension4_ID );
2133
2134 x_measure_rec.Dimension4_Short_Name := return_value_if_not_missing
2135 ( p_varchar2 => p_measure_rec.Dimension4_Short_Name );
2136
2137 x_measure_rec.Dimension4_Name := return_value_if_not_missing
2138 ( p_varchar2 => p_measure_rec.Dimension4_Name );
2139
2140
2141
2142 x_measure_rec.Dimension5_ID := return_value_if_not_missing
2143 ( p_number => p_measure_rec.Dimension5_ID );
2144
2145 x_measure_rec.Dimension5_Short_Name := return_value_if_not_missing
2146 ( p_varchar2 => p_measure_rec.Dimension5_Short_Name );
2147
2148 x_measure_rec.Dimension5_Name := return_value_if_not_missing
2149 ( p_varchar2 => p_measure_rec.Dimension5_Name );
2150
2151
2152
2153 x_measure_rec.Dimension6_ID := return_value_if_not_missing
2154 ( p_number => p_measure_rec.Dimension6_ID );
2155
2156 x_measure_rec.Dimension6_Short_Name := return_value_if_not_missing
2157 ( p_varchar2 => p_measure_rec.Dimension6_Short_Name );
2158
2159 x_measure_rec.Dimension6_Name := return_value_if_not_missing
2160 ( p_varchar2 => p_measure_rec.Dimension6_Name );
2161
2162
2163
2164 x_measure_rec.Dimension7_ID := return_value_if_not_missing
2165 ( p_number => p_measure_rec.Dimension7_ID );
2166
2167 x_measure_rec.Dimension7_Short_Name := return_value_if_not_missing
2168 ( p_varchar2 => p_measure_rec.Dimension7_Short_Name );
2169
2170 x_measure_rec.Dimension7_Name := return_value_if_not_missing
2171 ( p_varchar2 => p_measure_rec.Dimension7_Name );
2172
2173 --
2174 END UpdateMeasureRec;
2175
2176
2177 PROCEDURE ArrangeDimensions -- Added for 2784713
2178 ( p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
2179 , x_Measure_Rec OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
2180 ) IS
2181
2182 i NUMBER;
2183 j NUMBER;
2184 l_dim_tbl BIS_DIMENSION_PUB.Dimension_Tbl_Type;
2185 l_arranged_dim_tbl BIS_DIMENSION_PUB.Dimension_Tbl_Type;
2186 l_number_of_dims NUMBER := 0;
2187
2188 BEGIN
2189
2190 x_Measure_Rec := p_Measure_Rec ;
2191
2192 -- see if there are any dimesnions to be deleted
2193 -- bump all the remaining dimension together
2194
2195 l_dim_tbl(1).dimension_id := x_measure_rec.Dimension1_ID;
2196 l_dim_tbl(2).dimension_id := x_measure_rec.Dimension2_ID;
2197 l_dim_tbl(3).dimension_id := x_measure_rec.Dimension3_ID;
2198 l_dim_tbl(4).dimension_id := x_measure_rec.Dimension4_ID;
2199 l_dim_tbl(5).dimension_id := x_measure_rec.Dimension5_ID;
2200 l_dim_tbl(6).dimension_id := x_measure_rec.Dimension6_ID;
2201 l_dim_tbl(7).dimension_id := x_measure_rec.Dimension7_ID;
2202
2203
2204 j := 1;
2205
2206 FOR i IN 1..7 LOOP -- Dimensions ab_de_f become abdef__
2207
2208 IF ( bis_utilities_pvt.Value_Not_Missing
2209 (p_value => l_dim_tbl(i).Dimension_id ) = FND_API.G_TRUE ) THEN
2210
2211 l_arranged_dim_tbl(j).Dimension_id := l_dim_tbl(i).Dimension_id;
2212 j := j+1;
2213
2214 END IF;
2215
2216 END LOOP;
2217
2218 -- mdamle 4/23/2003 - PMD - Measure Definer - When a measure is created
2219 -- through PMD, Dimensions may not be assigned at that time.
2220 -- Hence LAST causes an exception for empty table.
2221 -- l_number_of_dims := l_arranged_dim_tbl.LAST;
2222 l_number_of_dims := l_arranged_dim_tbl.COUNT;
2223
2224 FOR i IN (l_number_of_dims+1)..7 LOOP
2225 l_arranged_dim_tbl(i).Dimension_id := NULL;
2226 END LOOP;
2227
2228
2229 x_measure_rec.Dimension1_ID := l_arranged_dim_tbl(1).dimension_id;
2230 x_measure_rec.Dimension2_ID := l_arranged_dim_tbl(2).dimension_id;
2231 x_measure_rec.Dimension3_ID := l_arranged_dim_tbl(3).dimension_id;
2232 x_measure_rec.Dimension4_ID := l_arranged_dim_tbl(4).dimension_id;
2233 x_measure_rec.Dimension5_ID := l_arranged_dim_tbl(5).dimension_id;
2234 x_measure_rec.Dimension6_ID := l_arranged_dim_tbl(6).dimension_id;
2235 x_measure_rec.Dimension7_ID := l_arranged_dim_tbl(7).dimension_id;
2236
2237 END ArrangeDimensions ;
2238
2239
2240 FUNCTION return_value_if_not_missing -- 2784713
2241 ( p_number IN NUMBER )
2242 RETURN NUMBER IS
2243 BEGIN
2244
2245 IF ( bis_utilities_pvt.Value_Missing
2246 (p_value => p_number ) = FND_API.G_TRUE ) THEN
2247 RETURN NULL;
2248 ELSE
2249 RETURN p_number;
2250 END IF;
2251
2252 EXCEPTION
2253 WHEN OTHERS THEN
2254 RETURN NULL;
2255 END;
2256
2257
2258 FUNCTION return_value_if_not_missing -- 2784713
2259 ( p_varchar2 IN VARCHAR2 )
2260 RETURN VARCHAR2 IS
2261 BEGIN
2262
2263 IF ( bis_utilities_pvt.Value_Missing
2264 (p_value => p_varchar2 ) = FND_API.G_TRUE ) THEN
2265 RETURN NULL;
2266 ELSE
2267 RETURN p_varchar2;
2268 END IF;
2269
2270 EXCEPTION
2271 WHEN OTHERS THEN
2272 RETURN NULL;
2273 END;
2274
2275
2276 PROCEDURE Update_Measure_Rec_Total -- 2664898
2277 ( p_Measure_Rec_orig IN BIS_MEASURE_PUB.Measure_Rec_Type
2278 , p_Measure_Rec_new IN BIS_MEASURE_PUB.Measure_Rec_Type
2279 , x_Measure_Rec OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
2280 )
2281 IS
2282
2283 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
2284
2285 BEGIN
2286
2287 UpdateMeasureRecNoDim
2288 ( p_Measure_Rec => p_Measure_Rec_new
2289 , p_Measure_Rec1 => p_Measure_Rec_Orig
2290 , x_Measure_Rec => l_Measure_Rec
2291 );
2292
2293 Update_Measure_Rec_Dims
2294 ( p_Measure_Rec_orig => l_Measure_Rec
2295 , p_Measure_Rec_new => p_Measure_Rec_new
2296 , x_Measure_Rec => x_Measure_Rec
2297 );
2298
2299 END Update_Measure_Rec_Total;
2300
2301
2302
2303 --
2304 --
2305 --Overload UpdateMeasureRec if loader is trying to Update
2306 -- Measure from old ldt allow update only of Measure Name and Description
2307 PROCEDURE UpdateMeasureRecNoDim
2308 ( p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
2309 , p_Measure_Rec1 IN BIS_MEASURE_PUB.Measure_Rec_Type
2310 , x_Measure_Rec OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
2311 )
2312 IS
2313 l_ret VARCHAR2(32000);
2314 l_dim_tbl BIS_DIMENSION_PUB.Dimension_Tbl_Type;
2315 BEGIN
2316
2317 x_measure_rec := p_Measure_Rec1;
2318
2319 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.Measure_Name);
2320 IF (l_ret = FND_API.G_FALSE) THEN
2321 x_measure_rec.Measure_Name := p_measure_rec.Measure_Name;
2322 END IF;
2323
2324 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.Description);
2325 IF (l_ret = FND_API.G_FALSE) THEN
2326 x_measure_rec.Description := p_measure_rec.Description;
2327 END IF;
2328
2329 -- Added for P1 2565752
2330 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.Unit_Of_Measure_Class);
2331 IF (l_ret = FND_API.G_FALSE) THEN
2332 x_measure_rec.Unit_Of_Measure_Class := p_measure_rec.Unit_Of_Measure_Class;
2333 END IF;
2334
2335 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.ACTUAL_DATA_SOURCE_TYPE);
2336 IF (l_ret = FND_API.G_FALSE) THEN
2337 x_measure_rec.ACTUAL_DATA_SOURCE_TYPE := p_measure_rec.ACTUAL_DATA_SOURCE_TYPE;
2338 END IF;
2339
2340 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.ACTUAL_DATA_SOURCE);
2341 IF (l_ret = FND_API.G_FALSE) THEN
2342 x_measure_rec.ACTUAL_DATA_SOURCE := p_measure_rec.ACTUAL_DATA_SOURCE;
2343 END IF;
2344
2345 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.FUNCTION_NAME);
2346 IF (l_ret = FND_API.G_FALSE) THEN
2347 x_measure_rec.FUNCTION_NAME := p_measure_rec.FUNCTION_NAME;
2348 END IF;
2349
2350 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.COMPARISON_SOURCE);
2351 IF (l_ret = FND_API.G_FALSE) THEN
2352 x_measure_rec.COMPARISON_SOURCE := p_measure_rec.COMPARISON_SOURCE;
2353 END IF;
2354
2355
2356 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.INCREASE_IN_MEASURE);
2357 IF (l_ret = FND_API.G_FALSE) THEN
2358 x_measure_rec.INCREASE_IN_MEASURE := p_measure_rec.INCREASE_IN_MEASURE;
2359 END IF;
2360
2361 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.ENABLE_LINK);
2362 IF (l_ret = FND_API.G_FALSE) THEN
2363 x_measure_rec.ENABLE_LINK := p_measure_rec.ENABLE_LINK;
2364 END IF;
2365 -- 3031053
2366 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.ENABLED);
2367 IF (l_ret = FND_API.G_FALSE) THEN
2368 x_measure_rec.ENABLED := p_measure_rec.ENABLED;
2369 END IF;
2370 --3031053
2371 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.Obsolete); --3865711
2372 IF (l_ret = FND_API.G_FALSE) THEN
2373 x_measure_rec.Obsolete := p_measure_rec.Obsolete;
2374 END IF;
2375 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.Measure_Type); --3865711
2376 IF (l_ret = FND_API.G_FALSE) THEN
2377 x_measure_rec.Measure_Type := p_measure_rec.Measure_Type;
2378 END IF;
2379 --- 2465354
2380 l_ret := BIS_UTILITIES_PUB.Value_Missing(p_measure_rec.Application_Id);
2381 IF (l_ret = FND_API.G_FALSE) THEN
2382 x_measure_rec.Application_Id := p_measure_rec.Application_Id;
2383 END IF;
2384 --- 2465354
2385
2386
2387 END UpdateMeasureRecNoDim;
2388
2389
2390
2391 -- Updating the dimensions of the measures too...
2392 -- This is for P1 bug reported by BIL team bug 2664898
2393 PROCEDURE Update_Measure_Rec_Dims
2394 ( p_Measure_Rec_orig IN BIS_MEASURE_PUB.Measure_Rec_Type
2395 , p_Measure_Rec_new IN BIS_MEASURE_PUB.Measure_Rec_Type
2396 , x_Measure_Rec OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
2397 )
2398 IS
2399 BEGIN
2400
2401 x_Measure_Rec := p_Measure_Rec_orig;
2402
2403 x_measure_rec.dimension1_id := get_dimension_ID
2404 ( p_dim_id => p_Measure_Rec_new.dimension1_id);
2405
2406 x_measure_rec.dimension2_id := get_dimension_ID
2407 ( p_dim_id => p_Measure_Rec_new.dimension2_id);
2408
2409 x_measure_rec.dimension3_id := get_dimension_ID
2410 ( p_dim_id => p_Measure_Rec_new.dimension3_id);
2411
2412 x_measure_rec.dimension4_id := get_dimension_ID
2413 ( p_dim_id => p_Measure_Rec_new.dimension4_id);
2414
2415 x_measure_rec.dimension5_id := get_dimension_ID
2416 ( p_dim_id => p_Measure_Rec_new.dimension5_id);
2417
2418 x_measure_rec.dimension6_id := get_dimension_ID
2419 ( p_dim_id => p_Measure_Rec_new.dimension6_id);
2420
2421 x_measure_rec.dimension7_id := get_dimension_ID
2422 ( p_dim_id => p_Measure_Rec_new.dimension7_id);
2423
2424 END Update_Measure_Rec_Dims;
2425
2426
2427 FUNCTION get_dimension_ID -- 2664898
2428 ( p_dim_id IN NUMBER )
2429 RETURN NUMBER IS
2430
2431 l_is_missing VARCHAR2(300);
2432 l_is_null VARCHAR2(300);
2433
2434 BEGIN
2435
2436 l_is_missing := BIS_UTILITIES_PUB.Value_Missing(p_dim_id);
2437 l_is_null := BIS_UTILITIES_PUB.Value_Null(p_dim_id);
2438
2439 IF ( (l_is_missing = FND_API.G_TRUE)
2440 OR (l_is_null = FND_API.G_TRUE)
2441 ) THEN
2442 RETURN NULL;
2443 ELSE
2444 RETURN p_dim_id;
2445 END IF;
2446
2447 EXCEPTION
2448 WHEN OTHERS THEN
2449 RETURN NULL;
2450 END;
2451
2452
2453 --
2454 -- PLEASE VERIFY COMMENT BELOW
2455 -- Update_Measures one Measure if
2456 -- 1) no Measure levels or targets exist
2457 -- 2) no users have selected to see actuals for the Measure
2458 Procedure Update_Measure
2459 ( p_api_version IN NUMBER
2460 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2461 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2462 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
2463 , x_return_status OUT NOCOPY VARCHAR2
2464 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2465 )
2466 IS
2467
2468 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2469 BEGIN
2470
2471 BIS_MEASURE_PVT.Update_Measure
2472 ( p_api_version => p_api_version
2473 , p_commit => p_commit
2474 , p_validation_level => p_validation_level
2475 , p_Measure_Rec => p_Measure_Rec
2476 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
2477 , x_return_status => x_return_status
2478 , x_error_Tbl => x_error_Tbl
2479 );
2480
2481 --commented RAISE
2482 EXCEPTION
2483 when others then
2484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2485 --added last two params
2486 l_error_tbl := x_error_tbl;
2487 BIS_UTILITIES_PVT.Add_Error_Message
2488 ( p_error_msg_id => SQLCODE
2489 , p_error_description => SQLERRM
2490 , p_error_proc_name => G_PKG_NAME||'.Update_Measure'
2491 , p_error_table => l_error_tbl
2492 , x_error_table => x_error_tbl
2493 );
2494 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2495
2496 END Update_Measure;
2497
2498 --
2499 -- PLEASE VERIFY COMMENT BELOW
2500 -- Update_Measures one Measure if
2501 -- 1) no Measure levels or targets exist
2502 -- 2) no users have selected to see actuals for the Measure
2503 Procedure Update_Measure -- Changed for 2784713
2504 ( p_api_version IN NUMBER
2505 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2506 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2507 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
2508 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
2509 , x_return_status OUT NOCOPY VARCHAR2
2510 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2511 )
2512 IS
2513 l_user_id number;
2514 l_login_id number;
2515 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
2516 l_Measure_Rec1 BIS_MEASURE_PUB.Measure_Rec_Type;
2517 l_Measure_Rec_Orig BIS_MEASURE_PUB.Measure_Rec_Type;
2518 l_Measure_Rec_Overlap BIS_MEASURE_PUB.Measure_Rec_Type;
2519 l_count NUMBER := 0;
2520 l_Measure_Id NUMBER;
2521 l_application_rec BIS_APPLICATION_PVT.Application_rec_type; --2465354
2522 l_own_appl VARCHAR2(100) := FND_API.G_FALSE ; --2465354
2523 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2524 l_Mapped_measure bis_indicators_tl.NAME%TYPE;
2525 l_Return_Status VARCHAR2(2000);
2526
2527 BEGIN
2528 -- l_measure_rec := p_measure_rec;
2529
2530 Validate_Measure( p_api_version
2531 , p_validation_level
2532 , p_Measure_Rec
2533 , p_owner
2534 , x_return_status
2535 , x_error_Tbl
2536 );
2537
2538 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2539 RAISE FND_API.G_EXC_ERROR;
2540 END IF;
2541
2542 --
2543 BIS_MEASURE_PVT.Retrieve_Measure
2544 ( p_api_version => 1.0
2545 , p_Measure_Rec => p_Measure_Rec
2546 , p_all_info => FND_API.G_TRUE
2547 , x_Measure_Rec => l_measure_rec_Orig
2548 , x_return_status => x_return_status
2549 , x_error_Tbl => x_error_Tbl
2550 );
2551
2552 -- mdamle 07/18/2003 - Allow enable_link = Y only if function_name is not null
2553 if (p_measure_rec.function_name is null and p_measure_rec.enable_link = 'Y') then
2554 l_error_tbl := x_error_tbl;
2555 BIS_UTILITIES_PVT.Add_Error_Message
2556 ( p_error_msg_name => 'BIS_PMF_ENABLE_LINK_ERR'
2557 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2558 , p_error_proc_name => G_PKG_NAME||'.Update_Measure'
2559 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2560 , p_error_table => l_error_tbl
2561 , x_error_table => x_error_tbl
2562 );
2563 RAISE FND_API.G_EXC_ERROR;
2564 end if;
2565
2566 -- ankgoel: bug#3557236
2567 IF (p_Measure_rec.is_validate <> FND_API.G_FALSE) THEN
2568 -- mdamle 07/18/2003 - Check if measure is being mapped to a source
2569 -- that's already mapped to another measure.
2570 if isSourceColumnMappedAlready(p_Measure_rec, l_Mapped_measure) then
2571 l_error_tbl := x_error_tbl;
2572
2573 BIS_UTILITIES_PVT.Add_Error_Message
2574 ( p_error_msg_name => 'BIS_PMF_SOURCE_MAPPING_ERR'
2575 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2576 , p_error_proc_name => G_PKG_NAME||'.Update_Measure'
2577 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2578 , p_token1 => 'MEASURE'
2579 , p_value1 => l_Mapped_measure
2580 , p_error_table => l_error_tbl
2581 , x_error_table => x_error_tbl
2582 );
2583 RAISE FND_API.G_EXC_ERROR;
2584 end if;
2585
2586 --sawu: 9/2/04: need to validate compare-to column also for bug#3859267
2587 if isCompareColumnMappedAlready(p_Measure_rec, l_Mapped_measure) then
2588 l_error_tbl := x_error_tbl;
2589 BIS_UTILITIES_PVT.Add_Error_Message
2590 ( p_error_msg_name => 'BIS_PMF_COLUMN_MAPPING_ERR'
2591 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2592 , p_error_proc_name => G_PKG_NAME||'.Update_Measure'
2593 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2594 , p_token1 => 'MEASURE'
2595 , p_value1 => l_Mapped_measure
2596 , p_error_table => l_error_tbl
2597 , x_error_table => x_error_tbl
2598 );
2599
2600 RAISE FND_API.G_EXC_ERROR;
2601 end if;
2602 END IF;
2603
2604 l_Measure_Id := l_measure_rec_Orig.measure_id;
2605
2606 if (l_measure_id is NOT NULL) then
2607 --added the first condition
2608 if (p_Measure_Rec.Measure_Id is NULL or l_measure_id <> p_Measure_Rec.Measure_Id) then
2609 --added last two params
2610 --changed error message-used to be 'BIS_MEASURE_SHORT_NAME_UNIQUE'
2611 l_error_tbl := x_error_tbl;
2612 BIS_UTILITIES_PVT.Add_Error_Message
2613 ( p_error_msg_name => 'BIS_INVALID_MEASURE_VALUE'
2614 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2615 , p_error_proc_name => G_PKG_NAME||'.Update_Measure'
2616 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2617 , p_error_table => l_error_tbl
2618 , x_error_table => x_error_tbl
2619 );
2620
2621 RAISE FND_API.G_EXC_ERROR;
2622 end if;
2623 end if;
2624 --
2625 BIS_TARGET_LEVEL_PVT.Count_Target_Levels( p_api_version =>1.0
2626 , p_Measure_Rec =>p_Measure_Rec
2627 , x_count =>l_count
2628 , x_return_status =>x_return_status
2629 , x_error_Tbl =>x_error_Tbl
2630 );
2631
2632 -- ankgoel: bug#3922308: This part of validation will only run for ldt uploads.
2633 -- The control coming from JAVA layer doesn't need this validation since all logic
2634 -- is present at JAVA layer itself.
2635 IF ( (l_count > 0) AND (p_Measure_rec.is_validate = FND_API.G_FALSE) AND
2636 ( NVL(l_Measure_Rec_Orig.dimension1_id,1) <> NVL(p_Measure_Rec.dimension1_id,1) OR
2637 NVL(l_Measure_Rec_Orig.dimension2_id,2) <> NVL(p_Measure_Rec.dimension2_id,2) OR
2638 NVL(l_Measure_Rec_Orig.dimension3_id,3) <> NVL(p_Measure_Rec.dimension3_id,3) OR
2639 NVL(l_Measure_Rec_Orig.dimension4_id,4) <> NVL(p_Measure_Rec.dimension4_id,4) OR
2640 NVL(l_Measure_Rec_Orig.dimension5_id,5) <> NVL(p_Measure_Rec.dimension5_id,5) OR
2641 NVL(l_Measure_Rec_Orig.dimension6_id,6) <> NVL(p_Measure_Rec.dimension6_id,6) OR
2642 NVL(l_Measure_Rec_Orig.dimension7_id,7) <> NVL(p_Measure_Rec.dimension7_id,7)
2643 )
2644 ) THEN
2645 l_error_tbl := x_error_tbl;
2646 BIS_UTILITIES_PVT.Add_Error_Message
2647 ( p_error_msg_name => 'BIS_NO_DIMENSION_CHANGE'
2648 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2649 , p_error_proc_name => G_PKG_NAME||'.Update_Measure'
2650 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2651 , p_error_table => l_error_tbl
2652 , x_error_table => x_error_tbl
2653 );
2654
2655 RAISE FND_API.G_EXC_ERROR;
2656 END IF;
2657
2658 -- ankgoel: bug#3922308: Now that the user can update the actual data source
2659 -- for a measure, however the new souce having the same set of dimensions,
2660 -- it may happen that the order of set of dimensions is modified.
2661 -- Get dimensions from the original record in that order, so that bis_indicator_dimensions
2662 -- will still have the same sequence of dimensions.
2663 -- This will be done only if the measure has an existing summary level.
2664 IF ((l_count > 0) AND (p_Measure_rec.is_validate <> FND_API.G_FALSE)) THEN
2665 GetOriginalDimensions( p_Measure_Rec_Orig => l_Measure_Rec_Orig
2666 , p_Measure_Rec => p_Measure_Rec
2667 , x_Measure_Rec_Overlap => l_Measure_Rec_Overlap
2668 );
2669 ELSE
2670 l_Measure_Rec_Overlap := p_Measure_Rec;
2671 END IF;
2672
2673 -- retrieve record from database and apply changes
2674 UpdateMeasureRec
2675 ( p_Measure_Rec => l_Measure_Rec_Overlap
2676 , p_Measure_Rec1 => l_Measure_Rec_Orig
2677 , x_Measure_Rec => l_Measure_Rec1
2678 );
2679
2680 ArrangeDimensions -- Added for 2784713
2681 ( p_Measure_Rec => l_measure_rec1
2682 , x_Measure_Rec => l_measure_rec
2683 ) ;
2684
2685 l_user_id := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
2686 l_login_id := fnd_global.LOGIN_ID;
2687
2688 l_Measure_Rec.Last_Updated_By := l_user_id;
2689 l_Measure_Rec.Last_Update_Login := l_login_id;
2690
2691 l_Measure_Rec.Last_Update_Date := p_Measure_Rec.Last_Update_Date;
2692 IF (l_Measure_Rec.Last_Update_Date IS NULL) THEN
2693 l_Measure_Rec.Last_Update_Date := SYSDATE;
2694 END IF;
2695
2696 Update bis_indicators
2697 set
2698 SHORT_NAME = l_Measure_Rec.Measure_Short_Name
2699 , UOM_Class = l_Measure_Rec.Unit_Of_Measure_Class
2700 , ACTUAL_DATA_SOURCE_TYPE = l_Measure_Rec.Actual_Data_Source_Type
2701 , ACTUAL_DATA_SOURCE = l_Measure_Rec.Actual_Data_Source
2702 , FUNCTION_NAME = l_Measure_Rec.Function_Name
2703 , COMPARISON_SOURCE = l_Measure_Rec.Comparison_Source
2704 , INCREASE_IN_MEASURE = l_Measure_Rec.Increase_In_Measure
2705 , ENABLE_LINK = NVL(l_Measure_Rec.Enable_Link, 'N')
2706 , ENABLED = l_Measure_Rec.enabled -- #3031053
2707 , OBSOLETE = l_Measure_Rec.Obsolete --3865711
2708 , MEASURE_TYPE = l_Measure_Rec.Measure_Type
2709 , LAST_UPDATE_DATE = l_Measure_Rec.Last_Update_Date
2710 , LAST_UPDATED_BY = l_user_id
2711 , LAST_UPDATE_LOGIN = l_login_id
2712 where INDICATOR_ID = l_Measure_Rec.Measure_Id;
2713
2714 BIS_MEASURE_PVT.Translate_measure
2715 ( p_api_version => p_api_version
2716 , p_commit => p_commit
2717 , p_validation_level => p_validation_level
2718 , p_Measure_Rec => l_Measure_Rec
2719 , p_owner => p_owner
2720 , x_return_status => x_return_status
2721 , x_error_Tbl => x_error_Tbl
2722 );
2723
2724 -- delete all the dimension links and reinsert the new ones
2725 delete from bis_indicator_dimensions
2726 where indicator_id = l_measure_rec.Measure_id;
2727
2728 Create_Indicator_Dimensions(l_measure_rec,p_owner,x_return_status,x_error_tbl);
2729
2730 /*ankgoel: bug#3583357
2731 Added for re-sequencing the dimensions for old style ldt upload
2732 with org and time levels.
2733 */
2734 IF (l_count > 0) THEN
2735 BIS_PMF_MIGRATION_PVT.resequence_ind_dimensions(l_Measure_Rec.Measure_Id);
2736 END IF;
2737
2738 --2465354
2739 l_application_rec.Application_id := l_Measure_Rec.Application_Id;
2740 IF (NVL(l_application_rec.Application_id,-1) <> -1 ) THEN
2741 l_own_appl := FND_API.G_TRUE;
2742 END IF;
2743
2744 Update_Application_Measure
2745 ( p_api_version => p_api_version
2746 , p_commit => p_commit
2747 , p_Measure_rec => l_Measure_rec
2748 , p_application_rec => l_application_rec
2749 , p_owning_application => l_own_appl
2750 , p_owner => p_owner
2751 , x_return_status => x_return_status
2752 , x_error_Tbl => x_error_Tbl
2753 );
2754 --2465354
2755
2756
2757 -- rpenneru 12/22/04 Update Functional Area short name in Measure Extension
2758 IF (p_Measure_Rec.Func_Area_Short_Name IS NOT NULL ) THEN
2759 l_Measure_Rec.Func_Area_Short_Name := p_Measure_Rec.Func_Area_Short_Name;
2760 Load_Measure_Extension
2761 ( p_api_version => p_api_version
2762 ,p_commit => p_commit
2763 , p_Measure_Rec => l_Measure_rec
2764 , p_owner => p_owner
2765 , x_return_status => l_Return_Status
2766 , x_error_Tbl => x_error_Tbl
2767 );
2768 IF (l_return_status IS NOT NULL AND l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2769 RAISE FND_API.G_EXC_ERROR;
2770 END IF;
2771 END IF;
2772
2773 if (p_commit = FND_API.G_TRUE) then
2774 COMMIT;
2775 end if;
2776
2777 --commented RAISE
2778 EXCEPTION
2779 WHEN NO_DATA_FOUND THEN
2780 x_return_status := FND_API.G_RET_STS_ERROR ;
2781 --RAISE FND_API.G_EXC_ERROR;
2782 when FND_API.G_EXC_ERROR then
2783 x_return_status := FND_API.G_RET_STS_ERROR ;
2784 -- RAISE FND_API.G_EXC_ERROR;
2785 when FND_API.G_EXC_UNEXPECTED_ERROR then
2786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2787 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2788 when others then
2789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2790 --added last two params
2791 l_error_tbl := x_error_tbl;
2792 BIS_UTILITIES_PVT.Add_Error_Message
2793 ( p_error_msg_id => SQLCODE
2794 , p_error_description => SQLERRM
2795 , p_error_proc_name => G_PKG_NAME||'.Update_Measure'
2796 , p_error_table => l_error_tbl
2797 , x_error_table => x_error_tbl
2798 );
2799 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2800
2801 END Update_Measure;
2802
2803 --
2804 --
2805 --Overload Update_Measure so that old data model ldts can be uploaded using
2806 --The latest lct file. The lct file can call Load_Measure which calls this
2807 --by passing in Org and Time dimension short_names also
2808 Procedure Update_Measure
2809 ( p_api_version IN NUMBER
2810 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2811 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2812 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
2813 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
2814 , p_Org_Dimension_ID IN NUMBER
2815 , p_Time_Dimension_ID IN NUMBER
2816 , x_return_status OUT NOCOPY VARCHAR2
2817 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2818 )
2819 IS
2820 l_user_id number;
2821 l_login_id number;
2822 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
2823 l_Measure_Rec_Orig BIS_MEASURE_PUB.Measure_Rec_Type;
2824 l_count NUMBER := 0;
2825 l_Measure_Id NUMBER;
2826 l_application_rec BIS_APPLICATION_PVT.Application_rec_type; --2465354
2827 l_own_appl VARCHAR2(100) := FND_API.G_FALSE ; --2465354
2828 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2829 BEGIN
2830 -- l_measure_rec := p_measure_rec;
2831
2832 Validate_Measure( p_api_version
2833 , p_validation_level
2834 , p_Measure_Rec
2835 , p_owner
2836 , x_return_status
2837 , x_error_Tbl
2838 );
2839
2840 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2841 RAISE FND_API.G_EXC_ERROR;
2842 END IF;
2843
2844 --added this call to validate Org Dimension
2845 BIS_MEASURE_VALIDATE_PVT.Validate_Dimension_Id
2846 ( p_api_version => p_api_version
2847 , p_dimension_id => p_Org_Dimension_ID
2848 , p_dimension_short_name => FND_API.G_MISS_CHAR
2849 , x_return_status => x_return_status
2850 , x_error_Tbl => x_error_Tbl
2851 );
2852 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2853 RAISE FND_API.G_EXC_ERROR;
2854 END IF;
2855
2856 --added this call to validate Time Dimension
2857 BIS_MEASURE_VALIDATE_PVT.Validate_Dimension_Id
2858 ( p_api_version => p_api_version
2859 , p_dimension_id => p_Time_Dimension_ID
2860 , p_dimension_short_name => FND_API.G_MISS_CHAR
2861 , x_return_status => x_return_status
2862 , x_error_Tbl => x_error_Tbl
2863 );
2864 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2865 RAISE FND_API.G_EXC_ERROR;
2866 END IF;
2867 --
2868 BIS_MEASURE_PVT.Retrieve_Measure
2869 ( p_api_version => 1.0
2870 , p_Measure_Rec => p_Measure_Rec
2871 , p_all_info => FND_API.G_FALSE
2872 , x_Measure_Rec => l_measure_rec_Orig
2873 , x_return_status => x_return_status
2874 , x_error_Tbl => x_error_Tbl
2875 );
2876
2877 l_Measure_Id := l_measure_rec_Orig.measure_id;
2878
2879 if (l_measure_id is NOT NULL) then
2880 --added the first condition
2881 if (p_Measure_Rec.Measure_Id is NULL or l_measure_id <> p_Measure_Rec.Measure_Id) then
2882 --added last two params
2883 --changed error message-used to be 'BIS_MEASURE_SHORT_NAME_UNIQUE'
2884 l_error_tbl := x_error_tbl;
2885 BIS_UTILITIES_PVT.Add_Error_Message
2886 ( p_error_msg_name => 'BIS_INVALID_MEASURE_VALUE'
2887 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2888 , p_error_proc_name => G_PKG_NAME||'.Update_Measure'
2889 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2890 , p_error_table => l_error_tbl
2891 , x_error_table => x_error_tbl
2892 );
2893
2894 RAISE FND_API.G_EXC_ERROR;
2895 end if;
2896 end if;
2897 --
2898 BIS_TARGET_LEVEL_PVT.Count_Target_Levels( p_api_version =>1.0
2899 , p_Measure_Rec =>p_Measure_Rec
2900 , x_count =>l_count
2901 , x_return_status =>x_return_status
2902 , x_error_Tbl =>x_error_Tbl
2903 );
2904
2905 IF ( (l_count > 0) AND
2906 ( NVL(l_Measure_Rec_Orig.dimension1_id,1) <> NVL(p_Measure_Rec.dimension1_id,1) OR
2907 NVL(l_Measure_Rec_Orig.dimension2_id,2) <> NVL(p_Measure_Rec.dimension2_id,2) OR
2908 NVL(l_Measure_Rec_Orig.dimension3_id,3) <> NVL(p_Measure_Rec.dimension3_id,3) OR
2909 NVL(l_Measure_Rec_Orig.dimension4_id,4) <> NVL(p_Measure_Rec.dimension4_id,4) OR
2910 NVL(l_Measure_Rec_Orig.dimension5_id,5) <> NVL(p_Measure_Rec.dimension5_id,5) OR
2911 NVL(l_Measure_Rec_Orig.dimension6_id,6) <> NVL(p_Measure_Rec.dimension6_id,6) OR
2912 NVL(l_Measure_Rec_Orig.dimension7_id,7) <> NVL(p_Measure_Rec.dimension7_id,7)
2913 )
2914 ) THEN
2915 l_error_tbl := x_error_tbl;
2916 BIS_UTILITIES_PVT.Add_Error_Message
2917 ( p_error_msg_name => 'BIS_NO_DIMENSION_CHANGE'
2918 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2919 , p_error_proc_name => G_PKG_NAME||'.Update_Measure'
2920 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2921 , p_error_table => l_error_tbl
2922 , x_error_table => x_error_tbl
2923 );
2924
2925 RAISE FND_API.G_EXC_ERROR;
2926 END IF;
2927
2928 -- retrieve record from database and apply changes
2929
2930
2931 Update_Measure_Rec_Total -- 2664898
2932 ( p_Measure_Rec_orig => l_Measure_Rec_Orig
2933 , p_Measure_Rec_new => p_Measure_Rec
2934 , x_Measure_Rec => l_Measure_Rec
2935 );
2936
2937
2938 l_user_id := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
2939 l_login_id := fnd_global.LOGIN_ID;
2940
2941 l_Measure_Rec.Last_Update_Date := p_Measure_Rec.Last_Update_Date;
2942 IF (l_Measure_Rec.Last_Update_Date IS NULL) THEN
2943 l_Measure_Rec.Last_Update_Date := SYSDATE;
2944 END IF;
2945
2946 Update bis_indicators
2947 set
2948 SHORT_NAME = l_Measure_Rec.Measure_Short_Name
2949 , UOM_Class = l_Measure_Rec.Unit_Of_Measure_Class -- Fix for 2167619 starts here
2950 , ACTUAL_DATA_SOURCE_TYPE = l_Measure_Rec.Actual_Data_Source_Type
2951 , ACTUAL_DATA_SOURCE = l_Measure_Rec.Actual_Data_Source
2952 , FUNCTION_NAME = l_Measure_Rec.Function_Name
2953 , COMPARISON_SOURCE = l_Measure_Rec.Comparison_Source
2954 , INCREASE_IN_MEASURE = l_Measure_Rec.Increase_In_Measure -- Fix for 2167619 ends here
2955 , ENABLE_LINK = NVL(l_Measure_Rec.Enable_Link, 'N') -- 2440739
2956 , ENABLED = l_Measure_Rec.enabled -- 3031053
2957 , OBSOLETE = l_Measure_Rec.Obsolete --3865711
2958 , MEASURE_TYPE = l_Measure_Rec.Measure_Type
2959 , LAST_UPDATE_DATE = l_Measure_Rec.Last_Update_Date
2960 , LAST_UPDATED_BY = l_user_id
2961 , LAST_UPDATE_LOGIN = l_login_id
2962 where INDICATOR_ID = l_Measure_Rec.Measure_Id;
2963
2964 BIS_MEASURE_PVT.Translate_measure
2965 ( p_api_version => p_api_version
2966 , p_commit => p_commit
2967 , p_validation_level => p_validation_level
2968 , p_Measure_Rec => l_Measure_Rec
2969 , p_owner => p_owner
2970 , x_return_status => x_return_status
2971 , x_error_Tbl => x_error_Tbl
2972 );
2973
2974 -- delete all the dimension links and reinsert the new ones
2975 delete from bis_indicator_dimensions
2976 where indicator_id = l_measure_rec.Measure_id;
2977
2978 --Changed the call to call the overloaded api with Org and Time
2979 Create_Indicator_Dimensions
2980 ( p_Measure_Rec =>l_measure_rec
2981 , p_Org_Dimension_ID => p_Org_Dimension_ID
2982 , p_Time_Dimension_ID => p_Time_Dimension_ID
2983 , p_owner => p_owner
2984 , x_return_status => x_return_status
2985 , x_error_tbl => x_error_tbl
2986 );
2987
2988 --2465354
2989 l_application_rec.Application_id := l_Measure_Rec.Application_Id;
2990 IF (NVL(l_application_rec.Application_id,-1) <> -1 ) THEN
2991 l_own_appl := FND_API.G_TRUE;
2992 END IF;
2993
2994 Update_Application_Measure
2995 ( p_api_version => p_api_version
2996 , p_commit => p_commit
2997 , p_Measure_rec => l_Measure_rec
2998 , p_application_rec => l_application_rec
2999 , p_owning_application => l_own_appl
3000 , p_owner => p_owner
3001 , x_return_status => x_return_status
3002 , x_error_Tbl => x_error_Tbl
3003 );
3004 --2465354
3005
3006 if (p_commit = FND_API.G_TRUE) then
3007 COMMIT;
3008 end if;
3009
3010 --commented RAISE
3011 EXCEPTION
3012 WHEN NO_DATA_FOUND THEN
3013 x_return_status := FND_API.G_RET_STS_ERROR ;
3014 --RAISE FND_API.G_EXC_ERROR;
3015 when FND_API.G_EXC_ERROR then
3016 x_return_status := FND_API.G_RET_STS_ERROR ;
3017 -- RAISE FND_API.G_EXC_ERROR;
3018 when FND_API.G_EXC_UNEXPECTED_ERROR then
3019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3020 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3021 when others then
3022 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3023 --added last two params
3024 l_error_tbl := x_error_tbl;
3025 BIS_UTILITIES_PVT.Add_Error_Message
3026 ( p_error_msg_id => SQLCODE
3027 , p_error_description => SQLERRM
3028 , p_error_proc_name => G_PKG_NAME||'.Update_Measure'
3029 , p_error_table => l_error_tbl
3030 , x_error_table => x_error_tbl
3031 );
3032 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3033
3034 END Update_Measure;
3035
3036 --
3037 -- PLEASE VERIFY COMMENT BELOW
3038 -- deletes one Measure if
3039 -- 1) no Measure levels, targets exist and
3040 -- 2) the Measure access has not been granted to a resonsibility
3041 -- 3) no users have selected to see actuals for the Measure
3042 --
3043 Procedure Delete_Measure
3044 ( p_api_version IN NUMBER
3045 , p_commit IN VARCHAR2 := FND_API.G_FALSE
3046 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
3047 , x_return_status OUT NOCOPY VARCHAR2
3048 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3049 )
3050 IS
3051 l_count number;
3052 l_dcount number;
3053 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3054 BEGIN
3055
3056 x_return_status := FND_API.G_RET_STS_SUCCESS;
3057 BIS_Target_Level_PVT.Count_Target_Levels( p_api_version
3058 , p_Measure_Rec
3059 , l_count
3060 , x_return_status
3061 , x_error_Tbl
3062 );
3063 if (l_count > 0) then
3064 --added last two params
3065 l_error_tbl := x_error_tbl;
3066 BIS_UTILITIES_PVT.Add_Error_Message
3067 ( p_error_msg_name => 'BIS_NO_DELETE_MEASURE'
3068 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3069 , p_error_proc_name => G_PKG_NAME||'.Delete_Measure'
3070 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3071 , p_error_table => l_error_tbl
3072 , x_error_table => x_error_tbl
3073 );
3074
3075 RAISE FND_API.G_EXC_ERROR;
3076 end if;
3077
3078 --added the select and the count check
3079 select count(1) into l_dcount from bis_application_measures
3080 where indicator_id = p_Measure_Rec.Measure_Id;
3081 IF(l_dcount = 0) then
3082 l_error_tbl := x_error_tbl ;
3083 BIS_UTILITIES_PVT.Add_Error_Message
3084 ( p_error_msg_name => 'BIS_INVALID_MEASURE_VALUE'
3085 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3086 , p_error_proc_name => G_PKG_NAME||'.Delete_Measure'
3087 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3088 , p_error_table => l_error_tbl
3089 , x_error_table => x_error_tbl
3090 );
3091 RAISE FND_API.G_EXC_ERROR;
3092 END IF;
3093 ---
3094
3095 delete from bis_application_measures
3096 where indicator_id = p_Measure_Rec.Measure_Id;
3097
3098 -- mdamle 4/23/2003 - PMD - Measure Definer - When a measure is created
3099 -- from PMD, dimensions will not be created within those pages. Hence, add the check.
3100 select count(1) into l_dcount from bis_indicator_dimensions
3101 where indicator_id = p_Measure_Rec.Measure_Id;
3102 if (l_dcount > 0) then
3103 delete from bis_indicator_dimensions
3104 where indicator_id = p_Measure_Rec.Measure_Id;
3105 end if;
3106
3107 delete from bis_indicators_tl
3108 where indicator_id = p_Measure_Rec.Measure_Id;
3109
3110 delete from bis_indicators
3111 where indicator_id = p_Measure_Rec.Measure_Id;
3112
3113 if (p_commit = FND_API.G_TRUE) then
3114 commit;
3115 end if;
3116
3117 --commented RAISE
3118 EXCEPTION
3119 WHEN NO_DATA_FOUND THEN
3120 x_return_status := FND_API.G_RET_STS_ERROR ;
3121 --RAISE FND_API.G_EXC_ERROR;
3122 when FND_API.G_EXC_ERROR then
3123 x_return_status := FND_API.G_RET_STS_ERROR ;
3124 --RAISE FND_API.G_EXC_ERROR;
3125 when FND_API.G_EXC_UNEXPECTED_ERROR then
3126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3127 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3128 when others then
3129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3130 --added last two params
3131 l_error_tbl := x_error_tbl;
3132 BIS_UTILITIES_PVT.Add_Error_Message
3133 ( p_error_msg_id => SQLCODE
3134 , p_error_description => SQLERRM
3135 , p_error_proc_name => G_PKG_NAME||'.Delete_Measure'
3136 , p_error_table => l_error_tbl
3137 , x_error_table => x_error_tbl
3138 );
3139 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3140
3141 END Delete_Measure;
3142 --
3143 --
3144 Procedure Translate_Measure
3145 ( p_api_version IN NUMBER
3146 , p_commit IN VARCHAR2 := FND_API.G_FALSE
3147 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3148 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
3149 , x_return_status OUT NOCOPY VARCHAR2
3150 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3151 )
3152 IS
3153 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3154 BEGIN
3155
3156 Translate_Measure
3157 ( p_api_version => p_api_version
3158 , p_commit => p_commit
3159 , p_validation_level => p_validation_level
3160 , p_Measure_Rec => p_Measure_Rec
3161 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
3162 , x_return_status => x_return_status
3163 , x_error_Tbl => x_error_Tbl
3164 );
3165 --commented RAISE
3166 EXCEPTION
3167 when others then
3168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3169 --added last two params
3170 l_error_tbl := x_error_tbl;
3171 BIS_UTILITIES_PVT.Add_Error_Message
3172 ( p_error_msg_id => SQLCODE
3173 , p_error_description => SQLERRM
3174 , p_error_proc_name => G_PKG_NAME||'.Translate_Measure'
3175 , p_error_table => l_error_tbl
3176 , x_error_table => x_error_tbl
3177 );
3178 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3179
3180 END Translate_Measure;
3181 --
3182 --
3183 Procedure Translate_Measure
3184 ( p_api_version IN NUMBER
3185 , p_commit IN VARCHAR2 := FND_API.G_FALSE
3186 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3187 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
3188 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
3189 , x_return_status OUT NOCOPY VARCHAR2
3190 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3191 )
3192 IS
3193 l_user_id NUMBER;
3194 l_login_id NUMBER;
3195 l_count NUMBER := 0;
3196 --l_Measure_Id NUMBER;
3197 l_measure_rec BIS_MEASURE_PUB.Measure_Rec_Type;
3198 l_Measure_Rec_Orig BIS_MEASURE_PUB.Measure_Rec_Type;
3199 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3200
3201 BEGIN
3202
3203 l_measure_rec := p_measure_rec;
3204 --
3205 BIS_MEASURE_PVT.Retrieve_Measure
3206 ( p_api_version => 1.0
3207 , p_Measure_Rec => l_Measure_Rec
3208 , p_all_info => FND_API.G_FALSE
3209 , x_Measure_Rec => l_measure_rec_Orig
3210 , x_return_status => x_return_status
3211 , x_error_Tbl => x_error_Tbl
3212 );
3213
3214 Validate_Measure( p_api_version
3215 , p_validation_level
3216 , l_measure_rec_orig
3217 , p_owner
3218 , x_return_status
3219 , x_error_Tbl
3220 );
3221
3222 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3223 RAISE FND_API.G_EXC_ERROR;
3224 END IF;
3225
3226 /*
3227 if (l_measure_rec_Orig.measure_id is NOT NULL) then
3228 if (l_measure_rec_Orig.measure_id <> p_Measure_Rec.Measure_Id) then
3229 --added last two params
3230 l_error_tbl := x_error_tbl;
3231 BIS_UTILITIES_PVT.Add_Error_Message
3232 ( p_error_msg_name => 'BIS_MEASURE_SHORT_NAME_UNIQUE'
3233 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3234 , p_error_proc_name => G_PKG_NAME||'.Translate_Measure'
3235 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3236 , p_error_table => l_error_tbl
3237 , x_error_table => x_error_tbl
3238 );
3239 RAISE FND_API.G_EXC_ERROR;
3240 end if;
3241 end if;
3242
3243 -- retrieve record from database and apply changes
3244 UpdateMeasureRec
3245 ( p_Measure_Rec => p_Measure_Rec
3246 , p_Measure_Rec1 => l_Measure_Rec_Orig
3247 , x_Measure_Rec => l_Measure_Rec
3248 );
3249 */
3250 --
3251
3252 l_user_id := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
3253
3254 l_login_id := fnd_global.LOGIN_ID;
3255 --
3256
3257 Update bis_INDICATORS_TL
3258 set
3259 NAME = l_Measure_Rec.Measure_Name
3260 , DESCRIPTION = l_Measure_Rec.description
3261 , LAST_UPDATE_DATE = l_Measure_Rec.Last_Update_Date
3262 , LAST_UPDATED_BY = l_user_id
3263 , LAST_UPDATE_LOGIN = l_login_id
3264 , SOURCE_LANG = userenv('LANG')
3265 where INDICATOR_ID = l_Measure_Rec_orig.Measure_Id
3266 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
3267
3268 if (p_commit = FND_API.G_TRUE) then
3269 COMMIT;
3270 end if;
3271
3272 --commented RAISE
3273 EXCEPTION
3274 WHEN NO_DATA_FOUND THEN
3275 x_return_status := FND_API.G_RET_STS_ERROR ;
3276 -- RAISE FND_API.G_EXC_ERROR;
3277 when FND_API.G_EXC_ERROR then
3278 x_return_status := FND_API.G_RET_STS_ERROR ;
3279 -- RAISE FND_API.G_EXC_ERROR;
3280 when FND_API.G_EXC_UNEXPECTED_ERROR then
3281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3282 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3283 when others then
3284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3285 --added last two params
3286 l_error_tbl := x_error_tbl;
3287 BIS_UTILITIES_PVT.Add_Error_Message
3288 ( p_error_msg_id => SQLCODE
3289 , p_error_description => SQLERRM
3290 , p_error_proc_name => G_PKG_NAME||'.Translate_Measure'
3291 , p_error_table => l_error_tbl
3292 , x_error_table => x_error_tbl
3293 );
3294 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3295
3296 END Translate_Measure;
3297 --
3298 -- Validates measure
3299 PROCEDURE Validate_Measure
3300 ( p_api_version IN NUMBER
3301 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3302 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
3303 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
3304 , x_return_status OUT NOCOPY VARCHAR2
3305 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3306 )
3307 IS
3308 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3309 l_error VARCHAR2(10) := FND_API.G_FALSE;
3310 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
3311 l_region_code varchar2(30);
3312 l_pos number;
3313 l_source_column varchar2(30);
3314 BEGIN
3315
3316 BEGIN
3317 BIS_MEASURE_VALIDATE_PVT.Validate_Dimension1_Id
3318 ( p_api_version => p_api_version
3319 , p_validation_level => p_validation_level
3320 , p_MEASURE_Rec => p_MEASURE_Rec
3321 , x_return_status => x_return_status
3322 , x_error_Tbl => l_error_Tbl
3323 );
3324 --EXCEPTION
3325 --when FND_API.G_EXC_ERROR then
3326 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3327 l_error := FND_API.G_TRUE;
3328 l_error_Tbl_p := x_error_Tbl;
3329 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3330 , l_error_Tbl
3331 , x_error_tbl
3332 );
3333 x_return_status := FND_API.G_RET_STS_ERROR;
3334 END IF;
3335 END;
3336
3337 BEGIN
3338 BIS_MEASURE_VALIDATE_PVT.Validate_Dimension2_Id
3339 ( p_api_version => p_api_version
3340 , p_validation_level => p_validation_level
3341 , p_MEASURE_Rec => p_MEASURE_Rec
3342 , x_return_status => x_return_status
3343 , x_error_Tbl => l_error_Tbl
3344 );
3345 --EXCEPTION
3346 --when FND_API.G_EXC_ERROR then
3347 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3348 l_error := FND_API.G_TRUE;
3349 l_error_Tbl_p := x_error_Tbl;
3350 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3351 , l_error_Tbl
3352 , x_error_tbl
3353 );
3354 x_return_status := FND_API.G_RET_STS_ERROR;
3355 END IF;
3356 END;
3357
3358 BEGIN
3359 BIS_MEASURE_VALIDATE_PVT.Validate_Dimension3_Id
3360 ( p_api_version => p_api_version
3361 , p_validation_level => p_validation_level
3362 , p_MEASURE_Rec => p_MEASURE_Rec
3363 , x_return_status => x_return_status
3364 , x_error_Tbl => l_error_Tbl
3365 );
3366 -- EXCEPTION
3367 -- when FND_API.G_EXC_ERROR then
3368 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3369 l_error := FND_API.G_TRUE;
3370 l_error_Tbl_p := x_error_Tbl;
3371 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3372 , l_error_Tbl
3373 , x_error_tbl
3374 );
3375 x_return_status := FND_API.G_RET_STS_ERROR;
3376 END IF;
3377 END;
3378
3379 BEGIN
3380 BIS_MEASURE_VALIDATE_PVT.Validate_Dimension4_Id
3381 ( p_api_version => p_api_version
3382 , p_validation_level => p_validation_level
3383 , p_MEASURE_Rec => p_MEASURE_Rec
3384 , x_return_status => x_return_status
3385 , x_error_Tbl => l_error_Tbl
3386 );
3387 --EXCEPTION
3388 --when FND_API.G_EXC_ERROR then
3389 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3390 l_error := FND_API.G_TRUE;
3391 l_error_Tbl_p := x_error_Tbl;
3392 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3393 , l_error_Tbl
3394 , x_error_tbl
3395 );
3396 x_return_status := FND_API.G_RET_STS_ERROR;
3397 END IF;
3398 END;
3399
3400
3401 BEGIN
3402 BIS_MEASURE_VALIDATE_PVT.Validate_Dimension5_Id
3403 ( p_api_version => p_api_version
3404 , p_validation_level => p_validation_level
3405 , p_MEASURE_Rec => p_MEASURE_Rec
3406 , x_return_status => x_return_status
3407 , x_error_Tbl => l_error_Tbl
3408 );
3409 -- EXCEPTION
3410 -- when FND_API.G_EXC_ERROR then
3411 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3412 l_error := FND_API.G_TRUE;
3413 l_error_Tbl_p := x_error_Tbl;
3414 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3415 , l_error_Tbl
3416 , x_error_tbl
3417 );
3418 x_return_status := FND_API.G_RET_STS_ERROR;
3419 END IF;
3420 END;
3421
3422 BEGIN
3423 BIS_MEASURE_VALIDATE_PVT.Validate_Dimension6_Id
3424 ( p_api_version => p_api_version
3425 , p_validation_level => p_validation_level
3426 , p_MEASURE_Rec => p_MEASURE_Rec
3427 , x_return_status => x_return_status
3428 , x_error_Tbl => l_error_Tbl
3429 );
3430 --EXCEPTION
3431 --when FND_API.G_EXC_ERROR then
3432 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3433 l_error := FND_API.G_TRUE;
3434 l_error_Tbl_p := x_error_Tbl;
3435 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3436 , l_error_Tbl
3437 , x_error_tbl
3438 );
3439 x_return_status := FND_API.G_RET_STS_ERROR;
3440 END IF;
3441 END;
3442
3443 BEGIN
3444 BIS_MEASURE_VALIDATE_PVT.Validate_Dimension7_Id
3445 ( p_api_version => p_api_version
3446 , p_validation_level => p_validation_level
3447 , p_MEASURE_Rec => p_MEASURE_Rec
3448 , x_return_status => x_return_status
3449 , x_error_Tbl => l_error_Tbl
3450 );
3451 --EXCEPTION
3452 --when FND_API.G_EXC_ERROR then
3453 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3454 l_error := FND_API.G_TRUE;
3455 l_error_Tbl_p := x_error_Tbl;
3456 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3457 , l_error_Tbl
3458 , x_error_tbl
3459 );
3460 x_return_status := FND_API.G_RET_STS_ERROR;
3461 END IF;
3462 END;
3463
3464 -- Fix for 1850860 starts here
3465 BEGIN
3466 BIS_MEASURE_VALIDATE_PVT.Val_Actual_Data_Sour_Type_Wrap
3467 ( p_api_version => p_api_version
3468 , p_validation_level => p_validation_level
3469 , p_MEASURE_Rec => p_MEASURE_Rec
3470 , x_return_status => x_return_status
3471 , x_error_Tbl => l_error_Tbl
3472 );
3473 --EXCEPTION
3474 --when FND_API.G_EXC_ERROR then
3475 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3476 l_error := FND_API.G_TRUE;
3477 l_error_Tbl_p := x_error_Tbl;
3478 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3479 , l_error_Tbl
3480 , x_error_tbl
3481 );
3482 x_return_status := FND_API.G_RET_STS_ERROR;
3483 END IF;
3484 END;
3485 --
3486
3487 -- mdamle 08/20/2003 - Source column must be selected if a data source is selected.
3488 l_pos := instr(p_measure_rec.actual_data_source, '.');
3489 if (l_pos > 0) then
3490 l_region_code := substr(p_measure_rec.actual_data_source, 1, instr(p_measure_rec.actual_data_source, '.') -1);
3491 l_source_column := substr(p_measure_rec.actual_data_source, instr(p_measure_rec.actual_data_source, '.') +1);
3492 else
3493 l_region_code := p_measure_rec.actual_data_source;
3494 l_source_column := null;
3495 end if;
3496
3497 if (l_region_code is not null and l_source_column is null) then
3498 l_error_tbl := x_error_tbl;
3499 BIS_UTILITIES_PVT.Add_Error_Message
3500 ( p_error_msg_name => 'BIS_SOURCE_COLUMN_ERR_TXT'
3501 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3502 , p_error_proc_name => G_PKG_NAME||'.Create_Measure'
3503 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3504 , p_error_table => l_error_tbl
3505 , x_error_table => x_error_tbl
3506 );
3507 RAISE FND_API.G_EXC_ERROR;
3508 end if;
3509
3510
3511 IF p_owner = BIS_UTILITIES_PUB.G_CUSTOM_OWNER THEN --2240105
3512 BEGIN
3513 BIS_MEASURE_VALIDATE_PVT.Val_Actual_Data_Sour_Wrap
3514 ( p_api_version => p_api_version
3515 , p_validation_level => p_validation_level
3516 , p_MEASURE_Rec => p_MEASURE_Rec
3517 , x_return_status => x_return_status
3518 , x_error_Tbl => l_error_Tbl
3519 );
3520 --EXCEPTION
3521 --when FND_API.G_EXC_ERROR then
3522 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3523 l_error := FND_API.G_TRUE;
3524 l_error_Tbl_p := x_error_Tbl;
3525 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3526 , l_error_Tbl
3527 , x_error_tbl
3528 );
3529 x_return_status := FND_API.G_RET_STS_ERROR;
3530 END IF;
3531 END;
3532 --
3533 BEGIN
3534 BIS_MEASURE_VALIDATE_PVT.Val_Func_Name_Wrap
3535 ( p_api_version => p_api_version
3536 , p_validation_level => p_validation_level
3537 , p_MEASURE_Rec => p_MEASURE_Rec
3538 , x_return_status => x_return_status
3539 , x_error_Tbl => l_error_Tbl
3540 );
3541 --EXCEPTION
3542 --when FND_API.G_EXC_ERROR then
3543 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3544 l_error := FND_API.G_TRUE;
3545 l_error_Tbl_p := x_error_Tbl;
3546 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3547 , l_error_Tbl
3548 , x_error_tbl
3549 );
3550 x_return_status := FND_API.G_RET_STS_ERROR;
3551 END IF;
3552 END;
3553 --
3554 BEGIN
3555 BIS_MEASURE_VALIDATE_PVT.Val_Comparison_Source_Wrap
3556 ( p_api_version => p_api_version
3557 , p_validation_level => p_validation_level
3558 , p_MEASURE_Rec => p_MEASURE_Rec
3559 , x_return_status => x_return_status
3560 , x_error_Tbl => l_error_Tbl
3561 );
3562 --EXCEPTION
3563 --when FND_API.G_EXC_ERROR then
3564 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3565 l_error := FND_API.G_TRUE;
3566 l_error_Tbl_p := x_error_Tbl;
3567 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3568 , l_error_Tbl
3569 , x_error_tbl
3570 );
3571 x_return_status := FND_API.G_RET_STS_ERROR;
3572 END IF;
3573 END;
3574 --
3575 END IF; -- 2240105
3576 BEGIN
3577 BIS_MEASURE_VALIDATE_PVT.Val_Incr_In_Measure_Wrap
3578 ( p_api_version => p_api_version
3579 , p_validation_level => p_validation_level
3580 , p_MEASURE_Rec => p_MEASURE_Rec
3581 , x_return_status => x_return_status
3582 , x_error_Tbl => l_error_Tbl
3583 );
3584 --EXCEPTION
3585 --when FND_API.G_EXC_ERROR then
3586 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3587 l_error := FND_API.G_TRUE;
3588 l_error_Tbl_p := x_error_Tbl;
3589 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3590 , l_error_Tbl
3591 , x_error_tbl
3592 );
3593 x_return_status := FND_API.G_RET_STS_ERROR;
3594 END IF;
3595 END;
3596 -- Fix for 1850860 ends here
3597 -- 2440739
3598 BEGIN
3599 BIS_MEASURE_VALIDATE_PVT.Val_Enable_Link_Wrap
3600 ( p_api_version => p_api_version
3601 , p_validation_level => p_validation_level
3602 , p_MEASURE_Rec => p_MEASURE_Rec
3603 , x_return_status => x_return_status
3604 , x_error_Tbl => l_error_Tbl
3605 );
3606 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3607 l_error := FND_API.G_TRUE;
3608 l_error_Tbl_p := x_error_Tbl;
3609 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3610 , l_error_Tbl
3611 , x_error_tbl
3612 );
3613 x_return_status := FND_API.G_RET_STS_ERROR;
3614 END IF;
3615 END;
3616 -- 2440739
3617
3618 -- Need to add for ENABLED Column ?
3619 -- 3031053
3620 BEGIN
3621 BIS_MEASURE_VALIDATE_PVT.Val_Enabled_Wrap
3622 ( p_api_version => p_api_version
3623 , p_validation_level => p_validation_level
3624 , p_MEASURE_Rec => p_MEASURE_Rec
3625 , x_return_status => x_return_status
3626 , x_error_Tbl => l_error_Tbl
3627 );
3628 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3629 l_error := FND_API.G_TRUE;
3630 l_error_Tbl_p := x_error_Tbl;
3631 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3632 , l_error_Tbl
3633 , x_error_tbl
3634 );
3635 x_return_status := FND_API.G_RET_STS_ERROR;
3636 END IF;
3637 END;
3638 -- 3031053
3639 BEGIN --3865711
3640 BIS_MEASURE_VALIDATE_PVT.Val_Obsolete_Wrap
3641 ( p_api_version => p_api_version
3642 , p_validation_level => p_validation_level
3643 , p_MEASURE_Rec => p_MEASURE_Rec
3644 , x_return_status => x_return_status
3645 , x_error_Tbl => l_error_Tbl
3646 );
3647 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3648 l_error := FND_API.G_TRUE;
3649 l_error_Tbl_p := x_error_Tbl;
3650 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3651 , l_error_Tbl
3652 , x_error_tbl
3653 );
3654 x_return_status := FND_API.G_RET_STS_ERROR;
3655 END IF;
3656 END;
3657
3658 BEGIN
3659 BIS_MEASURE_VALIDATE_PVT.Val_Measure_Type_Wrap
3660 ( p_api_version => p_api_version
3661 , p_validation_level => p_validation_level
3662 , p_MEASURE_Rec => p_MEASURE_Rec
3663 , x_return_status => x_return_status
3664 , x_error_Tbl => l_error_Tbl
3665 );
3666 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3667 l_error := FND_API.G_TRUE;
3668 l_error_Tbl_p := x_error_Tbl;
3669 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
3670 , l_error_Tbl
3671 , x_error_tbl
3672 );
3673 x_return_status := FND_API.G_RET_STS_ERROR;
3674 END IF;
3675 END;
3676
3677 if (l_error = FND_API.G_TRUE) then
3678 RAISE FND_API.G_EXC_ERROR;
3679 end if;
3680
3681 --commented RAISE
3682 EXCEPTION
3683 WHEN NO_DATA_FOUND THEN
3684 x_return_status := FND_API.G_RET_STS_ERROR ;
3685 -- RAISE FND_API.G_EXC_ERROR;
3686 when FND_API.G_EXC_ERROR then
3687 x_return_status := FND_API.G_RET_STS_ERROR ;
3688 -- RAISE FND_API.G_EXC_ERROR;
3689 when FND_API.G_EXC_UNEXPECTED_ERROR then
3690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3691 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3692 when others then
3693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3694 --added last two params
3695 l_error_Tbl := x_error_tbl;
3696 BIS_UTILITIES_PVT.Add_Error_Message
3697 ( p_error_msg_id => SQLCODE
3698 , p_error_description => SQLERRM
3699 , p_error_proc_name => G_PKG_NAME||'.Validate_Measure'
3700 , p_error_table => l_error_Tbl
3701 , x_error_table => x_error_tbl
3702 );
3703 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3704
3705 END Validate_Measure;
3706 --
3707 -- Value - ID conversion
3708 PROCEDURE Value_ID_Conversion
3709 ( p_api_version IN NUMBER
3710 , p_Measure_Rec IN BIS_Measure_PUB.Measure_Rec_Type
3711 , x_Measure_Rec IN OUT NOCOPY BIS_Measure_PUB.Measure_Rec_Type
3712 , x_return_status OUT NOCOPY VARCHAR2
3713 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3714 )
3715 IS
3716 l_convert VARCHAR2(32000);
3717 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3718 BEGIN
3719 x_return_status := FND_API.G_RET_STS_SUCCESS;
3720 x_Measure_Rec := p_Measure_Rec;
3721
3722 if (BIS_UTILITIES_PUB.Value_Missing
3723 (x_Measure_Rec.Measure_id) = FND_API.G_TRUE
3724 OR BIS_UTILITIES_PUB.Value_NULL(x_Measure_Rec.Measure_id)
3725 = FND_API.G_TRUE) then
3726 BEGIN
3727 BIS_Measure_PVT.Value_ID_Conversion
3728 ( p_api_version
3729 , x_Measure_Rec.Measure_Short_Name
3730 , x_Measure_Rec.Measure_Name
3731 , x_Measure_Rec.Measure_ID
3732 , x_return_status
3733 , x_error_Tbl
3734 );
3735 --changed this to if
3736 -- EXCEPTION
3737 -- WHEN FND_API.G_EXC_ERROR then
3738 -- NULL;
3739 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3740 RAISE FND_API.G_EXC_ERROR;
3741 end if;
3742 END;
3743 end if;
3744
3745 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
3746 ( x_Measure_Rec.Dimension1_ID
3747 , x_Measure_Rec.Dimension1_Short_Name
3748 , x_Measure_Rec.Dimension1_Name
3749 );
3750
3751 if (l_convert = FND_API.G_TRUE) then
3752 BEGIN
3753 BIS_DIMENSION_PVT.Value_ID_Conversion
3754 ( p_api_version
3755 , x_Measure_Rec.Dimension1_Short_Name
3756 , x_Measure_Rec.Dimension1_Name
3757 , x_Measure_Rec.Dimension1_ID
3758 , x_return_status
3759 , x_error_Tbl
3760 );
3761
3762 -- EXCEPTION
3763 -- WHEN FND_API.G_EXC_ERROR then
3764 -- NULL;
3765 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3766 RAISE FND_API.G_EXC_ERROR;
3767 end if;
3768 END;
3769 end if;
3770
3771 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
3772 ( x_Measure_Rec.Dimension2_ID
3773 , x_Measure_Rec.Dimension2_Short_Name
3774 , x_Measure_Rec.Dimension2_Name
3775 );
3776
3777 if (l_convert = FND_API.G_TRUE) then
3778 BEGIN
3779 BIS_DIMENSION_PVT.Value_ID_Conversion
3780 ( p_api_version
3781 , x_Measure_Rec.Dimension2_Short_Name
3782 , x_Measure_Rec.Dimension2_Name
3783 , x_Measure_Rec.Dimension2_ID
3784 , x_return_status
3785 , x_error_Tbl
3786 );
3787 -- EXCEPTION
3788 -- WHEN FND_API.G_EXC_ERROR then
3789 -- NULL;
3790 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3791 RAISE FND_API.G_EXC_ERROR;
3792 end if;
3793 END;
3794 end if;
3795
3796 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
3797 ( x_Measure_Rec.Dimension3_ID
3798 , x_Measure_Rec.Dimension3_Short_Name
3799 , x_Measure_Rec.Dimension3_Name
3800 );
3801
3802 if (l_convert = FND_API.G_TRUE) then
3803 BEGIN
3804 BIS_DIMENSION_PVT.Value_ID_Conversion
3805 ( p_api_version
3806 , x_Measure_Rec.Dimension3_Short_Name
3807 , x_Measure_Rec.Dimension3_Name
3808 , x_Measure_Rec.Dimension3_ID
3809 , x_return_status
3810 , x_error_Tbl
3811 );
3812 -- EXCEPTION
3813 -- WHEN FND_API.G_EXC_ERROR then
3814 -- NULL;
3815 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3816 RAISE FND_API.G_EXC_ERROR;
3817 end if;
3818 END;
3819 end if;
3820
3821 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
3822 ( x_Measure_Rec.Dimension4_ID
3823 , x_Measure_Rec.Dimension4_Short_Name
3824 , x_Measure_Rec.Dimension4_Name
3825 );
3826
3827 if (l_convert = FND_API.G_TRUE) then
3828 BEGIN
3829 BIS_DIMENSION_PVT.Value_ID_Conversion
3830 ( p_api_version
3831 , x_Measure_Rec.Dimension4_Short_Name
3832 , x_Measure_Rec.Dimension4_Name
3833 , x_Measure_Rec.Dimension4_ID
3834 , x_return_status
3835 , x_error_Tbl
3836 );
3837 -- EXCEPTION
3838 -- WHEN FND_API.G_EXC_ERROR then
3839 -- NULL;
3840 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3841 RAISE FND_API.G_EXC_ERROR;
3842 end if;
3843 END;
3844 end if;
3845
3846 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
3847 ( x_Measure_Rec.Dimension5_ID
3848 , x_Measure_Rec.Dimension5_Short_Name
3849 , x_Measure_Rec.Dimension5_Name
3850 );
3851
3852 if (l_convert = FND_API.G_TRUE) then
3853 BEGIN
3854 BIS_DIMENSION_PVT.Value_ID_Conversion
3855 ( p_api_version
3856 , x_Measure_Rec.Dimension5_Short_Name
3857 , x_Measure_Rec.Dimension5_Name
3858 , x_Measure_Rec.Dimension5_ID
3859 , x_return_status
3860 , x_error_Tbl
3861 );
3862 -- EXCEPTION
3863 -- WHEN FND_API.G_EXC_ERROR then
3864 -- NULL;
3865 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3866 RAISE FND_API.G_EXC_ERROR;
3867 end if;
3868 END;
3869 end if;
3870
3871 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
3872 ( x_Measure_Rec.Dimension6_ID
3873 , x_Measure_Rec.Dimension6_Short_Name
3874 , x_Measure_Rec.Dimension6_Name
3875 );
3876
3877 if (l_convert = FND_API.G_TRUE) then
3878 BEGIN
3879 BIS_DIMENSION_PVT.Value_ID_Conversion
3880 ( p_api_version
3881 , x_Measure_Rec.Dimension6_Short_Name
3882 , x_Measure_Rec.Dimension6_Name
3883 , x_Measure_Rec.Dimension6_ID
3884 , x_return_status
3885 , x_error_Tbl
3886 );
3887 -- EXCEPTION
3888 -- WHEN FND_API.G_EXC_ERROR then
3889 -- NULL;
3890 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3891 RAISE FND_API.G_EXC_ERROR;
3892 end if;
3893 END;
3894 end if;
3895
3896 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
3897 ( x_Measure_Rec.Dimension7_ID
3898 , x_Measure_Rec.Dimension7_Short_Name
3899 , x_Measure_Rec.Dimension7_Name
3900 );
3901
3902 if (l_convert = FND_API.G_TRUE) then
3903 BEGIN
3904 BIS_DIMENSION_PVT.Value_ID_Conversion
3905 ( p_api_version
3906 , x_Measure_Rec.Dimension7_Short_Name
3907 , x_Measure_Rec.Dimension7_Name
3908 , x_Measure_Rec.Dimension7_ID
3909 , x_return_status
3910 , x_error_Tbl
3911 );
3912 -- EXCEPTION
3913 -- WHEN FND_API.G_EXC_ERROR then
3914 -- NULL;
3915 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3916 RAISE FND_API.G_EXC_ERROR;
3917 end if;
3918 END;
3919 end if;
3920
3921 --commented RAISE
3922 EXCEPTION
3923 WHEN NO_DATA_FOUND THEN
3924 x_return_status := FND_API.G_RET_STS_ERROR ;
3925 --RAISE FND_API.G_EXC_ERROR;
3926 when FND_API.G_EXC_ERROR then
3927 x_return_status := FND_API.G_RET_STS_ERROR ;
3928 -- RAISE FND_API.G_EXC_ERROR;
3929 when FND_API.G_EXC_UNEXPECTED_ERROR then
3930 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3931 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3932 when others then
3933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3934 --added last two params
3935 l_error_tbl := x_error_tbl;
3936 BIS_UTILITIES_PVT.Add_Error_Message
3937 ( p_error_msg_id => SQLCODE
3938 , p_error_description => SQLERRM
3939 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
3940 , p_error_table => l_error_tbl
3941 , x_error_table => x_error_tbl
3942 );
3943 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3944
3945 END Value_ID_Conversion;
3946
3947 PROCEDURE Value_ID_Conversion
3948 ( p_api_version IN NUMBER
3949 , p_Measure_Short_Name IN VARCHAR2
3950 , p_Measure_Name IN VARCHAR2
3951 , x_Measure_ID OUT NOCOPY NUMBER
3952 , x_return_status OUT NOCOPY VARCHAR2
3953 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3954 )
3955 IS
3956 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3957 BEGIN
3958 x_return_status := FND_API.G_RET_STS_SUCCESS;
3959
3960 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_Measure_Short_Name)
3961 = FND_API.G_TRUE) then
3962 SELECT Measure_id into x_Measure_ID
3963 FROM bisbv_Performance_Measures
3964 WHERE Measure_short_name = p_Measure_Short_Name;
3965 elsif (BIS_UTILITIES_PUB.Value_Not_Missing(p_Measure_Name)
3966 = FND_API.G_TRUE) then
3967 SELECT Measure_id into x_Measure_ID
3968 FROM bisbv_Performance_Measures
3969 WHERE Measure_name = p_Measure_Name;
3970 else
3971 -- POLPULATE ERROR TABLE
3972 --added last two params
3973 l_error_tbl := x_error_tbl;
3974 BIS_UTILITIES_PVT.Add_Error_Message
3975 ( p_error_msg_name => 'BIS_NAME_SHORT_NAME_MISSING'
3976 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3977 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
3978 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3979 , p_error_table => l_error_tbl
3980 , x_error_table => x_error_tbl
3981 );
3982
3983 RAISE FND_API.G_EXC_ERROR;
3984 end if;
3985
3986 --commented RAISE
3987 EXCEPTION
3988 WHEN NO_DATA_FOUND THEN
3989 x_return_status := FND_API.G_RET_STS_ERROR ;
3990 -- RAISE FND_API.G_EXC_ERROR;
3991 when FND_API.G_EXC_ERROR then
3992 x_return_status := FND_API.G_RET_STS_ERROR ;
3993 -- RAISE FND_API.G_EXC_ERROR;
3994 when FND_API.G_EXC_UNEXPECTED_ERROR then
3995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3996 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3997 when others then
3998 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3999 --added last two params
4000 l_error_tbl := x_error_tbl;
4001 BIS_UTILITIES_PVT.Add_Error_Message
4002 ( p_error_msg_id => SQLCODE
4003 , p_error_description => SQLERRM
4004 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
4005 , p_error_table => l_error_tbl
4006 , x_error_table => x_error_tbl
4007 );
4008 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4009
4010 END Value_ID_Conversion;
4011 --
4012
4013 --new procedure to to value id conversion only for measure
4014 PROCEDURE Measure_Value_ID_Conversion
4015 ( p_api_version IN NUMBER
4016 , p_Measure_Rec IN BIS_Measure_PUB.Measure_Rec_Type
4017 , x_Measure_Rec OUT NOCOPY BIS_Measure_PUB.Measure_Rec_Type
4018 , x_return_status OUT NOCOPY VARCHAR2
4019 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
4020 )
4021 IS
4022 l_convert VARCHAR2(32000);
4023 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4024 BEGIN
4025 x_return_status := FND_API.G_RET_STS_SUCCESS;
4026 x_Measure_Rec := p_Measure_Rec;
4027
4028 if (BIS_UTILITIES_PUB.Value_Missing
4029 (x_Measure_Rec.Measure_id) = FND_API.G_TRUE
4030 OR BIS_UTILITIES_PUB.Value_NULL(x_Measure_Rec.Measure_id)
4031 = FND_API.G_TRUE) then
4032 BEGIN
4033 BIS_Measure_PVT.Value_ID_Conversion
4034 ( p_api_version
4035 , x_Measure_Rec.Measure_Short_Name
4036 , x_Measure_Rec.Measure_Name
4037 , x_Measure_Rec.Measure_ID
4038 , x_return_status
4039 , x_error_Tbl
4040 );
4041 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4042 l_error_tbl := x_error_tbl;
4043 BIS_UTILITIES_PVT.Add_Error_Message
4044 ( p_error_msg_name => 'BIS_INVALID_MEASURE_VALUE'
4045 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
4046 , p_error_proc_name => G_PKG_NAME||'.Measure_Value_ID_Conversion'
4047 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
4048 , p_error_table => l_error_tbl
4049 , x_error_table => x_error_tbl
4050 );
4051 RAISE FND_API.G_EXC_ERROR;
4052 end if;
4053 END;
4054 end if;
4055
4056 EXCEPTION
4057 WHEN NO_DATA_FOUND THEN
4058 x_return_status := FND_API.G_RET_STS_ERROR ;
4059 -- RAISE FND_API.G_EXC_ERROR;
4060 when FND_API.G_EXC_ERROR then
4061 x_return_status := FND_API.G_RET_STS_ERROR ;
4062 -- RAISE FND_API.G_EXC_ERROR;
4063 when FND_API.G_EXC_UNEXPECTED_ERROR then
4064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4065 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4066 when others then
4067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4068 --added last two params
4069 l_error_tbl := x_error_tbl;
4070 BIS_UTILITIES_PVT.Add_Error_Message
4071 ( p_error_msg_id => SQLCODE
4072 , p_error_description => SQLERRM
4073 , p_error_proc_name => G_PKG_NAME||'.Measure_Value_ID_Conversion'
4074 , p_error_table => l_error_tbl
4075 , x_error_table => x_error_tbl
4076 );
4077 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4078
4079 END Measure_Value_ID_Conversion;
4080 --
4081
4082 --new procedure to to value id conversion only for dimension
4083 PROCEDURE Dimension_Value_ID_Conversion
4084 ( p_api_version IN NUMBER
4085 , p_Measure_Rec IN BIS_Measure_PUB.Measure_Rec_Type
4086 , x_Measure_Rec IN OUT NOCOPY BIS_Measure_PUB.Measure_Rec_Type
4087 , x_return_status OUT NOCOPY VARCHAR2
4088 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
4089 )
4090 IS
4091 l_convert VARCHAR2(32000);
4092 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4093 BEGIN
4094 x_return_status := FND_API.G_RET_STS_SUCCESS;
4095 x_Measure_Rec := p_Measure_Rec;
4096
4097 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
4098 ( x_Measure_Rec.Dimension1_ID
4099 , x_Measure_Rec.Dimension1_Short_Name
4100 , x_Measure_Rec.Dimension1_Name
4101 );
4102
4103 if (l_convert = FND_API.G_TRUE) then
4104 BEGIN
4105 BIS_DIMENSION_PVT.Value_ID_Conversion
4106 ( p_api_version
4107 , x_Measure_Rec.Dimension1_Short_Name
4108 , x_Measure_Rec.Dimension1_Name
4109 , x_Measure_Rec.Dimension1_ID
4110 , x_return_status
4111 , x_error_Tbl
4112 );
4113
4114 -- EXCEPTION
4115 -- WHEN FND_API.G_EXC_ERROR then
4116 -- NULL;
4117 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4118 l_error_tbl := x_error_tbl;
4119 BIS_UTILITIES_PVT.Add_Error_Message
4120 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
4121 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
4122 , p_error_proc_name => G_PKG_NAME||'.Dimension_Value_ID_Conversion'
4123 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
4124 , p_error_table => l_error_tbl
4125 , x_error_table => x_error_tbl
4126 );
4127 RAISE FND_API.G_EXC_ERROR;
4128 end if;
4129 END;
4130 end if;
4131
4132 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
4133 ( x_Measure_Rec.Dimension2_ID
4134 , x_Measure_Rec.Dimension2_Short_Name
4135 , x_Measure_Rec.Dimension2_Name
4136 );
4137
4138 if (l_convert = FND_API.G_TRUE) then
4139 BEGIN
4140 BIS_DIMENSION_PVT.Value_ID_Conversion
4141 ( p_api_version
4142 , x_Measure_Rec.Dimension2_Short_Name
4143 , x_Measure_Rec.Dimension2_Name
4144 , x_Measure_Rec.Dimension2_ID
4145 , x_return_status
4146 , x_error_Tbl
4147 );
4148 -- EXCEPTION
4149 -- WHEN FND_API.G_EXC_ERROR then
4150 -- NULL;
4151 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4152 l_error_tbl := x_error_tbl;
4153 BIS_UTILITIES_PVT.Add_Error_Message
4154 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
4155 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
4156 , p_error_proc_name => G_PKG_NAME||'.Dimension_Value_ID_Conversion'
4157 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
4158 , p_error_table => l_error_tbl
4159 , x_error_table => x_error_tbl
4160 );
4161 RAISE FND_API.G_EXC_ERROR;
4162 end if;
4163 END;
4164 end if;
4165
4166 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
4167 ( x_Measure_Rec.Dimension3_ID
4168 , x_Measure_Rec.Dimension3_Short_Name
4169 , x_Measure_Rec.Dimension3_Name
4170 );
4171
4172 if (l_convert = FND_API.G_TRUE) then
4173 BEGIN
4174 BIS_DIMENSION_PVT.Value_ID_Conversion
4175 ( p_api_version
4176 , x_Measure_Rec.Dimension3_Short_Name
4177 , x_Measure_Rec.Dimension3_Name
4178 , x_Measure_Rec.Dimension3_ID
4179 , x_return_status
4180 , x_error_Tbl
4181 );
4182 -- EXCEPTION
4183 -- WHEN FND_API.G_EXC_ERROR then
4184 -- NULL;
4185 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4186 l_error_tbl := x_error_tbl;
4187 BIS_UTILITIES_PVT.Add_Error_Message
4188 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
4189 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
4190 , p_error_proc_name => G_PKG_NAME||'.Dimension_Value_ID_Conversion'
4191 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
4192 , p_error_table => l_error_tbl
4193 , x_error_table => x_error_tbl
4194 );
4195 RAISE FND_API.G_EXC_ERROR;
4196 end if;
4197 END;
4198 end if;
4199
4200 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
4201 ( x_Measure_Rec.Dimension4_ID
4202 , x_Measure_Rec.Dimension4_Short_Name
4203 , x_Measure_Rec.Dimension4_Name
4204 );
4205
4206 if (l_convert = FND_API.G_TRUE) then
4207 BEGIN
4208 BIS_DIMENSION_PVT.Value_ID_Conversion
4209 ( p_api_version
4210 , x_Measure_Rec.Dimension4_Short_Name
4211 , x_Measure_Rec.Dimension4_Name
4212 , x_Measure_Rec.Dimension4_ID
4213 , x_return_status
4214 , x_error_Tbl
4215 );
4216 -- EXCEPTION
4217 -- WHEN FND_API.G_EXC_ERROR then
4218 -- NULL;
4219 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4220 l_error_tbl := x_error_tbl;
4221 BIS_UTILITIES_PVT.Add_Error_Message
4222 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
4223 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
4224 , p_error_proc_name => G_PKG_NAME||'.Dimension_Value_ID_Conversion'
4225 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
4226 , p_error_table => l_error_tbl
4227 , x_error_table => x_error_tbl
4228 );
4229 RAISE FND_API.G_EXC_ERROR;
4230 end if;
4231 END;
4232 end if;
4233
4234 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
4235 ( x_Measure_Rec.Dimension5_ID
4236 , x_Measure_Rec.Dimension5_Short_Name
4237 , x_Measure_Rec.Dimension5_Name
4238 );
4239
4240 if (l_convert = FND_API.G_TRUE) then
4241 BEGIN
4242 BIS_DIMENSION_PVT.Value_ID_Conversion
4243 ( p_api_version
4244 , x_Measure_Rec.Dimension5_Short_Name
4245 , x_Measure_Rec.Dimension5_Name
4246 , x_Measure_Rec.Dimension5_ID
4247 , x_return_status
4248 , x_error_Tbl
4249 );
4250 -- EXCEPTION
4251 -- WHEN FND_API.G_EXC_ERROR then
4252 -- NULL;
4253 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4254 l_error_tbl := x_error_tbl;
4255 BIS_UTILITIES_PVT.Add_Error_Message
4256 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
4257 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
4258 , p_error_proc_name => G_PKG_NAME||'.Dimension_Value_ID_Conversion'
4259 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
4260 , p_error_table => l_error_tbl
4261 , x_error_table => x_error_tbl
4262 );
4263 RAISE FND_API.G_EXC_ERROR;
4264 end if;
4265 END;
4266 end if;
4267
4268 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
4269 ( x_Measure_Rec.Dimension6_ID
4270 , x_Measure_Rec.Dimension6_Short_Name
4271 , x_Measure_Rec.Dimension6_Name
4272 );
4273
4274 if (l_convert = FND_API.G_TRUE) then
4275 BEGIN
4276 BIS_DIMENSION_PVT.Value_ID_Conversion
4277 ( p_api_version
4278 , x_Measure_Rec.Dimension6_Short_Name
4279 , x_Measure_Rec.Dimension6_Name
4280 , x_Measure_Rec.Dimension6_ID
4281 , x_return_status
4282 , x_error_Tbl
4283 );
4284 -- EXCEPTION
4285 -- WHEN FND_API.G_EXC_ERROR then
4286 -- NULL;
4287 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4288 l_error_tbl := x_error_tbl;
4289 BIS_UTILITIES_PVT.Add_Error_Message
4290 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
4291 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
4292 , p_error_proc_name => G_PKG_NAME||'.Dimension_Value_ID_Conversion'
4293 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
4294 , p_error_table => l_error_tbl
4295 , x_error_table => x_error_tbl
4296 );
4297 RAISE FND_API.G_EXC_ERROR;
4298 end if;
4299 END;
4300 end if;
4301
4302 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
4303 ( x_Measure_Rec.Dimension7_ID
4304 , x_Measure_Rec.Dimension7_Short_Name
4305 , x_Measure_Rec.Dimension7_Name
4306 );
4307
4308 if (l_convert = FND_API.G_TRUE) then
4309 BEGIN
4310 BIS_DIMENSION_PVT.Value_ID_Conversion
4311 ( p_api_version
4312 , x_Measure_Rec.Dimension7_Short_Name
4313 , x_Measure_Rec.Dimension7_Name
4314 , x_Measure_Rec.Dimension7_ID
4315 , x_return_status
4316 , x_error_Tbl
4317 );
4318 -- EXCEPTION
4319 -- WHEN FND_API.G_EXC_ERROR then
4320 -- NULL;
4321 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4322 l_error_tbl := x_error_tbl;
4323 BIS_UTILITIES_PVT.Add_Error_Message
4324 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
4325 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
4326 , p_error_proc_name => G_PKG_NAME||'.Dimension_Value_ID_Conversion'
4327 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
4328 , p_error_table => l_error_tbl
4329 , x_error_table => x_error_tbl
4330 );
4331 RAISE FND_API.G_EXC_ERROR;
4332 end if;
4333 END;
4334 end if;
4335
4336 EXCEPTION
4337 WHEN NO_DATA_FOUND THEN
4338 x_return_status := FND_API.G_RET_STS_ERROR ;
4339 -- RAISE FND_API.G_EXC_ERROR;
4340 when FND_API.G_EXC_ERROR then
4341 x_return_status := FND_API.G_RET_STS_ERROR ;
4342 -- RAISE FND_API.G_EXC_ERROR;
4343 when FND_API.G_EXC_UNEXPECTED_ERROR then
4344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4345 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4346 when others then
4347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4348 --added last two params
4349 l_error_tbl := x_error_tbl;
4350 BIS_UTILITIES_PVT.Add_Error_Message
4351 ( p_error_msg_id => SQLCODE
4352 , p_error_description => SQLERRM
4353 , p_error_proc_name => G_PKG_NAME||'.Dimension_Value_ID_Conversion'
4354 , p_error_table => l_error_tbl
4355 , x_error_table => x_error_tbl
4356 );
4357 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4358
4359 END Dimension_Value_ID_Conversion;
4360 --
4361
4362
4363 Procedure Retrieve_Measure_Dimensions
4364 ( p_api_version IN NUMBER
4365 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
4366 , x_dimension_Tbl OUT NOCOPY BIS_DIMENSION_PUB.Dimension_Tbl_Type
4367 , x_return_status OUT NOCOPY VARCHAR2
4368 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
4369 )
4370 IS
4371
4372 i NUMBER := 0;
4373 l_dimension_rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
4374 l_dimension_rec_p BIS_DIMENSION_PUB.Dimension_Rec_Type;
4375 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4376
4377 cursor ind_dim(p_measure_id number) is
4378 select id.dimension_id
4379 from bis_indicator_dimensions id
4380 where id.indicator_id = p_measure_id
4381 order by id.sequence_no;
4382
4383 BEGIN
4384
4385 x_return_status := FND_API.G_RET_STS_SUCCESS;
4386
4387 for indim in ind_dim(p_measure_rec.measure_id) loop
4388
4389 i := i+1;
4390 l_dimension_rec.dimension_id := indim.dimension_id;
4391
4392 l_dimension_rec_p := l_dimension_rec;
4393 BIS_DIMENSION_PUB.Retrieve_Dimension
4394 ( p_api_version => 1.0
4395 , p_Dimension_Rec => l_dimension_rec_p
4396 , x_Dimension_Rec => l_dimension_rec
4397 , x_return_status => x_return_status
4398 , x_error_Tbl => x_error_Tbl
4399 );
4400
4401 x_dimension_Tbl(i) := l_dimension_rec;
4402
4403 end loop;
4404
4405 if (i = 0) then
4406 l_error_tbl := x_error_tbl;
4407 BIS_UTILITIES_PVT.Add_Error_Message
4408 ( p_error_msg_name => 'BIS_INVALID_MEASURE_VALUE'
4409 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
4410 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Measure_Dimensions'
4411 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
4412 , p_error_table => l_error_tbl
4413 , x_error_table => x_error_tbl
4414 );
4415 RAISE FND_API.G_EXC_ERROR;
4416 end if;
4417
4418 IF ind_dim%ISOPEN THEN CLOSE ind_dim; END IF;
4419
4420 --commented RAISE
4421 EXCEPTION
4422 WHEN NO_DATA_FOUND THEN
4423 x_return_status := FND_API.G_RET_STS_ERROR ;
4424 IF ind_dim%ISOPEN THEN CLOSE ind_dim; END IF;
4425 -- RAISE FND_API.G_EXC_ERROR;
4426 when FND_API.G_EXC_ERROR then
4427 x_return_status := FND_API.G_RET_STS_ERROR ;
4428 IF ind_dim%ISOPEN THEN CLOSE ind_dim; END IF;
4429 -- RAISE FND_API.G_EXC_ERROR;
4430 when FND_API.G_EXC_UNEXPECTED_ERROR then
4431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4432 IF ind_dim%ISOPEN THEN CLOSE ind_dim; END IF;
4433 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4434 when others then
4435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4436 IF ind_dim%ISOPEN THEN CLOSE ind_dim; END IF;
4437 --added last two params
4438 l_error_tbl := x_error_tbl;
4439 BIS_UTILITIES_PVT.Add_Error_Message
4440 ( p_error_msg_id => SQLCODE
4441 , p_error_description => SQLERRM
4442 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Measure_Dimensions'
4443 , p_error_table => l_error_tbl
4444 , x_error_table => x_error_tbl
4445 );
4446 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4447
4448 END Retrieve_Measure_Dimensions;
4449 --
4450 --
4451 PROCEDURE Retrieve_Last_Update_Date
4452 ( p_api_version IN NUMBER
4453 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
4454 , x_last_update_date OUT NOCOPY DATE
4455 , x_return_status OUT NOCOPY VARCHAR2
4456 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
4457 )
4458 IS
4459 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4460 BEGIN
4461 x_return_status := FND_API.G_RET_STS_SUCCESS;
4462 --
4463 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Measure_Rec.Measure_ID)
4464 = FND_API.G_TRUE
4465 AND BIS_UTILITIES_PUB.Value_Not_NULL(p_Measure_Rec.Measure_ID)
4466 = FND_API.G_TRUE
4467 ) THEN
4468 SELECT NVL(LAST_UPDATE_DATE, CREATION_DATE)
4469 INTO x_last_update_date
4470 FROM bis_indicators
4471 WHERE INDICATOR_ID = p_Measure_Rec.Measure_ID;
4472 END IF;
4473 --
4474
4475 --commented RAISE
4476 EXCEPTION
4477 WHEN NO_DATA_FOUND THEN
4478 --added this message
4479 l_error_tbl := x_error_tbl;
4480 BIS_UTILITIES_PVT.Add_Error_Message
4481 ( p_error_msg_name => 'BIS_INVALID_MEASURE_VALUE'
4482 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
4483 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Last_Update_Date'
4484 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
4485 , p_error_table => l_error_tbl
4486 , x_error_table => x_error_tbl
4487 );
4488 x_return_status := FND_API.G_RET_STS_ERROR;
4489 x_return_status := FND_API.G_RET_STS_ERROR;
4490 --RAISE FND_API.G_EXC_ERROR;
4491 WHEN FND_API.G_EXC_ERROR THEN
4492 x_return_status:= FND_API.G_RET_STS_ERROR;
4493 --RAISE;
4494 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4495 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
4496 --RAISE;
4497 WHEN OTHERS THEN
4498 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
4499 --added last two params
4500 l_error_tbl := x_error_tbl;
4501 BIS_UTILITIES_PVT.Add_Error_Message
4502 ( p_error_msg_id => SQLCODE
4503 , p_error_description => SQLERRM
4504 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Last_Update_Date'
4505 , p_error_table => l_error_tbl
4506 , x_error_table => x_error_tbl
4507 );
4508 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4509 --
4510 END Retrieve_Last_Update_Date;
4511 --
4512 --
4513 PROCEDURE Lock_Record
4514 ( p_api_version IN NUMBER
4515 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
4516 , p_timestamp IN VARCHAR := NULL
4517 , x_return_status OUT NOCOPY VARCHAR2
4518 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
4519 )
4520 IS
4521 --
4522 l_form_date DATE;
4523 l_last_update_date DATE;
4524 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
4525 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4526 --
4527 BEGIN
4528 x_return_status := FND_API.G_RET_STS_SUCCESS;
4529 --
4530 l_Measure_Rec.Measure_Id := p_Measure_Rec.Measure_Id;
4531 BIS_MEASURE_PVT.Retrieve_Last_Update_Date
4532 ( p_api_version => 1.0
4533 , p_Measure_Rec => p_Measure_Rec
4534 , x_last_update_date => l_last_update_date
4535 , x_return_status => x_return_status
4536 , x_error_Tbl => x_error_Tbl
4537 );
4538 IF(p_timestamp IS NOT NULL) THEN
4539 l_form_date := TO_DATE(p_timestamp, BIS_UTILITIES_PVT.G_DATE_FORMAT);
4540 IF(l_form_date = l_last_update_date) THEN
4541 x_return_status := FND_API.G_TRUE;
4542 ELSE
4543 x_return_status := FND_API.G_FALSE;
4544 END IF;
4545 ELSE
4546 x_return_status := FND_API.G_FALSE;
4547 END IF;
4548 --
4549
4550 --commented RAISE
4551 EXCEPTION
4552 WHEN FND_API.G_EXC_ERROR THEN
4553 x_return_status:= FND_API.G_RET_STS_ERROR;
4554 --RAISE;
4555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4556 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
4557 -- RAISE;
4558 WHEN OTHERS THEN
4559 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
4560 --added last two params
4561 l_error_tbl := x_error_tbl;
4562 BIS_UTILITIES_PVT.Add_Error_Message
4563 ( p_error_msg_id => SQLCODE
4564 , p_error_description => SQLERRM
4565 , p_error_proc_name => G_PKG_NAME||'.Lock_Record'
4566 , p_error_table => l_error_tbl
4567 , x_error_table => x_error_tbl
4568 );
4569 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4570 --
4571 END Lock_Record;
4572 --
4573 --
4574 -- APIS to handle Applicaiton measures
4575 --
4576 --
4577 --
4578 --
4579 --
4580 PROCEDURE Create_Application_Measure
4581 ( p_api_version IN NUMBER
4582 , p_commit IN VARCHAR2 := FND_API.G_FALSE
4583 , p_Measure_rec IN BIS_MEASURE_PUB.Measure_Rec_Type
4584 , p_application_rec IN BIS_Application_PVT.Application_Rec_Type
4585 , p_owning_application IN VARCHAR2
4586 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
4587 , x_return_status OUT NOCOPY VARCHAR2
4588 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
4589 )
4590 IS
4591 -- l_error_count number;
4592 l_rec BIS_APPLICATION_MEASURE_PVT.Application_Measure_Rec_type;
4593 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4594 BEGIN
4595 x_return_status := FND_API.G_RET_STS_SUCCESS;
4596 l_rec.Measure_id := p_Measure_rec.Measure_id;
4597 l_rec.Application_id := p_application_rec.application_id;
4598 l_rec.owning_application := p_owning_application;
4599 l_rec.Created_By := p_Measure_rec.Created_By;
4600 l_rec.Last_Updated_By := p_Measure_rec.Last_Updated_By;
4601 l_rec.Last_Update_Login := p_Measure_rec.Last_Update_Login;
4602
4603 -- htp.header(2,'POINT 2 CREATE_APPLICATION_MEASURE');
4604 -- BIS_ERROR_MESSAGE_PVT.get_error_count(l_error_count,x_return_status,x_error_tbl);
4605 -- htp.header(3,'Error Count = ' || l_error_count);
4606
4607 BIS_APPLICATION_MEASURE_PVT.Create_Application_Measure
4608 ( p_api_version => p_api_version
4609 , p_commit => p_commit
4610 , p_Application_Measure_Rec => l_rec
4611 , p_owner => p_owner
4612 , x_return_status => x_return_status
4613 , x_error_tbl => x_error_tbl
4614 );
4615
4616 -- htp.header(2,'POINT 3 CREATE_APPLICATION_MEASURE');
4617 -- BIS_ERROR_MESSAGE_PVT.get_error_count(l_error_count,x_return_status,x_error_tbl);
4618 -- htp.header(3,'Error Count = ' || l_error_count);
4619
4620 --commented RAISE
4621 EXCEPTION
4622 WHEN NO_DATA_FOUND THEN
4623 x_return_status := FND_API.G_RET_STS_ERROR ;
4624 --RAISE FND_API.G_EXC_ERROR;
4625 when FND_API.G_EXC_ERROR then
4626 -- htp.header(2,'EXCEPTION 2 CREATE_APPLICATION_MEASURE');
4627 -- BIS_ERROR_MESSAGE_PVT.get_error_count(l_error_count,x_return_status,x_error_tbl);
4628 -- htp.header(3,'Error Count = ' || l_error_count);
4629
4630 x_return_status := FND_API.G_RET_STS_ERROR ;
4631 --RAISE FND_API.G_EXC_ERROR;
4632 when FND_API.G_EXC_UNEXPECTED_ERROR then
4633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4634 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4635 when others then
4636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4637 --added last two params
4638 l_error_tbl := x_error_tbl;
4639 BIS_UTILITIES_PVT.Add_Error_Message
4640 ( p_error_msg_id => SQLCODE
4641 , p_error_description => SQLERRM
4642 , p_error_proc_name => G_PKG_NAME||'.Create_Application_Measure'
4643 , p_error_table => l_error_tbl
4644 , x_error_table => x_error_tbl
4645 );
4646 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4647
4648 end Create_Application_Measure;
4649 --
4650 --
4651 PROCEDURE Retrieve_Application_Measures
4652 ( p_api_version IN NUMBER
4653 , p_Measure_Rec IN BIS_Measure_PUB.Measure_Rec_Type
4654 , p_all_info IN VARCHAR2
4655 , x_owning_Application_rec OUT NOCOPY BIS_Application_PVT.Application_Rec_Type
4656 , x_Application_tbl OUT NOCOPY BIS_Application_PVT.Application_Tbl_Type
4657 , x_return_status OUT NOCOPY VARCHAR2
4658 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
4659 )
4660 IS
4661 l_tbl BIS_Application_Measure_PVT.Application_Measure_Tbl_Type;
4662 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4663 BEGIN
4664 x_return_status := FND_API.G_RET_STS_SUCCESS;
4665
4666 BIS_APPLICATION_MEASURE_PVT.Retrieve_Application_Measures( p_api_version
4667 , p_Measure_Rec , p_all_info
4668 , l_tbl
4669 , x_return_status
4670 , x_error_Tbl
4671 );
4672
4673 for i in 1 .. l_tbl.COUNT LOOP
4674 x_Application_tbl(i).Application_id := l_tbl(i).Application_id;
4675 x_Application_tbl(i).Application_short_name :=
4676 l_tbl(i).Application_short_name;
4677 x_Application_tbl(i).Application_name := l_tbl(i).Application_name;
4678
4679 if (l_tbl(i).owning_application = FND_API.G_TRUE) then
4680 x_owning_Application_rec := x_Application_tbl(i);
4681 end if;
4682 END LOOP;
4683
4684 --commented RAISE
4685 EXCEPTION
4686 WHEN NO_DATA_FOUND THEN
4687 x_return_status := FND_API.G_RET_STS_ERROR ;
4688 --RAISE FND_API.G_EXC_ERROR;
4689 when FND_API.G_EXC_ERROR then
4690 x_return_status := FND_API.G_RET_STS_ERROR ;
4691 --RAISE FND_API.G_EXC_ERROR;
4692 when FND_API.G_EXC_UNEXPECTED_ERROR then
4693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4694 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4695 when others then
4696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4697 --added last two params
4698 l_error_tbl := x_error_tbl;
4699 BIS_UTILITIES_PVT.Add_Error_Message
4700 ( p_error_msg_id => SQLCODE
4701 , p_error_description => SQLERRM
4702 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Application_Measures'
4703 , p_error_table => l_error_tbl
4704 , x_error_table => x_error_tbl
4705 );
4706 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4707
4708 end Retrieve_Application_Measures;
4709 --
4710 --
4711 PROCEDURE Update_Application_Measure
4712 ( p_api_version IN NUMBER
4713 , p_commit IN VARCHAR2 := FND_API.G_FALSE
4714 , p_Measure_rec IN BIS_MEASURE_PUB.Measure_Rec_Type
4715 , p_application_rec IN BIS_Application_PVT.Application_Rec_Type
4716 , p_owning_application IN VARCHAR2
4717 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
4718 , x_return_status OUT NOCOPY VARCHAR2
4719 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
4720 )
4721 IS
4722 l_rec BIS_APPLICATION_MEASURE_PVT.Application_Measure_Rec_type;
4723 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4724 BEGIN
4725 x_return_status := FND_API.G_RET_STS_SUCCESS;
4726
4727 l_rec.Measure_id := p_Measure_rec.Measure_id;
4728 l_rec.Application_id := p_application_rec.application_id;
4729 l_rec.owning_application := p_owning_application;
4730
4731 BIS_APPLICATION_MEASURE_PVT.Update_Application_Measure( p_api_version
4732 , p_commit
4733 , l_rec
4734 , p_owner
4735 , x_return_status
4736 , x_error_tbl
4737 );
4738 --commented RAISE
4739 EXCEPTION
4740 WHEN NO_DATA_FOUND THEN
4741 x_return_status := FND_API.G_RET_STS_ERROR ;
4742 --RAISE FND_API.G_EXC_ERROR;
4743 when FND_API.G_EXC_ERROR then
4744 x_return_status := FND_API.G_RET_STS_ERROR ;
4745 --RAISE FND_API.G_EXC_ERROR;
4746 when FND_API.G_EXC_UNEXPECTED_ERROR then
4747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4748 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4749 when others then
4750 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4751 --added last two params
4752 l_error_tbl := x_error_tbl;
4753 BIS_UTILITIES_PVT.Add_Error_Message
4754 ( p_error_msg_id => SQLCODE
4755 , p_error_description => SQLERRM
4756 , p_error_proc_name => G_PKG_NAME||'.Update_Application_Measure'
4757 , p_error_table => l_error_tbl
4758 , x_error_table => x_error_tbl
4759 );
4760 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4761
4762 end Update_Application_Measure;
4763 --
4764 --
4765 PROCEDURE Delete_Application_Measure
4766 ( p_api_version IN NUMBER
4767 , p_commit IN VARCHAR2 := FND_API.G_FALSE
4768 , p_Measure_rec IN BIS_MEASURE_PUB.Measure_Rec_Type
4769 , p_application_rec IN BIS_Application_PVT.Application_Rec_Type
4770 , x_return_status OUT NOCOPY VARCHAR2
4771 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
4772 )
4773 IS
4774 l_rec BIS_APPLICATION_MEASURE_PVT.Application_Measure_Rec_type;
4775 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4776 BEGIN
4777 x_return_status := FND_API.G_RET_STS_SUCCESS;
4778
4779 l_rec.Measure_id := p_Measure_rec.Measure_id;
4780 l_rec.Application_id := p_application_rec.application_id;
4781
4782 BIS_APPLICATION_MEASURE_PVT.Delete_Application_Measure( p_api_version
4783 , p_commit
4784 , l_rec
4785 , x_return_status
4786 , x_error_tbl
4787 );
4788
4789 --commented RAISE
4790 EXCEPTION
4791 WHEN NO_DATA_FOUND THEN
4792 x_return_status := FND_API.G_RET_STS_ERROR ;
4793 --RAISE FND_API.G_EXC_ERROR;
4794 when FND_API.G_EXC_ERROR then
4795 x_return_status := FND_API.G_RET_STS_ERROR ;
4796 -- RAISE FND_API.G_EXC_ERROR;
4797 when FND_API.G_EXC_UNEXPECTED_ERROR then
4798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4799 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4800 when others then
4801 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4802 --added last two params
4803 l_error_tbl := x_error_tbl;
4804 BIS_UTILITIES_PVT.Add_Error_Message
4805 ( p_error_msg_id => SQLCODE
4806 , p_error_description => SQLERRM
4807 , p_error_proc_name => G_PKG_NAME||'.Delete_Application_Measure'
4808 , p_error_table => l_error_tbl
4809 , x_error_table => x_error_tbl
4810 );
4811 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4812
4813 end Delete_Application_Measure;
4814 --
4815 PROCEDURE Delete_Application_Measures
4816 ( p_api_version IN NUMBER
4817 , p_commit IN VARCHAR2 := FND_API.G_FALSE
4818 , p_Measure_rec IN BIS_MEASURE_PUB.Measure_Rec_Type
4819 , x_return_status OUT NOCOPY VARCHAR2
4820 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
4821 )
4822 IS
4823 l_rec BIS_APPLICATION_PVT.Application_Rec_type;
4824 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4825 BEGIN
4826 x_return_status := FND_API.G_RET_STS_SUCCESS;
4827
4828 -- we should call the delete API on BIS_APPLICATION_MEASURE
4829 -- but this is much faster
4830
4831 delete from bis_application_measures
4832 where indicator_id = p_Measure_rec.Measure_id;
4833
4834 l_rec.Application_id := -1;
4835
4836 Create_Application_Measure
4837 ( p_api_version => p_api_version
4838 ,p_commit => p_commit
4839 ,p_Measure_rec => p_Measure_rec
4840 ,p_application_rec => l_rec
4841 ,p_owning_application => FND_API.G_FALSE
4842 ,x_return_status => x_return_status
4843 ,x_error_Tbl => x_error_Tbl
4844 );
4845
4846 --commented RAISE
4847 EXCEPTION
4848 WHEN NO_DATA_FOUND THEN
4849 x_return_status := FND_API.G_RET_STS_ERROR ;
4850 --RAISE FND_API.G_EXC_ERROR;
4851 when FND_API.G_EXC_ERROR then
4852 x_return_status := FND_API.G_RET_STS_ERROR ;
4853 --RAISE FND_API.G_EXC_ERROR;
4854 when FND_API.G_EXC_UNEXPECTED_ERROR then
4855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4856 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4857 when others then
4858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4859 --added last two params
4860 l_error_tbl := x_error_tbl;
4861 BIS_UTILITIES_PVT.Add_Error_Message
4862 ( p_error_msg_id => SQLCODE
4863 , p_error_description => SQLERRM
4864 , p_error_proc_name => G_PKG_NAME||'.Delete_Application_Measure'
4865 , p_error_table => l_error_tbl
4866 , x_error_table => x_error_tbl
4867 );
4868 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4869
4870 end Delete_Application_Measures;
4871 --
4872 PROCEDURE Lock_Record
4873 ( p_api_version IN NUMBER
4874 , p_Measure_rec IN BIS_MEASURE_PUB.Measure_Rec_Type
4875 , p_application_rec IN BIS_Application_PVT.Application_Rec_Type
4876 , p_timestamp IN VARCHAR := NULL
4877 , x_return_status OUT NOCOPY VARCHAR2
4878 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
4879 )
4880 IS
4881 l_rec BIS_APPLICATION_MEASURE_PVT.Application_Measure_Rec_type;
4882 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4883 BEGIN
4884 x_return_status := FND_API.G_RET_STS_SUCCESS;
4885
4886 l_rec.Measure_id := p_Measure_rec.Measure_id;
4887 l_rec.Application_id := p_application_rec.application_id;
4888
4889 BIS_APPLICATION_MEASURE_PVT.Lock_record( p_api_version
4890 , l_rec
4891 , p_timestamp
4892 , x_return_status
4893 , x_error_tbl
4894 );
4895 --commented RAISE
4896 EXCEPTION
4897 WHEN NO_DATA_FOUND THEN
4898 x_return_status := FND_API.G_RET_STS_ERROR ;
4899 --RAISE FND_API.G_EXC_ERROR;
4900 when FND_API.G_EXC_ERROR then
4901 x_return_status := FND_API.G_RET_STS_ERROR ;
4902 -- RAISE FND_API.G_EXC_ERROR;
4903 when FND_API.G_EXC_UNEXPECTED_ERROR then
4904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4905 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4906 when others then
4907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4908 --added last two params
4909 l_error_tbl := x_error_tbl;
4910 BIS_UTILITIES_PVT.Add_Error_Message
4911 ( p_error_msg_id => SQLCODE
4912 , p_error_description => SQLERRM
4913 , p_error_proc_name => G_PKG_NAME||'.Lock_Record'
4914 , p_error_table => l_error_tbl
4915 , x_error_table => x_error_tbl
4916 );
4917 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4918
4919 end Lock_Record;
4920 --
4921 PROCEDURE Retrieve_Last_Update_Date
4922 ( p_api_version IN NUMBER
4923 , p_Measure_rec IN BIS_MEASURE_PUB.Measure_Rec_Type
4924 , p_application_rec IN BIS_Application_PVT.Application_Rec_Type
4925 , x_last_update_date OUT NOCOPY DATE
4926 , x_return_status OUT NOCOPY VARCHAR2
4927 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
4928 )
4929 IS
4930 l_rec BIS_APPLICATION_MEASURE_PVT.Application_Measure_Rec_type;
4931 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4932 BEGIN
4933 x_return_status := FND_API.G_RET_STS_SUCCESS;
4934
4935 l_rec.Measure_id := p_Measure_rec.Measure_id;
4936 l_rec.Application_id := p_application_rec.application_id;
4937
4938 BIS_APPLICATION_MEASURE_PVT.Retrieve_Last_Update_Date( p_api_version
4939 , l_rec
4940 , x_last_update_date
4941 , x_return_status
4942 , x_error_tbl
4943 );
4944 --commented RAISE
4945 EXCEPTION
4946 WHEN NO_DATA_FOUND THEN
4947 x_return_status := FND_API.G_RET_STS_ERROR ;
4948 --RAISE FND_API.G_EXC_ERROR;
4949 when FND_API.G_EXC_ERROR then
4950 x_return_status := FND_API.G_RET_STS_ERROR ;
4951 --RAISE FND_API.G_EXC_ERROR;
4952 when FND_API.G_EXC_UNEXPECTED_ERROR then
4953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4954 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4955 when others then
4956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4957 --added last two params
4958 l_error_tbl := x_error_tbl;
4959 BIS_UTILITIES_PVT.Add_Error_Message
4960 ( p_error_msg_id => SQLCODE
4961 , p_error_description => SQLERRM
4962 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Last_Update_Date'
4963 , p_error_table => l_error_tbl
4964 , x_error_table => x_error_tbl
4965 );
4966 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4967
4968 end Retrieve_Last_Update_Date;
4969 --
4970
4971 FUNCTION IS_OLD_DATA_MODEL
4972 ( p_Measure_rec IN BIS_MEASURE_PUB.MEASURE_REC_TYPE
4973 ,p_Org_Dimension_Id IN NUMBER
4974 ,p_Time_Dimension_Id IN NUMBER
4975 )
4976 RETURN BOOLEAN
4977 IS
4978 l_org_time_exists BOOLEAN;
4979
4980 BEGIN
4981 -- IF either org or time exists then return true here. As it should never
4982 -- happen that, only org got migrated and not time
4983 l_org_time_exists := TRUE;
4984
4985 IF (BIS_UTILITIES_PUB.Value_Missing(p_Org_Dimension_Id) = FND_API.G_TRUE
4986 OR BIS_UTILITIES_PUB.Value_NULL(p_Org_Dimension_Id) = FND_API.G_TRUE)
4987 THEN
4988 l_org_time_exists := FALSE;
4989 GOTO returnfromproc;
4990 END IF;
4991
4992 IF (BIS_UTILITIES_PUB.Value_Missing(p_Time_Dimension_Id) = FND_API.G_TRUE
4993 OR BIS_UTILITIES_PUB.Value_NULL(p_Time_Dimension_Id) = FND_API.G_TRUE)
4994 THEN
4995 l_org_time_exists := FALSE;
4996 GOTO returnfromproc;
4997 END IF;
4998
4999
5000 IF (p_Org_Dimension_Id = p_Measure_rec.Dimension1_Id)
5001 THEN
5002 l_org_time_exists := FALSE;
5003 GOTO returnfromproc;
5004 END IF;
5005 IF (p_Org_Dimension_Id = p_Measure_rec.Dimension2_Id)
5006 THEN
5007 l_org_time_exists := FALSE;
5008 GOTO returnfromproc;
5009 END IF;
5010 IF (p_Org_Dimension_Id = p_Measure_rec.Dimension3_Id)
5011 THEN
5012 l_org_time_exists := FALSE;
5013 GOTO returnfromproc;
5014 END IF;
5015 IF (p_Org_Dimension_Id = p_Measure_rec.Dimension4_Id)
5016 THEN
5017 l_org_time_exists := FALSE;
5018 GOTO returnfromproc;
5019 END IF;
5020 IF (p_Org_Dimension_Id = p_Measure_rec.Dimension5_Id)
5021 THEN
5022 l_org_time_exists := FALSE;
5023 GOTO returnfromproc;
5024 END IF;
5025 IF (p_Org_Dimension_Id = p_Measure_rec.Dimension6_Id)
5026 THEN
5027 l_org_time_exists := FALSE;
5028 GOTO returnfromproc;
5029 END IF;
5030 IF (p_Org_Dimension_Id = p_Measure_rec.Dimension7_Id)
5031 THEN
5032 l_org_time_exists := FALSE;
5033 GOTO returnfromproc;
5034 END IF;
5035 IF (p_Time_Dimension_Id = p_Measure_rec.Dimension1_Id)
5036 THEN
5037 l_org_time_exists := FALSE;
5038 GOTO returnfromproc;
5039 END IF;
5040 IF (p_Time_Dimension_Id = p_Measure_rec.Dimension2_Id)
5041 THEN
5042 l_org_time_exists := FALSE;
5043 GOTO returnfromproc;
5044 END IF;
5045 IF (p_Time_Dimension_Id = p_Measure_rec.Dimension3_Id)
5046 THEN
5047 l_org_time_exists := FALSE;
5048 GOTO returnfromproc;
5049 END IF;
5050 IF (p_Time_Dimension_Id = p_Measure_rec.Dimension4_Id)
5051 THEN
5052 l_org_time_exists := FALSE;
5053 GOTO returnfromproc;
5054 END IF;
5055 IF (p_Time_Dimension_Id = p_Measure_rec.Dimension5_Id)
5056 THEN
5057 l_org_time_exists := FALSE;
5058 GOTO returnfromproc;
5059 END IF;
5060 IF p_Time_Dimension_Id = p_Measure_rec.Dimension6_Id
5061 THEN
5062 l_org_time_exists := FALSE;
5063 GOTO returnfromproc;
5064 END IF;
5065 IF p_Time_Dimension_Id = p_Measure_rec.Dimension7_Id
5066 THEN
5067 l_org_time_exists := FALSE;
5068 GOTO returnfromproc;
5069 END IF;
5070 <<returnfromproc>>
5071 RETURN l_org_time_exists;
5072 END ;
5073
5074
5075 PROCEDURE updt_pm_owner(p_pm_short_name IN VARCHAR2
5076 ,x_return_status OUT NOCOPY VARCHAR2) AS
5077 CURSOR c_updt1 IS
5078 SELECT indicator_id
5079 FROM bis_indicators
5080 WHERE short_name = p_pm_short_name FOR UPDATE OF last_updated_by , created_by;
5081
5082 l_pm_count NUMBER := 0;
5083
5084 BEGIN
5085
5086 x_return_status := FND_API.G_RET_STS_SUCCESS;
5087
5088
5089 FOR i IN c_updt1 LOOP
5090
5091 l_pm_count := l_pm_count + 1;
5092
5093
5094 UPDATE bis_indicators SET last_updated_by = 1 , created_by = 1
5095 WHERE current of c_updt1;
5096
5097 UPDATE bis_indicators_tl SET last_updated_by = 1 , created_by = 1
5098 WHERE indicator_id = i.indicator_id;
5099
5100 UPDATE bis_indicator_dimensions SET last_updated_by = 1 , created_by = 1
5101 WHERE indicator_id = i.indicator_id;
5102
5103 END LOOP;
5104
5105 if l_pm_count = 0 then
5106 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5107 end if;
5108
5109 EXCEPTION
5110 WHEN OTHERS THEN
5111 IF c_updt1%ISOPEN THEN
5112 CLOSE c_updt1;
5113 END IF;
5114
5115
5116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5117 END updt_pm_owner;
5118
5119 --sawu: bug#3859267: common function to check if a particular ak_region_item is mapped already
5120 FUNCTION isColumnMappedAlready(
5121 p_region_code IN Ak_Region_Items.REGION_CODE%TYPE
5122 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type
5123 ,p_attribute_code IN Ak_Region_Items.ATTRIBUTE_CODE%Type
5124 ,p_attribute_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type
5125 ,x_measure_short_name OUT NOCOPY Bisbv_Performance_Measures.MEASURE_SHORT_NAME%TYPE
5126 ,x_measure_name OUT NOCOPY Bisbv_Performance_Measures.MEASURE_NAME%TYPE
5127 ) RETURN boolean IS
5128 l_result boolean := false;
5129 l_attribute1 Ak_Region_Items.Attribute1%Type;
5130 l_attribute2 Bisbv_Performance_Measures.MEASURE_NAME%TYPE;
5131 BEGIN
5132 SELECT attribute1, attribute2 into l_attribute1, l_attribute2
5133 FROM Ak_Region_Items
5134 WHERE region_code = p_region_code
5135 AND region_application_id = p_region_app_id
5136 AND attribute_code = p_attribute_code
5137 AND attribute_application_id = p_attribute_app_id;
5138
5139 IF (BIS_AK_REGION_PUB.IS_MEASURE_TYPE(l_attribute1)) THEN
5140 l_result := BIS_AK_REGION_PUB.VALIDATE_MEASURE(p_short_name => l_attribute2,
5141 x_measure_short_name => x_measure_short_name,
5142 x_measure_name => x_measure_name);
5143 ELSIF (BIS_AK_REGION_PUB.IS_COMPARE_TYPE(l_attribute1)) THEN
5144 l_result := BIS_AK_REGION_PUB.VALIDATE_COMPARE(p_region_code => p_region_code,
5145 p_region_app_id => p_region_app_id,
5146 p_compare_code => l_attribute2,
5147 x_measure_short_name => x_measure_short_name,
5148 x_measure_name => x_measure_name);
5149 END IF;
5150
5151 RETURN l_result;
5152
5153 EXCEPTION
5154 WHEN others THEN RETURN false;
5155 END isColumnMappedAlready;
5156
5157 -- mdamle 07/18/2003 - Check if measure is being mapped to a source
5158 -- that's already mapped to another measure.
5159 FUNCTION isSourceColumnMappedAlready(
5160 p_Measure_rec IN BIS_MEASURE_PUB.MEASURE_REC_TYPE
5161 ,x_measure_name OUT NOCOPY Bisbv_Performance_Measures.MEASURE_NAME%TYPE
5162 ) RETURN boolean IS
5163 l_result boolean := false;
5164 l_region_code Ak_Region_Items.REGION_CODE%TYPE;
5165 l_source_column Ak_Region_Items.ATTRIBUTE_CODE%TYPE;
5166 l_measure_short_name Bisbv_Performance_Measures.MEASURE_SHORT_NAME%TYPE;
5167 BEGIN
5168 IF (p_measure_rec.actual_data_source IS NOT NULL) THEN
5169 l_region_code := substr(p_measure_rec.actual_data_source, 1, instr(p_measure_rec.actual_data_source, '.') -1);
5170 l_source_column := substr(p_measure_rec.actual_data_source, instr(p_measure_rec.actual_data_source, '.') +1);
5171 l_result := isColumnMappedAlready(p_region_code => l_region_code,
5172 p_region_app_id => p_measure_rec.Region_App_Id,
5173 p_attribute_code => l_source_column,
5174 p_attribute_app_id => p_measure_rec.Source_Column_App_Id,
5175 x_measure_short_name => l_measure_short_name,
5176 x_measure_name => x_measure_name);
5177 --make sure current mapping is not to this measure
5178 IF ((l_result = true) AND (p_Measure_rec.measure_short_name = l_measure_short_name)) THEN
5179 l_result := false;
5180 END IF;
5181 END IF;
5182
5183 RETURN l_result;
5184
5185 EXCEPTION
5186 WHEN others THEN RETURN false;
5187 END isSourceColumnMappedAlready;
5188
5189 --sawu: bug#3859267: need to validate compare-to column
5190 FUNCTION isCompareColumnMappedAlready(
5191 p_Measure_rec IN BIS_MEASURE_PUB.MEASURE_REC_TYPE
5192 ,x_measure_name OUT NOCOPY Bisbv_Performance_Measures.MEASURE_NAME%TYPE
5193 ) RETURN boolean IS
5194 l_result boolean := false;
5195 l_region_code Ak_Region_Items.REGION_CODE%TYPE;
5196 l_compare_column Ak_Region_Items.ATTRIBUTE_CODE%TYPE;
5197 l_measure_short_name Bisbv_Performance_Measures.MEASURE_SHORT_NAME%TYPE;
5198 BEGIN
5199 IF (p_measure_rec.Comparison_Source IS NOT NULL) THEN
5200 l_region_code := substr(p_measure_rec.Comparison_Source, 1, instr(p_measure_rec.Comparison_Source, '.') -1);
5201 l_compare_column := substr(p_measure_rec.Comparison_Source, instr(p_measure_rec.Comparison_Source, '.') +1);
5202 l_result := isColumnMappedAlready(p_region_code => l_region_code,
5203 p_region_app_id => p_measure_rec.Region_App_Id,
5204 p_attribute_code => l_compare_column,
5205 p_attribute_app_id => p_measure_rec.Compare_Column_App_Id,
5206 x_measure_short_name => l_measure_short_name,
5207 x_measure_name => x_measure_name);
5208 --make sure current mapping is not to this measure
5209 IF ((l_result = true) AND (p_Measure_rec.measure_short_name = l_measure_short_name)) THEN
5210 l_result := false;
5211 END IF;
5212 END IF;
5213
5214 RETURN l_result;
5215
5216 EXCEPTION
5217 WHEN others THEN RETURN false;
5218 END isCompareColumnMappedAlready;
5219
5220 -- mdamle 09/25/2003 - Sync up measures for all installed languages
5221 Procedure Translate_Measure_by_lang
5222 ( p_api_version IN NUMBER
5223 , p_commit IN VARCHAR2 := FND_API.G_FALSE
5224 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
5225 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
5226 , p_lang IN VARCHAR2
5227 , p_source_lang IN VARCHAR2
5228 , x_return_status OUT NOCOPY VARCHAR2
5229 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
5230 )
5231 IS
5232 l_user_id NUMBER;
5233 l_login_id NUMBER;
5234 l_count NUMBER := 0;
5235 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
5236
5237 BEGIN
5238
5239 SAVEPOINT TransMeasByLangPvt;
5240
5241 l_user_id := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
5242
5243 l_login_id := fnd_global.LOGIN_ID;
5244
5245 Update bis_INDICATORS_TL
5246 set
5247 NAME = p_Measure_Rec.Measure_Name
5248 , DESCRIPTION = p_Measure_Rec.description
5249 , LAST_UPDATED_BY = l_user_id
5250 , LAST_UPDATE_LOGIN = l_login_id
5251 , SOURCE_LANG = p_source_lang
5252 where INDICATOR_ID = p_Measure_Rec.Measure_Id
5253 and LANGUAGE = p_lang;
5254
5255 if (p_commit = FND_API.G_TRUE) then
5256 COMMIT;
5257 end if;
5258
5259 EXCEPTION
5260 WHEN NO_DATA_FOUND THEN
5261 ROLLBACK TO TransMeasByLangPvt;
5262 x_return_status := FND_API.G_RET_STS_ERROR ;
5263 when FND_API.G_EXC_ERROR then
5264 ROLLBACK TO TransMeasByLangPvt;
5265 x_return_status := FND_API.G_RET_STS_ERROR ;
5266 when FND_API.G_EXC_UNEXPECTED_ERROR then
5267 ROLLBACK TO TransMeasByLangPvt;
5268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5269 when others then
5270 ROLLBACK TO TransMeasByLangPvt;
5271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5272 --added last two params
5273 l_error_tbl := x_error_tbl;
5274 BIS_UTILITIES_PVT.Add_Error_Message
5275 ( p_error_msg_id => SQLCODE
5276 , p_error_description => SQLERRM
5277 , p_error_proc_name => G_PKG_NAME||'.Translate_Measure_by_lang'
5278 , p_error_table => l_error_tbl
5279 , x_error_table => x_error_tbl
5280 );
5281
5282
5283 END Translate_Measure_by_lang;
5284
5285 -------------------- Get customized Enabled ----------------------
5286 FUNCTION get_customized_enabled( p_indicator_id IN NUMBER) RETURN VARCHAR2 AS
5287 CURSOR c_cust IS SELECT
5288 ENABLED,
5289 USER_ID,
5290 APPLICATION_ID,
5291 RESPONSIBILITY_ID,
5292 ORG_ID,
5293 SITE_ID
5294 FROM BIS_IND_CUSTOMIZATIONS
5295 WHERE INDICATOR_ID = p_indicator_id
5296 AND (user_id = fnd_global.user_id
5297 OR responsibility_id = fnd_global.RESP_ID
5298 OR application_id = fnd_global.RESP_APPL_ID
5299 OR org_id = fnd_global.ORG_ID
5300 OR site_id = 0) ;
5301 l_bis_custom_enabled_usr BIS_INDICATORS.enabled%TYPE;
5302 l_bis_custom_enabled_resp BIS_INDICATORS.enabled%TYPE;
5303 l_bis_custom_enabled_appl BIS_INDICATORS.enabled%TYPE;
5304 l_bis_custom_enabled_org BIS_INDICATORS.enabled%TYPE;
5305 l_bis_custom_enabled_site BIS_INDICATORS.enabled%TYPE;
5306 BEGIN
5307 IF (c_cust%ISOPEN) THEN
5308 CLOSE c_cust;
5309 END IF;
5310
5311 FOR cr IN c_cust LOOP
5312 IF (cr.user_id IS NOT NULL) THEN
5313 l_bis_custom_enabled_usr := cr.enabled;
5314 ELSIF (cr.responsibility_id IS NOT NULL) THEN
5315 l_bis_custom_enabled_resp := cr.enabled;
5316 ELSIF (cr.application_id IS NOT NULL) THEN
5317 l_bis_custom_enabled_appl := cr.enabled;
5318 ELSIF (cr.org_id IS NOT NULL) THEN
5319 l_bis_custom_enabled_org := cr.enabled;
5320 ELSIF (cr.site_id IS NOT NULL) THEN
5321 l_bis_custom_enabled_site := cr.enabled;
5322 END IF;
5323 END LOOP;
5324
5325 IF ( l_bis_custom_enabled_usr IS NOT NULL) THEN
5326 RETURN l_bis_custom_enabled_usr ;
5327 ELSIF (l_bis_custom_enabled_resp IS NOT NULL) THEN
5328 RETURN l_bis_custom_enabled_resp ;
5329 ELSIF (l_bis_custom_enabled_appl IS NOT NULL) THEN
5330 RETURN l_bis_custom_enabled_appl ;
5331 ELSIF (l_bis_custom_enabled_org IS NOT NULL) THEN
5332 RETURN l_bis_custom_enabled_org ;
5333 ELSIF (l_bis_custom_enabled_site IS NOT NULL) THEN
5334 RETURN l_bis_custom_enabled_site ;
5335 END IF;
5336
5337 RETURN NULL;
5338
5339 EXCEPTION
5340 WHEN OTHERS THEN
5341 IF (c_cust%ISOPEN) THEN
5342 CLOSE c_cust;
5343 END IF;
5344 RETURN NULL;
5345 END get_customized_enabled;
5346 ---
5347
5348
5349
5350 PROCEDURE Load_Measure_Extension
5351 ( p_api_version IN NUMBER
5352 , p_commit IN VARCHAR2 := FND_API.G_FALSE
5353 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
5354 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
5355 , x_return_status OUT NOCOPY VARCHAR2
5356 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
5357 )
5358 IS
5359 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
5360 l_Return_Status VARCHAR2(2000);
5361 l_Msg_Count NUMBER;
5362 l_Msg_Data VARCHAR2(2000);
5363 l_Measure_Extension_Rec BIS_OBJECT_EXTENSIONS_PUB.Measure_Extension_Type;
5364 l_custom_mode VARCHAR2(6);
5365 BEGIN
5366
5367 -- rpenneru 12/20/2004 - Populate Measure Extension table
5368 -- Load Measure extension should be called only when LDT is uploaded,
5369 -- It should not be called for the UI flow
5370 -- Check if the Functional Area Short name, If it is null
5371 -- Measure will be assigned under Customer Defined Functional Area.
5372 l_Measure_Extension_Rec.Func_Area_Short_Name := p_Measure_Rec.Func_Area_Short_Name;
5373 IF (p_Measure_Rec.Func_Area_Short_Name IS NULL ) THEN
5374 -- bug#4447273 Don't call the load_measrue_extension
5375 -- when the p_Measure_Rec.Func_Area_Short_Name is NULL
5376 --l_Measure_Extension_Rec.Func_Area_Short_Name := 'BIS_UNN'; //Commented..
5377 x_return_status := FND_API.G_RET_STS_SUCCESS;
5378 RETURN;
5379 END IF;
5380 --l_Measure_Extension_Rec.Functional_Area_Id := BIS_OBJECT_EXTENSIONS_PVT.Get_FA_Id_By_Short_Name(p_Measure_Rec.Func_Area_Short_Name);
5381
5382 l_Measure_Extension_Rec.Measure_Short_Name := p_Measure_Rec.Measure_Short_Name;
5383 -- rpenneru bug#4073262, As part of revert back changes bug#4153331.
5384 -- BIS_MEASURES_EXTENSION table will have the TL Tables. In this case Name and Description values
5385 -- should not be updateded to the measure extension table, if measure is uploaded from BISPMFLD.lct
5386 -- The value of the Name and Description from BISPMFLD.lct will be BIS_COMMON_UTILS.G_DEF_CHAR
5387 l_Measure_Extension_Rec.Name := BIS_COMMON_UTILS.G_DEF_CHAR;
5388 l_Measure_Extension_Rec.Description := BIS_COMMON_UTILS.G_DEF_CHAR;
5389
5390 l_Measure_Extension_Rec.Created_By := p_Measure_Rec.Created_By;
5391 l_Measure_Extension_Rec.Creation_Date := p_Measure_Rec.Creation_Date;
5392 l_Measure_Extension_Rec.Last_Updated_By := p_Measure_Rec.Last_Updated_By;
5393 l_Measure_Extension_Rec.Last_Update_Date := p_Measure_Rec.Last_Update_Date;
5394 l_Measure_Extension_Rec.Last_Update_Login := p_Measure_Rec.Last_Update_Login;
5395
5396 -- From the UI Custom Mode is FORCE but when uploaded through LCT it is NULL
5397 IF ( p_owner IS NOT NULL AND p_owner <> BIS_UTILITIES_PUB.G_CUSTOM_OWNER ) THEN
5398 l_custom_mode := NULL;
5399 ELSE
5400 l_custom_mode := 'FORCE';
5401 END IF;
5402 BIS_OBJECT_EXTENSIONS_PUB.Load_Measure_Extension(
5403 p_Api_Version => p_api_version
5404 ,p_Commit => p_commit
5405 ,p_Meas_Extn_Rec => l_Measure_Extension_Rec
5406 ,p_Custom_mode => l_custom_mode
5407 ,x_Return_Status => l_return_status
5408 ,x_Msg_Count => l_Msg_Count
5409 ,x_Msg_Data => l_Msg_Data
5410 );
5411
5412 IF (l_return_status IS NOT NULL AND l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5413 l_error_tbl := x_error_tbl;
5414 BIS_UTILITIES_PVT.Add_Error_Message
5415 ( p_error_msg_id => NULL
5416 , p_error_description => l_Msg_Data
5417 , p_error_proc_name => G_PKG_NAME||'.Create_Measure'
5418 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
5419 , p_error_table => l_error_tbl
5420 , x_error_table => x_error_tbl
5421 );
5422 RAISE FND_API.G_EXC_ERROR;
5423 END IF;
5424
5425 x_return_status := FND_API.G_RET_STS_SUCCESS;
5426
5427 EXCEPTION
5428 WHEN NO_DATA_FOUND THEN
5429 x_return_status := FND_API.G_RET_STS_ERROR ;
5430 when FND_API.G_EXC_ERROR then
5431 x_return_status := FND_API.G_RET_STS_ERROR ;
5432 when FND_API.G_EXC_UNEXPECTED_ERROR then
5433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5434 when others then
5435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5436 --added last two params
5437 l_error_tbl := x_error_tbl;
5438 BIS_UTILITIES_PVT.Add_Error_Message
5439 ( p_error_msg_id => SQLCODE
5440 , p_error_description => SQLERRM
5441 , p_error_proc_name => G_PKG_NAME||'.Translate_Measure_by_lang'
5442 , p_error_table => l_error_tbl
5443 , x_error_table => x_error_tbl
5444 );
5445 END Load_Measure_Extension;
5446
5447
5448 PROCEDURE Update_Measure_Obsolete_Flag(
5449 p_commit IN VARCHAR2 := FND_API.G_FALSE,
5450 p_measure_short_name IN VARCHAR2,
5451 p_obsolete IN VARCHAR2,
5452 x_return_status OUT nocopy VARCHAR2,
5453 x_Msg_Count OUT NOCOPY NUMBER,
5454 x_msg_data OUT nocopy VARCHAR2
5455 ) IS
5456 BEGIN
5457 SAVEPOINT MeasureObsoleteUpdate;
5458 IF (p_measure_short_name IS NULL OR p_measure_short_name = '') THEN
5459 FND_MESSAGE.SET_NAME('BIS','BIS_PMF_INVALID_MEASURE_VALUE');
5460 FND_MSG_PUB.ADD;
5461 RAISE FND_API.G_EXC_ERROR;
5462 END IF;
5463
5464 IF (p_obsolete IS NULL OR (p_obsolete <> FND_API.G_TRUE AND p_obsolete <> FND_API.G_FALSE)) THEN
5465 FND_MESSAGE.SET_NAME('BIS','BIS_PMF_INVALID_OBSOLETE_FLAG');
5466 FND_MSG_PUB.ADD;
5467 RAISE FND_API.G_EXC_ERROR;
5468 END IF;
5469
5470 UPDATE bis_indicators
5471 SET
5472 obsolete = p_obsolete ,
5473 last_update_date = SYSDATE,
5474 last_updated_by = FND_GLOBAL.USER_ID ,
5475 last_update_login = FND_GLOBAL.USER_ID
5476 WHERE short_name = p_measure_short_name;
5477
5478 IF(p_Commit = FND_API.G_TRUE) THEN
5479 commit;
5480 END IF;
5481
5482 x_return_status := FND_API.G_RET_STS_SUCCESS;
5483 RETURN;
5484 EXCEPTION
5485 WHEN FND_API.G_EXC_ERROR THEN
5486 IF (x_msg_data IS NULL) THEN
5487 FND_MSG_PUB.Count_And_Get
5488 ( p_encoded => FND_API.G_FALSE
5489 , p_count => x_msg_count
5490 , p_data => x_msg_data
5491 );
5492 END IF;
5493 x_Return_Status := FND_API.G_RET_STS_ERROR;
5494 WHEN OTHERS THEN
5495 ROLLBACK TO MeasureObsoleteUpdate;
5496 IF (x_msg_data IS NOT NULL) THEN
5497 x_msg_data := x_msg_data||' -> BIS_FORM_FUNCTIONS_PUB.Update_Measure_Obsolete_Flag ';
5498 ELSE
5499 x_msg_data := SQLERRM||' at BIS_FORM_FUNCTIONS_PUB.Update_Measure_Obsolete_Flag ';
5500 END IF;
5501 END Update_Measure_Obsolete_Flag;
5502
5503
5504 END BIS_MEASURE_PVT;
5505