APPS.CCT_QDE_PUB SQL Statements

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

Line: 8

FUNCTION UPDATE_CLASSIFICATION_COUNT
(
	p_commit	    IN  VARCHAR2,
  	p_agent_id 		IN 	NUMBER,
    p_item_type    IN NUMBER,
    p_classification IN VARCHAR2,
    p_count         IN NUMBER
)
RETURN NUMBER IS
ENTRY_NOT_FOUND EXCEPTION;

Line: 30

    update cct_qde_agent_queues set count = p_count
    where agent_id = p_agent_id
        and item_type = l_item_type
        and ((p_classification is null and classification is null) or (p_classification is not null
        and classification = p_classification)) ;

Line: 48

        insert into cct_qde_agent_queues
        (   agent_queue_id, agent_id, item_type, classification, count,
            created_by, creation_date,
            last_updated_by, last_update_date, last_update_login
        )
        values
        (
            cct_qde_agent_queues_s1.nextval, p_agent_id, l_item_type, p_classification, p_count,
            1, sysdate, 1, sysdate, 1
        );

Line: 68

END UPDATE_CLASSIFICATION_COUNT;

Line: 143

     insert into cct_qde_data (item_id,
        item_kvp,
        created_by, creation_date,
        last_updated_by, last_update_date, last_update_login)
     values (p_item_id,
        p_kvp,
        1, sysdate, 1, sysdate, 1);

Line: 152

     insert into cct_qde_route_result (item_id,
        item_type, classification, route_result,
        is_route_to_all, is_reroute, is_routed, start_time,
        created_by, creation_date,
        last_updated_by, last_update_date, last_update_login)
     values (p_item_id,
        p_item_type, p_classification, null,
        'N', 'N', 'N', sysdate,
        1, sysdate, 1, sysdate, 1);

Line: 207

PROCEDURE UPDATE_ROUTE_RESULT
(
    p_api_version     IN	NUMBER,
  	p_init_msg_list		IN	VARCHAR2,
	  p_commit	    	  IN  VARCHAR2,
  	p_item_id 		    IN 	NUMBER,
    p_item_type       IN NUMBER,
    p_classification  IN VARCHAR2,
    p_route_result    IN VARCHAR2,
    p_is_route_to_all IN VARCHAR2,
    p_is_reroute      IN VARCHAR2,
    p_kvp             IN  cct_keyvalue_varr,
 	  x_return_status		OUT NOCOPY	VARCHAR2,
	  x_msg_count		OUT NOCOPY	NUMBER,
    x_msg_data		OUT NOCOPY	VARCHAR2
)
IS
l_api_name			CONSTANT VARCHAR2(30)	:= 'UPDATE_ROUTE_RESULT';

Line: 273

    update cct_qde_route_result
    set classification = p_classification,
        route_result = l_route_result,
        is_route_to_all = p_is_route_to_all,
        is_reroute = p_is_reroute,
        is_routed = 'Y'
    where item_id = p_item_id;

Line: 281

    update cct_qde_data
    set item_kvp = p_kvp
    where item_id = p_item_id;

Line: 289

    select agent_id, raw_agent_index, item_type, classification
        from cct_qde_agent_vw
        where (item_type = p_item_type)
            and ((classification is null) or (classification is not null
            and classification = p_classification))
            and (is_get_work = 1)
            and ((p_is_route_to_all = 'Y') or
                 ((p_is_route_to_all = 'N') and (is_bit_set(raw_agent_index, l_route_result) = 0)));

Line: 309

          select agent_id into l_agent_id
          from cct_qde_agent
          where agent_id = r1_rec.agent_id and is_get_work = 1
          for update nowait;

Line: 324

          select item_id into l_item_id
          from cct_qde_route_result
          where item_id = p_item_id
          for update NOWAIT;

Line: 337

        select item_kvp into l_kvp from cct_qde_data
        where item_id = p_item_id;

Line: 345

        delete from cct_qde_route_result where item_id = p_item_id;

Line: 349

        delete from cct_qde_data where item_id = p_item_id;

Line: 351

        update cct_qde_agent
        set is_get_work = 0, item_type = null, classification = null
        where agent_id = r1_rec.agent_id;

Line: 396

            x_msg_data :='UPDATE_ROUTE_RESULT: CCT_ERROR'
                        || ' ErrorCode = ' || x_err_num
                        || ' ErrorMsg = ' || x_err_msg;

Line: 414

            x_msg_data :='UPDATE_ROUTE_RESULT: CCT_ERROR'
                        || ' ErrorCode = ' || x_err_num
                        || ' ErrorMsg = ' || x_err_msg;

Line: 424

            x_msg_data :='UPDATE_ROUTE_RESULT: CCT_ERROR'
                        || ' ErrorCode = ' || x_err_num
                        || ' ErrorMsg = ' || x_err_msg;

Line: 434

            x_msg_data :='UPDATE_ROUTE_RESULT: CCT_ERROR'
                        || ' ErrorCode = ' || x_err_num
                        || ' ErrorMsg = ' || x_err_msg;

Line: 438

END UPDATE_ROUTE_RESULT;

Line: 508

    select raw_agent_index into l_raw_agent_index from cct_qde_agent
    where agent_id = p_agent_id ;

