V_QUERY_SALESORDER
Provides the ability to query sales orders.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| TRANSACTIONDATE | date | yes | Transaction date |
| COMMENTS | nvarchar(1000) | Comments | |
| AMOUNT | money | yes | Amount |
| LOOKUPID | nvarchar(100) | yes | Lookup ID |
| CONSTITUENTID | uniqueidentifier | yes | CONSTITUENTID |
| SALESMETHODTYPE | nvarchar(13) | yes | Sales method type |
| REVENUEID | uniqueidentifier | yes | REVENUEID |
| DELIVERYMETHODID | uniqueidentifier | yes | DELIVERYMETHODID |
| RECIPIENTID | uniqueidentifier | yes | RECIPIENTID |
| ADDRESSID | uniqueidentifier | yes | ADDRESSID |
| PHONEID | uniqueidentifier | yes | PHONEID |
| EMAILADDRESSID | uniqueidentifier | yes | EMAILADDRESSID |
| SAMEASPATRON | bit | Same as patron | |
| REFUNDSTATUS | int | yes | Refund status |
| APPUSERID | uniqueidentifier | yes | APPUSERID |
| 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 |
| STATUS | nvarchar(10) | yes | Sales order status |
| REFUNDSTATUSTEXT | varchar(18) | ||
| POSTCODE | nvarchar(12) | yes | |
| COUNTRY_TRANSLATION | nvarchar(100) | yes | |
| ORDERCOMPLETEDATE | datetime | yes | |
| ISTAXEXEMPT | int | ||
| SALESORDERTAXEXEMPTREASONCODEID | uniqueidentifier | yes | |
| SALESORDERTAXEXEMPTREASONCODEID_TRANSLATION | nvarchar(100) | yes | |
| TAXEXEMPTCOMMENTS | nvarchar(255) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:26:05 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SALESORDER AS
select
SALESORDER.ID,
cast(SALESORDER.TRANSACTIONDATE as date) as TRANSACTIONDATE,
SALESORDER.COMMENTS,
SALESORDER.AMOUNT,
SALESORDER.LOOKUPID,
SALESORDER.CONSTITUENTID,
SALESORDER.SALESMETHODTYPE,
SALESORDER.REVENUEID,
SALESORDER.DELIVERYMETHODID,
SALESORDER.RECIPIENTID,
CONTACTRECORDS.ADDRESSID as ADDRESSID,
CONTACTRECORDS.PHONEID as PHONEID,
CONTACTRECORDS.EMAILADDRESSID as EMAILADDRESSID,
SALESORDER.SAMEASPATRON,
REFUNDSTATUS_BULK.REFUNDSTATUS,
SALESORDER.APPUSERID,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SALESORDER.DATEADDED,
SALESORDER.DATECHANGED,
SALESORDER.TSLONG,
SALESORDER.STATUS,
case REFUNDSTATUS_BULK.REFUNDSTATUS
when 0 then 'Not refunded'
when 1 then 'Partially refunded'
else 'Fully refunded'
end as REFUNDSTATUSTEXT,
coalesce(SALESORDERMARKETINGINFORMATION.POSTCODE, ADDRESS.POSTCODE, '') as POSTCODE,
coalesce(SALESORDERMARKETINGINFORMATIONCOUNTRY.DESCRIPTION, COUNTRY.DESCRIPTION, '') as COUNTRY_TRANSLATION,
SALESORDER.TRANSACTIONDATE as ORDERCOMPLETEDATE,
case
when SALESORDERTAXEXEMPTINFO.ID is null then 0
else 1
end as ISTAXEXEMPT,
SALESORDERTAXEXEMPTINFO.SALESORDERTAXEXEMPTREASONCODEID,
SALESORDERTAXEXEMPTREASONCODE.DESCRIPTION as SALESORDERTAXEXEMPTREASONCODEID_TRANSLATION,
SALESORDERTAXEXEMPTINFO.COMMENTS as TAXEXEMPTCOMMENTS
/*EXTENSION*/
from
dbo.SALESORDER
left join
dbo.UFN_SALESORDER_GETREFUNDSTATUS_BULK() as REFUNDSTATUS_BULK on SALESORDER.ID = REFUNDSTATUS_BULK.ID
left outer join
dbo.CHANGEAGENT as [ADDEDBY] on SALESORDER.ADDEDBYID = [ADDEDBY].ID
left outer join
dbo.CHANGEAGENT as [CHANGEDBY] on SALESORDER.CHANGEDBYID = [CHANGEDBY].ID
outer apply
dbo.UFN_SALESORDER_CONTACTRECORDS(SALESORDER.ID) as CONTACTRECORDS
left outer join
dbo.SALESORDERMARKETINGINFORMATION on SALESORDER.ID = SALESORDERMARKETINGINFORMATION.ID
left outer join
dbo.COUNTRY SALESORDERMARKETINGINFORMATIONCOUNTRY on SALESORDERMARKETINGINFORMATION.COUNTRYID = SALESORDERMARKETINGINFORMATIONCOUNTRY.ID
left outer join
dbo.ADDRESS on SALESORDER.CONSTITUENTID = ADDRESS.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
left outer join
dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
left outer join
dbo.SALESORDERTAXEXEMPTINFO on SALESORDERTAXEXEMPTINFO.ID = SALESORDER.ID
left outer join
dbo.SALESORDERTAXEXEMPTREASONCODE on SALESORDERTAXEXEMPTREASONCODE.ID = SALESORDERTAXEXEMPTINFO.SALESORDERTAXEXEMPTREASONCODEID
where
SALESORDER.SALESMETHODTYPECODE <> 2 -- Online Sales
or SALESORDER.STATUSCODE in (1, 7) -- Complete, Unresolved