UFN_QUERY_PLEDGEREMINDEROUTPUT

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@NAMEFORMATPARAMETERID	uniqueidentifier	IN
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	IN
@PARAMETERSETID	uniqueidentifier	IN
@SEASONALDATE	datetime	IN
@INDUSESEASONALADDRESS	bit	IN
@ORGMAILINGPREFERENCE	tinyint	IN
@INDALTADDRESS1TYPECODEID	uniqueidentifier	IN
@INDALTADDRESS1ISPRIMARY	bit	IN
@INDALTADDRESS2TYPECODEID	uniqueidentifier	IN
@INDALTADDRESS2ISPRIMARY	bit	IN
@ORGALTADDRESS1TYPECODEID	uniqueidentifier	IN
@ORGALTADDRESS1ISPRIMARY	bit	IN
@ORGALTADDRESS2TYPECODEID	uniqueidentifier	IN
@ORGALTADDRESS2ISPRIMARY	bit	IN
@INDINCLUDEWITHNOADDRESS	bit	IN
@ORGINCLUDEWITHNOADDRESS	bit	IN
@ORGINCLUDEWITHNOCONTACT	bit	IN
@ORGSENDTOALLCONTACTS	bit	IN
@INDUSECONSTITUENTPREFS	bit	IN
@ORGUSECONSTITUENTPREFS	bit	IN
@GROUPALTADDRESS1TYPECODEID	uniqueidentifier	IN
@GROUPALTADDRESS1ISPRIMARY	bit	IN
@GROUPALTADDRESS2TYPECODEID	uniqueidentifier	IN
@GROUPALTADDRESS2ISPRIMARY	bit	IN
@GROUPINCLUDEWITHNOADDRESS	bit	IN
@GROUPUSECONSTITUENTPREFS	bit	IN
@INDIVIDUALADDRESSEES	xml	IN
@ADDRESSEEFUNCTIONID	uniqueidentifier	IN
@INDIVIDUALSALUTATIONS	xml	IN
@SALUTATIONFUNCTIONID	uniqueidentifier	IN
@ORGADDRESSEES	xml	IN
@CONTACTADDRESSEEFUNCTIONID	uniqueidentifier	IN
@ORGSALUTATIONCODE	tinyint	IN
@ORGSALUTATIONS	xml	IN
@CONTACTSALUTATIONFUNCTIONID	uniqueidentifier	IN
@CONTACTSALUTATIONOPTIONCODE	tinyint	IN
@CUSTOMNAME	nvarchar(100)	IN
@GROUPADDRESSEES	xml	IN
@GROUPADDRESSEEFUNCTIONID	uniqueidentifier	IN
@GROUPSALUTATIONCODE	tinyint	IN
@GROUPSALUTATIONS	xml	IN
@GROUPSALUTATIONFUNCTIONID	uniqueidentifier	IN
@GROUPNOCONTACTOPTIONCODE	bit	IN
@GROUPNOCONTACTCUSTOMNAME	nvarchar(100)	IN
@JOINTRULETYPECODE	tinyint	IN
@JOINTSELECTIONID	uniqueidentifier	IN
@JOINTSELECTIONBOTHRULETYPECODE	tinyint	IN
@JOINTSELECTIONNEITHERRULETYPECODE	tinyint	IN

Definition

Copy



