[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'(select id, value, leaf_node_flag, item_assgn_flag from eni_item_vbh_nodes_v where parent_id = child_id)';
'(select ''GROUP'' record_type, group_id id, group_name value from jtf_rs_groups_vl ' ||
'union all ' ||
'select ''RESOURCE'' record_type, resource_id id, resource_name value from jtf_rs_resource_extns_vl)';
'(select task_type_id id, name value from jtf_task_types_tl where language = userenv(''LANG''))';
'(select ''GROUP'' owner_type, group_id id, group_name value from jtf_rs_groups_vl ' ||
'union all ' ||
'select ''TEAM'' owner_type, team_id id, team_name value from jtf_rs_teams_vl ' ||
'union all ' ||
'select ''RESOURCE'' owner_type, resource_id id, resource_name value from jtf_rs_resource_extns_vl)';
'(select resource_id id, resource_name value from jtf_rs_resource_extns_vl)';
'(select ''GROUP'' assignee_type, group_id id, group_name value from jtf_rs_groups_vl ' ||
'union all ' ||
'select ''TEAM'' assignee_type, team_id id, team_name value from jtf_rs_teams_vl ' ||
'union all ' ||
'select ''RESOURCE'' assignee_type, resource_id id, resource_name value from jtf_rs_resource_extns_vl)';
'(select ''ADDRESS_ID'' address_type, party_site_id id, hz_format_pub.format_address(location_id) value from hz_party_sites ' ||
'union all ' ||
'select ''LOCATION_ID'' address_type, location_id id, hz_format_pub.format_address(location_id) value from hz_locations)';
'(select null id from dual where 1=3)';
l_stmt := 'select bin_to_num( 0' ||
add_bin_column(p_column1) ||
add_bin_column(p_column2) ||
add_bin_column(p_column3) ||
add_bin_column(p_column4) ||
add_bin_column(p_column5) ||
add_bin_column(p_column6) ||
add_bin_column(p_column7) ||
add_bin_column(p_column8) ||
') grp_id from dual';
( select 1
from org_access o
where o.responsibility_id = fnd_global.resp_id
and o.resp_application_id = fnd_global.resp_appl_id
and o.organization_id = ' || p_fact_alias ||'.organization_id ) or
exists
( select 1
from mtl_parameters org
where org.organization_id = ' || p_fact_alias ||'.organization_id
and not exists ( select 1
from org_access ora
where org.organization_id = ora.organization_id
)
)
)';
, x_viewby_select out nocopy varchar2
, x_join_tbl out nocopy poa_dbi_util_pkg.poa_dbi_join_tbl
, x_dim_bmap out nocopy number
, x_comparison_type out nocopy varchar2
, x_xtd out nocopy varchar2
, x_uom_suffix out nocopy varchar2
) is
l_as_of_date date;
'select ''TEAM'' record_type, team_id id, team_name value from jtf_rs_teams_vl ' ||
'union all '
);
x_viewby_select := case
when l_view_by = G_DISTRICT then
p_dimension_tbl(l_view_by).viewby_col_name
when p_dimension_tbl(l_view_by).dim_outer_join = 'Y' then
'nvl(' ||
p_dimension_tbl(l_view_by).dim_table_alias ||
'.' || p_dimension_tbl(l_view_by).viewby_col_name ||
',&ISC_UNASSIGNED)'
else
p_dimension_tbl(l_view_by).dim_table_alias ||
'.' || p_dimension_tbl(l_view_by).viewby_col_name
end ||
' VIEWBY
' ||
case
when p_dimension_tbl(l_view_by).viewby_id_col_name is not null then
case
when p_dimension_tbl(l_view_by).dim_outer_join = 'Y' then
', nvl(' ||
p_dimension_tbl(l_view_by).dim_table_alias ||
'.' || p_dimension_tbl(l_view_by).viewby_id_col_name ||
',' || p_dimension_tbl(l_view_by).viewby_id_unassigned ||
')'
else
', ' ||
case
when p_dimension_tbl(l_view_by).viewby_id_col_name not like '%.%' then
p_dimension_tbl(l_view_by).dim_table_alias || '.'
end ||
p_dimension_tbl(l_view_by).viewby_id_col_name
end ||
' VIEWBYID'
end;
, x_viewby_select out nocopy varchar2
, x_join_tbl out nocopy poa_dbi_util_pkg.poa_dbi_join_tbl
, x_dim_bmap out nocopy number
, x_comparison_type out nocopy varchar2
, x_xtd out nocopy varchar2
) is
l_uom_suffix varchar2(30);
, x_viewby_select
, x_join_tbl
, x_dim_bmap
, x_comparison_type
, x_xtd
, l_uom_suffix -- throw away this return value
);
l_select_list varchar2(4000);
l_fact_select_list varchar2(4000);
if l_select_list is null or
l_select_list not like '%, ' || l_col_name || '%' then
l_select_list := l_select_list || '
, ' || l_col_name;
if l_col_name is not null and l_select_list not like '%, '||l_col_name || '%' then
l_select_list := l_select_list || '
, ' || l_col_name;
l_fact_select_list := l_fact_select_list ||
'
, ' || l_detail_column_rec.fact_col_name ||
' ' || l_key;
l_fact_select_list := l_fact_select_list ||
'
, sum(' || l_detail_column_rec.fact_col_name || ') over()' ||
' ' || l_key || '_total';
return '( select
' || case
when p_rank_order is null then
'-1 rnk'
else
'rank() over(' || p_rank_order || ')-1 rnk'
end || l_select_list || l_fact_select_list || '
from
' || p_mv_name || ' fact
where ' ||
case
when p_override_date_clause is not null then
p_override_date_clause
else
'report_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE'
end || '
' || p_where_clause || ' ' || p_filter_where || '
) oset
, ' || poa_dbi_template_pkg.get_viewby_rank_clause
( l_join_tbl
, case
when p_rank_order is null then 'N'
else 'Y'
end );
function get_inner_select_col
( p_join_tables in poa_dbi_util_pkg.poa_dbi_join_tbl
) return varchar2
is
l_select_list varchar2(500);
l_select_list := l_select_list || ', ';
l_select_list := l_select_list || p_join_tables(i).fact_column;
return l_select_list;
end get_inner_select_col;
p_query like '%, (select null id from dual where 1=3) v0%' then
p_query := replace( p_query
, ', (select null id from dual where 1=3) v0'
, null
);
select count(*)
from isc_fs_002_mv
where parent_prg_id = to_number(l_district_id);