V_QUERY_MISCELLANEOUSPAYMENT
This provides the ability to query for miscellaneous payments.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System Record ID | |
| PAYMENTSOURCE | nvarchar(255) | yes | Reference |
| PAYMENTMETHOD | nvarchar(14) | yes | Payment method |
| REFERENCEDATE | date | yes | Reference date |
| AMOUNT | money | Payment amount | |
| PAYMENTDATE | datetime | yes | Payment date |
| POSTSTATUS | varchar(11) | Post status | |
| POSTDATE | datetime | yes | Post date |
| DEPOSITID | uniqueidentifier | yes | Deposit ID |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| BATCHNUMBER | nvarchar(100) | Batch number | |
| RECEIPTAMOUNT | money | Receipt amount | |
| RECEIPTDATE | datetime | yes | Receipt date |
| RECEIPTNUMBER | int | yes | Receipt number |
| CHECKDATE | UDT_FUZZYDATE | yes | Check date |
| CHECKNUMBER | nvarchar(20) | yes | Check number |
| CASHREFERENCEDATE | UDT_FUZZYDATE | yes | Cash reference date |
| CASHREFERENCENUMBER | nvarchar(20) | yes | Cash reference number |
| CARDHOLDERNAME | nvarchar(255) | yes | Name on card |
| CREDITTYPE | nvarchar(100) | yes | Card type |
| EXPIRESON | UDT_FUZZYDATE | yes | Credit card expires on |
| AUTHORIZATIONCODE | nvarchar(20) | yes | Authorization code |
| OTHERPAYMETHODDESCRIPTION | nvarchar(100) | yes | Other method |
| OTHERPAYREFERENCEDATE | UDT_FUZZYDATE | yes | Other reference date |
| OTHERPAYREFERENCENUMBER | nvarchar(20) | yes | Other reference number |
| TRANSACTIONNUMBER | varchar(30) | yes | Deposit 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 |
| RECEIPTSTATUS | nvarchar(50) | yes | Receipt status |
| ACCOUNTSYSTEM | nvarchar(50) | yes | Account system |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| BASEEXCHANGERATEID | uniqueidentifier | yes | Base exchange rate |
| ORGANIZATIONAMOUNT | money | Payment amount (organization currency) | |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization exchange rate |
| TRANSACTIONAMOUNT | money | Payment amount (transaction currency) | |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Transaction currency ID |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 10/27/2018 10:42:52 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.186.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MISCELLANEOUSPAYMENT AS
select
REVENUE.ID,
REVENUEREFERENCE.REFERENCE as [PAYMENTSOURCE],
REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0 then case when isdate([CASH].REFERENCEDATE) <> 0 then convert(date, [CASH].REFERENCEDATE, 111) end
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 1 then case when isdate([CHECK].CHECKDATE) <> 0 then convert(date, [CHECK].CHECKDATE, 111) end
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 then null
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 then case when isdate([OTHER].REFERENCEDATE) <> 0 then convert(date, [OTHER].REFERENCEDATE, 111) end
end AS [REFERENCEDATE],
REVENUE.AMOUNT,
REVENUE.DATE as [PAYMENTDATE],
case REVENUE.DONOTPOST when 1 then 'Do not post' else (case when (SELECT count(ID) from REVENUEPOSTED
WHERE REVENUEPOSTED.ID = REVENUE.ID) = 1 then 'Posted' else 'Not posted' end ) end as [POSTSTATUS],
REVENUE.POSTDATE,
BANKACCOUNTDEPOSITPAYMENT.DEPOSITID,
REVENUE.DATEADDED,
REVENUE.DATECHANGED,
REVENUE.TSLONG,
REVENUE.BATCHNUMBER,
REVENUE.RECEIPTAMOUNT,
(select top 1 RR.RECEIPTDATE from dbo.REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID order by RECEIPTPROCESSDATE desc) as RECEIPTDATE,
(select top 1 RR.RECEIPTNUMBER from dbo.REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID order by RECEIPTPROCESSDATE desc) as RECEIPTNUMBER,
[CHECK].CHECKDATE,
[CHECK].CHECKNUMBER,
[CASH].REFERENCEDATE as [CASHREFERENCEDATE],
[CASH].REFERENCENUMBER as [CASHREFERENCENUMBER],
[CREDIT].CARDHOLDERNAME,
CREDITTYPECODE.DESCRIPTION as [CREDITTYPE],
[CREDIT].EXPIRESON,
[CREDIT].AUTHORIZATIONCODE,
[OTHERPAYCODE].DESCRIPTION as [OTHERPAYMETHODDESCRIPTION],
[OTHERPAY].REFERENCEDATE as [OTHERPAYREFERENCEDATE],
[OTHERPAY].REFERENCENUMBER as [OTHERPAYREFERENCENUMBER],
cast(BANKACCOUNTTRANSACTION.TRANSACTIONNUMBER as varchar) as [TRANSACTIONNUMBER],
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
dbo.UFN_REVENUE_GETRECEIPTSTATUS(REVENUE.ID) as [RECEIPTSTATUS],
(
select PS.NAME from dbo.PDACCOUNTSYSTEM as PS
inner join dbo.PDACCOUNTSYSTEMFORREVENUE as PR on PS.ID = PR.PDACCOUNTSYSTEMID
where PR.ID = REVENUE.ID
) as [ACCOUNTSYSTEM],
REVENUE.BASECURRENCYID,
REVENUE.BASEEXCHANGERATEID,
REVENUE.ORGANIZATIONAMOUNT,
REVENUE.ORGANIZATIONEXCHANGERATEID,
REVENUE.TRANSACTIONAMOUNT,
REVENUE.TRANSACTIONCURRENCYID
from dbo.Revenue
inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID and REVENUESPLIT.APPLICATIONCODE = 11 and REVENUESPLIT.TYPECODE = 8
left outer join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
left outer join REVENUEREFERENCE on REVENUE.ID = REVENUEREFERENCE.ID
left join dbo.BANKACCOUNTDEPOSITPAYMENT on BANKACCOUNTDEPOSITPAYMENT.ID = REVENUE.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = REVENUE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = REVENUE.CHANGEDBYID
left outer join dbo.CASHPAYMENTMETHODDETAIL as [CASH] on [CASH].ID = REVENUEPAYMENTMETHOD.ID
left outer join dbo.CHECKPAYMENTMETHODDETAIL as [CHECK] on [CHECK].ID = REVENUEPAYMENTMETHOD.ID
left outer join dbo.CREDITCARDPAYMENTMETHODDETAIL as [CREDIT] on [CREDIT].ID = REVENUEPAYMENTMETHOD.ID
left outer join dbo.OTHERPAYMENTMETHODDETAIL as [OTHER] on [OTHER].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.OTHERPAYMENTMETHODDETAIL as [OTHERPAY] on [OTHERPAY].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.OTHERPAYMENTMETHODCODE as [OTHERPAYCODE] on [OTHERPAYCODE].ID = [OTHERPAY].OTHERPAYMENTMETHODCODEID
left join dbo.CREDITTYPECODE on CREDITTYPECODE.ID = [CREDIT].CREDITTYPECODEID
left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
where
REVENUE.TRANSACTIONTYPECODE = 0 and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE IN (0,1,2,9,10)