Перейти к содержанию

Сборка документов, участвующих в документообороте, в БД#

Общее описание#

Для участия документов определенного типа и успешного запуска этих документов по маршруту необходимо:

  1. В БД прописать таблицу, на которой построен тип документа, в представлении DocflowExists.
  2. Выполнить для типа документа "Настройку документа" (после чего обновить страницу).
  3. Создать для типа документа шаблон маршрута.

В данном разделе описывается первый шаг: настройка представления DocflowExists. В ней содержатся данные обо всех документах, участвующих в документообороте. Не внесённые в неё типы документов не отображаются на панели "Документооборот" на главной форме и не отправляются по маршруту.

Назначение полей#

Представление DocflowExists содержит данные о разных типах документов, для каждого из которых можно сопоставить следующие поля:

  • VCode – обязательное, целое число. Содержит уникальный код документа.
  • TypeName – обязательное. Содержит тип документа.
  • Subject – содержит тему документа.
  • Text – содержит основной текст документа.
  • DateOfExecution – содержит плановую дату исполнения.
  • RealDateOfExecution – содержит фактическую дату исполнения.
  • DocDescription – содержит описание документа.
  • Description – содержит описание содержимого документа.
  • DateControl – содержит дату проверки.
  • COrg – содержит код организации, в которой находился создавший документ пользователь на момент создания документа.
  • DocumentDate – содержит дату документа.
  • DocumentNumber – содержит номер документа, который может настраиваться с помощью журнала регистрации.
  • VirtualCUser – содержит логин виртуального (замещаемого) пользователя.
  • Projects – содержит уникальный код проекта.
  • SourceDocumentCode – содержит уникальный код документа-источника, из которого был создан текущий.
  • SourceDocumentType – содержит тип документа-источника.
  • GlobalId – содержит глобальный уникальный идентификатор, .

Не все поля обязательны к заполнению. Если такие поля не участвуют в бизнес-процессе документа, им присваивается NULL.

Для внесения в представление документов определённого типа необходимо подключать соответствующие им таблицы. Подключение новых таблиц происходит через union all.

Примеры#

Ниже приведены примеры представления DocflowExists на двух разных проектах. В первом может быть интересна настройка карточек СЭД (from dbo.DocflowDocument), во втором содержится настройка большего количества типов документов.

Пример 1#

