Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help with this TRIGGER

AlfonsoAlmonteJun 22 2015 — edited Jun 22 2015

I try to insert a register by the trigger show me this message

Pantallazo.png

CREATE OR REPLACE TRIGGER tr_insupd_cuotas

AFTER INSERT ON Recibo_Cobros

FOR EACH ROW

DECLARE v_transaccionid NUMBER;

v_cuotaid NUMBER;

v_fecha DATE;

v_fechacuota DATE;

v_fechacobro DATE;

v_cantidadplazo NUMBER;

v_plazoavencer NUMBER;

v_cobrado NUMBER(18,4);

v_fechatransaccion DATE;

v_montocuota NUMBER(18,4);

v_montotransaccion NUMBER;

v_metodoplazos CHAR(1);

v_saldocuota NUMBER(18,4);

v_cuotapagada CHAR(1);

v_capitalcobrado NUMBER(18,4);

v_mensual NUMBER := 1;

v_diasmes NUMBER;

v_montocobrado NUMBER(18,4):= :new.monto;

CURSOR c_transacciones

IS

SELECT t.transaccionid,

  c.cuotaid,

  c.fechacuota,

  c.saldocuota,

  t.cantidadplazo,

  c.cuotapagada,

  t.metodoplazos,

  t.fecha,

  t.monto,

  t.plazoavencer,

  t.capitalcobrado

FROM transacciones t

JOIN cuotas c

ON (c.transaccionid = t.transaccionid)

WHERE c.cuotapagada = 'F';

CURSOR c_cuotas

IS

SELECT SUM(montocuota) as montocuota

FROM cuotas

WHERE transaccionid = :new.transaccionid

AND cuotapagada = 'F'

GROUP BY transaccionid;

BEGIN

FOR transaccion IN c_transacciones

LOOP

v\_transaccionid    := transaccion.transaccionid;

v\_cuotaid          := transaccion.cuotaid;

v\_fechacuota       := transaccion.fechacuota;

v\_cantidadplazo    := transaccion.cantidadplazo;

v\_saldocuota       := transaccion.saldocuota;

v\_cuotapagada      := transaccion.cuotapagada;

v\_metodoplazos     := transaccion.metodoplazos;

v\_fecha            := transaccion.fecha;

v\_montotransaccion := transaccion.monto;

v\_plazoavencer     := transaccion.plazoavencer;

v\_capitalcobrado   := transaccion.capitalcobrado;

BEGIN

  IF (v\_metodoplazos = 'M') THEN

  FOR cuota IN c\_cuotas

  LOOP

    v\_montocuota    := cuota.montocuota;

    BEGIN

      WHILE (v\_montocobrado >= v\_montocuota)

      LOOP

        BEGIN

          v\_diasmes := to\_number(to\_char(LAST\_DAY(SYSDATE), 'DD'));

          v\_saldocuota := v\_montocuota;

          v\_montocuota :=  v\_montocobrado - v\_montocuota;

          v\_cuotapagada := 'T';

          v\_fecha := add\_months(v\_fecha, v\_mensual);

          v\_plazoavencer := v\_plazoavencer + v\_diasmes;

          IF (v\_plazoavencer > v\_cantidadplazo) THEN

            BEGIN

              v\_plazoavencer := v\_cantidadplazo;

            END;

          END IF;

          IF ((v\_plazoavencer = v\_cantidadplazo) and (v\_montocuota = 0)) THEN

                BEGIN

                  IF (v\_capitalcobrado >= 0) THEN

                    BEGIN

                      v\_capitalcobrado := v\_capitalcobrado + v\_montocobrado;

                    END;

                  END IF;

                END;

              END IF;

        END;

        UPDATE cuotas

          SET saldocuota = v\_saldocuota,

              cuotapagada = v\_cuotapagada

        WHERE transaccionid = v\_transaccionid;

        UPDATE transacciones

          SET fecha = v\_fecha,

              plazoavencer = v\_plazoavencer,

              capitalcobrado = v\_capitalcobrado

        WHERE transaccionid = v\_transaccionid;

      END LOOP;

    END;

  END LOOP;

  END IF;

END;

END LOOP;

END tr_insupd_cuotas;

Comments

user9540031
Answer

A quick look into the Globalization Support Guide, sections 5.9.1 Character Range '[x-y]' in Regular Expressions, and 5.5.2 Specifying a Case-Insensitive or Accent-Insensitive Collation, suggests that this is expected behaviour: a < A < b < B (etc.) is true in the GERMAN collating sequence (if I understand well). The reason why there is no match with 'aaaa' is because 'a' is not in the [A-Z] range, whereas 'b' is, when NLS_SORT = 'GERMAN'.
So in this case it would seem that 19.5 gives the right result, whereas 12.1 did not.

Marked as Answer by User_RVFU1 · Dec 18 2020
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 20 2015
Added on Jun 22 2015
2 comments
457 views