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

   IF V_CDCKLISTPRODINF IS NULL OR V_NRREQUISICAOINF IS NULL THEN
      RETURN;
   END IF;

   /*Limpa a referencia da requisicao no checklist*/
   UPDATE CKLISTPRODINF
      SET NRREQUISICAOINF = NULL,
          NRORGULTATU = P_NRORG,
          CDOPERULTATU = P_CDOPERADOR,
          DTULTATU = SYSDATE
    WHERE CDFILIAL = P_CDFILIAL
      AND NRCKLISTPRODINF = P_NRCKLISTPRODINF;

   SELECT MAX(IDSITUACAO), MAX(NRRETIRADAINF)
     INTO V_IDSITUACAO, V_NRRETIRADAINF
     FROM REQUISICAOINF
    WHERE CDFILIAL = P_CDFILIAL
      AND NRREQUISICAOINF = V_NRREQUISICAOINF;

   /*Requisicao nao estiver pendente*/
   IF V_IDSITUACAO <> 'PENDENTE' THEN
      /*Operacao bloqueada. Requisicao [NRREQUISICAOINF] do checklist [CDCKLISTPROD] ja foi aprovada.*/
      RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00129' || '$$' || 
                                       TO_CHAR(V_NRREQUISICAOINF) || '$$' ||
                                       V_CDCKLISTPRODINF
                                       || 'APPERRMSGEND');
   END IF;

   /*Requisicao possuir retirada gerada*/
   IF V_NRRETIRADAINF IS NOT NULL THEN
      /*Operacao bloqueada. Atendimento [NRRETIRADAINF] ja gerado para a requisicao [NRREQUISICAOINF] do checklist [CDCKLISTPROD].*/
      RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00130' || '$$' || 
                                      TO_CHAR(V_NRRETIRADAINF) || '$$' ||
                                      TO_CHAR(V_NRREQUISICAOINF) || '$$' ||
                                      V_CDCKLISTPRODINF
                                      || 'APPERRMSGEND');
   END IF;

   DELETE FROM PRODREQUISICAOINF
    WHERE CDFILIAL = P_CDFILIAL
      AND NRREQUISICAOINF = V_NRREQUISICAOINF;

   DELETE FROM REQUISICAOINF
    WHERE CDFILIAL = P_CDFILIAL
      AND NRREQUISICAOINF = V_NRREQUISICAOINF;

END;
/