Samuele E. Locatelli
401 lines
19 KiB
Transact-SQL
401 lines
19 KiB
Transact-SQL
-- =============================================
|
|
-- STORED PROCEDURE stp_QL_fullDataUpdate
|
|
--
|
|
-- Author: Steamware
|
|
-- Create date: 2014.01.27
|
|
-- Description: Recupera tutti i dati dalle tab QuoteList x avere schema x report/gestione web
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_QL_fullDataUpdate] (
|
|
@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
|
|
|
|
/* va rifatta struttura:
|
|
*
|
|
* tab temporanea
|
|
* calcolo parametri
|
|
* salvataggio in QuoteList dei 3 valori di sunto
|
|
* calcolo dei valori aggregati e restituzione riga risultati finali
|
|
*
|
|
* ATTENZIONE NON CONTROLLA IL CAMPO VALID DEVE ESSERE SEMPRE 1 PER ESSERE VALIDO
|
|
*
|
|
*/
|
|
|
|
|
|
--DECLARE @FullCost DECIMAL(9,6)
|
|
-- , @MinPrice DECIMAL(9,6)
|
|
IF (@DoUpdate = 'Y')
|
|
BEGIN
|
|
EXEC dbo.stp_QL_update @QuoteType
|
|
, @CodQuote
|
|
, @QuoteRev
|
|
END
|
|
|
|
|
|
-- inserire in tabelle: QuoteFull_Q (quotazioni/preventivi)
|
|
-- inserire in tabelle: QuoteFull_R (resoconto di commessa)
|
|
-- inserire in tabelle: QuoteFull_S (simulazioni)
|
|
|
|
-- elimino eventuale temp table...
|
|
IF OBJECT_ID('tempdb..#TempFullData') IS NOT NULL
|
|
DROP TABLE #TempFullData;
|
|
|
|
|
|
-- Costruisco la "superselect" di tutti i valori necessari...
|
|
|
|
-- =================================================
|
|
-- PIVOT TABELLA QuoteParameter
|
|
-- =================================================
|
|
;WITH ctePivotQuoteP
|
|
AS (
|
|
SELECT *
|
|
FROM (
|
|
SELECT QuoteType
|
|
, CodQuote
|
|
, QuoteRev
|
|
, Descr
|
|
, Parameter
|
|
FROM dbo.QuoteParam
|
|
WHERE CodQuote = @CodQuote AND QuoteType = @QuoteType AND QuoteRev = @QuoteRev
|
|
) AS SourceTable
|
|
|
|
PIVOT(MIN(Parameter) FOR Descr IN (
|
|
ItemDescr
|
|
, CodifProfilo
|
|
, ClasseMerc
|
|
, CodClientAssoc
|
|
, ClientNameAssoc
|
|
, CodDiesGroup
|
|
, TipoDies
|
|
, DiamDies
|
|
, HoleNumDies
|
|
, NumDiesInList
|
|
, NumDiesExaust
|
|
, CodPlant
|
|
, KeyAM
|
|
, KeyAMIL
|
|
, NumSMED
|
|
, KgTeo
|
|
, KgProdotti
|
|
, QuotaMan
|
|
, DiesPrice
|
|
, DiesExpLife
|
|
, DiesEOLife
|
|
, DiesByClass
|
|
, PkgDesc
|
|
, PRMWeight
|
|
, PWeight
|
|
, PTare
|
|
, TareStRatio
|
|
, LivPackage
|
|
, LivPackageDett
|
|
-- START nuovi campi x SIM
|
|
, DiesPrice_sim
|
|
, SamplePrice
|
|
, DiesFullCost
|
|
, DiesPriceClientQuote
|
|
, DiesTecLife
|
|
, DiesCommLife
|
|
, MonthSalesPrev
|
|
, SalesPrevCost
|
|
, DiesFinCost
|
|
, ClientQuoteReven
|
|
, ExtraDiscountDies
|
|
, WSR01_sim
|
|
, MSR01_sim
|
|
, NetProd01_sim
|
|
, NetProd04_sim
|
|
, DSR_sim
|
|
-- end nuovi campi x SIM
|
|
)
|
|
) AS PivotData
|
|
),
|
|
-- =================================================
|
|
-- PIVOT TABELLA QuoteOC
|
|
-- =================================================
|
|
ctePivotQuoteOC AS (
|
|
SELECT *
|
|
FROM (
|
|
SELECT QuoteType
|
|
, CodQuote
|
|
, QuoteRev
|
|
, Class01 + ProcessNum AS ClassProc
|
|
, C4UG
|
|
FROM dbo.QuoteOC
|
|
WHERE CodQuote = @CodQuote AND QuoteType = @QuoteType AND QuoteRev = @QuoteRev
|
|
) AS SourceTable
|
|
PIVOT(SUM(C4UG) FOR ClassProc IN
|
|
( EE01
|
|
, GAS01
|
|
, MATRICE01
|
|
, VENDITA01
|
|
, ROTTAME01
|
|
, VENDITA04
|
|
, ROTTAME04
|
|
, IMBALLO04
|
|
, TRASPORTI04
|
|
, PROVVIGIONI04
|
|
)
|
|
) AS PivotData
|
|
)
|
|
-- =================================================
|
|
-- MAIN Select Tabella con INTO temp table
|
|
-- =================================================
|
|
SELECT 'V14.1' AS Vers
|
|
, QL.QuoteType
|
|
, QL.CodQuote
|
|
, QL.QuoteRev
|
|
, QL.IdxQState
|
|
, QL.CodClient
|
|
, ISNULL(CD.ClientName,N'') AS ClientName
|
|
, QL.CodItem
|
|
, QL.CodItemGroup
|
|
, QL.RawMat
|
|
, QL.UnitWeight
|
|
, QL.BatchQty
|
|
, QL.CodInco
|
|
, ISNULL(CD.TranspZone,N'') AS TranspZone
|
|
, QL.KeyAM
|
|
, QL.Note
|
|
, QL.PriceOff
|
|
, QL.OrdDate
|
|
, QL.OrdNum
|
|
, QL.OrdRow
|
|
, QL.OrdPrice
|
|
, QL.OrdQty
|
|
, pQP.CodClientAssoc
|
|
, pQP.ClientNameAssoc
|
|
, pQP.ItemDescr
|
|
, pQP.CodifProfilo
|
|
, pQP.ClasseMerc
|
|
, pQP.TipoDies
|
|
, pQP.CodDiesGroup
|
|
, CONVERT(DECIMAL(18,6),pQP.DiamDies) AS DiamDies
|
|
, CONVERT(INT,pQP.HoleNumDies) AS HoleNumDies
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesPrice),0) AS DiesPrice
|
|
, CONVERT(DECIMAL(18,6),pQP.DiesExpLife) AS DiesExpLife
|
|
, CONVERT(DECIMAL(18,6),pQP.DiesEOLife) AS DiesEOLife
|
|
, CONVERT(BIT, ISNULL(pQP.DiesByClass,0)) AS DiesByClass
|
|
, CONVERT(DECIMAL(18,6),pQP.NumDiesInList) AS NumDiesInList
|
|
, CONVERT(DECIMAL(18,6),pQP.NumDiesExaust) AS NumDiesExaust
|
|
, pQP.CodPlant
|
|
, pQP.KeyAMIL
|
|
, CONVERT(DECIMAL(18,6),pQP.KgTeo) AS KgTeo
|
|
, CONVERT(DECIMAL(18,6),pQP.NumSMED) AS NumSMED
|
|
, CONVERT(DECIMAL(18,6),pQP.QuotaMan) AS QuotaMan
|
|
, CONVERT(DECIMAL(18,6),pQP.PRMWeight) AS PRMWeight
|
|
, CONVERT(DECIMAL(18,6),pQP.PWeight) AS PWeight
|
|
, CONVERT(DECIMAL(18,6),pQP.PTare) AS PTare
|
|
, pQP.LivPackage
|
|
, pQP.LivPackageDett
|
|
-- START nuovi campi x SIM
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesPrice_sim),0) AS DiesPrice_sim
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.SamplePrice),0) AS SamplePrice
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesFullCost),0) AS DiesFullCost
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesPriceClientQuote),0) AS DiesPriceClientQuote
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesTecLife),0) AS DiesTecLife
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesCommLife),1) AS DiesCommLife
|
|
, ISNULL(CONVERT(INT,pQP.MonthSalesPrev),0) AS MonthSalesPrev
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.SalesPrevCost),0) AS SalesPrevCost
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesFinCost),0) AS DiesFinCost
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.ClientQuoteReven),0) AS ClientQuoteReven
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.ExtraDiscountDies),0) AS ExtraDiscountDies
|
|
, ISNULL(CONVERT(DECIMAL(9,8),pQP.WSR01_sim),0) AS WSR01_sim
|
|
, ISNULL(CONVERT(DECIMAL(9,8),pQP.MSR01_sim),0) AS MSR01_sim
|
|
, ISNULL(CONVERT(DECIMAL(9,3),pQP.NetProd01_sim),0) AS NetProd01_sim
|
|
, ISNULL(CONVERT(DECIMAL(9,3),pQP.NetProd04_sim),0) AS NetProd04_sim
|
|
, ISNULL(CONVERT(DECIMAL(9,8),pQP.DSR_sim),0) AS DSR_sim
|
|
-- end nuovi campi x SIM
|
|
, QRM.RawMatCost
|
|
, QRM.RawMatExtraCost
|
|
, QRM.RawMatCost + QRM.RawMatExtraCost AS RawMatFullCost -- fare campo calcolato ?!?
|
|
, QRM.CodPackag
|
|
, pQP.PkgDesc
|
|
, QRM.FSR
|
|
, ISNULL(pQOC.PROVVIGIONI04, 0) AS ProvvCost
|
|
, ISNULL(pQOC.EE01, 0) AS EECost
|
|
, ISNULL(pQOC.GAS01, 0) AS GasCost
|
|
, ISNULL(pQOC.MATRICE01, 0) AS DiesCost
|
|
, ISNULL(pQOC.IMBALLO04, 0) AS PackCost
|
|
, ISNULL(pQOC.TRASPORTI04, 0) AS TranspCost
|
|
, ISNULL(pQOC.VENDITA01, 0) AS SellCost01
|
|
, ISNULL(pQOC.ROTTAME01, 0) AS RefCost01
|
|
, ISNULL(pQOC.VENDITA04, 0) AS SellCost04
|
|
, ISNULL(pQOC.ROTTAME04, 0) AS RefCost04
|
|
, QWI01.WSR AS WSR01
|
|
, QWI01.MSR AS MSR01
|
|
, QWI01.NetProd AS NetProd01
|
|
, QWI01.FC4UG AS FC4UG01
|
|
, QWI01.VC4UG AS VC4UG01
|
|
, QWI01.OH4UG AS OH4UG01
|
|
, QWI01.SC4UG AS SC4UG01
|
|
, 1 - (QWI01.DYR * QWI02.DYR * QWI03.DYR * QWI04.DYR) AS DSR
|
|
, QWI04.NetProd AS NetProd04
|
|
, QWI04.FC4UG AS FC4UG04
|
|
, QWI04.VC4UG AS VC4UG04
|
|
, ISNULL(pQOC.VENDITA01, 0) + ISNULL(pQOC.ROTTAME01, 0) + ISNULL(pQOC.VENDITA04, 0) + ISNULL(pQOC.ROTTAME04, 0) + ISNULL(pQOC.EE01, 0) + ISNULL(pQOC.GAS01, 0) + ISNULL(pQOC.MATRICE01, 0) + ISNULL(pQOC.IMBALLO04, 0) + ISNULL(pQOC.PROVVIGIONI04, 0) + ISNULL(pQOC.TRASPORTI04, 0) + QWI01.VC4UG + QWI04.VC4UG + QWI01.SC4UG AS FVC
|
|
, PD.Charge
|
|
, QL.valid
|
|
, QL.errorScore
|
|
|
|
INTO #TempFullData -- carico in temp table
|
|
FROM dbo.QuoteList AS QL
|
|
|
|
LEFT JOIN dbo.ClientDet CD -- non voglio che non crei record x mancanza Cliente
|
|
ON QL.CodClient = CD.CodClient
|
|
|
|
INNER JOIN ctePivotQuoteP AS pQP
|
|
ON QL.QuoteType = pQP.QuoteType
|
|
AND QL.CodQuote = pQP.CodQuote
|
|
AND QL.QuoteRev = pQP.QuoteRev
|
|
|
|
INNER JOIN dbo.QuoteRM AS QRM
|
|
ON QL.QuoteType = QRM.QuoteType
|
|
AND QL.CodQuote = QRM.CodQuote
|
|
AND QL.QuoteRev = QRM.QuoteRev
|
|
AND QL.rawmat = QRM.RawMat
|
|
|
|
INNER JOIN ctePivotQuoteOC AS pQOC
|
|
ON QL.QuoteType = pQOC.QuoteType
|
|
AND QL.CodQuote = pQOC.CodQuote
|
|
AND QL.QuoteRev = pQOC.QuoteRev
|
|
|
|
INNER JOIN dbo.QuoteWorkInt AS QWI01
|
|
ON QL.QuoteType = QWI01.QuoteType
|
|
AND QL.CodQuote = QWI01.CodQuote
|
|
AND QL.QuoteRev = QWI01.QuoteRev
|
|
AND QWI01.ProcessNum = '01'
|
|
INNER JOIN dbo.QuoteWorkInt AS QWI02
|
|
ON QL.QuoteType = QWI02.QuoteType
|
|
AND QL.CodQuote = QWI02.CodQuote
|
|
AND QL.QuoteRev = QWI02.QuoteRev
|
|
AND QWI02.ProcessNum = '02'
|
|
INNER JOIN dbo.QuoteWorkInt AS QWI03
|
|
ON QL.QuoteType = QWI03.QuoteType
|
|
AND QL.CodQuote = QWI03.CodQuote
|
|
AND QL.QuoteRev = QWI03.QuoteRev
|
|
AND QWI03.ProcessNum = '03'
|
|
INNER JOIN dbo.QuoteWorkInt AS QWI04
|
|
ON QL.QuoteType = QWI04.QuoteType
|
|
AND QL.CodQuote = QWI04.CodQuote
|
|
AND QL.QuoteRev = QWI04.QuoteRev
|
|
AND QWI04.ProcessNum = '04'
|
|
INNER JOIN dbo.PlantsDet AS PD
|
|
ON PD.CodPlant = pQP.CodPlant
|
|
|
|
|
|
-- 2014.09.10 modifica x proporre campi _sim a standard...
|
|
-- Campi _sim: faccio verifica, se fossero vuoti li metto pari agli equivalenti NON _sim
|
|
UPDATE #TempFullData
|
|
SET WSR01_sim = CASE WHEN WSR01_sim > 0 THEN WSR01_sim ELSE WSR01 END
|
|
, MSR01_sim = CASE WHEN MSR01_sim > 0 THEN MSR01_sim ELSE MSR01 END
|
|
, DSR_sim = CASE WHEN DSR_sim > 0 THEN DSR_sim ELSE DSR END
|
|
, NetProd01_sim = CASE WHEN NetProd01_sim > 0 THEN NetProd01_sim ELSE NetProd01 END
|
|
, NetProd04_sim = CASE WHEN NetProd04_sim > 0 THEN NetProd04_sim ELSE NetProd04 END
|
|
|
|
---- salvo i dati dell'ordine: FullCost, MinPrice, PriceOff (se zero lo pongo uguale a MinPrice)
|
|
--UPDATE QuoteList
|
|
--SET FullCost = @FullCost
|
|
-- , MinPrice = @MinPrice
|
|
-- , PriceOff = CASE WHEN ISNULL(PriceOff,0) = 0 THEN @MinPrice ELSE PriceOff END
|
|
--WHERE QuoteType = @QuoteType
|
|
-- AND CodQuote = @CodQuote
|
|
-- AND QuoteRev = @QuoteRev
|
|
|
|
-- SELECT TOP 1000 * FROM #TempFullData
|
|
|
|
IF @QuoteType = 'Q'
|
|
BEGIN
|
|
IF OBJECT_ID('QuoteFull_Q') IS NULL -- CREATE TABLE QuoteFull_Q da eliminare quando Tabella Definitiva
|
|
SELECT * INTO dbo.QuoteFull_Q FROM #TempFullData
|
|
ELSE
|
|
BEGIN
|
|
--DELETE dbo.QuoteFull_Q WHERE QuoteType = @QuoteType AND CodQuote = @CodQuote AND QuoteRev = @QuoteRev
|
|
DELETE dbo.QuoteFull_Q WHERE QuoteType = @QuoteType AND CodQuote = @CodQuote AND QuoteRev = @QuoteRev
|
|
INSERT INTO dbo.QuoteFull_Q (Vers, QuoteType, CodQuote, QuoteRev, IdxQState, CodClient, ClientName, CodItem, CodItemGroup, RawMat, UnitWeight, BatchQty, CodInco, TranspZone, KeyAM, Note, PriceOff, CodClientAssoc, ClientNameAssoc, ItemDescr, CodifProfilo, ClasseMerc, TipoDies, CodDiesGroup, DiamDies, HoleNumDies, DiesPrice, DiesExpLife, DiesEOLife, DiesByClass, NumDiesInList, NumDiesExaust, CodPlant, KeyAMIL, KgTeo, NumSMED, QuotaMan, PRMWeight, PWeight, PTare, LivPackage, LivPackageDett, RawMatCost, RawMatExtraCost, RawMatFullCost, CodPackag, PkgDesc, FSR, ProvvCost, EECost, GasCost, DiesCost, PackCost, TranspCost, SellCost01, RefCost01, SellCost04, RefCost04, WSR01, MSR01, NetProd01, FC4UG01, VC4UG01, OH4UG01, SC4UG01, DSR, NetProd04, FC4UG04, VC4UG04, FVC, Charge, valid, errorScore)
|
|
SELECT Vers, QuoteType, CodQuote, QuoteRev, IdxQState, CodClient, ClientName, CodItem, CodItemGroup, RawMat, UnitWeight, BatchQty, CodInco, TranspZone, KeyAM, Note, PriceOff, CodClientAssoc, ClientNameAssoc, ItemDescr, CodifProfilo, ClasseMerc, TipoDies, CodDiesGroup, DiamDies, HoleNumDies, DiesPrice, DiesExpLife, DiesEOLife, DiesByClass, NumDiesInList, NumDiesExaust, CodPlant, KeyAMIL, KgTeo, NumSMED, QuotaMan, PRMWeight, PWeight, PTare, LivPackage, LivPackageDett, RawMatCost, RawMatExtraCost, RawMatFullCost, CodPackag, PkgDesc, FSR, ProvvCost, EECost, GasCost, DiesCost, PackCost, TranspCost, SellCost01, RefCost01, SellCost04, RefCost04, WSR01, MSR01, NetProd01, FC4UG01, VC4UG01, OH4UG01, SC4UG01, DSR, NetProd04, FC4UG04, VC4UG04, FVC, Charge, valid, errorScore
|
|
FROM #TempFullData
|
|
END
|
|
END
|
|
|
|
ELSE IF @QuoteType = 'R'
|
|
BEGIN
|
|
IF OBJECT_ID('QuoteFull_R') IS NULL -- CREATE TABLE QuoteFull_R da eliminare quando Tabella Definitiva
|
|
SELECT * INTO dbo.QuoteFull_R FROM #TempFullData
|
|
ELSE
|
|
BEGIN
|
|
DELETE dbo.QuoteFull_R WHERE QuoteType = @QuoteType AND CodQuote = @CodQuote AND QuoteRev = @QuoteRev
|
|
INSERT INTO dbo.QuoteFull_R (Vers, QuoteType, CodQuote, QuoteRev, IdxQState, CodClient, ClientName, CodItem, CodItemGroup, RawMat, UnitWeight, BatchQty, CodInco, TranspZone, KeyAM, Note, PriceOff, CodClientAssoc, ClientNameAssoc, ItemDescr, CodifProfilo, ClasseMerc, TipoDies, CodDiesGroup, DiamDies, HoleNumDies, DiesPrice, DiesExpLife, DiesEOLife, DiesByClass, NumDiesInList, NumDiesExaust, CodPlant, KeyAMIL, KgTeo, NumSMED, QuotaMan, PRMWeight, PWeight, PTare, LivPackage, LivPackageDett, RawMatCost, RawMatExtraCost, RawMatFullCost, CodPackag, PkgDesc, FSR, ProvvCost, EECost, GasCost, DiesCost, PackCost, TranspCost, SellCost01, RefCost01, SellCost04, RefCost04, WSR01, MSR01, NetProd01, FC4UG01, VC4UG01, OH4UG01, SC4UG01, DSR, NetProd04, FC4UG04, VC4UG04, FVC, Charge, OrdDate, OrdNum, OrdRow, OrdPrice, OrdQty, valid, errorScore)
|
|
SELECT Vers, QuoteType, CodQuote, QuoteRev, IdxQState, CodClient, ClientName, CodItem, CodItemGroup, RawMat, UnitWeight, BatchQty, CodInco, TranspZone, KeyAM, Note, PriceOff, CodClientAssoc, ClientNameAssoc, ItemDescr, CodifProfilo, ClasseMerc, TipoDies, CodDiesGroup, DiamDies, HoleNumDies, DiesPrice, DiesExpLife, DiesEOLife, DiesByClass, NumDiesInList, NumDiesExaust, CodPlant, KeyAMIL, KgTeo, NumSMED, QuotaMan, PRMWeight, PWeight, PTare, LivPackage, LivPackageDett, RawMatCost, RawMatExtraCost, RawMatFullCost, CodPackag, PkgDesc, FSR, ProvvCost, EECost, GasCost, DiesCost, PackCost, TranspCost, SellCost01, RefCost01, SellCost04, RefCost04, WSR01, MSR01, NetProd01, FC4UG01, VC4UG01, OH4UG01, SC4UG01, DSR, NetProd04, FC4UG04, VC4UG04, FVC, Charge, OrdDate, OrdNum, OrdRow, OrdPrice, OrdQty, valid, errorScore
|
|
-- FROM [dbo].[v_pivot]
|
|
FROM #TempFullData
|
|
END
|
|
END
|
|
|
|
ELSE IF @QuoteType = 'S'
|
|
BEGIN
|
|
IF OBJECT_ID('QuoteFull_S') IS NULL -- CREATE TABLE QuoteFull_S da eliminare quando Tabella Definitiva
|
|
SELECT * INTO dbo.QuoteFull_S FROM #TempFullData
|
|
ELSE
|
|
BEGIN
|
|
DELETE dbo.QuoteFull_S WHERE QuoteType = @QuoteType AND CodQuote = @CodQuote AND QuoteRev = @QuoteRev
|
|
INSERT INTO dbo.QuoteFull_S (Vers, QuoteType, CodQuote, QuoteRev, IdxQState, CodClient, ClientName, CodItem, CodItemGroup, RawMat, UnitWeight, BatchQty, CodInco, TranspZone, KeyAM, Note, PriceOff, CodClientAssoc, ClientNameAssoc, ItemDescr, CodifProfilo, ClasseMerc, TipoDies, CodDiesGroup, DiamDies, HoleNumDies, DiesPrice_sim, DiesPrice, SamplePrice, DiesFullCost, DiesPriceClientQuote, DiesExpLife, DiesEOLife, DiesTecLife, DiesCommLife, MonthSalesPrev, SalesPrevCost, DiesFinCost, ClientQuoteReven, ExtraDiscountDies, DiesByClass, NumDiesInList, NumDiesExaust, CodPlant, KeyAMIL, KgTeo, NumSMED, QuotaMan, PRMWeight, PWeight, PTare, LivPackage, LivPackageDett, RawMatCost, RawMatExtraCost, RawMatFullCost, CodPackag, PkgDesc, FSR, ProvvCost, EECost, GasCost, DiesCost, PackCost, TranspCost, SellCost01, RefCost01, SellCost04, RefCost04, WSR01_sim, WSR01, MSR01_sim, MSR01, NetProd01_sim, NetProd01, FC4UG01, VC4UG01, OH4UG01, SC4UG01, DSR_sim, DSR, NetProd04_sim, NetProd04, FC4UG04, VC4UG04, FVC, Charge, valid, errorScore)
|
|
SELECT Vers, QuoteType, CodQuote, QuoteRev, IdxQState, CodClient, ClientName, CodItem, CodItemGroup, RawMat, UnitWeight, BatchQty, CodInco, TranspZone, KeyAM, Note, PriceOff, CodClientAssoc, ClientNameAssoc, ItemDescr, CodifProfilo, ClasseMerc, TipoDies, CodDiesGroup, DiamDies, HoleNumDies, DiesPrice_sim, DiesPrice, SamplePrice, (DiesPrice_sim + SamplePrice) AS DiesFullCost, DiesPriceClientQuote, DiesExpLife, DiesEOLife, DiesTecLife, DiesCommLife, MonthSalesPrev
|
|
-- se DiesCommLife è NULLA è messa a 1 sopra
|
|
,CASE WHEN ( DiesTecLife = 0 OR DiesCommLife = 0 )
|
|
THEN 0
|
|
ELSE ( (((DiesTecLife % DiesCommLife) / DiesTecLife) * ( DiesPrice_sim + SamplePrice) ) / DiesTecLife )
|
|
END AS SalesPrevCost
|
|
,CASE WHEN DiesCommLife > 0
|
|
THEN ((((DiesPrice_sim+SamplePrice)-DiesPriceClientQuote)*(power((1.05),MonthSalesPrev/(12))-(1))) / DiesCommLife )
|
|
ELSE ((((DiesPrice_sim+SamplePrice)-DiesPriceClientQuote)*(power((1.05),MonthSalesPrev/(12))-(1))) / 1 )
|
|
END AS DiesFinCost
|
|
,CASE WHEN DiesTecLife > 0
|
|
THEN ( -DiesPriceClientQuote / DiesTecLife )
|
|
ELSE -DiesPriceClientQuote
|
|
END AS ClientQuoteReven
|
|
,CASE WHEN ( DiesTecLife = 0 OR DiesCommLife = 0 )
|
|
THEN 0
|
|
ELSE (((((DiesTecLife % DiesCommLife) / DiesTecLife ) * (DiesPrice_sim+SamplePrice)) / DiesTecLife
|
|
+(((DiesPrice_sim+SamplePrice)-DiesPriceClientQuote) * (power((1.05),MonthSalesPrev/(12))-(1))) / DiesCommLife )
|
|
- DiesPriceClientQuote / DiesTecLife )
|
|
END AS ExtraDiscountDies
|
|
|
|
-- CALCOLI OLD Version
|
|
--((((DiesTecLife%DiesCommLife)/case when DiesTecLife=(0) then (1) else DiesTecLife end)*(DiesPrice+SamplePrice))/case when DiesTecLife=(0) then (1) else DiesTecLife end) AS SalesPrevCost,
|
|
--((((DiesPrice+SamplePrice)-DiesPriceClientQuote)*(power((1.05),MonthSalesPrev/(12))-(1)))/case when DiesCommLife>(0) then DiesCommLife else (1) end) AS DiesFinCost,
|
|
--( -DiesPriceClientQuote/case when DiesTecLife>(0) then DiesTecLife else (1) end) AS ClientQuoteReven,
|
|
--(((((DiesTecLife%DiesCommLife)/case when DiesTecLife=(0) then (1) else DiesTecLife end)*(DiesPrice+SamplePrice))/case when DiesTecLife=(0) then (1) else DiesTecLife end+(((DiesPrice+SamplePrice)-DiesPriceClientQuote)*(power((1.05),MonthSalesPrev/(12))-(1)))/case when DiesCommLife>(0) then DiesCommLife else (1) end)-DiesPriceClientQuote/case when DiesTecLife>(0) then DiesTecLife else (1) end) AS ExtraDiscountDies,
|
|
|
|
,DiesByClass, NumDiesInList, NumDiesExaust, CodPlant, KeyAMIL, KgTeo, NumSMED, QuotaMan, PRMWeight, PWeight, PTare, LivPackage, LivPackageDett, RawMatCost, RawMatExtraCost, RawMatFullCost, CodPackag, PkgDesc, FSR, ProvvCost, EECost, GasCost, DiesCost, PackCost, TranspCost, SellCost01, RefCost01, SellCost04, RefCost04, WSR01_sim, WSR01, MSR01_sim, MSR01, NetProd01_sim, NetProd01, FC4UG01, VC4UG01, OH4UG01, SC4UG01, DSR_sim, DSR, NetProd04_sim, NetProd04, FC4UG04, VC4UG04, FVC, Charge, valid, errorScore
|
|
|
|
FROM #TempFullData
|
|
END
|
|
END
|
|
ELSE IF @QuoteType = 'C'
|
|
BEGIN
|
|
DELETE dbo.QuoteFull_C WHERE QuoteType = @QuoteType AND CodQuote = @CodQuote AND QuoteRev = @QuoteRev
|
|
INSERT INTO dbo.QuoteFull_C ( Vers, QuoteType, CodQuote, QuoteRev, Class01, Class02, CodItem, RawMat, CodPlant, CodDiesGroup, CodPackag, batchQty, CodInco, RawMatFullCost, FVC, FullCost, valid, errorScore )
|
|
SELECT Vers, QuoteType, CodQuote, QuoteRev, CodifProfilo, ClasseMerc, tmp.CodItem, RawMat, tmp.CodPlant, CodDiesGroup, CodPackag, batchQty, CodInco, RawMatFullCost, FVC, FVC + FC4UG01 + OH4UG01 + FC4UG04 AS FullCost, valid, errorScore
|
|
FROM #TempFullData AS tmp
|
|
END
|
|
|
|
-- elimino temp table...
|
|
--IF OBJECT_ID('tempdb..#TempFullData') IS NOT NULL
|
|
-- DROP TABLE #TempFullData
|
|
|
|
RETURN
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|