[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT last_run_date
into l_last_run_date
FROM rci_dr_inc
WHERE fact_name = p_fact_name ;
select * from rci_compl_env_chg_summ_f;
select distinct fin_certification_id,organization_id,process_id
from rci_compl_env_chg_summ_f
where organization_id is not null
and process_id is not null;
DELETE FROM rci_dr_inc where fact_name = 'RCI_COMPL_ENV_CHG_SUMM_F';
insert into rci_compl_env_chg_summ_f(
fin_certification_id,
cert_status,
cert_type,
cert_period_name,
cert_period_set_name,
statement_group_id,
financial_statement_id,
financial_item_id,
account_group_id,
natural_account_id,
organization_id,
process_id,
revision_number,
latest_appr_revision_number,
NEW_REVISIONS_SINCE,
REVISED_PROCESS,
Total_Risks,
Num_Changed_Risks,
Total_Controls,
Num_Changed_Controls,
period_year,
period_num,
quarter_num,
ent_period_id,
ent_qtr_id,
ent_year_id,
report_date_julian,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login) /*02.02.2006 npanandi: added distinct below*/
(select distinct sc.fin_certification_id,
b.certification_status,
b.certification_type,
b.certification_period_name,
b.certification_period_set_name,
/*02.02.2006 npanandi: not using the below columns for performance reasons*/
/*sc.statement_group_id,*/ -1000,
/*sc.financial_statement_id,*/ -1000,
/*sc.financial_item_id,*/ -1000,
/*sc.account_group_id,*/ -1000,
sc.natural_account_id,
sc.organization_id,
sc.process_id,
nvl(peval.revision_number,1),
0, 0, 0, 0, 0, 0, 0,
agpv.period_year,
agpv.period_num,
agpv.quarter_num,
to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
agpv.period_year,
to_number(to_char(agpv.end_date,'J')),
sysdate,
G_USER_ID,
sysdate,
G_USER_ID,
G_LOGIN_ID
from amw_fin_cert_scope sc,
amw_fin_process_eval_sum peval,
amw_certification_b b,
amw_gl_periods_v agpv
where peval.fin_certification_id (+) = sc.fin_certification_id
and peval.organization_id (+) = sc.organization_id
and peval.process_id (+) = sc.process_id
and sc.fin_certification_id = b.certification_id
and b.certification_period_name = agpv.period_name
and b.certification_period_set_name = agpv.period_set_name);
select revision_number, significant_process_flag
into l_curr_rev_num, l_significant_process
from amw_process_organization a
where a.approval_date is not null
and a.approval_end_date is null
and process_id = cur_rec.process_id
and organization_id = cur_rec.organization_id;
select count(risk_id)
into l_risk_count
from amw_risk_associations
where object_type = 'PROCESS_FINCERT'
and pk1 = cur_rec.fin_certification_id
and pk2 = cur_rec.organization_id
and pk3 = cur_rec.process_id;
select count(control_id)
into l_control_count
from amw_control_associations
where object_type = 'RISK_FINCERT'
and pk1 = cur_rec.fin_certification_id
and pk2 = cur_rec.organization_id
and pk3 = cur_rec.process_id;
select DECODE(count(ACA.control_id),0,'N','Y')
into l_key_control
from amw_control_associations ACA, AMW_CONTROLS_ALL_VL ACAV
where object_type = 'RISK_FINCERT'
and pk1 = cur_rec.fin_certification_id
and pk2 = cur_rec.organization_id
and pk3 = cur_rec.process_id
AND ACA.CONTROL_ID = ACAV.CONTROL_ID
AND ACAV.CURR_APPROVED_FLAG = 'Y'
AND NVL(ACAV.KEY_MITIGATING,'N') = 'Y';
update rci_compl_env_chg_summ_f
set LATEST_APPR_REVISION_NUMBER = l_curr_rev_num,
/**01.25.2006 npanandi: changed below math, since it results in
negative values at times***/
/***NEW_REVISIONS_SINCE = latest_appr_revision_number - revision_number,***/
NEW_REVISIONS_SINCE = l_curr_rev_num - revision_number,
/**REVISED_PROCESS = decode(NEW_REVISIONS_SINCE, 0, 0, 1),***/
REVISED_PROCESS = decode((l_curr_rev_num - revision_number), 0, 0, 1),
Total_Risks = l_risk_count,
Total_Controls = l_control_count,
Num_Changed_Risks = calculate_risks_chg(cur_rec.fin_certification_id,
cur_rec.organization_id,
cur_rec.process_id),
Num_Changed_Controls = calculate_cntrl_chg(cur_rec.fin_certification_id,
cur_rec.organization_id,
cur_rec.process_id),
significant_process = NVL(l_significant_process,'N'),
key_control = l_key_control
where fin_certification_id = cur_rec.fin_certification_id
and organization_id = cur_rec.organization_id
and process_id = cur_rec.process_id;
INSERT INTO rci_dr_inc( fact_name
,last_run_date
,created_by
,creation_date
,last_update_date
,last_updated_by
,last_update_login
,program_id
,program_login_id
,program_application_id
,request_id ) VALUES (
'RCI_COMPL_ENV_CHG_SUMM_F'
,l_run_date
,l_user_id
,sysdate
,sysdate
,l_user_id
,l_login_id
,l_program_id
,l_program_login_id
,l_program_application_id
,l_request_id );
select * from rci_compl_env_chg_summ_f;
select distinct fin_certification_id,organization_id,process_id
from rci_compl_env_chg_summ_f
where organization_id is not null
and process_id is not null;
insert into rci_compl_env_chg_summ_f(
fin_certification_id,
cert_status,
cert_type,
cert_period_name,
cert_period_set_name,
statement_group_id,
financial_statement_id,
financial_item_id,
account_group_id,
natural_account_id,
organization_id,
process_id,
revision_number,
latest_appr_revision_number,
NEW_REVISIONS_SINCE,
REVISED_PROCESS,
Total_Risks,
Num_Changed_Risks,
Total_Controls,
Num_Changed_Controls,
period_year,
period_num,
quarter_num,
ent_period_id,
ent_qtr_id,
ent_year_id,
report_date_julian,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login) /*02.02.2006 npanandi: added distinct below*/
(select distinct sc.fin_certification_id,
b.certification_status,
b.certification_type,
b.certification_period_name,
b.certification_period_set_name,
/*02.02.2006 npanandi: not using the below columns for performance reasons*/
/*sc.statement_group_id,*/ -1000,
/*sc.financial_statement_id,*/ -1000,
/*sc.financial_item_id,*/ -1000,
/*sc.account_group_id,*/ -1000,
sc.natural_account_id,
sc.organization_id,
sc.process_id,
nvl(peval.revision_number,1),
0, 0, 0, 0, 0, 0, 0,
agpv.period_year,
agpv.period_num,
agpv.quarter_num,
to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
agpv.period_year,
to_number(to_char(agpv.end_date,'J')),
sysdate,
G_USER_ID,
sysdate,
G_USER_ID,
G_LOGIN_ID
from amw_fin_cert_scope sc,
amw_fin_process_eval_sum peval,
amw_certification_b b,
amw_gl_periods_v agpv
where peval.fin_certification_id (+) = sc.fin_certification_id
and peval.organization_id (+) = sc.organization_id
and peval.process_id (+) = sc.process_id
and sc.fin_certification_id = b.certification_id
and b.certification_period_name = agpv.period_name
and b.certification_period_set_name = agpv.period_set_name);
select revision_number, significant_process_flag
into l_curr_rev_num, l_significant_process
from amw_process_organization a
where a.approval_date is not null
and a.approval_end_date is null
and process_id = cur_rec.process_id
and organization_id = cur_rec.organization_id;
select count(risk_id)
into l_risk_count
from amw_risk_associations
where object_type = 'PROCESS_FINCERT'
and pk1 = cur_rec.fin_certification_id
and pk2 = cur_rec.organization_id
and pk3 = cur_rec.process_id;
select count(control_id)
into l_control_count
from amw_control_associations
where object_type = 'RISK_FINCERT'
and pk1 = cur_rec.fin_certification_id
and pk2 = cur_rec.organization_id
and pk3 = cur_rec.process_id;
select DECODE(count(ACA.control_id),0,'N','Y')
into l_key_control
from amw_control_associations ACA, AMW_CONTROLS_ALL_VL ACAV
where object_type = 'RISK_FINCERT'
and pk1 = cur_rec.fin_certification_id
and pk2 = cur_rec.organization_id
and pk3 = cur_rec.process_id
AND ACA.CONTROL_ID = ACAV.CONTROL_ID
AND ACAV.CURR_APPROVED_FLAG = 'Y'
AND NVL(ACAV.KEY_MITIGATING,'N') = 'Y';
update rci_compl_env_chg_summ_f
set LATEST_APPR_REVISION_NUMBER = l_curr_rev_num,
/**01.25.2006 npanandi: changed below math, since it results in
negative values at times***/
/***NEW_REVISIONS_SINCE = latest_appr_revision_number - revision_number,***/
NEW_REVISIONS_SINCE = l_curr_rev_num - revision_number,
REVISED_PROCESS = decode(NEW_REVISIONS_SINCE, 0, 0, 1),
Total_Risks = l_risk_count,
Total_Controls = l_control_count,
Num_Changed_Risks = calculate_risks_chg(cur_rec.fin_certification_id,
cur_rec.organization_id,
cur_rec.process_id),
Num_Changed_Controls = calculate_cntrl_chg(cur_rec.fin_certification_id,
cur_rec.organization_id,
cur_rec.process_id),
significant_process = NVL(l_significant_process,'N'),
key_control = l_key_control
where fin_certification_id = cur_rec.fin_certification_id
and organization_id = cur_rec.organization_id
and process_id = cur_rec.process_id;
UPDATE rci_dr_inc
SET last_run_date = l_run_date
,last_update_date = sysdate
,last_updated_by = l_user_id
,last_update_login = l_login_id
,program_id = l_program_id
,program_login_id = l_program_login_id
,program_application_id = l_program_application_id
,request_id = l_request_id
WHERE fact_name = 'RCI_COMPL_ENV_CHG_SUMM_F' ;
select count(risk_id)
into l_cnt1
from amw_risk_associations
where object_type = 'PROCESS_FINCERT'
and pk1 = cert_id
and pk2 = org_id
and pk3 = process_id
and risk_id not in (select risk_id
from amw_risk_associations
where object_type = 'PROCESS_ORG'
and pk1 = org_id
and pk2 = process_id
and approval_date is not null
and deletion_approval_date is null);
select count(risk_id)
into l_cnt2
from amw_risk_associations
where object_type = 'PROCESS_ORG'
and pk1 = org_id
and pk2 = process_id
and approval_date is not null
and deletion_approval_date is null
and risk_id not in (select risk_id
from amw_risk_associations
where object_type = 'PROCESS_FINCERT'
and pk1 = cert_id
and pk2 = org_id
and pk3 = process_id);
select count(control_id)
into l_cnt1
from amw_control_associations
where object_type = 'RISK_FINCERT'
and pk1 = cert_id
and pk2 = org_id
and pk3 = process_id
and control_id not in (select control_id
from amw_control_associations
where object_type = 'RISK_ORG'
and pk1 = org_id
and pk2 = process_id
and approval_date is not null
and deletion_approval_date is null);
select count(control_id)
into l_cnt2
from amw_control_associations
where object_type = 'RISK_ORG'
and pk1 = org_id
and pk2 = process_id
and approval_date is not null
and deletion_approval_date is null
and control_id not in (select control_id
from amw_control_associations
where object_type = 'RISK_FINCERT'
and pk1 = cert_id
and pk2 = org_id
and pk3 = process_id);
'select f.organization_id VIEWBYID,
(select name from hr_all_organization_units_tl v
where v.organization_id= f.organization_id
and v.language = userenv(''LANG'')) VIEWBY,
count(distinct process_id) RCI_COMP_ENV_MEASURE3,
SUM(REVISED_PROCESS) RCI_COMP_ENV_MEASURE2,
sum(NEW_REVISIONS_SINCE) RCI_COMP_ENV_MEASURE1,
decode(count(process_id), 0, null, SUM(REVISED_PROCESS)/count(process_id)*100) RCI_COMP_ENV_MEASURE4,
sum(Total_Risks) RCI_COMP_ENV_ATT1,
sum(Num_Changed_Risks) RCI_COMP_ENV_MEASURE5,
decode(sum(Total_Risks), 0, null, sum(Num_Changed_Risks)/sum(Total_Risks)*100) RCI_COMP_ENV_MEASURE6,
sum(Total_Controls) RCI_COMP_ENV_ATT2,
sum(Num_Changed_Controls) RCI_COMP_ENV_MEASURE7,
decode(sum(Total_Controls), 0, null, sum(Num_Changed_Controls)/sum(Total_Controls)*100) RCI_COMP_ENV_MEASURE8
from rci_compl_env_chg_summ_f f, fii_time_day ftd
where f.organization_id is not null
and f.report_date_julian = ftd.report_date_julian';
'select f.process_id VIEWBYID,
(select display_name from AMW_CURRENT_APPRVD_REV_V v
where v.process_id= f.process_id) VIEWBY,
count(distinct process_id) RCI_COMP_ENV_MEASURE3,
SUM(REVISED_PROCESS) RCI_COMP_ENV_MEASURE2,
sum(NEW_REVISIONS_SINCE) RCI_COMP_ENV_MEASURE1,
decode(count(process_id), 0, null, SUM(REVISED_PROCESS)/count(process_id)*100) RCI_COMP_ENV_MEASURE4,
sum(Total_Risks) RCI_COMP_ENV_ATT1,
sum(Num_Changed_Risks) RCI_COMP_ENV_MEASURE5,
decode(sum(Total_Risks), 0, null, sum(Num_Changed_Risks)/sum(Total_Risks)*100) RCI_COMP_ENV_MEASURE6,
sum(Total_Controls) RCI_COMP_ENV_ATT2,
sum(Num_Changed_Controls) RCI_COMP_ENV_MEASURE7,
decode(sum(Total_Controls), 0, null, sum(Num_Changed_Controls)/sum(Total_Controls)*100) RCI_COMP_ENV_MEASURE8
from rci_compl_env_chg_summ_f f, fii_time_day ftd
where f.process_id is not null
and f.report_date_julian = ftd.report_date_julian';
'select f.fin_certification_id VIEWBYID,
(select certification_name from amw_certification_vl v
where v.certification_id= f.fin_certification_id) VIEWBY,
count(distinct process_id) RCI_COMP_ENV_MEASURE3,
SUM(REVISED_PROCESS) RCI_COMP_ENV_MEASURE2,
sum(NEW_REVISIONS_SINCE) RCI_COMP_ENV_MEASURE1,
decode(count(process_id), 0, null, SUM(REVISED_PROCESS)/count(process_id)*100) RCI_COMP_ENV_MEASURE4,
sum(Total_Risks) RCI_COMP_ENV_ATT1,
sum(Num_Changed_Risks) RCI_COMP_ENV_MEASURE5,
decode(sum(Total_Risks), 0, null, sum(Num_Changed_Risks)/sum(Total_Risks)*100) RCI_COMP_ENV_MEASURE6,
sum(Total_Controls) RCI_COMP_ENV_ATT2,
sum(Num_Changed_Controls) RCI_COMP_ENV_MEASURE7,
decode(sum(Total_Controls), 0, null, sum(Num_Changed_Controls)/sum(Total_Controls)*100) RCI_COMP_ENV_MEASURE8
from rci_compl_env_chg_summ_f f, fii_time_day ftd
where f.fin_certification_id is not null
and f.report_date_julian = ftd.report_date_julian';
'select f.natural_account_id VIEWBYID,
/**(select name from AMW_FIN_KEY_ACCOUNTS_TL tl
where tl.ACCOUNT_GROUP_ID= f.ACCOUNT_GROUP_ID
and tl.NATURAL_ACCOUNT_ID= f.NATURAL_ACCOUNT_ID
and tl.language=userenv('||'''LANG'''||')) VIEWBY,**/
rsav.value VIEWBY,
count(distinct process_id) RCI_COMP_ENV_MEASURE3,
SUM(REVISED_PROCESS) RCI_COMP_ENV_MEASURE2,
sum(NEW_REVISIONS_SINCE) RCI_COMP_ENV_MEASURE1,
decode(count(process_id), 0, null, SUM(REVISED_PROCESS)/count(process_id)*100) RCI_COMP_ENV_MEASURE4,
sum(Total_Risks) RCI_COMP_ENV_ATT1,
sum(Num_Changed_Risks) RCI_COMP_ENV_MEASURE5,
decode(sum(Total_Risks), 0, null, sum(Num_Changed_Risks)/sum(Total_Risks)*100) RCI_COMP_ENV_MEASURE6,
sum(Total_Controls) RCI_COMP_ENV_ATT2,
sum(Num_Changed_Controls) RCI_COMP_ENV_MEASURE7,
decode(sum(Total_Controls), 0, null, sum(Num_Changed_Controls)/sum(Total_Controls)*100) RCI_COMP_ENV_MEASURE8
from rci_compl_env_chg_summ_f f, fii_time_day ftd, RCI_SIGNIFICANT_ACCT_V rsav
where f.account_group_id is not null
and f.natural_account_id is not null
and f.natural_account_id = rsav.id
and f.report_date_julian = ftd.report_date_julian';
l_act_sqlstmt := 'select VIEWBYID,VIEWBY,RCI_COMP_ENV_MEASURE3,RCI_COMP_ENV_MEASURE2
,RCI_COMP_ENV_MEASURE1,RCI_COMP_ENV_MEASURE4,RCI_COMP_ENV_ATT1
,RCI_COMP_ENV_MEASURE5,RCI_COMP_ENV_MEASURE6,RCI_COMP_ENV_ATT2
,RCI_COMP_ENV_MEASURE7,RCI_COMP_ENV_MEASURE8
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_query0||l_query1||l_query2||'
) t ) a
order by a.col_rank ';