V_QUERY_GROUPSALESCONTRACTOUTPUT
Provides the ability to query reservation fields that are used in contracts.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ID | |
| PATRON_NAME | nvarchar(154) | yes | PATRON_NAME |
| PATRON_ADDRESS | nvarchar(150) | yes | PATRON_ADDRESS |
| PATRON_CITY | nvarchar(50) | yes | PATRON_CITY |
| PATRON_STATE | nvarchar(50) | yes | PATRON_STATE |
| PATRON_ZIP | nvarchar(12) | yes | PATRON_ZIP |
| CONTACT_NAME | nvarchar(154) | yes | CONTACT_NAME |
| CONTACT_ADDRESS | nvarchar(150) | yes | CONTACT_ADDRESS |
| CONTACT_CITY | nvarchar(50) | yes | CONTACT_CITY |
| CONTACT_STATE | nvarchar(50) | yes | CONTACT_STATE |
| CONTACT_ZIP | nvarchar(12) | yes | CONTACT_ZIP |
| CONTACT_PHONE | nvarchar(100) | yes | CONTACT_PHONE |
| CONTACT_EMAIL | UDT_EMAILADDRESS | yes | CONTACT_EMAIL |
| ORDER_TOTAL | money | yes | ORDER_TOTAL |
| BALANCE_AFTER_DEPOSIT | money | yes | BALANCE_AFTER_DEPOSIT |
| ORDER_BALANCE | money | yes | ORDER_BALANCE |
| DEPOSIT_AMOUNT | money | DEPOSIT_AMOUNT | |
| DEPOSIT_DUE_DATE | date | yes | DEPOSIT_DUE_DATE |
| FINAL_COUNT_DUE_DATE | date | yes | FINAL_COUNT_DUE_DATE |
| ORDER_BALANCE_DUE_DATE | date | ORDER_BALANCE_DUE_DATE | |
| RESERVATION_DATE | date | RESERVATION_DATE | |
| ARRIVAL_TIME | UDT_HOURMINUTE | ARRIVAL_TIME | |
| NUMBER_OF_BUSES | smallint | NUMBER_OF_BUSES | |
| ARRIVAL_AREA | nvarchar(100) | yes | ARRIVAL_AREA |
| NUMBER_OF_VISITORS | int | yes | NUMBER_OF_VISITORS |
| ITINERARY_BLOCK | nvarchar(max) | yes | ITINERARY_BLOCK |
| RESOURCE_BLOCK | nvarchar(max) | yes | RESOURCE_BLOCK |
| ORGANIZATION_NAME | nvarchar(100) | yes | ORGANIZATION_NAME |
| ORGANIZATION_ADDRESS | nvarchar(150) | yes | ORGANIZATION_ADDRESS |
| ORGANIZATION_CITY | nvarchar(50) | yes | ORGANIZATION_CITY |
| ORGANIZATION_STATE | nvarchar(50) | yes | ORGANIZATION_STATE |
| ORGANIZATION_ZIP | nvarchar(12) | yes | ORGANIZATION_ZIP |
| ORGANIZATION_PHONE | nvarchar(100) | yes | ORGANIZATION_PHONE |
| ORGANIZATION_WEB | UDT_WEBADDRESS | yes | ORGANIZATION_WEB |
| FACILITY_BLOCK | nvarchar(max) | yes | FACILITY_BLOCK |
| SECURITY_DEPOSIT_AMOUNT | money | SECURITY_DEPOSIT_AMOUNT | |
| SECURITY_DEPOSIT_DUE_DATE | date | yes | SECURITY_DEPOSIT_DUE_DATE |
| SECURITY_DEPOSIT_BALANCE | money | yes | SECURITY_DEPOSIT_BALANCE |
| TKTDESC1 | nvarchar(1024) | yes | |
| TKTSTARTDT1 | date | yes | |
| TKTSTARTTM1 | varchar(8) | yes | |
| TKTQTY1 | int | yes | |
| TKTPRICE1 | money | yes | |
| TKTDSC1 | money | yes | |
| TKTFEE1 | money | yes | |
| TKTTOTAL1 | money | yes | |
| TKTDESC2 | nvarchar(1024) | yes | |
| TKTSTARTDT2 | date | yes | |
| TKTSTARTTM2 | varchar(8) | yes | |
| TKTQTY2 | int | yes | |
| TKTPRICE2 | money | yes | |
| TKTDSC2 | money | yes | |
| TKTFEE2 | money | yes | |
| TKTTOTAL2 | money | yes | |
| TKTDESC3 | nvarchar(1024) | yes | |
| TKTSTARTDT3 | date | yes | |
| TKTSTARTTM3 | varchar(8) | yes | |
| TKTQTY3 | int | yes | |
| TKTPRICE3 | money | yes | |
| TKTDSC3 | money | yes | |
| TKTFEE3 | money | yes | |
| TKTTOTAL3 | money | yes | |
| TKTDESC4 | nvarchar(1024) | yes | |
| TKTSTARTDT4 | date | yes | |
| TKTSTARTTM4 | varchar(8) | yes | |
| TKTQTY4 | int | yes | |
| TKTPRICE4 | money | yes | |
| TKTDSC4 | money | yes | |
| TKTFEE4 | money | yes | |
| TKTTOTAL4 | money | yes | |
| TKTDESCREST | varchar(13) | yes | |
| TKTQTYREST | int | yes | |
| TKTPRICEREST | int | yes | |
| TKTDSCREST | money | yes | |
| TKTFEEREST | money | yes | |
| TKTTOTALREST | money | yes | |
| RSCDESC1 | nvarchar(203) | yes | |
| RSCSTDT1 | datetime | yes | |
| RSCENDDT1 | datetime | yes | |
| RSCQTY1 | int | yes | |
| RSCPRICE1 | money | yes | |
| RSCTOTAL1 | money | yes | |
| RSCDESC2 | nvarchar(203) | yes | |
| RSCSTDT2 | datetime | yes | |
| RSCENDDT2 | datetime | yes | |
| RSCQTY2 | int | yes | |
| RSCPRICE2 | money | yes | |
| RSCTOTAL2 | money | yes | |
| RSCDESC3 | nvarchar(203) | yes | |
| RSCSTDT3 | datetime | yes | |
| RSCENDDT3 | datetime | yes | |
| RSCQTY3 | int | yes | |
| RSCPRICE3 | money | yes | |
| RSCTOTAL3 | money | yes | |
| RSCDESC4 | nvarchar(203) | yes | |
| RSCSTDT4 | datetime | yes | |
| RSCENDDT4 | datetime | yes | |
| RSCQTY4 | int | yes | |
| RSCPRICE4 | money | yes | |
| RSCTOTAL4 | money | yes | |
| RSCDESCREST | varchar(15) | yes | |
| RSCTOTALREST | money | yes | |
| STFTYPE1 | nvarchar(100) | yes | |
| STFNAME1 | nvarchar(154) | yes | |
| STFST1 | varchar(8) | yes | |
| STFET1 | varchar(8) | yes | |
| STFIN1 | nvarchar(154) | yes | |
| STFII1 | nvarchar(100) | yes | |
| STFOPEN1 | int | yes | |
| STFPRICE1 | money | yes | |
| STFTYPE2 | nvarchar(100) | yes | |
| STFNAME2 | nvarchar(154) | yes | |
| STFST2 | varchar(8) | yes | |
| STFET2 | varchar(8) | yes | |
| STFIN2 | nvarchar(154) | yes | |
| STFII2 | nvarchar(100) | yes | |
| STFOPEN2 | int | yes | |
| STFPRICE2 | money | yes | |
| STFTYPE3 | nvarchar(100) | yes | |
| STFNAME3 | nvarchar(154) | yes | |
| STFST3 | varchar(8) | yes | |
| STFET3 | varchar(8) | yes | |
| STFIN3 | nvarchar(154) | yes | |
| STFII3 | nvarchar(100) | yes | |
| STFOPEN3 | int | yes | |
| STFPRICE3 | money | yes | |
| STFTYPE4 | nvarchar(100) | yes | |
| STFNAME4 | nvarchar(154) | yes | |
| STFST4 | varchar(8) | yes | |
| STFET4 | varchar(8) | yes | |
| STFIN4 | nvarchar(154) | yes | |
| STFII4 | nvarchar(100) | yes | |
| STFOPEN4 | int | yes | |
| STFPRICE4 | money | yes | |
| STFTYPEREST | varchar(23) | yes | |
| ITINNAME1 | nvarchar(90) | yes | |
| ITINLDR1 | nvarchar(154) | yes | |
| ITINGT1 | nvarchar(100) | yes | |
| ITINST1 | varchar(8) | yes | |
| ITINET1 | varchar(8) | yes | |
| ITINNAME2 | nvarchar(90) | yes | |
| ITINLDR2 | nvarchar(154) | yes | |
| ITINGT2 | nvarchar(100) | yes | |
| ITINST2 | varchar(8) | yes | |
| ITINET2 | varchar(8) | yes | |
| ITINNAME3 | nvarchar(90) | yes | |
| ITINLDR3 | nvarchar(154) | yes | |
| ITINGT3 | nvarchar(100) | yes | |
| ITINST3 | varchar(8) | yes | |
| ITINET3 | varchar(8) | yes | |
| ITINNAME4 | nvarchar(90) | yes | |
| ITINLDR4 | nvarchar(154) | yes | |
| ITINGT4 | nvarchar(100) | yes | |
| ITINST4 | varchar(8) | yes | |
| ITINET4 | varchar(8) | yes | |
| ITINNAMEREST | varchar(19) | yes | |
| RESERVATIONNAME | nvarchar(100) | ||
| ORDERID | int | yes | |
| CONTRACTCREATEDDATE | varchar(30) | yes | |
| IMPORTANTNOTES | nvarchar(1000) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:08:50 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_GROUPSALESCONTRACTOUTPUT AS
select
RESERVATION.ID,
PATRONNAMEFORMAT.NAME as [PATRON_NAME],
PATRONADDRESS.ADDRESSBLOCK as [PATRON_ADDRESS],
PATRONADDRESS.CITY as [PATRON_CITY],
dbo.UFN_STATE_GETABBREVIATION(PATRONADDRESS.STATEID) as [PATRON_STATE],
PATRONADDRESS.POSTCODE as [PATRON_ZIP],
CONTACTNAMEFORMAT.NAME as [CONTACT_NAME],
CONTACTADDRESS.ADDRESSBLOCK as [CONTACT_ADDRESS],
CONTACTADDRESS.CITY as [CONTACT_CITY],
dbo.UFN_STATE_GETABBREVIATION(CONTACTADDRESS.STATEID) as [CONTACT_STATE],
CONTACTADDRESS.POSTCODE as [CONTACT_ZIP],
CONTACTPHONE.NUMBER as [CONTACT_PHONE],
CONTACTEMAILADDRESS.EMAILADDRESS as [CONTACT_EMAIL],
TOTALS.TOTAL as [ORDER_TOTAL],
(TOTALS.TOTAL - RESERVATION.DEPOSITAMOUNT) as [BALANCE_AFTER_DEPOSIT],
TOTALS.BALANCE as [ORDER_BALANCE],
RESERVATION.DEPOSITAMOUNT as [DEPOSIT_AMOUNT],
RESERVATION.DEPOSITDUEDATE as [DEPOSIT_DUE_DATE],
RESERVATION.FINALCOUNTDUEDATE as [FINAL_COUNT_DUE_DATE],
RESERVATION.FINALDUEDATE as [ORDER_BALANCE_DUE_DATE],
RESERVATION.ARRIVALDATE as [RESERVATION_DATE],
RESERVATION.ARRIVALTIME as [ARRIVAL_TIME],
RESERVATION.NUMBEROFBUSES as [NUMBER_OF_BUSES],
dbo.UFN_ARRIVALAREACODE_GETDESCRIPTION(RESERVATION.ARRIVALAREACODEID) as [ARRIVAL_AREA],
dbo.UFN_RESERVATION_TOTALVISITORCOUNT(RESERVATION.ID) as [NUMBER_OF_VISITORS],
dbo.UFN_RESERVATION_GETITINERARIESSTRING(RESERVATION.ID) as [ITINERARY_BLOCK],
dbo.UFN_RESERVATION_GETRESOURCESSTRING(RESERVATION.ID) as [RESOURCE_BLOCK],
ORGANIZATIONINFORMATION.NAME as [ORGANIZATION_NAME],
ORGANIZATIONINFORMATION.ADDRESSBLOCK as [ORGANIZATION_ADDRESS],
ORGANIZATIONINFORMATION.CITY as [ORGANIZATION_CITY],
dbo.UFN_STATE_GETABBREVIATION(ORGANIZATIONINFORMATION.STATEID) as [ORGANIZATION_STATE],
ORGANIZATIONINFORMATION.POSTCODE as [ORGANIZATION_ZIP],
ORGANIZATIONINFORMATION.PHONENUMBER as [ORGANIZATION_PHONE],
ORGANIZATIONINFORMATION.WEBADDRESS as [ORGANIZATION_WEB],
dbo.UFN_RESERVATION_GETFACILITIESSTRING(RESERVATION.ID) as [FACILITY_BLOCK],
[RESERVATION].[SECURITYDEPOSITAMOUNT] as [SECURITY_DEPOSIT_AMOUNT],
[RESERVATION].[SECURITYDEPOSITDUEDATE] as [SECURITY_DEPOSIT_DUE_DATE],
dbo.UFN_RESERVATION_GETSECURITYDEPOSITAMOUNTDUE([RESERVATION].[ID]) as [SECURITY_DEPOSIT_BALANCE],
SALESORDERTICKETDATA.TKTDESC1,
SALESORDERTICKETDATA.TKTSTARTDT1,
SALESORDERTICKETDATA.TKTSTARTTM1,
SALESORDERTICKETDATA.TKTQTY1,
SALESORDERTICKETDATA.TKTPRICE1,
SALESORDERTICKETDATA.TKTDSC1,
SALESORDERTICKETDATA.TKTFEE1,
SALESORDERTICKETDATA.TKTTOTAL1,
SALESORDERTICKETDATA.TKTDESC2,
SALESORDERTICKETDATA.TKTSTARTDT2,
SALESORDERTICKETDATA.TKTSTARTTM2,
SALESORDERTICKETDATA.TKTQTY2,
SALESORDERTICKETDATA.TKTPRICE2,
SALESORDERTICKETDATA.TKTDSC2,
SALESORDERTICKETDATA.TKTFEE2,
SALESORDERTICKETDATA.TKTTOTAL2,
SALESORDERTICKETDATA.TKTDESC3,
SALESORDERTICKETDATA.TKTSTARTDT3,
SALESORDERTICKETDATA.TKTSTARTTM3,
SALESORDERTICKETDATA.TKTQTY3,
SALESORDERTICKETDATA.TKTPRICE3,
SALESORDERTICKETDATA.TKTDSC3,
SALESORDERTICKETDATA.TKTFEE3,
SALESORDERTICKETDATA.TKTTOTAL3,
SALESORDERTICKETDATA.TKTDESC4,
SALESORDERTICKETDATA.TKTSTARTDT4,
SALESORDERTICKETDATA.TKTSTARTTM4,
SALESORDERTICKETDATA.TKTQTY4,
SALESORDERTICKETDATA.TKTPRICE4,
SALESORDERTICKETDATA.TKTDSC4,
SALESORDERTICKETDATA.TKTFEE4,
SALESORDERTICKETDATA.TKTTOTAL4,
SALESORDERTICKETDATA.TKTDESCREST,
SALESORDERTICKETDATA.TKTQTYREST,
SALESORDERTICKETDATA.TKTPRICEREST,
SALESORDERTICKETDATA.TKTDSCREST,
SALESORDERTICKETDATA.TKTFEEREST,
SALESORDERTICKETDATA.TKTTOTALREST,
SALESORDERRESOURCEDATA.RSCDESC1,
SALESORDERRESOURCEDATA.RSCSTDT1,
SALESORDERRESOURCEDATA.RSCENDDT1,
SALESORDERRESOURCEDATA.RSCQTY1,
SALESORDERRESOURCEDATA.RSCPRICE1,
SALESORDERRESOURCEDATA.RSCTOTAL1,
SALESORDERRESOURCEDATA.RSCDESC2,
SALESORDERRESOURCEDATA.RSCSTDT2,
SALESORDERRESOURCEDATA.RSCENDDT2,
SALESORDERRESOURCEDATA.RSCQTY2,
SALESORDERRESOURCEDATA.RSCPRICE2,
SALESORDERRESOURCEDATA.RSCTOTAL2,
SALESORDERRESOURCEDATA.RSCDESC3,
SALESORDERRESOURCEDATA.RSCSTDT3,
SALESORDERRESOURCEDATA.RSCENDDT3,
SALESORDERRESOURCEDATA.RSCQTY3,
SALESORDERRESOURCEDATA.RSCPRICE3,
SALESORDERRESOURCEDATA.RSCTOTAL3,
SALESORDERRESOURCEDATA.RSCDESC4,
SALESORDERRESOURCEDATA.RSCSTDT4,
SALESORDERRESOURCEDATA.RSCENDDT4,
SALESORDERRESOURCEDATA.RSCQTY4,
SALESORDERRESOURCEDATA.RSCPRICE4,
SALESORDERRESOURCEDATA.RSCTOTAL4,
SALESORDERRESOURCEDATA.RSCDESCREST,
SALESORDERRESOURCEDATA.RSCTOTALREST,
SALESORDERSTAFFRESOURCEDATA.STFTYPE1,
SALESORDERSTAFFRESOURCEDATA.STFNAME1,
SALESORDERSTAFFRESOURCEDATA.STFST1,
SALESORDERSTAFFRESOURCEDATA.STFET1,
SALESORDERSTAFFRESOURCEDATA.STFIN1,
SALESORDERSTAFFRESOURCEDATA.STFII1,
SALESORDERSTAFFRESOURCEDATA.STFOPEN1,
SALESORDERSTAFFRESOURCEDATA.STFPRICE1,
SALESORDERSTAFFRESOURCEDATA.STFTYPE2,
SALESORDERSTAFFRESOURCEDATA.STFNAME2,
SALESORDERSTAFFRESOURCEDATA.STFST2,
SALESORDERSTAFFRESOURCEDATA.STFET2,
SALESORDERSTAFFRESOURCEDATA.STFIN2,
SALESORDERSTAFFRESOURCEDATA.STFII2,
SALESORDERSTAFFRESOURCEDATA.STFOPEN2,
SALESORDERSTAFFRESOURCEDATA.STFPRICE2,
SALESORDERSTAFFRESOURCEDATA.STFTYPE3,
SALESORDERSTAFFRESOURCEDATA.STFNAME3,
SALESORDERSTAFFRESOURCEDATA.STFST3,
SALESORDERSTAFFRESOURCEDATA.STFET3,
SALESORDERSTAFFRESOURCEDATA.STFIN3,
SALESORDERSTAFFRESOURCEDATA.STFII3,
SALESORDERSTAFFRESOURCEDATA.STFOPEN3,
SALESORDERSTAFFRESOURCEDATA.STFPRICE3,
SALESORDERSTAFFRESOURCEDATA.STFTYPE4,
SALESORDERSTAFFRESOURCEDATA.STFNAME4,
SALESORDERSTAFFRESOURCEDATA.STFST4,
SALESORDERSTAFFRESOURCEDATA.STFET4,
SALESORDERSTAFFRESOURCEDATA.STFIN4,
SALESORDERSTAFFRESOURCEDATA.STFII4,
SALESORDERSTAFFRESOURCEDATA.STFOPEN4,
SALESORDERSTAFFRESOURCEDATA.STFPRICE4,
SALESORDERSTAFFRESOURCEDATA.STFTYPEREST,
SALESORDERITINERARYDATA.ITINNAME1,
SALESORDERITINERARYDATA.ITINLDR1,
SALESORDERITINERARYDATA.ITINGT1,
SALESORDERITINERARYDATA.ITINST1,
SALESORDERITINERARYDATA.ITINET1,
SALESORDERITINERARYDATA.ITINNAME2,
SALESORDERITINERARYDATA.ITINLDR2,
SALESORDERITINERARYDATA.ITINGT2,
SALESORDERITINERARYDATA.ITINST2,
SALESORDERITINERARYDATA.ITINET2,
SALESORDERITINERARYDATA.ITINNAME3,
SALESORDERITINERARYDATA.ITINLDR3,
SALESORDERITINERARYDATA.ITINGT3,
SALESORDERITINERARYDATA.ITINST3,
SALESORDERITINERARYDATA.ITINET3,
SALESORDERITINERARYDATA.ITINNAME4,
SALESORDERITINERARYDATA.ITINLDR4,
SALESORDERITINERARYDATA.ITINGT4,
SALESORDERITINERARYDATA.ITINST4,
SALESORDERITINERARYDATA.ITINET4,
SALESORDERITINERARYDATA.ITINNAMEREST,
RESERVATION.NAME as RESERVATIONNAME,
SALESORDER.SEQUENCEID as ORDERID,
convert(varchar,getdate(),101) as CONTRACTCREATEDDATE,
SALESORDER.COMMENTS as IMPORTANTNOTES
from
dbo.RESERVATION
left outer join dbo.SALESORDER
on RESERVATION.ID = SALESORDER.ID
left outer join dbo.ADDRESS as PATRONADDRESS
on SALESORDER.CONSTITUENTID = PATRONADDRESS.CONSTITUENTID and PATRONADDRESS.ISPRIMARY = 1
outer apply
dbo.UFN_SALESORDER_CONTACTRECORDS(RESERVATION.ID) as CONTACTRECORDS
left outer join dbo.ADDRESS as CONTACTADDRESS
on CONTACTRECORDS.ADDRESSID = CONTACTADDRESS.ID
left outer join dbo.PHONE as CONTACTPHONE
on CONTACTRECORDS.PHONEID = CONTACTPHONE.ID
left outer join dbo.EMAILADDRESS as CONTACTEMAILADDRESS
on CONTACTRECORDS.EMAILADDRESSID = CONTACTEMAILADDRESS.ID
outer apply (
select top 1
NAME,
ADDRESSBLOCK,
CITY,
STATEID,
POSTCODE,
PHONENUMBER,
WEBADDRESS
from
dbo.ORGANIZATIONINFORMATION
) as ORGANIZATIONINFORMATION
outer apply
dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.CONSTITUENTID) as PATRONNAMEFORMAT
outer apply
dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.RECIPIENTID) as CONTACTNAMEFORMAT
outer apply
dbo.UFN_SALESORDER_TOTALS(RESERVATION.ID) as TOTALS
outer apply
dbo.UFN_SALESORDER_TICKETS_FLAT(SALESORDER.ID) as SALESORDERTICKETDATA
outer apply
dbo.UFN_SALESORDER_RESERVATION_RESOURCES_FLAT(SALESORDER.ID) as SALESORDERRESOURCEDATA
outer apply
dbo.UFN_SALESORDER_RESERVATIONSTAFFASSIGNMENT_FLAT(SALESORDER.ID) as SALESORDERSTAFFRESOURCEDATA
outer apply
dbo.UFN_SALESORDER_ITINERARY_FLAT(SALESORDER.ID) as SALESORDERITINERARYDATA;