UFN_QUERY_RECEIPTFORMATTEDGIFTSUMMARYOUTPUT

CREATE function dbo.UFN_QUERY_RECEIPTFORMATTEDGIFTSUMMARYOUTPUT
(
  @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 REVENUE_CTE as (
  select -- don't need distinct here; REVENUEID is in the select clause

    REVENUE.CONSTITUENTID,
    REVENUE.TRANSACTIONCURRENCYID,
    REVENUE.ID as REVENUEID,
    REVENUE.TRANSACTIONAMOUNT,
    REVENUE.RECEIPTAMOUNT
  from dbo.REVENUE
  where REVENUE.TRANSACTIONTYPECODE in (0, 4, 7)
  and REVENUE.DONOTRECEIPT = 0
  and REVENUE.RECEIPTTYPECODE = 1
  and not exists (select RR.ID from REVENUERECEIPT as RR where RR.REVENUEID = REVENUE.ID and REVENUE.NEEDSRERECEIPT = 0)
), BENEFITS_CTE as (
  select
    REVENUE_CTE.CONSTITUENTID,
    sum(REVENUEBENEFIT.TRANSACTIONTOTALVALUE) as CONSTITUENTBENEFITAMOUNT -- REVENUEBENEFIT is one-to-optional-many with REVENUE; can't join above because it will affect aggregates

  from dbo.REVENUEBENEFIT
  inner join REVENUE_CTE on REVENUE_CTE.REVENUEID = REVENUEBENEFIT.REVENUEID
  group by REVENUE_CTE.CONSTITUENTID
), AMOUNTS_CTE as (
  select 
    REVENUE_CTE.CONSTITUENTID,
    REVENUE_CTE.TRANSACTIONCURRENCYID,
    sum(REVENUE_CTE.TRANSACTIONAMOUNT) as CONSTITUENTPAYMENTAMOUNT,
    sum(REVENUE_CTE.RECEIPTAMOUNT) as CONSTITUENTRECEIPTAMOUNT,
    count(REVENUE_CTE.REVENUEID) as NUMBEROFTRANSACTIONS,                          -- don't need distinct here; REVENUE_CTE is distinct already

    sum(isnull(REVENUEVAT.TRANSACTIONAMOUNTTOTAX, 0)) as TOTALPORTIONSUBJECTTOVAT, -- REVENUEVAT is one-to-optional-one with REVENUE

    sum(isnull(REVENUEVAT.TRANSACTIONVATAMOUNT, 0)) as TOTALVATAMOUNT
  from REVENUE_CTE
  left join REVENUEVAT on REVENUEVAT.ID = REVENUE_CTE.REVENUEID
  group by REVENUE_CTE.CONSTITUENTID, REVENUE_CTE.TRANSACTIONCURRENCYID
)
/*###WITH_CLAUSE_END###*/
select 
  ADDRESS.CONSTITUENTID,
  CONSTITUENT.NAME as CONSTITUENTNAME,
  CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
  ADDRESS.ADDRESSEE,    
  ADDRESS.SALUTATION,
  ADDRESS.CONTACT as CONTACTADDRESSEE,
  ADDRESS.POSITION,
  ADDRESS.ADDRESSBLOCK as ADDRESSBLOCK,
  ADDRESS.CITY as CITY,
  ADDRESS.STATE as STATE,
  ADDRESS.POSTCODE as POSTCODE,
  ADDRESS.COUNTRY as COUNTRY,                        
/*###WITH_FIELDS_BEGIN###*/
  AMOUNTS_CTE.CONSTITUENTPAYMENTAMOUNT,
  AMOUNTS_CTE.CONSTITUENTRECEIPTAMOUNT,
  isnull(BENEFITS_CTE.CONSTITUENTBENEFITAMOUNT, 0) as CONSTITUENTBENEFITAMOUNT,
  AMOUNTS_CTE.NUMBEROFTRANSACTIONS,
  AMOUNTS_CTE.TOTALPORTIONSUBJECTTOVAT,
  AMOUNTS_CTE.TOTALVATAMOUNT,
  AMOUNTS_CTE.TRANSACTIONCURRENCYID,
  CURRENCY.ISO4217 as TRANSACTIONCURRENCY,
/*###WITH_FIELDS_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
     order by RELATIONSHIPJOBINFO.STARTDATE desc)
  else null end as JOBTITLE
from dbo.CONSTITUENT
/*###WITH_JOINS_BEGIN###*/
inner join AMOUNTS_CTE on AMOUNTS_CTE.CONSTITUENTID = CONSTITUENT.ID
inner join dbo.CURRENCY on CURRENCY.ID = AMOUNTS_CTE.TRANSACTIONCURRENCYID
left join BENEFITS_CTE on BENEFITS_CTE.CONSTITUENTID = AMOUNTS_CTE.CONSTITUENTID
/*###WITH_JOINS_END###*/
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) as ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID
)