USP_REPORT_R68
Returns the data necessary for the R68 report.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @BUSINESSPROCESSSTATUSID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_R68
(
@BUSINESSPROCESSSTATUSID uniqueidentifier
)
with execute as owner
as
set nocount on;
declare @TABLENAME nvarchar(255);
declare @COUNT integer;
declare @REPORTTYPE nvarchar(10);
declare @SQL nvarchar(4000);
declare @ISSPONSORSHIP bit = 0;
begin try
select
@TABLENAME = TABLENAME
from dbo.BUSINESSPROCESSOUTPUT
where BUSINESSPROCESSSTATUSID = @BUSINESSPROCESSSTATUSID
and TABLEKEY = 'R68_OUTPUT';
if dbo.UFN_R68_HASPENDINGRECORDS(@BUSINESSPROCESSSTATUSID) = 0
set @REPORTTYPE = 'Committed';
else
set @REPORTTYPE = 'Preview';
select @ISSPONSORSHIP = R68.RUNGIFTAIDSPONSORSHIPSONLY
from dbo.R68ONLINESUBMISSIONPARAMETERS
inner join dbo.R68 on R68ONLINESUBMISSIONPARAMETERS.R68PROCESSID = R68.ID
where R68ONLINESUBMISSIONPARAMETERS.ID = @BUSINESSPROCESSSTATUSID;
if @ISSPONSORSHIP = 0
begin
set @SQL = 'select '
--determine whether there is an ALIAS
+ 'case '
--when the tax declaration specifies an alias to use
+ 'when ALIASES.ID is not null then ALIASES.NAME '
--when there is no alias specified use the constituent name as before
+ 'else
dbo.UFN_CONSTITUENT_BUILDNAME(FINANCIALTRANSACTION.CONSTITUENTID)
end as NAME,
max(OUTPUT.DATE) as DATE,
sum(AMOUNTRECEIVED) as AMOUNTRECEIVED,
sum(AMOUNTCLAIMED) as AMOUNTCLAIMED,
@REPORTTYPEIN as REPORTTYPE,'
--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 ADDRESSBLOCK,
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
from dbo.' + @TABLENAME + ' as OUTPUT '
+ 'join dbo.FINANCIALTRANSACTIONLINEITEM ON FINANCIALTRANSACTIONLINEITEM.ID = output.REVENUESPLITID '
+ 'join dbo.FINANCIALTRANSACTION ON FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID '
+ 'left join dbo.UFN_SITEID_MAPFROM_REVENUESPLITID_BULK() [REVENUESPLITSITE] on [REVENUESPLITSITE].ID = [OUTPUT].REVENUESPLITID
cross apply
(select top 1
[DECLARATIONS].ID,
TAXDECLARATION.ALIASID,
TAXDECLARATION.ADDRESSID
from
dbo.UFN_DECLARATIONS_GET([OUTPUT].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 dbo.FINANCIALTRANSACTION.DELETEDON is NULL
group by FINANCIALTRANSACTION.CONSTITUENTID, ALIASES.ID, ALIASES.NAME, SPECIFICADDRESS.ID, PRIMARYADDRESS.ADDRESSBLOCK,PRIMARYADDRESS.POSTCODE,PRIMARYCOUNTRY.ISO3166,PRIMARYADDRESS.DESCRIPTION,
SPECIFICADDRESS.ADDRESSBLOCK,SPECIFICADDRESS.POSTCODE,SPECIFICCOUNTRY.ISO3166,SPECIFICADDRESS.DESCRIPTION
order by NAME;'
end
else
begin
set @SQL = 'select '
--determine whether there is an ALIAS
+ 'case '
--when the tax declaration specifies an alias to use
+ 'when ALIASES.ID is not null then ALIASES.NAME '
--when there is no alias specified use the constituent name as before
+ 'else
dbo.UFN_CONSTITUENT_BUILDNAME(FINANCIALTRANSACTION.CONSTITUENTID)
end as NAME,
max(OUTPUT.DATE) as DATE,
sum(AMOUNTRECEIVED) as AMOUNTRECEIVED,
sum(AMOUNTCLAIMED) as AMOUNTCLAIMED,
@REPORTTYPEIN as REPORTTYPE,'
--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 ADDRESSBLOCK,
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
from dbo.' + @TABLENAME + ' as OUTPUT '
+ 'join dbo.FINANCIALTRANSACTIONLINEITEM ON FINANCIALTRANSACTIONLINEITEM.ID = output.REVENUESPLITID '
+ 'join dbo.FINANCIALTRANSACTION ON FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID '
+ 'left join dbo.UFN_SITEID_MAPFROM_REVENUESPLITID_BULK() [REVENUESPLITSITE] on [REVENUESPLITSITE].ID = [OUTPUT].REVENUESPLITID
outer apply
(select top 1
[DECLARATIONS].ID,
TAXDECLARATION.ALIASID,
TAXDECLARATION.ADDRESSID
from
dbo.UFN_DECLARATIONS_GET([OUTPUT].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 dbo.FINANCIALTRANSACTION.DELETEDON is NULL
group by FINANCIALTRANSACTION.CONSTITUENTID, ALIASES.ID, ALIASES.NAME, SPECIFICADDRESS.ID, PRIMARYADDRESS.ADDRESSBLOCK,PRIMARYADDRESS.POSTCODE,PRIMARYCOUNTRY.ISO3166,PRIMARYADDRESS.DESCRIPTION,
SPECIFICADDRESS.ADDRESSBLOCK,SPECIFICADDRESS.POSTCODE,SPECIFICCOUNTRY.ISO3166,SPECIFICADDRESS.DESCRIPTION
order by NAME;'
end
exec sp_executesql @SQL, N'@REPORTTYPEIN nvarchar(10)', @REPORTTYPEIN = @REPORTTYPE;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch