UFN_QUERY_PLEDGEREMINDEROUTPUT2

Return

Return Type
table

Parameters

Parameter Parameter Type Mode Description
@REVENUEID uniqueidentifier IN
@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_PLEDGEREMINDEROUTPUT2
(
  @REVENUEID uniqueidentifier,
  @NAMEFORMATPARAMETERID uniqueidentifier,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier,  
  @PARAMETERSETID uniqueidentifier,  
  @SEASONALDATE datetime,  
  @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 (
  select 
    DETAIL.REVENUEID,
    SUMMARY.CONSTITUENTNAME,
    SUMMARY.CONSTITUENTLOOKUPID,
    SUMMARY.ADDRESSEE,
    SUMMARY.SALUTATION,
    SUMMARY.CONTACTADDRESSEE,
    SUMMARY.POSITION,
    SUMMARY.ADDRESSBLOCK,
    SUMMARY.CITY,
    SUMMARY.STATE,
    SUMMARY.POSTCODE,
    SUMMARY.COUNTRY,
    DETAIL.TOTALPLEDGEAMOUNT,
    DETAIL.GIVENANONYMOUSLY,
    DETAIL.PLEDGEDATE, 
    DETAIL.PLEDGEBALANCE,
    DETAIL.AMOUNTPAID,
    DETAIL.APPEALNAME,
    -- keep these casts!

    -- under certain circumstances, PledgeRemindersProcess.vb substitutes UFN_QUERY_PLEDGEREMINDEROUTPUT for this, presumably for performance reasons

    -- UFN_QUERY_PLEDGEREMINDEROUTPUT populates this field with the fully qualified name of the designation, which can be 512 characters long -- if this

    -- cast is removed, that data will overflow this field in the output table that is generated

    cast(DETAIL.DESIGNATION1NAME as nvarchar(512)) as DESIGNATION1NAME,
    DETAIL.DESIGNATION1PUBLICNAME,
    DETAIL.DESIGNATION1AMOUNT,
    cast(DETAIL.DESIGNATION2NAME as nvarchar(512)) as DESIGNATION2NAME,
    DETAIL.DESIGNATION2PUBLICNAME,
    DETAIL.DESIGNATION2AMOUNT,
    DETAIL.REVENUELOOKUPID,
    DETAIL.TRANSACTIONCURRENCYID,
    DETAIL.TRANSACTIONCURRENCY
  from dbo.UFN_QUERY_PLEDGEREMINDEROUTPUTDETAILS2 (
    @REVENUEID,
    @NAMEFORMATPARAMETERID,
    @ADDRESSPROCESSINGOPTIONID,
    @PARAMETERSETID,
    @SEASONALDATE,
    @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) as DETAIL 
  inner join dbo.FINANCIALTRANSACTION on DETAIL.REVENUEID = FINANCIALTRANSACTION.ID
  cross apply 
  dbo.UFN_QUERY_PLEDGEREMINDEROUTPUTSUMMARY2 (
    FINANCIALTRANSACTION.CONSTITUENTID,
    @NAMEFORMATPARAMETERID,
    @ADDRESSPROCESSINGOPTIONID,
    @PARAMETERSETID,
    @SEASONALDATE,
    @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) as SUMMARY
)