CREATE OR REPLACE PROCEDURE sp_fco_gen_bco_ACH_occi
(
  psi_id_rep 	number,
  pvc_usuario	varchar2,
  pti_banco number,
  pdt_fecha date DEFAULT NULL,
  pvc_responsable  varchar2 DEFAULT '',
  pti_filtra_cta number DEFAULT 0  --0: no filtra, 1: si filtra 
)
IS
/********************************************************************/
/*** Módulo Fiscal de Colombia - Medios Magneticos para Bancos    ***/
/*** Formato: Transmision por ACH del Banco de Occidente          ***/
/*** Autor:	Diana Cecilia Rios Peinado                            ***/
/*** Fecha:	Julio 14/2005                                         ***/
/********************************************************************/
vvc_tablaTN   varchar2(20);
vdummy        varchar2(30) := NULL;
vbl_existe    boolean;
vnu_existe    number(1);
vvc_sql       varchar2(2000);
vin_id_cursor integer;
vin_rows      integer;
vti_sit       cal_proc_cias.sit_periodo%TYPE;
sComilla      char(1);
vdt_fecha_hoy	date;

cch_compania  char(4);
cch_clase_nom char(2);
cch_tipo_nom  char(2);
cnu_anio      number(5);
cnu_periodo   number(3);
cnu_id_cal    number(5);

vnu_concepto1 number(5);
vnu_concepto2 number(5);
vnu_concepto3 number(5);
vnu_concepto4 number(5);
vnu_concepto5 number(5);
vch_simb_mon  char(5);
vti_tipo_cta  number(1);
vvc_cuenta_bco  varchar2(30);
vvc_oficina_cta varchar2(15);
vvc_registro_fiscal varchar2(20);
vnu_reporte   number(1);

vdc_total     number(19,6);
vsi_nro_trab  integer;

vch_agr_id    char(10);
vch_agr_cta   char(10);

vvc_comprobante varchar2(10);
vvc_nombre_cia varchar2(60);

no_id_calendario	  EXCEPTION;
no_tabla		        EXCEPTION;
ins_tabla           EXCEPTION;
upd_tabla           EXCEPTION;
del_tabla           EXCEPTION;
ins_tabla_mm        EXCEPTION;
ins_tabla_rep       EXCEPTION;

-------------------------------------------------
--    Cursor para Verificar el estado del periodo
-------------------------------------------------
CURSOR c_ti_sit(
 ach_compania	companias.Compania%TYPE,
 asi_id_cal	calendario_procesos.id_calendario%TYPE
) IS
  SELECT
    sit_periodo
  FROM
    cal_proc_cias
  WHERE
    compania = ach_compania And
    id_calendario = asi_id_cal;
-------------------------------------------------
--    Cursor para Verificar existencia de tabla
-------------------------------------------------
CURSOR c_existe_tabla(
 atable_name	user_tables.table_name%TYPE
) IS
/*  SELECT
    'X'
  FROM
    USER_TABLES
  WHERE
    Table_Name = atable_name;*/    
  SELECT 'X'
  FROM 
      all_objects
  WHERE 
      object_name = UPPER(atable_name) And 
      object_type = 'TABLE';

-------------------------------------------------
--    Cursor para traer informacion de los bancos
-------------------------------------------------
CURSOR c_info_banco(
  ach_compania companias.compania%TYPE,
  anu_banco fdt_bancos.banco%TYPE
)IS
 SELECT b.concepto1, b.concepto2, b.concepto3, b.concepto4, b.concepto5,
 b.simbolo_moneda, b.tipo_cta, b.cuenta, b.oficina_cta, b.registro_fiscal
 FROM fdt_bancos a, fdt_bancos_cias_datos b
 WHERE a.banco = b.banco AND
 b.compania = ach_compania AND
 a.banco = anu_banco;
 
-------------------------------------------------
--    Cursor para traer los registros a procesar
-------------------------------------------------
CURSOR c_reg_procesa(
 asi_id_rep ri_procesar.id_rep%TYPE,
 avc_usuario ri_procesar.usuariow%TYPE
)IS
 SELECT DISTINCT c.compania, c.clase_nomina, c.tipo_nomina, c.anio, c.periodo, a.id_calendario
 FROM calendario_procesos a, cal_proc_cias b, ri_procesar c
 WHERE a.id_calendario = b.id_calendario
 AND a.tipo_nomina = c.tipo_nomina
 AND a.anio = c.anio
 AND a.periodo = c.periodo
 AND b.compania = c.compania
 AND c.id_rep = asi_id_rep
 AND c.usuariow = avc_usuario;
     
