UFN_REVENUEBATCH_GETAPPLICATIONSOLICITORS
Returns solicitors for a revenue batch ID grouped by application.
Return
| Return Type |
|---|
| xml |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @BATCHREVENUEID | uniqueidentifier | IN | |
| @USESPLITS | bit | IN |
Definition
Copy
CREATE function dbo.UFN_REVENUEBATCH_GETAPPLICATIONSOLICITORS
(
@BATCHREVENUEID uniqueidentifier,
@USESPLITS bit
)
returns xml
as
begin
declare @APPLICATIONSOLICITORS xml
declare @APPLICATIONSOLICITORSTABLE table
(
ADDITIONALAPPLICATIONTYPECODE tinyint,
ADDITIONALAPPLICATIONDESIGNATIONID uniqueidentifier,
ADDITIONALAPPLICATIONDECLINESGIFTAID bit,
ADDITIONALAPPLICATIONSPONSORSHIPID uniqueidentifier
)
declare @MAINOPPORTUNITYID uniqueidentifier
insert into @APPLICATIONSOLICITORSTABLE(ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, ADDITIONALAPPLICATIONDECLINESGIFTAID, ADDITIONALAPPLICATIONSPONSORSHIPID)
select distinct ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, ADDITIONALAPPLICATIONDECLINESGIFTAID, ADDITIONALAPPLICATIONSPONSORSHIPID
from dbo.BATCHREVENUESOLICITOR
where BATCHREVENUEID = @BATCHREVENUEID
select @MAINOPPORTUNITYID = OPPORTUNITYID from dbo.BATCHREVENUE where BATCHREVENUE.ID = @BATCHREVENUEID
if @USESPLITS = 1
set @APPLICATIONSOLICITORS = ( select
ADDITIONALAPPLICATIONTYPECODE,
ADDITIONALAPPLICATIONDESIGNATIONID,
BATCHREVENUESPLIT.AMOUNT as APPLICATIONAMOUNT,
'' as APPLICATIONDESCRIPTION,
dbo.UFN_REVENUEBATCH_GETCOLLECTIONDESCRIPTION_3(AST.ADDITIONALAPPLICATIONTYPECODE, AST.ADDITIONALAPPLICATIONDESIGNATIONID, @BATCHREVENUEID, 0, AST.ADDITIONALAPPLICATIONDECLINESGIFTAID, AST.ADDITIONALAPPLICATIONSPONSORSHIPID) as COLLECTIONDESCRIPTION,
( select
AMOUNT,
CONSTITUENTID,
ID,
SEQUENCE,
REVENUESOLICITORID
from dbo.BATCHREVENUESOLICITOR
where BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONTYPECODE = AST.ADDITIONALAPPLICATIONTYPECODE
and BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONDESIGNATIONID = AST.ADDITIONALAPPLICATIONDESIGNATIONID
and BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONDECLINESGIFTAID = AST.ADDITIONALAPPLICATIONDECLINESGIFTAID
and coalesce(BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(AST.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000')
and BATCHREVENUESOLICITOR.BATCHREVENUEID = @BATCHREVENUEID
for xml raw('ITEM'),type,elements,BINARY BASE64
) as SOLICITORS,
@MAINOPPORTUNITYID as ADDITIONALAPPLICATIONOPPORTUNITYID,
ADDITIONALAPPLICATIONDECLINESGIFTAID,
ADDITIONALAPPLICATIONSPONSORSHIPID
from @APPLICATIONSOLICITORSTABLE AST
inner join dbo.DESIGNATION on AST.ADDITIONALAPPLICATIONDESIGNATIONID = DESIGNATION.ID
inner join dbo.BATCHREVENUESPLIT on
BATCHREVENUESPLIT.DESIGNATIONID = AST.ADDITIONALAPPLICATIONDESIGNATIONID
and BATCHREVENUESPLIT.DECLINESGIFTAID = AST.ADDITIONALAPPLICATIONDECLINESGIFTAID
and BATCHREVENUEID = @BATCHREVENUEID
for xml raw('ITEM'),type,elements,root('APPLICATIONSOLICITORS'),BINARY BASE64)
else
set @APPLICATIONSOLICITORS = ( select
ADDITIONALAPPLICATIONTYPECODE,
ADDITIONALAPPLICATIONDESIGNATIONID,
BATCHREVENUEADDITIONALAPPLICATIONS.APPLIED as APPLICATIONAMOUNT,
'' as APPLICATIONDESCRIPTION,
dbo.UFN_REVENUEBATCH_GETCOLLECTIONDESCRIPTION_3(AST.ADDITIONALAPPLICATIONTYPECODE, AST.ADDITIONALAPPLICATIONDESIGNATIONID, @BATCHREVENUEID, 0, AST.ADDITIONALAPPLICATIONDECLINESGIFTAID, AST.ADDITIONALAPPLICATIONSPONSORSHIPID) as COLLECTIONDESCRIPTION,
( select
AMOUNT,
CONSTITUENTID,
ID,
SEQUENCE,
REVENUESOLICITORID
from dbo.BATCHREVENUESOLICITOR
where
BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONTYPECODE = AST.ADDITIONALAPPLICATIONTYPECODE
and BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONDESIGNATIONID = AST.ADDITIONALAPPLICATIONDESIGNATIONID
and BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONDECLINESGIFTAID = AST.ADDITIONALAPPLICATIONDECLINESGIFTAID
and coalesce(BATCHREVENUESOLICITOR.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(AST.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000')
and BATCHREVENUESOLICITOR.BATCHREVENUEID = @BATCHREVENUEID
for xml raw('ITEM'),type,elements,BINARY BASE64
) as SOLICITORS,
BATCHREVENUEADDITIONALAPPLICATIONS.OPPORTUNITYID as ADDITIONALAPPLICATIONOPPORTUNITYID,
ADDITIONALAPPLICATIONDECLINESGIFTAID,
ADDITIONALAPPLICATIONSPONSORSHIPID
from @APPLICATIONSOLICITORSTABLE AST
inner join dbo.DESIGNATION on AST.ADDITIONALAPPLICATIONDESIGNATIONID = DESIGNATION.ID
inner join dbo.BATCHREVENUEADDITIONALAPPLICATIONS on
BATCHREVENUEADDITIONALAPPLICATIONS.DESIGNATIONID = AST.ADDITIONALAPPLICATIONDESIGNATIONID
and BATCHREVENUEID = @BATCHREVENUEID
and BATCHREVENUEADDITIONALAPPLICATIONS.TYPECODE = AST.ADDITIONALAPPLICATIONTYPECODE
and BATCHREVENUEADDITIONALAPPLICATIONS.DECLINESGIFTAID = AST.ADDITIONALAPPLICATIONDECLINESGIFTAID
and coalesce(BATCHREVENUEADDITIONALAPPLICATIONS.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(AST.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000')
for xml raw('ITEM'),type,elements,root('APPLICATIONSOLICITORS'),BINARY BASE64
)
return @APPLICATIONSOLICITORS
end