-- =============================================
-- Author:		<Yonatan Gaete>
-- Create date: <02-07-2024>
-- Description:	<CARGAR VISUALIZACION DE LOTES Y SERIES CIERRE TOMA>
-- =============================================
CREATE PROCEDURE sp_invCierreTomaLotes_Sel
(
	@codbus AS VARCHAR(50),
	@numDoc	AS INT,
	@numlinDoc AS INT,
	@codbod AS INT,
	@conSaldo AS INT
)
AS
BEGIN
	IF @conSaldo = 0
		BEGIN
			SELECT
				DL.codbus,
				DL.cantidad,
				0 AS saldo,
				DL.serie,
				DL.lote,
				DL.fechaVencimiento,
				DL.idLinea,
				DL.numlinDoc
			FROM invDetalleLotes DL
			WHERE codbus = @codbus
			  AND numDoc = @numDoc
			  AND codDoc = 99
			  AND numlinDoc = @numlinDoc;
		END		
	
	IF @conSaldo = 1
		BEGIN
			SELECT
				PTL.codbus,
				PTL.cantidad,
				ISNULL((SELECT CONVERT(VARCHAR, SUM(CASE tipo WHEN 0 THEN cantidad WHEN 1 THEN cantidad * -1 END)) AS cantidad
				 FROM 
					(SELECT 
						L.*
					 FROM invDetalleLotes L
					 LEFT OUTER JOIN invEncabezado IE 
						ON IE.coddoc = L.codDoc 
						   AND IE.numinv = L.numDoc
					 WHERE IE.codbod = @codbod
					   AND L.codbus = PTL.codbus
					   AND L.serie = PTL.serie
					   AND L.lote = PTL.lote
					   AND L.fechaVencimiento = PTL.fechaVencimiento
					 UNION ALL
					 SELECT
						L.*
					 FROM invDetalleLotes L
					 LEFT OUTER JOIN venEncabezado VE 
						ON VE.codsii = L.codDoc 
						   AND VE.numinv = L.numDoc
					 WHERE VE.codbod = @codbod
					   AND L.codbus = PTL.codbus
					   AND L.serie = PTL.serie
					   AND L.lote = PTL.lote
					   AND L.fechaVencimiento = PTL.fechaVencimiento) AS SubQuery
				 WHERE codbus = PTL.codbus 
				   AND serie = PTL.serie 
				   AND lote = PTL.lote 
				   AND fechaVencimiento = PTL.fechaVencimiento), 'No Existe') AS saldo,
				PTL.serie,
				PTL.lote,
				PTL.fechaVencimiento,
				PTL.idLinea,
				PTL.numlinPreToma AS numlinDoc
			FROM invPreTomaLotes PTL
			WHERE codbus = @codbus
				AND numPreToma = @numDoc
				AND numlinPreToma = @numlinDoc
			ORDER BY idLinea
		END
END
GO