CREATE OR REPLACE PROCEDURE sp_fco_del_reportes
(
    pch_compania char,
    pti_cod_rep number,
    pch_operacion char
)
AS
/********************************************************************/
/*** Reportes e Interfases de Data Tecnologia                     ***/
/*** Descripcion: borra registros de las tablas fco_reportes      ***/
/*** y fco_reportes_cias, fco_reportes_par, fdt_rutas             ***/
/*** Autor:	Diana Cecilia Rios Peinado                            ***/
/*** Fecha:	Mayo 14 de 2005                                       ***/
/********************************************************************/
  vnm_existe      number(1);
  vvc_parametro   varchar2(10);
BEGIN--1
    vnm_existe := 0;
    BEGIN
      SELECT 1
      INTO vnm_existe
      FROM fco_reportes_cias
      WHERE cod_rep = pti_cod_rep AND compania <> pch_compania;
      EXCEPTION
           WHEN OTHERS THEN vnm_existe := 0;        
    END;
      
    IF vnm_existe = 1 THEN
        RAISE_APPLICATION_ERROR(-20001,'Este reporte no se puede eliminar porque tiene relación con otras compañías');
        RETURN;        
    END IF;
        
    vvc_parametro := 'report_' || pti_cod_rep;
    DELETE FROM fco_reportes_cias WHERE compania = pch_compania AND cod_rep = pti_cod_rep;
    DELETE FROM fco_reportes_par WHERE cod_rep = pti_cod_rep;
    DELETE FROM fdt_rutas WHERE codigo = pti_cod_rep AND operacion = pch_operacion;
    DELETE FROM fco_reportes WHERE cod_rep = pti_cod_rep;
    DELETE FROM fdt_parametros WHERE pais = 'COL' AND compania = pch_compania
    AND parametro = vvc_parametro;

    IF vnm_existe = 0 THEN
	    DELETE FROM fdt_parametros WHERE pais = 'COL' AND compania = '0'
	    AND parametro = vvc_parametro;
		END IF;
    
    COMMIT WORK;
    
END;--1
/
CREATE PUBLIC SYNONYM sp_fco_del_reportes FOR ADAM.sp_fco_del_reportes;
GRANT EXECUTE ON sp_fco_del_reportes TO adama;
