Egalware/C2P
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
develop
C2P/C2P_Project/dbo/Stored Procedures/stp_QL_update.sql
T
Samuele E. Locatelli 76452aa4dd Refresh Progetto DB
2014-10-21 16:42:06 +02:00

1122 lines
51 KiB
Transact-SQL
Raw Permalink Blame History

-- =============================================
-- Author: Steamware
-- Mod. date: 2014.10.21
-- Description: aggiorna le tabelle QuoteList con i dati selezionati dalla TabProdSc
-- =============================================
CREATE PROCEDURE [dbo].[stp_QL_update]
(
@QuoteType CHAR(1) = 'Q',
@CodQuote BIGINT = 0,
@QuoteRev INT = 0
)
AS
BEGIN
SET NOCOUNT ON;
-- dichiaro valori da recuperare
DECLARE @strTemp NVARCHAR(50) = '' --stringa temporanea x variabili "locali"
, @valid BIT -- bit validità generico totale
, @validStep BIT -- bit validità generico singolo step
, @errorScore INT -- punteggio errori (1 per errore o anche punti diversi x "Peso", da valutare
, @CodDiesGroup NVARCHAR(50) = ''
, @CodPlant NVARCHAR(50) = ''
, @CodPlantCMIL NVARCHAR(50)
, @CodPlantFix NVARCHAR(50) = ''
, @HoleNum INT
-- ITEM
DECLARE @CodItem NVARCHAR(50) = '' -- cod item
, @ItemDescr NVARCHAR(50) = '' -- descrizione item
, @CodifProfilo NVARCHAR(50) = '' -- Cod. Profilo
, @ClasseMerc NVARCHAR(50) = ''
, @CodClientAssoc NVARCHAR(50) = ''
, @KeyAM NVARCHAR(50) = ''
, @KeyAMI NVARCHAR(50) = ''
, @KeyAMIL NVARCHAR(50) = ''
, @NumSMED INT = 0 -- numero cambi
, @KgTeo DECIMAL(18, 6) = 0 -- kg totali
, @RawMat NVARCHAR(50)
, @ProdTeo DECIMAL(18,6) = 0
, @ProdTeo_sim DECIMAL(18,6) = 0
, @ScTec DECIMAL(18,6)
, @BatchQty INT
, @MAC_SC DECIMAL(18,6) -- Messa a cento scarto cesoia
, @MAC_ST DECIMAL(18,6) -- Messa a cento scarto tecnico
, @MAC_IN DECIMAL(18,6) -- Messa a cento difettosità all'incestatura
, @MAC_IM DECIMAL(18,6) -- Messa a cento difettosità all'imballo
, @MAC_SC_sim DECIMAL(18,6) = 0 -- Messa a cento scarto cesoia - SIMULAZIONE
, @MAC_ST_sim DECIMAL(18,6) = 0 -- Messa a cento scarto tecnico - SIMULAZIONE
, @MAC_IN_sim DECIMAL(18,6) = 0 -- Messa a cento difettosità all'incestatura - SIMULAZIONE
, @MAC_IM_sim DECIMAL(18,6) = 0 -- Messa a cento difettosità all'imballo - SIMULAZIONE
, @FCR_sim DECIMAL(18,6) = 0 -- Messa a cento complessiva - SIMULAZIONE
, @SSR DECIMAL(9,6) -- percentuale vendita rottame
, @DiesPrice DECIMAL(9,3) -- prezzo matrice
, @NumDiesExaust INT = 0 -- numero matrici rottamate
, @DiesEOLife DECIMAL(9,2) -- durata matrice CLASSE MATRICE
, @DiesExpLife DECIMAL(9,2) -- durata matrice ARTICOLO
, @DiesPriceLife DECIMAL(18,6) -- Price/Life matrice
, @DiesByClass BIT = 0 -- definisce se i dati della matrice siano calcolati puntualmente (0) o per classe (1)
, @OCF01 DECIMAL(18,6) = 0 -- altri costi di fase 01
, @VCF01 DECIMAL(18,6) = 0 -- costi variabili di fase 01
, @VCF04 DECIMAL(18,6) = 0 -- costi variabili di fase 04
, @SellCostF01 DECIMAL(18,6) = 0 -- costo vendita fase 01
, @SellCostF04 DECIMAL(18,6) = 0 -- costo vendita fase 04
, @RefCostF01 DECIMAL(18,6) = 0 -- costo rifusione fase 01
, @RefCostF04 DECIMAL(18,6) = 0 -- costo rifusione fase 04
-- IMBALLO
DECLARE @CodPackag NVARCHAR(50) = '0' -- codice dell'imballo
, @CodClient NVARCHAR(50) = '' -- codice cliente
, @NumImb DECIMAL(9,6) = 0 -- num imballatori
, @PkgDesc NVARCHAR(50) = '' -- descrizione imballo
, @PRMWeight DECIMAL(9,4) = 0 -- peso dell'imballo
, @PWeight DECIMAL(9,4) = 0 -- peso del pacco
, @PTare DECIMAL(9,4) = 0 -- media delal tara di imballo dichiarata
, @PCost DECIMAL(9,6) = 0 -- costo unitario dell'imballo (al kg)
, @PTareStRatio DECIMAL(9,4) = 0 -- coefficiente di recupero tara/prodotto
, @PTotQta DECIMAL(18,3) = 0 -- Qta Kg Tot Imballati
, @PDurata DECIMAL(18,6) = 0 -- Tempo d'imballaggio
, @PLiv NVARCHAR(50) = '' -- Livello Search dati Imballo
, @PLivDett NVARCHAR(50) = '' -- Key Livello Search Dati Imballo
, @ProdTeoImb DECIMAL(18,6) = 0 -- produttività SIMULATA imballo
, @ProdTeoImb_sim DECIMAL(18,6) = 0 -- produttività SIMULATA imballo
-- TRASPORTI
DECLARE @TranspZone NVARCHAR(50) = '' -- codice ZONA
, @TranspCost DECIMAL(9,6) = 0 -- costo di trasporto
, @HasTC INT
-- PROVVIGIONI
DECLARE @Provvig DECIMAL(9,6) -- provvigione agente
-- default: validità è true!
SET @valid = 1
-- default: error score è nullo
SET @errorScore = 0
-- =================================================
-- CREAZIONE TEMP TABLE
-- =================================================
-- elimino e ricreo #QuoteParam temp table... NON Serve nella stored
--IF OBJECT_ID('tempdb..#QuoteParam') IS NOT NULL
-- DROP TABLE #QuoteParam
CREATE TABLE #Quoteparam
(
Quotetype CHAR( 1 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
Codquote BIGINT NOT NULL,
Quoterev INT NOT NULL,
Nump INT IDENTITY( 1,1 ) NOT NULL,
Processnum NVARCHAR( 50 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
Descr NVARCHAR( 50 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
Parameter NVARCHAR( 100 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT(''),
--PRIMARY KEY
-- ( QuoteType ASC, CodQuote ASC, QuoteRev ASC ) WITH (PAD_INDEX = ON, FILLFACTOR = 99 )
);
-- oppure creo l'indice successivamente
--CREATE CLUSTERED INDEX #ix_Quoteparam ON #Quoteparam (QuoteType ASC, CodQuote ASC, QuoteRev ASC)
--WITH (PAD_INDEX = ON, FILLFACTOR = 100)
--CREATE STATISTICS [#ix_QuoteType_CodQuote] ON #Quoteparam ([QuoteType], [CodQuote], [QuoteRev])
-- elimino e ricreo ##QuoteWorkInt temp table...
--IF OBJECT_ID('tempdb..#QuoteWorkInt') IS NOT NULL DROP TABLE #QuoteWorkInt
CREATE TABLE #QuoteWorkInt
(
QuoteType CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CodQuote BIGINT NOT NULL,
QuoteRev INT NOT NULL,
NumWI INT IDENTITY(1,1) NOT NULL,
ProcessNum NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CodPlant NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
Class01 NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
Class02 NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
Class03 NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
Class04 NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
Class05 NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
NetProd DECIMAL(9, 3) NOT NULL DEFAULT ((0)),
WSR DECIMAL(9, 8) NOT NULL DEFAULT ((0)),
MSR DECIMAL(9, 8) NOT NULL DEFAULT ((0)),
DSR DECIMAL(9, 8) NOT NULL DEFAULT ((0)),
FC4UG DECIMAL(9, 6) NOT NULL DEFAULT ((0)),
VC4UG DECIMAL(9, 6) NOT NULL DEFAULT ((0)),
OH4UG DECIMAL(9, 6) NOT NULL DEFAULT ((0)),
SC4UG DECIMAL(9, 6) NOT NULL DEFAULT ((0)),
-- campi calcolati
WYR AS ((1)-WSR),
MYR AS ((1)-MSR),
DYR AS ((1)-DSR),
WCR AS ( CASE WHEN ((1)-WSR)=0 THEN 1 ELSE (1)/((1)-WSR) END ),
MCR AS ( CASE WHEN ((1)-MSR)=0 THEN 1 ELSE (1)/((1)-MSR) END ),
DCR AS ( CASE WHEN ((1)-DSR)=0 THEN 1 ELSE (1)/((1)-DSR) END ),
valid BIT NOT NULL DEFAULT((1)),
)
-- elimino e ricreo #QuoteOC temp table...
--IF OBJECT_ID('tempdb..#QuoteOC') IS NOT NULL DROP TABLE #QuoteOC
CREATE TABLE #QuoteOC
(
QuoteType CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CodQuote BIGINT NOT NULL,
QuoteRev INT NOT NULL,
NumEx INT IDENTITY(1,1) NOT NULL,
ProcessNum NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
CodPlant NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
Class01 NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
Class02 NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
Class03 NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
Class04 NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
Class05 NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
TotQty DECIMAL(18, 6) NOT NULL DEFAULT ((1)),
TotCost DECIMAL(18, 6) NOT NULL DEFAULT ((0)),
Quota DECIMAL(18, 6) NOT NULL DEFAULT ((1)),
C4UG AS ( CASE WHEN TotQty= 0 THEN 0 ELSE (TotCost*Quota) / TotQty END ),
valid BIT NOT NULL DEFAULT ((1))
)
-- ===================================
-- INIZIO TRANSAZIONE
-- ===================================
SET XACT_ABORT ON;
BEGIN TRAN
---------------------------------------
-- salvataggio parametri iniziale
---------------------------------------
DECLARE @NetProd01_sim DECIMAL(9,3) = 0 -- prod simulazione fase 01
DECLARE @NetProd04_sim DECIMAL(9,3) = 0 -- prod simulazione fase 04
-- cerco la prod netta fase 1 di simulazione...
SELECT @NetProd01_sim = CAST(Parameter AS DECIMAL(9,3))
FROM dbo.QuoteParam
WHERE descr = 'NetProd01_sim'
AND QuoteType = @QuoteType
AND CodQuote = @CodQuote
AND QuoteRev = @QuoteRev
-- cerco la prod netta fase 1 di simulazione...
SELECT @NetProd04_sim = CAST(Parameter AS DECIMAL(9,3))
FROM dbo.QuoteParam
WHERE descr = 'NetProd04_sim'
AND QuoteType = @QuoteType
AND CodQuote = @CodQuote
AND QuoteRev = @QuoteRev
--SELECT @NetProd01_sim as NetProd01_sim, @NetProd04_sim as NetProd04_sim
---------------------------------------
-- svuotamento iniziale
---------------------------------------
-- elimino quote PARAMETRI!
DELETE FROM dbo.QuoteParam
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
-- elimino lavorazioni INTERNE
DELETE FROM dbo.QuoteWorkInt
WHERE QuoteType = @QuoteType
AND CodQuote = @CodQuote
AND QuoteRev = @QuoteRev
-- elimino OtherCosts
DELETE FROM dbo.QuoteOC
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
IF (@QuoteType IN ('Q', 'R','C' ) ) -- Quotation, Reddittivity, Cost List
BEGIN
-- 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
, @CodifProfilo = i.Class01
, @ClasseMerc = i.Class02
, @CodClientAssoc = i.Class03
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
-- =================================================================================
-- recupero dall'offerta il valore del PACKAGE/IMBALLO scelto e i dati relativi
-- Secondo la logica Client-Item-Package ( vedi funzione )
-- =================================================================================
-- SET @CodPackag = ( SELECT dbo.f_getCodPackag( @CodItem , @CodClient ) )
-- attenzione è una function tabellare
--SELECT @CodPackag = CodPackag, @PTotQta = TotQta, @PWeight = Weight, @PTare = Tare,
-- @NumImb = NumImb, @PDurata = Durata, @PLiv = LivPackag , @PLivDett = LivPackagDet
--FROM f_getDataCodPackag ( @CodItem, @CodClient)
EXEC dbo.stp_getDataCodPackag @CodItem, @CodClient, @CodPackag = @CodPackag OUT, @TotQta = @PTotQta OUT, @Weight = @PWeight OUT, @Tare = @PTare OUT,
@NumImb = @NumImb OUT, @Durata = @PDurata OUT, @LivPackag = @PLiv OUT, @LivPackagDet = @PLivDett OUT
END
-- --------------------------------------------------------------------------------------------
-- se offerta tipo 'S' non recupero dati da storico ma li cerco da tabella apposita..
-- --------------------------------------------------------------------------------------------
ELSE IF (@QuoteType = 'S')
BEGIN
-- salvo i parametri da QuoteSimPar a #Quoteparam
INSERT INTO #Quoteparam(Quotetype, CodQuote, QuoteRev ,ProcessNum, Descr, Parameter)
SELECT Quotetype, CodQuote, QuoteRev, '01' AS ProcessNum, NomePar AS Descr, ValPar AS Parameter
FROM dbo.QuoteSimPar
WHERE QuoteType = @QuoteType
AND CodQuote = @CodQuote
AND QuoteRev = @QuoteRev
AND NomePar NOT IN ('CodDiesGroup', 'CodPlant')
-- recupero parametri da SIM iniziali......
;WITH myCTE as
( -- Pivot table
SELECT CodDiesGroup, CodPlant, CodPackag, NetProd01_sim, NetProd04_sim
FROM
( SELECT NomePar, ValPar
FROM dbo.QuoteSimPar
WHERE QuoteType = @QuoteType AND CodQuote = @CodQuote AND QuoteRev = @QuoteRev ) AS SourceTable
PIVOT ( MIN(ValPar) FOR NomePar IN ( CodDiesGroup, CodPlant, CodPackag, NetProd01_sim, NetProd04_sim )
) AS PivotTable
)
SELECT @CodDiesGroup = CodDiesGroup,
@CodPlant = CodPlant,
@CodPackag = ISNULL(CodPackag, 0),
@ProdTeo_sim = NetProd01_sim, -- ??? @ProdTeo_sim NON USATA?
@ProdTeoImb_sim = NetProd04_sim -- ??? @ProdTeoImb_sim NON USATA ?
FROM myCTE
-- 2014.10.12 anticipato calcolo x avere holeNum x chiave AMIL successiva...
SELECT @CodDiesGroup = ISNULL(@CodDiesGroup, '')
SELECT @CodPlant = ISNULL(@CodPlant, '')
SELECT @CodPlantCMIL = ISNULL(@CodPlant, '0000')
SELECT @HoleNum = dbo.f_getSubstring(@CodDiesGroup, '-', 3)
-- 2014.09.09 FIX calcolo prod teorica imballo da lookup...
SELECT TOP 1 @ProdTeo = tps.ProdTeo
FROM dbo.QuoteList ql
-- modifica 2014.10.12 messa chiave AMIL
--INNER JOIN dbo.TabProdSc tps ON ql.KeyAM = tps.KeyAM
INNER JOIN dbo.TabProdSc tps ON ql.KeyAM + '-' + @CodPlantCMIL + '-' + CAST(ISNULL(@HoleNum,0) AS NVARCHAR(10)) = tps.KeyAMIL
WHERE ql.QuoteType = @QuoteType
AND ql.CodQuote = @CodQuote
AND ql.QuoteRev = @QuoteRev
ORDER BY tps.KgTeo DESC
SELECT TOP 1 @CodItem = ql.CodItem
, @KeyAM = ql.KeyAM
, @KeyAMI = ql.KeyAM + '-' + @CodPlantCMIL
, @KeyAMIL = ql.KeyAM + '-' + @CodPlantCMIL + '-' + CAST(ISNULL(@HoleNum,0) AS NVARCHAR(10))
, @ScTec = 0
, @KgTeo = 0
, @NumSMED = 0
, @RawMat = ql.RawMat
, @BatchQty = ql.BatchQty
, @ItemDescr = i.ItemDescr
, @CodifProfilo = i.Class01
, @ClasseMerc = i.Class02
, @CodClientAssoc = i.Class03
FROM dbo.QuoteList ql
INNER JOIN dbo.ItemDet i ON ql.CodItem = i.CodItem
WHERE ql.QuoteType = @QuoteType
AND ql.CodQuote = @CodQuote
AND ql.QuoteRev = @QuoteRev
-- provo a caricare dati produzione da KeyAMIL + CodDiesGroup
SELECT TOP 1 @ScTec = ISNULL(ScTec, 0)
, @KgTeo = ISNULL(KgTeo, 0)
, @NumSMED = ISNULL(NumSMED, 0)
-- 2014.08.22 - TOLTO NON deve usare la teorica...
--, @ProdTeo = ISNULL(ProdTeo, 0) -- VERIFICARE!!! NON DOVREBBE FARLO... ha già calcolato
FROM dbo.TabProdSc
WHERE KeyAMIL = @KeyAMIL
AND CodDiesGroup = @CodDiesGroup
-- =================================================================================
-- carico dati package generali
-- =================================================================================
SELECT @PTotQta = TotQta, @PWeight = Weight, @PTare = Tare, @NumImb = NumImb, @PDurata = Durata
, @PLiv = 'Package', @PLivDett= CodPackag
FROM dbo.Package2 WHERE CodPackag = @CodPackag
--SELECT @CodPackag = CodPackag, @PTotQta = TotQta, @PWeight = Weight, @PTare = Tare,
-- @NumImb = NumImb, @PDurata = Durata, @PLiv = LivPackag , @PLivDett = LivPackagDet
--FROM f_getDataCodPackag ( @CodItem, @CodClient)
END
-- e aggiorno il CodPackage in QuoteRM
UPDATE dbo.QuoteRM
SET CodPackag = ISNULL(@CodPackag,'')
FROM dbo.QuoteRM
WHERE QuoteType = @QuoteType
AND CodQuote = @CodQuote
AND QuoteRev = @QuoteRev
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
VALUES ( @QuoteType, @CodQuote, @QuoteRev, '01', 'ItemDescr', @ItemDescr )
, ( @QuoteType, @CodQuote, @QuoteRev, '01', 'CodifProfilo', @CodifProfilo )
, ( @QuoteType, @CodQuote, @QuoteRev, '01', 'ClasseMerc', @ClasseMerc )
, ( @QuoteType, @CodQuote, @QuoteRev, '01', 'CodClientAssoc', @CodClientAssoc )
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
SELECT @QuoteType, @CodQuote, @QuoteRev, '01', 'ClientNameAssoc', ClientDet.ClientName
FROM dbo.ClientDet
WHERE ClientDet.CodClient = @CodClientAssoc
-- INFORMAZIONI su MATRICE
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
VALUES (@QuoteType, @CodQuote, @QuoteRev, '01', 'CodDiesGroup', ISNULL(@CodDiesGroup, '') )
SET @strTemp = dbo.f_getSubstring(@CodDiesGroup, '-', 1)
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
VALUES (@QuoteType, @CodQuote, @QuoteRev, '01', 'TipoDies', ISNULL(@strTemp, '') )
SET @strTemp = dbo.f_getSubstring(@CodDiesGroup, '-', 2)
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
VALUES (@QuoteType, @CodQuote, @QuoteRev, '01', 'DiamDies', CASE WHEN ISNULL(@strTemp,'') = '' THEN 1 ELSE @strTemp END )
SET @strTemp = dbo.f_getSubstring(@CodDiesGroup, '-', 3)
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
VALUES (@QuoteType, @CodQuote, @QuoteRev, '01', 'HoleNumDies', ISNULL(@strTemp, 1) )
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
SELECT @QuoteType, @CodQuote, @QuoteRev, '01', 'NumDiesInList', COUNT(CodDies)
FROM dbo.DiesDet
WHERE CodDiesGroup = @CodDiesGroup AND CodItem = @CodItem
------------------------------------------------------------------------------------------------------------
-- 2014.09.10 faccio MODIFICA x contare matrici esauste da articolo SE disponibili...
--
SELECT @NumDiesExaust = COUNT(CodDies)
FROM dbo.DiesDet
WHERE CodDiesGroup = @CodDiesGroup AND CodItem = @CodItem AND Active = 0
-- se zero ricalcolo x gruppo e salvo...
IF(@NumDiesExaust = 0)
BEGIN
SELECT @NumDiesExaust = COUNT(CodDies)
FROM dbo.DiesDet
WHERE CodDiesGroup = @CodDiesGroup AND Active = 0
END
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
VALUES ( @QuoteType, @CodQuote, @QuoteRev, '01', 'NumDiesExaust' , @NumDiesExaust)
--INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
--SELECT @QuoteType, @CodQuote, @QuoteRev, '01', 'NumDiesExaust', COUNT(CodDies)
--FROM dbo.DiesDet
--WHERE CodDiesGroup = @CodDiesGroup AND Active = 0
--
-- FINE modifica 2014.09.10
------------------------------------------------------------------------------------------------------------
-- fix valori null!!
SET @CodPlant = ISNULL(@CodPlant, '')
SET @KeyAM = ISNULL(@KeyAM, '')
SET @KeyAMIL = ISNULL(@KeyAMIL, '')
SET @NumSMED = ISNULL(@NumSMED, 0)
SET @BatchQty = ISNULL(@BatchQty, 0)
SET @KgTeo = ISNULL(@KgTeo, 0)
SET @RawMat = ISNULL(@RawMat, '')
SET @ProdTeo = ISNULL(@ProdTeo, 0)
SET @ProdTeoImb = ISNULL(@ProdTeoImb, 0)
SET @ScTec = ISNULL(@ScTec, 0)
-- inserisco parametri calcolati x lavorazione estrusione
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
VALUES ( @QuoteType, @CodQuote, @QuoteRev, '01', 'CodPlant' , @CodPlant)
,( @QuoteType, @CodQuote, @QuoteRev, '01', 'KeyAM' , @KeyAM)
,( @QuoteType, @CodQuote, @QuoteRev, '01', 'KeyAMIL' , @KeyAMIL)
,( @QuoteType, @CodQuote, @QuoteRev, '01', 'NumSMED' , CONVERT(NVARCHAR(100) , @NumSMED))
,( @QuoteType, @CodQuote, @QuoteRev, '01', 'KgTeo' , CONVERT(NVARCHAR(100) , @KgTeo))
-- -----------------------------------------
---- inserisco lavorazione estrusione!
-- -----------------------------------------
-- *** verifica errori ***
SELECT @errorScore = @errorScore + CASE WHEN @ProdTeo = 0 THEN 1 ELSE 0 END,
@valid = CASE WHEN @ProdTeo = 0 THEN 0 ELSE @valid END
INSERT INTO #QuoteWorkInt (QuoteType, CodQuote, QuoteRev, ProcessNum, CodPlant, Class01, Class02, Class03, NetProd, WSR, valid)
SELECT @QuoteType, @CodQuote, @QuoteRev, '01', @CodPlant, @KeyAM, @KeyAMIL, @RawMat, @ProdTeo, @ScTec, @valid
INSERT INTO #QuoteParam (QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
SELECT @QuoteType, @CodQuote, @QuoteRev, '01', 'KgProdotti', ISNULL(SUM(KgTeo),0)
FROM dbo.QuoteList ql
INNER JOIN dbo.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 #QuoteWorkInt
SET MSR = rmd.CSR
FROM #QuoteWorkInt qwi
INNER JOIN dbo.RawMatDet rmd ON qwi.Class03 = rmd.RawMat
IF (@QuoteType IN ('Q', 'R', 'C' ) ) -- Quotation, Reddittivity, Cost List
BEGIN
-- calcolo i costi legati alla fase estrusione!
UPDATE #QuoteWorkInt
SET FC4UG = CASE WHEN qwi.NetProd = 0 OR cp.QuotaManStd = 0 THEN -99 ELSE (cp.FC * cp.QuotaPlantStd / cp.QuotaManStd) / qwi.NetProd END
, VC4UG = CASE WHEN qwi.NetProd = 0 OR cp.QuotaManStd = 0 THEN -99 ELSE (cp.VC * cp.QuotaPlantStd / cp.QuotaManStd) / qwi.NetProd END
, OH4UG = CASE WHEN qwi.NetProd = 0 OR cp.QuotaManStd = 0 THEN -99 ELSE (cp.OH * cp.QuotaPlantStd / cp.QuotaManStd) / qwi.NetProd END
, SC4UG = CASE WHEN ql.BatchQty = 0 THEN -99 ELSE cp.SC / ql.BatchQty END
, valid = CASE WHEN qwi.NetProd = 0 OR cp.QuotaManStd = 0 OR ql.BatchQty = 0 THEN 0 ELSE qwi.valid END -- attenzione che è già impostato sopra
FROM #QuoteWorkInt qwi
INNER JOIN dbo.Cost2Plant cp ON qwi.CodPlant = cp.CodPlant AND qwi.ProcessNum = cp.ProcessNum
INNER JOIN dbo.QuoteList ql ON qwi.QuoteType = ql.QuoteType AND qwi.CodQuote = ql.CodQuote AND qwi.QuoteRev = ql.QuoteRev
WHERE qwi.ProcessNum = '01'
END
-- 2014.10.12 modifica x gestione del caso SIM come produttività da impiegare x calcoli
ELSE IF (@QuoteType = 'S' )
BEGIN
-- calcolo i costi legati alla fase estrusione!
UPDATE #QuoteWorkInt
SET FC4UG = CASE WHEN @NetProd01_sim = 0 OR cp.QuotaManStd = 0 THEN -99 ELSE (cp.FC * cp.QuotaPlantStd / cp.QuotaManStd) / @NetProd01_sim END
, VC4UG = CASE WHEN @NetProd01_sim = 0 OR cp.QuotaManStd = 0 THEN -99 ELSE (cp.VC * cp.QuotaPlantStd / cp.QuotaManStd) / @NetProd01_sim END
, OH4UG = CASE WHEN @NetProd01_sim = 0 OR cp.QuotaManStd = 0 THEN -99 ELSE (cp.OH * cp.QuotaPlantStd / cp.QuotaManStd) / @NetProd01_sim END
, SC4UG = CASE WHEN ql.BatchQty = 0 THEN -99 ELSE cp.SC / ql.BatchQty END
, valid = CASE WHEN @NetProd01_sim = 0 OR cp.QuotaManStd = 0 OR ql.BatchQty = 0 THEN 0 ELSE qwi.valid END -- attenzione che è già impostato sopra
FROM #QuoteWorkInt qwi
INNER JOIN dbo.Cost2Plant cp ON qwi.CodPlant = cp.CodPlant AND qwi.ProcessNum = cp.ProcessNum
INNER JOIN dbo.QuoteList ql ON qwi.QuoteType = ql.QuoteType AND qwi.CodQuote = ql.CodQuote AND qwi.QuoteRev = ql.QuoteRev
WHERE qwi.ProcessNum = '01'
END
-- *** verifica errori ***
SELECT @errorScore = @errorScore + CASE WHEN valid = 0 THEN 1 ELSE 0 END,
@valid = CASE WHEN valid = 0 THEN 0 ELSE @valid END
FROM #QuoteWorkInt qwi
--INNER JOIN dbo.Cost2Plant cp ON qwi.CodPlant = cp.CodPlant AND qwi.ProcessNum = cp.ProcessNum
--INNER JOIN dbo.QuoteList ql ON qwi.QuoteType = ql.QuoteType AND qwi.CodQuote = ql.CodQuote AND qwi.QuoteRev = ql.QuoteRev
WHERE qwi.ProcessNum = '01'
-- inserisco lavorazione incestatura!
INSERT INTO #QuoteWorkInt (QuoteType, CodQuote, QuoteRev, ProcessNum, CodPlant, Class01, Class02, Class03)
SELECT @QuoteType, @CodQuote, @QuoteRev, '02', @CodPlant, @KeyAM, @KeyAMI, @RawMat
-- inserisco dati mancanti di DSR x incestatura come 1 - OUT/IN
UPDATE #QuoteWorkInt
SET DSR = CASE td.TotIN WHEN 0 THEN -99 ELSE 1 - CAST(td.TotOUT AS DECIMAL(18,6)) / CAST(td.TotIN AS DECIMAL(18,6)) END ,
valid = CASE td.TotIN WHEN 0 THEN 0 ELSE 1 END
FROM #QuoteWorkInt qwi
INNER JOIN dbo.TabDifett td ON qwi.Class02 = td.KeyAMI AND qwi.ProcessNum = td.ProcessNum
WHERE qwi.ProcessNum = '02' -- solo incestatura
-- *** verifica errori ***
SELECT @errorScore = @errorScore + CASE WHEN qwi.valid = 0 THEN 1 ELSE 0 END,
@valid = CASE WHEN qwi.valid = 0 THEN 0 ELSE @valid END
FROM #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 #QuoteWorkInt(QuoteType, CodQuote, QuoteRev, ProcessNum, CodPlant, Class01, Class02, Class03)
SELECT @QuoteType, @CodQuote, @QuoteRev, '03', @CodPlant, @KeyAM, @KeyAMI, @RawMat
-- inserisco lavorazione imballo!
INSERT INTO #QuoteWorkInt(QuoteType, CodQuote, QuoteRev, ProcessNum, CodPlant, Class01, Class02, Class03, Class04, Class05)
SELECT @QuoteType, @CodQuote, @QuoteRev, '04', @CodPlant, @KeyAM, @KeyAMIL, @RawMat, @CodItem, @KeyAMI
-- 2014.08.22 inserito passaggio di calcolo produttività simulata imballo a seconda caso 'S' o caso standard...
DECLARE @valid04 BIT = 0
IF (@QuoteType <> 'S')
BEGIN
SELECT @ProdTeoImb = CASE @PDurata WHEN 0 THEN 0 ELSE @PTotQta / @PDurata END,
@valid04 = CASE WHEN @PDurata = 0 OR @PTotQta = 0 THEN 0 ELSE 1 END -- @PTotQta non può essere mai = 0
END
ELSE
BEGIN
SELECT @ProdTeoImb = CASE @PDurata WHEN 0 THEN 0 ELSE @PTotQta / @PDurata END,
@valid04 = 1
END
UPDATE qwi
SET NetProd = @ProdTeoImb,
valid = @valid04
FROM #QuoteWorkInt AS qwi
-- INNER JOIN dbo.TabImbArt ti ON qwi.Class04 = ti.KeyA
WHERE qwi.ProcessNum = '04' -- solo imballo
-- *** controllo errori
SELECT @errorScore = @errorScore + CASE WHEN @PDurata = 0 OR @PTotQta = 0 OR @PWeight = 0 THEN 1 ELSE 0 END, -- verificare @PWeight -> dove salvo l'errore?
@valid = CASE WHEN @PDurata = 0 OR @PTotQta = 0 OR @PWeight = 0 THEN 0 ELSE @valid END
-- parametro num imballatori
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
SELECT @QuoteType, @CodQuote, @QuoteRev, '04', 'QuotaMan', @NumImb
-- parametro Livello search package/imballo
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
SELECT @QuoteType, @CodQuote, @QuoteRev, '04', 'LivPackage', @PLiv
-- parametro Livello search package/imballo
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
SELECT @QuoteType, @CodQuote, @QuoteRev, '04', 'LivPackageDett', @PLivDett
-- calcolo i costi legati alla fase di imballo!
IF (@QuoteType IN ('Q', 'R', 'C' ) ) -- Quotation, Reddittivity, Cost List
BEGIN
UPDATE #QuoteWorkInt
SET FC4UG = CASE WHEN qwi.NetProd = 0 OR cp.QuotaManStd = 0 THEN -99 ELSE ( ( cp.FC * cp.QuotaPlantStd ) * ( @NumImb / cp.QuotaManStd ) ) / qwi.NetProd END
, VC4UG = CASE WHEN qwi.NetProd = 0 OR cp.QuotaManStd = 0 THEN -99 ELSE ( ( cp.VC * cp.QuotaPlantStd ) * ( @NumImb / cp.QuotaManStd ) ) / qwi.NetProd END
, OH4UG = CASE WHEN qwi.NetProd = 0 OR cp.QuotaManStd = 0 THEN -99 ELSE ( ( cp.OH * cp.QuotaPlantStd ) * ( @NumImb / cp.QuotaManStd ) ) / qwi.NetProd END
, SC4UG = CASE WHEN ql.BatchQty = 0 THEN -99 ELSE cp.SC / ql.BatchQty END
, valid = CASE WHEN qwi.NetProd = 0 OR cp.QuotaManStd = 0 OR ql.BatchQty = 0 THEN 0 ELSE 1 END
FROM #QuoteWorkInt qwi
INNER JOIN dbo.Cost2Plant cp ON qwi.CodPlant = cp.CodPlant AND qwi.ProcessNum = cp.ProcessNum
INNER JOIN dbo.QuoteList ql ON qwi.QuoteType = ql.QuoteType AND qwi.CodQuote = ql.CodQuote AND qwi.QuoteRev = ql.QuoteRev
WHERE qwi.ProcessNum = '04' -- solo imballo
END
ELSE IF (@QuoteType = 'S')
BEGIN
UPDATE #QuoteWorkInt
SET FC4UG = CASE WHEN @NetProd04_sim = 0 OR cp.QuotaManStd = 0 THEN -99 ELSE ( ( cp.FC * cp.QuotaPlantStd ) * ( @NumImb / cp.QuotaManStd ) ) / @NetProd04_sim END
, VC4UG = CASE WHEN @NetProd04_sim = 0 OR cp.QuotaManStd = 0 THEN -99 ELSE ( ( cp.VC * cp.QuotaPlantStd ) * ( @NumImb / cp.QuotaManStd ) ) / @NetProd04_sim END
, OH4UG = CASE WHEN @NetProd04_sim = 0 OR cp.QuotaManStd = 0 THEN -99 ELSE ( ( cp.OH * cp.QuotaPlantStd ) * ( @NumImb / cp.QuotaManStd ) ) / @NetProd04_sim END
, SC4UG = CASE WHEN ql.BatchQty = 0 THEN -99 ELSE cp.SC / ql.BatchQty END
, valid = CASE WHEN @NetProd04_sim = 0 OR cp.QuotaManStd = 0 OR ql.BatchQty = 0 THEN 0 ELSE 1 END
FROM #QuoteWorkInt qwi
INNER JOIN dbo.Cost2Plant cp ON qwi.CodPlant = cp.CodPlant AND qwi.ProcessNum = cp.ProcessNum
INNER JOIN dbo.QuoteList ql ON qwi.QuoteType = ql.QuoteType AND qwi.CodQuote = ql.CodQuote AND qwi.QuoteRev = ql.QuoteRev
WHERE qwi.ProcessNum = '04' -- solo imballo
END
-- *** verifica errori ***
SELECT @errorScore = @errorScore + CASE WHEN qwi.valid = 0 THEN 1 ELSE 0 END,
@valid = CASE WHEN qwi.valid = 0 THEN 0 ELSE @valid END
FROM #QuoteWorkInt qwi
--INNER JOIN dbo.Cost2Plant cp ON qwi.CodPlant = cp.CodPlant AND qwi.ProcessNum = cp.ProcessNum
--INNER JOIN dbo.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 imballo come 1 - OUT/IN
UPDATE #QuoteWorkInt
SET DSR = 1 - ( CASE WHEN CAST(td.TotIN AS DECIMAL(18,6)) = 0 THEN 1 ELSE CAST(td.TotOUT AS DECIMAL(18,6)) / CAST(td.TotIN AS DECIMAL(18,6)) END ) ,
valid = CASE WHEN td.TotIN = 0 THEN 0 ELSE 1 END
FROM #QuoteWorkInt qwi
INNER JOIN dbo.TabDifett td ON qwi.Class05 = td.KeyAMI AND qwi.ProcessNum = td.ProcessNum
WHERE qwi.ProcessNum = '04' -- solo imballo
-- *** verifica errori ***
SELECT @errorScore = @errorScore + CASE WHEN qwi.valid = 0 THEN 1 ELSE 0 END,
@valid = CASE WHEN qwi.valid = 0 THEN 0 ELSE @valid END
FROM #QuoteWorkInt qwi
--INNER JOIN dbo.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 #QuoteWorkInt
WHERE ProcessNum = '01'
-- recupero le MAC fase 02
SELECT @MAC_IN = DCR
FROM #QuoteWorkInt
WHERE ProcessNum = '02'
-- recupero le MAC fase 04
SELECT @MAC_IM = DCR
FROM #QuoteWorkInt
WHERE ProcessNum = '04'
-- calcolo percentuale vendita rottame
SELECT @SSR = SSR
FROM dbo.RawMatDet
WHERE RawMat = @RawMat
-- NOTA : la transazione posso probabilmente iniziarla qui visto che è il
-- primo Update/insert/delete su tabelle non temporanee delete a parte
IF (@QuoteType = 'S')
BEGIN
-- recupero i 3 scarti...
DECLARE @WSR01_sim DECIMAL(18,6) = 0
DECLARE @MSR01_sim DECIMAL(18,6) = 0
DECLARE @DSR_sim DECIMAL(18,6) = 0
-- recupero parametri da SIM iniziali......
;WITH myCTE2 as
(
-- Pivot table
SELECT WSR01_sim, MSR01_sim, DSR_sim
FROM
(SELECT NomePar, ValPar
FROM dbo.QuoteSimPar
WHERE QuoteType = @QuoteType
AND CodQuote = @CodQuote
AND QuoteRev = @QuoteRev
) AS SourceTable
PIVOT
( MIN(ValPar)
FOR NomePar IN
(
WSR01_sim, MSR01_sim, DSR_sim
)
) AS PivotTable
)
SELECT @MAC_ST_sim = 1 / (1 - CONVERT(DECIMAL(18,6), WSR01_sim)),
@MAC_SC_sim = 1 / (1 - CONVERT(DECIMAL(18,6), MSR01_sim)),
@MAC_IN_sim = 1,
@MAC_IM_sim = 1 / (1 - CONVERT(DECIMAL(18,6), DSR_sim))
FROM myCTE2
-- salvo la messa a cento da simulazione in variabile
SET @FCR_sim = CASE WHEN ( @MAC_ST_sim * @MAC_SC_sim * @MAC_IN_sim * @MAC_IM_sim ) = 0
THEN 1
ELSE ( @MAC_ST_sim * @MAC_SC_sim * @MAC_IN_sim * @MAC_IM_sim )
END
-- salvo in tabella!
UPDATE dbo.QuoteRM
SET FCR = ISNULL(@FCR_sim,1)
WHERE QuoteType = @QuoteType
AND CodQuote = @CodQuote
AND QuoteRev = @QuoteRev
AND RawMat = @RawMat
END
ELSE
BEGIN
-- 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
END
-- inserisco i costi delle utilities in QuoteOC
-- inserisco altri costi x lavorazione estrusione! GAS ed EE
INSERT INTO #QuoteOC(QuoteType, CodQuote, QuoteRev, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota)
SELECT @QuoteType, @CodQuote, @QuoteRev, '01', @CodPlant, CodUtil, @RawMat, @BatchQty, ISNULL(UnitCost * @BatchQty, 0), 1
FROM dbo.Utilities
WHERE CodPlant = @CodPlant
-- *** controllo errori - se non trovo costi GAS ed EE segnalo errore
IF @@ROWCOUNT = 0
SET @validStep = 0;
ELSE
SET @validStep = 1;
SELECT @errorScore = @errorScore + CASE WHEN @validStep = 0 THEN 1 ELSE 0 END,
@valid = CASE WHEN @validStep = 0 THEN 0 ELSE @valid END
/*************************************************************
* 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 e vita cmq superiore 5000 KG ( escludendo rotture accidentali )
* 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
*
*************************************************************/
-- se sono in simulazione leggo tutto direttamente da tab dbo.DiesGroupDet
IF (@QuoteType = 'S')
BEGIN
SELECT @DiesPrice = Price,
@DiesExpLife = ExpLife,
@DiesEOLife = CASE WHEN EOLLife > 0 THEN EOLLife ELSE ExpLife END,
@DiesByClass = 1 -- imposto come calcolata matrice da gruppo
FROM dbo.DiesGroupDet
WHERE CodDiesGroup = @CodDiesGroup
END
ELSE
BEGIN
-- (A) da DiesDet
SELECT @DiesPrice = CASE WHEN SUM(ISNULL(ActLife, 1)) <> 0 THEN SUM(ISNULL(Price, 0) * ISNULL(ActLife, 1)) / SUM(ISNULL(ActLife, 1)) END,
@DiesExpLife = CASE WHEN SUM(ISNULL(ActLife, 1)) <> 0 THEN SUM(ISNULL(ExpLife, 0) * ISNULL(ActLife, 1)) / SUM(ISNULL(ActLife, 1)) END
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 -- solo matrici rottamate
AND ActLife > 5000 -- solo se hanno Life > 5000 Kg ( non rotture accidentali )
GROUP BY CodItem, CodDiesGroup
END
-- continuo!
SET @DiesPrice = ISNULL(@DiesPrice,0)
SET @DiesExpLife = ISNULL(@DiesExpLife,0)
SET @DiesEOLife = ISNULL(@DiesEOLife,0)
SET @validStep = 1
-- (C) verifico se prezzo è OK...
IF (@DiesPrice = 0)
BEGIN
-- calcolo da DiesGroupDet
SELECT @DiesPrice = ISNULL(Price, 0), @DiesByClass = 1 -- imposto come calcolata matrice da gruppo
FROM dbo.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,
@DiesByClass = 1 -- imposto come calcolata matrice da gruppo
FROM dbo.DiesGroupDet
WHERE CodDiesGroup = @CodDiesGroup
END
-- controllo che non sia nulla/vuota la durata...
IF ( @DiesEOLife > 0 AND @DiesPrice > 0 )
BEGIN
-- (E) Calcolo price/life!
SET @DiesPriceLife = @DiesPrice / @DiesEOLife
END
ELSE
BEGIN
SET @DiesPriceLife = 0 -- metto a zero il DiesPriceLife
SET @validStep = 0 -- indico che valore non è valido ( ND excel )
END
-- se è una simulazione uso cmq i valori caricati e non calcolati
IF (@QuoteType = 'S')
BEGIN
-- recupero i costi matrice da simulazione
DECLARE @DiesPrice_sim DECIMAL(9,3) = 0
DECLARE @DiesCommLife DECIMAL(9,2) = 0 -- questa mi serve dopo?
DECLARE @DiesTecLife DECIMAL(9,2) = 0
;WITH myCTE2 as
( -- Pivot table
SELECT DiesPrice_sim, DiesCommLife, DiesTecLife
FROM
( SELECT NomePar, ValPar
FROM dbo.QuoteSimPar
WHERE QuoteType = @QuoteType AND CodQuote = @CodQuote AND QuoteRev = @QuoteRev
) AS SourceTable
PIVOT ( MIN(ValPar) FOR NomePar IN ( DiesPrice_sim, DiesCommLife, DiesTecLife )
) AS PivotTable
)
SELECT @DiesPrice_sim = DiesPrice_sim,
@DiesCommLife = DiesCommLife,
@DiesTecLife = DiesTecLife
FROM myCTE2
IF ( ISNULL(@DiesPrice_sim,0) = 0 OR ISNULL(@DiesTecLife,0) = 0 )
BEGIN
SET @DiesPriceLife = 0
SET @validStep = 0
END
ELSE
BEGIN
SET @DiesPriceLife = @DiesPrice_sim / @DiesTecLife
SET @validStep = 1 -- potrebbe essere stato messo = 0 da Quote non di simulazione
END
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, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota, valid)
SELECT @QuoteType, @CodQuote, @QuoteRev, '01', @CodPlant, 'MATRICE', @RawMat, @BatchQty, ISNULL(@DiesPriceLife * @BatchQty, 0), 1, @validStep
-- *** controllo errori
SELECT @errorScore = @errorScore + CASE WHEN @validStep = 0 THEN 1 ELSE 0 END,
@valid = CASE WHEN @validStep = 0 THEN 0 ELSE @valid END
-- inserisco parametri calcolati x matrice
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
VALUES ( @QuoteType, @CodQuote, @QuoteRev, '01', 'DiesPrice', CONVERT(NVARCHAR(100), @DiesPrice) )
,( @QuoteType, @CodQuote, @QuoteRev, '01', 'DiesExpLife', CONVERT(NVARCHAR(100), @DiesExpLife) )
,( @QuoteType, @CodQuote, @QuoteRev, '01', 'DiesEOLife', CONVERT(NVARCHAR(100), @DiesEOLife) )
,( @QuoteType, @CodQuote, @QuoteRev, '01', 'DiesByClass', CONVERT(NVARCHAR(100), ISNULL(@DiesByClass,0)) )
/*************************************************************
* 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
SELECT @VCF01 = @OCF01 + VC4UG
FROM #QuoteWorkInt
WHERE ProcessNum = '01'
SELECT @VCF04 = @OCF01 + SUM(VC4UG)
FROM #QuoteWorkInt
SELECT @SellCostF01 = CASE WHEN @QuoteType = 'S' THEN ((@MAC_ST_sim * @MAC_SC_sim * @MAC_IN_sim) - 1) ELSE ((@MAC_ST * @MAC_SC * @MAC_IN) - 1) END
* (q.RawMatCost + q.RawMatExtraCost - 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) *
CASE WHEN @QuoteType = 'S' THEN (@MAC_ST_sim * @MAC_SC_sim * @MAC_IN_sim * 100) ELSE (@MAC_ST * @MAC_SC * @MAC_IN * 100) END
)
-- (B) = ((MAC * 100) - 100) * Resa di fonderia * (costo pieno acquisto - costo di trasformazione))
- CASE WHEN @QuoteType = 'S' THEN ((@MAC_ST_sim * @MAC_SC_sim * @MAC_IN_sim * 100) - 100) ELSE ((@MAC_ST * @MAC_SC * @MAC_IN * 100) - 100) END
* 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 = CASE WHEN @QuoteType = 'S' THEN ((@MAC_IM_sim) - 1) ELSE ((@MAC_IM) - 1) END
* (q.RawMatCost + q.RawMatExtraCost - 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 01 e fase 04)
, @RefCostF04 = -- ( (A - B) / 100 - C )
(
-- (A) = (costo pieno di acquisto + costi variabili fase 1 e 4) * MAC * 100 (IN CENTESIMI)
((q.RawMatCost + q.RawMatExtraCost + @VCF01 + @VCF04) *
CASE WHEN @QuoteType = 'S' THEN (@MAC_IM_sim * 100) ELSE (@MAC_IM * 100) END
)
-- (B) = ((MAC * 100) - 100) * Resa di fonderia * (costo pieno acquisto - costo di trasformazione))
- CASE WHEN @QuoteType = 'S' THEN ((@MAC_IM_sim * 100) - 100) ELSE ((@MAC_IM * 100) - 100) END
* 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 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
-- inserisco costi per VENDITA
INSERT INTO #QuoteOC(QuoteType, CodQuote, QuoteRev, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota)
VALUES ( @QuoteType, @CodQuote, @QuoteRev, '01', @CodPlant, 'VENDITA', @RawMat, @BatchQty, ISNULL(@SellCostF01 * @BatchQty, 0), @SSR )
-- inserisco costi per ROTTAME
,( @QuoteType, @CodQuote, @QuoteRev, '01', @CodPlant, 'ROTTAME', @RawMat, @BatchQty, ISNULL(@RefCostF01 * @BatchQty, 0), 1 - @SSR )
-- inserisco costi per VENDITA
,( @QuoteType, @CodQuote, @QuoteRev, '04', @CodPlant, 'VENDITA', @RawMat, @BatchQty, ISNULL(@SellCostF04 * @BatchQty, 0), @SSR )
-- inserisco costi per ROTTAME
,( @QuoteType, @CodQuote, @QuoteRev, '04', @CodPlant, 'ROTTAME', @RawMat, @BatchQty, ISNULL(@RefCostF04 * @BatchQty, 0), 1 - @SSR )
-- calcolo Package Cost e RMWeight da tab PackageDet
-- EXEC stp_packageCost @CodItem, @CodClient, @CodPackag, @PDesc=@PkgDesc OUTPUT, @RMWeight=@PRMWeight OUTPUT, @Weight=@PWeight OUTPUT, @Tare=@PTare OUTPUT, @PackCost=@PCost OUTPUT, @TareStRat=@PTareStRatio OUTPUT
-- stored sostituita perchè la maggior parte dei calcoli li ho già fatti
SELECT @PCost = CASE @PWeight WHEN 0 THEN 0 ELSE RMCost / @PWeight END
, @PRMWeight = RMWeight
, @PkgDesc = PackagDesc
, @PTareStRatio = CASE @PWeight WHEN 0 THEN 0 ELSE ( RMWeight - @PTare) / @PWeight END
FROM dbo.PackagDet
WHERE CodPackag = @CodPackag
-- inserisco costi per IMBALLO
INSERT INTO #QuoteOC(QuoteType, CodQuote, QuoteRev, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota, valid)
SELECT @QuoteType, @CodQuote, @QuoteRev, '04', @CodPlant, 'IMBALLO', @RawMat, @BatchQty, ISNULL(@PCost * @BatchQty, 0), 1, CASE WHEN ISNULL(@PCost * @BatchQty, 0) = 0 THEN 0 ELSE 1 END
-- *** controllo errori - costo imballo non può essere = 0
SELECT @errorScore = @errorScore + CASE WHEN ISNULL(@PCost * @BatchQty, 0) = 0 THEN 1 ELSE 0 END,
@valid = CASE WHEN ISNULL(@PCost * @BatchQty, 0) = 0 THEN 0 ELSE @valid END
-- inserisco parametri calcolati x imballaggio
INSERT INTO #QuoteParam(QuoteType, CodQuote, QuoteRev, ProcessNum, Descr, Parameter)
VALUES ( @QuoteType, @CodQuote, @QuoteRev, '04', 'PkgDesc', @PkgDesc )
,( @QuoteType, @CodQuote, @QuoteRev, '04', 'PRMWeight', CONVERT(NVARCHAR(100), @PRMWeight ) )
,( @QuoteType, @CodQuote, @QuoteRev, '04', 'PWeight', CONVERT(NVARCHAR(100), @PWeight ) )
,( @QuoteType, @CodQuote, @QuoteRev, '04', 'PTare', CONVERT(NVARCHAR(100), @PTare) )
,( @QuoteType, @CodQuote, @QuoteRev, '04', 'TareStRatio', CONVERT(NVARCHAR(100), @PTareStRatio ) )
---------------------------------------------
-- inserisco costi per TRASPORTI
---------------------------------------------
-- recupero tipo di trasporto ( se ha costi o meno )
SELECT @HasTC = i.HasTC
FROM dbo.QuoteList q
INNER JOIN dbo.Incoterms i ON q.CodInco = i.CodInco
WHERE q.QuoteType = @QuoteType
AND q.CodQuote = @CodQuote
AND q.QuoteRev = @QuoteRev
-- Se non trovo @HasTC setto i valori in modo che diano errore
IF @HasTC IS NULL
BEGIN
SET @HasTC = 1
SET @TranspCost = 0
END
ELSE
BEGIN
-- recupero zona da cliente...
SELECT @TranspZone = TranspZone
FROM dbo.ClientDet
WHERE CodClient = @CodClient
-- recupero costi trasporto
SELECT @TranspCost = TranspCost -- se non lo trova rimane a 0
FROM dbo.TranspCostDet
WHERE TranspZone = @TranspZone
END
INSERT INTO #QuoteOC(QuoteType, CodQuote, QuoteRev, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota, valid)
SELECT @QuoteType, @CodQuote, @QuoteRev, '04', @CodPlant, 'TRASPORTI', @RawMat, @BatchQty, ISNULL(@HasTC * @TranspCost * @BatchQty, 0), 1
, CASE WHEN @HasTC = 1 AND @TranspCost = 0 THEN 0 ELSE 1 END
-- *** controllo errori - se previsti costi imballo allora devo avere il costo <> 0
SELECT @errorScore = @errorScore + CASE WHEN @HasTC = 1 AND @TranspCost = 0 THEN 1 ELSE 0 END,
@valid = CASE WHEN @HasTC = 1 AND @TranspCost = 0 THEN 0 ELSE @valid END
---------------------------------------------
-- inserisco costi per PROVVIGIONI
---------------------------------------------
IF (@QuoteType = 'R') OR (@QuoteType = 'S') -- SOLO se R o S prima leggo dai dati caricati negli ordini
BEGIN
SELECT @Provvig = CONVERT(DECIMAL(9,6),ValPar) FROM dbo.QuoteSimPar
WHERE NomePar = 'ProvvCost'
AND QuoteType=@QuoteType AND CodQuote=@CodQuote AND QuoteRev=@QuoteRev
END
-- leggo da tabella agenti
IF ISNULL(@Provvig, 0) = 0
BEGIN
SELECT @Provvig = ISNULL(a.QuotaProvvAg, 0)
FROM dbo.AgentsDet a
INNER JOIN dbo.ClientDet c ON a.CodAg = c.CodAg
INNER JOIN dbo.QuoteList q ON q.CodClient = c.CodClient
WHERE q.QuoteType = @QuoteType
AND q.CodQuote = @CodQuote
AND q.QuoteRev = @QuoteRev
END
-- fix eventuali valori null
SET @Provvig = ISNULL(@Provvig, 0)
-- inserisco costi per PROVVIGIONI
INSERT INTO #QuoteOC(QuoteType, CodQuote, QuoteRev, ProcessNum, CodPlant, Class01, Class02, TotQty, TotCost, Quota)
SELECT @QuoteType, @CodQuote, @QuoteRev, '04', @CodPlant, 'PROVVIGIONI', @RawMat, @BatchQty, ISNULL(@Provvig * @BatchQty, 0), 1
/* ==================================================
CARICO TABELLE EFFETTIVE
==================================================*/
-- carico quote parametri!
INSERT dbo.QuoteParam
SELECT * FROM #QuoteParam
-- carico lavorazioni INTERNE!
INSERT dbo.QuoteWorkInt -- ci sono valori calcolati quindi devo selezionare i campi
SELECT QuoteType, CodQuote, QuoteRev, NumWI, ProcessNum, CodPlant, Class01, Class02, Class03, Class04, Class05
, NetProd, WSR, MSR, DSR, FC4UG, VC4UG, OH4UG, SC4UG, valid
FROM #QuoteWorkInt
---- *** verifica errori ***
SELECT DISTINCT @errorScore = @errorScore + CASE WHEN TotQty = 0 THEN 1 ELSE 0 END,
@valid = CASE WHEN TotQty = 0 THEN 0 ELSE @valid END
FROM #QuoteOC
---- carico OTHERCOSTS
INSERT dbo.QuoteOC -- ci sono valori calcolati quindi devo selezionare i campi
SELECT QuoteType, CodQuote, QuoteRev, NumEx, ProcessNum, CodPlant, Class01, Class02
, Class03, Class04, Class05
, CASE TotQty WHEN 0 THEN 1 ELSE TotQty END -- evito errore in campo calcolato se TotQty = 0
, TotCost, Quota, valid
FROM #QuoteOC
-- update valori di errore generale
UPDATE dbo.QuoteList
SET valid = @valid
, errorScore = @errorScore
WHERE QuoteType = @QuoteType
AND CodQuote = @CodQuote
AND QuoteRev = @QuoteRev
COMMIT TRAN;
--SELECT COUNT(*) FROM #QuoteParam
--SELECT COUNT(*) FROM #QuoteWorkInt
--SELECT COUNT(*) FROM #QuoteOC
-- elimino temp table... NON serve nella stored
--IF OBJECT_ID('tempdb..#QuoteParam') IS NOT NULL
-- DROP TABLE #QuoteParam
--IF OBJECT_ID('tempdb..#QuoteWorkInt') IS NOT NULL
-- DROP TABLE #QuoteWorkInt
--IF OBJECT_ID('tempdb..#QuoteOC') IS NOT NULL
-- DROP TABLE #QuoteOC
END
Reference in New Issue View Git Blame Copy Permalink