[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_MTL_VAR_RPT_PKG
Source
1 PACKAGE BODY opi_dbi_mtl_var_rpt_pkg
2 /*$Header: OPIDMUVRPTB.pls 120.0 2005/05/24 19:20:46 appldev noship $ */
3 as
4
5 Function get_description(p_dim_name varchar2) return varchar2;
6 FUNCTION get_trend_sel_clause return VARCHAR2;
7 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2, p_period_type in VARCHAR2, p_org in VARCHAR2) return VARCHAR2;
8
9 PROCEDURE mtl_status_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
10 x_custom_sql OUT NOCOPY VARCHAR2,
11 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
12 IS
13 l_query VARCHAR2(15000);
14 l_view_by VARCHAR2(120);
15 l_view_by_col VARCHAR2 (120);
16 l_as_of_date DATE;
17 l_prev_as_of_date DATE;
18 l_xtd VARCHAR2(10);
19 l_comparison_type VARCHAR2(1) := 'Y';
20 l_nested_pattern NUMBER;
21 l_cur_suffix VARCHAR2(2);
22 l_custom_sql VARCHAR2 (10000);
23 l_period_type VARCHAR2(255) := NULL;
24 l_org VARCHAR2(255) := NULL;
25
26 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
27 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
28
29 l_where_clause VARCHAR2 (2000);
30 l_mv VARCHAR2 (30);
31
32 l_item_cat_flag varchar2(1) := '0';
33
34 l_custom_rec BIS_QUERY_ATTRIBUTES;
35
36 BEGIN
37
38 -- clear out the tables.
39 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
40 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
41
42
43 FOR i IN 1..p_param.COUNT
44 LOOP
45 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
46 THEN l_period_type := p_param(i).parameter_value;
47 END IF;
48 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
49 THEN l_org := p_param(i).parameter_id;
50 END IF;
51 END LOOP;
52
53 -- get all the query parameters
54 opi_dbi_rpt_util_pkg.process_parameters (p_param,
55 l_view_by,
56 l_view_by_col,
57 l_comparison_type,
58 l_xtd,
59 l_cur_suffix,
60 l_where_clause,
61 l_mv,
62 l_join_tbl,
63 l_item_cat_flag,
64 'N',
65 'OPI',
66 '6.0',
67 '',
68 'MUV',
69 'ITEM_CAT');
70
71
72
73 -- The measure columns that need to be aggregated are
74 -- Std_value_<b/g>, Act_value_<b/g>
75
76 poa_dbi_util_pkg.add_column (l_col_tbl,
77 'Std_value_' || l_cur_suffix,
78 'Std_value');
79
80 poa_dbi_util_pkg.add_column (l_col_tbl,
81 'Act_value_' || l_cur_suffix,
82 'Act_value');
83
84
85 -- construct the query
86 l_query := get_status_sel_clause (l_view_by, l_period_type, l_org)
87 || ' from ((
88 ' || poa_dbi_template_pkg.status_sql (l_mv,
89 l_where_clause,
90 l_join_tbl,
91 'N',
92 l_col_tbl,
93 'N');
94
95 -- prepare output for bind variables
96 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
97 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
98
99 -- set the basic bind variables for the status SQL
100 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
101
102 -- Passing OPI_ITEM_CAT_FLAG to PMV
103 l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
104 l_custom_rec.attribute_value := l_item_cat_flag;
105 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
106 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
107 x_custom_output.extend;
108 x_custom_output(x_custom_output.count) := l_custom_rec;
109
110 commit;
111
112 x_custom_sql := l_query;
113
114 END mtl_status_sql;
115
116
117 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2, p_period_type in VARCHAR2, p_org in VARCHAR2) return VARCHAR2
118 IS
119
120 l_sel_clause varchar2(4000);
121 l_view_by_col_name varchar2(60);
122 l_description varchar2(30);
123
124 BEGIN
125
126 /* Main Outer query */
127
128 -- Column to get view by column name
129 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name(p_view_by_dim);
130
131 l_description := get_description(p_view_by_dim);
132
133 l_sel_clause :=
134 'SELECT
135 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
136 || l_view_by_col_name || ' OPI_ATTRIBUTE1,
137 '|| l_description || ' OPI_ATTRIBUTE2,';
138
139 IF ((p_view_by_dim = 'ITEM+ENI_ITEM_ORG') AND (p_period_type = 'FII_TIME_WEEK' OR p_period_type =
140 'FII_TIME_ENT_PERIOD') AND (UPPER(p_org)<>'ALL')) THEN
141 l_sel_clause := l_sel_clause || ' ''pFunctionName=OPI_DBI_MTL_USAGE_JOB_DTL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'' OPI_ATTRIBUTE3 ,';
142 ELSE
143 l_sel_clause := l_sel_clause || 'NULL OPI_ATTRIBUTE3 ,';
144 END IF;
145
146 l_sel_clause := l_sel_clause ||
147 'oset.C_STD_VALUE OPI_MEASURE1,
148 oset.C_ACT_VALUE OPI_MEASURE2,
149 oset.P_STD_VALUE OPI_MEASURE3,
150 oset.P_ACT_VALUE OPI_MEASURE4,
151 oset.P_ACT_VALUE - oset.P_STD_VALUE OPI_MEASURE19,
152 oset.C_ACT_VALUE - oset.C_STD_VALUE OPI_MEASURE5,
153 CASE WHEN oset.P_ACT_VALUE - oset.P_STD_VALUE = 0 THEN to_number(NULL)
154 ELSE (((oset.C_ACT_VALUE - oset.C_STD_VALUE) - (oset.P_ACT_VALUE - oset.P_STD_VALUE))/ABS(oset.P_ACT_VALUE - oset.P_STD_VALUE))*100 END OPI_MEASURE6,
155 decode(oset.P_STD_VALUE, 0, to_number(null), ((oset.P_ACT_VALUE - oset.P_STD_VALUE)/oset.P_STD_VALUE)*100) OPI_MEASURE20,
156 decode(oset.C_STD_VALUE, 0, to_number(null), ((oset.C_ACT_VALUE - oset.C_STD_VALUE)/oset.C_STD_VALUE)*100) OPI_MEASURE7,
157 CASE WHEN oset.C_STD_VALUE = 0 THEN to_number(NULL)
158 WHEN oset.P_STD_VALUE = 0 THEN to_number(NULL)
159 ELSE ((oset.C_ACT_VALUE - oset.C_STD_VALUE)/oset.C_STD_VALUE)*100 - ((oset.P_ACT_VALUE - oset.P_STD_VALUE)/oset.P_STD_VALUE)*100 END OPI_MEASURE8,
160 oset.C_STD_VALUE_TOTAL OPI_MEASURE9,
161 oset.C_ACT_VALUE_TOTAL OPI_MEASURE10,
162 oset.C_ACT_VALUE_TOTAL - oset.C_STD_VALUE_TOTAL OPI_MEASURE11,
163 CASE WHEN oset.P_ACT_VALUE_TOTAL - oset.P_STD_VALUE_TOTAL = 0 THEN to_number(NULL)
164 ELSE (((oset.C_ACT_VALUE_TOTAL - oset.C_STD_VALUE_TOTAL) - (oset.P_ACT_VALUE_TOTAL - oset.P_STD_VALUE_TOTAL))/ABS(oset.P_ACT_VALUE_TOTAL - oset.P_STD_VALUE_TOTAL))*100 END OPI_MEASURE12,
165 CASE WHEN oset.C_STD_VALUE_TOTAL = 0 THEN to_number(NULL)
166 ELSE (( oset.C_ACT_VALUE_TOTAL - oset.C_STD_VALUE_TOTAL )/ oset.C_STD_VALUE_TOTAL)*100 END OPI_MEASURE13,
167 CASE WHEN oset.C_STD_VALUE_TOTAL = 0 THEN to_number(NULL)
168 WHEN oset.P_STD_VALUE_TOTAL = 0 THEN to_number(NULL)
169 ELSE (( oset.C_ACT_VALUE_TOTAL - oset.C_STD_VALUE_TOTAL)/ oset.C_STD_VALUE_TOTAL)*100 -
170 (( oset.P_ACT_VALUE_TOTAL - oset.P_STD_VALUE_TOTAL )/ oset.P_STD_VALUE_TOTAL )*100 END OPI_MEASURE14,
171 decode(oset.C_STD_VALUE,0, to_number(null), ((oset.C_ACT_VALUE - oset.C_STD_VALUE)/oset.C_STD_VALUE)*100) OPI_MEASURE15,
172 decode(oset.P_STD_VALUE,0, to_number(null), ((oset.P_ACT_VALUE - oset.P_STD_VALUE)/oset.P_STD_VALUE)*100) OPI_MEASURE16,
173 CASE WHEN oset.C_STD_VALUE_TOTAL = 0 THEN to_number(NULL)
174 ELSE (( oset.C_ACT_VALUE_TOTAL - oset.C_STD_VALUE_TOTAL )/ oset.C_STD_VALUE_TOTAL )*100 END OPI_MEASURE17,
175 CASE WHEN oset.P_STD_VALUE_TOTAL = 0 THEN to_number(NULL)
176 ELSE (( oset.P_ACT_VALUE_TOTAL - oset.P_STD_VALUE_TOTAL )/ oset.P_STD_VALUE_TOTAL )*100 END OPI_MEASURE18 ';
177
178 return l_sel_clause;
179
180 END get_status_sel_clause;
181
182
183
184 Function get_description(p_dim_name varchar2) return varchar2
185 is
186 l_description varchar2(30);
187 begin
188
189 l_description := (CASE p_dim_name
190 WHEN 'ITEM+ENI_ITEM_ORG' THEN 'v.description'
191 ELSE 'null'
192 END);
193
194 return l_description;
195
196 end get_description;
197
198
199 PROCEDURE mtl_trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
200 x_custom_sql OUT NOCOPY VARCHAR2,
201 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
202 IS
203 l_query VARCHAR2(15000);
204 l_view_by VARCHAR2(120);
205 l_view_by_col VARCHAR2 (120);
206 l_as_of_date DATE;
207 l_prev_as_of_date DATE;
208 l_xtd varchar2(10);
209 l_comparison_type VARCHAR2(1) := 'Y';
210 l_nested_pattern NUMBER;
211 l_cur_suffix VARCHAR2(2);
212 l_custom_sql VARCHAR2(4000);
213 l_mv VARCHAR2 (30);
214 l_where_clause VARCHAR2 (4000) := '';
215
216 l_item_cat_flag VARCHAR2(1) := '0';
217
218 l_custom_rec BIS_QUERY_ATTRIBUTES;
219
220 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
221 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
222
223
224 BEGIN
225
226 -- clear out the tables.
227 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
228 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
229
230 -- get all the query parameters
231 opi_dbi_rpt_util_pkg.process_parameters (p_param,
232 l_view_by,
233 l_view_by_col,
234 l_comparison_type,
235 l_xtd,
236 l_cur_suffix,
237 l_where_clause,
238 l_mv,
239 l_join_tbl,
240 l_item_cat_flag,
241 'Y',
242 'OPI',
243 '6.0',
244 '',
245 'MUV',
246 'ITEM_CAT');
247
248
249 -- The measure columns that need to be aggregated are
250 -- Std_value_<b/g>, Act_value_<b/g>
251 -- No Grand totals required.
252
253 poa_dbi_util_pkg.add_column (l_col_tbl,
254 'Std_value_' || l_cur_suffix,
255 'Std_value',
256 'N');
257 poa_dbi_util_pkg.add_column (l_col_tbl,
258 'Act_value_' || l_cur_suffix,
259 'Act_value',
260 'N');
261
262
263 -- Joining Outer and Inner Query
264 l_query := get_trend_sel_clause ||
265 ' from ' ||
266 poa_dbi_template_pkg.trend_sql (
267 l_xtd,
268 l_comparison_type,
269 l_mv,
270 l_where_clause,
271 l_col_tbl,
272 'N');
273
274
275 -- Prepare PMV bind variables
276 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
277 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
278
279 -- get all the basic binds used by POA queries
280 -- Do this before adding any of our binds, since the procedure
281 -- reinitializes the output table
282 poa_dbi_util_pkg.get_custom_trend_binds (l_xtd, l_comparison_type,
283 x_custom_output);
284
285 -- put the custom OPI binds in
286 l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
287 l_custom_rec.attribute_value := l_item_cat_flag;
288 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
289 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
290 x_custom_output.extend;
291 x_custom_output(x_custom_output.count) := l_custom_rec;
292
293 commit;
294
295 x_custom_sql := l_query;
296
297 END mtl_trend_sql;
298
299
300 FUNCTION get_trend_sel_clause return VARCHAR2
301 IS
302 l_sel_clause varchar2(5000);
303 BEGIN
304
305 l_sel_clause :=
306 'SELECT cal.name VIEWBY,
307 iset.C_STD_VALUE OPI_MEASURE1,
308 iset.C_ACT_VALUE OPI_MEASURE2,
309 iset.P_STD_VALUE OPI_MEASURE3,
310 iset.P_ACT_VALUE OPI_MEASURE4,
311 iset.P_ACT_VALUE - iset.P_STD_VALUE OPI_MEASURE5,
312 iset.C_ACT_VALUE - iset.C_STD_VALUE OPI_MEASURE6,
313 CASE WHEN iset.P_ACT_VALUE - iset.P_STD_VALUE = 0 THEN to_number(NULL)
314 ELSE (((iset.C_ACT_VALUE - iset.C_STD_VALUE) - (iset.P_ACT_VALUE - iset.P_STD_VALUE))/ABS(iset.P_ACT_VALUE - iset.P_STD_VALUE))*100 END OPI_MEASURE7,
315 decode(iset.P_STD_VALUE, 0, to_number(null), ((iset.P_ACT_VALUE - iset.P_STD_VALUE)/iset.P_STD_VALUE)*100) OPI_MEASURE8,
316 decode(iset.C_STD_VALUE, 0, to_number(null), ((iset.C_ACT_VALUE - iset.C_STD_VALUE)/iset.C_STD_VALUE)*100) OPI_MEASURE9,
317 CASE WHEN iset.C_STD_VALUE = 0 THEN to_number(NULL)
318 WHEN iset.P_STD_VALUE = 0 THEN to_number(NULL)
319 ELSE ((iset.C_ACT_VALUE - iset.C_STD_VALUE)/iset.C_STD_VALUE)*100 - ((iset.P_ACT_VALUE - iset.P_STD_VALUE)/iset.P_STD_VALUE)*100 END OPI_MEASURE10 ';
320
321 return l_sel_clause;
322
323 END get_trend_sel_clause;
324
325
326 end opi_dbi_mtl_var_rpt_pkg;