BEGIN
--borrando la informacion 
	DELETE fdt_bancos_mm
	WHERE id_rep = psi_id_rep AND
	usuariow = pvc_usuario AND
	banco = pti_banco;  

	DELETE fdt_bancos_tmp
	WHERE id_rep = psi_id_rep AND
	usuariow = pvc_usuario;  
  
--  Fecha Actual
	vdt_fecha_hoy := sysdate;
  sComilla := chr(39);

  --agrupación del tipo del documento, cambiar de acuerdo al cliente
  vch_agr_id := 'TDN';
  vch_agr_cta := 'CTA';  
  
-- Recorriendo el cursor de los registros a procesar.
  OPEN c_reg_procesa(psi_id_rep, pvc_usuario);  
	LOOP--L1  
  FETCH c_reg_procesa INTO cch_compania, cch_clase_nom, cch_tipo_nom, cnu_anio, cnu_periodo, cnu_id_cal;
  EXIT WHEN c_reg_procesa%NOTFOUND;
	BEGIN--B1
      --  Verificar el estado del periodo
      OPEN c_ti_sit(cch_compania, cnu_id_cal);
      FETCH c_ti_sit INTO vti_sit;
      CLOSE c_ti_sit;
      --  Asignar la tabla de acuerdo al estado del periodo
      IF vti_sit = 2 THEN
        vvc_tablaTN := UPPER('trans_'||RTRIM(cch_compania)||'_'||TO_CHAR(cnu_id_cal));
      ELSE
        vvc_tablaTN := UPPER('transacciones_ns');
      END IF;
      vvc_tablaTN := TRIM(vvc_tablaTN);
      DBMS_OUTPUT.PUT_LINE('7 - Verificar el estado del periodo');
      DBMS_OUTPUT.PUT_LINE('7A - tabla'||vvc_tablaTN);
      --  Verificar que la tabla existe
      OPEN c_existe_tabla(vvc_tablaTN);
      FETCH c_existe_tabla INTO vdummy;
      IF c_existe_tabla%NOTFOUND THEN
      	RAISE no_tabla;
      END IF;
      CLOSE c_existe_tabla;
      -- Trayendo al informacion del banco      
      OPEN c_info_banco(cch_compania, pti_banco);
      FETCH c_info_banco INTO vnu_concepto1, vnu_concepto2, vnu_concepto3, vnu_concepto4, vnu_concepto5, vch_simb_mon, vti_tipo_cta,
      vvc_cuenta_bco, vvc_oficina_cta, vvc_registro_fiscal;
      CLOSE c_info_banco;
      --Insertando en la tabla temporal los trabajadores
      vnu_concepto1 := NVL(vnu_concepto1,0);
      vnu_concepto2 := NVL(vnu_concepto2,0);
      vnu_concepto3 := NVL(vnu_concepto3,0);
      vnu_concepto4 := NVL(vnu_concepto4,0);
      vnu_concepto5 := NVL(vnu_concepto5,0);   
            
      vvc_sql := 'INSERT INTO fdt_bancos_tmp' ||
                 ' SELECT ' ||  psi_id_rep || ', ' ||
                  sComilla || pvc_usuario || sComilla || ', ' ||
                  'tns.compania, tns.clase_nomina, tns.id_calendario, ' ||
                  'tns.trabajador, SUM(tns.importe), 0, 0, ' ||
                  sComilla || vch_simb_mon || sComilla || ', 1, 0, 0, 0' ||
                  ' FROM ' || vvc_tablaTN || ' tns, trabajadores_grales tg' ||
                  ' WHERE tns.compania = tg.compania' ||
                  ' AND tns.trabajador = tg.trabajador' ||
                  ' AND tg.forma_pago = 3' ||
                  ' AND tns.importe > 0' ||
                  ' AND tns.sit_transaccion = 2' ||
                  ' AND tns.compania = ' || sComilla || cch_compania || sComilla ||
                  ' AND tns.clase_nomina = ' || sComilla || cch_clase_nom || sComilla ||
                  ' AND tns.id_calendario = ' || cnu_id_cal ||
                  ' AND tns.concepto IN ( ' || vnu_concepto1 || ',' || vnu_concepto2 || ',' || vnu_concepto3 || ',' ||
                  vnu_concepto4 || ',' || vnu_concepto5 || ')' ||
                  ' GROUP BY tns.trabajador, tns.compania, tns.clase_nomina, tns.id_calendario';                  
      BEGIN
          vin_id_cursor := dbms_sql.open_cursor;
          dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
          vin_rows := dbms_sql.execute(vin_id_cursor);
          dbms_sql.close_cursor(vin_id_cursor);
      EXCEPTION
          WHEN OTHERS THEN
              RAISE ins_tabla;
      END;

      DBMS_OUTPUT.PUT_LINE('8 - insersion en la tabla de la trans o transacciones_ns');                    
      --actualizamos los que tienen cuenta de deposito
      BEGIN    
