Contents
In comparison with standard hand made migration scheme, we offer a powerful set of tools developed and proven in complex relational database models. Depending on the nature of your system, these tools and our consulting specialists can ensure that your company’s database transition is smoothly accomplished.
From experience, we are familiar with the complexity and cost involved in a large database migration and the problems that arise in this process when inappropriate methods are employed. This is why we always check with a potential customer that the use of our tools and methods can offer a significant advantage.
Migration tools
In comparison with standard migration consultancy, which offers little more than database support, we can point to considerable experience in writing large enterprise applications in cross and native database syntax. Furthermore, we will teach your team a methodology and provide them with a powerful set of tools to reduce costs and optimize the migration process.
These tools include:
- Multi database copy tool with automatic conversion from data types (including geometric data types)
- Multi database schema check
- SQL XML grammar
- DDL XML grammar
- DML XML grammar
- SPL XML grammar
- Triggers XML grammar
- Support for conversion of geometric data types
Multi database copy
The copy tool is able to replicate all data from a target database to a destination, independently of the engine, creating tables, indexes, constraints and mapping data types when engines differ. With little effort, and after the time involved in copying you can see and explore your data in the new database. Of course, migration is not performed.
- Generates table structures according to target data types
- Automatically disables triggers and sequences during the copy process
- Automatically sets up sequence after a table copy
- Supports cross database rowid generation
- Supports geometric data type conversion allowing easy migration of spatial engines
- Supports after copy index and foreign key construction
- Supports after copy trigger and SPL compilation
Multi database schema check
Once you start a migration, you can generate an XML schema from your original database. This will let you translate the database model to any supported engine.
But what if your system continues to operate and even undergoes structural changes during the migration process? The schema check even compares databases of different types and shows differences in table structures, primary keys, foreign keys, indexes and constraints. A comparison can also be made with the master XML schema model. In both cases, a proposal for the changes to be applied to ensure that databases have the same physical structure is shown.
Support for development, test, pre-production and production systems
The migration tools are built around a database dictionary. This dictionary allows programmers to store their code (DML statements, SQL queries, SPL code, initial table data, etc.), which will constitute the applications database. Once stored in the dictionary, a set of web or command line commands enable the compilation, checking or release of upgrades to a database or a group of databases.
XML-XSQL syntax
The DDL, DML, SPL, trigger translation engine provides a framework with a common XML syntax in which developers can write applications in a database-independent syntax.
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
The database copy process can automatically create an XML model to generate the Data Definition Language of the database. You can see all your tables and objects defined in a natural XML definition that will allow on-line translation to the target database.
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>
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
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;
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
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 ;
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
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);
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
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);
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
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);
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
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);
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
DML
An XML grammar allows database-independent SQL statements to be written.
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>
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- ************************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- ************************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- ************************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- ************************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- ************************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
--************************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
Stored procedures
Business logic written in stored procedures, functions or triggers must be manually rewritten in XML. We offer this service ourselves or we can teach you how to do it. Thereafter, you will be able to automatically translate this logic to the target engine.
This step has a major advantage over conventional manual coding, since the AX XSQL translation engine will validate and generate the appropriate code without human errors.
The stored procedure manager will also take care of compilation status in target databases (development, test and production).
SPL - 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>
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- **************************************************************************
-- 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;
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- **************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- **************************************************************************
-- 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';
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- **************************************************************************
-- 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;
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- **************************************************************************
-- 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;
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- **************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
Triggers
If you have triggers, you are probably familiar with the complexity and differences involved in writing database-independent triggers. As in the case of stored procedures, you can use an XML grammar and the translation engine will generate the appropriate triggers for the target database.
Trigger - 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>
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- **************************************************************************
-- 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
;
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- **************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- **************************************************************************
-- 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();
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- **************************************************************************
-- 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;
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- **************************************************************************
-- 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;
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
Geometric data types
When a database has geometric data types, this constitutes a special case. We offer transportability between Oracle Spatial, DB2 Spatial Extender, Informix Spatial DataBlade and Postgres PostGis. The DML grammar offers a full set of functions to write SQL-independent queries and the DB copy engine will transfer data securely.
<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>
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- ************************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- ************************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- ************************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
-- ************************************************************************************
-- 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
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
Below is a list of the geometric functions supported that are available in the XML grammar:
| 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 |
This post/page is also available in: French, German, Spanish, Catalan, Portuguese (Brazil)
Blog
Blog feed (RSS2)
Deister Wiki
Users login @MyDeister.com
English 
Spanish 
German 
French 
Catalan 
Portuguese (Brazil) 
