[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_PDT_PKG
Source
1 PACKAGE BODY ENI_DBI_PDT_PKG AS
2 /*$Header: ENIPDTPB.pls 120.1 2006/03/23 04:40:45 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_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_lob VARCHAR2(1000);
32 l_org_where VARCHAR2(400);
33 l_item_where VARCHAR2(400);
34 l_priority_where VARCHAR2(400);
35 l_reason_where VARCHAR2(400);
36 l_type_where VARCHAR2(400);
37 l_status_where VARCHAR2(400);
38 l_open_url VARCHAR2(400);
39 l_from_clause VARCHAR2(1000);
40 l_where_clause VARCHAR2(1000);
41 l_group_by_clause VARCHAR2(1000);
42 l_outer_from VARCHAR2(200);
43 l_outer_where VARCHAR2(1000);
44 l_order VARCHAR2(20);
45
46 BEGIN
47
48 x_custom_output := bis_query_attributes_tbl();
49
50 ENI_DBI_UTIL_PKG.get_parameters( p_page_parameter_tbl
51 , l_period_type
52 , l_period_bitand
53 , l_view_by
54 , l_as_of_date
55 , l_prev_as_of_date
56 , l_report_start
57 , l_cur_period
58 , l_days_into_period
59 , l_comp_type
60 , l_category
61 , l_item
62 , l_org
63 , l_id_column
64 , l_order_by
65 , l_drill
66 , l_status
67 , l_priority
68 , l_reason
69 , l_lifecycle_phase
70 , l_currency
71 , l_bom_type
72 , l_type
73 , l_manager
74 , l_lob
75 );
76
77 /* Bug: 3394222 Rolling Period Conversion. New requirements specific to 7.0 */
78
79 /* Bug Fix: 3380925
80 Added ENI_MEASURE6,ENI_MEASURE7
81 Reverted the calculation to (current_date - need_by_date)
82
83 */
84
85 eni_dbi_util_pkg.get_time_clauses
86 (
87 'I',
88 'pdo',
89 l_period_type,
90 l_period_bitand,
91 l_as_of_date,
92 l_prev_as_of_date,
93 l_report_start,
94 l_cur_period,
95 l_days_into_period,
96 l_comp_type,
97 l_id_column,
98 l_from_clause,
99 l_where_clause,
100 l_group_by_clause,
101 'ROLLING'
102 );
103
104
105 IF INSTR(l_order_by,' DESC') > 0 THEN
106 l_order := ' DESC';
107 ELSE
108 l_order := ' ASC';
109 END IF;
110
111 l_outer_where := ' AND t.name = ftrs.name (+)
112 AND t.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE ';
113
114 l_open_url :='null';
115
116
117 IF (l_priority IS NULL OR l_priority = '' OR l_priority = 'All')
118 THEN
119 l_priority_where:= '';
120 ELSE
121 l_priority_where := ' and pdo.priority_code = :PRIORITY';
122 END IF;
123
124
125 IF (l_type IS NULL OR l_type = '' OR l_type = 'All')
126 THEN
127 l_type_where := '';
128 ELSE
129 l_type_where := ' and pdo.change_order_type_id = :TYPE';
130 END IF;
131
132
133 IF (l_reason IS NULL OR l_reason = '' OR l_reason = 'All')
134 THEN
135 l_reason_where := '';
136 ELSE
137 l_reason_where := ' and pdo.reason_code = :REASON';
138 END IF;
139
140
141 IF (l_status IS NULL OR l_status = '' OR l_status = 'All')
142 THEN
143 l_status_where := '';
144 ELSE
145 l_status_where := ' and pdo.status_type = :STATUS';
146 END IF;
147
148
149 IF (l_org IS NULL OR l_org = '' OR l_org = 'All')
150 THEN
151 l_org_where := '';
152 ELSE
153 l_org_where := ' AND pdo.organization_id = :ORG';
154 END IF;
155
156
157 IF (l_item IS NULL OR l_item = '' OR l_item = 'All')
158 THEN
159 X_CUSTOM_SQL:='
160 SELECT NULL AS VIEWBY,
161 NULL AS ENI_MEASURE1,
162 NULL AS ENI_MEASURE9,
163 NULL AS ENI_MEASURE10,
164 NULL AS ENI_MEASURE3,
165 NULL AS ENI_MEASURE6,
166 NULL AS ENI_MEASURE7,
167 NULL AS ENI_MEASURE11,
168 NULL AS ENI_MEASURE31,
169 NULL AS ENI_MEASURE32,
170 NULL AS ENI_MEASURE33,
171 NULL AS ENI_MEASURE34,
172 NULL AS ENI_MEASURE41,
173 NULL AS ENI_MEASURE42,
174 NULL AS ENI_MEASURE43,
175 NULL AS ENI_MEASURE44,
176 NULL AS ENI_MEASURE36
177 FROM DUAL';
178 RETURN;
179 ELSE
180 l_item_where := ' AND pdo.item_id = :ITEM';
181 END IF;
182
183 x_custom_sql := '
184 select t.name as VIEWBY
185 ,curr_open_cnt as ENI_MEASURE1
186 ,prev_open_cnt as ENI_MEASURE9
187 ,NVL(curr_open_cnt,0) as ENI_MEASURE10
188 ,curr_open_days_cnt/DECODE(curr_open_cnt,0,NULL,curr_open_cnt) as ENI_MEASURE3
189 ,prev_open_days_cnt/DECODE(prev_open_cnt,0,NULL,prev_open_cnt) as ENI_MEASURE11
190 ,curr_past_open_days_cnt/DECODE(curr_open_cnt,0,NULL,curr_open_cnt) as ENI_MEASURE6
191 ,prev_past_open_days_cnt/DECODE(prev_open_cnt,0,NULL,prev_open_cnt) as ENI_MEASURE7
192 ,avg1_cnt as ENI_MEASURE31
193 ,avg2_cnt as ENI_MEASURE32
194 ,avg3_cnt as ENI_MEASURE33
195 ,avg4_cnt as ENI_MEASURE34
196 ,NULL AS ENI_MEASURE41
197 ,NULL AS ENI_MEASURE42
198 ,NULL AS ENI_MEASURE43
199 ,NULL AS ENI_MEASURE44
200 ,NULL as ENI_MEASURE36
201 from
202 (
203 SELECT t.name,
204 t.start_date,
205 t.c_end_date,
206 SUM(
207 case
208 When pdo.creation_date <= t.c_end_date
209 AND pdo.need_by_date < t.c_end_date
210 AND (NVL(IMPLEMENTATION_DATE,
211 NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
212 Then pdo.cnt
213 Else NULL
214 end
215 ) curr_open_cnt,
216 SUM(
217 case
218 When pdo.creation_date <= t.p_end_date
219 AND pdo.need_by_date < t.p_end_date
220 AND (NVL(IMPLEMENTATION_DATE,
221 NVL(CANCELLATION_DATE,t.p_end_date+1))) > t.p_end_date
222 Then pdo.cnt
223 Else NULL
224 End
225 ) prev_open_cnt,
226 SUM(
227 case
228 When pdo.creation_date <= t.c_end_date
229 AND pdo.need_by_date < t.c_end_date
230 AND (NVL(IMPLEMENTATION_DATE,
231 NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
232 Then ((t.c_end_date - pdo.creation_date)*pdo.cnt)
233 Else NULL
234 end
235 ) curr_open_days_cnt,
236 SUM(
237 case
238 When pdo.creation_date <= t.p_end_date
239 AND pdo.need_by_date < t.p_end_date
240 AND (NVL(IMPLEMENTATION_DATE,
241 NVL(CANCELLATION_DATE,t.p_end_date+1))) > t.p_end_date
242 Then ((t.p_end_date-pdo.creation_date)*pdo.cnt)
243 Else NULL
244 End
245 ) prev_open_days_cnt,
246 SUM(
247 case
248 When pdo.creation_date <= t.c_end_date
249 AND pdo.need_by_date < t.c_end_date
250 AND (NVL(IMPLEMENTATION_DATE,
251 NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
252 Then ((t.c_end_date -pdo.need_by_date)*pdo.cnt)
253 Else NULL
254 end
255 ) curr_past_open_days_cnt,
256 SUM(
257 case
258 When pdo.creation_date <= t.p_end_date
259 AND pdo.need_by_date < t.p_end_date
260 AND (NVL(IMPLEMENTATION_DATE,
261 NVL(CANCELLATION_DATE,t.p_end_date+1))) > t.p_end_date
262 Then ((t.p_end_date-pdo.need_by_date)*pdo.cnt)
263 Else NULL
264 End
265 ) prev_past_open_days_cnt,
266 SUM(
267 case
268 When pdo.creation_date <= t.c_end_date
269 AND pdo.need_by_date < t.c_end_date
270 AND (NVL(IMPLEMENTATION_DATE,
271 NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
272 AND (t.c_end_date-pdo.need_by_date) between 0 and 1
273 Then pdo.cnt
274 Else NULL
275 end
276 ) avg1_cnt,
277 SUM(
278 case
279 When pdo.creation_date <= t.c_end_date
280 AND pdo.need_by_date < t.c_end_date
281 AND (NVL(IMPLEMENTATION_DATE,
282 NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
283 AND (t.c_end_date-pdo.need_by_date) between 2 and 5
284 Then pdo.cnt
285 Else NULL
286 end
287 ) avg2_cnt,
288 SUM(
289 case
290 When pdo.creation_date <= t.c_end_date
291 AND pdo.need_by_date < t.c_end_date
292 AND (NVL(IMPLEMENTATION_DATE,
293 NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
294 AND (t.c_end_date-pdo.need_by_date) between 6 and 10
295 Then pdo.cnt
296 Else NULL
297 end
298 ) avg3_cnt,
299 SUM(
300 case
301 When pdo.creation_date <= t.c_end_date
302 AND pdo.need_by_date < t.c_end_date
303 AND (NVL(IMPLEMENTATION_DATE,
304 NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
305 AND (t.c_end_date-pdo.need_by_date) > 10
306 Then pdo.cnt
307 Else NULL
308 end
309 ) avg4_cnt
310
311 FROM
312 eni_dbi_co_dnum_mv pdo,' ||
313 l_from_clause || '
314 WHERE
315 pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
316 AND pdo.need_by_date is not null' ||
317 l_item_where ||
318 l_priority_where ||
319 l_type_where ||
320 l_reason_where ||
321 l_status_where ||
322 l_org_where || '
323 GROUP BY
324 ' || l_group_by_clause || '
325 )ftrs,' || l_from_clause || '
326 WHERE
327 1 = 1
328 and t.name = ftrs.name(+)
329 ORDER BY t.start_date' || l_order;
330
331
332
333
334 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
335 x_custom_output := bis_query_attributes_tbl();
336
337 x_custom_output.extend;
338 l_custom_rec.attribute_name := ':ITEM';
339 l_custom_rec.attribute_value := l_item;
340 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
341 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
342 x_custom_output(1) := l_custom_rec;
343
344 x_custom_output.extend;
345 l_custom_rec.attribute_name := ':ORG';
346 l_custom_rec.attribute_value := replace(l_org,'''');
347 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
348 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
349 x_custom_output(2) := l_custom_rec;
350
351 x_custom_output.extend;
352 l_custom_rec.attribute_name := ':REASON';
353 l_custom_rec.attribute_value := l_reason;
354 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
355 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
356 x_custom_output(3) := l_custom_rec;
357
358 x_custom_output.extend;
359 l_custom_rec.attribute_name := ':PRIORITY';
360 l_custom_rec.attribute_value := l_priority;
361 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
362 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
363 x_custom_output(4) := l_custom_rec;
364
365 x_custom_output.extend;
366 l_custom_rec.attribute_name := ':TYPE';
367 l_custom_rec.attribute_value := replace(l_type,'''');
368 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
369 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
370 x_custom_output(5) := l_custom_rec;
371
372 x_custom_output.extend;
373 l_custom_rec.attribute_name := ':STATUS';
374 l_custom_rec.attribute_value := replace(l_status,'''');
375 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
376 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
377 x_custom_output(6) := l_custom_rec;
378
379 --Bug 5083652 -- Start Code
380
381 x_custom_output.extend;
382 l_custom_rec.attribute_name := ':PERIODTYPE';
383 l_custom_rec.attribute_value := REPLACE(l_period_type,'''');
384 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
385 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
386 x_custom_output(7) := l_custom_rec;
387
388 x_custom_output.extend;
389 l_custom_rec.attribute_name := ':COMPARETYPE';
390 l_custom_rec.attribute_value := REPLACE(l_comp_type,'''');
391 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
392 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
393 x_custom_output(8) := l_custom_rec;
394
395
396 x_custom_output.extend;
397 l_custom_rec.attribute_name := ':PERIODAND';
398 l_custom_rec.attribute_value := REPLACE(l_period_bitand,'''');
399 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
400 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
401 x_custom_output(9) := l_custom_rec;
402
403 x_custom_output.extend;
404 l_custom_rec.attribute_name := ':CUR_PERIOD_ID';
405 l_custom_rec.attribute_value := REPLACE(l_cur_period,'''');
406 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
407 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
408 x_custom_output(10) := l_custom_rec;
409
410 --Bug 5083652 -- End Code
411
412 END GET_SQL;
413
414 END ENI_DBI_PDT_PKG;