BEGIN TRAN
	--Nuevos enviados por Janeth el 240222020
	IF NOT EXISTS (select IdResFis FROM TiposRespFis WHERE IdResFis = 'O-13')
		INSERT TiposRespFis VALUES (N'O-13', N'Gran Contribuyente', 0);
	GO
	IF NOT EXISTS (select IdResFis FROM TiposRespFis WHERE IdResFis = 'O-15')
		INSERT TiposRespFis VALUES (N'O-15', N'Autorretenedor', 0);
	GO
	IF NOT EXISTS (select IdResFis FROM TiposRespFis WHERE IdResFis = 'O-23')
		INSERT TiposRespFis VALUES (N'O-23', N'Agente de retención IVA', 0);
	GO
	IF NOT EXISTS (select IdResFis FROM TiposRespFis WHERE IdResFis = 'O-47')
		INSERT TiposRespFis VALUES (N'O-47', N'Régimen simple de tributación', 0)
	GO
	IF NOT EXISTS (select IdResFis FROM TiposRespFis WHERE IdResFis = 'ZZ')
		INSERT TiposRespFis VALUES (N'ZZ', N'No aplica', 0);
    GO

	--Actualizar los que tiene cod diferentes
	UPDATE TercRespFiscal set IdResFis= 'ZZ' WHERE  IdResFis NOT IN ('O-13', 'O-15', 'O-23', 'O-47', 'ZZ')

	GO
	--Eliminar registros
    DELETE FROM TiposRespFis WHERE  IdResFis NOT IN ('O-13', 'O-15', 'O-23', 'O-47', 'ZZ')


	--Eliminar reg duplicados
	DELETE FROM dbo.TercRespFiscal
		WHERE Id IN (
							SELECT X.Id
							FROM (
									SELECT  ROW_NUMBER() OVER(PARTITION BY NR.IdTercero, NR.IdResFis ORDER BY NR.IdResFis ASC) Position
											,NR.Id
									FROM dbo.TercRespFiscal AS NR
								 ) X
									WHERE X.Position >1
								)
COMMIT TRAN