Samuele E. Locatelli
63 lines
1.7 KiB
Transact-SQL
63 lines
1.7 KiB
Transact-SQL
|
|
-- =============================================
|
|
-- Author: Steamware - S.E.L.
|
|
-- Create date: 2014.04.16
|
|
-- Description: insert di un record in QL (Full) e poi aggiorna a cascata
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_QLF_Insert]
|
|
(
|
|
@QuoteType CHAR(1) = 'Q',
|
|
@CodClient NVARCHAR(50) = '',
|
|
@CodItem NVARCHAR(50) = '',
|
|
@RawMat NVARCHAR(50),
|
|
@RawMatCost DECIMAL(18, 6),
|
|
@RawMatExtraCost DECIMAL(18, 6),
|
|
@BatchQty INT,
|
|
@CodInco NVARCHAR(5),
|
|
@Note NVARCHAR(2500) = ''
|
|
)
|
|
AS
|
|
|
|
-- variabili
|
|
DECLARE @CodQuote BIGINT = 0,
|
|
@QuoteRev INT = 0
|
|
|
|
-- check QTA: deve essere > 0 !!!
|
|
IF(@BatchQty = 0)
|
|
BEGIN
|
|
SET @BatchQty = 1
|
|
END
|
|
|
|
SET XACT_ABORT ON;
|
|
BEGIN TRAN
|
|
|
|
-- calcolo nuovo record odierno
|
|
SELECT @CodQuote = dbo.f_getNewCodQuote()
|
|
|
|
-- inserisco riga in QuoteList
|
|
INSERT INTO QuoteList(QuoteType, CodQuote, QuoteRev, CodClient, CodItem, UnitWeight, BatchQty, CodItemGroup, RawMat, CodInco, Note)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, @CodClient, CodItem, UnitWeight, @BatchQty, CodItemGroup, @RawMat, @CodInco, @Note
|
|
FROM ItemDet
|
|
WHERE CodItem = @CodItem
|
|
|
|
-- ora inserisco in QuoteRM
|
|
INSERT INTO QuoteRM(QuoteType, CodQuote, QuoteRev, NumRM, RawMat, RawMatCost, RawMatExtraCost)
|
|
VALUES (@QuoteType, @CodQuote, @QuoteRev, 1, @RawMat, @RawMatCost, @RawMatExtraCost)
|
|
|
|
-- ricalcolo!
|
|
EXEC stp_QL_fullDataUpdate @QuoteType
|
|
,@CodQuote
|
|
,@QuoteRev
|
|
,'Y'
|
|
|
|
COMMIT TRAN
|
|
|
|
-- seleziono intera riga!
|
|
SELECT *
|
|
FROM v_QuoteFull_Q
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
|
|
RETURN |