APPS.BIX_CCI_COLLECT SQL Statements

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

Line: 12

  SELECT media_id,
         media_item_type,
         media_item_ref,
         media_data,
         DECODE(media_item_type,'Telephony, Inbound','INBOUND',NULL) direction,
         start_date_time,
         end_date_time,
         source_id,
         DECODE(media_abandon_flag,'Y','ABANDON',
			 DECODE(media_transferred_flag,'Y','TRANSFER',NULL)) interaction_subtype
  FROM   jtf_ih_media_items
  WHERE  start_date_time  between p_start_date and p_end_date
  and active = 'N';

Line: 27

  SELECT
      ih_milcs.DURATION,
      ih_milcs_ty.MILCS_CODE,
	 ih_milcs.HANDLER_ID,
	 ih_milcs.RESOURCE_ID,
	 DECODE(ih_milcs_ty.MILCS_CODE,'IN_QUEUE',1,'WITH_AGENT',2) segment_order
  FROM
      JTF_IH_MEDIA_ITEMS           ih_mitem,
      JTF_IH_MEDIA_ITEM_LC_SEGS    ih_milcs,
      JTF_IH_MEDIA_ITM_LC_SEG_TYS  ih_milcs_ty
    WHERE
      (ih_mitem.MEDIA_ID = cv_ih_mi_id) and
      (ih_mitem.MEDIA_ID = ih_milcs.MEDIA_ID) and
      (ih_milcs.MILCS_TYPE_ID = ih_milcs_ty.MILCS_TYPE_ID)
	 order by segment_order;

Line: 44

  SELECT sum((act.end_date_time - act.start_date_time) * 24 * 3600) wrap_time
  FROM   jtf_ih_activities act, jtf_ih_action_items_vl actitems
  WHERE  act.media_id = p_media_id
  AND    act.action_item_id = actitems.action_item_id
  AND    actitems.action_item = 'Wrapup';

Line: 52

  SELECT sum((int.end_date_time - seg.end_date_time) * 24 * 3600) wrap_time
  FROM   jtf_ih_interactions int, jtf_ih_media_item_lc_segs seg, jtf_ih_media_itm_lc_seg_tys tys
  WHERE  int.productive_time_amount = p_media_id
  AND    int.resource_id = p_resource_id
  AND    seg.media_id = p_media_id
  AND    seg.resource_id = p_resource_id
  AND    tys.milcs_type_id = seg.milcs_type_id
  AND    tys.milcs_code = 'WITH_AGENT'
  AND    int.end_date_time > seg.end_date_time;

Line: 64

	   DELETE from BIX_INTERACTIONS;

Line: 72

         INSERT INTO BIX_INTERACTIONS
   		(
       	INTERACTIONS_ID,
      	START_TS,
         	COMPLETED_TS,
         	MEDIA_ITEM_TYPE,
         	MEDIA_ITEM_REF,
         	INTERACTION_TYPE,
         	INTERACTION_SUBTYPE,
	    	RESOURCE_ID,
	    	INTERACTION_CENTER_ID
        	)
       	VALUES
      	(
      	act.media_id,
          act.START_DATE_TIME,
     	act.END_DATE_TIME,
          act.MEDIA_ITEM_TYPE,
      	act.MEDIA_ITEM_REF,
      	act.DIRECTION,
      	act.INTERACTION_SUBTYPE,
      	'-1',
 	act.source_id
      	);

Line: 104

            UPDATE BIX_INTERACTIONS
            SET IVR_TIME = milcs.DURATION
            WHERE INTERACTIONS_ID = act.media_id
		  AND resource_id = -1;

Line: 111

            UPDATE BIX_INTERACTIONS
            SET ROUTE_TIME = milcs.DURATION
            WHERE INTERACTIONS_ID = act.media_id
		  AND resource_id = -1;

Line: 120

            UPDATE BIX_INTERACTIONS
            SET PARTY_WAIT_TIME = milcs.DURATION,USER_ATTRIBUTE1 = 'T'
            WHERE INTERACTIONS_ID = act.media_id
		  AND  resource_id = -1;

Line: 144

         INSERT INTO BIX_INTERACTIONS
         (
         INTERACTIONS_ID,
         START_TS,
         COMPLETED_TS,
         MEDIA_ITEM_TYPE,
         MEDIA_ITEM_REF,
         INTERACTION_TYPE,
	 RESOURCE_ID,
	 HANDLER_ID,
	 INTERACTION_CENTER_ID,
	 WRAP_TIME,
	 TALK_TIME,
	 USER_ATTRIBUTE2
         )
         VALUES
         (
      	act.media_id,
      	act.START_DATE_TIME,
      	act.END_DATE_TIME,
      	act.MEDIA_ITEM_TYPE,
      	act.MEDIA_ITEM_REF,
      	act.DIRECTION,
      	milcs.resource_id,
 	milcs.HANDLER_ID,
 	act.source_id,
 	l_wrap_time,
 	milcs.duration,
	DECODE(l_queue_segment,'T','T')
          );