IF EXISTS (SELECT
               1
           FROM SYS.OBJECTS
           WHERE
               OBJECT_ID = OBJECT_ID(N'APS.sp_RDS_LloElectronicCertificate'))
    DROP PROCEDURE APS.sp_RDS_LloElectronicCertificate
GO

CREATE PROCEDURE APS.sp_RDS_LloElectronicCertificate @Xml xml = NULL,
                                                     @ID UNIQUEIDENTIFIER = NULL,
                                                     @IsNew BIT = 1,
                                                     @QueueName VARCHAR(100),
                                                     @dateTime DATETIME = NULL,
                                                     @User UNIQUEIDENTIFIER = NULL
AS
BEGIN

    IF (@XML IS NOT NULL)
        BEGIN
            SELECT
                CONVERT(DATETIME, t.c.value('./ApplicationDate[1]', 'varchar(10)'), 104) AS ApplicationDate,
                t.c.value('./Serial[1]', 'varchar(128)')                                 AS Serial,
                t.c.value('./Number[1]', 'varchar(128)')                                 AS Number,
                t.c.value('./Status[1]', 'integer')                                      AS Status,
                t.c.value('./RecipeID[1]', 'uniqueidentifier')                           AS RecipeId
            INTO
                #tmp
            FROM @Xml.nodes('ELECTRONIC_CERTIFICATE_APPLICATION') t(c)

            UPDATE r
            SET
                r.Certificate = CASE t.status WHEN 1 THEN 1 ELSE 0 END
            FROM aps.t_recipe r
            JOIN #tmp t ON t.ApplicationDate = r.ApplicationDate AND r.Serial = t.Serial AND r.Number = t.Number
        END

END;