 category proc descripciu8c3b3n descripciu8c3b3n descripciu8c3b3n elu800 procedimiento estu8c3a1 enu800 funciu8c3b3n deu800 lau800 ediciu8c3b3n deu800 losu800 elementos deu800 unu800 producto compuesto durante lau800 confecciu8c3b3n deu800 unu800 presupuesto. agrega au800 lau800 tabla vidquoten3tmp lasu800 registraciones correspondientes alu800 producto au800 editar. lasu800 caracteru8c3adsticas delu800 proceso sonu800 lasu800 siguientes selecciona registros deu800 lau800 tabla composicion queu800 corresponde alu800 producto seleccionado yu800 agrega aquellos registros queu800 nou800 existan enu800 lau800 selececiu8c3b3n original alta deu800 producto selecciona losu800 registros deu800 lau800 tabla composicion cuyo elemento esu800 igual alu800 producto producto propiamente dicho . este punto seu800 explicita para queu800 nou800 entre enu800 loop selecciona losu800 registros deu800 lau800 tabla composicion cuyos elementos sonu800 diferentes alu800 producto adicionales bu8c3a1sicos simple yu800 completo . dado queu800 permite unu800 grado deu800 profundidad adicional toma tanto losu800 registros componentes sino tambiu8c3a9n todos aquellos queu800 estu8c3a1n referenciados como elemento componente. establece unau800 relaciu8c3b3n conu800 lasu800 tabla productos yu800 tabla precios . establece unau800 relaciu8c3b3n anidada conu800 lau800 misma tabla composicion para tomar todos losu800 elementos como siu800 fuesen productos yu800 asu8c3ad tener enu800 cuenta lau800 definiciu8c3b3n enu800 aquellos queu800 tengan mu8c3a1s deu800 unu800 componente yu800 poder calcular elu800 precio deu800 acuerdo au800 losu800 paru8c3a1metros definidos agrega losu800 datos resultantes au800 lau800 tabla vidquoten3tmp previo borrado deu800 losu800 datos anteriores dentro deu800 lau800 tabla temporal para mantener lau800 integridad deu800 lau800 operaciu8c3b3n para proceder au800 lau800 ediciu8c3b3n. nou800 seu800 deben agregar aquellos registros yau800 existentes enu800 lau800 tabla vidquoten3 composiciu8c3b3n composiciu8c3b3n composiciu8c3b3n tabla composicion tabla productos tabla precios tabla vidquoten3 tabla vidquoten3tmp cu8c3b3digo cu8c3b3digo cu8c3b3digo drop procedure altaitempresupuestotmp ; delimiter create definer sas_admin procedure altaitempresupuestotmp inu800 pvidquoteid intu800 5u800 inu800 pvidquotelpre intu800 1u800 inu800 pvidquoteitem intu800 5u800 inu800 pvidquoteprodid char 8u800 inu800 pvidquoteprodcantidad intu800 5u800 inu800 pvidquoteancho intu800 5u800 inu800 pvidquotealto intu800 5u800 inu800 pcoeficientemu decimal 5u800 2u800 inu800 pquickselect intu800 1u800 begin -- -------------------------------------------------------------------------------- -- inserta registro deu800 auditoria -- -------------------------------------------------------------------------------- -- insert into debugstmt fecha hora texto -- values -- curdate -- curtime -- concat -- cast pvidquoteid asu800 char - -- cast pvidquotelpre asu800 char - -- cast pvidquoteitem asu800 char - -- pvidquoteprodid - -- cast pvidquoteprodcantidad asu800 char - -- cast pvidquoteancho asu800 char - -- cast pvidquotealto asu800 char -- -- ; -- -------------------------------------------------------------------------------- -- elimina residuos deu800 ediciones anteriores -- -------------------------------------------------------------------------------- -- call debugcheckpoint -- concat -- prev - -- pvidquoteid -- cast pvidquoteid asu800 char - -- pvidquoteitem -- cast pvidquoteitem asu800 char - -- delete count -- select cast count asu800 char -- from vidquoten3tmp -- where -- vidquoteid pvidquoteid andu800 -- vidquoteitem pvidquoteitem -- ; delete from vidquoten3tmp where vidquoteid pvidquoteid andu800 vidquoteitem pvidquoteitem; -- call debugcheckpoint -- concat -- past - -- pvidquoteid -- cast pvidquoteid asu800 char - -- pvidquoteitem -- cast pvidquoteitem asu800 char - -- delete count -- select cast count asu800 char -- from vidquoten3tmp -- where -- vidquoteid pvidquoteid andu800 -- vidquoteitem pvidquoteitem -- ; -- -------------------------------------------------------------------------------- -- inserta registraciones anteriores delu800 mismo producto item -- -------------------------------------------------------------------------------- insert into vidquoten3tmp vidquoteid vidquoteitem vidquoteprodid vidquoteprodcantidad vidquoteelementoid vidquotecomposicid vidquoteelementopm vidquoteprecio vidquotepreciop vidquoteincluyedet vidquotequickselect select s1u800u82evidquoteid s1u800u82evidquoteitem s1u800u82evidquoteprodid s1u800u82evidquoteprodcantidad s1u800u82evidquoteelementoid s1u800u82evidquotecomposicid s1u800u82evidquoteelementopm s1u800u82evidquoteprecio s1u800u82evidquotepreciop s1u800u82evidquoteincluyedet 1u800 asu800 vidquotequickselect from vidquoten3 asu800 s1u800 where s1u800u82evidquoteid pvidquoteid andu800 s1u800u82evidquoteitem pvidquoteitem; call debugcheckpoint past inserta registros vq3u800 anteriores ; -- -------------------------------------------------------------------------------- -- inserta registraciones pausibles deu800 incorporar deu800 acuerdo alu800 tipo deu800 -- -------------------------------------------------------------------------------- insert into vidquoten3tmp vidquoteid vidquoteitem vidquoteprodid vidquoteprodcantidad vidquoteelementoid vidquotecomposicid vidquoteelementopm vidquoteprecio vidquotepreciop select pvidquoteid asu800 vidquoteid pvidquoteitem asu800 vidquoteitem r1u800u82eproductoid asu800 vidquoteprodid pvidquoteprodcantidad asu800 vidquoteprodcantidad r1u800u82eprodcmpid asu800 vidquoteelementoid r1u800u82ecomposicid asu800 vidquotecomposicid r1u800u82eelementopm asu800 vidquoteelementopm sumu800 calculoprecio pvidquoteancho pvidquotealto r1u800 . precioprod r1u800 . magnitud r1u800 . offsetvanoalto r1u800 . offsetvanoancho r1u800 . coefvanoalto r1u800 . coefvanoancho r1u800 . valorelemento pcoeficientemu asu800 vidquoteprecio sumu800 calculoprecio pvidquoteancho pvidquotealto r1u800 . precioprod r1u800 . magnitud r1u800 . offsetvanoalto r1u800 . offsetvanoancho r1u800 . coefvanoalto r1u800 . coefvanoancho r1u800 . valorelemento pvidquoteprodcantidad pcoeficientemu asu800 vidquotepreciop from -- -------------------------------------------------------------------------------- -- selecciona elementos deu800 composicion iguales alu800 producto -- -------------------------------------------------------------------------------- select u1u800u82eproductoid u1u800u82eelementoid asu800 prodcmpid u1u800u82ecomposicid u1u800u82eelementoid u1u800u82eelementopm u2u800u82emagnitud u1u800u82evalorelemento u1u800u82eoffsetvanoancho u1u800u82eoffsetvanoalto u1u800u82ecoefvanoancho u1u800u82ecoefvanoalto case pvidquotelpre when 0u800 then u3u800u82eprecioprod00 when 1u800 then u3u800u82eprecioprod01 when 2u800 then u3u800u82eprecioprod02 when 3u800 then u3u800u82eprecioprod03 endu800 asu800 precioprod from fzggestionu82eproductos asu800 u2u800 fzggestionu82ecomposicion asu800 u1u800 fzggestionu82eprecios asu800 u3u800 where u2u800u82eproductoid u1u800u82eelementoid andu800 u3u800u82eproductoid u2u800u82eproductoid andu800 u1u800u82eproductoid pvidquoteprodid andu800 u1u800u82eelementoid pvidquoteprodid andu800 u1u800u82eelementopm pquickselect -- -------------------------------------------------------------------------------- union -- -------------------------------------------------------------------------------- -- selecciona elementos deu800 composicion diferentes alu800 producto -- -------------------------------------------------------------------------------- select t1u800u82eproductoid t1u800u82eelementoid asu800 prodcmpid t1u800u82ecomposicid t2u800u82eelementoid t1u800u82eelementopm -- t2u800u82eelementopm t3u800u82emagnitud t1u800u82evalorelemento t2u800u82evalorelemento asu800 valorelemento t1u800u82eoffsetvanoancho t2u800u82eoffsetvanoancho asu800 offsetvanoancho t1u800u82eoffsetvanoalto t2u800u82eoffsetvanoalto asu800 offsetvanoalto t1u800u82ecoefvanoancho t2u800u82ecoefvanoancho asu800 coefvanoancho t1u800u82ecoefvanoalto t2u800u82ecoefvanoalto asu800 coefvanoalto case pvidquotelpre when 0u800 then t4u800 . precioprod00 when 1u800 then t4u800 . precioprod01 when 2u800 then t4u800 . precioprod02 when 3u800 then t4u800 . precioprod03 endu800 asu800 precioprod from fzggestionu82ecomposicion asu800 t2u800 fzggestionu82ecomposicion asu800 t1u800 fzggestionu82eproductos asu800 t3u800 fzggestionu82eprecios asu800 t4u800 where t2u800u82eproductoid t1u800u82eelementoid andu800 t3u800u82eproductoid t2u800u82eelementoid andu800 t4u800u82eproductoid t3u800u82eproductoid andu800 t1u800u82eproductoid t1u800u82eelementoid andu800 t2u800u82eelementopm pquickselect andu800 t1u800u82eproductoid pvidquoteprodid andu800 t1u800u82eelementopm pquickselect order byu800 t1u800u82eproductoid ascu800 t1u800u82eelementoid ascu800 t1u800u82ecomposicid ascu800 t2u800u82eelementoid ascu800 -- -------------------------------------------------------------------------------- -- r1u800 esu800 elu800 sinu8c3b3nimo deu800 lau800 union deu800 lasu800 tablas -- -------------------------------------------------------------------------------- asu800 r1u800 where concat r1u800u82eproductoid r1u800u82eprodcmpid lpad cast r1u800u82ecomposicid asu800 char 3u800 '000u800' notu800 inu800 select concat vidquoteprodid vidquoteelementoid lpad cast vidquotecomposicid asu800 char 3u800 '000u800' from vidquoten3tmp asu800 q1u800 where q1u800u82evidquoteid pvidquoteid andu800 q1u800u82evidquoteitem pvidquoteitem group byu800 vidquoteelementoid vidquotecomposicid; -- -------------------------------------------------------------------------------- -- joint para completar info deu800 elementopm -- -------------------------------------------------------------------------------- update vidquoten3tmp asu800 t1u800 composicion asu800 t2u800 setu800 t1u800u82evidquoteelementopm t2u800u82eelementopm where t1u800u82evidquoteid pvidquoteid andu800 isnull t1u800u82evidquoteelementopm andu800 t1u800u82evidquoteprodid t2u800u82eproductoid andu800 t1u800u82evidquoteelementoid t2u800u82eelementoid andu800 t1u800u82evidquotecomposicid t2u800u82ecomposicid; endu800 bitu8c3a1cora bitu8c3a1cora bitu8c3a1cora 20120904 cu8c3b3digo anterior delimiter create definer sas_admin procedure altaitempresupuestotmp inu800 pvidquoteid intu800 5u800 inu800 pvidquotelpre intu800 1u800 inu800 pvidquoteitem intu800 5u800 inu800 pvidquoteprodid char 8u800 inu800 pvidquoteprodcantidad intu800 5u800 inu800 pvidquoteancho intu800 5u800 inu800 pvidquotealto intu800 5u800 begin insert into debugstmt fecha hora texto values curdate curtime concat cast pvidquoteid asu800 char - cast pvidquotelpre asu800 char - cast pvidquoteitem asu800 char - pvidquoteprodid - cast pvidquoteprodcantidad asu800 char - cast pvidquoteancho asu800 char - cast pvidquotealto asu800 char ; delete from vidquoten3tmp where vidquoteid pvidquoteid andu800 vidquoteitem pvidquoteitem; insert into vidquoten3tmp vidquoteid vidquoteitem vidquoteprodid vidquoteprodcantidad vidquoteelementoid vidquoteprecio vidquotepreciop select pvidquoteid pvidquoteitem pvidquoteprodid pvidquoteprodcantidad t3u800 . prodcmpid sumu800 calculoprecio pvidquoteancho pvidquotealto t3u800 . precioprod t3u800 . magnitud t3u800 . offsetvanoalto t3u800 . offsetvanoancho t3u800 . coefvanoalto t3u800 . coefvanoancho t3u800 . valorelemento asu800 pvidquoteprecio sumu800 calculoprecio pvidquoteancho pvidquotealto t3u800 . precioprod t3u800 . magnitud t3u800 . offsetvanoalto t3u800 . offsetvanoancho t3u800 . coefvanoalto t3u800 . coefvanoancho t3u800 . valorelemento pvidquoteprodcantidad asu800 pvidquotepreciop from select t1u800 . productoid asu800 productoid t1u800 . productoid asu800 prodcmpid t1u800 . elementoid t1u800 . valorelemento t1u800 . descripcion t1u800 . magnitud t1u800 . offsetvanoancho t1u800 . offsetvanoalto t1u800 . coefvanoancho t1u800 . coefvanoalto case pvidquotelpre when 0u800 then t1u800 . precioprod00 when 1u800 then t1u800 . precioprod01 when 2u800 then t1u800 . precioprod02 when 3u800 then t1u800 . precioprod03 endu800 asu800 precioprod from visprodadic asu800 t1u800 where productoid pvidquoteprodid andu800 elementoid pvidquoteprodid andu800 elementopm 1u800 union select pvidquoteprodid asu800 productoid t2u800 . productoid asu800 prodcmpid t2u800 . elementoid t2u800 . valorelemento t2u800 . descripcion t2u800 . magnitud t2u800 . offsetvanoancho t2u800 . offsetvanoalto t2u800 . coefvanoancho t2u800 . coefvanoalto case pvidquotelpre when 0u800 then t2u800 . precioprod00 when 1u800 then t2u800 . precioprod01 when 2u800 then t2u800 . precioprod02 when 3u800 then t2u800 . precioprod03 endu800 asu800 precioprod from visprodadic asu800 t2u800 where productoid inu800 select elementoid from visprodadic where productoid pvidquoteprodid andu800 elementoid pvidquoteprodid andu800 elementopm 1u800 asu800 t3u800 group byu800 prodcmpid; endu800 20121009 error deu800 change management alu800 subir elu800 programa deu800 test conu800 losu800 paru8c3a1metros deu800 prueba. condiciu8c3b3n deu800 lau800 tabla vidquoten3tmp tipo select inu800 para incorporar solo losu800 registros queu800 yau800 nou800 existan enu800 lau800 tabla vidquoten3tmp . tambiu8c3a9n seu800 agrega lau800 salvaguarda deu800 grabaciu8c3b3n deu800 elementopm enu800 elu800 caso deu800 nou800 integral yu800 losu800 errores deu800 paru8c3a1metros deu800 prueba para parm pvidquotelpre -- -------------------------------------------------------------------------------- -- routine ddlu800 -- note comments before andu800 after theu800 routine body will notu800 beu800 stored byu800 theu800 server -- -------------------------------------------------------------------------------- delimiter create definer sas_admin procedure altaitempresupuestotmp inu800 pvidquoteid intu800 5u800 inu800 pvidquotelpre intu800 1u800 inu800 pvidquoteitem intu800 5u800 inu800 pvidquoteprodid char 8u800 inu800 pvidquoteprodcantidad intu800 5u800 inu800 pvidquoteancho intu800 5u800 inu800 pvidquotealto intu800 5u800 inu800 pcoeficientemu decimal 5u800 2u800 inu800 pquickselect intu800 1u800 begin -- -------------------------------------------------------------------------------- -- inserta registro deu800 auditoria -- -------------------------------------------------------------------------------- insert into debugstmt fecha hora texto values curdate curtime concat cast pvidquoteid asu800 char - cast pvidquotelpre asu800 char - cast pvidquoteitem asu800 char - pvidquoteprodid - cast pvidquoteprodcantidad asu800 char - cast pvidquoteancho asu800 char - cast pvidquotealto asu800 char ; -- -------------------------------------------------------------------------------- -- elimina residuos deu800 ediciones anteriores -- -------------------------------------------------------------------------------- delete from vidquoten3tmp where vidquoteid pvidquoteid andu800 vidquoteitem pvidquoteitem; -- -------------------------------------------------------------------------------- -- inserta registraciones anteriores delu800 mismo producto item -- -------------------------------------------------------------------------------- insert into vidquoten3tmp vidquoteid vidquoteitem vidquoteprodid vidquoteprodcantidad vidquoteelementoid vidquotecomposicid vidquoteprecio vidquotepreciop vidquoteincluyedet vidquotequickselect select s1u800u82evidquoteid s1u800u82evidquoteitem s1u800u82evidquoteprodid s1u800u82evidquoteprodcantidad s1u800u82evidquoteelementoid s1u800u82evidquotecomposicid s1u800u82evidquoteprecio s1u800u82evidquotepreciop s1u800u82evidquoteincluyedet 1u800 asu800 vidquotequickselect from vidquoten3 asu800 s1u800 where s1u800u82evidquoteid pvidquoteid andu800 s1u800u82evidquoteitem pvidquoteitem; -- -------------------------------------------------------------------------------- -- inserta registraciones pausibles deu800 incorporar deu800 acuerdo alu800 tipo deu800 -- -------------------------------------------------------------------------------- insert into vidquoten3tmp vidquoteid vidquoteitem vidquoteprodid vidquoteprodcantidad vidquoteelementoid vidquotecomposicid vidquoteprecio vidquotepreciop select pvidquoteid asu800 vidquoteid pvidquoteitem asu800 vidquoteitem r1u800u82eproductoid asu800 vidquoteprodid pvidquoteprodcantidad asu800 vidquoteprodcantidad r1u800u82eprodcmpid asu800 vidquoteelementoid r1u800u82ecomposicid asu800 vidquotecomposicid sumu800 calculoprecio pvidquoteancho pvidquotealto r1u800 . precioprod r1u800 . magnitud r1u800 . offsetvanoalto r1u800 . offsetvanoancho r1u800 . coefvanoalto r1u800 . coefvanoancho r1u800 . valorelemento pcoeficientemu asu800 vidquoteprecio sumu800 calculoprecio pvidquoteancho pvidquotealto r1u800 . precioprod r1u800 . magnitud r1u800 . offsetvanoalto r1u800 . offsetvanoancho r1u800 . coefvanoalto r1u800 . coefvanoancho r1u800 . valorelemento pvidquoteprodcantidad pcoeficientemu asu800 vidquotepreciop from -- -------------------------------------------------------------------------------- -- selecciona elementos deu800 composicion iguales alu800 producto -- -------------------------------------------------------------------------------- select u1u800u82eproductoid u1u800u82eelementoid asu800 prodcmpid u1u800u82ecomposicid u1u800u82eelementoid u1u800u82eelementopm u2u800u82emagnitud u1u800u82evalorelemento u1u800u82eoffsetvanoancho u1u800u82eoffsetvanoalto u1u800u82ecoefvanoancho u1u800u82ecoefvanoalto case 2u800 when 0u800 then u3u800u82eprecioprod00 when 1u800 then u3u800u82eprecioprod01 when 2u800 then u3u800u82eprecioprod02 when 3u800 then u3u800u82eprecioprod03 endu800 asu800 precioprod from fzggestionu82eproductos asu800 u2u800 fzggestionu82ecomposicion asu800 u1u800 fzggestionu82eprecios asu800 u3u800 where u2u800u82eproductoid u1u800u82eelementoid andu800 u3u800u82eproductoid u2u800u82eproductoid andu800 u1u800u82eproductoid pvidquoteprodid andu800 u1u800u82eelementoid pvidquoteprodid andu800 u1u800u82eelementopm pquickselect -- -------------------------------------------------------------------------------- union -- -------------------------------------------------------------------------------- -- selecciona elementos deu800 composicion diferentes alu800 producto -- -------------------------------------------------------------------------------- select t1u800u82eproductoid t1u800u82eelementoid asu800 prodcmpid t1u800u82ecomposicid t2u800u82eelementoid t2u800u82eelementopm t3u800u82emagnitud t1u800u82evalorelemento t1u800u82eoffsetvanoancho t1u800u82eoffsetvanoalto t1u800u82ecoefvanoancho t1u800u82ecoefvanoalto case 2u800 when 0u800 then t4u800 . precioprod00 when 1u800 then t4u800 . precioprod01 when 2u800 then t4u800 . precioprod02 when 3u800 then t4u800 . precioprod03 endu800 asu800 precioprod from fzggestionu82ecomposicion asu800 t2u800 fzggestionu82ecomposicion asu800 t1u800 fzggestionu82eproductos asu800 t3u800 fzggestionu82eprecios asu800 t4u800 where t2u800u82eproductoid t1u800u82eelementoid andu800 t3u800u82eproductoid t2u800u82eelementoid andu800 t4u800u82eproductoid t3u800u82eproductoid andu800 t1u800u82eproductoid t1u800u82eelementoid andu800 t2u800u82eelementopm pquickselect andu800 t1u800u82eproductoid pvidquoteprodid andu800 t1u800u82eelementopm pquickselect order byu800 t1u800u82eproductoid ascu800 t1u800u82eelementoid ascu800 t1u800u82ecomposicid ascu800 t2u800u82eelementoid ascu800 -- -------------------------------------------------------------------------------- -- r1u800 esu800 elu800 sinu8c3b3nimo deu800 lau800 union deu800 lasu800 tablas -- -------------------------------------------------------------------------------- asu800 r1u800 where concat r1u800u82eproductoid r1u800u82eprodcmpid lpad cast r1u800u82ecomposicid asu800 char 3u800 '000u800' notu800 inu800 select concat vidquoteprodid vidquoteelementoid lpad cast vidquotecomposicid asu800 char 3u800 '000u800' from vidquoten3tmp asu800 q1u800 where q1u800u82evidquoteid pvidquoteid andu800 q1u800u82evidquoteitem pvidquoteitem group byu800 vidquoteelementoid vidquotecomposicid; endu800 20121207 seu800 detecta errores enu800 elu800 cu8c3a1lculo combinado poru800 lau800 falta deu800 propagaciu8c3b3n deu800 atributos deu800 componentes conu800 niveles adicionales deu800 composiciu8c3b3n dvhu800 etcu800 . anterior -- -------------------------------------------------------------------------------- -- routine ddlu800 -- note comments before andu800 after theu800 routine body will notu800 beu800 stored byu800 theu800 server -- -------------------------------------------------------------------------------- delimiter create definer sas_admin procedure altaitempresupuestotmp inu800 pvidquoteid intu800 5u800 inu800 pvidquotelpre intu800 1u800 inu800 pvidquoteitem intu800 5u800 inu800 pvidquoteprodid char 8u800 inu800 pvidquoteprodcantidad intu800 5u800 inu800 pvidquoteancho intu800 5u800 inu800 pvidquotealto intu800 5u800 inu800 pcoeficientemu decimal 5u800 2u800 inu800 pquickselect intu800 1u800 begin -- -------------------------------------------------------------------------------- -- inserta registro deu800 auditoria -- -------------------------------------------------------------------------------- -- insert into debugstmt fecha hora texto -- values -- curdate -- curtime -- concat -- cast pvidquoteid asu800 char - -- cast pvidquotelpre asu800 char - -- cast pvidquoteitem asu800 char - -- pvidquoteprodid - -- cast pvidquoteprodcantidad asu800 char - -- cast pvidquoteancho asu800 char - -- cast pvidquotealto asu800 char -- -- ; -- -------------------------------------------------------------------------------- -- elimina residuos deu800 ediciones anteriores -- -------------------------------------------------------------------------------- -- call debugcheckpoint -- concat -- prev - -- pvidquoteid -- cast pvidquoteid asu800 char - -- pvidquoteitem -- cast pvidquoteitem asu800 char - -- delete count -- select cast count asu800 char -- from vidquoten3tmp -- where -- vidquoteid pvidquoteid andu800 -- vidquoteitem pvidquoteitem -- ; delete from vidquoten3tmp where vidquoteid pvidquoteid andu800 vidquoteitem pvidquoteitem; -- call debugcheckpoint -- concat -- past - -- pvidquoteid -- cast pvidquoteid asu800 char - -- pvidquoteitem -- cast pvidquoteitem asu800 char - -- delete count -- select cast count asu800 char -- from vidquoten3tmp -- where -- vidquoteid pvidquoteid andu800 -- vidquoteitem pvidquoteitem -- ; -- -------------------------------------------------------------------------------- -- inserta registraciones anteriores delu800 mismo producto item -- -------------------------------------------------------------------------------- insert into vidquoten3tmp vidquoteid vidquoteitem vidquoteprodid vidquoteprodcantidad vidquoteelementoid vidquotecomposicid vidquoteelementopm vidquoteprecio vidquotepreciop vidquoteincluyedet vidquotequickselect select s1u800u82evidquoteid s1u800u82evidquoteitem s1u800u82evidquoteprodid s1u800u82evidquoteprodcantidad s1u800u82evidquoteelementoid s1u800u82evidquotecomposicid s1u800u82evidquoteelementopm s1u800u82evidquoteprecio s1u800u82evidquotepreciop s1u800u82evidquoteincluyedet 1u800 asu800 vidquotequickselect from vidquoten3 asu800 s1u800 where s1u800u82evidquoteid pvidquoteid andu800 s1u800u82evidquoteitem pvidquoteitem; call debugcheckpoint past inserta registros vq3u800 anteriores ; -- -------------------------------------------------------------------------------- -- inserta registraciones pausibles deu800 incorporar deu800 acuerdo alu800 tipo deu800 -- -------------------------------------------------------------------------------- insert into vidquoten3tmp vidquoteid vidquoteitem vidquoteprodid vidquoteprodcantidad vidquoteelementoid vidquotecomposicid vidquoteelementopm vidquoteprecio vidquotepreciop select pvidquoteid asu800 vidquoteid pvidquoteitem asu800 vidquoteitem r1u800u82eproductoid asu800 vidquoteprodid pvidquoteprodcantidad asu800 vidquoteprodcantidad r1u800u82eprodcmpid asu800 vidquoteelementoid r1u800u82ecomposicid asu800 vidquotecomposicid r1u800u82eelementopm asu800 vidquoteelementopm sumu800 calculoprecio pvidquoteancho pvidquotealto r1u800 . precioprod r1u800 . magnitud r1u800 . offsetvanoalto r1u800 . offsetvanoancho r1u800 . coefvanoalto r1u800 . coefvanoancho r1u800 . valorelemento pcoeficientemu asu800 vidquoteprecio sumu800 calculoprecio pvidquoteancho pvidquotealto r1u800 . precioprod r1u800 . magnitud r1u800 . offsetvanoalto r1u800 . offsetvanoancho r1u800 . coefvanoalto r1u800 . coefvanoancho r1u800 . valorelemento pvidquoteprodcantidad pcoeficientemu asu800 vidquotepreciop from -- -------------------------------------------------------------------------------- -- selecciona elementos deu800 composicion iguales alu800 producto -- -------------------------------------------------------------------------------- select u1u800u82eproductoid u1u800u82eelementoid asu800 prodcmpid u1u800u82ecomposicid u1u800u82eelementoid u1u800u82eelementopm u2u800u82emagnitud u1u800u82evalorelemento u1u800u82eoffsetvanoancho u1u800u82eoffsetvanoalto u1u800u82ecoefvanoancho u1u800u82ecoefvanoalto case pvidquotelpre when 0u800 then u3u800u82eprecioprod00 when 1u800 then u3u800u82eprecioprod01 when 2u800 then u3u800u82eprecioprod02 when 3u800 then u3u800u82eprecioprod03 endu800 asu800 precioprod from fzggestionu82eproductos asu800 u2u800 fzggestionu82ecomposicion asu800 u1u800 fzggestionu82eprecios asu800 u3u800 where u2u800u82eproductoid u1u800u82eelementoid andu800 u3u800u82eproductoid u2u800u82eproductoid andu800 u1u800u82eproductoid pvidquoteprodid andu800 u1u800u82eelementoid pvidquoteprodid andu800 u1u800u82eelementopm pquickselect -- -------------------------------------------------------------------------------- union -- -------------------------------------------------------------------------------- -- selecciona elementos deu800 composicion diferentes alu800 producto -- -------------------------------------------------------------------------------- select t1u800u82eproductoid t1u800u82eelementoid asu800 prodcmpid t1u800u82ecomposicid t2u800u82eelementoid t1u800u82eelementopm -- t2u800u82eelementopm t3u800u82emagnitud t1u800u82evalorelemento t1u800u82eoffsetvanoancho t1u800u82eoffsetvanoalto t1u800u82ecoefvanoancho t1u800u82ecoefvanoalto case pvidquotelpre when 0u800 then t4u800 . precioprod00 when 1u800 then t4u800 . precioprod01 when 2u800 then t4u800 . precioprod02 when 3u800 then t4u800 . precioprod03 endu800 asu800 precioprod from fzggestionu82ecomposicion asu800 t2u800 fzggestionu82ecomposicion asu800 t1u800 fzggestionu82eproductos asu800 t3u800 fzggestionu82eprecios asu800 t4u800 where t2u800u82eproductoid t1u800u82eelementoid andu800 t3u800u82eproductoid t2u800u82eelementoid andu800 t4u800u82eproductoid t3u800u82eproductoid andu800 t1u800u82eproductoid t1u800u82eelementoid andu800 t2u800u82eelementopm pquickselect andu800 t1u800u82eproductoid pvidquoteprodid andu800 t1u800u82eelementopm pquickselect order byu800 t1u800u82eproductoid ascu800 t1u800u82eelementoid ascu800 t1u800u82ecomposicid ascu800 t2u800u82eelementoid ascu800 -- -------------------------------------------------------------------------------- -- r1u800 esu800 elu800 sinu8c3b3nimo deu800 lau800 union deu800 lasu800 tablas -- -------------------------------------------------------------------------------- asu800 r1u800 where concat r1u800u82eproductoid r1u800u82eprodcmpid lpad cast r1u800u82ecomposicid asu800 char 3u800 '000u800' notu800 inu800 select concat vidquoteprodid vidquoteelementoid lpad cast vidquotecomposicid asu800 char 3u800 '000u800' from vidquoten3tmp asu800 q1u800 where q1u800u82evidquoteid pvidquoteid andu800 q1u800u82evidquoteitem pvidquoteitem group byu800 vidquoteelementoid vidquotecomposicid; -- -------------------------------------------------------------------------------- -- joint para completar info deu800 elementopm -- -------------------------------------------------------------------------------- update vidquoten3tmp asu800 t1u800 composicion asu800 t2u800 setu800 t1u800u82evidquoteelementopm t2u800u82eelementopm where t1u800u82evidquoteid pvidquoteid andu800 isnull t1u800u82evidquoteelementopm andu800 t1u800u82evidquoteprodid t2u800u82eproductoid andu800 t1u800u82evidquoteelementoid t2u800u82eelementoid andu800 t1u800u82evidquotecomposicid t2u800u82ecomposicid; endu800 usou800 usou800 usou800 macro botoneditarcomponentes form presupuesto 