[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_getcnt := 'select count(1) from (
select distinct rocsf.fin_certification_id, organization_id
from rci_org_cert_summ_f rocsf
,fii_time_day ftd
where 1=1 and rocsf.report_date_julian = ftd.report_date_julian ';
l_sqlstmt := 'select round((nvl(a1.not_certified,0)*100/'||l_total||'),2) as RCI_ORG_CERT_MEASURE1
,round((nvl(a2.certified_w_issues,0)*100/'||l_total||'),2) as RCI_ORG_CERT_MEASURE2
,round((nvl(a3.certified,0)*100/'||l_total||'),2) as RCI_ORG_CERT_MEASURE3
from ';
l_qry1 := '(select count(1) as certified
from (select distinct organization_id,fin_certification_id
from rci_org_cert_summ_f rocsf, fii_time_day ftd
where rocsf.report_date_julian = ftd.report_date_julian
and rocsf.org_certification_status=''EFFECTIVE'''||l_from_clause||')) a3, ';
l_qry2 := '(select count(1) as certified_w_issues
from (select distinct organization_id,fin_certification_id
from rci_org_cert_summ_f rocsf, fii_time_day ftd
where rocsf.report_date_julian = ftd.report_date_julian
and org_certification_status <> ''EFFECTIVE'''||l_from_clause||')) a2, ';
l_qry3 := '(select count(1) as not_certified
from (select distinct organization_id,fin_certification_id
from rci_org_cert_summ_f rocsf, fii_time_day ftd
where rocsf.report_date_julian = ftd.report_date_julian
and org_certification_status is null '||l_from_clause||')) a1';
l_issues_sql := ' (select roif.organization_id,count(distinct roif.change_id) as open_issues
from rci_open_issues_f roif,eng_engineering_changes eec
where eec.change_id=roif.change_id and roif.certification_id is not null and roif.organization_id is not null ';
l_outer_sql := 'select name VIEWBY
,0 RCI_GRAND_TOTAL
,rocsf.organization_id RCI_ORG_CERT_MEASURE1
,nvl(ro1.org_certified_with_issues,0) RCI_ORG_CERT_MEASURE2
,nvl(ro2.org_certified,0) RCI_ORG_CERT_MEASURE3
,nvl(ro3.org_not_certified,0) RCI_ORG_CERT_MEASURE4
,/*sum(proc_w_ineff_ctrls)*/ nvl(def.processes,0) RCI_ORG_CERT_MEASURE5
,sum(proc_certified_with_issues) RCI_ORG_CERT_MEASURE6
,sum(proc_certified) RCI_ORG_CERT_MEASURE7
,sum(proc_not_certified) RCI_ORG_CERT_MEASURE8
,/*sum(unmitigated_risks)*/ nvl(risk.risk_id,0) RCI_ORG_CERT_MEASURE9
,/*sum(ineffective_controls)*/ nvl(ctrls.controls,0) RCI_ORG_CERT_MEASURE10
,/**sum(**/nvl(op.open_issues,0)/**)**/ RCI_ORG_CERT_MEASURE11
,org_id RCI_ORG_CERT_URL1 from ( ';
l_inner_sql := 'select distinct rocsf.fin_certification_id
, aauv.name
,rocsf.organization_id
,rocsf.org_certified_with_issues
,rocsf.org_certified
,rocsf.org_not_certified
,rocsf.process_id
,rocsf.proc_w_ineff_ctrls
,rocsf.proc_certified_with_issues
,rocsf.proc_certified
,rocsf.proc_not_certified
,rocsf.unmitigated_risks
,rocsf.organization_id org_id
,rocsf.report_date_julian
from rci_org_cert_summ_f rocsf
,amw_audit_units_v aauv
,fii_time_day ftd
where rocsf.organization_id = aauv.organization_id
and rocsf.report_date_julian = ftd.report_date_julian ';
) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';
) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';
) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';
l_org_statuses := ' (select organization_id,count(fin_certification_id) as org_certified_with_issues from (
select distinct fin_certification_id,rocsf.report_date_julian,rocsf.organization_id
from rci_org_cert_summ_f rocsf,fii_time_day ftd
where rocsf.report_date_julian=ftd.report_date_julian and org_certification_status=''INEFFECTIVE'' '||l_org_status_where1||' ) group by organization_id) ro1,
(select organization_id,count(fin_certification_id) as org_certified from (
select distinct fin_certification_id,rocsf.report_date_julian,rocsf.organization_id
from rci_org_cert_summ_f rocsf,fii_time_day ftd
where rocsf.report_date_julian=ftd.report_date_julian and org_certification_status=''EFFECTIVE'' '||l_org_status_where2||' ) group by organization_id) ro2,
(select organization_id,count(fin_certification_id) as org_not_certified from (
select distinct fin_certification_id,rocsf.report_date_julian,rocsf.organization_id
from rci_org_cert_summ_f rocsf,fii_time_day ftd
where rocsf.report_date_julian=ftd.report_date_julian and org_certification_status is null '||l_org_status_where3||' ) group by organization_id) ro3,';
l_def_proc_sql := '(select organization_id,count(process_id) as processes from (
select distinct process_id,organization_id,fin_certification_id,certification_result_code,evaluation_result_code
from rci_org_proc_dfcy_f ropdf, fii_time_day ftd
where ropdf.report_date_julian=ftd.report_date_julian
'||l_def_proc_where||' ) group by organization_id) def, ';
l_ctrls_sql := '(select organization_id,count(distinct control_id) as controls
from RCI_ORG_CERT_CTRLS_F roccf,fii_time_day ftd where 1=1 and roccf.report_date_julian = ftd.report_date_julian '||l_ctrls_from||'
group by organization_id) ctrls, ';
l_risks_sql := '(select organization_id,count(risk_id) as risk_id from (select distinct organization_id,process_id,risk_id
from RCI_ORG_CERT_RISKS_F roccf,fii_time_day ftd
where roccf.report_date_julian = ftd.report_date_julian and audit_result_code <> ''EFFECTIVE'' and audit_result_code is not null
'||l_risks_from||' )
group by organization_id) risk
where rocsf.organization_id = op.organization_id(+)
and rocsf.organization_id = ro1.organization_id(+)
and rocsf.organization_id = ro2.organization_id(+)
and rocsf.organization_id = ro3.organization_id(+)
and rocsf.organization_id = ctrls.organization_id(+)
and rocsf.organization_id = risk.organization_id(+)
and rocsf.organization_id = def.organization_id(+)
group by name,rocsf.organization_id,ro1.org_certified_with_issues,ro2.org_certified,ro3.org_not_certified,def.processes,ctrls.controls,risk.risk_id,op.open_issues ';
l_act_sqlstmt := 'select VIEWBY,RCI_GRAND_TOTAL,RCI_ORG_CERT_MEASURE1
,RCI_ORG_CERT_MEASURE2,RCI_ORG_CERT_MEASURE3
,RCI_ORG_CERT_MEASURE4,RCI_ORG_CERT_MEASURE5
,RCI_ORG_CERT_MEASURE6,RCI_ORG_CERT_MEASURE7
,RCI_ORG_CERT_MEASURE8,RCI_ORG_CERT_MEASURE9
,RCI_ORG_CERT_MEASURE10,RCI_ORG_CERT_MEASURE11
,RCI_ORG_CERT_URL1
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_sqlstmt ||'
) t ) a
order by a.col_rank ';
l_getcnt := 'select count(1) from (
select distinct nvl(rocsf.fin_certification_id,0) certification_id,
nvl(rocsf.organization_id,0) organization_id,
nvl(rocsf.org_certification_status,''NOT_CERTIFIED'') org_certification_status
from rci_org_cert_summ_f rocsf
,fii_time_day ftd
where 1=1 and rocsf.report_date_julian = ftd.report_date_julian ';
l_sqlstmt := 'select rbcrv.value VIEWBY
,count(rocsf.organization_id) RCI_ORG_CERT_MEASURE1 ';
,(select distinct nvl(rocsf.fin_certification_id,0) certification_id,
nvl(rocsf.organization_id,0) organization_id,
/**nvl(rocsf.org_certification_status,''NOT_CERTIFIED'') org_certification_status**/
decode(rocsf.org_certification_status,null,''NOT_CERTIFIED'',''EFFECTIVE'',''EFFECTIVE'',''INEFFECTIVE'') org_certification_status
from rci_org_cert_summ_f rocsf
,fii_time_day ftd
where 1=1 and rocsf.report_date_julian = ftd.report_date_julian ';
l_act_sqlstmt := 'select VIEWBY
,RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2,RCI_ORG_CERT_MEASURE3,RCI_ORG_CERT_URL1
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_sqlstmt || l_from_clause || l_grp_by||'
) t ) a
order by a.col_rank ';
l_sqlstmt := 'select distinct art.name RCI_ORG_CERT_MEASURE1
,aapv.project_name RCI_ORG_CERT_MEASURE2
,aauv.name RCI_ORG_CERT_MEASURE3
,apv.display_name RCI_ORG_CERT_MEASURE4
,/*nvl(arb.material,''N'')*/ flv.meaning RCI_ORG_CERT_MEASURE5
,al1.meaning RCI_ORG_CERT_MEASURE6
,al2.meaning RCI_ORG_CERT_MEASURE7
,rfaev.value RCI_ORG_CERT_MEASURE8
,papf.full_name RCI_ORG_CERT_MEASURE9
,rocrf.last_evaluated_on RCI_ORG_CERT_MEASURE10
,arb.risk_rev_id RCI_ORG_CERT_URL1
,arb.risk_id RCI_ORG_CERT_URL2
,rocrf.process_id RCI_ORG_CERT_URL3
,rocrf.organization_id RCI_ORG_CERT_URL4
from rci_org_cert_risks_f rocrf
,amw_risks_b arb
,amw_risks_tl art
,amw_audit_projects_v aapv
,amw_audit_units_v aauv
,amw_process_vl apv
,amw_lookups al1
,amw_lookups al2
,RCI_FS_ACCT_EVAL_V rfaev
,PER_ALL_PEOPLE_F papf
,FND_USER fu
/** 10.20.2005 npanandi begin ***/
,fii_time_day ftd
/** 10.20.2005 npanandi end ***/
/**01.31.2006 npanandi: changing reference to fnd_lookups below because
of lang issues **/
,/*fnd_lookup_values*/fnd_lookups flv
where arb.risk_rev_id = art.risk_rev_id
and upper(arb.curr_approved_flag) = ''Y''
and art.language = userenv(''LANG'')
and arb.risk_id = rocrf.risk_id
and rocrf.organization_id = aauv.organization_id
and aapv.audit_project_id = rocrf.project_id
and aauv.organization_id = rocrf.organization_id
and apv.process_id = rocrf.process_id
and apv.approval_date is not null
and apv.approval_end_date is null
and rocrf.risk_impact = al1.lookup_code(+)
and al1.lookup_type(+) = ''AMW_IMPACT''
and al1.enabled_flag(+) = ''Y''
and rocrf.likelihood = al2.lookup_code(+)
and al2.lookup_type(+) = ''AMW_LIKELIHOOD''
and al2.enabled_flag(+) = ''Y''
and rocrf.audit_result_code = rfaev.id
and rfaev.OBJ_NAME = ''AMW_ORG_PROCESS_RISK''
and rocrf.last_evaluator_id = fu.user_id
and fu.employee_id = papf.person_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and papf.employee_number is not null
and rocrf.report_date_julian = ftd.report_date_julian
and nvl(arb.material,''N'') = flv.lookup_code(+)
and flv.lookup_type(+) = ''AMW_YES_NO''
AND rocrf.audit_result_code <> ''EFFECTIVE'' ';
l_act_sqlstmt := 'select RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2
,RCI_ORG_CERT_MEASURE3,RCI_ORG_CERT_MEASURE4
,RCI_ORG_CERT_MEASURE5,RCI_ORG_CERT_MEASURE6
,RCI_ORG_CERT_MEASURE7,RCI_ORG_CERT_MEASURE8
,RCI_ORG_CERT_MEASURE9,RCI_ORG_CERT_MEASURE10
,RCI_ORG_CERT_URL1,RCI_ORG_CERT_URL2
,RCI_ORG_CERT_URL3,RCI_ORG_CERT_URL4
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_sqlstmt || l_from_clause||'
) t ) a
order by a.col_rank ';
l_sqlstmt := 'select distinct act.name RCI_ORG_CERT_MEASURE1
,act.description RCI_ORG_CERT_MEASURE2
,aauv.name RCI_ORG_CERT_MEASURE3
,al1.meaning RCI_ORG_CERT_MEASURE4
,fl1.meaning RCI_ORG_CERT_MEASURE5
,fl2.meaning RCI_ORG_CERT_MEASURE6
,rfaev.value RCI_ORG_CERT_MEASURE7
,aovt1.OPINION_VALUE_NAME RCI_ORG_CERT_MEASURE8
,aovt2.OPINION_VALUE_NAME RCI_ORG_CERT_MEASURE9
,papf.FULL_NAME RCI_ORG_CERT_MEASURE10
,roccf.last_evaluated_on RCI_ORG_CERT_MEASURE11
,acb.control_rev_id RCI_ORG_CERT_URL1
,acb.control_id RCI_ORG_CERT_URL2
from rci_org_cert_ctrls_f roccf
,amw_controls_b acb
,amw_controls_tl act
,amw_audit_units_v aauv
,amw_lookups al1
,fnd_lookups fl1
,fnd_lookups fl2
,amw_opinion_values_tl aovt1
,amw_opinion_values_tl aovt2
,RCI_FS_ACCT_EVAL_V rfaev
,PER_ALL_PEOPLE_F papf
,FND_USER fu
/** 10.20.2005 npanandi begin ***/
,fii_time_day ftd
/** 10.20.2005 npanandi end ***/
where acb.control_rev_id = act.control_rev_id
and upper(acb.curr_approved_flag) = ''Y''
and act.language = userenv(''LANG'')
and acb.control_id = roccf.control_id
and roccf.organization_id = aauv.organization_id
and al1.LOOKUP_CODE = acb.control_type/*roccf.CONTROL_TYPE*/
and al1.LOOKUP_TYPE = ''AMW_CONTROL_TYPE''
and fl1.LOOKUP_CODE = roccf.KEY_CONTROL
and fl1.LOOKUP_TYPE = ''YES_NO''
and fl2.LOOKUP_CODE = roccf.DISCLOSURE_CONTROL
and fl2.LOOKUP_TYPE = ''YES_NO''
and roccf.audit_result_code = rfaev.id
and rfaev.OBJ_NAME = ''AMW_ORG_CONTROL''
and roccf.DES_EFF_ID = aovt1.OPINION_VALUE_ID(+)
and aovt1.LANGUAGE(+) = userenv(''LANG'')
and roccf.OP_EFF_ID = aovt2.OPINION_VALUE_ID(+)
and aovt2.LANGUAGE(+) = userenv(''LANG'')
and roccf.last_evaluated_by_id = fu.user_id
and fu.employee_id = papf.person_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and papf.employee_number is not null
and roccf.report_date_julian = ftd.report_date_julian';
l_act_sqlstmt := 'select RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2
,RCI_ORG_CERT_MEASURE3,RCI_ORG_CERT_MEASURE4
,RCI_ORG_CERT_MEASURE5,RCI_ORG_CERT_MEASURE6
,RCI_ORG_CERT_MEASURE7,RCI_ORG_CERT_MEASURE8
,RCI_ORG_CERT_MEASURE9,RCI_ORG_CERT_MEASURE10
,RCI_ORG_CERT_MEASURE11,RCI_ORG_CERT_URL1,RCI_ORG_CERT_URL2
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_sqlstmt || l_from_clause||'
) t ) a
order by a.col_rank ';
select min(distinct last_day(to_date(to_char(ent_period_end_date,'YYYYMM'),'YYYYMM')))
into l_end_date /*gives in the form 30-SEP-06*/
from fii_time_day
where ent_period_id=p_page_parameter_tbl(i).parameter_id;
) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';
select min(distinct last_day(to_date(to_char(ent_qtr_end_date,'YYYYMM'),'YYYYMM')))
into l_end_date /*gives in the form 30-SEP-06*/
from fii_time_day
where ent_qtr_id=p_page_parameter_tbl(i).parameter_id;
) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';
select min(distinct last_day(to_date(to_char(ent_year_end_date,'YYYYMM'),'YYYYMM')))
into l_end_date /*gives in the form 30-SEP-06*/
from fii_time_day
where ent_year_id=p_page_parameter_tbl(i).parameter_id;
) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';
l_sqlstmt := 'select distinct eec.change_name RCI_ORG_CERT_MEASURE1
,aecv.full_name RCI_ORG_CERT_MEASURE2
,ecst.status_name RCI_ORG_CERT_MEASURE3
,ecp.description RCI_ORG_CERT_MEASURE4
,round((sysdate - eec.initiation_date)) RCI_ORG_CERT_MEASURE5
,eec.need_by_date RCI_ORG_CERT_MEASURE6
/*,decode(eec.need_by_date,null,round(sysdate - eec.initiation_date),round(sysdate - eec.need_by_date)) RCI_ORG_CERT_MEASURE7*/
,to_number(to_char(to_date('''||l_end_date||''',''DD-MON-YYYY''),''J'')) - to_number(to_char(eec.need_by_date,''J'')) RCI_ORG_CERT_MEASURE7
, eec.change_notice RCI_ORG_CERT_MEASURE9
,eec.change_id RCI_ORG_CERT_URL1
from rci_open_issues_f open_issues,
/*amw_audit_units_v aauv,*/
amw_latest_revisions_v alrv,
eng_engineering_changes eec,
amw_employees_current_v aecv,
eng_change_statuses_tl ecst,
eng_change_priorities ecp,
fii_time_day ftd
where /*aauv.organization_id=open_issues.organization_id(+)
and*/ open_issues.change_id=eec.change_id
and aecv.party_id = eec.assignee_id
and eec.status_code = ecst.status_code
and ecst.language = userenv(''LANG'')
and eec.priority_code = ecp.eng_change_priority_code(+)
/*and nvl(open_issues.process_id,-1) = nvl(alrv.process_id,-1)
and open_issues.certification_id is not null
and open_issues.organization_id is not null*/
and open_issues.report_date_julian = ftd.report_date_julian';
l_act_sqlstmt := 'select RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2,RCI_ORG_CERT_MEASURE3
,RCI_ORG_CERT_MEASURE4,RCI_ORG_CERT_MEASURE5,RCI_ORG_CERT_MEASURE6
,RCI_ORG_CERT_MEASURE7,RCI_ORG_CERT_MEASURE9,RCI_ORG_CERT_URL1
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_sqlstmt || l_from_clause||'
) t ) a
order by a.col_rank ';
l_sqlstmt := 'select distinct alrv.display_name RCI_ORG_CERT_MEASURE1
,aauv.name RCI_ORG_CERT_MEASURE2
,fl1.meaning RCI_ORG_CERT_MEASURE3
,fl2.meaning RCI_ORG_CERT_MEASURE4
,acv.certification_name RCI_ORG_CERT_MEASURE5
,rbcrv.value RCI_ORG_CERT_MEASURE6
,papf1.full_name RCI_ORG_CERT_MEASURE7
,aapv.project_name RCI_ORG_CERT_MEASURE8
,rfaev.value RCI_ORG_CERT_MEASURE9
,papf2.full_name RCI_ORG_CERT_MEASURE10
,ropdf.last_evaluated_on RCI_ORG_CERT_MEASURE11
,ropdf.unmitigated_risks RCI_ORG_CERT_MEASURE12
,ropdf.ineffective_controls RCI_ORG_CERT_MEASURE13
,alrv.process_id RCI_ORG_CERT_URL1
,aauv.organization_id RCI_ORG_CERT_URL2
,alrv.revision_number RCI_ORG_CERT_URL3
from rci_org_proc_dfcy_f ropdf
,amw_latest_revisions_v alrv
,amw_audit_units_v aauv
,fnd_lookups fl1
,fnd_lookups fl2
,amw_certification_vl acv
,RCI_BP_CERT_RESULT_V rbcrv
,(select full_name,person_id from PER_ALL_PEOPLE_F where employee_number is not null and (trunc(sysdate) between effective_start_date and effective_end_date)) papf1
,FND_USER fu1
,amw_audit_projects_v aapv
,rci_fs_acct_eval_v rfaev
,(select full_name,person_id from PER_ALL_PEOPLE_F where employee_number is not null and (trunc(sysdate) between effective_start_date and effective_end_date)) papf2
,FND_USER fu2
/** 10.20.2005 npanandi begin ***/
,fii_time_day ftd
/** 10.20.2005 npanandi end ***/
where ropdf.process_id = alrv.process_id
and ropdf.organization_id = aauv.organization_id
and ropdf.significant_process_flag = fl1.lookup_code
and fl1.lookup_type = ''YES_NO''
and ropdf.standard_process_flag = fl2.lookup_code
and fl2.lookup_type = ''YES_NO''
and ropdf.fin_certification_id = acv.certification_id
/**and acv.object_type = ''PROCESS''**/
and nvl(ropdf.certification_result_code,''NOT_CERTIFIED'') = rbcrv.id(+)
and ropdf.certified_by_id = fu1.user_id(+)
and fu1.employee_id = papf1.person_id(+)
and ropdf.project_id = aapv.audit_project_id(+)
and ropdf.evaluation_result_code = rfaev.id(+)
and rfaev.obj_name(+)=''AMW_ORG_PROCESS''
and ropdf.last_evaluated_by_id = fu2.user_id(+)
and fu2.employee_id = papf2.person_id(+)
and ropdf.report_date_julian = ftd.report_date_julian';
l_act_sqlstmt := 'select RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2
,RCI_ORG_CERT_MEASURE3,RCI_ORG_CERT_MEASURE4
,RCI_ORG_CERT_MEASURE5,RCI_ORG_CERT_MEASURE6
,RCI_ORG_CERT_MEASURE7,RCI_ORG_CERT_MEASURE8
,RCI_ORG_CERT_MEASURE9,RCI_ORG_CERT_MEASURE10
,RCI_ORG_CERT_MEASURE11,RCI_ORG_CERT_MEASURE12,RCI_ORG_CERT_MEASURE13
,RCI_ORG_CERT_URL1,RCI_ORG_CERT_URL2,RCI_ORG_CERT_URL3
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_sqlstmt || l_from_clause||'
) t ) a
order by a.col_rank ';
l_sqlstmt := 'select distinct aauv.name RCI_ORG_CERT_MEASURE13
, acv.certification_name RCI_ORG_CERT_MEASURE1
,al1.meaning RCI_ORG_CERT_MEASURE2
,aecv1.full_name RCI_ORG_CERT_MEASURE3
,''Q''||agpv.quarter_num RCI_ORG_CERT_MEASURE4
,agpv.period_year RCI_ORG_CERT_MEASURE5
,al2.meaning RCI_ORG_CERT_MEASURE6
,acv.certification_creation_date RCI_ORG_CERT_MEASURE7
,acv.target_completion_date RCI_ORG_CERT_MEASURE8
,sum(proc_not_certified) RCI_ORG_CERT_MEASURE9
,rbocrv.value RCI_ORG_CERT_MEASURE10
,papf.full_name RCI_ORG_CERT_MEASURE11
,rocsf.org_certified_on RCI_ORG_CERT_MEASURE12
,acv.certification_id RCI_ORG_CERT_URL1
from rci_org_cert_summ_f rocsf
,amw_certification_vl acv
,amw_lookups al1
,amw_employees_current_v aecv1
,amw_gl_periods_v agpv
,amw_lookups al2
,(select papf.full_name,fu.user_id from PER_ALL_PEOPLE_F papf,fnd_user fu where fu.employee_id = papf.person_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and papf.employee_number is not null) papf
,RCI_BP_ORG_CERT_RESULT_V rbocrv
,hr_all_organization_units_tl aauv
/** 10.20.2005 npanandi begin ***/
,fii_time_day ftd
/** 10.20.2005 npanandi end ***/
where rocsf.fin_certification_id = acv.certification_id
and acv.object_type = ''FIN_STMT''
and rocsf.certification_type = al1.lookup_code
and al1.lookup_type = ''AMW_FINSTMT_CERTIFICATION_TYPE''
and rocsf.certification_owner_id = aecv1.party_id
and rocsf.certification_period_name = agpv.period_name
and rocsf.certification_period_set_name = agpv.period_set_name
and rocsf.certification_status = al2.lookup_code
and al2.lookup_type = ''AMW_PROC_CERTIFICATION_STATUS''
and rocsf.org_certified_by = papf.user_id(+)
and nvl(rocsf.org_certification_status,''NOT_CERTIFIED'') = rbocrv.id(+)
and rocsf.organization_id = aauv.organization_id
and aauv.language = userenv(''LANG'')
and rocsf.report_date_julian = ftd.report_date_julian
/**** group by acv.certification_name,al1.meaning,aecv1.full_name,agpv.quarter_num,agpv.period_year,al2.meaning
,acv.certification_creation_date,acv.target_completion_date,aauv.name,rbocrv.value,papf.full_name
,rocsf.org_certified_on
order by acv.certification_name,aauv.name ****/ ';
l_act_sqlstmt := 'select RCI_ORG_CERT_MEASURE13, RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2
,RCI_ORG_CERT_MEASURE3,RCI_ORG_CERT_MEASURE4
,RCI_ORG_CERT_MEASURE5,RCI_ORG_CERT_MEASURE6
,RCI_ORG_CERT_MEASURE7,RCI_ORG_CERT_MEASURE8
,RCI_ORG_CERT_MEASURE9,RCI_ORG_CERT_MEASURE10
,RCI_ORG_CERT_MEASURE11,RCI_ORG_CERT_MEASURE12,RCI_ORG_CERT_URL1
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_sqlstmt || l_from_clause|| l_group_by || '
) t ) a
order by a.col_rank ';
l_sqlstmt := 'select distinct apov.display_name RCI_ORG_CERT_MEASURE1
,aauv.name RCI_ORG_CERT_MEASURE2
,al1.meaning RCI_ORG_CERT_MEASURE3
,al2.meaning RCI_ORG_CERT_MEASURE4
,al3.meaning RCI_ORG_CERT_MEASURE5
,acv.certification_name RCI_ORG_CERT_MEASURE6
,rbcrv.value RCI_ORG_CERT_MEASURE7
,rpdf.certified_on RCI_ORG_CERT_MEASURE8
,aapv.project_name RCI_ORG_CERT_MEASURE9
,rfaev.value RCI_ORG_CERT_MEASURE10
,papf.full_name RCI_ORG_CERT_MEASURE11
,rpdf.last_evaluated_on RCI_ORG_CERT_MEASURE12
,apov.process_id RCI_ORG_CERT_URL1
,apov.organization_id RCI_ORG_CERT_URL2
,apov.revision_number RCI_ORG_CERT_URL3
from rci_process_detail_f rpdf
,amw_process_organization_vl apov
,amw_audit_units_v aauv
,amw_lookups al1
,amw_lookups al2
,amw_lookups al3
,amw_certification_vl acv
,RCI_BP_CERT_RESULT_V rbcrv
,amw_audit_projects_v aapv
/*** ,RCI_FS_ACCT_EVAL_V rfaev ***/
,(select id,value from RCI_FS_ACCT_EVAL_V where obj_name=''AMW_ORG_PROCESS'') rfaev
,(select papf.full_name,fu.user_id from PER_ALL_PEOPLE_F papf,fnd_user fu where fu.employee_id = papf.person_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and papf.employee_number is not null) papf
/** 10.19.2005 npanandi begin ***/
,fii_time_day ftd
/** 10.19.2005 npanandi end ***/
where rpdf.process_org_rev_id = apov.process_org_rev_id
and rpdf.process_id = apov.process_id
and rpdf.organization_id = apov.organization_id
and rpdf.organization_id = aauv.organization_id
and rpdf.significant_process_flag = al1.lookup_code
and al1.lookup_type = ''AMW_SIGNIFICANT_PROCESS''
and rpdf.standard_process_flag = al2.lookup_code
and al2.lookup_type = ''AMW_STANDARD_PROCESS''
and rpdf.process_category = al3.lookup_code(+)
and al3.lookup_type(+) = ''AMW_PROCESS_CATEGORY''
and rpdf.fin_certification_id = acv.certification_id
/**01.25.2006 npanandi: bug 5000369 fix**/
/**and rpdf.certification_result_code = rbcrv.id(+)**/
and nvl(rpdf.certification_result_code,''NOT_CERTIFIED'') = rbcrv.id(+)and rpdf.project_id = aapv.audit_project_id(+)
and rpdf.evaluation_result_code = rfaev.id(+)
and rpdf.evaluated_by_id = papf.user_id(+)
and rpdf.report_date_julian = ftd.report_date_julian';
l_act_sqlstmt := 'select RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2
,RCI_ORG_CERT_MEASURE3,RCI_ORG_CERT_MEASURE4
,RCI_ORG_CERT_MEASURE5,RCI_ORG_CERT_MEASURE6
,RCI_ORG_CERT_MEASURE7,RCI_ORG_CERT_MEASURE8
,RCI_ORG_CERT_MEASURE9,RCI_ORG_CERT_MEASURE10
,RCI_ORG_CERT_MEASURE11,RCI_ORG_CERT_MEASURE12
,RCI_ORG_CERT_URL1,RCI_ORG_CERT_URL2,RCI_ORG_CERT_URL3
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_sqlstmt || l_from_clause||'
) t ) a
order by a.col_rank ';
select to_char(sysdate,'YYYY') into l_current_year from dual;