CREATE function [dbo].[UFN_QUERY_PLEDGEREMINDEROUTPUT]
(
  @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 [REV] as
  (
    select 
      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,    
      isnull((select sum(AMOUNT) from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = FINANCIALTRANSACTION.ID and TYPECODE = 0), 0) as AMOUNTPAID,  
      FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as REVENUELOOKUPID,
      FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
      FINANCIALTRANSACTION.CONSTITUENTID,
      REVENUE_EXT.APPEALID
    from dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    where FINANCIALTRANSACTION.TYPECODE = 2
    and FINANCIALTRANSACTION.DELETEDON is null

    union all

    select 
      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 = FINANCIALTRANSACTION.ID
                and FT.TYPECODE = 20 and FTLI.DELETEDON is null
               ), 0)
      else
        FINANCIALTRANSACTION.TRANSACTIONAMOUNT - 
        (isnull((select 
                   sum(INSTALLMENTSPLITPAYMENT.AMOUNT) 
                 from dbo.INSTALLMENTSPLITPAYMENT 
                 where INSTALLMENTSPLITPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID), 0) + 
        isnull((select
                  sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) 
                from dbo.INSTALLMENTSPLITWRITEOFF 
                inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
                where INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTION.ID), 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 FTLI on FTLI.ID = INSTALLMENTPAYMENT.PAYMENTID
           inner join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
           inner join dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID
           inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
           where
              INSTALLMENTPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID
              and FT.DELETEDON is null
              and FTLI.DELETEDON is null
              and FTLI.TYPECODE <> 1), 
          0) as AMOUNTPAID,
      FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as REVENUELOOKUPID,
      FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
      FINANCIALTRANSACTION.CONSTITUENTID,
      REVENUE_EXT.APPEALID
    from dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    where FINANCIALTRANSACTION.TYPECODE = 1
    and FINANCIALTRANSACTION.DELETEDON is null
  )
  select 
    REV.REVENUEID,
    CONSTITUENT.NAME as CONSTITUENTNAME,
    CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
    A.ADDRESSEE,    
    A.SALUTATION,
    A.CONTACT as CONTACTADDRESSEE,
    A.POSITION,
    A.ADDRESSBLOCK,
    A.CITY,
    A.STATE,
    A.POSTCODE,
    A.COUNTRY,
    REV.TOTALPLEDGEAMOUNT,
    REV.GIVENANONYMOUSLY,
    REV.PLEDGEDATE,
    REV.PLEDGEBALANCE,
    REV.AMOUNTPAID,
    APPEAL.NAME as APPEALNAME,
    [DESIGNATION1].[DESIGNATIONNAME] as DESIGNATION1NAME,
    [DESIGNATION1].[DESIGNATIONPUBLICNAME] as DESIGNATION1PUBLICNAME,
    [DESIGNATION1].[AMOUNT] as DESIGNATION1AMOUNT,
    [DESIGNATION2].[DESIGNATIONNAME] as DESIGNATION2NAME,
    [DESIGNATION2].[DESIGNATIONPUBLICNAME] as DESIGNATION2PUBLICNAME,
    [DESIGNATION2].[AMOUNT] as DESIGNATION2AMOUNT,
    REV.REVENUELOOKUPID,
    REV.TRANSACTIONCURRENCYID,
    CURRENCY.ISO4217 as TRANSACTIONCURRENCY
  from [REV]
  inner join dbo.CONSTITUENT on REV.CONSTITUENTID = CONSTITUENT.ID
  inner join dbo.UFN_CONTACTPREFERENCES_GETFORMATS_EXT(
    @NAMEFORMATPARAMETERID,
    @ADDRESSPROCESSINGOPTIONID,
    4,
    @PARAMETERSETID,
    @SEASONALDATE,
    0,
    @INDUSESEASONALADDRESS ,
    @ORGMAILINGPREFERENCE ,
    @INDALTADDRESS1TYPECODEID,
    @INDALTADDRESS1ISPRIMARY ,
    @INDALTADDRESS2TYPECODEID,
    @INDALTADDRESS2ISPRIMARY ,
    @ORGALTADDRESS1TYPECODEID,
    @ORGALTADDRESS1ISPRIMARY ,
    @ORGALTADDRESS2TYPECODEID,
    @ORGALTADDRESS2ISPRIMARY ,
    @INDINCLUDEWITHNOADDRESS ,
    @ORGINCLUDEWITHNOADDRESS ,
    @ORGINCLUDEWITHNOCONTACT ,
    @ORGSENDTOALLCONTACTS ,
    @INDUSECONSTITUENTPREFS ,
    @ORGUSECONSTITUENTPREFS ,
    @GROUPALTADDRESS1TYPECODEID,
    @GROUPALTADDRESS1ISPRIMARY ,
    @GROUPALTADDRESS2TYPECODEID,
    @GROUPALTADDRESS2ISPRIMARY ,
    @GROUPINCLUDEWITHNOADDRESS ,
    @GROUPUSECONSTITUENTPREFS ,
    @INDIVIDUALADDRESSEES ,
    @ADDRESSEEFUNCTIONID,
    @INDIVIDUALSALUTATIONS ,
    @SALUTATIONFUNCTIONID,
    @ORGADDRESSEES ,
    @CONTACTADDRESSEEFUNCTIONID,
    @ORGSALUTATIONCODE,
    @ORGSALUTATIONS ,
    @CONTACTSALUTATIONFUNCTIONID,
    @CONTACTSALUTATIONOPTIONCODE ,
    @CUSTOMNAME ,
    @GROUPADDRESSEES ,
    @GROUPADDRESSEEFUNCTIONID,
    @GROUPSALUTATIONCODE,
    @GROUPSALUTATIONS ,
    @GROUPSALUTATIONFUNCTIONID,
    @GROUPNOCONTACTOPTIONCODE ,
    @GROUPNOCONTACTCUSTOMNAME,
    @JOINTRULETYPECODE,
    @JOINTSELECTIONID,
    @JOINTSELECTIONBOTHRULETYPECODE,
    @JOINTSELECTIONNEITHERRULETYPECODE, 
    0) A on CONSTITUENT.ID = A.CONSTITUENTID  
  left join dbo.APPEAL on REV.APPEALID = APPEAL.ID
  left join dbo.CURRENCY on CURRENCY.ID = REV.TRANSACTIONCURRENCYID
  outer apply dbo.[UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER](REV.REVENUEID, 1) as [DESIGNATION1]
  outer apply dbo.[UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER](REV.REVENUEID, 2) as [DESIGNATION2]
)