UFN_QUERY_PLEDGEREMINDEROUTPUTDETAILS2

CREATE function [dbo].[UFN_QUERY_PLEDGEREMINDEROUTPUTDETAILS2]
(
  @REVENUEID uniqueidentifier,
  @NAMEFORMATPARAMETERID uniqueidentifier,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
  @PARAMETERSETID uniqueidentifier = null,
  @SEASONALDATE datetime = null,
  @INDUSESEASONALADDRESS bit,
  @ORGMAILINGPREFERENCE tinyint,
  @INDALTADDRESS1TYPECODEID uniqueidentifier,
  @INDALTADDRESS1ISPRIMARY bit,
  @INDALTADDRESS2TYPECODEID uniqueidentifier,
  @INDALTADDRESS2ISPRIMARY bit,
  @ORGALTADDRESS1TYPECODEID uniqueidentifier,
  @ORGALTADDRESS1ISPRIMARY bit,
  @ORGALTADDRESS2TYPECODEID uniqueidentifier,
  @ORGALTADDRESS2ISPRIMARY bit,
  @INDINCLUDEWITHNOADDRESS bit,
  @ORGINCLUDEWITHNOADDRESS bit,
  @ORGINCLUDEWITHNOCONTACT bit,
  @ORGSENDTOALLCONTACTS bit,
  @INDUSECONSTITUENTPREFS bit,
  @ORGUSECONSTITUENTPREFS bit,
  @GROUPALTADDRESS1TYPECODEID uniqueidentifier,
  @GROUPALTADDRESS1ISPRIMARY bit,
  @GROUPALTADDRESS2TYPECODEID uniqueidentifier,
  @GROUPALTADDRESS2ISPRIMARY bit,
  @GROUPINCLUDEWITHNOADDRESS bit,
  @GROUPUSECONSTITUENTPREFS bit,
  @INDIVIDUALADDRESSEES xml,
  @ADDRESSEEFUNCTIONID uniqueidentifier,
  @INDIVIDUALSALUTATIONS xml,
  @SALUTATIONFUNCTIONID uniqueidentifier,
  @ORGADDRESSEES xml,
  @CONTACTADDRESSEEFUNCTIONID uniqueidentifier,
  @ORGSALUTATIONCODE tinyint,
  @ORGSALUTATIONS xml,
  @CONTACTSALUTATIONFUNCTIONID uniqueidentifier,
  @CONTACTSALUTATIONOPTIONCODE tinyint,
  @CUSTOMNAME nvarchar(100),
  @GROUPADDRESSEES xml,
  @GROUPADDRESSEEFUNCTIONID uniqueidentifier,
  @GROUPSALUTATIONCODE tinyint,
  @GROUPSALUTATIONS xml,
  @GROUPSALUTATIONFUNCTIONID uniqueidentifier,
  @GROUPNOCONTACTOPTIONCODE bit,
  @GROUPNOCONTACTCUSTOMNAME nvarchar(100),
  @JOINTRULETYPECODE As tinyint,
  @JOINTSELECTIONID As uniqueidentifier,
  @JOINTSELECTIONBOTHRULETYPECODE As tinyint,
  @JOINTSELECTIONNEITHERRULETYPECODE As tinyint
)
returns table
as
return
(
  with [DESIGNATIONS_TOP2] ([NAME], [PUBLICNAME], [AMOUNT], [ROWNUM]) as
  (
    select top 2
      DESIGNATIONLEVEL.NAME,
      isnull(nullif(DESIGNATION.VANITYNAME,''), DESIGNATIONLEVEL.NAME) as PUBLICNAME,
      FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT as AMOUNT,
      row_number() over (order by BASEAMOUNT desc) as ROWNUM
    from dbo.FINANCIALTRANSACTIONLINEITEM
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
    left join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
    left join dbo.DESIGNATIONLEVEL on coalesce(DESIGNATION.DESIGNATIONLEVEL5ID,DESIGNATION.DESIGNATIONLEVEL4ID,DESIGNATION.DESIGNATIONLEVEL3ID,DESIGNATION.DESIGNATIONLEVEL2ID,DESIGNATION.DESIGNATIONLEVEL1ID) = DESIGNATIONLEVEL.ID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
    order by ROWNUM
  ),
  [DESIGNATION1] ([NAME], [PUBLICNAME], [AMOUNT]) as
  (
    select
      NAME,
      PUBLICNAME,
      AMOUNT
    from [DESIGNATIONS_TOP2]
    where [ROWNUM] = 1
  ),
  [DESIGNATION2] ([NAME], [PUBLICNAME], [AMOUNT]) as
  (
    select
      NAME,
      PUBLICNAME,
      AMOUNT
    from [DESIGNATIONS_TOP2]
    where [ROWNUM] = 2
  )
  select
    CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
    FINANCIALTRANSACTION.ID as REVENUEID,
    FINANCIALTRANSACTION.TRANSACTIONAMOUNT as TOTALPLEDGEAMOUNT,
    case REVENUE_EXT.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,
    cast(FINANCIALTRANSACTION.DATE as datetime) as PLEDGEDATE,
    null as PLEDGEBALANCE,
    coalesce((select sum(AMOUNT) from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = FINANCIALTRANSACTION.ID and TYPECODE = 0), 0) as AMOUNTPAID,
    APPEAL.NAME as APPEALNAME,
    [DESIGNATION1].[NAME] as [DESIGNATION1NAME],
    [DESIGNATION1].[PUBLICNAME] as [DESIGNATION1PUBLICNAME],
    [DESIGNATION1].[AMOUNT] as [DESIGNATION1AMOUNT],
    [DESIGNATION2].[NAME] as [DESIGNATION2NAME],
    [DESIGNATION2].[PUBLICNAME] as [DESIGNATION2PUBLICNAME],
    [DESIGNATION2].[AMOUNT] as [DESIGNATION2AMOUNT],
    FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as REVENUELOOKUPID,
    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    CURRENCY.ISO4217 as TRANSACTIONCURRENCY
  from dbo.FINANCIALTRANSACTION
  cross apply [DESIGNATION1]
  outer apply [DESIGNATION2]
  inner join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  left join dbo.APPEAL on REVENUE_EXT.APPEALID = APPEAL.ID 
  left join dbo.CURRENCY on FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = CURRENCY.ID
  where FINANCIALTRANSACTION.TYPECODE = 2
  and FINANCIALTRANSACTION.ID = @REVENUEID

  union all

  select
    CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
    FINANCIALTRANSACTION.ID as REVENUEID,
    FINANCIALTRANSACTION.TRANSACTIONAMOUNT as TOTALPLEDGEAMOUNT,
    case REVENUE_EXT.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,
    cast(FINANCIALTRANSACTION.DATE as datetime) as PLEDGEDATE,
    --Inline the scalar function...

    --dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID) as PLEDGEBALANCE,

    case when FINANCIALTRANSACTION.TYPECODE = 7 then --Auction donations do not have installments

      FINANCIALTRANSACTION.TRANSACTIONAMOUNT - 
      isnull((select sum(FTLI.TRANSACTIONAMOUNT) 
              from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI
              inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
              where FT.PARENTID = @REVENUEID
              and FT.TYPECODE = 20 and FTLI.DELETEDON is null), 0)
    else
      FINANCIALTRANSACTION.TRANSACTIONAMOUNT - 
      (isnull((select 
                 sum(INSTALLMENTSPLITPAYMENT.AMOUNT) 
               from dbo.INSTALLMENTSPLITPAYMENT 
               where INSTALLMENTSPLITPAYMENT.PLEDGEID = @REVENUEID), 0) + 
      isnull((select
                sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) 
              from dbo.INSTALLMENTSPLITWRITEOFF 
              inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
              where INSTALLMENTSPLIT.PLEDGEID = @REVENUEID), 0))
    end as PLEDGEBALANCE,
    --Inline the scalar function...

    --dbo.UFN_PLEDGE_GETAMOUNTPAID(FINANCIALTRANSACTION.ID) as AMOUNTPAID,

    isnull((select
              sum (INSTALLMENTPAYMENT.AMOUNT)
            from dbo.INSTALLMENTPAYMENT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTPAYMENT.PAYMENTID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            where INSTALLMENTPAYMENT.PLEDGEID = @REVENUEID
            and FINANCIALTRANSACTION.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1), 
          0) as AMOUNTPAID,
    APPEAL.NAME as APPEALNAME,
    [DESIGNATION1].[NAME] as [DESIGNATION1NAME],
    [DESIGNATION1].[PUBLICNAME] as [DESIGNATION1PUBLICNAME],
    [DESIGNATION1].[AMOUNT] as [DESIGNATION1AMOUNT],
    [DESIGNATION2].[NAME] as [DESIGNATION2NAME],
    [DESIGNATION2].[PUBLICNAME] as [DESIGNATION2PUBLICNAME],
    [DESIGNATION2].[AMOUNT] as [DESIGNATION2AMOUNT],
    FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as REVENUELOOKUPID,
    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    CURRENCY.ISO4217 as TRANSACTIONCURRENCY
  from dbo.FINANCIALTRANSACTION
  cross apply [DESIGNATION1]
  outer apply [DESIGNATION2]
  inner join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  left join dbo.APPEAL on REVENUE_EXT.APPEALID = APPEAL.ID 
  left join dbo.CURRENCY on FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = CURRENCY.ID
  where FINANCIALTRANSACTION.TYPECODE <> 2
  and FINANCIALTRANSACTION.ID = @REVENUEID
)