DROP TABLE fco_cir_cab;
CREATE TABLE fco_cir_cab
(
  id_rep      number(5) NOT NULL,
  usuariow	  varchar2(30) NOT NULL,
  cod_rep     number(3) NOT NULL,  
  compania         char(4) NOT NULL,
  sucursal         char(10) NOT NULL,
  anio 	     number(5) NOT NULL,
  fecha_exp        date,
  nombre_cia       varchar2(60),
  registro_fiscal  varchar2(20),
  domicilio        varchar2(40),
  municipio        varchar2(60),
  departamento     varchar2(60),
  sucursales       varchar2(255),
  pagador_nombre   varchar2(100),
  pagador_id       varchar2(60),
  poner_carreta    number(5),
  cod_admon        varchar2(40),
  cod_depto        char(2), 
  cod_municipio    char(3), 
  telefono         varchar2(25),
    Constraint PK_fco_cir_cab PRIMARY KEY (id_rep, usuariow, cod_rep, compania, sucursal, anio)
    USING INDEX TABLESPACE TS_ADAMINDICES
)
TABLESPACE TS_ADAMDATOS;

CREATE PUBLIC SYNONYM fco_cir_cab FOR ADAM.fco_cir_cab;
GRANT ALL ON fco_cir_cab TO ADAMA;
DROP TABLE fco_cir_det;
CREATE TABLE fco_cir_det
(
  id_rep      number(5) NOT NULL,
  usuariow	  varchar2(30) NOT NULL,
  cod_rep     number(3) NOT NULL,  
  compania    char(4) NOT NULL,
  sucursal    char(10) NOT NULL,
  anio 	      number(5) NOT NULL,
  numero           number(5) NOT NULL,
  trabajador       char(10),
  nombre           varchar2(60),
  cedula           varchar2(20),
  fecha_desde      date,
  fecha_hasta      date,
  campo_ng         number(19,6),
  campo_gr         number(19,6),
  secuencia        number(5),
  valor_retencion  number(13,2),
  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  
)
TABLESPACE TS_ADAMDATOS;

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

CREATE OR REPLACE VIEW vw_fco_rep_cir
AS
/********************************************************************/
/*** Módulo Fiscal de Colombia - Reporte de CIR                   ***/
/*** Descripcion: Vista que ve el reporte del CIR                 ***/
/*** Autor:	Diana Cecilia Rios Peinado                            ***/
/*** Fecha:	Mayo 15 de 2005                                       ***/
/********************************************************************/
SELECT a.id_rep, a.usuariow, a.cod_rep, a.compania, a.sucursal, a.anio,
a.fecha_exp, a.nombre_cia, a.registro_fiscal, a.domicilio, a.municipio, a.departamento,
a.sucursales, a.pagador_nombre, a.pagador_id, a.poner_carreta, a.cod_admon, a.cod_depto,
a.cod_municipio, a.telefono, b.numero, b.trabajador, b.nombre, b.cedula,
b.fecha_desde, b.fecha_hasta, b.campo_ng, b.campo_gr, b.secuencia, b.valor_retencion,
b.agr1, b.dato1, b.desc1, b.agr2, b.dato2, b.desc2, b.agr3, b.dato3, b.desc3
FROM fco_cir_cab a, fco_cir_det b
WHERE a.Id_Rep = b.Id_Rep
AND a.usuariow = b.usuariow
AND a.cod_rep = b.cod_rep
AND a.compania = b.compania
AND a.sucursal = b.sucursal;

CREATE PUBLIC SYNONYM vw_fco_rep_cir FOR ADAM.vw_fco_rep_cir;
GRANT ALL ON vw_fco_rep_cir TO ADAMA;
CREATE OR REPLACE PROCEDURE sp_fco_rep_cir_mm
(
  psi_id_rep  number,
  pvc_usuario varchar2,
  pti_cod_rep number  
)

/********************************************************************/
/*** Modulo Fiscal Colombiano de Data Tecnologia                  ***/
/*** Descripcion: crea archivos planos con la informacion que esta***/
/*** en la tabla fco_cir_det                                      ***/
/*** Autor:	Diana Cecilia Rios Peinado                            ***/
/*** Fecha:	Julio 13 de 2005                                      ***/
/********************************************************************/
AS
cch_compania char(4);
cch_trabajador char(10);
cch_cedula varchar2(20);
cch_anio number(5);

vnu_sec_31 number(19,6);
vnu_sec_32 number(19,6);
vnu_sec_33 number(19,6);
vnu_sec_34 number(19,6);
vnu_sec_35 number(19,6);
vnu_sec_36 number(19,6);
vnu_sec_37 number(19,6);
vnu_sec_37s number(19,6);
vnu_sec_38 number(19,6);
vnu_sec_39 number(19,6);
vnu_sec_40 number(19,6);

vvc_reg_fiscal varchar2(20);
vvc_nombre_trab varchar2(60);
vvc_domicilio varchar2(40);
vvc_poblacion varchar2(30);

CURSOR cr_trab_cir IS
SELECT DISTINCT compania, trabajador, cedula, anio
FROM fco_cir_det
WHERE id_rep = psi_id_rep
AND usuariow = pvc_usuario
AND cod_rep = pti_cod_rep
AND secuencia = 1;


