/***************************************
* STORED stp_getUdcDetailFullCode
*
* ottiene l'elenco degli UDC con i dati significativi partendo da un codice UDC COMPLETO
*
* Steamware, S.E.L.
* mod: 2010.05.24
*
****************************************/
CREATE PROCEDURE [dbo].[stp_getUdcDetailFullCode]
(
	@UDC VARCHAR(50),
	@CodCS VARCHAR(2)	
)
AS

SELECT        ec.UDC, ISNULL(ec.RagSociale, N'nd') AS RagSociale, ISNULL(ec.Particolare, N'nd') AS Particolare,
			  ISNULL(ec.DescParticolare, N'nd') AS DescParticolare, ISNULL(ec.DisegnoGrezzo, N'nd') AS DisegnoGrezzo, 
              ISNULL(ec.Esponente, N'nd') AS Esponente, ISNULL(ec.DescImpianto, N'nd') AS DescImpianto,
              ISNULL(ec.DataFus, N'') AS DataFus, ISNULL(ec.TurnoFus, 0) AS TurnoFus, ec.CodImballo, 
              ec.Qta,  ISNULL(AnagStatiProdotto.DescStato, N'nd') AS DescStato, ec.ModDate, 
              CASE WHEN(ec.IdxPosizione < 0)  THEN 'Consum' ELSE ISNULL(Blocchi.CodMag, N'nd') END AS CodMag, 
              ISNULL(Blocchi.CodBlocco, N'nd') AS CodBlocco, ISNULL(Celle.CodCella, N'nd') AS CodCella, ISNULL(dbo.Celle.IdxCella, 0) AS IdxCella,
              ISNULL(Celle.X, 0) AS X, ISNULL(Celle.Y, 0) AS Y, ISNULL(Celle.Z, 0) AS Z, ec.IdxPosizione, ISNULL(ec.Note,'') AS Note,
			  dbo.PosizioneUdcCorrente.DataRif AS lastMov
FROM          AnagStatiProdotto RIGHT OUTER JOIN
              ElencoCartellini ec ON AnagStatiProdotto.CodStato = ec.CodStato AND AnagStatiProdotto.CodStato = ec.CodStato 
              LEFT OUTER JOIN Celle INNER JOIN PosizioneUdcCorrente ON Celle.IdxCella = PosizioneUdcCorrente.IdxCella 
              AND Celle.IdxCella = PosizioneUdcCorrente.IdxCella AND Celle.IdxCella = PosizioneUdcCorrente.IdxCella 
              INNER JOIN Blocchi ON Celle.IdxBlocco = Blocchi.IdxBlocco INNER JOIN AnagMag ON Blocchi.CodMag = AnagMag.CodMag 
              AND Blocchi.CodCS = AnagMag.CodCS AND Blocchi.CodMag = AnagMag.CodMag AND Blocchi.CodCS = AnagMag.CodCS AND Blocchi.CodMag = AnagMag.CodMag 
              AND Blocchi.CodCS = AnagMag.CodCS ON ec.UDC = PosizioneUdcCorrente.UDC
WHERE        (ec.UDC = @UDC) AND (ec.CodCS = @CodCS)

RETURN