ALTER PROCEDURE [dbo].[sp_cobContabilizacionPagos]
	-- Add the parameters for the stored procedure here
(
@fechaini as datetime,
@fechafin as datetime,
@tipo as integer,
@idpago as int = 0,
@formapago AS INT = 0
)
AS
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
----------------------------------------------------------------------------------------------------------
--	TIPPAG		0 = PAGO NORMAL		1 = EXCEDENTE DISPONIBLE	2 = PAGO CON EXCEDENTE		3 = HISTORICO
----------------------------------------------------------------------------------------------------------
--	NORMAL AL DEBE	--			--		AGRUPADA POR DOCUMENTO DE PAGO		--		CUENTA FORMA DE PAGO
----------------------------------------------------------------------------------------------------------
	DECLARE @CUENTACLIENTE AS INTEGER
	DECLARE @checkconceptos AS INTEGER
	DECLARE @refDocOri AS INTEGER = 0

	--SET @CUENTACLIENTE = (SELECT TOP 1  cobParametros.ctacliente FROM cobParametros)
	--SET @checkconceptos = (SELECT TOP 1  cobParametros.ctacliente FROM cobParametros)
    

	SELECT @CUENTACLIENTE=ctacliente, @checkconceptos=checkconceptos,@refDocOri = refDocOriginal  FROM cobParametros
	
	 -- LINEA DOCUMENTO
SELECT      CASE WHEN (PD.conceptocobranza > 0 and @checkconceptos =1)  THEN  dbo.cuentacobranza(PD.conceptocobranza) ELSE @CUENTACLIENTE END AS cuenta, 
		0 AS debe, 
		SUM(PD.monto) AS haber, 
		venDocumentos.alias_doc + 'N° ' + CONVERT(varchar(MAX), PD.ndocumento) + ' ' + COALESCE (conCtacte.nomcte, '') AS glosa_linea, 
		PD.rut AS ctacte, 
		PD.tipo AS tipo_docto, 
		PD.ndocumento AS num_docto, 
		E.fecven AS vencimiento,1 AS orden,
		PD.fpago,
		PD.serie,
		venDocumentos.codsii,
		ISNULL(PD.id_anticipo, 0) AS id_anticipo
INTO #A
FROM				cobPagoDocumentos AS PD INNER JOIN
				venEncabezado AS E ON PD.tipo = E.codsii AND PD.ndocumento = E.numinv INNER JOIN
				venDocumentos ON PD.tipo = venDocumentos.codsii LEFT OUTER JOIN
				conCtacte ON PD.rut = conCtacte.rutcte 
WHERE     (PD.comprocontable = 0) 
AND		  (PD.fechamov BETWEEN @fechaIni AND @fechaFin)
AND		  (PD.id = @idpago OR @idpago = 0)
--AND		  (PD.fpago = @formapago OR @formapago = 0)
GROUP BY E.fecven, PD.rut, conCtacte.nomcte, PD.ndocumento, venDocumentos.alias_doc, PD.tipo,conceptocobranza,PD.fpago, PD.serie, venDocumentos.codsii, PD.id_anticipo

UNION ALL

--LINEA PAGO
SELECT      COALESCE(CP.cuentacontable, 999999) AS cuenta, 
		SUM(PD.monto) AS debe, 
		0 AS haber, 
		CP.nompag + 'N° ' + PD.serie + ' ' + COALESCE (conBancos.nombre, 'SIN BANCO') AS glosa_linea, 
		PD.rut AS ctacte,
		CASE @refDocOri 
			WHEN 0 THEN 
				CASE  CP.tippag WHEN 2 THEN PD.tipo ELSE cobParametros.cheque END
			WHEN 1 THEN
				CASE  CP.tippag WHEN 2 THEN PD.tipo ELSE PD.tipondcto END
			END as tip_doc,
		CASE @refDocOri
			WHEN 0 THEN 
			CASE  CP.tippag WHEN 2 THEN PD.ndocumento ELSE PD.serie END 
			WHEN 1 THEN
			CASE  CP.tippag WHEN 2 THEN PD.ndocumento ELSE PD.ndocumento END 
		END AS num_docto,
				
		--CASE  CP.tippag WHEN 2 THEN PD.tipo ELSE cobParametros.cheque END AS tip_doc,
		--CASE  CP.tippag WHEN 2 THEN PD.ndocumento ELSE PD.serie END AS num_docto,

		PD.vencimiento,2 AS orden,
		PD.fpago,
		PD.serie,
		0 as codsii,
		id_anticipo