BEGIN

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

	OPEN cr_trab_cir;
	FETCH cr_trab_cir 
    INTO cch_compania, cch_trabajador, cch_cedula, cch_anio;
	WHILE cr_trab_cir%FOUND  LOOP  

    --hallando la casilla 31(ingresos)
    vnu_sec_31 := 0;
    BEGIN
       SELECT campo_gr
       INTO vnu_sec_31
       FROM fco_cir_det
       WHERE id_rep = psi_id_rep
       AND usuariow = pvc_usuario
       AND cod_rep = pti_cod_rep
       AND compania = cch_compania
       AND anio = cch_anio
       AND trabajador = cch_trabajador
       AND secuencia = 1;           
    EXCEPTION
       WHEN OTHERS THEN vnu_sec_31 := 0;
    END;
    
    --hallando la casilla 32(cesantias)
    vnu_sec_32 := 0;
    BEGIN
       SELECT campo_ng
       INTO vnu_sec_32
       FROM fco_cir_det
       WHERE id_rep = psi_id_rep
       AND usuariow = pvc_usuario
       AND cod_rep = pti_cod_rep
       AND compania = cch_compania
       AND anio = cch_anio
       AND trabajador = cch_trabajador
       AND secuencia = 2;           
    EXCEPTION
       WHEN OTHERS THEN vnu_sec_32 := 0;
    END;
    
    --hallando la casilla 33(gastos de representacion)
    vnu_sec_33 := 0;
    BEGIN
       SELECT campo_ng
       INTO vnu_sec_33
       FROM fco_cir_det
       WHERE id_rep = psi_id_rep
       AND usuariow = pvc_usuario
       AND cod_rep = pti_cod_rep
       AND compania = cch_compania
       AND anio = cch_anio
       AND trabajador = cch_trabajador
       AND secuencia = 3;
    EXCEPTION
       WHEN OTHERS THEN vnu_sec_33 := 0;
    END;

    --hallando la casilla 34(Pension)
    vnu_sec_34 := 0;
    BEGIN
       SELECT campo_ng
       INTO vnu_sec_34
       FROM fco_cir_det
       WHERE id_rep = psi_id_rep
       AND usuariow = pvc_usuario
       AND cod_rep = pti_cod_rep
       AND compania = cch_compania
       AND anio = cch_anio
       AND trabajador = cch_trabajador
       AND secuencia = 4;
    EXCEPTION
       WHEN OTHERS THEN vnu_sec_34 := 0;
    END;

    --hallando la casilla 35(otros ingresos)
    vnu_sec_35 := 0;
    BEGIN
       SELECT campo_ng
       INTO vnu_sec_35
       FROM fco_cir_det
       WHERE id_rep = psi_id_rep
       AND usuariow = pvc_usuario
       AND cod_rep = pti_cod_rep
       AND compania = cch_compania
       AND anio = cch_anio
       AND trabajador = cch_trabajador
       AND secuencia = 5;
    EXCEPTION
       WHEN OTHERS THEN vnu_sec_35 := 0;
    END;

    --hallando la casilla 36(total ingresos brutos)
    vnu_sec_36 := NVL(vnu_sec_31,0) + NVL(vnu_sec_32,0) + NVL(vnu_sec_33,0) + NVL(vnu_sec_34,0) + NVL(vnu_sec_35,0);

    --hallando la casilla 37(salud, pension y fondo de solidaridad)
    vnu_sec_37 := 0;
    BEGIN
       SELECT campo_ng
       INTO vnu_sec_37
       FROM fco_cir_det
       WHERE id_rep = psi_id_rep
       AND usuariow = pvc_usuario
       AND cod_rep = pti_cod_rep
       AND compania = cch_compania
       AND anio = cch_anio
       AND trabajador = cch_trabajador
       AND secuencia = 6;
    EXCEPTION
       WHEN OTHERS THEN vnu_sec_37 := 0;
    END;
    
    --hallando la casilla 37S (aporte salud)
    vnu_sec_37s := 0;
    BEGIN
       SELECT campo_ng
       INTO vnu_sec_37s
       FROM fco_cir_det
       WHERE id_rep = psi_id_rep
       AND usuariow = pvc_usuario
       AND cod_rep = pti_cod_rep
       AND compania = cch_compania
       AND anio = cch_anio
       AND trabajador = cch_trabajador
       AND secuencia = 9;
    EXCEPTION
       WHEN OTHERS THEN vnu_sec_37s := 0;
    END;

    --hallando la casilla 38(pension voluntaria)
    vnu_sec_38 := 0;
    BEGIN
       SELECT campo_ng
       INTO vnu_sec_38
       FROM fco_cir_det
       WHERE id_rep = psi_id_rep
       AND usuariow = pvc_usuario
       AND cod_rep = pti_cod_rep
       AND compania = cch_compania
       AND anio = cch_anio
       AND trabajador = cch_trabajador
       AND secuencia = 7;
    EXCEPTION
       WHEN OTHERS THEN vnu_sec_38 := 0;
    END;

    --hallando la casilla 39(Rentas excentas)
    vnu_sec_39 := 0;
    BEGIN
       SELECT campo_ng
       INTO vnu_sec_39
       FROM fco_cir_det
       WHERE id_rep = psi_id_rep
       AND usuariow = pvc_usuario
       AND cod_rep = pti_cod_rep
       AND compania = cch_compania
       AND anio = cch_anio
       AND trabajador = cch_trabajador
       AND secuencia = 8;
    EXCEPTION
       WHEN OTHERS THEN vnu_sec_39 := 0;
    END;

    --hallando la casilla 40(Valor de la retencion)
    vnu_sec_40 := 0;
    BEGIN
       SELECT valor_retencion
       INTO vnu_sec_40
       FROM fco_cir_det
       WHERE id_rep = psi_id_rep
       AND usuariow = pvc_usuario
       AND cod_rep = pti_cod_rep
       AND compania = cch_compania
       AND anio = cch_anio
       AND trabajador = cch_trabajador
       AND secuencia = 1;
    EXCEPTION
       WHEN OTHERS THEN vnu_sec_40 := 0;
    END;

    --buscar el registro fiscal del trabajador
    vvc_reg_fiscal := '0';
    vvc_nombre_trab := '0';
    vvc_domicilio := '0';
    vvc_poblacion := '0';
    BEGIN
       SELECT REPLACE(nombre,'/',' '), registro_fiscal, domicilio, poblacion
       INTO vvc_nombre_trab, vvc_reg_fiscal, vvc_domicilio, vvc_poblacion
       FROM trabajadores
       WHERE trabajador = cch_trabajador;
    EXCEPTION
       WHEN OTHERS THEN vvc_reg_fiscal := '0';
                        vvc_nombre_trab := '0';
                        vvc_domicilio := '0';
                        vvc_poblacion := '0';       
    END;    

    --Armamos el archivo plano (TDIAN). La sec grabada 1
    INSERT INTO fco_reportes_mm
    SELECT psi_id_rep, pvc_usuario, pti_cod_rep, 1, 1, 
    NVL(TRIM(cch_cedula),'0') || '|' ||
    NVL(TRIM(vvc_nombre_trab),'0') || '|' ||
    NVL(TRIM(vvc_domicilio),'0') || '|' ||
    NVL(TRIM(vvc_poblacion),'0') || '|' ||
    NVL(vnu_sec_31, 0) || '|' ||
    NVL(vnu_sec_32, 0) || '|' ||
    NVL(vnu_sec_36, 0) || '|' ||
    NVL(vnu_sec_34, 0)            
    FROM dual;
    
    --Armamos el archivo plano (TCIRE). La sec grabada 2
    INSERT INTO fco_reportes_mm
    SELECT psi_id_rep, pvc_usuario, pti_cod_rep, 2, 1, 
    NVL(TRIM(vvc_reg_fiscal),'0') || '|' ||
    NVL(cch_anio, 0) || '|' ||
    NVL(TRIM(cch_cedula),'0') || '|' ||
    NVL(vnu_sec_31, 0) || '|' ||
    NVL(vnu_sec_32, 0) || '|' ||
    NVL(vnu_sec_33, 0) || '|' ||
    NVL(vnu_sec_34, 0) || '|' ||
    NVL(vnu_sec_35, 0) || '|' ||    
    NVL(vnu_sec_36, 0) || '|' ||
    NVL(vnu_sec_37, 0) || '|' ||
    NVL(vnu_sec_37s, 0) || '|' ||    
    NVL(vnu_sec_38, 0) || '|' ||
    NVL(vnu_sec_39, 0) || '|' ||
    NVL(vnu_sec_40, 0)            
    FROM dual;
    
  	FETCH cr_trab_cir 
    INTO cch_compania, cch_trabajador, cch_cedula, cch_anio;
      
	END LOOP;
	CLOSE cr_trab_cir;

  COMMIT;
