[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_FR_COST_PER_D_PKG
Source
1 PACKAGE BODY ISC_DBI_FR_COST_PER_D_PKG AS
2 /*$Header: ISCRGC1B.pls 120.1 2006/06/14 12:17:44 abhdixi noship $
3 /*----------------------------------------------------
4 Declare PRIVATE procedures and functions for package
5 -----------------------------------------------------*/
6 FUNCTION get_status_filter_where(p_view_by IN VARCHAR2)
7 RETURN VARCHAR2;
8
9
10 /* Rated Freight Cost per Unit Distance Report */
11 FUNCTION get_rpt_sel_clause (p_view_by_dim IN VARCHAR2,
12 p_join_tbl IN
13 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
14 RETURN VARCHAR2;
15
16
17 /*------------------------------------------------
18 Rated Freight Cost per Unit Distance Report Function
19 -------------------------------------------------*/
20 PROCEDURE get_tbl_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
21 x_custom_sql OUT NOCOPY VARCHAR2,
22 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
23 IS
24 l_query VARCHAR2(32767);
25 l_view_by VARCHAR2(120);
26 l_view_by_col VARCHAR2 (120);
27 l_xtd VARCHAR2(10);
28 l_comparison_type VARCHAR2(1);
29 l_cur_suffix VARCHAR2(10);
30 l_currency VARCHAR2(10);
31 l_custom_sql VARCHAR2 (10000);
32
33 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
34 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
35 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
36
37 l_where_clause VARCHAR2 (2000);
38 l_filter_where VARCHAR2 (240);
39 l_mv VARCHAR2 (30);
40
41 l_aggregation_level_flag VARCHAR2(10);
42
43 l_custom_rec BIS_QUERY_ATTRIBUTES;
44
45 l_mode_val VARCHAR2 (120);
46
47 BEGIN
48
49 -- initialization block
50 l_comparison_type := 'Y';
51 l_aggregation_level_flag := '0';
52
53 -- clear out the column and Join info tables.
54 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
55 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
56
57 -- get all the query parameters
58 isc_dbi_sutil_pkg.process_parameters (
59 p_param => p_param,
60 p_view_by => l_view_by,
61 p_view_by_col_name => l_view_by_col,
62 p_comparison_type => l_comparison_type,
63 p_xtd => l_xtd,
64 p_cur_suffix => l_cur_suffix,
65 p_where_clause => l_where_clause,
66 p_mv => l_mv,
67 p_join_tbl => l_join_tbl,
68 p_mv_level_flag => l_aggregation_level_flag,
69 p_trend => 'N',
70 p_func_area => 'ISC',
71 p_version => '7.1',
72 p_role => '',
73 p_mv_set => 'C11',
74 p_mv_flag_type => 'FLAG2',
75 p_in_join_tbl => l_in_join_tbl);
76
77 --Convert the currency suffix to conform to ISC standards
78 IF (l_cur_suffix = 'g')
79 THEN l_currency := 'g';
80 ELSIF (l_cur_suffix = 'sg')
81 THEN l_currency := 'g1';
82 ELSE l_currency := 'g';
83 END IF;
84
85 -- Add measure columns that need to be aggregated
86 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
87 p_col_name => 'freight_cost_'||l_currency,
88 p_alias_name => 'freight_cost',
89 p_grand_total => 'Y',
90 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
91 p_to_date_type => 'XTD');
92
93 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
94 p_col_name => 'freight_distance_g',
95 p_alias_name => 'freight_distance',
96 p_grand_total => 'Y',
97 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
98 p_to_date_type => 'XTD');
99
100 /* Additional filter needed to avoid displaying records queried due to total values at node */
101
102 -- l_filter_where := ' ISC_MEASURE_4 IS NOT NULL AND ISC_MEASURE_1 IS NOT NULL';
103
104 -- bug fix 5230691
105
106 FOR i IN 1..p_param.COUNT
107 LOOP
108 IF(p_param(i).parameter_name = 'ISC_TRANSPORTATION_MODE+ISC_TRANSPORTATION_MODE')
109 THEN l_mode_val := p_param(i).parameter_value;
110 END IF;
111 END LOOP;
112
113 IF( l_mode_val = 'All')
114 THEN
115 l_where_clause := l_where_clause || ' AND fact.mode_of_transport IN (''TRUCK'') ';
116 END IF;
117
118 -- construct the query
119 l_query := get_rpt_sel_clause (l_view_by, l_join_tbl)
120 || ' from
121 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
122 p_where_clause => l_where_clause,
123 p_join_tables => l_join_tbl,
124 p_use_windowing => 'Y',
125 p_col_name => l_col_tbl,
126 p_use_grpid => 'N',
127 p_paren_count => 3,
128 p_filter_where => get_status_filter_where(l_view_by),
129 p_generate_viewby => 'Y',
130 p_in_join_tables => NULL);
131
132
133 -- prepare output for bind variables
134 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
135 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
136
137 -- set the basic bind variables for the status SQL
138 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
139
140 -- Passing ISC_AGG_FLAG to PMV
141 l_custom_rec.attribute_name := ':ISC_AGG_FLAG';
142 l_custom_rec.attribute_value := l_aggregation_level_flag;
143 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
144 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
145 x_custom_output.extend;
146 x_custom_output(x_custom_output.count) := l_custom_rec;
147
148 x_custom_sql := l_query;
149
150 END get_tbl_sql;
151
152
153 /*------------------------------------------------------------------------------------
154 Function: get_status_filter_where
155 Description: creates additional where clause to filter rows without meaningful data
156 -------------------------------------------------------------------------------------*/
157 FUNCTION get_status_filter_where(p_view_by in VARCHAR2) return VARCHAR2
158 IS
159 l_col_tbl poa_dbi_util_pkg.poa_dbi_filter_tbl;
160 BEGIN
161 l_col_tbl := poa_dbi_util_pkg.POA_DBI_FILTER_TBL();
162 l_col_tbl.extend;
163 l_col_tbl(1) := 'ISC_MEASURE_10';
164 l_col_tbl.extend;
165 l_col_tbl(2) := 'ISC_MEASURE_4';
166
167 return poa_dbi_util_pkg.get_filter_where(l_col_tbl);
168
169 END;
170
171
172 /*--------------------------------------------------
173 Function: get_rtp_sel_clause
174 Description: builds the outer select clause
175 ---------------------------------------------------*/
176 FUNCTION get_rpt_sel_clause(p_view_by_dim IN VARCHAR2,
177 p_join_tbl IN
178 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
179 RETURN VARCHAR2
180 IS
181 l_sel_clause VARCHAR2(32000);
182 l_view_by_col_name VARCHAR2(60);
183 l_description VARCHAR2(30);
184 l_drill_across_rep_1 VARCHAR2(50);
185 l_drill_across_rep_2 VARCHAR2(50);
186 l_view_by_fact_col VARCHAR2(400);
187 l_drill_across VARCHAR2(1000);
188
189 BEGIN
190
191 -- initialization block
192
193 -- Column to get view by column name
194 l_view_by_col_name := isc_dbi_sutil_pkg.get_view_by_col_name (p_view_by_dim);
195
196 -- fact column view by's
197 l_view_by_fact_col := isc_dbi_sutil_pkg.get_fact_select_columns (p_join_tbl);
198
199 -- Outer select clause
200 l_sel_clause :=
201 'SELECT
202 ' || isc_dbi_sutil_pkg.get_view_by_select_clause (p_view_by_dim)
203 || 'oset.ISC_MEASURE_9 ISC_MEASURE_9,
204 ' || 'oset.ISC_MEASURE_1 ISC_MEASURE_1,
205 ' || 'oset.ISC_MEASURE_12 ISC_MEASURE_12,
206 ' || 'oset.ISC_MEASURE_2 ISC_MEASURE_2,
207 ' || 'oset.ISC_MEASURE_13 ISC_MEASURE_13,
208 ' || 'oset.ISC_MEASURE_3 ISC_MEASURE_3,
209 ' || 'oset.ISC_MEASURE_14 ISC_MEASURE_14,
210 ' || 'oset.ISC_MEASURE_10 ISC_MEASURE_10,
211 ' || 'oset.ISC_MEASURE_4 ISC_MEASURE_4,
212 ' || 'oset.ISC_MEASURE_15 ISC_MEASURE_15,
213 ' || 'oset.ISC_MEASURE_5 ISC_MEASURE_5,
214 ' || 'oset.ISC_MEASURE_16 ISC_MEASURE_16,
215 ' || 'oset.ISC_MEASURE_6 ISC_MEASURE_6,
216 ' || 'oset.ISC_MEASURE_17 ISC_MEASURE_17,
217 ' || 'oset.ISC_MEASURE_11 ISC_MEASURE_11,
218 ' || 'oset.ISC_MEASURE_7 ISC_MEASURE_7,
219 ' || 'oset.ISC_MEASURE_18 ISC_MEASURE_18,
220 ' || 'oset.ISC_MEASURE_8 ISC_MEASURE_8,
221 ' || 'oset.ISC_MEASURE_19 ISC_MEASURE_19
222 ' || 'FROM
223 ' || '(SELECT (rank () over
224 ' || ' (&ORDER_BY_CLAUSE nulls last,
225 ' || l_view_by_fact_col || ')) - 1 rnk,
226 ' || l_view_by_fact_col || ',
227 ' || 'ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
228 ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,
229 ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
230 ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19
231 ' || 'FROM
232 ' || '(SELECT
233 ' || l_view_by_fact_col || ',
234 nvl(c_freight_cost,0) ISC_MEASURE_1,
235 nvl(p_freight_cost,0) ISC_MEASURE_9,
236 nvl(c_freight_cost_total,0) ISC_MEASURE_12,
237 nvl(p_freight_distance,0) ISC_MEASURE_10,
238 nvl(c_freight_distance,0) ISC_MEASURE_4,
239 nvl(c_freight_distance_total,0) ISC_MEASURE_15,
240 ' || isc_dbi_sutil_pkg.change_str (
241 p_new_numerator => 'c_freight_cost',
242 p_old_numerator => 'p_freight_cost',
243 p_denominator => 'p_freight_cost',
244 p_measure_name => 'ISC_MEASURE_2') || ', -- Rated Fr Cost Change
245 ' || isc_dbi_sutil_pkg.change_str (
246 p_new_numerator => 'c_freight_cost_total',
247 p_old_numerator => 'p_freight_cost_total',
248 p_denominator => 'p_freight_cost_total',
249 p_measure_name => 'ISC_MEASURE_13') || ', -- Grand Total Rated Fr Cost Change
250 ' || isc_dbi_sutil_pkg.rate_str (
251 p_numerator => 'c_freight_cost',
252 p_denominator => 'c_freight_cost_total',
253 p_rate_type => 'PERCENT',
254 p_measure_name => 'ISC_MEASURE_3') || ', -- Percent of Total Fr Cost
255 ' || isc_dbi_sutil_pkg.rate_str (
256 p_numerator => 'c_freight_cost_total',
257 p_denominator => 'c_freight_cost_total',
258 p_rate_type => 'PERCENT',
259 p_measure_name => 'ISC_MEASURE_14') || ', -- Grand total Percent of Total Fr Cost
260 ' || isc_dbi_sutil_pkg.change_str (
261 p_new_numerator => 'c_freight_distance',
262 p_old_numerator => 'p_freight_distance',
263 p_denominator => 'p_freight_distance',
264 p_measure_name => 'ISC_MEASURE_5') || ', -- Rated Fr Distance Change
265 ' || isc_dbi_sutil_pkg.change_str (
266 p_new_numerator => 'c_freight_distance_total',
267 p_old_numerator => 'p_freight_distance_total',
268 p_denominator => 'p_freight_distance_total',
269 p_measure_name => 'ISC_MEASURE_16') || ', -- Grand Total Rated Fr Distance Change
270 ' || isc_dbi_sutil_pkg.rate_str (
274 p_measure_name => 'ISC_MEASURE_6') || ', -- Percent of Total Fr Distance
271 p_numerator => 'c_freight_distance',
272 p_denominator => 'c_freight_distance_total',
273 p_rate_type => 'PERCENT',
275 ' || isc_dbi_sutil_pkg.rate_str (
276 p_numerator => 'c_freight_cost_total',
277 p_denominator => 'c_freight_cost_total',
278 p_rate_type => 'PERCENT',
279 p_measure_name => 'ISC_MEASURE_17') || ', -- Grand total Percent of total Fr Cost
280 ' || isc_dbi_sutil_pkg.rate_str (
281 p_numerator => 'p_freight_cost',
282 p_denominator => 'p_freight_distance',
283 p_rate_type => 'RATIO',
284 p_measure_name => 'ISC_MEASURE_11') || ', -- Fr Cost per Distance Prior
285 ' || isc_dbi_sutil_pkg.rate_str (
286 p_numerator => 'c_freight_cost',
287 p_denominator => 'c_freight_distance',
288 p_rate_type => 'RATIO',
289 p_measure_name => 'ISC_MEASURE_7') || ', -- Fr Cost per Distance
290 ' || isc_dbi_sutil_pkg.rate_str (
291 p_numerator => 'c_freight_cost_total',
292 p_denominator => 'c_freight_distance_total',
293 p_rate_type => 'RATIO',
294 p_measure_name => 'ISC_MEASURE_18') || ', -- Grand Total Fr Cost per Distance
295 ' || isc_dbi_sutil_pkg.change_rate_str (
296 p_new_numerator => 'c_freight_cost',
297 p_new_denominator => 'c_freight_distance',
298 p_old_numerator => 'p_freight_cost',
299 p_old_denominator => 'p_freight_distance',
300 p_rate_type => 'RATIO',
301 p_measure_name => 'ISC_MEASURE_8') || ', -- Fr Cost per Distance Change
302 ' || isc_dbi_sutil_pkg.change_rate_str (
303 p_new_numerator => 'c_freight_cost_total',
304 p_new_denominator => 'c_freight_distance_total',
305 p_old_numerator => 'p_freight_cost_total',
306 p_old_denominator => 'p_freight_distance_total',
307 p_rate_type => 'RATIO',
308 p_measure_name => 'ISC_MEASURE_19'); -- Grand Total Fr Cost per Distance Change
309
310 RETURN l_sel_clause;
311
312 END get_rpt_sel_clause;
313
314
315 END ISC_DBI_FR_COST_PER_D_PKG;