UFN_REVENUEBATCH_GETAPPLICATIONS
Returns the applications for a given row in a revenue batch table.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @BATCHREVENUEID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_REVENUEBATCH_GETAPPLICATIONS(
@BATCHREVENUEID uniqueidentifier
)
returns @APPLICATIONS table(
ID uniqueidentifier,
APPLICATIONID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
CONSTITUENTNAME nvarchar(700),
APPLIED money,
BALANCE money,
AMOUNTDUE money,
DATEDUE datetime,
TYPECODE tinyint,
RECEIPTAMOUNT money,
DONOTRECEIPT bit,
DESCRIPTION nvarchar(300),
WASGENERATED bit,
OVERPAYMENTAPPLICATIONTYPECODE tinyint,
DESIGNATIONLIST nvarchar(300),
SPONSORSHIPOPPORTUNITY nvarchar(300),
REVENUESPLITID uniqueidentifier,
BATCHREVENUEREGISTRANTID uniqueidentifier,
APPLICATIONCURRENCYID uniqueidentifier,
PDACCOUNTSYSTEMID uniqueidentifier,
SPONSORSHIPRGADDITIONALGIFT bit,
CATEGORYCODEID uniqueidentifier,
APPLICATIONCOMMITMENTID uniqueidentifier
)
as
begin
declare @EMPTYGUID uniqueidentifier;
set @EMPTYGUID = '00000000-0000-0000-0000-000000000000';
declare @REVENUEAPPLICATIONS table (
ID uniqueidentifier,
APPLICATIONID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
CONSTITUENTNAME nvarchar(700),
APPLIED money,
BALANCE money,
AMOUNTDUE money,
DATEDUE datetime,
TYPECODE tinyint,
RECEIPTAMOUNT money,
DONOTRECEIPT bit,
DESCRIPTION nvarchar(300),
WASGENERATED bit,
OVERPAYMENTAPPLICATIONTYPECODE tinyint,
DESIGNATIONLIST nvarchar(300),
SPONSORSHIPOPPORTUNITY nvarchar(300),
REVENUESPLITID uniqueidentifier,
APPLICATIONCURRENCYID uniqueidentifier,
PDACCOUNTSYSTEMID uniqueidentifier,
SPONSORSHIPRGADDITIONALGIFT bit,
CATEGORYCODEID uniqueidentifier
);
declare @EVENTAPPLICATIONS table (
ID uniqueidentifier,
APPLICATIONID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
CONSTITUENTNAME nvarchar(700),
APPLIED money,
BALANCE money,
AMOUNTDUE money,
DATEDUE datetime,
TYPECODE tinyint,
RECEIPTAMOUNT money,
DONOTRECEIPT bit,
DESCRIPTION nvarchar(300),
REVENUESPLITID uniqueidentifier,
BATCHREVENUEREGISTRANTID uniqueidentifier,
APPLICATIONCURRENCYID uniqueidentifier,
PDACCOUNTSYSTEMID uniqueidentifier
);
declare @NOW datetime;
set @NOW=GETDATE();
declare @REVENUEDATE datetime;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @BASEEXCHANGERATEID uniqueidentifier;
declare @SPOTRATE decimal(20,8);
select
@REVENUEDATE = DATE,
@TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
@BASECURRENCYID = BASECURRENCYID,
@BASEEXCHANGERATEID = BASEEXCHANGERATEID,
@SPOTRATE = EXCHANGERATE
from dbo.BATCHREVENUE
where ID = @BATCHREVENUEID;
insert into @REVENUEAPPLICATIONS(ID, APPLICATIONID, CONSTITUENTID, CONSTITUENTNAME, APPLIED, BALANCE, AMOUNTDUE, DATEDUE, TYPECODE, RECEIPTAMOUNT, DONOTRECEIPT, DESCRIPTION, WASGENERATED, OVERPAYMENTAPPLICATIONTYPECODE, DESIGNATIONLIST, SPONSORSHIPOPPORTUNITY, REVENUESPLITID, APPLICATIONCURRENCYID,PDACCOUNTSYSTEMID,SPONSORSHIPRGADDITIONALGIFT, CATEGORYCODEID)
select
BATCHREVENUEAPPLICATION.ID as [ID],
BATCHREVENUEAPPLICATION.REVENUEID as [APPLICATIONID],
FINANCIALTRANSACTION.CONSTITUENTID as [CONSTITUENTID],
--JamesWill 09/12/2008 Bug 13504, include the matched constituent's name for matching gift claims. (Copied from the Constituent Unrealized Revenue datalist (USP_DATALIST_CONSTITUENTUNREALIZEDREVENUE))
(select
case FINANCIALTRANSACTION.TYPECODE
when 3
then CONSTITUENT_NF.NAME + ' - '
+ (
select
C1_NF.NAME + ' (' + C1.LOOKUPID + ')'
from dbo.REVENUEMATCHINGGIFT MG1
inner join dbo.FINANCIALTRANSACTION R1 on R1.ID = MG1.MGSOURCEREVENUEID
inner join dbo.CONSTITUENT C1 on C1.ID = R1.CONSTITUENTID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(R1.CONSTITUENTID) C1_NF
where MG1.ID = FINANCIALTRANSACTION.ID
)
else CONSTITUENT_NF.NAME
end
) as [CONSTITUENTNAME],
BATCHREVENUEAPPLICATION.APPLIED,
case
when FINANCIALTRANSACTION.TYPECODE in (1, 3, 4, 6, 8)
then dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID)
when FINANCIALTRANSACTION.TYPECODE = 2 and REVENUESCHEDULE.STATUSCODE in (0,5)
then FINANCIALTRANSACTION.TRANSACTIONAMOUNT
when FINANCIALTRANSACTION.TYPECODE = 2 and REVENUESCHEDULE.STATUSCODE in (1, 2, 3, 4)
then 0
else null
end as [BALANCE],
case
when FINANCIALTRANSACTION.TYPECODE in (1 ,3, 4, 6, 8)
then dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(dbo.UFN_REVENUE_GETNEXTINSTALLMENT(FINANCIALTRANSACTION.ID))
when FINANCIALTRANSACTION.TYPECODE = 2 and REVENUESCHEDULE.STATUSCODE in (0,5)
then FINANCIALTRANSACTION.TRANSACTIONAMOUNT
when FINANCIALTRANSACTION.TYPECODE = 2 and REVENUESCHEDULE.STATUSCODE in (1, 2, 3, 4)
then 0
else null
end as [AMOUNTDUE],
case
when FINANCIALTRANSACTION.TYPECODE in (1, 3, 4, 6, 8)
then INSTALLMENT.DATE
when FINANCIALTRANSACTION.TYPECODE = 2
then REVENUESCHEDULE.NEXTTRANSACTIONDATE
else null
end as [DATEDUE],
case FINANCIALTRANSACTION.TYPECODE
when 6
then 9
when 8
then 10
else FINANCIALTRANSACTION.TYPECODE
end as TRANSACTIONTYPECODE,
BATCHREVENUEAPPLICATION.RECEIPTAMOUNT as [RECEIPTAMOUNT],
0 as [DONOTRECEIPT], --Default to 'Not receipted' like a payment made from the Add Payment screen
'' as [DESCRIPTION],
BATCHREVENUEAPPLICATION.WASGENERATED,
BATCHREVENUEAPPLICATION.OVERPAYMENTAPPLICATIONTYPECODE,
left((
select top 1
dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST
from dbo.REVENUESPLIT_EXT
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
where (BATCHREVENUEAPPLICATION.REVENUESPLITID is null and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID)
or (BATCHREVENUEAPPLICATION.REVENUESPLITID is not null and FINANCIALTRANSACTIONLINEITEM.ID = BATCHREVENUEAPPLICATION.REVENUESPLITID)
),300) as DESIGNATIONLIST,
(
select top 1
coalesce(
(select NAME from SPONSORSHIPOPPORTUNITYCHILD where ID=S.SPONSORSHIPOPPORTUNITYID),
(select NAME from SPONSORSHIPOPPORTUNITYPROJECT where ID = S.SPONSORSHIPOPPORTUNITYID)
) as SPONSORSHIPOPPORTUNITY
from dbo.SPONSORSHIP S
inner join FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = S.REVENUESPLITID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = BATCHREVENUEAPPLICATION.REVENUEID
and S.ISMOSTRECENTFORCOMMITMENT = 1
) as SPONSORSHIPOPPORTUNITY,
BATCHREVENUEAPPLICATION.REVENUESPLITID,
FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
PDAS.PDACCOUNTSYSTEMID,
case
when exists (
select 1
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
and REVENUESPLIT_EXT.TYPECODE = 17
)
then 1
else 0
end SPONSORSHIPRGADDITIONALGIFT,
(
select top 1
REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
from
dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.REVENUECATEGORY on FINANCIALTRANSACTIONLINEITEM.ID = REVENUECATEGORY.ID
where
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
) as CATEGORYCODEID
from dbo.BATCHREVENUEAPPLICATION
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = BATCHREVENUEAPPLICATION.REVENUEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) CONSTITUENT_NF
left join dbo.INSTALLMENT on INSTALLMENT.ID = dbo.UFN_REVENUE_GETNEXTINSTALLMENT(FINANCIALTRANSACTION.ID)
left join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
left join dbo.PDACCOUNTSYSTEMFORREVENUE PDAS on BATCHREVENUEAPPLICATION.REVENUEID = PDAS.ID
where BATCHREVENUEAPPLICATION.BATCHREVENUEID = @BATCHREVENUEID and BATCHREVENUEAPPLICATION.APPLICATIONTYPECODE = 0;
-- applications to pre-existing event registrations
insert into @EVENTAPPLICATIONS(ID, APPLICATIONID, CONSTITUENTID, CONSTITUENTNAME, APPLIED, BALANCE, AMOUNTDUE, DATEDUE, TYPECODE, RECEIPTAMOUNT, DONOTRECEIPT, DESCRIPTION, REVENUESPLITID, APPLICATIONCURRENCYID,PDACCOUNTSYSTEMID)
select
BATCHREVENUEAPPLICATION.ID as [ID],
BATCHREVENUEAPPLICATION.REGISTRANTID as [APPLICATIONID],
REGISTRANT.CONSTITUENTID as [CONSTITUENTID],
CONSTITUENT_NF.NAME as [CONSTITUENTNAME],
BATCHREVENUEAPPLICATION.APPLIED,
dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as [BALANCE],
dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as [AMOUNTDUE],
EVENT.STARTDATE as [DATEDUE], --JamesWill CR260844-113006 2007/03/15 Use the event start date as the due date
6 as [TYPECODE],
dbo.UFN_EVENTREGISTRANT_GETRECEIPTAMOUNT_NOBENEFITS(
REGISTRANT.ID,
@REVENUEDATE,
BATCHREVENUEAPPLICATION.APPLIED,
@TRANSACTIONCURRENCYID,
@BASECURRENCYID,
@BASEEXCHANGERATEID,
@SPOTRATE,
0,
null
) as [RECEIPTAMOUNT],
0 as [DONOTRECEIPT], --Default to 'Not receipted' like a payment made from the Add Payment screen
EVENT.NAME as [DESCRIPTION],
BATCHREVENUEAPPLICATION.REVENUESPLITID,
EVENT.BASECURRENCYID,
PDAS.PDACCOUNTSYSTEMID
from dbo.BATCHREVENUEAPPLICATION
inner join dbo.REGISTRANT on REGISTRANT.ID = BATCHREVENUEAPPLICATION.REGISTRANTID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) CONSTITUENT_NF
inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
left join dbo.PDACCOUNTSYSTEMFORREVENUE PDAS on BATCHREVENUEAPPLICATION.REVENUEID = PDAS.ID
where BATCHREVENUEAPPLICATION.BATCHREVENUEID = @BATCHREVENUEID and BATCHREVENUEAPPLICATION.APPLICATIONTYPECODE = 1;
-- applications to pre-existing event registrations (for enhanced revenue batch and revenue update batch)
insert into @EVENTAPPLICATIONS(ID, APPLICATIONID, CONSTITUENTID, CONSTITUENTNAME, APPLIED, BALANCE, AMOUNTDUE, DATEDUE, TYPECODE, RECEIPTAMOUNT, DONOTRECEIPT, DESCRIPTION, REVENUESPLITID, APPLICATIONCURRENCYID,PDACCOUNTSYSTEMID)
select
BATCHREVENUE.ID as [ID],
REGISTRANT.ID as [APPLICATIONID],
REGISTRANT.CONSTITUENTID as [CONSTITUENTID],
CONSTITUENT_NF.NAME as [CONSTITUENTNAME],
case when V1.APPLICATIONAMOUNT > BATCHREVENUE.AMOUNT then BATCHREVENUE.AMOUNT else V1.APPLICATIONAMOUNT end as [APPLIED], -- Handle the case partial paid amount.
dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as [BALANCE],
dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as [AMOUNTDUE],
EVENT.STARTDATE as [DATEDUE], --JamesWill CR260844-113006 2007/03/15 Use the event start date as the due date
6 as [TYPECODE],
dbo.UFN_EVENTREGISTRANT_GETRECEIPTAMOUNT_NOBENEFITS(
REGISTRANT.ID,
@REVENUEDATE,
V1.APPLICATIONAMOUNT,
@TRANSACTIONCURRENCYID,
@BASECURRENCYID,
@BASEEXCHANGERATEID,
@SPOTRATE,
0,
null
) as [RECEIPTAMOUNT],
0 as [DONOTRECEIPT], --Default to 'Not receipted' like a payment made from the Add Payment screen
EVENT.NAME as [DESCRIPTION],
BATCHREVENUE.REVENUESPLITID,
EVENT.BASECURRENCYID,
PDAS.PDACCOUNTSYSTEMID
from dbo.BATCHREVENUE
inner join (
select
BR1.ID,
(
select TOP 1 APPLICATIONTYPECODE
from dbo.UFN_REVENUEBATCH_PARSEAPPLICATIONINFO(BR1.APPLICATIONINFO)
) as APPLICATIONTYPECODE,
(
select TOP 1 SINGLEAPPLICATIONID
from dbo.UFN_REVENUEBATCH_PARSEAPPLICATIONINFO(BR1.APPLICATIONINFO)
) as SINGLEAPPLICATIONID,
(
select TOP 1 APPLICATIONAMOUNT
from dbo.UFN_REVENUEBATCH_PARSEAPPLICATIONINFO(BR1.APPLICATIONINFO)
) as APPLICATIONAMOUNT
from dbo.BATCHREVENUE BR1
where BR1.ID = @BATCHREVENUEID and len(BR1.APPLICATIONINFO)>3
) V1 on BATCHREVENUE.ID=V1.ID
inner join dbo.REGISTRANT on REGISTRANT.ID = V1.SINGLEAPPLICATIONID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) CONSTITUENT_NF
inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
left join dbo.PDACCOUNTSYSTEMFORREVENUE PDAS on BATCHREVENUE.REVENUEID = PDAS.ID
where BATCHREVENUE.ID = @BATCHREVENUEID and V1.APPLICATIONTYPECODE =7
and REGISTRANT.ID not in (select APPLICATIONID from @EVENTAPPLICATIONS);
-- applications to event registrations created in batch
insert into @EVENTAPPLICATIONS(ID, APPLICATIONID, CONSTITUENTID, CONSTITUENTNAME, APPLIED, BALANCE, AMOUNTDUE, DATEDUE, TYPECODE, RECEIPTAMOUNT, DONOTRECEIPT, DESCRIPTION, REVENUESPLITID, BATCHREVENUEREGISTRANTID, APPLICATIONCURRENCYID,PDACCOUNTSYSTEMID)
select
BATCHREVENUEAPPLICATION.ID as [ID],
BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID as APPLICATIONID,
BATCHREVENUEREGISTRANT.CONSTITUENTID,
coalesce(CONSTITUENT_NF.NAME, BATCHREVENUECONSTITUENT.NAME) as CONSTITUENTNAME,
BATCHREVENUEAPPLICATION.APPLIED,
dbo.UFN_REVENUEBATCH_EVENTREGISTRANT_GETBALANCE(BATCHREVENUEREGISTRANT.ID) as BALANCE,
dbo.UFN_REVENUEBATCH_EVENTREGISTRANT_GETBALANCE(BATCHREVENUEREGISTRANT.ID) as AMOUNTDUE,
EVENT.STARTDATE as DATEDUE,
6 as TYPECODE,
dbo.UFN_EVENTREGISTRANT_GETRECEIPTAMOUNT_NOBENEFITS(
BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID,
@REVENUEDATE,
BATCHREVENUEAPPLICATION.APPLIED,
@TRANSACTIONCURRENCYID,
@BASECURRENCYID,
@BASEEXCHANGERATEID,
@SPOTRATE,
1,
null
) as [RECEIPTAMOUNT],
0 as DONOTRECEIPT,
EVENT.NAME as DESCRIPTION,
BATCHREVENUEAPPLICATION.REVENUESPLITID,
BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID,
EVENT.BASECURRENCYID,
PDAS.PDACCOUNTSYSTEMID
from dbo.BATCHREVENUE
inner join dbo.BATCHREVENUEAPPLICATION on BATCHREVENUE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEID
inner join dbo.BATCHREVENUEREGISTRANT on BATCHREVENUEREGISTRANT.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(BATCHREVENUE.CONSTITUENTID) CONSTITUENT_NF
left join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUE.CONSTITUENTID = BATCHREVENUECONSTITUENT.ID
left outer join dbo.EVENT on BATCHREVENUEREGISTRANT.EVENTID = EVENT.ID
left join dbo.PDACCOUNTSYSTEMFORREVENUE PDAS on BATCHREVENUEAPPLICATION.REVENUEID = PDAS.ID
where BATCHREVENUE.ID = @BATCHREVENUEID;
insert into @REVENUEAPPLICATIONS(ID, APPLICATIONID, CONSTITUENTID, CONSTITUENTNAME, APPLIED, BALANCE, AMOUNTDUE, DATEDUE, TYPECODE, RECEIPTAMOUNT, DONOTRECEIPT, DESCRIPTION, WASGENERATED, OVERPAYMENTAPPLICATIONTYPECODE, SPONSORSHIPOPPORTUNITY, REVENUESPLITID, APPLICATIONCURRENCYID,PDACCOUNTSYSTEMID, CATEGORYCODEID)
select
BATCHREVENUEAPPLICATION.ID as [ID],
BATCHREVENUEAPPLICATION.REVENUEID as [APPLICATIONID],
BATCHREVENUE.CONSTITUENTID as [CONSTITUENTID],
CONSTITUENT_NF.NAME as [CONSTITUENTNAME],
BATCHREVENUEAPPLICATION.APPLIED,
BATCHREVENUEAPPLICATION.APPLIED as [BALANCE],
BATCHREVENUEAPPLICATION.APPLIED as [AMOUNTDUE],
@NOW as [DATEDUE],
3,
BATCHREVENUEAPPLICATION.RECEIPTAMOUNT as [RECEIPTAMOUNT],
0 as [DONOTRECEIPT], --Default to 'Not receipted' like a payment made from the Add Payment screen
'' as [DESCRIPTION],
BATCHREVENUEAPPLICATION.WASGENERATED,
BATCHREVENUEAPPLICATION.OVERPAYMENTAPPLICATIONTYPECODE,
(
select top 1
coalesce(
(select NAME from SPONSORSHIPOPPORTUNITYCHILD where ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID),
(select NAME from SPONSORSHIPOPPORTUNITYPROJECT where ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID)
) as SPONSORSHIPOPPORTUNITY
from dbo.SPONSORSHIP
inner join FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = SPONSORSHIP.REVENUESPLITID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = BATCHREVENUEAPPLICATION.REVENUEID
and SPONSORSHIP.ISMOSTRECENTFORCOMMITMENT = 1
) as SPONSORSHIPOPPORTUNITY,
BATCHREVENUEAPPLICATION.REVENUESPLITID,
BATCHREVENUE.TRANSACTIONCURRENCYID,
PDAS.PDACCOUNTSYSTEMID,
(
select top 1
REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
from
dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.REVENUECATEGORY on FINANCIALTRANSACTIONLINEITEM.ID = REVENUECATEGORY.ID
where
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = BATCHREVENUEAPPLICATION.REVENUEID
) as CATEGORYCODEID
from dbo.BATCHREVENUE
inner join dbo.BATCHREVENUEAPPLICATION ON BATCHREVENUE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(BATCHREVENUE.CONSTITUENTID) CONSTITUENT_NF
left join dbo.PDACCOUNTSYSTEMFORREVENUE PDAS on BATCHREVENUEAPPLICATION.REVENUEID = PDAS.ID
where BATCHREVENUE.ID = @BATCHREVENUEID
and BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONPLEDGEID IS NULL
and BATCHREVENUEAPPLICATION.REVENUEID IS NULL
and BATCHREVENUEAPPLICATION.APPLICATIONTYPECODE <> 1
and BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONMEMBERSHIPID is null;
insert into @REVENUEAPPLICATIONS(ID, APPLICATIONID, CONSTITUENTID, CONSTITUENTNAME, APPLIED, BALANCE, AMOUNTDUE, DATEDUE, TYPECODE, RECEIPTAMOUNT, DONOTRECEIPT, DESCRIPTION, WASGENERATED, OVERPAYMENTAPPLICATIONTYPECODE, SPONSORSHIPOPPORTUNITY, REVENUESPLITID, APPLICATIONCURRENCYID,PDACCOUNTSYSTEMID)
select
BATCHREVENUEAPPLICATION.ID as [ID],
BATCHREVENUEAPPLICATIONMEMBERSHIP.MEMBERSHIPID as [APPLICATIONID],
BATCHREVENUE.CONSTITUENTID as [CONSTITUENTID],
CONSTITUENT_NF.NAME as [CONSTITUENTNAME],
BATCHREVENUEAPPLICATION.APPLIED,
BATCHREVENUEAPPLICATION.APPLIED as [BALANCE],
BATCHREVENUEAPPLICATION.APPLIED as [AMOUNTDUE],
@NOW as [DATEDUE],
5 as [TYPECODE],
BATCHREVENUEAPPLICATION.RECEIPTAMOUNT as [RECEIPTAMOUNT],
0 as [DONOTRECEIPT], --Default to 'Not receipted' like a payment made from the Add Payment screen
dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(BATCHREVENUEAPPLICATIONMEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(BATCHREVENUEAPPLICATIONMEMBERSHIP.MEMBERSHIPLEVELID) as [DESCRIPTION],
0 as WASGENERATED,
BATCHREVENUEAPPLICATION.OVERPAYMENTAPPLICATIONTYPECODE,
(
select top 1
coalesce(
(select NAME from SPONSORSHIPOPPORTUNITYCHILD where ID=S.SPONSORSHIPOPPORTUNITYID),
(select NAME from SPONSORSHIPOPPORTUNITYPROJECT where ID = S.SPONSORSHIPOPPORTUNITYID)
) as SPONSORSHIPOPPORTUNITY
from dbo.SPONSORSHIP S
inner join FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = S.REVENUESPLITID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = BATCHREVENUEAPPLICATION.REVENUEID
and S.ISMOSTRECENTFORCOMMITMENT = 1
) as SPONSORSHIPOPPORTUNITY,
BATCHREVENUEAPPLICATION.REVENUESPLITID,
dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() as APPLICATIONCURRENCYID,
PDAS.PDACCOUNTSYSTEMID
from dbo.BATCHREVENUE
inner join dbo.BATCHREVENUEAPPLICATION ON BATCHREVENUE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEID
inner join dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP on BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONMEMBERSHIPID = BATCHREVENUEAPPLICATIONMEMBERSHIP.ID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(BATCHREVENUE.CONSTITUENTID) CONSTITUENT_NF
left join dbo.PDACCOUNTSYSTEMFORREVENUE PDAS on BATCHREVENUEAPPLICATION.REVENUEID = PDAS.ID
where BATCHREVENUE.ID = @BATCHREVENUEID
--Membership
and BATCHREVENUEAPPLICATION.APPLICATIONTYPECODE = 5;
delete from @REVENUEAPPLICATIONS where APPLIED = 0 and dbo.UFN_REVENUE_ISPENDING(APPLICATIONID) = 1;
insert into @APPLICATIONS(ID, APPLICATIONID, CONSTITUENTID, CONSTITUENTNAME, APPLIED, BALANCE, AMOUNTDUE, DATEDUE, TYPECODE, RECEIPTAMOUNT, DONOTRECEIPT, DESCRIPTION, WASGENERATED, OVERPAYMENTAPPLICATIONTYPECODE, DESIGNATIONLIST, SPONSORSHIPOPPORTUNITY, REVENUESPLITID, BATCHREVENUEREGISTRANTID, APPLICATIONCURRENCYID,PDACCOUNTSYSTEMID,SPONSORSHIPRGADDITIONALGIFT, CATEGORYCODEID, APPLICATIONCOMMITMENTID)
select
ID,
coalesce(APPLICATIONID, @EMPTYGUID),
CONSTITUENTID,
CONSTITUENTNAME,
APPLIED,
BALANCE,
AMOUNTDUE,
DATEDUE,
TYPECODE,
RECEIPTAMOUNT,
DONOTRECEIPT,
DESCRIPTION,
WASGENERATED,
case
when OVERPAYMENTAPPLICATIONTYPECODE = 255
then null
else OVERPAYMENTAPPLICATIONTYPECODE
end as [OVERPAYMENTAPPLICATIONTYPECODE],
DESIGNATIONLIST,
SPONSORSHIPOPPORTUNITY,
REVENUESPLITID,
null as [BATCHREVENUEREGISTRANTID],
APPLICATIONCURRENCYID,
PDACCOUNTSYSTEMID,
SPONSORSHIPRGADDITIONALGIFT,
CATEGORYCODEID,
APPLICATIONID as APPLICATIONCOMMITMENTID
from @REVENUEAPPLICATIONS
union all
select
ID,
APPLICATIONID,
CONSTITUENTID,
CONSTITUENTNAME,
APPLIED,
BALANCE,
AMOUNTDUE,
DATEDUE,
TYPECODE,
RECEIPTAMOUNT,
DONOTRECEIPT,
DESCRIPTION,
0 as [WASGENERATED],
null as [OVERPAYMENTAPPLICATIONTYPECODE],
'' as DESIGNATIONLIST,
'' as SPONSORSHIPOPPORTUNITY,
REVENUESPLITID,
BATCHREVENUEREGISTRANTID,
APPLICATIONCURRENCYID,
PDACCOUNTSYSTEMID,
0 as [SPONSORSHIPRGADDITIONALGIFT],
null as CATEGORYCODEID,
APPLICATIONID as APPLICATIONCOMMITMENTID
from @EVENTAPPLICATIONS;
return;
end