[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;
DELETE FROM rci_dr_inc where fact_name = 'RCI_PROCESS_DETAIL_F';
INSERT INTO RCI_PROCESS_DETAIL_F(
project_id
,process_org_rev_id
,fin_certification_id
,certification_id
,certification_status
,certification_type
,certification_period_name
,certification_period_set_name
,organization_id
,process_id
,natural_account_id
,significant_process_flag
,standard_process_flag
,process_category
,certification_result_code
,certified_by_id
,certified_on
,evaluation_result_code
,evaluated_by_id
,last_evaluated_on
/** 10.19.2005 npanandi begin ***/
,period_year
,period_num
,quarter_num
,ent_period_id
,ent_qtr_id
,ent_year_id
,report_date_julian
/** 10.19.2005 npanandi end ***/
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
select distinct evalopn.pk2_value, /*project_id*/
proc.process_org_rev_id,
finprocsum.FIN_CERTIFICATION_ID,
-10000, /*certification_id, cannot insert NULL here*/
acb.certification_status,
acb.certification_type,
acb.certification_period_name,
acb.certification_period_set_name,
o.organization_id,
proc.process_id,
aaa.natural_account_id,
nvl(proc.significant_process_flag,'N'),
nvl(proc.standard_process_flag,'N'),
proc.process_category,
certopn.audit_result_code, /*certification_result_code*/
certopn.authored_by, /*certified_by_id*/
certopn.authored_date, /*certified_on*/
evalopn.audit_result_code, /*evaluation_result_code*/
evalopn.authored_by, /*evaluated_by_id*/
evalopn.authored_date, /*last_evaluated_on*/
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_FIN_CERT_SCOPE finscope,
AMW_FIN_PROC_CERT_RELAN REL,
AMW_FIN_PROCESS_EVAL_SUM finprocsum,
HR_ALL_ORGANIZATION_UNITS o,
HR_ALL_ORGANIZATION_UNITS_TL otl,
AMW_PROCESS_ORGANIZATION_VL proc,
/*AMW_OPINIONS_V*/ AMW_OPINIONS_LOG_V certopn,
/*AMW_OPINIONS_V*/ AMW_OPINIONS_LOG_V evalopn,
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_gl_periods_v agpv
where rel.FIN_STMT_CERT_ID = finprocsum.FIN_CERTIFICATION_ID
and rel.end_date is null
and finprocsum.FIN_CERTIFICATION_ID = finscope.fin_certification_id
and finprocsum.ORGANIZATION_ID = finscope.ORGANIZATION_ID
and finprocsum.PROCESS_ID = finscope.PROCESS_ID
and finprocsum.PROCESS_ORG_REV_ID = proc.PROCESS_ORG_REV_ID
and o.organization_id = finscope.organization_id
and o.organization_id = otl.organization_id
and otl.language = userenv('LANG')
and finprocsum.EVAL_OPINION_log_ID = evalopn.opinion_log_id(+)
and finprocsum.cert_opinion_log_id = certopn.opinion_log_id(+)
/***and certopn.opinion_type_code = 'CERTIFICATION'
and certopn.object_name = 'AMW_ORG_PROCESS'
and certopn.pk1_value = finscope.process_id
and certopn.pk2_value = rel.PROC_CERT_ID
and certopn.pk3_value = finscope.organization_id
AND certopn.authored_date = (select max(aov2.authored_date) from AMW_OPINIONS aov2
where aov2.object_opinion_type_id = certopn.object_opinion_type_id
and aov2.pk3_value = certopn.pk3_value
AND aov2.pk2_value in (select proc_cert_Id
from AMW_FIN_PROC_CERT_RELAN
where fin_stmt_cert_id = finprocsum.FIN_CERTIFICATION_ID
and end_date is null)
and aov2.pk1_value = certopn.pk1_value)
***/
and finprocsum.FIN_CERTIFICATION_ID = acb.certification_id
and aaa.pk1(+) = finprocsum.organization_id
and aaa.pk2(+) = finprocsum.process_id
and acb.certification_period_name = agpv.period_name
and acb.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_PROCESS_DETAIL_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_PROCESS_DETAIL_F';
INSERT INTO RCI_PROCESS_DETAIL_F(
project_id
,process_org_rev_id
,fin_certification_id
,certification_id
,certification_status
,certification_type
,certification_period_name
,certification_period_set_name
,organization_id
,process_id
,natural_account_id
,significant_process_flag
,standard_process_flag
,process_category
,certification_result_code
,certified_by_id
,certified_on
,evaluation_result_code
,evaluated_by_id
,last_evaluated_on
/** 10.19.2005 npanandi begin ***/
,period_year
,period_num
,quarter_num
,ent_period_id
,ent_qtr_id
,ent_year_id
,report_date_julian
/** 10.19.2005 npanandi end ***/
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT distinct opinions_eval.pk2_value,
execs.PROCESS_ORG_REV_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,
afkav.natural_account_id,
nvl(process.significant_process_flag,'N'),
nvl(process.standard_process_flag,'N'),
process.process_category,
opinions_cert.audit_result_code,
opinions_cert.authored_by,
opinions_cert.authored_date,
opinions_eval.audit_result_code,
opinions_eval.authored_by,
opinions_eval.authored_date,
/** 10.19.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.19.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_certification_vl acv,
amw_audit_projects_v aapv,
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.19.2005 npanandi begin ***/
amw_gl_periods_v agpv
/** 10.19.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 execs.process_org_rev_id = proccert.process_org_rev_id
and execs.entity_id = acv.certification_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 aapv.audit_project_id(+) = opinions_eval.pk2_value
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.19.2005 npanandi begin ***/
and acv.certification_period_name = agpv.period_name
and acv.certification_period_set_name = agpv.period_set_name
/** 10.19.2005 npanandi end ***/
ORDER BY execs.entity_id,execs.organization_id,execs.process_id asc;
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_PROCESS_DETAIL_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_PROCESS_DETAIL_F(
project_id
,process_org_rev_id
,fin_certification_id
,certification_id
,certification_status
,certification_type
,certification_period_name
,certification_period_set_name
,organization_id
,process_id
,natural_account_id
,significant_process_flag
,standard_process_flag
,process_category
,certification_result_code
,certified_by_id
,certified_on
,evaluation_result_code
,evaluated_by_id
,last_evaluated_on
/** 10.19.2005 npanandi begin ***/
,period_year
,period_num
,quarter_num
,ent_period_id
,ent_qtr_id
,ent_year_id
,report_date_julian
/** 10.19.2005 npanandi end ***/
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
select distinct evalopn.pk2_value, /*project_id*/
proc.process_org_rev_id,
finprocsum.FIN_CERTIFICATION_ID,
-10000, /*certification_id, cannot insert NULL here*/
acb.certification_status,
acb.certification_type,
acb.certification_period_name,
acb.certification_period_set_name,
o.organization_id,
proc.process_id,
aaa.natural_account_id,
nvl(proc.significant_process_flag,'N'),
nvl(proc.standard_process_flag,'N'),
proc.process_category,
certopn.audit_result_code, /*certification_result_code*/
certopn.authored_by, /*certified_by_id*/
certopn.authored_date, /*certified_on*/
evalopn.audit_result_code, /*evaluation_result_code*/
evalopn.authored_by, /*evaluated_by_id*/
evalopn.authored_date, /*last_evaluated_on*/
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_FIN_CERT_SCOPE finscope,
AMW_FIN_PROC_CERT_RELAN REL,
AMW_FIN_PROCESS_EVAL_SUM finprocsum,
HR_ALL_ORGANIZATION_UNITS o,
HR_ALL_ORGANIZATION_UNITS_TL otl,
AMW_PROCESS_ORGANIZATION_VL proc,
/*AMW_OPINIONS_V*/ AMW_OPINIONS_LOG_V certopn,
/*AMW_OPINIONS_V*/ AMW_OPINIONS_LOG_V evalopn,
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_gl_periods_v agpv
where rel.FIN_STMT_CERT_ID = finprocsum.FIN_CERTIFICATION_ID
and rel.end_date is null
and finprocsum.FIN_CERTIFICATION_ID = finscope.fin_certification_id
and finprocsum.ORGANIZATION_ID = finscope.ORGANIZATION_ID
and finprocsum.PROCESS_ID = finscope.PROCESS_ID
and finprocsum.PROCESS_ORG_REV_ID = proc.PROCESS_ORG_REV_ID
and o.organization_id = finscope.organization_id
and o.organization_id = otl.organization_id
and otl.language = userenv('LANG')
and finprocsum.EVAL_OPINION_log_ID = evalopn.opinion_log_id(+)
and finprocsum.cert_opinion_log_id = certopn.opinion_log_id(+)
/***and certopn.opinion_type_code = 'CERTIFICATION'
and certopn.object_name = 'AMW_ORG_PROCESS'
and certopn.pk1_value = finscope.process_id
and certopn.pk2_value = rel.PROC_CERT_ID
and certopn.pk3_value = finscope.organization_id
AND certopn.authored_date = (select max(aov2.authored_date) from AMW_OPINIONS aov2
where aov2.object_opinion_type_id = certopn.object_opinion_type_id
and aov2.pk3_value = certopn.pk3_value
AND aov2.pk2_value in (select proc_cert_Id
from AMW_FIN_PROC_CERT_RELAN
where fin_stmt_cert_id = finprocsum.FIN_CERTIFICATION_ID
and end_date is null)
and aov2.pk1_value = certopn.pk1_value)
***/
and finprocsum.FIN_CERTIFICATION_ID = acb.certification_id
and aaa.pk1(+) = finprocsum.organization_id
and aaa.pk2(+) = finprocsum.process_id
and acb.certification_period_name = agpv.period_name
and acb.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_PROCESS_DETAIL_F' ;
INSERT INTO RCI_PROCESS_DETAIL_F(
project_id
,process_org_rev_id
,fin_certification_id
,certification_id
,certification_status
,certification_type
,certification_period_name
,certification_period_set_name
,organization_id
,process_id
,natural_account_id
,significant_process_flag
,standard_process_flag
,process_category
,certification_result_code
,certified_by_id
,certified_on
,evaluation_result_code
,evaluated_by_id
,last_evaluated_on
/** 10.19.2005 npanandi begin ***/
,period_year
,period_num
,quarter_num
,ent_period_id
,ent_qtr_id
,ent_year_id
,report_date_julian
/** 10.19.2005 npanandi end ***/
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT distinct opinions_eval.pk2_value,
execs.PROCESS_ORG_REV_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,
afkav.natural_account_id,
nvl(process.significant_process_flag,'N'),
nvl(process.standard_process_flag,'N'),
process.process_category,
opinions_cert.audit_result_code,
opinions_cert.authored_by,
opinions_cert.authored_date,
opinions_eval.audit_result_code,
opinions_eval.authored_by,
opinions_eval.authored_date,
/** 10.19.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.19.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_certification_vl acv,
amw_audit_projects_v aapv,
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.19.2005 npanandi begin ***/
amw_gl_periods_v agpv
/** 10.19.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 execs.process_org_rev_id = proccert.process_org_rev_id
and execs.entity_id = acv.certification_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 aapv.audit_project_id(+) = opinions_eval.pk2_value
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.19.2005 npanandi begin ***/
and acv.certification_period_name = agpv.period_name
and acv.certification_period_set_name = agpv.period_set_name
/** 10.19.2005 npanandi end ***/
ORDER BY execs.entity_id,execs.organization_id,execs.process_id asc;
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_PROCESS_DETAIL_F' ;