CREATE PROCEDURE [dbo].[sp_AddFrigPlantillaOrden]
(
@numeroFaena INT = 1,
@diseño INT = 0)
AS		
	--###### DATOS DE ORDEN DE FAENAMINETO #######
	SELECT
		ROW_NUMBER() OVER (ORDER BY D.idcorrelativo) AS 'LINEA',
		COUNT(D.idcorrelativo) AS 'CANTIDAD',
		C.nomcte AS 'NOMBRE_PROVEEDOR',
		CASE D.tipoanimal
			WHEN 1 THEN 'VACUNO'
			WHEN 2 THEN 'CERDO'
			WHEN 3 THEN 'CORDERO'
			WHEN 4 THEN 'OVEJAS'
			WHEN 5 THEN 'CABALLO Y YEGUAS'
			WHEN 6 THEN 'POTRONES Y POTRANCAS'
		END AS 'TIPO_ANIMAL',
		ISNULL((SELECT 
					STUFF((SELECT DISTINCT '-' + D2.codigoinicial
						FROM addFrigMovAnimalDetalle AS D2
						WHERE D.idcorrelativo = D2.idcorrelativo AND D2.codigoinicial <> ''
						FOR XML PATH('')), 1, 1, '')), '') AS 'FERIA_MARCA',
		E.marca AS 'MARCA_CLIENTE',
		MIN(D.codigointerno) AS 'DEL',
		MAX(D.codigointerno) AS 'AL',
		E.corral AS 'CORRAL'
	FROM addFrigMovAnimalDetalle D
		LEFT OUTER JOIN addFrigMovAnimalEnc E ON E.correlativo = D.idcorrelativo
		LEFT OUTER JOIN conCtacte C ON E.rut = C.rutcte
	WHERE 1 = D.numeroFaena
	GROUP BY D.idcorrelativo, C.nomcte, D.tipoanimal, E.marca, E.corral
	ORDER BY D.idcorrelativo ASC


	--###### FECHA ORDEN FENAMIENTO #######
	SELECT fechaOrden
	FROM addFrigOrdenFaenamiento
	WHERE numeroOrden = @numeroFaena