[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
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' /* replaced asset_number with instance_id */
when isc_maint_rpt_util_pkg.G_ACTIVITY then 'decode(activity_id,-1,-1,organization_id) organization_id'
else 'organization_id'
end ||'
, organization_id organization_id_c
, ' ||
case l_view_by
when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then 'decode(instance_id,-1,-1,asset_group_id) asset_group_id ' /* replaced asset_number with instance_id */
else 'asset_group_id'
end ||'
, instance_id /* replaced asset_number with instance_id */
, activity_id
, work_order_type
, 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(activity_id,-1,''-1'',activity_id||''-''||organization_id) activity_id_c
, decode(department_id,-1,''-1'',department_id||''-1'') department_id_c
, num_completion
, num_late_completion
, days_late
from isc_maint_004_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
time_id
, period_type_id
, grp_id
, organization_id
, organization_id organization_id_c
, bucket_num
, asset_group_id
, instance_id /* replaced asset_number with instance_id */
, activity_id
, work_order_type
, 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(activity_id,-1,''-1'',activity_id||''-''||organization_id) activity_id_c
, decode(department_id,-1,''-1'',department_id||''-1'') department_id_c
, num_count
from isc_maint_006_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
activity_id
, organization_id
, organization_id organization_id_c
, asset_group_id
, instance_id /* replaced asset_number with instance_id */
, work_order_name
, to_char(department_id) department_id
, asset_group_id asset_group_id_c
, decode(instance_id,-1,-1,instance_id) instance_id_c /* replaced asset_number with instance_id */
, decode(activity_id,-1,''-1'',activity_id||''-''||organization_id) activity_id_c
, decode(department_id,-1,''-1'',department_id||''-1'') department_id_c
, work_order_id
, status.value wo_status
, work_order_type
, SCHEDULED_COMPLETION_DATE
, SCHEDULED_START_DATE
, COMPLETION_DATE
from isc_maint_work_orders_f f,
biv_maint_wo_status_lvl_v status
where
f.user_defined_status_id = status.id
and f.Include_WO = 1 /* Do not include Pending Close, Failed Close, Cancelled */
and f.COMPLETION_DATE is not null
and f.COMPLETION_DATE <= &BIS_CURRENT_ASOF_DATE
and f.COMPLETION_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE ' ||
case
when l_org_id is null then
'
and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'f', l_org_id )
end || '
) ';
return '(select
activity_id
, organization_id
, organization_id organization_id_c
, asset_group_id
, instance_id /* replaced asset_number with instance_id */
, work_order_name
, 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(activity_id,-1,''-1'',activity_id||''-''||organization_id) activity_id_c
, decode(department_id,-1,''-1'',department_id||''-1'') department_id_c
, work_order_id
, status.value wo_status
, work_order_type
, SCHEDULED_COMPLETION_DATE
, SCHEDULED_START_DATE
, COMPLETION_DATE
, DAYS_LATE
, case
when f.days_late >= b.range1_low and (f.days_late < b.range1_high or b.range1_high is null) then 1
when f.days_late >= b.range2_low and (f.days_late < b.range2_high or b.range2_high is null) then 2
when f.days_late >= b.range3_low and (f.days_late < b.range3_high or b.range3_high is null) then 3
when f.days_late >= b.range4_low and (f.days_late < b.range4_high or b.range4_high is null) then 4
when f.days_late >= b.range5_low and (f.days_late < b.range5_high or b.range5_high is null) then 5
when f.days_late >= b.range6_low and (f.days_late < b.range6_high or b.range6_high is null) then 6
when f.days_late >= b.range7_low and (f.days_late < b.range7_high or b.range7_high is null) then 7
when f.days_late >= b.range8_low and (f.days_late < b.range8_high or b.range8_high is null) then 8
when f.days_late >= b.range9_low and (f.days_late < b.range9_high or b.range9_high is null) then 9
else 10
end bucket_num
from isc_maint_work_orders_f f,
bis_bucket_customizations b,
bis_bucket bb,
biv_maint_wo_status_lvl_v status
where
f.user_defined_status_id = status.id
and f.Include_WO = 1 /* Do not include Pending Close, Failed Close, Cancelled */
and f.COMPLETION_DATE is not null
and bb.short_name = ''BIV_MAINT_LATECMPL_AGING''
and bb.bucket_id = b.bucket_id
and f.SCHEDULED_COMPLETION_DATE < f.COMPLETION_DATE
and f.COMPLETION_DATE <= &BIS_CURRENT_ASOF_DATE
and f.COMPLETION_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE ' ||
case
when l_org_id is null then
'
and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'f', 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_ATTRIBUTE1
, BIV_ATTRIBUTE2
, BIV_MEASURE1
, BIV_MEASURE2
, BIV_MEASURE3
, BIV_MEASURE22
, BIV_MEASURE4
, BIV_MEASURE23
, BIV_MEASURE5
, BIV_MEASURE6
, BIV_MEASURE7
, BIV_MEASURE8
, BIV_MEASURE9
, BIV_MEASURE10
, BIV_MEASURE11
, BIV_MEASURE12
, BIV_MEASURE26
, BIV_MEASURE13
, BIV_MEASURE14
, BIV_MEASURE15
, BIV_MEASURE24
, BIV_MEASURE16
, BIV_MEASURE17
, BIV_MEASURE18
, BIV_MEASURE28
, BIV_MEASURE19
, BIV_MEASURE20
, BIV_MEASURE21 ,
' ||
l_asset_grp_column ||' BIV_MEASURE30 ';
l_inner_query := 'from ( select row_number() over(&ORDER_BY_CLAUSE)-1 rnk,iset.*
from (select '||
case
when isc_maint_rpt_util_pkg.get_parameter_id
( p_param
, 'VIEW_BY'
) in ( isc_maint_rpt_util_pkg.G_ASSET_GROUP
, isc_maint_rpt_util_pkg.G_ASSET_NUMBER
, isc_maint_rpt_util_pkg.G_ACTIVITY ) then
'
''pFunctionName=ISC_MAINT_WO_CMPL_DTL_RPT_REP'' ||
''&VIEW_BY_NAME=VIEW_BY_ID'' ||
''&pParamIds=Y'' BIV_ATTRIBUTE1 '
else '
null BIV_ATTRIBUTE1 ' end || '
, ''pFunctionName=ISC_MAINT_LATECMPL_DTL_RPT_REP'' ||
''&VIEW_BY_NAME=VIEW_BY_ID'' ||
''&pParamIds=Y'' BIV_ATTRIBUTE2
, nvl(oset05.p_num_completion,0) BIV_MEASURE1
, nvl(oset05.c_num_completion,0) BIV_MEASURE2' || '
, ' ||
isc_maint_rpt_util_pkg.change_column
( 'oset05.c_num_completion'
, 'oset05.p_num_completion'
, 'BIV_MEASURE3' ) || ' /* Completion Change */
, nvl(oset05.p_num_completion - oset05.p_num_late_completion,0) BIV_MEASURE22
, nvl(oset05.c_num_completion - oset05.c_num_late_completion,0) BIV_MEASURE4 ' || '
, ' ||
isc_maint_rpt_util_pkg.change_column
( 'oset05.c_num_completion - oset05.c_num_late_completion'
, 'oset05.p_num_completion - oset05.p_num_late_completion'
, 'BIV_MEASURE23' ) || ' /* On Time Completion Change */
, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'oset05.p_num_completion - oset05.p_num_late_completion'
, 'oset05.p_num_completion'
, 'BIV_MEASURE5'
, 'Y' ) || '
/* Prior On time completion percent */, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'oset05.c_num_completion - oset05.c_num_late_completion'
, 'oset05.c_num_completion'
, 'BIV_MEASURE6'
, 'Y' ) || '
/* On time completion percent */, ' ||
isc_maint_rpt_util_pkg.change_column
( isc_maint_rpt_util_pkg.rate_column
( 'oset05.c_num_completion - oset05.c_num_late_completion'
, 'oset05.c_num_completion'
, ''
, 'Y' )
, isc_maint_rpt_util_pkg.rate_column
( 'oset05.p_num_completion - oset05.p_num_late_completion'
, 'oset05.p_num_completion'
, ''
, 'Y' )
, 'BIV_MEASURE7'
, 'N' ) || ' /* On Time Completion Change */
, nvl(oset05.c_num_late_completion,0) BIV_MEASURE8 ' || '
, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'oset05.p_num_late_completion'
, 'oset05.p_num_completion'
, 'BIV_MEASURE9'
, 'Y' ) || '
/* Late Completion Percent */, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'oset05.c_num_late_completion'
, 'oset05.c_num_completion'
, 'BIV_MEASURE10'
, 'Y' ) || '
/* Prior Late Completion Percent */, ' ||
isc_maint_rpt_util_pkg.change_column
( isc_maint_rpt_util_pkg.rate_column
( 'oset05.c_num_late_completion'
, 'oset05.c_num_completion'
, ''
, 'Y' )
, isc_maint_rpt_util_pkg.rate_column
( 'oset05.p_num_late_completion'
, 'oset05.p_num_completion'
, ''
, 'Y' )
, 'BIV_MEASURE11'
, 'N' ) || '
/* Late Completion Change */, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'oset05.c_days_late'
, 'oset05.c_num_late_completion'
, 'BIV_MEASURE12'
, 'N' ) || ' /* Average Days Late */
, nvl(oset05.p_num_completion_total,0) BIV_MEASURE26 ' || '
, nvl(oset05.c_num_completion_total,0) BIV_MEASURE13 ' || '
, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'oset05.c_num_completion_total - oset05.p_num_completion_total'
, 'oset05.p_num_completion_total'
, 'BIV_MEASURE14'
, 'Y' ) || ' /* Grand Total Completion Change */
, nvl(oset05.c_num_completion_total - oset05.c_num_late_completion_total,0) BIV_MEASURE15 ' || '
, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'oset05.c_num_completion_total - oset05.c_num_late_completion_total'
, 'oset05.p_num_completion_total - oset05.p_num_late_completion_total'
, 'BIV_MEASURE24'
, 'Y' ) || ' /* Grand Total On time completion Change */, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'oset05.c_num_completion_total - oset05.c_num_late_completion_total'
, 'oset05.c_num_completion_total'
, 'BIV_MEASURE16'
, 'Y' ) || ' /* On time completion percent Grand Total */, ' ||
isc_maint_rpt_util_pkg.change_column
( isc_maint_rpt_util_pkg.rate_column
( 'oset05.c_num_completion_total - oset05.c_num_late_completion_total'
, 'oset05.c_num_completion_total'
, ''
, 'Y' )
, isc_maint_rpt_util_pkg.rate_column
( 'oset05.p_num_completion_total - oset05.p_num_late_completion_total'
, 'oset05.p_num_completion_total'
, ''
, 'Y' )
, 'BIV_MEASURE17'
, 'N' ) || ' /* Grand Total On Time Completion Percent Change */
, nvl(oset05.c_num_late_completion_total,0) BIV_MEASURE18, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'oset05.p_num_late_completion_total'
, 'oset05.p_num_completion_total'
, 'BIV_MEASURE28'
, 'Y' ) || ' /* Prior Late Completion Percent Grand Total */, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'oset05.c_num_late_completion_total'
, 'oset05.c_num_completion_total'
, 'BIV_MEASURE19'
, 'Y' ) || ' /* Late Completion Percent Grand Total */, ' ||
isc_maint_rpt_util_pkg.change_column
( isc_maint_rpt_util_pkg.rate_column
( 'oset05.c_num_late_completion_total'
, 'oset05.c_num_completion_total'
, ''
, 'Y' )
, isc_maint_rpt_util_pkg.rate_column
( 'oset05.p_num_late_completion_total'
, 'oset05.p_num_completion_total'
, ''
, 'Y' )
, 'BIV_MEASURE20'
, 'N' ) || ' /* Grand Total Late Completion Change */, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'oset05.c_days_late_total'
, 'c_num_late_completion_total'
, 'BIV_MEASURE21'
, 'N' ) || ' , ' ||
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
cal.name VIEWBY
, nvl(iset.p_num_completion,0) BIV_MEASURE1
, nvl(iset.c_num_completion,0) BIV_MEASURE2
, ' ||
isc_maint_rpt_util_pkg.change_column
( 'iset.c_num_completion'
, 'iset.p_num_completion'
, 'BIV_MEASURE3' ) || ' /* Completion Change */
, nvl(iset.p_num_completion - iset.p_num_late_completion,0) BIV_MEASURE13
, nvl(iset.c_num_completion - iset.c_num_late_completion,0) BIV_MEASURE4
, ' ||
isc_maint_rpt_util_pkg.change_column
( 'iset.c_num_completion - iset.c_num_late_completion'
, 'iset.p_num_completion - iset.p_num_late_completion'
, 'BIV_MEASURE14' ) || ' /* On time Completion Change */
, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'iset.p_num_completion - iset.p_num_late_completion'
, 'iset.p_num_completion'
, 'BIV_MEASURE5'
, 'Y' ) || '
/* Prior On time completion percent */
, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'iset.c_num_completion - iset.c_num_late_completion'
, 'iset.c_num_completion'
, 'BIV_MEASURE6'
, 'Y' ) || '
/* On time completion percent */
, ' ||
isc_maint_rpt_util_pkg.change_column
( isc_maint_rpt_util_pkg.rate_column
( 'iset.c_num_completion - iset.c_num_late_completion'
, 'iset.c_num_completion'
, ''
, 'Y' )
, isc_maint_rpt_util_pkg.rate_column
( 'iset.p_num_completion - iset.p_num_late_completion'
, 'iset.p_num_completion'
, ''
, 'Y' )
, 'BIV_MEASURE7'
, 'N' ) || ' /* On Time Completion Change */
, nvl(iset.c_num_late_completion,0) BIV_MEASURE8 ' || '
, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'iset.p_num_late_completion'
, 'iset.p_num_completion'
, 'BIV_MEASURE9'
, 'Y' ) || '
/* Prior Late Completion Percent */, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'iset.c_num_late_completion'
, 'iset.c_num_completion'
, 'BIV_MEASURE10'
, 'Y' ) || '
/* Late Completion Percent */
, ' ||
isc_maint_rpt_util_pkg.change_column
( isc_maint_rpt_util_pkg.rate_column
( 'iset.c_num_late_completion'
, 'iset.c_num_completion'
, ''
, 'Y' )
, isc_maint_rpt_util_pkg.rate_column
( 'iset.p_num_late_completion'
, 'iset.p_num_completion'
, ''
, 'Y' )
, 'BIV_MEASURE11'
, 'N' ) || '
/* Late Completion Change */
, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'iset.c_days_late'
, 'iset.c_num_late_completion'
, 'BIV_MEASURE12'
, 'N' ) || ' /* Average Days Late */
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'
);
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 || '
,''pFunctionName=ISC_MAINT_LATECMPL_DTL_RPT_REP'' ||
''&VIEW_BY_NAME=VIEW_BY_ID'' ||
''&pParamIds=Y'' BIV_ATTRIBUTE1
, nvl(oset.p_num_count,0) BIV_MEASURE1
, nvl(oset.c_num_count,0) BIV_MEASURE2' || '
, ' ||
isc_maint_rpt_util_pkg.change_column
( 'oset.c_num_count'
, 'oset.p_num_count'
, 'BIV_MEASURE3'
, 'Y' ) || ' /* Late Completion Work Orders Change */
, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'oset.c_num_count'
, 'avg(oset.c_num_count_total) over()'
, 'BIV_MEASURE4'
, 'Y' ) || '
/* Percent of Total */ ' || '
, nvl(avg(c_num_count_total) over(),0) BIV_MEASURE5
, ' ||
isc_maint_rpt_util_pkg.change_column
( 'avg(oset.c_num_count_total) over()'
, 'avg(oset.p_num_count_total) over()'
, 'BIV_MEASURE6'
, 'Y' ) || ' /* Grand Total Late Completion Work Orders Change */
, ' ||
isc_maint_rpt_util_pkg.rate_column
( 'avg(c_num_count_total) over()'
, 'avg(c_num_count_total) over ()'
, 'BIV_MEASURE7'
, 'Y' ) || '
/* Grand Total Percent of Total */
, null BIV_ATTRIBUTE10
from
( select * from ( ' || poa_dbi_template_pkg.status_sql
( p_fact_name => l_mv
, p_where_clause => l_where_clause
, p_join_tables => l_join_tbl
, p_use_windowing => 'N'
, p_col_name => l_col_tbl
, p_use_grpid => 'N'
, p_paren_count => 3
, p_filter_where => ''
, p_generate_viewby => 'Y'
);
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
oset.work_order_name BIV_ATTRIBUTE1' || '
, ' || isc_maint_rpt_util_pkg.get_detail_column
(l_detail_col_tbl,'work_order_type','BIV_ATTRIBUTE2') || '
, ' || isc_maint_rpt_util_pkg.get_detail_column
(l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE3') || '
, ' || isc_maint_rpt_util_pkg.get_detail_column
(l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE4') || '
, ' || isc_maint_rpt_util_pkg.get_detail_column
(l_detail_col_tbl,'activity','BIV_ATTRIBUTE5') || '
, oset.wo_status BIV_ATTRIBUTE6' || '
, ' || isc_maint_rpt_util_pkg.get_detail_column
(l_detail_col_tbl,'department','BIV_ATTRIBUTE7') || '
, oset.SCHEDULED_START_DATE BIV_ATTRIBUTE9
, oset.SCHEDULED_COMPLETION_DATE BIV_ATTRIBUTE10
, oset.COMPLETION_DATE BIV_ATTRIBUTE11 ' || '
, ' || isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE8') || '
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
oset.work_order_name BIV_ATTRIBUTE1' || '
, ' || isc_maint_rpt_util_pkg.get_detail_column
(l_detail_col_tbl,'work_order_type','BIV_ATTRIBUTE2') || '
, ' || isc_maint_rpt_util_pkg.get_detail_column
(l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE3') || '
, ' || isc_maint_rpt_util_pkg.get_detail_column
(l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE4') || '
, ' || isc_maint_rpt_util_pkg.get_detail_column
(l_detail_col_tbl,'activity','BIV_ATTRIBUTE5') || '
, oset.wo_status BIV_ATTRIBUTE6' || '
, ' || isc_maint_rpt_util_pkg.get_detail_column
(l_detail_col_tbl,'department','BIV_ATTRIBUTE7') || '
, oset.SCHEDULED_START_DATE BIV_ATTRIBUTE9
, oset.SCHEDULED_COMPLETION_DATE BIV_ATTRIBUTE10
, oset.COMPLETION_DATE BIV_ATTRIBUTE11
, oset.DAYS_LATE BIV_ATTRIBUTE12 ' || '
, null BIV_ATTRIBUTE13
, ' || isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE8') || '
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 '
);