--sin importar de que banco sea, se debe traer la cuenta del trabajador
        UPDATE fdt_bancos_tmp c
        SET cuenta_dep =
             (SELECT MAX(a.cuenta_deposito)
              FROM rel_trab_ins_dep a
              WHERE a.compania = c.compania
              AND a.trabajador = c.trabajador
              AND a.ins_dep_principal = 1 
              AND c.compania = cch_compania
              AND c.clase_nomina = cch_clase_nom
              AND c.id_calendario = cnu_id_cal)
         WHERE compania = cch_compania 
         AND clase_nomina = cch_clase_nom
         AND id_calendario = cnu_id_cal
         AND id_rep = psi_id_rep
         AND usuariow = pvc_usuario;         
      EXCEPTION
          WHEN OTHERS THEN
              RAISE upd_tabla;
      END;

DBMS_OUTPUT.PUT_LINE('9A - update a la tabla');

      BEGIN    
        UPDATE fdt_bancos_tmp c
        SET ins_dep = 
             (SELECT MAX(b.cod_banco) 
              FROM rel_trab_ins_dep a, fdt_bancos_ins_dep b 
              WHERE a.compania = c.compania
              AND a.trabajador = c.trabajador
              AND a.ins_dep_principal = 1
              AND a.institucion_deposito = b.ins_dep
              AND b.banco = pti_banco
              AND c.compania = cch_compania 
              AND c.clase_nomina = cch_clase_nom
              AND c.id_calendario = cnu_id_cal)
         WHERE compania = cch_compania 
         AND clase_nomina = cch_clase_nom
         AND id_calendario = cnu_id_cal
         AND id_rep = psi_id_rep
         AND usuariow = pvc_usuario;         
      EXCEPTION
          WHEN OTHERS THEN
              RAISE upd_tabla;
      END;

DBMS_OUTPUT.PUT_LINE('9B - update a la tabla');

