select ra.resource_id, sum(j.counts) counts
from ss_resc_acti ra,
(select prce_id, acti_id, count(*) counts
from (select d.prce_id, d.acti_id, d.code
from table(cast(io_acin_record1 as ty_task_record1)) d, wf_activities a
where d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type is null
union
select distinct d.prce_id, d.acti_id acti_id, to_char(dest_id) code
from tb_declare_stock_chr e, wf_activities a, (select prce_id, acti_id, code from table(cast(io_acin_record1 as ty_task_record1))) d
where to_char(e.id) = d.code
and e.status = 1
and d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type = 1 -- 申报书
union
select distinct d.prce_id, d.acti_id acti_id, to_char(stpr_id) code
from tb_stock_project_bundle_chr e, wf_activities a, (select prce_id, acti_id, code from table(cast(io_acin_record1 as ty_task_record1))) d
where to_char(e.desc_id) = d.code
and e.status = 1
and e.acti_id is null
and d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type = 2 -- 项目
union
select distinct d.prce_id, d.acti_id acti_id, to_char(gather_id) code
from tb_budget_audit e, wf_activities a, (select prce_id, acti_id, code from table(cast(io_acin_record1 as ty_task_record1))) d
where to_char(e.id) = d.code
and e.status = 1
and d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type = 6 -- 采购计划汇总
union
select distinct d.prce_id, d.acti_id acti_id, to_char(stpr_id || bundle_code) code
from tb_stock_project_bundle_chr e, wf_activities a, (select prce_id, acti_id, code from table(cast(io_acin_record1 as ty_task_record1))) d
where to_char(e.desc_id) = d.code
and e.status = 1
and e.acti_id is null
and d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type = 7 -- 采购项目包组
union
select distinct d.prce_id, d.acti_id acti_id, to_char(stpb.before_bull_id) code
from tb_stock_project_bundle_chr e,
tb_stock_project_bundle stpb,
wf_activities a,
(select prce_id, acti_id, code from table(cast(io_acin_record1 as ty_task_record1))) d
where to_char(e.desc_id) = d.code
and e.status = 1
and e.acti_id is null
and e.stpr_id = stpb.stpr_id
and e.bundle_code = stpb.bundle_code
and d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type = 8 -- 询价预成交公告
union
select distinct d.prce_id, d.acti_id acti_id, to_char(stpb.bull_id) code
from tb_stock_project_bundle_chr e,
tb_stock_project_bundle stpb,
wf_activities a,
(select prce_id, acti_id, code from table(cast(io_acin_record1 as ty_task_record1))) d
where to_char(e.desc_id) = d.code
and e.status = 1
and e.acti_id is null
and e.stpr_id = stpb.stpr_id
and e.bundle_code = stpb.bundle_code
and d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type = 9 -- 询价成交公告
) h
group by h.prce_id, h.acti_id) j
where ra.prce_id = j.prce_id
and ra.acti_id = j.acti_id
group by ra.resource_id
order by ra.resource_id
from ss_resc_acti ra,
(select prce_id, acti_id, count(*) counts
from (select d.prce_id, d.acti_id, d.code
from table(cast(io_acin_record1 as ty_task_record1)) d, wf_activities a
where d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type is null
union
select distinct d.prce_id, d.acti_id acti_id, to_char(dest_id) code
from tb_declare_stock_chr e, wf_activities a, (select prce_id, acti_id, code from table(cast(io_acin_record1 as ty_task_record1))) d
where to_char(e.id) = d.code
and e.status = 1
and d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type = 1 -- 申报书
union
select distinct d.prce_id, d.acti_id acti_id, to_char(stpr_id) code
from tb_stock_project_bundle_chr e, wf_activities a, (select prce_id, acti_id, code from table(cast(io_acin_record1 as ty_task_record1))) d
where to_char(e.desc_id) = d.code
and e.status = 1
and e.acti_id is null
and d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type = 2 -- 项目
union
select distinct d.prce_id, d.acti_id acti_id, to_char(gather_id) code
from tb_budget_audit e, wf_activities a, (select prce_id, acti_id, code from table(cast(io_acin_record1 as ty_task_record1))) d
where to_char(e.id) = d.code
and e.status = 1
and d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type = 6 -- 采购计划汇总
union
select distinct d.prce_id, d.acti_id acti_id, to_char(stpr_id || bundle_code) code
from tb_stock_project_bundle_chr e, wf_activities a, (select prce_id, acti_id, code from table(cast(io_acin_record1 as ty_task_record1))) d
where to_char(e.desc_id) = d.code
and e.status = 1
and e.acti_id is null
and d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type = 7 -- 采购项目包组
union
select distinct d.prce_id, d.acti_id acti_id, to_char(stpb.before_bull_id) code
from tb_stock_project_bundle_chr e,
tb_stock_project_bundle stpb,
wf_activities a,
(select prce_id, acti_id, code from table(cast(io_acin_record1 as ty_task_record1))) d
where to_char(e.desc_id) = d.code
and e.status = 1
and e.acti_id is null
and e.stpr_id = stpb.stpr_id
and e.bundle_code = stpb.bundle_code
and d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type = 8 -- 询价预成交公告
union
select distinct d.prce_id, d.acti_id acti_id, to_char(stpb.bull_id) code
from tb_stock_project_bundle_chr e,
tb_stock_project_bundle stpb,
wf_activities a,
(select prce_id, acti_id, code from table(cast(io_acin_record1 as ty_task_record1))) d
where to_char(e.desc_id) = d.code
and e.status = 1
and e.acti_id is null
and e.stpr_id = stpb.stpr_id
and e.bundle_code = stpb.bundle_code
and d.prce_id = a.prce_id
and d.acti_id = a.id
and a.activity_type = 9 -- 询价成交公告
) h
group by h.prce_id, h.acti_id) j
where ra.prce_id = j.prce_id
and ra.acti_id = j.acti_id
group by ra.resource_id
order by ra.resource_id
