UFN_REVENUEBATCH_GETAPPLICATIONRECOGNITIONS
Returns recognitions 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_GETAPPLICATIONRECOGNITIONS
(
@BATCHREVENUEID uniqueidentifier,
@USESPLITS bit
)
returns xml
as
begin
declare @APPLICATIONRECOGNITIONS xml
declare @APPLICATIONRECOGNITIONSTABLE table
(
ADDITIONALAPPLICATIONTYPECODE tinyint,
ADDITIONALAPPLICATIONDESIGNATIONID uniqueidentifier,
ADDITIONALAPPLICATIONDECLINESGIFTAID bit,
ADDITIONALAPPLICATIONSPONSORSHIPID uniqueidentifier,
APPLICATIONID uniqueidentifier
)
insert into @APPLICATIONRECOGNITIONSTABLE
(
ADDITIONALAPPLICATIONTYPECODE,
ADDITIONALAPPLICATIONDESIGNATIONID,
ADDITIONALAPPLICATIONDECLINESGIFTAID,
ADDITIONALAPPLICATIONSPONSORSHIPID,
APPLICATIONID
)
select distinct
ADDITIONALAPPLICATIONTYPECODE,
ADDITIONALAPPLICATIONDESIGNATIONID,
ADDITIONALAPPLICATIONDECLINESGIFTAID,
ADDITIONALAPPLICATIONSPONSORSHIPID,
APPLICATIONID
from dbo.BATCHREVENUERECOGNITION
where BATCHREVENUEID = @BATCHREVENUEID
if @USESPLITS = 1
set @APPLICATIONRECOGNITIONS = ( select
NULL as APPLICATIONID,
ADDITIONALAPPLICATIONTYPECODE,
ADDITIONALAPPLICATIONDESIGNATIONID,
BATCHREVENUESPLIT.AMOUNT as APPLICATIONAMOUNT,
'' as APPLICATIONDESCRIPTION,
dbo.UFN_REVENUEBATCH_GETCOLLECTIONDESCRIPTION_3(ART.ADDITIONALAPPLICATIONTYPECODE, ART.ADDITIONALAPPLICATIONDESIGNATIONID, @BATCHREVENUEID, 1, ART.ADDITIONALAPPLICATIONDECLINESGIFTAID, ART.ADDITIONALAPPLICATIONSPONSORSHIPID) as COLLECTIONDESCRIPTION,
( select
AMOUNT,
CONSTITUENTID,
EFFECTIVEDATE,
BATCHREVENUERECOGNITION.ID,
REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONTYPE,
REVENUERECOGNITIONTYPECODEID,
REVENUERECOGNITIONID
from dbo.BATCHREVENUERECOGNITION
left join dbo.REVENUERECOGNITIONTYPECODE on BATCHREVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
where
BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONTYPECODE = ART.ADDITIONALAPPLICATIONTYPECODE
and BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONDESIGNATIONID = ART.ADDITIONALAPPLICATIONDESIGNATIONID
and BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONDECLINESGIFTAID = ART.ADDITIONALAPPLICATIONDECLINESGIFTAID
and coalesce(BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(ART.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000')
and BATCHREVENUERECOGNITION.BATCHREVENUEID = @BATCHREVENUEID
for xml raw('ITEM'),type,elements,BINARY BASE64
) as RECOGNITIONS,
ADDITIONALAPPLICATIONDECLINESGIFTAID,
ADDITIONALAPPLICATIONSPONSORSHIPID
from @APPLICATIONRECOGNITIONSTABLE ART
inner join dbo.DESIGNATION on ART.ADDITIONALAPPLICATIONDESIGNATIONID = DESIGNATION.ID
inner join dbo.BATCHREVENUESPLIT on
BATCHREVENUESPLIT.DESIGNATIONID = ART.ADDITIONALAPPLICATIONDESIGNATIONID
and BATCHREVENUESPLIT.DECLINESGIFTAID = ART.ADDITIONALAPPLICATIONDECLINESGIFTAID
--and BATCHREVENUESPLIT.SPONSORSHIPID = ART.ADDITIONALAPPLICATIONSPONSORSHIPID
and BATCHREVENUEID = @BATCHREVENUEID
for xml raw('ITEM'),type,elements,root('APPLICATIONRECOGNITIONS'),BINARY BASE64)
else
set @APPLICATIONRECOGNITIONS = (
select
APPLICATIONID,
ADDITIONALAPPLICATIONTYPECODE,
ADDITIONALAPPLICATIONDESIGNATIONID,
APPLICATIONAMOUNT,
APPLICATIONDESCRIPTION,
COLLECTIONDESCRIPTION,
RECOGNITIONS,
ADDITIONALAPPLICATIONDECLINESGIFTAID,
ADDITIONALAPPLICATIONSPONSORSHIPID
from (
select
NULL as APPLICATIONID,
ADDITIONALAPPLICATIONTYPECODE,
ADDITIONALAPPLICATIONDESIGNATIONID,
BATCHREVENUEADDITIONALAPPLICATIONS.APPLIED as APPLICATIONAMOUNT,
'' as APPLICATIONDESCRIPTION,
dbo.UFN_REVENUEBATCH_GETCOLLECTIONDESCRIPTION_3(ART.ADDITIONALAPPLICATIONTYPECODE, ART.ADDITIONALAPPLICATIONDESIGNATIONID, @BATCHREVENUEID, 1, ART.ADDITIONALAPPLICATIONDECLINESGIFTAID, ART.ADDITIONALAPPLICATIONSPONSORSHIPID) as COLLECTIONDESCRIPTION,
( select
AMOUNT,
CONSTITUENTID,
EFFECTIVEDATE,
BATCHREVENUERECOGNITION.ID,
REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONTYPE,
REVENUERECOGNITIONTYPECODEID,
REVENUERECOGNITIONID
from dbo.BATCHREVENUERECOGNITION
left join dbo.REVENUERECOGNITIONTYPECODE on BATCHREVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
where
BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONTYPECODE = ART.ADDITIONALAPPLICATIONTYPECODE
and BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONDESIGNATIONID = ART.ADDITIONALAPPLICATIONDESIGNATIONID
and BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONDECLINESGIFTAID = ART.ADDITIONALAPPLICATIONDECLINESGIFTAID
and coalesce(BATCHREVENUERECOGNITION.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(ART.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000')
and BATCHREVENUERECOGNITION.BATCHREVENUEID = @BATCHREVENUEID
for xml raw('ITEM'),type,elements,BINARY BASE64
) as RECOGNITIONS,
ADDITIONALAPPLICATIONDECLINESGIFTAID,
ADDITIONALAPPLICATIONSPONSORSHIPID
from @APPLICATIONRECOGNITIONSTABLE ART
inner join dbo.DESIGNATION on ART.ADDITIONALAPPLICATIONDESIGNATIONID = DESIGNATION.ID
inner join dbo.BATCHREVENUEADDITIONALAPPLICATIONS on
BATCHREVENUEADDITIONALAPPLICATIONS.DESIGNATIONID = ART.ADDITIONALAPPLICATIONDESIGNATIONID
and BATCHREVENUEID = @BATCHREVENUEID
and BATCHREVENUEADDITIONALAPPLICATIONS.TYPECODE = ART.ADDITIONALAPPLICATIONTYPECODE
and BATCHREVENUEADDITIONALAPPLICATIONS.DECLINESGIFTAID = ART.ADDITIONALAPPLICATIONDECLINESGIFTAID
and coalesce(BATCHREVENUEADDITIONALAPPLICATIONS.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(ART.ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000')
union all
select
ART.APPLICATIONID,
ADDITIONALAPPLICATIONTYPECODE,
ADDITIONALAPPLICATIONDESIGNATIONID,
BATCHREVENUEAPPLICATION.APPLIED as APPLICATIONAMOUNT,
'' as APPLICATIONDESCRIPTION,
dbo.UFN_REVENUEBATCH_GETCOLLECTIONDESCRIPTION_3(ART.ADDITIONALAPPLICATIONTYPECODE, ART.ADDITIONALAPPLICATIONDESIGNATIONID, @BATCHREVENUEID, 1, ART.ADDITIONALAPPLICATIONDECLINESGIFTAID, ART.ADDITIONALAPPLICATIONSPONSORSHIPID) as COLLECTIONDESCRIPTION,
( select
AMOUNT,
CONSTITUENTID,
EFFECTIVEDATE,
BATCHREVENUERECOGNITION.ID,
REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONTYPE,
REVENUERECOGNITIONTYPECODEID,
REVENUERECOGNITIONID
from dbo.BATCHREVENUERECOGNITION
left join dbo.REVENUERECOGNITIONTYPECODE on BATCHREVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
where
BATCHREVENUERECOGNITION.APPLICATIONID = ART.APPLICATIONID
and BATCHREVENUERECOGNITION.BATCHREVENUEID = @BATCHREVENUEID
for xml raw('ITEM'),type,elements,BINARY BASE64
) as RECOGNITIONS,
ADDITIONALAPPLICATIONDECLINESGIFTAID,
ADDITIONALAPPLICATIONSPONSORSHIPID
from @APPLICATIONRECOGNITIONSTABLE ART
inner join dbo.BATCHREVENUEAPPLICATION on
coalesce(BATCHREVENUEAPPLICATION.REVENUESPLITID, BATCHREVENUEAPPLICATION.REVENUEID, BATCHREVENUEAPPLICATION.REGISTRANTID) = ART.APPLICATIONID
and BATCHREVENUEID = @BATCHREVENUEID
union all
select
ART.APPLICATIONID,
ADDITIONALAPPLICATIONTYPECODE,
ADDITIONALAPPLICATIONDESIGNATIONID,
BATCHREVENUEAPPLICATION.APPLIED as APPLICATIONAMOUNT,
'' as APPLICATIONDESCRIPTION,
dbo.UFN_REVENUEBATCH_GETCOLLECTIONDESCRIPTION_3(ART.ADDITIONALAPPLICATIONTYPECODE, ART.ADDITIONALAPPLICATIONDESIGNATIONID, @BATCHREVENUEID, 1, ART.ADDITIONALAPPLICATIONDECLINESGIFTAID, ART.ADDITIONALAPPLICATIONSPONSORSHIPID) as COLLECTIONDESCRIPTION,
( select
AMOUNT,
CONSTITUENTID,
EFFECTIVEDATE,
BATCHREVENUERECOGNITION.ID,
REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONTYPE,
REVENUERECOGNITIONTYPECODEID,
REVENUERECOGNITIONID
from dbo.BATCHREVENUERECOGNITION
left join dbo.REVENUERECOGNITIONTYPECODE on BATCHREVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
where
BATCHREVENUERECOGNITION.APPLICATIONID = ART.APPLICATIONID
and BATCHREVENUERECOGNITION.BATCHREVENUEID = @BATCHREVENUEID
for xml raw('ITEM'),type,elements,BINARY BASE64
) as RECOGNITIONS,
ADDITIONALAPPLICATIONDECLINESGIFTAID,
ADDITIONALAPPLICATIONSPONSORSHIPID
from @APPLICATIONRECOGNITIONSTABLE ART
inner join
dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATION.BATCHREVENUEID = @BATCHREVENUEID and BATCHREVENUEAPPLICATION.APPLICATIONTYPECODE = 2
where
ART.APPLICATIONID = '9B9C1DC8-7960-4D31-A0BC-8199AB7F94DA'
union all
select
ART.APPLICATIONID,
ADDITIONALAPPLICATIONTYPECODE,
ADDITIONALAPPLICATIONDESIGNATIONID,
BATCHREVENUEAPPLICATION.APPLIED as APPLICATIONAMOUNT,
'' as APPLICATIONDESCRIPTION,
dbo.UFN_REVENUEBATCH_GETCOLLECTIONDESCRIPTION_3(ART.ADDITIONALAPPLICATIONTYPECODE, ART.ADDITIONALAPPLICATIONDESIGNATIONID, @BATCHREVENUEID, 1, ART.ADDITIONALAPPLICATIONDECLINESGIFTAID, ART.ADDITIONALAPPLICATIONSPONSORSHIPID) as COLLECTIONDESCRIPTION,
( select
AMOUNT,
CONSTITUENTID,
EFFECTIVEDATE,
BATCHREVENUERECOGNITION.ID,
REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONTYPE,
REVENUERECOGNITIONTYPECODEID,
REVENUERECOGNITIONID
from dbo.BATCHREVENUERECOGNITION
left join dbo.REVENUERECOGNITIONTYPECODE on BATCHREVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
where
BATCHREVENUERECOGNITION.APPLICATIONID = ART.APPLICATIONID
and BATCHREVENUERECOGNITION.BATCHREVENUEID = @BATCHREVENUEID
for xml raw('ITEM'),type,elements,BINARY BASE64
) as RECOGNITIONS,
ADDITIONALAPPLICATIONDECLINESGIFTAID,
ADDITIONALAPPLICATIONSPONSORSHIPID
from @APPLICATIONRECOGNITIONSTABLE ART
inner join
dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID = ART.APPLICATIONID
where
BATCHREVENUEAPPLICATION.BATCHREVENUEID = @BATCHREVENUEID and
BATCHREVENUEAPPLICATION.APPLICATIONTYPECODE = 1
) as RECOGNITIONSSET
for xml raw('ITEM'),type,elements,root('APPLICATIONRECOGNITIONS'),BINARY BASE64)
return @APPLICATIONRECOGNITIONS
end