CREATE TABLE fco_embargos_rep
(
  id_rep      smallint NOT NULL,
  usuariow	  varchar2(30) NOT NULL,
  cod_rep     number(3) NOT NULL,  
  compania    char(4) NOT NULL,
  trabajador          char(10) NOT NULL,
  concepto            smallint NOT NULL,
  secuencia           smallint NOT NULL,
  apellido_demandante varchar2(20) NULL,
  nombre_demandante   varchar2(20) NULL,
  apellidos_trab      varchar2(20) NULL,
  nombres_trab        varchar2(20) NULL,
  agr_conceptos_base  char(3) NULL,
  concepto_base       smallint NULL,
  quinta_parte        smallint NULL,
  monto_total         number(14,2) NULL,
  saldo_actual        number(14,2) NULL,
  porcentaje          number(4,2) NULL,
  cuota_fija          number(14,2) NULL,
  nombre_cia          varchar2(60) NULL,
  agr1        varchar2(10) NULL,
  dato1       varchar2(10) NULL,
  desc1	      varchar2(40) NULL,
  agr2        varchar2(10) NULL,
  dato2       varchar2(10) NULL,
  desc2       varchar2(40) NULL,
  agr3        varchar2(10) NULL,
  dato3       varchar2(10) NULL,
  desc3	      varchar2(40) NULL,
  CONSTRAINT PK_fco_embargos_rep PRIMARY KEY 
  (id_rep, usuariow, cod_rep, compania, trabajador, concepto, secuencia) 
	USING INDEX TABLESPACE TS_ADAMINDICES  
)
TABLESPACE TS_ADAMDATOS;

CREATE PUBLIC SYNONYM fco_embargos_rep FOR ADAM.fco_embargos_rep;
GRANT ALL ON fco_embargos_rep TO ADAMA;
CREATE OR REPLACE PROCEDURE sp_fco_rep_embargos
(
  psi_id_rep  number,
  pvc_usuario varchar2,
  pti_cod_rep number,
  pch_trab_dde char DEFAULT '',
  pch_trab_hta char DEFAULT '',
  pch_agr_1 char DEFAULT '',
  pch_agr_2 char DEFAULT '',
  pch_agr_3 char DEFAULT '',
  pch_dato_dde char DEFAULT '',
  pch_dato_hta char DEFAULT '',--hasta aqui serian parametros fijos    
  pch_compania    varchar2,
  pdt_fecha       varchar2,
  pch_activos     varchar2
  
)
/********************************************************************/
/*** Fiscal de Colombia de Data Tecnologia                        ***/
/*** Descripcion: Genera el reporte de cajas de compensacion      ***/
/*** Autor:	Diana Cecilia Rios Peinado                            ***/
/*** Expl.:	Antes se ejecutaba una vista vw_fdt_embargos          ***/
/*** Fecha:	Mayo 15 de 2005                                       ***/
/********************************************************************/
--Despues de los parametros fijos, los parametros se reciben como varchar y dentro del sp se convierten
--al tipo de datos correspondiente
AS
  vch_compania char(4);
  vdt_fecha    date;
  vnu_situacion number(1);
  
  vti_trab_dde number(1);
  vti_trab_hta number(1);  
  vti_fecha number(1);
  vti_sit number(1);  
