== Descripción ==
Actualiza los precios unitario y subtotal en la [[TABLA:vidquoten3]] de un Producto determinado para todos sus componentes.

== Composición ==
*[[TABLA:composicion]]
*[[TABLA:vidquoten3]]
*[[TABLA:precios]]
*[[TABLA:productos]]
== Código ==
<small><pre>
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`sas_admin`@`%` PROCEDURE `ActualizaItemPresupuesto`(
    IN pVidQuoteID INT(5),
    IN pVidQuoteItem INT(5),
    IN pVidQuoteProdCantidad INT(5),
    IN pVidQuoteAncho INT(5),
    IN pVidQuoteAlto INT(5),
    IN pVidQuoteLista INT(1),
    IN pCoeficienteAC DECIMAL(10,5))
BEGIN
-- ----------------------------------------------------
-- Update from tables with ProductoID=ElementoID
-- ----------------------------------------------------
-- SET
-- @pVidQuoteID = 50,
-- @pVidQuoteItem = 3,
-- @pVidQuoteProdCantidad = 1,
-- @pVidQuoteAncho = 1000,
-- @pVidQuoteAlto = 1000,
-- @pVidQuoteLista = 1,
-- @pCoeficienteAC = 1.0;
-- CALL DebugCheckPoint(
--     CONCAT(
--     "PROC:ActualizaItemPresupuesto","/VAR/",
--     CAST(pVidQuoteID AS CHAR),"-",
--     CAST(pVidQuoteLista AS CHAR),"-",
--     CAST(pVidQuoteItem AS CHAR),"-",
--     CAST(pVidQuoteProdCantidad AS CHAR),"-",
--     CAST(pVidQuoteAncho AS CHAR),"-",
--     CAST(pVidQuoteAlto AS CHAR),"-",
--     CAST(pCoeficienteAC AS CHAR)
--     ));
UPDATE
    composicion AS T2,
    vidquoten3 AS T1,
    precios AS T3,
    productos AS T4
SET 
    T1.VidQuotePrecio = CalculoPrecio(
                        pVidQuoteAncho, 
                        pVidQuoteAlto,
                        (CASE (pVidQuoteLista)
                          WHEN 0 THEN T3.PrecioProd00
                          WHEN 1 THEN T3.PrecioProd01
                          WHEN 2 THEN T3.PrecioProd02
                          WHEN 3 THEN T3.PrecioProd03
                        END),
                        T4.Magnitud,
                        T2.OffsetVanoAlto,
                        T2.OffsetVanoAncho,
                        T2.CoefVanoAlto,
                        T2.CoefVanoAncho,
                        T2.ValorElemento) * pCoeficienteAC,
    T1.VidQuotePrecioP= CalculoPrecio(
                        pVidQuoteAncho, 
                        pVidQuoteAlto,
                        (CASE (pVidQuoteLista)
                          WHEN 0 THEN T3.PrecioProd00
                          WHEN 1 THEN T3.PrecioProd01
                          WHEN 2 THEN T3.PrecioProd02
                          WHEN 3 THEN T3.PrecioProd03
                        END),
                        T4.Magnitud,
                        T2.OffsetVanoAlto,
                        T2.OffsetVanoAncho,
                        T2.CoefVanoAlto,
                        T2.CoefVanoAncho,
                        T2.ValorElemento) * pCoeficienteAC * pVidQuoteProdCantidad 
WHERE
    T1.VidQuoteID = pVidQuoteID AND
    T1.VidQuoteItem = pVidQuoteItem AND
    T2.ProductoID = T1.VidQuoteProdID AND 
    T2.ElementoID = T1.VidQuoteElementoID AND 
    T2.ComposicID = T1.VidQuoteComposicID AND 
    T4.ProductoID = T3.ProductoID AND 
    T3.ProductoID = T2.ElementoID AND 
    T1.VidQuoteProdID = T1.VidQuoteElementoID;