END;
/
CREATE PUBLIC SYNONYM sp_fco_rep_cir_mm FOR ADAM.sp_fco_rep_cir_mm;
GRANT EXECUTE ON sp_fco_rep_cir_mm TO adama;
  
CREATE OR REPLACE PROCEDURE sp_fco_rep_cir
(
  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,
  pch_sucursal    varchar2,
  psm_anio        varchar2,
  pdt_fecha_exp   varchar2
)
/**********************************************************************************/
/*** Modulo:      Modulo Fiscal Colombiano ADAM V.3				                      ***/
/*** Descripcion: Genera tablas con la informacion del Certificado de Ingresos	***/
/***              y Retenciones DIAN                                    	      ***/
/*** Tablas:	  vw_fdt_parametros, Ciudades, Departamentos, Trabajadores,           ***/
/***              Trabajadores_Grales, Acumulados_Mensuales                     ***/
/***		  Version con Sociedades: MF_sucursales, rel_trab_agr    	              ***/
/*** Vistas:	  vw_companias                                                    ***/
/*** Autor:	  Juan Carlos Leguizamon					                                  ***/
/*** Fecha:	  Febrero 4 de 2002						                                      ***/
/*** Revision: 	  Febrero 22 de 2002						                                ***/
/*** Revision: 	  Marzo 17 de 2002 (SURTIGAS)					                          ***/
/*** Motor:	  Oracle 8		Version interna: 1.00                                 ***/
/**********************************************************************************/
/*** Adecuacion: Diana Cecilia Rios Peinado                                     ***/
/*** Mayo 15 de 2005                                                            ***/

