create or replace PROCEDURE P_MIGRACION_10_UPDATE_EXPS IS

    K_USU_MIGRADO               VARCHAR2(7) := 'MIGRADO';

    v_idestadoexp021            NUMBER;
    v_idsolicitante001          VARCHAR2(32);
    v_fechabaja009              DATE;
    v_count_total               PLS_INTEGER := 0;
    v_count_regs_founds         PLS_INTEGER := 0;
    v_count_regs_not_founds     PLS_INTEGER := 0;
    v_count_regs_too_many_rows  PLS_INTEGER := 0;
    v_count_when_others         PLS_INTEGER := 0;
	v_count_estado_update       PLS_INTEGER := 0;
    v_count_exps_baja           PLS_INTEGER := 0;
	v_count_exps_baja_update    PLS_INTEGER := 0;
	

CURSOR c_exps IS
            SELECT folder_id_00         folder_id_00,
                   id_procedimiento_00  id_procedimiento_00,
                   id_solicitante_00    id_solicitante_00
            FROM    expedientes
            WHERE  usu_alta_00 = K_USU_MIGRADO
            AND id_estado_00 IN (10,--Cerrado 
                                 24,--Pendiente de archivar 
                                 25--En tramitación X87
                                 );
								 
vSql varchar2(4000);								 
BEGIN
    --DBMS_OUTPUT.PUT_LINE('Tiempo inicio-->' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
    --Abre cursor
    FOR reg_exps IN c_exps
    LOOP
        v_count_total := v_count_total + 1;

        BEGIN
            SELECT  idestadoexp_021     idestadoexp_021,
                    idsolicitante_001   idsolicitante_001
            INTO    v_idestadoexp021,
                    v_idsolicitante001
            FROM    x87b.x8700t00@x87b_dbl0
            WHERE   folderid_000 = reg_exps.folder_id_00;

            v_count_regs_founds := v_count_regs_founds + 1;

        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                    v_count_regs_not_founds := v_count_regs_not_founds + 1;
            WHEN TOO_MANY_ROWS THEN
                    v_count_regs_too_many_rows := v_count_regs_too_many_rows + 1;
            WHEN OTHERS THEN
                v_count_when_others := v_count_when_others + 1;               
        END;
  
        BEGIN
            UPDATE expedientes
            SET id_estado_00 = DECODE (TRIM(NVL(v_idestadoexp021,42)),41,10,42,23,45,24,25), F_MODIF_00 = SYSDATE, USU_MODIF_00 = 'UPDATE'
            WHERE folder_id_00 = reg_exps.folder_id_00;
			v_count_estado_update := v_count_estado_update +1;

        EXCEPTION
            WHEN OTHERS THEN
				vSql:= sqlcode||'-'||substr(sqlerrm,1,1000);
				PG_MIGRACION.INSERTA_TRAZA('UPDATE EXPS','update estado', 'ERROR '||vSql||', folder_id_00:'||reg_exps.folder_id_00, null);
                --DBMS_OUTPUT.PUT_LINE('Error en update expedientes en folder_id_00-->' || reg_exps.folder_id_00);
                --DBMS_OUTPUT.PUT_LINE('Error en update expedientes-->' || SQLCODE || '--' || SQLERRM);
        END;
   
        IF reg_exps.id_procedimiento_00 = 'X87_TEA_2' THEN-- Si el procedimiento del expediente es de baja

            v_count_exps_baja := v_count_exps_baja + 1;

			v_fechabaja009:=NULL;
            BEGIN
                SELECT fechabaja_009 fechabaja_009
                INTO    v_fechabaja009
                FROM    x87b.x8709t00@x87b_dbl0
                WHERE   idsolicitante_001 = v_idsolicitante001;						

            EXCEPTION
                WHEN OTHERS THEN
					PG_MIGRACION.INSERTA_TRAZA('UPDATE EXPS','update fecha baja', 'ERROR No se ha encontrado registro en x8709t00 para idsolicitante_001-->' || v_idsolicitante001, null);
                    --DBMS_OUTPUT.PUT_LINE('No se ha encontrado registor en x8709t00 para idsolicitante_001-->' || v_idsolicitante001);              
            END;

			IF v_fechabaja009 IS NOT NULL THEN
				BEGIN
					UPDATE expedientes
					SET f_baja_00 = v_fechabaja009, F_MODIF_00 = SYSDATE, USU_MODIF_00 = 'UPDATE'
					WHERE folder_id_00 = reg_exps.folder_id_00;
					
					v_count_exps_baja_update := v_count_exps_baja_update +1;

				EXCEPTION
					WHEN OTHERS THEN
						vSql:= sqlcode||'-'||substr(sqlerrm,1,1000);
						PG_MIGRACION.INSERTA_TRAZA('UPDATE EXPS','update fecha baja exp', 'ERROR '||vSql||', folder_id_00:'||reg_exps.folder_id_00, null);
						--DBMS_OUTPUT.PUT_LINE('Error en update (fechas de baja) expedientes en folder_id_00-->' || reg_exps.folder_id_00);
						--DBMS_OUTPUT.PUT_LINE('Error en update (fechas de baja) expedientes-->' || SQLCODE || '--' || SQLERRM);
				END;

				 BEGIN
					UPDATE solicitantes
					SET f_baja_01 = v_fechabaja009,
						f_baja_ps_01 = v_fechabaja009,
						F_MODIF_01 = SYSDATE, USU_MODIF_01 = 'UPDATE'
					WHERE id_solicitante_01 = reg_exps.id_solicitante_00;

				EXCEPTION
					WHEN OTHERS THEN
						vSql:= sqlcode||'-'||substr(sqlerrm,1,1000);
						PG_MIGRACION.INSERTA_TRAZA('UPDATE EXPS','update fecha baja exp', 'ERROR '||vSql||', folder_id_00:'||reg_exps.folder_id_00, null);
						--DBMS_OUTPUT.PUT_LINE('Error en update (solicitantes - fechas de baja) solicitantes en folder_id_00-->' || reg_exps.folder_id_00);
						--DBMS_OUTPUT.PUT_LINE('Error en update (solicitantes - fechas de baja) solicitantes-->' || SQLCODE || '--' || SQLERRM);
				END; 			
			END IF;                 
        END IF;
        
        COMMIT;
        
    END LOOP;

	PG_MIGRACION.INSERTA_TRAZA('UPDATE EXPS',null, null, 'TOTALES: Total exp:'  || v_count_total || ';Total exp. encontrados: ' || v_count_regs_founds || ';Total exp. actualizados: ' || v_count_estado_update || ';Total exp. baja: ' || v_count_exps_baja || ';Total exp. baja actualizados: ' || v_count_exps_baja_update);
    --DBMS_OUTPUT.PUT_LINE('v_count_total-->' || v_count_total);
    --DBMS_OUTPUT.PUT_LINE('v_count_regs_founds-->' || v_count_regs_founds);
    --DBMS_OUTPUT.PUT_LINE('v_count_regs_not_founds-->' || v_count_regs_not_founds);
    --DBMS_OUTPUT.PUT_LINE('v_count_regs_too_many_rows-->' || v_count_regs_too_many_rows);
    --DBMS_OUTPUT.PUT_LINE('v_count_when_others-->' || v_count_when_others);
    --DBMS_OUTPUT.PUT_LINE('v_count_exps_baja-->' || v_count_exps_baja);
    --DBMS_OUTPUT.PUT_LINE('Tiempo final-->' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));

END;