CREATE OR REPLACE PROCEDURE AKUZ.sp_AKUZ_FTLOAD_DloAccessPost(
    Xml xml DEFAULT NULL::XML,
    IsNew integer DEFAULT 1,
    ID uuid DEFAULT NULL::uuid,
    QueueName character varying DEFAULT NULL::character varying,
    dateTime timestamp DEFAULT NULL,
    "user" uuid DEFAULT NULL
)
AS $$
DECLARE
    v_ContentEncoding varchar(255);
    v_Type varchar(255);

    p_Xml xml := Xml;
    p_IsNew integer := IsNew;
    p_ID uuid := ID;
    p_QueueName varchar(100) := QueueName;
    p_dateTime timestamp := dateTime;
    p_User uuid := "user";
BEGIN
    p_dateTime := COALESCE(p_dateTime, CURRENT_TIMESTAMP(0));
    v_ContentEncoding := 'utf-8';
    v_Type := 'DLO_ACCESSED_POSTS_FTLOAD';

    IF (p_Xml IS NOT NULL) THEN
        CREATE TEMPORARY TABLE temp_DLO_ACCESSED_POSTS AS
        SELECT
            (xpath('/DLO_ACCESSED_POST/ID/text()', x))[1]::text::uuid AS ID,
            (xpath('/DLO_ACCESSED_POST/POST_CODE/text()', x))[1]::text AS PostCode,
            (xpath('/DLO_ACCESSED_POST/LAST_NAME/text()', x))[1]::text AS LastName,
            (xpath('/DLO_ACCESSED_POST/FIRST_NAME/text()', x))[1]::text AS FirstName,
            (xpath('/DLO_ACCESSED_POST/FATHER_NAME/text()', x))[1]::text AS FatherName,
            (xpath('/DLO_ACCESSED_POST/CATEGORY/text()', x))[1]::text::int AS Category,
            to_timestamp((xpath('/DLO_ACCESSED_POST/BEGIN_ACCESS_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS BeginAccessDate,
            to_timestamp((xpath('/DLO_ACCESSED_POST/END_ACCESS_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS EndAccessDate,
            to_timestamp((xpath('/DLO_ACCESSED_POST/BEGIN_WORK_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS BeginWorkDate,
            to_timestamp((xpath('/DLO_ACCESSED_POST/CERTIFICATE_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS CertificateDate,
            (xpath('/DLO_ACCESSED_POST/MEDICAL_SPECIALITY_CODE/text()', x))[1]::text AS MedicalSpecialityCode,
            to_timestamp((xpath('/DLO_ACCESSED_POST/EXPULSION_DATE/text()', x))[1]::text, 'YYYY-MM-DD') AS ExpulsionDate,
            (xpath('/DLO_ACCESSED_POST/SNILS/text()', x))[1]::text AS Snils,
            (xpath('/DLO_ACCESSED_POST/POST_TYPE/text()', x))[1]::text AS PostType,
            (xpath('/DLO_ACCESSED_POST/SPECIALITY/text()', x))[1]::text AS Speciality,
            (xpath('/DLO_ACCESSED_POST/LPU_MAIN/text()', x))[1]::text AS LpuMain,
            (xpath('/DLO_ACCESSED_POST/EhrRus7PJSAM6GJM/text()', x))[1]::text::int AS EhrRus7pjsam6gjmIdEhr,
            (xpath('/DLO_ACCESSED_POST/EhrRusLS9R0Q4VR4/text()', x))[1]::text::int AS EhrRusLs9r0q4vr4IdEhr
        FROM
            unnest(xpath('//DLO_ACCESSED_POST', p_Xml)) as x;

        DELETE FROM AKUZ.T_DLO_ACCESSED_POSTS;

        INSERT INTO AKUZ.T_DLO_ACCESSED_POSTS (
            DloAccessedPostsID,
            PostCode,
            LastName,
            FirstName,
            FatherName,
            Category,
            BeginAccessDate,
            EndAccessDate,
            BeginWorkDate,
            CertificateDate,
            MedicalSpecialityCode,
            ExpulsionDate,
            Snils,
            PostType,
            Speciality,
            LpuMain,
            EhrRus7pjsam6gjm,
            EhrRus7pjsam6gjmIdEhr,
            EhrRusLs9r0q4vr4,
            EhrRusLs9r0q4vr4IdEhr
        )
        SELECT
            p.ID,
            PostCode,
            LastName,
            FirstName,
            FatherName,
            Category,
            BeginAccessDate,
            EndAccessDate,
            BeginWorkDate,
            CertificateDate,
            MedicalSpecialityCode,
            ExpulsionDate,
            Snils,
            (SELECT pt.PostTypeID FROM AKUZ.T_POST_TYPE pt WHERE pt.Code = p.PostType LIMIT 1),
            (SELECT s.SpecialityID FROM AKUZ.T_SPECIALITY s WHERE s.Code = p.Speciality LIMIT 1),
            (SELECT lm.LpuId FROM AKUZ.T_LPU_MAIN lm WHERE lm.LpuCode = p.LpuMain LIMIT 1),
            (SELECT e.EhrRusId from EHR.T_EHR_RUS_7PJSAM6GJM e WHERE e.IdEhr = p.EhrRus7pjsam6gjmIdEhr LIMIT 1) as EhrRus7pjsam6gjm,
            p.EhrRus7pjsam6gjmIdEhr,
            (SELECT e.EhrRusId from EHR.T_EHR_RUS_LS9R0Q4VR4 e WHERE e.IdEhr = p.EhrRusLs9r0q4vr4IdEhr LIMIT 1) as EhrRusLs9r0q4vr4,
            p.EhrRusLs9r0q4vr4IdEhr
        FROM temp_DLO_ACCESSED_POSTS p;

        DROP TABLE temp_DLO_ACCESSED_POSTS;
    END IF;
END;
$$
LANGUAGE plpgsql;
