[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 ;
DELETE FROM rci_dr_inc where fact_name = 'RCI_ORG_CERT_CTRLS_F';
INSERT INTO RCI_ORG_CERT_CTRLS_F(
fin_certification_id
,certification_id
,certification_status
,certification_type
,certification_period_name
,certification_period_set_name
,organization_id
,process_id
,natural_account_id
,control_id
,control_rev_id
,control_type
,control_location
,automation_type
,control_frequency
,key_control
,disclosure_control
,latest_rev_num
,audit_result_code
,last_evaluated_by_id
,last_evaluated_on
,op_eff_id
,des_eff_id
,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 ctrls.pk1 as certification_id /*fin_certification_id*/
,-10000 /*certification_id*/
,acv.certification_status
,acv.certification_type
,acv.certification_period_name
,acv.certification_period_set_name
,o.organization_id
,ctrls.pk3 /*process_id*/
,afcs.natural_account_id /*natural_account_id*/
,all_ctrls.control_id
,all_ctrls.control_rev_id
,all_ctrls.control_type
,all_ctrls.control_location
,all_ctrls.automation_type
,all_ctrls.control_frequency
,upper(nvl(all_ctrls.key_mitigating,'N'))
,upper(nvl(all_ctrls.disclosure_control,'N'))
,(select max(rev_num) from amw_controls_b where control_id=all_ctrls.control_id) /*latest_rev_num*/
,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
,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_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,
amw_certification_vl acv,
amw_fin_cert_scope afcs,
amw_gl_periods_v agpv
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 acv.certification_id = ctrls.pk1
and afcs.fin_certification_id = ctrls.pk1
and afcs.organization_id = o.organization_id
and afcs.process_id = ctrls.pk3
and acv.certification_period_name = agpv.period_name
and acv.certification_period_set_name = agpv.period_set_name;
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_CTRLS_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 );
DELETE FROM rci_dr_inc where fact_name = 'RCI_ORG_CERT_CTRLS_F';
INSERT INTO RCI_ORG_CERT_CTRLS_F(
fin_certification_id
,certification_id
,certification_status
,certification_type
,certification_period_name
,certification_period_set_name
,organization_id
,process_id
,natural_account_id
,control_id
,control_rev_id
,control_type
,control_location
,automation_type
,control_frequency
,key_control
,disclosure_control
,latest_rev_num
,audit_result_code
,last_evaluated_by_id
,last_evaluated_on
,op_eff_id
,des_eff_id
,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 acv2.certification_id,
ctrlassoc.pk1,
acv.certification_status,
acv.certification_type,
acv.certification_period_name,
acv.certification_period_set_name,
orgtable.organization_id,
procorg.process_id,
afkav.natural_account_id,
controltable.control_id,
ctrlassoc.control_rev_id,
controltable.control_type,
controltable.control_location,
controltable.automation_type,
controltable.control_frequency,
nvl(controltable.key_mitigating,'N'),
nvl(controltable.disclosure_control,'N'),
(select max(rev_num) from amw_controls_b where control_id=controltable.control_id),
opinionstable.audit_result_code,
opinionstable.last_updated_by,
opinionstable.authored_date,
(SELECT valuestable.opinion_value_id
FROM amw_opinion_log_details details,
amw_opinion_values_tl valuestable,
amw_opinion_componts_b compb
WHERE opinionstable.opinion_log_id = details.opinion_log_id
AND details.opinion_component_id = compb.opinion_component_id
AND compb.object_opinion_type_id = opinionstable.object_opinion_type_id
AND compb.opinion_component_code = 'OPERATING'
AND valuestable.language = userenv('LANG')
AND details.opinion_value_id = valuestable.opinion_value_id ),
(SELECT valuestable.opinion_value_id
FROM amw_opinion_log_details details,
amw_opinion_values_tl valuestable,
amw_opinion_componts_b compb
WHERE opinionstable.opinion_log_id = details.opinion_log_id
AND details.opinion_component_id = compb.opinion_component_id
AND compb.object_opinion_type_id = opinionstable.object_opinion_type_id
AND compb.opinion_component_code = 'DESIGN'
AND valuestable.language = userenv('LANG')
AND details.opinion_value_id = valuestable.opinion_value_id ),
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_control_associations ctrlassoc,
amw_controls_all_vl controltable,
amw_audit_units_v orgtable,
amw_opinions_log_v opinionstable,
amw_execution_scope execs,
AMW_PROCESS_ORGANIZATION_VL procorg,
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
,amw_gl_periods_v agpv
WHERE execs.entity_id = ctrlassoc.pk1
AND execs.entity_type = ctrlassoc.object_type
AND execs.organization_id = orgtable.organization_id
AND procorg.process_org_rev_id(+) = execs.process_org_rev_id
AND ctrlassoc.control_rev_id = controltable.control_rev_id
AND ctrlassoc.pk5 = opinionstable.opinion_log_id (+)
AND ctrlassoc.object_type = 'BUSIPROC_CERTIFICATION'
AND ctrlassoc.pk2 = orgtable.organization_id
AND NVL(ctrlassoc.pk3,-1) = NVL(execs.process_id,-1)
and acv.certification_id = execs.entity_id
and opinionstable.audit_result_code <> 'EFFECTIVE'
and opinionstable.OBJECT_NAME = 'AMW_ORG_CONTROL'
and opinionstable.OPINION_TYPE_CODE = 'EVALUATION'
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(+)
and acv.certification_period_name = agpv.period_name
and acv.certification_period_set_name = agpv.period_set_name
order by acv2.certification_id,ctrlassoc.pk1,orgtable.organization_id,procorg.process_id,controltable.control_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_CTRLS_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 );
INSERT INTO RCI_ORG_CERT_CTRLS_F(
fin_certification_id
,certification_id
,certification_status
,certification_type
,certification_period_name
,certification_period_set_name
,organization_id
,process_id
,natural_account_id
,control_id
,control_rev_id
,control_type
,control_location
,automation_type
,control_frequency
,key_control
,disclosure_control
,latest_rev_num
,audit_result_code
,last_evaluated_by_id
,last_evaluated_on
,op_eff_id
,des_eff_id
,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 ctrls.pk1 as certification_id /*fin_certification_id*/
,-10000 /*certification_id*/
,acv.certification_status
,acv.certification_type
,acv.certification_period_name
,acv.certification_period_set_name
,o.organization_id
,ctrls.pk3 /*process_id*/
,afcs.natural_account_id /*natural_account_id*/
,all_ctrls.control_id
,all_ctrls.control_rev_id
,all_ctrls.control_type
,all_ctrls.control_location
,all_ctrls.automation_type
,all_ctrls.control_frequency
,upper(nvl(all_ctrls.key_mitigating,'N'))
,upper(nvl(all_ctrls.disclosure_control,'N'))
,(select max(rev_num) from amw_controls_b where control_id=all_ctrls.control_id) /*latest_rev_num*/
,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
,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_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,
amw_certification_vl acv,
amw_fin_cert_scope afcs,
amw_gl_periods_v agpv
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 acv.certification_id = ctrls.pk1
and afcs.fin_certification_id = ctrls.pk1
and afcs.organization_id = o.organization_id
and afcs.process_id = ctrls.pk3
and acv.certification_period_name = agpv.period_name
and acv.certification_period_set_name = agpv.period_set_name;
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_CTRLS_F' ;
INSERT INTO RCI_ORG_CERT_CTRLS_F(
fin_certification_id
,certification_id
,certification_status
,certification_type
,certification_period_name
,certification_period_set_name
,organization_id
,process_id
,natural_account_id
,control_id
,control_rev_id
,control_type
,control_location
,automation_type
,control_frequency
,key_control
,disclosure_control
,latest_rev_num
,audit_result_code
,last_evaluated_by_id
,last_evaluated_on
,op_eff_id
,des_eff_id
,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 acv2.certification_id,
ctrlassoc.pk1,
acv.certification_status,
acv.certification_type,
acv.certification_period_name,
acv.certification_period_set_name,
orgtable.organization_id,
procorg.process_id,
afkav.natural_account_id,
controltable.control_id,
ctrlassoc.control_rev_id,
controltable.control_type,
controltable.control_location,
controltable.automation_type,
controltable.control_frequency,
nvl(controltable.key_mitigating,'N'),
nvl(controltable.disclosure_control,'N'),
(select max(rev_num) from amw_controls_b where control_id=controltable.control_id),
opinionstable.audit_result_code,
opinionstable.last_updated_by,
opinionstable.authored_date,
(SELECT valuestable.opinion_value_id
FROM amw_opinion_log_details details,
amw_opinion_values_tl valuestable,
amw_opinion_componts_b compb
WHERE opinionstable.opinion_log_id = details.opinion_log_id
AND details.opinion_component_id = compb.opinion_component_id
AND compb.object_opinion_type_id = opinionstable.object_opinion_type_id
AND compb.opinion_component_code = 'OPERATING'
AND valuestable.language = userenv('LANG')
AND details.opinion_value_id = valuestable.opinion_value_id ),
(SELECT valuestable.opinion_value_id
FROM amw_opinion_log_details details,
amw_opinion_values_tl valuestable,
amw_opinion_componts_b compb
WHERE opinionstable.opinion_log_id = details.opinion_log_id
AND details.opinion_component_id = compb.opinion_component_id
AND compb.object_opinion_type_id = opinionstable.object_opinion_type_id
AND compb.opinion_component_code = 'DESIGN'
AND valuestable.language = userenv('LANG')
AND details.opinion_value_id = valuestable.opinion_value_id ),
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_control_associations ctrlassoc,
amw_controls_all_vl controltable,
amw_audit_units_v orgtable,
amw_opinions_log_v opinionstable,
amw_execution_scope execs,
AMW_PROCESS_ORGANIZATION_VL procorg,
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
,amw_gl_periods_v agpv
WHERE execs.entity_id = ctrlassoc.pk1
AND execs.entity_type = ctrlassoc.object_type
AND execs.organization_id = orgtable.organization_id
AND procorg.process_org_rev_id(+) = execs.process_org_rev_id
AND ctrlassoc.control_rev_id = controltable.control_rev_id
AND ctrlassoc.pk5 = opinionstable.opinion_log_id (+)
AND ctrlassoc.object_type = 'BUSIPROC_CERTIFICATION'
AND ctrlassoc.pk2 = orgtable.organization_id
AND NVL(ctrlassoc.pk3,-1) = NVL(execs.process_id,-1)
and acv.certification_id = execs.entity_id
and opinionstable.audit_result_code <> 'EFFECTIVE'
and opinionstable.OBJECT_NAME = 'AMW_ORG_CONTROL'
and opinionstable.OPINION_TYPE_CODE = 'EVALUATION'
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(+)
and acv.certification_period_name = agpv.period_name
and acv.certification_period_set_name = agpv.period_set_name
order by acv2.certification_id,ctrlassoc.pk1,orgtable.organization_id,procorg.process_id,controltable.control_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_CTRLS_F' ;