APPS.BIX_SUMMARY_PUB SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 17

  SELECT interaction_type,
         media_item_type,
         interaction_center_id,
         campaign_id,
         resource_id,
         TO_DATE(to_char(start_ts, 'DD-MM-YYYY-HH24'),'DD-MM-YYYY-HH24') hour,
         interaction_classification,
         SUM(ivr_time) ivr_time,
         SUM(route_time) route_time,
         SUM(party_wait_time) party_wait_time,
         SUM(talk_time) talk_time,
         SUM(wrap_time) wrap_time,
         SUM(idle_time) idle_time,
         SUM(DECODE(interaction_subtype,'TRANSFER',1,NULL)) transfers,
         SUM(DECODE(interaction_subtype,'ABANDON',1,NULL)) abandoned_count,
         SUM(DECODE(interaction_subtype,'ABANDON',party_wait_time,NULL)) wait_time_to_abandon,
         SUM(DECODE(first_interaction_resoln_flag,1,1,NULL)) first_interaction_resoln_count,
         SUM(DECODE(NVL(USER_ATTRIBUTE2,'F'),'T',1,NULL)) interactions_answered_live,
         SUM(preview_time) preview_time,
         SUM(non_productive_time) non_productive_time,
         SUM(response_time) response_time,
         SUM(resolution_time_internal) resolution_time_internal,
         SUM(resolution_time) resolution_time,
         SUM(DECODE(NVL(USER_ATTRIBUTE1,'F'),'T',1,NULL)) number_of_interactions
   FROM  bix_interactions
   GROUP BY interaction_type, media_item_type, interaction_center_id,
   campaign_id, resource_id, to_char(start_ts, 'DD-MM-YYYY-HH24'),
   interaction_classification;

Line: 53

    DELETE FROM bix_sum_grp_cls
    WHERE hour BETWEEN g_start_date AND g_end_date;

Line: 56

    DELETE FROM bix_sum_agt_cls
    WHERE hour BETWEEN g_start_date AND g_end_date;

Line: 61

     INSERT INTO BIX_SUM_GRP_CLS
     (
     sum_grp_cls_id,
     campaign_id,
     interaction_classification,
     media_item_type,
     interaction_center_id,
     resource_group_id,
     interaction_type,
     hour,
     transfers,
     abandoned_count,
     wait_time_to_abandon,
     interactions_answered_live,
     first_interaction_resoln_count,
     ivr_time,
     route_time,
     party_wait_time,
     speed_to_answer,
     talk_time,
     wrap_time,
     idle_time,
     preview_time,
     non_productive_time,
     response_time,
     resolution_time_internal,
     number_of_interactions
     )
     VALUES
      (
     BIX_SUM_GRP_CLS_S.nextval,
     call.campaign_id,
     call.interaction_classification,
     call.media_item_type,
     call.interaction_center_id,
     call.resource_id,
     call.interaction_type,
     call.hour,
     call.transfers,
     call.abandoned_count,
     call.wait_time_to_abandon,
     call.interactions_answered_live,
     call.first_interaction_resoln_count,
     call.ivr_time,
     call.route_time,
     call.party_wait_time,
     call.party_wait_time,
     call.talk_time,
     call.wrap_time,
     call.idle_time,
     call.preview_time,
     call.non_productive_time,
     call.response_time,
     call.resolution_time_internal,
     call.number_of_interactions
     );

Line: 118

     INSERT INTO BIX_SUM_AGT_CLS
     (
     sum_agt_cls_id,
     campaign_id,
     interaction_classification,
     media_item_type,
     interaction_center_id,
     resource_id,
     resource_group_id,
     interaction_type,
     hour,
     transfers,
     abandoned_count,
     wait_time_to_abandon,
     interactions_answered_live,
     first_interaction_resoln_count,
     ivr_time,
     route_time,
     party_wait_time,
     speed_to_answer_time,
     talk_time,
     wrap_time,
     idle_time,
     preview_time,
     non_productive_time,
     response_time,
     resolution_time_internal,
     number_of_interactions
     )
     VALUES
      (
     BIX_SUM_AGT_CLS_S.nextval,
     call.campaign_id,
     call.interaction_classification,
     call.media_item_type,
     call.interaction_center_id,
     call.resource_id,
     call.resource_id,
     call.interaction_type,
     call.hour,
     call.transfers,
     call.abandoned_count,
     call.wait_time_to_abandon,
     call.interactions_answered_live,
     call.first_interaction_resoln_count,
     call.ivr_time,
     call.route_time,
     call.party_wait_time,
     call.party_wait_time,
     call.talk_time,
     call.wrap_time,
     call.idle_time,
     call.preview_time,
     call.non_productive_time,
     call.response_time,
     call.resolution_time_internal,
     call.number_of_interactions
     );

Line: 206

  SELECT COUNT(*) INTO l_count
  FROM BIX_INTERACTIONS;

Line: 209

/* Since we capture data in summary tables at 1 hour level. We need delete the complete bucket
   and re collect again
*/

  --dbms_output.put_line('Count of rows in interactions : ' || l_count);

Line: 216

     SELECT TO_DATE(TO_CHAR(MIN(start_ts),'DD/MM/YYYY HH24'),'DD/MM/YYYY HH24')
            INTO g_start_date
     FROM  bix_interactions;

Line: 219

     SELECT TO_DATE(TO_CHAR(MAX(start_ts),'DD/MM/YYYY HH24'),'DD/MM/YYYY HH24')
            INTO g_end_date
     FROM  bix_interactions;