Samuele E. Locatelli
300 lines
7.0 KiB
Transact-SQL
300 lines
7.0 KiB
Transact-SQL
set xact_abort on
|
|
go
|
|
|
|
begin transaction
|
|
go
|
|
|
|
INSERT INTO dbo.AnagTipoEvento
|
|
VALUES (N'MagPreOdet', N'Prelievo Standard Odette')
|
|
go
|
|
|
|
commit transaction
|
|
go
|
|
|
|
|
|
|
|
set xact_abort on
|
|
go
|
|
|
|
begin transaction
|
|
go
|
|
|
|
INSERT INTO dbo.TipoListaPrelievo
|
|
VALUES (N'04-Odette', N'Lista di Prelievo per Odette TK', N'LPO010101', N'MagPreOdet', 0, 1, N'P')
|
|
go
|
|
|
|
commit transaction
|
|
go
|
|
|
|
set xact_abort on
|
|
go
|
|
|
|
begin transaction
|
|
go
|
|
|
|
DELETE FROM dbo.TipoListaPrelievo WHERE CodTipoLista=N'04-OdetTK'
|
|
go
|
|
|
|
commit transaction
|
|
go
|
|
|
|
|
|
set xact_abort on
|
|
go
|
|
|
|
begin transaction
|
|
go
|
|
|
|
DELETE FROM dbo.TabTranPosizEventi WHERE CodEvento=N'MagPreGbox' and IdxPosizione=24
|
|
INSERT INTO dbo.TabTranPosizEventi
|
|
VALUES (N'MagPreOdet', 24, 200, 0, NULL, N'LDP Odette')
|
|
INSERT INTO dbo.TabTranPosizEventi
|
|
VALUES (N'MagPreOdet', 200, 24, 0, NULL, N'LDP Odette - Reset')
|
|
go
|
|
|
|
commit transaction
|
|
go
|
|
|
|
|
|
set xact_abort on
|
|
go
|
|
|
|
begin transaction
|
|
go
|
|
|
|
UPDATE dbo.TabTranPosizEventi SET
|
|
Note=N'LDP a Cliente'
|
|
WHERE CodEvento=N'MagPreFin' and IdxPosizione=24
|
|
INSERT INTO dbo.TabTranPosizEventi
|
|
VALUES (N'MagPreFin', 200, 24, 0, NULL, N'LDP a Cliente - Reset')
|
|
go
|
|
|
|
commit transaction
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/*****************************************
|
|
* STORED stp_ODETTE_upsertUdc
|
|
*
|
|
* effettua upsert x l'UDC (ovver crea nuovo solo se non c'è...)
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2011.04.28
|
|
*
|
|
****************************************/
|
|
alter PROCEDURE stp_ODETTE_upsertUdc
|
|
(
|
|
@UDC NVARCHAR(50),
|
|
@CodCS VARCHAR(2),
|
|
@numBolla NVARCHAR(6),
|
|
@dataBolla NVARCHAR(8),
|
|
@CodMag NVARCHAR(2),
|
|
@GrpBolla NVARCHAR(1),
|
|
@Particolare NVARCHAR(15),
|
|
@CodLista NVARCHAR(12),
|
|
@IndStabFrom NVARCHAR(50),
|
|
@qtaCont NVARCHAR(50)
|
|
)
|
|
AS
|
|
|
|
|
|
-- DECLARE iniziali x gestione contatori odette
|
|
DECLARE @Flusso AS VARCHAR(2)
|
|
DECLARE @Anno AS VARCHAR(2)
|
|
DECLARE @numOdette AS INT
|
|
DECLARE @nextOdette AS INT
|
|
|
|
------------------------------------------------------------------------------------------------------
|
|
-- Fix flusso ed anno x 'OD' e '00' (contatore non spezzato su anno...
|
|
------------------------------------------------------------------------------------------------------
|
|
SET @Flusso = 'OD'
|
|
SET @Anno = '00'
|
|
|
|
------------------------------------------------------------------------------------------------------
|
|
-- Contatore Odette
|
|
------------------------------------------------------------------------------------------------------
|
|
-- controllo se ci sia già un odette per company / flusso / anno
|
|
BEGIN TRAN
|
|
-- cerco nella tab contatori UDC l'ultimo valido
|
|
/*
|
|
SET @numOdette = (
|
|
SELECT COUNT(*)
|
|
FROM Odette
|
|
WHERE CodCS = @CodCS
|
|
)
|
|
*/
|
|
-- controllo se record c'è...
|
|
IF EXISTS(
|
|
SELECT *
|
|
FROM ContatoriUdc
|
|
WHERE CodCS = @CodCS
|
|
AND Flusso = @Flusso
|
|
AND Anno = @Anno
|
|
)
|
|
BEGIN
|
|
-- se c'è incremento di 1 in tab
|
|
UPDATE ContatoriUdc
|
|
SET LastIdx = LastIdx + 1
|
|
WHERE CodCS = @CodCS
|
|
AND Flusso = @Flusso
|
|
AND Anno = @Anno
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
-- lo creo!
|
|
INSERT INTO ContatoriUdc(CodCS, Flusso, Anno, LastIdx)
|
|
VALUES (@CodCS, @Flusso, @Anno, 1)
|
|
END
|
|
-- aggiorno numero UDC
|
|
SET @numOdette = (
|
|
SELECT LastIdx
|
|
FROM ContatoriUdc
|
|
WHERE CodCS = @CodCS
|
|
AND Flusso = @Flusso
|
|
AND Anno = @Anno
|
|
)
|
|
COMMIT TRAN
|
|
|
|
-- cerco se ci sia già la riga Odette
|
|
DECLARE @udcTrovati AS INT
|
|
SET @udcTrovati = ( SELECT COUNT(*) FROM Odette WHERE CampoUDC = @UDC )
|
|
|
|
-- controllo se esista già... se non c'è creo
|
|
IF (@udcTrovati = 0)
|
|
BEGIN
|
|
INSERT INTO Odette(CampoUDC, StatoOk, CodCS)
|
|
VALUES(@UDC, 0, @CodCS)
|
|
END
|
|
|
|
-- calcolo valori x update
|
|
DECLARE @DataB AS NVARCHAR(7)
|
|
SET @DataB = ( SELECT 'D'+ SUBSTRING(@dataBolla, 3, 6) )
|
|
DECLARE @udcShort NVARCHAR(10)
|
|
SET @udcShort = dbo.f_onlyNumbers(@UDC)
|
|
|
|
-- update valori (sovrascrivo...)
|
|
UPDATE Odette
|
|
SET Campo3_2 = @numBolla,
|
|
Campo3_3 = @numBolla,
|
|
Campo4_2 = @IndStabFrom,
|
|
Campo9_2 = @qtaCont,
|
|
Campo9_3 = @qtaCont,
|
|
Campo13_2 = @DataB,
|
|
Campo15_2 = @numOdette,
|
|
Campo15_3 = @numOdette,
|
|
Campo16_2 = @udcShort, --@UDC, -- DA VERICARE, mettere UDC da anno in poi?!? @udcShort calcolato sopra? magari è sbagliato...
|
|
CodMag = @CodMag,
|
|
GrpBolla = @GrpBolla,
|
|
DataBolla = CAST(@dataBolla AS NUMERIC(8,0)),
|
|
NumBolla = CAST(@numBolla AS NUMERIC(6,0)),
|
|
Particolare = @Particolare,
|
|
CodLista = @CodLista
|
|
WHERE CampoUDC = @UDC
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/***************************************
|
|
* STORED stp_selDestListePrelByConditio
|
|
*
|
|
* Ottiene i destinatari delle liste di prelievo data la condizione indicata
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2010.11.02
|
|
*
|
|
****************************************/
|
|
alter PROCEDURE stp_selDestListePrelByConditio
|
|
(
|
|
@conditio NVARCHAR(20)
|
|
)
|
|
AS
|
|
|
|
IF (@conditio='01-PreFus')
|
|
BEGIN
|
|
SELECT CAST('*' AS NVARCHAR(6)) AS value, CAST('*' AS NVARCHAR(50)) AS label, '01-PreFus' AS conditio
|
|
END
|
|
|
|
ELSE IF (@conditio='01-PreAnim')
|
|
BEGIN
|
|
SELECT CAST(CodCliente AS NVARCHAR(6)) AS value, CAST(CodCliente AS NVARCHAR(6)) +'- ' +CAST(RagSociale AS NVARCHAR(50)) AS label, '01-PreAnim' AS conditio
|
|
FROM RilPro.AnagClienti
|
|
ORDER BY label
|
|
END
|
|
|
|
ELSE IF (@conditio='02-PreCli')
|
|
BEGIN
|
|
SELECT CAST(CodCliente AS NVARCHAR(6)) AS value, CAST(CodCliente AS NVARCHAR(6)) +'- ' +CAST(RagSociale AS NVARCHAR(50)) AS label, '02-PreCli' AS conditio
|
|
FROM RilPro.AnagClienti
|
|
ORDER BY label
|
|
END
|
|
|
|
ELSE IF (@conditio='04-Odette')
|
|
BEGIN
|
|
SELECT CAST(CodCliente AS NVARCHAR(6)) AS value, CAST(CodCliente AS NVARCHAR(6)) +'- ' +CAST(RagSociale AS NVARCHAR(50)) AS label, '04-Odette' AS conditio
|
|
FROM RilPro.AnagClienti
|
|
ORDER BY label
|
|
END
|
|
|
|
ELSE IF (@conditio='05-TerAn')
|
|
BEGIN
|
|
SELECT CAST(CodTerzista AS NVARCHAR(6)) AS value, CAST(CodTerzista AS NVARCHAR(6))+ '- '+CAST(DescTerzista AS NVARCHAR(50)) AS label, '05-TerAn' AS conditio
|
|
FROM RilPro.AnagDepositi
|
|
ORDER BY label
|
|
END
|
|
|
|
ELSE IF (@conditio='03-TerWip')
|
|
BEGIN
|
|
SELECT CAST(CodTerzista AS NVARCHAR(6)) AS value, CAST(CodTerzista AS NVARCHAR(6))+ '- '+CAST(DescTerzista AS NVARCHAR(50)) AS label, '03-TerWip' AS conditio
|
|
FROM RilPro.AnagDepositi
|
|
ORDER BY label
|
|
END
|
|
|
|
ELSE -- Se non configurata prendo elenco destinatari esterni
|
|
BEGIN
|
|
SELECT '-99999' AS value, 'STORED [stp_selDestListePrelByConditio] da configurare!!!' AS label, '999-ND' AS conditio
|
|
FROM RilPro.AnagDepositi
|
|
ORDER BY label
|
|
END
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(533, GETDATE())
|
|
GO
|
|
SELECT TOP 5 * FROM LogUpdateDb ORDER BY Versione DESC
|
|
GO
|