V_QUERY_RECEIVABLEPAYMENT
Returns information about a given receivable payment.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | yes | System record ID |
| PAIDBYID | uniqueidentifier | yes | Paid by ID |
| PAYMENTMETHOD | nvarchar(12) | yes | Payment method |
| REFERENCEDATE | char(8) | yes | Cash reference date |
| REFERENCENUMBER | nvarchar(20) | yes | Cash reference number |
| CHECKDATE | UDT_FUZZYDATE | yes | Check date |
| CHECKNUMBER | nvarchar(20) | yes | Check number |
| CARDHOLDERNAME | nvarchar(255) | yes | Name on card |
| CREDITCARDTYPE | nvarchar(100) | yes | Credit type |
| EXPIRESON | UDT_FUZZYDATE | yes | Credit card expires on |
| AUTHORIZATIONCODE | nvarchar(20) | yes | Authorization code |
| DEBITREFERENCEDATE | char(8) | yes | Direct debit reference date |
| DEBITREFERENCENUMBER | nvarchar(20) | yes | Direct debit reference number |
| DIRECTDEBITRESULTCODE | nvarchar(10) | yes | Direct debit result code |
| ISREJECTED | bit | yes | Direct debit is rejected |
| OTHERPAYMENTMETHOD | nvarchar(100) | yes | Other method |
| OTHERREFERENCEDATE | char(8) | yes | Other reference date |
| OTHERREFERENCENUMBER | nvarchar(20) | yes | Other reference number |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/30/2010 11:21:39 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.8.2022.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RECEIVABLEPAYMENT AS
select RECEIVABLEPAYMENT.ID,
RECEIVABLEPAYMENT.CONSTITUENTID as PAIDBYID,
RECEIVABLEPAYMENT.PAYMENTMETHOD,
CASE WHEN RECEIVABLEPAYMENT.PAYMENTMETHODCODE = 0 THEN RECEIVABLEPAYMENT.REFERENCEDATE ELSE NULL END as [REFERENCEDATE],
CASE WHEN RECEIVABLEPAYMENT.PAYMENTMETHODCODE = 0 THEN RECEIVABLEPAYMENT.REFERENCENUMBER ELSE NULL END as [REFERENCENUMBER],
RECEIVABLEPAYMENTCHECK.CHECKDATE,
RECEIVABLEPAYMENTCHECK.CHECKNUMBER,
RECEIVABLEPAYMENTCREDITCARD.CARDHOLDERNAME,
CREDITTYPECODE.DESCRIPTION [CREDITCARDTYPE],
RECEIVABLEPAYMENTCREDITCARD.EXPIRESON,
RECEIVABLEPAYMENTCREDITCARD.AUTHORIZATIONCODE,
CASE WHEN RECEIVABLEPAYMENT.PAYMENTMETHODCODE = 3 THEN RECEIVABLEPAYMENT.REFERENCEDATE ELSE NULL END as [DEBITREFERENCEDATE],
CASE WHEN RECEIVABLEPAYMENT.PAYMENTMETHODCODE = 3 THEN RECEIVABLEPAYMENT.REFERENCENUMBER ELSE NULL END as [DEBITREFERENCENUMBER],
RECEIVABLEPAYMENTDIRECTDEBIT.DIRECTDEBITRESULTCODE,
RECEIVABLEPAYMENTDIRECTDEBIT.ISREJECTED,
OTHERPAYMENTMETHODCODE.DESCRIPTION [OTHERPAYMENTMETHOD],
CASE WHEN RECEIVABLEPAYMENT.PAYMENTMETHODCODE = 10 THEN RECEIVABLEPAYMENT.REFERENCEDATE ELSE NULL END as [OTHERREFERENCEDATE],
CASE WHEN RECEIVABLEPAYMENT.PAYMENTMETHODCODE = 10 THEN RECEIVABLEPAYMENT.REFERENCENUMBER ELSE NULL END as [OTHERREFERENCENUMBER],
-- System fields
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
dbo.FINANCIALTRANSACTION.[DATEADDED],
dbo.FINANCIALTRANSACTION.[DATECHANGED],
dbo.FINANCIALTRANSACTION.[TSLONG]
from dbo.FINANCIALTRANSACTION
left outer join dbo.RECEIVABLEPAYMENT
on FINANCIALTRANSACTION.ID = RECEIVABLEPAYMENT.ID
left outer join dbo.RECEIVABLEPAYMENTCHECK
on FINANCIALTRANSACTION.ID = RECEIVABLEPAYMENTCHECK.ID
left outer join dbo.RECEIVABLEPAYMENTCREDITCARD
on FINANCIALTRANSACTION.ID = RECEIVABLEPAYMENTCREDITCARD.ID
left outer join dbo.RECEIVABLEPAYMENTDIRECTDEBIT
on FINANCIALTRANSACTION.ID = RECEIVABLEPAYMENTDIRECTDEBIT.ID
left outer join dbo.RECEIVABLEPAYMENTOTHERMETHOD
on FINANCIALTRANSACTION.ID = RECEIVABLEPAYMENTOTHERMETHOD.ID
left outer join dbo.CREDITTYPECODE
on RECEIVABLEPAYMENTCREDITCARD.CREDITTYPECODEID = CREDITTYPECODE.ID
left outer join dbo.OTHERPAYMENTMETHODCODE
on RECEIVABLEPAYMENTOTHERMETHOD.OTHERPAYMENTMETHODCODEID = dbo.OTHERPAYMENTMETHODCODE.ID
left outer join dbo.CONSTITUENTACCOUNT
on RECEIVABLEPAYMENTDIRECTDEBIT.CONSTITUENTACCOUNTID = CONSTITUENTACCOUNT.ID
left join dbo.CHANGEAGENT as [ADDEDBY]
on [ADDEDBY].ID = dbo.FINANCIALTRANSACTION.[ADDEDBYID]
left join dbo.CHANGEAGENT as [CHANGEDBY]
on [CHANGEDBY].ID = dbo.FINANCIALTRANSACTION.[CHANGEDBYID]
where FINANCIALTRANSACTION.TYPECODE in (104, 105)