DROP PROCEDURE IF EXISTS DBO.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
)
	language plpgsql
as 
$$
BEGIN
	DECLARE _Xml xml;
			_Xml_Items xml;
			_XmlDop xml;
			_XmlTable xml;
			_recordTarget text;
			_author xml;
			_custodian xml;
			_informationRecipient xml;
			_legalAuthenticator xml;
			_documentationOf xml;
			_delayedTabl xml;
			_delayedData xml;
			_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) := (select (case when r.PostalCode is not null and r.PostalCode <> '' then '<postalCode>' || r.PostalCode || '</postalCode>' else '<postalCode nullFlavor="NI"/>' end) from Recipe r where r.RecipeId = "IdRec");
            _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(Current_TimeStamp::TIMESTAMP, 'yyyyMMddHHmmss') || '+' || "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, 'yyyyMMddHHmm'), '') 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(r.Phone, '-', ''), '(', ''), ')', ''), ' ', '')) = 0 then null
                ELSE 'tel:' || 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",
            coalesce(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(p.Phone, '-', ''), '(', ''), ')', ''), ' ', '')) = 0 then null
                else 'tel:' || 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(TO_CHAR(r.IssueDate, 'yyyyMMddHHmm') || '+' || "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) + to_char(date_part('year', r.RecipeDate)) + 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";

            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",
                coalesce((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(Current_TimeStamp::TIMESTAMP, 'yyyyMMddHHmm') || '+' || "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 _recordTarget = (
                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>'
                    into _recordTarget
                    from Recipe_Items re
                    where re.RecipeID = "IdRec"
                )

                select 
                    (
                        select 
                            xmlelement(name author,
                                xmlforest(
                                    NULL AS time,
                                    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 Recipe_Items r
                        WHERE r.RecipeID = "IdRec"
                    ) 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 Recipe_Items r 
                        where r.RecipeID = "IdRec"
                    )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, 
                            xmlforest(
                                    NULL AS time,
                                    NULL as signatureCode,
                                    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 Recipe_Items r 
                        WHERE r.RecipeID = "IdRec"
                    )INTO _legalAuthenticator;
                    
                    select xmlparse(content '<documentationOf>
                            <serviceEvent>
				                <code code="54" codeSystem="1.2.643.5.1.13.13.99.2.726" codeSystemName="Типы документированных событий" displayName="Отпуск лекарственного препарата" codeSystemVersion="3.1"/>
				                <effectiveTime value ="{sql:column("RecipeIssueDate")}"/>
			                </serviceEvent>
		                </documentationOf>') 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 "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>
                            </section>';
                            
                            _Xml_Items := (
                            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);
                            
                            _XmlDop := regexp_replace(_XmlDop, '(</section>)', _Xml_Items);                        
                        
                        end if;
                        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' passing _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' passing _XmlDop), Xml_Items)));         
                        end if;
                        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' passing _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' passing _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"/>
		                        <Replace/>
		                        <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>
		                        				</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>
		                        			</section>
		                        		</component>
		                        		<component>
		                        		</component>
		                        	</structuredBody>
		                        </component>
	                        </ClinicalDocument>', '')
                        from Recipe);

                        _Xml := xmlconcat(xmlparse(content xpath('//*[local-name() = ''ClinicalDocument'']' passing xml)), xmlelement(name 'Replace', xmlparse(content xpath('//xmlns:Replace' passing _Xml)), _documentationOf));
                        _Xml := xmlconcat(xmlparse(content xpath('//*[local-name() = ''ClinicalDocument'']' passing xml)), xmlelement(name 'Replace', xmlparse(content xpath('//xmlns:Replace' passing _Xml)), _legalAuthenticator));
                        _Xml := xmlconcat(xmlparse(content xpath('//*[local-name() = ''ClinicalDocument'']' passing xml)), xmlelement(name 'Replace', xmlparse(content xpath('//xmlns:Replace' passing _Xml)), _informationRecipient));
                        _Xml := xmlconcat(xmlparse(content xpath('//*[local-name() = ''ClinicalDocument'']' passing xml)), xmlelement(name 'Replace', xmlparse(content xpath('//xmlns:Replace' passing _Xml)), _custodian));
                        _Xml := xmlconcat(xmlparse(content xpath('//*[local-name() = ''ClinicalDocument'']' passing xml)), xmlelement(name 'Replace', xmlparse(content xpath('//xmlns:Replace' passing _Xml)), _author));
                        _Xml := xmlconcat(xmlparse(content xpath('//*[local-name() = ''ClinicalDocument'']' passing xml)), xmlelement(name 'Replace', xmlparse(content xpath('//xmlns:Replace' passing _Xml)), _XmlDop));
                        
                        if _delayedTabl is not null
                        then
                            _Xml := xmlelement(name 'ClinicalDocument', 
                                xmlconcat(xpath('//*[local-name() = ''component'']' passing _Xml), 
                                xmlelement(name 'structuredBody', xmlconcat(xpath('//*[local-name() = ''section'']//text' passing _Xml),_delayedTabl)),
                                xmlelement(name 'component', xmlelement(name 'section', _delayedData))
                                )
                            );
                        end if;

                        _Xml := cast(replace(cast(_Xml as text)),  ' xmlns=""', '') as xml);
                        select _Xml;

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