USE [MovilBusiness5STD]
GO
/****** Object:  StoredProcedure [dbo].[sp_RepresentantesActualizarFromSAPBO-INC]    Script Date: 13/10/15 12:27:46 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_RepresentantesActualizarFromSAPBO-INC]
 AS
 DECLARE @Cantidad INT
 DECLARE @Representantes AS  TABLE  (
     [RepCodigo] VARCHAR(15)  NOT NULL ,
     [RepNombre] VARCHAR(25)  NULL ,
     [RepCargo] VARCHAR(25)  NULL ,
     [RepClasificacion] VARCHAR(25)  NULL ,
     [RepTelefono1] VARCHAR(20)  NULL ,
     [RepTelefono2] VARCHAR(20)  NULL ,
     [RepClave] VARCHAR(10)  NULL ,
     [RepIndicadorVenta] bit  NULL ,
     [RepInventarioVentas] bit  NULL ,
     [RepPedidos] bit  NULL ,
     [RepCobros] bit  NULL ,
     [RepDevoluciones] bit  NULL ,
     [RepEntrega] bit  NULL ,
     [RepDeposito] bit  NULL ,
     [RepInvestigacion] bit  NULL ,
     [RepM1] bit  NULL ,
     [RepM2] bit  NULL ,
     [RepM3] bit  NULL ,
     [RepM4] bit  NULL ,
     [RepM5] bit  NULL ,
     [ZonID] INT  NULL ,
     [RepIndicadorSupervisor] bit  NULL ,
     [RepSupervisor] VARCHAR(10)  NULL ,
     [RepDivision] INT  NULL ,
     [AlmID] INT  NULL ,
     [RepFechaUltimaActualizacion] datetime  NULL ,
     [RepClaveSync] VARCHAR(10)  NULL ,
     [RepIndicadorRutaVisitasFecha] bit  NULL ,
     [RepLicencia] uniqueidentifier  NOT NULL ,
     [RutID] INT  NULL ,  [EquID] INT  NULL ,
     [RepFechaActualizacion] datetime  NULL ,
     [UsuInicioSesion] VARCHAR(64)  NULL ,
     [rowguid] uniqueidentifier  NOT NULL ,
     [RepEstatus]  SMALLINT  NOT NULL ,
      PRIMARY KEY (   [RepCodigo]  )  )
 INSERT INTO @Representantes( [RepCodigo], [RepNombre], [RepCargo], [RepClasificacion], [RepTelefono1], [RepTelefono2], [RepClave], [RepIndicadorVenta], [RepInventarioVentas], [RepPedidos], [RepCobros], [RepDevoluciones], [RepEntrega], [RepDeposito], [RepInvestigacion], [RepM1], [RepM2], [RepM3], [RepM4], [RepM5], [ZonID], [RepIndicadorSupervisor], [RepSupervisor], [RepDivision], [AlmID], [RepFechaUltimaActualizacion], [RepClaveSync], [RepIndicadorRutaVisitasFecha], [RepLicencia], [RutID], [EquID], [RepFechaActualizacion], [UsuInicioSesion], [rowguid], [RepEstatus])
 SELECT 'R000'+ CAST(slpcode AS VARCHAR)  AS [RepCodigo],
 SUBSTRING(slpName,1,25) AS [RepNombre],
 'Vendedor' AS [RepCargo],
 0 AS [RepClasificacion],
 '' AS [RepTelefono1],
 '' AS [RepTelefono2],
 'R000'+ CAST(slpcode AS VARCHAR) AS [RepClave],
 0 AS [RepIndicadorVenta],
 0 AS [RepInventarioVentas],
 0 AS [RepPedidos],
 0 AS [RepCobros],
 0 AS [RepDevoluciones],
 0 AS [RepEntrega],
 0 AS [RepDeposito],
 0 AS [RepInvestigacion],
 0 AS [RepM1],
 0 AS [RepM2],
 0 AS [RepM3],
 0 AS [RepM4],
 CASE Active
 WHEN 'Y' THEN  1
 ELSE 0
 END AS [RepM5],
 0 AS [ZonID],
 0 AS [RepIndicadorSupervisor],
 NULL AS [RepSupervisor],
 0 AS [RepDivision],
 1 AS [AlmID],
 getdate() AS [RepFechaUltimaActualizacion],
 0 AS [RepClaveSync],
 0 AS [RepIndicadorRutaVisitasFecha],
 NEWID() AS [RepLicencia],
 0  AS [RutID],
 0 AS [EquID],
 getdate() AS [RepFechaActualizacion],
 'mildiaz' AS [UsuInicioSesion],
 newid() AS [rowguid],
 CASE Active
 WHEN 'Y' THEN  1
 ELSE 0
 END AS [RepEstatus]
FROM B1_INCARNA.dbo.oslp
WHERE slpcode > 0

 SELECT @Cantidad = COUNT(*) FROM  @Representantes

 IF @Cantidad = 0
 RETURN

UPDATE @Representantes
SET
 rowguid = B.rowguid
FROM @Representantes A,Representantes B
WHERE
 A.RepCodigo = B.RepCodigo  AND
 (
A.rowguid <>  B.rowguid  )

INSERT INTO Representantes ( RepCodigo, RepNombre, RepCargo, RepClasificacion, RepTelefono1, RepTelefono2, RepClave, RepIndicadorVenta, RepInventarioVentas, RepPedidos, RepCobros, RepDevoluciones, RepEntrega, RepDeposito, RepInvestigacion, RepM1, RepM2, RepM3, RepM4, RepM5, ZonID, RepIndicadorSupervisor, RepSupervisor, RepDivision, AlmID, RepFechaUltimaActualizacion, RepClaveSync, RepIndicadorRutaVisitasFecha, RepLicencia, RutID, EquID, RepFechaActualizacion, UsuInicioSesion, rowguid, [RepEstatus])
SELECT RepCodigo, RepNombre, RepCargo, RepClasificacion, RepTelefono1, RepTelefono2, RepClave, RepIndicadorVenta, RepInventarioVentas, RepPedidos, RepCobros, RepDevoluciones, RepEntrega, RepDeposito, RepInvestigacion, RepM1, RepM2, RepM3, RepM4, RepM5, ZonID, RepIndicadorSupervisor, RepSupervisor, RepDivision, AlmID, RepFechaUltimaActualizacion, RepClaveSync, RepIndicadorRutaVisitasFecha, RepLicencia, RutID, EquID, RepFechaActualizacion, UsuInicioSesion, rowguid, [RepEstatus]
FROM @Representantes A
WHERE A.rowguid NOT IN  (
 SELECT  Representantes.rowguid FROM Representantes )

UPDATE Representantes
SET
 RepCodigo = B.RepCodigo,
 RepNombre = B.RepNombre,
 RepCargo = B.RepCargo,
 RepClasificacion = B.RepClasificacion,
 RepTelefono1 = B.RepTelefono1,
 RepTelefono2 = B.RepTelefono2,
 RepClave = B.RepClave,
 RepIndicadorVenta = B.RepIndicadorVenta,
 RepInventarioVentas = B.RepInventarioVentas,
 RepPedidos = B.RepPedidos,
 RepCobros = B.RepCobros,
 RepDevoluciones = B.RepDevoluciones,
 RepEntrega = B.RepEntrega,
 RepDeposito = B.RepDeposito,
 RepInvestigacion = B.RepInvestigacion,
 RepM1 = B.RepM1,
 RepM2 = B.RepM2,
 RepM3 = B.RepM3,
 RepM4 = B.RepM4,
 RepM5 = B.RepM5,
 ZonID = B.ZonID,
 RepIndicadorSupervisor = B.RepIndicadorSupervisor,
 RepSupervisor = B.RepSupervisor,
 RepDivision = B.RepDivision,
 AlmID = B.AlmID,
 RepClaveSync = B.RepClaveSync,
 RepIndicadorRutaVisitasFecha = B.RepIndicadorRutaVisitasFecha,
 RepLicencia = B.RepLicencia,
 RutID = B.RutID,
 EquID = B.EquID,
 RepFechaActualizacion = B.RepFechaActualizacion,
 UsuInicioSesion = B.UsuInicioSesion,
 Representantes.[RepEstatus] = B.[RepEstatus]
FROM Representantes A,@Representantes B
WHERE  A.ROWGUID = B.ROWGUID AND
 (
A.RepCodigo <>  B.RepCodigo OR
A.RepNombre <>  B.RepNombre OR
A.RepCargo <>  B.RepCargo OR
A.RepClasificacion <>  B.RepClasificacion OR
A.RepTelefono1 <>  B.RepTelefono1 OR
A.RepTelefono2 <>  B.RepTelefono2 OR
A.RepClave <>  B.RepClave OR
A.RepIndicadorVenta <>  B.RepIndicadorVenta OR
A.RepInventarioVentas <>  B.RepInventarioVentas OR
A.RepPedidos <>  B.RepPedidos OR
A.RepCobros <>  B.RepCobros OR
A.RepDevoluciones <>  B.RepDevoluciones OR
A.RepEntrega <>  B.RepEntrega OR
A.RepDeposito <>  B.RepDeposito OR
A.RepInvestigacion <>  B.RepInvestigacion OR
A.RepM1 <>  B.RepM1 OR
A.RepM2 <>  B.RepM2 OR
A.RepM3 <>  B.RepM3 OR
A.RepM4 <>  B.RepM4 OR
A.RepM5 <>  B.RepM5 OR
A.ZonID <>  B.ZonID OR
A.RepIndicadorSupervisor <>  B.RepIndicadorSupervisor OR
A.RepSupervisor <>  B.RepSupervisor OR
A.RepDivision <>  B.RepDivision OR
A.AlmID <>  B.AlmID OR
A.RepClaveSync <>  B.RepClaveSync OR
A.RepIndicadorRutaVisitasFecha <>  B.RepIndicadorRutaVisitasFecha OR
A.RutID <>  B.RutID OR
A.EquID <>  B.EquID OR
A.UsuInicioSesion <>  B.UsuInicioSesion OR
A.[RepEstatus] <> B.[RepEstatus])

 DELETE FROM  Representantes
 WHERE rowguid NOT IN (SELECT rowguid FROM @Representantes) AND RepCodigo != 'test'