USP_REPORT_R68REFUNDS

CREATE procedure dbo.USP_REPORT_R68REFUNDS
(
    @BUSINESSPROCESSSTATUSID uniqueidentifier
)
with execute as owner
as 
    set nocount on;

    declare @TABLENAME nvarchar(255);
    declare @REPORTTYPE nvarchar(10);
    declare @SQL nvarchar(max);

    begin try
        select 
            @TABLENAME = TABLENAME
        from dbo.BUSINESSPROCESSOUTPUT 
        where BUSINESSPROCESSSTATUSID = @BUSINESSPROCESSSTATUSID 
            and TABLEKEY = 'R68REFUNDS_OUTPUT';

        if dbo.UFN_R68_HASPENDINGRECORDS(@BUSINESSPROCESSSTATUSID) = 0
            set @REPORTTYPE = 'Committed';
        else
            set @REPORTTYPE = 'Preview';

        set @SQL = N'
                with DECLARATIONS_CTE as (
                    select
                        R68REFUNDDETAILS.ID as REFUNDDETAILSID,
                        DECLARATIONS.ID as TAXDECLARATIONID,
                        row_number() over (partition by R68REFUNDDETAILS.ID order by DECLARATIONS.DATETAXDECLARATIONCHANGED desc) as ROWNUM
                    from
                        dbo.' + @TABLENAME;
        set @SQL = @SQL + N' as R68REFUNDDETAILS
                    join    
                        dbo.FINANCIALTRANSACTIONLINEITEM ON FINANCIALTRANSACTIONLINEITEM.ID = R68REFUNDDETAILS.REVENUESPLITID
                    join    
                        dbo.FINANCIALTRANSACTION ON FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                    left join
                        dbo.UFN_DECLARATIONS_GETWITHCCRN_BULK() DECLARATIONS
                        on
                            DECLARATIONS.CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID and
                            DECLARATIONS.CHARITYCLAIMREFERENCENUMBER = R68REFUNDDETAILS.ORIGINALCHARITYCLAIMREFERENCENUMBER and
                            DECLARATIONS.DECLARATIONSTARTS <= R68REFUNDDETAILS.MAXGIFTDATEFROMORIGINALCLAIM and
                            DECLARATIONS.DECLARATIONENDS >= R68REFUNDDETAILS.MAXGIFTDATEFROMORIGINALCLAIM
                )
                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,
                    ORIGINALTAXCLAIMNUMBER,
                    MAXGIFTDATEFROMORIGINALCLAIM,
                    TOTALGIFTAMOUNTFROMORIGINALCLAIM,
                    TOTALTAXCLAIMAMOUNTFROMORIGINALCLAIM,
                    ORIGINALGIFTAMOUNT SPLITAMOUNT,
                    case 
                        when ORIGINALTRANSITIONALTAXCLAIMAMOUNT = 0 then ORIGINALBASETAXCLAIMAMOUNT
                        when dbo.UFN_REVENUESPLITGIFTAID_TRANSITIONALREFLIEFEXPIRED(REVENUESPLITID, INCLUDETRANSITIONALAMOUNTCODE) = 0 then ORIGINALBASETAXCLAIMAMOUNT + ORIGINALTRANSITIONALTAXCLAIMAMOUNT
                        else ORIGINALBASETAXCLAIMAMOUNT
                    end * -1 REFUNDAMOUNT,

                    case REFUNDSOURCECODE
                        when 0 then ''Eligibility changed''
                        when 1 then ''Manually refunded''
                        when 2 then ''Application adjusted''
                        when 3 then ''Application deleted''
                        when 4 then ''Charity claim reference number changed''
                    end as REFUNDSOURCE,
                    @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
        set @SQL = @SQL + N' as [OUTPUT]
            join    
                dbo.FINANCIALTRANSACTIONLINEITEM ON FINANCIALTRANSACTIONLINEITEM.ID = [OUTPUT].REVENUESPLITID
            join    
                dbo.FINANCIALTRANSACTION ON FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            left join
                DECLARATIONS_CTE on DECLARATIONS_CTE.REFUNDDETAILSID = [OUTPUT].ID and DECLARATIONS_CTE.ROWNUM = 1
            left join
                dbo.TAXDECLARATION on TAXDECLARATION.ID = DECLARATIONS_CTE.TAXDECLARATIONID
            left join
                dbo.ALIAS ALIASES on ALIASES.ID = TAXDECLARATION.ALIASID
            left join
                dbo.[ADDRESS] PRIMARYADDRESS on PRIMARYADDRESS.CONSTITUENTID = [FINANCIALTRANSACTION].CONSTITUENTID and PRIMARYADDRESS.ISPRIMARY = 1
            left join
                dbo.[ADDRESS] SPECIFICADDRESS on SPECIFICADDRESS.ID = TAXDECLARATION.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
            order by NAME'

        exec sp_executesql @SQL, N'@REPORTTYPEIN nvarchar(10)', @REPORTTYPEIN = @REPORTTYPE;    
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch