[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Description: builds the outer select clause for
Cycle Count Accuracy Report
---------------------------------------------------*/
FUNCTION get_cc_rpt_sel_clause(p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2
IS
l_sel_clause VARCHAR2(15000);
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_MEASURE3,
' || 'oset.OPI_MEASURE4,
' || 'oset.OPI_MEASURE5,
' || 'oset.OPI_MEASURE7,
' || 'oset.OPI_MEASURE8,
' || 'oset.OPI_MEASURE9,
' || 'oset.OPI_MEASURE10,
' || '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_MEASURE24,
' || 'oset.OPI_MEASURE25,
' || 'oset.OPI_MEASURE26,
' || 'oset.OPI_MEASURE27,
' || 'oset.OPI_MEASURE28,
' || 'oset.OPI_MEASURE29,
' || 'oset.OPI_MEASURE30,
' || 'oset.OPI_MEASURE31,
' || 'oset.OPI_MEASURE32,
' || 'oset.OPI_MEASURE33,
' || 'oset.OPI_MEASURE34,
' || l_drill_across || '
' || 'FROM
' || '(SELECT (rank () over
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE1,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE5,
' || 'OPI_MEASURE7,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE10,
' || '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_MEASURE24,
' || 'OPI_MEASURE25,
' || 'OPI_MEASURE26,
' || 'OPI_MEASURE27,
' || 'OPI_MEASURE28,
' || 'OPI_MEASURE29,
' || 'OPI_MEASURE30,
' || 'OPI_MEASURE31,
' || 'OPI_MEASURE32,
' || 'OPI_MEASURE33,
' || 'OPI_MEASURE34
' || 'FROM
' || '(SELECT
' || l_view_by_fact_col || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_tot_entries',
p_default_val => 0)
|| ' OPI_MEASURE1,
' || opi_dbi_rpt_util_pkg.percent_str(
p_numerator => 'p_hits',
p_denominator => 'p_tot_entries',
p_measure_name => 'OPI_MEASURE3') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator => 'c_hits',
p_denominator => 'c_tot_entries',
p_measure_name => 'OPI_MEASURE4') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_hits',
p_new_denominator => 'c_tot_entries',
p_old_numerator => 'p_hits',
p_old_denominator => 'p_tot_entries',
p_measure_name => 'OPI_MEASURE5') || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_gross_adj_val',
p_default_val => 0)
|| ' OPI_MEASURE7,
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator => 'p_gross_adj_val',
p_denominator => 'p_system_val',
p_measure_name => 'OPI_MEASURE8') || ',
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator => 'c_gross_adj_val',
p_denominator => 'c_system_val',
p_measure_name => 'OPI_MEASURE9') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_gross_adj_val',
p_new_denominator => 'c_system_val',
p_old_numerator => 'p_gross_adj_val',
p_old_denominator => 'p_system_val',
p_measure_name => 'OPI_MEASURE10') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator => 'p_exact_matches',
p_denominator => 'p_tot_entries',
p_measure_name => 'OPI_MEASURE12') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator => 'c_exact_matches',
p_denominator => 'c_tot_entries',
p_measure_name => 'OPI_MEASURE13') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_exact_matches',
p_new_denominator => 'c_tot_entries',
p_old_numerator => 'p_exact_matches',
p_old_denominator => 'p_tot_entries',
p_measure_name => 'OPI_MEASURE14') || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_tot_entries_total',
p_default_val => 0)
|| ' OPI_MEASURE15,
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'c_hits_total',
p_denominator =>'c_tot_entries_total',
p_measure_name => 'OPI_MEASURE16') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_hits_total',
p_new_denominator => 'c_tot_entries_total',
p_old_numerator => 'p_hits_total',
p_old_denominator => 'p_tot_entries_total',
p_measure_name => 'OPI_MEASURE17') || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_gross_adj_val_total',
p_default_val => 0)
|| ' OPI_MEASURE18,
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator =>'c_gross_adj_val_total',
p_denominator =>'c_system_val_total',
p_measure_name => 'OPI_MEASURE19') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_gross_adj_val_total',
p_new_denominator => 'c_system_val_total',
p_old_numerator => 'p_gross_adj_val_total',
p_old_denominator => 'p_system_val_total',
p_measure_name => 'OPI_MEASURE20') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'c_exact_matches_total',
p_denominator => 'c_tot_entries_total',
p_measure_name => 'OPI_MEASURE21') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str(
p_new_numerator => 'c_exact_matches_total',
p_new_denominator => 'c_tot_entries_total',
p_old_numerator => 'p_exact_matches_total',
p_old_denominator => 'p_tot_entries_total',
p_measure_name => 'OPI_MEASURE22') ||',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'c_hits',
p_denominator => 'c_tot_entries',
p_measure_name => 'OPI_MEASURE23') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'p_hits',
p_denominator => 'p_tot_entries',
p_measure_name => 'OPI_MEASURE24') || ',
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator =>'c_gross_adj_val',
p_denominator => 'c_system_val',
p_measure_name => 'OPI_MEASURE25') || ',
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator =>'p_gross_adj_val',
p_denominator => 'p_system_val',
p_measure_name => 'OPI_MEASURE26') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'c_exact_matches',
p_denominator => 'c_tot_entries',
p_measure_name => 'OPI_MEASURE27') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'p_exact_matches',
p_denominator => 'p_tot_entries',
p_measure_name => 'OPI_MEASURE28') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'c_hits_total',
p_denominator => 'c_tot_entries_total',
p_measure_name => 'OPI_MEASURE29') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'p_hits_total',
p_denominator => 'p_tot_entries_total',
p_measure_name => 'OPI_MEASURE30') || ',
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator =>'c_gross_adj_val_total',
p_denominator => 'c_system_val_total',
p_measure_name => 'OPI_MEASURE31') || ',
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator =>'p_gross_adj_val_total',
p_denominator => 'p_system_val_total',
p_measure_name => 'OPI_MEASURE32') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'c_exact_matches_total',
p_denominator => 'c_tot_entries_total',
p_measure_name => 'OPI_MEASURE33') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'p_exact_matches_total',
p_denominator => 'p_tot_entries_total',
p_measure_name => 'OPI_MEASURE34') ;
column for item description to outer select
------------------------------------------------------------------------------------*/
PROCEDURE get_cc_item_columns ( p_dim_name VARCHAR2,
p_description OUT NOCOPY VARCHAR2,
p_col_type IN VARCHAR2 := 'ITEM')
IS
l_view VARCHAR2(3);
Description: builds the outer select clause for
Cycle Count Accuracy Trend Report
---------------------------------------------------*/
FUNCTION get_cc_trd_sel_clause (p_view_by_dim IN VARCHAR2)
RETURN VARCHAR2
IS
l_sel_clause varchar2(7500);
'SELECT
' || ' cal.name VIEWBY,
' || opi_dbi_rpt_util_pkg.nvl_str (p_str => 'iset.c_tot_entries')
|| ' OPI_MEASURE1,
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'iset.p_hits',
p_denominator => 'iset.p_tot_entries',
p_measure_name => 'OPI_MEASURE3') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'iset.c_hits',
p_denominator => 'iset.c_tot_entries',
p_measure_name => 'OPI_MEASURE4') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'iset.c_hits',
p_new_denominator => 'iset.c_tot_entries',
p_old_numerator => 'iset.p_hits',
p_old_denominator => 'iset.p_tot_entries',
p_measure_name => 'OPI_MEASURE5') || ',
' || opi_dbi_rpt_util_pkg.nvl_str (p_str => 'iset.c_gross_adj_val')
|| ' OPI_MEASURE7,
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator =>'iset.p_gross_adj_val',
p_denominator => 'iset.p_system_val',
p_measure_name => 'OPI_MEASURE8') || ',
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator =>'iset.c_gross_adj_val',
p_denominator => 'iset.c_system_val',
p_measure_name => 'OPI_MEASURE9') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'iset.c_gross_adj_val',
p_new_denominator => 'iset.c_system_val',
p_old_numerator => 'iset.p_gross_adj_val',
p_old_denominator => 'iset.p_system_val',
p_measure_name => 'OPI_MEASURE10') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'iset.p_exact_matches',
p_denominator => 'iset.p_tot_entries',
p_measure_name => 'OPI_MEASURE12') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'iset.c_exact_matches',
p_denominator => 'iset.c_tot_entries',
p_measure_name => 'OPI_MEASURE13') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'iset.c_exact_matches',
p_new_denominator => 'iset.c_tot_entries',
p_old_numerator => 'iset.p_exact_matches',
p_old_denominator => 'iset.p_tot_entries',
p_measure_name => 'OPI_MEASURE14') ;
Description: builds the outer select clause for
Hit/Miss Summary Report
---------------------------------------------------*/
FUNCTION get_hitmiss_sel_clause(p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
return VARCHAR2
IS
l_sel_clause varchar2(7000);
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_MEASURE3,
' || 'oset.OPI_MEASURE4,
' || 'oset.OPI_MEASURE5,
' || 'oset.OPI_MEASURE6,
' || 'oset.OPI_MEASURE8,
' || 'oset.OPI_MEASURE9,
' || 'oset.OPI_MEASURE10,
' || 'oset.OPI_MEASURE11,
' || '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_MEASURE24,
' || 'oset.OPI_MEASURE25,
' || 'oset.OPI_MEASURE26
' || 'FROM
' || '(SELECT (rank () over
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE1,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE5,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE10,
' || 'OPI_MEASURE11,
' || 'OPI_MEASURE13,
' || 'OPI_MEASURE14,
' || 'OPI_MEASURE15,
' || 'OPI_MEASURE16,
' || 'OPI_MEASURE17,
' || 'OPI_MEASURE18,
' || 'OPI_MEASURE19,
' || 'OPI_MEASURE20,
' || 'OPI_MEASURE21,
' || 'OPI_MEASURE22,
' || 'OPI_MEASURE23,
' || 'OPI_MEASURE24,
' || 'OPI_MEASURE25,
' || 'OPI_MEASURE26
' || 'FROM
' || '(SELECT
' || l_view_by_fact_col || ',
' || opi_dbi_rpt_util_pkg.nvl_str (p_str => 'c_tot_entries')
|| ' OPI_MEASURE1,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_hits')
|| ' OPI_MEASURE3,
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'p_hits',
p_denominator => 'p_tot_entries',
p_measure_name => 'OPI_MEASURE4') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'c_hits',
p_denominator => 'c_tot_entries',
p_measure_name => 'OPI_MEASURE5') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_hits',
p_new_denominator => 'c_tot_entries',
p_old_numerator => 'p_hits',
p_old_denominator => 'p_tot_entries',
p_measure_name => 'OPI_MEASURE6') || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_misses')
|| ' OPI_MEASURE8,
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'p_misses',
p_denominator => 'p_tot_entries',
p_measure_name => 'OPI_MEASURE9') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'c_misses',
p_denominator => 'c_tot_entries',
p_measure_name => 'OPI_MEASURE10') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_misses',
p_new_denominator => 'c_tot_entries',
p_old_numerator => 'p_misses',
p_old_denominator => 'p_tot_entries',
p_measure_name => 'OPI_MEASURE11') || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_exact_matches')
|| ' OPI_MEASURE13,
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'p_exact_matches',
p_denominator => 'p_tot_entries',
p_measure_name => 'OPI_MEASURE14') || ',
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'c_exact_matches',
p_denominator => 'c_tot_entries',
p_measure_name => 'OPI_MEASURE15') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_exact_matches',
p_new_denominator => 'c_tot_entries',
p_old_numerator => 'p_exact_matches',
p_old_denominator => 'p_tot_entries',
p_measure_name => 'OPI_MEASURE16') || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_tot_entries_total')
|| ' OPI_MEASURE17,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_hits_total')
|| ' OPI_MEASURE18,
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'c_hits_total',
p_denominator => 'c_tot_entries_total',
p_measure_name => 'OPI_MEASURE19') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_hits_total',
p_new_denominator => 'c_tot_entries_total',
p_old_numerator => 'p_hits_total',
p_old_denominator => 'p_tot_entries_total',
p_measure_name => 'OPI_MEASURE20') || ',
' || opi_dbi_rpt_util_pkg.nvl_str (p_str => 'c_misses_total')
|| ' OPI_MEASURE21,
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'c_misses_total',
p_denominator => 'c_tot_entries_total',
p_measure_name => 'OPI_MEASURE22') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_misses_total',
p_new_denominator => 'c_tot_entries_total',
p_old_numerator => 'p_misses_total',
p_old_denominator => 'p_tot_entries_total',
p_measure_name => 'OPI_MEASURE23') || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_exact_matches_total')
|| ' OPI_MEASURE24,
' || opi_dbi_rpt_util_pkg.percent_str (
p_numerator =>'c_exact_matches_total',
p_denominator => 'c_tot_entries_total',
p_measure_name => 'OPI_MEASURE25') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_exact_matches_total',
p_new_denominator => 'c_tot_entries_total',
p_old_numerator => 'p_exact_matches_total',
p_old_denominator => 'p_tot_entries_total',
p_measure_name => 'OPI_MEASURE26') ;
Description: builds the outer select clause for
Cycle Count Adjustment Summary Report
---------------------------------------------------*/
FUNCTION get_adj_rpt_sel_clause(p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2
IS
l_sel_clause VARCHAR2(7500);
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_item || ' OPI_ATTRIBUTE2,
' || l_uom || ' OPI_ATTRIBUTE3,
' || 'oset.OPI_MEASURE2,
' || 'oset.OPI_MEASURE3,
' || 'oset.OPI_MEASURE5,
' || 'oset.OPI_MEASURE6,
' || 'oset.OPI_MEASURE8,
' || 'oset.OPI_MEASURE9,
' || 'oset.OPI_MEASURE10,
' || 'oset.OPI_MEASURE11,
' || 'oset.OPI_MEASURE12,
' || '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_MEASURE24,
' || 'oset.OPI_MEASURE25,
' || 'oset.OPI_MEASURE26,
' || 'oset.OPI_MEASURE27
' || 'FROM
' || '(SELECT (rank () over
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE2,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE5,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE10,
' || 'OPI_MEASURE11,
' || 'OPI_MEASURE12,
' || 'OPI_MEASURE14,
' || 'OPI_MEASURE15,
' || 'OPI_MEASURE16,
' || 'OPI_MEASURE17,
' || 'OPI_MEASURE18,
' || 'OPI_MEASURE19,
' || 'OPI_MEASURE20,
' || 'OPI_MEASURE21,
' || 'OPI_MEASURE22,
' || 'OPI_MEASURE23,
' || 'OPI_MEASURE24,
' || 'OPI_MEASURE25,
' || 'OPI_MEASURE26,
' || 'OPI_MEASURE27
' || 'FROM
' || '(SELECT
' || l_view_by_fact_col || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_tot_entries',
p_default_val => 0)
|| ' OPI_MEASURE2,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_adjustments',
p_default_val => 0)
|| ' OPI_MEASURE3,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_system_qty',
p_default_val => 0)
|| ' OPI_MEASURE5,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_system_val',
p_default_val => 0)
|| ' OPI_MEASURE6,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_gross_adj_qty',
p_default_val => 0)
|| ' OPI_MEASURE8,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_gross_adj_val',
p_default_val => 0)
|| ' OPI_MEASURE9,
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator => 'p_gross_adj_val',
p_denominator => 'p_system_val',
p_measure_name => 'OPI_MEASURE10') || ',
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator => 'c_gross_adj_val',
p_denominator => 'c_system_val',
p_measure_name => 'OPI_MEASURE11') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_gross_adj_val',
p_new_denominator => 'c_system_val',
p_old_numerator => 'p_gross_adj_val',
p_old_denominator => 'p_system_val',
p_measure_name => 'OPI_MEASURE12') || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_net_adj_qty',
p_default_val => 0)
|| ' OPI_MEASURE14,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_net_adj_val',
p_default_val => 0)
|| ' OPI_MEASURE15,
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator => 'p_net_adj_val',
p_denominator => 'p_system_val',
p_measure_name => 'OPI_MEASURE16') || ',
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator => 'c_net_adj_val',
p_denominator => 'c_system_val',
p_measure_name => 'OPI_MEASURE17') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_net_adj_val',
p_new_denominator => 'c_system_val',
p_old_numerator => 'p_net_adj_val',
p_old_denominator => 'p_system_val',
p_measure_name => 'OPI_MEASURE18') || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_tot_entries_total',
p_default_val => 0)
|| ' OPI_MEASURE19,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_adjustments_total',
p_default_val => 0)
|| ' OPI_MEASURE20,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_system_val_total',
p_default_val => 0)
|| ' OPI_MEASURE21,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_gross_adj_val_total',
p_default_val => 0)
|| ' OPI_MEASURE22,
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator =>'c_gross_adj_val_total',
p_denominator =>'c_system_val_total',
p_measure_name => 'OPI_MEASURE23') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_gross_adj_val_total',
p_new_denominator => 'c_system_val_total',
p_old_numerator => 'p_gross_adj_val_total',
p_old_denominator => 'p_system_val_total',
p_measure_name => 'OPI_MEASURE24') || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_net_adj_val_total',
p_default_val => 0)
|| ' OPI_MEASURE25,
' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
p_numerator =>'c_net_adj_val_total',
p_denominator =>'c_system_val_total',
p_measure_name => 'OPI_MEASURE26') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str (
p_new_numerator => 'c_net_adj_val_total',
p_new_denominator => 'c_system_val_total',
p_old_numerator => 'p_net_adj_val_total',
p_old_denominator => 'p_system_val_total',
p_measure_name => 'OPI_MEASURE27');
Description: builds the outer select clause for
Cycle Count Adjustment Summary Report
---------------------------------------------------*/
FUNCTION get_adj_dtl_sel_clause(p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2
IS
l_sel_clause VARCHAR2(7500);
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_item || ' OPI_ATTRIBUTE2,
' || l_uom || ' OPI_ATTRIBUTE3,
' || 'oset.OPI_MEASURE1,
' || 'oset.OPI_MEASURE3,
' || 'oset.OPI_MEASURE4,
' || 'oset.OPI_MEASURE6,
' || 'oset.OPI_MEASURE7,
' || 'oset.OPI_MEASURE9,
' || 'oset.OPI_MEASURE10,
' || 'oset.OPI_MEASURE12,
' || 'oset.OPI_MEASURE13,
' || 'oset.OPI_MEASURE14,
' || 'oset.OPI_MEASURE15,
' || 'oset.OPI_MEASURE16,
' || 'oset.OPI_MEASURE17,
' || 'oset.OPI_MEASURE18,
' || 'oset.OPI_MEASURE19
' || 'FROM
' || '(SELECT (rank () over
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE1,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE7,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE10,
' || 'OPI_MEASURE12,
' || 'OPI_MEASURE13,
' || 'OPI_MEASURE14,
' || 'OPI_MEASURE15,
' || 'OPI_MEASURE16,
' || 'OPI_MEASURE17,
' || 'OPI_MEASURE18,
' || 'OPI_MEASURE19
' || 'FROM
' || '(SELECT
' || l_view_by_fact_col || ',
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_tot_entries',
p_default_val => 0)
|| ' OPI_MEASURE1,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_system_qty',
p_default_val => 0)
|| ' OPI_MEASURE3,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_system_val',
p_default_val => 0)
|| ' OPI_MEASURE4,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_positive_adj_qty',
p_default_val => 0)
|| ' OPI_MEASURE6,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_positive_adj_val',
p_default_val => 0)
|| ' OPI_MEASURE7,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_negative_adj_qty',
p_default_val => 0)
|| ' OPI_MEASURE9,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_negative_adj_val',
p_default_val => 0)
|| ' OPI_MEASURE10,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_gross_adj_val',
p_default_val => 0)
|| ' OPI_MEASURE12,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_net_adj_val',
p_default_val => 0)
|| ' OPI_MEASURE13,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_tot_entries_total',
p_default_val => 0)
|| ' OPI_MEASURE14,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_system_val_total',
p_default_val => 0)
|| ' OPI_MEASURE15,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_positive_adj_val_total',
p_default_val => 0)
|| ' OPI_MEASURE16,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_negative_adj_val_total',
p_default_val => 0)
|| ' OPI_MEASURE17,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_gross_adj_val_total',
p_default_val => 0)
|| ' OPI_MEASURE18,
' || opi_dbi_rpt_util_pkg.nvl_str (
p_str => 'c_net_adj_val_total',
p_default_val => 0)
|| ' OPI_MEASURE19';