BEGIN

  vch_compania := TRIM(pch_compania);

  vti_fecha := 1;
  IF NVL(TRIM(pdt_fecha),'0') <> '0' THEN  --viene una fecha
     vti_fecha := 0;
     vdt_fecha := TO_DATE(SUBSTR(TRIM(pdt_fecha),1,8),'YYYYMMDD');
  END IF;
  
  vti_sit := 1;
  IF NVL(TRIM(pch_activos),'0') <> '0' THEN  --viene una situacion
     vti_sit := 0;
     IF pch_activos = 'S' THEN
        vnu_situacion := 1;
     ELSE
        vnu_situacion := 0;
     END IF;
  END IF;
  
  vti_trab_dde := 0;
  IF Nvl(TRIM(pch_trab_dde),'0') = '0' THEN
      vti_trab_dde := 1;
  END IF;
  
  vti_trab_hta := 0;
  IF Nvl(TRIM(pch_trab_hta),'0') = '0' THEN
      vti_trab_hta := 1;
  END IF;

  --trayendo la informacion de embargos
  INSERT INTO fco_embargos_rep
  (id_rep, usuariow, cod_rep, compania, trabajador, concepto, secuencia, apellido_demandante,
  nombre_demandante, apellidos_trab, nombres_trab, agr_conceptos_base, concepto_base,
  quinta_parte, monto_total, saldo_actual, porcentaje, cuota_fija, nombre_cia)
  SELECT psi_id_rep, pvc_usuario, pti_cod_rep, E.Compania, E.Trabajador, E.Concepto, E.Secuencia,
  E.Apellido_Demandante, E.Nombre_Demandante, 
  fn_Primer_Apellido(T.nombre) || ' ' || fn_Segundo_Apellido(T.nombre) As apellidos_trab,
  fn_Primer_Nombre(T.nombre) || ' ' ||  fn_Segundo_Nombre(T.nombre) As nombres_trab,
  E.Agr_Conceptos_Base, E.Concepto_Base, E.Quinta_Parte, E.Monto_Total, E.Saldo_Actual, E.Porcentaje,
  E.Cuota_Fija, D.Nombre_Cia
  FROM fdt_embargos E, trabajadores T, conceptos C, companias D
  WHERE
    E.compania = D.compania And
    E.trabajador = T.trabajador And
    E.concepto = C.concepto And
    E.compania = vch_compania
    And ((TRIM(vti_trab_dde) = 1) OR (T.trabajador >= RTRIM(pch_trab_dde) AND TRIM(vti_trab_dde) = 0)) AND
       ((TRIM(vti_trab_hta) = 1) OR (T.trabajador <= RTRIM(pch_trab_hta) AND TRIM(vti_trab_hta) = 0))
    And ((TRIM(vti_fecha) = 1) OR (E.fecha_alta >= vdt_fecha AND TRIM(vti_fecha) = 0)) 
    And ((TRIM(vti_sit) = 1) OR (E.Situacion = vnu_situacion AND TRIM(vti_sit) = 0));
  
  
  -- NO PROCESAR LOS TRABAJADORES FUERA DEL RANGO DE DATOS DE LA AGR. 4
  IF NVL(TRIM(pch_dato_dde),'0') <> '0' THEN
    DELETE fco_embargos_rep
    WHERE id_rep = psi_id_rep
    AND usuariow = pvc_usuario
    AND cod_rep = pti_cod_rep
    AND trabajador IN
      (SELECT trabajador FROM rel_trab_agr
      WHERE agrupacion = pch_agr_1
      AND dato < pch_dato_dde);
  END IF;
    
  IF NVL(TRIM(pch_dato_hta),'0') <> '0' THEN
    DELETE fco_embargos_rep
    WHERE id_rep = psi_id_rep
    AND usuariow = pvc_usuario
    AND cod_rep = pti_cod_rep    
    AND trabajador IN
        (SELECT trabajador FROM rel_trab_agr
         WHERE agrupacion = pch_agr_1
         AND dato > pch_dato_hta);
  END IF;

  -- DATOS DE LAS AGRUPACIONES SELECCIONADAS POR EL USUARIO 
  IF NVL(TRIM(pch_agr_1),'0') <> '0' THEN
    --Agrupacion 1 - agr1
    UPDATE fco_embargos_rep rv
    SET agr1 = pch_agr_1
    WHERE rv.id_rep = psi_id_rep 
      AND rv.usuariow = pvc_usuario
      AND rv.cod_rep = pti_cod_rep;

    --Agrupacion 1 - dato1
    UPDATE fco_embargos_rep rv
    SET dato1 = (SELECT b.dato
                FROM rel_trab_agr a, datos_agr_trab b                          
                WHERE a.agrupacion = b.agrupacion 
                  AND a.dato = b.dato 
                  AND rv.compania = a.compania 
                  AND rv.trabajador = a.trabajador
                  AND TRIM(rv.agr1) = TRIM(a.agrupacion)     
                  AND a.agrupacion = pch_agr_1
                  AND rv.id_rep = psi_id_rep 
                  AND rv.usuariow = pvc_usuario
                  AND rv.cod_rep = pti_cod_rep)
    WHERE rv.id_rep = psi_id_rep 
      AND rv.usuariow = pvc_usuario
      AND rv.cod_rep = pti_cod_rep;