-- ----------------------------------------------------
-- Update from tables with ProductoID<>ElementoID
-- ----------------------------------------------------
UPDATE
    vidquoten3 AS W1,
    (SELECT 
        V1.VidQuoteID,
        V1.VidQuoteItem,
        V1.VidQuoteProdID,
        V1.VidQuoteElementoID,
        V1.VidQuoteComposicID,
        SUM(CalculoPrecio(
            pVidQuoteAncho, 
            pVidQuoteAlto,
            (CASE (pVidQuoteLista)
              WHEN 0 THEN V4.PrecioProd00
              WHEN 1 THEN V4.PrecioProd01
              WHEN 2 THEN V4.PrecioProd02
              WHEN 3 THEN V4.PrecioProd03
            END),
            V5.Magnitud,
            V2.OffsetVanoAlto + V3.OffsetVanoAlto,
            V2.OffsetVanoAncho + V3.OffsetVanoAncho,
            V2.CoefVanoAlto * V3.CoefVanoAlto,
            V2.CoefVanoAncho * V3.CoefVanoAncho,
            V2.ValorElemento * V3.ValorElemento)) * pCoeficienteAC AS VidQuotePrecio,
        SUM(CalculoPrecio(
            pVidQuoteAncho, 
            pVidQuoteAlto,
            (CASE (pVidQuoteLista)
              WHEN 0 THEN V4.PrecioProd00
              WHEN 1 THEN V4.PrecioProd01
              WHEN 2 THEN V4.PrecioProd02
              WHEN 3 THEN V4.PrecioProd03
            END),
            V5.Magnitud,
            V2.OffsetVanoAlto + V3.OffsetVanoAlto,
            V2.OffsetVanoAncho + V3.OffsetVanoAncho,
            V2.CoefVanoAlto * V3.CoefVanoAlto,
            V2.CoefVanoAncho * V3.CoefVanoAncho,
            V2.ValorElemento * V3.ValorElemento)) * pCoeficienteAC * pVidQuoteProdCantidad AS VidQuotePrecioP
    FROM
        fzggestion.composicion AS V3,
        fzggestion.composicion AS V2,
        fzggestion.vidquoten3 AS V1,
        fzggestion.precios AS V4,
        fzggestion.productos AS V5
    WHERE
        V1.VidQuoteID = pVidQuoteID AND
        V1.VidQuoteItem = pVidQuoteItem AND
        V3.ProductoID = V2.ElementoID AND 
        V2.ProductoID = V1.VidQuoteProdID AND 
        V2.ElementoID = V1.VidQuoteElementoID AND 
        V2.ComposicID = V1.VidQuoteComposicID AND 
        V4.ProductoID = V3.ElementoID AND 
        V5.ProductoID = V4.ProductoID AND 
        V1.VidQuoteProdID <> V1.VidQuoteElementoID AND 
        V3.ElementoPM = 0
    GROUP BY
        V1.VidQuoteID,
        V1.VidQuoteItem,
        V1.VidQuoteElementoID,
        V1.VidQuoteComposicID) AS W2
SET
    W1.VidQuotePrecio = W2.VidQuotePrecio,
    W1.VidQuotePrecioP = W2.VidQuotePrecioP
WHERE
    -- ----------------------------------------------------------
    -- Join between result of calculating prices and vidquoten3
    -- ----------------------------------------------------------
    W2.VidQuoteID = W1.VidQuoteID AND 
    W2.VidQuoteItem = W1.VidQuoteItem AND 
    W2.VidQuoteProdID = W1.VidQuoteProdID AND 
    W2.VidQuoteElementoID = W1.VidQuoteElementoID AND 
    W2.VidQuoteComposicID = W1.VidQuoteComposicID AND
    -- ----------------------------------------------------------
    -- Select what Qoute will be affected
    -- ----------------------------------------------------------
    W1.VidQuoteID = pVidQuoteID AND
    -- ----------------------------------------------------------
    -- Select records not PPD
    -- ----------------------------------------------------------
    W1.VidQuoteProdID <> W1.VidQuoteElementoID;
END
</pre></small>
== Bitácora ==
*20120921
**Alta ante rediseño de [[FORM:Presupuesto]]
== Uso ==
*[[FORM:Presupuesto]]
*[[TRIG:Actualiza_Item_Presupuesto]]