[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_COC_PKG
Source
1 PACKAGE BODY ENI_DBI_COC_PKG AS
2 /*$Header: ENICOCPB.pls 120.1 2006/03/16 06:22:24 pgopalar noship $*/
3 PROCEDURE GET_SQL ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
4 , x_custom_sql OUT NOCOPY VARCHAR2
5 , x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
6
7 l_custom_rec BIS_QUERY_ATTRIBUTES;
8 l_err_msg VARCHAR2(1000);
9 l_period_type VARCHAR2(1000);
10 l_period_bitand NUMBER;
11 l_view_by VARCHAR2(1000);
12 l_as_of_date DATE;
13 l_prev_as_of_date DATE;
14 l_report_start DATE;
15 l_cur_period NUMBER;
16 l_days_into_period NUMBER;
17 l_comp_type VARCHAR2(100);
18 l_category VARCHAR2(100);
19 l_item VARCHAR2(100);
20 l_org VARCHAR2(100);
21 l_id_column VARCHAR2(100);
22 l_order_by VARCHAR2(1000);
23 l_drill VARCHAR2(100);
24 l_status VARCHAR2(100);
25 l_priority VARCHAR2(100);
26 l_reason VARCHAR2(100);
27 l_lifecycle_phase VARCHAR2(100);
28 l_currency VARCHAR2(100);
29 l_bom_type VARCHAR2(100);
30 l_type VARCHAR2(100);
31 l_manager VARCHAR2(100);
32 l_temp VARCHAR2(1000);
33 l_lob VARCHAR2(1000);
34 l_comp_where VARCHAR2(1000);
35 l_org_where VARCHAR2(1000);
36 l_cat_where VARCHAR2(1000);
37 l_item_where VARCHAR2(1000);
38 l_priority_where VARCHAR2(1000);
39 l_reason_where VARCHAR2(1000);
40 l_status_where VARCHAR2(200);
41 l_type_where VARCHAR2(1000);
42 l_from_clause VARCHAR2(1000);
43 l_where_clause VARCHAR2(1000);
44 l_group_by_clause VARCHAR2(1000);
45 l_priority_from VARCHAR2(1000);
46 l_type_from VARCHAR2(1000);
47 l_reason_from VARCHAR2(1000);
48 l_lookup_from VARCHAR2(1000);
49 l_lookup_outer_where VARCHAR2(1000);
50 l_reason_outer_where VARCHAR2(1000);
51 l_priority_outer_where VARCHAR2(1000);
52 l_type_outer_where VARCHAR2(1000);
53 l_status_outer_where VARCHAR2(200);
54 l_lookup_value VARCHAR2(100);
55 l_impl_url VARCHAR2(1000);
56 l_description VARCHAR2(1000);
57 BEGIN
58
59 x_custom_output := bis_query_attributes_tbl();
60
61
62 ENI_DBI_UTIL_PKG.get_parameters( p_page_parameter_tbl
63 , l_period_type
64 , l_period_bitand
65 , l_view_by
66 , l_as_of_date
67 , l_prev_as_of_date
68 , l_report_start
69 , l_cur_period
70 , l_days_into_period
71 , l_comp_type
72 , l_category
73 , l_item
74 , l_org
75 , l_id_column
76 , l_order_by
77 , l_drill
78 , l_status
79 , l_priority
80 , l_reason
81 , l_lifecycle_phase
82 , l_currency
83 , l_bom_type
84 , l_type
85 , l_manager
86 , l_lob
87 );
88
89
90
91 l_impl_url:='''pFunctionName=ENI_DBI_COL_IMPL_R&pCustomView=ENI_DBI_COL_CV2&REPORTED=IMPL&VIEW_BY=VIEW_BY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
92
93 IF (l_priority IS NULL OR l_priority = '' OR l_priority = 'All')
94 THEN
95 l_priority_where := ' and edcs.priority_code is null ';
96 l_priority_outer_where := '';
97 ELSE
98 l_priority_where := ' and edcs.priority_code= :PRIORITY';
99 l_priority_outer_where := ' and edcs.priority_code = :PRIORITY';
100 END IF;
101
102 IF (l_type IS NULL OR l_type = '' OR l_type = 'All')
103 THEN
104 l_type_where := ' and edcs.change_order_type_id is null ';
105 l_type_outer_where := '';
106 ELSE
107 l_type_where := ' and edcs.change_order_type_id= :TYPE';
108 l_type_outer_where := ' and edcs.change_order_type_id= :TYPE';
109 END IF;
110
111
112 IF (l_reason IS NULL OR l_reason = '' OR l_reason = 'All')
113 THEN
114 l_reason_where := ' and edcs.reason_code is null ';
115 l_reason_outer_where := '';
116 ELSE
117 l_reason_where := ' and edcs.reason_code = :REASON';
118 l_reason_outer_where := ' and edcs.reason_code = :REASON';
119 END IF;
120
121 IF (l_item IS NULL OR l_item = '' OR l_item = 'All')
122 THEN
123
124 l_item_where := '';
125
126 ELSE
127 l_item_where := ' and edcs.item_id = :ITEM';
128 END IF;
129
130 IF (l_status IS NULL OR l_status = '' OR l_status = 'All')
131 THEN
132 l_status_where := ' and edcs.status_type is null ';
133 l_status_outer_where := '';
134 ELSE
135 l_status_where := ' and edcs.status_type = :STATUS';
136 l_status_outer_where := ' and edcs.status_type = :STATUS';
137 END IF;
138
139
140 IF (l_org IS NULL OR l_org = '' OR l_org = 'All')
141 THEN
142 l_org_where := '';
143 ELSE
144 l_org_where := ' AND edcs.organization_id = :ORG';
145 END IF;
146
147 l_lookup_from:=' ,eni_chg_mgmt_priority_v eiv';
148 l_lookup_outer_where:=' and edcs.priority_code is not null
149 and edcs.priority_code = eiv.id(+)
150 ' || l_priority_outer_where || '
151 ' || l_type_where || '
152 ' || l_status_where || '
153 ' || l_reason_where;
154 l_lookup_value := 'priority_code';
155 l_description :=' null as ENI_MEASURE50 ';
156
157 IF (l_view_by like '%TYPE%')
158 THEN
159 l_lookup_from:=' , eni_chg_mgmt_type_v eiv ';
160 l_lookup_outer_where:=' and edcs.change_order_type_id is not null
161 and edcs.change_order_type_id = eiv.id
162 ' || l_type_outer_where || '
163 ' || l_priority_where || '
164 ' || l_status_where || '
165 ' || l_reason_where;
166 l_lookup_value := 'change_order_type_id';
167 l_description :=' null as ENI_MEASURE50 ';
168
169 ELSIF (l_view_by like '%REASON%')
170 THEN
171 l_lookup_from:=',eni_chg_mgmt_reason_v eiv';
172 l_lookup_outer_where:=' and edcs.reason_code is not null
173 and edcs.reason_code = eiv.id(+)
174 ' || l_reason_outer_where || '
175 ' || l_priority_where || '
176 ' || l_status_where || '
177 ' || l_type_where;
178 l_lookup_value := 'reason_code';
179 l_description :=' null as ENI_MEASURE50 ';
180
181 ELSIF (l_view_by like '%STATUS%')
182 THEN
183 l_lookup_from:=',eni_chg_mgmt_status_v eiv';
184 l_lookup_outer_where:=' and edcs.status_type is not null
185 and edcs.status_type = eiv.id
186 ' || l_status_outer_where || '
187 ' || l_priority_where || '
188 ' || l_reason_where || '
189 ' || l_type_where;
190 l_lookup_value := 'status_type';
191 l_description :=' null as ENI_MEASURE50 ';
192
193 ELSIF (l_view_by like 'ITEM+ENI_ITEM_ORG')
194 THEN
195 l_lookup_from:=',eni_item_org_v eiv';
196 l_lookup_outer_where:=' and edcs.item_id = eiv.inventory_item_id
197 and edcs.organization_id = eiv.organization_id
198 ' || l_reason_where || '
199 ' || l_priority_where || '
200 ' || l_status_where || '
201 ' || l_type_where;
202 l_description :=' eiv.description as ENI_MEASURE50 ';
203
204 END IF;
205
206
207 IF (l_view_by not like '%ITEM%') AND (l_item IS NULL OR l_item = '' OR l_item = 'All')
208 THEN
209 x_custom_sql :=
210 'SELECT
211 NULL AS VIEWBY,
212 NULL AS ENI_MEASURE50,
213 NULL AS ENI_MEASURE1,
214 NULL AS ENI_MEASURE9,
215 NULL AS ENI_MEASURE3,
216 NULL AS ENI_MEASURE6,
217 NULL AS ENI_MEASURE7,
218 NULL AS ENI_MEASURE8,
219 NULL AS ENI_MEASURE16,
220 NULL AS ENI_MEASURE17,
221 NULL AS ENI_MEASURE18,
222 NULL AS ENI_MEASURE19,
223 NULL AS ENI_MEASURE51,
224 NULL AS ENI_MEASURE52,
225 NULL AS ENI_MEASURE53,
226 NULL AS ENI_MEASURE54,
227 NULL AS ENI_MEASURE55,
228 NULL AS ENI_MEASURE56,
229 NULL AS ENI_MEASURE57,
230 NULL AS ENI_MEASURE58,
231 NULL AS ENI_MEASURE59,
232 NULL AS ENI_MEASURE60,
233 NULL AS ENI_MEASURE62
234 FROM
235 DUAL';
236 RETURN;
237
238 ELSIF (l_item IS NOT NULL AND l_item <> '' AND l_item <> 'All') THEN
239 l_item_where := ' AND edcs.item_id = :ITEM'; --Bug 5083894
240 END IF;
241
242 x_custom_sql := '
243 SELECT
244 t.VALUE as VIEWBY
245 ,ENI_MEASURE50
246 ,t.id as VIEWBYID
247 ,ENI_MEASURE1
248 ,ENI_MEASURE9
249 ,ENI_MEASURE93/DECODE(ENI_MEASURE89,0,NULL,ENI_MEASURE89) as ENI_MEASURE3
250 ,ENI_MEASURE96/DECODE(ENI_MEASURE88,0,NULL,ENI_MEASURE88) as ENI_MEASURE6
251 ,ENI_MEASURE97/DECODE(ENI_MEASURE1,0,NULL,ENI_MEASURE1) as ENI_MEASURE7
252 ,ENI_MEASURE98/DECODE(ENI_MEASURE9,0,NULL,ENI_MEASURE9) as ENI_MEASURE8
253 ,ENI_MEASURE16
254 ,ENI_MEASURE17
255 ,ENI_MEASURE18
256 ,ENI_MEASURE19';
257
258 -- No need to compute Grand Total if view by is on "Item" #Purushothaman
259
260 IF l_view_by not like 'ENI_ITEM_ORG%' THEN
261
262 x_custom_sql := x_custom_sql ||
263 ',SUM(ENI_MEASURE1) OVER() as ENI_MEASURE51
264 ,((SUM(ENI_MEASURE1) OVER() - SUM(ENI_MEASURE9) OVER())
265 /DECODE(SUM(ENI_MEASURE9) OVER(),0,NULL,SUM(ENI_MEASURE9) OVER()))*100
266 as ENI_MEASURE52
267 ,(SUM(ENI_MEASURE93) OVER())
268 /DECODE(SUM(ENI_MEASURE89) OVER(),0,NULL,SUM(ENI_MEASURE89) OVER())
269 as ENI_MEASURE53
270 ,(SUM(ENI_MEASURE96) OVER())
271 /DECODE(SUM(ENI_MEASURE88) OVER(),0,NULL,SUM(ENI_MEASURE88) OVER())
272 as ENI_MEASURE54
273 ,(SUM(ENI_MEASURE97) OVER())
274 /DECODE(SUM(ENI_MEASURE1) OVER(),0,NULL,SUM(ENI_MEASURE1) OVER())
275 as ENI_MEASURE55
276 ,((
277 (
278 (SUM(ENI_MEASURE97) OVER())
279 /DECODE(SUM(ENI_MEASURE1) OVER(),0,NULL,SUM(ENI_MEASURE1) OVER())
280 )
281 -
282 (
283 (SUM(ENI_MEASURE98) OVER())
284 /DECODE(SUM(ENI_MEASURE9) OVER(),0,NULL,SUM(ENI_MEASURE9) OVER())
285 )
286 )
287 /DECODE(
288 (
289 (SUM(ENI_MEASURE98) OVER())
290 /DECODE(SUM(ENI_MEASURE9) OVER(),0,NULL,SUM(ENI_MEASURE9) OVER())
291 )
292 ,0
293 ,NULL
294 ,(
295 (SUM(ENI_MEASURE98) OVER())
296 /DECODE(SUM(ENI_MEASURE9) OVER(),0,NULL,SUM(ENI_MEASURE9) OVER())
297 )
298 )
299 ) * 100
300 as ENI_MEASURE56
301 ,SUM(ENI_MEASURE16) OVER() as ENI_MEASURE57
302 ,SUM(ENI_MEASURE17) OVER() as ENI_MEASURE58
303 ,SUM(ENI_MEASURE18) OVER() as ENI_MEASURE59
304 ,SUM(ENI_MEASURE19) OVER() as ENI_MEASURE60';
305 ELSE
306 x_custom_sql := x_custom_sql ||
307 ',NULL as ENI_MEASURE51
308 ,NULL as ENI_MEASURE52
309 ,NULL as ENI_MEASURE53
310 ,NULL as ENI_MEASURE54
311 ,NULL as ENI_MEASURE55
312 ,NULL as ENI_MEASURE56
313 ,NULL as ENI_MEASURE57
314 ,NULL as ENI_MEASURE58
315 ,NULL as ENI_MEASURE59
316 ,NULL as ENI_MEASURE60';
317 END IF;
318
319 x_custom_sql := x_custom_sql ||
320 ',(CASE WHEN ENI_MEASURE1 IS NULL OR ENI_MEASURE1=0
321 THEN NULL
322 ELSE '||l_impl_url||'
323 END
324 ) as ENI_MEASURE62
325 FROM
326 (
327 SELECT
328 edcs.VALUE as VALUE
329 ,edcs.ID as ID
330 ,ENI_MEASURE50
331 ,SUM(CASE WHEN
332 ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
333 THEN edcs.IMPLEMENTED_SUM
334 ELSE null
335 END) as ENI_MEASURE1
336
337 ,SUM(CASE WHEN
338 ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
339 THEN edcs.IMPLEMENTED_SUM
340 ELSE null
341 END) as ENI_MEASURE9
342
343 ,SUM(CASE WHEN
344 ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
345 THEN NVL(edcs.CREATE_TO_APPROVE_SUM,0)
346 ELSE null
347 END) as ENI_MEASURE93
348
349 ,SUM(CASE WHEN
350 ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
351 THEN NVL(edcs.CREATE_TO_APPROVE_CNT,0)
352 ELSE null
353 END) as ENI_MEASURE89
354
355 ,SUM(CASE WHEN
356 ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
357 THEN NVL(edcs.APPROVE_TO_IMPL_SUM,0)
358 ELSE null
359 END) as ENI_MEASURE96
360
361 ,SUM(CASE WHEN
362 ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
363 THEN NVL(edcs.APPROVE_TO_IMPL_CNT,0)
364 ELSE null
365 END) as ENI_MEASURE88
366
367 ,SUM(CASE WHEN
368 ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
369 THEN NVL(edcs.CYCLE_TIME_SUM,0)
370 ELSE null
371 END) as ENI_MEASURE97
372
373 ,SUM(CASE WHEN
374 ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
375 THEN NVL(edcs.CYCLE_TIME_SUM,0)
376 ELSE null
377 END) as ENI_MEASURE98
378
379 ,SUM(CASE WHEN
380 ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
381 THEN edcs.BUCKET1_SUM
382 ELSE null
383 END) as ENI_MEASURE16
384
385 ,SUM(CASE WHEN
386 ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
387 THEN edcs.BUCKET2_SUM
388 ELSE null
389 END) as ENI_MEASURE17
390
391 ,SUM(CASE WHEN
392 ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
393 THEN edcs.BUCKET3_SUM
394 ELSE null
395 END) as ENI_MEASURE18
396
397 ,SUM(CASE WHEN
398 ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
399 THEN edcs.BUCKET4_SUM
400 ELSE null
401 END) as ENI_MEASURE19
402 FROM
403 (SELECT edcs.IMPLEMENTED_SUM
404 ,edcs.TIME_ID
405 ,edcs.PERIOD_TYPE_ID
406 ,edcs.CREATE_TO_APPROVE_SUM
407 ,edcs.CREATE_TO_APPROVE_CNT
408 ,edcs.APPROVE_TO_IMPL_SUM
409 ,edcs.APPROVE_TO_IMPL_CNT
410 ,edcs.CYCLE_TIME_SUM
411 ,edcs.BUCKET1_SUM
412 ,edcs.BUCKET2_SUM
413 ,edcs.BUCKET3_SUM
414 ,edcs.BUCKET4_SUM
415 ,eiv.value
416 ,eiv.id
417 ,'||l_description || '
418 from ENI_DBI_CO_SUM_MV edcs
419 ' || l_lookup_from || '
420 where 1=1
421 ' || l_item_where ||'
422 ' || l_org_where || '
423 ' || l_cat_where || '
424 ' || l_lookup_outer_where || '
425 ) edcs
426 , fii_time_structures ftrs
427 WHERE 1=1
428 AND
429 (
430 ftrs.report_date = '|| '&' ||'BIS_CURRENT_ASOF_DATE
431 OR
432 ftrs.report_date = '|| '&' ||'BIS_PREVIOUS_ASOF_DATE
433 )
434 AND edcs.implemented_sum is not null
435 AND edcs.time_id = ftrs.time_id
436 AND edcs.period_type_id = ftrs.period_type_id
437 AND BITAND(ftrs.record_type_id, :PERIOD ) = :PERIOD
438 GROUP BY
439 edcs.value, edcs.id,ENI_MEASURE50
440 ) t
441 GROUP BY
442 t.VALUE,t.id,ENI_MEASURE1,ENI_MEASURE9,
443 ENI_MEASURE89,ENI_MEASURE88,
444 ENI_MEASURE16,ENI_MEASURE17,ENI_MEASURE18,ENI_MEASURE19,
445 ENI_MEASURE93,ENI_MEASURE96,ENI_MEASURE97,ENI_MEASURE98,
446 ENI_MEASURE50
447 ORDER BY
448 ' ||l_order_by ;
449
450
451
452 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
453 x_custom_output := bis_query_attributes_tbl();
454
455
456
457 x_custom_output.extend;
458 l_custom_rec.attribute_name := ':ITEM';
459 l_custom_rec.attribute_value := l_item;
460 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
461 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
462 x_custom_output(1) := l_custom_rec;
463
464
465 x_custom_output.extend;
466 l_custom_rec.attribute_name := ':ORG';
467 l_custom_rec.attribute_value := replace(l_org,'''');
468 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
469 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
470 x_custom_output(2) := l_custom_rec;
471
472 x_custom_output.extend;
473 l_custom_rec.attribute_name := ':REASON';
474 l_custom_rec.attribute_value := l_reason;
475 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
476 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
477 x_custom_output(3) := l_custom_rec;
478
479 x_custom_output.extend;
480 l_custom_rec.attribute_name := ':PRIORITY';
481 l_custom_rec.attribute_value := l_priority;
482 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
483 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
484 x_custom_output(4) := l_custom_rec;
485
486 x_custom_output.extend;
487 l_custom_rec.attribute_name := ':STATUS';
488 l_custom_rec.attribute_value := replace(l_status,'''');
489 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
490 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
491 x_custom_output(5) := l_custom_rec;
492
493 x_custom_output.extend;
494 l_custom_rec.attribute_name := ':TYPE';
495 l_custom_rec.attribute_value := replace(l_type,'''');
496 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
497 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
498 x_custom_output(6) := l_custom_rec;
499
500 --Bug 5083894 - Start Code
501
502 x_custom_output.extend;
503 l_custom_rec.attribute_name := ':PERIOD';
504 l_custom_rec.attribute_value := replace(l_period_bitand,'''');
505 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
506 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
507 x_custom_output(7) := l_custom_rec;
508
509 --Bug 5083894 - End Code
510
511 --l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
512 --l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
513
514
515
516 END GET_SQL;
517 END ENI_DBI_COC_PKG;