CREATE OR REPLACE PROCEDURE DELETA_SERVICOPROPCOM
(
  P_NRSERVICOPROPCOM IN NUMBER
)
AS  
  V_IDSITUACAO VARCHAR2(30);
BEGIN
   V_IDSITUACAO := SIT_SERVICOPROPCOM(P_NRSERVICOPROPCOM);

   IF V_IDSITUACAO NOT IN ('PENDENTE', 'CALCULADO', 'FINALIZADOPLANEJAMENTO', 'RECUSADO') THEN
      RAISE_APPLICATION_ERROR(-20000, MSG('TCOPROC_SERVPROPCOMERCIAL_N_PODE_EXCLUIR'));
   END IF;

   -- Histrico de notificaes
   DELETE FROM HISTNOTIFPROPCOM
    WHERE NRSERVICOPROPCOM = P_NRSERVICOPROPCOM;

   -- limpa detalhes do servio
   UPDATE SERVICOPROPCOM SET NRDETALHESERVPROPCOM = NULL, NRDETSERVPROPCOMPLAN = NULL
    WHERE NRSERVICOPROPCOM = P_NRSERVICOPROPCOM;

   -- Configurao de servio
   DELETE FROM CONFIGSERVPROPCOM C
    WHERE EXISTS (
             SELECT 1
               FROM DETALHESERVPROPCOM D
              WHERE D.NRSERVICOPROPCOM = P_NRSERVICOPROPCOM
                AND D.NRDETALHESERVPROPCOM = C.NRDETALHESERVPROPCOM
          );
   
   -- Configurao de grupo de tipo de prato
   DELETE FROM CONFGRUPOTPPROPCOM C
    WHERE EXISTS (
             SELECT 1
               FROM DETALHESERVPROPCOM D
              WHERE D.NRSERVICOPROPCOM = P_NRSERVICOPROPCOM
                AND D.NRDETALHESERVPROPCOM = C.NRDETALHESERVPROPCOM
          );
   
   -- Incidncias solicitadas
   DELETE FROM INCIDENCIAPROPCOM I
    WHERE EXISTS (
             SELECT 1
               FROM DETALHESERVPROPCOM D
              WHERE D.NRSERVICOPROPCOM = P_NRSERVICOPROPCOM
                AND D.NRDETALHESERVPROPCOM = I.NRDETALHESERVPROPCOM
          );

   -- Produto
   DELETE FROM PRODUTOPROPCOM P
    WHERE EXISTS (
             SELECT 1
               FROM DETALHESERVPROPCOM D
              WHERE D.NRSERVICOPROPCOM = P_NRSERVICOPROPCOM
                AND D.NRDETALHESERVPROPCOM = P.NRDETALHESERVPROPCOM
          );

   -- Detalhes do Servio
   DELETE FROM DETALHESERVPROPCOM
    WHERE NRSERVICOPROPCOM = P_NRSERVICOPROPCOM;

   -- Servio
   DELETE FROM SERVICOPROPCOM
    WHERE NRSERVICOPROPCOM = P_NRSERVICOPROPCOM;   
END;
/