CREATE OR REPLACE PROCEDURE EXCLUI_CKLISTPRODINF
(
P_CDFILIAL IN VARCHAR2,
P_NRCKLISTPRODINF IN NUMBER
)
AS
   V_IDSITUACAO VARCHAR2(30);
   V_NRREQUISICAOINF NUMBER;
   V_CDCKLISTPRODINF VARCHAR2(20);
BEGIN
   SELECT MAX(IDSITUACAO), MAX(NRREQUISICAOINF), MAX(CDCKLISTPRODINF)
     INTO V_IDSITUACAO, V_NRREQUISICAOINF, V_CDCKLISTPRODINF
     FROM CKLISTPRODINF
    WHERE CDFILIAL = P_CDFILIAL
      AND NRCKLISTPRODINF = P_NRCKLISTPRODINF;

   IF V_IDSITUACAO IS NULL THEN
      RETURN;
   END IF;

   IF V_IDSITUACAO <> 'PENDENTE' THEN
      /*Checklist [CDCKLISTPRODINF] esta aprovado*/
      RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00132' || '$$' ||
                                       V_CDCKLISTPRODINF
                                       || 'APPERRMSGEND');
   END IF;

   IF V_NRREQUISICAOINF IS NOT NULL THEN
      /*Checklist [CDCKLISTPRODINF] possui requisicao de numero [NRREQUISICAOINF] relacionada*/
      RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00133' || '$$' ||
                                       V_CDCKLISTPRODINF || '$$' ||
                                       TO_CHAR(V_NRREQUISICAOINF)
                                       || 'APPERRMSGEND');
   END IF;

   /*Apaga os destinos da receita*/
   DELETE FROM DESTCKLISTPRODINF
    WHERE CDFILIAL = P_CDFILIAL
      AND EXISTS (SELECT 1
                    FROM RECCKLISTPRODINF R
                   WHERE R.CDFILIAL = DESTCKLISTPRODINF.CDFILIAL
                     AND R.NRRECCKLISTPRODINF = DESTCKLISTPRODINF.NRRECCKLISTPRODINF
                     AND R.NRCKLISTPRODINF = P_NRCKLISTPRODINF);

   /*Apaga as justificativas de rejeitos / desperdicio*/
   DELETE FROM JUSREJDESP
    WHERE CDFILIAL = P_CDFILIAL      
      AND EXISTS (SELECT 1
                    FROM RECCKLISTPRODINF R
                   WHERE R.CDFILIAL = JUSREJDESP.CDFILIAL
                     AND R.NRRECCKLISTPRODINF = JUSREJDESP.NRRECCKLISTPRODINF
                     AND R.NRCKLISTPRODINF = P_NRCKLISTPRODINF);

   /*Apaga as receitas*/
   DELETE FROM RECCKLISTPRODINF
    WHERE CDFILIAL = P_CDFILIAL
      AND NRCKLISTPRODINF = P_NRCKLISTPRODINF;

   /*Apaga o checklist*/
   DELETE FROM CKLISTPRODINF
    WHERE CDFILIAL = P_CDFILIAL
      AND NRCKLISTPRODINF = P_NRCKLISTPRODINF;
END;
/