V_QUERY_BANKACCOUNTDEPOSITCORRECTION
Provides the ability to query for bank account deposit correction.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| DEPOSITID | uniqueidentifier | Deposit ID | |
| DATE | datetime | Date | |
| REFERENCE | nvarchar(100) | Reference | |
| PAYMENTMETHOD | nvarchar(11) | yes | Method |
| AMOUNT | decimal(19, 4) | Amount | |
| CORRECTIONTYPE | nvarchar(16) | yes | Correction type |
| 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 |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Transaction currency |
| TRANSACTIONAMOUNT | money | Amount (transaction currency) | |
| ORGANIZATIONAMOUNT | money | Amount (organization currency) | |
| BASEEXCHANGERATEID | uniqueidentifier | yes | Base exchange rate |
| BASECURRENCYID | uniqueidentifier | yes | Base currency |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization exchange rate |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:18:08 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_BANKACCOUNTDEPOSITCORRECTION AS
select
FT.ID,
isnull(FT.PARENTID, '00000000-0000-0000-0000-000000000000') as DEPOSITID,
isnull(cast(FT.[DATE] as datetime), '') as DATE,
isnull(cast(left(FT.DESCRIPTION,100) as nvarchar(100)),'') as REFERENCE,
BADCX.PAYMENTMETHOD,
isnull(cast(FT.BASEAMOUNT as decimal(19,4)),0) as AMOUNT,
case cast(FT.TYPECODE - 24 + 2 * BADCX.CORRECTIONTYPECODE as tinyint)
when (0) then N'Sales - Short'
when (1) then N'Sales - Over'
when (2) then N'Treasury - Short'
when (3) then N'Treasury - Over' else null end as CORRECTIONTYPE,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME]
,BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID
,FT.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT
,FT.ORGAMOUNT as ORGANIZATIONAMOUNT
,FT.BASEEXCHANGERATEID
,case CS.BASECURRENCYID
when '00000000-0000-0000-0000-000000000000' then null
else CS.BASECURRENCYID end as BASECURRENCYID
,FT.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
from
dbo.FINANCIALTRANSACTION FT
inner join dbo.BANKACCOUNTDEPOSITCORRECTION_EXT BADCX on BADCX.ID = FT.ID
inner join dbo.BANKACCOUNTDEPOSIT on FT.PARENTID = BANKACCOUNTDEPOSIT.ID
inner join dbo.PDACCOUNTSYSTEM as PAS on PAS.ID = FT.PDACCOUNTSYSTEMID
inner join dbo.CURRENCYSET as CS on PAS.CURRENCYSETID = CS.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = FT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = FT.CHANGEDBYID
where FT.TYPECODE in (24,25)