SELECT 
    co.razon_social compania,
    cl.descripcion clasificacion,
    rco.descripcion regional,
    r.descripcion riesgo,
    clr.porcentaje_comision,
    clr.porcentaje_comision_facul,
    clr.tasa,
    clr.porcentaje_financiamiento,
    fac.porcentaje,
    fac.porcentaje_credito,
    fac.porcentaje_credito_facultativo,
    fac.porcentaje_facultativo
FROM tec_compania co
INNER JOIN tec_clasificacion cl
ON co.id=cl.id_compania
INNER JOIN tec_regional_compania rco
ON co.id=rco.id_compania
INNER JOIN tec_clasificacion_riesgo clr
ON cl.id=clr.id_clasificacion AND rco.id=clr.id_regional_compania
INNER JOIN tec_riesgo r ON clr.id_riesgo=r.id
INNER JOIN tec_factor fac ON clr.id=fac.id_clasificacion_riesgo
WHERE co.id=27 AND cl.id=157 AND rco.id=315;

/********************* VERIFICAR SI EXISTE FORMULARIO PCC04 REGISTRADO (SI EXISTE NO ELIMINAR EL CERTIFICADO) *************************/
SELECT COM_DATOS_CERTIFICADO.ID_PCC04_POLIZA, UIF_PCC04_POLIZA.NUMERO_PCC04, UIF_PCC04_POLIZA.ESTADO
FROM BROKER.COM_DATOS_CERTIFICADO, BROKER.UIF_PCC04_POLIZA
WHERE COM_DATOS_CERTIFICADO.ID = 4116137
AND UIF_PCC04_POLIZA.ID = COM_DATOS_CERTIFICADO.ID_PCC04_POLIZA;

/********************* OBTENER TIPO DE CERTIFICADO (POR DEFECTO ES EL PRIMER REGISTRO) *************************/
SELECT BAN_TIPO_LIQ_SLIP_MADRE.*
FROM BROKER.COM_DATOS_CERTIFICADO,
BROKER.TEC_SLIP_MADRE_CABECERA,
BROKER.BAN_TIPO_LIQ_SLIP_MADRE
WHERE (COM_DATOS_CERTIFICADO.ID_SLIP_MADRE_CABECERA = TEC_SLIP_MADRE_CABECERA.ID)
AND (BAN_TIPO_LIQ_SLIP_MADRE.ID_SLIP_MADRE_CABECERA = TEC_SLIP_MADRE_CABECERA.ID)
AND (COM_DATOS_CERTIFICADO.ID = 4116137);


/******* tipo_certificado = 0 *******/
    SELECT ID_COMPROBANTE FROM BROKER.CON_PROVISION_CERTIFICADO WHERE ID_DATOS_CERTIFICADO = 4116137; --VERIFICAR SI HAY COMPROBANTE (SI EXISTE NO ELIMINAR EL COMPROBANTE YA QUE EXISTE RELACION)
    -- SI NO EXISTE EL COMPROBANTE
    DELETE FROM BROKER.CON_LIBERACION_CERTIFICADO 
    WHERE ID IN (
        SELECT CON_LIBERACION_CERTIFICADO.ID
        FROM BROKER.CON_LIBERACION_CERTIFICADO, BROKER.COM_DIFERIMIENTO_CERTIFICADO
        WHERE (CON_LIBERACION_CERTIFICADO.ID_DIFERIMIENTO_CERTIFICADO = COM_DIFERIMIENTO_CERTIFICADO.ID) AND (COM_DIFERIMIENTO_CERTIFICADO.ID_DATOS_CERTIFICADO = 4116137)
    );
    
    DELETE BROKER.CON_AJUSTE_COMISION_CERTIFICA WHERE
    CON_AJUSTE_COMISION_CERTIFICA.ID_DIFERIMIENTO_CERTIFICA
    IN (
        SELECT COM_DIFERIMIENTO_CERTIFICADO.ID
        FROM BROKER.COM_DIFERIMIENTO_CERTIFICADO  WHERE COM_DIFERIMIENTO_CERTIFICADO.ID_DATOS_CERTIFICADO = 4116137
    );
    
    DELETE FROM BROKER.COM_DIFERIMIENTO_CERTIFICADO WHERE ID_DATOS_CERTIFICADO =4116137;
    
    DELETE FROM BROKER.CON_PROVISION_CERTIFICADO WHERE ID_DATOS_CERTIFICADO =4116137;
    
