[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
total_qry := 'SELECT COUNT(1) FROM (
select
sig_acct_id
,acct_eval_result_code
,certification_id
,acct_eval_e
,acct_eval_ne
,acct_eval_ie
,proc_cert_result_cwi
,proc_cert_result_c
,proc_cert_result_nc
,orgs_with_ineff_ctrls
,unmiti_risks
,ineff_ctrls
from
rci_sig_acct_eval_f accteval,fii_time_day ftd
where
accteval.report_date_julian = ftd.report_date_julian
'|| where_clause || '
group by
sig_acct_id
,acct_eval_result_code
,certification_id
,acct_eval_e
,acct_eval_ne
,acct_eval_ie
,proc_cert_result_cwi
,proc_cert_result_c
,proc_cert_result_nc
,orgs_with_ineff_ctrls
,unmiti_risks
,ineff_ctrls )' ;
l_sqlstmt :='SELECT
ROUND((nvl(MES1,0)*100/'||v_total ||'),2) AS RCI_SIG_ACCT_EVAL_SUMM_DIM1,
ROUND((nvl(MES2,0)*100/'||v_total ||'),2) AS RCI_SIG_ACCT_EVAL_SUMM_DIM2,
ROUND((nvl(MES3,0)*100/'||v_total ||'),2) AS RCI_SIG_ACCT_EVAL_SUMM_DIM3
FROM (
SELECT
SUM(acct_eval_ne) MES1,
SUM(acct_eval_ie) MES2,
SUM(acct_eval_e) MES3
FROM
(select
sig_acct_id
,acct_eval_result_code
,certification_id
,acct_eval_e
,acct_eval_ne
,acct_eval_ie
,proc_cert_result_cwi
,proc_cert_result_c
,proc_cert_result_nc
,orgs_with_ineff_ctrls
,unmiti_risks
,ineff_ctrls
from
rci_sig_acct_eval_f accteval,fii_time_day ftd
where
accteval.report_date_julian = ftd.report_date_julian
'|| where_clause || '
group by
sig_acct_id
,acct_eval_result_code
,certification_id
,acct_eval_e
,acct_eval_ne
,acct_eval_ie
,proc_cert_result_cwi
,proc_cert_result_c
,proc_cert_result_nc
,orgs_with_ineff_ctrls
,unmiti_risks
,ineff_ctrls
) accteval
)
';
SELECT DISTINCT
accteval.sig_acct_id RCI_DRILLDOWN_PARAM2
,'' '' RCI_PROC_CERT_MEASURE14
,afces.fin_certification_id RCI_DRILLDOWN_PARAM1
,acv.certification_name RCI_PROC_CERT_MEASURE1
,typelook.value RCI_PROC_CERT_MEASURE2
,aecv.full_name RCI_PROC_CERT_MEASURE3
,afsv.name RCI_PROC_CERT_MEASURE4
,agpv.quarter_num RCI_PROC_CERT_MEASURE5
,agpv.period_year RCI_PROC_CERT_MEASURE6
,statuslook.value RCI_PROC_CERT_MEASURE7
,trunc(acv.certification_creation_date) RCI_PROC_CERT_MEASURE8
,trunc(acv.target_completion_date) RCI_PROC_CERT_MEASURE9
,proc_pending_certification RCI_PROC_CERT_MEASURE10
-- ,evallook.value RCI_PROC_CERT_MEASURE11
/*01.26.2006 npanandi: bug 5000443 fix below**/
,/*o.audit_result*/rfaev.value RCI_PROC_CERT_MEASURE11
,o.author RCI_PROC_CERT_MEASURE12
,trunc(o.authored_date) RCI_PROC_CERT_MEASURE13
FROM
rci_sig_acct_eval_f accteval
,fii_time_day ftd
,amw_fin_cert_eval_sum afces
,amw_opinions_v o
,amw_certification_vl acv
,amw_employees_current_v aecv
,amw_fin_stmnt_vl afsv
/* ,(select id,value from rci_fs_acct_eval_v where obj_name=''AMW_KEY_ACCOUNT'') evallook
,(select * from rci_fs_cert_status_v rfcsv) statuslook
,(select * from rci_fs_cert_type_v rfctv) typelook
,(select period_name, quarter_num, period_year from amw_gl_periods_v) agpv*/
,rci_fs_cert_status_v statuslook
,rci_fs_cert_type_v typelook
,amw_gl_periods_v agpv
/*01.26.2006 npanandi: bug 5000443 fix below**/
,RCI_FS_ACCT_EVAL_V rfaev
WHERE
accteval.sig_acct_id = afces.natural_account_id
AND accteval.report_date_julian = ftd.report_date_julian
AND afces.object_type = ''ACCOUNT''
AND accteval.certification_id = acv.certification_id
AND afces.fin_certification_id = acv.certification_id
AND o.pk1_value(+) = afces.natural_account_id
AND o.pk2_value (+)= afces.fin_certification_id
AND o.opinion_type_code(+) = ''EVALUATION''
AND o.object_name(+) = ''AMW_KEY_ACCOUNT''
/*01.26.2006 npanandi: bug 5000443 fix below 2 lines**/
and nvl(o.audit_result_code,''NOT_EVALUATED'')=rfaev.id(+)
and rfaev.obj_name(+)=''AMW_KEY_ACCOUNT''
AND typelook.id(+) = accteval.cert_type
AND statuslook.id(+) = accteval.cert_status
-- AND evallook.id(+) = o.audit_result_code
AND acv.certification_owner_id = aecv.party_id
AND acv.financial_statement_id = afsv.financial_statement_id
' || where_clause || '
and acv.certification_period_name = agpv.period_name
';
select count(*) into v_count from amw_risk_Associations where pk1=p_org_id and (object_type='PROCESS_ORG' or object_type='ENTITY_RISK' );
select count(*) into v_count from amw_control_Associations where pk1=p_org_id and (object_type='RISK_ORG' or object_type='ENTITY_CONTROL');
select project_name into v_name from amw_audit_projects_v where audit_project_id =
(select entity_id from
(select distinct entity_id,creation_date from amw_Execution_scope
where entity_type='PROJECT' and organization_id=p_org_id
order by creation_date desc
) where rownum<2
);
SELECT DISTINCT
aauv.organization_id RCI_DRILLDOWN_PARAM1
,aauv.NAME RCI_PROC_CERT_MEASURE1
,aauv.company RCI_PROC_CERT_MEASURE2
,aauv.lob_description RCI_PROC_CERT_MEASURE3
,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_risk_count(aauv.organization_id) RCI_PROC_CERT_MEASURE4
,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_control_count(aauv.organization_id) RCI_PROC_CERT_MEASURE5
,'' '' RCI_PROC_CERT_MEASURE6
,acv.certification_name RCI_PROC_CERT_MEASURE7
,certres.value RCI_PROC_CERT_MEASURE8
,trunc(rocsf.ORG_CERTIFIED_ON) RCI_PROC_CERT_MEASURE9
,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_latest_engagement(aauv.organization_id) RCI_PROC_CERT_MEASURE10
,evalopinion.eval_result RCI_PROC_CERT_MEASURE11
,trunc(evalopinion.eval_date)RCI_PROC_CERT_MEASURE12
,evalopinion.eval_by RCI_PROC_CERT_MEASURE13
FROM
amw_audit_units_v aauv
,rci_org_cert_summ_f rocsf
,amw_certification_vl acv
,rci_bp_cert_result_v certres
,(select audit_result eval_result,authored_date eval_date,pk1_value org_id,pk2_value project_id,author eval_by from amw_opinions_v aov1
where aov1.opinion_type_code(+) = ''EVALUATION'' and aov1.object_name(+) = ''AMW_ORGANIZATION''
and aov1.authored_date = (select max(aov2.authored_date) from amw_opinions_v aov2
where aov2.opinion_type_code(+) = ''EVALUATION'' and aov2.object_name(+) = ''AMW_ORGANIZATION''
and aov1.pk1_value=aov2.pk1_value))
evalopinion
,(select period_name, period_set_name,
to_number(to_char(period_year)||to_char(quarter_num)||to_char(period_num)) ent_period_id,
to_number(to_char(period_year)||to_char(quarter_num)) ent_qtr_id,
period_year ent_year_id from amw_gl_periods_v) agpv
WHERE
aauv.organization_id = rocsf.organization_id
AND acv.certification_id = rocsf.certification_id
AND certres.id = nvl(rocsf.org_certification_status,''NOT_CERTIFIED'')
AND evalopinion.org_id(+) = aauv.organization_id
' || where_clause || '
AND acv.certification_period_name = agpv.period_name
AND acv.certification_period_set_name = agpv.period_set_name
';
l_sqlstmt := 'SELECT DISTINCT aauv.organization_id RCI_DRILLDOWN_PARAM1
,aauv.NAME RCI_PROC_CERT_MEASURE1
,aauv.company RCI_PROC_CERT_MEASURE2
,aauv.lob_description RCI_PROC_CERT_MEASURE3
,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_risk_count(aauv.organization_id) RCI_PROC_CERT_MEASURE4
,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_control_count(aauv.organization_id) RCI_PROC_CERT_MEASURE5
,'' '' RCI_PROC_CERT_MEASURE6
,acv.certification_name RCI_PROC_CERT_MEASURE7
,certres.value RCI_PROC_CERT_MEASURE8
,trunc(rocsf.ORG_CERTIFIED_ON) RCI_PROC_CERT_MEASURE9
,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_latest_engagement(aauv.organization_id) RCI_PROC_CERT_MEASURE10
,evalopinion.eval_result RCI_PROC_CERT_MEASURE11
,evalopinion.eval_by RCI_PROC_CERT_MEASURE12
,trunc(evalopinion.eval_date) RCI_PROC_CERT_MEASURE13
FROM amw_audit_units_v aauv
,rci_org_cert_summ_f rocsf
,amw_certification_vl acv
,rci_bp_cert_result_v certres
,(select audit_result eval_result,authored_date eval_date,pk1_value org_id,pk2_value project_id,author eval_by
from amw_opinions_v aov1
where aov1.opinion_type_code(+) = ''EVALUATION''
and aov1.object_name(+) = ''AMW_ORGANIZATION''
and aov1.authored_date = (select max(aov2.authored_date)
from amw_opinions_v aov2
where aov2.opinion_type_code(+) = ''EVALUATION''
and aov2.object_name(+) = ''AMW_ORGANIZATION''
and aov1.pk1_value=aov2.pk1_value)) evalopinion
,fii_time_day ftd
WHERE aauv.organization_id = rocsf.organization_id
/***01.10.2006 npanandi: changed below to join to rocsf
fin_certification_id instead of previous certification_id ***/
AND acv.certification_id = rocsf.fin_certification_id
/**01.11.2006 npanandi: changed below to handle bug in populating
cert_opinion_log_id column in amw_org_eval_sum tbl ***/
/***AND certres.id = nvl(rocsf.org_certification_status,''NOT_CERTIFIED'')***/
AND certres.id = decode(rocsf.org_certification_status,null,''NOT_CERTIFIED'',''EFFECTIVE'',''EFFECTIVE'',''INEFFECTIVE'')
AND evalopinion.org_id(+) = aauv.organization_id
and rocsf.report_date_julian=ftd.report_date_julian';
l_act_sqlstmt := 'select RCI_DRILLDOWN_PARAM1,RCI_PROC_CERT_MEASURE1,RCI_PROC_CERT_MEASURE2
,RCI_PROC_CERT_MEASURE3,RCI_PROC_CERT_MEASURE4
,RCI_PROC_CERT_MEASURE5,RCI_PROC_CERT_MEASURE6
,RCI_PROC_CERT_MEASURE7,RCI_PROC_CERT_MEASURE8
,RCI_PROC_CERT_MEASURE9,RCI_PROC_CERT_MEASURE10
,RCI_PROC_CERT_MEASURE11,RCI_PROC_CERT_MEASURE12,RCI_PROC_CERT_MEASURE13
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_sqlstmt || where_clause||'
) t ) a
order by a.col_rank ';
SELECT
count(1) into v_cnt
FROM (SELECT DISTINCT aca.pk1 cert_id, aca.pk2 org_id, aca.control_id
FROM amw_control_associations aca,amw_opinions_v aov
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND aca.pk1 = p_cert_id
AND aca.pk2 = p_org_id
AND aov.object_name = 'AMW_ORG_CONTROL'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk1_value = aca.control_id
AND aov.pk3_value = aca.pk2
AND aov.audit_result_code <> 'EFFECTIVE'
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value)
);
SELECT count(1) into v_cnt
FROM (SELECT DISTINCT aca.pk1 cert_id, aca.pk2 org_id, aca.control_id
FROM amw_control_associations aca,amw_opinions_v aov
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND aca.pk1 = p_cert_id
AND aca.pk2 = p_org_id
AND aov.object_name = 'AMW_ORG_CONTROL'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk1_value = aca.control_id
AND aov.pk3_value = aca.pk2
AND aov.audit_result_code <> 'EFFECTIVE'
AND aov.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions aov2
WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
AND aov2.pk3_value = aov.pk3_value
AND aov2.pk1_value = aov.pk1_value)
);
SELECT
count(DISTINCT amw_exec.process_id) into v_cnt
FROM
amw_execution_scope amw_exec
WHERE
amw_exec.entity_type = 'BUSIPROC_CERTIFICATION'
AND amw_exec.entity_id = p_cert_id
AND EXISTS (SELECT opinion.opinion_id
FROM amw_opinions_v opinion
WHERE opinion.pk1_value = amw_exec.process_id
AND opinion.pk3_value = p_org_id
AND opinion.opinion_type_code = 'EVALUATION'
AND opinion.object_name = 'AMW_ORG_PROCESS'
AND opinion.audit_result_code <> 'EFFECTIVE'
AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
FROM amw_opinions aov2
WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
AND aov2.pk3_value = opinion.pk3_value
AND aov2.pk1_value = opinion.pk1_value)
);
SELECT
DISTINCT aauv.organization_id RCI_DRILLDOWN_PARAM1
,aauv.NAME RCI_PROC_CERT_MEASURE1
,aauv.company RCI_PROC_CERT_MEASURE2
,aauv.lob_description RCI_PROC_CERT_MEASURE3
,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_unmiti_risks(acv.certification_id,aauv.organization_id) RCI_PROC_CERT_MEASURE4
,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_ineff_ctrls(acv.certification_id,aauv.organization_id) RCI_PROC_CERT_MEASURE5
,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_ineff_procs(acv.certification_id,aauv.organization_id) RCI_PROC_CERT_MEASURE6
,'' '' RCI_PROC_CERT_MEASURE7
,acv.certification_name RCI_PROC_CERT_MEASURE8
,certres.value RCI_PROC_CERT_MEASURE9
,trunc(rocsf.ORG_CERTIFIED_ON) RCI_PROC_CERT_MEASURE10
,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_latest_engagement(aauv.organization_id) RCI_PROC_CERT_MEASURE11
,evalopinion.eval_result RCI_PROC_CERT_MEASURE12
,evalopinion.eval_by RCI_PROC_CERT_MEASURE13
,trunc(evalopinion.eval_date) RCI_PROC_CERT_MEASURE14
FROM amw_audit_units_v aauv
,rci_org_cert_summ_f rocsf
,amw_certification_vl acv
,rci_bp_cert_result_v certres
,(select audit_result eval_result,authored_date eval_date,pk1_value org_id,pk2_value project_id,author eval_by
from amw_opinions_v aov1
where aov1.opinion_type_code(+) = ''EVALUATION''
and aov1.object_name(+) = ''AMW_ORGANIZATION''
and aov1.authored_date = (select max(aov2.authored_date)
from amw_opinions_v aov2
where aov2.opinion_type_code(+) = ''EVALUATION''
and aov2.object_name(+) = ''AMW_ORGANIZATION''
and aov1.pk1_value=aov2.pk1_value)) evalopinion
,fii_time_day ftd
WHERE aauv.organization_id = rocsf.organization_id
AND acv.certification_id = rocsf.fin_certification_id
AND certres.id = decode(rocsf.org_certification_status,null,''NOT_CERTIFIED'',''EFFECTIVE'',''EFFECTIVE'',''INEFFECTIVE'')
AND evalopinion.org_id(+) = aauv.organization_id
AND rocsf.report_date_julian=ftd.report_date_julian
AND rocsf.ineffective_controls > 0
'||where_clause;
SELECT DISTINCT
accteval.certification_id RCI_DRILLDOWN_PARAM1
,accteval.sig_acct_id RCI_DRILLDOWN_PARAM2
,afka.account_name RCI_PROC_CERT_MEASURE1
,nvl(afces.proc_pending_certification,0) RCI_PROC_CERT_MEASURE2
,nvl(afces.proc_with_ineffective_controls,0) RCI_PROC_CERT_MEASURE3
,nvl(afces.unmitigated_risks,0) RCI_PROC_CERT_MEASURE4
,nvl(afces.ineffective_controls,0) RCI_PROC_CERT_MEASURE5
,acv.certification_name RCI_PROC_CERT_MEASURE6
,rfaev.value RCI_PROC_CERT_MEASURE7
,o.author RCI_PROC_CERT_MEASURE8
,trunc(o.authored_date) RCI_PROC_CERT_MEASURE9
FROM
rci_sig_acct_eval_f accteval
,fii_time_day ftd
,amw_fin_cert_eval_sum afces
,amw_fin_key_accounts_vl afka
,(select distinct authored_date,pk1_value,pk2_value,author
from amw_opinions_v aov1
where aov1.opinion_type_code(+) = ''EVALUATION''
and aov1.object_name(+) = ''AMW_KEY_ACCOUNT''
and aov1.authored_date = (select max(aov2.authored_date)
from amw_opinions_v aov2
where aov2.opinion_type_code(+) = ''EVALUATION''
and aov2.object_name(+) = ''AMW_KEY_ACCOUNT''
and aov1.pk1_value=aov2.pk1_value
and aov1.pk2_value=aov2.pk2_value)) o
,amw_certification_vl acv
,RCI_FS_ACCT_EVAL_V rfaev
WHERE
afces.fin_certification_id(+) = accteval.certification_id
AND accteval.report_date_julian = ftd.report_date_julian
AND afces.natural_account_id(+) = accteval.sig_acct_id
AND afces.financial_Statement_id(+) = accteval.financial_Statement_id
AND afces.financial_item_id(+) = accteval.financial_item_id
AND rfaev.id = accteval.acct_eval_result_code
AND o.pk1_value(+) = accteval.sig_acct_id
AND o.pk2_value (+)= accteval.certification_id
AND rfaev.obj_name=''AMW_KEY_ACCOUNT''
AND acv.certification_id = accteval.certification_id
AND afka.natural_account_id = accteval.sig_acct_id
' || where_clause;
total_qry := 'SELECT COUNT(1) FROM (
select
sig_acct_id
,acct_eval_result_code
,certification_id
,acct_eval_e
,acct_eval_ne
,acct_eval_ie
,proc_cert_result_cwi
,proc_cert_result_c
,proc_cert_result_nc
,orgs_with_ineff_ctrls
,unmiti_risks
,ineff_ctrls
from
rci_sig_acct_eval_f accteval
,fii_time_day ftd
where
accteval.report_date_julian = ftd.report_date_julian
'|| where_clause || '
group by
sig_acct_id
,acct_eval_result_code
,certification_id
,acct_eval_e
,acct_eval_ne
,acct_eval_ie
,proc_cert_result_cwi
,proc_cert_result_c
,proc_cert_result_nc
,orgs_with_ineff_ctrls
,unmiti_risks
,ineff_ctrls )' ;
SELECT
value VIEWBY,
nvl(RCI_SIG_ACCT_EVAL_SUMM_DIM1,0) RCI_SIG_ACCT_EVAL_SUMM_DIM1,
nvl(RCI_SIG_ACCT_EVAL_SUMM_DIM2,0) RCI_SIG_ACCT_EVAL_SUMM_DIM2,
0 RCI_SIG_ACCT_EVAL_SUMM_DIM3,
rci_fs_acct_eval_v.id RCI_DRILLDOWN_PARAM1
FROM
(SELECT
acct_eval_result_code,
count(acct_eval_result_code) RCI_SIG_ACCT_EVAL_SUMM_DIM1,
ROUND(COUNT(acct_eval_result_code)/'||v_total||'*100,2) RCI_SIG_ACCT_EVAL_SUMM_DIM2
FROM
(select
sig_acct_id
,acct_eval_result_code
,certification_id
,acct_eval_e
,acct_eval_ne
,acct_eval_ie
,proc_cert_result_cwi
,proc_cert_result_c
,proc_cert_result_nc
,orgs_with_ineff_ctrls
,unmiti_risks
,ineff_ctrls
from
rci_sig_acct_eval_f accteval
,fii_time_day ftd
where
accteval.report_date_julian = ftd.report_date_julian
'|| where_clause || '
group by
sig_acct_id
,acct_eval_result_code
,certification_id
,acct_eval_e
,acct_eval_ne
,acct_eval_ie
,proc_cert_result_cwi
,proc_cert_result_c
,proc_cert_result_nc
,orgs_with_ineff_ctrls
,unmiti_risks
,ineff_ctrls) accteval
GROUP BY
acct_eval_result_code) rsae
,(select id,value from rci_fs_acct_eval_v where obj_name=''AMW_KEY_ACCOUNT'') rci_fs_acct_eval_v
WHERE
id = acct_eval_result_code(+)
';
l_act_sqlstmt := 'select VIEWBY,RCI_SIG_ACCT_EVAL_SUMM_DIM1,RCI_SIG_ACCT_EVAL_SUMM_DIM2
,RCI_SIG_ACCT_EVAL_SUMM_DIM3,RCI_DRILLDOWN_PARAM1
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_sqlstmt||'
) t ) a
order by a.col_rank ';
SELECT
DISTINCT name || '' ('' || one.natural_account_value || '')'' VIEWBY
,0 RCI_GRAND_TOTAL
,sig_acct_id RCI_DRILLDOWN_PARAM1
,RCI_SIG_ACCT_EVAL_SUMM_DIM1 ,RCI_SIG_ACCT_EVAL_SUMM_DIM2 ,RCI_SIG_ACCT_EVAL_SUMM_DIM3
,RCI_SIG_ACCT_EVAL_SUMM_DIM4 ,RCI_SIG_ACCT_EVAL_SUMM_DIM5 ,RCI_SIG_ACCT_EVAL_SUMM_DIM6
,RCI_SIG_ACCT_EVAL_SUMM_DIM7 ,RCI_SIG_ACCT_EVAL_SUMM_DIM8 ,RCI_SIG_ACCT_EVAL_SUMM_DIM9
FROM
(SELECT DISTINCT natural_account_value, sig_acct_id
,RCI_SIG_ACCT_EVAL_SUMM_DIM1 ,RCI_SIG_ACCT_EVAL_SUMM_DIM2 ,RCI_SIG_ACCT_EVAL_SUMM_DIM3
,RCI_SIG_ACCT_EVAL_SUMM_DIM4 ,RCI_SIG_ACCT_EVAL_SUMM_DIM5 ,RCI_SIG_ACCT_EVAL_SUMM_DIM6
,RCI_SIG_ACCT_EVAL_SUMM_DIM7 ,RCI_SIG_ACCT_EVAL_SUMM_DIM8 ,RCI_SIG_ACCT_EVAL_SUMM_DIM9
FROM
(
select
acct_eval.sig_acct_id
,acct_eval.ie RCI_SIG_ACCT_EVAL_SUMM_DIM1
,acct_eval.e RCI_SIG_ACCT_EVAL_SUMM_DIM2
,acct_eval.ne RCI_SIG_ACCT_EVAL_SUMM_DIM3
,nvl(ineff_org.org_with_ie_ctrls,0) RCI_SIG_ACCT_EVAL_SUMM_DIM4
,nvl(proc_cert.ie,0) RCI_SIG_ACCT_EVAL_SUMM_DIM5
,nvl(proc_cert.e,0) RCI_SIG_ACCT_EVAL_SUMM_DIM6
,nvl(proc_cert.ne,0) RCI_SIG_ACCT_EVAL_SUMM_DIM7
,nvl(unmiti_risks.risk_cnt,0) RCI_SIG_ACCT_EVAL_SUMM_DIM8
,nvl(ineff_ctrls.ctrl_cnt,0) RCI_SIG_ACCT_EVAL_SUMM_DIM9
from
(select
sig_acct_id
,sum(acct_eval_ie) ie
,sum(acct_eval_e) e
,sum(acct_eval_ne) ne
from (
select
sig_acct_id
,acct_eval_result_code
,certification_id
,acct_eval_ie
,acct_eval_e
,acct_eval_ne
from
rci_sig_acct_eval_f accteval
,fii_time_day ftd
where
accteval.report_date_julian = ftd.report_date_julian
' || where_clause || '
group by
sig_acct_id
,acct_eval_result_code
,certification_id
,acct_eval_ie
,acct_eval_e
,acct_eval_ne
)
group by
sig_acct_id
) acct_eval
,(select
NATURAL_ACCOUNT_ID, count(1) org_with_ie_ctrls
from (
select
distinct NATURAL_ACCOUNT_ID, ORGANIZATION_ID, fin_certification_id
from
rci_org_cert_summ_f fa_tab, fii_time_day ftd
where
fa_tab.report_date_julian = ftd.report_date_julian
and fa_tab.ineffective_controls > 0
' || where_clause1 || '
)
group by
NATURAL_ACCOUNT_ID
) ineff_org
,(select
NATURAL_ACCOUNT_ID, sum(ne) ne, sum(e) e, sum(ie) ie
from(
select
NATURAL_ACCOUNT_ID
,sum(decode(CERTIFICATION_RESULT_CODE, null,1,0)) ne
,sum(decode(CERTIFICATION_RESULT_CODE, ''EFFECTIVE'',1,0)) e
,sum(decode(CERTIFICATION_RESULT_CODE, ''INEFFECTIVE'',1,0)) ie
from
rci_process_detail_f fa_tab,fii_time_day ftd
where
fa_tab.report_date_julian = ftd.report_date_julian
' || where_clause1 || '
group by
NATURAL_ACCOUNT_ID, CERTIFICATION_RESULT_CODE
)
group by
NATURAL_ACCOUNT_ID
) proc_cert
,(select NATURAL_ACCOUNT_ID, count(1) risk_cnt
from (
select
distinct NATURAL_ACCOUNT_ID, RISK_ID, ORGANIZATION_ID, PROCESS_ID
from
rci_org_cert_risks_f fa_tab, fii_time_day ftd
where
fa_tab.report_date_julian = ftd.report_date_julian
and fa_tab.AUDIT_RESULT_CODE <> ''EFFECTIVE''
' || where_clause1 || '
)
group by
NATURAL_ACCOUNT_ID
) unmiti_risks
,(select NATURAL_ACCOUNT_ID, count(1) ctrl_cnt
from (
select
distinct NATURAL_ACCOUNT_ID, CONTROL_ID, ORGANIZATION_ID
from
rci_org_cert_ctrls_f fa_tab, fii_time_day ftd
where
fa_tab.report_date_julian = ftd.report_date_julian
and fa_tab.AUDIT_RESULT_CODE <> ''EFFECTIVE''
' || where_clause1 || '
)
group by
NATURAL_ACCOUNT_ID
) ineff_ctrls
where
acct_eval.sig_acct_id = proc_cert.NATURAL_ACCOUNT_ID(+)
and acct_eval.sig_acct_id = ineff_org.NATURAL_ACCOUNT_ID(+)
and acct_eval.sig_acct_id = unmiti_risks.NATURAL_ACCOUNT_ID(+)
and acct_eval.sig_acct_id = ineff_ctrls.NATURAL_ACCOUNT_ID(+)
) rsae, amw_fin_key_accounts_b accts
where sig_acct_id = natural_account_id
) one, AMW_FIN_KEY_ACCOUNTS_TL two
WHERE one.sig_acct_id = two.natural_account_id
and two.language=userenv(''LANG'')
';
l_act_sqlstmt := 'select VIEWBY,RCI_GRAND_TOTAL,RCI_DRILLDOWN_PARAM1
,RCI_SIG_ACCT_EVAL_SUMM_DIM1,RCI_SIG_ACCT_EVAL_SUMM_DIM2,RCI_SIG_ACCT_EVAL_SUMM_DIM3
,RCI_SIG_ACCT_EVAL_SUMM_DIM4,RCI_SIG_ACCT_EVAL_SUMM_DIM5,RCI_SIG_ACCT_EVAL_SUMM_DIM6
,RCI_SIG_ACCT_EVAL_SUMM_DIM7,RCI_SIG_ACCT_EVAL_SUMM_DIM8,RCI_SIG_ACCT_EVAL_SUMM_DIM9
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_sqlstmt||'
) t ) a
order by a.col_rank ';
SELECT pk1_value, pk2_value, audit_result_code
FROM amw_opinions_v
WHERE opinion_type_code = 'EVALUATION'
AND object_name = 'AMW_KEY_ACCOUNT';
SELECT pk1_value, pk2_value, pk3_value, audit_result_code
FROM amw_opinions_v
WHERE opinion_type_code = 'CERTIFICATION'
AND object_name = 'AMW_ORG_PROCESS';
SELECT DISTINCT STATEMENT_GROUP_ID, FINANCIAL_STATEMENT_ID, FINANCIAL_ITEM_ID
FROM rci_sig_acct_eval_f;
INSERT INTO RCI_SIG_ACCT_EVAL_F(
STATEMENT_GROUP_ID
,FINANCIAL_STATEMENT_ID
,FINANCIAL_ITEM_ID
,ACCOUNT_GROUP_ID
,CERT_STATUS
,CERT_TYPE
,CERTIFICATION_ID
,SIG_ACCT_ID
,ORGANIZATION_ID
,PROCESS_ID
,ACCT_EVAL_RESULT_CODE
,ACCT_EVAL_E
,ACCT_EVAL_IE
,ACCT_EVAL_NE
,ORGS_WITH_INEFF_CTRLS
,PROC_CERT_RESULT_CWI
,PROC_CERT_RESULT_C
,PROC_CERT_RESULT_NC
,UNMITI_RISKS
,INEFF_CTRLS
,PERIOD_YEAR
,PERIOD_NUM
,QUARTER_NUM
,ENT_PERIOD_ID
,ENT_QTR_ID
,ENT_YEAR_ID
,REPORT_DATE_JULIAN
,CREATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
SELECT
DISTINCT
a.statement_group_id
,a.financial_statement_id
,a.financial_item_id
,a.account_group_id
,acb.certification_status CERT_STATUS
,acb.certification_type CERT_TYPE
,acb.certification_id
,a.natural_account_id sig_acct_id
,a.organization_id
,a.process_id
,nvl(o.audit_result_code,'NOT_EVALUATED') ACCT_EVAL_RESULT_CODE
,decode(o.audit_result_code, 'EFFECTIVE', 1,0) ACCT_EVAL_E
,case o.audit_result_code when 'INEFFECTIVE' then 1 when 'SOMEWHAT_EFFECTIVE' then 1
when 'NEARLY_INEFFECTIVE' then 1 else 0 end ACCT_EVAL_IE
,decode(o.audit_result_code, null, 1,0) ACCT_EVAL_NE
,nvl(afces.org_with_ineffective_controls,0) ORGS_WITH_INEFF_CTRLS
,nvl(afces.proc_certified_with_issues,0) PROC_CERT_RESULT_CWI
,nvl(afces.procs_for_cert_done,0) PROC_CERT_RESULT_C
,nvl(afces.proc_pending_certification,0) PROC_CERT_RESULT_NC
,nvl(afces.unmitigated_risks,0) UNMITI_RISKS
,nvl(afces.ineffective_controls,0) INEFF_CTRLS
,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)) ENT_PERIOD_ID
,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)) ENT_QTR_ID
,agpv.period_year ENT_YEAR_ID
,to_number(to_char(agpv.end_date,'J')) REPORT_DATE_JULIAN
,G_USER_ID,G_LOGIN_ID,sysdate,G_USER_ID,sysdate
FROM
amw_fin_cert_scope a
,amw_fin_cert_eval_sum afces
,amw_certification_b acb
,amw_opinions_v o
,amw_gl_periods_v agpv
WHERE
a.fin_certification_id = acb.certification_id
and acb.object_type = 'FIN_STMT'
and afces.object_type = 'ACCOUNT'
and afces.natural_account_id = a.natural_account_id
and afces.fin_certification_id = a.fin_certification_id
and afces.financial_statement_id(+) = a.financial_statement_id
and afces.financial_item_id(+) = a.financial_item_id
and o.pk1_value(+) = a.natural_account_id
and o.pk2_value (+)= a.fin_certification_id
and o.opinion_type_code(+) = 'EVALUATION'
and o.object_name(+) = 'AMW_KEY_ACCOUNT'
and acb.certification_period_name = agpv.period_name
and acb.certification_period_set_name = agpv.period_set_name
-- and a.natural_account_id is not null
;
UPDATE rci_sig_acct_eval_f
SET
ACCT_EVAL_RESULT_CODE = v_evaluation_result,
ACCT_EVAL_IE = v_ineffective,
ACCT_EVAL_E = v_effective,
ACCT_EVAL_NE = v_not_evaluated,
lAST_UPDATE_DATE = SYSDATE,
lAST_UPDATED_BY = G_USER_ID,
lAST_UPDATE_LOGIN = G_LOGIN_ID
WHERE
CERTIFICATION_ID = r_get_acct_evaluations.pk2_value
AND SIG_ACCT_ID = r_get_acct_evaluations.pk1_value;
UPDATE rci_sig_acct_eval_f
SET
PROC_CERT_RESULT_CWI = v_cert_with_issues,
PROC_CERT_RESULT_C = v_certified,
PROC_CERT_RESULT_NC = v_not_certified,
lAST_UPDATE_DATE = SYSDATE,
lAST_UPDATED_BY = G_USER_ID,
lAST_UPDATE_LOGIN = G_LOGIN_ID
WHERE
PROCESS_ID = r_get_process_certifications.pk1_value
AND CERTIFICATION_ID = r_get_process_certifications.pk2_value
AND ORGANIZATION_ID = r_get_process_certifications.pk3_value;
UPDATE rci_sig_acct_eval_f
SET
ORGS_WITH_INEFF_CTRLS = v_orgs_with_ineff_ctrls,
UNMITI_RISKS = v_unmiti_risks,
INEFF_CTRLS = v_ineff_ctrls,
lAST_UPDATE_DATE = SYSDATE,
lAST_UPDATED_BY = G_USER_ID,
lAST_UPDATE_LOGIN = G_LOGIN_ID
WHERE STATEMENT_GROUP_ID = p_statement_group_id
AND FINANCIAL_STATEMENT_ID = p_financial_statement_id
AND FINANCIAL_ITEM_ID = p_financial_item_id;
DELETE FROM rci_dr_inc WHERE fact_name='RCI_SIG_ACCT_EVAL_F';
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_SIG_ACCT_EVAL_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 );