SET SQLBLANKLINES ON;

/
-- Query para eliminar direcciones, cuentas y perfiles duplicados
DECLARE
    CURSOR CDIR IS 
        WITH q AS (
        SELECT S.ID_SOLICITANTE_01, COUNT(D.ID_DIRECCION_06) OVER (PARTITION BY S.ID_SOLICITANTE_01) TOTAL, D.ID_DIRECCION_06, S.ID_DIRECCION_01, D.F_ALTA_06
        FROM AD74A01T00 S, AD74A06T00 D
        WHERE S.ID_SOLICITANTE_01 = D.ID_SOLICITANTE_06
        AND S.ID_DIRECCION_01 <> D.ID_DIRECCION_06
        AND D.F_HISTORICO_06 IS NULL
        ORDER BY S.ID_SOLICITANTE_01)
        SELECT ID_SOLICITANTE_01, ID_DIRECCION_06, TOTAL, ID_DIRECCION_01, F_ALTA_06
        FROM q
        WHERE TOTAL > 1
        ORDER BY ID_SOLICITANTE_01, ID_DIRECCION_06;

    CURSOR CCUE IS 
        WITH q2 AS (
        SELECT S.ID_SOLICITANTE_01, COUNT(C.ID_CUENTA_05) OVER (PARTITION BY S.ID_SOLICITANTE_01) TOTAL, C.ID_CUENTA_05, S.ID_CUENTA_01, C.F_ALTA_05
        FROM AD74A01T00 S, AD74A05T00 C
        WHERE S.ID_SOLICITANTE_01 = C.ID_SOLICITANTE_05
        AND S.id_CUENTA_01 <> C.ID_CUENTA_05
        AND C.F_HISTORICO_05 IS NULL
        ORDER BY S.ID_SOLICITANTE_01)
        SELECT ID_SOLICITANTE_01, ID_CUENTA_05, TOTAL, ID_CUENTA_01, F_ALTA_05
        FROM q2
        WHERE TOTAL > 1
        ORDER BY ID_SOLICITANTE_01, ID_CUENTA_05;
        
    CURSOR CPER IS 
        SELECT P.ID_PERFIL_07
        FROM AD74A07T00 P LEFT JOIN AD74A01T00 S ON (P.ID_PERFIL_07 = S.ID_PERFIL_01)
        WHERE S.ID_PERFIL_01 IS NULL
        ORDER BY P.ID_PERFIL_07;
    
    wCont NUMBER := 0;
	wContD NUMBER := 0;
	wContC NUMBER := 0;
	wContP NUMBER := 0;
    wSolicitante NUMBER := 0;
    wExpediente NUMBER := NULL;
    