--si por casualidad ins_dep queda en cero, buscar la info de rel_trab_ins_dep
      BEGIN    
        UPDATE fdt_bancos_tmp c
        SET ins_dep = 
             (SELECT MAX(a.institucion_deposito)
              FROM rel_trab_ins_dep a
              WHERE a.compania = c.compania
              AND a.trabajador = c.trabajador
              AND a.ins_dep_principal = 1 
              AND c.compania = cch_compania
              AND c.clase_nomina = cch_clase_nom
              AND c.id_calendario = cnu_id_cal)
         WHERE compania = cch_compania 
         AND clase_nomina = cch_clase_nom
         AND id_calendario = cnu_id_cal
         AND id_rep = psi_id_rep
         AND usuariow = pvc_usuario
         AND c.ins_dep = '0';         
      EXCEPTION
          WHEN OTHERS THEN
              RAISE upd_tabla;
      END;
      
      DBMS_OUTPUT.PUT_LINE('9 - update a la tabla');
      --si viene la opcion de filtrar unicamente los trabajadores que tengan cuenta en las ins dep parametrizadas
      IF pti_filtra_cta = 1 THEN--2
        BEGIN   
            DELETE fdt_bancos_tmp c
            WHERE compania = cch_compania
            AND clase_nomina = cch_clase_nom
            AND id_calendario = cnu_id_cal
            AND id_rep = psi_id_rep
            AND usuariow = pvc_usuario
            AND NOT EXISTS (SELECT 1 FROM rel_trab_ins_dep a, fdt_bancos_ins_dep b
                            WHERE a.institucion_deposito = b.ins_dep
                             AND a.compania = c.compania
                             AND a.trabajador = c.trabajador
                             AND b.banco = pti_banco
                             AND c.compania = cch_compania                                
                             AND c.clase_nomina = cch_clase_nom
                             AND c.id_calendario = cnu_id_cal);
        EXCEPTION
            WHEN OTHERS THEN
                RAISE del_tabla;
        END;

      DBMS_OUTPUT.PUT_LINE('10 - delete a la tabla');
      END IF;--2
      
      --Actualizar el tipo de documento *** cambiar de acuerdo al cliente ***
      BEGIN    
        UPDATE fdt_bancos_tmp c
        SET tipo_doc = 
             (SELECT DECODE (TRIM(a.dato), '001', 'C',
                   '002', 'E')
              FROM rel_trab_agr a
              WHERE a.compania = c.compania
              AND a.trabajador = c.trabajador
              AND a.agrupacion = vch_agr_id
              AND c.compania = cch_compania
              AND c.clase_nomina = cch_clase_nom
              AND c.id_calendario = cnu_id_cal)
         WHERE compania = cch_compania 
         AND clase_nomina = cch_clase_nom
         AND id_calendario = cnu_id_cal
         AND id_rep = psi_id_rep
         AND usuariow = pvc_usuario;
      EXCEPTION
          WHEN OTHERS THEN
              RAISE upd_tabla;
      END;

      BEGIN
        UPDATE fdt_bancos_tmp c
        SET tipo_cuenta = 
             (SELECT DECODE (TRIM(a.dato), '1', '1', '2')
              FROM rel_trab_agr a
              WHERE a.compania = c.compania
              AND a.trabajador = c.trabajador
              AND a.agrupacion = vch_agr_cta
              AND c.compania = cch_compania
              AND c.clase_nomina = cch_clase_nom
              AND c.id_calendario = cnu_id_cal)
         WHERE compania = cch_compania 
         AND clase_nomina = cch_clase_nom
         AND id_calendario = cnu_id_cal
         AND id_rep = psi_id_rep
         AND usuariow = pvc_usuario;
      EXCEPTION
          WHEN OTHERS THEN
              RAISE upd_tabla;
      END;

      --Jul14. Agregado para desarrollo de Luis Fernando.
