[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_RES_UTL_PKG
Source
1 PACKAGE BODY opi_dbi_res_utl_pkg AS
2 /*$Header: OPIDRRSUTB.pls 120.0 2005/05/24 18:08:48 appldev noship $ */
3
4
5 /*++++++++++++++++++++++++++++++++++++++++*/
6 /* Function and procedure declarations in this file but not in spec*/
7 /*++++++++++++++++++++++++++++++++++++++++*/
8
9 FUNCTION get_status_sel_clause (p_view_by_dim IN VARCHAR2)
10 RETURN VARCHAR2;
11
12 PROCEDURE get_qty_columns (p_dim_name VARCHAR2,
13 p_description OUT NOCOPY VARCHAR2,
14 p_uom OUT NOCOPY VARCHAR2,
15 p_qty1 OUT NOCOPY VARCHAR2,
16 p_qty2 OUT NOCOPY VARCHAR2,
17 p_qty3 OUT NOCOPY VARCHAR2);
18
19
20 FUNCTION get_trend_sel_clause(p_view_by_dim IN VARCHAR2, p_url IN VARCHAR2)
21 return VARCHAR2;
22
23
24 PROCEDURE get_rpt_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
25 x_custom_sql OUT NOCOPY VARCHAR2,
26 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
27 IS
28 l_query VARCHAR2(15000);
29 l_view_by VARCHAR2(120);
30 l_view_by_col VARCHAR2 (120);
31 l_xtd VARCHAR2(10);
32 l_comparison_type VARCHAR2(1) := 'Y';
33 l_cur_suffix VARCHAR2(2);
34 l_custom_sql VARCHAR2 (10000);
35
36 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
37 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
38
39 l_where_clause VARCHAR2 (2000);
40 l_mv VARCHAR2 (30);
41
42 l_resource_level_flag varchar2(1) := '0';
43
44 l_custom_rec BIS_QUERY_ATTRIBUTES;
45
46 BEGIN
47
48 -- clear out the tables.
49 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
50 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
51
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_resource_level_flag,
64 'N',
65 'OPI',
66 '6.0',
67 '',
68 'RSUT',
69 'RESOURCE_LEVEL');
70
71 -- The measure columns that need to be aggregated are
72 -- avail_val_ <b/g>, actual_val_ <b/g>
73 -- If viewing by Resource, then sum up
74 -- avail_qty, actual_qty
75
76 poa_dbi_util_pkg.add_column (l_col_tbl,
77 'avail_val_' || l_cur_suffix,
78 'avail_val');
79
80 poa_dbi_util_pkg.add_column (l_col_tbl,
81 'actual_val_' || l_cur_suffix,
82 'actual_val');
83
84 -- Quantity columns are only needed for Resource viewby.
85 -- IF (l_view_by = 'RESOURCE+ENI_RESOURCE') THEN
86 poa_dbi_util_pkg.add_column (l_col_tbl,
87 'avail_qty',
88 'avail_qty');
89
90 poa_dbi_util_pkg.add_column (l_col_tbl,
91 'actual_qty',
92 'actual_qty');
93 -- END IF;
94
95 -- construct the query
96
97 l_query := get_status_sel_clause (l_view_by)
98 || ' from ((
99 ' || poa_dbi_template_pkg.status_sql (l_mv,
100 l_where_clause,
101 l_join_tbl,
102 'N',
103 l_col_tbl,
104 'N');
105
106 -- prepare output for bind variables
107 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
108 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
109
110 -- set the basic bind variables for the status SQL
111 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
112
113 -- Passing OPI_RESOURCE_LEVEL_FLAG to PMV
114 l_custom_rec.attribute_name := ':OPI_RESOURCE_LEVEL_FLAG';
115 l_custom_rec.attribute_value := l_resource_level_flag;
116 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
117 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
118 x_custom_output.extend;
119 x_custom_output(x_custom_output.count) := l_custom_rec;
120
121 x_custom_sql := l_query;
122
123 END get_rpt_sql;
124
125
126 FUNCTION get_status_sel_clause(p_view_by_dim IN VARCHAR2)
127 return VARCHAR2
128 IS
129
130 l_sel_clause varchar2(4500);
131 l_view_by_col_name varchar2(60);
132 l_description varchar2(30);
133 l_uom varchar2(30) := '';
134 l_qty1 varchar2(35);
135 l_qty2 varchar2(35);
136 l_qty3 varchar2(200);
137
138 BEGIN
139
140 -- Main Outer query
141
142 -- Column to get view by column name
143 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
144 (p_view_by_dim);
145
146 -- Quantity columns for
147 get_qty_columns (p_view_by_dim, l_description, l_uom, l_qty1,
148 l_qty2, l_qty3);
149
150 -- Outer select clause
151 l_sel_clause :=
152 'SELECT
153 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
154 || l_view_by_col_name || ' OPI_ATTRIBUTE1,
155 NULL OPI_ATTRIBUTE2,
156 ' || l_qty1 || ' OPI_MEASURE1,
157 ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_val')
158 || ' OPI_MEASURE2,
159 ' || l_qty2 || ' OPI_MEASURE3,
160 ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_avail_val')
161 || ' OPI_MEASURE4,
162 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.p_actual_val',
163 'oset.p_avail_val',
164 'OPI_MEASURE5') || ',
165 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.c_actual_val',
166 'oset.c_avail_val',
167 'OPI_MEASURE6') || ',
168 ' || opi_dbi_rpt_util_pkg.change_pct_str ('oset.c_actual_val',
169 'oset.c_avail_val',
170 'oset.p_actual_val',
171 'oset.p_avail_val',
172 'OPI_MEASURE7') || ',
173 ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_val_total')
174 || ' OPI_MEASURE8,
175 ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_avail_val_total')
176 || ' OPI_MEASURE9,
177 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.c_actual_val_total',
178 'oset.c_avail_val_total',
179 'OPI_MEASURE10') || ',
180 ' || opi_dbi_rpt_util_pkg.change_pct_str ('oset.c_actual_val_total',
181 'oset.c_avail_val_total',
182 'oset.p_actual_val_total',
183 'oset.p_avail_val_total',
184 'OPI_MEASURE11') || ',
185 ' || opi_dbi_rpt_util_pkg.nvl_str('oset.c_avail_qty_total')
186 || ' OPI_MEASURE16 ,
187 ' || opi_dbi_rpt_util_pkg.nvl_str('oset.c_actual_qty_total')
188 || ' OPI_MEASURE17 ,
189 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.c_actual_val',
190 'oset.c_avail_val',
191 'OPI_MEASURE12') || ',
192 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.p_actual_val',
193 'oset.p_avail_val',
194 'OPI_MEASURE13') || ',
195 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.c_actual_val_total',
196 'oset.c_avail_val_total',
197 'OPI_MEASURE14') || ',
198 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.p_actual_val_total',
199 'oset.p_avail_val_total',
200 'OPI_MEASURE15'
201 );
202
203 RETURN l_sel_clause;
204
205 END get_status_sel_clause;
206
207
208 PROCEDURE get_qty_columns (p_dim_name VARCHAR2,
209 p_description OUT NOCOPY VARCHAR2,
210 p_uom OUT NOCOPY VARCHAR2,
211 p_qty1 OUT NOCOPY VARCHAR2,
212 p_qty2 OUT NOCOPY VARCHAR2,
213 p_qty3 OUT NOCOPY VARCHAR2)
214 IS
215 l_description varchar2(30);
216 l_uom varchar2(30);
217
218 BEGIN
219 CASE
220 WHEN p_dim_name = 'RESOURCE+ENI_RESOURCE' THEN
221 BEGIN
222 /*
223 p_description := 'v.description';
224 p_uom := 'v2.unit_of_measure';
225 */
226 p_qty1 := opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_qty');
227 p_qty2 := opi_dbi_rpt_util_pkg.nvl_str
228 ('oset.c_avail_qty');
229 p_qty3 := opi_dbi_rpt_util_pkg.percent_str
230 ('oset.c_actual_qty',
231 'oset.c_avail_qty',
232 '');
233 END;
234 ELSE
238 p_uom := 'null';
235 BEGIN
236 /*
237 p_description := 'null';
239 */
240 p_qty1 := 'null';
241 p_qty2 := 'null';
242 p_qty3 := 'null';
243 END;
244 END CASE;
245 END get_qty_columns;
246
247
248 /*
249 Report query for viewby = time
250 */
251
252 PROCEDURE get_trd_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
253 x_custom_sql OUT NOCOPY VARCHAR2,
254 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
255 IS
256 l_query VARCHAR2(15000);
257 l_view_by VARCHAR2(120);
258 l_view_by_col VARCHAR2 (120);
259 l_xtd varchar2(10);
260 l_comparison_type VARCHAR2(1) := 'Y';
261 l_cur_suffix VARCHAR2(2);
262 l_custom_sql VARCHAR2(4000);
263 l_mv VARCHAR2 (30);
264 l_where_clause VARCHAR2 (4000) := '';
265
266 l_resource_level_flag VARCHAR2(1) := '0';
267
268 l_custom_rec BIS_QUERY_ATTRIBUTES;
269
270 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
271 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
272
273 BEGIN
274
275 -- clear out the tables.
276 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
277 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
278
279 -- get all the query parameters
280 opi_dbi_rpt_util_pkg.process_parameters (p_param,
281 l_view_by,
282 l_view_by_col,
283 l_comparison_type,
284 l_xtd,
285 l_cur_suffix,
286 l_where_clause,
287 l_mv,
288 l_join_tbl,
289 l_resource_level_flag,
290 'Y',
291 'OPI',
292 '6.0',
293 '',
294 'RSUT',
295 'RESOURCE_LEVEL');
296
297 -- The measure columns that need to be aggregated are
298 -- avail_val_ <b/g>, actual_val_ <b/g>
299 -- No Grand totals required.
300 poa_dbi_util_pkg.add_column (l_col_tbl,
301 'actual_val_' || l_cur_suffix,
302 'actual_val',
303 'N');
304 poa_dbi_util_pkg.add_column (l_col_tbl,
305 'avail_val_' || l_cur_suffix,
306 'avail_val',
307 'N');
308
309 -- Joining Outer and Inner Query
310 l_query := get_trend_sel_clause(l_view_by, null) ||
311 ' from ' ||
312 poa_dbi_template_pkg.trend_sql (
313 l_xtd,
314 l_comparison_type,
315 l_mv,
316 l_where_clause,
317 l_col_tbl,
318 'N');
319
320
321
322 -- Prepare PMV bind variables
323 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
324 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
325
326 -- get all the basic binds used by POA queries
327 -- Do this before adding any of our binds, since the procedure
328 -- reinitializes the output table
329 poa_dbi_util_pkg.get_custom_trend_binds (l_xtd, l_comparison_type,
330 x_custom_output);
331
332 -- put the custom OPI binds in
333 l_custom_rec.attribute_name := ':OPI_RESOURCE_LEVEL_FLAG';
334 l_custom_rec.attribute_value := l_resource_level_flag;
335 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
336 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
337 x_custom_output.extend;
338 x_custom_output(x_custom_output.count) := l_custom_rec;
339
340 x_custom_sql := l_query;
341
342
343 END get_trd_sql;
344
345 /*
346 The outer main query for the trend SQL.
347 */
348
349 FUNCTION get_trend_sel_clause(p_view_by_dim IN VARCHAR2, p_url IN VARCHAR2)
350 return VARCHAR2
351 IS
352
353 l_sel_clause varchar2(4500);
354
355 BEGIN
356
357 -- Main Outer query
358
359 l_sel_clause :=
360 'SELECT
361 ' || ' cal.name VIEWBY,
362 ' || ' cal.name OPI_ATTRIBUTE1,
363 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_actual_val')
364 || ' OPI_MEASURE1,
365 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_actual_val')
366 || ' OPI_MEASURE2,
367 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_avail_val')
368 || ' OPI_MEASURE3,
369 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_avail_val')
370 || ' OPI_MEASURE4,
371 ' || opi_dbi_rpt_util_pkg.percent_str ('iset.p_actual_val',
372 'iset.p_avail_val',
373 'OPI_MEASURE5') || ',
374 ' || opi_dbi_rpt_util_pkg.percent_str ('iset.c_actual_val',
375 'iset.c_avail_val',
376 'OPI_MEASURE6') || ',
377 ' || opi_dbi_rpt_util_pkg.change_pct_str ('iset.c_actual_val',
378 'iset.c_avail_val',
379 'iset.p_actual_val',
380 'iset.p_avail_val',
381 'OPI_MEASURE7');
382 RETURN l_sel_clause;
383
384 END get_trend_sel_clause;
385
386 END opi_dbi_res_utl_pkg;