USP_RPT_RECOGNITIONGIVINGLISTDETAIL
Definition
Copy
CREATE procedure BBDW.[USP_RPT_RECOGNITIONGIVINGLISTDETAIL] as
--#CONSTITUENCIES
create table #CONSTITUENCIES ([CONSTITUENCY] nvarchar(100), [CONSTITUENTDIMID] int);
insert into #CONSTITUENCIES ([CONSTITUENCY], [CONSTITUENTDIMID])
select
DC.[CONSTITUENCY],
FC.[CONSTITUENTDIMID]
from BBDW.[FACT_CONSTITUENCY] FC
inner join BBDW.[DIM_CONSTITUENCY] DC on FC.[CONSTITUENCYDIMID] = DC.[CONSTITUENCYDIMID];
insert into BBDW.[RPT_RECOGNITIONGIVINGLISTDETAIL] (
[REVENUERECOGNITIONSYSTEMID],
[FINANCIALTRANSACTIONSYSTEMID],
[CONSTITUENTLOOKUPID],
[CONSTITUENTSYSTEMID],
[NAME],
[PRIMARYEMAILADDRESS],
[PRIMARYPHONE],
[DONOTEMAIL],
[DONOTCALL],
[PRIMARYADDRESS],
[CITYSTATEZIP],
[PRIMARYADDRESSCITY],
[PRIMARYADDRESSSTATEABBREVIATION],
[PRIMARYADDRESSPOSTCODE],
[GIVENANONYMOUSLY],
[ISORGANIZATION],
[ISCONSTITUENT],
[ISGROUP],
[ISACTIVE],
[ISDECEASED],
[FINANCIALTRANSACTIONDATE],
[REVENUERECOGNITIONDATE],
[TRANAPPLICATION],
[CAMPAIGNS],
[APPEALNAME],
[DESIGNATIONLOOKUPID],
[DESIGNATIONNAME],
[DESIGNATIONREPORT1CODE],
[DESIGNATIONREPORT2CODE],
[ISO4217],
[FINANCIALTRANSACTIONLINEITEMBASEAMOUNT],
[REVENUERECOGNITIONBASEAMOUNT],
[BASECURRENCYSYSTEMID],
[PROSPECTMANAGERFULLNAME],
[CONSTITUENCY],
[SOLICITORS],
[SITES],
[REVENUECHANNELDESCRIPTION],
[REVENUEPAYMENTMETHOD],
[REVENUEAPPLICATION],
[REVENUEAPPLICATIONCODE],
[GLREVENUECATEGORYNAME],
[RECOGNITIONTYPE],
[FINANCIALTRANSACTIONLINEITEMSYSTEMID]
)
select
RR.[REVENUERECOGNITIONSYSTEMID],
LI.[FINANCIALTRANSACTIONSYSTEMID],
C.[CONSTITUENTLOOKUPID],
LI.[CONSTITUENTSYSTEMID],
case
when C.[CONSTITUENTDIMID] = 0 then ''
else
case
when C.[ISCONSTITUENT] = 1 and C.[ISGROUP] = 0 and C.[ISORGANIZATION] = 0 then
case
when (nullif(C.[FIRSTNAME], '') is null) and (nullif(C.[MIDDLENAME], '') is null) then C.[KEYNAME]
when (nullif(C.[FIRSTNAME], '') is null) and not (nullif(C.[MIDDLENAME], '') is null) then C.[KEYNAME] + ', ' + C.[MIDDLENAME]
when not (nullif(C.[FIRSTNAME], '') is null) and (nullif(C.[MIDDLENAME], '') is null) then C.[KEYNAME] + ', ' + C.[FIRSTNAME]
else C.[KEYNAME] + ', ' + C.[FIRSTNAME] + ' ' + C.[MIDDLENAME]
end
else
C.[FULLNAME]
end
end as [NAME],
case when C.[CONSTITUENTDIMID] = 0 then '' else C.[PRIMARYEMAILADDRESS] end [PRIMARYEMAILADDRESS],
case when C.[CONSTITUENTDIMID] = 0 then '' else C.[PRIMARYPHONE] end [PRIMARYPHONE],
case when C.[CONSTITUENTDIMID] = 0 then '' else C.[DONOTEMAIL] end [DONOTEMAIL],
case when C.[CONSTITUENTDIMID] = 0 then '' else C.[DONOTCALL] end [DONOTCALL],
case when C.[CONSTITUENTDIMID] = 0 then '' else C.[PRIMARYADDRESS] end [PRIMARYADDRESS],
case when C.[CONSTITUENTDIMID] = 0 then '' else isnull(C.[PRIMARYADDRESSCITY] + ', ' + isnull(C.[PRIMARYADDRESSSTATEABBREVIATION], '') + isnull(C.[PRIMARYADDRESSPOSTCODE], ''), '') end [CITYSTATEZIP],
case when C.[CONSTITUENTDIMID] = 0 then '' else C.[PRIMARYADDRESSCITY] end [PRIMARYADDRESSCITY],
case when C.[CONSTITUENTDIMID] = 0 then '' else C.[PRIMARYADDRESSSTATEABBREVIATION] end [PRIMARYADDRESSSTATEABBREVIATION],
case when C.[CONSTITUENTDIMID] = 0 then '' else C.[PRIMARYADDRESSPOSTCODE] end [PRIMARYADDRESSPOSTCODE],
case when REVENUEFLAG.[GIVENANONYMOUSLY] = 0 then 0 else 1 end [GIVENANONYMOUSLY],
C.[ISORGANIZATION],
C.[ISCONSTITUENT],
C.[ISGROUP],
C.[ISACTIVE],
C.[ISDECEASED],
convert(date,FT.[FINANCIALTRANSACTIONDATE]) [FINANCIALTRANSACTIONDATE],
convert(date,RR.[EFFECTIVEDATE]) [REVENUERECOGNITIONDATE],
REVENUECODE.[REVENUETRANSACTIONTYPE] + ' -> ' + REVENUECODE.[REVENUEAPPLICATION] [TRANAPPLICATION],
isnull(gc.[CAMPAIGNS], '') [CAMPAIGNS],
case when LI.[APPEALDIMID] = 0 then '' else APPEAL.[APPEALNAME] end as [APPEALNAME],
case when D.[DESIGNATIONDIMID] = 0 then '' else D.[DESIGNATIONLOOKUPID] end as [DESIGNATIONLOOKUPID],
case when D.[DESIGNATIONDIMID] = 0 then '' else D.[DESIGNATIONNAME] end as [DESIGNATIONNAME],
case when D.[DESIGNATIONDIMID] = 0 then '' else D.[DESIGNATIONREPORT1CODE] end as [DESIGNATIONREPORT1CODE],
case when D.[DESIGNATIONDIMID] = 0 then '' else D.[DESIGNATIONREPORT2CODE] end as [DESIGNATIONREPORT2CODE],
BASECURRENCY.[ISO4217] as [ISO4217],
LI.[FINANCIALTRANSACTIONLINEITEMBASEAMOUNT],
RR.[BASEREVENUERECOGNITIONAMOUNT],
BASECURRENCY.[CURRENCYSYSTEMID] as [BASECURRENCYSYSTEMID],
case
when PROSPECTMANAGER.[CONSTITUENTDIMID] = 0 then ''
else
case
when PROSPECTMANAGER.[ISCONSTITUENT] = 1 and PROSPECTMANAGER.[ISGROUP] = 0 and PROSPECTMANAGER.[ISORGANIZATION] = 0 then
case
when (nullif(PROSPECTMANAGER.[FIRSTNAME], '') is null) and (nullif(PROSPECTMANAGER.[MIDDLENAME], '') is null) then PROSPECTMANAGER.[KEYNAME]
when (nullif(PROSPECTMANAGER.[FIRSTNAME], '') is null) and not (nullif(PROSPECTMANAGER.[MIDDLENAME], '') is null) then PROSPECTMANAGER.[KEYNAME] + ', ' + PROSPECTMANAGER.[MIDDLENAME]
when not (nullif(PROSPECTMANAGER.[FIRSTNAME], '') is null) and (nullif(PROSPECTMANAGER.[MIDDLENAME], '') is null) then PROSPECTMANAGER.[KEYNAME] + ', ' + PROSPECTMANAGER.[FIRSTNAME]
else PROSPECTMANAGER.[KEYNAME] + ', ' + PROSPECTMANAGER.[FIRSTNAME] + ' ' + PROSPECTMANAGER.[MIDDLENAME]
end
else
PROSPECTMANAGER.[FULLNAME]
end
end as [PROSPECTMANAGERFULLNAME],
left(isnull(case
when C.[CONSTITUENTDIMID] = 0 then '' else
stuff((
select ', ' + C.[CONSTITUENCY]
from #CONSTITUENCIES as C
where C.[CONSTITUENTDIMID] = LI.[CONSTITUENTDIMID]
for xml path ('')
), 1, 2, '')
end, ''),4000) [CONSTITUENCY]
,isnull(gf.[SOLICITORS], '') [SOLICITORS]
,isnull(gs.[SITES], '') [SITES],
CHANNEL.[REVENUECHANNELDESCRIPTION],
REVENUECODE.[REVENUEPAYMENTMETHOD],
REVENUECODE.[REVENUEAPPLICATION],
REVENUECODE.[REVENUEAPPLICATIONCODE],
case when GLRC.[GLREVENUECATEGORYDIMID] = 0 then '' else GLRC.[GLREVENUECATEGORYNAME] end [GLREVENUECATEGORYNAME],
REVENUERECOGNITIONTYPE.[REVENUERECOGNITIONTYPE],
LI.[FINANCIALTRANSACTIONLINEITEMSYSTEMID]
from BBDW.[FACT_REVENUERECOGNITION] RR
inner join BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM] LI on RR.[FINANCIALTRANSACTIONLINEITEMFACTID] = LI.[FINANCIALTRANSACTIONLINEITEMFACTID]
inner join BBDW.[FACT_FINANCIALTRANSACTION] FT on FT.[FINANCIALTRANSACTIONFACTID] = LI.[FINANCIALTRANSACTIONFACTID]
inner join BBDW.[DIM_REVENUEFLAG] REVENUEFLAG on REVENUEFLAG.[REVENUEFLAGDIMID] = LI.[REVENUEFLAGDIMID]
inner join BBDW.[DIM_REVENUECHANNEL] CHANNEL on CHANNEL.[REVENUECHANNELDIMID] = FT.[REVENUECHANNELDIMID]
inner join BBDW.[DIM_CONSTITUENT] C on C.[CONSTITUENTDIMID] = RR.[CONSTITUENTDIMID]
inner join BBDW.[DIM_REVENUECODE] REVENUECODE on REVENUECODE.[REVENUECODEDIMID] = LI.[REVENUECODEDIMID]
inner join BBDW.[DIM_DESIGNATION] D on D.[DESIGNATIONDIMID] = LI.[DESIGNATIONDIMID]
inner join BBDW.[DIM_PURPOSELEVEL] PURPOSELEVEL on PURPOSELEVEL.[PURPOSELEVELDIMID] = coalesce(nullif(D.[PURPOSELEVEL5DIMID], 0), nullif(D.[PURPOSELEVEL4DIMID], 0), nullif(D.[PURPOSELEVEL3DIMID], 0), nullif(D.PURPOSELEVEL2DIMID, 0), D.[PURPOSELEVEL1DIMID])
inner join BBDW.[DIM_APPEAL] APPEAL on APPEAL.[APPEALDIMID] = LI.[APPEALDIMID]
inner join BBDW.[DIM_CURRENCY] BASECURRENCY on BASECURRENCY.[CURRENCYDIMID] = LI.[BASECURRENCYDIMID]
inner join BBDW.[DIM_GLREVENUECATEGORY] GLRC on GLRC.[GLREVENUECATEGORYDIMID] = LI.[GLREVENUECATEGORYDIMID]
inner join BBDW.[DIM_REVENUERECOGNITIONTYPE] REVENUERECOGNITIONTYPE on REVENUERECOGNITIONTYPE.[REVENUERECOGNITIONTYPEDIMID] = RR.[REVENUERECOGNITIONTYPEDIMID]
left join BBDW.[RPT_GIVINGLISTDETAIL_CAMPAIGNS] gc on LI.[FINANCIALTRANSACTIONLINEITEMFACTID] = gc.[FINANCIALTRANSACTIONLINEITEMFACTID]
left join BBDW.[RPT_GIVINGLISTDETAIL_SOLICITORS] gf on LI.[FINANCIALTRANSACTIONLINEITEMFACTID] = gf.[FINANCIALTRANSACTIONLINEITEMFACTID]
left join BBDW.[RPT_GIVINGLISTDETAIL_SITES] gs on LI.[FINANCIALTRANSACTIONLINEITEMFACTID] = gs.[FINANCIALTRANSACTIONLINEITEMFACTID]
left join BBDW.[DIM_PROSPECT] PROSPECT on PROSPECT.[CONSTITUENTDIMID] = C.[CONSTITUENTDIMID]
left join BBDW.[DIM_CONSTITUENT] PROSPECTMANAGER on PROSPECTMANAGER.[CONSTITUENTDIMID] = PROSPECT.[FUNDRAISERDIMID]
where
REVENUECODE.[REVENUETRANSACTIONTYPECODE] in (0,1,3,4,6,7,8,9)
and REVENUECODE.[REVENUEAPPLICATIONCODE] != -1
and REVENUEFLAG.[ISDELETEDFINANCIALTRANSACTIONLINEITEM] = 0;
drop table #CONSTITUENCIES;