APPS.BIX_DM_SESSIONINFO_SUMMARY_PKG SQL Statements

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

Line: 9

g_insert_count        NUMBER := 0;

Line: 10

g_delete_count        NUMBER := 0;

Line: 11

g_update_count        NUMBER := 0;

Line: 44

PROCEDURE insert_log_table
IS
  l_proc_name VARCHAR2(20) := 'INSERT_LOG_TABLE';

Line: 49

/* Insert status into log table */
   INSERT INTO BIX_DM_COLLECT_LOG
        (
        collect_id,
        collect_concur_id,
        object_name,
        object_type,
        run_start_date,
        run_end_date,
        collect_start_date,
        collect_end_date,
        collect_status,
        collect_excep_mesg,
        rows_deleted,
        rows_inserted,
        rows_updated,
        last_update_date,
        last_updated_by,
        creation_date,
        created_by,
        last_update_login,
        request_id,
        program_application_id,
        program_id,
        program_update_date
        )
  VALUES
        (
        BIX_DM_COLLECT_LOG_S.NEXTVAL,
        null,
        g_table_name,
        'TABLE',
        g_run_start_date,
        g_run_end_date,
        g_collect_start_date,
        g_collect_end_date,
        g_status,
        g_error_msg,
        g_delete_count,
        g_insert_count,
        g_update_count,
        sysdate,
        g_user_id,
        sysdate,
        g_user_id,
        g_login_id,
        g_request_id,
        g_program_appl_id,
        g_program_id,
        sysdate
        );

Line: 103

END insert_log_table;

Line: 106

PROCEDURE delete_in_chunks(p_table_name    IN VARCHAR2,
                           p_type          IN NUMBER,
                           p_rows_deleted OUT nocopy NUMBER)
IS
  l_rows_deleted       NUMBER;

Line: 111

  l_proc_name VARCHAR2(20) := 'DELETE_IN_CHUNKS';

Line: 115

  l_rows_deleted := 0;

Line: 120

	 	DELETE FROM BIX_DM_AGENT_SESSION_SUM
	     WHERE period_start_date_time between g_rounded_collect_start_date AND g_rounded_collect_end_date
	     AND rownum <= g_commit_chunk_size;

Line: 124

	     DELETE FROM BIX_DM_GROUP_SESSION_SUM
	     WHERE period_start_date_time between g_rounded_collect_start_date AND g_rounded_collect_end_date
	     AND rownum <= g_commit_chunk_size;

Line: 128

	     DELETE FROM BIX_DM_AGENT_SESSION_SUM
	     WHERE request_id =  g_request_id
	     AND rownum <= g_commit_chunk_size;

Line: 132

	     DELETE FROM BIX_DM_GROUP_SESSION_SUM
          WHERE request_id =  g_request_id
	     AND rownum <= g_commit_chunk_size;

Line: 137

      l_rows_deleted := l_rows_deleted + SQL%ROWCOUNT;

Line: 147

  g_message := 'Deleted ' || l_rows_deleted || ' rows from table '
					 || p_table_name ;

Line: 150

  p_rows_deleted := l_rows_deleted;

Line: 155

END delete_in_chunks;

Line: 158

PROCEDURE insert_login_row(p_resource_id IN NUMBER,
                           p_start_date  IN DATE,
                           p_secs        IN NUMBER,
                           p_agent_cost  IN NUMBER,
                           p_ddl_type OUT nocopy VARCHAR2)
IS
   l_proc_name VARCHAR2(20) := 'INSERT_LOGIN_ROW';

Line: 170

        SELECT 'Y'
		INTO l_exists
		FROM BIX_DM_AGENT_SESSION_SUM
         WHERE resource_id = p_resource_id
		 AND period_start_date_time = p_start_date;

