USR_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 |
| REFERENCE | nvarchar(20) | yes | Reference/check number |
| REFERENCEDATE | date | yes | Reference date |
| AMOUNT | money | Amount | |
| PAYMENTDATE | datetime | yes | Payment date |
| POSTSTATUS | varchar(14) | 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 |
| 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 |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 1/27/2010 6:50:51 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.5.465.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.USR_V_QUERY_MISCELLANEOUSPAYMENT AS
select
REVENUE.ID,
REVENUEREFERENCE.REFERENCE as [PAYMENTSOURCE],
REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0 then [CASH].REFERENCENUMBER
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 1 then [CHECK].CHECKNUMBER
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 then [CREDIT].CREDITCARDPARTIALNUMBER
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 then [OTHER].REFERENCENUMBER
end AS [REFERENCE],
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 yet posted' end ) end as [POSTSTATUS],
REVENUE.POSTDATE,
BANKACCOUNTDEPOSITPAYMENT.DEPOSITID,
REVENUE.DATEADDED,
REVENUE.DATECHANGED,
REVENUE.TSLONG,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME]
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
where
REVENUE.TRANSACTIONTYPECODE = 0 and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE IN (0,1,2,9,10)