Good Morning,
I am trying to do some analysis on receipts over a certain time period to determine whereby I am looking to determine what % of receipts are of type Incoming Wire. I would also like to understand what % of those wires are coming from different contact types (i.e. Buyers, Lenders, etc.). I found the payorpayee column in the pt.transaction table, but would prefer to use the payor code field associated with the receipt. Is this column available in the database somewhere?
Payor Code for a Transation
Moderator: Lisa Ennis
-
- Posts: 84
- Joined: Thu Sep 11, 2008 1:57 pm
Re: Payor Code for a Transation
Below are the joins for many of the ProTrust (pt schema) tables. You would need to join to pt.ContactDetail, then to zref.ContactType. Hope this helps!
Code: Select all
SELECT *
FROM
pt.[Transaction] AS T
INNER JOIN pt.TransactionExtended AS TE ON T.[GUID]=TE.ID
INNER JOIN [pt].[TransactionDetail] AS TD ON TD.ID = T.DetailID
INNER JOIN pt.Ledger AS L ON T.LedgerID=L.[ID]
INNER JOIN zref.TransactionType AS TT ON T.Kind=TT.Code
INNER JOIN zref.TransactionStatus AS zTS ON T.[Status]=zTS.ID
INNER JOIN pt.TransactionAttribute AS TA ON TA.ID = T.AttributeID
INNER JOIN pt.ContactDetail AS OC ON TA.OrderContactID=OC.[ID]
INNER JOIN zref.ContactType AS zCT ON OC.[Type]=zCT.ID
LEFT OUTER JOIN (SELECT [Guid], [ID] FROM [pt].[Transaction]) AS TTX ON TTX.[ID] = T.[RelatedTransactionID]
LEFT OUTER JOIN pt.GroupDeposit AS GDEP ON TD.GroupDepositID=GDEP.ID
LEFT OUTER JOIN zref.MediumType AS zM ON T.MediumID = zM.[ID]
LEFT OUTER JOIN (SELECT TTX.[Guid], TTX.[ID], TXL.Name AS TransferToFrom_LedgerName
FROM pt.[Transaction] AS TTX
INNER JOIN pt.Ledger AS TXL ON TTX.LedgerID=TXL.ID ) AS TTF ON TTF.ID = T.RelatedTransactionID
Lisa Ennis
Senior Report Developer
SoftPro
Senior Report Developer
SoftPro
-
- Posts: 10
- Joined: Fri Jan 10, 2014 4:18 pm
Re: Payor Code for a Transation
That worked. Thank you!