== Descripción ==
El procedimiento está en función de la edición de los elementos de un producto compuesto durante la confección de un presupuesto.
Agrega a la [[TABLA:vidquoten3tmp]] las registraciones correspondientes al producto a editar. Las características del proceso son las siguientes:
*Selecciona registros de la [[TABLA:composicion]] que corresponde al producto seleccionado y agrega aquellos registros que no existan en la selececión original (Alta de producto)
**Selecciona los registros de la [[TABLA:composicion]] cuyo elemento es igual al producto (producto propiamente dicho). Este punto se explicita para que no entre en loop
**Selecciona los registros de la [[TABLA:composicion]] cuyos elementos son diferentes al producto (adicionales Básicos, Simple y Completo).
***Dado que permite un grado de profundidad adicional, toma tanto los registros componentes sino también todos aquellos que están referenciados como elemento componente.
***Establece una relación con las [[TABLA:productos]] y [[TABLA:precios]].
***Establece una relación anidada con la misma [[TABLA:composicion]] para tomar todos los elementos como si fuesen productos y así tener en cuenta la definición en aquellos que tengan más de un componente y poder calcular el precio de acuerdo a los parámetros definidos
**Agrega los datos resultantes a la [[TABLA:vidquoten3tmp]] previo borrado de los datos anteriores dentro de la tabla temporal para mantener la integridad de la operación para proceder a la edición. No se deben agregar aquellos registros ya existentes en la [[TABLA:vidquoten3]]

== Composición ==
* [[TABLA:composicion]]
* [[TABLA:productos]]
* [[TABLA:precios]]
* [[TABLA:vidquoten3]]
* [[TABLA:vidquoten3tmp]]

== Código ==
<small><pre>
DROP PROCEDURE `AltaItemPresupuestoTmp`;
delimiter $$

CREATE DEFINER=`sas_admin`@`%` PROCEDURE `AltaItemPresupuestoTmp`(
	IN pVidQuoteID INT(5), 
	IN pVidQuoteLPre INT(1), 
	IN pVidQuoteItem INT(5), 
	IN pVidQuoteProdID CHAR(8), 
	IN pVidQuoteProdCantidad INT(5), 
	IN pVidQuoteAncho INT(5), 
	IN pVidQuoteAlto INT(5), 
	IN pCoeficienteMU DECIMAL(5,2),
	IN pQuickSelect INT(1))
BEGIN
	-- --------------------------------------------------------------------------------
	-- Inserta Registro de Auditoria
	-- --------------------------------------------------------------------------------
--     INSERT INTO debugstmt (Fecha,Hora,Texto) 
-- 		VALUES (
-- 			CURDATE(),
-- 			CURTIME(),
-- 			CONCAT(
-- 				CAST(pVidQuoteID AS CHAR),"-",
-- 				CAST(pVidQuoteLPre AS CHAR),"-",
-- 				CAST(pVidQuoteItem AS CHAR),"-",
-- 				pVidQuoteProdID,"-",
-- 				CAST(pVidQuoteProdCantidad AS CHAR),"-",
-- 				CAST(pVidQuoteAncho AS CHAR),"-",
-- 				CAST(pVidQuoteAlto AS CHAR)
-- 				)
-- 			);
	-- --------------------------------------------------------------------------------
	-- Elimina residuos de ediciones anteriores
	-- --------------------------------------------------------------------------------
--     CALL DebugCheckPoint(
--         CONCAT(
--         "Prev - ",
--         "pVidQuoteID:",
--         CAST(pVidQuoteID AS CHAR),"-",
--         "pVidQuoteItem:",
--         CAST(pVidQuoteItem AS CHAR),"-",
--         "Delete Count:",
--         (SELECT CAST(Count(*) AS CHAR) 
--             FROM vidquoten3tmp 
--             WHERE
--                 VidQuoteID=pVidQuoteID AND 
--                 VidQuoteItem=pVidQuoteItem)
--         ));
    DELETE FROM vidquoten3tmp WHERE VidQuoteID=pVidQuoteID AND VidQuoteItem=pVidQuoteItem;