Line: 184

        INSERT INTO BIX_DM_AGENT_SESSION_SUM
        ( agent_session_summary_id
         ,resource_id
         ,period_start_date
         ,period_start_time
         ,period_start_date_time
         ,last_update_date
         ,last_updated_by
         ,creation_date
         ,created_by
         ,last_update_login
         ,login_time
         ,available_time
	    ,idle_time
         ,agent_cost
         ,request_id
         ,program_application_id
         ,program_id
         ,program_update_date
        ) VALUES
        ( BIX_DM_AGENT_SESSION_SUM_S.NEXTVAL
         ,p_resource_id
         ,TRUNC(p_start_date)
         ,TO_CHAR(p_start_date,'HH24:MI')
         ,p_start_date
         ,SYSDATE
         ,g_user_id
         ,SYSDATE
         ,g_user_id
         ,g_user_id
         ,p_secs
         ,0
	    ,0
         ,(p_secs/3600)* p_agent_cost
         ,g_request_id
         ,g_program_appl_id
         ,g_program_id
         ,SYSDATE );

Line: 224

	  UPDATE BIX_DM_AGENT_SESSION_SUM
          SET login_time = login_time + p_secs,
		    agent_cost = agent_cost + ((p_secs/3600) * p_agent_cost ),
		    last_update_date = SYSDATE,
		    last_updated_by  = g_user_id,
		    program_update_date = SYSDATE
	   WHERE resource_id = p_resource_id
	     AND period_start_date_time = p_start_date;

Line: 237

END insert_login_row;

Line: 263

  SELECT begin_date_time,end_date_time
  FROM ieu_sh_activities
  WHERE session_id = l_session_id
  AND   activity_type_code = 'MEDIA_CYCLE'
  ORDER BY begin_date_time;

Line: 302

	 SELECT TO_CHAR(l_begin_date,'YYYY/MM/DD')||
               LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0') ||
               DECODE(SIGN(TO_NUMBER(TO_CHAR(l_begin_date,'MI'))-29),
			   0,'00',1,'30',-1,'00')
        INTO l_temp FROM DUAL;

Line: 327

                UPDATE BIX_DM_AGENT_SESSION_SUM
            	   SET idle_time = nvl(idle_time,0)+l_secs,
		            last_update_date = SYSDATE,
		            last_updated_by  = g_user_id,
				  program_update_date = SYSDATE
	           WHERE resource_id = l_resource_id
	             AND period_start_date_time = l_period_start;

Line: 369

	 SELECT TO_CHAR(l_begin_date,'YYYY/MM/DD')||
               LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0') ||
               DECODE(SIGN(TO_NUMBER(TO_CHAR(l_begin_date,'MI'))-29),
			   0,'00',1,'30',-1,'00')
        INTO l_temp FROM DUAL;

Line: 394

                UPDATE BIX_DM_AGENT_SESSION_SUM
            	   SET idle_time = nvl(idle_time,0)+l_secs,
		            last_update_date = SYSDATE,
		            last_updated_by  = g_user_id,
				  program_update_date = SYSDATE
	           WHERE resource_id = l_resource_id
	             AND period_start_date_time = l_period_start;

Line: 415

  g_update_count := l_row_count;

Line: 416

  g_message := 'Finished collecting agent idle time : Updated ' ||
			   l_row_count || ' rows in BIM_DM_AGENT_SESSION_SUM' ;

Line: 441

  SELECT iss.session_id session_id,
         iss.resource_id     resource_id,
	   iss.begin_date_time begin_date_time,
         iss.end_date_time   end_date_time
  FROM   ieu_sh_sessions iss
  WHERE  iss.application_id = 696
    AND  iss.begin_date_time <=  g_rounded_collect_end_date
    AND (iss.end_date_time >= g_rounded_collect_start_date
		 OR iss.end_date_time is NULL ) ;

Line: 495

	   SELECT TO_CHAR(l_begin_date,'YYYY/MM/DD')||
                       LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')||
                DECODE(SIGN(TO_NUMBER(TO_CHAR(l_begin_date,'MI'))-29),
						 0,'00',1,'30',-1,'00')
          INTO l_temp FROM DUAL;

Line: 519

            INSERT_LOGIN_ROW(login_time.resource_id,l_period_start,l_secs,
					    l_agent_cost, l_ddl_type);

Line: 542

  g_message :=  'Finished collecting agent login time : Inserted ' ||
				l_row_count || ' rows into BIM_DM_AGENT_SESSION_SUM' ;

Line: 546

  g_insert_count := l_row_count;

