USP_DATALIST_R68ONLINESUBMISSIONDATAFORPROCESS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONTEXTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_R68ONLINESUBMISSIONDATAFORPROCESS
(
@CONTEXTID uniqueidentifier
)
with execute as owner
as
set nocount on;
declare @TABLENAME nvarchar(255);
declare @ISSPONSORSHIP bit = 0;
declare @SQL nvarchar(4000);
select
@TABLENAME = TABLENAME
from dbo.BUSINESSPROCESSOUTPUT
where BUSINESSPROCESSSTATUSID = @CONTEXTID
and TABLEKEY = 'R68_OUTPUT';
select @ISSPONSORSHIP = R68.RUNGIFTAIDSPONSORSHIPSONLY
from dbo.R68ONLINESUBMISSIONPARAMETERS
inner join dbo.R68 on R68ONLINESUBMISSIONPARAMETERS.R68PROCESSID = R68.ID
where R68ONLINESUBMISSIONPARAMETERS.ID = @CONTEXTID
if @ISSPONSORSHIP = 0
begin
set @SQL = 'select '
--determine whether there is an ALIAS
--for KEYNAME
+ 'case '
--when the tax declaration specifies an alias to use
+ 'when ALIASES.ID is not null
then cast(ALIASES.KEYNAME as nvarchar(35)) '
--when there is no alias specified use the constituent name as before
+ 'else
cast(CONSTITUENT.KEYNAME as nvarchar(35))
end as KEYNAME, '
--for FIRSTNAME
+ 'case '
--when the tax declaration specifies an alias to use
+ 'when ALIASES.ID is not null
then cast(ALIASES.FIRSTNAME as nvarchar(35)) '
--when there is no alias specified use the constituent name as before
+ 'else
cast(CONSTITUENT.FIRSTNAME as nvarchar(35))
end as FIRSTNAME, '
--determine the address to use
+ 'case '
--when the tax declaration specifies an address to use
+ 'when SPECIFICADDRESS.ID is not null and SPECIFICCOUNTRY.ISO3166 = ''GB''
then cast(dbo.UFN_ADDRESS_GETADDRESSLINE(1, SPECIFICADDRESS.ADDRESSBLOCK) as nvarchar(50))
when SPECIFICADDRESS.ID is not null
then cast(SPECIFICADDRESS.DESCRIPTION as nvarchar(50)) '
--when there is no address specified on the tax declaration use the primary address as previously
+ 'when PRIMARYCOUNTRY.ISO3166 = ''GB''
then cast(dbo.UFN_ADDRESS_GETADDRESSLINE(1, PRIMARYADDRESS.ADDRESSBLOCK) as nvarchar(50))
else cast(PRIMARYADDRESS.DESCRIPTION as nvarchar(50))
end as HOUSE,
case '
--when the tax declaration specifies an address use this for the postcode
+ 'when SPECIFICADDRESS.ID is not null and SPECIFICCOUNTRY.ISO3166 = ''GB''
then SPECIFICADDRESS.POSTCODE
when SPECIFICADDRESS.ID is not null
then null '
--when there is no address specified on the tax declaration use the postcode on hte primary address
+ 'when PRIMARYCOUNTRY.ISO3166 = ''GB''
then PRIMARYADDRESS.POSTCODE
else null
end as POSTCODE,
max(R68TABLE.DATE) as DATE,
sum(AMOUNTRECEIVED) as AMOUNT,
min(R68TABLE.DATE) as EARLIESTDATE,
case
when not SPECIFICADDRESS.ID is null and SPECIFICCOUNTRY.ISO3166 = ''GB'' then 0
when SPECIFICADDRESS.ID is null and PRIMARYCOUNTRY.ISO3166 = ''GB'' then 0
else 1
end as ISOVERSEAS
from dbo.' + @TABLENAME + ' as R68TABLE'
+ ' inner join dbo.FINANCIALTRANSACTIONLINEITEM ON FINANCIALTRANSACTIONLINEITEM.ID = R68TABLE.REVENUESPLITID '
+ ' inner join dbo.FINANCIALTRANSACTION ON FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID '
+ ' inner join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID'
+ ' left join dbo.UFN_SITEID_MAPFROM_REVENUESPLITID_BULK() [REVENUESPLITSITE] on [REVENUESPLITSITE].ID = [R68TABLE].REVENUESPLITID
cross apply
(select top 1
[DECLARATIONS].ID,
TAXDECLARATION.ADDRESSID,
TAXDECLARATION.ALIASID
from
dbo.UFN_DECLARATIONS_GET([R68TABLE].DATE, [REVENUESPLITSITE].SITEID, FINANCIALTRANSACTION.CONSTITUENTID) [DECLARATIONS]
left join dbo.TAXDECLARATION on TAXDECLARATION.ID = [DECLARATIONS].ID
order by TAXDECLARATION.DATECHANGED DESC) [VALIDDECLARATIONS]
left join dbo.ALIAS ALIASES on ALIASES.ID=VALIDDECLARATIONS.ALIASID
left join dbo.ADDRESS PRIMARYADDRESS on PRIMARYADDRESS.CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID and PRIMARYADDRESS.ISPRIMARY = 1
left join dbo.ADDRESS SPECIFICADDRESS on SPECIFICADDRESS.ID = VALIDDECLARATIONS.ADDRESSID
left join dbo.COUNTRY PRIMARYCOUNTRY on PRIMARYADDRESS.COUNTRYID = PRIMARYCOUNTRY.ID
left join dbo.COUNTRY SPECIFICCOUNTRY on SPECIFICADDRESS.COUNTRYID = SPECIFICCOUNTRY.ID '
+ 'where FINANCIALTRANSACTION.DELETEDON is null '
+ ' group by FINANCIALTRANSACTION.CONSTITUENTID, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, ALIASES.ID, ALIASES.KEYNAME, ALIASES.FIRSTNAME,
SPECIFICADDRESS.ID, PRIMARYCOUNTRY.ISO3166, PRIMARYADDRESS.ADDRESSBLOCK, PRIMARYADDRESS.DESCRIPTION, PRIMARYADDRESS.POSTCODE,
SPECIFICCOUNTRY.ISO3166,SPECIFICADDRESS.ADDRESSBLOCK, SPECIFICADDRESS.DESCRIPTION, SPECIFICADDRESS.POSTCODE'
+ ' order by DATE'
end
else
begin
set @SQL = 'select '
--determine whether there is an ALIAS
--for KEYNAME
+ 'case '
--when the tax declaration specifies an alias to use
+ 'when ALIASES.ID is not null
then cast(ALIASES.KEYNAME as nvarchar(35)) '
--when there is no alias specified use the constituent name as before
+ 'else
cast(CONSTITUENT.KEYNAME as nvarchar(35))
end as KEYNAME, '
--for FIRSTNAME
+ 'case '
--when the tax declaration specifies an alias to use
+ 'when ALIASES.ID is not null
then cast(ALIASES.FIRSTNAME as nvarchar(35)) '
--when there is no alias specified use the constituent name as before
+ 'else
cast(CONSTITUENT.FIRSTNAME as nvarchar(35))
end as FIRSTNAME, '
--determine the address to use
+ 'case '
--when the tax declaration specifies an address to use
+ 'when SPECIFICADDRESS.ID is not null and SPECIFICCOUNTRY.ISO3166 = ''GB''
then cast(dbo.UFN_ADDRESS_GETADDRESSLINE(1, SPECIFICADDRESS.ADDRESSBLOCK) as nvarchar(50))
when SPECIFICADDRESS.ID is not null
then cast(SPECIFICADDRESS.DESCRIPTION as nvarchar(50)) '
--when there is no address specified on the tax declaration use the primary address as previously
+ 'when PRIMARYCOUNTRY.ISO3166 = ''GB''
then cast(dbo.UFN_ADDRESS_GETADDRESSLINE(1, PRIMARYADDRESS.ADDRESSBLOCK) as nvarchar(50))
else cast(PRIMARYADDRESS.DESCRIPTION as nvarchar(50))
end as HOUSE,
case '
--when the tax declaration specifies an address use this for the postcode
+ 'when SPECIFICADDRESS.ID is not null and SPECIFICCOUNTRY.ISO3166 = ''GB''
then SPECIFICADDRESS.POSTCODE
when SPECIFICADDRESS.ID is not null
then null '
--when there is no address specified on the tax declaration use the postcode on hte primary address
+ 'when PRIMARYCOUNTRY.ISO3166 = ''GB''
then PRIMARYADDRESS.POSTCODE
else null
end as POSTCODE,
max(R68TABLE.DATE) as DATE,
sum(AMOUNTRECEIVED) as AMOUNT,
min(R68TABLE.DATE) as EARLIESTDATE,
case
when not SPECIFICADDRESS.ID is null and SPECIFICCOUNTRY.ISO3166 = ''GB'' then 0
when SPECIFICADDRESS.ID is null and PRIMARYCOUNTRY.ISO3166 = ''GB'' then 0
else 1
end as ISOVERSEAS
from dbo.' + @TABLENAME + ' as R68TABLE'
+ ' inner join dbo.FINANCIALTRANSACTIONLINEITEM ON FINANCIALTRANSACTIONLINEITEM.ID = R68TABLE.REVENUESPLITID '
+ ' inner join dbo.FINANCIALTRANSACTION ON FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID '
+ ' inner join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID'
+ ' left join dbo.UFN_SITEID_MAPFROM_REVENUESPLITID_BULK() [REVENUESPLITSITE] on [REVENUESPLITSITE].ID = [R68TABLE].REVENUESPLITID
outer apply '
+ '(select top 1 '
+ '[DECLARATIONS].ID, '
+ 'TAXDECLARATION.ADDRESSID, '
+ 'TAXDECLARATION.ALIASID '
+ 'from '
+ 'dbo.UFN_DECLARATIONS_GET([R68TABLE].DATE, [REVENUESPLITSITE].SITEID, FINANCIALTRANSACTION.CONSTITUENTID) [DECLARATIONS]
left join dbo.TAXDECLARATION on TAXDECLARATION.ID = [DECLARATIONS].ID
order by TAXDECLARATION.DATECHANGED DESC) [VALIDDECLARATIONS]
left join dbo.TAXDECLARATION on TAXDECLARATION.ID = [VALIDDECLARATIONS].ID
left join dbo.ALIAS ALIASES on ALIASES.ID=VALIDDECLARATIONS.ALIASID
left join dbo.ADDRESS PRIMARYADDRESS on PRIMARYADDRESS.CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID and PRIMARYADDRESS.ISPRIMARY = 1
left join dbo.ADDRESS SPECIFICADDRESS on SPECIFICADDRESS.ID = VALIDDECLARATIONS.ADDRESSID
left join dbo.COUNTRY PRIMARYCOUNTRY on PRIMARYADDRESS.COUNTRYID = PRIMARYCOUNTRY.ID
left join dbo.COUNTRY SPECIFICCOUNTRY on SPECIFICADDRESS.COUNTRYID = SPECIFICCOUNTRY.ID '
+ 'where FINANCIALTRANSACTION.DELETEDON is null '
+ ' group by FINANCIALTRANSACTION.CONSTITUENTID, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, ALIASES.ID, ALIASES.KEYNAME, ALIASES.FIRSTNAME,
SPECIFICADDRESS.ID, PRIMARYCOUNTRY.ISO3166, PRIMARYADDRESS.ADDRESSBLOCK, PRIMARYADDRESS.DESCRIPTION, PRIMARYADDRESS.POSTCODE,
SPECIFICCOUNTRY.ISO3166,SPECIFICADDRESS.ADDRESSBLOCK, SPECIFICADDRESS.DESCRIPTION, SPECIFICADDRESS.POSTCODE'
+ ' order by DATE'
end
exec sp_executesql @SQL