spTransactions_RestoreFailedTransaction

  CREATE PROCEDURE spTransactions_RestoreFailedTransaction(
  @TranID AS INTEGER,
  @TranType AS CHAR(1),
  @AuthCode AS VARCHAR(255)=NULL,
  @RefNum AS VARCHAR(255)=NULL
  ) AS
  BEGIN

  DECLARE @OldStatus INTEGER
  DECLARE @ExistingTran INTEGER

  DECLARE @TransactionTypeGuid NVARCHAR(100)
  DECLARE @XmlData XML
  DECLARE @XmlTextData NVARCHAR(MAX)
  DECLARE @UserID INTEGER
  DECLARE @BackOfficeID INTEGER

  SELECT @TransactionTypeGuid =
  CASE @TranType
  WHEN 'd' THEN '{5705B543-4033-4a3a-BBCD-A731403EE1E6}'
  WHEN 'e' THEN '{172A5D9A-5241-493c-A2C2-EAC164C095B0}'
  WHEN 'm' THEN '{D7D6143F-823D-4c74-AC2F-947CC96B7008}'
  ELSE NULL
  END

  IF @TransactionTypeGuid IS NULL
  BEGIN
  RAISERROR('Error - Invalid transaction type argument - Type argument must be "d", "e", or "m".',18,1)
  RETURN
  END

  SELECT @ExistingTran=COUNT(*) FROM Transactions WHERE TransactionID=@TranID AND TransactionType=@TransactionTypeGuid
  IF @ExistingTran <> 0
  BEGIN
  RAISERROR('Error - Transaction already exists in the transactions table',18,1)
  RETURN
  END

  SET @OldStatus = -1

  IF @TranType='d'
  BEGIN

  SELECT @OldStatus=[Status],
  @XmlTextData=XmlObjectData,
  @UserID = AddedByUserID
  FROM DonationTransactions WHERE DonationTransactionsID = @TranID

  IF @OldStatus = 1
  BEGIN
  RAISERROR('Error - DonationTransaction has Status=1 (Completed)',18,1)
  RETURN
  END
  IF @OldStatus = -1
  BEGIN
  RAISERROR('Error - Specified transaction ID does not exist in DonationTransactions',18,1)
  RETURN
  END

  SET @XmlData = CAST(
  REPLACE(@XmlTextData, 'encoding="utf-8"',    '')
  AS XML
  )


  -- Crack open the XML data to retrieve the BackOfficeUserID
  -- And add Reference Number & Authorization Code if specified
  SELECT @BackOfficeID =@XmlData.value(
  'declare namespace ns="urn:blackbaud.RE7.XDATA"; (/ns:ShelbyDonationTran/ns:Donor/ns:BackOfficeID)[1]',
  'int'
  )

  IF NOT @RefNum IS NULL
  BEGIN
  SET @XmlData.modify('declare namespace ns="urn:blackbaud.RE7.XDATA";
  insert
  if ( count(/ns:ShelbyDonationTran/ns:Gift/ns:ReferenceNumber) < 1 )
                                then element ReferenceNumber {sql:variable("@RefNum")}
                                else()
                                after (/ns:ShelbyDonationTran/ns:Gift/ns:CardHolderName)[1]')
        END
        IF NOT @AuthCode IS NULL
        BEGIN
            SET @XmlData.modify('declare namespace ns="urn:blackbaud.RE7.XDATA";
                                insert if ( count(/ns:ShelbyDonationTran/ns:Gift/ns:AuthorizationCode) < 1 )
                                then element AuthorizationCode {sql:variable("@AuthCode")}
                                else()
                                after (/ns:ShelbyDonationTran/ns:Gift/ns:CardHolderName)[1]')
        END

        -- Update the status of the transaction and write in the dirty XML data
        UPDATE dbo.DonationTransactions SET
            [Status] = 1,
            XMLObjectData = CAST(@XmlData AS NVARCHAR(MAX)) WHERE DonationTransactionsID=@TranID

    END

    ELSE IF @TranType='e'
    BEGIN

        SELECT @OldStatus=[Status],
               @XmlTextData=XmlObjectData,
               @UserID = AddedByUserID
        FROM EventTransactions WHERE EventTransactionsID = @TranID

        IF @OldStatus = 1
        BEGIN
            RAISERROR('Error - EventTransaction has Status=1 (Completed)',18,1)
            RETURN
        END
        IF @OldStatus = -1
        BEGIN
            RAISERROR('Error - Specified transaction ID does not exist in EventTransactions',18,1)
            RETURN
        END

        SET @XmlData = CAST(
                            REPLACE(@XmlTextData, 'encoding="utf-8"',    '')
                            AS XML
                          )

        -- Crack open the XML data to retrieve the BackOfficeUserID
        -- And add Reference Number & Authorization Code if specified
        SELECT @BackOfficeID =@XmlData.value(
            'declare namespace ns="urn:blackbaud.RE7.XDATA"; (/ns:ShelbyEventRegTran/ns:Donor/ns:BackOfficeID)[1]',
            'int'
        )

        IF NOT @RefNum IS NULL
        BEGIN
            SET @XmlData.modify('declare namespace ns="urn:blackbaud.RE7.XDATA";
                                insert
                                if ( count(/ns:ShelbyEventRegTran/ns:Gift/ns:ReferenceNumber) < 1 )
                                then element ReferenceNumber {sql:variable("@RefNum")}
                                else()
                                after (/ns:ShelbyEventRegTran/ns:Gift/ns:CardHolderName)[1]')
        END
        IF NOT @AuthCode IS NULL
        BEGIN
            SET @XmlData.modify('declare namespace ns="urn:blackbaud.RE7.XDATA";
                                insert if ( count(/ns:ShelbyEventRegTran/ns:Gift/ns:AuthorizationCode) < 1 )
                                then element AuthorizationCode {sql:variable("@AuthCode")}
                                else()
                                after (/ns:ShelbyEventRegTran/ns:Gift/ns:CardHolderName)[1]')
        END

        -- Update the status of the transaction and write in the dirty XML data
        UPDATE dbo.EventTransactions SET
            [Status] = 1,
            XMLObjectData = CAST(@XmlData AS NVARCHAR(MAX)) WHERE EventTransactionsID=@TranID

    END

    ELSE IF @TranType='m'
    BEGIN
        SELECT @OldStatus=[Status],
               @XmlTextData=XmlObjectData,
               @UserID = AddedByUserID
        FROM MembershipTransactions WHERE ID = @TranID

        IF @OldStatus = 1
        BEGIN
            RAISERROR('Error - MembershipTransaction has Status=1 (Completed)',18,1)
            RETURN
        END
        IF @OldStatus = -1
        BEGIN
            RAISERROR('Error - Specified transaction ID does not exist in MembershipTransactions',18,1)
            RETURN
        END

        SET @XmlData = CAST(
                            REPLACE(@XmlTextData, 'encoding="utf-8"',    '')
                            AS XML
                          )

        -- Crack open the XML data to retrieve the BackOfficeUserID
        -- And add Reference Number & Authorization Code if specified
        SELECT @BackOfficeID =@XmlData.value(
            'declare namespace ns="urn:blackbaud.RE7.XDATA"; (/ns:ShelbyMembershipTran/ns:Donor/ns:BackOfficeID)[1]',
            'int'
        )

        IF NOT @RefNum IS NULL
        BEGIN
            SET @XmlData.modify('declare namespace ns="urn:blackbaud.RE7.XDATA";
                                insert
                                if ( count(/ns:ShelbyMembershipTran/ns:Gift/ns:ReferenceNumber) < 1 )
                                then element ReferenceNumber {sql:variable("@RefNum")}
                                else()
                                after (/ns:ShelbyMembershipTran/ns:Gift/ns:CardHolderName)[1]')
        END
        IF NOT @AuthCode IS NULL
        BEGIN
            SET @XmlData.modify('declare namespace ns="urn:blackbaud.RE7.XDATA";
                                insert if ( count(/ns:ShelbyMembershipTran/ns:Gift/ns:AuthorizationCode) < 1 )
                                then element AuthorizationCode {sql:variable("@AuthCode")}
                                else()
                                after (/ns:ShelbyMembershipTran/ns:Gift/ns:CardHolderName)[1]')
        END

        -- Update the status of the transaction and write in the dirty XML data
        UPDATE dbo.MembershipTransactions SET
            [Status] = 1,
            XMLObjectData = CAST(@XmlData AS NVARCHAR(MAX)) WHERE ID=@TranID
    END

    SET @XmlTextData = CAST(@XmlData AS NVARCHAR(MAX))
    EXEC spTransactions_InsertTransaction 0, @TranID, @TransactionTypeGuid, @XmlTextData, @UserID, @BackOfficeID        
END