UFN_REVENUEINFORMATION
Returns revenue information given an ID and a source code, this is used in the GL mapping functions.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @INFORMATIONSOURCECODE | tinyint | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_REVENUEINFORMATION
(
@REVENUEID uniqueidentifier,
@INFORMATIONSOURCECODE tinyint, -- 0 is the revenue table, 1 is the batch revenue table
@CURRENTAPPUSERID uniqueidentifier = null
)
returns @RETVAL table
(
ID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
DATE datetime,
POSTDATE datetime,
DONOTPOST bit,
DONOTRECEIPT bit,
RECEIPTAMOUNT money,
AMOUNT money,
SOURCECODE nvarchar(50),
FINDERNUMBER bigint,
APPEALID uniqueidentifier,
MAILINGID uniqueidentifier,
CHANNELCODEID uniqueidentifier,
BENEFITID uniqueidentifier,
SENDBENEFIT bit,
GIVENANONYMOUSLY bit,
DONOTACKNOWLEDGE bit,
BENEFITSWAIVED bit,
RECEIPTTYPECODE tinyint,
TRANSACTIONTYPECODE tinyint,
ELIGIBLEFORMATCHINGGIFTCLAIM bit,
CUSTOMIDENTIFIER nvarchar(100),
ISREIMBURSABLE bit,
BASECURRENCYID uniqueidentifier,
ORGANIZATIONAMOUNT money,
TRANSACTIONAMOUNT money,
TRANSACTIONCURRENCYID uniqueidentifier,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
BASEEXCHANGERATEID uniqueidentifier,
PAYMENTMETHODCODE tinyint,
CREDITTYPECODEID uniqueidentifier,
OTHERPAYMENTMETHODCODEID uniqueidentifier,
PROPERTYSUBTYPECODEID uniqueidentifier,
GIFTINKINDSUBTYPECODEID uniqueidentifier,
PLEDGESUBTYPEID uniqueidentifier,
PDACCOUNTSYSTEMID uniqueidentifier
)
as
begin
if @INFORMATIONSOURCECODE = 0 -- Revenue table
begin
declare @BASECURRENCYID uniqueidentifier;
declare @REVENUEPAYMENTMETHODID uniqueidentifier;
declare @PAYMENTMETHODCODE tinyint;
select @BASECURRENCYID = V_BASECURRENCYFORFINANCIALTRANSACTION_I.BASECURRENCYID
from dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I
where V_BASECURRENCYFORFINANCIALTRANSACTION_I.FINANCIALTRANSACTIONID = @REVENUEID;
select @PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
,@REVENUEPAYMENTMETHODID = REVENUEPAYMENTMETHOD.ID
from dbo.REVENUEPAYMENTMETHOD
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID;
insert into @RETVAL
(
ID,
CONSTITUENTID,
DATE,
POSTDATE,
DONOTPOST,
DONOTRECEIPT,
RECEIPTAMOUNT,
AMOUNT,
SOURCECODE,
FINDERNUMBER,
APPEALID,
MAILINGID,
CHANNELCODEID,
BENEFITID,
SENDBENEFIT,
GIVENANONYMOUSLY,
DONOTACKNOWLEDGE,
BENEFITSWAIVED,
RECEIPTTYPECODE,
TRANSACTIONTYPECODE,
ELIGIBLEFORMATCHINGGIFTCLAIM,
CUSTOMIDENTIFIER,
ISREIMBURSABLE,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
ORGANIZATIONEXCHANGERATEID,
BASEEXCHANGERATEID,
PAYMENTMETHODCODE,
CREDITTYPECODEID,
OTHERPAYMENTMETHODCODEID,
PROPERTYSUBTYPECODEID,
GIFTINKINDSUBTYPECODEID,
PLEDGESUBTYPEID,
PDACCOUNTSYSTEMID
)
select
REVENUE.ID,
REVENUE.CONSTITUENTID,
REVENUE.DATE,
REVENUE.POSTDATE,
cast(case REVENUE.POSTSTATUSCODE when 3 then 1 else 0 end as bit) DONOTPOST,
REVENUE_EXT.DONOTRECEIPT,
REVENUE_EXT.RECEIPTAMOUNT,
REVENUE.BASEAMOUNT AMOUNT,
REVENUE_EXT.SOURCECODE,
REVENUE_EXT.FINDERNUMBER,
REVENUE_EXT.APPEALID,
REVENUE_EXT.MAILINGID,
REVENUE_EXT.CHANNELCODEID,
REVENUEBENEFIT.BENEFITID,
REVENUEBENEFIT.SENDBENEFIT,
REVENUE_EXT.GIVENANONYMOUSLY,
REVENUE_EXT.DONOTACKNOWLEDGE,
REVENUE_EXT.BENEFITSWAIVED,
REVENUE_EXT.RECEIPTTYPECODE,
REVENUE.TYPECODE TRANSACTIONTYPECODE,
REVENUE_EXT.ELIGIBLEFORMATCHINGGIFTCLAIM,
REVENUE.USERDEFINEDID CUSTOMIDENTIFIER,
REVENUE_EXT.ISREIMBURSABLE,
@BASECURRENCYID,
REVENUE.ORGAMOUNT ORGANIZATIONAMOUNT,
REVENUE.TRANSACTIONAMOUNT,
REVENUE.TRANSACTIONCURRENCYID,
REVENUE.ORGEXCHANGERATEID ORGANIZATIONEXCHANGERATEID,
REVENUE.BASEEXCHANGERATEID,
@PAYMENTMETHODCODE PAYMENTMETHODCODE,
(select CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID from dbo.CREDITCARDPAYMENTMETHODDETAIL where CREDITCARDPAYMENTMETHODDETAIL.ID = @REVENUEPAYMENTMETHODID) CREDITTYPECODEID,
(select OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID from dbo.OTHERPAYMENTMETHODDETAIL where OTHERPAYMENTMETHODDETAIL.ID = @REVENUEPAYMENTMETHODID) OTHERPAYMENTMETHODCODEID,
(select PROPERTYDETAIL_EXT.PROPERTYSUBTYPECODEID from dbo.FINANCIALTRANSACTION inner join dbo.PROPERTYDETAIL_EXT on FINANCIALTRANSACTION.ID = PROPERTYDETAIL_EXT.ID where FINANCIALTRANSACTION.TYPECODE = 22 and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTION.ID = @REVENUEPAYMENTMETHODID) PROPERTYSUBTYPECODEID,
(select GIFTINKINDPAYMENTMETHODDETAIL.GIFTINKINDSUBTYPECODEID from dbo.GIFTINKINDPAYMENTMETHODDETAIL where GIFTINKINDPAYMENTMETHODDETAIL.ID = @REVENUEPAYMENTMETHODID) GIFTINKINDSUBTYPECODEID,
(select REVENUESCHEDULE.PLEDGESUBTYPEID from dbo.REVENUESCHEDULE where REVENUESCHEDULE.ID = @REVENUEID) PLEDGESUBTYPEID,
REVENUE.PDACCOUNTSYSTEMID
from dbo.FINANCIALTRANSACTION REVENUE
inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
left outer join (select RBX.BENEFITID, RBX.SENDBENEFIT, LI.FINANCIALTRANSACTIONID as REVENUEID
from dbo.FINANCIALTRANSACTIONLINEITEM LI
inner join dbo.REVENUEBENEFIT_EXT RBX on LI.ID = RBX.ID
where LI.DELETEDON is null and LI.FINANCIALTRANSACTIONID = @REVENUEID) REVENUEBENEFIT on REVENUE.ID = REVENUEBENEFIT.REVENUEID
where REVENUE.ID = @REVENUEID and REVENUE.DELETEDON is null;
end
else if @INFORMATIONSOURCECODE = 1 -- Batch revenue table
insert into @RETVAL
(
ID,
CONSTITUENTID,
DATE,
POSTDATE,
DONOTPOST,
DONOTRECEIPT,
RECEIPTAMOUNT,
AMOUNT,
SOURCECODE,
FINDERNUMBER,
APPEALID,
MAILINGID,
CHANNELCODEID,
BENEFITID,
SENDBENEFIT,
GIVENANONYMOUSLY,
DONOTACKNOWLEDGE,
BENEFITSWAIVED,
RECEIPTTYPECODE,
TRANSACTIONTYPECODE,
ELIGIBLEFORMATCHINGGIFTCLAIM,
CUSTOMIDENTIFIER,
ISREIMBURSABLE,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
ORGANIZATIONEXCHANGERATEID,
BASEEXCHANGERATEID,
PAYMENTMETHODCODE,
CREDITTYPECODEID,
OTHERPAYMENTMETHODCODEID,
PROPERTYSUBTYPECODEID,
GIFTINKINDSUBTYPECODEID,
PLEDGESUBTYPEID,
PDACCOUNTSYSTEMID
)
select
BATCHREVENUE.ID,
BATCHREVENUE.CONSTITUENTID,
BATCHREVENUE.DATE,
BATCHREVENUE.POSTDATE,
null,
BATCHREVENUE.DONOTRECEIPT,
BATCHREVENUE.RECEIPTAMOUNT,
BATCHREVENUE.AMOUNT,
BATCHREVENUE.SOURCECODE,
BATCHREVENUE.FINDERNUMBER,
isnull(BATCHREVENUE.APPEALID, (select top 1 R.APPEALID from dbo.UFN_REVENUEBATCH_GETSINGLEAPPLICATIONINFO(@REVENUEID)T inner join dbo.REVENUE R on R.ID = T.SINGLEAPPLICATIONID)),
BATCHREVENUE.MAILINGID,
BATCHREVENUE.CHANNELCODEID,
BATCHREVENUEBENEFIT.BENEFITID,
1,
BATCHREVENUE.GIVENANONYMOUSLY,
BATCHREVENUE.DONOTACKNOWLEDGE,
BATCHREVENUE.BENEFITSWAIVED,
BATCHREVENUE.RECEIPTTYPECODE,
BATCHREVENUE.TYPECODE,
null,
BATCHREVENUE.REVENUELOOKUPID,
null,
BATCHREVENUE.BASECURRENCYID,
null,
null,
BATCHREVENUE.TRANSACTIONCURRENCYID,
null,
BATCHREVENUE.BASEEXCHANGERATEID,
case BATCHREVENUE.TYPECODE
when 1 then
9
else
BATCHREVENUE.PAYMENTMETHODCODE
end,
CREDITCARD.CREDITTYPECODEID,
BATCHREVENUE.OTHERPAYMENTMETHODCODEID,
BATCHREVENUE.PROPERTYSUBTYPECODEID,
BATCHREVENUE.GIFTINKINDSUBTYPECODEID,
BATCHREVENUE.PLEDGESUBTYPEID,
case when (dbo.UFN_VALID_BASICGL_INSTALLED() = 1) and (BATCHREVENUE.PDACCOUNTSYSTEMID is null) then
dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)
else
BATCHREVENUE.PDACCOUNTSYSTEMID
end
from dbo.BATCHREVENUE
left outer join dbo.CREDITCARD
on CREDITCARD.ID = BATCHREVENUE.CREDITCARDID
left outer join dbo.BATCHREVENUEBENEFIT
on BATCHREVENUEBENEFIT.BATCHREVENUEID = BATCHREVENUE.ID
where (BATCHREVENUE.ID = @REVENUEID);
return;
end