UFN_QUERY_RECEIPTOUTPUT
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @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_RECEIPTOUTPUT]
(
@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
(
select
REVENUE.ID,
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,
RECEIPTAMOUNT,
(select sum(RB.TRANSACTIONTOTALVALUE) 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,
[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],
case REVENUE.BENEFITSWAIVED when 1 then 'Yes' else 'No' end as BENEFITSWAIVED,
REVENUE.TRANSACTIONCURRENCYID,
dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCY,
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 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
where
REVENUE.TRANSACTIONTYPECODE in (0, 4, 7)
)