ALTER VIEW [dbo].[DocflowExists]
AS
select a.VCode, a.TypeName, a.[Subject], a.[Text], a.DateOfExecution, a.RealDateOfExecution, a.DocDescription, a.[Description], a.DateControl, a.COrg, a.DocumentDate, a.DocumentNumber, a.VirtualCUser,Projects =CONVERT(bigint, null ),
a.GlobalID, a.DocumentSubtype, a.SourceDocumentCode,a.SourceDocumentType
from (
        select VCode, TypeName, [Subject], [Text] = convert(varchar(max), NULL),
        DateOfExecution = case TypeName when 'Instruction' then DateOfExecution
                                        when 'IncomingDocument' then DateOfExecution
                                        else convert(datetimeoffset, NULL) end,
        RealDateOfExecution = case TypeName when 'Instruction' then RealDateOfExecution else convert(datetimeoffset, NULL) end,
        DocDescription = convert(varchar(max), NULL), [Description] = convert(varchar(max), NULL),
            DateControl = convert(datetimeoffset, NULL), COrg,
            DocumentDate = case when TypeName in ('IncomingDocument', 'OrganizationalDocument', 'OutgoingDocument')
                                            then  convert(date,(coalesce([DateOfRegistration],[DocumentDate])))
                                    else convert(date,DocumentDate) end,
        DocumentNumber,
        VirtualCUser = coalesce(VirtualCUser, CUser),
        GlobalID,
        DocumentSubtype,
        SourceDocumentCode = convert(bigint,null),
        SourceDocumentType = convert(varchar(255),null)
        from dbo.DocflowDocument
        union all
        select VCode, TypeName, [Subject] = SubjectOfContract, [Text] = convert(varchar(max), NULL), DateOfExecution = convert(datetimeoffset, NULL),
                RealDateOfExecution = convert(datetimeoffset, NULL), DocDescription = convert(varchar(max), NULL), [Description] = convert(varchar(max), NULL),
            DateControl = convert(datetimeoffset, NULL), COrg,
            DocumentDate = DateContract,
            DocumentNumber = VhodNumber,
            VirtualCUser = coalesce(VirtualCUser, CUser),
        GlobalID = convert(uniqueidentifier, null),
        DocumentSubtype = convert(bigint,null),
        SourceDocumentCode = convert(bigint,null),
        SourceDocumentType = convert(varchar(255),null)
        from dbo.[Contract]
        union all
        select VCode, TypeName, [Subject] = SubjectOfContract, [Text] = convert(varchar(max), NULL), DateOfExecution = convert(datetimeoffset, NULL),
            RealDateOfExecution = convert(datetimeoffset, NULL), DocDescription = convert(varchar(max), NULL), [Description] = convert(varchar(max), NULL),
        DateControl = convert(datetimeoffset, NULL), COrg,
        DocumentDate = DateContract,
            DocumentNumber = Number,
            VirtualCUser = coalesce(VirtualCUser, CUser),
        GlobalID = convert(uniqueidentifier, null),
        DocumentSubtype = convert(bigint,null),
        SourceDocumentCode = convert(bigint,null),
        SourceDocumentType = convert(varchar(255),null)
        from dbo.[AdditionalContract]

        union all
    select VCode, 'Test',null , [Text] = convert(varchar(max), NULL), DateOfExecution = convert(datetimeoffset, NULL),
            RealDateOfExecution = convert(datetimeoffset, NULL), DocDescription = convert(varchar(max), NULL), [Description] = convert(varchar(max), NULL),
        DateControl = convert(datetimeoffset, NULL), 1,
        DocumentDate = null,
            DocumentNumber = null,
            VirtualCUser = null,
        GlobalID = convert(uniqueidentifier, null),
        DocumentSubtype = convert(bigint,null),
        SourceDocumentCode = convert(bigint,null),
        SourceDocumentType = convert(varchar(255),null)
        from dbo.[Test]
            union all
    select VCode, TypeName, [Subject] = Text, [Text] = convert(varchar(max), Text), DateOfExecution = convert(datetimeoffset, NULL),
            RealDateOfExecution = convert(datetimeoffset, NULL), DocDescription = convert(varchar(max), NULL), [Description] = convert(varchar(max), NULL),
        DateControl = convert(datetimeoffset, NULL), COrg,
        DocumentDate ,
            DocumentNumber ,
            VirtualCUser = coalesce(VirtualCUser, CUser),
        GlobalID,
        DocumentSubtype ,
        SourceDocumentCode = convert(bigint,null),
        SourceDocumentType = convert(varchar(255),null)
        from dbo.[UniversalDocument]
            union all
    select VCode, TypeName, [Subject] = Subject, [Text] = convert(varchar(max), Text), DateOfExecution = convert(datetimeoffset, NULL),
            RealDateOfExecution = convert(datetimeoffset, NULL), DocDescription = convert(varchar(max), NULL), [Description] = convert(varchar(max), NULL),
        DateControl = convert(datetimeoffset, NULL), COrg,
        DocumentDate = DocDate ,
            DocumentNumber  = null,
            VirtualCUser = coalesce(VirtualCUser, CUser),
        GlobalID ,
        DocumentSubtype = convert(bigint,null),
        SourceDocumentCode = convert(bigint,null),
        SourceDocumentType = convert(varchar(255),null)
        from dbo.[CancellationAct]

     ) as a

Пример 2#

ALTER VIEW [dbo].[DocflowExists]
AS
-- Общее представление документов СЭД
SELECT a."VCode"
     , a."TypeName"
     , a."Subject"
     , a."Text"
     , a."DateOfExecution"
     , a."RealDateOfExecution"
     , a."DocDescription"
     , a."Description"
     , a."DateControl"
     , a."COrg"
     , a."DocumentDate"
     , a."DocumentNumber"
     , a."VirtualCUser"
     , a."Projects"
     , a."SourceDocumentCode"
     , a."SourceDocumentType"
     , a."GlobalId"
