INSERT INTO dbo.Sys_ObjetosApp (IdObj, IdGrupo,	SubModulo, IndObj, Nombre, Formulario, Permisos, NivelMinimo, NomArchivo)
--Catálogos tablas básicas,
VALUES
('FRMOTROSI','TESPRO','TES',3,'OtroSí de Contratos','FRMDOTROSI','S',4,NULL)
GO
INSERT INTO dbo.Sys_Report VALUES
('COE','12','Listado Contratos (OtroSí)','LtCoeDtOtS.rpt',1,2,1,0,1,'paWcfSeLisTrn_TraEspOtroSiFmt','','Listado de Contratos'),
('COE','13','Listado OtroSí (No Aplicados)','LtCoeDtOtSNA.rpt',1,2,1,0,1,'paWcfSeLisTrn_TraEspOtroSiFmt','','Listado de Contratos')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Trn_TraEspOtroSi](
	[IdOtroSi] [int] IDENTITY(0,1) NOT NULL,
	[Fecha] [smalldatetime] NOT NULL,
	[Adicion] [bit] NOT NULL,
	[CFecha] [bit] NOT NULL,
	[Items] [bit] NOT NULL,
	[Aplicado] [bit] NOT NULL,
	[TimeSys] [smalldatetime] NOT NULL,
	[Observacion] [varchar](500) NULL,
	[IdUsuario] [varchar](11) NOT NULL,
	[Anulado] [bit] NOT NULL,
	[FecDev] [smalldatetime] NULL,
 CONSTRAINT [PK_Trn_TraEspOtroSi] PRIMARY KEY CLUSTERED 
(
	[IdOtroSi] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Trn_TraEspOtroSiContratos]    Script Date: 13/07/2019 11:34:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Trn_TraEspOtroSiContratos](
	[IdOtroSi] [int] NOT NULL,
	[TipDocCnt] [varchar](3) NOT NULL,
	[Contrato] [int] NOT NULL,
	[IdCiaCnt] [char](2) NOT NULL,
	[FechaFinAnt] [smalldatetime] NOT NULL,
	[FechaFinNew] [smalldatetime] NOT NULL,
	[VrPpto] [money] NOT NULL,
	[VrSaldoPpo] [money] NOT NULL,
	[VrAdicionPpto] [money] NOT NULL,
 CONSTRAINT [PK_Trn_TraEspOtroSiContratos] PRIMARY KEY CLUSTERED 
(
	[IdOtroSi] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Trn_TraEspOtroSi] ADD  CONSTRAINT [DF_Trn_TraEspOtroSi_Adicion]  DEFAULT ((0)) FOR [Adicion]
GO
ALTER TABLE [dbo].[Trn_TraEspOtroSi] ADD  CONSTRAINT [DF_Trn_TraEspOtroSi_CFecha]  DEFAULT ((0)) FOR [CFecha]
GO
ALTER TABLE [dbo].[Trn_TraEspOtroSi] ADD  CONSTRAINT [DF_Trn_TraEspOtroSi_Anulado]  DEFAULT ((0)) FOR [Anulado]
GO
ALTER TABLE [dbo].[Trn_TraEspOtroSi]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspOtroSi_adm_Usuarios] FOREIGN KEY([IdUsuario])
REFERENCES [dbo].[adm_Usuarios] ([IdUsuario])
GO
ALTER TABLE [dbo].[Trn_TraEspOtroSi] CHECK CONSTRAINT [FK_Trn_TraEspOtroSi_adm_Usuarios]
GO
ALTER TABLE [dbo].[Trn_TraEspOtroSiContratos]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspOtroSiContratos_Trn_TraEspContratos] FOREIGN KEY([TipDocCnt], [Contrato], [IdCiaCnt])
REFERENCES [dbo].[Trn_TraEspContratos] ([TipDoc], [Contrato], [IdCia])
GO
ALTER TABLE [dbo].[Trn_TraEspOtroSiContratos] CHECK CONSTRAINT [FK_Trn_TraEspOtroSiContratos_Trn_TraEspContratos]
GO
ALTER TABLE [dbo].[Trn_TraEspOtroSiContratos]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspOtroSiContratos_Trn_TraEspOtroSi] FOREIGN KEY([IdOtroSi])
REFERENCES [dbo].[Trn_TraEspOtroSi] ([IdOtroSi])
GO
ALTER TABLE [dbo].[Trn_TraEspOtroSiContratos] CHECK CONSTRAINT [FK_Trn_TraEspOtroSiContratos_Trn_TraEspOtroSi]
GO
--------
INSERT INTO Trn_TraEspOtroSi VALUES('12/07/2019',0,0,0,1,'12/07/2019',null,'SUPERVISOR', 1,null)
GO
ALTER TABLE [dbo].[Trn_TraEspContratoDet] ADD 
	IdOtroSi int NOT NULL DEFAULT (0),
	ItemAnuladoOS bit NOT NULL DEFAULT (0)
GO
ALTER TABLE [dbo].[Trn_TraEspContratoDet]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspContratoDet_Trn_TraEspOtroSi] FOREIGN KEY([IdOtroSi])
REFERENCES [dbo].[Trn_TraEspOtroSi] ([IdOtroSi])
GO
ALTER TABLE [dbo].[Trn_TraEspResDetalle] ADD 
	IdOtroSi int NOT NULL DEFAULT (0)
GO
ALTER TABLE [dbo].[Trn_TraEspResDetalle]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspResDetalle_Trn_TraEspOtroSi] FOREIGN KEY([IdOtroSi])
REFERENCES [dbo].[Trn_TraEspOtroSi] ([IdOtroSi])
GO
INSERT INTO dbo.Sys_Roles VALUES
('FRMCONTTES','PAO','Permitir realizar OtroSí a Contrato')
GO
ALTER TABLE  [dbo].[Trn_TraEspContratoAnexo]
ALTER COLUMN NomCliente VARCHAR(250)
GO
ALTER TABLE  [dbo].[Trn_TraEspResAnexo]
ALTER COLUMN NomCliente VARCHAR(250)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	John Jairo Roa Castañeda
-- Create date(dd/MM/yyyy): 29/07/2019
-- Description:	*********************
-- =============================================
  CREATE TRIGGER [dbo].[Tr_OtroSiCOE] ON [dbo].[Trn_TraEspOtroSiContratos]
  INSTEAD OF INSERT, UPDATE
  AS
  BEGIN
    SET NOCOUNT ON;
	ROLLBACK TRAN
  END
--AFTER UPDATE
--for INSERT, Update
--FOR ALTER
--ROLLBACK TRANSACTION
--SELECT 1
GO
ALTER TABLE [dbo].[Trn_TraEspOtroSiContratos] ENABLE TRIGGER [Tr_OtroSiCOE]
GO