eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ENI_DBI_COT_PKG

Source


1 PACKAGE BODY ENI_DBI_COT_PKG AS
2 /*$Header: ENICOTPB.pls 120.4 2006/03/31 05:10:12 sdebroy 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_priority_out_where	VARCHAR2(1000);
40 l_reason_where		VARCHAR2(1000);
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_report		VARCHAR2(1000);
49 l_coltype		VARCHAR2(1000);
50 l_impl_url		VARCHAR2(1000);
51 BEGIN
52 
53 		x_custom_output := bis_query_attributes_tbl();
54 
55 			ENI_DBI_UTIL_PKG.get_parameters( p_page_parameter_tbl
56                                  , l_period_type
57                                  , l_period_bitand
58                                  , l_view_by
59                                  , l_as_of_date
60                                  , l_prev_as_of_date
61                                  , l_report_start
62                                  , l_cur_period
63                                  , l_days_into_period
64                                  , l_comp_type
65                                  , l_category
66                                  , l_item
67                                  , l_org
68                                  , l_id_column
69                                  , l_order_by
70                                  , l_drill
71                                  , l_status
72                                  , l_priority
73                                  , l_reason
74                                  , l_lifecycle_phase
75                                  , l_currency
76                                  , l_bom_type
77                                  , l_type
78                                  , l_manager
79 				 , l_lob
80                                  );
81 
82 
83 			ENI_DBI_UTIL_PKG.get_time_clauses
84             		(
85                         	'I',
86 				                  'edcs',
87         	                l_period_type,
88                 	        l_period_bitand,
89                         	l_as_of_date,
90 	                        l_prev_as_of_date,
91         	                l_report_start,
92                 	        l_cur_period,
93                         	l_days_into_period,
94 	                        l_comp_type,
95         	                l_id_column,
96                 	        l_from_clause,
97                         	l_where_clause,
98 				l_group_by_clause,
99 				'ROLLING'
100         		);
101 
102 
103 	IF(l_order_by like '%DESC%')
104 	THEN
105 		l_order_by:=' t.start_date desc ';
106 	ELSIF(l_order_by like '%ASC%')
107 	THEN
108 		l_order_by:=' t.start_date asc ';
109 	ELSE
110 		l_order_by:=' t.start_date asc ';
111 	END IF;
112 
113 
114 --  l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
115 
116 	IF (l_priority IS NULL OR l_priority = '' OR l_priority = 'All')
117 	THEN
118 		null;
119 		l_priority:='';
120 
121 	ELSE
122 		l_priority_where := ' and edcs.priority_code= :PRIORITY ';
123 	END IF;
124 
125 	IF (l_type IS NULL OR l_type = '' OR l_type = 'All')
126 	THEN
127 		l_type_where := 'and edcs.change_order_type_id is null';
128 
129 	ELSE
130 		l_type_where := ' and edcs.change_order_type_id= :TYPE ';
131 	END IF;
132 
133 
134 	IF (l_reason IS NULL OR l_reason = '' OR l_reason = 'All')
135 	THEN
136 		l_reason_where := 'and edcs.reason_code is null';
137 
138 	ELSE
139 		l_reason_where := ' and edcs.reason_code = :REASON ';
140 	END IF;
141 
142 	IF (l_org IS NULL OR l_org = '' OR l_org = 'All')
143 	THEN
144 		l_org_where := '';
145 
146 	ELSE
147 		l_org_where := ' AND edcs.organization_id = :ORG ';
148 	END IF;
149 
150 -- Bug : 3465553
151 /*
152 l_impl_url:='''pFunctionName=ENI_DBI_COL_IMPL_R&pCustomView=ENI_DBI_COL_CV2&REPORTED=IMPL'||
153 '&start_date=''||to_char(t.start_date,''dd-mm-yyyy'')||''&end_date=''||to_char(t.c_end_date,''dd-mm-yyyy'')||''&''||''AS_OF_DATE=''||to_char(t.c_end_date,''dd-mm-yyyy'')';
154 */
155 -- Bug : 3465553
156 l_impl_url :=null;
157 
158 	IF (l_item IS NULL OR l_item = '' OR l_item = 'All')
159 	THEN
160 		l_item_where := '';
161 		x_custom_sql :='
162 				select	null as VIEWBY,
163 					null as ENI_MEASURE1,
164 					null as ENI_MEASURE10,
165 					null as ENI_MEASURE9,
166 					null as ENI_MEASURE3,
167 					null as ENI_MEASURE6,
168 					null as ENI_MEASURE7,
169 					null as ENI_MEASURE8,
170 					null as ENI_MEASURE16,
171 					null as ENI_MEASURE17,
172 					null as ENI_MEASURE18,
173 					null as ENI_MEASURE19,
174 					null as ENI_MEASURE11,
175 					null as ENI_MEASURE12,
176 					null as ENI_MEASURE13,
177 					null as ENI_MEASURE21,
178 					null as ENI_MEASURE22,
179 					null as ENI_MEASURE23,
180 					null as ENI_MEASURE25,
181 					null as ENI_MEASURE26,
182 					null as ENI_MEASURE27,
183 					null as ENI_MEASURE28,
184 					null as ENI_MEASURE40
185 
186 
187 
188 				from	dual';
189 				return;
190 	ELSE
191 		l_item_where := ' AND edcs.item_id = :ITEM ';
192 	END IF;
193 
194 x_custom_sql :=  '
195 	SELECT
196 	t.name as VIEWBY,
197 
198 	SUM(CASE WHEN
199 			ftrs.report_date = t.c_end_date
200 		THEN	ftrs.IMPLEMENTED_SUM
201 		ELSE	null
202 		END)	as ENI_MEASURE1,
203 
204 	nvl(SUM(CASE WHEN
205 			ftrs.report_date = t.c_end_date
206 		THEN	ftrs.IMPLEMENTED_SUM
207 		ELSE	0
208 		END),0)	as ENI_MEASURE10,
209 
210 	SUM(CASE WHEN
211 			ftrs.report_date =t.p_end_date
212 		THEN	ftrs.IMPLEMENTED_SUM
213 		ELSE	null
214 		END)	as ENI_MEASURE9,
215 
216 	SUM(CASE WHEN
217 			ftrs.report_date =t.c_end_date
218 		THEN	NVL(ftrs.CREATE_TO_APPROVE_SUM,0)
219 		ELSE	null
220 		END)
221 	/
222 	decode(SUM(CASE WHEN
223 			ftrs.report_date =t.c_end_date
224 		THEN	ftrs.CREATE_TO_APPROVE_CNT
225 		ELSE	null
226 		END),0,1,SUM(CASE WHEN
227 			ftrs.report_date =t.c_end_date
228 		THEN	ftrs.CREATE_TO_APPROVE_CNT
229 		ELSE	null
230 		END))	 as ENI_MEASURE3,
231 
232 
233 
234 	SUM(CASE WHEN
235 			ftrs.report_date =t.c_end_date
236 		THEN	NVL(ftrs.APPROVE_TO_IMPL_SUM,0)
237 		ELSE	null
238 		END)
239 	/
240 	decode(SUM(CASE WHEN
241 			ftrs.report_date =t.c_end_date
242 		THEN	ftrs.APPROVE_TO_IMPL_CNT
243 		ELSE	null
244 		END),0,1,SUM(CASE WHEN
245 			ftrs.report_date =t.c_end_date
246 		THEN	ftrs.APPROVE_TO_IMPL_CNT
247 		ELSE	null
248 		END))	as ENI_MEASURE6,
249 
250 	SUM(CASE WHEN
251 			ftrs.report_date =t.c_end_date
252 		THEN	ftrs.CYCLE_TIME_SUM
253 		ELSE	null
254 		END)
255 	/
256 	decode(SUM(CASE WHEN
257 			ftrs.report_date = t.c_end_date
258 		THEN	ftrs.IMPLEMENTED_SUM
259 		ELSE	null
260 		END),0,1,SUM(CASE WHEN
261 			ftrs.report_date = t.c_end_date
262 		THEN	ftrs.IMPLEMENTED_SUM
263 		ELSE	null
264 		END)) as ENI_MEASURE7,
265 
266 	SUM(CASE WHEN
267 			ftrs.report_date =t.p_end_date
268 		THEN	NVL(ftrs.CYCLE_TIME_SUM,0)
269 		ELSE	null
270 		END)
271 	/
272 	decode(SUM(CASE WHEN
273 			ftrs.report_date =t.p_end_date
274 		THEN	ftrs.IMPLEMENTED_SUM
275 		ELSE	null
276 		END),0,1,SUM(CASE WHEN
277 			ftrs.report_date =t.p_end_date
278 		THEN	ftrs.IMPLEMENTED_SUM
279 		ELSE	null
280 		END)) as ENI_MEASURE8,
281 
282 	SUM(CASE WHEN
283 			ftrs.report_date =t.c_end_date
284 		THEN	ftrs.BUCKET1_SUM
285 		ELSE	null
286 		END)	as ENI_MEASURE16 ,
287 
288 	SUM(CASE WHEN
289 			ftrs.report_date =t.c_end_date
290 		THEN	ftrs.BUCKET2_SUM
291 		ELSE	null
292 		END)	as ENI_MEASURE17 ,
293 
294 	SUM(CASE WHEN
295 			ftrs.report_date =t.c_end_date
296 		THEN	ftrs.BUCKET3_SUM
297 		ELSE	null
298 		END)	as ENI_MEASURE18 ,
299 
300 	SUM(CASE WHEN
301 			ftrs.report_date =t.c_end_date
302 		THEN	ftrs.BUCKET4_SUM
303 		ELSE	null
304 		END)	as ENI_MEASURE19,
305 
306 	SUM(CASE WHEN	ftrs.report_date = t.c_end_date
307 					and ftrs.priority_level=0
308 				THEN	ftrs.CYCLE_TIME_SUM
309 				else 0 end)
310 					/
311 			decode(sum( case when ftrs.report_date=t.c_end_date
312 					and ftrs.priority_level=0
313 					then ftrs.implemented_sum
314 					else 0 end ),0,1,sum( case when ftrs.report_date=t.c_end_date
315 					and ftrs.priority_level=0
316 					then ftrs.implemented_sum
317 					else 0 end ))
318 					as ENI_MEASURE11,
319 
320 	SUM(CASE WHEN	ftrs.report_date = t.c_end_date
321 					and ftrs.priority_level=1
322 				THEN	ftrs.CYCLE_TIME_SUM
323 				else 0 end)
324 					/
325 			decode(sum( case when ftrs.report_date=t.c_end_date
326 					and ftrs.priority_level=1
327 					then ftrs.implemented_sum
328 					else 0 end ),0,1,sum( case when ftrs.report_date=t.c_end_date
329 					and ftrs.priority_level=1
330 					then ftrs.implemented_sum
331 					else 0 end ))
332 					as ENI_MEASURE12,
333 
334 	SUM(CASE WHEN	ftrs.report_date = t.c_end_date
335 					and nvl(ftrs.priority_level,2) <> 0 and nvl(ftrs.priority_level,2) <> 1
336 				THEN	ftrs.CYCLE_TIME_SUM
337 				else 0 end)
338 					/
339 			decode(sum( case when ftrs.report_date=t.c_end_date
340 					and nvl(ftrs.priority_level,2) <> 0 and nvl(ftrs.priority_level,2) <> 1
341 					then ftrs.implemented_sum
342 					else 0 end ),0,1,sum( case when ftrs.report_date=t.c_end_date
343 					and  nvl(ftrs.priority_level,2) <> 0 and nvl(ftrs.priority_level,2) <> 1
344 					then ftrs.implemented_sum
345 					else 0 end ))
346 					as ENI_MEASURE13,
347 			10 as ENI_MEASURE21,
348 			11 as ENI_MEASURE22,
349 			12 as ENI_MEASURE23,
350 			13 as ENI_MEASURE25,
351 			14 as ENI_MEASURE26,
352 			15 as ENI_MEASURE27,
353 			16 as ENI_MEASURE28,
354 			NULL as ENI_MEASURE40
355 
356 FROM
357 	(	SELECT edcs.*,t.c_end_date,t.p_end_date,prio.*,ftrs.report_date
358 		FROM ENI_DBI_CO_SUM_MV edcs,eni_chg_mgmt_priority_v prio, fii_time_structures ftrs,'||l_from_clause||'
359 		WHERE
360 		edcs.status_type is null
361 		' || l_item_where ||'
362 		' || l_org_where || '
363 		' || l_type_where || '
364 		' || l_priority_where || '
365 		' || l_reason_where|| '
366 		' || l_cat_where || '
367 		and edcs.priority_code is not null
368 		and prio.id(+)=edcs.priority_code
369 		and (ftrs.report_date=t.c_end_date  OR ftrs.report_date=t.p_end_date )
370 	  and edcs.time_id (+) = ftrs.time_id
371 	  and edcs.period_type_id (+) = ftrs.period_type_id
372 	  and bitand(ftrs.record_type_id,:PERIODAND) = :PERIODAND --Bug 5083882
373 	) ftrs,'||l_from_clause||'
374 	WHERE ftrs.c_end_date(+) = t.c_end_date
375 GROUP BY
376 	t.name,t.start_date,t.c_end_date
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 
384 
385  x_custom_output.extend;
386  l_custom_rec.attribute_name := ':ITEM';
387  l_custom_rec.attribute_value := l_item;
388  l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
389  l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
390  x_custom_output(1) := l_custom_rec;
391 
392 
393  x_custom_output.extend;
394  l_custom_rec.attribute_name := ':ORG';
395  l_custom_rec.attribute_value := replace(l_org,'''');
396  l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
397  l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
398  x_custom_output(2) := l_custom_rec;
399 
400  x_custom_output.extend;
401  l_custom_rec.attribute_name := ':REASON';
402  l_custom_rec.attribute_value := l_reason;
403  l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
404  l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
405  x_custom_output(3) := l_custom_rec;
406 
407  x_custom_output.extend;
408  l_custom_rec.attribute_name := ':PRIORITY';
409  l_custom_rec.attribute_value := l_priority;
410  l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
411  l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
412  x_custom_output(4) := l_custom_rec;
413 
414  x_custom_output.extend;
415  l_custom_rec.attribute_name := ':STATUS';
416  l_custom_rec.attribute_value := replace(l_status,'''');
417  l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
418  l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
419  x_custom_output(5) := l_custom_rec;
420 
421  x_custom_output.extend;
422  l_custom_rec.attribute_name := ':TYPE';
423  l_custom_rec.attribute_value := replace(l_type,'''');
424  l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
425  l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
426  x_custom_output(6) := l_custom_rec;
427 
428  --Bug 5083882 - Start Code
429  x_custom_output.extend;
430  l_custom_rec.attribute_name := ':PERIODAND'; --Bug 5083652
431  l_custom_rec.attribute_value := replace(l_period_bitand,'''');
432  l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
433  l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
434  x_custom_output(7) := l_custom_rec;
435 --Bug 5083882 - End Code
436 
437 --Bug 5083652 --	Start Code
438   x_custom_output.extend;
439   l_custom_rec.attribute_name := ':PERIODTYPE';
440   l_custom_rec.attribute_value := REPLACE(l_period_type,'''');
441   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
442   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
443   x_custom_output(8) := l_custom_rec;
444 
445    x_custom_output.extend;
446   l_custom_rec.attribute_name := ':COMPARETYPE';
447   l_custom_rec.attribute_value := REPLACE(l_comp_type,'''');
448   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
449   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
450   x_custom_output(9) := l_custom_rec;
451 
452   x_custom_output.extend;
453   l_custom_rec.attribute_name := ':CUR_PERIOD_ID';
454   l_custom_rec.attribute_value := REPLACE(l_cur_period,'''');
455   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
456   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
457   x_custom_output(10) := l_custom_rec;
458 --Bug 5083652 --	End Code
459 
460 
461 END GET_SQL;
462 END ENI_DBI_COT_PKG;