/*
      DELETE ri_hist_pagos_bcos
      WHERE compania = cch_compania
      AND tipo_nomina = cch_tipo_nom
      AND anio = cnu_anio
      AND periodo = cnu_periodo
      AND institucion_deposito = '023';
      
      INSERT INTO ri_hist_pagos_bcos  
      SELECT cch_compania, cch_tipo_nom, cnu_anio, cnu_periodo,
      tns.trabajador, '023', tns.importe
      FROM fdt_bancos_tmp tns
      WHERE tns.id_rep = psi_id_rep
      AND tns.usuariow = pvc_usuario
      AND tipo = 1;
*/                  
  END;--B1  
  END LOOP;--L1
  CLOSE c_reg_procesa;  
  --Finalizado el cursor, se debe agrupar por una sola cuenta por trabajador, los registros de los varios
  --tipos de nomina seleccionados
  BEGIN
    INSERT INTO fdt_bancos_tmp(id_rep, usuariow, trabajador, importe, ins_dep, cuenta_dep, 
    simbolo_mon, tipo, tipo_doc, tipo_cuenta)
    SELECT tn.id_rep, tn.usuariow, tn.trabajador, SUM(tn.importe), tn.ins_dep, tn.cuenta_dep, tn.simbolo_mon, 2,
    tn.tipo_doc, tn.tipo_cuenta
    FROM fdt_bancos_tmp tn
    WHERE tn.id_rep = psi_id_rep
    AND tn.usuariow = pvc_usuario
    AND tn.tipo = 1
    GROUP BY tn.id_rep, tn.usuariow, tn.trabajador, tn.ins_dep, tn.cuenta_dep, tn.simbolo_mon, tn.tipo_doc, tn.tipo_cuenta;
  EXCEPTION
      WHEN OTHERS THEN
          RAISE ins_tabla;
  END;

  DBMS_OUTPUT.PUT_LINE('12 - Insert a la tabla ya agrupado');             
  
  --trayendo el nombre del cia:
  vvc_nombre_cia := '';
  BEGIN
     SELECT nombre_cia
     INTO vvc_nombre_cia
     FROM companias
     WHERE compania = cch_compania;
  EXCEPTION
     WHEN OTHERS THEN vvc_nombre_cia := '';  
  END;

  DBMS_OUTPUT.PUT_LINE('vvc_nombre_cia' || vvc_nombre_cia);             
  DBMS_OUTPUT.PUT_LINE('cch_compania' || cch_compania);               
      
  vvc_comprobante :=  '00' || TRIM(TO_CHAR(cnu_anio,'0000')) || TRIM(TO_CHAR(cnu_periodo,'00'));
  --Ahora el detalle
  BEGIN
    INSERT INTO fdt_bancos_mm(id_rep, usuariow, banco, sec_archivo, secuencia, registros)
    SELECT psi_id_rep, pvc_usuario, pti_banco, 1, Abs(rownum)+1, 
    '2' || --constante
    LTRIM(TO_CHAR(Abs(rownum),'0999')) || --consecutivo
    LPAD(NVL(vvc_cuenta_bco,'0'), 16, '0') || --cuenta del banco
    RPAD(REPLACE(LTRIM(RTRIM(t.nombre)), '/' , ' '), 30, ' ' ) || --nombre del trabajador
    LPAD(SUBSTR(LTRIM(t.clave_unica), 1, 11), 11, '0' ) || --cedula
    LTRIM(TO_CHAR(TO_NUMBER(tns.ins_dep),'0999')) || --codigo del banco
    TO_CHAR(pdt_fecha, 'YYYYMMDD') || --fecha digitada en el front
    DECODE(LTRIM(TO_CHAR(TO_NUMBER(tns.ins_dep),'099')),'023','2','3') || --forma de pago
    LPAD(REPLACE(LTRIM(TO_CHAR(tns.importe,'9999999999999.99')),'.',''),15,'0') || --valor pagado
    RPAD(SUBSTR(NVL(tns.cuenta_dep,'0'), 2, 16), 16, ' ' ) || --cuenta deposito
    TRIM(vvc_comprobante)||LTRIM(TO_CHAR(Abs(rownum),'0999')) || --comprobante
    SUBSTR(NVL(tns.cuenta_dep,'0'), 1, 1) || --tipo de cuenta
    RPAD(('Nomina ' || TRIM(vvc_nombre_cia)),80,' ')    
    FROM trabajadores t , fdt_bancos_tmp tns
    WHERE t.trabajador = tns.trabajador
    AND tns.id_rep = psi_id_rep
    AND tns.usuariow = pvc_usuario
    AND tns.tipo = 2;      
  EXCEPTION
      WHEN OTHERS THEN
          RAISE ins_tabla_mm;
  END;

  DBMS_OUTPUT.PUT_LINE('13 - insert del detalle');
