[Home] [Help]
PACKAGE BODY: APPS.ICX_ON
Source
1 package body icx_on as
2 /* $Header: ICXONMB.pls 120.0 2005/10/07 12:16:13 gjimenez noship $ */
3
4 procedure get_page(p_attributes in icx_on_utilities.v80_table,
5 p_conditions in icx_on_utilities.v80_table,
6 p_inputs in icx_on_utilities.v80_table,
7 p_match in varchar2,
8 p_and_or in varchar2) is
9 l_timer number;
10
11 l_type varchar2(30);
12 l_flow_appl_id number(15);
13 l_flow_code varchar2(30);
14 l_page_appl_id number(15);
15 l_page_code varchar2(30);
16 l_start number;
17 l_start_region varchar2(30);
18 l_count number;
19 l_encrypted_where number;
20
21 l_where varchar2(2000);
22 l_Y varchar2(2000);
23 l_help_url varchar2(2000);
24 l_message varchar2(2000);
25 l_err_mesg varchar2(240);
26 l_err_num number;
27 l_web_user_date_format varchar2(240);
28
29 begin
30
31 -- select HSECS into l_timer from v$timer;htp.p('begin get_page @ '||l_timer);htp.nl;
32
33 l_type := icx_on_utilities.g_on_parameters(1);
34
35 if l_type = 'Q1' or l_type = 'Q5'
36 then
37 l_flow_appl_id := icx_on_utilities.g_on_parameters(2);
38 l_flow_code := icx_on_utilities.g_on_parameters(3);
39 l_page_appl_id := icx_on_utilities.g_on_parameters(4);
40 l_page_code := icx_on_utilities.g_on_parameters(5);
41
42 l_Y := icx_call.encrypt2('DQ'||'*'||l_flow_appl_id||'*'||l_flow_code||'*'||l_page_appl_id||'*'||l_page_code||'*****]');
43
44 l_help_url := 'OracleON.IC?X='||icx_call.encrypt2(l_flow_appl_id||'*'||l_flow_code||'*'||l_page_appl_id||'*'||l_page_code||'*'||'ICX_HLP_QUERY'||'**]');
45
46 if ( substr(icx_sec.g_mode_code,1,3) = '115' or
47 icx_sec.g_mode_code = 'SLAVE')
48 then
49 if l_type = 'Q1'
50 then
51 icx_on_cabo.findPage(
52 p_flow_appl_id => l_flow_appl_id,
53 p_flow_code => l_flow_code,
54 p_page_appl_id => l_page_appl_id,
55 p_page_code => l_page_code,
56 p_region_appl_id => '',
57 p_region_code => '',
58 p_lines_now => 1,
59 p_lines_next => 5,
60 p_hidden_name => 'Y',
61 p_hidden_value => l_Y,
62 p_help_url => l_help_url);
63 else
64 icx_on_cabo.findPage(l_flow_appl_id,l_flow_code,l_page_appl_id,l_page_code,'','',5,1,'Y',l_Y,l_help_url);
65 end if;
66 else
67 if l_type = 'Q1'
68 then
69 icx_on_utilities.findPage(l_flow_appl_id,l_flow_code,l_page_appl_id,l_page_code,'','','',1,'',5,'Y',l_Y,l_help_url);
70 else
71 icx_on_utilities.findPage(l_flow_appl_id,l_flow_code,l_page_appl_id,l_page_code,'','','',5,'',1,'Y',l_Y,l_help_url);
72 end if;
73 end if;
74 else
75 if l_type = 'DQ'
76 then
77 l_encrypted_where := icx_on_utilities.g_on_parameters(9);
78
79 if l_encrypted_where is null
80 then
81 l_where := icx_on_utilities.whereSegment(p_attributes,p_conditions,p_inputs,p_match,p_and_or);
82 l_encrypted_where := icx_call.encrypt2(l_where);
83 else
84 l_where := icx_call.decrypt2(l_encrypted_where);
85 end if;
86
87 -- htp.p('DEBUG where => '||l_where);htp.nl;
88
89 icx_on_utilities.g_on_parameters(9) := l_encrypted_where;
90
91 icx_on_utilities.getRegions(l_where);
92 elsif l_type = 'W'
93 then
94 l_start := 1;
95
96 select REGION_CODE
97 into l_start_region
98 from AK_FLOW_PAGE_REGIONS
99 where PAGE_CODE = icx_on_utilities.g_on_parameters(5)
100 and PAGE_APPLICATION_ID = icx_on_utilities.g_on_parameters(4)
101 and FLOW_CODE = icx_on_utilities.g_on_parameters(3)
102 and FLOW_APPLICATION_ID = icx_on_utilities.g_on_parameters(2)
103 and PARENT_REGION_CODE is null;
104
105 -- The parameters(6) is a funny used to pass in a direct where
106 l_where := replace(icx_on_utilities.g_on_parameters(6),'~','=')||'**]';
107 l_encrypted_where := icx_call.encrypt2(l_where);
108
109 -- 2093780 nlbarlow, multiple region first page
110 select count(*)
111 into l_count
112 from AK_FLOW_PAGE_REGIONS
113 where PAGE_CODE = icx_on_utilities.g_on_parameters(5)
114 and PAGE_APPLICATION_ID = icx_on_utilities.g_on_parameters(4)
115 and FLOW_CODE = icx_on_utilities.g_on_parameters(3)
116 and FLOW_APPLICATION_ID = icx_on_utilities.g_on_parameters(2);
117
118 if l_count > 1
119 then
120 icx_on_utilities.g_on_parameters(1) := 'W';
121 else
122 icx_on_utilities.g_on_parameters(1) := 'DQ';
123 end if;
124
125 icx_on_utilities.g_on_parameters(6) := l_start;
126 icx_on_utilities.g_on_parameters(7) := '';
127 icx_on_utilities.g_on_parameters(8) := l_start_region;
128 icx_on_utilities.g_on_parameters(9) := l_encrypted_where;
129
130 icx_on_cabo.wherePage;
131 elsif l_type = 'D'
132 then
133 icx_on_cabo.WFPage;
134 else
135 icx_on_utilities.getRegions;
136 end if;
137
138 if ak_query_pkg.g_regions_table(0).flow_application_id > 0
139 then
140 if ( substr(icx_sec.g_mode_code,1,3) = '115' or
141 icx_sec.g_mode_code = 'SLAVE')
142 then
143 icx_on_cabo.displayPage;
144 else
145 icx_on_utilities.displayPage;
146 end if;
147 end if;
148
149 end if;
150
151 -- select HSECS into l_timer from v$timer;htp.p('end get_page @ '||l_timer);htp.nl;
152
153 exception
154 when VALUE_ERROR or INVALID_NUMBER then
155 fnd_message.set_name('ICX','ICX_USE_NUMBER');
156 l_message := fnd_message.get;
157 icx_util.add_error(l_message) ;
158 icx_admin_sig.error_screen(l_message);
159 when others then
160 l_err_num := SQLCODE;
161 l_message := SQLERRM;
162 select substr(l_message,12,512) into l_err_mesg from dual;
163 if (abs(l_err_num) between 1800 and 1899)
164 then
165 fnd_message.set_name('ICX','ICX_USE_DATE_FORMAT');
166 l_web_user_date_format := icx_sec.getID(icx_sec.pv_date_format);
167 fnd_message.set_token('FORMAT_MASK_TOKEN',nvl(l_web_user_date_format,'DD-MON-YYYY'));
168 l_message := l_err_mesg||'<br>'||fnd_message.get;
169 icx_util.add_error(l_message) ;
170 icx_admin_sig.error_screen(l_err_mesg);
171 else
172 icx_util.add_error(l_err_mesg);
173 icx_admin_sig.error_screen(l_err_mesg);
174 end if;
175 end;
176
177 procedure create_file(S in number,
178 c_delimiter in varchar2) is
179
180 l_type varchar2(30);
181 l_flow_appl_id number(15);
182 l_flow_code varchar2(30);
183 l_page_appl_id number(15);
184 l_page_code varchar2(30);
185 l_start number;
186 l_end number;
187 l_start_region varchar2(30);
188 c_rowid rowid;
189 l_encrypted_where number;
190 c_unique_key_name varchar2(30);
191 c_parameters icx_on_utilities.v240_table;
192 c_keys icx_on_utilities.v80_table;
193
194 l_region_appl_id number(15);
195 l_region_code varchar2(30);
196 l_where varchar2(2000);
197
198 c_from_page_appl_id number(15);
199 c_from_page_code varchar2(30);
200 c_from_region_appl_id number(15);
201 c_from_region_code varchar2(30);
202 c_to_page_appl_id number(15);
203 c_to_page_code varchar2(30);
204
205 l_responsibility_id number;
206 l_user_id number;
207
208 c_labels varchar2(4000);
209 l_values_table icx_util.char4000_table;
210 l_value varchar2(4000);
211 c_data varchar2(4000);
212
213 l_count1 number;
214 l_count2 number;
215 l_message varchar2(2000);
216
217 l_where_clause varchar2(2000);
218 l_query_binds ak_query_pkg.bind_tab;
219
220 -- Bug 3460155
221 c_labels1 varchar2(4000);
222 c_data1 varchar2(4000);
223 l_dbcharset v$nls_parameters.value%TYPE;
224
225
226 begin
227
228 icx_on_utilities.unpack_parameters(icx_call.decrypt2(S),c_parameters);
229
230 l_type := c_parameters(1);
231
232 if l_type = 'DQ' or l_type = 'W'
233 then
234
235 l_flow_appl_id := c_parameters(2);
236 l_flow_code := c_parameters(3);
237 l_page_appl_id := c_parameters(4);
238 l_page_code := c_parameters(5);
239 l_start := c_parameters(6);
240 l_end := c_parameters(7);
241 l_start_region := c_parameters(8);
242 l_encrypted_where := c_parameters(9);
243
244 select REGION_APPLICATION_ID,REGION_CODE
245 into l_region_appl_id,l_region_code
246 from AK_FLOW_PAGE_REGIONS
247 where FLOW_CODE = l_flow_code
248 and FLOW_APPLICATION_ID = l_flow_appl_id
249 and PAGE_CODE = l_page_code
250 and PAGE_APPLICATION_ID = l_page_appl_id;
251
252 l_where := icx_call.decrypt2(l_encrypted_where);
253
254 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
255 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
256
257 icx_on_utilities.unpack_whereSegment(l_where,l_where_clause,l_query_binds);
258
259 ak_query_pkg.exec_query(
260 P_FLOW_APPL_ID => l_flow_appl_id,
261 P_FLOW_CODE => l_flow_code,
262 P_PARENT_PAGE_APPL_ID => l_page_appl_id,
263 P_PARENT_PAGE_CODE => l_page_code,
264 P_PARENT_REGION_APPL_ID => l_region_appl_id,
265 P_PARENT_REGION_CODE => l_region_code,
266 P_WHERE_CLAUSE => l_where_clause,
267 P_WHERE_BINDS => l_query_binds,
268 P_RESPONSIBILITY_ID => l_responsibility_id,
269 P_USER_ID => l_user_id,
270 P_RETURN_PARENTS => 'T',
271 P_RETURN_CHILDREN => 'F',
272 P_RETURN_NODE_DISPLAY_ONLY => 'T');
273
274 else
275
276 l_start := c_parameters(6);
277 l_end := c_parameters(7);
278 l_start_region := c_parameters(8);
279 c_rowid := c_parameters(10);
280 c_unique_key_name := c_parameters(11);
281 c_keys(1) := c_parameters(12);
282 c_keys(2) := c_parameters(13);
283 c_keys(3) := c_parameters(14);
284 c_keys(4) := c_parameters(15);
285 c_keys(5) := c_parameters(16);
286 c_keys(6) := c_parameters(17);
287 c_keys(7) := c_parameters(18);
288 c_keys(8) := c_parameters(19);
289 c_keys(9) := c_parameters(20);
290 c_keys(10) := c_parameters(21);
291
292 select FLOW_APPLICATION_ID,FLOW_CODE,
293 FROM_PAGE_APPL_ID,FROM_PAGE_CODE,
294 FROM_REGION_APPL_ID,FROM_REGION_CODE,
295 TO_PAGE_APPL_ID,TO_PAGE_CODE
296 into l_flow_appl_id,l_flow_code,
297 c_from_page_appl_id,c_from_page_code,
298 c_from_region_appl_id,c_from_region_code,
299 c_to_page_appl_id,c_to_page_code
300 from AK_FLOW_REGION_RELATIONS
301 where ROWID = c_rowid;
302
303 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
304 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
305
306 ak_query_pkg.exec_query(
307 P_FLOW_APPL_ID => l_flow_appl_id,
308 P_FLOW_CODE => l_flow_code,
309 P_PARENT_PAGE_APPL_ID => c_from_page_appl_id,
310 P_PARENT_PAGE_CODE => c_from_page_code,
311 P_PARENT_REGION_APPL_ID => c_from_region_appl_id,
312 P_PARENT_REGION_CODE => c_from_region_code,
313 P_PARENT_PRIMARY_KEY_NAME => c_unique_key_name,
314 P_PARENT_KEY_VALUE1 => c_keys(1),
315 P_PARENT_KEY_VALUE2 => c_keys(2),
316 P_PARENT_KEY_VALUE3 => c_keys(3),
317 P_PARENT_KEY_VALUE4 => c_keys(4),
318 P_PARENT_KEY_VALUE5 => c_keys(5),
319 P_PARENT_KEY_VALUE6 => c_keys(6),
320 P_PARENT_KEY_VALUE7 => c_keys(7),
321 P_PARENT_KEY_VALUE8 => c_keys(8),
322 P_PARENT_KEY_VALUE9 => c_keys(9),
323 P_PARENT_KEY_VALUE10 => c_keys(10),
324 P_CHILD_PAGE_APPL_ID => c_to_page_appl_id,
325 P_CHILD_PAGE_CODE => c_to_page_code,
326 P_RESPONSIBILITY_ID => l_responsibility_id,
327 P_USER_ID => l_user_id,
328 P_RETURN_PARENTS => 'F',
329 P_RETURN_CHILDREN => 'T',
330 P_RETURN_NODE_DISPLAY_ONLY => 'T');
331
332 end if;
333
334 owa_util.mime_header('application/x-excel', TRUE);
335
336 -- icx_on_utilities2.printPLSQLtables;
337
338 if ak_query_pkg.g_results_table.COUNT = 0
339 then
340 fnd_message.set_name('ICX','ICX_NO_RECORDS_FOUND');
341 fnd_message.set_token('NAME_OF_REGION_TOKEN',ak_query_pkg.g_regions_table(0).name);
342 l_message := fnd_message.get;
343
344 htp.p(l_message);
345 else
346
347 for r in ak_query_pkg.g_regions_table.FIRST..ak_query_pkg.g_regions_table.LAST loop
348
349 if ak_query_pkg.g_regions_table(r).total_result_count > 0
350 then
351
352 if ak_query_pkg.g_regions_table(r).region_style = 'FORM'
353 then
354
355 l_count1 := 0;
356
357 for i in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
358
359 if ak_query_pkg.g_items_table(i).region_rec_id = ak_query_pkg.g_regions_table(r).region_rec_id
360 and ak_query_pkg.g_items_table(i).secured_column = 'F'
361 and ak_query_pkg.g_items_table(i).node_display_flag = 'Y'
362 and ak_query_pkg.g_items_table(i).item_style = 'TEXT'
363 then
364
365 for v in ak_query_pkg.g_results_table.FIRST..ak_query_pkg.g_results_table.LAST loop
366
367 if ak_query_pkg.g_results_table(v).region_rec_id = ak_query_pkg.g_results_table(r).region_rec_id
368 then
369
370 if l_count1 = ak_query_pkg.g_regions_table(r).number_of_format_columns
371 then
372 htp.p;
373 l_count1 := 0;
374 end if;
375
376 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(v),l_values_table);
377
378 if ak_query_pkg.g_items_table(i).value_id is null
379 then
380 l_value := '';
381 else
382 l_value := replace(l_values_table(ak_query_pkg.g_items_table(i).value_id),','
383 ,'');
384 end if;
385 if ak_query_pkg.g_items_table(i).attribute_label_long is null and l_value is null
386 then
387 l_value := l_value;
388 else
389 htp.prn(ak_query_pkg.g_items_table(i).attribute_label_long||c_delimiter||l_value||c_delimiter);
390 l_count1 := l_count1 + 1;
391 end if;
392
393 end if; -- region result
394
395 end loop; -- results
396
397 end if; -- display item
398
399 end loop; -- items
400
401 htp.p('');
402 htp.p('');
403
404 else
405
406 c_labels := '';
407 for i in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
408
409 if ak_query_pkg.g_items_table(i).region_rec_id = ak_query_pkg.g_regions_table(r).region_rec_id
410 and ak_query_pkg.g_items_table(i).secured_column = 'F'
411 and ak_query_pkg.g_items_table(i).node_display_flag = 'Y'
412 and ak_query_pkg.g_items_table(i).item_style = 'TEXT'
413 then
414 c_labels := c_labels||ak_query_pkg.g_items_table(i).attribute_label_long||c_delimiter;
415
416 end if;
417
418 end loop; -- items
419
420
421 --Start Bug3460155
422
423 l_dbcharset:=icx_sec.getNLS_PARAMETER('NLS_CHARACTERSET');
424
425 c_labels1 := convert(c_labels,fnd_profile.value('FND_NATIVE_CLIENT_ENCODING'),l_dbcharset);
426
427 htp.p(c_labels1);
428
429 c_data1 := convert(c_data,fnd_profile.value('FND_NATIVE_CLIENT_ENCODING'),l_dbcharset);
430
431 htp.p(c_data1);
432
433 --- End Bug3460155
434
435
436 for v in ak_query_pkg.g_results_table.FIRST..ak_query_pkg.g_results_table.LAST loop
437
438 if ak_query_pkg.g_results_table(v).region_rec_id = ak_query_pkg.g_regions_table(r).region_rec_id
439 then
440
441 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(v),l_values_table);
442
443 c_data := '';
444 for i in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST loop
445
446 if ak_query_pkg.g_items_table(i).region_rec_id = ak_query_pkg.g_regions_table(r).region_rec_id
447 and ak_query_pkg.g_items_table(i).secured_column = 'F'
448 and ak_query_pkg.g_items_table(i).node_display_flag = 'Y'
449 and ak_query_pkg.g_items_table(i).item_style = 'TEXT'
450 then
451 if ak_query_pkg.g_items_table(i).value_id is null
452 then
453 l_value := '';
454 else
455 l_value := replace(l_values_table(ak_query_pkg.g_items_table(i).value_id),',','');
456 end if;
457 c_data := c_data||l_value||c_delimiter;
458 end if;
459
460 end loop; -- items
461
462 -- Start Bug3460155
463
464 --htp.p(c_data);
465
466 c_data1 := convert(c_data,fnd_profile.value('FND_NATIVE_CLIENT_ENCODING'),l_dbcharset);
467
468 htp.p(c_data1);
469
470 --- End Bug3460155
471
472
473 end if;
474
475 end loop; -- results
476
477 end if; -- region style
478
479 else
480 fnd_message.set_name('ICX','ICX_NO_RECORDS_FOUND');
481 fnd_message.set_token('NAME_OF_REGION_TOKEN',ak_query_pkg.g_regions_table(r).name);
482 l_message := fnd_message.get;
483 htp.p(l_message);
484
485 end if; -- no row in region
486
487 end loop; -- regions
488
489 end if; -- no results
490
491 end;
492
493 end icx_on;