UFN_QUERY_RECEIPTFORMATTEDGIFTDETAILOUTPUT

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@NAMEFORMATPARAMETERID	uniqueidentifier	IN
@ADDRESSPROCESSINGOPTIONID	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_RECEIPTFORMATTEDGIFTDETAILOUTPUT]
(
  @NAMEFORMATPARAMETERID uniqueidentifier,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
  @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_CLAUSE_BEGIN###*/
  with REV_CTE as
  (
    select ID,
      CONSTITUENTID,
      AMOUNT,
      RECEIPTAMOUNT,
      DATE,
      GIVENANONYMOUSLY,
      TRANSACTIONAMOUNT,
      TRANSACTIONCURRENCYID
    from dbo.REVENUE
    where REVENUE.TRANSACTIONTYPECODE = 0
      and REVENUE.DONOTRECEIPT = 0
      and REVENUE.RECEIPTTYPECODE = 1 -- consolidated

      and not exists (select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID and REVENUE.NEEDSRERECEIPT = 0)
  )
/*###WITH_CLAUSE_END###*/
  select
    REVENUE.ID REVENUEID,
    A.CONSTITUENTID,
    CONSTITUENT.NAME CONSTITUENTNAME,
    CONSTITUENT.LOOKUPID CONSTITUENTLOOKUPID,
    A.ADDRESSEE,    
    A.SALUTATION,
    A.CONTACT as CONTACTADDRESSEE,
    A.POSITION,
    A.ADDRESSBLOCK as ADDRESSBLOCK,
    A.CITY as CITY,
    A.STATE as STATE,
    A.POSTCODE as POSTCODE,
    A.COUNTRY as COUNTRY,
    REVENUE.DATE as DATE,
    REVENUE.TRANSACTIONAMOUNT as PAYMENTAMOUNT,
    REVENUE.RECEIPTAMOUNT as RECEIPTAMOUNT,
    (select coalesce(sum(RB.TRANSACTIONTOTALVALUE),0) from dbo.REVENUEBENEFIT RB where RB.REVENUEID = REVENUE.ID) as TOTALBENEFITAMOUNT,
    REVENUEPAYMENTMETHOD.PAYMENTMETHOD as PAYMENTMETHOD,
    STOCKDETAIL.ISSUER as ISSUER,
    STOCKDETAIL.SYMBOL as SYMBOL,
    STOCKDETAIL.NUMBEROFUNITS as NUMBEROFUNITS,
    STOCKDETAIL.TRANSACTIONMEDIANPRICE as MEDIANPRICE,
    PROPERTYGIKSUBTYPE = 
      case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (5,8) then PTC.DESCRIPTION
      when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 6 then GIKTC.DESCRIPTION
      else null end,
    case REVENUE.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,

/*###WITH_FIELDS_BEGIN###*/
    (select coalesce(sum(TRANSACTIONAMOUNT),0) 
     from REV_CTE R
     where 
       R.CONSTITUENTID = REVENUE.CONSTITUENTID and
       R.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
      ) as CONSTITUENTPAYMENTAMOUNT,
    (select coalesce(sum(RECEIPTAMOUNT),0) 
     from REV_CTE R
      where 
        R.CONSTITUENTID = REVENUE.CONSTITUENTID and
        R.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
      ) as CONSTITUENTRECEIPTAMOUNT,
    (select coalesce(sum(RB.TRANSACTIONTOTALVALUE),0) 
     from REV_CTE R 
     inner join dbo.REVENUEBENEFIT RB on R.ID = RB.REVENUEID
     where 
       R.CONSTITUENTID = REVENUE.CONSTITUENTID and
       R.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
      ) as CONSTITUENTBENEFITAMOUNT,
    (select count(distinct R.ID) 
     from REV_CTE R
     where 
       R.CONSTITUENTID = REVENUE.CONSTITUENTID and
       R.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
      ) as NUMBEROFTRANSACTIONS,
    (select coalesce(sum(RV.TRANSACTIONAMOUNTTOTAX),0) 
     from REV_CTE R
     left join REVENUEVAT RV on R.ID = RV.ID
     where 
       R.CONSTITUENTID = REVENUE.CONSTITUENTID and
       R.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
      ) as TOTALPORTIONSUBJECTTOVAT,
    (select coalesce(sum(RV.TRANSACTIONVATAMOUNT),0) 
     from REV_CTE R
     left join REVENUEVAT RV on R.ID = RV.ID
     where 
       R.CONSTITUENTID = REVENUE.CONSTITUENTID and
       R.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
    ) as TOTALVATAMOUNT,    
/*###WITH_FIELDS_END###*/

    [REVENUEVAT].TRANSACTIONAMOUNTTOTAX as [PORTIONSUBJECTTOVAT],
    [VATTAXRATE].DESCRIPTION as [VATTAXRATEDESCRIPTION],
    [VATTAXRATE].TAXRATE as [VATTAXRATE],
    [REVENUEVAT].TRANSACTIONVATAMOUNT as VATAMOUNT,
    [GIK].ITEMNAME as [GIFTINKINDITEMNAME],
    [GIK].DISPOSITION as [GIFTINKINDDISPOSITION],
    [GIK].NUMBEROFUNITS as [GIFTINKINDNUMBEROFUNITS],
    [GIK].TRANSACTIONFAIRMARKETVALUE as [GIFTINKINDFAIRMARKETVALUE],

/*###WITH_FIELDS_CURRENCY_BEGIN###*/
    [REVENUE].[TRANSACTIONCURRENCYID],
    [CURRENCY].[ISO4217] as [TRANSACTIONCURRENCY],
/*###WITH_FIELDS_CURRENCY_END###*/

    case when CONSTITUENT.ISORGANIZATION = 1 then
      (select top 1 RELATIONSHIPJOBINFO.JOBTITLE 
        from dbo.RELATIONSHIP 
        inner join dbo.RELATIONSHIPJOBINFO on RELATIONSHIP.RELATIONSHIPSETID = RELATIONSHIPJOBINFO.RELATIONSHIPSETID
        inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
        where RELATIONSHIP.ISPRIMARYCONTACT = 1
          and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
    else
      null
    end as JOBTITLE

  from dbo.REVENUE
  inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID    
  inner join dbo.UFN_CONTACTPREFERENCES_GETFORMATS_EXT(  
      @NAMEFORMATPARAMETERID,
      @ADDRESSPROCESSINGOPTIONID,
      5,
      null,
      @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  
  inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
  left join dbo.STOCKDETAIL on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
  left join dbo.PROPERTYDETAIL PD on PD.ID = REVENUEPAYMENTMETHOD.ID
  left join dbo.PROPERTYSUBTYPECODE PTC on PTC.ID = PD.PROPERTYSUBTYPECODEID
  left join dbo.GIFTINKINDPAYMENTMETHODDETAIL GIK on GIK.ID = REVENUEPAYMENTMETHOD.ID
  left join dbo.GIFTINKINDSUBTYPECODE GIKTC on GIKTC.ID = GIK.GIFTINKINDSUBTYPECODEID
  left join dbo.REVENUEVAT on REVENUE.ID = REVENUEVAT.ID
  left join dbo.VATTAXRATE on REVENUEVAT.VATTAXRATEID = VATTAXRATE.ID
  left join dbo.[CURRENCY] on [CURRENCY].[ID] = [REVENUE].[TRANSACTIONCURRENCYID]
/*###WITH_JOINS_BEGIN###*/
/*###WITH_JOINS_END###*/
  where 
    REVENUE.TRANSACTIONTYPECODE in (0, 4)
    and REVENUE.DONOTRECEIPT = 0
    and REVENUE.RECEIPTTYPECODE = 1 -- consolidated

    and not exists (select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID and REVENUE.NEEDSRERECEIPT = 0)

        )