USP_DATALIST_SPONSORSHIPUNRESOLVEDTRANSACTIONS

CREATE procedure dbo.USP_DATALIST_SPONSORSHIPUNRESOLVEDTRANSACTIONS
as
    set nocount on;

    select 
        [SALESORDERITEM].[ID],
        [SALESORDERITEM].[SALESORDERID] as [SID],
        null as SPONSOR ,
        case when GIFTRECIPIENTID is not null then dbo.UFN_CONSTITUENT_BUILDNAME(GIFTRECIPIENTID) else '' end as GIFTSPONSOR,
        [SALESORDERITEM].[SPONSORSHIPTYPE],
        [SALESORDERITEM].[PRICE] as AMOUNT,
        (select NAME from dbo.SPONSORSHIPPROGRAM SPR WHERE SPR.ID = SPONSORSHIPPROGRAMID) PROGRAM,
        case when exists (select 'x' from dbo.SPONSORSHIPOPPORTUNITYCHILD where ID = SPONSORSHIPOPPORTUNITYID) then 
            dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIPOPPORTUNITYID) 
        else
            ''
        end as CHILD,
        case when exists (select 'x' from dbo.SPONSORSHIPOPPORTUNITYPROJECT where ID = SPONSORSHIPOPPORTUNITYID) then 
          dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIPOPPORTUNITYID) 
        else
          ''
        end as PROJECT,
        case when ORDERTYPE = 0 then 
          SALESORDERITEMSPONSORSHIP.STARTDATE 
        else
          SALESORDERITEM.DATE
        end as DATE,
        SEQUENCEID,
        0 as ISVALIDRECORDOPERATION,
        SALESORDERITEM.TRANSACTIONCURRENCYID,
        0 as CONSTITUENTISMISSING
    from dbo.[SALESORDER]
    inner join (
            select 
              SALESORDERITEM.ID,
              SALESORDERITEM.SALESORDERID,
              SALESORDERITEM.PRICE,
              case when SALESORDERITEM.TYPE='Donation' then 'Sponsorship additional donation' else 'Sponsorship' end as SPONSORSHIPTYPE,
              case when SALESORDERITEM.TYPE='Donation' then 2 else 0 end as ORDERTYPE,
              SALESORDERITEM.DATEADDED as DATE,
              REVENUE.TRANSACTIONCURRENCYID
            from  dbo.SALESORDERITEM 
            inner join dbo.SALESORDERITEMSPONSORSHIP on SALESORDERITEMSPONSORSHIP.ID = SALESORDERITEM.ID
            left join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.SALESORDERID = SALESORDERITEM.SALESORDERID
            left join dbo.FINANCIALTRANSACTION REVENUE on SALESORDERPAYMENT.PAYMENTID = REVENUE.ID
            where SALESORDERITEM.CATEGORYNAME ='Sponsorship'
            union all
            select 
              SALESORDERITEM.ID,
              SALESORDERITEM.SALESORDERID,
              SALESORDERITEM.PRICE,
              'Sponsorship payment' as SPONSORSHIPTYPE,
              '1' ORDERTYPE,
              SALESORDERPAYMENT.PAYMENTDATEWITHTIMEOFFSET as DATE,
              REVENUE.TRANSACTIONCURRENCYID
            from 
            dbo.SALESORDERITEM 
            inner join dbo.SALESORDERITEMSPONSORSHIP on SALESORDERITEMSPONSORSHIP.ID = SALESORDERITEM.ID
            inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.SALESORDERID = SALESORDERITEM.SALESORDERID
            inner join dbo.FINANCIALTRANSACTION REVENUE on SALESORDERPAYMENT.PAYMENTID = REVENUE.ID
        ) as SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID
    left join dbo.SALESORDERITEMSPONSORSHIP on SALESORDERITEMSPONSORSHIP.ID = SALESORDERITEM.ID  
    where 
        [SALESORDER].[STATUSCODE] = 7 --order by SALESORDER.DATEADDED desc, SALESORDERITEM.ORDERTYPE

    union 
        select 
        [SALESORDER].[ID], 
        null as [SID],
        dbo.UFN_CONSTITUENT_BUILDNAME(SALESORDER.CONSTITUENTID) as SPONSOR,
        null as GIFTSPONSOR,
        null as SPONSORSHIPTYPE,
        SALESORDER.AMOUNT as AMOUNT,
        null as PROGRAM,
        null as CHILD,
        null as PROJECT,
        TRANSACTIONDATE as DATE,
        SALESORDER.SEQUENCEID,
        1 as ISVALIDRECORDOPERATION,
        REVENUE.TRANSACTIONCURRENCYID,
        case when SALESORDER.CONSTITUENTID is null then 1 else 0 end as CONSTITUENTISMISSING
    from dbo.[SALESORDER]
    inner join dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID
    inner join dbo.SALESORDERITEMSPONSORSHIP on SALESORDERITEMSPONSORSHIP.ID = SALESORDERITEM.ID
    left join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.SALESORDERID = SALESORDERITEM.SALESORDERID
    left join dbo.FINANCIALTRANSACTION REVENUE on SALESORDERPAYMENT.PAYMENTID = REVENUE.ID
    where  [SALESORDER].[STATUSCODE] = 7 
    order by SEQUENCEID desc