--     CALL DebugCheckPoint(
--         CONCAT(
--         "Past - ",
--         "pVidQuoteID:",
--         CAST(pVidQuoteID AS CHAR),"-",
--         "pVidQuoteItem:",
--         CAST(pVidQuoteItem AS CHAR),"-",
--         "Delete Count:",
--         (SELECT CAST(Count(*) AS CHAR) 
--             FROM vidquoten3tmp 
--             WHERE
--                 VidQuoteID=pVidQuoteID AND 
--                 VidQuoteItem=pVidQuoteItem)
--         ));
	-- --------------------------------------------------------------------------------
	-- Inserta Registraciones anteriores del mismo Producto/Item
	-- --------------------------------------------------------------------------------
	INSERT INTO vidquoten3tmp (
		VidQuoteID,
		VidQuoteItem,
		VidQuoteProdID,
		VidQuoteProdCantidad,
		VidQuoteElementoID,
		VidQuoteComposicID,
		VidQuoteElementoPM,
		VidQuotePrecio,
		VidQuotePrecioP,
		VidQuoteIncluyeDet,
		VidQuoteQuickSelect)
	SELECT
		S1.VidQuoteID,
		S1.VidQuoteItem,
		S1.VidQuoteProdID,
		S1.VidQuoteProdCantidad,
		S1.VidQuoteElementoID,
		S1.VidQuoteComposicID,
		S1.VidQuoteElementoPM,
		S1.VidQuotePrecio,
		S1.VidQuotePrecioP,
		S1.VidQuoteIncluyeDet,
		1 AS VidQuoteQuickSelect
	FROM
		vidquoten3 AS S1
	WHERE
		S1.VidQuoteID=pVidQuoteID AND 
		S1.VidQuoteItem=pVidQuoteItem;
    CALL DebugCheckPoint("Past Inserta Registros VQ3 anteriores");
	-- --------------------------------------------------------------------------------
	-- Inserta Registraciones pausibles de incorporar de acuerdo al tipo de 
	-- --------------------------------------------------------------------------------
	INSERT INTO vidquoten3tmp (
		VidQuoteID,
		VidQuoteItem,
		VidQuoteProdID,
		VidQuoteProdCantidad,
		VidQuoteElementoID,
		VidQuoteComposicID,
		VidQuoteElementoPM,
		VidQuotePrecio,
		VidQuotePrecioP)
	SELECT
		pVidQuoteID as VidQuoteID,
		pVidQuoteItem as VidQuoteItem,
		R1.ProductoID AS VidQuoteProdID,
		pVidQuoteProdCantidad AS VidQuoteProdCantidad,
		R1.ProdCmpID AS VidQuoteElementoID,
		R1.ComposicID AS VidQuoteComposicID,
		R1.ElementoPM AS VidQuoteElementoPM,
		SUM(
          CalculoPrecio(
			pVidQuoteAncho, 
			pVidQuoteAlto,
			`R1`.`PrecioProd`,
			`R1`.`Magnitud`,
			`R1`.`OffsetVanoAlto`,
			`R1`.`OffsetVanoAncho`,
			`R1`.`CoefVanoAlto`,
			`R1`.`CoefVanoAncho`,
			`R1`.`ValorElemento`) * pCoeficienteMU) AS VidQuotePrecio,
		SUM(
          CalculoPrecio(
			pVidQuoteAncho, 
			pVidQuoteAlto,
			`R1`.`PrecioProd`,
			`R1`.`Magnitud`,
			`R1`.`OffsetVanoAlto`,
			`R1`.`OffsetVanoAncho`,
			`R1`.`CoefVanoAlto`,
			`R1`.`CoefVanoAncho`,
			`R1`.`ValorElemento`) * pVidQuoteProdCantidad * pCoeficienteMU) AS VidQuotePrecioP

	FROM
		-- --------------------------------------------------------------------------------
		-- Selecciona elementos de composicion iguales al producto 
		-- --------------------------------------------------------------------------------
		((
		SELECT 
			U1.ProductoID,
			U1.ElementoID AS ProdCmpID,
			U1.ComposicID,
			U1.ElementoID,
			U1.ElementoPM,
			U2.Magnitud,
			U1.ValorElemento,
			U1.OffsetVanoAncho,
			U1.OffsetVanoAlto,
			U1.CoefVanoAncho,
			U1.CoefVanoAlto,
			(CASE (pVidQuoteLPre)
				WHEN 0 THEN U3.PrecioProd00
				WHEN 1 THEN U3.PrecioProd01
				WHEN 2 THEN U3.PrecioProd02
				WHEN 3 THEN U3.PrecioProd03
			END) AS PrecioProd
		FROM
			fzggestion.productos AS U2,
			fzggestion.composicion AS U1,
			fzggestion.precios AS U3
		WHERE
			U2.ProductoID = U1.ElementoID AND 
			U3.ProductoID = U2.ProductoID AND 
			U1.ProductoID = pVidQuoteProdID AND 
			U1.ElementoID = pVidQuoteProdID AND 
			U1.ElementoPM <= pQuickSelect)
		-- --------------------------------------------------------------------------------
		UNION
		-- --------------------------------------------------------------------------------
		-- Selecciona elementos de composicion diferentes al producto
		-- --------------------------------------------------------------------------------
		(
		SELECT 
			T1.ProductoID,
			T1.ElementoID AS ProdCmpID,
			T1.ComposicID,
			T2.ElementoID,
			T1.ElementoPM,
-- 			T2.ElementoPM,
			T3.Magnitud,
			T1.ValorElemento * T2.ValorElemento AS ValorElemento,        
			T1.OffsetVanoAncho + T2.OffsetVanoAncho AS OffsetVanoAncho,
			T1.OffsetVanoAlto + T2.OffsetVanoAlto AS OffsetVanoAlto,
			T1.CoefVanoAncho * T2.CoefVanoAncho AS CoefVanoAncho,
			T1.CoefVanoAlto * T2.CoefVanoAlto AS CoefVanoAlto,
			(CASE (pVidQuoteLPre)
				WHEN 0 THEN `T4`.`PrecioProd00`
				WHEN 1 THEN `T4`.`PrecioProd01`
				WHEN 2 THEN `T4`.`PrecioProd02`
				WHEN 3 THEN `T4`.`PrecioProd03`
			END) AS PrecioProd
		FROM
			fzggestion.composicion AS T2,
			fzggestion.composicion AS T1,
			fzggestion.productos AS T3,
			fzggestion.precios AS T4
		WHERE
			T2.ProductoID = T1.ElementoID AND 
			T3.ProductoID = T2.ElementoID AND 
			T4.ProductoID = T3.ProductoID AND 
			T1.ProductoID <> T1.ElementoID AND 
			T2.ElementoPM <= pQuickSelect AND 
			T1.ProductoID = pVidQuoteProdID AND 
			T1.ElementoPM <= pQuickSelect
		ORDER BY
			T1.ProductoID ASC ,
			T1.ElementoID ASC ,
			T1.ComposicID ASC ,
			T2.ElementoID ASC)
		-- --------------------------------------------------------------------------------
		-- R1 es el sinónimo de la union de las tablas 
		-- --------------------------------------------------------------------------------
		) AS R1
      WHERE
			CONCAT(R1.ProductoID,R1.ProdCmpID,LPAD(CAST(R1.ComposicID AS CHAR),3,'000'))
      	NOT IN (
				SELECT
					CONCAT(VidQuoteProdID,VidQuoteElementoID,LPAD(CAST(VidQuoteComposicID AS CHAR),3,'000'))
				FROM
					vidquoten3tmp AS Q1 
				WHERE 
					Q1.VidQuoteID=pVidQuoteID AND 
					Q1.VidQuoteItem=pVidQuoteItem)
		GROUP BY
			VidQuoteElementoID,
			VidQuoteComposicID;
    -- --------------------------------------------------------------------------------
    -- Joint Para completar info de ElementoPM
    -- --------------------------------------------------------------------------------
    UPDATE
        vidquoten3tmp AS T1,
        composicion AS T2
    SET
        T1.VidQuoteElementoPM = T2.ElementoPM
    WHERE
        T1.VidQuoteID=pVidQuoteID AND
        ISNULL(T1.VidQuoteElementoPM) AND
        T1.VidQuoteProdID=T2.ProductoID AND
        T1.VidQuoteElementoID=T2.ElementoID AND
        T1.VidQuoteComposicID=T2.ComposicID;
