

/***************************************
* STORED stp_prtCartFusiByUDC
*
* ottiene il record del Cartellino FUSI dato l'UDC richiesto x la stampa
*
* Steamware, G.A.R.
* mod: 2012.02.27
*
****************************************/
CREATE PROCEDURE [dbo].[stp_prtCartFusiByUDC]
(
	@UDC NVARCHAR(50)
)
AS

    -- estraggo i trattamenti nelle note ( x max 10 trattamenti )
	DECLARE @tmp		NVARCHAR(500)
	DECLARE @NoteTratt	NVARCHAR(500)
	SET @NoteTratt = ''

	-- SET @NoteTratt = (SELECT SUBSTRING(@tmp, 0, LEN(@tmp)))

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

RETURN