/******* tipo_certificado = 1 (VALIDAR SI EXISTE UN COMPROBANTE - SI EXISTE NO ELIMINAR EL CERTIFICADO)*******/
    SELECT CON_PROVISION_BANCA_DESGRA.ID_COMPROBANTE
    FROM BROKER.BAN_DATOS_BANCA_SEGUROS,
    BROKER.BAN_LIQUIDACION_DESGRAVAMEN,
    BROKER.CON_PROVISION_BANCA_DESGRA
    WHERE (BAN_LIQUIDACION_DESGRAVAMEN.ID_DATOS_BANCA_SEGUROS = BAN_DATOS_BANCA_SEGUROS.ID)
    AND (CON_PROVISION_BANCA_DESGRA.ID_LIQUIDACION_DESGRAVAMEN = BAN_LIQUIDACION_DESGRAVAMEN.ID)
    AND (BAN_DATOS_BANCA_SEGUROS.ID_DATOS_CERTIFICADO = 4116137);
    
    SELECT CON_PROVISION_BANCA_CESAN.ID_COMPROBANTE
    FROM BROKER.BAN_DATOS_BANCA_SEGUROS,
    BROKER.BAN_LIQUIDACION_CESANTIA,
    BROKER.CON_PROVISION_BANCA_CESAN
    WHERE (BAN_LIQUIDACION_CESANTIA.ID_DATOS_BANCA_SEGUROS = BAN_DATOS_BANCA_SEGUROS.ID)
    AND (CON_PROVISION_BANCA_CESAN.ID_LIQUIDACION_CESANTIA = BAN_LIQUIDACION_CESANTIA.ID)
    AND (BAN_DATOS_BANCA_SEGUROS.ID_DATOS_CERTIFICADO = 4116137);

/******* tipo_certificado = 2 (VALIDAR SI EXISTE UN COMPROBANTE - SI EXISTE NO ELIMINAR EL CERTIFICADO)*******/
    SELECT CON_PROVISION_BANCA_DESGRA.ID_COMPROBANTE
    FROM BROKER.BAN_DATOS_BANCA_SEGUROS,
    BROKER.BAN_LIQUIDACION_DESGRAVAMEN,
    BROKER.CON_PROVISION_BANCA_DESGRA
    WHERE (BAN_LIQUIDACION_DESGRAVAMEN.ID_DATOS_BANCA_SEGUROS = BAN_DATOS_BANCA_SEGUROS.ID)
    AND (CON_PROVISION_BANCA_DESGRA.ID_LIQUIDACION_DESGRAVAMEN = BAN_LIQUIDACION_DESGRAVAMEN.ID)
    AND (BAN_DATOS_BANCA_SEGUROS.ID_DATOS_CERTIFICADO = 4116137);
    
    SELECT CON_PROVISION_BANCA_CESAN.ID_COMPROBANTE
    FROM BROKER.BAN_DATOS_BANCA_SEGUROS,
    BROKER.BAN_LIQUIDACION_CESANTIA,
    BROKER.CON_PROVISION_BANCA_CESAN
    WHERE (BAN_LIQUIDACION_CESANTIA.ID_DATOS_BANCA_SEGUROS = BAN_DATOS_BANCA_SEGUROS.ID)
    AND (CON_PROVISION_BANCA_CESAN.ID_LIQUIDACION_CESANTIA = BAN_LIQUIDACION_CESANTIA.ID)
    AND (BAN_DATOS_BANCA_SEGUROS.ID_DATOS_CERTIFICADO = 4116137);

