[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
(p_join_tbl);
'SELECT
' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
|| l_view_by_col_name || ' OPI_ATTRIBUTE1,
' || l_description || ' OPI_ATTRIBUTE2,
' || 'oset.OPI_MEASURE1,
' || 'oset.OPI_MEASURE2,
' || 'oset.OPI_MEASURE3,
' || 'oset.OPI_MEASURE4,
' || 'oset.OPI_MEASURE5,
' || 'oset.OPI_MEASURE6,
' || 'oset.OPI_MEASURE7,
' || 'oset.OPI_MEASURE8,
' || 'oset.OPI_MEASURE9,
' || 'oset.OPI_MEASURE10,
' || 'oset.OPI_MEASURE11,
' || 'oset.OPI_MEASURE12,
' || 'oset.OPI_MEASURE13,
' || 'oset.OPI_MEASURE14,
' || 'oset.OPI_MEASURE15,
' || 'oset.OPI_MEASURE16,
' || 'oset.OPI_MEASURE17,
' || 'oset.OPI_MEASURE18,
' || 'oset.OPI_MEASURE19,
' || 'oset.OPI_MEASURE20,
' || 'oset.OPI_MEASURE21,
' || 'oset.OPI_MEASURE22,
' || 'oset.OPI_MEASURE23,
' || 'oset.OPI_DYNAMIC_URL_1,
' || 'oset.OPI_DYNAMIC_URL_2
' || 'FROM
' || '(SELECT (rank () over
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE1,
' || 'OPI_MEASURE2,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE5,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE7,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE10,
' || 'OPI_MEASURE11,
' || 'OPI_MEASURE12,
' || 'OPI_MEASURE13,
' || 'OPI_MEASURE14,
' || 'OPI_MEASURE15,
' || 'OPI_MEASURE16,
' || 'OPI_MEASURE17,
' || 'OPI_MEASURE18,
' || 'OPI_MEASURE19,
' || 'OPI_MEASURE20,
' || 'OPI_MEASURE21,
' || 'OPI_MEASURE22,
' || 'OPI_MEASURE23,
' || 'OPI_DYNAMIC_URL_1,
' || 'OPI_DYNAMIC_URL_2
' || 'FROM
' || '(SELECT
' || l_view_by_fact_col || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('c_onhand_value')
|| ' OPI_MEASURE1,
' || opi_dbi_rpt_util_pkg.change_str ('c_onhand_value',
'p_onhand_value',
'p_onhand_value',
'OPI_MEASURE2') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('c_intransit_value')
|| ' OPI_MEASURE3,
' || opi_dbi_rpt_util_pkg.change_str ('c_intransit_value',
'p_intransit_value',
'p_intransit_value',
'OPI_MEASURE4') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('c_wip_value')
|| ' OPI_MEASURE5,
' || opi_dbi_rpt_util_pkg.change_str ('c_wip_value',
'p_wip_value',
'p_wip_value',
'OPI_MEASURE6') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('p_inv_total_value')
|| ' OPI_MEASURE7,
' || opi_dbi_rpt_util_pkg.nvl_str ('c_inv_total_value')
|| ' OPI_MEASURE8,
' || opi_dbi_rpt_util_pkg.change_str ('c_inv_total_value',
'p_inv_total_value',
'p_inv_total_value',
'OPI_MEASURE9') || ',
' || opi_dbi_rpt_util_pkg.percent_str ('c_inv_total_value',
'c_inv_total_value_total',
'OPI_MEASURE10') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('c_onhand_value_total')
|| ' OPI_MEASURE11,
' || opi_dbi_rpt_util_pkg.change_str ('c_onhand_value_total',
'p_onhand_value_total',
'p_onhand_value_total',
'OPI_MEASURE12') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('c_intransit_value_total')
|| ' OPI_MEASURE13,
' || opi_dbi_rpt_util_pkg.change_str ('c_intransit_value_total',
'p_intransit_value_total',
'p_intransit_value_total',
'OPI_MEASURE14') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('c_wip_value_total')
|| ' OPI_MEASURE15,
' || opi_dbi_rpt_util_pkg.change_str ('c_wip_value_total',
'p_wip_value_total',
'p_wip_value_total',
'OPI_MEASURE16') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('c_inv_total_value_total')
|| ' OPI_MEASURE17,
' || opi_dbi_rpt_util_pkg.change_str ('c_inv_total_value_total',
'p_inv_total_value_total',
'p_inv_total_value_total',
'OPI_MEASURE18') || ',
' || opi_dbi_rpt_util_pkg.percent_str ('c_inv_total_value_total',
'c_inv_total_value_total',
'OPI_MEASURE19') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('c_inv_total_value')
|| ' OPI_MEASURE20,
' || opi_dbi_rpt_util_pkg.nvl_str ('p_inv_total_value')
|| ' OPI_MEASURE21,
' || opi_dbi_rpt_util_pkg.nvl_str ('c_inv_total_value_total')
|| ' OPI_MEASURE22,
' || opi_dbi_rpt_util_pkg.nvl_str ('p_inv_total_value_total')
|| ' OPI_MEASURE23,
' || '''pFunctionName=OPI_DBI_INV_ONH_ORG_TBL_REP&VIEW_BY_NAME=VIEW_BY_VALUE&VIEW_BY=' || p_view_by_dim || ''' OPI_DYNAMIC_URL_1,
' || '''pFunctionName=OPI_DBI_INV_INT_ORG_TBL_REP&VIEW_BY_NAME=VIEW_BY_VALUE&VIEW_BY=' || p_view_by_dim || ''' OPI_DYNAMIC_URL_2 ';
'SELECT
' || ' cal.name VIEWBY,
' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_onhand_value')
|| ' OPI_MEASURE1,
' || opi_dbi_rpt_util_pkg.change_str ('iset.c_onhand_value',
'iset.p_onhand_value',
'iset.p_onhand_value',
'OPI_MEASURE2') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_intransit_value')
|| ' OPI_MEASURE3,
' || opi_dbi_rpt_util_pkg.change_str ('iset.c_intransit_value',
'iset.p_intransit_value',
'iset.p_intransit_value',
'OPI_MEASURE4') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_wip_value')
|| ' OPI_MEASURE5,
' || opi_dbi_rpt_util_pkg.change_str ('iset.c_wip_value',
'iset.p_wip_value',
'iset.p_wip_value',
'OPI_MEASURE6') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('iset.p_inv_total_value')
|| ' OPI_MEASURE8,
' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_inv_total_value')
|| ' OPI_MEASURE7,
' || opi_dbi_rpt_util_pkg.change_str ('iset.c_inv_total_value',
'iset.p_inv_total_value',
'iset.p_inv_total_value',
'OPI_MEASURE9') ; --OPI Measure 9 is added for bug 3570094
l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
(p_join_tbl);
'SELECT
' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
|| l_view_by_col_name || ' OPI_ATTRIBUTE1,
' || l_description || ' OPI_ATTRIBUTE2,
' || l_uom || ' OPI_ATTRIBUTE3,
' || 'oset.OPI_MEASURE1,
' || 'oset.OPI_MEASURE2,
' || 'oset.OPI_MEASURE3,
' || 'oset.OPI_MEASURE4,
' || 'oset.OPI_MEASURE5,
' || 'oset.OPI_MEASURE6,
' || 'oset.OPI_MEASURE7,
' || 'oset.OPI_MEASURE8,
' || 'oset.OPI_MEASURE9,
' || 'oset.OPI_MEASURE10,
' || 'OPI_DYNAMIC_URL_1
' || 'FROM
' || '(SELECT (rank () over
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE1,
' || 'OPI_MEASURE2,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE5,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE7,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE10,
' || 'OPI_DYNAMIC_URL_1
' || 'FROM
' || '(SELECT
' || l_view_by_fact_col || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('c_onhand_qty')
|| ' OPI_MEASURE1,
' || opi_dbi_rpt_util_pkg.nvl_str ('p_onhand_qty')
|| ' OPI_MEASURE2,
' || opi_dbi_rpt_util_pkg.change_str ('c_onhand_qty',
'p_onhand_qty',
'p_onhand_qty',
'OPI_MEASURE3') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('p_onhand_value')
|| ' OPI_MEASURE4,
' || opi_dbi_rpt_util_pkg.nvl_str ('c_onhand_value')
|| ' OPI_MEASURE5,
' || opi_dbi_rpt_util_pkg.change_str ('c_onhand_value',
'p_onhand_value',
'p_onhand_value',
'OPI_MEASURE6') || ',
' || opi_dbi_rpt_util_pkg.percent_str ('c_onhand_value',
'c_onhand_value_total',
'OPI_MEASURE7') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('c_onhand_value_total')
|| ' OPI_MEASURE8,
' || opi_dbi_rpt_util_pkg.change_str ('c_onhand_value_total',
'p_onhand_value_total',
'p_onhand_value_total',
'OPI_MEASURE9') || ',
' || opi_dbi_rpt_util_pkg.percent_str ('c_onhand_value_total',
'c_onhand_value_total',
'OPI_MEASURE10') || ',
' || l_drill_url_1 || ' OPI_DYNAMIC_URL_1 ';
l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
(p_join_tbl);
'SELECT
' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
|| l_view_by_col_name || ' OPI_ATTRIBUTE1,
' || l_description || ' OPI_ATTRIBUTE2,
' || l_uom || ' OPI_ATTRIBUTE3,
' || 'oset.OPI_MEASURE1,
' || 'oset.OPI_MEASURE2,
' || 'oset.OPI_MEASURE3,
' || 'oset.OPI_MEASURE4,
' || 'oset.OPI_MEASURE5,
' || 'oset.OPI_MEASURE6,
' || 'oset.OPI_MEASURE7,
' || 'oset.OPI_MEASURE8,
' || 'oset.OPI_MEASURE9,
' || 'oset.OPI_MEASURE10
' || 'FROM
' || '(SELECT (rank () over
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE1,
' || 'OPI_MEASURE2,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE5,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE7,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE10
' || 'FROM
' || '(SELECT
' || l_view_by_fact_col || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('c_intransit_qty')
|| ' OPI_MEASURE1,
' || opi_dbi_rpt_util_pkg.nvl_str ('p_intransit_qty')
|| ' OPI_MEASURE2,
' || opi_dbi_rpt_util_pkg.change_str ('c_intransit_qty',
'p_intransit_qty',
'p_intransit_qty',
'OPI_MEASURE3') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('p_intransit_value')
|| ' OPI_MEASURE4,
' || opi_dbi_rpt_util_pkg.nvl_str ('c_intransit_value')
|| ' OPI_MEASURE5,
' || opi_dbi_rpt_util_pkg.change_str ('c_intransit_value',
'p_intransit_value',
'p_intransit_value',
'OPI_MEASURE6') || ',
' || opi_dbi_rpt_util_pkg.percent_str ('c_intransit_value',
'c_intransit_value_total',
'OPI_MEASURE7') || ',
' || opi_dbi_rpt_util_pkg.nvl_str ('c_intransit_value_total')
|| ' OPI_MEASURE8,
' || opi_dbi_rpt_util_pkg.change_str ('c_intransit_value_total',
'p_intransit_value_total',
'p_intransit_value_total',
'OPI_MEASURE9') || ',
' || opi_dbi_rpt_util_pkg.percent_str ('c_intransit_value_total',
'c_intransit_value_total',
'OPI_MEASURE10');
select
inventory_type OPI_ATTRIBUTE1,
' || opi_dbi_rpt_util_pkg.nvl_str ('c_value') ||
' OPI_MEASURE1,
' || opi_dbi_rpt_util_pkg.change_str ('c_value',
'p_value',
'p_value',
'OPI_MEASURE2') || '
from
(select
fnd.meaning inventory_type,
sum (decode (fnd.lookup_code,
''ONH'', oset.c_onhand_value,
''INT'', oset.c_intransit_value,
''WIP'', oset.c_wip_value)) c_value,
sum (decode (fnd.lookup_code,
''ONH'', oset.p_onhand_value,
''INT'', oset.p_intransit_value,
''WIP'', oset.p_wip_value)) p_value
' ||
' from
(select
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
onhand_value_'
|| l_cur_suffix || ', null)) c_onhand_value,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
onhand_value_'
|| l_cur_suffix || ', null)) p_onhand_value,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
intransit_value_'
|| l_cur_suffix || ', null)) c_intransit_value,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
intransit_value_'
|| l_cur_suffix || ', null)) p_intransit_value,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
wip_value_'
|| l_cur_suffix || ', null)) c_wip_value,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
wip_value_'
|| l_cur_suffix || ', null)) p_wip_value
from ' || l_mv || ' fact, fii_time_rpt_struct_v cal
where fact.time_id = cal.time_id
' || l_where_clause || '
and cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
and bitand(cal.record_type_id, 1143) = cal.record_type_id
) oset,
fnd_lookup_values_vl fnd
where fnd.lookup_type = ''OPI_DBI_INV_TYPE''
-- and fnd.language = USERENV(''LANG'')
group by fnd.meaning) oset2
&ORDER_BY_CLAUSE nulls last';