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
WHEREprj_obs_units.type_id = prj_obs_types.idAND prj_obs_types.name = 'investment type'AND prj_obs_associations.table_name = 'SRM_PROJECTS'AND prj_obs_associations.record_id = ppa_wip.investment_idAND 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.
You might consider adding a condition for the ppa_wip.status field
Indicates the current status of this transaction. Values are:
Retrieving data ...