/******* tipo_certificado = 3 (VALIDAR SI EXISTE UN COMPROBANTE - SI EXISTE NO ELIMINAR EL CERTIFICADO)*******/
    SELECT CON_PROVISION_BANCA_VIDA_TC.ID_COMPROBANTE
    FROM BROKER.BAN_DATOS_BANCA_SEGUROS,
    BROKER.BAN_LIQUIDACION_VIDA_TC,
    BROKER.CON_PROVISION_BANCA_VIDA_TC
    WHERE (BAN_LIQUIDACION_VIDA_TC.ID_DATOS_BANCA_SEGUROS = BAN_DATOS_BANCA_SEGUROS.ID)
    AND (CON_PROVISION_BANCA_VIDA_TC.ID_LIQUIDACION_VIDA_TC = BAN_LIQUIDACION_VIDA_TC.ID)
    AND (BAN_DATOS_BANCA_SEGUROS.ID_DATOS_CERTIFICADO = 4116137);

/******* tipo_certificado = 4 (VALIDAR SI EXISTE UN COMPROBANTE - SI EXISTE NO ELIMINAR EL CERTIFICADO)*******/
    SELECT CON_PROVISION_BANCA_AP_TC.ID_COMPROBANTE
    FROM BROKER.CON_PROVISION_BANCA_AP_TC,
    BROKER.BAN_LIQUIDACION_AP_TC,
    BROKER.BAN_DATO_BANCA_SEGUROS_TARJETA
    WHERE (CON_PROVISION_BANCA_AP_TC.ID_LIQUIDACION_AP_TC = BAN_LIQUIDACION_AP_TC.ID)
    AND (BAN_LIQUIDACION_AP_TC.ID_DATOS_BANCA_SEGUROS_TARJETA = BAN_DATO_BANCA_SEGUROS_TARJETA.ID)
    AND (BAN_DATO_BANCA_SEGUROS_TARJETA.ID_DATOS_CERTIFICADO = 4116137);
    
/******* tipo_certificado = 5 (VALIDAR SI EXISTE UN COMPROBANTE - SI EXISTE NO ELIMINAR EL CERTIFICADO)*******/
    SELECT CON_PROVISION_BANCA_AP_TD.ID_COMPROBANTE
    FROM BROKER.BAN_DATOS_BANCA_TARJETA_DEBI,
    BROKER.BAN_LIQUIDACION_AP_TD,
    BROKER.CON_PROVISION_BANCA_AP_TD
    WHERE (BAN_LIQUIDACION_AP_TD.ID_DATOS_SEGUROS_TARJETA_DEBI = BAN_DATOS_BANCA_TARJETA_DEBI.ID)
    AND (CON_PROVISION_BANCA_AP_TD.ID_LIQUIDACION_AP_TD = BAN_LIQUIDACION_AP_TD.ID)
    AND (BAN_DATOS_BANCA_TARJETA_DEBI.ID_DATOS_CERTIFICADO = 4116137);
    
/******* tipo_certificado = 6 (VALIDAR SI EXISTE UN COMPROBANTE - SI EXISTE NO ELIMINAR EL CERTIFICADO)*******/
    SELECT CON_PROVISION_BANCA_ROBO.ID_COMPROBANTE
    FROM BROKER.BAN_DATOS_BANCA_ROBO,
    BROKER.BAN_LIQUIDACION_ROBO,
    BROKER.CON_PROVISION_BANCA_ROBO
    WHERE (BAN_LIQUIDACION_ROBO.ID_DATOS_BANCA_ROBO = BAN_DATOS_BANCA_ROBO.ID)
    AND (CON_PROVISION_BANCA_ROBO.ID_LIQUIDACION_ROBO = BAN_LIQUIDACION_ROBO.ID)
    AND (BAN_DATOS_BANCA_ROBO.ID_DATOS_CERTIFICADO = 4116137);
    
