[Home] [Help]
PACKAGE BODY: APPS.POR_LOV_PKG
Source
1 PACKAGE BODY POR_LOV_PKG AS
2 /* $Header: PORLOVB.pls 115.6 2002/11/22 06:42:17 ssuri ship $ */
3
4 PROCEDURE REMOVE_QUERY_RESULT(
5 p_session_id IN NUMBER
6 ) is
7
8 BEGIN
9
10 DELETE FROM POR_LOV_DISPLAY_RESULTS WHERE SESSION_ID = p_session_id;
11 COMMIT;
12
13 DELETE FROM POR_LOV_RESULT_VALUES WHERE SESSION_ID = p_session_id;
14 COMMIT;
15
16
17 EXCEPTION
18 WHEN OTHERS THEN
19 RAISE;
20 END REMOVE_QUERY_RESULT;
21
22
23 PROCEDURE EXEC_AK_QUERY(
24 p_session_id IN NUMBER,
25 p_region_app_id IN NUMBER,
26 p_region_code IN VARCHAR2,
27 p_attribute_app_id IN NUMBER,
28 p_attribute_code IN VARCHAR2,
29 p_query_column IN VARCHAR2 default null,
30 p_query_text IN VARCHAR2 default null,
31 c_1 in varchar2 default 'DSTART',
32 p_where_clause IN VARCHAR2 default null,
33 p_js_where_clause IN VARCHAR2 default null,
34 p_start_row in number default 1,
35 p_end_row in number default null,
36 p_case_sensitive IN VARCHAR2 default 'off',
37 p_display_column OUT NOCOPY NUMBER,
38 p_value_column OUT NOCOPY NUMBER,
39 p_total_row OUT NOCOPY NUMBER
40 ) is
41
42
43
44 l_responsibility_id number;
45 l_responsibility_app_id number;
46 l_user_id number;
47 l_LOV_foreign_key_name varchar2(30);
48 l_LOV_region_id number;
49 l_LOV_region varchar2(30);
50
51 l_query_binds ak_query_pkg.bind_tab;
52 l_where_clause varchar2(2000);
53 l_order_clause varchar2(2000);
54 c_where_clause varchar2(2000);
55
56 l_cursor number;
57 l_result_row_table icx_util.char240_table;
58 l_display_column number;
59
60 l_insert_values varchar2(4000);
61 l_column_names varchar2(2000);
62
63 l_query_size number;
64 l_max_rows number;
65 l_end_row number;
66
67 where_clause varchar2(2000);
68 tmp_string varchar2(2000);
69 tmp_num number;
70 i number;
71 j number;
72 temp_column varchar2(30);
73 temp_attribute varchar2(50);
74 temp_type varchar2(1);
75
76
77 cursor lov_query_columns is
78 select d.COLUMN_NAME,b.ATTRIBUTE_LABEL_LONG,
79 substr(a.DATA_TYPE,1,1)
80 from AK_ATTRIBUTES a,
81 AK_REGION_ITEMS_VL b,
82 AK_REGIONS c,
83 AK_OBJECT_ATTRIBUTES d
84 where b.REGION_APPLICATION_ID = l_LOV_region_id
85 and b.REGION_CODE = l_LOV_region
86 and b.NODE_QUERY_FLAG = 'Y'
87 and b.ATTRIBUTE_APPLICATION_ID = d.ATTRIBUTE_APPLICATION_ID
88 and b.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
89 and b.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
90 and b.REGION_CODE = c.REGION_CODE
91 and c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
92 and d.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
93 and d.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
94 and d.ATTRIBUTE_APPLICATION_ID = a.ATTRIBUTE_APPLICATION_ID
95 and d.ATTRIBUTE_CODE = a.ATTRIBUTE_CODE
96 order by b.DISPLAY_SEQUENCE;
97
98
99 BEGIN
100
101 DELETE FROM POR_LOV_DISPLAY_RESULTS WHERE SESSION_ID = p_session_id;
102 COMMIT;
103
104 DELETE FROM POR_LOV_RESULT_VALUES WHERE SESSION_ID = p_session_id;
105 COMMIT;
106
107
108 select USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID
109 into l_user_id, l_responsibility_id, l_responsibility_app_id
110 from ICX_SESSIONS
111 where SESSION_ID = p_session_id;
112
113 fnd_global.APPS_INITIALIZE(l_user_id, l_responsibility_id, l_responsibility_app_id);
114
115 -- Look up the LOV region being called
116 select LOV_FOREIGN_KEY_NAME, LOV_REGION_APPLICATION_ID, LOV_REGION_CODE
117 into l_LOV_foreign_key_name, l_LOV_region_id, l_LOV_region
118 from AK_REGION_ITEMS
119 where REGION_APPLICATION_ID = p_region_app_id
120 and REGION_CODE = p_region_code
121 and ATTRIBUTE_APPLICATION_ID = p_attribute_app_id
122 and ATTRIBUTE_CODE = p_attribute_code;
123
124
125 -- Look up the number of rows to display
126 select QUERY_SET, MAX_ROWS
127 into l_query_size, l_max_rows
128 from ICX_PARAMETERS;
129 /* Added session_id to icx_call wrto bug 2675309 **/
130 /* suggested by Neal **/
131 /* This avoids any mod_sql commands when invoked from java **/
132
133 -- Combine two where clauses
134 if p_where_clause is not null then
135 if p_js_where_clause is not null then
136 c_where_clause := icx_call.encrypt2(icx_call.decrypt2(p_where_clause,p_session_id)||' and '||replace(p_js_where_clause,'^@~^',' '),p_session_id);
137 else
138 c_where_clause := c_where_clause;
139 end if;
140 else
141 if p_js_where_clause is not null then
142 c_where_clause := icx_call.encrypt2(replace(p_js_where_clause,'^@~^',' '),p_session_id);
143 end if;
144 end if;
145
146 -- Perform Object Navigator query
147
148 -- Call whereSegment to construct where clause
149 if p_case_sensitive = 'on' then
150 where_clause := icx_on_utilities.whereSegment
151 (a_1 => p_query_column,
152 c_1 => c_1,
153 i_1 => p_query_text,
154 m => p_case_sensitive);
155 else
156 where_clause := icx_on_utilities.whereSegment
157 (a_1 => p_query_column,
158 c_1 => c_1,
159 i_1 => p_query_text);
160 end if;
161
162
163 -- unpack where clause to use bind variables
164 icx_on_utilities.unpack_whereSegment(where_clause,l_where_clause,l_query_binds);
165
166 /* Added session_id to icx_call wrto bug 2675309 **/
167 /* suggested by Neal **/
168 /* This avoids any mod_sql commands when invoked from java **/
169
170 -- Add where clause LOV parameter to generated where clause
171 if c_where_clause is not null then
172 if l_where_clause is null then
173 l_where_clause := icx_call.decrypt2(c_where_clause,p_session_id);
174 else
175 l_where_clause := l_where_clause||' and '||icx_call.decrypt2(c_where_clause,p_session_id);
176 end if;
177 end if;
178
179 -- Create order clause
180 open lov_query_columns;
181 i := 0;
182 loop
183
184 fetch lov_query_columns into temp_column, temp_attribute, temp_type;
185
186 exit when lov_query_columns%NOTFOUND;
187 i := i + 1;
188 if substr(p_query_column,2,31) = temp_column then
189 l_order_clause := i;
190 exit;
191 end if;
192 end loop;
193 close lov_query_columns;
194
195 -- figure end row value to display */
196 if p_end_row is null then
197 l_end_row := l_query_size;
198 else
199 l_end_row := p_end_row;
200 end if;
201
202
203
204 ak_query_pkg.exec_query (
205 P_PARENT_REGION_APPL_ID => l_LOV_region_id ,
206 P_PARENT_REGION_CODE => l_LOV_region ,
207 P_WHERE_CLAUSE => l_where_clause ,
208 P_WHERE_BINDS => l_query_binds ,
209 P_ORDER_BY_CLAUSE => l_order_clause ,
210 P_RESPONSIBILITY_ID => l_responsibility_id ,
211 P_USER_ID => l_user_id ,
212 P_RETURN_PARENTS => 'T' ,
213 P_RETURN_CHILDREN => 'F' ,
214 P_RANGE_LOW => p_start_row ,
215 P_RANGE_HIGH => l_end_row ,
216 P_MAX_ROWS => 1000);
217
218
219 p_total_row := ak_query_pkg.g_regions_table(0).total_result_count;
220
221 l_insert_values := '';
222 l_column_names := '';
223 l_display_column := 0;
224 j := 0;
225 for j in 0..ak_query_pkg.g_items_table.COUNT-1 loop
226
227 if (l_display_column=0) then
228 l_insert_values := l_insert_values ||
229 ':VALUE' || to_char(j+1);
230 l_column_names := l_column_names || 'VALUE' || to_char(j+1);
231 else
232 l_insert_values := l_insert_values || ', ' ||
233 ':VALUE' || to_char(j+1);
234 l_column_names := l_column_names ||', ' || 'VALUE' || to_char(j+1);
235 end if;
236 l_display_column := l_display_column + 1;
237 end loop;
238
239 l_column_names := '(SESSION_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE, TYPE,' || l_column_names || ')';
240 l_insert_values := '(:SESSION_ID, :LAST_UPDATED_BY, :LAST_UPDATE_DATE, :TYPE, ' || l_insert_values || ')';
241 tmp_string := 'INSERT INTO POR_LOV_RESULT_VALUES ' || l_column_names ||' VALUES ' || l_insert_values;
242
243 l_cursor := dbms_sql.open_cursor;
244 dbms_sql.parse(l_cursor, tmp_string, dbms_sql.v7);
245
246 dbms_sql.bind_variable(l_cursor, ':SESSION_ID', p_session_id);
247 dbms_sql.bind_variable(l_cursor, ':LAST_UPDATED_BY', l_user_id);
248 dbms_sql.bind_variable(l_cursor, ':LAST_UPDATE_DATE', sysdate);
249 dbms_sql.bind_variable(l_cursor, ':TYPE', 'CODE');
250 for j in 0..ak_query_pkg.g_items_table.COUNT-1 loop
251 dbms_sql.bind_variable(l_cursor, ':VALUE'|| to_char(j+1) , ak_query_pkg.g_items_table(j).attribute_code );
252 end loop;
253
254 tmp_num := dbms_sql.execute(l_cursor);
255 dbms_sql.close_cursor(l_cursor);
256
257
258
259 i := 0;
260 j := 0;
261 for i in 0..ak_query_pkg.g_results_table.COUNT-1 loop
262 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(i), l_result_row_table);
263 l_insert_values := '';
264 l_column_names := '';
265 l_display_column := 0;
266 for j in 0..ak_query_pkg.g_items_table.COUNT-1 loop
267
268 if (l_display_column=0) then
269 l_insert_values := l_insert_values || ':VALUE' || to_char(j+1);
270 l_column_names := l_column_names || 'VALUE' || to_char(j+1);
271 else
272 l_insert_values := l_insert_values || ', ' || ':VALUE' || to_char(j+1);
273 l_column_names := l_column_names ||', ' || 'VALUE' || to_char(j+1);
274
275 end if;
276
277 l_display_column := l_display_column + 1;
278 end loop;
279
280 l_column_names := '(SESSION_ID, SEQUENCE, LAST_UPDATED_BY, LAST_UPDATE_DATE, TYPE, ' || l_column_names || ')';
281 l_insert_values := '(:SESSION_ID, :SEQUENCE, :LAST_UPDATED_BY, :LAST_UPDATE_DATE, :TYPE,' || l_insert_values ||')';
282 tmp_string := 'INSERT INTO POR_LOV_RESULT_VALUES ' || l_column_names ||' VALUES ' || l_insert_values;
283
284 l_cursor := dbms_sql.open_cursor;
285 dbms_sql.parse(l_cursor, tmp_string, dbms_sql.v7);
286
287 dbms_sql.bind_variable(l_cursor, ':SESSION_ID', p_session_id);
288 dbms_sql.bind_variable(l_cursor, ':SEQUENCE', i);
289 dbms_sql.bind_variable(l_cursor, ':LAST_UPDATED_BY', l_user_id);
290 dbms_sql.bind_variable(l_cursor, ':LAST_UPDATE_DATE', sysdate);
291 dbms_sql.bind_variable(l_cursor, ':TYPE', 'VALUE');
292 for j in 0..ak_query_pkg.g_items_table.COUNT-1 loop
293 dbms_sql.bind_variable(l_cursor, ':VALUE' || to_char(j+1), l_result_row_table(ak_query_pkg.g_items_table(j).value_id));
294 end loop;
295
296 tmp_num := dbms_sql.execute(l_cursor);
297 dbms_sql.close_cursor(l_cursor);
298
299 end loop;
300
301 p_value_column := l_display_column;
302
303 l_insert_values := '';
304 l_column_names := '';
305 l_display_column := 0;
306 j := 0;
307 for j in 0..ak_query_pkg.g_items_table.COUNT-1 loop
308 if ak_query_pkg.g_items_table(j).secured_column = 'F' and
309 ak_query_pkg.g_items_table(j).node_display_flag = 'Y' then
310
311 if (l_display_column=0) then
312 l_insert_values := l_insert_values || ':VALUE' || to_char(j+1);
313 l_column_names := l_column_names || 'VALUE' || to_char(j+1);
314 else
315 l_insert_values := l_insert_values || ', ' || ':VALUE' || to_char(j+1);
316 l_column_names := l_column_names ||', ' || 'VALUE' || to_char(j+1);
317 end if;
318 l_display_column := l_display_column + 1;
319 end if;
320 end loop;
321
322 l_column_names := '(SESSION_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE, TYPE, ' || l_column_names || ')';
323 l_insert_values := '(:SESSION_ID, :LAST_UPDATED_BY, :LAST_UPDATE_DATE, :TYPE, ' || l_insert_values || ')';
324 tmp_string := 'INSERT INTO POR_LOV_DISPLAY_RESULTS ' || l_column_names ||' VALUES ' || l_insert_values;
325
326 l_cursor := dbms_sql.open_cursor;
327 dbms_sql.parse(l_cursor, tmp_string, dbms_sql.v7);
328
329 dbms_sql.bind_variable(l_cursor, ':SESSION_ID', p_session_id);
330 dbms_sql.bind_variable(l_cursor, ':LAST_UPDATED_BY', l_user_id);
331 dbms_sql.bind_variable(l_cursor, ':LAST_UPDATE_DATE', sysdate);
332 dbms_sql.bind_variable(l_cursor, ':TYPE', 'TITLE');
333
334 for j in 0..ak_query_pkg.g_items_table.COUNT-1 loop
335 if ak_query_pkg.g_items_table(j).secured_column = 'F' and
336 ak_query_pkg.g_items_table(j).node_display_flag = 'Y' then
337 dbms_sql.bind_variable(l_cursor, ':VALUE' || to_char(j+1), ak_query_pkg.g_items_table(j).attribute_label_long);
338 end if;
339 end loop;
340
341 tmp_num := dbms_sql.execute(l_cursor);
342 dbms_sql.close_cursor(l_cursor);
343
344 i := 0;
345 j := 0;
346 for i in 0..ak_query_pkg.g_results_table.COUNT-1 loop
347 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(i), l_result_row_table);
348 l_insert_values := '';
349 l_column_names := '';
350 l_display_column := 0;
351 for j in 0..ak_query_pkg.g_items_table.COUNT-1 loop
352 if ak_query_pkg.g_items_table(j).secured_column = 'F' and
353 ak_query_pkg.g_items_table(j).node_display_flag = 'Y' then
354
355 if (l_display_column=0) then
356 l_insert_values := l_insert_values || ':VALUE' || to_char(j+1);
357 l_column_names := l_column_names || 'VALUE' || to_char(j+1);
358 else
359 l_insert_values := l_insert_values || ', ' || ':VALUE' || to_char(j+1);
360 l_column_names := l_column_names ||', ' || 'VALUE' || to_char(j+1);
361
362 end if;
363
364 l_display_column := l_display_column + 1;
365 end if;
366 end loop;
367
368 l_column_names := '(SESSION_ID, SEQUENCE, LAST_UPDATED_BY, LAST_UPDATE_DATE, TYPE, ' || l_column_names || ')';
369 l_insert_values := '(:SESSION_ID, :SEQUENCE, :LAST_UPDATED_BY, :LAST_UPDATE_DATE, :TYPE, ' || l_insert_values || ')';
370 tmp_string := 'INSERT INTO POR_LOV_DISPLAY_RESULTS ' || l_column_names ||' VALUES ' || l_insert_values;
371
372 l_cursor := dbms_sql.open_cursor;
373 dbms_sql.parse(l_cursor, tmp_string, dbms_sql.v7);
374
375 dbms_sql.bind_variable(l_cursor, ':SESSION_ID', p_session_id);
376 dbms_sql.bind_variable(l_cursor, ':SEQUENCE', i);
377 dbms_sql.bind_variable(l_cursor, ':LAST_UPDATED_BY', l_user_id);
378 dbms_sql.bind_variable(l_cursor, ':LAST_UPDATE_DATE', sysdate);
379 dbms_sql.bind_variable(l_cursor, ':TYPE', 'VALUE');
380
381 for j in 0..ak_query_pkg.g_items_table.COUNT-1 loop
382 if ak_query_pkg.g_items_table(j).secured_column = 'F' and
383 ak_query_pkg.g_items_table(j).node_display_flag = 'Y' then
384 dbms_sql.bind_variable(l_cursor, ':VALUE' || to_char(j+1),l_result_row_table(ak_query_pkg.g_items_table(j).value_id));
385 end if;
386 end loop;
387
388 tmp_num := dbms_sql.execute(l_cursor);
389 dbms_sql.close_cursor(l_cursor);
390
391 end loop;
392
393 p_display_column := l_display_column;
394
395 EXCEPTION
396 WHEN OTHERS THEN
397 RAISE;
398 END EXEC_AK_QUERY;
399
400
401 END POR_LOV_PKG;