AS
    vsm_numero      number(5);
    vch_agr_ing     char(3);
    vch_agr_ngr     char(3);
    vch_agr_ces     char(3);
    vch_agr_pen     char(3);
    vch_agr_apr     char(3);
    vtn_tipo_param  number(1);
    vch_agr_rtf     char(3);
    vsm_cnc_rtf     number(5);
    cch_trabajador  char(10);
    cvc_nombre      varchar2(100);
    cvc_cedula      varchar2(25);
    cdt_ingreso     date;
    cdt_baja        date;
    vdt_desde       date;
    vdt_hasta       date;
    vdc_ingresos    number(19,6);
    vdc_no_gravados number(19,6);
    vdc_cesantias   number(19,6);
    vdc_retencion   number(19,6);
    vdc_valor_ng    number(19,6);
    vdc_valor_gr    number(19,6);
    vch_x_sucursal  char(2);
    vdc_tope_exc    number(19,6);
    vdc_sal_minimo  number(19,6);
    vdc_valor_pn    number(19,6);
    vdc_valor_ap    number(19,6);
    vdc_tope_aporte number(19,6);
    vsm_dif_dias    number(19,6);
    /* LAG - Cambios para Certificado 2005 */
    vch_agr_AFP     char(3);
    vch_agr_EPS     char(3); 
    vch_agr_VOL     char(3);
    vch_agr_VOL_SB  char(3);
    vdc_valor_AFP   decimal(19,6);
    vdc_valor_EPS   decimal(19,6);
    vdc_valor_VOL   decimal(19,6);

    --DCRP para convertir los parametros de entrada
    vch_compania    char(4);
    vch_sucursal    char(10);
    vsm_anio        number(5);
    vdt_fecha       date;    

    vti_trab_dde number(1);
    vti_trab_hta number(1);  
    
    --DCRP, faltan los gastos de representacion
    vch_agr_rep     char(3);
    vdc_gastos_rep  decimal(19,6);
    vdc_gastos_rep_ng  decimal(19,6);
    vdc_gastos_rep_gr  decimal(19,6);
    
    CURSOR cr_detalle_suc IS
    SELECT 
    T.trabajador, SUBSTR(T.nombre,1,60), T.clave_unica, TG.fecha_ingreso, TG.fecha_baja
    FROM
    trabajadores T, trabajadores_grales TG, vw_fco_div_trabajador SUC
    WHERE 
    TG.trabajador = T.trabajador And
    TG.compania = SUC.compania And TG.trabajador = SUC.trabajador And
    TG.compania = vch_compania And
    To_Number(To_Char(Nvl(TG.fecha_baja, sysdate), 'yyyy')) >= vsm_anio And
    To_Number(To_Char(TG.fecha_ingreso, 'yyyy')) <= vsm_anio And
    SUC.dato = vch_sucursal
    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))    
    ORDER BY   T.nombre;

    CURSOR cr_detalle IS
    SELECT 
    T.trabajador, SUBSTR(T.nombre,1,60), T.clave_unica, TG.fecha_ingreso, TG.fecha_baja
    FROM
    trabajadores T, trabajadores_grales TG
    WHERE 
    TG.trabajador = T.trabajador And
    TG.compania = vch_compania And
    To_Number(To_Char(Nvl(TG.fecha_baja, sysdate), 'yyyy')) >= vsm_anio And
    To_Number(To_Char(TG.fecha_ingreso, 'yyyy')) <= vsm_anio
    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))    
    ORDER BY
    T.nombre;
