V_SEARCH_UNLINKEDCREDITPAYMENT
Search for refund payments that have been reconciled but are not linked to a bank account deposit.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| TRANSACTIONDATE | datetime | yes | |
| AMOUNT | money | ||
| ORDER | nvarchar(100) | yes | |
| PATRONNAME | nvarchar(154) | yes | |
| USERNAME | nvarchar(128) | yes | |
| PAYMENTMETHOD | nvarchar(14) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 10/27/2011 12:19:32 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.92.537.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_SEARCH_UNLINKEDCREDITPAYMENT AS
select CREDITPAYMENT.ID
,CREDIT.TRANSACTIONDATE
,CREDITPAYMENT.AMOUNT
,isnull(convert(nvarchar(100), SALESORDER.SEQUENCEID), R.LOOKUPID) [ORDER]
,dbo.UFN_CONSTITUENT_BUILDNAME(CREDIT.CONSTITUENTID) as [PATRONNAME]
,dbo.UFN_APPUSER_GETNAME(APPUSER.ID) as [USERNAME]
,CREDITPAYMENT.PAYMENTMETHOD
from dbo.CREDITPAYMENT
inner join dbo.CREDIT on CREDIT.ID = CREDITPAYMENT.CREDITID
left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT LINK on LINK.ID = CREDITPAYMENT.ID
inner join dbo.APPUSER on CREDIT.APPUSERID = APPUSER.ID
left join dbo.SALESORDER on SALESORDER.ID = CREDIT.SALESORDERID
left join dbo.REVENUESPLIT RS on RS.ID = CREDITPAYMENT.REVENUESPLITID
left join dbo.REVENUE R on R.ID = RS.REVENUEID
where CREDITPAYMENT.RECONCILIATIONID is not null and LINK.DEPOSITID is null