[Home] [Help]
PACKAGE BODY: APPS.IEC_REPORTS_UTIL_PVT
Source
1 PACKAGE BODY IEC_REPORTS_UTIL_PVT AS
2 /* $Header: IECVREPB.pls 120.2 2006/08/01 23:03:42 hhuang noship $ */
3
4 -- Sub-Program Unit Declarations
5
6 -- Update table
7 -- iec_rep_agent_status
8 -- resource_id
9 -- dial_server_id
10 -- campaign_id
11 -- campaign_schedule_id
12 -- status
13 -- status_reason
14 -- status_start_time
15 -- .... Std who columns
16 -- security_group_id
17 -- object_version_number
18
19 -- Check if a cpn is active..
20
21 PROCEDURE UPDATE_AGENT_STATE
22 (P_RESOURCE_ID IN NUMBER
23 ,P_DIAL_SERVER_ID IN NUMBER
24 ,P_CAMPAIGN_ID IN NUMBER
25 ,P_CPN_SCHEDULE_ID IN NUMBER
26 ,P_STATUS IN VARCHAR2
27 ,P_STATUS_REASON IN VARCHAR2
28 ,P_START_TIME IN VARCHAR2
29 )
30 AS
31 l_start_time DATE;
32 BEGIN
33 l_start_time := sysdate;
34 -- begin
35 -- dbms_output.put_line( ' Begin work on Update_agent_state');
36 -- update iec_rep_agent_status
37 -- set campaign_schedule_id = P_CPN_SCHEDULE_ID,
38 -- status = P_STATUS,
39 -- status_reason = P_STATUS_REASON,
40 -- status_start_time = to_date(P_START_TIME, 'yyyy-mm-dd HH24:MI:SS')
41 -- where resource_id = P_RESOURCE_ID
42 -- and dial_server_id = P_DIAL_SERVER_ID
43 -- and status = P_STATUS;
44
45 -- if SQL%ROWCOUNT = 0
46 -- then
47 if P_START_TIME IS NOT NULL then
48 l_start_time := to_date(P_START_TIME, 'yyyy-mm-dd HH24:MI:SS');
49 end if;
50 insert into iec_rep_agent_status (
51 resource_Id,
52 dial_server_id,
53 campaign_id,
54 campaign_schedule_id,
55 status,
56 status_reason,
57 status_start_time,
58 created_by,
59 creation_date,
60 last_updated_by,
61 last_update_date,
62 last_update_login,
63 security_group_id,
64 object_version_number )
65 values(
66 P_RESOURCE_ID,
67 P_DIAL_SERVER_ID,
68 P_CAMPAIGN_ID,
69 P_CPN_SCHEDULE_ID,
70 P_STATUS,
71 P_STATUS_REASON,
72 l_start_time,
73 NVL(FND_GLOBAL.user_id,-1),
74 sysdate,
75 NVL(FND_GLOBAL.conc_login_id,-1),
76 sysdate,
77 NVL(FND_GLOBAL.conc_login_id,-1),
78 0,
79 0
80 );
81 -- end if;
82 -- Exception
83 --WHEN OTHERS then
84 -- raise;
85 -- end;
86 -- dbms_output.put_line( 'Leaving.');
87 END UPDATE_AGENT_STATE;
88
89
90 -- Table columns -
91 -- AGENT_CPN_DETAIL_ID NUMBER(15) NOT NULL,
92 -- CREATED_BY NUMBER(15) NOT NULL,
93 -- CREATION_DATE DATE NOT NULL,
94 -- LAST_UPDATED_BY NUMBER(15),
95 -- LAST_UPDATE_DATE DATE NOT NULL,
96 -- LAST_UPDATE_LOGIN NUMBER(15),
97
98 -- RESOURCE_ID NUMBER(15) NOT NULL,
99 -- CAMPAIGN_ID NUMBER(15) NOT NULL,
100 -- CAMPAIGN_SCHEDULE_ID NUMBER(10) NOT NULL,
101 -- DIAL_SERVER_ID NUMBER(15) NOT NULL,
102 -- TOTAL_LOGIN_TIME NUMBER(9) NOT NULL,
103 -- TOTAL_ACTIVITY_TIME NUMBER(9) NOT NULL,
104 -- CURRENT_STATUS VARCHAR2(4) NOT NULL,
105 -- CURRENT_STATUS_BEGIN_TIME DATE NOT NULL,
106 -- COMPLETED_TRANSACTION_COUNT NUMBER(9) NOT NULL,
107 -- TOTAL_IDLE_TIME NUMBER(9),
108 -- TOTAL_WAIT_TIME NUMBER(9),
109 -- TOTAL_TALK_TIME NUMBER(9),
110 -- TOTAL_WRAPUP_TIME NUMBER(9),
111 -- CALLS_OFFERED NUMBER(4) NOT NULL,
112 -- PREDICTIVE_CALLS_OFFERED NUMBER(4) NOT NULL,
113 -- OUTCOME_ID NUMBER(10) NOT NULL,
114 -- RESULT_ID NUMBER(10) NOT NULL,
115 -- RESULT_COUNT NUMBER(10) NOT NULL,
116 -- POSITIVE_RESPONSE_FLAG VARCHAR2(1),
117 -- SECURITY_GROUP_ID NUMBER(15),
118 -- OBJECT_VERSION_NUMBER NUMBER
119
120 PROCEDURE UPDATE_AGENT_OUTCOME_DETAILS
121 ( P_RESOURCE_ID IN NUMBER
122 ,P_DIAL_SERVER_ID IN NUMBER
123 ,P_CAMPAIGN_ID IN NUMBER
124 ,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
125 ,P_OUTCOME_ID IN NUMBER
126 ,P_RESULT_ID IN NUMBER
127 ,P_RESULT_COUNT IN NUMBER
128 ,P_FTC_ABANDON_COUNT IN NUMBER
129 ,P_MESSAGE_PLAYED_COUNT IN NUMBER
130 ,P_POSITIVE_RESPONSE_FLAG IN VARCHAR2
131 ,P_CONTACT_FLAG IN VARCHAR2
132 ,P_TOTAL_IDLE_TIME IN NUMBER
133 ,P_TOTAL_WAIT_TIME IN NUMBER
134 ,P_TOTAL_TALK_TIME IN NUMBER
135 ,P_TOTAL_WRAPUP_TIME IN NUMBER
136 ,P_TOTAL_BREAK_TIME IN NUMBER
137 ,P_CALLS_OFFERED IN NUMBER
138 ,P_PRED_CALLS_OFFERED IN NUMBER
139 ,P_LOGIN_AGENT_COUNT IN NUMBER
140 )
141
142 AS
143 l_result_count NUMBER := 0;
144
145 l_login_time NUMBER := 0;
146 l_activity_time NUMBER := 0;
147
148 BEGIN
149 -- begin
150 l_activity_time := P_TOTAL_TALK_TIME + P_TOTAL_WRAPUP_TIME;
151 l_login_time := l_activity_time + P_TOTAL_IDLE_TIME + P_TOTAL_BREAK_TIME + P_TOTAL_WAIT_TIME;
152
153 l_result_count := P_RESULT_COUNT;
154
155 if P_RESULT_COUNT = 0 AND P_OUTCOME_ID > 0
156 then
157 l_result_count := 1;
158 end if;
159
160 -- dbms_output.put_line( ' Begin work on Update_agent_cpn_details');
161 -- update iec_rep_agent_cpn_details
162 -- set outcome_id = P_OUTCOME_ID,
163 -- result_id = P_RESULT_ID,
164 -- result_count = nvl( result_count, 0 ) + l_result_count,
165 -- positive_response_flag = P_POSITIVE_RESPONSE_FLAG,
166 -- contact_flag = P_CONTACT_FLAG,
167 -- last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
168 -- last_update_date = sysdate
169 -- where resource_id = P_RESOURCE_ID
170 -- and dial_server_id = P_DIAL_SERVER_ID
171 -- and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID
172 -- and ( outcome_id = P_OUTCOME_ID OR nvl(outcome_id, 0 ) = 0 )
173 -- and ( result_id = P_RESULT_id OR nvl( result_id, 0 ) = 0 );
174
175 -- if SQL%ROWCOUNT = 0
176 -- then
177 insert into iec_rep_agent_cpn_details (
178 agent_cpn_detail_id,
179 created_by,
180 creation_date,
181 last_updated_by,
182 last_update_date,
183 last_update_login,
184 resource_Id,
185 dial_server_id,
186 campaign_id,
187 campaign_schedule_id,
188 total_login_time,
189 total_activity_time,
190 total_idle_time,
191 total_wait_time,
192 total_wrapup_time,
193 total_talk_time,
194 calls_offered,
195 predictive_calls_offered,
196 outcome_id,
197 result_id,
198 result_count,
199 positive_response_flag,
200 security_group_id,
201 object_version_number,
202 current_status,
203 current_Status_begin_time,
204 contact_flag,
205 login_agent_count
206 )
207 values(
208 iec_rep_agent_cpn_details_s.nextval,
209 NVL(FND_GLOBAL.user_id,-1),
210 sysdate,
211 NVL(FND_GLOBAL.conc_login_id,-1),
212 sysdate,
213 NVL(FND_GLOBAL.conc_login_id,-1),
214 P_RESOURCE_ID,
215 P_DIAL_SERVER_ID,
216 P_CAMPAIGN_ID,
217 P_CAMPAIGN_SCHEDULE_ID,
218 l_login_time,
219 l_activity_time,
220 P_TOTAL_IDLE_TIME,
221 P_TOTAL_WAIT_TIME,
222 P_TOTAL_WRAPUP_TIME,
223 P_TOTAL_TALK_TIME,
224 P_CALLS_OFFERED,
225 P_PRED_CALLS_OFFERED,
226 P_OUTCOME_ID,
227 P_RESULT_ID,
228 l_result_count,
229 P_POSITIVE_RESPONSE_FLAG,
230 0,
231 0,
232 '5',
233 sysdate,
234 P_CONTACT_FLAG,
235 P_LOGIN_AGENT_COUNT
236 );
237 -- end if;
238
239
240 UPDATE_CPN_AGT_OUTCOME_DETAILS( P_DIAL_SERVER_ID
241 ,P_CAMPAIGN_ID
242 ,P_CAMPAIGN_SCHEDULE_ID
243 ,P_OUTCOME_ID
244 ,P_RESULT_ID
245 ,l_result_count
246 ,P_FTC_ABANDON_COUNT
247 ,P_MESSAGE_PLAYED_COUNT
248 ,P_POSITIVE_RESPONSE_FLAG
249 ,P_CONTACT_FLAG
250 ,P_TOTAL_IDLE_TIME
251 ,P_TOTAL_WAIT_TIME
252 ,P_TOTAL_TALK_TIME
253 ,P_TOTAL_WRAPUP_TIME
254 ,P_TOTAL_BREAK_TIME
255 ,P_CALLS_OFFERED
256 ,P_PRED_CALLS_OFFERED
257 );
258
259 -- dbms_output.put_line( 'Leaving.');
260 END UPDATE_AGENT_OUTCOME_DETAILS;
261
262
263 PROCEDURE UPDATE_AGENT_CURRENT_STATE
264 ( P_RESOURCE_ID IN NUMBER
265 ,P_DIAL_SERVER_ID IN NUMBER
266 ,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
267 ,P_CURRENT_STATUS IN VARCHAR2
268 ,P_CURRENT_STATUS_BEGIN_TIME IN VARCHAR2
269 )
270 AS
271 l_current_status_begin_time DATE;
272 BEGIN
273 l_current_status_begin_time := sysdate;
274 if P_CURRENT_STATUS_BEGIN_TIME IS NOT NULL then
275 l_current_status_begin_time := to_date(P_CURRENT_STATUS_BEGIN_TIME, 'yyyy-mm-dd HH24:MI:SS');
276 end if;
277 -- begin
278 -- Update all rows to '5' first and then set
279 -- the supplied cpn value with the "STATE".
280 -- '5' is state 'OUT' in fnd_lloups with lookup_type = 'BIX_DM_AGENT_STATUS'
281 --
282 Update iec_rep_agent_cpn_details /*+ index(iec_rep_agent_cpn_details iec_rep_agent_cpn_details_N1) */
283 set CURRENT_STATUS = '5'
284 where resource_Id = P_RESOURCE_ID
285 and dial_server_id = P_DIAL_SERVER_ID
286 and campaign_schedule_id <> P_CAMPAIGN_SCHEDULE_ID;
287
288 -- This will always exist.
289 if( P_CAMPAIGN_SCHEDULE_ID = -999999 )
290 then
291 Update iec_rep_agent_cpn_details /*+ index(iec_rep_agent_cpn_details iec_rep_agent_cpn_details_N1) */
292 set CURRENT_STATUS = P_CURRENT_STATUS,
293 current_status_begin_time = l_current_status_begin_time
294 where resource_Id = P_RESOURCE_ID
295 and dial_server_id = P_DIAL_SERVER_ID;
296 else
297 Update iec_rep_agent_cpn_details /*+ index(iec_rep_agent_cpn_details iec_rep_agent_cpn_details_N1) */
298 set CURRENT_STATUS = P_CURRENT_STATUS,
299 current_status_begin_time = l_current_status_begin_time
300 where resource_Id = P_RESOURCE_ID
301 and dial_server_id = P_DIAL_SERVER_ID
302 and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
303 end if;
304 -- exception
305 -- When others then
306 -- raise;
307 --end;
308 END UPDATE_AGENT_CURRENT_STATE;
309
310
311 -- CAMPAIGN_REP_ID NUMBER(15) NOT NULL,
312 -- CREATED_BY NUMBER(15) NOT NULL,
313 -- CREATION_DATE DATE NOT NULL,
314 -- LAST_UPDATED_BY NUMBER(15) NOT NULL,
315 -- LAST_UPDATE_DATE DATE NOT NULL,
316 -- LAST_UPDATE_LOGIN NUMBER,
317 -- CAMPAIGN_ID NUMBER(15) NOT NULL,
318 -- CAMPAIGN_SCHEDULE_ID NUMBER(15) NOT NULL,
319 -- DIAL_SERVER_ID NUMBER(15) NOT NULL,
320 -- DIALING_MODE VARCHAR2(4) NOT NULL,
321 -- TOTAL_LOGIN_TIME NUMBER(9) NOT NULL,
322 -- TOTAL_ACTIVITY_TIME NUMBER(9) NOT NULL,
323 -- NUM_LOGIN_AGENT NUMBER(4) NOT NULL,
324 -- MAX_LOGIN_AGENT NUMBER(4) NOT NULL,
325 -- COMPLETED_TRANSACTION_COUNT NUMBER(9) NOT NULL,
326 -- TOTAL_IDLE_TIME NUMBER(9) NOT NULL,
327 -- TOTAL_WAIT_TIME NUMBER(9) NOT NULL,
328 -- TOTAL_PREVIEW_TIME NUMBER(9) NOT NULL,
329 -- TOTAL_TALK_TIME NUMBER(9) NOT NULL,
330 -- TOTAL_WRAPUP_TIME NUMBER(9) NOT NULL,
331 -- LONGEST_IDLE_TIME NUMBER(9) NOT NULL,
332 -- LONGEST_WAIT_TIME NUMBER(9) NOT NULL,
333 -- LONGEST_TALK_TIME NUMBER(9) NOT NULL,
334 -- LONGEST_WRAPUP_TIME NUMBER(9) NOT NULL,
335 -- SHORTEST_IDLE_TIME NUMBER(9) NOT NULL,
336 -- SHORTEST_WAIT_TIME NUMBER(9) NOT NULL,
337 -- SHORTEST_TALK_TIME NUMBER(9) NOT NULL,
338 -- SHORTEST_WRAPUP_TIME NUMBER(9) NOT NULL,
339 -- TOTAL_DIALS NUMBER(9) NOT NULL,
340 -- PREVIEW_DIALS NUMBER(9),
341 -- PROGRESSIVE_DIALS NUMBER(9),
342 -- PREDICTIVE_DIALS NUMBER(9),
343 -- MANUAL_DIALS NUMBER(9),
344 -- PREDICTIVE_OUTCOME_ID NUMBER(10),
345 -- PREDICTIVE_OUTCOME_COUNT NUMBER(10),
346 -- PREDICTIVE_RESULT_ID NUMBER(10),
347 -- PREDICTIVE_RESULT_COUNT NUMBER(10),
348 -- PREDICTIVE_DIAL_FHQ_COUNT NUMBER(9),
349 -- CALLS_OFFERED NUMBER(9) NOT NULL,
350 -- PREDICTIVE_CALLS_OFFERED NUMBER(9) NOT NULL,
351 -- AGENT_OUTCOME_ID NUMBER(10),
352 -- AGENT_OUTCOME_COUNT NUMBER(10),
353 -- AGENT_RESULT_ID NUMBER(10),
354 -- AGENT_RESULT_COUNT NUMBER(10),
355 -- POSITIVE_RESPONSE_FLAG VARCHAR2(1),
356 -- NUM_AGENTS_ON_CALL NUMBER(10) NOT NULL,
357 -- NUM_AGENTS_IN_WRAPUP NUMBER(10) NOT NULL,
358 -- NUM_AGENTS_AVAILABLE NUMBER(10) NOT NULL ,
359 -- NUM_AGENTS_IDLE NUMBER(10) NOT NULL,
360 -- NUM_AGENTS_ON_BREAK NUMBER(10) NOT NULL,
361 -- SECURITY_GROUP_ID NUMBER(15),
362 -- OBJECT_VERSION_NUMBER NUMBER
363
364 PROCEDURE UPDATE_CPN_AGT_OUTCOME_DETAILS
365 ( P_DIAL_SERVER_ID IN NUMBER
366 ,P_CAMPAIGN_ID IN NUMBER
367 ,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
368 ,P_OUTCOME_ID IN NUMBER
369 ,P_RESULT_ID IN NUMBER
370 ,P_RESULT_COUNT IN NUMBER
371 ,P_FTC_ABANDON_COUNT IN NUMBER
372 ,P_MESSAGE_PLAYED_COUNT IN NUMBER
373 ,P_POSITIVE_RESPONSE_FLAG IN VARCHAR2
374 ,P_CONTACT_FLAG IN VARCHAR2
375 ,P_TOTAL_IDLE_TIME IN NUMBER
376 ,P_TOTAL_WAIT_TIME IN NUMBER
377 ,P_TOTAL_TALK_TIME IN NUMBER
378 ,P_TOTAL_WRAPUP_TIME IN NUMBER
379 ,P_TOTAL_BREAK_TIME IN NUMBER
380 ,P_CALLS_OFFERED IN NUMBER
381 ,P_PRED_CALLS_OFFERED IN NUMBER
382 )
383 AS
384 l_result_count NUMBER := 0;
385 l_activity_time NUMBER := 0;
386 l_login_time NUMBER := 0;
387
388 l_ln_idle_time NUMBER := 0;
389 l_ln_wait_time NUMBER := 0;
390 l_ln_talk_time NUMBER := 0;
391 l_ln_wrapup_time NUMBER := 0;
392
393 l_sh_idle_time NUMBER := 0;
394 l_sh_wait_time NUMBER := 0;
395 l_sh_talk_time NUMBER := 0;
396 l_sh_wrapup_time NUMBER := 0;
397
398 l_mx_login_time NUMBER := 0;
399 l_mx_activity_time NUMBER := 0;
400 l_mx_idle_time NUMBER := 0;
401 l_mx_wait_time NUMBER := 0;
402 l_mx_talk_time NUMBER := 0;
403 l_mx_wrapup_time NUMBER := 0;
404 l_mx_calls_offered NUMBER := 0;
405 l_mx_pred_calls_offered NUMBER := 0;
406
407 l_dialing_method VARCHAR2(10);
408 BEGIN
409
410 BEGIN
411 select dialing_method into l_dialing_method from
412 iec_g_executing_lists_v where schedule_id = P_CAMPAIGN_SCHEDULE_ID;
413
414 Exception
415 When No_DATA_FOUND then
416 l_dialing_method := 'UNKN';
417 end;
418
419 l_activity_time := P_TOTAL_TALK_TIME + P_TOTAL_WRAPUP_TIME;
420 l_login_time := l_activity_time + P_TOTAL_IDLE_TIME + P_TOTAL_BREAK_TIME + P_TOTAL_WAIT_TIME;
421
422 l_result_count := P_RESULT_COUNT;
423
424 if l_result_count <= 0 AND P_OUTCOME_ID > 0
425 then
426 l_result_count := 1;
427 end if;
428
429 begin
430 select max( nvl( total_login_time, 0 ) ),
431 max( nvl( total_activity_time, 0 ) ),
432 max( nvl( total_idle_time, 0 ) ),
433 max( nvl( total_wait_time, 0 ) ),
434 max( nvl( total_talk_time, 0 ) ),
435 max( nvl( total_wrapup_time, 0 ) ),
436 max( nvl( calls_offered, 0 ) ),
437 max( nvl( predictive_calls_offered, 0 ) ),
441 max(nvl( longest_wrapup_time, 0 )),
438 max(nvl( longest_idle_time, 0 )),
439 max( nvl( longest_wait_time, 0 ) ),
440 max(nvl( longest_talk_time, 0 ) ),
442 max(nvl( shortest_idle_time, 0 )),
443 max(nvl( shortest_wait_time, 0 )),
444 max(nvl( shortest_talk_time, 0 )),
445 max(nvl( shortest_wrapup_time, 0 ))
446 into
447 l_mx_login_time,
448 l_mx_activity_time,
449 l_mx_idle_time,
450 l_mx_wait_time,
451 l_mx_talk_time,
452 l_mx_wrapup_time,
453 l_mx_calls_offered,
454 l_mx_pred_calls_offered,
455 l_ln_idle_time,
456 l_ln_wait_time,
457 l_ln_talk_time,
458 l_ln_wrapup_time,
459 l_sh_idle_time,
460 l_sh_wait_time,
461 l_sh_talk_time,
462 l_sh_wrapup_time
463 from iec_rep_campaign_details
464 where dial_server_id = P_DIAL_SERVER_ID
465 and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
466
467 -- dbms_output.put_line( 'After Select...<'|| l_mx_idle_time||'> <'||l_mx_login_time);
468 if( l_mx_login_time is null AND l_mx_activity_time is null
469 and l_mx_idle_time is null and l_mx_wait_time is null
470 and l_mx_talk_time is null and l_mx_wrapup_time is null
471 and l_ln_idle_time is null and l_ln_wait_time is null
472 and l_sh_idle_time is null and l_sh_wait_time is null )
473 then
474
475 l_mx_login_time := 0;
476 l_mx_activity_time := 0;
477 l_mx_idle_time := 0;
478 l_mx_wait_time := 0;
479 l_mx_talk_time := 0;
480 l_mx_wrapup_time := 0;
481 l_mx_calls_offered := 0;
482 l_mx_pred_calls_offered := 0;
483 l_ln_idle_time := 0;
484 l_ln_wait_time := 0;
485 l_ln_talk_time := 0;
486 l_ln_wrapup_time := 0;
487 l_sh_idle_time := 0;
488 l_sh_wait_time := 0;
489 l_sh_talk_time := 0;
490 l_sh_wrapup_time := 0;
491
492 add_dummy_agent_record(
493 P_DIAL_SERVER_ID,
494 P_CAMPAIGN_ID,
495 P_CAMPAIGN_SCHEDULE_ID,
496 P_OUTCOME_ID,
497 P_RESULT_ID,
498 l_RESULT_COUNT,
499 P_FTC_ABANDON_COUNT,
500 P_MESSAGE_PLAYED_COUNT,
501 P_POSITIVE_RESPONSE_FLAG,
502 P_CONTACT_FLAG
503 );
504
505 else
506 -- dbms_output.put_line( 'In the else part of things...');
507 if l_ln_idle_time < P_TOTAL_IDLE_TIME
508 then
509 l_ln_idle_time := P_TOTAL_IDLE_TIME;
510 end if;
511
512 if l_ln_wait_time < P_TOTAL_WAIT_TIME
513 then
514 l_ln_wait_time := P_TOTAL_WAIT_TIME;
515 end if;
516
517 if l_ln_talk_time < P_TOTAL_TALK_TIME
518 then
519 l_ln_talk_time := P_TOTAL_TALK_TIME;
520 end if;
521
522 if l_ln_wrapup_time < P_TOTAL_WRAPUP_TIME
523 then
524 l_ln_wrapup_time := P_TOTAL_WRAPUP_TIME;
525 end if;
526
527 if P_TOTAL_IDLE_TIME > 0
528 then
529 if l_sh_idle_time > P_TOTAL_IDLE_TIME
530 then
531 l_sh_idle_time := P_TOTAL_IDLE_TIME;
532 elsif l_sh_idle_time = 0
533 then
534 l_sh_idle_time := P_TOTAL_IDLE_TIME;
535 end if;
536 end if;
537
538 if P_TOTAL_WAIT_TIME > 0
539 then
540 if l_sh_wait_time > P_TOTAL_WAIT_TIME
541 then
542 l_sh_wait_time := P_TOTAL_WAIT_TIME;
543 elsif l_sh_wait_time = 0
544 then
545 l_sh_wait_time := P_TOTAL_WAIT_TIME;
546 end if;
547 end if;
548
549 if P_TOTAL_TALK_TIME > 0
550 then
551 if l_sh_talk_time > P_TOTAL_TALK_TIME
552 then
553 l_sh_talk_time := P_TOTAL_TALK_TIME;
554 elsif l_sh_talk_time = 0
555 then
556 l_sh_talk_time := P_TOTAL_TALK_TIME;
557 end if;
558 end if;
559
560 if P_TOTAL_WRAPUP_TIME > 0
561 then
562 if l_sh_wrapup_time > P_TOTAL_WRAPUP_TIME
563 then
564 l_sh_wrapup_time := P_TOTAL_WRAPUP_TIME;
565 elsif l_sh_wrapup_time = 0
566 then
567 l_sh_wrapup_time := P_TOTAL_WRAPUP_TIME;
568 end if;
569 end if;
570
571 if P_OUTCOME_ID <> -999999
572 then
573
574 update iec_rep_campaign_details
575 set
576 agent_outcome_id = P_OUTCOME_ID,
577 agent_result_id = P_RESULT_ID,
578 dialing_mode = l_dialing_method,
579 agent_result_count = nvl( agent_result_count, 0 ) + l_result_count,
580 FTC_ABANDONMENT_COUNT = nvl(FTC_ABANDONMENT_COUNT, 0) + P_FTC_ABANDON_COUNT,
581 MESSAGE_PLAYED_COUNT = nvl(MESSAGE_PLAYED_COUNT, 0) + P_MESSAGE_PLAYED_COUNT,
582 positive_response_flag = P_POSITIVE_RESPONSE_FLAG,
583 contact_flag = P_CONTACT_FLAG,
584 last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
585 last_update_date = sysdate
586 where dial_server_id = P_DIAL_SERVER_ID
587 and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID
588 and (
589 (
590 ( agent_outcome_id = P_OUTCOME_ID and agent_result_id = P_RESULT_id )
591 OR
592 ( agent_outcome_id = -999999 and agent_result_id = -999999 )
593 )
594 AND
598 if SQL%ROWCOUNT = 0
595 predictive_outcome_id = -999999
596 );
597
599 then
600 -- dbms_output.put_line( 'Inserting a new row as the outcomes are different...');
601 -- This is a different outcome.
602 add_dummy_agent_record(
603 P_DIAL_SERVER_ID,
604 P_CAMPAIGN_ID,
605 P_CAMPAIGN_SCHEDULE_ID,
606 P_OUTCOME_ID,
607 P_RESULT_ID,
608 l_RESULT_COUNT,
609 P_FTC_ABANDON_COUNT,
610 P_MESSAGE_PLAYED_COUNT,
611 P_POSITIVE_RESPONSE_FLAG,
612 P_CONTACT_FLAG
613 );
614 end if;
615
616 end if;
617 end if;
618 -- Do not udpate num_voice_detected, calls_offered and predictive_calls_offered
619 -- They are updated by UPDATE_CPN_AGENT_STATS
620
621 update iec_rep_campaign_details
622 set total_login_time = nvl( l_mx_login_time, 0 ) + l_login_time,
623 total_activity_time = nvl( l_mx_activity_time, 0 )+ l_activity_time,
624 total_idle_time = nvl( l_mx_idle_time, 0 ) + P_TOTAL_IDLE_TIME,
625 total_wait_time = nvl( l_mx_wait_time, 0 ) + P_TOTAL_WAIT_TIME,
626 total_talk_time = nvl( l_mx_talk_time, 0 ) + P_TOTAL_TALK_TIME,
627 total_wrapup_time = nvl( l_mx_wrapup_time, 0 ) + P_TOTAL_WRAPUP_TIME,
628 longest_idle_time = l_ln_idle_time,
629 longest_wait_time = l_ln_wait_time,
630 longest_talk_time = l_ln_talk_time,
631 longest_wrapup_time = l_ln_wrapup_time,
632 shortest_idle_time = l_sh_idle_time,
633 shortest_wait_time = l_sh_wait_time,
634 shortest_talk_time = l_sh_talk_time,
635 shortest_wrapup_time = l_sh_wrapup_time,
636 dialing_mode = l_dialing_method,
637 last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
638 last_update_date = sysdate
639 where dial_server_id = P_DIAL_SERVER_ID
640 and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
641
642 Exception
643 When others then
644 raise;
645 end;
646
647 END UPDATE_CPN_AGT_OUTCOME_DETAILS;
648
649 PROCEDURE UPDATE_CPN_AGENT_STATS
650 ( P_DIAL_SERVER_ID IN NUMBER
651 ,P_CAMPAIGN_ID IN NUMBER
652 ,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
653 ,P_NUM_LOGIN_AGENTS IN NUMBER
654 ,P_NUM_CPN_LOGIN_AGENTS IN NUMBER
655 ,P_PREVIEW_DIALS IN NUMBER
656 ,P_TIMED_PREVIEW_DIALS IN NUMBER
657 ,P_PROGRESSIVE_DIALS IN NUMBER
658 ,P_PREDICTIVE_DIALS IN NUMBER
659 ,P_MANUAL_DIALS IN NUMBER
660 ,P_PREDICTIVE_DIAL_FHQ_COUNT IN NUMBER
661 ,P_CURRENT_IN_FHQ IN NUMBER
662 ,P_NUM_AGENTS_ON_CALL IN NUMBER
663 ,P_NUM_AGENTS_IN_WRAPUP IN NUMBER
664 ,P_NUM_AGENTS_AVAILABLE IN NUMBER
665 ,P_NUM_AGENTS_IDLE IN NUMBER
666 ,P_NUM_AGENTS_ON_BREAK IN NUMBER
667 ,P_NUM_VOICE_DETECTED IN NUMBER
668 ,P_CALLS_OFFERED IN NUMBER
669 ,P_PRED_CALLS_OFFERED IN NUMBER
670 )
671 AS
672 l_mx_login_agent NUMBER := 0;
673 l_mx_cpn_login_agent NUMBER :=0;
674 l_mx_total_dials NUMBER := 0;
675
676 l_mx_preview_dials NUMBER := 0;
677 l_mx_timed_preview_dials NUMBER := 0;
678 l_mx_progressive_dials NUMBER := 0;
679 l_mx_manual_dials NUMBER := 0;
680 l_mx_predictive_dials NUMBER := 0;
681 l_mx_voice_detected NUMBER := 0;
682 l_mx_calls_offered NUMBER := 0;
683 l_mx_pred_calls_offered NUMBER := 0;
684
685 BEGIN
686
687 begin
688 select max( nvl( max_login_agent,0) ),
689 max( nvl( cpn_max_login_agent, 0) ),
690 max( nvl( preview_dials, 0 ) ),
691 max( nvl( timed_preview_dials, 0 ) ),
692 max( nvl( progressive_dials, 0 ) ),
693 max( nvl( manual_dials, 0 ) ),
694 max( nvl( predictive_dials, 0 ) ),
695 max( nvl( num_voice_detected, 0 ) ),
696 max( nvl( calls_offered, 0 )),
697 max( nvl( predictive_calls_offered, 0 ))
698 into
699 l_mx_login_agent,
700 l_mx_cpn_login_agent,
701 l_mx_preview_dials,
702 l_mx_timed_preview_dials,
703 l_mx_progressive_dials,
704 l_mx_manual_dials,
705 l_mx_predictive_dials,
706 l_mx_voice_detected,
707 l_mx_calls_offered,
708 l_mx_pred_calls_offered
709
710 from iec_rep_campaign_details
711 where ( dial_server_id = P_DIAL_SERVER_ID OR dial_server_id = 0 )
712 and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
713
714 if( l_mx_login_agent is null AND
715 l_mx_preview_dials is null AND
716 l_mx_timed_preview_dials is null AND
717 l_mx_progressive_dials is null AND
718 l_mx_manual_dials is null AND
719 l_mx_predictive_dials is null AND
720 l_mx_voice_detected is null)
721 then
722 l_mx_total_dials := P_PREVIEW_DIALS + P_TIMED_PREVIEW_DIALS + P_PROGRESSIVE_DIALS + P_PREDICTIVE_DIALS + P_MANUAL_DIALS;
723
724 insert into iec_rep_campaign_details (
725 campaign_rep_id,
726 created_by,
727 creation_date,
728 last_updated_by,
729 last_update_date,
730 last_update_login,
731 campaign_id,
732 campaign_schedule_id,
733 dial_server_id,
734 dialing_mode,
735 total_login_time,
736 total_activity_time,
737 num_login_agent,
738 max_login_agent,
742 total_wait_time,
739 cpn_num_login_agent,
740 cpn_max_login_agent,
741 total_idle_time,
743 total_wrapup_time,
744 total_talk_time,
745 longest_idle_time,
746 longest_wait_time,
747 longest_talk_time,
748 longest_wrapup_time,
749 shortest_idle_time,
750 shortest_wait_time,
751 shortest_talk_time,
752 shortest_wrapup_time,
753 total_dials,
754 preview_dials,
755 timed_preview_dials,
756 progressive_dials,
757 predictive_dials,
758 manual_dials,
759 predictive_outcome_id,
760 predictive_result_id,
761 predictive_result_count,
762 predictive_dial_fhq_count,
763 calls_offered,
764 predictive_calls_offered,
765 agent_outcome_id,
766 agent_result_id,
767 agent_result_count,
768 positive_response_flag,
769 num_agents_on_call,
770 num_agents_in_wrapup,
771 num_agents_available,
772 num_agents_idle,
773 num_agents_on_break,
774 security_group_id,
775 object_version_number,
776 contact_flag,
777 num_cust_in_fhq,
778 num_voice_detected
779 )
780 values
781 (
782 iec_rep_campaign_details_s.nextval,
783 NVL(FND_GLOBAL.user_id,-1),
784 sysdate,
785 NVL(FND_GLOBAL.conc_login_id,-1),
786 sysdate,
787 NVL(FND_GLOBAL.conc_login_id,-1),
788 P_CAMPAIGN_ID,
789 P_CAMPAIGN_SCHEDULE_ID,
790 P_DIAL_SERVER_ID,
791 'UNKN',
792 0,
793 0,
794 P_NUM_LOGIN_AGENTS,
795 P_NUM_LOGIN_AGENTS,
796 P_NUM_CPN_LOGIN_AGENTS,
797 P_NUM_CPN_LOGIN_AGENTS,
798 0,
799 0,
800 0,
801 0,
802 0,
803 0,
804 0,
805 0,
806 0,
807 0,
808 0,
809 0,
810 l_mx_total_dials,
811 P_PREVIEW_DIALS,
812 P_TIMED_PREVIEW_DIALS,
813 P_PROGRESSIVE_DIALS,
814 P_PREDICTIVE_DIALS,
815 P_MANUAL_DIALS,
816 -999999,
817 -999999,
818 0,
819 P_PREDICTIVE_DIAL_FHQ_COUNT,
820 0,
821 0,
822 -999999,
823 -999999,
824 0,
825 'N',
826 P_NUM_AGENTS_ON_CALL,
827 P_NUM_AGENTS_IN_WRAPUP,
828 P_NUM_AGENTS_AVAILABLE,
829 P_NUM_AGENTS_IDLE,
830 P_NUM_AGENTS_ON_BREAK,
831 0,
832 0,
833 'N',
834 P_CURRENT_IN_FHQ,
835 0
836 );
837
838 else
839
840 if l_mx_login_agent < P_NUM_LOGIN_AGENTS
841 then
842 l_mx_login_agent := P_NUM_LOGIN_AGENTS;
843 end if;
844
845 if l_mx_cpn_login_agent < P_NUM_CPN_LOGIN_AGENTS
846 then
847 l_mx_cpn_login_agent := P_NUM_CPN_LOGIN_AGENTS;
848 end if;
849
850 l_mx_preview_dials := l_mx_preview_dials + P_PREVIEW_DIALS;
851 l_mx_timed_preview_dials := l_mx_timed_preview_dials + P_TIMED_PREVIEW_DIALS;
852 l_mx_progressive_dials := l_mx_progressive_dials + P_PROGRESSIVE_DIALS;
853 l_mx_manual_dials := l_mx_manual_dials + P_MANUAL_DIALS;
854 l_mx_predictive_dials := l_mx_predictive_dials + P_PREDICTIVE_DIALS;
855
856 l_mx_total_dials := l_mx_preview_dials + l_mx_progressive_dials + l_mx_manual_dials + l_mx_predictive_dials + l_mx_timed_preview_dials;
857
858 update iec_rep_campaign_details
859 set num_login_agent = P_NUM_LOGIN_AGENTS,
860 max_login_agent = l_mx_login_agent,
861 cpn_num_login_agent = P_NUM_CPN_LOGIN_AGENTS,
862 cpn_max_login_agent = l_mx_cpn_login_agent,
863 total_dials = l_mx_total_dials,
864 preview_dials = l_mx_preview_dials,
865 timed_preview_dials = l_mx_timed_preview_dials,
866 progressive_dials = l_mx_PROGRESSIVE_DIALS,
867 predictive_dials = l_mx_PREDICTIVE_DIALS,
868 manual_dials = l_mx_MANUAL_DIALS,
869 num_voice_detected = nvl(l_mx_voice_detected, 0) + P_NUM_VOICE_DETECTED,
870 calls_offered = nvl( l_mx_calls_offered, 0 ) + P_CALLS_OFFERED,
871 predictive_calls_offered = nvl( l_mx_pred_calls_offered, 0 ) + P_PRED_CALLS_OFFERED,
872 predictive_dial_fhq_count = predictive_dial_fhq_count + P_PREDICTIVE_DIAL_FHQ_COUNT,
873 num_cust_in_fhq = P_CURRENT_IN_FHQ,
874 num_agents_on_call = P_NUM_AGENTS_ON_CALL,
875 num_agents_in_wrapup = P_NUM_AGENTS_IN_WRAPUP,
876 num_agents_available = P_NUM_AGENTS_AVAILABLE,
877 num_agents_idle = P_NUM_AGENTS_IDLE,
878 num_agents_on_break = P_NUM_AGENTS_ON_BREAK,
879 last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
880 last_update_date = sysdate
881 where ( dial_server_id = P_DIAL_SERVER_ID OR dial_server_id = 0 )
882 and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
883 end if;
884 Exception
885 When OTHERS THEN
886 raise;
887 end;
888 END UPDATE_CPN_AGENT_STATS;
889
890 PROCEDURE UPDATE_CPN_DIAL_STATS
891 ( P_DIAL_SERVER_ID IN NUMBER
892 ,P_CAMPAIGN_ID IN NUMBER
893 ,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
894 ,P_OUTCOME_ID IN NUMBER
895 ,P_RESULT_ID IN NUMBER
896 ,P_RESULT_COUNT IN NUMBER
897 ,P_FTC_ABANDON_COUNT IN NUMBER
898 ,P_MESSAGE_PLAYED_COUNT IN NUMBER
899 ,P_POSITIVE_RESPONSE_FLAG IN VARCHAR2
903 AS
900 ,P_CONTACT_FLAG IN VARCHAR2
901 ,P_TOTAL_DIALS IN NUMBER
902 )
904 l_result_count NUMBER := 0;
905 BEGIN
906 -- begin
907 l_result_count := P_RESULT_COUNT;
908
909 if P_RESULT_COUNT = 0 AND P_OUTCOME_ID > 0
910 then
911 l_result_count := 1;
912 end if;
913
914 insert into iec_rep_cpn_dial_stats (
915 cpn_dial_stats_id,
916 created_by,
917 creation_date,
918 last_updated_by,
919 last_update_date,
920 last_update_login,
921 campaign_id,
922 campaign_schedule_id,
923 dial_server_id,
924 data_start_time,
925 data_end_time,
926 total_dials,
927 outcome_id,
928 result_id,
929 result_count,
930 positive_response_flag,
931 security_group_id,
932 object_version_number,
933 contact_flag
934 )
935 values(
936 iec_rep_cpn_dial_stats_s.nextval,
937 NVL(FND_GLOBAL.user_id,-1),
938 sysdate,
939 NVL(FND_GLOBAL.conc_login_id,-1),
940 sysdate,
941 NVL(FND_GLOBAL.conc_login_id,-1),
942 P_CAMPAIGN_ID,
943 P_CAMPAIGN_SCHEDULE_ID,
944 P_DIAL_SERVER_ID,
945 sysdate,
946 sysdate,
947 P_TOTAL_DIALS,
948 P_OUTCOME_ID,
949 P_RESULT_ID,
950 l_result_count,
951 P_POSITIVE_RESPONSE_FLAG,
952 0,
953 0,
954 P_CONTACT_FLAG
955 );
956
957 UPDATE_CPN_SYS_OUTCOME_DETAILS
958 ( P_DIAL_SERVER_ID
959 ,P_CAMPAIGN_ID
960 ,P_CAMPAIGN_SCHEDULE_ID
961 ,P_OUTCOME_ID
962 ,P_RESULT_ID
963 ,l_RESULT_COUNT
964 ,P_FTC_ABANDON_COUNT
965 ,P_MESSAGE_PLAYED_COUNT
966 ,P_POSITIVE_RESPONSE_FLAG
967 ,P_CONTACT_FLAG
968 ,P_TOTAL_DIALS
969 );
970
971 END UPDATE_CPN_DIAL_STATS;
972
973
974 PROCEDURE UPDATE_CPN_SYS_OUTCOME_DETAILS
975 ( P_DIAL_SERVER_ID IN NUMBER
976 ,P_CAMPAIGN_ID IN NUMBER
977 ,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
978 ,P_PREDICTIVE_OUTCOME_ID IN NUMBER
979 ,P_PREDICTIVE_RESULT_ID IN NUMBER
980 ,P_PREDICTIVE_RESULT_COUNT IN NUMBER
981 ,P_FTC_ABANDON_COUNT IN NUMBER
982 ,P_MESSAGE_PLAYED_COUNT IN NUMBER
983 ,P_POSITIVE_RESPONSE_FLAG IN VARCHAR2
984 ,P_CONTACT_FLAG IN VARCHAR2
985 ,P_TOTAL_DIALS IN NUMBER
986 )
987 AS
988 l_result_count NUMBER := 0;
989 l_activity_time NUMBER := 0;
990 l_login_time NUMBER := 0;
991
992 l_rec_present NUMBER := 0;
993 l_dialing_method VARCHAR2(10);
994
995 l_ln_idle_time NUMBER := 0;
996 l_ln_wait_time NUMBER := 0;
997 l_ln_talk_time NUMBER := 0;
998 l_ln_wrapup_time NUMBER := 0;
999
1000 l_sh_idle_time NUMBER := 0;
1001 l_sh_wait_time NUMBER := 0;
1002 l_sh_talk_time NUMBER := 0;
1003 l_sh_wrapup_time NUMBER := 0;
1004
1005 l_mx_login_time NUMBER := 0;
1006 l_mx_activity_time NUMBER := 0;
1007 l_mx_idle_time NUMBER := 0;
1008 l_mx_wait_time NUMBER := 0;
1009 l_mx_talk_time NUMBER := 0;
1010 l_mx_wrapup_time NUMBER := 0;
1011 l_mx_calls_offered NUMBER := 0;
1012 l_mx_pred_calls_offered NUMBER := 0;
1013
1014 l_total_dials NUMBER := 0;
1015
1016 BEGIN
1017
1018 BEGIN
1019 select dialing_method into l_dialing_method from
1020 iec_g_executing_lists_v where schedule_id = P_CAMPAIGN_SCHEDULE_ID;
1021
1022 Exception
1023 When No_DATA_FOUND then
1024 l_dialing_method := 'UNKN';
1025 end;
1026
1027 l_result_count := P_PREDICTIVE_RESULT_COUNT;
1028
1029 if l_result_count <= 0 AND P_PREDICTIVE_OUTCOME_ID > 0
1030 then
1031 l_result_count := 1;
1032 l_total_dials := 1;
1033 end if;
1034
1035 begin
1036 select campaign_rep_id
1037 into
1038 l_rec_present
1039 from iec_rep_campaign_details
1040 where dial_server_id = P_DIAL_SERVER_ID
1041 and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID
1042 and predictive_outcome_id = P_PREDICTIVE_OUTCOME_ID
1043 and predictive_result_id = P_PREDICTIVE_RESULT_ID
1044 and nvl(agent_outcome_id, -999999 ) < 0;
1045
1046 update iec_rep_campaign_details
1047 set predictive_outcome_id = P_PREDICTIVE_OUTCOME_ID,
1048 predictive_result_id = P_PREDICTIVE_RESULT_ID,
1049 dialing_mode = l_dialing_method,
1050 predictive_result_count = nvl( predictive_result_count, 0 ) + l_result_count,
1051 FTC_ABANDONMENT_COUNT = nvl(FTC_ABANDONMENT_COUNT, 0) + P_FTC_ABANDON_COUNT,
1052 MESSAGE_PLAYED_COUNT = nvl(MESSAGE_PLAYED_COUNT, 0) + P_MESSAGE_PLAYED_COUNT,
1053 positive_response_flag = P_POSITIVE_RESPONSE_FLAG,
1054 contact_flag = P_CONTACT_FLAG,
1055 total_dials = l_TOTAL_DIALS,
1056 last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
1057 last_update_date = sysdate
1058 where campaign_rep_id = l_rec_present;
1059
1060 exception
1061 When no_data_found then
1062
1063 begin
1064 select campaign_rep_id
1065 into
1066 l_rec_present
1067 from iec_rep_campaign_details
1068 where dial_server_id = P_DIAL_SERVER_ID
1069 and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID
1073
1070 and nvl(predictive_outcome_id, -999999 ) = -999999
1071 and nvl(predictive_result_id, -999999) = -999999
1072 and nvl(agent_outcome_id, -999999 ) < 0;
1074 update iec_rep_campaign_details
1075 set predictive_outcome_id = P_PREDICTIVE_OUTCOME_ID,
1076 predictive_result_id = P_PREDICTIVE_RESULT_ID,
1077 dialing_mode = l_dialing_method,
1078 predictive_result_count = nvl( predictive_result_count, 0 ) + l_result_count,
1079 FTC_ABANDONMENT_COUNT = nvl(FTC_ABANDONMENT_COUNT, 0) + P_FTC_ABANDON_COUNT,
1080 MESSAGE_PLAYED_COUNT = nvl(MESSAGE_PLAYED_COUNT, 0) + P_MESSAGE_PLAYED_COUNT,
1081 positive_response_flag = P_POSITIVE_RESPONSE_FLAG,
1082 contact_flag = P_CONTACT_FLAG,
1083 total_dials = l_TOTAL_DIALS,
1084 last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
1085 last_update_date = sysdate
1086 where campaign_rep_id = l_rec_present;
1087
1088 exception
1089 when no_data_found then
1090 -- dbms_output.put_line( 'After Select...<'|| l_mx_idle_time||'> <'||l_mx_login_time);
1091 ADD_DUMMY_SYS_RECORD (
1092 P_DIAL_SERVER_ID
1093 ,P_CAMPAIGN_ID
1094 ,P_CAMPAIGN_SCHEDULE_ID
1095 ,P_PREDICTIVE_OUTCOME_ID
1096 ,P_PREDICTIVE_RESULT_ID
1097 ,l_RESULT_COUNT
1098 ,P_FTC_ABANDON_COUNT
1099 ,P_MESSAGE_PLAYED_COUNT
1100 ,P_POSITIVE_RESPONSE_FLAG
1101 ,P_CONTACT_FLAG
1102 ,l_dialing_method
1103 );
1104 end;
1105 end;
1106
1107 select max( nvl( total_login_time, 0 ) ),
1108 max( nvl( total_activity_time, 0 ) ),
1109 max( nvl( total_idle_time, 0 ) ),
1110 max( nvl( total_wait_time, 0 ) ),
1111 max( nvl( total_talk_time, 0 ) ),
1112 max( nvl( total_wrapup_time, 0 ) ),
1113 max( nvl( calls_offered, 0 ) ),
1114 max( nvl( predictive_calls_offered, 0 ) ),
1115 max(nvl( longest_idle_time, 0 )),
1116 max( nvl( longest_wait_time, 0 ) ),
1117 max(nvl( longest_talk_time, 0 ) ),
1118 max(nvl( longest_wrapup_time, 0 )),
1119 max(nvl( shortest_idle_time, 0 )),
1120 max(nvl( shortest_wait_time, 0 )),
1121 max(nvl( shortest_talk_time, 0 )),
1122 max(nvl( shortest_wrapup_time, 0 ))
1123 into
1124 l_mx_login_time,
1125 l_mx_activity_time,
1126 l_mx_idle_time,
1127 l_mx_wait_time,
1128 l_mx_talk_time,
1129 l_mx_wrapup_time,
1130 l_mx_calls_offered,
1131 l_mx_pred_calls_offered,
1132 l_ln_idle_time,
1133 l_ln_wait_time,
1134 l_ln_talk_time,
1135 l_ln_wrapup_time,
1136 l_sh_idle_time,
1137 l_sh_wait_time,
1138 l_sh_talk_time,
1139 l_sh_wrapup_time
1140 from iec_rep_campaign_details
1141 where dial_server_id = P_DIAL_SERVER_ID
1142 and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
1143
1144 update iec_rep_campaign_details
1145 set total_login_time = l_mx_login_time,
1146 total_activity_time = l_mx_activity_time,
1147 total_idle_time = l_mx_idle_time,
1148 total_wait_time = l_mx_wait_time,
1149 total_talk_time = l_mx_talk_time,
1150 total_wrapup_time = l_mx_wrapup_time,
1151 longest_idle_time = l_ln_idle_time,
1152 longest_wait_time = l_ln_wait_time,
1153 longest_talk_time = l_ln_talk_time,
1154 longest_wrapup_time = l_ln_wrapup_time,
1155 shortest_idle_time = l_sh_idle_time,
1156 shortest_wait_time = l_sh_wait_time,
1157 shortest_talk_time = l_sh_talk_time,
1158 shortest_wrapup_time = l_sh_wrapup_time,
1159 calls_offered = l_mx_calls_offered,
1160 predictive_calls_offered = l_mx_pred_calls_offered,
1161 dialing_mode = l_dialing_method,
1162 last_updated_by = NVL(FND_GLOBAL.conc_login_id,-1),
1163 last_update_date = sysdate
1164 where dial_server_id = P_DIAL_SERVER_ID
1165 and campaign_schedule_id = P_CAMPAIGN_SCHEDULE_ID;
1166
1167 END UPDATE_CPN_SYS_OUTCOME_DETAILS;
1168
1169 PROCEDURE ADD_DUMMY_AGENT_RECORD
1170 ( P_DIAL_SERVER_ID IN NUMBER
1171 ,P_CAMPAIGN_ID IN NUMBER
1172 ,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
1173 ,P_OUTCOME_ID IN NUMBER
1174 ,P_RESULT_ID IN NUMBER
1175 ,P_RESULT_COUNT IN NUMBER
1176 ,P_FTC_ABANDON_COUNT IN NUMBER
1177 ,P_MESSAGE_PLAYED_COUNT IN NUMBER
1178 ,P_POSITIVE_RESPONSE_FLAG IN VARCHAR2
1179 ,P_CONTACT_FLAG IN VARCHAR2
1180 )
1181 AS
1182
1183 BEGIN
1184
1185 insert into iec_rep_campaign_details (
1186 campaign_rep_id,
1187 created_by,
1188 creation_date,
1189 last_updated_by,
1190 last_update_date,
1191 last_update_login,
1192 campaign_id,
1193 campaign_schedule_id,
1194 dial_server_id,
1195 dialing_mode,
1196 total_login_time,
1197 total_activity_time,
1198 num_login_agent,
1199 max_login_agent,
1200 total_idle_time,
1201 total_wait_time,
1202 total_wrapup_time,
1203 total_talk_time,
1204 longest_idle_time,
1205 longest_wait_time,
1206 longest_talk_time,
1207 longest_wrapup_time,
1208 shortest_idle_time,
1209 shortest_wait_time,
1210 shortest_talk_time,
1211 shortest_wrapup_time,
1212 total_dials,
1213 preview_dials,
1214 progressive_dials,
1218 predictive_result_id,
1215 predictive_dials,
1216 manual_dials,
1217 predictive_outcome_id,
1219 predictive_result_count,
1220 predictive_dial_fhq_count,
1221 calls_offered,
1222 predictive_calls_offered,
1223 agent_outcome_id,
1224 agent_result_id,
1225 agent_result_count,
1226 positive_response_flag,
1227 num_agents_on_call,
1228 num_agents_in_wrapup,
1229 num_agents_available,
1230 num_agents_idle,
1231 num_agents_on_break,
1232 security_group_id,
1233 object_version_number,
1234 contact_flag,
1235 num_cust_in_fhq,
1236 num_voice_detected,
1237 FTC_ABANDONMENT_COUNT,
1238 MESSAGE_PLAYED_COUNT
1239 )
1240 values
1241 (
1242 iec_rep_campaign_details_s.nextval,
1243 NVL(FND_GLOBAL.user_id,-1),
1244 sysdate,
1245 NVL(FND_GLOBAL.conc_login_id,-1),
1246 sysdate,
1247 NVL(FND_GLOBAL.conc_login_id,-1),
1248 P_CAMPAIGN_ID,
1249 P_CAMPAIGN_SCHEDULE_ID,
1250 P_DIAL_SERVER_ID,
1251 'UNKN',
1252 0,
1253 0,
1254 0,
1255 0,
1256 0,
1257 0,
1258 0,
1259 0,
1260 0,
1261 0,
1262 0,
1263 0,
1264 0,
1265 0,
1266 0,
1267 0,
1268 0,
1269 0,
1270 0,
1271 0,
1272 0,
1273 -999999,
1274 -999999,
1275 0,
1276 0,
1277 0,
1278 0,
1279 P_OUTCOME_ID,
1280 P_RESULT_ID,
1281 P_RESULT_COUNT,
1282 P_POSITIVE_RESPONSE_FLAG,
1283 0,
1284 0,
1285 0,
1286 0,
1287 0,
1288 0,
1289 0,
1290 P_CONTACT_FLAG,
1291 0,
1292 0,
1293 P_FTC_ABANDON_COUNT,
1294 P_MESSAGE_PLAYED_COUNT
1295 );
1296
1297 END ADD_DUMMY_AGENT_RECORD;
1298
1299
1300 PROCEDURE ADD_DUMMY_SYS_RECORD
1301 ( P_DIAL_SERVER_ID IN NUMBER
1302 ,P_CAMPAIGN_ID IN NUMBER
1303 ,P_CAMPAIGN_SCHEDULE_ID IN NUMBER
1304 ,P_PREDICTIVE_OUTCOME_ID IN NUMBER
1305 ,P_PREDICTIVE_RESULT_ID IN NUMBER
1306 ,P_PREDICTIVE_RESULT_COUNT IN NUMBER
1307 ,P_FTC_ABANDON_COUNT IN NUMBER
1308 ,P_MESSAGE_PLAYED_COUNT IN NUMBER
1309 ,P_POSITIVE_RESPONSE_FLAG IN VARCHAR2
1310 ,P_CONTACT_FLAG IN VARCHAR2
1311 ,P_DIALING_METHOD IN VARCHAR2
1312 )
1313 AS
1314 BEGIN
1315 insert into iec_rep_campaign_details (
1316 campaign_rep_id,
1317 created_by,
1318 creation_date,
1319 last_updated_by,
1320 last_update_date,
1321 last_update_login,
1322 campaign_id,
1323 campaign_schedule_id,
1324 dial_server_id,
1325 dialing_mode,
1326 total_login_time,
1327 total_activity_time,
1328 num_login_agent,
1329 max_login_agent,
1330 total_idle_time,
1331 total_wait_time,
1332 total_wrapup_time,
1333 total_talk_time,
1334 longest_idle_time,
1335 longest_wait_time,
1336 longest_talk_time,
1337 longest_wrapup_time,
1338 shortest_idle_time,
1339 shortest_wait_time,
1340 shortest_talk_time,
1341 shortest_wrapup_time,
1342 total_dials,
1343 preview_dials,
1344 progressive_dials,
1345 predictive_dials,
1346 manual_dials,
1347 predictive_outcome_id,
1348 predictive_result_id,
1349 predictive_result_count,
1350 predictive_dial_fhq_count,
1351 calls_offered,
1352 predictive_calls_offered,
1353 agent_outcome_id,
1354 agent_result_id,
1355 agent_result_count,
1356 positive_response_flag,
1357 num_agents_on_call,
1358 num_agents_in_wrapup,
1359 num_agents_available,
1360 num_agents_idle,
1361 num_agents_on_break,
1362 security_group_id,
1363 object_version_number,
1364 contact_flag,
1365 num_cust_in_fhq,
1366 num_voice_detected,
1367 FTC_ABANDONMENT_COUNT,
1368 MESSAGE_PLAYED_COUNT
1369 )
1370 values
1371 (
1372 iec_rep_campaign_details_s.nextval,
1373 NVL(FND_GLOBAL.user_id,-1),
1374 sysdate,
1375 NVL(FND_GLOBAL.conc_login_id,-1),
1376 sysdate,
1377 NVL(FND_GLOBAL.conc_login_id,-1),
1378 P_CAMPAIGN_ID,
1379 P_CAMPAIGN_SCHEDULE_ID,
1380 P_DIAL_SERVER_ID,
1381 P_DIALING_Method,
1382 0,
1383 0,
1384 0,
1385 0,
1386 0,
1387 0,
1388 0,
1389 0,
1390 0,
1391 0,
1392 0,
1393 0,
1394 0,
1395 0,
1396 0,
1397 0,
1398 P_PREDICTIVE_RESULT_COUNT,
1399 0,
1400 0,
1401 0,
1402 0,
1403 P_PREDICTIVE_OUTCOME_ID,
1404 P_PREDICTIVE_RESULT_ID,
1405 P_PREDICTIVE_RESULT_COUNT,
1406 0,
1407 0,
1408 0,
1409 -999999,
1410 -999999,
1411 0,
1412 P_POSITIVE_RESPONSE_FLAG,
1413 0,
1414 0,
1415 0,
1416 0,
1417 0,
1418 0,
1419 0,
1420 P_CONTACT_FLAG,
1421 0,
1422 0,
1423 P_FTC_ABANDON_COUNT,
1424 P_MESSAGE_PLAYED_COUNT
1425 );
1426 END ADD_DUMMY_SYS_RECORD;
1427
1431 --
1428
1429 -- HZH 04/27/2006 Add New Procedures for fixing a report problem
1430 -- as decribed in bug 5123333. Four Procedures are added this time:
1432 -- PROCEDURE UPDATE_ITEM_CC_TZS_COUNTS ()
1433 -- PROCEDURE CHECK_ALL_CAMPAIGN_CC_TZS ()
1434 -- PROCEDURE CHECK_ONE_CAMPAIGN_CC_TZS ()
1435 -- PROCEDURE CHECK_SCHEDULE_CC_TZS()
1436 --
1437 -- At least one of the last three procedures should be called each time
1438 -- when a report related to record counts is generated.
1439 --
1440 -- PROCEDURE UPDATE_ITEM_CC_TZS_COUNTS updates the Available and
1441 -- Unavailable counts in IEC_G_MKTG_ITEM_CC_TZS
1442 --
1443 PROCEDURE UPDATE_ITEM_CC_TZS_COUNTS (
1444 X_ITM_CC_TZ_ID IN NUMBER,
1445 X_STATUS OUT NOCOPY VARCHAR2
1446 ) is
1447 L_RECORD_AVAIL_COUNT NUMBER;
1448 L_RECORD_UNAVAIL_COUNT NUMBER;
1449 L_CALLABLE_FLAG VARCHAR2(1);
1450 L_LAST_CALLABLE_TIME DATE;
1451 BEGIN
1452
1453 X_STATUS := 'SUCCESS';
1454
1455 -- Get Unavailable Record Count First
1456 BEGIN
1457 SELECT NVL(COUNT(*), 0)
1458 INTO L_RECORD_UNAVAIL_COUNT
1459 FROM iec_g_return_entries a, IEC_G_MKTG_ITEM_CC_TZS C
1460 WHERE C.ITM_CC_TZ_ID = a.itm_cc_tz_id
1461 AND a.itm_cc_tz_id = X_ITM_CC_TZ_ID
1462 AND NVL(a.DO_NOT_USE_FLAG, 'N') = 'N'
1463 AND (C.CALLABLE_FLAG IS NULL
1464 OR C.CALLABLE_FLAG <> 'Y'
1465 OR C.LAST_CALLABLE_TIME < SYSDATE
1466 OR (C.CALLABLE_FLAG = 'Y' AND C.LAST_CALLABLE_TIME > SYSDATE
1467 AND a.callback_flag = 'Y' AND a.next_call_time > sysdate));
1468 EXCEPTION
1469 WHEN NO_DATA_FOUND THEN
1470 L_RECORD_UNAVAIL_COUNT := 0;
1471 WHEN OTHERS THEN
1472 L_RECORD_UNAVAIL_COUNT := 0;
1473 END;
1474
1475 L_CALLABLE_FLAG := NULL;
1476
1477 -- Get Available Record Count
1478 --
1479 -- Check the section callable status first.
1480 -- If the section is not callable set the
1481 -- available count to 0 immediately
1482 -- to avoid scanning iec_g_return_entries
1483
1484 BEGIN
1485 SELECT A.CALLABLE_FLAG, A.LAST_CALLABLE_TIME
1486 INTO L_CALLABLE_FLAG, L_LAST_CALLABLE_TIME
1487 FROM IEC_G_MKTG_ITEM_CC_TZS A
1488 WHERE A.ITM_CC_TZ_ID = X_ITM_CC_TZ_ID;
1489
1490 EXCEPTION
1491 WHEN NO_DATA_FOUND THEN
1492 L_CALLABLE_FLAG := 'N';
1493 WHEN OTHERS THEN
1494 L_CALLABLE_FLAG := 'N';
1495 END;
1496
1497 IF (L_CALLABLE_FLAG = NULL
1498 OR L_CALLABLE_FLAG = 'N'
1499 OR (L_CALLABLE_FLAG = 'Y'
1500 AND L_LAST_CALLABLE_TIME < SYSDATE))
1501 THEN
1502 -- Set the available count to 0 immediately
1503 -- if the section if not callable.
1504 --
1505 L_RECORD_AVAIL_COUNT := 0;
1506 ELSE
1507 --
1508 -- Section is callable, scan iec_g_return_entries
1509 -- All useable records are consider available except
1510 -- those records scheduled to be call back at
1511 -- a future time.
1512 --
1513 BEGIN
1514 SELECT NVL(COUNT(*), 0)
1515 INTO L_RECORD_AVAIL_COUNT
1516 FROM iec_g_return_entries a, IEC_G_MKTG_ITEM_CC_TZS C
1517 WHERE C.ITM_CC_TZ_ID = a.itm_cc_tz_id
1518 AND a.itm_cc_tz_id = X_ITM_CC_TZ_ID
1519 AND NVL(a.DO_NOT_USE_FLAG, 'N') = 'N'
1520 AND (C.CALLABLE_FLAG = 'Y' AND C.LAST_CALLABLE_TIME > SYSDATE
1521 AND (NVL(a.callback_flag, 'N') = 'N'
1522 OR a.next_call_time < sysdate));
1523
1524 EXCEPTION
1525 WHEN NO_DATA_FOUND THEN
1526 L_RECORD_AVAIL_COUNT := 0;
1527 WHEN OTHERS THEN
1528 L_RECORD_AVAIL_COUNT := 0;
1529 END;
1530 END IF;
1531
1532 -- Update IEC_G_MKTG_ITEM_CC_TZS with new Available and Unavailable counts.
1533 -- Some information updated by the servers are recorded for future reference
1534 -- in order to determine if a scan of records is needed.
1535 --
1536 BEGIN
1537 UPDATE IEC_G_MKTG_ITEM_CC_TZS
1538 SET
1539 ORG_CALLABLE_FLAG = CALLABLE_FLAG,
1540 ORG_LAST_UPDATE_DATE = LAST_UPDATE_DATE,
1541 ORG_LAST_CALLABLE_TIME = LAST_CALLABLE_TIME,
1542 COUNT_LAST_UPDATE_DATE = SYSDATE,
1543 ORG_RECORD_COUNT = RECORD_COUNT,
1544 RECORD_AVAILABLE = L_RECORD_AVAIL_COUNT,
1545 RECORD_UNAVAILABLE = L_RECORD_UNAVAIL_COUNT
1546 WHERE ITM_CC_TZ_ID = X_ITM_CC_TZ_ID;
1547
1548 EXCEPTION
1549 WHEN NO_DATA_FOUND THEN
1550 X_STATUS := 'FAILED';
1554 COMMIT;
1551 WHEN OTHERS THEN
1552 X_STATUS := 'FAILED';
1553 END;
1555 END UPDATE_ITEM_CC_TZS_COUNTS;
1556
1557 --
1558 -- PROCEDURE CHECK_ALL_CAMPAIGN_CC_TZS checks all campaings
1559 -- and updates the available and unavailable counts.
1560 --
1561 --
1562 PROCEDURE CHECK_ALL_CAMPAIGN_CC_TZS
1563 (
1564 X_CAMPAIGN_ID IN NUMBER
1565 )
1566 IS
1567 --------------------------------------------------------
1568 -- The following rules are set for the updates in order
1569 -- to keep the record counts recent and reduce the number of
1570 -- scans to avoid the performance problem, because each update
1571 -- needs to scan iec_g_return_entries.
1572 --
1573 -- The rules for checking all campaigns at the same time are
1574 -- the following:
1575 --
1576 -- 1) COUNT_LAST_UPDATE_DATE is NULL, which
1577 -- happens when the IEC_G_MKTG_ITEM_CC_TZS
1578 -- is updated the first time.
1579 --
1580 -- 2) IEC_G_MKTG_ITEM_CC_TZS has been updated
1581 -- by the Central server or the Dial server
1582 --
1583 -- 3) The CALLABLE_FLAG, LAST_CALLABLE_TIME
1584 -- or RECORD_COUNT has been changed since
1585 -- the last update.
1586 --
1587 -- 4) Available record count is greater than 0 when
1588 -- CC_TZ is not callable. This may happen when
1589 -- the LAST_CALLABLE_TIME is just past or
1590 -- the first time the record is checked since
1591 -- LAST_CALLABLE_TIME.
1592 --
1593 -- 5) Unavailable record count is greater than 0 when
1594 -- CC_TZ is callable and the counts have not
1595 -- been updated in the past 3 minutes. This is
1596 -- to check if any callback records become available.
1597 --
1598 -- 6) Any callable sections that are not checked for
1599 -- at least 5 minutes.
1600 --
1601 -- 7) All CC_TZS record counts will be rechecked
1602 -- if the last update was at least 1 day ago.
1603 --
1604 --
1605 cursor check_all_camp_cursor is
1606 SELECT A.ITM_CC_TZ_ID
1607 FROM IEC_G_MKTG_ITEM_CC_TZS A
1608 WHERE COUNT_LAST_UPDATE_DATE is NULL
1609 OR A.COUNT_LAST_UPDATE_DATE < A.LAST_UPDATE_DATE
1610 OR A.ORG_LAST_UPDATE_DATE <> A.LAST_UPDATE_DATE
1611 OR A.ORG_CALLABLE_FLAG <> A.CALLABLE_FLAG
1612 OR A.ORG_LAST_CALLABLE_TIME <> A.LAST_CALLABLE_TIME
1613 OR A.ORG_RECORD_COUNT <> A.RECORD_COUNT
1614 OR ((A.CALLABLE_FLAG <> 'Y' OR A.LAST_CALLABLE_TIME < SYSDATE)
1615 AND RECORD_AVAILABLE > 0 )
1616 OR (A.CALLABLE_FLAG = 'Y'
1617 AND A.LAST_CALLABLE_TIME > SYSDATE
1618 AND RECORD_UNAVAILABLE > 0
1619 AND A.COUNT_LAST_UPDATE_DATE < SYSDATE - 3/(24*60))
1620 OR (A.CALLABLE_FLAG = 'Y'
1621 AND A.LAST_CALLABLE_TIME > SYSDATE
1622 AND A.COUNT_LAST_UPDATE_DATE < SYSDATE - 5/(24*60))
1623 OR A.COUNT_LAST_UPDATE_DATE < sysdate - 1;
1624
1625 L_ITM_CC_TZ_ID NUMBER;
1626 L_STATUS VARCHAR2(30);
1627
1628 BEGIN
1629
1630 OPEN check_all_camp_cursor;
1631 LOOP
1632 FETCH check_all_camp_cursor
1633 INTO L_ITM_CC_TZ_ID;
1634 EXIT WHEN check_all_camp_cursor%notfound;
1635
1636 UPDATE_ITEM_CC_TZS_COUNTS (
1637 X_ITM_CC_TZ_ID => L_ITM_CC_TZ_ID,
1638 X_STATUS => L_STATUS
1639 );
1640
1641 IF L_STATUS <> 'SUCCESS'
1642 THEN
1643 EXIT;
1644 END IF;
1645 END LOOP;
1646
1647 CLOSE check_all_camp_cursor;
1648
1649 END CHECK_ALL_CAMPAIGN_CC_TZS;
1650
1651 --
1652 -- PROCEDURE CHECK_ONE_CAMPAIGN_CC_TZS checks and
1653 -- updates the available and unavailable counts
1654 -- for a given campaign.
1655 --
1656 PROCEDURE CHECK_ONE_CAMPAIGN_CC_TZS
1657 (
1658 X_CAMPAIGN_ID IN NUMBER
1659 )
1660 IS
1661 --------------------------------------------------------
1662 --
1663 -- The rules for checking one campaigns are
1664 -- similar to checking all campaings except
1665 -- rule 5:
1666 --
1667 -- 5) Unavailable record count is greater than 0 when
1668 -- CC_TZ is callable and the counts have not
1669 -- been updated in the past 1 minutes. This is
1670 -- to check if any callback records become available.
1671 --
1672
1673 cursor check_one_camp_cursor(X_CAMPAIGN_ID IN NUMBER) is
1674 SELECT A.ITM_CC_TZ_ID
1675 FROM IEC_G_MKTG_ITEM_CC_TZS A
1676 WHERE A.CAMPAIGN_SCHEDULE_ID in
1677 (SELECT UNIQUE C.SCHEDULE_ID
1678 FROM IEC_G_SCHEDULES_V c
1679 WHERE C.CAMPAIGN_ID = X_CAMPAIGN_ID)
1680 AND (A.COUNT_LAST_UPDATE_DATE is NULL
1681 OR A.COUNT_LAST_UPDATE_DATE < A.LAST_UPDATE_DATE
1682 OR A.ORG_LAST_UPDATE_DATE <> A.LAST_UPDATE_DATE
1683 OR A.ORG_CALLABLE_FLAG <> A.CALLABLE_FLAG
1684 OR A.ORG_LAST_CALLABLE_TIME <> A.LAST_CALLABLE_TIME
1685 OR A.ORG_RECORD_COUNT <> A.RECORD_COUNT
1686 OR ((A.CALLABLE_FLAG <> 'Y' OR A.LAST_CALLABLE_TIME < SYSDATE)
1687 AND RECORD_AVAILABLE > 0 )
1688 OR (A.CALLABLE_FLAG = 'Y'
1689 AND A.LAST_CALLABLE_TIME > SYSDATE
1690 AND RECORD_UNAVAILABLE > 0
1691 AND A.COUNT_LAST_UPDATE_DATE < SYSDATE - 1/(24*60))
1692 OR (A.CALLABLE_FLAG = 'Y'
1693 AND A.LAST_CALLABLE_TIME > SYSDATE
1694 AND A.COUNT_LAST_UPDATE_DATE < SYSDATE - 5/(24*60))
1695 OR A.COUNT_LAST_UPDATE_DATE < sysdate - 1);
1696
1697 L_ITM_CC_TZ_ID NUMBER;
1698 L_STATUS VARCHAR2(30);
1699
1700 BEGIN
1701
1705 INTO L_ITM_CC_TZ_ID;
1702 OPEN check_one_camp_cursor(X_CAMPAIGN_ID);
1703 LOOP
1704 FETCH check_one_camp_cursor
1706 EXIT WHEN check_one_camp_cursor%notfound;
1707
1708 UPDATE_ITEM_CC_TZS_COUNTS (
1709 X_ITM_CC_TZ_ID => L_ITM_CC_TZ_ID,
1710 X_STATUS => L_STATUS
1711 );
1712
1713 IF L_STATUS <> 'SUCCESS'
1714 THEN
1715 EXIT;
1716 END IF;
1717 END LOOP;
1718
1719 CLOSE check_one_camp_cursor;
1720
1721 END CHECK_ONE_CAMPAIGN_CC_TZS;
1722
1723 --
1724 -- PROCEDURE CHECK_SCHEDULE_CC_TZS checks and
1725 -- updates the available and unavailable counts
1726 -- for a given schedule.
1727 --
1728 PROCEDURE CHECK_SCHEDULE_CC_TZS
1729 (
1730 X_SCHEDULE_ID IN NUMBER
1731 )
1732 IS
1733 --------------------------------------------------------
1734 --
1735 -- The rules for checking one schedule are
1736 -- same as those for one campaign.
1737 --
1738
1739 cursor check_schedule_cursor(X_SCHEDULE_ID IN NUMBER) is
1740 SELECT A.ITM_CC_TZ_ID
1741 FROM IEC_G_MKTG_ITEM_CC_TZS A
1742 WHERE A.CAMPAIGN_SCHEDULE_ID = X_SCHEDULE_ID
1743 AND (A.COUNT_LAST_UPDATE_DATE is NULL
1744 OR A.COUNT_LAST_UPDATE_DATE < A.LAST_UPDATE_DATE
1745 OR A.ORG_LAST_UPDATE_DATE <> A.LAST_UPDATE_DATE
1746 OR A.ORG_CALLABLE_FLAG <> A.CALLABLE_FLAG
1747 OR A.ORG_LAST_CALLABLE_TIME <> A.LAST_CALLABLE_TIME
1748 OR A.ORG_RECORD_COUNT <> A.RECORD_COUNT
1749 OR ((A.CALLABLE_FLAG <> 'Y' OR A.LAST_CALLABLE_TIME < SYSDATE)
1750 AND RECORD_AVAILABLE > 0 )
1751 OR (A.CALLABLE_FLAG = 'Y'
1752 AND A.LAST_CALLABLE_TIME > SYSDATE
1753 AND RECORD_UNAVAILABLE > 0
1754 AND A.COUNT_LAST_UPDATE_DATE < SYSDATE - 1/(24*60))
1755 OR (A.CALLABLE_FLAG = 'Y'
1756 AND A.LAST_CALLABLE_TIME > SYSDATE
1757 AND A.COUNT_LAST_UPDATE_DATE < SYSDATE - 5/(24*60))
1758 OR A.COUNT_LAST_UPDATE_DATE < sysdate - 1);
1759
1760 L_ITM_CC_TZ_ID NUMBER;
1761 L_STATUS VARCHAR2(30);
1762
1763 BEGIN
1764
1765 OPEN check_schedule_cursor(X_SCHEDULE_ID);
1766 LOOP
1767 FETCH check_schedule_cursor
1768 INTO L_ITM_CC_TZ_ID;
1769 EXIT WHEN check_schedule_cursor%notfound;
1770
1771 UPDATE_ITEM_CC_TZS_COUNTS (
1772 X_ITM_CC_TZ_ID => L_ITM_CC_TZ_ID,
1773 X_STATUS => L_STATUS
1774 );
1775
1776 IF L_STATUS <> 'SUCCESS'
1777 THEN
1778 EXIT;
1779 END IF;
1780 END LOOP;
1781
1782 CLOSE check_schedule_cursor;
1783
1784 END CHECK_SCHEDULE_CC_TZS;
1785
1786 END IEC_REPORTS_UTIL_PVT;