CREATE TABLE fco_embargos_trans
(
  id_rep      smallint NOT NULL,
  usuariow	  varchar2(30) NOT NULL,
  cod_rep     number(3) NOT NULL,  
  compania char(4) NOT NULL,
  anio number(4) NOT NULL,
  mes number(2) NOT NULL,
  trabajador char(10) NOT NULL,
  concepto number(5) NOT NULL,
  secuencia number(5) NOT NULL,
  importe number(13,2) NOT NULL
)
TABLESPACE TS_ADAMDATOS;

CREATE PUBLIC SYNONYM fco_embargos_trans FOR ADAM.fco_embargos_trans;
GRANT ALL ON fco_embargos_trans TO ADAMA;

CREATE TABLE fco_embargos_mm_paso
( 
  id_rep      smallint NOT NULL,
  usuariow	  varchar2(30) NOT NULL,
  cod_rep     number(3) NOT NULL,  
  consecutivo INTEGER,
  registro VARCHAR2(700)
)
TABLESPACE TS_ADAMDATOS;

CREATE PUBLIC SYNONYM fco_embargos_mm_paso FOR ADAM.fco_embargos_mm_paso;
GRANT ALL ON fco_embargos_mm_paso TO ADAMA;


CREATE TABLE fco_embargos_mm_paso0
( 
  id_rep      smallint NOT NULL,
  usuariow	  varchar2(30) NOT NULL,
  cod_rep     number(3) NOT NULL,  
  registro VARCHAR2(700)
)
TABLESPACE TS_ADAMDATOS;

CREATE PUBLIC SYNONYM fco_embargos_mm_paso0 FOR ADAM.fco_embargos_mm_paso0;
GRANT ALL ON fco_embargos_mm_paso0 TO ADAMA;



