CREATE TABLE fco_reportes_ala
(	
  codigo          char(10)   	NOT NULL,
  nombre          varchar2(100)  	NOT NULL,
  maneja_EPS      char(1)        NULL,
  maneja_FPO      char(1)        NULL,
  ISS_Integrado   char(1)        NULL,
  maneja_SUC      char(1)        NULL,
  maneja_ARP      char(1)        NULL,
  maneja_ISS      char(1)        NULL,
  maneja_rep      char(1)        NULL,
  maneja_mm       char(1)        NULL,  
  ruta_rep        varchar2(255) 	NULL,
  ruta_mm         varchar2(255)  NULL,
  orden           char(1)        NULL,  
  Constraint PK_fco_reportes_ala PRIMARY KEY (codigo)
  USING INDEX TABLESPACE TS_ADAMINDICES
)
TABLESPACE TS_ADAMDATOS;

CREATE PUBLIC SYNONYM fco_reportes_ala FOR ADAM.fco_reportes_ala;
GRANT ALL ON fco_reportes_ala TO ADAMA;
CREATE TABLE fco_proc_reportes_ala
      (compania                 char(4)         NOT NULL, 
       codigo                   char(4)   	NOT NULL,
       anio                     smallint  	NOT NULL,
       mes                      smallint	NOT NULL,
       EPS_FPO                  char(10)        NULL,
       sucursal                 char(10)        NULL,
       procesar                 char(1)         NULL,
       secuencia	        smallint	NULL
)
TABLESPACE TS_ADAMDATOS;

CREATE PUBLIC SYNONYM fco_proc_reportes_ala FOR ADAM.fco_proc_reportes_ala;
GRANT ALL ON fco_proc_reportes_ala TO ADAMA;
Create Or Replace View vw_fco_companias
As
Select 
	C.compania,
  registro_fiscal,
  C.nombre_cia,
	NVL(C.domicilio,' ') domicilio, 
	NVL(C.telefono,' ') telefono, 
	NVL(A.cod_dpto,'11') cod_dpto, 
	NVL(A.cod_ciudad,'001') cod_ciudad, 
	NVL(A.fax,' ') fax, 
	NVL(A.nit_ARP,'000000000-0') nit_ARP, 
	NVL(A.nombre_ARP,' ') nombre_ARP, 
	NVL(A.clase_aportante,'G') clase_aportante, 
	NVL(A.forma_presentacion,'U') forma_presentacion, 
	NVL(A.nro_patronal,' ') nro_patronal,
	NVL(A.nombre_retenedor, ' ') pagador_nombre,
	NVL(A.cedula_retenedor, ' ') pagador_id,
	NVL(A.agencias, ' ') agencias,
	NVL(A.cod_admon, ' ') cod_admon
From
	companias C, fco_cias_datos_adi A
Where
	C.compania = A.compania;

CREATE PUBLIC SYNONYM vw_fco_companias FOR ADAM.vw_fco_companias;
GRANT ALL ON vw_fco_companias TO ADAMA;                  
CREATE OR REPLACE VIEW vw_fco_proc_reportes_ala
AS
SELECT p.compania, p.procesar, p.codigo, r.nombre, p.anio, p.mes,
       p.EPS_FPO, p.sucursal, p.secuencia, 
       v.descripcion
FROM  fco_proc_reportes_ala p, fco_reportes_ala r, vw_fco_administradoras v
WHERE p.codigo = r.codigo and p.EPS_FPO = v.codigo And r.maneja_EPS = '1' And v.tipo='EPS'
      AND p.compania = v.compania
UNION
SELECT p.compania, p.procesar, p.codigo, r.nombre, p.anio, p.mes,
       p.EPS_FPO, p.sucursal, p.secuencia, 
       v.descripcion
FROM  fco_proc_reportes_ala p, fco_reportes_ala r, vw_fco_administradoras v
WHERE p.codigo = r.codigo and p.EPS_FPO = v.codigo And r.maneja_FPO = '1' And v.tipo='AFP'
      AND p.compania = v.compania      
