Contents
En comparación con los esquemas estándares de migración a mano, ofrecemos una potente gama de herramientas desarrolladas de probada eficacia en complejos módulos de bases de datos relacionales. Estas herramientas y nuestros especialistas pueden asegurar que las transiciones de las bases de datos se realicen perfectamente, independientemente de la naturaleza del sistema.
Desde la experiencia, estamos familiarizados con la complejidad, el coste que supone una larga migración de bases de datos y los problemas que aparecen durante el proceso cuando se emplean métodos inapropiados; ya que siempre comprobamos con los clientes potenciales que el uso de nuestras herramientas y métodos pueda ofrecer una ventaja significativa.
Herramientas de migración
En comparación con la consultoría estándar de migraciones, la cual puede ofrecer poco más que soporte a la base de datos, nosotros tenemos gran experiencia en escribir grandes aplicaciones para empresas en sintaxis de la base de datos nativa y cross. Además, enseñamos a los equipos de las empresas una metodología y les proporcionamos una potente gama de herramientas para reducir costes y optimizar el proceso de migración.
Estas herramientas incluyen:
- Herramienta de copia multi-bases de datos con conversión automática desde los tipos de datos (incluyendo tipos de datos geométricos)
- Comprobación del esquema multi-base de datos
- Grámatica SQL XML
- Gramática DDL XML
- Gramática DML XML
- Gramática SPL XML
- Gramática Triggers XML
- Soporte para la conversión de tipos de datos geométricos
Copia multi-base de datos
La herramienta de copia puede replicar todos los datos desde una base de datos a una destinación, independientemente del motor, las tablas creadas, los índices, las restricciones y el mapeo de tipos de datos cuando los motores difieren. Con poco esfuerzo, y después del tiempo que supone copiar los datos, se puede ver y explorar los datos en la nueva base de datos. Por supuesto, no se realiza una migración en estos casos.
- Genera estructuras de tablas acorde con los tipos de datos objetivo
- Desactiva automáticamente triggers y secuencias durante el proceso de copia
- Instala automáticamente la secuencia asociada después de copiar una tabla
- Soporta la generación de bases de datos cruzadas rowid
- Soporta la conversión de tipos de datos geométricos permitiendo una fácil migración de motores espaciales
- Soporta la construcción de índices post-copia y foreign keys
- Soporta la compilación de triggers post-copia y SPL
Comprobación del esquema multi-bases de datos
Una vez se empieza una migración, se puede generar un esquema XML desde la base de datos original. Esto permite traducir el modelo de base de datos a cualquier motor.
Sin embargo, ¿qué pasa si el sistema continúa operando e incluso sufre cambios estructurales durante el proceso de migración? La comprobación del esquema compara las bases de datos de tipos diferentes y muestra las diferencia entre estructuras de tablas, claves primarias, foreign keys, índices y restricciones. También, se puede hacer una comparación con el modelo de esquema maestro en XML. En ambos casos, se aplicará una propuesta de cambios para asegurar que se muestra la misma estructura física.
Soporte al desarrollo, test, pre-producción y producción
Las herramientas de migración están construidas alrededor de un diccionario de base de datos. El diccionario permite a los programadores almacenar su código (sentencias DML, queries SQL, código SPL, datos de tablas iniciales, etc.), el cual constituye la base de datos de las aplicaciones. Una vez almacenado en el diccionario, un grupo de comandos web o comandos shell permite la compilación, el chequeo o la salida de nuevas actualizaciones para una base de datos o un grupo.
Sintaxis XML-XSQL
El motor de traducción de triggers DDL, DML, SPL proporciona una estructura con una sintaxis común XML, en la cual los desarrolladores pueden escribir aplicaciones en una sintaxis independiente de la propia del motor de base de datos.
XML-XSQL syntax available
Informix | Postgres | Oracle | SQLServer | Db2u | Db2i | |
---|---|---|---|---|---|---|
rowid | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
alias | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
length | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
trim | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
substr | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
decode | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
matches | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
regexp | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
exists | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
nvl | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
whitespace | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
cast | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
min | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
max | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
char | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
date | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
datetime | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
user | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
today | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
current | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
lastday | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
weekday | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
days | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
year | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
month | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
hour | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
minute | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
second | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
seconds | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
mdy | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
extend | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
eval-date | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
eval-datetime | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
seconds-todsinterval | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
deferred-constraint | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
select | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
union | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
columns | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
from | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
where | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
join | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
on | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
order | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
group | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
having | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
insert | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
update | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
delete | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
drop | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
index | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
update-statistics | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
set-triggers | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
set-indexes | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
set-constraints | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
call | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
function | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
if | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
if-exists | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
conditional | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
while | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
for | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
exit | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
continue | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
foreach | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
do | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
set | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
lock | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
unlock | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
debug | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
sqlca.serial | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
sqlca.rowcount | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
exception | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
return | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
into | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
execute-function | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
execute-procedure | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
trigger | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
xsql-trigger | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
before | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
after | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
set | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
foreach-row | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
expr-select | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
expr-into | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
when | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
prv | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
nxt | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
expr | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
then | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
try | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
prv | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
for | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
conditional | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
prv | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
for | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
DDL
El proceso de copia de una base de datos puede crear automáticamente un modelo XML que genera el Data Definition Language (DDL) de la base de datos. Se pueden ver todas las tablas y objetos definidos en una definición natural XML que permitirá la traducción on-line a la base de datos deseada.
DDL – XML transformation sample
<table name='galmacen'> <!-- COLUMNS --> <column name='almid' type='serial' required='y' /> <column name='codigo' type='char' size='6' required='y' /> <column name='nomalm' type='varchar' size='40' required='y' /> <column name='tercer' type='char' size='12' required='y' /> <column name='tipdir' type='char' size='6' default='0' required='y' /> <column name='empcode' type='char' size='4' required='y' /> <column name='agrupa' type='char' size='4' required='y' /> <column name='recint' type='char' size='6' required='y' /> <column name='delega' type='char' size='6' /> <column name='depart' type='char' size='6' /> <column name='codpre' type='char' size='10' /><!-- Partida presupuestaria. el almacen se ha creado para suministrar un proyecto --> <column name='stkneg' type='smallint' default='0' required='y' /> <column name='stkrup' type='char' size='1' default='N' required='y' /> <column name='tipval' type='char' size='1' required='y' /> <column name='cosuni' type='char' size='1' default='N' required='y' /> <column name='costol' type='smallint' default='0' required='y' /> <column name='fecval' type='date' required='y' /> <column name='aplven' type='smallint' default='0' required='y' /> <column name='indlot' type='smallint' default='1' required='y' /> <column name='disdel' type='smallint' default='0' required='y' /> <column name='reflec' type='int' /> <column name='capdes' type='decimal' size='11,3' default='0.000' required='y' /> <column name='horini' type='smallint' default='0' required='y' /> <column name='horfin' type='smallint' default='0' required='y' /> <column name='tippro' type='char' size='4' /> <column name='impre1' type='char' size='10' /> <column name='impre2' type='char' size='10' /> <column name='tipast' type='char' size='4' /> <column name='fecalt' type='date' default='today' required='y' /> <column name='fecbaj' type='date' /> <column name='fecult' type='date' required='y' /> <column name='feccon' type='date' /> <column name='inipot' type='smallint' /> <column name='estado' type='char' size='1' required='y' /> <column name='errnum' type='smallint' default='0' required='y' /> <column name='wkfnum' type='smallint' /> <column name='auxchr1' type='char' size='1' /> <column name='auxchr2' type='char' size='1' /> <column name='auxchr3' type='char' size='1' /> <column name='auxchr4' type='char' size='1' /> <column name='auxchr5' type='char' size='1' /> <column name='auxfec1' type='date' /> <column name='auxfec2' type='date' /> <column name='auxnum1' type='smallint' /> <column name='auxnum2' type='smallint' /> <column name='auxnum3' type='smallint' /> <column name='user_created' type='char' size='20' default='user' required='y' /> <column name='date_created' type='datetime' size='year to second' default='current' required='y' /> <column name='user_updated' type='char' size='20' default='user' required='y' /> <column name='date_updated' type='datetime' size='year to second' default='current' required='y' /> <!-- INDEXES --> <!-- f_galmacen1 == foreign key --> <!-- f_galmacen2 == foreign key --> <!-- f_galmacen3 == foreign key --> <!-- f_galmacen4 == foreign key --> <!-- f_galmacen5 == foreign key --> <!-- f_galmacen6 == foreign key --> <!-- f_galmacen7 == foreign key --> <primary name='p_galmacen' columns='codigo' /> <unique name='u_galmacen1' columns='almid' /> <!-- FOREIGN KEYS --> <foreign name='f_galmacen1' columns='tercer' references='ctercero' refcols='codigo' /> <foreign name='f_galmacen2' columns='tipast' references='gconasih' refcols='codigo' /> <foreign name='f_galmacen3' columns='agrupa' references='gagrupac' refcols='codigo' /> <foreign name='f_galmacen4' columns='delega,depart' references='gdeparta' refcols='delega,depart' /> <foreign name='f_galmacen5' unique='yes' columns='tercer,tipdir' references='cterdire' refcols='codigo,tipdir' /> <foreign name='f_galmacen6' columns='recint' references='galmreci' refcols='codigo' /> <foreign name='f_galmacen7' columns='empcode' references='cempresa' refcols='empcode' /> <foreign name='f_galmacen8' columns='tippro' references='gmovprod' refcols='codigo' /> <!-- CHECKS --> <check name='c_galmacen1'> <constraint><![CDATA[ (tipval IN ('F', 'L', 'M', 'N')) ]]> </constraint> </check> <check name='c_galmacen2'> <constraint><![CDATA[ (cosuni IN ('S', 'N')) ]]> </constraint> </check> <check name='c_galmacen3'> <constraint><![CDATA[ (feccon IS NULL OR feccon <= fecval) ]]> </constraint> </check> <check name='c_galmacen4'> <constraint><![CDATA[ (feccon IS NULL OR (feccon IS NOT NULL AND tipast IS NOT NULL)) ]]> </constraint> </check> <check name='c_galmacen5'> <constraint><![CDATA[ (fecbaj IS NULL OR fecalt <= fecbaj) ]]> </constraint> </check> <check name='c_galmacen6'> <constraint><![CDATA[ (horini <= horfin) ]]> </constraint> </check> <check name='c_galmacen7'> <constraint><![CDATA[ ((horini BETWEEN 0 AND 23) AND (horfin BETWEEN 0 AND 23)) ]]> </constraint> </check> <check name='c_galmacen8'> <constraint><![CDATA[ (costol = 0 OR (costol > 0 AND tipval = 'M')) ]]> </constraint> </check> <check name='c_galmacen9'> <constraint><![CDATA[ errnum = 0 OR (errnum != 0 AND estado = 'B') ]]> </constraint> </check> <check name='c_galmacen10'> <constraint><![CDATA[ wkfnum IS NULL OR (wkfnum IS NOT NULL AND errnum != 0) ]]> </constraint> </check> <check name='c_galmacen11'> <constraint><![CDATA[ stkrup IN ('N', 'G', 'Z') ]]> </constraint> </check> </table>
CREATE TABLE galmacen ( almid serial not null, codigo char(6) not null, nomalm varchar(40) not null, tercer char(12) not null, tipdir char(6) default '0' not null, empcode char(4) not null, agrupa char(4) not null, recint char(6) not null, delega char(6), depart char(6), codpre char(10), stkneg smallint default 0 not null, stkrup char(1) default 'N' not null, tipval char(1) not null, cosuni char(1) default 'N' not null, costol smallint default 0 not null, fecval date not null, aplven smallint default 0 not null, indlot smallint default 1 not null, disdel smallint default 0 not null, reflec int, capdes decimal(11,3) default 0.000 not null, horini smallint default 0 not null, horfin smallint default 0 not null, tippro char(4), impre1 char(10), impre2 char(10), tipast char(4), fecalt date default today not null, fecbaj date, fecult date not null, feccon date, inipot smallint, estado char(1) not null, errnum smallint default 0 not null, wkfnum smallint, auxchr1 char(1), auxchr2 char(1), auxchr3 char(1), auxchr4 char(1), auxchr5 char(1), auxfec1 date, auxfec2 date, auxnum1 smallint, auxnum2 smallint, auxnum3 smallint, user_created char(20) default user not null, date_created datetime year to second default current year to second not null, user_updated char(20) default user not null, date_updated datetime year to second default current year to second not null ,CHECK ( (tipval IN ('F', 'L', 'M', 'N')) ) CONSTRAINT c_galmacen1 ,CHECK ( (cosuni IN ('S', 'N')) ) CONSTRAINT c_galmacen2 ,CHECK ( (feccon IS NULL OR feccon <= fecval) ) CONSTRAINT c_galmacen3 ,CHECK ( (feccon IS NULL OR (feccon IS NOT NULL AND tipast IS NOT NULL)) ) CONSTRAINT c_galmacen4 ,CHECK ( (fecbaj IS NULL OR fecalt <= fecbaj) ) CONSTRAINT c_galmacen5 ,CHECK ( (horini <= horfin) ) CONSTRAINT c_galmacen6 ,CHECK ( ((horini BETWEEN 0 AND 23) AND (horfin BETWEEN 0 AND 23)) ) CONSTRAINT c_galmacen7 ,CHECK ( (costol = 0 OR (costol > 0 AND tipval = 'M')) ) CONSTRAINT c_galmacen8 ,CHECK ( errnum = 0 OR (errnum != 0 AND estado = 'B') ) CONSTRAINT c_galmacen9 ,CHECK ( wkfnum IS NULL OR (wkfnum IS NOT NULL AND errnum != 0) ) CONSTRAINT c_galmacen10 ,CHECK ( stkrup IN ('N', 'G', 'Z') ) CONSTRAINT c_galmacen11 ); ALTER TABLE galmacen LOCK MODE (ROW); CREATE UNIQUE INDEX u_galmacen1 ON galmacen(almid); ALTER TABLE galmacen ADD CONSTRAINT UNIQUE (almid) CONSTRAINT u_galmacen1; -- Index to avoid automatic named by database (named with pk name). CREATE UNIQUE INDEX p_galmacen ON galmacen(codigo); ALTER TABLE galmacen ADD CONSTRAINT PRIMARY KEY (codigo) CONSTRAINT p_galmacen; CREATE INDEX f_galmacen1 ON galmacen(tercer); ALTER TABLE galmacen ADD CONSTRAINT FOREIGN KEY (tercer) REFERENCES ctercero(codigo) CONSTRAINT f_galmacen1; CREATE INDEX f_galmacen2 ON galmacen(tipast); ALTER TABLE galmacen ADD CONSTRAINT FOREIGN KEY (tipast) REFERENCES gconasih(codigo) CONSTRAINT f_galmacen2; CREATE INDEX f_galmacen3 ON galmacen(agrupa); ALTER TABLE galmacen ADD CONSTRAINT FOREIGN KEY (agrupa) REFERENCES gagrupac(codigo) CONSTRAINT f_galmacen3; CREATE INDEX f_galmacen4 ON galmacen(delega,depart); ALTER TABLE galmacen ADD CONSTRAINT FOREIGN KEY (delega,depart) REFERENCES gdeparta(delega,depart) CONSTRAINT f_galmacen4; CREATE UNIQUE INDEX f_galmacen5 ON galmacen(tercer,tipdir); ALTER TABLE galmacen ADD CONSTRAINT FOREIGN KEY (tercer,tipdir) REFERENCES cterdire(codigo,tipdir) CONSTRAINT f_galmacen5; CREATE INDEX f_galmacen6 ON galmacen(recint); ALTER TABLE galmacen ADD CONSTRAINT FOREIGN KEY (recint) REFERENCES galmreci(codigo) CONSTRAINT f_galmacen6; CREATE INDEX f_galmacen7 ON galmacen(empcode); ALTER TABLE galmacen ADD CONSTRAINT FOREIGN KEY (empcode) REFERENCES cempresa(empcode) CONSTRAINT f_galmacen7; CREATE INDEX f_galmacen8 ON galmacen(tippro); ALTER TABLE galmacen ADD CONSTRAINT FOREIGN KEY (tippro) REFERENCES gmovprod(codigo) CONSTRAINT f_galmacen8;
CREATE TABLE galmacen ( almid number(10) default 0 not null, codigo varchar2(6 CHAR) not null, nomalm varchar2(40 CHAR) not null, tercer varchar2(12 CHAR) not null, tipdir varchar2(6 CHAR) default '0' not null, empcode varchar2(4 CHAR) not null, agrupa varchar2(4 CHAR) not null, recint varchar2(6 CHAR) not null, delega varchar2(6 CHAR), depart varchar2(6 CHAR), codpre varchar2(10 CHAR), stkneg number(5) default 0 not null, stkrup varchar2(1 CHAR) default 'N' not null, tipval varchar2(1 CHAR) not null, cosuni varchar2(1 CHAR) default 'N' not null, costol number(5) default 0 not null, fecval date not null, aplven number(5) default 0 not null, indlot number(5) default 1 not null, disdel number(5) default 0 not null, reflec number(10), capdes number(11,3) default 0.000 not null, horini number(5) default 0 not null, horfin number(5) default 0 not null, tippro varchar2(4 CHAR), impre1 varchar2(10 CHAR), impre2 varchar2(10 CHAR), tipast varchar2(4 CHAR), fecalt date default CURRENT_DATE not null, fecbaj date, fecult date not null, feccon date, inipot number(5), estado varchar2(1 CHAR) not null, errnum number(5) default 0 not null, wkfnum number(5), auxchr1 varchar2(1 CHAR), auxchr2 varchar2(1 CHAR), auxchr3 varchar2(1 CHAR), auxchr4 varchar2(1 CHAR), auxchr5 varchar2(1 CHAR), auxfec1 date, auxfec2 date, auxnum1 number(5), auxnum2 number(5), auxnum3 number(5), user_created varchar2(20 CHAR) default user not null, date_created timestamp default CURRENT_TIMESTAMP not null, user_updated varchar2(20 CHAR) default user not null, date_updated timestamp default CURRENT_TIMESTAMP not null , CONSTRAINT c_galmacen1 CHECK ( (tipval IN ('F', 'L', 'M', 'N')) ) DEFERRABLE , CONSTRAINT c_galmacen2 CHECK ( (cosuni IN ('S', 'N')) ) DEFERRABLE , CONSTRAINT c_galmacen3 CHECK ( (feccon IS NULL OR feccon <= fecval) ) DEFERRABLE , CONSTRAINT c_galmacen4 CHECK ( (feccon IS NULL OR (feccon IS NOT NULL AND tipast IS NOT NULL)) ) DEFERRABLE , CONSTRAINT c_galmacen5 CHECK ( (fecbaj IS NULL OR fecalt <= fecbaj) ) DEFERRABLE , CONSTRAINT c_galmacen6 CHECK ( (horini <= horfin) ) DEFERRABLE , CONSTRAINT c_galmacen7 CHECK ( ((horini BETWEEN 0 AND 23) AND (horfin BETWEEN 0 AND 23)) ) DEFERRABLE , CONSTRAINT c_galmacen8 CHECK ( (costol = 0 OR (costol > 0 AND tipval = 'M')) ) DEFERRABLE , CONSTRAINT c_galmacen9 CHECK ( errnum = 0 OR (errnum != 0 AND estado = 'B') ) DEFERRABLE , CONSTRAINT c_galmacen10 CHECK ( wkfnum IS NULL OR (wkfnum IS NOT NULL AND errnum != 0) ) DEFERRABLE , CONSTRAINT c_galmacen11 CHECK ( stkrup IN ('N', 'G', 'Z') ) DEFERRABLE ); -- Sequence for serial column. Tabname CRC: 31e1e7a9 create sequence seq_31e1e7a9_almid start with 1 increment by 1; -- Trigger for sequence. Automatically increases nextvalue of sequence. create or replace trigger galmacen_ins_seq before insert on galmacen for each row declare v_newval number(12) := 0; v_incval number(12) := 0; begin if (:new.almid = 0) or (:new.almid is null) then select seq_31e1e7a9_almid.nextval into v_newval from dual; else v_newval := :new.almid; v_incval := 1; loop exit when v_incval >= v_newval; select seq_31e1e7a9_almid.nextval into v_incval from dual; end loop; end if; :new.almid := v_newval; end; . run CREATE INDEX u_galmacen1 ON galmacen(almid); ALTER TABLE galmacen ADD CONSTRAINT u_galmacen1 UNIQUE (almid) DEFERRABLE ; CREATE INDEX p_galmacen ON galmacen(codigo); ALTER TABLE galmacen ADD CONSTRAINT p_galmacen PRIMARY KEY (codigo) DEFERRABLE ; CREATE INDEX f_galmacen1 ON galmacen(tercer); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen1 FOREIGN KEY (tercer) REFERENCES ctercero(codigo) DEFERRABLE ; CREATE INDEX f_galmacen2 ON galmacen(tipast); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen2 FOREIGN KEY (tipast) REFERENCES gconasih(codigo) DEFERRABLE ; CREATE INDEX f_galmacen3 ON galmacen(agrupa); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen3 FOREIGN KEY (agrupa) REFERENCES gagrupac(codigo) DEFERRABLE ; CREATE INDEX f_galmacen4 ON galmacen(delega,depart); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen4 FOREIGN KEY (delega,depart) REFERENCES gdeparta(delega,depart) DEFERRABLE ; CREATE UNIQUE INDEX f_galmacen5 ON galmacen(tercer,tipdir); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen5 FOREIGN KEY (tercer,tipdir) REFERENCES cterdire(codigo,tipdir) DEFERRABLE ; CREATE INDEX f_galmacen6 ON galmacen(recint); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen6 FOREIGN KEY (recint) REFERENCES galmreci(codigo) DEFERRABLE ; CREATE INDEX f_galmacen7 ON galmacen(empcode); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen7 FOREIGN KEY (empcode) REFERENCES cempresa(empcode) DEFERRABLE ; CREATE INDEX f_galmacen8 ON galmacen(tippro); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen8 FOREIGN KEY (tippro) REFERENCES gmovprod(codigo) DEFERRABLE ;
CREATE TABLE galmacen ( almid serial not null, codigo varchar(6) not null, nomalm varchar(40) not null, tercer varchar(12) not null, tipdir varchar(6) default '0' not null, empcode varchar(4) not null, agrupa varchar(4) not null, recint varchar(6) not null, delega varchar(6), depart varchar(6), codpre varchar(10), stkneg smallint default 0 not null, stkrup varchar(1) default 'N' not null, tipval varchar(1) not null, cosuni varchar(1) default 'N' not null, costol smallint default 0 not null, fecval date not null, aplven smallint default 0 not null, indlot smallint default 1 not null, disdel smallint default 0 not null, reflec int, capdes decimal(11,3) default 0.000 not null, horini smallint default 0 not null, horfin smallint default 0 not null, tippro varchar(4), impre1 varchar(10), impre2 varchar(10), tipast varchar(4), fecalt date default CURRENT_DATE not null, fecbaj date, fecult date not null, feccon date, inipot smallint, estado varchar(1) not null, errnum smallint default 0 not null, wkfnum smallint, auxchr1 varchar(1), auxchr2 varchar(1), auxchr3 varchar(1), auxchr4 varchar(1), auxchr5 varchar(1), auxfec1 date, auxfec2 date, auxnum1 smallint, auxnum2 smallint, auxnum3 smallint, user_created varchar(20) default user not null, date_created timestamp default CURRENT_TIMESTAMP not null, user_updated varchar(20) default user not null, date_updated timestamp default CURRENT_TIMESTAMP not null , CONSTRAINT c_galmacen1 CHECK ( (tipval IN ('F', 'L', 'M', 'N')) ) , CONSTRAINT c_galmacen2 CHECK ( (cosuni IN ('S', 'N')) ) , CONSTRAINT c_galmacen3 CHECK ( (feccon IS NULL OR feccon <= fecval) ) , CONSTRAINT c_galmacen4 CHECK ( (feccon IS NULL OR (feccon IS NOT NULL AND tipast IS NOT NULL)) ) , CONSTRAINT c_galmacen5 CHECK ( (fecbaj IS NULL OR fecalt <= fecbaj) ) , CONSTRAINT c_galmacen6 CHECK ( (horini <= horfin) ) , CONSTRAINT c_galmacen7 CHECK ( ((horini BETWEEN 0 AND 23) AND (horfin BETWEEN 0 AND 23)) ) , CONSTRAINT c_galmacen8 CHECK ( (costol = 0 OR (costol > 0 AND tipval = 'M')) ) , CONSTRAINT c_galmacen9 CHECK ( errnum = 0 OR (errnum != 0 AND estado = 'B') ) , CONSTRAINT c_galmacen10 CHECK ( wkfnum IS NULL OR (wkfnum IS NOT NULL AND errnum != 0) ) , CONSTRAINT c_galmacen11 CHECK ( stkrup IN ('N', 'G', 'Z') ) ) WITH OIDS; ALTER TABLE galmacen ADD CONSTRAINT u_galmacen1 UNIQUE (almid); -- Database Server on Postgres, DB2 will be created a index automatically with the same name of pk ALTER TABLE galmacen ADD CONSTRAINT p_galmacen PRIMARY KEY (codigo); CREATE INDEX f_galmacen1 ON galmacen(tercer); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen1 FOREIGN KEY (tercer) REFERENCES ctercero(codigo); CREATE INDEX f_galmacen2 ON galmacen(tipast); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen2 FOREIGN KEY (tipast) REFERENCES gconasih(codigo); CREATE INDEX f_galmacen3 ON galmacen(agrupa); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen3 FOREIGN KEY (agrupa) REFERENCES gagrupac(codigo); CREATE INDEX f_galmacen4 ON galmacen(delega,depart); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen4 FOREIGN KEY (delega,depart) REFERENCES gdeparta(delega,depart); CREATE UNIQUE INDEX f_galmacen5 ON galmacen(tercer,tipdir); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen5 FOREIGN KEY (tercer,tipdir) REFERENCES cterdire(codigo,tipdir); CREATE INDEX f_galmacen6 ON galmacen(recint); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen6 FOREIGN KEY (recint) REFERENCES galmreci(codigo); CREATE INDEX f_galmacen7 ON galmacen(empcode); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen7 FOREIGN KEY (empcode) REFERENCES cempresa(empcode); CREATE INDEX f_galmacen8 ON galmacen(tippro); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen8 FOREIGN KEY (tippro) REFERENCES gmovprod(codigo);
CREATE TABLE galmacen ( almid integer generated by default as identity not null, codigo char(6) not null, nomalm varchar(40) not null, tercer char(12) not null, tipdir char(6) default '0' not null, empcode char(4) not null, agrupa char(4) not null, recint char(6) not null, delega char(6), depart char(6), codpre char(10), stkneg smallint default 0 not null, stkrup char(1) default 'N' not null, tipval char(1) not null, cosuni char(1) default 'N' not null, costol smallint default 0 not null, fecval date not null, aplven smallint default 0 not null, indlot smallint default 1 not null, disdel smallint default 0 not null, reflec integer, capdes decimal(11,3) default 0.000 not null, horini smallint default 0 not null, horfin smallint default 0 not null, tippro char(4), impre1 char(10), impre2 char(10), tipast char(4), fecalt date default CURRENT DATE not null, fecbaj date, fecult date not null, feccon date, inipot smallint, estado char(1) not null, errnum smallint default 0 not null, wkfnum smallint, auxchr1 char(1), auxchr2 char(1), auxchr3 char(1), auxchr4 char(1), auxchr5 char(1), auxfec1 date, auxfec2 date, auxnum1 smallint, auxnum2 smallint, auxnum3 smallint, user_created varchar(128) default user not null, date_created timestamp default CURRENT TIMESTAMP not null, user_updated varchar(128) default user not null, date_updated timestamp default CURRENT TIMESTAMP not null ,rowid rowid implicitly hidden , CONSTRAINT c_galmacen1 CHECK ( (tipval IN ('F', 'L', 'M', 'N')) ) , CONSTRAINT c_galmacen2 CHECK ( (cosuni IN ('S', 'N')) ) , CONSTRAINT c_galmacen3 CHECK ( (feccon IS NULL OR feccon <= fecval) ) , CONSTRAINT c_galmacen4 CHECK ( (feccon IS NULL OR (feccon IS NOT NULL AND tipast IS NOT NULL)) ) , CONSTRAINT c_galmacen5 CHECK ( (fecbaj IS NULL OR fecalt <= fecbaj) ) , CONSTRAINT c_galmacen6 CHECK ( (horini <= horfin) ) , CONSTRAINT c_galmacen7 CHECK ( ((horini BETWEEN 0 AND 23) AND (horfin BETWEEN 0 AND 23)) ) , CONSTRAINT c_galmacen8 CHECK ( (costol = 0 OR (costol > 0 AND tipval = 'M')) ) , CONSTRAINT c_galmacen9 CHECK ( errnum = 0 OR (errnum != 0 AND estado = 'B') ) , CONSTRAINT c_galmacen10 CHECK ( wkfnum IS NULL OR (wkfnum IS NOT NULL AND errnum != 0) ) , CONSTRAINT c_galmacen11 CHECK ( stkrup IN ('N', 'G', 'Z') ) ); ALTER TABLE galmacen ADD CONSTRAINT u_galmacen1 UNIQUE (almid); -- Database Server on Postgres, DB2 will be created a index automatically with the same name of pk ALTER TABLE galmacen ADD CONSTRAINT p_galmacen PRIMARY KEY (codigo); CREATE INDEX f_galmacen1 ON galmacen(tercer); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen1 FOREIGN KEY (tercer) REFERENCES ctercero(codigo); CREATE INDEX f_galmacen2 ON galmacen(tipast); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen2 FOREIGN KEY (tipast) REFERENCES gconasih(codigo); CREATE INDEX f_galmacen3 ON galmacen(agrupa); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen3 FOREIGN KEY (agrupa) REFERENCES gagrupac(codigo); CREATE INDEX f_galmacen4 ON galmacen(delega,depart); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen4 FOREIGN KEY (delega,depart) REFERENCES gdeparta(delega,depart); CREATE UNIQUE INDEX f_galmacen5 ON galmacen(tercer,tipdir); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen5 FOREIGN KEY (tercer,tipdir) REFERENCES cterdire(codigo,tipdir); CREATE INDEX f_galmacen6 ON galmacen(recint); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen6 FOREIGN KEY (recint) REFERENCES galmreci(codigo); CREATE INDEX f_galmacen7 ON galmacen(empcode); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen7 FOREIGN KEY (empcode) REFERENCES cempresa(empcode); CREATE INDEX f_galmacen8 ON galmacen(tippro); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen8 FOREIGN KEY (tippro) REFERENCES gmovprod(codigo);
CREATE TABLE galmacen ( almid integer not null generated by default as identity(start with 1, increment by 1, no cache), codigo char(6) not null, nomalm varchar(40) not null, tercer char(12) not null, tipdir char(6) default '0' not null, empcode char(4) not null, agrupa char(4) not null, recint char(6) not null, delega char(6), depart char(6), codpre char(10), stkneg smallint default 0 not null, stkrup char(1) default 'N' not null, tipval char(1) not null, cosuni char(1) default 'N' not null, costol smallint default 0 not null, fecval date not null, aplven smallint default 0 not null, indlot smallint default 1 not null, disdel smallint default 0 not null, reflec integer, capdes decimal(11,3) default 0.000 not null, horini smallint default 0 not null, horfin smallint default 0 not null, tippro char(4), impre1 char(10), impre2 char(10), tipast char(4), fecalt date default CURRENT DATE not null, fecbaj date, fecult date not null, feccon date, inipot smallint, estado char(1) not null, errnum smallint default 0 not null, wkfnum smallint, auxchr1 char(1), auxchr2 char(1), auxchr3 char(1), auxchr4 char(1), auxchr5 char(1), auxfec1 date, auxfec2 date, auxnum1 smallint, auxnum2 smallint, auxnum3 smallint, user_created varchar(128) default user not null, date_created timestamp default CURRENT TIMESTAMP not null, user_updated varchar(128) default user not null, date_updated timestamp default CURRENT TIMESTAMP not null ,rowid integer not null generated always as (almid) , CONSTRAINT c_galmacen1 CHECK ( (tipval IN ('F', 'L', 'M', 'N')) ) , CONSTRAINT c_galmacen2 CHECK ( (cosuni IN ('S', 'N')) ) , CONSTRAINT c_galmacen3 CHECK ( (feccon IS NULL OR feccon <= fecval) ) , CONSTRAINT c_galmacen4 CHECK ( (feccon IS NULL OR (feccon IS NOT NULL AND tipast IS NOT NULL)) ) , CONSTRAINT c_galmacen5 CHECK ( (fecbaj IS NULL OR fecalt <= fecbaj) ) , CONSTRAINT c_galmacen6 CHECK ( (horini <= horfin) ) , CONSTRAINT c_galmacen7 CHECK ( ((horini BETWEEN 0 AND 23) AND (horfin BETWEEN 0 AND 23)) ) , CONSTRAINT c_galmacen8 CHECK ( (costol = 0 OR (costol > 0 AND tipval = 'M')) ) , CONSTRAINT c_galmacen9 CHECK ( errnum = 0 OR (errnum != 0 AND estado = 'B') ) , CONSTRAINT c_galmacen10 CHECK ( wkfnum IS NULL OR (wkfnum IS NOT NULL AND errnum != 0) ) , CONSTRAINT c_galmacen11 CHECK ( stkrup IN ('N', 'G', 'Z') ) ); ALTER TABLE galmacen ADD CONSTRAINT u_galmacen1 UNIQUE (almid); -- Database Server on Postgres, DB2 will be created a index automatically with the same name of pk ALTER TABLE galmacen ADD CONSTRAINT p_galmacen PRIMARY KEY (codigo); CREATE INDEX f_galmacen1 ON galmacen(tercer); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen1 FOREIGN KEY (tercer) REFERENCES ctercero(codigo); CREATE INDEX f_galmacen2 ON galmacen(tipast); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen2 FOREIGN KEY (tipast) REFERENCES gconasih(codigo); CREATE INDEX f_galmacen3 ON galmacen(agrupa); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen3 FOREIGN KEY (agrupa) REFERENCES gagrupac(codigo); CREATE INDEX f_galmacen4 ON galmacen(delega,depart); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen4 FOREIGN KEY (delega,depart) REFERENCES gdeparta(delega,depart); CREATE UNIQUE INDEX f_galmacen5 ON galmacen(tercer,tipdir); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen5 FOREIGN KEY (tercer,tipdir) REFERENCES cterdire(codigo,tipdir); CREATE INDEX f_galmacen6 ON galmacen(recint); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen6 FOREIGN KEY (recint) REFERENCES galmreci(codigo); CREATE INDEX f_galmacen7 ON galmacen(empcode); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen7 FOREIGN KEY (empcode) REFERENCES cempresa(empcode); CREATE INDEX f_galmacen8 ON galmacen(tippro); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen8 FOREIGN KEY (tippro) REFERENCES gmovprod(codigo);
CREATE TABLE galmacen ( almid bigint identity(1,1) not null, codigo char(6) not null, nomalm varchar(40) not null, tercer char(12) not null, tipdir char(6) default '0' not null, empcode char(4) not null, agrupa char(4) not null, recint char(6) not null, delega char(6), depart char(6), codpre char(10), stkneg smallint default 0 not null, stkrup char(1) default 'N' not null, tipval char(1) not null, cosuni char(1) default 'N' not null, costol smallint default 0 not null, fecval datetime not null, aplven smallint default 0 not null, indlot smallint default 1 not null, disdel smallint default 0 not null, reflec int, capdes decimal(11,3) default 0.000 not null, horini smallint default 0 not null, horfin smallint default 0 not null, tippro char(4), impre1 char(10), impre2 char(10), tipast char(4), fecalt datetime default GETDATE() not null, fecbaj datetime, fecult datetime not null, feccon datetime, inipot smallint, estado char(1) not null, errnum smallint default 0 not null, wkfnum smallint, auxchr1 char(1), auxchr2 char(1), auxchr3 char(1), auxchr4 char(1), auxchr5 char(1), auxfec1 datetime, auxfec2 datetime, auxnum1 smallint, auxnum2 smallint, auxnum3 smallint, user_created char(20) default user not null, date_created datetime default GETDATE() not null, user_updated char(20) default user not null, date_updated datetime default GETDATE() not null , CONSTRAINT c_galmacen1 CHECK ( (tipval IN ('F', 'L', 'M', 'N')) ) , CONSTRAINT c_galmacen2 CHECK ( (cosuni IN ('S', 'N')) ) , CONSTRAINT c_galmacen3 CHECK ( (feccon IS NULL OR feccon <= fecval) ) , CONSTRAINT c_galmacen4 CHECK ( (feccon IS NULL OR (feccon IS NOT NULL AND tipast IS NOT NULL)) ) , CONSTRAINT c_galmacen5 CHECK ( (fecbaj IS NULL OR fecalt <= fecbaj) ) , CONSTRAINT c_galmacen6 CHECK ( (horini <= horfin) ) , CONSTRAINT c_galmacen7 CHECK ( ((horini BETWEEN 0 AND 23) AND (horfin BETWEEN 0 AND 23)) ) , CONSTRAINT c_galmacen8 CHECK ( (costol = 0 OR (costol > 0 AND tipval = 'M')) ) , CONSTRAINT c_galmacen9 CHECK ( errnum = 0 OR (errnum != 0 AND estado = 'B') ) , CONSTRAINT c_galmacen10 CHECK ( wkfnum IS NULL OR (wkfnum IS NOT NULL AND errnum != 0) ) , CONSTRAINT c_galmacen11 CHECK ( stkrup IN ('N', 'G', 'Z') ) ); ALTER TABLE galmacen ADD CONSTRAINT u_galmacen1 UNIQUE (almid); -- Database Server on Postgres, DB2 will be created a index automatically with the same name of pk ALTER TABLE galmacen ADD CONSTRAINT p_galmacen PRIMARY KEY (codigo); CREATE INDEX f_galmacen1 ON galmacen(tercer); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen1 FOREIGN KEY (tercer) REFERENCES ctercero(codigo); CREATE INDEX f_galmacen2 ON galmacen(tipast); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen2 FOREIGN KEY (tipast) REFERENCES gconasih(codigo); CREATE INDEX f_galmacen3 ON galmacen(agrupa); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen3 FOREIGN KEY (agrupa) REFERENCES gagrupac(codigo); CREATE INDEX f_galmacen4 ON galmacen(delega,depart); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen4 FOREIGN KEY (delega,depart) REFERENCES gdeparta(delega,depart); CREATE UNIQUE INDEX f_galmacen5 ON galmacen(tercer,tipdir); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen5 FOREIGN KEY (tercer,tipdir) REFERENCES cterdire(codigo,tipdir); CREATE INDEX f_galmacen6 ON galmacen(recint); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen6 FOREIGN KEY (recint) REFERENCES galmreci(codigo); CREATE INDEX f_galmacen7 ON galmacen(empcode); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen7 FOREIGN KEY (empcode) REFERENCES cempresa(empcode); CREATE INDEX f_galmacen8 ON galmacen(tippro); ALTER TABLE galmacen ADD CONSTRAINT f_galmacen8 FOREIGN KEY (tippro) REFERENCES gmovprod(codigo);
DML
Una gramática XML permite escribir sentencias SQL independientes de la base de datos.
DML – XML transformation sample
<union type='all' intotemp='@tmp_resultado'> <select> <columns> CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN <substr>codfam, 1, $NIVFAM</substr> WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END <alias name='nivel1'/>, SUM(CASE WHEN anyo = $EJERCI THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) <alias name='valact'/>, 0 <alias name='preact'/>, SUM(CASE WHEN anyo = $EJERCI - 1 THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) <alias name='valant'/> </columns> <from table='gdwh_cdelarta'> <join table='gdelgrpl'> <on>gdwh_cdelarta.delega = gdelgrpl.delgrp</on> </join> <join table='galmgrpl'> <on>gdwh_cdelarta.codalm = galmgrpl.almgrp</on> </join> </from> <where> gdwh_cdelarta.anyo BETWEEN $EJERCI - 1 AND $EJERCI AND $0 </where> <group>1</group> </select> <select> <columns> CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN <substr>codfam, 1, $NIVFAM</substr> WHEN '$NIVEL1' = 'CODART' THEN codart END <alias name='nivel1'/>, 0 <alias name='valact'/>, SUM(CASE WHEN $TIPVAL = 'IMPNET' THEN import WHEN $TIPVAL = 'CANMOV' THEN cantid WHEN $TIPVAL = 'IMPDTO' THEN import WHEN $TIPVAL = 'IMPCOS' THEN import WHEN $TIPVAL = 'IMPBRD' THEN import WHEN $TIPVAL = 'IMPBRT' THEN import END) <alias name='preact'/>, 0 <alias name='valant'/> </columns> <from table='gcom_presupa'> <join table='gdelgrpl'> <on>gcom_presupa.delega = gdelgrpl.delgrp</on> </join> <join table='galmgrpl'> <on>gcom_presupa.codalm = galmgrpl.almgrp</on> </join> </from> <where> gcom_presupa.anyo = $EJERCI AND gcom_presupa.tippre = $CODPRE AND $0 </where> <group>1</group> </select> </union> <select> <columns> nivel1, CASE WHEN '$NIVEL1' = 'GRPDEL' THEN gdwh_get_descrip('gdelgrph', nivel1) WHEN '$NIVEL1' = 'GRPALM' THEN gdwh_get_descrip('galmgrph', nivel1) WHEN '$NIVEL1' = 'CODALM' THEN gdwh_get_descrip('galmacen', nivel1) WHEN '$NIVEL1' = 'DELEGA' THEN gdwh_get_descrip('gdelegac', nivel1) WHEN '$NIVEL1' = 'DEPART' THEN gdwh_get_descrip('gdeparta', nivel1) WHEN '$NIVEL1' = 'CLADOC' THEN 'DEPENDE CLASE DOCUMENTO' WHEN '$NIVEL1' = 'TIPDOC' THEN 'DEPENDE TIPO DOCUMENTO' WHEN '$NIVEL1' = 'SECCIO' THEN gdwh_get_descrip('gcontipo', nivel1) WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN gdwh_get_descrip('gartfami', nivel1) WHEN '$NIVEL1' = 'CODART' THEN gdwh_get_descrip('garticul', nivel1) WHEN '$NIVEL1' IN ('AUXAL0','AUXAL1','AUXAL2','AUXAL3','AUXAL4') THEN '' END <alias name='desniv1'/>, <char>(($EJERCI*100)+1)</char>||':'||<char>(($EJERCI*100)+12)</char> <alias name='anymesact'/>, <char>((($EJERCI-1)*100)+1)</char>||':'||<char>((($EJERCI-1)*100)+12)</char> <alias name='anymesant'/>, SUM(<nvl>valact, 0</nvl>) <alias name='valact'/>, SUM(<nvl>preact, 0</nvl>) <alias name='preact'/>, ROUND(0,2) <alias name='varpre'/>, SUM(<nvl>valant, 0</nvl>) <alias name='valant'/>, ROUND(0,2) <alias name='vartot'/> </columns> <from table='@tmp_resultado' /> <group>1, 2, 3, 4</group> <having> SUM(valact) != 0 OR SUM(preact) != 0 OR SUM(valant) != 0 </having> </select>
-- ************************************************************************************ -- DEISTER WebStudio XSQL-SELECT Mon Oct 10 18:09:19 CEST 2011 Engine: ids -- ************************************************************************************ SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END nivel1, SUM(CASE WHEN anyo = $EJERCI THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valact, 0 preact, SUM(CASE WHEN anyo = $EJERCI - 1 THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valant FROM gdwh_cdelarta ,gdelgrpl ,galmgrpl WHERE gdwh_cdelarta.delega = gdelgrpl.delgrp AND gdwh_cdelarta.codalm = galmgrpl.almgrp AND gdwh_cdelarta.anyo BETWEEN $EJERCI - 1 AND $EJERCI AND $0 GROUP BY 1 UNION ALL SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END nivel1, 0 valact, SUM(CASE WHEN $TIPVAL = 'IMPNET' THEN import WHEN $TIPVAL = 'CANMOV' THEN cantid WHEN $TIPVAL = 'IMPDTO' THEN import WHEN $TIPVAL = 'IMPCOS' THEN import WHEN $TIPVAL = 'IMPBRD' THEN import WHEN $TIPVAL = 'IMPBRT' THEN import END) preact, 0 valant FROM gcom_presupa ,gdelgrpl ,galmgrpl WHERE gcom_presupa.delega = gdelgrpl.delgrp AND gcom_presupa.codalm = galmgrpl.almgrp AND gcom_presupa.anyo = $EJERCI AND gcom_presupa.tippre = $CODPRE AND $0 GROUP BY 1 INTO TEMP @tmp_resultado WITH NO LOG; SELECT nivel1, CASE WHEN '$NIVEL1' = 'GRPDEL' THEN gdwh_get_descrip('gdelgrph', nivel1) WHEN '$NIVEL1' = 'GRPALM' THEN gdwh_get_descrip('galmgrph', nivel1) WHEN '$NIVEL1' = 'CODALM' THEN gdwh_get_descrip('galmacen', nivel1) WHEN '$NIVEL1' = 'DELEGA' THEN gdwh_get_descrip('gdelegac', nivel1) WHEN '$NIVEL1' = 'DEPART' THEN gdwh_get_descrip('gdeparta', nivel1) WHEN '$NIVEL1' = 'CLADOC' THEN 'DEPENDE CLASE DOCUMENTO' WHEN '$NIVEL1' = 'TIPDOC' THEN 'DEPENDE TIPO DOCUMENTO' WHEN '$NIVEL1' = 'SECCIO' THEN gdwh_get_descrip('gcontipo', nivel1) WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN gdwh_get_descrip('gartfami', nivel1) WHEN '$NIVEL1' = 'CODART' THEN gdwh_get_descrip('garticul', nivel1) WHEN '$NIVEL1' IN ('AUXAL0','AUXAL1','AUXAL2','AUXAL3','AUXAL4') THEN '' END desniv1, (($EJERCI*100)+1)||':'||(($EJERCI*100)+12) anymesact, ((($EJERCI-1)*100)+1)||':'||((($EJERCI-1)*100)+12) anymesant, SUM(NVL(valact, 0)) valact, SUM(NVL(preact, 0)) preact, ROUND(0,2) varpre, SUM(NVL(valant, 0)) valant, ROUND(0,2) vartot FROM @tmp_resultado GROUP BY 1, 2, 3, 4 HAVING SUM(valact) != 0 OR SUM(preact) != 0 OR SUM(valant) != 0
-- ************************************************************************************ -- DEISTER WebStudio XSQL-SELECT Mon Oct 10 18:09:19 CEST 2011 Engine: oracle -- ************************************************************************************ CREATE GLOBAL TEMPORARY TABLE @tmp_resultado ON COMMIT PRESERVE ROWS AS SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END nivel1, SUM(CASE WHEN anyo = $EJERCI THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valact, 0 preact, SUM(CASE WHEN anyo = $EJERCI - 1 THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valant FROM gdwh_cdelarta ,gdelgrpl ,galmgrpl WHERE gdwh_cdelarta.delega = gdelgrpl.delgrp AND gdwh_cdelarta.codalm = galmgrpl.almgrp AND gdwh_cdelarta.anyo BETWEEN $EJERCI - 1 AND $EJERCI AND $0 GROUP BY CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END UNION ALL SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END nivel1, 0 valact, SUM(CASE WHEN $TIPVAL = 'IMPNET' THEN import WHEN $TIPVAL = 'CANMOV' THEN cantid WHEN $TIPVAL = 'IMPDTO' THEN import WHEN $TIPVAL = 'IMPCOS' THEN import WHEN $TIPVAL = 'IMPBRD' THEN import WHEN $TIPVAL = 'IMPBRT' THEN import END) preact, 0 valant FROM gcom_presupa ,gdelgrpl ,galmgrpl WHERE gcom_presupa.delega = gdelgrpl.delgrp AND gcom_presupa.codalm = galmgrpl.almgrp AND gcom_presupa.anyo = $EJERCI AND gcom_presupa.tippre = $CODPRE AND $0 GROUP BY CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END ; SELECT nivel1, CASE WHEN '$NIVEL1' = 'GRPDEL' THEN gdwh_get_descrip('gdelgrph', nivel1) WHEN '$NIVEL1' = 'GRPALM' THEN gdwh_get_descrip('galmgrph', nivel1) WHEN '$NIVEL1' = 'CODALM' THEN gdwh_get_descrip('galmacen', nivel1) WHEN '$NIVEL1' = 'DELEGA' THEN gdwh_get_descrip('gdelegac', nivel1) WHEN '$NIVEL1' = 'DEPART' THEN gdwh_get_descrip('gdeparta', nivel1) WHEN '$NIVEL1' = 'CLADOC' THEN 'DEPENDE CLASE DOCUMENTO' WHEN '$NIVEL1' = 'TIPDOC' THEN 'DEPENDE TIPO DOCUMENTO' WHEN '$NIVEL1' = 'SECCIO' THEN gdwh_get_descrip('gcontipo', nivel1) WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN gdwh_get_descrip('gartfami', nivel1) WHEN '$NIVEL1' = 'CODART' THEN gdwh_get_descrip('garticul', nivel1) WHEN '$NIVEL1' IN ('AUXAL0','AUXAL1','AUXAL2','AUXAL3','AUXAL4') THEN '' END desniv1, (($EJERCI*100)+1)||':'||(($EJERCI*100)+12) anymesact, ((($EJERCI-1)*100)+1)||':'||((($EJERCI-1)*100)+12) anymesant, SUM(NVL(valact, 0)) valact, SUM(NVL(preact, 0)) preact, ROUND(0,2) varpre, SUM(NVL(valant, 0)) valant, ROUND(0,2) vartot FROM @tmp_resultado GROUP BY nivel1, CASE WHEN '$NIVEL1' = 'GRPDEL' THEN gdwh_get_descrip('gdelgrph', nivel1) WHEN '$NIVEL1' = 'GRPALM' THEN gdwh_get_descrip('galmgrph', nivel1) WHEN '$NIVEL1' = 'CODALM' THEN gdwh_get_descrip('galmacen', nivel1) WHEN '$NIVEL1' = 'DELEGA' THEN gdwh_get_descrip('gdelegac', nivel1) WHEN '$NIVEL1' = 'DEPART' THEN gdwh_get_descrip('gdeparta', nivel1) WHEN '$NIVEL1' = 'CLADOC' THEN 'DEPENDE CLASE DOCUMENTO' WHEN '$NIVEL1' = 'TIPDOC' THEN 'DEPENDE TIPO DOCUMENTO' WHEN '$NIVEL1' = 'SECCIO' THEN gdwh_get_descrip('gcontipo', nivel1) WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN gdwh_get_descrip('gartfami', nivel1) WHEN '$NIVEL1' = 'CODART' THEN gdwh_get_descrip('garticul', nivel1) WHEN '$NIVEL1' IN ('AUXAL0','AUXAL1','AUXAL2','AUXAL3','AUXAL4') THEN '' END, (($EJERCI*100)+1)||':'||(($EJERCI*100)+12), ((($EJERCI-1)*100)+1)||':'||((($EJERCI-1)*100)+12) HAVING SUM(valact) != 0 OR SUM(preact) != 0 OR SUM(valant) != 0
-- ************************************************************************************ -- DEISTER WebStudio XSQL-SELECT Mon Oct 10 18:09:19 CEST 2011 Engine: postgres -- ************************************************************************************ CREATE TEMP TABLE @tmp_resultado AS SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END AS nivel1, SUM(CASE WHEN anyo = $EJERCI THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) AS valact, 0 AS preact, SUM(CASE WHEN anyo = $EJERCI - 1 THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) AS valant FROM gdwh_cdelarta INNER JOIN gdelgrpl ON gdwh_cdelarta.delega = gdelgrpl.delgrp INNER JOIN galmgrpl ON gdwh_cdelarta.codalm = galmgrpl.almgrp WHERE gdwh_cdelarta.anyo BETWEEN $EJERCI - 1 AND $EJERCI AND $0 GROUP BY 1 UNION ALL SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END AS nivel1, 0 AS valact, SUM(CASE WHEN $TIPVAL = 'IMPNET' THEN import WHEN $TIPVAL = 'CANMOV' THEN cantid WHEN $TIPVAL = 'IMPDTO' THEN import WHEN $TIPVAL = 'IMPCOS' THEN import WHEN $TIPVAL = 'IMPBRD' THEN import WHEN $TIPVAL = 'IMPBRT' THEN import END) AS preact, 0 AS valant FROM gcom_presupa INNER JOIN gdelgrpl ON gcom_presupa.delega = gdelgrpl.delgrp INNER JOIN galmgrpl ON gcom_presupa.codalm = galmgrpl.almgrp WHERE gcom_presupa.anyo = $EJERCI AND gcom_presupa.tippre = $CODPRE AND $0 GROUP BY 1 ; SELECT nivel1, CASE WHEN '$NIVEL1' = 'GRPDEL' THEN gdwh_get_descrip('gdelgrph', nivel1) WHEN '$NIVEL1' = 'GRPALM' THEN gdwh_get_descrip('galmgrph', nivel1) WHEN '$NIVEL1' = 'CODALM' THEN gdwh_get_descrip('galmacen', nivel1) WHEN '$NIVEL1' = 'DELEGA' THEN gdwh_get_descrip('gdelegac', nivel1) WHEN '$NIVEL1' = 'DEPART' THEN gdwh_get_descrip('gdeparta', nivel1) WHEN '$NIVEL1' = 'CLADOC' THEN 'DEPENDE CLASE DOCUMENTO' WHEN '$NIVEL1' = 'TIPDOC' THEN 'DEPENDE TIPO DOCUMENTO' WHEN '$NIVEL1' = 'SECCIO' THEN gdwh_get_descrip('gcontipo', nivel1) WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN gdwh_get_descrip('gartfami', nivel1) WHEN '$NIVEL1' = 'CODART' THEN gdwh_get_descrip('garticul', nivel1) WHEN '$NIVEL1' IN ('AUXAL0','AUXAL1','AUXAL2','AUXAL3','AUXAL4') THEN '' END AS desniv1, CAST((($EJERCI*100)+1) AS text)||':'||CAST((($EJERCI*100)+12) AS text) AS anymesact, CAST(((($EJERCI-1)*100)+1) AS text)||':'||CAST(((($EJERCI-1)*100)+12) AS text) AS anymesant, SUM(COALESCE(valact, 0)) AS valact, SUM(COALESCE(preact, 0)) AS preact, ROUND(0,2) AS varpre, SUM(COALESCE(valant, 0)) AS valant, ROUND(0,2) AS vartot FROM @tmp_resultado GROUP BY 1, 2, 3, 4 HAVING SUM(valact) != 0 OR SUM(preact) != 0 OR SUM(valant) != 0
-- ************************************************************************************ -- DEISTER WebStudio XSQL-SELECT Mon Oct 10 18:09:19 CEST 2011 Engine: db2i -- ************************************************************************************ DECLARE GLOBAL TEMPORARY TABLE @tmp_resultado AS ( SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END nivel1, SUM(CASE WHEN anyo = $EJERCI THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valact, 0 preact, SUM(CASE WHEN anyo = $EJERCI - 1 THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valant FROM gdwh_cdelarta INNER JOIN gdelgrpl ON gdwh_cdelarta.delega = gdelgrpl.delgrp INNER JOIN galmgrpl ON gdwh_cdelarta.codalm = galmgrpl.almgrp WHERE gdwh_cdelarta.anyo BETWEEN $EJERCI - 1 AND $EJERCI AND $0 GROUP BY CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END UNION ALL SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END nivel1, 0 valact, SUM(CASE WHEN $TIPVAL = 'IMPNET' THEN import WHEN $TIPVAL = 'CANMOV' THEN cantid WHEN $TIPVAL = 'IMPDTO' THEN import WHEN $TIPVAL = 'IMPCOS' THEN import WHEN $TIPVAL = 'IMPBRD' THEN import WHEN $TIPVAL = 'IMPBRT' THEN import END) preact, 0 valant FROM gcom_presupa INNER JOIN gdelgrpl ON gcom_presupa.delega = gdelgrpl.delgrp INNER JOIN galmgrpl ON gcom_presupa.codalm = galmgrpl.almgrp WHERE gcom_presupa.anyo = $EJERCI AND gcom_presupa.tippre = $CODPRE AND $0 GROUP BY CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END ) DEFINITION ONLY NOT LOGGED ON COMMIT PRESERVE ROWS WITH REPLACE; INSERT INTO @tmp_resultado SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END nivel1, SUM(CASE WHEN anyo = $EJERCI THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valact, 0 preact, SUM(CASE WHEN anyo = $EJERCI - 1 THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valant FROM gdwh_cdelarta INNER JOIN gdelgrpl ON gdwh_cdelarta.delega = gdelgrpl.delgrp INNER JOIN galmgrpl ON gdwh_cdelarta.codalm = galmgrpl.almgrp WHERE gdwh_cdelarta.anyo BETWEEN $EJERCI - 1 AND $EJERCI AND $0 GROUP BY CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END UNION ALL SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END nivel1, 0 valact, SUM(CASE WHEN $TIPVAL = 'IMPNET' THEN import WHEN $TIPVAL = 'CANMOV' THEN cantid WHEN $TIPVAL = 'IMPDTO' THEN import WHEN $TIPVAL = 'IMPCOS' THEN import WHEN $TIPVAL = 'IMPBRD' THEN import WHEN $TIPVAL = 'IMPBRT' THEN import END) preact, 0 valant FROM gcom_presupa INNER JOIN gdelgrpl ON gcom_presupa.delega = gdelgrpl.delgrp INNER JOIN galmgrpl ON gcom_presupa.codalm = galmgrpl.almgrp WHERE gcom_presupa.anyo = $EJERCI AND gcom_presupa.tippre = $CODPRE AND $0 GROUP BY CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END ; SELECT nivel1, CASE WHEN '$NIVEL1' = 'GRPDEL' THEN gdwh_get_descrip('gdelgrph', nivel1) WHEN '$NIVEL1' = 'GRPALM' THEN gdwh_get_descrip('galmgrph', nivel1) WHEN '$NIVEL1' = 'CODALM' THEN gdwh_get_descrip('galmacen', nivel1) WHEN '$NIVEL1' = 'DELEGA' THEN gdwh_get_descrip('gdelegac', nivel1) WHEN '$NIVEL1' = 'DEPART' THEN gdwh_get_descrip('gdeparta', nivel1) WHEN '$NIVEL1' = 'CLADOC' THEN 'DEPENDE CLASE DOCUMENTO' WHEN '$NIVEL1' = 'TIPDOC' THEN 'DEPENDE TIPO DOCUMENTO' WHEN '$NIVEL1' = 'SECCIO' THEN gdwh_get_descrip('gcontipo', nivel1) WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN gdwh_get_descrip('gartfami', nivel1) WHEN '$NIVEL1' = 'CODART' THEN gdwh_get_descrip('garticul', nivel1) WHEN '$NIVEL1' IN ('AUXAL0','AUXAL1','AUXAL2','AUXAL3','AUXAL4') THEN '' END desniv1, TRIM(CHAR((($EJERCI*100)+1)))||':'||TRIM(CHAR((($EJERCI*100)+12))) anymesact, TRIM(CHAR(((($EJERCI-1)*100)+1)))||':'||TRIM(CHAR(((($EJERCI-1)*100)+12))) anymesant, SUM(COALESCE(valact, 0)) valact, SUM(COALESCE(preact, 0)) preact, ROUND(0,2) varpre, SUM(COALESCE(valant, 0)) valant, ROUND(0,2) vartot FROM @tmp_resultado GROUP BY nivel1, CASE WHEN '$NIVEL1' = 'GRPDEL' THEN gdwh_get_descrip('gdelgrph', nivel1) WHEN '$NIVEL1' = 'GRPALM' THEN gdwh_get_descrip('galmgrph', nivel1) WHEN '$NIVEL1' = 'CODALM' THEN gdwh_get_descrip('galmacen', nivel1) WHEN '$NIVEL1' = 'DELEGA' THEN gdwh_get_descrip('gdelegac', nivel1) WHEN '$NIVEL1' = 'DEPART' THEN gdwh_get_descrip('gdeparta', nivel1) WHEN '$NIVEL1' = 'CLADOC' THEN 'DEPENDE CLASE DOCUMENTO' WHEN '$NIVEL1' = 'TIPDOC' THEN 'DEPENDE TIPO DOCUMENTO' WHEN '$NIVEL1' = 'SECCIO' THEN gdwh_get_descrip('gcontipo', nivel1) WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN gdwh_get_descrip('gartfami', nivel1) WHEN '$NIVEL1' = 'CODART' THEN gdwh_get_descrip('garticul', nivel1) WHEN '$NIVEL1' IN ('AUXAL0','AUXAL1','AUXAL2','AUXAL3','AUXAL4') THEN '' END, TRIM(CHAR((($EJERCI*100)+1)))||':'||TRIM(CHAR((($EJERCI*100)+12))), TRIM(CHAR(((($EJERCI-1)*100)+1)))||':'||TRIM(CHAR(((($EJERCI-1)*100)+12))) HAVING SUM(valact) != 0 OR SUM(preact) != 0 OR SUM(valant) != 0
-- ************************************************************************************ -- DEISTER WebStudio XSQL-SELECT Mon Oct 10 18:09:19 CEST 2011 Engine: db2u -- ************************************************************************************ DECLARE GLOBAL TEMPORARY TABLE @tmp_resultado AS ( SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END nivel1, SUM(CASE WHEN anyo = $EJERCI THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valact, 0 preact, SUM(CASE WHEN anyo = $EJERCI - 1 THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valant FROM gdwh_cdelarta INNER JOIN gdelgrpl ON gdwh_cdelarta.delega = gdelgrpl.delgrp INNER JOIN galmgrpl ON gdwh_cdelarta.codalm = galmgrpl.almgrp WHERE gdwh_cdelarta.anyo BETWEEN $EJERCI - 1 AND $EJERCI AND $0 GROUP BY CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END UNION ALL SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END nivel1, 0 valact, SUM(CASE WHEN $TIPVAL = 'IMPNET' THEN import WHEN $TIPVAL = 'CANMOV' THEN cantid WHEN $TIPVAL = 'IMPDTO' THEN import WHEN $TIPVAL = 'IMPCOS' THEN import WHEN $TIPVAL = 'IMPBRD' THEN import WHEN $TIPVAL = 'IMPBRT' THEN import END) preact, 0 valant FROM gcom_presupa INNER JOIN gdelgrpl ON gcom_presupa.delega = gdelgrpl.delgrp INNER JOIN galmgrpl ON gcom_presupa.codalm = galmgrpl.almgrp WHERE gcom_presupa.anyo = $EJERCI AND gcom_presupa.tippre = $CODPRE AND $0 GROUP BY CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END ) DEFINITION ONLY NOT LOGGED ON COMMIT PRESERVE ROWS WITH REPLACE; INSERT INTO @tmp_resultado SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END nivel1, SUM(CASE WHEN anyo = $EJERCI THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valact, 0 preact, SUM(CASE WHEN anyo = $EJERCI - 1 THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valant FROM gdwh_cdelarta INNER JOIN gdelgrpl ON gdwh_cdelarta.delega = gdelgrpl.delgrp INNER JOIN galmgrpl ON gdwh_cdelarta.codalm = galmgrpl.almgrp WHERE gdwh_cdelarta.anyo BETWEEN $EJERCI - 1 AND $EJERCI AND $0 GROUP BY CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END UNION ALL SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END nivel1, 0 valact, SUM(CASE WHEN $TIPVAL = 'IMPNET' THEN import WHEN $TIPVAL = 'CANMOV' THEN cantid WHEN $TIPVAL = 'IMPDTO' THEN import WHEN $TIPVAL = 'IMPCOS' THEN import WHEN $TIPVAL = 'IMPBRD' THEN import WHEN $TIPVAL = 'IMPBRT' THEN import END) preact, 0 valant FROM gcom_presupa INNER JOIN gdelgrpl ON gcom_presupa.delega = gdelgrpl.delgrp INNER JOIN galmgrpl ON gcom_presupa.codalm = galmgrpl.almgrp WHERE gcom_presupa.anyo = $EJERCI AND gcom_presupa.tippre = $CODPRE AND $0 GROUP BY CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END ; SELECT nivel1, CASE WHEN '$NIVEL1' = 'GRPDEL' THEN gdwh_get_descrip('gdelgrph', nivel1) WHEN '$NIVEL1' = 'GRPALM' THEN gdwh_get_descrip('galmgrph', nivel1) WHEN '$NIVEL1' = 'CODALM' THEN gdwh_get_descrip('galmacen', nivel1) WHEN '$NIVEL1' = 'DELEGA' THEN gdwh_get_descrip('gdelegac', nivel1) WHEN '$NIVEL1' = 'DEPART' THEN gdwh_get_descrip('gdeparta', nivel1) WHEN '$NIVEL1' = 'CLADOC' THEN 'DEPENDE CLASE DOCUMENTO' WHEN '$NIVEL1' = 'TIPDOC' THEN 'DEPENDE TIPO DOCUMENTO' WHEN '$NIVEL1' = 'SECCIO' THEN gdwh_get_descrip('gcontipo', nivel1) WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN gdwh_get_descrip('gartfami', nivel1) WHEN '$NIVEL1' = 'CODART' THEN gdwh_get_descrip('garticul', nivel1) WHEN '$NIVEL1' IN ('AUXAL0','AUXAL1','AUXAL2','AUXAL3','AUXAL4') THEN '' END desniv1, TRIM(CHAR((($EJERCI*100)+1)))||':'||TRIM(CHAR((($EJERCI*100)+12))) anymesact, TRIM(CHAR(((($EJERCI-1)*100)+1)))||':'||TRIM(CHAR(((($EJERCI-1)*100)+12))) anymesant, SUM(COALESCE(valact, 0)) valact, SUM(COALESCE(preact, 0)) preact, ROUND(0,2) varpre, SUM(COALESCE(valant, 0)) valant, ROUND(0,2) vartot FROM @tmp_resultado GROUP BY nivel1, CASE WHEN '$NIVEL1' = 'GRPDEL' THEN gdwh_get_descrip('gdelgrph', nivel1) WHEN '$NIVEL1' = 'GRPALM' THEN gdwh_get_descrip('galmgrph', nivel1) WHEN '$NIVEL1' = 'CODALM' THEN gdwh_get_descrip('galmacen', nivel1) WHEN '$NIVEL1' = 'DELEGA' THEN gdwh_get_descrip('gdelegac', nivel1) WHEN '$NIVEL1' = 'DEPART' THEN gdwh_get_descrip('gdeparta', nivel1) WHEN '$NIVEL1' = 'CLADOC' THEN 'DEPENDE CLASE DOCUMENTO' WHEN '$NIVEL1' = 'TIPDOC' THEN 'DEPENDE TIPO DOCUMENTO' WHEN '$NIVEL1' = 'SECCIO' THEN gdwh_get_descrip('gcontipo', nivel1) WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN gdwh_get_descrip('gartfami', nivel1) WHEN '$NIVEL1' = 'CODART' THEN gdwh_get_descrip('garticul', nivel1) WHEN '$NIVEL1' IN ('AUXAL0','AUXAL1','AUXAL2','AUXAL3','AUXAL4') THEN '' END, TRIM(CHAR((($EJERCI*100)+1)))||':'||TRIM(CHAR((($EJERCI*100)+12))), TRIM(CHAR(((($EJERCI-1)*100)+1)))||':'||TRIM(CHAR(((($EJERCI-1)*100)+12))) HAVING SUM(valact) != 0 OR SUM(preact) != 0 OR SUM(valant) != 0
--************************************************************************************ -- DEISTER WebStudio XSQL-SELECT Mon Oct 10 18:09:19 CEST 2011 Engine: sqlserver -- ************************************************************************************ SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTRING(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END nivel1, SUM(CASE WHEN anyo = $EJERCI THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valact, 0 preact, SUM(CASE WHEN anyo = $EJERCI - 1 THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot WHEN $TIPVAL = 'CANMOV' THEN canmov_tot WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot END END) valant FROM gdwh_cdelarta INNER JOIN gdelgrpl ON gdwh_cdelarta.delega = gdelgrpl.delgrp INNER JOIN galmgrpl ON gdwh_cdelarta.codalm = galmgrpl.almgrp WHERE gdwh_cdelarta.anyo BETWEEN $EJERCI - 1 AND $EJERCI AND $0 GROUP BY CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTRING(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4 END UNION ALL SELECT CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTRING(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END nivel1, 0 valact, SUM(CASE WHEN $TIPVAL = 'IMPNET' THEN import WHEN $TIPVAL = 'CANMOV' THEN cantid WHEN $TIPVAL = 'IMPDTO' THEN import WHEN $TIPVAL = 'IMPCOS' THEN import WHEN $TIPVAL = 'IMPBRD' THEN import WHEN $TIPVAL = 'IMPBRT' THEN import END) preact, 0 valant FROM gcom_presupa INNER JOIN gdelgrpl ON gcom_presupa.delega = gdelgrpl.delgrp INNER JOIN galmgrpl ON gcom_presupa.codalm = galmgrpl.almgrp WHERE gcom_presupa.anyo = $EJERCI AND gcom_presupa.tippre = $CODPRE AND $0 GROUP BY CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel WHEN '$NIVEL1' = 'GRPALM' THEN grpalm WHEN '$NIVEL1' = 'CODALM' THEN codalm WHEN '$NIVEL1' = 'DELEGA' THEN delega WHEN '$NIVEL1' = 'DEPART' THEN depart WHEN '$NIVEL1' = 'CLADOC' THEN cladoc WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc WHEN '$NIVEL1' = 'SECCIO' THEN seccio WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTRING(codfam, 1, $NIVFAM) WHEN '$NIVEL1' = 'CODART' THEN codart END ; SELECT nivel1, CASE WHEN '$NIVEL1' = 'GRPDEL' THEN gdwh_get_descrip('gdelgrph', nivel1) WHEN '$NIVEL1' = 'GRPALM' THEN gdwh_get_descrip('galmgrph', nivel1) WHEN '$NIVEL1' = 'CODALM' THEN gdwh_get_descrip('galmacen', nivel1) WHEN '$NIVEL1' = 'DELEGA' THEN gdwh_get_descrip('gdelegac', nivel1) WHEN '$NIVEL1' = 'DEPART' THEN gdwh_get_descrip('gdeparta', nivel1) WHEN '$NIVEL1' = 'CLADOC' THEN 'DEPENDE CLASE DOCUMENTO' WHEN '$NIVEL1' = 'TIPDOC' THEN 'DEPENDE TIPO DOCUMENTO' WHEN '$NIVEL1' = 'SECCIO' THEN gdwh_get_descrip('gcontipo', nivel1) WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN gdwh_get_descrip('gartfami', nivel1) WHEN '$NIVEL1' = 'CODART' THEN gdwh_get_descrip('garticul', nivel1) WHEN '$NIVEL1' IN ('AUXAL0','AUXAL1','AUXAL2','AUXAL3','AUXAL4') THEN '' END desniv1, (($EJERCI*100)+1)||':'||(($EJERCI*100)+12) anymesact, ((($EJERCI-1)*100)+1)||':'||((($EJERCI-1)*100)+12) anymesant, SUM(ISNULL(valact, 0)) valact, SUM(ISNULL(preact, 0)) preact, ROUND(0,2) varpre, SUM(ISNULL(valant, 0)) valant, ROUND(0,2) vartot FROM @tmp_resultado GROUP BY nivel1, CASE WHEN '$NIVEL1' = 'GRPDEL' THEN gdwh_get_descrip('gdelgrph', nivel1) WHEN '$NIVEL1' = 'GRPALM' THEN gdwh_get_descrip('galmgrph', nivel1) WHEN '$NIVEL1' = 'CODALM' THEN gdwh_get_descrip('galmacen', nivel1) WHEN '$NIVEL1' = 'DELEGA' THEN gdwh_get_descrip('gdelegac', nivel1) WHEN '$NIVEL1' = 'DEPART' THEN gdwh_get_descrip('gdeparta', nivel1) WHEN '$NIVEL1' = 'CLADOC' THEN 'DEPENDE CLASE DOCUMENTO' WHEN '$NIVEL1' = 'TIPDOC' THEN 'DEPENDE TIPO DOCUMENTO' WHEN '$NIVEL1' = 'SECCIO' THEN gdwh_get_descrip('gcontipo', nivel1) WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN gdwh_get_descrip('gartfami', nivel1) WHEN '$NIVEL1' = 'CODART' THEN gdwh_get_descrip('garticul', nivel1) WHEN '$NIVEL1' IN ('AUXAL0','AUXAL1','AUXAL2','AUXAL3','AUXAL4') THEN '' END, (($EJERCI*100)+1)||':'||(($EJERCI*100)+12), ((($EJERCI-1)*100)+1)||':'||((($EJERCI-1)*100)+12) HAVING SUM(valact) != 0 OR SUM(preact) != 0 OR SUM(valant) != 0
Procedimientos (SPL)
La lógica de negocio escrita en procedimientos (SPL), funciones o triggers debe ser reescrita manualmente en XML. Nosotros ofrecemos este servicio o enseñamos como hacerlo. A partir de entonces, se podrá traducir automáticamente la lógica al motor que se desee.
Este paso tiene una mayor ventaja sobre la codificación manual convencional, ya que el motor de traducción Axional XSQL validará y generará el código apropiado sin errores humanos.
El manager de procedimientos (SPL) se encargará del status de compilación en las bases de datos deseadas (desarrollo, test y producción).
DDL – XML transformation sample
<function name='gart_unidefs_get_canbase'> <args> <arg name='p_codart' like='gvenpedl.codart' /> <!-- Artículo --> <arg name='p_varlog' like='gvenpedl.varlog' /> <!-- Variable logística --> <arg name='p_udmdoc' like='gvenpedl.udmven' /> <!-- Unidad medida documento --> <arg name='p_udmalt' like='gvenpedl.udmalt' /> <!-- Unidad alternativa --> <arg name='p_candoc' like='gvenpedl.canped' /> <!-- Cantidad del documento --> <arg name='p_canalt' like='gvenpedl.canalt' /> <!-- Cantidad alternativa --> </args> <returns type='decimal' size='12,3' /> <!-- Cantidad en unidad base --> <define> <variable name='m_udmbas' like='garticul.udmbas' /> <variable name='m_udmaux' like='garticul.udmaux' /> <variable name='m_udmrel' like='gart_unidefs.udmrel' /> <variable name='m_candes' like='gvenpedl.canped' /> </define> <body> <set name='m_candes'>NULL</set> <!-- ============================================================================== --> <!-- Obtener la unidad base y auxiliar del artículo. --> <!-- ============================================================================== --> <select>udmbas, udmaux <into>m_udmbas, m_udmaux</into> FROM garticul WHERE codigo = p_codart </select> <if> <expr>m_udmbas IS NULL</expr> <then> <exception>'Artículo: [' || p_codart || '] no encontrado en garticul'</exception> </then> </if> <!-- ============================================================================== --> <!-- Si la unidad del documento coincide con la base, la cantidad en unidades base --> <!-- es la cantidad del documento. --> <!-- ============================================================================== --> <if> <expr>p_udmdoc = m_udmbas</expr> <then> <return>p_candoc</return> </then> </if> <!-- ============================================================================== --> <!-- La unidad alternativa coincide con la unidad base. --> <!-- ============================================================================== --> <if> <expr>p_udmalt IS NOT NULL AND p_udmalt = m_udmbas </expr> <then> <return>p_canalt</return> </then> </if> <!-- ============================================================================== --> <!-- La unidad del documento no coincide con la unidad base pero está relacionada. --> <!-- ============================================================================== --> <execute-function name='gart_unidefs_get_udmrel' into='m_udmrel'> p_codart, p_varlog, p_udmdoc </execute-function> <if> <expr> m_udmrel = 'B' OR (m_udmrel = 'A' AND p_udmalt IS NULL) </expr> <then> <execute-function name='gart_uniconv_get_cantid' into='m_candes'> <cast type='smallint'>0</cast>, p_codart, p_varlog, p_udmdoc, <cast type='varchar'>NULL</cast>, m_udmbas, p_candoc, <cast type='decimal' size='16,6'>NULL</cast> </execute-function> </then> </if> <!-- ============================================================================== --> <!-- La unidad alternativa del documento está relacionada con la unidad base. --> <!-- ============================================================================== --> <if> <expr>m_candes IS NULL AND p_udmalt IS NOT NULL</expr> <then> <execute-function name='gart_unidefs_get_udmrel' into='m_udmrel'> p_codart, p_varlog, p_udmalt </execute-function> <if> <expr>m_udmrel = 'B'</expr> <then> <execute-function name='gart_uniconv_get_cantid' into='m_candes'> <cast type='smallint'>0</cast>, p_codart, p_varlog, p_udmalt, <cast type='varchar'>NULL</cast>, m_udmbas, p_canalt, <cast type='decimal' size='16,6'>NULL</cast> </execute-function> </then> <else> <execute-function name='gart_uniconv_get_cantid' into='m_candes'> <cast type='smallint'>0</cast>, p_codart, p_varlog, p_udmdoc, <cast type='varchar'>NULL</cast>, m_udmbas, p_candoc, <cast type='decimal' size='16,6'>NULL</cast> </execute-function> </else> </if> </then> </if> <!-- ============================================================================== --> <!-- No se pudo obtener la cantidad de la línea de documento unidad base. --> <!-- ============================================================================== --> <!-- BPR 19-05-2009 Este exception debe ser condicional a un parámetro. Mientras no se realice esta modificación se comenta, ya que el error impide entrar lineas de documentos, en caso de haber un artículo mal parametrizado. <if> <expr>m_candes IS NULL</expr> <then> <exception>'Art:[' || <trim>p_codart</trim> || '] VL:[' || <trim><nvl>p_varlog, ''</nvl></trim> || '] Udm:[' || <trim>p_udmdoc</trim> || '. No se pudo convertir a unidad base' </exception> </then> </if> --> <return>m_candes</return> </body> </function>
-- ************************************************************************** -- gart_unidefs_get_canbase -- DEISTER WebStudio XSQL-UDFUNC - Engine: ids -- ************************************************************************** CREATE FUNCTION gart_unidefs_get_canbase( p_codart LIKE gvenpedl.codart, p_varlog LIKE gvenpedl.varlog, p_udmdoc LIKE gvenpedl.udmven, p_udmalt LIKE gvenpedl.udmalt, p_candoc LIKE gvenpedl.canped, p_canalt LIKE gvenpedl.canalt ) RETURNING decimal(12,3); -- ================================= -- Definition of variables -- ================================= DEFINE m_udmbas LIKE garticul.udmbas; DEFINE m_udmaux LIKE garticul.udmaux; DEFINE m_udmrel LIKE gart_unidefs.udmrel; DEFINE m_candes LIKE gvenpedl.canped; -- ========================================================= -- Debug -- To activate debug mesages, call sdm_set_debug(1) or level -- ========================================================= DEFINE GLOBAL gl_debug SMALLINT DEFAULT 0; -- DEBUG FLAG IF gl_debug > 1 THEN TRACE ON; END IF -- ================================= -- Function body -- ================================= LET m_candes = NULL; SELECT udmbas, udmaux INTO m_udmbas, m_udmaux FROM garticul WHERE codigo = p_codart ; IF m_udmbas IS NULL THEN RAISE EXCEPTION -746, 0, 'Artículo: [' || p_codart || '] no encontrado en garticul'; END IF; IF p_udmdoc = m_udmbas THEN RETURN p_candoc; END IF; IF p_udmalt IS NOT NULL AND p_udmalt = m_udmbas THEN RETURN p_canalt; END IF; LET m_udmrel = gart_unidefs_get_udmrel( p_codart, p_varlog, p_udmdoc ); IF m_udmrel = 'B' OR (m_udmrel = 'A' AND p_udmalt IS NULL) THEN LET m_candes = gart_uniconv_get_cantid(CAST(0 AS smallint ) , p_codart, p_varlog, p_udmdoc, CAST(NULL AS varchar (255)) , m_udmbas, p_candoc, CAST(NULL AS decimal (16,6)) ); END IF; IF m_candes IS NULL AND p_udmalt IS NOT NULL THEN LET m_udmrel = gart_unidefs_get_udmrel( p_codart, p_varlog, p_udmalt ); IF m_udmrel = 'B' THEN LET m_candes = gart_uniconv_get_cantid(CAST(0 AS smallint ) , p_codart, p_varlog, p_udmalt, CAST(NULL AS varchar (255)) , m_udmbas, p_canalt, CAST(NULL AS decimal (16,6)) ); ELSE LET m_candes = gart_uniconv_get_cantid(CAST(0 AS smallint ) , p_codart, p_varlog, p_udmdoc, CAST(NULL AS varchar (255)) , m_udmbas, p_candoc, CAST(NULL AS decimal (16,6)) ); END IF; END IF; RETURN m_candes; END FUNCTION;
-- ************************************************************************** -- gart_unidefs_get_canbase -- DEISTER WebStudio XSQL-UDFUNC - Engine: oracle -- ************************************************************************** CREATE OR REPLACE FUNCTION gart_unidefs_get_canbase( p_codart IN gvenpedl.codart%TYPE, p_varlog IN gvenpedl.varlog%TYPE, p_udmdoc IN gvenpedl.udmven%TYPE, p_udmalt IN gvenpedl.udmalt%TYPE, p_candoc IN gvenpedl.canped%TYPE, p_canalt IN gvenpedl.canalt%TYPE ) RETURN number AS -- ================================= -- Definition of variables -- ================================= m_udmbas garticul.udmbas%TYPE; m_udmaux garticul.udmaux%TYPE; m_udmrel gart_unidefs.udmrel%TYPE; m_candes gvenpedl.canped%TYPE; -- ===================================================================== -- Debug -- To activate debug mesages, execute command line $ set serveroutput on -- ===================================================================== BEGIN -- ================================= -- Function body -- ================================= m_candes := NULL; BEGIN SELECT udmbas, udmaux INTO m_udmbas, m_udmaux FROM garticul WHERE codigo = p_codart ; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; IF m_udmbas IS NULL THEN RAISE_APPLICATION_ERROR (-20000, 'Artículo: [' || p_codart || '] no encontrado en garticul'); END IF; IF p_udmdoc = m_udmbas THEN RETURN p_candoc; END IF; IF p_udmalt IS NOT NULL AND p_udmalt = m_udmbas THEN RETURN p_canalt; END IF; m_udmrel := gart_unidefs_get_udmrel( p_codart, p_varlog, p_udmdoc ); IF m_udmrel = 'B' OR (m_udmrel = 'A' AND p_udmalt IS NULL) THEN m_candes := gart_uniconv_get_cantid(0, p_codart, p_varlog, p_udmdoc, NULL, m_udmbas, p_candoc, NULL); END IF; IF m_candes IS NULL AND p_udmalt IS NOT NULL THEN m_udmrel := gart_unidefs_get_udmrel( p_codart, p_varlog, p_udmalt ); IF m_udmrel = 'B' THEN m_candes := gart_uniconv_get_cantid(0, p_codart, p_varlog, p_udmalt, NULL, m_udmbas, p_canalt, NULL); ELSE m_candes := gart_uniconv_get_cantid(0, p_codart, p_varlog, p_udmdoc, NULL, m_udmbas, p_candoc, NULL); END IF; END IF; RETURN m_candes; END; . run
-- ************************************************************************** -- gart_unidefs_get_canbase -- DEISTER WebStudio XSQL-UDFUNC - Engine: postgres -- ************************************************************************** CREATE OR REPLACE FUNCTION gart_unidefs_get_canbase( p_codart IN gvenpedl.codart%TYPE, p_varlog IN gvenpedl.varlog%TYPE, p_udmdoc IN gvenpedl.udmven%TYPE, p_udmalt IN gvenpedl.udmalt%TYPE, p_candoc IN gvenpedl.canped%TYPE, p_canalt IN gvenpedl.canalt%TYPE ) RETURNS decimal(12,3) AS $$ DECLARE -- ================================= -- Definition of variables -- ================================= m_udmbas garticul.udmbas%TYPE; m_udmaux garticul.udmaux%TYPE; m_udmrel gart_unidefs.udmrel%TYPE; m_candes gvenpedl.canped%TYPE; m_msg VARCHAR(255); BEGIN -- ================================= -- Function body -- ================================= m_candes := NULL; SELECT udmbas, udmaux INTO m_udmbas, m_udmaux FROM garticul WHERE codigo = p_codart ; IF m_udmbas IS NULL THEN m_msg :='Artículo: [' || p_codart || '] no encontrado en garticul'; RAISE EXCEPTION '%', m_msg; END IF; IF p_udmdoc = m_udmbas THEN RETURN p_candoc; END IF; IF p_udmalt IS NOT NULL AND p_udmalt = m_udmbas THEN RETURN p_canalt; END IF; m_udmrel := gart_unidefs_get_udmrel( p_codart, p_varlog, p_udmdoc ); IF m_udmrel = 'B' OR (m_udmrel = 'A' AND p_udmalt IS NULL) THEN m_candes := gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmdoc, CAST(NULL AS varchar), m_udmbas, p_candoc, CAST(NULL AS decimal)); END IF; IF m_candes IS NULL AND p_udmalt IS NOT NULL THEN m_udmrel := gart_unidefs_get_udmrel( p_codart, p_varlog, p_udmalt ); IF m_udmrel = 'B' THEN m_candes := gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmalt, CAST(NULL AS varchar), m_udmbas, p_canalt, CAST(NULL AS decimal)); ELSE m_candes := gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmdoc, CAST(NULL AS varchar), m_udmbas, p_candoc, CAST(NULL AS decimal)); END IF; END IF; RETURN m_candes; END $$ LANGUAGE 'plpgsql';
-- ************************************************************************** -- gart_unidefs_get_canbase -- DEISTER WebStudio XSQL-UDFUNC - Engine: db2i -- ************************************************************************** CREATE FUNCTION gart_unidefs_get_canbase( p_codart varchar(16), p_varlog varchar(6), p_udmdoc varchar(6), p_udmalt varchar(6), p_candoc decimal(12,3), p_canalt decimal(12,3) ) RETURNS decimal(12,3) LANGUAGE SQL NO EXTERNAL ACTION NOT DETERMINISTIC READS SQL DATA BEGIN ATOMIC -- Variable para los exceptions DECLARE m_errorMessageText char(80); -- ================================= -- Definition of variables -- ================================= DECLARE m_udmbas varchar(6); DECLARE m_udmaux varchar(6); DECLARE m_udmrel varchar(1); DECLARE m_candes decimal(12,3); -- ================================= -- Function body -- ================================= SET m_candes = NULL; SET (m_udmbas, m_udmaux) = (SELECT udmbas, udmaux FROM garticul WHERE codigo = p_codart ); IF m_udmbas IS NULL THEN SET m_errorMessageText = 'Artículo: [' || p_codart || '] no encontrado en garticul'; SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT= m_errorMessageText; END IF; IF p_udmdoc = m_udmbas THEN RETURN p_candoc; END IF; IF p_udmalt IS NOT NULL AND p_udmalt = m_udmbas THEN RETURN p_canalt; END IF; SET m_udmrel = gart_unidefs_get_udmrel( p_codart, p_varlog, p_udmdoc ); IF m_udmrel = 'B' OR (m_udmrel = 'A' AND p_udmalt IS NULL) THEN SET m_candes = gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmdoc, CAST(NULL AS varchar(255)), m_udmbas, p_candoc, CAST(NULL AS decimal)); END IF; IF m_candes IS NULL AND p_udmalt IS NOT NULL THEN SET m_udmrel = gart_unidefs_get_udmrel( p_codart, p_varlog, p_udmalt ); IF m_udmrel = 'B' THEN SET m_candes = gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmalt, CAST(NULL AS varchar(255)), m_udmbas, p_canalt, CAST(NULL AS decimal)); ELSE SET m_candes = gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmdoc, CAST(NULL AS varchar(255)), m_udmbas, p_candoc, CAST(NULL AS decimal)); END IF; END IF; RETURN m_candes; END;
-- ************************************************************************** -- gart_unidefs_get_canbase -- DEISTER WebStudio XSQL-UDFUNC - Engine: db2u -- ************************************************************************** CREATE FUNCTION gart_unidefs_get_canbase( p_codart , p_varlog , p_udmdoc , p_udmalt , p_candoc , p_canalt ) RETURNS decimal(12,3) NO EXTERNAL ACTION NOT DETERMINISTIC READS SQL DATA BEGIN ATOMIC -- Variable para los exceptions DECLARE m_errorMessageText char(80); -- ================================= -- Definition of variables -- ================================= DECLARE m_udmbas ; DECLARE m_udmaux ; DECLARE m_udmrel ; DECLARE m_candes ; -- ================================= -- Function body -- ================================= SET m_candes = NULL; SET (m_udmbas, m_udmaux) = (SELECT udmbas, udmaux FROM garticul WHERE codigo = p_codart ); IF m_udmbas IS NULL THEN SET m_errorMessageText = 'Artículo: [' || p_codart || '] no encontrado en garticul'; SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT= m_errorMessageText; END IF; IF p_udmdoc = m_udmbas THEN RETURN p_candoc; END IF; IF p_udmalt IS NOT NULL AND p_udmalt = m_udmbas THEN RETURN p_canalt; END IF; SET m_udmrel = gart_unidefs_get_udmrel( p_codart, p_varlog, p_udmdoc ); IF m_udmrel = 'B' OR (m_udmrel = 'A' AND p_udmalt IS NULL) THEN SET m_candes = gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmdoc, CAST(NULL AS varchar(254)), m_udmbas, p_candoc, CAST(NULL AS decimal)); END IF; IF m_candes IS NULL AND p_udmalt IS NOT NULL THEN SET m_udmrel = gart_unidefs_get_udmrel( p_codart, p_varlog, p_udmalt ); IF m_udmrel = 'B' THEN SET m_candes = gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmalt, CAST(NULL AS varchar(254)), m_udmbas, p_canalt, CAST(NULL AS decimal)); ELSE SET m_candes = gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmdoc, CAST(NULL AS varchar(254)), m_udmbas, p_candoc, CAST(NULL AS decimal)); END IF; END IF; RETURN m_candes; END;
-- ************************************************************************** -- gart_unidefs_get_canbase -- DEISTER WebStudio XSQL-UDFUNC - Engine: sqlserver -- ************************************************************************** CREATE FUNCTION gart_unidefs_get_canbase ( @p_codart char(16), @p_varlog char(6), @p_udmdoc char(6), @p_udmalt char(6), @p_candoc decimal(12,3), @p_canalt decimal(12,3) ) RETURNS decimal(12,3) AS BEGIN DECLARE @m_udmbas char(6) DECLARE @m_udmaux char(6) DECLARE @m_udmrel char(1) DECLARE @m_candes decimal(12,3) -- ================================= -- Function body -- ================================= SET @m_candes = NULL SELECT udmbas, udmaux INTO m_udmbas, m_udmaux FROM garticul WHERE codigo = p_codart IF m_udmbas IS NULL BEGIN RAISE EXCEPTION -746, 0, 'Artículo: [' || p_codart || '] no encontrado en garticul'; END IF p_udmdoc = m_udmbas BEGIN RETURN p_candoc END IF p_udmalt IS NOT NULL AND p_udmalt = m_udmbas BEGIN RETURN p_canalt END IF m_udmrel = 'B' OR (m_udmrel = 'A' AND p_udmalt IS NULL) BEGIN END IF m_candes IS NULL AND p_udmalt IS NOT NULL BEGIN IF m_udmrel = 'B' BEGIN END ELSE BEGIN END END RETURN m_candes -- ================================= -- SQLSERVER needs RETURN at last instruction (error:455) -- ================================= RETURN p_candoc END
Triggers
Si tiene triggers, quizás es conocedor de la complejidad y las diferencias que supone escribir triggers independientes de la base de datos. Como en el caso de los procedimientos (SPL), se puede utilizar gramática XML y el motor de de traducción generará los triggers apropiados para la base de datos deseada.
DDL – XML transformation sample
<xsql-trigger name='gcommovh_upd' table='gcommovh' event='update' > <foreach-row> <!-- ===================================================================== --> <!-- Posible código particularizado de customizacion. --> <!-- ===================================================================== --> <include name='before' /> <!-- ===================================================================== --> <!-- Restricciones de modificación. --> <!-- Siempre: tipdoc --> <!-- Si líneas docori, almori, almdes --> <!-- Si docori informado delega, tercer, almori, almdes --> <!-- Si lineas procesadas: delega, tercer, terenv, divisa --> <!-- ===================================================================== --> <when> <expr> <prv>tipdoc</prv> != <nxt>tipdoc</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='sdm_raise_msg'> <in> <param>0</param> <param>'No se puede modificar ciertos datos claves del movimiento'</param> </in> </execute-procedure> </then> </when> <when> <expr> (<prv>almori</prv> != <nxt>almori</nxt> OR <prv>almdes</prv> != <nxt>almdes</nxt> OR (<prv>almdes</prv> IS NULL AND <nxt>almdes</nxt> IS NOT NULL) OR (<prv>almdes</prv> IS NOT NULL AND <nxt>almdes</nxt> IS NULL) OR <nvl><prv>docori</prv>, <whitespace /></nvl> != <nvl><nxt>docori</nxt>, <whitespace /></nvl>) AND <nxt>movhis</nxt> != -1 </expr> <expr> <expr-select> SELECT COUNT(*) <expr-into type='integer' /> FROM gcommovl WHERE cabid = <nxt>cabid</nxt> </expr-select> > 0 </expr> <then> <execute-procedure name='sdm_raise_msg'> <in> <param>0</param> <param>'No se puede modificar ciertos datos si hay líneas'</param> </in> </execute-procedure> </then> </when> <when> <expr> <length><nxt>docori</nxt></length> > 0 AND (<prv>delega</prv> != <nxt>delega</nxt> OR <prv>tercer</prv> != <nxt>tercer</nxt>) AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='sdm_raise_msg'> <in> <param>0</param> <param>'Mov. con documento origen, ciertos datos no se pueden modificar'</param> </in> </execute-procedure> </then> </when> <when> <expr> (<prv>delega</prv> != <nxt>delega</nxt> OR <prv>tercer</prv> != <nxt>tercer</nxt> OR <prv>terenv</prv> != <nxt>terenv</nxt> OR <prv>divisa</prv> != <nxt>divisa</nxt>) AND <nxt>movhis</nxt> != -1 </expr> <expr> <expr-select> SELECT COUNT(*) <expr-into type='integer' /> FROM gcommovd WHERE codigo = <nxt>tipdoc</nxt> AND tabdes = 'FC' </expr-select> = 0 AND <expr-select> SELECT COUNT(*) <expr-into type='integer' /> FROM gcommovl WHERE cabid = <nxt>cabid</nxt> AND estlin NOT IN ('E', 'V') </expr-select> > 0 </expr> <then> <execute-procedure name='sdm_raise_msg'> <in> <param>0</param> <param>'Mov. con lineas procesadas, ciertos datos no se pueden modificar'</param> </in> </execute-procedure> </then> </when> <!-- ===================================================================== --> <!-- Fecha del movimiento anterior a última valoración de almacén --> <!-- ===================================================================== --> <when> <expr> <nxt>fecmov</nxt> != <prv>fecmov</prv> AND <nxt>movhis</nxt> != -1 AND <expr-select> SELECT COUNT(*) <expr-into type='integer' /> FROM gcommovd, galmctas a, galmctas b WHERE gcommovd.codigo = <nxt>tipdoc</nxt> AND gcommovd.ctaori = a.codigo AND gcommovd.ctades = b.codigo AND (a.indsal != 'N' OR b.indsal != 'N') </expr-select> > 0 AND <expr-select> SELECT COUNT(*) <expr-into type='integer' /> FROM galmacen WHERE (codigo = <nxt>almori</nxt> OR codigo = <nxt>almdes</nxt>) AND fecval IS NOT NULL AND (fecval >= <nxt>fecmov</nxt> OR fecval >= <prv>fecmov</prv>) </expr-select> > 0 </expr> <then> <execute-procedure name='sdm_raise_msg'> <in> <param>0</param> <param>'Fecha movimiento anterior a la del ultimo cierre del almacen orig/dest'</param> </in> </execute-procedure> </then> </when> <!-- ===================================================================== --> <!-- Si el documento ha evolucionado en importaciones, no se permite --> <!-- modificar dosier, contenedores ni despachos(aduana y transito) --> <!-- ===================================================================== --> <when> <expr> <nxt>impnxt</nxt> = 1 AND (<nvl><prv>conten</prv>,<whitespace /></nvl> != <nvl><nxt>conten</nxt>,<whitespace /></nvl> OR <nvl><prv>dosier</prv>,<whitespace /></nvl> != <nvl><nxt>dosier</nxt>,<whitespace /></nvl> OR <nvl><prv>destra</prv>,<whitespace /></nvl> != <nvl><nxt>destra</nxt>,<whitespace /></nvl> OR <nvl><prv>desadu</prv>,<whitespace /></nvl> != <nvl><nxt>desadu</nxt>,<whitespace /></nvl>) </expr> <then> <execute-procedure name='sdm_raise_msg'> <in> <param>0</param> <param>'Movimiento [' || <trim><nxt>docser</nxt></trim> || '] evolucionado. No se puede cambiar datos de importaciones'</param> </in> </execute-procedure> </then> </when> <!-- ===================================================================== --> <!-- Resetear los campos movest y movis a 0 ante cualquier cambio, salvo --> <!-- los cambios sobre sí mismos --> <!-- ===================================================================== --> <when> <expr> <prv>movest</prv> = 1 AND <prv>movest</prv> = <nxt>movest</nxt> AND <prv>movhis</prv> = <nxt>movhis</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-function name='sdm_set_value' into='movest'>'0'</execute-function> </then> </when> <when> <expr> <prv>movhis</prv> = 1 AND <prv>movhis</prv> = <nxt>movhis</nxt> AND (<prv>movest</prv> = <nxt>movest</nxt> OR <prv>movest</prv> = 1 AND <nxt>movest</nxt> = 0) </expr> <then> <execute-function name='sdm_set_value' into='movhis'>'0'</execute-function> </then> </when> <!-- ===================================================================== --> <!-- Registro de tracking para estadísticas --> <!-- ===================================================================== --> <when> <expr> <prv>movest</prv> != <nxt>movest</nxt> AND <nxt>movest</nxt> = 0 AND <nxt>movhis</nxt> != -1 </expr> <then> <insert table='gdoc_traspaso'> <column name='tabname'>'gcommovh'</column> <column name='colname'>'cabid'</column> <column name='colval'><nxt>cabid</nxt></column> <column name='tipope'>1</column> <column name='tippro'>0</column> </insert> </then> </when> <!-- ===================================================================== --> <!-- Registro de tracking para histórico --> <!-- ===================================================================== --> <when> <expr> <prv>movhis</prv> != <nxt>movhis</nxt> AND <nxt>movhis</nxt> = 0 AND <prv>movhis</prv> != -1 </expr> <then> <insert table='gdoc_traspaso'> <column name='tabname'>'gcommovh'</column> <column name='colname'>'cabid'</column> <column name='colval'><nxt>cabid</nxt></column> <column name='tipope'>1</column> <column name='tippro'>1</column> </insert> </then> </when> <!-- ===================================================================== --> <!-- Borrado de tracking para estadísticas --> <!-- ===================================================================== --> <when> <expr> <prv>movest</prv> != <nxt>movest</nxt> AND <nxt>movest</nxt> = 1 AND <nxt>movhis</nxt> != -1 </expr> <then> <delete table='gdoc_traspaso'> <where> tabname = 'gcommovh' AND colname = 'cabid' AND colval = <nxt>cabid</nxt> AND tippro = 0 </where> </delete> </then> </when> <!-- ===================================================================== --> <!-- Borrado de tracking para histórico --> <!-- ===================================================================== --> <when> <expr> <prv>movhis</prv> != <nxt>movhis</nxt> AND <nxt>movhis</nxt> = 1 AND <prv>movhis</prv> != -1 </expr> <then> <delete table='gdoc_traspaso'> <where> tabname = 'gcommovh' AND colname = 'cabid' AND colval = <nxt>cabid</nxt> AND tippro = 1 </where> </delete> </then> </when> <!-- ===================================================================== --> <!-- Si se cambia el estado del movimiento, o la fecha de recepcion y el --> <!-- tipo de movimiento actualiza 'stock planificado'; actualizar los --> <!-- movimientos planificados correspondientes. --> <!-- ===================================================================== --> <when> <expr> ((<prv>estcab</prv> = 'E' AND <nxt>estcab</nxt> != 'E') OR (<prv>estcab</prv> != 'E' AND <nxt>estcab</nxt> = 'E') OR <prv>fecrec</prv> != <nxt>fecrec</nxt>) AND <nxt>movhis</nxt> != -1 AND <expr-select> SELECT COUNT(*) <expr-into type='integer' /> FROM gcommovd WHERE codigo = <prv>tipdoc</prv> AND stkpla IN (1, 2, 3, 4) </expr-select> > 0 </expr> <then> <update table='gpro_stkplanm'> <column name='estado'>CASE WHEN <nxt>estcab</nxt> = 'E' THEN 'E' ELSE 'V' END</column> <column name='fecmov'><nxt>fecrec</nxt></column> <where> tabmov = 'gcommovh' AND docser = <prv>docser</prv> </where> </update> </then> </when> <!-- ===================================================================== --> <!-- Si existe una reserva de stock planificado para alguna línea del --> <!-- suministro y la fecha de recepción es anterior a la fecha de entrega --> <!-- del pedido de ventas, anular reserva. --> <!-- ===================================================================== --> <when> <expr> <prv>fecrec</prv> < <nxt>fecrec</nxt> AND <nxt>movhis</nxt> != -1 AND <expr-select> SELECT COUNT(*) <expr-into type='integer' /> FROM gcommovd WHERE codigo = <prv>tipdoc</prv> AND stkpla IN (1, 2, 3, 4) </expr-select> > 0 </expr> <then> <update table='greserva'> <column name='estlin'>'A'</column> <where> tabres = 'gvenpedh' AND tabapr = 'gcommovh' AND docapr = <nxt>docser</nxt> AND fecent < <nxt>fecrec</nxt> AND estlin != 'A' AND canpro != 0 </where> </update> </then> </when> <!-- ===================================================================== --> <!-- El coste medio pasa a 0 para obligar a modificar la fecha de última --> <!-- valoración de las líneas de suministros --> <!-- ===================================================================== --> <when> <expr> <nxt>fecmov</nxt> != <prv>fecmov</prv> AND <nxt>movhis</nxt> != -1 </expr> <then> <update table='gcommovl'> <column name='cosmed'>0</column> <where> cabid = <nxt>cabid</nxt> AND cosmed != 0 </where> </update> </then> </when> <!-- ===================================================================== --> <!-- Si se modifican datos que intervienen en valoración -> recalcular --> <!-- ===================================================================== --> <when mutating-reference='gcommovl'> <expr> (<prv>tercer</prv> != <nxt>tercer</nxt> OR <prv>delega</prv> != <nxt>delega</nxt> OR <prv>terenv</prv> != <nxt>terenv</nxt> OR <prv>divisa</prv> != <nxt>divisa</nxt>) AND <nxt>movhis</nxt> != -1 </expr> <then> <delete table='gcommovh_dtcl'> <where>cabid = <nxt>cabid</nxt></where> </delete> <delete table='gcommovl_dtlh'> <where>cabid = <nxt>cabid</nxt></where> </delete> <update table='gcommovl'> <column name='precio'>0</column> <column name='dtolin'>0</column> <column name='pretar'>NULL</column> <column name='dtotar'>NULL</column> <where>cabid = <nxt>cabid</nxt></where> </update> </then> </when> <!-- ===================================================================== --> <!-- Si se contabiliza el albarán y tiene activado el indicador de --> <!-- generar gastos aplicados de presupuestos al contabilizar. Eliminar --> <!-- los registros de compromisos de la inversión. --> <!-- ===================================================================== --> <when> <expr> <prv>fconta</prv> IS NULL AND <nxt>fconta</nxt> IS NOT NULL AND <nxt>movhis</nxt> != -1 AND <expr-select> SELECT COUNT(*) <expr-into type='integer' /> FROM gcommovd WHERE codigo = <prv>tipdoc</prv> AND cominv != 'N' AND aplcon != 0 </expr-select> > 0 </expr> <then> <delete table='cpar_premovi'> <where> tabori = 'gcomalbh' AND docser = <prv>docser</prv> AND estado = 'C' </where> </delete> </then> </when> <!-- ===================================================================== --> <!-- Si un albarán pasa a entrado y el tipo de documento indicaba --> <!-- que inserta obligaciones de inversión. Eliminar los registros --> <!-- de obligación de la inversión --> <!-- ===================================================================== --> <when> <expr> <prv>estcab</prv> = 'V' AND <nxt>estcab</nxt> = 'E' AND <nxt>movhis</nxt> != -1 AND <expr-select> SELECT COUNT(*) <expr-into type='integer' /> FROM gcommovd WHERE codigo = <prv>tipdoc</prv> AND cominv = 'S' </expr-select> > 0 </expr> <then> <delete table='cpar_premovi'> <where> tabori = 'gcomalbh' AND docser = <prv>docser</prv> </where> </delete> </then> </when> <!-- ===================================================================== --> <!-- Si el documento se ha cursado ya no se puede modificar --> <!-- [-] => [-] --> <!-- [P] => [V] --> <!-- [EV] => [P] --> <!-- ===================================================================== --> <when> <expr> <prv>estcab</prv> != <nxt>estcab</nxt> AND (<prv>estcab</prv> = 'P' AND <nxt>estcab</nxt> != 'V') AND (<prv>estcab</prv> != 'E' AND <prv>estcab</prv> != 'V') AND (<nxt>estcab</nxt> = 'E' OR <nxt>estcab</nxt> = 'V') AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='sdm_raise_msg'> <in> <param>0</param> <param>'Cabecera de transaccion cursada. No se puede alterar'</param> </in> </execute-procedure> </then> </when> <!-- ===================================================================== --> <!-- Si cambio el descuento general debo actualizar el acuerdo --> <!-- Se hace a partir de la suma de las lineas ya que el importe total --> <!-- total del documento no se tiene en el trigger sino con posterioridad --> <!-- cuando se revalora el documento --> <!-- ===================================================================== --> <when mutating='y'> <expr> <prv>dtogen</prv> != <nxt>dtogen</nxt> AND <nxt>docori</nxt> IS NOT NULL AND <nxt>movhis</nxt> != -1 AND <expr-select> SELECT COUNT(*) <expr-into type='integer' /> FROM gcommovd WHERE codigo = <nxt>tipdoc</nxt> AND tabori = 'MC' AND actori = 'S' </expr-select> > 0 </expr> <then> <update table='gcomacuh'> <column name='imptot'> imptot + (SELECT <nvl>SUM(impnet), 0</nvl> * (<nxt>dtogen</nxt> - <prv>dtogen</prv>) / 100 FROM gcommovl WHERE cabid = <nxt>cabid</nxt>)</column> <column name='user_updated'><user /></column> <column name='date_updated'><current /></column> <where> docser = <nxt>docori</nxt> </where> </update> </then> </when> <!-- ===================================================================== --> <!-- Si es un movimiento que genera factura si se modifica el descuento --> <!-- general en el movimiento se ha de modificar en la factura --> <!-- automáticamente. Lo mismo para el descuento pronto pago y fecha --> <!-- ===================================================================== --> <when> <expr> (<prv>dtogen</prv> != <nxt>dtogen</nxt> OR <prv>fecmov</prv> != <nxt>fecmov</nxt> OR <prv>dtopp</prv> != <nxt>dtopp</nxt>) AND <nxt>movhis</nxt> != -1 AND <expr-select> SELECT COUNT(*) <expr-into type='integer' /> FROM gcommovd WHERE codigo = <nxt>tipdoc</nxt> AND tabdes = 'FC' </expr-select> > 0 </expr> <then> <update table='gcomfach'> <column name='dtogen'><nxt>dtogen</nxt></column> <column name='dtopp'><nxt>dtopp</nxt></column> <column name='fecha'><nxt>fecmov</nxt></column> <column name='valor'><nxt>fecmov</nxt></column> <column name='user_updated'><user /></column> <column name='date_updated'><current /></column> <where> cabori = <nxt>cabid</nxt> </where> </update> </then> </when> <!-- ===================================================================== --> <!-- Invalidar autorizaciones para bloqueos relacionados con ciertos --> <!-- campos. --> <!-- ===================================================================== --> <when> <expr> <prv>tipdoc</prv> != <nxt>tipdoc</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'tipdoc'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>delega</prv> != <nxt>delega</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'delega'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>depart</prv> != <nxt>depart</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'depart'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>almori</prv> != <nxt>almori</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'almori'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>almdes</prv> != <nxt>almdes</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'almdes'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>refter</prv> != <nxt>refter</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'refter'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>tipefe</prv> != <nxt>tipefe</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'tipefe'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>frmpag</prv> != <nxt>frmpag</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'frmpag'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>imptot</prv> != <nxt>imptot</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'imptot'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>tercer</prv> != <nxt>tercer</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'tercer'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>tipdir</prv> != <nxt>tipdir</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'tipdir'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>terfac</prv> != <nxt>terfac</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'terfac'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>dirfac</prv> != <nxt>dirfac</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'dirfac'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>divisa</prv> != <nxt>divisa</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'divisa'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>dtogen</prv> != <nxt>dtogen</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'dtogen'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>dtopp</prv> != <nxt>dtopp</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'dtopp'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <nvl><prv>conten</prv>, <whitespace /></nvl> != <nvl><nxt>conten</nxt>, <whitespace /></nvl> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'conten'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <when> <expr> <prv>impres</prv> != <nxt>impres</nxt> AND <nxt>movhis</nxt> != -1 </expr> <then> <execute-procedure name='cerrauth_anular'> <in> <param>'gcommovh'</param> <param>'impres'</param> <param><nxt>cabid</nxt></param> <param>0</param> </in> </execute-procedure> </then> </when> <!-- ===================================================================== --> <!-- Inserción en la tabla de sincronización de documentos. --> <!-- Sincronización de documentos al validar. --> <!-- ===================================================================== --> <when> <expr><nxt>movhis</nxt> != -1</expr> <then> <execute-procedure name='csyn_document_inserta'> <in> <param>'gcommovh'</param> <param><nxt>tipdoc</nxt></param> <param><nxt>docser</nxt></param> </in> </execute-procedure> </then> </when> <!-- ===================================================================== --> <!-- Inserción en la tabla de sincronización de documentos. --> <!-- Sincronización de documentos al imprimir. --> <!-- ===================================================================== --> <when> <expr> <prv>impres</prv> != <nxt>impres</nxt> AND <nxt>impres</nxt> = 'S' AND <nxt>movhis</nxt> != -1 </expr> <then> <insert table='csyn_document' columns='tabid, docser, accion, progname, proces'> <select> <columns> tabid, <nxt>docser</nxt>, 2, progname, 0 </columns> <from table='csyn_tablas' /> <where> tabid = 'gcommovh' AND <nvl>tipdoc, <nxt>tipdoc</nxt></nvl> = <nxt>tipdoc</nxt> AND indnot = 2 </where> </select> </insert> </then> </when> <!-- ===================================================================== --> <!-- Posible código particularizado de customizacion. --> <!-- ===================================================================== --> <include name='after' /> </foreach-row> </xsql-trigger>
-- ************************************************************************** -- gcommovh_upd -- DEISTER WebStudio XSQL-TRIGGER Mon Oct 10 18:58:30 CEST 2011 Engine: ids -- ************************************************************************** CREATE TRIGGER gcommovh_upd UPDATE ON gcommovh REFERENCING NEW AS nxt OLD AS prv FOR EACH ROW -- Include [before] for trigger [gcommovh_upd] must be resolved at runtime WHEN (prv.tipdoc != nxt.tipdoc AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE sdm_raise_msg(0, 'No se puede modificar ciertos datos claves del movimiento') ), WHEN ( (prv.almori != nxt.almori OR prv.almdes != nxt.almdes OR (prv.almdes IS NULL AND nxt.almdes IS NOT NULL) OR (prv.almdes IS NOT NULL AND nxt.almdes IS NULL) OR NVL(prv.docori, '') != NVL(nxt.docori, '')) AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovl WHERE cabid = nxt.cabid) > 0 ) ( EXECUTE PROCEDURE sdm_raise_msg(0, 'No se puede modificar ciertos datos si hay líneas') ), WHEN (LENGTH(nxt.docori) > 0 AND (prv.delega != nxt.delega OR prv.tercer != nxt.tercer) AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE sdm_raise_msg(0, 'Mov. con documento origen, ciertos datos no se pueden modificar') ), WHEN ( (prv.delega != nxt.delega OR prv.tercer != nxt.tercer OR prv.terenv != nxt.terenv OR prv.divisa != nxt.divisa) AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = nxt.tipdoc AND tabdes = 'FC' ) = 0 AND ( SELECT COUNT(*) FROM gcommovl WHERE cabid = nxt.cabid AND estlin NOT IN ('E', 'V') ) > 0 ) ( EXECUTE PROCEDURE sdm_raise_msg(0, 'Mov. con lineas procesadas, ciertos datos no se pueden modificar') ), WHEN (nxt.fecmov != prv.fecmov AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd, galmctas a, galmctas b WHERE gcommovd.codigo = nxt.tipdoc AND gcommovd.ctaori = a.codigo AND gcommovd.ctades = b.codigo AND (a.indsal != 'N' OR b.indsal != 'N') ) > 0 AND ( SELECT COUNT(*) FROM galmacen WHERE (codigo = nxt.almori OR codigo = nxt.almdes) AND fecval IS NOT NULL AND (fecval >= nxt.fecmov OR fecval >= prv.fecmov) ) > 0 ) ( EXECUTE PROCEDURE sdm_raise_msg(0, 'Fecha movimiento anterior a la del ultimo cierre del almacen orig/dest') ), WHEN (nxt.impnxt = 1 AND (NVL(prv.conten,'') != NVL(nxt.conten,'') OR NVL(prv.dosier,'') != NVL(nxt.dosier,'') OR NVL(prv.destra,'') != NVL(nxt.destra,'') OR NVL(prv.desadu,'') != NVL(nxt.desadu,'')) ) ( EXECUTE PROCEDURE sdm_raise_msg(0, 'Movimiento [' || TRIM(nxt.docser) || '] evolucionado. No se puede cambiar datos de importaciones') ), WHEN (prv.movest = 1 AND prv.movest = nxt.movest AND prv.movhis = nxt.movhis AND nxt.movhis != -1 ) ( EXECUTE FUNCTION sdm_set_value('0') INTO movest ), WHEN (prv.movhis = 1 AND prv.movhis = nxt.movhis AND (prv.movest = nxt.movest OR prv.movest = 1 AND nxt.movest = 0) ) ( EXECUTE FUNCTION sdm_set_value('0') INTO movhis ), WHEN (prv.movest != nxt.movest AND nxt.movest = 0 AND nxt.movhis != -1 ) ( INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro) VALUES ('gcommovh', 'cabid', nxt.cabid, 1, 0) ), WHEN (prv.movhis != nxt.movhis AND nxt.movhis = 0 AND prv.movhis != -1 ) ( INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro) VALUES ('gcommovh', 'cabid', nxt.cabid, 1, 1) ), WHEN (prv.movest != nxt.movest AND nxt.movest = 1 AND nxt.movhis != -1 ) ( DELETE FROM gdoc_traspaso WHERE tabname = 'gcommovh' AND colname = 'cabid' AND colval = nxt.cabid AND tippro = 0 ), WHEN (prv.movhis != nxt.movhis AND nxt.movhis = 1 AND prv.movhis != -1 ) ( DELETE FROM gdoc_traspaso WHERE tabname = 'gcommovh' AND colname = 'cabid' AND colval = nxt.cabid AND tippro = 1 ), WHEN ( ((prv.estcab = 'E' AND nxt.estcab != 'E') OR (prv.estcab != 'E' AND nxt.estcab = 'E') OR prv.fecrec != nxt.fecrec) AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND stkpla IN (1, 2, 3, 4) ) > 0 ) ( UPDATE gpro_stkplanm SET estado=CASE WHEN nxt.estcab = 'E' THEN 'E' ELSE 'V' END, fecmov=nxt.fecrec WHERE tabmov = 'gcommovh' AND docser = prv.docser ), WHEN (prv.fecrec < nxt.fecrec AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND stkpla IN (1, 2, 3, 4) ) > 0 ) ( UPDATE greserva SET estlin='A' WHERE tabres = 'gvenpedh' AND tabapr = 'gcommovh' AND docapr = nxt.docser AND fecent < nxt.fecrec AND estlin != 'A' AND canpro != 0 ), WHEN (nxt.fecmov != prv.fecmov AND nxt.movhis != -1 ) ( UPDATE gcommovl SET cosmed=0 WHERE cabid = nxt.cabid AND cosmed != 0 ), WHEN ( (prv.tercer != nxt.tercer OR prv.delega != nxt.delega OR prv.terenv != nxt.terenv OR prv.divisa != nxt.divisa) AND nxt.movhis != -1 ) ( DELETE FROM gcommovh_dtcl WHERE cabid = nxt.cabid, DELETE FROM gcommovl_dtlh WHERE cabid = nxt.cabid, UPDATE gcommovl SET precio=0, dtolin=0, pretar=NULL, dtotar=NULL WHERE cabid = nxt.cabid ), WHEN (prv.fconta IS NULL AND nxt.fconta IS NOT NULL AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND cominv != 'N' AND aplcon != 0 ) > 0 ) ( DELETE FROM cpar_premovi WHERE tabori = 'gcomalbh' AND docser = prv.docser AND estado = 'C' ), WHEN (prv.estcab = 'V' AND nxt.estcab = 'E' AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND cominv = 'S' ) > 0 ) ( DELETE FROM cpar_premovi WHERE tabori = 'gcomalbh' AND docser = prv.docser ), WHEN (prv.estcab != nxt.estcab AND (prv.estcab = 'P' AND nxt.estcab != 'V') AND (prv.estcab != 'E' AND prv.estcab != 'V') AND (nxt.estcab = 'E' OR nxt.estcab = 'V') AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE sdm_raise_msg(0, 'Cabecera de transaccion cursada. No se puede alterar') ), WHEN (prv.dtogen != nxt.dtogen AND nxt.docori IS NOT NULL AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = nxt.tipdoc AND tabori = 'MC' AND actori = 'S' ) > 0 ) ( UPDATE gcomacuh SET imptot= imptot + (SELECT NVL(SUM(impnet), 0) * (nxt.dtogen - prv.dtogen) / 100 FROM gcommovl WHERE cabid = nxt.cabid), user_updated=USER, date_updated=CURRENT WHERE docser = nxt.docori ), WHEN ( (prv.dtogen != nxt.dtogen OR prv.fecmov != nxt.fecmov OR prv.dtopp != nxt.dtopp) AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = nxt.tipdoc AND tabdes = 'FC' ) > 0 ) ( UPDATE gcomfach SET dtogen=nxt.dtogen, dtopp=nxt.dtopp, fecha=nxt.fecmov, valor=nxt.fecmov, user_updated=USER, date_updated=CURRENT WHERE cabori = nxt.cabid ), WHEN (prv.tipdoc != nxt.tipdoc AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'tipdoc', nxt.cabid, 0) ), WHEN (prv.delega != nxt.delega AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'delega', nxt.cabid, 0) ), WHEN (prv.depart != nxt.depart AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'depart', nxt.cabid, 0) ), WHEN (prv.almori != nxt.almori AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'almori', nxt.cabid, 0) ), WHEN (prv.almdes != nxt.almdes AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'almdes', nxt.cabid, 0) ), WHEN (prv.refter != nxt.refter AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'refter', nxt.cabid, 0) ), WHEN (prv.tipefe != nxt.tipefe AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'tipefe', nxt.cabid, 0) ), WHEN (prv.frmpag != nxt.frmpag AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'frmpag', nxt.cabid, 0) ), WHEN (prv.imptot != nxt.imptot AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'imptot', nxt.cabid, 0) ), WHEN (prv.tercer != nxt.tercer AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'tercer', nxt.cabid, 0) ), WHEN (prv.tipdir != nxt.tipdir AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'tipdir', nxt.cabid, 0) ), WHEN (prv.terfac != nxt.terfac AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'terfac', nxt.cabid, 0) ), WHEN (prv.dirfac != nxt.dirfac AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'dirfac', nxt.cabid, 0) ), WHEN (prv.divisa != nxt.divisa AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'divisa', nxt.cabid, 0) ), WHEN (prv.dtogen != nxt.dtogen AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'dtogen', nxt.cabid, 0) ), WHEN (prv.dtopp != nxt.dtopp AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'dtopp', nxt.cabid, 0) ), WHEN (NVL(prv.conten, '') != NVL(nxt.conten, '') AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'conten', nxt.cabid, 0) ), WHEN (prv.impres != nxt.impres AND nxt.movhis != -1 ) ( EXECUTE PROCEDURE cerrauth_anular('gcommovh', 'impres', nxt.cabid, 0) ), WHEN (nxt.movhis != -1) ( EXECUTE PROCEDURE csyn_document_inserta('gcommovh', nxt.tipdoc, nxt.docser) ), WHEN (prv.impres != nxt.impres AND nxt.impres = 'S' AND nxt.movhis != -1 ) ( INSERT INTO csyn_document (tabid, docser, accion, progname, proces) SELECT tabid, nxt.docser, 2, progname, 0 FROM csyn_tablas WHERE tabid = 'gcommovh' AND NVL(tipdoc, nxt.tipdoc) = nxt.tipdoc AND indnot = 2 ) -- Include [after] for trigger [gcommovh_upd] must be resolved at runtime ;
-- ************************************************************************** -- gcommovh_upd -- DEISTER WebStudio XSQL-TRIGGER Mon Oct 10 18:58:30 CEST 2011 Engine: oracle -- ************************************************************************** -- ************************************************************************** -- PACKAGE FOR MUTATING TABLES USING METHOD ASSOCIATIVE ARRAYS gcommovh_pkg -- ************************************************************************** CREATE OR REPLACE PACKAGE gcommovh_pkg AS type ridArray is table of gcommovh%ROWTYPE index by binary_integer; type rowidArray is table of rowid index by binary_integer; nxtRows ridArray; prvRows ridArray; empty ridArray; rowids rowidArray; empty_rd rowidArray; END; . run -- ************************************************************************** -- TRIGGER: gcommovh_upd_b (BEFORE-update) -- ************************************************************************** CREATE OR REPLACE TRIGGER gcommovh_upd_b BEFORE UPDATE ON gcommovh DECLARE BEGIN gcommovh_pkg.nxtRows := gcommovh_pkg.empty; gcommovh_pkg.prvRows := gcommovh_pkg.empty; gcommovh_pkg.rowids := gcommovh_pkg.empty_rd; -- Instruccion nula por si el body del trigger esta vacio. null; END gcommovh_upd_b; . run -- ************************************************************************** -- TRIGGER: gcommovh_upd_pre (BEFORE-update-FOR EACH ROW) -- Functions and Procedures[out]. -- ************************************************************************** CREATE OR REPLACE TRIGGER gcommovh_upd_pre BEFORE UPDATE ON gcommovh REFERENCING NEW AS nxt OLD AS prv FOR EACH ROW DECLARE BEGIN -- Instruccion nula por si el body del trigger esta vacio. null; IF (:prv.movest = 1 AND :prv.movest = :nxt.movest AND :prv.movhis = :nxt.movhis AND :nxt.movhis != -1 ) THEN :nxt.movest := sdm_set_value('0'); END IF; IF (:prv.movhis = 1 AND :prv.movhis = :nxt.movhis AND (:prv.movest = :nxt.movest OR :prv.movest = 1 AND :nxt.movest = 0) ) THEN :nxt.movhis := sdm_set_value('0'); END IF; END gcommovh_upd_pre; . run -- ************************************************************************** -- TRIGGER: gcommovh_upd (AFTER-update-FOR EACH ROW) -- ************************************************************************** CREATE OR REPLACE TRIGGER gcommovh_upd AFTER UPDATE ON gcommovh REFERENCING NEW AS nxt OLD AS prv FOR EACH ROW DECLARE N65620 integer; N65696 integer; N65705 integer; N65736 integer; N65745 integer; N66048 integer; N66090 integer; N66220 integer; N66253 integer; N66399 integer; BEGIN -- Colocamos en el array de rowids en nxt, ya lo que utilizaremos en procedures OUT gcommovh_pkg.rowids(gcommovh_pkg.rowids.count+1) := :nxt.rowid; -- Para cada tag prv y nxt seteamos el valor en el vector correspondiente del package -- Block nxtRows. First line increments count (index of row). gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count+1).tipdoc := :nxt.tipdoc; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).almori := :nxt.almori; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).almdes := :nxt.almdes; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).almdes := :nxt.almdes; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).almdes := :nxt.almdes; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).docori := :nxt.docori; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).docori := :nxt.docori; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).delega := :nxt.delega; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tercer := :nxt.tercer; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).delega := :nxt.delega; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tercer := :nxt.tercer; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).terenv := :nxt.terenv; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).divisa := :nxt.divisa; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tipdoc := :nxt.tipdoc; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).fecmov := :nxt.fecmov; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tipdoc := :nxt.tipdoc; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).almori := :nxt.almori; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).almdes := :nxt.almdes; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).fecmov := :nxt.fecmov; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).impnxt := :nxt.impnxt; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).conten := :nxt.conten; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).dosier := :nxt.dosier; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).destra := :nxt.destra; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).desadu := :nxt.desadu; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).docser := :nxt.docser; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movest := :nxt.movest; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movest := :nxt.movest; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movest := :nxt.movest; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movest := :nxt.movest; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movest := :nxt.movest; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movest := :nxt.movest; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movest := :nxt.movest; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).estcab := :nxt.estcab; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).estcab := :nxt.estcab; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).fecrec := :nxt.fecrec; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).estcab := :nxt.estcab; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).fecrec := :nxt.fecrec; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).fecrec := :nxt.fecrec; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).docser := :nxt.docser; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).fecrec := :nxt.fecrec; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).fecmov := :nxt.fecmov; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tercer := :nxt.tercer; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).delega := :nxt.delega; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).terenv := :nxt.terenv; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).divisa := :nxt.divisa; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).fconta := :nxt.fconta; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).estcab := :nxt.estcab; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).estcab := :nxt.estcab; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).estcab := :nxt.estcab; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).estcab := :nxt.estcab; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).estcab := :nxt.estcab; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).dtogen := :nxt.dtogen; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).docori := :nxt.docori; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tipdoc := :nxt.tipdoc; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).dtogen := :nxt.dtogen; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).docori := :nxt.docori; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).dtogen := :nxt.dtogen; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).fecmov := :nxt.fecmov; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).dtopp := :nxt.dtopp; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tipdoc := :nxt.tipdoc; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).dtogen := :nxt.dtogen; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).dtopp := :nxt.dtopp; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).fecmov := :nxt.fecmov; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).fecmov := :nxt.fecmov; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tipdoc := :nxt.tipdoc; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).delega := :nxt.delega; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).depart := :nxt.depart; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).almori := :nxt.almori; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).almdes := :nxt.almdes; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).refter := :nxt.refter; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tipefe := :nxt.tipefe; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).frmpag := :nxt.frmpag; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).imptot := :nxt.imptot; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tercer := :nxt.tercer; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tipdir := :nxt.tipdir; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).terfac := :nxt.terfac; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).dirfac := :nxt.dirfac; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).divisa := :nxt.divisa; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).dtogen := :nxt.dtogen; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).dtopp := :nxt.dtopp; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).conten := :nxt.conten; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).impres := :nxt.impres; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).cabid := :nxt.cabid; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tipdoc := :nxt.tipdoc; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).docser := :nxt.docser; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).impres := :nxt.impres; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).impres := :nxt.impres; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).movhis := :nxt.movhis; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).docser := :nxt.docser; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tipdoc := :nxt.tipdoc; gcommovh_pkg.nxtRows(gcommovh_pkg.nxtRows.count).tipdoc := :nxt.tipdoc; -- Block prvRows. First line increments count (index of row). gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count+1).tipdoc := :prv.tipdoc; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).almori := :prv.almori; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).almdes := :prv.almdes; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).almdes := :prv.almdes; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).almdes := :prv.almdes; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).docori := :prv.docori; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).delega := :prv.delega; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).tercer := :prv.tercer; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).delega := :prv.delega; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).tercer := :prv.tercer; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).terenv := :prv.terenv; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).divisa := :prv.divisa; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).fecmov := :prv.fecmov; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).fecmov := :prv.fecmov; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).conten := :prv.conten; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).dosier := :prv.dosier; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).destra := :prv.destra; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).desadu := :prv.desadu; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movest := :prv.movest; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movest := :prv.movest; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movhis := :prv.movhis; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movhis := :prv.movhis; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movhis := :prv.movhis; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movest := :prv.movest; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movest := :prv.movest; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movest := :prv.movest; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movhis := :prv.movhis; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movhis := :prv.movhis; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movest := :prv.movest; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movhis := :prv.movhis; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).movhis := :prv.movhis; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).estcab := :prv.estcab; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).estcab := :prv.estcab; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).fecrec := :prv.fecrec; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).tipdoc := :prv.tipdoc; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).docser := :prv.docser; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).fecrec := :prv.fecrec; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).tipdoc := :prv.tipdoc; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).fecmov := :prv.fecmov; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).tercer := :prv.tercer; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).delega := :prv.delega; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).terenv := :prv.terenv; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).divisa := :prv.divisa; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).fconta := :prv.fconta; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).tipdoc := :prv.tipdoc; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).docser := :prv.docser; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).estcab := :prv.estcab; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).tipdoc := :prv.tipdoc; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).docser := :prv.docser; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).estcab := :prv.estcab; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).estcab := :prv.estcab; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).estcab := :prv.estcab; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).estcab := :prv.estcab; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).dtogen := :prv.dtogen; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).dtogen := :prv.dtogen; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).dtogen := :prv.dtogen; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).fecmov := :prv.fecmov; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).dtopp := :prv.dtopp; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).tipdoc := :prv.tipdoc; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).delega := :prv.delega; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).depart := :prv.depart; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).almori := :prv.almori; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).almdes := :prv.almdes; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).refter := :prv.refter; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).tipefe := :prv.tipefe; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).frmpag := :prv.frmpag; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).imptot := :prv.imptot; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).tercer := :prv.tercer; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).tipdir := :prv.tipdir; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).terfac := :prv.terfac; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).dirfac := :prv.dirfac; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).divisa := :prv.divisa; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).dtogen := :prv.dtogen; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).dtopp := :prv.dtopp; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).conten := :prv.conten; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).impres := :prv.impres; gcommovh_pkg.prvRows(gcommovh_pkg.prvRows.count).impres := :prv.impres; -- Instruccion nula por si el body del trigger esta vacio. null; -- Include [before] for trigger [gcommovh_upd] must be resolved at runtime IF (:prv.tipdoc != :nxt.tipdoc AND :nxt.movhis != -1 ) THEN sdm_raise_msg(0, 'No se puede modificar ciertos datos claves del movimiento'); END IF; IF ( (:prv.almori != :nxt.almori OR :prv.almdes != :nxt.almdes OR (:prv.almdes IS NULL AND :nxt.almdes IS NOT NULL) OR (:prv.almdes IS NOT NULL AND :nxt.almdes IS NULL) OR NVL(:prv.docori, ' ') != NVL(:nxt.docori, ' ')) AND :nxt.movhis != -1 ) THEN -- Variable ID: N65620 SELECT COUNT(*) INTO N65620 FROM gcommovl WHERE cabid = :nxt.cabid; IF (N65620 > 0 ) THEN sdm_raise_msg(0, 'No se puede modificar ciertos datos si hay líneas'); END IF; END IF; IF (LENGTH(TRIM(:nxt.docori)) > 0 AND (:prv.delega != :nxt.delega OR :prv.tercer != :nxt.tercer) AND :nxt.movhis != -1 ) THEN sdm_raise_msg(0, 'Mov. con documento origen, ciertos datos no se pueden modificar'); END IF; IF ( (:prv.delega != :nxt.delega OR :prv.tercer != :nxt.tercer OR :prv.terenv != :nxt.terenv OR :prv.divisa != :nxt.divisa) AND :nxt.movhis != -1 ) THEN -- Variable ID: N65696 SELECT COUNT(*) INTO N65696 FROM gcommovd WHERE codigo = :nxt.tipdoc AND tabdes = 'FC' ; SELECT COUNT(*) INTO N65705 FROM gcommovl WHERE cabid = :nxt.cabid AND estlin NOT IN ('E', 'V') ; IF (N65696 = 0 AND N65705 > 0 ) THEN sdm_raise_msg(0, 'Mov. con lineas procesadas, ciertos datos no se pueden modificar'); END IF; END IF; -- Variable ID: N65736 SELECT COUNT(*) INTO N65736 FROM gcommovd, galmctas a, galmctas b WHERE gcommovd.codigo = :nxt.tipdoc AND gcommovd.ctaori = a.codigo AND gcommovd.ctades = b.codigo AND (a.indsal != 'N' OR b.indsal != 'N') ; SELECT COUNT(*) INTO N65745 FROM galmacen WHERE (codigo = :nxt.almori OR codigo = :nxt.almdes) AND fecval IS NOT NULL AND (fecval >= :nxt.fecmov OR fecval >= :prv.fecmov) ; IF (:nxt.fecmov != :prv.fecmov AND :nxt.movhis != -1 AND N65736 > 0 AND N65745 > 0 ) THEN sdm_raise_msg(0, 'Fecha movimiento anterior a la del ultimo cierre del almacen orig/dest'); END IF; IF (:nxt.impnxt = 1 AND (NVL(:prv.conten,' ') != NVL(:nxt.conten,' ') OR NVL(:prv.dosier,' ') != NVL(:nxt.dosier,' ') OR NVL(:prv.destra,' ') != NVL(:nxt.destra,' ') OR NVL(:prv.desadu,' ') != NVL(:nxt.desadu,' ')) ) THEN sdm_raise_msg(0, 'Movimiento [' || TRIM(:nxt.docser) || '] evolucionado. No se puede cambiar datos de importaciones'); END IF; IF (:prv.movest != :nxt.movest AND :nxt.movest = 0 AND :nxt.movhis != -1 ) THEN INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro) VALUES ('gcommovh', 'cabid', :nxt.cabid, 1, 0); END IF; IF (:prv.movhis != :nxt.movhis AND :nxt.movhis = 0 AND :prv.movhis != -1 ) THEN INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro) VALUES ('gcommovh', 'cabid', :nxt.cabid, 1, 1); END IF; IF (:prv.movest != :nxt.movest AND :nxt.movest = 1 AND :nxt.movhis != -1 ) THEN DELETE FROM gdoc_traspaso WHERE tabname = 'gcommovh' AND colname = 'cabid' AND colval = :nxt.cabid AND tippro = 0 ; END IF; IF (:prv.movhis != :nxt.movhis AND :nxt.movhis = 1 AND :prv.movhis != -1 ) THEN DELETE FROM gdoc_traspaso WHERE tabname = 'gcommovh' AND colname = 'cabid' AND colval = :nxt.cabid AND tippro = 1 ; END IF; -- Variable ID: N66048 SELECT COUNT(*) INTO N66048 FROM gcommovd WHERE codigo = :prv.tipdoc AND stkpla IN (1, 2, 3, 4) ; IF ( ((:prv.estcab = 'E' AND :nxt.estcab != 'E') OR (:prv.estcab != 'E' AND :nxt.estcab = 'E') OR :prv.fecrec != :nxt.fecrec) AND :nxt.movhis != -1 AND N66048 > 0 ) THEN UPDATE gpro_stkplanm SET estado=CASE WHEN :nxt.estcab = 'E' THEN 'E' ELSE 'V' END, fecmov=:nxt.fecrec WHERE tabmov = 'gcommovh' AND docser = :prv.docser; END IF; -- Variable ID: N66090 SELECT COUNT(*) INTO N66090 FROM gcommovd WHERE codigo = :prv.tipdoc AND stkpla IN (1, 2, 3, 4) ; IF (:prv.fecrec < :nxt.fecrec AND :nxt.movhis != -1 AND N66090 > 0 ) THEN UPDATE greserva SET estlin='A' WHERE tabres = 'gvenpedh' AND tabapr = 'gcommovh' AND docapr = :nxt.docser AND fecent < :nxt.fecrec AND estlin != 'A' AND canpro != 0 ; END IF; IF (:nxt.fecmov != :prv.fecmov AND :nxt.movhis != -1 ) THEN UPDATE gcommovl SET cosmed=0 WHERE cabid = :nxt.cabid AND cosmed != 0 ; END IF; IF gcommovl_pkg.nxtRows.count = 0 THEN IF ( (:prv.tercer != :nxt.tercer OR :prv.delega != :nxt.delega OR :prv.terenv != :nxt.terenv OR :prv.divisa != :nxt.divisa) AND :nxt.movhis != -1 ) THEN DELETE FROM gcommovh_dtcl WHERE cabid = :nxt.cabid; DELETE FROM gcommovl_dtlh WHERE cabid = :nxt.cabid; UPDATE gcommovl SET precio=0, dtolin=0, pretar=NULL, dtotar=NULL WHERE cabid = :nxt.cabid; END IF; END IF; -- Variable ID: N66220 SELECT COUNT(*) INTO N66220 FROM gcommovd WHERE codigo = :prv.tipdoc AND cominv != 'N' AND aplcon != 0 ; IF (:prv.fconta IS NULL AND :nxt.fconta IS NOT NULL AND :nxt.movhis != -1 AND N66220 > 0 ) THEN DELETE FROM cpar_premovi WHERE tabori = 'gcomalbh' AND docser = :prv.docser AND estado = 'C' ; END IF; -- Variable ID: N66253 SELECT COUNT(*) INTO N66253 FROM gcommovd WHERE codigo = :prv.tipdoc AND cominv = 'S' ; IF (:prv.estcab = 'V' AND :nxt.estcab = 'E' AND :nxt.movhis != -1 AND N66253 > 0 ) THEN DELETE FROM cpar_premovi WHERE tabori = 'gcomalbh' AND docser = :prv.docser; END IF; IF (:prv.estcab != :nxt.estcab AND (:prv.estcab = 'P' AND :nxt.estcab != 'V') AND (:prv.estcab != 'E' AND :prv.estcab != 'V') AND (:nxt.estcab = 'E' OR :nxt.estcab = 'V') AND :nxt.movhis != -1 ) THEN sdm_raise_msg(0, 'Cabecera de transaccion cursada. No se puede alterar'); END IF; -- Variable ID: N66399 SELECT COUNT(*) INTO N66399 FROM gcommovd WHERE codigo = :nxt.tipdoc AND tabdes = 'FC' ; IF ( (:prv.dtogen != :nxt.dtogen OR :prv.fecmov != :nxt.fecmov OR :prv.dtopp != :nxt.dtopp) AND :nxt.movhis != -1 AND N66399 > 0 ) THEN UPDATE gcomfach SET dtogen=:nxt.dtogen, dtopp=:nxt.dtopp, fecha=:nxt.fecmov, valor=:nxt.fecmov, user_updated=USER, date_updated=CURRENT_TIMESTAMP WHERE cabori = :nxt.cabid; END IF; IF (:prv.tipdoc != :nxt.tipdoc AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'tipdoc', :nxt.cabid, 0); END IF; IF (:prv.delega != :nxt.delega AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'delega', :nxt.cabid, 0); END IF; IF (:prv.depart != :nxt.depart AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'depart', :nxt.cabid, 0); END IF; IF (:prv.almori != :nxt.almori AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'almori', :nxt.cabid, 0); END IF; IF (:prv.almdes != :nxt.almdes AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'almdes', :nxt.cabid, 0); END IF; IF (:prv.refter != :nxt.refter AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'refter', :nxt.cabid, 0); END IF; IF (:prv.tipefe != :nxt.tipefe AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'tipefe', :nxt.cabid, 0); END IF; IF (:prv.frmpag != :nxt.frmpag AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'frmpag', :nxt.cabid, 0); END IF; IF (:prv.imptot != :nxt.imptot AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'imptot', :nxt.cabid, 0); END IF; IF (:prv.tercer != :nxt.tercer AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'tercer', :nxt.cabid, 0); END IF; IF (:prv.tipdir != :nxt.tipdir AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'tipdir', :nxt.cabid, 0); END IF; IF (:prv.terfac != :nxt.terfac AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'terfac', :nxt.cabid, 0); END IF; IF (:prv.dirfac != :nxt.dirfac AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'dirfac', :nxt.cabid, 0); END IF; IF (:prv.divisa != :nxt.divisa AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'divisa', :nxt.cabid, 0); END IF; IF (:prv.dtogen != :nxt.dtogen AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'dtogen', :nxt.cabid, 0); END IF; IF (:prv.dtopp != :nxt.dtopp AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'dtopp', :nxt.cabid, 0); END IF; IF (NVL(:prv.conten, ' ') != NVL(:nxt.conten, ' ') AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'conten', :nxt.cabid, 0); END IF; IF (:prv.impres != :nxt.impres AND :nxt.movhis != -1 ) THEN cerrauth_anular('gcommovh', 'impres', :nxt.cabid, 0); END IF; IF (:nxt.movhis != -1) THEN csyn_document_inserta('gcommovh', :nxt.tipdoc, :nxt.docser); END IF; IF (:prv.impres != :nxt.impres AND :nxt.impres = 'S' AND :nxt.movhis != -1 ) THEN INSERT INTO csyn_document (tabid, docser, accion, progname, proces) SELECT tabid, :nxt.docser, 2, progname, 0 FROM csyn_tablas WHERE tabid = 'gcommovh' AND NVL(tipdoc, :nxt.tipdoc) = :nxt.tipdoc AND indnot = 2 ; END IF; -- Include [after] for trigger [gcommovh_upd] must be resolved at runtime END gcommovh_upd; . run -- ************************************************************************** -- TRIGGER: gcommovh_upd_a (AFTER-update) -- ************************************************************************** CREATE OR REPLACE TRIGGER gcommovh_upd_a AFTER UPDATE ON gcommovh DECLARE N66333 integer; BEGIN -- Instruccion nula por si el body del trigger esta vacio. null; IF gcommovl_pkg.nxtRows.count != 0 THEN FOR i IN 1 .. gcommovh_pkg.nxtRows.count LOOP IF ( (gcommovh_pkg.prvRows(i).tercer != gcommovh_pkg.nxtRows(i).tercer OR gcommovh_pkg.prvRows(i).delega != gcommovh_pkg.nxtRows(i).delega OR gcommovh_pkg.prvRows(i).terenv != gcommovh_pkg.nxtRows(i).terenv OR gcommovh_pkg.prvRows(i).divisa != gcommovh_pkg.nxtRows(i).divisa) AND gcommovh_pkg.nxtRows(i).movhis != -1 ) THEN DELETE FROM gcommovh_dtcl WHERE cabid = gcommovh_pkg.nxtRows(i).cabid; DELETE FROM gcommovl_dtlh WHERE cabid = gcommovh_pkg.nxtRows(i).cabid; UPDATE gcommovl SET precio=0, dtolin=0, pretar=NULL, dtotar=NULL WHERE cabid = gcommovh_pkg.nxtRows(i).cabid; END IF; END LOOP; END IF; FOR i IN 1 .. gcommovh_pkg.nxtRows.count LOOP -- Variable ID: N66333 SELECT COUNT(*) INTO N66333 FROM gcommovd WHERE codigo = gcommovh_pkg.nxtRows(i).tipdoc AND tabori = 'MC' AND actori = 'S' ; IF (gcommovh_pkg.prvRows(i).dtogen != gcommovh_pkg.nxtRows(i).dtogen AND gcommovh_pkg.nxtRows(i).docori IS NOT NULL AND gcommovh_pkg.nxtRows(i).movhis != -1 AND N66333 > 0 ) THEN UPDATE gcomacuh SET imptot= imptot + (SELECT NVL(SUM(impnet), 0) * (gcommovh_pkg.nxtRows(i).dtogen - gcommovh_pkg.prvRows(i).dtogen) / 100 FROM gcommovl WHERE cabid = gcommovh_pkg.nxtRows(i).cabid), user_updated=USER, date_updated=CURRENT_TIMESTAMP WHERE docser = gcommovh_pkg.nxtRows(i).docori; END IF; END LOOP; gcommovh_pkg.nxtRows := gcommovh_pkg.empty; gcommovh_pkg.prvRows := gcommovh_pkg.empty; END gcommovh_upd_a; . run
-- ************************************************************************** -- gcommovh_upd -- DEISTER WebStudio XSQL-TRIGGER Mon Oct 10 18:58:30 CEST 2011 Engine: postgres -- ************************************************************************** CREATE OR REPLACE FUNCTION func_gcommovh_upd() RETURNS TRIGGER AS $$ DECLARE N65620 integer; N65696 integer; N65705 integer; N65736 integer; N65745 integer; N66048 integer; N66090 integer; N66220 integer; N66253 integer; N66333 integer; N66399 integer; m_void CHAR(1); BEGIN -- Include [before] for trigger [gcommovh_upd] must be resolved at runtime IF (OLD.tipdoc != NEW.tipdoc AND NEW.movhis != -1 ) THEN m_void := sdm_raise_msg(0, 'No se puede modificar ciertos datos claves del movimiento'); END IF; IF ( (OLD.almori != NEW.almori OR OLD.almdes != NEW.almdes OR (OLD.almdes IS NULL AND NEW.almdes IS NOT NULL) OR (OLD.almdes IS NOT NULL AND NEW.almdes IS NULL) OR COALESCE(OLD.docori, '') != COALESCE(NEW.docori, '')) AND NEW.movhis != -1 ) THEN -- Variable ID: N65620 SELECT COUNT(*) INTO N65620 FROM gcommovl WHERE cabid = NEW.cabid; IF (N65620 > 0 ) THEN m_void := sdm_raise_msg(0, 'No se puede modificar ciertos datos si hay líneas'); END IF; END IF; IF (LENGTH(NEW.docori) > 0 AND (OLD.delega != NEW.delega OR OLD.tercer != NEW.tercer) AND NEW.movhis != -1 ) THEN m_void := sdm_raise_msg(0, 'Mov. con documento origen, ciertos datos no se pueden modificar'); END IF; IF ( (OLD.delega != NEW.delega OR OLD.tercer != NEW.tercer OR OLD.terenv != NEW.terenv OR OLD.divisa != NEW.divisa) AND NEW.movhis != -1 ) THEN -- Variable ID: N65696 SELECT COUNT(*) INTO N65696 FROM gcommovd WHERE codigo = NEW.tipdoc AND tabdes = 'FC' ; SELECT COUNT(*) INTO N65705 FROM gcommovl WHERE cabid = NEW.cabid AND estlin NOT IN ('E', 'V') ; IF (N65696 = 0 AND N65705 > 0 ) THEN m_void := sdm_raise_msg(0, 'Mov. con lineas procesadas, ciertos datos no se pueden modificar'); END IF; END IF; -- Variable ID: N65736 SELECT COUNT(*) INTO N65736 FROM gcommovd, galmctas a, galmctas b WHERE gcommovd.codigo = NEW.tipdoc AND gcommovd.ctaori = a.codigo AND gcommovd.ctades = b.codigo AND (a.indsal != 'N' OR b.indsal != 'N') ; SELECT COUNT(*) INTO N65745 FROM galmacen WHERE (codigo = NEW.almori OR codigo = NEW.almdes) AND fecval IS NOT NULL AND (fecval >= NEW.fecmov OR fecval >= OLD.fecmov) ; IF (NEW.fecmov != OLD.fecmov AND NEW.movhis != -1 AND N65736 > 0 AND N65745 > 0 ) THEN m_void := sdm_raise_msg(0, 'Fecha movimiento anterior a la del ultimo cierre del almacen orig/dest'); END IF; IF (NEW.impnxt = 1 AND (COALESCE(OLD.conten,'') != COALESCE(NEW.conten,'') OR COALESCE(OLD.dosier,'') != COALESCE(NEW.dosier,'') OR COALESCE(OLD.destra,'') != COALESCE(NEW.destra,'') OR COALESCE(OLD.desadu,'') != COALESCE(NEW.desadu,'')) ) THEN m_void := sdm_raise_msg(0, 'Movimiento [' || TRIM(NEW.docser) || '] evolucionado. No se puede cambiar datos de importaciones'); END IF; IF (OLD.movest = 1 AND OLD.movest = NEW.movest AND OLD.movhis = NEW.movhis AND NEW.movhis != -1 ) THEN SELECT * FROM sdm_set_value('0') INTO NEW.movest; END IF; IF (OLD.movhis = 1 AND OLD.movhis = NEW.movhis AND (OLD.movest = NEW.movest OR OLD.movest = 1 AND NEW.movest = 0) ) THEN SELECT * FROM sdm_set_value('0') INTO NEW.movhis; END IF; IF (OLD.movest != NEW.movest AND NEW.movest = 0 AND NEW.movhis != -1 ) THEN INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro) VALUES ('gcommovh', 'cabid', NEW.cabid, 1, 0); END IF; IF (OLD.movhis != NEW.movhis AND NEW.movhis = 0 AND OLD.movhis != -1 ) THEN INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro) VALUES ('gcommovh', 'cabid', NEW.cabid, 1, 1); END IF; IF (OLD.movest != NEW.movest AND NEW.movest = 1 AND NEW.movhis != -1 ) THEN DELETE FROM gdoc_traspaso WHERE tabname = 'gcommovh' AND colname = 'cabid' AND colval = NEW.cabid AND tippro = 0 ; END IF; IF (OLD.movhis != NEW.movhis AND NEW.movhis = 1 AND OLD.movhis != -1 ) THEN DELETE FROM gdoc_traspaso WHERE tabname = 'gcommovh' AND colname = 'cabid' AND colval = NEW.cabid AND tippro = 1 ; END IF; -- Variable ID: N66048 SELECT COUNT(*) INTO N66048 FROM gcommovd WHERE codigo = OLD.tipdoc AND stkpla IN (1, 2, 3, 4) ; IF ( ((OLD.estcab = 'E' AND NEW.estcab != 'E') OR (OLD.estcab != 'E' AND NEW.estcab = 'E') OR OLD.fecrec != NEW.fecrec) AND NEW.movhis != -1 AND N66048 > 0 ) THEN UPDATE gpro_stkplanm SET estado=CASE WHEN NEW.estcab = 'E' THEN 'E' ELSE 'V' END, fecmov=NEW.fecrec WHERE tabmov = 'gcommovh' AND docser = OLD.docser; END IF; -- Variable ID: N66090 SELECT COUNT(*) INTO N66090 FROM gcommovd WHERE codigo = OLD.tipdoc AND stkpla IN (1, 2, 3, 4) ; IF (OLD.fecrec < NEW.fecrec AND NEW.movhis != -1 AND N66090 > 0 ) THEN UPDATE greserva SET estlin='A' WHERE tabres = 'gvenpedh' AND tabapr = 'gcommovh' AND docapr = NEW.docser AND fecent < NEW.fecrec AND estlin != 'A' AND canpro != 0 ; END IF; IF (NEW.fecmov != OLD.fecmov AND NEW.movhis != -1 ) THEN UPDATE gcommovl SET cosmed=0 WHERE cabid = NEW.cabid AND cosmed != 0 ; END IF; IF ( (OLD.tercer != NEW.tercer OR OLD.delega != NEW.delega OR OLD.terenv != NEW.terenv OR OLD.divisa != NEW.divisa) AND NEW.movhis != -1 ) THEN DELETE FROM gcommovh_dtcl WHERE cabid = NEW.cabid; DELETE FROM gcommovl_dtlh WHERE cabid = NEW.cabid; UPDATE gcommovl SET precio=0, dtolin=0, pretar=NULL, dtotar=NULL WHERE cabid = NEW.cabid; END IF; -- Variable ID: N66220 SELECT COUNT(*) INTO N66220 FROM gcommovd WHERE codigo = OLD.tipdoc AND cominv != 'N' AND aplcon != 0 ; IF (OLD.fconta IS NULL AND NEW.fconta IS NOT NULL AND NEW.movhis != -1 AND N66220 > 0 ) THEN DELETE FROM cpar_premovi WHERE tabori = 'gcomalbh' AND docser = OLD.docser AND estado = 'C' ; END IF; -- Variable ID: N66253 SELECT COUNT(*) INTO N66253 FROM gcommovd WHERE codigo = OLD.tipdoc AND cominv = 'S' ; IF (OLD.estcab = 'V' AND NEW.estcab = 'E' AND NEW.movhis != -1 AND N66253 > 0 ) THEN DELETE FROM cpar_premovi WHERE tabori = 'gcomalbh' AND docser = OLD.docser; END IF; IF (OLD.estcab != NEW.estcab AND (OLD.estcab = 'P' AND NEW.estcab != 'V') AND (OLD.estcab != 'E' AND OLD.estcab != 'V') AND (NEW.estcab = 'E' OR NEW.estcab = 'V') AND NEW.movhis != -1 ) THEN m_void := sdm_raise_msg(0, 'Cabecera de transaccion cursada. No se puede alterar'); END IF; -- Variable ID: N66333 SELECT COUNT(*) INTO N66333 FROM gcommovd WHERE codigo = NEW.tipdoc AND tabori = 'MC' AND actori = 'S' ; IF (OLD.dtogen != NEW.dtogen AND NEW.docori IS NOT NULL AND NEW.movhis != -1 AND N66333 > 0 ) THEN UPDATE gcomacuh SET imptot= imptot + (SELECT COALESCE(SUM(impnet), 0) * (NEW.dtogen - OLD.dtogen) / 100 FROM gcommovl WHERE cabid = NEW.cabid), user_updated=USER, date_updated=CURRENT_TIMESTAMP WHERE docser = NEW.docori; END IF; -- Variable ID: N66399 SELECT COUNT(*) INTO N66399 FROM gcommovd WHERE codigo = NEW.tipdoc AND tabdes = 'FC' ; IF ( (OLD.dtogen != NEW.dtogen OR OLD.fecmov != NEW.fecmov OR OLD.dtopp != NEW.dtopp) AND NEW.movhis != -1 AND N66399 > 0 ) THEN UPDATE gcomfach SET dtogen=NEW.dtogen, dtopp=NEW.dtopp, fecha=NEW.fecmov, valor=NEW.fecmov, user_updated=USER, date_updated=CURRENT_TIMESTAMP WHERE cabori = NEW.cabid; END IF; IF (OLD.tipdoc != NEW.tipdoc AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'tipdoc', NEW.cabid, 0); END IF; IF (OLD.delega != NEW.delega AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'delega', NEW.cabid, 0); END IF; IF (OLD.depart != NEW.depart AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'depart', NEW.cabid, 0); END IF; IF (OLD.almori != NEW.almori AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'almori', NEW.cabid, 0); END IF; IF (OLD.almdes != NEW.almdes AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'almdes', NEW.cabid, 0); END IF; IF (OLD.refter != NEW.refter AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'refter', NEW.cabid, 0); END IF; IF (OLD.tipefe != NEW.tipefe AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'tipefe', NEW.cabid, 0); END IF; IF (OLD.frmpag != NEW.frmpag AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'frmpag', NEW.cabid, 0); END IF; IF (OLD.imptot != NEW.imptot AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'imptot', NEW.cabid, 0); END IF; IF (OLD.tercer != NEW.tercer AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'tercer', NEW.cabid, 0); END IF; IF (OLD.tipdir != NEW.tipdir AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'tipdir', NEW.cabid, 0); END IF; IF (OLD.terfac != NEW.terfac AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'terfac', NEW.cabid, 0); END IF; IF (OLD.dirfac != NEW.dirfac AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'dirfac', NEW.cabid, 0); END IF; IF (OLD.divisa != NEW.divisa AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'divisa', NEW.cabid, 0); END IF; IF (OLD.dtogen != NEW.dtogen AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'dtogen', NEW.cabid, 0); END IF; IF (OLD.dtopp != NEW.dtopp AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'dtopp', NEW.cabid, 0); END IF; IF (COALESCE(OLD.conten, '') != COALESCE(NEW.conten, '') AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'conten', NEW.cabid, 0); END IF; IF (OLD.impres != NEW.impres AND NEW.movhis != -1 ) THEN m_void := cerrauth_anular('gcommovh', 'impres', NEW.cabid, 0); END IF; IF (NEW.movhis != -1) THEN m_void := csyn_document_inserta('gcommovh', NEW.tipdoc, NEW.docser); END IF; IF (OLD.impres != NEW.impres AND NEW.impres = 'S' AND NEW.movhis != -1 ) THEN INSERT INTO csyn_document (tabid, docser, accion, progname, proces) SELECT tabid, NEW.docser, 2, progname, 0 FROM csyn_tablas WHERE tabid = 'gcommovh' AND COALESCE(tipdoc, NEW.tipdoc) = NEW.tipdoc AND indnot = 2 ; END IF; -- Include [after] for trigger [gcommovh_upd] must be resolved at runtime RETURN NEW; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER gcommovh_upd AFTER UPDATE ON gcommovh FOR EACH ROW EXECUTE PROCEDURE func_gcommovh_upd();
-- ************************************************************************** -- gcommovh_upd -- DEISTER WebStudio XSQL-TRIGGER Mon Oct 10 18:58:30 CEST 2011 Engine: db2i -- ************************************************************************** CREATE TRIGGER gcommovh_upd BEFORE UPDATE ON gcommovh REFERENCING NEW AS nxt OLD AS prv FOR EACH ROW MODE DB2ROW BEGIN ATOMIC DECLARE m_var_N65832 varchar(20); DECLARE m_nxt_cabid integer; DECLARE m_nxt_tipdoc varchar(4); DECLARE m_nxt_docser varchar(20); -- Include [before] for trigger [gcommovh_upd] must be resolved at runtime IF prv.tipdoc != nxt.tipdoc AND nxt.movhis != -1 THEN THEN CALL sdm_raise_msg(0, 'No se puede modificar ciertos datos claves del movimiento'); END IF; IF (prv.almori != nxt.almori OR prv.almdes != nxt.almdes OR (prv.almdes IS NULL AND nxt.almdes IS NOT NULL) OR (prv.almdes IS NOT NULL AND nxt.almdes IS NULL) OR COALESCE(prv.docori, '') != COALESCE(nxt.docori, '')) AND nxt.movhis != -1 THEN IF ( SELECT COUNT(*) FROM gcommovl WHERE cabid = nxt.cabid) > 0 THEN THEN CALL sdm_raise_msg(0, 'No se puede modificar ciertos datos si hay líneas'); END IF; END IF; IF LENGTH(RTRIM(nxt.docori)) > 0 AND (prv.delega != nxt.delega OR prv.tercer != nxt.tercer) AND nxt.movhis != -1 THEN THEN CALL sdm_raise_msg(0, 'Mov. con documento origen, ciertos datos no se pueden modificar'); END IF; IF (prv.delega != nxt.delega OR prv.tercer != nxt.tercer OR prv.terenv != nxt.terenv OR prv.divisa != nxt.divisa) AND nxt.movhis != -1 THEN IF ( SELECT COUNT(*) FROM gcommovd WHERE codigo = nxt.tipdoc AND tabdes = 'FC' ) = 0 AND ( SELECT COUNT(*) FROM gcommovl WHERE cabid = nxt.cabid AND estlin NOT IN ('E', 'V') ) > 0 THEN THEN CALL sdm_raise_msg(0, 'Mov. con lineas procesadas, ciertos datos no se pueden modificar'); END IF; END IF; IF nxt.fecmov != prv.fecmov AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd, galmctas a, galmctas b WHERE gcommovd.codigo = nxt.tipdoc AND gcommovd.ctaori = a.codigo AND gcommovd.ctades = b.codigo AND (a.indsal != 'N' OR b.indsal != 'N') ) > 0 AND ( SELECT COUNT(*) FROM galmacen WHERE (codigo = nxt.almori OR codigo = nxt.almdes) AND fecval IS NOT NULL AND (fecval >= nxt.fecmov OR fecval >= prv.fecmov) ) > 0 THEN THEN CALL sdm_raise_msg(0, 'Fecha movimiento anterior a la del ultimo cierre del almacen orig/dest'); END IF; IF nxt.impnxt = 1 AND (COALESCE(prv.conten,'') != COALESCE(nxt.conten,'') OR COALESCE(prv.dosier,'') != COALESCE(nxt.dosier,'') OR COALESCE(prv.destra,'') != COALESCE(nxt.destra,'') OR COALESCE(prv.desadu,'') != COALESCE(nxt.desadu,'')) THEN THEN SET m_var_N65832 = 'Movimiento [' || RTRIM(nxt.docser) || '] evolucionado. No se puede cambiar datos de importaciones'; CALL sdm_raise_msg(0, m_var_N65832); END IF; IF prv.movest = 1 AND prv.movest = nxt.movest AND prv.movhis = nxt.movhis AND nxt.movhis != -1 THEN THEN SET nxt.movest = sdm_set_value('0'); END IF; IF prv.movhis = 1 AND prv.movhis = nxt.movhis AND (prv.movest = nxt.movest OR prv.movest = 1 AND nxt.movest = 0) THEN THEN SET nxt.movhis = sdm_set_value('0'); END IF; IF prv.movest != nxt.movest AND nxt.movest = 0 AND nxt.movhis != -1 THEN THEN INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro) VALUES ('gcommovh', 'cabid', nxt.cabid, 1, 0); END IF; IF prv.movhis != nxt.movhis AND nxt.movhis = 0 AND prv.movhis != -1 THEN THEN INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro) VALUES ('gcommovh', 'cabid', nxt.cabid, 1, 1); END IF; IF prv.movest != nxt.movest AND nxt.movest = 1 AND nxt.movhis != -1 THEN THEN DELETE FROM gdoc_traspaso WHERE tabname = 'gcommovh' AND colname = 'cabid' AND colval = nxt.cabid AND tippro = 0 ; END IF; IF prv.movhis != nxt.movhis AND nxt.movhis = 1 AND prv.movhis != -1 THEN THEN DELETE FROM gdoc_traspaso WHERE tabname = 'gcommovh' AND colname = 'cabid' AND colval = nxt.cabid AND tippro = 1 ; END IF; IF ((prv.estcab = 'E' AND nxt.estcab != 'E') OR (prv.estcab != 'E' AND nxt.estcab = 'E') OR prv.fecrec != nxt.fecrec) AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND stkpla IN (1, 2, 3, 4) ) > 0 THEN THEN UPDATE gpro_stkplanm SET estado=CASE WHEN nxt.estcab = 'E' THEN 'E' ELSE 'V' END, fecmov=nxt.fecrec WHERE tabmov = 'gcommovh' AND docser = prv.docser; END IF; IF prv.fecrec < nxt.fecrec AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND stkpla IN (1, 2, 3, 4) ) > 0 THEN THEN UPDATE greserva SET estlin='A' WHERE tabres = 'gvenpedh' AND tabapr = 'gcommovh' AND docapr = nxt.docser AND fecent < nxt.fecrec AND estlin != 'A' AND canpro != 0 ; END IF; IF nxt.fecmov != prv.fecmov AND nxt.movhis != -1 THEN THEN UPDATE gcommovl SET cosmed=0 WHERE cabid = nxt.cabid AND cosmed != 0 ; END IF; IF (prv.tercer != nxt.tercer OR prv.delega != nxt.delega OR prv.terenv != nxt.terenv OR prv.divisa != nxt.divisa) AND nxt.movhis != -1 THEN THEN DELETE FROM gcommovh_dtcl WHERE cabid = nxt.cabid; DELETE FROM gcommovl_dtlh WHERE cabid = nxt.cabid; UPDATE gcommovl SET precio=0, dtolin=0, pretar=NULL, dtotar=NULL WHERE cabid = nxt.cabid; END IF; IF prv.fconta IS NULL AND nxt.fconta IS NOT NULL AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND cominv != 'N' AND aplcon != 0 ) > 0 THEN THEN DELETE FROM cpar_premovi WHERE tabori = 'gcomalbh' AND docser = prv.docser AND estado = 'C' ; END IF; IF prv.estcab = 'V' AND nxt.estcab = 'E' AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND cominv = 'S' ) > 0 THEN THEN DELETE FROM cpar_premovi WHERE tabori = 'gcomalbh' AND docser = prv.docser; END IF; IF prv.estcab != nxt.estcab AND (prv.estcab = 'P' AND nxt.estcab != 'V') AND (prv.estcab != 'E' AND prv.estcab != 'V') AND (nxt.estcab = 'E' OR nxt.estcab = 'V') AND nxt.movhis != -1 THEN THEN CALL sdm_raise_msg(0, 'Cabecera de transaccion cursada. No se puede alterar'); END IF; IF prv.dtogen != nxt.dtogen AND nxt.docori IS NOT NULL AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = nxt.tipdoc AND tabori = 'MC' AND actori = 'S' ) > 0 THEN THEN UPDATE gcomacuh SET imptot= imptot + (SELECT COALESCE(SUM(impnet), 0) * (nxt.dtogen - prv.dtogen) / 100 FROM gcommovl WHERE cabid = nxt.cabid), user_updated=USER, date_updated=CURRENT TIMESTAMP WHERE docser = nxt.docori; END IF; IF (prv.dtogen != nxt.dtogen OR prv.fecmov != nxt.fecmov OR prv.dtopp != nxt.dtopp) AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = nxt.tipdoc AND tabdes = 'FC' ) > 0 THEN THEN UPDATE gcomfach SET dtogen=nxt.dtogen, dtopp=nxt.dtopp, fecha=nxt.fecmov, valor=nxt.fecmov, user_updated=USER, date_updated=CURRENT TIMESTAMP WHERE cabori = nxt.cabid; END IF; IF prv.tipdoc != nxt.tipdoc AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'tipdoc', m_nxt_cabid, 0); END IF; IF prv.delega != nxt.delega AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'delega', m_nxt_cabid, 0); END IF; IF prv.depart != nxt.depart AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'depart', m_nxt_cabid, 0); END IF; IF prv.almori != nxt.almori AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'almori', m_nxt_cabid, 0); END IF; IF prv.almdes != nxt.almdes AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'almdes', m_nxt_cabid, 0); END IF; IF prv.refter != nxt.refter AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'refter', m_nxt_cabid, 0); END IF; IF prv.tipefe != nxt.tipefe AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'tipefe', m_nxt_cabid, 0); END IF; IF prv.frmpag != nxt.frmpag AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'frmpag', m_nxt_cabid, 0); END IF; IF prv.imptot != nxt.imptot AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'imptot', m_nxt_cabid, 0); END IF; IF prv.tercer != nxt.tercer AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'tercer', m_nxt_cabid, 0); END IF; IF prv.tipdir != nxt.tipdir AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'tipdir', m_nxt_cabid, 0); END IF; IF prv.terfac != nxt.terfac AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'terfac', m_nxt_cabid, 0); END IF; IF prv.dirfac != nxt.dirfac AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'dirfac', m_nxt_cabid, 0); END IF; IF prv.divisa != nxt.divisa AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'divisa', m_nxt_cabid, 0); END IF; IF prv.dtogen != nxt.dtogen AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'dtogen', m_nxt_cabid, 0); END IF; IF prv.dtopp != nxt.dtopp AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'dtopp', m_nxt_cabid, 0); END IF; IF COALESCE(prv.conten, '') != COALESCE(nxt.conten, '') AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'conten', m_nxt_cabid, 0); END IF; IF prv.impres != nxt.impres AND nxt.movhis != -1 THEN THEN SET m_nxt_cabid = nxt.cabid; CALL cerrauth_anular('gcommovh', 'impres', m_nxt_cabid, 0); END IF; IF nxt.movhis != -1 THEN THEN SET m_nxt_tipdoc = nxt.tipdoc; SET m_nxt_docser = nxt.docser; CALL csyn_document_inserta('gcommovh', m_nxt_tipdoc, m_nxt_docser); END IF; IF prv.impres != nxt.impres AND nxt.impres = 'S' AND nxt.movhis != -1 THEN THEN INSERT INTO csyn_document (tabid, docser, accion, progname, proces) SELECT tabid, nxt.docser, 2, progname, 0 FROM csyn_tablas WHERE tabid = 'gcommovh' AND COALESCE(tipdoc, nxt.tipdoc) = nxt.tipdoc AND indnot = 2 ; END IF; -- Include [after] for trigger [gcommovh_upd] must be resolved at runtime END;
-- ************************************************************************** -- gcommovh_upd -- DEISTER WebStudio XSQL-TRIGGER Mon Oct 10 18:58:30 CEST 2011 Engine: db2u -- ************************************************************************** CREATE TRIGGER gcommovh_upd BEFORE UPDATE ON gcommovh REFERENCING NEW AS nxt OLD AS prv FOR EACH ROW BEGIN ATOMIC -- Include [before] for trigger [gcommovh_upd] must be resolved at runtime IF prv.tipdoc != nxt.tipdoc AND nxt.movhis != -1 THEN CALL sdm_raise_msg(0, 'No se puede modificar ciertos datos claves del movimiento'); END IF; IF (prv.almori != nxt.almori OR prv.almdes != nxt.almdes OR (prv.almdes IS NULL AND nxt.almdes IS NOT NULL) OR (prv.almdes IS NOT NULL AND nxt.almdes IS NULL) OR COALESCE(prv.docori, '') != COALESCE(nxt.docori, '')) AND nxt.movhis != -1 THEN IF ( SELECT COUNT(*) FROM gcommovl WHERE cabid = nxt.cabid) > 0 THEN CALL sdm_raise_msg(0, 'No se puede modificar ciertos datos si hay líneas'); END IF; END IF; IF LENGTH(RTRIM(nxt.docori)) > 0 AND (prv.delega != nxt.delega OR prv.tercer != nxt.tercer) AND nxt.movhis != -1 THEN CALL sdm_raise_msg(0, 'Mov. con documento origen, ciertos datos no se pueden modificar'); END IF; IF (prv.delega != nxt.delega OR prv.tercer != nxt.tercer OR prv.terenv != nxt.terenv OR prv.divisa != nxt.divisa) AND nxt.movhis != -1 THEN IF ( SELECT COUNT(*) FROM gcommovd WHERE codigo = nxt.tipdoc AND tabdes = 'FC' ) = 0 AND ( SELECT COUNT(*) FROM gcommovl WHERE cabid = nxt.cabid AND estlin NOT IN ('E', 'V') ) > 0 THEN CALL sdm_raise_msg(0, 'Mov. con lineas procesadas, ciertos datos no se pueden modificar'); END IF; END IF; IF nxt.fecmov != prv.fecmov AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd, galmctas a, galmctas b WHERE gcommovd.codigo = nxt.tipdoc AND gcommovd.ctaori = a.codigo AND gcommovd.ctades = b.codigo AND (a.indsal != 'N' OR b.indsal != 'N') ) > 0 AND ( SELECT COUNT(*) FROM galmacen WHERE (codigo = nxt.almori OR codigo = nxt.almdes) AND fecval IS NOT NULL AND (fecval >= nxt.fecmov OR fecval >= prv.fecmov) ) > 0 THEN CALL sdm_raise_msg(0, 'Fecha movimiento anterior a la del ultimo cierre del almacen orig/dest'); END IF; IF nxt.impnxt = 1 AND (COALESCE(prv.conten,'') != COALESCE(nxt.conten,'') OR COALESCE(prv.dosier,'') != COALESCE(nxt.dosier,'') OR COALESCE(prv.destra,'') != COALESCE(nxt.destra,'') OR COALESCE(prv.desadu,'') != COALESCE(nxt.desadu,'')) THEN CALL sdm_raise_msg(0, 'Movimiento [' || RTRIM(nxt.docser) || '] evolucionado. No se puede cambiar datos de importaciones'); END IF; IF prv.movest = 1 AND prv.movest = nxt.movest AND prv.movhis = nxt.movhis AND nxt.movhis != -1 THEN SET nxt.movest = sdm_set_value('0'); END IF; IF prv.movhis = 1 AND prv.movhis = nxt.movhis AND (prv.movest = nxt.movest OR prv.movest = 1 AND nxt.movest = 0) THEN SET nxt.movhis = sdm_set_value('0'); END IF; IF prv.movest != nxt.movest AND nxt.movest = 0 AND nxt.movhis != -1 THEN INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro) VALUES ('gcommovh', 'cabid', nxt.cabid, 1, 0); END IF; IF prv.movhis != nxt.movhis AND nxt.movhis = 0 AND prv.movhis != -1 THEN INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro) VALUES ('gcommovh', 'cabid', nxt.cabid, 1, 1); END IF; IF prv.movest != nxt.movest AND nxt.movest = 1 AND nxt.movhis != -1 THEN DELETE FROM gdoc_traspaso WHERE tabname = 'gcommovh' AND colname = 'cabid' AND colval = nxt.cabid AND tippro = 0 ; END IF; IF prv.movhis != nxt.movhis AND nxt.movhis = 1 AND prv.movhis != -1 THEN DELETE FROM gdoc_traspaso WHERE tabname = 'gcommovh' AND colname = 'cabid' AND colval = nxt.cabid AND tippro = 1 ; END IF; IF ((prv.estcab = 'E' AND nxt.estcab != 'E') OR (prv.estcab != 'E' AND nxt.estcab = 'E') OR prv.fecrec != nxt.fecrec) AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND stkpla IN (1, 2, 3, 4) ) > 0 THEN UPDATE gpro_stkplanm SET estado=CASE WHEN nxt.estcab = 'E' THEN 'E' ELSE 'V' END, fecmov=nxt.fecrec WHERE tabmov = 'gcommovh' AND docser = prv.docser; END IF; IF prv.fecrec < nxt.fecrec AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND stkpla IN (1, 2, 3, 4) ) > 0 THEN UPDATE greserva SET estlin='A' WHERE tabres = 'gvenpedh' AND tabapr = 'gcommovh' AND docapr = nxt.docser AND fecent < nxt.fecrec AND estlin != 'A' AND canpro != 0 ; END IF; IF nxt.fecmov != prv.fecmov AND nxt.movhis != -1 THEN UPDATE gcommovl SET cosmed=0 WHERE cabid = nxt.cabid AND cosmed != 0 ; END IF; IF (prv.tercer != nxt.tercer OR prv.delega != nxt.delega OR prv.terenv != nxt.terenv OR prv.divisa != nxt.divisa) AND nxt.movhis != -1 THEN DELETE FROM gcommovh_dtcl WHERE cabid = nxt.cabid; DELETE FROM gcommovl_dtlh WHERE cabid = nxt.cabid; UPDATE gcommovl SET precio=0, dtolin=0, pretar=NULL, dtotar=NULL WHERE cabid = nxt.cabid; END IF; IF prv.fconta IS NULL AND nxt.fconta IS NOT NULL AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND cominv != 'N' AND aplcon != 0 ) > 0 THEN DELETE FROM cpar_premovi WHERE tabori = 'gcomalbh' AND docser = prv.docser AND estado = 'C' ; END IF; IF prv.estcab = 'V' AND nxt.estcab = 'E' AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND cominv = 'S' ) > 0 THEN DELETE FROM cpar_premovi WHERE tabori = 'gcomalbh' AND docser = prv.docser; END IF; IF prv.estcab != nxt.estcab AND (prv.estcab = 'P' AND nxt.estcab != 'V') AND (prv.estcab != 'E' AND prv.estcab != 'V') AND (nxt.estcab = 'E' OR nxt.estcab = 'V') AND nxt.movhis != -1 THEN CALL sdm_raise_msg(0, 'Cabecera de transaccion cursada. No se puede alterar'); END IF; IF prv.dtogen != nxt.dtogen AND nxt.docori IS NOT NULL AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = nxt.tipdoc AND tabori = 'MC' AND actori = 'S' ) > 0 THEN UPDATE gcomacuh SET imptot= imptot + (SELECT COALESCE(SUM(impnet), 0) * (nxt.dtogen - prv.dtogen) / 100 FROM gcommovl WHERE cabid = nxt.cabid), user_updated=USER, date_updated=CURRENT TIMESTAMP WHERE docser = nxt.docori; END IF; IF (prv.dtogen != nxt.dtogen OR prv.fecmov != nxt.fecmov OR prv.dtopp != nxt.dtopp) AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = nxt.tipdoc AND tabdes = 'FC' ) > 0 THEN UPDATE gcomfach SET dtogen=nxt.dtogen, dtopp=nxt.dtopp, fecha=nxt.fecmov, valor=nxt.fecmov, user_updated=USER, date_updated=CURRENT TIMESTAMP WHERE cabori = nxt.cabid; END IF; IF prv.tipdoc != nxt.tipdoc AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'tipdoc', nxt.cabid, 0); END IF; IF prv.delega != nxt.delega AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'delega', nxt.cabid, 0); END IF; IF prv.depart != nxt.depart AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'depart', nxt.cabid, 0); END IF; IF prv.almori != nxt.almori AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'almori', nxt.cabid, 0); END IF; IF prv.almdes != nxt.almdes AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'almdes', nxt.cabid, 0); END IF; IF prv.refter != nxt.refter AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'refter', nxt.cabid, 0); END IF; IF prv.tipefe != nxt.tipefe AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'tipefe', nxt.cabid, 0); END IF; IF prv.frmpag != nxt.frmpag AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'frmpag', nxt.cabid, 0); END IF; IF prv.imptot != nxt.imptot AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'imptot', nxt.cabid, 0); END IF; IF prv.tercer != nxt.tercer AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'tercer', nxt.cabid, 0); END IF; IF prv.tipdir != nxt.tipdir AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'tipdir', nxt.cabid, 0); END IF; IF prv.terfac != nxt.terfac AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'terfac', nxt.cabid, 0); END IF; IF prv.dirfac != nxt.dirfac AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'dirfac', nxt.cabid, 0); END IF; IF prv.divisa != nxt.divisa AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'divisa', nxt.cabid, 0); END IF; IF prv.dtogen != nxt.dtogen AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'dtogen', nxt.cabid, 0); END IF; IF prv.dtopp != nxt.dtopp AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'dtopp', nxt.cabid, 0); END IF; IF COALESCE(prv.conten, '') != COALESCE(nxt.conten, '') AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'conten', nxt.cabid, 0); END IF; IF prv.impres != nxt.impres AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'impres', nxt.cabid, 0); END IF; IF nxt.movhis != -1 THEN CALL csyn_document_inserta('gcommovh', nxt.tipdoc, nxt.docser); END IF; IF prv.impres != nxt.impres AND nxt.impres = 'S' AND nxt.movhis != -1 THEN INSERT INTO csyn_document (tabid, docser, accion, progname, proces) SELECT tabid, nxt.docser, 2, progname, 0 FROM csyn_tablas WHERE tabid = 'gcommovh' AND COALESCE(tipdoc, nxt.tipdoc) = nxt.tipdoc AND indnot = 2 ; END IF; -- Include [after] for trigger [gcommovh_upd] must be resolved at runtime END; -- ************************************************************************** -- gcommovh_upd_a -- DEISTER WebStudio XSQL-TRIGGER Mon Oct 10 18:58:30 CEST 2011 Engine: db2u -- ************************************************************************** CREATE TRIGGER gcommovh_upd_a AFTER UPDATE ON gcommovh REFERENCING NEW AS nxt OLD AS prv FOR EACH ROW MODE DB2SQL BEGIN ATOMIC DECLARE m_movest smallint; DECLARE m_movhis smallint; -- Include [before] for trigger [gcommovh_upd] must be resolved at runtime IF prv.tipdoc != nxt.tipdoc AND nxt.movhis != -1 THEN CALL sdm_raise_msg(0, 'No se puede modificar ciertos datos claves del movimiento'); END IF; IF (prv.almori != nxt.almori OR prv.almdes != nxt.almdes OR (prv.almdes IS NULL AND nxt.almdes IS NOT NULL) OR (prv.almdes IS NOT NULL AND nxt.almdes IS NULL) OR COALESCE(prv.docori, '') != COALESCE(nxt.docori, '')) AND nxt.movhis != -1 THEN IF ( SELECT COUNT(*) FROM gcommovl WHERE cabid = nxt.cabid) > 0 THEN CALL sdm_raise_msg(0, 'No se puede modificar ciertos datos si hay líneas'); END IF; END IF; IF LENGTH(RTRIM(nxt.docori)) > 0 AND (prv.delega != nxt.delega OR prv.tercer != nxt.tercer) AND nxt.movhis != -1 THEN CALL sdm_raise_msg(0, 'Mov. con documento origen, ciertos datos no se pueden modificar'); END IF; IF (prv.delega != nxt.delega OR prv.tercer != nxt.tercer OR prv.terenv != nxt.terenv OR prv.divisa != nxt.divisa) AND nxt.movhis != -1 THEN IF ( SELECT COUNT(*) FROM gcommovd WHERE codigo = nxt.tipdoc AND tabdes = 'FC' ) = 0 AND ( SELECT COUNT(*) FROM gcommovl WHERE cabid = nxt.cabid AND estlin NOT IN ('E', 'V') ) > 0 THEN CALL sdm_raise_msg(0, 'Mov. con lineas procesadas, ciertos datos no se pueden modificar'); END IF; END IF; IF nxt.fecmov != prv.fecmov AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd, galmctas a, galmctas b WHERE gcommovd.codigo = nxt.tipdoc AND gcommovd.ctaori = a.codigo AND gcommovd.ctades = b.codigo AND (a.indsal != 'N' OR b.indsal != 'N') ) > 0 AND ( SELECT COUNT(*) FROM galmacen WHERE (codigo = nxt.almori OR codigo = nxt.almdes) AND fecval IS NOT NULL AND (fecval >= nxt.fecmov OR fecval >= prv.fecmov) ) > 0 THEN CALL sdm_raise_msg(0, 'Fecha movimiento anterior a la del ultimo cierre del almacen orig/dest'); END IF; IF nxt.impnxt = 1 AND (COALESCE(prv.conten,'') != COALESCE(nxt.conten,'') OR COALESCE(prv.dosier,'') != COALESCE(nxt.dosier,'') OR COALESCE(prv.destra,'') != COALESCE(nxt.destra,'') OR COALESCE(prv.desadu,'') != COALESCE(nxt.desadu,'')) THEN CALL sdm_raise_msg(0, 'Movimiento [' || RTRIM(nxt.docser) || '] evolucionado. No se puede cambiar datos de importaciones'); END IF; IF prv.movest = 1 AND prv.movest = nxt.movest AND prv.movhis = nxt.movhis AND nxt.movhis != -1 THEN SET nxt.movest = sdm_set_value('0'); END IF; IF prv.movhis = 1 AND prv.movhis = nxt.movhis AND (prv.movest = nxt.movest OR prv.movest = 1 AND nxt.movest = 0) THEN SET nxt.movhis = sdm_set_value('0'); END IF; IF prv.movest != nxt.movest AND nxt.movest = 0 AND nxt.movhis != -1 THEN INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro) VALUES ('gcommovh', 'cabid', nxt.cabid, 1, 0); END IF; IF prv.movhis != nxt.movhis AND nxt.movhis = 0 AND prv.movhis != -1 THEN INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro) VALUES ('gcommovh', 'cabid', nxt.cabid, 1, 1); END IF; IF prv.movest != nxt.movest AND nxt.movest = 1 AND nxt.movhis != -1 THEN DELETE FROM gdoc_traspaso WHERE tabname = 'gcommovh' AND colname = 'cabid' AND colval = nxt.cabid AND tippro = 0 ; END IF; IF prv.movhis != nxt.movhis AND nxt.movhis = 1 AND prv.movhis != -1 THEN DELETE FROM gdoc_traspaso WHERE tabname = 'gcommovh' AND colname = 'cabid' AND colval = nxt.cabid AND tippro = 1 ; END IF; IF ((prv.estcab = 'E' AND nxt.estcab != 'E') OR (prv.estcab != 'E' AND nxt.estcab = 'E') OR prv.fecrec != nxt.fecrec) AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND stkpla IN (1, 2, 3, 4) ) > 0 THEN UPDATE gpro_stkplanm SET estado=CASE WHEN nxt.estcab = 'E' THEN 'E' ELSE 'V' END, fecmov=nxt.fecrec WHERE tabmov = 'gcommovh' AND docser = prv.docser; END IF; IF prv.fecrec < nxt.fecrec AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND stkpla IN (1, 2, 3, 4) ) > 0 THEN UPDATE greserva SET estlin='A' WHERE tabres = 'gvenpedh' AND tabapr = 'gcommovh' AND docapr = nxt.docser AND fecent < nxt.fecrec AND estlin != 'A' AND canpro != 0 ; END IF; IF nxt.fecmov != prv.fecmov AND nxt.movhis != -1 THEN UPDATE gcommovl SET cosmed=0 WHERE cabid = nxt.cabid AND cosmed != 0 ; END IF; IF (prv.tercer != nxt.tercer OR prv.delega != nxt.delega OR prv.terenv != nxt.terenv OR prv.divisa != nxt.divisa) AND nxt.movhis != -1 THEN DELETE FROM gcommovh_dtcl WHERE cabid = nxt.cabid; DELETE FROM gcommovl_dtlh WHERE cabid = nxt.cabid; UPDATE gcommovl SET precio=0, dtolin=0, pretar=NULL, dtotar=NULL WHERE cabid = nxt.cabid; END IF; IF prv.fconta IS NULL AND nxt.fconta IS NOT NULL AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND cominv != 'N' AND aplcon != 0 ) > 0 THEN DELETE FROM cpar_premovi WHERE tabori = 'gcomalbh' AND docser = prv.docser AND estado = 'C' ; END IF; IF prv.estcab = 'V' AND nxt.estcab = 'E' AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = prv.tipdoc AND cominv = 'S' ) > 0 THEN DELETE FROM cpar_premovi WHERE tabori = 'gcomalbh' AND docser = prv.docser; END IF; IF prv.estcab != nxt.estcab AND (prv.estcab = 'P' AND nxt.estcab != 'V') AND (prv.estcab != 'E' AND prv.estcab != 'V') AND (nxt.estcab = 'E' OR nxt.estcab = 'V') AND nxt.movhis != -1 THEN CALL sdm_raise_msg(0, 'Cabecera de transaccion cursada. No se puede alterar'); END IF; IF prv.dtogen != nxt.dtogen AND nxt.docori IS NOT NULL AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = nxt.tipdoc AND tabori = 'MC' AND actori = 'S' ) > 0 THEN UPDATE gcomacuh SET imptot= imptot + (SELECT COALESCE(SUM(impnet), 0) * (nxt.dtogen - prv.dtogen) / 100 FROM gcommovl WHERE cabid = nxt.cabid), user_updated=USER, date_updated=CURRENT TIMESTAMP WHERE docser = nxt.docori; END IF; IF (prv.dtogen != nxt.dtogen OR prv.fecmov != nxt.fecmov OR prv.dtopp != nxt.dtopp) AND nxt.movhis != -1 AND ( SELECT COUNT(*) FROM gcommovd WHERE codigo = nxt.tipdoc AND tabdes = 'FC' ) > 0 THEN UPDATE gcomfach SET dtogen=nxt.dtogen, dtopp=nxt.dtopp, fecha=nxt.fecmov, valor=nxt.fecmov, user_updated=USER, date_updated=CURRENT TIMESTAMP WHERE cabori = nxt.cabid; END IF; IF prv.tipdoc != nxt.tipdoc AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'tipdoc', nxt.cabid, 0); END IF; IF prv.delega != nxt.delega AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'delega', nxt.cabid, 0); END IF; IF prv.depart != nxt.depart AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'depart', nxt.cabid, 0); END IF; IF prv.almori != nxt.almori AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'almori', nxt.cabid, 0); END IF; IF prv.almdes != nxt.almdes AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'almdes', nxt.cabid, 0); END IF; IF prv.refter != nxt.refter AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'refter', nxt.cabid, 0); END IF; IF prv.tipefe != nxt.tipefe AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'tipefe', nxt.cabid, 0); END IF; IF prv.frmpag != nxt.frmpag AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'frmpag', nxt.cabid, 0); END IF; IF prv.imptot != nxt.imptot AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'imptot', nxt.cabid, 0); END IF; IF prv.tercer != nxt.tercer AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'tercer', nxt.cabid, 0); END IF; IF prv.tipdir != nxt.tipdir AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'tipdir', nxt.cabid, 0); END IF; IF prv.terfac != nxt.terfac AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'terfac', nxt.cabid, 0); END IF; IF prv.dirfac != nxt.dirfac AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'dirfac', nxt.cabid, 0); END IF; IF prv.divisa != nxt.divisa AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'divisa', nxt.cabid, 0); END IF; IF prv.dtogen != nxt.dtogen AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'dtogen', nxt.cabid, 0); END IF; IF prv.dtopp != nxt.dtopp AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'dtopp', nxt.cabid, 0); END IF; IF COALESCE(prv.conten, '') != COALESCE(nxt.conten, '') AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'conten', nxt.cabid, 0); END IF; IF prv.impres != nxt.impres AND nxt.movhis != -1 THEN CALL cerrauth_anular('gcommovh', 'impres', nxt.cabid, 0); END IF; IF nxt.movhis != -1 THEN CALL csyn_document_inserta('gcommovh', nxt.tipdoc, nxt.docser); END IF; IF prv.impres != nxt.impres AND nxt.impres = 'S' AND nxt.movhis != -1 THEN INSERT INTO csyn_document (tabid, docser, accion, progname, proces) SELECT tabid, nxt.docser, 2, progname, 0 FROM csyn_tablas WHERE tabid = 'gcommovh' AND COALESCE(tipdoc, nxt.tipdoc) = nxt.tipdoc AND indnot = 2 ; END IF; -- Include [after] for trigger [gcommovh_upd] must be resolved at runtime END;
Tipos de datos geométricos
Cuando la base de datos tiene tipos de datos geométricos, constituye un caso especial. Ofrecemos transportabilidad entre Oracle Spatial, DB2 Spatial Extender, Informix Spatial DataBlade y Postgres PostGIS. La gramática DML ofrece una amplia gama de funciones para escribir queries independientes de SQL y el motor de copia DB tranferir los datos de forma segura.
<select> <columns> d.the_geom, d.id, d.p1, d.p2, d.p3, d.p4, d.p5, d.p6 </columns> <from alias='d' > <select> <columns> bican_zonas_censales_data.p1, bican_zonas_censales_data.p2, bican_zonas_censales_data.p3, bican_zonas_censales_data.p4, bican_zonas_censales_data.p5, bican_zonas_censales_data.p6, bican_zonas_censales_data.id, <geo.asWkt>bican_zonas_censales.the_geom</geo.asWkt> the_geom </columns> <from alias='c' > <select> <columns> <geo.buffer> <geom1><geo.aggrUnion pg_collect='y'>bican_areas_influencia.the_geom</geo.aggrUnion></geom1> <dist>0</dist> </geo.buffer> the_geom </columns> <from table='bican_areas_influencia' /> <where> bican_areas_influencia.id_oficina = '#oficina' </where> </select> <join table='bican_zonas_censales'> <on> <geo.intersects> <geom1>c.the_geom</geom1> <geom2>bican_zonas_censales.the_geom</geom2> </geo.intersects> </on> </join> <join table='bican_zonas_censales_data'> <on>bican_zonas_censales.gid = bican_zonas_censales_data.id</on> </join> </from> </select> </from> </select>
-- ************************************************************************************ -- DEISTER WebStudio XSQL-SELECT Tue Oct 11 11:21:50 CEST 2011 Engine: ids -- ************************************************************************************ SELECT d.the_geom, d.id, d.p1, d.p2, d.p3, d.p4, d.p5, d.p6 FROM (SELECT bican_zonas_censales_data.p1, bican_zonas_censales_data.p2, bican_zonas_censales_data.p3, bican_zonas_censales_data.p4, bican_zonas_censales_data.p5, bican_zonas_censales_data.p6, bican_zonas_censales_data.id, ST_AsText(bican_zonas_censales.the_geom) the_geom FROM (SELECT ST_Buffer(SE_Dissolve(bican_areas_influencia.the_geom),0) the_geom FROM bican_areas_influencia WHERE bican_areas_influencia.id_oficina = '#oficina' ) c ,bican_zonas_censales ,bican_zonas_censales_data WHERE ST_Intersects(c.the_geom,bican_zonas_censales.the_geom) AND bican_zonas_censales.gid = bican_zonas_censales_data.id ) d WHERE ST_Intersects(c.the_geom,bican_zonas_censales.the_geom) AND bican_zonas_censales.gid = bican_zonas_censales_data.id
-- ************************************************************************************ -- DEISTER WebStudio XSQL-SELECT Tue Oct 11 11:21:50 CEST 2011 Engine: oracle -- ************************************************************************************ SELECT d.the_geom, d.id, d.p1, d.p2, d.p3, d.p4, d.p5, d.p6 FROM (SELECT bican_zonas_censales_data.p1, bican_zonas_censales_data.p2, bican_zonas_censales_data.p3, bican_zonas_censales_data.p4, bican_zonas_censales_data.p5, bican_zonas_censales_data.p6, bican_zonas_censales_data.id, SDO_UTIL.TO_WKTGEOMETRY(bican_zonas_censales.the_geom) the_geom FROM (SELECT SDO_GEOM.SDO_BUFFER(SDO_AGGR_UNION(SDOAGGRTYPE(bican_areas_influencia.the_geom,0.05)),0,0.05) the_geom FROM bican_areas_influencia WHERE bican_areas_influencia.id_oficina = '#oficina' ) c ,bican_zonas_censales ,bican_zonas_censales_data WHERE SDO_ANYINTERACT(c.the_geom,bican_zonas_censales.the_geom) AND bican_zonas_censales.gid = bican_zonas_censales_data.id ) d WHERE SDO_ANYINTERACT(c.the_geom,bican_zonas_censales.the_geom) AND bican_zonas_censales.gid = bican_zonas_censales_data.id
-- ************************************************************************************ -- DEISTER WebStudio XSQL-SELECT Tue Oct 11 11:21:50 CEST 2011 Engine: postgres -- ************************************************************************************ SELECT d.the_geom, d.id, d.p1, d.p2, d.p3, d.p4, d.p5, d.p6 FROM (SELECT bican_zonas_censales_data.p1, bican_zonas_censales_data.p2, bican_zonas_censales_data.p3, bican_zonas_censales_data.p4, bican_zonas_censales_data.p5, bican_zonas_censales_data.p6, bican_zonas_censales_data.id, ST_AsText(bican_zonas_censales.the_geom) the_geom FROM (SELECT ST_Buffer(ST_Collect(bican_areas_influencia.the_geom),0) the_geom FROM bican_areas_influencia WHERE bican_areas_influencia.id_oficina = '#oficina' ) c INNER JOIN bican_zonas_censales ON ST_Intersects(c.the_geom,bican_zonas_censales.the_geom) INNER JOIN bican_zonas_censales_data ON bican_zonas_censales.gid = bican_zonas_censales_data.id ) d
-- ************************************************************************************ -- DEISTER WebStudio XSQL-SELECT Tue Oct 11 11:21:50 CEST 2011 Engine: sqlserver -- ************************************************************************************ SELECT d.the_geom, d.id, d.p1, d.p2, d.p3, d.p4, d.p5, d.p6 FROM (SELECT bican_zonas_censales_data.p1, bican_zonas_censales_data.p2, bican_zonas_censales_data.p3, bican_zonas_censales_data.p4, bican_zonas_censales_data.p5, bican_zonas_censales_data.p6, bican_zonas_censales_data.id, bican_zonas_censales.the_geom.STAsText() the_geom FROM (SELECT -- Tag [geo.aggrUnion] not defined for engine sqlserver.BufferWithTolerance(0,,) the_geom FROM bican_areas_influencia WHERE bican_areas_influencia.id_oficina = '#oficina' ) c INNER JOIN bican_zonas_censales ON c.the_geom.STIntersects(bican_zonas_censales.the_geom) INNER JOIN bican_zonas_censales_data ON bican_zonas_censales.gid = bican_zonas_censales_data.id ) d
Debajo, hay una lista de las funciones geométricas que están disponibles con la gramática XML:
Informix Datablade | Postgres PostGis | Oracle Spatial | SQLServer Spatial | DB2 Spatial Extender | |
---|---|---|---|---|---|
ST_Point | ![]() |
![]() |
![]() |
![]() |
![]() |
ST_Line | ![]() |
![]() |
![]() |
![]() |
![]() |
St_Polygon | ![]() |
![]() |
![]() |
![]() |
![]() |
getType | ![]() |
![]() |
![]() |
![]() |
![]() |
getX | ![]() |
![]() |
![]() |
![]() |
![]() |
getY | ![]() |
![]() |
![]() |
![]() |
![]() |
getZ | ![]() |
![]() |
![]() |
![]() |
![]() |
getEnvelope | ![]() |
![]() |
![]() |
![]() |
![]() |
isValid | ![]() |
![]() |
![]() |
![]() |
![]() |
getCoordDim | ![]() |
![]() |
|||
getDim | ![]() |
![]() |
![]() |
![]() |
|
getSRID | ![]() |
![]() |
![]() |
![]() |
|
getNumVertices | ![]() |
![]() |
![]() |
||
asKml | ![]() |
![]() |
|||
asGml | ![]() |
![]() |
![]() |
||
asWkt | ![]() |
![]() |
![]() |
![]() |
|
asWkb | ![]() |
![]() |
![]() |
![]() |
|
asSvg | ![]() |
![]() |
|||
transform | ![]() |
![]() |
![]() |
![]() |
|
reverseLine | ![]() |
![]() |
![]() |
||
distanceCartesian | ![]() |
![]() |
![]() |
![]() |
![]() |
distanceSphere | ![]() |
![]() |
![]() |
![]() |
|
distanceSpheroid | ![]() |
![]() |
![]() |
![]() |
|
length | ![]() |
![]() |
![]() |
![]() |
![]() |
lengthSpheroid | ![]() |
![]() |
![]() |
![]() |
|
perimeter | ![]() |
![]() |
![]() |
![]() |
|
area | ![]() |
![]() |
![]() |
![]() |
|
centroid | ![]() |
![]() |
![]() |
![]() |
|
withinDist | ![]() |
![]() |
|||
equals | ![]() |
![]() |
![]() |
![]() |
![]() |
touches | ![]() |
![]() |
![]() |
![]() |
![]() |
overlaps | ![]() |
![]() |
![]() |
![]() |
![]() |
covers | ![]() |
![]() |
![]() |
![]() |
![]() |
coveredBy | ![]() |
![]() |
![]() |
||
contains | ![]() |
![]() |
![]() |
![]() |
![]() |
within | ![]() |
![]() |
![]() |
![]() |
![]() |
intersects | ![]() |
![]() |
![]() |
![]() |
![]() |
disjoint | ![]() |
![]() |
![]() |
![]() |
![]() |
intersection | ![]() |
![]() |
![]() |
![]() |
![]() |
buffer | ![]() |
![]() |
![]() |
![]() |
![]() |
simplify | ![]() |
![]() |
![]() |
![]() |
|
convexHull | ![]() |
![]() |
![]() |
![]() |
![]() |
difference | ![]() |
![]() |
![]() |
![]() |
|
symDifference | ![]() |
![]() |
![]() |
![]() |
|
aggrUnion | ![]() |
![]() |
![]() |
||
aggrMbr | ![]() |
![]() |
![]() |
||
aggrConvexHull | ![]() |
![]() |
![]() |