[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
null VIEWBY
,nvl(sum(r_open),0) RCI_PROC_CERT_MEASURE1
,nvl(sum(f_open),0) RCI_PROC_CERT_MEASURE2
FROM
(
SELECT
sum(f.open) f_open
,0 r_open
FROM
RCI_OPEN_FINDINGS_F f
WHERE f.open=1 AND f.age_in_days >= 0
and f.organization_id is not null
'|| f_where_clause || '
UNION
SELECT
0 f_open
,sum(r.open) r_open
FROM
RCI_OPEN_REMEDIATIONS_F r
WHERE r.open=1 AND r.age_in_days >= 0
and r.organization_id is not null
'|| r_where_clause || '
) ';
AND (proc.REVISION_NUMBER = (select max(REVISION_NUMBER) from amw_process_vl where proc.process_id=process_id)
OR proc.process_id is null ) ';
l_sqlstmt :='SELECT DISTINCT
'|| view_by_col ||' VIEWBY
,'|| group_by_col_name ||' VIEWBYID
,'''||view_by_name || ''' RCI_DRILLDOWN_PARAM1
,'|| group_by_col_name ||' RCI_DRILLDOWN_PARAM2
,RCI_PROC_CERT_MEASURE1, RCI_PROC_CERT_MEASURE2
,decode(RCI_PROC_CERT_MEASURE1,0,0,round(RCI_PROC_CERT_MEASURE2/RCI_PROC_CERT_MEASURE1*100,2)) RCI_PROC_CERT_MEASURE3
,RCI_PROC_CERT_MEASURE4, RCI_PROC_CERT_MEASURE5, RCI_PROC_CERT_MEASURE6
,RCI_PROC_CERT_MEASURE7, RCI_PROC_CERT_MEASURE8, RCI_PROC_CERT_MEASURE9
,RCI_PROC_CERT_MEASURE10
,decode(RCI_PROC_CERT_MEASURE10,0,0,round(RCI_PROC_CERT_MEASURE10/RCI_PROC_CERT_MEASURE9*100,2)) RCI_PROC_CERT_MEASURE11
,RCI_PROC_CERT_MEASURE12
,RCI_PROC_CERT_MEASURE13, RCI_PROC_CERT_MEASURE14, RCI_PROC_CERT_MEASURE15
,RCI_PROC_CERT_MEASURE16
FROM (
select
'||group_by_col_name||'
,sum(r_open) RCI_PROC_CERT_MEASURE1
,sum(r_past_due) RCI_PROC_CERT_MEASURE2
,sum(r_AGE_IN_DAYS ) RCI_PROC_CERT_MEASURE4
,sum(r_AGE_BUCKET_1) RCI_PROC_CERT_MEASURE5
,sum(r_AGE_BUCKET_2) RCI_PROC_CERT_MEASURE6
,sum(r_AGE_BUCKET_3) RCI_PROC_CERT_MEASURE7
,sum(r_AGE_BUCKET_4) RCI_PROC_CERT_MEASURE8
,sum(f_open) RCI_PROC_CERT_MEASURE9
,sum(f_past_due) RCI_PROC_CERT_MEASURE10
,sum(f_AGE_IN_DAYS ) RCI_PROC_CERT_MEASURE12
,sum(f_AGE_BUCKET_1) RCI_PROC_CERT_MEASURE13
,sum(f_AGE_BUCKET_2) RCI_PROC_CERT_MEASURE14
,sum(f_AGE_BUCKET_3) RCI_PROC_CERT_MEASURE15
,sum(f_AGE_BUCKET_4) RCI_PROC_CERT_MEASURE16
from(
SELECT f.'|| group_by_col ||group_by_col_name||'
,sum(f.open) f_open
,sum(f.past_due) f_past_due
,round(avg(f.AGE_IN_DAYS )) f_AGE_IN_DAYS
,sum(f.AGE_BUCKET_1) f_AGE_BUCKET_1
,sum(f.AGE_BUCKET_2) f_AGE_BUCKET_2
,sum(f.AGE_BUCKET_3) f_AGE_BUCKET_3
,sum(f.AGE_BUCKET_4) f_AGE_BUCKET_4
,0 r_open
,0 r_past_due
,0 r_AGE_IN_DAYS
,0 r_AGE_BUCKET_1
,0 r_AGE_BUCKET_2
,0 r_AGE_BUCKET_3
,0 r_AGE_BUCKET_4
FROM
RCI_OPEN_FINDINGS_F f
WHERE f.open=1 AND f.age_in_days >= 0
'|| f_where_clause || '
group by f.'|| group_by_col || '
UNION
SELECT r.'|| group_by_col ||group_by_col_name||'
,0 f_open
,0 f_past_due
,0 f_AGE_IN_DAYS
,0 f_AGE_BUCKET_1
,0 f_AGE_BUCKET_2
,0 f_AGE_BUCKET_3
,0 f_AGE_BUCKET_4
,sum(r.open) r_open
,sum(r.past_due) r_past_due
,round(avg(r.AGE_IN_DAYS )) r_AGE_IN_DAYS
,sum(r.AGE_BUCKET_1) r_AGE_BUCKET_1
,sum(r.AGE_BUCKET_2) r_AGE_BUCKET_2
,sum(r.AGE_BUCKET_3) r_AGE_BUCKET_3
,sum(r.AGE_BUCKET_4) r_AGE_BUCKET_4
FROM
RCI_OPEN_REMEDIATIONS_F r
WHERE r.open=1 AND r.age_in_days >= 0
'|| r_where_clause || '
group by r.'|| group_by_col || '
) group by '||group_by_col_name||'
) opn '
|| join_table
|| ' where
' ||outer_where_clause
;
l_act_sqlstmt := 'select VIEWBY,VIEWBYID,RCI_DRILLDOWN_PARAM1,RCI_DRILLDOWN_PARAM2
,RCI_PROC_CERT_MEASURE1,RCI_PROC_CERT_MEASURE2,RCI_PROC_CERT_MEASURE3
,RCI_PROC_CERT_MEASURE4,RCI_PROC_CERT_MEASURE5,RCI_PROC_CERT_MEASURE6
,RCI_PROC_CERT_MEASURE7,RCI_PROC_CERT_MEASURE8,RCI_PROC_CERT_MEASURE9
,RCI_PROC_CERT_MEASURE10,RCI_PROC_CERT_MEASURE11
,RCI_PROC_CERT_MEASURE12,RCI_PROC_CERT_MEASURE13
,RCI_PROC_CERT_MEASURE14,RCI_PROC_CERT_MEASURE15
,RCI_PROC_CERT_MEASURE16
from (select t.*
,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
from ( '||l_sqlstmt||'
) t ) a
order by a.col_rank ';
SELECT
ecv.change_id RCI_DRILLDOWN_PARAM1
,ecv.change_name RCI_PROC_CERT_MEASURE7
,ecv.requestor RCI_PROC_CERT_MEASURE1
,ecst.status_name RCI_PROC_CERT_MEASURE2
,ecv.priority_code RCI_PROC_CERT_MEASURE3
,ecv.reported_days_since RCI_PROC_CERT_MEASURE4
,-ecv.days_until_due RCI_PROC_CERT_MEASURE5
,trunc(last_day(to_date('''||v_yyyymm||''',''YYYYMM''))-ecv.days_until_due) RCI_PROC_CERT_MEASURE6
FROM
eng_changes_v ecv, eng_change_statuses_tl ecst
WHERE
change_mgmt_type_code = ''AMW_PROJ_FINDING''
and ecst.status_code = ecv.status_code
and ecst.language = userenv(''LANG'')
and change_id in ( SELECT
finding_id
FROM
rci_open_findings_f f
WHERE age_in_days >= 0
/** 12.26.2005 npanandi: added following clause to display
only OPEN findings from rci_open_findings_f
bug 4908320 fix ***/
and f.OPEN=1
' || where_clause ||
')'
;
SELECT
ecv.change_id RCI_DRILLDOWN_PARAM1
,ecv.change_name RCI_PROC_CERT_MEASURE7
,ecv.requestor RCI_PROC_CERT_MEASURE1
,ecst.status_name RCI_PROC_CERT_MEASURE2
,ecv.priority_code RCI_PROC_CERT_MEASURE3
,ecv.reported_days_since RCI_PROC_CERT_MEASURE4
,-ecv.days_until_due RCI_PROC_CERT_MEASURE5
,trunc(last_day(to_date('''||v_yyyymm||''',''YYYYMM''))-ecv.days_until_due) RCI_PROC_CERT_MEASURE6
FROM
eng_changes_v ecv, eng_change_statuses_tl ecst
WHERE
ecv.change_mgmt_type_code = ''AMW_REMEDIATION''
and ecst.status_code = ecv.status_code
and ecst.language = userenv(''LANG'')
/** 12.19.2005 npanandi: adding the below, because only those remediations
should be chosen which are initiated BEFORE the chosen periods last day
**/
and ecv.INITIATION_DATE < last_day(to_date('''||v_yyyymm||''',''YYYYMM''))
/** 12.19.2005 npanandi: need to see if this is open, and if not open,
whether it was set to completed AFTER the last day of the
chosen period ***/
and (ecv.STATUS_CODE not in (0,11) or (ecv.status_code=11 and round(ecv.last_update_date-last_day(to_date('''||v_yyyymm||''',''YYYYMM''))) > 0 ))
and ecv.change_id in ( SELECT
remediation_id
FROM
rci_open_remediations_f r
WHERE 1=1 ' || where_clause ||
')'
;
insert_findings_increment_load(
errbuf => errbuf
,retcode => retcode);
insert_remeds_increment_load(
errbuf => errbuf
,retcode => retcode);
insert_findings_initial_load(
errbuf => errbuf
,retcode => retcode);
insert_remeds_initial_load(
errbuf => errbuf
,retcode => retcode);
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_OPEN_FINDINGS_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_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_OPEN_REMEDIATIONS_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 );
PROCEDURE insert_findings_increment_load(
errbuf IN OUT NOCOPY VARCHAR2
,retcode IN OUT NOCOPY NUMBER) IS
BEGIN
insert_findings_initial_load(
errbuf => errbuf
,retcode => retcode);
END insert_findings_increment_load;
added valid report_date_julian in the ETL insert
added entries in rci_dr_inc audit table for every load
added misc procedures -- check_initial_load_setup and err_mesg
**/
PROCEDURE insert_findings_initial_load(
errbuf IN OUT NOCOPY VARCHAR2
,retcode IN OUT NOCOPY NUMBER) IS
CURSOR c_find_age IS
SELECT
DISTINCT finding_id, age_in_days
FROM
rci_open_findings_f rof;
SELECT
DISTINCT finding_id
FROM
rci_open_findings_f rof, eng_change_subjects ecs
WHERE
rof.finding_id = ecs.change_id AND
(ecs.entity_name = 'PROJ_ORG' OR ecs.entity_name = 'PROJ_ORG_PROC');
SELECT
entity_name, pk1_value
FROM
eng_change_subjects ecs
WHERE change_id = p_change_id;
DELETE FROM rci_dr_inc where fact_name = 'RCI_OPEN_FINDINGS_F';
INSERT INTO rci_open_findings_f (
finding_id, priority_code, reason_code, phase_code,
age_in_days,
need_by_date, completion_date,
open, past_due,
period_year, period_num, quarter_num,
ent_period_id, ent_qtr_id, ent_year_id,
report_date_julian,
created_by,last_update_login,creation_date,last_updated_by,last_update_date)
SELECT
eec1.change_id , eec1.priority_code, eec1.reason_code, eec1.status_code,
ROUND(v_end_date-eec1.initiation_date),
eec1.need_by_date, eec1.implementation_date,
case when(eec1.STATUS_CODE not in (0,11) or (eec1.status_code=11 and round(eec1.last_update_date-v_end_date) > 0 )) then 1 else 0 end,
-- case when( implementation_date is null or round(implementation_date-v_end_date)>0) then 1 else 0 end,
case when( round(v_end_date-need_by_date)>0 and (implementation_date is null or (implementation_date-v_end_date) >0 )) then 1 else 0 end,
y, m, q,
v_period, v_qtr, v_year,
to_number(to_char(v_end_date,'J')),/** 12.16.2005 npanandi: added report_date_julian **/
G_USER_ID, G_USER_ID, SYSDATE, G_USER_ID, SYSDATE
FROM
eng_engineering_changes eec1
WHERE
eec1.change_mgmt_type_code = 'AMW_PROJ_FINDING';
UPDATE rci_open_findings_f
SET age_bucket_1 = v_bucket_1 , age_bucket_2 = v_bucket_2,
age_bucket_3 = v_bucket_3, age_bucket_4 = v_bucket_4
WHERE finding_id = v_finding_id;
UPDATE rci_open_findings_f
SET organization_id = r_ch_sub.pk1_value
WHERE finding_id = v_finding_id;
UPDATE rci_open_findings_f
SET process_id = r_ch_sub.pk1_value
WHERE finding_id = v_finding_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_OPEN_FINDINGS_F' ;
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_OPEN_FINDINGS_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 );
END insert_findings_initial_load;
PROCEDURE insert_remeds_increment_load(
errbuf IN OUT NOCOPY VARCHAR2
,retcode IN OUT NOCOPY NUMBER) IS
BEGIN
insert_remeds_initial_load(
errbuf => errbuf
,retcode => retcode);
END insert_remeds_increment_load;
added valid report_date_julian in the ETL insert
added entries in rci_dr_inc audit table for every load
added misc procedures -- check_initial_load_setup and err_mesg
**/
PROCEDURE insert_remeds_initial_load(
errbuf IN OUT NOCOPY VARCHAR2
,retcode IN OUT NOCOPY NUMBER) IS
CURSOR c_remed_age IS
SELECT
DISTINCT finding_id, age_in_days
FROM
rci_open_remediations_f;
SELECT
DISTINCT finding_id, age_in_days
FROM
rci_open_remediations_f rof, eng_change_subjects ecs
WHERE
rof.finding_id = ecs.change_id AND
(ecs.entity_name = 'PROJ_ORG' OR ecs.entity_name = 'PROJ_ORG_PROC');
SELECT
entity_name, pk1_value
FROM
eng_change_subjects ecs
WHERE change_id = p_change_id;
DELETE FROM rci_dr_inc where fact_name = 'RCI_OPEN_REMEDIATIONS_F';
INSERT INTO rci_open_remediations_f(
finding_id, remediation_id, priority_code, reason_code, phase_code,
age_in_days,
need_by_date, completion_date,
open, past_due,
period_year, period_num, quarter_num,
ent_period_id, ent_qtr_id, ent_year_id,
report_date_julian,
created_by,last_update_login,creation_date,last_updated_by,last_update_date)
SELECT
ecor.change_id , ecor.object_to_id1, eec1.priority_code, eec1.reason_code, eec1.status_code,
ROUND(v_end_date-eec1.initiation_date),
eec1.need_by_date, eec1.implementation_date,
case when(eec1.STATUS_CODE not in (0,11) or (eec1.status_code=11 and round(eec1.last_update_date-v_end_date) > 0 )) then 1 else 0 end,
-- case when( implementation_date is null or round(implementation_date-v_end_date)>0) then 1 else 0 end,
case when( round(v_end_date-need_by_date)>0 and (implementation_date is null or (implementation_date-v_end_date) >0 )) then 1 else 0 end,
y, m, q,
v_period, v_qtr, v_year,
to_number(to_char(v_end_date,'J')),/** 12.16.2005 npanandi: added report_date_julian **/
G_USER_ID, G_USER_ID, SYSDATE, G_USER_ID, SYSDATE
FROM
eng_engineering_changes eec1, eng_change_obj_relationships ecor
WHERE
eec1.change_mgmt_type_code = 'AMW_REMEDIATION'
AND ecor.relationship_code = 'RESOLVED_BY'
AND eec1.change_id = ecor.object_to_id1;
UPDATE rci_open_remediations_f
SET age_bucket_1 = v_bucket_1 , age_bucket_2 = v_bucket_2,
age_bucket_3 = v_bucket_3, age_bucket_4 = v_bucket_4
WHERE finding_id = v_finding_id;
update rci_open_remediations_f set organization_id = r_ch_sub.pk1_value
WHERE finding_id = v_finding_id;
update rci_open_remediations_f set process_id = r_ch_sub.pk1_value
WHERE finding_id = v_finding_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_OPEN_REMEDIATIONS_F' ;
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_OPEN_REMEDIATIONS_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 );
END insert_remeds_initial_load;