Cameron pfm.Contact to pt.Transaction relationship
-
Brian.Snavely
- Posts: 18
- Joined: Tue Jan 29, 2013 12:06 pm
Cameron pfm.Contact to pt.Transaction relationship
How do I relate an outbound wire contact to the order contact via the database as I want to pull the reference number that is part of the order contact?
Re: Cameron pfm.Contact to pt.Transaction relationship
Brian,
I am pasting a sample SQL query that gives you the contact's Reference Number when you are coming from the ProTrust side. Please let me know if you have any questions.
select c.ReferenceNumber
from pt.[Transaction] as t
inner join pt.TransactionAttribute as ta on ta.ID = t.AttributeID
inner join pt.Ledger as l on l.ID = t.LedgerID
inner join pf.[order] as o on o.Guid = l.OrderID
inner join pfm.Entity as e on e.Guid# = ta.OrderContactID and e.RootId# = o.RootID
inner join pfm.Contact as c on c.RootId# = o.RootID and e.Id# = c.Id#
where t.GUID = 'C80B2757-E35E-E411-B1F3-E839354BC12C'
and t.IsCurrent = 1
I am pasting a sample SQL query that gives you the contact's Reference Number when you are coming from the ProTrust side. Please let me know if you have any questions.
select c.ReferenceNumber
from pt.[Transaction] as t
inner join pt.TransactionAttribute as ta on ta.ID = t.AttributeID
inner join pt.Ledger as l on l.ID = t.LedgerID
inner join pf.[order] as o on o.Guid = l.OrderID
inner join pfm.Entity as e on e.Guid# = ta.OrderContactID and e.RootId# = o.RootID
inner join pfm.Contact as c on c.RootId# = o.RootID and e.Id# = c.Id#
where t.GUID = 'C80B2757-E35E-E411-B1F3-E839354BC12C'
and t.IsCurrent = 1