DROP PROCEDURE IF EXISTS aps.sp_rds_lloelectroniccertificate;

CREATE OR REPLACE PROCEDURE aps.sp_rds_lloelectroniccertificate(
    "Xml" text DEFAULT NULL,
    "IsNew" boolean DEFAULT '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 TRUE ELSE FALSE END
        FROM temp t
        WHERE t.applicationdate = r.applicationdate
          AND r.serial = t.serial
          AND r.number = t.number;
    
    END IF;
END;
$$