create or replace PROCEDURE P_MIGRACION_CUATRO AS 
-- V17.01.2024 -- control del too_many_rows (cursor para que recorra todos los valores y no de error)
CURSOR  INF
IS
--AD74A16T00 - INFORMES		Y AD74A02T00 INFORMES SOCIALES					
SELECT
/*CAMPOS PARA INFORMES SOCIALES*/
RTRIM(LTRIM(EMAILTS_003))  AS EMAIL_16,
'7'          AS ESTADO_16,
RTRIM(LTRIM(FECHAINFORME_003)) AS F_ALTA_16,
RTRIM(LTRIM(FECHAMODIF_003)) AS F_ENVIO_ADMON_16,
RTRIM(LTRIM(FECHAMODIF_003)) AS F_FIN_16,
RTRIM(LTRIM(FECHAINFORME_003)) AS F_INICIO_16,
RTRIM(LTRIM(FECHAMODIF_003)) AS F_MODIF_16,
NULL AS ID_AYUNTAMIENTO_16,
NULL as ID_BENEFICIARIO_16, --HALLADO MAS ABAJO
NULL  AS ID_DOC_INFORME_16,
NULL as ID_EXPEDIENTE_16, --HALLADO MAS ABAJO
NULL AS ID_INFORME_16,-- SE CREA CON NEXTVAL, NO EXISTE EN LA X87	
NULL AS ID_USUARIO_16,
'MIGRADO DESDE LA X87' AS OBSERVACIONES_16,
1 AS OCULTO_16,	
2 AS ORIGEN_16,
RTRIM(LTRIM(TELEFONOTS_003)) AS TELEFONO_16,
'1' AS TIPO_INFORME_16,
NULL AS UNIDAD_BASE_16,
0 AS URGENTE_16,
'MIGRADO' AS USU_ALTA_16,
NULL AS USU_MODIF_16,
RTRIM(LTRIM(AYUNTAMIENTO_003)) AS AYUNTAMIENTO_16,
RTRIM(LTRIM(SSB_003)) AS SSB_16,
RTRIM(LTRIM(TRABAJADORSOCIAL_003)) AS TRABAJADORSOCIAL_16,



/*CAMPOS PARA INFORMES*/
RTRIM(LTRIM(APRECIANECESIDAD_003)) AS APRECIACION_02,
RTRIM(LTRIM(SOLAMANIANA_003)) AS ESTA_SOLA_MANANA_02,
RTRIM(LTRIM(SOLANOCHE_003))  AS ESTA_SOLA_NOCHE_02,
RTRIM(LTRIM(SOLATARDE_003)) AS ESTA_SOLA_TARDE_02,
RTRIM(LTRIM(FECHAINFORME_003)) AS F_ALTA_02,
RTRIM(LTRIM(FALLECFAMI_003)) AS FAMILIAR_FALLECIDO_02,
RTRIM(LTRIM(FECHAINFORME_003)) AS F_INFORME_02,
DECODE (RTRIM(LTRIM(FRECUSALIDAS_028)),1,0,2,1,3,2,4,3,5,4) AS FRECUENCIA_SALIDAS_02,
NULL AS IDENTIFICADOR_02,
RTRIM(LTRIM(IDINFSOCIAL_003)) AS IDINFSOCIAL_003,-- METER EN LA TABLA DE CLAVES
NULL AS NOMBRE_02,
RTRIM(LTRIM(OBSERVPRESTATARIO_003)) AS OBSERVACIONES_PS_02,
RTRIM(LTRIM(OBSERVACIONES_003)) AS OBSERVACIONES_02,
RTRIM(LTRIM(TIEMPOSOLEDAD_003)) AS PERIODO_SIN_VISITAS_02,
RTRIM(LTRIM(GRADORELENTORNO_003)) AS RELACION_ENTORNO_02,
RTRIM(LTRIM(VIVESOLA_003)) AS SOLA_02,
RTRIM(LTRIM(SOLEDADLARGA_003)) AS TIEMPO_SIN_VISITAS_02,
NULL AS TIPO_IDENTIFICADOR_02,
RTRIM(LTRIM(VINCULO_003)) AS VINCULO_FALLECIDO_02,
DECODE(RTRIM(LTRIM(FRECUVISITAAMIST_028)),1,0,2,1,3,2,4,3,5,4) AS VISITAS_AMIGOS_02,
DECODE(RTRIM(LTRIM(FRECUVISITAFAMILIA_028)),1,0,2,1,3,2,4,3,5,4)AS VISITAS_FAMILIA_02
FROM X87B.X8703T00@X87B_DBL0;

