USP_R68_SNAPSHOT_REPORT

create procedure dbo.USP_R68_SNAPSHOT_REPORT
(
    @BUSINESSPROCESSSTATUSID nvarchar(60)
)
with execute as owner
as 
    set nocount on;

    declare @REPORTTYPE nvarchar(10);
    declare @SUBMITTED nvarchar(10);
    declare @SQL nvarchar(4000);


    begin try

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

        if (select FILESUBMITTED from dbo.R68ONLINESUBMISSIONPARAMETERS where ID = @BUSINESSPROCESSSTATUSID) = 0
            set @SUBMITTED = 'No';
        else
            set @SUBMITTED = 'Yes';

            set @SQL = 'select ' +
                         'R68ONLINESUBMISSIONPARAMETERS.ID,
                          SUBMISSION.N.value(''' + '(*:Total[1])' + ''',''' + 'money' + ''') as XMLAMOUNT,
                          SUBMISSION.N.value(''' + '(*:Date[1])' + ''',''' + 'datetime' + ''') as Date,
                          SUBMISSION.N.value(''' + '(*:Donor[1]/*:Fore[1])' + ''',''' + 'nvarchar(50)' + ''') as FORENAME,
                          SUBMISSION.N.value(''' + '(*:Donor[1]/*:Sur[1])' + ''',''' + 'nvarchar(50)' + ''') as SURNAME,
                          SUBMISSION.N.value(''' + '(*:Donor[1]/*:House[1])' + ''',''' + 'nvarchar(50)' + ''') as ADDRESS,
                          SUBMISSION.N.value(''' + '(*:Donor[1]/*:Postcode[1])' + ''',''' + 'nvarchar(50)' + ''') as POSTCODE,
                          @REPORTTYPEIN as REPORTTYPE,
                          @SUBMITTEDIN as SUBMITTED
                        from dbo.R68ONLINESUBMISSIONPARAMETERS
                          cross apply R68ONLINESUBMISSIONPARAMETERS.SUBMISSIONFILE.nodes(''' + '//*:GAD' + ''') as SUBMISSION(N)
                        where R68ONLINESUBMISSIONPARAMETERS.ID = ''' + CAST(@BUSINESSPROCESSSTATUSID as nvarchar(max)) + '''
                        order by SURNAME asc;'

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

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