-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2016-03-14
-- Description: Выгрузка/загрузка заявок по ДЛО в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_rds_drugrequest;

CREATE PROCEDURE sp_rds_drugrequest(
    "Xml" text DEFAULT NULL
, "IsNew" integer = 1
, "ID" uuid DEFAULT NULL::uuid
, "QueueName" character varying DEFAULT 'RDSSync'::character varying
, "dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
, "User" uuid DEFAULT NULL::uuid
, "PATH" character varying DEFAULT NULL::character varying
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    
    DECLARE
        _result          text;
        _contentencoding varchar(255) := 'utf-8';
        _type            varchar(255) := 'DRUG_REQUEST';
        _operationtype   varchar(255);
        _ownerid         uuid         := "ID";
        -- Operation types
        _insert          varchar(6)   := 'Insert';
        _update          varchar(6)   := 'Update';
        _delete          varchar(6)   := 'Delete';
        _ftload          varchar(6)   := 'FTLoad';
        _ftsend          varchar(6)   := 'FTSend';
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        
        IF "Xml" IS NULL
        THEN
            --выясняем тип операции
            IF "ID" IS NULL
            THEN
                _operationtype := _ftsend;
            ELSIF "IsNew" = '1'
            THEN
                _operationtype := _insert;
            ELSIF "IsNew" = '0' AND EXISTS(SELECT* FROM akuz.t_drug_request dr WHERE dr.drugrequestid = "ID")
            THEN
                _operationtype := _update;
            ELSIF "IsNew" = '0' AND NOT EXISTS(SELECT* FROM akuz.t_drug_request dr WHERE dr.drugrequestid = "ID")
            THEN
                _operationtype := _delete;
                _result := XMLELEMENT(NAME "DRUG_REQUEST", XMLELEMENT(NAME header,
                                                                      XMLFOREST(_operationtype AS "TYPE", "ID" AS "DrugRequestID")));
            END IF;
            
            -- Обработка операции
            IF _operationtype = _ftsend
            THEN
                -- собираем все DRUG_REQUEST
                SELECT '<"DRUG_REQUEST"><header><TYPE>' || _operationtype ||
                       '</TYPE></header><body><"DRUG_REQUEST_LIST">' ||
                       COALESCE(STRING_AGG(
                                    XMLSERIALIZE(CONTENT drug_request_xml AS TEXT),
                                    ''
                                ), '') ||
                       '</"DRUG_REQUEST_LIST"></body></"DRUG_REQUEST">'
                INTO _result
                FROM (SELECT XMLELEMENT(
                                 NAME "DRUG_REQUEST",
                                 XMLFOREST(
                                     dr.drugrequestid AS "DrugRequestID"
                                     , TO_CHAR(bdr.requestdate, 'YYYY-MM-DD') AS "RequestDate"
                                     , bdr.number AS "Number"
                                     , bdr.active AS "Active"
                                     , bdr.additional AS "Additional"
                                     , TO_CHAR(bdr.beginperiod, 'YYYY-MM-DD') AS "BeginPeriod"
                                     , TO_CHAR(bdr.endperiod, 'YYYY-MM-DD') AS "EndPeriod"
                                     , bdr.name AS "Name"
                                     , dr.importfilename AS "ImportFileName"
                                     , bdr.comment AS "Comment"
                                     , lp.lpucode AS "LpuMain"
                                     , br.branchcode AS "BranchCode"
                                     , drm.drugrequestid AS "DrugRequestID"
                                     , dr.requesttype AS "RequestType"
                                     , bdr.status AS "Status"
                                     , bdr.limit AS "Limit"
                                     , er.codeehr AS "EhrRusEj36pmtjqk"
                                     , rb.currversion AS "EhrRusEj36pmtjqkVer"
                                     , ervzn.idehr AS "EhrRusVzn17"
                                     , rbvzn.currversion AS "EhrRusVzn17Ver",
                                     (SELECT XMLAGG(
                                                 XMLELEMENT(
                                                     NAME "DRUG_DEMAND",
                                                     XMLFOREST(
                                                         dd.drugdemandid AS "DrugDemandID"
                                                         , dd.demandonperiod AS "DemandOnPeriod"
                                                         , dd.requestquantity AS "RequestQuantity"
                                                         , dd.requestcode AS "RequestCode"
                                                         , dd.lsfo AS "LSFO"
                                                         , dd.comment AS "Comment"
                                                         , dd.drugrequest AS "DrugRequest"
                                                         , dd.sectioncode AS "SectionCode"
                                                         , dd.requestcatalog AS "RequestCatalog",
                                                         (SELECT XMLAGG(
                                                                     XMLELEMENT(
                                                                         NAME "PHARMACY_DRUG_DEMAND",
                                                                         XMLFOREST(
                                                                             pdd.pharmacydrugdemandid AS
                                                                             "PharmacyDrugDemandID"
                                                                             , pdd.demandonperiod AS "DemandOnPeriod"
                                                                             , pdd.requestquantity AS "RequestQuantity"
                                                                             , pdd.mont1 AS "Mont1"
                                                                             , pdd.mont2 AS "Mont2"
                                                                             , pdd.mont3 AS "Mont3"
                                                                             , pdd.mont4 AS "Mont4"
                                                                             , pdd.mont5 AS "Mont5"
                                                                             , pdd.mont6 AS "Mont6"
                                                                             , pdd.mont7 AS "Mont7"
                                                                             , pdd.mont8 AS "Mont8"
                                                                             , pdd.mont9 AS "Mont9"
                                                                             , pdd.mont10 AS "Mont10"
                                                                             , pdd.mont11 AS "Mont11"
                                                                             , pdd.mont12 AS "Mont12"
                                                                             , pdd.drugdemand AS "DrugDemand"
                                                                             , pdd.pharmacy AS "Pharmacy",
                                                                             (SELECT XMLAGG(
                                                                                         XMLELEMENT(
                                                                                             NAME "PATIENT_DRUG_DEMAND",
                                                                                             XMLFOREST(
                                                                                                 pat.patientdrugdemandid AS
                                                                                                 "PatientDrugDemandID"
                                                                                                 ,
                                                                                                 pat.pharmacydrugdemand AS
                                                                                                 "PharmacyDrugDemand"
                                                                                                 , pat.demandonperiod AS
                                                                                                 "DemandOnPeriod"
                                                                                                 ,
                                                                                                 pat.requestquantity AS
                                                                                                 "RequestQuantity"
                                                                                                 , pat.mont1 AS "Mont1"
                                                                                                 , pat.mont2 AS "Mont2"
                                                                                                 , pat.mont3 AS "Mont3"
                                                                                                 , pat.mont4 AS "Mont4"
                                                                                                 , pat.mont5 AS "Mont5"
                                                                                                 , pat.mont6 AS "Mont6"
                                                                                                 , pat.mont7 AS "Mont7"
                                                                                                 , pat.mont8 AS "Mont8"
                                                                                                 , pat.mont9 AS "Mont9"
                                                                                                 , pat.mont10 AS
                                                                                                 "Mont10"
                                                                                                 , pat.mont11 AS
                                                                                                 "Mont11"
                                                                                                 , pat.mont12 AS
                                                                                                 "Mont12"
                                                                                                 , p.birthday AS
                                                                                                 "Birthday"
                                                                                                 , p.firstname AS
                                                                                                 "FirstName"
                                                                                                 , p.fathername AS
                                                                                                 "FatherName"
                                                                                                 , p.lastname AS
                                                                                                 "LastName"
                                                                                                 , esex.code AS "Sex"
                                                                                                 , b.benefitcode AS
                                                                                                 "BenefitCode"
                                                                                                 , pat.signa AS "Signa"
                                                                                                 , worker.fio AS "Post"
                                                                                                 , b.benefitviews AS
                                                                                                 "BenefitsType"
                                                                                                 , pat.mkb AS "MKB"
                                                                                                 , pat.comment AS
                                                                                                 "Comment"
                                                                                                 , p.snils AS "Snils"
                                                                                                 , CASE
                                                                                                       WHEN p.docseries = '0'
                                                                                                           THEN NULL
                                                                                                       ELSE p.docseries
                                                                                                   END
                                                                                                 AS "DocSeries"
                                                                                                 , CASE
                                                                                                       WHEN p.docnumber = '0'
                                                                                                           THEN NULL
                                                                                                       ELSE p.docnumber
                                                                                                   END
                                                                                                 AS "DocNumber"
                                                                                                 ,
                                                                                                 TO_CHAR(p.docissuedate, 'YYYY-MM-DD') AS
                                                                                                 "DocIssueDate"
                                                                                                 , p.docissueorg AS
                                                                                                 "DocIssueOrg"
                                                                                                 , edt.code AS "DocType"
                                                                                                 , polis.number AS
                                                                                                 "PolicyNumber"
                                                                                                 , polis.serial AS
                                                                                                 "PolicySerial"
                                                                                             )
                                                                                         )
                                                                                     )
                                                                              FROM akuz.t_patient_drug_demand pat
                                                                              JOIN      akuz.t_patient        p ON p.patientid = pat.patient
                                                                              JOIN      vclib.t_enums         edt ON edt.enumid = p.doctype
                                                                              JOIN      vclib.t_enums         esex ON esex.enumid = p.sex
                                                                              LEFT JOIN akuz.t_benefits       b ON b.benefitsid = pat.benefits
                                                                              LEFT JOIN akuz.t_post           post ON post.resourceid = pat.post
                                                                              LEFT JOIN akuz.t_worker         worker ON post.worker = worker.workerid
                                                                              LEFT JOIN akuz.t_policy         polis
                                                                                            ON polis.policyid = dbo.fn_getomspolicy(pat.patient, "dateTime")
                                                                              WHERE pat.pharmacydrugdemand = pdd.pharmacydrugdemandid
                                                                             ) AS "PATIENT_DRUG_DEMAND"
                                                                         )
                                                                     )
                                                                 )
                                                          FROM akuz.t_pharmacy_drug_demand pdd
                                                          WHERE dd.drugdemandid = pdd.drugdemand
                                                         ) AS "PHARMACY_DRUG_DEMAND"
                                                     )
                                                 )
                                             )
                                      FROM akuz.t_drug_demand dd
                                      WHERE dd.drugrequest = dr.drugrequestid
                                     ) AS "DRUG_DEMAND"
                                 )
                             ) AS drug_request_xml
                      FROM akuz.t_drug_request           dr
                      JOIN      akuz.t_base_drug_request bdr ON bdr.drugrequestid = dr.drugrequestid
                      JOIN      akuz.t_lpu_main          lp ON lp.lpuid = bdr.lpumain
                      LEFT JOIN akuz.t_branch            br ON br.lpuid = dr.branch
                      LEFT JOIN akuz.t_drug_request      drm ON drm.drugrequestid = bdr.drugrequestmain
                      LEFT JOIN ehr.t_ehr_rus_ej36pmtjqk er ON er.ehrrusid = bdr.ehrrusej36pmtjqk
                      LEFT JOIN ehr.t_ehr_rus_ref_books  rb ON rb.ehrrusrefbooksid = er.ehrrusrefbooks
                      LEFT JOIN ehr.t_ehr_rus_vzn17      ervzn ON ervzn.ehrrusid = bdr.ehrrusvzn17
                      LEFT JOIN ehr.t_ehr_rus_ref_books  rbvzn
                                    ON rbvzn.ehrrusrefbooksid = ervzn.ehrrusrefbooks
                      WHERE bdr.status = 3
                     ) AS "DRUG_REQUEST";
            
            ELSIF _operationtype <> _delete
            THEN
                -- собираем DRUG_REQUEST
                SELECT XMLELEMENT
                       (
                           NAME "DRUG_REQUEST"
                           , XMLELEMENT
                             (
                               NAME header
                               , XMLFOREST
                                 (
                                   _operationtype AS "TYPE"
                                 )
                             )
                           , XMLFOREST
                             (
                               "ID" AS "DrugRequestID"
                               , bdr.requestdate AS "RequestDate"
                               , bdr.number AS "Number"
                               , bdr.active AS "Active"
                               , bdr.additional AS "Additional"
                               , bdr.beginperiod AS "BeginPeriod"
                               , bdr.endperiod AS "EndPeriod"
                               , bdr.name AS "Name"
                               , dr.importfilename AS "ImportFileName"
                               , bdr.comment AS "Comment"
                               , lp.lpucode AS "LpuMain"
                               , drm.drugrequestid AS "DrugRequestID"
                               , dr.requesttype AS "RequestType"
                               , br.branchcode AS "BranchCode"
                               , bdr.status AS "Status"
                               , bdr.limit AS "Limit"
                               , er.codeehr AS "EhrRusEj36pmtjqk"
                               , rb.currversion AS "EhrRusEj36pmtjqkVer"
                               , ervzn.idehr AS "EhrRusVzn17"
                               , rbvzn.currversion AS "EhrRusVzn17Ver"
                             )
                           , --собираем DRUG_DEMAND
                           (SELECT XMLAGG
                                   (
                                       XMLELEMENT
                                       (
                                           NAME "DRUG_DEMAND"
                                           , XMLFOREST
                                             (
                                               dd.drugdemandid AS "DrugDemandID"
                                               , dd.demandonperiod AS "DemandOnPeriod"
                                               , dd.requestquantity AS "RequestQuantity"
                                               , dd.requestcode AS "RequestCode"
                                               , dd.lsfo AS "LSFO"
                                               , dd.comment AS "Comment"
                                               , dd.drugrequest AS "DrugRequest"
                                               , dd.sectioncode AS "SectionCode"
                                               , dd.requestcatalog AS "RequestCatalog"
                                             )
                                           , --собираем PHARMACY_DRUG_DEMAND
                                           (SELECT XMLAGG
                                                   (
                                                       XMLELEMENT
                                                       (
                                                           NAME "PHARMACY_DRUG_DEMAND"
                                                           , XMLFOREST
                                                             (
                                                               pdd.pharmacydrugdemandid AS "PharmacyDrugDemandID"
                                                               , pdd.demandonperiod AS "DemandOnPeriod"
                                                               , pdd.requestquantity AS "RequestQuantity"
                                                               , pdd.mont1 AS "Mont1"
                                                               , pdd.mont2 AS "Mont2"
                                                               , pdd.mont3 AS "Mont3"
                                                               , pdd.mont4 AS "Mont4"
                                                               , pdd.mont5 AS "Mont5"
                                                               , pdd.mont6 AS "Mont6"
                                                               , pdd.mont7 AS "Mont7"
                                                               , pdd.mont8 AS "Mont8"
                                                               , pdd.mont9 AS "Mont9"
                                                               , pdd.mont10 AS "Mont10"
                                                               , pdd.mont11 AS "Mont11"
                                                               , pdd.mont12 AS "Mont12"
                                                               , pdd.drugdemand AS "DrugDemand"
                                                               , pdd.pharmacy AS "Pharmacy"
                                                             )
                                                           , --собираем PATIENT_DRUG_DEMAND
                                                           (SELECT XMLAGG
                                                                   (
                                                                       XMLELEMENT
                                                                       (
                                                                           NAME "PATIENT_DRUG_DEMAND"
                                                                           , XMLFOREST
                                                                             (
                                                                               pat.patientdrugdemandid AS
                                                                               "PatientDrugDemandID"
                                                                               , pat.pharmacydrugdemand AS
                                                                               "PharmacyDrugDemand"
                                                                               , pat.demandonperiod AS "DemandOnPeriod"
                                                                               , pat.requestquantity AS
                                                                               "RequestQuantity"
                                                                               , pat.mont1 AS "Mont1"
                                                                               , pat.mont2 AS "Mont2"
                                                                               , pat.mont3 AS "Mont3"
                                                                               , pat.mont4 AS "Mont4"
                                                                               , pat.mont5 AS "Mont5"
                                                                               , pat.mont6 AS "Mont6"
                                                                               , pat.mont7 AS "Mont7"
                                                                               , pat.mont8 AS "Mont8"
                                                                               , pat.mont9 AS "Mont9"
                                                                               , pat.mont10 AS "Mont10"
                                                                               , pat.mont11 AS "Mont11"
                                                                               , pat.mont12 AS "Mont12"
                                                                               , p.birthday AS "Birthday"
                                                                               , p.firstname AS "FirstName"
                                                                               , p.fathername AS "FatherName"
                                                                               , p.lastname AS "LastName"
                                                                               , esex.code AS "Sex"
                                                                               , b.benefitcode AS "BenefitCode"
                                                                               , pat.signa AS "Signa"
                                                                               , worker.fio AS "Post"
                                                                               , b.benefitviews AS "BenefitsType"
                                                                               , pat.mkb AS "MKB"
                                                                               , pat.comment AS "Comment"
                                                                               , p.snils AS "Snils"
                                                                               , CASE
                                                                                     WHEN p.docseries = '0' THEN NULL
                                                                                     ELSE p.docseries
                                                                                 END AS "DocSeries"
                                                                               , CASE
                                                                                     WHEN p.docnumber = '0' THEN NULL
                                                                                     ELSE p.docnumber
                                                                                 END AS "DocNumber"
                                                                               ,
                                                                               TO_CHAR(p.docissuedate, 'YYYY-MM-DD') AS
                                                                               "DocIssueDate"
                                                                               , p.docissueorg AS "DocIssueOrg"
                                                                               , edt.code AS "DocType"
                                                                               , polis.number AS "PolicyNumber"
                                                                               , polis.serial AS "PolicySerial"
                                                                             )
                                                                       )
                                                                   )
                                                            FROM akuz.t_patient_drug_demand pat
                                                            JOIN      akuz.t_patient        p ON p.patientid = pat.patient
                                                            JOIN      vclib.t_enums         edt ON edt.enumid = p.doctype
                                                            JOIN      vclib.t_enums         esex ON esex.enumid = p.sex
                                                            LEFT JOIN akuz.t_benefits       b ON b.benefitsid = pat.benefits
                                                            LEFT JOIN akuz.t_post           post ON post.resourceid = pat.post
                                                            LEFT JOIN akuz.t_worker         worker ON post.worker = worker.workerid
                                                            LEFT JOIN akuz.t_policy         polis
                                                                          ON polis.policyid = dbo.fn_getomspolicy(pat.patient, "dateTime")
                                                            WHERE pat.pharmacydrugdemand = pdd.pharmacydrugdemandid
                                                           )
                                                       )
                                                   )
                                            FROM akuz.t_pharmacy_drug_demand pdd
                                            WHERE dd.drugdemandid = pdd.drugdemand
                                           )
                                       )
                                   )
                            FROM akuz.t_drug_demand dd
                            WHERE dd.drugrequest = dr.drugrequestid
                           )
                       )
                INTO _result
                FROM akuz.t_drug_request           dr
                JOIN      akuz.t_base_drug_request bdr ON bdr.drugrequestid = dr.drugrequestid
                JOIN      akuz.t_lpu_main          lp ON lp.lpuid = bdr.lpumain
                LEFT JOIN akuz.t_branch            br ON br.lpuid = dr.branch
                LEFT JOIN akuz.t_drug_request      drm ON drm.drugrequestid = bdr.drugrequestmain
                LEFT JOIN ehr.t_ehr_rus_ej36pmtjqk er ON er.ehrrusid = bdr.ehrrusej36pmtjqk
                LEFT JOIN ehr.t_ehr_rus_ref_books  rb ON rb.ehrrusrefbooksid = er.ehrrusrefbooks
                LEFT JOIN ehr.t_ehr_rus_vzn17      ervzn ON ervzn.ehrrusid = bdr.ehrrusvzn17
                LEFT JOIN ehr.t_ehr_rus_ref_books  rbvzn ON rbvzn.ehrrusrefbooksid = ervzn.ehrrusrefbooks
                WHERE dr.drugrequestid = "ID";
            END IF;
            
            -- Отправка
            INSERT INTO
                akuz.t_rabbit_send_object
            (rabbitsendobjectid,
             queuename,
             content,
             messageid,
             headers,
             contentencoding,
             type,
             userid,
             state,
             createdatetime,
             ownerid)
            SELECT gen_random_uuid(),
                   "QueueName",
                   converttexttobinaryzip(_result, ''),
                   NULL,
                   NULL,
                   _contentencoding,
                   _type,
                   "User",
                   0,
                   "dateTime",
                   _ownerid;
        ELSE
            IF "Xml" IS NOT NULL
            THEN
                -- Получаем тип операции
                _operationtype := (XPATH('/DRUG_REQUEST/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);
                
                
                -- Обработка операции
                IF _operationtype = _delete
                THEN
                    RAISE EXCEPTION USING MESSAGE = 'Операция не поддерживается';
                    RETURN;
                ELSE
                    -- Generate table #DRUG_REQUEST
                    DROP TABLE IF EXISTS tmp_drug_request;
                    CREATE TEMP TABLE tmp_drug_request
                    AS
                        SELECT (XPATH('/DRUG_REQUEST[1]/DrugRequestID[1]/text()', x))[1]::text::uuid    AS drugrequestid
                             , TO_TIMESTAMP((XPATH('/DRUG_REQUEST[1]/RequestDate[1]/text()', x))[1]::text,
                                            'YYYY-MM-DD')                                               AS requestdate
                             , (XPATH('/DRUG_REQUEST[1]/Active[1]/text()', x))[1]::text::boolean        AS active
                             , (XPATH('/DRUG_REQUEST[1]/Additional[1]/text()', x))[1]::text::boolean    AS additional
                             , (XPATH('/DRUG_REQUEST[1]/Number[1]/text()', x))[1]::text::int            AS number
                             , TO_TIMESTAMP((XPATH('/DRUG_REQUEST[1]/BeginPeriod[1]/text()', x))[1]::text,
                                            'YYYY-MM-DD')                                               AS beginperiod
                             , TO_TIMESTAMP((XPATH('/DRUG_REQUEST[1]/EndPeriod[1]/text()', x))[1]::text,
                                            'YYYY-MM-DD')                                               AS endperiod
                             , (XPATH('/DRUG_REQUEST[1]/Name[1]/text()', x))[1]::varchar(255)           AS name
                             , (XPATH('/DRUG_REQUEST[1]/ImportFileName[1]/text()', x))[1]::varchar(255) AS importfilename
                             , (XPATH('/DRUG_REQUEST[1]/Comment[1]/text()', x))[1]::varchar(8000)       AS comment
                             , (XPATH('/DRUG_REQUEST[1]/LpuMain[1]/text()', x))[1]::varchar(6)          AS lpumain
                             , (XPATH('/DRUG_REQUEST[1]/DrugRequestMain[1]/text()', x))[1]::text::uuid  AS drugrequestmain
                             , (XPATH('/DRUG_REQUEST[1]/RequestType[1]/text()', x))[1]::text::int       AS requesttype
                             , (XPATH('/DRUG_REQUEST[1]/Status[1]/text()', x))[1]::text::int            AS status
                             , (XPATH('/DRUG_REQUEST[1]/Limit[1]/text()', x))[1]::text::decimal(31, 10) AS limit
                             , (XPATH('/DRUG_REQUEST[1]/BranchCode[1]/text()', x))[1]::varchar(16)      AS branchcode
                        FROM UNNEST(XPATH('/DRUG_REQUEST', "Xml"::xml)) x
                        WHERE _operationtype <> _ftload;
                    
                    -- Generate table #DRUG_DEMAND
                    DROP TABLE IF EXISTS tmp_drug_demand;
                    CREATE TEMP TABLE tmp_drug_demand
                    AS
                        SELECT (XPATH('/DRUG_DEMAND[1]/DrugDemandID[1]/text()', x))[1]::text::uuid              AS drugdemandid
                             , (XPATH('/DRUG_DEMAND[1]/DemandOnPeriod[1]/text()', x))[1]::text::decimal(12, 2)  AS demandonperiod
                             , (XPATH('/DRUG_DEMAND[1]/RequestQuantity[1]/text()', x))[1]::text::decimal(12, 2) AS requestquantity
                             , (XPATH('/DRUG_DEMAND[1]/RequestCode[1]/text()', x))[1]::text::int                AS requestcode
                             , (XPATH('/DRUG_DEMAND[1]/LSFO[1]/text()', x))[1]::text::int                       AS lsfo
                             , (XPATH('/DRUG_DEMAND[1]/Comment[1]/text()', x))[1]::varchar(8000)                AS comment
                             , (XPATH('/DRUG_DEMAND[1]/DrugRequest[1]/text()', x))[1]::text::uuid               AS drugrequest
                             , (XPATH('/DRUG_DEMAND[1]/SectionCode[1]/text()', x))[1]::varchar(255)             AS sectioncode
                             , (XPATH('/DRUG_DEMAND[1]/RequestCatalog[1]/text()', x))[1]::text::uuid            AS requestcatalog
                        FROM UNNEST(XPATH('/DRUG_REQUEST/DRUG_DEMAND', "Xml"::xml)) x
                        WHERE _operationtype <> _ftload;
                    
                    -- Generate table #PATIENT_DRUG_DEMAND
                    DROP TABLE IF EXISTS tmp_patient_drug_demand;
                    CREATE TEMP TABLE tmp_patient_drug_demand
                    AS
                        SELECT (XPATH('/PATIENT_DRUG_DEMAND[1]/PatientDrugDemandID[1]/text()', x))[1]::text::uuid      AS patientdrugdemandid
                             , (XPATH('/PATIENT_DRUG_DEMAND[1]/PharmacyDrugDemand[1]/text()', x))[1]::text::uuid       AS pharmacydrugdemand
                             , (XPATH('/PATIENT_DRUG_DEMAND[1]/DemandOnPeriod[1]/text()', x))[1]::text::decimal(12, 2) AS demandonperiod
                             , TO_TIMESTAMP((XPATH('/PATIENT_DRUG_DEMAND[1]/Birthday[1]/text()', x))[1]::text,
                                            'YYYY-MM-DD')                                                              AS birthday
                             , (XPATH('/PATIENT_DRUG_DEMAND[1]/FirstName[1]/text()', x))[1]::varchar(40)               AS firstname
                             , (XPATH('/PATIENT_DRUG_DEMAND[1]/FatherName[1]/text()', x))[1]::varchar(40)              AS fathername
                             , (XPATH('/PATIENT_DRUG_DEMAND[1]/LastName[1]/text()', x))[1]::varchar(40)                AS lastname
                             , (XPATH('/PATIENT_DRUG_DEMAND[1]/BenefitCode[1]/text()', x))[1]::text::int               AS benefitcode
                             , (XPATH('/PATIENT_DRUG_DEMAND[1]/BenefitType[1]/text()', x))[1]::text::int               AS benefittype
                             , (XPATH('/PATIENT_DRUG_DEMAND[1]/MKB[1]/text()', x))[1]::varchar(10)                     AS mkb
                             , (XPATH('/PATIENT_DRUG_DEMAND[1]/Comment[1]/text()', x))[1]::varchar(8000)               AS comment
                             , (XPATH('/PATIENT_DRUG_DEMAND[1]/Snils[1]/text()', x))[1]::varchar(14)                   AS snils
                        FROM UNNEST(XPATH('/DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND/PATIENT_DRUG_DEMAND',
                                          "Xml"::xml)) x
                        WHERE _operationtype <> _ftload;
                    
                    -- Generate table #DRUG_REQUEST_COMMENTS
                    DROP TABLE IF EXISTS tmp_drug_request_comments;
                    CREATE TEMP TABLE tmp_drug_request_comments
                    AS
                        SELECT (XPATH('/DRUG_REQUEST_COMMENTS[1]/DrugRequestCommentsID[1]/text()', x))[1]::text::uuid AS drugrequestcommentsid
                             , (XPATH('/DRUG_REQUEST_COMMENTS[1]/Comment[1]/text()', x))[1]::varchar(8000)            AS comment
                             , (XPATH('/DRUG_REQUEST_COMMENTS[1]/RequestCode[1]/text()', x))[1]::text::int            AS requestcode
                             , (XPATH('/DRUG_REQUEST_COMMENTS[1]/Processed[1]/text()', x))[1]::text::int              AS processed
                             , (XPATH('/DRUG_REQUEST_COMMENTS[1]/UserProcessed[1]/text()', x))[1]::text::int          AS userprocessed
                             , (XPATH('/DRUG_REQUEST_COMMENTS[1]/Worker[1]/text()', x))[1]::varchar(500)              AS worker
                             , (XPATH('/DRUG_REQUEST_COMMENTS[1]/DrugRequest[1]/text()', x))[1]::text::uuid           AS drugrequest
                        FROM UNNEST(XPATH('/DRUG_REQUEST/DRUG_REQUEST_COMMENTS', "Xml"::xml)) x
                        WHERE _operationtype <> _ftload;
                    
                    UPDATE akuz.t_base_drug_request dr
                    SET comment = dre.comment
                      , status  = dre.status
                      , "limit" = dre.limit
                    FROM tmp_drug_request dre
                    WHERE dre.drugrequestid = dr.drugrequestid;
                    
                    UPDATE akuz.t_drug_demand dd
                    SET comment = ddl.comment
                    FROM tmp_drug_demand ddl
                    WHERE ddl.drugdemandid = dd.drugdemandid;
                    
                    UPDATE akuz.t_patient_drug_demand pdd
                    SET comment = pddl.comment
                    FROM tmp_patient_drug_demand pddl
                    WHERE pddl.patientdrugdemandid = pdd.patientdrugdemandid;
                    
                    UPDATE akuz.t_drug_request_comments drc
                    SET comment   = rc.comment
                      , processed = rc.processed
                    FROM tmp_drug_request_comments rc
                    WHERE rc.drugrequestcommentsid = rc.drugrequestcommentsid;
                    
                    DELETE
                    FROM akuz.t_drug_request_comments drc
                    WHERE NOT EXISTS(SELECT 1
                                     FROM tmp_drug_request_comments rc
                                     WHERE drc.drugrequestcommentsid = rc.drugrequestcommentsid
                                    );
                    
                    INSERT INTO
                        akuz.t_drug_request_comments(drugrequestcommentsid,
                                                     comment,
                                                     requestcode,
                                                     processed,
                                                     userprocessed,
                                                     worker,
                                                     drugrequest)
                    SELECT drugrequestcommentsid, comment, requestcode, processed, userprocessed, worker, drugrequest
                    FROM tmp_drug_request_comments drc
                    WHERE NOT EXISTS(SELECT 1
                                     FROM akuz.t_drug_request_comments rc
                                     WHERE drc.drugrequestcommentsid = rc.drugrequestcommentsid
                                    );
                END IF;
            END IF;
        END IF;
    END;
END;
$$;