W_IDSOLICITANTE_X87 VARCHAR2(50);
W_ID_EXPEDIENTE_X87 VARCHAR2(50);
W_IDSOLICITANTE_AD74A NUMBER;
W_ID_EXPEDIENTE_AD74A NUMBER;
W_ID_INFORME_16       NUMBER;
W_ID_INF_SOCIAL_02    NUMBER;

 

CURSOR CONV (W_INF_SOCIAL VARCHAR2)--CONVIVIENTES
IS
SELECT 
FECHANACIMIENTO_037 AS  F_NACIMIENTO_82,
IDPERSONACONV_037 AS ID_CONVIVIENTE_82,
IDINFSOCIAL_003,
NOMBREAPELLIDOS_037 AS NOMBRE_APELLIDOS_82,
VINCULO_037 AS PARENTESCO_82
FROM X87B.X8737T00@X87B_DBL0
WHERE IDINFSOCIAL_003=(W_INF_SOCIAL);

W_ID_CONVIVIENTE_82 NUMBER;


BEGIN
    FOR R IN INF LOOP  --CURSOR PARA INFORMES E INFORMES SOCIALES
        /*HALLAMOS EL BENEFICIARIO Y EL EXPEDIENTE PARA INFORMES*/
        DECLARE 
            CURSOR CSOL IS
            SELECT X00.IDSOLICITANTE_001 IDSOLICITANTE_001,X00.IDSOLICITUD_000 IDSOLICITUD_000
            --INTO W_IDSOLICITANTE_X87, W_ID_EXPEDIENTE_X87 --SERAN VARCHAR LOS DOS
            FROM X87B.X8703T00@X87B_DBL0 X03, X87B.X8700T00@X87B_DBL0 X00
            WHERE X00.IDINFSOCIAL_003=X03.IDINFSOCIAL_003
            AND X03.IDINFSOCIAL_003=R.IDINFSOCIAL_003;
         BEGIN 
            FOR RSOL IN CSOL LOOP
                W_IDSOLICITANTE_X87:= RSOL.IDSOLICITANTE_001;
                W_ID_EXPEDIENTE_X87 := RSOL.IDSOLICITUD_000;
                
                BEGIN
                 SELECT DISTINCT VALOR_COLUM_AD74A
                  INTO W_IDSOLICITANTE_AD74A  --ESTO VA EN EL CAMPO ID_BENEFICIARIO_16 (NUMBER)
                  FROM T_CLAVESX87B_AD74A
                  WHERE NOMBRE_COLUM_X87B='IDSOLICITANTE_001'
                  AND VALOR_COLUM_X87B   =W_IDSOLICITANTE_X87;
                EXCEPTION
                  WHEN NO_DATA_FOUND THEN 
                       W_IDSOLICITANTE_AD74A:=NULL;
                  WHEN OTHERS THEN
                  DBMS_OUTPUT.PUT_LINE('error al hallar el solicitante.  '||R.IDINFSOCIAL_003||' '||W_IDSOLICITANTE_X87||' '||SQLCODE||SQLERRM);
                 END;
         
                 BEGIN
                  SELECT DISTINCT VALOR_COLUM_AD74A
                  INTO W_ID_EXPEDIENTE_AD74A  --ESTO VA EN EL CAMPO ID_EXPEDIENTE_16  (NUMBER)
                  FROM T_CLAVESX87B_AD74A
                  WHERE NOMBRE_COLUM_X87B='IDSOLICITUD_000'
                  AND VALOR_COLUM_X87B   =W_ID_EXPEDIENTE_X87;
                EXCEPTION
                  WHEN NO_DATA_FOUND THEN 
                       W_ID_EXPEDIENTE_AD74A:=NULL;
                  WHEN OTHERS THEN
                  DBMS_OUTPUT.PUT_LINE('error al hallar el solicitante.  '||R.IDINFSOCIAL_003||' '||W_IDSOLICITANTE_X87||' '||SQLCODE||SQLERRM);
                 END;
                /* FIN HALLAMOS EL BENEFICIARIO Y EL EXPEDIENTE PARA INFORMES*/
                
                SELECT AD74A16T00_SEQ.NEXTVAL
                  INTO W_ID_INFORME_16 --ESTO VA AL CAMPO ID_INFORME_16
                FROM DUAL;
                
                BEGIN
                  INSERT
                  INTO AD74A.AD74A16T00--informes
                  (
                AYUNTAMIENTO_16,
                EMAIL_16,
                ESTADO_16,
                F_ALTA_16,
                F_ENVIO_ADMON_16,
                F_FIN_16,
                F_INICIO_16,
                F_MODIF_16,
                ID_BENEFICIARIO_16,
                ID_EXPEDIENTE_16,
                ID_INFORME_16,
                OBSERVACIONES_16,
                OCULTO_16,
                ORIGEN_16,
                SSB_16,
                TELEFONO_16,
                TIPO_INFORME_16,
                TRABAJADORSOCIAL_16,
                UNIDAD_BASE_16,
                URGENTE_16,
                USU_ALTA_16
                )
                  VALUES
                  (
                R.AYUNTAMIENTO_16,
                R.EMAIL_16,
                R.ESTADO_16,
                R.F_ALTA_16,
                R.F_ENVIO_ADMON_16,
                R.F_FIN_16,
                R.F_INICIO_16,
                R.F_MODIF_16,
                W_IDSOLICITANTE_AD74A,
                W_ID_EXPEDIENTE_AD74A,
                W_ID_INFORME_16,
                R.OBSERVACIONES_16,
                R.OCULTO_16,
                R.ORIGEN_16,
                R.SSB_16,
                R.TELEFONO_16,
                R.TIPO_INFORME_16,
                R.TRABAJADORSOCIAL_16,
                R.UNIDAD_BASE_16,
                R.URGENTE_16,
                R.USU_ALTA_16
                
                  );
                  EXCEPTION
                  WHEN OTHERS THEN
                  DBMS_OUTPUT.PUT_LINE('ERROR INSERT AD74A16T00.  '||W_ID_EXPEDIENTE_AD74A||' '||W_IDSOLICITANTE_AD74A||' '||W_ID_INFORME_16||' '||SQLCODE||SQLERRM);
                END;
            END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN 
       W_IDSOLICITANTE_AD74A:=NULL;
       W_ID_EXPEDIENTE_X87:=NUll;
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('error al hallar el solicitante y el expediente  '||R.IDINFSOCIAL_003||' '||SQLCODE||SQLERRM);
 END;


 
 
 /*CARGA DE INFORMES SOCIALES*/
 
 SELECT AD74A02T00_SEQ.NEXTVAL
  INTO W_ID_INF_SOCIAL_02 --ESTO VA AL CAMPO ID_INF_SOCIAL_02
