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;
