[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_COA_PKG
Source
1 PACKAGE BODY ENI_DBI_COA_PKG AS
2 /*$Header: ENICOAPB.pls 115.23 2004/06/24 22:26:37 adhachol 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_type_where VARCHAR2(1000);
41 l_from_clause VARCHAR2(1000);
42 l_where_clause VARCHAR2(1000);
43 l_group_by_clause VARCHAR2(1000);
44 l_priority_from VARCHAR2(1000);
45 l_type_from VARCHAR2(1000);
46 l_reason_from VARCHAR2(1000);
47 l_item_from VARCHAR2(1000);
48 l_common_part VARCHAR2(32000);
49 l_inner_part VARCHAR2(32000);
50 l_lookup_from VARCHAR2(4000);
51 l_lookup_outer_where VARCHAR2(4000);
52 l_status_from VARCHAR2(4000);
53 l_status_where VARCHAR2(4000);
54 l_open_url VARCHAR2(4000);
55 l_description VARCHAR2(4000);
56 l_inner_group_by VARCHAR2(4000);
57
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 IF (l_priority IS NULL OR l_priority = '' OR l_priority = 'All')
91 THEN
92 l_priority_from:='';
93 l_priority_where:= '';
94 ELSIF (l_priority = '-1')
95 THEN
96 l_priority_where := ' and edcd.priority_code = :PRIORITY ';
97 ELSE
98 l_priority_where := ' and edcd.priority_code = :PRIORITY ';
99 END IF;
100
101 IF (l_type IS NULL OR l_type = '' OR l_type = 'All')
102 THEN
103 l_type_from:='';
104 l_type_where := '';
105
106 ELSE
107 l_type_where := ' and edcd.change_order_type_id = :TYPE ';
108 END IF;
109
110
111 IF (l_reason IS NULL OR l_reason = '' OR l_reason = 'All')
112 THEN
113 l_reason_from:='';
114 l_reason_where := '';
115 ELSIF (l_reason = '-1')
116 THEN
117 l_reason_where := ' and edcd.reason_code = :REASON ';
118 ELSE
119 l_reason_where := ' and edcd.reason_code = :REASON ';
120 END IF;
121
122 IF (l_status IS NULL OR l_status = '' OR l_status = 'All')
123 THEN
124 l_status_from:='';
125 l_status_where := '';
126
127 ELSE
128 l_status_where := ' and edcd.status_type = :STATUS ';
129 END IF;
130
131 IF (l_org IS NULL OR l_org = '' OR l_org = 'All')
132 THEN
133 l_org_where := '';
134
135 ELSE
136 l_org_where := ' AND edcd.organization_id = :ORG ';
137 END IF;
138
139 l_open_url:= '''pFunctionName=ENI_DBI_COL_OPEN_R&pCustomView=ENI_DBI_COL_CV1&REPORTED=OPEN'||
140 '&VIEW_BY=VIEW_BY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
141
142 IF (l_item IS NULL OR l_item = '' OR l_item = 'All')
143 THEN
144 l_item_where := '';
145 IF(l_view_by NOT like '%ITEM+%')
146 THEN
147 X_CUSTOM_SQL:='
148 SELECT NULL AS VIEWBY,
149 NULL AS ENI_MEASURE60,
150 NULL AS ENI_MEASURE1,
151 NULL AS ENI_MEASURE9,
152 NULL AS ENI_MEASURE3,
153 NULL AS ENI_MEASURE11,
154 NULL AS ENI_MEASURE31,
155 NULL AS ENI_MEASURE32,
156 NULL AS ENI_MEASURE33,
157 NULL AS ENI_MEASURE34,
158 NULL AS ENI_MEASURE20,
159 NULL AS ENI_MEASURE21,
160 NULL AS ENI_MEASURE22,
161 NULL AS ENI_MEASURE23,
162 NULL AS ENI_MEASURE25,
163 NULL AS ENI_MEASURE26,
164 NULL AS ENI_MEASURE27,
165 NULL AS ENI_MEASURE28,
166 NULL AS ENI_MEASURE35,
167 NULL AS ENI_MEASURE36,
168 NULL AS ENI_MEASURE37,
169 NULL AS ENI_MEASURE38,
170 NULL AS ENI_MEASURE40
171 FROM DUAL';
172 RETURN;
173 END IF;
174 ELSE
175 l_item_where := ' AND edcd.item_id = :ITEM ';
176 END IF;
177
178
179 /*
180 Bug #3114681 : Previously the condition in the CASE was without the equal sign and not taking the
181 current_as_of_date into account .
182
183 */
184
185 l_inner_part:='
186
187
188 SUM(CASE WHEN ( edcd.creation_date
189 <= &BIS_CURRENT_ASOF_DATE ) AND
190 (nvl(edcd.IMPLEMENTATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE ) AND
191 ( nvl(edcd.CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE )
192 THEN edcd.cnt
193 ELSE 0 END) as ENI_MEASURE1,
194
195 SUM(CASE WHEN ( edcd.creation_date
196 <= &BIS_PREVIOUS_ASOF_DATE ) AND
197 ( nvl(IMPLEMENTATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE ) AND
198 ( nvl(CANCELLATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1 ) > &BIS_PREVIOUS_ASOF_DATE )
199 THEN edcd.cnt
200 ELSE null END) as ENI_MEASURE9,
201
202 SUM(CASE WHEN ( edcd.creation_date
203 <= &BIS_CURRENT_ASOF_DATE ) AND
204 ( nvl(IMPLEMENTATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE ) AND
205 ( nvl(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE )
206 THEN ((&BIS_CURRENT_ASOF_DATE - edcd.CREATION_DATE)* edcd.cnt)
207 ELSE 0 END)/
208 (case when (SUM(CASE WHEN ( edcd.creation_date
209 <= &BIS_CURRENT_ASOF_DATE ) AND
210 ( nvl(IMPLEMENTATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE ) AND
211 ( nvl(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE )
212 THEN edcd.cnt
213 ELSE 0 END) = 0) then null else (SUM(CASE WHEN ( edcd.creation_date
214 <= &BIS_CURRENT_ASOF_DATE ) AND
215 ( nvl(IMPLEMENTATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE ) AND
216 ( nvl(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE )
217 THEN edcd.cnt
218 ELSE 0 END)) END)
219 as ENI_MEASURE3,
220
221 SUM(CASE WHEN ( edcd.creation_date
222 <= &BIS_PREVIOUS_ASOF_DATE ) AND
223 ( nvl(IMPLEMENTATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE ) AND
224 ( nvl(CANCELLATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE )
225 THEN ((&BIS_PREVIOUS_ASOF_DATE - edcd.CREATION_DATE)* edcd.cnt)
226 ELSE 0 END)/
227 (case when (SUM(CASE WHEN ( edcd.creation_date
228 <= &BIS_PREVIOUS_ASOF_DATE ) AND
229 ( nvl(IMPLEMENTATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE ) AND
230 ( nvl(CANCELLATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE )
231 THEN edcd.cnt
232 ELSE 0 END) = 0) then null else (SUM(CASE WHEN ( edcd.creation_date
233 <= &BIS_PREVIOUS_ASOF_DATE ) AND
234 ( nvl(IMPLEMENTATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE ) AND
235 ( nvl(CANCELLATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE )
236 THEN edcd.cnt
237 ELSE 0 END)) END )
238 as ENI_MEASURE11 ,
239
240 SUM(CASE WHEN ( edcd.creation_date
241 <= &BIS_CURRENT_ASOF_DATE ) AND
242 ((&BIS_CURRENT_ASOF_DATE -edcd.creation_date) between 0
243 AND 1 ) AND
244 ( nvl(IMPLEMENTATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE ) AND
245 ( nvl(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE )
246 THEN edcd.cnt
247 ELSE 0 END ) as ENI_MEASURE31 ,
248
249 SUM(CASE WHEN ( edcd.creation_date
250 <= &BIS_CURRENT_ASOF_DATE ) and
251 ((&BIS_CURRENT_ASOF_DATE -edcd.creation_date) between 2
252 AND 5 ) AND
253 ( nvl(IMPLEMENTATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE ) AND
254 ( nvl(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE )
255 THEN edcd.cnt
256 ELSE 0 END ) as ENI_MEASURE32 ,
257
258 SUM(CASE WHEN (edcd.creation_date
259 <= &BIS_CURRENT_ASOF_DATE ) and
260 ((&BIS_CURRENT_ASOF_DATE -edcd.creation_date) between 6
261 AND 10 ) AND
262 ( nvl(IMPLEMENTATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE ) AND
263 ( nvl(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE )
264 THEN edcd.cnt
265 ELSE 0 END ) as ENI_MEASURE33 ,
266
267 SUM(CASE WHEN (edcd.creation_date
268 <= &BIS_CURRENT_ASOF_DATE ) and
269 ((&BIS_CURRENT_ASOF_DATE -edcd.creation_date) > 10
270 ) AND
271 ( nvl(IMPLEMENTATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE ) AND
272 ( nvl(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE )
273 THEN edcd.cnt
274 ELSE 0 END ) as ENI_MEASURE34 ,
275
276 SUM(CASE WHEN ( edcd.creation_date
277 <= &BIS_CURRENT_ASOF_DATE ) AND
278 ( nvl(IMPLEMENTATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE ) AND
279 ( nvl(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE )
280 THEN ((&BIS_CURRENT_ASOF_DATE - edcd.CREATION_DATE)* edcd.cnt)
281 ELSE 0 END) AS INNER11,
282
283 SUM(CASE WHEN ( edcd.creation_date
284 <= &BIS_CURRENT_ASOF_DATE ) AND
285 ( nvl(IMPLEMENTATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE ) AND
286 ( nvl(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE )
287 THEN edcd.cnt
288 ELSE 0 END) AS INNER12,
289
290
291 SUM(CASE WHEN ( edcd.creation_date
292 <= &BIS_PREVIOUS_ASOF_DATE ) AND
293 ( nvl(IMPLEMENTATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE ) AND
294 ( nvl(CANCELLATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE )
295 THEN ((&BIS_PREVIOUS_ASOF_DATE - edcd.CREATION_DATE)* edcd.cnt)
296 ELSE 0 END) AS INNER21,
297
298
299 SUM(CASE WHEN ( edcd.creation_date
300 <= &BIS_PREVIOUS_ASOF_DATE ) AND
301 ( nvl(IMPLEMENTATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE ) AND
302 ( nvl(CANCELLATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE )
303 THEN edcd.cnt
304 ELSE 0 END) AS INNER22
305
306 FROM
307 ';
308 -- Bug 3722506 : INNER21 was coming as 0 and hence the calculation of measure23 was giving a
309 -- Division by Zero error .
310
311 l_common_part:='
312
313 ENI_MEASURE1,
314 ENI_MEASURE9,
315 ENI_MEASURE3,
316 ENI_MEASURE11,
317 ENI_MEASURE31,
318 ENI_MEASURE32,
319 ENI_MEASURE33,
320 ENI_MEASURE34,
321 SUM(ENI_MEASURE1) OVER() AS ENI_MEASURE20,
322 ((SUM(ENI_MEASURE1) OVER() - SUM(ENI_MEASURE9) OVER()))
323 /
324 (SUM(ENI_MEASURE9) OVER() )* 100 AS ENI_MEASURE21,
325
326 (SUM(INNER11) OVER())/ DECODE ( SUM(INNER12) OVER(),0,NULL,SUM(INNER12) OVER()) AS ENI_MEASURE22,
327
328 ((((SUM(INNER11) OVER())/ DECODE ( SUM(INNER12) OVER(),0,NULL,SUM(INNER12) OVER()))
329 -
330 ((SUM(INNER21) OVER())/ DECODE ( SUM(INNER22) OVER(),0,NULL,SUM(INNER22) OVER())))
331 /
332 decode (((SUM(INNER21) OVER())/ DECODE ( SUM(INNER22) OVER(),0,NULL,SUM(INNER22) OVER())),0,null,
333 ((SUM(INNER21) OVER())/ DECODE ( SUM(INNER22) OVER(),0,NULL,SUM(INNER22) OVER())))
334 )
335 * 100
336 AS ENI_MEASURE23 ,
337
338 SUM(ENI_MEASURE31) OVER() AS ENI_MEASURE25,
339 SUM(ENI_MEASURE32) OVER() AS ENI_MEASURE26,
340 SUM(ENI_MEASURE33) OVER() AS ENI_MEASURE27,
341 SUM(ENI_MEASURE34) OVER() AS ENI_MEASURE28,
342 35 AS ENI_MEASURE35,
343 36 AS ENI_MEASURE36,
344 37 AS ENI_MEASURE37,
345 38 AS ENI_MEASURE38,
346 DECODE(ENI_MEASURE1,0,NULL,'||l_open_url || ') as ENI_MEASURE40';
347
348
349 l_lookup_from:=' , eni_item_org_v eiv ';
350 l_lookup_outer_where:=' and edcd.item_id = eiv.inventory_item_id
351 and eiv.organization_id = edcd.organization_id ';
352 l_description:=' eiv.description as ENI_MEASURE60 ';
353 l_inner_group_by:=', eiv.description ';
354
355 IF(l_view_by like '%ITEM+%')
356 THEN
357 l_lookup_from:=' , eni_item_org_v eiv ';
358 l_lookup_outer_where:=' and edcd.item_id = eiv.inventory_item_id
359 and eiv.organization_id = edcd.organization_id ';
360 l_description:=' eiv.description as ENI_MEASURE60 ';
361 l_inner_group_by:=', eiv.description ';
362
363 ELSIF(l_view_by like '%TYPE%')
364 THEN
365 l_lookup_from:=' , eni_chg_mgmt_type_v eiv ';
366 l_lookup_outer_where:=' and edcd.change_order_type_id = eiv.id ';
367 l_description:=' null as ENI_MEASURE60 ';
368 l_inner_group_by:='';
369
370
371 ELSIF(l_view_by like '%PRIORITY%')
372 THEN
373 l_lookup_from:=' ,eni_chg_mgmt_priority_v eiv';
374 l_lookup_outer_where:=' and edcd.priority_code = eiv.id(+) ';
375 l_description:=' null as ENI_MEASURE60 ';
376 l_inner_group_by:='';
377
378
379 ELSIF(l_view_by like '%REASON%')
380 THEN
381 l_lookup_from:=',eni_chg_mgmt_reason_v eiv';
382 l_lookup_outer_where:=' and edcd.reason_code = eiv.id(+) ';
383 l_description:=' null as ENI_MEASURE60 ';
384 l_inner_group_by:='';
385
386
387 ELSIF(l_view_by like '%STATUS%')
388 THEN
389 l_lookup_from:=',eni_chg_mgmt_status_v eiv';
390 l_lookup_outer_where:=' and edcd.status_type = eiv.id ';
391 l_description:=' null as ENI_MEASURE60 ';
392 l_inner_group_by:='';
393
394 END IF;
395
396
397 X_CUSTOM_SQL:=
398 '
399 select value as VIEWBY,
400 ID AS VIEWBYID,
401 ENI_MEASURE60, '
402 || l_common_part ||'
403
404 FROM ( SELECT EIV.VALUE AS VALUE, eiv.id as id,
405 '|| l_description ||',
406 '|| l_inner_part ||'
407 ENI_DBI_CO_DNUM_MV edcd
408 ' || l_lookup_from || '
409 WHERE
410 1=1
411 AND
412 ((edcd.creation_date
413 <= &BIS_CURRENT_ASOF_DATE ) AND
414 ( nvl(IMPLEMENTATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE ) AND
415 ( nvl(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1) > &BIS_CURRENT_ASOF_DATE )
416 OR
417 (edcd.creation_date
418 <= &BIS_PREVIOUS_ASOF_DATE ) AND
419 ( nvl(IMPLEMENTATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE ) AND
420 ( nvl(CANCELLATION_DATE,&BIS_PREVIOUS_ASOF_DATE +1) > &BIS_PREVIOUS_ASOF_DATE))
421 ' || l_item_where ||'
422 ' || l_lookup_outer_where ||'
423 ' || l_org_where || '
424 ' || l_type_where || '
425 ' || l_priority_where || '
426 ' || l_reason_where|| '
427 ' || l_status_where || '
428
429 GROUP BY
430 eiv.id,eiv.value'||l_inner_group_by||'
431 ) t
432 WHERE
433 ENI_MEASURE1 > 0 OR ENI_MEASURE9 > 0
434 group by
435 id,value,ENI_MEASURE1, ENI_MEASURE9, ENI_MEASURE3, ENI_MEASURE11,
436 ENI_MEASURE31, ENI_MEASURE32, ENI_MEASURE33, ENI_MEASURE34,
437 ENI_MEASURE60,INNER11,INNER12,INNER21,INNER22
438 order by
439 ' || l_order_by ;
440
441
442 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
443 x_custom_output := bis_query_attributes_tbl();
444
445
446
447 x_custom_output.extend;
448 l_custom_rec.attribute_name := ':ITEM';
449 l_custom_rec.attribute_value := l_item;
450 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
451 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
452 x_custom_output(1) := l_custom_rec;
453
454
455 x_custom_output.extend;
456 l_custom_rec.attribute_name := ':ORG';
457 l_custom_rec.attribute_value := replace(l_org,'''');
458 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
459 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
460 x_custom_output(2) := l_custom_rec;
461
462 x_custom_output.extend;
463 l_custom_rec.attribute_name := ':REASON';
464 l_custom_rec.attribute_value := l_reason;
465 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
466 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
467 x_custom_output(3) := l_custom_rec;
468
469 x_custom_output.extend;
470 l_custom_rec.attribute_name := ':PRIORITY';
471 l_custom_rec.attribute_value := l_priority;
472 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
473 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
474 x_custom_output(4) := l_custom_rec;
475
476 x_custom_output.extend;
477 l_custom_rec.attribute_name := ':STATUS';
478 l_custom_rec.attribute_value := replace(l_status,'''');
479 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
480 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
481 x_custom_output(5) := l_custom_rec;
482
483 x_custom_output.extend;
484 l_custom_rec.attribute_name := ':TYPE';
485 l_custom_rec.attribute_value := replace(l_type,'''');
486 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
487 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
488 x_custom_output(6) := l_custom_rec;
489
490
491 --l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
492 --l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
493
494
495
496 END GET_SQL;
497 END ENI_DBI_COA_PKG;