Hi,
I tried to create a portlet for Budget plan along with the investment details. When I tried with it, I get duplication of transaction class( almost list all transaction class, whereas the investment uses only 2/3 classes).
Below is the query that I used:
SELECT
rownum rn,
i.id id,
i.name name,
i.code code,
i.manager_id mgrId,
mgr.full_name mgrName,
ocp.az_set_is_id setisid,
ocp.az_setis_contact setiscontact,
res.full_name resname,
oci.obj_work_Status workstatus,
stat.name status,
dobs.unit_id did,
dobs.path dpath,
dept.unit_id dpid,
dept.path dppath,
fp.period_type_code,
prt.name prtname,
fp.name fpname,
fp.code cpcode,
cl.name cstname,
prcc.prname prname,
TRAN.DESCRIPTION tclass,
tran.id tdesc,
stp.start_date sdate,
fsp.end_date-1 enddate,
cst.start_date startdate,
cst.finish_date-1 finishdate,
cst.slice slice,
ROUND(((cst.finish_date-cst.start_date)*cst.slice),2) costvalue
FROM INV_INVESTMENTS I
inner JOIN FIN_PLANS FP
ON I.ID =FP.OBJECT_ID
AND FP.OBJECT_CODE ='project'
AND FP.PLAN_TYPE_CODE ='FORECAST'
AND FP.IS_PLAN_OF_RECORD=1
LEFT OUTER JOIN FIN_COST_PLAN_DETAILS FP_DET
ON FP.ID=FP_DET.PLAN_ID
LEFT OUTER JOIN TRANSCLASS TRAN
ON FP_DET.TRANSACTION_CLASS_ID = TRAN.ID
LEFT OUTER JOIN odf_ca_project ocp
ON ocp.id=i.id
LEFT OUTER JOIN odf_ca_inv oci
ON oci.id=i.id
LEFT OUTER JOIN srm_Resources res
ON res.user_id=ocp.az_setis_contact
LEFT OUTER JOIN srm_resources mgr
on i.manager_id=mgr.user_id
LEFT OUTER JOIN cmn_lookups_v stat
ON stat.lookup_code =oci.obj_work_status
AND language_code ='en'
AND stat.lookup_type='OBJ_INVESTMENT_WORK_STATUS'
LEFT OUTER JOIN srm_Resources res
ON res.user_id=ocp.az_setis_contact
LEFT OUTER JOIN fin_cost_plan_details fcp
ON fp.id=fcp.plan_id
LEFT OUTER JOIN odf_ssl_cst_dtl_cost cst
ON cst.prj_object_id=fcp.id
LEFT OUTER JOIN BIZ_COM_PERIODS stp
ON stp.id=fp.start_period_id
LEFT OUTER JOIN BIZ_COM_PERIODS fsp
ON fsp.id=fp.end_period_id
LEFT OUTER JOIN cmn_lookups_v prt
ON prt.lookup_type ='PERIOD_TYPE'
AND prt.language_code='en'
AND prt.lookup_code =fp.period_type_code
LEFT OUTER JOIN cmn_lookups_v cl
ON cl.lookup_type ='LOOKUP_FIN_COSTTYPECODE'
AND cl.language_code='en'
AND cl.id =fcp.cost_type_id
LEFT OUTER JOIN prchargecode prcc
ON prcc.prid=i.chargecodeid
left outer join (
select poa.record_id,poa.unit_id,ndo.obs_unit_id,ndo.path,ndo.obs_type_id,ndo.obs_type_name
from prj_obs_associations poa
inner join nbi_dim_obs ndo on poa.unit_id=ndo.obs_unit_id
inner join inv_investments i on i.id=poa.record_id and i.is_active=1
where ndo.obs_type_id=5004001
)dobs on dobs.record_id=i.id
left outer join (
select poa.record_id,poa.unit_id,ndo.obs_unit_id,ndo.path,ndo.obs_type_id,ndo.obs_type_name
from prj_obs_associations poa
inner join nbi_dim_obs ndo on poa.unit_id=ndo.obs_unit_id
inner join inv_investments i on i.id=poa.record_id and i.is_active=1
where ndo.obs_type_id=5000002
)dept on dept.record_id=i.id
WHERE i.is_active=1
Could you please help me out to sort it down?
Thanks in advance.
Monica