select distinct
i1.id PRJID,
i1.code PRJCODE,
i1.name PRJNAME,
r1.full_name RSCNAME,
r1.unique_name RSCCODE,
r1.full_name || ' (' || r1.unique_name || ')' RESOURCENAMECODE,
t1.transclass TRANSCLASS,
t1.description TRANCLASSDESC,
wg003.getattributevalue ('WMC_PROGRAM', o1.wmc_program) PROGRAM,
wg003.getattributevalue ('WMC_IS_CAT', o1.wmc_category) ISCATEGORY,
decode (wg003.istransclassfixedprice (t1.transclass), 1, 'FIXED PRICE', 'NON LABOR') transtype, 
wg003.currencyexchange (i1.currency_code, 'USD', sysdate, wg003.wmc_plantypebytransclass ('BUDGET', oc1.start_date, i1.id, t1.transclass, r1.unique_name)) budget,
wg003.currencyexchange (i1.currency_code, 'USD', sysdate, wg003.wmc_plantypebytransclass ('FORECAST', oc1.start_date, i1.id, t1.transclass, r1.unique_name)) forecast,
(
select 
nvl (sum (v.totalcost), 0)
from
wg003.ppa_wip w,
wg003.ppa_wip_values v
where
w.status = 0
and w.project_code = i1.code
and w.role_code = r1.unique_name
and w.transclass = t1.transclass
and v.currency_type = 'REPORTING'
and trunc (w.transdate, 'year') = trunc (sysdate, 'year')
and w.transdate <= wg003.actualcutoffdate
and w.transno = v.transno
) actuals,
'a' finaltotals
--wg003.wmc_plantypebytransclass ('ACTUALS', oc1.start_date, i1.id, t1.transclass, r1.unique_name) actuals
from
wg003.fin_plans fp1,
wg003.inv_investments i1,
wg003.odf_ca_project o1,
wg003.fin_cost_plan_details fd1,
wg003.odf_ssl_cst_dtl_cost oc1,
wg003.srm_resources r1,
wg003.transclass t1
where
fp1.object_id = i1.id
and i1.id = o1.id
and fp1.id = fd1.plan_id
and fp1.is_plan_of_record = 1
and fp1.plan_type_code = 'FORECAST'
and fd1.plan_detail_1_key is not null
and fd1.plan_detail_2_key is not null
and substr (fd1.plan_detail_1_key, instr (fd1.plan_detail_1_key, ':', -1, 1) + 1) = r1.id
and substr (fd1.plan_detail_2_key, instr (fd1.plan_detail_2_key, ':', -1, 1) + 1) = t1.id
and fd1.id = oc1.prj_object_id
and trunc (oc1.start_date, 'year') = trunc(sysdate, 'year')
and t1.transclass not in ('10000', '10001', '13134')
and wg003.reportproject (i1.id) = 1
and wg003.getobsleveldesc (i1.id, 3)  <> 'Construction' 
and wg003.getobsleveldesc (i1.id, 3)  <> 'Finance Projects1'

union

select distinct
i2.id PRJID,
i2.code PRJCODE,
i2.name PRJNAME,
wg003.getobsleveldesc (i2.id, 3) obs3,
wg003.getobsleveldesc (i2.id, 4) obs4,
wg003.getobsleveldesc (i2.id, 5) obs5,
r2.full_name RSCNAME,
r2.unique_name RSCCODE,
r2.full_name || ' (' || r2.unique_name || ')' RESOURCENAMECODE,
t2.transclass TRANSCLASS,
t2.description TRANCLASSDESC,
wg003.getattributevalue ('WMC_PROGRAM', o2.wmc_program) PROGRAM,
wg003.getattributevalue ('WMC_IS_CAT', o2.wmc_category) ISCATEGORY,
decode (wg003.istransclassfixedprice (t2.transclass), 1, 'FIXED PRICE', 'NON LABOR') transtype,
wg003.currencyexchange (i2.currency_code, 'USD', sysdate, wg003.wmc_plantypebytransclass ('BUDGET', oc2.start_date, i2.id, t2.transclass, r2.unique_name)) budget,
wg003.currencyexchange (i2.currency_code, 'USD', sysdate, wg003.wmc_plantypebytransclass ('FORECAST', oc2.start_date, i2.id, t2.transclass, r2.unique_name)) forecast,
--wg003.wmc_plantypebytransclass ('ACTUALS', oc2.start_date, i2.id, t2.transclass, r2.unique_name) actuals
(
select 
nvl (sum (v.totalcost), 0)
from
wg003.ppa_wip w,
wg003.ppa_wip_values v
where
w.status = 0
and w.project_code = i2.code
and w.role_code = r2.unique_name
and w.transclass = t2.transclass
and v.currency_type = 'REPORTING'
and trunc (w.transdate, 'year') = trunc (sysdate, 'year')
and w.transdate <= wg003.actualcutoffdate
and w.transno = v.transno
) actuals,
'a' finaltotals
from
wg003.fin_plans fp2,
wg003.inv_investments i2,
wg003.odf_ca_project o2,
wg003.fin_cost_plan_details fd2,
wg003.odf_ssl_cst_dtl_cost oc2,
wg003.srm_resources r2,
wg003.transclass t2
where
fp2.object_id = i2.id
and i2.id = o2.id 
and fp2.id = fd2.plan_id
and fp2.is_plan_of_record = 1
and fp2.plan_type_code = 'FORECAST'
and fd2.plan_detail_1_key is not null
and fd2.plan_detail_2_key is not null
and substr (fd2.plan_detail_1_key, instr (fd2.plan_detail_1_key, ':', -1, 1) + 1) = r2.id
and substr (fd2.plan_detail_2_key, instr (fd2.plan_detail_2_key, ':', -1, 1) + 1) = t2.id
and fd2.id = oc2.prj_object_id
and trunc (oc2.start_date, 'year') = trunc(sysdate, 'year')
and t2.transclass not in ('10000', '10001', '13134')
and wg003.reportproject (i2.id) = 1
and wg003.getobsleveldesc (i2.id, 3)  <> 'Construction' 
and wg003.getobsleveldesc (i2.id, 3)  <> 'Finance Projects1'
and (to_char (oc2.start_date, 'yyyy'), i2.code, r2.unique_name, t2.transclass) not in 
(
select distinct
to_char (oc.start_date, 'yyyy'), i.code, r.unique_name, t.transclass
from
wg003.fin_plans fp,
wg003.inv_investments i,
wg003.odf_ca_project o,
wg003.fin_cost_plan_details fd,
wg003.odf_ssl_cst_dtl_cost oc,
wg003.srm_resources r,
wg003.transclass t
where
fp.object_id = i.id
and i.id = o.id 
and fp.id = fd.plan_id
and fp.is_plan_of_record = 1
and fp.plan_type_code = 'BUDGET'
and fd.plan_detail_1_key is not null
and fd.plan_detail_2_key is not null
and substr (fd.plan_detail_1_key, instr (fd.plan_detail_1_key, ':', -1, 1) + 1) = r.id
and substr (fd.plan_detail_2_key, instr (fd.plan_detail_2_key, ':', -1, 1) + 1) = t.id
and fd.id = oc.prj_object_id
and trunc (oc.start_date, 'year') = trunc(sysdate, 'year')
and t.transclass not in ('10000', '10001', '13134')
)