/******* tipo_certificado = 9 *******/
    delete from BROKER.BAN_DATOS_TRDP where BAN_DATOS_TRDP.ID_DATOS_CERTIFICADO = 4116137;
    
/******* tipo_certificado = 11 *******/
    delete from BROKER.BAN_DATOS_TRDP_OLD where BAN_DATOS_TRDP_OLD.ID_DATOS_CERTIFICADO = 4116137;


/********************* SI NO EXISTE NINGUN COMPROBANTE RELACIONADO AL CERTIFICADO SEGUN EL TIPO DE CERTIFICADO PROCEDER A SU ELIMINACION **************************/

/********************* ELIMINAR BAN LIQUIDACION (EN CASO DE EXISTIR RELACION NO ELIMINAR EL CERTIFICADO) *************************/
DELETE FROM BROKER.BAN_LIQ_ESTADO_DETALLE WHERE ID IN (
    SELECT BAN_LIQ_ESTADO_DETALLE.ID
    FROM BROKER.BAN_LIQ_ESTADO_DETALLE, BROKER.BAN_LIQ_ESTADO_CABECERA
    WHERE BAN_LIQ_ESTADO_DETALLE.ID_LIQ_ESTADO_CABECERA = BAN_LIQ_ESTADO_CABECERA.ID
    AND BAN_LIQ_ESTADO_CABECERA.ESTADO = 0
    AND BAN_LIQ_ESTADO_DETALLE.ID_DATOS_CERTIFICADO=4116137
);

/**************************** ELIMINAR COM_AGENTE_DATOS_CERTIFICADO ************************/
DELETE
FROM BROKER.COM_AGENTE_DATOS_CERTIFICADO where ID_DATOS_CERTIFICADO = 4116137 AND ESTADO = 0;

/**************************** ELIMINAR COM_AGENTE_INTERNO_DATOS_CERT ************************/
DELETE
FROM BROKER.COM_AGENTE_INTERNO_DATOS_CERT where ID_DATOS_CERTIFICADO = 4116137 AND ESTADO = 0;

/**************************** ELIMINAR CON_INGRESO_PREVISION_CERTIFI ************************/
DELETE
FROM BROKER.CON_INGRESO_PREVISION_CERTIFI WHERE CON_INGRESO_PREVISION_CERTIFI.ID_DATOS_CERTIFICADO = 4116137;

/**************************** ELIMINAR COM_DATOS_CERTIFICADO ************************/
DELETE
FROM BROKER.COM_DATOS_CERTIFICADO WHERE COM_DATOS_CERTIFICADO.ID_DATOS_CERTIFICADO =4116137;

/**************************** ELIMINAR BAN_LIQUIDACION_SAFI ************************/
DELETE
FROM BROKER.BAN_LIQUIDACION_SAFI where ID_DATOS_SAFI = (SELECT ID FROM BROKER.BAN_DATOS_SAFI WHERE ID_DATOS_CERTIFICADO = 4116137);

/**************************** ELIMINAR COM_DATOS_CERTIFICADO_COMPANIA ************************/
DELETE
FROM BROKER.COM_DATOS_CERTIFICADO_COMPANIA where ID_DATOS_CERTIFICADO = 4116137;

/**************************** ELIMINAR COM_DATOS_CERTIFICADO_CLIENTE ************************/
DELETE
FROM BROKER.COM_DATOS_CERTIFICADO_CLIENTE WHERE COM_DATOS_CERTIFICADO_CLIENTE.ID_DATOS_CERTIFICADO = 4116137;

/**************************** ELIMINAR COM_DATOS_CERTIFICADO ************************/
DELETE
FROM BROKER.COM_DATOS_CERTIFICADO where ID = 4116137;