--Hash:1C889BF4CEABC15D71E2DFADEC7715CE
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 < 903
)
RAISERROR('Error : hay un script que no se ejecuto , debe ejecutarlo manualmente.', 20, -1) with log
--//

/*
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


ALTER PROCEDURE [dbo].[GetArqueoCaja]

@IdDocumentos	NVARCHAR(1000),
@FechaIni		NVARCHAR(10),
@FechaFin		NVARCHAR(10),
@IdEmpresas		NVARCHAR(1000),
@IdSucursales	NVARCHAR(1000),
@IdBodegas		NVARCHAR(1000)
AS
BEGIN
     
DECLARE
@FechaIniT DATETIME,
@FechaFinT DATETIME 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

SELECT enc.IdEncab,enc.IdDocumento
INTO #tempEncabezados
FROM 
Encabezados enc
WHERE 
enc.Fecha Between @FechaIniT and @FechaFinT AND
enc.IdEmpresa in (select number from iter$simple_intlist_to_tbl(@IdEmpresas)) AND
enc.IdDocumento in (select number from iter$simple_intlist_to_tbl(@IdDocumentos)) AND
coalesce(enc.IdSucursal,0) in (select number from iter$simple_intlist_to_tbl(@IdSucursales)) and
enc.EsAnulado = 0  


--Registros padres (Detalles documento)
SELECT 
cast(ROW_NUMBER() OVER (ORDER BY q.IdEncab) as int) as KeyRow,
q.IdEncab,
emp.EmpresaNombre as NomEmpresa,
emp.EmpresaNit as NitEmpresa,
suc.CodSucursal +'-'+suc.NomSucursal as NomSucursal,
q.Fecha,
q.NomPrefijo as Prefijo,
q.IdDocumento,
doc.NomDocumento,
q.Documento,
q.NumDocumento,
doc.FactorInventario,
q.SubTotGravado,
q.SubTotExento,
doc.EsMov,
COALESCE(cli.Nombre1 + ' ','') + COALESCE(cli.Nombre2 + ' ','') + COALESCE(cli.Apellido1 + ' ','') + COALESCE(cli.Apellido2 + ' ','') as NombreCliente,
COALESCE(q.SubTotal,0)		* ((doc.FactorInventario) * -1) as SubTotal,
COALESCE(q.TotalDcto,0)		* ((doc.FactorInventario) * -1) as TotalDcto,
COALESCE(q.TotalIVA,0)		* ((doc.FactorInventario) * -1) as TotalIVA,
COALESCE(q.TotalImptoCosumo,0) * ((doc.FactorInventario) * -1) as TotalImptoCosumo,
COALESCE(q.GranTotal,0)		* ((doc.FactorInventario) * -1) as GranTotal,
COALESCE(SumReteFuente,0) as ReteFuente,NomPc
FROM
(
	SELECT 
	enc.IdEncab,
	enc.Fecha,
	NumDocumento,
	COALESCE(pref.NomPrefijo + '  ','') + CAST(enc.NumDocumento as NVARCHAR(50)) as Documento,
	enc.IdDocumento,
	pref.NomPrefijo,
	enc.IdCliente1,
	enc.IdEmpresa,
	enc.IdSucursal,	
	SUM(encM.Cantidad * encM.FactorCantidad * encM.ValorUnitario) as SubTotal,
	SUM(encM.Cantidad * encM.FactorCantidad * encM.ValorUnitario * (encM.Dcto / 100)) as TotalDcto,
	SUM(((encM.Cantidad * encM.FactorCantidad * encM.ValorUnitario)-
	(encM.Cantidad * encM.FactorCantidad * encM.ValorUnitario * (encM.Dcto / 100)))
	 * (encM.IvaPorc / 100)) as TotalIVA,
	SUM(encM.ValorImptoConsumoMov)as TotalImptoCosumo,
	SUM(encM.Cantidad * encM.FactorCantidad * encM.ValorUnitario) -
	SUM(encM.Cantidad * encM.FactorCantidad * encM.ValorUnitario * (encM.Dcto / 100))+
	SUM(((encM.Cantidad * encM.FactorCantidad * encM.ValorUnitario)-
	(encM.Cantidad * encM.FactorCantidad * encM.ValorUnitario * (encM.Dcto / 100)))
	 * (encM.IvaPorc / 100)+ encM.ValorImptoConsumoMov) as GranTotal,
	 max(isnull(NomPc,''))as NomPc,
	 MAX(dbo.VTot_EncMov.SubTotExento) as SubTotExento,
	 MAX(dbo.VTot_EncMov.SubTotGravado) as SubTotGravado
	FROM 
    #tempEncabezados encT INNER JOIN 
	Encabezados enc ON encT.IdEncab=enc.IdEncab LEFT OUTER JOIN
	Prefijos pref ON enc.IdPrefijo = pref.IdPrefijo LEFT OUTER JOIN
	VEncabezadosMov encM ON enc.IdEncab = encM.IdEncab LEFT OUTER JOIN
	dbo.VTot_EncMov ON Enc.IdEncab = dbo.VTot_EncMov.IdEncab 
	WHERE 
	(LEN(@IdBodegas)=0  OR encM.IdBodega in (select number from iter$simple_intlist_to_tbl(@IdBodegas))) AND
	coalesce(encM.EsVisible,1)=1
	GROUP BY enc.IdEncab,enc.IdDocumento,enc.Fecha, enc.NumDocumento, enc.IdCliente1, pref.NomPrefijo, enc.IdEmpresa, enc.IdSucursal
) as q INNER JOIN
Documentos doc ON q.IdDocumento = doc.IdDocumento INNER JOIN
Clientes cli ON q.IdCliente1 = cli.IdCliente INNER JOIN
Empresas emp ON emp.IdEmpresa = q.IdEmpresa LEFT OUTER JOIN
Sucursales suc ON suc.IdSucursal=q.IdSucursal LEFT OUTER JOIN
dbo.VTotalesRete rete ON rete.IdEncab=q.IdEncab 



----Registros hijos1 (Detalles formas de pago)

SELECT 
s.IdEncab,
CASE WHEN s.FormaPagoDet='' THEN '**No Especificado**' ELSE s.FormaPagoDet END AS FormaPagoDet,
s.NumDoc,
CASE WHEN s.FormaPagoDet = '' THEN ValorPago2 ELSE ValorPago1 END AS ValorPago

FROM 

(

SELECT 
q.IdEncab,
COALESCE(encPFP.NomFormaPago , '') as FormaPagoDet,
COALESCE(encP.NumDoc,'') as NumDoc,
COALESCE(encP.ValorPago,0) * coalesce(encP.FactorSuma,doc.FactorContable)  as ValorPago1,
COALESCE(r.GranTotal,0) as ValorPago2
FROM
(
	SELECT 
	encT.IdEncab,
	encT.IdDocumento
	FROM 
	#tempEncabezados encT
) as q LEFT OUTER JOIN
(
	SELECT encM.IdEncab, 
	SUM(encM.Cantidad * encM.FactorCantidad * encM.ValorUnitario) -	
	SUM(encM.Cantidad * encM.FactorCantidad * encM.ValorUnitario * (encM.Dcto / 100)) + 	
	SUM(((encM.Cantidad * encM.FactorCantidad * encM.ValorUnitario)- 	
	(encM.Cantidad * encM.FactorCantidad * encM.ValorUnitario * (encM.Dcto / 100))) * (encM.IvaPorc / 100)+ encM.ValorImptoConsumoMov) as GranTotal 
	FROM #tempEncabezados as encT LEFT JOIN 
	VEncabezadosMov as encM ON encT.IdEncab = encM.IdEncab
	GROUP BY encM.IdEncab
) r ON q.IdEncab = r.IdEncab  INNER JOIN
Documentos doc ON q.IdDocumento = doc.IdDocumento LEFT OUTER JOIN
Encabezados_PagoDet encP ON q.IdEncab = encP.IdEncab  LEFT OUTER JOIN
Encabezados_PagoDet_FormasPago encPFP ON encP.IdFormaPago = encPFP.IdFormaPago
) s


----Registros hijos2 (Detalles IVA)
SELECT
IdEncab,
Operacion,
Tarifa,
SUM(BaseGravable) AS BaseGravable,
SUM(Descuentos) AS Descuentos,
SUM(ImptoConsumo) AS ImptoConsumo,
SUM(ValorIva) AS ValorIva
FROM
(
	SELECT 
	enc.IdEncab,
	NomClasifImpto AS Operacion,
	encM.ValorIva AS Tarifa,
	encM.SubTotal AS BaseGravable,
	encM.ValorDcto AS Descuentos,
	(FactorImpoConsPorc * SubTotal / 100 + FactorImpoConsValor * CantConFactor) * encM.ValorImptoConsumo  AS ImptoConsumo,
	encM.SubIVA AS ValorIva
	FROM 
	#tempEncabezados encT INNER JOIN 
	Encabezados enc ON encT.IdEncab=enc.IdEncab INNER JOIN 
	[dbo].[VContab_EncMov] encM ON enc.IdEncab = encM.IdEncab INNER JOIN
	[dbo].[Inventarios] Inv ON encM.IdInventario = Inv.IdInventario INNER JOIN
	[dbo].[Inventarios_ClasifImpto] Clas ON Inv.IdClasifImpto = Clas.IdClasifImpto
	WHERE 
	(LEN(@IdBodegas)=0  OR encM.IdBodega in (select number from iter$simple_intlist_to_tbl(@IdBodegas))) 
)AS q
GROUP BY  IdEncab,Operacion,Tarifa

OPTION(RECOMPILE)

END
GO
PRINT N'Modificando Procedimiento [dbo].[GetBalancePrueba]...';


GO
--SE EJECUTO EN COMPUSPAR EL 11/11/2021 CON OPTION RECOMPILE
ALTER PROCEDURE [dbo].[GetBalancePrueba]
	@FechaIni NVARCHAR(10) = '',
	@FechaFin NVARCHAR(10) = '',
	@CodCuentaIni NVARCHAR(200)='',
	@CodCuentaFin NVARCHAR(200)='',
    @Detallar BIT = 1,
	@IdEmpresas NVARCHAR(1000) = '',
	@IdDocsExc NVARCHAR(1000) ='',
	@IdCentroCosto INT = NULL,
	@EsNotGAAP BIT = NULL,
    @EsNotIFRS BIT = NULL,
	@IdCliente INT=NUll,
	@EsAgrupIFRS BIT = NULL	
AS
BEGIN

DECLARE

@IdTipoPUC INT,
@FechaIniT DATETIME,
@FechaFinT DATETIME,
@SepCuenta NVARCHAR(100)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT @IdTipoPUC = ValParametro FROM PARAMETROS WHERE CodParametro = 1
IF @IdTipoPUC IS NULL
	SET @IdTipoPUC = 1

SELECT @SepCuenta = ValParametro FROM PARAMETROS WHERE CodParametro = 9
IF @SepCuenta IS NULL
	SET @SepCuenta = '-'
	
SET @FechaIniT = CAST(CONVERT(DATETIME, @FechaIni, 103) + ' 00:00:00' AS DATETIME)
SET @FechaFinT = CAST(CONVERT(DATETIME, @FechaFin, 103) + ' 23:59:59' AS DATETIME)

;WITH tmpMov (CodCuenta, NomCuenta, DetallarCliente, Factor, SenDet, IdCliente, SaldoIni, Debito, Credito, SaldoIniExt, DebitoExt, CreditoExt, CodCuentaIFRS,DebitoGAAP,CreditoGAAP,DebitoIFRS,CreditoIFRS,SaldoIniGAAP,SaldoIniIFRS)
as
(
	SELECT 
	Cta.CodCuenta, Cta.NomCuenta, Cta.DetallarCliente, Factor, (@Detallar & Cta.DetallarCliente) as SenDet, CASE WHEN (@Detallar = 1 AND Cta.DetallarCliente = 1) THEN Enc_C.IdCliente ELSE NULL END AS [IdCliente],
	0.0 AS [SaldoIni], 
	CAST(SUM([Debito]) AS NUMERIC(28,6)) AS [Debitos], 
	CAST(SUM([Credito]) AS NUMERIC(28,6)) AS [Creditos], 	
	0.0 AS [SaldoIniExt],
	CAST(SUM([DebitoExt]) AS NUMERIC(28,6)) AS [DebitosExt], 
	CAST(SUM([CreditoExt]) AS NUMERIC(28,6)) AS [CreditosExt],
	CodCuentaIFRS,
	CAST(sum(case when Enc_C.EsNotGAAP=0 THEN  Debito else 0 end) AS NUMERIC(28,6)) DebitoGAAP,
	CAST(sum(case when Enc_C.EsNotGAAP=0 THEN  Credito else 0 end) AS NUMERIC(28,6))  CreditoGAAP,
	CAST(sum(case when Enc_C.EsIFRS=0 THEN  Debito else 0 end) AS NUMERIC(28,6)) DebitoIFRS,
	CAST(sum(case when Enc_C.EsIFRS=0 THEN  Credito else 0 end) AS NUMERIC(28,6)) CreditoIFRS,
	0.0 AS [SaldoIniGAAP],0.0 AS [SaldoIniIFRS]
	FROM 
	dbo.Encabezados AS Enc INNER JOIN dbo.Cuentas AS Cta INNER JOIN dbo.Cuentas_Tipos AS Cta_T ON Cta.IdCuentaTipo = Cta_T.IdCuentaTipo INNER JOIN dbo.Cuentas_Clases AS Cta_C ON Cta_T.IdClase = Cta_C.IdClase INNER JOIN dbo.Encabezados_Cont AS Enc_C ON Cta.IdCuenta = Enc_C.IdCuenta ON Enc.IdEncab = Enc_C.IdEncab
	WHERE
	(Enc.Fecha >= @FechaIniT And Enc.Fecha <= @FechaFinT) AND
	Cta.CodCuenta BETWEEN @CodCuentaIni And @CodCuentaFin AND
	Enc.IdEmpresa IN(select number from iter$simple_intlist_to_tbl(@IdEmpresas)) AND
	Enc.IdDocumento NOT IN(select nstr from iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
	Enc.EsAnulado=0 AND
	(@EsNotGAAP IS NULL OR Enc_C.EsNotGAAP=@EsNotGAAP) AND
	(@EsNotIFRS IS NULL OR Enc_C.EsIFRS=@EsNotIFRS) AND
	(@IdCentroCosto IS NULL OR Enc_C.IdCentroCosto=@IdCentroCosto) AND
	(@IdCliente IS NULL OR Enc_C.IdCliente=@IdCliente)
	GROUP BY
	Cta.CodCuenta, Cta.NomCuenta, Cta.DetallarCliente, Factor, (@Detallar & Cta.DetallarCliente), CASE WHEN (@Detallar = 1 AND Cta.DetallarCliente = 1) THEN Enc_C.IdCliente ELSE NULL END,CodCuentaIFRS
)
,
tmpSaldoIni (CodCuenta, NomCuenta, DetallarCliente, Factor, SenDet, IdCliente, SaldoIni, Debito, Credito,SaldoIniExt, DebitoExt, CreditoExt,CodCuentaIFRS,DebitoGAAP,CreditoGAAP,DebitoIFRS,CreditoIFRS,SaldoIniGAAP,SaldoIniIFRS)
as
(
	SELECT 
	Cta.CodCuenta, Cta.NomCuenta, Cta.DetallarCliente, Factor, (@Detallar & Cta.DetallarCliente) as SenDet, CASE WHEN (@Detallar = 1 AND Cta.DetallarCliente = 1) THEN Enc_C.IdCliente ELSE NULL END AS [IdCliente],
	CAST(SUM(([Debito]-[Credito])*[Factor]) AS NUMERIC(28,6)) AS [SaldoIni], 
	0.0 AS [Debitos], 
	0.0 AS [Creditos], 
	CAST(SUM(([DebitoExt]-[CreditoExt])*[Factor]) AS NUMERIC(28,6)) AS [SaldoIniExt],
	0.0 AS [DebitosExt], 
	0.0 AS [CreditosExt], 	
	CodCuentaIFRS,
	0.0 AS DebitoGAAP,0.0 AS CreditoGAAP,0.0 AS DebitoIFRS,0.0 AS CreditoIFRS,
	CAST(sum(case when Enc_C.EsNotGAAP=0 THEN  Factor * ([Debito]-[Credito]) else 0 end) AS NUMERIC(28,6)) SaldoFinGAAP,
	CAST(sum(case when Enc_C.EsIFRS=0 THEN  Factor * ([Debito]-[Credito]) else 0 end) AS NUMERIC(28,6)) SaldoFinIFRS
	FROM 
	dbo.Encabezados AS Enc INNER JOIN dbo.Cuentas AS Cta INNER JOIN dbo.Cuentas_Tipos AS Cta_T ON Cta.IdCuentaTipo = Cta_T.IdCuentaTipo INNER JOIN dbo.Cuentas_Clases AS Cta_C ON Cta_T.IdClase = Cta_C.IdClase INNER JOIN dbo.Encabezados_Cont AS Enc_C ON Cta.IdCuenta = Enc_C.IdCuenta ON Enc.IdEncab = Enc_C.IdEncab
	WHERE
	Enc.Fecha < @FechaIniT AND
	Cta.CodCuenta BETWEEN @CodCuentaIni And @CodCuentaFin AND
	Enc.IdEmpresa IN(select number from iter$simple_intlist_to_tbl(@IdEmpresas)) AND
	--Enc.IdDocumento NOT IN(select nstr from iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
	Enc.EsAnulado=0
	AND

	(@EsNotGAAP IS NULL OR Enc_C.EsNotGAAP=@EsNotGAAP) AND
	(@EsNotIFRS IS NULL OR Enc_C.EsIFRS=@EsNotIFRS) AND
	(@IdCentroCosto IS NULL OR Enc_C.IdCentroCosto=@IdCentroCosto) AND
	(@IdCliente IS NULL OR Enc_C.IdCliente=@IdCliente)

	GROUP BY
	Cta.CodCuenta, Cta.NomCuenta, Cta.DetallarCliente, Factor, (@Detallar & Cta.DetallarCliente), CASE WHEN (@Detallar = 1 AND Cta.DetallarCliente = 1) THEN Enc_C.IdCliente ELSE NULL END,CodCuentaIFRS
),

	tempCuentas(CodCuenta,NomCuentaBase,CodCuentaEspejo,NomCuentaEspejo)
		as(
			select CodAgrup,
			CASE WHEN @EsAgrupIFRS=1 and min(isnull(NomCuentaIFRS,'')) <>'' then min(isnull(NomCuentaIFRS,'')) else min(isnull(NomCuenta,'')) end NomCuenta,
			CASE WHEN @EsAgrupIFRS=1 then min(isnull(CodCuenta,'')) else min(isnull(CodCuentaIFRS,''))end CodCuentaEspejo,
			CASE WHEN @EsAgrupIFRS=1 then min(isnull(NomCuenta,'')) else min(isnull(NomCuentaIFRS,''))end NomCuentaEspejo
			from
			(
			select CodCuenta,NomCuenta,
			CodCuentaIFRS,NomCuentaIFRS,
			 case when @EsAgrupIFRS=1 and len(CodCuentaIFRS)> 0 then COALESCE(CodCuentaIFRS,CodCuenta) else CodCuenta end CodAgrup
			from cuentas
			where idtipoPuc=@IdTipoPUC
			)as q1
			group by CodAgrup
		)

SELECT
CodCuenta,
q1.Cuenta_Tercero, q1.Cuenta_Nit, q1.IdTipoIdent, q1.NomTipoIdent, q1.Identificacion, q1.DVCliente, q1.Direccion, q1.DireccionFormato1, q1.DireccionFormato2, q1.IdCiudad, q1.NomCiudad, 
q1.SaldoIni, q1.TotalDebitos, q1.TotalCreditos, q1.SaldoIniExt, q1.TotalDebitosExt, q1.TotalCreditosExt,  q1.SaldoFin, q1.SaldoFinExt, q1.DetallarCliente,
q1.SaldoIniGAAP, q1.TotalDebitosGAAP, q1.TotalCreditosGAAP,q1.SaldoFinGAAP,
q1.SaldoIniIFRS, q1.TotalDebitosIFRS, q1.TotalCreditosIFRS,q1.SaldoFinIFRS,
q1.CodNiv1 AS CodNiv1, CASE WHEN q1.CodNiv2 = q1.CodNiv1 THEN NULL ELSE q1.CodNiv7 END AS CodNiv2, CASE WHEN q1.CodNiv3 = q1.CodNiv2 THEN NULL ELSE q1.CodNiv7 END AS CodNiv3, CASE WHEN q1.CodNiv4 = q1.CodNiv3 THEN NULL ELSE q1.CodNiv7 END AS CodNiv4, CASE WHEN q1.CodNiv5 = q1.CodNiv4 THEN NULL ELSE q1.CodNiv7 END AS CodNiv5, CASE WHEN q1.CodNiv6 = q1.CodNiv5 THEN NULL ELSE q1.CodNiv6 END AS CodNiv6, CASE WHEN q1.CodNiv7 = q1.CodNiv6 THEN NULL ELSE q1.CodNiv7 END AS CodNiv7,
q1.NomCta1, q1.NomCta2, q1.NomCta3, q1.NomCta4, q1.NomCta5, q1.NomCta6, q1.NomCta7,
q1.CodNiv1 + @SepCuenta + q1.NomCta1 AS NomCuenta1, 
CASE WHEN q1.CodNiv2 = q1.CodNiv1 THEN NULL ELSE q1.CodNiv2 + @SepCuenta + q1.NomCta2 END AS NomCuenta2,
CASE WHEN q1.CodNiv3 = q1.CodNiv2 THEN NULL ELSE q1.CodNiv3 + @SepCuenta + q1.NomCta3 END AS NomCuenta3,
CASE WHEN q1.CodNiv4 = q1.CodNiv3 THEN NULL ELSE q1.CodNiv4 + @SepCuenta + q1.NomCta4 END AS NomCuenta4,
CASE WHEN q1.CodNiv5 = q1.CodNiv4 THEN NULL ELSE q1.CodNiv5 + @SepCuenta + q1.NomCta5 END AS NomCuenta5,
CASE WHEN q1.CodNiv6 = q1.CodNiv5 THEN NULL ELSE q1.CodNiv6 + @SepCuenta + q1.NomCta6 END AS NomCuenta6,
CASE WHEN q1.CodNiv7 = q1.CodNiv6 THEN NULL ELSE q1.CodNiv7 + @SepCuenta + q1.NomCta7 END AS NomCuenta7,

q1.CodEsp1 + @SepCuenta + q1.NomCtaEsp1 AS NomCuentaEsp1, 
CASE WHEN q1.CodNiv2 = q1.CodNiv1 THEN NULL ELSE q1.CodEsp2 + @SepCuenta + q1.NomCtaEsp2 END AS NomCuentaEsp2,
CASE WHEN q1.CodNiv3 = q1.CodNiv2 THEN NULL ELSE q1.CodEsp3 + @SepCuenta + q1.NomCtaEsp3 END AS NomCuentaEsp3,
CASE WHEN q1.CodNiv4 = q1.CodNiv3 THEN NULL ELSE q1.CodEsp4 + @SepCuenta + q1.NomCtaEsp4 END AS NomCuentaEsp4,
CASE WHEN q1.CodNiv5 = q1.CodNiv4 THEN NULL ELSE q1.CodEsp5 + @SepCuenta + q1.NomCtaEsp5 END AS NomCuentaEsp5,
CASE WHEN q1.CodNiv6 = q1.CodNiv5 THEN NULL ELSE q1.CodEsp6 + @SepCuenta + q1.NomCtaEsp6 END AS NomCuentaEsp6,
CASE WHEN q1.CodNiv7 = q1.CodNiv6 THEN NULL ELSE q1.CodEsp7 + @SepCuenta + q1.NomCtaEsp7 END AS NomCuentaEsp7,
CodCtaUgpp,
NomCtaUgpp

FROM 
(
	SELECT u.CodCuenta,u.CodCuentaIFRS,
	CASE WHEN u.SenDet = 1 THEN LTRIM(RTRIM(Cli.[Apellidos] + ' ' + Cli.[Nombres])) ELSE u.NomCuenta END AS [Cuenta_Tercero],
	CASE WHEN u.SenDet = 1 THEN LTRIM(RTRIM(Cli.[NomTipoIdent] + ' ' + Cli.[Identificacion])) ELSE '' END AS [Cuenta_Nit],
	CASE WHEN u.SenDet = 1 THEN Cli.IdTipoIdent ELSE '' END AS IdTipoIdent,
	CASE WHEN u.SenDet = 1 THEN Cli.NomTipoIdent ELSE '' END AS NomTipoIdent,
	CASE WHEN u.SenDet = 1 THEN Cli.Identificacion ELSE '' END AS Identificacion,
	CASE WHEN u.SenDet = 1 THEN Cli.DVCliente ELSE '' END AS DVCliente,
	CASE WHEN u.SenDet = 1 THEN CliDir.Direccion ELSE '' END AS Direccion,
	CASE WHEN u.SenDet = 1 THEN CliDir.DireccionFormato1 ELSE '' END AS DireccionFormato1,
	CASE WHEN u.SenDet = 1 THEN CliDir.DireccionFormato2 ELSE '' END AS DireccionFormato2,
	CASE WHEN u.SenDet = 1 THEN CliDir.IdCiudad ELSE '' END AS IdCiudad,
	CASE WHEN u.SenDet = 1 THEN CliDir.NomCiudad ELSE '' END AS NomCiudad,
	u.SaldoIni, u.SaldoIniExt, u.TotalDebitos, u.TotalDebitosExt, u.TotalCreditos, u.TotalCreditosExt, u.SaldoIni + (u.TotalDebitos - u.TotalCreditos) * u.Factor as SaldoFin, 	u.SaldoIniExt + (u.TotalDebitosExt - u.TotalCreditosExt) * u.Factor as SaldoFinExt,	u.SaldoIniGAAP, u.TotalDebitosGAAP, u.TotalCreditosGAAP, u.SaldoIniGAAP + (u.TotalDebitosGAAP - u.TotalCreditosGAAP) * u.Factor as SaldoFinGAAP, 
	u.SaldoIniIFRS, u.TotalDebitosIFRS, u.TotalCreditosIFRS, u.SaldoIniIFRS + (u.TotalDebitosIFRS - u.TotalCreditosIFRS) * u.Factor as SaldoFinIFRS,  
	u.DetallarCliente,
	SUBSTRING(u.CodCuenta,1,1) AS CodNiv1, SUBSTRING(u.CodCuenta,1,2) AS CodNiv2, SUBSTRING(u.CodCuenta,1,4) AS CodNiv3, SUBSTRING(u.CodCuenta,1,6) AS CodNiv4, SUBSTRING(u.CodCuenta,1,8) AS CodNiv5, SUBSTRING(u.CodCuenta,1,10) AS CodNiv6, SUBSTRING(u.CodCuenta,1,12) AS CodNiv7,
	Cta1.NomCuentaBase AS NomCta1, 
	Cta2.NomCuentaBase AS NomCta2, 
	Cta3.NomCuentaBase AS NomCta3, 
	Cta4.NomCuentaBase AS NomCta4, 
	Cta5.NomCuentaBase AS NomCta5, 
	Cta6.NomCuentaBase AS NomCta6, 
	Cta7.NomCuentaBase AS NomCta7,
    Cta1.NomCuentaEspejo AS NomCtaEsp1, 
	Cta2.NomCuentaEspejo AS NomCtaEsp2, 
	Cta3.NomCuentaEspejo AS NomCtaEsp3, 
	Cta4.NomCuentaEspejo AS NomCtaEsp4, 
	Cta5.NomCuentaEspejo AS NomCtaEsp5, 
	Cta6.NomCuentaEspejo AS NomCtaEsp6, 
	Cta7.NomCuentaEspejo AS NomCtaEsp7,
	Cta1.CodCuentaEspejo AS CodEsp1, 
	Cta2.CodCuentaEspejo AS CodEsp2, 
	Cta3.CodCuentaEspejo AS CodEsp3, 
	Cta4.CodCuentaEspejo AS CodEsp4, 
	Cta5.CodCuentaEspejo AS CodEsp5, 
	Cta6.CodCuentaEspejo AS CodEsp6, 
	Cta7.CodCuentaEspejo AS CodEsp7,
	isnull(Cta3.CodCuenta+', ' ,'')+isnull(Cta4.CodCuenta,'')  as CodCtaUgpp,
	isnull(Cta3.NomCuentaBase+', ' ,'')+isnull(Cta4.NomCuentaBase,'')  as NomCtaUgpp
	FROM
	(
		SELECT case when @EsAgrupIFRS=1 and len(CodCuentaIFRS)> 0 then COALESCE(CodCuentaIFRS,CodCuenta) else CodCuenta end CodCuenta, 
		NomCuenta, DetallarCliente, Factor, SenDet, IdCliente, SUM(SaldoIni) as SaldoIni, Sum(SaldoIniExt) as SaldoIniExt, SUM(Debito) as TotalDebitos, SUM(Credito) as TotalCreditos, SUM(DebitoExt) as TotalDebitosExt, SUM(CreditoExt) as TotalCreditosExt,max(isnull(CodCuentaIFRS,''))CodCuentaIFRS,
		SUM(DebitoGAAP) as TotalDebitosGAAP, SUM(CreditoGAAP) as TotalCreditosGAAP,SUM(DebitoIFRS) as TotalDebitosIFRS, SUM(CreditoIFRS) as TotalCreditosIFRS,SUM(SaldoIniGAAP) as SaldoIniGAAP,SUM(SaldoIniIFRS) as SaldoIniIFRS
		FROM 
		(
			SELECT * FROM tmpMov UNION ALL SELECT * FROM tmpSaldoIni 
		) t
		GROUP BY
		case when @EsAgrupIFRS=1 and len(CodCuentaIFRS)> 0 then COALESCE(CodCuentaIFRS,CodCuenta) else CodCuenta end, 
		NomCuenta, DetallarCliente, Factor, SenDet, IdCliente
		HAVING 
		NOT(SUM(SaldoIni)=0 And SUM(Debito)= 0 And SUM(Credito) = 0) 
) u	LEFT OUTER JOIN dbo.VClientes2 AS Cli ON u.IdCliente = Cli.IdCliente LEFT OUTER JOIN dbo.VClientesDir AS CliDir ON u.IdCliente = CliDir.IdCliente
	LEFT OUTER JOIN
	tempCuentas AS Cta1 ON SUBSTRING(u.CodCuenta,1,1) = Cta1.CodCuenta LEFT OUTER JOIN
	tempCuentas AS Cta2 ON SUBSTRING(u.CodCuenta,1,2) = Cta2.CodCuenta LEFT OUTER JOIN
	tempCuentas AS Cta3 ON SUBSTRING(u.CodCuenta,1,4) = Cta3.CodCuenta LEFT OUTER JOIN
	tempCuentas AS Cta4 ON SUBSTRING(u.CodCuenta,1,6) = Cta4.CodCuenta LEFT OUTER JOIN
	tempCuentas AS Cta5 ON SUBSTRING(u.CodCuenta,1,8) = Cta5.CodCuenta LEFT OUTER JOIN
	tempCuentas AS Cta6 ON SUBSTRING(u.CodCuenta,1,10) = Cta6.CodCuenta LEFT OUTER JOIN
	tempCuentas AS Cta7 ON SUBSTRING(u.CodCuenta,1,12) = Cta7.CodCuenta 
) q1
ORDER BY q1.CodCuenta, q1.[Cuenta_Tercero]
OPTION(RECOMPILE)
END
GO
PRINT N'Modificando Procedimiento [dbo].[GetDesprendibleNomina]...';


GO
ALTER PROCEDURE [dbo].[GetDesprendibleNomina]  
  
@IdEncab INT,  
@IdContrato NVARCHAR(MAX) = '',  
@IdEmpresa INT,
@EsNe BIT = 0
  
  
AS  
BEGIN  
  
DECLARE  
  
@EsCentroCosto BIT=0  
  
set @EsCentroCosto=(select ValParametro from Parametros where CodParametro=193)  
  
  
;with tmpChilds(IdEncabNomMov,CantidadChild)  
as  
(  
	SELECT mov.IdEncabNomMov,sum(case when item1.IdClasif=1 and item1.FactorProv=1 then Mov1.Cantidad else 0 end)Cantidad  
	FROM dbo.Nomina_Movimientos Mov INNER JOIN  
	dbo.Nomina_Movimientos Mov1 ON Mov1.IdEncabNomMovBase=Mov.IdEncabNomMov  LEFT JOIN  
	dbo.Nomina_Contratos Cont ON Mov.IdContrato = Cont.IdContrato INNER JOIN  
	dbo.Nomina_Items item ON Mov.IdItem=item.IdItem  INNER JOIN  
	dbo.Nomina_Items item1 ON Mov1.IdItem=item1.IdItem    
	where item.IdTipoAporte=1 and mov.IdEncab=@IdEncab   
	and Mov.IdContrato IN(select number from iter$simple_intlist_to_tbl(@IdContrato)) and item1.IdClasif=1  
	group by mov.IdEncabNomMov  
)  
  
select q1.*,  
cli.Identificacion+'-'+cli.Nombre1+' '+COALESCE(cli.Nombre2,'')+' '+COALESCE(cli.Apellido1,'')+' '+COALESCE(cli.Apellido2,'') NomCliente,NomCentroCosto,tipoIdent.DescripTipoIdent,tipoIdent.NomTipoIdent  
from
(  
	SELECT IdCLiente,NomCargo,IdTipo,Nomtipo,IdClasif,NomClasif,SUM(Valor) Valor,SUM(ValorReal) ValorReal,  
	CASE WHEN IdClasif NOT IN (5,6) THEN sum (Cantidad) ELSE (MAX(coalesce(ConfiDivParteSuperior,itemDivParteSuperior)/coalesce(ConfiDivParteInferior,itemDivParteInferior)))*100 END cantidad,  
	OcultarDesprendible,max(FactorProv)FactorProv,max (NomMovRetiro)NomMovRetiro,  
	case when max(IdClasifSistem)=1 then sum(ISNULL(CantidadChild,Cantidad)) else max(ISNULL(CantidadChild,Cantidad)) end as CantidadChild,   
	sum(case when ((@EsNe=0 AND IdClasifSistem=1 AND FactorProv=1) OR (@EsNe=1 AND IdClasifSistem=1 AND IdClasif<>24)) then Cantidad else 0 end) as CantidadLaborados,  
	max(ISNULL(CantidadChild,0))as CantidadLaboradosChild,  
	CASE WHEN @EsCentroCosto=1 THEN IdCentroCosto ELSE NULL END as IdCentroCosto, Max(NominaNota) NotaNomina, case when Max(SueldoActivo) > 0 then Max(SueldoActivo) else Max(SueldoInactivo) end Sueldo  
	from  
	(  
		select distinct mov.IdEncabNomMov, Mov.IdCLiente,NomCargo,Tipo.IdTipo,Tipo.Nomtipo,Clasif.IdClasif,Clasif.NomClasif,ValorHora,  
		case when PorcDesprendible is not null then ValorHora*PorcDesprendible*Cantidad else ValorUnit/cast(ValorUnitInferior as float)*Cantidad end Valor,  
		ValorUnit/cast(ValorUnitInferior as float)*Cantidad as ValorReal,  
		Confi.DivParteSuperior as ConfiDivParteSuperior,item.DivParteSuperior as itemDivParteSuperior,Confi.DivParteInferior as ConfiDivParteInferior,item.DivParteInferior as itemDivParteInferior,Cantidad,  
		item.OcultarDesprendible,FactorProv, NomMovRetiro,CantidadChild, item.IdClasif as IdClasifSistem, item.FactorGeneral,IdCentroCosto, nomnot.Nota as NominaNota, 
		case when Cont.EsActivo = 1 then Cont.SueldoIni else 0 end as SueldoActivo, case when Cont.EsActivo = 0 then Cont.SueldoIni else 0 end as SueldoInactivo   
		from dbo.Nomina_Movimientos Mov LEFT JOIN  
		dbo.Nomina_Contratos Cont ON  Mov.IdContrato = Cont.IdContrato LEFT JOIN  
		dbo.Nomina_Cargos Carg ON Cont.IdCargo=Carg.IdCargo INNER JOIN  
		dbo.Nomina_Items item ON Mov.IdItem=item.IdItem INNER JOIN  
		dbo.Nomina_Clasificacion Clasif ON (CASE WHEN @EsNe = 0 THEN item.IdClasifPrint ELSE COALESCE(item.IdClasifNe,item.IdClasifPrint) END)=Clasif.IdClasif INNER JOIN  
		dbo.Nomina_Tipos Tipo ON item.IdTipo=Tipo.IdTipo LEFT JOIN   
		dbo.Nomina_Configuracion Confi ON Mov.IdItem=Confi.IdItem and Cont.Idplantilla=Confi.IdPlantilla left join  
		dbo.VNominaValorHora VHora ON VHora.IdEncab=Mov.IdEncab and VHora.IdCliente=Mov.IdCliente LEFT JOIN  
		dbo.Nomina_Motivos_Retiro as NomMotRet ON Cont.IdMovRetiro= NomMotRet.IdMovRetiro  LEFT JOIN  
		tmpChilds on tmpChilds.IdEncabNomMov=Mov.IdEncabNomMov LEFT JOIN  
		dbo.Nomina_Notas nomnot ON Mov.IdEncab = nomnot.IdEncab AND Mov.IdCliente = nomnot.IdCliente   
		where IdTipoAporte=1 and mov.IdEncab=@IdEncab 
		and Mov.IdContrato IN(select number from iter$simple_intlist_to_tbl(@IdContrato))  
		and (COALESCE (Mov.FactorMov, 1) = 1) and coalesce(Cont.IdEmpresa,@IdEmpresa)=@IdEmpresa   
	)as q  
	group by IdCLiente,NomCargo,IdTipo,Nomtipo,IdClasif,NomClasif,OcultarDesprendible,  
	CASE WHEN @EsCentroCosto=1 THEN IdCentroCosto ELSE NULL END  
)as q1 INNER JOIN  
dbo.Clientes cli ON q1.IdCliente=cli.IdCliente INNER JOIN  
dbo.Clientes_TiposIdent tipoIdent on cli.IdTipoIdent=tipoIdent.IdTipoIdent  
 LEFT JOIN  
dbo.CentroCostos ccos ON q1.IdCentroCosto=ccos.IdCentroCosto
END
GO
PRINT N'Modificando Procedimiento [dbo].[GetDocumentosCierre]...';


GO
ALTER PROCEDURE [dbo].[GetDocumentosCierre]

@IdDocs NVARCHAR(1000),
@FechaFin datetime,
@IdSucursal int,
@IdVend int

AS
BEGIN

DECLARE
@tIdDocs NVARCHAR(1000),
@tFechaFin datetime,
@tIdSucursal int,
@tIdVend int

SET @tIdDocs = @IdDocs
SET @tFechaFin = @FechaFin
SET @tIdSucursal = @IdSucursal
SET @tIdVend = @IdVend

--Se ejecuto en GUSTAVO NEIRA OSORIO y en FERRETERIA en GOLD
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

select distinct IdEncab
into #tempEncabezados
FROM
(
	select distinct IdEncab
	from
	[dbo].[Encabezados_Cierres_Docs] as cierre inner join
	Encabezados_Mov as encMov on cierre.IdEncabMov=encMov.IdEncabMov

	UNION ALL 

	select distinct IdEncab
	from
	[dbo].[Encabezados_Cierres_Docs_Cont] as cierre inner join
	Encabezados_Cont as encCont on cierre.IdEncabCont=encCont.IdEncabCont
)AS q


alter table #tempEncabezados add primary key ([IdEncab])        

select VEnc.IdEncab,VEnc.Fecha,RTRIM(LTRIM(VEnc.CodDocumento + ' ' + ISNULL(IdPrefijo,''))) + ' ' + CAST(NumDocumento AS NVARCHAR(200)) AS DescripDoc,NumDocumento,
ISNULL(VEnc.Cantidad,0)Cantidad,ISNULL(SubIVA,0)SubIVA,ISNULL(SubTotal+SubIVA,ISNULL(Debito,0)) as Total, VEnc.CodDocumento,  VEnc.NomDocumento, EsMov, VEnc.NombreVend
from
#tempEncabezados as encMov
right join VEncabezados VEnc on encMov.IdEncab=VEnc.IdEncab INNER JOIN
Documentos Doc ON VEnc.IdDocumento = Doc.IdDocumento
where encMov.IdEncab IS NULL AND
VEnc.IdDocumento IN(select number from iter$simple_intlist_to_tbl(@tIdDocs)) AND
VEnc.Fecha<=@tFechaFin AND
(@tIdSucursal is null OR @tIdSucursal=VEnc.IdSucursal)
AND(isnull(SubTotal,0)>0 OR isnull(Debito,0)>0)
AND (@tIdVend IS NULL OR @tIdVend = VEnc.IdClienteVend)
OPTION(RECOMPILE)

END
GO
PRINT N'Modificando Procedimiento [dbo].[GetExistenciasPorProducto]...';


GO
ALTER PROCEDURE [dbo].[GetExistenciasPorProducto]
@FechaCorte NVARCHAR(10) = '',
@IdInventario INT,
@IdLote INT = NULL,
@NumSerial NVARCHAR(50) = '',
@IdBodega INT,
@IdEncabMov INT,
@IdTipo NVARCHAR(5) = '',
@SumExist NUMERIC(28,6) OUTPUT

AS
BEGIN

DECLARE

@FechaCorteTemp datetime,
@EsSerial bit=0

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON;	

SET @FechaCorteTemp = CAST(convert(datetime, @FechaCorte, 103) + ' 23:59:59' AS DATETIME)
SET @EsSerial = (SELECT EsSerialRequerido FROM Inventarios where IdInventario=@IdInventario)

IF @IdLote IS NULL
	
	IF @NumSerial IS NULL OR @NumSerial=''
		SELECT @SumExist=SUM(t.SumByDoc * Doc.FactorInventario)
		FROM
		(
			SELECT Enc.IdDocumento, SUM(Enc_Mov.Cantidad * Enc_Mov.FactorCantidad * Enc_Mov.FactorMovim) AS SumByDoc
			FROM 
			dbo.Encabezados AS Enc INNER JOIN
			dbo.Encabezados_Mov AS Enc_Mov ON Enc.IdEncab = Enc_Mov.IdEncab 
			WHERE     
			Enc.Fecha < @FechaCorteTemp AND
			IdInventario = @IdInventario AND
			IdBodega=@IdBodega AND
			Enc.EsAnulado=0 AND
			Enc_Mov.FactorMovim <> 0 AND
			Enc_Mov.IdLote IS NULL AND
			Enc_Mov.IdEncabMov<>@IdEncabMov AND  
			(Enc_Mov.NumSerial IS NULL OR Enc_Mov.NumSerial='' OR  @EsSerial=1)
			GROUP BY Enc.IdDocumento, Enc_Mov.IdInventario, Enc_Mov.IdBodega
		) t INNER JOIN dbo.Documentos AS Doc ON t.IdDocumento = Doc.IdDocumento
	ELSE
		SELECT @SumExist=SUM(t.SumByDoc * Doc.FactorInventario)
		FROM
		(
			SELECT Enc.IdDocumento, SUM(Enc_Mov.Cantidad * Enc_Mov.FactorCantidad * Enc_Mov.FactorMovim) AS SumByDoc
			FROM 
			dbo.Encabezados AS Enc INNER JOIN
			dbo.Encabezados_Mov AS Enc_Mov ON Enc.IdEncab = Enc_Mov.IdEncab
			WHERE     
			Enc.Fecha < @FechaCorteTemp AND
			IdInventario = @IdInventario AND
			IdBodega=@IdBodega AND
			Enc.EsAnulado=0 AND
			Enc_Mov.FactorMovim <> 0 AND
			Enc_Mov.NumSerial=@NumSerial AND coalesce(Enc_Mov.IdTipoReg,'')=@IdTipo AND
			Enc_Mov.IdLote IS NULL AND
			Enc_Mov.IdEncabMov<>@IdEncabMov
			GROUP BY Enc.IdDocumento, Enc_Mov.IdInventario,Enc_Mov.IdBodega,Enc_Mov.NumSerial
		) t INNER JOIN dbo.Documentos AS Doc ON t.IdDocumento = Doc.IdDocumento
	
ELSE
	IF @NumSerial IS NULL OR @NumSerial=''
		SELECT @SumExist=SUM(t.SumByDoc * Doc.FactorInventario)
		FROM
		(
			SELECT Enc.IdDocumento, SUM(Enc_Mov.Cantidad * Enc_Mov.FactorCantidad * Enc_Mov.FactorMovim) AS SumByDoc
			FROM 
			dbo.Encabezados AS Enc INNER JOIN
			dbo.Encabezados_Mov AS Enc_Mov ON Enc.IdEncab = Enc_Mov.IdEncab 
			WHERE     
			Enc.Fecha < @FechaCorteTemp AND
			IdInventario = @IdInventario AND
			IdBodega=@IdBodega AND
			Enc.EsAnulado=0 AND 
			Enc_Mov.FactorMovim <> 0 AND
			Enc_Mov.IdLote = @IdLote AND
			Enc_Mov.IdEncabMov<>@IdEncabMov AND  
			(Enc_Mov.NumSerial IS NULL OR Enc_Mov.NumSerial='' OR @EsSerial=1)
			GROUP BY Enc.IdDocumento, Enc_Mov.IdInventario,Enc_Mov.IdBodega,Enc_Mov.IdLote
		) t INNER JOIN dbo.Documentos AS Doc ON t.IdDocumento = Doc.IdDocumento


	ELSE
		SELECT @SumExist=SUM(t.SumByDoc * Doc.FactorInventario)
		FROM
		(
			SELECT Enc.IdDocumento, SUM(Enc_Mov.Cantidad * Enc_Mov.FactorCantidad * Enc_Mov.FactorMovim) AS SumByDoc
			FROM 
			dbo.Encabezados AS Enc INNER JOIN
			dbo.Encabezados_Mov AS Enc_Mov ON Enc.IdEncab = Enc_Mov.IdEncab INNER JOIN
			dbo.Documentos AS Doc ON Enc.IdDocumento = Doc.IdDocumento
			WHERE     
			Enc.Fecha < @FechaCorteTemp AND
			IdInventario = @IdInventario AND
			IdBodega=@IdBodega AND
			Enc.EsAnulado=0 AND 
			Enc_Mov.FactorMovim <> 0 AND
			Enc_Mov.IdLote = @IdLote AND
			Enc_Mov.NumSerial=@NumSerial AND coalesce(Enc_Mov.IdTipoReg,'')=@IdTipo AND
			Enc_Mov.IdEncabMov<>@IdEncabMov
			GROUP BY Enc.IdDocumento, Enc_Mov.IdInventario,Enc_Mov.IdBodega, Enc_Mov.IdLote,Enc_Mov.NumSerial+'-'+Enc_Mov.IdTipoReg
		) t INNER JOIN dbo.Documentos AS Doc ON t.IdDocumento = Doc.IdDocumento

		OPTION(RECOMPILE)
END
GO
PRINT N'Modificando Procedimiento [dbo].[GetInvKardexPorLote]...';


GO
ALTER PROCEDURE [dbo].[GetInvKardexPorLote]

@FechaCorte NVARCHAR(10) = '',
@Lote BIT=0,
@IdEmpresas NVARCHAR(1000) = '',
@IdInventario INT = null,
@IdBodega INT = null,
@IdLote INT = null,
@EsBodega BIT=1,
@EsSerial BIT=0,
@EsSinExist BIT=1,
@EsValidarMax BIT=0

AS
BEGIN


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON;	

DECLARE

@FechaCorteTemp datetime,
@EsLote BIT=0,
@IdEmp NVARCHAR(1000),
@IdInv INT = null,
@IdBod INT = null,
@IdLot INT = null,
@EsBod BIT,
@Serial BIT=0,
@SinExist BIT

SET @FechaCorteTemp = CAST(convert(datetime, @FechaCorte, 103) + ' 23:59:59' AS DATETIME)
SET @EsLote =@Lote
SET @IdEmp= @IdEmpresas
SET @IdInv =@IdInventario
SET @IdBod =@IdBodega
SET @IdLot =@IdLote
SET @EsBod =@EsBodega
SET @Serial =@EsSerial
SET @SinExist =@EsSinExist

IF LEN(@IdEmp)=0
	SELECT @IdEmp= COALESCE(@IdEmp + ', ', '') + CAST(IdEmpresa as nvarchar(50)) FROM empresas


        SELECT 
		isnull(Mov.IdInventario,VCru.IdInventario) AS IdInventario,
		isnull(Mov.IdBodega,VCru.IdBodega) AS IdBodega,
		isnull(Mov.IdLote,VCru.IdLote) AS IdLote,
		isnull(Mov.NumSerial,VCru.NumSerial) AS NumSerial,
		isnull(Mov.IdTipoReg,VCru.IdTipoReg) AS IdTipoReg,
		SUM(isnull(Mov.Cantidad*Mov.FactorCantidad*Mov.FactorMovim,SumCantidad)*Doc.FactorInventarioTeorico) AS ExistenciaTeo
		INTO #tmpSaldoTeo
		from dbo.VCruces_TodosDocs VCru 
		INNER JOIN Documentos Doc ON VCru.IdDocumento=Doc.IdDocumento LEFT JOIN
		Encabezados_Mov as Mov on Vcru.IdEncabMov=Mov.IdEncabMov2
		WHERE  
		VCru.Fecha <= @FechaCorteTemp AND 
		VCru.EsAnulado=0 AND
		ISNULL(VCru.IdEstado,0)<>-5 AND
		Doc.FactorInventarioTeorico<>0  AND

		(@IdInv IS NULL OR Mov.IdInventario = @IdInv) AND
		(@IdInv IS NULL OR VCru.IdInventario = @IdInv) 

     	GROUP BY   
		isnull(Mov.IdInventario,VCru.IdInventario),
		isnull(Mov.IdBodega,VCru.IdBodega),
		isnull(Mov.IdLote,VCru.IdLote),
		isnull(Mov.NumSerial,VCru.NumSerial),
		isnull(Mov.IdTipoReg,VCru.IdTipoReg)

		SELECT EncMov.IdInventario, 
		SUM(EncMov.Cantidad * EncMov.FactorCantidad * Doc.FactorInventario * EncMov.FactorMovim) AS Existencia,
		SUM(ISNULL(EncMov.Cantidad1,0) * Doc.FactorInventario * EncMov.FactorMovim) AS Existencia1,  
		MAX(EncMov.ValorTRM) AS ValorTRM,NumSerial,IdTipoReg,IdBodega,IdLote
		INTO #tmpSaldo
		FROM  
		dbo.Encabezados AS Enc INNER JOIN
		dbo.Encabezados_Mov AS EncMov ON Enc.IdEncab = EncMov.IdEncab INNER JOIN
		dbo.Documentos AS Doc ON Enc.IdDocumento = Doc.IdDocumento
		WHERE
		Doc.FactorInventario <> 0 AND 
		Enc.Fecha <= @FechaCorteTemp AND 
		EncMov.FactorMovim <> 0 AND
		Enc.EsAnulado=0 AND
		(@IdInv IS NULL OR EncMov.IdInventario = @IdInv) AND
		(@IdBod IS NULL OR EncMov.IdBodega = @IdBod) AND
		(@IdLot IS NULL OR EncMov.IdLote = @IdLot) AND
		Enc.IdEmpresa IN(select number from iter$simple_intlist_to_tbl(@IdEmp)) 
		GROUP BY  EncMov.IdInventario,EncMov.IdBodega,EncMov.IdLote,EncMov.NumSerial,EncMov.IdTipoReg

		--Por Lote, serial y Bodega
		SELECT Saldo.IdInventario,Existencia,Existencia1,ValorTRM,Saldo.NumSerial,Saldo.IdTipoReg,Saldo.IdBodega,Saldo.IdLote
		INTO #tmpFin
		from #tmpSaldo as Saldo
		where ISNULL(@EsBod,0)=1 and ISNULL(@Serial,0)=1 and ISNULL(@EsLote,0)=1
				
		union all 

		--Por Lote y serial
		SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,Saldo.NumSerial,Saldo.IdTipoReg,null as IdBodega,Saldo.IdLote
		from #tmpSaldo as Saldo
		where ISNULL(@Serial,0)=1 and ISNULL(@EsLote,0)=1 and ISNULL(@EsBod,0)=0
		group by Saldo.IdInventario,Saldo.IdLote,Saldo.NumSerial,Saldo.IdTipoReg

		union all 

		--Por Lote y Bodega
		SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,null as NumSerial, null as IdTipoReg,Saldo.IdBodega,Saldo.IdLote
		from #tmpSaldo  Saldo
		where ISNULL(@EsBod,0)=1 and ISNULL(@Serial,0)=0 and ISNULL(@EsLote,0)=1
		group by Saldo.IdInventario,Saldo.IdBodega,Saldo.IdLote

		union all 

		--Solo por lote 
		SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,null as NumSerial, null as IdTipoReg, null as IdBodega,Saldo.IdLote
		from #tmpSaldo  Saldo
		where ISNULL(@EsBod,0)=0 and ISNULL(@Serial,0)=0 and ISNULL(@EsLote,0)=1
		group by Saldo.IdInventario,Saldo.IdLote

		union all 

		--Por serial y Bodega
		SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,Saldo.NumSerial,Saldo.IdTipoReg,Saldo.IdBodega,null as IdLote
		from #tmpSaldo  Saldo
		where ISNULL(@EsBod,0)=1 and ISNULL(@Serial,0)=1 and ISNULL(@EsLote,0)=0
		group by Saldo.IdInventario,Saldo.IdBodega,Saldo.NumSerial,Saldo.IdTipoReg

		union all 

		--Solo por serial
		SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,Saldo.NumSerial,Saldo.IdTipoReg,null as IdBodega,null as IdLote
		from #tmpSaldo  Saldo
		where ISNULL(@EsBod,0)=0 and ISNULL(@Serial,0)=1 and ISNULL(@EsLote,0)=0
		group by Saldo.IdInventario,Saldo.NumSerial,Saldo.IdTipoReg

		union all 

		--Solo por Bodega
		SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,null as NumSerial, null as IdTipoReg,Saldo.IdBodega,null as IdLote
		from #tmpSaldo  Saldo
		where ISNULL(@EsBod,0)=1 and ISNULL(@Serial,0)=0 and ISNULL(@EsLote,0)=0
		group by Saldo.IdInventario,Saldo.IdBodega

		union all 

		--Solo Inventario
		SELECT Saldo.IdInventario,SUM(Existencia)Existencia,SUM(Existencia1)Existencia1,MAX(ValorTRM)ValorTRM,null as NumSerial, null as IdTipoReg, null as IdBodega,null as IdLote
		from #tmpSaldo  Saldo
		where ISNULL(@EsBod,0)=0 and ISNULL(@Serial,0)=0 and ISNULL(@EsLote,0)=0
		group by Saldo.IdInventario

		select saldo.IdInventario,saldo.Existencia,saldo.Existencia1, saldo.ValorTRM,saldo.NumSerial,saldo.IdTipoReg,saldo.IdBodega,saldo.IdLote,sum(isnull(ExistenciaTeo,0))ExistenciaTeo,
		max(isnull(maximoGeneral.CantMaxima,0)) CantMaxima,max(isnull(maximoGeneral.CantMinima,0))CantMinima,max(isnull(maximoGeneral.PuntoReorden,0))PuntoReorden,
		max(cast(isnull(maximoGeneral.EsOk,0) as int)) EsOk,max(cast(isnull(maximoGeneral.EsError,0) as int)) EsError,max(cast(isnull(maximoGeneral.EsWarning,0) as int)) EsWarning, 
		max(isnull(maximoGeneral.TimeIni,0)) TimeIni,max(isnull(maximoGeneral.TimeFinal,0)) TimeFinal,max(isnull(maximoGeneral.TimeReminder,0)) TimeReminder,
		max(isnull(maximoGeneral.IdMaximo,0)) IdMaximo, MAX(ISNULL(IdUsuarios,''))IdUsuarios,MAX(ISNULL(IdRoles,''))IdRoles
		INTO #tmpTotalFin
		from #tmpFin as saldo 
		left join 
		#tmpSaldoTeo as teo on saldo.IdInventario=teo.IdInventario and (saldo.IdBodega=teo.IdBodega OR ISNULL(@EsBod,0)=0)and
		(saldo.IdLote=teo.IdLote OR ISNULL(@EsLote,0)=0) and (saldo.NumSerial=teo.NumSerial and saldo.IdTipoReg=teo.IdTipoReg OR  ISNULL(@Serial,0)=0)
			left join 
			VMaximosYMinimos as maximoGeneral on saldo.IdInventario=maximoGeneral.IdInventario and ISNULL(@EsBod,0)=maximoGeneral.EsBodega and isnull(saldo.IdBodega,0)=COALESCE(maximoGeneral.IdBodega,saldo.IdBodega,0)
			and ISNULL(@EsLote,0)=maximoGeneral.EsLote and ISNULL(saldo.IdLote,0)=COALESCE(maximoGeneral.IdLote,saldo.IdLote,0) and ISNULL(@Serial,0)=maximoGeneral.EsSerial and ISNULL(saldo.NumSerial,0)=COALESCE(maximoGeneral.NumSerial,saldo.NumSerial,0)
		and @EsValidarMax=1
		group by saldo.IdInventario,saldo.Existencia,saldo.Existencia1, saldo.ValorTRM,saldo.NumSerial,saldo.IdTipoReg,saldo.IdBodega,saldo.IdLote

	SELECT q1.*,Existencia1 AS ExistenciaUni1,costPromInv.CostoProm, CASE WHEN q1.ValorTRM = 0 THEN 0 ELSE costPromInv.CostoProm / q1.ValorTRM END AS CostoPromExt
	FROM
	(
		SELECT EncMov.IdInventario,EncMov.IdLote AS IdLote, Inv_L.CodLote AS CodLote, Inv_L.NomLote AS Nomlote, Inv.IdUnidad, 
		Existencia,
		Existencia1,
		ExistenciaTeo,
		Inv.CodInventario,  (EncMov.ValorTRM) AS ValorTRM, Inv_L.FechaVence AS FechaVence,
		Inv.NomInventario,
		ISNULL(EncMov.CantMaxima,0) CantMaxima,
		ISNULL(EncMov.CantMinima,0) CantMinima,
		Inv_Bod.NomBodega,
		Agr1.NomAgrup1,Agr2.NomAgrup2,Inv.CodInventario + ' ' + Inv.NomInventario AS NomInvCompleto,Inv_Uni.NomUnidad,Inv_Uni.IdTipoUnidad,
		ISNULL(Inv.FactorEscala,Inv_Uni.FactorEscala)AS FactorEscala,
		ISNULL(Inv.FactorEscala1,ISNULL(Inv_Uni1.FactorEscala,0))AS FactorEscala1,
		ISNULL(EncMov.PuntoReorden,0) PuntoReorden,
		CAST(CAST(inv.EsActivo AS int)AS bit) EsActivo,
		NumSerial,
		motor.NomAutomotor,motor.NumPlaca +'-'+marca.NomMarca AS Automotor,
		(talla.NomTalla) AS NomTalla,
		(talla.Agrup1) AS AgrupTalla1,
		(talla.Agrup2) AS AgrupTalla2,
		(talla.Agrup3) AS AgrupTalla3,
		Existencia + ExistenciaTeo as TotalExistencia,
		CAST(ISNULL(EncMov.EsOk,0)AS BIT) EsOk,
		CAST(ISNULL(EncMov.EsError,0) AS BIT)  EsError,
		CAST(ISNULL(EncMov.EsWarning,0) AS BIT) EsWarning,

		EncMov.TimeIni,
		EncMov.TimeFinal ,
		EncMov.TimeReminder,
		IdMaximo ,EncMov.IdBodega,IdUsuarios,IdRoles
			
		FROM  
		#tmpTotalFin as EncMov INNER JOIN
		dbo.Inventarios AS Inv ON EncMov.IdInventario = Inv.IdInventario LEFT OUTER JOIN
		dbo.Inventarios_Agrup1 AS Agr1 ON Agr1.IdAgrup1=Inv.IdAgrup1 LEFT OUTER JOIN
		dbo.Inventarios_Agrup2 AS Agr2 ON Agr2.IdAgrup2=Inv.IdAgrup2 LEFT OUTER JOIN
		dbo.Inventarios_Lotes AS Inv_L ON EncMov.IdLote = Inv_L.IdLote  LEFT OUTER JOIN
		dbo.Inventarios_Bodegas AS Inv_Bod ON EncMov.IdBodega = Inv_Bod.IdBodega LEFT OUTER JOIN
		dbo.Inventarios_Unidades AS Inv_Uni ON Inv.IdUnidad = Inv_Uni.IdUnidad LEFT OUTER JOIN
		dbo.Inventarios_Unidades AS Inv_Uni1 ON Inv.IdUnidad1 = Inv_Uni1.IdUnidad LEFT JOIN
		[dbo].[Reparacion_Automotores] AS motor  ON EncMov.NumSerial = motor.IdAutomotor and EncMov.IdTipoReg=1 LEFT JOIN
		dbo.Inventarios_Colores_Tallas AS Talla ON EncMov.NumSerial = Talla.CodTalla and EncMov.IdTipoReg=2 LEFT JOIN
		[dbo].[Reparacion_Marcas] AS marca ON marca.IdMarca=motor.IdMarca
	    WHERE
		Inv.IdTipoInv=1  AND (@SinExist=1 or (Existencia<>0 or ExistenciaTeo<>0))
	)
	q1 LEFT JOIN CostoPromedioPorInventario(@FechaCorte) AS costPromInv ON costPromInv.IdInventario=q1.IdInventario


	OPTION(RECOMPILE); 

END
GO
PRINT N'Modificando Procedimiento [dbo].[GetListaPrecios]...';


GO

ALTER PROCEDURE GetListaPrecios
	@IdAgrup1 int = 0,
	@IdAgrup2 int = 0,
	@InvTipos nvarchar(10)='1',
	@NomInventario nvarchar(500)='',
	@EsCodigo bit=0
As
BEGIN
	SET NOCOUNT ON;	

	select i.CodigoBarras,i.IdInventario,i.CodInventario, i.NomInventario, i.ValorIva as Iva,
	L1.Precio AS Lista_1,
	L2.Precio AS Lista_2,
	L3.Precio AS Lista_3,
	L4.Precio AS Lista_4,
	L5.Precio AS Lista_5,
	L6.Precio AS Lista_6,
	L7.Precio AS Lista_7,
	L8.Precio AS Lista_8,
	L9.Precio AS Lista_9,
	L10.Precio AS Lista_10,
	L11.Precio AS Lista_11,
	L12.Precio AS Lista_12,
	L13.Precio AS Lista_13,
	L14.Precio AS Lista_14,
	L15.Precio AS Lista_15,
	L16.Precio AS Lista_16,
	L17.Precio AS Lista_17,
	L18.Precio AS Lista_18,
	L19.Precio AS Lista_19,
	L20.Precio AS Lista_20,
	L21.Precio AS Lista_21,
	L22.Precio AS Lista_22,
	L23.Precio AS Lista_23,
	L24.Precio AS Lista_24,
	L25.Precio AS Lista_25,
	L26.Precio AS Lista_26,
	L27.Precio AS Lista_27,
	L28.Precio AS Lista_28,
	L29.Precio AS Lista_29,
	L30.Precio AS Lista_30,
	L31.Precio AS Lista_31,

	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L1.EsIvaIncluido,0) = 1 THEN L1.Precio ELSE L1.Precio + L1.Precio * i.ValorIva/100 END AS ListaIva_1,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L2.EsIvaIncluido,0) = 1 THEN L2.Precio ELSE L2.Precio + L2.Precio * i.ValorIva/100 END AS ListaIva_2,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L3.EsIvaIncluido,0) = 1 THEN L3.Precio ELSE L3.Precio + L3.Precio * i.ValorIva/100 END AS ListaIva_3,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L4.EsIvaIncluido,0) = 1 THEN L4.Precio ELSE L4.Precio + L4.Precio * i.ValorIva/100 END AS ListaIva_4,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L5.EsIvaIncluido,0) = 1 THEN L5.Precio ELSE L5.Precio + L5.Precio * i.ValorIva/100 END AS ListaIva_5,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L6.EsIvaIncluido,0) = 1 THEN L6.Precio ELSE L6.Precio + L6.Precio * i.ValorIva/100 END AS ListaIva_6,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L7.EsIvaIncluido,0) = 1 THEN L7.Precio ELSE L7.Precio + L7.Precio * i.ValorIva/100 END AS ListaIva_7,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L8.EsIvaIncluido,0) = 1 THEN L8.Precio ELSE L8.Precio + L8.Precio * i.ValorIva/100 END AS ListaIva_8,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L9.EsIvaIncluido,0) = 1 THEN L9.Precio ELSE L9.Precio + L9.Precio * i.ValorIva/100 END AS ListaIva_9,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L10.EsIvaIncluido,0) = 1 THEN L10.Precio ELSE L10.Precio + L10.Precio * i.ValorIva/100 END AS ListaIva_10,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L11.EsIvaIncluido,0) = 1 THEN L11.Precio ELSE L11.Precio + L11.Precio * i.ValorIva/100 END AS ListaIva_11,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L12.EsIvaIncluido,0) = 1 THEN L12.Precio ELSE L12.Precio + L12.Precio * i.ValorIva/100 END AS ListaIva_12,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L13.EsIvaIncluido,0) = 1 THEN L13.Precio ELSE L13.Precio + L13.Precio * i.ValorIva/100 END AS ListaIva_13,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L14.EsIvaIncluido,0) = 1 THEN L14.Precio ELSE L14.Precio + L14.Precio * i.ValorIva/100 END AS ListaIva_14,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L15.EsIvaIncluido,0) = 1 THEN L15.Precio ELSE L15.Precio + L15.Precio * i.ValorIva/100 END AS ListaIva_15,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L16.EsIvaIncluido,0) = 1 THEN L16.Precio ELSE L16.Precio + L16.Precio * i.ValorIva/100 END AS ListaIva_16,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L17.EsIvaIncluido,0) = 1 THEN L17.Precio ELSE L17.Precio + L17.Precio * i.ValorIva/100 END AS ListaIva_17,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L18.EsIvaIncluido,0) = 1 THEN L18.Precio ELSE L18.Precio + L18.Precio * i.ValorIva/100 END AS ListaIva_18,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L19.EsIvaIncluido,0) = 1 THEN L19.Precio ELSE L19.Precio + L19.Precio * i.ValorIva/100 END AS ListaIva_19,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L20.EsIvaIncluido,0) = 1 THEN L20.Precio ELSE L20.Precio + L20.Precio * i.ValorIva/100 END AS ListaIva_20,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L21.EsIvaIncluido,0) = 1 THEN L21.Precio ELSE L21.Precio + L21.Precio * i.ValorIva/100 END AS ListaIva_21,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L22.EsIvaIncluido,0) = 1 THEN L22.Precio ELSE L22.Precio + L22.Precio * i.ValorIva/100 END AS ListaIva_22,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L23.EsIvaIncluido,0) = 1 THEN L23.Precio ELSE L23.Precio + L23.Precio * i.ValorIva/100 END AS ListaIva_23,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L24.EsIvaIncluido,0) = 1 THEN L24.Precio ELSE L24.Precio + L24.Precio * i.ValorIva/100 END AS ListaIva_24,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L25.EsIvaIncluido,0) = 1 THEN L25.Precio ELSE L25.Precio + L25.Precio * i.ValorIva/100 END AS ListaIva_25,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L26.EsIvaIncluido,0) = 1 THEN L26.Precio ELSE L26.Precio + L26.Precio * i.ValorIva/100 END AS ListaIva_26,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L27.EsIvaIncluido,0) = 1 THEN L27.Precio ELSE L27.Precio + L27.Precio * i.ValorIva/100 END AS ListaIva_27,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L28.EsIvaIncluido,0) = 1 THEN L28.Precio ELSE L28.Precio + L28.Precio * i.ValorIva/100 END AS ListaIva_28,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L29.EsIvaIncluido,0) = 1 THEN L29.Precio ELSE L29.Precio + L29.Precio * i.ValorIva/100 END AS ListaIva_29,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L30.EsIvaIncluido,0) = 1 THEN L30.Precio ELSE L30.Precio + L30.Precio * i.ValorIva/100 END AS ListaIva_30,
	CASE WHEN COALESCE(cast(i.EsIvaIncluido as int)*L31.EsIvaIncluido,0) = 1 THEN L31.Precio ELSE L31.Precio + L31.Precio * i.ValorIva/100 END AS ListaIva_31,

	L1.PrecioExt AS ListaExt_1,
	L2.PrecioExt AS ListaExt_2,
	L3.PrecioExt AS ListaExt_3,
	L4.PrecioExt AS ListaExt_4,
	L5.PrecioExt AS ListaExt_5,
	L6.PrecioExt AS ListaExt_6,
	L7.PrecioExt AS ListaExt_7,
	L8.PrecioExt AS ListaExt_8,
	L9.PrecioExt AS ListaExt_9,
	L10.PrecioExt AS ListaExt_10,
	L11.PrecioExt AS ListaExt_11,
	L12.PrecioExt AS ListaExt_12,
	L13.PrecioExt AS ListaExt_13,
	L14.PrecioExt AS ListaExt_14,
	L15.PrecioExt AS ListaExt_15,
	L16.PrecioExt AS ListaExt_16,
	L17.PrecioExt AS ListaExt_17,
	L18.PrecioExt AS ListaExt_18,
	L19.PrecioExt AS ListaExt_19,
	L20.PrecioExt AS ListaExt_20,
	L21.PrecioExt AS ListaExt_21,
	L22.PrecioExt AS ListaExt_22,
	L23.PrecioExt AS ListaExt_23,
	L24.PrecioExt AS ListaExt_24,
	L25.PrecioExt AS ListaExt_25,
	L26.PrecioExt AS ListaExt_26,
	L27.PrecioExt AS ListaExt_27,
	L28.PrecioExt AS ListaExt_28,
	L29.PrecioExt AS ListaExt_29,
	L30.PrecioExt AS ListaExt_30,
	L31.PrecioExt AS ListaExt_31

	FROM 
	Inventarios i
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 1) L1 ON i.IdInventario = L1.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 2) L2 ON i.IdInventario = L2.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 3) L3 ON i.IdInventario = L3.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 4) L4 ON i.IdInventario = L4.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 5) L5 ON i.IdInventario = L5.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 6) L6 ON i.IdInventario = L6.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 7) L7 ON i.IdInventario = L7.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 8) L8 ON i.IdInventario = L8.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 9) L9 ON i.IdInventario = L9.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 10) L10 ON i.IdInventario = L10.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 11) L11 ON i.IdInventario = L11.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 12) L12 ON i.IdInventario = L12.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 13) L13 ON i.IdInventario = L13.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 14) L14 ON i.IdInventario = L14.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 15) L15 ON i.IdInventario = L15.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 16) L16 ON i.IdInventario = L16.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 17) L17 ON i.IdInventario = L17.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 18) L18 ON i.IdInventario = L18.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 19) L19 ON i.IdInventario = L19.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 20) L20 ON i.IdInventario = L20.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 21) L21 ON i.IdInventario = L21.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 22) L22 ON i.IdInventario = L22.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 23) L23 ON i.IdInventario = L23.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 24) L24 ON i.IdInventario = L24.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 25) L25 ON i.IdInventario = L25.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 26) L26 ON i.IdInventario = L26.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 27) L27 ON i.IdInventario = L27.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 28) L28 ON i.IdInventario = L28.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 29) L29 ON i.IdInventario = L29.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 30) L30 ON i.IdInventario = L30.IdInventario
	LEFT JOIN (SELECT p.IdInventario, cast(l.EsIvaIncluido as int) as EsIvaIncluido, p.Precio, p.PrecioExt FROM Inventarios_Precios p INNER JOIN Inventarios_Listas l ON l.IdLista = p.IdLista WHERE p.IdLista = 31) L31 ON i.IdInventario = L31.IdInventario
	WHERE
	(@IdAgrup1 = 0 OR i.IdAgrup1 = @IdAgrup1) AND
	(@IdAgrup2 = 0 OR i.IdAgrup2 = @IdAgrup2) AND
	(i.IdTipoInv IN(select number from iter$simple_intlist_to_tbl(@InvTipos))) AND
	(
	   (@EsCodigo=0 AND (LEN(@NomInventario)=0 OR LOWER(i.CodInventario + '-' + i.NomInventario) LIKE '%'+ @NomInventario + '%')) OR
	   (@EsCodigo=1 AND (LEN(@NomInventario)=0 OR LOWER(ISNULL(i.CodigoBarras,'') + '-' + i.CodInventario + '-' + i.NomInventario) LIKE '%'+ @NomInventario + '%')) 
	)
	OPTION (RECOMPILE, MERGE JOIN)	

END
GO
PRINT N'Modificando Procedimiento [dbo].[GetPagoSeguridadSocial]...';


GO
ALTER PROCEDURE [dbo].[GetPagoSeguridadSocial]

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

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, 
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, 
	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, 
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, 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, 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, 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, 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, SUM(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
    
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
	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
	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
order by q.IdContrato
END
GO
PRINT N'Modificando Procedimiento [dbo].[GetVEncabezados]...';


GO
ALTER PROCEDURE [dbo].[GetVEncabezados]
	-- Add the parameters for the stored procedure here
	@IdEmpresa INT,
	@IdDocumento INT,
	@NumDocumento INT,
	@IdCliente1 INT,
	@FechaIni DATETIME,
	@FechaFin DATETIME,
    @IdSucursal INT

AS
BEGIN

SET NOCOUNT ON;	
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE

@IdEmp INT,
@IdDoc INT,
@NumDoc INT,
@IdCli1 INT,
@FechaI DATETIME,
@FechaF DATETIME,
@IdSuc INT



SET @IdEmp=@IdEmpresa
SET @IdDoc=@IdDocumento
SET @NumDoc=@NumDocumento
SET @IdCli1=@IdCliente1
SET @FechaI=@FechaIni
SET @FechaF=@FechaFin
SET @IdSuc=@IdSucursal


	select q.IdEncab, SUM(SubTotal) AS SubTotal, SUM(SubTotalSinDcto) AS SubTotalSinDcto, SUM(ValorDcto) AS ValorDcto, SUM(SubIVA) AS SubIVA, SUM((SubTotal + SubIVA) 
                         + (FactorImpoConsPorc * SubTotal / 100 + FactorImpoConsValor * CantConFactor) * ValorImptoConsumo) AS TotDoc, MAX(ValorIva) AS IVA, SUM(SubTotExento) AS SubTotExento, SUM(SubTotGravado) 
                         AS SubTotGravado, SUM(CantConFactor) AS Cantidad,SUM(ValorPuntos)AS ValorPuntos
    INTO #tempMovimientos
 	from
	(  
		SELECT  EncMov.IdEncabMov, EncMov.IdEncab, EncMov.IdInventario, EncMov.IdBodega, EncMov.IdLote, EncMov.Cantidad, EncMov.Cantidad * EncMov.FactorCantidad AS CantConFactor, EncMov.CantidadFisica, 
								EncMov.IdUnidad, EncMov.FactorCantidad, EncMov.FactorMovim, EncMov.ValorUnitario, EncMov.ValorUnitarioExt, EncMov.ValorIva, EncMov.TipoImptoConsumo, isnull (EncMov.ValorImptoConsumo, 0) 
								AS ValorImptoConsumo, EncMov.TotalMov, EncMov.IdCliente, EncMov.Vence, EncMov.IdCentroCosto, EncMov.Dcto, EncMov.EsIvaAlCosto, EncMov.ValorUnitario * EncMov.Dcto / 100 AS ValorDctoUnit, 
								(EncMov.Cantidad * EncMov.FactorCantidad * EncMov.ValorUnitario) * (1 - EncMov.Dcto / 100) AS SubTotal, EncMov.Cantidad * EncMov.FactorCantidad * EncMov.ValorUnitario AS SubTotalSinDcto, 
								EncMov.Cantidad * EncMov.FactorCantidad * EncMov.ValorUnitario * EncMov.Dcto / 100 AS ValorDcto, (EncMov.Cantidad * EncMov.FactorCantidad * EncMov.ValorUnitario) * (1 - EncMov.Dcto / 100) 
								* EncMov.ValorIva / 100 AS SubIVA, CASE WHEN EncMov.TipoImptoConsumo = 0 THEN 1 ELSE 0 END AS FactorImpoConsPorc, 
								CASE WHEN EncMov.TipoImptoConsumo = 1 THEN 1 ELSE 0 END AS FactorImpoConsValor, EncMov.ValorTRM, EncMov.IdMoneda, 
								CASE WHEN EncMov.ValorIva = 0 THEN (EncMov.Cantidad * EncMov.FactorCantidad * EncMov.ValorUnitario) * (1 - EncMov.Dcto / 100) ELSE 0 END AS SubTotExento, 
								CASE WHEN EncMov.ValorIva > 0 THEN (EncMov.Cantidad * EncMov.FactorCantidad * EncMov.ValorUnitario) * (1 - EncMov.Dcto / 100) ELSE 0 END AS SubTotGravado,
								ValorPuntos
		FROM   
		dbo.Encabezados_Mov AS EncMov INNER JOIN dbo.Encabezados AS Enc ON EncMov.IdEncab = Enc.IdEncab
		WHERE        
		(@IdEmp is null or enc.IdEmpresa=@IdEmp) AND
		(@IdDoc is null or enc.IdDocumento=@IdDoc) AND
		(@NumDoc is null or enc.NumDocumento=@NumDoc) AND
		(@IdCli1 is null or enc.IdCliente1=@IdCli1) AND
		(@FechaI is null or enc.Fecha>=@FechaI) AND
		(@FechaF is null or enc.Fecha<=@FechaF) AND
		(@IdSuc is null or enc.IdSucursal=@IdSuc) AND
		(EncMov.EsVisible = CASE WHEN Enc.FinanDebe = 1 AND EncMov.FinanNumCuotas >= 0 THEN 0 ELSE 1 END)
	)as q
	group by q.IdEncab

	--Crear primary key en tabla temporal
    alter table #tempMovimientos add primary key ([IdEncab])

	SELECT  IdEncab, SUM(Credito) AS Credito, SUM(Debito) AS Debito,SUM(Normal) AS SumNormal, SUM(Tesoreria) AS SumTesoreria, SUM(ReteFuente) AS SumReteFuente, SUM(Iva) AS SumIva, SUM(ReteIva) AS SumReteIva, SUM(ReteIca) AS SumReteIca, 
                        SUM(ReteIvaRegimenComun) AS SumReteIvaRegimenComun, SUM(ReteIvaRegimenSimplificadoD) AS SumReteIvaRegimenSimplificadoD, SUM(ReteivaRSC) AS SumReteIvaRSC, SUM(ImpuestoalConsumo) 
                        AS SumImpuestoalConsumo, SUM(DiferidosPasivo) AS SumDiferidosPasivo, SUM(ReteIvaNoDomiciliado) AS SumReteIvaNoDomiciliado, SUM(CuentasPorPagar) AS SumCuentasPorPagar, 
                        SUM(CuentasPorCobrar) AS SumCuentasPorCobrar, SUM(ReteFuenteCree) AS SumReteFuenteCree, SUM(ComisionTarjetas) AS SumComisionTarjetas, SUM(AutoReteCreeC) AS SumAutoReteCreeC, 
                        SUM(AutoReteCreeD) AS SumAutoReteCreeD
    INTO #tempCont
	FROM
	(
		SELECT  encCont.IdEncab, encCont.Credito AS Credito, encCont.Debito AS Debito,
		 Cuen.IdGrupoCuenta, CASE WHEN Cuen.IdGrupoCuenta = 1 THEN encCont.Credito + encCont.Debito ELSE 0 END AS Normal, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 2 THEN encCont.Credito + encCont.Debito ELSE 0 END AS Tesoreria, CASE WHEN Cuen.IdGrupoCuenta = 4 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteFuente,
                                                    CASE WHEN Cuen.IdGrupoCuenta = 5 THEN encCont.Credito + encCont.Debito ELSE 0 END AS Iva, CASE WHEN Cuen.IdGrupoCuenta = 6 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteIva, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 7 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteIca, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 8 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteIvaRegimenComun, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 9 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteIvaRegimenSimplificadoD, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 10 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteivaRSC, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 11 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ImpuestoalConsumo, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 12 THEN encCont.Credito + encCont.Debito ELSE 0 END AS DiferidosPasivo, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 13 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteIvaNoDomiciliado, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 20 THEN encCont.Credito + encCont.Debito ELSE 0 END AS CuentasPorPagar, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 21 THEN encCont.Credito + encCont.Debito ELSE 0 END AS CuentasPorCobrar, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 30 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ReteFuenteCree, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 31 THEN encCont.Credito + encCont.Debito ELSE 0 END AS ComisionTarjetas, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 33 THEN encCont.Credito + encCont.Debito ELSE 0 END AS AutoReteCreeC, 
                                                    CASE WHEN Cuen.IdGrupoCuenta = 34 THEN encCont.Credito + encCont.Debito ELSE 0 END AS AutoReteCreeD
		FROM            dbo.Encabezados_Cont as encCont INNER JOIN
								 dbo.Cuentas AS Cuen ON encCont.IdCuenta =Cuen.IdCuenta LEFT JOIN
								 dbo.Cuentas_Grupos ON Cuen.IdGrupoCuenta = dbo.Cuentas_Grupos.IdGrupoCuenta INNER JOIN
								 dbo.Encabezados Enc on encCont.IdEncab=Enc.IdEncab
		WHERE
		(@IdEmp is null or enc.IdEmpresa=@IdEmp) AND
		(@IdDoc is null or enc.IdDocumento=@IdDoc) AND
		(@NumDoc is null or enc.NumDocumento=@NumDoc) AND
		(@IdCli1 is null or enc.IdCliente1=@IdCli1) AND
		(@FechaI is null or enc.Fecha>=@FechaI) AND
		(@FechaF is null or enc.Fecha<=@FechaF) AND
		(@IdSuc is null or enc.IdSucursal=@IdSuc) 
	)AS q
	GROUP BY q.IdEncab

	alter table #tempCont add primary key ([IdEncab])

	SELECT        Enc.IdEncab, Enc.IdEmpresa, Enc.IdDocumento, Enc.IdPrefijo, pre.NomPrefijo, Enc.NumDocumento, Enc.Fecha, Enc.IdCliente1, Enc.IdCliente2, Enc.Nota, Enc.IdFormaPago, FP.NomFormaPago, 
                         dbo.Documentos.NomDocumento, Emp.EmpresaNombre, COALESCE (Cli1.Nombre1, N'') + N' ' + COALESCE (Cli1.Nombre2, N'') + N' ' + COALESCE (Cli1.Apellido1, N'') + N' ' + COALESCE (Cli1.Apellido2, N'') 
                         AS NombresCliente1, COALESCE (Cli2.Nombre1, N'') + N' ' + COALESCE (Cli2.Nombre2, N'') + N' ' + COALESCE (Cli2.Apellido1, N'') + N' ' + COALESCE (Cli2.Apellido2, N'') AS NombresCliente2, 
                         Enc.IdCliente1_IdDir, dbo.Clientes_Direcciones.Direccion, dbo.Monedas.NomMoneda1 AS NomMoneda, Enc.Vence, Reparacion_Automotores_1.NumPlaca, Reparacion_Automotores_1.NumChasis, 
                         Enc.IdAutomotor, Cli1.Identificacion, Emp.EmpresaNit, dbo.Clientes_Direcciones.Telefonos, Enc.NomPc, Enc.NumDocumentoExt, dbo.Ciudades.NomCiudad, Reparacion_Automotores_1.NumMotor, 
                         Reparacion_Automotores_1.Kilometraje, Reparacion_Automotores_1.Modelo, Reparacion_Automotores_1.Color, dbo.Reparacion_Marcas.NomMarca, Emp.EmpresaDV, Emp.EmpresaDireccion, 
                         Emp.EmpresaTelefono, Emp.EmpresaCorreoElect, COALESCE (Emp.EmpresaNit, N'') + '' + COALESCE ('-' + CAST(Emp.EmpresaDV AS varchar), N' ') AS NIT, dbo.Clientes_Contactos.Nombres AS NombresContacto, 
                         dbo.Clientes_Contactos.Telefono AS TelefonoContacto, dbo.Clientes_Contactos.Email AS EmailContacto, dbo.Clientes_Contactos.Cargo AS CargoContacto, Enc.ValorTRM, COALESCE (Cli3.Nombre1, N'') 
                         + N' ' + COALESCE (Cli3.Nombre2, N'') + N' ' + COALESCE (Cli3.Apellido1, N'') + N' ' + COALESCE (Cli3.Apellido2, N'') AS NombreVend, COALESCE (CliVend2.Nombre1, N'') + N' ' + COALESCE (CliVend2.Nombre2, 
                         N'') + N' ' + COALESCE (CliVend2.Apellido1, N'') + N' ' + COALESCE (CliVend2.Apellido2, N'') AS NombreVend2, dbo.Documentos.IdPlantilla, Enc.EsBloqueado, Enc.EsAnulado, Emp.EmpresaResolucion, 
                         dbo.Monedas.Abrev, dbo.Encabezados_Estados.NomEstado, Enc.IdEstado, dbo.Clientes_Direcciones.Celular AS CelularCli, dbo.Clientes_Direcciones.EMail AS EmailCli, Cli4.Identificacion AS IdentificacionProp, 
                         COALESCE (Cli4.Nombre1, N'') + N' ' + COALESCE (Cli4.Nombre2, N'') + N' ' + COALESCE (Cli4.Apellido1, N'') + N' ' + COALESCE (Cli4.Apellido2, N'') AS NombreProp, RegCli.NomTipoRegimen AS RegimenCli, 
                         RegProp.NomTipoRegimen AS RegimenProp, dbo.PH_Inmuebles.Direccion AS DireccionInmueble, Cli1.DVCliente, COALESCE (Cli1.Identificacion, N'') + N'' + COALESCE (N'-' + CAST(Cli1.DVCliente AS varchar), N' ')
                          AS NitCliente, VTot_EncCont.Credito, VTot_EncCont.Debito, VTot_EncCont.SumReteFuente, VTot_EncCont.SumIva, VTot_EncCont.SumReteIva, VTot_EncCont.SumReteIca, VTot_EncCont.SumReteFuenteCree, 
                         VTot_EncCont.SumReteIvaRSC, VTot_EncMov.SubTotal, VTot_EncMov.SubTotalSinDcto, VTot_EncMov.ValorDcto, VTot_EncMov.SubIVA, 
                         VTot_EncMov.TotDoc, Enc.IdSucursal, dbo.Sucursales.NomSucursal, dbo.Sucursales.CodSucursal, VTot_EncCont.SumAutoReteCreeC, VTot_EncCont.SumAutoReteCreeD, 
                         VTot_EncCont.SumImpuestoalConsumo, VTot_EncCont.SumReteIvaRegimenSimplificadoD, VTot_EncCont.SumDiferidosPasivo, VTot_EncCont.SumReteIvaNoDomiciliado, 
                         VTot_EncCont.SumCuentasPorCobrar, VTot_EncCont.SumCuentasPorPagar, VTot_EncCont.SumReteIvaRegimenComun, VTot_EncCont.SumTesoreria, VTot_EncCont.SumNormal, 
                         VTot_EncCont.SumComisionTarjetas, 0 AS EsDelete, Reparacion_Automotores_1.NomAutomotor, Enc.ValorPago, tipoDir.NomTipoDir, dbo.Documentos.CodDocumento, dbo.Encabezados_Tipos.NomTipo, 
						 VTot_EncMov.IVA, VTot_EncMov.SubTotExento, VTot_EncMov.SubTotGravado, VTot_EncMov.Cantidad, 
						 '' AS EspecialTercero01, '' AS EspecialTercero02, '' AS EspecialTercero03, '' AS EspecialTercero04, '' AS EspecialTercero05, '' AS EspecialTercero06, '' AS EspecialTercero07, '' AS EspecialTercero08,
						 '' AS EspecialTercero09, '' AS EspecialTercero10, '' AS EspecialTercero11, '' AS EspecialTercero12, '' AS EspecialTercero13, '' AS EspecialTercero14, '' AS EspecialTercero15, '' AS EspecialTercero16,
						 '' AS EspecialTercero17, '' AS EspecialTercero18, '' AS EspecialTercero19, '' AS EspecialTercero20, '' AS EspecialTercero21, '' AS EspecialTercero22, '' AS EspecialTercero23, '' AS EspecialTercero24,
						 '' AS EspecialTercero25, '' AS EspecialTercero26, '' AS EspecialTercero27, '' AS EspecialTercero28, '' AS EspecialTercero29, '' AS EspecialTercero30, '' AS EspecialTercero31, '' AS EspecialTercero32,
						 '' AS EspecialTercero33, '' AS EspecialTercero34, '' AS EspecialTercero35, '' AS EspecialTercero36, '' AS EspecialTercero37, '' AS EspecialTercero38, '' AS EspecialTercero39, '' AS EspecialTercero40,
						 '' AS Especial01, '' AS Especial02, '' AS Especial03, '' AS Especial04, '' AS Especial05, '' AS Especial06, '' AS Especial07, '' AS Especial08, '' AS Especial09, '' AS Especial10,
						 '' AS Especial11, '' AS Especial12, '' AS Especial13, '' AS Especial14, '' AS Especial15, '' AS Especial16, '' AS Especial17, '' AS Especial18, '' AS Especial19, '' AS Especial20,
						 '' AS Especial21, '' AS Especial22, '' AS Especial23, '' AS Especial24, '' AS Especial25, '' AS Especial26, '' AS Especial27, '' AS Especial28, '' AS Especial29, '' AS Especial30,
						 '' AS Especial31, '' AS Especial32, '' AS Especial33, '' AS Especial34, '' AS Especial35, '' AS Especial36, '' AS Especial37, '' AS Especial38, '' AS Especial39, '' AS Especial40,
						 NULL AS IdHistClinPaciente, '' AS TipoIdenHistClinPaciente, '' AS IdenHistClinPaciente, '' AS NombresHistClinPaciente, '' AS ApellidosHistClinPaciente, NULL AS EdadHistClinPaciente, NULL AS FechaNaciHistClinPaciente,
						 '' AS DireccionHistClinPaciente, '' AS GeneroHistClinPaciente, '' AS CelularHistClinPaciente,'' AS TelefonosHistClinPaciente,'' AS MedPrepContratoHistClin, '' AS IdenPrepagadaHistClin,
						  '' AS NomPrepagadaHistClin, '' AS EpsContratoHistClin, '' AS IdenEpsHistClin,'' AS NomEpsHistClin, NULL AS FechaIniHistClinPaciente, NULL AS FechaFinHistClinPaciente,'' AS CodigoInterno,
						  cast(Zona1.IdZona1 as nvarchar(10))+'-'+Zona1.NomZona1 as Zona1, VTot_EncMov.ValorPuntos,CAST(0 AS numeric(28,6)) SaldoPuntosCliente, Enc.FeCUFE, Enc.FeFechaAutorizacion, Enc.FeIdProveedor, FeProvee.NomProveedor,
						  FeEstDian.NomEstado AS FeNomEstadoDian, FeEstProvee.NomEstado AS FeNomEstadoProvee, FeConcep.NomConcepto, Enc.EsFeSincronizada, Emp.NumLocalizacionGlobal,Enc.FeIdEstadoDian, FeTipoOpe.CodOperacion AS TipoOpeEnc, 
						  FeMedPago.CodMedPago, FeMedPago.IdMedPago, FeTipoOp.CodOperacion AS TipoOpeDoc, Enc.FeCufeND, Enc.FePrefijoND, Enc.FeNumDocND, Enc.FeFechaEmisionND, Enc.IdMoneda,  FeEstProvee.IdEstado AS FeEstProveedor, Enc.FeIdVersion AS FeDocVersion, Enc.IdClienteVend, Enc.FeOrdenComp, Enc.FeDocDesp, Enc.FeIdTipoIngreso, Enc.FeEnvioAnticipo

	FROM  dbo.Encabezados AS Enc INNER JOIN
		  dbo.Empresas AS Emp ON Enc.IdEmpresa = Emp.IdEmpresa INNER JOIN
		  dbo.Documentos ON Enc.IdDocumento = dbo.Documentos.IdDocumento INNER JOIN
		  dbo.Clientes as Cli1 ON Cli1.IdCliente = Enc.IdCliente1 LEFT  JOIN
          #tempMovimientos AS VTot_EncMov ON Enc.IdEncab = VTot_EncMov.IdEncab LEFT  JOIN
          #tempCont as VTot_EncCont ON Enc.IdEncab = VTot_EncCont.IdEncab LEFT  JOIN
          dbo.Clientes AS Cli4 ON Enc.IdClienteProp = Cli4.IdCliente LEFT  JOIN
          dbo.Clientes AS Cli2 ON Enc.IdCliente2 = Cli2.IdCliente LEFT  JOIN
          dbo.Clientes AS Cli3 ON Enc.IdClienteVend = Cli3.IdCliente LEFT  JOIN
          dbo.Clientes AS CliVend2 ON Enc.IdClienteVend2 = CliVend2.IdCliente LEFT  JOIN
		  dbo.Sucursales ON dbo.Sucursales.IdSucursal = Enc.IdSucursal LEFT  JOIN
		  dbo.Clientes_Contactos ON Enc.IdContacto = dbo.Clientes_Contactos.IdContacto LEFT  JOIN	
		  dbo.Clientes_Direcciones ON Enc.IdCliente1_IdDir = dbo.Clientes_Direcciones.IdClienteDir LEFT  JOIN
		  dbo.Ciudades ON dbo.Ciudades.IdCiudad = dbo.Clientes_Direcciones.IdCiudad  LEFT  JOIN
		  dbo.Monedas ON Enc.IdMoneda = dbo.Monedas.IdMoneda LEFT  JOIN
		  dbo.Reparacion_Automotores AS Reparacion_Automotores_1 ON  Enc.IdAutomotor = Reparacion_Automotores_1.IdAutomotor  LEFT  JOIN
		  dbo.Reparacion_Marcas ON dbo.Reparacion_Marcas.IdMarca = Reparacion_Automotores_1.IdMarca LEFT  JOIN
          dbo.FormasPago AS FP ON Enc.IdFormaPago = FP.IdFormaPago LEFT  JOIN
          dbo.Encabezados_Estados ON Enc.IdEstado = dbo.Encabezados_Estados.IdEstado LEFT  JOIN
          dbo.Clientes_TiposRegimen AS RegCli ON Cli1.IdTipoRegimen = RegCli.IdTipoRegimen LEFT  JOIN
          dbo.Clientes_TiposRegimen AS RegProp ON Cli4.IdTipoRegimen = RegProp.IdTipoRegimen LEFT  JOIN
          dbo.PH_Inmuebles ON dbo.PH_Inmuebles.IdInmueble = Enc.IdInmueble LEFT  JOIN
          dbo.Clientes_TiposDireccion AS tipoDir ON tipoDir.IdTipoDir = dbo.Clientes_Direcciones.IdTipoDir LEFT  JOIN
          dbo.Prefijos AS pre ON pre.IdPrefijo = Enc.IdPrefijo LEFT  JOIN
		  dbo.Encabezados_Tipos ON  dbo.Encabezados_Tipos.IdTipo=Enc.IdTipo LEFT JOIN
		  dbo.Clientes_Zona1 AS Zona1 ON Cli1.IdZona1=Zona1.IdZona1 LEFT JOIN 
		  dbo.FacturacionElectronica_Proveedores AS FeProvee ON Enc.FeIdProveedor = FeProvee.IdProveedor LEFT  JOIN
		  dbo.FacturacionElectronica_Proveedores_Estados AS FeEstDian ON Enc.FeIdEstadoDian = FeEstDian.IdEstado LEFT  JOIN
		  dbo.FacturacionElectronica_Proveedores_Estados AS FeEstProvee ON Enc.FeIdEstadoProveedor = FeEstProvee.IdEstado  LEFT  JOIN
		  dbo.FacturacionElectronica_Conceptos_Documentos AS FeConcep ON Enc.FeIdConcepto = FeConcep.IdConcepto LEFT OUTER JOIN
		  dbo.FacturacionElectronica_MediosPagoContado AS FeMedPago ON FeMedPago.IdMedPago = Enc.FeIdMedPago LEFT OUTER JOIN
		  dbo.FacturacionElectronica_TipoOperacion AS FeTipoOpe ON FeTipoOpe.IdOperacion = Enc.FeIdOperacion LEFT OUTER JOIN
		  dbo.FacturacionElectronica_TipoOperacion AS FeTipoOp ON FeTipoOp.IdOperacion = Documentos.FeIdOperacion
		  		 
	WHERE
	(@IdEmp is null or enc.IdEmpresa=@IdEmp) AND
	(@IdDoc is null or enc.IdDocumento=@IdDoc) AND
	(@NumDoc is null or enc.NumDocumento=@NumDoc) AND
	(@IdCli1 is null or enc.IdCliente1=@IdCli1) AND
	(@FechaI is null or enc.Fecha>=@FechaI) AND
	(@FechaF is null or enc.Fecha<=@FechaF) AND
	(@IdSuc is null or enc.IdSucursal=@IdSuc) 	
	OPTION(RECOMPILE, MAXDOP 4)
END
GO

ALTER PROCEDURE [dbo].[GetLiquidarNomina]

	@IdMoneda INT,
	@Fecha datetime,
	@FechaIni datetime,
	@FechaFin datetime,
	@IdEmpresa INT,
	@NumDocumento INT,
	@IdCliente1 INT,
	@EsAnulado BIT,
	@EsBloqueado BIT,
	@EsRevisado BIT,
	@EsImpreso BIT,
	@InfoUsuario NVARCHAR(100),
	@Cantidad NUMERIC(28,6),
	@IdEncab INT,
	@IdEmpleado NVARCHAR(max),
	@ValorBase NUMERIC(28,6),
	@AuxTransport NUMERIC(28,6),
	@EsFondoSolid BIT=0,
	@EsAuxTransportDed BIT=0,
	@EsHorasExtas BIT=0,
	@EsNew BIT=0,
	@Exito INT = 0 output,
	@IdEncabNuevo INT = 0 output
As

BEGIN

DECLARE 

@salarioMin NUMERIC(28,6),
@AuxTrans NUMERIC(28,6),
@EmpleadoHoras int=0,
@FechaCorte DATETIME,
@FechaIniT DATETIME,
@IdDocumento int=0,
@IdEmpleadoCurrent INT=0

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	--Información Usuario
	exec CrearContextoUsuario @InfoUsuario
	
	-- Salario Mínimo y Auxilio de transporte
	SET @salarioMin=(SELECT Valor FROM dbo.Nomina_Valores WHERE IdTipo=1 AND EsActivo=1)
	SET @AuxTrans=(SELECT Valor FROM dbo.Nomina_Valores WHERE IdTipo=2 AND EsActivo=1)
	
	IF @IdEncab is null
		INSERT INTO Encabezados(IdEmpresa,IdDocumento,NumDocumento,Fecha,FechaIni,FechaFin,IdCliente1,IdMoneda,EsAnulado,EsBloqueado,EsRevisado,EsImpreso,IdTipo)
		VALUES (@IdEmpresa,20,@NumDocumento,@Fecha,@FechaIni,@FechaFin,@IdCliente1,@IdMoneda,@EsAnulado,@EsBloqueado,@EsRevisado,@EsImpreso,1)
		
	SET @IdEncabNuevo=IsNull(@IdEncab,IDENT_CURRENT('Encabezados'))
	
	--Fecha de corte para el calculo de Aux de Transporte
	SET @FechaCorte= DATEADD(dd,-(DATEPART(dd,@FechaIni)-1),@FechaIni)
	
	SET @FechaIniT = CAST(CONVERT(DATETIME, @FechaIni, 103) + ' 23:59:59' AS DATETIME)

	
	select @IdDocumento=IdDocumento
	from Encabezados
	where IdEncab=@IdEncabNuevo

	--Eliminar registros de sueldo
	IF @EsNew=1
		DELETE FROM Nomina_Movimientos
		WHERE IdEncab=IsNull(@IdEncabNuevo,0) and IdCliente IN(select number from iter$simple_intlist_to_tbl(@IdEmpleado)) 
		and (EsVisible=0 or IdItem in(
		select IdItem
		from dbo.Nomina_Items
		where IdClasif in(1)
		))
	
	--Eliminar registros de Aux de transporte
	DELETE FROM Nomina_Movimientos
	WHERE IdEncab=IsNull(@IdEncabNuevo,0) and IdCliente IN(select number from iter$simple_intlist_to_tbl(@IdEmpleado)) 
	and (EsVisible=0 or IdItem in(
	select IdItem
	from dbo.Nomina_Items
	where IdClasif = 3 -- in(3)
	))
	
IF @EsFondoSolid=1
	--Eliminar registros de fondo de solidaridad
	DELETE FROM Nomina_Movimientos
	WHERE IdEncab=IsNull(@IdEncabNuevo,0) and IdCliente IN(select number from iter$simple_intlist_to_tbl(@IdEmpleado)) 
	and IdItem=199

	
	--Recalcular Item de sueldo
	IF @EsNew=1
		INSERT INTO dbo.Nomina_Movimientos(IdEncab, IdCliente,IdContrato,IdItem, Cantidad, IdTiempo, ValorBase, ValorUnit,ValorUnitInferior,IdCentroCosto)
		select @IdEncabNuevo,q.IdCliente,q.IdCOntrato,min(q.IdItem),case when @IdDocumento=49 then @Cantidad else q.Cantidad end,1,q.ValorBase,q.ValorUnit,q.ValorDiv,q.IdCentroCosto
		
		from
		(	
			SELECT cont.IdCliente,IdContrato,item.IdItem,
			CASE WHEN FechaIni>@FechaIni AND FechaRetiro<@FechaFin THEN DATEDIFF(day,FechaIni,FechaRetiro)+1 ELSE
			CASE WHEN FechaIni>@FechaIni THEN DATEDIFF(day,FechaIni,@FechaFin)+1 ELSE
			CASE WHEN FechaRetiro<@FechaFin THEN DATEDIFF(day,@FechaIni,FechaRetiro)+1 ELSE @Cantidad END END END AS Cantidad,
		   IsNull(@ValorBase,SueldoIni)ValorBase,IsNull(@ValorBase,SueldoIni)ValorUnit,30 ValorDiv,cliEmp.IdCentroCosto
			FROM  dbo.Nomina_Contratos cont inner join Nomina_Configuracion confi 
			on cont.IdPlantilla=confi.IdPlantilla inner join Nomina_Items item
			on confi.IdItem=item.IdItem inner join dbo.Clientes_Empleados as cliEmp
			on cliEmp.IdCliente=cont.IdCliente
			WHERE cont.EsActivo=1 and @FechaFin>=IsNull(FechaIni,@FechaFin) and @FechaIni<=IsNull(FechaRetiro,@FechaIni)and cont.IdCliente IN(select number from iter$simple_intlist_to_tbl(@IdEmpleado))
			and item.IdClasif=1 and IsNull(cont.IdEmpresa,@IdEmpresa)=@IdEmpresa) as q
		group by q.IdCliente,q.IdCOntrato,q.Cantidad,q.ValorBase,q.ValorUnit,q.ValorDiv,q.IdCentroCosto
		
	IF @EsNew=0 and @ValorBase is not null
		UPDATE dbo.Nomina_Movimientos
		SET ValorBase=@ValorBase,
		ValorUnit=@ValorBase
		WHERE Nomina_Movimientos.IdCliente IN(select number from iter$simple_intlist_to_tbl(@IdEmpleado))
		  and IdEncab=@IdEncabNuevo and Iditem in (100,101,102)

  IF @EsFondoSolid=1
--Recalcular Fondo de Solidaridad
	INSERT INTO dbo.Nomina_Movimientos(IdEncab, IdCliente, IdItem,IdContrato, Cantidad, IdTiempo,ValorUnit,ValorUnitInferior,IdCentroCosto)
	select @IdEncabNuevo,q1.IdCliente,199,q1.IdContrato,1,1,max(Valor1)-MAX(ValorUnit),1,q1.IdCentroCosto
	from
	(	
	select 
	q.IdCliente,q.IdContrato,
	(Valor*IsNull(config.DivParteSuperior,1)/IsNull(config.DivParteInferior,1))*IsNull(PorcTarifa,0)/100 as Valor1,
	case when mov.IdItem=199 then ValorUnit else 0 end ValorUnit,
	cliEmp.IdCentroCosto
	from
		(
	select IdCliente,IdContrato,
			sum((ValorUnit/ValorUnitInferior)*cantidad) as Valor
			from dbo.Nomina_Movimientos mov inner join dbo.Nomina_Items item on mov.IdItem=item.IdItem
			inner join dbo.Encabezados as enc on mov.IdEncab=enc.IdEncab
			where EsFondoSolid=1 and (IsNull (Mov.FactorMov, 1) = 1)
			and enc.FechaIni between @FechaCorte and @FechaIniT and EsAnulado=0
			group by IdCliente,IdContrato) as q inner join dbo.Nomina_Movimientos mov on q.IdCliente=mov.IdCliente and q.IdContrato=mov.IdContrato
			inner join dbo.Encabezados as enc on mov.IdEncab=enc.IdEncab
		inner join dbo.Nomina_Items item on mov.IdItem=item.IdItem inner join dbo.Clientes_Empleados as cliEmp
		on cliEmp.IdCliente=q.IdCliente
		inner join Nomina_Contratos con on q.IdContrato=con.IdContrato left join [dbo].[Nomina_Configuracion] config on con.IdPlantilla=config.IdPlantilla and config.IdItem=199
		 left join  dbo.Nomina_Rangos nomRan on ((Valor*IsNull(config.DivParteSuperior,1)/IsNull(config.DivParteInferior,1))/@salarioMin) between nomRan.RangoIni and nomRan.RangoFin and nomRan.IdTipo='4'
		where  enc.FechaIni between @FechaCorte and  @FechaIniT and EsAnulado=0
		and (Valor/@salarioMin)>4 and q.IdCliente IN(select number from iter$simple_intlist_to_tbl(@IdEmpleado))
		and ((item.IdClasif=1 and mov.IdEncab=@IdEncabNuevo) or mov.IdItem=199)) q1
		group by q1.IdCliente,q1.IdContrato,q1.IdCentroCosto
	
		
	--Recalcular Horas Extras	
IF (select count(number) from iter$simple_intlist_to_tbl(@IdEmpleado)) = 1 AND @EsHorasExtas=1
	BEGIN
		set @EmpleadoHoras=(select min(number) from iter$simple_intlist_to_tbl(@IdEmpleado))
		EXEC	RecalcNominaHorasExtra
 				      @EmpleadoHoras,
 				      @IdEncabNuevo,
 				      null,
 				      @Exito
     END
     
 	
 	--Recalcular aux de Transporte (Por ley)			      			      	
	IF @AuxTransport=-1	
	BEGIN

	;with tempPlantillas(IdPlantilla,IdItem,FactorPlantilla)
	as
	(
		select IdPlantilla,IdItem,max((isnull(DivParteSuperior,1)/isnull(DivParteInferior,1)))as FactorPlantilla
			from Nomina_Configuracion
			where IdItem=110
			group by IdPlantilla,IdItem
	)

	INSERT INTO dbo.Nomina_Movimientos(IdEncab, IdCliente, IdItem,IdContrato,Cantidad, IdTiempo,ValorUnit,ValorUnitInferior,IdCentroCosto)
		select @IdEncabNuevo,q.IdCliente,110,q.IdContrato,sum(mov.Cantidad),1,@AuxTrans*ISNULL(FactorPlantilla,1),30,cliEmp.IdCentroCosto
		from
		(select IdCliente,IdContrato,
			case when count(distinct enc.IdEncab)=1 then sum (case when IdClasif=1 then (ValorBase * EsValorBase) else (ValorUnit/ValorUnitInferior)*cantidad end)
			else sum((ValorUnit/ValorUnitInferior)*cantidad)end as Valor,
			max(cantidad) as cantidad
			from dbo.Nomina_Movimientos mov inner join dbo.Nomina_Items item on mov.IdItem=item.IdItem
			inner join dbo.Encabezados as enc on mov.IdEncab=enc.IdEncab
			where EsAuxTransp=1 and (COALESCE (Mov.FactorMov, 1) = 1)
			and enc.FechaIni between @FechaCorte and @FechaIniT and EsAnulado=0
			group by IdCliente,IdContrato)as q inner join dbo.Nomina_Movimientos mov on q.IdCliente=mov.IdCliente and q.IdContrato=mov.IdContrato
		inner join dbo.Nomina_Items item on mov.IdItem=item.IdItem inner join dbo.Clientes_Empleados as cliEmp
		on cliEmp.IdCliente=q.IdCliente inner join Nomina_Contratos contrato
		on contrato.IdContrato=q.IdContrato inner join tempPlantillas plantilla on contrato.IdPlantilla=plantilla.IdPlantilla 
		where Valor<=(@salarioMin*2)	and q.IdCliente IN(select number from iter$simple_intlist_to_tbl(@IdEmpleado))
		and item.IdClasif=1 and item.EsAuxTranspDias=1 and mov.IdEncab=@IdEncabNuevo
		group by q.IdCliente,q.IdContrato,cliEmp.IdCentroCosto,FactorPlantilla

     END


	    IF @EsAuxTransportDed=1	
		--Calcular descuento del Auxilio de Transporte
		BEGIN
		
		DELETE FROM Nomina_Movimientos
		WHERE IdEncab=IsNull(@IdEncabNuevo,0) and IdCliente IN(select number from iter$simple_intlist_to_tbl(@IdEmpleado))
		AND IdItem=217 
		
		INSERT INTO dbo.Nomina_Movimientos(IdEncab, IdCliente, IdItem, IdContrato, Cantidad, IdTiempo,ValorUnit,ValorUnitInferior,IdCentroCosto)
		select @IdEncabNuevo,q.IdCliente,217,q.IdContrato,1,1,(@AuxTrans*mov.Cantidad/30),1,cliEmp.IdCentroCosto
		from
		(select IdCliente,IdContrato,
			sum((ValorUnit/ValorUnitInferior)*cantidad)as Valor,
			max(cantidad) as cantidad
			from dbo.Nomina_Movimientos mov inner join dbo.Nomina_Items item on mov.IdItem=item.IdItem
			inner join dbo.Encabezados as enc on mov.IdEncab=enc.IdEncab
			where EsAuxTransp=1 and (IsNull (Mov.FactorMov, 1) = 1)
			and enc.FechaIni between @FechaCorte and @FechaIniT and EsAnulado=0
			group by IdCliente,IdContrato)as q inner join dbo.Nomina_Movimientos mov on q.IdCliente=mov.IdCliente and q.IdContrato=mov.IdContrato
		inner join dbo.Nomina_Items item on mov.IdItem=item.IdItem inner join dbo.Clientes_Empleados as cliEmp
		on cliEmp.IdCliente=q.IdCliente inner join dbo.Encabezados as enc on mov.IdEncab=enc.IdEncab
		where Valor>(@salarioMin*2)	and q.IdCliente IN(select number from iter$simple_intlist_to_tbl(@IdEmpleado))
		and item.IdItem=110 and enc.FechaIni between @FechaCorte and @FechaIniT and EsAnulado=0
		END
	  
	--Recalcular Aux de Transporte Personalizado
	IF @AuxTransport>0
	INSERT INTO dbo.Nomina_Movimientos(IdEncab, IdCliente, IdItem,IdContrato,Cantidad, IdTiempo,ValorUnit,ValorUnitInferior,IdCentroCosto)
	SELECT @IdEncabNuevo,IsNull(@IdEmpleado,cont.IdCliente),110,cont.IdContrato,@Cantidad,1,@AuxTransport,30,cliEmp.IdCentroCosto
	FROM  dbo.Nomina_Contratos cont inner join Nomina_Configuracion confi 
	on cont.IdPlantilla=confi.IdPlantilla inner join Nomina_Items item
	on confi.IdItem=item.IdItem inner join dbo.Clientes_Empleados as cliEmp
	on cliEmp.IdCliente=cont.IdCliente
	WHERE cont.EsActivo=1 and cont.IdCliente IN(select number from iter$simple_intlist_to_tbl(@IdEmpleado))
	and item.IdClasif=3 and IsNull(cont.IdEmpresa,@IdEmpresa)=@IdEmpresa
	      
   --Insert de los registros hijos(Visible=0)--
	EXEC  dbo.GetLiquidarNominaHijos @IdEncabNuevo,@IdEmpresa,@IdEmpleado,NULL
	
	--Procedimiento actualizar intereses sobre cesantias
	EXEC  dbo.GenerarInteresesCesantias @IdEncabNuevo,@IdEmpleado

		
IF @@error <> 0 GOTO ERR_HANDLER
    
SET @Exito = 1
RETURN
    
ERR_HANDLER:
SET @Exito = 0
	       
END
GO

--Correccion de ids por tema de tildes
--//
UPDATE [dbo].[Cuentas_Grupos]
SET [CodigoDian] = '06', [NombreDian] = 'ReteFuente'
WHERE IdGrupoCuenta = 4
GO

UPDATE [dbo].[Cuentas_Grupos]
SET [CodigoDian] = '05', [NombreDian] = 'ReteIVA'
WHERE IdGrupoCuenta = 6
GO

UPDATE [dbo].[Cuentas_Grupos]
SET [CodigoDian] = '07', [NombreDian] = 'ReteICA'
WHERE IdGrupoCuenta = 7
GO
--//

--Reclasificacion de nomina items de vacaciones
--//
UPDATE Nomina_Items SET IdClasifNe = 23 WHERE IdItem = 220
UPDATE Nomina_Items SET IdClasifNe = 24 WHERE IdItem = 221
--//
GO

INSERT INTO ZiurVersiones (IdVersion, NumVersion, Descripcion)
VALUES (313,903,'Mejoras de performance y correccion FE y SS')
GO

COMMIT
GO
