[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AO_KPI_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AO_KPI_PRTLT_PKG AS
2 /*$Header: bixokpip.plb 115.5 2004/03/26 18:05:32 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_contgoal NUMBER;
16 l_abangoal NUMBER;
17 l_custom_rec BIS_QUERY_ATTRIBUTES;
18 l_period_type_id NUMBER := 1;
19 l_period_from DATE;
20 l_period_to DATE;
21 l_call_center VARCHAR2(3000);
22 l_call_where_clause VARCHAR2(3000);
23 l_session_where_clause VARCHAR2(3000);
24 l_source_code_where_clause VARCHAR2(3000);
25 l_sess_source_where_clause VARCHAR2(3000);
26 l_campaign_where_clause VARCHAR2(3000);
27 l_schedule_where_clause VARCHAR2(3000);
28 l_view_by VARCHAR2(3000);
29 l_campaign_id VARCHAR2(3000);
30 l_schedule_id VARCHAR2(3000);
31 l_source_code_id VARCHAR2(3000);
32 l_agent_group VARCHAR2(3000);
33
34 BEGIN
35 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
36 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
37
38 IF (FND_PROFILE.DEFINED('BIX_PMV_AO_TARCONT')) THEN
39 begin
40 l_contgoal := TO_NUMBER(FND_PROFILE.VALUE('BIX_PMV_AO_TARCONT'));
41 exception
42 when others then
43 l_contgoal:=0;
44 end;
45 ELSE
46 l_contgoal := 0;
47 END IF;
48
49 IF l_contgoal IS NULL THEN
50 l_contgoal := 0;
51 END IF;
52
53
54 IF (FND_PROFILE.DEFINED('BIX_PMV_AO_ABANRATEGOAL')) THEN
55 begin
56 l_abangoal := TO_NUMBER(FND_PROFILE.VALUE('BIX_PMV_AO_ABANRATEGOAL'));
57 exception
58 when others then
59 l_abangoal:=0;
60 end;
61 ELSE
62 l_abangoal := 0;
63 END IF;
64
65 IF l_abangoal IS NULL THEN
66 l_abangoal := 0;
67 END IF;
68
69
70 -- Get the parameters
71
72 BIX_PMV_DBI_UTL_PKG.get_ao_page_params( p_page_parameter_tbl,
73 l_as_of_date,
74 l_period_type,
75 l_record_type_id,
76 l_comp_type,
77 l_call_center,
78 l_campaign_id,
79 l_schedule_id,
80 l_source_code_id,
81 l_agent_group,
82 l_view_by
83 );
84
85 IF l_call_center IS NOT NULL THEN
86 l_call_where_clause := ' AND mv.server_group_id IN (:l_call_center) ';
87 l_session_where_clause := ' AND server_group_id IN (:l_call_center) ';
88 END IF;
89 IF l_campaign_id IS NOT NULL THEN
90 l_campaign_where_clause := ' AND mv.campaign_id IN (:l_campaign_id) ';
91 END IF;
92 IF l_schedule_id IS NOT NULL THEN
93 l_schedule_where_clause := ' AND mv.schedule_id IN (:l_schedule_id) ';
94 END IF;
95 IF l_source_code_id IS NOT NULL THEN
96 l_source_code_where_clause := ' AND mv.campaign_id in (select source_code_for_id from
97 ams_source_codes where source_code_id IN (:l_source_code_id) and arc_source_code_for=''CAMP'' and active_flag=''Y'') ';
98 l_sess_source_where_clause := ' AND sess.campaign_id in (select source_code_for_id from
99 ams_source_codes where source_code_id IN (:l_source_code_id) and arc_source_code_for=''CAMP'' and active_flag=''Y'') ';
100 END IF;
101
102 /*
103 l_sqltext := '
104 SELECT
105 1 BIX_PMV_AO_CONTPERHR,
106 1 BIX_PMV_AO_PREVCONTPERHR,
107 1 BIX_PMV_AO_TARCONT,
108 1 BIX_PMV_AO_PREVTARCONT,
109 1 BIX_PMV_AO_PORESP,
110 1 BIX_PMV_AO_PREVPORESP,
111 1 BIX_PMV_AO_ABANRATE,
112 1 BIX_PMV_AO_PREVABANRATE,
113 1 BIX_PMV_AO_ABANRATE_GL,
114 1 BIX_PMV_AO_PREVARGOAL,
115 1 BIX_PMV_AO_OUTCALLHAND,
116 1 BIX_PMV_AO_PREVOUTCALLHAND,
117 1 BIX_PMV_AO_SCHED,
118 1 BIX_PMV_AO_PREVSCHED,
119 1 BIX_PMV_AO_AVAILRATE,
120 1 BIX_PMV_AO_PREVAVAILRATE,
121 1 BIX_PMV_AO_UTILRATE,
122 1 BIX_PMV_AO_PREVUTILRATE,
123 1 BIX_PMV_AO_AVGTALK,
124 1 BIX_PMV_AO_PREVAVGTALK,
125 1 BIX_PMV_AO_AVGWRAP,
126 1 BIX_PMV_AO_PREVAVGWRAP,
127 1 BIX_PMV_AO_HANDPERHR,
128 1 BIX_PMV_AO_PREVHANDPERHR,
129 1 BIX_PMV_AO_CUST,
130 1 BIX_PMV_AO_PREVCUST,
131 1 BIX_PMV_AO_LECR,
132 1 BIX_PMV_AO_PREVLECR,
133 1 BIX_PMV_AO_OPCR,
134 1 BIX_PMV_AO_PREVOPCR,
135 1 BIX_PMV_AO_SRCR,
136 1 BIX_PMV_AO_PREVSRCR
137 FROM DUAL ';
138 ***/
139
140
141
142 l_sqltext := '
143 SELECT
144 SUM(CCONT/(DECODE(CLO,0,NULL,CLO)/3600)) BIX_PMV_AO_CONTPERHR ,
145 SUM(PCONT/(DECODE(PLO,0,NULL,PLO)/3600)) BIX_PMV_AO_PREVCONTPERHR,
146 SUM(CPR) BIX_PMV_AO_PORESP,
147 SUM(PPR) BIX_PMV_AO_PREVPORESP,
148 '||l_contgoal||' BIX_PMV_AO_TARCONT,
149 '||l_contgoal||' BIX_PMV_AO_PREVTARCONT,
150 sum(PAB)*100/
151 decode(sum(nvl(PIOPRED,0)+nvl(PIOCPRED,0)),0,NULL,
152 sum(nvl(PIOPRED,0)+nvl(PIOCPRED,0))) BIX_PMV_AO_PREVABANRATE,
153 sum(CAB)*100/
154 decode(sum(nvl(CIOPRED,0)+nvl(CIOCPRED,0)),0,NULL,
155 sum(nvl(CIOPRED,0)+nvl(CIOCPRED,0))) BIX_PMV_AO_ABANRATE,
156 /* Added for US Abandonment Rate */
157 sum(PABFTC)*100/
158 decode(sum(nvl(PIOPRED,0)+nvl(PIOCPRED,0)),0,NULL,
159 sum(nvl(PIOPRED,0)+nvl(PIOCPRED,0))) BIX_PMV_AO_US_ABANRATE_PP,
160 sum(CABFTC)*100/
161 decode(sum(nvl(CIOPRED,0)+nvl(CIOCPRED,0)),0,NULL,
162 sum(nvl(CIOPRED,0)+nvl(CIOCPRED,0))) BIX_PMV_AO_US_ABANRATE_CP,
163 /* End of additions */
164 '||l_abangoal||' BIX_PMV_AO_ABANRATE_GL,
165 '||l_abangoal||' BIX_PMV_AO_PREVARGOAL,
166 sum(csched) BIX_PMV_AO_SCHED,
167 sum(psched) BIX_PMV_AO_PREVSCHED,
168 sum(CHA) BIX_PMV_AO_OUTCALLHAND,
169 sum(PHA) BIX_PMV_AO_PREVOUTCALLHAND,
170 sum(nvl(CLO,0)-nvl(CID,0))*100/
171 decode(sum(CLO),0,NULL,sum(CLO)) BIX_PMV_AO_AVAILRATE,
172 sum(nvl(PLO,0)-nvl(PID,0))*100/
173 decode(sum(PLO),0,NULL,sum(PLO)) BIX_PMV_AO_PREVAVAILRATE,
174 sum(nvl(CLO,0)-nvl(CAV,0)-nvl(CID,0))*100/
175 decode(sum(CLO),0,NULL,sum(CLO)) BIX_PMV_AO_UTILRATE,
176 sum(nvl(PLO,0)-nvl(PAV,0)-nvl(PID,0))*100/
177 decode(sum(PLO),0,NULL,sum(PLO)) BIX_PMV_AO_PREVUTILRATE,
178 sum(PTA)/
179 decode(sum(nvl(PHA,0)+nvl(PHAC,0)),0,NULL,
180 sum(nvl(PHA,0)+nvl(PHAC,0))) BIX_PMV_AO_PREVAVGTALK,
181 sum(CTA)/
182 decode(sum(nvl(CHA,0)+nvl(CHAC,0)),0,NULL,
183 sum(nvl(CHA,0)+nvl(CHAC,0))) BIX_PMV_AO_AVGTALK,
184 sum(PWA)/
185 decode(sum(nvl(PHA,0)+nvl(PHAC,0)),0,NULL,
186 sum(nvl(PHA,0)+nvl(PHAC,0))) BIX_PMV_AO_PREVAVGWRAP,
187 sum(CWA)/
188 decode(sum(nvl(CHA,0)+nvl(CHAC,0)),0,NULL,
189 sum(nvl(CHA,0)+nvl(CHAC,0))) BIX_PMV_AO_AVGWRAP,
190 round( sum(CHA)*3600/
191 decode(sum(CLO),0,NULL,sum(CLO)),1) BIX_PMV_AO_HANDPERHR,
192 round(sum(PHA)*3600/
193 decode(sum(PLO),0,NULL,sum(PLO)),1) BIX_PMV_AO_PREVHANDPERHR,
194 sum(CCU) BIX_PMV_AO_CUST,
195 sum(PCU) BIX_PMV_AO_PREVCUST,
196 sum(CSR) BIX_PMV_AO_SRCR,
197 sum(PSR) BIX_PMV_AO_PREVSRCR,
198 sum(CLE) BIX_PMV_AO_LECR,
199 sum(PLE) BIX_PMV_AO_PREVLECR,
200 sum(COP) BIX_PMV_AO_OPCR,
201 sum(POP) BIX_PMV_AO_PREVOPCR
202 FROM
203 (
204 SELECT
205 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
206 AGENTCALL_CONTACT_COUNT ,0
207 )
208 ) PCONT,
209 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
210 AGENTCALL_CONTACT_COUNT ,0
211 )
212 ) CCONT,
213 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
214 AGENTCALL_PR_COUNT ,0
215 )
216 ) PPR,
217 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
218 AGENTCALL_PR_COUNT ,0
219 )
220 ) CPR,
221 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
222 CALL_CALLS_OFFERED_TOTAL,0
223 )
224 ) PIO,
225 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
226 CALL_CALLS_OFFERED_TOTAL,0
227 )
228 ) CIO,
229 sum(
230 case when dialing_method=''PRED'' then
231 decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
232 CALL_CALLS_OFFERED_TOTAL,0
233 )
234 else
235 0
236 end
237 ) PIOPRED,
238 sum(
239 case when dialing_method=''PRED'' then
240 decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
241 CALL_CALLS_OFFERED_TOTAL,0
242 )
243 else
244 0
245 end
246 )
247 CIOPRED,
248 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
249 CALL_CALLS_HANDLED_TOTAL ,0)) PHA,
250 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
251 CALL_CALLS_HANDLED_TOTAL,0)) CHA,
252 sum(
253 case when dialing_method=''PRED'' then
254 decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
255 CALL_CALLS_ABANDONED,0)
256 else 0
257 end
258 ) PAB,
259 sum(
260 case when dialing_method=''PRED'' then
261 decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
262 CALL_CALLS_ABANDONED,0)
263
264 else 0
265 end ) CAB,
266 /* Added for US Abandonment rate */
267 sum(
268 case when dialing_method=''PRED'' then
269 decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
270 CALL_CALLS_ABANDONED_US,0)
271 else 0
272 end
273 ) PABFTC,
274 sum(
275 case when dialing_method=''PRED'' then
276 decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
277 CALL_CALLS_ABANDONED_US,0)
278
279 else 0
280 end ) CABFTC,
281 /* End of additions */
282 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
283 CALL_CALLS_TRANSFERRED,0 )) PTR,
284 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
285 CALL_CALLS_TRANSFERRED ,0)) CTR,
286 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
287 CALL_TALK_TIME,0)) PTA,
288 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
289 CALL_TALK_TIME,0)) CTA,
290 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
291 AGENT_WRAP_TIME_NAC,0)) PWA,
292 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
293 AGENT_WRAP_TIME_NAC,0)) CWA,
294 count(DISTINCT(CASE
295 WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
296 AND party_id <> -1
297 THEN
298 PARTY_ID
299 END
300 )
301 ) CCU,
302 count(DISTINCT(CASE
303 WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
304 THEN
305 schedule_id
306 END
307 )
308 ) CSCHED,
309 count(DISTINCT(CASE
310 WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
311 THEN
312 schedule_id
313 END
314 )
315 ) PSCHED,
316
317 count(DISTINCT(CASE
318 WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
319 AND party_id <> -1
320 THEN
321 PARTY_ID
322 END
323 )
324 ) PCU,
325 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
326 AGENT_SR_CREATED,0)) PSR,
327 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
328 AGENT_SR_CREATED,0)) CSR,
329 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
330 AGENT_LEADS_CREATED,0)) PLE,
331 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
332 AGENT_LEADS_CREATED,0)) CLE,
333 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
334 AGENT_OPPORTUNITIES_CREATED,0)) POP,
335 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
336 AGENT_OPPORTUNITIES_CREATED,0)) COP
337 FROM bix_ao_call_details_mv mv,
338 fii_time_rpt_struct cal
339 WHERE mv.time_id = cal.time_id
340 AND mv.row_type = :l_cust_row_type
341 AND mv.period_type_id = cal.period_type_id
342 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
343 cal.record_type_id
344 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,
345 &BIS_PREVIOUS_ASOF_DATE) ';
346
347 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
348 '
349 ) fresh,
350 (
351 SELECT
352 nvl(SUM( CASE when
353 period_start_date = &BIS_PREVIOUS_EFFECTIVE_START_DATE
354 then
355 CALL_CONT_CALLS_OFFERED_NA
356 else
357 0
358 end),0) PIOC,
359 nvl(SUM( CASE when
360 period_start_date = &BIS_CURRENT_EFFECTIVE_START_DATE
361 then
362 CALL_CONT_CALLS_OFFERED_NA
363 else
364 0
365 end),0) CIOC,
366 nvl(SUM( CASE when
367 period_start_date = &BIS_PREVIOUS_EFFECTIVE_START_DATE
368 then
369 CALL_CONT_CALLS_OFFERED_NA
370 else
371 0
372 end),0) PIOCPRED,
373 nvl(SUM( CASE when
374 period_start_date = &BIS_CURRENT_EFFECTIVE_START_DATE
375 and dialing_method=''PRED''
376 then
377 CALL_CONT_CALLS_OFFERED_NA
378 else
379 0
380 end),0) CIOCPRED,
381 nvl(SUM( CASE when
382 period_start_date = &BIS_PREVIOUS_EFFECTIVE_START_DATE
383 then
384 CALL_CONT_CALLS_HANDLED_TOT_NA
385 else
386 0
387 end),0) PIHC,
388 nvl(SUM( CASE when
389 period_start_date = &BIS_CURRENT_EFFECTIVE_START_DATE
390 then
391 CALL_CONT_CALLS_HANDLED_TOT_NA
392 else
393 0
394 end),0) CIHC,
395 nvl(SUM(decode(period_start_date,&BIS_PREVIOUS_EFFECTIVE_START_DATE,
396 CALL_CONT_CALLS_HANDLED_TOT_NA,NULL)),0) PHAC,
397 nvl(SUM(decode(period_start_date,&BIS_CURRENT_EFFECTIVE_START_DATE,
398 CALL_CONT_CALLS_HANDLED_TOT_NA,NULL)),0) CHAC
399 FROM bix_ao_call_details_mv mv
400 WHERE time_id IN ( to_char(&BIS_CURRENT_EFFECTIVE_START_DATE,''J''),
401 to_char(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J''))
402 AND mv.period_type_id = :l_period_type_id
403 AND mv.row_type = :l_class_row_type
404 ';
405
406 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
407 '
408 ) continued,
409 (
410 SELECT
411 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
412 LOGIN_TIME,0)) PLO,
413 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
414 LOGIN_TIME,0)) CLO,
415 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
416 AVAILABLE_TIME,0)) PAV,
417 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
418 AVAILABLE_TIME,0)) CAV,
419 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
420 IDLE_TIME,0)) PID,
421 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
422 IDLE_TIME,0)) CID
423 FROM bix_agent_session_f sess,
424 fii_time_rpt_struct cal
425 WHERE sess.time_id = cal.time_id
426 AND sess.period_type_id = cal.period_type_id
427 AND application_id = :l_application_id
428 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
429 cal.record_type_id
430 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,
431 &BIS_PREVIOUS_ASOF_DATE) ';
432
433 l_sqltext := l_sqltext || l_session_where_clause ||
434 '
435 ) sess ';
436
437 /* Before passing l_sqltext to the calling proc, we trim it up a bit */
438 l_sqltext:=replace(replace(replace(replace(replace(l_sqltext,
439 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
440 p_sql_text := l_sqltext;
441
442
443 l_custom_rec.attribute_name := ':l_cust_row_type';
444 l_custom_rec.attribute_value:= 'C';
445 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
446 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
447
448 p_custom_output.Extend();
449 p_custom_output(p_custom_output.count) := l_custom_rec;
450
451 l_custom_rec.attribute_name := ':l_class_row_type';
452 l_custom_rec.attribute_value:= 'C';
453 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
454 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
455
456 p_custom_output.Extend();
457 p_custom_output(p_custom_output.count) := l_custom_rec;
458
459 l_custom_rec.attribute_name := ':l_period_type_id';
460 l_custom_rec.attribute_value:= 1;
461 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
462 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
463
464 p_custom_output.Extend();
465 p_custom_output(p_custom_output.count) := l_custom_rec;
466
467
468 IF l_call_center IS NOT NULL
469 THEN
470 l_custom_rec.attribute_name := ':l_call_center';
471 l_custom_rec.attribute_value:= l_call_center;
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
475 p_custom_output.Extend();
476 p_custom_output(p_custom_output.count) := l_custom_rec;
477 END IF;
478 IF l_campaign_id IS NOT NULL
479 THEN
480 l_custom_rec.attribute_name := ':l_campaign_id';
481 l_custom_rec.attribute_value:= l_campaign_id;
482 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
483 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
484
485 p_custom_output.Extend();
486 p_custom_output(p_custom_output.count) := l_custom_rec;
487 END IF;
488
489 IF l_schedule_id IS NOT NULL
490 THEN
491 l_custom_rec.attribute_name := ':l_schedule_id';
492 l_custom_rec.attribute_value:= l_schedule_id;
493 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
494 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
495
496 p_custom_output.Extend();
497 p_custom_output(p_custom_output.count) := l_custom_rec;
498 END IF;
499 IF l_source_code_id IS NOT NULL
500 THEN
501 l_custom_rec.attribute_name := ':l_source_code_id';
502 l_custom_rec.attribute_value:= l_source_code_id;
503 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
504 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
505
506 p_custom_output.Extend();
507 p_custom_output(p_custom_output.count) := l_custom_rec;
508 END IF;
509
510
511 l_custom_rec.attribute_name := ':l_application_id';
512 l_custom_rec.attribute_value := 696;
513 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
514 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
515
516 p_custom_output.EXTEND;
517 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
518
519 --insert into bixtest values ('KPI:');
520 --insert into bixtest values (l_sqltext);
521 --commit;
522
523
524
525 EXCEPTION
526 WHEN OTHERS THEN
527 NULL;
528 END GET_SQL;
529 END BIX_PMV_AO_KPI_PRTLT_PKG;