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