FROM DUAL;

  BEGIN
      INSERT
      INTO T_CLAVESX87B_AD74A
        (
          TABLA_X87B ,
          NOMBRE_COLUM_X87B,
          VALOR_COLUM_X87B,
          TABLA_AD74A,
          NOMBRE_COLUM_AD7A4,
          VALOR_COLUM_AD74A
        )
        VALUES
        (
          'X87B.X8703T00',
          'IDINFSOCIAL_003',
          R.IDINFSOCIAL_003,
          'AD7402T00',
          'ID_INF_SOCIAL_02',
          W_ID_INF_SOCIAL_02
        );
    EXCEPTION
    WHEN dup_val_on_index THEN
      NULL;
    END;
    
BEGIN
  INSERT
  INTO AD74A.AD74A02T00--informe Social
  (

APRECIACION_02,
ESTA_SOLA_MANANA_02,
ESTA_SOLA_NOCHE_02,
ESTA_SOLA_TARDE_02,
F_ALTA_02,
FAMILIAR_FALLECIDO_02,
F_INFORME_02,
FRECUENCIA_SALIDAS_02,
ID_INFORME_02,
ID_INF_SOCIAL_02,
OBSERVACIONES_PS_02,
OBSERVACIONES_02,
PERIODO_SIN_VISITAS_02,
RELACION_ENTORNO_02,
SOLA_02,
TIEMPO_SIN_VISITAS_02,
VINCULO_FALLECIDO_02,
VISITAS_AMIGOS_02,
VISITAS_FAMILIA_02
)
VALUES
  (
R.APRECIACION_02,
R.ESTA_SOLA_MANANA_02,
R.ESTA_SOLA_NOCHE_02,
R.ESTA_SOLA_TARDE_02,
R.F_ALTA_02,
R.FAMILIAR_FALLECIDO_02,
R.F_INFORME_02,
R.FRECUENCIA_SALIDAS_02,
W_ID_INFORME_16,
W_ID_INF_SOCIAL_02,
R.OBSERVACIONES_PS_02,
R.OBSERVACIONES_02,
R.PERIODO_SIN_VISITAS_02,
R.RELACION_ENTORNO_02,
R.SOLA_02,
R.TIEMPO_SIN_VISITAS_02,
R.VINCULO_FALLECIDO_02,
R.VISITAS_AMIGOS_02,
R.VISITAS_FAMILIA_02
 );
  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('ERROR INSERT EN INFORMES SOCIALES AD74A02T00.  '||R.IDINFSOCIAL_003||' '||SQLCODE||SQLERRM);
