USP_DATAFORMTEMPLATE_VIEW_R68ONLINESUBMISSIONSETTINGSFORPROCESS

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@USERID	nvarchar(100)	INOUT
@PASSWORD	nvarchar(200)	INOUT
@TYPE	tinyint	INOUT
@ORGANISATION	nvarchar(160)	INOUT
@CCRN	nvarchar(20)	INOUT
@ADJUSTMENTAMOUNT	money	INOUT
@ADJUSTMENTREASONS	nvarchar(1000)	INOUT
@REGULATORCODE	tinyint	INOUT
@REGULATORNAME	nvarchar(160)	INOUT
@REGULATORNUMBER	nvarchar(20)	INOUT
@CLAIMNUMBER	nvarchar(20)	INOUT
@FILECREATED	bit	INOUT
@FILESUBMITTED	bit	INOUT
@FILEACCEPTED	bit	INOUT
@FILEREMOVEDAFTERACCEPTED	bit	INOUT
@SUBMISSIONFILE	xml	INOUT
@CORRELATIONID	nvarchar(32)	INOUT
@TRANSACTIONID	nvarchar(32)	INOUT
@SUBMISSIONDATE	datetime	INOUT
@ISTRUSTEE	bit	INOUT
@TRUSTEENAME	nvarchar(160)	INOUT
@OFFICIALTITLE	nvarchar(4)	INOUT
@OFFICIALFORENAME	nvarchar(35)	INOUT
@OFFICIALSURNAME	nvarchar(35)	INOUT
@PHONENUMBER	nvarchar(35)	INOUT
@ISOVERSEAS	bit	INOUT
@POSTCODE	nvarchar(8)	INOUT
@HASSETTINGS	bit	INOUT
@GATEWAYCODE	tinyint	INOUT
@ISSPONSORSHIP	bit	INOUT
Definition

