Does somebody knows in witch table are the information of a staffing profile written? I need to make a query that shows the total of hours from each role into the staffing profile? In the table rsc_staffing_profiles I only can find the basic information from the the staffing profile.
The hours are stored in ITG_COMPRESSED_ELEMENTS, and you have to 'decompress' them into days first if you want to query by date range. Otherwise, you could just sum(REPEATS_COUNT * VALUE) to get a total for each for each OWNER_ID (position).
Sorry for the delay in my response... Semenov I do not have that kind of query because for my case we only need the total time that the role is needed. But maybe my query helps you in something...
select max(S.POSITION_NAME) CARGO,max(S.ROLE_ID) COD_ROL,sum(Z.REPEATS_COUNT * Z.VALUE) CANT_HORAS_ROLE,S.POSITION_ID, max(W.AV_ID_TIPO_COSTO_TOTAL/160) COSTOXHORA , sum(Z.REPEATS_COUNT * Z.VALUE)* max(W.AV_ID_TIPO_COSTO_TOTAL/160) COSTO_TOTAL from rsc_positions S, itg_compressed_elements Z, av_criterios_costos W where S.position_id=Z.OWNER_ID and W.ROLE_ID=S.ROLE_ID and S.POSITION_ID in (select S1.POSITION_ID from kcrt_request_details T, rsc_positions S1 where S1.STAFFING_PROFILE_ID=T.PARAMETER5 and T.REQUEST_ID = REQID) group by S.POSITION_ID;