 category proc descripciu8c3b3n descripciu8c3b3n descripciu8c3b3n actualizador deu800 precios deu800 unu800 presupuesto indicado. toma toda lau800 informaciu8c3b3n existente yu800 lau800 actualiza deu800 acuerdo au800 lau800 lista deu800 precios vigente. luego deu800 actualizar losu800 precios au800 nivel elemento procede au800 actualizar losu800 precios delu800 detalle delu800 presupuesto invocando unu800 update ficticio para queu800 ejecute sinu800 dead-lock au800 travu8c3a9s delu800 trig actualiza_item_presupuesto composiciu8c3b3n composiciu8c3b3n composiciu8c3b3n tabla vidquoten3 tabla vidquoten2 tabla precios tabla productos tabla composicion cu8c3b3digo cu8c3b3digo cu8c3b3digo -- -------------------------------------------------------------------------------- -- routine ddlu800 -- note comments before andu800 after theu800 routine body will notu800 beu800 stored byu800 theu800 server -- -------------------------------------------------------------------------------- delimiter create definer sas_admin procedure actualizapreciospresupuesto inu800 pvidquoteid intu800 5u800 -- inu800 pvidquoteitem intu800 5u800 inu800 pvidquotelista intu800 1u800 inu800 pcoeficienteac decimal 10u800 5u800 begin -- ---------------------------------------------------- -- update from tables with productoid elementoid -- ---------------------------------------------------- update composicion asu800 t2u800 vidquoten3 asu800 t1u800 precios asu800 t3u800 productos asu800 t4u800 vidquoten2 asu800 t5u800 setu800 t1u800u82evidquoteprecio calculoprecio t5u800u82evidquoteancho t5u800u82evidquotealto case pvidquotelista when 0u800 then t3u800u82eprecioprod00 when 1u800 then t3u800u82eprecioprod01 when 2u800 then t3u800u82eprecioprod02 when 3u800 then t3u800u82eprecioprod03 endu800 t4u800u82emagnitud t2u800u82eoffsetvanoalto t2u800u82eoffsetvanoancho t2u800u82ecoefvanoalto t2u800u82ecoefvanoancho t2u800u82evalorelemento pcoeficienteac t1u800u82evidquotepreciop calculoprecio t5u800u82evidquoteancho t5u800u82evidquotealto case pvidquotelista when 0u800 then t3u800u82eprecioprod00 when 1u800 then t3u800u82eprecioprod01 when 2u800 then t3u800u82eprecioprod02 when 3u800 then t3u800u82eprecioprod03 endu800 t4u800u82emagnitud t2u800u82eoffsetvanoalto t2u800u82eoffsetvanoancho t2u800u82ecoefvanoalto t2u800u82ecoefvanoancho t2u800u82evalorelemento pcoeficienteac t5u800u82evidquoteprodcantidad where t1u800u82evidquoteid pvidquoteid andu800 -- t1u800u82evidquoteitem ifu800 isnull pvidquoteitem t1u800u82evidquoteitem pvidquoteitem andu800 t2u800u82eproductoid t1u800u82evidquoteprodid andu800 t2u800u82eelementoid t1u800u82evidquoteelementoid andu800 t2u800u82ecomposicid t1u800u82evidquotecomposicid andu800 t4u800u82eproductoid t3u800u82eproductoid andu800 t5u800u82evidquoteid t1u800u82evidquoteid andu800 t5u800u82evidquoteitem t1u800u82evidquoteitem andu800 t3u800u82eproductoid t2u800u82eelementoid andu800 t1u800u82evidquoteprodid t1u800u82evidquoteelementoid; -- ---------------------------------------------------- -- update from tables with productoid elementoid -- ---------------------------------------------------- update vidquoten3 asu800 w1u800 select v1u800u82evidquoteid v1u800u82evidquoteitem v1u800u82evidquoteprodid v1u800u82evidquoteelementoid v1u800u82evidquotecomposicid sumu800 calculoprecio v6u800u82evidquoteancho v6u800u82evidquotealto case pvidquotelista when 0u800 then v4u800u82eprecioprod00 when 1u800 then v4u800u82eprecioprod01 when 2u800 then v4u800u82eprecioprod02 when 3u800 then v4u800u82eprecioprod03 endu800 v5u800u82emagnitud v2u800u82eoffsetvanoalto v3u800u82eoffsetvanoalto v2u800u82eoffsetvanoancho v3u800u82eoffsetvanoancho v2u800u82ecoefvanoalto v3u800u82ecoefvanoalto v2u800u82ecoefvanoancho v3u800u82ecoefvanoancho v2u800u82evalorelemento v3u800u82evalorelemento pcoeficienteac asu800 vidquoteprecio sumu800 calculoprecio v6u800u82evidquoteancho v6u800u82evidquotealto case pvidquotelista when 0u800 then v4u800u82eprecioprod00 when 1u800 then v4u800u82eprecioprod01 when 2u800 then v4u800u82eprecioprod02 when 3u800 then v4u800u82eprecioprod03 endu800 v5u800u82emagnitud v2u800u82eoffsetvanoalto v3u800u82eoffsetvanoalto v2u800u82eoffsetvanoancho v3u800u82eoffsetvanoancho v2u800u82ecoefvanoalto v3u800u82ecoefvanoalto v2u800u82ecoefvanoancho v3u800u82ecoefvanoancho v2u800u82evalorelemento v3u800u82evalorelemento pcoeficienteac v6u800u82evidquoteprodcantidad asu800 vidquotepreciop from fzggestionu82ecomposicion asu800 v3u800 fzggestionu82ecomposicion asu800 v2u800 fzggestionu82evidquoten3 asu800 v1u800 fzggestionu82eprecios asu800 v4u800 fzggestionu82eproductos asu800 v5u800 fzggestionu82evidquoten2 asu800 v6u800 where v1u800u82evidquoteid pvidquoteid andu800 -- v1u800u82evidquoteitem ifu800 isnull pvidquoteitem v1u800u82evidquoteitem pvidquoteitem andu800 v6u800u82evidquoteid v1u800u82evidquoteid andu800 v6u800u82evidquoteitem v1u800u82evidquoteitem andu800 v3u800u82eproductoid v2u800u82eelementoid andu800 v2u800u82eproductoid v1u800u82evidquoteprodid andu800 v2u800u82eelementoid v1u800u82evidquoteelementoid andu800 v2u800u82ecomposicid v1u800u82evidquotecomposicid andu800 v4u800u82eproductoid v3u800u82eelementoid andu800 v5u800u82eproductoid v4u800u82eproductoid andu800 v1u800u82evidquoteprodid v1u800u82evidquoteelementoid andu800 v3u800u82eelementopm 0u800 group byu800 v1u800u82evidquoteid v1u800u82evidquoteitem v1u800u82evidquoteelementoid v1u800u82evidquotecomposicid asu800 w2u800 setu800 w1u800u82evidquoteprecio w2u800u82evidquoteprecio w1u800u82evidquotepreciop w2u800u82evidquotepreciop where -- ---------------------------------------------------------- -- join between result ofu800 calculating prices andu800 vidquoten3 -- ---------------------------------------------------------- w2u800u82evidquoteid w1u800u82evidquoteid andu800 w2u800u82evidquoteitem w1u800u82evidquoteitem andu800 w2u800u82evidquoteprodid w1u800u82evidquoteprodid andu800 w2u800u82evidquoteelementoid w1u800u82evidquoteelementoid andu800 w2u800u82evidquotecomposicid w1u800u82evidquotecomposicid andu800 -- ---------------------------------------------------------- -- select what qoute will beu800 affected -- ---------------------------------------------------------- w1u800u82evidquoteid pvidquoteid andu800 -- ---------------------------------------------------------- -- select records notu800 ppdu800 -- ---------------------------------------------------------- w1u800u82evidquoteprodid w1u800u82evidquoteelementoid; -- ---------------------------------------------------------- -- update precios resumen onu800 vidquoten2. nou800 effect onu800 others -- fields than vidquoteprecio -- ---------------------------------------------------------- update vidquoten2 asu800 t1u800 setu800 t1u800u82evidquoteprodcantidad t1u800u82evidquoteprodcantidad where t1u800u82evidquoteid pvidquoteid; endu800 bitu8c3a1cora bitu8c3a1cora bitu8c3a1cora 20120921 actualizaciu8c3b3n deu800 acuerdo alu800 cambio deu800 diseu8c3b1o deu800 form presupuesto -- -------------------------------------------------------------------------------- -- routine ddlu800 -- note comments before andu800 after theu800 routine body will notu800 beu800 stored byu800 theu800 server -- -------------------------------------------------------------------------------- delimiter create definer sas_admin procedure actualizapreciospresupuesto_old inu800 pvidquoteid intu800 5u800 begin update vidquoten3 asu800 t5u800 select t4u800u82evidquoteprodid t4u800u82evidquoteelementoid t4u800u82evidquoteprodcantidad t4u800u82evidquoteancho t4u800u82evidquotealto t4u800u82eprecioprod sumu800 calculoprecio t4u800u82evidquoteancho t4u800u82evidquotealto t4u800u82eprecioprod t4u800u82emagnitud t4u800u82eoffsetvanoalto t4u800u82eoffsetvanoancho t4u800u82ecoefvanoalto t4u800u82ecoefvanoancho t4u800u82evalorelemento asu800 vidquoteprecio sumu800 calculoprecio t4u800u82evidquoteancho t4u800u82evidquotealto t4u800u82eprecioprod t4u800u82emagnitud t4u800u82eoffsetvanoalto t4u800u82eoffsetvanoancho t4u800u82ecoefvanoalto t4u800u82ecoefvanoancho t4u800u82evalorelemento t4u800u82evidquoteprodcantidad asu800 vidquotepreciop from select t1u800u82evidquotelpre asu800 vidquotelpre t1u800u82evidquoteprodid asu800 vidquoteprodid t1u800u82evidquoteelementoid asu800 vidquoteelementoid t2u800u82eelementoid asu800 elementoid t1u800u82evidquoteprodcantidad asu800 vidquoteprodcantidad t1u800u82evidquoteancho asu800 vidquoteancho t1u800u82evidquotealto asu800 vidquotealto t2u800u82emagnitud t2u800u82evalorelemento asu800 valorelemento t2u800u82eoffsetvanoancho asu800 offsetvanoancho t2u800u82eoffsetvanoalto asu800 offsetvanoalto t2u800u82ecoefvanoancho asu800 coefvanoancho t2u800u82ecoefvanoalto asu800 coefvanoalto case t1u800u82evidquotelpre when 0u800 then t2u800. precioprod00 when 1u800 then t2u800. precioprod01 when 2u800 then t2u800. precioprod02 when 3u800 then t2u800. precioprod03 endu800 asu800 precioprod from oju800 fzggestionu82evisprodadic asu800 t2u800 right outer join fzggestionu82evidquoten3 asu800 t1u800 onu800 t2u800u82eproductoid t1u800u82evidquoteprodid andu800 t2u800u82eelementoid t1u800u82evidquoteelementoid where t1u800u82evidquoteprodid vidquoteelementoid andu800 t1u800u82evidquoteid pvidquoteid union select t1u800u82evidquotelpre asu800 vidquotelpre t1u800u82evidquoteprodid asu800 vidquoteprodid t1u800u82evidquoteelementoid asu800 vidquoteelementoid t2u800u82eelementoid asu800 elementoid t1u800u82evidquoteprodcantidad asu800 vidquoteprodcantidad t1u800u82evidquoteancho asu800 vidquoteancho t1u800u82evidquotealto asu800 vidquotealto t2u800u82emagnitud t3u800u82evalorelemento t2u800u82evalorelemento asu800 valorelemento t3u800u82eoffsetvanoancho t2u800u82eoffsetvanoancho asu800 offsetvanoancho t3u800u82eoffsetvanoalto t2u800u82eoffsetvanoalto asu800 offsetvanoalto t3u800u82ecoefvanoancho t2u800u82ecoefvanoancho asu800 coefvanoancho t3u800u82ecoefvanoalto t2u800u82ecoefvanoalto asu800 coefvanoalto case t1u800u82evidquotelpre when 0u800 then t2u800. precioprod00 when 1u800 then t2u800. precioprod01 when 2u800 then t2u800. precioprod02 when 3u800 then t2u800. precioprod03 endu800 asu800 precioprod from oju800 fzggestionu82evisprodadic asu800 t2u800 right outer join fzggestionu82evidquoten3 asu800 t1u800 onu800 t2u800u82eproductoid t1u800u82evidquoteelementoid fzggestionu82evisprodadic asu800 t3u800 where t3u800u82eproductoid t1u800u82evidquoteprodid andu800 t3u800u82eelementoid t1u800u82evidquoteelementoid andu800 t1u800u82evidquoteprodid t1u800u82evidquoteelementoid andu800 t1u800u82evidquoteid pvidquoteid order byu800 vidquoteprodid vidquoteelementoid elementoid asu800 t4u800 group byu800 vidquoteprodid vidquoteelementoid asu800 t6u800 setu800 t5u800u82evidquoteprecio t6u800u82evidquoteprecio t5u800u82evidquotepreciop t6u800u82evidquotepreciop where t5u800u82evidquoteid pvidquoteid andu800 t5u800u82evidquoteprodid t6u800u82evidquoteprodid andu800 t5u800u82evidquoteelementoid t6u800u82evidquoteelementoid; endu800 20120925 reestructura actualizaciu8c3b3n delu800 atributo deu800 precio debido au800 queu800 seu800 incorpora elu800 trig actualiza_item_presupuesto yu800 produce unu800 dead-lock alu800 gestionar lasu800 mismas tablas. seu800 modifica elu800 trig actualiza_item_presupuesto yu800 seu800 hace unu800 update falso para forzar elu800 trigger deu800 actualizaciu8c3b3n au800 hacer elu800 recu8c3a1lculo. update vidquoten2 asu800 t1u800 select vidquoteid vidquoteitem vidquoteprodcantidad vidquoteprodid sumu800 vidquotepreciop asu800 vidquotepreciop from vidquoten3 group byu800 vidquoteid vidquoteitem asu800 t2u800 setu800 t1u800u82evidquoteprecio t2u800u82evidquotepreciop where t1u800u82evidquoteid pvidquoteid andu800 t1u800u82evidquoteid t2u800u82evidquoteid andu800 t1u800u82evidquoteitem t2u800u82evidquoteitem; endu800 usou800 usou800 usou800 form presupuesto macro botonactualizarpresupuesto trig actualiza_item_presupuesto 