Notes

with
UTM as (
select
"@УТМ"
from "УТМ"
--where "Организация" = 1
),
InDocs as (
select
"@Документ",
"Содержимое",
"Документ"."ДатаВремя",
"УведомлениеЭДО",
"Тип",
'Входящий'::TEXT as "ТипДокумент",
"ВхДокумент"."Статус" AS "Статус",
"ВхДокумент"."ДатаВремя" AS "ПоследняяОбработка"
from "ВхДокумент"
inner join UTM on "УТМ" = "@УТМ"
left join "Документ" on "Документ" = "@Документ"
where "Документ"."Тип" = ANY(ARRAY[10,30,50])
order by "@ВхДокумент" DESC
limit 10
),
OutDocs as (
select
"@Документ",
"Содержимое",
"Документ"."ДатаВремя",
"УведомлениеЭДО",
"Тип",
'Исходящий'::TEXT as "ТипДокумент",
"ИсхДокумент"."Статус" AS "Статус",
"ИсхДокумент"."ДатаВремя" AS "ПоследняяОбработка"
from "ИсхДокумент"
inner join UTM on "УТМ" = "@УТМ"
left join "Документ" on "Документ" = "@Документ"
where "Документ"."Тип" = ANY(ARRAY[10,30,50])
order by "@ИсхДокумент" DESC
limit 10

),
AggResult as (
select * from InDocs
union
select * from OutDocs
order by "ДатаВремя" DESC
limit 10
)
select
"@Документ",
"Содержимое",
"ДатаВремя",
"УведомлениеЭДО",
"Статус",
"ПоследняяОбработка",
"Тип",
"ТипДокумент",

CASE
WHEN "Тип" = 10
THEN (SELECT ARRAY_TO_STRING(REGEXP_MATCHES("Содержимое", '<w+:s*Shipper.*>.*<(?:w+:)?s*FullName.*>(d+)</(?:w+:)?s*FullNames*>.*</w+:s*Shippers*>'), '.'))
ELSE NULL
END AS "ОтправительНазвание",
CASE
WHEN "Тип" = 10
THEN (SELECT ARRAY_TO_STRING(REGEXP_MATCHES("Содержимое", '<w+:s*Shipper.*>.*<(?:w+:)?s*INN.*>(d+)</(?:w+:)?s*INNs*>.*</w+:s*Shippers*>'), '.'))
ELSE NULL
END AS "ОтправительИНН",
CASE
WHEN "Тип" = 10
THEN (SELECT ARRAY_TO_STRING(REGEXP_MATCHES("Содержимое", '<w+:s*Shipper.*>.*<(?:w+:)?s*KPP.*>(d+)</(?:w+:)?s*KPPs*>.*</w+:s*Shippers*>'), '.'))
ELSE NULL
END AS "ОтправительКПП",
CASE
WHEN "ТипДокумент" = 'Исходящий'
THEN (SELECT ARRAY_TO_STRING(REGEXP_MATCHES("Содержимое", '<w+:s*Owner.*>.*<(?:w+:)?s*FSRAR_ID.*>(d+)</(?:w+:)?s*FSRAR_IDs*>.*</w+:s*Owners*>'), '.'))
ELSE (SELECT ARRAY_TO_STRING(REGEXP_MATCHES("Содержимое", '<w+:s*Shipper.*>.*<(?:w+:)?s*ClientRegId.*>(d+)</(?:w+:)?s*ClientRegIds*>.*</w+:s*Shippers*>'), '.'))
END AS "ОтправительФСРАР",

CASE
WHEN "Тип" = 10
THEN (SELECT ARRAY_TO_STRING(REGEXP_MATCHES("Содержимое", '<w+:s*Consignee.*>.*<(?:w+:)?s*FullName.*>(d+)</(?:w+:)?s*FullNames*>.*</w+:s*Consignees*>'), '.'))
ELSE NULL
END AS "ПолучательНазвание",
CASE
WHEN "Тип" = 10
THEN (SELECT ARRAY_TO_STRING(REGEXP_MATCHES("Содержимое", '<w+:s*Consignee.*>.*<(?:w+:)?s*INN.*>(d+)</(?:w+:)?s*INNs*>.*</w+:s*Consignees*>'), '.'))
ELSE NULL
END AS "ПолучательИНН",
CASE
WHEN "Тип" = 10
THEN (SELECT ARRAY_TO_STRING(REGEXP_MATCHES("Содержимое", '<w+:s*Consignee.*>.*<(?:w+:)?s*KPP.*>(d+)</(?:w+:)?s*KPPs*>.*</w+:s*Consignees*>'), '.'))
ELSE NULL
END AS "ПолучательКПП",
CASE
WHEN "Тип" = 10
THEN (SELECT ARRAY_TO_STRING(REGEXP_MATCHES("Содержимое", '<w+:s*Consignee.*>.*<(?:w+:)?s*ClientRegId.*>(d+)</(?:w+:)?s*ClientRegIds*>.*</w+:s*Consignees*>'), '.'))
ELSE NULL
END AS "ПолучательФСРАР"
from AggResult
left join "ЭДОДокумент" on "@Документ" = "Документ"