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;
  
