[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_INV_RPT_PKG
Source
1 PACKAGE BODY OPI_DBI_INV_RPT_PKG AS
2 /* $Header: OPIDRINVRB.pls 120.1 2005/08/10 04:00:54 srayadur noship $ */
3
4 /* PMV date mask */
5 s_pmv_date_mask VARCHAR2 (15) := '''DD-MM-RR'''; -- No more used bug 3580454
6
7 /* Local functions */
8
9 FUNCTION get_turns_sel_clause (p_view_by_col_name in VARCHAR2)
10 RETURN VARCHAR2;
11
12 /* inner part of dynamic SQL for Inventory Turns table portlet */
13 FUNCTION turns_status_inner_sql(p_as_of_date in varchar2,
14 p_prev_as_of_date in varchar2,
15 p_xtd in varchar2,
16 p_curr in varchar2,
17 p_comparison_type in varchar2,
18 p_view_by_col_name in varchar2,
19 p_view_by_col_id in varchar2,
20 p_fact_name in varchar2,
21 p_where_clause in varchar2,
22 p_in_where_clause in varchar2,
23 p_kpi_group_by in varchar2,
24 p_kpi_in_group_by in varchar2,
25 p_view_by_dim in varchar2,
26 p_use_windowing in varchar2,
27 p_col_name in OPI_DBI_COL_TBL,
28 p_total_col_name in OPI_DBI_COL_TBL)
29 RETURN VARCHAR2;
30
31
32 FUNCTION get_turns_trd_sel_clause RETURN VARCHAR2;
33
34 FUNCTION turns_trd_inner_sql (p_as_of_date in varchar2,
35 p_prev_as_of_date in varchar2,
36 p_curr in varchar2,
37 p_xtd in varchar2,
38 p_comparison_type in varchar2,
39 p_fact_name in varchar2,
40 p_where_clause in varchar2,
41 p_in_where_clause in varchar2,
42 p_col_name in OPI_DBI_COL_TBL,
43 p_total_col_name in OPI_DBI_COL_TBL)
44 RETURN VARCHAR2;
45
46
47
48 Function get_view_by_table (dim_name varchar2) RETURN VARCHAR2;
49
50
51 PROCEDURE get_parameter_values(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
52 p_dim_in_tbl in OPI_DBI_DIM_TBL,
53 p_dim_out_tbl out NOCOPY OPI_DBI_DIM_TBL,
54 p_view_by out NOCOPY VARCHAR2,
55 p_comparison_type out NOCOPY VARCHAR2,
56 p_xtd out NOCOPY VARCHAR2,
57 p_as_of_date out NOCOPY DATE,
58 p_prev_as_of_date out NOCOPY DATE,
59 p_cur_suffix out NOCOPY VARCHAR2,
60 p_nested_pattern out NOCOPY NUMBER);
61
62
63 Function get_calendar_table(period_type varchar2) return varchar2;
64
65 Function get_nested_pattern(period_type varchar2) return number;
66
67 /****************************
68 Inventory Turns Report
69 *****************************/
70
71 PROCEDURE inv_turns_tbl_sql(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
72 x_custom_sql OUT NOCOPY VARCHAR2,
73 x_custom_output OUT NOCOPY
74 BIS_QUERY_ATTRIBUTES_TBL)
75 IS
76 l_query varchar2(8000);
77 l_view_by varchar2(120);
78 l_as_of_date date;
79 l_prev_as_of_date date;
80 l_org varchar2(100);
81 l_plant varchar2(100);
82 l_category varchar2(2000);
83 l_item varchar2(2000);
84 l_xtd varchar2(10);
85 l_comparison_type varchar2(1) := 'Y';
86 l_nested_pattern number;
87 l_org_where varchar2(240);
88 l_in_org_where varchar2(240);
89 l_kpi_group_by varchar2(40) := '';
90 l_kpi_in_group_by varchar2(40) := '';
91 l_category_where varchar2(120);
92 l_item_where varchar2(120);
93 l_cur_suffix varchar2(5);
94 l_rev_amount varchar2(25);
95 l_cogs_amount varchar2(25);
96 l_dim_in_tbl OPI_DBI_DIM_TBL;
97 l_dim_out_tbl OPI_DBI_DIM_TBL;
98 l_col_rec OPI_DBI_COL_REC;
99 l_col_tbl OPI_DBI_COL_TBL;
100 l_total_col_tbl OPI_DBI_COL_TBL;
101 l_plant_where varchar2(240);
102 l_in_plant_where varchar2(240);
103
104 BEGIN
105
106 /* figure out what parameters we need for filtering and map them with available page paramters from PMV */
107 l_dim_in_tbl := OPI_DBI_DIM_TBL('ORGANIZATION+ORGANIZATION');
108
109 get_parameter_values(p_param, l_dim_in_tbl, l_dim_out_tbl, l_view_by, l_comparison_type, l_xtd, l_as_of_date, l_prev_as_of_date, l_cur_suffix, l_nested_pattern);
110
111 l_plant := l_dim_out_tbl(1);
112
113 l_col_tbl := OPI_DBI_COL_TBL();
114 l_total_col_tbl := OPI_DBI_COL_TBL();
115
116 /* construct the list of measures to be appended to the select clause of the dynamic SQL */
117 l_col_rec.column_name := 'total_inv_val_' || l_cur_suffix;
118 l_col_rec.column_alias := 'total_inv_val';
119 l_col_tbl.extend;
120 l_col_tbl(l_col_tbl.count) := l_col_rec;
121
122 l_col_rec.column_name := 'cogs_val_' || l_cur_suffix;
123 l_col_rec.column_alias := 'cogs_value';
124 l_col_tbl.extend;
125 l_col_tbl(l_col_tbl.count) := l_col_rec;
126
127 l_col_rec.column_name := 'total_inv_val_' || l_cur_suffix;
128 l_col_rec.column_alias := 'total_inv_val_total';
129 l_total_col_tbl.extend;
130 l_total_col_tbl(l_total_col_tbl.count) := l_col_rec;
131
132 l_col_rec.column_name := 'cogs_val_' || l_cur_suffix;
133 l_col_rec.column_alias := 'cogs_val_total';
134 l_total_col_tbl.extend;
135 l_total_col_tbl(l_total_col_tbl.count) := l_col_rec;
136
137 /* construct where caluse of the dynamic SQL based on chosen page parameter values */
138 /*
139 if(l_org is null or l_org = '') then
140 l_org_where := ' and fact.operating_unit_id in (select organization_id from per_organization_list where security_profile_id= nvl(fnd_profile.value(''XLA_MO_SECURITY_PROFILE_LEVEL''), -1) ) ';
141 else
142 l_org_where := ' and fact.operating_unit_id = &ORGANIZATION+FII_OPERATING_UNITS ';
143 end if;
144
145 if(l_org is null or l_org = '') then
146 l_in_org_where := ' and insv.operating_unit_id in (select organization_id from per_organization_list where security_profile_id= nvl(fnd_profile.value(''XLA_MO_SECURITY_PROFILE_LEVEL''), -1) ) ';
147 else
148 l_in_org_where := ' and insv.operating_unit_id = &ORGANIZATION+FII_OPERATING_UNITS ';
149 end if;
150 */
151
152 if(l_plant is null or l_plant = '' or l_plant = 'All') then
153 l_plant_where := '';
154 l_in_plant_where := '';
155 else
156 l_plant_where := ' and fact.organization_id in (' || l_plant || ') ';
157 l_in_plant_where := ' and insv.organization_id in (' || l_plant || ') ';
158 end if;
159
160 l_query :=
161 get_turns_sel_clause('organization_id') || ' from
162 ' || turns_status_inner_sql(l_as_of_date,
163 l_prev_as_of_date,
164 l_xtd,
165 l_cur_suffix,
166 l_comparison_type,
167 'organization_id',
168 'id',
169 'opi_dbi_inv_turns_f',
170 l_plant_where ,
171 l_in_org_where ,
172 l_kpi_group_by ,
173 l_kpi_in_group_by ,
174 'INV_ORG',
175 'N',
176 l_col_tbl,
177 l_total_col_tbl);
178
179
180 -- empty the output table because we have no bind values to pass back
181 x_custom_output := bis_query_attributes_tbl();
182
183 -- return the query
184 x_custom_sql := l_query;
185
186 END inv_turns_tbl_sql;
187
188
189 /* Outer select clause of Inventory Turns table portlet query */
190
191 FUNCTION get_turns_sel_clause (p_view_by_col_name in VARCHAR2)
192 RETURN VARCHAR2
193 IS
194
195 l_sel_clause varchar2(7000);
196
197 BEGIN
198
199 l_sel_clause :=
200 'select v.value VIEWBY, v.value OPI_ATTRIBUTE1, oset.OPI_MEASURE1 OPI_MEASURE1,
201 oset.OPI_MEASURE2 OPI_MEASURE2, oset.OPI_MEASURE3 OPI_MEASURE3,
202 oset.OPI_MEASURE4 OPI_MEASURE4, oset.OPI_MEASURE5 OPI_MEASURE5,
203 oset.OPI_MEASURE6 OPI_MEASURE6, oset.OPI_MEASURE7 OPI_MEASURE7,
204 oset.OPI_MEASURE8 OPI_MEASURE8, oset.OPI_MEASURE9 OPI_MEASURE9,
205 oset.OPI_MEASURE10 OPI_MEASURE10, oset.OPI_MEASURE11 OPI_MEASURE11,
206 oset.OPI_MEASURE12 OPI_MEASURE12, oset.OPI_MEASURE13 OPI_MEASURE13
207 from
208 (select (rank() over
209 (&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col_name || ')) - 1 rnk,'
210 || p_view_by_col_name || ',
211 OPI_MEASURE1, OPI_MEASURE2, OPI_MEASURE3, OPI_MEASURE4,
212 OPI_MEASURE5, OPI_MEASURE6, OPI_MEASURE7, OPI_MEASURE8,
213 OPI_MEASURE9, OPI_MEASURE10, OPI_MEASURE11, OPI_MEASURE12, OPI_MEASURE13
214 from
215 (select ' || p_view_by_col_name || ', ' || p_view_by_col_name || ' VIEW_BY,
216 c_total_inv_val OPI_MEASURE1, c_cogs_val OPI_MEASURE2,
217 round(decode(sign(p_total_inv_val), 0, NULL, -1, NULL, p_cogs_val/p_total_inv_val),5) OPI_MEASURE3,
218 round(decode(sign(c_total_inv_val), 0, NULL, -1, NULL, c_cogs_val/c_total_inv_val),5) OPI_MEASURE4,
219 round((decode(sign(c_total_inv_val), 0, NULL, -1, NULL, c_cogs_val/c_total_inv_val) - decode(p_total_inv_val, 0, NULL, p_cogs_val/p_total_inv_val)),5) OPI_MEASURE5,
220 c_total_inv_val_total OPI_MEASURE6, c_cogs_val_total OPI_MEASURE7,
221 decode(sign(c_total_inv_val_total), 0, NULL,-1,NULL,
222 (c_cogs_val_total / c_total_inv_val_total)) OPI_MEASURE8,
223 decode(sign(c_total_inv_val_total), 0, NULL, -1, NULL,
224 decode(sign(p_total_inv_val_total), 0, NULL, -1, NULL,
225 ((c_cogs_val_total / c_total_inv_val_total) -
226 (p_cogs_val_total / p_total_inv_val_total)))) OPI_MEASURE9,
227 round(decode(sign(c_total_inv_val), 0, NULL, -1, NULL, c_cogs_val/c_total_inv_val),5) OPI_MEASURE10,
228 round(decode(sign(p_total_inv_val), 0, NULL, -1, NULL, p_cogs_val/p_total_inv_val),5) OPI_MEASURE11,
229 decode(sign(c_total_inv_val_total), 0, NULL,-1,NULL,
230 (c_cogs_val_total / c_total_inv_val_total)) OPI_MEASURE12,
231 decode(sign(p_total_inv_val_total), 0, NULL,-1,NULL,
232 (p_cogs_val_total / p_total_inv_val_total)) OPI_MEASURE13 from
233 (select c.' || p_view_by_col_name || ' ,
234 c.total_inv_val c_total_inv_val , c.cogs_val c_cogs_val ,
235 p.total_inv_val p_total_inv_val , p.cogs_val p_cogs_val ,
236 sum(c.total_inv_val) over () c_total_inv_val_total,
237 sum(c.cogs_val) over () c_cogs_val_total,
238 sum(p.total_inv_val) over () p_total_inv_val_total,
239 sum(p.cogs_val) over () p_cogs_val_total';
240
241 return l_sel_clause;
242
243 END get_turns_sel_clause;
244
245
246 /* inner part of dynamic SQL for Inventory Turns table portlet */
247 FUNCTION turns_status_inner_sql(p_as_of_date in varchar2,
248 p_prev_as_of_date in varchar2,
249 p_xtd in varchar2,
250 p_curr in varchar2,
251 p_comparison_type in varchar2,
252 p_view_by_col_name in varchar2,
253 p_view_by_col_id in varchar2,
254 p_fact_name in varchar2,
255 p_where_clause in varchar2,
256 p_in_where_clause in varchar2,
257 p_kpi_group_by in varchar2,
258 p_kpi_in_group_by in varchar2,
259 p_view_by_dim in varchar2,
260 p_use_windowing in varchar2,
261 p_col_name in OPI_DBI_COL_TBL,
262 p_total_col_name in OPI_DBI_COL_TBL)
263 RETURN VARCHAR2
264 IS
265 l_query varchar2(5000);
266 l_col_names varchar2(4000);
267 l_partial_weight varchar2(1000);
268 l_prev_partial_weight varchar2(1000);
269 l_total_col_names varchar2(4000);
270 l_view_by varchar2(120);
271 l_view_by_table varchar2(300);
272 BEGIN
273
274 FOR i IN 1 .. p_col_name.COUNT
275 LOOP
276 l_col_names := l_col_names || ',
277 sum(' || p_col_name(i).column_name || ') ' || p_col_name(i).column_alias;
278 END LOOP;
279
280 FOR i IN 1 .. p_total_col_name.COUNT
281 LOOP
282 l_total_col_names := l_total_col_names || ',
283 sum(sum(' || p_total_col_name(i).column_name || ')) over ()
284 ' || p_total_col_name(i).column_alias;
285 END LOOP;
286
287 l_view_by_table := get_view_by_table(p_view_by_dim);
288
289 l_partial_weight := ' (&BIS_CURRENT_ASOF_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) ';
290
291 l_prev_partial_weight := ' ( &BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) ';
292
293
294 l_query := '(select ' || p_view_by_col_name ||', start_date_'|| p_xtd || ',
295 sum(cogs_val_'|| p_curr || ') * 365 / ' || l_partial_weight || ' cogs_val,
296 (sum(weight * inv_balance_' || p_curr || ') -
297 (select distinct
298 last_value(weight * inv_balance_' || p_curr || ')
299 over (partition by organization_id, start_date_'|| p_xtd || '
300 order by transaction_date asc
301 range between unbounded preceding and unbounded following) -
302 last_value(inv_balance_' || p_curr || ' * ( &BIS_CURRENT_ASOF_DATE - transaction_date + 1))
303 over (partition by organization_id, start_date_'|| p_xtd || '
304 order by transaction_date asc
305 range between unbounded preceding and unbounded following )
306 from ' || p_fact_name || ' insv
307 where
308 fact.start_date_'|| p_xtd || ' = insv.start_date_'|| p_xtd || '
309 and insv.organization_id = fact.organization_id
310 ' || p_in_where_clause || '
311 and insv.transaction_date <= &BIS_CURRENT_ASOF_DATE))
312 / ' || l_partial_weight || ' total_inv_val
313 from ' || p_fact_name || ' fact
314 where
315 (exists
316 (SELECT 1
317 FROM ORG_ACCESS o
318 WHERE o.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
319 AND o.RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID
320 and o.organization_id = fact.organization_id)
321 or exists
322 (SELECT 1
323 FROM mtl_parameters org
324 where org.organization_id = fact.organization_id
325 and NOT EXISTS
326 (select 1
327 from org_access ora
328 where org.organization_id = ora.organization_id)))
329 and transaction_date >= &BIS_CURRENT_EFFECTIVE_START_DATE
330 and transaction_date <= &BIS_CURRENT_ASOF_DATE
331 ' || p_where_clause || '
332 group by fact.' || p_view_by_col_name || ', start_date_'|| p_xtd || p_kpi_in_group_by || '
333 -- having sum(weight * inv_balance_' || p_curr || ') <> 0 and sum(cogs_val_'|| p_curr || ') is not null
334 ) c,
335 (select ' || p_view_by_col_name ||', start_date_'|| p_xtd || ',
336 sum(cogs_val_'|| p_curr || ') * 365 / ' || l_prev_partial_weight || ' cogs_val,
337 (sum(weight * inv_balance_' || p_curr || ') -
338 (select distinct
339 last_value(weight * inv_balance_' || p_curr || ')
340 over (partition by organization_id, start_date_'|| p_xtd || '
341 order by transaction_date asc
342 range between unbounded preceding and unbounded following) -
343 last_value(inv_balance_' || p_curr || ' * (&BIS_PREVIOUS_ASOF_DATE - transaction_date + 1))
344 over (partition by organization_id, start_date_'|| p_xtd || '
345 order by transaction_date asc
346 range between unbounded preceding and unbounded following )
347 from ' || p_fact_name || ' insv
348 where
349 fact.start_date_'|| p_xtd || ' = insv.start_date_'|| p_xtd || '
350 and insv.organization_id = fact.organization_id
351 ' || p_in_where_clause || '
352 and insv.transaction_date <= &BIS_PREVIOUS_ASOF_DATE))/ ' || l_prev_partial_weight || ' total_inv_val
353 from ' || p_fact_name || ' fact
354 where
355 (exists
356 (SELECT 1
357 FROM ORG_ACCESS o
358 WHERE o.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
359 AND o.RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID
360 and o.organization_id = fact.organization_id)
361 or exists
362 (SELECT 1
363 FROM mtl_parameters org
364 where org.organization_id = fact.organization_id
365 and NOT EXISTS
366 (select 1
367 from org_access ora
368 where org.organization_id = ora.organization_id)))
369 and transaction_date >= &BIS_PREVIOUS_EFFECTIVE_START_DATE
370 and transaction_date <= &BIS_PREVIOUS_ASOF_DATE
371 ' || p_where_clause || '
372 group by fact.' || p_view_by_col_name || ', start_date_'|| p_xtd || p_kpi_in_group_by || '
373 -- having sum(weight * inv_balance_' || p_curr || ') <> 0 and sum(cogs_val_'|| p_curr || ') is not null
374 ) p
375 where c.' || p_view_by_col_name || ' = p.' || p_view_by_col_name || ' (+) )' || p_kpi_group_by || ')) oset,
376 ' || l_view_by_table || ' v
377 where oset.' || p_view_by_col_name || ' = v.' || p_view_by_col_id;
378
379 if(p_use_windowing = 'Y') then l_query := l_query || '
380 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)';
381 end if;
382
383 l_query := l_query || '
384 &ORDER_BY_CLAUSE nulls last';
385
386 return l_query;
387
388 END turns_status_inner_sql;
389
390
391
392 /****************************
393 Inventory Turns Trend Report
394 *****************************/
395
396 PROCEDURE inv_turns_trd_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
397 x_custom_sql OUT NOCOPY VARCHAR2,
398 x_custom_output OUT NOCOPY
399 BIS_QUERY_ATTRIBUTES_TBL)
400 IS
401 l_query varchar2(12000);
402 l_view_by varchar2(120);
403 l_as_of_date date;
404 l_prev_as_of_date date;
405 l_plant varchar2(100);
406 l_category varchar2(2000);
407 l_item varchar2(2000);
408 l_xtd varchar2(10);
409 l_comparison_type varchar2(1) := 'Y';
410 l_nested_pattern number;
411 l_plant_where varchar2(240);
412 l_in_plant_where varchar2(240);
413 l_cur_suffix varchar2(5);
414 l_custom_rec BIS_MAP_REC := BIS_MAP_REC(null, null);
415 l_dim_in_tbl OPI_DBI_DIM_TBL;
416 l_dim_out_tbl OPI_DBI_DIM_TBL;
417 l_col_rec OPI_DBI_COL_REC;
418 l_col_tbl OPI_DBI_COL_TBL;
419 l_total_col_tbl OPI_DBI_COL_TBL;
420
421
422 l_custom_attr_rec BIS_QUERY_ATTRIBUTES;
423 l_period_type VARCHAR2(30);
424
425 BEGIN
426
427
428 /* figure out what parameters we need for filtering and map them with available page paramters from PMV */
429 l_dim_in_tbl := OPI_DBI_DIM_TBL('ORGANIZATION+ORGANIZATION');
430 get_parameter_values(p_param, l_dim_in_tbl, l_dim_out_tbl, l_view_by, l_comparison_type, l_xtd, l_as_of_date, l_prev_as_of_date, l_cur_suffix, l_nested_pattern);
431
432 l_plant := l_dim_out_tbl(1);
433
434 l_col_tbl := OPI_DBI_COL_TBL();
435 l_total_col_tbl := OPI_DBI_COL_TBL();
436
437 /* construct the list of measures to be appended to the select clause of the dynamic SQL */
438 l_col_rec.column_name := 'cogs_val_' || l_cur_suffix;
439 l_col_rec.column_alias := 'cogs_val';
440 l_col_tbl.extend;
441 l_col_tbl(l_col_tbl.count) := l_col_rec;
442
443 l_col_rec.column_name := 'inv_balance_' || l_cur_suffix;
444 l_col_rec.column_alias := 'inv_balance';
445 l_col_tbl.extend;
446 l_col_tbl(l_col_tbl.count) := l_col_rec;
447
448 l_col_rec.column_name := 'cogs_val_' || l_cur_suffix;
449 l_col_rec.column_alias := 'cogs_val_total';
450 l_total_col_tbl.extend;
451 l_total_col_tbl(l_total_col_tbl.count) := l_col_rec;
452
453 l_col_rec.column_name := 'inv_balance_' || l_cur_suffix;
454 l_col_rec.column_alias := 'inv_balance_total';
455 l_total_col_tbl.extend;
456 l_total_col_tbl(l_total_col_tbl.count) := l_col_rec;
457
458 /* construct where caluse of the dynamic SQL based on chosen page parameter values */
459 /*
460 if(l_org is null or l_org = '') then
461 l_org_where := ' and (fact.operating_unit_id is null or fact.operating_unit_id in (select organization_id
462 from per_organization_list where security_profile_id = nvl(fnd_profile.value(''XLA_MO_SECURITY_PROFILE_LEVEL''), -1) )) ';
463 else
464 l_org_where := ' and fact.operating_unit_id (+) = &ORGANIZATION+FII_OPERATING_UNITS ' ;
465 end if;
466
467 if(l_org is null or l_org = '') then
468 l_in_org_where := ' and insv.operating_unit_id in (select organization_id from per_organization_list where
469 security_profile_id = nvl(fnd_profile.value(''XLA_MO_SECURITY_PROFILE_LEVEL''), -1) ) ' ;
470 else
471 l_in_org_where := ' and insv.operating_unit_id = &ORGANIZATION+FII_OPERATING_UNITS ';
472 end if;
473 */
474
475 if(l_plant is null or l_plant = '' or l_plant = 'All') then
476 l_plant_where := '';
477 l_in_plant_where := '';
478 else
479 l_plant_where := ' and fact.organization_id in (' || l_plant || ') ';
480 l_in_plant_where := ' and insv.organization_id in (' || l_plant || ') ';
481 end if;
482
483 l_query := get_turns_trd_sel_clause || ' from
484 '|| turns_trd_inner_sql(l_as_of_date,
485 l_prev_as_of_date,
486 l_cur_suffix,
487 l_xtd,
488 l_comparison_type,
489 'opi_dbi_inv_turns_f',
490 l_plant_where,
491 l_in_plant_where,
492 l_col_tbl,
493 l_total_col_tbl);
494 x_custom_sql := l_query;
495
496 if(l_xtd = 'YTD') then
497 l_period_type := 'FII_TIME_ENT_YEAR';
498 elsif(l_xtd = 'QTD') then
499 l_period_type := 'FII_TIME_ENT_QTR';
500 elsif(l_xtd = 'MTD') then
501 l_period_type := 'FII_TIME_ENT_PERIOD';
502 else
503 l_period_type := 'FII_TIME_WEEK';
504 end if;
505
506 l_custom_attr_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
507 x_custom_output := bis_query_attributes_tbl();
508
509 l_custom_attr_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
510 l_custom_attr_rec.attribute_value := 'TIME+'||l_period_type;
511 l_custom_attr_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
512 x_custom_output.extend;
513 x_custom_output(1) := l_custom_attr_rec;
514
515 END inv_turns_trd_sql;
516
517
518 /* Outer select clause of Inventory Turns Trend portlet query */
519 FUNCTION get_turns_trd_sel_clause
520 RETURN VARCHAR2
521 IS
522
523 l_sel_clause varchar2(4000);
524
525 BEGIN
526
527 l_sel_clause :=
528 'select cal.name VIEWBY,
529 cal.name OPI_ATTRIBUTE1,
530 c.avg_daily_inv OPI_MEASURE1,
531 c.annualized_cogs OPI_MEASURE2,
532 decode(p.avg_daily_inv, 0, NULL, p.annualized_cogs/p.avg_daily_inv) OPI_MEASURE3,
533 decode(c.avg_daily_inv, 0, NULL, c.annualized_cogs/c.avg_daily_inv) OPI_MEASURE4,
534 (decode(c.avg_daily_inv, 0, NULL, c.annualized_cogs/c.avg_daily_inv)
535 - decode(p.avg_daily_inv, 0, NULL, p.annualized_cogs/p.avg_daily_inv)) OPI_MEASURE5 ';
536
537 RETURN l_sel_clause;
538
539 END get_turns_trd_sel_clause;
540
541
542 /* inner part of dynamic SQL for Inventory Turns Trend portlet */
543 FUNCTION turns_trd_inner_sql (p_as_of_date in varchar2,
544 p_prev_as_of_date in varchar2,
545 p_curr in varchar2,
546 p_xtd in varchar2,
547 p_comparison_type in varchar2,
548 p_fact_name in varchar2,
549 p_where_clause in varchar2,
550 p_in_where_clause in varchar2,
551 p_col_name in OPI_DBI_COL_TBL,
552 p_total_col_name in OPI_DBI_COL_TBL)
553 RETURN VARCHAR2
554 IS
555 l_query varchar2(12000);
556 l_col_names varchar2(4000);
557 l_total_col_names varchar2(4000);
558 l_view_by varchar2(120);
559 l_partial_weight varchar2(1000);
560 l_prev_partial_weight varchar2(1000);
561 l_global_start_date date;
562 l_span number;
563
564 BEGIN
565
566 FOR i IN p_col_name.FIRST .. p_col_name.LAST
567 LOOP
568 l_col_names := l_col_names || ',
569 ' || p_col_name(i).column_name || ' ' || p_col_name(i).column_alias;
570 END LOOP;
571
572 FOR i IN p_total_col_name.FIRST .. p_total_col_name.LAST
573 LOOP
574 l_total_col_names := l_total_col_names || ',
575 sum(sum(' || p_total_col_name(i).column_name || ')) over () ' || p_total_col_name(i).column_alias;
576 END LOOP;
577
578 l_span := (CASE p_xtd WHEN 'YTD' THEN 365 WHEN 'QTD' THEN 90 WHEN 'MTD' THEN 30 ELSE 7 END);
579 l_global_start_date := bis_common_parameters.get_global_start_date;
580
581 l_partial_weight := ' (&BIS_CURRENT_ASOF_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) ';
582
583 l_prev_partial_weight := ' (&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) ';
584
585 l_query :=
586 '(select start_date_' || p_xtd || ' period_name, sum(cogs_val) annualized_cogs, sum(total_inv_val) avg_daily_inv, id from
587 (select organization_id, start_date_' || p_xtd || ' ,
588 sum(cogs_val_' || p_curr || ') * 365 / sum(weight) cogs_val,
589 sum(weight * inv_balance_' || p_curr || ') / sum(weight) total_inv_val,
590 decode (fact.start_date_' || p_xtd || ', ''' || l_global_start_date || ''',
591 round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ') + 1,
592 round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ')) id
593 from ' || p_fact_name || ' fact
594 where
595 (exists
596 (SELECT 1
597 FROM ORG_ACCESS o
598 WHERE o.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
599 AND o.RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID
600 and o.organization_id = fact.organization_id)
601 or exists
602 (SELECT 1
603 FROM mtl_parameters org
604 where org.organization_id = fact.organization_id
605 and NOT EXISTS
606 (select 1
607 from org_access ora
608 where org.organization_id = ora.organization_id)))
609 and transaction_date >= &BIS_CURRENT_REPORT_START_DATE
610 and transaction_date < &BIS_CURRENT_EFFECTIVE_START_DATE
611 ' || p_where_clause || '
612 group by fact.organization_id, start_date_'|| p_xtd || ',
613 round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ')
614 -- having sum(weight * inv_balance_' || p_curr || ') <> 0 and sum(cogs_val_'|| p_curr || ') is not null
615 union all
616 select organization_id, start_date_'|| p_xtd || ',
617 sum(cogs_val_'|| p_curr || ') * 365 /
618 ' || l_partial_weight || ' cogs_val,
619 (sum(weight * inv_balance_' || p_curr || ') -
620 (select distinct
621 last_value(weight * inv_balance_' || p_curr || ')
622 over (partition by organization_id, start_date_'|| p_xtd || '
623 order by transaction_date asc
624 range between unbounded preceding and unbounded following) -
625 last_value(inv_balance_' || p_curr || ' * (&BIS_CURRENT_ASOF_DATE - transaction_date + 1))
626 over (partition by organization_id, start_date_'|| p_xtd || '
627 order by transaction_date asc
628 range between unbounded preceding and unbounded following )
629 from ' || p_fact_name || ' insv
630 where
631 fact.start_date_'|| p_xtd || ' = insv.start_date_'|| p_xtd || '
632 and insv.organization_id = fact.organization_id
633 ' || p_in_where_clause || '
634 and insv.transaction_date <= &BIS_CURRENT_ASOF_DATE)) /
635 ' || l_partial_weight || ' total_inv_val,
636 decode (fact.start_date_' || p_xtd || ', ''' || l_global_start_date || ''',
637 round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ') + 1,
638 round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ')) id
639 from ' || p_fact_name || ' fact
640 where
641 (exists
642 (SELECT 1
643 FROM ORG_ACCESS o
644 WHERE o.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
645 AND o.RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID
646 and o.organization_id = fact.organization_id)
647 or exists
648 (SELECT 1
649 FROM mtl_parameters org
650 where org.organization_id = fact.organization_id
651 and NOT EXISTS
652 (select 1
653 from org_access ora
654 where org.organization_id = ora.organization_id)))
655 and transaction_date >= &BIS_CURRENT_EFFECTIVE_START_DATE
656 and transaction_date <= &BIS_CURRENT_ASOF_DATE
657 ' || p_where_clause || '
658 group by fact.organization_id, start_date_'|| p_xtd || ',
659 decode (fact.start_date_' || p_xtd || ', ''' || l_global_start_date || ''',
660 round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ') + 1,
661 round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || '))
662 -- having sum(weight * inv_balance_' || p_curr || ') <> 0 and sum(cogs_val_'|| p_curr || ') is not null
663 )
664 group by start_date_' || p_xtd || ', id order by id asc) c,
665 (select start_date_' || p_xtd || ' period_name, sum(cogs_val) annualized_cogs, sum(total_inv_val) avg_daily_inv, id from
666 (select organization_id, start_date_' || p_xtd || ',
667 sum(cogs_val_' || p_curr || ') * 365 / sum(weight) cogs_val,
668 sum(weight * inv_balance_' || p_curr || ') / sum(weight) total_inv_val,
669 decode (fact.start_date_' || p_xtd || ', ''' || l_global_start_date || ''',
670 round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ') + 1,
671 round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ')) id
672 from ' || p_fact_name || ' fact
673 where
674 (exists
675 (SELECT 1
676 FROM ORG_ACCESS o
677 WHERE o.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
678 AND o.RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID
679 and o.organization_id = fact.organization_id)
680 or exists
681 (SELECT 1
682 FROM mtl_parameters org
683 where org.organization_id = fact.organization_id
684 and NOT EXISTS
685 (select 1
686 from org_access ora
687 where org.organization_id = ora.organization_id)))
688 and transaction_date >= &BIS_PREVIOUS_REPORT_START_DATE
689 and transaction_date < &BIS_PREVIOUS_EFFECTIVE_START_DATE
690 ' || p_where_clause || '
691 group by fact.organization_id, start_date_'|| p_xtd || ',
692 round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ')
693 -- having sum(weight * inv_balance_' || p_curr || ') <> 0 and sum(cogs_val_'|| p_curr || ') is not null
694 union all
695 select organization_id, start_date_'|| p_xtd || ',
696 sum(cogs_val_'|| p_curr || ') * 365 /
697 ' || l_prev_partial_weight || ' cogs_val,
698 (sum(weight * inv_balance_' || p_curr || ') -
699 (select distinct
700 last_value(weight * inv_balance_' || p_curr || ')
701 over (partition by organization_id, start_date_'|| p_xtd || '
702 order by transaction_date asc
703 range between unbounded preceding and unbounded following) -
704 last_value(inv_balance_' || p_curr || ' * (&BIS_PREVIOUS_ASOF_DATE - transaction_date + 1))
705 over (partition by organization_id, start_date_'|| p_xtd || '
706 order by transaction_date asc
707 range between unbounded preceding and unbounded following )
708 from ' || p_fact_name || ' insv
709 where
710 fact.start_date_'|| p_xtd || ' = insv.start_date_'|| p_xtd || '
711 and insv.organization_id = fact.organization_id
712 ' || p_in_where_clause || '
713 and insv.transaction_date <= &BIS_PREVIOUS_ASOF_DATE)) /
714 ' || l_prev_partial_weight || ' total_inv_val,
715 decode (fact.start_date_' || p_xtd || ', ''' || l_global_start_date || ''',
716 round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ') + 1,
717 round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ')) id
718 from ' || p_fact_name || ' fact
719 where
720 (exists
721 (SELECT 1
722 FROM ORG_ACCESS o
723 WHERE o.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
724 AND o.RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID
725 and o.organization_id = fact.organization_id)
726 or exists
727 (SELECT 1
728 FROM mtl_parameters org
729 where org.organization_id = fact.organization_id
730 and NOT EXISTS
731 (select 1
732 from org_access ora
733 where org.organization_id = ora.organization_id)))
734 and transaction_date >= &BIS_PREVIOUS_EFFECTIVE_START_DATE
735 and transaction_date <= &BIS_PREVIOUS_ASOF_DATE
736 ' || p_where_clause || '
737 group by fact.organization_id, start_date_'|| p_xtd || ',
738 decode (fact.start_date_' || p_xtd || ', ''' || l_global_start_date || ''',
739 round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ') + 1,
740 round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || '))
741 -- having sum(weight * inv_balance_' || p_curr || ') <> 0 and sum(cogs_val_'|| p_curr || ') is not null
742 )
743 group by start_date_' || p_xtd || ', id order by id asc) p,
744 ' || get_calendar_table(p_xtd) || ' cal
745 where
746 c.id = p.id(+)
747 and cal.start_date = c.period_name
748 order by c.id desc';
749
750 return l_query;
751
752 END turns_trd_inner_sql;
753
754
755 /****************************
756 Utilities
757 *****************************/
758
759 /*
760 Get the view by based on the dimension level
761 */
762 Function get_view_by_table (dim_name varchar2)
763 RETURN VARCHAR2
764
765 IS
766
767 l_table varchar2(300);
768
769 begin
770
771 l_table := (CASE dim_name
772 WHEN 'OPER_UNIT' THEN 'fii_operating_units_v'
773 WHEN 'INV_ORG' THEN '(select organization_id id, name value from hr_all_organization_units_tl where language = userenv(''LANG'')) '
774 WHEN 'PROD_CAT' THEN 'eni_item_vbh_nodes_v'
775 WHEN 'INV_CAT' THEN 'eni_item_inv_cat_v'
776 WHEN 'ITEM' THEN 'eni_item_org_v '
777 ELSE ''
778 END);
779
780 return l_table;
781
782 end get_view_by_table;
783
784
785 /*
786 Get parameter values for the inventory turns reports
787 */
788
789 PROCEDURE get_parameter_values(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
790 p_dim_in_tbl in OPI_DBI_DIM_TBL,
791 p_dim_out_tbl out NOCOPY OPI_DBI_DIM_TBL,
792 p_view_by out NOCOPY VARCHAR2,
793 p_comparison_type out NOCOPY VARCHAR2,
794 p_xtd out NOCOPY VARCHAR2,
795 p_as_of_date out NOCOPY DATE,
796 p_prev_as_of_date out NOCOPY DATE,
797 p_cur_suffix out NOCOPY VARCHAR2,
798 p_nested_pattern out NOCOPY NUMBER)
799 IS
800
801 l_currency varchar2(30);
802 l_period_type varchar2(30);
803
804 BEGIN
805
806 p_dim_out_tbl := OPI_DBI_DIM_TBL();
807 p_dim_out_tbl.extend(p_dim_in_tbl.count);
808
809 for i in 1..p_param.COUNT LOOP
810
811 if( p_param(i).parameter_name= 'VIEW_BY') then
812 p_view_by := p_param(i).parameter_value;
813 end if;
814 if(p_param(i).parameter_name = 'PERIOD_TYPE') then
815 l_period_type := p_param(i).parameter_value;
816 end if;
817 if(p_param(i).parameter_name = 'TIME_COMPARISON_TYPE') then
818 if(p_param(i).parameter_value = 'YEARLY') then
819 p_comparison_type := 'Y';
820 else
821 p_comparison_type := 'S';
822 end if;
823 end if;
824 if(p_param(i).parameter_name = 'AS_OF_DATE') then
825 p_as_of_date := to_date(p_param(i).parameter_value, 'DD-MM-RR');
826 end if;
827 if(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') then
828 l_currency := p_param(i).parameter_id;
829 end if;
830
831 FOR j IN 1 .. p_dim_in_tbl.COUNT
832 LOOP
833 if(p_param(i).parameter_name = p_dim_in_tbl(j)) then
834 p_dim_out_tbl(j) := p_param(i).parameter_id;
835 end if;
836 END LOOP;
837
838 END LOOP;
839
840 if(l_period_type = 'FII_TIME_ENT_YEAR') then p_xtd := 'YTD';
841 elsif (l_period_type = 'FII_TIME_ENT_QTR') then p_xtd := 'QTD';
842 elsif (l_period_type = 'FII_TIME_ENT_PERIOD') then p_xtd := 'MTD';
843 else p_xtd := 'WTD';
844 end if;
845
846 if(p_as_of_date is null) then p_as_of_date := sysdate; end if;
847 p_prev_as_of_date := opi_dbi_calendar_pkg.previous_period_asof_date(p_as_of_date, p_xtd, p_comparison_type);
848 p_nested_pattern := get_nested_pattern(p_xtd);
849
850 if(p_comparison_type is null) then p_comparison_type := 'Y'; end if;
851
852 /*Mohit - 08/23/2004
853 1. removed the else clause as it was causing currency suffix to default to 'B'
854 2. Replace else with elsif statement (same as in util package)
855 */
856 if(l_currency = '''FII_GLOBAL1''') then
857 p_cur_suffix := 'g';
858 elsif(l_currency = '''FII_GLOBAL2''') then
859 p_cur_suffix := 'sg';
860 elsif(l_currency is not null) then
861 -- else
862 p_cur_suffix := 'b';
863 end if;
864
865 if(p_cur_suffix is null) then p_cur_suffix := 'g'; end if;
866
867 END get_parameter_values;
868
869
870 Function get_calendar_table(period_type varchar2)
871 return varchar2 is
872
873 l_table_name varchar2(25);
874 begin
875
876 if(period_type = 'YTD') then
877 l_table_name := 'fii_time_ent_year';
878 elsif(period_type = 'QTD') then
879 l_table_name := 'fii_time_ent_qtr';
880 elsif(period_type = 'MTD') then
881 l_table_name := 'fii_time_ent_period';
882 elsif(period_type = 'WTD') then
883 l_table_name := 'fii_time_week';
884 end if;
885
886 return l_table_name;
887
888 end get_calendar_table;
889
890 Function get_nested_pattern(period_type varchar2)
891 return number is
892
893 l_pattern number;
894 begin
895
896 if(period_type = 'YTD') then
897 l_pattern := 119;
898 elsif(period_type = 'QTD') then
899 l_pattern := 55;
900 elsif(period_type = 'MTD') then
901 l_pattern := 23;
902 elsif(period_type = 'WTD') then
903 l_pattern := 11;
904 end if;
905
906 return l_pattern;
907
908 end get_nested_pattern;
909
910
911 END OPI_DBI_INV_RPT_PKG;