APPS.CCT_CLASS_ENGINE_UPG SQL Statements

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

Line: 7

    l_updated VARCHAR2(1) := 'N';

Line: 11

    l_deleted_count INTEGER := 0;

Line: 14

        SELECT Count(*) INTO l_upgraded
        FROM CCT_CLASSIFICATIONS
        WHERE CLASSIFICATION_VALUE_ID IS NOT NULL ;

Line: 29

  insert into cct_classification_values (classification_value_id, classification_value,seeded,
      LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,f_deletedflag)
  select cct_classification_values_s.nextval, classification,'N',
  	TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,
  	TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,a.f_deletedflag  from cct_classifications a
  where (upper(type)= 'LITERAL') AND classification_value_id is null
  	AND upper(classification) <> 'UNCLASSIFIED';

Line: 37

  insert into cct_classification_values (classification_value_id, classification_value,
		seeded,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
  select cct_classification_values_s.nextval, 'unClassified', 'Y',
  	TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,
  	TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1  from dual;

Line: 44

  insert into cct_plsql_functions (function_id, function, function_name, package,appdb,
  	dburl, dbdriver, return_type,seeded,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
	CREATED_BY)
  select cct_plsql_functions_s.nextval,substr(classification, instr(classification,'.',1)+1),
  classification,substr(classification,1, instr(classification,'.',1)-1),
 	DECODE(db_driver, nvl(db_driver,'Y'),'N','Y'), dburl, db_driver,  'occtClassification',
	'N', TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,TO_DATE ('01/01/1951', 'DD/MM/YYYY'),1
	from cct_classifications a
	where (upper(type)= 'DBPROC')
 		AND classification_value_id is null AND nvl(a.f_deletedflag,'N') <> 'D';

Line: 58

    SELECT classification_id, classification FROM cct_classifications
    WHERE type = 'LITERAL' order by f_deletedflag desc)
 LOOP
   l_loop_count := l_loop_count + 1;

Line: 62

   l_updated := 'N';

Line: 63

   select COUNT(*) into l_count from cct_classification_values where l_record.classification = classification_value
                                             and nvl(f_deletedflag,'N')<>'D';

Line: 65

   select COUNT(*) into l_deleted_count from cct_classification_values where l_record.classification = classification_value
                                             and nvl(f_deletedflag,'N')='D';

Line: 67

   FOR l_classValue_record IN (select classification_value_id, nvl(f_deletedflag,'N') f_deletedflag
  	  from cct_classification_values where l_record.classification = classification_value
           OR (upper(l_record.classification)='UNCLASSIFIED' AND classification_value='unClassified'))
   LOOP
     IF ((l_count >= 1 OR l_deleted_count >= 1 OR upper(l_record.classification)='UNCLASSIFIED') AND l_updated = 'N') THEN
     l_updated := 'Y';

Line: 76

       update cct_classifications a set a.classification_value_id = l_classValue_record.classification_value_id,
     	   a.IsEnabled = nvl(a.IsEnabled,'YES'),
         a.Priority = l_loop_count,
      	 a.Rule_Chaining = nvl(a.Rule_Chaining,'AND')
       where a.classification_id = l_record.classification_id;

Line: 88

   SELECT classification_id, classification FROM cct_classifications
   WHERE type = 'DBPROC' AND nvl(f_deletedflag,'N')<>'D')
LOOP
      l_loop_count := l_loop_count + 1;

Line: 92

 update cct_classifications a set a.classification_value_id = (select b.function_id
 		from cct_plsql_functions b
		where l_record.classification = b.package||decode(b.package, '','','.')||b.function
			AND nvl(b.f_deletedflag,'N')<>'D'), a.IsEnabled = nvl(a.IsEnabled,'YES'),
 a.Priority = l_loop_count,
 a.Rule_Chaining = nvl(a.Rule_Chaining,'AND')
 where a.classification_id = l_record.classification_id;

Line: 102

