Сборка документов, участвующих в документообороте, в БД#
Общее описание#
Для участия документов определенного типа и успешного запуска этих документов по маршруту необходимо:
- В БД прописать таблицу, на которой построен тип документа, в представлении
DocflowExists
. - Выполнить для типа документа "Настройку документа" (после чего обновить страницу).
- Создать для типа документа шаблон маршрута.
В данном разделе описывается первый шаг: настройка представления 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