CREATE OR REPLACE TRIGGER INSERE_CARDACLI
AFTER INSERT
ON CARDADIA FOR EACH ROW
DECLARE
   V_DIASEMANA VARCHAR2(1);
   V_IDFERIADO VARCHAR2(1);
   V_QTCOMENSSERV FLOAT;
   V_PERCENTUAL FLOAT;
   V_QTCOMEST_SUM FLOAT;
   V_CDCLIENTE VARCHAR2(21);
   V_CDDESTFILI VARCHAR2(3);
BEGIN
   V_DIASEMANA := TO_CHAR(:NEW.DTCARDAPIO, 'D');

   IF :NEW.QTCOMESTICAR > 0 THEN

      SELECT DECODE(MAX(DTFERIADO), NULL, 'N', 'S')
        INTO V_IDFERIADO
      FROM(SELECT DTFERIFILI AS DTFERIADO
           FROM   FERIFILI F
           WHERE  F.CDFILIAL = :NEW.CDFILIAL  AND
                  DTFERIFILI = :NEW.DTCARDAPIO
           UNION ALL
           SELECT DTFERICARD AS DTFERIADO
           FROM   FERICARD
           WHERE  CDFILIAL = :NEW.CDFILIAL AND
                  DTFERICARD = :NEW.DTCARDAPIO
          );

      IF V_IDFERIADO = 'S' THEN
         V_DIASEMANA := '8';
      END IF;

      SELECT NVL(MAX(DECODE(V_DIASEMANA,
                           '1', QTCLIESTIDOM,
                           '2', QTCLIESTISEG,
                           '3', QTCLIESTITER,
                           '4', QTCLIESTIQUA,
                           '5', QTCLIESTIQUI,
                           '6', QTCLIESTISEX,
                           '7', QTCLIESTISAB,
                           '8', QTCLIESTIFER)), 0)
              INTO V_QTCOMENSSERV
      FROM SERVICO
      WHERE CDFILIAL = :NEW.CDFILIAL
        AND CDSERVFILI = :NEW.CDSERVFILI;

      IF (V_QTCOMENSSERV > 0) THEN
         V_PERCENTUAL := (:NEW.QTCOMESTICAR * 100)/V_QTCOMENSSERV;

         /* PONTOS DE ENTREGA */
         FOR C_PONTOENT IN (SELECT CDCLIENTE, CDDESTFILI,
                                   NVL(TRUNC(DECODE(V_DIASEMANA,
                                                   '1', QTCOESTCSDOM,
                                                   '2', QTCOESTCSSEG,
                                                   '3', QTCOESTCSTER,
                                                   '4', QTCOESTCSQUA,
                                                   '5', QTCOESTCSQUI,
                                                   '6', QTCOESTCSSEX,
                                                   '7', QTCOESTCSSAB,
                                                   '8', QTCOESTCSFER) * V_PERCENTUAL / 100), 0) QTCOESTCS
                            FROM DESTSRVCLI
                            WHERE CDFILIAL = :NEW.CDFILIAL
                              AND CDSERVFILI = :NEW.CDSERVFILI
                            ORDER BY CDCLIENTE
         )LOOP

            INSERT INTO CARDACLI
               (CDFILIAL, NRCARDAPIO, CDCLIENTE, CDDESTFILI, QTCOMESTICLI)
            VALUES
               (:NEW.CDFILIAL, :NEW.NRCARDAPIO, C_PONTOENT.CDCLIENTE, C_PONTOENT.CDDESTFILI, C_PONTOENT.QTCOESTCS);
         END LOOP;

         SELECT SUM(QTCOMESTICLI)
           INTO V_QTCOMEST_SUM
         FROM CARDACLI
         WHERE CDFILIAL = :NEW.CDFILIAL
           AND NRCARDAPIO = :NEW.NRCARDAPIO;
         
         V_QTCOMEST_SUM := NVL(V_QTCOMEST_SUM, 0.0);

         IF V_QTCOMEST_SUM > 0 AND (V_QTCOMEST_SUM <> :NEW.QTCOMESTICAR) THEN
            BEGIN
              SELECT CDCLIENTE, CDDESTFILI
                INTO V_CDCLIENTE, V_CDDESTFILI
              FROM CARDACLI
              WHERE CDFILIAL = :NEW.CDFILIAL
                AND NRCARDAPIO = :NEW.NRCARDAPIO
                AND QTCOMESTICLI > 0
                AND ROWNUM = 1;
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                V_CDCLIENTE := NULL;
                V_CDDESTFILI := NULL;
            END;
            IF V_CDCLIENTE IS NOT NULL THEN
              IF V_QTCOMEST_SUM < :NEW.QTCOMESTICAR THEN
                 UPDATE CARDACLI
                    SET QTCOMESTICLI = QTCOMESTICLI + (:NEW.QTCOMESTICAR - V_QTCOMEST_SUM)
                 WHERE CDFILIAL = :NEW.CDFILIAL
                   AND NRCARDAPIO = :NEW.NRCARDAPIO
                   AND CDCLIENTE = V_CDCLIENTE
                   AND CDDESTFILI = V_CDDESTFILI;
              ELSE
                 UPDATE CARDACLI
                    SET QTCOMESTICLI = QTCOMESTICLI - (V_QTCOMEST_SUM - :NEW.QTCOMESTICAR)
                 WHERE CDFILIAL = :NEW.CDFILIAL
                   AND NRCARDAPIO = :NEW.NRCARDAPIO
                   AND CDCLIENTE = V_CDCLIENTE
                   AND CDDESTFILI = V_CDDESTFILI;
              END IF;
            END IF;
         END IF;
      END IF;
      /* FIM PONTOS DE ENTREGA*/
   END IF;
END;
/