

/***************************************
* STORED stp_prtCartLiquidi_F10ByUDC
*
* ottiene il record del Cartellino Liquidi dato l'UDC richiesto x la stampa
*
* Steamware, S.E.L.
* mod: 2011.12.19
*
****************************************/
CREATE PROCEDURE [dbo].[stp_prtCartLiquidi_F10ByUDC]
(
	@UDC NVARCHAR(50)
)
AS

	-- estraggo i trattamenti nelle note ( x max 10 trattamenti )
	DECLARE @tmp		NVARCHAR(500)
	DECLARE @NoteTratt	NVARCHAR(500)
	SET @tmp = ''
	SELECT  TOP 10 @tmp = @tmp + RIGHT(UDC_parent,8) + ' - ' from RelazUDC WHERE UDC_child = @UDC
    ORDER BY UDC_parent
    
	SET	@NoteTratt = (SELECT SUBSTRING(@tmp, 0, LEN(@tmp)))

	-- ora seleziono i dati veri e propri
	SELECT
		ISNULL(ElencoCartellini.UDC, N'0000') AS UDC, ISNULL(ElencoCartellini.CodCS, N'') AS CodCS, ISNULL(ElencoCartellini.CodCliente, N'') AS codcliente, 
		ISNULL(ElencoCartellini.RagSociale, N'') AS ragsociale, ISNULL(ElencoCartellini.Particolare, N'') AS particolare,
		ISNULL(ElencoCartellini.DescParticolare, N'') AS DescParticolare, ISNULL(ElencoCartellini.DisegnoGrezzo, N'') AS DisegnoGrezzo, 
		ISNULL(ElencoCartellini.Esponente, N'') AS esponente, ISNULL(ElencoCartellini.CodImpianto, N'') AS CodImpianto,
		ISNULL(ElencoCartellini.DescImpianto, N'') AS DescImpianto, ISNULL(ElencoCartellini.CodStampo, N'') AS codstampo, 
		ISNULL(ElencoCartellini.Figura, N'') AS figura, ISNULL(ElencoCartellini.DataFus, N'') AS datafus,
		ISNULL(ElencoCartellini.TurnoFus, N'') AS turnofus, ISNULL(ElencoCartellini.CodImballo, N'') AS CodImballo, 
		ISNULL(ElencoCartellini.CodSoggetto, N'') AS CodSoggetto, ISNULL(ElencoCartellini.NumCont, N'') AS NumCont,
		ISNULL(ElencoCartellini.Tara, N'') AS tara, ISNULL(ElencoCartellini.Qta, N'') AS qta, ISNULL(ElencoCartellini.CodStato, N'') AS CodStato, 
		ISNULL(ElencoCartellini.IdxPosizione, N'') AS IdxPosizione,
		ISNULL(ElencoCartellini.PesoTot, N'') AS PesoTot, ISNULL(ElencoCartellini.PesoCad, N'') AS PesoCad, ElencoCartellini.CreateDate, 
		ElencoCartellini.ModDate, ISNULL(RilPro.AnagOperatori.Cognome, N'') AS cognome,
		ISNULL(RilPro.AnagOperatori.Nome, N'') AS nome, ISNULL(AnagStatiProdotto.DescStato, N'') AS DescStato, 
		ISNULL(RilPro.AnagImballi.DescImballo, N'') AS DescImballo, RilPro.AnagParticolari.CodFamiglia, RilPro.AnagFamiglie.DescFamiglia,
		ISNULL(ElencoCartellini.Note, N'') AS Note, ISNULL(@NoteTratt, N'') AS NoteTratt
	FROM
		RilPro.AnagParticolari 
	LEFT OUTER JOIN
		RilPro.AnagFamiglie 
	ON
		RilPro.AnagParticolari.CodFamiglia = RilPro.AnagFamiglie.CodFamiglia 
	RIGHT OUTER JOIN
		ElencoCartellini 
	ON
		RilPro.AnagParticolari.Particolare = ElencoCartellini.Particolare 
	LEFT OUTER JOIN
		AnagStatiProdotto 
	ON
		ElencoCartellini.CodStato = AnagStatiProdotto.CodStato 
	LEFT OUTER JOIN
		RilPro.AnagOperatori 
	ON
		ElencoCartellini.CodSoggetto = RilPro.AnagOperatori.CodSoggetto 
	LEFT OUTER JOIN
		RilPro.AnagImballi 
	ON
		ElencoCartellini.CodImballo = RilPro.AnagImballi.CodImballo 
	WHERE
		(ElencoCartellini.UDC = @UDC)
 

RETURN