union

select  
i3.id PRJID,
i3.code PRJCODE,
i3.name PRJNAME,
wg003.getobsleveldesc (i3.id, 3) obs3,
wg003.getobsleveldesc (i3.id, 4) obs4,
wg003.getobsleveldesc (i3.id, 5) obs5,
r3.full_name RSCNAME,
w3.role_code RSCCODE,
r3.full_name || ' (' || r3.unique_name || ')' RESOURCENAMECODE,
w3.transclass TRANSCLASS,
t3.description TRANCLASSDESC,
wg003.getattributevalue ('WMC_PROGRAM', o3.wmc_program) PROGRAM,
wg003.getattributevalue ('WMC_IS_CAT', o3.wmc_category) ISCATEGORY,
decode (wg003.istransclassfixedprice (t3.transclass), 1, 'FIXED PRICE', 'NON LABOR') transtype,
0 BUDGET,
0 FORECAST,
nvl (sum (v3.totalcost), 0) ACTUALS,
'a' finaltotals
from 
wg003.inv_investments i3,
wg003.odf_ca_project o3,
wg003.ppa_wip w3,
wg003.ppa_wip_values v3,
wg003.srm_resources r3,
wg003.transclass t3
where
i3.id = o3.id
and i3.code = w3.project_code
and w3.transno = v3.transno
and w3.role_code = r3.unique_name 
and w3.transclass = t3.transclass 
and w3.status = 0
and v3.currency_type = 'REPORTING'
and trunc (w3.transdate, 'year') = trunc (sysdate, 'year')
and trunc (w3.transdate) <= wg003.actualcutoffdate
and t3.transclass not in ('10000', '10001', '13134')
and wg003.reportproject (i3.id) = 1
and wg003.getobsleveldesc (i3.id, 3)  <> 'Construction' 
and wg003.getobsleveldesc (i3.id, 3)  <> 'Finance Projects1'
and (i3.code, r3.unique_name, t3.transclass) not in 
(
select distinct
i.code, r.unique_name, t.transclass
from
wg003.fin_plans fp,
wg003.inv_investments i,
wg003.odf_ca_project o,
wg003.fin_cost_plan_details fd,
wg003.odf_ssl_cst_dtl_cost oc,
wg003.srm_resources r,
wg003.transclass t
where
fp.object_id = i3.id
and i.id = o.id 
and fp.id = fd.plan_id
and fp.is_plan_of_record = 1
and fp.plan_type_code in ('FORECAST',  'BUDGET')
and fd.plan_detail_1_key is not null
and fd.plan_detail_2_key is not null
and substr (fd.plan_detail_1_key, instr (fd.plan_detail_1_key, ':', -1, 1) + 1) = r.id
and substr (fd.plan_detail_2_key, instr (fd.plan_detail_2_key, ':', -1, 1) + 1) = t.id
and fd.id = oc.prj_object_id
and trunc (oc.start_date, 'year') = trunc(sysdate, 'year')
and t.transclass not in ('10000', '10001', '13134')
)
group by
i3.id,
i3.code,
i3.name,
wg003.getobsleveldesc (i3.id, 3),
wg003.getobsleveldesc (i3.id, 4),
wg003.getobsleveldesc (i3.id, 5),
r3.full_name,
w3.role_code,
r3.full_name || ' (' || r3.unique_name || ')',
w3.transclass,
t3.description,
wg003.getattributevalue ('WMC_PROGRAM', o3.wmc_program),
wg003.getattributevalue ('WMC_IS_CAT', o3.wmc_category),
decode (wg003.istransclassfixedprice (t3.transclass), 1, 'FIXED PRICE', 'NON LABOR'),
to_char (w3.transdate, 'yyyy')