V_QUERY_PAYABLES_TRANSACTION
Query for payables transactions.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| USERDEFINEDID | nvarchar(100) | Number | |
| TYPE | nvarchar(27) | yes | Type |
| AMOUNT | money | Amount | |
| DATE | datetime | yes | Date |
| POSTDATE | date | yes | Post date |
| POSTSTATUS | nvarchar(11) | yes | Post status |
| CONSTITUENTID | uniqueidentifier | yes | Constituent ID |
| 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 |
| PVID | uniqueidentifier | yes | System record ID |
| PAYABLEVENDORNAME | nvarchar(154) | yes | Name |
| CUSTOMERNUMBER | nvarchar(20) | yes | Customer number |
| PAYMENTTERMNAME | nvarchar(20) | yes | Payment terms |
| PAYMENTMETHOD | nvarchar(5) | yes | Payment method |
| INCLUDEIN1099 | bit | yes | Include in 1099 reporting |
| ALLOWPAYMENTS | bit | yes | Allow payments |
| ALLOWTOCREATEINVOICES | bit | yes | Allow invoices |
| TAXIDNUMBER | nvarchar(64) | yes | Tax Identification Number |
| INVOICE_PURCHASEORDERID | nvarchar(20) | yes | PO number (Invoice) |
| INVOICE_DATEDUE | datetime | yes | Due date |
| INVOICE_DISCOUNTPERCENT | decimal(20, 4) | yes | Discount percent |
| INVOICE_DISCOUNTAMOUNT | money | yes | Discount amount |
| INVOICE_DISCOUNTEXPIRATIONDATE | datetime | yes | Discount expiration date |
| INVOICE_PAYMENTMETHOD | nvarchar(5) | yes | Payment method |
| INVOICE_BANKACCOUNTID | nvarchar(100) | yes | Bank account (Invoice) |
| INVOICE_SEPARATEPAYMENT | bit | yes | Create a separate payment |
| INVOICE_HOLDPAYMENT | bit | yes | On hold |
| CREDITMEMO_PURCHASEORDERID | nvarchar(20) | yes | PO number (Credit Memo) |
| CREDITMEMO_BANKACCOUNTID | nvarchar(100) | yes | Bank account (Credit Memo) |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:17:54 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_PAYABLES_TRANSACTION AS
select
FT.ID,
FT.USERDEFINEDID,
FT.TYPE,
FT.TRANSACTIONAMOUNT as AMOUNT,
cast(FT.DATE as datetime) [DATE],
FTLI.POSTDATE,
FTLI.POSTSTATUS,
FT.CONSTITUENTID,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
FT.DATEADDED,
FT.DATECHANGED,
FT.TSLONG,
PV.ID as PVID,
(SELECT NAME FROM DBO.CONSTITUENT WHERE ID = PV.ID) PAYABLEVENDORNAME,
PV.CUSTOMERNUMBER,
(SELECT NAME FROM DBO.PAYMENTTERM WHERE ID = PV.PAYMENTTERMID) PAYMENTTERMNAME,
PV.PAYMENTMETHOD,
PV.INCLUDEIN1099,
PV.ALLOWPAYMENTS,
PV.ALLOWTOCREATEINVOICES,
PV.TAXIDNUMBER,
I.PURCHASEORDERID as INVOICE_PURCHASEORDERID,
I.DATEDUE as INVOICE_DATEDUE,
I.DISCOUNTPERCENT as INVOICE_DISCOUNTPERCENT,
I.DISCOUNTAMOUNT as INVOICE_DISCOUNTAMOUNT,
I.DISCOUNTEXPIRATIONDATE as INVOICE_DISCOUNTEXPIRATIONDATE,
I.PAYMENTMETHOD as INVOICE_PAYMENTMETHOD,
(SELECT ACCOUNTNAME FROM DBO.BANKACCOUNT WHERE ID = I.BANKACCOUNTID) INVOICE_BANKACCOUNTID,
I.SEPARATEPAYMENT as INVOICE_SEPARATEPAYMENT,
I.HOLDPAYMENT as INVOICE_HOLDPAYMENT,
CM.PURCHASEORDERID as CREDITMEMO_PURCHASEORDERID,
(SELECT ACCOUNTNAME FROM DBO.BANKACCOUNT WHERE ID = CM.BANKACCOUNTID) CREDITMEMO_BANKACCOUNTID
from
dbo.FINANCIALTRANSACTION as FT
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on ((FT.ID = FTLI.FINANCIALTRANSACTIONID) and (FTLI.TYPECODE = 0) and (FTLI.DELETEDON is null))
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = FT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = FT.CHANGEDBYID
left join dbo.VENDOR as PV on PV.ID = FT.CONSTITUENTID
left join dbo.INVOICE as I on I.ID = FT.ID
left join dbo.CREDITMEMO as CM on CM.ID = FT.ID
where
FT.TYPECODE in (101,102)