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('./certStatus[1]', 'integer')                                  AS certStatus,
                t.c.value('./recipeID[1]', 'uniqueidentifier')                           AS RecipeId,
                t.c.value('./refusalReason[1]', 'varchar(300)')                          AS RefusalReason
            INTO
                #tmp
            FROM @Xml.nodes('ELECTRONIC_CERTIFICATE_APPLICATION/body') t(c)

            UPDATE r
            SET
                r.Certificate = CASE t.status   WHEN 1 THEN 1 
                                                WHEN 2 THEN 0 
                                                WHEN 3 THEN 2 
                                                END,
            certificateStatus = coalesce(t.certStatus, ap.CertificateStatus)
            FROM aps.t_recipe r
            JOIN #tmp t ON t.ApplicationDate = r.RecipeDate AND r.Serial = t.Serial AND r.Number = t.Number
        END

END;