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