END;

/*CARGA PARA CONVIVIENTES*/
FOR T IN CONV (R.IDINFSOCIAL_003) LOOP
  -- ABRO CURSOR DE DIRECCIONES
  
 SELECT AD74A82T00_SEQ.NEXTVAL
  INTO W_ID_CONVIVIENTE_82
FROM DUAL;
  -- Insert para la nueva tabla de claves ID_DIRECCION_06 ++
  
BEGIN
  INSERT INTO  T_CLAVESX87B_AD74A
  ( TABLA_X87B ,
  NOMBRE_COLUM_X87B,
  VALOR_COLUM_X87B,
  TABLA_AD74A,
  NOMBRE_COLUM_AD7A4,
  VALOR_COLUM_AD74A )
  values ('X8737T00',
  'IDPERSONACONV_037',
  T.ID_CONVIVIENTE_82,
  'AD74A82T00',
  'ID_CONVIVIENTE_82',
  W_ID_CONVIVIENTE_82);
  exception when dup_val_on_index then
  NULL;
   WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('error al INSERTAR EN T_CLAVESX87B_AD74A. ID_CONVIVIENTE_82  '||SQLCODE||SQLERRM);
  END;

BEGIN
  INSERT
  INTO AD74A.AD74A82T00--CONVIVIENTES
  (
F_NACIMIENTO_82,
ID_CONVIVIENTE_82,
ID_INF_SOCIAL_82,
NOMBRE_APELLIDOS_82,
PARENTESCO_82
)
VALUES
  (
T.F_NACIMIENTO_82,
W_ID_CONVIVIENTE_82,
W_ID_INF_SOCIAL_02,--FK a la tabla 02 de INFORMES_SOCIALES
T.NOMBRE_APELLIDOS_82,
T.PARENTESCO_82
 );
  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('ERROR INSERT EN CONVIVIENTES AD74A82T00.  '||W_ID_CONVIVIENTE_82||' '||T.NOMBRE_APELLIDOS_82||' '||W_ID_INF_SOCIAL_02||' '||SQLCODE||SQLERRM);
END;
END LOOP;--FIN LOOP CONVIVIENTES
END LOOP;--FIN LOOP INFORMES SOCIALES
COMMIT;
END P_MIGRACION_CUATRO;