/******************************************************************************/
/* Sistemas Integrados TECNISA                                                */
/*                                                                            */
/* Planejamento Alimentar Industrial e Hospitalar                             */
/*                                                                            */
/******************************************************************************/
/*                                                                            */
/* Nome: 0335_PLATRGO.sql                                                     */
/*                                                                            */
/* Descrição:                                                                 */
/* Ao excluir cardápio da tabela CARDADIA, é excluído também os efetivos      */
/* da tabela EFETCLIE(para o PLA) e EFETDIET(para o PAH)                      */
/*                                                                            */
/* Criação:                                                                   */
/* 11/01/2005 - Leonardo Leão de Assis                                        */
/*                                                                            */
/*                                                                            */
/*                                                                            */
/******************************************************************************/

CREATE OR REPLACE TRIGGER DELETE_EFETCLIE
BEFORE DELETE
ON CARDADIA FOR EACH ROW
DECLARE
   V_COUNT INTEGER;
BEGIN
   V_COUNT := 0;
   IF (NVL(:OLD.CDDIETPAD, '¿') = '¿') THEN

      SELECT
         COUNT(*) INTO V_COUNT
      FROM
         EFETCLIE
      WHERE
         CDFILIAL   = :OLD.CDFILIAL   AND
         CDSERVPLAN = :OLD.CDSERVFILI AND
         DTEFETCLIE = :OLD.DTCARDAPIO AND
         (CDFILIFATU IS NOT NULL AND NRAPURACFAT IS NOT NULL);
      IF (V_COUNT = 0) THEN
         DELETE FROM TURNOCLIE
         WHERE
            CDFILIAL   = :OLD.CDFILIAL AND
            CDSERVFILI = :OLD.CDSERVFILI AND
            DTFATTURNO = :OLD.DTCARDAPIO;

         DELETE FROM EFETCLIE
         WHERE
            CDFILIAL   = :OLD.CDFILIAL   AND
            CDSERVPLAN = :OLD.CDSERVFILI AND
            DTEFETCLIE = :OLD.DTCARDAPIO;
      END IF;
   ELSE

      DELETE FROM EFETDIET
      WHERE
         CDFILIAL   = :OLD.CDFILIAL   AND
         CDSERVFILI = :OLD.CDSERVFILI AND
         CDDIETPAD  = :OLD.CDDIETPAD  AND
         DTEFETDIET = :OLD.DTCARDAPIO;
   END IF;
END;