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