1 PACKAGE BSC_SIMULATION_VIEW_PUB AS
2 /* $Header: BSCSIMPS.pls 120.3.12000000.1 2007/07/17 07:44:27 appldev noship $ */
3
4 /*REM +=======================================================================+
5 REM | Copyright (c) 2004 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BSCSIMPS.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Wrapper API for SIMULATION |
13 REM | |
14 REM | NOTES |
15 REM | |
16 REM | HISTORY |
17 REM | 22-NOV-06 ashankar Created. |
18 REM | 15-MAR-07 ankgoel Bug#5933448 - Get correct measure formula for|
19 REM | non calculated KPIs in Simulation objectives |
20 REM | 29/03/07 ashankar Bug#5932973 Supporting filters and key items |
21 REM | for SM tree |
22 REM | 06-Jul-07 ashankar Bug#6166829 Fix the prototype_flag issues |
23 REM | +=====================================================================+*/
24
25
26 --////////////////////Node Specific Properties ////////////
27 c_EXISTS CONSTANT VARCHAR(2) := 'Y';
28 c_NOT_EXISTS CONSTANT VARCHAR(2) := 'N';
29 c_INDICATOR_TYPE CONSTANT NUMBER := 2;
30 c_TYPE CONSTANT NUMBER := 7;
31 c_NON_SIM_NODE CONSTANT NUMBER := 0;
32 c_SIM_NODE CONSTANT NUMBER := 1;
33 c_SIM_NODE_ID CONSTANT VARCHAR(10) := 'S_NODE_ID';
34 c_SIM_COLOR_FORMULA CONSTANT VARCHAR(20) := 'S_COLOR_FORMULA';
35 c_DEFAULT_SIM_NODE_ID CONSTANT NUMBER := -1;
36 c_TAB_ID CONSTANT NUMBER := -999;
37 c_CALCULATED_KPI CONSTANT VARCHAR2(10):='CDS_CALC';
38 c_YEAR_TO_DATE_ENABLED CONSTANT VARCHAR2(2) := 'Y';
39 c_YEAR_TO_DATE_DISABLED CONSTANT VARCHAR2(2) := 'N';
40 c_VISIBLE CONSTANT NUMBER :=2;
41 c_HIDE CONSTANT NUMBER :=0;
42 c_YTD_CALC CONSTANT NUMBER :=2;
43 c_SEMI_COLON_DELIM CONSTANT VARCHAR2(1) := ';';
44 c_DEFAULT_DATASET_ID CONSTANT NUMBER := -1;
45 c_CALC_KPI CONSTANT VARCHAR2(10):='CDS';
46 C_EMPTY CONSTANT VARCHAR2(10):='';
47
48
49
50 --////////////Color Specific Properties ////////////
51 c_TYPE_MEASURE_COLOR CONSTANT NUMBER := 13;
52 c_MEASURE_COLOR CONSTANT VARCHAR(16) := '<measure:color>';
53 c_PERCENT_OF_TARGET CONSTANT VARCHAR(30) := 'PERCENT_OF_TARGET';
54
55 --////////////////Ak Region Specific //////////////
56
57 c_MEASURE_NO_TARGET CONSTANT VARCHAR2(30) :='MEASURE_NOTARGET';
58
59 --//////////Dimension specific ///////////////
60 c_SIM_DIM_SET CONSTANT NUMBER :=0;
61
62
63
64 TYPE Bsc_Ak_Region_Items_Rec is RECORD
65 (
66 Attribute_Code ak_region_items_vl.attribute_code%TYPE
67 , shortName ak_region_items_vl.Attribute2%TYPE
68 , Formula VARCHAR2(32000)
69 , Measure_Col bsc_sys_measures.measure_col%TYPE
70 , Acutual_Formula ak_region_items_vl.Attribute3%TYPE
71 );
72
73 TYPE Bsc_Ak_Region_Items_Tbl_Type IS TABLE OF Bsc_Ak_Region_Items_Rec INDEX BY BINARY_INTEGER;
74
75
76
77 PROCEDURE Add_Or_Update_Sim_Tree_Bg (
78 p_obj_id IN NUMBER
79 ,p_image_id IN NUMBER
80 ,p_file_name IN VARCHAR2
81 ,p_description IN VARCHAR2
82 ,p_width IN NUMBER
83 ,p_height IN NUMBER
84 ,p_mime_type IN VARCHAR2
85 ,x_image_id OUT NOCOPY NUMBER
86 ,x_return_status OUT NOCOPY VARCHAR2
87 ,x_msg_count OUT NOCOPY NUMBER
88 ,x_msg_data OUT NOCOPY VARCHAR2
89 );
90
91
92 PROCEDURE Get_Objective_Details
93 (
94 p_Region_Code IN AK_REGIONS.REGION_CODE%TYPE
95 ,x_indicator OUT NOCOPY VARCHAR2
96 ,x_ind_group_id OUT NOCOPY VARCHAR2
97 ,x_tab_id OUT NOCOPY VARCHAR2
98 ,x_prototype_flag OUT NOCOPY VARCHAR2
99 ,x_ind_name OUT NOCOPY VARCHAR2
100 ,x_ytd_enabled OUT NOCOPY VARCHAR2
101 ,x_return_status OUT NOCOPY VARCHAR2
102 ,x_msg_count OUT NOCOPY NUMBER
103 ,x_msg_data OUT NOCOPY VARCHAR2
104 );
105
106
107 PROCEDURE add_or_update_measure
108 (
109 p_tab_id IN NUMBER
110 ,p_tab_view_id IN NUMBER
111 ,p_text_object_id IN NUMBER
112 ,p_text_flag IN NUMBER
113 ,p_font_size IN NUMBER
114 ,p_font_style IN NUMBER
115 ,p_font_color IN NUMBER
116 ,p_text_left IN NUMBER
117 ,p_text_top IN NUMBER
118 ,p_text_width IN NUMBER
119 ,p_text_height IN NUMBER
120 ,p_slider_object_id IN NUMBER
121 ,p_slider_flag IN NUMBER
122 ,p_slider_left IN NUMBER
123 ,p_slider_top IN NUMBER
124 ,p_slider_width IN NUMBER
125 ,p_slider_height IN NUMBER
126 ,p_actual_object_id IN NUMBER
127 ,p_actual_flag IN NUMBER
128 ,p_actual_left IN NUMBER
129 ,p_actual_top IN NUMBER
130 ,p_actual_width IN NUMBER
131 ,p_actual_height IN NUMBER
132 ,p_change_object_id IN NUMBER
133 ,p_change_flag IN NUMBER
134 ,p_change_left IN NUMBER
135 ,p_change_top IN NUMBER
136 ,p_change_width IN NUMBER
137 ,p_change_height IN NUMBER
138 ,p_color_object_id IN NUMBER
139 ,p_color_flag IN NUMBER
140 ,p_color_left IN NUMBER
141 ,p_color_top IN NUMBER
142 ,p_color_width IN NUMBER
143 ,p_color_height IN NUMBER
144 ,p_indicator_id IN NUMBER
145 ,p_function_id IN NUMBER
146 ,p_Node_Id IN NUMBER
147 ,p_Node_Name IN VARCHAR2
148 ,p_Node_Help IN VARCHAR2
149 ,p_SimulateFlag IN NUMBER
150 ,p_Format_id IN NUMBER
151 ,p_Node_Color_flag IN NUMBER
152 ,p_Node_Color_method IN NUMBER
153 ,p_Navigates_to_trend IN NUMBER
154 ,p_Top_position IN NUMBER
155 ,p_Left_position IN NUMBER
156 ,p_Width IN NUMBER
157 ,p_Height IN NUMBER
158 ,p_Autoscale_flag IN NUMBER
159 ,p_Y_axis_title IN VARCHAR2
160 ,p_Node_Attr_Code IN VARCHAR2
161 ,p_Node_Short_Name IN VARCHAR2
162 ,p_default_node IN NUMBER
163 ,p_color_thresholds IN VARCHAR2
164 ,p_color_by_total IN NUMBER
165 ,x_return_status OUT NOCOPY VARCHAR2
166 ,x_msg_count OUT NOCOPY NUMBER
167 ,x_msg_data OUT NOCOPY VARCHAR2
168 );
169
170 PROCEDURE add_or_update_sim_node_props
171 (
172 p_indicator IN NUMBER
173 ,p_Node_Id IN NUMBER
174 ,p_Node_Name IN VARCHAR2
175 ,p_Node_Help IN VARCHAR2
176 ,p_SimulateFlag IN NUMBER
177 ,p_Format_id IN NUMBER
178 ,p_Color_flag IN NUMBER
179 ,p_Color_method IN NUMBER
180 ,p_Navigates_to_trend IN NUMBER
181 ,p_Top_position IN NUMBER
182 ,p_Left_position IN NUMBER
183 ,p_Width IN NUMBER
184 ,p_Height IN NUMBER
185 ,p_Autoscale_flag IN NUMBER
186 ,p_Y_axis_title IN VARCHAR2
187 ,p_Node_Attr_Code IN VARCHAR2
188 ,p_Node_Short_Name IN VARCHAR2
189 ,x_return_status OUT NOCOPY VARCHAR2
190 ,x_msg_count OUT NOCOPY NUMBER
191 ,x_msg_data OUT NOCOPY VARCHAR2
192 );
193
194 PROCEDURE set_default_node
195 (
196 p_indicator IN NUMBER
197 ,p_default_node IN NUMBER
198 ,p_dataset_id IN NUMBER
199 ,x_return_status OUT NOCOPY VARCHAR2
200 ,x_msg_count OUT NOCOPY NUMBER
201 ,x_msg_data OUT NOCOPY VARCHAR2
202 );
203
204
205 PROCEDURE remove_simulation_view_items
206 (
207 p_tab_id IN NUMBER
208 ,p_obj_Id IN NUMBER
209 ,p_labels IN VARCHAR2
210 ,x_return_status OUT NOCOPY VARCHAR2
211 ,x_msg_count OUT NOCOPY NUMBER
212 ,x_msg_data OUT NOCOPY VARCHAR2
213 );
214
215
216 PROCEDURE Duplicate_kpi_metadata
217 (
218 p_source_kpi IN NUMBER
219 ,p_target_kpi IN NUMBER
220 ,x_return_status OUT NOCOPY VARCHAR2
221 ,x_msg_count OUT NOCOPY NUMBER
222 ,x_msg_data OUT NOCOPY VARCHAR2
223 );
224
225
226 PROCEDURE Validate_Name_In_Tab
227 (
228 p_name IN VARCHAR2
229 ,p_tabId IN NUMBER
230 ,x_return_status OUT NOCOPY VARCHAR2
231 ,x_msg_count OUT NOCOPY NUMBER
232 ,x_msg_data OUT NOCOPY VARCHAR2
233
234 );
235
236 PROCEDURE Add_Or_Update_YTD
237 (
238 p_indicator IN NUMBER
239 ,p_YTD IN VARCHAR2
240 ,p_prev_YTD IN VARCHAR2
241 ,x_return_status OUT NOCOPY VARCHAR2
242 ,x_msg_count OUT NOCOPY NUMBER
243 ,x_msg_data OUT NOCOPY VARCHAR2
244 );
245
246 PROCEDURE Save_Color_Ranges
247 (
248 p_indicator IN NUMBER
249 ,p_dataset_id IN NUMBER
250 ,p_color_ranges IN VARCHAR2
251 ,x_return_status OUT NOCOPY VARCHAR2
252 ,x_msg_count OUT NOCOPY NUMBER
253 ,x_msg_data OUT NOCOPY VARCHAR2
254 );
255
256
257 FUNCTION Get_Kpi_Measure_Id
258 (
259 p_indicator IN NUMBER
260 ,p_dataset_id IN NUMBER
261 ) RETURN NUMBER;
262
263
264 FUNCTION Get_Formula_Base_Columns
265 (
266 p_indicator IN bsc_kpis_b.indicator%TYPE
267 ,p_Dataset_Id IN bsc_sys_datasets_b.dataset_id%TYPE
268 ,p_Meas_Col IN bsc_sys_measures.measure_col%TYPE
269 )RETURN VARCHAR2;
270
271 PROCEDURE copy_sim_metadata
272 (
273 p_source_kpi IN NUMBER
274 ,p_target_kpi IN NUMBER
275 ,x_return_status OUT NOCOPY VARCHAR2
276 ,x_msg_count OUT NOCOPY NUMBER
277 ,x_msg_data OUT NOCOPY VARCHAR2
278 );
279
280 FUNCTION Get_Kpi_MeasureCol
281 (
282 p_DatasetId IN bsc_sys_datasets_b.dataset_id%TYPE
283 ) RETURN VARCHAR2;
284
285
286 PROCEDURE Set_Sim_Key_Values
287 (
288 p_ind_Sht_Name IN BSC_KPIS_B.short_name%TYPE
289 ,p_indicator IN BSC_KPIS_B.indicator%TYPE
290 ,x_return_status OUT NOCOPY VARCHAR2
291 ,x_msg_count OUT NOCOPY NUMBER
292 ,x_msg_data OUT NOCOPY VARCHAR2
293 );
294
295 END BSC_SIMULATION_VIEW_PUB;