[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
return '(select
time_id
, period_type_id
, grp_id
, ' ||
case l_view_by /* replaced asset_number with instance_id */
when isc_maint_rpt_util_pkg.G_DEPARTMENT then 'decode(department_id,-1,-1,organization_id) organization_id'
when isc_maint_rpt_util_pkg.G_ASSET_GROUP then 'decode(asset_group_id,-1,-1,organization_id) organization_id'
when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then 'decode(instance_id,-1,-1,organization_id) organization_id'
when isc_maint_rpt_util_pkg.G_ASSET_CATEGORY then 'decode(category_id,-1,-1,organization_id) organization_id'
else 'organization_id'
end ||'
,asset_group_id
,instance_id /* replaced asset_number with instance_id */
,organization_id organization_id_c
,category_id
,to_char(department_id) department_id
,asset_group_id asset_group_id_c /* removed concatenation to org. to make asset group independent of org. */
,decode(instance_id,-1,-1,instance_id) instance_id_c /* replaced asset_number with instance_id */
,decode(department_id,-1,''-1'',department_id||''-1'') department_id_c
,asset_criticality_code
,dt_non_overlap_hrs
from isc_maint_001_mv fact' || '
where fact.grp_id = &ISC_GRP_ID ' ||
case
when l_org_id is null then
'
and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )
end || '
)';
return '( select
fact.organization_id organization_id
,fact.asset_group_id asset_group_id
,fact.instance_id instance_id /* replace asset_number with instance_id */
,fact.category_id category_id
,fact.asset_criticality_code asset_criticality_code
,fact.organization_id organization_id_c
,to_char(fact.department_id) department_id
,fact.asset_group_id asset_group_id_c /* removed concatenation to org. to make it independent of org. */
,decode(fact.instance_id,-1,-1,fact.instance_id)
instance_id_c /* replaced asset_number with instance_id */
,fact.department_id||''-1'' department_id_c
,fact.start_date start_date
,fact.end_date end_date
,24*(fact.end_date - fact.start_date)
dt_overlap_hrs
,w.wip_entity_name work_order_name
,fact.work_order_id work_order_id
,fact.description description
,fact.operation_seq_number operation_seq_number
from
isc_maint_asset_down_f fact
,wip_entities w
where
w.wip_entity_id(+) = fact.WORK_ORDER_ID and
fact.start_date < &BIS_CURRENT_ASOF_DATE+1 and
fact.end_date >= &BIS_CURRENT_EFFECTIVE_START_DATE ' ||
case
when l_org_id is null then
'and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )
end || '
) ';
l_viewby_select varchar2(200);
, x_viewby_select => l_viewby_select
, x_join_tbl => l_join_tbl
, x_dim_bmap => l_dim_bmap
, x_comparison_type => l_comparison_type
, x_xtd => l_xtd
);
l_stmt := ' select ' || l_viewby_select ||
', biv_measure1
, biv_measure2
, biv_measure3
, biv_measure13
, biv_measure14
, biv_measure15 , ' ||
l_asset_grp_column || ' BIV_MEASURE16 ,' ||
l_criticality_column || ' BIV_MEASURE20 , ' ||
l_drill_across || ' BIV_Attribute1 , '||
l_drill_down || ' BIV_Attribute2 ' ;
l_inner_query := 'from ( select row_number() over(&ORDER_BY_CLAUSE nulls last )-1 rnk,iset.*'||
' from ( select nvl(oset05.p_dt_non_overlap_hrs,0) BIV_MEASURE1
,nvl(oset05.c_dt_non_overlap_hrs,0) BIV_MEASURE2, ' ||
isc_maint_rpt_util_pkg.change_column('nvl(oset05.c_dt_non_overlap_hrs,0)',
'nvl(oset05.p_dt_non_overlap_hrs,0)','BIV_MEASURE3','X') || '
,nvl(oset05.c_dt_non_overlap_hrs_total,0) BIV_MEASURE13, ' ||
isc_maint_rpt_util_pkg.change_column('nvl(oset05.c_dt_non_overlap_hrs_total,0)',
'nvl(oset05.p_dt_non_overlap_hrs_total,0)','BIV_MEASURE14','X') || '
,nvl(oset05.p_dt_non_overlap_hrs_total,0) BIV_MEASURE15,'||
isc_maint_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ' from ';
l_viewby_select varchar2(200);
, x_viewby_select => l_viewby_select
, x_join_tbl => l_join_tbl
, x_dim_bmap => l_dim_bmap
, x_comparison_type => l_comparison_type
, x_xtd => l_xtd
);
l_stmt := 'select
' || isc_maint_rpt_util_pkg.get_detail_column
(l_detail_col_tbl,'instance_id','BIV_MEASURE1') || '
, ' || isc_maint_rpt_util_pkg.get_detail_column
(l_detail_col_tbl,'asset_group','BIV_MEASURE2') || '
, oset.START_DATE BIV_MEASURE3
, oset.END_DATE BIV_MEASURE13
, oset.dt_overlap_hrs BIV_MEASURE14
, oset.WORK_ORDER_NAME BIV_MEASURE15
, oset.OPERATION BIV_MEASURE16
, oset.DESCRIPTION BIV_MEASURE17 ,
case when oset.work_order_id is null then null else '||
isc_maint_rpt_util_pkg.get_drill_detail(' ') ||
' end BIV_ATTRIBUTE1 from
' || isc_maint_rpt_util_pkg.detail_sql
( p_detail_col_tbl => l_detail_col_tbl
, p_dimension_tbl => l_dimension_tbl
, p_mv_name => l_mv
, p_where_clause => l_where_clause
, p_rank_order => l_rank_order
, p_override_date_clause => '1 = 1 '
);
l_viewby_select varchar2(200);
, x_viewby_select => l_viewby_select
, x_join_tbl => l_join_tbl
, x_dim_bmap => l_dim_bmap
, x_comparison_type => l_comparison_type
, x_xtd => l_xtd
);
l_stmt := 'select
cal.name VIEWBY
, nvl(iset.p_dt_non_overlap_hrs,0) BIV_MEASURE2
, nvl(iset.c_dt_non_overlap_hrs,0) BIV_MEASURE3 , ' ||
isc_maint_rpt_util_pkg.change_column('nvl(iset.c_dt_non_overlap_hrs,0)',
'nvl(iset.p_dt_non_overlap_hrs,0)','BIV_MEASURE4','X')||'
from ' ||
poa_dbi_template_pkg.trend_sql
( p_xtd => l_xtd
, p_comparison_type => l_comparison_type
, p_fact_name => l_mv
, p_where_clause => l_where_clause
, p_col_name => l_col_tbl
, p_use_grpid => 'N'
);