[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_WMS_RTP_PKG
Source
1 PACKAGE BODY opi_dbi_wms_rtp_pkg AS
2 /*$Header: OPIDRWMSRTPB.pls 120.0 2005/05/24 18:17:27 appldev noship $ */
3 /*----------------------------------------------------
4 Declare PRIVATE procedures and functions for package
5 -----------------------------------------------------*/
6
7 FUNCTION get_tbl_sel_clause1 (p_view_by_dim IN VARCHAR2,
8 p_join_tbl IN
9 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
10 RETURN VARCHAR2;
11
12
13 FUNCTION get_tbl_sel_clause2 (p_view_by_dim IN VARCHAR2,
14 p_join_tbl IN
15 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
16 RETURN VARCHAR2;
17
18
19 FUNCTION get_trd_sel_clause(p_view_by_dim IN VARCHAR2)
20 RETURN VARCHAR2;
21
22 FUNCTION get_trd_sel_clause2(p_view_by_dim IN VARCHAR2)
23 RETURN VARCHAR2;
24
25 FUNCTION get_tbl_filter_where1(p_view_by in VARCHAR2) return varchar2;
26
27
28 /*----------------------------------------
29 Receipt to Putaway Cycle Time
30 ----------------------------------------*/
31 PROCEDURE get_tbl_sql1 (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
32 x_custom_sql OUT NOCOPY VARCHAR2,
33 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
34 IS
35 l_query VARCHAR2(32767);
36 l_view_by VARCHAR2(120);
37 l_view_by_col VARCHAR2 (120);
38 l_xtd1 VARCHAR2(10);
39 l_xtd2 VARCHAR2(10);
40 l_comparison_type VARCHAR2(1);
41 l_cur_suffix VARCHAR2(5);
42 l_custom_sql VARCHAR2 (10000);
43 l_subinv_val VARCHAR2 (120) := NULL;
44
45
46 l_col_tbl1 poa_dbi_util_pkg.POA_DBI_COL_TBL;
47 l_col_tbl2 poa_dbi_util_pkg.POA_DBI_COL_TBL;
48 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
49
50 l_where_clause VARCHAR2 (2000);
51 l_where_clause2 VARCHAR2 (2000);
52 l_mv1 VARCHAR2 (30);
53 l_mv2 VARCHAR2 (30);
54
55 l_aggregation_level_flag1 VARCHAR2(10);
56 l_aggregation_level_flag2 VARCHAR2(10);
57
58 l_custom_rec BIS_QUERY_ATTRIBUTES;
59 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
60
61
62 BEGIN
63
64 -- initialization block
65 l_comparison_type := 'Y';
66 l_aggregation_level_flag1 := '0';
67
68 -- clear out the column and Join info tables.
69 l_col_tbl1 := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
70 l_col_tbl2 := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
71 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
72
73 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
74
75
76 -- get all the query parameters for the RTP MV
77 opi_dbi_rpt_util_pkg.process_parameters (
78 p_param => p_param,
79 p_view_by => l_view_by,
80 p_view_by_col_name => l_view_by_col,
81 p_comparison_type => l_comparison_type,
82 p_xtd => l_xtd2,
83 p_cur_suffix => l_cur_suffix,
84 p_where_clause => l_where_clause2,
85 p_mv => l_mv2,
86 p_join_tbl => l_join_tbl,
87 p_mv_level_flag => l_aggregation_level_flag2,
88 p_trend => 'N',
89 p_func_area => 'OPI',
90 p_version => '7.1',
91 p_role => '',
92 p_mv_set => 'RTP',
93 p_mv_flag_type => 'WMS_RTP');
94
95
96 -- Add measure columns that need to be aggregated
97
98 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2,
99 p_col_name => 'c_putaway_quantity' ,
100 p_alias_name => 'putaways',
101 p_grand_total => 'Y',
102 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
103 p_to_date_type => 'RLX');
104
105 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2,
106 p_col_name => 'putaway_quantity' ,
107 p_alias_name => 'qty_putaway',
108 p_grand_total => 'N',
109 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
110 p_to_date_type => 'RLX');
111
112 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2,
113 p_col_name => 'rtp_cycle_time',
114 p_alias_name => 'rtp_cyc_time',
115 p_grand_total => 'Y',
116 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
117 p_to_date_type => 'RLX');
118
119
120
121
122
123 -- get all the query parameters for RTX MV and viewbys other than SUB
124 IF l_view_by <> 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' AND l_where_clause2 not like '%fact.subinventory%' THEN
125 opi_dbi_rpt_util_pkg.process_parameters (
126 p_param => p_param,
127 p_view_by => l_view_by,
128 p_view_by_col_name => l_view_by_col,
129 p_comparison_type => l_comparison_type,
130 p_xtd => l_xtd1,
131 p_cur_suffix => l_cur_suffix,
132 p_where_clause => l_where_clause,
133 p_mv => l_mv1,
134 p_join_tbl => l_join_tbl,
135 p_mv_level_flag => l_aggregation_level_flag1,
136 p_trend => 'N',
137 p_func_area => 'OPI',
138 p_version => '7.1',
139 p_role => '',
140 p_mv_set => 'RTX',
141 p_mv_flag_type => 'WMS_RTX');
142
143 -- Add measure columns that need to be aggregated
144
145
146 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
147 p_col_name => 'transactions_cnt' ,
148 p_alias_name => 'rcv_txns',
149 p_grand_total => 'Y',
150 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
151 p_to_date_type => 'RLX');
152 END IF;
153
154 IF l_view_by = 'ITEM+ENI_ITEM_ORG' THEN
155 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
156 p_col_name => 'quantity' ,
157 p_alias_name => 'quantity_rcv',
158 p_grand_total => 'N',
159 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
160 p_to_date_type => 'RLX');
161 END IF;
162
163
164
165 -- construct the query
166 IF l_view_by <> 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' AND l_where_clause2 not like '%fact.subinventory%' THEN
167 l_mv_tbl.extend;
168 l_mv_tbl(1).mv_name := l_mv1;
169 l_mv_tbl(1).mv_col := l_col_tbl1;
170 l_mv_tbl(1).mv_where := l_where_clause;
171 l_mv_tbl(1).in_join_tbls := NULL;
172 l_mv_tbl(1).use_grp_id := 'N';
173
174 l_mv_tbl.extend;
175 l_mv_tbl(2).mv_name := l_mv2;
176 l_mv_tbl(2).mv_col := l_col_tbl2;
177 l_mv_tbl(2).mv_where := l_where_clause2;
178 l_mv_tbl(2).in_join_tbls := NULL;
179 l_mv_tbl(2).use_grp_id := 'N';
180
181
182 l_query := get_tbl_sel_clause1 (l_view_by, l_join_tbl)
183 || ' from (
184 ' || poa_dbi_template_pkg.union_all_status_sql
185 (p_mv => l_mv_tbl,
186 p_join_tables => l_join_tbl,
187 p_use_windowing => 'Y',
188 p_paren_count => 3,
189 p_filter_where => get_tbl_filter_where1(l_view_by),
190 p_generate_viewby => 'Y');
191
192 ELSE
193 l_query := get_tbl_sel_clause2(l_view_by, l_join_tbl)
194 || ' from
195 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv2,
196 p_where_clause => l_where_clause2,
197 p_join_tables => l_join_tbl,
198 p_use_windowing => 'Y',
199 p_col_name => l_col_tbl2,
200 p_use_grpid => 'N',
201 p_paren_count => 3,
202 p_filter_where => get_tbl_filter_where1(l_view_by),
203 p_generate_viewby => 'Y',
204 p_in_join_tables => NULL);
205
206 END IF;
207 -- prepare output for bind variables
208 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
209 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
210
211 -- set the basic bind variables for the status SQL
212 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
213 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd2);
214 IF l_view_by <> 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' AND l_where_clause2 not like '%fact.subinventory%' THEN
215 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd1);
216 END IF;
217 -- Passing OPI_AGGREGATION_LEVEL_FLAGS to PMV
218 IF l_view_by <> 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' AND l_where_clause2 not like '%fact.subinventory%' THEN
219 l_custom_rec.attribute_name := ':OPI_RTX_AGG_LEVEL_FLAG';
220 l_custom_rec.attribute_value := l_aggregation_level_flag1;
221 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
222 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
223 x_custom_output.extend;
224 x_custom_output(x_custom_output.count) := l_custom_rec;
225 END IF;
226
227 l_custom_rec.attribute_name := ':OPI_RTP_AGG_LEVEL_FLAG';
228 l_custom_rec.attribute_value := l_aggregation_level_flag2;
229 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
230 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
231 x_custom_output.extend;
232
233 x_custom_output(x_custom_output.count) := l_custom_rec;
234
235 x_custom_sql := l_query;
236
237 END get_tbl_sql1;
238
239
240 /*--------------------------------------------------
241 Function: get_tbl_sel_clause1
242 Description: Builds the outer select clause for
243 Receipt to Putaway Cycle Time Report.
244 For viewbys other than subinventory
245 ---------------------------------------------------*/
246
247 FUNCTION get_tbl_sel_clause1(p_view_by_dim IN VARCHAR2,
248 p_join_tbl IN
249 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
250 RETURN VARCHAR2
251 IS
252
253 l_sel_clause VARCHAR2(15000);
254 l_view_by_col_name VARCHAR2(60);
255 l_description VARCHAR2(30);
256 l_drill_across_rep_1 VARCHAR2(50);
257 l_drill_across_rep_2 VARCHAR2(50);
258 l_view_by_fact_col VARCHAR2(400);
259 l_drill_across VARCHAR2(1000);
260 l_inner_qty_rcv_sel_clause VARCHAR2(300);
261 l_inner_qty_put_sel_clause VARCHAR2(300);
262 BEGIN
263
264 -- initialization block
265
266 -- Column to get view by column name
267 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
268 (p_view_by_dim);
269
270
271 -- fact column view by's
272 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
273 (p_join_tbl);
274
275 -- Outer select clause
276 l_sel_clause :=
277 'SELECT
278 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim);
279 IF p_view_by_dim = 'ITEM+ENI_ITEM_ORG' THEN
280 l_sel_clause := l_sel_clause || '
281 v.description OPI_ATTRIBUTE2, --Description
282 v2.description OPI_ATTRIBUTE3, --UOM
283 oset.OPI_MEASURE1 OPI_MEASURE1, -- Quantity Received
284 oset.OPI_MEASURE4 OPI_MEASURE4, -- Quantity Putaway
285 ';
286 l_inner_qty_rcv_sel_clause:= opi_dbi_rpt_util_pkg.nvl_str (
287 p_str => 'c_quantity_rcv',
288 p_default_val => 0);
289 l_inner_qty_put_sel_clause:= opi_dbi_rpt_util_pkg.nvl_str (
290 p_str => 'c_qty_putaway',
291 p_default_val => 0);
292
293 ELSE
294 l_sel_clause := l_sel_clause || '
295 null OPI_ATTRIBUTE2, -- Description
296 null OPI_ATTRIBUTE3, -- UOM
297 null OPI_MEASURE1, -- Quantity Received
298 null OPI_MEASURE4, -- Quantity Putaway
299 ';
300 l_inner_qty_rcv_sel_clause:= ' NULL ';
301 l_inner_qty_put_sel_clause:= ' NULL ';
302
303 END IF;
304
305 l_sel_clause := l_sel_clause ||
306 ' oset.OPI_MEASURE2,
307 oset.OPI_MEASURE3,
308 oset.OPI_MEASURE5, -- Putaways
309 oset.OPI_MEASURE6, -- Change
310 oset.OPI_MEASURE7, -- Receipt to Putaway Cycle Time (Hours)
311 oset.OPI_MEASURE8, -- Change
312 oset.OPI_MEASURE9, -- Total Receiving Transactions
313 oset.OPI_MEASURE10, -- Total Change
314 oset.OPI_MEASURE11, -- Total Putaways
315 oset.OPI_MEASURE12, -- Total Change
316 oset.OPI_MEASURE13, -- Total Receipt to Putaway
317 oset.OPI_MEASURE14, -- Total Change
318 oset.OPI_MEASURE15, -- Total Prior RTP (Hours)
319 oset.OPI_ATTRIBUTE5,
320 oset.OPI_ATTRIBUTE7,
321 oset.OPI_ATTRIBUTE9
322 FROM
323 (SELECT (rank () over
324 (&ORDER_BY_CLAUSE nulls last,
325 ' || l_view_by_fact_col || ')) - 1 rnk,
326 ' || l_view_by_fact_col || ',
327 OPI_MEASURE1,
328 OPI_MEASURE2,
329 OPI_MEASURE3,
330 OPI_MEASURE4,
331 OPI_MEASURE5,
332 OPI_MEASURE6,
333 OPI_MEASURE7,
334 OPI_MEASURE8,
335 OPI_MEASURE9,
336 OPI_MEASURE10,
337 OPI_MEASURE11,
338 OPI_MEASURE12,
339 OPI_MEASURE13,
340 OPI_MEASURE14,
341 OPI_MEASURE15,
342 OPI_ATTRIBUTE5,
343 OPI_ATTRIBUTE7,
344 OPI_ATTRIBUTE9
345 FROM
346 (SELECT
347 ' || l_view_by_fact_col || ',
348 ' || l_inner_qty_rcv_sel_clause
349 || ' OPI_MEASURE1,
350 ' || opi_dbi_rpt_util_pkg.nvl_str (
351 p_str => 'c_rcv_txns',
352 p_default_val => 0)
353 || ' OPI_MEASURE2,
354 ' || opi_dbi_rpt_util_pkg.change_str (
355 p_new_numerator => 'c_rcv_txns',
356 p_old_numerator => 'p_rcv_txns',
357 p_denominator => 'p_rcv_txns',
358 p_measure_name => 'OPI_MEASURE3') || ',
359 ' || l_inner_qty_put_sel_clause
360 || ' OPI_MEASURE4,
361 ' || opi_dbi_rpt_util_pkg.nvl_str (
362 p_str => 'c_putaways',
363 p_default_val => 0)
364 || ' OPI_MEASURE5,
365 ' || opi_dbi_rpt_util_pkg.change_str (
366 p_new_numerator => 'c_putaways',
367 p_old_numerator => 'p_putaways',
368 p_denominator => 'p_putaways',
369 p_measure_name => 'OPI_MEASURE6') || ',
370 ' || opi_dbi_rpt_util_pkg.rate_str (
371 p_numerator => '24*c_rtp_cyc_time',
372 p_denominator => 'c_putaways',
373 p_rate_type => 'NP')
374 || 'OPI_MEASURE7,
375 ' || (opi_dbi_rpt_util_pkg.rate_str (
376 p_numerator => '24*c_rtp_cyc_time',
377 p_denominator => 'c_putaways',
378 p_rate_type => 'NP')) || '-'
379 || (opi_dbi_rpt_util_pkg.rate_str (
380 p_numerator => '24*p_rtp_cyc_time',
381 p_denominator => 'p_putaways',
382 p_rate_type => 'NP'))
383 || ' OPI_MEASURE8,
384 ' || opi_dbi_rpt_util_pkg.nvl_str (
385 p_str => 'c_rcv_txns_total',
386 p_default_val => 0)
387 || ' OPI_MEASURE9,
388 ' || opi_dbi_rpt_util_pkg.change_str (
389 p_new_numerator => 'c_rcv_txns_total',
390 p_old_numerator => 'p_rcv_txns_total',
391 p_denominator => 'p_rcv_txns_total',
392 p_measure_name => 'OPI_MEASURE10') || ',
393 ' || opi_dbi_rpt_util_pkg.nvl_str (
394 p_str => 'c_putaways_total',
395 p_default_val => 0)
396 || ' OPI_MEASURE11,
397 ' || opi_dbi_rpt_util_pkg.change_str (
398 p_new_numerator => 'c_putaways_total',
399 p_old_numerator => 'p_putaways_total',
400 p_denominator => 'p_putaways_total',
401 p_measure_name => 'OPI_MEASURE12') || ',
402 ' || opi_dbi_rpt_util_pkg.rate_str (
403 p_numerator => '24*c_rtp_cyc_time_total',
404 p_denominator => 'c_putaways_total',
405 p_rate_type => 'NP')
406 || 'OPI_MEASURE13,
407 ' || (opi_dbi_rpt_util_pkg.rate_str (
408 p_numerator => '24*c_rtp_cyc_time_total',
409 p_denominator => 'c_putaways_total',
410 p_rate_type => 'NP')) || '-'
411 || (opi_dbi_rpt_util_pkg.rate_str (
412 p_numerator => '24*p_rtp_cyc_time_total',
413 p_denominator => 'p_putaways_total',
414 p_rate_type => 'NP'))
415 || ' OPI_MEASURE14,
416 ' || opi_dbi_rpt_util_pkg.rate_str (
417 p_numerator => '24*p_rtp_cyc_time_total',
418 p_denominator => 'p_putaways_total',
419 p_rate_type => 'NP')
420 || 'OPI_MEASURE15,
421 ' || opi_dbi_rpt_util_pkg.nvl_str (
422 p_str => 'p_rcv_txns',
423 p_default_val => 0)
424 || ' OPI_ATTRIBUTE5,
425 ' || opi_dbi_rpt_util_pkg.nvl_str (
426 p_str => 'p_putaways',
427 p_default_val => 0)
428 || ' OPI_ATTRIBUTE7,
429 ' || opi_dbi_rpt_util_pkg.rate_str (
430 p_numerator => '24*p_rtp_cyc_time',
431 p_denominator => 'p_putaways',
432 p_rate_type => 'NP')
433 || 'OPI_ATTRIBUTE9
434
435 ';
436
437 RETURN l_sel_clause;
438
439 END get_tbl_sel_clause1;
440
441 /*--------------------------------------------------
442 Function: get_tbl_filter_where1
443 Description: Generates a where clause to restrict
444 rows with NA/0 values
445 ---------------------------------------------------*/
446
447 function get_tbl_filter_where1(p_view_by in VARCHAR2) return varchar2
448 is
449 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
450 begin
451 l_col_tbl := poa_dbi_sutil_pkg.poa_dbi_filter_tbl();
452 l_col_tbl.extend;
453 l_col_tbl(1) := 'OPI_MEASURE2';
454 l_col_tbl.extend;
455 l_col_tbl(2) := 'OPI_MEASURE3';
456 l_col_tbl.extend;
457 l_col_tbl(3) := 'OPI_MEASURE5';
458 l_col_tbl.extend;
459 l_col_tbl(4) := 'OPI_MEASURE6';
460 l_col_tbl.extend;
461 l_col_tbl(5) := 'OPI_MEASURE7';
462 l_col_tbl.extend;
463 l_col_tbl(6) := 'OPI_MEASURE8';
464 if(p_view_by = 'ITEM+POA_ITEMS') then
465 l_col_tbl.extend;
466 l_col_tbl(10) := 'OPI_MEASURE1';
467 l_col_tbl.extend;
468 l_col_tbl(10) := 'OPI_MEASURE4';
469 end if;
470 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
471 end;
472
473
474
475
476 /*--------------------------------------------------
477 Function: get_tbl_sel_clause2
478 Description: Builds the outer select clause for
479 Receipt to Putaway Cycle Time Report.
480 For viewby subinventory or when a
481 specific subinventory is chosen
482 ---------------------------------------------------*/
483
484 FUNCTION get_tbl_sel_clause2(p_view_by_dim IN VARCHAR2,
485 p_join_tbl IN
486 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
487 RETURN VARCHAR2
488 IS
489
490 l_sel_clause VARCHAR2(15000);
491 l_view_by_col_name VARCHAR2(60);
492 l_description VARCHAR2(30);
493 l_drill_across_rep_1 VARCHAR2(50);
494 l_drill_across_rep_2 VARCHAR2(50);
495 l_view_by_fact_col VARCHAR2(400);
496 l_drill_across VARCHAR2(1000);
497 l_inner_qty_rcv_sel_clause VARCHAR2(300);
498 l_inner_qty_put_sel_clause VARCHAR2(300);
499
500 BEGIN
501
502 -- initialization block
503
504 -- Column to get view by column name
505 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
506 (p_view_by_dim);
507
508
509 -- fact column view by's
510 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
511 (p_join_tbl);
512
513 -- Outer select clause
514 l_sel_clause :=
515 'SELECT
516 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim);
517
518 IF p_view_by_dim = 'ITEM+ENI_ITEM_ORG' THEN
519 l_sel_clause := l_sel_clause || '
520 v.description OPI_ATTRIBUTE2, --Description
521 v2.description OPI_ATTRIBUTE3, --UOM
522 null OPI_MEASURE1, -- Quantity Received
523 oset.OPI_MEASURE4 OPI_MEASURE4, -- Quantity Putaway
524 ';
525 l_inner_qty_rcv_sel_clause:= ' NULL ';
526 l_inner_qty_put_sel_clause:= opi_dbi_rpt_util_pkg.nvl_str (
527 p_str => 'c_qty_putaway',
528 p_default_val => 0);
529
530 ELSE
531 l_sel_clause := l_sel_clause || '
532 null OPI_ATTRIBUTE2, -- Description
533 null OPI_ATTRIBUTE3, -- UOM
534 null OPI_MEASURE1, -- Quantity Received
535 null OPI_MEASURE4, -- Quantity Putaway
536 ';
537 l_inner_qty_rcv_sel_clause:= ' NULL ';
538 l_inner_qty_put_sel_clause:= ' NULL ';
539
540 END IF;
541
542 l_sel_clause := l_sel_clause || '
543 oset.OPI_MEASURE2, -- Receiving Transactions
544 oset.OPI_MEASURE3, -- Change
545 oset.OPI_MEASURE5, -- Putaways
546 oset.OPI_MEASURE6, -- Change
547 oset.OPI_MEASURE7, -- Receipt to Putaway Cycle Time (Hours)
548 oset.OPI_MEASURE8, -- Change
549 oset.OPI_MEASURE9, -- Total Receiving Transactions
550 oset.OPI_MEASURE10, -- Total Change
551 oset.OPI_MEASURE11, -- Total Putaways
552 oset.OPI_MEASURE12, -- Total Change
553 oset.OPI_MEASURE13, -- Total Receipt to Putaway
554 oset.OPI_MEASURE14, -- Total Change
555 oset.OPI_MEASURE15, -- Total Prior RTP(Hours)
556 oset.OPI_ATTRIBUTE5,
557 oset.OPI_ATTRIBUTE7,
558 oset.OPI_ATTRIBUTE9
559 FROM
560 (SELECT (rank () over
561 (&ORDER_BY_CLAUSE nulls last,
562 ' || l_view_by_fact_col || ')) - 1 rnk,
563 ' || l_view_by_fact_col || ',
564 OPI_MEASURE1,
565 OPI_MEASURE4,
566 OPI_MEASURE2,
567 OPI_MEASURE3,
568 OPI_MEASURE5,
569 OPI_MEASURE6,
570 OPI_MEASURE7,
571 OPI_MEASURE8,
572 OPI_MEASURE9,
573 OPI_MEASURE10,
574 OPI_MEASURE11,
575 OPI_MEASURE12,
576 OPI_MEASURE13,
577 OPI_MEASURE14,
578 OPI_MEASURE15,
579 OPI_ATTRIBUTE5,
580 OPI_ATTRIBUTE7,
581 OPI_ATTRIBUTE9
582 FROM
583 (SELECT
584 ' || l_view_by_fact_col || ',
585 ' || l_inner_qty_rcv_sel_clause
586 || ' OPI_MEASURE1,
587 null OPI_MEASURE2,
588 null OPI_MEASURE3,
589 ' || l_inner_qty_put_sel_clause
590 || ' OPI_MEASURE4,
591 ' || opi_dbi_rpt_util_pkg.nvl_str (
592 p_str => 'c_putaways',
593 p_default_val => 0)
594 || ' OPI_MEASURE5,
595 ' || opi_dbi_rpt_util_pkg.change_str (
596 p_new_numerator => 'c_putaways',
597 p_old_numerator => 'p_putaways',
598 p_denominator => 'p_putaways',
599 p_measure_name => 'OPI_MEASURE6') || ',
600 ' || opi_dbi_rpt_util_pkg.rate_str (
601 p_numerator => '24*c_rtp_cyc_time',
602 p_denominator => 'c_putaways',
603 p_rate_type => 'NP')
604 || 'OPI_MEASURE7,
605 ' || (opi_dbi_rpt_util_pkg.rate_str (
606 p_numerator => '24*c_rtp_cyc_time',
607 p_denominator => 'c_putaways',
608 p_rate_type => 'NP')) || '-'
609 || (opi_dbi_rpt_util_pkg.rate_str (
610 p_numerator => '24*p_rtp_cyc_time',
611 p_denominator => 'p_putaways',
612 p_rate_type => 'NP'))
613 || ' OPI_MEASURE8,
614 null OPI_MEASURE9,
615 null OPI_MEASURE10,
616 ' || opi_dbi_rpt_util_pkg.nvl_str (
617 p_str => 'c_putaways_total',
618 p_default_val => 0)
619 || ' OPI_MEASURE11,
620 ' || opi_dbi_rpt_util_pkg.change_str (
621 p_new_numerator => 'c_putaways_total',
622 p_old_numerator => 'p_putaways_total',
623 p_denominator => 'p_putaways_total',
624 p_measure_name => 'OPI_MEASURE12') || ',
625 ' || opi_dbi_rpt_util_pkg.rate_str (
626 p_numerator => '24*c_rtp_cyc_time_total',
627 p_denominator => 'c_putaways_total',
628 p_rate_type => 'NP')
629 || 'OPI_MEASURE13,
630 ' || (opi_dbi_rpt_util_pkg.rate_str (
631 p_numerator => '24*c_rtp_cyc_time_total',
632 p_denominator => 'c_putaways_total',
633 p_rate_type => 'NP')) || '-'
634 || (opi_dbi_rpt_util_pkg.rate_str (
635 p_numerator => '24*p_rtp_cyc_time_total',
636 p_denominator => 'p_putaways_total',
637 p_rate_type => 'NP'))
638 || ' OPI_MEASURE14,
639 ' || opi_dbi_rpt_util_pkg.rate_str (
640 p_numerator => '24*p_rtp_cyc_time_total',
641 p_denominator => 'p_putaways_total',
642 p_rate_type => 'NP')
643 || 'OPI_MEASURE15,
644 null OPI_ATTRIBUTE5,
645 ' || opi_dbi_rpt_util_pkg.nvl_str (
646 p_str => 'p_putaways',
647 p_default_val => 0)
648 || ' OPI_ATTRIBUTE7,
649
650 ' || opi_dbi_rpt_util_pkg.rate_str (
651 p_numerator => '24*p_rtp_cyc_time',
652 p_denominator => 'p_putaways',
653 p_rate_type => 'NP')
654 || 'OPI_ATTRIBUTE9
655
656 ';
657 RETURN l_sel_clause;
658
659 END get_tbl_sel_clause2;
660
661
662
663
664 /*----------------------------------------
665 Receipt to Putaway Cycle Time Trend
666 ----------------------------------------*/
667
668 PROCEDURE get_trd_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
669 x_custom_sql OUT NOCOPY VARCHAR2,
670 x_custom_output OUT NOCOPY
671 BIS_QUERY_ATTRIBUTES_TBL)
672 IS
673 l_query VARCHAR2(32767);
674 l_view_by VARCHAR2(120);
675 l_view_by_col VARCHAR2 (120);
676 l_xtd1 VARCHAR2(10);
677 l_xtd2 VARCHAR2(10);
678 l_comparison_type VARCHAR2(1);
679 l_cur_suffix VARCHAR2(5);
680 l_custom_sql VARCHAR2 (10000);
681
682 l_col_tbl1 poa_dbi_util_pkg.POA_DBI_COL_TBL;
683 l_col_tbl2 poa_dbi_util_pkg.POA_DBI_COL_TBL;
684 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
685 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
686 l_where_clause VARCHAR2 (2000);
687 l_where_clause2 VARCHAR2 (2000);
688 l_mv1 VARCHAR2 (30);
689 l_mv2 VARCHAR2 (30);
690
691 l_aggregation_level_flag1 VARCHAR2(10);
692 l_aggregation_level_flag2 VARCHAR2(10);
693
694 l_custom_rec BIS_QUERY_ATTRIBUTES;
695 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
696
697 BEGIN
698
699 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
700
701 -- initialization block
702 l_comparison_type := 'Y';
703 l_aggregation_level_flag1 := '0';
704
705 -- clear out the tables.
706 l_col_tbl1 := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
707 l_col_tbl2 := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
708 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
709
710 -- get all the query parameters for putaway side
711
712
713 opi_dbi_rpt_util_pkg.process_parameters (
714 p_param => p_param,
715 p_view_by => l_view_by,
716 p_view_by_col_name => l_view_by_col,
717 p_comparison_type => l_comparison_type,
718 p_xtd => l_xtd2,
719 p_cur_suffix => l_cur_suffix,
720 p_where_clause => l_where_clause2,
721 p_mv => l_mv2,
722 p_join_tbl => l_join_tbl,
723 p_mv_level_flag =>l_aggregation_level_flag2,
724 p_trend => 'Y',
725 p_func_area => 'OPI',
726 p_version => '7.1',
727 p_role => '',
728 p_mv_set => 'RTP',
729 p_mv_flag_type => 'WMS_RTP');
730
731 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2,
732 p_col_name => 'c_putaway_quantity' ,
733 p_alias_name => 'putaways',
734 p_grand_total => 'N',
735 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
736 p_to_date_type => 'RLX');
737
738
739 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2,
740 p_col_name => 'rtp_cycle_time',
741 p_alias_name => 'rtp_cyc_time',
742 p_grand_total => 'N',
743 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
744 p_to_date_type => 'RLX');
745
746
747 -- get all the query parameters for receiving side
748
749 IF l_where_clause2 not like '%fact.subinventory%' THEN
750 opi_dbi_rpt_util_pkg.process_parameters (
751 p_param => p_param,
752 p_view_by => l_view_by,
753 p_view_by_col_name => l_view_by_col,
754 p_comparison_type => l_comparison_type,
755 p_xtd => l_xtd1,
756 p_cur_suffix => l_cur_suffix,
757 p_where_clause => l_where_clause,
758 p_mv => l_mv1,
759 p_join_tbl => l_join_tbl,
760 p_mv_level_flag =>l_aggregation_level_flag1,
761 p_trend => 'Y',
762 p_func_area => 'OPI',
763 p_version => '7.1',
764 p_role => '',
765 p_mv_set => 'RTX',
766 p_mv_flag_type => 'WMS_RTX');
767 -- Add measure columns that need to be aggregated
768 -- No Grand totals required.
769 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
770 p_col_name => 'transactions_cnt' ,
771 p_alias_name => 'rcv_txns',
772 p_grand_total => 'N',
773 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
774 p_to_date_type => 'RLX');
775 END IF;
776
777
778 -- Merge Outer and Inner Query
779 IF l_where_clause2 not like '%fact.subinventory%' THEN
780
781 l_mv_tbl.extend;
782 l_mv_tbl(1).mv_name := l_mv1;
783 l_mv_tbl(1).mv_col := l_col_tbl1;
784 l_mv_tbl(1).mv_where := l_where_clause;
785 l_mv_tbl(1).in_join_tbls := NULL;
786 l_mv_tbl(1).use_grp_id := 'N';
787 l_mv_tbl(1).mv_hint := poa_dbi_sutil_pkg.get_fact_hint(l_mv1);
788 l_mv_tbl(1).mv_xtd := l_xtd1;
789
790 l_mv_tbl.extend;
791 l_mv_tbl(2).mv_name := l_mv2;
792 l_mv_tbl(2).mv_col := l_col_tbl2;
793 l_mv_tbl(2).mv_where := l_where_clause2;
794 l_mv_tbl(2).in_join_tbls := NULL;
795 l_mv_tbl(2).use_grp_id := 'N';
796 l_mv_tbl(2).mv_hint := poa_dbi_sutil_pkg.get_fact_hint(l_mv2);
797 l_mv_tbl(2).mv_xtd := l_xtd2;
798
799 l_query := get_trd_sel_clause(l_view_by) ||
800 ' from ' ||
801 poa_dbi_template_pkg.union_all_trend_sql(
802 p_mv => l_mv_tbl,
803 p_comparison_type => l_comparison_type,
804
805 p_filter_where => NULL
806 );
807
808 ELSE
809 l_query := get_trd_sel_clause2(l_view_by) ||
810 ' from ' ||
811 poa_dbi_template_pkg.trend_sql(
812 p_xtd => l_xtd2,
813 p_comparison_type => l_comparison_type,
814 p_fact_name => l_mv2,
815 p_where_clause => l_where_clause2,
816 p_col_name => l_col_tbl2,
817 p_use_grpid => 'N',
818 p_in_join_tables => NULL,
819 p_fact_hint => poa_dbi_sutil_pkg.get_fact_hint(l_mv2)
820
821 );
822 END IF;
823
824 -- Prepare PMV bind variables
825 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
826 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
827
828 -- get all the basic binds used by POA queries
829 -- Do this before adding any of our binds, since the procedure
830 -- reinitializes the output table
831 poa_dbi_util_pkg.get_custom_trend_binds (
832 p_xtd => l_xtd2,
833 p_comparison_type => l_comparison_type,
834 x_custom_output => x_custom_output);
835 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd2);
836 -- Passing OPI_AGGREGATION_LEVEL_FLAG to PMV
837 IF l_where_clause2 not like '%fact.subinventory%' THEN
838 l_custom_rec.attribute_name := ':OPI_RTX_AGG_LEVEL_FLAG';
839 l_custom_rec.attribute_value := l_aggregation_level_flag1;
840 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
841 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
842 x_custom_output.extend;
843 x_custom_output(x_custom_output.count) := l_custom_rec;
844
845 END IF;
846
847 l_custom_rec.attribute_name := ':OPI_RTP_AGG_LEVEL_FLAG';
848 l_custom_rec.attribute_value := l_aggregation_level_flag2;
849 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
850 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
851 x_custom_output.extend;
852
853 x_custom_output(x_custom_output.count) := l_custom_rec;
854
855 x_custom_sql := l_query;
856
857 END get_trd_sql;
858
859 /*--------------------------------------------------
860 Function: get_trd_sel_clause
861 Description: Builds the outer select clause for
862 Receipt to Putaway Cycle Time Trend
863 Report when viewing all subinventories
864 ---------------------------------------------------*/
865
866 FUNCTION get_trd_sel_clause (p_view_by_dim IN VARCHAR2)
867 RETURN VARCHAR2
868 IS
869
870 l_sel_clause varchar2(7500);
871
872 BEGIN
873
874 -- Main Outer query
875
876 l_sel_clause :=
877 'SELECT
878 ' || ' cal_name VIEWBY,
879 ' || opi_dbi_rpt_util_pkg.nvl_str (
880 p_str => 'p_rcv_txns',
881 p_default_val => 0)
882 || ' OPI_ATTRIBUTE5,
883 ' || opi_dbi_rpt_util_pkg.nvl_str (
884 p_str => 'c_rcv_txns',
885 p_default_val => 0)
886 || ' OPI_MEASURE1,
887 ' || opi_dbi_rpt_util_pkg.change_str (
888 p_new_numerator => 'c_rcv_txns',
889 p_old_numerator => 'p_rcv_txns',
890 p_denominator => 'p_rcv_txns',
891 p_measure_name => 'OPI_MEASURE2') || ',
892 ' || opi_dbi_rpt_util_pkg.nvl_str (
893 p_str => 'p_putaways',
894 p_default_val => 0)
895 || ' OPI_ATTRIBUTE6,
896 ' || opi_dbi_rpt_util_pkg.nvl_str (
897 p_str => 'c_putaways',
898 p_default_val => 0)
899 || ' OPI_MEASURE3,
900 ' || opi_dbi_rpt_util_pkg.change_str (
901 p_new_numerator => 'c_putaways',
902 p_old_numerator => 'p_putaways',
903 p_denominator => 'p_putaways',
904 p_measure_name => 'OPI_MEASURE4') || ',
905 ' || opi_dbi_rpt_util_pkg.rate_str (
906 p_numerator => '24*p_rtp_cyc_time',
907 p_denominator => 'p_putaways',
908 p_rate_type => 'NP')
909 || 'OPI_ATTRIBUTE7,
910 ' || opi_dbi_rpt_util_pkg.rate_str (
911 p_numerator => '24*c_rtp_cyc_time',
912 p_denominator => 'c_putaways',
913 p_rate_type => 'NP')
914 || 'OPI_MEASURE5,
915 ' || (opi_dbi_rpt_util_pkg.rate_str (
916 p_numerator => '24*c_rtp_cyc_time',
917 p_denominator => 'c_putaways',
918 p_rate_type => 'NP')) || '-'
919 || (opi_dbi_rpt_util_pkg.rate_str (
920 p_numerator => '24*p_rtp_cyc_time',
921 p_denominator => 'p_putaways',
922 p_rate_type => 'NP'))
923 || ' OPI_MEASURE6'
924 ;
925 RETURN l_sel_clause;
926
927 END get_trd_sel_clause;
928
929 /*--------------------------------------------------
930 Function: get_trd_sel_clause2
931 Description: Builds the outer select clause for
932 Receipt to Putaway Cycle Time Trend
933 Report when a specific subinventory
934 is selected
935 ---------------------------------------------------*/
936
937 FUNCTION get_trd_sel_clause2 (p_view_by_dim IN VARCHAR2)
938 RETURN VARCHAR2
939 IS
940
941 l_sel_clause varchar2(7500);
942
943 BEGIN
944
945 -- Main Outer query
946
947 l_sel_clause :=
948 'SELECT
949 ' || ' cal.name VIEWBY,
950 null OPI_ATTRIBUTE5,
951 null OPI_MEASURE1,
952 null OPI_MEASURE2,
953 ' || opi_dbi_rpt_util_pkg.nvl_str (
954 p_str => 'p_putaways',
955 p_default_val => 0)
956 || ' OPI_ATTRIBUTE6,
957 ' || opi_dbi_rpt_util_pkg.nvl_str (
958 p_str => 'c_putaways',
959 p_default_val => 0)
960 || ' OPI_MEASURE3,
961 ' || opi_dbi_rpt_util_pkg.change_str (
962 p_new_numerator => 'c_putaways',
963 p_old_numerator => 'p_putaways',
964 p_denominator => 'p_putaways',
965 p_measure_name => 'OPI_MEASURE4') || ',
966 ' || opi_dbi_rpt_util_pkg.rate_str (
967 p_numerator => '24*p_rtp_cyc_time',
968 p_denominator => 'p_putaways',
969 p_rate_type => 'NP')
970 || 'OPI_ATTRIBUTE7,
971 ' || opi_dbi_rpt_util_pkg.rate_str (
972 p_numerator => '24*c_rtp_cyc_time',
973 p_denominator => 'c_putaways',
974 p_rate_type => 'NP')
975 || 'OPI_MEASURE5,
976 ' || (opi_dbi_rpt_util_pkg.rate_str (
977 p_numerator => '24*c_rtp_cyc_time',
978 p_denominator => 'c_putaways',
979 p_rate_type => 'NP')) || '-'
980 || (opi_dbi_rpt_util_pkg.rate_str (
981 p_numerator => '24*p_rtp_cyc_time',
982 p_denominator => 'p_putaways',
983 p_rate_type => 'NP'))
984 || ' OPI_MEASURE6';
985 RETURN l_sel_clause;
986
987 END get_trd_sel_clause2;
988 END opi_dbi_wms_rtp_pkg;