[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_PDA_PKG
Source
1 PACKAGE BODY ENI_DBI_PDA_PKG AS
2 /*$Header: ENIPDAPB.pls 115.14 2004/02/23 00:02:35 pthambu 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_period_type VARCHAR2(1000);
9 l_period_bitand NUMBER;
10 l_view_by VARCHAR2(1000);
11 l_as_of_date DATE;
12 l_prev_as_of_date DATE;
13 l_report_start DATE;
14 l_cur_period NUMBER;
15 l_days_into_period NUMBER;
16 l_comp_type VARCHAR2(100);
17 l_category VARCHAR2(100);
18 l_item VARCHAR2(100);
19 l_org VARCHAR2(100);
20 l_id_column VARCHAR2(100);
21 l_order_by VARCHAR2(1000);
22 l_drill VARCHAR2(100);
23 l_status VARCHAR2(100);
24 l_priority VARCHAR2(100);
25 l_reason VARCHAR2(100);
26 l_lifecycle_phase VARCHAR2(100);
27 l_currency VARCHAR2(100);
28 l_bom_type VARCHAR2(100);
29 l_type VARCHAR2(100);
30 l_manager VARCHAR2(100);
31 l_temp VARCHAR2(1000);
32 l_lob VARCHAR2(1000);
33 l_org_where VARCHAR2(400);
34 l_item_where VARCHAR2(400);
35 l_priority_where VARCHAR2(400);
36 l_reason_where VARCHAR2(400);
37 l_type_where VARCHAR2(400);
38 l_status_where VARCHAR2(400);
39 l_join_col_name VARCHAR2(100);
40 l_viewby_tbl VARCHAR2(100);
41 l_open_url VARCHAR2(400);
42 l_item_description VARCHAR2(100) := 'NULL';
43 l_item_desc_grp VARCHAR2(100);
44
45 BEGIN
46
47 x_custom_output := bis_query_attributes_tbl();
48
49 ENI_DBI_UTIL_PKG.get_parameters( p_page_parameter_tbl
50 , l_period_type
51 , l_period_bitand
52 , l_view_by
53 , l_as_of_date
54 , l_prev_as_of_date
55 , l_report_start
56 , l_cur_period
57 , l_days_into_period
58 , l_comp_type
59 , l_category
60 , l_item
61 , l_org
62 , l_id_column
63 , l_order_by
64 , l_drill
65 , l_status
66 , l_priority
67 , l_reason
68 , l_lifecycle_phase
69 , l_currency
70 , l_bom_type
71 , l_type
72 , l_manager
73 , l_lob
74 );
75
76 /* Bug Fix: 3380925
77 Added ENI_MEASURE6,ENI_MEASURE7, ENI_MEASURE37,ENI_MEASURE38
78 Reverted the calculation to (current_date - need_by_date)
79
80 */
81
82 l_open_url:='''pFunctionName=ENI_DBI_COL_PAST_R&pCustomView=ENI_DBI_COL_CV4&REPORTED=PAST'||
83 '&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y''';
84
85
86 IF (l_priority IS NULL OR l_priority = '' OR l_priority = 'All')
87 THEN
88 l_priority_where:= '';
89 ELSE
90 l_priority_where := ' and pdo.priority_code = :PRIORITY';
91 END IF;
92
93
94 IF (l_type IS NULL OR l_type = '' OR l_type = 'All')
95 THEN
96 l_type_where := '';
97 ELSE
98 l_type_where := ' and pdo.change_order_type_id = :TYPE';
99 END IF;
100
101
102 IF (l_reason IS NULL OR l_reason = '' OR l_reason = 'All')
103 THEN
104 l_reason_where := '';
105 ELSE
106 l_reason_where := ' and pdo.reason_code = :REASON';
107 END IF;
108
109 IF (l_status IS NULL OR l_status = '' OR l_status = 'All')
110 THEN
111 l_status_where := '';
112 ELSE
113 l_status_where := ' and pdo.status_type = :STATUS';
114 END IF;
115
116
117 IF (l_org IS NULL OR l_org = '' OR l_org = 'All')
118 THEN
119 l_org_where := '';
120 ELSE
121 l_org_where := ' AND pdo.organization_id = :ORG';
122 END IF;
123
124
125 l_item_desc_grp := '';
126
127 CASE l_view_by
128 WHEN 'ENI_CHANGE_MGMT_STATUS+ENI_CHANGE_MGMT_STATUS' THEN
129 l_viewby_tbl := ' eni_chg_mgmt_status_v vby';
130 l_join_col_name := ' and vby.id = pdo.status_type';
131 WHEN 'ENI_CHANGE_MGMT_PRIORITY+ENI_CHANGE_MGMT_PRIORITY' THEN
132 l_viewby_tbl := ' eni_chg_mgmt_priority_v vby';
133 l_join_col_name := ' and vby.id = pdo.priority_code';
134 WHEN 'ENI_CHANGE_MGMT_REASON+ENI_CHANGE_MGMT_REASON' THEN
135 l_viewby_tbl := ' eni_chg_mgmt_reason_v vby';
136 l_join_col_name := ' and vby.id = pdo.reason_code';
137 WHEN 'ENI_CHANGE_MGMT_TYPE+ENI_CHANGE_MGMT_TYPE' THEN
138 l_viewby_tbl := ' eni_chg_mgmt_type_v vby';
139 l_join_col_name := ' and vby.id = pdo.change_order_type_id';
140 ELSE
141 l_item_description := ' vby.description';
142 l_item_desc_grp := ', vby.description';
143 l_viewby_tbl := ' eni_item_org_v vby';
144 l_join_col_name := ' and vby.inventory_item_id = pdo.item_id
145 and vby.organization_id = pdo.organization_id';
146 END CASE;
147
148
149 IF (l_item IS NULL OR l_item = '' OR l_item = 'All')
150 THEN
151 l_item_where := '';
152 IF(l_view_by NOT like '%ITEM+%')
153 THEN
154 X_CUSTOM_SQL:='
155 SELECT NULL AS VIEWBY,
156 NULL AS ENI_MEASURE30,
157 NULL AS ENI_MEASURE1,
158 NULL AS ENI_MEASURE9,
159 NULL AS ENI_MEASURE3,
160 NULL AS ENI_MEASURE6,
161 NULL AS ENI_MEASURE7,
162 NULL AS ENI_MEASURE11,
163 NULL AS ENI_MEASURE31,
164 NULL AS ENI_MEASURE32,
165 NULL AS ENI_MEASURE33,
166 NULL AS ENI_MEASURE34,
167 NULL AS ENI_MEASURE20,
168 NULL AS ENI_MEASURE21,
169 NULL AS ENI_MEASURE22,
170 NULL AS ENI_MEASURE23,
171 NULL AS ENI_MEASURE25,
172 NULL AS ENI_MEASURE26,
173 NULL AS ENI_MEASURE27,
174 NULL AS ENI_MEASURE28,
175 NULL AS ENI_MEASURE36,
176 NULL AS ENI_MEASURE37,
177 NULL AS ENI_MEASURE38
178 FROM DUAL';
179 RETURN;
180 END IF;
181 ELSE
182 l_item_where := ' AND pdo.item_id = :ITEM';
183 END IF;
184
185 /* Bug: 3394222 Rolling Period Conversion. New requirements specific to 7.0
186 Days open = currrent date - creation date
187 */
188
189 x_custom_sql := '
190 select value as VIEWBY
191 ,id as VIEWBYID
192 ,NULL as ENI_MEASURE30
193 ,curr_open_cnt as ENI_MEASURE1
194 ,prev_open_cnt as ENI_MEASURE9
195 ,curr_open_days_cnt/DECODE(curr_open_cnt,0,NULL,curr_open_cnt) as ENI_MEASURE3
196 ,prev_open_days_cnt/DECODE(prev_open_cnt,0,NULL,prev_open_cnt) as ENI_MEASURE11
197 ,curr_past_open_days_cnt/DECODE(curr_open_cnt,0,NULL,curr_open_cnt) as ENI_MEASURE6
198 ,prev_past_open_days_cnt/DECODE(prev_open_cnt,0,NULL,prev_open_cnt) as ENI_MEASURE7
199 ,avg1_cnt as ENI_MEASURE31
200 ,avg2_cnt as ENI_MEASURE32
201 ,avg3_cnt as ENI_MEASURE33
202 ,avg4_cnt as ENI_MEASURE34
203 ,SUM(curr_open_cnt) OVER() as ENI_MEASURE20,
204 (
205 (SUM(curr_open_cnt) OVER() - SUM(prev_open_cnt) OVER())
206 /(DECODE(SUM(prev_open_cnt) OVER(),0,NULL,SUM(prev_open_cnt) OVER()))
207 ) * 100
208 as ENI_MEASURE21,
209 SUM(curr_open_days_cnt) OVER()/DECODE(SUM(curr_open_cnt) OVER(),0,NULL,SUM(curr_open_cnt) OVER() ) as ENI_MEASURE22
210 ,(
211 (
212 SUM(curr_open_days_cnt) OVER()/DECODE(SUM(curr_open_cnt) OVER(),0,NULL,SUM(curr_open_cnt) OVER() )
213 - SUM(prev_open_days_cnt) OVER()/DECODE(SUM(prev_open_cnt) OVER(),0,NULL,SUM(prev_open_cnt) OVER() )
214 )
215 /DECODE(SUM(prev_open_days_cnt) OVER()/DECODE(SUM(prev_open_cnt) OVER(),0,NULL,SUM(prev_open_cnt) OVER() )
216 ,0
217 ,NULL
218 ,SUM(prev_open_days_cnt) OVER()/DECODE(SUM(prev_open_cnt) OVER(),0,NULL,SUM(prev_open_cnt) OVER() )
219 )
220 ) * 100 as ENI_MEASURE23
221 ,SUM( avg1_cnt ) OVER() as ENI_MEASURE25
222 ,SUM( avg2_cnt ) OVER() as ENI_MEASURE26
223 ,SUM( avg3_cnt ) OVER() as ENI_MEASURE27
224 ,SUM( avg4_cnt ) OVER() as ENI_MEASURE28
225 ,(CASE WHEN curr_open_cnt IS NULL OR curr_open_cnt = 0 THEN
226 NULL
227 ELSE
228 ' || l_open_url || '
229 END ) as ENI_MEASURE36
230 ,SUM(curr_past_open_days_cnt) OVER()/DECODE(SUM(curr_open_cnt) OVER(),0,NULL,SUM(curr_open_cnt) OVER() ) as ENI_MEASURE37
231 ,SUM(prev_past_open_days_cnt) OVER()/DECODE(SUM(prev_open_cnt) OVER(),0,NULL,SUM(prev_open_cnt) OVER() ) as ENI_MEASURE38
232 from
233 (
234 SELECT vby.value
235 ,vby.id
236 ,'|| l_item_description || ' as ENI_MEASURE30
237 ,SUM(
238 case
239 When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
240 AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
241 AND (NVL(IMPLEMENTATION_DATE,
242 NVL(CANCELLATION_DATE,(&BIS_CURRENT_ASOF_DATE)+1))) > &BIS_CURRENT_ASOF_DATE
243 Then pdo.cnt
244 Else 0
245 end
246 ) curr_open_cnt,
247 SUM(
248 case
249 When pdo.creation_date <= &BIS_PREVIOUS_ASOF_DATE
250 AND pdo.need_by_date < &BIS_PREVIOUS_ASOF_DATE
251 AND (NVL(IMPLEMENTATION_DATE,
252 NVL(CANCELLATION_DATE,(&BIS_PREVIOUS_ASOF_DATE)+1))) > &BIS_PREVIOUS_ASOF_DATE
253 Then pdo.cnt
254 Else 0
255 End
256 ) prev_open_cnt,
257 SUM(
258 case
259 When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
260 AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
261 AND (NVL(IMPLEMENTATION_DATE,
262 NVL(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1))) > &BIS_CURRENT_ASOF_DATE
263 Then ((&BIS_CURRENT_ASOF_DATE-pdo.creation_date)*pdo.cnt)
264 Else 0
265 end
266 ) curr_open_days_cnt,
267 SUM(
268 case
269 When pdo.creation_date <= &BIS_PREVIOUS_ASOF_DATE
270 AND pdo.need_by_date < &BIS_PREVIOUS_ASOF_DATE
271 AND (NVL(IMPLEMENTATION_DATE,
272 NVL(CANCELLATION_DATE,&BIS_PREVIOUS_ASOF_DATE + 1))) > &BIS_PREVIOUS_ASOF_DATE
273 Then ((&BIS_PREVIOUS_ASOF_DATE-pdo.creation_date)*pdo.cnt)
274 Else 0
275 End
276 ) prev_open_days_cnt,
277 SUM(
278 case
279 When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
280 AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
281 AND (NVL(IMPLEMENTATION_DATE,
282 NVL(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1))) > &BIS_CURRENT_ASOF_DATE
283 Then ((&BIS_CURRENT_ASOF_DATE-pdo.need_by_date)*pdo.cnt)
284 Else 0
285 end
286 ) curr_past_open_days_cnt,
287 SUM(
288 case
289 When pdo.creation_date <= &BIS_PREVIOUS_ASOF_DATE
290 AND pdo.need_by_date < &BIS_PREVIOUS_ASOF_DATE
291 AND (NVL(IMPLEMENTATION_DATE,
292 NVL(CANCELLATION_DATE,&BIS_PREVIOUS_ASOF_DATE + 1))) > &BIS_PREVIOUS_ASOF_DATE
293 Then ((&BIS_PREVIOUS_ASOF_DATE-pdo.need_by_date)*pdo.cnt)
294 Else 0
295 End
296 ) prev_past_open_days_cnt,
297 SUM(
298 case
299 When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
300 AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
301 AND (NVL(IMPLEMENTATION_DATE,
302 NVL(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE + 1))) > &BIS_CURRENT_ASOF_DATE
303 AND (&BIS_CURRENT_ASOF_DATE-pdo.need_by_date) between 0 and 1
304 Then pdo.cnt
305 Else 0
306 end
307 ) avg1_cnt,
308 SUM(
309 case
310 When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
311 AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
312 AND (NVL(IMPLEMENTATION_DATE,
313 NVL(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE + 1))) > &BIS_CURRENT_ASOF_DATE
314 AND (&BIS_CURRENT_ASOF_DATE-pdo.need_by_date) between 2 and 5
315 Then pdo.cnt
316 Else 0
317 end
318 ) avg2_cnt,
319 SUM(
320 case
321 When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
322 AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
323 AND (NVL(IMPLEMENTATION_DATE,
324 NVL(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE + 1))) > &BIS_CURRENT_ASOF_DATE
325 AND (&BIS_CURRENT_ASOF_DATE-pdo.need_by_date) between 6 and 10
326 Then pdo.cnt
327 Else 0
328 end
329 ) avg3_cnt,
330 SUM(
331 case
332 When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
333 AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
334 AND (NVL(IMPLEMENTATION_DATE,
335 NVL(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE + 1))) > &BIS_CURRENT_ASOF_DATE
336 AND (&BIS_CURRENT_ASOF_DATE-pdo.need_by_date) > 10
337 Then pdo.cnt
338 Else 0
339 end
340 ) avg4_cnt
341 FROM
342 eni_dbi_co_dnum_mv pdo,' ||
343 l_viewby_tbl || '
344 WHERE
345 pdo.need_by_date is not null
346 and pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
347 and nvl(pdo.implementation_date, &BIS_CURRENT_ASOF_DATE + 1) > pdo.need_by_date
348 and nvl(pdo.cancellation_date, &BIS_CURRENT_ASOF_DATE + 1) > pdo.need_by_date ' ||
349 l_join_col_name ||
350 l_item_where ||
351 l_priority_where ||
352 l_type_where ||
353 l_reason_where ||
354 l_status_where ||
355 l_org_where || '
356 GROUP BY
357 vby.value,
358 vby.id'
359 || l_item_desc_grp || '
360 )t
361 WHERE
362 curr_open_cnt <> 0 or prev_open_cnt <> 0
363 GROUP BY
364 value,
365 id,
366 ENI_MEASURE30,
367 curr_open_cnt,
368 prev_open_cnt,
369 curr_open_days_cnt,
370 prev_open_days_cnt,
371 curr_past_open_days_cnt,
372 prev_past_open_days_cnt,
373 avg1_cnt,
374 avg2_cnt,
375 avg3_cnt,
376 avg4_cnt
377 ORDER BY
378 ' || l_order_by;
379
380 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
381 x_custom_output := bis_query_attributes_tbl();
382
383 x_custom_output.extend;
384 l_custom_rec.attribute_name := ':ITEM';
385 l_custom_rec.attribute_value := l_item;
386 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
387 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
388 x_custom_output(1) := l_custom_rec;
389
390 x_custom_output.extend;
391 l_custom_rec.attribute_name := ':ORG';
392 l_custom_rec.attribute_value :=replace(l_org,'''');
393 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
394 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
395 x_custom_output(2) := l_custom_rec;
396
397 x_custom_output.extend;
398 l_custom_rec.attribute_name := ':REASON';
399 l_custom_rec.attribute_value := l_reason;
400 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
401 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
402 x_custom_output(3) := l_custom_rec;
403
404 x_custom_output.extend;
405 l_custom_rec.attribute_name := ':PRIORITY';
406 l_custom_rec.attribute_value := l_priority;
407 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
408 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
409 x_custom_output(4) := l_custom_rec;
410
411 x_custom_output.extend;
412 l_custom_rec.attribute_name := ':TYPE';
413 l_custom_rec.attribute_value := replace(l_type,'''');
414 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
415 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
416 x_custom_output(5) := l_custom_rec;
417
418
419 x_custom_output.extend;
420 l_custom_rec.attribute_name := ':STATUS';
421 l_custom_rec.attribute_value := replace(l_status,'''');
422 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
423 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
424 x_custom_output(6) := l_custom_rec;
425
426
427 EXCEPTION
428 WHEN OTHERS THEN
429 NULL;
430 END GET_SQL;
431 END ENI_DBI_PDA_PKG;