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))
AS
BEGIN

    DECLARE @xml VARCHAR(MAX)

    SELECT @XML =
        convert(varchar(max), (SELECT
             'Update' AS "header/TYPE",
             (SELECT
                  CONVERT(VARCHAR(10), ap.applicationdate, 104)                          AS applicationdate,
                  LEFT(ap.recipeserialnumber, CHARINDEX(' ', ap.recipeserialnumber) - 1) AS serial,
                  SUBSTRING(ap.recipeserialnumber, CHARINDEX(' ', ap.recipeserialnumber) + 1,
                            LEN(ap.recipeserialnumber))                                  AS number,
                  ap.status                                                              AS status,
                  ap.recipeid                                                            AS recipeid
              FROM rds.t_electronic_certificate_application ap
              WHERE
                  ap.electroniccertificateapplicationid = @id
              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