Samuele E. Locatelli
52 lines
1.6 KiB
Transact-SQL
52 lines
1.6 KiB
Transact-SQL
-- =============================================
|
|
-- STORED PROCEDURE stp_QL_fullData
|
|
--
|
|
-- Author: Steamware
|
|
-- Create date: 2014.01.27
|
|
-- Description: Recupera tutti i dati dalle tab QuoteList x avere schema x report/gestioen web
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_QL_fullData] (
|
|
@QuoteType CHAR(1) = 'Q'
|
|
, @CodQuote BIGINT = 0
|
|
, @QuoteRev INT = 0
|
|
, @DoUpdate CHAR(1) = 'N' -- se si debba fare ricalcolo Y/N
|
|
)
|
|
AS
|
|
SET NOCOUNT ON
|
|
|
|
IF(@QuoteType <> '' AND @DoUpdate = 'N')
|
|
BEGIN
|
|
EXEC stp_QL_fullDataUpdate @QuoteType
|
|
, @CodQuote
|
|
, @QuoteRev
|
|
, @DoUpdate
|
|
END
|
|
|
|
---- calcolo con aggiunta campi sommati...
|
|
--SELECT *
|
|
-- , FVC + RawMatFullCost AS P_FVC
|
|
-- , FC4UG01 + OH4UG01 AS CPS
|
|
-- , FC4UG01 + OH4UG01 + FVC + RawMatFullCost AS P_CPS
|
|
-- , FC4UG04 + FVC + RawMatFullCost AS P_FC4UG04
|
|
-- , FVC + FC4UG01 + OH4UG01 + FC4UG04 AS FullCost
|
|
-- , FVC + FC4UG01 + OH4UG01 + FC4UG04 + RawMatFullCost AS P_FullCost
|
|
-- , Charge * (FVC + FC4UG01 + OH4UG01 + FC4UG04) AS MinPrice
|
|
-- , Charge * (FVC + FC4UG01 + OH4UG01 + FC4UG04) + RawMatFullCost AS P_MinPrice
|
|
-- , PriceOff + RawMatFullCost AS P_PriceOff
|
|
-- , (FVC + FC4UG01 + OH4UG01 + FC4UG04) - PriceOff AS NetMar
|
|
-- , (PriceOff - FVC) / (FC4UG01 + OH4UG01 + FC4UG04) AS FCShield
|
|
-- , (PRMWeight - Ptare) * (PriceOff + RawMatFullCost) / Pweight AS PckSur
|
|
--FROM QuoteFull_Q
|
|
--WHERE QuoteType = @QuoteType
|
|
-- AND CodQuote = @CodQuote
|
|
-- AND QuoteRev = @QuoteRev
|
|
|
|
-- calcolo con aggiunta campi sommati...
|
|
SELECT *
|
|
FROM v_QuoteFull_Q
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
RETURN
|