[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_num_select OUT NOCOPY number,
p_num_key OUT NOCOPY number,
p_new_key_columns OUT NOCOPY rel_key_tab,
p_rls_binds OUT NOCOPY bind_tab,
p_select OUT NOCOPY varchar2,
p_from OUT NOCOPY varchar2,
p_where OUT NOCOPY varchar2,
p_order_by OUT NOCOPY varchar2
);
p_num_select IN number,
p_num_key IN number,
p_region_rec IN region_rec,
p_display_region IN boolean,
p_key_values OUT NOCOPY rel_key_value_tab
);
p_num_select IN number
);
p_select IN varchar2,
p_from IN varchar2,
p_where IN varchar2,
p_order_by IN varchar2
);
num_select number := 0;
g_regions_table.delete;
g_regions_bind_table.delete;
g_items_table.delete;
g_results_table.delete;
select ao.database_object_name, ao.primary_key_name
into root.database_object_name, root.primary_key_name
from ak_objects ao,
ak_regions ar
where ar.region_code = root.region_code
and ar.region_application_id = root.region_application_id
and ar.database_object_name = ao.database_object_name;
l_num_select number := 0;
l_select varchar2(10000);
l_num_select,
l_num_key,
l_key_columns,
l_rls_binds,
l_select,
l_from,
l_where,
l_order_by);
l_select,
l_from,
l_where,
l_order_by);
l_num_select,
l_num_key,
l_key_columns,
l_rls_binds,
l_select,
l_from,
l_where,
l_order_by);
l_select,
l_from,
l_where,
l_order_by);
ak_query_pkg.define_sql(cursor_id, l_num_select + l_num_key);
l_num_select,
l_num_key,
p_node,
p_display_region,
l_key_values);
SELECT afrr.foreign_key_name foreign_key_name,
afk.unique_key_name fk_unique_key_name,
afk.database_object_name fk_db_object_name,
afrr.from_page_appl_id from_page_appl_id,
afrr.from_page_code from_page_code,
afrr.from_region_appl_id from_region_appl_id,
afrr.from_region_code from_region_code,
ar1.database_object_name from_db_object_name,
ao1.primary_key_name from_obj_unique_key,
decode(afpr1.display_sequence, null, 'N','Y') from_region_disp_flag,
afrr.to_page_appl_id to_page_appl_id,
afrr.to_page_code to_page_code,
afrr.to_region_appl_id to_region_appl_id,
afrr.to_region_code to_region_code,
ar2.database_object_name to_db_object_name,
ao2.primary_key_name to_obj_unique_key,
decode(afpr2.display_sequence, null, 'N','Y') to_region_disp_flag,
decode(afpr2.display_sequence, null, 0 ,afpr2.display_sequence) disp_seq
FROM
ak_flow_region_relations afrr,
ak_regions ar1,
ak_regions ar2,
ak_flow_page_regions afpr1,
ak_flow_page_regions afpr2,
ak_objects ao1,
ak_objects ao2,
ak_foreign_keys afk
WHERE afrr.flow_application_id = flow_appl_id_param
AND afrr.flow_code = flow_code_param
AND afrr.from_page_appl_id = from_page_appl_id_param
AND afrr.from_page_code = from_page_code_param
AND afrr.from_region_appl_id = from_region_appl_id_param
AND afrr.from_region_code = from_region_code_param
AND afrr.to_page_appl_id = NVL(to_page_appl_id_param,afrr.to_page_appl_id)
AND afrr.to_page_code = NVL(to_page_code_param,afrr.to_page_code)
AND afrr.from_region_appl_id = ar1.region_application_id
AND afrr.from_region_code = ar1.region_code
AND afrr.to_region_appl_id = ar2.region_application_id
AND afrr.to_region_code = ar2.region_code
AND afrr.flow_application_id = afpr1.flow_application_id
AND afrr.flow_code = afpr1.flow_code
AND afrr.from_page_appl_id = afpr1.page_application_id
AND afrr.from_page_code = afpr1.page_code
AND afrr.from_region_appl_id = afpr1.region_application_id
AND afrr.from_region_code = afpr1.region_code
AND afrr.flow_application_id = afpr2.flow_application_id
AND afrr.flow_code = afpr2.flow_code
AND afrr.to_page_appl_id = afpr2.page_application_id
AND afrr.to_page_code = afpr2.page_code
AND afrr.to_region_appl_id = afpr2.region_application_id
AND afrr.to_region_code = afpr2.region_code
AND ar1.database_object_name = ao1.database_object_name
AND ar2.database_object_name = ao2.database_object_name
AND afrr.foreign_key_name = afk.foreign_key_name
ORDER BY disp_seq
;
p_num_select OUT NOCOPY number,
p_num_key OUT NOCOPY number,
p_new_key_columns OUT NOCOPY rel_key_tab,
p_rls_binds OUT NOCOPY bind_tab,
p_select OUT NOCOPY varchar2,
p_from OUT NOCOPY varchar2,
p_where OUT NOCOPY varchar2,
p_order_by OUT NOCOPY varchar2
)
IS
CURSOR select_cur
(
p_child_region_appl_id NUMBER,
p_child_region_code VARCHAR2,
p_responsibility_id NUMBER
)
IS
-- Select region_items that are also object_attributes
SELECT aoa.column_name column_name,
ari.display_sequence display_sequence,
ari.attribute_application_id attribute_application_id,
ari.attribute_code attribute_code,
decode(aei.attribute_code,NULL,'F','T') secured_column,
decode(arsa.attribute_code,NULL,'F','T') rls_column,
decode(ari.icx_custom_call,'INDEX','T','F') indexed_column,
arit.attribute_label_long attribute_label_long,
ari.attribute_label_length attribute_label_length,
aa.attribute_value_length attribute_value_length,
ari.display_value_length display_value_length,
ari.item_style item_style,
ari.bold bold,
ari.italic italic,
ari.vertical_alignment vertical_alignment,
ari.horizontal_alignment horizontal_alignment,
ari.object_attribute_flag object_attribute_flag,
ari.node_query_flag node_query_flag,
ari.node_display_flag node_display_flag,
ari.update_flag update_flag,
ari.required_flag required_flag,
ari.icx_custom_call icx_custom_call,
aoa.validation_api_pkg object_validation_api_pkg,
aoa.validation_api_proc object_validation_api_proc,
aoa.defaulting_api_pkg object_defaulting_api_pkg,
aoa.defaulting_api_proc object_defaulting_api_proc,
ari.region_validation_api_pkg region_validation_api_pkg,
ari.region_validation_api_proc region_validation_api_proc,
ari.region_defaulting_api_pkg region_defaulting_api_pkg,
ari.region_defaulting_api_proc region_defaulting_api_proc,
ari.lov_foreign_key_name lov_foreign_key_name,
ari.lov_region_application_id lov_region_application_id,
ari.lov_region_code lov_region_code,
ari.lov_attribute_application_id lov_attribute_application_id,
ari.lov_attribute_code lov_attribute_code,
ari.lov_default_flag lov_default_flag,
ari.order_sequence order_sequence,
ari.order_direction order_direction,
aa.data_type data_type
FROM ak_object_attributes aoa,
ak_excluded_items aei,
ak_resp_security_attributes arsa,
ak_attributes aa,
ak_regions ar,
ak_region_items_tl arit,
ak_region_items ari
WHERE ari.object_attribute_flag = 'Y'
AND aoa.attribute_application_id = ari.attribute_application_id
AND aoa.attribute_code = ari.attribute_code
AND aoa.database_object_name = ar.database_object_name
AND ar.region_application_id = ari.region_application_id
AND ar.region_code = ari.region_code
AND ari.region_code = p_child_region_code
AND ari.region_application_id = p_child_region_appl_id
AND arit.region_code = ari.region_code
AND arit.region_application_id = ari.region_application_id
AND arit.attribute_code = ari.attribute_code
AND arit.attribute_application_id = ari.attribute_application_id
AND arit.language = userenv('LANG')
AND aei.responsibility_id (+) = p_responsibility_id
AND aei.attribute_application_id (+) = ari.attribute_application_id
AND aei.attribute_code (+) = ari.attribute_code
AND arsa.responsibility_id (+) = p_responsibility_id
AND arsa.attribute_application_id (+) = ari.attribute_application_id
AND arsa.attribute_code (+) = ari.attribute_code
AND ari.attribute_code = aa.attribute_code
AND ari.attribute_application_id = aa.attribute_application_id
UNION ALL
-- Select region_items that are not object attributes
SELECT null column_name,
ari.display_sequence display_sequence,
ari.attribute_application_id attribute_application_id,
ari.attribute_code attribute_code,
decode(aei.attribute_code,NULL,'F','T') secured_column,
decode(arsa.attribute_code,NULL,'F','T') rls_column,
decode(ari.icx_custom_call,'INDEX','T','F') indexed_column,
arit.attribute_label_long attribute_label_long,
ari.attribute_label_length attribute_label_length,
aa.attribute_value_length attribute_value_length,
ari.display_value_length display_value_length,
ari.item_style item_style,
ari.bold bold,
ari.italic italic,
ari.vertical_alignment vertical_alignment,
ari.horizontal_alignment horizontal_alignment,
ari.object_attribute_flag object_attribute_flag,
ari.node_query_flag node_query_flag,
ari.node_display_flag node_display_flag,
ari.update_flag update_flag,
ari.required_flag required_flag,
ari.icx_custom_call icx_custom_call,
null object_validation_api_pkg,
null object_validation_api_proc,
null object_defaulting_api_pkg,
null object_defaulting_api_proc,
ari.region_validation_api_pkg region_validation_api_pkg,
ari.region_validation_api_proc region_validation_api_proc,
ari.region_defaulting_api_pkg region_defaulting_api_pkg,
ari.region_defaulting_api_proc region_defaulting_api_proc,
ari.lov_foreign_key_name lov_foreign_key_name,
ari.lov_region_application_id lov_region_application_id,
ari.lov_region_code lov_region_code,
ari.lov_attribute_application_id lov_attribute_application_id,
ari.lov_attribute_code lov_attribute_code,
ari.lov_default_flag lov_default_flag,
ari.order_sequence order_sequence,
ari.order_direction order_direction,
aa.data_type data_type
FROM ak_excluded_items aei,
ak_resp_security_attributes arsa,
ak_attributes aa,
ak_region_items_tl arit,
ak_region_items ari
WHERE ari.object_attribute_flag <> 'Y'
AND ari.region_code = p_child_region_code
AND ari.region_application_id = p_child_region_appl_id
AND arit.region_code = ari.region_code
AND arit.region_application_id = ari.region_application_id
AND arit.attribute_code = ari.attribute_code
AND arit.attribute_application_id = ari.attribute_application_id
AND arit.language = userenv('LANG')
AND aei.responsibility_id (+) = p_responsibility_id
AND aei.attribute_application_id (+) = ari.attribute_application_id
AND aei.attribute_code (+) = ari.attribute_code
AND arsa.responsibility_id (+) = p_responsibility_id
AND arsa.attribute_application_id (+) = ari.attribute_application_id
AND arsa.attribute_code (+) = ari.attribute_code
AND ari.attribute_code = aa.attribute_code
AND ari.attribute_application_id = aa.attribute_application_id
ORDER BY 2;
select_rec select_cur%rowtype;
SELECT nvl(to_char(number_value),
nvl(varchar2_value,to_char(date_value))) sec_value
FROM ak_web_user_sec_attr_values awusav
WHERE awusav.web_user_id = p_user_id
AND awusav.attribute_application_id = p_attribute_appl_id
AND awusav.attribute_code = p_attribute_code
union
SELECT nvl(to_char(number_value),
nvl(varchar2_value,to_char(date_value))) sec_value
FROM AK_RESP_SECURITY_ATTR_VALUES arsav
WHERE arsav.responsibility_id = p_responsibility_id
AND arsav.attribute_application_id = p_attribute_appl_id
AND arsav.attribute_code = p_attribute_code;
select_count number := 0;
select_stmt varchar2(10000);
OPEN select_cur(p_region_rec.region_application_id,
p_region_rec.region_code,
p_responsibility_id);
print_debug('building select list and order by list');
FETCH select_cur INTO select_rec;
EXIT WHEN select_cur%NOTFOUND;
row_count := select_cur%ROWCOUNT;
(p_return_node_display_only and select_rec.node_display_flag = 'Y') ) then
IF select_rec.object_attribute_flag = 'Y' THEN
select_count := select_count + 1;
IF (select_count > 1) THEN
select_stmt := select_stmt || ', ';
if ( select_rec.data_type = 'DATETIME' ) then
select value into l_date_format
from V$NLS_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';
select_stmt := select_stmt ||'TO_CHAR(akq.'||
select_rec.column_name||', '''||
l_date_format||''')';
select_stmt := select_stmt || 'SUBSTR(akq.'||
select_rec.column_name ||',1,4000)';
print_debug('select column = '||select_rec.column_name);
IF select_rec.order_sequence IS NOT NULL THEN
order_by_col_tab(select_rec.order_sequence) := select_rec.column_name;
order_by_dir_tab(select_rec.order_sequence) :=
select_rec.order_direction;
(p_return_node_display_only and select_rec.node_display_flag = 'Y') ) then
--
-- Add item defintion to g_items_table.
--
print_debug('Adding attribute to Items Table = '||
select_rec.attribute_code);
g_items_table(i).attribute_application_id := select_rec.attribute_application_id;
g_items_table(i).attribute_code := select_rec.attribute_code;
g_items_table(i).attribute_label_long := select_rec.attribute_label_long;
g_items_table(i).attribute_label_length := select_rec.attribute_label_length;
g_items_table(i).attribute_value_length := select_rec.attribute_value_length;
g_items_table(i).display_value_length := select_rec.display_value_length;
g_items_table(i).display_sequence := select_rec.display_sequence;
g_items_table(i).item_style := select_rec.item_style;
g_items_table(i).bold := select_rec.bold;
g_items_table(i).italic := select_rec.italic;
g_items_table(i).vertical_alignment := select_rec.vertical_alignment;
g_items_table(i).horizontal_alignment := select_rec.horizontal_alignment;
g_items_table(i).object_attribute_flag := select_rec.object_attribute_flag;
g_items_table(i).node_query_flag := select_rec.node_query_flag;
g_items_table(i).node_display_flag := select_rec.node_display_flag;
g_items_table(i).update_flag := select_rec.update_flag;
g_items_table(i).required_flag := select_rec.required_flag;
g_items_table(i).icx_custom_call := select_rec.icx_custom_call;
g_items_table(i).region_defaulting_api_pkg := select_rec.region_defaulting_api_pkg;
g_items_table(i).region_defaulting_api_proc := select_rec.region_defaulting_api_proc;
g_items_table(i).region_validation_api_pkg := select_rec.region_validation_api_pkg;
g_items_table(i).region_validation_api_proc := select_rec.region_validation_api_proc;
g_items_table(i).object_defaulting_api_pkg := select_rec.object_defaulting_api_pkg;
g_items_table(i).object_defaulting_api_proc := select_rec.object_defaulting_api_proc;
g_items_table(i).object_validation_api_pkg := select_rec.object_validation_api_pkg;
g_items_table(i).object_validation_api_proc := select_rec.object_validation_api_proc;
g_items_table(i).lov_foreign_key_name := select_rec.lov_foreign_key_name;
g_items_table(i).lov_region_application_id := select_rec.lov_region_application_id;
g_items_table(i).lov_region_code := select_rec.lov_region_code;
g_items_table(i).lov_attribute_application_id := select_rec.lov_attribute_application_id;
g_items_table(i).lov_attribute_code := select_rec.lov_attribute_code;
g_items_table(i).lov_default_flag := select_rec.lov_default_flag;
g_items_table(i).secured_column := select_rec.secured_column;
g_items_table(i).indexed_column := select_rec.indexed_column;
g_items_table(i).rls_column := select_rec.rls_column;
IF select_rec.object_attribute_flag = 'Y' THEN
g_items_table(i).value_id := results_table_value_id;
IF select_rec.rls_column = 'T' THEN
-- if there are more than 255 sec attr values, force p_use_subquery
-- to TRUE
rec_count := 0;
for attr_values_rec in attr_values_cur(p_user_id, select_rec.attribute_application_id,
select_rec.attribute_code, p_responsibility_id) loop
rec_count := rec_count + 1;
select_rec.attribute_application_id,
select_rec.attribute_code,
p_responsibility_id) LOOP
i := i + 1;
where_temp := '(akq.'||select_rec.column_name || ' IN (';
to_char(select_count)||'_'||to_char(i);
'BIND'||to_char(select_count)||'_'||to_char(i);
to_char(select_count)||'_'||to_char(i);
'BIND'||to_char(select_count)||'_'||to_char(i);
where_temp := '(akq.' || select_rec.column_name || ' = NULL)';
where_temp := '(akq.'||select_rec.column_name || ' IN ('
|| 'SELECT nvl(number_value,nvl(varchar2_value,date_value)) '
|| 'FROM ak_web_user_sec_attr_values awusav '
|| 'WHERE awusav.web_user_id = :BIND'
|| to_char(select_count) || 'USER_ID '
|| 'AND awusav.attribute_application_id = :BIND'
|| to_char(select_count) || 'ATTR_APPL_ID '
|| 'AND awusav.attribute_code = :BIND'
|| to_char(select_count) || 'ATTR_CODE '
|| 'union '
|| 'SELECT nvl(number_value,nvl(varchar2_value,date_value)) '
|| 'FROM AK_RESP_SECURITY_ATTR_VALUES arsav '
|| 'WHERE arsav.responsibility_id = :BIND'
|| to_char(select_count) || 'RESPONSIBILITY_ID '
|| 'AND arsav.attribute_application_id = :BIND'
|| to_char(select_count) || 'ATTR_APPL_ID '
|| 'AND arsav.attribute_code = :BIND'
|| to_char(select_count) || 'ATTR_CODE ))';
p_rls_binds(i+1).name := 'BIND'||to_char(select_count)||'USER_ID';
p_rls_binds(i+2).name := 'BIND'||to_char(select_count)||
'ATTR_APPL_ID';
p_rls_binds(i+2).value := select_rec.attribute_application_id;
p_rls_binds(i+3).name := 'BIND'||to_char(select_count)||
'ATTR_CODE';
p_rls_binds(i+3).value := select_rec.attribute_code;
p_rls_binds(i+4).name := 'BIND'||to_char(select_count)||'RESPONSIBILITY_ID';
p_num_select := select_count;
print_debug('Select count = '||to_char(select_count));
CLOSE select_cur;
IF select_stmt IS NOT NULL THEN
select_stmt := select_stmt ||', ';
select_stmt := select_stmt || 'TO_CHAR(akq.'||l_uk_column_tab(i).name||
',''YYYY/MM/DD HH24:MI:SS'')';
select_stmt := select_stmt || 'akq.' || l_uk_column_tab(i).name;
j number := select_count + key_count;
select_stmt := select_stmt ||', akq.'|| order_by_col_tab(i);
order_by_stmt := order_by_stmt ||to_char(select_count + uk_column_index + 1)||' '||order_by_dir_tab(i);
p_select := select_stmt;
l_query_stmt := 'SELECT '||select_stmt||' FROM '||
p_region_rec.database_object_name ||' akq' ||
' WHERE '||where_stmt;
p_num_select IN number,
p_num_key IN number,
p_region_rec IN region_rec,
p_display_region IN boolean,
p_key_values OUT NOCOPY rel_key_value_tab
)
IS
key_index number := 0;
FOR display_index in 0..(p_num_select + p_num_key - 1) LOOP
IF (display_index < p_num_select) THEN
--
-- Retrieve display attribute values
--
dbms_sql.column_value(p_cursor_id, display_index + 1,
display_value(display_index));
if (1 <= p_num_select) then
g_results_table(i).value1 := display_value(0);
if (2 <= p_num_select) then
g_results_table(i).value2 := display_value(1);
if (3 <= p_num_select) then
g_results_table(i).value3 := display_value(2);
if (4 <= p_num_select) then
g_results_table(i).value4 := display_value(3);
if (5 <= p_num_select) then
g_results_table(i).value5 := display_value(4);
if (6 <= p_num_select) then
g_results_table(i).value6 := display_value(5);
if (7 <= p_num_select) then
g_results_table(i).value7 := display_value(6);
if (8 <= p_num_select) then
g_results_table(i).value8 := display_value(7);
if (9 <= p_num_select) then
g_results_table(i).value9 := display_value(8);
if (10 <= p_num_select) then
g_results_table(i).value10 := display_value(9);
if (11 <= p_num_select) then
g_results_table(i).value11 := display_value(10);
if (12 <= p_num_select) then
g_results_table(i).value12 := display_value(11);
if (13 <= p_num_select) then
g_results_table(i).value13 := display_value(12);
if (14 <= p_num_select) then
g_results_table(i).value14 := display_value(13);
if (15 <= p_num_select) then
g_results_table(i).value15 := display_value(14);
if (16 <= p_num_select) then
g_results_table(i).value16 := display_value(15);
if (17 <= p_num_select) then
g_results_table(i).value17 := display_value(16);
if (18 <= p_num_select) then
g_results_table(i).value18 := display_value(17);
if (19 <= p_num_select) then
g_results_table(i).value19 := display_value(18);
if (20 <= p_num_select) then
g_results_table(i).value20 := display_value(19);
if (21 <= p_num_select) then
g_results_table(i).value21 := display_value(20);
if (22 <= p_num_select) then
g_results_table(i).value22 := display_value(21);
if (23 <= p_num_select) then
g_results_table(i).value23 := display_value(22);
if (24 <= p_num_select) then
g_results_table(i).value24 := display_value(23);
if (25 <= p_num_select) then
g_results_table(i).value25 := display_value(24);
if (26 <= p_num_select) then
g_results_table(i).value26 := display_value(25);
if (27 <= p_num_select) then
g_results_table(i).value27 := display_value(26);
if (28 <= p_num_select) then
g_results_table(i).value28 := display_value(27);
if (29 <= p_num_select) then
g_results_table(i).value29 := display_value(28);
if (30 <= p_num_select) then
g_results_table(i).value30 := display_value(29);
if (31 <= p_num_select) then
g_results_table(i).value31 := display_value(30);
if (32 <= p_num_select) then
g_results_table(i).value32 := display_value(31);
if (33 <= p_num_select) then
g_results_table(i).value33 := display_value(32);
if (34 <= p_num_select) then
g_results_table(i).value34 := display_value(33);
if (35 <= p_num_select) then
g_results_table(i).value35 := display_value(34);
if (36 <= p_num_select) then
g_results_table(i).value36 := display_value(35);
if (37 <= p_num_select) then
g_results_table(i).value37 := display_value(36);
if (38 <= p_num_select) then
g_results_table(i).value38 := display_value(37);
if (39 <= p_num_select) then
g_results_table(i).value39 := display_value(38);
if (40 <= p_num_select) then
g_results_table(i).value40 := display_value(39);
if (41 <= p_num_select) then
g_results_table(i).value41 := display_value(40);
if (42 <= p_num_select) then
g_results_table(i).value42 := display_value(41);
if (43 <= p_num_select) then
g_results_table(i).value43 := display_value(42);
if (44 <= p_num_select) then
g_results_table(i).value44 := display_value(43);
if (45 <= p_num_select) then
g_results_table(i).value45 := display_value(44);
if (46 <= p_num_select) then
g_results_table(i).value46 := display_value(45);
if (47 <= p_num_select) then
g_results_table(i).value47 := display_value(46);
if (48 <= p_num_select) then
g_results_table(i).value48 := display_value(47);
if (49 <= p_num_select) then
g_results_table(i).value49 := display_value(48);
if (50 <= p_num_select) then
g_results_table(i).value50 := display_value(49);
if (51 <= p_num_select) then
g_results_table(i).value51 := display_value(50);
if (52 <= p_num_select) then
g_results_table(i).value52 := display_value(51);
if (53 <= p_num_select) then
g_results_table(i).value53 := display_value(52);
if (54 <= p_num_select) then
g_results_table(i).value54 := display_value(53);
if (55 <= p_num_select) then
g_results_table(i).value55 := display_value(54);
if (56 <= p_num_select) then
g_results_table(i).value56 := display_value(55);
if (57 <= p_num_select) then
g_results_table(i).value57 := display_value(56);
if (58 <= p_num_select) then
g_results_table(i).value58 := display_value(57);
if (59 <= p_num_select) then
g_results_table(i).value59 := display_value(58);
if (60 <= p_num_select) then
g_results_table(i).value60 := display_value(59);
if (61 <= p_num_select) then
g_results_table(i).value61 := display_value(60);
if (62 <= p_num_select) then
g_results_table(i).value62 := display_value(61);
if (63 <= p_num_select) then
g_results_table(i).value63 := display_value(62);
if (64 <= p_num_select) then
g_results_table(i).value64 := display_value(63);
if (65 <= p_num_select) then
g_results_table(i).value65 := display_value(64);
if (66 <= p_num_select) then
g_results_table(i).value66 := display_value(65);
if (67 <= p_num_select) then
g_results_table(i).value67 := display_value(66);
if (68 <= p_num_select) then
g_results_table(i).value68 := display_value(67);
if (69 <= p_num_select) then
g_results_table(i).value69 := display_value(68);
if (70 <= p_num_select) then
g_results_table(i).value70 := display_value(69);
if (71 <= p_num_select) then
g_results_table(i).value71 := display_value(70);
if (72 <= p_num_select) then
g_results_table(i).value72 := display_value(71);
if (73 <= p_num_select) then
g_results_table(i).value73 := display_value(72);
if (74 <= p_num_select) then
g_results_table(i).value74 := display_value(73);
if (75 <= p_num_select) then
g_results_table(i).value75 := display_value(74);
if (76 <= p_num_select) then
g_results_table(i).value76 := display_value(75);
if (77 <= p_num_select) then
g_results_table(i).value77 := display_value(76);
if (78 <= p_num_select) then
g_results_table(i).value78 := display_value(77);
if (79 <= p_num_select) then
g_results_table(i).value79 := display_value(78);
if (80 <= p_num_select) then
g_results_table(i).value80 := display_value(79);
if (81 <= p_num_select) then
g_results_table(i).value81 := display_value(80);
if (82 <= p_num_select) then
g_results_table(i).value82 := display_value(81);
if (83 <= p_num_select) then
g_results_table(i).value83 := display_value(82);
if (84 <= p_num_select) then
g_results_table(i).value84 := display_value(83);
if (85 <= p_num_select) then
g_results_table(i).value85 := display_value(84);
if (86 <= p_num_select) then
g_results_table(i).value86 := display_value(85);
if (87 <= p_num_select) then
g_results_table(i).value87 := display_value(86);
if (88 <= p_num_select) then
g_results_table(i).value88 := display_value(87);
if (89 <= p_num_select) then
g_results_table(i).value89 := display_value(88);
if (90 <= p_num_select) then
g_results_table(i).value90 := display_value(89);
if (91 <= p_num_select) then
g_results_table(i).value91 := display_value(90);
if (92 <= p_num_select) then
g_results_table(i).value92 := display_value(91);
if (93 <= p_num_select) then
g_results_table(i).value93 := display_value(92);
if (94 <= p_num_select) then
g_results_table(i).value94 := display_value(93);
if (95 <= p_num_select) then
g_results_table(i).value95 := display_value(94);
if (96 <= p_num_select) then
g_results_table(i).value96 := display_value(95);
if (97 <= p_num_select) then
g_results_table(i).value97 := display_value(96);
if (98 <= p_num_select) then
g_results_table(i).value98 := display_value(97);
if (99 <= p_num_select) then
g_results_table(i).value99 := display_value(98);
if (100 <= p_num_select) then
g_results_table(i).value100:= display_value(99);
p_num_select IN number
)
IS
select_index number := 0;
FOR select_index in 0..(p_num_select - 1) LOOP
dbms_sql.define_column(p_cursor_id, select_index + 1, NULL, 4000);
p_select IN varchar2,
p_from IN varchar2,
p_where IN varchar2,
p_order_by IN varchar2
)
IS
l_region_style varchar2(30);
SELECT fpr.region_style region_style,
fpr.num_columns number_of_format_columns,
art.name region_name,
ao.validation_api_pkg object_validation_api_pkg,
ao.validation_api_proc object_validation_api_proc,
ao.defaulting_api_pkg object_defaulting_api_pkg,
ao.defaulting_api_proc object_defaulting_api_proc,
ar.region_validation_api_pkg region_validation_api_pkg,
ar.region_validation_api_proc region_validation_api_proc,
ar.region_defaulting_api_pkg region_defaulting_api_pkg,
ar.region_defaulting_api_proc region_defaulting_api_proc,
fpr.display_sequence display_sequence
INTO l_region_style ,
l_number_of_format_columns ,
l_region_name ,
l_object_validation_api_pkg ,
l_object_validation_api_proc,
l_object_defaulting_api_pkg ,
l_object_defaulting_api_proc,
l_region_validation_api_pkg ,
l_region_validation_api_proc,
l_region_defaulting_api_pkg ,
l_region_defaulting_api_proc,
l_display_sequence
FROM ak_flow_page_regions fpr,
ak_regions ar,
ak_regions_tl art,
ak_objects ao
WHERE fpr.flow_application_id = p_region_rec.flow_application_id
AND fpr.flow_code = p_region_rec.flow_code
AND fpr.page_application_id = p_region_rec.page_application_id
AND fpr.page_code = p_region_rec.page_code
AND fpr.region_application_id = p_region_rec.region_application_id
AND fpr.region_code = p_region_rec.region_code
AND ar.region_application_id = fpr.region_application_id
AND ar.region_code = fpr.region_code
AND art.region_application_id = ar.region_application_id
AND art.region_code = ar.region_code
AND art.language = userenv('LANG')
AND ao.database_object_name = ar.database_object_name;
SELECT ar.region_style region_style,
ar.num_columns number_of_format_columns,
art.name region_name,
ao.validation_api_pkg object_validation_api_pkg,
ao.validation_api_proc object_validation_api_proc,
ao.defaulting_api_pkg object_defaulting_api_pkg,
ao.defaulting_api_proc object_defaulting_api_proc,
ar.region_validation_api_pkg region_validation_api_pkg,
ar.region_validation_api_proc region_validation_api_proc,
ar.region_defaulting_api_pkg region_defaulting_api_pkg,
ar.region_defaulting_api_proc region_defaulting_api_proc,
null
INTO l_region_style ,
l_number_of_format_columns ,
l_region_name ,
l_object_validation_api_pkg ,
l_object_validation_api_proc,
l_object_defaulting_api_pkg ,
l_object_defaulting_api_proc,
l_region_validation_api_pkg ,
l_region_validation_api_proc,
l_region_defaulting_api_pkg ,
l_region_defaulting_api_proc,
l_display_sequence
FROM ak_regions ar,
ak_regions_tl art,
ak_objects ao
WHERE ar.region_application_id = p_region_rec.region_application_id
AND ar.region_code = p_region_rec.region_code
AND art.region_application_id = ar.region_application_id
AND art.region_code = ar.region_code
AND art.language = userenv('LANG')
AND ao.database_object_name = ar.database_object_name;
g_regions_table(i).sql_select := p_select;
l_sql_statement varchar2(2000) := 'SELECT ';
SELECT aoa.column_name foreign_key_column_name, aa.data_type
FROM ak_foreign_keys afk,
ak_foreign_key_columns afkc,
ak_object_attributes aoa,
ak_attributes aa
WHERE
afk.database_object_name = aoa.database_object_name
AND afkc.attribute_application_id = aoa.attribute_application_id
AND afkc.attribute_code = aoa.attribute_code
AND afkc.foreign_key_name = afk.foreign_key_name
AND afk.foreign_key_name = foreign_key_name_param
AND aoa.attribute_code = aa.attribute_code
AND aoa.attribute_application_id = aa.attribute_application_id
ORDER BY afkc.foreign_key_sequence;
SELECT aoa.column_name unique_key_column_name, aa.data_type
FROM ak_unique_keys auk,
ak_unique_key_columns aukc,
ak_object_attributes aoa,
ak_attributes aa
WHERE
auk.database_object_name = aoa.database_object_name
AND aukc.attribute_application_id = aoa.attribute_application_id
AND aukc.attribute_code = aoa.attribute_code
AND aukc.unique_key_name = auk.unique_key_name
AND auk.unique_key_name = unique_key_name_param
AND aoa.attribute_code = aa.attribute_code
AND aoa.attribute_application_id = aa.attribute_application_id
ORDER BY aukc.unique_key_sequence;
CURSOR select_cur
(
p_child_region_appl_id NUMBER,
p_child_region_code VARCHAR2,
p_responsibility_id NUMBER,
p_node_display_criteria VARCHAR2
)
IS
-- Select region_items that are also object_attributes
SELECT aoa.column_name column_name,
ari.display_sequence display_sequence,
ari.attribute_application_id attribute_application_id,
ari.attribute_code attribute_code,
decode(aei.attribute_code,NULL,'F','T') secured_column,
decode(arsa.attribute_code,NULL,'F','T') rls_column,
decode(ari.icx_custom_call,'INDEX','T','F') indexed_column,
arit.attribute_label_long attribute_label_long,
ari.attribute_label_length attribute_label_length,
aa.attribute_value_length attribute_value_length,
ari.display_value_length display_value_length,
ari.item_style item_style,
ari.bold bold,
ari.italic italic,
ari.vertical_alignment vertical_alignment,
ari.horizontal_alignment horizontal_alignment,
ari.object_attribute_flag object_attribute_flag,
ari.node_query_flag node_query_flag,
ari.node_display_flag node_display_flag,
ari.update_flag update_flag,
ari.required_flag required_flag,
ari.icx_custom_call icx_custom_call,
aoa.validation_api_pkg object_validation_api_pkg,
aoa.validation_api_proc object_validation_api_proc,
aoa.defaulting_api_pkg object_defaulting_api_pkg,
aoa.defaulting_api_proc object_defaulting_api_proc,
ari.region_validation_api_pkg region_validation_api_pkg,
ari.region_validation_api_proc region_validation_api_proc,
ari.region_defaulting_api_pkg region_defaulting_api_pkg,
ari.region_defaulting_api_proc region_defaulting_api_proc,
ari.lov_foreign_key_name lov_foreign_key_name,
ari.lov_region_application_id lov_region_application_id,
ari.lov_region_code lov_region_code,
ari.lov_attribute_application_id lov_attribute_application_id,
ari.lov_attribute_code lov_attribute_code,
ari.lov_default_flag lov_default_flag,
ari.order_sequence order_sequence,
ari.order_direction order_direction,
aa.data_type data_type
FROM ak_object_attributes aoa,
ak_excluded_items aei,
ak_resp_security_attributes arsa,
ak_attributes aa,
ak_regions ar,
ak_region_items_tl arit,
ak_region_items ari
WHERE ari.object_attribute_flag = 'Y'
AND aoa.attribute_application_id = ari.attribute_application_id
AND aoa.attribute_code = ari.attribute_code
AND aoa.database_object_name = ar.database_object_name
AND ar.region_application_id = ari.region_application_id
AND ar.region_code = ari.region_code
AND ari.region_code = p_child_region_code
AND ari.region_application_id = p_child_region_appl_id
AND ari.node_display_flag =
decode(p_node_display_criteria,'Y','Y',ari.node_display_flag)
AND arit.region_code = ari.region_code
AND arit.region_application_id = ari.region_application_id
AND arit.attribute_code = ari.attribute_code
AND arit.attribute_application_id = ari.attribute_application_id
AND arit.language = userenv('LANG')
AND aei.responsibility_id (+) = p_responsibility_id
AND aei.attribute_application_id (+) = ari.attribute_application_id
AND aei.attribute_code (+) = ari.attribute_code
AND arsa.responsibility_id (+) = p_responsibility_id
AND arsa.attribute_application_id (+) = ari.attribute_application_id
AND arsa.attribute_code (+) = ari.attribute_code
AND ari.attribute_code = aa.attribute_code
AND ari.attribute_application_id = aa.attribute_application_id
UNION ALL
-- Select region_items that are not object attributes
SELECT null column_name,
ari.display_sequence display_sequence,
ari.attribute_application_id attribute_application_id,
ari.attribute_code attribute_code,
decode(aei.attribute_code,NULL,'F','T') secured_column,
decode(arsa.attribute_code,NULL,'F','T') rls_column,
decode(ari.icx_custom_call,'INDEX','T','F') indexed_column,
arit.attribute_label_long attribute_label_long,
ari.attribute_label_length attribute_label_length,
aa.attribute_value_length attribute_value_length,
ari.display_value_length display_value_length,
ari.item_style item_style,
ari.bold bold,
ari.italic italic,
ari.vertical_alignment vertical_alignment,
ari.horizontal_alignment horizontal_alignment,
ari.object_attribute_flag object_attribute_flag,
ari.node_query_flag node_query_flag,
ari.node_display_flag node_display_flag,
ari.update_flag update_flag,
ari.required_flag required_flag,
ari.icx_custom_call icx_custom_call,
null object_validation_api_pkg,
null object_validation_api_proc,
null object_defaulting_api_pkg,
null object_defaulting_api_proc,
ari.region_validation_api_pkg region_validation_api_pkg,
ari.region_validation_api_proc region_validation_api_proc,
ari.region_defaulting_api_pkg region_defaulting_api_pkg,
ari.region_defaulting_api_proc region_defaulting_api_proc,
ari.lov_foreign_key_name lov_foreign_key_name,
ari.lov_region_application_id lov_region_application_id,
ari.lov_region_code lov_region_code,
ari.lov_attribute_application_id lov_attribute_application_id,
ari.lov_attribute_code lov_attribute_code,
ari.lov_default_flag lov_default_flag,
ari.order_sequence order_sequence,
ari.order_direction order_direction,
aa.data_type data_type
FROM ak_excluded_items aei,
ak_resp_security_attributes arsa,
ak_attributes aa,
ak_region_items_tl arit,
ak_region_items ari
WHERE ari.object_attribute_flag <> 'Y'
AND ari.region_code = p_child_region_code
AND ari.region_application_id = p_child_region_appl_id
AND ari.node_display_flag =
decode(p_node_display_criteria,'Y','Y',ari.node_display_flag)
AND arit.region_code = ari.region_code
AND arit.region_application_id = ari.region_application_id
AND arit.attribute_code = ari.attribute_code
AND arit.attribute_application_id = ari.attribute_application_id
AND arit.language = userenv('LANG')
AND aei.responsibility_id (+) = p_responsibility_id
AND aei.attribute_application_id (+) = ari.attribute_application_id
AND aei.attribute_code (+) = ari.attribute_code
AND arsa.responsibility_id (+) = p_responsibility_id
AND arsa.attribute_application_id (+) = ari.attribute_application_id
AND arsa.attribute_code (+) = ari.attribute_code
AND ari.attribute_code = aa.attribute_code
AND ari.attribute_application_id = aa.attribute_application_id
ORDER BY 2;
select_rec select_cur%rowtype;
SELECT nvl(to_char(number_value),nvl(varchar2_value, to_char(date_value))) sec_value
FROM ak_web_user_sec_attr_values awusav
WHERE awusav.web_user_id = p_user_id
AND awusav.attribute_application_id = p_attribute_appl_id
AND awusav.attribute_code = p_attribute_code
union
SELECT nvl(to_char(number_value),
nvl(varchar2_value,to_char(date_value))) sec_value
FROM AK_RESP_SECURITY_ATTR_VALUES arsav
WHERE arsav.responsibility_id = p_responsibility_id
AND arsav.attribute_application_id = p_attribute_appl_id
AND arsav.attribute_code = p_attribute_code;
select_count1 number := 0;
select_count2 number := 0;
select_count number := 0;
select_stmt varchar2(20000);
l_query_stmt := 'SELECT ';
OPEN select_cur(p_region_rec.region_application_id,
p_region_rec.region_code,
p_responsibility_id,
node_display_criteria);
FETCH select_cur INTO select_rec;
EXIT WHEN select_cur%NOTFOUND;
row_count := select_cur%ROWCOUNT;
print_debug('select column1 = '||select_rec.column_name);
print_debug('secure value1 = '||select_rec.rls_column);
print_debug('obj attr flag = '||select_rec.object_attribute_flag);
print_debug ( 'Secured Value is -> ' || select_rec.rls_column);
IF select_rec.rls_column = 'T' THEN
where_temp := NULL;
SELECT count(*)
INTO select_count1
FROM ak_web_user_sec_attr_values awusav
WHERE awusav.web_user_id = p_user_id
AND awusav.attribute_application_id = select_rec.attribute_application_id
AND awusav.attribute_code = select_rec.attribute_code
AND (( varchar2_value is not null and date_value is not null) or
( varchar2_value is not null and number_value is not null) or
( date_value is not null and number_value is not null));
SELECT count(*)
INTO select_count2
FROM ak_resp_security_attr_values arsav
WHERE arsav.responsibility_id = p_responsibility_id
AND arsav.attribute_application_id = select_rec.attribute_application_id
AND arsav.attribute_code = select_rec.attribute_code
AND (( varchar2_value is not null and date_value is not null) or
( varchar2_value is not null and number_value is not null) or
( date_value is not null and number_value is not null));
select_count := select_count1 + select_count2;
if select_count = 0 then
FOR attr_values_rec IN attr_values_cur(p_user_id,
select_rec.attribute_application_id,
select_rec.attribute_code,
p_responsibility_id) LOOP
IF where_temp IS NULL THEN
where_temp := '('||select_rec.column_name || ' IN (';
where_temp := '(' || select_rec.column_name || ' = NULL)';
CLOSE select_cur;