alter table STD.tblExpedient
add SharepointID int null
go

ALTER view [STD].[view_expedient]
as

Select
convert(varchar(20), expedient.ExpedientID) + '-' + convert(varchar(20), IsNull(expedient_derivation.ExpedientDerivationID, 0)) ExpedientCompID,
expedient.ExpedientID,
IsNull(expedient_derivation.ExpedientDerivationID, 0) ExpedientDerivationID,
expedient.IsDeleted,
expedient.ExpedientNumber,
expedient.DocTypeID,
expedient.CategoryID,
expedient.Campus,
IsNull(expedient.DocumentNumber, '') DocumentNumber,
expedient.RegisterDate,
expedient.DueDate,
expedient.ArrivalWay,
expedient.[Priority],
IsNull(priorities.[Description], '') PriorityName,
IsNull(IsNull(external_company.RazSoc, expedient.ClientName), '') ExternalCompanyName,
--'' ExternalCompanyName,
IsNull(business_area.[Description], '') BusinessAreaName,
(Case When IsNull(expedient.DueDate, 0) != 0 Then DATEDIFF(day, expedient.DueDate, getdate()) Else 0 End) NumberPastDue,
(Case When isnull(origin_management_derivation.descripcion_puesto, '') = '' then isnull(derivation_module.moduleDescription, '')
      Else IsNull(origin_management_derivation.descripcion_puesto collate SQL_Latin1_General_CP1_CI_AS, module.moduleDescription) End) ManagementOriginName,
IsNull(expedient_derivation.OriginID, expedient.RolID) ManagementOriginId,
(Case When IsNull(expedient_derivation.ExpedientDerivationID, 0) != 0 Then
        (Case When expedient_derivation.[type] = (Select ParameterID From tblSystemParameter Where IsDerivedTo = 1) Then 0 Else 1 End) Else 0 End) IsCopy,
IsNull(expedient_derivation.DestinationID, '') DestinationID,
IsNull(expedient.DelegationManagementId, '') DelegationManagementId,
(Case When isnull(destination_management_derivation.descripcion_puesto, '') = '' then isnull(destination_module.moduleDescription, '')
      Else IsNull(destination_management_derivation.descripcion_puesto collate SQL_Latin1_General_CP1_CI_AS, module.moduleDescription) End) ManagementDestinationName,

IsNull(delegation_management.descripcion_puesto, '') ManagementDelegationName,

IsNull(expedient_derivation.StateID, expedient.StateId) StateID,
IsNull(expedient_derivation.StateID, 0) StateDerivationID,
IsNull(state_derivation.[Description], states.[Description]) StateName,
expedient.Confidentiality,
IsNull(expedient.[Subject], '') [Subject],
document_type.[Description] DocumentTypeName,
category.[Description] CategoryName,
'' DerivationComment,
'' CancelComment,
LTrim(Lower(expedient.InsertUserId)) InsertUserId,
(Case When IsNull(expedient.DelegationUserId, '') != '' Then expedient.DelegationUserId
      When expedient_derivation.StateID <> (Select ParameterID From STD.tblSystemParameter Where IsRegisterState = 1) Then IsNull(expedient_derivation.DestinationUserID, expedient.InsertUserId)
      Else expedient.InsertUserId End) DestinationUser,
IsNull(expedient.DelegationUserId, '') DelegationUser,
(Case When IsNull(expedient_courier.ReceivedDate, '') != '' Then 1 Else 0 End) IsCourierReceived,
expedient_courier.ReceivedDate CourierReceivedDate,
expedient_courier.SendDate CourierSendDate,
IsNull(expedient_courier.Destination, '') Destination,
(Case When IsNull((select count(*) from STD.tblExpedientDocument where ExpedientID = expedient.ExpedientID And IsDeleted = 0), 0) != 0 Then 1 Else 0 End) HasDocuments,
(Case When IsNull((select count(*) from STD.tblExpedientDerivation where ExpedientID = expedient.ExpedientID And IsDeleted = 0), 0) != 0 Then 1 Else 0 End) HasDerivations,
Isnull(expedient_derivation.Received,0) Received,
Isnull(expedient_derivation.Worked,0) Worked,
IsNull((Select Distinct 1 From STD.tblExpedientDerivation where ExpedientID = expedient.ExpedientID And Received = 1), 0) as Block,
IsNull(courier.Name, '') CourierName,
Sender,
isnull(RolID, 0) as RolID,
isnull(BusinessAreaID, 0) as BusinessAreaID,
isnull(expedient_derivation.UpdateUserId, '') as DerivationUpdateUserId,
isnull(SharepointID,0) as SharepointID
From STD.tblExpedient expedient
inner join STD.tblSystemParameter document_type on expedient.DocTypeID = document_type.ParameterID
inner join STD.tblSystemParameter category on expedient.CategoryID = category.ParameterID
inner join STD.tblSystemParameter states on expedient.StateID = states.ParameterID
left join BDINTBANNER.ACCESS.TblGroupModule group_module on group_module.groupID = expedient.RolID aND group_module.campus = expedient.Campus
left join BDINTBANNER.ACCESS.TblModule module on group_module.moduleID = module.moduleID
left join STD.tblSystemParameter priorities on expedient.Priority = priorities.ParameterID
left join STD.tblSystemParameter business_area on expedient.BusinessAreaID = business_area.ParameterID
left join BDUCCI.dbo.tblInstitucion external_company on expedient.ExternalCompanyID = external_company.IDInstitucionN
left join STD.tblExpedientCourier expedient_courier on expedient.ExpedientID = expedient_courier.ExpedientID
left join STD.tblCourier courier on expedient_courier.CourierID = courier.CourierID
left join STD.tblExpedientDerivation expedient_derivation on expedient.ExpedientID = expedient_derivation.ExpedientID And expedient_derivation.Type = 37
left join STD.tblSystemParameter state_derivation on expedient_derivation.StateID = state_derivation.ParameterID

left join BDINTBANNER.ACCESS.TblGroupModule derivation_group_module on derivation_group_module.groupID = expedient_derivation.OriginID And derivation_group_module.campus = expedient.Campus
left join BDINTBANNER.ACCESS.TblModule derivation_module on derivation_group_module.moduleID = derivation_module.moduleID

left join BDINTBANNER.ACCESS.TblGroupModule destination_group_module on destination_group_module.groupID = expedient_derivation.DestinationID And destination_group_module.campus = expedient.Campus
left join BDINTBANNER.ACCESS.TblModule destination_module on destination_group_module.moduleID = destination_module.moduleID

left join [BDINTBANNER].[ADRYAN].[Tbl_puesto_compania] origin_management_derivation on expedient_derivation.OriginID = origin_management_derivation.puesto collate SQL_Latin1_General_CP1_CI_AS and origin_management_derivation.compania = '01' collate SQL_Latin1_General_CP1_CI_AS
left join [BDINTBANNER].[ADRYAN].[Tbl_puesto_compania] destination_management_derivation on expedient_derivation.DestinationID = destination_management_derivation.puesto collate SQL_Latin1_General_CP1_CI_AS and destination_management_derivation.compania ='01' collate SQL_Latin1_General_CP1_CI_AS
left join [BDINTBANNER].[ADRYAN].[Tbl_puesto_compania] delegation_management on expedient.DelegationManagementId = delegation_management.puesto collate SQL_Latin1_General_CP1_CI_AS and destination_management_derivation.compania ='01' collate SQL_Latin1_General_CP1_CI_AS
GO