eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OPI_DBI_RES_VAR_PKG

Source


1 PACKAGE BODY opi_dbi_res_var_pkg AS
2 /*$Header: OPIDRRSVRB.pls 120.0 2005/05/24 17:20:13 appldev noship $ */
3 
4 /*++++++++++++++++++++++++++++++++++++++++*/
5 /* Function and procedure declarations in this file but not in spec*/
6 /*++++++++++++++++++++++++++++++++++++++++*/
7 
8 FUNCTION get_status_sel_clause (p_view_by_dim IN VARCHAR2, p_period_type in VARCHAR2, p_org in VARCHAR2)
9     RETURN VARCHAR2;
10 
11 PROCEDURE get_qty_columns (p_dim_name VARCHAR2,
12                            p_description OUT NOCOPY VARCHAR2,
13                            p_uom OUT NOCOPY VARCHAR2,
14                            p_qty1 OUT NOCOPY VARCHAR2,
15                            p_qty2 OUT NOCOPY VARCHAR2,
16                            p_qty3 OUT NOCOPY VARCHAR2);
17 
18 
19 FUNCTION get_trend_sel_clause(p_view_by_dim IN VARCHAR2, p_url IN VARCHAR2)
20     return VARCHAR2;
21 
22 
23 /*----------------------------------------*/
24 
25 /*
26     Report query Function for viewby = Resource Group, Department, Resource, Org
27 */
28 PROCEDURE get_rpt_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
29                             x_custom_sql OUT NOCOPY VARCHAR2,
30                             x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
31 IS
32     l_query VARCHAR2(15000);
33     l_view_by VARCHAR2(120);
34     l_view_by_col VARCHAR2 (120);
35     l_xtd VARCHAR2(10);
36     l_comparison_type VARCHAR2(1) := 'Y';
37     l_cur_suffix VARCHAR2(2);
38     l_custom_sql VARCHAR2 (10000);
39 
40     l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
41     l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
42 
43     l_where_clause VARCHAR2 (2000);
44     l_mv VARCHAR2 (30);
45 
46     l_resource_level_flag varchar2(1) := '0';
47 
48     l_custom_rec BIS_QUERY_ATTRIBUTES;
49 
50     l_period_type VARCHAR2(255)  := NULL;
51     l_org VARCHAR2(255) := NULL;
52 
53 BEGIN
54 
55     -- clear out the tables.
56     l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
57     l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
58 
59     -- Extracting the period type selected
60     FOR i IN 1..p_param.COUNT
61     LOOP
62       	IF(p_param(i).parameter_name = 'PERIOD_TYPE')
63        	    THEN l_period_type := p_param(i).parameter_value;
64         END IF;
65 
66         IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
67             THEN l_org := p_param(i).parameter_value;
68         END IF;
69 
70     END LOOP;
71     -- get all the query parameters
72     opi_dbi_rpt_util_pkg.process_parameters (p_param,
73                                           l_view_by,
74                                           l_view_by_col,
75                                           l_comparison_type,
76                                           l_xtd,
77                                           l_cur_suffix,
78                                           l_where_clause,
79                                           l_mv,
80                                           l_join_tbl,
81                                           l_resource_level_flag,
82                                           'N',
83                                           'OPI',
84                                           '6.0',
85                                           '',
86                                           'RSVR',
87                                           'RESOURCE_LEVEL');
88 
89     -- The measure columns that need to be aggregated are
90     -- std_usage_val_ <b/g>, actual_val_ <b/g>
91     -- If viewing by Resource, then sum up
92     -- std_usage_qty, actual_qty
93     poa_dbi_util_pkg.add_column (l_col_tbl,
94                                  'actual_val_' || l_cur_suffix,
95                                  'actual_val');
96 
97     poa_dbi_util_pkg.add_column (l_col_tbl,
98                                  'std_usage_val_' || l_cur_suffix,
99                                  'std_usage_val');
100 
101     poa_dbi_util_pkg.add_column (l_col_tbl,
102                                  'std_usage_qty',
103                                  'std_usage_qty');
104 
105     poa_dbi_util_pkg.add_column (l_col_tbl,
106                                  'actual_qty',
107                                  'actual_qty');
108 
109     -- construct the query
110     l_query := get_status_sel_clause (l_view_by, l_period_type, l_org)
111           || ' from ((
112         ' || poa_dbi_template_pkg.status_sql (l_mv,
113                                               l_where_clause,
114                                               l_join_tbl,
115                                               'N',
116                                               l_col_tbl,
117                                               'N');
118 
119     -- prepare output for bind variables
120     x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
121     l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
122 
123     -- set the basic bind variables for the status SQL
124     poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
125 
126     -- Passing OPI_RESOURCE_LEVEL_FLAG to PMV
127     l_custom_rec.attribute_name := ':OPI_RESOURCE_LEVEL_FLAG';
128     l_custom_rec.attribute_value := l_resource_level_flag;
129     l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
130     l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
131     x_custom_output.extend;
132     x_custom_output(x_custom_output.count) := l_custom_rec;
133 
134     x_custom_sql := l_query;
135 
136 END get_rpt_sql;
137 
138 
139 /*
140     Outer main query for viewby = Org,Resource Group, Resource Department, Resource
141 */
142 
143 FUNCTION get_status_sel_clause(p_view_by_dim IN VARCHAR2, p_period_type in VARCHAR2, p_org VARCHAR2)
144     return VARCHAR2
145 IS
146 
147     l_sel_clause varchar2(4500);
148     l_view_by_col_name varchar2(60);
149     l_description varchar2(30);
150     l_uom varchar2(30) := '';
151 
152 BEGIN
153 
154     -- Main Outer query
155 
156     -- Column to get view by column name
157     l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
158                                                 (p_view_by_dim);
159     -- Outer select clause
160     --
161     -- For bug 3055129 changed the calculation for
162     -- OPI_MEASURE6 - Change in Variance Amount
163     --
164     l_sel_clause :=
165     'SELECT
166       ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause(p_view_by_dim) ||
167                 l_view_by_col_name                              || ' OPI_ATTRIBUTE1,
168            NULL                         OPI_ATTRIBUTE2,' ;
169 
170       IF ((p_view_by_dim = 'RESOURCE+ENI_RESOURCE') AND
171          (upper(p_org) <> 'ALL') AND
172          (p_period_type = 'FII_TIME_WEEK' OR p_period_type = 'FII_TIME_ENT_PERIOD')) THEN
173               l_sel_clause := l_sel_clause || ' ''pFunctionName=OPI_DBI_RES_VAR_JOB_DTL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=RESOURCE+ENI_RESOURCE&pParamIds=Y'' OPI_ATTRIBUTE5 ,';
174           ELSE
175               l_sel_clause := l_sel_clause || 'NULL OPI_ATTRIBUTE5 ,';
176 
177       END IF;
178 
179       l_sel_clause := l_sel_clause ||
180            opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_qty') || ' OPI_MEASURE1,
181       ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_val') || ' OPI_MEASURE2,
182       ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_std_usage_qty') || ' OPI_MEASURE3,
183       ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_std_usage_val') || ' OPI_MEASURE4,
184       ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_val') || ' -
185       ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_std_usage_val') || ' OPI_MEASURE5,
186       ' || opi_dbi_rpt_util_pkg.change_str (
187             opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_val') || ' - ' ||
188             opi_dbi_rpt_util_pkg.nvl_str ('oset.c_std_usage_val'),
189             opi_dbi_rpt_util_pkg.nvl_str ('oset.p_actual_val') || ' - ' ||
190             opi_dbi_rpt_util_pkg.nvl_str ('oset.p_std_usage_val'),
191             opi_dbi_rpt_util_pkg.nvl_str ('oset.p_actual_val') || ' - ' ||
192             opi_dbi_rpt_util_pkg.nvl_str ('oset.p_std_usage_val'),
193             'OPI_MEASURE6') || ',
194       ' || opi_dbi_rpt_util_pkg.change_str ('oset.c_actual_val',
195                                             'oset.c_std_usage_val',
196                                             'oset.c_std_usage_val',
197                                             'OPI_MEASURE7') || ',
198       ' || opi_dbi_rpt_util_pkg.change_str ('oset.c_actual_val',
199                             'oset.c_std_usage_val',
200                             'oset.c_std_usage_val',
201                             '') || ' - '
202         || opi_dbi_rpt_util_pkg.change_str ('oset.p_actual_val',
203                                 'oset.p_std_usage_val',
204                                 'oset.p_std_usage_val',
205                                 '')
206                                             || ' OPI_MEASURE8,
207       ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_val_total')
208                                     || ' OPI_MEASURE9,
209       ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_std_usage_val_total')
210                                     || ' OPI_MEASURE10,
211       ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_val_total') || ' - '
212         || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_std_usage_val_total')
213                                     || ' OPI_MEASURE11,
214       ' || opi_dbi_rpt_util_pkg.change_str (
215             opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_val_total') ||' - ' ||
216             opi_dbi_rpt_util_pkg.nvl_str ('oset.c_std_usage_val_total'),
217             opi_dbi_rpt_util_pkg.nvl_str ('oset.p_actual_val_total') ||' - ' ||
218             opi_dbi_rpt_util_pkg.nvl_str ('oset.p_std_usage_val_total'),
219             opi_dbi_rpt_util_pkg.nvl_str ('oset.p_actual_val_total') ||' - ' ||
220             opi_dbi_rpt_util_pkg.nvl_str ('oset.p_std_usage_val_total'),
221             'OPI_MEASURE12') || ',
222       ' || opi_dbi_rpt_util_pkg.change_str ('oset.c_actual_val_total',
223                             'oset.c_std_usage_val_total',
224                             'oset.c_std_usage_val_total',
225                             'OPI_MEASURE13') || ',
226       ' || opi_dbi_rpt_util_pkg.change_str ('oset.c_actual_val_total',
227                                 'oset.c_std_usage_val_total',
228                             'oset.c_std_usage_val_total',
229                             '') ||
230       ' - ' || opi_dbi_rpt_util_pkg.change_str ('oset.p_actual_val_total',
231                                 'oset.p_std_usage_val_total',
232                                 'oset.p_std_usage_val_total',
233                                 '')          || ' OPI_MEASURE14,
234       ' || opi_dbi_rpt_util_pkg.nvl_str('oset.p_actual_val') || ' - '
235     || opi_dbi_rpt_util_pkg.nvl_str('oset.p_std_usage_val')    || ' OPI_MEASURE15,
236       ' || opi_dbi_rpt_util_pkg.change_str('oset.p_actual_val',
237                            'oset.p_std_usage_val',
238                        'oset.p_std_usage_val',
239                        'OPI_MEASURE16') || ' ,
240       ' || opi_dbi_rpt_util_pkg.change_str ('oset.c_actual_val',
241                                                   'oset.c_std_usage_val',
242                                                   'oset.c_std_usage_val',
243                                             'OPI_MEASURE19') || ',
244       ' || opi_dbi_rpt_util_pkg.change_str('oset.p_actual_val',
245                                'oset.p_std_usage_val',
246                            'oset.p_std_usage_val',
247                        'OPI_MEASURE20') || ' ,
248       ' || opi_dbi_rpt_util_pkg.change_str ('oset.c_actual_val_total',
249                             'oset.c_std_usage_val_total',
250                             'oset.c_std_usage_val_total',
251                             'OPI_MEASURE21') || ',
252       ' || opi_dbi_rpt_util_pkg.change_str ('oset.p_actual_val_total',
253                             'oset.p_std_usage_val_total',
254                             'oset.p_std_usage_val_total',
255                             'OPI_MEASURE22') || ',
256       ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_qty_total') || ' OPI_MEASURE17,
257       ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_std_usage_qty_total') || ' OPI_MEASURE18 ';
258 
259 
260   RETURN l_sel_clause;
261 
262 END get_status_sel_clause;
263 
264 
265 PROCEDURE get_qty_columns (p_dim_name VARCHAR2,
266                            p_description OUT NOCOPY VARCHAR2,
267                            p_uom OUT NOCOPY VARCHAR2,
268                            p_qty1 OUT NOCOPY VARCHAR2,
269                            p_qty2 OUT NOCOPY VARCHAR2,
270                            p_qty3 OUT NOCOPY VARCHAR2)
271 IS
272    l_description varchar2(30);
273    l_uom varchar2(30);
274 
275 BEGIN
276       CASE
277       WHEN p_dim_name = 'RESOURCE+ENI_RESOURCE' THEN
278               BEGIN
279 /*
280                   p_description := 'v.description';
281                   p_uom := 'v2.unit_of_measure';
282 */
283                   p_qty1 := opi_dbi_rpt_util_pkg.nvl_str ('oset.c_actual_val');
284                   p_qty2 := opi_dbi_rpt_util_pkg.nvl_str
285                                     ('oset.c_std_usage_val');
286                   p_qty3 := opi_dbi_rpt_util_pkg.percent_str
287                                         ('oset.c_actual_val',
288                                          'oset.c_std_usage_val',
289                                          '');
290               END;
291           ELSE
292               BEGIN
293 /*
294                   p_description := 'null';
295                   p_uom := 'null';
296 */
297                   p_qty1 := 'null';
298                   p_qty2 := 'null';
299                   p_qty3 := 'null';
300               END;
301       END CASE;
302 END get_qty_columns;
303 
304 
305 /*
306     Report query for viewby = time
307 */
308 
312 IS
309 PROCEDURE get_trd_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
310                            x_custom_sql OUT NOCOPY VARCHAR2,
311                            x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
313     l_query VARCHAR2(15000);
314     l_view_by VARCHAR2(120);
315     l_view_by_col VARCHAR2 (120);
316     l_xtd varchar2(10);
317     l_comparison_type VARCHAR2(1) := 'Y';
318     l_cur_suffix VARCHAR2(2);
319     l_custom_sql VARCHAR2(4000);
320     l_mv VARCHAR2 (30);
321     l_where_clause VARCHAR2 (4000) := '';
322 
323     l_resource_level_flag VARCHAR2(1) := '0';
324 
325     l_custom_rec BIS_QUERY_ATTRIBUTES;
326 
327     l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
328     l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
329 
330 BEGIN
331 
332     -- clear out the tables.
333     l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
334     l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
335 
336     -- get all the query parameters
337     opi_dbi_rpt_util_pkg.process_parameters (p_param,
338                                           l_view_by,
339                                           l_view_by_col,
340                                           l_comparison_type,
341                                           l_xtd,
342                                           l_cur_suffix,
343                                           l_where_clause,
344                                           l_mv,
345                                           l_join_tbl,
346                                           l_resource_level_flag,
347                                           'Y',
348                                           'OPI',
349                                           '6.0',
350                                           '',
351                                           'RSVR',
352                                           'RESOURCE_LEVEL');
353 
354     -- The measure columns that need to be aggregated are
355     -- std_usage_val_ <b/g>, actual_val_ <b/g>
356     -- No Grand totals required.
357 
358 
359     poa_dbi_util_pkg.add_column (l_col_tbl,
360                                 'actual_val_' || l_cur_suffix,
361                                 'actual_val',
362                                 'N');
363     poa_dbi_util_pkg.add_column (l_col_tbl,
364                                  'std_usage_val_' || l_cur_suffix,
365                                  'std_usage_val',
366                                  'N');
367 
368 
369 
370     -- Joining Outer and Inner Query
371     l_query := get_trend_sel_clause(l_view_by, null) ||
372                ' from ' ||
373                poa_dbi_template_pkg.trend_sql (
374                     l_xtd,
375                     l_comparison_type,
376                     l_mv,
377                     l_where_clause,
378                     l_col_tbl,
379                     'N');
380 
381 
382 
383     -- Prepare PMV bind variables
384     x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
385     l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
386 
387     -- get all the basic binds used by POA queries
388     -- Do this before adding any of our binds, since the procedure
389     -- reinitializes the output table
390     poa_dbi_util_pkg.get_custom_trend_binds (l_xtd, l_comparison_type,
391                                              x_custom_output);
392 
393     -- put the custom OPI binds in
394     l_custom_rec.attribute_name := ':OPI_RESOURCE_LEVEL_FLAG';
395     l_custom_rec.attribute_value := l_resource_level_flag;
396     l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
397     l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
398     x_custom_output.extend;
399     x_custom_output(x_custom_output.count) := l_custom_rec;
400 
401     x_custom_sql := l_query;
402 
403 
404 END get_trd_sql;
405 
406 /*
407     The outer main query for the trend SQL.
408 */
409 
410 FUNCTION get_trend_sel_clause(p_view_by_dim IN VARCHAR2, p_url IN VARCHAR2)
411     return VARCHAR2
412 IS
413 
414     l_sel_clause varchar2(4500);
415 
416 BEGIN
417 
418     -- Main Outer query
419 
420     l_sel_clause :=
421     'SELECT
422         ' || ' cal.name VIEWBY,
423         ' || ' cal.name OPI_ATTRIBUTE1,
424         ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_actual_val')
425                                            || ' OPI_MEASURE2,
426         ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_std_usage_val')
427                                            || ' OPI_MEASURE4,
428         ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_actual_val') || ' -
429         ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_std_usage_val')
430                            || 'OPI_MEASURE5,
431         ' ||  opi_dbi_rpt_util_pkg.change_str (
432                 opi_dbi_rpt_util_pkg.nvl_str ('iset.c_actual_val') || ' - ' ||
433                 opi_dbi_rpt_util_pkg.nvl_str ('iset.c_std_usage_val'),
434                 opi_dbi_rpt_util_pkg.nvl_str ('iset.p_actual_val') || ' - ' ||
435                 opi_dbi_rpt_util_pkg.nvl_str ('iset.p_std_usage_val'),
436                 opi_dbi_rpt_util_pkg.nvl_str ('iset.p_actual_val') || ' - ' ||
437                 opi_dbi_rpt_util_pkg.nvl_str ('iset.p_std_usage_val'),
438                 'OPI_MEASURE6') || ',
439         ' || opi_dbi_rpt_util_pkg.change_str ('iset.c_actual_val',
440                                               'iset.c_std_usage_val',
441                                               'iset.c_std_usage_val',
442                                               'OPI_MEASURE7') || ',
443         ' || opi_dbi_rpt_util_pkg.change_str ('iset.c_actual_val',
444                               'iset.c_std_usage_val',
445                               'iset.c_std_usage_val',
446                               '') || ' - '
447       || opi_dbi_rpt_util_pkg.change_str ('iset.p_actual_val',
448                               'iset.p_std_usage_val',
449                                   'iset.p_std_usage_val',
450                                   '')
451                                || 'OPI_MEASURE8,
452     ' || opi_dbi_rpt_util_pkg.nvl_str('iset.p_actual_val') || ' - '
453       || opi_dbi_rpt_util_pkg.nvl_str('iset.p_std_usage_val')    || ' OPI_MEASURE15,
454     ' || opi_dbi_rpt_util_pkg.change_str('iset.p_actual_val',
455                              'iset.p_std_usage_val',
456                                  'iset.p_std_usage_val',
457                          'OPI_MEASURE16');
458 
459   RETURN l_sel_clause;
460 
461 END get_trend_sel_clause;
462 
463 END opi_dbi_res_var_pkg;