BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_ClientProgram
	(
	Id int NOT NULL IDENTITY (1, 1),
	ClientId int NOT NULL,
	Fraction nvarchar(8) NOT NULL,
	[Rule] nvarchar(20) NULL,
	Amount money NULL,
	Charter bit NULL,
	Prosec nvarchar(50) NULL,
	ExpirationDate datetime NULL,
	Enable bit NOT NULL,
	CreatedBy int NOT NULL,
	CreatedOn datetime NOT NULL,
	UpdatedBy int NULL,
	UpdatedOn datetime NULL
	)  ON [PRIMARY]
GO

ALTER TABLE dbo.Tmp_ClientProgram SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_ClientProgram ON
GO
IF EXISTS(SELECT * FROM dbo.ClientProgram)
	 EXEC('INSERT INTO dbo.Tmp_ClientProgram (Id, ClientId, Fraction, [Rule], Amount, Charter, ExpirationDate, Enable, CreatedBy, CreatedOn, UpdatedBy, UpdatedOn)
		SELECT Id, ClientId, Fraction, [Rule], Amount, Charter, ExpirationDate, Enable, CreatedBy, CreatedOn, UpdatedBy, UpdatedOn FROM dbo.ClientProgram WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_ClientProgram OFF
GO
DROP TABLE dbo.ClientProgram
GO
EXECUTE sp_rename N'dbo.Tmp_ClientProgram', N'ClientProgram', 'OBJECT'
GO
COMMIT
GO

ALTER PROCEDURE [dbo].[GetFractionInfo]
	(
	@clientId int,
	@fraction nvarchar(8)
	)
AS
	SET NOCOUNT ON
	SELECT        f.Fraction, f.Sensible, COALESCE(cp.[Rule], '') AS [Rule], COALESCE(cp.ExpirationDate, GETDATE()) AS ExpirationDate,
	COALESCE(cp.Charter, '') AS Charte, COALESCE(cp.Amount, 0) AS Amount, COALESCE(cp.Prosec, '') AS Prosec
FROM            Fraction AS f LEFT OUTER JOIN
                         ClientProgram AS cp
              ON f.Fraction = cp.Fraction AND cp.ClientId = @clientId
WHERE        f.Fraction = @fraction
	RETURN

GO


ALTER PROCEDURE [dbo].[InsClientProgram]
	(
		@ClientId int,
		@Fraction varchar(8),
		@Rule varchar(20),
		@Charter bit,
		@Prosec nvarchar(50),
		@Amount money,
		@ExpirationDate datetime,
		@Enable bit,
		@CreatedBy int,
		@CreatedOn datetime
	)
AS

	INSERT INTO ClientProgram(ClientId, Fraction, [Rule], Charter, Prosec, Amount, ExpirationDate, Enable, CreatedBy, CreatedOn)
	 VALUES(@ClientId, @Fraction, @Rule, @Charter, @Prosec, @Amount, @ExpirationDate, @Enable, @CreatedBy, @CreatedOn)

	SELECT * FROM ClientProgram WHERE Id = @@Identity

	RETURN

GO


ALTER PROCEDURE [dbo].[UpdClientProgram]
	(
		@Id int,
		@Fraction varchar(8),
		@Rule varchar(20),
		@Charter bit,
		@Prosec nvarchar(50),
		@Amount money,
		@ExpirationDate datetime,
		@Enable bit,
		@UpdatedBy int,
		@UpdatedOn datetime
	)
AS

	UPDATE ClientProgram SET Fraction = @Fraction, [Rule] = @Rule, Charter = @Charter, Prosec = @Prosec,
		Amount = @Amount, ExpirationDate = @ExpirationDate, Enable = @Enable,
		UpdatedBy = @UpdatedBy, UpdatedOn = @UpdatedOn
	 WHERE Id = @Id

	RETURN

GO