CREATE OR REPLACE PROCEDURE CANCELA_REQUI_RETIRADAINF
(
P_CDFILIAL IN VARCHAR2,
P_NRREQUISICAOINF IN NUMBER,
P_CDOPERADOR IN VARCHAR2,
P_NRORG IN NUMBER
)
AS
   V_NRRETIRADAINF NUMBER;
   V_IDSITUACAO VARCHAR2(30);
BEGIN
   SELECT MAX(NRRETIRADAINF)
     INTO V_NRRETIRADAINF
     FROM REQUISICAOINF
    WHERE CDFILIAL = P_CDFILIAL
      AND NRREQUISICAOINF = P_NRREQUISICAOINF;

   IF P_NRREQUISICAOINF IS NULL OR V_NRRETIRADAINF IS NULL THEN
      RETURN;
   END IF;

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

   SELECT MAX(IDSITUACAO)
     INTO V_IDSITUACAO
     FROM RETIRADAINF
    WHERE CDFILIAL = P_CDFILIAL
      AND NRRETIRADAINF = V_NRRETIRADAINF;

   /*Retirada nao estiver pendente*/
   IF V_IDSITUACAO <> 'PENDENTE' THEN
      /*Operacao bloqueada. Atendimento [NRRETIRADAINF] da requisicao [NRREQUISICAOINF] ja foi aprovado.*/
      RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00141' || '$$' || 
                                       TO_CHAR(V_NRRETIRADAINF) || '$$' ||
                                       TO_CHAR(P_NRREQUISICAOINF)
                                       || 'APPERRMSGEND');
   END IF;

   DELETE FROM PRODRETIRADAINF
    WHERE CDFILIAL = P_CDFILIAL
      AND NRRETIRADAINF = V_NRRETIRADAINF;

   DELETE FROM RETIRADAINF
    WHERE CDFILIAL = P_CDFILIAL
      AND NRRETIRADAINF = V_NRRETIRADAINF;

END;
/