END$$
</pre><small>
== Bitácora ==
*20120904
**Código Anterior
<small><pre>
DELIMITER $$

CREATE DEFINER=`sas_admin`@`%` PROCEDURE `AltaItemPresupuestoTmp`(IN pVidQuoteID INT(5), IN pVidQuoteLPre INT(1), IN pVidQuoteItem INT(5), IN pVidQuoteProdID CHAR(8), IN pVidQuoteProdCantidad INT(5), IN pVidQuoteAncho INT(5), IN pVidQuoteAlto INT(5))
BEGIN
    INSERT INTO debugstmt (Fecha,Hora,Texto) VALUES (CURDATE(),CURTIME(),CONCAT(CAST(pVidQuoteID AS CHAR),"-",CAST(pVidQuoteLPre AS CHAR),"-",CAST(pVidQuoteItem AS CHAR),"-",pVidQuoteProdID,"-",CAST(pVidQuoteProdCantidad AS CHAR),"-",CAST(pVidQuoteAncho AS CHAR),"-",CAST(pVidQuoteAlto AS CHAR)));
    DELETE FROM vidquoten3tmp WHERE VidQuoteID=pVidQuoteID AND VidQuoteItem=pVidQuoteItem;
    INSERT INTO vidquoten3tmp (
		VidQuoteID,
		VidQuoteItem,
		VidQuoteProdID,
		VidQuoteProdCantidad,
		VidQuoteElementoID,
		VidQuotePrecio,
		VidQuotePrecioP)
	SELECT
		pVidQuoteID,
		pVidQuoteItem,
		pVidQuoteProdID,
		pVidQuoteProdCantidad,
		`T3`.`ProdCmpID`,
		SUM(CalculoPrecio(
			pVidQuoteAncho, 
			pVidQuoteAlto,
			`T3`.`PrecioProd`,
			`T3`.`Magnitud`,
			`T3`.`OffsetVanoAlto`,
			`T3`.`OffsetVanoAncho`,
			`T3`.`CoefVanoAlto`,
			`T3`.`CoefVanoAncho`,
			`T3`.`ValorElemento`)) AS pVidQuotePrecio,
		SUM(CalculoPrecio(
			pVidQuoteAncho, 
			pVidQuoteAlto,
			`T3`.`PrecioProd`,
			`T3`.`Magnitud`,
			`T3`.`OffsetVanoAlto`,
			`T3`.`OffsetVanoAncho`,
			`T3`.`CoefVanoAlto`,
			`T3`.`CoefVanoAncho`,
			`T3`.`ValorElemento`) * pVidQuoteProdCantidad) AS pVidQuotePrecioP
	FROM
		(
		SELECT
		`T1`.`ProductoID` AS ProductoID,
		`T1`.`ProductoID` AS ProdCmpID,
		`T1`.`ElementoID`,
		`T1`.`ValorElemento`,
		`T1`.`Descripcion`,
		`T1`.`Magnitud`,
		`T1`.`OffsetVanoAncho`,
		`T1`.`OffsetVanoAlto`,
		`T1`.`CoefVanoAncho`,
		`T1`.`CoefVanoAlto`,
		(CASE (pVidQuoteLPre)
		 WHEN 0 THEN `T1`.`PrecioProd00`
		 WHEN 1 THEN `T1`.`PrecioProd01`
		 WHEN 2 THEN `T1`.`PrecioProd02`
		 WHEN 3 THEN `T1`.`PrecioProd03`
		END) AS PrecioProd
		FROM visprodadic AS T1
		WHERE ProductoID=pVidQuoteProdID AND ElementoID=pVidQuoteProdID AND ElementoPM<=1
		UNION
		SELECT 
		pVidQuoteProdID AS ProductoID,
		`T2`.`ProductoID` AS ProdCmpID,
		`T2`.`ElementoID`,
		`T2`.`ValorElemento`,
		`T2`.`Descripcion`,
		`T2`.`Magnitud`,
		`T2`.`OffsetVanoAncho`,
		`T2`.`OffsetVanoAlto`,
		`T2`.`CoefVanoAncho`,
		`T2`.`CoefVanoAlto`,
		(CASE (pVidQuoteLPre)
		 WHEN 0 THEN `T2`.`PrecioProd00`
		 WHEN 1 THEN `T2`.`PrecioProd01`
		 WHEN 2 THEN `T2`.`PrecioProd02`
		 WHEN 3 THEN `T2`.`PrecioProd03`
		END) AS PrecioProd
		FROM visprodadic AS T2
		WHERE ProductoID IN 
			 (SELECT ElementoID 
			  FROM visprodadic 
			  WHERE ProductoID=pVidQuoteProdID AND ElementoID<>pVidQuoteProdID AND ElementoPM<=1
		 )
		) AS T3
	GROUP BY ProdCmpID;