UNION
SELECT p.compania, p.procesar, p.codigo, r.nombre, p.anio, p.mes,
       p.EPS_FPO, p.sucursal, p.secuencia, 
       v.descripcion
FROM  fco_proc_reportes_ala p, fco_reportes_ala r, vw_fco_administradoras v
WHERE p.codigo = r.codigo and p.EPS_FPO = v.codigo And r.maneja_ISS = '1' And v.tipo='EPS'
      AND p.compania = v.compania
UNION
SELECT p.compania, p.procesar, p.codigo, r.nombre, p.anio, p.mes,
       p.EPS_FPO, p.sucursal, p.secuencia, 
       v.nombre_ARP
FROM  fco_proc_reportes_ala p, fco_reportes_ala r, vw_fco_companias v
WHERE p.codigo = r.codigo and 
      p.compania = v.compania And r.maneja_ARP = '1';

CREATE PUBLIC SYNONYM vw_fco_proc_reportes_ala FOR ADAM.vw_fco_proc_reportes_ala;
GRANT ALL ON vw_fco_proc_reportes_ala TO ADAMA;                  
CREATE OR REPLACE PROCEDURE sp_fco_baja_reportes_ala
(
  i_codigo char
)
AS
BEGIN
    DELETE fco_reportes_ala 
    WHERE Codigo = i_codigo;

    DELETE fco_proc_reportes_ala
    WHERE Codigo = i_codigo;
    
    COMMIT WORK;
END;
/
CREATE PUBLIC SYNONYM sp_fco_baja_reportes_ala FOR ADAM.sp_fco_baja_reportes_ala;
GRANT EXECUTE ON sp_fco_baja_reportes_ala TO adama;

CREATE OR REPLACE PROCEDURE sp_fco_baja_proc_reportes_ala
(
    i_compania    char,
    i_codigo      char,
    i_anio        number,
    i_mes         number,
    i_EPS_FPO     char,
    i_sucursal    char
)
AS
BEGIN
    	DELETE fco_proc_reportes_ala 
    	WHERE compania = i_compania And
        RTrim(codigo) = RTrim(i_codigo) And
        anio = i_anio And
        mes = i_mes And
        RTrim(EPS_FPO) = RTrim(i_EPS_FPO) And
        NVL(RTrim(sucursal),'0') = NVL(RTrim(i_sucursal),'0');

	COMMIT;
END;
/
CREATE PUBLIC SYNONYM sp_fco_baja_proc_reportes_ala FOR ADAM.sp_fco_baja_proc_reportes_ala;
GRANT EXECUTE ON sp_fco_baja_proc_reportes_ala TO adama;
CREATE OR REPLACE PROCEDURE sp_fco_alta_reportes_ala
(
       	pch_codigo         char,
       	pch_nombre         char,
       	pch_maneja_EPS     char,
       	pch_maneja_FPO     char,
       	pch_iss_integrado  char,
       	pch_maneja_SUC     char,
        pch_maneja_ARP		 char,
        pch_maneja_ISS		 char,
       	pch_maneja_rep     char,        
       	pch_maneja_mm      char,                
       	pvc_ruta           varchar2,
       	pvc_ruta_salida    varchar2,
        pch_orden          char        
)
AS
    vti_existe			  number(1);
