Samuele E. Locatelli
284 lines
7.6 KiB
Transact-SQL
284 lines
7.6 KiB
Transact-SQL
/***************************************
|
|
* STORED stp_processaCodaConsumoMP
|
|
*
|
|
* Processa la coda dei consumo di UDC di MP e chiaam procedura x salvataggio dato su AS400
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2010.10.12
|
|
*
|
|
****************************************/
|
|
ALTER PROCEDURE [BatchQueue].[stp_processaCodaConsumoMP]
|
|
(
|
|
@ExecBatchInter BIT -- 0 Eseguita in Batch , 1 in Interattivo
|
|
)
|
|
AS
|
|
-- BOZZA DA VERIFICARE E COMPLETARE A CURA MAZZUCCONI!!! PERCHE'??? NON VA VERSO AS400!!!
|
|
|
|
-- faccio una query con un cursore x processare tutte le righe da inviare ad AS400..
|
|
DECLARE @UDC NVARCHAR(50) -- UDC SQL
|
|
DECLARE @XRPQLN INT -- UDC
|
|
DECLARE @XNRETI NVARCHAR(10) -- Progr UDC AS400
|
|
DECLARE @XDTPRO INT -- DataPrelievo
|
|
DECLARE @XTRPRO NVARCHAR(5) -- Turno Prelievo
|
|
DECLARE @XDESMT NVARCHAR(3) -- Destinazione Lega
|
|
DECLARE @XCDTER NVARCHAR(6) -- Cod. Terzista
|
|
|
|
-- Dichiaro e carico il cursore
|
|
DECLARE UdcMpList CURSOR LOCAL FOR
|
|
SELECT UDC FROM AS400_BatchConsumoMP WHERE DataInvioAs IS NULL
|
|
|
|
OPEN UdcMpList
|
|
FETCH NEXT FROM UdcMpList
|
|
INTO @UDC
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
-- controllo ci sia la riga
|
|
IF (SELECT COUNT(UDC) FROM AS400_BatchConsumoMP WHERE UDC=@UDC) > 0
|
|
BEGIN
|
|
-- carico valori che mi servono
|
|
SELECT @XRPQLN=rq.nRapQual, @XNRETI=rq.ProgUDC, @XDTPRO=bcu.DataPrelFus, @XTRPRO=bcu.TurnoPrelFus,
|
|
@XDESMT=bcu.DestLega, @XCDTER=bcu.DestTerz
|
|
FROM AS400_BatchConsumoMP as bcu
|
|
INNER JOIN RilPro.RapQual as rq ON bcu.UDC = rq.UDC
|
|
WHERE bcu.UDC=@UDC
|
|
|
|
-- BEGIN TRAN qui non va messa perchè se verso AS400 le transazioni non sono gestite
|
|
-- MANCA IL CONTROLLO SE L'AGGIORNAMENTO E' STATO OK
|
|
DECLARE @ScritturaASOK AS INT
|
|
EXECUTE @ScritturaASOK = BatchQueue.stp_consumaMP @XRPQLN ,@XNRETI ,@XDTPRO ,@XTRPRO ,@XDESMT ,@XCDTER
|
|
|
|
-- registro la data del movimento BATCH solo se stp_consumaMP è ritorna OK
|
|
-- NON ANDAVA BENE PERCHE' SE SUCCESSIVA ALL' ERRORE LA ESEGUE CMQ
|
|
-- Così non aggiorna la tabella MA L'UTENTE NON SI ACCORGE DI NULLA!!!
|
|
IF @ScritturaASOK = 0
|
|
UPDATE AS400_BatchConsumoMP SET DataInvioAs = GETDATE() WHERE UDC = @UDC
|
|
|
|
FETCH NEXT FROM UdcMpList INTO @UDC
|
|
END
|
|
END
|
|
CLOSE UdcMpList
|
|
DEALLOCATE UdcMpList
|
|
|
|
-- aggiunta per dare errore se schedulata S.Possidonio!!! GCARLO
|
|
IF @ScritturaASOK <> 0 AND @ExecBatchInter = 0
|
|
BEGIN
|
|
DECLARE @ErrorMessage NVARCHAR(4000);
|
|
DECLARE @ErrorSeverity INT;
|
|
DECLARE @ErrorState INT;
|
|
|
|
SELECT
|
|
@ErrorMessage = ERROR_MESSAGE()+ ' '+ CONVERT( VARCHAR(10),ERROR_NUMBER()),
|
|
@ErrorSeverity = ERROR_SEVERITY(),
|
|
@ErrorState = ERROR_STATE();
|
|
|
|
RAISERROR (@ErrorMessage, -- Message text.
|
|
16, -- Severity.
|
|
@ErrorState -- State.
|
|
);
|
|
END
|
|
RETURN
|
|
|
|
|
|
set xact_abort on
|
|
go
|
|
|
|
begin transaction
|
|
go
|
|
|
|
set ANSI_NULLS on
|
|
go
|
|
|
|
/***************************************
|
|
* STORED stp_prtCartMatPrimaByUDC
|
|
*
|
|
* ottiene il record del Cartellino Materia Prima dato l'UDC richiesto x la sstampa
|
|
*
|
|
****************************************/
|
|
alter PROCEDURE stp_prtCartMatPrimaByUDC
|
|
(
|
|
@UDC VARCHAR(50)
|
|
)
|
|
AS
|
|
SELECT
|
|
RQ.nRapQual,
|
|
RQ.DataRapQual,
|
|
RQ.ProgUDC,
|
|
RQ.CodFor,
|
|
RQ.DestTerz,
|
|
RQ.CodLega,
|
|
RQ.DataPrelFus,
|
|
RQ.TurnoPrelFus,
|
|
RQ.Qta,
|
|
RQ.DestLega,
|
|
RQ.LegaScaric,
|
|
RQ.BenesQual,
|
|
RQ.UDC,
|
|
Fo.DescFornitore,
|
|
Fo.DescFornitore2,
|
|
Le.CodLega AS CodLega2,
|
|
Le.DescLega,
|
|
Le.DescLega2
|
|
FROM RilPro.RapQual AS RQ
|
|
LEFT JOIN RilPro.AnagFornitori AS Fo
|
|
ON RQ.CodFor = Fo.CodFornitore
|
|
LEFT JOIN RilPro.AnagLeghe Le
|
|
ON RQ.CodLega = 'L' + Le.CodLega
|
|
WHERE RQ.UDC = RTRIM(@UDC) -- 'UB31000000001'
|
|
RETURN
|
|
go
|
|
|
|
commit
|
|
go
|
|
|
|
set xact_abort on
|
|
go
|
|
|
|
begin transaction
|
|
go
|
|
|
|
set ANSI_NULLS on
|
|
go
|
|
|
|
/***************************************
|
|
* STORED stp_rappQualConsumabili
|
|
*
|
|
* Ottiene l'elenco dei record di rapp qualità (e quindi degli UDC) di AS non ancora consumati per range di data di approvazione
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2010.10.05
|
|
*
|
|
****************************************/
|
|
alter PROCEDURE stp_rappQualConsumabili
|
|
(
|
|
@dataFrom DATETIME,
|
|
@dataTo DATETIME
|
|
)
|
|
|
|
AS
|
|
-- restituisce le righe richieste
|
|
SELECT nRapQual, DataRapQual, ProgUDC, CodFor, DestTerz, CodLega, DataPrelFus, TurnoPrelFus, Qta, DestLega, LegaScaric, BenesQual, UDC
|
|
FROM RilPro.RapQual
|
|
WHERE ((DataPrelFus IS NULL) OR (DataPrelFus = 0)) AND -- condizione sul NON aver fatto ancora il prelievo
|
|
((DataRapQual >= dbo.dateToAsFormat(@dataFrom)) AND (DataRapQual < dbo.dateToAsFormat(@dataTo))) -- cond data RQ
|
|
AND (ISNULL(UDC,'') <>'') -- non nullo UDC, quindi stampati...
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit
|
|
go
|
|
|
|
|
|
set xact_abort on
|
|
go
|
|
|
|
begin transaction
|
|
go
|
|
|
|
set ANSI_NULLS on
|
|
go
|
|
|
|
-- Batch submitted through debugger: GMW_00290.sql|183|0|C:\Users\RottGian\AppData\Local\Temp\2\Temp1_GMW_00290.zip\GMW_00290.sql
|
|
|
|
/***************************************
|
|
* 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='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
|
|
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
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit
|
|
go
|
|
|
|
|
|
set xact_abort on
|
|
go
|
|
|
|
begin transaction
|
|
go
|
|
|
|
set ANSI_NULLS on
|
|
go
|
|
|
|
-- Batch submitted through debugger: GMW_00290.sql|183|0|C:\Users\RottGian\AppData\Local\Temp\2\Temp1_GMW_00290.zip\GMW_00290.sql
|
|
|
|
/***************************************
|
|
* 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='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
|
|
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
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(345, GETDATE())
|
|
GO
|