RE: reporting problem for listing collaborators in MA and AG

[Former Member]

Hi,

I am facing a problem while listing the current/active collaborators by MA(Master Agreement) and AG(Sub Agreement). collaborators are in the header record.

while joining table FCI_DOC_COLLABORATOR and FCI_CONTRACT on PARENT_OBJECT_ID=OBJECTID,

the resulted reports shows all the collaborators both active and inactive.

I want to filter only active collaborators, but I can't find a field to filter it.

Could you please suggest?

Thanks In Advance,

Roopesh Vardhan

[Former Member · Accepted solution]

Hi Roopesh,

You might also want to join on User Account to be able to retrieve user inactive status.

Sample query:

SELECT <%RESULTS%>

FROM <%SCHEMA%>.FCI_CONTRACT T1

     INNER JOIN <%SCHEMA%>.FCI_DOC_COLLABORATOR T2 ON T2.PARENT_OBJECT_ID = T1.OBJECTID

     INNER JOIN <%SCHEMA%>.FCI_UPP_USER_ACCOUNT T3 ON T2.USER_NAME_OBJECT_ID = T3.OBJECTID

WHERE 

T3.INACTIVE = 0

Results: (I selected these columns to be able to see the desired results)

T1.UNIQUE_DOC_NAME

T2.DISPLAY_NAME

T3.INACTIVE

This will give the active collaborators. However, you may want to change the where clause to add more filtering according to your requirements.

Hope this helps!

Thanks and regards,

Bindu

[Former Member · Accepted solution]

Hi Bindu,

This was really a helpful answer,

I have one more doubt adding a extension definition by hiding the original comment field which we will find after reviewing the contract document and adding a Extension TEXT field in the place of the original.

I Did it by adding "Workflow Approval Wizard", but I could not find data from that which we get from the database

     But I could not find any data from the table FCI_DYN_2147482848.

Can you please help !!

Thanks,

Roopesh

[Former Member · Accepted solution]

Hi Roopesh,

If you observe your screenshots carefully then,

• In 3rd screenshot, you have created an extension with Bid comments which will going into FCI_DYN_2147482848 extension schema and in 4th screenshot, you are showing the field with the name Comments which is not matching with Bid comments.
• That means the extension field is not reflected in that workflow.

Could you pls recheck and let me know.

Regards,

Vignesh

[Former Member · Accepted solution]

Hi Vignesh,

I am sorry, My screen shot showed you different extension, But with this table and fields I could not get any data

for a rough when I checked the table for results .

Result, No results Found

[Former Member · Accepted solution]

Hi Roopesh,

Can you pls remove the DISTINCT from query and add T1.OBJECTID in the Results Fields tab and check the output. It should surely solve your problem.

Do let me know once you do this.

Regards,

Vignesh

[Former Member · Accepted solution]

Hi Vignesh,

I tried that, I got the same result as below no results found.

Thanks,

Roopesh