DML XML

DML XML

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