BEGIN
     vch_compania := TRIM(pch_compania);
     vch_sucursal := TRIM(pch_sucursal);
     vdt_fecha := TO_DATE(SUBSTR(TRIM(pdt_fecha_exp),1,8),'YYYYMMDD');
     vsm_anio := TO_NUMBER(psm_anio);

     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;
     
    /* Recuperar los parametros */
    SELECT dato INTO vch_agr_ing FROM vw_fdt_parametros
    WHERE pais = 'COL' AND compania = vch_compania AND parametro = 'retencion' AND secuencia = 1;

    SELECT dato INTO vch_agr_ngr FROM vw_fdt_parametros 
    WHERE pais = 'COL' AND compania = vch_compania AND parametro = 'retencion' AND secuencia = 2;

    SELECT dato INTO vch_agr_ces FROM vw_fdt_parametros 
    WHERE pais = 'COL' AND compania = vch_compania AND parametro = 'retencion' AND secuencia = 3;

    SELECT dato INTO vch_agr_rep FROM vw_fdt_parametros 
    WHERE pais = 'COL' AND compania = vch_compania AND parametro = 'retencion' AND secuencia = 4;
            
    SELECT dato INTO vch_agr_pen FROM vw_fdt_parametros         
    WHERE pais = 'COL' AND compania = vch_compania AND parametro = 'retencion' AND secuencia = 5;

    SELECT dato INTO vch_agr_apr FROM vw_fdt_parametros         
    WHERE pais = 'COL' AND compania = vch_compania AND parametro = 'retencion' AND secuencia = 6;

    SELECT tipo INTO vtn_tipo_param FROM vw_fdt_parametros
    WHERE pais = 'COL' AND compania = vch_compania And parametro = 'retencion' And secuencia = 7;  

    IF vtn_tipo_param = 0 THEN
        SELECT dato INTO vsm_cnc_rtf FROM vw_fdt_parametros
        WHERE pais = 'COL' AND compania = vch_compania And parametro = 'retencion' And secuencia = 7;
    ELSE
        SELECT dato INTO vch_agr_rtf FROM vw_fdt_parametros
        WHERE pais = 'COL' AND compania = vch_compania And parametro = 'retencion' And secuencia = 7;
    END IF;

    SELECT dato INTO vch_x_sucursal FROM vw_fdt_parametros 
    WHERE pais = 'COL' AND compania = vch_compania AND parametro = 'parafiscal' AND secuencia = 5;

    /* LAG - Nuevos parámetros certificado 2005 */
    SELECT dato INTO vch_agr_AFP FROM vw_fdt_parametros               
    WHERE pais = 'COL' AND compania = vch_compania AND parametro = 'retencion' AND secuencia = 17;

    SELECT dato INTO vch_agr_EPS FROM vw_fdt_parametros               
    WHERE pais = 'COL' AND compania = vch_compania AND parametro = 'retencion' AND secuencia = 18;

    SELECT dato INTO vch_agr_VOL FROM vw_fdt_parametros               
    WHERE pais = 'COL' AND compania = vch_compania AND parametro = 'retencion' AND secuencia = 19;

    /* HALLAR TOPE DE EXCENSION */
    vdc_tope_exc := 0;    
    BEGIN
      SELECT CUOTA_FIJA INTO vdc_tope_exc FROM IMPUESTOS    
      WHERE IMPUESTO = 5  
      AND LIMITE_INFERIOR = vsm_anio;
    EXCEPTION
      WHEN OTHERS THEN vdc_tope_exc := 0;
    END;
      
    /* Hallar valor salario minimo */
    vdc_sal_minimo := 0;
    BEGIN
      SELECT salario_minimo INTO vdc_sal_minimo
      FROM hist_sal_min_zona
      WHERE zona = 'A'
      AND to_number(to_char(fecha, 'yyyy')) = vsm_anio;
    EXCEPTION
      WHEN OTHERS THEN vdc_sal_minimo := 0;
    END;
    /* Borrar los datos actuales */
    DELETE fco_cir_cab
    WHERE id_rep = psi_id_rep
    AND usuariow = pvc_usuario
    AND cod_rep = pti_cod_rep;
    
    DELETE fco_cir_det
    WHERE id_rep = psi_id_rep
    AND usuariow = pvc_usuario
    AND cod_rep = pti_cod_rep;
        
    /* Insertar los nuevos datos */
    /* PARTE I: Cabecera del Certificado */
    INSERT INTO fco_cir_cab
    (id_rep, usuariow, cod_rep, compania, sucursal, anio, fecha_exp,
    nombre_cia, registro_fiscal, domicilio,
    municipio, departamento, 
    sucursales, pagador_nombre, pagador_id,
    poner_carreta, cod_admon, cod_depto, cod_municipio, telefono) 
    SELECT 
    psi_id_rep, pvc_usuario, pti_cod_rep, vch_compania, SubStr(vch_sucursal,1,10), vsm_anio, vdt_fecha, 
    C.nombre_cia, replace(replace(SubStr(C.registro_fiscal,1,20),'.',''),'-','') , C.domicilio,
    M.nombre, D.nombre,
    SubStr(C.agencias,1,255), C.pagador_nombre, C.pagador_id, 0,
    c.cod_admon, c.cod_dpto, c.cod_ciudad, c.telefono
    FROM
    vw_fco_companias C, fco_ciudades M, fco_departamentos D
    WHERE
    C.cod_dpto = D.codigo And
    C.cod_ciudad = M.codigo And
    M.cod_dpto = D.codigo And
    C.compania = vch_compania;

    /* PARTE II: Detalle del Certificado */
    vsm_numero := 0;

    IF vch_x_sucursal = 'SI' THEN
        OPEN cr_detalle_suc;
    ELSE
        OPEN cr_detalle;
    END IF;

    LOOP
        IF vch_x_sucursal = 'SI' THEN
            FETCH cr_detalle_suc
            INTO cch_trabajador, cvc_nombre, cvc_cedula, cdt_ingreso, cdt_baja;
            EXIT WHEN cr_detalle_suc%NOTFOUND;
        ELSE
            FETCH cr_detalle 
            INTO cch_trabajador, cvc_nombre, cvc_cedula, cdt_ingreso, cdt_baja;
            EXIT WHEN cr_detalle%NOTFOUND;
        END IF;

        vdc_ingresos := 0;
        vdc_no_gravados := 0;
        vdc_cesantias := 0;
        vdc_retencion := 0;
        vdc_valor_ng := 0;
        vdc_valor_gr := 0;
        vdc_valor_pn := 0;
        vdc_retencion := 0;
        vdc_valor_AFP := 0;
        vdc_valor_EPS := 0;
        vdc_valor_VOL := 0;
        vch_agr_rep := 0;

        IF To_Number(To_Char(cdt_ingreso, 'yyyy')) = vsm_anio THEN
            vdt_desde := cdt_ingreso;
        ELSE
            vdt_desde := To_Date(To_Char(vsm_anio) || '0101','yyyymmdd');
        END IF;

        IF To_Number(To_Char(cdt_baja, 'yyyy')) = vsm_anio THEN
            vdt_hasta := cdt_baja;
        ELSE
            vdt_hasta := To_Date(To_Char(vsm_anio) || '1231', 'yyyymmdd');
        END IF;

        /* Ingresos Laborales */
        BEGIN
            SELECT 
            acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +
            acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +
            acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12
            INTO
            vdc_ingresos
            FROM
            acumulados_mensuales
            WHERE
            compania = vch_compania And
            trabajador = cch_trabajador And
            agr_conceptos = vch_agr_ing And
            concepto = 0 And
            anio = vsm_anio;
            EXCEPTION
            WHEN OTHERS THEN vdc_ingresos := 0;
        END;

        /* Gastos de Representacion */
        BEGIN
            SELECT 
            acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +
            acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +
            acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12
            INTO
            vdc_gastos_rep
            FROM
            acumulados_mensuales
            WHERE
            compania = vch_compania And
            trabajador = cch_trabajador And
            agr_conceptos = vch_agr_rep And
            concepto = 0 And
            anio = vsm_anio;
            EXCEPTION
            WHEN OTHERS THEN vdc_gastos_rep := 0;
        END;
        
        /* Conceptos No Gravados: Aportes a Pensiones y al Fondo de Solidaridad */
        BEGIN
            SELECT 
            acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +
            acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +
            acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12
            INTO
            vdc_no_gravados
            FROM
            acumulados_mensuales
            WHERE
            compania = vch_compania And
            trabajador = cch_trabajador And
            agr_conceptos = vch_agr_ngr And
            concepto = 0 And
            anio = vsm_anio;
            EXCEPTION
            WHEN OTHERS THEN vdc_no_gravados := 0;
        END;

        /* Conceptos de Cesantias */
        BEGIN
            SELECT 
            acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +
            acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +
            acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12
            INTO
            vdc_cesantias
            FROM
            acumulados_mensuales
            WHERE
            compania = vch_compania And
            trabajador = cch_trabajador And
            agr_conceptos = vch_agr_ces And
            concepto = 0 And
            anio = vsm_anio;
            EXCEPTION
            WHEN OTHERS THEN vdc_cesantias := 0;
        END;

        /* Conceptos de Pensiones */  
        BEGIN
            SELECT         
            acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +        
            acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +        
            acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12
            INTO
            vdc_valor_pn
            FROM        
            acumulados_mensuales        
            WHERE        
            compania = vch_compania And        
            trabajador = cch_trabajador And        
            agr_conceptos = vch_agr_pen And        
            concepto = 0 And        
            anio = vsm_anio;
            EXCEPTION
            WHEN OTHERS THEN vdc_valor_pn := 0;
        END;

        /* Conceptos de aprendices */  
        BEGIN
            SELECT         
            acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +        
            acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +        
            acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12        
            INTO
            vdc_valor_ap
            FROM        
            acumulados_mensuales        
            WHERE        
            compania = vch_compania And        
            trabajador = cch_trabajador And        
            agr_conceptos = vch_agr_apr And        
            concepto = 0 And        
            anio = vsm_anio;
            EXCEPTION
            WHEN OTHERS THEN vdc_valor_ap := 0;
        END;

        /* Valor de la Retención en la Fuente */
        IF vtn_tipo_param = 0  THEN 
        BEGIN
            SELECT 
            acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +
            acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +
            acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12
            INTO
            vdc_retencion
            FROM
            acumulados_mensuales
            WHERE
            compania = vch_compania And
            trabajador = cch_trabajador And
            concepto = vsm_cnc_rtf And
            anio = vsm_anio;
            EXCEPTION
            WHEN OTHERS THEN vdc_retencion := 0;
        END;
        ELSE
        BEGIN
            SELECT 
            acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +
            acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +
            acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12
            INTO
            vdc_retencion
            FROM
            acumulados_mensuales
            WHERE
            compania = vch_compania And
            trabajador = cch_trabajador And
            agr_conceptos = vch_agr_rtf And
            concepto = 0 And
            anio = vsm_anio;
            EXCEPTION
            WHEN OTHERS THEN vdc_retencion := 0;
        END;
        END IF;

        /*  Pensiones Obligatorias y Salud */  
        BEGIN
          SELECT            
            NVL(acum_imp_mes_01,0) + NVL(acum_imp_mes_02,0) + NVL(acum_imp_mes_03,0) + NVL(acum_imp_mes_04,0) + 
            NVL(acum_imp_mes_05,0) + NVL(acum_imp_mes_06,0) + NVL(acum_imp_mes_07,0) + NVL(acum_imp_mes_08,0) +  
            NVL(acum_imp_mes_09,0) + NVL(acum_imp_mes_10,0) + NVL(acum_imp_mes_11,0) + NVL(acum_imp_mes_12,0)
          INTO 
            vdc_valor_AFP
          FROM                  
            acumulados_mensuales                  
          WHERE                  
            compania = vch_compania And                  
            trabajador = cch_trabajador And                  
            agr_conceptos = vch_agr_AFP And                  
            concepto = 0 And                  
            anio = vsm_anio;       
          EXCEPTION
            WHEN OTHERS THEN vdc_valor_AFP := 0;
        END;

        BEGIN
          SELECT 
            NVL(acum_imp_mes_01,0) + NVL(acum_imp_mes_02,0) + NVL(acum_imp_mes_03,0) + NVL(acum_imp_mes_04,0) + 
            NVL(acum_imp_mes_05,0) + NVL(acum_imp_mes_06,0) + NVL(acum_imp_mes_07,0) + NVL(acum_imp_mes_08,0) +  
            NVL(acum_imp_mes_09,0) + NVL(acum_imp_mes_10,0) + NVL(acum_imp_mes_11,0) + NVL(acum_imp_mes_12,0)    
          INTO 
            vdc_valor_EPS
          FROM                  
            acumulados_mensuales                  
          WHERE                  
            compania = vch_compania And                  
            trabajador = cch_trabajador And                  
            agr_conceptos = NVL(vch_agr_EPS,'') And                  
            concepto = 0 And                  
            anio = vsm_anio;
          EXCEPTION
            WHEN OTHERS THEN vdc_valor_EPS := 0;
        END;
 
        --vdc_valor_AFP := NVL(vdc_valor_AFP,0) + NVL(vdc_valor_EPS,0);
        --DCRP, Aqui en Porvenir, manejan pension y salud en una sola agrupacion
        vdc_valor_AFP := NVL(vdc_valor_AFP,0);
  
        BEGIN
  	  SELECT 
            NVL(acum_imp_mes_01,0) + NVL(acum_imp_mes_02,0) + NVL(acum_imp_mes_03,0) + NVL(acum_imp_mes_04,0) + 
            NVL(acum_imp_mes_05,0) + NVL(acum_imp_mes_06,0) + NVL(acum_imp_mes_07,0) + NVL(acum_imp_mes_08,0) +  
            NVL(acum_imp_mes_09,0) + NVL(acum_imp_mes_10,0) + NVL(acum_imp_mes_11,0) + NVL(acum_imp_mes_12,0)    
          INTO 
            vdc_valor_VOL
          FROM                  
            acumulados_mensuales                  
          WHERE                  
            compania = vch_compania And                  
            trabajador = cch_trabajador And                  
            agr_conceptos = vch_agr_VOL And                  
            concepto = 0 And                  
            anio = vsm_anio;
          EXCEPTION
            WHEN OTHERS THEN vdc_valor_VOL := 0;
        END;

        /* Hallar el 30% de los ingresos */
        vdc_tope_aporte := Nvl(vdc_ingresos, 0) * 0.3;

        /* Permitir excensión hasta el 30% de los ingresos  */
        IF vdc_no_gravados > vdc_tope_aporte THEN
            vdc_no_gravados := vdc_tope_aporte;
        END IF;

        /* Calcular el 75% de gravados */