Line: 511

    update cct_qde_agent
    set is_get_work = 1, item_type = p_item_type,
        classification = p_classification,
        gw_req_time = sysdate
    where agent_id = p_agent_id;

Line: 521

    select item_id, item_type, classification, route_result, is_route_to_all
        from cct_qde_route_result_vw
        where (item_type = p_item_type)
            and ((p_classification is null) or (p_classification is not null
            and classification = p_classification))
            and ((is_route_to_all = 'Y') or
                 ((is_route_to_all = 'N') and (is_bit_set(l_raw_agent_index,route_result) = 0)));

Line: 548

          select agent_id into l_agent_id from cct_qde_agent
          where agent_id = p_agent_id
          for update nowait;

Line: 559

          select item_id,item_type,classification
          into x_item_id, x_item_type, x_classification
          from cct_qde_route_result
          where item_id = r1_rec.item_id
          for update nowait;

Line: 576

        delete from cct_qde_route_result where item_id = r1_rec.item_id;

Line: 578

        select item_kvp into x_kvp from cct_qde_data
        where item_id = r1_rec.item_id;

Line: 580

        delete from cct_qde_data where item_id = r1_rec.item_id;

Line: 582

        update cct_qde_agent
        set is_get_work = 0, item_type = null, classification = null
        where agent_id = p_agent_id;

Line: 668

PROCEDURE UPDATE_AGENT_QUEUES
(
    p_api_version   IN	NUMBER,
  	p_init_msg_list	IN	VARCHAR2,
	p_commit	    IN  VARCHAR2,
  	p_agent_id 		IN 	NUMBER,
    p_item_type    IN NUMBER,
 	x_return_status	OUT NOCOPY	VARCHAR2,
	x_msg_count		OUT NOCOPY	NUMBER,
    x_msg_data		OUT NOCOPY	VARCHAR2
)
IS
l_api_name			CONSTANT VARCHAR2(30)	:= 'UPDATE_AGENT_QUEUES';

Line: 714

    SAVEPOINT UPDATE_QUEUE_COUNTS_SAVEPOINT;

Line: 722

    select raw_agent_index into l_raw_agent_index from cct_qde_agent
    where agent_id = p_agent_id;

Line: 730

    select item_id, item_type, classification, route_result, is_route_to_all
        from cct_qde_route_result_vw
        where (item_type = p_item_type)
        order by classification;

Line: 772

            l_return_val := UPDATE_CLASSIFICATION_COUNT(FND_API.G_TRUE,
                p_agent_id, p_item_type, l_prev_classification, l_prev_classification_count);

Line: 791

            rollback to UPDATE_QUEUE_COUNTS_SAVEPOINT;

Line: 803

        l_return_val := UPDATE_CLASSIFICATION_COUNT(FND_API.G_TRUE, p_agent_id,
            p_item_type,l_prev_classification, l_prev_classification_count);

Line: 810

    l_return_val := UPDATE_CLASSIFICATION_COUNT(FND_API.G_TRUE, p_agent_id,
        p_item_type,null, l_any_classification_count);

Line: 817

    select agent_queue_id , classification
        from cct_qde_agent_queues
        where (item_type = CCT_MEDIA_TYPES_PUB.GET_UWQ_MEDIA_TYPE_ID(p_item_type))
        and classification is not null
        and agent_id = p_agent_id ;

Line: 843

                delete from cct_qde_agent_queues
                where agent_queue_id = r2_rec.agent_queue_id;

Line: 867

            x_msg_data :='UPDATE_AGENT_QUEUES: CCT_NO_AGENT_FOUND'
                        || ' ErrorCode = ' || x_err_num
                        || ' ErrorMsg = ' || x_err_msg;

Line: 871

            rollback to UPDATE_QUEUE_COUNTS_SAVEPOINT;

Line: 878

            x_msg_data :='UPDATE_AGENT_QUEUES: CCT_ERROR'
                        || ' ErrorCode = ' || x_err_num
                        || ' ErrorMsg = ' || x_err_msg;

Line: 882

            rollback to UPDATE_QUEUE_COUNTS_SAVEPOINT;

Line: 889

            x_msg_data :='UPDATE_AGENT_QUEUES: CCT_ERROR'
                        || ' ErrorCode = ' || x_err_num
                        || ' ErrorMsg = ' || x_err_msg;

Line: 893

            rollback to UPDATE_QUEUE_COUNTS_SAVEPOINT;

Line: 900

            x_msg_data :='UPDATE_AGENT_QUEUES: CCT_ERROR'
                        || ' ErrorCode = ' || x_err_num
                        || ' ErrorMsg = ' || x_err_msg;

Line: 904

            rollback to UPDATE_QUEUE_COUNTS_SAVEPOINT;

Line: 905

END UPDATE_AGENT_QUEUES;

Line: 924

    select max(agent_index) into l_max_agent_index
    from cct_qde_agent;

Line: 935

    insert into cct_qde_agent
    (   agent_id, agent_index, raw_agent_index,
        created_by, creation_date,
        last_updated_by, last_update_date, last_update_login
    )
    values
    (
        p_agent_id, x_agent_index, x_raw_agent_index,
        1, sysdate, 1, sysdate, 1
    );

Line: 997

    select agent_index, raw_agent_index
    into x_agent_index, x_raw_agent_index from cct_qde_agent
    where agent_id = p_agent_id;