1 PACKAGE BIS_MEASURE_PUB AS
2 /* $Header: BISPMEAS.pls 120.0 2005/06/01 18:24:19 appldev noship $ */
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 | BISPMEAS.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-NOV-2001 Fix for 1850860 MAHRAO
20 REM | 26-JUL-2002 rchandra Fixed for enh 2440739 |
21 REM | 23-APR-2003 mdamle PMD - Measure Definer Support |
22 REM | 24-JUN-2003 rchandra leap frog PMD Changes to verssion 115.25 |
23 REM | 24-JUN-2003 rchandra leap frog to verssion 115.26 which has the |
24 REM | attribute for dataset_id in Measure_Rec_Type |
25 REM | for bug 3004651 |
26 REM | 26-JUN-03 RCHANDRA do away with hard coded length for name and |
27 REM | description for bug 2910316 |
28 REM | for dimension and dimension levels |
29 REM | 25-JUL-03 mahrao As following procedures are referred from BSCPBMSB.pls,|
30 REM | they are removed from BISPMEASB.pls. |
31 REM | 25-SEP-03 mdamle Bug#3160325 - Sync up measures for all installed |
32 REM | languages |
33 REM | 29-SEP-2003 adrao Bug#3160325 - Sync up measures for all installed |
34 REM | source languages |
35 REM | 05-NOV-2003 smargand Adding a new column enabled to the record type |
36 REM | 08-APR-2004 ankgoel Modified for bug#3557236 |
37 REM | 27-JUL-2004 sawu Modified create/update measure api to take a |
38 REM | default p_owner parameter |
39 REM | 01-SEP-2004 sawu Added region, source/compare column app id to |
40 REM | Measure_Rec_Type |
41 REM | 29-SEP-2004 ankgoel Added WHO columns in Rec for Bug#3891748 |
42 REM | 27-Dec-2004 rpenneru Added Func_Area_Short_Name field to Measure_Rec |
43 REM | for enh#4080204 |
44 REM | 29-Jan-2005 vtulasi Enh#4102897- Increasing buffer size for |
45 REM | function_name related variables |
46 REM | 21-FEB-2005 ankagarw modified measure name and description |
47 REM | column length for enh. 3862703 |
48 REM | 22-APR-2005 akoduri Enhancement#3865711 -- Obsolete Seeded Objects |
49 REM | 03-MAY-2005 akoduri Enh #4268374 -- Weighted Average Measures |
50 REM +=======================================================================+
51 */
52 --
53 -- Data Types: Records
54 --
55 TYPE Measure_Instance_type IS RECORD
56 ( Measure_ID NUMBER
57 , Measure_Short_Name VARCHAR2(32000)
58 , Measure_Name VARCHAR2(32000)
59 , Target_Level_ID NUMBER
60 , Target_Level_Short_Name VARCHAR2(32000)
61 , Target_Level_Name VARCHAR2(32000)
62 , Plan_ID NUMBER
63 , Plan_Short_Name VARCHAR2(32000)
64 , Plan_Name VARCHAR2(32000)
65 , Actual_ID NUMBER
66 , Actual NUMBER
67 , Target_ID NUMBER
68 , Target NUMBER
69 , Range1_low NUMBER
70 , Range1_high NUMBER
71 , Range2_low NUMBER
72 , Range2_high NUMBER
73 , Range3_low NUMBER
74 , Range3_high NUMBER
75 , Range1_Owner_ID NUMBER
76 , Range1_Owner_Short_Name VARCHAR2(32000)
77 , Range1_Owner_Name VARCHAR2(32000)
78 , Range2_Owner_ID NUMBER
79 , Range2_Owner_Short_Name VARCHAR2(32000)
80 , Range2_Owner_Name VARCHAR2(32000)
81 , Range3_Owner_ID NUMBER
82 , Range3_Owner_Short_Name VARCHAR2(32000)
83 , Range3_Owner_Name VARCHAR2(32000)
84 );
85
86 TYPE Measure_Rec_Type IS RECORD
87 ( Measure_ID NUMBER
88 , Measure_Short_Name VARCHAR2(30)
89 , Measure_Name bis_indicators_tl.name%TYPE
90 , Description bis_indicators_tl.DESCRIPTION%TYPE
91 ------fix for bug#3859267 -------------
92 , Region_App_Id Ak_Region_Items.REGION_APPLICATION_ID%Type
93 , Source_Column_App_Id Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type
94 , Compare_Column_App_Id Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type
95 ------fix for 1850860 starts here------
96 , Actual_Data_Source_Type VARCHAR2(30)
97 , Actual_Data_Source VARCHAR2(240)
98 , Function_Name FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE
99 , Comparison_Source VARCHAR2(240)
100 , Increase_In_Measure VARCHAR2(1)
101 ------fix for 1850860 ends here--------
102 , Enable_Link VARCHAR2(1) -- 2440739
103 ------enhancement #3031053 start --------
104 , Enabled VARCHAR2(1) := FND_API.G_TRUE -- 2440739
105 ------enhancement #3031053 end --------
106 ------Enhancement 3865711--------------
107 , Obsolete VARCHAR2(1) := FND_API.G_FALSE
108 ------Enhancement 4268374--------------
109 , Measure_Type VARCHAR2(30)
110 , Dimension1_ID NUMBER
111 , Dimension1_Short_Name VARCHAR2(30)
112 , Dimension1_Name bis_dimensions_tl.name%TYPE
113 , Dimension2_ID NUMBER
114 , Dimension2_Short_Name VARCHAR2(30)
115 , Dimension2_Name bis_dimensions_tl.name%TYPE
116 , Dimension3_ID NUMBER
117 , Dimension3_Short_Name VARCHAR2(30)
118 , Dimension3_Name bis_dimensions_tl.name%TYPE
119 , Dimension4_ID NUMBER
120 , Dimension4_Short_Name VARCHAR2(30)
121 , Dimension4_Name bis_dimensions_tl.name%TYPE
122 , Dimension5_ID NUMBER
123 , Dimension5_Short_Name VARCHAR2(30)
124 , Dimension5_Name bis_dimensions_tl.name%TYPE
125 , Dimension6_ID NUMBER
126 , Dimension6_Short_Name VARCHAR2(30)
127 , Dimension6_Name bis_dimensions_tl.name%TYPE
128 , Dimension7_ID NUMBER
129 , Dimension7_Short_Name VARCHAR2(30)
130 , Dimension7_Name bis_dimensions_tl.name%TYPE
131 , Unit_Of_Measure_Class VARCHAR2(10)
132 , Application_Id NUMBER := -1 --2465354
133 -- mdamle 04/23/2003 - PMD - Measure Definer - link to BSC tables
134 , dataset_id NUMBER
135 -- ankgoel: bug#3557236 - Required to be FALSE for ldt file upload
136 , is_validate VARCHAR2(1) := FND_API.G_TRUE
137 -- rpenneru bug#4073262 -
138 , Func_Area_Short_Name VARCHAR2(30)
139 -- ankgoel: bug#3891748
140 , Created_By BIS_INDICATORS.CREATED_BY%TYPE
141 , Creation_Date BIS_INDICATORS.CREATION_DATE%TYPE
142 , Last_Updated_By BIS_INDICATORS.LAST_UPDATED_BY%TYPE
143 , Last_Update_Date BIS_INDICATORS.LAST_UPDATE_DATE%TYPE
144 , Last_Update_Login BIS_INDICATORS.LAST_UPDATE_LOGIN%TYPE
145 );
146 --
147 TYPE UOM_Class_Rec_Type IS RECORD (
148 UOM_Class VARCHAR2(10));
149 --
150 --
151 TYPE Rule_Set_Rec_Type is RECORD (
152 Rule VARCHAR2(2000) );
153 --
154 --
155 -- Data Types: Tables
156 --
157 TYPE Measure_Tbl_Type IS TABLE of Measure_Rec_Type
158 INDEX BY BINARY_INTEGER;
159 --
160 TYPE UOM_Class_Tbl_Type IS TABLE of UOM_Class_Rec_Type
161 INDEX BY BINARY_INTEGER;
162 --
163 TYPE Rule_Set_Tbl_Type is TABLE of Rule_Set_Rec_Type
164 INDEX BY BINARY_INTEGER;
165 --
166 --
167 -- Global Missing Composite Types
168 --
169 G_MISS_MEASURE_REC Measure_Rec_Type;
170 G_MISS_UOM_CLASS_REC UOM_Class_Rec_Type;
171 --
172 G_MISS_MEASURE_TBL Measure_Tbl_Type;
173 G_MISS_UOM_CLASS_Tbl UOM_Class_Tbl_Type;
174 --
175 --
176 -- PROCEDUREs
177 --
178 -- creates one Measure, with the dimensions sequenced in the order
179 -- they are passed in
180 PROCEDURE Create_Measure
181 ( p_api_version IN NUMBER
182 , p_commit IN VARCHAR2 := FND_API.G_FALSE
183 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
184 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
185 , x_return_status OUT NOCOPY VARCHAR2
186 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
187 );
188 --
189 --
190 -- Gets All Performance Measures
191 -- If information about the dimensions are not required, set all_info to
192 -- FALSE
193 PROCEDURE Retrieve_Measures
194 ( p_api_version IN NUMBER
195 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
196 , x_Measure_tbl OUT NOCOPY BIS_MEASURE_PUB.Measure_Tbl_Type
197 , x_return_status OUT NOCOPY VARCHAR2
198 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
199 );
200 --
201 --
202 -- Gets Information for One Performance Measure
203 -- If information about the dimension are not required, set all_info to FALSE.
204 PROCEDURE Retrieve_Measure
205 ( p_api_version IN NUMBER
206 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
207 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
208 , x_Measure_Rec IN OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
209 , x_return_status OUT NOCOPY VARCHAR2
210 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
211 );
212 --
213 --
214 -- PLEASE VERIFY COMMENT BELOW
215 -- Update_Measures one Measure if
216 -- 1) no Measure levels or targets exist
217 -- 2) no users have selected to see actuals for the Measure
218 PROCEDURE Update_Measure
219 ( p_api_version IN NUMBER
220 , p_commit IN VARCHAR2 := FND_API.G_FALSE
221 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
222 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
223 , x_return_status OUT NOCOPY VARCHAR2
224 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
225 );
226 --
227 --
228 -- PLEASE VERIFY COMMENT BELOW
229 -- deletes one Measure if
230 -- 1) no Measure levels, targets exist and
231 -- 2) the Measure access has not been granted to a resonsibility
232 -- 3) no users have selected to see actuals for the Measure
233 PROCEDURE Delete_Measure
234 ( p_api_version IN NUMBER
235 , p_commit IN VARCHAR2 := FND_API.G_FALSE
236 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
237 , x_return_status OUT NOCOPY VARCHAR2
238 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
239 );
240 --
241 -- Validates measure
242 PROCEDURE Validate_Measure
243 ( p_api_version IN NUMBER
244 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
245 , x_return_status OUT NOCOPY VARCHAR2
246 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
247 );
248 --
249 Procedure Retrieve_Measure_Dimensions
250 ( p_api_version IN NUMBER
251 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
252 , x_dimension_Tbl OUT NOCOPY BIS_DIMENSION_PUB.Dimension_Tbl_Type
253 , x_return_status OUT NOCOPY VARCHAR2
254 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
255 );
256 --
257 --
258 Procedure Translate_Measure
259 ( p_api_version IN NUMBER
260 , p_commit IN VARCHAR2 := FND_API.G_FALSE
261 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
262 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
263 , p_OWNER IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
264 , x_return_status OUT NOCOPY VARCHAR2
265 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
266 );
267 --
268 Procedure Load_Measure
269 ( p_api_version IN NUMBER
270 , p_commit IN VARCHAR2 := FND_API.G_FALSE
271 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
272 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
273 , p_OWNER IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
274 , x_return_status OUT NOCOPY VARCHAR2
275 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
276 );
277
278 --
279 --Overload Load_Measure so that old data model ldts can be uploaded using
280 --The latest lct file. The lct file can call this overloaded procedure
281 --by passing in Org and Time dimension short_names also
282 Procedure Load_Measure
283 ( p_api_version IN NUMBER
284 , p_commit IN VARCHAR2 := FND_API.G_FALSE
285 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
286 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
287 , p_OWNER IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
288 , p_Org_Dimension_Short_Name IN VARCHAR2
289 , p_Time_Dimension_Short_Name IN VARCHAR2
290 , x_return_status OUT NOCOPY VARCHAR2
291 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
292 );
293 --
294
295 -- Given a performance measure short name update the
296 -- bis_indicators, bis_indicators_tl and bis_indicator_dimensions
297 -- for last_updated_by , created_by as 1
298 PROCEDURE updt_pm_owner(p_pm_short_name IN VARCHAR2
299 ,x_return_status OUT NOCOPY VARCHAR2);
300
301
302 -- mdamle 09/25/2003 - Sync up measures for all installed languages
303 Procedure Translate_Measure_By_lang
304 ( p_api_version IN NUMBER
305 , p_commit IN VARCHAR2 := FND_API.G_FALSE
306 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
307 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
308 , p_lang IN VARCHAR2
309 , p_source_lang IN VARCHAR2
310 , x_return_status OUT NOCOPY VARCHAR2
311 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
312 );
313
314 --=============================================================================
315 --added new function to determine whether the given indicator is customized
316
317 FUNCTION GET_CUSTOMIZED_ENABLED
318 ( p_indicator_id IN NUMBER
319 )
320 RETURN VARCHAR2;
321
322
323
324
325 END BIS_MEASURE_PUB;