Line: 567

  SELECT iss.resource_id       resource_id,
         isa.begin_date_time   begin_date_time,
         isa.deliver_date_time end_date_time
    FROM ieu_sh_sessions iss,
	    ieu_sh_activities isa
   WHERE isa.begin_date_time <=  g_rounded_collect_end_date
     AND (isa.deliver_date_time >= g_rounded_collect_start_date
		OR (isa.deliver_date_time is NULL AND isa.end_date_time IS NULL)
	    )
     AND iss.application_id  = 696
     AND iss.session_id  = isa.session_id
     AND isa.activity_type_code = 'MEDIA'
	   UNION ALL
  SELECT iss.resource_id     resource_id,
         isa.begin_date_time begin_date_time,
         isa.end_date_time   end_date_time
    FROM ieu_sh_sessions iss,
	    ieu_sh_activities isa
   WHERE isa.begin_date_time <=  g_rounded_collect_end_date
     AND isa.end_date_time >= g_rounded_collect_start_date
     AND isa.deliver_date_time IS NULL
     AND iss.application_id  = 696
     AND iss.session_id  = isa.session_id
     AND isa.activity_type_code = 'MEDIA';

Line: 618

	 SELECT TO_CHAR(l_begin_date,'YYYY/MM/DD')||
               LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0') ||
               DECODE(SIGN(TO_NUMBER(TO_CHAR(l_begin_date,'MI'))-29),
			   0,'00',1,'30',-1,'00')
        INTO l_temp FROM DUAL;

Line: 643

                UPDATE BIX_DM_AGENT_SESSION_SUM
            	   SET available_time = nvl(available_time,0)+l_secs,
		            last_update_date = SYSDATE,
		            last_updated_by  = g_user_id,
				  program_update_date = SYSDATE
	           WHERE resource_id = avail_time.resource_id
	             AND period_start_date_time = l_period_start;

Line: 664

  g_update_count := l_row_count;

Line: 665

  g_message := 'Finished collecting agent available time : Updated ' ||
			   l_row_count || ' rows in BIM_DM_AGENT_SESSION_SUM' ;

Line: 682

    SELECT group_denorm.parent_group_id group_id,
           agt_sum.period_start_date,
           agt_sum.period_start_time,
           agt_sum.period_start_date_time,
           SUM(agt_sum.available_time) available_time,
           SUM(agt_sum.login_time) login_time,
		 SUM(agt_sum.idle_time) idle_time,
           SUM(agt_sum.agent_cost) group_cost
      FROM bix_dm_agent_session_sum agt_sum,
           jtf_rs_group_members     groups,
           jtf_rs_groups_denorm     group_denorm
WHERE agt_sum.period_start_date_time  BETWEEN g_rounded_collect_start_date
                                              AND g_rounded_collect_end_date
AND   agt_sum.resource_id = groups.resource_id
AND   groups.group_id    = group_denorm.group_id
AND   NVL(groups.delete_flag,'N') <> 'Y'
AND   agt_sum.period_start_date_time BETWEEN
      NVL(group_denorm.start_date_active,agt_sum.period_start_date_time)
      AND NVL(group_denorm.end_date_active,SYSDATE)
AND   groups.group_member_id =
       (select max(mem1.group_member_id)
        from jtf_rs_group_members mem1
        where mem1.group_id in
               (select den1.group_id
                from   jtf_rs_groups_denorm den1
                where  den1.parent_group_id = group_denorm.parent_group_id
                AND    agt_sum.period_start_date_time BETWEEN
                       NVL(den1.start_date_active,agt_sum.period_start_date_time)
                AND NVL(den1.end_date_active,SYSDATE)
               )
         AND mem1.resource_id = groups.resource_id
         AND nvl(mem1.delete_flag,'N') <> 'Y'
      )
GROUP BY group_denorm.parent_group_id,
           agt_sum.period_start_date_time,
           agt_sum.period_start_date,
           agt_sum.period_start_time;

Line: 721

    g_insert_count       := 0;

Line: 722

    g_delete_count       := 0;

Line: 723

    g_update_count       := 0;

