[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select * from rci_open_issues_f;
insert into rci_open_issues_f(
change_id,
change_name,
description,
status_type,
status_code,
change_order_type_id,
change_mgmt_type_code,
initiation_date,
need_by_date,
priority_code,
reason_code,
certification_id,
organization_id,
process_id,
fin_cert_id,
fin_cert_type,
fin_cert_status,
open,
past_due,
age,
age_distribution_1,
age_distribution_2,
age_distribution_3,
age_distribution_4,
implementation_date,
cancellation_date,
period_year,
period_num,
quarter_num,
ent_year_id,
ent_qtr_id,
ent_period_id,
report_date_julian)(
select eec.change_id,
change_name,
eec.description,
status_type,
status_code,
change_order_type_id,
change_mgmt_type_code,
initiation_date,
need_by_date,
priority_code,
reason_code,
afpcr.PROC_CERT_ID,/** as certificationId,**/
null, /**as organizationId,**/
null, /**as processId,**/
afpcr.FIN_STMT_CERT_ID, /**as finCertId,**/
fin_cert.CERTIFICATION_TYPE, /**as finCertType,**/
fin_cert.CERTIFICATION_STATUS, /**as finCertStatus,**/
decode(status_code, 0, 0, 11, 0, 1),/** as open,**/
0,/** as pastDue,**/
0,/** as age,**/
0,/** as ageDistribution1,**/
0,/** as ageDistribution2,**/
0,/** as ageDistribution3,**/
0,/** as ageDistribution4,**/
implementation_date,
cancellation_date,
agpv.PERIOD_YEAR, /**as periodYear,**/
agpv.PERIOD_NUM, /**as periodNum,**/
agpv.QUARTER_NUM, /**as quarterNum,**/
ftd.ENT_YEAR_ID, /**as entYearId,**/
ftd.ENT_QTR_ID, /**as entQuarterId,**/
ftd.ENT_PERIOD_ID, /**as entPeriodId,**/
to_number(to_char(agpv.end_date,'J')) /**as reportDateJulian**/
from eng_engineering_changes eec,
eng_change_subjects ecs,
AMW_FIN_PROC_CERT_RELAN afpcr,
amw_certification_b proc_cert,
amw_certification_b fin_cert,
amw_gl_periods_v agpv,
fii_time_day ftd
where change_order_type_id in (select change_order_type_id
from eng_change_order_types
where type_classification='HEADER'
and change_mgmt_type_code='AMW_PROC_CERT_ISSUES')
and ecs.CHANGE_ID = eec.CHANGE_ID
and ecs.ENTITY_NAME = 'CERTIFICATION'
and afpcr.END_DATE is null
and proc_cert.CERTIFICATION_ID = ecs.PK1_VALUE
and proc_cert.OBJECT_TYPE = 'PROCESS'
and proc_cert.CERTIFICATION_ID = afpcr.PROC_CERT_ID
and afpcr.FIN_STMT_CERT_ID = fin_cert.CERTIFICATION_ID
and fin_cert.CERTIFICATION_PERIOD_NAME = agpv.PERIOD_NAME
and fin_cert.CERTIFICATION_PERIOD_SET_NAME = agpv.PERIOD_SET_NAME
and ftd.REPORT_DATE_JULIAN = to_number(to_char(agpv.END_DATE,'J')));
select pk1_value
into l_cert_id
from ENG_CHANGE_SUBJECTS where change_id = cur_rec.change_id
and entity_name = 'CERTIFICATION';
select pk1_value
into l_org_id
from ENG_CHANGE_SUBJECTS where change_id = cur_rec.change_id
and entity_name = 'ORGANIZATION';
select pk1_value
into l_proc_id
from ENG_CHANGE_SUBJECTS where change_id = cur_rec.change_id
and entity_name = 'PROCESS';
select cert2.certification_id
,cert2.CERTIFICATION_TYPE
,cert2.CERTIFICATION_STATUS,
agpv.period_year,
agpv.period_num,
agpv.quarter_num,
ftd.ent_period_id,
ftd.ent_qtr_id,
ftd.ent_year_id,
--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')),
ftd.ent_period_end_date,
ftd.ent_qtr_end_date,
ftd.ent_year_end_date
into l_FIN_CERT_ID, l_FIN_CERT_TYPE, l_FIN_CERT_STATUS,
l_period_year, l_period_num, l_quarter_num, l_ent_period_id,
l_ent_qtr_id, l_ent_year_id, l_report_date_julian,
l_ent_period_end, l_ent_qtr_end, l_ent_yr_end
from AMW_CERTIFICATION_B cert
,AMW_FIN_PROC_CERT_RELAN rln
,AMW_CERTIFICATION_B cert2
,amw_gl_periods_v agpv
,fii_time_day ftd
where cert.OBJECT_TYPE = 'PROCESS'
and rln.PROC_CERT_ID=cert.CERTIFICATION_ID
and rln.END_DATE IS NULL
and rln.fin_stmt_cert_id = cert2.certification_id
and cert.CERTIFICATION_ID = l_cert_id
and cert2.certification_period_name = agpv.period_name
and cert2.certification_period_set_name = agpv.period_set_name
and ftd.report_date_julian = to_number(to_char(agpv.end_date,'J'));
update rci_open_issues_f
set /*certification_id = l_cert_id,*/
organization_id = l_org_id,
process_id = l_proc_id,
past_due = l_past_due,
age = l_age,
age_distribution_1 = l_age_distribution_1,
age_distribution_2 = l_age_distribution_2,
age_distribution_3 = l_age_distribution_3,
age_distribution_4 = l_age_distribution_4/**,
FIN_CERT_ID = l_FIN_CERT_ID,
FIN_CERT_TYPE = l_FIN_CERT_TYPE,
FIN_CERT_STATUS = l_FIN_CERT_STATUS,
period_year = l_period_year,
period_num = l_period_num,
quarter_num = l_quarter_num,
ent_period_id = l_ent_period_id,
ent_qtr_id = l_ent_qtr_id,
ent_year_id = l_ent_year_id,
report_date_julian = l_report_date_julian**/
where change_id = cur_rec.change_id;
update rci_open_issues_f
set open_per = l_open_per,
past_due_per = l_past_due_per,
age_per = l_age_per,
age_distribution1_per = l_age_distribution1_per,
age_distribution2_per = l_age_distribution2_per,
age_distribution3_per = l_age_distribution3_per,
age_distribution4_per = l_age_distribution4_per,
open_yr = l_open_yr,
past_due_yr = l_past_due_yr,
age_yr = l_age_yr,
age_distribution1_yr = l_age_distribution1_yr,
age_distribution2_yr = l_age_distribution2_yr,
age_distribution3_yr = l_age_distribution3_yr,
age_distribution4_yr = l_age_distribution4_yr,
open_qtr = l_open_qtr,
past_due_qtr = l_past_due_qtr,
age_qtr = l_age_qtr,
age_distribution1_qtr = l_age_distribution1_qtr,
age_distribution2_qtr = l_age_distribution2_qtr,
age_distribution3_qtr = l_age_distribution3_qtr,
age_distribution4_qtr = l_age_distribution4_qtr
where change_id = cur_rec.change_id;
l_query0 := 'select organization_id as VIEWBYID
,name as VIEWBY
,-1000 as RCI_OPEN_ISSUE_MEASURE1
,sum(past_due) as RCI_OPEN_ISSUE_MEASURE2
,round(((sum(past_due)/count(change_id))*100),2) as RCI_OPEN_ISSUE_MEASURE3
,floor(sum(open_days)/count(change_id)) as RCI_OPEN_ISSUE_MEASURE4
,sum(age_buck1) as RCI_OPEN_ISSUE_MEASURE5
,sum(age_buck2) as RCI_OPEN_ISSUE_MEASURE6
,sum(age_buck3) as RCI_OPEN_ISSUE_MEASURE7
,sum(age_buck4) as RCI_OPEN_ISSUE_MEASURE8
/**,organization_id as RCI_ORG_CERT_URL1
,''ALL'' as RCI_ORG_CERT_URL2
,''ALL'' as RCI_ORG_CERT_URL3
,''ALL'' as RCI_ORG_CERT_URL4
,''ALL'' as RCI_ORG_CERT_URL5
from ( **/' ;
l_query1 := ' select distinct roif.organization_id
,aauv.name
,roif.change_id
,eec.change_name
,eec.initiation_date ';
l_query0 := 'select process_id as VIEWBYID
,display_name as VIEWBY
,-1000 as RCI_OPEN_ISSUE_MEASURE1
,sum(past_due) as RCI_OPEN_ISSUE_MEASURE2
,round(((sum(past_due)/count(change_id))*100),2) as RCI_OPEN_ISSUE_MEASURE3
,floor(sum(open_days)/count(change_id)) as RCI_OPEN_ISSUE_MEASURE4
,sum(age_buck1) as RCI_OPEN_ISSUE_MEASURE5
,sum(age_buck2) as RCI_OPEN_ISSUE_MEASURE6
,sum(age_buck3) as RCI_OPEN_ISSUE_MEASURE7
,sum(age_buck4) as RCI_OPEN_ISSUE_MEASURE8
/**,organization_id as RCI_ORG_CERT_URL1
,''ALL'' as RCI_ORG_CERT_URL2
,''ALL'' as RCI_ORG_CERT_URL3
,''ALL'' as RCI_ORG_CERT_URL4
,''ALL'' as RCI_ORG_CERT_URL5
from ( **/';
l_query1 := ' select distinct roif.process_id
,alrv.display_name
,roif.change_id
,eec.change_name
,eec.initiation_date ';
l_query0 := 'select fin_cert_id as VIEWBYID
,certification_name as VIEWBY
,-1000 as RCI_OPEN_ISSUE_MEASURE1
,sum(past_due) as RCI_OPEN_ISSUE_MEASURE2
,round(((sum(past_due)/count(change_id))*100),2) as RCI_OPEN_ISSUE_MEASURE3
,floor(sum(open_days)/count(change_id)) as RCI_OPEN_ISSUE_MEASURE4
,sum(age_buck1) as RCI_OPEN_ISSUE_MEASURE5
,sum(age_buck2) as RCI_OPEN_ISSUE_MEASURE6
,sum(age_buck3) as RCI_OPEN_ISSUE_MEASURE7
,sum(age_buck4) as RCI_OPEN_ISSUE_MEASURE8
/**,organization_id as RCI_ORG_CERT_URL1
,''ALL'' as RCI_ORG_CERT_URL2
,''ALL'' as RCI_ORG_CERT_URL3
,''ALL'' as RCI_ORG_CERT_URL4
,''ALL'' as RCI_ORG_CERT_URL5
from ( **/';
l_query1 := ' select distinct roif.fin_cert_id
,acv.certification_name
,roif.change_id
,eec.change_name
,eec.initiation_date ';
l_query0 := 'select status_code as VIEWBYID
,value as VIEWBY
,-1000 as RCI_OPEN_ISSUE_MEASURE1
,sum(past_due) as RCI_OPEN_ISSUE_MEASURE2
,round(((sum(past_due)/count(change_id))*100),2) as RCI_OPEN_ISSUE_MEASURE3
,floor(sum(open_days)/count(change_id)) as RCI_OPEN_ISSUE_MEASURE4
,sum(age_buck1) as RCI_OPEN_ISSUE_MEASURE5
,sum(age_buck2) as RCI_OPEN_ISSUE_MEASURE6
,sum(age_buck3) as RCI_OPEN_ISSUE_MEASURE7
,sum(age_buck4) as RCI_OPEN_ISSUE_MEASURE8
/**,organization_id as RCI_ORG_CERT_URL1
,''ALL'' as RCI_ORG_CERT_URL2
,''ALL'' as RCI_ORG_CERT_URL3
,''ALL'' as RCI_ORG_CERT_URL4
,''ALL'' as RCI_ORG_CERT_URL5
from ( **/';
l_query1 := ' select distinct roif.status_code
,ripv.value
,roif.change_id
,eec.change_name
,eec.initiation_date ';
l_query0 := 'select priority_code as VIEWBYID
,value as VIEWBY
,-1000 as RCI_OPEN_ISSUE_MEASURE1
,sum(past_due) as RCI_OPEN_ISSUE_MEASURE2
,round(((sum(past_due)/count(change_id))*100),2) as RCI_OPEN_ISSUE_MEASURE3
,floor(sum(open_days)/count(change_id)) as RCI_OPEN_ISSUE_MEASURE4
,sum(age_buck1) as RCI_OPEN_ISSUE_MEASURE5
,sum(age_buck2) as RCI_OPEN_ISSUE_MEASURE6
,sum(age_buck3) as RCI_OPEN_ISSUE_MEASURE7
,sum(age_buck4) as RCI_OPEN_ISSUE_MEASURE8
/**,organization_id as RCI_ORG_CERT_URL1
,''ALL'' as RCI_ORG_CERT_URL2
,''ALL'' as RCI_ORG_CERT_URL3
,''ALL'' as RCI_ORG_CERT_URL4
,''ALL'' as RCI_ORG_CERT_URL5
from ( **/';
l_query1 := ' select distinct roif.priority_code
,ripv.value
,roif.change_id
,eec.change_name
,eec.initiation_date ';
l_query0 := 'select reason_code as VIEWBYID
,value as VIEWBY
,-1000 as RCI_OPEN_ISSUE_MEASURE1
,sum(past_due) as RCI_OPEN_ISSUE_MEASURE2
,round(((sum(past_due)/count(change_id))*100),2) as RCI_OPEN_ISSUE_MEASURE3
,floor(sum(open_days)/count(change_id)) as RCI_OPEN_ISSUE_MEASURE4
,sum(age_buck1) as RCI_OPEN_ISSUE_MEASURE5
,sum(age_buck2) as RCI_OPEN_ISSUE_MEASURE6
,sum(age_buck3) as RCI_OPEN_ISSUE_MEASURE7
,sum(age_buck4) as RCI_OPEN_ISSUE_MEASURE8
/**,organization_id as RCI_ORG_CERT_URL1
,''ALL'' as RCI_ORG_CERT_URL2
,''ALL'' as RCI_ORG_CERT_URL3
,''ALL'' as RCI_ORG_CERT_URL4
,''ALL'' as RCI_ORG_CERT_URL5
from ( **/';
l_query1 := ' select distinct roif.reason_code
,rirv.value
,roif.change_id
,eec.change_name
,eec.initiation_date ';
select distinct last_day(to_date(to_char(ent_period_end_date,'YYYYMM'),'YYYYMM'))
into l_end_date
from fii_time_day
where ent_period_id=p_param(i).parameter_id;
select min(distinct last_day(to_date(to_char(ent_period_start_date,'YYYYMM'),'YYYYMM')))
into l_start_date /*gives in the form 30-SEP-06*/
from fii_time_day
where ent_period_id=p_param(i).parameter_id;
or (eec.status_code=11 and eec.last_update_date > to_date('''||l_end_date||''',''DD-MON-YYYY'')))';
select distinct last_day(to_date(to_char(ent_qtr_end_date,'YYYYMM'),'YYYYMM'))
into l_end_date
from fii_time_day
where ent_qtr_id=p_param(i).parameter_id;
select min(distinct last_day(to_date(to_char(ent_qtr_start_date,'YYYYMM'),'YYYYMM')))
into l_start_date /*gives in the form 30-SEP-06*/
from fii_time_day
where ent_qtr_id=p_param(i).parameter_id;
or (eec.status_code=11 and eec.last_update_date > to_date('''||l_end_date||''',''DD-MON-YYYY'')))';
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_param(i).parameter_id;
select min(distinct last_day(to_date(to_char(ent_year_start_date,'YYYYMM'),'YYYYMM')))
into l_start_date /*gives in the form 30-SEP-06*/
from fii_time_day
where ent_year_id=p_param(i).parameter_id;
or (eec.status_code=11 and eec.last_update_date > to_date('''||l_end_date||''',''DD-MON-YYYY'')))';
and ((eec.status_code not in (0,11)) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('||v_yyyymm||',''YYYYMM''))))';
l_act_sqlstmt := 'select VIEWBYID,VIEWBY,RCI_OPEN_ISSUE_MEASURE1,RCI_OPEN_ISSUE_MEASURE2
,RCI_OPEN_ISSUE_MEASURE3,RCI_OPEN_ISSUE_MEASURE4
,RCI_OPEN_ISSUE_MEASURE5,RCI_OPEN_ISSUE_MEASURE6
,RCI_OPEN_ISSUE_MEASURE7,RCI_OPEN_ISSUE_MEASURE8
,RCI_ORG_CERT_URL1,RCI_ORG_CERT_URL2,RCI_ORG_CERT_URL3
,RCI_ORG_CERT_URL4,RCI_ORG_CERT_URL5
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_query0||l_query1||l_query2||l_query22||l_query3||l_query4||'
) t ) a
order by a.col_rank ';