END
</pre></small>
*20121009
**Error de Change Management al subir el programa de TEST con los parámetros de prueba. Condición de la [[TABLA:vidquoten3tmp]] tipo "SELECT IN" para incorporar solo los registros que ya no existan en la [[TABLA:vidquoten3tmp]]. También se agrega la salvaguarda de grabación de ElementoPM en el caso de no integral y los errores de parámetros de prueba para [[PARM:pVidQuoteLPre]]
<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 `AltaItemPresupuestoTmp`(
	IN pVidQuoteID INT(5), 
	IN pVidQuoteLPre INT(1), 
	IN pVidQuoteItem INT(5), 
	IN pVidQuoteProdID CHAR(8), 
	IN pVidQuoteProdCantidad INT(5), 
	IN pVidQuoteAncho INT(5), 
	IN pVidQuoteAlto INT(5), 
	IN pCoeficienteMU DECIMAL(5,2),
	IN pQuickSelect INT(1))
BEGIN
	-- --------------------------------------------------------------------------------
	-- Inserta Registro de Auditoria
	-- --------------------------------------------------------------------------------
    INSERT INTO debugstmt (Fecha,Hora,Texto) 
		VALUES (
			CURDATE(),
			CURTIME(),
			CONCAT(
				CAST(pVidQuoteID AS CHAR),"-",
				CAST(pVidQuoteLPre AS CHAR),"-",
				CAST(pVidQuoteItem AS CHAR),"-",
				pVidQuoteProdID,"-",
				CAST(pVidQuoteProdCantidad AS CHAR),"-",
				CAST(pVidQuoteAncho AS CHAR),"-",
				CAST(pVidQuoteAlto AS CHAR)
				)
			);
	-- --------------------------------------------------------------------------------
	-- Elimina residuos de ediciones anteriores
	-- --------------------------------------------------------------------------------
    DELETE FROM vidquoten3tmp WHERE VidQuoteID=pVidQuoteID AND VidQuoteItem=pVidQuoteItem;

	-- --------------------------------------------------------------------------------
	-- Inserta Registraciones anteriores del mismo Producto/Item
	-- --------------------------------------------------------------------------------
	INSERT INTO vidquoten3tmp (
		VidQuoteID,
		VidQuoteItem,
		VidQuoteProdID,
		VidQuoteProdCantidad,
		VidQuoteElementoID,
		VidQuoteComposicID,
		VidQuotePrecio,
		VidQuotePrecioP,
		VidQuoteIncluyeDet,
		VidQuoteQuickSelect)
	SELECT
		S1.VidQuoteID,
		S1.VidQuoteItem,
		S1.VidQuoteProdID,
		S1.VidQuoteProdCantidad,
		S1.VidQuoteElementoID,
		S1.VidQuoteComposicID,
		S1.VidQuotePrecio,
		S1.VidQuotePrecioP,
		S1.VidQuoteIncluyeDet,
		1 AS VidQuoteQuickSelect
	FROM
		vidquoten3 AS S1
	WHERE
		S1.VidQuoteID=pVidQuoteID AND 
		S1.VidQuoteItem=pVidQuoteItem;
	-- --------------------------------------------------------------------------------
	-- Inserta Registraciones pausibles de incorporar de acuerdo al tipo de 
	-- --------------------------------------------------------------------------------
	INSERT INTO vidquoten3tmp (
		VidQuoteID,
		VidQuoteItem,
		VidQuoteProdID,
		VidQuoteProdCantidad,
		VidQuoteElementoID,
		VidQuoteComposicID,
		VidQuotePrecio,
		VidQuotePrecioP)
	SELECT
		pVidQuoteID as VidQuoteID,
		pVidQuoteItem as VidQuoteItem,
		R1.ProductoID AS VidQuoteProdID,
		pVidQuoteProdCantidad AS VidQuoteProdCantidad,
		R1.ProdCmpID AS VidQuoteElementoID,
		R1.ComposicID AS VidQuoteComposicID,
		SUM(
          CalculoPrecio(
			pVidQuoteAncho, 
			pVidQuoteAlto,
			`R1`.`PrecioProd`,
			`R1`.`Magnitud`,
			`R1`.`OffsetVanoAlto`,
			`R1`.`OffsetVanoAncho`,
			`R1`.`CoefVanoAlto`,
			`R1`.`CoefVanoAncho`,
			`R1`.`ValorElemento`) * pCoeficienteMU) AS VidQuotePrecio,
		SUM(
          CalculoPrecio(
			pVidQuoteAncho, 
			pVidQuoteAlto,
			`R1`.`PrecioProd`,
			`R1`.`Magnitud`,
			`R1`.`OffsetVanoAlto`,
			`R1`.`OffsetVanoAncho`,
			`R1`.`CoefVanoAlto`,
			`R1`.`CoefVanoAncho`,
			`R1`.`ValorElemento`) * pVidQuoteProdCantidad * pCoeficienteMU) AS VidQuotePrecioP

	FROM
		-- --------------------------------------------------------------------------------
		-- Selecciona elementos de composicion iguales al producto 
		-- --------------------------------------------------------------------------------
		((
		SELECT 
		U1.ProductoID,
		U1.ElementoID AS ProdCmpID,
		U1.ComposicID,
		U1.ElementoID,
		U1.ElementoPM,
		U2.Magnitud,
		U1.ValorElemento,
		U1.OffsetVanoAncho,
		U1.OffsetVanoAlto,
		U1.CoefVanoAncho,
		U1.CoefVanoAlto,
		(CASE (2)
		  WHEN 0 THEN U3.PrecioProd00
		  WHEN 1 THEN U3.PrecioProd01
		  WHEN 2 THEN U3.PrecioProd02
		  WHEN 3 THEN U3.PrecioProd03
		END) AS PrecioProd
		FROM
		fzggestion.productos AS U2,
		fzggestion.composicion AS U1,
		fzggestion.precios AS U3
		WHERE
		U2.ProductoID = U1.ElementoID AND 
		U3.ProductoID = U2.ProductoID AND 
		U1.ProductoID = pVidQuoteProdID AND 
		U1.ElementoID = pVidQuoteProdID AND 
		U1.ElementoPM <= pQuickSelect)
		-- --------------------------------------------------------------------------------
		UNION
		-- --------------------------------------------------------------------------------
		-- Selecciona elementos de composicion diferentes al producto
		-- --------------------------------------------------------------------------------
		(
		SELECT 
			T1.ProductoID,
			T1.ElementoID AS ProdCmpID,
			T1.ComposicID,
			T2.ElementoID,
			T2.ElementoPM,
			T3.Magnitud,
			T1.ValorElemento,        
			T1.OffsetVanoAncho,
			T1.OffsetVanoAlto,
			T1.CoefVanoAncho,
			T1.CoefVanoAlto,
			(CASE (2)
				WHEN 0 THEN `T4`.`PrecioProd00`
				WHEN 1 THEN `T4`.`PrecioProd01`
				WHEN 2 THEN `T4`.`PrecioProd02`
				WHEN 3 THEN `T4`.`PrecioProd03`
			END) AS PrecioProd
		FROM
			fzggestion.composicion AS T2,
			fzggestion.composicion AS T1,
			fzggestion.productos AS T3,
			fzggestion.precios AS T4
		WHERE
			T2.ProductoID = T1.ElementoID AND 
			T3.ProductoID = T2.ElementoID AND 
			T4.ProductoID = T3.ProductoID AND 
			T1.ProductoID <> T1.ElementoID AND 
			T2.ElementoPM <= pQuickSelect AND 
			T1.ProductoID = pVidQuoteProdID AND 
			T1.ElementoPM <= pQuickSelect
		ORDER BY
			T1.ProductoID ASC ,
			T1.ElementoID ASC ,
			T1.ComposicID ASC ,
			T2.ElementoID ASC)
		-- --------------------------------------------------------------------------------
		-- R1 es el sinónimo de la union de las tablas 
		-- --------------------------------------------------------------------------------
		) AS R1
      WHERE
			CONCAT(R1.ProductoID,R1.ProdCmpID,LPAD(CAST(R1.ComposicID AS CHAR),3,'000'))
      	NOT IN (
				SELECT
					CONCAT(VidQuoteProdID,VidQuoteElementoID,LPAD(CAST(VidQuoteComposicID AS CHAR),3,'000'))
				FROM
					vidquoten3tmp AS Q1 
				WHERE 
					Q1.VidQuoteID=pVidQuoteID AND 
					Q1.VidQuoteItem=pVidQuoteItem)
		GROUP BY
			VidQuoteElementoID,
			VidQuoteComposicID;