Line: 727

    /* Delete data between these dates and re-compute */
    delete_in_chunks( 'BIX_DM_GROUP_SESSION_SUM', 1, g_delete_count);

Line: 730

    g_message := 'Start Inserting  rows into BIX_DM_GROUP_SESSION_SUM table';

Line: 735

	  INSERT INTO BIX_DM_GROUP_SESSION_SUM
	  ( group_session_summary_id
         ,group_id
         ,period_start_date
         ,period_start_time
         ,period_start_date_time
         ,last_update_date
         ,last_updated_by
         ,creation_date
         ,created_by
         ,last_update_login
         ,available_time
         ,login_time
	    ,idle_time
         ,group_cost
         ,request_id
         ,program_application_id
         ,program_id
         ,program_update_date )
       VALUES (
 	     BIX_DM_GROUP_CALL_SUM_S.NEXTVAL
          ,groupinfo.group_id
     	,groupinfo.period_start_date
	     ,groupinfo.period_start_time
	     ,groupinfo.period_start_date_time
	     ,SYSDATE
	     ,g_user_id
	     ,SYSDATE
	     ,g_user_id
	     ,g_user_id
	     ,groupinfo.available_time
	     ,groupinfo.login_time
		,groupinfo.idle_time
          ,groupinfo.group_cost
	     ,g_request_id
	     ,g_program_appl_id
	     ,g_program_id
	     ,SYSDATE
         );

Line: 782

    g_message :=  'Finished inserting rows into BIX_DM_GROUP_SESSION_SUM. Inserted ' || l_row_count || ' rows ';

Line: 785

    g_insert_count := l_row_count;

Line: 789

    insert_log_table;

Line: 803

             delete_in_chunks('BIX_DM_GROUP_SESSION_SUM', 2, l_row_count);

Line: 816

          delete_in_chunks('BIX_DM_AGENT_SESSION_SUM', 2, l_row_count);

Line: 825

       UPDATE BIX_DM_COLLECT_LOG
          SET collect_status  = 'FAILURE',
              rows_inserted = 0,
              rows_updated  = 0,
              collect_excep_mesg = g_error_msg
        WHERE request_id = g_request_id
          AND object_name = 'BIX_DM_AGENT_SESSION_SUM';

Line: 835

        insert_log_table;

Line: 844

  l_delete_count  NUMBER   := 0;

Line: 846

    g_insert_count       := 0;

Line: 847

    g_delete_count       := 0;

Line: 848

    g_update_count       := 0;

Line: 852

   /* Delete data between these dates and re-compute */
   delete_in_chunks( 'BIX_DM_AGENT_SESSION_SUM', 1, g_delete_count);

Line: 862

   /* Insert the status into BIX_DM_COLLECT_LOG table */
   g_run_end_date := sysdate;

Line: 865

   insert_log_table;

Line: 867

   COMMIT;  --commit after all rows are inserted in bix_dm_agent_session_sum

Line: 879

             delete_in_chunks('BIX_DM_AGENT_SESSION_SUM', 2, l_delete_count);

Line: 890

        g_insert_count := 0;

Line: 891

        g_update_count := 0;

Line: 894

        insert_log_table;

Line: 918

   IF FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')
   THEN
      g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));

Line: 948

   SELECT TO_DATE(TO_CHAR(g_collect_start_date,'YYYY/MM/DD')||
	LPAD(TO_CHAR(g_collect_start_date,'HH24:'),3,'0')||
	DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_start_date,'MI'))-29),
		   0,'00:00',1,'30:00',-1,'00:00'), 'YYYY/MM/DDHH24:MI:SS')
    INTO g_rounded_collect_start_date
    FROM DUAL;

Line: 958

    SELECT TO_DATE(
	TO_CHAR(g_collect_end_date,'YYYY/MM/DD')||
	LPAD(TO_CHAR(g_collect_end_date,'HH24:'),3,'0')||
	DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_end_date,'MI'))-29),
		0,'29:59',1,'59:59',-1,'29:59'), 'YYYY/MM/DDHH24:MI:SS')
     INTO g_rounded_collect_end_date
     FROM DUAL;

Line: 997

     Success log tables were already inserted, so just commit
    */
    COMMIT;