[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 distinct master_organization_id
from mtl_parameters;
SELECT user_profile_option_name
INTO l_profile_name
FROM fnd_profile_options_vl
WHERE profile_option_name = 'CS_INV_VALIDATION_ORG';
SELECT master_organization_id INTO l_master_org FROM mtl_parameters WHERE organization_id = l_org;
select distinct fin_certification_id
,organization_id
,process_id
from rci_org_cert_summ_f;
DELETE FROM rci_dr_inc where fact_name = 'RCI_ORG_CERT_SUMM_F';
INSERT INTO RCI_ORG_CERT_SUMM_F(
fin_certification_id
,natural_account_id
,certification_id
,certification_type
,certification_status
,certification_period_name
,certification_period_set_name
,certification_owner_id
,organization_id
,org_certification_status
,org_certified_by
,org_certified_on
,org_certified_with_issues
,org_certified
,org_not_certified
,process_id
,proc_certified_with_issues
,proc_certified
,proc_not_certified
,proc_w_ineff_ctrls
,unmitigated_risks
,ineffective_controls
,open_issues
,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)
SELECT DISTINCT hier.entity_id /*fin_certification_id*/
,afcs.natural_account_id /*natural_account_id*/
,-10000 /*certification_id, cannot insert NULL*/
,acv.certification_type /*certification_type*/
,acv.certification_status /*certification_status*/
,acv.certification_period_name /*certification_period_name*/
,acv.certification_period_set_name /*certification_period_set_name*/
,acv.certification_owner_id /*certification_owner_id*/
,hier.object_id /*organization_id*/
,certopinion.audit_result_code /*org_certification_status*/
---,certopinion.audit_result as cert_result
,certopinion.authored_by /*org_certified_by*/
,certopinion.authored_date /*org_certified_on*/
,decode(certopinion.audit_result_code,null,0,'EFFECTIVE',0,1) /*org_certified_with_issues*/
,decode(certopinion.audit_result_code,null,0,'EFFECTIVE',1,0) /*org_certified*/
,decode(certopinion.audit_result_code,null,1,0) /*org_not_certified*/
,afcs.process_id /*process_id*/
,null /*proc_certified_with_issues*/
,null /*proc_certified*/
,null /*proc_not_certified*/
,null /*proc_w_ineff_ctrls*/
,null /*unmitigated_risks*/
,null /*ineffective_controls*/
,null /*open_issues*/
,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_entity_hierarchies hier,
amw_fin_org_eval_sum orgeval,
amw_audit_units_v auv,
amw_opinions_log_v certopinion,
amw_opinions_log_v evalopinion,
amw_certification_vl acv,
amw_gl_periods_v agpv,
amw_fin_cert_scope afcs
WHERE hier.entity_type = 'FINSTMT_CERTIFICATION'
AND hier.entity_id = orgeval.fin_certification_id
and acv.certification_id = hier.entity_id
and acv.object_type = 'FIN_STMT'
AND hier.object_type = 'ORG'
AND hier.object_id = orgeval.organization_id
AND orgeval.organization_id = auv.organization_id
and orgeval.cert_opinion_log_id = certopinion.opinion_log_id(+)
AND orgeval.eval_opinion_log_id = evalopinion.opinion_log_id(+)
AND hier.parent_object_type IN ('ROOTNODE', 'ORG')
and acv.certification_period_name = agpv.period_name
and acv.certification_period_set_name = agpv.period_set_name
and hier.entity_id = afcs.fin_certification_id
and afcs.organization_id = orgeval.organization_id;
update rci_org_cert_summ_f
set proc_w_ineff_ctrls = l_proc_w_ineff_ctrls
where fin_certification_id = r_load_cols.fin_certification_id
and organization_id = r_load_cols.organization_id;
update rci_org_cert_summ_f
set proc_w_ineff_ctrls = l_proc_w_ineff_ctrls
,proc_certified_with_issues = l_processes_certified_w_issues
,proc_certified = l_processes_certified
,proc_not_certified = l_processes_not_certified
,unmitigated_risks = l_unmitigated_risks
,ineffective_controls = l_ineffective_ctrls
/***,open_issues = l_open_issues***/
where fin_certification_id = r_load_cols.fin_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_CERT_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 proc_w_ineff_ctrls
,unmitigated_risks
,ineffective_controls
,open_issues
,certification_id
,organization_id
,process_id
from rci_org_cert_summ_f;
DELETE FROM rci_dr_inc where fact_name = 'RCI_ORG_CERT_SUMM_F';
INSERT INTO RCI_ORG_CERT_SUMM_F(
fin_certification_id
,natural_account_id
,certification_id
,certification_type
,certification_status
,certification_period_name
,certification_period_set_name
,certification_owner_id
,organization_id
,org_certification_status
,org_certified_by
,org_certified_on
,process_id
,proc_certified_with_issues
,proc_certified
,proc_not_certified
,proc_w_ineff_ctrls
,unmitigated_risks
,ineffective_controls
,open_issues
/** 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)
select distinct acv2.certification_id
,afkav.natural_account_id
,acv.certification_id
,acv.certification_type
,acv.certification_status
,acv.certification_period_name
,acv.certification_period_set_name
,acv.CERTIFICATION_OWNER_ID
,haou.organization_id
,orgcert_aov.audit_result_code
,orgcert_aov.AUTHORED_BY
,orgcert_aov.AUTHORED_DATE
,ap.process_id
,decode(ap.process_id,null,0,decode(proccert_aolv.audit_result_code,'INEFFECTIVE',1,0))
,decode(ap.process_id,null,0,decode(proccert_aolv.audit_result_code,'EFFECTIVE',1,0))
,decode(ap.process_id,null,0,decode(proccert_aolv.audit_result_code,null,1,0))
,to_number(null)
,to_number(null)
,to_number(null)
,to_number(null)
/** 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_execution_scope aes
,amw_certification_vl acv
,amw_process ap
,amw_process_names_tl apnt
,amw_process_organization aop
,hr_all_organization_units haou
,hr_all_organization_units_tl haout
,hr_organization_information hoi
,amw_opinions_v orgcert_aov
,amw_opinions_v proccert_aolv
,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 entity_type='BUSIPROC_CERTIFICATION'
and acv.object_type='PROCESS'
and acv.certification_id=aes.entity_id
and (aes.level_id = (select max(level_id) from amw_execution_scope where entity_type='BUSIPROC_CERTIFICATION' and entity_id=aes.entity_id and organization_id=aes.organization_id) or aes.level_id > 3)
and haou.organization_id=haout.organization_id
and haout.language=userenv('LANG')
and haou.organization_id=hoi.organization_id
and hoi.org_information_context='CLASS'
and hoi.org_information1='AMW_AUDIT_UNIT'
and hoi.org_information2='Y'
and aes.organization_id=haou.organization_id
and aes.process_org_rev_id=aop.process_org_rev_id(+)
and aop.rl_process_rev_id=ap.process_rev_id(+)
and ap.process_rev_id=apnt.process_rev_id(+)
and apnt.language(+)=userenv('LANG')
and aes.entity_id=orgcert_aov.pk2_value(+)
and aes.organization_id=orgcert_aov.pk1_value(+)
and orgcert_aov.object_name(+)='AMW_ORGANIZATION'
and orgcert_aov.opinion_type_code(+)='CERTIFICATION'
and proccert_aolv.object_name(+)='AMW_ORG_PROCESS'
and proccert_aolv.opinion_type_code(+)='CERTIFICATION'
and proccert_aolv.pk1_value(+)=aes.process_id
and proccert_aolv.pk2_value(+)=aes.entity_id
and proccert_aolv.pk3_value(+)=aes.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(+) = aes.organization_id
and aaa.pk2(+) = aes.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;
update rci_org_cert_summ_f
set proc_w_ineff_ctrls = nvl(l_proc_w_ineff_ctrls,0)
,unmitigated_risks = nvl(l_unmitigated_risks,0)
,ineffective_controls = nvl(l_ineffective_ctrls,0)
,open_issues = nvl(l_open_issues,0)
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_CERT_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 distinct fin_certification_id
,organization_id
,process_id
from rci_org_cert_summ_f;
INSERT INTO RCI_ORG_CERT_SUMM_F(
fin_certification_id
,natural_account_id
,certification_id
,certification_type
,certification_status
,certification_period_name
,certification_period_set_name
,certification_owner_id
,organization_id
,org_certification_status
,org_certified_by
,org_certified_on
,org_certified_with_issues
,org_certified
,org_not_certified
,process_id
,proc_certified_with_issues
,proc_certified
,proc_not_certified
,proc_w_ineff_ctrls
,unmitigated_risks
,ineffective_controls
,open_issues
,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)
SELECT DISTINCT hier.entity_id /*fin_certification_id*/
,afcs.natural_account_id /*natural_account_id*/
,-10000 /*certification_id, cannot insert NULL*/
,acv.certification_type /*certification_type*/
,acv.certification_status /*certification_status*/
,acv.certification_period_name /*certification_period_name*/
,acv.certification_period_set_name /*certification_period_set_name*/
,acv.certification_owner_id /*certification_owner_id*/
,hier.object_id /*organization_id*/
,certopinion.audit_result_code /*org_certification_status*/
---,certopinion.audit_result as cert_result
,certopinion.authored_by /*org_certified_by*/
,certopinion.authored_date /*org_certified_on*/
,decode(certopinion.audit_result_code,null,0,'EFFECTIVE',0,1) /*org_certified_with_issues*/
,decode(certopinion.audit_result_code,null,0,'EFFECTIVE',1,0) /*org_certified*/
,decode(certopinion.audit_result_code,null,1,0) /*org_not_certified*/
,afcs.process_id /*process_id*/
,null /*proc_certified_with_issues*/
,null /*proc_certified*/
,null /*proc_not_certified*/
,null /*proc_w_ineff_ctrls*/
,null /*unmitigated_risks*/
,null /*ineffective_controls*/
,null /*open_issues*/
,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_entity_hierarchies hier,
amw_fin_org_eval_sum orgeval,
amw_audit_units_v auv,
amw_opinions_log_v certopinion,
amw_opinions_log_v evalopinion,
amw_certification_vl acv,
amw_gl_periods_v agpv,
amw_fin_cert_scope afcs
WHERE hier.entity_type = 'FINSTMT_CERTIFICATION'
AND hier.entity_id = orgeval.fin_certification_id
and acv.certification_id = hier.entity_id
and acv.object_type = 'FIN_STMT'
AND hier.object_type = 'ORG'
AND hier.object_id = orgeval.organization_id
AND orgeval.organization_id = auv.organization_id
and orgeval.cert_opinion_log_id = certopinion.opinion_log_id(+)
AND orgeval.eval_opinion_log_id = evalopinion.opinion_log_id(+)
AND hier.parent_object_type IN ('ROOTNODE', 'ORG')
and acv.certification_period_name = agpv.period_name
and acv.certification_period_set_name = agpv.period_set_name
and hier.entity_id = afcs.fin_certification_id
and afcs.organization_id = orgeval.organization_id;
update rci_org_cert_summ_f
set proc_w_ineff_ctrls = l_proc_w_ineff_ctrls
where fin_certification_id = r_load_cols.fin_certification_id
and organization_id = r_load_cols.organization_id;
update rci_org_cert_summ_f
set proc_w_ineff_ctrls = l_proc_w_ineff_ctrls
,proc_certified_with_issues = l_processes_certified_w_issues
,proc_certified = l_processes_certified
,proc_not_certified = l_processes_not_certified
,unmitigated_risks = l_unmitigated_risks
,ineffective_controls = l_ineffective_ctrls
/***,open_issues = l_open_issues***/
where fin_certification_id = r_load_cols.fin_certification_id
and organization_id = r_load_cols.organization_id
and process_id = r_load_cols.process_id;
select proc_w_ineff_ctrls
,unmitigated_risks
,ineffective_controls
,open_issues
,certification_id
,organization_id
,process_id
from rci_org_cert_summ_f;
INSERT INTO RCI_ORG_CERT_SUMM_F(
fin_certification_id
,natural_account_id
,certification_id
,certification_type
,certification_status
,certification_period_name
,certification_period_set_name
,certification_owner_id
,organization_id
,org_certification_status
,org_certified_by
,org_certified_on
,process_id
,proc_certified_with_issues
,proc_certified
,proc_not_certified
,proc_w_ineff_ctrls
,unmitigated_risks
,ineffective_controls
,open_issues
/** 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)
select distinct acv2.CERTIFICATION_ID
,afkav.natural_account_id
,acv.certification_id
,acv.certification_type
,acv.certification_status
,acv.certification_period_name
,acv.certification_period_set_name
,acv.CERTIFICATION_OWNER_ID
,haou.organization_id
,orgcert_aov.audit_result_code
,orgcert_aov.AUTHORED_BY
,orgcert_aov.AUTHORED_DATE
,ap.process_id
,decode(ap.process_id,null,0,decode(proccert_aolv.audit_result_code,'INEFFECTIVE',1,0))
,decode(ap.process_id,null,0,decode(proccert_aolv.audit_result_code,'EFFECTIVE',1,0))
,decode(ap.process_id,null,0,decode(proccert_aolv.audit_result_code,null,1,0))
,to_number(null)
,to_number(null)
,to_number(null)
,to_number(null)
/** 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_execution_scope aes
,amw_certification_vl acv
,amw_process ap
,amw_process_names_tl apnt
,amw_process_organization aop
,hr_all_organization_units haou
,hr_all_organization_units_tl haout
,hr_organization_information hoi
,amw_opinions_v orgcert_aov
,amw_opinions_log_v proccert_aolv
,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 entity_type='BUSIPROC_CERTIFICATION'
and acv.object_type='PROCESS'
and acv.certification_id=aes.entity_id
and haou.organization_id=haout.organization_id
and haout.language=userenv('LANG')
and haou.organization_id=hoi.organization_id
and hoi.org_information_context='CLASS'
and hoi.org_information1='AMW_AUDIT_UNIT'
and hoi.org_information2='Y'
and aes.organization_id=haou.organization_id
and aes.process_org_rev_id=aop.process_org_rev_id(+)
and aop.rl_process_rev_id=ap.process_rev_id(+)
and ap.process_rev_id=apnt.process_rev_id(+)
and apnt.language(+)=userenv('LANG')
and aes.entity_id=orgcert_aov.pk2_value(+)
and aes.organization_id=orgcert_aov.pk1_value(+)
and orgcert_aov.object_name(+)='AMW_ORGANIZATION'
and orgcert_aov.opinion_type_code(+)='CERTIFICATION'
and proccert_aolv.object_name(+)='AMW_ORG_PROCESS'
and proccert_aolv.opinion_type_code(+)='CERTIFICATION'
and proccert_aolv.pk1_value(+)=aes.process_id
and proccert_aolv.pk2_value(+)=aes.entity_id
and proccert_aolv.pk3_value(+)=aes.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(+) = aes.organization_id
and aaa.pk2(+) = aes.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;
update rci_org_cert_summ_f
set proc_w_ineff_ctrls = l_proc_w_ineff_ctrls
,unmitigated_risks = l_unmitigated_risks
,ineffective_controls = l_ineffective_ctrls
,open_issues = l_open_issues
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_CERT_SUMM_F' ;