Giancarlo Rottoli
741 lines
34 KiB
Transact-SQL
741 lines
34 KiB
Transact-SQL
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2013.12.13
|
|
-- Description: aggiorna tabella QuoteList con i dati selezionati dalla TabProdSc
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_QL_update_SAM]
|
|
(
|
|
@QuoteType CHAR(1) = 'Q',
|
|
@CodQuote BIGINT = 0,
|
|
@QuoteRev INT = 0
|
|
)
|
|
AS
|
|
BEGIN
|
|
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
BEGIN TRAN
|
|
|
|
-- dichiaro valori da recuperare
|
|
DECLARE @cont INT = 0 -- contatore x numeri consecutivi...
|
|
DECLARE @strTemp NVARCHAR(50) = '' --stringa temporanea x variabili "locali"
|
|
|
|
DECLARE @valid BIT
|
|
DECLARE @CodDiesGroup NVARCHAR(50)
|
|
DECLARE @CodPlant NVARCHAR(50)
|
|
DECLARE @CodPlantFix NVARCHAR(50) = ''
|
|
DECLARE @HoleNum INT
|
|
DECLARE @CodItem NVARCHAR(50) = '' --cod item
|
|
DECLARE @ItemDescr NVARCHAR(50) = '' -- descrizione item
|
|
DECLARE @KeyAM NVARCHAR(50) = ''
|
|
DECLARE @KeyAMI NVARCHAR(50) = ''
|
|
DECLARE @KeyAMIL NVARCHAR(50) = ''
|
|
DECLARE @NumSMED INT = 0 -- numero cambi
|
|
DECLARE @KgTeo DECIMAL(18, 6) = 0 -- kg totali
|
|
DECLARE @RawMat NVARCHAR(50)
|
|
DECLARE @ProdTeo DECIMAL(18,6)
|
|
DECLARE @ScTec DECIMAL(18,6)
|
|
DECLARE @BatchQty INT
|
|
DECLARE @MAC_SC DECIMAL(18,6) -- Messa a cento scarto cesoia
|
|
DECLARE @MAC_ST DECIMAL(18,6) -- Messa a cento scarto tecnico
|
|
DECLARE @MAC_IN DECIMAL(18,6) -- Messa a cento difettosità all'incestatura
|
|
DECLARE @MAC_IM DECIMAL(18,6) -- Messa a cento difettosità all'imballo
|
|
DECLARE @SSR DECIMAL(9,6) -- percentuale vendita rottame
|
|
DECLARE @DiesPrice DECIMAL(9,3) -- prezzo matrice
|
|
DECLARE @DiesEOLife DECIMAL(9,2) -- durata matrice
|
|
DECLARE @DiesExpLife DECIMAL(9,2) -- durata matrice
|
|
DECLARE @DiesPriceLife DECIMAL(18,6) -- Price/Life matrice
|
|
DECLARE @OCF01 DECIMAL(18,6) = 0 -- altri costi di fase 01
|
|
DECLARE @VCF01 DECIMAL(18,6) = 0 -- costi variabili di fase 01
|
|
DECLARE @VCF04 DECIMAL(18,6) = 0 -- costi variabili di fase 04
|
|
DECLARE @SellCostF01 DECIMAL(18,6) = 0 -- costo vendita fase 01
|
|
DECLARE @SellCostF04 DECIMAL(18,6) = 0 -- costo vendita fase 04
|
|
DECLARE @RefCostF01 DECIMAL(18,6) = 0 -- costo rifusione fase 01
|
|
DECLARE @RefCostF04 DECIMAL(18,6) = 0 -- costo rifusione fase 04
|
|
-- IMBALLO
|
|
DECLARE @CodPackag NVARCHAR(50) = '0' -- codice dell'imballo
|
|
DECLARE @CodClient NVARCHAR(50) = '' -- codice cliente
|
|
DECLARE @NumImb DECIMAL(9,6) = 0 -- num imballatori
|
|
DECLARE @PkgDesc NVARCHAR(50) = '' -- descrizione imballo
|
|
DECLARE @PRMWeight DECIMAL(9,4) = 0 -- peso dell'imballo
|
|
DECLARE @PWeight DECIMAL(9,4) = 0 -- peso del pacco
|
|
DECLARE @PTare DECIMAL(9,4) = 0 -- media delal tara di imballo dichiarata
|
|
DECLARE @PCost DECIMAL(9,6) = 0 -- costo unitario dell'imballo (al kg)
|
|
DECLARE @PTareStRatio DECIMAL(9,4) = 0 -- coefficiente di recupero tara/prodotto
|
|
|
|
-- TRASPORTI
|
|
DECLARE @TranspZone NVARCHAR(50) = '', -- codice ZONA
|
|
@TranspCost DECIMAL(9,6), -- costo di trasporto
|
|
@HasTC INT
|
|
|
|
-- PROVVIGIONI
|
|
DECLARE @Provvig DECIMAL(9,6) -- provvigione agente
|
|
|
|
|
|
-- default: validità è true!
|
|
SET @valid = 1
|
|
|
|
|
|
---------------------------------------
|
|
-- svuotamento iniziale
|
|
---------------------------------------
|
|
-- elimino parametri!
|
|
DELETE
|
|
FROM dbo.QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
-- elimino lavorazioni interne!
|
|
DELETE
|
|
FROM dbo.QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
-- elimino preventivamente OtherCosts già inseriti!
|
|
DELETE
|
|
FROM dbo.QuoteOC
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
-- elimino lavorazioni esterne!
|
|
DELETE
|
|
FROM dbo.QuoteWorkExt
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
-- se ci sono eccezioni nella tab dell'anagrafico articoli imposto il codPlant desiderato
|
|
SET @CodPlantFix = ISNULL ( ( SELECT id.CodPlant FROM dbo.ItemDet id INNER JOIN dbo.QuoteList ql ON id.CodItem = ql.CodItem
|
|
WHERE ql.QuoteType = @QuoteType
|
|
AND ql.CodQuote = @CodQuote
|
|
AND ql.QuoteRev = @QuoteRev ), '' )
|
|
|
|
-- recupero da dati dell'offerta il valore del cliente
|
|
SELECT @CodClient = CodClient
|
|
FROM dbo.QuoteList
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
|
|
-- recupero da dati dell'offerta il valore del package scelto
|
|
SELECT @CodPackag = CodPackag
|
|
FROM dbo.QuoteRM
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
-- recupero dati!!
|
|
SELECT TOP 1 @CodItem = ql.CodItem
|
|
, @CodDiesGroup = tps.CodDiesGroup
|
|
, @CodPlant = CASE WHEN @CodPlantFix <> '' THEN @CodPlantFix ELSE tps.CodPlant END
|
|
, @HoleNum = tps.HoleNum
|
|
, @KeyAM = ql.KeyAM
|
|
, @KeyAMI = ql.KeyAM + '-' + ISNULL(@CodPlant,'0000')
|
|
, @KeyAMIL = ql.KeyAM + '-' + ISNULL(@CodPlant,'0000') + '-' + CAST(ISNULL(tps.HoleNum,0) AS NVARCHAR(10))
|
|
, @ProdTeo = tps.ProdTeo
|
|
, @ScTec = tps.ScTec
|
|
, @KgTeo = tps.KgTeo
|
|
, @NumSMED = tps.NumSMED
|
|
, @RawMat = ql.RawMat
|
|
, @BatchQty = ql.BatchQty
|
|
, @ItemDescr= i.ItemDescr
|
|
FROM dbo.QuoteList ql
|
|
INNER JOIN dbo.TabProdSc tps ON ql.KeyAM = tps.KeyAM
|
|
INNER JOIN dbo.ItemDet i ON ql.CodItem = i.CodItem
|
|
WHERE ql.QuoteType = @QuoteType
|
|
AND ql.CodQuote = @CodQuote
|
|
AND ql.QuoteRev = @QuoteRev
|
|
ORDER BY tps.KgTeo DESC
|
|
|
|
|
|
-- inserisco note sull'articolo
|
|
SELECT @cont = ISNULL(MAX(NumP),0) + 1
|
|
FROM dbo.QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO dbo.QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
VALUES (@QuoteType, @CodQuote, @QuoteRev, @cont, '01', 'ItemDescr', @ItemDescr)
|
|
|
|
SELECT @cont = @cont + 1
|
|
INSERT INTO dbo.QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, @cont, '01', 'CodifProfilo', Class01
|
|
FROM dbo.ItemDet
|
|
WHERE CodItem = @CodItem
|
|
|
|
SELECT @cont = @cont + 1
|
|
INSERT INTO dbo.QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, @cont, '01', 'ClasseMerc', Class02
|
|
FROM dbo.ItemDet
|
|
WHERE CodItem = @CodItem
|
|
|
|
SELECT @cont = @cont + 1
|
|
INSERT INTO dbo.QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, @cont, '01', 'ClienteAssoc', Class03
|
|
FROM dbo.ItemDet
|
|
WHERE CodItem = @CodItem
|
|
|
|
-- INFORMAZIONI su MATRICE
|
|
SELECT @cont = @cont + 1
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
VALUES (@QuoteType, @CodQuote, @QuoteRev, @cont, '01', 'CodDiesGroup', @CodDiesGroup)
|
|
|
|
SELECT @cont = @cont + 1
|
|
SET @strTemp = dbo.f_getSubstring(@CodDiesGroup, '-', 1) --@strTemp = SUBSTRING(@CodDiesGroup, 0, CHARINDEX('-', @CodDiesGroup)) -- !!!fare con finc Gian
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
VALUES (@QuoteType, @CodQuote, @QuoteRev, @cont, '01', 'TipoDies', @strTemp)
|
|
|
|
SELECT @cont = @cont + 1
|
|
SET @strTemp = dbo.f_getSubstring(@CodDiesGroup, '-', 2) --@strTemp = SUBSTRING(REPLACE(@CodDiesGroup, @strTemp,''), 2, CHARINDEX('-', @CodDiesGroup)) -- !!!fare con finc Gian
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
VALUES (@QuoteType, @CodQuote, @QuoteRev, @cont, '01', 'DiamDies', @strTemp)
|
|
|
|
SELECT @cont = @cont + 1
|
|
SET @strTemp = dbo.f_getSubstring(@CodDiesGroup, '-', 3) --@strTemp = SUBSTRING(REPLACE(@CodDiesGroup, @strTemp,''), 5, CHARINDEX('-', @CodDiesGroup)) -- !!!fare con finc Gian
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
VALUES (@QuoteType, @CodQuote, @QuoteRev, @cont, '01', 'HoleNumDies', @strTemp)
|
|
|
|
SELECT @cont = @cont + 1
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, @cont, '01', 'NumDiesInList', COUNT(CodDies)
|
|
FROM dbo.DiesDet
|
|
WHERE CodDiesGroup = @CodDiesGroup AND CodItem = @CodItem
|
|
|
|
SELECT @cont = @cont + 1
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, @cont, '01', 'NumDiesExaust', COUNT(CodDies)
|
|
FROM dbo.DiesDet
|
|
WHERE CodDiesGroup = @CodDiesGroup AND Active = 0
|
|
|
|
|
|
-- inserisco lavorazione estrusione!
|
|
INSERT INTO dbo.QuoteWorkInt(QuoteType, CodQuote, QuoteRev, NumWI, ProcessNum, CodPlant, Class01, Class02, Class03, NetProd, WSR)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumWI),0) + 1, '01', @CodPlant, @KeyAM, @KeyAMIL, @RawMat, @ProdTeo, @ScTec
|
|
FROM QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
|
|
-- inserisco parametri calcolati x lavorazione estruzione
|
|
INSERT INTO dbo.QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '01', 'CodPlant', @CodPlant
|
|
FROM dbo.QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '01', 'KeyAM', @KeyAM
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '01', 'KeyAMIL', @KeyAMIL
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '01', 'NumCambi', @NumSMED
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '01', 'KgTeo', @KgTeo
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '01', 'HoleNum', @HoleNum
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
SELECT @cont = ISNULL(MAX(NumP),0) + 1
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, @cont, '01', 'KgProdotti', SUM(KgTeo)
|
|
FROM QuoteList ql
|
|
INNER JOIN TabProdSc tps ON ql.KeyAM = tps.KeyAM
|
|
WHERE ql.QuoteType = @QuoteType
|
|
AND ql.CodQuote = @CodQuote
|
|
AND ql.QuoteRev = @QuoteRev
|
|
|
|
--SELECT @cont = @cont + 1
|
|
--INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
--SELECT @QuoteType, @CodQuote, @QuoteRev, @cont, '01', 'NumLotti', COUNT(*)
|
|
--FROM QuoteList ql INNER JOIN TabProdSc tps ON ql.KeyAM = tps.KeyAM
|
|
--WHERE ql.QuoteType = @QuoteType
|
|
-- AND ql.CodQuote = @CodQuote
|
|
-- AND ql.QuoteRev = @QuoteRev
|
|
|
|
-- inserisco dati mancanti di MSR/CSR (scarto cesoia x estrusione) - DSR è NULLO x estrusione!
|
|
UPDATE qwi
|
|
SET MSR = rmd.CSR
|
|
FROM dbo.QuoteWorkInt qwi
|
|
INNER JOIN RawMatDet rmd ON qwi.Class03 = rmd.RawMat
|
|
WHERE qwi.QuoteType = @QuoteType
|
|
AND qwi.CodQuote = @CodQuote
|
|
AND qwi.QuoteRev = @QuoteRev
|
|
|
|
-- calcolo i costi legati alla fase estrusione!
|
|
UPDATE qwi
|
|
SET FC4UG = (cp.FC * cp.QuotaPlantStd / cp.QuotaManStd) / qwi.NetProd
|
|
, VC4UG = (cp.VC * cp.QuotaPlantStd / cp.QuotaManStd) / qwi.NetProd
|
|
, OH4UG = (cp.OH * cp.QuotaPlantStd / cp.QuotaManStd) / qwi.NetProd
|
|
, SC4UG = cp.SC / ql.BatchQty
|
|
FROM dbo.QuoteWorkInt qwi
|
|
INNER JOIN Cost2Plant cp ON qwi.CodPlant = cp.CodPlant AND qwi.ProcessNum = cp.ProcessNum
|
|
INNER JOIN QuoteList ql ON qwi.QuoteType = ql.QuoteType AND qwi.CodQuote = ql.CodQuote AND qwi.QuoteRev = ql.QuoteRev
|
|
WHERE qwi.QuoteType = @QuoteType
|
|
AND qwi.CodQuote = @CodQuote
|
|
AND qwi.QuoteRev = @QuoteRev
|
|
|
|
-- inserisco lavorazione incestatura!
|
|
INSERT INTO QuoteWorkInt(QuoteType, CodQuote, QuoteRev, NumWI, ProcessNum, CodPlant, Class01, Class02, Class03)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumWI),0) + 1, '02', @CodPlant, @KeyAM, @KeyAMI, @RawMat
|
|
FROM dbo.QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
|
|
-- inserisco dati mancanti di DSR x incestatura come 1 - OUT/IN
|
|
UPDATE qwi
|
|
SET DSR = 1 - CAST(td.TotOUT AS DECIMAL(18,6))/CAST(td.TotIN AS DECIMAL(18,6))
|
|
FROM dbo.QuoteWorkInt qwi
|
|
INNER JOIN dbo.TabDifett td ON qwi.Class02 = td.KeyAMI AND qwi.ProcessNum = td.ProcessNum
|
|
WHERE qwi.ProcessNum = '02' -- solo incestatura
|
|
|
|
-- inserisco lavorazione FORNO!
|
|
INSERT INTO dbo.QuoteWorkInt(QuoteType, CodQuote, QuoteRev, NumWI, ProcessNum, CodPlant, Class01, Class02, Class03)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumWI),0) + 1, '03', @CodPlant, @KeyAM, @KeyAMI, @RawMat
|
|
FROM dbo.QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
-- inserisco lavorazione imballo!
|
|
INSERT INTO dbo.QuoteWorkInt(QuoteType, CodQuote, QuoteRev, NumWI, ProcessNum, CodPlant, Class01, Class02, Class03, Class04, Class05)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumWI),0) + 1, '04', @CodPlant, @KeyAM, @KeyAMIL, @RawMat, @CodItem, @KeyAMI
|
|
FROM dbo.QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
-- calcolo produttività netta nella fase imballo
|
|
UPDATE qwi
|
|
SET NetProd = ti.TotOut/ ti.Durata
|
|
FROM dbo.QuoteWorkInt qwi
|
|
INNER JOIN TabImbArt ti ON qwi.Class04 = ti.KeyA
|
|
|
|
-- salvo numero imballatori
|
|
SELECT @NumImb = ISNULL(NumImb,0)
|
|
FROM TabImbArt
|
|
WHERE KeyA = @CodItem
|
|
|
|
-- parametro num imballatori
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '04', 'NumImballatori', @NumImb
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
-- calcolo i costi legati alla fase di imballo!
|
|
UPDATE qwi
|
|
SET FC4UG = (cp.FC * cp.QuotaPlantStd / cp.QuotaManStd) / qwi.NetProd
|
|
, VC4UG = (cp.VC * cp.QuotaPlantStd / cp.QuotaManStd) / qwi.NetProd
|
|
, OH4UG = (cp.OH * cp.QuotaPlantStd / cp.QuotaManStd) / qwi.NetProd
|
|
, SC4UG = cp.SC / ql.BatchQty
|
|
FROM dbo.QuoteWorkInt qwi
|
|
INNER JOIN Cost2Plant cp ON qwi.CodPlant = cp.CodPlant AND qwi.ProcessNum = cp.ProcessNum
|
|
INNER JOIN QuoteList ql ON qwi.QuoteType = ql.QuoteType AND qwi.CodQuote = ql.CodQuote AND qwi.QuoteRev = ql.QuoteRev
|
|
WHERE qwi.ProcessNum = '04' -- solo imballo
|
|
|
|
|
|
-- inserisco dati mancanti di DSR x incestatura come 1 - OUT/IN
|
|
UPDATE qwi
|
|
SET DSR = 1 - CAST(td.TotOUT AS DECIMAL(18,6))/CAST(td.TotIN AS DECIMAL(18,6))
|
|
FROM dbo.QuoteWorkInt qwi
|
|
INNER JOIN TabDifett td ON qwi.Class05 = td.KeyAMI AND qwi.ProcessNum = td.ProcessNum
|
|
WHERE qwi.ProcessNum = '04' -- solo imballo
|
|
|
|
|
|
-- recupero le MAC fase 01
|
|
SELECT
|
|
@MAC_SC = MCR
|
|
,@MAC_ST = WCR
|
|
FROM dbo.QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
AND ProcessNum = '01'
|
|
|
|
-- recupero le MAC fase 02
|
|
SELECT
|
|
@MAC_IN = DCR
|
|
FROM dbo.QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
AND ProcessNum = '02'
|
|
|
|
-- recupero le MAC fase 04
|
|
SELECT
|
|
@MAC_IM = DCR
|
|
FROM dbo.QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
AND ProcessNum = '04'
|
|
|
|
-- calcolo percentuale vendita rottame
|
|
SELECT @SSR = SSR
|
|
FROM dbo.RawMatDet
|
|
WHERE RawMat = @RawMat
|
|
|
|
-- update rese RawMat!
|
|
UPDATE dbo.QuoteRM
|
|
SET FCR = @MAC_SC * @MAC_ST * @MAC_IN * @MAC_IM
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
AND RawMat = @RawMat
|
|
|
|
|
|
|
|
|
|
-- inserisco i costi delle utilities in QuoteOC
|
|
|
|
-- inserisco altri costi x lavorazione estrusione! GAS ed EE
|
|
INSERT INTO dbo.QuoteOC(QuoteType, CodQuote, QuoteRev, NumEx, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ROW_NUMBER() OVER(ORDER BY CodUtil ASC) AS Row, '01', @CodPlant, CodUtil, @RawMat, @BatchQty, UnitCost * @BatchQty, 1
|
|
FROM dbo.Utilities
|
|
WHERE CodPlant = @CodPlant
|
|
|
|
|
|
/*************************************************************
|
|
* MATRICE
|
|
*
|
|
* calcolo prezzo e durata matrice con procedura a 5 step...
|
|
* A) media ponderata prezzo ed ExpLife da DiesDet selezionando x CodItem + CodDiesGroup
|
|
* B) EOLLife x media ponderata su DiesDet dove Active = 0
|
|
* C) SE prezzo = 0 --> prendo prezzo da DiesGroupDet selezionando x CodDiesGroup
|
|
* D) Se EOL = 0 --> prendo ExpLife della singola matrice, se è 0 --> prendo EOLLife da DiesGroupDet selezionando x CodDiesGroup
|
|
* E) Calcolo Price / Life
|
|
*
|
|
*************************************************************/
|
|
|
|
-- (A) da DiesDet
|
|
SELECT @DiesPrice = SUM(ISNULL(Price, 0) * ISNULL(ActLife, 1)) / SUM(ISNULL(ActLife, 1)),
|
|
@DiesExpLife = SUM(ISNULL(ExpLife, 0) * ISNULL(ActLife, 1)) / SUM(ISNULL(ActLife, 1))
|
|
FROM dbo.DiesDet
|
|
WHERE CodItem = @CodItem
|
|
AND CodDiesGroup = @CodDiesGroup
|
|
GROUP BY CodItem, CodDiesGroup
|
|
|
|
-- (B) da DiesDet
|
|
SELECT @DiesEOLife = AVG(ISNULL(ActLife, 0))
|
|
FROM dbo.DiesDet
|
|
WHERE CodItem = @CodItem
|
|
AND CodDiesGroup = @CodDiesGroup
|
|
AND Active = 0
|
|
GROUP BY CodItem, CodDiesGroup
|
|
|
|
SET @DiesPrice = ISNULL(@DiesPrice,0)
|
|
SET @DiesExpLife = ISNULL(@DiesExpLife,0)
|
|
SET @DiesEOLife = ISNULL(@DiesEOLife,0)
|
|
|
|
-- (C) verifico se prezzo è OK...
|
|
IF (@DiesPrice = 0)
|
|
BEGIN
|
|
-- calcolo da DiesGroupDet
|
|
SELECT @DiesPrice = ISNULL(Price, 0)
|
|
FROM DiesGroupDet
|
|
WHERE CodDiesGroup = @CodDiesGroup
|
|
END
|
|
|
|
-- (D) verifico se vita è OK...
|
|
IF (@DiesEOLife = 0)
|
|
BEGIN
|
|
-- uso exp life articolo...
|
|
SET @DiesEOLife = @DiesExpLife
|
|
END
|
|
-- controllo che non sia nulla/vuota la durata...
|
|
IF (@DiesEOLife = 0)
|
|
BEGIN
|
|
SELECT @DiesEOLife = CASE WHEN EOLLife > 0 THEN EOLLife ELSE ExpLife END
|
|
FROM DiesGroupDet
|
|
WHERE CodDiesGroup = @CodDiesGroup
|
|
END
|
|
|
|
-- controllo che non sia nulla/vuota la durata...
|
|
IF (@DiesEOLife = 0)
|
|
BEGIN
|
|
SELECT @DiesEOLife = 1 -- metto 1 kg che è sbagliato e mi fa esplodere prezzo matrice ma non blocca calcoli
|
|
END
|
|
|
|
-- (E) Calcolo price/life!
|
|
SET @DiesPriceLife = @DiesPrice / @DiesEOLife
|
|
|
|
-- (D) verifico se vita è OK...
|
|
IF (@DiesPriceLife = 0)
|
|
BEGIN
|
|
SET @DiesPriceLife = 1
|
|
SET @valid = 0
|
|
END
|
|
|
|
-- inserisco costi per Matrice: costo e durata da matrice, se non ci sono da gruppo, (anche solo uno dei due, ovviamente più facile trovare durata x matrice...)
|
|
INSERT INTO QuoteOC(QuoteType, CodQuote, QuoteRev, NumEx, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota, valid)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumEx),0) + 1, '01', @CodPlant, 'MATRICE', @RawMat, @BatchQty, @DiesPriceLife * @BatchQty, 1, @valid
|
|
FROM QuoteOC
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
|
|
-- inserisco parametri calcolati x matrice
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '01', 'DiesPrice', @DiesPrice
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '01', 'DiesExpLife', @DiesExpLife
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '01', 'DiesEOLife', @DiesEOLife
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
/*************************************************************
|
|
* VENDITA ROTTAME
|
|
*
|
|
* calcolo COSTO DI VENDITA PER LE 2 FASI ESTRUSIONE 01 ED IMBALLO 04
|
|
* - calcolo costi variabili fase 01 / 04
|
|
* - calcolo costo di vendita x fase 01 / 04
|
|
*************************************************************/
|
|
|
|
SELECT @OCF01 = SUM(ISNULL(C4UG,0))
|
|
FROM QuoteOC
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
SELECT @VCF01 = @OCF01 + VC4UG
|
|
FROM QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
AND ProcessNum = '01'
|
|
|
|
SELECT @VCF04 = @OCF01 + SUM(VC4UG)
|
|
FROM dbo.QuoteWorkInt
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
SELECT @SellCostF01 = ((@MAC_ST * @MAC_SC * @MAC_IN) - 1) * (q.RawMatCost - CASE WHEN SSVA > 0 THEN SSVA ELSE q.RawMatCost * SSVR END + @VCF01) -- ((prodotto delle MAC = inverso del prodotto delle rese) - 1) * (costo Al - prezzo rivendita + costi var fase 01)
|
|
, @RefCostF01 = -- ( (A - B) / 100 - C )
|
|
(
|
|
-- (A) = (costo pieno di acquisto + costi fase 1) * MAC * 100 (IN CENTESIMI)
|
|
((q.RawMatCost + q.RawMatExtraCost + @VCF01) * (@MAC_ST * @MAC_SC * @MAC_IN * 100))
|
|
-- (B) = ((MAC * 100) - 100) * Resa di fonderia * (costo pieno acquisto - costo di trasformazione))
|
|
- ((@MAC_ST * @MAC_SC * @MAC_IN * 100) - 100) * rmd.ProcYield * (q.RawMatCost + q.RawMatExtraCost + @VCF01 - rmd.ProcCost)
|
|
) / 100 -- divido per 100 finale
|
|
-- (C) = sottraggo costo pieno alluminio
|
|
- (q.RawMatCost + q.RawMatExtraCost + @VCF01)
|
|
FROM dbo.RawMatDet rmd
|
|
INNER JOIN dbo.QuoteRM q ON q.RawMat = rmd.RawMat
|
|
WHERE q.QuoteType = @QuoteType
|
|
AND q.CodQuote = @CodQuote
|
|
AND q.QuoteRev = @QuoteRev
|
|
AND q.RawMat = @RawMat
|
|
|
|
SELECT @SellCostF04 = ((@MAC_IM) - 1) * (q.RawMatCost - CASE WHEN SSVA > 0 THEN SSVA ELSE q.RawMatCost * SSVR END + @VCF01 + @VCF04) -- ((prodotto delle MAC = inverso del prodotto delle rese) - 1) * (costo Al - prezzo rivendita + costi var fase 04)
|
|
, @RefCostF04 = -- ( (A - B) / 100 - C )
|
|
(
|
|
-- (A) = (costo pieno di acquisto + costi fase 1) * MAC * 100 (IN CENTESIMI)
|
|
((q.RawMatCost + q.RawMatExtraCost + @VCF01 + @VCF04) * (@MAC_IM * 100))
|
|
-- (B) = ((MAC * 100) - 100) * Resa di fonderia * (costo pieno acquisto - costo di trasformazione))
|
|
- ((@MAC_IM * 100) - 100) * rmd.ProcYield * (q.RawMatCost + q.RawMatExtraCost + @VCF01 + @VCF04 - rmd.ProcCost)
|
|
) / 100 -- divido per 100 finale
|
|
-- (C) = sottraggo costo pieno alluminio
|
|
- (q.RawMatCost + q.RawMatExtraCost + @VCF01 + @VCF04)
|
|
|
|
-- NOTA: usiamo (q.RawMatCost + q.RawMatExtraCost + @VCF01) e non (q.RawMatCost + q.RawMatExtraCost + @VCF01 + @RefCostF01) perché la differenza è minimale, si tratta di 10/20 PPM...
|
|
|
|
FROM RawMatDet rmd INNER JOIN QuoteRM q ON q.RawMat = rmd.RawMat
|
|
WHERE q.QuoteType = @QuoteType
|
|
AND q.CodQuote = @CodQuote
|
|
AND q.QuoteRev = @QuoteRev
|
|
AND q.RawMat = @RawMat
|
|
|
|
-- inserisco costi per VENDITA
|
|
INSERT INTO QuoteOC(QuoteType, CodQuote, QuoteRev, NumEx, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumEx),0) + 1, '01', @CodPlant, 'VENDITA', @RawMat, @BatchQty, @SellCostF01 * @BatchQty, @SSR
|
|
FROM QuoteOC
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
-- inserisco costi per ROTTAME
|
|
INSERT INTO QuoteOC(QuoteType, CodQuote, QuoteRev, NumEx, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumEx),0) + 1, '01', @CodPlant, 'ROTTAME', @RawMat, @BatchQty, @RefCostF01 * @BatchQty, 1 - @SSR
|
|
FROM QuoteOC
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
-- inserisco costi per VENDITA
|
|
INSERT INTO QuoteOC(QuoteType, CodQuote, QuoteRev, NumEx, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumEx),0) + 1, '04', @CodPlant, 'VENDITA', @RawMat, @BatchQty, @SellCostF04 * @BatchQty, @SSR
|
|
FROM QuoteOC
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
-- inserisco costi per ROTTAME
|
|
INSERT INTO QuoteOC(QuoteType, CodQuote, QuoteRev, NumEx, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumEx),0) + 1, '04', @CodPlant, 'ROTTAME', @RawMat, @BatchQty, @RefCostF04 * @BatchQty, 1 - @SSR
|
|
FROM dbo.QuoteOC
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
|
|
EXEC stp_packageCost @CodItem, @CodClient, @CodPackag, @PDesc=@PkgDesc OUTPUT, @RMWeight=@PRMWeight OUTPUT, @Weight=@PWeight OUTPUT, @Tare=@PTare OUTPUT, @PackCost=@PCost OUTPUT, @TareStRat=@PTareStRatio OUTPUT
|
|
|
|
-- inserisco costi per IMBALLO
|
|
INSERT INTO QuoteOC(QuoteType, CodQuote, QuoteRev, NumEx, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumEx),0) + 1, '04', @CodPlant, 'IMBALLO', @RawMat, @BatchQty, @PCost * @BatchQty, 1
|
|
FROM dbo.QuoteOC
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
-- inserisco parametri calcolati x imballaggio
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '04', 'PkgDesc', @PkgDesc
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '04', 'RMWeight', @PRMWeight
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '04', 'PWeight', @PWeight
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '04', 'PTare', @PTare
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
INSERT INTO QuoteParam(QuoteType, CodQuote, QuoteRev, NumP, ProcessNum, Descr, Parameter)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumP),0) + 1, '04', 'TareStRatio', @PTareStRatio
|
|
FROM QuoteParam
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
|
|
---------------------------------------------
|
|
-- inserisco costi per TRASPORTI
|
|
---------------------------------------------
|
|
-- recupero zona da cliente...
|
|
SELECT @TranspZone = TranspZone
|
|
FROM dbo.ClientDet
|
|
WHERE CodClient = @CodClient
|
|
|
|
-- recupero tipo di trasporto
|
|
SELECT @HasTC = ISNULL(i.HasTC, 0)
|
|
FROM QuoteList q INNER JOIN Incoterms i ON q.CodInco = i.CodInco
|
|
WHERE q.QuoteType = @QuoteType
|
|
AND q.CodQuote = @CodQuote
|
|
AND q.QuoteRev = @QuoteRev
|
|
|
|
-- recupero costi trasporto
|
|
SELECT @TranspCost = TranspCost
|
|
FROM dbo.TranspCostDet
|
|
WHERE TranspZone = @TranspZone
|
|
|
|
INSERT INTO QuoteOC(QuoteType, CodQuote, QuoteRev, NumEx, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumEx),0) + 1, '04', @CodPlant, 'TRASPORTI', @RawMat, @BatchQty, @HasTC * @TranspCost * @BatchQty, 1
|
|
FROM dbo.QuoteOC
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
|
|
---------------------------------------------
|
|
-- inserisco costi per PROVVIGIONI
|
|
---------------------------------------------
|
|
SELECT @Provvig = ISNULL(a.QuotaProvvAg, 0)
|
|
FROM dbo.AgentsDet a
|
|
INNER JOIN ClientDet c ON a.CodAg = c.CodAg
|
|
INNER JOIN QuoteList q ON q.CodClient = c.CodClient
|
|
WHERE q.QuoteType = @QuoteType
|
|
AND q.CodQuote = @CodQuote
|
|
AND q.QuoteRev = @QuoteRev
|
|
|
|
-- inserisco costi per PROVVIGIONI
|
|
INSERT INTO QuoteOC(QuoteType, CodQuote, QuoteRev, NumEx, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota)
|
|
SELECT @QuoteType, @CodQuote, @QuoteRev, ISNULL(MAX(NumEx),0) + 1, '04', @CodPlant, 'PROVVIGIONI', @RawMat, @BatchQty, @Provvig * @BatchQty, 1
|
|
FROM dbo.QuoteOC
|
|
WHERE QuoteType = @QuoteType
|
|
AND CodQuote = @CodQuote
|
|
AND QuoteRev = @QuoteRev
|
|
|
|
|
|
|
|
COMMIT TRAN
|
|
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|