Samuele E. Locatelli
100 lines
2.4 KiB
Transact-SQL
100 lines
2.4 KiB
Transact-SQL
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/***************************************
|
|
* STORED stp_MagFifoByParticolare
|
|
*
|
|
* restituisce elenco giacenze a magazzino in logica FIFO per un dato particolare
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2012.10.01
|
|
*
|
|
****************************************/
|
|
create PROCEDURE stp_MagFifoByParticolare
|
|
(
|
|
@CodCS VARCHAR(2),
|
|
@Particolare VARCHAR(20)
|
|
)
|
|
AS
|
|
|
|
SELECT TOP (100) PERCENT b.IdxBlocco, b.CodBlocco, b.DescBlocco, ec.Particolare,
|
|
COUNT(puc.UDC) AS NumUDC, ISNULL(MIN(puc.DataRif),
|
|
GETDATE()) AS Oldest, ISNULL(MAX(puc.DataRif), GETDATE()) AS Newest, SUM(ec.Qta) AS QtaTotPz
|
|
FROM AnagMag am INNER JOIN
|
|
Blocchi b ON am.CodMag = b.CodMag AND am.CodCS = b.CodCS INNER JOIN
|
|
Celle c INNER JOIN
|
|
ElencoCartellini ec LEFT OUTER JOIN
|
|
PosizioneUdcCorrente puc ON ec.UDC = puc.UDC ON c.IdxCella = puc.IdxCella ON
|
|
b.IdxBlocco = c.IdxBlocco INNER JOIN
|
|
AnagStatiProdotto asp ON ec.CodStato = asp.CodStato
|
|
WHERE (c.Attiva = 1) AND b.CodMag <> 'VMLP' AND (am.CodCS = @CodCS) AND (ec.Particolare = @Particolare)
|
|
GROUP BY b.IdxBlocco, b.CodBlocco, b.DescBlocco, b.NumX, b.NumY, b.NumZ,
|
|
ec.Particolare
|
|
ORDER BY Oldest ASC
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/***************************************
|
|
* STORED stp_UDC_FifoByParticolareBlocco
|
|
*
|
|
* restituisce elenco UDC a magazzino in logica FIFO per un dato particolare+blocco
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2012.10.01
|
|
*
|
|
****************************************/
|
|
create PROCEDURE stp_UDC_FifoByParticolareBlocco
|
|
(
|
|
@CodCS VARCHAR(2),
|
|
@Particolare VARCHAR(20),
|
|
@idxBlocco INT
|
|
)
|
|
AS
|
|
|
|
|
|
|
|
SELECT TOP (100) PERCENT ec.UDC, c.CodCella, puc.DataRif, ec.Qta
|
|
FROM AnagMag am INNER JOIN
|
|
Blocchi b ON am.CodMag = b.CodMag AND am.CodCS = b.CodCS INNER JOIN
|
|
Celle c INNER JOIN
|
|
ElencoCartellini ec LEFT OUTER JOIN
|
|
PosizioneUdcCorrente puc ON ec.UDC = puc.UDC ON c.IdxCella = puc.IdxCella ON
|
|
b.IdxBlocco = c.IdxBlocco INNER JOIN
|
|
AnagStatiProdotto asp ON ec.CodStato = asp.CodStato
|
|
WHERE (c.Attiva = 1) AND b.CodMag <> 'VMLP' AND (am.CodCS = @CodCS) AND (ec.Particolare = @Particolare) AND b.IdxBlocco = @idxBlocco
|
|
GROUP BY ec.UDC, c.CodCella, puc.DataRif, ec.Qta
|
|
ORDER BY puc.DataRif ASC
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(509, GETDATE())
|
|
GO
|