END
</pre></small>
*20121207
**Se detecta errores en el cálculo combinado por la falta de propagación de atributos de componentes con niveles adicionales de composición (DVH, etc). Anterior: 
<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 `AltaItemPresupuestoTmp`(
	IN pVidQuoteID INT(5), 
	IN pVidQuoteLPre INT(1), 
	IN pVidQuoteItem INT(5), 
	IN pVidQuoteProdID CHAR(8), 
	IN pVidQuoteProdCantidad INT(5), 
	IN pVidQuoteAncho INT(5), 
	IN pVidQuoteAlto INT(5), 
	IN pCoeficienteMU DECIMAL(5,2),
	IN pQuickSelect INT(1))
BEGIN
	-- --------------------------------------------------------------------------------
	-- Inserta Registro de Auditoria
	-- --------------------------------------------------------------------------------
--     INSERT INTO debugstmt (Fecha,Hora,Texto) 
-- 		VALUES (
-- 			CURDATE(),
-- 			CURTIME(),
-- 			CONCAT(
-- 				CAST(pVidQuoteID AS CHAR),"-",
-- 				CAST(pVidQuoteLPre AS CHAR),"-",
-- 				CAST(pVidQuoteItem AS CHAR),"-",
-- 				pVidQuoteProdID,"-",
-- 				CAST(pVidQuoteProdCantidad AS CHAR),"-",
-- 				CAST(pVidQuoteAncho AS CHAR),"-",
-- 				CAST(pVidQuoteAlto AS CHAR)
-- 				)
-- 			);
	-- --------------------------------------------------------------------------------
	-- Elimina residuos de ediciones anteriores
	-- --------------------------------------------------------------------------------
