V_SEARCH_SALESORDER

Search for orders by patron, date, order number, or program.
Fields

Field	Field Type	Null
ID	uniqueidentifier
RECIPIENTNAME	nvarchar(154)	yes
PATRONNAME	nvarchar(154)	yes
TRANSACTIONDATE	datetime	yes
SEQUENCEID	nvarchar(20)	yes
AMOUNT	money	yes
AMOUNTDUE	money	yes
ITEMS	nvarchar(max)	yes
Definition

Copy
/*
Generated by Blackbaud AppFx Platform
Date:  11/11/2014 4:18:34 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_SEARCH_SALESORDER AS



            select 
                distinct [SALESORDER].[ID], 
                dbo.UFN_CONSTITUENT_BUILDNAME([SALESORDER].[RECIPIENTID]) as [RECIPIENTNAME],
                dbo.UFN_CONSTITUENT_BUILDNAME([SALESORDER].[CONSTITUENTID]) as [PATRONNAME],
                [SALESORDER].[TRANSACTIONDATE], 
                convert(nvarchar(20), [SALESORDER].[SEQUENCEID]) as [SEQUENCEID], 
                [SALESORDER].[AMOUNT],
                [SALESORDER].[AMOUNT] AMOUNTDUE,
                (
                    select dbo.UDA_BUILDLIST(SALESORDERITEM.DESCRIPTION) 
                    from (select distinct [COMBINATION].[NAME] + ' - ' + [PRICETYPECODE].[DESCRIPTION] as [DESCRIPTION]
                            from dbo.[SALESORDERITEM] 
                  inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID] 
                  inner join dbo.[TICKET] on [SALESORDERITEMTICKET].ID = [TICKET].SALESORDERITEMTICKETID and [TICKET].STATUSCODE not in (2, 3)
                  inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
                  inner join dbo.[COMBINATION] on [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
                  inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
                where [SALESORDERID] = [SALESORDER].[ID]

                union all

                select SALESORDERITEM.DESCRIPTION 
                from dbo.[SALESORDERITEM] 
                  left join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
                  left join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
                          where [SALESORDERID] = [SALESORDER].[ID]
                            and [SALESORDERITEMTICKETCOMBINATION].ID is null
                        ) as SALESORDERITEM
                ) as [ITEMS]
            from dbo.[SALESORDER]             
            where [SALESORDER].[STATUSCODE] not in (0,7)