insert into cct_plsql_function_params (function_param_id,function_id,param,datatype,direction,
    sequence,value,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
    select cct_plsql_function_params_s.nextval,b.classification_value_id,a.param,
    		a.datatype,a.direction, a.sequence,a.value, TO_DATE ('01/01/1951', 'DD/MM/YYYY'),
		1,TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1
	from cct_route_params a, cct_classifications b
	where a.classification_id=b.classification_id AND upper(b.type)='DBPROC'
		AND nvl(a.f_deletedflag,'N')<>'D' AND nvl(b.f_deletedflag,'N')<>'D';

Line: 112

insert into cct_classification_sg_map (classification_sg_map_id, classification_id,
		server_group_id, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
	select cct_classification_sg_map_s.nextval, b.classification_id, a.server_group_id,
	    TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1
	from ieo_svr_groups a, cct_classifications b
	where a.group_group_id is null AND nvl(b.f_deletedflag,'N')<>'D';

Line: 120

insert into cct_classification_mt_map (classification_mt_map_id,
classification_id, media_type_uuid,LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY)
select cct_classification_mt_map_s.nextval, b.classification_id,
a.media_type_uuid, TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,
TO_DATE ('01/01/1951', 'DD/MM/YYYY'),
 1 from cct_supported_media_types a, cct_classifications b
where nvl(b.f_deletedflag,'N')<>'D';

Line: 137

    Select distinct Server_Group_id
    From cct_classification_sg_map
    where nvl(f_deletedflag,'N')<>'D';

Line: 142

    SELECT map.classification_id
    FROM cct_classification_sg_map map,cct_classifications class
    where map.server_group_id=p_server_Group_id
    and nvl(map.f_deletedflag,'N')<>'D'
    and map.priority is null
    and map.classification_id=class.classification_id
    and nvl(class.f_deletedflag,'N')<>'D'
    ORDER BY class.priority;

Line: 168

        update cct_classification_sg_map
        set priority = l_priority
        where classification_id = l_class_id;

Line: 195

    SELECT classification, classification_id FROM cct_classifications
    where (upper(type)= 'LITERAL') AND classification_value_id is null
     AND nvl(f_deletedflag,'N')='D' AND upper(classification) <> 'UNCLASSIFIED')
 LOOP
  select cct_classification_values_s.nextval into l_class_value_id from dual;

Line: 200

  insert into cct_classification_values (classification_value_id, classification_value,seeded,
      LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,f_deletedflag)
  select l_class_value_id, l_record.classification,'N',
     TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1, TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,'D'
     from dual;

Line: 206

   update cct_classifications a set a.classification_value_id = l_class_value_id
   where a.classification_id = l_record.classification_id;

Line: 224

    select classification_rule_id, operation from CCT_CLASSIFICATION_RULES
    where key in ('AccountCode','AccountNum','occtRoutePoint', 'ContractNum')
    AND operation in ('=','>','>=','<','<=','!=','DOESNOTEXISTSIN','BETWEEN')
    AND nvl(f_deletedflag,'N')<>'D'
    )
LOOP
--dbms_output.put_line('At beginning of For Loop');

Line: 238

 update CCT_CLASSIFICATION_RULES set operation=StringOperation
 where classification_rule_id = l_record.classification_rule_id;

Line: 245

  select route_param_id, operation from cct_route_params
  WHERE value IS NOT NULL AND direction is null and sequence is null
  AND param in ('AccountCode','AccountNum','occtRoutePoint', 'ContractNum')
  AND operation in ('=','>','>=','<','<=','!=','DOESNOTEXISTSIN','BETWEEN')
  AND nvl(f_deletedflag,'N')<>'D'
  )
LOOP
--dbms_output.put_line('At beginning of 2nd For Loop');

Line: 260

 update cct_route_params set operation=StringOperation
 where route_param_id = l_record.route_param_id;

Line: 266

update cct_interaction_keys set IVR_MAP_ENABLED='Y' where interaction_key='occtAgentID' ;