FROM(
  -- Заявки LexDesk
  SELECT t."VCode"
       , t."TypeName"
       , t."Name" AS "Subject"
       , t."Description" AS "Text"
       , CONVERT(datetimeoffset, t.Deadline) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , t."Name" AS "DocDescription"
       , ('"' + t."Name" + '" ' + t."Description") AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, t."CDate") AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, t.Project) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."Ticket" AS t
  -- Карточки СЭД
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , t."Subject"
       , t."Text"
       , t."DateOfExecution"
       , t."RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , t."PeriodDate" AS "DocumentDate"
       , t."DocumentNumber"
       , COALESCE(t."VirtualCUser", t."CUser") AS "VirtualCUser"
       , t."Projects"
       , t."SourceDocumentCode"
       , t."SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."DocflowDocument" AS t
  -- Договора
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , t."SubjectOfContract" AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , t."DateContract" AS "DocumentDate"
       , t."VhodNumber" AS "DocumentNumber"
       , COALESCE(t."VirtualCUser", t."CUser") AS "VirtualCUser"
       , t."Projects"
       , t."SourceDocumentCode"
       , t."SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."Contract" AS t
  -- Приложения к договорам
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , t.SubjectOfContract AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , t."DateContract" AS "DocumentDate"
       , t."Number" AS "DocumentNumber"
       , COALESCE(t."VirtualCUser", t."CUser") AS "VirtualCUser"
       , t."Projects"
       , t."SourceDocumentCode"
       , t."SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."AdditionalContract" AS t
  -- Контрагенты, интересы
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , CONVERT(varchar(max), NULL) AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, t."CDate") AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."Contractor" AS t
  UNION ALL
  SELECT t."VCode"
       , 'ProjectsMakeDetail' AS "TypeName"
       , t."Comment" AS "Subject"
       , t."Comment" AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), t."Comment") + ' (' +  CONVERT(varchar(max), t."ContractorName") + ')' AS "DocDescription"
       , CONVERT(varchar(max), t."Comment") + ' (' +  CONVERT(varchar(max), t."ContractorName") + ') ' + COALESCE('Результат действия: ' + CONVERT(varchar(max), t."Result"), '') AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, t."CDate") AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."V_crm_ProjectsMakeDetail" AS t
  -- Проекты
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , t."Goal" AS "Subject"
       , CONVERT(varchar(max), t."Goal") AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), t."Goal") AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, t."CDate") AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , t."VCode" AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."Projects" AS t
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , CONVERT(varchar(max), NULL) AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, t."CDate") AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."CommercialProposal" AS t
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , CONVERT(varchar(max), NULL) AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, t."CDate") AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."WriteOffExpensesFP" AS t
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , t."AnalyticsName" AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, t."CDate") AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."ExpensesFuture" AS t
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , t."AnalyticsName" AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, t."CDate") AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."ReceiptOfBill" AS t
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , t."AnalyticsName" AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, t."CDate") AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."ExpenseOfBill" AS t
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , CONVERT(varchar(max), t."TypeDocs") AS "Subject"
       , CONVERT(varchar(max), t."Note") AS "Text"
       , t."CDate" AS "DateOfExecution"
       , t."WDate" AS "RealDateOfExecution"
       , CONVERT(varchar(max), t."Note") AS "DocDescription"
       , CONVERT(varchar(max), t."Note") AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, t."CDate") AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."VOtherDocuments" AS t
  -- Услуги
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , CONVERT(varchar(max), NULL) AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, t."AccountingDate") AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."ServiceReceipts" AS t
  -- Заявки на платеж
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , CONVERT(varchar(max), '') + CASE WHEN rc."VCode" IS NULL THEN '' ELSE 'Получатель: ' + rc."Name" END AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , t."PaymentDestination" AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , t."AccountingDate" AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , t."Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."RequestForPayment" AS t
    LEFT JOIN dbo."UniAnalyt" AS rc ON rc."VCode" = t."Receiver"
  -- Банковские документы
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , CONVERT(varchar(max), NULL) AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, t."AccountingDate") AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."FinanceMoving" AS t
  -- TestECP
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , CONVERT(varchar(max), 'TestEcp') AS "Subject"
       , CONVERT(varchar(max), 'TestEcp') AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , CONVERT(bigint, NULL) AS "COrg"
       , CONVERT(date, NULL) AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,'EEDB3414-EFA5-4610-A7D5-2B3FD6FEA152') as "GlobalId"
  FROM dbo."TestEcp" AS t
  -- KarginTest
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , CONVERT(varchar(max), 'Документ') AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , CONVERT(date, NULL) AS "DocumentDate"
       , CONVERT(varchar(255), NULL) AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."KarginTest" AS t
  -- Универсальный документ
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , COALESCE(t."Name",'Документ') AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , t."DocumentDate"
       , t."DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."UniversalDocument" AS t
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , CONVERT(varchar(max), NULL) AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , t."DateDoc" AS "DocumentDate"
       , t."NumDoc" AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo."RP_DocAbsence" AS t
  WHERE t."TypeName" = 'Base.RP_OrderVacation'
  UNION ALL
  SELECT t."VCode"
       , t."TypeName"
       , CONVERT(varchar(max), NULL) AS "Subject"
       , CONVERT(varchar(max), NULL) AS "Text"
       , CONVERT(datetimeoffset, NULL) AS "DateOfExecution"
       , CONVERT(datetimeoffset, NULL) AS "RealDateOfExecution"
       , CONVERT(varchar(max), NULL) AS "DocDescription"
       , CONVERT(varchar(max), NULL) AS "Description"
       , CONVERT(datetimeoffset, NULL) AS "DateControl"
       , t."COrg"
       , t."AccountingDate" AS "DocumentDate"
       , t."DocumentNumber" AS "DocumentNumber"
       , t."CUser" AS "VirtualCUser"
       , CONVERT(bigint, NULL) AS "Projects"
       , CONVERT(bigint, NULL) AS "SourceDocumentCode"
       , CONVERT(varchar(255), NULL) AS "SourceDocumentType"
       , CONVERT(uniqueidentifier,null) as "GlobalId"
  FROM dbo.ActOfReconciliation AS t
  WHERE t."TypeName" = 'Base.ActOfReconciliation'
) AS a