--Hash:8B5676FFE777049105E53FA6D99FF819
SET XACT_ABORT ON
BEGIN TRANSACTION;

GO

SET DATEFORMAT mdy;

--Validar que no existan scripts que no se hayan ejecutado
--//
IF EXISTS
(
	SELECT a.NumVersion + 1 AS HuecoInicio
	FROM ZiurVersiones a
	LEFT JOIN ZiurVersiones b ON a.NumVersion + 1 = b.NumVersion
	WHERE a.NumVersion>830 AND b.NumVersion IS NULL
	AND a.NumVersion + 1 < 908
)
RAISERROR('Error : hay un script que no se ejecuto , debe ejecutarlo manualmente.', 20, -1) with log
--//

--Colocar contenido de script diferencial aqui (Ziur.Database.publish.sql)

/*
 Plantilla de script anterior a la implementación							
--------------------------------------------------------------------------------------
 Este archivo contiene instrucciones de SQL que se ejecutarán antes del script de compilación	
 Use la sintaxis de SQLCMD para incluir un archivo en el script anterior a la implementación			
 Ejemplo:      :r .\miArchivo.sql								
 Use la sintaxis de SQLCMD para hacer referencia a una variable en el script anterior a la implementación		
 Ejemplo:      :setvar TableName miTabla							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/
GO

GO
PRINT N'Altering Procedure [dbo].[GetPagoSeguridadSocial]...';


GO
ALTER PROCEDURE [dbo].[GetPagoSeguridadSocial]

@IdEmpresa INT,
@FechaIni NVARCHAR(10) = '',
@FechaFin NVARCHAR(10) = '',
@IdCentroCosto INT = NULL

AS
BEGIN

DECLARE
@FechaIniT DATETIME,
@FechaFinT DATETIME,
@salarioMin NUMERIC(28,6)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON;	

SET @FechaIniT = CAST(CONVERT(DATETIME, @FechaIni, 103) + ' 00:00:00' AS DATETIME)
SET @FechaFinT = CAST(CONVERT(DATETIME, @FechaFin, 103) + ' 23:59:59' AS DATETIME)

-- Salario Mínimo
SET @salarioMin=(SELECT top(1) Valor FROM dbo.Nomina_Valores WHERE IdTipo=1 AND EsActivo=1)

;WITH tmpIBC(IdEncab, IdContrato, IdCliente, IdCentroCosto, 
IBC_SALUD_EMPRESA, 
IBC_SALUD_EMPLEADO, 
NUM_DIAS_SALUD_EMPRESA, NUM_DIAS_SALUD_EMPLEADO, 
VALOR_SALUD_EMPRESA, 
VALOR_SALUD_EMPLEADO, 
IBC_PENSION, 
NUM_DIAS_PENSION, 
VALOR_PENSION_EMPRESA, 
VALOR_PENSION_EMPLEADO, 
IBC_RIESGOS, 
NUM_DIAS_RIESGOS, 
VALOR_RIESGOS, 
IBC_CCF, 
NUM_DIAS_CCF, 
VALOR_CCF, APV_AFIL, AVP_APORT,
AVP,
INTEGRAL, 
APORT_FONDO_SOLID,IdItem,IdItem1,
FechaIni,FechaFin,Cantidad
)
AS
( 
	SELECT  nomMov1.IdEncab, nomMov1.IdContrato, nomMov1.IdCliente, nomMov1.IdCentroCosto,
	CASE WHEN nomMov.iditem = 201 THEN nomMov1.Cantidad * nomMov1.ValorUnit / nomMov1.ValorUnitInferior ELSE 0 END AS IBC_SALUD_EMPRESA, 
	CASE WHEN nomMov.iditem = 202 THEN nomMov1.Cantidad * nomMov1.ValorUnit / nomMov1.ValorUnitInferior ELSE 0 END AS IBC_SALUD_EMPLEADO, 
	CASE WHEN nomMov.iditem = 201 AND idclasif = 1 AND nomMov1.iditem <> 221 THEN nomMov1.cantidad ELSE 0 END AS NUM_DIAS_SALUD_EMPRESA, 
	CASE WHEN nomMov.iditem = 202 AND idclasif = 1 AND nomMov1.iditem <> 221 THEN nomMov1.cantidad ELSE 0 END AS NUM_DIAS_SALUD_EMPLEADO, 
	CASE WHEN nomMov.iditem = 201 THEN nomMov.ValorUnit ELSE 0 END AS VALOR_SALUD_EMPRESA, 
	CASE WHEN nomMov.iditem = 202 THEN nomMov.ValorUnit ELSE 0 END AS VALOR_SALUD_EMPLEADO, 
	CASE WHEN nomMov.iditem = 203 THEN nomMov1.Cantidad * nomMov1.ValorUnit / nomMov1.ValorUnitInferior ELSE 0 END AS IBC_PENSION, 
	CASE WHEN nomMov.iditem = 203 AND idclasif = 1 AND nomMov1.iditem <> 221 THEN nomMov1.cantidad ELSE 0 END AS NUM_DIAS_PENSION, 
	CASE WHEN nomMov.iditem = 203 THEN nomMov.ValorUnit ELSE 0 END AS VALOR_PENSION_EMPRESA, 
	CASE WHEN nomMov.iditem = 204 THEN nomMov.ValorUnit ELSE 0 END AS VALOR_PENSION_EMPLEADO, 
	CASE WHEN nomMov.iditem = 200 THEN nomMov1.Cantidad * nomMov1.ValorUnit / nomMov1.ValorUnitInferior ELSE 0 END AS IBC_RIESGOS, 
	CASE WHEN nomMov.iditem = 200 AND idclasif = 1 AND nomMov1.iditem <> 221 THEN nomMov1.cantidad ELSE 0 END AS NUM_DIAS_RIESGOS, 
	CASE WHEN nomMov.iditem = 200 THEN nomMov.ValorUnit ELSE 0 END AS VALOR_RIESGOS, 
	CASE WHEN nomMov.iditem = 205 OR nomMov1.iditem = 221 THEN nomMov1.Cantidad * nomMov1.ValorUnit / nomMov1.ValorUnitInferior ELSE 0 END AS IBC_CCF, 
	CASE WHEN nomMov.iditem = 205 AND idclasif = 1 AND nomMov1.iditem <> 221 THEN nomMov1.cantidad ELSE 0 END AS NUM_DIAS_CCF, 
	CASE WHEN nomMov.iditem = 205 THEN nomMov.ValorUnit ELSE 0 END AS VALOR_CCF, 
	CASE WHEN nomMov.iditem = 223 OR nomMov1.iditem = 223 THEN nomMov.ValorUnit ELSE 0 END AS APV_AFIL, 
	CASE WHEN nomMov.iditem = 224 OR nomMov1.iditem = 224 THEN nomMov.ValorUnit ELSE 0 END AS AVP_APORT,
	CASE WHEN nomMov.iditem = 223 OR nomMov1.iditem = 223 OR nomMov1.iditem = 224 OR nomMov.iditem = 224 THEN 1 ELSE 0 END AS AVP,
	CASE WHEN nomMov1.iditem = 100 THEN 1 ELSE 0 END AS INTEGRAL, 
	CASE WHEN nomMov1.iditem = 199 THEN nomMov1.ValorUnit ELSE 0 END AS APORT_FONDO_SOLID, 
	nomMov.IdItem, 
	nomMov1.IdItem,
	nomMov1.FechaIniVac,
	nomMov1.FechaFinVac,
	nomMov1.Cantidad as Cantidad

	FROM dbo.Nomina_Movimientos AS nomMov INNER JOIN
	dbo.Encabezados AS enc ON nomMov.IdEncab = enc.IdEncab RIGHT OUTER JOIN
	dbo.Nomina_Movimientos AS nomMov1 ON nomMov.IdEncabNomMovBase = nomMov1.IdEncabNomMov INNER JOIN
	dbo.Nomina_Items AS item ON nomMov1.IdItem = item.IdItem
),

tmpIBC1
(IdEncab, IdContrato, IdCliente, IdCentroCosto, 
IBC_SALUD_EMPRESA, 
IBC_SALUD_EMPLEADO, 
NUM_DIAS_SALUD_EMPRESA, NUM_DIAS_SALUD_EMPLEADO, 
VALOR_SALUD_EMPRESA, 
VALOR_SALUD_EMPLEADO, 
IBC_PENSION, 
NUM_DIAS_PENSION, 
VALOR_PENSION_EMPRESA, 
VALOR_PENSION_EMPLEADO, 
IBC_RIESGOS, 
NUM_DIAS_RIESGOS, 
VALOR_RIESGOS, 
IBC_CCF, 
NUM_DIAS_CCF, 
VALOR_CCF, APV_AFIL, AVP_APORT,
AVP,
INTEGRAL, 
APORT_FONDO_SOLID,IdItem,IdItem1,
FechaIni,FechaFin,Cantidad,
IRP,FechaIniIRP,FechaFinIRP,
IGE,FechaIniIGE, FechaFinIGE,
LMA,FechaIniLMA,FechaFinLMA,
VAC,FechaIniVAC,FechaFinVAC,
SLN,FechaIniSLN, FechaFinSLN,
TipoRow1
)
as
(
	select 
	qBase.*,
	0 as IRP,Null as FechaIniIRP,Null as FechaFinIRP,
	0 as IGE,Null as FechaIniIGE,Null as FechaFinIGE,
	0 as LMA,Null as FechaIniLMA,Null as FechaFinLMA,
	'' as VAC,Null as FechaIniVAC,Null as FechaFinVAC,
	0 as SLN,Null as FechaIniSLN,Null as FechaFinSLN,
	1 as TipoRow1
	from tmpIBC as qBase
	where qBase.IdItem1 not in(225,112,222,226,220,219,218)

	union all

	select
	qBase.*,
	qBase.Cantidad AS IRP, FechaIni as FechaIniIRP,FechaFin as FechaFinIRP,
	0 as IGE,Null as FechaIniIGE,Null as FechaFinIGE,
	0 as LMA,Null as FechaIniLMA,Null as FechaFinLMA,
	'' as VAC,Null as FechaIniVAC,Null as FechaFinVAC,
	0 as SLN,Null as FechaIniSLN,Null as FechaFinSLN,
	2 as TipoRow1
	from tmpIBC as qBase
	where qBase.IdItem1 in(225)

	union all

	select
	qBase.*,
	0 as IRP,Null as FechaIniIRP,Null as FechaFinIRP,
	1 as IGE,FechaIni as FechaIniIGE,FechaFin as FechaFinIGE,
	0 as LMA,Null as FechaIniLMA,Null as FechaFinLMA,
	'' as VAC,Null as FechaIniVAC,Null as FechaFinVAC,
	0 as SLN,Null as FechaIniSLN,Null as FechaFinSLN,
	3 as TipoRow1
	from tmpIBC as qBase
	where qBase.IdItem1 in(112)

	union all

	select
	qBase.*,
	0 as IRP,Null as FechaIniIRP,Null as FechaFinIRP,
	0 as IGE,Null as FechaIniIGE,Null as FechaFinIGE,
	1 as LMA,FechaIni as FechaIniLMA,FechaFin as FechaFinLMA,
	'' as VAC,Null as FechaIniVAC,Null as FechaFinVAC,
	0 as SLN,Null as FechaIniSLN,Null as FechaFinSLN,
	4 as TipoRow1
	from tmpIBC as qBase
	where qBase.IdItem1 in(222,226)

	union all

	select
	qBase.*,
	0 as IRP,Null as FechaIniIRP,Null as FechaFinIRP,
	0 as IGE,Null as FechaIniIGE,Null as FechaFinIGE,
	0 as LMA,Null as FechaIniLMA,Null as FechaFinLMA,
	case when IdItem1=218 THEN 'L' ELSE 'X' END as VAC,FechaIni as FechaIniVAC,FechaFin as FechaFinVAC,
	0 as SLN,Null as FechaIniSLN,null as FechaFinSLN,
	5 as TipoRow1
	from tmpIBC as qBase
	where qBase.IdItem1 in(220,218)

	union all

	select
	qBase.*,
	0 as IRP,Null as FechaIniIRP,Null as FechaFinIRP,
	0 as IGE,Null as FechaIniIGE,Null as FechaFinIGE,
	0 as LMA,Null as FechaIniLMA,Null as FechaFinLMA,
	'' as VAC,Null as FechaIniVAC,Null as FechaFinVAC,
	1 as SLN,FechaIni as FechaIniSLN,FechaFin as FechaFinSLN,
	6 as TipoRow1
	from tmpIBC as qBase
	where qBase.IdItem1 in(219)
),


tmpIBCFin(
 IdEncab, IdCliente, IdContrato, IdCentroCosto, VALOR_SALUD, VALOR_PENSION, IBC_RIESGOS, IBC_SALUD, 
                        IBC_PENSION,NUM_DIAS_SALUD, NUM_DIAS_PENSION, 
                        NUM_DIAS_RIESGOS, VALOR_RIESGOS, IBC_CCF, NUM_DIAS_CCF, VALOR_CCF, APV_AFIL, AVP_APORT, IRP, IGE, LMA, VAC, AVP, INTEGRAL, APORT_FONDO_SOLID, SLN,
						FechaIniIRP,FechaFinIRP,FechaIniIGE, FechaFinIGE,FechaIniLMA,FechaFinLMA,FechaIniVAC,FechaFinVAC,FechaIniSLN, FechaFinSLN,TipoRow1
)
as
(
	SELECT      IdEncab, IdCliente, IdContrato, IdCentroCosto, VALOR_SALUD, VALOR_PENSION, 
				CASE WHEN IBC_RIESGOS = 0 AND TipoRow1 != 1 THEN IBC_PENSION ELSE IBC_RIESGOS END AS IBC_RIESGOS, 
				CASE WHEN IBC_SALUD = 0 AND TipoRow1 != 1 THEN IBC_PENSION ELSE IBC_SALUD END AS IBC_SALUD, 
				CASE WHEN IBC_PENSION = 0 AND TipoRow1 != 1 THEN IBC_SALUD ELSE IBC_PENSION END AS IBC_PENSION, 
				CASE WHEN NUM_DIAS_SALUD = 0 AND TipoRow1 != 1 THEN Cantidad ELSE NUM_DIAS_SALUD END AS NUM_DIAS_SALUD, 
				CASE WHEN NUM_DIAS_PENSION = 0 AND TipoRow1 != 1 THEN Cantidad ELSE NUM_DIAS_PENSION END AS NUM_DIAS_PENSION, 
				CASE WHEN NUM_DIAS_RIESGOS = 0 AND TipoRow1 != 1 THEN Cantidad ELSE NUM_DIAS_RIESGOS END AS NUM_DIAS_RIESGOS, VALOR_RIESGOS, 
				CASE WHEN IBC_CCF = 0 AND TipoRow1 != 1 THEN IBC_PENSION ELSE IBC_CCF END AS IBC_CCF, 
				CASE WHEN NUM_DIAS_CCF = 0 AND TipoRow1 != 1 THEN Cantidad ELSE NUM_DIAS_CCF END AS NUM_DIAS_CCF, 
				VALOR_CCF, APV_AFIL, AVP_APORT, IRP, IGE, LMA, VAC, AVP, INTEGRAL, APORT_FONDO_SOLID, SLN,
				FechaIniIRP,FechaFinIRP,FechaIniIGE, FechaFinIGE,FechaIniLMA,FechaFinLMA,FechaIniVAC,FechaFinVAC,FechaIniSLN, FechaFinSLN,TipoRow1
	FROM
	(
		SELECT      IdEncab, IdCliente, IdContrato, IdCentroCosto, CASE WHEN IBC_SALUD_EMPRESA = 0 THEN IBC_SALUD_EMPLEADO ELSE IBC_SALUD_EMPRESA END AS IBC_SALUD, 
					CASE WHEN NUM_DIAS_SALUD_EMPRESA = 0 THEN NUM_DIAS_SALUD_EMPLEADO ELSE NUM_DIAS_SALUD_EMPRESA END AS NUM_DIAS_SALUD, VALOR_SALUD, IBC_PENSION, 
					NUM_DIAS_PENSION, VALOR_PENSION, IBC_RIESGOS, NUM_DIAS_RIESGOS, VALOR_RIESGOS, IBC_CCF, NUM_DIAS_CCF, VALOR_CCF, APV_AFIL, AVP_APORT, IRP, IGE, LMA, VAC, AVP, 
					INTEGRAL, APORT_FONDO_SOLID, SLN, Cantidad, FechaIniIRP,FechaFinIRP,FechaIniIGE, FechaFinIGE,FechaIniLMA,FechaFinLMA,FechaIniVAC,FechaFinVAC,FechaIniSLN, FechaFinSLN,TipoRow1
		FROM 
		(
			SELECT      IdEncab, IdCliente, MAX(IdContrato) AS IdContrato, MAX(IdCentroCosto) AS IdCentroCosto, SUM(IBC_SALUD_EMPRESA) AS IBC_SALUD_EMPRESA, SUM(IBC_SALUD_EMPLEADO) AS IBC_SALUD_EMPLEADO, 
						SUM(NUM_DIAS_SALUD_EMPRESA) AS NUM_DIAS_SALUD_EMPRESA, SUM(NUM_DIAS_SALUD_EMPLEADO) AS NUM_DIAS_SALUD_EMPLEADO, SUM(VALOR_SALUD_EMPRESA) 
						+ SUM(VALOR_SALUD_EMPLEADO) AS VALOR_SALUD, SUM(IBC_PENSION) AS IBC_PENSION, SUM(NUM_DIAS_PENSION) AS NUM_DIAS_PENSION, SUM(VALOR_PENSION_EMPRESA) 
						+ SUM(VALOR_PENSION_EMPLEADO) AS VALOR_PENSION, SUM(IBC_RIESGOS) AS IBC_RIESGOS, SUM(NUM_DIAS_RIESGOS) AS NUM_DIAS_RIESGOS, SUM(VALOR_RIESGOS) 
						AS VALOR_RIESGOS, SUM(IBC_CCF) AS IBC_CCF, SUM(NUM_DIAS_CCF) AS NUM_DIAS_CCF, SUM(VALOR_CCF) AS VALOR_CCF, SUM(APV_AFIL) AS APV_AFIL, SUM(AVP_APORT) 
						AS AVP_APORT, SUM(IRP) AS IRP, MAX(IGE) AS IGE, MAX(LMA) AS LMA, MAX(VAC) AS VAC, MAX(AVP) AS AVP, MAX(INTEGRAL) AS INTEGRAL, MAX(APORT_FONDO_SOLID) 
						AS APORT_FONDO_SOLID, MAX(SLN) AS SLN, MAX(Cantidad) AS Cantidad,
						FechaIniIRP,FechaFinIRP,FechaIniIGE, FechaFinIGE,FechaIniLMA,FechaFinLMA,FechaIniVAC,FechaFinVAC,FechaIniSLN, FechaFinSLN,TipoRow1
			FROM tmpIBC1
			GROUP BY IdEncab, IdCliente,TipoRow1,FechaIniIRP,FechaFinIRP,FechaIniIGE, FechaFinIGE,FechaIniLMA,FechaFinLMA,FechaIniVAC,FechaFinVAC,FechaIniSLN,FechaFinSLN
		)AS q
	)AS q1
)

--Consulta Final

select ROW_NUMBER() OVER(PARTITION BY q.IdCliente ORDER BY q.IdContrato asc) AS Row, q.IdCliente,tipo.CodMiPlanilla as NomTipoIdent,cli.Identificacion,coalesce(vnom.IdTipoCot,1)TipoCot,
coalesce(vnom.IdSubTipoCot,0)SubTipoCot,case when vnom.IdTipoExt=1 then cast(1 as bit) else cast(0 as bit) end Extranjero,
case when vnom.IdTipoExt=2 then cast(1 as bit) else cast(0 as bit) end Col_Ext, 
cast(apellido1 as varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS as Apellido1, 
cast(Apellido2 as varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS as Apellido2,
cast(Nombre1 as varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS as Nombre1,
cast(Nombre2 as varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS as Nombre2,
case when vnom.FechaIni between @FechaIniT and @FechaFinT and ROW_NUMBER() OVER(PARTITION BY q.IdCliente ORDER BY q.IdContrato asc)=1 then cast(1 as bit) else cast(0 as bit) end ING,
case when vnom.FechaRetiro between @FechaIniT and @FechaFinT and ROW_NUMBER() OVER(PARTITION BY q.IdCliente ORDER BY q.IdContrato asc)=1 then cast(1 as bit) else cast(0 as bit) end RET,
cast(0 as bit) TDE,cast(0 as bit) TAE,cast(0 as bit) TDP, cast(0 as bit) TAP, cast(0 as bit) VSP,
cast(0 as bit) VTE, cast(0 as bit) VST, 
cast(SLN as bit)SLN,
cast(q.IGE as bit)IGE, 
cast(q.LMA as bit)LMA, 
VAC,
cast(q.AVP as bit)AVP,
cast(0 as bit)VCT, 
q.IRP,
CASE WHEN IBC_PENSION > 0 THEN CodAFP ELSE '' END AS COD_AFP,
'' COD_AFP_TRANS,CodEPS AS COD_EPS,
'' COD_EPS_TRANS,CodCCF AS COD_CCF, NUM_DIAS_PENSION,NUM_DIAS_SALUD,NUM_DIAS_RIESGOS,NUM_DIAS_CCF, vnom.SueldoIni SALARIO_BASICO,q.INTEGRAL,
IBC_PENSION,IBC_SALUD,IBC_RIESGOS,IBC_CCF,TARIFA_AFP, q.APV_AFIL,q.AVP_APORT,CAST (NULL  AS NUMERIC(28,6)) VALOR_NO_RETE,CAST (0 AS NUMERIC(28,6)) UPC_ADIC,
CASE WHEN SLN=1 AND (q.EsExoneradoAportes=1 or VNom.EsExoneradoAportes = 1) THEN 0 ELSE TARIFA_EPS END TARIFA_EPS,
CASE WHEN TipoRow1!=1 THEN 0 ELSE vnom.ARPTarifa END TARIFA_RIESGO,
TARIFA_CCF,TARIFA_SENA,TARIFA_ICBF, CAST(0 AS  NUMERIC(28,6)) TARIFA_ESAP,CAST(0 AS NUMERIC(28,6)) TARIFA_MISNIS,
CEILING(q.VALOR_RIESGOS/100)*100 AS COTIZACION_RIESGOS,ceiling(q.VALOR_CCF/100)*100 AS COTIZACION_CAJA, ceiling(VALOR_PENSION/100)*100 AporteObligatorioAFP,
round(APORT_FONDO_SOLID/2,-2)APORT_FONDO_SOLID,round(APORT_FONDO_SOLID/2,-2)APORT_FONDO_SUBCUENTA,
ceiling(VALOR_SALUD/100)*100 AporteObligatorioSalud, '' as NumIncap, 0 as ValorIncap,'' as NumMat, 0 as ValorMat, 0 as CodigoCT,round((IBC_CCF*TARIFA_SENA/100),-2)VALOR_SENA,
round((IBC_CCF*TARIFA_ICBF/100),-2)VALOR_ICBF, cast(0 AS  numeric(28,6)) VALOR_ESAP,cast(0 AS numeric(28,6)) VALOR_MISNIS,[IdDptoCaja]Dpto,[CodMunicipioCaja]Municipio,
case when (q.EsExoneradoAportes=1 or VNom.EsExoneradoAportes = 1) then 'S' else 'N' end  Exonerado,CodTarifa,
case when vnom.FechaIni between @FechaIniT and @FechaFinT then vnom.FechaIni else null end FechaIng,
case when vnom.FechaRetiro between @FechaIniT and @FechaFinT then FechaRetiro else null end FechaRet,
null as FechaVsp,FechaIniSLN,FechaFinSLN,FechaIniIGE, FechaFinIGE,FechaIniLMA,FechaFinLMA,FechaIniVAC,FechaFinVAC,
null as FechaIniVCT ,null as FechaFinVCT,FechaIniIRP,FechaFinIRP, IBC_CCF IBC_Diferente_CCF,NUM_DIAS_SALUD*8 as HorasLabor, VNom.PreTipoSalario,
CC.NomCentroCosto
    
from
(
	select vIBC.IdCliente,vIBC.IdContrato as IdContrato,
	case when SUM(vIBC.IBC_SALUD)> @salarioMin OR SLN =1 then ceiling(SUM(vIBC.IBC_SALUD))  else ceiling(SUM(vIBC.IBC_SALUD))  end IBC_SALUD, 
	SUM(vIBC.NUM_DIAS_SALUD)NUM_DIAS_SALUD,SUM(vIBC.VALOR_SALUD)VALOR_SALUD,
	case when SUM(vIBC.IBC_PENSION) > @salarioMin OR SLN =1 then ceiling(SUM(vIBC.IBC_PENSION)) else ceiling(SUM(vIBC.IBC_PENSION)) end IBC_PENSION,
	SUM(vIBC.NUM_DIAS_PENSION)NUM_DIAS_PENSION,SUM(vIBC.VALOR_PENSION)VALOR_PENSION,
	case when SUM(vIBC.IBC_RIESGOS) > @salarioMin  OR TipoRow1 !=1then ceiling(SUM(vIBC.IBC_RIESGOS)) else  ceiling(SUM(vIBC.IBC_RIESGOS)) end  IBC_RIESGOS,
	SUM(vIBC.NUM_DIAS_RIESGOS)NUM_DIAS_RIESGOS,SUM(vIBC.VALOR_RIESGOS)VALOR_RIESGOS,
	case when SUM(vIBC.IBC_CCF) > @salarioMin   OR TipoRow1 !=1 then  ceiling(SUM(vIBC.IBC_CCF)) else ceiling(SUM(vIBC.IBC_CCF)) end IBC_CCF,
	SUM(vIBC.NUM_DIAS_CCF)NUM_DIAS_CCF,SUM(vIBC.VALOR_CCF)VALOR_CCF,	
	SUM(vIBC.APV_AFIL)APV_AFIL,SUM(vIBC.AVP_APORT)AVP_APORT,SUM(vIBC.IRP)IRP,max(IGE)IGE,max(LMA)LMA,max(VAC)VAC,max(AVP)AVP,max(INTEGRAL)INTEGRAL,SLN,
	max(nomTar.TARIFA_EPS)TARIFA_EPS,max(nomTar.TARIFA_AFP)TARIFA_AFP,
	case when SLN =1 then 0 else max(nomTar.TARIFA_CCF)end TARIFA_CCF,
	max(nomTar.TARIFA_SENA)TARIFA_SENA,max(nomTar.TARIFA_ICBF)TARIFA_ICBF,
	SUM(vIBC.APORT_FONDO_SOLID)APORT_FONDO_SOLID,
	Emp.EsExoneradoAportes, FechaIniIRP,FechaFinIRP,FechaIniIGE, FechaFinIGE,FechaIniLMA,FechaFinLMA,FechaIniVAC,FechaFinVAC,FechaIniSLN, FechaFinSLN,TipoRow1,
	MAX(vIBC.IdCentroCosto) AS IdCentroCosto
	from tmpIBCFin vIBC inner join [dbo].[Encabezados] enc
	on vIBC.IdEncab=enc.IdEncab left join [dbo].[VNominaTarifas] nomTar on vIBC.IdEncab=nomTar.IdEncab and vIBC.IdContrato=nomTar.IdContrato
	inner join Empresas Emp on emp.IdEmpresa=enc.IdEmpresa
	WHERE
	enc.IdEmpresa=@IdEmpresa and FechaIni between @FechaIniT and @FechaFinT 
	AND (@IdCentroCosto IS NULL OR vIBC.IdCentroCosto = @IdCentroCosto)
	group by  vIBC.IdCliente,vIBC.IdContrato,SLN,Emp.EsExoneradoAportes,TipoRow1, FechaIniIRP,FechaFinIRP,FechaIniIGE, FechaFinIGE,FechaIniLMA,FechaFinLMA,FechaIniVAC,FechaFinVAC,FechaIniSLN, FechaFinSLN

) as q inner join VClientes2 cli on q.IdCliente=cli.IdCliente
inner join VNominaContratos VNom on VNom.IdContrato=q.IdContrato
inner join [dbo].[Clientes_TiposIdent] tipo on tipo.IdTipoIdent=cli.IdTipoIdent 
left join [dbo].[Clientes_ArpTarifas] arpTarifa on arpTarifa.IdTarifa=VNom.IdArpTarifa
LEFT JOIN CentroCostos CC ON q.IdCentroCosto = CC.IdCentroCosto
order by q.IdContrato

END
GO
	/*
	Plantilla de script posterior a la implementación							
	--------------------------------------------------------------------------------------
	 Este archivo contiene instrucciones de SQL que se anexarán al script de compilación.		
	 Use la sintaxis de SQLCMD para incluir un archivo en el script posterior a la implementación.			
	 Ejemplo:      :r .\miArchivo.sql								
	 Use la sintaxis de SQLCMD para hacer referencia a una variable en el script posterior a la implementación.		
	 Ejemplo:      :setvar TableName miTabla							
				   SELECT * FROM [$(TableName)]					
	--------------------------------------------------------------------------------------
	*/

