Payor Code for a Transation

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
tim.pageler
Posts: 10
Joined: Fri Jan 10, 2014 4:18 pm

Payor Code for a Transation

Post by tim.pageler » Tue Apr 04, 2017 8:09 am

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?

Lisa Ennis
Posts: 81
Joined: Thu Sep 11, 2008 1:57 pm

Re: Payor Code for a Transation

Post by Lisa Ennis » Tue Apr 04, 2017 10:29 am

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
Report Developer
SoftPro

tim.pageler
Posts: 10
Joined: Fri Jan 10, 2014 4:18 pm

Re: Payor Code for a Transation

Post by tim.pageler » Tue Apr 04, 2017 10:12 pm

That worked. Thank you!

Post Reply