ALTER PROCEDURE [dbo].[sp_proFiltro_ProgramaOP]
(
@idOP BIGINT = 0,
@codEtapa INT = 0,
@codMaquina INT = 0,
@fecIni DATETIME,
@fecFin DATETIME,
@estado INT = -1 -- | -1 TODOS | 0 EMITIDAS | 1 EN PROCESO
)
AS

SELECT  proOP.idOP, proOPEtapas.linea, ISNULL(proOPDetalleNP.NP,0) AS NP , ISNULL(conCtacte.nomcte,'') AS nomcte,
		ISNULL(CONVERT(VARCHAR(20),conCtacte.rutcte) + '-' + conCtacte.digcte,'') AS rutcte,
		proOP.codbus, invArticulos.nomart, proOPEtapas.cantProceso AS cantidad,
		proOPEtapas.cantAlt, CONVERT(VARCHAR(10),proEtapas.codEtapa) + ' - ' + proEtapas.nomEtapa AS etapa,
		maquina, CONVERT(DECIMAL(18,2),duracion) AS duracion, CONVERT(date, inicio) AS fecInicio,
		CONVERT(char(5), inicio, 108) AS horaInicio, CONVERT(date, termino) AS fecTermino,
		CONVERT(char(5), termino, 108) AS horaTermino--, 'CALCULAR' as Calcular
FROM proOPEtapas INNER JOIN
proOP ON proOPEtapas.idOp = proOP.idOP INNER JOIN
proOPDetalleNP ON proOP.idOP = proOPDetalleNP.idOP INNER JOIN
proEtapas ON proOPEtapas.etapa = proEtapas.codEtapa AND proOPEtapas.periodo = proEtapas.periodo INNER JOIN
invArticulos ON proOP.codbus = invArticulos.codbus INNER JOIN
cotNotaPedido ON proOPDetalleNP.NP = cotNotaPedido.numdoc AND cotNotaPedido.coddoc = 2 INNER JOIN
conCtacte ON cotNotaPedido.cliente = conCtacte.rutcte
WHERE (proOP.idOp = @idOP OR @idOP = 0)
AND (etapa = @codEtapa OR @codEtapa = 0)
AND (maquina = @codMaquina OR @codMaquina =0)
AND (CONVERT(DATE,proOP.fecha_ingreso) BETWEEN @fecIni AND @fecFin)
AND  (proOP.estado = @estado OR @estado = -1)
AND (proOP.estado <> 4)
AND tipoOP = 1
AND proEtapas.periodo BETWEEN YEAR(@fecIni) AND YEAR(@fecfin)

UNION ALL

SELECT  proOP.idOP, proOPEtapas.linea, 0 AS NP , 'SOLICITUD DE STOCK' AS nomcte, '' AS rutcte,
		proOP.codbus, invArticulos.nomart, proOPEtapas.cantProceso AS cantidad,
		proOPEtapas.cantAlt, CONVERT(VARCHAR(10),proEtapas.codEtapa) + ' - ' + proEtapas.nomEtapa AS etapa,
		maquina, CONVERT(DECIMAL(18,2),duracion) AS duracion, CONVERT(date, inicio) AS fecInicio,
		CONVERT(char(5), inicio, 108) AS horaInicio, CONVERT(date, termino) AS fecTermino,
		CONVERT(char(5), termino, 108) AS horaTermino--, 'CALCULAR' as Calcular
FROM proOPEtapas INNER JOIN
proOP ON proOPEtapas.idOp = proOP.idOP INNER JOIN
proEtapas ON proOPEtapas.etapa = proEtapas.codEtapa INNER JOIN
invArticulos ON proOP.codbus = invArticulos.codbus
WHERE (proOP.idOp = @idOP OR @idOP = 0)
AND (etapa = @codEtapa OR @codEtapa = 0)
AND (maquina = @codMaquina OR @codMaquina =0)
AND (CONVERT(DATE,proOP.fecha_ingreso) BETWEEN @fecIni AND @fecFin)
AND  (proOP.estado = @estado OR @estado = -1)
AND (proOP.estado <> 4)
AND tipoOP = 2
AND proEtapas.periodo BETWEEN YEAR(@fecIni) AND YEAR(@fecfin)
order by fecInicio