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,
	ltrim(rtrim( 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,
	(case when parametro.Description is null then '' else UPPER(REPLACE(parametro.Description,'-' ,' ' )) 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 [DEGREES].[std].[tblSystemParameter] parametro   on expedient.RolID=parametro.Code
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