The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Obtain rule details for certain trasaction, and insert into temp tables */
PROCEDURE GET_DETAILS_TRANS ( p_trans_id IN VARCHAR2, p_input_var IN VARCHAR2
) IS
l_ssnid NUMBER;
select USERENV('SESSIONID') into l_ssnid from dual;
select distinct application_id, application_name into l_apps_id, l_apps_name
from ame_calling_apps
where transaction_type_id = p_trans_id
--Bug 4652277: Start
--and end_Date is null;
select distinct rule_id, start_date, end_date bulk collect
into l_rule_ids, l_date_0s, l_date_1s from ame_rule_usages
--Bug 4652277: Start
--where item_id = l_apps_id and (end_date is null OR end_date > sysdate);
select count(*) into l_count_num from ame_rules where rule_id = l_rule_ids(ith)
and description in ( select distinct rule_name from edr_amerule_input_var
where ame_trans_name = l_apps_name and input_name = p_input_var )
--Bug 4652277: Start
-- and (end_date is null or end_date > sysdate);
insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
( l_ssnid, p_trans_id, l_rule_ids(ith), l_rule_desc, l_rule_type, l_appr_type,
l_appr_desc, l_deft_use, l_date_0s(ith), l_date_1s(ith) );
insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id,
condition_id, condition_desc ) values ( l_ssnid, p_trans_id, l_rule_ids(ith),
l_cond_ids(jth), l_cond_desc(jth) );
select distinct description into l_rule_name from ame_rules where rule_id = l_rule_ids(ith);
insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
default_var, start_date, end_date) values ( l_ssnid, p_trans_id, l_rule_ids(ith),
l_rule_name, l_deft_use, l_date_0s(ith), l_date_1s(ith) );
/* insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id,
condition_id, condition_desc )
values ( l_ssnid, p_trans_id, l_rule_ids(ith), null, null ); */
select USERENV('SESSIONID') into l_ssnid from dual;
select transaction_type_id, application_id into l_trans_id, l_apps_id
from ame_calling_apps
where application_name = p_trans_name
--Bug 4652277: Start
--and end_Date is null;
select distinct rule_id into l_rule_id from ame_rules where description = p_rule_name
and rule_id in ( select distinct rule_id from ame_rule_usages where item_id = l_apps_id )
--Bug 4652277: Start
--and (end_date is null or end_date > sysdate);
select start_date, end_date into l_date_frm, l_date_end
from ame_rule_usages
where rule_id = l_rule_id
--Bug 4652277: Start
--and (end_date is null or end_date > sysdate);
insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
( l_ssnid, l_trans_id, l_rule_id, l_rule_desc, l_rule_type, l_appr_type,
l_appr_desc, 'N', l_date_frm, l_date_end );
insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
condition_desc ) values ( l_ssnid, l_trans_id, l_rule_id, l_cond_ids(jth), l_cond_desc(jth) );
insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name, default_var,
start_date, end_date) values
( l_ssnid, l_trans_id, l_rule_id, p_rule_name, 'N', l_date_frm, l_date_end );
select USERENV('SESSIONID') into l_ssnid from dual;
delete edr_rule_detail_temp where transaction_type_id = p_trans_id and SESSION_ID = l_ssnid;
delete edr_rule_condition_temp where transaction_type_id = p_trans_id and SESSION_ID = l_ssnid;
select USERENV('SESSIONID') into l_ssnid from dual;
delete edr_rule_detail_temp where transaction_type_id = p_trans_id
and RULE_ID = p_rule_id and SESSION_ID = l_ssnid;
delete edr_rule_condition_temp where transaction_type_id = p_trans_id
and RULE_ID = p_rule_id and SESSION_ID = l_ssnid;
select substr( p_trans_var, 1, instr(p_trans_var,'-')-1 ) into p_trans_id from dual;
select substr( p_trans_var, 1+instr(p_trans_var,'-') ) into p_input_var from dual;
select USERENV('SESSIONID') into l_ssnid from dual;
select distinct application_id, application_name into l_apps_id, l_apps_name
from ame_calling_apps
where transaction_type_id = p_trans_id
--Bug 4652277: Start
--AND end_date is null ;
select distinct rule_id, start_date, end_date bulk collect
into l_rule_ids, l_date_0s, l_date_1s from ame_rule_usages
where item_id = l_apps_id
--Bug 4652277: Start
--and (end_date is null OR end_date > sysdate);
select count(*) into l_count_num from ame_rules where rule_id = l_rule_ids(ith)
and description in ( select distinct rule_name from edr_amerule_input_var
where ame_trans_name = l_apps_name and input_name = p_input_var );
insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
( l_ssnid, p_trans_id, l_rule_ids(ith), l_rule_desc, l_rule_type, l_appr_type,
l_appr_desc, l_deft_use, l_date_0s(ith), l_date_1s(ith) );
insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
condition_desc ) values
( l_ssnid, p_trans_id, l_rule_ids(ith), l_cond_ids(jth), l_cond_desc(jth) );
select distinct description into l_rule_name from ame_rules where rule_id = l_rule_ids(ith);
insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name, default_var,
start_date, end_date) values ( l_ssnid, p_trans_id, l_rule_ids(ith),
l_rule_name, l_deft_use, l_date_0s(ith), l_date_1s(ith) );
/* insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
condition_desc ) values ( l_ssnid, p_trans_id, l_rule_ids(ith), null, null ); */
select substr( p_trans_rule, 1, instr(p_trans_rule,'-')-1 ) into p_trans_name from dual;
select instr(p_trans_rule, '-', 1+instr(p_trans_rule,'-')) into l_position from dual;
select TO_NUMBER( substr( p_trans_rule, 1+instr(p_trans_rule,'-'),
l_position - instr(p_trans_rule,'-') - 1 ),'999999999999.999999' ) into p_rule_id from dual;
select USERENV('SESSIONID') into l_ssnid from dual;
select transaction_type_id, application_id into l_trans_id, l_apps_id
from ame_calling_apps
where application_name = p_trans_name
--Bug 4652277: Start
--and end_date is null;
select distinct description into p_rule_name from ame_rules
where rule_id = l_rule_id
--Bug 4652277: Start
--and (end_date is null or end_date > sysdate);
select start_date, end_date into l_date_frm, l_date_end
from ame_rule_usages where rule_id = l_rule_id and item_id = l_apps_id
--Bug 4652277: Start
--and (end_date is null OR end_date > sysdate);
insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
( l_ssnid, l_trans_id, l_rule_id, l_rule_desc, l_rule_type, l_appr_type,
l_appr_desc, 'N', l_date_frm, l_date_end );
insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
condition_desc ) values ( l_ssnid, l_trans_id, l_rule_id, l_cond_ids(jth), l_cond_desc(jth) );
insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
default_var, start_date, end_date) values
( l_ssnid, l_trans_id, l_rule_id, p_rule_name, 'N', l_date_frm, l_date_end );
select distinct ame_trans_id, input_name into p_trans_id, p_input_var
from edr_ametran_input_var where tran_config_id = TO_NUMBER(p_trans_config_id,'999999999999.999999');
select USERENV('SESSIONID') into l_ssnid from dual;
select distinct application_id, application_name into l_apps_id, l_apps_name
from ame_calling_apps
where transaction_type_id = p_trans_id
--Bug 4652277: Start
--and end_date is null;
select distinct rule_id, start_date, end_date bulk collect
into l_rule_ids, l_date_0s, l_date_1s from ame_rule_usages
where item_id = l_apps_id
--Bug 4652277: Start
--and (end_date is null OR end_date > sysdate);
select count(*) into l_count_num from ame_rules where rule_id = l_rule_ids(ith)
and description in ( select distinct rule_name from edr_amerule_input_var
where ame_trans_name in (select distinct application_name from ame_calling_apps
where transaction_type_id = p_trans_id) and input_name = p_input_var );
insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
( l_ssnid, p_trans_id, l_rule_ids(ith), l_rule_desc, l_rule_type, l_appr_type,
l_appr_desc, l_deft_use, l_date_0s(ith), l_date_1s(ith) );
insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
condition_desc ) values
( l_ssnid, p_trans_id, l_rule_ids(ith), l_cond_ids(jth), l_cond_desc(jth) );
select distinct description into l_rule_name from ame_rules
where rule_id = l_rule_ids(ith) and (end_date is null or end_date > sysdate);
insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name, default_var,
start_date, end_date) values ( l_ssnid, p_trans_id, l_rule_ids(ith),
l_rule_name, l_deft_use, l_date_0s(ith), l_date_1s(ith) );
/* insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
condition_desc ) values ( l_ssnid, p_trans_id, l_rule_ids(ith), null, null ); */
select distinct ame_trans_name, rule_id into p_trans_name, p_rule_id
from edr_amerule_input_var where rule_config_id = TO_NUMBER(p_rule_config_id,'999999999999.999999');
select USERENV('SESSIONID') into l_ssnid from dual;
select transaction_type_id, application_id into l_trans_id, l_apps_id
from ame_calling_apps
where application_name = p_trans_name
--Bug 4652277: Start
--and end_date is null;
select distinct description into p_rule_name from ame_rules
where rule_id = l_rule_id
--Bug 4652277: Start
--and (end_date is null or end_date > sysdate);
select distinct start_date, end_date into l_date_frm, l_date_end
from ame_rule_usages where rule_id = l_rule_id and item_id = l_apps_id
--Bug 4652277: Start
--and (end_date is null OR end_date > sysdate);
insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
( l_ssnid, l_trans_id, l_rule_id, l_rule_desc, l_rule_type, l_appr_type,
l_appr_desc, 'N', l_date_frm, l_date_end );
insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
condition_desc ) values ( l_ssnid, l_trans_id, l_rule_id, l_cond_ids(jth), l_cond_desc(jth) );
insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
default_var, start_date, end_date) values
( l_ssnid, l_trans_id, l_rule_id, p_rule_name, 'N', l_date_frm, l_date_end );
SELECT USERENV('SESSIONID') INTO L_SSNID FROM DUAL;
SELECT DISTINCT APPLICATION_ID, APPLICATION_NAME INTO L_APPS_ID, L_APPS_NAME
FROM AME_CALLING_APPS_VL
WHERE TRANSACTION_TYPE_ID = P_TRANSACTION_ID
AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE, SYSDATE);
SELECT DISTINCT RULE_ID, START_DATE, END_DATE BULK COLLECT
INTO L_RULE_IDS, L_DATE_0S, L_DATE_1S FROM AME_RULE_USAGES
WHERE ITEM_ID = L_APPS_ID
AND SYSDATE <= NVL(END_DATE,SYSDATE);
SELECT COUNT(*)
INTO L_COUNT_NUM
FROM AME_RULES_VL AME_RULES,
EDR_AMERULE_INPUT_VAR RULE_VAR
WHERE AME_RULES.RULE_ID = L_RULE_IDS(ith)
AND AME_RULES.RULE_ID = RULE_VAR.RULE_ID
AND RULE_VAR.AME_TRANS_ID = P_TRANSACTION_ID
AND RULE_VAR.INPUT_NAME = P_VARIABLE_NAME
AND SYSDATE <= NVL(AME_RULES.END_DATE,SYSDATE);
INSERT INTO EDR_RULE_DETAIL_TEMP (SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, RULE_NAME,
RULE_TYPE, APPR_TYPE, APPR_DESC, DEFAULT_VAR, START_DATE, END_DATE )
VALUES (L_SSNID, P_TRANSACTION_ID, L_RULE_IDS(ith), L_RULE_DESC, L_RULE_TYPE, L_APPR_TYPE,
L_APPR_DESC, L_DEFT_USE, L_DATE_0S(ith), L_DATE_1S(ith) );
INSERT INTO EDR_RULE_CONDITION_TEMP( SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, CONDITION_ID,CONDITION_DESC )
VALUES ( L_SSNID, P_TRANSACTION_ID, L_RULE_IDS(ith), L_COND_IDS(jth), L_COND_DESC(jth) );
SELECT DISTINCT DESCRIPTION
INTO L_RULE_NAME FROM AME_RULES_VL
WHERE RULE_ID = L_RULE_IDS(ith) AND (END_DATE IS NULL OR END_DATE > SYSDATE);
INSERT INTO EDR_RULE_DETAIL_TEMP ( SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, RULE_NAME,
DEFAULT_VAR, START_DATE, END_DATE)
VALUES ( L_SSNID, P_TRANSACTION_ID, L_RULE_IDS(ith),L_RULE_NAME, L_DEFT_USE, L_DATE_0S(ith), L_DATE_1S(ith));
SELECT USERENV('SESSIONID') INTO L_SSNID FROM DUAL;
SELECT APPLICATION_ID INTO L_APPS_ID
FROM AME_CALLING_APPS_VL
WHERE TRANSACTION_TYPE_ID = P_TRANSACTION_ID
AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE, SYSDATE);
SELECT DISTINCT DESCRIPTION
INTO L_RULE_NAME FROM AME_RULES_VL
WHERE RULE_ID = P_RULE_ID
AND SYSDATE <= NVL(END_DATE,SYSDATE);
SELECT DISTINCT START_DATE, END_DATE INTO L_DATE_FRM, L_DATE_END
FROM AME_RULE_USAGES
WHERE RULE_ID = P_RULE_ID
AND ITEM_ID = L_APPS_ID
AND SYSDATE <= NVL(END_DATE,SYSDATE);
INSERT INTO EDR_RULE_DETAIL_TEMP (SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, RULE_NAME, RULE_TYPE, APPR_TYPE,
APPR_DESC, DEFAULT_VAR, START_DATE, END_DATE )
VALUES (L_SSNID, P_TRANSACTION_ID, P_RULE_ID, L_RULE_DESC, L_RULE_TYPE, L_APPR_TYPE,
L_APPR_DESC, 'N', L_DATE_FRM, L_DATE_END );
INSERT INTO EDR_RULE_CONDITION_TEMP ( SESSION_ID, TRANSACTION_TYPE_ID,
RULE_ID, CONDITION_ID,CONDITION_DESC)
VALUES (L_SSNID, P_TRANSACTION_ID, P_RULE_ID, L_COND_IDS(JTH), L_COND_DESC(JTH) );
INSERT INTO EDR_RULE_DETAIL_TEMP (SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, RULE_NAME,
DEFAULT_VAR, START_DATE, END_DATE)
VALUES(L_SSNID, P_TRANSACTION_ID, P_RULE_ID, L_RULE_NAME, 'N', L_DATE_FRM, L_DATE_END );
DELETE FROM EDR_AMERULE_INPUT_VAR RULE_VAR
WHERE RULE_VAR.AME_TRANS_ID = P_TRANSACTION_ID
AND RULE_VAR.RULE_ID NOT IN (SELECT AME_USAGES.RULE_ID
FROM AME_CALLING_APPS_VL AME_APPS,
AME_RULE_USAGES AME_USAGES
WHERE AME_APPS.TRANSACTION_TYPE_ID = P_TRANSACTION_ID
AND AME_APPS.APPLICATION_ID = AME_USAGES.ITEM_ID
AND SYSDATE <= NVL(AME_USAGES.END_DATE,SYSDATE));
SELECT COUNT(*) INTO L_COUNT FROM EDR_AMERULE_INPUT_VAR RULE_VAR
WHERE RULE_VAR.AME_TRANS_ID = P_TRANSACTION_ID
AND RULE_VAR.RULE_ID NOT IN (SELECT AME_USAGES.RULE_ID
FROM AME_CALLING_APPS_VL AME_APPS,
AME_RULE_USAGES AME_USAGES
WHERE AME_APPS.TRANSACTION_TYPE_ID = P_TRANSACTION_ID
AND AME_APPS.APPLICATION_ID = AME_USAGES.ITEM_ID
AND SYSDATE <= NVL(AME_USAGES.END_DATE,SYSDATE));