UFN_REVENUEBATCH_GETAPPLICATIONSWITHCHILDREN
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @BATCHREVENUEID | uniqueidentifier | IN |
Definition
Copy
CREATE function [dbo].[UFN_REVENUEBATCH_GETAPPLICATIONSWITHCHILDREN]
(
@BATCHREVENUEID uniqueidentifier
)
returns table
return
--Applications to pledges created in batch
select
BATCHREVENUEAPPLICATION.ID as ID,
'9B9C1DC8-7960-4D31-A0BC-8199AB7F94DA' as 'APPLICATIONID',
BATCHREVENUE.CONSTITUENTID as 'CONSTITUENTID',
isnull(CONSTITUENT_NF.NAME, BATCHREVENUECONSTITUENT.NAME) as CONSTITUENTNAME,
BATCHREVENUEAPPLICATION.APPLIED,
BATCHREVENUEAPPLICATIONPLEDGE.AMOUNT as 'BALANCE',
BATCHREVENUEAPPLICATION_NF.AMOUNTDUE,
BATCHREVENUEAPPLICATION_NF.DATEDUE,
1 as 'TYPECODE',
BATCHREVENUEAPPLICATION.RECEIPTAMOUNT as 'RECEIPTAMOUNT',
0 as 'DONOTRECEIPT',
'' as 'DESCRIPTION',
BATCHREVENUEAPPLICATION.WASGENERATED,
(select
ID,
CONSTITUENTID,
GIVENANONYMOUSLY,
FINDERNUMBER,
DATE,
AMOUNT,
SOURCECODE,
CHANNELCODEID,
APPEALID,
MAILINGID,
PLEDGESUBTYPEID,
BENEFITSWAIVED,
FREQUENCYCODE,
NUMBEROFINSTALLMENTS,
INSTALLMENTAMOUNT,
STARTDATE,
POSTDATE,
POSTSTATUSCODE,
SENDPLEDGEREMINDER,
DONOTACKNOWLEDGE,
(select
ID,
BATCHREVENUEAPPLICATIONPLEDGEID,
DESIGNATIONID,
AMOUNT,
0 as APPLICATIONCODE,
0 as TYPECODE,
DECLINESGIFTAID
from dbo.BATCHREVENUEAPPLICATIONPLEDGESPLIT
where BATCHREVENUEAPPLICATIONPLEDGESPLIT.BATCHREVENUEAPPLICATIONPLEDGEID = BATCHREVENUEAPPLICATIONPLEDGE.ID
for xml raw('ITEM'), type, elements, root('SPLITS'), binary base64
),
(select
ID,
BATCHREVENUEAPPLICATIONPLEDGEID,
AMOUNT,
RECEIPTAMOUNT,
DATE,
SEQUENCE,
(select
ID,
INSTALLMENTSPLITID,
DESIGNATIONID,
AMOUNT
from dbo.BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENTSPLIT
where BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENT.ID = BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENTSPLIT.BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENTID
for xml raw('ITEM'), type, elements, root('INSTALLMENTSPLITS'), binary base64
)
from dbo.BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENT
where BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENT.BATCHREVENUEAPPLICATIONPLEDGEID = BATCHREVENUEAPPLICATIONPLEDGE.ID
for xml raw('ITEM'), type, elements, root('INSTALLMENTS'), binary base64
),
(select
ID,
BATCHREVENUEAPPLICATIONPLEDGEID,
BENEFITID,
QUANTITY,
UNITVALUE,
DETAILS,
SEQUENCE
from dbo.BATCHREVENUEAPPLICATIONPLEDGEBENEFIT
where BATCHREVENUEAPPLICATIONPLEDGEBENEFIT.BATCHREVENUEAPPLICATIONPLEDGEID = BATCHREVENUEAPPLICATIONPLEDGE.ID
for xml raw('ITEM'), type, elements, root('BENEFITS'), binary base64
),
(select
ID,
BATCHREVENUEAPPLICATIONPLEDGEID,
BENEFITID,
PERCENTAPPLICABLEAMOUNT,
VALUEPERCENT,
DETAILS,
SEQUENCE
from dbo.BATCHREVENUEAPPLICATIONPLEDGEPERCENTAGEBENEFIT
where BATCHREVENUEAPPLICATIONPLEDGEPERCENTAGEBENEFIT.BATCHREVENUEAPPLICATIONPLEDGEID = BATCHREVENUEAPPLICATIONPLEDGE.ID
for xml raw('ITEM'), type, elements, root('PERCENTAGEBENEFITS'), binary base64
),
REFERENCE,
GLREVENUECATEGORYMAPPINGID CATEGORYCODEID,
OPPORTUNITYID,
PROSPECT_NF.NAME as OPPORTUNITYCONSTITUENTNAME,
OPPORTUNITY.ASKDATE as OPPORTUNITYASKDATE,
OPPORTUNITY.ASKAMOUNT as OPPORTUNITYASKAMOUNT,
BATCHREVENUEAPPLICATIONPLEDGE.BASEEXCHANGERATEID,
BATCHREVENUEAPPLICATIONPLEDGE.EXCHANGERATE
from dbo.BATCHREVENUEAPPLICATIONPLEDGE
where BATCHREVENUEAPPLICATIONPLEDGE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONPLEDGEID
for xml raw('ITEM'), type, elements, binary base64
) as 'PLEDGES',
null as 'MEMBERSHIPS',
null as 'BATCHREVENUEREGISTRANTID',
case
when BATCHREVENUEAPPLICATION.OVERPAYMENTAPPLICATIONTYPECODE = 255
then null
else BATCHREVENUEAPPLICATION.OVERPAYMENTAPPLICATIONTYPECODE
end as 'OVERPAYMENTAPPLICATIONTYPECODE',
(select top 1
dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST
from dbo.BATCHREVENUEAPPLICATIONPLEDGESPLIT
inner join dbo.DESIGNATION on BATCHREVENUEAPPLICATIONPLEDGESPLIT.DESIGNATIONID = DESIGNATION.ID
where BATCHREVENUEAPPLICATIONPLEDGESPLIT.BATCHREVENUEAPPLICATIONPLEDGEID = BATCHREVENUEAPPLICATIONPLEDGE.ID
) as 'DESIGNATIONLIST',
(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 as APPLICATIONCURRENCYID,
case
when BATCHREVENUEAPPLICATION.REVENUEID is null then
BATCHREVENUE.PDACCOUNTSYSTEMID
else
PDAS.PDACCOUNTSYSTEMID
end as PDACCOUNTSYSTEMID,
0 as SPONSORSHIPRGADDITIONALGIFT,
BATCHREVENUEAPPLICATIONPLEDGE.GLREVENUECATEGORYMAPPINGID as CATEGORYCODEID
from dbo.BATCHREVENUEAPPLICATION
inner join dbo.BATCHREVENUE on BATCHREVENUE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(BATCHREVENUE.CONSTITUENTID) CONSTITUENT_NF
left join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENT.ID = BATCHREVENUE.CONSTITUENTID
left join dbo.BATCHREVENUEAPPLICATIONPLEDGE on BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONPLEDGEID = BATCHREVENUEAPPLICATIONPLEDGE.ID
left join dbo.OPPORTUNITY on OPPORTUNITY.ID = BATCHREVENUEAPPLICATIONPLEDGE.OPPORTUNITYID
left outer join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PROSPECTID) PROSPECT_NF
left join dbo.PDACCOUNTSYSTEMFORREVENUE PDAS on BATCHREVENUEAPPLICATION.REVENUEID = PDAS.ID
outer apply
( select
top 1 sum(BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENT.AMOUNT) as 'AMOUNTDUE',BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENT.DATE as 'DATEDUE'
from dbo.BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENT
where BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENT.BATCHREVENUEAPPLICATIONPLEDGEID = BATCHREVENUEAPPLICATIONPLEDGE.ID
group by BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENT.DATE
order by BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENT.DATE) BATCHREVENUEAPPLICATION_NF
where BATCHREVENUEAPPLICATION.BATCHREVENUEID = @BATCHREVENUEID
and not BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONPLEDGEID is null
union all
--Applications to memberships created in this batch.
select
BATCHREVENUEAPPLICATION.ID as ID,
BATCHREVENUEAPPLICATIONMEMBERSHIP.MEMBERSHIPID as [APPLICATIONID],
BATCHREVENUE.CONSTITUENTID,
isnull(CONSTITUENT_NF.NAME, BATCHREVENUECONSTITUENT.NAME) as CONSTITUENTNAME,
BATCHREVENUEAPPLICATION.APPLIED,
MEMBERSHIPLEVELTERM.AMOUNT as BALANCE,
MEMBERSHIPLEVELTERM.AMOUNT as AMOUNTDUE,
BATCHREVENUEAPPLICATIONMEMBERSHIP.EXPIRATIONDATE 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,
null,
(select
ID,
MEMBERSHIPPROGRAMID,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
MEMBERSHIPLEVELTYPECODEID,
NUMBEROFCHILDREN,
COMMENTS,
ISGIFT,
SENDRENEWALCODE,
EXPIRATIONDATE,
GIVENBYID,
(
select
coalesce(MEMBERID, ID) as ID, --For backwards compat return the member ID in this column, we don't use the table's PK anyways
CONSTITUENTID,
ISPRIMARY,
dbo.UFN_REVENUEBATCH_GETMEMBERSHIPMEMBERMEMBERSHIPCARDS_TOITEMLISTXML(ID)
from dbo.BATCHREVENUEAPPLICATIONMEMBERSHIPMEMBER
where BATCHREVENUEAPPLICATIONMEMBERSHIPMEMBER.BATCHREVENUEAPPLICATIONMEMBERSHIPID = MEM.ID
for xml raw('ITEM'), type, elements, binary base64
) as MEMBERS
from dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP MEM
where MEM.ID = BATCHREVENUEAPPLICATIONMEMBERSHIP.ID
for xml raw('ITEM'), type, elements, binary base64
) as 'MEMBERSHIPS',
null as 'BATCHREVENUEREGISTRANTID',
case
when BATCHREVENUEAPPLICATION.OVERPAYMENTAPPLICATIONTYPECODE = 255
then null
else BATCHREVENUEAPPLICATION.OVERPAYMENTAPPLICATIONTYPECODE
end as 'OVERPAYMENTAPPLICATIONTYPECODE',
'' as 'DESIGNATIONLIST',
(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,
MEMBERSHIPLEVELTERM.BASECURRENCYID as APPLICATIONCURRENCYID,
PDAS.PDACCOUNTSYSTEMID,
0 as SPONSORSHIPRGADDITIONALGIFT,
null as CATEGORYCODEID
from
dbo.BATCHREVENUEAPPLICATION
inner join dbo.BATCHREVENUE on BATCHREVENUE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(BATCHREVENUE.CONSTITUENTID) CONSTITUENT_NF
left join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENT.ID = BATCHREVENUE.CONSTITUENTID
left join dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP on BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONMEMBERSHIPID = BATCHREVENUEAPPLICATIONMEMBERSHIP.ID
left join dbo.MEMBERSHIPLEVELTERM on BATCHREVENUEAPPLICATIONMEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
left join dbo.PDACCOUNTSYSTEMFORREVENUE PDAS on BATCHREVENUEAPPLICATION.REVENUEID = PDAS.ID
where BATCHREVENUEAPPLICATION.BATCHREVENUEID = @BATCHREVENUEID
and not BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONMEMBERSHIPID is null
union all
--Applications to commitments already in the system or non-pledge commitments created in batch.
select
ID,
APPLICATIONID,
CONSTITUENTID,
CONSTITUENTNAME,
APPLIED,
BALANCE,
AMOUNTDUE,
DATEDUE,
TYPECODE,
RECEIPTAMOUNT,
DONOTRECEIPT,
[DESCRIPTION],
WASGENERATED,
null,
null,
BATCHREVENUEREGISTRANTID,
OVERPAYMENTAPPLICATIONTYPECODE,
DESIGNATIONLIST,
SPONSORSHIPOPPORTUNITY,
REVENUESPLITID,
APPLICATIONCURRENCYID,
PDACCOUNTSYSTEMID,
SPONSORSHIPRGADDITIONALGIFT,
CATEGORYCODEID
from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS(@BATCHREVENUEID) BATCHREVENUEAPPLICATIONS
where (BATCHREVENUEAPPLICATIONS.TYPECODE <> 5)