1 PACKAGE BODY bis_setup_kpi_pkg AS
2 /* $Header: BISKPISB.pls 120.1 2006/01/17 03:30:46 aguwalan noship $ */
3 version CONSTANT VARCHAR2(80) := '$Header: BISKPISB.pls 120.1 2006/01/17 03:30:46 aguwalan noship $';
4
5 FUNCTION getValue(
6 p_key IN VARCHAR2
7 ,p_parameters IN VARCHAR2
8 ,p_delimiter IN VARCHAR2 := c_amp
9 ) RETURN VARCHAR2
10 IS
11 l_key VARCHAR2(2000);
12 l_parameters VARCHAR2(2000);
13 l_key_start NUMBER;
14 l_value_start NUMBER;
15 l_amp_start NUMBER;
16
17 l_val VARCHAR2(2000);
18 BEGIN
19 IF ( (p_key IS NULL) or (p_parameters IS NULL)) THEN
20 RETURN NULL;
21 END IF;
22
23 l_key := UPPER(p_key);
24 l_parameters := UPPER(p_parameters);
25 -- dbms_output.put_line('p_parameters='|| p_parameters);
26 -- first occurance
27 l_key_start := INSTRB(l_parameters, RTRIM(l_key)|| c_eq, 1);
28 -- dbms_output.put_line('l key start='||l_key_start);
29 IF (l_key_start = 0) THEN -- key not found
30 RETURN NULL;
31 END IF;
32
33 -- get the starting position of v2 in "p2=v2"
34 l_value_start := l_key_start + LENGTHB(p_key)+1; -- including c_eq
35 l_amp_start := INSTRB(p_parameters, p_delimiter, l_value_start);
36
37 IF (l_amp_start = 0) THEN -- the last one or key not found
38 l_val := SUBSTRB(p_parameters, l_value_start);
39 ELSE
40 l_val := SUBSTRB(p_parameters, l_value_start, (l_amp_start - l_value_start));
41 END IF;
42 RETURN l_val;
43
44 EXCEPTION
45 WHEN OTHERS THEN
46 RETURN NULL;
47
48 END getValue;
49
50 -- note that we only take seeded OA pages in RSG into account
51 -- the following function is changed to procedure due to 9i dependency
52 -- problem. Now instead of returning a PL/SQL table, we will be using
53 -- temporary global table, which is in a DB session scope
54 -- FUNCTION getKpisAndPages RETURN t_bis_kpi_page_tab IS
55 /* This API is not used with current Administer KPI UI; and the query used is coming up
56 in the APPSPERF: R12 bug#4912250. Hence commenting this API
57 PROCEDURE getKpisAndPages IS
58 CURSOR c_pages IS
59 SELECT distinct kpi_funcs.function_id, kpi_funcs.parameters,
60 pages.page_name, pages.page_internal_name
61 FROM
62 (SELECT page_funcs.user_function_name page_name,
63 prop.object_name page_internal_name, menus.menu_id page_menu_id
64 FROM bis_obj_properties prop, fnd_form_functions_vl page_funcs, fnd_menus menus
65 WHERE prop.object_type = 'PAGE'
66 --AND prop.object_name like '%_OA'
67 AND page_funcs.type = 'JSP'
68 AND UPPER(page_funcs.web_html_call) like 'OA.JSP?AKREGIONCODE=BIS_COMPONENT_PAGE'||'&'||'AKREGIONAPPLICATIONID=191%'
69 AND UPPER(page_funcs.parameters) LIKE '%PAGENAME%'
70 --AND page_funcs.FUNCTION_NAME = SUBSTR(prop.object_name, 1, LENGTH(prop.object_name)-3)
71 AND page_funcs.function_name = bis_impl_dev_pkg.get_function_by_page(prop.object_name)
72 AND menus.menu_name = getValue('pageName', page_funcs.parameters)
73 ) pages,
74 fnd_form_functions kpi_portlet_funcs,
75 fnd_menus kpi_portlet_menus,
76 fnd_form_functions kpi_funcs
77 WHERE kpi_portlet_funcs.type in ('WEBPORTLET','WEBPORTLETX')
78 AND (UPPER(kpi_portlet_funcs.parameters) LIKE '%PMEASUREDEFINITION%'
79 OR UPPER(kpi_portlet_funcs.parameters) LIKE '%PXMLDEFINITION%')
80 AND UPPER(kpi_portlet_funcs.web_html_call) LIKE 'OA.JSP?AKREGIONCODE=BIS_PMF_PORTLET_TABLE_LAYOUT'||'&'||'AKREGIONAPPLICATIONID=191%'
81 AND kpi_portlet_funcs.function_id IN (SELECT entries.function_id
82 FROM fnd_menu_entries entries
83 START WITH entries.menu_id = pages.page_menu_id
84 CONNECT BY PRIOR entries.sub_menu_id = entries.menu_id)
85 AND kpi_portlet_menus.menu_name = Nvl(getValue('pMeasureDefinition', kpi_portlet_funcs.parameters), getValue('pXMLDefinition', kpi_portlet_funcs.parameters))
86 AND kpi_funcs.type <> 'DBPORTLET'
87 AND kpi_funcs.type <> 'WEBPORTLET'
88 and kpi_funcs.type<>'WEBPORTLETX'
89 AND UPPER(kpi_funcs.parameters) LIKE '%PTARGETLEVELSHORTNAME%'
90 AND kpi_funcs.function_id IN (SELECT entries1.function_id
91 FROM fnd_menu_entries entries1
92 START WITH entries1.menu_id = kpi_portlet_menus.menu_id
93 CONNECT BY PRIOR entries1.sub_menu_id = entries1.menu_id);
94
95 CURSOR c_kpis(p_func_param VARCHAR2) IS
96 SELECT tl.indicator_id, indicators.name indicator_name
97 FROM bis_target_levels tl, bis_indicators_vl indicators
98 WHERE tl.short_name = getValue('pTargetLevelShortName', p_func_param)
99 AND nvl(Upper(getValue('pHide', p_func_param)), 'NO') <> 'YES'
100 AND tl.indicator_id = indicators.indicator_id;
101
102 v_page c_pages%ROWTYPE;
103 v_kpi c_kpis%ROWTYPE;
104 v_page_kpi t_bis_kpi_page_obj;
105
106 BEGIN
107 --dbms_output.put_line('g_populated: '||g_populated);
108 IF (g_populated = 'Y') THEN
109 --RETURN g_kpis_pages;
110 RETURN;
111 ELSE
112 execute immediate 'delete from BIS_SETUP_KPI_PAGE';
113 --IF (g_kpis_pages IS NOT NULL AND g_kpis_pages.COUNT > 0) THEN
114 -- g_kpis_pages.DELETE;
115 --END IF;
116 END IF;
117
118 --dbms_output.put_line('execute complicated queries');
119
120 OPEN c_pages;
121
122 --dbms_output.put_line('after executing complicated queries');
123
124 LOOP
125 FETCH c_pages INTO v_page;
126 EXIT WHEN c_pages%NOTFOUND;
127
128 OPEN c_kpis(v_page.parameters);
129 FETCH c_kpis INTO v_kpi;
130 CLOSE c_kpis;
131
132 IF (v_kpi.indicator_id IS NOT NULL) THEN
133 --v_page_kpi := t_bis_kpi_page_obj(v_page.page_name, v_page.page_internal_name, v_kpi.indicator_id, v_kpi.indicator_name);
134 --g_kpis_pages.extend;
135 --g_kpis_pages(g_kpis_pages.COUNT) := v_page_kpi;
136 execute immediate 'insert into BIS_SETUP_KPI_PAGE values (:1, :2, :3, :4)'
137 using v_page.page_name, v_page.page_internal_name,
138 v_kpi.indicator_id, v_kpi.indicator_name;
139
140 --execute immediate 'insert into ywu_debug_bis_setup_kpi_page (:1, :2, :3, :4)'
141 -- using v_page.page_name, v_page.page_internal_name,
142 -- v_kpi.indicator_id, v_kpi.indicator_name;
143 --COMMIT;
144 END IF;
145 END LOOP;
146
147 CLOSE c_pages;
148
149 g_populated := 'Y';
150
151 --RETURN g_kpis_pages;
152 EXCEPTION
153 WHEN OTHERS THEN
154 --dbms_output.put_line('exception thrown in getKpisAndPages:' || sqlerrm);
155 -- RETURN NULL;
156 RETURN;
157 END getKpisAndPages;
158 */
159
160
161 --new implementation
162 FUNCTION getPagesAndImplFlag(
163 p_kpi_id IN NUMBER
164 ) RETURN VARCHAR2
165 IS
166 CURSOR c_pages_and_impl_flag(p_c_kpi_id NUMBER) IS
167 SELECT distinct b.IMPLEMENTATION_FLAG, a.page_name
168 --FROM TABLE(cast(getKpisAndPages AS t_bis_kpi_page_tab)) a,
169 FROM bis_setup_kpi_page a,
170 bis_obj_properties b
171 WHERE a.indicator_id = p_c_kpi_id
172 AND a.page_internal_name = b.object_name
173 AND b.object_type = 'PAGE';
174
175 v_pages VARCHAR2(4000);
176 v_impl_flag CHAR(1) := 'N';
177 v_pages_and_impl_flag_rec c_pages_and_impl_flag%ROWTYPE;
178 BEGIN
179 IF (p_kpi_id IS NULL) THEN
180 RETURN NULL;
181 END IF;
182
183 --initialize BIS_SETUP_KPI_PAGE
184 --dbms_output.put_line('before calling getkpisandpages ');
185 --getkpisandpages;
186
187 --dbms_output.put_line('after calling getkpisandpages ');
188
189 OPEN c_pages_and_impl_flag(p_kpi_id);
190
191 LOOP
192 FETCH c_pages_and_impl_flag INTO v_pages_and_impl_flag_rec;
193 EXIT WHEN c_pages_and_impl_flag%NOTFOUND;
194 IF (v_pages_and_impl_flag_rec.implementation_flag = 'Y') THEN
195 v_impl_flag := 'Y';
196 END IF;
197 IF (Length(v_pages) > 0) THEN
198 v_pages := v_pages || ',' || v_pages_and_impl_flag_rec.page_name;
199 ELSE
200 v_pages := v_pages || v_pages_and_impl_flag_rec.page_name;
201 END IF;
202 END LOOP;
203 CLOSE c_pages_and_impl_flag;
204
205 IF (v_impl_flag = 'Y') THEN
206 v_impl_flag := 'N';
207 ELSE
208 v_impl_flag := 'Y';
209 END IF;
210
211 IF (Length(v_pages) > 0) THEN
212 RETURN 'pages=' || v_pages || '&' || 'implFlag=' || v_impl_flag;
213 END IF;
214
215 RETURN NULL;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 RETURN NULL;
220 END getPagesAndImplflag;
221 -- new implementation
222
223
224
225
226 --new implementation
227 -- get rid of 9i dependency
228 --FUNCTION getKpis(p_page_name IN VARCHAR2) RETURN t_bis_setup_kpi_tab
229 PROCEDURE getkpis(p_page_name IN VARCHAR2)
230 IS
231 CURSOR c_kpis(p_c_page_name VARCHAR2) IS
232 SELECT DISTINCT indicator_id, indicator_name
233 --FROM TABLE(cast(getKpisAndPages AS t_bis_kpi_page_tab))
234 FROM BIS_SETUP_KPI_PAGE
235 WHERE Upper(page_name) LIKE Upper(p_c_page_name);
236
237 v_kpi t_bis_setup_kpi_obj := t_bis_setup_kpi_obj(-1, '');
238 v_ret_kpis t_bis_setup_kpi_tab := t_bis_setup_kpi_tab();
239 v_index INTEGER;
240
241 BEGIN
242
243 execute immediate 'delete from BIS_SETUP_KPI';
244
245 IF (p_page_name IS NULL) THEN
246 --RETURN NULL;
247 RETURN;
248 END IF;
249
250 --initialize BIS_SETUP_KPI_PAGE
251 --getkpisandpages;
252
253 --dbms_output.put_line('before cleaning up BIS_SETUP_KPI');
254 -- clear up the global temporary table
255 --dbms_output.put_line('after cleaning up BIS_SETUP_KPI');
256
257 OPEN c_kpis(p_page_name);
258
259 LOOP
260 FETCH c_kpis INTO v_kpi.kpi_id, v_kpi.kpi_name;
261 EXIT WHEN c_kpis%notfound;
262
263 IF v_kpi.kpi_id IS NOT NULL THEN
264 --v_ret_kpis.extend;
265 --v_ret_kpis(v_ret_kpis.count) := v_kpi;
266 execute immediate 'insert into BIS_SETUP_KPI values (:1, :2)'
267 using v_kpi.kpi_id, v_kpi.kpi_name;
268
269 --execute immediate 'insert into ywu_debug_bis_setup_kpi values (:1, :2)'
270 -- using v_kpi.kpi_id, v_kpi.kpi_name;
271 --COMMIT;
272 END IF;
273 END LOOP;
274
275 CLOSE c_kpis;
276
277 --RETURN v_ret_kpis;
278 RETURN;
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 --RETURN NULL;
283 --dbms_output.put_line('Exception thrown in getKpis');
284 RETURN;
285 END getkpis;
286 -- new implementation
287
288
289 PROCEDURE invalidatecache IS
290 BEGIN
291 g_populated := 'N';
292 RETURN;
293 END invalidatecache;
294
295 END bis_setup_kpi_pkg;
296
[Home] [Help]