Query for Contract Phase duration

[Former Member]

Hi,

my management want a report on each phase of the contract (e.g. how long is the duration of each phase : from "draft" to "sent to vendor" to "manager approval" etc). This can be easily found under the Master Agreement, contract tab. Click on the contract document, document --> Phase information. However this information is not downloadable. The management would like to download this information to do a metric measurement. So the only way we can think of is to write a query. However, we are not sure where this contract phase is residing in which schema. Does anyone out there know where can we retrieve this information from? Is it FCI_DOC_WORKFLOW_HISTORY (CLASS ID : 576)?

Thank you.

[former_member398983]

Hi

Look on the below reply from Vigneshwar Reddy... I did the same but mine has a problem when it comes to seconds. In some instances, it adds one extra second from those that you find on the phase information in a contact document. You can try it, it works fine I just have an issue with that extra 1 second

=========================================================================

Hi Marcin,

You will get all the phase information related data in FCI_DOC_WORKFLOW_HISTORY.

You link FCI_DOC_WORKFLOW_HISTORY T1 table with FCI_PRO_PROJECTS T2. Under results tab,

create a Duration field with database column name as

(CASE WHEN T1.END_DATETIME IS NULL THEN NULL ELSE (TRUNC(<%SCHEMA%>.FCI_DATEDIFF(T1.START_DATETIME,T1.END_DATETIME, 2)/86400) <%+%> 'days :' <%+%> TRUNC(MOD(<%SCHEMA%>.FCI_DATEDIFF(T1.START_DATETIME,T1.END_DATETIME, 2),86400)/3600) <%+%> ':' <%+%> TRUNC(MOD(MOD(<%SCHEMA%>.FCI_DATEDIFF(T1.START_DATETIME,T1.END_DATETIME, 2),86400),3600)/60) <%+%> ':' <%+%> MOD(MOD(MOD(<%SCHEMA%>.FCI_DATEDIFF(T1.START_DATETIME,T1.END_DATETIME, 2),86400),3600),60)) END).

This will solve your issue.

Regards,

Vignesh