[Home] [Help]
PACKAGE BODY: APPS.BIS_JAVASCRIPTLOV_PVT
Source
1 PACKAGE BODY BIS_JAVASCRIPTLOV_PVT AS
2 /* $Header: BISVJLOB.pls 120.1 2006/04/10 07:55:50 psomesul noship $ */
3 -- +==========================================================================+
4 -- | Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA |
5 -- | All rights reserved. |
6 -- +==========================================================================+
7 -- | FILENAME |
8 -- | BISVJLOB.pls |
9 -- | |
10 -- | DESCRIPTION |
11 -- | Body of Javascript LOV |
12 -- | NOTES |
13 -- | |
14 -- | HISTORY |
15 -- | |
16 -- |21-Mar-2001 mdamle Created |
17 -- |24-APR-2003 sugopal Made changes so that bislovwn.jsp uses bind variables
18 -- | instead of literals by constructing the sql with a
19 -- | bind variable in getLOVSQL for bug#2917806
20 -- |20-AUG-2003 rchandra Enh 2972146 ; added the where clause to link the
21 -- | bis_indicators and bsc_sys_datasets_b thru datasetid
22 -- | in getLOVsql function for Measure type lovs
23 -- |16-MAR-2004 ankgoel Modified for bug# 3463573 |
24 -- | 22-APR-2005 akoduri Enhancement#3865711 -- Obsolete Seeded Objects |
25 -- | 10-APR-05 psomesul Bug#5140269 - PERFORMANCE ISSUE WITH TARGET OWNER |
26 -- | LOV IN PMF PAGES - replaced WF_ROLES with WF_ROLE_LOV_VL |
27 -- +==========================================================================+
28
29
30 PROCEDURE showLOV (p_lov_type in varchar2
31 ,p_form_name in varchar2
32 ,p_param_field in varchar2
33 ,p_param_id_field in varchar2 default NULL
34 ,p_filter in varchar2 default ''
35 ,p_parameter1 in varchar2 default NULL
36 ,p_parameter2 in varchar2 default NULL
37 ,p_parameter3 in varchar2 default NULL
38 ,p_parameter4 in varchar2 default NULL
39 ,p_parameter5 in varchar2 default NULL
40 ,p_callback_function in varchar2 default NULL) IS
41
42 type c1_cur_type is ref cursor;
43 c1 c1_cur_type;
44 l_id varchar2(1000);
45 l_value varchar2(1000);
46 l_lov_sql varchar2(32000);
47 l_callback_function varchar2(1000);
48 BEGIN
49 htp.htmlOpen;
50 htp.headOpen;
51 htp.title('Valid Values');
52
53 if p_callback_function is null then
54 l_callback_function := '';
55 else
56 l_callback_function := 'self.opener.'||p_callback_function||';';
57 end if;
58
59 -- javascript functions
60 htp.print('<script language="JavaScript">');
61 htp.print('function closeLovWindow(fp_value, fp_id) {
62 self.opener.document.'||p_form_name||'.'||p_param_field||'.value = fp_value;
63 if (fp_id != "")
64 self.opener.document.'||p_form_name||'.'||p_param_id_field||'.value = fp_id; '||
65 l_callback_function||'
66 parent.self.close()}');
67 htp.print('</script>');
68
69 htp.headClose;
70 htp.bodyOpen;
71
72
73 l_lov_sql := getLOVSQL(p_lov_type, p_filter, p_parameter1, p_parameter2, p_parameter3, p_parameter4, p_parameter5);
74
75 open c1 for l_lov_sql;
76 loop
77 fetch c1 into l_id, l_value;
78 exit when c1%notfound;
79
80 if (p_param_id_field is null) then
81 htp.print('<a href="javascript:closeLovWindow('||''''||replace(l_value, '''', '\''') ||''', '''')">');
82 else
83 htp.print('<a href="javascript:closeLovWindow('||''''||replace(l_value, '''', '\''') ||''','||''''||l_id||''')">');
84 end if;
85 htp.print(l_value);
86 htp.print('</a>');
87 htp.br;
88 end loop;
89
90 close c1;
91 htp.bodyClose;
92 htp.htmlClose;
93
94 END showLOV;
95
96 FUNCTION getLOVSQL (p_lov_type in varchar2
97 ,p_filter in varchar2 default ''
98 ,p_parameter1 in varchar2 default NULL
99 ,p_parameter2 in varchar2 default NULL
100 ,p_parameter3 in varchar2 default NULL
101 ,p_parameter4 in varchar2 default NULL
102 ,p_parameter5 in varchar2 default NULL) return varchar IS
103
104 l_lov_sql varchar2(32000);
105 l_where_clause varchar2(32000);
106 l_temp varchar2(1000);
107 l_temp1 varchar2(1000);
108
109 l_table_name varchar2(1000);
110 l_id_name varchar2(1000);
111 l_value_name varchar2(1000);
112 l_time_level varchar2(1000);
113 l_return_status varchar2(1000);
114 l_msg_count varchar2(1000);
115 l_msg_data varchar2(32000);
116 -- 2359096
117 CURSOR c_source( cp_dim_level_short_name IN VARCHAR2) IS
118 SELECT source
119 FROM bis_levels WHERE short_name = cp_dim_level_short_name ;
120
121 l_source bis_levels.source%TYPE;
122 --2359096
123 l_meas_src VARCHAR2(10) := 'PMF';
124 BEGIN
125 -- Add your SQL here
126 -- Add your LOV Type constants in the Package Specification
127 -- p_filter can be used as the initial filter
128 -- p_parameter1...p_parameter5 can be used in your where clause for additional filtering if necessary
129
130 if (p_lov_type = G_PERF_MEAS_S_LOV) or (p_lov_type = G_PERF_MEAS_R_LOV) then
131
132 l_lov_sql := 'select measure_id, measure_name from bisfv_performance_measures perfMeasuresList, BSC_SYS_DATASETS_B ds WHERE perfMeasuresList.dataset_id = ds.dataset_id AND perfMeasuresList.Obsolete = ''F'' AND ds.source = '||'''' || l_meas_src ||'''';
133 if p_lov_type = G_PERF_MEAS_S_LOV then
134 if p_parameter1 is not null then
135 l_where_clause := bis_utilities_pvt.perf_measure_where_clause(p_parameter1);
136 end if;
137 if l_where_clause is null then
138 l_lov_sql := l_lov_sql || ' AND upper(measure_name) like ' || 'replace('|| ':1' || ',' || '''''''''' || ',' || '''''''''''''' || ') ';
139 else
140 l_lov_sql := l_lov_sql || ' AND ' || l_where_clause || ' and upper(measure_name) like ' || 'replace('|| ':1' || ',' || '''''''''' || ',' || '''''''''''''' || ') ';
141 end if;
142 else
143 l_lov_sql := l_lov_sql || ' AND upper(measure_name) like ' || 'replace('|| ':1' || ',' || '''''''''' || ',' || '''''''''''''' || ') ';
144 end if;
145 l_lov_sql := l_lov_sql || ' order by measure_name ';
146
147 end if;
148
149 if (p_lov_type = G_TARGET_LEVEL_S_LOV) or (p_lov_type = G_TARGET_LEVEL_R_LOV)then
150 l_temp := ' nvl(dimension1_level_name, '''') ||
151 decode(nvl(dimension2_level_name, ''''), '''', '''', '';''||dimension2_level_name) ||
152 decode(nvl(dimension3_level_name, ''''), '''', '''', '';''||dimension3_level_name) ||
153 decode(nvl(dimension4_level_name, ''''), '''', '''', '';''||dimension4_level_name) ||
154 decode(nvl(dimension5_level_name, ''''), '''', '''', '';''||dimension5_level_name) ||
155 decode(nvl(dimension6_level_name, ''''), '''', '''', '';''||dimension6_level_name) ||
156 decode(nvl(dimension7_level_name, ''''), '''', '''', '';''||dimension7_level_name) ';
157
158
159
160 l_temp1 := ' nvl(upper(dimension1_level_name), '''') ||
161 decode(nvl(upper(dimension2_level_name), ''''), '''', '''', '';''||upper(dimension2_level_name)) ||
162 decode(nvl(upper(dimension3_level_name), ''''), '''', '''', '';''||upper(dimension3_level_name)) ||
163 decode(nvl(upper(dimension4_level_name), ''''), '''', '''', '';''||upper(dimension4_level_name)) ||
164 decode(nvl(upper(dimension5_level_name), ''''), '''', '''', '';''||upper(dimension5_level_name)) ||
165 decode(nvl(upper(dimension6_level_name), ''''), '''', '''', '';''||upper(dimension6_level_name)) ||
166 decode(nvl(upper(dimension7_level_name), ''''), '''', '''', '';''||upper(dimension7_level_name)) ';
167
168 /*l_temp1 := ' nvl(upper(dimension1_level_name), '''') ||
169 nvl(upper(dimension2_level_name), '''') ||
170 nvl(upper(dimension3_level_name), '''') ||
171 nvl(upper(dimension4_level_name), '''') ||
172 nvl(upper(dimension5_level_name), '''') ||
173 nvl(upper(dimension6_level_name), '''') ||
174 nvl(upper(dimension7_level_name), '''') ';*/
175
176 l_lov_sql := 'select target_level_id || ''+'' || dimension1_level_id || ''+'' || dimension2_level_id || ''+'' || dimension3_level_id
177 || ''+'' || dimension4_level_id || ''+'' || dimension5_level_id || ''+'' || dimension6_level_id || ''+'' || dimension7_level_id, '
178 || l_temp || '
179 from bisfv_target_levels ';
180
181 l_lov_sql := l_lov_sql || ' where ';
182
183 if p_parameter2 is not null then
184 l_lov_sql := l_lov_sql || ' measure_id='||p_parameter2 || ' and ';
185 end if;
186
187 /* p_lov_type = G_TARGET_LEVEL_S_LOV is no more used after bug #3448500 */
188 if p_lov_type = G_TARGET_LEVEL_S_LOV then
189 if p_parameter1 is not null then
190 l_where_clause := bis_utilities_pvt.target_level_where_clause(p_parameter1);
191 end if;
192
193 if l_where_clause is null then
194 l_lov_sql := l_lov_sql || l_temp1 || ' like ' || 'replace('|| ':1' || ',' || '''''''''' || ',' || '''''''''''''' || ') ';
195 else
196 l_lov_sql := l_lov_sql || l_where_clause || ' and ' || l_temp1 || ' like ' || 'replace('|| ':1' || ',' || '''''''''' || ',' || '''''''''''''' || ') ';
197 end if;
198 else
199 l_lov_sql := l_lov_sql || l_temp1 || ' like ' || 'replace('|| ':1' || ',' || '''''''''' || ',' || '''''''''''''' || ') ';
200 end if;
201 --if p_parameter2 is not null then
202 -- l_lov_sql := l_lov_sql || ' and measure_id='||p_parameter2;
203 --end if;
204 l_lov_sql := l_lov_sql || ' order by dimension1_level_name, dimension2_level_name, dimension3_level_name, dimension4_level_name,
205 dimension5_level_name, dimension6_level_name, dimension7_level_name ';
206 end if;
207
208
209 IF (p_lov_type = G_OWNERS_LOV) THEN
210 l_lov_sql := 'SELECT name, display_name FROM WF_ROLE_LOV_VL ';
211 l_lov_sql := l_lov_sql || ' WHERE upper(display_name) like ' || 'replace('|| ':1' || ',' || '''''''''' || ',' || '''''''''''''' || ') ';
212 l_lov_sql := l_lov_sql || ' ORDER BY display_name ';
213 END IF;
214
215 if (p_lov_type = G_RESPS_LOV) then
216 l_lov_sql := ' select responsibility_id, responsibility_name ' ||
217 ' from fnd_responsibility_vl where upper(responsibility_name) like ' || 'replace('|| ':1' || ',' || '''''''''' || ',' || '''''''''''''' || ') ';
218 l_lov_sql := l_lov_sql || ' ORDER BY responsibility_name ';
219 end if;
220
221 -- for Time LOV
222 if (p_lov_type = 'TIME') then
223
224 if p_parameter1 is not null then
225
226 BIS_PMF_GET_DIMLEVELS_PVT.GET_DIMLEVEL_SELECT_STRING
227 (p_DimLevelName => p_parameter1
228 ,x_Select_String => l_lov_sql
229 ,x_table_name => l_table_name
230 ,x_id_name => l_id_name
231 ,x_value_name => l_value_name
232 ,x_time_level => l_time_level
233 ,x_return_status => l_return_status
234 ,x_msg_count => l_msg_count
235 ,x_msg_data => l_msg_data);
236
237
238 end if;
239
240 if p_parameter1 is not null and p_parameter2 is not null and p_parameter3 is not null and l_lov_sql is not null then
241 /* 2359096
242 bis_utilities_pub.retrieve_Time_where_clause
243 (p_time_dim_level_short_name =>p_parameter1
244 ,p_org_dim_level_short_name=>p_parameter2
245 ,p_org_id=>p_parameter3
246 ,x_where_clause=>l_where_clause);
247 2359096 */
248 --2359096
249
250 OPEN c_source( cp_dim_level_short_name => p_parameter1);
251 FETCH c_source INTO l_source;
252 CLOSE c_source;
253
254 bis_utilities_pub.get_Time_where_clause(
255 p_dim_level_short_name => p_parameter1
256 ,p_parent_level_short_name => p_parameter2
257 ,p_parent_level_id => p_parameter3
258 ,p_source => l_source
259 ,x_where_clause => l_where_clause
260 ,x_return_status => l_return_status
261 ,x_err_count => l_msg_count
262 ,x_errorMessage => l_msg_data
263 );
264 --2359096
265
266 if (l_where_clause is null) or (l_where_clause='""') then
267 l_lov_sql := l_lov_sql || ' where upper(' || l_value_name || ') like ' || 'replace('|| ':1' || ',' || '''''''''' || ',' || '''''''''''''' || ') ';
268 else
269 l_lov_sql := l_lov_sql || ' where '|| l_where_clause || ' and upper(' || l_value_name || ') like ' || 'replace('|| ':1' || ',' || '''''''''' || ',' || '''''''''''''' || ') ';
270 end if;
271
272 -- end if;
273 else
274 if l_lov_sql is not null then
275 l_lov_sql := l_lov_sql || ' where upper(' || l_value_name || ') like ' || 'replace('|| ':1' || ',' || '''''''''' || ',' || '''''''''''''' || ') ';
276 end if;
277 end if;
278 end if;
279
280
281 return l_lov_sql;
282
283 END getLOVSQL;
284
285 END BIS_JAVASCRIPTLOV_PVT;