
ALTER PROCEDURE [dbo].[sp_remTrabajadores_Ins]
(@periodo AS                INT, 
 @mes AS                    INT, 
 @ficha AS                  INT, 
 @activo AS                 INT, 
 @rut AS                    INT, 
 @dv AS                     CHAR(1), 
 @extranjero AS             INT, 
 @nombres AS                VARCHAR(50), 
 @apePaterno AS             VARCHAR(50), 
 @apeMaterno AS             VARCHAR(50), 
 @fechaNac AS               SMALLDATETIME, 
 @sexo AS                   INT, 
 @cargo AS                  INT, 
 @profesion AS              INT, 
 @direccion AS              VARCHAR(100), 
 @comuna AS                 INT, 
 @ciudad AS                 VARCHAR(100), 
 @codSucursal AS			INT,
 @nomSucursal AS			VARCHAR(150),
 @dirSucursal AS            VARCHAR(100), 
 @comSucursal AS            INT, 
 @ciuSucursal AS            VARCHAR(100), 
 @pais AS                   VARCHAR(20), 
 @telefonoPart AS           VARCHAR(10), 
 @telefonoEmp AS            VARCHAR(10), 
 @celularPart AS            VARCHAR(13), 
 @celularEmp AS             VARCHAR(13), 
 @correoPart AS             VARCHAR(50), 
 @correoEmp AS              VARCHAR(50), 
 @estadoCivil AS            INT, 
 @seccion AS                INT, 
 @departamento AS           INT, 
 @unidadNegocios AS         INT, 
 @fechaIngreso AS           SMALLDATETIME, 
 @fechaContrato AS          SMALLDATETIME, 
 @tipoContrato AS           INT, 
 @fechaTermino AS           SMALLDATETIME, 
 @fechaFiniquito AS         SMALLDATETIME, 
 @tipoSueldo AS             INT, 
 @sueldoBase AS             FLOAT, 
 @tipoJornada AS            INT, 
 @cantidadHoras AS          FLOAT, 
 @tipoMovilizacion AS       INT, 
 @montoMovilizacion AS      FLOAT, 
 @tipoColacion AS           INT, 
 @montoColacion AS          FLOAT, 
 @rentaPromedio AS          FLOAT, 
 @formaPago AS              INT, 
 @numCuenta AS              VARCHAR(50), 
 @banco AS                  INT, 
 @tipoTrabajador AS         INT, 
 @regimenPrevisional AS     INT, 
 @idPrevision AS            INT, 
 @porcPrevision AS          FLOAT, 
 @tipoAhorroAFP AS          INT, 
 @montoAhorroAFP AS         DECIMAL(18, 4), 
 @segcesantia AS            INT, 
 @idDesahucio AS            INT, 
 @porcDesahucio AS          FLOAT, 
 @regimenSalud AS           INT, 
 @instSalud AS              INT, 
 @monedaSalud AS            INT, 
 @pactadoSalud AS           FLOAT, 
 @bienestar AS              INT, 
 @modoBienestar AS          INT, 
 @montoBienestar AS         FLOAT, 
 @idSindicato AS            INT, 
 @modoSindicato AS          INT, 
 @montoSindicato AS         FLOAT, 
 @servMedico AS             INT, 
 @smedicoNormal AS          INT, 
 @smedicoMaternal AS        INT, 
 @smedicoEspecial AS        INT, 
 @idApvi1 AS                INT, 
 @contratoApvi1 AS          VARCHAR(200), 
 @formapagoApvi1 AS         INT, 
 @modoApvi1 AS              INT, 
 @montoApvi1 AS             FLOAT, 
 @idApvi2 AS                INT, 
 @contratoApvi2 AS          VARCHAR(200), 
 @formapagoApvi2 AS         INT, 
 @modoApvi2 AS              INT, 
 @montoApvi2 AS             FLOAT, 
 @idApvi3 AS                INT, 
 @contratoApvi3 AS          VARCHAR(200), 
 @formapagoApvi3 AS         INT, 
 @modoApvi3 AS              INT, 
 @montoApvi3 AS             FLOAT, 
 @idApvc1 AS                INT, 
 @contratoApvc1 AS          VARCHAR(200), 
 @formapagoApvc1 AS         INT, 
 @modoApvc1 AS              INT, 
 @montoApvc1 AS             FLOAT, 
 @saldoperiodoanterior AS   INT, 
 @areconocidos AS           INT, 
 @moneda AS                 INT, 
 @montoretencionJudicial AS DECIMAL(18, 4), 
 @imponiblecontrato AS      DECIMAL(18, 4), 
 @coment1 AS                VARCHAR(200), 
 @coment2 AS                VARCHAR(200), 
 @coment3 AS                VARCHAR(200), 
 @coment4 AS                VARCHAR(200), 
 @CargaNormal AS            INT, 
 @CargaInvalida AS          INT, 
 @CargaMaternal AS          INT, 
 @TramoAsignacion AS        INT, 
 @PrevisionCuenta2 AS       INT, 
 @loging AS                 VARCHAR(15), 
 @fecing AS                 DATETIME, 
 @logmod AS                 VARCHAR(15), 
 @fecmod AS                 DATETIME, 
 @cambios AS                INTEGER        = 0, 
 @tomadoperiodoanterior AS  INTEGER        = 0, 
 @fechainicioperiodo AS     DATETIME, 
 @fechainivacprog AS        DATETIME, 
 @InstCesantia AS           INTEGER        = 0, 
 @diasprogresivostomados AS DECIMAL(18, 4)  = 0.00, 
 @caja AS                   INTEGER        = 0,
 @nogratificacion AS        INT,
 @turno AS					INT,
 @tipoTurno	AS				INT
)
AS
     IF EXISTS
     (
         SELECT 1
         FROM remTrabajadores
         WHERE periodo = @periodo
               AND mes = @mes
               AND ficha = @ficha
     )
         BEGIN
             UPDATE remTrabajadores
               SET 
                   periodo = @periodo, 
                   mes = @mes, 
                   ficha = @ficha, 
                   activo = @activo, 
                   rut = @rut, 
                   dv = @dv, 
                   extranjero = @extranjero, 
                   nombres = @nombres, 
                   apePaterno = @apePaterno, 
                   apeMaterno = @apeMaterno, 
                   fechaNac = @fechaNac, 
                   sexo = @sexo, 
                   cargo = @cargo, 
                   profesion = @profesion, 
                   direccion = @direccion, 
                   comuna = @comuna, 
                   ciudad = @ciudad,
				   codSucursal = @codSucursal,
				   nomSucursal = @nomSucursal,
                   dirSucursal = @dirSucursal, 
                   comSucursal = @comSucursal, 
                   ciuSucursal = @ciuSucursal, 
                   pais = @pais, 
                   telefonoPart = @telefonoPart, 
                   telefonoEmp = @telefonoEmp, 
                   celularPart = @celularPart, 
                   celularEmp = @celularEmp, 
                   correoPart = @correoPart, 
                   correoEmp = @correoEmp, 
                   estadoCivil = @estadoCivil, 
                   seccion = @seccion, 
                   departamento = @departamento, 
                   unidadNegocios = @unidadNegocios, 
                   fechaIngreso = @fechaIngreso, 
                   fechaContrato = @fechaContrato, 
                   tipoContrato = @tipoContrato, 
                   fechaTermino = @fechaTermino, 
                   fechaFiniquito = @fechaFiniquito, 
                   tipoSueldo = @tipoSueldo, 
                   sueldoBase = @sueldoBase, 
                   tipoJornada = @tipoJornada, 
                   cantidadHoras = @cantidadHoras, 
                   tipoMovilizacion = @tipoMovilizacion, 
                   montoMovilizacion = @montoMovilizacion, 
                   tipoColacion = @tipoColacion, 
                   montoColacion = @montoColacion, 
                   rentaPromedio = @rentaPromedio, 
                   formaPago = @formaPago, 
                   numCuenta = @numCuenta, 
                   banco = @banco, 
                   tipoTrabajador = @tipoTrabajador, 
                   regimenPrevisional = @regimenPrevisional, 
                   idPrevision = @idPrevision, 
                   porcPrevision = @porcPrevision, 
                   tipoAhorroAFP = @tipoAhorroAFP, 
                   montoAhorroAFP = @montoAhorroAFP, 
                   segcesantia = @segcesantia, 
                   idDesahucio = @idDesahucio, 
                   porcDesahucio = @porcDesahucio, 
                   regimenSalud = @regimenSalud, 
                   instSalud = @instSalud, 
                   monedaSalud = @monedaSalud, 
                   pactadoSalud = @pactadoSalud, 
                   bienestar = @bienestar, 
                   modoBienestar = @modoBienestar, 
                   montoBienestar = @montoBienestar, 
                   idSindicato = @idSindicato, 
                   modoSindicato = @modoSindicato, 
                   montoSindicato = @montoSindicato, 
                   servMedico = @servMedico, 
                   smedicoNormal = @smedicoNormal, 
                   smedicoMaternal = @smedicoMaternal, 
                   smedicoEspecial = @smedicoEspecial, 
                   idApvi1 = @idApvi1, 
                   contratoApvi1 = @contratoApvi1, 
                   formapagoApvi1 = @formapagoApvi1, 
                   modoApvi1 = @modoApvi1, 
                   montoApvi1 = @montoApvi1, 
                   idApvi2 = @idApvi2, 
                   contratoApvi2 = @contratoApvi2, 
                   formapagoApvi2 = @formapagoApvi2, 
                   modoApvi2 = @modoApvi2, 
                   montoApvi2 = @montoApvi2, 
                   idApvi3 = @idApvi3, 
                   contratoApvi3 = @contratoApvi3, 
                   formapagoApvi3 = @formapagoApvi3, 
                   modoApvi3 = @modoApvi3, 
                   montoApvi3 = @montoApvi3, 
                   idApvc1 = @idApvc1, 
                   contratoApvc1 = @contratoApvc1, 
                   formapagoApvc1 = @formapagoApvc1, 
                   modoApvc1 = @modoApvc1, 
                   montoApvc1 = @montoApvc1, 
                   saldoperiodoanterior = @saldoperiodoanterior, 
                   areconocidos = @areconocidos, 
                   moneda = @moneda, 
                   montoretencionJudicial = @montoretencionJudicial, 
                   imponiblecontrato = @imponiblecontrato, 
                   coment1 = @coment1, 
                   coment2 = @coment2, 
                   coment3 = @coment3, 
                   coment4 = @coment4, 
                   CargaNormal = @CargaNormal, 
                   CargaInvalida = @CargaInvalida, 
                   CargaMaternal = @CargaMaternal, 
                   tramoasigfamiliar = @TramoAsignacion, 
                   PrevisionCuenta2 = @PrevisionCuenta2, 
                   logmod = @logmod, 
                   fecmod = GETDATE(), 
                   fecmod2 = CASE
                                 WHEN @cambios = 1
                                 THEN GETDATE()
                             END, 
                   tomadoperiodoanterior = @tomadoperiodoanterior, 
                   fechainicioperiodo = @fechainicioperiodo, 
                   fechainivacprog = @fechainivacprog, 
                   InstCesantia = @InstCesantia, 
                   diasprogresivostomados = @diasprogresivostomados, 
                   caja = @caja,
				   nogratificacion = @nogratificacion,
				   turno = @turno,
				   tipoTurno = @tipoTurno
             WHERE periodo = @periodo
                   AND mes = @mes
                   AND ficha = @ficha;
     END;
         ELSE
         BEGIN
             INSERT INTO remTrabajadores
             ([periodo], 
              [mes], 
              [ficha], 
              [activo], 
              [rut], 
              [dv], 
              [extranjero], 
              [nombres], 
              [apePaterno], 
              [apeMaterno], 
              [fechaNac], 
              [sexo], 
              [cargo], 
              [profesion], 
              [direccion], 
              [comuna], 
              [ciudad],
			  [codSucursal],
			  [nomSucursal],
			  [dirSucursal], 
              [comSucursal], 
              [ciuSucursal], 
              [pais], 
              [telefonoPart], 
              [telefonoEmp], 
              [celularPart], 
              [celularEmp], 
              [correoPart], 
              [correoEmp], 
              [estadoCivil], 
              [seccion], 
              [departamento], 
              [unidadNegocios], 
              [fechaIngreso], 
              [fechaContrato], 
              [tipoContrato], 
              [fechaTermino], 
              [fechaFiniquito], 
              [tipoSueldo], 
              [sueldoBase], 
              [tipoJornada], 
              [cantidadHoras], 
              [tipoMovilizacion], 
              [montoMovilizacion], 
              [tipoColacion], 
              [montoColacion], 
              [rentaPromedio], 
              [formaPago], 
              [numCuenta], 
              [banco], 
              [tipoTrabajador], 
              [regimenPrevisional], 
              [idPrevision], 
              [porcPrevision], 
              [tipoAhorroAFP], 
              [montoAhorroAFP], 
              [segcesantia], 
              [idDesahucio], 
              [porcDesahucio], 
              [regimenSalud], 
              [instSalud], 
              [monedaSalud], 
              [pactadoSalud], 
              [bienestar], 
              [modoBienestar], 
              [montoBienestar], 
              [idSindicato], 
              [modoSindicato], 
              [montoSindicato], 
              [servMedico], 
              [smedicoNormal], 
              [smedicoMaternal], 
              [smedicoEspecial], 
              [idApvi1], 
              [contratoApvi1], 
              [formapagoApvi1], 
              [modoApvi1], 
              [montoApvi1], 
              [idApvi2], 
              [contratoApvi2], 
              [formapagoApvi2], 
              [modoApvi2], 
              [montoApvi2], 
              [idApvi3], 
              [contratoApvi3], 
              [formapagoApvi3], 
              [modoApvi3], 
              [montoApvi3], 
              [idApvc1], 
              [contratoApvc1], 
              [formapagoApvc1], 
              [modoApvc1], 
              [montoApvc1], 
              [saldoperiodoanterior], 
              [areconocidos], 
              [moneda], 
              [montoretencionJudicial], 
              [imponiblecontrato], 
              coment1, 
              coment2, 
              coment3, 
              coment4, 
              CargaNormal, 
              CargaInvalida, 
              CargaMaternal, 
              tramoasigfamiliar, 
              PrevisionCuenta2, 
              [loging], 
              [fecing], 
              [logmod], 
              [fecmod], 
              tomadoperiodoanterior, 
              fechainicioperiodo, 
              fechainivacprog, 
              InstCesantia, 
              diasprogresivostomados, 
              [caja],
			  nogratificacion,
			  [turno],
			  [tipoTurno]
             )
             VALUES
             (@periodo, 
              @mes, 
              @ficha, 
              @activo, 
              @rut, 
              @dv, 
              @extranjero, 
              @nombres, 
              @apePaterno, 
              @apeMaterno, 
              @fechaNac, 
              @sexo, 
              @cargo, 
              @profesion, 
              @direccion, 
              @comuna, 
              @ciudad,
			  @codSucursal,
			  @nomSucursal,
              @dirSucursal, 
              @comSucursal, 
              @ciuSucursal,
              @pais, 
              @telefonoPart, 
              @telefonoEmp, 
              @celularPart, 
              @celularEmp, 
              @correoPart, 
              @correoEmp, 
              @estadoCivil, 
              @seccion, 
              @departamento, 
              @unidadNegocios, 
              @fechaIngreso, 
              @fechaContrato, 
              @tipoContrato, 
              @fechaTermino, 
              @fechaFiniquito, 
              @tipoSueldo, 
              @sueldoBase, 
              @tipoJornada, 
              @cantidadHoras, 
              @tipoMovilizacion, 
              @montoMovilizacion, 
              @tipoColacion, 
              @montoColacion, 
              @rentaPromedio, 
              @formaPago, 
              @numCuenta, 
              @banco, 
              @tipoTrabajador, 
              @regimenPrevisional, 
              @idPrevision, 
              @porcPrevision, 
              @tipoAhorroAFP, 
              @montoAhorroAFP, 
              @segcesantia, 
              @idDesahucio, 
              @porcDesahucio, 
              @regimenSalud, 
              @instSalud, 
              @monedaSalud, 
              @pactadoSalud, 
              @bienestar, 
              @modoBienestar, 
              @montoBienestar, 
              @idSindicato, 
              @modoSindicato, 
              @montoSindicato, 
              @servMedico, 
              @smedicoNormal, 
              @smedicoMaternal, 
              @smedicoEspecial, 
              @idApvi1, 
              @contratoApvi1, 
              @formapagoApvi1, 
              @modoApvi1, 
              @montoApvi1, 
              @idApvi2, 
              @contratoApvi2, 
              @formapagoApvi2, 
              @modoApvi2, 
              @montoApvi2, 
              @idApvi3, 
              @contratoApvi3, 
              @formapagoApvi3, 
              @modoApvi3, 
              @montoApvi3, 
              @idApvc1, 
              @contratoApvc1, 
              @formapagoApvc1, 
              @modoApvc1, 
              @montoApvc1, 
              @saldoperiodoanterior, 
              @areconocidos, 
              @moneda, 
              @montoretencionJudicial, 
              @imponiblecontrato, 
              @coment1, 
              @coment2, 
              @coment3, 
              @coment4, 
              @CargaNormal, 
              @CargaInvalida, 
              @CargaMaternal, 
              @TramoAsignacion, 
              @PrevisionCuenta2, 
              @loging, 
              GETDATE(), 
              @logmod, 
              GETDATE(), 
              @tomadoperiodoanterior, 
              @fechainicioperiodo, 
              @fechainivacprog, 
              @InstCesantia, 
              @diasprogresivostomados, 
              @caja,
			  @nogratificacion,
			  @turno,
			  @tipoTurno
             );
     END;