DML Db2i

DML Db2i

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