[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'(select organization_id id, name value ' ||
'from hr_all_organization_units_tl ' ||
'where language = userenv(''LANG''))';
'(select ' ||
'star.inventory_item_id id ' ||
', msi.concatenated_segments value ' ||
'from ' ||
'mtl_system_items_kfv msi, ' ||
'ENI_OLTP_ITEM_STAR star ' ||
'where msi.eam_item_type in (1,3) ' ||
'and msi.inventory_item_id = star.inventory_item_id ' ||
'and msi.organization_id = star.organization_id ' ||
' group by star.inventory_item_id, msi.concatenated_segments)';
'(select ' ||
'cii.instance_id id ' ||
', cii.instance_number value ' ||
', cii.serial_number ' ||
', CII.LAST_VLD_ORGANIZATION_ID ' ||
', MSI.INVENTORY_ITEM_ID ' ||
', MSI.CONCATENATED_SEGMENTS ASSET_GROUP '||
', MP.MAINT_ORGANIZATION_ID ' ||
'from ' ||
'mtl_system_items_kfv msi ' ||
', CSI_ITEM_INSTANCES CII ' ||
', MTL_PARAMETERS MP '||
'where msi.eam_item_type in (1,3) ' ||
'and serial_number_control_code <> 1 '||
'and msi.inventory_item_id = cii.inventory_item_id '||
'and msi.organization_id = cii.last_vld_organization_id '||
'and msi.organization_id = mp.organization_id) ';
'(select ' ||
'kfv.inventory_item_id || ''-'' || kfv.organization_id id ' ||
', kfv.concatenated_segments || '' ('' || mp.organization_code || '')'' value ' ||
', tl.description ' ||
', kfv.inventory_item_id activity_id ' ||
', kfv.organization_id ' ||
'from ' ||
'mtl_system_items_kfv kfv ' ||
', mtl_system_items_tl tl ' ||
', mtl_parameters mp ' ||
'where kfv.eam_item_type = 2 ' ||
'and kfv.inventory_item_id = tl.inventory_item_id(+) ' ||
'and kfv.organization_id = tl.organization_id(+) ' ||
'and tl.language (+) = userenv(''LANG'') ' ||
'and kfv.organization_id = mp.organization_id)';
'(select ''2'' request_type' ||
', incident_id maint_request_id' ||
', summary description ' ||
'from cs_incidents_all_tl ' ||
'where LANGUAGE = userenv(''LANG'') ' ||
'union all ' ||
'select ''1''' ||
', work_request_id' ||
', description ' ||
'from wip_eam_work_requests ' ||
')';
'(select ''1'' request_type' ||
', lookup_code request_severity_id' ||
', meaning name ' ||
'from fnd_lookup_values ' ||
'where lookup_type = ''WIP_EAM_ACTIVITY_PRIORITY'' ' ||
'and language = userenv(''LANG'') ' ||
'and view_application_id = 700 ' ||
'and security_group_id = ' ||
'fnd_global.lookup_security_group(lookup_type,view_application_id) ' ||
'union all ' ||
'select ''2''' ||
', to_char(incident_severity_id)' ||
', name ' ||
'from cs_incident_severities_tl ' ||
'where LANGUAGE = userenv(''LANG'') ' ||
')';
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
) is
l_as_of_date date;
x_viewby_select := 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_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
', ' ||
p_dimension_tbl(l_view_by).dim_table_alias ||
'.' || p_dimension_tbl(l_view_by).viewby_id_col_name
end ||
' VIEWBYID'
end;
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;