--        vdc_valor_gr := (vdc_ingresos - vdc_no_gravados) * 0.75;
        vdc_valor_gr := vdc_ingresos;
        vdc_valor_ng := (vdc_ingresos - vdc_no_gravados) * 0.25;


        /* hallando la diferencia en dias entre desde y hasta */
        vsm_dif_dias := (to_number(to_char(vdt_hasta, 'yyyy')) * 360) + (to_number(to_char(vdt_hasta, 'mm')) * 30);
        IF to_number(to_char(vdt_hasta, 'dd')) = 31 THEN
            vsm_dif_dias := vsm_dif_dias + 30;
        ELSE
            IF to_number(to_char(vdt_hasta, 'dd')) = 28 THEN
                IF to_number(to_char(vdt_hasta, 'mm')) = 2 THEN
                    vsm_dif_dias := vsm_dif_dias + 30;
                ELSE
                    vsm_dif_dias := vsm_dif_dias + to_number(to_char(vdt_hasta, 'dd'));
                END IF;
            ELSE
                vsm_dif_dias := vsm_dif_dias + to_number(to_char(vdt_hasta, 'dd'));
            END IF;
        END IF;

        vsm_dif_dias := vsm_dif_dias - (to_number(to_char(vdt_desde, 'yyyy')) * 360) - (to_number(to_char(vdt_desde, 'mm')) * 30);

        IF to_number(to_char(vdt_desde, 'dd')) = 31 THEN
            vsm_dif_dias := vsm_dif_dias - 30;
        ELSE
            IF to_number(to_char(vdt_desde, 'dd')) = 28 THEN
                IF to_number(to_char(vdt_desde, 'mm')) = 2 THEN
                    vsm_dif_dias := vsm_dif_dias - 30;
                ELSE
                    vsm_dif_dias := vsm_dif_dias - to_number(to_char(vdt_desde, 'dd'));
                END IF;
            ELSE
                vsm_dif_dias := vsm_dif_dias - to_number(to_char(vdt_desde, 'dd'));
            END IF;
        END IF;

        vsm_dif_dias := vsm_dif_dias + 1;

        IF vdc_valor_ng > vdc_tope_exc * vsm_dif_dias / 30 THEN
