[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_FR_COST_PER_V_TR_PKG
Source
1 PACKAGE BODY ISC_DBI_FR_COST_PER_V_TR_PKG AS
2 /*$Header: ISCRGC0B.pls 120.1 2006/06/26 06:41:59 abhdixi noship $
3 /*----------------------------------------------------
4 Declare PRIVATE procedures and functions for package
5 -----------------------------------------------------*/
6
7 /* Trend Report */
8 FUNCTION get_trd_sel_clause(p_view_by_dim IN VARCHAR2)
9 RETURN VARCHAR2;
10
11
12 /*----------------------------------------
13 Trend Report Function
14 ----------------------------------------*/
15 PROCEDURE get_trd_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
16 x_custom_sql OUT NOCOPY VARCHAR2,
17 x_custom_output OUT NOCOPY
18 BIS_QUERY_ATTRIBUTES_TBL)
19 IS
20 l_query VARCHAR2(32767);
21 l_view_by VARCHAR2(120);
22 l_view_by_col VARCHAR2 (120);
23 l_xtd VARCHAR2(10);
24 l_comparison_type VARCHAR2(1);
25 l_cur_suffix VARCHAR2(10);
26 l_currency VARCHAR2(10);
27 l_custom_sql VARCHAR2 (10000);
28
29 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
30 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
31 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
32
33 l_where_clause VARCHAR2 (2000);
34 l_mv VARCHAR2 (30);
35
36 l_aggregation_level_flag VARCHAR2(10);
37
38 l_custom_rec BIS_QUERY_ATTRIBUTES;
39
40 BEGIN
41
42 -- initialization block
43 l_comparison_type := 'Y';
44 l_aggregation_level_flag := '0';
45
46 -- clear out the tables.
47 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
48 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
49 x_custom_sql := l_query;
50
51 -- get all the query parameters
52 isc_dbi_sutil_pkg.process_parameters (
53 p_param => p_param,
54 p_view_by => l_view_by,
55 p_view_by_col_name => l_view_by_col,
56 p_comparison_type => l_comparison_type,
57 p_xtd => l_xtd,
58 p_cur_suffix => l_cur_suffix,
59 p_where_clause => l_where_clause,
60 p_mv => l_mv,
61 p_join_tbl => l_join_tbl,
62 p_mv_level_flag =>l_aggregation_level_flag,
63 p_trend => 'Y',
64 p_func_area => 'ISC',
65 p_version => '7.1',
66 p_role => '',
67 p_mv_set => 'C01',
68 p_mv_flag_type => 'FLAG1',
69 p_in_join_tbl => l_in_join_tbl);
70
71 --Convert the currency suffix to conform to ISC standards
72 IF (l_cur_suffix = 'g')
73 THEN l_currency := 'g';
74 ELSIF (l_cur_suffix = 'sg')
75 THEN l_currency := 'g1';
76 ELSE l_currency := 'f';
77 END IF;
78
79 -- Add measure columns that need to be aggregated
80 -- No Grand totals required.
81 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
82 p_col_name => 'freight_cost_vol_'||l_currency,
83 p_alias_name => 'freight_cost',
84 p_grand_total => 'N',
85 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
86 p_to_date_type => 'XTD');
87
88 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
89 p_col_name => 'freight_volume_g',
90 p_alias_name => 'freight_volume',
91 p_grand_total => 'N',
92 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
93 p_to_date_type => 'XTD');
94
95 -- Merge Outer and Inner Query
96 l_query := get_trd_sel_clause(l_view_by) ||
97 ' from ' ||
98 poa_dbi_template_pkg.trend_sql (
99 p_xtd => l_xtd,
100 p_comparison_type => l_comparison_type,
101 p_fact_name => l_mv,
102 p_where_clause => l_where_clause,
103 p_col_name => l_col_tbl,
104 p_use_grpid => 'N');
105
106
107 -- Prepare PMV bind variables
108 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
109 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
110
111 -- get all the basic binds used by POA queries
112 -- Do this before adding any of our binds, since the procedure
113 -- reinitializes the output table
114 poa_dbi_util_pkg.get_custom_trend_binds (
115 p_xtd => l_xtd,
116 p_comparison_type => l_comparison_type,
117 x_custom_output => x_custom_output);
118
119 -- Passing ISC_AGGREGATION_LEVEL_FLAG to PMV
120 l_custom_rec.attribute_name := ':ISC_AGG_FLAG';
121 l_custom_rec.attribute_value := l_aggregation_level_flag;
122 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
123 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
124 x_custom_output.extend;
125 x_custom_output(x_custom_output.count) := l_custom_rec;
126
127 x_custom_sql := l_query;
128
129 END get_trd_sql;
130
131 /*--------------------------------------------------
132 Function: get_trd_sel_clause
133 Description: builds the outer select clause
134 ---------------------------------------------------*/
135
136 FUNCTION get_trd_sel_clause (p_view_by_dim IN VARCHAR2)
137 RETURN VARCHAR2
138 IS
139 l_sel_clause varchar2(7500);
140 BEGIN
141
142 -- Main Outer query
143
144 l_sel_clause :=
145 'SELECT
146 ' || ' cal.name VIEWBY,
147 nvl(p_freight_cost,0) ISC_MEASURE_9,
148 nvl(c_freight_cost,0) ISC_MEASURE_1,
149 ' || isc_dbi_sutil_pkg.change_str (
150 p_new_numerator => 'c_freight_cost',
151 p_old_numerator => 'p_freight_cost',
152 p_denominator => 'p_freight_cost',
153 p_measure_name => 'ISC_MEASURE_2') || ', -- Rated Fr Cost Change
154 nvl(p_freight_volume,0) ISC_MEASURE_10,
155 nvl(c_freight_volume,0) ISC_MEASURE_4,
156 ' || isc_dbi_sutil_pkg.change_str (
157 p_new_numerator => 'c_freight_volume',
158 p_old_numerator => 'p_freight_volume',
159 p_denominator => 'p_freight_volume',
160 p_measure_name => 'ISC_MEASURE_5') || ', -- Rated Fr Volume Change
161 ' || isc_dbi_sutil_pkg.rate_str (
162 p_numerator => 'p_freight_cost',
163 p_denominator => 'p_freight_volume',
164 p_rate_type => 'RATIO',
165 p_measure_name => 'ISC_MEASURE_11') || ', -- Fr Cost per Volume Prior
166 ' || isc_dbi_sutil_pkg.rate_str (
167 p_numerator => 'c_freight_cost',
168 p_denominator => 'c_freight_volume',
169 p_rate_type => 'RATIO',
170 p_measure_name => 'ISC_MEASURE_7') || ', -- Fr Cost per Volume
171 ' || isc_dbi_sutil_pkg.change_rate_str (
172 p_new_numerator => 'c_freight_cost',
173 p_new_denominator => 'c_freight_volume',
174 p_old_numerator => 'p_freight_cost',
175 p_old_denominator => 'p_freight_volume',
176 p_rate_type => 'RATIO',
177 p_measure_name => 'ISC_MEASURE_8'); -- Fr Cost per Volume Change
178
179 RETURN l_sel_clause;
180
181 END get_trd_sel_clause;
182
183 END ISC_DBI_FR_COST_PER_V_TR_PKG;