[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_INV_MGMT_RPT_PKG
Source
1 PACKAGE BODY OPI_DBI_INV_MGMT_RPT_PKG AS
2 /*$Header: OPIDRINVMB.pls 120.2 2005/09/21 03:51:16 srayadur noship $ */
3
4
5 /*++++++++++++++++++++++++++++++++++++++++*/
6 /* Function and procedure declarations in this file but not in spec*/
7 /*++++++++++++++++++++++++++++++++++++++++*/
8
9 /* Inventory Value Report */
10
11 FUNCTION get_inv_val_status_sel_clause (p_view_by_dim IN VARCHAR2,
12 p_join_tbl IN
13 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
14 RETURN VARCHAR2;
15
16 FUNCTION get_onhand_sel_clause (p_view_by_dim IN VARCHAR2,
17 p_join_tbl IN
18 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
19 RETURN VARCHAR2;
20
21 FUNCTION get_intransit_sel_clause (p_view_by_dim IN VARCHAR2,
22 p_join_tbl IN
23 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
24 RETURN VARCHAR2;
25
26 PROCEDURE get_inv_val_item_columns (p_dim_name VARCHAR2,
27 p_description OUT NOCOPY VARCHAR2,
28 p_uom OUT NOCOPY VARCHAR2);
29
30
31 /* Inventory Value Trend Report */
32
33 FUNCTION get_inv_val_trend_sel_clause(p_view_by_dim IN VARCHAR2)
34 return VARCHAR2;
35
36
37
38
39 /*----------------------------------------
40 Inventory Value Report Functions
41 ----------------------------------------*/
42
43
44
45 /*
46 Report query Function for viewby = Item, Org, Cat.
47 */
48 PROCEDURE inv_val_status_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
49 x_custom_sql OUT NOCOPY VARCHAR2,
50 x_custom_output OUT NOCOPY
51 BIS_QUERY_ATTRIBUTES_TBL)
52 IS
53 l_query VARCHAR2(15000);
54 l_view_by VARCHAR2(120);
55 l_view_by_col VARCHAR2 (120);
56 l_xtd VARCHAR2(10);
57 l_comparison_type VARCHAR2(1);
58 l_cur_suffix VARCHAR2(5);
59 l_custom_sql VARCHAR2 (10000);
60
61 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
62 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
63
64 l_where_clause VARCHAR2 (2000);
65 l_mv VARCHAR2 (30);
66
67 l_aggregation_level_flag varchar2(1);
68
69 l_custom_rec BIS_QUERY_ATTRIBUTES;
70 l_filter_where VARCHAR2(120);
71
72 BEGIN
73
74 -- initialization block
75 l_comparison_type := 'Y';
76 l_aggregation_level_flag := '0';
77
78 -- clear out the tables.
79 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
80 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
81
82
83 -- get all the query parameters
84 opi_dbi_rpt_util_pkg.process_parameters (p_param => p_param,
85 p_view_by => l_view_by,
86 p_view_by_col_name => l_view_by_col,
87 p_comparison_type => l_comparison_type,
88 p_xtd => l_xtd,
89 p_cur_suffix => l_cur_suffix,
90 p_where_clause => l_where_clause,
91 p_mv => l_mv,
92 p_join_tbl => l_join_tbl,
93 p_mv_level_flag => l_aggregation_level_flag,
94 p_trend => 'N',
95 p_func_area => 'OPI',
96 p_version => '7.0',
97 p_role => '',
98 p_mv_set => 'INV_VAL',
99 p_mv_flag_type => 'INV_VAL_LEVEL');
100
101 -- The measure columns that need to be aggregated are
102 -- onhand_value_<b/g>, intransit_value_<b/g>,
103 -- wip_value_<b/g>, inv_total_value_<b/g>
104 poa_dbi_util_pkg.add_column (l_col_tbl,
105 'onhand_value_' || l_cur_suffix,
106 'onhand_value');
107
108 poa_dbi_util_pkg.add_column (l_col_tbl,
109 'intransit_value_' || l_cur_suffix,
110 'intransit_value');
111
112 poa_dbi_util_pkg.add_column (l_col_tbl,
113 'wip_value_' || l_cur_suffix,
114 'wip_value');
115
116 poa_dbi_util_pkg.add_column (l_col_tbl,
117 'inv_total_value_' || l_cur_suffix,
118 'inv_total_value');
119
120 --Add filtering condition to suppress rows
121 l_filter_where := 'abs(OPI_MEASURE7) > 0 or abs(OPI_MEASURE8) > 0';
122
123 -- construct the query
124 l_query := get_inv_val_status_sel_clause (l_view_by, l_join_tbl)
125 || ' from
126 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
127 p_where_clause => l_where_clause,
128 p_join_tables => l_join_tbl,
129 p_use_windowing => 'Y',
130 p_col_name => l_col_tbl,
131 p_use_grpid => 'N',
132 p_filter_where => l_filter_where);
133
134 -- prepare output for bind variables
135 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
136 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
137
138 -- set the basic bind variables for the status SQL
139 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
140
141 -- Passing OPI_AGGREGATION_LEVEL_FLAG to PMV
142 l_custom_rec.attribute_name := ':OPI_AGGREGATION_LEVEL_FLAG';
143 l_custom_rec.attribute_value := l_aggregation_level_flag;
144 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
145 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
146 x_custom_output.extend;
147 x_custom_output(x_custom_output.count) := l_custom_rec;
148
149 -- make the nested pattern ITD since inv. value is reported as a balance,
150 -- not an XTD value.
151 l_query := replace (l_query, '&BIS_NESTED_PATTERN', '1143');
152
153 x_custom_sql := l_query;
154
155 END inv_val_status_sql;
156
157
158 /*
159 Outer main query for viewby = item, org, cat
160 */
161
162 FUNCTION get_inv_val_status_sel_clause(p_view_by_dim IN VARCHAR2,
163 p_join_tbl IN
164 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
165 return VARCHAR2
166 IS
167
168 l_sel_clause varchar2(4500);
169 l_view_by_col_name varchar2(60);
170 l_description varchar2(30);
171 l_uom varchar2(30);
172 l_view_by_fact_col VARCHAR2(400);
173
174 BEGIN
175
176 -- Main Outer query
177
178 -- Column to get view by column name
179 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
180 (p_view_by_dim);
181
182 -- fact column view by's
183 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
184 (p_join_tbl);
185
186 -- Description for item view by
187 get_inv_val_item_columns (p_view_by_dim, l_description, l_uom);
188
189 -- Outer select clause
190 l_sel_clause :=
191 'SELECT
192 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
193 || l_view_by_col_name || ' OPI_ATTRIBUTE1,
194 ' || l_description || ' OPI_ATTRIBUTE2,
195 ' || 'oset.OPI_MEASURE1,
196 ' || 'oset.OPI_MEASURE2,
197 ' || 'oset.OPI_MEASURE3,
198 ' || 'oset.OPI_MEASURE4,
199 ' || 'oset.OPI_MEASURE5,
200 ' || 'oset.OPI_MEASURE6,
201 ' || 'oset.OPI_MEASURE7,
202 ' || 'oset.OPI_MEASURE8,
203 ' || 'oset.OPI_MEASURE9,
204 ' || 'oset.OPI_MEASURE10,
205 ' || 'oset.OPI_MEASURE11,
206 ' || 'oset.OPI_MEASURE12,
207 ' || 'oset.OPI_MEASURE13,
208 ' || 'oset.OPI_MEASURE14,
209 ' || 'oset.OPI_MEASURE15,
210 ' || 'oset.OPI_MEASURE16,
211 ' || 'oset.OPI_MEASURE17,
212 ' || 'oset.OPI_MEASURE18,
213 ' || 'oset.OPI_MEASURE19,
214 ' || 'oset.OPI_MEASURE20,
215 ' || 'oset.OPI_MEASURE21,
216 ' || 'oset.OPI_MEASURE22,
217 ' || 'oset.OPI_MEASURE23,
218 ' || 'oset.OPI_DYNAMIC_URL_1,
219 ' || 'oset.OPI_DYNAMIC_URL_2
220 ' || 'FROM
221 ' || '(SELECT (rank () over
222 ' || ' (&ORDER_BY_CLAUSE nulls last,
223 ' || l_view_by_fact_col || ')) - 1 rnk,
224 ' || l_view_by_fact_col || ',
225 ' || 'OPI_MEASURE1,
226 ' || 'OPI_MEASURE2,
227 ' || 'OPI_MEASURE3,
228 ' || 'OPI_MEASURE4,
229 ' || 'OPI_MEASURE5,
230 ' || 'OPI_MEASURE6,
231 ' || 'OPI_MEASURE7,
232 ' || 'OPI_MEASURE8,
233 ' || 'OPI_MEASURE9,
234 ' || 'OPI_MEASURE10,
235 ' || 'OPI_MEASURE11,
236 ' || 'OPI_MEASURE12,
237 ' || 'OPI_MEASURE13,
238 ' || 'OPI_MEASURE14,
239 ' || 'OPI_MEASURE15,
240 ' || 'OPI_MEASURE16,
241 ' || 'OPI_MEASURE17,
242 ' || 'OPI_MEASURE18,
243 ' || 'OPI_MEASURE19,
244 ' || 'OPI_MEASURE20,
245 ' || 'OPI_MEASURE21,
246 ' || 'OPI_MEASURE22,
247 ' || 'OPI_MEASURE23,
248 ' || 'OPI_DYNAMIC_URL_1,
249 ' || 'OPI_DYNAMIC_URL_2
250 ' || 'FROM
251 ' || '(SELECT
252 ' || l_view_by_fact_col || ',
253 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_onhand_value')
254 || ' OPI_MEASURE1,
255 ' || opi_dbi_rpt_util_pkg.change_str ('c_onhand_value',
256 'p_onhand_value',
257 'p_onhand_value',
258 'OPI_MEASURE2') || ',
259 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_intransit_value')
260 || ' OPI_MEASURE3,
261 ' || opi_dbi_rpt_util_pkg.change_str ('c_intransit_value',
262 'p_intransit_value',
263 'p_intransit_value',
264 'OPI_MEASURE4') || ',
265 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_wip_value')
266 || ' OPI_MEASURE5,
267 ' || opi_dbi_rpt_util_pkg.change_str ('c_wip_value',
268 'p_wip_value',
269 'p_wip_value',
270 'OPI_MEASURE6') || ',
271 ' || opi_dbi_rpt_util_pkg.nvl_str ('p_inv_total_value')
272 || ' OPI_MEASURE7,
273 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_inv_total_value')
274 || ' OPI_MEASURE8,
275 ' || opi_dbi_rpt_util_pkg.change_str ('c_inv_total_value',
276 'p_inv_total_value',
277 'p_inv_total_value',
278 'OPI_MEASURE9') || ',
279 ' || opi_dbi_rpt_util_pkg.percent_str ('c_inv_total_value',
280 'c_inv_total_value_total',
281 'OPI_MEASURE10') || ',
282 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_onhand_value_total')
283 || ' OPI_MEASURE11,
284 ' || opi_dbi_rpt_util_pkg.change_str ('c_onhand_value_total',
285 'p_onhand_value_total',
286 'p_onhand_value_total',
287 'OPI_MEASURE12') || ',
288 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_intransit_value_total')
289 || ' OPI_MEASURE13,
290 ' || opi_dbi_rpt_util_pkg.change_str ('c_intransit_value_total',
291 'p_intransit_value_total',
292 'p_intransit_value_total',
293 'OPI_MEASURE14') || ',
294 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_wip_value_total')
295 || ' OPI_MEASURE15,
296 ' || opi_dbi_rpt_util_pkg.change_str ('c_wip_value_total',
297 'p_wip_value_total',
298 'p_wip_value_total',
299 'OPI_MEASURE16') || ',
300 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_inv_total_value_total')
301 || ' OPI_MEASURE17,
302 ' || opi_dbi_rpt_util_pkg.change_str ('c_inv_total_value_total',
303 'p_inv_total_value_total',
304 'p_inv_total_value_total',
305 'OPI_MEASURE18') || ',
306 ' || opi_dbi_rpt_util_pkg.percent_str ('c_inv_total_value_total',
307 'c_inv_total_value_total',
308 'OPI_MEASURE19') || ',
309 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_inv_total_value')
310 || ' OPI_MEASURE20,
311 ' || opi_dbi_rpt_util_pkg.nvl_str ('p_inv_total_value')
312 || ' OPI_MEASURE21,
313 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_inv_total_value_total')
314 || ' OPI_MEASURE22,
315 ' || opi_dbi_rpt_util_pkg.nvl_str ('p_inv_total_value_total')
316 || ' OPI_MEASURE23,
317 ' || '''pFunctionName=OPI_DBI_INV_ONH_ORG_TBL_REP&VIEW_BY_NAME=VIEW_BY_VALUE&VIEW_BY=' || p_view_by_dim || ''' OPI_DYNAMIC_URL_1,
321 RETURN l_sel_clause;
318 ' || '''pFunctionName=OPI_DBI_INV_INT_ORG_TBL_REP&VIEW_BY_NAME=VIEW_BY_VALUE&VIEW_BY=' || p_view_by_dim || ''' OPI_DYNAMIC_URL_2 ';
319
320
322
323 END get_inv_val_status_sel_clause;
324
325
326 /*
327 For viewby = item_org, the inventory value report has to display
328 a description and unit of measure
329 */
330 PROCEDURE get_inv_val_item_columns (p_dim_name VARCHAR2,
331 p_description OUT NOCOPY VARCHAR2,
332 p_uom OUT NOCOPY VARCHAR2)
333
334 IS
335 l_description varchar2(30);
336 l_uom varchar2(30);
337
338 BEGIN
339 CASE
340 WHEN p_dim_name = 'ITEM+ENI_ITEM_ORG' THEN
341 BEGIN
342 p_description := 'v.description';
343 p_uom := 'v2.unit_of_measure';
344 END;
345 ELSE
346 BEGIN
347 p_description := 'null';
348 p_uom := 'null';
349 END;
350 END CASE;
351
352 END get_inv_val_item_columns;
353
354
355
356 /*----------------------------------------
357 Inventory Value Report Functions
358 ----------------------------------------*/
359
360
361 /*
362 Report query for viewby = time
363 */
364
365 /*
366 Report query for viewby = Time
367 */
368 PROCEDURE inv_val_trend_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
369 x_custom_sql OUT NOCOPY VARCHAR2,
370 x_custom_output OUT NOCOPY
371 BIS_QUERY_ATTRIBUTES_TBL)
372 IS
373 l_query VARCHAR2(15000);
374 l_view_by VARCHAR2(120);
375 l_view_by_col VARCHAR2 (120);
376 l_xtd varchar2(10);
377 l_comparison_type VARCHAR2(1);
378 l_cur_suffix VARCHAR2(5);
379 l_custom_sql VARCHAR2(4000);
380 l_mv VARCHAR2 (30);
381 l_where_clause VARCHAR2 (4000);
382
383 l_aggregation_level_flag VARCHAR2(1);
384
385 l_custom_rec BIS_QUERY_ATTRIBUTES;
386
387 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
388 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
389
390 BEGIN
391
392 -- initialization block
393 l_comparison_type := 'Y';
394 l_where_clause := '';
395 l_aggregation_level_flag := '0';
396
397 -- clear out the tables.
398 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
399 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
400
401 -- get all the query parameters
402 opi_dbi_rpt_util_pkg.process_parameters (p_param => p_param,
403 p_view_by => l_view_by,
404 p_view_by_col_name => l_view_by_col,
405 p_comparison_type => l_comparison_type,
406 p_xtd => l_xtd,
407 p_cur_suffix => l_cur_suffix,
408 p_where_clause => l_where_clause,
409 p_mv => l_mv,
410 p_join_tbl => l_join_tbl,
411 p_mv_level_flag => l_aggregation_level_flag,
412 p_trend => 'Y',
413 p_func_area => 'OPI',
414 p_version => '7.0',
415 p_role => '',
416 p_mv_set => 'INV_VAL',
417 p_mv_flag_type => 'INV_VAL_LEVEL');
418
419 -- The measure columns that need to be aggregated are
420 -- onhand_value_<b/g>, intransit_value_<b/g>,
421 -- wip_value_<b/g>, inv_total_value_<b/g>
422 -- No Grand totals required.
423
424 poa_dbi_util_pkg.add_column (l_col_tbl,
425 'onhand_value_' || l_cur_suffix,
426 'onhand_value',
427 'N');
428
429 poa_dbi_util_pkg.add_column (l_col_tbl,
430 'intransit_value_' || l_cur_suffix,
431 'intransit_value',
432 'N');
433
434 poa_dbi_util_pkg.add_column (l_col_tbl,
435 'wip_value_' || l_cur_suffix,
436 'wip_value',
437 'N');
438
439 poa_dbi_util_pkg.add_column (l_col_tbl,
440 'inv_total_value_' || l_cur_suffix,
441 'inv_total_value',
442 'N');
443
444
445 -- Joining Outer and Inner Query
446 l_query := get_inv_val_trend_sel_clause(l_view_by) ||
447 ' from ' ||
448 poa_dbi_template_pkg.trend_sql (
449 l_xtd,
450 l_comparison_type,
451 l_mv,
452 l_where_clause,
453 l_col_tbl,
454 'N');
455
456
457
461
458 -- Prepare PMV bind variables
459 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
460 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
462 -- get all the basic binds used by POA queries
463 -- Do this before adding any of our binds, since the procedure
464 -- reinitializes the output table
465 poa_dbi_util_pkg.get_custom_trend_binds (l_xtd, l_comparison_type,
466 x_custom_output);
467
468 -- Passing OPI_AGGREGATION_LEVEL_FLAG to PMV
469 l_custom_rec.attribute_name := ':OPI_AGGREGATION_LEVEL_FLAG';
470 l_custom_rec.attribute_value := l_aggregation_level_flag;
471 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
472 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
473 x_custom_output.extend;
474 x_custom_output(x_custom_output.count) := l_custom_rec;
475
476 -- make the nested pattern ITD since inv. value is reported as a balance,
477 -- not an XTD value.
478 l_query := replace (l_query, '&BIS_NESTED_PATTERN', '1143');
479
480 x_custom_sql := l_query;
481
482 x_custom_sql := l_query;
483
484
485 END inv_val_trend_sql;
486
487 /*
488 The outer main query for the trend SQL.
489 */
490 FUNCTION get_inv_val_trend_sel_clause (p_view_by_dim IN VARCHAR2)
491 RETURN VARCHAR2
492 IS
493
494 l_sel_clause varchar2(4500);
495
496 BEGIN
497
498 -- Main Outer query
499
500 l_sel_clause :=
501 'SELECT
502 ' || ' cal.name VIEWBY,
503 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_onhand_value')
504 || ' OPI_MEASURE1,
505 ' || opi_dbi_rpt_util_pkg.change_str ('iset.c_onhand_value',
506 'iset.p_onhand_value',
507 'iset.p_onhand_value',
508 'OPI_MEASURE2') || ',
509 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_intransit_value')
510 || ' OPI_MEASURE3,
511 ' || opi_dbi_rpt_util_pkg.change_str ('iset.c_intransit_value',
512 'iset.p_intransit_value',
513 'iset.p_intransit_value',
514 'OPI_MEASURE4') || ',
515 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_wip_value')
516 || ' OPI_MEASURE5,
517 ' || opi_dbi_rpt_util_pkg.change_str ('iset.c_wip_value',
518 'iset.p_wip_value',
519 'iset.p_wip_value',
520 'OPI_MEASURE6') || ',
521 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.p_inv_total_value')
522 || ' OPI_MEASURE8,
523 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_inv_total_value')
524 || ' OPI_MEASURE7,
525 ' || opi_dbi_rpt_util_pkg.change_str ('iset.c_inv_total_value',
526 'iset.p_inv_total_value',
527 'iset.p_inv_total_value',
528 'OPI_MEASURE9') ; --OPI Measure 9 is added for bug 3570094
529
530 RETURN l_sel_clause;
531
532 END get_inv_val_trend_sel_clause;
533
534 PROCEDURE onhand_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
535 x_custom_sql OUT NOCOPY VARCHAR2,
536 x_custom_output OUT NOCOPY
537 BIS_QUERY_ATTRIBUTES_TBL)
538 IS
539 l_query VARCHAR2(15000);
540 l_view_by VARCHAR2(120);
541 l_view_by_col VARCHAR2 (120);
542 l_xtd VARCHAR2(10);
543 l_comparison_type VARCHAR2(1);
544 l_cur_suffix VARCHAR2(5);
545 l_custom_sql VARCHAR2 (10000);
546
547 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
548 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
549
550 l_where_clause VARCHAR2 (2000);
551 l_mv VARCHAR2 (30);
552
553 l_aggregation_level_flag varchar2(1);
554 l_custom_rec BIS_QUERY_ATTRIBUTES;
555 l_filter_where VARCHAR2(120);
556
557 BEGIN
558
559 -- initialization block
560 l_comparison_type := 'Y';
561 l_aggregation_level_flag := '0';
562
563 -- clear out the tables.
564 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
565 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
566
567
568 -- get all the query parameters
569 opi_dbi_rpt_util_pkg.process_parameters (p_param => p_param,
570 p_view_by => l_view_by,
571 p_view_by_col_name => l_view_by_col,
572 p_comparison_type => l_comparison_type,
573 p_xtd => l_xtd,
574 p_cur_suffix => l_cur_suffix,
575 p_where_clause => l_where_clause,
576 p_mv => l_mv,
577 p_join_tbl => l_join_tbl,
581 p_version => '7.0',
578 p_mv_level_flag => l_aggregation_level_flag,
579 p_trend => 'N',
580 p_func_area => 'OPI',
582 p_role => '',
583 p_mv_set => 'ONH',
584 p_mv_flag_type => 'INV_VAL_LEVEL');
585
586 -- The measure columns that need to be aggregated are
587 -- onhand_value_<b/g>, onhand_qty,
588 poa_dbi_util_pkg.add_column (l_col_tbl,
589 'onhand_value_' || l_cur_suffix,
590 'onhand_value');
591
592 poa_dbi_util_pkg.add_column (l_col_tbl,
593 'onhand_qty',
594 'onhand_qty');
595
596 --Add filtering condition to suppress rows
597 l_filter_where := 'abs(OPI_MEASURE1) > 0 ' ||
598 'OR abs(OPI_MEASURE4) > 0 ' ||
599 'OR abs(OPI_MEASURE5) > 0';
600
601 -- construct the query
602 l_query := get_onhand_sel_clause (l_view_by, l_join_tbl)
603 || ' from
604 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
605 p_where_clause => l_where_clause,
606 p_join_tables => l_join_tbl,
607 p_use_windowing => 'Y',
608 p_col_name => l_col_tbl,
609 p_use_grpid => 'N',
610 p_filter_where => l_filter_where);
611
612 -- prepare output for bind variables
613 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
614 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
615
616 -- set the basic bind variables for the status SQL
617 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
618
619 -- Passing OPI_AGGREGATION_LEVEL_FLAG to PMV
620 l_custom_rec.attribute_name := ':OPI_AGGREGATION_LEVEL_FLAG';
621 l_custom_rec.attribute_value := l_aggregation_level_flag;
622 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
623 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
624 x_custom_output.extend;
625 x_custom_output(x_custom_output.count) := l_custom_rec;
626
627 -- make the nested pattern ITD since inv. value is reported as a balance,
628 -- not an XTD value.
629 l_query := replace (l_query, '&BIS_NESTED_PATTERN', '1143');
630
631 x_custom_sql := l_query;
632
633 END onhand_sql;
634
635 FUNCTION get_onhand_sel_clause(p_view_by_dim IN VARCHAR2,
636 p_join_tbl IN
637 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
638 return VARCHAR2
639 IS
640
641 l_sel_clause varchar2(4500);
642 l_view_by_col_name varchar2(60);
643 l_description varchar2(30);
644 l_uom varchar2(30);
645 l_view_by_fact_col VARCHAR2(400);
646 l_drill_url_1 VARCHAR2 (500);
647
648 BEGIN
649
650 -- Main Outer query
651
652 -- Column to get view by column name
653 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
654 (p_view_by_dim);
655 -- Description for item view by
656 get_inv_val_item_columns (p_view_by_dim, l_description, l_uom);
657
658 -- fact column view by's
659 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
660 (p_join_tbl);
661
662 -- Drill across URL, for viewby Item, is AsOfDate = trunc (sysdate)
663 l_drill_url_1 := 'NULL';
664 IF (p_view_by_dim = 'ITEM+ENI_ITEM_ORG') THEN
665 -- {
666 l_drill_url_1 := ' decode (&BIS_CURRENT_ASOF_DATE,
667 trunc (sysdate), ''pFunctionName=OPI_DBI_INV_CURR_STS_TBL_REP&VIEW_BY_NAME=VIEW_BY_VALUE&VIEW_BY=' || 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL' || ''', NULL) ';
668 -- }
669 END IF;
670
671 -- Outer select clause
672 l_sel_clause :=
673 'SELECT
674 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
675 || l_view_by_col_name || ' OPI_ATTRIBUTE1,
676 ' || l_description || ' OPI_ATTRIBUTE2,
677 ' || l_uom || ' OPI_ATTRIBUTE3,
678 ' || 'oset.OPI_MEASURE1,
679 ' || 'oset.OPI_MEASURE2,
680 ' || 'oset.OPI_MEASURE3,
681 ' || 'oset.OPI_MEASURE4,
682 ' || 'oset.OPI_MEASURE5,
683 ' || 'oset.OPI_MEASURE6,
684 ' || 'oset.OPI_MEASURE7,
685 ' || 'oset.OPI_MEASURE8,
686 ' || 'oset.OPI_MEASURE9,
687 ' || 'oset.OPI_MEASURE10,
688 ' || 'OPI_DYNAMIC_URL_1
689 ' || 'FROM
690 ' || '(SELECT (rank () over
691 ' || ' (&ORDER_BY_CLAUSE nulls last,
692 ' || l_view_by_fact_col || ')) - 1 rnk,
693 ' || l_view_by_fact_col || ',
694 ' || 'OPI_MEASURE1,
695 ' || 'OPI_MEASURE2,
696 ' || 'OPI_MEASURE3,
697 ' || 'OPI_MEASURE4,
698 ' || 'OPI_MEASURE5,
699 ' || 'OPI_MEASURE6,
700 ' || 'OPI_MEASURE7,
701 ' || 'OPI_MEASURE8,
705 ' || 'FROM
702 ' || 'OPI_MEASURE9,
703 ' || 'OPI_MEASURE10,
704 ' || 'OPI_DYNAMIC_URL_1
706 ' || '(SELECT
707 ' || l_view_by_fact_col || ',
708 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_onhand_qty')
709 || ' OPI_MEASURE1,
710 ' || opi_dbi_rpt_util_pkg.nvl_str ('p_onhand_qty')
711 || ' OPI_MEASURE2,
712 ' || opi_dbi_rpt_util_pkg.change_str ('c_onhand_qty',
713 'p_onhand_qty',
714 'p_onhand_qty',
715 'OPI_MEASURE3') || ',
716 ' || opi_dbi_rpt_util_pkg.nvl_str ('p_onhand_value')
717 || ' OPI_MEASURE4,
718 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_onhand_value')
719 || ' OPI_MEASURE5,
720 ' || opi_dbi_rpt_util_pkg.change_str ('c_onhand_value',
721 'p_onhand_value',
722 'p_onhand_value',
723 'OPI_MEASURE6') || ',
724 ' || opi_dbi_rpt_util_pkg.percent_str ('c_onhand_value',
725 'c_onhand_value_total',
726 'OPI_MEASURE7') || ',
727 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_onhand_value_total')
728 || ' OPI_MEASURE8,
729 ' || opi_dbi_rpt_util_pkg.change_str ('c_onhand_value_total',
730 'p_onhand_value_total',
731 'p_onhand_value_total',
732 'OPI_MEASURE9') || ',
733 ' || opi_dbi_rpt_util_pkg.percent_str ('c_onhand_value_total',
734 'c_onhand_value_total',
735 'OPI_MEASURE10') || ',
736 ' || l_drill_url_1 || ' OPI_DYNAMIC_URL_1 ';
737
738 RETURN l_sel_clause;
739
740 END get_onhand_sel_clause;
741
742 PROCEDURE intransit_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
743 x_custom_sql OUT NOCOPY VARCHAR2,
744 x_custom_output OUT NOCOPY
745 BIS_QUERY_ATTRIBUTES_TBL)
746 IS
747 l_query VARCHAR2(15000);
748 l_view_by VARCHAR2(120);
749 l_view_by_col VARCHAR2 (120);
750 l_xtd VARCHAR2(10);
751 l_comparison_type VARCHAR2(1);
752 l_cur_suffix VARCHAR2(5);
753 l_custom_sql VARCHAR2 (10000);
754
755 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
756 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
757
758 l_where_clause VARCHAR2 (2000);
759 l_mv VARCHAR2 (30);
760
761 l_aggregation_level_flag varchar2(1);
762 l_custom_rec BIS_QUERY_ATTRIBUTES;
763 l_filter_where VARCHAR2(120);
764
765 BEGIN
766
767 -- initialization block
768 l_comparison_type := 'Y';
769 l_aggregation_level_flag := '0';
770
771 -- clear out the tables.
772 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
773 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
774
775
776 -- get all the query parameters
777 opi_dbi_rpt_util_pkg.process_parameters (p_param => p_param,
778 p_view_by => l_view_by,
779 p_view_by_col_name => l_view_by_col,
780 p_comparison_type => l_comparison_type,
781 p_xtd => l_xtd,
782 p_cur_suffix => l_cur_suffix,
783 p_where_clause => l_where_clause,
784 p_mv => l_mv,
785 p_join_tbl => l_join_tbl,
786 p_mv_level_flag => l_aggregation_level_flag,
787 p_trend => 'N',
788 p_func_area => 'OPI',
789 p_version => '7.0',
790 p_role => '',
791 p_mv_set => 'INT',
792 p_mv_flag_type => 'INV_VAL_LEVEL');
793
794 -- The measure columns that need to be aggregated are
795 -- onhand_value_<b/g>, onhand_qty,
796 poa_dbi_util_pkg.add_column (l_col_tbl,
797 'intransit_value_' || l_cur_suffix,
798 'intransit_value');
799
800 poa_dbi_util_pkg.add_column (l_col_tbl,
801 'intransit_qty',
802 'intransit_qty');
803
804 --Add filtering condition to suppress rows
805 l_filter_where := 'abs(OPI_MEASURE1) > 0 ' ||
806 'OR abs(OPI_MEASURE4) > 0 ' ||
807 'OR abs(OPI_MEASURE5) > 0';
808
809 -- construct the query
810 l_query := get_intransit_sel_clause (l_view_by, l_join_tbl)
811 || ' from
812 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
813 p_where_clause => l_where_clause,
814 p_join_tables => l_join_tbl,
815 p_use_windowing => 'Y',
816 p_col_name => l_col_tbl,
817 p_use_grpid => 'N',
818 p_filter_where => l_filter_where);
819
820 -- prepare output for bind variables
821 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
822 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
823
824 -- set the basic bind variables for the status SQL
825 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
826
827 -- Passing OPI_AGGREGATION_LEVEL_FLAG to PMV
828 l_custom_rec.attribute_name := ':OPI_AGGREGATION_LEVEL_FLAG';
829 l_custom_rec.attribute_value := l_aggregation_level_flag;
830 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
831 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
832 x_custom_output.extend;
833 x_custom_output(x_custom_output.count) := l_custom_rec;
834
835 -- make the nested pattern ITD since inv. value is reported as a balance,
836 -- not an XTD value.
837 l_query := replace (l_query, '&BIS_NESTED_PATTERN', '1143');
838
839 x_custom_sql := l_query;
840
841 END intransit_sql;
842
843 FUNCTION get_intransit_sel_clause(p_view_by_dim IN VARCHAR2,
844 p_join_tbl IN
845 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
846 return VARCHAR2
847 IS
848
849 l_sel_clause varchar2(4500);
850 l_view_by_col_name varchar2(60);
851 l_description varchar2(30);
852 l_uom varchar2(30);
853 l_view_by_fact_col VARCHAR2(400);
854
855 BEGIN
856
857 -- Main Outer query
858
859 -- Column to get view by column name
860 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
861 (p_view_by_dim);
862
863 -- Description for item view by
864 get_inv_val_item_columns (p_view_by_dim, l_description, l_uom);
865
866 -- fact column view by's
867 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
868 (p_join_tbl);
869
870 -- Outer select clause
871 l_sel_clause :=
872 'SELECT
873 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
874 || l_view_by_col_name || ' OPI_ATTRIBUTE1,
875 ' || l_description || ' OPI_ATTRIBUTE2,
876 ' || l_uom || ' OPI_ATTRIBUTE3,
877 ' || 'oset.OPI_MEASURE1,
878 ' || 'oset.OPI_MEASURE2,
879 ' || 'oset.OPI_MEASURE3,
880 ' || 'oset.OPI_MEASURE4,
881 ' || 'oset.OPI_MEASURE5,
882 ' || 'oset.OPI_MEASURE6,
883 ' || 'oset.OPI_MEASURE7,
884 ' || 'oset.OPI_MEASURE8,
885 ' || 'oset.OPI_MEASURE9,
886 ' || 'oset.OPI_MEASURE10
887 ' || 'FROM
888 ' || '(SELECT (rank () over
889 ' || ' (&ORDER_BY_CLAUSE nulls last,
890 ' || l_view_by_fact_col || ')) - 1 rnk,
891 ' || l_view_by_fact_col || ',
892 ' || 'OPI_MEASURE1,
893 ' || 'OPI_MEASURE2,
894 ' || 'OPI_MEASURE3,
895 ' || 'OPI_MEASURE4,
896 ' || 'OPI_MEASURE5,
897 ' || 'OPI_MEASURE6,
898 ' || 'OPI_MEASURE7,
899 ' || 'OPI_MEASURE8,
900 ' || 'OPI_MEASURE9,
901 ' || 'OPI_MEASURE10
902 ' || 'FROM
903 ' || '(SELECT
904 ' || l_view_by_fact_col || ',
905 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_intransit_qty')
906 || ' OPI_MEASURE1,
907 ' || opi_dbi_rpt_util_pkg.nvl_str ('p_intransit_qty')
908 || ' OPI_MEASURE2,
912 'OPI_MEASURE3') || ',
909 ' || opi_dbi_rpt_util_pkg.change_str ('c_intransit_qty',
910 'p_intransit_qty',
911 'p_intransit_qty',
913 ' || opi_dbi_rpt_util_pkg.nvl_str ('p_intransit_value')
914 || ' OPI_MEASURE4,
915 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_intransit_value')
916 || ' OPI_MEASURE5,
917 ' || opi_dbi_rpt_util_pkg.change_str ('c_intransit_value',
918 'p_intransit_value',
919 'p_intransit_value',
920 'OPI_MEASURE6') || ',
921 ' || opi_dbi_rpt_util_pkg.percent_str ('c_intransit_value',
922 'c_intransit_value_total',
923 'OPI_MEASURE7') || ',
924 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_intransit_value_total')
925 || ' OPI_MEASURE8,
926 ' || opi_dbi_rpt_util_pkg.change_str ('c_intransit_value_total',
927 'p_intransit_value_total',
928 'p_intransit_value_total',
929 'OPI_MEASURE9') || ',
930 ' || opi_dbi_rpt_util_pkg.percent_str ('c_intransit_value_total',
931 'c_intransit_value_total',
932 'OPI_MEASURE10');
933
934 RETURN l_sel_clause;
935
936 END get_intransit_sel_clause;
937
938 /*
939 Report query Function for viewby = Item, Org, Cat.
940 */
941 PROCEDURE inv_val_type_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
942 x_custom_sql OUT NOCOPY VARCHAR2,
943 x_custom_output OUT NOCOPY
944 BIS_QUERY_ATTRIBUTES_TBL)
945 IS
946 -- {
947 l_query VARCHAR2(15000);
948 l_view_by VARCHAR2(120);
949 l_view_by_col VARCHAR2 (120);
950 l_xtd VARCHAR2(10);
951 l_comparison_type VARCHAR2(1);
952 l_cur_suffix VARCHAR2(5);
953 l_custom_sql VARCHAR2 (32767);
954 l_viewby_rank_clause VARCHAR2 (32767);
955
956 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
957 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
958
959
960 l_where_clause VARCHAR2 (2000);
961 l_mv VARCHAR2 (30);
962
963 l_aggregation_level_flag varchar2(1);
964
965 l_custom_rec BIS_QUERY_ATTRIBUTES;
966 l_filter_where VARCHAR2(120);
967 -- }
968 BEGIN
969 -- {
970 -- initialization block
971 l_comparison_type := 'Y';
972 l_aggregation_level_flag := '0';
973
974 -- clear out the tables.
975 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
976 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
977
978
979 -- get all the query parameters
980 opi_dbi_rpt_util_pkg.process_parameters (p_param => p_param,
981 p_view_by => l_view_by,
982 p_view_by_col_name => l_view_by_col,
983 p_comparison_type => l_comparison_type,
984 p_xtd => l_xtd,
985 p_cur_suffix => l_cur_suffix,
986 p_where_clause => l_where_clause,
987 p_mv => l_mv,
988 p_join_tbl => l_join_tbl,
989 p_mv_level_flag => l_aggregation_level_flag,
990 p_trend => 'N',
991 p_func_area => 'OPI',
992 p_version => '7.0',
993 p_role => '',
994 p_mv_set => 'INV_VAL',
995 p_mv_flag_type => 'INV_VAL_LEVEL');
996
997 -- Since this query is pretty straightforward, define most of it
998 -- here. The only thing that will be returned from the OPI report
999 -- query template is the where clause. The POA template is not
1000 -- directly needed.
1001 l_query := '
1002 select
1003 inventory_type OPI_ATTRIBUTE1,
1004 ' || opi_dbi_rpt_util_pkg.nvl_str ('c_value') ||
1005 ' OPI_MEASURE1,
1006 ' || opi_dbi_rpt_util_pkg.change_str ('c_value',
1007 'p_value',
1008 'p_value',
1009 'OPI_MEASURE2') || '
1010 from
1011 (select
1012 fnd.meaning inventory_type,
1013 sum (decode (fnd.lookup_code,
1014 ''ONH'', oset.c_onhand_value,
1015 ''INT'', oset.c_intransit_value,
1016 ''WIP'', oset.c_wip_value)) c_value,
1017 sum (decode (fnd.lookup_code,
1018 ''ONH'', oset.p_onhand_value,
1019 ''INT'', oset.p_intransit_value,
1020 ''WIP'', oset.p_wip_value)) p_value
1021 ' ||
1022 ' from
1023 (select
1024 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1025 onhand_value_'
1026 || l_cur_suffix || ', null)) c_onhand_value,
1027 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
1028 onhand_value_'
1029 || l_cur_suffix || ', null)) p_onhand_value,
1030 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1031 intransit_value_'
1032 || l_cur_suffix || ', null)) c_intransit_value,
1033 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
1034 intransit_value_'
1035 || l_cur_suffix || ', null)) p_intransit_value,
1036 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
1037 wip_value_'
1038 || l_cur_suffix || ', null)) c_wip_value,
1039 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
1040 wip_value_'
1041 || l_cur_suffix || ', null)) p_wip_value
1042 from ' || l_mv || ' fact, fii_time_rpt_struct_v cal
1043 where fact.time_id = cal.time_id
1044 ' || l_where_clause || '
1045 and cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
1046 and bitand(cal.record_type_id, 1143) = cal.record_type_id
1047 ) oset,
1048 fnd_lookup_values_vl fnd
1049 where fnd.lookup_type = ''OPI_DBI_INV_TYPE''
1050 -- and fnd.language = USERENV(''LANG'')
1051 group by fnd.meaning) oset2
1052 &ORDER_BY_CLAUSE nulls last';
1053
1054
1055 -- prepare output for bind variables
1056 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1057 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1058
1059 -- set the basic bind variables for the status SQL
1060 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
1061
1062 -- Passing OPI_AGGREGATION_LEVEL_FLAG to PMV
1063 l_custom_rec.attribute_name := ':OPI_AGGREGATION_LEVEL_FLAG';
1064 l_custom_rec.attribute_value := l_aggregation_level_flag;
1065 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1066 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1067 x_custom_output.extend;
1068 x_custom_output(x_custom_output.count) := l_custom_rec;
1069
1070 -- make the nested pattern ITD since inv. value is reported as a balance,
1071 -- not an XTD value.
1072 -- l_query := replace (l_query, '&BIS_NESTED_PATTERN', '1143');
1073
1074 x_custom_sql := l_query;
1075 -- }
1076 END inv_val_type_sql;
1077
1078 END OPI_DBI_INV_MGMT_RPT_PKG;