DROP PROCEDURE IF EXISTS public.sp_rds_drugrequest;

CREATE OR REPLACE PROCEDURE public.sp_rds_drugrequest(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "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"::boolean;
    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 '<"DRUG_REQUEST"><header><TYPE>' || v_operationtype ||
                   '</TYPE></header><body><"DRUG_REQUEST_LIST">' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT drug_request_xml AS TEXT),
                                ''
                            ), '') ||
                   '</"DRUG_REQUEST_LIST"></body></"DRUG_REQUEST">'
            INTO v_result
            FROM (SELECT 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
                                 ) AS "DRUG_REQUEST_COMMENTS",
                                 (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
                                                                         ) AS "PATIENT_DRUG_DEMAND"
                                                                     )
                                                                 )
                                                             )
                                                      FROM rds.t_pharmacy_drug_demand pdd
                                                      WHERE pdd.drugdemand = dd.drugdemandid
                                                     ) AS "PHARMACY_DRUG_DEMAND"
                                                 )
                                             )
                                         )
                                  FROM rds.t_drug_demand dd
                                  WHERE dd.drugrequest = dr.drugrequestid
                                 ) AS "DRUG_DEMAND"
                             )
                         ) AS drug_request_xml
                  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
                 ) AS "DRUG_REQUEST";
        
        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
            DROP TABLE IF EXISTS temp_drug_request_list;
            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::timestamp 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;
            
            ANALYZE temp_drug_request_list;
            
            DROP TABLE IF EXISTS temp_drug_demand_list;
            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;
            
            ANALYZE temp_drug_demand_list;
            
            DROP TABLE IF EXISTS temp_pharmacy_drug_demand_list;
            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;
            
            ANALYZE temp_pharmacy_drug_demand_list;
            
            DROP TABLE IF EXISTS temp_patient_drug_demand_list;
            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                     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;
            
            ANALYZE temp_patient_drug_demand_list;
            
            DROP TABLE IF EXISTS temp_drug_request_comments_list;
            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::boolean      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;
            
            ANALYZE temp_drug_request_comments_list;
            
            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.dloadddate <= 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.dloadddate <= 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.dloadddate <= 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.dloadddate <= 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
            DROP TABLE IF EXISTS temp_drug_request;
            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::timestamp 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;
            
            DROP TABLE IF EXISTS temp_drug_demand;
            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;
            
            DROP TABLE IF EXISTS temp_pharmacy_drug_demand;
            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;
            
            DROP TABLE IF EXISTS temp_patient_drug_demand;
            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                     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;
            
            DROP TABLE IF EXISTS temp_drug_request_comments;
            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::boolean      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.dloadddate <= 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.dloadddate <= 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.dloadddate <= 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.dloadddate <= 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
$$