[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_CST_REC_RT_TR_PKG
Source
1 PACKAGE BODY ISC_DBI_CST_REC_RT_TR_PKG AS
2 /*$Header: ISCRGBUB.pls 120.1 2006/06/26 06:55:56 abhdixi noship $
3 /*----------------------------------------------------
4 Declare PRIVATE procedures and functions for package
5 -----------------------------------------------------*/
6 /* Freight Cost Recovery Rate Trend */
7 FUNCTION get_trd_sel_clause(p_view_by_dim IN VARCHAR2)
8 RETURN VARCHAR2;
9
10
11 /*------------------------------------------------
12 Freight Cost Recovery Rate 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 BEGIN
40
41 -- initialization block
42 l_comparison_type := 'Y';
43 l_aggregation_level_flag := '0';
44
45 -- clear out the tables.
46 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
47 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
48 x_custom_sql := l_query;
49
50 -- get all the query parameters
51 isc_dbi_sutil_pkg.process_parameters (
52 p_param => p_param,
53 p_view_by => l_view_by,
54 p_view_by_col_name => l_view_by_col,
55 p_comparison_type => l_comparison_type,
56 p_xtd => l_xtd,
57 p_cur_suffix => l_cur_suffix,
58 p_where_clause => l_where_clause,
59 p_mv => l_mv,
60 p_join_tbl => l_join_tbl,
61 p_mv_level_flag => l_aggregation_level_flag,
62 p_trend => 'Y',
63 p_func_area => 'ISC',
64 p_version => '7.1',
65 p_role => '',
66 p_mv_set => 'BT1',
67 p_mv_flag_type => 'FLAG3',
68 p_in_join_tbl => l_in_join_tbl);
69
70 --Convert the currency suffix to conform to ISC standards
71 IF (l_cur_suffix = 'g')
72 THEN l_currency := 'g';
73 ELSIF (l_cur_suffix = 'sg')
74 THEN l_currency := 'g1';
75 ELSE l_currency := 'f';
76 END IF;
77
78 -- Add measure columns that need to be aggregated
79 -- No Grand totals required.
80 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
81 p_col_name => 'freight_cost_amt_'||l_currency,
82 p_alias_name => 'freight_cost',
83 p_grand_total => 'N',
84 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
85 p_to_date_type => 'XTD');
86
87 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
88 p_col_name => 'freight_charge_amt_'||l_currency,
89 p_alias_name => 'freight_charge',
90 p_grand_total => 'N',
91 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
92 p_to_date_type => 'XTD');
93
94 -- Merge Outer and Inner Query
95 l_query := get_trd_sel_clause(l_view_by) ||
96 ' from ' ||
97 poa_dbi_template_pkg.trend_sql (
98 p_xtd => l_xtd,
99 p_comparison_type => l_comparison_type,
100 p_fact_name => l_mv,
101 p_where_clause => l_where_clause,
102 p_col_name => l_col_tbl,
103 p_use_grpid => 'N',
104 p_in_join_tables => l_in_join_tbl);
105
106
107 -- Prepare PMV bind variables
108 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
109 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
110
111 -- get all the basic binds used by POA queries
112 -- Do this before adding any of our binds, since the procedure
113 -- reinitializes the output table
114 poa_dbi_util_pkg.get_custom_trend_binds (
115 p_xtd => l_xtd,
116 p_comparison_type => l_comparison_type,
117 x_custom_output => x_custom_output);
118
119 -- Passing ISC_AGGREGATION_LEVEL_FLAG to PMV
120 l_custom_rec.attribute_name := ':ISC_AGG_FLAG';
121 l_custom_rec.attribute_value := l_aggregation_level_flag;
122 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
123 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
124 x_custom_output.extend;
125 x_custom_output(x_custom_output.count) := l_custom_rec;
126
127 x_custom_sql := l_query;
128
129 END get_trd_sql;
130
131 /*--------------------------------------------------
132 Function: get_trd_sel_clause
133 Description: builds the outer select clause
134 ---------------------------------------------------*/
135
136 FUNCTION get_trd_sel_clause (p_view_by_dim IN VARCHAR2)
137 RETURN VARCHAR2
138 IS
139 l_sel_clause varchar2(7500);
140 BEGIN
141
142 -- Main Outer query
143
144 l_sel_clause :=
145 'SELECT
146 ' || ' cal.name VIEWBY,
147 nvl(p_freight_cost,0) ISC_MEASURE_11,
148 nvl(c_freight_cost,0) ISC_MEASURE_2,
149 ' || isc_dbi_sutil_pkg.change_str (
150 p_new_numerator => 'c_freight_cost',
151 p_old_numerator => 'p_freight_cost',
152 p_denominator => 'p_freight_cost',
153 p_measure_name => 'ISC_MEASURE_3') || ', -- Estimated Fr Cost Change
154 nvl(p_freight_charge,0) ISC_MEASURE_4,
155 nvl(c_freight_charge,0) ISC_MEASURE_5,
156 ' || isc_dbi_sutil_pkg.change_str (
157 p_new_numerator => 'c_freight_charge',
158 p_old_numerator => 'p_freight_charge',
159 p_denominator => 'p_freight_charge',
160 p_measure_name => 'ISC_MEASURE_6') || ', -- Order Fr Charge Change
161 ' || isc_dbi_sutil_pkg.rate_str (
162 p_numerator => 'p_freight_charge',
163 p_denominator => 'p_freight_cost',
164 p_rate_type => 'PERCENT',
165 p_measure_name => 'ISC_MEASURE_8') || ', -- Prior Recovery Rate
166 ' || isc_dbi_sutil_pkg.rate_str (
167 p_numerator => 'c_freight_charge',
168 p_denominator => 'c_freight_cost',
169 p_rate_type => 'PERCENT',
170 p_measure_name => 'ISC_MEASURE_9') || ', -- Recovery Rate
171 ' || isc_dbi_sutil_pkg.change_rate_str (
172 p_new_numerator => 'c_freight_charge',
173 p_new_denominator => 'c_freight_cost',
174 p_old_numerator => 'p_freight_charge',
175 p_old_denominator => 'p_freight_cost',
176 p_rate_type => 'PERCENT',
177 p_measure_name => 'ISC_MEASURE_10'); -- Recovery Rate Change
178
179 RETURN l_sel_clause;
180
181 END get_trd_sel_clause;
182
183 END ISC_DBI_CST_REC_RT_TR_PKG;