GO
--Actualizacion de videos 17/06/2022

UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/148HXw15D38' WHERE IdInterface = 334
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/pNMeoHiiN0o' WHERE IdInterface = 374
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/2hI-ykH6qZA' WHERE IdInterface = 393
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/D7rzoY6XRoI' WHERE IdInterface = 407
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/epnfNysBPCU' WHERE IdInterface = 408
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/Zlg4jw6uEBc' WHERE IdInterface = 419
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/xFcDADACFkE' WHERE IdInterface = 504
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/X_ar4GWnPGM' WHERE IdInterface = 505
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/ecNwXi7P0zk' WHERE IdInterface = 506
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/HzklzCigm9g' WHERE IdInterface = 584
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/MFLIsF3N48I' WHERE IdInterface = 585
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/xwvJKLgem8k' WHERE IdInterface = 590
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/ilE-MMLVSXs' WHERE IdInterface = 591
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/6t6bXjeDoJI' WHERE IdInterface = 656
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/POuMehtjs-4' WHERE IdInterface = 657
UPDATE Permisos_Interfaces SET VideoURL = 'https://youtu.be/Y8uzWRaXKlU' WHERE IdInterface = 663

GO

GO
PRINT N'Update complete.';


GO


--Actualizar datos de la version
INSERT INTO ZiurVersiones (IdVersion, NumVersion, Descripcion)
VALUES (318, 908, 'Pago seguridad social y videos interfaces')
GO

COMMIT
GO