--  si por casualidad el trabajador esta dado de baja, se busca en el hist_agrupaciones
  UPDATE fco_embargos_rep rv
  SET dato1 = (SELECT b.dato
              FROM hist_agrupaciones a, datos_agr_trab b                          
              WHERE a.agrupacion = b.agrupacion 
                AND a.dato = b.dato 
                AND rv.compania = a.compania 
                AND rv.trabajador = a.trabajador
                AND TRIM(rv.agr1) = TRIM(a.agrupacion)  
                AND a.agrupacion = pch_agr_1
                AND a.fecha_alta = (select max(fecha_alta) from hist_agrupaciones h                        
                           where a.compania = h.compania AND
                                 a.agrupacion = h.agrupacion AND
                                 a.dato = h.dato AND                           
                                 a.trabajador = h.trabajador AND                          
                                 h.fecha_alta <= vdt_fecha) AND                          
                a.secuencia = (select max(secuencia) from hist_agrupaciones c                      
                               where a.compania = c.compania AND
                                     a.agrupacion = c.agrupacion AND
                                     a.dato = c.dato AND
                                     a.trabajador = c.trabajador AND
                                     c.fecha_alta <= vdt_fecha)
                AND rv.id_rep = psi_id_rep 
                AND rv.usuariow = pvc_usuario
                AND rv.cod_rep = pti_cod_rep
                AND rv.trabajador IN (SELECT trabajador FROM trabajadores_grales tg                    
                     WHERE rv.compania = tg.compania                    
                     AND rv.trabajador = tg.trabajador                    
                     AND tg.sit_trabajador = 2))                  
  WHERE NVL(TRIM(rv.dato1),' ') = ' '
  AND rv.id_rep = psi_id_rep 
  AND rv.usuariow = pvc_usuario
  AND rv.cod_rep = pti_cod_rep;
      
    --Agrupacion 1 - desc1
    UPDATE fco_embargos_rep rv
    SET desc1 = (SELECT b.descripcion
              FROM datos_agr_trab b                          
              WHERE TRIM(rv.agr1) = TRIM(b.agrupacion)
                AND TRIM(rv.dato1) = TRIM(b.dato)
                  AND rv.id_rep = psi_id_rep 
                  AND rv.usuariow = pvc_usuario
                  AND rv.cod_rep = pti_cod_rep)  
    WHERE rv.id_rep = psi_id_rep 
      AND rv.usuariow = pvc_usuario
      AND rv.cod_rep = pti_cod_rep;
  
  END IF;
    -- fin agrupacion 1

  IF NVL(TRIM(pch_agr_2),'0') <> '0' THEN
    --Agrupacion 2 - agr2
    UPDATE fco_embargos_rep rv
    SET agr2 = pch_agr_2
    WHERE rv.id_rep = psi_id_rep 
      AND rv.usuariow = pvc_usuario
      AND rv.cod_rep = pti_cod_rep;

    --Agrupacion 2 - dato2
    UPDATE fco_embargos_rep rv
    SET dato2 = (SELECT b.dato
                FROM rel_trab_agr a, datos_agr_trab b                          
                WHERE a.agrupacion = b.agrupacion 
                  AND a.dato = b.dato 
                  AND rv.compania = a.compania 
                  AND rv.trabajador = a.trabajador
                  AND TRIM(rv.agr1) = TRIM(a.agrupacion)     
                  AND a.agrupacion = pch_agr_2
                  AND rv.id_rep = psi_id_rep 
                  AND rv.usuariow = pvc_usuario
                  AND rv.cod_rep = pti_cod_rep)
    WHERE rv.id_rep = psi_id_rep 
      AND rv.usuariow = pvc_usuario
      AND rv.cod_rep = pti_cod_rep;

