I need to create a report to show incidents hours and cost. Do you know which tables in the database store this information?.
Thanks very much
Try imm_incidents or odf_ca_incident (if you have one).
Thanks a lot for your answer.
I can use the imm_incidents to get the incident info such as Name, investment, etc, but I need to get the cost associated to the actual hours entered by resources to the incident in order to have the cost of the incident.
Do you have any idea where I can find this info?.
Enviado el: miércoles, 8 de febrero de 2017 16:00
Para: Maria del Pilar Fariña <email@example.com>
Asunto: Re: - Re: Incident costs
<https://communities.ca.com/?et=watches.email.thread> CA Communities
Re: Incident costs
reply from Suhail Sayed <https://communities.ca.com/people/suhail-sayed?et=watches.email.thread> in CA PPM - View the full discussion <https://communities.ca.com/message/241956772?commentID=241956772&et=watches.email.thread#comment-241956772>
Did you check odf_ca_incident DB Table? Also, check out fin_financials if you are able to find any incident related data there.
Thanks for the answer.
I checked both tables and could not find information about incidents.
The only place I found something is in the prtimeentry and ppa_wip tables.
With the following query I should be able to bring hours and total cost for the incidents but there is a bug in version 14.x, the job Post Incident Financials does not update correctly the field PPA_WIP .EXTERNALBATCHID with the PRTIMESHEETID.PRID so it is no possible to link both tables in order to get the incident data.
from PRTIMESHEET t
inner join PRTIMEENTRY e on e.PRTIMESHEETID = t.PRID
inner join PPA_WIP w on w.EXTERNALBATCHID = t.PRID
inner join PPA_WIP_VALUES v on w.TRANSNO = v.TRANSNO
where v.CURRENCY_TYPE = 'NATURAL' and INCIDENT_ID is not null
You are not making the proper joins. Please refer the screenshot shown below about Transactions ER Diagram.
You just need to be sure that you are using the correct DB Tables to get the data that you require. Hope this helps.
Thanks a lot Suhail, I am going to ckeck it right now. Let you know
Enviado el: jueves, 9 de febrero de 2017 10:46
reply from Suhail Sayed <https://communities.ca.com/people/suhail-sayed?et=watches.email.thread> in CA PPM - View the full discussion <https://communities.ca.com/message/241956974?commentID=241956974&et=watches.email.thread#comment-241956974>
Thanks again for your help.
I have been checking the tables following the ERD, I couldn´t find a way to tie back the PPA_WIP with the PRTIMESHEET and PRTIMEENTRY tables or with the FIN_TXNS table that also holds information about the incidents. The externalbatchid field in the PPA_WIP should be the PRTIMESHEET id, but it is blank even in version 15.1 ( this was reported as a bug).
Tables IMP_TRANSACTIONIMPORT and PPA_TRANSCONTROL are empty I suppose because they are intermediate tables to process transations and when this is done these tables become empty.
Still figuring out how to do this.
Use the following DB Tables for Incidents:-
The third DB Table should help you get the Incident Costs, if I am not mistaken. I hope you are checking the data in these DB Tables on the correct CA Clarity PPM DB Server.
PRTIMEENTRY has a column called PRID. This column PRID is linked with the External_ID column in the PPA_WIP table. And for every row in PPA_WIP table, you will see a column called "TRANSNO". Please use this TRANSNO column to get the related rows from PPA_WIP_VALUES. For each TRANSNO value there are 5 rows in PPA_WIP_VALUES table. Unless you are looking for some specific currency, you can go ahead with the rows which is having CURRENCY_TYPE as NATURAL.
Retrieving data ...