------------------------------------
--  Generar Estadisticas del Proceso
------------------------------------
  BEGIN
    SELECT NVL(SUM(importe), 0) Total, COUNT(trabajador) Trabajadores
    INTO vdc_total, vsi_nro_trab
    FROM fdt_bancos_tmp tns 
    WHERE tns.id_rep = psi_id_rep
    AND tns.usuariow = pvc_usuario
    AND tipo = 2;
    EXCEPTION
        WHEN OTHERS THEN vdc_total := 0;
                         vsi_nro_trab := 0;               
  END;             
  DBMS_OUTPUT.PUT_LINE('13A - Select de totales');
  
  DELETE
      fdt_bancos_resultados
  WHERE
      id_rep = psi_id_rep AND
      usuariow = pvc_usuario AND
      banco = pti_banco;

  INSERT INTO fdt_bancos_resultados
    (id_rep, usuariow, banco, trabajadores, valor_total)
  VALUES (psi_id_rep, pvc_usuario, pti_banco, vsi_nro_trab, vdc_total);

  --insertamos la cabeza
    INSERT INTO fdt_bancos_mm(id_rep, usuariow, banco, sec_archivo, secuencia, registros)
    SELECT psi_id_rep, pvc_usuario, pti_banco, 1, 1, 
    '10000' || --constante
    TO_CHAR(pdt_fecha, 'YYYYMMDD') || --fecha digitada en el front
    LTRIM(TO_CHAR(Abs(vsi_nro_trab),'0999')) || --total registros del detalle
    LPAD(REPLACE(LTRIM(TO_CHAR(vdc_total,'9999999999999999.99')),'.',''),18,'0') || --valor total del detalle
    LPAD(NVL(vvc_cuenta_bco,'0'), 16, '0') || --cuenta del banco
    RPAD('0', 148, '0')
    FROM dual;
    
    --insertamos el pie
    INSERT INTO fdt_bancos_mm(id_rep, usuariow, banco, sec_archivo, secuencia, registros)
    SELECT psi_id_rep, pvc_usuario, pti_banco, 1, Abs(vsi_nro_trab)+2, 
    '39999' || --constante
    LTRIM(TO_CHAR(Abs(vsi_nro_trab),'0999')) || --total registros del detalle
    LPAD(REPLACE(LTRIM(TO_CHAR(vdc_total,'9999999999999999.99')),'.',''),18,'0') || --valor total del detalle
    RPAD('0', 172, '0')
    FROM dual;
      
  vnu_reporte := 0;
  BEGIN
     SELECT 1
     INTO vnu_reporte
     FROM fdt_rutas
     WHERE operacion = 'FCO1070'
     AND codigo = pti_banco
     AND tipo = 1;  
  EXCEPTION
      WHEN TOO_MANY_ROWS THEN vnu_reporte := 1;
      WHEN OTHERS THEN vnu_reporte := 0;
  END;
    
  IF vnu_reporte = 1  THEN --si se debe generar reporte --rep
		DELETE fdt_bancos_rep
		WHERE id_rep = psi_id_rep AND
		usuariow = pvc_usuario AND
		banco = pti_banco;
  
    BEGIN
      INSERT INTO fdt_bancos_rep
      SELECT tns.id_rep, tns.usuariow,
      pti_banco, pdt_fecha, pvc_responsable, 
      tns.trabajador, tns.importe, tns.cuenta_dep, tns.simbolo_mon
      FROM fdt_bancos_tmp tns
      WHERE tns.id_rep = psi_id_rep
      AND tns.usuariow = pvc_usuario
      AND tipo = 2;
      EXCEPTION
          WHEN OTHERS THEN
              RAISE ins_tabla_rep;
    END;                
    DBMS_OUTPUT.PUT_LINE('14 - insert a la tabla fdt_bancos_rep');  		
 	END IF;--rep

  COMMIT;         
  
  EXCEPTION
    WHEN no_id_calendario THEN
         RAISE_APPLICATION_ERROR(-20000, 'Error de datos: No existe el periodo especificado');
         RETURN;
    WHEN no_tabla THEN
         RAISE_APPLICATION_ERROR(-20001, 'Error de datos: No existe la tabla de respaldo');
         RETURN;
    WHEN ins_tabla THEN
         RAISE_APPLICATION_ERROR(-20001, 'Error al insertar en la tabla fdt_bancos_tmp');
         RETURN;         
    WHEN upd_tabla THEN
         RAISE_APPLICATION_ERROR(-20001, 'Error al actualizar en la tabla fdt_bancos_tmp');
         RETURN;
    WHEN del_tabla THEN
         RAISE_APPLICATION_ERROR(-20001, 'Error al eliminar en la tabla fdt_bancos_tmp');
         RETURN;                           
    WHEN ins_tabla_mm THEN
         RAISE_APPLICATION_ERROR(-20001, 'Error al insertar en la tabla: fdt_bancos_mm');
         RETURN;                  
    WHEN ins_tabla_rep THEN
         RAISE_APPLICATION_ERROR(-20001, 'Error al insertar en la tabla: fdt_bancos_rep');
         RETURN;                  
    WHEN OTHERS THEN
         RAISE_APPLICATION_ERROR(-20002, SQLCODE||SQLERRM(SQLCODE));
         RETURN;         
END;
/
CREATE PUBLIC SYNONYM sp_fco_gen_bco_ACH_occi FOR ADAM.sp_fco_gen_bco_ACH_occi;
GRANT EXECUTE ON sp_fco_gen_bco_ACH_occi TO adama;
