[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT '|| ISC_DEPOT_RPT_UTIL_PKG.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
l_description || ' BIV_ATTRIBUTE1
,BIV_MEASURE1
,BIV_MEASURE11
,BIV_MEASURE2
,BIV_MEASURE3
,BIV_MEASURE4
,BIV_MEASURE5
,BIV_MEASURE12
,BIV_MEASURE6
,BIV_MEASURE7
,BIV_MEASURE13
,BIV_MEASURE8
,BIV_MEASURE21
,BIV_MEASURE22
,BIV_MEASURE23
,BIV_MEASURE24
,BIV_MEASURE25
,BIV_MEASURE26
,BIV_MEASURE27
,BIV_MEASURE28
,BIV_MEASURE29
,BIV_MEASURE30
,BIV_MEASURE31
,BIV_MEASURE32
,BIV_MEASURE33
,BIV_MEASURE34
,BIV_MEASURE35
,BIV_MEASURE36
,' || l_drill_across_rep_1 || ' BIV_DYNAMIC_URL1
,BIV_DYNAMIC_URL_2 ' || fnd_global.newline ||
'FROM ( SELECT
rank() over (&ORDER_BY_CLAUSE'||' nulls last, '||p_view_by_col||' ) - 1 rnk
,'||p_view_by_col||'
,BIV_MEASURE1
,BIV_MEASURE11
,BIV_MEASURE2
,BIV_MEASURE3
,BIV_MEASURE4
,BIV_MEASURE5
,BIV_MEASURE12
,BIV_MEASURE6
,BIV_MEASURE7
,BIV_MEASURE13
,BIV_MEASURE8
,BIV_MEASURE21
,BIV_MEASURE22
,BIV_MEASURE23
,BIV_MEASURE24
,BIV_MEASURE25
,BIV_MEASURE26
,BIV_MEASURE27
,BIV_MEASURE28
,BIV_MEASURE29
,BIV_MEASURE30
,BIV_MEASURE31
,BIV_MEASURE32
,BIV_MEASURE33
,BIV_MEASURE34
,BIV_MEASURE35
,BIV_MEASURE36
,BIV_DYNAMIC_URL_2 ' || fnd_global.newline ||
' FROM ( SELECT ' || fnd_global.newline ||
p_view_by_col || fnd_global.newline ||
',' || 'NVL(c_completed_count,0) BIV_MEASURE1 ' || fnd_global.newline ||
',' || 'NVL(p_completed_count,0) BIV_MEASURE11 ' || fnd_global.newline ||
',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_completed_count'
,prior_col => 'p_completed_count'
,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
'SELECT cal.name VIEWBY ' || fnd_global.newline ||
',' || 'NVL(iset.c_completed_count,0) BIV_MEASURE1 ' || fnd_global.newline ||
',' || 'NVL(iset.p_completed_count,0) BIV_MEASURE11 ' || fnd_global.newline ||
',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_completed_count'
,prior_col => 'p_completed_count'
,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
'SELECT
BIV_ATTRIBUTE1
,BIV_ATTRIBUTE2
,BIV_ATTRIBUTE3
,BIV_ATTRIBUTE4
,BIV_ATTRIBUTE5
,BIV_ATTRIBUTE6
,BIV_MEASURE1
,BIV_ATTRIBUTE7
,BIV_ATTRIBUTE8
,BIV_DATE1
,BIV_DATE2
,BIV_MEASURE2
,BIV_MEASURE3
,''pFunctionName=CSD_RO_DETAILS&csdInvOrgId=''||BIV_MEASURE3||''&csdRepairLineId=''||BIV_MEASURE2 BIV_DYNAMIC_URL1
,' || ISC_DEPOT_RPT_UTIL_PKG.get_service_request_url || ' || BIV_ATTRIBUTE8 BIV_DYNAMIC_URL2
FROM (
SELECT
rank() over (&ORDER_BY_CLAUSE nulls last,BIV_ATTRIBUTE1) - 1 rnk
,BIV_ATTRIBUTE1
,BIV_ATTRIBUTE2
,BIV_ATTRIBUTE3
,BIV_ATTRIBUTE4
,BIV_ATTRIBUTE5
,BIV_ATTRIBUTE6
,BIV_MEASURE1
,BIV_ATTRIBUTE7
,BIV_ATTRIBUTE8
,BIV_DATE1
,BIV_DATE2
,BIV_MEASURE2
,BIV_MEASURE3
FROM (
SELECT repair_number BIV_ATTRIBUTE1 ' || fnd_global.newline ||
',' || ' incident_number BIV_ATTRIBUTE2 ' || fnd_global.newline ||
',' || ' crt.name BIV_ATTRIBUTE3 ' || fnd_global.newline ||
',' || ' eiov.value BIV_ATTRIBUTE4 ' || fnd_global.newline ||
',' || ' eiov.description BIV_ATTRIBUTE5 ' || fnd_global.newline ||
',' || ' mum.unit_of_measure BIV_ATTRIBUTE6 ' || fnd_global.newline ||
',' || ' quantity BIV_MEASURE1 ' || fnd_global.newline ||
',' || ' serial_number BIV_ATTRIBUTE7 ' || fnd_global.newline ||
',' || ' incident_id BIV_ATTRIBUTE8 ' || fnd_global.newline ||
',' || ' promise_date BIV_DATE1 ' || fnd_global.newline ||
',' || ' date_closed BIV_DATE2 ' || fnd_global.newline ||
',' || ' fact.repair_line_id BIV_MEASURE2 ' || fnd_global.newline ||
',' || ' fact.master_organization_id BIV_MEASURE3 ' || fnd_global.newline
|| ' from ' || fnd_global.newline
|| l_mv || fnd_global.newline
|| ' ISC_DR_REPAIR_ORDERS_F fact, ' || fnd_global.newline
|| ' ENI_ITEM_V EIOV, ' || fnd_global.newline
|| ' FND_LOOKUPS FL, ' || fnd_global.newline
|| ' MTL_UNITS_OF_MEASURE_VL MUM ' || fnd_global.newline
|| ' WHERE dbi_date_closed between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE ' || fnd_global.newline
|| ' AND FL.LOOKUP_TYPE = ''CSD_REPAIR_STATUS'' ' || fnd_global.newline
|| ' AND FL.LOOKUP_CODE = fact.status ' || fnd_global.newline
|| ' AND FACT.item_org_id = eiov.id ' || fnd_global.newline
|| ' AND mum.uom_code = fact.uom_code ' || fnd_global.newline
|| l_where_clause || fnd_global.newline
|| ' ) ) where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
ORDER BY rnk' || fnd_global.newline ;
'SELECT
BIV_ATTRIBUTE1
,BIV_ATTRIBUTE2
,BIV_ATTRIBUTE3
,BIV_ATTRIBUTE4
,BIV_ATTRIBUTE5
,BIV_ATTRIBUTE6
,BIV_MEASURE1
,BIV_ATTRIBUTE7
,BIV_ATTRIBUTE8
,BIV_DATE1
,BIV_DATE2
,BIV_MEASURE2
,BIV_MEASURE3
,BIV_MEASURE4
,''pFunctionName=CSD_RO_DETAILS&csdInvOrgId=''||BIV_MEASURE4||''&csdRepairLineId=''||BIV_MEASURE3 BIV_DYNAMIC_URL1
,' || ISC_DEPOT_RPT_UTIL_PKG.get_service_request_url || ' || BIV_ATTRIBUTE8 BIV_DYNAMIC_URL2
FROM (
SELECT
rank() over (&ORDER_BY_CLAUSE nulls last,BIV_ATTRIBUTE1 ) - 1 rnk
,BIV_ATTRIBUTE1
,BIV_ATTRIBUTE2
,BIV_ATTRIBUTE3
,BIV_ATTRIBUTE4
,BIV_ATTRIBUTE5
,BIV_ATTRIBUTE6
,BIV_MEASURE1
,BIV_ATTRIBUTE7
,BIV_ATTRIBUTE8
,BIV_DATE1
,BIV_DATE2
,BIV_MEASURE2
,BIV_MEASURE3
,BIV_MEASURE4
FROM (
SELECT repair_number BIV_ATTRIBUTE1 ' || fnd_global.newline ||
',' || ' incident_number BIV_ATTRIBUTE2 ' || fnd_global.newline ||
',' || ' crt.name BIV_ATTRIBUTE3 ' || fnd_global.newline ||
',' || ' eiov.value BIV_ATTRIBUTE4 ' || fnd_global.newline ||
',' || ' eiov.description BIV_ATTRIBUTE5 ' || fnd_global.newline ||
',' || ' mum.unit_of_measure BIV_ATTRIBUTE6 ' || fnd_global.newline ||
',' || ' quantity BIV_MEASURE1 ' || fnd_global.newline ||
',' || ' serial_number BIV_ATTRIBUTE7 ' || fnd_global.newline ||
',' || ' incident_id BIV_ATTRIBUTE8 ' || fnd_global.newline ||
',' || ' promise_date BIV_DATE1 ' || fnd_global.newline ||
',' || ' date_closed BIV_DATE2 ' || fnd_global.newline ||
',' || ' trunc(date_closed) - trunc(promise_date) BIV_MEASURE2 ' || fnd_global.newline ||
',' || ' fact.repair_line_id BIV_MEASURE3 ' || fnd_global.newline ||
',' || ' fact.master_organization_id BIV_MEASURE4 ' || fnd_global.newline
|| ' from ' || fnd_global.newline
|| l_mv || fnd_global.newline
|| ' ISC_DR_REPAIR_ORDERS_F fact, ' || fnd_global.newline
|| ' ENI_ITEM_V EIOV, ' || fnd_global.newline
|| ' FND_LOOKUPS FL, ' || fnd_global.newline
|| ' MTL_UNITS_OF_MEASURE_VL MUM ' || fnd_global.newline
|| ' WHERE dbi_date_closed between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE ' || fnd_global.newline
|| ' AND dbi_date_closed > fact.promise_date ' || fnd_global.newline
|| ' AND FL.LOOKUP_TYPE = ''CSD_REPAIR_STATUS'' ' || fnd_global.newline
|| ' AND FL.LOOKUP_CODE = fact.status ' || fnd_global.newline
|| ' AND FACT.item_org_id = eiov.id ' || fnd_global.newline
|| ' AND mum.uom_code = fact.uom_code ' || fnd_global.newline
|| l_where_clause || fnd_global.newline
|| ' ) ) where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
ORDER BY rnk' || fnd_global.newline ;
l_query := ' SELECT range_name BIV_ATTRIBUTE1 ' || fnd_global.newline ||
' ,nvl(c_LATE_COMPLETE_COUNT,0) BIV_MEASURE1 ' || fnd_global.newline ||
' ,nvl(p_LATE_COMPLETE_COUNT,0) BIV_MEASURE11 ' || fnd_global.newline ||
' ,' || poa_dbi_util_pkg.change_clause(cur_col => 'c_LATE_COMPLETE_COUNT'
,prior_col => 'p_LATE_COMPLETE_COUNT'
,change_type => 'NP') || 'BIV_MEASURE2' || fnd_global.newline ||
' ,'|| poa_dbi_util_pkg.rate_clause(numerator => 'c_LATE_COMPLETE_COUNT'
,denominator => 'c_LATE_COMPLETE_COUNT_TOTAL'
,rate_type => 'P') || 'BIV_MEASURE3' || fnd_global.newline ||
' ,nvl(c_LATE_COMPLETE_COUNT_total,0) BIV_MEASURE21 ' || fnd_global.newline ||
' ,'|| poa_dbi_util_pkg.change_clause(cur_col => 'c_LATE_COMPLETE_COUNT_total'
,prior_col => 'p_LATE_COMPLETE_COUNT_total'
,change_type => 'NP') || 'BIV_MEASURE22' || fnd_global.newline ||
' ,'|| poa_dbi_util_pkg.rate_clause(numerator => 'c_LATE_COMPLETE_COUNT_TOTAL'
,denominator => 'c_LATE_COMPLETE_COUNT_TOTAL'
,rate_type => 'P') || 'BIV_MEASURE23' || fnd_global.newline ||
-- ',' || 'ISC_DEPOT_COMPLETION_PKG.GET_BUCKET_DRILL_ACROSS_URL(''ISC_DEPOT_LAT_COMP_DTL_TBL_REP'', bucket_number)' || ' BIV_DYNAMIC_URL1 ' ||
',' || '''pFunctionName=ISC_DEPOT_LAT_COMP_DTL_TBL_REP&pParamIds=Y&BIV_ATTRIBUTE1=-1&BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET=''|| bucket_number ||''''' || ' BIV_DYNAMIC_URL1 ' ||
' FROM (select sum (decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, ' || fnd_global.newline ||
' decode (buckets.bucket_number, 1, days_late_age_b1 ' || fnd_global.newline ||
' ,2, days_late_age_b2 ' || fnd_global.newline ||
' ,3, days_late_age_b3 ' || fnd_global.newline ||
' ,4, days_late_age_b4 ' || fnd_global.newline ||
' ,5, days_late_age_b5 ' || fnd_global.newline ||
' ,6, days_late_age_b6 ' || fnd_global.newline ||
' ,7, days_late_age_b7 ' || fnd_global.newline ||
' ,8, days_late_age_b8 ' || fnd_global.newline ||
' ,9, days_late_age_b9 ' || fnd_global.newline ||
' ,10, days_late_age_b10 ))) c_LATE_COMPLETE_COUNT ' || fnd_global.newline ||
' ,sum (decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, ' || fnd_global.newline ||
' decode (buckets.bucket_number, 1, days_late_age_b1 ' || fnd_global.newline ||
' ,2, days_late_age_b2 ' || fnd_global.newline ||
' ,3, days_late_age_b3 ' || fnd_global.newline ||
' ,4, days_late_age_b4 ' || fnd_global.newline ||
' ,5, days_late_age_b5 ' || fnd_global.newline ||
' ,6, days_late_age_b6 ' || fnd_global.newline ||
' ,7, days_late_age_b7 ' || fnd_global.newline ||
' ,8, days_late_age_b8 ' || fnd_global.newline ||
' ,9, days_late_age_b9 ' || fnd_global.newline ||
' ,10, days_late_age_b10 ))) p_LATE_COMPLETE_COUNT ' || fnd_global.newline ||
-- ' ,0 c_LATE_COMPLETE_COUNT_total , 0 p_LATE_COMPLETE_COUNT_total ' ||
' ,sum (sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, ' || fnd_global.newline ||
' decode (buckets.bucket_number, 1, days_late_age_b1 ' || fnd_global.newline ||
' ,2, days_late_age_b2 ' || fnd_global.newline ||
' ,3, days_late_age_b3 ' || fnd_global.newline ||
' ,4, days_late_age_b4 ' || fnd_global.newline ||
' ,5, days_late_age_b5 ' || fnd_global.newline ||
' ,6, days_late_age_b6 ' || fnd_global.newline ||
' ,7, days_late_age_b7 ' || fnd_global.newline ||
' ,8, days_late_age_b8 ' || fnd_global.newline ||
' ,9, days_late_age_b9 ' || fnd_global.newline ||
' ,10, days_late_age_b10 )))) over () c_LATE_COMPLETE_COUNT_total ' || fnd_global.newline ||
' ,sum (sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, ' || fnd_global.newline ||
' decode (buckets.bucket_number, 1, days_late_age_b1 ' || fnd_global.newline ||
' ,2, days_late_age_b2 ' || fnd_global.newline ||
' ,3, days_late_age_b3 ' || fnd_global.newline ||
' ,4, days_late_age_b4 ' || fnd_global.newline ||
' ,5, days_late_age_b5 ' || fnd_global.newline ||
' ,6, days_late_age_b6 ' || fnd_global.newline ||
' ,7, days_late_age_b7 ' || fnd_global.newline ||
' ,8, days_late_age_b8 ' || fnd_global.newline ||
' ,9, days_late_age_b9 ' || fnd_global.newline ||
' ,10, days_late_age_b10 )))) over () p_LATE_COMPLETE_COUNT_total ' || fnd_global.newline ||
' ,range_name ' || fnd_global.newline ||
' ,buckets.bucket_number ' || fnd_global.newline ||
' from (';
'SELECT '|| i || ' bucket_number, ' || fnd_global.newline ||
' bbct.range'|| i ||'_name range_name, ' || fnd_global.newline ||
' bbc.range' || i || '_low range_low, ' || fnd_global.newline ||
' bbc.range' || i || '_high range_high ' || fnd_global.newline ||
'FROM bis_bucket_customizations bbc, ' || fnd_global.newline ||
' bis_bucket bb, ' || fnd_global.newline ||
' bis_bucket_customizations_tl bbct ' || fnd_global.newline ||
'WHERE short_name = ''ISC_DEPOT_BKLG_CMP_AGING'' ' || fnd_global.newline ||
' and bb.bucket_id = bbc.bucket_id ' || fnd_global.newline ||
' and nvl(bbc.range' || i || '_low,bbc.range' || i || '_high) is not null' || fnd_global.newline ||
' and bbct.language =USERENV(''LANG'') ' || fnd_global.newline ||
' and bbC.id = bbct.id '|| fnd_global.newline;