CREATE OR REPLACE PROCEDURE sp_fco_del_rep_embargos_mm
(
  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_reportes_mm
     WHERE id_rep = psi_id_rep
     AND usuariow = pvc_usuario
     AND cod_rep = pti_cod_rep;

     DELETE fco_embargos_trans    
     WHERE id_rep = psi_id_rep
     AND usuariow = pvc_usuario
     AND cod_rep = pti_cod_rep;

    DELETE fco_embargos_mm_paso0
    WHERE id_rep = psi_id_rep
      AND usuariow = pvc_usuario
      AND cod_rep = pti_cod_rep;
    
    DELETE fco_embargos_mm_paso
    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_mm FOR ADAM.sp_fco_del_rep_embargos_mm;
GRANT EXECUTE ON sp_fco_del_rep_embargos_mm TO adama;
CREATE OR REPLACE PROCEDURE sp_fco_crea_embargos_mm
(
  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,
  psi_anio        varchar2,
  pti_mes         varchar2,
  pch_ciudad_pago varchar2
)
/**********************************************************************/
/*** Modulo:      Módulo Fiscal ADAM V.3                            ***/
/*** Descripcion: Medio Magnético Embargos                          ***/
-- REVISION GENERAL Juan Carlos Leguizamon
-- DIC. 06/04. PARA SURTIGAS
/**********************************************************************/
/*** Adecuacion: Diana Cecilia Rios Peinado                         ***/
/*** Mayo 15 de 2005                                                ***/
AS
	vch_agr_embargos  CHAR(3);
	csi_id_cal        INTEGER; --calendario_procesos.id_calendario%TYPE;
	vvc_sql           VARCHAR2(500);
	vvc_tabla         VARCHAR2(16);   
	vsi_registros     INTEGER;
	vdt_fecha_hoy	    DATE;
	comilla				CHAR(1);

  vch_compania    char(4);
  vsi_anio        number(5);
  vti_mes         number(3);
  vch_ciudad_pago char(2);
  
  vti_trab_dde number(1);
  vti_trab_hta number(1);  
    
/*************************************/
/* Cursor para hallar la institución */
/*************************************/
  CURSOR cr_agr_embargos IS
  SELECT 
    dato
  FROM 
    vw_fdt_parametros
  WHERE 
    pais = 'COL'
    AND compania = vch_compania
    AND parametro = 'otros'
    AND secuencia = 1;

/***************************************/
/* Cursor para hallar el id_calendario */
/***************************************/
  CURSOR cr_id_calendario IS
  SELECT    
    cp.id_calendario    
  FROM    
    calendario_procesos cp, 
    cal_proc_cias cpc    
  WHERE    
    cp.id_calendario = cpc.id_calendario 
    AND cpc.compania = vch_compania 
    AND cp.anio = vsi_anio 
    AND cp.mes_acumular = vti_mes 
    AND cpc.sit_periodo = 2;    

BEGIN
	comilla := '''';
	vdt_fecha_hoy := sysdate;

  vch_compania := TRIM(pch_compania);
  vsi_anio := TO_NUMBER(psi_anio);  
  vti_mes := TO_NUMBER(pti_mes);  
  vch_ciudad_pago := TRIM(pch_ciudad_pago);
  
  /* Hallar la agrupación de embargos */
  OPEN cr_agr_embargos;
  FETCH cr_agr_embargos INTO vch_agr_embargos;
  CLOSE cr_agr_embargos;
  
  /* ELIMINAR DATOS ACTUALES */    
  DELETE fco_embargos_trans    
  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;

   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;
    
  OPEN cr_id_calendario;    

  /* INSERTAR REGISTROS DE TRANSACCIONES NS */
  LOOP
    FETCH cr_id_calendario INTO csi_id_cal;
    EXIT WHEN cr_id_calendario%NOTFOUND;
  
    vvc_sql := 'INSERT INTO fco_embargos_trans ' ||
    ' SELECT ' ||  psi_id_rep || ', ' ||
                  comilla || pvc_usuario || comilla || ', ' || pti_cod_rep || ', ' ||
    ' tns.compania, ' || LTRIM(RTRIM(vsi_anio)) || ',' || LTRIM(RTrim(vti_mes)) || ',' ||    
    ' tns.trabajador, tns.concepto, tns.factor_02, SUM(tns.importe)' ||    
    ' FROM' ||    
    ' trans_' || LTRIM(RTRIM(vch_compania)) || '_' || LTRIM(RTRIM(csi_id_cal)) || ' tns,' ||    
    ' rel_conceptos_agr rca' ||    
    ' WHERE' ||    
    ' tns.compania = rca.compania And tns.concepto = rca.concepto And' ||    
    ' rca.agr_conceptos = ' || comilla || vch_agr_embargos || comilla || ' And' ||    
    ' rca.signo = ' || comilla || '+' || comilla || ' And' ||
    ' tns.compania = ' || comilla || vch_compania || comilla || ' And' ||    
    ' tns.id_calendario = ' || LTRIM(RTRIM(csi_id_cal)) || ' And' ||    
    ' tns.importe > 0' ||
    ' AND ' ||
    ' (( '|| TRIM(vti_trab_dde) || ' = 1) OR (tns.trabajador >= RTRIM(''' || pch_trab_dde || ''') AND '|| TRIM(vti_trab_dde) || ' = 0)) AND' ||
    ' (( '|| TRIM(vti_trab_hta) || ' = 1) OR (tns.trabajador <= RTRIM(''' || pch_trab_hta || ''') AND '|| TRIM(vti_trab_hta) || ' = 0)) ' ||
    ' GROUP BY' ||    
    ' tns.compania, tns.trabajador, tns.concepto, tns.factor_02';

    COMMIT;
    EXECUTE IMMEDIATE vvc_sql;
    COMMIT;
  END LOOP;
  CLOSE cr_id_calendario;

  /* INSERTAR REGISTROS DE LIQUIDACIONES */  
  INSERT INTO 
    fco_embargos_trans    
  SELECT    
    psi_id_rep, pvc_usuario, pti_cod_rep, ld.compania, vsi_anio, vti_mes, ld.trabajador, ld.concepto, ld.factor_02, SUM(ld.importe)    
  FROM    
    liquidaciones_enc le, liquidaciones_det ld, rel_conceptos_agr rca    
  WHERE    
    le.compania = ld.compania 
    AND le.numero_liquidacion = ld.numero_liquidacion 
    AND ld.compania = rca.compania 
    AND ld.concepto = rca.concepto 
    AND rca.agr_conceptos = vch_agr_embargos 
    AND rca.signo = '+' 
    AND ld.compania = vch_compania 
    AND le.sit_liquidacion = 2 
    AND ld.sit_transaccion = 2 
    AND ld.anio_acumular = vsi_anio 
    AND ld.mes_acumular = vti_mes 
    AND ld.importe > 0   
    AND ((TRIM(vti_trab_dde) = 1) OR (ld.trabajador >= RTRIM(pch_trab_dde) AND TRIM(vti_trab_dde) = 0)) AND
       ((TRIM(vti_trab_hta) = 1) OR (ld.trabajador <= RTRIM(pch_trab_hta) AND TRIM(vti_trab_hta) = 0))      
  GROUP BY     
    ld.compania, ld.trabajador, ld.concepto, ld.factor_02;

  -- 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_trans
    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_trans
    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;
    
    
  /* GENERACION DEL REGISTRO DE DATOS */
  INSERT INTO fco_embargos_mm_paso0
  SELECT psi_id_rep, pvc_usuario, pti_cod_rep,
    TO_CHAR(vdt_fecha_hoy, 'YYYYMMDD') ||
    LPAD(LTRIM(RTRIM(NVL(a.oficina_origen, '10'))), 4, '0') ||
    LPAD(LTRIM(RTRIM(NVL(a.oficina_destino, '10'))), 4, '0') ||
    SUBSTR(LTRIM(RTRIM(clase_deposito)), 1, 1) ||    
    RPAD(a.numero_expediente, 10, ' ') ||
    LPAD(NVL(RTRIM(LTRIM(a.codigo_juzgado)), '0'), 12, '0') ||
    LPAD(NVL(RTRIM(LTRIM(a.cuenta)), '0'), 12, '0') ||
    LPAD(RTRIM(LTRIM(FLOOR(SUM(NVL(b.importe, 0))))), 13, '0') || '.00' ||
    SUBSTR(LTRIM(RTRIM(a.tipo_id_demandante)), 1, 1) ||
    LPAD(NVL(RTRIM(LTRIM(a.id_demandante)), '0'), 11, '0') ||
    '1' ||
    LPAD(NVL(RTRIM(LTRIM(a.cedula)), '0'), 11, '0') ||
    RPAD(a.apellido_demandante, 20, ' ') ||
    RPAD(a.nombre_demandante, 20, ' ') ||
    RPAD(a.apellidos, 20, ' ') ||
    RPAD(a.nombres, 20, ' ') registro
  FROM     
    vw_fdt_embargos a, fco_embargos_trans b    
  WHERE    
    a.compania = b.compania 
    AND a.trabajador = b.trabajador 
    AND a.concepto = b.concepto 
    AND a.secuencia = b.secuencia 
    AND b.compania = vch_compania 
    AND b.anio = vsi_anio 
    AND b.mes = vti_mes 
    AND a.banco = 1  
    AND a.ciudad_pago = vch_ciudad_pago
    AND b.id_rep = psi_id_rep
    AND b.usuariow = pvc_usuario
    AND b.cod_rep = pti_cod_rep
  GROUP BY    
    vdt_fecha_hoy, 
    a.oficina_origen, a.oficina_destino, a.clase_deposito, a.numero_expediente,    
    a.codigo_juzgado, a.cuenta, a.tipo_id_demandante, a.id_demandante,    
    a.cedula, a.apellido_demandante, a.nombre_demandante,    
    a.apellidos, a.nombres;

  INSERT INTO fco_embargos_mm_paso 
  SELECT psi_id_rep, pvc_usuario, pti_cod_rep,
  ROWNUM,registro 
  FROM fco_embargos_mm_paso0
  WHERE id_rep = psi_id_rep
    AND usuariow = pvc_usuario
    AND cod_rep = pti_cod_rep;

  --se guarda con secuencia de archivo 1
  INSERT INTO 
    fco_reportes_mm    
  SELECT     
    psi_id_rep, pvc_usuario,  pti_cod_rep, 1, 
    consecutivo + 2,     
    LPAD(RTrim(consecutivo), 6, '0') || registro || CHR(10)    
  FROM    
    fco_embargos_mm_paso
  WHERE id_rep = psi_id_rep
    AND usuariow = pvc_usuario
    AND cod_rep = pti_cod_rep;
    
  SELECT 
    MAX(consecutivo)    
  INTO vsi_registros
  FROM 
    fco_embargos_mm_paso
  WHERE id_rep = psi_id_rep
    AND usuariow = pvc_usuario
    AND cod_rep = pti_cod_rep;
    
  DELETE fco_embargos_mm_paso0
  WHERE id_rep = psi_id_rep
    AND usuariow = pvc_usuario
    AND cod_rep = pti_cod_rep;
  
  DELETE fco_embargos_mm_paso
  WHERE id_rep = psi_id_rep
    AND usuariow = pvc_usuario
    AND cod_rep = pti_cod_rep;
  
  INSERT INTO 
    fco_reportes_mm    
  SELECT  
    psi_id_rep, pvc_usuario,  pti_cod_rep, 1,
    1,     
    LPAD(' ', 23, ' ') ||
    LPAD(NVL(LTRIM(RTRIM(vsi_registros)), '0'), 10, '0') ||
    LPAD(' ', 40, ' ') ||
    '5' ||
    '00' || SUBSTR(registro_fiscal, 1, 9) ||    
    LPAD(' ', 12, ' ') ||
    LPAD(nombre_cia, 40, ' ') ||
    LPAD(' ', 51, ' ')
  FROM    
    vw_fco_companias    
  WHERE    
    compania = vch_compania;

  COMMIT;
  EXCEPTION
  WHEN OTHERS THEN
       RAISE_APPLICATION_ERROR(-20002, SQLCODE||SQLERRM(SQLCODE));
       RETURN;          
          
END;
/
CREATE PUBLIC SYNONYM sp_fco_crea_embargos_mm FOR ADAM.sp_fco_crea_embargos_mm;
GRANT EXECUTE ON sp_fco_crea_embargos_mm TO adama;
