Clarity

Hi We have a requirement where in we need to pull the "actuals" by "investment type" (its not OOTB, a custom OBS) in hours yearly.

Which way we can get this from PPA_WIP_TABLES or from Slices or labor_actsum from inv_investments?

Start with trying something  like

SELECT
prj_obs_units.name OBS_unit,
SUM(ppa_wip.quantity) HOURS

FROM
ppa_wip,
prj_obs_units,
prj_obs_types,
prj_obs_associations

WHERE
prj_obs_units.type_id = prj_obs_types.id
AND prj_obs_types.name = 'investment type'
AND prj_obs_associations.table_name = 'SRM_PROJECTS'
AND prj_obs_associations.record_id = ppa_wip.investment_id
AND ppa_wip.transdate <= '2018-12-31'
and ppa_wip.transdate >= '2018-01-01'
GROUP BY prj_obs_units.name

WIP table could be the best as it contains the project ID's and transdates on the outset that quantity is hours or similar.

The custom OBS units are not likely to be associated with the timeentries or WIP entries. Therefore you have to get the association separately.

labor_actsum from inv_investments is no good as it is the total and you cannot get aactuals for a specific time frame from that.

Slices are also good if they contain the timeframe you want. You may need to crate custom slices for that.

In the system I looked there are Monthly investment actuals which does require much SQL like using DAILYRESOURCEACTUALS would.

Thanks

You might consider adding a condition for the ppa_wip.status field