[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_PME_KPI_PKG
Source
1 PACKAGE BODY eni_dbi_pme_kpi_pkg AS
2 /*$Header: ENIPMEPB.pls 120.4 2006/08/08 12:11:14 lparihar noship $*/
3 PROCEDURE get_sql
4 (
5 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 x_custom_sql OUT NOCOPY VARCHAR2,
7 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
8 )
9 IS
10 l_period_type VARCHAR2(100);
11 l_period_bitand NUMBER;
12 l_view_by VARCHAR2(100);
13 l_as_of_date DATE;
14 l_prev_as_of_date DATE;
15 l_report_start DATE;
16 l_cur_period NUMBER;
17 l_days_into_period NUMBER;
18 l_comp_type VARCHAR2(100);
19 l_category VARCHAR2(100);
20 l_item VARCHAR2(100);
21 l_org VARCHAR2(100);
22 l_id_column VARCHAR2(100);
23 l_order_by VARCHAR2(100);
24 l_drill VARCHAR2(100);
25 l_status VARCHAR2(100);
26 l_priority VARCHAR2(100);
27 l_reason VARCHAR2(100);
28 l_lifecycle_phase VARCHAR2(100);
29 l_currency VARCHAR2(100);
30 l_currency_rate VARCHAR2(30);
31 l_bom_type VARCHAR2(100);
32 l_type VARCHAR2(100);
33 l_manager VARCHAR2(100);
34 l_lob VARCHAR2(100);
35 l_from_clause VARCHAR2(1000);
36 l_where_clause VARCHAR2(1000);
37 l_group_by_clause VARCHAR2(1000);
38 l_err_msg VARCHAR2(32000);
39 -- The record structure for bind variable values
40 l_custom_rec BIS_QUERY_ATTRIBUTES;
41 l_view_by_col VARCHAR2(100);
42 l_group_by_col VARCHAR2(100);
43 l_lookup VARCHAR2(100);
44 l_summary VARCHAR2(100);
45 l_item_pco NUMBER;
46 l_org_pco NUMBER;
47 l_org_exists NUMBER;
48
49 BEGIN
50
51 eni_dbi_util_pkg.get_parameters
52 (
53 p_page_parameter_tbl,
54 l_period_type,
55 l_period_bitand,
56 l_view_by,
57 l_as_of_date,
58 l_prev_as_of_date,
59 l_report_start,
60 l_cur_period,
61 l_days_into_period,
62 l_comp_type,
63 l_category,
64 l_item,
65 l_org,
66 l_id_column,
67 l_order_by,
68 l_drill,
69 l_status,
70 l_priority,
71 l_reason,
72 l_lifecycle_phase,
73 l_currency,
74 l_bom_type,
75 l_type,
76 l_manager,
77 l_lob
78 );
79
80 eni_dbi_util_pkg.get_time_clauses
81 (
82 'I',
83 'fgbm',
84 l_period_type,
85 l_period_bitand,
86 l_as_of_date,
87 l_prev_as_of_date,
88 l_report_start,
89 l_cur_period,
90 l_days_into_period,
91 l_comp_type,
92 l_id_column,
93 l_from_clause,
94 l_where_clause,
95 l_group_by_clause ,
96 'ROLLING'
97 );
98
99 -- Set currency rate based on currency chosen by user
100
101 l_currency_rate :=
102 CASE l_currency
103 WHEN ENI_DBI_UTIL_PKG.get_curr_sec
104 THEN 'secondary_currency_rate' -- secondary global currency
105 ELSE 'primary_currency_rate' -- primary global currency (default)
106 END;
107
108 BEGIN
109 select
110 NVL(common_assembly_item_id,assembly_item_id),
111 NVL(common_organization_id ,organization_id)
112 INTO
113 l_item_pco,l_org_pco
114 from
115 bom_bill_of_materials
116 where
117 assembly_item_id = l_item and
118 organization_id = l_org and
119 alternate_bom_designator IS NULL;
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 l_item_pco := NULL; l_org_pco := NULL;
123 END;
124
125 IF (l_item_pco IS NULL) THEN
126 l_item_pco := -9999;
127 l_org_pco := -9999;
128 ELSE -- Display the data only when the organization of the item exists
129 -- in the org_temp table. Added for bug # 3669751
130 SELECT count(*)
131 INTO l_org_exists
132 FROM eni_dbi_part_count_org_temp
133 WHERE organization_id = l_org;
134 IF (l_org_exists = 0) THEN
135 l_item_pco := -9999;
136 l_org_pco := -9999;
137 END IF;
138 END IF;
139
140 IF (l_item ='' OR l_item IS NULL OR l_item = 'ALL') THEN
141
142 x_custom_sql := '
143 SELECT
144 null AS ENI_MEASURE1,
145 null AS ENI_MEASURE2,
146 null AS ENI_MEASURE3,
147 null AS ENI_MEASURE4,
148 null AS ENI_MEASURE5,
149 null AS ENI_MEASURE6,
150 null AS ENI_MEASURE7,
151 null AS ENI_MEASURE8,
152 null AS ENI_MEASURE9,
153 null AS ENI_MEASURE10,
154 null AS ENI_MEASURE11,
155 null AS ENI_MEASURE12,
156 null AS ENI_MEASURE13,
157 null AS ENI_MEASURE14,
158 null as ENI_MEASURE22,
159 null as ENI_MEASURE23,
160 null as ENI_MEASURE24,
161 null as ENI_MEASURE25,
162 null as ENI_MEASURE26,
163 null as ENI_MEASURE27,
164 null as ENI_MEASURE28,
165 null as ENI_MEASURE29,
166 null as ENI_MEASURE30,
167 null as ENI_MEASURE31,
168 null as ENI_MEASURE32,
169 null as ENI_MEASURE33,
170 null as ENI_MEASURE34,
171 null as ENI_MEASURE35
172 FROM dual';
173 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
174 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
175 RETURN;
176
177 END IF;
178
179 x_custom_sql :=
180 ' SELECT
181 SUM(ENI_MEASURE1) AS ENI_MEASURE1,
182 SUM(ENI_MEASURE2) AS ENI_MEASURE2,
183 SUM(ENI_MEASURE3) AS ENI_MEASURE3,
184 SUM(ENI_MEASURE4) AS ENI_MEASURE4,
185 SUM(ENI_MEASURE5) AS ENI_MEASURE5,
186 SUM(ENI_MEASURE6) AS ENI_MEASURE6,
187 SUM(ENI_MEASURE7) AS ENI_MEASURE7,
188 SUM(ENI_MEASURE8) AS ENI_MEASURE8,
189 SUM(ENI_MEASURE9) AS ENI_MEASURE9,
190 SUM(ENI_MEASURE10) AS ENI_MEASURE10,
191 SUM(ENI_MEASURE11) AS ENI_MEASURE11,
192 SUM(ENI_MEASURE12) AS ENI_MEASURE12,
193 SUM(ENI_MEASURE13) AS ENI_MEASURE13,
194 SUM(ENI_MEASURE14) AS ENI_MEASURE14,
195 SUM(ENI_MEASURE1) AS ENI_MEASURE22,
196 SUM(ENI_MEASURE2) AS ENI_MEASURE23,
197 SUM(ENI_MEASURE3) AS ENI_MEASURE24,
198 SUM(ENI_MEASURE4) AS ENI_MEASURE25,
199 SUM(ENI_MEASURE5) AS ENI_MEASURE26,
200 SUM(ENI_MEASURE6) AS ENI_MEASURE27,
201 SUM(ENI_MEASURE7) AS ENI_MEASURE28,
202 SUM(ENI_MEASURE8) AS ENI_MEASURE29,
203 SUM(ENI_MEASURE9) AS ENI_MEASURE30,
204 SUM(ENI_MEASURE10) AS ENI_MEASURE31,
205 SUM(ENI_MEASURE11) AS ENI_MEASURE32,
206 SUM(ENI_MEASURE12) AS ENI_MEASURE33,
207 SUM(ENI_MEASURE13) AS ENI_MEASURE34,
208 SUM(ENI_MEASURE14) AS ENI_MEASURE35
209 FROM
210 (
211 SELECT -- unit cost KPI
212 SUM
213 (
214 CASE WHEN edic.effective_date = c.effective_date
215 THEN
216 item_cost * ' || l_currency_rate || '
217 ELSE
218 NULL
219 END
220 ) AS ENI_MEASURE1, -- unit cost current
221 SUM
222 (
223 CASE WHEN edic.effective_date = p.effective_date
224 THEN
225 item_cost * ' || l_currency_rate || '
226 ELSE
227 NULL
228 END
229 ) AS ENI_MEASURE2, -- unit cost prior
230 SUM(NULL) AS ENI_MEASURE3, -- part count current
231 SUM(NULL) AS ENI_MEASURE4, -- part count prior
232 SUM(NULL) AS ENI_MEASURE5, -- mfg steps current
233 SUM(NULL) AS ENI_MEASURE6, -- mfg steps prior
234 SUM(NULL) AS ENI_MEASURE7, -- new change orders current
235 SUM(NULL) AS ENI_MEASURE8, -- new change orders prior
236 SUM(NULL) AS ENI_MEASURE9, -- open change orders current
237 SUM(NULL) AS ENI_MEASURE10, -- open change orders prior
238 SUM(NULL) AS ENI_MEASURE11, -- change order cycle time current
239 SUM(NULL) AS ENI_MEASURE12, -- change order cycle time prior
240 SUM(NULL) AS ENI_MEASURE13, -- max BOM Levels current
241 SUM(NULL) AS ENI_MEASURE14 -- max BOM Levels prior
242 FROM
243 eni_dbi_item_cost_f edic,
244 (
245 SELECT
246 max(effective_date) AS effective_date
247 FROM
248 eni_dbi_item_cost_f
249 WHERE
250 --inventory_item_id = ' || l_item || ' Bug 5083900
251 inventory_item_id = :ITEM
252 --AND organization_id = ' || l_org || ' Bug 5083900
253 AND organization_id = :ORG
254 AND effective_date <= ' || '&' || 'BIS_CURRENT_ASOF_DATE
255 ) c,
256 (
257 SELECT
258 max(effective_date) AS effective_date
259 FROM
260 eni_dbi_item_cost_f
261 WHERE
262 --inventory_item_id = ' || l_item || ' Bug 5083900
263 inventory_item_id = :ITEM
264 --AND organization_id = ' || l_org || ' Bug 5083900
265 AND organization_id = :ORG
266 AND effective_date <= ' || '&' || 'BIS_PREVIOUS_ASOF_DATE
267 ) p
268 WHERE
269 --edic.inventory_item_id = ' || l_item || ' Bug 5083900
270 edic.inventory_item_id = :ITEM
271 --AND edic.organization_id = ' || l_org || ' Bug 5083900
272 AND edic.organization_id = :ORG
273 AND
274 (
275 edic.effective_date = c.effective_date
276 OR edic.effective_date = p.effective_date
277 )
278 UNION ALL
279 SELECT -- part count
280 SUM(NULL) AS ENI_MEASURE1, -- unit cost current
281 SUM(NULL) AS ENI_MEASURE2, -- unit cost prior
282 SUM
283 (
284 CASE WHEN ' || '&' || 'BIS_CURRENT_ASOF_DATE
285 BETWEEN trunc(effectivity_date)
286 AND
287 nvl(trunc(disable_date),' || '&' || 'BIS_CURRENT_ASOF_DATE + 1)
288 THEN
289 part_count
290 ELSE
291 NULL
292 END
293 ) AS ENI_MEASURE3, -- part count current
294 SUM
295 (
296 CASE WHEN ' || '&' || 'BIS_PREVIOUS_ASOF_DATE
297 BETWEEN trunc(effectivity_date)
298 AND
299 nvl(trunc(disable_date),' || '&' || 'BIS_PREVIOUS_ASOF_DATE + 1)
300 THEN
301 part_count
302 ELSE
303 NULL
304 END
305 ) AS ENI_MEASURE4, -- part count prior
306 SUM(NULL) AS ENI_MEASURE5, -- mfg steps current
307 SUM(NULL) AS ENI_MEASURE6, -- mfg steps prior
308 SUM(NULL) AS ENI_MEASURE7, -- new change orders current
309 SUM(NULL) AS ENI_MEASURE8, -- new change orders prior
310 SUM(NULL) AS ENI_MEASURE9, -- open change orders current
311 SUM(NULL) AS ENI_MEASURE10, -- open change orders prior
312 SUM(NULL) AS ENI_MEASURE11, -- change order cycle time current
313 SUM(NULL) AS ENI_MEASURE12,-- change order cycle time prior
314 MAX
315 (
316 CASE WHEN ' || '&' || 'BIS_CURRENT_ASOF_DATE
317 BETWEEN trunc(effectivity_date)
318 AND
319 nvl(trunc(disable_date),' || '&' || 'BIS_CURRENT_ASOF_DATE + 1)
320 THEN
321 max_bom_level
322 ELSE
323 NULL
324 END
325 ) AS ENI_MEASURE13, -- max BOM Levels current,
326 MAX
327 (
328 CASE WHEN ' || '&' || 'BIS_PREVIOUS_ASOF_DATE
329 BETWEEN trunc(effectivity_date)
330 AND
331 nvl(trunc(disable_date),' || '&' || 'BIS_PREVIOUS_ASOF_DATE + 1)
332 THEN
333 max_bom_level
334 ELSE
335 NULL
336 END
337 ) AS ENI_MEASURE14 -- max BOM Levels prior
338 FROM
339 eni_dbi_part_count_mv edpc
340 WHERE
341 --edpc.assembly_item_id = ' || l_item_pco || ' Bug 5083900
342 edpc.assembly_item_id = :ITEMPCO
343 --AND edpc.organization_id = ' || l_org_pco || ' Bug 5083900
344 AND edpc.organization_id = :ORGPCO
345 AND
346 (
347 ' || '&' || 'BIS_CURRENT_ASOF_DATE
348 BETWEEN trunc(effectivity_date) AND nvl(trunc(disable_date),' || '&' || 'BIS_CURRENT_ASOF_DATE + 1)
349 OR
350 ' || '&' || 'BIS_PREVIOUS_ASOF_DATE
351 BETWEEN trunc(effectivity_date) AND nvl(trunc(disable_date),' || '&' || 'BIS_PREVIOUS_ASOF_DATE + 1)
352 )
353 UNION ALL
354 SELECT -- mfg steps
355 SUM(NULL) AS ENI_MEASURE1, -- unit cost current
356 SUM(NULL) AS ENI_MEASURE2, -- unit cost prior
357 SUM(NULL) AS ENI_MEASURE3, -- part count current
358 SUM(NULL) AS ENI_MEASURE4, -- part count prior
359 SUM
360 (
361 CASE WHEN ' || '&' || 'BIS_CURRENT_ASOF_DATE
362 BETWEEN trunc(effectivity_date)
363 AND
364 nvl(trunc(disable_date),' || '&' || 'BIS_CURRENT_ASOF_DATE)
365 THEN
366 mfgsteps_count
367 ELSE
368 NULL
369 END
370 ) AS ENI_MEASURE5, -- mfg steps current
371 SUM
372 (
373 CASE WHEN ' || '&' || 'BIS_PREVIOUS_ASOF_DATE
374 BETWEEN trunc(effectivity_date)
375 AND
376 nvl(trunc(disable_date),' || '&' || 'BIS_PREVIOUS_ASOF_DATE)
377 THEN
378 mfgsteps_count
379 ELSE
380 NULL
381 END
382 ) AS ENI_MEASURE6, -- mfg steps prior
383 SUM(NULL) AS ENI_MEASURE7, -- new change orders current
384 SUM(NULL) AS ENI_MEASURE8, -- new change orders prior
385 SUM(NULL) AS ENI_MEASURE9, -- open change orders current
386 SUM(NULL) AS ENI_MEASURE10, -- open change orders prior
387 SUM(NULL) AS ENI_MEASURE11, -- change order cycle time current
388 SUM(NULL) AS ENI_MEASURE12, -- change order cycle time prior
389 SUM(NULL) AS ENI_MEASURE13, -- max BOM Levels current
390 SUM(NULL) AS ENI_MEASURE14 -- max BOM Levels prior
391 FROM
392 eni_dbi_mfg_steps_join_mv
393 WHERE
394 --item_id = ' || l_item || ' Bug 5083900
395 item_id = :ITEM
396 --AND organization_id = ' || l_org || ' Bug 5083900
397 AND organization_id = :ORG
398 AND
399 (
400 ' || '&' || 'BIS_CURRENT_ASOF_DATE
401 BETWEEN trunc(effectivity_date) AND nvl(trunc(disable_date),' || '&' || 'BIS_CURRENT_ASOF_DATE)
402 OR
403 ' || '&' || 'BIS_PREVIOUS_ASOF_DATE
404 BETWEEN trunc(effectivity_date) AND nvl(trunc(disable_date),' || '&' || 'BIS_PREVIOUS_ASOF_DATE)
405 )
406 UNION ALL
407 SELECT -- new change orders
408 SUM(NULL) AS ENI_MEASURE1, -- unit cost current
409 SUM(NULL) AS ENI_MEASURE2, -- unit cost prior
410 SUM(NULL) AS ENI_MEASURE3, -- part count current
411 SUM(NULL) AS ENI_MEASURE4, -- part count prior
412 SUM(NULL) AS ENI_MEASURE5, -- mfg steps current
413 SUM(NULL) AS ENI_MEASURE6, -- mfg steps prior
414 SUM
415 (
416 CASE WHEN ' || '&' || 'BIS_CURRENT_ASOF_DATE = ftrs.report_date
417 THEN
418 new_sum
419 ELSE
420 NULL
421 END
422 ) AS ENI_MEASURE7, -- new change orders current
423 SUM
424 (
425 CASE WHEN ' || '&' || 'BIS_PREVIOUS_ASOF_DATE = ftrs.report_date
426 THEN
427 new_sum
428 ELSE
429 NULL
430 END
431 ) AS ENI_MEASURE8, -- new change orders prior
432 SUM(NULL) AS ENI_MEASURE9, -- open change orders current
433 SUM(NULL) AS ENI_MEASURE10, -- open change orders prior
434 SUM
435 (
436 CASE WHEN ' || '&' || 'BIS_CURRENT_ASOF_DATE = ftrs.report_date
437 THEN
438 cycle_time_sum
439 ELSE
440 NULL
441 END
442 )
443 /
444 SUM
445 (
446 CASE WHEN ' || '&' || 'BIS_CURRENT_ASOF_DATE = ftrs.report_date
447 THEN
448 implemented_sum
449 ELSE
450 null
451 END
452 ) AS ENI_MEASURE11, -- change order cycle time current
453 SUM
454 (
455 CASE WHEN ' || '&' || 'BIS_PREVIOUS_ASOF_DATE = ftrs.report_date
456 THEN
457 cycle_time_sum
458 ELSE
459 NULL
460 END
461 )
462 /
463 SUM
464 (
465 CASE WHEN ' || '&' || 'BIS_PREVIOUS_ASOF_DATE = ftrs.report_date
466 THEN
467 implemented_sum
468 ELSE
469 null
470 END
471 ) AS ENI_MEASURE12, -- change order cycle time prior
472 SUM(NULL) AS ENI_MEASURE13, -- max BOM Levels current
473 SUM(NULL) AS ENI_MEASURE14 -- max BOM Levels prior
474 FROM
475 eni_dbi_co_sum_mv fgbm,
476 fii_time_structures ftrs
477 WHERE
478 (
479 ftrs.report_date = ' || '&' || 'BIS_CURRENT_ASOF_DATE
480 OR ftrs.report_date = ' || '&' || 'BIS_PREVIOUS_ASOF_DATE
481 )
482 AND fgbm.time_id(+) = ftrs.time_id
483 AND fgbm.period_type_id(+) = ftrs.period_type_id
484 --AND BITAND(ftrs.record_type_id, ' || l_period_bitand || ') = ' || l_period_bitand || ' Bug 5083900
485 AND BITAND(ftrs.record_type_id, :PERIODAND) = :PERIODAND --Bug 5083652
486 --AND item_id = ''' || l_item || ''' Bug 5083900
487 AND item_id = :ITEM
488 --AND organization_id = ' || l_org || ' Bug 5083900
489 AND organization_id = :ORG
490 AND fgbm.status_type is null
491 AND fgbm.reason_code is null
492 AND fgbm.change_order_type_id is null
493 AND fgbm.priority_code is null
494 UNION ALL
495 SELECT -- open change orders
496 SUM(NULL) AS ENI_MEASURE1, -- unit cost current
497 SUM(NULL) AS ENI_MEASURE2, -- unit cost prior
498 SUM(NULL) AS ENI_MEASURE3, -- part count current
499 SUM(NULL) AS ENI_MEASURE4, -- part count prior
500 SUM(NULL) AS ENI_MEASURE5, -- mfg steps current
501 SUM(NULL) AS ENI_MEASURE6, -- mfg steps prior
502 SUM(NULL) AS ENI_MEASURE7, -- new change orders current
503 SUM(NULL) AS ENI_MEASURE8, -- new change orders prior
504 SUM
505 (
506 CASE WHEN
507 trunc(creation_date) <= ' || '&' || 'BIS_CURRENT_ASOF_DATE AND
508 (
509 (
510 trunc(implementation_date) IS NULL
511 OR trunc(implementation_date) > ' || '&' || 'BIS_CURRENT_ASOF_DATE
512 )
513 AND
514 (
515 trunc(cancellation_date) IS NULL
516 OR trunc(cancellation_date) > ' || '&' || 'BIS_CURRENT_ASOF_DATE
517 )
518 )
519 THEN
520 CNT
521 ELSE
522 NULL
523 END
524 ) AS ENI_MEASURE9, -- open change orders current
525 SUM
526 (
527 CASE WHEN
528 trunc(creation_date) <= ' || '&' || 'BIS_PREVIOUS_ASOF_DATE AND
529 (
530 (
531 trunc(implementation_date) IS NULL
532 OR trunc(implementation_date) > ' || '&' || 'BIS_PREVIOUS_ASOF_DATE
533 )
534 AND
535 (
536 trunc(cancellation_date) IS NULL
537 OR trunc(cancellation_date) > ' || '&' || 'BIS_PREVIOUS_ASOF_DATE
538 )
539 )
540 THEN
541 CNT
542 ELSE
543 NULL
544 END
545 ) AS ENI_MEASURE10, -- open change orders prior
546 SUM(NULL) AS ENI_MEASURE11, -- change order cycle time current
547 SUM(NULL) AS ENI_MEASURE12, -- change order cycle time prior
548 SUM(NULL) AS ENI_MEASURE13, -- max BOM Levels current
549 SUM(NULL) AS ENI_MEASURE14 -- max BOM Levels prior
550 FROM
551 eni_dbi_co_dnum_mv
552 WHERE
553 --item_id = ''' || l_item || ''' Bug 5083900
554 item_id = :ITEM
555 --AND organization_id = ' || l_org || ' Bug 5083900
556 AND organization_id = :ORG
557 AND
558 (
559 (
560 (
561 trunc(creation_date) <= ' || '&' || 'BIS_CURRENT_ASOF_DATE
562 AND
563 (
564 (
565 trunc(implementation_date) IS NULL
566 OR trunc(implementation_date) > ' || '&' || 'BIS_CURRENT_ASOF_DATE
567 )
568 AND
569 (
570 trunc(cancellation_date) IS NULL
571 OR trunc(cancellation_date) > ' || '&' || 'BIS_CURRENT_ASOF_DATE
572 )
573 )
574 )
575 )
576 OR
577 (
578 trunc(creation_date) <= ' || '&' || 'BIS_PREVIOUS_ASOF_DATE
579 AND
580 (
581 (
582 trunc(implementation_date) IS NULL
583 OR trunc(implementation_date) > ' || '&' || 'BIS_PREVIOUS_ASOF_DATE
584 )
585 AND
586 (
587 trunc(cancellation_date) IS NULL
588 OR trunc(cancellation_date) > ' || '&' || 'BIS_PREVIOUS_ASOF_DATE
589 )
590 )
591 )
592 )
593 )
594
595 ';
596
597 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
598 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
599
600 x_custom_output.extend;
601 l_custom_rec.attribute_name := ':ITEM';
602 l_custom_rec.attribute_value := replace(l_item,'''');
603 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
604 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
605 x_custom_output(1) := l_custom_rec;
606
607 x_custom_output.extend;
608 l_custom_rec.attribute_name := ':ORG';
609 l_custom_rec.attribute_value :=replace(l_org,'''');
610 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
611 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
612 x_custom_output(2) := l_custom_rec;
613
614 x_custom_output.extend;
615 l_custom_rec.attribute_name := ':ITEMPCO';
616 l_custom_rec.attribute_value := replace(l_item_pco,'''');
617 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
618 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
619 x_custom_output(3) := l_custom_rec;
620
621 x_custom_output.extend;
622 l_custom_rec.attribute_name := ':ORGPCO';
623 l_custom_rec.attribute_value :=replace(l_org_pco,'''');
624 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
625 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
626 x_custom_output(4) := l_custom_rec;
627
628 x_custom_output.extend;
629 l_custom_rec.attribute_name := ':PERIODAND';--Bug 5083652
630 l_custom_rec.attribute_value := replace(l_period_bitand,'''');
631 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
632 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
633 x_custom_output(5) := l_custom_rec;
634
635 --Bug 5083652 -- Start Code
636
637 x_custom_output.extend;
638 l_custom_rec.attribute_name := ':PERIODTYPE';
639 l_custom_rec.attribute_value := REPLACE(l_period_type,'''');
640 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
641 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
642 x_custom_output(6) := l_custom_rec;
643
644 x_custom_output.extend;
645 l_custom_rec.attribute_name := ':COMPARETYPE';
646 l_custom_rec.attribute_value := REPLACE(l_comp_type,'''');
647 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
648 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
649 x_custom_output(7) := l_custom_rec;
650
651
652 x_custom_output.extend;
653 l_custom_rec.attribute_name := ':CUR_PERIOD_ID';
654 l_custom_rec.attribute_value := REPLACE(l_cur_period,'''');
655 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
656 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
657 x_custom_output(8) := l_custom_rec;
658
659 --Bug 5083652 -- End Code
660
661 END get_sql;
662 END eni_dbi_pme_kpi_pkg;