Clarity

Hello,

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

Pilar

Hello Pilar,

Try imm_incidents or odf_ca_incident (if you have one).

Suhail.

Hello Suhail,

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?.

Best regards

Pilar

De: suhail-sayed 

Enviado el: miércoles, 8 de febrero de 2017 16:00

Para: Maria del Pilar Fariña <pfarinas@odpe.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>

Hello Pilar,

Did you check odf_ca_incident DB Table? Also, check out fin_financials if you are able to find any incident related data there.

Suhail.

Hello Suhail,

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.

         select  e.INCIDENT_ID,e.INCIDENT_INVESTMENT_ID,e.PRACTSUM/3600,v.TOTALCOST,v.BILLRATE 

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

Hello Pilar,

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.

Suhail.

Thanks a lot Suhail, I am going to ckeck it right now. Let you know

De: suhail-sayed 

Enviado el: jueves, 9 de febrero de 2017 10:46

Para: Maria del Pilar Fariña <pfarinas@odpe.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/241956974?commentID=241956974&et=watches.email.thread#comment-241956974>

Hello Suhail

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.

Best regards

Pilar

Hello Pilar,

Use the following DB Tables for Incidents:-

1. imm_incidents

2. odf_ca_incident

3. imp_transactionimport

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.

Kind Regards,

Suhail Sayed.

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.