1 PACKAGE BSC_CUSTOM_VIEW_UI_WRAPPER AS
2 /* $Header: BSCCVDPS.pls 120.7 2007/03/15 10:42:03 ashankar ship $ */
3 c_type_label CONSTANT NUMBER := 0;
4 c_type_link CONSTANT NUMBER := 1;
5 c_type_launch_pad CONSTANT NUMBER := 2;
6 c_type_kpi CONSTANT NUMBER := 4;
7 c_type_kpi_actual CONSTANT NUMBER := 5;
8 c_type_kpi_change CONSTANT NUMBER := 6;
9 c_type_measure CONSTANT NUMBER := 10;
10 c_type_measure_actual CONSTANT NUMBER := 11;
11 c_type_measure_change CONSTANT NUMBER := 12;
12 c_type_measure_slider CONSTANT NUMBER := 14;
13 c_type_hotspot CONSTANT NUMBER := 0;
14
15 c_kpi CONSTANT VARCHAR(5) := '<kpi>';
16 c_kpi_actual CONSTANT VARCHAR(12) := '<kpi:actual>';
17 c_kpi_change CONSTANT VARCHAR(12) := '<kpi:change>';
18
19 c_measure CONSTANT VARCHAR(9) := '<measure>';
20 c_measure_actual CONSTANT VARCHAR(16) := '<measure:actual>';
21 c_measure_change CONSTANT VARCHAR(16) := '<measure:change>';
22 c_measure_slider CONSTANT VARCHAR(16) := '<measure:slider>';
23
24 C_FUNC_WEB_HTML_CALL CONSTANT VARCHAR2(100) := 'OA.jsp?akRegionCode=BSC_PORTLET_CUSTOM_VIEW&akRegionApplicationId=271&dispRespCustPg=N';
25 C_FUNC_REGION_APPLICATION_ID CONSTANT VARCHAR2(5) := '271';
26 C_FUNC_REGION_CODE CONSTANT VARCHAR2(50) := 'BSC_PORTLET_CUSTVIEW_CUST';
27 C_FUNC_TYPE CONSTANT VARCHAR2(20) := 'WEBPORTLET';
28 C_FUNCTIONAL_AREA CONSTANT VARCHAR2(3) := 'FA';
29 C_FORM_FUNCTION CONSTANT VARCHAR2(3) := 'FN';
30
31 --/////Added for Trend icon support in custom view
32 c_type_kpi_trend CONSTANT NUMBER := 7;
33 c_kpi_trend CONSTANT VARCHAR(12) := '<kpi:trend>';
34
35
36 --Compact all label ids in BSC_TAB_VIEW_LABELS_TL and BSC_TAB_VIEW_LABELS_B to be in consecutive order
37 PROCEDURE compact_custom_view_labels(
38 p_tab_id IN NUMBER
39 ,p_tab_view_id IN NUMBER
40 ,x_return_status OUT NOCOPY VARCHAR2
41 ,x_msg_count OUT NOCOPY NUMBER
42 ,x_msg_data OUT NOCOPY VARCHAR2
43 );
44
45 -- Clear BSC_TAB_VIEW_LABELS_TL, BSC_TAB_VIEW_LABELS_B and BSC_TAB_VIEW_KPI_TL with given tab_id and tab_view_id
46 PROCEDURE clear_custom_view_canvas(
47 p_tab_id IN NUMBER
48 ,p_tab_view_id IN NUMBER
49 ,x_return_status OUT NOCOPY VARCHAR2
50 ,x_msg_count OUT NOCOPY NUMBER
51 ,x_msg_data OUT NOCOPY VARCHAR2
52 );
53
54 --Remove all items specified in removedKPIs and removedLabels
55 --Format of removedKPIs and removedLabels are id1,id2,id3,...,idN
56 PROCEDURE remove_custom_view_items(
57 p_tab_id IN NUMBER
58 ,p_tab_view_id IN NUMBER
59 ,p_kpis IN VARCHAR2
60 ,p_labels IN VARCHAR2
61 ,x_return_status OUT NOCOPY VARCHAR2
62 ,x_msg_count OUT NOCOPY NUMBER
63 ,x_msg_data OUT NOCOPY VARCHAR2
64 );
65
66 -- Add specified label to BSC_TAB_VIEW_LABELS_PKG
67 PROCEDURE add_or_update_kpi_label(
68 p_tab_id IN NUMBER
69 ,p_tab_view_id IN NUMBER
70 ,p_object_id IN NUMBER
71 ,p_text_flag IN NUMBER
72 ,p_label_text IN VARCHAR2
73 ,p_font_size IN NUMBER
74 ,p_font_style IN NUMBER
75 ,p_font_color IN NUMBER
76 ,p_left IN NUMBER
77 ,p_top IN NUMBER
78 ,p_width IN NUMBER
79 ,p_height IN NUMBER
80 ,p_kpi_id IN NUMBER
81 ,p_function_id IN NUMBER
82 ,x_return_status OUT NOCOPY VARCHAR2
83 ,x_msg_count OUT NOCOPY NUMBER
84 ,x_msg_data OUT NOCOPY VARCHAR2
85 );
86
87 -- Add specified kpi to BSC_TAB_VIEW_KPI_TL
88 PROCEDURE add_or_update_kpi(
89 p_tab_id IN NUMBER
90 ,p_tab_view_id IN NUMBER
91 ,p_object_id IN NUMBER
92 ,p_kpi_id IN NUMBER
93 ,p_text_flag IN NUMBER
94 ,p_font_size IN NUMBER
95 ,p_font_style IN NUMBER
96 ,p_font_color IN NUMBER
97 ,p_hotspot_left IN NUMBER
98 ,p_hotspot_top IN NUMBER
99 ,p_hotspot_width IN NUMBER
100 ,p_hotspot_height IN NUMBER
101 ,p_alarm_left IN NUMBER
102 ,p_alarm_top IN NUMBER
103 ,p_alarm_width IN NUMBER
104 ,p_alarm_height IN NUMBER
105 ,p_actual_object_id IN NUMBER
106 ,p_actual_flag IN NUMBER
107 ,p_actual_left IN NUMBER
108 ,p_actual_top IN NUMBER
109 ,p_actual_width IN NUMBER
110 ,p_actual_height IN NUMBER
111 ,p_change_object_id IN NUMBER
112 ,p_change_flag IN NUMBER
113 ,p_change_left IN NUMBER
114 ,p_change_top IN NUMBER
115 ,p_change_width IN NUMBER
116 ,p_change_height IN NUMBER
117 ,p_link_function_id IN NUMBER
118 ,p_trend_object_id IN NUMBER
119 ,p_trend_flag IN NUMBER
120 ,p_trend_left IN NUMBER
121 ,p_trend_top IN NUMBER
122 ,p_trend_width IN NUMBER
123 ,p_trend_height IN NUMBER
124 ,x_return_status OUT NOCOPY VARCHAR2
125 ,x_msg_count OUT NOCOPY NUMBER
126 ,x_msg_data OUT NOCOPY VARCHAR2
127 );
128
129 -- Add specified label to BSC_TAB_VIEW_LABELS_PKG
130 PROCEDURE add_or_update_label(
131 p_tab_id IN NUMBER
132 ,p_tab_view_id IN NUMBER
133 ,p_object_id IN NUMBER
134 ,p_label_text IN VARCHAR2
135 ,p_font_size IN NUMBER
136 ,p_font_style IN NUMBER
137 ,p_font_color IN NUMBER
138 ,p_left IN NUMBER
139 ,p_top IN NUMBER
140 ,p_width IN NUMBER
141 ,p_height IN NUMBER
142 ,x_return_status OUT NOCOPY VARCHAR2
143 ,x_msg_count OUT NOCOPY NUMBER
144 ,x_msg_data OUT NOCOPY VARCHAR2
145 );
146
147 -- Add specified hotspot to BSC_TAB_VIEW_LABELS_PKG
148 PROCEDURE add_or_update_hotspot(
149 p_tab_id IN NUMBER
150 ,p_tab_view_id IN NUMBER
151 ,p_object_id IN NUMBER
152 ,p_label_text IN VARCHAR2
153 ,p_left IN NUMBER
154 ,p_top IN NUMBER
155 ,p_width IN NUMBER
156 ,p_height IN NUMBER
157 ,x_return_status OUT NOCOPY VARCHAR2
158 ,x_msg_count OUT NOCOPY NUMBER
159 ,x_msg_data OUT NOCOPY VARCHAR2
160 );
161
162 -- Add specified custom view link to BSC_TAB_VIEW_LABELS_PKG
163 PROCEDURE add_or_update_custom_view_link(
164 p_tab_id IN NUMBER
165 ,p_tab_view_id IN NUMBER
166 ,p_object_id IN NUMBER
167 ,p_text_flag IN NUMBER
168 ,p_label_text IN VARCHAR2
169 ,p_font_size IN NUMBER
170 ,p_font_style IN NUMBER
171 ,p_font_color IN NUMBER
172 ,p_left IN NUMBER
173 ,p_top IN NUMBER
174 ,p_width IN NUMBER
175 ,p_height IN NUMBER
176 ,p_link_tab_view_id IN NUMBER
177 ,x_return_status OUT NOCOPY VARCHAR2
178 ,x_msg_count OUT NOCOPY NUMBER
179 ,x_msg_data OUT NOCOPY VARCHAR2
180 );
181
182 -- Add specified launchpad to BSC_TAB_VIEW_LABELS_PKG
183 PROCEDURE add_or_update_launch_pad(
184 p_tab_id IN NUMBER
185 ,p_tab_view_id IN NUMBER
186 ,p_object_id IN NUMBER
187 ,p_label_text IN VARCHAR2
188 ,p_note_text IN VARCHAR2
189 ,p_left IN NUMBER
190 ,p_top IN NUMBER
191 ,p_width IN NUMBER
192 ,p_height IN NUMBER
193 ,p_menu_id IN NUMBER
194 ,x_return_status OUT NOCOPY VARCHAR2
195 ,x_msg_count OUT NOCOPY NUMBER
196 ,x_msg_data OUT NOCOPY VARCHAR2
197 );
198
199 -- Add specified measure (existing kpi) to BSC_TAB_VIEW_LABELS_PKG
200 PROCEDURE add_or_update_measure(
201 p_tab_id IN NUMBER
202 ,p_tab_view_id IN NUMBER
203 ,p_text_object_id IN NUMBER
204 ,p_text_flag IN NUMBER
205 ,p_font_size IN NUMBER
206 ,p_font_style IN NUMBER
207 ,p_font_color IN NUMBER
208 ,p_text_left IN NUMBER
209 ,p_text_top IN NUMBER
210 ,p_text_width IN NUMBER
211 ,p_text_height IN NUMBER
212 ,p_slider_object_id IN NUMBER
213 ,p_slider_flag IN NUMBER
214 ,p_slider_left IN NUMBER
215 ,p_slider_top IN NUMBER
216 ,p_slider_width IN NUMBER
217 ,p_slider_height IN NUMBER
218 ,p_actual_object_id IN NUMBER
219 ,p_actual_flag IN NUMBER
220 ,p_actual_left IN NUMBER
221 ,p_actual_top IN NUMBER
222 ,p_actual_width IN NUMBER
223 ,p_actual_height IN NUMBER
224 ,p_change_object_id IN NUMBER
225 ,p_change_flag IN NUMBER
226 ,p_change_left IN NUMBER
227 ,p_change_top IN NUMBER
228 ,p_change_width IN NUMBER
229 ,p_change_height IN NUMBER
230 ,p_indicator_id IN NUMBER
231 ,p_function_id IN NUMBER
232 ,x_return_status OUT NOCOPY VARCHAR2
233 ,x_msg_count OUT NOCOPY NUMBER
234 ,x_msg_data OUT NOCOPY VARCHAR2
235 );
236
237 -- Wrappers for calling BSC_TAB_VIEW_KPI_PKG
238 PROCEDURE add_or_update_tab_view_kpi(
239 p_tab_id IN NUMBER
240 ,p_tab_view_id IN NUMBER
241 ,p_kpi_id IN NUMBER
242 ,p_text_flag IN NUMBER
243 ,p_font_size IN NUMBER
244 ,p_font_style IN NUMBER
245 ,p_font_color IN NUMBER
246 ,p_hotspot_left IN NUMBER
247 ,p_hotspot_top IN NUMBER
248 ,p_hotspot_width IN NUMBER
249 ,p_hotspot_height IN NUMBER
250 ,p_alarm_left IN NUMBER
251 ,p_alarm_top IN NUMBER
252 ,p_alarm_width IN NUMBER
253 ,p_alarm_height IN NUMBER
254 ,x_return_status OUT NOCOPY VARCHAR2
255 ,x_msg_count OUT NOCOPY NUMBER
256 ,x_msg_data OUT NOCOPY VARCHAR2
257 );
258
259 -- Create or udpate tab view's background in BSC_SYS_IMAGES and BSC_SYS_IMAGES_MAP_PKG
260 PROCEDURE add_or_update_tab_view_bg (
261 p_tab_id IN NUMBER
262 ,p_tab_view_id IN NUMBER
263 ,p_image_id IN NUMBER
264 ,p_file_name IN VARCHAR2
265 ,p_description IN VARCHAR2
266 ,p_width IN NUMBER
267 ,p_height IN NUMBER
268 ,p_mime_type IN VARCHAR2
269 ,x_image_id OUT NOCOPY NUMBER
270 ,x_return_status OUT NOCOPY VARCHAR2
271 ,x_msg_count OUT NOCOPY NUMBER
272 ,x_msg_data OUT NOCOPY VARCHAR2
273 );
274
275 -- Wrapper for calling BSC_TAB_VIEW_LABELS_PKG procedures
276 PROCEDURE add_or_update_tab_view_label(
277 p_tab_id IN NUMBER
278 ,p_tab_view_id IN NUMBER
279 ,p_object_id IN NUMBER
280 ,p_object_type IN NUMBER
281 ,p_label_text IN VARCHAR2
282 ,p_text_flag IN NUMBER
283 ,p_font_color IN NUMBER
284 ,p_font_size IN NUMBER
285 ,p_font_style IN NUMBER
286 ,p_left IN NUMBER
287 ,p_top IN NUMBER
288 ,p_width IN NUMBER
289 ,p_height IN NUMBER
290 ,p_note_text IN VARCHAR2
291 ,p_link_id IN NUMBER
292 ,p_function_id IN NUMBER
293 ,x_return_status OUT NOCOPY VARCHAR2
294 ,x_msg_count OUT NOCOPY NUMBER
295 ,x_msg_data OUT NOCOPY VARCHAR2
296 );
297
298 -- Create or update tab view properties in BSC_TAB_VIEWS_PKG
299 PROCEDURE add_or_update_tab_view (
300 p_tab_id IN NUMBER
301 ,p_tab_view_id IN NUMBER
302 ,p_name IN VARCHAR2
303 ,p_func_area_short_name IN VARCHAR2
304 ,p_internal_name IN VARCHAR2
305 ,p_description IN VARCHAR2
306 ,p_enable_flag IN NUMBER
307 ,p_create_form_func IN VARCHAR2
308 ,p_last_update_date IN VARCHAR2
309 ,x_return_status OUT NOCOPY VARCHAR2
310 ,x_msg_count OUT NOCOPY NUMBER
311 ,x_msg_data OUT NOCOPY VARCHAR2
312 );
313
314 -- Create or update tab view properties in BSC_TAB_VIEWS_PKG
315 -- Called from UI for the extra original Name
316 PROCEDURE add_or_update_tab_view (
317 p_tab_id IN NUMBER
318 ,p_tab_view_id IN NUMBER
319 ,p_name IN VARCHAR2
320 ,p_func_area_short_name IN VARCHAR2
321 ,p_internal_name IN VARCHAR2
322 ,p_description IN VARCHAR2
323 ,p_enable_flag IN NUMBER
324 ,p_is_default_int_name IN VARCHAR2
325 ,p_create_form_func IN VARCHAR2
326 ,p_last_update_date IN VARCHAR2
327 ,x_return_status OUT NOCOPY VARCHAR2
328 ,x_msg_count OUT NOCOPY NUMBER
329 ,x_msg_data OUT NOCOPY VARCHAR2
330 );
331 -- Check if the given tab view exists, return 'Y' if it exists, 'N' otherwise
332 FUNCTION is_tab_view_exist (
333 p_tab_id IN NUMBER
334 ,p_tab_view_id IN NUMBER
335 ) RETURN VARCHAR2;
336
337 -- Compare given tab view timestamp with that in DB. Return 0 if it is the
338 -- same, 1 otherwise.
339 FUNCTION compare_tab_view_timestamp (
340 p_tab_id IN NUMBER
341 ,p_tab_view_id IN NUMBER
342 ,p_last_update_date IN VARCHAR2
343 ) RETURN NUMBER ;
344
345 PROCEDURE add_or_update_function (
346 p_tab_id IN NUMBER
347 ,p_tab_view_id IN NUMBER
348 ,p_name IN VARCHAR2
349 ,p_internal_name IN VARCHAR2 := NULL
350 ,p_description IN VARCHAR2
351 ,x_function_id OUT NOCOPY NUMBER
352 ,x_return_status OUT NOCOPY VARCHAR2
353 ,x_msg_count OUT NOCOPY NUMBER
354 ,x_msg_data OUT NOCOPY VARCHAR2
355 );
356
357 procedure delete_function (
358 p_tab_id IN NUMBER
359 ,p_tab_view_id IN NUMBER
360 ,x_return_status OUT NOCOPY VARCHAR2
361 ,x_msg_count OUT NOCOPY NUMBER
362 ,x_msg_data OUT NOCOPY VARCHAR2
363 );
364
365 FUNCTION get_param_search_string (
366 p_tab_id IN NUMBER
367 ,p_tab_view_id IN NUMBER
368 ) RETURN VARCHAR2 ;
369
370 FUNCTION next_custom_view_id (
371 p_tab_id IN NUMBER
372 ) RETURN NUMBER ;
373
374 PROCEDURE Get_Or_CreateNew_Scorecard
375 (
376 p_report_sht_name IN VARCHAR
377 , p_resp_Id IN NUMBER
378 , p_time_stamp IN VARCHAR2
379 , p_Application_Id IN NUMBER
380 , x_time_stamp OUT NOCOPY VARCHAR2
381 , x_tab_Id OUT NOCOPY NUMBER
382 , x_return_status OUT NOCOPY VARCHAR2
383 , x_msg_count OUT NOCOPY NUMBER
384 , x_msg_data OUT NOCOPY VARCHAR2
385 );
386
387 PROCEDURE Get_Measure_Display_Name
388 (
389 p_region_code IN VARCHAR
390 ,p_dataset_id IN NUMBER
391 ,x_meas_disp_name OUT NOCOPY VARCHAR
392 );
393
394 PROCEDURE Get_Measure_Prop
395 (
396 p_region_code IN VARCHAR
397 ,p_dataset_id IN NUMBER
398 ,x_meas_disp_name OUT NOCOPY AK_REGION_ITEMS_VL.ATTRIBUTE_LABEL_LONG%TYPE
399 ,x_measure_type OUT NOCOPY BIS_INDICATORS_VL.MEASURE_TYPE%TYPE
400 ,x_source OUT NOCOPY BSC_SYS_DATASETS_B.SOURCE%TYPE
401 ,x_item_type OUT NOCOPY AK_REGION_ITEMS_VL.attribute1%TYPE
402 );
403
404
405 FUNCTION Get_Functional_Area_Code
406 RETURN VARCHAR2;
407
408 FUNCTION Get_Form_Function_Code
409 RETURN VARCHAR2;
410
411 FUNCTION Get_Tab_Fun_Fa_Prop
412 (
413 p_tab_id IN NUMBER
414 , p_tab_view_id IN NUMBER
415 , p_type IN VARCHAR
416 ) RETURN VARCHAR2;
417
418 FUNCTION get_function_name_string (
419 p_tab_id IN NUMBER
420 ,p_tab_view_id IN NUMBER
421 ) RETURN VARCHAR2 ;
422
423
424
425 END BSC_CUSTOM_VIEW_UI_WRAPPER; -- Package spec
426