[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 certification_id
,organization_id
,process_id
from rci_org_proc_dfcy_f;
DELETE FROM rci_dr_inc where fact_name = 'RCI_ORG_PROC_DFCY_F';
INSERT INTO RCI_ORG_PROC_DFCY_F(
project_id
,fin_certification_id
,certification_id
,certification_status
,certification_type
,certification_period_name
,certification_period_set_name
,organization_id
,process_id
,significant_process_flag
,standard_process_flag
,certification_result_code
,certified_by_id
,evaluation_result_code
,last_evaluated_by_id
,last_evaluated_on
,unmitigated_risks
,ineffective_controls
,natural_account_id
/** 10.20.2005 npanandi begin ***/
,period_year
,period_num
,quarter_num
,ent_period_id
,ent_qtr_id
,ent_year_id
,report_date_julian
/** 10.20.2005 npanandi end ***/
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
---,unmitigated_risks hold on to these for now
---,ineffective_controls
) select distinct aolv2.pk2_value /*project_id*/
,afpes.fin_certification_id
,-10000 /*certification_id*/
,acb.certification_status
,acb.certification_type
,acb.certification_period_name
,acb.certification_period_set_name
,afpes.organization_id
,afpes.process_id
,upper(nvl(alrv.significant_process_flag,'N'))
,upper(nvl(alrv.standard_process_flag,'N'))
,aolv1.audit_result_code /*certification_result_code*/
,aolv1.authored_by /*certified_by_id*/
,aolv2.audit_result_code /*evaluation_result_code*/
,aolv2.authored_by /*last_evaluated_by_id*/
,aolv2.authored_date /*last_evaluated_on*/
,null /*unmitigated_risks*/
,null /*ineffective_controls*/
,afkav.natural_account_id
,agpv.period_year /*period_year*/
,agpv.period_num /*period_num*/
,agpv.quarter_num /*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*/
,sysdate
,G_USER_ID
,sysdate
,G_USER_ID
,G_LOGIN_ID
from amw_fin_process_eval_sum afpes
,amw_certification_b acb
,amw_latest_revisions_v alrv
,amw_opinions_log_v aolv1
,amw_opinions_log_v aolv2
,(select pk1,pk2,NATURAL_ACCOUNT_ID from AMW_ACCT_ASSOCIATIONS where object_type='PROCESS_ORG' and approval_date is not null and deletion_approval_date is null) aaa
,AMW_FIN_KEY_ACCOUNTS_VL AFKAV
,amw_gl_periods_v agpv
where afpes.fin_certification_id = acb.certification_id
and afpes.process_id = alrv.process_id
and afpes.cert_opinion_log_id = aolv1.opinion_log_id(+)
and afpes.eval_opinion_log_id = aolv2.opinion_log_id(+)
and aaa.pk1(+) = afpes.organization_id
and aaa.pk2(+) = afpes.process_id
and aaa.natural_account_id = afkav.natural_account_id(+)
and acb.certification_period_name = agpv.period_name
and acb.certification_period_set_name = agpv.period_set_name
and exists (SELECT DISTINCT ctrls.pk1 as certification_id /*fin_certification_id*/
,o.organization_id
,all_ctrls.control_id
,op.audit_result_code
,op.authored_by /*last_evaluated_by_id*/
,op.authored_date /*last_evaluated_on*/
,(select /*aov.OPINION_VALUE_NAME*/ aov.opinion_value_id from AMW_OPINION_LOG_DETAILS aod, AMW_OPINION_VALUES_TL aov
WHERE aov.language=userenv('LANG') and op.OPINION_LOG_ID = aod.OPINION_LOG_ID
and aod.OPINION_VALUE_ID = aov.OPINION_VALUE_ID
and aod.OPINION_COMPONENT_ID = (select OPINION_COMPONENT_ID from AMW_OPINION_COMPONTS_B
where OBJECT_OPINION_TYPE_ID = op.OBJECT_OPINION_TYPE_ID
and OPINION_COMPONENT_CODE = 'OPERATING')) op_eff_id
,(select /*aov.OPINION_VALUE_NAME*/ aov.opinion_value_id from AMW_OPINION_LOG_DETAILS aod, AMW_OPINION_VALUES_TL aov
WHERE aov.language=userenv('LANG') and op.OPINION_LOG_ID = aod.OPINION_LOG_ID
and aod.OPINION_VALUE_ID = aov.OPINION_VALUE_ID
and aod.OPINION_COMPONENT_ID = (select OPINION_COMPONENT_ID from AMW_OPINION_COMPONTS_B
where OBJECT_OPINION_TYPE_ID = op.OBJECT_OPINION_TYPE_ID
and OPINION_COMPONENT_CODE = 'DESIGN')) des_eff_id
FROM AMW_CONTROL_ASSOCIATIONS ctrls,
AMW_CONTROLS_ALL_VL all_ctrls,
HR_ALL_ORGANIZATION_UNITS o,
HR_ALL_ORGANIZATION_UNITS_TL otl,
AMW_OPINIONS_LOG_V op
WHERE ctrls.object_type = 'RISK_FINCERT'
and ctrls.control_rev_id = all_ctrls.control_rev_id
and all_ctrls.APPROVAL_STATUS = 'A'
and o.organization_id = ctrls.pk2
and o.organization_id = otl.organization_id
and otl.language = userenv('LANG')
and op.opinion_log_id(+) = ctrls.pk5
and op.AUDIT_RESULT_CODE <> 'EFFECTIVE'
AND ctrls.pk1 = afpes.fin_certification_id
and o.ORGANIZATION_ID = afpes.organization_id
and ctrls.pk3 = afpes.process_id);
/****select distinct aov2.pk2_value ---project_id
,aca.pk1 --fin_certified_id
,-10000 --certification_id
,acb.certification_status
,acb.certification_type
,acb.certification_period_name
,acb.certification_period_set_name
,haou.organization_id
,alrv.process_id
,nvl(alrv.significant_process_flag,'N')
,nvl(alrv.standard_process_flag,'N')
,aov1.audit_result_code --certification_result_code
,aov1.authored_by --certified_by_id
,aov2.audit_result_code --evaluation_result_code
,aov2.authored_by --last_evaluated_by_id
,aov2.authored_date --last_evaluated_on
,null --unmitigated_risks
,null --ineffective_controls
,afkav.natural_account_id
,agpv.period_year --period_year
,agpv.period_num --period_num
,agpv.quarter_num --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
,sysdate
,G_USER_ID
,sysdate
,G_USER_ID
,G_LOGIN_ID
from amw_control_associations aca
,amw_opinions_log_v aolv
,amw_latest_revisions_v alrv
,HR_ALL_ORGANIZATION_UNITS haou
,HR_ALL_ORGANIZATION_UNITS_TL haout
,amw_opinions_v aov1
,amw_opinions_v aov2
,amw_certification_b acb
,(select pk1,pk2,NATURAL_ACCOUNT_ID from AMW_ACCT_ASSOCIATIONS where object_type='PROCESS_ORG' and approval_date is not null and deletion_approval_date is null) aaa
,AMW_FIN_KEY_ACCOUNTS_VL AFKAV
,amw_gl_periods_v agpv
where aca.object_type='RISK_FINCERT'
and aca.pk5=aolv.opinion_log_id
and aca.pk3=alrv.process_id
and aca.pk2=haou.organization_id
and haou.organization_id=haout.organization_id
and haout.language=userenv('LANG')
and aov1.opinion_type_code='CERTIFICATION'
and aov1.object_name='AMW_ORG_PROCESS'
and aov1.pk1_value=alrv.process_id
and aov1.pk3_value=aca.pk2
and aov2.opinion_type_code='EVALUATION'
and aov2.object_name='AMW_ORG_PROCESS'
and aov2.pk1_value=alrv.process_id
and aov2.pk3_value=aca.pk2
and aca.pk1=acb.certification_id
and aaa.pk1(+) = aca.pk2
and aaa.pk2(+) = aca.pk3
and aaa.natural_account_id = afkav.natural_account_id(+)
and acb.certification_period_name = agpv.period_name
and acb.certification_period_set_name = agpv.period_set_name
and aov2.authored_date in (select max(aov.authored_date)
from AMW_OPINIONS aov
where aov.object_opinion_type_id = aov2.object_opinion_type_id
and aov.pk1_value = aov2.pk1_value
and aov.pk3_value = aov2.pk3_value);***/
SELECT decode(count(aca.control_id),0,'N','Y')
into l_key_control
FROM amw_control_associations aca
,amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND aca.pk1 = r_load_cols.certification_id
AND aca.pk2 = r_load_cols.organization_id
AND aca.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = r_load_cols.process_id
AND organization_id = r_load_cols.organization_id
AND entity_id = r_load_cols.certification_id
and entity_type='BUSIPROC_CERTIFICATION'
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
and entity_type=prior entity_type)
and aca.control_id = acb.control_id
and acb.curr_approved_flag = 'Y'
and nvl(acb.key_mitigating,'N') = 'Y';
update RCI_ORG_PROC_DFCY_F
set unmitigated_risks = l_unmitigated_risks
,ineffective_controls = l_ineffective_controls
/** 12.12.2005 npanandi: bug 4862301 fix for keyControl **/
,key_control = l_key_control
where certification_id = r_load_cols.certification_id
and organization_id = r_load_cols.organization_id
and process_id = r_load_cols.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_ORG_PROC_DFCY_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 certification_id
,organization_id
,process_id
from rci_org_proc_dfcy_f;
DELETE FROM rci_dr_inc where fact_name = 'RCI_ORG_PROC_DFCY_F';
INSERT INTO RCI_ORG_PROC_DFCY_F(
project_id
,fin_certification_id
,certification_id
,certification_status
,certification_type
,certification_period_name
,certification_period_set_name
,organization_id
,process_id
,significant_process_flag
,standard_process_flag
,certification_result_code
,certified_by_id
,evaluation_result_code
,last_evaluated_by_id
,last_evaluated_on
,natural_account_id
/** 10.20.2005 npanandi begin ***/
,period_year
,period_num
,quarter_num
,ent_period_id
,ent_qtr_id
,ent_year_id
,report_date_julian
/** 10.20.2005 npanandi end ***/
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
---,unmitigated_risks hold on to these for now
---,ineffective_controls
) SELECT distinct aapv.AUDIT_PROJECT_ID,
acv2.certification_id,
execs.entity_id,
acv.CERTIFICATION_STATUS,
acv.CERTIFICATION_TYPE,
acv.CERTIFICATION_PERIOD_NAME,
acv.CERTIFICATION_PERIOD_SET_NAME,
execs.organization_id,
execs.process_id,
nvl(process.significant_process_flag,'N'),
nvl(process.standard_process_flag,'N'),
opinions_cert.audit_result_code,
opinions_cert.authored_by,
opinions_eval.audit_result_code,
opinions_eval.AUTHORED_BY,
opinions_eval.authored_date,
afkav.natural_account_id,
/** 10.20.2005 npanandi begin ***/
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')),
/** 10.20.2005 npanandi end ***/
sysdate,
G_USER_ID,
sysdate,
G_USER_ID,
G_LOGIN_ID
FROM amw_proc_cert_eval_sum proccert,
amw_opinions_log_v opinions_eval,
amw_opinions_v opinions_cert,
amw_process_organization_vl process,
amw_execution_scope execs,
amw_audit_units_v audit_v,
amw_audit_projects_v aapv,
amw_certification_vl acv,
amw_certification_vl acv2,
AMW_FIN_PROC_CERT_RELAN afpcr,
(select pk1,pk2,NATURAL_ACCOUNT_ID from AMW_ACCT_ASSOCIATIONS where object_type='PROCESS_ORG' and approval_date is not null and deletion_approval_date is null) aaa,
AMW_FIN_KEY_ACCOUNTS_VL AFKAV
/** 10.20.2005 npanandi begin ***/
,amw_gl_periods_v agpv
/** 10.20.2005 npanandi end ***/
WHERE execs.entity_id = opinions_cert.pk2_value(+)
AND execs.organization_id = opinions_cert.pk3_value(+)
AND execs.process_id = opinions_cert.pk1_value(+)
AND execs.entity_type = 'BUSIPROC_CERTIFICATION'
AND execs.entity_id = proccert.certification_id
and acv.CERTIFICATION_ID = proccert.CERTIFICATION_ID
and acv.OBJECT_TYPE = 'PROCESS'
and aapv.audit_project_id(+) = opinions_eval.pk2_value
AND execs.process_org_rev_id = proccert.process_org_rev_id
AND opinions_cert.opinion_type_code(+) = 'CERTIFICATION'
AND opinions_cert.object_name(+) = 'AMW_ORG_PROCESS'
AND proccert.evaluation_opinion_log_id = opinions_eval.opinion_log_id(+)
AND process.process_org_rev_id = execs.process_org_rev_id
AND process.organization_id = audit_v.organization_id
and afpcr.PROC_CERT_ID = acv.CERTIFICATION_ID
and afpcr.END_DATE is null
and afpcr.FIN_STMT_CERT_ID = acv2.CERTIFICATION_ID
and acv2.object_type='FIN_STMT'
and aaa.pk1(+) = execs.organization_id
and aaa.pk2(+) = execs.process_id
and aaa.natural_account_id = afkav.natural_account_id(+)
/** 10.20.2005 npanandi begin ***/
and acv.certification_period_name = agpv.period_name
and acv.certification_period_set_name = agpv.period_set_name;
SELECT decode(count(aca.control_id),0,'N','Y')
into l_key_control
FROM amw_control_associations aca
,amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND aca.pk1 = r_load_cols.certification_id
AND aca.pk2 = r_load_cols.organization_id
AND aca.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = r_load_cols.process_id
AND organization_id = r_load_cols.organization_id
AND entity_id = r_load_cols.certification_id
and entity_type='BUSIPROC_CERTIFICATION'
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
and entity_type=prior entity_type)
and aca.control_id = acb.control_id
and acb.curr_approved_flag = 'Y'
and nvl(acb.key_mitigating,'N') = 'Y';
update RCI_ORG_PROC_DFCY_F
set unmitigated_risks = l_unmitigated_risks
,ineffective_controls = l_ineffective_controls
/** 12.12.2005 npanandi: bug 4862301 fix for keyControl **/
,key_control = l_key_control
where certification_id = r_load_cols.certification_id
and organization_id = r_load_cols.organization_id
and process_id = r_load_cols.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_ORG_PROC_DFCY_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 certification_id
,organization_id
,process_id
from rci_org_proc_dfcy_f;
INSERT INTO RCI_ORG_PROC_DFCY_F(
project_id
,fin_certification_id
,certification_id
,certification_status
,certification_type
,certification_period_name
,certification_period_set_name
,organization_id
,process_id
,significant_process_flag
,standard_process_flag
,certification_result_code
,certified_by_id
,evaluation_result_code
,last_evaluated_by_id
,last_evaluated_on
,unmitigated_risks
,ineffective_controls
,natural_account_id
/** 10.20.2005 npanandi begin ***/
,period_year
,period_num
,quarter_num
,ent_period_id
,ent_qtr_id
,ent_year_id
,report_date_julian
/** 10.20.2005 npanandi end ***/
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
---,unmitigated_risks hold on to these for now
---,ineffective_controls
) select distinct aolv2.pk2_value /*project_id*/
,afpes.fin_certification_id
,-10000 /*certification_id*/
,acb.certification_status
,acb.certification_type
,acb.certification_period_name
,acb.certification_period_set_name
,afpes.organization_id
,afpes.process_id
,upper(nvl(alrv.significant_process_flag,'N'))
,upper(nvl(alrv.standard_process_flag,'N'))
,aolv1.audit_result_code /*certification_result_code*/
,aolv1.authored_by /*certified_by_id*/
,aolv2.audit_result_code /*evaluation_result_code*/
,aolv2.authored_by /*last_evaluated_by_id*/
,aolv2.authored_date /*last_evaluated_on*/
,null /*unmitigated_risks*/
,null /*ineffective_controls*/
,afkav.natural_account_id
,agpv.period_year /*period_year*/
,agpv.period_num /*period_num*/
,agpv.quarter_num /*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*/
,sysdate
,G_USER_ID
,sysdate
,G_USER_ID
,G_LOGIN_ID
from amw_fin_process_eval_sum afpes
,amw_certification_b acb
,amw_latest_revisions_v alrv
,amw_opinions_log_v aolv1
,amw_opinions_log_v aolv2
,(select pk1,pk2,NATURAL_ACCOUNT_ID from AMW_ACCT_ASSOCIATIONS where object_type='PROCESS_ORG' and approval_date is not null and deletion_approval_date is null) aaa
,AMW_FIN_KEY_ACCOUNTS_VL AFKAV
,amw_gl_periods_v agpv
where afpes.fin_certification_id = acb.certification_id
and afpes.process_id = alrv.process_id
and afpes.cert_opinion_log_id = aolv1.opinion_log_id(+)
and afpes.eval_opinion_log_id = aolv2.opinion_log_id(+)
and aaa.pk1(+) = afpes.organization_id
and aaa.pk2(+) = afpes.process_id
and aaa.natural_account_id = afkav.natural_account_id(+)
and acb.certification_period_name = agpv.period_name
and acb.certification_period_set_name = agpv.period_set_name
and exists (SELECT DISTINCT ctrls.pk1 as certification_id /*fin_certification_id*/
,o.organization_id
,all_ctrls.control_id
,op.audit_result_code
,op.authored_by /*last_evaluated_by_id*/
,op.authored_date /*last_evaluated_on*/
,(select /*aov.OPINION_VALUE_NAME*/ aov.opinion_value_id from AMW_OPINION_LOG_DETAILS aod, AMW_OPINION_VALUES_TL aov
WHERE aov.language=userenv('LANG') and op.OPINION_LOG_ID = aod.OPINION_LOG_ID
and aod.OPINION_VALUE_ID = aov.OPINION_VALUE_ID
and aod.OPINION_COMPONENT_ID = (select OPINION_COMPONENT_ID from AMW_OPINION_COMPONTS_B
where OBJECT_OPINION_TYPE_ID = op.OBJECT_OPINION_TYPE_ID
and OPINION_COMPONENT_CODE = 'OPERATING')) op_eff_id
,(select /*aov.OPINION_VALUE_NAME*/ aov.opinion_value_id from AMW_OPINION_LOG_DETAILS aod, AMW_OPINION_VALUES_TL aov
WHERE aov.language=userenv('LANG') and op.OPINION_LOG_ID = aod.OPINION_LOG_ID
and aod.OPINION_VALUE_ID = aov.OPINION_VALUE_ID
and aod.OPINION_COMPONENT_ID = (select OPINION_COMPONENT_ID from AMW_OPINION_COMPONTS_B
where OBJECT_OPINION_TYPE_ID = op.OBJECT_OPINION_TYPE_ID
and OPINION_COMPONENT_CODE = 'DESIGN')) des_eff_id
FROM AMW_CONTROL_ASSOCIATIONS ctrls,
AMW_CONTROLS_ALL_VL all_ctrls,
HR_ALL_ORGANIZATION_UNITS o,
HR_ALL_ORGANIZATION_UNITS_TL otl,
AMW_OPINIONS_LOG_V op
WHERE ctrls.object_type = 'RISK_FINCERT'
and ctrls.control_rev_id = all_ctrls.control_rev_id
and all_ctrls.APPROVAL_STATUS = 'A'
and o.organization_id = ctrls.pk2
and o.organization_id = otl.organization_id
and otl.language = userenv('LANG')
and op.opinion_log_id(+) = ctrls.pk5
and op.AUDIT_RESULT_CODE <> 'EFFECTIVE'
AND ctrls.pk1 = afpes.fin_certification_id
and o.ORGANIZATION_ID = afpes.organization_id
and ctrls.pk3 = afpes.process_id);
SELECT decode(count(aca.control_id),0,'N','Y')
into l_key_control
FROM amw_control_associations aca
,amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND aca.pk1 = r_load_cols.certification_id
AND aca.pk2 = r_load_cols.organization_id
AND aca.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = r_load_cols.process_id
AND organization_id = r_load_cols.organization_id
AND entity_id = r_load_cols.certification_id
and entity_type='BUSIPROC_CERTIFICATION'
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
and entity_type=prior entity_type)
and aca.control_id = acb.control_id
and acb.curr_approved_flag = 'Y'
and nvl(acb.key_mitigating,'N') = 'Y';
update RCI_ORG_PROC_DFCY_F
set unmitigated_risks = l_unmitigated_risks
,ineffective_controls = l_ineffective_controls
/** 12.12.2005 npanandi: bug 4862301 fix for keyControl **/
,key_control = l_key_control
where certification_id = r_load_cols.certification_id
and organization_id = r_load_cols.organization_id
and process_id = r_load_cols.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_ORG_PROC_DFCY_F' ;
select certification_id
,organization_id
,process_id
from rci_org_proc_dfcy_f;
INSERT INTO RCI_ORG_PROC_DFCY_F(
project_id
,fin_certification_id
,certification_id
,certification_status
,certification_type
,certification_period_name
,certification_period_set_name
,organization_id
,process_id
,significant_process_flag
,standard_process_flag
,certification_result_code
,certified_by_id
,evaluation_result_code
,last_evaluated_by_id
,last_evaluated_on
,natural_account_id
/** 10.20.2005 npanandi begin ***/
,period_year
,period_num
,quarter_num
,ent_period_id
,ent_qtr_id
,ent_year_id
,report_date_julian
/** 10.20.2005 npanandi end ***/
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
---,unmitigated_risks hold on to these for now
---,ineffective_controls
) SELECT distinct aapv.AUDIT_PROJECT_ID,
acv2.certification_id,
execs.entity_id,
acv.CERTIFICATION_STATUS,
acv.CERTIFICATION_TYPE,
acv.CERTIFICATION_PERIOD_NAME,
acv.CERTIFICATION_PERIOD_SET_NAME,
execs.organization_id,
execs.process_id,
nvl(process.significant_process_flag,'N'),
nvl(process.standard_process_flag,'N'),
opinions_cert.audit_result_code,
opinions_cert.authored_by,
opinions_eval.audit_result_code,
opinions_eval.AUTHORED_BY,
opinions_eval.authored_date,
afkav.natural_account_id,
/** 10.20.2005 npanandi begin ***/
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')),
/** 10.20.2005 npanandi end ***/
sysdate,
G_USER_ID,
sysdate,
G_USER_ID,
G_LOGIN_ID
FROM amw_proc_cert_eval_sum proccert,
amw_opinions_log_v opinions_eval,
amw_opinions_v opinions_cert,
amw_process_organization_vl process,
amw_execution_scope execs,
amw_audit_units_v audit_v,
amw_audit_projects_v aapv,
amw_certification_vl acv,
amw_certification_vl acv2,
AMW_FIN_PROC_CERT_RELAN afpcr,
(select pk1,pk2,NATURAL_ACCOUNT_ID from AMW_ACCT_ASSOCIATIONS where object_type='PROCESS_ORG' and approval_date is not null and deletion_approval_date is null) aaa,
AMW_FIN_KEY_ACCOUNTS_VL AFKAV
/** 10.20.2005 npanandi begin ***/
,amw_gl_periods_v agpv
/** 10.20.2005 npanandi end ***/
WHERE execs.entity_id = opinions_cert.pk2_value(+)
AND execs.organization_id = opinions_cert.pk3_value(+)
AND execs.process_id = opinions_cert.pk1_value(+)
AND execs.entity_type = 'BUSIPROC_CERTIFICATION'
AND execs.entity_id = proccert.certification_id
and acv.CERTIFICATION_ID = proccert.CERTIFICATION_ID
and acv.OBJECT_TYPE = 'PROCESS'
and aapv.audit_project_id(+) = opinions_eval.pk2_value
AND execs.process_org_rev_id = proccert.process_org_rev_id
AND opinions_cert.opinion_type_code(+) = 'CERTIFICATION'
AND opinions_cert.object_name(+) = 'AMW_ORG_PROCESS'
AND proccert.evaluation_opinion_log_id = opinions_eval.opinion_log_id(+)
AND process.process_org_rev_id = execs.process_org_rev_id
AND process.organization_id = audit_v.organization_id
and afpcr.PROC_CERT_ID = acv.CERTIFICATION_ID
and afpcr.END_DATE is null
and afpcr.FIN_STMT_CERT_ID = acv2.CERTIFICATION_ID
and acv2.object_type='FIN_STMT'
and aaa.pk1(+) = execs.organization_id
and aaa.pk2(+) = execs.process_id
and aaa.natural_account_id = afkav.natural_account_id(+)
/** 10.20.2005 npanandi begin ***/
and acv.certification_period_name = agpv.period_name
and acv.certification_period_set_name = agpv.period_set_name;
SELECT decode(count(aca.control_id),0,'N','Y')
into l_key_control
FROM amw_control_associations aca
,amw_controls_b acb
WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
AND aca.pk1 = r_load_cols.certification_id
AND aca.pk2 = r_load_cols.organization_id
AND aca.pk3 IN (SELECT DISTINCT process_id
FROM amw_execution_scope
START WITH process_id = r_load_cols.process_id
AND organization_id = r_load_cols.organization_id
AND entity_id = r_load_cols.certification_id
and entity_type='BUSIPROC_CERTIFICATION'
CONNECT BY PRIOR process_id = parent_process_id
AND organization_id = PRIOR organization_id
AND entity_id = PRIOR entity_id
and entity_type=prior entity_type)
and aca.control_id = acb.control_id
and acb.curr_approved_flag = 'Y'
and nvl(acb.key_mitigating,'N') = 'Y';
update RCI_ORG_PROC_DFCY_F
set unmitigated_risks = l_unmitigated_risks
,ineffective_controls = l_ineffective_controls
/** 12.12.2005 npanandi: bug 4862301 fix for keyControl **/
,key_control = l_key_control
where certification_id = r_load_cols.certification_id
and organization_id = r_load_cols.organization_id
and process_id = r_load_cols.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_ORG_PROC_DFCY_F' ;