DROP PROCEDURE IF EXISTS aps.sp_rds_lloelectroniccertificate;

CREATE OR REPLACE PROCEDURE aps.sp_rds_lloelectroniccertificate(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL::uuid,
    "QueueName" character varying DEFAULT 'rds.registry'::character varying,
    "dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone,
    "User" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml xml := "Xml";
BEGIN
    
    IF ("Xml" IS NOT NULL)
    THEN
        CREATE TEMP TABLE temp AS
            
            SELECT (XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION/applicationDate/text()', x))[1]::timestamp AS applicationdate,
                   (XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION/serial/text()', x))[1]::varchar(128)       AS serial,
                   (XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION/number/text()', x))[1]::varchar(128)       AS number,
                   (XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION/status/text()', x))[1]::integer            AS status,
                   (XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION/recipeID/text()', x))[1]::uuid             AS recipeid
            FROM UNNEST(XPATH('/ELECTRONIC_CERTIFICATE_APPLICATION', p_xml)) AS x;
        
        UPDATE aps.t_recipe r
        SET certificate = CASE t.status 
                                    WHEN 1 THEN 1 
                                    WHEN 2 THEN 0 
                                    WHEN 3 THEN 2 
                                    END
        FROM temp t
        WHERE t.applicationdate = r.applicationdate
          AND r.serial = t.serial
          AND r.number = t.number;
    
    END IF;
END;
$$