V_SEARCH_CREDIT_REFUND

Search for refunds by patron, date, or program.
Fields

Field	Field Type	Null
ID	uniqueidentifier
TRANSACTIONDATE	date	yes
AMOUNT	money
PATRONNAME	nvarchar(154)	yes
USERNAME	nvarchar(128)	yes
ITEMS	nvarchar(max)	yes
Definition

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



select 
    FT.ID, 
    FT.CALCULATEDDATE as TRANSACTIONDATE, 
    FT.TRANSACTIONAMOUNT as AMOUNT, 
    NF.NAME as PATRONNAME,
    dbo.UFN_APPUSER_GETNAME(FT.APPUSERID) as USERNAME,
    (
        select
            dbo.UDA_BUILDLIST(SALESORDERITEM.DESCRIPTION) 
        from (
            select distinct
                COMBINATION.NAME + ' - ' + PRICETYPECODE.DESCRIPTION as DESCRIPTION
            from
                dbo.FINANCIALTRANSACTIONLINEITEM as LI
            inner join
                dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
            inner join
                dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = EXT.SALESORDERITEMID
            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
                LI.FINANCIALTRANSACTIONID = FT.ID

            union all
            select
                SALESORDERITEM.DESCRIPTION 
            from
                dbo.FINANCIALTRANSACTIONLINEITEM as LI
            inner join
                dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
            inner join
                dbo.SALESORDERITEM on EXT.SALESORDERITEMID = SALESORDERITEM.ID
            left join
                dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKETCOMBINATION.ID = EXT.SALESORDERITEMID 
            where
                LI.FINANCIALTRANSACTIONID = FT.ID
                and SALESORDERITEMTICKETCOMBINATION.ID is null
        ) as SALESORDERITEM
    ) as ITEMS
from
    dbo.FINANCIALTRANSACTION as FT 
outer apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(FT.CONSTITUENTID) as NF
where
    FT.TYPECODE = 23  -- Refund