ALTER PROCEDURE [dbo].[sp_proListadoOP]
( @fecini DATETIME,
  @fecfin DATETIME,
  @etapa  INT   = 0,
  @tipo   INT   = 0,
  @codSuc INT   = 0
)
AS
	IF @tipo = 0

		BEGIN
		  SELECT proOP.idOP,
                    invArticulos.nomart,
                    invArticulos.codbus,
                    proOP.fecha_ingreso,
                    proOP.tipoOP,
                    CONVERT(VARCHAR(10),proOPEtapas.etapa) + ' - ' + proetapas.nomEtapa AS etapa, --COUNT(proOPEtapas.linea) AS etapas,
                    MIN(proOPEtapas.inicio) AS inicio_op,
                    MAX(proOPEtapas.termino) AS termino_op,
                --     '' AS inicio_opreal,
                --   '' AS termino_opreal,
                    proOPEtapas.cantTotal,
                    proOPEtapas.cantProceso,
                    proOPEtapas.cantProceso - proOPEtapas.cantTotal AS Diferencia,
                    CASE proOP.tipoOP
                            WHEN 1
                            THEN 'PEDIDO'
                            ELSE 'STOCK'
                    END AS origen,
						proOPDetalleNP.cantidad as cantidadPlanificada,
						dbo.CantidadLiberacionOP(proOP.idOP, proOPEtapas.linea ) as cantLiberada,
						proOPDetalleNP.cantidad - dbo.CantidadLiberacionOP(proOP.idOP, proOPEtapas.linea) as Saldo,
						proOP.codsuc,
						STUFF((SELECT ' ; ' + LEFT(REPLACE(CONVERT(varchar(20), CONVERT(money, cli.rutcte), 1), ',', '.'), LEN(REPLACE(CONVERT(varchar(20), CONVERT(money, cli.rutcte), 1), ',', '.')) - 3) + '-' + LTRIM(RTRIM(ISNULL(cli.digcte, '')))
							   FROM (SELECT DISTINCT cte.rutcte, cte.digcte
									 FROM proOPDetalleNP dnp
										  INNER JOIN cotNotaPedido np ON np.numdoc = dnp.NP AND coddoc = 2
										  INNER JOIN conCtacte cte ON cte.rutcte = np.cliente
									 WHERE dnp.idOP = proOP.idOP AND dnp.NP > 0) cli
							   ORDER BY cli.rutcte
							   FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 3, '') AS rutCliente,
						STUFF((SELECT ' ; ' + LTRIM(RTRIM(cli.nomcte))
							   FROM (SELECT DISTINCT cte.rutcte, cte.nomcte
									 FROM proOPDetalleNP dnp
										  INNER JOIN cotNotaPedido np ON np.numdoc = dnp.NP AND coddoc = 2
										  INNER JOIN conCtacte cte ON cte.rutcte = np.cliente
									 WHERE dnp.idOP = proOP.idOP AND dnp.NP > 0) cli
							   ORDER BY cli.rutcte
							   FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 3, '') AS nomCliente,
						STUFF((SELECT ' ; ' + CONVERT(varchar(20), pedido.NP)
							   FROM (SELECT DISTINCT dnp.NP
									 FROM proOPDetalleNP dnp
									 WHERE dnp.idOP = proOP.idOP AND dnp.NP > 0) pedido
							   ORDER BY pedido.NP
							   FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 3, '') AS notaPedido



                  FROM proOPEtapas
                            INNER JOIN invArticulos
                            INNER JOIN proOP ON invArticulos.codbus = proOP.codbus ON proOPEtapas.idOp = proOP.idOP
							INNER JOIN proOPDetalleNP ON proOP.idOP = proOPDetalleNP.idOP
					-- left JOIN proLiberacion ON proOP.idOP = proLiberacion.idOP
							INNER JOIN proEtapas ON proOPEtapas.etapa = proEtapas.codEtapa AND proetapas.periodo = proOPEtapas.periodo
                  WHERE(proOP.fecha_ingreso BETWEEN @fecini AND @fecfin)
                        -- AND PROOP.estado = @tipo
                            AND (proOPEtapas.etapa= @etapa OR @etapa=0)
                            AND (proOP.codsuc=@codSuc OR @codSuc=0)


                  GROUP BY proOP.idOP,
                                    invArticulos.nomart,
                                    invArticulos.codbus,
						    proOPEtapas.etapa,
                                    proOP.fecha_ingreso,
                                    proOP.tipoOP,
                                    proOPEtapas.cantProceso,
                                    proOPEtapas.cantTotal,
						    proOPDetalleNP.cantidad,
						    proOPEtapas.linea,
						    proOP.codsuc,
							proetapas.nomEtapa;

	END

	ELSE
		BEGIN
		    SELECT proOP.idOP,
                    invArticulos.nomart,
                    invArticulos.codbus,
                    proOP.fecha_ingreso,
                    proOP.tipoOP,
                    CONVERT(VARCHAR(10),proOPEtapas.etapa) + ' - ' + proetapas.nomEtapa AS etapa,
                    MIN(proOPEtapas.inicio) AS inicio_op,
                    MAX(proOPEtapas.termino) AS termino_op,
                --     '' AS inicio_opreal,
                --   '' AS termino_opreal,
                    proOPEtapas.cantTotal,
                    proOPEtapas.cantProceso,
                    proOPEtapas.cantProceso - proOPEtapas.cantTotal AS Diferencia,
                    CASE proOP.tipoOP
                            WHEN 1
                            THEN 'PEDIDO'
                            ELSE 'STOCK'
                    END AS origen,
						proOPDetalleNP.cantidad as cantidadPlanificada,
						dbo.CantidadLiberacionOP(proOP.idOP, proOPEtapas.linea ) as cantLiberada,
						proOPDetalleNP.cantidad - dbo.CantidadLiberacionOP(proOP.idOP, proOPEtapas.linea) as Saldo,
						proOP.codsuc,
						STUFF((SELECT ' ; ' + LEFT(REPLACE(CONVERT(varchar(20), CONVERT(money, cli.rutcte), 1), ',', '.'), LEN(REPLACE(CONVERT(varchar(20), CONVERT(money, cli.rutcte), 1), ',', '.')) - 3) + '-' + LTRIM(RTRIM(ISNULL(cli.digcte, '')))
							   FROM (SELECT DISTINCT cte.rutcte, cte.digcte
									 FROM proOPDetalleNP dnp
										  INNER JOIN cotNotaPedido np ON np.numdoc = dnp.NP AND coddoc = 2
										  INNER JOIN conCtacte cte ON cte.rutcte = np.cliente
									 WHERE dnp.idOP = proOP.idOP AND dnp.NP > 0) cli
							   ORDER BY cli.rutcte
							   FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 3, '') AS rutCliente,
						STUFF((SELECT ' ; ' + LTRIM(RTRIM(cli.nomcte))
							   FROM (SELECT DISTINCT cte.rutcte, cte.nomcte
									 FROM proOPDetalleNP dnp
										  INNER JOIN cotNotaPedido np ON np.numdoc = dnp.NP AND coddoc = 2
										  INNER JOIN conCtacte cte ON cte.rutcte = np.cliente
									 WHERE dnp.idOP = proOP.idOP AND dnp.NP > 0) cli
							   ORDER BY cli.rutcte
							   FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 3, '') AS nomCliente,
						STUFF((SELECT ' ; ' + CONVERT(varchar(20), pedido.NP)
							   FROM (SELECT DISTINCT dnp.NP
									 FROM proOPDetalleNP dnp
									 WHERE dnp.idOP = proOP.idOP AND dnp.NP > 0) pedido
							   ORDER BY pedido.NP
							   FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 3, '') AS notaPedido



                  FROM proOPEtapas
                            INNER JOIN invArticulos
                            INNER JOIN proOP ON invArticulos.codbus = proOP.codbus ON proOPEtapas.idOp = proOP.idOP
							INNER JOIN proOPDetalleNP ON proOP.idOP = proOPDetalleNP.idOP
					-- left JOIN proLiberacion ON proOP.idOP = proLiberacion.idOP
							INNER JOIN proEtapas ON proOPEtapas.etapa = proEtapas.codEtapa AND proetapas.periodo = proOPEtapas.periodo
                  WHERE(proOP.fecha_ingreso BETWEEN @fecini AND @fecfin)
                      AND PROOP.estado = @tipo
                            AND (proOPEtapas.etapa= @etapa OR @etapa=0)
                            AND (proOP.codsuc=@codSuc OR @codSuc=0)


                  GROUP BY proOP.idOP,
                                    invArticulos.nomart,
                                    invArticulos.codbus,
						    proOPEtapas.etapa,
                                    proOP.fecha_ingreso,
                                    proOP.tipoOP,
                                    proOPEtapas.cantProceso,
                                    proOPEtapas.cantTotal,
						    proOPDetalleNP.cantidad,
						    proOPEtapas.linea,
						    proOP.codsuc,
							proetapas.nomEtapa;


	END

                  --                                 SELECT proOP.idOP,
                  --               invArticulos.nomart,
                  --               invArticulos.codbus,
                  --               proOP.fecha_ingreso,
                  --               proOP.tipoOP,
                  --               COUNT(proOPEtapas.linea) AS etapas,
                  --               MIN(proOPEtapas.inicio) AS inicio_op,
                  --               MAX(proOPEtapas.termino) AS termino_op,
                  --           --     '' AS inicio_opreal,
                  --           --   '' AS termino_opreal,
                  --               proOPEtapas.cantTotal,
                  --               proOPEtapas.cantProceso,
                  --               proOPEtapas.cantProceso - proOPEtapas.cantTotal AS Diferencia,
                  --               CASE proOP.tipoOP
                  --                       WHEN 1
                  --                       THEN 'NOTA DE PEDIDO'
                  --                       ELSE 'STOCK'
                  --               END AS origen
                  -- FROM proOPEtapas
                  --           INNER JOIN invArticulos
                  --           INNER JOIN proOP ON invArticulos.codbus = proOP.codbus ON proOPEtapas.idOp = proOP.idOP
                  -- WHERE(proOP.fecha_ingreso BETWEEN @fecini AND @fecfin)
                  --           AND PROOP.estado = @tipo
                  --           AND (proOPEtapas.etapa= @etapa OR @etapa=0)
                  --           AND (proOP.codsuc=@codSuc OR @codSuc=0)

                  -- GROUP BY proOP.idOP,
                  --                   invArticulos.nomart,
                  --                   invArticulos.codbus,
                  --                   proOP.fecha_ingreso,
                  --                   proOP.tipoOP,
                  --                   proOPEtapas.cantProceso,
                  --                   proOPEtapas.cantTotal;

                  --                   SELECT @fecfin
                  --                   SELECT @FECFIN
