V_QUERY_BANKACCOUNTDEPOSIT
This provides the ability to query for deposits.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System Record ID | |
| BANKACCOUNTID | uniqueidentifier | Account ID | |
| ACCOUNTNAME | nvarchar(100) | Account name | |
| REFERENCE | nvarchar(100) | Reference | |
| TRANSACTIONDATE | datetime | yes | Deposit date |
| POSTSTATUS | nvarchar(11) | yes | Post status |
| POSTDATE | datetime | yes | Post date |
| TRANSACTIONNUMBER | int | Deposit number | |
| AMOUNT | money | Deposit amount | |
| NUMBEROFPAYMENTS | int | yes | Number of payments |
| PROJECTEDNUMBEROFPAYMENTS | int | Projected number of payments | |
| PROJECTEDAMOUNT | decimal(19, 4) | Projected amount | |
| DEFAULTPAYMENTDATE | datetime | yes | Default payment date |
| DEFAULTPAYMENTAMOUNT | decimal(19, 4) | Default payment amount | |
| CHECKALLOWED | int | Check | |
| CASHALLOWED | int | Cash | |
| CREDITCARDALLOWED | int | Credit card | |
| OTHERALLOWED | int | Other | |
| DEFAULTPAYMENTMETHOD | nvarchar(11) | yes | Default payment method |
| STATUS | nvarchar(8) | yes | Deposit status |
| 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 |
| ACCOUNTSYSTEM | nvarchar(50) | yes | Account system |
| PAYMENTCURRENCYID | uniqueidentifier | yes | Payment Currency |
| PAYMENTEXCHANGERATEID | uniqueidentifier | yes | Payment Exchange Rate |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Currency |
| TRANSACTIONAMOUNT | money | Deposit amount (transaction currency) | |
| BASECURRENCYID | uniqueidentifier | yes | Base currency |
| BASEEXCHANGERATEID | uniqueidentifier | yes | Base exchange rate |
| ORGANIZATIONAMOUNT | money | Deposit amount (organization currency) | |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization exchange rate |
| TOTALPAYMENTAMOUNT | money | Deposit amount (payment currency) | |
| DIFFERENCEAMOUNT | decimal(20, 4) | yes | Difference (Amount) |
| DIFFERENCENUMBEROFPAYMENTS | int | yes | Difference (Number of payments) |
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_BANKACCOUNTDEPOSIT AS
select
BANKACCOUNTDEPOSIT.ID,
BATX.BANKACCOUNTID,
--dbo.UFN_BANKACCOUNT_GETACCOUNTNAME(BATX.BANKACCOUNTID) as [ACCOUNTNAME],
BANKACCOUNT.ACCOUNTNAME as [ACCOUNTNAME],
isnull(cast(left(FT.DESCRIPTION,100) as nvarchar(100)),'') as REFERENCE,
cast(FT.[DATE] as datetime) as TRANSACTIONDATE,
FT.POSTSTATUS,
cast(FT.POSTDATE as datetime) as POSTDATE,
BATX.TRANSACTIONNUMBER,
FT.BASEAMOUNT as AMOUNT,
BANKACCOUNTDEPOSIT.NUMBEROFPAYMENTS,
BANKACCOUNTDEPOSIT.PROJECTEDNUMBEROFPAYMENTS,
BANKACCOUNTDEPOSIT.PROJECTEDAMOUNT,
BANKACCOUNTDEPOSIT.DEFAULTPAYMENTDATE,
BANKACCOUNTDEPOSIT.DEFAULTPAYMENTAMOUNT,
CASE WHEN DEFAULTALLOWEDPAYMENTMETHODS & 1 = 1
THEN 1
ELSE 0
END AS [CHECKALLOWED],
CASE WHEN DEFAULTALLOWEDPAYMENTMETHODS & 2 = 2
THEN 1
ELSE 0
END AS [CASHALLOWED],
CASE WHEN DEFAULTALLOWEDPAYMENTMETHODS & 8 = 8
THEN 1
ELSE 0
END AS [CREDITCARDALLOWED],
CASE WHEN DEFAULTALLOWEDPAYMENTMETHODS & 32 = 32
THEN 1
ELSE 0
END AS [OTHERALLOWED],
BANKACCOUNTDEPOSIT.DEFAULTPAYMENTMETHOD,
BANKACCOUNTDEPOSIT.STATUS,
BANKACCOUNTDEPOSIT.DATEADDED,
BANKACCOUNTDEPOSIT.DATECHANGED,
BANKACCOUNTDEPOSIT.TSLONG,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
PDACCOUNTSYSTEM.NAME as [ACCOUNTSYSTEM]
,BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID [PAYMENTCURRENCYID]
,BANKACCOUNTDEPOSIT.TRANSACTIONEXCHANGERATEID [PAYMENTEXCHANGERATEID]
,BANKACCOUNT.TRANSACTIONCURRENCYID
,FT.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT
,case when FT.DELETEDON is null then V.BASECURRENCYID else null end as BASECURRENCYID
,FT.BASEEXCHANGERATEID
,FT.ORGAMOUNT as ORGANIZATIONAMOUNT
,FT.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
,BANKACCOUNTDEPOSIT.TOTALPAYMENTAMOUNT
,coalesce(BANKACCOUNTDEPOSIT.TOTALPAYMENTAMOUNT - BANKACCOUNTDEPOSIT.PROJECTEDAMOUNT, 0) AS [DIFFERENCEAMOUNT]
,coalesce(BANKACCOUNTDEPOSIT.NUMBEROFPAYMENTS - BANKACCOUNTDEPOSIT.PROJECTEDNUMBEROFPAYMENTS, 0) AS [DIFFERENCENUMBEROFPAYMENTS]
from dbo.BANKACCOUNTDEPOSIT
join dbo.FINANCIALTRANSACTION FT on FT.ID = BANKACCOUNTDEPOSIT.ID
join dbo.BANKACCOUNTTRANSACTION_EXT BATX on BATX.ID = FT.ID
join dbo.BANKACCOUNT on BANKACCOUNT.ID = BATX.BANKACCOUNTID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = BANKACCOUNTDEPOSIT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = BANKACCOUNTDEPOSIT.CHANGEDBYID
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FT.ID = V.FINANCIALTRANSACTIONID
left outer join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = BANKACCOUNT.PDACCOUNTSYSTEMID