[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AI_KPI_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AI_KPI_PRTLT_PKG AS
2 /*$Header: bixikpip.plb 115.10 2004/03/08 04:11:23 suray noship $ */
3
4 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5 p_sql_text OUT NOCOPY VARCHAR2,
6 p_custom_output OUT NOCOPY bis_query_attributes_TBL
7 )
8 AS
9 l_sqltext VARCHAR2(32000) ;
10 l_as_of_date DATE;
11 l_period_type varchar2(2000);
12 l_comp_type varchar2(2000);
13 l_record_type_id NUMBER;
14 l_sql_errm varchar2(32000);
15 l_goal NUMBER;
16 l_custom_rec BIS_QUERY_ATTRIBUTES;
17 l_period_type_id NUMBER := 1;
18 l_period_from DATE;
19 l_period_to DATE;
20 l_call_center VARCHAR2(3000);
21 l_classification VARCHAR2(3000);
22 l_dnis VARCHAR2(3000);
23 l_call_where_clause VARCHAR2(3000);
24 l_session_where_clause VARCHAR2(3000);
25 l_view_by VARCHAR2(3000);
26
27 BEGIN
28 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
29 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
30
31 IF (FND_PROFILE.DEFINED('BIX_CALL_SLGOAL_PERCENT')) THEN
32 BEGIN
33 l_goal := TO_NUMBER(FND_PROFILE.VALUE('BIX_CALL_SLGOAL_PERCENT'));
34 EXCEPTION
35 WHEN OTHERS THEN
36 l_goal := 0;
37 END;
38 ELSE
39 l_goal := 0;
40 END IF;
41
42 IF l_goal IS NULL THEN
43 l_goal := 0;
44 END IF;
45
46 -- Get the parameters
47
48 BIX_PMV_DBI_UTL_PKG.get_ai_page_params( p_page_parameter_tbl,
49 l_as_of_date,
50 l_period_type,
51 l_record_type_id,
52 l_comp_type,
53 l_call_center,
54 l_classification,
55 l_dnis,
56 l_view_by
57 );
58
59 IF l_call_center IS NOT NULL THEN
60 l_call_where_clause := ' AND mv.server_group_id IN (:l_call_center) ';
61 l_session_where_clause := ' AND server_group_id IN (:l_call_center) ';
62 END IF;
63
64 IF l_classification IS NOT NULL THEN
65 l_call_where_clause := l_call_where_clause || ' AND mv.classification_value IN (:l_classification) ';
66 END IF;
67
68 --insert into bixtest values ('l_dnis is ' || l_dnis);
69 --commit;
70
71 IF l_dnis IS NOT NULL THEN
72 IF l_dnis = '''INBOUND'''
73 THEN
74 l_call_where_clause := l_call_where_clause ||
75 ' AND mv.dnis_name <> ''OUTBOUND'' ';
76 ELSIF l_dnis = '''OUTBOUND'''
77 THEN
78 l_call_where_clause := l_call_where_clause ||
79 ' AND mv.dnis_name = ''OUTBOUND'' ';
80 ELSE
81 l_call_where_clause := l_call_where_clause ||
82 ' AND mv.dnis_name IN (:l_dnis) ';
83 END IF;
84 END IF;
85
86
87 --insert into bixtest
88 --values ('Entere and about to set SQL');
89 --commit;
90
91 l_sqltext := '
92 SELECT
93 sum(PAG)*100/
94 decode(sum(nvl(PIO,0)+nvl(PIOC,0)),0,NULL,
95 sum(nvl(PIO,0)+nvl(PIOC,0))) BIX_PMV_AI_PREVSL,
96 sum(CAG)*100/
97 decode(sum(nvl(CIO,0)+nvl(CIOC,0)),0,NULL,
98 sum(nvl(CIO,0)+nvl(CIOC,0))) BIX_PMV_AI_SL,
99 ' || l_goal ||' BIX_PMV_AI_SLGOAL ,
100 ' || l_goal ||' BIX_PMV_AI_PREVSLGOAL ,
101 sum(PAT)/
102 decode(sum(nvl(PIH,0)+nvl(PIHC,0)),0,NULL,
103 sum(nvl(PIH,0)+nvl(PIHC,0))) BIX_PMV_AI_PREVSPANS,
104 sum(CAT)/
105 decode(sum(nvl(CIH,0)+nvl(CIHC,0)),0,NULL,
106 sum(nvl(CIH,0)+nvl(CIHC,0))) BIX_PMV_AI_SPANS,
107 sum(PAB)*100/
108 decode(sum(nvl(PIO,0)+nvl(PIOC,0)),0,NULL,
109 sum(nvl(PIO,0)+nvl(PIOC,0))) BIX_PMV_AI_PREVABANRATE,
110 sum(CAB)*100/
111 decode(sum(nvl(CIO,0)+nvl(CIOC,0)),0,NULL,
112 sum(nvl(CIO,0)+nvl(CIOC,0))) BIX_PMV_AI_ABANRATE,
113 sum(PTR)*100/
114 decode(sum(PIH),0,NULL,sum(PIH)) BIX_PMV_AI_PREVTRANRATE,
115 sum(CTR)*100/
116 decode(sum(CIH),0,NULL,sum(CIH)) BIX_PMV_AI_TRANRATE,
117 sum(CIH) BIX_PMV_AI_INCALLHAND,
118 sum(PIH) BIX_PMV_AI_PREVINCALLHAND,
119 sum(CDI) BIX_PMV_AI_DIALED,
120 sum(PDI) BIX_PMV_AI_PREVDIALED,
121 sum(CWE) BIX_PMV_AI_WEBCALL,
122 sum(PWE) BIX_PMV_AI_PREVWEBCALL,
123 sum(nvl(CLO,0)-nvl(CID,0))*100/
124 decode(sum(CLO),0,NULL,sum(CLO)) BIX_PMV_AI_AVAILRATE,
125 sum(nvl(PLO,0)-nvl(PID,0))*100/
126 decode(sum(PLO),0,NULL,sum(PLO)) BIX_PMV_AI_PREVAVAILRATE,
127 sum(nvl(CLO,0)-nvl(CAV,0)-nvl(CID,0))*100/
128 decode(sum(CLO),0,NULL,sum(CLO)) BIX_PMV_AI_UTILRATE,
129 sum(nvl(PLO,0)-nvl(PAV,0)-nvl(PID,0))*100/
130 decode(sum(PLO),0,NULL,sum(PLO)) BIX_PMV_AI_PREVUTILRATE,
131 sum(PTA)/
132 decode(sum(nvl(PHA,0)+nvl(PHAC,0)),0,NULL,
133 sum(nvl(PHA,0)+nvl(PHAC,0))) BIX_PMV_AI_PREVAVGTALK,
134 sum(CTA)/
135 decode(sum(nvl(CHA,0)+nvl(CHAC,0)),0,NULL,
136 sum(nvl(CHA,0)+nvl(CHAC,0))) BIX_PMV_AI_AVGTALK,
137 sum(PWA)/
138 decode(sum(nvl(PHA,0)+nvl(PHAC,0)),0,NULL,
139 sum(nvl(PHA,0)+nvl(PHAC,0))) BIX_PMV_AI_PREVAVGWRAP,
140 sum(CWA)/
141 decode(sum(nvl(CHA,0)+nvl(CHAC,0)),0,NULL,
142 sum(nvl(CHA,0)+nvl(CHAC,0))) BIX_PMV_AI_AVGWRAP,
143 sum(CHA)*3600/
144 decode(sum(CLO),0,NULL,sum(CLO)) BIX_PMV_AI_HANDPERHR,
145 sum(PHA)*3600/
146 decode(sum(PLO),0,NULL,sum(PLO)) BIX_PMV_AI_PREVHANDPERHR,
147 sum(CCU) BIX_PMV_AI_CUST,
148 sum(PCU) BIX_PMV_AI_PREVCUST,
149 sum(CSR) BIX_PMV_AI_SRCR,
150 sum(PSR) BIX_PMV_AI_PREVSRCR,
151 sum(CLE) BIX_PMV_AI_LECR,
152 sum(PLE) BIX_PMV_AI_PREVLECR,
153 sum(COP) BIX_PMV_AI_OPCR,
154 sum(POP) BIX_PMV_AI_PREVOPCR
155 FROM
156 (
157 SELECT
158 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
159 (
160 CASE WHEN media_item_type IN (''TELE_INB'',''TELE_DIRECT'')
161 THEN CALL_CALLS_OFFERED_TOTAL ELSE 0 END
162 )
163 ,0)) PIO,
164 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
165 (
166 CASE WHEN media_item_type IN (''TELE_INB'',''TELE_DIRECT'')
167 THEN CALL_CALLS_OFFERED_TOTAL ELSE 0 END
168 )
169 ,0)) CIO,
170 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
171 CALL_CALLS_HANDLED_TOTAL ,0)) PHA,
172 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
173 CALL_CALLS_HANDLED_TOTAL,0)) CHA,
174 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
175 (
176 CASE WHEN media_item_type IN (''TELE_INB'',''TELE_DIRECT'')
177 THEN AGENT_CALLS_ANSWERED_BY_GOAL ELSE 0 END
178 )
179 ,0)) PAG,
180 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
181 (
182 CASE WHEN media_item_type IN (''TELE_INB'',''TELE_DIRECT'')
183 THEN AGENT_CALLS_ANSWERED_BY_GOAL ELSE 0 END
184 )
185 ,0)) CAG,
186 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
187 (
188 CASE WHEN media_item_type IN (''TELE_INB'',''TELE_DIRECT'')
189 THEN CALL_TOT_QUEUE_TO_ANSWER ELSE 0 END
190 )
191 ,0)) PAT,
192 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
193 (
194 CASE WHEN media_item_type IN (''TELE_INB'',''TELE_DIRECT'')
195 THEN CALL_TOT_QUEUE_TO_ANSWER ELSE 0 END
196 )
197 ,0)) CAT,
198 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
199 (
200 CASE WHEN media_item_type IN (''TELE_INB'',''TELE_DIRECT'')
201 THEN CALL_CALLS_ABANDONED ELSE 0 END
202 )
203 ,0)) PAB,
204 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
205 (
206 CASE WHEN media_item_type IN (''TELE_INB'',''TELE_DIRECT'')
207 THEN CALL_CALLS_ABANDONED ELSE 0 END
208 )
209 ,0)) CAB,
210 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
211 (
212 CASE WHEN media_item_type IN (''TELE_INB'',''TELE_DIRECT'')
213 THEN CALL_CALLS_TRANSFERRED ELSE 0 END
214 )
215 ,0)) PTR,
216 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
217 (
218 CASE WHEN media_item_type IN (''TELE_INB'',''TELE_DIRECT'')
219 THEN CALL_CALLS_TRANSFERRED ELSE 0 END
220 )
221 ,0)) CTR,
222 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
223 (
224 CASE WHEN media_item_type IN (''TELE_INB'',''TELE_DIRECT'')
225 THEN CALL_CALLS_HANDLED_TOTAL ELSE 0 END
226 )
227 ,0)) PIH,
228 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
229 (
230 CASE WHEN media_item_type IN (''TELE_INB'',''TELE_DIRECT'')
231 THEN CALL_CALLS_HANDLED_TOTAL ELSE 0 END
232 )
233 ,0)) CIH,
234 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
235 decode(media_item_type,
236 ''TELE_MANUAL'',CALL_CALLS_HANDLED_TOTAL,
237 0)
238 ,0)) PDI,
239 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
240 decode(media_item_type,
241 ''TELE_MANUAL'',CALL_CALLS_HANDLED_TOTAL,
242 0)
243 ,0)) CDI,
244 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
245 decode(media_item_type,
246 ''TELE_WEB_CALLBACK'',CALL_CALLS_HANDLED_TOTAL,
247 0)
248 ,0)) PWE,
249 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
250 decode(media_item_type,
251 ''TELE_WEB_CALLBACK'',CALL_CALLS_HANDLED_TOTAL,
252 0)
253 ,0)) CWE,
254 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
255 CALL_TALK_TIME,0)) PTA,
256 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
257 CALL_TALK_TIME,0)) CTA,
258 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
259 AGENT_WRAP_TIME_NAC,0)) PWA,
260 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
261 AGENT_WRAP_TIME_NAC,0)) CWA,
262 count(DISTINCT(CASE
263 WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
264 AND party_id <> -1
265 THEN
266 PARTY_ID
267 END
268 )
269 ) CCU,
270 count(DISTINCT(CASE
271 WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
272 AND party_id <> -1
273 THEN
274 PARTY_ID
275 END
276 )
277 ) PCU,
278 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
279 AGENT_SR_CREATED,0)) PSR,
280 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
281 AGENT_SR_CREATED,0)) CSR,
282 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
283 AGENT_LEADS_CREATED,0)) PLE,
284 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
285 AGENT_LEADS_CREATED,0)) CLE,
286 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
287 AGENT_OPPORTUNITIES_CREATED,0)) POP,
288 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
289 AGENT_OPPORTUNITIES_CREATED,0)) COP
290 FROM bix_ai_call_details_mv mv,
291 fii_time_rpt_struct cal
292 WHERE mv.time_id = cal.time_id
293 AND mv.row_type = :l_cust_row_type
294 AND mv.period_type_id = cal.period_type_id
295 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
296 cal.record_type_id
297 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,
298 &BIS_PREVIOUS_ASOF_DATE) ';
299
300 l_sqltext := l_sqltext || l_call_where_clause ||
301 '
302 ) fresh,
303 (
304 SELECT
305 nvl(SUM( CASE when
306 period_start_date = &BIS_PREVIOUS_EFFECTIVE_START_DATE
307 then
308 decode(media_item_type,
309 ''TELE_INB'',CALL_CONT_CALLS_OFFERED_NA,
310 ''TELE_DIRECT'',CALL_CONT_CALLS_OFFERED_NA,
311 0)
312 else
313 0
314 end),0) PIOC,
315 nvl(SUM( CASE when
316 period_start_date = &BIS_CURRENT_EFFECTIVE_START_DATE
317 then
318 decode(media_item_type,
319 ''TELE_INB'',CALL_CONT_CALLS_OFFERED_NA,
320 ''TELE_DIRECT'',CALL_CONT_CALLS_OFFERED_NA,
321 0)
322 else
323 0
324 end),0) CIOC,
325 nvl(SUM( CASE when
326 period_start_date = &BIS_PREVIOUS_EFFECTIVE_START_DATE
327 then
328 decode(media_item_type,
329 ''TELE_INB'',CALL_CONT_CALLS_HANDLED_TOT_NA,
330 ''TELE_DIRECT'',CALL_CONT_CALLS_HANDLED_TOT_NA,
331 0)
332 else
333 0
334 end),0) PIHC,
335 nvl(SUM( CASE when
336 period_start_date = &BIS_CURRENT_EFFECTIVE_START_DATE
337 then
338 decode(media_item_type,
339 ''TELE_INB'',CALL_CONT_CALLS_HANDLED_TOT_NA,
340 ''TELE_DIRECT'',CALL_CONT_CALLS_HANDLED_TOT_NA,
341 0)
342 else
343 0
344 end),0) CIHC,
345 nvl(SUM(decode(period_start_date,&BIS_PREVIOUS_EFFECTIVE_START_DATE,
346 CALL_CONT_CALLS_HANDLED_TOT_NA,NULL)),0) PHAC,
347 nvl(SUM(decode(period_start_date,&BIS_CURRENT_EFFECTIVE_START_DATE,
348 CALL_CONT_CALLS_HANDLED_TOT_NA,NULL)),0) CHAC
349 FROM bix_ai_call_details_mv mv
350 WHERE time_id IN ( to_char(&BIS_CURRENT_EFFECTIVE_START_DATE,''J''),
351 to_char(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J''))
352 AND mv.period_type_id = :l_period_type_id
353 AND mv.row_type = :l_class_row_type
354 AND period_start_time = :l_period_start_time ';
355
356 l_sqltext := l_sqltext || l_call_where_clause ||
357 '
358 ) continued,
359 (
360 SELECT
361 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
362 LOGIN_TIME,0)) PLO,
363 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
364 LOGIN_TIME,0)) CLO,
365 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
366 AVAILABLE_TIME,0)) PAV,
367 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
368 AVAILABLE_TIME,0)) CAV,
369 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
370 IDLE_TIME,0)) PID,
371 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
372 IDLE_TIME,0)) CID
373 FROM bix_agent_session_f sess,
374 fii_time_rpt_struct cal
375 WHERE sess.time_id = cal.time_id
376 AND sess.period_type_id = cal.period_type_id
377 AND application_id = :l_application_id
378 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
379 cal.record_type_id
380 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,
381 &BIS_PREVIOUS_ASOF_DATE) ';
382
383 l_sqltext := l_sqltext || l_session_where_clause ||
384 '
385 ) sess ';
386
387
388 /****
389 l_sqltext := '
390 SELECT
391 75 BIX_PMV_AI_SL,
392 80 BIX_PMV_AI_PREVSL,
393 90 BIX_PMV_AI_SLGOAL,
394 90 BIX_PMV_AI_PREVSLGOAL,
395 36 BIX_PMV_AI_SPANS,
396 36 BIX_PMV_AI_PREVSPANS,
397 34.2 BIX_PMV_AI_ABANRATE,
398 34.2 BIX_PMV_AI_PREVABANRATE,
399 30 BIX_PMV_AI_TRANRATE,
400 30 BIX_PMV_AI_PREVTRANRATE,
401 30 BIX_PMV_AI_INCALLHAND,
402 30 BIX_PMV_AI_PREVINCALLHAND,
403 273000 BIX_PMV_AI_DIALED,
404 273000 BIX_PMV_AI_PREVDIALED,
405 242970 BIX_PMV_AI_WEBCALL,
406 242970 BIX_PMV_AI_PREVWEBCALL,
407 268000 BIX_PMV_AI_AVAILRATE,
408 268000 BIX_PMV_AI_PREVAVAILRATE,
409 246560 BIX_PMV_AI_UTILRATE,
410 246560 BIX_PMV_AI_PREVUTILRATE,
411 18500 BIX_PMV_AI_AVGTALK,
412 18500 BIX_PMV_AI_PREVAVGTALK,
413 19240 BIX_PMV_AI_AVGWRAP,
414 19240 BIX_PMV_AI_PREVAVGWRAP,
415 5.2 BIX_PMV_AI_HANDPERHR,
416 5.2 BIX_PMV_AI_PREVHANDPERHR,
417 5.824 BIX_PMV_AI_CUST,
418 5.824 BIX_PMV_AI_PREVCUST,
419 56 BIX_PMV_AI_SRCR,
420 56 BIX_PMV_AI_PREVSRCR,
421 54.5 BIX_PMV_AI_LECR,
422 54.5 BIX_PMV_AI_PREVLECR
423 FROM DUAL ';
424
425 ****/
426
427 p_sql_text := l_sqltext;
428
429 l_custom_rec.attribute_name := ':l_cust_row_type';
430 l_custom_rec.attribute_value:= 'CDPR';
431 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
432 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
433
434 p_custom_output.Extend();
435 p_custom_output(p_custom_output.count) := l_custom_rec;
436
437 l_custom_rec.attribute_name := ':l_class_row_type';
438 l_custom_rec.attribute_value:= 'CDR';
439 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
440 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
441
442 p_custom_output.Extend();
443 p_custom_output(p_custom_output.count) := l_custom_rec;
444
445 l_custom_rec.attribute_name := ':l_period_type_id';
446 l_custom_rec.attribute_value:= 1;
447 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
448 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
449
450 p_custom_output.Extend();
451 p_custom_output(p_custom_output.count) := l_custom_rec;
452
453 l_custom_rec.attribute_name := ':l_period_start_time';
454 l_custom_rec.attribute_value:= '00:00';
455 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
456 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
457
458 p_custom_output.Extend();
459 p_custom_output(p_custom_output.count) := l_custom_rec;
460
461 IF l_call_center IS NOT NULL
462 THEN
463 l_custom_rec.attribute_name := ':l_call_center';
464 l_custom_rec.attribute_value:= l_call_center;
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
468 p_custom_output.Extend();
469 p_custom_output(p_custom_output.count) := l_custom_rec;
470 END IF;
471
472 IF l_classification IS NOT NULL
473 THEN
474 l_custom_rec.attribute_name := ':l_classification';
475 l_custom_rec.attribute_value:= l_classification;
476 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
477 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
478
479 p_custom_output.EXTEND;
480 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
481 END IF;
482
483 IF l_dnis IS NOT NULL AND l_dnis NOT IN ('INBOUND','OUTBOUND')
484 THEN
485 l_custom_rec.attribute_name := ':l_dnis';
486 l_custom_rec.attribute_value:= l_dnis;
487 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
488 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
489
490 p_custom_output.Extend();
491 p_custom_output(p_custom_output.count) := l_custom_rec;
492 END IF;
493
494 l_custom_rec.attribute_name := ':l_application_id';
495 l_custom_rec.attribute_value := 696;
496 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
497 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
498
499 p_custom_output.EXTEND;
500 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
501
502 --insert into bixtest values ('KPI:');
503 --insert into bixtest values (l_sqltext);
504 --commit;
505
506 EXCEPTION
507 WHEN OTHERS THEN
508 NULL;
509 END GET_SQL;
510 END BIX_PMV_AI_KPI_PRTLT_PKG;