--            vdc_valor_gr := vdc_valor_gr + (vdc_valor_ng - (vdc_tope_exc * vsm_dif_dias / 30));
            vdc_valor_ng := vdc_tope_exc * vsm_dif_dias / 30;
        END IF;

        vdc_valor_ng := vdc_valor_ng + vdc_cesantias;

        IF Nvl(vdc_ingresos,0) > 0 THEN

            vsm_numero := vsm_numero + 1;

            INSERT INTO fco_cir_det 
            (id_rep, usuariow, cod_rep, compania, sucursal, anio, numero, 
            trabajador, nombre, cedula, 
            fecha_desde, fecha_hasta, campo_ng, campo_gr, secuencia,
            valor_retencion)
            VALUES 
            (psi_id_rep, pvc_usuario, pti_cod_rep, vch_compania, vch_sucursal, vsm_anio, vsm_numero, 
            cch_trabajador, cvc_nombre, cvc_cedula,
            vdt_desde, vdt_hasta, 0, vdc_valor_gr, 1,
            nvl(vdc_retencion,0));

            INSERT INTO fco_cir_det 
            (id_rep, usuariow, cod_rep, compania, sucursal, anio, numero, trabajador,
            campo_ng, campo_gr, secuencia, valor_retencion) 
            VALUES 
            (psi_id_rep, pvc_usuario, pti_cod_rep, vch_compania, vch_sucursal, vsm_anio, vsm_numero, cch_trabajador,
            vdc_cesantias, 0, 2,0);

            /* Gastos de Representacion*/ --DCRP
            vdc_gastos_rep_ng := NVL(vdc_gastos_rep,0) * 0.25;
            vdc_gastos_rep_gr := NVL(vdc_gastos_rep,0) - NVL(vdc_gastos_rep_ng,0);
            IF vdc_gastos_rep > 0 THEN
                INSERT INTO fco_cir_det 
                (id_rep, usuariow, cod_rep, compania, sucursal, anio, numero, trabajador,
                campo_ng, campo_gr, secuencia, valor_retencion) 
                VALUES 
                (psi_id_rep, pvc_usuario, pti_cod_rep, vch_compania, vch_sucursal, vsm_anio, vsm_numero, cch_trabajador,
                vdc_gastos_rep_ng, vdc_gastos_rep_gr, 3, 0);            
            END IF;
                        
            /* Salud, solidaridad y pensiones obligatorias */  
            INSERT INTO fco_cir_det                   
                (id_rep, usuariow, cod_rep, compania, sucursal, anio, numero, trabajador,
                 campo_ng, campo_gr, secuencia,valor_retencion)                  
            VALUES                   
                (psi_id_rep, pvc_usuario, pti_cod_rep, vch_compania, vch_sucursal, vsm_anio, vsm_numero, cch_trabajador,
                 nvl(vdc_valor_AFP,0), 0, 6,0);                  
        
            /* Aportes Voluntarios y AFC */  
            INSERT INTO fco_cir_det                   
                (id_rep, usuariow, cod_rep, compania, sucursal, anio, numero, trabajador,
                 campo_ng, campo_gr, secuencia,valor_retencion)                  
            VALUES                   
                (psi_id_rep, pvc_usuario, pti_cod_rep, vch_compania, vch_sucursal, vsm_anio, vsm_numero, cch_trabajador,
                 nvl(vdc_valor_VOL,0), 0, 7,0);                  
        
            /* Rentas Exentas (25% y Cesantias) */  
            INSERT INTO fco_cir_det                   
                (id_rep, usuariow, cod_rep, compania, sucursal, anio, numero, trabajador,
                 campo_ng, campo_gr, secuencia,valor_retencion)                  
            VALUES                   
                (psi_id_rep, pvc_usuario, pti_cod_rep, vch_compania, vch_sucursal, vsm_anio, vsm_numero, cch_trabajador,
                 nvl(vdc_valor_ng,0), 0, 8,0);
                 
            --DCRP, Aqui en porvenir, discriminan el valor del pago de Salud, se guarda en la sec 9
            IF vdc_valor_EPS > 0 THEN
                INSERT INTO fco_cir_det                   
                    (id_rep, usuariow, cod_rep, compania, sucursal, anio, numero, trabajador,
                     campo_ng, campo_gr, secuencia,valor_retencion)                  
                VALUES                   
                    (psi_id_rep, pvc_usuario, pti_cod_rep, vch_compania, vch_sucursal, vsm_anio, vsm_numero, cch_trabajador,
                     nvl(vdc_valor_EPS,0), 0, 9,0);                  
            END IF;
        END IF;

        IF vdc_valor_pn > 0 THEN
            vsm_numero := vsm_numero + 1;

            INSERT INTO fco_cir_det         
            (id_rep, usuariow, cod_rep, compania, sucursal, anio, numero, 
            trabajador, nombre, cedula,         
            fecha_desde, fecha_hasta, campo_ng, campo_gr, secuencia,        
            valor_retencion)        
            VALUES         
            (psi_id_rep, pvc_usuario, pti_cod_rep, vch_compania, vch_sucursal, vsm_anio, vsm_numero,
            cch_trabajador, cvc_nombre, cvc_cedula,        
            vdt_desde, vdt_hasta, vdc_valor_pn, 0, 4, 0);
        END IF;  

        IF vdc_valor_ap > 0 THEN
            vsm_numero := vsm_numero + 1;

            INSERT INTO fco_cir_det         
            (id_rep, usuariow, cod_rep, compania, sucursal, anio, numero, 
            trabajador, nombre, cedula,         
            fecha_desde, fecha_hasta, campo_ng, campo_gr, secuencia,        
            valor_retencion)        
            VALUES         
            (psi_id_rep, pvc_usuario, pti_cod_rep, vch_compania, vch_sucursal, vsm_anio, vsm_numero, 
            cch_trabajador, cvc_nombre, cvc_cedula,        
            vdt_desde, vdt_hasta, vdc_valor_ap, 0, 5, 0);
        END IF;
    END LOOP;
        
    IF vch_x_sucursal = 'SI' THEN
        CLOSE cr_detalle_suc;
    ELSE
        CLOSE cr_detalle;
    END IF;

   -- NO PROCESAR LOS TRABAJADORES FUERA DEL RANGO DE DATOS DE LA AGR. 4
  IF NVL(TRIM(pch_dato_dde),'0') <> '0' THEN
    DELETE fco_cir_det
    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_cir_det
    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_cir_det 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_cir_det 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_cir_det 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_hasta) 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_hasta)
                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_cir_det 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_cir_det 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_cir_det 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_cir_det 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_hasta) 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_hasta)
                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_cir_det 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_cir_det 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_cir_det 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_cir_det 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_hasta) 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_hasta)
                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_cir_det 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

  --se ejecuta el sp que Genera archivos planos
  sp_fco_rep_cir_mm (psi_id_rep, pvc_usuario, pti_cod_rep);
    
  COMMIT;

  EXCEPTION
  WHEN OTHERS THEN
       RAISE_APPLICATION_ERROR(-20002, SQLCODE||SQLERRM(SQLCODE));
       RETURN;                        
END;
/
CREATE PUBLIC SYNONYM sp_fco_rep_cir FOR ADAM.sp_fco_rep_cir;
GRANT EXECUTE ON sp_fco_rep_cir TO adama;
CREATE OR REPLACE PROCEDURE sp_fco_del_rep_cir
(
  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 ingresos y retenciones                        ***/
/*** 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_cir_cab
     WHERE id_rep = psi_id_rep
     AND usuariow = pvc_usuario
     AND cod_rep = pti_cod_rep;

     DELETE fco_cir_det
     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_cir FOR ADAM.sp_fco_del_rep_cir;
GRANT EXECUTE ON sp_fco_del_rep_cir TO adama;