--     CALL DebugCheckPoint(
--         CONCAT(
--         "Prev - ",
--         "pVidQuoteID:",
--         CAST(pVidQuoteID AS CHAR),"-",
--         "pVidQuoteItem:",
--         CAST(pVidQuoteItem AS CHAR),"-",
--         "Delete Count:",
--         (SELECT CAST(Count(*) AS CHAR) 
--             FROM vidquoten3tmp 
--             WHERE
--                 VidQuoteID=pVidQuoteID AND 
--                 VidQuoteItem=pVidQuoteItem)
--         ));
    DELETE FROM vidquoten3tmp WHERE VidQuoteID=pVidQuoteID AND VidQuoteItem=pVidQuoteItem;
--     CALL DebugCheckPoint(
--         CONCAT(
--         "Past - ",
--         "pVidQuoteID:",
--         CAST(pVidQuoteID AS CHAR),"-",
--         "pVidQuoteItem:",
--         CAST(pVidQuoteItem AS CHAR),"-",
--         "Delete Count:",
--         (SELECT CAST(Count(*) AS CHAR) 
--             FROM vidquoten3tmp 
--             WHERE
--                 VidQuoteID=pVidQuoteID AND 
--                 VidQuoteItem=pVidQuoteItem)
--         ));
	-- --------------------------------------------------------------------------------
	-- Inserta Registraciones anteriores del mismo Producto/Item
	-- --------------------------------------------------------------------------------
	INSERT INTO vidquoten3tmp (
		VidQuoteID,
		VidQuoteItem,
		VidQuoteProdID,
		VidQuoteProdCantidad,
		VidQuoteElementoID,
		VidQuoteComposicID,
		VidQuoteElementoPM,
		VidQuotePrecio,
		VidQuotePrecioP,
		VidQuoteIncluyeDet,
		VidQuoteQuickSelect)
	SELECT
		S1.VidQuoteID,
		S1.VidQuoteItem,
		S1.VidQuoteProdID,
		S1.VidQuoteProdCantidad,
		S1.VidQuoteElementoID,
		S1.VidQuoteComposicID,
		S1.VidQuoteElementoPM,
		S1.VidQuotePrecio,
		S1.VidQuotePrecioP,
		S1.VidQuoteIncluyeDet,
		1 AS VidQuoteQuickSelect
	FROM
		vidquoten3 AS S1
	WHERE
		S1.VidQuoteID=pVidQuoteID AND 
		S1.VidQuoteItem=pVidQuoteItem;
    CALL DebugCheckPoint("Past Inserta Registros VQ3 anteriores");
	-- --------------------------------------------------------------------------------
	-- Inserta Registraciones pausibles de incorporar de acuerdo al tipo de 
	-- --------------------------------------------------------------------------------
	INSERT INTO vidquoten3tmp (
		VidQuoteID,
		VidQuoteItem,
		VidQuoteProdID,
		VidQuoteProdCantidad,
		VidQuoteElementoID,
		VidQuoteComposicID,
		VidQuoteElementoPM,
		VidQuotePrecio,
		VidQuotePrecioP)
	SELECT
		pVidQuoteID as VidQuoteID,
		pVidQuoteItem as VidQuoteItem,
		R1.ProductoID AS VidQuoteProdID,
		pVidQuoteProdCantidad AS VidQuoteProdCantidad,
		R1.ProdCmpID AS VidQuoteElementoID,
		R1.ComposicID AS VidQuoteComposicID,
		R1.ElementoPM AS VidQuoteElementoPM,
		SUM(
          CalculoPrecio(
			pVidQuoteAncho, 
			pVidQuoteAlto,
			`R1`.`PrecioProd`,
			`R1`.`Magnitud`,
			`R1`.`OffsetVanoAlto`,
			`R1`.`OffsetVanoAncho`,
			`R1`.`CoefVanoAlto`,
			`R1`.`CoefVanoAncho`,
			`R1`.`ValorElemento`) * pCoeficienteMU) AS VidQuotePrecio,
		SUM(
          CalculoPrecio(
			pVidQuoteAncho, 
			pVidQuoteAlto,
			`R1`.`PrecioProd`,
			`R1`.`Magnitud`,
			`R1`.`OffsetVanoAlto`,
			`R1`.`OffsetVanoAncho`,
			`R1`.`CoefVanoAlto`,
			`R1`.`CoefVanoAncho`,
			`R1`.`ValorElemento`) * pVidQuoteProdCantidad * pCoeficienteMU) AS VidQuotePrecioP

	FROM
		-- --------------------------------------------------------------------------------
		-- Selecciona elementos de composicion iguales al producto 
		-- --------------------------------------------------------------------------------
		((
		SELECT 
		U1.ProductoID,
		U1.ElementoID AS ProdCmpID,
		U1.ComposicID,
		U1.ElementoID,
		U1.ElementoPM,
		U2.Magnitud,
		U1.ValorElemento,
		U1.OffsetVanoAncho,
		U1.OffsetVanoAlto,
		U1.CoefVanoAncho,
		U1.CoefVanoAlto,
		(CASE (pVidQuoteLPre)
		  WHEN 0 THEN U3.PrecioProd00
		  WHEN 1 THEN U3.PrecioProd01
		  WHEN 2 THEN U3.PrecioProd02
		  WHEN 3 THEN U3.PrecioProd03
		END) AS PrecioProd
		FROM
		fzggestion.productos AS U2,
		fzggestion.composicion AS U1,
		fzggestion.precios AS U3
		WHERE
		U2.ProductoID = U1.ElementoID AND 
		U3.ProductoID = U2.ProductoID AND 
		U1.ProductoID = pVidQuoteProdID AND 
		U1.ElementoID = pVidQuoteProdID AND 
		U1.ElementoPM <= pQuickSelect)
		-- --------------------------------------------------------------------------------
		UNION
		-- --------------------------------------------------------------------------------
		-- Selecciona elementos de composicion diferentes al producto
		-- --------------------------------------------------------------------------------
		(
		SELECT 
			T1.ProductoID,
			T1.ElementoID AS ProdCmpID,
			T1.ComposicID,
			T2.ElementoID,
			T1.ElementoPM,
-- 			T2.ElementoPM,
			T3.Magnitud,
			T1.ValorElemento,        
			T1.OffsetVanoAncho,
			T1.OffsetVanoAlto,
			T1.CoefVanoAncho,
			T1.CoefVanoAlto,
			(CASE (pVidQuoteLPre)
				WHEN 0 THEN `T4`.`PrecioProd00`
				WHEN 1 THEN `T4`.`PrecioProd01`
				WHEN 2 THEN `T4`.`PrecioProd02`
				WHEN 3 THEN `T4`.`PrecioProd03`
			END) AS PrecioProd
		FROM
			fzggestion.composicion AS T2,
			fzggestion.composicion AS T1,
			fzggestion.productos AS T3,
			fzggestion.precios AS T4
		WHERE
			T2.ProductoID = T1.ElementoID AND 
			T3.ProductoID = T2.ElementoID AND 
			T4.ProductoID = T3.ProductoID AND 
			T1.ProductoID <> T1.ElementoID AND 
			T2.ElementoPM <= pQuickSelect AND 
			T1.ProductoID = pVidQuoteProdID AND 
			T1.ElementoPM <= pQuickSelect
		ORDER BY
			T1.ProductoID ASC ,
			T1.ElementoID ASC ,
			T1.ComposicID ASC ,
			T2.ElementoID ASC)
		-- --------------------------------------------------------------------------------
		-- R1 es el sinónimo de la union de las tablas 
		-- --------------------------------------------------------------------------------
		) AS R1
      WHERE
			CONCAT(R1.ProductoID,R1.ProdCmpID,LPAD(CAST(R1.ComposicID AS CHAR),3,'000'))
      	NOT IN (
				SELECT
					CONCAT(VidQuoteProdID,VidQuoteElementoID,LPAD(CAST(VidQuoteComposicID AS CHAR),3,'000'))
				FROM
					vidquoten3tmp AS Q1 
				WHERE 
					Q1.VidQuoteID=pVidQuoteID AND 
					Q1.VidQuoteItem=pVidQuoteItem)
		GROUP BY
			VidQuoteElementoID,
			VidQuoteComposicID;
    -- --------------------------------------------------------------------------------
    -- Joint Para completar info de ElementoPM
    -- --------------------------------------------------------------------------------
    UPDATE
        vidquoten3tmp AS T1,
        composicion AS T2
    SET
        T1.VidQuoteElementoPM = T2.ElementoPM
    WHERE
        T1.VidQuoteID=pVidQuoteID AND
        ISNULL(T1.VidQuoteElementoPM) AND
        T1.VidQuoteProdID=T2.ProductoID AND
        T1.VidQuoteElementoID=T2.ElementoID AND
        T1.VidQuoteComposicID=T2.ComposicID;
END
</pre><small>

== Uso ==
* [[MACRO:BotonEditarComponentes]]
* [[FORM:Presupuesto]]