
/***************************************
* STORED stp_getUdcDetailFullCode_storico
*
* ottiene ULTIM dati UDC DA STORICO con i dati significativi partendo da un codice UDC COMPLETO
*
* Steamware, S.E.L.
* mod: 2012.10.01
*
****************************************/
CREATE PROCEDURE [dbo].[stp_getUdcDetailFullCode_storico]
(
	@DataOraFrom	DATETIME,
	@DataOraTo		DATETIME,
	@UDC			NVARCHAR(50),
	@CodCS			NVARCHAR(2)	
)
AS

WITH myCTE AS
(
	SELECT	TOP 1 *
	FROM	StoricoCartellini
	WHERE	DataEv BETWEEN @DataOraFrom AND @DataOraTo
	AND		UDC = @UDC
	ORDER BY DataEv DESC
)

SELECT        stc.UDC, ISNULL(stc.RagSociale, N'nd') AS RagSociale, ISNULL(stc.Particolare, N'nd') AS Particolare,
			  ISNULL(stc.DescParticolare, N'nd') AS DescParticolare, ISNULL(stc.DisegnoGrezzo, N'nd') AS DisegnoGrezzo, 
              ISNULL(stc.Esponente, N'nd') AS Esponente, ISNULL(stc.DescImpianto, N'nd') AS DescImpianto,
              ISNULL(stc.DataFus, N'') AS DataFus, ISNULL(stc.TurnoFus, 0) AS TurnoFus, stc.CodImballo, 
              stc.Qta,  ISNULL(AnagStatiProdotto.DescStato, N'nd') AS DescStato, stc.ModDate, 
              CASE WHEN(stc.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, stc.IdxPosizione, ISNULL(stc.Note,'') AS Note,
			  dbo.PosizioneUdcCorrente.DataRif AS lastMov
FROM          AnagStatiProdotto RIGHT OUTER JOIN
              myCTE stc ON AnagStatiProdotto.CodStato = stc.CodStato AND AnagStatiProdotto.CodStato = stc.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 stc.UDC = PosizioneUdcCorrente.UDC
WHERE        (stc.UDC = @UDC) AND (stc.CodCS = @CodCS)

RETURN
