Samuele E. Locatelli
460 lines
18 KiB
PL/PgSQL
460 lines
18 KiB
PL/PgSQL
SET NUMERIC_ROUNDABORT OFF
|
|
GO
|
|
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
|
|
GO
|
|
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
|
|
GO
|
|
CREATE TABLE #tmpErrors (Error int)
|
|
GO
|
|
SET XACT_ABORT ON
|
|
GO
|
|
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
|
|
GO
|
|
BEGIN TRANSACTION
|
|
GO
|
|
PRINT N'Dropping constraints from [RawData].[BORI202J]'
|
|
GO
|
|
ALTER TABLE [RawData].[BORI202J] DROP CONSTRAINT [PK_BORI202J]
|
|
GO
|
|
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
|
|
GO
|
|
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
|
|
GO
|
|
PRINT N'Rebuilding [RawData].[BORI202J]'
|
|
GO
|
|
CREATE TABLE [RawData].[tmp_rg_xx_BORI202J]
|
|
(
|
|
[CDMFS] [nvarchar] (2) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[GRBOS] [nvarchar] (1) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[DTBOS] [numeric] (8, 0) NOT NULL,
|
|
[NRBOS] [numeric] (6, 0) NOT NULL,
|
|
[CDARS] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[NRRGS] [numeric] (5, 0) NOT NULL,
|
|
[CDINB] [nvarchar] (6) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[RA1ND] [nvarchar] (35) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[RA2ND] [nvarchar] (35) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[CAPND] [nvarchar] (9) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[CITND] [nvarchar] (25) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[RASCL] [nvarchar] (35) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[INDCL] [nvarchar] (35) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[CAPCL] [nvarchar] (9) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[LOCCL] [nvarchar] (25) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[PROCL] [nvarchar] (2) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[INEND] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[CRAGCL] [nvarchar] (4) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[TELND] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[CCDMOL] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[CCDPTR] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[EPLAVO] [numeric] (7, 3) NOT NULL,
|
|
[NRC1B] [numeric] (5, 0) NOT NULL,
|
|
[ECMM01] [nvarchar] (30) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[DEPAR] [nvarchar] (30) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[RCARTC] [nvarchar] (30) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[PERND] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[CCDET1] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[ECDNAE] [nvarchar] (6) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[ECDNA1] [nvarchar] (1) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[EESPMD] [nvarchar] (6) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[PELDB] [numeric] (7, 2) NOT NULL,
|
|
[QTCNS] [numeric] (9, 2) NOT NULL,
|
|
[ECLAQP] [nchar] (1) COLLATE Latin1_General_CI_AS NOT NULL DEFAULT '',
|
|
[EPNETT] [numeric] (7, 3) NOT NULL DEFAULT 0,
|
|
[FCDI01] [nvarchar] (35) COLLATE Latin1_General_CI_AS NOT NULL DEFAULT '',
|
|
[OCDODM] [varchar] (15) COLLATE Latin1_General_CI_AS NOT NULL DEFAULT ''
|
|
)
|
|
|
|
-- Add a new column to the table BORI202J
|
|
ALTER TABLE RawData.BORI202J
|
|
ADD ECLAQP NCHAR(1) NOT NULL DEFAULT ''
|
|
,EPNETT NUMERIC(7,3) NOT NULL DEFAULT 0
|
|
,FCDI01 NVARCHAR(35) NOT NULL DEFAULT ''
|
|
,OCDODM VARCHAR(15) NOT NULL DEFAULT ''
|
|
GO
|
|
GO
|
|
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
|
|
GO
|
|
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
|
|
GO
|
|
INSERT INTO [RawData].[tmp_rg_xx_BORI202J]([CDMFS], [GRBOS], [DTBOS], [NRBOS], [CDARS], [NRRGS], [CDINB], [RA1ND], [RA2ND], [CAPND], [CITND], [RASCL], [INDCL], [CAPCL], [LOCCL], [PROCL], [INEND], [CRAGCL], [TELND], [CCDMOL], [CCDPTR], [EPLAVO], [NRC1B], [ECMM01], [DEPAR], [RCARTC], [PERND], [CCDET1], [ECDNAE], [ECDNA1], [EESPMD], [PELDB], [QTCNS]) SELECT [CDMFS], [GRBOS], [DTBOS], [NRBOS], [CDARS], [NRRGS], [CDINB], [RA1ND], [RA2ND], [CAPND], [CITND], [RASCL], [INDCL], [CAPCL], [LOCCL], [PROCL], [INEND], [CRAGCL], [TELND], [CCDMOL], [CCDPTR], [EPLAVO], [NRC1B], [ECMM01], [DEPAR], [RCARTC], [PERND], [CCDET1], [ECDNAE], [ECDNA1], [EESPMD], [PELDB], [QTCNS] FROM [RawData].[BORI202J]
|
|
GO
|
|
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
|
|
GO
|
|
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
|
|
GO
|
|
DROP TABLE [RawData].[BORI202J]
|
|
GO
|
|
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
|
|
GO
|
|
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
|
|
GO
|
|
EXEC sp_rename N'[RawData].[tmp_rg_xx_BORI202J]', N'BORI202J'
|
|
GO
|
|
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
|
|
GO
|
|
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
|
|
GO
|
|
PRINT N'Creating primary key [PK_BORI202J] on [RawData].[BORI202J]'
|
|
GO
|
|
ALTER TABLE [RawData].[BORI202J] ADD CONSTRAINT [PK_BORI202J] PRIMARY KEY CLUSTERED ([CDMFS], [GRBOS], [DTBOS], [NRBOS], [CDARS], [NRRGS])
|
|
GO
|
|
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
|
|
GO
|
|
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
|
|
GO
|
|
PRINT N'Altering [dbo].[f_getLikedAS400]'
|
|
GO
|
|
|
|
|
|
|
|
-- =============================================
|
|
-- Author: Gcarlo
|
|
-- Create date:
|
|
-- Description: In base al database corrente passato ritorno il corretto linked Server AS400
|
|
-- =============================================
|
|
ALTER FUNCTION [dbo].[f_getLikedAS400]
|
|
(
|
|
@CurrentDB nVarchar(128) -- Database Corrente
|
|
)
|
|
RETURNS nvarchar(50)
|
|
AS
|
|
BEGIN
|
|
-- Declare the return variable here
|
|
DECLARE @As400File nvarchar(50);
|
|
|
|
-- Valorizzo il corretto Linked Server in base al database passato
|
|
SET @As400File =
|
|
CASE
|
|
-- LINKED SERVER AS400 S.POSSIDONIO
|
|
WHEN ( LEFT(@CurrentDB,3) = 'SP_' ) THEN 'AS400POSS' -- S.POSSIDONIO
|
|
-- LINKED SERVER AS400 TEKAL
|
|
WHEN ( LEFT(@CurrentDB,3) = 'TK_' ) THEN 'AS400TEKAL' -- TEKAL
|
|
|
|
ELSE ''
|
|
END
|
|
|
|
-- Return the result of the function
|
|
RETURN @As400File
|
|
|
|
END
|
|
|
|
|
|
GO
|
|
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
|
|
GO
|
|
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
|
|
GO
|
|
PRINT N'Altering [dbo].[f_getLibreriaAS400]'
|
|
GO
|
|
|
|
|
|
-- =============================================
|
|
-- Author: Gcarlo
|
|
-- Create date:
|
|
-- Description: In base al database corrente passato ritorno la corretta libreria di AS400
|
|
-- che deve essere utilizzata
|
|
-- =============================================
|
|
ALTER FUNCTION [dbo].[f_getLibreriaAS400]
|
|
(
|
|
@CurrentDB nVarchar(128)
|
|
,@Function nVarchar(50)
|
|
)
|
|
RETURNS nvarchar(100)
|
|
AS
|
|
BEGIN
|
|
-- FORSE MEGLIO USARE IL NOME DELLA STORED CHE LA RICHIAMA CON ... nella stored che la richiama
|
|
-- DECLARE @StoredName AS NVARCHAR(256);
|
|
-- SET @StoredName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
|
|
-- Declare the return variable here
|
|
DECLARE @As400File nvarchar(100)
|
|
|
|
-- Valorizzo la corretta libreria in base al database passato
|
|
SET @As400File =
|
|
CASE
|
|
-- impostazione tramite stored che richiama la function
|
|
WHEN @Function = 'dbo.stp_BORI_caricaDatiBolla' THEN -- carica dati bolla
|
|
CASE
|
|
-- S.POSSIDONIO
|
|
WHEN ( @CurrentDB = 'SP_GMW' ) THEN '??.BORI202J'
|
|
WHEN ( @CurrentDB = 'SP_GMWTest' ) THEN '??.BORI202J' -- TEST SP
|
|
-- TEKAL
|
|
WHEN ( @CurrentDB = 'TK_GMW' ) THEN 'TEKA_AZI.BORI202J'
|
|
WHEN ( @CurrentDB = 'TK_GMWTest' ) THEN 'TEKA_AZI.BORI202J' -- TEST TEKAL
|
|
WHEN ( @CurrentDB = 'TK_GMWTestCopy' ) THEN 'TEKA_AZI.BORI202J' -- TEST TEKAL
|
|
ELSE 'DB_NON_TROVATO'
|
|
END
|
|
|
|
WHEN @Function = 'BatchQueue.stp_batch_RapQual_S01' THEN -- legge rapporti qualita
|
|
CASE
|
|
-- S.POSSIDONIO
|
|
WHEN ( @CurrentDB = 'SP_GMW' ) THEN ''
|
|
WHEN ( @CurrentDB = 'SP_GMWTest' ) THEN '' -- TEST SP
|
|
-- TEKAL
|
|
WHEN ( @CurrentDB = 'TK_GMW' ) THEN 'TEKA_DATV3.XOGIX00F'
|
|
WHEN ( @CurrentDB = 'TK_GMWTest' ) THEN 'TEKA_DATV3.XOGIX00F' -- TEST TEKAL
|
|
WHEN ( @CurrentDB = 'TK_GMWTestCopy' ) THEN 'TEKA_DATV3.XOGIX00F' -- TEST TEKAL
|
|
ELSE 'DB_NON_TROVATO'
|
|
END
|
|
|
|
WHEN @Function = 'XOGIX' THEN -- Materia Prima - Rapporti Qualità
|
|
CASE
|
|
-- S.POSSIDONIO
|
|
WHEN ( @CurrentDB = 'SP_GMW' ) THEN 'POSN_DATV3.XOGIX00F'
|
|
WHEN ( @CurrentDB = 'SP_GMWTest' ) THEN 'MAZZT_DTV3.XOGIX00F' -- TEST SP
|
|
-- TEKAL
|
|
WHEN ( @CurrentDB = 'TK_GMW' ) THEN 'TEKA_DATV3.XOGIX00F'
|
|
WHEN ( @CurrentDB = 'TK_GMWTest' ) THEN 'MAZZT_DTV3.XOGIX00F' -- TEST TEKAL
|
|
ELSE 'DB_NON_TROVATO'
|
|
END
|
|
|
|
WHEN @Function = 'REGMOVMAG' THEN -- Registrazione Movimenti Magazzino
|
|
CASE
|
|
-- S.POSSIDONIO
|
|
WHEN ( @CurrentDB = 'SP_GMW' ) THEN 'POSN_DATV3...'
|
|
WHEN ( @CurrentDB = 'SP_GMWTest' ) THEN 'MAZZT_DTV3.MWMV230F' -- TEST SP
|
|
-- TEKAL
|
|
WHEN ( @CurrentDB = 'TK_GMW' ) THEN 'TEKA_DATV3.MWMV260F'
|
|
WHEN ( @CurrentDB = 'TK_GMWTest' ) THEN 'MAZZT_DTV3.MWMV230F' -- TEST TEKAL
|
|
ELSE 'DB_NON_TROVATO'
|
|
END
|
|
|
|
ELSE 'FUNZ_NON_TROVATA'
|
|
END
|
|
|
|
-- Return the result of the function
|
|
RETURN @As400File
|
|
|
|
END
|
|
|
|
GO
|
|
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
|
|
GO
|
|
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
|
|
GO
|
|
PRINT N'Creating [RawData].[ANODM00F]'
|
|
GO
|
|
CREATE TABLE [RawData].[ANODM00F]
|
|
(
|
|
[OATV08] [nchar] (1) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[OCDPAR] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[OCDODM] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL,
|
|
[ODTVAL] [numeric] (8, 0) NOT NULL
|
|
)
|
|
GO
|
|
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
|
|
GO
|
|
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
|
|
GO
|
|
PRINT N'Creating primary key [PK_ANODM00F] on [RawData].[ANODM00F]'
|
|
GO
|
|
ALTER TABLE [RawData].[ANODM00F] ADD CONSTRAINT [PK_ANODM00F] PRIMARY KEY CLUSTERED ([OCDPAR], [ODTVAL])
|
|
GO
|
|
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
|
|
GO
|
|
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
|
|
GO
|
|
PRINT N'Altering [dbo].[stp_BORI_caricaDatiBolla]'
|
|
GO
|
|
|
|
/*****************************************
|
|
* STORED stp_BORI_caricaDatiBolla
|
|
*
|
|
* richiama la stored di caricamento da AS400 x la bolla indicata
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2011.04.28
|
|
*
|
|
****************************************/
|
|
ALTER PROCEDURE [dbo].[stp_BORI_caricaDatiBolla]
|
|
(
|
|
@RaggrMagazz NVARCHAR(2) ,
|
|
@GruppoBolle NVARCHAR(1) ,
|
|
@NumeroBolla NVARCHAR(6) ,
|
|
@DataBolla NVARCHAR(8) ,
|
|
@Particolare NVARCHAR(15)
|
|
)
|
|
AS
|
|
|
|
SET NOCOUNT ON;
|
|
|
|
--DECLARE @RaggrMagazz AS NVARCHAR(2) = N'TK'
|
|
--DECLARE @GruppoBolle AS NVARCHAR(1) = N'0'
|
|
--DECLARE @NumeroBolla AS NVARCHAR(6) = N'678'
|
|
--DECLARE @DataBolla AS NVARCHAR(8) = N'20110421'
|
|
--DECLARE @Particolare AS NVARCHAR(15) = N'P00528536146'
|
|
|
|
-- ATTENZIONE!!! i parametri non devono essere nulli altrimenti la stringa ritornata è nulla
|
|
|
|
-- Prima cancello i dati della se già presenti bolla poi li carico
|
|
DELETE RawData.BORI202J
|
|
WHERE CDMFS = @RaggrMagazz
|
|
AND GRBOS = @GruppoBolle
|
|
AND NRBOS = @NumeroBolla
|
|
AND DTBOS = @DataBolla
|
|
AND CDARS = @Particolare
|
|
|
|
DECLARE @SQL AS NVARCHAR(4000);
|
|
DECLARE @SqlOK AS INT;
|
|
DECLARE @As400File AS NVARCHAR(50) = ''; -- Libreria e File AS400
|
|
DECLARE @As400Linked AS NVARCHAR(50) = ''; -- Linked Server AS400
|
|
|
|
SET @SqlOK = 0;
|
|
|
|
/*
|
|
Prima estraggo i dati della bolla dal file in AS400 in una tabella temporanea
|
|
Poi va letta la tabella temporanea per creare i dati per la stampa Odette
|
|
NON VA BENE, SE ESEGUONO IN DUE LA STAMPA PUO ANDARE IN CONFLITTO
|
|
*/
|
|
|
|
--IF OBJECT_ID(N'tempdb..##TabTemp') IS NOT NULL -- ##TabTemp Tabella temporanea globale altrimenti non riesco ad usarla
|
|
-- DROP TABLE ##TabTemp;
|
|
-- SET @SQL = 'SELECT * INTO ##TabTemp FROM OPENQUERY(AS400TEKAL,
|
|
|
|
-- In base al nome del DB allora setto Linked Server e file di AS400
|
|
SET @As400Linked = ( SELECT dbo.f_getLikedAS400(DB_NAME()) );
|
|
-- SET @As400File = ( SELECT dbo.f_getLibreriaAS400 ( DB_NAME() , OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) ) )
|
|
SET @As400File = ( SELECT dbo.f_getLibreriaAS400 ( DB_NAME(), 'dbo.stp_BORI_caricaDatiBolla' ) );
|
|
|
|
BEGIN TRY
|
|
|
|
SET @SQL = 'INSERT INTO RawData.BORI202J SELECT * FROM OPENQUERY(' -- OPENQUERY(AS400TEKAL,'SELECT ...
|
|
SET @SQL = @SQL + @As400Linked + ',
|
|
''SELECT
|
|
CDMFS
|
|
,GRBOS
|
|
,DTBOS
|
|
,NRBOS
|
|
,CDARS
|
|
,NRRGS
|
|
,CDINB
|
|
,RA1ND
|
|
,RA2ND
|
|
,CAPND
|
|
,CITND
|
|
,RASCL
|
|
,INDCL
|
|
,CAPCL
|
|
,LOCCL
|
|
,PROCL
|
|
,INEND
|
|
,CRAGCL
|
|
,TELND
|
|
,CCDMOL
|
|
,CCDPTR
|
|
,EPLAVO
|
|
,NRC1B
|
|
,ECMM01
|
|
,DEPAR
|
|
,RCARTC
|
|
,PERND
|
|
,CCDET1
|
|
,ECDNAE
|
|
,ECDNA1
|
|
,EESPMD
|
|
,PELDB
|
|
,QTCNS
|
|
,ECLAQP
|
|
,EPNETT
|
|
,FCDI01
|
|
,'''''''' AS OCDODM
|
|
FROM ' + @As400File
|
|
|
|
SET @SQL = @SQL + ' WHERE ATV08 <> ''''A''''' -- escludo bolle annullate
|
|
|
|
-- commemntare le righe successive per estrarre tutto
|
|
-- Parametri di selezione della bolla
|
|
SET @SQL = @SQL + ' AND CDMFS = ''''' + @RaggrMagazz + ''''' '
|
|
SET @SQL = @SQL + ' AND GRBOS = ''''' + @GruppoBolle + ''''' '
|
|
SET @SQL = @SQL + ' AND NRBOS = ' + @NumeroBolla + ' '
|
|
SET @SQL = @SQL + ' AND DTBOS = ' + @DataBolla + ' '
|
|
SET @SQL = @SQL + ' AND CDARS = ''''' + @Particolare + ''''' '
|
|
|
|
SET @SQL = @SQL + ''' )';
|
|
|
|
EXEC dbo.sp_executesql @SQL
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
-- PRINT ERROR_NUMBER()
|
|
SET @SqlOK = ERROR_NUMBER();
|
|
|
|
-- Uso RAISERROR mandare in errore la procedura e x per tornare le info
|
|
-- sull'errore originale che ha portato l'esecuzione nel blocco CATCH
|
|
--DECLARE @ErrorMessage NVARCHAR(4000);
|
|
--DECLARE @ErrorSeverity INT;
|
|
--DECLARE @ErrorState INT;
|
|
--SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();
|
|
--RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState); -- Se non uso RAISERROR la procedura non mi da errore es. se schedulata
|
|
END CATCH
|
|
|
|
-- Memorizzo il comando per verifica T-SQL e l'esito del comando se 0 è OK
|
|
INSERT INTO RawData.AS400_Comandi
|
|
SELECT GETDATE(), 'stp_BORI_caricaDatiBolla', @SQL, @SqlOK;
|
|
|
|
/* NEW 2013/05/01
|
|
Aggiorno il change index in base alla data validità e data bolla
|
|
leggo dalla tabella RawData.ANODM00F aggiornata da import AS400 ( potrei aggiornarla anche qui )
|
|
*/
|
|
IF @SqlOK = 0 -- se import Bolle è OK
|
|
BEGIN
|
|
;WITH cte AS
|
|
(
|
|
SELECT OCDPAR,
|
|
OCDODM,
|
|
ODTVAL,
|
|
ISNULL(( SELECT MIN(ODTVAL)
|
|
FROM RawData.ANODM00F AS T2
|
|
WHERE T2.OCDPAR = v.OCDPAR AND T2.ODTVAL > v.ODTVAL )
|
|
,'20993112') AS ODTVALEND
|
|
FROM RawData.ANODM00F AS v
|
|
)
|
|
UPDATE b
|
|
SET OCDODM = c.OCDODM
|
|
FROM RawData.BORI202J AS b
|
|
INNER JOIN cte AS c
|
|
ON b.CDARS = c.OCDPAR AND b.DTBOS >= c.ODTVAL AND b.DTBOS < c.ODTVALEND
|
|
WHERE CDMFS = @RaggrMagazz -- aggiorno solo la bolla corretta
|
|
AND GRBOS = @GruppoBolle
|
|
AND NRBOS = @NumeroBolla
|
|
AND DTBOS = @DataBolla
|
|
AND CDARS = @Particolare
|
|
|
|
/* query di test
|
|
;WITH cte AS
|
|
(
|
|
SELECT OCDPAR,
|
|
OCDODM,
|
|
ODTVAL,
|
|
ISNULL(( SELECT MIN(ODTVAL) FROM RawData.ANODM00F AS T2
|
|
WHERE T2.OCDPAR = v.OCDPAR AND T2.ODTVAL > v.ODTVAL )
|
|
,'20993112') AS ODTVALEND
|
|
FROM RawData.ANODM00F AS v
|
|
)
|
|
SELECT b.*, c.OCDODM FROM RawData.BORI202J AS b
|
|
LEFT JOIN cte AS c
|
|
ON b.CDARS = c.OCDPAR AND b.DTBOS >= c.ODTVAL AND b.DTBOS < c.ODTVALEND
|
|
*/
|
|
END
|
|
|
|
-- Carico i dati della Temp table nel file di appoggio
|
|
-- INSERT INTO RawData.BORI202J
|
|
-- SELECT * FROM ##TabTemp
|
|
|
|
|
|
RETURN
|
|
|
|
GO
|
|
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
|
|
GO
|
|
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
|
|
GO
|
|
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
|
|
GO
|
|
IF @@TRANCOUNT>0 BEGIN
|
|
PRINT 'The database update succeeded'
|
|
COMMIT TRANSACTION
|
|
END
|
|
ELSE PRINT 'The database update failed'
|
|
GO
|
|
DROP TABLE #tmpErrors
|
|
GO
|