[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_MEASURE12
,BIV_MEASURE3
,BIV_MEASURE4
,BIV_MEASURE13
,BIV_MEASURE5
,BIV_MEASURE6
,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
, ' || l_drill_across_rep_1 || ' BIV_DYNAMIC_URL1 ' || fnd_global.newline ||
',(case when :LAST_REFRESH_DATE <= &BIS_CURRENT_ASOF_DATE THEN ' || l_drill_across_rep_2 || ' ELSE NULL END ) BIV_DYNAMIC_URL2 ' || fnd_global.newline ||
',(case when :LAST_REFRESH_DATE = &BIS_CURRENT_ASOF_DATE THEN ' || l_drill_across_rep_3 || ' ELSE NULL END ) BIV_DYNAMIC_URL3 ' || 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_MEASURE12
,BIV_MEASURE3
,BIV_MEASURE4
,BIV_MEASURE13
,BIV_MEASURE5
,BIV_MEASURE6
,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 ' || fnd_global.newline ||
' FROM ( SELECT ' || fnd_global.newline ||
p_view_by_col || fnd_global.newline ||
',' || 'NVL(c_backlog,0) BIV_MEASURE1 ' || fnd_global.newline ||
',' || 'NVL(p_backlog,0) BIV_MEASURE11 ' || fnd_global.newline ||
',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_backlog'
,prior_col => 'p_backlog'
,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
'SELECT cal.name VIEWBY ' || fnd_global.newline ||
',' || 'NVL(iset.c_backlog,0) BIV_MEASURE1 ' || fnd_global.newline ||
',' || 'NVL(iset.p_backlog,0) BIV_MEASURE11 ' || fnd_global.newline ||
',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_backlog'
,prior_col => 'p_backlog'
,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_ATTRIBUTE9
,BIV_DATE1
,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_ATTRIBUTE9 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_ATTRIBUTE9
,BIV_DATE1
,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 ||
',' || ' fact.repair_line_id BIV_MEASURE2 ' || fnd_global.newline ||
',' || ' fact.master_organization_id BIV_MEASURE3 ' || fnd_global.newline ||
',' || ' serial_number BIV_ATTRIBUTE7 ' || fnd_global.newline ||
',' || ' fl.meaning BIV_ATTRIBUTE8 ' || fnd_global.newline ||
',' || ' incident_id BIV_ATTRIBUTE9 '|| fnd_global.newline ||
',' || ' promise_date BIV_DATE1 ' || fnd_global.newline
|| ' from ' || fnd_global.newline
|| l_mv
|| ' ISC_DR_CURR_01_MV fact, ' || fnd_global.newline
|| ' ENI_ITEM_V EIOV, ' || fnd_global.newline
|| ' CSD_FLOW_STATUSES_B CFSB, ' || fnd_global.newline
|| ' FND_LOOKUPS FL, ' || fnd_global.newline
|| ' MTL_UNITS_OF_MEASURE_VL MUM ' || fnd_global.newline
-- Mapped fact.flow_status_id to CFSB table which will be mapped to FND_LOOKUPS
|| ' WHERE FL.LOOKUP_TYPE = ''CSD_REPAIR_FLOW_STATUS'' ' || fnd_global.newline
|| ' AND FL.LOOKUP_CODE = CFSB.flow_status_code ' || fnd_global.newline
|| ' AND CFSB.flow_status_id = fact.flow_status_id ' || 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
|| ' ) ) where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
ORDER BY rnk' || fnd_global.newline ;
' SELECT range_name BIV_ATTRIBUTE1 ' || fnd_global.newline ||
' ,nvl(past_due_count,0) BIV_MEASURE1 ' || fnd_global.newline ||
' ,' || poa_dbi_util_pkg.rate_clause(numerator => 'past_due_count'
,denominator => 'past_due_count_total'
,rate_type => 'P') || ' BIV_MEASURE2' || fnd_global.newline ||
' ,nvl(past_due_count_total,0) BIV_MEASURE21 ' || fnd_global.newline ||
' ,' || poa_dbi_util_pkg.rate_clause(numerator => 'past_due_count_total'
,denominator => 'past_due_count_total'
,rate_type => 'P') || ' BIV_MEASURE22' || fnd_global.newline ||
' ,' || '''pFunctionName=ISC_DEPOT_PAST_DUE_DTL_TBL_REP&pParamIds=Y&BIV_ATTRIBUTE1=-1&BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET=''|| bucket_number ||''''' || ' BIV_ATTRIBUTE10' ||
' FROM (' || fnd_global.newline ||
' SELECT range_name ' || fnd_global.newline ||
' ,bucket_number ' || fnd_global.newline ||
' ,sum (decode (buckets.bucket_number, 1, past_due_age_b1 ' || fnd_global.newline ||
' ,2, past_due_age_b2 ' || fnd_global.newline ||
' ,3, past_due_age_b3 ' || fnd_global.newline ||
' ,4, past_due_age_b4 ' || fnd_global.newline ||
' ,5, past_due_age_b5 ' || fnd_global.newline ||
' ,6, past_due_age_b6 ' || fnd_global.newline ||
' ,7, past_due_age_b7 ' || fnd_global.newline ||
' ,8, past_due_age_b8 ' || fnd_global.newline ||
' ,9, past_due_age_b9 ' || fnd_global.newline ||
' ,10, past_due_age_b10 ) ) past_due_count ' || fnd_global.newline ||
' ,sum(sum(decode (buckets.bucket_number, 1, past_due_age_b1 ' || fnd_global.newline ||
' ,2, past_due_age_b2 ' || fnd_global.newline ||
' ,3, past_due_age_b3 ' || fnd_global.newline ||
' ,4, past_due_age_b4 ' || fnd_global.newline ||
' ,5, past_due_age_b5 ' || fnd_global.newline ||
' ,6, past_due_age_b6 ' || fnd_global.newline ||
' ,7, past_due_age_b7 ' || fnd_global.newline ||
' ,8, past_due_age_b8 ' || fnd_global.newline ||
' ,9, past_due_age_b9 ' || fnd_global.newline ||
' ,10, past_due_age_b10 ) ) ) over () past_due_count_total ' || fnd_global.newline ||
' from ' || fnd_global.newline ||
l_mv || fnd_global.newline ||
' ISC_DR_CURR_02_MV fact, ' || fnd_global.newline ||
' (';
'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;
'SELECT
BIV_ATTRIBUTE1
,BIV_ATTRIBUTE2
,BIV_ATTRIBUTE3
,BIV_ATTRIBUTE4
,BIV_ATTRIBUTE5
,BIV_ATTRIBUTE6
,BIV_MEASURE1
,BIV_ATTRIBUTE7
,BIV_ATTRIBUTE8
,BIV_ATTRIBUTE9
,BIV_DATE1
,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_ATTRIBUTE9 BIV_DYNAMIC_URL2
FROM (
SELECT
rank() over (&ORDER_BY_CLAUSE nulls last,BIV_ATTRIBUTE1, BIV_MEASURE3) - 1 rnk
,BIV_ATTRIBUTE1
,BIV_ATTRIBUTE2
,BIV_ATTRIBUTE3
,BIV_ATTRIBUTE4
,BIV_ATTRIBUTE5
,BIV_ATTRIBUTE6
,BIV_MEASURE1
,BIV_ATTRIBUTE7
,BIV_ATTRIBUTE8
,BIV_ATTRIBUTE9
,BIV_DATE1
,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 ||
',' || ' fact.repair_line_id BIV_MEASURE3 ' || fnd_global.newline ||
',' || ' fact.master_organization_id BIV_MEASURE4 ' || 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 ||
',' || ' fl.meaning BIV_ATTRIBUTE8 ' || fnd_global.newline ||
',' || ' incident_id BIV_ATTRIBUTE9 ' || fnd_global.newline ||
',' || ' promise_date BIV_DATE1 ' || fnd_global.newline ||
',' || ' PAST_DUE_DAYS BIV_MEASURE2 ' || fnd_global.newline
|| ' from ' || fnd_global.newline
|| l_mv || fnd_global.newline
|| ' ISC_DR_CURR_01_MV fact, ' || fnd_global.newline
|| ' ENI_ITEM_V EIOV, ' || fnd_global.newline
|| ' CSD_FLOW_STATUSES_B CFSB, ' || fnd_global.newline
|| ' FND_LOOKUPS FL, ' || fnd_global.newline
|| ' MTL_UNITS_OF_MEASURE_VL MUM ' || fnd_global.newline
-- Mapped fact.flow_status_id to CFSB table which will be mapped to FND_LOOKUPS
|| ' WHERE FL.LOOKUP_TYPE = ''CSD_REPAIR_FLOW_STATUS'' ' || fnd_global.newline
|| ' AND CFSB.flow_status_id = fact.flow_status_id ' || fnd_global.newline
|| ' AND FL.LOOKUP_CODE = CFSB.flow_status_code ' || fnd_global.newline
|| ' AND FACT.item_org_id = eiov.id ' || fnd_global.newline
|| ' AND FACT.past_due_flag = ''Y'' ' || fnd_global.newline
|| ' AND mum.uom_code = fact.uom_code '
|| l_where_clause
|| ' ) ) where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
ORDER BY rnk' || fnd_global.newline ;
l_mv := l_mv || '( SELECT ' || fnd_global.newline;
' SELECT ' || fnd_global.newline;
'SELECT '|| ISC_DEPOT_RPT_UTIL_PKG.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
l_description || ' BIV_ATTRIBUTE1 ';
'FROM ( SELECT
rank() over (&ORDER_BY_CLAUSE'||' nulls last, '||p_view_by_col||') - 1 rnk
,'||p_view_by_col;
SELECT ' || p_view_by_col || fnd_global.newline ||
',' || ' NVL(c_p_backlog,0) BIV_MEASURE2 ' || fnd_global.newline ||
',' || ' NVL(c_c_backlog,0) BIV_MEASURE1 ' || fnd_global.newline ||
',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_c_backlog'
,prior_col => 'c_p_backlog'
,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent