[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_RES_EFF_PKG
Source
1 PACKAGE BODY opi_dbi_res_eff_pkg AS
2 /*$Header: OPIDRRSEFB.pls 120.0 2005/05/27 18:38:32 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, p_period_type in VARCHAR2, p_org 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 /*----------------------------------------*/
25
26 /*
27 Report query Function for viewby = Resource Group, Department, Resource, Org
28 */
29 PROCEDURE get_rpt_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
30 x_custom_sql OUT NOCOPY VARCHAR2,
31 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
32 IS
33 l_query VARCHAR2(15000);
34 l_view_by VARCHAR2(120);
35 l_view_by_col VARCHAR2 (120);
36 l_xtd VARCHAR2(10);
37 l_comparison_type VARCHAR2(1) := 'Y';
38 l_cur_suffix VARCHAR2(2);
39 l_custom_sql VARCHAR2 (10000);
40
41 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
42 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
43
44 l_where_clause VARCHAR2 (2000);
45 l_mv VARCHAR2 (30);
46
47 l_resource_level_flag varchar2(1) := '0';
48
49 l_custom_rec BIS_QUERY_ATTRIBUTES;
50
51 l_period_type VARCHAR2(255) := NULL;
52 l_org VARCHAR2(255) := NULL;
53
54 BEGIN
55
56 -- clear out the tables.
57 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
58 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
59
60
61 -- Extracting the period type selected
62 FOR i IN 1..p_param.COUNT
63 LOOP
64 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
65 THEN l_period_type := p_param(i).parameter_value;
66 END IF;
67
68 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
69 THEN l_org := p_param(i).parameter_value;
70 END IF;
71
72 END LOOP;
73
74 -- get all the query parameters
75 opi_dbi_rpt_util_pkg.process_parameters (p_param,
76 l_view_by,
77 l_view_by_col,
78 l_comparison_type,
79 l_xtd,
80 l_cur_suffix,
81 l_where_clause,
82 l_mv,
83 l_join_tbl,
84 l_resource_level_flag,
85 'N',
86 'OPI',
87 '6.0',
88 '',
89 'RSEF',
90 'RESOURCE_LEVEL');
91
92 -- The measure columns that need to be aggregated are
93
94
95 poa_dbi_util_pkg.add_column (l_col_tbl,
96 'std_usage_qty',
97 'std_usage_qty');
98
99 poa_dbi_util_pkg.add_column (l_col_tbl,
100 'actual_qty',
101 'actual_qty');
102
103 -- construct the query
104 l_query := get_status_sel_clause (l_view_by, l_period_type, l_org)
105 || ' from ((
106 ' || poa_dbi_template_pkg.status_sql (l_mv,
107 l_where_clause,
108 l_join_tbl,
109 'N',
110 l_col_tbl,
111 'N');
112
113 -- prepare output for bind variables
114 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
115 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
116
117 -- set the basic bind variables for the status SQL
118 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
119
120 -- Passing OPI_RESOURCE_LEVEL_FLAG to PMV
121 l_custom_rec.attribute_name := ':OPI_RESOURCE_LEVEL_FLAG';
122 l_custom_rec.attribute_value := l_resource_level_flag;
123 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
124 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
125 x_custom_output.extend;
126 x_custom_output(x_custom_output.count) := l_custom_rec;
127
128 x_custom_sql := l_query;
129
130 END get_rpt_sql;
131
132
133 /*
134 Outer main query for viewby = Org,Resource Group, Resource Department, Resource
135 */
136
137 FUNCTION get_status_sel_clause(p_view_by_dim IN VARCHAR2, p_period_type in VARCHAR2, p_org VARCHAR2)
138 return VARCHAR2
139 IS
140
141 l_sel_clause varchar2(4500);
142 l_view_by_col_name varchar2(60);
143 l_description varchar2(30);
144 l_uom varchar2(30) := '';
145 l_qty1 varchar2(35);
146 l_qty2 varchar2(35);
147 l_qty3 varchar2(200);
148
149 BEGIN
150
151 -- Main Outer query
152
153 -- Column to get view by column name
154 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
155 (p_view_by_dim);
156
157 -- Quantity columns for
158 get_qty_columns (p_view_by_dim, l_description, l_uom, l_qty1,
159 l_qty2, l_qty3);
160
161 -- Outer select clause
162 l_sel_clause :=
163 'SELECT '|| opi_dbi_rpt_util_pkg.get_viewby_select_clause(p_view_by_dim) ||
164 l_view_by_col_name || ' OPI_ATTRIBUTE1,';
165
166 IF ((p_view_by_dim = 'RESOURCE+ENI_RESOURCE') AND
167 (upper(p_org) <> 'ALL') AND
168 (p_period_type = 'FII_TIME_WEEK' OR p_period_type = 'FII_TIME_ENT_PERIOD')) THEN
169 l_sel_clause := l_sel_clause || ' ''pFunctionName=OPI_DBI_RES_EFF_JOB_DTL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=RESOURCE+ENI_RESOURCE&pParamIds=Y'' OPI_ATTRIBUTE2 ,';
170 ELSE
171 l_sel_clause := l_sel_clause || 'NULL OPI_ATTRIBUTE2 ,';
172 END IF;
173
174 l_sel_clause := l_sel_clause ||
175 l_qty2 || ' OPI_MEASURE1,
176 ' || l_qty1 || ' OPI_MEASURE2,
177 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.p_std_usage_qty',
178 'oset.p_actual_qty',
179 ' OPI_MEASURE3') || ',
180 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.c_std_usage_qty',
181 'oset.c_actual_qty',
182 ' OPI_MEASURE4') || ',
183 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.c_std_usage_qty',
184 'oset.c_actual_qty',
185 '') || ' -
186 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.p_std_usage_qty',
187 'oset.p_actual_qty',
188 '') || ' OPI_MEASURE5 ,
189 ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_std_usage_qty_total')
190 || ' OPI_MEASURE7 ,
191 ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_qty_total')
192 || ' OPI_MEASURE8 ,
193 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.c_std_usage_qty_total',
194 'oset.c_actual_qty_total',
195 ' OPI_MEASURE9') || ',
196 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.c_std_usage_qty_total',
197 'oset.c_actual_qty_total',
198 '') || ' -
199 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.p_std_usage_qty_total',
200 'oset.p_actual_qty_total',
201 '') || ' OPI_MEASURE10 ';
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_std_usage_qty');
229 p_qty3 := opi_dbi_rpt_util_pkg.percent_str
230 ('oset.c_std_usage_qty',
231 'oset.c_actual_qty',
232 '');
233 END;
234 ELSE
235 BEGIN
236 /*
237 p_description := 'null';
238 p_uom := '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 'RSEF',
295 'RESOURCE_LEVEL');
296
297 -- The measure columns that need to be aggregated are
298 -- No Grand totals required.
299 poa_dbi_util_pkg.add_column (l_col_tbl,
300 'actual_qty',
301 'actual_qty',
302 'N');
303 poa_dbi_util_pkg.add_column (l_col_tbl,
304 'std_usage_qty',
305 'std_usage_qty',
306 'N');
307
308 -- Joining Outer and Inner Query
309 l_query := get_trend_sel_clause(l_view_by, null) ||
310 ' from ' ||
311 poa_dbi_template_pkg.trend_sql (
312 l_xtd,
313 l_comparison_type,
314 l_mv,
315 l_where_clause,
316 l_col_tbl,
317 'N');
318
319
320
321 -- Prepare PMV bind variables
322 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
323 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
324
325 -- get all the basic binds used by POA queries
326 -- Do this before adding any of our binds, since the procedure
327 -- reinitializes the output table
328 poa_dbi_util_pkg.get_custom_trend_binds (l_xtd, l_comparison_type,
329 x_custom_output);
330
331 -- put the custom OPI binds in
332 l_custom_rec.attribute_name := ':OPI_RESOURCE_LEVEL_FLAG';
333 l_custom_rec.attribute_value := l_resource_level_flag;
334 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
335 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
336 x_custom_output.extend;
337 x_custom_output(x_custom_output.count) := l_custom_rec;
338
339 x_custom_sql := l_query;
340
341
342 END get_trd_sql;
343
344 /*
345 The outer main query for the trend SQL.
346 */
347
348 FUNCTION get_trend_sel_clause(p_view_by_dim IN VARCHAR2, p_url IN VARCHAR2)
349 return VARCHAR2
350 IS
351
352 l_sel_clause varchar2(4500);
353
354 BEGIN
355
356 -- Main Outer query
357
358 l_sel_clause :=
359 'SELECT
360 ' || ' cal.name VIEWBY,
361 ' || ' cal.name OPI_ATTRIBUTE1,
362 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_std_usage_qty')
363 || ' OPI_MEASURE1,
364 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_actual_qty')
365 || ' OPI_MEASURE2,
366 ' || opi_dbi_rpt_util_pkg.percent_str ('iset.p_std_usage_qty',
367 'iset.p_actual_qty',
368 'OPI_MEASURE3') || ',
369 ' || opi_dbi_rpt_util_pkg.percent_str ('iset.c_std_usage_qty',
370 'iset.c_actual_qty ',
371 'OPI_MEASURE4') || ',
372 ' || opi_dbi_rpt_util_pkg.percent_str ('iset.c_std_usage_qty',
373 'iset.c_actual_qty',
374 '') || ' -
375 ' || opi_dbi_rpt_util_pkg.percent_str ('iset.p_std_usage_qty',
376 'iset.p_actual_qty',
377 '') || ' OPI_MEASURE5 ';
378
379 RETURN l_sel_clause;
380
381 END get_trend_sel_clause;
382
383 END opi_dbi_res_eff_pkg;