--  si por casualidad el trabajador esta dado de baja, se busca en el hist_agrupaciones
  UPDATE fco_embargos_rep rv
  SET dato2 = (SELECT b.dato
              FROM hist_agrupaciones a, datos_agr_trab b                          
              WHERE a.agrupacion = b.agrupacion 
                AND a.dato = b.dato 
                AND rv.compania = a.compania 
                AND rv.trabajador = a.trabajador
                AND TRIM(rv.agr2) = TRIM(a.agrupacion)  
                AND a.agrupacion = pch_agr_2
                AND a.fecha_alta = (select max(fecha_alta) from hist_agrupaciones h                        
                           where a.compania = h.compania AND
                                 a.agrupacion = h.agrupacion AND
                                 a.dato = h.dato AND                           
                                 a.trabajador = h.trabajador AND                          
                                 h.fecha_alta <= vdt_fecha) AND                          
                a.secuencia = (select max(secuencia) from hist_agrupaciones c                      
                               where a.compania = c.compania AND
                                     a.agrupacion = c.agrupacion AND
                                     a.dato = c.dato AND
                                     a.trabajador = c.trabajador AND
                                     c.fecha_alta <= vdt_fecha)
                AND rv.id_rep = psi_id_rep 
                AND rv.usuariow = pvc_usuario
                AND rv.cod_rep = pti_cod_rep
                AND rv.trabajador IN (SELECT trabajador FROM trabajadores_grales tg                    
                     WHERE rv.compania = tg.compania                    
                     AND rv.trabajador = tg.trabajador                    
                     AND tg.sit_trabajador = 2))                  
  WHERE NVL(TRIM(rv.dato2),' ') = ' '
  AND rv.id_rep = psi_id_rep 
  AND rv.usuariow = pvc_usuario
  AND rv.cod_rep = pti_cod_rep;
      
    --Agrupacion 2 - desc2
    UPDATE fco_embargos_rep rv
    SET desc2 = (SELECT b.descripcion
              FROM datos_agr_trab b                          
              WHERE TRIM(rv.agr2) = TRIM(b.agrupacion)
                AND TRIM(rv.dato2) = TRIM(b.dato)
                  AND rv.id_rep = psi_id_rep 
                  AND rv.usuariow = pvc_usuario
                  AND rv.cod_rep = pti_cod_rep)  
    WHERE rv.id_rep = psi_id_rep 
      AND rv.usuariow = pvc_usuario
      AND rv.cod_rep = pti_cod_rep;
  
  END IF;
    -- fin agrupacion 2

  IF NVL(TRIM(pch_agr_3),'0') <> '0' THEN
    --Agrupacion 3 - agr3
    UPDATE fco_embargos_rep rv
    SET agr3 = pch_agr_3
    WHERE rv.id_rep = psi_id_rep 
      AND rv.usuariow = pvc_usuario
      AND rv.cod_rep = pti_cod_rep;

    --Agrupacion 2 - dato2
    UPDATE fco_embargos_rep rv
    SET dato3 = (SELECT b.dato
                FROM rel_trab_agr a, datos_agr_trab b                          
                WHERE a.agrupacion = b.agrupacion 
                  AND a.dato = b.dato 
                  AND rv.compania = a.compania 
                  AND rv.trabajador = a.trabajador
                  AND TRIM(rv.agr3) = TRIM(a.agrupacion)     
                  AND a.agrupacion = pch_agr_3
                  AND rv.id_rep = psi_id_rep 
                  AND rv.usuariow = pvc_usuario
                  AND rv.cod_rep = pti_cod_rep)
    WHERE rv.id_rep = psi_id_rep 
      AND rv.usuariow = pvc_usuario
      AND rv.cod_rep = pti_cod_rep;

