Page 1 of 1

Payor Code for a Transation

Posted: Tue Apr 04, 2017 8:09 am
by tim.pageler
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?

Re: Payor Code for a Transation

Posted: Tue Apr 04, 2017 10:29 am
by Lisa Ennis
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

Re: Payor Code for a Transation

Posted: Tue Apr 04, 2017 10:12 pm
by tim.pageler
That worked. Thank you!