daily pastebin goal
11%
SHARE
TWEET

Untitled

a guest Jun 13th, 2018 51 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. alter table STD.tblExpedient
  2. add SharepointID int null
  3. go
  4.  
  5. ALTER view [STD].[view_expedient]
  6. as
  7.  
  8. Select
  9. convert(varchar(20), expedient.ExpedientID) + '-' + convert(varchar(20), IsNull(expedient_derivation.ExpedientDerivationID, 0)) ExpedientCompID,
  10. expedient.ExpedientID,
  11. IsNull(expedient_derivation.ExpedientDerivationID, 0) ExpedientDerivationID,
  12. expedient.IsDeleted,
  13. expedient.ExpedientNumber,
  14. expedient.DocTypeID,
  15. expedient.CategoryID,
  16. expedient.Campus,
  17. IsNull(expedient.DocumentNumber, '') DocumentNumber,
  18. expedient.RegisterDate,
  19. expedient.DueDate,
  20. expedient.ArrivalWay,
  21. expedient.[Priority],
  22. IsNull(priorities.[Description], '') PriorityName,
  23. IsNull(IsNull(external_company.RazSoc, expedient.ClientName), '') ExternalCompanyName,
  24. --'' ExternalCompanyName,
  25. IsNull(business_area.[Description], '') BusinessAreaName,
  26. (Case When IsNull(expedient.DueDate, 0) != 0 Then DATEDIFF(day, expedient.DueDate, getdate()) Else 0 End) NumberPastDue,
  27. (Case When isnull(origin_management_derivation.descripcion_puesto, '') = '' then isnull(derivation_module.moduleDescription, '')
  28.       Else IsNull(origin_management_derivation.descripcion_puesto collate SQL_Latin1_General_CP1_CI_AS, module.moduleDescription) End) ManagementOriginName,
  29. IsNull(expedient_derivation.OriginID, expedient.RolID) ManagementOriginId,
  30. (Case When IsNull(expedient_derivation.ExpedientDerivationID, 0) != 0 Then
  31.         (Case When expedient_derivation.[type] = (Select ParameterID From tblSystemParameter Where IsDerivedTo = 1) Then 0 Else 1 End) Else 0 End) IsCopy,
  32. IsNull(expedient_derivation.DestinationID, '') DestinationID,
  33. IsNull(expedient.DelegationManagementId, '') DelegationManagementId,
  34. (Case When isnull(destination_management_derivation.descripcion_puesto, '') = '' then isnull(destination_module.moduleDescription, '')
  35.       Else IsNull(destination_management_derivation.descripcion_puesto collate SQL_Latin1_General_CP1_CI_AS, module.moduleDescription) End) ManagementDestinationName,
  36.  
  37. IsNull(delegation_management.descripcion_puesto, '') ManagementDelegationName,
  38.  
  39. IsNull(expedient_derivation.StateID, expedient.StateId) StateID,
  40. IsNull(expedient_derivation.StateID, 0) StateDerivationID,
  41. IsNull(state_derivation.[Description], states.[Description]) StateName,
  42. expedient.Confidentiality,
  43. IsNull(expedient.[Subject], '') [Subject],
  44. document_type.[Description] DocumentTypeName,
  45. category.[Description] CategoryName,
  46. '' DerivationComment,
  47. '' CancelComment,
  48. LTrim(Lower(expedient.InsertUserId)) InsertUserId,
  49. (Case When IsNull(expedient.DelegationUserId, '') != '' Then expedient.DelegationUserId
  50.       When expedient_derivation.StateID <> (Select ParameterID From STD.tblSystemParameter Where IsRegisterState = 1) Then IsNull(expedient_derivation.DestinationUserID, expedient.InsertUserId)
  51.       Else expedient.InsertUserId End) DestinationUser,
  52. IsNull(expedient.DelegationUserId, '') DelegationUser,
  53. (Case When IsNull(expedient_courier.ReceivedDate, '') != '' Then 1 Else 0 End) IsCourierReceived,
  54. expedient_courier.ReceivedDate CourierReceivedDate,
  55. expedient_courier.SendDate CourierSendDate,
  56. IsNull(expedient_courier.Destination, '') Destination,
  57. (Case When IsNull((select count(*) from STD.tblExpedientDocument where ExpedientID = expedient.ExpedientID And IsDeleted = 0), 0) != 0 Then 1 Else 0 End) HasDocuments,
  58. (Case When IsNull((select count(*) from STD.tblExpedientDerivation where ExpedientID = expedient.ExpedientID And IsDeleted = 0), 0) != 0 Then 1 Else 0 End) HasDerivations,
  59. Isnull(expedient_derivation.Received,0) Received,
  60. Isnull(expedient_derivation.Worked,0) Worked,
  61. IsNull((Select Distinct 1 From STD.tblExpedientDerivation where ExpedientID = expedient.ExpedientID And Received = 1), 0) as Block,
  62. IsNull(courier.Name, '') CourierName,
  63. Sender,
  64. isnull(RolID, 0) as RolID,
  65. isnull(BusinessAreaID, 0) as BusinessAreaID,
  66. isnull(expedient_derivation.UpdateUserId, '') as DerivationUpdateUserId,
  67. isnull(SharepointID,0) as SharepointID
  68. From STD.tblExpedient expedient
  69. inner join STD.tblSystemParameter document_type on expedient.DocTypeID = document_type.ParameterID
  70. inner join STD.tblSystemParameter category on expedient.CategoryID = category.ParameterID
  71. inner join STD.tblSystemParameter states on expedient.StateID = states.ParameterID
  72. left join BDINTBANNER.ACCESS.TblGroupModule group_module on group_module.groupID = expedient.RolID aND group_module.campus = expedient.Campus
  73. left join BDINTBANNER.ACCESS.TblModule module on group_module.moduleID = module.moduleID
  74. left join STD.tblSystemParameter priorities on expedient.Priority = priorities.ParameterID
  75. left join STD.tblSystemParameter business_area on expedient.BusinessAreaID = business_area.ParameterID
  76. left join BDUCCI.dbo.tblInstitucion external_company on expedient.ExternalCompanyID = external_company.IDInstitucionN
  77. left join STD.tblExpedientCourier expedient_courier on expedient.ExpedientID = expedient_courier.ExpedientID
  78. left join STD.tblCourier courier on expedient_courier.CourierID = courier.CourierID
  79. left join STD.tblExpedientDerivation expedient_derivation on expedient.ExpedientID = expedient_derivation.ExpedientID And expedient_derivation.Type = 37
  80. left join STD.tblSystemParameter state_derivation on expedient_derivation.StateID = state_derivation.ParameterID
  81.  
  82. left join BDINTBANNER.ACCESS.TblGroupModule derivation_group_module on derivation_group_module.groupID = expedient_derivation.OriginID And derivation_group_module.campus = expedient.Campus
  83. left join BDINTBANNER.ACCESS.TblModule derivation_module on derivation_group_module.moduleID = derivation_module.moduleID
  84.  
  85. left join BDINTBANNER.ACCESS.TblGroupModule destination_group_module on destination_group_module.groupID = expedient_derivation.DestinationID And destination_group_module.campus = expedient.Campus
  86. left join BDINTBANNER.ACCESS.TblModule destination_module on destination_group_module.moduleID = destination_module.moduleID
  87.  
  88. 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
  89. 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
  90. 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
  91. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top