FROM         cobPagoDocumentos AS PD LEFT OUTER JOIN
					conBancos ON PD.banco = conBancos.idBanco LEFT OUTER JOIN
					venCondicionPago AS CP ON PD.fpago = CP.codpag LEFT OUTER JOIN
					conCtacte ON PD.rut = conCtacte.rutcte CROSS JOIN
					cobParametros
WHERE     (PD.comprocontable = 0) 
AND		  (PD.fechamov BETWEEN @fechaIni AND @fechaFin)
AND		  (PD.id = @idpago OR @idpago = 0)
--AND       (PD.fpago = @formapago OR @formapago = 0)
GROUP BY CP.nompag, CP.cuentacontable, PD.vencimiento, PD.rut, PD.serie, conBancos.nombre, cobParametros.cheque
, CP.tippag,PD.tipo,PD.ndocumento,PD.tipondcto, PD.ndocumento,PD.fpago, PD.id_anticipo


UNION ALL
	
SELECT      COALESCE(cobParametros.ctaexcedentes, 999999) AS cuenta, 
		0 AS debe, 
		SUM(cobAnticipos.monto)  AS haber, 
		'EXCEDENTE CLIENTE' AS glosa_linea, 
		cobAnticipos.rut AS ctacte, 
		cobAnticipos.tipoDocto AS tip_doc, 
		cobAnticipos.numDocto AS num_doc, 
		cobAnticipos.vencimiento ,4 AS orden,
	    0 as fpago,
		'' as serie,
		0 as codsii,
		cobAnticipos.id_anticipo
FROM            cobAnticipos CROSS JOIN
                cobParametros
WHERE  cobAnticipos.tipo=2 AND (cobAnticipos.comprocontable = 0) 
AND		  (cobAnticipos.fechamov BETWEEN @fechaIni AND @fechaFin)
group by cobParametros.ctaexcedentes,cobAnticipos.rut , cobAnticipos.tipoDocto , cobAnticipos.numDocto , 
cobAnticipos.vencimiento, cobAnticipos.id_anticipo

UNION ALL
	
--LINEA ANTICIPOS Y EXCEDENTES
SELECT      COALESCE(CP.cuentacontable, 999999) AS cuenta, 
		SUM(cobAnticipos.monto) AS debe, 
		0 AS haber, 
		CP.nompag + 'N° ' + cobAnticipos.serie AS glosa_linea, 
		cobAnticipos.rut AS ctacte, 
		cobAnticipos.tipoDocto AS tip_doc, 
		cobAnticipos.numDocto AS num_doc, 
		cobAnticipos.vencimiento,3 AS orden,
		0 as fpago,
		cobAnticipos.serie,
		0 as codsii,
		cobAnticipos.id_anticipo
FROM            venCondicionPago AS CP INNER JOIN
                cobAnticipos ON CP.codpag = cobAnticipos.codpag CROSS JOIN
                cobParametros
WHERE  cobAnticipos.tipo=2 AND (cobAnticipos.comprocontable = 0) 
AND		  (cobAnticipos.fechamov BETWEEN @fechaIni AND @fechaFin)
GROUP BY  cobAnticipos.numDocto,cobAnticipos.tipoDocto,CP.cuentacontable,CP.nompag ,cobAnticipos.serie,cobAnticipos.rut,cobParametros.cheque,cobAnticipos.vencimiento, cobAnticipos.id_anticipo


	--SELECT *  FROM #A 
	--ORDER BY orden

	SELECT  cuenta, sum(debe) as debe, haber, glosa_linea, 
				 ctacte, tipo_docto,num_docto, vencimiento as vencimiento,
				orden, #A.fpago, #A.serie, #A.codsii, #A.id_anticipo
	INTO #TABLAFINAL
	FROM #A
	WHERE (#A.fpago = @formapago or @formapago=0)
	GROUP BY cuenta,haber,glosa_linea,ctacte,tipo_docto,num_docto,vencimiento,orden,#A.fpago, #A.serie, #A.codsii, #A.id_anticipo
	ORDER BY orden desc 

	SELECT *, [dbo].[CentroCostoDocumento](ctacte,tipo_docto,num_docto) as uni_negocio
	FROM #TABLAFINAL