Nimmakayala Trusted Contributor.
Trusted Contributor.
222 views

how to get retrieve details of forecast value and allocate value in a single query.

Hi team,

We have a requirement that, to develop the portlet related to resource request detailsl

Like as position name, project id, project name , Requested and allocated rftes. am able to query it but getting duplicate values. will you provide the query if you have already implemented in ur org.

select rspa.position_id position_id,
rp.position_name postion_name,
rspa.resource_id resource_id,
0 rfte,
round(SUM(Allocation_value)/(Ppm_Calc_Working_Days(rspa.resource_id,To_Char(to_date(rspa.Allocation_Date),'MMYYYY'))*8),2) Alloc_FTE
from rsc_staff_prof_allocation rspa,rsc_positions rp,kcrt_fg_pfm_project kfpp
where kfpp.prj_staff_prof_id=rp.staffing_profile_id
and rp.position_id=rspa.position_id
and rspa.position_id=137579
group by To_Char(to_date(rspa.Allocation_Date),'MMYYYY'),
rspa.position_id,rspa.allocation_value,rp.position_name,rspa.resource_id
union all
select rp.position_id position_id,
rp.position_name postion_name,
0 resource_id,
ROUND(SUM (FORECAST_VALUE) /Resource_get_working_days(rtrim(TO_CHAR(forecast_date,'Month'))||' '||TO_CHAR(forecast_date,'YYYY'))/8, 2) rfte,
0 alloc_fte
from
rsc_positions rp,
kcrt_fg_pfm_project kfpp,
knta_lookups kl,
rsc_position_forecast rpf
where kfpp.prj_staff_prof_id=rp.staffing_profile_id
and rp.position_id=rpf.position_id
and rp.status_code=kl.lookup_code
and rp.status_Code <>0
and kl.lookup_type='RSC - Staffing Profile Position Status'
--and kfpp.request_id =
and rp.position_id=137579
group by rp.position_id,rp.position_name,rpf.position_id,TO_CHAR(forecast_date,'Month'),TO_CHAR(forecast_date,'YYYY');

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.