--  si por casualidad el trabajador esta dado de baja, se busca en el hist_agrupaciones
  UPDATE fco_embargos_rep rv
  SET dato3 = (SELECT b.dato
              FROM hist_agrupaciones a, datos_agr_trab b                          
              WHERE a.agrupacion = b.agrupacion 
                AND a.dato = b.dato 
                AND rv.compania = a.compania 
                AND rv.trabajador = a.trabajador
                AND TRIM(rv.agr3) = TRIM(a.agrupacion)  
                AND a.agrupacion = pch_agr_3
                AND a.fecha_alta = (select max(fecha_alta) from hist_agrupaciones h                        
                           where a.compania = h.compania AND
                                 a.agrupacion = h.agrupacion AND
                                 a.dato = h.dato AND                           
                                 a.trabajador = h.trabajador AND                          
                                 h.fecha_alta <= vdt_fecha) AND                          
                a.secuencia = (select max(secuencia) from hist_agrupaciones c                      
                               where a.compania = c.compania AND
                                     a.agrupacion = c.agrupacion AND
                                     a.dato = c.dato AND
                                     a.trabajador = c.trabajador AND
                                     c.fecha_alta <= vdt_fecha)
                AND rv.id_rep = psi_id_rep 
                AND rv.usuariow = pvc_usuario
                AND rv.cod_rep = pti_cod_rep
                AND rv.trabajador IN (SELECT trabajador FROM trabajadores_grales tg                    
                     WHERE rv.compania = tg.compania                    
                     AND rv.trabajador = tg.trabajador                    
                     AND tg.sit_trabajador = 2))                  
  WHERE NVL(TRIM(rv.dato3),' ') = ' '
  AND rv.id_rep = psi_id_rep 
  AND rv.usuariow = pvc_usuario
  AND rv.cod_rep = pti_cod_rep;
      
    --Agrupacion 3 - desc3
    UPDATE fco_embargos_rep rv
    SET desc3 = (SELECT b.descripcion
              FROM datos_agr_trab b                          
              WHERE TRIM(rv.agr3) = TRIM(b.agrupacion)
                AND TRIM(rv.dato3) = TRIM(b.dato)
                  AND rv.id_rep = psi_id_rep 
                  AND rv.usuariow = pvc_usuario
                  AND rv.cod_rep = pti_cod_rep)  
    WHERE rv.id_rep = psi_id_rep 
      AND rv.usuariow = pvc_usuario
      AND rv.cod_rep = pti_cod_rep;
  
  END IF;
    -- fin agrupacion 3
  
    COMMIT;
  
    EXCEPTION
  WHEN OTHERS THEN
       RAISE_APPLICATION_ERROR(-20002, SQLCODE||SQLERRM(SQLCODE));
       RETURN;                    
END;
/
CREATE PUBLIC SYNONYM sp_fco_rep_embargos FOR ADAM.sp_fco_rep_embargos;
GRANT EXECUTE ON sp_fco_rep_embargos TO adama;
CREATE OR REPLACE PROCEDURE sp_fco_del_rep_embargos
(
  psi_id_rep  number,
  pvc_usuario varchar2,
  pti_cod_rep number
)
/********************************************************************/
/*** Fiscal de Colombia de Data Tecnologia                        ***/
/*** Descripcion: limpia las tablas involucradas en la ejecucion  ***/
/*** del reporte de embargos                                         ***/
/*** Autor:	Diana Cecilia Rios Peinado                            ***/
/*** Fecha:	Mayo 15 de 2005                                      ***/
/********************************************************************/

AS
BEGIN
     DELETE fco_reportes_proc
     WHERE id_rep = psi_id_rep
     AND usuariow = pvc_usuario
     AND cod_rep = pti_cod_rep;
     
     DELETE fco_embargos_rep
     WHERE id_rep = psi_id_rep
     AND usuariow = pvc_usuario
     AND cod_rep = pti_cod_rep;

     DELETE fco_reportes_mm
     WHERE id_rep = psi_id_rep
     AND usuariow = pvc_usuario
     AND cod_rep = pti_cod_rep;
          
     COMMIT;
END;
/
CREATE PUBLIC SYNONYM sp_fco_del_rep_embargos FOR ADAM.sp_fco_del_rep_embargos;
GRANT EXECUTE ON sp_fco_del_rep_embargos TO adama;
