1 PACKAGE BSC_BIS_MEASURE_PUB AUTHID CURRENT_USER AS
2 /* $Header: BSCPBMSS.pls 120.7 2006/01/24 01:20:40 ankgoel noship $ */
3 --- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
4 --- All rights reserved.
5 ---
6 ---==========================================================================
7 --- FILENAME
8 ---
9 --- BSCPBMSS.pls
10 ---
11 --- DESCRIPTION
12 --- Package Specification File for Measure transactions
13 ---
14 --- NOTES
15 ---
16 --- HISTORY
17 ---
18 --- 23-Apr-2003 mdamle Created
19 --- 16-JUN-2003 adrao Added time_stamp params for Update/Delete APIs
20 --- for Granular Locking
21 -- 25-AUG-2003 mahrao Added procedure Ret_Dataset_Fr_Meas_Shrt_Name and
22 -- Order_Dimensions_For_Ldt
23 -- 03-Sep-03 mdamle Fixed Bug #3123734, 3123558 - Get measure col
24 -- 28-NOV-03 adrao Added constants c_QTD_CODE and c_XTD_CODE for warning messages
25 -- for Bug#3238554
26 -- 08-APR-04 ankgoel Modified for bug#3557236
27 -- 13-APR-04 ppandey Bug# 3530050- Generating unique measure col if not unique
28 -- 27-JUL-04 sawu Modified create/update measure api to take p_owner
29 -- 09-AUG-2004 ashankar Bug#3809014 added the parameter p_ui_flag in Create_measure
30 -- and Update_measure procedures.
31 -- Added c_UI_FLAG := 'N'
32 -- 09-AUG-04 sawu Added create_measure wrapper to handle default internal name
33 -- 26-AUG-04 sawu Bug#3813603: added Is_Unique_Measure_Display_Name()
34 -- 01-SEP-04 sawu Bug#3859267: added region, source/compare column app
35 -- id to create/update api
36 -- 09-SEP-04 ankgoel Bug#3874911: Made get_Measure_Name public
37 -- 18-OCT-04 adrao Modified Create_Measure, Update_Measure signatures by added
38 -- p_measure_col_help to the APIs for POSCO Bug#3817894
39 -- 17-Nov-04 sawu Bug#4015015: added api Is_Numeric_Column api
40 -- 17-Dec-04 sawu Bug#4045287: added Upload_Test, added p_custom_mode
41 -- to Load_Measure() and Translate_Measure(). Overloaded
42 -- Create_Measure() and Update_Measure().
43 -- 27-Dec-04 rpenneru Enh#4080204: Added Func_Area_Short name parameters to
44 -- Create_Measue() and Update_Measure().
45 -- 09-FEB-04 skchoudh Enh#4141738 Added Functiona Area Combobox to MD
46 -- 21-Feb-05 rpenneru Enh#4059160, Add FA as property to Custom KPIs
47 -- 22-MAY-05 akoduri Enhancement#3865711 -- Obsolete Seeded Objects --
48 -- 03-MAY-05 akoduri Enh #4268374 -- Weighted Average Measures --
49 -- 23-May-05 visuri Bug#3994115 Added Get_Meas_With_Src_Col() and Get_Sing_Par_Meas_DS()
50 -- 17-JUL-05 sawu Bug#4482736: Added Get_Primary_Data_Source
51 -- 20-Sep-05 akoduri Bug#4613172: CDS type measures should not get populated into
52 -- bsc_db_measure_cols_tl
53 -- 22-Sep-05 ashankar Bug#4605142:Modified the API Get_Incr_Truigger
54 -- 17-Nov-05 adrao added API Is_Formula_Type() Bug#4617140
55 -- 12-JAN-06 ppandey Bug #4938364 - Color Warning for BIS Measure (AG)
56 -- 24-JAN-06 ankgoel Bug#4954663 Show Info text for AG to PL/SQL or VB conversion
57 ---===========================================================================
58
59 c_PMD CONSTANT VARCHAR2(4) := 'PMD_';
60 c_BSC CONSTANT VARCHAR2(3) := 'BSC';
61 c_PMF CONSTANT VARCHAR2(3) := 'PMF';
62 c_CDS CONSTANT VARCHAR2(3) := 'CDS';
63 c_LEVEL CONSTANT VARCHAR2(7) := 'DATASET';
64 c_SEPARATOR CONSTANT VARCHAR2(1) := ';';
65 c_INTERNAL_COLUMN_NAME CONSTANT varchar2(5) := 'BSCIC';
66 c_FORMULA_SOURCE CONSTANT varchar2(15) := 'pFormulaSource';
67 c_AVGL CONSTANT varchar2(6) := 'pAvgL';
68 c_SUM CONSTANT varchar2(3) := 'SUM';
69 c_AVGL_CODE CONSTANT varchar2(3) := 'AVL';
70 c_YTD_CODE constant number := 2; -- Added for YTD
71 c_QTD_CODE constant number := 6; -- Added for QTD
72 c_XTD_CODE constant number := 12; -- Added for XTD
73 c_MAX_MSG_LENGTH constant number := 4000; -- Added for YTD Disabling
74 c_UI_FLAG CONSTANT VARCHAR(2) :='N';
75
76 --Validates the measure/dataset name
77 PROCEDURE get_Measure_Name
78 ( p_dataset_id IN NUMBER -- if NULL it means Create otherwise update
79 ,p_ui_flag IN VARCHAR2
80 ,p_dataset_source IN VARCHAR2 -- BSC or PMF
81 ,p_dataset_name IN VARCHAR2 -- passed measure name
82 ,x_measure_name OUT NOCOPY VARCHAR2 -- trimmed output measure name
83 );
84
85 --Wrapper of Create_Measure that takes in p_default_short_name
86 -- 18-OCT-2004 ADRAO, changed signature added p_measure_col_help, Bug#3817894
87 procedure Create_Measure(
88 p_commit IN VARCHAR2 := FND_API.G_FALSE
89 ,x_dataset_id OUT NOCOPY NUMBER
90 ,p_dataset_source IN VARCHAR2
91 ,p_dataset_name IN VARCHAR2
92 ,p_dataset_help IN VARCHAR2 := NULL
93 ,p_dataset_measure_id1 IN NUMBER := NULL
94 ,p_dataset_operation IN VARCHAR2 := NULL
95 ,p_dataset_measure_id2 IN NUMBER := NULL
96 ,p_dataset_format_id IN NUMBER := NULL
97 ,p_dataset_color_method IN NUMBER := NULL
98 ,p_dataset_autoscale_flag IN NUMBER := NULL
99 ,p_dataset_projection_flag IN NUMBER := NULL
100 ,p_measure_short_name IN VARCHAR2
101 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type := -1
102 ,p_source_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
103 ,p_compare_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
104 ,p_measure_act_data_src_type IN VARCHAR2 := NULL
105 ,p_measure_act_data_src IN VARCHAR2 := NULL
106 ,p_measure_comparison_source IN VARCHAR2 := NULL
107 ,p_measure_operation IN VARCHAR2 := c_SUM
108 ,p_measure_uom_class IN VARCHAR2 := NULL
109 ,p_measure_increase_in_measure IN VARCHAR2 := NULL
110 ,p_measure_random_style IN NUMBER := NULL
111 ,p_measure_min_act_value IN NUMBER := NULL
112 ,p_measure_max_act_value IN NUMBER := NULL
113 ,p_measure_min_bud_value IN NUMBER := NULL
114 ,p_measure_max_bud_value IN NUMBER := NULL
115 ,p_measure_app_id IN NUMBER := NULL
116 ,p_measure_col IN VARCHAR2 := NULL
117 ,p_measure_col_help IN VARCHAR2 := NULL
118 ,p_measure_group_id IN NUMBER := NULL
119 ,p_measure_projection_id IN NUMBER := NULL
120 ,p_measure_type IN NUMBER := NULL
121 ,p_measure_apply_rollup IN VARCHAR2 := NULL
122 ,p_measure_function_name IN VARCHAR2 := NULL
123 ,p_measure_enable_link IN VARCHAR2 := NULL
124 ,p_measure_obsolete IN VARCHAR2 := FND_API.G_FALSE
125 ,p_type IN VARCHAR2 := NULL
126 ,p_measure_is_validate IN VARCHAR2 := FND_API.G_TRUE -- ankgoel: bug#3557236
127 ,p_dimension1_id IN NUMBER
128 ,p_dimension2_id IN NUMBER
129 ,p_dimension3_id IN NUMBER
130 ,p_dimension4_id IN NUMBER
131 ,p_dimension5_id IN NUMBER
132 ,p_dimension6_id IN NUMBER
133 ,p_dimension7_id IN NUMBER
134 ,p_y_axis_title IN VARCHAR2 := NULL
135 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
136 ,p_ui_flag IN VARCHAR2 := c_UI_FLAG
137 ,p_is_default_short_name IN VARCHAR2
138 ,p_func_area_short_name IN VARCHAR2 := NULL
139 ,x_return_status OUT NOCOPY VARCHAR2
140 ,x_msg_count OUT NOCOPY NUMBER
141 ,x_msg_data OUT NOCOPY VARCHAR2
142 );
143
144 -- 18-OCT-2004 ADRAO, changed signature added p_measure_col_help, Bug#3817894
145 procedure Create_Measure(
146 p_commit IN VARCHAR2 := FND_API.G_FALSE
147 ,x_dataset_id OUT NOCOPY NUMBER
148 ,p_dataset_source IN VARCHAR2
149 ,p_dataset_name IN VARCHAR2
150 ,p_dataset_help IN VARCHAR2 := NULL
151 ,p_dataset_measure_id1 IN NUMBER := NULL
152 ,p_dataset_operation IN VARCHAR2 := NULL
153 ,p_dataset_measure_id2 IN NUMBER := NULL
154 ,p_dataset_format_id IN NUMBER := NULL
155 ,p_dataset_color_method IN NUMBER := NULL
156 ,p_dataset_autoscale_flag IN NUMBER := NULL
157 ,p_dataset_projection_flag IN NUMBER := NULL
158 ,p_measure_short_name IN VARCHAR2
159 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type := -1
160 ,p_source_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
161 ,p_compare_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
162 ,p_measure_act_data_src_type IN VARCHAR2 := NULL
163 ,p_measure_act_data_src IN VARCHAR2 := NULL
164 ,p_measure_comparison_source IN VARCHAR2 := NULL
165 ,p_measure_operation IN VARCHAR2 := c_SUM
166 ,p_measure_uom_class IN VARCHAR2 := NULL
167 ,p_measure_increase_in_measure IN VARCHAR2 := NULL
168 ,p_measure_random_style IN NUMBER := NULL
169 ,p_measure_min_act_value IN NUMBER := NULL
170 ,p_measure_max_act_value IN NUMBER := NULL
171 ,p_measure_min_bud_value IN NUMBER := NULL
172 ,p_measure_max_bud_value IN NUMBER := NULL
173 ,p_measure_app_id IN NUMBER := NULL
174 ,p_measure_col IN VARCHAR2 := NULL
175 ,p_measure_col_help IN VARCHAR2 := NULL
176 ,p_measure_group_id IN NUMBER := NULL
177 ,p_measure_projection_id IN NUMBER := NULL
178 ,p_measure_type IN NUMBER := NULL
179 ,p_measure_apply_rollup IN VARCHAR2 := NULL
180 ,p_measure_function_name IN VARCHAR2 := NULL
181 ,p_measure_enable_link IN VARCHAR2 := NULL
182 ,p_measure_obsolete IN VARCHAR2 := FND_API.G_FALSE
183 ,p_type IN VARCHAR2 := NULL
184 ,p_measure_is_validate IN VARCHAR2 := FND_API.G_TRUE -- ankgoel: bug#3557236
185 ,p_dimension1_id IN NUMBER
186 ,p_dimension2_id IN NUMBER
187 ,p_dimension3_id IN NUMBER
188 ,p_dimension4_id IN NUMBER
189 ,p_dimension5_id IN NUMBER
190 ,p_dimension6_id IN NUMBER
191 ,p_dimension7_id IN NUMBER
192 ,p_y_axis_title IN VARCHAR2 := NULL
193 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
194 ,p_ui_flag IN VARCHAR2 := c_UI_FLAG
195 ,p_func_area_short_name IN VARCHAR2 := NULL
196 ,x_return_status OUT NOCOPY VARCHAR2
197 ,x_msg_count OUT NOCOPY NUMBER
198 ,x_msg_data OUT NOCOPY VARCHAR2
199 );
200
201 --Bug#4045278: Wrapper for Create_Measure that takes in last_update_date
202 procedure Create_Measure(
203 p_commit IN VARCHAR2 := FND_API.G_FALSE
204 ,x_dataset_id OUT NOCOPY NUMBER
205 ,p_dataset_source IN VARCHAR2
206 ,p_dataset_name IN VARCHAR2
207 ,p_dataset_help IN VARCHAR2 := NULL
208 ,p_dataset_measure_id1 IN NUMBER := NULL
209 ,p_dataset_operation IN VARCHAR2 := NULL
210 ,p_dataset_measure_id2 IN NUMBER := NULL
211 ,p_dataset_format_id IN NUMBER := NULL
212 ,p_dataset_color_method IN NUMBER := NULL
213 ,p_dataset_autoscale_flag IN NUMBER := NULL
214 ,p_dataset_projection_flag IN NUMBER := NULL
215 ,p_measure_short_name IN VARCHAR2
216 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type := -1
217 ,p_source_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
218 ,p_compare_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
219 ,p_measure_act_data_src_type IN VARCHAR2 := NULL
220 ,p_measure_act_data_src IN VARCHAR2 := NULL
221 ,p_measure_comparison_source IN VARCHAR2 := NULL
222 ,p_measure_operation IN VARCHAR2 := c_SUM
223 ,p_measure_uom_class IN VARCHAR2 := NULL
224 ,p_measure_increase_in_measure IN VARCHAR2 := NULL
225 ,p_measure_random_style IN NUMBER := NULL
226 ,p_measure_min_act_value IN NUMBER := NULL
227 ,p_measure_max_act_value IN NUMBER := NULL
228 ,p_measure_min_bud_value IN NUMBER := NULL
229 ,p_measure_max_bud_value IN NUMBER := NULL
230 ,p_measure_app_id IN NUMBER := NULL
231 ,p_measure_col IN VARCHAR2 := NULL
232 ,p_measure_col_help IN VARCHAR2 := NULL
233 ,p_measure_group_id IN NUMBER := NULL
234 ,p_measure_projection_id IN NUMBER := NULL
235 ,p_measure_type IN NUMBER := NULL
236 ,p_measure_apply_rollup IN VARCHAR2 := NULL
237 ,p_measure_function_name IN VARCHAR2 := NULL
238 ,p_measure_enable_link IN VARCHAR2 := NULL
239 ,p_measure_obsolete IN VARCHAR2 := FND_API.G_FALSE
240 ,p_type IN VARCHAR2 := NULL
241 ,p_measure_is_validate IN VARCHAR2 := FND_API.G_TRUE -- ankgoel: bug#3557236
242 ,p_dimension1_id IN NUMBER
243 ,p_dimension2_id IN NUMBER
244 ,p_dimension3_id IN NUMBER
245 ,p_dimension4_id IN NUMBER
246 ,p_dimension5_id IN NUMBER
247 ,p_dimension6_id IN NUMBER
248 ,p_dimension7_id IN NUMBER
249 ,p_y_axis_title IN VARCHAR2 := NULL
250 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
251 ,p_ui_flag IN VARCHAR2 := c_UI_FLAG
252 ,p_last_update_date IN BIS_INDICATORS.LAST_UPDATE_DATE%TYPE
253 ,p_func_area_short_name IN VARCHAR2 := NULL
254 ,x_return_status OUT NOCOPY VARCHAR2
255 ,x_msg_count OUT NOCOPY NUMBER
256 ,x_msg_data OUT NOCOPY VARCHAR2
257 );
258
259
260 -- 18-OCT-2004 ADRAO, changed signature added p_measure_col_help, Bug#3817894
261 procedure Update_Measure(
262 p_commit IN VARCHAR2 := FND_API.G_FALSE
263 ,p_dataset_id IN NUMBER
264 ,p_dataset_source IN VARCHAR2
265 ,p_dataset_name IN VARCHAR2
266 ,p_dataset_help IN VARCHAR2 := NULL
267 ,p_dataset_measure_id1 IN NUMBER := NULL
268 ,p_dataset_operation IN VARCHAR2 := NULL
269 ,p_dataset_measure_id2 IN NUMBER := NULL
270 ,p_dataset_format_id IN NUMBER := NULL
271 ,p_dataset_color_method IN NUMBER := NULL
272 ,p_dataset_autoscale_flag IN NUMBER := NULL
273 ,p_dataset_projection_flag IN NUMBER := NULL
274 ,p_measure_short_name IN VARCHAR2
275 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type := -1
276 ,p_source_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
277 ,p_compare_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
278 ,p_measure_act_data_src_type IN VARCHAR2 := NULL
279 ,p_measure_act_data_src IN VARCHAR2 := NULL
280 ,p_measure_comparison_source IN VARCHAR2 := NULL
281 ,p_measure_operation IN VARCHAR2 := c_SUM
282 ,p_measure_uom_class IN VARCHAR2 := NULL
283 ,p_measure_increase_in_measure IN VARCHAR2 := NULL
284 ,p_measure_random_style IN NUMBER := NULL
285 ,p_measure_min_act_value IN NUMBER := NULL
286 ,p_measure_max_act_value IN NUMBER := NULL
287 ,p_measure_min_bud_value IN NUMBER := NULL
288 ,p_measure_max_bud_value IN NUMBER := NULL
289 ,p_measure_app_id IN NUMBER := NULL
290 ,p_measure_col IN VARCHAR2 := NULL
291 ,p_measure_col_help IN VARCHAR2 := NULL
292 ,p_measure_group_id IN NUMBER := NULL
293 ,p_measure_projection_id IN NUMBER := NULL
294 ,p_measure_type IN NUMBER := NULL
295 ,p_measure_apply_rollup IN VARCHAR2 := NULL
296 ,p_measure_function_name IN VARCHAR2 := NULL
297 ,p_measure_enable_link IN VARCHAR2 := NULL
298 ,p_measure_obsolete IN VARCHAR2 := FND_API.G_FALSE
299 ,p_type IN VARCHAR2 := NULL
300 ,p_measure_is_validate IN VARCHAR2 := FND_API.G_TRUE -- ankgoel: bug#3557236
301 ,p_time_stamp IN VARCHAR2 := NULL -- Added for Granular Locking
302 ,p_dimension1_id IN NUMBER
303 ,p_dimension2_id IN NUMBER
304 ,p_dimension3_id IN NUMBER
305 ,p_dimension4_id IN NUMBER
306 ,p_dimension5_id IN NUMBER
307 ,p_dimension6_id IN NUMBER
308 ,p_dimension7_id IN NUMBER
309 ,p_y_axis_title IN VARCHAR2 := NULL
310 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
311 ,p_ui_flag IN VARCHAR2 := c_UI_FLAG
312 ,p_func_area_short_name IN VARCHAR2 := NULL
313 ,x_return_status OUT NOCOPY VARCHAR2
314 ,x_msg_count OUT NOCOPY NUMBER
315 ,x_msg_data OUT NOCOPY VARCHAR2
316 );
317
318 --Bug#4045278: Wrapper for Update_Measure that takes in last_update_date
319 procedure Update_Measure(
320 p_commit IN VARCHAR2 := FND_API.G_FALSE
321 ,p_dataset_id IN NUMBER
322 ,p_dataset_source IN VARCHAR2
323 ,p_dataset_name IN VARCHAR2
324 ,p_dataset_help IN VARCHAR2 := NULL
325 ,p_dataset_measure_id1 IN NUMBER := NULL
326 ,p_dataset_operation IN VARCHAR2 := NULL
327 ,p_dataset_measure_id2 IN NUMBER := NULL
328 ,p_dataset_format_id IN NUMBER := NULL
329 ,p_dataset_color_method IN NUMBER := NULL
330 ,p_dataset_autoscale_flag IN NUMBER := NULL
331 ,p_dataset_projection_flag IN NUMBER := NULL
332 ,p_measure_short_name IN VARCHAR2
333 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type := -1
334 ,p_source_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
335 ,p_compare_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
336 ,p_measure_act_data_src_type IN VARCHAR2 := NULL
337 ,p_measure_act_data_src IN VARCHAR2 := NULL
338 ,p_measure_comparison_source IN VARCHAR2 := NULL
339 ,p_measure_operation IN VARCHAR2 := c_SUM
340 ,p_measure_uom_class IN VARCHAR2 := NULL
341 ,p_measure_increase_in_measure IN VARCHAR2 := NULL
342 ,p_measure_random_style IN NUMBER := NULL
343 ,p_measure_min_act_value IN NUMBER := NULL
344 ,p_measure_max_act_value IN NUMBER := NULL
345 ,p_measure_min_bud_value IN NUMBER := NULL
346 ,p_measure_max_bud_value IN NUMBER := NULL
347 ,p_measure_app_id IN NUMBER := NULL
348 ,p_measure_col IN VARCHAR2 := NULL
349 ,p_measure_col_help IN VARCHAR2 := NULL
350 ,p_measure_group_id IN NUMBER := NULL
351 ,p_measure_projection_id IN NUMBER := NULL
352 ,p_measure_type IN NUMBER := NULL
353 ,p_measure_apply_rollup IN VARCHAR2 := NULL
354 ,p_measure_function_name IN VARCHAR2 := NULL
355 ,p_measure_enable_link IN VARCHAR2 := NULL
356 ,p_measure_obsolete IN VARCHAR2 := FND_API.G_FALSE
357 ,p_type IN VARCHAR2 := NULL
358 ,p_measure_is_validate IN VARCHAR2 := FND_API.G_TRUE -- ankgoel: bug#3557236
359 ,p_time_stamp IN VARCHAR2 := NULL -- Added for Granular Locking
360 ,p_dimension1_id IN NUMBER
361 ,p_dimension2_id IN NUMBER
362 ,p_dimension3_id IN NUMBER
363 ,p_dimension4_id IN NUMBER
364 ,p_dimension5_id IN NUMBER
365 ,p_dimension6_id IN NUMBER
366 ,p_dimension7_id IN NUMBER
367 ,p_y_axis_title IN VARCHAR2 := NULL
368 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
369 ,p_ui_flag IN VARCHAR2 := c_UI_FLAG
370 ,p_last_update_date IN BIS_INDICATORS.LAST_UPDATE_DATE%TYPE
371 ,p_func_area_short_name IN VARCHAR2 := NULL
372 ,x_return_status OUT NOCOPY VARCHAR2
373 ,x_msg_count OUT NOCOPY NUMBER
374 ,x_msg_data OUT NOCOPY VARCHAR2
375 );
376
377
378 procedure Delete_measure(
379 p_commit IN VARCHAR2 := FND_API.G_FALSE
380 ,p_dataset_id IN NUMBER
381 ,p_time_stamp IN VARCHAR2 := NULL -- Added for Granular Locking
382 ,x_return_status OUT NOCOPY VARCHAR2
383 ,x_msg_count OUT NOCOPY NUMBER
384 ,x_msg_data OUT NOCOPY VARCHAR2
385 );
386
387
388 procedure Apply_Dataset_Calc(
389 p_commit IN VARCHAR2 := FND_API.G_FALSE
390 ,p_dataset_id IN NUMBER
391 ,p_Disabled_Calc_table IN BSC_NUM_LIST
392 ,x_return_status OUT NOCOPY VARCHAR2
393 ,x_msg_count OUT NOCOPY NUMBER
394 ,x_msg_data OUT NOCOPY VARCHAR2
395 );
396
397
398 PROCEDURE Apply_Cause_Effect_Rels(
399 p_commit IN VARCHAR2 := FND_API.G_FALSE
400 ,p_dataset_id IN NUMBER
401 ,p_causes_table IN BSC_NUM_LIST
402 ,p_effects_table IN BSC_NUM_LIST
403 ,x_return_status OUT NOCOPY VARCHAR2
404 ,x_msg_count OUT NOCOPY NUMBER
405 ,x_msg_data OUT NOCOPY VARCHAR2
406 );
407
408 function getColorFormula(
409 p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
410 ,p_Measure_Apply_Rollup IN VARCHAR2) return varchar2;
411
412 --ASHANKAR added on 09-Jun-2003
413 FUNCTION GET_AO_NAME
414 (
415 p_indicator in NUMBER
416 , p_a0 in NUMBER
417 , p_a1 in NUMBER
418 , p_a2 in NUMBER
419 , p_group_id in NUMBER
420 ) RETURN VARCHAR2;
421
422 FUNCTION GET_SERIES_COUNT
423 (
424 p_indicator IN NUMBER
425 , p_a0 IN NUMBER
426 , p_a1 IN NUMBER
427 , p_a2 IN NUMBER
428 ) RETURN NUMBER;
429
430
431 --=============================================================================
432
433 Procedure Load_Measure
434 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
435 ,p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
436 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
437 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
438 ,p_custom_mode IN VARCHAR2 := NULL
439 ,p_application_short_name IN VARCHAR2
440 ,p_Org_Dimension_Short_Name IN VARCHAR2
441 ,p_Time_Dimension_Short_Name IN VARCHAR2
442 ,p_measure_group_name IN VARCHAR2
443 ,p_measure_apply_rollup IN VARCHAR2
444 ,x_return_status OUT NOCOPY VARCHAR2
445 ,x_msg_count OUT NOCOPY NUMBER
446 ,x_msg_data OUT NOCOPY VARCHAR2
447 );
448 --=============================================================================
449 PROCEDURE Translate_Measure
450 (p_commit IN VARCHAR2 := FND_API.G_FALSE
451 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
452 ,p_custom_mode IN VARCHAR2 := NULL
453 ,p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
454 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
455 ,x_return_status OUT NOCOPY VARCHAR2
456 ,x_msg_count OUT NOCOPY NUMBER
457 ,x_msg_data OUT NOCOPY VARCHAR2
458 ) ;
459 --=============================================================================
460 --=============================================================================
461 PROCEDURE Ret_Dataset_Fr_Meas_Shrt_Name(
462 p_Measure_Short_Name IN VARCHAR2
463 ,x_Dataset_Id OUT NOCOPY NUMBER
464 );
465 --=============================================================================
466 PROCEDURE Order_Dimensions_For_Ldt(
467 p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
468 ,p_Org_Dimension_Short_Name IN VARCHAR2
469 ,p_Time_Dimension_Short_Name IN VARCHAR2
470 ,x_Measure_Rec OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
471 ,x_return_status OUT NOCOPY VARCHAR2
472 ,x_msg_count OUT NOCOPY NUMBER
473 ,x_msg_data OUT NOCOPY VARCHAR2
474 );
475 --=============================================================================
476
477
478 -- mdamle 09/03/03 - Get measure col
479 function get_measure_col (
480 p_dataset_name IN VARCHAR2
481 ,p_source IN VARCHAR2
482 ,p_measure_id IN NUMBER
483 ,p_short_name IN VARCHAR2
484 ) return VARCHAR2;
485
486
487 -- mdamle 09/03/03 - Is Formula
488 function isFormula
489 (p_measure_col IN VARCHAR2) return boolean;
490
491 function getMeasureKpis (
492 p_dataset_id in number
493 ) return varchar2 ;
494
495
496 function getReturnMessage (
497 p_dataset_id in number
498 , p_message in varchar2
499 )
500 return varchar2;
501
502 procedure Get_Incr_Trigger(
503 p_commit in varchar2 := fnd_api.g_false
504 ,p_dataset_id in number
505 ,p_measure_projection_id in number := -1
506 ,p_measure_type in number := -1
507 ,p_is_ytd_enabled in varchar2 := null
508 ,p_is_qtd_enabled in varchar2 := null
509 ,p_is_xtd_enabled in varchar2 := null
510 ,p_rollup_calc in varchar2 := null
511 ,p_formula in varchar2 := null
512 ,p_Measure_Group_Id IN VARCHAR2
513 ,p_Check_Autogen_Only IN VARCHAR2 := null
514 ,x_return_status out nocopy varchar2
515 ,x_msg_count out nocopy number
516 ,x_msg_data out nocopy varchar2
517 ) ;
518
519
520 procedure get_Color_Change_Trigger(
521 p_kpi_id IN varchar2
522 ,p_dataset_data IN varchar2
523 ,p_m1_accept IN varchar2
524 ,p_m1_marg IN varchar2
525 ,p_m2_accept IN varchar2
526 ,p_m2_marg IN varchar2
527 ,p_m3_upr_accept IN varchar2
528 ,p_m3_upr_marg IN varchar2
529 ,p_m3_lwr_accept IN varchar2
530 ,p_m3_lwr_marg IN varchar2
531 ,x_return_status OUT NOCOPY varchar2
532 ,x_msg_count OUT NOCOPY number
533 ,x_msg_data OUT NOCOPY varchar2
534 );
535
536 FUNCTION Is_Unique_Measure_Display_Name(
537 p_dataset_id NUMBER
538 ,p_display_name VARCHAR2
539 ,p_source_type VARCHAR2
540 ) RETURN VARCHAR2;
541
542 --Return 'T' if specified column name in table is 'NUMBER', return 'F' otherwise
543 FUNCTION Is_Numeric_Column(
544 p_table_name VARCHAR2
545 ,p_column_name VARCHAR2
546 ) RETURN VARCHAR2;
547
548 -- Wrapper for fnd_load_util.upload_test() that test whether a record should
549 -- be updated based on Last_Update_Date.
550 FUNCTION Upload_Test (
551 p_measure_short_name IN VARCHAR2
552 ,p_nls_mode IN VARCHAR2
553 ,p_file_lub IN NUMBER
554 ,p_file_lud IN DATE
555 ,p_custom_mode IN VARCHAR2
556 ) RETURN BOOLEAN;
557
558
559 FUNCTION Get_Meas_With_Src_Col (
560 p_measure_col IN VARCHAR2
561 ) RETURN VARCHAR2;
562
563
564 FUNCTION Get_Sing_Par_Meas_DS (
565 p_measure_id IN VARCHAR2
566 ) RETURN VARCHAR2 ;
567
568 --Return primary data source (i.e. region_code portion of actual_data_source) of the indicator
569 FUNCTION Get_Primary_Data_Source (
570 p_indicator_id IN BIS_INDICATORS.INDICATOR_ID%TYPE
571 ) RETURN BIS_INDICATORS.ACTUAL_DATA_SOURCE%TYPE;
572
573 -- added for Bug#4617140
574 FUNCTION Is_Formula_Type
575 (p_measure_col IN VARCHAR2) RETURN VARCHAR2;
576
577 FUNCTION Get_Report_Objectives (
578 p_Dataset_Id IN NUMBER
579 ) RETURN VARCHAR2;
580
581 FUNCTION Is_Src_Col_In_Formulas(
582 p_Source_Col IN VARCHAR2
583 ) RETURN BOOLEAN;
584
585 end BSC_BIS_MEASURE_PUB;
[Home] [Help]