CREATE OR REPLACE PROCEDURE EXCLUI_SUBGRUPO_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_NRORG IN NUMBER DEFAULT 1
)
AS
   V_IDSITUAPROPCIA VARCHAR2(1);
   V_IDALTERAAPROVADO VARCHAR2(1);
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 PROCIASUBSEMAM
   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;
         
  DELETE FROM PROPSUBGRUSE
   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;

  FOR CUR_CICLOS IN
  (SELECT CDCICLO
     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) LOOP
     EXCLUI_CICLO_MENUGRID(P_CDFILIAL, P_CDCIA, P_CDMANCIA, P_NRSERVICOCIAFILI, P_CDCLASSE, 
                           P_CDSUBGRU, P_NRSEQSUBGRUSEMAM, CUR_CICLOS.CDCICLO);
  END LOOP;
        
  DELETE FROM SUBGRUSEMAM
   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;
END;
/