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