[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_COR_PKG
Source
1 PACKAGE BODY ENI_DBI_COR_PKG AS
2 /*$Header: ENICORPB.pls 120.2 2006/03/23 04:37:02 pgopalar noship $*/
3
4 PROCEDURE get_sql
5 (
6 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
7 , x_custom_sql OUT NOCOPY VARCHAR2
8 , x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
9 ) IS
10
11 l_custom_rec BIS_QUERY_ATTRIBUTES;
12 l_err_msg VARCHAR2(500);
13 l_period_type VARCHAR2(140);
14 l_sql_stmt VARCHAR2(15000);
15 l_period_bitand NUMBER;
16 l_view_by VARCHAR2(200);
17 l_as_of_date DATE;
18 l_prev_as_of_date DATE;
19 l_report_start DATE;
20 l_cur_period NUMBER;
21 l_days_into_period NUMBER;
22 l_comp_type VARCHAR2(200);
23 l_category VARCHAR2(200);
24 l_item VARCHAR2(200);
25 l_org VARCHAR2(200);
26 l_id_column VARCHAR2(130);
27 l_order_by VARCHAR2(200);
28 l_drill VARCHAR2(130);
29 l_status VARCHAR2(130);
30 l_priority VARCHAR2(130);
31 l_reason VARCHAR2(130);
32 l_lifecycle_phase VARCHAR2(130);
33 l_currency VARCHAR2(130);
34 l_bom_type VARCHAR2(130);
35 l_type VARCHAR2(130);
36 l_manager VARCHAR2(130);
37 l_org_where VARCHAR2(100);
38 l_org_where_dn VARCHAR2(100);
39 l_item_where VARCHAR2(100);
40 l_item_where_dn VARCHAR2(100);
41 l_priority_where VARCHAR2(100);
42 l_priority_where_dn VARCHAR2(100);
43 l_status_where VARCHAR2(100);
44 l_status_where_dn VARCHAR2(100);
45 l_type_where VARCHAR2(100);
46 l_type_where_dn VARCHAR2(100);
47 l_reason_where VARCHAR2(100);
48 l_reason_where_dn VARCHAR2(100);
49 l_lob VARCHAR2(1000);
50 l_from_clause VARCHAR2(1000);
51 l_where_clause VARCHAR2(500);
52 l_where_clause_dn VARCHAR2(500);
53 l_group_by_clause VARCHAR2(500);
54 l_concat_var VARCHAR2(1000);
55 l_lookup VARCHAR2(100);
56 l_lookup_alias VARCHAR2(100);
57 l_group_by VARCHAR2(100);
58 l_select VARCHAR2(100);
59 l_select_id VARCHAR2(100);
60 l_cursor INTEGER;
61 l_new_url VARCHAR2(1000);
62 l_impl_url VARCHAR2(1000);
63 l_open_url VARCHAR2(1000);
64 l_canc_url VARCHAR2(1000);
65 l_new_url_time VARCHAR2(1000);
66 l_impl_url_time VARCHAR2(1000);
67 l_open_url_time VARCHAR2(1000);
68 l_canc_url_time VARCHAR2(1000);
69 l_avg_age_url VARCHAR2(1000);
70 l_cycle_url VARCHAR2(1000);
71 l_avg_age_url_time VARCHAR2(1000);
72 l_cycle_url_time VARCHAR2(1000);
73 l_item_from_clause VARCHAR2(1000);
74 l_select_id_list VARCHAR2(1000);
75 l_outer_join_condition VARCHAR2(1000);
76 l_description VARCHAR2(1000);
77 l_outer_group_by VARCHAR2(1000);
78
79
80 BEGIN
81
82 -- TODO Change the nvl_date to bind parameter in Non TIME VIEWBY
83
84 l_open_url:='''pFunctionName=ENI_DBI_COL_OPEN_R&pCustomView=ENI_DBI_COL_CV1&REPORTED=OPEN&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y''';
85 l_new_url:= '''pFunctionName=ENI_DBI_COL_NEW_R&pCustomView=ENI_DBI_COL_CV3&REPORTED=NEW&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y''';
86 l_impl_url:='''pFunctionName=ENI_DBI_COL_IMPL_R&pCustomView=ENI_DBI_COL_CV2&REPORTED=IMPL&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y''';
87 l_canc_url:='''pFunctionName=ENI_DBI_COL_CANC_R&pCustomView=ENI_DBI_COL_CV5&REPORTED=CANC&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y''';
88 l_avg_age_url:='''pFunctionName=ENI_DBI_COA_R&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ENI_CHANGE_MGMT_TYPE+ENI_CHANGE_MGMT_TYPE&pParamIds=Y''';
89 l_cycle_url:='''pFunctionName=ENI_DBI_COC_R&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ENI_CHANGE_MGMT_TYPE+ENI_CHANGE_MGMT_TYPE&pParamIds=Y''';
90
91 /* Bug : 3465553
92 l_open_url_time:='''pFunctionName=ENI_DBI_COL_OPEN_R&pCustomView=ENI_DBI_COL_CV1&REPORTED=OPEN'||
93 '&start_date=''||to_char(s.start_date,''dd-mm-yyyy'')||''&end_date=''||to_char(s.c_end_date,''dd-mm-yyyy'')||''&''||''AS_OF_DATE=''||to_char(s.c_end_date,''dd-mm-yyyy'')';
94 l_new_url_time:= '''pFunctionName=ENI_DBI_COL_NEW_R&pCustomView=ENI_DBI_COL_CV3&REPORTED=NEW'||
95 '&start_date=''||to_char(s.start_date,''dd-mm-yyyy'')||''&end_date=''||to_char(s.c_end_date,''dd-mm-yyyy'')||''&''||''AS_OF_DATE=''||to_char(s.c_end_date,''dd-mm-yyyy'')';
96 l_impl_url_time:='''pFunctionName=ENI_DBI_COL_IMPL_R&pCustomView=ENI_DBI_COL_CV2&REPORTED=IMPL'||
97 '&start_date=''||to_char(s.start_date,''dd-mm-yyyy'')||''&end_date= ''||to_char(s.c_end_date,''dd-mm-yyyy'')||''&''||''AS_OF_DATE=''||to_char(s.c_end_date,''dd-mm-yyyy'')';
98 l_canc_url_time:='''pFunctionName=ENI_DBI_COL_CANC_R&pCustomView=ENI_DBI_COL_CV5&REPORTED=CANC'||
99 '&start_date=''||to_char(s.start_date,''dd-mm-yyyy'')||''&end_date= ''||to_char(s.c_end_date,''dd-mm-yyyy'')||''&''||''AS_OF_DATE=''||to_char(s.c_end_date,''dd-mm-yyyy'')';
100
101 Bug : 3465553
102 */
103 l_open_url_time :=null;
104 l_new_url_time :=null;
105 l_impl_url_time :=null;
106 l_avg_age_url_time :=null;
107 l_cycle_url_time :=null;
108 /*l_avg_age_url_time:='''pFunctionName=ENI_DBI_COA_R'''||
109 '||''&''||''AS_OF_DATE=''||to_char(s.c_end_date,''dd-mm-yyyy'')||''&VIEW_BY=ENI_CHANGE_MGMT_TYPE+ENI_CHANGE_MGMT_TYPE''';
110 l_cycle_url_time:='''pFunctionName=ENI_DBI_COC_R'''||
111 '||''&''||''AS_OF_DATE=''||to_char(s.c_end_date,''dd-mm-yyyy'')||''&VIEW_BY=ENI_CHANGE_MGMT_TYPE+ENI_CHANGE_MGMT_TYPE''';
112 */
113
114 ENI_DBI_UTIL_PKG.get_parameters( p_page_parameter_tbl
115 , l_period_type
116 , l_period_bitand
117 , l_view_by
118 , l_as_of_date
119 , l_prev_as_of_date
120 , l_report_start
121 , l_cur_period
122 , l_days_into_period
123 , l_comp_type
124 , l_category
125 , l_item
126 , l_org
127 , l_id_column
128 , l_order_by
129 , l_drill
130 , l_status
131 , l_priority
132 , l_reason
133 , l_lifecycle_phase
134 , l_currency
135 , l_bom_type
136 , l_type
137 , l_manager
138 , l_lob
139 );
140
141
142 l_order_by := UPPER(l_order_by);
143 IF INSTR(l_order_by,'.') > 0 THEN
144 l_order_by := SUBSTR(l_order_by,INSTR(l_order_by,'.')+1);
145 END IF;
146
147 IF l_order_by like '%AS' THEN
148 l_order_by := l_order_by||'C';
149 ELSIF l_order_by like '%DES' THEN
150 l_order_by := l_order_by || 'C';
151 END IF;
152
153 --If the item is null, then we return no rows
154 if(l_view_by <> 'ITEM+ENI_ITEM_ORG')
155 then
156 IF l_item IS NULL OR l_item = 'All' THEN
157 l_sql_stmt :=
158 'SELECT null as VIEWBY,
159 null as ENI_MEASURE64,
160 null as ENI_MEASURE1,
161 null as ENI_MEASURE12,
162 null as ENI_MEASURE6,
163 null as ENI_MEASURE2,
164 null as ENI_MEASURE3,
165 null as ENI_MEASURE7,
166 null as ENI_MEASURE4,
167 null as ENI_MEASURE5,
168 null as ENI_MEASURE40,
169 null as ENI_MEASURE41,
170 null as ENI_MEASURE42,
171 null as ENI_MEASURE9,
172 null as ENI_MEASURE13,
173 null as ENI_MEASURE8,
174 null as ENI_MEASURE10,
175 null as ENI_MEASURE14,
176 null as ENI_MEASURE15,
177 null as ENI_MEASURE27,
178 null as ENI_MEASURE28,
179 null as ENI_MEASURE16,
180 null as ENI_MEASURE17,
181 null as ENI_MEASURE20,
182 null as ENI_MEASURE18,
183 null as ENI_MEASURE21,
184 null as ENI_MEASURE22,
185 null as ENI_MEASURE23,
186 null as ENI_MEASURE24,
187 null as ENI_MEASURE25,
188 null as ENI_MEASURE26,
189 null as ENI_MEASURE43,
190 null as ENI_MEASURE44,
191 null as ENI_MEASURE45,
192 null as ENI_MEASURE30,
193 null as ENI_MEASURE31,
194 null as ENI_MEASURE35,
195 null as ENI_MEASURE36,
196 null as ENI_MEASURE37,
197 null as ENI_MEASURE38,
198 null as ENI_MEASURE39,
199 null as ENI_MEASURE47,
200 null as ENI_MEASURE48,
201 null as ENI_MEASURE49,
202 null as ENI_MEASURE50,
203 null as ENI_MEASURE56,
204 null as ENI_MEASURE57,
205 null as ENI_MEASURE58,
206 null as ENI_MEASURE59,
207 null as ENI_MEASURE61,
208 null as ENI_MEASURE62,
209 null as ENI_MEASURE63,
210 null as ENI_MEASURE53,
211 null as ENI_MEASURE54
212 FROM DUAL';
213
214 END IF;
215 END IF;
216 -- set where clause
217 IF l_org IS NOT NULL AND l_org <> 'All' THEN
218 --Bug 5083876 -Start Code
219 l_org_where := ' AND edcs.organization_id(+) = :ORGANIZATION_ID';
220 l_org_where_dn := ' AND edcs.organization_id = :ORGANIZATION_ID';
221 -- l_org_where := ' AND edcs.organization_id(+) = ' || REPLACE(l_org,'''');
222 -- l_org_where_dn := ' AND edcs.organization_id = ' || REPLACE(l_org,'''');
223 --Bug 5083876 - End Code
224 END IF;
225
226 IF l_item IS NOT NULL AND l_item <> 'All' THEN
227 l_item_where := ' AND edcs.item_id(+) = :ITEM_ID';
228 l_item_where_dn := ' AND edcs.item_id = :ITEM_ID';
229 -- l_item_where := ' AND edcs.item_id(+) = ' || REPLACE(l_item,'''');
230 -- l_item_where_dn := ' AND edcs.item_id = ' || REPLACE(l_item,'''');
231 END IF;
232
233 IF (l_status IS NULL OR l_status = 'All') AND l_view_by LIKE '%STATUS' THEN
234 l_status_where := ' AND edcs.status_type IS NOT NULL';
235 l_status := '';
236 ELSIF (l_status IS NULL OR l_status = 'All') THEN
237 l_status_where := ' AND edcs.status_type IS NULL';
238 l_status_where_dn := '';
239 l_status := '';
240 ELSE
241 l_status_where := ' AND edcs.status_type(+) = :STATUS_ID';
242 l_status_where_dn := ' AND edcs.status_type = :STATUS_ID';
243 END IF;
244
245 IF (l_priority IS NULL OR l_priority = 'All') AND l_view_by LIKE '%PRIORITY' THEN
246 l_priority_where := ' AND edcs.priority_code IS NOT NULL';
247 l_priority := '';
248 ELSIF (l_priority IS NULL OR l_priority = 'All') THEN
249 l_priority_where := ' AND edcs.priority_code IS NULL';
250 l_priority_where_dn := '';
251 l_priority := '';
252 ELSE
253 l_priority_where := ' AND edcs.priority_code(+) = :PRIORITY_ID';
254 l_priority_where_dn := ' AND edcs.priority_code = :PRIORITY_ID';
255 END IF;
256
257 IF (l_reason IS NULL OR l_reason = 'All') AND l_view_by LIKE '%REASON' THEN
258 l_reason_where := ' AND edcs.reason_code IS NOT NULL';
259 l_reason := '';
260 ELSIF (l_reason IS NULL OR l_reason = 'All') THEN
261 l_reason_where := ' AND edcs.reason_code IS NULL';
262 l_reason_where_dn := '';
263 l_reason := '';
264 ELSE
265 l_reason_where := ' AND edcs.reason_code(+) = :REASON_ID'; --|| l_priority;
266 l_reason_where_dn := ' AND edcs.reason_code = :REASON_ID'; --|| l_priority;
267 END IF;
268
269 IF (l_type IS NULL OR l_type = 'All') AND l_view_by LIKE '%TYPE' THEN
270 l_type_where := ' AND edcs.change_order_type_id IS NOT NULL';
271 l_type := '';
272 ELSIF l_type IS NULL OR l_type = 'All' THEN
273 l_type_where := ' AND edcs.change_order_type_id IS NULL';
274 l_type_where_dn := '';
275 l_type := '';
276 ELSE
277 l_type_where := ' AND edcs.change_order_type_id(+) = :TYPE_ID';
281 l_where_clause_dn := l_item_where_dn
278 l_type_where_dn := ' AND edcs.change_order_type_id = :TYPE_ID';
279 END IF;
280
282 || l_org_where_dn
283 || l_status_where_dn
284 || l_priority_where_dn
285 || l_reason_where_dn
286 || l_type_where_dn;
287
288 -- Time view by
289 IF substr(l_view_by, 1, 5) = 'TIME+' AND (l_item IS NOT NULL AND l_item <> 'All' ) THEN
290
291 eni_dbi_util_pkg.get_time_clauses(
292 'A',
293 'edcs',
294 l_period_type,
295 l_period_bitand,
296 l_as_of_date,
297 l_prev_as_of_date,
298 l_report_start,
299 l_cur_period,
300 l_days_into_period,
301 l_comp_type,
302 l_id_column,
303 l_from_clause,
304 l_where_clause,
305 l_group_by_clause,
306 'ROLLING'
307 );
308
309 l_where_clause := l_where_clause || l_item_where
310 || l_org_where
311 || l_status_where
312 || l_priority_where
313 || l_reason_where
314 || l_type_where;
315
316 IF l_order_by like '%START_DATE%' THEN
317 IF l_order_by like '%ASC' THEN
318 l_order_by := 'S.TIME_ID ASC';
319 ELSE
320 l_order_by := 'S.TIME_ID DESC';
321 END IF;
322 ELSE
323 l_order_by := 'S.TIME_ID ASC';
324 END IF;
325
326 l_sql_stmt := '
327 SELECT
328 s.name AS VIEWBY
329 , NULL AS ENI_MEASURE64
330 , c.C_OPEN_SUM AS ENI_MEASURE1
331 , nvl(c.C_OPEN_SUM,0) AS ENI_MEASURE12
332 , c.P_OPEN_SUM AS ENI_MEASURE6
333 , (((c.C_OPEN_SUM - c.P_OPEN_SUM)
334 /DECODE(c.P_OPEN_SUM,0,NULL,c.P_OPEN_SUM))* 100)
335 AS ENI_MEASURE2
336 , (c.c_avg_age/DECODE(c.c_open_sum,0,NULL,c.c_open_sum))
337 AS ENI_MEASURE3
338 , (c.p_avg_age/DECODE(c.p_open_sum,0,NULL,c.p_open_sum))
339 AS ENI_MEASURE7
340 , ((((c.c_avg_age/DECODE(c.c_open_sum,0,NULL,c.c_open_sum))
341 -(c.p_avg_age/DECODE(c.p_open_sum,0,NULL,c.p_open_sum)))
342 /DECODE((c.p_avg_age
343 /DECODE(c.p_open_sum,0,NULL,c.p_open_sum))
344 ,0,NULL,
345 (c.p_avg_age/DECODE(c.p_open_sum,0,NULL,c.p_open_sum)))) * 100)
346 AS ENI_MEASURE4
347 , c.c_bucket1 AS ENI_MEASURE5
348 , c.c_bucket2 AS ENI_MEASURE40
349 , c.c_bucket3 AS ENI_MEASURE41
350 , c.c_bucket4 AS ENI_MEASURE42
351 , s.C_NEW_SUM AS ENI_MEASURE9
352 , nvl(s.C_NEW_SUM,0) AS ENI_MEASURE13
353 , s.P_NEW_SUM AS ENI_MEASURE8
354 , (((s.C_NEW_SUM - s.P_NEW_SUM)/DECODE(s.P_NEW_SUM,0,NULL,s.P_NEW_SUM))*100)
355 AS ENI_MEASURE10
356 , s.C_CANL_SUM AS ENI_MEASURE14
357 , s.C_IMPL_SUM AS ENI_MEASURE15
358 , s.P_CANL_SUM AS ENI_MEASURE27
359 , s.P_CANL_SUM AS ENI_MEASURE28
360 , ((((s.C_CANL_SUM+s.C_IMPL_SUM)-(s.P_CANL_SUM+s.P_IMPL_SUM))
361 /DECODE((s.P_CANL_SUM+s.P_IMPL_SUM),0,NULL,(s.P_CANL_SUM+s.P_IMPL_SUM))) * 100)
362 AS ENI_MEASURE16
363 , s.C_CYCL_SUM/DECODE(s.C_CYCL_CNT,0,NULL,s.C_CYCL_CNT)
364 AS ENI_MEASURE17
365 , s.P_CYCL_SUM/DECODE(s.P_CYCL_CNT,0,NULL,s.P_CYCL_CNT)
366 AS ENI_MEASURE20
367 , ((((s.C_CYCL_SUM/DECODE(s.C_CYCL_CNT,0,NULL,s.C_CYCL_CNT)
368 )
369 -(s.P_CYCL_SUM/DECODE(s.P_CYCL_CNT,0,NULL,s.P_CYCL_CNT))
370 )
371 /DECODE((s.P_CYCL_SUM/DECODE(s.P_CYCL_CNT,0,NULL,s.P_CYCL_CNT)),0,NULL,
372 (s.P_CYCL_SUM/DECODE(s.P_CYCL_CNT,0,NULL,s.P_CYCL_CNT)))
373 )*100
374 ) AS ENI_MEASURE18
375 , NVL(s.C_CANL_SUM,0)+NVL(s.C_IMPL_SUM,0) AS ENI_MEASURE21
379 , NULL AS ENI_MEASURE25
376 , NULL AS ENI_MEASURE22
377 , NULL AS ENI_MEASURE23
378 , NULL AS ENI_MEASURE24
380 , NULL AS ENI_MEASURE26
381 , NULL AS ENI_MEASURE43
382 , NULL AS ENI_MEASURE44
383 , NULL AS ENI_MEASURE45
384 , NULL AS ENI_MEASURE30
385 , NULL AS ENI_MEASURE31
386 , NULL AS ENI_MEASURE35
387 , NULL AS ENI_MEASURE36
388 , NULL AS ENI_MEASURE37
389 , NULL AS ENI_MEASURE38
390 , NULL AS ENI_MEASURE39
391 , NULL AS ENI_MEASURE47
392 , NULL AS ENI_MEASURE48
393 , NULL AS ENI_MEASURE49
394 , NULL AS ENI_MEASURE50
395 , NULL AS ENI_MEASURE56
396 , NULL AS ENI_MEASURE57
397 , NULL AS ENI_MEASURE58
398 , NULL as ENI_MEASURE59
399 , NULL as ENI_MEASURE61
400 , NULL as ENI_MEASURE62
401 , NULL as ENI_MEASURE63
402 , NULL as ENI_MEASURE53
403 , NULL as ENI_MEASURE54
404 FROM
405 (SELECT
406 t.name as name,t.start_date,t.c_end_date,t.c_end_date as time_id
407 , SUM(CASE WHEN ftrs.report_date = t.c_end_date
408 THEN edcs.new_sum
409 ELSE null
410 END)
411 AS C_NEW_SUM
412 , SUM(CASE WHEN ftrs.report_date = t.p_end_date
413 THEN edcs.new_sum
414 ELSE 0
415 END)
416 AS P_NEW_SUM
417 , SUM(CASE WHEN ftrs.report_date = t.c_end_date
418 THEN edcs.implemented_sum
419 ELSE null
420 END)
421 AS C_IMPL_SUM
422 , SUM(CASE WHEN ftrs.report_date = t.p_end_date
423 THEN edcs.implemented_sum
424 ELSE 0
425 END)
426 AS P_IMPL_SUM
427 , SUM(CASE WHEN ftrs.report_date = t.c_end_date
428 THEN edcs.cancelled_sum
429 ELSE null
430 END)
431 AS C_CANL_SUM
432 , SUM(CASE WHEN ftrs.report_date = t.p_end_date
433 THEN edcs.cancelled_sum
434 ELSE 0
435 END)
436 AS P_CANL_SUM
437 , SUM(CASE WHEN ftrs.report_date = t.c_end_date
438 THEN edcs.cycle_time_sum
439 ELSE 0
440 END)
441 AS C_CYCL_SUM
442 , SUM(CASE WHEN ftrs.report_date = t.p_end_date
443 THEN edcs.cycle_time_sum
444 ELSE 0
445 END)
446 AS P_CYCL_SUM
447 , SUM(CASE WHEN ftrs.report_date = t.c_end_date
448 THEN edcs.cycle_time_cnt
449 ELSE 0
450 END)
451 AS C_CYCL_CNT
452 , SUM(CASE WHEN ftrs.report_date = t.p_end_date
453 THEN edcs.cycle_time_cnt
454 ELSE 0
455 END)
456 AS P_CYCL_CNT
457 FROM eni_dbi_co_sum_mv edcs
458 , '|| l_from_clause ||
459 ' WHERE '|| l_where_clause ||
460 ' GROUP BY ' || l_group_by_clause||'
461 ) s';
462
463 eni_dbi_util_pkg.get_time_clauses(
464 'I',
465 'edcs',
466 l_period_type,
467 l_period_bitand,
468 l_as_of_date,
469 l_prev_as_of_date,
470 l_report_start,
471 l_cur_period,
472 l_days_into_period,
473 l_comp_type,
474 l_id_column,
475 l_from_clause,
476 l_where_clause,
477 l_group_by_clause,
478 'ROLLING'
479 );
480
481 l_sql_stmt := l_sql_stmt ||'
482 , (SELECT
483 t.name,t.c_end_date AS time_id
484 , SUM(CASE WHEN
485 (NVL(edcs.cancellation_date, NVL(edcs.implementation_date,:NVL_DATE)
486 ) > t.c_end_date
487 ) AND edcs.creation_date <= t.c_end_date
488 THEN edcs.cnt ELSE null
489 END) AS C_OPEN_SUM
490 , SUM(CASE WHEN
491 (NVL(edcs.cancellation_date, NVL(edcs.implementation_date,:NVL_DATE)
492 ) > t.p_end_date
493 ) AND edcs.creation_date <= t.p_end_date
494 THEN edcs.cnt ELSE 0
495 END) AS P_OPEN_SUM
496 , SUM(CASE WHEN
497 NVL(edcs.cancellation_date,
498 NVL(edcs.implementation_date,:NVL_DATE)
499 ) > t.c_end_date
500 AND edcs.creation_date <= t.c_end_date
501 THEN
502 (CASE WHEN
503 (LEAST(NVL(edcs.cancellation_date,
504 NVL(edcs.implementation_date,t.c_end_date)
505 ), t.c_end_date) - edcs.creation_date)
506 BETWEEN 0 AND 1
507 THEN edcs.CNT ELSE null END)
508 ELSE null END)
509 AS c_bucket1
510 , SUM(CASE WHEN
511 NVL(edcs.cancellation_date,
512 NVL(edcs.implementation_date,:NVL_DATE)
513 ) > t.c_end_date
514 AND edcs.creation_date <= t.c_end_date
515 THEN
516 (CASE WHEN
517 (LEAST(NVL(edcs.cancellation_date,
518 NVL(edcs.implementation_date,t.c_end_date)
519 ), t.c_end_date) - edcs.creation_date)
520 BETWEEN 2 AND 5
521 THEN edcs.cnt ELSE null END)
522 ELSE null END)
523 AS c_bucket2
524 , SUM(CASE WHEN
528 AND edcs.creation_date <= t.c_end_date
525 NVL(edcs.cancellation_date,
526 NVL(edcs.implementation_date,:NVL_DATE)
527 ) > t.c_end_date
529 THEN
530 (CASE WHEN
531 (LEAST(NVL(edcs.cancellation_date,
532 NVL(edcs.implementation_date,t.c_end_date)
533 ), t.c_end_date ) - edcs.creation_date)
534 BETWEEN 6 and 10
535 THEN edcs.cnt ELSE null END)
536 ELSE null END)
537 AS c_bucket3
538 , SUM(CASE WHEN
539 NVL(edcs.cancellation_date,
540 NVL(edcs.implementation_date,:NVL_DATE)
541 ) > t.c_end_date
542 AND edcs.creation_date <= t.c_end_date
543 THEN
544 (CASE WHEN
545 (LEAST(NVL(edcs.cancellation_date,
546 NVL(edcs.implementation_date,t.c_end_date)
547 ), t.c_end_date) - edcs.creation_date)
548 > 10
549 THEN edcs.cnt ELSE null END)
550 ELSE null END)
551 AS c_bucket4
552 , SUM(CASE WHEN
553 NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
554 > t.c_end_date
555 AND edcs.creation_date <= t.c_end_date
556 THEN
557 ((t.c_end_date - edcs.creation_date) * edcs.cnt)
558 ELSE 0 END)
559 AS c_avg_age
560 , SUM(CASE WHEN
561 NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
562 > t.p_end_date
563 AND edcs.creation_date <= t.p_end_date
564 THEN
565 ((t.p_end_date - edcs.creation_date) * edcs.cnt)
566 ELSE 0 END)
567 AS p_avg_age
568 FROM eni_dbi_co_dnum_mv edcs
569 , '|| l_from_clause || '
570 WHERE
571 1 = 1
572 AND
573 edcs.creation_date <= &BIS_CURRENT_ASOF_DATE
574 ' || l_where_clause_dn || '
575 GROUP BY
576 ' || l_group_by_clause || '
577 ) c
578 WHERE
579 s.time_id = c.time_id(+)
580 ORDER BY '||l_order_by;
581
582 ELSIF ((l_view_by = 'ITEM+ENI_ITEM_ORG') OR(l_item IS NOT NULL AND l_item <> 'All')) THEN -- non-time view-by
583
584 l_where_clause := l_where_clause || l_item_where
585 || l_org_where
586 || l_status_where
587 || l_priority_where
588 || l_reason_where
589 || l_type_where;
590
591
592 IF l_view_by = 'ENI_CHANGE_MGMT_TYPE+ENI_CHANGE_MGMT_TYPE' THEN
593
594 l_lookup := 'eni_chg_mgmt_type_v ecmt';
595 l_lookup_alias := 'ecmt';
596 l_group_by := 'edcs.change_order_type_id';
597 l_select := 'ecmt.value';
598 l_select_id := l_group_by;
599 l_select_id_list := l_group_by || ' as viewby_id ';
600 l_outer_join_condition:= 'dnum_sum.viewby_id = '|| l_lookup_alias || '.id(+)';
601 l_description:=' null as ENI_MEASURE64 ';
602 l_outer_group_by:= l_select ||' , viewby_id,'|| l_lookup_alias || '.id';
603
604 ELSIF l_view_by = 'ENI_CHANGE_MGMT_STATUS+ENI_CHANGE_MGMT_STATUS' THEN
605
606 l_lookup := 'eni_chg_mgmt_status_v ecms';
607 l_lookup_alias := 'ecms';
608 l_group_by := 'edcs.status_type';
609 l_select := 'ecms.value';
610 l_select_id := l_group_by;
611 l_select_id_list := l_group_by || ' as viewby_id ';
612 l_outer_join_condition:= 'dnum_sum.viewby_id = '|| l_lookup_alias || '.id(+)';
613 l_description:=' null as ENI_MEASURE64 ';
614 l_outer_group_by:= l_select ||' , viewby_id,'|| l_lookup_alias || '.id';
615
616
617 ELSIF l_view_by = 'ENI_CHANGE_MGMT_REASON+ENI_CHANGE_MGMT_REASON' THEN
618
619 l_lookup := 'eni_chg_mgmt_reason_v ecmr';
620 l_lookup_alias := 'ecmr';
621 l_group_by := 'edcs.reason_code';
622 l_select := 'ecmr.value';
623 l_select_id := l_group_by;
624 l_select_id_list := l_group_by || ' as viewby_id ';
625 l_outer_join_condition:= 'dnum_sum.viewby_id = '|| l_lookup_alias || '.id(+)';
626 l_description:=' null as ENI_MEASURE64 ';
627 l_outer_group_by:= l_select ||' , viewby_id,'|| l_lookup_alias || '.id';
628
629 ELSIF l_view_by = 'ENI_CHANGE_MGMT_PRIORITY+ENI_CHANGE_MGMT_PRIORITY' THEN
630
631 l_lookup := 'eni_chg_mgmt_priority_v ecmp';
632 l_lookup_alias := 'ecmp';
633 l_group_by := 'edcs.priority_code';
634 l_select:=' ecmp.value ';
635 l_select_id := l_group_by;
636 l_select_id_list := l_group_by || ' as viewby_id ';
637 l_outer_join_condition:= 'dnum_sum.viewby_id = '|| l_lookup_alias || '.id(+)';
638 l_description:=' null as ENI_MEASURE64 ';
639 l_outer_group_by:= l_select ||' , viewby_id,'|| l_lookup_alias || '.id';
640
641 ELSIF l_view_by = 'ITEM+ENI_ITEM_ORG' THEN
642 IF(l_item Is NULL or l_item = 'All')
643 THEN
644 l_where_clause:=l_where_clause||' and edcs.organization_id(+) = ecmp.organization_id
648 l_item_from_clause:=' , eni_item_org_v ecmp';
645 and edcs.item_id(+) = ecmp.inventory_item_id';
646 l_where_clause_dn:=l_where_clause_dn||' and edcs.organization_id(+) = ecmp.organization_id
647 and edcs.item_id(+) = ecmp.inventory_item_id';
649 END IF;
650 l_lookup := 'eni_item_org_v ecmp';
651 l_lookup_alias := 'ecmp';
652 l_group_by := 'edcs.item_id,edcs.organization_id ';
653 l_select := 'ecmp.value ';
654 l_select_id := l_group_by;
655 l_select_id_list := 'edcs.item_id as viewby_id , edcs.organization_id as org_viewby_id ';
656 l_outer_join_condition:='dnum_sum.viewby_id = ecmp.inventory_item_id(+) and
657 dnum_sum.org_viewby_id = ecmp.organization_id(+) ';
658 l_description:=' ecmp.description as ENI_MEASURE64 ';
659 l_outer_group_by:= l_select ||' , viewby_id , ecmp.description ,'|| l_lookup_alias || '.id';
660
661 END IF;
662
663 /* adhachol :
664 Bug : 3223904
665 Modified the Grand Total calculation to do sum/cnt ..previously it was the respective
666 sums/sums hence getting the wrong result
667 */
668
669 l_sql_stmt := '
670 SELECT ' || l_select || '
671 AS VIEWBY
672 , '||l_description||'
673 , '|| l_lookup_alias || '.id AS VIEWBYID
674 , SUM(c_open_sum) AS ENI_MEASURE1
675 , SUM(c_open_sum) AS ENI_MEASURE12
676 , SUM(p_open_sum) AS ENI_MEASURE6
677 , ((SUM(c_open_sum)-SUM(p_open_sum))
678 /DECODE(SUM(p_open_sum),0,NULL,SUM(p_open_sum))
679 ) * 100
680 AS ENI_MEASURE2
681 , SUM(c_open_days_sum)
682 /DECODE(SUM(c_open_sum),0,NULL,SUM(c_open_sum))
683 AS ENI_MEASURE3
684 , SUM(p_open_days_sum)
685 /DECODE(SUM(p_open_sum),0,NULL,SUM(p_open_sum))
686 AS ENI_MEASURE7
687 , (((SUM(c_open_days_sum)
688 /DECODE(SUM(c_open_sum),0,NULL,SUM(c_open_sum))
689 )
690 -(SUM(p_open_days_sum)/DECODE(SUM(p_open_sum),0,NULL,SUM(p_open_sum)))
691 )
692 /(DECODE(SUM(p_open_days_sum),0,NULL,SUM(p_open_days_sum))/DECODE(SUM(p_open_sum),0,NULL,SUM(p_open_sum)))
693 ) * 100
694 AS ENI_MEASURE4
695 , SUM(c_bucket1) AS ENI_MEASURE5
696 , SUM(c_bucket2) AS ENI_MEASURE40
697 , SUM(c_bucket3) AS ENI_MEASURE41
698 , SUM(c_bucket4) AS ENI_MEASURE42
699 , SUM(c_new_sum) AS ENI_MEASURE9
700 , SUM(c_new_sum) AS ENI_MEASURE13
701 , SUM(p_new_sum) AS ENI_MEASURE8
702 , ((SUM(c_new_sum)-SUM(p_new_sum))
703 /DECODE(SUM(p_new_sum),0,NULL,SUM(p_new_sum))
704 ) * 100
705 AS ENI_MEASURE10
706 , SUM(c_cancelled_sum) AS ENI_MEASURE14
707 , SUM(c_implemented_sum) AS ENI_MEASURE15
708 , SUM(p_cancelled_sum) AS ENI_MEASURE27
709 , SUM(p_implemented_sum) AS ENI_MEASURE28
710 , (((NVL(SUM(c_cancelled_sum),0)+NVL(SUM(c_implemented_sum),0))
711 -(NVL(SUM(p_cancelled_sum),0)+NVL(SUM(p_implemented_sum),0))
712 )
713 /DECODE(
714 (NVL(SUM(p_cancelled_sum),0)
715 +NVL(SUM(p_implemented_sum),0)),0,NULL,
716 (NVL(SUM(p_cancelled_sum),0)+NVL(SUM(p_implemented_sum),0))
717 )
718 ) * 100
719 AS ENI_MEASURE16
720 , SUM(c_cycle_time_sum)
721 /DECODE(SUM(c_cycle_time_cnt),0,NULL,SUM(c_cycle_time_cnt))
722 AS ENI_MEASURE17
723 , SUM(p_cycle_time_sum)
724 /DECODE(SUM(p_cycle_time_cnt),0,NULL,SUM(p_cycle_time_cnt))
725 AS ENI_MEASURE20
726 , (((SUM(c_cycle_time_sum)
727 /DECODE(SUM(c_cycle_time_cnt),0,NULL,SUM(c_cycle_time_cnt))
728 )
729 -(SUM(p_cycle_time_sum)
730 /DECODE(SUM(p_cycle_time_cnt),0,NULL,SUM(p_cycle_time_cnt))
731 )
732 )/(DECODE(SUM(p_cycle_time_sum),0,NULL,SUM(p_cycle_time_sum))
733 /DECODE(SUM(p_cycle_time_cnt),0,NULL,SUM(p_cycle_time_cnt))
734 )
735 ) * 100
736 AS ENI_MEASURE18
737 , NVL(SUM(c_cancelled_sum),0)+NVL(SUM(c_implemented_sum),0) AS ENI_MEASURE21
738 , SUM(SUM(c_open_sum)) OVER()
739 AS ENI_MEASURE22
740 , (((SUM(SUM(c_open_sum)) OVER())-(SUM(SUM(p_open_sum)) OVER()))
741 /DECODE(SUM(SUM(p_open_sum)) OVER(),0,NULL,SUM(SUM(p_open_sum)) OVER())
742 ) * 100
743 AS ENI_MEASURE23
744 , ((SUM(SUM(c_open_days_sum)) OVER())
745 /DECODE(SUM(SUM(c_open_sum)) OVER(),0,NULL,SUM(SUM(c_open_sum)) OVER())
746 )
747 AS ENI_MEASURE24
748 , ((((SUM(SUM(c_open_days_sum)) OVER())
749 /DECODE(SUM(SUM(c_open_sum)) OVER(),0,NULL,SUM(SUM(c_open_sum)) OVER()))
750 -((SUM(SUM(p_open_days_sum)) OVER())
751 /DECODE(SUM(SUM(p_open_sum)) OVER(),0,NULL,SUM(SUM(p_open_sum)) OVER()))
752 )
753 /DECODE(((SUM(SUM(p_open_days_sum)) OVER())
754 /DECODE(SUM(SUM(p_open_sum)) OVER(),0,NULL,SUM(SUM(p_open_sum)) OVER()))
755 ,0,NULL,
756 ((SUM(SUM(p_open_days_sum)) OVER())
757 /DECODE(SUM(SUM(p_open_sum)) OVER(),0,NULL,SUM(SUM(p_open_sum)) OVER()))
758 )
759 ) * 100
760 AS ENI_MEASURE25
761 , SUM(SUM(c_bucket1)) OVER() as ENI_MEASURE26
762 , SUM(SUM(c_bucket2)) OVER() as ENI_MEASURE43
763 , SUM(SUM(c_bucket3)) OVER() as ENI_MEASURE44
764 , SUM(SUM(c_bucket4)) OVER() as ENI_MEASURE45
765 , SUM(SUM(c_new_sum)) OVER() as ENI_MEASURE30
766 , ((SUM(SUM(c_new_sum)) OVER()-SUM(SUM(p_new_sum)) OVER())
767 /DECODE(SUM(SUM(p_new_sum)) OVER(),0,NULL,SUM(SUM(p_new_sum)) OVER())
768 ) * 100
769 AS ENI_MEASURE31
770 , SUM(SUM(c_cancelled_sum)) OVER() as ENI_MEASURE35
771 , SUM(SUM(c_implemented_sum)) OVER() as ENI_MEASURE36
772 , (((NVL(SUM(SUM(c_cancelled_sum)) OVER(),0)+NVL(SUM(SUM(c_implemented_sum)) OVER(),0))
776 (NVL(SUM(SUM(p_cancelled_sum)) OVER(),0)+NVL(SUM(SUM(p_implemented_sum)) OVER(),0)),0,NULL,
773 -(NVL(SUM(SUM(p_cancelled_sum)) OVER(),0)+NVL(SUM(SUM(p_implemented_sum)) OVER(),0))
774 )
775 /DECODE(
777 (NVL(SUM(SUM(p_cancelled_sum)) OVER(),0)+NVL(SUM(SUM(p_implemented_sum)) OVER(),0))
778 )
779 ) * 100
780 AS ENI_MEASURE37
781 , SUM(SUM(c_cycle_time_sum)) OVER()
782 /DECODE(SUM(SUM(c_cycle_time_cnt)) OVER(),0,NULL,SUM(SUM(c_cycle_time_cnt)) OVER())
783 AS ENI_MEASURE38
784 , (((SUM(SUM(c_cycle_time_sum)) OVER()
785 /DECODE(SUM(SUM(c_cycle_time_cnt)) OVER(),0,NULL,SUM(SUM(c_cycle_time_cnt)) OVER())
786 )
787 -
788 (
789 SUM(SUM(p_cycle_time_sum)) OVER()
790 /DECODE(SUM(SUM(p_cycle_time_cnt)) OVER(),0,NULL,SUM(SUM(p_cycle_time_cnt)) OVER())
791 )
792 )
793 /DECODE(
794 (SUM(SUM(p_cycle_time_sum)) OVER()
795 /DECODE(SUM(SUM(p_cycle_time_cnt)) OVER(),0,NULL,SUM(SUM(p_cycle_time_cnt)) OVER())
796 )
797 ,0,NULL,
798 (SUM(SUM(p_cycle_time_sum)) OVER()
799 /DECODE(SUM(SUM(p_cycle_time_cnt)) OVER(),0,NULL,SUM(SUM(p_cycle_time_cnt)) OVER()))
800 )
801 ) * 100
802 AS ENI_MEASURE39
803 , NULL AS ENI_MEASURE47
804 , NULL AS ENI_MEASURE48
805 , NULL AS ENI_MEASURE49
806 , NULL AS ENI_MEASURE50
807 , NULL AS ENI_MEASURE56
808 , NULL AS ENI_MEASURE57
809 , NULL AS ENI_MEASURE58
810 ,(CASE WHEN SUM(c_open_sum) IS NULL
811 OR SUM(c_open_sum)=0
812 THEN NULL
813 ELSE '||l_open_url||' END) as ENI_MEASURE59
814 ,(CASE WHEN SUM(c_new_sum) IS NULL
815 OR SUM(c_new_sum)=0
816 THEN NULL
817 ELSE '||l_new_url||' END) as ENI_MEASURE61
818 ,(CASE WHEN SUM(c_implemented_sum) IS NULL
819 OR SUM(c_implemented_sum)=0
820 THEN NULL
821 ELSE '||l_impl_url||' END) as ENI_MEASURE62
822 ,(CASE WHEN SUM(c_cancelled_sum) IS NULL
823 OR SUM(c_cancelled_sum)=0
824 THEN NULL
825 ELSE '||l_canc_url||' END) as ENI_MEASURE63
826 ,(CASE WHEN SUM(c_open_sum) IS NULL
827 OR SUM(c_open_sum)=0
828 THEN NULL
829 ELSE '||l_avg_age_url||' END) as ENI_MEASURE53
830 ,(CASE WHEN SUM(c_implemented_sum) IS NULL
831 OR SUM(c_implemented_sum)=0
832 THEN NULL
833 ELSE '||l_cycle_url||' END) as ENI_MEASURE54
834 FROM
835 ( SELECT '||l_select_id_list ||'
836 , null c_open_sum
837 , null p_open_sum
838 , null c_open_days_sum
839 , null p_open_days_sum
840 , null c_bucket1
841 , null c_bucket2
842 , null c_bucket3
843 , null c_bucket4
844 , SUM(
845 CASE WHEN ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
846 THEN edcs.new_sum ELSE 0 END
847 ) AS c_new_sum
848 , SUM(
849 CASE WHEN ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
850 THEN edcs.new_sum ELSE 0 END
851 ) AS p_new_sum
852 , SUM(
853 CASE WHEN ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
854 THEN edcs.cancelled_sum ELSE 0 END
855 ) AS c_cancelled_sum
856 , SUM(
857 CASE WHEN ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
858 THEN edcs.cancelled_sum ELSE 0 END
859 ) AS p_cancelled_sum
860 , SUM(
861 CASE WHEN ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
862 THEN edcs.implemented_sum ELSE 0 END
863 ) AS c_implemented_sum
864 , SUM(
865 CASE WHEN ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
866 THEN edcs.implemented_sum ELSE 0 END
867 ) AS p_implemented_sum
868 , SUM(
869 CASE WHEN ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
870 THEN edcs.cycle_time_sum ELSE 0 END
871 ) AS c_cycle_time_sum
872 , SUM(
873 CASE WHEN ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
874 THEN edcs.cycle_time_cnt ELSE 0 END
875 ) AS c_cycle_time_cnt
876 , SUM(
877 CASE WHEN ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
878 THEN edcs.cycle_time_sum ELSE 0 END
879 ) AS p_cycle_time_sum
880 , SUM(
881 CASE WHEN ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
885 , fii_time_structures ftrs'||l_item_from_clause||'
882 THEN edcs.cycle_time_cnt ELSE 0 END
883 ) AS p_cycle_time_cnt
884 FROM eni_dbi_co_sum_mv edcs
886 WHERE
887 edcs.time_id = ftrs.time_id
888 AND edcs.period_type_id = ftrs.period_type_id
889 AND(ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
890 OR ftrs.report_Date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE)
891 AND BITAND(ftrs.record_type_id, :PERIODAND )= :PERIODAND --Bug 5083876,5083652
892 ' || l_where_clause || '
893 GROUP BY
894 ' || l_select_id || '
895 UNION ALL
896 SELECT '|| l_select_id_list || '
897 , SUM(
898 CASE WHEN '|| '&' ||'BIS_CURRENT_ASOF_DATE BETWEEN edcs.creation_date
899 AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
900 THEN edcs.cnt ELSE 0 END
901 ) AS c_open_sum
902 , SUM(
903 CASE WHEN '|| '&' ||'BIS_PREVIOUS_ASOF_DATE BETWEEN edcs.creation_date
904 AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
905 THEN edcs.cnt ELSE 0 END
906 ) AS p_open_sum
907 , SUM(
908 CASE WHEN '|| '&' ||'BIS_CURRENT_ASOF_DATE BETWEEN edcs.creation_date
909 AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
910 THEN ('|| '&' ||'BIS_CURRENT_ASOF_DATE - edcs.creation_date ) * edcs.cnt
911 ELSE 0 END
912 ) AS c_open_days_sum
913 , SUM(
914 CASE WHEN '|| '&' ||'BIS_PREVIOUS_ASOF_DATE BETWEEN edcs.creation_date
915 AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
916 THEN ('|| '&' ||'BIS_PREVIOUS_ASOF_DATE - edcs.creation_date ) * edcs.cnt
917 ELSE 0 END
918 ) AS p_open_days_sum
919 , SUM(
920 CASE WHEN '|| '&' ||'BIS_CURRENT_ASOF_DATE BETWEEN edcs.creation_date
921 AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
922 THEN (CASE WHEN ('|| '&' ||'BIS_CURRENT_ASOF_DATE - edcs.creation_date)
923 BETWEEN 0 AND 1
924 THEN edcs.cnt ELSE 0 END
925 )
926 ELSE 0 END
927 ) AS c_bucket1
928 , SUM(
929 CASE WHEN '|| '&' ||'BIS_CURRENT_ASOF_DATE BETWEEN edcs.creation_date
930 AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
931 THEN (CASE WHEN ('|| '&' ||'BIS_CURRENT_ASOF_DATE - edcs.creation_date)
932 BETWEEN 2 AND 5
933 THEN edcs.cnt ELSE 0 END
934 )
935 ELSE 0 END
936 ) AS c_bucket2
937 , SUM(
938 CASE WHEN '|| '&' ||'BIS_CURRENT_ASOF_DATE BETWEEN edcs.creation_date
939 AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
940 THEN(CASE WHEN ('|| '&' ||'BIS_CURRENT_ASOF_DATE - edcs.creation_date)
941 BETWEEN 6 and 10
942 THEN edcs.cnt ELSE 0 END
943 )
944 ELSE 0 END
945 ) AS c_bucket3
946 , SUM(
947 CASE WHEN '|| '&' ||'BIS_CURRENT_ASOF_DATE BETWEEN edcs.creation_date
948 AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date),:NVL_DATE)
949 THEN(CASE WHEN ('|| '&' ||'BIS_CURRENT_ASOF_DATE - edcs.creation_date) > 10
950 THEN edcs.cnt ELSE 0 END
951 )
952 ELSE 0 END
953 ) AS c_bucket4
954 , null c_new_sum
955 , null p_new_sum
956 , null c_cancelled_sum
957 , null p_cancelled_sum
958 , null c_implemented_sum
959 , null p_implemented_sum
960 , null c_cycle_time_sum
961 , null c_cycle_time_cnt
962 , null p_cycle_time_sum
963 , null p_cycle_time_cnt
964 FROM eni_dbi_co_dnum_mv edcs'||l_item_from_clause||'
965 WHERE
966 (('|| '&' ||'BIS_CURRENT_ASOF_DATE >= edcs.creation_date
967 AND '|| '&' ||'BIS_CURRENT_ASOF_DATE < NVL(NVL(edcs.cancellation_date, edcs.implementation_date), 1 + '|| '&' ||'BIS_CURRENT_ASOF_DATE)
968 )
969 OR
970 ('|| '&' ||'BIS_PREVIOUS_ASOF_DATE >= edcs.creation_date
971 AND '|| '&' ||'BIS_PREVIOUS_ASOF_DATE < NVL(NVL(edcs.cancellation_date, edcs.implementation_date), 1 + '|| '&' ||'BIS_PREVIOUS_ASOF_DATE)
972 )) ' || l_where_clause_dn || '
973 GROUP BY
974 ' || l_select_id || '
975 ) dnum_sum
976 , '|| l_lookup || '
977 WHERE
978 '||l_outer_join_condition||'
979 GROUP BY '||l_outer_group_by||'
980 ORDER BY ' || l_order_by;
981
982 /* achampan: for bug 3151655, I modified the login in the where clause directly above
983 from BETWEEN edcs.creation_date AND NVL(NVL(edcs.cancellation_date, edcs.implementation_date), as_of_date)
984 to >= edcs.creation_date AND < NVL(NVL(edcs.cancellation_date, edcs.implementation_date), 1 + as_of_date)
985 otherwise, the query counts an ECO that was closed on as_of_date as still open.
986 */
987 /* eletuchy: for bug 4099352, I changed the c_bucket1..4 logic to use edcs.cnt instead of 1
988 otherwise, the query counts the rows of the denum view instead of the change orders those
989 rows represent.
990 */
991
992 END IF;
993
994 x_custom_sql := l_sql_stmt;
995
996
997 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
998 x_custom_output := bis_query_attributes_tbl();
999 x_custom_output.extend;
1000 l_custom_rec.attribute_name := ':STATUS_ID';
1001 l_custom_rec.attribute_value := REPLACE(l_status,'''');
1002 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1003 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1004 x_custom_output(1) := l_custom_rec;
1005
1006 x_custom_output.extend;
1007 l_custom_rec.attribute_name := ':PRIORITY_ID';
1008 l_custom_rec.attribute_value := l_priority;
1009 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1010 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1011 x_custom_output(2) := l_custom_rec;
1012
1013 x_custom_output.extend;
1014 l_custom_rec.attribute_name := ':TYPE_ID';
1015 l_custom_rec.attribute_value := REPLACE(l_type,'''');
1016 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1017 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1018 x_custom_output(3) := l_custom_rec;
1019
1020 x_custom_output.extend;
1021 l_custom_rec.attribute_name := ':REASON_ID';
1022 l_custom_rec.attribute_value := l_reason;
1023 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1024 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1025 x_custom_output(4) := l_custom_rec;
1026
1027 x_custom_output.extend;
1028 l_custom_rec.attribute_name := ':NVL_DATE';
1029 l_custom_rec.attribute_value := '31/12/3000';
1030 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1031 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1032 x_custom_output(5) := l_custom_rec;
1033
1034 x_custom_output.extend;
1035 l_custom_rec.attribute_name := ':ITEM_ID';
1036 l_custom_rec.attribute_value := l_item;
1037 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1038 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1039 x_custom_output(6) := l_custom_rec;
1040
1041 --Bug 5083876 - Start Code
1042 x_custom_output.extend;
1043 l_custom_rec.attribute_name := ':ORGANIZATION_ID';
1044 l_custom_rec.attribute_value := REPLACE(l_org,'''');
1045 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1046 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1047 x_custom_output(7) := l_custom_rec;
1048
1049 x_custom_output.extend;
1050 l_custom_rec.attribute_name := ':PERIODAND'; --Bug 5083652
1051 l_custom_rec.attribute_value := REPLACE(l_period_bitand,'''');
1052 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1053 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1054 x_custom_output(8) := l_custom_rec;
1055 --Bug 5083876 - End Code
1056
1057 --Bug 5083652 -- Start Code
1058 x_custom_output.extend;
1059 l_custom_rec.attribute_name := ':PERIODTYPE';
1060 l_custom_rec.attribute_value := REPLACE(l_period_type,'''');
1061 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1062 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1063 x_custom_output(9) := l_custom_rec;
1064
1065 x_custom_output.extend;
1066 l_custom_rec.attribute_name := ':COMPARETYPE';
1067 l_custom_rec.attribute_value := REPLACE(l_comp_type,'''');
1068 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1069 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1070 x_custom_output(10) := l_custom_rec;
1071
1072 x_custom_output.extend;
1073 l_custom_rec.attribute_name := ':CUR_PERIOD_ID';
1074 l_custom_rec.attribute_value := REPLACE(l_cur_period,'''');
1075 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1076 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1077 x_custom_output(11) := l_custom_rec;
1078
1079 --Bug 5083652 -- End Code
1080
1081 EXCEPTION
1082
1083 WHEN OTHERS THEN
1084 l_err_msg := SQLERRM;
1085 -- TODO: log this somewhere!
1086 NULL;
1087 END GET_SQL;
1088
1089 END ENI_DBI_COR_PKG;