[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_COL_PKG
Source
1 PACKAGE BODY ENI_DBI_COL_PKG AS
2 /*$Header: ENICOLPB.pls 120.0 2005/05/26 19:34:21 appldev 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_period_type VARCHAR2(1000);
13 l_period_bitand NUMBER;
14 l_view_by VARCHAR2(1000);
15 l_as_of_date DATE;
16 l_prev_as_of_date DATE;
17 l_report_start DATE;
18 l_cur_period NUMBER;
19 l_days_into_period NUMBER;
20 l_comp_type VARCHAR2(100);
21 l_category VARCHAR2(100);
22 l_item VARCHAR2(100);
23 l_org VARCHAR2(100);
24 l_id_column VARCHAR2(100);
25 l_order_by VARCHAR2(1000);
26 l_drill VARCHAR2(100);
27 l_status VARCHAR2(100);
28 l_priority VARCHAR2(100);
29 l_reason VARCHAR2(100);
30 l_lifecycle_phase VARCHAR2(100);
31 l_currency VARCHAR2(100);
32 l_bom_type VARCHAR2(100);
33 l_type VARCHAR2(100);
34 l_manager VARCHAR2(100);
35 l_lob VARCHAR2(1000);
36 l_org_where VARCHAR2(1000);
37 l_cat_where VARCHAR2(1000);
38 l_item_where VARCHAR2(1000);
39 l_priority_where VARCHAR2(1000);
40 l_reason_where VARCHAR2(1000);
41 l_type_where VARCHAR2(1000);
42 l_group_by_clause VARCHAR2(1000);
43 l_priority_from VARCHAR2(1000);
44 l_reason_from VARCHAR2(1000);
45 l_status_where VARCHAR2(1000);
46 l_report VARCHAR2(1000);
47 l_start_date DATE;
48 l_end_date DATE;
49 l_impl_where VARCHAR2(1000);
50 l_canc_where VARCHAR2(1000);
51 l_past_where VARCHAR2(1000);
52 l_open_where VARCHAR2(1000);
53 l_final_where VARCHAR2(1000);
54 l_new_where VARCHAR2(1000);
55 l_priority_value VARCHAR2(1000);
56 l_reason_value VARCHAR2(1000);
57 l_oa_url VARCHAR2(1000);
58 BEGIN
59
60 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
61 x_custom_output := bis_query_attributes_tbl();
62
63 ENI_DBI_UTIL_PKG.get_parameters( p_page_parameter_tbl
64 , l_period_type
65 , l_period_bitand
66 , l_view_by
67 , l_as_of_date
68 , l_prev_as_of_date
69 , l_report_start
70 , l_cur_period
71 , l_days_into_period
72 , l_comp_type
73 , l_category
74 , l_item
75 , l_org
76 , l_id_column
77 , l_order_by
78 , l_drill
79 , l_status
80 , l_priority
81 , l_reason
82 , l_lifecycle_phase
83 , l_currency
84 , l_bom_type
85 , l_type
86 , l_manager
87 , l_lob
88 );
89
90 get_col_parameters( p_page_parameter_tbl
91 , l_report
92 , l_start_date
93 , l_end_date
94 );
95
96 if(l_start_date is null)
97 then
98 l_start_date:= l_as_of_date - l_days_into_period ;
99
100 end if;
101 if(l_end_date is null)
102 then
103 l_end_date:= l_as_of_date;
104 end if;
105
106 -- l_oa_url:='OA.jsp?OAFunc=ENG_CHANGE_DETAIL_PAGE&changeId=';
107 -- Bug : 3560677.Modified the drill URL
108
109 l_oa_url:='pFunctionName=ENG_CHANGE_SUMMARY_PAGE&OAPB=ENI_CHG_MGMT_PROD_BRNDNG_TEXT';
110
111 -- Bug : 3487387 changed the where clauses . Bug caused by the fix of 3472006.
112 l_impl_where:=' and eco.implementation_date > :START_DATE and eco.implementation_date <= :END_DATE';
113 l_canc_where:=' and eco.cancellation_date > :START_DATE and eco.cancellation_date <= :END_DATE';
114 l_new_where:= ' and eco.CREATION_date > :START_DATE and eco.creation_date <= :END_DATE ';
115 l_open_where:=' and eco.creation_date <= :END_DATE
116 and nvl(eco.implementation_date,:END_DATE +1) > :END_DATE
117 and nvl(eco.cancellation_date,:END_DATE +1) > :END_DATE ';
118 l_past_where:=' and eco.need_by_date is not null
119 and eco.need_by_date < :END_DATE
120 AND NVL(IMPLEMENTATION_DATE, :END_DATE +1) > greatest(ECO.NEED_BY_DATE,:END_DATE)
121 AND NVL(CANCELLATION_DATE,:END_DATE +1) > greatest(ECO.NEED_BY_DATE,:END_DATE)';
122
123 IF (l_priority IS NULL OR l_priority = '' OR l_priority = 'All')
124 THEN
125 l_priority_from:=' ,eni_chg_mgmt_priority_v prio ';
126 l_priority_value:=' prio.value ';
127 l_priority_where:= ' and prio.id(+)=eco.priority_code ';
128 ELSE
129 l_priority_from:=' ,eni_chg_mgmt_priority_v prio ';
130 l_priority_value:=' prio.value ';
131 l_priority_where := ' and prio.id(+)=eco.priority_code and eco.priority_code = :PRIORITY_ID';
132 END IF;
133
134 IF (l_type IS NULL OR l_type = '' OR l_type = 'All')
135 THEN
136 l_type_where := '';
137
138 ELSE
139 l_type_where := ' and eco.change_order_type_id = :TYPE_ID ';
140 END IF;
141
142 --added for Bug 3435046
143
144 IF (l_reason IS NULL OR l_reason = '' OR l_reason = 'All')
145 THEN
146 l_reason_from:=' ,eni_chg_mgmt_reason_v rea ';
147 l_reason_value:=' rea.value ';
148 l_reason_where:= ' and rea.id(+)=eco.reason_code ';
149 ELSE
150 l_reason_from:=' ,eni_chg_mgmt_reason_v rea ';
151 l_reason_value:=' rea.value ';
152 l_reason_where := ' and rea.id(+)=eco.reason_code and eco.reason_code = :REASON_ID';
153 END IF;
154
155 --added for Bug 3435046
156
157 IF (l_status IS NULL OR l_status = '' OR l_status = 'All')
158 THEN
159 l_status_where := '';
160
161 ELSE
162 l_status_where := ' and eco.status_type = :STATUS_ID ';
163 END IF;
164
165 IF (l_org IS NULL OR l_org = '' OR l_org = 'All')
166 THEN
167 l_org_where := '';
168
169 ELSE
170 l_org_where := ' AND eco.organization_id = :ORGANIZATION_ID ';
171 END IF;
172
173 if(l_item= 'All' or l_item ='' or l_item is null)
174 then
175 x_custom_sql :=
176 '
177 SELECT
178 NULL as ENI_MEASURE1,
179 NULL as ENI_MEASURE2,
180 NULL as ENI_MEASURE3,
181 NULL as ENI_MEASURE4,
182 NULL as ENI_MEASURE20,
183 NULL as ENI_MEASURE5,
184 NULL as ENI_MEASURE6,
185 NULL as ENI_MEASURE7,
186 NULL as ENI_MEASURE8,
187 NULL as ENI_MEASURE9,
188 NULL as ENI_MEASURE10,
189 NULL as ENI_MEASURE11,
190 NULL as ENI_MEASURE12,
191 NULL as ENI_MEASURE13,
192 NULL as ENI_MEASURE14,
193 NULL as ENI_ATTRIBUTE2
194
195 FROM
196 DUAL
197 ';
198 return;
199
200 else
201 l_item_where:=' and eco.item_id = :ITEM_ID';
202 if(l_report='NEW')
203 then
204 l_final_where:=l_new_where;
205 elsif(l_report='IMPL')
206 then
207 l_final_where:=l_impl_where;
208 elsif(l_report='OPEN')
209 then
210 l_final_where:=l_open_where;
211 elsif(l_report='PAST')
212 then
213 l_final_where:=l_past_where;
214 elsif(l_report='CANC')
215 then
216 l_final_where:=l_canc_where;
217 end if;
218 end if;
219
220 /* Bug : 3221341 : Modified the select clause to calculate the Days Open only for Non-Implementated
221 and Non-Cancelled Change Orders */
222
223 x_custom_sql :=
224 '
225 SELECT
226 change_notice as ENI_MEASURE1,
227 eco.description as ENI_MEASURE2,
228 stat.value as ENI_MEASURE3,
229 '||l_priority_value||' as ENI_MEASURE4,
230 '||l_reason_value||' as ENI_MEASURE20,
231 typ.value as ENI_MEASURE5,
232 eco.creation_date as ENI_MEASURE6,
233 need_by_date as ENI_MEASURE7,
234 (case when :END_DATE < eco.implementation_date
235 then null else eco.implementation_date end )as ENI_MEASURE8,
236 ( case when :END_DATE < eco.implementation_date
237 then null
238 else (eco.implementation_date - eco.creation_date +1) end ) as ENI_MEASURE9,
239 ( case when nvl(nvl(cancellation_date,implementation_date),:NVL_DATE) > :END_DATE
240 then (:END_DATE - eco.creation_date)
241 else null end ) as ENI_MEASURE10,
242 nvl(ass.party_name,'' '') as ENI_MEASURE11,
243 :END_DATE - eco.need_by_date as ENI_MEASURE12,
244 (case when :END_DATE < eco.cancellation_date
245 then null else eco.cancellation_date end )as ENI_MEASURE13,
246 :OA_URL||''&retainAM=N&changeId=''|| TO_CHAR(ECO.CHANGE_ID) AS ENI_MEASURE14 ,
247 ECO.CHANGE_ID as ENI_ATTRIBUTE2
248 FROM
249 eni_dbi_co_num_mv eco,
250 hz_parties ass,
251 eni_chg_mgmt_type_v typ,
252 eni_chg_mgmt_status_v stat
253 '||l_priority_from||'
254 '||l_reason_from||'
255 WHERE
256 ass.party_id(+) = eco.assignee_id
257 and eco.change_order_type_id = typ.id
258 and eco.status_type = stat.id
259 '|| l_final_where||'
260 '|| l_reason_where ||'
261 '|| l_type_where ||'
262 '|| l_priority_where ||'
263 '|| l_status_where ||'
264 '|| l_item_where ||'
265 '|| l_org_where||'
266 ORDER BY
267 '||l_order_by;
268
269 x_custom_output.extend;
270 l_custom_rec.attribute_name := ':STATUS_ID';
271 l_custom_rec.attribute_value := replace(l_status,'''');
272 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
273 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
274 x_custom_output(1) := l_custom_rec;
275
276 x_custom_output.extend;
277 l_custom_rec.attribute_name := ':PRIORITY_ID';
278 l_custom_rec.attribute_value := l_priority;
279 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
280 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
281 x_custom_output(2) := l_custom_rec;
282
283 x_custom_output.extend;
284 l_custom_rec.attribute_name := ':TYPE_ID';
285 l_custom_rec.attribute_value := REPLACE(l_type,'''');
286 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
287 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
288 x_custom_output(3) := l_custom_rec;
289
290 x_custom_output.extend;
291 l_custom_rec.attribute_name := ':REASON_ID';
292 l_custom_rec.attribute_value := l_reason;
293 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
294 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
295 x_custom_output(4) := l_custom_rec;
296
297 x_custom_output.extend;
298 l_custom_rec.attribute_name := ':ORGANIZATION_ID';
299 l_custom_rec.attribute_value := REPLACE(l_org,'''');
300 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
301 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
302 x_custom_output(5) := l_custom_rec;
303
304 x_custom_output.extend;
305 l_custom_rec.attribute_name := ':ITEM_ID';
306 l_custom_rec.attribute_value := l_item;
307 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
308 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
309 x_custom_output(6) := l_custom_rec;
310
311 x_custom_output.extend;
312 l_custom_rec.attribute_name := ':NVL_DATE';
313 l_custom_rec.attribute_value := '31/12/3000';
314 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
315 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
316 x_custom_output(7) := l_custom_rec;
317
318 x_custom_output.extend;
319 l_custom_rec.attribute_name := ':OA_URL';
320 l_custom_rec.attribute_value := l_oa_url;
321 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
322 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
323 x_custom_output(8) := l_custom_rec;
324
325 x_custom_output.extend;
326 l_custom_rec.attribute_name := ':START_DATE';
327 l_custom_rec.attribute_value := to_char(l_start_date,'DD/MM/YYYY');
328 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
329 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
330 x_custom_output(9) := l_custom_rec;
331
332 x_custom_output.extend;
333 l_custom_rec.attribute_name := ':END_DATE';
334 l_custom_rec.attribute_value := to_char(l_end_date,'DD/MM/YYYY');
335 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
336 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
337 x_custom_output(10) := l_custom_rec;
338 END GET_SQL;
339
340 PROCEDURE GET_COL_PARAMETERS (
341 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
342 p_report OUT NOCOPY VARCHAR2,
343 p_start_date OUT NOCOPY DATE,
344 p_end_date OUT NOCOPY DATE
345 ) IS
346
347 BEGIN
348
349 IF (p_page_parameter_tbl.count > 0) THEN
350 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
351 IF p_page_parameter_tbl(i).parameter_name = 'start_date' THEN
352 p_start_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD-MM-YYYY');
353 ELSIF p_page_parameter_tbl(i).parameter_name = 'end_date' THEN
354 p_end_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD-MM-YYYY');
355 ELSIF p_page_parameter_tbl(i).parameter_name = 'REPORTED' THEN
356 p_report := p_page_parameter_tbl(i).parameter_value;
357 END IF;
358 END LOOP;
359 END IF;
360 END GET_COL_PARAMETERS;
361
362 END ENI_DBI_COL_PKG;