[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_ONTIME_PROD_RPT_PKG
Source
1 PACKAGE BODY OPI_DBI_ONTIME_PROD_RPT_PKG AS
2 /*$Header: OPIDOTPRPTB.pls 120.2 2005/09/29 23:12 sberi noship $ */
3 FUNCTION GET_ONTIME_PROD_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
4 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
5 RETURN VARCHAR2;
6
7
8 /* -------------------------------------------------------------------------------------------
9 Procedure Name: GET_ONTIME_PROD_SQL
10 Parameters : p_param(IN parameter), x_custom_sql (OUT parameter)
11 Purpose : This procedure calls process parameters of the OPI util package to get things
12 like MV name, aggregation flag, View By and p_param (the parameter portlet).
13 It also forms the report query by calling the function GET_ONTIME_PROD_SEL_
14 CLAUSE
15 ----------------------------------------------------------------------------------------------
16 */
17 PROCEDURE GET_ONTIME_PROD_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
18 x_custom_sql OUT NOCOPY VARCHAR2,
19 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
20 IS
21 l_query VARCHAR2(15000);
22 l_view_by VARCHAR2(120);
23 l_view_by_col VARCHAR2 (120);
24 l_xtd VARCHAR2(10);
25 l_comparison_type VARCHAR2(1);
26 l_cur_suffix VARCHAR2(5);
27 l_custom_sql VARCHAR2 (10000);
28 l_subinv_val VARCHAR2 (120) := NULL;
29 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
30 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
31 l_where_clause VARCHAR2 (2000);
32 l_mv VARCHAR2 (30);
33 l_aggregation_level_flag VARCHAR2(10);
34 l_custom_rec BIS_QUERY_ATTRIBUTES;
35 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
36
37 BEGIN
38 -- initialization block
39 l_comparison_type := 'Y';
40 l_aggregation_level_flag := '0';
41
42 -- clear out the column and Join info tables.
43 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
44 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
45 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
46
47 -- get all the query parameters
48 opi_dbi_rpt_util_pkg.process_parameters (
49 p_param => p_param,
50 p_view_by => l_view_by,
51 p_view_by_col_name => l_view_by_col,
52 p_comparison_type => l_comparison_type,
53 p_xtd => l_xtd,
54 p_cur_suffix => l_cur_suffix,
55 p_where_clause => l_where_clause,
56 p_mv => l_mv,
57 p_join_tbl => l_join_tbl,
58 p_mv_level_flag => l_aggregation_level_flag,
59 p_trend => 'N',
60 p_func_area => 'OPI',
61 p_version => '8.0',
62 p_role => '',
63 p_mv_set => 'OTP',
64 p_mv_flag_type => 'ITEM_CAT');
65 -- Add measure columns that need to be aggregated
66
67 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
68 p_col_name => 'late_prod_cnt' ,
69 p_alias_name => 'late_prod_cnt',
70 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
71 p_grand_total => 'Y',
72 p_to_date_type => 'XTD'
73 );
74 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
75 p_col_name =>'late_prod_val_' || l_cur_suffix,
76 p_alias_name =>'late_prod_val',
77 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
78 p_grand_total => 'Y',
79 p_to_date_type => 'XTD'
80 );
81 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
82 p_col_name => 'ontime_prod_cnt',
83 p_alias_name => 'ontime_prod_cnt',
84 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
85 p_grand_total => 'Y',
86 p_to_date_type => 'XTD'
87 );
88 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
89 p_col_name =>'ontime_prod_val_' || l_cur_suffix,
90 p_alias_name =>'ontime_prod_val',
91 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
92 p_grand_total => 'Y',
93 p_to_date_type => 'XTD'
94 );
95
96 -- Quantity columns are only needed for Item viewby.
97 IF (l_view_by = 'ITEM+ENI_ITEM_ORG') THEN
98 --{
99 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
100 p_col_name => 'late_prod_qty' ,
101 p_alias_name => 'late_prod_qty',
102 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
103 p_grand_total => 'N',
104 p_to_date_type => 'XTD'
105 );
106 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
107 p_col_name => 'ontime_prod_qty' ,
108 p_alias_name => 'ontime_prod_qty',
109 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
110 p_grand_total => 'N',
111 p_to_date_type => 'XTD'
112 );
113 --}
114 END IF;
115 l_query := GET_ONTIME_PROD_SEL_CLAUSE (p_view_by_dim => l_view_by,
116 p_join_tbl => l_join_tbl)
117 || ' from
118 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
119 p_where_clause => l_where_clause,
120 p_join_tables => l_join_tbl,
121 p_use_windowing => 'Y',
122 p_col_name => l_col_tbl,
123 p_use_grpid => 'N',
124 p_paren_count => 3,
125 p_filter_where => NULL,
126 p_generate_viewby => 'Y',
127 p_in_join_tables => NULL);
128 -- prepare output for bind variables
129 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
130 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
131 -- set the basic bind variables for the status SQL
132 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
133
134 -- Passing OPI_AGGREGATION_LEVEL_FLAGS to PMV
135 l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
136 l_custom_rec.attribute_value := l_aggregation_level_flag;
137 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
138 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
139 x_custom_output.extend;
140 x_custom_output(x_custom_output.count) := l_custom_rec;
141 commit;
142 x_custom_sql := l_query;
143 END GET_ONTIME_PROD_SQL;
144
145 /*
146 ----------------------------------------------------------------------------------------------
147 Function Name: GET_ONTIME_PROD_SEL_CLAUSE
148 Parameters : p_view_by_dim(IN parameter), p_join_tbl (IN parameter)
149 Purpose : This function helps in constructing the report query of the On-Time Production
150 Report. It defines each attribute and measure and how we would source them
151 in our query.
152 ----------------------------------------------------------------------------------------------
153 */
154
155 FUNCTION GET_ONTIME_PROD_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
156 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
157 RETURN VARCHAR2
158 IS
159 l_sel_clause VARCHAR2(15000);
160 l_view_by_col_name VARCHAR2(120);
161 l_description VARCHAR2(30);
162 l_uom VARCHAR2(30);
163 l_view_by_fact_col VARCHAR2(400);
164 BEGIN
165 l_description := 'null';
166 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
167 (p_view_by_dim);
168 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
169 (p_join_tbl);
170
171
172 opi_dbi_rpt_util_pkg.get_viewby_item_columns (p_view_by_dim, l_description, l_uom);
173
174
175 l_sel_clause :=
176 'SELECT
177 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
178 l_description || ' OPI_ATTRIBUTE1,
179 ' || l_uom || ' OPI_ATTRIBUTE2';
180
181 l_sel_clause := l_sel_clause ||
182 ',OPI_MEASURE1
183 ,OPI_MEASURE2
184 ,OPI_MEASURE3
185 ,OPI_MEASURE4
186 ,OPI_MEASURE5
187 ,OPI_MEASURE6
188 ,OPI_MEASURE7
189 ,OPI_MEASURE8
190 ,OPI_MEASURE9
191 ,OPI_MEASURE10
192 ,OPI_MEASURE24
193 ,OPI_MEASURE11
194 ,OPI_MEASURE12
195 ,OPI_MEASURE13
196 ,OPI_MEASURE14
197 ,OPI_MEASURE15
198 ,OPI_MEASURE16
199 ,OPI_MEASURE17
200 ,OPI_MEASURE18
201 ,OPI_MEASURE19
202 ,OPI_MEASURE20
203 ,OPI_MEASURE21
204 ,OPI_MEASURE22
205 ,OPI_MEASURE23
206 ,OPI_MEASURE25
207 ,OPI_MEASURE26
208 ,OPI_MEASURE27
209 ,OPI_MEASURE28'|| fnd_global.newline;
210
211 l_sel_clause := l_sel_clause ||
212 'FROM ( SELECT
213 rank() over (&ORDER_BY_CLAUSE nulls last '||', '||l_view_by_fact_col||') - 1 rnk
214 ,'||l_view_by_fact_col;
215
216 l_sel_clause := l_sel_clause ||
217 ',OPI_MEASURE1
218 ,OPI_MEASURE2
219 ,OPI_MEASURE3
220 ,OPI_MEASURE4
221 ,OPI_MEASURE5
222 ,OPI_MEASURE6
223 ,OPI_MEASURE7
224 ,OPI_MEASURE8
225 ,OPI_MEASURE9
226 ,OPI_MEASURE10
227 ,OPI_MEASURE24
228 ,OPI_MEASURE11
229 ,OPI_MEASURE12
230 ,OPI_MEASURE13
231 ,OPI_MEASURE14
232 ,OPI_MEASURE15
233 ,OPI_MEASURE16
234 ,OPI_MEASURE17
235 ,OPI_MEASURE18
236 ,OPI_MEASURE19
237 ,OPI_MEASURE20
238 ,OPI_MEASURE21
239 ,OPI_MEASURE22
240 ,OPI_MEASURE23
241 ,OPI_MEASURE25
242 ,OPI_MEASURE26
243 ,OPI_MEASURE27
244 ,OPI_MEASURE28'|| fnd_global.newline;
245
246 l_sel_clause := l_sel_clause ||
247 'FROM ( SELECT ' || fnd_global.newline ||
248 l_view_by_fact_col || fnd_global.newline ||
249 ',' || opi_dbi_rpt_util_pkg.nvl_str (
250 p_str => 'c_late_prod_cnt',
251 p_default_val => 0) || ' OPI_MEASURE1, '|| fnd_global.newline;
252
253 IF (p_view_by_dim = 'ITEM+ENI_ITEM_ORG') THEN
254 --{
255 l_sel_clause := l_sel_clause ||
259 --}
256 opi_dbi_rpt_util_pkg.nvl_str (
257 p_str => 'c_late_prod_qty',
258 p_default_val => 0) || ' OPI_MEASURE2, '|| fnd_global.newline;
260 ELSE
261 --{
262 l_sel_clause := l_sel_clause || 'NULL OPI_MEASURE2, '|| fnd_global.newline;
263 --}
264 END IF;
265
266 l_sel_clause := l_sel_clause ||
267 opi_dbi_rpt_util_pkg.nvl_str (
268 p_str => 'c_late_prod_val',
269 p_default_val => 0) || ' OPI_MEASURE3,
270 ' || opi_dbi_rpt_util_pkg.nvl_str (
271 p_str => 'c_ontime_prod_cnt',
272 p_default_val => 0) || ' OPI_MEASURE4,'|| fnd_global.newline;
273
274 IF (p_view_by_dim = 'ITEM+ENI_ITEM_ORG') THEN
275 --{
276 l_sel_clause := l_sel_clause ||
277 opi_dbi_rpt_util_pkg.nvl_str (
278 p_str => 'c_ontime_prod_qty',
279 p_default_val => 0) || ' OPI_MEASURE5,'|| fnd_global.newline;
280 --}
281 ELSE
282 --{
283 l_sel_clause := l_sel_clause || 'NULL OPI_MEASURE5, '|| fnd_global.newline;
284 --}
285 END IF;
286
287 l_sel_clause := l_sel_clause ||
288 opi_dbi_rpt_util_pkg.nvl_str (
289 p_str => 'p_ontime_prod_val',
290 p_default_val => 0) || ' OPI_MEASURE6,
291 ' || opi_dbi_rpt_util_pkg.nvl_str (
292 p_str => 'c_ontime_prod_val',
293 p_default_val => 0) || ' OPI_MEASURE7,
294 ' || opi_dbi_rpt_util_pkg.nvl_str (
295 p_str => '(c_late_prod_cnt+c_ontime_prod_cnt)',
296 p_default_val => 0) || ' OPI_MEASURE8,'|| fnd_global.newline;
297
298 IF (p_view_by_dim = 'ITEM+ENI_ITEM_ORG') THEN
299 --{
300 l_sel_clause := l_sel_clause ||
301 opi_dbi_rpt_util_pkg.nvl_str (
302 p_str => '(c_late_prod_qty+c_ontime_prod_qty)',
303 p_default_val => 0) || ' OPI_MEASURE9,'|| fnd_global.newline;
304 --}
305 ELSE
306 --{
307 l_sel_clause := l_sel_clause || 'NULL OPI_MEASURE9, '|| fnd_global.newline;
308 --}
309 END IF;
310
311 l_sel_clause := l_sel_clause ||
312 opi_dbi_rpt_util_pkg.nvl_str (
313 p_str => '(c_late_prod_val+c_ontime_prod_val)',
314 p_default_val => 0) || ' OPI_MEASURE10,
315 ' || opi_dbi_rpt_util_pkg.percent_str(
316 p_numerator => 'p_ontime_prod_cnt',
317 p_denominator => '(p_late_prod_cnt+p_ontime_prod_cnt)',
318 p_measure_name => 'OPI_MEASURE24') || ',
319 ' || opi_dbi_rpt_util_pkg.percent_str(
320 p_numerator => 'c_ontime_prod_cnt',
321 p_denominator => '(c_late_prod_cnt+c_ontime_prod_cnt)',
322 p_measure_name => 'OPI_MEASURE11') || ',
323 ' || opi_dbi_rpt_util_pkg.percent_str(
324 p_numerator => 'p_ontime_prod_val',
325 p_denominator => '(p_late_prod_val+p_ontime_prod_val)',
326 p_measure_name => 'OPI_MEASURE12') || ',
327 ' || opi_dbi_rpt_util_pkg.percent_str(
328 p_numerator => 'c_ontime_prod_val',
329 p_denominator => '(c_late_prod_val+c_ontime_prod_val)',
330 p_measure_name => 'OPI_MEASURE13') || ',
331 ' || opi_dbi_rpt_util_pkg.rate_str (
332 p_numerator => 'c_ontime_prod_val',
333 p_denominator => '(c_late_prod_val+c_ontime_prod_val)',
334 p_rate_type => 'NP') || '-' ||
335 opi_dbi_rpt_util_pkg.rate_str (
336 p_numerator => 'p_ontime_prod_val',
337 p_denominator => '(p_late_prod_val+p_ontime_prod_val)',
338 p_rate_type => 'NP') || 'OPI_MEASURE14,
339 ' || opi_dbi_rpt_util_pkg.nvl_str (
340 p_str => 'c_late_prod_cnt_total',
341 p_default_val => 0) || ' OPI_MEASURE15,
342 ' || opi_dbi_rpt_util_pkg.nvl_str (
343 p_str => 'c_late_prod_val_total',
344 p_default_val => 0) || ' OPI_MEASURE16,
345 ' || opi_dbi_rpt_util_pkg.nvl_str (
346 p_str => 'c_ontime_prod_cnt_total',
347 p_default_val => 0) || ' OPI_MEASURE17,
348 ' || opi_dbi_rpt_util_pkg.nvl_str (
349 p_str => 'c_ontime_prod_val_total',
350 p_default_val => 0) || ' OPI_MEASURE18,
351 ' || opi_dbi_rpt_util_pkg.nvl_str (
352 p_str => '(c_late_prod_cnt_total+c_ontime_prod_cnt_total)',
353 p_default_val => 0) || ' OPI_MEASURE19,
354 ' || opi_dbi_rpt_util_pkg.nvl_str (
355 p_str => '(c_late_prod_val_total+c_ontime_prod_val_total)',
356 p_default_val => 0) || ' OPI_MEASURE20,
357 ' || opi_dbi_rpt_util_pkg.percent_str(
358 p_numerator => 'c_ontime_prod_cnt_total',
359 p_denominator => '(c_late_prod_cnt_total+c_ontime_prod_cnt_total)',
360 p_measure_name => 'OPI_MEASURE21') || ',
361 ' || opi_dbi_rpt_util_pkg.percent_str(
362 p_numerator => 'c_ontime_prod_val_total',
363 p_denominator => '(c_late_prod_val_total+c_ontime_prod_val_total)',
367 p_denominator => '(c_late_prod_val_total+c_ontime_prod_val_total)',
364 p_measure_name => 'OPI_MEASURE22') || ',
365 ' || opi_dbi_rpt_util_pkg.rate_str (
366 p_numerator => 'c_ontime_prod_val_total',
368 p_rate_type => 'NP') || '-' ||
369 opi_dbi_rpt_util_pkg.rate_str (
370 p_numerator => 'p_ontime_prod_val_total',
371 p_denominator => '(p_late_prod_val_total+p_ontime_prod_val_total)',
372 p_rate_type => 'NP') || 'OPI_MEASURE23,
373 ' || opi_dbi_rpt_util_pkg.percent_str(
374 p_numerator => 'c_ontime_prod_val',
375 p_denominator => '(c_late_prod_val+c_ontime_prod_val)',
376 p_measure_name => 'OPI_MEASURE25') || ',
377 ' || opi_dbi_rpt_util_pkg.percent_str(
378 p_numerator => 'p_ontime_prod_val',
379 p_denominator => '(p_late_prod_val+p_ontime_prod_val)',
380 p_measure_name => 'OPI_MEASURE26') || ',
381 ' || opi_dbi_rpt_util_pkg.percent_str(
382 p_numerator => 'c_ontime_prod_val_total',
383 p_denominator => '(c_late_prod_val_total+c_ontime_prod_val_total)',
384 p_measure_name => 'OPI_MEASURE27') || ',
385 ' || opi_dbi_rpt_util_pkg.percent_str(
386 p_numerator => 'p_ontime_prod_val_total',
387 p_denominator => '(p_late_prod_val_total+p_ontime_prod_val_total)',
388 p_measure_name => 'OPI_MEASURE28');
389
390 RETURN l_sel_clause;
391
392 END GET_ONTIME_PROD_SEL_CLAUSE;
393
394 END OPI_DBI_ONTIME_PROD_RPT_PKG;