BEGIN
  vti_existe := 0;
  BEGIN
      SELECT 1 INTO vti_existe
      FROM fco_reportes_ala
      WHERE codigo = pch_codigo;
	EXCEPTION
			WHEN OTHERS THEN vti_existe := 0;	      
  END;

  IF vti_existe = 1 THEN
	BEGIN
		UPDATE fco_reportes_ala 
	  	SET Nombre = pch_nombre,
        	Maneja_EPS = pch_maneja_EPS,
        	Maneja_FPO = pch_maneja_FPO,
        	ISS_Integrado = pch_iss_integrado,
        	Maneja_SUC = pch_maneja_SUC,
	      	Maneja_ARP = pch_maneja_ARP,
	      	Maneja_ISS = pch_maneja_ISS,
          maneja_rep = pch_maneja_rep,
	      	maneja_mm = pch_maneja_mm,          
        	ruta_rep   = Replace(pvc_ruta,'/','\'),
	      	ruta_mm = Replace(pvc_ruta_salida,'/','\'),
          orden = pch_orden
	  	WHERE
	      	Codigo = pch_codigo;
	EXCEPTION
		WHEN OTHERS THEN
		raise_application_error(-20001, 'Error al tratar de cambiar en la tabla [fco_reportes_ala]');
		return;
	END;    
  ELSE
	BEGIN
		INSERT INTO fco_reportes_ala VALUES 
		( pch_codigo,
      pch_nombre,
      pch_maneja_EPS,
      pch_maneja_FPO,
      pch_ISS_Integrado,
      pch_maneja_SUC,
      pch_maneja_ARP,
      pch_maneja_ISS,
      pch_maneja_rep,
      pch_maneja_mm,
      Replace(pvc_ruta,'/','\'),
      Replace(pvc_ruta_salida,'/','\'),
      pch_orden
      );
	EXCEPTION
		WHEN OTHERS THEN 
			raise_application_error(-20001, 'Error al tratar de insertar en la tabla [fco_reportes_ala]');
			return;
	END;
  END IF;	
  	COMMIT WORK;
END;
/
CREATE PUBLIC SYNONYM sp_fco_alta_reportes_ala FOR ADAM.sp_fco_alta_reportes_ala;
GRANT EXECUTE ON sp_fco_alta_reportes_ala TO adama;

CREATE OR REPLACE PROCEDURE sp_fco_alta_proc_reportes_ala
(
  i_compania	char,
  i_codigo        char,
  i_anio          smallint,
  i_mes           smallint,
  i_eps_fpo       char,
  i_sucursal      char
)
AS
	w_maneja_eps char(1);
	w_maneja_fpo char(1);
	w_maneja_suc char(1);
	w_maneja_iss char(1);
	w_maneja_arp char(1);
	w_EPS varchar2(10);
	w_FPO varchar2(10);
	w_SUC varchar2(10);
	w_secuencia number(5,0);
BEGIN
	/* Obtiene el parametro para agrupacion de la EPS */
	BEGIN
		SELECT  NVL(dato,'EPS') INTO w_EPS
		FROM vw_fdt_parametros 
		WHERE pais = 'COL' 
    AND compania = i_compania 
		AND parametro = 'autoliq' 
		AND secuencia = 21;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN w_EPS := 'EPS';
	END;

	/* Obtiene el parametro para agrupacion de la FPO */
	BEGIN
		SELECT  NVL(dato,'FPO') INTO w_FPO
		FROM vw_fdt_parametros 
		WHERE pais = 'COL' 
    AND compania = i_compania 
		AND parametro = 'autoliq' 
		AND secuencia = 22;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN w_FPO := 'FPO';
	END;

	/* Obtiene el parametro para agrupacion de la SUC */
	BEGIN
		SELECT  NVL(dato,'SUC') INTO w_SUC
		FROM vw_fdt_parametros 
		WHERE pais = 'COL' 
    AND compania = i_compania 
		AND parametro = 'autoliq' 
		AND secuencia = 23;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN w_SUC := 'SUC';
	END;


	/* Halla la secuencia siguiente -para mantener orden en el spread-*/
	SELECT NVL(Max(secuencia),0)+1 INTO w_secuencia FROM fco_proc_reportes_ala;


	/* AQUI COMIENZA LA ACCION */
	 /* Hallar que maneja el reporte */
    SELECT  NVL(maneja_eps,'0'), NVL(maneja_fpo,'0'), 
    NVL(maneja_suc,'0'), NVL(maneja_iss,'0'), 
    NVL(maneja_arp,'0')
    INTO w_maneja_eps, w_maneja_fpo, W_maneja_suc, w_maneja_iss, w_maneja_arp
    FROM fco_reportes_ala
    WHERE codigo = i_codigo;

	/* CASOS DE REPORTE */

        /* CASO 0 */
        IF w_maneja_arp = '1' THEN
             /* CASO 0 CON SUCURSALES */
          IF w_maneja_suc = '1' THEN
               IF Nvl(i_sucursal || '0','0') <> '0' THEN
                    Delete fco_proc_reportes_ala
                    WHERE compania=i_compania AND codigo=i_codigo
                    AND anio = i_anio AND mes = i_mes
                    AND eps_fpo = 'ARP' AND sucursal = i_sucursal;
               
                    INSERT INTO fco_proc_reportes_ala
                    (compania, codigo, anio, mes, EPS_FPO, sucursal, procesar, secuencia)
                    VALUES (i_compania, i_codigo, i_anio, i_mes, 'ARP', i_sucursal, '0', w_secuencia);
               ELSE
                    Delete fco_proc_reportes_ala
                    WHERE compania=i_compania AND codigo=i_codigo
                    AND anio = i_anio AND mes = i_mes
                    AND eps_fpo = 'ARP';

                    INSERT INTO fco_proc_reportes_ala
                    SELECT i_compania, i_codigo, i_anio, i_mes, 'ARP', sucursal,'0', w_secuencia
                    From vw_fco_sucursales_cia
                    WHERE compania = i_compania;
                 	END IF;
          ELSE
            /* CASO 0 SIN SUCURSALES */
              Delete fco_proc_reportes_ala
              WHERE compania=i_compania AND codigo=i_codigo
              AND anio = i_anio AND mes = i_mes
              AND eps_fpo = 'ARP';
            
            	INSERT INTO fco_proc_reportes_ala
             	(compania, codigo, anio, mes, EPS_FPO, procesar, secuencia)
              VALUES (i_compania, i_codigo, i_anio, i_mes, 'ARP', '0', w_secuencia);
          END IF;
		COMMIT WORK;
	END IF;        
        
        /* CASO 1 */
        IF w_maneja_iss = '1' THEN
		/*IF i_eps_fpo= '' THEN 
			i_eps_fpo :='ISS';
		END IF;*/
             
        	/* CASO 1 CON SUCURSALES */
                IF w_maneja_suc = '1' THEN
	                IF Nvl(i_sucursal || '0','0') <> '0' THEN
                        Delete fco_proc_reportes_ala
                        WHERE compania=i_compania AND codigo=i_codigo
                        AND anio = i_anio AND mes = i_mes
                        AND eps_fpo = 'ISS' AND sucursal = i_sucursal;
                  
                     		INSERT INTO fco_proc_reportes_ala
                     		(compania, codigo, anio, mes, EPS_FPO, sucursal, procesar, secuencia)
                        VALUES (i_compania, i_codigo, i_anio, i_mes, 'ISS', i_sucursal, '0', w_secuencia);
                  	ELSE
                        Delete fco_proc_reportes_ala
                        WHERE compania=i_compania AND codigo=i_codigo
                        AND anio = i_anio AND mes = i_mes
                        AND eps_fpo = 'ISS';

                        INSERT INTO fco_proc_reportes_ala
                     		SELECT i_compania, i_codigo, i_anio, i_mes, i_eps_fpo, sucursal,'0', w_secuencia
                     		From vw_fco_sucursales_cia
                     		WHERE compania = i_compania;
                   	END IF;
                ELSE
                  Delete fco_proc_reportes_ala
                  WHERE compania=i_compania AND codigo=i_codigo
                  AND anio = i_anio AND mes = i_mes
                  AND eps_fpo = 'ISS';

               		/* CASO 1 SIN SUCURSALES */
                  INSERT INTO fco_proc_reportes_ala
                  (compania, codigo, anio, mes, EPS_FPO, procesar, secuencia)
                  VALUES (i_compania, i_codigo, i_anio, i_mes, i_eps_fpo, '0', w_secuencia);
               	End IF;
		COMMIT WORK;
	END IF;
                
        /* CASO A */
        IF (w_maneja_eps = '1' and w_maneja_suc = '1' AND Nvl(Length(i_eps_fpo),0) > 0 AND Nvl(Length(i_sucursal),0) > 0) OR
           (w_maneja_fpo = '1' and w_maneja_suc = '1' AND Nvl(Length(i_eps_fpo),0) > 0 AND Nvl(Length(i_sucursal),0) > 0) THEN 
            Delete fco_proc_reportes_ala
            WHERE compania=i_compania AND codigo=i_codigo
            AND anio = i_anio AND mes = i_mes
            AND eps_fpo = i_eps_fpo AND sucursal = i_sucursal;
                       
            INSERT INTO fco_proc_reportes_ala
            VALUES (i_compania, i_codigo, i_anio, i_mes, i_eps_fpo, i_sucursal, '0', w_secuencia);
		COMMIT WORK;
        END IF;
        /**********/
        
        /* CASO B */
        IF w_maneja_eps = '1' and w_maneja_suc = '1' AND Nvl(Length(i_eps_fpo),0) = 0 and Nvl(Length(i_sucursal),0) > 0 THEN
            Delete fco_proc_reportes_ala
            WHERE compania=i_compania AND codigo=i_codigo
            AND anio = i_anio AND mes = i_mes
            AND sucursal = i_sucursal;
                       
            INSERT INTO fco_proc_reportes_ala
           	SELECT i_compania, i_codigo, i_anio, i_mes, codigo, i_sucursal, '0', w_secuencia
           	FROM fco_administradoras
           	WHERE tipo='EPS';
		COMMIT WORK;
        END IF;
        /**********/
        
        /* CASO C */
        IF (w_maneja_eps = '1' and w_maneja_suc = '1' and Nvl(Length(i_eps_fpo),0) > 0 and Nvl(Length(i_sucursal),0) = 0) OR
	   (w_maneja_fpo = '1' and w_maneja_suc = '1' and Nvl(Length(i_eps_fpo),0) > 0 and Nvl(Length(i_sucursal),0) = 0)  THEN
            Delete fco_proc_reportes_ala
            WHERE compania=i_compania AND codigo=i_codigo
            AND anio = i_anio AND mes = i_mes
            AND eps_fpo = i_eps_fpo;
                       
            INSERT INTO fco_proc_reportes_ala
            SELECT i_compania,i_codigo,i_anio,i_mes,i_eps_fpo,sucursal,'0', w_secuencia
            From vw_fco_sucursales_cia
            WHERE compania = i_compania;
		COMMIT WORK;
	END IF;
        /**********/
        
        /* CASO D */
        IF w_maneja_eps = '1' and w_maneja_suc = '1' AND  Nvl(Length(i_eps_fpo),0) = 0 and Nvl(Length(i_sucursal),0) = 0 THEN
            Delete fco_proc_reportes_ala
            WHERE compania=i_compania AND codigo=i_codigo
            AND anio = i_anio AND mes = i_mes;
                       
            INSERT INTO fco_proc_reportes_ala
            SELECT i_compania,i_codigo,i_anio,i_mes, b.codigo, a.sucursal, '0', w_secuencia
            FROM vw_fco_sucursales_cia a, fco_administradoras b
            WHERE a.compania = i_compania And
            b.tipo='EPS';
		COMMIT WORK;
	END IF;
        /**********/
        
        /* CASO E */
        IF (w_maneja_eps = '1' and w_maneja_suc = '0' and Nvl(Length(i_eps_fpo),0) > 0) OR
	   (w_maneja_fpo = '1' and w_maneja_suc = '0' and Nvl(Length(i_eps_fpo),0) > 0)  THEN
          Delete fco_proc_reportes_ala
          WHERE compania=i_compania AND codigo=i_codigo
          AND anio = i_anio AND mes = i_mes
          AND eps_fpo = i_eps_fpo;

          INSERT INTO fco_proc_reportes_ala
          VALUES (i_compania, i_codigo, i_anio, i_mes, i_eps_fpo,'', '0', w_secuencia);
		COMMIT WORK;
	END IF;
        /**********/
        
        /* CASO F */
        IF w_maneja_eps = '1' and w_maneja_suc = '0' AND NVL(i_eps_fpo,'VACIO') = 'VACIO' THEN
            Delete fco_proc_reportes_ala
            WHERE compania=i_compania AND codigo=i_codigo
            AND anio = i_anio AND mes = i_mes;
                       
            INSERT INTO fco_proc_reportes_ala
            SELECT i_compania,i_codigo,i_anio,i_mes,codigo,'','0', w_secuencia
            FROM fco_administradoras WHERE tipo='EPS';
		COMMIT WORK;
	END IF;
        /**********/
        
        /* CASO G */
        IF w_maneja_fpo = '1' and w_maneja_suc = '1' AND NVL(i_eps_fpo,'VACIO') = 'VACIO' and i_sucursal <> '' THEN
            Delete fco_proc_reportes_ala
            WHERE compania=i_compania AND codigo=i_codigo
            AND anio = i_anio AND mes = i_mes
            AND sucursal = i_sucursal;
                       
            INSERT INTO fco_proc_reportes_ala
            SELECT i_compania, i_codigo, i_anio, i_mes, codigo, i_sucursal, '0', w_secuencia
            FROM fco_administradoras
            WHERE tipo='AFP';
		COMMIT WORK;
	END IF;
        /**********/
        
        /* CASO H */
        IF w_maneja_fpo = '1' and w_maneja_suc = '1' AND NVL(i_eps_fpo,'VACIO') = 'VACIO' and i_sucursal ='' THEN
          Delete fco_proc_reportes_ala
          WHERE compania=i_compania AND codigo=i_codigo
          AND anio = i_anio AND mes = i_mes;
                       
          INSERT INTO fco_proc_reportes_ala
          SELECT i_compania,i_codigo,i_anio,i_mes,b.codigo,a.sucursal,'0', w_secuencia
          FROM vw_fco_sucursales_cia a, fco_administradoras b
          WHERE a.compania = i_compania And
          b.tipo = 'AFP';
		COMMIT WORK;
	END IF;
        /**********/
        
        /* CASO I */
        IF w_maneja_fpo = '1' and w_maneja_suc = '0' AND NVL(i_eps_fpo,'VACIO') = 'VACIO' THEN
          Delete fco_proc_reportes_ala
          WHERE compania=i_compania AND codigo=i_codigo
          AND anio = i_anio AND mes = i_mes;
                       
          INSERT INTO fco_proc_reportes_ala
         	SELECT i_compania,i_codigo,i_anio,i_mes,codigo,'','0', w_secuencia
         	FROM fco_administradoras WHERE tipo ='AFP';
        	COMMIT WORK;       
	END IF;
       	/**********/
END;
/
CREATE PUBLIC SYNONYM sp_fco_alta_proc_reportes_ala FOR ADAM.sp_fco_alta_proc_reportes_ala;
GRANT EXECUTE ON sp_fco_alta_proc_reportes_ala TO adama;


INSERT INTO fco_reportes_ala 
(codigo, nombre, maneja_EPS, maneja_FPO, ISS_Integrado, maneja_SUC, maneja_ARP, 
maneja_ISS, maneja_rep, maneja_mm, ruta_rep, ruta_mm, orden)
SELECT codigo, nombre, maneja_EPS, maneja_FPO, ISS_Integrado, maneja_SUC, maneja_ARP,
maneja_ISS, CASE WHEN medio_magn = 1 THEN 0 ELSE 1 END, medio_magn, ruta, ruta_salida, 'A'
FROM reportes_ala;

commit;