BEGIN
    -- DIRECCIONES
    DBMS_OUTPUT.PUT_LINE('#####DIRECCIONES#####');
    FOR DIR IN CDIR LOOP
        IF wSolicitante <> DIR.ID_SOLICITANTE_01 THEN 
            wCont := 0;            
            wSolicitante:= DIR.ID_SOLICITANTE_01;
            --DBMS_OUTPUT.PUT_LINE('------IDSOLICITANTE: '|| DIR.ID_SOLICITANTE_01 || ' - IDDIRECCION ACTIVA: ' || DIR.ID_DIRECCION_01 || ' - TOTAL A BORRAR: '|| DIR.TOTAL || '------');    
            
        END IF;
        wCont := wCont+1;
        
        -- Comprobar que la dirección no es de un expediente de cambio de domicilio
        BEGIN 
            SELECT ID_EXPEDIENTE_00 INTO wExpediente FROM AD74A00T00 WHERE ID_DIRECCION_00 = DIR.ID_DIRECCION_06;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN	
                wExpediente:= NULL;
            WHEN TOO_MANY_ROWS THEN
                wExpediente:= 0;
        END;    
                
        IF wExpediente IS NULL THEN            
            --DBMS_OUTPUT.PUT_LINE(wCont || '/' || DIR.TOTAL|| ' (IDSOLICITANTE=' || DIR.ID_SOLICITANTE_01 || ', IDDIRECCION=' || DIR.ID_DIRECCION_06 || ' - ' || DIR.F_ALTA_06 ||') --> ' || 'DELETE FROM AD74A06T00 WHERE ID_DIRECCION_06 = '|| DIR.ID_DIRECCION_06);          		
            DELETE FROM AD74A06T00 WHERE ID_DIRECCION_06 = DIR.ID_DIRECCION_06;
            wContD:=wContD+1;
        ELSE 
            DBMS_OUTPUT.PUT_LINE(wCont || '/' || DIR.TOTAL|| ' (IDSOLICITANTE=' || DIR.ID_SOLICITANTE_01 || ', IDDIRECCION=' || DIR.ID_DIRECCION_06 || ' - ' || DIR.F_ALTA_06 ||') --> ' || 'Esta direcicón está asociada a un expediente y no se puede borrar');          		        
        END IF;
    END LOOP;    
	COMMIT;
	
    -- CUENTAS
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('#####CUENTAS#####');
    wSolicitante:= 0;    
    wCont := 0;   
    wExpediente:= NULL;
    FOR CUE IN CCUE LOOP
        IF wSolicitante <> CUE.ID_SOLICITANTE_01 THEN 
            wCont := 0;            
            wSolicitante:= CUE.ID_SOLICITANTE_01;
            --DBMS_OUTPUT.PUT_LINE('------IDSOLICITANTE: '|| CUE.ID_SOLICITANTE_01 || ' - IDCUENTA ACTIVA: ' || CUE.ID_CUENTA_01 || ' - TOTAL: '|| CUE.TOTAL  || '------');                
        END IF;
        wCont := wCont+1;
        
        -- Comprobar que la cuenta no es de un expediente
        BEGIN 
            SELECT ID_EXPEDIENTE_00 INTO wExpediente FROM AD74A00T00 WHERE ID_CUENTA_00 = CUE.ID_CUENTA_05;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN	
                wExpediente:= NULL;
        END;    
        
        IF wExpediente IS NULL THEN            
            --DBMS_OUTPUT.PUT_LINE(wCont || '/' || CUE.TOTAL|| ' (IDSOLICITANTE=' || CUE.ID_SOLICITANTE_01 || ', IDCUENTA= '|| CUE.ID_CUENTA_05 || ' -  ' || CUE.F_ALTA_05 || ') --> ' || 'DELETE FROM AD74A05T00 WHERE ID_CUENTA_05 = '|| CUE.ID_CUENTA_05);          
            DELETE FROM AD74A05T00 WHERE ID_CUENTA_05 = CUE.ID_CUENTA_05;
            wContC:=wContC+1;
        ELSE 
            DBMS_OUTPUT.PUT_LINE(wCont || '/' || CUE.TOTAL|| ' (IDSOLICITANTE=' || CUE.ID_SOLICITANTE_01 || ', IDCUENTA=' || CUE.ID_CUENTA_05 || ' -  ' || CUE.F_ALTA_05 || ') --> ' || 'Esta cuenta está asociada a un expediente y no se puede borrar');          		        
        END IF;
    END LOOP;
	COMMIT;
    -- PERFILES (no afecta a nada en la aplicaicón pero lo limpiamos)
    /*DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('#####PERFILES#####');
    FOR PER IN CPER LOOP
        DBMS_OUTPUT.PUT_LINE('DELETE FROM AD74A07T00 WHERE ID_PERFIL_07 = ' || PER.ID_PERFIL_07);                  
        --DELETE FROM AD74A07T00 WHERE ID_PERFIL_07 = PER.ID_PERFIL_07;
		wContP:=wContP+1;
    END LOOP;	
	COMMIT;	*/
    
	--DBMS_OUTPUT.PUT_LINE('TOTALES - direcciones borradas: ' || wContD || ', cuentas borradas: ' || wContC || ', perfiles borrados: ' || wContP);
    DBMS_OUTPUT.PUT_LINE('TOTALES - direcciones borradas: ' || wContD || ', cuentas borradas: ' || wContC);
END;
/