CREATE OR REPLACE PROCEDURE EXCLUI_CICLO_MENUGRID
(
  P_CDFILIAL IN VARCHAR2,
  P_CDCIA IN VARCHAR2,
  P_CDMANCIA IN VARCHAR2,
  P_NRSERVICOCIAFILI IN VARCHAR2,
  P_CDCLASSE IN VARCHAR2,
  P_CDSUBGRU IN VARCHAR2,
  P_NRSEQSUBGRUSEMAM IN VARCHAR2,
  P_CDCICLO IN VARCHAR2,
  P_NRORG IN NUMBER DEFAULT 1
)
AS
   V_IDSITUAPROPCIA VARCHAR2(1);
   V_IDALTERAAPROVADO VARCHAR2(1);
   V_FOTO VARCHAR2(2000);
   V_CONTADOR NUMBER;
BEGIN
    /*
  Verifica se o status do manual de destino
  */
  SELECT MAX(IDSITUAPROPCIA)
    INTO V_IDSITUAPROPCIA
    FROM MANUALCIAM
   WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND CDMANCIA = P_CDMANCIA;

  /*
  Verifica se e permitida alteracao no subgrupo
  */
  SELECT MAX(IDALTERAAPROVADO) 
    INTO V_IDALTERAAPROVADO
    FROM SUBGRUSEMAM
   WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND CDMANCIA = P_CDMANCIA
     AND NRSERVICOCIAFILI = P_NRSERVICOCIAFILI
     AND CDCLASSE = P_CDCLASSE
     AND P_CDSUBGRU = P_CDSUBGRU
     AND NRSEQSUBGRUSEMAM = P_NRSEQSUBGRUSEMAM;
  
  /*Operacao bloqueada. Manual de destino ja esta em proposta ou aprovado.*/
  IF V_IDSITUAPROPCIA NOT IN ('E', 'A') THEN
     IF V_IDALTERAAPROVADO <> 'S' THEN     
        RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00046' || 'APPERRMSGEND' , TRUE);
     END IF;
  END IF;

  DELETE FROM PRECOSESUBGRU
   WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND CDMANCIA = P_CDMANCIA
     AND NRSERVICOCIAFILI = P_NRSERVICOCIAFILI
     AND CDCLASSE = P_CDCLASSE
     AND CDSUBGRU = P_CDSUBGRU
     AND NRSEQSUBGRUSEMAM = P_NRSEQSUBGRUSEMAM
     AND CDCICLO = P_CDCICLO;
          
  DELETE FROM RECSUBSEMAM
  WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND CDMANCIA = P_CDMANCIA
     AND NRSERVICOCIAFILI = P_NRSERVICOCIAFILI
     AND CDCLASSE = P_CDCLASSE
     AND CDSUBGRU = P_CDSUBGRU
     AND NRSEQSUBGRUSEMAM = P_NRSEQSUBGRUSEMAM
     AND CDCICLO = P_CDCICLO;
  
  DELETE FROM PRODSUBSEMAM
   WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND CDMANCIA = P_CDMANCIA
     AND NRSERVICOCIAFILI = P_NRSERVICOCIAFILI
     AND CDCLASSE = P_CDCLASSE
     AND CDSUBGRU = P_CDSUBGRU
     AND NRSEQSUBGRUSEMAM = P_NRSEQSUBGRUSEMAM
     AND CDCICLO = P_CDCICLO;
  
  DELETE FROM MONTASUBSEMAM
  WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND CDMANCIA = P_CDMANCIA
     AND NRSERVICOCIAFILI = P_NRSERVICOCIAFILI
     AND CDCLASSE = P_CDCLASSE
     AND CDSUBGRU = P_CDSUBGRU
     AND NRSEQSUBGRUSEMAM = P_NRSEQSUBGRUSEMAM
     AND CDCICLO = P_CDCICLO;

  DELETE FROM SUBGRUPRINCIPALMENU
    WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND CDMANCIA = P_CDMANCIA
     AND NRSERVICOCIAFILI = P_NRSERVICOCIAFILI
     AND CDCLASSE = P_CDCLASSE
     AND CDSUBGRUPRIN = P_CDSUBGRU
     AND NRSEQSUBGRUSEMAMPRIN = P_NRSEQSUBGRUSEMAM
     AND CDCICLO = P_CDCICLO;

  DELETE FROM SUBGRUPRINCIPALMENU
    WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND CDMANCIA = P_CDMANCIA
     AND NRSERVICOCIAFILI = P_NRSERVICOCIAFILI
     AND CDCLASSE = P_CDCLASSE
     AND CDSUBGRU = P_CDSUBGRU
     AND NRSEQSUBGRUSEMAM = P_NRSEQSUBGRUSEMAM
     AND CDCICLO = P_CDCICLO;

  /*Verifica se a foto pode ser excluida do fileserver*/
  SELECT MAX(FGFOTOSUBSEMAM)
    INTO V_FOTO
    FROM CICLOSUBGRUSEMAM
   WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND CDMANCIA = P_CDMANCIA
     AND NRSERVICOCIAFILI = P_NRSERVICOCIAFILI
     AND CDCLASSE = P_CDCLASSE
     AND CDSUBGRU = P_CDSUBGRU
     AND NRSEQSUBGRUSEMAM = P_NRSEQSUBGRUSEMAM
     AND CDCICLO = P_CDCICLO;

  DELETE FROM CICLOSUBGRUSEMAM
   WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND CDMANCIA = P_CDMANCIA
     AND NRSERVICOCIAFILI = P_NRSERVICOCIAFILI
     AND CDCLASSE = P_CDCLASSE
     AND CDSUBGRU = P_CDSUBGRU
     AND NRSEQSUBGRUSEMAM = P_NRSEQSUBGRUSEMAM
     AND CDCICLO = P_CDCICLO;

  IF V_FOTO IS NOT NULL THEN
     SELECT COUNT(1)
       INTO V_CONTADOR
       FROM CICLOSUBGRUSEMAM
      WHERE FGFOTOSUBSEMAM = V_FOTO;

     IF V_CONTADOR > 0 THEN
        SELECT COUNT(1)
          INTO V_CONTADOR
          FROM FSGARBAGE
          WHERE FILEURL = V_FOTO;

          IF V_CONTADOR = 0 THEN
             INSERT INTO FSGARBAGE
             (FILEURL, NRORG)
             VALUES
             (V_FOTO, P_NRORG);
          END IF;
     END IF;
  END IF;
END;
/