Copy


            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_R68ONLINESUBMISSIONSETTINGSFORPROCESS
            (
                @ID uniqueidentifier,
                  @DATALOADED bit = 0 output,
                  @USERID nvarchar(100) = null output,
                @PASSWORD nvarchar(200) = null output,
                  @TYPE tinyint = null output,
                @ORGANISATION nvarchar(160) = null output,
                @CCRN nvarchar(20) = null output,
                @ADJUSTMENTAMOUNT money = null output,
                @ADJUSTMENTREASONS nvarchar(1000) = null output,
                @REGULATORCODE tinyint = null output,
                @REGULATORNAME nvarchar(160) = null output,
                @REGULATORNUMBER nvarchar(20) = null output,
                @CLAIMNUMBER nvarchar(20) = null output,
                @FILECREATED bit = null output,
                @FILESUBMITTED bit = null output,
                @FILEACCEPTED bit = null output,
                @FILEREMOVEDAFTERACCEPTED bit = null output,
                @SUBMISSIONFILE xml = null output,
                @CORRELATIONID nvarchar(32) = null output,
                @TRANSACTIONID nvarchar(32) = null output,
                @SUBMISSIONDATE datetime = null output,
                @ISTRUSTEE bit = null output,
                @TRUSTEENAME nvarchar(160) = null output,
                @OFFICIALTITLE nvarchar(4) = null output,
                @OFFICIALFORENAME nvarchar(35) = null output,
                @OFFICIALSURNAME nvarchar(35) = null output,
                @PHONENUMBER nvarchar(35) = null output,
                @ISOVERSEAS bit = null output,
                @POSTCODE nvarchar(8) = null output,
                @HASSETTINGS bit = null output,
                @GATEWAYCODE tinyint = null output,
                @ISSPONSORSHIP bit = null output
            )
            as
                set nocount on;

                declare @SENDERINFOID uniqueidentifier;
                set @HASSETTINGS = 0;

                select @CCRN = R68.REFERENCENUMBER,
                        @CLAIMNUMBER = R68ARCHIVEDPARAMETERS.TAXCLAIMNUMBER,
                        @FILECREATED = R68ONLINESUBMISSIONPARAMETERS.FILECREATED,
                        @FILESUBMITTED = R68ONLINESUBMISSIONPARAMETERS.FILESUBMITTED,
                        @FILEACCEPTED = R68ONLINESUBMISSIONPARAMETERS.FILEACCEPTED,
                        @FILEREMOVEDAFTERACCEPTED = R68ONLINESUBMISSIONPARAMETERS.FILEREMOVEDAFTERACCEPTED,
                        @SUBMISSIONFILE = R68ONLINESUBMISSIONPARAMETERS.SUBMISSIONFILE,
                        @CORRELATIONID = R68ONLINESUBMISSIONPARAMETERS.CORRELATIONID,
                        @TRANSACTIONID = R68ONLINESUBMISSIONPARAMETERS.TRANSACTIONID,
                        @SUBMISSIONDATE = R68ONLINESUBMISSIONPARAMETERS.SUBMISSIONDATE,
                        @SENDERINFOID = R68ONLINESUBMISSIONPARAMETERS.R68ONLINESUBMISSIONSETTINGSSENDERINFOID,
                        @ISSPONSORSHIP = R68.RUNGIFTAIDSPONSORSHIPSONLY
                from dbo.R68ONLINESUBMISSIONPARAMETERS
                inner join dbo.R68 on R68ONLINESUBMISSIONPARAMETERS.R68PROCESSID = R68.ID
                inner join dbo.R68ARCHIVEDPARAMETERS ON R68ARCHIVEDPARAMETERS.ID = R68ONLINESUBMISSIONPARAMETERS.ID
                where R68ONLINESUBMISSIONPARAMETERS.ID = @ID

                declare @TABLENAME nvarchar(255);
                declare @SQL nvarchar(4000);

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

                set @SQL = 
                    'select
                        @ADJUSTMENTAMOUNT = sum(case 
                                                when ORIGINALTRANSITIONALTAXCLAIMAMOUNT = 0 then ORIGINALBASETAXCLAIMAMOUNT
                                                when dbo.UFN_REVENUESPLITGIFTAID_TRANSITIONALREFLIEFEXPIRED(REVENUESPLITID, INCLUDETRANSITIONALAMOUNTCODE) = 0 then ORIGINALBASETAXCLAIMAMOUNT + ORIGINALTRANSITIONALTAXCLAIMAMOUNT
                                                else ORIGINALBASETAXCLAIMAMOUNT
                                            end)
                    from dbo.'
                    + @TABLENAME

                exec sp_executesql @SQL, N'@ADJUSTMENTAMOUNT money output', @ADJUSTMENTAMOUNT = @ADJUSTMENTAMOUNT output;

                set @SQL = 
                    'select @ADJUSTMENTREASONS = dbo.UDA_BUILDLIST(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''
                                                else ''Application adjusted''
                                            end)
                    from (select distinct REFUNDSOURCECODE
                            from dbo.' + @TABLENAME + ') as REFUNDS'

                 exec sp_executesql @SQL, N'@ADJUSTMENTREASONS nvarchar(1000) output', @ADJUSTMENTREASONS = @ADJUSTMENTREASONS output;

             --open the symmetric key for encryption

             exec dbo.USP_GET_KEY_ACCESS;

                select @USERID = R68ONLINESUBMISSIONSETTINGSSENDERINFO.USERID,
                        @PASSWORD = DecryptByKey(R68ONLINESUBMISSIONSETTINGSSENDERINFO.PASSWORD),
                            @TYPE = R68ONLINESUBMISSIONSETTINGSSENDERINFO.TYPECODE,
                        @ORGANISATION = R68ONLINESUBMISSIONSETTINGS.ORGANISATION,
                        @REGULATORCODE = R68ONLINESUBMISSIONSETTINGS.REGULATORCODE,
                        @REGULATORNAME = case when R68ONLINESUBMISSIONSETTINGS.REGULATORCODE in (0,1,2) then R68ONLINESUBMISSIONSETTINGS.REGULATOR
                                                when R68ONLINESUBMISSIONSETTINGS.REGULATORCODE = 101 then R68ONLINESUBMISSIONSETTINGS.OTHERREGULATORNAME
                                                else 'None' end,
                        @REGULATORNUMBER = R68ONLINESUBMISSIONSETTINGS.REGULATORNUMBER,
                        @ISTRUSTEE = R68ONLINESUBMISSIONSETTINGSSENDERINFO.ISTRUSTEE,
                        @TRUSTEENAME = R68ONLINESUBMISSIONSETTINGSSENDERINFO.TRUSTEENAME,
                        @OFFICIALTITLE = R68ONLINESUBMISSIONSETTINGSSENDERINFO.OFFICIALTITLE,
                        @OFFICIALFORENAME = R68ONLINESUBMISSIONSETTINGSSENDERINFO.OFFICIALFORENAME,
                        @OFFICIALSURNAME = R68ONLINESUBMISSIONSETTINGSSENDERINFO.OFFICIALSURNAME,
                        @PHONENUMBER = R68ONLINESUBMISSIONSETTINGSSENDERINFO.PHONENUMBER,
                        @ISOVERSEAS = R68ONLINESUBMISSIONSETTINGSSENDERINFO.ISOVERSEAS,
                        @POSTCODE = R68ONLINESUBMISSIONSETTINGSSENDERINFO.POSTCODE,
                        @HASSETTINGS = 1
                from dbo.R68ONLINESUBMISSIONSETTINGS
                inner join dbo.R68ONLINESUBMISSIONSETTINGSSENDERINFO on R68ONLINESUBMISSIONSETTINGS.ID = R68ONLINESUBMISSIONSETTINGSSENDERINFO.R68ONLINESUBMISSIONSETTINGSID
                where R68ONLINESUBMISSIONSETTINGSSENDERINFO.ID = @SENDERINFOID

                select @GATEWAYCODE = GATEWAYCODE
                from dbo.R68ONLINESUBMISSIONGATEWAYSETTINGS

                set @DATALOADED = 1

                return 0;