[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_PTP_RPT_PKG
Source
1 PACKAGE BODY OPI_DBI_PTP_RPT_PKG AS
2 /* $Header: OPIDRPTPB.pls 120.0 2005/05/24 17:55:10 appldev noship $ */
3
4 PROCEDURE GET_TBL_SQL(
5 p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
6 x_custom_sql OUT NOCOPY VARCHAR2,
7 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
8 ) IS
9 l_formula_sql VARCHAR2(4000) := '';
10 l_inner_sql VARCHAR2(8000) := '';
11 l_stmt VARCHAR2(10240) := '';
12 l_period_type VARCHAR2(255) := NULL;
13 l_view_by VARCHAR2(255) := NULL;
14 l_org VARCHAR2(255) := NULL;
15 l_org_where VARCHAR2(2000) := ' 1=1';
16 l_org_security VARCHAR2(2000) := ' AND 1=1';
17 l_item VARCHAR2(255) := NULL;
18 l_item_where VARCHAR2(2000) := ' AND 1=1';
19 l_inv_cat VARCHAR2(255) := NULL;
20 l_inv_cat_where VARCHAR2(2000) := ' AND 1=1';
21 l_item_cat_flag NUMBER; -- 0 for item and 1 for inv. category
22 l_currency VARCHAR2(30) := '';
23 l_currency_code VARCHAR2(2) := 'B';
24 l_lang_code VARCHAR2(20) := NULL;
25 l_custom_rec BIS_QUERY_ATTRIBUTES;
26 l_curr_asof_date DATE;
27 l_prev_asof_date DATE;
28 l_nested_pattern NUMBER;
29
30 BEGIN
31
32 FOR i IN 1..p_param.COUNT
33 LOOP
34 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
35 THEN l_period_type := p_param(i).parameter_value;
36 END IF;
37
38 IF( p_param(i).parameter_name= 'VIEW_BY')
39 THEN l_view_by := p_param(i).parameter_value;
40 END IF;
41
42 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
43 THEN l_org := p_param(i).parameter_id;
44 END IF;
45
46 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
47 IF ( p_param(i).parameter_value IS NULL OR p_param(i).parameter_value = 'All' ) THEN
48 l_inv_cat := p_param(i).parameter_value;
49 ELSE
50 l_inv_cat := 'Selected';
51 END IF;
52 END IF;
53
54 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
55 IF ( p_param(i).parameter_value IS NULL OR p_param(i).parameter_value = 'All' ) THEN
56 l_item := p_param(i).parameter_value;
57 ELSE
58 l_item := 'Selected';
59 END IF;
60 END IF;
61
62 IF(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') then
63 l_currency := p_param(i).parameter_id;
64 END IF;
65
66 IF(p_param(i).parameter_name = 'AS_OF_DATE') then
67 l_curr_asof_date := p_param(i).period_date;
68 END IF;
69
70 IF(p_param(i).parameter_name = 'BIS_P_ASOF_DATE') then
71 l_prev_asof_date := p_param(i).period_date;
72 END IF;
73 END LOOP;
74
75 IF(l_currency = '''FII_GLOBAL1''') then
76 l_currency_code := 'G';
77 ELSIF (l_currency = '''FII_GLOBAL2''') then
78 l_currency_code := 'SG';
79 END IF;
80
81 l_lang_code := USERENV('LANG');
82
83 IF ( l_org IS NULL OR l_org = 'All' ) THEN
84 l_org_security := '
85 AND (EXISTS
86 (SELECT 1
87 FROM org_access o
88 WHERE o.responsibility_id = fnd_global.resp_id
89 AND o.resp_application_id = fnd_global.resp_appl_id
90 AND o.organization_id = f.organization_id)
91 OR EXISTS
92 (SELECT 1
93 FROM mtl_parameters org
94 WHERE org.organization_id = f.organization_id
95 AND NOT EXISTS
96 (SELECT 1
97 FROM org_access ora
98 WHERE org.organization_id = ora.organization_id)))';
99
100 ELSE
101 --do we allow mutliple selects of org here?
102 --l_org_where := ' AND f.organization_id = &ORGANIZATION+ORGANIZATION';
103 l_org_where := ' f.organization_id = '||l_org;
104 END IF;
105
106 IF ( l_inv_cat IS NULL OR l_inv_cat = 'All' ) THEN
107 l_inv_cat_where :='';
108 ELSE
109 l_inv_cat_where := ' AND f.inv_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
110 END IF;
111
112 IF ( l_item IS NULL OR l_item = 'All' ) THEN
113 l_item_where :='';
114 ELSE
115 l_item_where := ' AND f.item_org_id in (&ITEM+ENI_ITEM_ORG)';
116 END IF;
117
118 IF (l_item IS NULL OR l_item = 'All') THEN
119 IF (l_view_by = 'ITEM+ENI_ITEM_ORG') THEN
120 l_item_cat_flag := 0; -- item
121 ELSIF (l_view_by = 'ITEM+ENI_ITEM_INV_CAT') THEN
122 l_item_cat_flag := 1; -- category
123 ELSE
124 IF (l_inv_cat IS NULL OR l_inv_cat = 'All') THEN
125 l_item_cat_flag := 3; -- all
126 ELSE
127 l_item_cat_flag := 1; -- category
128 END IF;
129 END IF;
130 ELSE
131 l_item_cat_flag := 0; -- item
132 END IF;
133
134 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
135 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
136
137 l_formula_sql :='CURR_PLANNED_QUANTITY OPI_MEASURE1,
138 PREV_PLANNED_STANDARD_VALUE OPI_MEASURE2,
139 CURR_PLANNED_STANDARD_VALUE OPI_MEASURE3,
140 CURR_ACTUAL_QUANTITY OPI_MEASURE4,
141 PREV_ACTUAL_STANDARD_VALUE OPI_MEASURE5,
142 CURR_ACTUAL_STANDARD_VALUE OPI_MEASURE6,
143 PREV_ACTUAL_STANDARD_VALUE/decode(PREV_PLANNED_STANDARD_VALUE, 0, null, PREV_PLANNED_STANDARD_VALUE)*100 OPI_MEASURE7,
144 CURR_ACTUAL_STANDARD_VALUE/decode(CURR_PLANNED_STANDARD_VALUE, 0, null, CURR_PLANNED_STANDARD_VALUE)*100 OPI_MEASURE8,
145 (CURR_ACTUAL_STANDARD_VALUE/decode(CURR_PLANNED_STANDARD_VALUE, 0, null, CURR_PLANNED_STANDARD_VALUE) -
146 PREV_ACTUAL_STANDARD_VALUE/decode(PREV_PLANNED_STANDARD_VALUE, 0, null, PREV_PLANNED_STANDARD_VALUE))*100 OPI_MEASURE9,
147 PREV_ACTUAL_VALUE OPI_MEASURE10,
148 CURR_ACTUAL_VALUE OPI_MEASURE11,
149 (CURR_ACTUAL_VALUE - PREV_ACTUAL_VALUE)/decode(PREV_ACTUAL_VALUE, 0, null, abs(PREV_ACTUAL_VALUE))*100 OPI_MEASURE13,
150 SUM(CURR_PLANNED_STANDARD_VALUE) OVER () OPI_MEASURE14,
151 SUM(CURR_ACTUAL_STANDARD_VALUE) OVER() OPI_MEASURE15,
152 SUM(CURR_ACTUAL_STANDARD_VALUE) OVER ()/decode(SUM(CURR_PLANNED_STANDARD_VALUE) OVER (), 0, null, SUM(CURR_PLANNED_STANDARD_VALUE) OVER ())*100 OPI_MEASURE16,
153 (SUM(CURR_ACTUAL_STANDARD_VALUE) OVER ()/decode(SUM(CURR_PLANNED_STANDARD_VALUE) OVER (), 0, null, SUM(CURR_PLANNED_STANDARD_VALUE) OVER ()) -
154 SUM(PREV_ACTUAL_STANDARD_VALUE) OVER ()/decode(SUM(PREV_PLANNED_STANDARD_VALUE) OVER (), 0, null, SUM(PREV_PLANNED_STANDARD_VALUE) OVER ()))*100 OPI_MEASURE17,
155 SUM(CURR_ACTUAL_VALUE) OVER () OPI_MEASURE18,
156 (SUM(CURR_ACTUAL_VALUE) OVER () - SUM(PREV_ACTUAL_VALUE) OVER ())/decode(SUM(PREV_ACTUAL_VALUE) OVER (), 0, null, abs(SUM(PREV_ACTUAL_VALUE) OVER ()))*100 OPI_MEASURE19,
157 CURR_ACTUAL_STANDARD_VALUE/decode(CURR_PLANNED_STANDARD_VALUE, 0, null, CURR_PLANNED_STANDARD_VALUE)*100 OPI_MEASURE20,
158 PREV_ACTUAL_STANDARD_VALUE/decode(PREV_PLANNED_STANDARD_VALUE, 0, null, PREV_PLANNED_STANDARD_VALUE)*100 OPI_MEASURE21,
159 SUM(CURR_ACTUAL_STANDARD_VALUE) OVER ()/decode(SUM(CURR_PLANNED_STANDARD_VALUE) OVER (), 0, null, SUM(CURR_PLANNED_STANDARD_VALUE) OVER ())*100 OPI_MEASURE22,
160 SUM(PREV_ACTUAL_STANDARD_VALUE) OVER ()/decode(SUM(PREV_PLANNED_STANDARD_VALUE) OVER (), 0, null, SUM(PREV_PLANNED_STANDARD_VALUE) OVER ())*100 OPI_MEASURE23,
161 CURR_ACTUAL_VALUE OPI_MEASURE25,
162 PREV_ACTUAL_VALUE OPI_MEASURE26,
163 SUM(CURR_ACTUAL_VALUE) OVER () OPI_MEASURE28,
164 SUM(PREV_ACTUAL_VALUE) OVER () OPI_MEASURE29
165 ';
166
167 l_inner_sql:='sum(decode(sign(report_date - &BIS_CURRENT_EFFECTIVE_START_DATE), -1, 0, decode(c.report_date, c.curr_asof_date, f.PLANNED_QUANTITY, 0))) CURR_PLANNED_QUANTITY,
168 sum(decode(sign(report_date - &BIS_PREVIOUS_EFFECTIVE_START_DATE), -1, 0, decode(c.report_date, c.prev_asof_date, f.PLANNED_QUANTITY, 0))) PREV_PLANNED_QUANTITY,
169 sum(decode(sign(report_date - &BIS_CURRENT_EFFECTIVE_START_DATE), -1, 0, decode(c.report_date, c.curr_asof_date, f.PLANNED_STANDARD_VALUE, 0))) CURR_PLANNED_STANDARD_VALUE,
170 sum(decode(sign(report_date - &BIS_PREVIOUS_EFFECTIVE_START_DATE), -1, 0, decode(c.report_date, c.prev_asof_date, f.PLANNED_STANDARD_VALUE, 0))) PREV_PLANNED_STANDARD_VALUE,
171 sum(decode(sign(report_date - &BIS_CURRENT_EFFECTIVE_START_DATE), -1, 0, decode(c.report_date, c.curr_asof_date, f.ACTUAL_QUANTITY, 0))) CURR_ACTUAL_QUANTITY,
172 sum(decode(sign(report_date - &BIS_PREVIOUS_EFFECTIVE_START_DATE), -1, 0, decode(c.report_date, c.prev_asof_date, f.ACTUAL_QUANTITY, 0))) PREV_ACTUAL_QUANTITY,
173 sum(decode(sign(report_date - &BIS_CURRENT_EFFECTIVE_START_DATE), -1, 0, decode(c.report_date, c.curr_asof_date, f.ACTUAL_STANDARD_VALUE, 0))) CURR_ACTUAL_STANDARD_VALUE,
174 sum(decode(sign(report_date - &BIS_PREVIOUS_EFFECTIVE_START_DATE), -1, 0, decode(c.report_date, c.prev_asof_date, f.ACTUAL_STANDARD_VALUE, 0))) PREV_ACTUAL_STANDARD_VALUE,
175 sum(decode(sign(report_date - &BIS_CURRENT_EFFECTIVE_START_DATE), -1, 0, decode(c.report_date, c.curr_asof_date, f.ACTUAL_VALUE, 0))) CURR_ACTUAL_VALUE,
176 sum(decode(sign(report_date - &BIS_PREVIOUS_EFFECTIVE_START_DATE), -1, 0, decode(c.report_date, c.prev_asof_date, f.ACTUAL_VALUE, 0))) PREV_ACTUAL_VALUE
177 FROM
178 (select
179 f.ORGANIZATION_ID,
180 f.INVENTORY_ITEM_ID,
181 f.INV_CATEGORY_ID,
182 f.ITEM_ORG_ID,
183 f.UOM_CODE,
184 f.TIME_ID,
185 f.PERIOD_TYPE_ID,
186 0 ACTUAL_QUANTITY,
187 0 ACTUAL_VALUE,
188 f.ACTUAL_STANDARD_VALUE_'||l_currency_code||' ACTUAL_STANDARD_VALUE,
189 f.PLANNED_QUANTITY,
190 f.PLANNED_STANDARD_VALUE_'||l_currency_code||' PLANNED_STANDARD_VALUE
191 from OPI_PTP_SUM_F_MV f
192 where
193 f.item_cat_flag = :OPI_ITEM_CAT_FLAG
194 '||l_inv_cat_where||l_item_where||
195 ' union all
196 select
197 f.ORGANIZATION_ID,
198 f.INVENTORY_ITEM_ID,
199 f.INV_CATEGORY_ID,
200 f.ITEM_ORG_ID,
201 f.UOM_CODE,
202 f.TIME_ID,
203 f.PERIOD_TYPE_ID,
204 0 ACTUAL_QUANTITY,
205 0 ACTUAL_VALUE,
206 0 ACTUAL_STANDARD_VALUE,
207 f.PLANNED_QUANTITY,
208 f.PLANNED_STANDARD_VALUE_'||l_currency_code||' PLANNED_STANDARD_VALUE
209 from OPI_PTP_SUM_STG_MV f
210 where
211 f.item_cat_flag = :OPI_ITEM_CAT_FLAG
212 '||l_inv_cat_where||l_item_where||
213 ' union all
214 select
215 f.ORGANIZATION_ID,
216 f.INVENTORY_ITEM_ID,
217 f.INV_CATEGORY_ID,
218 f.ITEM_ORG_ID,
219 f.UOM_CODE,
220 f.TIME_ID,
221 f.PERIOD_TYPE_ID,
222 nvl(f.PRODUCTION_QTY, 0) - nvl(f.SCRAP_QTY, 0) ACTUAL_QUANTITY,
223 nvl(f.PRODUCTION_VAL_'||l_currency_code||', 0) - nvl(f.SCRAP_VAL_'||l_currency_code||', 0) ACTUAL_VALUE,
224 0 ACTUAL_STANDARD_VALUE,
225 0 PLANNED_QUANTITY,
226 0 PLANNED_STANDARD_VALUE
227 from OPI_SCRAP_SUM_MV f
228 where
229 f.item_cat_flag = :OPI_ITEM_CAT_FLAG
230 '||l_inv_cat_where||l_item_where||
231 ') f,
232 OPI_DBI_PTP_TBL_TMP c
233 WHERE f.organization_id = c.organization_id
234 AND f.time_id = c.time_id
235 AND c.period_type_id = f.period_type_id '
236 ;
237
238 IF l_view_by = 'ITEM+ENI_ITEM_INV_CAT' THEN
239 l_stmt := ' SELECT eni.value VIEWBY,
240 fact.inv_category_id VIEWBYID,
241 eni.value OPI_ATTRIBUTE1,
242 null OPI_ATTRIBUTE2,
243 null OPI_ATTRIBUTE3,
244 null OPI_ATTRIBUTE6,
245 '||l_formula_sql||'
246 FROM (SELECT /*+ push_pred(f) leading(c) */ f.inv_category_id,
247 '||l_inner_sql||'
248 group by f.inv_category_id) fact,
249 ENI_ITEM_INV_CAT_V eni
250 WHERE fact.inv_category_id = eni.id (+)
251 &ORDER_BY_CLAUSE NULLS LAST';
252
253 ELSIF l_view_by = 'ORGANIZATION+ORGANIZATION' THEN
254 l_stmt := 'SELECT org.name VIEWBY,
255 org.name OPI_ATTRIBUTE1,
256 null OPI_ATTRIBUTE2,
257 null OPI_ATTRIBUTE3,
258 null OPI_ATTRIBUTE6,
259 '||l_formula_sql||'
260 FROM (SELECT /*+ push_pred(f) leading(c) */ f.organization_id,
261 '||l_inner_sql||'
262 group by f.organization_id) fact,
263 HR_ALL_ORGANIZATION_UNITS_TL org
264 WHERE org.organization_id = fact.organization_id
265 AND org.language = :OPI_LANG_CODE
266 &ORDER_BY_CLAUSE NULLS LAST' ;
267
268 ELSE --l_view_by = 'ITEM+ENI_ITEM_ORG' THEN
269 l_stmt := 'SELECT items.value VIEWBY,
270 items.id VIEWBYID,
271 items.value OPI_ATTRIBUTE1,
272 items.description OPI_ATTRIBUTE2,
273 uom.unit_of_measure OPI_ATTRIBUTE3,';
274 IF((l_period_type = 'FII_TIME_WEEK' OR l_period_type =
275 'FII_TIME_ENT_PERIOD') AND (UPPER(l_org)<>'ALL')) THEN
276 l_stmt := l_stmt || ' ''pFunctionName=OPI_DBI_PTP_JOB_DTL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'' OPI_ATTRIBUTE6 ,';
277 ELSE
278 l_stmt := l_stmt || 'NULL OPI_ATTRIBUTE6 ,';
279 END IF;
280 l_stmt := l_stmt
281 ||l_formula_sql||'
282 FROM (SELECT /*+ push_pred(f) leading(c) */ f.item_org_id, f.uom_code,
283 '||l_inner_sql||'
284 group by f.item_org_id,f.uom_code) fact,
285 ENI_ITEM_ORG_V items,
286 MTL_UNITS_OF_MEASURE_VL uom
287 WHERE fact.item_org_id = items.id
288 AND uom.uom_code = fact.uom_code
289 AND uom.language = :OPI_LANG_CODE
290 &ORDER_BY_CLAUSE NULLS LAST';
291 END IF;
292
293 BEGIN
294 execute immediate '
295 CREATE GLOBAL TEMPORARY TABLE OPI_DBI_PTP_TBL_TMP(
296 organization_id number,
297 time_id number,
298 report_date date,
299 curr_asof_date date,
300 prev_asof_date date,
301 period_type_id number
302 ) ON COMMIT PRESERVE ROWS'
303 ;
304 EXCEPTION
305 WHEN others THEN null;
306 execute immediate 'truncate table opi_dbi_ptp_tbl_tmp';
307 END;
308 BEGIN
309 if(l_period_type = 'FII_TIME_ENT_YEAR') then
310 l_nested_pattern := 119;
311 elsif(l_period_type = 'FII_TIME_ENT_QTR') then
312 l_nested_pattern := 55;
313 elsif(l_period_type = 'FII_TIME_ENT_PERIOD') then
314 l_nested_pattern := 23;
315 elsif(l_period_type = 'FII_TIME_WEEK') then
316 l_nested_pattern := 11;
317 elsif(l_period_type = 'FII_TIME_DAY') then
318 l_nested_pattern := 1;
319 else
320 l_nested_pattern := 119;
321 end if;
322 execute immediate '
323 insert into OPI_DBI_PTP_TBL_TMP
324 (organization_id,
325 time_id,
326 report_date,
327 curr_asof_date,
328 prev_asof_date,
329 period_type_id
330 )
331 select
332 bnd.organization_id,
333 cal.time_id,
334 cal.report_date,
335 bnd.curr_asof_date,
336 bnd.prev_asof_date,
337 cal.period_type_id
338 from
339 FII_TIME_RPT_STRUCT_V cal,
340 (select
341 organization_id,
345 opi_ptp_rpt_bnd_mv f
342 decode(data_clean_date, NULL, :l_curr_asof_date, decode(sign(:l_curr_asof_date - data_clean_date), 1, data_clean_date, :l_curr_asof_date)) curr_asof_date,
343 decode(data_clean_date, NULL, :l_prev_asof_date, decode(sign(:l_prev_asof_date - data_clean_date), 1, data_clean_date, :l_prev_asof_date)) prev_asof_date
344 from
346 where '||l_org_where||l_org_security||'
347 ) bnd
348 where cal.report_date in (bnd.curr_asof_date, bnd.prev_asof_date)
349 AND bitand(cal.record_type_id, :l_nested_parttern) = cal.record_type_id
350 '
351 using l_curr_asof_date, l_curr_asof_date, l_curr_asof_date, l_prev_asof_date, l_prev_asof_date, l_prev_asof_date, l_nested_pattern;
352 EXCEPTION
353 WHEN others THEN null;
354 END;
355
356 x_custom_sql := l_stmt;
357
358 l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
359 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
360 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
361 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
362 x_custom_output.EXTEND;
363 x_custom_output(1) := l_custom_rec;
364
365 l_custom_rec.attribute_name := ':OPI_LANG_CODE';
366 l_custom_rec.attribute_value := l_lang_code;
367 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
368 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
369 x_custom_output.EXTEND;
370 x_custom_output(2) := l_custom_rec;
371
372 END GET_TBL_SQL;
373
374 PROCEDURE GET_TRD_SQL(
375 p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
376 x_custom_sql OUT NOCOPY VARCHAR2,
377 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
378 ) IS
379
380 l_formula_sql VARCHAR2(4000) := '';
381 l_inner_sql VARCHAR2(6000) := '';
382 l_stmt VARCHAR2(10240):= '';
383 l_period_type VARCHAR2(255) := NULL;
384 l_org VARCHAR2(255) := NULL;
385 l_org_where VARCHAR2(2000) := ' AND 1=1';
386 l_org_security VARCHAR2(2000) := '1=1';
387 l_item VARCHAR2(255) := NULL;
388 l_item_where VARCHAR2(2000) := ' AND 1=1';
389 l_inv_cat VARCHAR2(255) := NULL;
390 l_inv_cat_where VARCHAR2(2000) := ' AND 1=1';
391 l_item_cat_flag NUMBER; -- 0 for item and 1 for inv. category
392 l_currency VARCHAR2(30) := '';
393 l_currency_code VARCHAR2(2) := 'B';
394 l_nested_pattern NUMBER;
395 l_period_id VARCHAR2(20);
396 l_custom_rec BIS_QUERY_ATTRIBUTES;
397 l_curr_asof_date DATE;
398 l_prev_asof_date DATE;
399 l_curr_rpt_start_date DATE;
400 l_prev_rpt_start_date DATE;
401 BEGIN
402
403 FOR i IN 1..p_param.COUNT
404 LOOP
405
406 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
407 THEN l_period_type := p_param(i).parameter_value;
408 END IF;
409
410 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
411 THEN l_org := p_param(i).parameter_value;
412 END IF;
413
414 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
415 IF ( p_param(i).parameter_value IS NULL OR p_param(i).parameter_value = 'All' ) THEN
416 l_inv_cat := p_param(i).parameter_value;
417 ELSE
418 l_inv_cat := 'Selected';
419 END IF;
420 END IF;
421
422 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
423 IF ( p_param(i).parameter_value IS NULL OR p_param(i).parameter_value = 'All' ) THEN
424 l_item := p_param(i).parameter_value;
425 ELSE
426 l_item := 'Selected';
427 END IF;
428 END IF;
429
430 IF(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') then
431 l_currency := p_param(i).parameter_id;
432 END IF;
433
434 IF(p_param(i).parameter_name = 'AS_OF_DATE') then
435 l_curr_asof_date := p_param(i).period_date;
436 END IF;
437
438 IF(p_param(i).parameter_name = 'BIS_P_ASOF_DATE') then
439 l_prev_asof_date := p_param(i).period_date;
440 END IF;
441
442 IF(p_param(i).parameter_name = 'BIS_CURRENT_REPORT_START_DATE') then
443 l_curr_rpt_start_date := p_param(i).period_date;
444 END IF;
445
446 IF(p_param(i).parameter_name = 'BIS_PREVIOUS_REPORT_START_DATE') then
447 l_prev_rpt_start_date := p_param(i).period_date;
448 END IF;
449
450 END LOOP;
451
452 /* Use Global or Functional Currency */
453 IF(l_currency = '''FII_GLOBAL1''') then
454 l_currency_code := 'G';
455 ELSIF (l_currency = '''FII_GLOBAL2''') then
456 l_currency_code := 'SG';
457 END IF;
458
459
460 /* Security */
461 IF ( l_org IS NULL OR l_org = 'All' ) THEN
462 l_org_security := '
463 (EXISTS
464 (SELECT 1
465 FROM org_access o
466 WHERE o.responsibility_id = fnd_global.resp_id
467 AND o.resp_application_id = fnd_global.resp_appl_id
468 AND o.organization_id = f.organization_id)
469 OR EXISTS
470 (SELECT 1
471 FROM mtl_parameters org
472 WHERE org.organization_id = f.organization_id
473 AND NOT EXISTS
474 (SELECT 1
475 FROM org_access ora
476 WHERE org.organization_id = ora.organization_id)))';
477
478 ELSE
482
479 --do we allow mutliple selects of org here?
480 l_org_where := ' AND f.organization_id = &ORGANIZATION+ORGANIZATION';
481 END IF;
483 IF ( l_inv_cat IS NULL OR l_inv_cat = 'All' ) THEN
484 l_inv_cat_where :='';
485 ELSE
486 l_inv_cat_where := ' AND f.inv_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
487 END IF;
488
489 IF ( l_item IS NULL OR l_item = 'All' ) THEN
490 l_item_where :='';
491 ELSE
492 l_item_where := ' AND f.item_org_id in (&ITEM+ENI_ITEM_ORG)';
493 END IF;
494
495 IF((l_inv_cat IS NULL OR l_inv_cat = 'All' ) AND ( l_item IS NULL OR l_item = 'All')) THEN
496 l_item_cat_flag := 3; -- no grouping on item dimension
497 ELSE
498 IF (l_item IS NULL OR l_item = 'All') THEN
499 l_item_cat_flag := 1; -- inv, category
500 ELSE
501 l_item_cat_flag := 0; -- item is needed
502 END IF;
503 END IF;
504
505 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
506 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
507
508 l_stmt :=
509 'SELECT
510 fii.NAME VIEWBY,
511 fii.NAME OPI_ATTRIBUTE1,
512 PREV_PLANNED_STANDARD_VALUE OPI_MEASURE2,
513 CURR_PLANNED_STANDARD_VALUE OPI_MEASURE3,
514 PREV_ACTUAL_STANDARD_VALUE OPI_MEASURE4,
515 CURR_ACTUAL_STANDARD_VALUE OPI_MEASURE5,
516 PREV_ACTUAL_STANDARD_VALUE/decode(PREV_PLANNED_STANDARD_VALUE, 0,
517 null, PREV_PLANNED_STANDARD_VALUE)*100 OPI_MEASURE6,
518 CURR_ACTUAL_STANDARD_VALUE/decode(CURR_PLANNED_STANDARD_VALUE, 0,
519 null, CURR_PLANNED_STANDARD_VALUE)*100 OPI_MEASURE7,
520 (CURR_ACTUAL_STANDARD_VALUE/decode(CURR_PLANNED_STANDARD_VALUE, 0,
521 null, CURR_PLANNED_STANDARD_VALUE) -
522 PREV_ACTUAL_STANDARD_VALUE/decode(PREV_PLANNED_STANDARD_VALUE, 0,
523 null, PREV_PLANNED_STANDARD_VALUE))*100 OPI_MEASURE8,
524 PREV_ACTUAL_VALUE OPI_MEASURE9,
525 CURR_ACTUAL_VALUE OPI_MEASURE10,
526 (CURR_ACTUAL_VALUE - PREV_ACTUAL_VALUE)/
527 decode(PREV_ACTUAL_VALUE, 0, null,
528 abs(PREV_ACTUAL_VALUE))*100 OPI_MEASURE11
529 FROM (SELECT /*+ leading(cal) push_pred(fact) */
530 cal.start_date START_DATE,
531 sum(CASE WHEN cal.curr_day < bnd.DATA_CLEAN_DATE then
532 decode(cal.report_date, cal.curr_day,nvl(
533 fact.PLANNED_STANDARD_VALUE,0),0)
534 else 0
535 end) +
536 sum(CASE WHEN cal.curr_day = bnd.DATA_CLEAN_DATE then
537 decode(cal.report_date, cal.curr_day,nvl(
538 fact.PLANNED_STANDARD_VALUE,0)/2,0)
539 else 0
540 end) +
541 sum(CASE WHEN (cal.curr_day > bnd.DATA_CLEAN_DATE) and
542 (cal.start_date <= bnd.DATA_CLEAN_DATE) then
543 decode(cal.report_date, bnd.DATA_CLEAN_DATE,
544 nvl(fact.PLANNED_STANDARD_VALUE,0),0)
545 else 0
546 end) CURR_PLANNED_STANDARD_VALUE,
547 sum(decode(cal.report_date, cal.prev_day,
548 nvl(fact.PLANNED_STANDARD_VALUE,0), 0)) PREV_PLANNED_STANDARD_VALUE,
549 sum(CASE WHEN cal.curr_day < bnd.DATA_CLEAN_DATE then
550 decode(cal.report_date, cal.curr_day,nvl(
551 fact.ACTUAL_STANDARD_VALUE,0),0)
552 else 0
553 end) +
554 sum(CASE WHEN cal.curr_day = bnd.DATA_CLEAN_DATE then
555 decode(cal.report_date, cal.curr_day,nvl(
556 fact.ACTUAL_STANDARD_VALUE,0)/2,0)
557 else 0
558 end) +
559 sum(CASE WHEN (cal.curr_day > bnd.DATA_CLEAN_DATE) and
560 (cal.start_date <= bnd.DATA_CLEAN_DATE) then
561 decode(cal.report_date, bnd.DATA_CLEAN_DATE,
562 nvl(fact.ACTUAL_STANDARD_VALUE,0),0)
563 else 0
564 end) CURR_ACTUAL_STANDARD_VALUE,
565 sum(decode(cal.report_date, cal.prev_day,
566 nvl(fact.ACTUAL_STANDARD_VALUE,0), 0)) PREV_ACTUAL_STANDARD_VALUE,
567 sum(CASE WHEN cal.curr_day < bnd.DATA_CLEAN_DATE then
568 decode(cal.report_date, cal.curr_day,nvl(
569 fact.ACTUAL_VALUE,0),0)
570 else 0
571 end) +
572 sum(CASE WHEN cal.curr_day = bnd.DATA_CLEAN_DATE then
573 decode(cal.report_date, cal.curr_day,nvl(
574 fact.ACTUAL_VALUE,0)/2,0)
575 else 0
576 end) +
577 sum(CASE WHEN (cal.curr_day > bnd.DATA_CLEAN_DATE) and
578 (cal.start_date <= bnd.DATA_CLEAN_DATE) then
579 decode(cal.report_date, bnd.DATA_CLEAN_DATE,
580 nvl(fact.ACTUAL_VALUE,0),0)
581 else 0
582 end) CURR_ACTUAL_VALUE,
583 sum(decode(cal.report_date, cal.prev_day,
584 nvl(fact.ACTUAL_VALUE,0), 0)) PREV_ACTUAL_VALUE
588 tmp.organization_id,
585 FROM (select /*+ no_merge */
586 dates.start_date,
587 dates.name,
589 dates.curr_day,
590 dates.prev_day,
591 tmp.report_date,
592 tmp.time_id,
593 tmp.period_type_id
594 from
595 (SELECT curr.start_date START_DATE,
596 curr.name NAME,
597 curr.day CURR_DAY,
598 prev.day PREV_DAY
599 FROM (SELECT fii.start_date START_DATE,
600 fii.NAME NAME,
601 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) DAY,
602 rownum ID
603 FROM '||l_period_type||' fii
604 WHERE fii.start_date BETWEEN
605 &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
606 ORDER BY fii.start_date DESC) curr,
607 (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) DAY,
608 rownum ID
609 FROM '||l_period_type||' fii
610 WHERE fii.start_date BETWEEN
611 &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
612 ORDER BY fii.start_date DESC) prev
613 WHERE curr.id = prev.id(+)) dates,
614 OPI_DBI_PTP_TRD_TMP tmp
615 where decode(tmp.organization_id, -1, tmp.report_date, dates.curr_day) in (dates.curr_day, dates.prev_day)) cal,
616 (select ORGANIZATION_ID,
617 INVENTORY_ITEM_ID,
618 INV_CATEGORY_ID,
619 UOM_CODE,
620 TIME_ID,
621 PERIOD_TYPE_ID,
622 ACTUAL_QUANTITY,
623 ACTUAL_VALUE,
624 ACTUAL_STANDARD_VALUE,
625 PLANNED_QUANTITY,
626 PLANNED_STANDARD_VALUE
627 from
628 (select
629 f.ORGANIZATION_ID,
630 f.INVENTORY_ITEM_ID,
631 f.INV_CATEGORY_ID,
632 f.UOM_CODE,
633 f.TIME_ID,
634 f.PERIOD_TYPE_ID,
635 0 ACTUAL_QUANTITY,
636 0 ACTUAL_VALUE,
637 f.ACTUAL_STANDARD_VALUE_'||l_currency_code||' ACTUAL_STANDARD_VALUE,
638 f.PLANNED_QUANTITY,
639 f.PLANNED_STANDARD_VALUE_'||l_currency_code||' PLANNED_STANDARD_VALUE
640 from OPI_PTP_SUM_F_MV f
641 where
642 f.item_cat_flag = :OPI_ITEM_CAT_FLAG
643 '||l_org_where||l_inv_cat_where||l_item_where||'
644 union all
645 select
646 f.ORGANIZATION_ID,
647 f.INVENTORY_ITEM_ID,
648 f.INV_CATEGORY_ID,
649 f.UOM_CODE,
650 f.TIME_ID,
651 f.PERIOD_TYPE_ID,
652 0 ACTUAL_QUANTITY,
653 0 ACTUAL_VALUE,
654 0 ACTUAL_STANDARD_VALUE,
655 f.PLANNED_QUANTITY,
656 f.PLANNED_STANDARD_VALUE_'||l_currency_code||' PLANNED_STANDARD_VALUE
657 from OPI_PTP_SUM_STG_MV f where
658 f.item_cat_flag = :OPI_ITEM_CAT_FLAG
659 '||l_org_where||l_inv_cat_where||l_item_where||'
660 union all
661 select
662 f.ORGANIZATION_ID,
663 f.INVENTORY_ITEM_ID,
664 f.INV_CATEGORY_ID,
665 f.UOM_CODE,
666 f.TIME_ID,
667 f.PERIOD_TYPE_ID,
668 nvl(f.PRODUCTION_QTY, 0) - nvl(f.SCRAP_QTY, 0) ACTUAL_QUANTITY,
669 nvl(f.PRODUCTION_VAL_'||l_currency_code||' , 0)
670 - nvl(f.SCRAP_VAL_'||l_currency_code||' , 0) ACTUAL_VALUE,
671 0 ACTUAL_STANDARD_VALUE,
672 0 PLANNED_QUANTITY,
673 0 PLANNED_STANDARD_VALUE
674 from OPI_SCRAP_SUM_MV /*OPI_SCR_NEST_MV*/ f
675 where
676 f.item_cat_flag = :OPI_ITEM_CAT_FLAG
680 ') fact,
677 '||l_org_where||l_inv_cat_where||l_item_where||'
678 )f
679 where '||l_org_security||
681 OPI_PTP_RPT_BND_MV bnd
682 WHERE fact.time_id = cal.time_id
683 AND fact.period_type_id = cal.period_type_id
684 AND fact.organization_id = decode(cal.organization_id, -1, fact.organization_id, cal.organization_id)
685 AND fact.organization_id = bnd.organization_id
686 GROUP BY cal.start_date) f,
687 '|| l_period_type ||' fii
688 WHERE fii.start_date = f.start_date(+)
689 AND fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
690 AND &BIS_CURRENT_ASOF_DATE
691 ORDER BY fii.start_date';
692
693 BEGIN
694 execute immediate '
695 CREATE GLOBAL TEMPORARY TABLE OPI_DBI_PTP_TRD_TMP(
696 organization_id number,
697 report_date date,
698 time_id number,
699 period_type_id number
700 ) ON COMMIT PRESERVE ROWS'
701 ;
702 EXCEPTION
703 WHEN others THEN null;
704 execute immediate 'truncate table opi_dbi_ptp_trd_tmp';
705 END;
706 BEGIN
707 if(l_period_type = 'FII_TIME_ENT_YEAR') then
708 l_nested_pattern := 119;
709 elsif(l_period_type = 'FII_TIME_ENT_QTR') then
710 l_nested_pattern := 55;
711 elsif(l_period_type = 'FII_TIME_ENT_PERIOD') then
712 l_nested_pattern := 23;
713 elsif(l_period_type = 'FII_TIME_WEEK') then
714 l_nested_pattern := 11;
715 elsif(l_period_type = 'FII_TIME_DAY') then
716 l_nested_pattern := 1;
717 else
718 l_nested_pattern := 119;
719 end if;
720 execute immediate '
721 insert into OPI_DBI_PTP_TRD_TMP
722 (organization_id,
723 report_date,
724 time_id,
725 period_type_id
726 )
727 select
728 -1 organization_id,
729 cal.report_date,
730 cal.time_id,
731 cal.period_type_id
732 FROM
733 (SELECT least(fii.end_date, :l_curr_asof_date) DAY
734 FROM '||l_period_type||' fii
735 WHERE fii.start_date BETWEEN
736 :l_curr_rpt_start_date AND :l_curr_asof_date
737 union
738 SELECT least(fii.end_date, :l_prev_asof_date) DAY
739 FROM '||l_period_type||' fii
740 WHERE fii.start_date BETWEEN
741 :l_prev_rpt_start_date AND :l_prev_asof_date
742 ) dates,
743 FII_TIME_RPT_STRUCT_V cal
744 WHERE cal.report_date = dates.day
745 AND bitand(cal.record_type_id, :l_nested_pattern) = cal.record_type_id
746 union all
747 select
748 bnd.organization_id,
749 cal.report_date,
750 cal.time_id,
751 cal.period_type_id
752 from
753 FII_TIME_RPT_STRUCT_V cal,
754 OPI_PTP_RPT_BND_MV bnd
755 WHERE cal.report_date = bnd.DATA_CLEAN_DATE
756 AND bitand(cal.record_type_id, :l_nested_pattern) = cal.record_type_id
757 ' using l_curr_asof_date, l_curr_rpt_start_date, l_curr_asof_date, l_prev_asof_date, l_prev_rpt_start_date, l_prev_asof_date, l_nested_pattern, l_nested_pattern;
758 EXCEPTION
759 WHEN others THEN null;
760 END;
761 x_custom_sql := l_stmt;
762
763 l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
764 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
765 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
766 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
767 x_custom_output.EXTEND;
768 x_custom_output(1) := l_custom_rec;
769
770 END GET_TRD_SQL;
771
772 PROCEDURE GET_CMLTV_TRD_SQL(
773 p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
774 x_custom_sql OUT NOCOPY VARCHAR2,
775 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
776 ) IS
777 l_formula_sql VARCHAR2(4000) := '';
778 l_inner_sql VARCHAR2(6000) := '';
779 l_stmt VARCHAR2(8000) := '';
780 l_period_type VARCHAR2(255) := NULL;
781 l_org VARCHAR2(255) := NULL;
782 l_org_where VARCHAR2(2000) := ' AND 1=1';
783 l_org_security VARCHAR2(2000) := '1=1';
784 l_item VARCHAR2(255) := NULL;
785 l_item_where VARCHAR2(2000) := ' AND 1=1';
786 l_inv_cat VARCHAR2(255) := NULL;
787 l_inv_cat_where VARCHAR2(2000) := ' AND 1=1';
788 l_item_cat_flag NUMBER; -- 0 for item and 1 for inv. category
789 l_currency VARCHAR2(30) := '';
790 l_currency_code VARCHAR2(2) := 'B';
791 l_period_detail VARCHAR2(30);
792 l_nested_pattern NUMBER;
793 l_period_id VARCHAR2(20);
794 l_custom_rec BIS_QUERY_ATTRIBUTES;
795 l_pmv_nested_pattern NUMBER;
796 l_curr_asof_date DATE;
797 l_curr_eft_start_date DATE;
798 l_curr_eft_end_date DATE;
799 l_period_end_date date;
800 l_period_select varchar2(200):=NULL;
801
802 l_error varchar2(255);
803 BEGIN
804
805 FOR i IN 1..p_param.COUNT
806 LOOP
807
808 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
809 THEN l_period_type := p_param(i).parameter_value;
810 END IF;
811
812 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
813 THEN l_org := p_param(i).parameter_value;
814 END IF;
815
816 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
817 IF ( p_param(i).parameter_value IS NULL OR p_param(i).parameter_value = 'All' ) THEN
818 l_inv_cat := p_param(i).parameter_value;
819 ELSE
823
820 l_inv_cat := 'Selected';
821 END IF;
822 END IF;
824 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
825 IF ( p_param(i).parameter_value IS NULL OR p_param(i).parameter_value = 'All' ) THEN
826 l_item := p_param(i).parameter_value;
827 ELSE
828 l_item := 'Selected';
829 END IF;
830 END IF;
831
832 IF(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') then
833 l_currency := p_param(i).parameter_id;
834 END IF;
835
836 IF(p_param(i).parameter_name = 'AS_OF_DATE') then
837 l_curr_asof_date := p_param(i).period_date;
838 END IF;
839
840 IF(p_param(i).parameter_name = 'BIS_CURRENT_EFFECTIVE_END_DATE') then
841 l_curr_eft_end_date := p_param(i).period_date;
842 END IF;
843
844 IF(p_param(i).parameter_name = 'BIS_CURRENT_EFFECTIVE_START_DATE') then
845 l_curr_eft_start_date := p_param(i).period_date;
846 END IF;
847 END LOOP;
848
849 IF(l_currency = '''FII_GLOBAL1''') then
850 l_currency_code := 'G';
851 ELSIF (l_currency = '''FII_GLOBAL2''') then
852 l_currency_code := 'SG';
853 END IF;
854
855 IF l_period_type = 'FII_TIME_WEEK' THEN
856 l_period_detail := 'FII_TIME_DAY_V';
857 l_period_id := 'WEEK_ID';
858 l_nested_pattern := 1;
859 l_pmv_nested_pattern := 11;
860 l_period_end_date:=fii_time_api.cwk_start(l_curr_asof_date);
861 l_period_select:= ' (bucket.start_date - :l_period_end_date) +1 name, ';
862 ELSIF l_period_type = 'FII_TIME_ENT_PERIOD' THEN
863 l_period_detail := 'FII_TIME_DAY_V';
864 l_period_id := 'ENT_PERIOD_ID';
865 l_nested_pattern := 1;
866 --l_nested_pattern := 11;
867 l_pmv_nested_pattern := 23;
868 l_period_end_date :=fii_time_api.ent_cper_start(l_curr_asof_date);
869 l_period_select:=' (bucket.start_date - :l_period_end_date) +1 name, ';
870 ELSIF l_period_type = 'FII_TIME_ENT_QTR' THEN
871 l_period_detail := 'FII_TIME_DAY_V';
872 l_period_id := 'ENT_QTR_ID';
873 l_nested_pattern := 1;
874 --l_nested_pattern := 11;
875 l_pmv_nested_pattern := 55;
876 l_period_end_date :=fii_time_api.ent_cqtr_end(l_curr_asof_date) ;
877 l_period_select :=' (bucket.start_date - :l_period_end_date) -1 name, ';
878 ELSE
879 l_period_detail := 'FII_TIME_ENT_PERIOD_V';
880 l_period_id := 'ENT_YEAR_ID';
881 l_nested_pattern := 23;
882 l_pmv_nested_pattern := 119;
883 l_period_end_date:=NULL;
884 l_period_select := ' substr(bucket.name,1,3 ) || :l_period_end_date name, ';
885 END IF;
886
887 IF ( l_org IS NULL OR l_org = 'All' ) THEN
888 l_org_security := '
889 (EXISTS
890 (SELECT 1
891 FROM org_access o
892 WHERE o.responsibility_id = fnd_global.resp_id
893 AND o.resp_application_id = fnd_global.resp_appl_id
894 AND o.organization_id = f.organization_id)
895 OR EXISTS
896 (SELECT 1
897 FROM mtl_parameters org
898 WHERE org.organization_id = f.organization_id
899 AND NOT EXISTS
900 (SELECT 1
901 FROM org_access ora
902 WHERE org.organization_id = ora.organization_id)))';
903
904 ELSE
905 --do we allow mutliple selects of org here?
906 l_org_where := ' AND f.organization_id = &ORGANIZATION+ORGANIZATION';
907 END IF;
908
909 IF ( l_inv_cat IS NULL OR l_inv_cat = 'All' ) THEN
910 l_inv_cat_where :='';
911 ELSE
912 l_inv_cat_where := ' AND f.inv_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
913 END IF;
914
915 IF ( l_item IS NULL OR l_item = 'All' ) THEN
916 l_item_where :='';
917 ELSE
918 l_item_where := ' AND f.item_org_id in (&ITEM+ENI_ITEM_ORG)';
919 END IF;
920
921 IF((l_inv_cat IS NULL OR l_inv_cat = 'All' ) AND ( l_item IS NULL OR l_item = 'All')) THEN
922 l_item_cat_flag := 3; -- no grouping on item dimension
923 ELSE
924 IF (l_item IS NULL OR l_item = 'All') THEN
925 l_item_cat_flag := 1; -- inv, category
926 ELSE
927 l_item_cat_flag := 0; -- item is needed
928 END IF;
929 END IF;
930
931 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
932 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
933
934 l_stmt :='SELECT fact.name VIEWBY,
935 CURR_ACTUAL_STANDARD_VALUE OPI_MEASURE1,
936 CURR_PLANNED_STANDARD_VALUE OPI_MEASURE2,
937 decode(sign(fact.start_date - &BIS_CURRENT_ASOF_DATE), 1, null, SUM(nvl(CURR_ACTUAL_STANDARD_VALUE, 0)) OVER (ORDER BY fact.start_date ASC ROWS UNBOUNDED PRECEDING)) AS OPI_MEASURE3,
938 SUM(nvl(CURR_PLANNED_STANDARD_VALUE, 0)) OVER (ORDER BY fact.start_date ASC ROWS UNBOUNDED PRECEDING) AS OPI_MEASURE4
939 FROM ( SELECT /*+ leading(c) push_pred(f) */
940 c.start_date,
941 c.name,
942 sum(decode(sign(&BIS_CURRENT_ASOF_DATE - c.start_date), -1, null, decode(sign(&BIS_CURRENT_ASOF_DATE - c.report_date), -1, 0, f.ACTUAL_STANDARD_VALUE))) CURR_ACTUAL_STANDARD_VALUE,
943 sum(decode(c.report_date, c.end_date, f.PLANNED_STANDARD_VALUE, 0)) CURR_PLANNED_STANDARD_VALUE
944 FROM
945 (select ORGANIZATION_ID,
946 INVENTORY_ITEM_ID,
947 INV_CATEGORY_ID,
948 TIME_ID,
952 from
949 PERIOD_TYPE_ID,
950 ACTUAL_STANDARD_VALUE,
951 PLANNED_STANDARD_VALUE
953 (select
954 f.ORGANIZATION_ID,
955 f.INVENTORY_ITEM_ID,
956 f.INV_CATEGORY_ID,
957 f.TIME_ID,
958 f.PERIOD_TYPE_ID,
959 f.ACTUAL_STANDARD_VALUE_'||l_currency_code||' ACTUAL_STANDARD_VALUE,
960 f.PLANNED_STANDARD_VALUE_'||l_currency_code||' PLANNED_STANDARD_VALUE
961 from OPI_PTP_SUM_F_MV f
962 where
963 f.item_cat_flag = :OPI_ITEM_CAT_FLAG
964 '||l_org_where||l_inv_cat_where||l_item_where||
965 ' union all
966 select
967 f.ORGANIZATION_ID,
968 f.INVENTORY_ITEM_ID,
969 f.INV_CATEGORY_ID,
970 f.TIME_ID,
971 f.PERIOD_TYPE_ID,
972 0 ACTUAL_STANDARD_VALUE,
973 f.PLANNED_STANDARD_VALUE_'||l_currency_code||' PLANNED_STANDARD_VALUE
974 from OPI_PTP_SUM_STG_MV f
975 where
976 f.item_cat_flag = :OPI_ITEM_CAT_FLAG
977 '||l_org_where||l_inv_cat_where||l_item_where||
978 ') f
979 where '||l_org_security||
980 ') f,
981 OPI_DBI_PTP_CMLTV_TMP c
982 WHERE f.time_id (+) = c.time_id
983 AND f.period_type_id (+) = c.period_type_id
984 GROUP BY
985 c.start_date,
986 c.name
987 ORDER BY start_date ASC
988 ) fact';
989
990 BEGIN
991 execute immediate '
992 CREATE GLOBAL TEMPORARY TABLE OPI_DBI_PTP_CMLTV_TMP (
993 start_date DATE,
994 end_date DATE,
995 name VARCHAR2(100),
996 time_id NUMBER,
997 report_date DATE,
998 period_type_id NUMBER,
999 report_start_date DATE
1000 ) ON COMMIT PRESERVE ROWS'
1001 ;
1002 EXCEPTION
1006 BEGIN
1003 WHEN others THEN null;
1004 execute immediate 'truncate table opi_dbi_ptp_cmltv_tmp';
1005 END;
1007 execute immediate '
1008 insert into OPI_DBI_PTP_CMLTV_TMP
1009 (start_date,
1010 end_date,
1011 name,
1012 time_id,
1013 report_date,
1014 period_type_id,
1015 report_start_date
1016 )
1017 select
1018 bucket.start_date,
1019 bucket.end_date,
1020 -- bucket.name,
1021 '|| l_period_select || '
1022 cal.time_id,
1023 cal.report_date,
1024 cal.period_type_id,
1025 bucket.report_start_date report_start_date
1026 from
1027 FII_TIME_RPT_STRUCT_V cal,
1028 (SELECT t1.start_date START_DATE,
1029 least(t1.end_date, :l_eft_end_date) END_DATE,
1030 t1.value NAME,
1031 least(t1.end_date, :l_eft_end_date) PDAY,
1032 least(t1.end_date, :l_curr_asof_date) ADAY,
1033 :l_eft_start_date REPORT_START_DATE
1034 FROM '||l_period_detail||' t1
1035 WHERE t1.start_date BETWEEN :l_eft_start_date AND :l_eft_end_date
1036 OR
1037 t1.end_date BETWEEN :l_eft_start_date AND :l_eft_end_date
1038 ) bucket
1039 where cal.report_date in (bucket.pday, bucket.aday)
1040 AND decode(sign(bucket.start_date - bucket.report_start_date), -1, bitand(cal.record_type_id, :l_pmv_nested_pattern), bitand(cal.record_type_id, :l_nested_pattern)) = cal.record_type_id
1041 ' using l_period_end_date,l_curr_eft_end_date, l_curr_eft_end_date, l_curr_asof_date, l_curr_eft_start_date, l_curr_eft_start_date, l_curr_eft_end_date, l_curr_eft_start_date, l_curr_eft_end_date, l_pmv_nested_pattern, l_nested_pattern;
1042 EXCEPTION
1043 WHEN others THEN null;
1044 END;
1045
1046 x_custom_sql := l_stmt;
1047
1048 l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
1049 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
1050 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1051 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1052 x_custom_output.EXTEND;
1053 x_custom_output(1) := l_custom_rec;
1054
1055 l_custom_rec.attribute_name := ':OPI_NESTED_PATTERN';
1056 l_custom_rec.attribute_value := l_nested_pattern;
1057 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1058 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1059 x_custom_output.EXTEND;
1060 x_custom_output(2) := l_custom_rec;
1061
1062 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1063 l_custom_rec.attribute_value := 'TIME+'||substrb(l_period_detail, 1, instrb(l_period_detail, '_', -1, 1)-1);
1064 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
1065 x_custom_output.EXTEND;
1066 x_custom_output(3) := l_custom_rec;
1067
1068 END GET_CMLTV_TRD_SQL;
1069
1070 END OPI_DBI_PTP_RPT_PKG;