How to fetch email field in master agreement report?

[Former Member]

Hi All,

I am trying to fetch Supplier Primary Contact Email Id in "My Agreements and Contract Documents" report.

I tried adding below lines of code show in bold in query definition:

T5.EMAIL AS EMAIL

FROM

<%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T3,

<%SCHEMA%>.FCI_CONTRACT T1 LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_INTERNAL_CAT T2 ON T1.INT_CAT_OBJECT_ID = T2.OBJECTID,

<%SCHEMA%>.FCI_MAS_CONTACT T5 LEFT OUTER JOIN <%SCHEMA%>.FCI_CONTRACT T6 ON (T5.OBJECTID = T6.CONTACT_OBJECT_ID)

But when I run the query, it gives wrong value in Email field.(different email Id's then primary contact email in master agreement)

Please guide me what should be right way to get the correct value.

Thanks,

Saloni

[former_member207877 · Accepted solution]

Hi Saloni,

Please use the below given query

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_CONTRACT T1

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_VENDOR T2
ON T1.VENDOR_OBJECT_ID= T2.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_CONTACT T3
ON T1.OBJECTID= T2.PARENT_OBJECT_ID

In result fields please give the below mentioned fields

T2.BUYER_CONTACT_EMAIL  will give you Internal Contact E-mail from contacts tab in supplier
T3.EMAIL will give you EMIAL ID from the Supplier contacts collection from contacts tab in supplier

Please let me know if you need any other assistance.

Thanks,

Raj,

[Former Member · Accepted solution]

Hi Rajshekhar,

Thanks for your reply. After adding below statement query was giving correct results.

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_CONTRACT T1 

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_VENDOR T2
ON T1.VENDOR_OBJECT_ID= T2.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_CONTACT T3
ON T2.OBJECTID= T3.PARENT_OBJECT_ID

WHERE T3.PRIMARY_CONTACT=1

Thanks,

Saloni