DROP PROCEDURE IF EXISTS aps.sp_createsemdprocess;

CREATE OR REPLACE PROCEDURE APS.sp_createsemdprocess
(
    "IdRec" uuid,
    "IdMis" varchar(20),
    "IdEkzMis" varchar(10),
    "TimeZone" varchar(4),
    "Type" varchar,
    "Region" varchar(2),
    "Vers" int,
    "PostingDate" TIMESTAMP,
    OUT "Result" TEXT
)
	language plpgsql
as 
$$
BEGIN
	DECLARE _Xml text;
			_Xml_Items xml;
			_XmlDop xml;
			_XmlTable xml;
			_recordTarget text;
			_author xml;
			_custodian xml;
			_informationRecipient xml;
			_legalAuthenticator xml;
			_documentationOf xml;
			_delayedTabl text;
			_delayedData text;
			_RegionName varchar := (select ehr.NameEHR from EHR.T_EHR_RUS_J5PHS5F9RA ehr where ehr.IdEHR = "Region" and ehr.NameEHR is not null limit 1);
            _postalcode varchar(50);
            _TypeDelay varchar(1); 
            _NameDelay varchar(100); 
            _VersDelay varchar(10);
	begin
	    create temp table tempRecipe as 
        select  
	    	coalesce((erPharm.oid || '.100.' || "IdMis" || '.' || "IdEkzMis") || '.51', '') AS "UnicIdDoc51",
	    	coalesce((erPharm.oid || '.100.' || "IdMis" || '.' || "IdEkzMis") || '.50', '') as "UnicIdDoc50",
	    	coalesce((erPharm.oid || '.100.' || "IdMis" || '.' || "IdEkzMis") || '.10', '') AS "UnicIdDoc10",
            coalesce(r.Serial || cast(r.Number as varchar(100)) || to_char(r.RecipeDate, 'YYYY') || cast(p.Code as varchar(100)), '') AS "IdDoc",
            coalesce(to_char("PostingDate", 'yyyyMMddHH24miss+') || "TimeZone", '') as "DocDate",
            coalesce("Vers", 1) as "UnicIdVers",
            replace(replace(coalesce(r.SNILS, ''), '-', ''), ' ', '') AS "SNILS",
            coalesce(r.DocSeries, '') as "DocSeries",
            coalesce(r.DocNumber, '') AS "DocNumber",
            coalesce(cast(ehrDocType.IdEHR as varchar(100)), '') as "DocType",
            coalesce(cast(ehrDocTypeVers.CurrVersion as varchar(100)), '') as "DocTypeVers",
            coalesce(r.DocIssueOrg, '') as "DocIssueOrg",
            coalesce(r.DocOrgNumber, '') as "DocOrgNumber",
            coalesce(r.PolicyNumber, '') as "PolicyNumber",
            coalesce(r.PolicySerial, '') as "PolicySerial",
            coalesce(to_char(r.DocIssueDate, 'yyyyMMddHHmi'), '') as "DocIssueDate",
            coalesce(r.Address, '') as "Address",
            coalesce("Region", '') as "Region",
            coalesce(_RegionName, '') as "RegionName",
            coalesce(r.FiasAddress, '') as "FiasAddress",
            coalesce(r.FiasHome, '') as "FiasHome",
            case 
                when r.Phone is null or length(r.Phone) = 0 then null
                when length(replace(replace(replace(replace(replace(r.Phone, '-', ''), '(', ''), ')', ''), ' ', ''), '"', '')) = 0 then null
                ELSE 'tel:' || replace(replace(replace(replace(replace(r.Phone, '-', ''), '(', ''), ')', ''), ' ', ''), '"', '')
            end as "Phone",
            coalesce(r.LastName, '') as "LastName",
            coalesce(r.FirstName, '') as "FirstName",
            (CASE WHEN r.FatherName is not null then r.FatherName else null end) as "FatherName",
            r.Sex as "Sex",
            coalesce(to_char(r.Birthday, 'yyyyMMdd'), '') as "Birthday",
            coalesce(erPharm.oid, '') as "PharmacyOid",
            coalesce(p.LicenseNumber, '') as "LicenseNumber",
            coalesce(p.LicenseIssue, '') as "LicenseIssue",
            coalesce(to_char(p.LicenseDate, 'dd.MM.yyyy'), '') as "LicenseDate",
            coalesce(p.OGRN, '') as "OGRN",
            coalesce(p.Name, '') as "Pharmacy",
            case 
                when p.Phone is null or length(p.Phone) = 0 then null
                when length(replace(replace(replace(replace(replace(p.Phone, '-', ''), '(', ''), ')', ''), ' ', ''), '"', '')) = 0 then null
                else 'tel:' || replace(replace(replace(replace(replace(p.Phone, '-', ''), '(', ''), ')', ''), ' ', ''), '"', '')
            end as "PharmacyPhone",
            coalesce('mailto:' || p.Mail, '') as "PharmacyMail",
            coalesce(p.Address, '') as "PharmacyAddress",
            coalesce(p.PostalCode, '') as "PostalCode",
            coalesce(p.FiasAddress, '') as "PharmacyFiasAddress",
		    coalesce(p.FiasHome,'') as "PharmacyFiasHome",
            coalesce(TO_CHAR(r.IssueDate, 'yyyyMMddHHmi') || '+' || "TimeZone" , '') as "RecipeIssueDate",
            coalesce((erPharm.oid || '.100.'|| "IdMis" || '.' || "IdEkzMis" || '.70'), '') as "UnicIdPharm", 
            replace(replace(coalesce(w.Snils,''), '-', ''), ' ', '') as "PostSnils",
            coalesce(cast(postT.IdEHR as varchar(100)), '') as "PostOid",
            coalesce(cast(postTB.CurrVersion as varchar(100)), '') as "PostOidVers",
            coalesce(postT.NameEHR,'') as "PostName",
            coalesce(w.LastName,'') as "PostLastName",
            coalesce(w.FirstName,'') as "PostFirstName",
            (case when w.FatherName is not null then w.FatherName else null end) as "PostFatherName",
            coalesce(r.Serial,'') as "Serial",
	    	coalesce(cast(r.Number as varchar(100)), '') as "Number",
	    	coalesce((er.oid|| '.100.'|| "IdMis" || '.'|| "IdEkzMis") + '.51','') as "UnicIdDocMo51",
	    	coalesce((er.oid|| '.100.'|| "IdMis" || '.'|| "IdEkzMis") + '.50','') as "UnicIdDocMo50",
	    	coalesce(r.Serial + cast(r.Number as varchar) + date_part('year', r.RecipeDate)::varchar(4) + er.oid,'') as "IdDocMO"
            from APS.T_RECIPE r
                left join APS.T_LPU_MAIN lp on r.LpuMain = lp.MCOD
                left join EHR.T_EHR_RUS_MDR308 er on er.EhrRusID = lp.EhrRusMdr308
                left join VCLib.T_ENUMS eDoc on eDoc.EnumID = r.DocType
                left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
                left join EHR.T_EHR_RUS_NN5MEATU5T ehrDocType on ehrDocType.EhrRusID = dul.EhrRusNn5meatu5t
                left join EHR.T_EHR_RUS_REF_BOOKS ehrDocTypeVers on ehrDocTypeVers.EhrRusRefBooksID = ehrDocType.EhrRusRefBooks
                left join APS.T_RECIPE_INVOICE ri on ri.Recipe = r.RecipeID
                left join STOR.T_STORAGE_INVOICE si on si.InvoiceID = ri.InvoiceID
                left join STOR.T_STORAGE s on s.StorageID = si.Storage
                left join APS.T_PHARMACY_ISSUE_PLACE pip on pip.IssuePlaceID = s.IssuePlace
                left join APS.T_PHARMACY p on pip.Pharmacy = p.Code
                left join EHR.T_EHR_RUS_MDR308 erPharm on erPharm.EhrRusID = p.EhrRusMdr308
                left join APS.T_USERS us on us.UserID = si.HoldingUser
                left join APS.T_POST post on post.PostID = us.Post
                left join APS.T_WORKER w on w.WorkerID = post.Worker
                left join EHR.T_EHR_RUS_7PJSAM6GJM postT on postT.EhrRusID = post.EhrRus7pjsam6gjm
                left join EHR.T_EHR_RUS_REF_BOOKS postTB on postTB.EhrRusRefBooksID = postT.EhrRusRefBooks
            where r.RecipeID = "IdRec";

            _postalcode := (select (case when r."PostalCode" is not null and r."PostalCode" <> '' then '<postalCode>' || r."PostalCode" || '</postalCode>' else '<postalCode nullFlavor="NI"/>' end) from tempRecipe r);

            create temp table Recipe_Items as 
            select  
                coalesce(ehrEsklp.NameEHR, '') as "Esklp_Trn",
                coalesce(ehrEsklp.STANDARD_DOZE, '') as "Esklp_DoseView",
                coalesce(ehrEsklp.STANDARD_FORM, '') as "Esklp_MedicationForms",
                coalesce(ehrEsklp.NAME_PRODUCER, '') as "Esklp_MedicamentProducer",
                coalesce(ehrEsklp.NUMBER_UNITS_2, '') as "Esklp_PackQuantity",
                coalesce(CAST(re.Amount as varchar(100)), '') as "Amount",
                coalesce(CAST(re.Amount * r.Price as varchar(100)), '') as "Sum",
                coalesce(ehrEsklp.NameEHR, '') as "ekslp_name",
                coalesce(ehrEsklp.KLP_CODE, '') as "ekslp_code",
                coalesce(rbEsklp.CurrVersion, '') as "ekslp_version",
                (row_number() over (order by re.RecipeElementsID)) as "Number",
                coalesce(ehMedDev.NameEHR, '') as "meddev_name",
                coalesce(ehMedDev.CHARACTERISTICS, '') as "meddev_MedicationForms",
                coalesce(ehMedDev.CodeEHR, '') as "meddev_code",
                coalesce(rbMedDev.CurrVersion, '') as "meddev_version",
                coalesce(ehNutNut.NameEHR, '') as "nut_name",
                coalesce(ehNutNut.CodeEHR, '') as "nut_code",
                coalesce(ehNutNut.CHARACTERISTICS, '') as "nut_MedicationForms",
                coalesce(rbNut.CurrVersion, '') as "nut_version",
                coalesce(to_char("PostingDate", 'yyyyMMddHH24miss+') || "TimeZone", '') as "DocDate"
            from APS.T_RECIPE_ELEMENTS re
            left join STOR.T_REMAINS r on r.RemainsID = re.Remains
            left join PHARM.T_BILL_OF_GOODS bg on bg.BillOfGoodsID = r.BillOfGoods
            left join PHARM.T_C_TRN trn on trn.CTrnID = bg.CTrn
            left join EHR.T_EHR_RUS_2M69I03GTD ehrEsklp on ehrEsklp.EhrRusID = bg.EhrRus2m69i03gtd
            left join EHR.T_EHR_RUS_REF_BOOKS rbEsklp on rbEsklp.EhrRusRefBooksID = ehrEsklp.EhrRusRefBooks
            left join EHR.T_EHR_RUS_P08TSH954N ehMedDev on ehMedDev.EhrRusID = trn.EhrRusP08TSH954N
            left join EHR.T_EHR_RUS_REF_BOOKS rbMedDev on rbMedDev.EhrRusRefBooksID = ehMedDev.EhrRusRefBooks
            left join EHR.T_EHR_RUS_575VSF88OG ehNutNut on ehNutNut.EhrRusID = trn.EhrRus575VSF88OG
            left join EHR.T_EHR_RUS_REF_BOOKS rbNut on rbNut.EhrRusRefBooksID = ehNutNut.EhrRusRefBooks
            where re.Recipe = "IdRec";

            select (
                select 
                '<recordTarget>
                    <patientRole>
                        <id root="1.2.643.100.3" extension="' || r."SNILS" || '"/>' 
                        ||
                        CASE 
                            WHEN r."DocType" is not null and r."DocType" <> '' and r."DocSeries" is not null 
                            and r."DocSeries" <> '' and r."DocNumber" is not null and r."DocNumber" <> '' 
                            and r."DocIssueDate" is not null and r."DocIssueDate" <> '' and r."DocIssueOrg" is not null 
                            and r."DocIssueOrg" <> '' and r."DocTypeVers" is not null and r."DocTypeVers" <> '' then    
                                '<identity:IdentityDoc>
                                    <identity:IdentityCardTypeId code="' || r."DocType" || '" codeSystem="1.2.643.5.1.13.13.99.2.48" codeSystemVersion="' || r."DocTypeVers" || '" codeSystemName="Документы, удостоверяющие    личность" displayName="Паспорт гражданина Российской Федерации"/>
                                    <identity:Series>' || r."DocSeries" || '</identity:Series>
                                    <identity:Number>' || r."DocNumber" || '</identity:Number>
                                    <identity:IssueOrgName>' || r."DocIssueOrg" || '</identity:IssueOrgName>' ||
                                    case when r."DocOrgNumber" <> '' then '<identity:IssueOrgCode>' || r."DocOrgNumber" || '</identity:IssueOrgCode>' else '<identity:IssueOrgCode nullFlavor="NI"/>' end ||
                                    '<identity:IssueDate value="' || r."DocIssueDate" || '"/>
                                </identity:IdentityDoc>'
                            else 
                                '<identity:IdentityDoc nullFlavor="NI"/>'
                        end 
                        ||
                        case 
                            when r."PolicyNumber" is not null and r."PolicyNumber" <> '' then
                                '<identity:InsurancePolicy>
                                    <identity:InsurancePolicyType ' 
                                    || case when r."PolicySerial" is not null and r."PolicySerial" <> '' then 'code="1"' else 'code="2"' end || ' codeSystem="1.2.643.5.1.13.13.11.1035" codeSystemVersion="1.3" codeSystemName="Виды полиса обязательного медицинского страхования" ' 
                                    || case when r."PolicySerial" is not null and r."PolicySerial" <> '' then 'displayName="Полис ОМС старого образца"' else 'displayName="Полис ОМС единого образца, бессрочный"' end || '/>' 
                                    || case when r."PolicySerial" is not null and r."PolicySerial" <> '' then '<identity:Series>' || r."PolicySerial" || '</identity:Series>' else '<identity:Series nullFlavor="NI"/>' end 
                                    || '<identity:Number>' || r."PolicyNumber" || '</identity:Number>
                                </identity:InsurancePolicy>'
                            else 
                                '<identity:InsurancePolicy nullFlavor="NI"/>'
                        end 
                        ||
                        '<addr use="H">
                            <address:Type code="1" codeSystem="1.2.643.5.1.13.13.11.1504" codeSystemVersion="1.3" codeSystemName="Тип адреса пациента" displayName="Адрес по месту жительства (постоянной регистрации)"/>
                            <streetAddressLine>' || r."Address" || '</streetAddressLine>
                            <address:stateCode code="' || r."Region" || '" codeSystem="1.2.643.5.1.13.13.99.2.206" codeSystemVersion="6.3" codeSystemName="Субъекты Российской Федерации" displayName="' || r."RegionName" || '"/>' 
                            || '<postalCode nullFlavor="NI"/>' ||
                            case 
                                when r."FiasAddress" is not null and r."FiasAddress" <> '' AND r."FiasHome" is not null and r."FiasHome" <> '' then 
                                    '<fias:Address>
                                        <fias:AOGUID>' || r."FiasAddress" || '</fias:AOGUID>
                                        <fias:HOUSEGUID>' || r."FiasHome" || '</fias:HOUSEGUID>			
                                    </fias:Address>' 
                                else 
                                    '<fias:Address nullFlavor="NI"/>'
                            end 
                            || '</addr>' 
                            || case when r."Phone" is null then '' else '<telecom value="' || r."Phone" || '"/>' end ||
                                '<patient>
                                    <name>
                                        <family>' || r."LastName" || '</family>
                                        <given>' || r."FirstName" || '</given>' 
                                        || case when r."FatherName" is not null and r."FatherName" <> '' then '<identity:Patronymic>' || r."FatherName" || '</identity:Patronymic>' else '' end || 
                                    '</name>
                                    <administrativeGenderCode ' ||
                                    case when r."Sex" <> 10301 then 'code="2"' else 'code="1"' end || ' codeSystem="1.2.643.5.1.13.13.11.1040" codeSystemVersion="2.1" codeSystemName="Пол пациента"' ||
                                    case when r."Sex" <> 10301 then ' displayName="Женский"' else ' displayName="Мужской"' end || '/>
                                    <birthTime value="' || r."Birthday" || '"/>
                                </patient>
                                <providerOrganization>
                                    <id root="' || r."PharmacyOid" || '"/> 
                                    '|| case when r."LicenseNumber" = '' OR r."LicenseNumber" is null then '' else '<id root="1.2.643.5.1.13.2.1.1.1504.101" extension="'|| r."LicenseNumber" ||'" assigningAuthorityName="'|| r."LicenseIssue" ||'. Дата регистрации: '|| r."LicenseDate" ||'."/>' END ||
                                    '<id root="1.2.643.5.1.13.2.1.1.1504.101" extension="ЛО-61-02-003811" assigningAuthorityName="Министерство Здравоохранения Ростовской области. Дата регистрации: 16.09.2019."/>
                                    <identity:Props>
                                        <identity:Ogrn>'|| r."OGRN" ||'</identity:Ogrn>
                                        <identity:Ogrnip nullFlavor="NA"/>
                                    </identity:Props>
                                    <name>'|| r."Pharmacy" ||'</name> 
                                    '|| case when r."PharmacyPhone" IS NULL then '' else '<telecom value="'|| r."PharmacyPhone" ||'" use="WP"/>' end || 
                                    '<addr>
                                        <streetAddressLine>'|| r."PharmacyAddress" ||'</streetAddressLine>
                                        <address:stateCode code="'|| r."Region" ||'" codeSystem="1.2.643.5.1.13.13.99.2.206" codeSystemVersion="6.3" codeSystemName="Субъекты Российской Федерации" displayName="'||r."RegionName" ||'"/>' ||
                                            case when r."PostalCode" is not null and r."PostalCode" <> ''  
                                                 then '<postalCode>'||r."PostalCode"||'</postalCode>' 
                                                 else '<postalCode nullFlavor="NI"/>' end ||
                                            case when r."PharmacyFiasAddress" is not null and r."PharmacyFiasAddress" <> '' and r."PharmacyFiasHome" is not null and r."PharmacyFiasHome" <> '' then 
                                                '<fias:Address>
                                                    <fias:AOGUID>'+r."PharmacyFiasAddress"+'</fias:AOGUID>
                                                    <fias:HOUSEGUID>'+r."PharmacyFiasHome"+'</fias:HOUSEGUID>			
                                                </fias:Address>'
                                            else '<fias:Address nullFlavor="NI"/>'
                                            end ||
                                    '</addr>
                                </providerOrganization>
                            </patientRole>
                        </recordTarget>'
	                from tempRecipe r
                )into _recordTarget;

                select 
                    (
                        SELECT 
                            xmlelement(name author,
                                    xmlelement(name time, xmlattributes('NI' AS "nullFlavor")),
                                    xmlelement(name "assignedAuthor",
                                        xmlelement(name id, xmlattributes('1.2.643.100.3' AS root, r."PostSnils" AS extension )),
                                        xmlelement(name code, xmlattributes( r."PostOid" AS code, '1.2.643.5.1.13.13.11.1002' AS "codeSystem", r."PostOidVers" AS "codeSystemVersion", 'Должности медицинских и фармацевтических работников' AS "codeSystemName", r."PostName" AS "displayName")),
                                        xmlelement(name "assignedPerson",
                                            xmlelement(name name,
                                                xmlelement(name family, r."PostLastName"),
                                                xmlelement(name given, r."PostFirstName"),
                                                xmlelement(name "identity:Patronymic", r."PostFatherName")
                                            )
                                        ),
                                        xmlelement(name "representedOrganization",
                                            xmlattributes('ORG' AS classCode),
                                            xmlelement(name id, xmlattributes(r."PharmacyOid" AS root) ),
                                            xmlelement(name name, r."Pharmacy"),
                                            xmlelement(name telecom, xmlattributes(r."PharmacyMail" AS value)),
                                            xmlelement(name addr, xmlelement(name "streetAddressLine", r."PharmacyAddress"), xmlelement(name "address:stateCode", 
                                            xmlattributes( r."Region" AS code, '1.2.643.5.1.13.13.99.2.206' AS "codeSystem", '6.3' AS "codeSystemVersion", 'Субъекты Российской Федерации' AS "codeSystemName", r."RegionName" AS "displayName")),
                                                CASE 
                                                    WHEN _postalcode IS NOT NULL THEN xmlelement(name "postalCode", _postalcode)
                                                    ELSE xmlelement(name "postalCode", xmlattributes('NI' AS "nullFlavor") )
                                                END, xmlelement(name "fias:Address", xmlattributes('NI' AS "nullFlavor") )
                                            )
                                        )
                                )
                            )::xml
                        FROM tempRecipe r
                    ) INTO _author; 

                    select 
                    (
                        select
                            xmlelement(name custodian, 
                            xmlelement(name "assignedCustodian", xmlelement(name "representedCustodianOrganization", xmlattributes('ORG' AS "classCode"), 
                            xmlelement(name id, xmlattributes(r."PharmacyOid" as "root")),
                            xmlelement(name name, r."Pharmacy"),
                            xmlelement(name telecom, xmlattributes(r."PharmacyPhone" as "value")),
                            xmlelement(name addr, xmlelement(name "streetAddressLine", r."PharmacyAddress"), xmlelement(name "address:stateCode", 
                            xmlattributes(r."Region" as code, '1.2.643.5.1.13.13.99.2.206' as "codeSystem", '6.3' as "codeSystemVersion", 'Субъекты Российской Федерации' AS "codeSystemName", r."RegionName" AS "displayName")),
                            case 
                                when _postalcode IS NOT NULL then xmlelement(name "postalCode", _postalcode) 
                                else xmlelement(name "postalCode", xmlattributes('NI' as "nullFlavor")) 
                            end,
                            xmlelement(name "fias:Address", xmlattributes('NI' as "nullFlavor")))))
                        )::xml
	                    from tempRecipe r
                    )into _custodian;

                    select xmlparse(content '<informationRecipient>
                        <intendedRecipient>
                            <receivedOrganization>
                                <id root="1.2.643.5.1.13"/>
                                <name>Министерство здравоохранения Российской Федерации</name>
                            </receivedOrganization>
                        </intendedRecipient>
                    </informationRecipient>') into _informationRecipient;

                    select 
                    (
                        select
                            xmlelement(name "legalAuthenticator", 
                        		xmlelement(name time, xmlattributes('NI' AS "nullFlavor")),
                        		xmlelement(name "signatureCode", xmlattributes('NI' AS "nullFlavor")),
                                    xmlelement(name "assignedEntity", 
                                        xmlelement(name id, xmlattributes('1.2.643.100.3' as root, r."PostSnils" as extension)),
                                        xmlelement(name code, xmlattributes(r."PostOid" as code, '1.2.643.5.1.13.13.11.1002' as "codeSystem", r."PostOidVers" as "codeSystemVersion", 'Должности медицинских и фармацевтических работников' as "codeSystemName", r."PostName" as "displayName")),
                                        xmlelement(name "assignedPerson", xmlelement(name name, xmlelement(name family, r."PostLastName"), xmlelement(name given, r."PostFirstName"), xmlelement(name "identity:Patronymic", r."PostFatherName"))),
                                        xmlelement(name "representedOrganization", 
                                            xmlattributes('ORG' as classCode), 
                                            xmlelement(name id, xmlattributes(r."PharmacyOid" as root)),
                                            xmlelement(name name, r."Pharmacy"),
                                            xmlelement(name telecom, xmlattributes(r."PharmacyPhone" as value)),
                                            xmlelement(name addr, xmlelement(name "streetAddressLine", r."PharmacyAddress"), 
                                            xmlelement(name "address:stateCode", xmlattributes(r."Region" as code, '1.2.643.5.1.13.13.99.2.206' as "codeSystem", '6.3' as "codeSystemVersion", 'Субъекты Российской Федерации' as "codeSystemName", r."RegionName" as "displayName")),
                                            CASE 
                                                WHEN _postalcode is not null then xmlelement(name "postalCode", _postalcode) 
                                                ELSE xmlelement(name "postalCode", xmlattributes('NI' as "nullFlavor")) 
                                            END,
                                            xmlelement(name "fias:Address", xmlattributes('NI' as "nullFlavor"))))
                                   )
                            )::xml
                        FROM tempRecipe r 
                    )INTO _legalAuthenticator;
                    
                    select (

                        select (
                        '<documentationOf>
                            <serviceEvent>
				                <code code="54" codeSystem="1.2.643.5.1.13.13.99.2.726" codeSystemName="Типы документированных событий" displayName="Отпуск лекарственного препарата" codeSystemVersion="3.1"/>
				                <effectiveTime value ="' || r."RecipeIssueDate" || '"/>
			                </serviceEvent>
		                </documentationOf>') :: xml
                        FROM tempRecipe r  
                        )INTO _documentationOf;


                    if _TypeDelay is not null 
                    then
                        _delayedTabl := '<table> 
                                            <caption>
                                                Информация об отсроченном обслуживании
                                            </caption>
                                            <tbody>
                                                <tr>
                                                    <td>
                                                        <content>Отсроченное обслуживание</content>
                                                    </td>
                                                    <td>
                                                        <content>' || COALESCE(_NameDelay, '') || '</content>
                                                    </td>
                                                </tr>
                                            </tbody>
                                          </table>';

                        _delayedData := 'declare namespace fias = "urn:hl7-ru:fias";
                                        <entry>
                                            <observation classCode="OBS" moodCode="EVN">
                                                <code code="6013" codeSystem="1.2.643.5.1.13.13.99.2.166" 
                                                      codeSystemVersion="2.1" 
                                                      codeSystemName="Кодируемые поля CDA документов" 
                                                      displayName="Отсроченное обслуживание">
                                                    <originalText>
                                                        <reference value="#DOC_9"/>
                                                    </originalText>
                                                </code>
                                                <value xsi:type="CD" 
                                                      code="' || COALESCE(_TypeDelay, '') || '" 
                                                      codeSystem="1.2.643.5.1.13.13.99.2.637" 
                                                      codeSystemVersion="' || COALESCE(_VersDelay, '') || '" 
                                                      codeSystemName="Отсроченное обслуживание" 
                                                      displayName="' || COALESCE(_NameDelay, '') || '"/>
                                            </observation>
                                        </entry>';

                        END if;
                        
                        if "Type" = 'Dispense_max' 
                        then 
                            _XmlDop := (('<section>
                                                 <code code="MEDDISPENSE" codeSystem="1.2.643.5.1.13.13.99.2.197" codeSystemVersion="2.1" codeSystemName="Секции CDA документов" displayName="Отпуск лекарственного препарата/изделия медицинского назначения/специализированного продукта лечебного питания"/>
                                                 <title>Отпуск</title>
                                                 <text>
                                                     <table>
                                                         <thead>
                                                             <tr>
                                                                 <th>Торговое наименование</th>
                                                                 <th>Нормализованная доза препарата</th>
                                                                 <th>Форма отпущенного препарата</th>
                                                                 <th>Производитель</th>
                                                                 <th>Количество единиц в одной упаковке</th>
                                                                 <th>Отпущено (в потребительских упаковках)</th>
                                                                 <th>Стоимость</th>
                                                             </tr>
                                                         </thead>
                                                         <tbody>' || (SELECT string_agg('<tr>
                                                                                 <td>' || "ekslp_name" || '</td>
                                                                                 <td>' || "Esklp_DoseView" || '</td>
                                                                                 <td>' || "Esklp_MedicationForms" || '</td>
                                                                                 <td>' || "Esklp_MedicamentProducer" || '</td>
                                                                                 <td>' || "Esklp_PackQuantity" || '</td>
                                                                                 <td>' || "Amount" || '</td>
                                                                                 <td>' || "Sum" || '</td>
                                                                             </tr>', '')
                                                                     FROM Recipe_Items) || 
                                                         '</tbody> 
                                                     </table>
                                                 </text>
                                                	' || (
                                                 SELECT string_agg('
                                                				<entry>
                                                                     <supply classCode="SPLY" moodCode="EVN">
                                                                         <effectiveTime value="' || "DocDate" || '"/>
                                                                         <quantity value="' || "Amount" || '"/>
                                                                         <product typeCode="PRD">
                                                                             <manufacturedProduct classCode="MANU">
                                                                                 <manufacturedMaterial classCode="MMAT" determinerCode="KIND">
                                                                                     <code code="' || "ekslp_code" || '" codeSystem="1.2.643.5.1.13.13.99.2.540" codeSystemVersion="' || "ekslp_version" || '" displayName="' || "ekslp_name" || '" codeSystemName="Лекарственные препараты. Товарные позиции. ЕСКЛП с кодами КТРУ"/>
                                                                                 </manufacturedMaterial>
                                                                             </manufacturedProduct>
                                                                         </product>
                                                                         <entryRelationship typeCode="COMP">
                                                                             <observation classCode="OBS" moodCode="EVN">
                                                                                 <code code="6015" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Стоимость"/>
                                                                                 <value xsi:type="REAL" value="' || "Sum" || '"/>
                                                                             </observation>
                                                                         </entryRelationship>
                                                                     </supply>
                                                                 </entry>', '')
                         FROM Recipe_Items)) || '</section>') :: xml;                        
                        
                        elsif _Type = 'Dispense_Med_Prod'
                        then
                            _XmlDop := '<section>
					        <code code="MEDDISPENSE" codeSystem="1.2.643.5.1.13.13.99.2.197" codeSystemVersion="2.1" codeSystemName="Секции CDA документов" displayName="Отпуск лекарственного препарата/изделия медицинского назначения/специализированного продукта лечебного питания"/>
					        <title>Отпуск</title>
					            <text>
					            	<table>
					            		<thead>
					            			<tr>
					            				<th>Наименование</th>
					            				<th>Количество единиц в упаковке</th>
					            				<th>Стоимость</th>
					            			</tr>
					            		</thead>
					            		<tbody> </tbody> 
					            	</table>
					            </text>
				            </section>';
                            
                            _XmlTable := (
                            SELECT string_agg('<tr>
									                <td>' || "meddev_name" || '</td>
									                <td>' || "Amount" || '</td>
									                <td>' || "Sum" || '</td>
								               </tr>', '')
                            FROM Recipe_Items);
                            
                            --SET @XmlDop.modify('insert sql:variable("@XmlTable") into (//tbody)[1]')   
                            _XmlDop := xmlparse(document (select xmlconcat(xpath('//tbody' ,_XmlDop), _XmlTable)));         

                            SELECT string_agg('declare namespace fias = "urn:hl7-ru:fias";
						            <entry>
						            <supply classCode="SPLY" moodCode="EVN">
						            	<effectiveTime value="' || "DocDate" || '"/>
						            	<quantity value="' || "Amount" || '"/>
						            	<product typeCode="PRD">
						            		<manufacturedProduct classCode="MANU">
						            			<manufacturedMaterial classCode="MMAT" determinerCode="KIND">
						            				<code code="' || "meddev_code" || '" codeSystem="1.2.643.5.1.13.13.99.2.604" codeSystemVersion="' || "meddev_version" || '" displayName="' || "meddev_name" || '" codeSystemName="ФРЛЛО. Справочник медицинских изделий огласно аталогу    товаров, работ, услуг для обеспечения государственных и муниципальных нужд"/>
						            			</manufacturedMaterial>
						            		</manufacturedProduct>
						            	</product>
						            	<entryRelationship typeCode="COMP">
						            		<observation classCode="OBS" moodCode="EVN">
						            			<code code="6015" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Стоимость"/>
						            			<value xsi:type="REAL" value="' || "Sum" || '"/>
						            		</observation>
						            	</entryRelationship>
						            </supply>
					                </entry>', '')
                            FROM Recipe_Items;

                            --SET @XmlDop.modify('insert sql:variable("@Xml_Items") into (//section)[1]');  
                            _XmlDop := xmlparse(document (select xmlconcat(xpath('//section' ,_XmlDop), Xml_Items)));         
                        elsif _Type = 'Dispense_nut_sup'
                        then 
                            _XmlDop := '<section>
					            <code code="MEDDISPENSE" codeSystem="1.2.643.5.1.13.13.99.2.197" codeSystemVersion="2.1" codeSystemName="Секции CDA документов" displayName="Отпуск лекарственного препарата/изделия медицинского назначения/специализированного продукта лечебного питания"/>
					            <title>Отпуск</title>
					            <text>
					            	<table>
					            		<thead>
					            			<tr>
					            				<th>Торговое наименование</th>
					            				<th>Форма отпущенного препарата</th>
					            				<th>Отпущено (в потребительских упаковках)</th>
					            				<th>Стоимость</th>
					            			</tr>
					            		</thead>
					            		<tbody> </tbody> 
					            	</table>
					            </text>
				             </section>';

                            _XmlTable := (
                            SELECT string_agg('<tr>
									                <td>' || "nut_name" || '</td>
									                <td>' || "nut_MedicationForms" || '</td>
									                <td>' || "Amount" || '</td>
									                <td>' || "Sum" || '</td>
								               </tr>', '')
                            FROM Recipe_Items);

                        	--SET @XmlDop.modify('insert sql:variable("@XmlTable") into (//tbody)[1]');
                            _XmlDop := xmlparse(document (select xmlconcat(xpath('//tbody' ,_XmlDop), _XmlTable)));  

                            _XmlTable := (
                            SELECT string_agg('declare namespace fias = "urn:hl7-ru:fias";
						            <entry>
						            <supply classCode="SPLY" moodCode="EVN">
						            	<effectiveTime value="' || "DocDate" || '"/>
						            	<quantity value="' || "Amount" || '"/>
						            	<product typeCode="PRD">
						            		<manufacturedProduct classCode="MANU">
						            			<manufacturedMaterial classCode="MMAT" determinerCode="KIND">
						            				<code code="' || "nut_code" || '" codeSystem="1.2.643.5.1.13.13.99.2.603" codeSystemVersion="' || "nut_version" || '" displayName="' || "nut_name" || '" codeSystemName="ФРЛЛО. Справочник специализированного питания согласно       каталогу    товаров, работ, услуг для обеспечения государственных и муниципальных нужд"/>
						            			</manufacturedMaterial>
						            		</manufacturedProduct>
						            	</product>
						            	<entryRelationship typeCode="COMP">
						            		<observation classCode="OBS" moodCode="EVN">
						            			<code code="6015" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Стоимость"/>
						            			<value xsi:type="REAL" value="' || "Sum" || '"/>
						            		</observation>
						            	</entryRelationship>
						            </supply>
					            </entry>', '')
                            FROM Recipe_Items);
                            
                            --SET @XmlDop.modify('insert sql:variable("@Xml_Items") into (//section)[1]')   
                            _XmlDop := xmlparse(document (select xmlconcat(xpath('//section' ,_XmlDop), _Xml_Items)));  
                        
                        end if;
                        
                        _Xml := (
                        select string_agg('<ClinicalDocument xmlns="urn:hl7-org:v3" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:fias="urn:hl7-ru:fias" xmlns:identity="urn:hl7-ru:identity" xmlns:address="urn:hl7-ru:address" xmlns:medService="urn:hl7-ru:medService">
		                        <realmCode code="RU"/>
		                        <typeId root="2.16.840.1.113883.1.3" extension="POCD_MT000040"/>
		                        <templateId root="1.2.643.5.1.13.13.14.38.9.4"/>
		                        <id root="' || "UnicIdDoc51" || '" extension="' || "IdDoc" || '"/>
		                        <code code="38" codeSystem="1.2.643.5.1.13.13.11.1522" codeSystemVersion="7.22" codeSystemName="Виды медицинской документации" displayName="Отпуск по рецепту на лекарственный препарат, изделие медицинского назначения и специализированный продукт   ечебного питания"/>
		                        <title>Отпуск по рецепту на лекарственный препарат, изделие медицинского назначения и специализированный продукт лечебного питания</title>
		                        <effectiveTime value="' || "DocDate" || '"/>
		                        <confidentialityCode code="N" codeSystem="1.2.643.5.1.13.13.99.2.285" codeSystemVersion="1.2" codeSystemName="Уровень конфиденциальности медицинского документа" displayName="Обычный"/>
		                        <languageCode code="ru-RU"/>
		                        <setId root="' || "UnicIdDoc50" || '" extension="' || "UnicIdVers" || '"/>
		                        <versionNumber value="1"/>
                                ' || _recordTarget::text || '
                                ' || _author::text || '
                                ' || _custodian::text || '
                                ' || _informationRecipient::text || '
                                ' || _legalAuthenticator::text || '
		                        ' || _documentationOf::text || '
		                        <component>
		                        	<structuredBody>
		                        		<component>
		                        			<section>
		                        				<code code="DOCINFO" codeSystem="1.2.643.5.1.13.13.99.2.197" codeSystemVersion="2.1" codeSystemName="Секции CDA документов" displayName="Сведения о документе"/>
		                        				<title>Сведения о рецепте</title>
		                        				<text>
		                        					<table>
		                        						<caption>Информация о рецепте</caption>
		                        						<tbody>
		                        							<tr>
		                        								<td>
		                        									<content>Серия рецепта</content>
		                        								</td>
		                        								<td>
		                        									<content>' || "Serial" || '</content>
		                        								</td>
		                        							</tr>
		                        							<tr>
		                        								<td>
		                        									<content>Номер рецепта</content>
		                        								</td>
		                        								<td>
		                        									<content>' || "Number" || '</content>
		                        								</td>
		                        							</tr>
		                        						</tbody>
		                        					</table>
                                                    ' || case when _delayedTabl is not null then _delayedTabl else '' end || '
		                        				</text>
		                        				<entry>
		                        					<observation classCode="OBS" moodCode="EVN">
		                        						<code code="6012" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Статус рецепта"/>
		                        						<value xsi:type="BL" value="true"/>
		                        						<entryRelationship typeCode="COMP">
		                        							<observation classCode="OBS" moodCode="EVN">
		                        								<code code="6001" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Серия рецепта"/>
		                        								<value xsi:type="ST">' || "Serial" || '</value>
		                        							</observation>
		                        						</entryRelationship>
		                        						<entryRelationship typeCode="COMP">
		                        							<observation classCode="OBS" moodCode="EVN">
		                        								<code code="6002" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Номер рецепта"/>											
		                        								<value xsi:type="ST">' || "Number" || '</value>
		                        							</observation>
		                        						</entryRelationship>
		                        						<reference typeCode="REFR">
		                        							<externalDocument>
		                        								<id root="' || "UnicIdDocMo51" || '" extension="' || "IdDocMO" || '"/>
		                        								<id nullFlavor="NI"/>
		                        								<setId root="' || "UnicIdDocMo50" || '" extension="87"/>
		                        							</externalDocument>
		                        						</reference>
		                        					</observation>
		                        				</entry>
                                                ' || case when _delayedData is not null then _delayedData else '' end || '
		                        			</section>
		                        		</component>
		                        		<component>
                                        ' || _XmlDop::text || '
		                        		</component>
		                        	</structuredBody>
		                        </component>
	                        </ClinicalDocument>', '')
                        from tempRecipe);

                        "Result" := replace(_Xml,  ' xmlns=""', '');

                        drop table if exists tempRecipe;
                        drop table if exists Recipe_Items;
    end;
END;
$$