[Home] [Help]
PACKAGE BODY: APPS.AK_QUERY_PKG
Source
1 PACKAGE BODY AK_QUERY_PKG AS
2 /* $Header: akqueryb.pls 115.40 2004/03/10 20:53:58 tshort ship $ */
3
4 TYPE relation_rec IS RECORD
5 ( foreign_key_name varchar2(30),
6 fk_unique_key_name varchar2(30),
7 fk_db_object_name varchar2(30),
8 from_page_appl_id number,
9 from_page_code varchar2(30),
10 from_region_appl_id number,
11 from_region_code varchar2(30),
12 from_db_object_name varchar2(30),
13 from_obj_unique_key varchar2(30),
14 from_display_region boolean,
15 to_page_appl_id number,
16 to_page_code varchar2(30),
17 to_region_appl_id number,
18 to_region_code varchar2(30),
19 to_db_object_name varchar2(30),
20 to_obj_unique_key varchar2(30),
21 to_display_region boolean
22 );
23
24 TYPE relations_table_type is table of relation_rec
25 index by binary_integer;
26
27 PROCEDURE do_execute_query
28 (
29 p_node IN region_rec,
30 p_node_key_columns IN rel_key_tab,
31 p_node_key_values IN rel_key_value_tab,
32 p_child_page_appl_id IN number,
33 p_child_page_code IN varchar2,
34 p_where_clause IN varchar2,
35 p_where_binds IN bind_tab,
36 p_order_by_clause IN varchar2,
37 p_responsibility_id IN number,
38 p_user_id IN number,
39 p_return_parents IN boolean,
40 p_return_children IN boolean,
41 p_return_node_display_only IN boolean,
42 p_display_region IN boolean,
43 p_range_low IN number ,
44 p_range_high IN number ,
45 p_max_rows IN number ,
46 p_use_subquery IN boolean
47 );
48
49 PROCEDURE process_children
50 ( p_parent IN region_rec,
51 p_parent_key_columns IN rel_key_tab,
52 p_parent_key_values IN rel_key_value_tab,
53 p_child_page_appl_id IN number,
54 p_child_page_code IN varchar2,
55 p_responsibility_id IN number,
56 p_user_id IN number,
57 p_return_node_display_only IN boolean,
58 p_display_region IN boolean,
59 p_use_subquery IN boolean,
60 p_range_low IN number ,
61 p_range_high IN number ,
62 p_where_clause IN varchar2 ,
63 p_where_binds IN bind_tab
64 );
65
66
67 PROCEDURE load_relations
68 (
69 p_region_rec IN region_rec,
70 p_target_page_appl_id IN number,
71 p_target_page_code IN varchar2,
72 p_relations_table OUT NOCOPY relations_table_type
73 );
74
75 PROCEDURE construct_query
76 (
77 p_region_rec IN region_rec,
78 p_key_columns IN rel_key_tab,
79 p_key_values IN rel_key_value_tab,
80 p_where_clause IN varchar2,
81 p_order_by_clause IN varchar2,
82 p_return_node_display_only IN boolean,
83 p_display_region IN boolean,
84 p_responsibility_id IN number,
85 p_user_id IN number,
86 p_max_rows IN number,
87 p_use_subquery IN boolean,
88 p_query_stmt OUT NOCOPY varchar2,
89 p_num_select OUT NOCOPY number,
90 p_num_key OUT NOCOPY number,
91 p_new_key_columns OUT NOCOPY rel_key_tab,
92 p_rls_binds OUT NOCOPY bind_tab,
93 p_select OUT NOCOPY varchar2,
94 p_from OUT NOCOPY varchar2,
95 p_where OUT NOCOPY varchar2,
96 p_order_by OUT NOCOPY varchar2
97 );
98
99 PROCEDURE get_sql
100 (
101 p_cursor_id IN number,
102 p_num_select IN number,
103 p_num_key IN number,
104 p_region_rec IN region_rec,
105 p_display_region IN boolean,
106 p_key_values OUT NOCOPY rel_key_value_tab
107 );
108
109 PROCEDURE define_sql
110 (
111 p_cursor_id IN number,
112 p_num_select IN number
113 );
114
115 PROCEDURE bind_sql
116 (
117 p_cursor_id IN number,
118 p_key_values IN rel_key_value_tab
119 );
120
121 PROCEDURE bind_where_clause
122 (
123 p_cursor_id IN number,
124 p_where_binds IN bind_tab
125 );
126
127 PROCEDURE create_region_record
128 (
129 p_region_rec IN region_rec,
130 p_key_column_values IN rel_key_value_tab,
131 p_key_column_count IN number,
132 p_where_binds IN bind_tab,
133 p_rls_binds IN bind_tab,
134 p_select IN varchar2,
135 p_from IN varchar2,
136 p_where IN varchar2,
137 p_order_by IN varchar2
138 );
139
140 PROCEDURE get_new_key_values
141 (
142 p_db_object_name IN varchar2,
143 p_current_key_columns IN rel_key_tab,
144 p_current_key_values IN rel_key_value_tab,
145 p_new_key_columns IN rel_key_tab,
146 p_new_key_values OUT NOCOPY rel_key_value_tab
147 );
148
149 PROCEDURE get_fk_columns
150 ( p_foreign_key_name IN varchar2,
151 p_fk_column_tab OUT NOCOPY rel_key_tab
152 );
153
154 PROCEDURE get_uk_columns
155 ( p_unique_key_name IN varchar2,
156 p_uk_column_tab OUT NOCOPY rel_key_tab
157 );
158
159 PROCEDURE print_debug
160 ( dMessage IN varchar2
161 );
162
163 -- ======================================================
164 -- EXEC_QUERY |
165 -- ======================================================
166 PROCEDURE exec_query
167 (
168 p_flow_appl_id IN number,
169 p_flow_code IN varchar2,
170 p_parent_page_appl_id IN number,
171 p_parent_page_code IN varchar2,
172 p_parent_region_appl_id IN number,
173 p_parent_region_code IN varchar2,
174 p_parent_primary_key_name IN varchar2,
175 p_parent_key_value1 IN varchar2,
176 p_parent_key_value2 IN varchar2,
177 p_parent_key_value3 IN varchar2,
178 p_parent_key_value4 IN varchar2,
179 p_parent_key_value5 IN varchar2,
180 p_parent_key_value6 IN varchar2,
181 p_parent_key_value7 IN varchar2,
182 p_parent_key_value8 IN varchar2,
183 p_parent_key_value9 IN varchar2,
184 p_parent_key_value10 IN varchar2,
185 p_child_page_appl_id IN number,
186 p_child_page_code IN varchar2,
187 p_where_clause IN varchar2,
188 p_order_by_clause IN varchar2,
189 p_responsibility_id IN number,
190 p_user_id IN number,
191 p_return_parents IN varchar2,
192 p_return_children IN varchar2,
193 p_return_node_display_only IN varchar2,
194 p_set_trace IN varchar2,
195 p_range_low IN number,
196 p_range_high IN number,
197 p_where_binds IN bind_tab,
198 p_max_rows IN number,
199 p_use_subquery IN varchar2
200 )
201 IS
202
203 root region_rec;
204 dummy varchar2(200);
205 query_stmt varchar2(20000);
206 retval number := 0;
207 cursor_id number := 0;
208 num_select number := 0;
209 num_key number := 0;
210 row_num number := 0;
211 l_current_key_columns rel_key_tab;
212 l_current_key_values rel_key_value_tab;
213 l_new_key_columns rel_key_tab;
214 l_new_key_values rel_key_value_tab;
215 l_return_parents boolean := FALSE;
216 l_return_children boolean := FALSE;
217 l_return_node_display_only boolean := FALSE;
218 l_range_low number := 0;
219 l_range_high number := MAXROWNUM;
220 l_use_subquery boolean := FALSE;
221
222 BEGIN
223 print_debug('** In function: exec_query **');
224
225 g_regions_table.delete;
226 g_regions_bind_table.delete;
227 g_items_table.delete;
228 g_results_table.delete;
229 --
230 -- If set_trace is true, enable sql trace
231 --
232 -- REMOVED FOR BUG 3304342
233 --IF (p_set_trace = 'T') THEN
234 --dummy := 'alter session set sql_trace = true';
235 --cursor_id := dbms_sql.open_cursor;
236 --dbms_sql.parse(cursor_id, dummy, dbms_sql.v7);
237 --retval := dbms_sql.execute(dummy);
238 --dbms_sql.close_cursor(cursor_id);
239 --END IF;
240
241 IF p_return_node_display_only = 'T' THEN
242 l_return_node_display_only := TRUE;
243 ELSE
244 l_return_node_display_only := FALSE;
245 END IF;
246
247 IF p_return_parents = 'T' THEN
248 l_return_parents := TRUE;
249 ELSE
250 l_return_parents := FALSE;
251 END IF;
252
253 IF p_return_children = 'T' THEN
254 l_return_children := TRUE;
255 ELSE
256 l_return_children := FALSE;
257 END IF;
258
259 IF p_use_subquery = 'T' THEN
260 l_use_subquery := TRUE;
261 ELSE
262 l_use_subquery := FALSE;
263 END IF;
264
265 --
266 -- Setup root node
267 --
268 root.region_rec_id := 0;
269 root.parent_region_rec_id := null;
270 root.flow_application_id := p_flow_appl_id;
271 root.flow_code := p_flow_code;
272 root.page_application_id := p_parent_page_appl_id;
273 root.page_code := p_parent_page_code;
274 root.region_application_id := p_parent_region_appl_id;
275 root.region_code := p_parent_region_code;
276
277 -- Get object's database_object_name and primary key
278 select ao.database_object_name, ao.primary_key_name
279 into root.database_object_name, root.primary_key_name
280 from ak_objects ao,
281 ak_regions ar
282 where ar.region_code = root.region_code
283 and ar.region_application_id = root.region_application_id
284 and ar.database_object_name = ao.database_object_name;
285
286 l_current_key_values(0) := substr(p_parent_key_value1,1,4000);
287 l_current_key_values(1) := substr(p_parent_key_value2,1,4000);
288 l_current_key_values(2) := substr(p_parent_key_value3,1,4000);
289 l_current_key_values(3) := substr(p_parent_key_value4,1,4000);
290 l_current_key_values(4) := substr(p_parent_key_value5,1,4000);
291 l_current_key_values(5) := substr(p_parent_key_value6,1,4000);
292 l_current_key_values(6) := substr(p_parent_key_value7,1,4000);
293 l_current_key_values(7) := substr(p_parent_key_value8,1,4000);
294 l_current_key_values(8) := substr(p_parent_key_value9,1,4000);
295 l_current_key_values(9) := substr(p_parent_key_value10,1,4000);
296
297 -- Setup l_new_key_columns and l_new_key_values
298 -- If a key was passed then check if the passed PK is different
299 -- than the current PK, if so convert
300 IF p_parent_primary_key_name is NOT NULL THEN
301 IF p_parent_primary_key_name <> root.primary_key_name THEN
302 get_uk_columns(p_parent_primary_key_name, l_current_key_columns);
303 get_uk_columns(root.primary_key_name, l_new_key_columns);
304 get_new_key_values(root.database_object_name,
305 l_current_key_columns,
306 l_current_key_values,
307 l_new_key_columns,
308 l_new_key_values);
309 ELSE
310 get_uk_columns(p_parent_primary_key_name, l_current_key_columns);
311 l_new_key_columns := l_current_key_columns;
312 l_new_key_values := l_current_key_values;
313 END IF;
314 END IF;
315
316 --
317 -- Only use p_range_low and p_range_high if this is an LOV type call
318 -- i.e. Parent query = TRUE and Child query = FALSE
319 --
320 IF ( (l_return_parents = TRUE and l_return_children = FALSE)
321 or (l_return_parents = FALSE and l_return_children = TRUE) )then
322 l_range_low := nvl(p_range_low,0);
323 l_range_high := nvl(p_range_high,MAXROWNUM);
324 END IF;
325
326 --
327 -- Now that everything is setup, call do_execute_query to do the work
328 --
329
330 -- set defaults due to gscc standard
331 l_range_low := nvl(l_range_low,0);
332 l_range_high := nvl(l_range_high,MAXROWNUM);
333 l_use_subquery := nvl(l_use_subquery, FALSE);
334
335 ak_query_pkg.do_execute_query(
336 root,
337 l_new_key_columns,
338 l_new_key_values,
339 p_child_page_appl_id,
340 p_child_page_code,
341 p_where_clause,
342 p_where_binds,
343 p_order_by_clause,
344 p_responsibility_id,
345 p_user_id,
346 l_return_parents,
347 l_return_children,
348 l_return_node_display_only,
349 TRUE,
350 l_range_low,
351 l_range_high,
352 p_max_rows,
353 l_use_subquery);
354
355 END exec_query;
356
357 -- ======================================================
358 -- DO_EXECUTE_QUERY |
359 -- ======================================================
360 PROCEDURE do_execute_query
361 (
362 p_node IN region_rec,
363 p_node_key_columns IN rel_key_tab,
364 p_node_key_values IN rel_key_value_tab,
365 p_child_page_appl_id IN number,
366 p_child_page_code IN varchar2,
367 p_where_clause IN varchar2,
368 p_where_binds IN bind_tab,
369 p_order_by_clause IN varchar2,
370 p_responsibility_id IN number,
371 p_user_id IN number,
372 p_return_parents IN boolean,
373 p_return_children IN boolean,
374 p_return_node_display_only IN boolean,
375 p_display_region IN boolean,
376 p_range_low IN number,
377 p_range_high IN number,
378 p_max_rows IN number,
379 p_use_subquery IN boolean
380 )
381 IS
382 l_retval number := 0;
383 l_row_num number := 0;
384 l_num_select number := 0;
385 l_num_key number := 0;
386 cursor_id number;
387 l_query_stmt varchar2(32000);
388 l_key_columns rel_key_tab;
389 l_key_values rel_key_value_tab;
390 l_rls_binds bind_tab;
391 l_select varchar2(10000);
392 l_from varchar2(240);
393 l_where varchar2(10000);
394 l_order_by varchar2(1000);
395
396 -- set defaults due to gscc standard
397 l_range_low number := 0;
398 l_range_high number := MAXROWNUM;
399 l_where_clause varchar2(10000) := null;
400 l_where_binds bind_tab := G_BIND_TAB_NULL;
401
402 BEGIN
403 print_debug('** in function: do_execute_query ** ');
404
405 IF p_return_parents = FALSE and p_return_children = FALSE THEN
406 -- return the region and item information for the parent only
407
408 ak_query_pkg.construct_query(
409 p_node,
410 p_node_key_columns,
411 p_node_key_values,
412 p_where_clause,
413 p_order_by_clause,
414 p_return_node_display_only,
415 p_display_region,
416 p_responsibility_id,
417 p_user_id,
418 p_max_rows,
419 p_use_subquery,
420 l_query_stmt,
421 l_num_select,
422 l_num_key,
423 l_key_columns,
424 l_rls_binds,
425 l_select,
426 l_from,
427 l_where,
428 l_order_by);
429
430 IF p_display_region THEN
431 ak_query_pkg.create_region_record(p_node,
432 p_node_key_values,
433 p_node_key_columns.COUNT,
434 l_rls_binds,
435 p_where_binds,
436 l_select,
437 l_from,
438 l_where,
439 l_order_by);
440 END IF;
441
442
443 END IF;
444
445
446 IF p_return_parents THEN
447
448 ak_query_pkg.construct_query(
449 p_node,
450 p_node_key_columns,
451 p_node_key_values,
452 p_where_clause,
453 p_order_by_clause,
454 p_return_node_display_only,
455 p_display_region,
456 p_responsibility_id,
457 p_user_id,
458 p_max_rows,
459 p_use_subquery,
460 l_query_stmt,
461 l_num_select,
462 l_num_key,
463 l_key_columns,
464 l_rls_binds,
465 l_select,
466 l_from,
467 l_where,
468 l_order_by);
469
470 IF p_display_region THEN
471 ak_query_pkg.create_region_record(p_node,
472 p_node_key_values,
473 p_node_key_columns.COUNT,
474 l_rls_binds,
475 p_where_binds,
476 l_select,
477 l_from,
478 l_where,
479 l_order_by);
480 END IF;
481
482 --
483 -- Retreive data results
484 --
485 cursor_id := dbms_sql.open_cursor;
489 ak_query_pkg.bind_sql(cursor_id, p_node_key_values);
486 dbms_sql.parse(cursor_id, l_query_stmt, dbms_sql.v7);
487
488 IF (p_node_key_columns.count > 0) THEN
490 END IF;
491
492 IF (p_where_binds.count > 0) THEN
493 ak_query_pkg.bind_where_clause(cursor_id, p_where_binds);
494 END IF;
495
496 IF (l_rls_binds.count > 0) THEN
497 ak_query_pkg.bind_where_clause(cursor_id, l_rls_binds);
498 END IF;
499
500 ak_query_pkg.define_sql(cursor_id, l_num_select + l_num_key);
501
502 l_retval := dbms_sql.execute(cursor_id);
503
504 -- Fetch rows
505 LOOP
506 l_retval := dbms_sql.fetch_rows(cursor_id);
507
508 IF (l_retval = 0) THEN -- no more rows
509 -- If the region is displayed it will be the highest numbered region,
510 -- then set region with the total number of rows the query returned
511 -- regardless of how many rows are actually fetched based on the range
512 -- used below.
513 IF p_display_region THEN
514 g_regions_table(p_node.region_rec_id).total_result_count
515 := l_row_num;
516 END IF;
517 exit;
518 ELSE
519 l_row_num := l_row_num + 1;
520 print_debug('row# = '||to_char(l_row_num));
521 END IF;
522
523 -- Only get the row and do something with it if it falls within
524 -- the range of rows to get (i.e. this is an LOV and we are only
525 -- to return rows 25 - 50
526 IF l_row_num >= p_range_low AND l_row_num <= p_range_high THEN
527
528 ak_query_pkg.get_sql(cursor_id,
529 l_num_select,
530 l_num_key,
531 p_node,
532 p_display_region,
533 l_key_values);
534
535 IF p_return_children THEN
536 process_children( p_node,
537 l_key_columns,
538 l_key_values,
539 p_child_page_appl_id,
540 p_child_page_code,
541 p_responsibility_id,
542 p_user_id,
543 p_return_node_display_only,
544 p_display_region,
545 p_use_subquery,
546 l_range_low,
547 l_range_high,
548 l_where_clause,
549 l_where_binds);
550 END IF;
551 END IF;
552 END LOOP;
553 dbms_sql.close_cursor(cursor_id);
554 ELSE
555 -- p_return_parents is false
556 -- don't return parents, just take the key_values and get children
557 IF p_return_children THEN
558 -- The parameter p_display_region is FALSE, because the parent
559 -- region wasn't displayed, because p_return_parents was false
560
561 -- set defaults to gscc standard
562 l_range_low := nvl(p_range_low,0);
563 l_range_high := nvl(p_range_high,MAXROWNUM);
564 l_where_binds := nvl(p_where_binds,G_BIND_TAB_NULL);
565
566 process_children( p_node,
567 p_node_key_columns,
568 p_node_key_values,
569 p_child_page_appl_id,
570 p_child_page_code,
571 p_responsibility_id,
572 p_user_id,
573 p_return_node_display_only,
574 FALSE,
575 p_use_subquery,
576 l_range_low,
577 l_range_high,
578 p_where_clause,
579 l_where_binds);
580 END IF;
581
582 END IF;
583
584 END do_execute_query;
585
586
587 -- ======================================================
588 -- LOAD_RELATIONS |
589 -- ======================================================
590 PROCEDURE load_relations
591 (
592 p_region_rec IN region_rec,
593 p_target_page_appl_id IN number,
594 p_target_page_code IN varchar2,
595 p_relations_table OUT NOCOPY relations_table_type
596 )
597 IS
598
599 CURSOR relations_cur
600 (
601 flow_appl_id_param NUMBER,
602 flow_code_param VARCHAR2,
603 from_page_appl_id_param NUMBER,
604 from_page_code_param VARCHAR2,
605 from_region_appl_id_param NUMBER,
606 from_region_code_param VARCHAR2,
607 to_page_appl_id_param NUMBER,
608 to_page_code_param VARCHAR2
609 )
610 IS
611 SELECT afrr.foreign_key_name foreign_key_name,
612 afk.unique_key_name fk_unique_key_name,
613 afk.database_object_name fk_db_object_name,
614 afrr.from_page_appl_id from_page_appl_id,
615 afrr.from_page_code from_page_code,
616 afrr.from_region_appl_id from_region_appl_id,
617 afrr.from_region_code from_region_code,
618 ar1.database_object_name from_db_object_name,
619 ao1.primary_key_name from_obj_unique_key,
620 decode(afpr1.display_sequence, null, 'N','Y') from_region_disp_flag,
621 afrr.to_page_appl_id to_page_appl_id,
622 afrr.to_page_code to_page_code,
623 afrr.to_region_appl_id to_region_appl_id,
624 afrr.to_region_code to_region_code,
625 ar2.database_object_name to_db_object_name,
626 ao2.primary_key_name to_obj_unique_key,
627 decode(afpr2.display_sequence, null, 'N','Y') to_region_disp_flag,
628 decode(afpr2.display_sequence, null, 0 ,afpr2.display_sequence) disp_seq
629 FROM
630 ak_flow_region_relations afrr,
631 ak_regions ar1,
632 ak_regions ar2,
633 ak_flow_page_regions afpr1,
634 ak_flow_page_regions afpr2,
635 ak_objects ao1,
636 ak_objects ao2,
637 ak_foreign_keys afk
638 WHERE afrr.flow_application_id = flow_appl_id_param
639 AND afrr.flow_code = flow_code_param
640 AND afrr.from_page_appl_id = from_page_appl_id_param
641 AND afrr.from_page_code = from_page_code_param
645 AND afrr.to_page_code = NVL(to_page_code_param,afrr.to_page_code)
642 AND afrr.from_region_appl_id = from_region_appl_id_param
643 AND afrr.from_region_code = from_region_code_param
644 AND afrr.to_page_appl_id = NVL(to_page_appl_id_param,afrr.to_page_appl_id)
646 AND afrr.from_region_appl_id = ar1.region_application_id
647 AND afrr.from_region_code = ar1.region_code
648 AND afrr.to_region_appl_id = ar2.region_application_id
649 AND afrr.to_region_code = ar2.region_code
650 AND afrr.flow_application_id = afpr1.flow_application_id
651 AND afrr.flow_code = afpr1.flow_code
652 AND afrr.from_page_appl_id = afpr1.page_application_id
653 AND afrr.from_page_code = afpr1.page_code
654 AND afrr.from_region_appl_id = afpr1.region_application_id
655 AND afrr.from_region_code = afpr1.region_code
656 AND afrr.flow_application_id = afpr2.flow_application_id
657 AND afrr.flow_code = afpr2.flow_code
658 AND afrr.to_page_appl_id = afpr2.page_application_id
659 AND afrr.to_page_code = afpr2.page_code
660 AND afrr.to_region_appl_id = afpr2.region_application_id
661 AND afrr.to_region_code = afpr2.region_code
662 AND ar1.database_object_name = ao1.database_object_name
663 AND ar2.database_object_name = ao2.database_object_name
664 AND afrr.foreign_key_name = afk.foreign_key_name
665 ORDER BY disp_seq
666 ;
667
668 rel_rec relations_cur%rowtype;
669 rel_count number := 0;
670 BEGIN
671 print_debug('** In function: load_relations');
672 OPEN relations_cur(p_region_rec.flow_application_id,
673 p_region_rec.flow_code,
674 p_region_rec.page_application_id,
675 p_region_rec.page_code,
676 p_region_rec.region_application_id,
677 p_region_rec.region_code,
678 p_target_page_appl_id,
679 p_target_page_code);
680 LOOP
681 FETCH relations_cur into rel_rec;
682 exit when relations_cur%notfound;
683
684 p_relations_table(rel_count).foreign_key_name := rel_rec.foreign_key_name;
685 p_relations_table(rel_count).fk_unique_key_name := rel_rec.fk_unique_key_name;
686 p_relations_table(rel_count).fk_db_object_name := rel_rec.fk_db_object_name;
687 p_relations_table(rel_count).from_page_appl_id := rel_rec.from_page_appl_id;
688 p_relations_table(rel_count).from_page_code := rel_rec.from_page_code;
689 p_relations_table(rel_count).from_region_appl_id:= rel_rec.from_region_appl_id;
690 p_relations_table(rel_count).from_region_code := rel_rec.from_region_code;
691 p_relations_table(rel_count).from_db_object_name:= rel_rec.from_db_object_name;
692 p_relations_table(rel_count).from_obj_unique_key:= rel_rec.from_obj_unique_key;
693 p_relations_table(rel_count).from_display_region:= (rel_rec.from_region_disp_flag = 'Y');
694 p_relations_table(rel_count).to_page_appl_id := rel_rec.to_page_appl_id;
695 p_relations_table(rel_count).to_page_code := rel_rec.to_page_code;
696 p_relations_table(rel_count).to_region_appl_id := rel_rec.to_region_appl_id;
697 p_relations_table(rel_count).to_region_code := rel_rec.to_region_code;
698 p_relations_table(rel_count).to_db_object_name := rel_rec.to_db_object_name;
699 p_relations_table(rel_count).to_obj_unique_key := rel_rec.to_obj_unique_key;
700 p_relations_table(rel_count).to_display_region := (rel_rec.to_region_disp_flag = 'Y');
701
702
703 rel_count := rel_count + 1;
704 END LOOP;
705 CLOSE relations_cur;
706
707 print_debug('** Leaving function: load_relations');
708
709 END load_relations;
710
711
712 -- ======================================================
713 -- CONSTRUCT_QUERY |
714 -- ======================================================
715 PROCEDURE construct_query
716 (
717 p_region_rec IN region_rec,
718 p_key_columns IN rel_key_tab,
719 p_key_values IN rel_key_value_tab,
720 p_where_clause IN varchar2,
721 p_order_by_clause IN varchar2,
722 p_return_node_display_only IN boolean,
723 p_display_region IN boolean,
724 p_responsibility_id IN number,
725 p_user_id IN number,
726 p_max_rows IN number,
727 p_use_subquery IN boolean,
728 p_query_stmt OUT NOCOPY varchar2,
729 p_num_select OUT NOCOPY number,
730 p_num_key OUT NOCOPY number,
731 p_new_key_columns OUT NOCOPY rel_key_tab,
732 p_rls_binds OUT NOCOPY bind_tab,
733 p_select OUT NOCOPY varchar2,
734 p_from OUT NOCOPY varchar2,
735 p_where OUT NOCOPY varchar2,
736 p_order_by OUT NOCOPY varchar2
737 )
738 IS
739
740 CURSOR select_cur
741 (
742 p_child_region_appl_id NUMBER,
743 p_child_region_code VARCHAR2,
744 p_responsibility_id NUMBER
745 )
746 IS
747 -- Select region_items that are also object_attributes
748 SELECT aoa.column_name column_name,
749 ari.display_sequence display_sequence,
750 ari.attribute_application_id attribute_application_id,
751 ari.attribute_code attribute_code,
752 decode(aei.attribute_code,NULL,'F','T') secured_column,
753 decode(arsa.attribute_code,NULL,'F','T') rls_column,
754 decode(ari.icx_custom_call,'INDEX','T','F') indexed_column,
755 arit.attribute_label_long attribute_label_long,
756 ari.attribute_label_length attribute_label_length,
757 aa.attribute_value_length attribute_value_length,
758 ari.display_value_length display_value_length,
762 ari.vertical_alignment vertical_alignment,
759 ari.item_style item_style,
760 ari.bold bold,
761 ari.italic italic,
763 ari.horizontal_alignment horizontal_alignment,
764 ari.object_attribute_flag object_attribute_flag,
765 ari.node_query_flag node_query_flag,
766 ari.node_display_flag node_display_flag,
767 ari.update_flag update_flag,
768 ari.required_flag required_flag,
769 ari.icx_custom_call icx_custom_call,
770 aoa.validation_api_pkg object_validation_api_pkg,
771 aoa.validation_api_proc object_validation_api_proc,
772 aoa.defaulting_api_pkg object_defaulting_api_pkg,
773 aoa.defaulting_api_proc object_defaulting_api_proc,
774 ari.region_validation_api_pkg region_validation_api_pkg,
775 ari.region_validation_api_proc region_validation_api_proc,
776 ari.region_defaulting_api_pkg region_defaulting_api_pkg,
777 ari.region_defaulting_api_proc region_defaulting_api_proc,
778 ari.lov_foreign_key_name lov_foreign_key_name,
779 ari.lov_region_application_id lov_region_application_id,
780 ari.lov_region_code lov_region_code,
781 ari.lov_attribute_application_id lov_attribute_application_id,
782 ari.lov_attribute_code lov_attribute_code,
783 ari.lov_default_flag lov_default_flag,
784 ari.order_sequence order_sequence,
785 ari.order_direction order_direction,
786 aa.data_type data_type
787 FROM ak_object_attributes aoa,
788 ak_excluded_items aei,
789 ak_resp_security_attributes arsa,
790 ak_attributes aa,
791 ak_regions ar,
792 ak_region_items_tl arit,
793 ak_region_items ari
794 WHERE ari.object_attribute_flag = 'Y'
795 AND aoa.attribute_application_id = ari.attribute_application_id
796 AND aoa.attribute_code = ari.attribute_code
797 AND aoa.database_object_name = ar.database_object_name
798 AND ar.region_application_id = ari.region_application_id
799 AND ar.region_code = ari.region_code
800 AND ari.region_code = p_child_region_code
801 AND ari.region_application_id = p_child_region_appl_id
802 AND arit.region_code = ari.region_code
803 AND arit.region_application_id = ari.region_application_id
804 AND arit.attribute_code = ari.attribute_code
805 AND arit.attribute_application_id = ari.attribute_application_id
806 AND arit.language = userenv('LANG')
807 AND aei.responsibility_id (+) = p_responsibility_id
808 AND aei.attribute_application_id (+) = ari.attribute_application_id
809 AND aei.attribute_code (+) = ari.attribute_code
810 AND arsa.responsibility_id (+) = p_responsibility_id
811 AND arsa.attribute_application_id (+) = ari.attribute_application_id
812 AND arsa.attribute_code (+) = ari.attribute_code
813 AND ari.attribute_code = aa.attribute_code
814 AND ari.attribute_application_id = aa.attribute_application_id
815 UNION ALL
816 -- Select region_items that are not object attributes
817 SELECT null column_name,
818 ari.display_sequence display_sequence,
819 ari.attribute_application_id attribute_application_id,
820 ari.attribute_code attribute_code,
821 decode(aei.attribute_code,NULL,'F','T') secured_column,
822 decode(arsa.attribute_code,NULL,'F','T') rls_column,
823 decode(ari.icx_custom_call,'INDEX','T','F') indexed_column,
824 arit.attribute_label_long attribute_label_long,
825 ari.attribute_label_length attribute_label_length,
826 aa.attribute_value_length attribute_value_length,
827 ari.display_value_length display_value_length,
828 ari.item_style item_style,
829 ari.bold bold,
830 ari.italic italic,
831 ari.vertical_alignment vertical_alignment,
832 ari.horizontal_alignment horizontal_alignment,
833 ari.object_attribute_flag object_attribute_flag,
834 ari.node_query_flag node_query_flag,
835 ari.node_display_flag node_display_flag,
836 ari.update_flag update_flag,
837 ari.required_flag required_flag,
838 ari.icx_custom_call icx_custom_call,
839 null object_validation_api_pkg,
840 null object_validation_api_proc,
841 null object_defaulting_api_pkg,
842 null object_defaulting_api_proc,
843 ari.region_validation_api_pkg region_validation_api_pkg,
844 ari.region_validation_api_proc region_validation_api_proc,
845 ari.region_defaulting_api_pkg region_defaulting_api_pkg,
846 ari.region_defaulting_api_proc region_defaulting_api_proc,
847 ari.lov_foreign_key_name lov_foreign_key_name,
848 ari.lov_region_application_id lov_region_application_id,
849 ari.lov_region_code lov_region_code,
850 ari.lov_attribute_application_id lov_attribute_application_id,
851 ari.lov_attribute_code lov_attribute_code,
852 ari.lov_default_flag lov_default_flag,
853 ari.order_sequence order_sequence,
854 ari.order_direction order_direction,
855 aa.data_type data_type
856 FROM ak_excluded_items aei,
857 ak_resp_security_attributes arsa,
858 ak_attributes aa,
859 ak_region_items_tl arit,
860 ak_region_items ari
864 AND arit.region_code = ari.region_code
861 WHERE ari.object_attribute_flag <> 'Y'
862 AND ari.region_code = p_child_region_code
863 AND ari.region_application_id = p_child_region_appl_id
865 AND arit.region_application_id = ari.region_application_id
866 AND arit.attribute_code = ari.attribute_code
867 AND arit.attribute_application_id = ari.attribute_application_id
868 AND arit.language = userenv('LANG')
869 AND aei.responsibility_id (+) = p_responsibility_id
870 AND aei.attribute_application_id (+) = ari.attribute_application_id
871 AND aei.attribute_code (+) = ari.attribute_code
872 AND arsa.responsibility_id (+) = p_responsibility_id
873 AND arsa.attribute_application_id (+) = ari.attribute_application_id
874 AND arsa.attribute_code (+) = ari.attribute_code
875 AND ari.attribute_code = aa.attribute_code
876 AND ari.attribute_application_id = aa.attribute_application_id
877 ORDER BY 2;
878
879 select_rec select_cur%rowtype;
880
881
882 CURSOR attr_values_cur
883 (
884 p_user_id NUMBER,
885 p_attribute_appl_id NUMBER,
886 p_attribute_code VARCHAR2,
887 p_responsibility_id NUMBER
888 )
889 IS
890 SELECT nvl(to_char(number_value),
891 nvl(varchar2_value,to_char(date_value))) sec_value
892 FROM ak_web_user_sec_attr_values awusav
893 WHERE awusav.web_user_id = p_user_id
894 AND awusav.attribute_application_id = p_attribute_appl_id
895 AND awusav.attribute_code = p_attribute_code
896 union
897 SELECT nvl(to_char(number_value),
898 nvl(varchar2_value,to_char(date_value))) sec_value
899 FROM AK_RESP_SECURITY_ATTR_VALUES arsav
900 WHERE arsav.responsibility_id = p_responsibility_id
901 AND arsav.attribute_application_id = p_attribute_appl_id
902 AND arsav.attribute_code = p_attribute_code;
903
904
905 attr_values_rec attr_values_cur%rowtype;
906
907 l_query_stmt varchar2(32000);
908 bind_count number := 0;
909 select_count number := 0;
910 key_count number := 0;
911 row_count number := 0;
912 select_stmt varchar2(10000);
913 from_stmt varchar2(240);
914 where_stmt varchar2(10000);
915 order_by_stmt varchar2(1000);
916 order_by_col_tab rel_name_tab;
917 order_by_dir_tab rel_name_tab;
918 node_display_criteria varchar2(1);
919 results_table_value_id integer := 1;
920 where_temp varchar2(1000);
921 where_secured varchar2(5000) := NULL;
922 i integer;
923 rec_count number;
924 l_use_subquery boolean := FALSE;
925 l_date_format varchar2(80);
926 BEGIN
927 print_debug('** In function: construct_query');
928
929 print_debug('retrieve item/attribute information');
930 --
931 -- When constructing the SQL statement, choose
932 -- between all columns or only those that are marked as
933 -- node_display_flag = 'Y'.
934 --
935 IF p_return_node_display_only THEN
936 node_display_criteria := 'Y';
937 ELSE
938 node_display_criteria := null;
939 END IF;
940
941 --
942 -- Construct the SQL statement by selecting
943 -- the region items
944 --
945 OPEN select_cur(p_region_rec.region_application_id,
946 p_region_rec.region_code,
947 p_responsibility_id);
948
949 print_debug('building select list and order by list');
950 LOOP
951 FETCH select_cur INTO select_rec;
952 EXIT WHEN select_cur%NOTFOUND;
953 row_count := select_cur%ROWCOUNT;
954
955 if ( NOT(p_return_node_display_only) or
956 (p_return_node_display_only and select_rec.node_display_flag = 'Y') ) then
957 IF select_rec.object_attribute_flag = 'Y' THEN
958 select_count := select_count + 1;
959
960 IF (select_count > 1) THEN
961 select_stmt := select_stmt || ', ';
962 END IF;
963
964 if ( select_rec.data_type = 'DATETIME' ) then
965 select value into l_date_format
966 from V$NLS_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';
967 l_date_format := l_date_format ||' HH24:MI:SS';
968 select_stmt := select_stmt ||'TO_CHAR(akq.'||
969 select_rec.column_name||', '''||
970 l_date_format||''')';
971 else
972 select_stmt := select_stmt || 'SUBSTR(akq.'||
973 select_rec.column_name ||',1,4000)';
974 end if;
975 print_debug('select column = '||select_rec.column_name);
976
977 IF select_rec.order_sequence IS NOT NULL THEN
978 order_by_col_tab(select_rec.order_sequence) := select_rec.column_name;
979 order_by_dir_tab(select_rec.order_sequence) :=
980 select_rec.order_direction;
981
982 END IF;
983
984 END IF; -- end if object_attribute_flag = 'Y'
985 end if;
986
987 IF p_display_region THEN
988 if ( NOT(p_return_node_display_only) or
989 (p_return_node_display_only and select_rec.node_display_flag = 'Y') ) then
990
991 --
992 -- Add item defintion to g_items_table.
993 --
994 print_debug('Adding attribute to Items Table = '||
995 select_rec.attribute_code);
996 i := g_items_table.COUNT;
997 g_items_table(i).region_rec_id := p_region_rec.region_rec_id;
998 g_items_table(i).attribute_application_id := select_rec.attribute_application_id;
1002 g_items_table(i).attribute_value_length := select_rec.attribute_value_length;
999 g_items_table(i).attribute_code := select_rec.attribute_code;
1000 g_items_table(i).attribute_label_long := select_rec.attribute_label_long;
1001 g_items_table(i).attribute_label_length := select_rec.attribute_label_length;
1003 g_items_table(i).display_value_length := select_rec.display_value_length;
1004 g_items_table(i).display_sequence := select_rec.display_sequence;
1005 g_items_table(i).item_style := select_rec.item_style;
1006 g_items_table(i).bold := select_rec.bold;
1007 g_items_table(i).italic := select_rec.italic;
1008 g_items_table(i).vertical_alignment := select_rec.vertical_alignment;
1009 g_items_table(i).horizontal_alignment := select_rec.horizontal_alignment;
1010 g_items_table(i).object_attribute_flag := select_rec.object_attribute_flag;
1011 g_items_table(i).node_query_flag := select_rec.node_query_flag;
1012 g_items_table(i).node_display_flag := select_rec.node_display_flag;
1013 g_items_table(i).update_flag := select_rec.update_flag;
1014 g_items_table(i).required_flag := select_rec.required_flag;
1015 g_items_table(i).icx_custom_call := select_rec.icx_custom_call;
1016 g_items_table(i).region_defaulting_api_pkg := select_rec.region_defaulting_api_pkg;
1017 g_items_table(i).region_defaulting_api_proc := select_rec.region_defaulting_api_proc;
1018 g_items_table(i).region_validation_api_pkg := select_rec.region_validation_api_pkg;
1019 g_items_table(i).region_validation_api_proc := select_rec.region_validation_api_proc;
1020 g_items_table(i).object_defaulting_api_pkg := select_rec.object_defaulting_api_pkg;
1021 g_items_table(i).object_defaulting_api_proc := select_rec.object_defaulting_api_proc;
1022 g_items_table(i).object_validation_api_pkg := select_rec.object_validation_api_pkg;
1023 g_items_table(i).object_validation_api_proc := select_rec.object_validation_api_proc;
1024 g_items_table(i).lov_foreign_key_name := select_rec.lov_foreign_key_name;
1025 g_items_table(i).lov_region_application_id := select_rec.lov_region_application_id;
1026 g_items_table(i).lov_region_code := select_rec.lov_region_code;
1027 g_items_table(i).lov_attribute_application_id := select_rec.lov_attribute_application_id;
1028 g_items_table(i).lov_attribute_code := select_rec.lov_attribute_code;
1029 g_items_table(i).lov_default_flag := select_rec.lov_default_flag;
1030 g_items_table(i).secured_column := select_rec.secured_column;
1031 g_items_table(i).indexed_column := select_rec.indexed_column;
1032 g_items_table(i).rls_column := select_rec.rls_column;
1033
1034 --
1035 -- Set index into result_table for items that are 'object_attributes'.
1036 -- Region Items that are only 'attributes' will have no entry
1037 -- in result_table.
1038 --
1039 IF select_rec.object_attribute_flag = 'Y' THEN
1040 g_items_table(i).value_id := results_table_value_id;
1041 results_table_value_id := results_table_value_id +1;
1042 ELSE
1043 g_items_table(i).value_id := NULL;
1044 END IF;
1045 end if; -- end if display_node_flag = 'Y'
1046
1047 --
1048 -- Item has secured VALUES if it was found on the
1049 -- ak_web_user_sec_attr_values or on the AK_RESP_SECURITY_ATTR_VALUES table.
1050 -- If found, then the
1051 -- record(s) will contain value(s) to use in the where clause
1052 -- to limit row selection. If item was suppose to have secured
1053 -- values, but none where found then create a where clause
1054 -- containing '= NULL' which forces no rows to be found.
1055 --
1056 IF select_rec.rls_column = 'T' THEN
1057 -- if there are more than 255 sec attr values, force p_use_subquery
1058 -- to TRUE
1059 rec_count := 0;
1060 l_use_subquery := p_use_subquery;
1061 for attr_values_rec in attr_values_cur(p_user_id, select_rec.attribute_application_id,
1062 select_rec.attribute_code, p_responsibility_id) loop
1063 rec_count := rec_count + 1;
1064 end loop;
1065 if rec_count > 255 then
1066 l_use_subquery := TRUE;
1067 end if;
1068
1069 IF l_use_subquery = FALSE THEN
1070 DECLARE
1071 i number := nvl(p_rls_binds.LAST,0);
1072 j number := 0;
1073 last_value varchar2(4000);
1074 where_temp varchar2(5000) := NULL;
1075 BEGIN
1076 FOR attr_values_rec IN attr_values_cur(p_user_id,
1077 select_rec.attribute_application_id,
1078 select_rec.attribute_code,
1079 p_responsibility_id) LOOP
1080 i := i + 1;
1081 j := j + 1;
1082 IF where_temp IS NULL THEN
1083 where_temp := '(akq.'||select_rec.column_name || ' IN (';
1084 ELSE
1085 where_temp := where_temp ||', ';
1086 END IF;
1087 where_temp := where_temp || ':BIND'||
1088 to_char(select_count)||'_'||to_char(i);
1089 p_rls_binds(i).name:=
1090 'BIND'||to_char(select_count)||'_'||to_char(i);
1091 p_rls_binds(i).value:= attr_values_rec.sec_value;
1092 END LOOP;
1093 -- pad out the number of binds to 6,11,16,21...
1094 -- this is so there is a better hit ratio in sga for
1095 -- the sql statement
1096 IF j <> 0 THEN
1097 last_value := p_rls_binds(i).value;
1098 WHILE MOD(j,5) <> 1 LOOP
1099 i := i + 1;
1100 j := j + 1;
1101 where_temp := where_temp || ', :BIND'||
1102 to_char(select_count)||'_'||to_char(i);
1103 p_rls_binds(i).name:=
1104 'BIND'||to_char(select_count)||'_'||to_char(i);
1105 p_rls_binds(i).value:= last_value;
1106 END LOOP;
1110 ELSE
1107 END IF;
1108 IF where_temp IS NOT NULL THEN
1109 where_temp := where_temp || '))';
1111 where_temp := '(akq.' || select_rec.column_name || ' = NULL)';
1112 END IF;
1113
1114 where_secured := where_secured || ' AND ' || where_temp;
1115 END;
1116 ELSE -- p_use_subquery = T
1117 where_temp := '(akq.'||select_rec.column_name || ' IN ('
1118 || 'SELECT nvl(number_value,nvl(varchar2_value,date_value)) '
1119 || 'FROM ak_web_user_sec_attr_values awusav '
1120 || 'WHERE awusav.web_user_id = :BIND'
1121 || to_char(select_count) || 'USER_ID '
1122 || 'AND awusav.attribute_application_id = :BIND'
1123 || to_char(select_count) || 'ATTR_APPL_ID '
1124 || 'AND awusav.attribute_code = :BIND'
1125 || to_char(select_count) || 'ATTR_CODE '
1126 || 'union '
1127 || 'SELECT nvl(number_value,nvl(varchar2_value,date_value)) '
1128 || 'FROM AK_RESP_SECURITY_ATTR_VALUES arsav '
1129 || 'WHERE arsav.responsibility_id = :BIND'
1130 || to_char(select_count) || 'RESPONSIBILITY_ID '
1131 || 'AND arsav.attribute_application_id = :BIND'
1132 || to_char(select_count) || 'ATTR_APPL_ID '
1133 || 'AND arsav.attribute_code = :BIND'
1134 || to_char(select_count) || 'ATTR_CODE ))';
1135
1136
1137 DECLARE
1138 i number := nvl(p_rls_binds.LAST,0);
1139 BEGIN
1140 p_rls_binds(i+1).name := 'BIND'||to_char(select_count)||'USER_ID';
1141 p_rls_binds(i+1).value := p_user_id;
1142 p_rls_binds(i+2).name := 'BIND'||to_char(select_count)||
1143 'ATTR_APPL_ID';
1144 p_rls_binds(i+2).value := select_rec.attribute_application_id;
1145 p_rls_binds(i+3).name := 'BIND'||to_char(select_count)||
1146 'ATTR_CODE';
1147 p_rls_binds(i+3).value := select_rec.attribute_code;
1148 p_rls_binds(i+4).name := 'BIND'||to_char(select_count)||'RESPONSIBILITY_ID';
1149 p_rls_binds(i+4).value := p_responsibility_id;
1150
1151 END;
1152
1153 where_secured := where_secured || ' AND ' || where_temp;
1154 END IF;
1155 END IF;
1156 END IF;
1157 END LOOP;
1158
1159 p_num_select := select_count;
1160 print_debug('Select count = '||to_char(select_count));
1161 CLOSE select_cur;
1162
1163 --
1164 -- Now add the key columns to the select
1165 --
1166 DECLARE
1167 l_uk_column_tab rel_key_tab;
1168 BEGIN
1169 get_uk_columns(p_region_rec.primary_key_name, l_uk_column_tab);
1170
1171 FOR i IN 0..(l_uk_column_tab.count -1) LOOP
1172 IF select_stmt IS NOT NULL THEN
1173 select_stmt := select_stmt ||', ';
1174 END IF;
1175 IF l_uk_column_tab(i).is_date THEN
1176 select_stmt := select_stmt || 'TO_CHAR(akq.'||l_uk_column_tab(i).name||
1177 ',''YYYY/MM/DD HH24:MI:SS'')';
1178 ELSE
1179 select_stmt := select_stmt || 'akq.' || l_uk_column_tab(i).name;
1180 END IF;
1181 print_debug('key column = '||l_uk_column_tab(i).name);
1182 END LOOP;
1183 key_count := l_uk_column_tab.count;
1184 p_num_key := l_uk_column_tab.count;
1185 p_new_key_columns := l_uk_column_tab;
1186 END;
1187
1188
1189 --
1190 -- Now add the order by columns to the select
1191 -- We add these to the select list so that we can solve the following
1192 -- problems: 1) Must use positional notation since the same column
1193 -- may be in the select list multiple times
1194 -- 2) Must select these columns again to get the proper
1195 -- datatyping. Since the case columns are now substr'ed
1196 -- this implicitly converts them to varchars so that
1197 -- ordering by them will give a character ordering
1198 -- we need to select the raw column to order by here.
1199 --
1200 DECLARE
1201 max_order_by_sequence number := order_by_col_tab.LAST;
1202 i number := order_by_col_tab.FIRST;
1203 -- j is the total number of selected columns so far
1204 j number := select_count + key_count;
1205 append_col boolean;
1206 l_uk_column_tab rel_key_tab;
1207 k number;
1208 uk_column_index number;
1209 BEGIN
1210 get_uk_columns(p_region_rec.primary_key_name, l_uk_column_tab);
1211 -- Only do this if there are columns to order by (order_by_col_tab.FIRST
1212 -- is not null)
1213 IF order_by_col_tab.FIRST IS NOT NULL THEN
1214 WHILE i <= max_order_by_sequence LOOP
1215 append_col := true;
1216 -- we don't want to append the order by column to select list
1217 -- if it's one of the unique key columns
1218 for k in 0 .. (l_uk_column_tab.count - 1 ) loop
1219 if order_by_col_tab(i) = l_uk_column_tab(k).name then
1220 append_col := false;
1221 uk_column_index := k;
1222 end if;
1223 end loop;
1224 if append_col then
1225 j := j + 1;
1226 select_stmt := select_stmt ||', akq.'|| order_by_col_tab(i);
1227 IF order_by_stmt IS NOT NULL THEN
1228 order_by_stmt := order_by_stmt ||', ';
1229 END IF;
1230 order_by_stmt := order_by_stmt ||to_char(j)||' '||order_by_dir_tab(i);
1231 else
1232 if order_by_stmt is not null then
1233 order_by_stmt := order_by_stmt ||', ';
1234 end if;
1235 order_by_stmt := order_by_stmt ||to_char(select_count + uk_column_index + 1)||' '||order_by_dir_tab(i);
1236 print_debug('not append_col order_by = '||order_by_stmt);
1237 end if;
1238 i := order_by_col_tab.NEXT(i);
1239 END LOOP;
1240 END IF;
1241 END;
1242
1243 --
1244 -- Construct the WHERE clause
1245 --
1246
1247 print_debug('constructing where clause');
1248
1249 FOR bind_count IN 1..(p_key_columns.count) LOOP
1250
1254
1251 IF (bind_count > 1) THEN
1252 where_stmt := where_stmt || ' AND ';
1253 END IF;
1255 print_debug('bind fk column '||to_char(bind_count)|| ' is '||
1256 p_key_columns(bind_count-1).name);
1257
1258 if (p_key_values(bind_count-1) is null) then
1259 where_stmt := where_stmt ||'akq.'|| p_key_columns(bind_count-1).name ||
1260 ' is NULL ';
1261 else
1262 IF p_key_columns(bind_count-1).is_date THEN
1263 where_stmt := where_stmt ||'akq.'|| p_key_columns(bind_count-1).name ||
1264 ' = TO_DATE(:BIND' || to_char(bind_count) ||
1265 ',''YYYY/MM/DD HH24:MI:SS'')';
1266 ELSE
1267 where_stmt := where_stmt ||'akq.'|| p_key_columns(bind_count-1).name
1268 || ' = :BIND' || to_char(bind_count);
1269 END IF;
1270 end if;
1271
1272 END LOOP;
1273
1274 -- Assembling the sql statement
1275 print_debug('assembling sql statement');
1276
1277 -- If a where clause was passed, then add it on to where_stmt.
1278 -- If where_stmt is not null then need to include 'AND'
1279 if p_where_clause is not null then
1280 if where_stmt is not null then
1281 where_stmt := where_stmt || ' AND ('|| p_where_clause || ')';
1282 else
1283 where_stmt := p_where_clause;
1284 end if;
1285 end if;
1286
1287 --
1288 -- Add on row security 'where clause'.
1289 --
1290
1291 -- First make sure there is some where clause to and the row clause to
1292 IF where_stmt IS NULL THEN
1293 where_stmt := ' 1=1 ';
1294 END IF;
1295
1296 -- Add parens around where_stmt for bug 1774098
1297
1298 IF where_secured IS NOT NULL THEN -- where secured includes the AND
1299 where_stmt := '(' || where_stmt || ')' || where_secured;
1300 END IF;
1301
1302
1303 -- Assign sql fragments to out variables
1304 p_select := select_stmt;
1305 p_from := p_region_rec.database_object_name || ' akq';
1306 p_where := where_stmt;
1307 p_order_by := nvl(p_order_by_clause, order_by_stmt);
1308
1309
1310 --
1311 -- construct query using the following form:
1312 -- SELECT * FROM (
1313 -- SELECT p_select || FROM p_from || WHERE p_where || ORDER BY p_order_by
1314 -- )
1315 -- WHERE rownum < n
1316 --
1317
1318
1319 -- commented out this to fix bug 1409489
1320 -- l_query_stmt := 'SELECT * FROM ( SELECT ' || select_stmt ||' FROM '||
1321 -- p_region_rec.database_object_name || ' akq' ||
1322 -- ' WHERE ' || where_stmt;
1323
1324 -- If necessary add the top n where condition
1325 IF p_max_rows IS NOT NULL THEN
1326 where_stmt := where_stmt || ' AND ROWNUM < '||
1327 to_char(p_max_rows+1);
1328 END IF;
1329
1330 l_query_stmt := 'SELECT '||select_stmt||' FROM '||
1331 p_region_rec.database_object_name ||' akq' ||
1332 ' WHERE '||where_stmt;
1333
1334 -- Add order by if one exists (i.e. it was calculated or passed)
1335 -- If an order by clause was passed then use it, else use the one we built
1336 if p_order_by_clause is not null then
1337 l_query_stmt := l_query_stmt || ' ORDER BY '|| p_order_by_clause;
1338 else
1339 if order_by_stmt is not null then
1340 l_query_stmt := l_query_stmt || ' ORDER BY '|| order_by_stmt;
1341 end if;
1342 end if;
1343
1344 -- commented out the line below to fix bug 1409489
1345 -- now add the closing ')' after the inner select
1346 -- l_query_stmt := l_query_stmt || ')';
1347
1348 -- If necessary add the top n where condition
1349 -- IF p_max_rows IS NOT NULL THEN
1350 -- l_query_stmt := l_query_stmt || ' WHERE ROWNUM < '||
1351 -- to_char(p_max_rows+1);
1352 -- END IF;
1353
1354
1355 p_query_stmt := l_query_stmt;
1356 ak_query_pkg.sql_stmt := l_query_stmt;
1357 print_debug('sql_stmt = ' ||substr(l_query_stmt,1,240));
1358 print_debug('where clause = '|| where_stmt);
1359 print_debug('order by clause = '||
1360 nvl(p_order_by_clause,order_by_stmt));
1361
1362 END construct_query;
1363
1364
1365 -- ==============================================
1366 -- GET_SQL |
1367 -- ==============================================
1368 PROCEDURE get_sql
1369 (
1370 p_cursor_id IN number,
1371 p_num_select IN number,
1372 p_num_key IN number,
1373 p_region_rec IN region_rec,
1374 p_display_region IN boolean,
1375 p_key_values OUT NOCOPY rel_key_value_tab
1376 )
1377 IS
1378
1379 key_index number := 0;
1380 display_index number := 0;
1381 key_value rel_key_value_tab;
1382 display_value rel_key_value_tab;
1383 i integer;
1384 BEGIN
1385 print_debug('** In function: get_sql **');
1386
1387 --
1388 -- Retrieve column values
1389 --
1390 FOR display_index in 0..(p_num_select + p_num_key - 1) LOOP
1391
1392 IF (display_index < p_num_select) THEN
1393 --
1394 -- Retrieve display attribute values
1395 --
1396 dbms_sql.column_value(p_cursor_id, display_index + 1,
1397 display_value(display_index));
1398 print_debug('display_value('||to_char(display_index)||') = '
1399 ||display_value(display_index));
1400
1401 ELSE
1402 --
1403 -- Retrieve primary key values
1404 --
1405 dbms_sql.column_value(p_cursor_id, display_index + 1,
1406 key_value(key_index));
1407 print_debug('** key_value(' || to_char(key_index)
1411 END LOOP;
1408 || ')=' || key_value(key_index));
1409 key_index := key_index + 1;
1410 END IF;
1412
1413 IF p_display_region THEN
1414 print_debug('** creating results record **');
1415 --
1416 -- Populate the Results Table with the returned data.
1417 -- The routine 'g_results_table.COUNT' retrieves the next available slot in the table.
1418 --
1419 i := nvl(g_results_table.last, -1) + 1;
1420 g_results_table(i).region_rec_id := p_region_rec.region_rec_id;
1421
1422 if (1 <= p_num_key) then
1423 g_results_table(i).key1 := key_value(0);
1424 end if;
1425 if (2 <= p_num_key) then
1426 g_results_table(i).key2 := key_value(1);
1427 end if;
1428 if (3 <= p_num_key) then
1429 g_results_table(i).key3 := key_value(2);
1430 end if;
1431 if (4 <= p_num_key) then
1432 g_results_table(i).key4 := key_value(3);
1433 end if;
1434 if (5 <= p_num_key) then
1435 g_results_table(i).key5 := key_value(4);
1436 end if;
1437 if (6 <= p_num_key) then
1438 g_results_table(i).key6 := key_value(5);
1439 end if;
1440 if (7 <= p_num_key) then
1441 g_results_table(i).key7 := key_value(6);
1442 end if;
1443 if (8 <= p_num_key) then
1444 g_results_table(i).key8 := key_value(7);
1445 end if;
1446 if (9 <= p_num_key) then
1447 g_results_table(i).key9 := key_value(8);
1448 end if;
1449 if (10 <= p_num_key) then
1450 g_results_table(i).key10 := key_value(9);
1451 end if;
1452
1453 if (1 <= p_num_select) then
1454 g_results_table(i).value1 := display_value(0);
1455 end if;
1456 if (2 <= p_num_select) then
1457 g_results_table(i).value2 := display_value(1);
1458 end if;
1459 if (3 <= p_num_select) then
1460 g_results_table(i).value3 := display_value(2);
1461 end if;
1462 if (4 <= p_num_select) then
1463 g_results_table(i).value4 := display_value(3);
1464 end if;
1465 if (5 <= p_num_select) then
1466 g_results_table(i).value5 := display_value(4);
1467 end if;
1468 if (6 <= p_num_select) then
1469 g_results_table(i).value6 := display_value(5);
1470 end if;
1471 if (7 <= p_num_select) then
1472 g_results_table(i).value7 := display_value(6);
1473 end if;
1474 if (8 <= p_num_select) then
1475 g_results_table(i).value8 := display_value(7);
1476 end if;
1477 if (9 <= p_num_select) then
1478 g_results_table(i).value9 := display_value(8);
1479 end if;
1480 if (10 <= p_num_select) then
1481 g_results_table(i).value10 := display_value(9);
1482 end if;
1483 if (11 <= p_num_select) then
1484 g_results_table(i).value11 := display_value(10);
1485 end if;
1486 if (12 <= p_num_select) then
1487 g_results_table(i).value12 := display_value(11);
1488 end if;
1489 if (13 <= p_num_select) then
1490 g_results_table(i).value13 := display_value(12);
1491 end if;
1492 if (14 <= p_num_select) then
1493 g_results_table(i).value14 := display_value(13);
1494 end if;
1495 if (15 <= p_num_select) then
1496 g_results_table(i).value15 := display_value(14);
1497 end if;
1498 if (16 <= p_num_select) then
1499 g_results_table(i).value16 := display_value(15);
1500 end if;
1501 if (17 <= p_num_select) then
1502 g_results_table(i).value17 := display_value(16);
1503 end if;
1504 if (18 <= p_num_select) then
1505 g_results_table(i).value18 := display_value(17);
1506 end if;
1507 if (19 <= p_num_select) then
1508 g_results_table(i).value19 := display_value(18);
1509 end if;
1510 if (20 <= p_num_select) then
1511 g_results_table(i).value20 := display_value(19);
1512 end if;
1513 if (21 <= p_num_select) then
1514 g_results_table(i).value21 := display_value(20);
1515 end if;
1516 if (22 <= p_num_select) then
1517 g_results_table(i).value22 := display_value(21);
1518 end if;
1519 if (23 <= p_num_select) then
1520 g_results_table(i).value23 := display_value(22);
1521 end if;
1522 if (24 <= p_num_select) then
1523 g_results_table(i).value24 := display_value(23);
1524 end if;
1525 if (25 <= p_num_select) then
1526 g_results_table(i).value25 := display_value(24);
1527 end if;
1528 if (26 <= p_num_select) then
1529 g_results_table(i).value26 := display_value(25);
1530 end if;
1531 if (27 <= p_num_select) then
1532 g_results_table(i).value27 := display_value(26);
1533 end if;
1534 if (28 <= p_num_select) then
1535 g_results_table(i).value28 := display_value(27);
1536 end if;
1537 if (29 <= p_num_select) then
1538 g_results_table(i).value29 := display_value(28);
1539 end if;
1540 if (30 <= p_num_select) then
1541 g_results_table(i).value30 := display_value(29);
1542 end if;
1543 if (31 <= p_num_select) then
1544 g_results_table(i).value31 := display_value(30);
1545 end if;
1546 if (32 <= p_num_select) then
1550 g_results_table(i).value33 := display_value(32);
1547 g_results_table(i).value32 := display_value(31);
1548 end if;
1549 if (33 <= p_num_select) then
1551 end if;
1552 if (34 <= p_num_select) then
1553 g_results_table(i).value34 := display_value(33);
1554 end if;
1555 if (35 <= p_num_select) then
1556 g_results_table(i).value35 := display_value(34);
1557 end if;
1558 if (36 <= p_num_select) then
1559 g_results_table(i).value36 := display_value(35);
1560 end if;
1561 if (37 <= p_num_select) then
1562 g_results_table(i).value37 := display_value(36);
1563 end if;
1564 if (38 <= p_num_select) then
1565 g_results_table(i).value38 := display_value(37);
1566 end if;
1567 if (39 <= p_num_select) then
1568 g_results_table(i).value39 := display_value(38);
1569 end if;
1570 if (40 <= p_num_select) then
1571 g_results_table(i).value40 := display_value(39);
1572 end if;
1573 if (41 <= p_num_select) then
1574 g_results_table(i).value41 := display_value(40);
1575 end if;
1576 if (42 <= p_num_select) then
1577 g_results_table(i).value42 := display_value(41);
1578 end if;
1579 if (43 <= p_num_select) then
1580 g_results_table(i).value43 := display_value(42);
1581 end if;
1582 if (44 <= p_num_select) then
1583 g_results_table(i).value44 := display_value(43);
1584 end if;
1585 if (45 <= p_num_select) then
1586 g_results_table(i).value45 := display_value(44);
1587 end if;
1588 if (46 <= p_num_select) then
1589 g_results_table(i).value46 := display_value(45);
1590 end if;
1591 if (47 <= p_num_select) then
1592 g_results_table(i).value47 := display_value(46);
1593 end if;
1594 if (48 <= p_num_select) then
1595 g_results_table(i).value48 := display_value(47);
1596 end if;
1597 if (49 <= p_num_select) then
1598 g_results_table(i).value49 := display_value(48);
1599 end if;
1600 if (50 <= p_num_select) then
1601 g_results_table(i).value50 := display_value(49);
1602 end if;
1603 if (51 <= p_num_select) then
1604 g_results_table(i).value51 := display_value(50);
1605 end if;
1606 if (52 <= p_num_select) then
1607 g_results_table(i).value52 := display_value(51);
1608 end if;
1609 if (53 <= p_num_select) then
1610 g_results_table(i).value53 := display_value(52);
1611 end if;
1612 if (54 <= p_num_select) then
1613 g_results_table(i).value54 := display_value(53);
1614 end if;
1615 if (55 <= p_num_select) then
1616 g_results_table(i).value55 := display_value(54);
1617 end if;
1618 if (56 <= p_num_select) then
1619 g_results_table(i).value56 := display_value(55);
1620 end if;
1621 if (57 <= p_num_select) then
1622 g_results_table(i).value57 := display_value(56);
1623 end if;
1624 if (58 <= p_num_select) then
1625 g_results_table(i).value58 := display_value(57);
1626 end if;
1627 if (59 <= p_num_select) then
1628 g_results_table(i).value59 := display_value(58);
1629 end if;
1630 if (60 <= p_num_select) then
1631 g_results_table(i).value60 := display_value(59);
1632 end if;
1633 if (61 <= p_num_select) then
1634 g_results_table(i).value61 := display_value(60);
1635 end if;
1636 if (62 <= p_num_select) then
1637 g_results_table(i).value62 := display_value(61);
1638 end if;
1639 if (63 <= p_num_select) then
1640 g_results_table(i).value63 := display_value(62);
1641 end if;
1642 if (64 <= p_num_select) then
1643 g_results_table(i).value64 := display_value(63);
1644 end if;
1645 if (65 <= p_num_select) then
1646 g_results_table(i).value65 := display_value(64);
1647 end if;
1648 if (66 <= p_num_select) then
1649 g_results_table(i).value66 := display_value(65);
1650 end if;
1651 if (67 <= p_num_select) then
1652 g_results_table(i).value67 := display_value(66);
1653 end if;
1654 if (68 <= p_num_select) then
1655 g_results_table(i).value68 := display_value(67);
1656 end if;
1657 if (69 <= p_num_select) then
1658 g_results_table(i).value69 := display_value(68);
1659 end if;
1660 if (70 <= p_num_select) then
1661 g_results_table(i).value70 := display_value(69);
1662 end if;
1663 if (71 <= p_num_select) then
1664 g_results_table(i).value71 := display_value(70);
1665 end if;
1666 if (72 <= p_num_select) then
1667 g_results_table(i).value72 := display_value(71);
1668 end if;
1669 if (73 <= p_num_select) then
1670 g_results_table(i).value73 := display_value(72);
1671 end if;
1672 if (74 <= p_num_select) then
1673 g_results_table(i).value74 := display_value(73);
1674 end if;
1675 if (75 <= p_num_select) then
1676 g_results_table(i).value75 := display_value(74);
1677 end if;
1678 if (76 <= p_num_select) then
1679 g_results_table(i).value76 := display_value(75);
1680 end if;
1684 if (78 <= p_num_select) then
1681 if (77 <= p_num_select) then
1682 g_results_table(i).value77 := display_value(76);
1683 end if;
1685 g_results_table(i).value78 := display_value(77);
1686 end if;
1687 if (79 <= p_num_select) then
1688 g_results_table(i).value79 := display_value(78);
1689 end if;
1690 if (80 <= p_num_select) then
1691 g_results_table(i).value80 := display_value(79);
1692 end if;
1693 if (81 <= p_num_select) then
1694 g_results_table(i).value81 := display_value(80);
1695 end if;
1696 if (82 <= p_num_select) then
1697 g_results_table(i).value82 := display_value(81);
1698 end if;
1699 if (83 <= p_num_select) then
1700 g_results_table(i).value83 := display_value(82);
1701 end if;
1702 if (84 <= p_num_select) then
1703 g_results_table(i).value84 := display_value(83);
1704 end if;
1705 if (85 <= p_num_select) then
1706 g_results_table(i).value85 := display_value(84);
1707 end if;
1708 if (86 <= p_num_select) then
1709 g_results_table(i).value86 := display_value(85);
1710 end if;
1711 if (87 <= p_num_select) then
1712 g_results_table(i).value87 := display_value(86);
1713 end if;
1714 if (88 <= p_num_select) then
1715 g_results_table(i).value88 := display_value(87);
1716 end if;
1717 if (89 <= p_num_select) then
1718 g_results_table(i).value89 := display_value(88);
1719 end if;
1720 if (90 <= p_num_select) then
1721 g_results_table(i).value90 := display_value(89);
1722 end if;
1723 if (91 <= p_num_select) then
1724 g_results_table(i).value91 := display_value(90);
1725 end if;
1726 if (92 <= p_num_select) then
1727 g_results_table(i).value92 := display_value(91);
1728 end if;
1729 if (93 <= p_num_select) then
1730 g_results_table(i).value93 := display_value(92);
1731 end if;
1732 if (94 <= p_num_select) then
1733 g_results_table(i).value94 := display_value(93);
1734 end if;
1735 if (95 <= p_num_select) then
1736 g_results_table(i).value95 := display_value(94);
1737 end if;
1738 if (96 <= p_num_select) then
1739 g_results_table(i).value96 := display_value(95);
1740 end if;
1741 if (97 <= p_num_select) then
1742 g_results_table(i).value97 := display_value(96);
1743 end if;
1744 if (98 <= p_num_select) then
1745 g_results_table(i).value98 := display_value(97);
1746 end if;
1747 if (99 <= p_num_select) then
1748 g_results_table(i).value99 := display_value(98);
1749 end if;
1750 if (100 <= p_num_select) then
1751 g_results_table(i).value100:= display_value(99);
1752 end if;
1753
1754 print_debug('finish populate results_table');
1755 END IF;
1756
1757 -- Set out variables
1758 p_key_values := key_value;
1759
1760 END get_sql;
1761
1762
1763 -- ==============================================
1764 -- DEFINE_SQL |
1765 -- ==============================================
1766 PROCEDURE define_sql
1767 (
1768 p_cursor_id IN number,
1769 p_num_select IN number
1770 )
1771 IS
1772
1773 select_index number := 0;
1774 BEGIN
1775 print_debug('** In function: define_sql **');
1776
1777 --
1778 -- define each select column with an appropriate variable
1779 --
1780
1781 FOR select_index in 0..(p_num_select - 1) LOOP
1782 dbms_sql.define_column(p_cursor_id, select_index + 1, NULL, 4000);
1783 END LOOP;
1784 END define_sql;
1785
1786
1787 -- ==============================================
1788 -- BIND_SQL |
1789 -- ==============================================
1790 PROCEDURE bind_sql
1791 (
1792 p_cursor_id IN number,
1793 p_key_values IN rel_key_value_tab
1794 ) IS
1795
1796 key_index number := 0;
1797 dont_care_error EXCEPTION;
1798
1799 PRAGMA EXCEPTION_INIT(dont_care_error, -1006);
1800 BEGIN
1801 print_debug('** In function: bind_sql **');
1802
1803 --
1804 -- bind each variable to the appropriate key value
1805 --
1806
1807 FOR key_index in 0..(MAXKEYNUM-1) LOOP
1808 BEGIN
1809 print_debug('key_value(' || to_char(key_index) || ') = '
1810 || p_key_values(key_index));
1811
1812 IF (p_key_values(key_index) IS NOT NULL) THEN
1813 BEGIN
1814 print_debug('binding bind variable :BIND'||
1815 to_char(key_index + 1));
1816 dbms_sql.bind_variable(p_cursor_id, 'BIND' ||
1817 to_char(key_index + 1),
1818 p_key_values(key_index));
1819 EXCEPTION
1820 WHEN dont_care_error THEN NULL;
1821 END;
1822 END IF;
1823 EXCEPTION
1824 WHEN no_data_found THEN EXIT;
1825 END;
1826 END LOOP;
1827
1828 END bind_sql;
1829
1830
1831 -- ==============================================
1832 -- BIND_WHERE_CLAUSE |
1833 -- ==============================================
1834 PROCEDURE bind_where_clause
1835 (
1836 p_cursor_id IN number,
1837 p_where_binds IN bind_tab
1838 ) IS
1839
1840 bind_index number := 1;
1841 i number := p_where_binds.FIRST;
1842
1843 BEGIN
1844 print_debug('** In function: bind_where_clause **');
1845
1846 --
1847 -- bind each variable to the appropriate value
1848 --
1849
1850 FOR bind_index in 1..(p_where_binds.count) LOOP
1851
1852 print_debug('binding bind variable :'
1853 || p_where_binds(i).name || ' to '
1857 i := p_where_binds.NEXT(i);
1854 || '''' || p_where_binds(i).value || '''');
1855 dbms_sql.bind_variable(p_cursor_id, p_where_binds(i).name,
1856 p_where_binds(i).value);
1858 END LOOP;
1859
1860 END bind_where_clause;
1861
1862 -- ==============================================
1863 -- CREATE_REGION_RECORD |
1864 -- ==============================================
1865 PROCEDURE create_region_record
1866 (
1867 p_region_rec IN region_rec,
1868 p_key_column_values IN rel_key_value_tab,
1869 p_key_column_count IN number,
1870 p_where_binds IN bind_tab,
1871 p_rls_binds IN bind_tab,
1872 p_select IN varchar2,
1873 p_from IN varchar2,
1874 p_where IN varchar2,
1875 p_order_by IN varchar2
1876 )
1877 IS
1878 l_region_style varchar2(30);
1879 l_number_of_format_columns number;
1880 l_region_name varchar2(80);
1881 l_object_validation_api_pkg varchar2(30);
1882 l_object_validation_api_proc varchar2(30);
1883 l_object_defaulting_api_pkg varchar2(30);
1884 l_object_defaulting_api_proc varchar2(30);
1885 l_region_validation_api_pkg varchar2(30);
1886 l_region_validation_api_proc varchar2(30);
1887 l_region_defaulting_api_pkg varchar2(30);
1888 l_region_defaulting_api_proc varchar2(30);
1889 l_display_sequence number;
1890 i integer;
1891 BEGIN
1892 print_debug('** In function: create_region_record **');
1893
1894 -- Get the rest of the attributes of the region
1895 -- First try to get them from AK_FLOW_PAGE_REGIONS
1896
1897 BEGIN
1898 SELECT fpr.region_style region_style,
1899 fpr.num_columns number_of_format_columns,
1900 art.name region_name,
1901 ao.validation_api_pkg object_validation_api_pkg,
1902 ao.validation_api_proc object_validation_api_proc,
1903 ao.defaulting_api_pkg object_defaulting_api_pkg,
1904 ao.defaulting_api_proc object_defaulting_api_proc,
1905 ar.region_validation_api_pkg region_validation_api_pkg,
1906 ar.region_validation_api_proc region_validation_api_proc,
1907 ar.region_defaulting_api_pkg region_defaulting_api_pkg,
1908 ar.region_defaulting_api_proc region_defaulting_api_proc,
1909 fpr.display_sequence display_sequence
1910 INTO l_region_style ,
1911 l_number_of_format_columns ,
1912 l_region_name ,
1913 l_object_validation_api_pkg ,
1914 l_object_validation_api_proc,
1915 l_object_defaulting_api_pkg ,
1916 l_object_defaulting_api_proc,
1917 l_region_validation_api_pkg ,
1918 l_region_validation_api_proc,
1919 l_region_defaulting_api_pkg ,
1920 l_region_defaulting_api_proc,
1921 l_display_sequence
1922 FROM ak_flow_page_regions fpr,
1923 ak_regions ar,
1924 ak_regions_tl art,
1925 ak_objects ao
1926 WHERE fpr.flow_application_id = p_region_rec.flow_application_id
1927 AND fpr.flow_code = p_region_rec.flow_code
1928 AND fpr.page_application_id = p_region_rec.page_application_id
1929 AND fpr.page_code = p_region_rec.page_code
1930 AND fpr.region_application_id = p_region_rec.region_application_id
1931 AND fpr.region_code = p_region_rec.region_code
1932 AND ar.region_application_id = fpr.region_application_id
1933 AND ar.region_code = fpr.region_code
1934 AND art.region_application_id = ar.region_application_id
1935 AND art.region_code = ar.region_code
1936 AND art.language = userenv('LANG')
1937 AND ao.database_object_name = ar.database_object_name;
1938
1939 EXCEPTION
1940 WHEN NO_DATA_FOUND THEN
1941 -- If AK_FLOW_PAGE_REGIONS does not have a record (i.e. this
1942 -- call is for an LOV (i.e. no flow) then get the default information
1943 -- from AK_REGIONS directly
1944 SELECT ar.region_style region_style,
1945 ar.num_columns number_of_format_columns,
1946 art.name region_name,
1947 ao.validation_api_pkg object_validation_api_pkg,
1948 ao.validation_api_proc object_validation_api_proc,
1949 ao.defaulting_api_pkg object_defaulting_api_pkg,
1950 ao.defaulting_api_proc object_defaulting_api_proc,
1951 ar.region_validation_api_pkg region_validation_api_pkg,
1952 ar.region_validation_api_proc region_validation_api_proc,
1953 ar.region_defaulting_api_pkg region_defaulting_api_pkg,
1954 ar.region_defaulting_api_proc region_defaulting_api_proc,
1955 null
1956 INTO l_region_style ,
1957 l_number_of_format_columns ,
1958 l_region_name ,
1959 l_object_validation_api_pkg ,
1960 l_object_validation_api_proc,
1961 l_object_defaulting_api_pkg ,
1962 l_object_defaulting_api_proc,
1963 l_region_validation_api_pkg ,
1964 l_region_validation_api_proc,
1965 l_region_defaulting_api_pkg ,
1966 l_region_defaulting_api_proc,
1967 l_display_sequence
1968 FROM ak_regions ar,
1969 ak_regions_tl art,
1970 ak_objects ao
1971 WHERE ar.region_application_id = p_region_rec.region_application_id
1972 AND ar.region_code = p_region_rec.region_code
1973 AND art.region_application_id = ar.region_application_id
1974 AND art.region_code = ar.region_code
1975 AND art.language = userenv('LANG')
1976 AND ao.database_object_name = ar.database_object_name;
1977 WHEN OTHERS THEN RAISE;
1978 END;
1979
1980 --
1981 -- Add region defintion child g_regions_table.
1982 --
1983 i := p_region_rec.region_rec_id;
1984 g_regions_table(i).region_rec_id := i;
1985 g_regions_table(i).parent_region_rec_id := p_region_rec.parent_region_rec_id;
1986 g_regions_table(i).flow_application_id := p_region_rec.flow_application_id;
1990 g_regions_table(i).region_application_id := p_region_rec.region_application_id;
1987 g_regions_table(i).flow_code := p_region_rec.flow_code;
1988 g_regions_table(i).page_application_id := p_region_rec.page_application_id;
1989 g_regions_table(i).page_code := p_region_rec.page_code;
1991 g_regions_table(i).region_code := p_region_rec.region_code;
1992 g_regions_table(i).name := l_region_name;
1993 g_regions_table(i).region_style := l_region_style;
1994 g_regions_table(i).primary_key_name := p_region_rec.primary_key_name;
1995 g_regions_table(i).number_of_format_columns := l_number_of_format_columns;
1996 g_regions_table(i).object_validation_api_pkg := l_object_validation_api_pkg;
1997 g_regions_table(i).object_validation_api_proc := l_object_validation_api_proc;
1998 g_regions_table(i).object_defaulting_api_pkg := l_object_defaulting_api_pkg;
1999 g_regions_table(i).object_defaulting_api_proc := l_object_defaulting_api_proc;
2000 g_regions_table(i).region_validation_api_pkg := l_region_validation_api_pkg;
2001 g_regions_table(i).region_validation_api_proc := l_region_validation_api_proc;
2002 g_regions_table(i).region_defaulting_api_pkg := l_region_defaulting_api_pkg;
2003 g_regions_table(i).region_defaulting_api_proc := l_region_defaulting_api_proc;
2004 g_regions_table(i).display_sequence := l_display_sequence;
2005 g_regions_table(i).sql_select := p_select;
2006 g_regions_table(i).sql_from := p_from;
2007 g_regions_table(i).sql_where := p_where;
2008 g_regions_table(i).sql_order_by := p_order_by;
2009
2010 -- add records to bind values table for this region
2011
2012 -- first add the bind variables for the passed PK values (if any)
2013 DECLARE
2014 bind_index number := 1;
2015 j number := p_key_column_values.FIRST;
2016 k number := nvl(g_regions_bind_table.LAST,0) + 1;
2017 BEGIN
2018 FOR key_index in 1..(p_key_column_count) LOOP
2019 -- only non null values have bind variables
2020 IF p_key_column_values(j) is not null THEN
2021 g_regions_bind_table(k).region_rec_id := i;
2022 g_regions_bind_table(k).name := 'BIND'|| j;
2023 g_regions_bind_table(k).value := p_key_column_values(j);
2024 print_debug('adding to bind table: '||k||' region='||
2025 i||' name='||'BIND'||j||' value='||
2026 p_key_column_values(j));
2027 j := p_key_column_values.NEXT(j);
2028 k := k+1;
2029 END IF;
2030 END LOOP;
2031 END;
2032
2033 -- add in binds from p_where_binds
2034 DECLARE
2035 bind_index number := 1;
2036 j number := p_where_binds.FIRST;
2037 k number := nvl(g_regions_bind_table.LAST,0) + 1;
2038 BEGIN
2039 FOR key_index in 1..(p_where_binds.COUNT) LOOP
2040 g_regions_bind_table(k).region_rec_id := i;
2041 g_regions_bind_table(k).name := p_where_binds(j).name;
2042 g_regions_bind_table(k).value := p_where_binds(j).value;
2043 print_debug('adding to bind table: '||k||' region='||
2044 i||' name='||p_where_binds(j).name||' value='||p_where_binds(j).value);
2045 j := p_where_binds.NEXT(j);
2046 k := k+1;
2047 END LOOP;
2048 END;
2049
2050 -- add in binds from p_rls_binds
2051 DECLARE
2052 bind_index number := 1;
2053 j number := p_rls_binds.FIRST;
2054 k number := nvl(g_regions_bind_table.LAST,0) + 1;
2055 BEGIN
2056 FOR key_index in 1..(p_rls_binds.COUNT) LOOP
2057 g_regions_bind_table(k).region_rec_id := i;
2058 g_regions_bind_table(k).name := p_rls_binds(j).name;
2059 g_regions_bind_table(k).value := p_rls_binds(j).value;
2060 print_debug('adding to bind table: '||k||' region='||
2061 i||' name='||p_rls_binds(j).name||' value='||p_rls_binds(j).value);
2062 j := p_rls_binds.NEXT(j);
2063 k := k+1;
2064 END LOOP;
2065 END;
2066
2067 END create_region_record;
2068
2069
2070 -- ==============================================
2071 -- GET_NEW_KEY_VALUES |
2072 -- ==============================================
2073 PROCEDURE get_new_key_values
2074 (
2075 p_db_object_name IN varchar2,
2076 p_current_key_columns IN rel_key_tab,
2077 p_current_key_values IN rel_key_value_tab,
2078 p_new_key_columns IN rel_key_tab,
2079 p_new_key_values OUT NOCOPY rel_key_value_tab
2080 )
2081 IS
2082
2083 c integer;
2084 l_retval number;
2085 l_sql_statement varchar2(2000) := 'SELECT ';
2086 x integer;
2087 y integer;
2088 l_new_key_values rel_key_value_tab;
2089
2090 BEGIN
2091 print_debug('** In function: get_new_key_values **');
2092
2093 FOR x in 0..p_new_key_columns.count - 1 LOOP
2094 IF (x > 0) THEN
2095 l_sql_statement := l_sql_statement || ', ';
2096 END IF;
2097
2098 IF p_new_key_columns(x).is_date THEN
2099 l_sql_statement := l_sql_statement || 'TO_CHAR('||
2100 p_new_key_columns(x).name || ',''YYYY/MM/DD HH24:MI:SS'')';
2101 ELSE
2102 l_sql_statement := l_sql_statement || 'SUBSTR('||
2103 p_new_key_columns(x).name || ',1,4000)';
2104 END IF;
2105 END LOOP;
2106
2107 -- Add FROM clause
2108
2109 l_sql_statement := l_sql_statement ||' FROM '|| p_db_object_name;
2110
2111 -- Add WHERE clause
2112
2113 l_sql_statement := l_sql_statement || ' WHERE ';
2114
2115 FOR y in 0..p_current_key_columns.count - 1 LOOP
2116 IF (y > 0) THEN
2117 l_sql_statement := l_sql_statement || ' AND ';
2118 END IF;
2119
2120 IF (p_current_key_values(y) is null) THEN
2121 l_sql_statement:=l_sql_statement||p_current_key_columns(y).name||
2125 l_sql_statement:= l_sql_statement || p_current_key_columns(y).name ||
2122 ' is NULL';
2123 ELSE
2124 IF p_current_key_columns(y).is_date THEN
2126 ' = TO_DATE(:BIND' || to_char(y+1) ||',''YYYY/MM/DD HH24:MI:SS'')';
2127 ELSE
2128 l_sql_statement:= l_sql_statement || p_current_key_columns(y).name ||
2129 ' = :BIND' || to_char(y+1);
2130 END IF;
2131 END IF;
2132
2133 END LOOP;
2134
2135 ak_query_pkg.sql_stmt := l_sql_statement;
2136
2137 -- Now execute the select statement built above
2138 c := dbms_sql.open_cursor;
2139 dbms_sql.parse(c, l_sql_statement, dbms_sql.v7);
2140
2141 -- Bind Values
2142
2143 bind_sql(c,p_current_key_values);
2144
2145 -- Define select list data types
2146 FOR i in 1..p_new_key_columns.count LOOP
2147 dbms_sql.define_column(c, i, NULL, 4000);
2148 END LOOP;
2149
2150 l_retval := dbms_sql.execute(c);
2151
2152 -- Fetch the first row from SQL statement (there should be none or one)
2153
2154 l_retval := dbms_sql.fetch_rows(c);
2155 -- If the fetch returns a row then get new values, else no rows were
2156 -- returned. This may happen when a FK
2157 IF l_retval = 0 THEN
2158 RAISE NO_DATA_FOUND;
2159 END IF;
2160
2161 -- Retrieve column values
2162 FOR i in 1..p_new_key_columns.count LOOP
2163 print_debug('New Key Column'||to_char(i));
2164 dbms_sql.column_value(c, i, l_new_key_values(i - 1));
2165 print_debug(' value: '||l_new_key_values(i - 1));
2166 END LOOP;
2167
2168 -- Assign values to OUT parameters
2169
2170 p_new_key_values := l_new_key_values;
2171 dbms_sql.close_cursor(c);
2172
2173 END;
2174
2175 -- ======================================================
2176 -- get_fk_columns |
2177 -- ======================================================
2178 PROCEDURE get_fk_columns
2179 ( p_foreign_key_name IN varchar2,
2180 p_fk_column_tab OUT NOCOPY rel_key_tab )
2181 IS
2182
2183 CURSOR fk_cur
2184 (
2185 foreign_key_name_param VARCHAR2
2186 )
2187 IS
2188 SELECT aoa.column_name foreign_key_column_name, aa.data_type
2189 FROM ak_foreign_keys afk,
2190 ak_foreign_key_columns afkc,
2191 ak_object_attributes aoa,
2192 ak_attributes aa
2193 WHERE
2194 afk.database_object_name = aoa.database_object_name
2195 AND afkc.attribute_application_id = aoa.attribute_application_id
2196 AND afkc.attribute_code = aoa.attribute_code
2197 AND afkc.foreign_key_name = afk.foreign_key_name
2198 AND afk.foreign_key_name = foreign_key_name_param
2199 AND aoa.attribute_code = aa.attribute_code
2200 AND aoa.attribute_application_id = aa.attribute_application_id
2201 ORDER BY afkc.foreign_key_sequence;
2202
2203 fk_cur_rec fk_cur%rowtype;
2204 i integer := 0;
2205 BEGIN
2206 print_debug('** In function: get_fk_columns');
2207 print_debug('foreign_key_name='||p_foreign_key_name);
2208
2209 OPEN fk_cur(p_foreign_key_name);
2210 LOOP
2211 FETCH fk_cur INTO fk_cur_rec;
2212 EXIT WHEN fk_cur%NOTFOUND;
2213 p_fk_column_tab(i).name := fk_cur_rec.foreign_key_column_name;
2214 if (fk_cur_rec.data_type = 'DATE' or fk_cur_rec.data_type = 'DATETIME') THEN
2215 p_fk_column_tab(i).is_date := TRUE;
2216 else
2217 p_fk_column_tab(i).is_date := FALSE;
2218 end if;
2219 i := i + 1;
2220 END LOOP;
2221 CLOSE fk_cur;
2222
2223 END get_fk_columns;
2224
2225 -- ======================================================
2226 -- get_uk_columns |
2227 -- ======================================================
2228 PROCEDURE get_uk_columns
2229 ( p_unique_key_name IN varchar2,
2230 p_uk_column_tab OUT NOCOPY rel_key_tab )
2231 IS
2232
2233 CURSOR uk_cur
2234 (
2235 unique_key_name_param VARCHAR2
2236 )
2237 IS
2238 SELECT aoa.column_name unique_key_column_name, aa.data_type
2239 FROM ak_unique_keys auk,
2240 ak_unique_key_columns aukc,
2241 ak_object_attributes aoa,
2242 ak_attributes aa
2243 WHERE
2244 auk.database_object_name = aoa.database_object_name
2245 AND aukc.attribute_application_id = aoa.attribute_application_id
2246 AND aukc.attribute_code = aoa.attribute_code
2247 AND aukc.unique_key_name = auk.unique_key_name
2248 AND auk.unique_key_name = unique_key_name_param
2249 AND aoa.attribute_code = aa.attribute_code
2250 AND aoa.attribute_application_id = aa.attribute_application_id
2251 ORDER BY aukc.unique_key_sequence;
2252
2253 uk_cur_rec uk_cur%rowtype;
2254 i integer := 0;
2255 BEGIN
2256 print_debug('** In function: get_uk_columns (UK='||
2257 p_unique_key_name||')');
2258
2259 OPEN uk_cur(p_unique_key_name);
2260 LOOP
2261 FETCH uk_cur INTO uk_cur_rec;
2262 EXIT WHEN uk_cur%NOTFOUND;
2263 p_uk_column_tab(i).name := uk_cur_rec.unique_key_column_name;
2264 if (uk_cur_rec.data_type = 'DATE' or uk_cur_rec.data_type = 'DATETIME') THEN
2265 p_uk_column_tab(i).is_date := TRUE;
2266 else
2267 p_uk_column_tab(i).is_date := FALSE;
2268 end if;
2269 i := i + 1;
2270 END LOOP;
2271 CLOSE uk_cur;
2272
2273 END get_uk_columns;
2274
2275 -- ======================================================
2276 -- PROCESS_CHILDREN |
2277 -- ======================================================
2278 PROCEDURE process_children
2279 (
2280 p_parent IN region_rec,
2284 p_child_page_code IN varchar2,
2281 p_parent_key_columns IN rel_key_tab,
2282 p_parent_key_values IN rel_key_value_tab,
2283 p_child_page_appl_id IN number,
2285 p_responsibility_id IN number,
2286 p_user_id IN number,
2287 p_return_node_display_only IN boolean,
2288 p_display_region IN boolean,
2289 p_use_subquery IN boolean,
2290 p_range_low IN number,
2291 p_range_high IN number,
2292 p_where_clause IN Varchar2,
2293 p_where_binds IN bind_tab)
2294 IS
2295
2296 l_parent_key_columns rel_key_tab;
2297 l_child region_rec;
2298 l_child_key_columns rel_key_tab;
2299 l_child_key_values rel_key_value_tab;
2300 l_relations_table relations_table_type;
2301
2302 -- set defaults due to gscc standard
2303 l_range_low number := 0;
2304 l_range_high number := MAXROWNUM;
2305 l_use_subquery boolean := FALSE;
2306
2307 BEGIN
2308 print_debug('** in function: process_children ** ');
2309
2310 -- If no key_columns where passed then get them
2311 -- This can happen if the original call to execute_query didn't pass
2312 -- a PK
2313 IF p_parent_key_columns.count = 0 THEN
2314 get_uk_columns(p_parent.primary_key_name,l_parent_key_columns);
2315 ELSE
2316 l_parent_key_columns := p_parent_key_columns;
2317 END IF;
2318
2319 -- For each row recurse down to get it children
2320
2321 ak_query_pkg.load_relations(p_parent,
2322 p_child_page_appl_id,
2323 p_child_page_code,
2324 l_relations_table);
2325
2326 print_debug('Found '||to_char(l_relations_table.count)||
2327 ' relations');
2328 IF l_relations_table.count > 0 THEN
2329 FOR i in 0..(l_relations_table.count - 1) LOOP
2330
2331 print_debug('relation number = '||to_char(i+1));
2332
2333 --
2334 -- Get columns and values for child
2335 --
2336
2337 -- determine direction of FK
2338 -- check if the parent object is different than the FK object
2339 IF p_parent.database_object_name <>
2340 l_relations_table(i).fk_db_object_name THEN
2341 -- relation is PK->FK
2342 print_debug('relation is PK->FK');
2343
2344 -- First get the key_columns for the child from the FK
2345 get_fk_columns(l_relations_table(i).foreign_key_name,
2346 l_child_key_columns);
2347
2348 -- Now get the appropriate values to bind to these FK columns
2349
2350 -- Since we are going from a UK on the parent to a FK on the child
2351 -- if the passed UK is the same as the UK referenced by the FK
2352 -- the passed values can be used
2353 IF (p_parent.primary_key_name =
2354 l_relations_table(i).fk_unique_key_name) THEN
2355 print_debug('An easy join');
2356 l_child_key_values := p_parent_key_values;
2357 ELSE
2358 -- This is a diferent UK, therefore we need to convert from one
2359 -- to the other
2360 print_debug('Not an easy join');
2361
2362 DECLARE
2363 l_key_columns rel_key_tab;
2364 BEGIN
2365 get_uk_columns(l_relations_table(i).fk_unique_key_name,
2366 l_key_columns);
2367 get_new_key_values(
2368 p_parent.database_object_name,
2369 l_parent_key_columns,
2370 p_parent_key_values,
2371 l_key_columns,
2372 l_child_key_values);
2373 END;
2374 END IF;
2375
2376 ELSE
2377 -- Relation is FK->PK
2378 print_debug('relation is FK->PK');
2379
2380 -- First get the key_columns for the child from the UK that the FK
2381 -- references
2382 get_uk_columns(l_relations_table(i).fk_unique_key_name,
2383 l_child_key_columns);
2384
2385 -- Now get the appropriate values to bind to these UK columns
2386
2387 -- Since we are going from a FK on the parent to a UK on the child
2388 -- we need to convert the passed parent UK values to the FK values
2389 print_debug('Not an easy join');
2390
2391 DECLARE
2392 l_key_columns rel_key_tab;
2393 BEGIN
2394 get_fk_columns(l_relations_table(i).foreign_key_name,
2395 l_key_columns);
2396 get_new_key_values(
2397 p_parent.database_object_name,
2398 l_parent_key_columns,
2399 p_parent_key_values,
2400 l_key_columns,
2401 l_child_key_values);
2402 END;
2403
2404 END IF;
2405
2406 -- Setup new region
2407 l_child.region_rec_id := g_regions_table.count;
2408 l_child.flow_application_id :=
2409 p_parent.flow_application_id;
2410 l_child.flow_code := p_parent.flow_code;
2411 l_child.page_application_id :=
2412 l_relations_table(i).to_page_appl_id;
2413 l_child.page_code := l_relations_table(i).to_page_code;
2414 l_child.region_application_id :=
2415 l_relations_table(i).to_region_appl_id;
2416 l_child.region_code := l_relations_table(i).to_region_code;
2417 l_child.primary_key_name :=
2418 l_relations_table(i).to_obj_unique_key;
2419 l_child.database_object_name :=
2420 l_relations_table(i).to_db_object_name;
2421 --
2422 -- If parent region is not displayed, parent_region_rec_id of
2423 -- child region should point to the grandparent region.
2424 --
2425
2426 IF (p_display_region = FALSE) THEN
2427 l_child.parent_region_rec_id := p_parent.parent_region_rec_id;
2428 ELSE
2429 l_child.parent_region_rec_id := p_parent.region_rec_id;
2430 END IF;
2431
2432 --
2436 -- set defaults due to gscc standard
2433 -- Call do_execute_query recursively to get children
2434 --
2435
2437 l_range_low := nvl(p_range_low, 0);
2438 l_range_high := nvl(p_range_high, MAXROWNUM);
2439 l_use_subquery := nvl(p_use_subquery, FALSE);
2440
2441 print_debug('before calling do_execute_query recursively');
2442 do_execute_query(l_child,
2443 l_child_key_columns,
2444 l_child_key_values,
2445 l_child.page_application_id,
2446 l_child.page_code,
2447 p_where_clause, -- = null if p_return_parents = true
2448 p_where_binds,
2449 null,
2450 p_responsibility_id,
2451 p_user_id,
2452 TRUE,
2453 TRUE,
2454 p_return_node_display_only,
2455 l_relations_table(i).to_display_region,
2456 l_range_low,
2457 l_range_high,
2458 null,
2459 l_use_subquery);
2460 print_debug('after calling do_execute_query recursively');
2461 END LOOP;
2462 END IF;
2463 END process_children;
2464
2465 function getSecuredWhere(
2466 p_region_rec IN region_rec,
2467 p_responsibility_id IN number,
2468 p_user_id IN number,
2469 p_where_clause IN varchar2,
2470 p_order_by_clause IN varchar2,
2471 p_return_node_display_only IN boolean,
2472 p_display_region IN boolean
2473 ) return varchar2
2474
2475 IS
2476 where_secured varchar2(12000) := NULL;
2477
2478
2479 CURSOR select_cur
2480 (
2481 p_child_region_appl_id NUMBER,
2482 p_child_region_code VARCHAR2,
2483 p_responsibility_id NUMBER,
2484 p_node_display_criteria VARCHAR2
2485 )
2486 IS
2487 -- Select region_items that are also object_attributes
2488 SELECT aoa.column_name column_name,
2489 ari.display_sequence display_sequence,
2490 ari.attribute_application_id attribute_application_id,
2491 ari.attribute_code attribute_code,
2492 decode(aei.attribute_code,NULL,'F','T') secured_column,
2493 decode(arsa.attribute_code,NULL,'F','T') rls_column,
2494 decode(ari.icx_custom_call,'INDEX','T','F') indexed_column,
2495 arit.attribute_label_long attribute_label_long,
2496 ari.attribute_label_length attribute_label_length,
2497 aa.attribute_value_length attribute_value_length,
2498 ari.display_value_length display_value_length,
2499 ari.item_style item_style,
2500 ari.bold bold,
2501 ari.italic italic,
2502 ari.vertical_alignment vertical_alignment,
2503 ari.horizontal_alignment horizontal_alignment,
2504 ari.object_attribute_flag object_attribute_flag,
2505 ari.node_query_flag node_query_flag,
2506 ari.node_display_flag node_display_flag,
2507 ari.update_flag update_flag,
2508 ari.required_flag required_flag,
2509 ari.icx_custom_call icx_custom_call,
2510 aoa.validation_api_pkg object_validation_api_pkg,
2511 aoa.validation_api_proc object_validation_api_proc,
2512 aoa.defaulting_api_pkg object_defaulting_api_pkg,
2513 aoa.defaulting_api_proc object_defaulting_api_proc,
2514 ari.region_validation_api_pkg region_validation_api_pkg,
2515 ari.region_validation_api_proc region_validation_api_proc,
2516 ari.region_defaulting_api_pkg region_defaulting_api_pkg,
2517 ari.region_defaulting_api_proc region_defaulting_api_proc,
2518 ari.lov_foreign_key_name lov_foreign_key_name,
2519 ari.lov_region_application_id lov_region_application_id,
2520 ari.lov_region_code lov_region_code,
2521 ari.lov_attribute_application_id lov_attribute_application_id,
2522 ari.lov_attribute_code lov_attribute_code,
2523 ari.lov_default_flag lov_default_flag,
2524 ari.order_sequence order_sequence,
2525 ari.order_direction order_direction,
2526 aa.data_type data_type
2527 FROM ak_object_attributes aoa,
2528 ak_excluded_items aei,
2529 ak_resp_security_attributes arsa,
2530 ak_attributes aa,
2531 ak_regions ar,
2532 ak_region_items_tl arit,
2533 ak_region_items ari
2534 WHERE ari.object_attribute_flag = 'Y'
2535 AND aoa.attribute_application_id = ari.attribute_application_id
2536 AND aoa.attribute_code = ari.attribute_code
2537 AND aoa.database_object_name = ar.database_object_name
2538 AND ar.region_application_id = ari.region_application_id
2539 AND ar.region_code = ari.region_code
2540 AND ari.region_code = p_child_region_code
2541 AND ari.region_application_id = p_child_region_appl_id
2542 AND ari.node_display_flag =
2543 decode(p_node_display_criteria,'Y','Y',ari.node_display_flag)
2544 AND arit.region_code = ari.region_code
2545 AND arit.region_application_id = ari.region_application_id
2546 AND arit.attribute_code = ari.attribute_code
2547 AND arit.attribute_application_id = ari.attribute_application_id
2548 AND arit.language = userenv('LANG')
2549 AND aei.responsibility_id (+) = p_responsibility_id
2550 AND aei.attribute_application_id (+) = ari.attribute_application_id
2551 AND aei.attribute_code (+) = ari.attribute_code
2552 AND arsa.responsibility_id (+) = p_responsibility_id
2553 AND arsa.attribute_application_id (+) = ari.attribute_application_id
2554 AND arsa.attribute_code (+) = ari.attribute_code
2555 AND ari.attribute_code = aa.attribute_code
2559 SELECT null column_name,
2556 AND ari.attribute_application_id = aa.attribute_application_id
2557 UNION ALL
2558 -- Select region_items that are not object attributes
2560 ari.display_sequence display_sequence,
2561 ari.attribute_application_id attribute_application_id,
2562 ari.attribute_code attribute_code,
2563 decode(aei.attribute_code,NULL,'F','T') secured_column,
2564 decode(arsa.attribute_code,NULL,'F','T') rls_column,
2565 decode(ari.icx_custom_call,'INDEX','T','F') indexed_column,
2566 arit.attribute_label_long attribute_label_long,
2567 ari.attribute_label_length attribute_label_length,
2568 aa.attribute_value_length attribute_value_length,
2569 ari.display_value_length display_value_length,
2570 ari.item_style item_style,
2571 ari.bold bold,
2572 ari.italic italic,
2573 ari.vertical_alignment vertical_alignment,
2574 ari.horizontal_alignment horizontal_alignment,
2575 ari.object_attribute_flag object_attribute_flag,
2576 ari.node_query_flag node_query_flag,
2577 ari.node_display_flag node_display_flag,
2578 ari.update_flag update_flag,
2579 ari.required_flag required_flag,
2580 ari.icx_custom_call icx_custom_call,
2581 null object_validation_api_pkg,
2582 null object_validation_api_proc,
2583 null object_defaulting_api_pkg,
2584 null object_defaulting_api_proc,
2585 ari.region_validation_api_pkg region_validation_api_pkg,
2586 ari.region_validation_api_proc region_validation_api_proc,
2587 ari.region_defaulting_api_pkg region_defaulting_api_pkg,
2588 ari.region_defaulting_api_proc region_defaulting_api_proc,
2589 ari.lov_foreign_key_name lov_foreign_key_name,
2590 ari.lov_region_application_id lov_region_application_id,
2591 ari.lov_region_code lov_region_code,
2592 ari.lov_attribute_application_id lov_attribute_application_id,
2593 ari.lov_attribute_code lov_attribute_code,
2594 ari.lov_default_flag lov_default_flag,
2595 ari.order_sequence order_sequence,
2596 ari.order_direction order_direction,
2597 aa.data_type data_type
2598 FROM ak_excluded_items aei,
2599 ak_resp_security_attributes arsa,
2600 ak_attributes aa,
2601 ak_region_items_tl arit,
2602 ak_region_items ari
2603 WHERE ari.object_attribute_flag <> 'Y'
2604 AND ari.region_code = p_child_region_code
2605 AND ari.region_application_id = p_child_region_appl_id
2606 AND ari.node_display_flag =
2607 decode(p_node_display_criteria,'Y','Y',ari.node_display_flag)
2608 AND arit.region_code = ari.region_code
2609 AND arit.region_application_id = ari.region_application_id
2610 AND arit.attribute_code = ari.attribute_code
2611 AND arit.attribute_application_id = ari.attribute_application_id
2612 AND arit.language = userenv('LANG')
2613 AND aei.responsibility_id (+) = p_responsibility_id
2614 AND aei.attribute_application_id (+) = ari.attribute_application_id
2615 AND aei.attribute_code (+) = ari.attribute_code
2616 AND arsa.responsibility_id (+) = p_responsibility_id
2617 AND arsa.attribute_application_id (+) = ari.attribute_application_id
2618 AND arsa.attribute_code (+) = ari.attribute_code
2619 AND ari.attribute_code = aa.attribute_code
2620 AND ari.attribute_application_id = aa.attribute_application_id
2621 ORDER BY 2;
2622
2623
2624 select_rec select_cur%rowtype;
2625
2626
2627 CURSOR attr_values_cur
2628 (
2629 p_user_id NUMBER,
2630 p_attribute_appl_id NUMBER,
2631 p_attribute_code VARCHAR2,
2632 p_responsibility_id NUMBER
2633 )
2634 IS
2635 SELECT nvl(to_char(number_value),nvl(varchar2_value, to_char(date_value))) sec_value
2636 FROM ak_web_user_sec_attr_values awusav
2637 WHERE awusav.web_user_id = p_user_id
2638 AND awusav.attribute_application_id = p_attribute_appl_id
2639 AND awusav.attribute_code = p_attribute_code
2640 union
2641 SELECT nvl(to_char(number_value),
2642 nvl(varchar2_value,to_char(date_value))) sec_value
2643 FROM AK_RESP_SECURITY_ATTR_VALUES arsav
2644 WHERE arsav.responsibility_id = p_responsibility_id
2645 AND arsav.attribute_application_id = p_attribute_appl_id
2646 AND arsav.attribute_code = p_attribute_code;
2647
2648 attr_values_rec attr_values_cur%rowtype;
2649
2650 l_query_stmt varchar2(32000);
2651 select_count1 number := 0;
2652 select_count2 number := 0;
2653 select_count number := 0;
2654 row_count number := 0;
2655 select_stmt varchar2(20000);
2656 where_stmt varchar2(20000);
2657 order_by_stmt varchar2(1000);
2658 order_by_col_tab rel_name_tab;
2659 order_by_dir_tab rel_name_tab;
2660 node_display_criteria varchar2(1);
2661 results_table_value_id integer := 1;
2662 where_temp varchar2(10000);
2663 i integer;
2664 svalue_datatype varchar2(40) := NULL;
2665
2666 BEGIN
2670
2667 print_debug('** In function: getSecuredWhere');
2668
2669 l_query_stmt := 'SELECT ';
2671 print_debug('retrieve item/attribute information');
2672 --
2673 -- When constructing the SQL statement, choose
2674 -- between all columns or only those that are marked as
2675 -- node_display_flag = 'Y'.
2676 --
2677 IF p_return_node_display_only THEN
2678 node_display_criteria := 'Y';
2679 ELSE
2680 node_display_criteria := null;
2681 END IF;
2682
2683 --
2684 -- Construct the SQL statement by selecting
2685 -- the region items
2686 --
2687 OPEN select_cur(p_region_rec.region_application_id,
2688 p_region_rec.region_code,
2689 p_responsibility_id,
2690 node_display_criteria);
2691
2692 LOOP
2693 FETCH select_cur INTO select_rec;
2694 EXIT WHEN select_cur%NOTFOUND;
2695 row_count := select_cur%ROWCOUNT;
2696
2697 print_debug('select column1 = '||select_rec.column_name);
2698 print_debug('secure value1 = '||select_rec.rls_column);
2699 print_debug('obj attr flag = '||select_rec.object_attribute_flag);
2700
2701 IF p_display_region THEN
2702
2703 print_debug ( 'Secured Value is -> ' || select_rec.rls_column);
2704 --
2705 -- Item has secured VALUES if it was found on the
2706 -- ak_web_user_sec_attr_values table or ak_resp_security_attr_values table
2707 -- If found, then the
2708 -- record(s) will contain value(s) to use in the where clause
2709 -- to limit row selection. If item was suppose to have secured
2710 -- values, but none where found then create a where clause
2711 -- containing '= NULL' which forces no rows to be found.
2712 --
2713 IF select_rec.rls_column = 'T' THEN
2714 where_temp := NULL;
2715 svalue_datatype := NULL;
2716
2717 /** Verify if there is bad data **/
2718
2719 SELECT count(*)
2720 INTO select_count1
2721 FROM ak_web_user_sec_attr_values awusav
2722 WHERE awusav.web_user_id = p_user_id
2723 AND awusav.attribute_application_id = select_rec.attribute_application_id
2724 AND awusav.attribute_code = select_rec.attribute_code
2725 AND (( varchar2_value is not null and date_value is not null) or
2726 ( varchar2_value is not null and number_value is not null) or
2727 ( date_value is not null and number_value is not null));
2728
2729 SELECT count(*)
2730 INTO select_count2
2731 FROM ak_resp_security_attr_values arsav
2732 WHERE arsav.responsibility_id = p_responsibility_id
2733 AND arsav.attribute_application_id = select_rec.attribute_application_id
2734 AND arsav.attribute_code = select_rec.attribute_code
2735 AND (( varchar2_value is not null and date_value is not null) or
2736 ( varchar2_value is not null and number_value is not null) or
2737 ( date_value is not null and number_value is not null));
2738
2739 select_count := select_count1 + select_count2;
2740 if select_count = 0 then
2741 FOR attr_values_rec IN attr_values_cur(p_user_id,
2742 select_rec.attribute_application_id,
2743 select_rec.attribute_code,
2744 p_responsibility_id) LOOP
2745 IF where_temp IS NULL THEN
2746 where_temp := '('||select_rec.column_name || ' IN (';
2747 ELSE
2748 where_temp := where_temp || ', ';
2749 END IF;
2750
2751 IF attr_values_rec.sec_value IS NOT NULL THEN
2752 where_temp := where_temp || '''' || attr_values_rec.sec_value || '''';
2753 END IF;
2754
2755 END LOOP;
2756
2757 end if;
2758
2759 IF where_temp IS NOT NULL THEN
2760 where_temp := where_temp || '))';
2761 ELSE
2762 where_temp := '(' || select_rec.column_name || ' = NULL)';
2763 END IF;
2764 where_secured := where_secured || ' AND ' || where_temp;
2765 END IF;
2766 END IF;
2767 END LOOP;
2768 CLOSE select_cur;
2769 return where_secured;
2770
2771 end getSecuredWhere;
2772
2773 procedure print_debug(dMessage in varchar2) is
2774 begin
2775 if (AK_QUERY_PKG.PRINT_DEBUG_ON) then
2776 null;
2777 -- comment out dbms_output so that adchkdrv would not complain
2778 -- uncomment the following line when debug
2779 --dbms_output.put_line(dMessage);
2780 end if;
2781 end print_debug;
2782
2783 END AK_QUERY_PKG;