GO
if not exists(select 1 from syscolumns where id = object_id('CG_FND_VERBA_RC_CRED') and name = 'CD_FND_VERBA_RECB')
begin
	ALTER TABLE dbo.CG_FND_VERBA_RC_CRED ADD
		CD_FND_VERBA_RECB int NOT NULL CONSTRAINT DF_CG_FND_VERBA_RC_CRED_CD_FND_VERBA_RECB DEFAULT 0
end

--Corrigindo lancamentos
update  
	CG_FND_VERBA_RC_CRED
set 
    CG_FND_VERBA_RC_CRED.CD_FND_VERBA_RECB = CG_FND_VERBA_RECB.CD_FND_VERBA_RECB
FROM         
	CG_FND_VERBA_RC_CRED INNER JOIN CG_FND_VERBA_RECB ON 
	CG_FND_VERBA_RC_CRED.CD_EMP = CG_FND_VERBA_RECB.CD_EMP AND 
    CG_FND_VERBA_RC_CRED.CD_FND_VERBA = CG_FND_VERBA_RECB.CD_FND_VERBA 
    INNER JOIN RC_CRED ON 
    CG_FND_VERBA_RC_CRED.CD_EMP = RC_CRED.CD_EMP AND 
    CG_FND_VERBA_RC_CRED.CD_FILIAL = RC_CRED.CD_FILIAL AND 
    CG_FND_VERBA_RC_CRED.CD_RC_DEB = RC_CRED.CD_RC_DEB AND 
    CG_FND_VERBA_RC_CRED.CD_DP_LANC = RC_CRED.CD_DP_LANC
WHERE
 CG_FND_VERBA_RECB.VLR_RECB = RC_CRED.VL_PGTO        
 AND RC_CRED.DT_PAG = CG_FND_VERBA_RECB.DT_RECEB
 AND  (CG_FND_VERBA_RC_CRED.CD_FND_VERBA_RECB IS NULL OR CG_FND_VERBA_RC_CRED.CD_FND_VERBA_RECB = 0)


delete
    CG_FND_VERBA_RC_CRED
FROM         
	CG_FND_VERBA_RC_CRED INNER JOIN CG_FND_VERBA_RECB ON 
	CG_FND_VERBA_RC_CRED.CD_EMP = CG_FND_VERBA_RECB.CD_EMP AND 
    CG_FND_VERBA_RC_CRED.CD_FND_VERBA = CG_FND_VERBA_RECB.CD_FND_VERBA 
    INNER JOIN RC_CRED ON 
    CG_FND_VERBA_RC_CRED.CD_EMP = RC_CRED.CD_EMP AND 
    CG_FND_VERBA_RC_CRED.CD_FILIAL = RC_CRED.CD_FILIAL AND 
    CG_FND_VERBA_RC_CRED.CD_RC_DEB = RC_CRED.CD_RC_DEB AND 
    CG_FND_VERBA_RC_CRED.CD_DP_LANC = RC_CRED.CD_DP_LANC
WHERE
  (CG_FND_VERBA_RC_CRED.CD_FND_VERBA_RECB IS NULL OR CG_FND_VERBA_RC_CRED.CD_FND_VERBA_RECB = 0)

--Excluindo PK para recriar
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CG_FND_VERBA_RC_CRED_RC_CRED]') AND parent_object_id = OBJECT_ID(N'[dbo].[CG_FND_VERBA_RC_CRED]'))
ALTER TABLE [dbo].[CG_FND_VERBA_RC_CRED] DROP CONSTRAINT [FK_CG_FND_VERBA_RC_CRED_RC_CRED]
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CG_FND_VERBA_RC_CRED_CG_FND_VERBA]') AND parent_object_id = OBJECT_ID(N'[dbo].[CG_FND_VERBA_RC_CRED]'))
ALTER TABLE [dbo].[CG_FND_VERBA_RC_CRED] DROP CONSTRAINT [FK_CG_FND_VERBA_RC_CRED_CG_FND_VERBA]

/****** Object:  Index [PK_CG_FND_VERBA_RC_CRED]    Script Date: 04/13/2013 14:34:54 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CG_FND_VERBA_RC_CRED]') AND name = N'PK_CG_FND_VERBA_RC_CRED')
ALTER TABLE [dbo].[CG_FND_VERBA_RC_CRED] DROP CONSTRAINT [PK_CG_FND_VERBA_RC_CRED]

--Criando PK 
/****** Object:  Index [PK_CG_FND_VERBA_RC_CRED]    Script Date: 04/13/2013 14:34:57 ******/
ALTER TABLE [dbo].[CG_FND_VERBA_RC_CRED] ADD  CONSTRAINT [PK_CG_FND_VERBA_RC_CRED] PRIMARY KEY CLUSTERED 
(
	[CD_EMP] ASC,
	[CD_FILIAL] ASC,
	[CD_RC_DEB] ASC,
	[CD_DP_LANC] ASC,
	[CD_FND_VERBA] ASC,
	[CD_FND_VERBA_RECB] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

--Criando FK
GO
GO
ALTER TABLE [dbo].[CG_FND_VERBA_RC_CRED]  WITH CHECK ADD  CONSTRAINT [FK_CG_FND_VERBA_RC_CRED_RC_CRED] FOREIGN KEY([CD_EMP], [CD_FILIAL], [CD_RC_DEB], [CD_DP_LANC])
REFERENCES [dbo].[RC_CRED] ([CD_EMP], [CD_FILIAL], [CD_RC_DEB], [CD_DP_LANC])
GO

ALTER TABLE [dbo].[CG_FND_VERBA_RC_CRED] CHECK CONSTRAINT [FK_CG_FND_VERBA_RC_CRED_RC_CRED]
GO
ALTER TABLE dbo.CG_FND_VERBA_RC_CRED ADD CONSTRAINT
	FK_CG_FND_VERBA_RC_CRED_CG_FND_VERBA_RECB FOREIGN KEY
	(
	CD_FND_VERBA_RECB,
	CD_FND_VERBA,
	CD_EMP
	) REFERENCES dbo.CG_FND_VERBA_RECB
	(
	CD_FND_VERBA_RECB,
	CD_FND_VERBA,
	CD_EMP
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 