IF EXISTS (SELECT
               1
           FROM SYS.objects
           WHERE
               object_id = OBJECT_ID(N'rds.sp_LloElectronicCertificate'))
    DROP PROCEDURE rds.sp_lloelectroniccertificate
GO

CREATE PROCEDURE rds.sp_lloelectroniccertificate(
    @id UNIQUEIDENTIFIER,
    @mcod VARCHAR(6),
    @applicationdate datetime,
    @recipeserialnumber VARCHAR(50),
    @status int,
    @refusalReason VARCHAR(300),
    @recipeid UNIQUEIDENTIFIER,
    @certstatus int)
AS
BEGIN

    DECLARE @xml VARCHAR(MAX)

    SELECT @XML =
        convert(varchar(max), (SELECT
             'Update' AS "header/TYPE",
             (SELECT
                  CONVERT(VARCHAR(10), @applicationdate, 104)                                   AS applicationDate,
                  LEFT(@recipeserialnumber, CHARINDEX(' ', @recipeserialnumber) - 1)        AS serial,
                  SUBSTRING(@recipeserialnumber, CHARINDEX(' ', @recipeserialnumber) + 1,
                            LEN(@recipeserialnumber))                                         AS number,
                  @status                                                                       AS status,
                  @refusalReason                                                                AS refusalReason,
                  @recipeid                                                                     AS recipeID,
                  @certstatus                                                                   AS certStatus
              FOR XML PATH('body'), TYPE)
                 FOR XML PATH (''), ROOT('ELECTRONIC_CERTIFICATE_APPLICATION'), TYPE))

    INSERT INTO
        rds.t_rabbit_send_object
    (rabbitsendobjectid,
     queuename,
     messageid,
     headers,
     contentencoding,
     type,
     userid,
     createdatetime,
     ownerid,
     routingkey,
     state,
     correlationid,
     content)
    SELECT
        NEWID(),
        s.servername,
        NULL,
        NULL,
        'utf-8',
        'ELECTRONIC_CERTIFICATE_APPLICATION',
        NULL,
        GETDATE(),
        @id,
        NULL,
        0,
        NULL,
        dbo.converttexttobinaryzip(@xml, '')
    FROM rds.t_sync_servers s
    WHERE
         s.servertype = 1
      OR (EXISTS (SELECT *
                  FROM rds.t_servers_link_lpu sll
                  JOIN rds.t_servers_link sl ON sl.serverslinkid = sll.serverslinkid
                  WHERE
                        sll.lpumain = @mcod
                    AND sl.syncservers = s.syncserversid))


END