Samuele E. Locatelli
127 lines
4.4 KiB
Transact-SQL
127 lines
4.4 KiB
Transact-SQL
-- =============================================
|
|
-- Author: Steamware
|
|
-- Mod date: 2014.10.01
|
|
-- Description: modifica una quotazione da Q ad S in QuoteList e ricalcola
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_QL_makeSim]
|
|
(
|
|
@CodQuote_orig BIGINT = 0,
|
|
@QuoteRev_orig INT = 0
|
|
)
|
|
AS
|
|
|
|
SET XACT_ABORT ON;
|
|
BEGIN TRAN
|
|
|
|
-- passo x definizione a 'S'
|
|
DECLARE @QuoteType_orig CHAR(1) = 'Q',
|
|
@QuoteType CHAR(1) = 'S'
|
|
|
|
-- elimino record in QuoteFull_Q
|
|
DELETE
|
|
FROM QuoteFull_Q
|
|
WHERE QuoteType = @QuoteType_orig
|
|
AND CodQuote = @CodQuote_orig
|
|
AND QuoteRev = @QuoteRev_orig
|
|
|
|
-- carico alcuni dati accessori precompilando...
|
|
INSERT INTO QuoteSimPar(QuoteType, CodQuote, QuoteRev, NomePar, ValPar)
|
|
SELECT @QuoteType, @CodQuote_orig, @QuoteRev_orig, Descr, Parameter
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType_orig
|
|
AND CodQuote = @CodQuote_orig
|
|
AND QuoteRev = @QuoteRev_orig
|
|
AND Descr IN ('CodDiesGroup', 'CodPlant')
|
|
|
|
INSERT INTO QuoteSimPar(QuoteType, CodQuote, QuoteRev, NomePar, ValPar)
|
|
SELECT @QuoteType, @CodQuote_orig, @QuoteRev_orig, 'DiesTecLife', Parameter
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType_orig
|
|
AND CodQuote = @CodQuote_orig
|
|
AND QuoteRev = @QuoteRev_orig
|
|
AND Descr = 'KgTeo'
|
|
|
|
INSERT INTO QuoteSimPar(QuoteType, CodQuote, QuoteRev, NomePar, ValPar)
|
|
SELECT @QuoteType, @CodQuote_orig, @QuoteRev_orig, 'DiesPrice_sim', Parameter
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType_orig
|
|
AND CodQuote = @CodQuote_orig
|
|
AND QuoteRev = @QuoteRev_orig
|
|
AND Descr = 'DiesPrice'
|
|
|
|
INSERT INTO QuoteSimPar(QuoteType, CodQuote, QuoteRev, NomePar, ValPar)
|
|
SELECT @QuoteType, @CodQuote_orig, @QuoteRev_orig, 'CodPackag', CodPackag
|
|
FROM QuoteRM
|
|
WHERE QuoteType = @QuoteType_orig
|
|
AND CodQuote = @CodQuote_orig
|
|
AND QuoteRev = @QuoteRev_orig
|
|
|
|
INSERT INTO QuoteSimPar(QuoteType, CodQuote, QuoteRev, NomePar, ValPar)
|
|
SELECT @QuoteType, @CodQuote_orig, @QuoteRev_orig, 'NetProd01_sim', NetProd
|
|
FROM QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType_orig
|
|
AND CodQuote = @CodQuote_orig
|
|
AND QuoteRev = @QuoteRev_orig
|
|
AND ProcessNum = '01'
|
|
|
|
INSERT INTO QuoteSimPar(QuoteType, CodQuote, QuoteRev, NomePar, ValPar)
|
|
SELECT @QuoteType, @CodQuote_orig, @QuoteRev_orig, 'NetProd04_sim', NetProd
|
|
FROM QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType_orig
|
|
AND CodQuote = @CodQuote_orig
|
|
AND QuoteRev = @QuoteRev_orig
|
|
AND ProcessNum = '04'
|
|
|
|
INSERT INTO QuoteSimPar(QuoteType, CodQuote, QuoteRev, NomePar, ValPar)
|
|
SELECT @QuoteType, @CodQuote_orig, @QuoteRev_orig, 'MSR01_sim', MSR
|
|
FROM QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType_orig
|
|
AND CodQuote = @CodQuote_orig
|
|
AND QuoteRev = @QuoteRev_orig
|
|
AND ProcessNum = '01'
|
|
|
|
INSERT INTO QuoteSimPar(QuoteType, CodQuote, QuoteRev, NomePar, ValPar)
|
|
SELECT @QuoteType, @CodQuote_orig, @QuoteRev_orig, 'WSR01_sim', WSR
|
|
FROM QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType_orig
|
|
AND CodQuote = @CodQuote_orig
|
|
AND QuoteRev = @QuoteRev_orig
|
|
AND ProcessNum = '01'
|
|
|
|
INSERT INTO QuoteSimPar(QuoteType, CodQuote, QuoteRev, NomePar, ValPar)
|
|
SELECT @QuoteType, @CodQuote_orig, @QuoteRev_orig, 'DSR_sim', DSR
|
|
FROM QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType_orig
|
|
AND CodQuote = @CodQuote_orig
|
|
AND QuoteRev = @QuoteRev_orig
|
|
AND ProcessNum = '04'
|
|
|
|
-- update riga in QuoteList
|
|
UPDATE QuoteList
|
|
SET QuoteType = @QuoteType
|
|
WHERE QuoteType = @QuoteType_orig
|
|
AND CodQuote = @CodQuote_orig
|
|
AND QuoteRev = @QuoteRev_orig
|
|
|
|
-- ricalcolo!
|
|
EXEC stp_QL_fullDataUpdate @QuoteType
|
|
, @CodQuote_orig
|
|
, @QuoteRev_orig
|
|
, 'Y'
|
|
|
|
-- PATCH 2014/10 rieseguo ricalcolo x problema parametri simulati mancanti in quoteparam
|
|
EXEC stp_QL_fullDataUpdate @QuoteType
|
|
, @CodQuote_orig
|
|
, @QuoteRev_orig
|
|
, 'Y'
|
|
|
|
COMMIT TRAN
|
|
|
|
-- seleziono intera riga!
|
|
SELECT *
|
|
FROM QuoteList
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote_orig
|
|
AND QuoteRev = @QuoteRev_orig
|
|
|
|
RETURN |