[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_RISKS_F';
INSERT INTO RCI_ORG_CERT_RISKS_F(
project_id
,fin_certification_id
,certification_id
,certification_status
,certification_type
/** 10.20.2005 npanandi begin ***/
,certification_period_name
,certification_period_set_name
/** 10.20.2005 npanandi end ***/
,organization_id
,process_id
,natural_account_id
,risk_id
,material
,risk_impact
,likelihood
,last_evaluator_id
,last_evaluated_on
,risk_rev_id
,audit_result_code
/** 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 op.pk2_value, /*project_id*/
risks.pk1 as certification_id, /*fin_certification_id*/
-10000, /*certification_id, cannot insert NULL*/
acv.certification_status, /*certification_status*/
acv.certification_type, /*certification_type*/
acv.certification_period_name,
acv.certification_period_set_name,
proc.organization_id,
proc.process_id,
AFKAV.natural_account_id,
all_risks.risk_id,
nvl(all_risks.material,'N'),
all_risks.risk_impact,
all_risks.likelihood,
op.last_updated_by, /*last_evaluator_id*/
op.authored_date, /*last_evaluated_on*/
all_risks.risk_rev_id,
op.audit_result_code,
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_RISK_ASSOCIATIONS risks,
AMW_RISKS_ALL_VL all_risks,
AMW_PROCESS_ORGANIZATION_VL proc,
HR_ALL_ORGANIZATION_UNITS o,
HR_ALL_ORGANIZATION_UNITS_TL otl,
AMW_OPINIONS_LOG_V op,
amw_certification_vl acv,
(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 risks.object_type = 'PROCESS_FINCERT'
and all_risks.risk_rev_id = risks.risk_rev_id
and o.organization_id = risks.pk2
and o.organization_id = otl.organization_id
and otl.language = userenv('LANG')
and proc.organization_id = risks.pk2
and proc.process_id = risks.pk3
and risks.approval_date is not null
and proc.approval_status = 'A'
and proc.approval_date = risks.approval_date
and op.opinion_log_id(+) = risks.pk4
and risks.pk1 = acv.certification_id
and aaa.pk1(+) = proc.organization_id
and aaa.pk2(+) = proc.process_id
and aaa.natural_account_id = afkav.natural_account_id(+)
----and op.audit_result_code <> 'EFFECTIVE'
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_RISKS_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_RISKS_F';
INSERT INTO RCI_ORG_CERT_RISKS_F(
project_id
,fin_certification_id
,certification_id
,certification_status
,certification_type
/** 10.20.2005 npanandi begin ***/
,certification_period_name
,certification_period_set_name
/** 10.20.2005 npanandi end ***/
,organization_id
,process_id
,natural_account_id
,risk_id
,material
,risk_impact
,likelihood
,last_evaluator_id
,last_evaluated_on
,risk_rev_id
,audit_result_code
/** 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 opinionstable.pk2_value,
acv2.certification_id,
assoctable.pk1,
acb.certification_status,
acb.certification_type,
/** 10.20.2005 npanandi begin ***/
acb.certification_period_name,
acb.certification_period_set_name,
/** 10.20.2005 npanandi end ***/
orgtable.organization_id,
assoctable.pk3,
afkav.natural_account_id,
assoctable.risk_id,
nvl(risktable.material,'N'),
risktable.risk_impact,
risktable.likelihood,
opinionstable.last_updated_by, ---storing last_updated_by, since this maps to papf to give the evaluator name
opinionstable.authored_date,
assoctable.risk_rev_id,
opinionstable.audit_result_code,
/** 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_risk_associations assoctable,
amw_risks_all_vl risktable,
amw_audit_units_v orgtable,
amw_opinions_log_v opinionstable,
amw_execution_scope execs,
AMW_PROCESS_ORGANIZATION_VL procorg,
amw_audit_projects_v aapv,
AMW_CERTIFICATION_B acb,
AMW_CERTIFICATION_TL ACT,
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 = assoctable.pk1
AND execs.entity_type = assoctable.object_type
AND execs.organization_id = orgtable.organization_id
AND assoctable.object_type = 'BUSIPROC_CERTIFICATION'
AND execs.process_org_rev_id = procorg.process_org_rev_id --(+)
AND assoctable.pk4 = opinionstable.opinion_log_id ---(+)
AND assoctable.pk2 = orgtable.organization_id
AND NVL(assoctable.pk3, -1) = NVL(execs.process_id,-1)
AND assoctable.risk_rev_id = risktable.risk_rev_id
and opinionstable.pk2_value = aapv.audit_project_id ---(+)
and assoctable.pk1 = acb.certification_id
and acb.certification_id = act.certification_id
and act.language = userenv('LANG')
and opinionstable.audit_result_code <> 'EFFECTIVE'
and afpcr.PROC_CERT_ID = acb.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(+)
/*** 11.28.2005 npanandi: added below ***/
---12.20.2005 npanandi: commenting this currApprovedFlag out for compatibility
---between this fact table ETL and the orgcertsummary fact table
---unmitigated risk count
---and risktable.curr_approved_flag='Y'
and procorg.approval_date is not null
and procorg.approval_end_date is null
/*** 11.28.2005 npanandi: added below ***/
/** 10.20.2005 npanandi begin ***/
and acb.certification_period_name = agpv.period_name
and acb.certification_period_set_name = agpv.period_set_name
/** 10.20.2005 npanandi end ***/
order by acv2.certification_id,assoctable.pk1,orgtable.organization_id,assoctable.pk3,afkav.natural_account_id,assoctable.risk_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_RISKS_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_RISKS_F(
project_id
,fin_certification_id
,certification_id
,certification_status
,certification_type
/** 10.20.2005 npanandi begin ***/
,certification_period_name
,certification_period_set_name
/** 10.20.2005 npanandi end ***/
,organization_id
,process_id
,natural_account_id
,risk_id
,material
,risk_impact
,likelihood
,last_evaluator_id
,last_evaluated_on
,risk_rev_id
,audit_result_code
/** 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 op.pk2_value, /*project_id*/
risks.pk1 as certification_id, /*fin_certification_id*/
-10000, /*certification_id, cannot insert NULL*/
acv.certification_status, /*certification_status*/
acv.certification_type, /*certification_type*/
acv.certification_period_name,
acv.certification_period_set_name,
proc.organization_id,
proc.process_id,
AFKAV.natural_account_id,
all_risks.risk_id,
nvl(all_risks.material,'N'),
all_risks.risk_impact,
all_risks.likelihood,
op.last_updated_by, /*last_evaluator_id*/
op.authored_date, /*last_evaluated_on*/
all_risks.risk_rev_id,
op.audit_result_code,
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_RISK_ASSOCIATIONS risks,
AMW_RISKS_ALL_VL all_risks,
AMW_PROCESS_ORGANIZATION_VL proc,
HR_ALL_ORGANIZATION_UNITS o,
HR_ALL_ORGANIZATION_UNITS_TL otl,
AMW_OPINIONS_LOG_V op,
amw_certification_vl acv,
(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 risks.object_type = 'PROCESS_FINCERT'
and all_risks.risk_rev_id = risks.risk_rev_id
and o.organization_id = risks.pk2
and o.organization_id = otl.organization_id
and otl.language = userenv('LANG')
and proc.organization_id = risks.pk2
and proc.process_id = risks.pk3
and risks.approval_date is not null
and proc.approval_status = 'A'
and proc.approval_date = risks.approval_date
and op.opinion_log_id(+) = risks.pk4
and risks.pk1 = acv.certification_id
and aaa.pk1(+) = proc.organization_id
and aaa.pk2(+) = proc.process_id
and aaa.natural_account_id = afkav.natural_account_id(+)
----and op.audit_result_code <> 'EFFECTIVE'
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_RISKS_F' ;