The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_updated VARCHAR2(1) := 'N';
l_deleted_count INTEGER := 0;
SELECT Count(*) INTO l_upgraded
FROM CCT_CLASSIFICATIONS
WHERE CLASSIFICATION_VALUE_ID IS NOT NULL ;
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';
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;
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';
SELECT classification_id, classification FROM cct_classifications
WHERE type = 'LITERAL' order by f_deletedflag desc)
LOOP
l_loop_count := l_loop_count + 1;
l_updated := 'N';
select COUNT(*) into l_count from cct_classification_values where l_record.classification = classification_value
and nvl(f_deletedflag,'N')<>'D';
select COUNT(*) into l_deleted_count from cct_classification_values where l_record.classification = classification_value
and nvl(f_deletedflag,'N')='D';
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';
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;
SELECT classification_id, classification FROM cct_classifications
WHERE type = 'DBPROC' AND nvl(f_deletedflag,'N')<>'D')
LOOP
l_loop_count := l_loop_count + 1;
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;
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';
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';
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';
Select distinct Server_Group_id
From cct_classification_sg_map
where nvl(f_deletedflag,'N')<>'D';
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;
update cct_classification_sg_map
set priority = l_priority
where classification_id = l_class_id;
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;
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;
update cct_classifications a set a.classification_value_id = l_class_value_id
where a.classification_id = l_record.classification_id;
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');
update CCT_CLASSIFICATION_RULES set operation=StringOperation
where classification_rule_id = l_record.classification_rule_id;
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');
update cct_route_params set operation=StringOperation
where route_param_id = l_record.route_param_id;
update cct_interaction_keys set IVR_MAP_ENABLED='Y' where interaction_key='occtAgentID' ;