DROP PROCEDURE IF EXISTS public.sp_rds_drugrequest;

CREATE OR REPLACE PROCEDURE public.sp_rds_drugrequest(
    "Xml" text DEFAULT NULL,
    "IsNew" boolean DEFAULT TRUE,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT 'RDSSync',
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml"::xml;
    p_isnew           boolean      := "IsNew";
    p_id              uuid         := "ID";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'DRUG_REQUEST';
    v_operationtype   varchar(6);
    v_ownerid         uuid         := gen_random_uuid();
    v_lpucode         varchar(6);
    c_insert          varchar(6)   := 'Insert';
    c_update          varchar(6)   := 'Update';
    c_delete          varchar(6)   := 'Delete';
    c_ftload          varchar(6)   := 'FTLoad';
    c_ftsend          varchar(6)   := 'FTSend';
BEGIN
    
    IF (p_xml IS NULL) THEN
        
        IF (p_id IS NULL) THEN
            v_operationtype = c_ftload;
        ELSIF (p_isnew = TRUE) THEN
            v_operationtype = c_insert;
        ELSIF (p_isnew = FALSE AND EXISTS(SELECT 1 FROM rds.t_drug_request dr WHERE dr.drugrequestid = p_id)) THEN
            v_operationtype = c_update;
        ELSIF (p_isnew = FALSE AND NOT EXISTS (SELECT 1 FROM rds.t_drug_request dr WHERE dr.drugrequestid = p_id)) THEN
            
            v_operationtype = c_delete;
            SELECT XMLELEMENT(
                       NAME "DRUG_REQUEST",
                       XMLELEMENT(
                           NAME "header",
                           XMLFOREST(
                               v_operationtype AS "TYPE",
                               p_id AS "DrugRequestID"
                           )
                       )
                   )
            INTO v_result;
        
        
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            SELECT XMLELEMENT(
                       NAME "DRUG_REQUEST",
                       XMLELEMENT(
                           NAME "header",
                           XMLFOREST(c_ftload AS "TYPE")
                       ),
                       XMLELEMENT(
                           NAME "body",
                           XMLELEMENT(
                               NAME "DRUG_REQUEST_LIST",
                               (SELECT XMLAGG(
                                           XMLELEMENT(
                                               NAME "DRUG_REQUEST",
                                               XMLFOREST(
                                                   dr.drugrequestid AS "DrugRequestID",
                                                   TO_CHAR(dr.requestdate, 'YYYY-MM-DD') AS "RequestDate",
                                                   dr.number AS "Number",
                                                   dr.active AS "Active",
                                                   TO_CHAR(dr.beginperiod, 'YYYY-MM-DD') AS "BeginPeriod",
                                                   TO_CHAR(dr.endperiod, 'YYYY-MM-DD') AS "EndPeriod",
                                                   dr.name AS "Name",
                                                   dr.importfilename AS "ImportFileName",
                                                   dr.comment AS "Comment",
                                                   lp.mcod AS "LpuMain",
                                                   drm.drugrequestid AS "DrugRequestMain",
                                                   dr.requesttype AS "RequestType",
                                                   dr.status AS "Status",
                                                   dr.limit AS "Limit",
                                                   br.branchcode AS "BranchCode",
                                                   dr.additional AS "Additional",
                                                   TO_CHAR(dr.lasteditdate, 'YYYY-MM-DD') AS "LastEditDate",
                                                   er.codeehr AS "EhrRusEj36pmtjqk",
                                                   rb.currversion AS "EhrRusEj36pmtjqkVer",
                                                   ervzn.idehr AS "EhrRusVzn17",
                                                   rbvzn.currversion AS "EhrRusVzn17Ver"
                                               ),
                                               (SELECT XMLAGG(
                                                           XMLELEMENT(
                                                               NAME "DRUG_REQUEST_COMMENTS",
                                                               XMLFOREST(
                                                                   drc.drugrequestcommentsid AS "DrugRequestCommentsID",
                                                                   drc.comment AS "Comment",
                                                                   drc.requestcode AS "RequestCode",
                                                                   drc.processed AS "Processed",
                                                                   drc.userprocessed AS "UserProcessed",
                                                                   idd.identityname AS "Worker",
                                                                   drc.drugrequest AS "DrugRequest"
                                                               )
                                                           )
                                                       )
                                                FROM rds.t_drug_request_comments drc
                                                JOIN rds.t_users u ON u.userid = drc.worker
                                                JOIN vclib.t_users vu ON vu.userid = u.userid
                                                JOIN vclib.t_identity idd ON vu.identity = idd.identityid
                                                WHERE drc.drugrequest = dr.drugrequestid
                                               ),
                                               (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.persrequest AS "PersRequest",
                                                                   dd.drugapplicationlistitem 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",
                                                                                                   TO_CHAR(pat.birthday, 'YYYY-MM-DD') AS
                                                                                                   "Birthday",
                                                                                                   pat.firstname AS
                                                                                                   "FirstName",
                                                                                                   pat.fathername AS
                                                                                                   "FatherName",
                                                                                                   pat.lastname AS
                                                                                                   "LastName",
                                                                                                   esex.code AS "Sex",
                                                                                                   b.benefitcode AS
                                                                                                   "BenefitCode",
                                                                                                   b.benefitviews AS
                                                                                                   "BenefitsType",
                                                                                                   pat.mkb AS "MKB",
                                                                                                   pat.comment AS
                                                                                                   "Comment",
                                                                                                   pat.signa AS "Signa",
                                                                                                   pat.post AS "Post",
                                                                                                   pat.snils AS "Snils",
                                                                                                   CASE
                                                                                                       WHEN pat.docseries = '0'
                                                                                                           THEN NULL
                                                                                                       ELSE pat.docseries
                                                                                                   END AS "DocSeries",
                                                                                                   CASE
                                                                                                       WHEN pat.docnumber = '0'
                                                                                                           THEN NULL
                                                                                                       ELSE pat.docnumber
                                                                                                   END AS "DocNumber",
                                                                                                   TO_CHAR(pat.docissuedate, 'YYYY-MM-DD') AS
                                                                                                   "DocIssueDate",
                                                                                                   pat.docissueorg AS
                                                                                                   "DocIssueOrg",
                                                                                                   edt.code AS
                                                                                                   "DocType",
                                                                                                   pat.omsnumber AS
                                                                                                   "PolicyNumber",
                                                                                                   pat.omsserial AS
                                                                                                   "PolicySerial"
                                                                                               )
                                                                                           )
                                                                                       )
                                                                                FROM rds.t_patient_drug_demand pat
                                                                                LEFT JOIN vclib.t_enums edt ON edt.enumid = pat.doctype
                                                                                LEFT JOIN rds.t_benefits b ON b.benefitsid = pat.benefits
                                                                                LEFT JOIN vclib.t_enums esex ON esex.enumid = pat.sex
                                                                                WHERE pat.pharmacydrugdemand = pdd.pharmacydrugdemandid
                                                                               )
                                                                           )
                                                                       )
                                                                FROM rds.t_pharmacy_drug_demand pdd
                                                                WHERE pdd.drugdemand = dd.drugdemandid
                                                               )
                                                           )
                                                       )
                                                FROM rds.t_drug_demand dd
                                                WHERE dd.drugrequest = dr.drugrequestid
                                               )
                                           )
                                       )
                                FROM rds.t_drug_request dr
                                JOIN rds.t_lpu_main lp ON lp.mcod = dr.lpumain
                                LEFT JOIN rds.t_branch br ON br.branchid = dr.branch
                                LEFT JOIN rds.t_drug_request drm ON drm.drugrequestid = dr.drugrequestmain
                                LEFT JOIN ehr.t_ehr_rus_ej36pmtjqk er ON er.ehrrusid = dr.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 = dr.ehrrusvzn17
                                LEFT JOIN ehr.t_ehr_rus_ref_books rbvzn ON rbvzn.ehrrusrefbooksid = ervzn.ehrrusrefbooks
                               )
                           )
                       )
                   )
            INTO v_result;
        
        ELSIF (v_operationtype <> c_delete) THEN
            
            SELECT XMLELEMENT(
                       NAME "DRUG_REQUEST",
                       XMLELEMENT(
                           NAME "header",
                           XMLFOREST(v_operationtype AS "TYPE")
                       ),
                       XMLELEMENT(
                           NAME "body",
                           XMLFOREST(
                               dr.drugrequestid AS "DrugRequestID",
                               TO_CHAR(dr.requestdate, 'YYYY-MM-DD') AS "RequestDate",
                               dr.number AS "Number",
                               dr.active AS "Active",
                               TO_CHAR(dr.beginperiod, 'YYYY-MM-DD') AS "BeginPeriod",
                               TO_CHAR(dr.endperiod, 'YYYY-MM-DD') AS "EndPeriod",
                               dr.name AS "Name",
                               dr.importfilename AS "ImportFileName",
                               dr.comment AS "Comment",
                               lp.mcod AS "LpuMain",
                               drm.drugrequestid AS "DrugRequestMain",
                               dr.requesttype AS "RequestType",
                               dr.status AS "Status",
                               dr.limit AS "Limit",
                               br.branchcode AS "BranchCode",
                               dr.additional AS "Additional",
                               TO_CHAR(dr.lasteditdate, 'YYYY-MM-DD') AS "LastEditDate",
                               er.codeehr AS "EhrRusEj36pmtjqk",
                               rb.currversion AS "EhrRusEj36pmtjqkVer",
                               ervzn.idehr AS "EhrRusVzn17",
                               rbvzn.currversion AS "EhrRusVzn17Ver"
                           ),
                           (SELECT XMLAGG(
                                       XMLELEMENT(
                                           NAME "DRUG_REQUEST_COMMENTS",
                                           XMLFOREST(
                                               drc.drugrequestcommentsid AS "DrugRequestCommentsID",
                                               drc.comment AS "Comment",
                                               drc.requestcode AS "RequestCode",
                                               drc.processed AS "Processed",
                                               drc.userprocessed AS "UserProcessed",
                                               idd.identityname AS "Worker",
                                               drc.drugrequest AS "DrugRequest"
                                           )
                                       )
                                   )
                            FROM rds.t_drug_request_comments drc
                            JOIN rds.t_users u ON u.userid = drc.users
                            JOIN vclib.t_users vu ON vu.userid = u.userid
                            JOIN vclib.t_identity idd ON idd.identityid = vu.identity
                            WHERE drc.drugrequest = dr.drugrequestid
                           ),
                           (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.persrequest AS "PersRequest",
                                               dd.drugapplicationlistitem 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",
                                                                               TO_CHAR(pat.birthday, 'YYYY-MM-DD') AS
                                                                               "Birthday",
                                                                               pat.firstname AS "FirstName",
                                                                               pat.fathername AS "FatherName",
                                                                               pat.lastname AS "LastName",
                                                                               esex.code AS "Sex",
                                                                               b.benefitcode AS "BenefitCode",
                                                                               b.benefitviews AS "BenefitsType",
                                                                               pat.mkb AS "MKB",
                                                                               pat.comment AS "Comment",
                                                                               pat.signa AS "Signa",
                                                                               pat.post AS "Post",
                                                                               pat.snils AS "Snils",
                                                                               CASE
                                                                                   WHEN pat.docseries = '0' THEN NULL
                                                                                   ELSE pat.docseries
                                                                               END AS "DocSeries",
                                                                               CASE
                                                                                   WHEN pat.docnumber = '0' THEN NULL
                                                                                   ELSE pat.docnumber
                                                                               END AS "DocNumber",
                                                                               TO_CHAR(pat.docissuedate, 'YYYY-MM-DD') AS
                                                                               "DocIssueDate",
                                                                               pat.docissueorg AS "DocIssueOrg",
                                                                               edt.code AS "DocType",
                                                                               pat.omsnumber AS "PolicyNumber",
                                                                               pat.omsserial AS "PolicySerial"
                                                                           )
                                                                       )
                                                                   )
                                                            FROM rds.t_patient_drug_demand pat
                                                            LEFT JOIN vclib.t_enums edt ON edt.enumid = pat.doctype
                                                            LEFT JOIN rds.t_benefits b ON b.benefitsid = pat.benefits
                                                            LEFT JOIN vclib.t_enums esex ON esex.enumid = pat.sex
                                                            WHERE pat.pharmacydrugdemand = pdd.pharmacydrugdemandid
                                                           )
                                                       )
                                                   )
                                            FROM rds.t_pharmacy_drug_demand pdd
                                            WHERE pdd.drugdemand = dd.drugdemandid
                                           )
                                       )
                                   )
                            FROM rds.t_drug_demand dd
                            WHERE dd.drugrequest = dr.drugrequestid
                              AND dd.comment IS NOT NULL
                           )
                       )
                   )
            INTO v_result
            FROM rds.t_drug_request dr
            JOIN rds.t_lpu_main lp ON lp.mcod = dr.lpumain
            LEFT JOIN rds.t_branch br ON br.branchid = dr.branch
            LEFT JOIN rds.t_drug_request drm ON drm.drugrequestid = dr.drugrequestmain
            LEFT JOIN ehr.t_ehr_rus_ej36pmtjqk er ON er.ehrrusid = dr.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 = dr.ehrrusvzn17
            LEFT JOIN ehr.t_ehr_rus_ref_books rbvzn ON rbvzn.ehrrusrefbooksid = ervzn.ehrrusrefbooks
            WHERE dr.drugrequestid = p_id;
        
        END IF;
        
        SELECT dr.lpumain
        INTO v_lpucode
        FROM rds.t_drug_request dr
        WHERE dr.drugrequestid = p_id;
        
        
        INSERT INTO
            rds.t_rabbit_send_object (rabbitsendobjectid,
                                      queuename,
                                      content,
                                      messageid,
                                      headers,
                                      contentencoding,
                                      type,
                                      userid,
                                      state,
                                      createdatetime,
                                      ownerid)
        SELECT gen_random_uuid(),
               s.servername,
               public.converttexttobinaryzip(v_result, ''),
               NULL,
               NULL,
               v_contentencoding,
               v_type,
               p_user,
               0,
               p_datetime,
               v_ownerid
        FROM rds.t_sync_servers s
        WHERE s.servertype <> 1
          AND EXISTS (SELECT 1
                      FROM rds.t_sync_servers s1
                      JOIN rds.t_servers_link sl ON sl.syncservers = s1.syncserversid
                      JOIN rds.t_servers_link_lpu ll ON ll.serverslinkid = sl.serverslinkid
                      JOIN rds.t_lpu_main lp ON lp.mcod = ll.lpumain
                      WHERE lp.mcod = v_lpucode
                        AND s1.syncserversid = s.syncserversid
                     );
    
    ELSIF (p_xml IS NOT NULL) THEN
        
        SELECT (XPATH('/DRUG_REQUEST/header/TYPE/text()', p_xml))[1]::text
        INTO v_operationtype;
        
        IF (v_operationtype = c_ftsend) THEN
            CREATE TEMP TABLE temp_drug_request_list AS
                SELECT (XPATH('/DRUG_REQUEST/DrugRequestID/text()', x))[1]::text::uuid    AS drugrequestid,
                       (XPATH('/DRUG_REQUEST/RequestDate/text()', x))[1]::text::timestamp AS requestdate,
                       (XPATH('/DRUG_REQUEST/Active/text()', x))[1]::text::boolean        AS active,
                       (XPATH('/DRUG_REQUEST/Number/text()', x))[1]::text::integer        AS number,
                       (XPATH('/DRUG_REQUEST/BeginPeriod/text()', x))[1]::text::timestamp AS beginperiod,
                       (XPATH('/DRUG_REQUEST/EndPeriod/text()', x))[1]::text::timestamp   AS endperiod,
                       (XPATH('/DRUG_REQUEST/Name/text()', x))[1]::text                   AS name,
                       (XPATH('/DRUG_REQUEST/ImportFileName/text()', x))[1]::text         AS importfilename,
                       (XPATH('/DRUG_REQUEST/Comment/text()', x))[1]::text                AS comment,
                       (XPATH('/DRUG_REQUEST/LpuMain/text()', x))[1]::text                AS lpumain,
                       (XPATH('/DRUG_REQUEST/DrugRequestMain/text()', x))[1]::text::uuid  AS drugrequestmain,
                       (XPATH('/DRUG_REQUEST/RequestType/text()', x))[1]::text::integer   AS requesttype,
                       (XPATH('/DRUG_REQUEST/Status/text()', x))[1]::text::integer        AS status,
                       (XPATH('/DRUG_REQUEST/Limit/text()', x))[1]::text::numeric         AS "limit",
                       (XPATH('/DRUG_REQUEST/BranchCode/text()', x))[1]::text             AS branchcode,
                       (XPATH('/DRUG_REQUEST/LastEditDate/text()', x))[1]::text::numeric  AS lasteditdate,
                       (XPATH('/DRUG_REQUEST/Additional/text()', x))[1]::text::boolean    AS additional,
                       (XPATH('/DRUG_REQUEST/EhrRusEj36pmtjqk/text()', x))[1]::text       AS ehrrusej36pmtjqk,
                       (XPATH('/DRUG_REQUEST/EhrRusEj36pmtjqkVer/text()', x))[1]::text    AS ehrrusej36pmtjqkver,
                       (XPATH('/DRUG_REQUEST/EhrRusVzn17/text()', x))[1]::text            AS ehrrusvzn17,
                       (XPATH('/DRUG_REQUEST/EhrRusVzn17Ver/text()', x))[1]::text         AS ehrrusvzn17ver
                FROM UNNEST(XPATH('/DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST', p_xml)) x;
            
            CREATE TEMP TABLE temp_drug_demand_list AS
                SELECT (XPATH('/DRUG_DEMAND/DrugDemandID/text()', x))[1]::text::uuid              AS drugdemandid,
                       (XPATH('/DRUG_DEMAND/DemandOnPeriod/text()', x))[1]::text::numeric(12, 2)  AS demandonperiod,
                       (XPATH('/DRUG_DEMAND/RequestQuantity/text()', x))[1]::text::numeric(12, 2) AS requestquantity,
                       (XPATH('/DRUG_DEMAND/RequestCode/text()', x))[1]::text::integer            AS requestcode,
                       (XPATH('/DRUG_DEMAND/LSFO/text()', x))[1]::text::integer                   AS lsfo,
                       (XPATH('/DRUG_DEMAND/Comment/text()', x))[1]::text                         AS comment,
                       (XPATH('/DRUG_DEMAND/DrugRequest/text()', x))[1]::text::uuid               AS drugrequest,
                       (XPATH('/DRUG_DEMAND/SectionCode/text()', x))[1]::text                     AS sectioncode,
                       (XPATH('/DRUG_DEMAND/PersRequest/text()', x))[1]::text::boolean            AS persrequest,
                       (XPATH('/DRUG_DEMAND/RequestCatalog/text()', x))[1]::text::uuid            AS requestcatalog
                FROM UNNEST(XPATH('/DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST/DRUG_DEMAND', p_xml)) x;
            
            CREATE TEMP TABLE temp_pharmacy_drug_demand_list AS
                SELECT (XPATH('/PHARMACY_DRUG_DEMAND/PharmacyDrugDemandID/text()', x))[1]::text::uuid      AS pharmacydrugdemandid,
                       (XPATH('/PHARMACY_DRUG_DEMAND/DemandOnPeriod/text()', x))[1]::text::numeric(12, 2)  AS demandonperiod,
                       (XPATH('/PHARMACY_DRUG_DEMAND/RequestQuantity/text()', x))[1]::text::numeric(12, 2) AS requestquantity,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont1/text()', x))[1]::text::numeric(12, 2)           AS mont1,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont2/text()', x))[1]::text::numeric(12, 2)           AS mont2,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont3/text()', x))[1]::text::numeric(12, 2)           AS mont3,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont4/text()', x))[1]::text::numeric(12, 2)           AS mont4,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont5/text()', x))[1]::text::numeric(12, 2)           AS mont5,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont6/text()', x))[1]::text::numeric(12, 2)           AS mont6,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont7/text()', x))[1]::text::numeric(12, 2)           AS mont7,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont8/text()', x))[1]::text::numeric(12, 2)           AS mont8,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont9/text()', x))[1]::text::numeric(12, 2)           AS mont9,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont10/text()', x))[1]::text::numeric(12, 2)          AS mont10,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont11/text()', x))[1]::text::numeric(12, 2)          AS mont11,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont12/text()', x))[1]::text::numeric(12, 2)          AS mont12,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Pharmacy/text()', x))[1]::text::integer               AS pharmacy,
                       (XPATH('/PHARMACY_DRUG_DEMAND/DrugDemand/text()', x))[1]::text::uuid                AS drugdemand
                FROM UNNEST(XPATH('/DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND',
                                  p_xml)) x;
            
            CREATE TEMP TABLE temp_patient_drug_demand_list AS
                SELECT (XPATH('/PATIENT_DRUG_DEMAND/PatientDrugDemandID/text()', x))[1]::text::uuid       AS patientdrugdemandid,
                       (XPATH('/PATIENT_DRUG_DEMAND/PharmacyDrugDemand/text()', x))[1]::text::uuid        AS pharmacydrugdemand,
                       (XPATH('/PATIENT_DRUG_DEMAND/DemandOnPeriod/text()', x))[1]::text::numeric(12, 2)  AS demandonperiod,
                       (XPATH('/PATIENT_DRUG_DEMAND/RequestQuantity/text()', x))[1]::text::numeric(12, 2) AS requestquantity,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont1/text()', x))[1]::text::numeric(12, 2)           AS mont1,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont2/text()', x))[1]::text::numeric(12, 2)           AS mont2,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont3/text()', x))[1]::text::numeric(12, 2)           AS mont3,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont4/text()', x))[1]::text::numeric(12, 2)           AS mont4,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont5/text()', x))[1]::text::numeric(12, 2)           AS mont5,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont6/text()', x))[1]::text::numeric(12, 2)           AS mont6,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont7/text()', x))[1]::text::numeric(12, 2)           AS mont7,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont8/text()', x))[1]::text::numeric(12, 2)           AS mont8,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont9/text()', x))[1]::text::numeric(12, 2)           AS mont9,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont10/text()', x))[1]::text::numeric(12, 2)          AS mont10,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont11/text()', x))[1]::text::numeric(12, 2)          AS mont11,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont12/text()', x))[1]::text::numeric(12, 2)          AS mont12,
                       (XPATH('/PATIENT_DRUG_DEMAND/Birthday/text()', x))[1]::text::timestamp             AS birthday,
                       (XPATH('/PATIENT_DRUG_DEMAND/FirstName/text()', x))[1]::text                       AS firstname,
                       (XPATH('/PATIENT_DRUG_DEMAND/FatherName/text()', x))[1]::text                      AS fathername,
                       (XPATH('/PATIENT_DRUG_DEMAND/LastName/text()', x))[1]::text                        AS lastname,
                       (XPATH('/PATIENT_DRUG_DEMAND/BenefitCode/text()', x))[1]::text::integer            AS benefitcode,
                       (XPATH('/PATIENT_DRUG_DEMAND/BenefitsType/text()', x))[1]::text::integer           AS benefittype,
                       (XPATH('/PATIENT_DRUG_DEMAND/MKB/text()', x))[1]::text                             AS mkb,
                       (XPATH('/PATIENT_DRUG_DEMAND/Comment/text()', x))[1]::text                         AS comment,
                       (XPATH('/PATIENT_DRUG_DEMAND/Signa/text()', x))[1]::text                           AS signa,
                       (XPATH('/PATIENT_DRUG_DEMAND/Post/text()', x))[1]::text                            AS post,
                       (XPATH('/PATIENT_DRUG_DEMAND/Snils/text()', x))[1]::text                           AS snils,
                       (XPATH('/PATIENT_DRUG_DEMAND/DocSeries/text()', x))[1]::text                       AS docseries,
                       (XPATH('/PATIENT_DRUG_DEMAND/DocNumber/text()', x))[1]::text                       AS docnumber,
                       (XPATH('/PATIENT_DRUG_DEMAND/DocIssueDate/text()', x))[1]::text::timestamp         AS docissuedate,
                       (XPATH('/PATIENT_DRUG_DEMAND/DocIssueOrg/text()', x))[1]::text                     AS docissueorg,
                       (XPATH('/PATIENT_DRUG_DEMAND/DocType/text()', x))[1]::text                         AS doctype,
                       (XPATH('/PATIENT_DRUG_DEMAND/PolicyNumber/text()', x))[1]::text                    AS omsnumber,
                       (XPATH('/PATIENT_DRUG_DEMAND/PolicySerial/text()', x))[1]::text                    AS omsserial,
                       (XPATH('/PATIENT_DRUG_DEMAND/Sex/text()', x))[1]::text                             AS sex
                FROM UNNEST(XPATH(
                    '/DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND/PATIENT_DRUG_DEMAND',
                    p_xml)) x;
            
            CREATE TEMP TABLE temp_drug_request_comments_list AS
                SELECT (XPATH('/DRUG_REQUEST_COMMENTS/DrugRequestCommentsID/text()', x))[1]::text::uuid AS drugrequestcommentsid,
                       (XPATH('/DRUG_REQUEST_COMMENTS/Comment/text()', x))[1]::text                     AS comment,
                       (XPATH('/DRUG_REQUEST_COMMENTS/RequestCode/text()', x))[1]::text::integer        AS requestcode,
                       (XPATH('/DRUG_REQUEST_COMMENTS/Processed/text()', x))[1]::text::integer          AS processed,
                       (XPATH('/DRUG_REQUEST_COMMENTS/UserProcessed/text()', x))[1]::text::integer      AS userprocessed,
                       (XPATH('/DRUG_REQUEST_COMMENTS/Worker/text()', x))[1]::text                      AS worker,
                       (XPATH('/DRUG_REQUEST_COMMENTS/DrugRequest/text()', x))[1]::text::uuid           AS drugrequest
                FROM UNNEST(XPATH('//DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST/DRUG_REQUEST_COMMENTS', p_xml)) x;
            
            UPDATE rds.t_drug_request dr
            SET requestdate      = drl.requestdate,
                lpumain          = COALESCE(lp.mcod, tt.lpumain),
                active           = drl.active,
                drugrequestmain  = drl.drugrequestmain,
                number           = drl.number,
                requesttype      = drl.requesttype,
                importfilename   = drl.importfilename,
                name             = drl.name,
                endperiod        = drl.endperiod,
                beginperiod      = drl.beginperiod,
                status           = drl.status,
                comment          = drl.comment,
                "limit"          = drl.limit,
                branch           = tt.branchid,
                lasteditdate     = COALESCE(drl.lasteditdate, CURRENT_TIMESTAMP),
                additional       = COALESCE(drl.additional, FALSE),
                ehrrusej36pmtjqk = ehr.ehrrusid,
                ehrrusvzn17      = ehrvzn.ehrrusid
            FROM temp_drug_request_list drl
            LEFT JOIN rds.t_lpu_main lp ON lp.mcod = drl.lpumain AND (lp.dloaddate <= drl.requestdate AND
                                                                      (lp.dloenddate IS NULL OR lp.dloenddate >= drl.requestdate))
            LEFT JOIN LATERAL (
                SELECT br.branchid, br.lpumain
                FROM rds.t_branch br
                JOIN rds.t_lpu_main lpp ON lpp.mcod = br.lpumain AND (lpp.dloaddate <= drl.requestdate AND
                                                                      (lpp.dloenddate IS NULL OR lpp.dloenddate >= drl.requestdate))
                WHERE (lp.mcod IS NOT NULL AND br.lpumain = lp.mcod AND br.branchcode = drl.branchcode)
                   OR (lp.mcod IS NULL AND br.branchcode = drl.lpumain)
                LIMIT 1
                ) tt ON TRUE
            LEFT JOIN LATERAL (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_ej36pmtjqk er
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.codeehr = drl.ehrrusej36pmtjqk
                  AND rrb.currversion = drl.ehrrusej36pmtjqkver
                LIMIT 1
                ) ehr ON TRUE
            LEFT JOIN LATERAL (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_vzn17 er
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.idehr = drl.ehrrusvzn17
                  AND rrb.currversion = drl.ehrrusvzn17ver
                LIMIT 1
                ) ehrvzn ON TRUE
            WHERE drl.drugrequestid = dr.drugrequestid;
            
            -- Insert new drug requests
            INSERT INTO
                rds.t_drug_request (drugrequestid,
                                    requestdate,
                                    lpumain,
                                    active,
                                    drugrequestmain,
                                    number,
                                    requesttype,
                                    importfilename,
                                    name,
                                    endperiod,
                                    beginperiod,
                                    status,
                                    comment,
                                    "limit",
                                    branch,
                                    lasteditdate,
                                    additional,
                                    ehrrusej36pmtjqk,
                                    ehrrusvzn17)
            SELECT drl.drugrequestid,
                   drl.requestdate,
                   COALESCE(lp.mcod, tt.lpumain),
                   drl.active,
                   drl.drugrequestmain,
                   drl.number,
                   drl.requesttype,
                   drl.importfilename,
                   drl.name,
                   drl.endperiod,
                   drl.beginperiod,
                   drl.status,
                   drl.comment,
                   drl.limit,
                   tt.branchid,
                   COALESCE(drl.lasteditdate, CURRENT_TIMESTAMP),
                   COALESCE(drl.additional, FALSE),
                   ehr.ehrrusid,
                   ehrvzn.ehrrusid
            FROM temp_drug_request_list drl
            LEFT JOIN rds.t_lpu_main lp ON lp.mcod = drl.lpumain AND (lp.dloaddate <= drl.requestdate AND
                                                                      (lp.dloenddate IS NULL OR lp.dloenddate >= drl.requestdate))
            LEFT JOIN LATERAL (
                SELECT br.branchid, br.lpumain
                FROM rds.t_branch br
                JOIN rds.t_lpu_main lpp ON lpp.mcod = br.lpumain AND (lpp.dloaddate <= drl.requestdate AND
                                                                      (lpp.dloenddate IS NULL OR lpp.dloenddate >= drl.requestdate))
                WHERE (lp.mcod IS NOT NULL AND br.lpumain = lp.mcod AND br.branchcode = drl.branchcode)
                   OR (lp.mcod IS NULL AND br.branchcode = drl.lpumain)
                LIMIT 1
                ) tt ON TRUE
            LEFT JOIN LATERAL (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_ej36pmtjqk er
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.codeehr = drl.ehrrusej36pmtjqk
                  AND rrb.currversion = drl.ehrrusej36pmtjqkver
                LIMIT 1
                ) ehr ON TRUE
            LEFT JOIN LATERAL (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_vzn17 er
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.idehr = drl.ehrrusvzn17
                  AND rrb.currversion = drl.ehrrusvzn17ver
                LIMIT 1
                ) ehrvzn ON TRUE
            WHERE NOT EXISTS (SELECT 1 FROM rds.t_drug_request dr WHERE dr.drugrequestid = drl.drugrequestid);
            
            -- Update drug demands
            UPDATE rds.t_drug_demand dd
            SET drugrequest             = ddl.drugrequest,
                demandonperiod          = ddl.demandonperiod,
                requestquantity         = ddl.requestquantity,
                requestcode             = ddl.requestcode,
                lsfo                    = ddl.lsfo,
                comment                 = ddl.comment,
                sectioncode             = ddl.sectioncode,
                persrequest             = COALESCE(ddl.persrequest, FALSE),
                drugapplicationlistitem = ddl.requestcatalog
            FROM temp_drug_demand_list ddl
            WHERE ddl.drugdemandid = dd.drugdemandid;
            
            -- Insert new drug demands
            INSERT INTO
                rds.t_drug_demand (drugdemandid,
                                   drugrequest,
                                   demandonperiod,
                                   requestquantity,
                                   requestcode,
                                   lsfo,
                                   comment,
                                   sectioncode,
                                   persrequest,
                                   drugapplicationlistitem)
            SELECT ddl.drugdemandid,
                   ddl.drugrequest,
                   ddl.demandonperiod,
                   ddl.requestquantity,
                   ddl.requestcode,
                   ddl.lsfo,
                   ddl.comment,
                   ddl.sectioncode,
                   COALESCE(ddl.persrequest, FALSE),
                   ddl.requestcatalog
            FROM temp_drug_demand_list ddl
            WHERE NOT EXISTS (SELECT 1 FROM rds.t_drug_demand dd WHERE dd.drugdemandid = ddl.drugdemandid);
            
            -- Update pharmacy drug demands
            UPDATE rds.t_pharmacy_drug_demand pdd
            SET drugdemand      = pddl.drugdemand,
                demandonperiod  = pddl.demandonperiod,
                pharmacy        = pddl.pharmacy,
                requestquantity = pddl.requestquantity,
                mont1           = pddl.mont1,
                mont2           = pddl.mont2,
                mont3           = pddl.mont3,
                mont4           = pddl.mont4,
                mont5           = pddl.mont5,
                mont6           = pddl.mont6,
                mont7           = pddl.mont7,
                mont8           = pddl.mont8,
                mont9           = pddl.mont9,
                mont10          = pddl.mont10,
                mont11          = pddl.mont11,
                mont12          = pddl.mont12
            FROM temp_pharmacy_drug_demand_list pddl
            WHERE pddl.pharmacydrugdemandid = pdd.pharmacydrugdemandid;
            
            -- Insert new pharmacy drug demands
            INSERT INTO
                rds.t_pharmacy_drug_demand (pharmacydrugdemandid,
                                            drugdemand,
                                            demandonperiod,
                                            pharmacy,
                                            requestquantity,
                                            mont1,
                                            mont2,
                                            mont3,
                                            mont4,
                                            mont5,
                                            mont6,
                                            mont7,
                                            mont8,
                                            mont9,
                                            mont10,
                                            mont11,
                                            mont12)
            SELECT pddl.pharmacydrugdemandid,
                   pddl.drugdemand,
                   pddl.demandonperiod,
                   pddl.pharmacy,
                   pddl.requestquantity,
                   pddl.mont1,
                   pddl.mont2,
                   pddl.mont3,
                   pddl.mont4,
                   pddl.mont5,
                   pddl.mont6,
                   pddl.mont7,
                   pddl.mont8,
                   pddl.mont9,
                   pddl.mont10,
                   pddl.mont11,
                   pddl.mont12
            FROM temp_pharmacy_drug_demand_list pddl
            WHERE NOT EXISTS (SELECT 1
                              FROM rds.t_pharmacy_drug_demand pdd
                              WHERE pdd.pharmacydrugdemandid = pddl.pharmacydrugdemandid
                             );
            
            UPDATE rds.t_patient_drug_demand pdd
            SET demandonperiod     = pddl.demandonperiod,
                pharmacydrugdemand = pddl.pharmacydrugdemand,
                comment            = pddl.comment,
                benefits           = (SELECT b.benefitsid
                                      FROM rds.t_benefits b
                                      WHERE b.benefitcode = pddl.benefitcode
                                        AND b.benefitviews = pddl.benefittype
                                      LIMIT 1
                ),
                mkb                = pddl.mkb,
                firstname          = pddl.firstname,
                fathername         = pddl.fathername,
                lastname           = pddl.lastname,
                birthday           = pddl.birthday,
                requestquantity    = pddl.requestquantity,
                mont1              = pddl.mont1,
                mont2              = pddl.mont2,
                mont3              = pddl.mont3,
                mont4              = pddl.mont4,
                mont5              = pddl.mont5,
                mont6              = pddl.mont6,
                mont7              = pddl.mont7,
                mont8              = pddl.mont8,
                mont9              = pddl.mont9,
                mont10             = pddl.mont10,
                mont11             = pddl.mont11,
                mont12             = pddl.mont12,
                signa              = pddl.signa,
                post               = pddl.post,
                snils              = pddl.snils,
                docseries          = pddl.docseries,
                docnumber          = pddl.docnumber,
                docissuedate       = pddl.docissuedate,
                docissueorg        = pddl.docissueorg,
                doctype            = edt.enumid,
                omsnumber          = pddl.omsnumber,
                omsserial          = pddl.omsserial,
                ehrrus5o5e88vhc6   = dul.ehrrus5o5e88vhc6,
                sex                = esex.enumid
            FROM temp_patient_drug_demand_list pddl
            LEFT JOIN vclib.t_enums edt ON edt.enumtypeid = 105 AND edt.code = pddl.doctype
            LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = edt.enumid
            LEFT JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.code = pddl.sex
            WHERE pddl.patientdrugdemandid = pdd.patientdrugdemandid;
            
            INSERT INTO
                rds.t_patient_drug_demand (patientdrugdemandid,
                                           demandonperiod,
                                           pharmacydrugdemand,
                                           comment,
                                           benefits,
                                           mkb,
                                           firstname,
                                           fathername,
                                           lastname,
                                           birthday,
                                           requestquantity,
                                           mont1,
                                           mont2,
                                           mont3,
                                           mont4,
                                           mont5,
                                           mont6,
                                           mont7,
                                           mont8,
                                           mont9,
                                           mont10,
                                           mont11,
                                           mont12,
                                           signa,
                                           post,
                                           snils,
                                           docseries,
                                           docnumber,
                                           docissuedate,
                                           docissueorg,
                                           doctype,
                                           omsnumber,
                                           omsserial,
                                           ehrrus5o5e88vhc6,
                                           sex)
            SELECT pddl.patientdrugdemandid,
                   pddl.demandonperiod,
                   pddl.pharmacydrugdemand,
                   pddl.comment,
                   (SELECT b.benefitsid
                    FROM rds.t_benefits b
                    WHERE b.benefitcode = pddl.benefitcode
                      AND b.benefitviews = pddl.benefittype
                    LIMIT 1
                   ),
                   pddl.mkb,
                   pddl.firstname,
                   pddl.fathername,
                   pddl.lastname,
                   pddl.birthday,
                   pddl.requestquantity,
                   pddl.mont1,
                   pddl.mont2,
                   pddl.mont3,
                   pddl.mont4,
                   pddl.mont5,
                   pddl.mont6,
                   pddl.mont7,
                   pddl.mont8,
                   pddl.mont9,
                   pddl.mont10,
                   pddl.mont11,
                   pddl.mont12,
                   pddl.signa,
                   pddl.post,
                   pddl.snils,
                   pddl.docseries,
                   pddl.docnumber,
                   pddl.docissuedate,
                   pddl.docissueorg,
                   edt.enumid,
                   pddl.omsnumber,
                   pddl.omsserial,
                   dul.ehrrus5o5e88vhc6,
                   esex.enumid
            FROM temp_patient_drug_demand_list pddl
            LEFT JOIN vclib.t_enums edt ON edt.enumtypeid = 105 AND edt.code = pddl.doctype
            LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = edt.enumid
            LEFT JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.code = pddl.sex
            WHERE NOT EXISTS (SELECT 1
                              FROM rds.t_patient_drug_demand pdd
                              WHERE pdd.patientdrugdemandid = pddl.patientdrugdemandid
                             );
            
            UPDATE rds.t_drug_request_comments drc
            SET userprocessed = rc.userprocessed
            FROM temp_drug_request_comments_list rc
            WHERE rc.drugrequestcommentsid = drc.drugrequestcommentsid;
            
            DELETE
            FROM rds.t_patient_drug_demand pdd
                USING rds.t_pharmacy_drug_demand phdd, rds.t_drug_demand dd, temp_drug_request_list dr
            WHERE phdd.pharmacydrugdemandid = pdd.pharmacydrugdemand
              AND dd.drugdemandid = phdd.drugdemand
              AND dr.drugrequestid = dd.drugrequest
              AND NOT EXISTS (SELECT 1
                              FROM temp_patient_drug_demand_list pddl
                              JOIN temp_pharmacy_drug_demand_list phddl
                                  ON phddl.pharmacydrugdemandid = pddl.pharmacydrugdemand
                              JOIN temp_drug_demand_list ddl ON ddl.drugdemandid = phddl.drugdemand
                              WHERE pddl.patientdrugdemandid = pdd.patientdrugdemandid
                                AND dd.drugrequest = ddl.drugrequest
                             );
            
            DELETE
            FROM rds.t_pharmacy_drug_demand pdd
                USING rds.t_drug_demand dd, temp_drug_request_list dr
            WHERE dd.drugdemandid = pdd.drugdemand
              AND dr.drugrequestid = dd.drugrequest
              AND NOT EXISTS (SELECT 1
                              FROM temp_pharmacy_drug_demand_list phddl
                              JOIN temp_drug_demand_list ddl ON ddl.drugdemandid = phddl.drugdemand
                              WHERE phddl.pharmacydrugdemandid = pdd.pharmacydrugdemandid
                                AND dd.drugrequest = ddl.drugrequest
                             );
            
            DELETE
            FROM rds.t_drug_demand dd
                USING temp_drug_request_list dr
            WHERE dr.drugrequestid = dd.drugrequest
              AND NOT EXISTS (SELECT 1
                              FROM temp_drug_demand_list ddl
                              WHERE ddl.drugdemandid = dd.drugdemandid
                                AND dd.drugrequest = ddl.drugrequest
                             );
        
        
        ELSE
            CREATE TEMP TABLE temp_drug_request AS
                SELECT (XPATH('/DRUG_REQUEST/DrugRequestID/text()', x))[1]::text::uuid    AS drugrequestid,
                       (XPATH('/DRUG_REQUEST/RequestDate/text()', x))[1]::text::timestamp AS requestdate,
                       (XPATH('/DRUG_REQUEST/Active/text()', x))[1]::text::boolean        AS active,
                       (XPATH('/DRUG_REQUEST/Number/text()', x))[1]::text::integer        AS number,
                       (XPATH('/DRUG_REQUEST/BeginPeriod/text()', x))[1]::text::timestamp AS beginperiod,
                       (XPATH('/DRUG_REQUEST/EndPeriod/text()', x))[1]::text::timestamp   AS endperiod,
                       (XPATH('/DRUG_REQUEST/Name/text()', x))[1]::text                   AS name,
                       (XPATH('/DRUG_REQUEST/ImportFileName/text()', x))[1]::text         AS importfilename,
                       (XPATH('/DRUG_REQUEST/Comment/text()', x))[1]::text                AS comment,
                       (XPATH('/DRUG_REQUEST/LpuMain/text()', x))[1]::text                AS lpumain,
                       (XPATH('/DRUG_REQUEST/DrugRequestMain/text()', x))[1]::text::uuid  AS drugrequestmain,
                       (XPATH('/DRUG_REQUEST/RequestType/text()', x))[1]::text::integer   AS requesttype,
                       (XPATH('/DRUG_REQUEST/Status/text()', x))[1]::text::integer        AS status,
                       (XPATH('/DRUG_REQUEST/Limit/text()', x))[1]::text::numeric         AS "limit",
                       (XPATH('/DRUG_REQUEST/BranchCode/text()', x))[1]::text             AS branchcode,
                       (XPATH('/DRUG_REQUEST/LastEditDate/text()', x))[1]::text::numeric  AS lasteditdate,
                       (XPATH('/DRUG_REQUEST/Additional/text()', x))[1]::text::boolean    AS additional,
                       (XPATH('/DRUG_REQUEST/EhrRusEj36pmtjqk/text()', x))[1]::text       AS ehrrusej36pmtjqk,
                       (XPATH('/DRUG_REQUEST/EhrRusEj36pmtjqkVer/text()', x))[1]::text    AS ehrrusej36pmtjqkver,
                       (XPATH('/DRUG_REQUEST/EhrRusVzn17/text()', x))[1]::text            AS ehrrusvzn17,
                       (XPATH('/DRUG_REQUEST/EhrRusVzn17Ver/text()', x))[1]::text         AS ehrrusvzn17ver
                FROM UNNEST(XPATH('/DRUG_REQUEST', p_xml)) x;
            
            CREATE TEMP TABLE temp_drug_demand AS
                SELECT (XPATH('/DRUG_DEMAND/DrugDemandID/text()', x))[1]::text::uuid              AS drugdemandid,
                       (XPATH('/DRUG_DEMAND/DemandOnPeriod/text()', x))[1]::text::numeric(12, 2)  AS demandonperiod,
                       (XPATH('/DRUG_DEMAND/RequestQuantity/text()', x))[1]::text::numeric(12, 2) AS requestquantity,
                       (XPATH('/DRUG_DEMAND/RequestCode/text()', x))[1]::text::integer            AS requestcode,
                       (XPATH('/DRUG_DEMAND/LSFO/text()', x))[1]::text::integer                   AS lsfo,
                       (XPATH('/DRUG_DEMAND/Comment/text()', x))[1]::text                         AS comment,
                       (XPATH('/DRUG_DEMAND/DrugRequest/text()', x))[1]::text::uuid               AS drugrequest,
                       (XPATH('/DRUG_DEMAND/SectionCode/text()', x))[1]::text                     AS sectioncode,
                       (XPATH('/DRUG_DEMAND/PersRequest/text()', x))[1]::text::boolean            AS persrequest,
                       (XPATH('/DRUG_DEMAND/RequestCatalog/text()', x))[1]::text::uuid            AS requestcatalog
                FROM UNNEST(XPATH('/DRUG_REQUEST/DRUG_DEMAND', p_xml)) x;
            
            CREATE TEMP TABLE temp_pharmacy_drug_demand AS
                SELECT (XPATH('/PHARMACY_DRUG_DEMAND/PharmacyDrugDemandID/text()', x))[1]::text::uuid      AS pharmacydrugdemandid,
                       (XPATH('/PHARMACY_DRUG_DEMAND/DemandOnPeriod/text()', x))[1]::text::numeric(12, 2)  AS demandonperiod,
                       (XPATH('/PHARMACY_DRUG_DEMAND/RequestQuantity/text()', x))[1]::text::numeric(12, 2) AS requestquantity,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont1/text()', x))[1]::text::numeric(12, 2)           AS mont1,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont2/text()', x))[1]::text::numeric(12, 2)           AS mont2,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont3/text()', x))[1]::text::numeric(12, 2)           AS mont3,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont4/text()', x))[1]::text::numeric(12, 2)           AS mont4,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont5/text()', x))[1]::text::numeric(12, 2)           AS mont5,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont6/text()', x))[1]::text::numeric(12, 2)           AS mont6,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont7/text()', x))[1]::text::numeric(12, 2)           AS mont7,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont8/text()', x))[1]::text::numeric(12, 2)           AS mont8,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont9/text()', x))[1]::text::numeric(12, 2)           AS mont9,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont10/text()', x))[1]::text::numeric(12, 2)          AS mont10,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont11/text()', x))[1]::text::numeric(12, 2)          AS mont11,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Mont12/text()', x))[1]::text::numeric(12, 2)          AS mont12,
                       (XPATH('/PHARMACY_DRUG_DEMAND/Pharmacy/text()', x))[1]::text::integer               AS pharmacy,
                       (XPATH('/PHARMACY_DRUG_DEMAND/DrugDemand/text()', x))[1]::text::uuid                AS drugdemand
                FROM UNNEST(XPATH('/DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND', p_xml)) x;
            
            CREATE TEMP TABLE temp_patient_drug_demand AS
                SELECT (XPATH('/PATIENT_DRUG_DEMAND/PatientDrugDemandID/text()', x))[1]::text::uuid       AS patientdrugdemandid,
                       (XPATH('/PATIENT_DRUG_DEMAND/PharmacyDrugDemand/text()', x))[1]::text::uuid        AS pharmacydrugdemand,
                       (XPATH('/PATIENT_DRUG_DEMAND/DemandOnPeriod/text()', x))[1]::text::numeric(12, 2)  AS demandonperiod,
                       (XPATH('/PATIENT_DRUG_DEMAND/RequestQuantity/text()', x))[1]::text::numeric(12, 2) AS requestquantity,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont1/text()', x))[1]::text::numeric(12, 2)           AS mont1,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont2/text()', x))[1]::text::numeric(12, 2)           AS mont2,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont3/text()', x))[1]::text::numeric(12, 2)           AS mont3,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont4/text()', x))[1]::text::numeric(12, 2)           AS mont4,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont5/text()', x))[1]::text::numeric(12, 2)           AS mont5,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont6/text()', x))[1]::text::numeric(12, 2)           AS mont6,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont7/text()', x))[1]::text::numeric(12, 2)           AS mont7,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont8/text()', x))[1]::text::numeric(12, 2)           AS mont8,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont9/text()', x))[1]::text::numeric(12, 2)           AS mont9,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont10/text()', x))[1]::text::numeric(12, 2)          AS mont10,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont11/text()', x))[1]::text::numeric(12, 2)          AS mont11,
                       (XPATH('/PATIENT_DRUG_DEMAND/Mont12/text()', x))[1]::text::numeric(12, 2)          AS mont12,
                       (XPATH('/PATIENT_DRUG_DEMAND/Birthday/text()', x))[1]::text::timestamp             AS birthday,
                       (XPATH('/PATIENT_DRUG_DEMAND/FirstName/text()', x))[1]::text                       AS firstname,
                       (XPATH('/PATIENT_DRUG_DEMAND/FatherName/text()', x))[1]::text                      AS fathername,
                       (XPATH('/PATIENT_DRUG_DEMAND/LastName/text()', x))[1]::text                        AS lastname,
                       (XPATH('/PATIENT_DRUG_DEMAND/BenefitCode/text()', x))[1]::text::integer            AS benefitcode,
                       (XPATH('/PATIENT_DRUG_DEMAND/BenefitsType/text()', x))[1]::text::integer           AS benefittype,
                       (XPATH('/PATIENT_DRUG_DEMAND/MKB/text()', x))[1]::text                             AS mkb,
                       (XPATH('/PATIENT_DRUG_DEMAND/Comment/text()', x))[1]::text                         AS comment,
                       (XPATH('/PATIENT_DRUG_DEMAND/Signa/text()', x))[1]::text                           AS signa,
                       (XPATH('/PATIENT_DRUG_DEMAND/Post/text()', x))[1]::text                            AS post,
                       (XPATH('/PATIENT_DRUG_DEMAND/Snils/text()', x))[1]::text                           AS snils,
                       (XPATH('/PATIENT_DRUG_DEMAND/DocSeries/text()', x))[1]::text                       AS docseries,
                       (XPATH('/PATIENT_DRUG_DEMAND/DocNumber/text()', x))[1]::text                       AS docnumber,
                       (XPATH('/PATIENT_DRUG_DEMAND/DocIssueDate/text()', x))[1]::text::timestamp         AS docissuedate,
                       (XPATH('/PATIENT_DRUG_DEMAND/DocIssueOrg/text()', x))[1]::text                     AS docissueorg,
                       (XPATH('/PATIENT_DRUG_DEMAND/DocType/text()', x))[1]::text                         AS doctype,
                       (XPATH('/PATIENT_DRUG_DEMAND/PolicyNumber/text()', x))[1]::text                    AS omsnumber,
                       (XPATH('/PATIENT_DRUG_DEMAND/PolicySerial/text()', x))[1]::text                    AS omsserial,
                       (XPATH('/PATIENT_DRUG_DEMAND/Sex/text()', x))[1]::text                             AS sex
                FROM UNNEST(XPATH('/DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND/PATIENT_DRUG_DEMAND', p_xml)) x;
            
            CREATE TEMP TABLE temp_drug_request_comments AS
                SELECT (XPATH('/DRUG_REQUEST_COMMENTS/DrugRequestCommentsID/text()', x))[1]::text::uuid AS drugrequestcommentsid,
                       (XPATH('/DRUG_REQUEST_COMMENTS/Comment/text()', x))[1]::text                     AS comment,
                       (XPATH('/DRUG_REQUEST_COMMENTS/RequestCode/text()', x))[1]::text::integer        AS requestcode,
                       (XPATH('/DRUG_REQUEST_COMMENTS/Processed/text()', x))[1]::text::integer          AS processed,
                       (XPATH('/DRUG_REQUEST_COMMENTS/UserProcessed/text()', x))[1]::text::integer      AS userprocessed,
                       (XPATH('/DRUG_REQUEST_COMMENTS/Worker/text()', x))[1]::text                      AS worker,
                       (XPATH('/DRUG_REQUEST_COMMENTS/DrugRequest/text()', x))[1]::text::uuid           AS drugrequest
                FROM UNNEST(XPATH('/DRUG_REQUEST/DRUG_REQUEST_COMMENTS', p_xml)) x;
            
            IF (v_operationtype = c_delete) THEN
                
                DELETE
                FROM rds.t_patient_drug_demand pdd
                    USING rds.t_pharmacy_drug_demand ppdd, rds.t_drug_demand dd, temp_drug_request drl
                WHERE ppdd.pharmacydrugdemandid = pdd.pharmacydrugdemand
                  AND dd.drugdemandid = ppdd.drugdemand
                  AND drl.drugrequestid
                  AND dd.drugrequest;
                
                DELETE
                FROM rds.t_pharmacy_drug_demand pdd
                    USING rds.t_drug_demand dd, temp_drug_request drl
                WHERE dd.drugdemandid = pdd.drugdemand
                  AND drl.drugrequestid = dd.drugrequest;
                
                DELETE
                FROM rds.t_drug_demand dd
                    USING temp_drug_request drl
                WHERE drl.drugrequestid = dd.drugrequest;
                
                DELETE
                FROM rds.t_drug_request dr
                    USING temp_drug_request drl
                WHERE drl.drugrequestid = dr.drugrequestid;
                
            ELSE
            
                UPDATE rds.t_drug_request dr
            SET
                requestdate = drl.requestdate,
                lpumain = COALESCE(lp.mcod, tt.lpumain),
                active = drl.active,
                drugrequestmain = drl.drugrequestmain,
                number = drl.number,
                requesttype = drl.requesttype,
                importfilename = drl.importfilename,
                name = drl.name,
                endperiod = drl.endperiod,
                beginperiod = drl.beginperiod,
                status = drl.status,
                comment = drl.comment,
                "limit" = drl.limit,
                branch = tt.branchid,
                lasteditdate = COALESCE(drl.lasteditdate, CURRENT_TIMESTAMP),
                additional = COALESCE(drl.additional, FALSE),
                ehrrusej36pmtjqk = ehr.ehrrusid,
                ehrrusvzn17 = ehrvzn.ehrrusid
            FROM temp_drug_request drl
            LEFT JOIN rds.t_lpu_main lp ON lp.mcod = drl.lpumain AND (lp.dloaddate <= drl.requestdate AND (lp.dloenddate IS NULL OR lp.dloenddate >= drl.requestdate))
            LEFT JOIN LATERAL (
                SELECT br.branchid, br.lpumain
                FROM rds.t_branch br
                JOIN rds.t_lpu_main lpp ON lpp.mcod = br.lpumain AND (lpp.dloaddate <= drl.requestdate AND (lpp.dloenddate IS NULL OR lpp.dloenddate >= drl.requestdate))
                WHERE (lp.mcod IS NOT NULL AND br.lpumain = lp.mcod AND br.branchcode = drl.branchcode)
                   OR (lp.mcod IS NULL AND br.branchcode = drl.lpumain)
                LIMIT 1
            ) tt ON TRUE
            LEFT JOIN LATERAL (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_ej36pmtjqk er
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.codeehr = drl.ehrrusej36pmtjqk AND rrb.currversion = drl.ehrrusej36pmtjqkver
                LIMIT 1
            ) ehr ON TRUE
            LEFT JOIN LATERAL (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_vzn17 er
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.idehr = drl.ehrrusvzn17 AND rrb.currversion = drl.ehrrusvzn17ver
                LIMIT 1
            ) ehrvzn ON TRUE
            WHERE drl.drugrequestid = dr.drugrequestid;
            
            -- Insert new drug requests
            INSERT INTO rds.t_drug_request (
                drugrequestid,
                requestdate,
                lpumain,
                active,
                drugrequestmain,
                number,
                requesttype,
                importfilename,
                name,
                endperiod,
                beginperiod,
                status,
                comment,
                "limit",
                branch,
                lasteditdate,
                additional,
                ehrrusej36pmtjqk,
                ehrrusvzn17
            )
            SELECT
                drl.drugrequestid,
                drl.requestdate,
                COALESCE(lp.mcod, tt.lpumain),
                drl.active,
                drl.drugrequestmain,
                drl.number,
                drl.requesttype,
                drl.importfilename,
                drl.name,
                drl.endperiod,
                drl.beginperiod,
                drl.status,
                drl.comment,
                drl.limit,
                tt.branchid,
                COALESCE(drl.lasteditdate, CURRENT_TIMESTAMP),
                COALESCE(drl.additional, FALSE),
                ehr.ehrrusid,
                ehrvzn.ehrrusid
            FROM temp_drug_request drl
            LEFT JOIN rds.t_lpu_main lp ON lp.mcod = drl.lpumain AND (lp.dloaddate <= drl.requestdate AND (lp.dloenddate IS NULL OR lp.dloenddate >= drl.requestdate))
            LEFT JOIN LATERAL (
                SELECT br.branchid, br.lpumain
                FROM rds.t_branch br
                JOIN rds.t_lpu_main lpp ON lpp.mcod = br.lpumain AND (lpp.dloaddate <= drl.requestdate AND (lpp.dloenddate IS NULL OR lpp.dloenddate >= drl.requestdate))
                WHERE (lp.mcod IS NOT NULL AND br.lpumain = lp.mcod AND br.branchcode = drl.branchcode)
                   OR (lp.mcod IS NULL AND br.branchcode = drl.lpumain)
                LIMIT 1
            ) tt ON TRUE
            LEFT JOIN LATERAL (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_ej36pmtjqk er
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.codeehr = drl.ehrrusej36pmtjqk AND rrb.currversion = drl.ehrrusej36pmtjqkver
                LIMIT 1
            ) ehr ON TRUE
            LEFT JOIN LATERAL (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_vzn17 er
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.idehr = drl.ehrrusvzn17 AND rrb.currversion = drl.ehrrusvzn17ver
                LIMIT 1
            ) ehrvzn ON TRUE
            WHERE NOT EXISTS (SELECT 1 FROM rds.t_drug_request dr WHERE dr.drugrequestid = drl.drugrequestid);
            
            UPDATE rds.t_drug_demand dd
            SET
                drugrequest = ddl.drugrequest,
                demandonperiod = ddl.demandonperiod,
                requestquantity = ddl.requestquantity,
                requestcode = ddl.requestcode,
                lsfo = ddl.lsfo,
                comment = ddl.comment,
                sectioncode = ddl.sectioncode,
                persrequest = COALESCE(ddl.persrequest, FALSE),
                drugapplicationlistitem = ddl.requestcatalog
            FROM temp_drug_demand ddl
            WHERE ddl.drugdemandid = dd.drugdemandid;
            
            INSERT INTO rds.t_drug_demand (
                drugdemandid,
                drugrequest,
                demandonperiod,
                requestquantity,
                requestcode,
                lsfo,
                comment,
                sectioncode,
                persrequest,
                drugapplicationlistitem
            )
            SELECT
                ddl.drugdemandid,
                ddl.drugrequest,
                ddl.demandonperiod,
                ddl.requestquantity,
                ddl.requestcode,
                ddl.lsfo,
                ddl.comment,
                ddl.sectioncode,
                COALESCE(ddl.persrequest, FALSE),
                ddl.requestcatalog
            FROM temp_drug_demand ddl
            WHERE NOT EXISTS (SELECT 1 FROM rds.t_drug_demand dd WHERE dd.drugdemandid = ddl.drugdemandid);
            
            UPDATE rds.t_pharmacy_drug_demand pdd
            SET
                drugdemand = pddl.drugdemand,
                demandonperiod = pddl.demandonperiod,
                pharmacy = pddl.pharmacy,
                requestquantity = pddl.requestquantity,
                mont1 = pddl.mont1,
                mont2 = pddl.mont2,
                mont3 = pddl.mont3,
                mont4 = pddl.mont4,
                mont5 = pddl.mont5,
                mont6 = pddl.mont6,
                mont7 = pddl.mont7,
                mont8 = pddl.mont8,
                mont9 = pddl.mont9,
                mont10 = pddl.mont10,
                mont11 = pddl.mont11,
                mont12 = pddl.mont12
            FROM temp_pharmacy_drug_demand pddl
            WHERE pddl.pharmacydrugdemandid = pdd.pharmacydrugdemandid;
            
            INSERT INTO rds.t_pharmacy_drug_demand (
                pharmacydrugdemandid,
                drugdemand,
                demandonperiod,
                pharmacy,
                requestquantity,
                mont1,
                mont2,
                mont3,
                mont4,
                mont5,
                mont6,
                mont7,
                mont8,
                mont9,
                mont10,
                mont11,
                mont12
            )
            SELECT
                pddl.pharmacydrugdemandid,
                pddl.drugdemand,
                pddl.demandonperiod,
                pddl.pharmacy,
                pddl.requestquantity,
                pddl.mont1,
                pddl.mont2,
                pddl.mont3,
                pddl.mont4,
                pddl.mont5,
                pddl.mont6,
                pddl.mont7,
                pddl.mont8,
                pddl.mont9,
                pddl.mont10,
                pddl.mont11,
                pddl.mont12
            FROM temp_pharmacy_drug_demand pddl
            WHERE NOT EXISTS (SELECT 1 FROM rds.t_pharmacy_drug_demand pdd WHERE pdd.pharmacydrugdemandid = pddl.pharmacydrugdemandid);

            UPDATE rds.t_patient_drug_demand pdd
            SET
                demandonperiod = pddl.demandonperiod,
                pharmacydrugdemand = pddl.pharmacydrugdemand,
                comment = pddl.comment,
                benefits = (SELECT b.benefitsid FROM rds.t_benefits b WHERE b.benefitcode = pddl.benefitcode AND b.benefitviews = pddl.benefittype LIMIT 1),
                mkb = pddl.mkb,
                firstname = pddl.firstname,
                fathername = pddl.fathername,
                lastname = pddl.lastname,
                birthday = pddl.birthday,
                requestquantity = pddl.requestquantity,
                mont1 = pddl.mont1,
                mont2 = pddl.mont2,
                mont3 = pddl.mont3,
                mont4 = pddl.mont4,
                mont5 = pddl.mont5,
                mont6 = pddl.mont6,
                mont7 = pddl.mont7,
                mont8 = pddl.mont8,
                mont9 = pddl.mont9,
                mont10 = pddl.mont10,
                mont11 = pddl.mont11,
                mont12 = pddl.mont12,
                signa = pddl.signa,
                post = pddl.post,
                snils = pddl.snils,
                docseries = pddl.docseries,
                docnumber = pddl.docnumber,
                docissuedate = pddl.docissuedate,
                docissueorg = pddl.docissueorg,
                doctype = edt.enumid,
                omsnumber = pddl.omsnumber,
                omsserial = pddl.omsserial,
                ehrrus5o5e88vhc6 = dul.ehrrus5o5e88vhc6,
                sex = esex.enumid
            FROM temp_patient_drug_demand pddl
            LEFT JOIN vclib.t_enums edt ON edt.enumtypeid = 105 AND edt.code = pddl.doctype
            LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = edt.enumid
            LEFT JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.code = pddl.sex
            WHERE pddl.patientdrugdemandid = pdd.patientdrugdemandid;
            
            INSERT INTO rds.t_patient_drug_demand (
                patientdrugdemandid,
                demandonperiod,
                pharmacydrugdemand,
                comment,
                benefits,
                mkb,
                firstname,
                fathername,
                lastname,
                birthday,
                requestquantity,
                mont1,
                mont2,
                mont3,
                mont4,
                mont5,
                mont6,
                mont7,
                mont8,
                mont9,
                mont10,
                mont11,
                mont12,
                signa,
                post,
                snils,
                docseries,
                docnumber,
                docissuedate,
                docissueorg,
                doctype,
                omsnumber,
                omsserial,
                ehrrus5o5e88vhc6,
                sex
            )
            SELECT
                pddl.patientdrugdemandid,
                pddl.demandonperiod,
                pddl.pharmacydrugdemand,
                pddl.comment,
                (SELECT b.benefitsid FROM rds.t_benefits b WHERE b.benefitcode = pddl.benefitcode AND b.benefitviews = pddl.benefittype LIMIT 1),
                pddl.mkb,
                pddl.firstname,
                pddl.fathername,
                pddl.lastname,
                pddl.birthday,
                pddl.requestquantity,
                pddl.mont1,
                pddl.mont2,
                pddl.mont3,
                pddl.mont4,
                pddl.mont5,
                pddl.mont6,
                pddl.mont7,
                pddl.mont8,
                pddl.mont9,
                pddl.mont10,
                pddl.mont11,
                pddl.mont12,
                pddl.signa,
                pddl.post,
                pddl.snils,
                pddl.docseries,
                pddl.docnumber,
                pddl.docissuedate,
                pddl.docissueorg,
                edt.enumid,
                pddl.omsnumber,
                pddl.omsserial,
                dul.ehrrus5o5e88vhc6,
                esex.enumid
            FROM temp_patient_drug_demand pddl
            LEFT JOIN vclib.t_enums edt ON edt.enumtypeid = 105 AND edt.code = pddl.doctype
            LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = edt.enumid
            LEFT JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.code = pddl.sex
            WHERE NOT EXISTS (SELECT 1 FROM rds.t_patient_drug_demand pdd WHERE pdd.patientdrugdemandid = pddl.patientdrugdemandid);
            
            UPDATE rds.t_drug_request_comments drc
            SET
                userprocessed = rc.userprocessed
            FROM temp_drug_request_comments rc
            WHERE rc.drugrequestcommentsid = drc.drugrequestcommentsid;
            
            DELETE FROM rds.t_patient_drug_demand pdd
            USING rds.t_pharmacy_drug_demand phdd, rds.t_drug_demand dd, temp_drug_request dr
            WHERE phdd.pharmacydrugdemandid = pdd.pharmacydrugdemand
              AND dd.drugdemandid = phdd.drugdemand
              AND dr.drugrequestid = dd.drugrequest
              AND NOT EXISTS (
                  SELECT 1 FROM temp_patient_drug_demand pddl
                  JOIN temp_pharmacy_drug_demand phddl ON phddl.pharmacydrugdemandid = pddl.pharmacydrugdemand
                  JOIN temp_drug_demand ddl ON ddl.drugdemandid = phddl.drugdemand
                  WHERE pddl.patientdrugdemandid = pdd.patientdrugdemandid AND dd.drugrequest = ddl.drugrequest
              );
            
            DELETE FROM rds.t_pharmacy_drug_demand pdd
            USING rds.t_drug_demand dd, temp_drug_request dr
            WHERE dd.drugdemandid = pdd.drugdemand
              AND dr.drugrequestid = dd.drugrequest
              AND NOT EXISTS (
                  SELECT 1 FROM temp_pharmacy_drug_demand phddl
                  JOIN temp_drug_demand ddl ON ddl.drugdemandid = phddl.drugdemand
                  WHERE phddl.pharmacydrugdemandid = pdd.pharmacydrugdemandid AND dd.drugrequest = ddl.drugrequest
              );
            
            DELETE FROM rds.t_drug_demand dd
            USING temp_drug_request dr
            WHERE dr.drugrequestid = dd.drugrequest
              AND NOT EXISTS (
                  SELECT 1 FROM temp_drug_demand ddl
                  WHERE ddl.drugdemandid = dd.drugdemandid AND dd.drugrequest = ddl.drugrequest
              );
              
            END IF;
        
        END IF;
    
    
    END IF;
END
$$