AnsweredAssumed Answered

CA PPM - Timesheet Porlet issue

Question asked by Patricio_1979 on Nov 12, 2018
Latest reply on Nov 14, 2018 by Patricio_1979

Hello everybody,

 

I contact to the community because we have a Timesheet porlet ("Horas de Facturación") and we have a problem related with the OSB of a resource. A few months ago we had to modify the OBS of the resource "Gaston Gonzalez" but this change it is not displayed in the porlet.

 

Following I share more information:

 

The Query that use the porlet (Horas de Facturación) is:

SELECT
@SELECT:DIM:USER_DEF:IMPLIED:TS:UniqueID:UniqueID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:Inversion:Inversion@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:tipoInversion:tipoInversion@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:Parent:Parent@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:oie:oie@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:oiep:oiep@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:IdInversion:IdInversion@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:Tarea:Tarea@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:Fecha:Fecha@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:NombreDeRecurso:NombreDeRecurso@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:IdRecurso:IdRecurso@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:CodigoCarga:CodigoCarga@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:Status:Status@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:Hours:Hours@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:NombreOBS:NombreOBS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:filterOBS:filterOBS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:Naturaleza:Naturaleza@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:IdNaturaleza:IdNaturaleza@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:Cliente:Cliente@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:IdCliente:IdCliente@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TS:IdUnidad:IdUnidad@
from
(
select
ROW_NUMBER() OVER (ORDER BY pbs.slice_date) AS UniqueID,
inv.NAME as Inversion,
inv.odf_object_code as tipoInversion,
invp.NAME as Parent,
oinv.smg_oieinv oie,
oinvp.smg_oieinv as oiep,
inv.id as IdInversion,
ta.PRNAME as Tarea,
un.id as IdUnidad,
pbs.SLICE_DATE as Fecha,
res.full_name as NombreDeRecurso,
res.id as IdRecurso,
cc.prname CodigoCarga,
case when ts.prstatus = 4 then 'Posted' end Status,
--sum(pbs.slice) as Hours,
pbs.slice as Hours,
obs.path as NombreOBS,
obs.OBS_UNIT_ID as filterOBS,
LOV1.NAME as Cliente,
case prj.smg_naturaleza
when 'smg_nat_incidente' then 'Incidente'
when 'smg_nat_mantenimiento' then 'Mantenimiento'
when 'smg_nat_proyecto' then 'Proyecto'
else 'No aplica'
END as Naturaleza,
LOV1.CODE as IdCliente,
prj.smg_naturaleza as IdNaturaleza

from srm_resources res
LEFT OUTER JOIN prj_obs_associations aso ON aso.record_id = res.id
LEFT OUTER JOIN PRJ_OBS_UNITS un on un.id = aso.UNIT_ID
LEFT OUTER JOIN PRJ_OBS_UNITS_FLAT unf on unf.UNIT_ID = un.id
LEFT OUTER JOIN PRJ_OBS_TYPES ut on ut.ID = un.TYPE_ID
LEFT OUTER JOIN NBI_DIM_OBS obs on obs.OBS_UNIT_ID = un.ID
LEFT OUTER JOIN prtimesheet ts ON ts.prresourceid = res.id
LEFT OUTER JOIN prtimeentry te ON te.prtimesheetid = ts.prid
LEFT OUTER JOIN FIN_BROWSE_USER_LOV1_V LOV1 on LOV1.CODE = te.USER_LOV1
LEFT OUTER JOIN prchargecode cc ON te.prchargecodeid = cc.prid
LEFT OUTER JOIN prassignment asg ON asg.prid = te.prassignmentid
LEFT OUTER JOIN prtask ta ON ta.prid = asg.prtaskid
LEFT OUTER JOIN prtimeperiod tp ON ts.prtimeperiodid = tp.prid
RIGHT OUTER JOIN inv_investments inv ON inv.id = ta.prprojectid
LEFT OUTER JOIN ODF_CA_INV oinv on oinv.id = inv.ID
LEFT OUTER JOIN prj_blb_slices pbs on te.PRID = pbs.PRJ_OBJECT_ID
LEFT OUTER JOIN prj_blb_slicerequests pbsr on pbsr.id = pbs.slice_request_id
LEFT OUTER JOIN ODF_CA_PROJECT prj on prj.ID = inv.ID
LEFT OUTER JOIN ODF_INVESTMENTHIERARCHY_V2 hier on hier.child_id = inv.ID and hier.parent_inv_type = 'application'
LEFT OUTER JOIN INV_INVESTMENTS invp on invp.id = hier.parent_id
LEFT OUTER JOIN ODF_CA_INV oinvp on oinvp.ID = invp.ID
LEFT OUTER JOIN ODF_CA_PROJECT prj on prj.ID = inv.ID


where

pbsr.REQUEST_NAME = 'DAILYRESOURCETIMECURVE'
and aso.table_name in ('SRM_RESOURCES')
and ut.unique_name = 'smg_organaization'
and ts.PRSTATUS = 4
and pbs.SLICE_DATE > '2018-07-01'
and (lov1.LANGUAGE_CODE = 'es' or te.USER_LOV1 is null)
AND (
(@NVL@(@WHERE:PARAM:USER_DEF:INTEGER:myOBS@,0) = 0) OR
unf.branch_unit_id= @WHERE:PARAM:USER_DEF:INTEGER:myOBS@)

and (
(@NVL@(@WHERE:PARAM:USER_DEF:INTEGER:myResource@,0) = 0) OR

res.id in (@WHERE:PARAM:USER_DEF:INTEGER:myResource@))

and (
(@NVL@(@WHERE:PARAM:USER_DEF:STRING:myNaturaleza@,0) = 0) OR

prj.smg_naturaleza = @WHERE:PARAM:USER_DEF:STRING:myNaturaleza@)

and (
(@NVL@(@WHERE:PARAM:USER_DEF:STRING:myClient@,0) = 0 ) OR

LOV1.CODE = @WHERE:PARAM:USER_DEF:STRING:myClient@)


group by
inv.id,
invp.NAME,
oinv.smg_oieinv,
oinvp.smg_oieinv,
inv.NAME,
inv.id,
un.id,
ta.PRNAME,
pbs.SLICE_DATE,
res.full_name,
res.id,
cc.prname,
obs.path,
LOV1.NAME,
ts.prstatus,
pbs.SLICE,
te.practsum,
inv.odf_object_code,
obs.OBS_UNIT_ID,
prj.smg_naturaleza,
LOV1.CODE

)results

where
1=1
AND @FILTER@

 

The OBS that we must change is "Eq. Florencia Sbarra" to "Eq. Gaston Gonzalez".

 

 

We change this parameter in the OBS of the resource

 

The problem is that this change does not refresh in the report:

 

 

If anyone can help me I'll be very grateful.

Best regards.

Patricio.

Outcomes