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 | |
|
|