If Object_Id('dbo.sp_fco_gen_bco_colombia') Is Not Null
   Drop Procedure dbo.sp_fco_gen_bco_colombia
Go
Create Procedure dbo.sp_fco_gen_bco_colombia
(
    @psi_id_rep smallint,
    @pvc_usuario varchar(30),
    @pti_banco tinyint,
    @pdt_fecha smalldatetime = Null,
    @pvc_responsable varchar(20) = '',
    @pti_filtra_cta tinyint = 0  --0: no filtra, 1: si filtra 
)
As
/********************************************************************/
/*** Módulo Fiscal de Colombia - Medios Magneticos para Bancos    ***/
/*** Formato: Transmision por el Banco de Colombia                ***/
/*** Autor: Diana Cecilia Rios Peinado                            ***/
/*** Fecha: Mayo 21/2005                                          ***/
/********************************************************************/
--OCT-01-05: DCR, Pasada a SQL de Oracle
--NOV-24-05: DCR, Este sp puede servir para Bancolombia y Conavi, solo que en la configuracion de bancos 
--a la institucion de deposito Bancolombia se debe colocar 005600078 y para Convai 005701100
--y si se desea utilizar como ACH los codigos serian los siguientes:
--  '005600065' -- SANTANDER              
--  '005895142' -- DAVIVIENDA              
--  '005701304' -- GRANAHORRAR              
--  '005600010' -- BOGOTA              
--  '005600023' -- POPULAR              
--  '005600104' -- TSB BANK - ANGLO              
--  '005600078' -- BANCOLOMBIA              
--  '005600094' -- CITIBANK              
--  '005600052' -- BANCAFE              
--  '005600230' -- OCCIDENTE              
--  '005600829' -- CAJA SOCIAL              
--  '005600133' -- GANADERO              
--  '005600191' -- COLPATRIA              
--  '006013677' -- AV VILLAS              
--  '005701100' -- CONAVI              
--  '005701809' -- COLMENA              
--  '000001036' -- MEGABANCO              
--  '005600340' -- SUPERIOR              
--  '999999999' -- COOMEVA              
-- JCL FEB-12-07 Ajuste para permitir filtro de trabajadores
Declare
    @vdt_fecha_hoy smalldatetime,
    @vti_sit tinyint,
    @vvc_tablaTN varchar(20),
    @vvc_sql varchar(2000),
    @vsi_cnc1 smallint,
    @vsi_cnc2 smallint,
    @vsi_cnc3 smallint,
    @vsi_cnc4 smallint,
    @vsi_cnc5 smallint,
    @vch_simb_mon char(5),
    @vti_tipo_cta tinyint,
    @vvc_cuenta_bco varchar(30),
    @vvc_oficina_cta varchar(15),
    @vvc_registro_fiscal varchar(20),
    @vvc_mensaje varchar(80),
    @vdc_total decimal(19,6),
    @vsi_nro_trab integer,
    
--    @vch_agr_id char(10),
--    @vch_agr_cta char(10),
    
    @cch_compania char(4),
    @cch_clase_nom char(2),
    @cch_tipo_nom char(2),
    @csi_anio smallint,
    @cti_periodo tinyint,
    @csi_id_cal smallint     
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
    Set @vdt_fecha_hoy = getdate()
        
    Declare c_reg_procesa Cursor For
    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 = @psi_id_rep
    And c.usuariow = @pvc_usuario
    
    Open c_reg_procesa  
    Fetch Next From c_reg_procesa Into @cch_compania, @cch_clase_nom, @cch_tipo_nom, @csi_anio, @cti_periodo, @csi_id_cal
    
    While (@@Fetch_Status = 0)  
    Begin--B1
        Set @csi_id_cal = IsNull(@csi_id_cal,0)
        
        If IsNull(@csi_id_cal,0) = 0
        Begin
            Set @vvc_sql = @cch_tipo_nom + ' ' + RTrim(@csi_anio) + ' ' + RTrim(@cti_periodo)
            Raiserror ('Error de parametrizacion: No existe el periodo específicado (%s)', 16, 1, @vvc_sql)
            Goto SgteRegistro
        End
        
        --  Verificar el estado del periodo
        Select @vti_sit = sit_periodo From cal_proc_cias
        Where compania = @cch_compania And id_calendario = @csi_id_cal
        
        Set @vti_sit = IsNull(@vti_sit, 1)
        
        --  Asignar la tabla de acuerdo al estado del periodo
        If @vti_sit = 2
        Begin
            Set @vvc_tablaTN = 'trans_' + RTrim(@cch_compania) + '_' + RTrim(@csi_id_cal)
            If Object_Id(@vvc_tablaTN) Is Null
            Begin
                Raiserror ('Error de datos: No existe la tabla de respaldo [%s]', 16, 1, @vvc_tablaTN)
                Return
            End
        End
        Else
            Set @vvc_tablaTN = 'transacciones_ns'
        
        -- Trayendo al informacion del banco      
        Select 
            @vsi_cnc1 = b.concepto1, 
            @vsi_cnc2 = b.concepto2, 
            @vsi_cnc3 = b.concepto3, 
            @vsi_cnc4 = b.concepto4, 
            @vsi_cnc5 = b.concepto5,
            @vch_simb_mon = b.simbolo_moneda, 
            @vti_tipo_cta = b.tipo_cta, 
            @vvc_cuenta_bco = b.cuenta, 
            @vvc_oficina_cta = b.oficina_cta, 
            @vvc_registro_fiscal = b.registro_fiscal
        From fdt_bancos a, fdt_bancos_cias_datos b
        Where a.banco = b.banco And b.compania = @cch_compania And a.banco = @pti_banco
        
        --Insertando en la tabla temporal los trabajadores
        Set @vsi_cnc1 = IsNull(@vsi_cnc1, 0)
        Set @vsi_cnc2 = IsNull(@vsi_cnc2, 0)
        Set @vsi_cnc3 = IsNull(@vsi_cnc3, 0)
        Set @vsi_cnc4 = IsNull(@vsi_cnc4, 0)
        Set @vsi_cnc5 = IsNull(@vsi_cnc5, 0)
        Set @vch_simb_mon = IsNull(@vch_simb_mon,'')
        Set @vti_tipo_cta = IsNull(@vti_tipo_cta,1)
        Set @vvc_cuenta_bco = IsNull(@vvc_cuenta_bco,'') 
        Set @vvc_oficina_cta = IsNull(@vvc_oficina_cta,'') 
        Set @vvc_registro_fiscal = IsNull(@vvc_registro_fiscal, '')
        
        Set @vvc_sql = 'Insert Into fdt_bancos_tmp' +
        ' Select ' +  RTrim(@psi_id_rep) + ', ' +
            '''' + @pvc_usuario + ''', ' +
            'tns.compania, tns.clase_nomina, tns.id_calendario, ' +
            'tns.trabajador, SUM(tns.importe), 0, 0, ' +
            '''' + @vch_simb_mon + ''', 1, 0, Null, 0, Null' +
        ' 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 = ''' + @cch_compania + '''' +
            ' And tns.clase_nomina = ''' + @cch_clase_nom + '''' +
            ' And tns.id_calendario = ' + RTrim(@csi_id_cal) +
            ' And tns.concepto IN ( ' +
            RTrim(@vsi_cnc1) + ',' + RTrim(@vsi_cnc2) + ',' + RTrim(@vsi_cnc3) + ',' + RTrim(@vsi_cnc4) + ',' + RTrim(@vsi_cnc5) + ')' +
        ' GROUP BY tns.trabajador, tns.compania, tns.clase_nomina, tns.id_calendario'
        
        Execute (@vvc_sql)
        
        -- JCL FEB-12-07
        If Object_Id('dbo.fdt_bancos_trab') Is Not Null
        Begin
            If Exists 
            (Select 1 From fdt_bancos_trab  Where usuariow = System_User And banco = @pti_banco And procesar = 1)        
                Delete fdt_bancos_tmp
                Where compania = @cch_compania 
                And clase_nomina = @cch_clase_nom
                And id_calendario = @csi_id_cal
                And id_rep = @psi_id_rep
                And usuariow = @pvc_usuario
                And Not Exists
                (Select 1 From fdt_bancos_trab x
                 Where x.usuariow = System_User
                   And x.banco = @pti_banco
                   And fdt_bancos_tmp.compania = x.compania
                   And fdt_bancos_tmp.trabajador = x.trabajador
                   And x.procesar = 1)
        End
        -- End JCL
    
        -- Actualizamos los que tienen cuenta de deposito
        -- sin importar de que banco sea, se debe traer la cuenta del trabajador
        Update fdt_bancos_tmp
        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 = @csi_id_cal)
        From fdt_bancos_tmp c 
        Where compania = @cch_compania 
          And clase_nomina = @cch_clase_nom
          And id_calendario = @csi_id_cal
          And id_rep = @psi_id_rep
          And usuariow = @pvc_usuario
        
        Update fdt_bancos_tmp
        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 = @csi_id_cal)
        From fdt_bancos_tmp c
        Where compania = @cch_compania 
          And clase_nomina = @cch_clase_nom
          And id_calendario = @csi_id_cal
          And id_rep = @psi_id_rep
          And usuariow = @pvc_usuario
        
        --si por casualidad ins_dep queda en cero, buscar la info de rel_trab_ins_dep
        Update fdt_bancos_tmp
        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 = @csi_id_cal)
        From fdt_bancos_tmp c
        Where compania = @cch_compania 
          And clase_nomina = @cch_clase_nom
          And id_calendario = @csi_id_cal
          And id_rep = @psi_id_rep
          And usuariow = @pvc_usuario
          And LTrim(RTrim(c.ins_dep)) = '0'
        
        --si viene la opcion de filtrar unicamente los trabajadores que tengan cuenta en las ins dep parametrizadas
        If @pti_filtra_cta = 1
        Begin
            Delete From fdt_bancos_tmp
            Where compania = @cch_compania
              And clase_nomina = @cch_clase_nom
              And id_calendario = @csi_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 = fdt_bancos_tmp.compania
               And a.trabajador = fdt_bancos_tmp.trabajador
               And b.banco = @pti_banco
               And fdt_bancos_tmp.compania = @cch_compania                                
               And fdt_bancos_tmp.clase_nomina = @cch_clase_nom
               And fdt_bancos_tmp.id_calendario = @csi_id_cal)
        End
        
        --Actualizar el tipo de documento *** cambiar de acuerdo al cliente ***
-- para BanColombia es perdida de tiempo buscar el tipo de documento
        -- JCL 10-JUL-08 PARA QUE NO QUEDE AMARRADO EL TIPO DE CUENTA HAY QUE LLENAR LA TABLA [fdt_bancos_tipos_cta]
        -- si no se llena le queda a todo el mundo AHORROS (37) QUE ES LO NORMAL
        Update fdt_bancos_tmp
        Set tipo_cuenta = a.tipo_cta
        From fdt_bancos_tmp c
        Inner Join vw_fdt_tra_tipo_cta a On a.compania = c.compania And a.trabajador = c.trabajador
        Where c.compania = @cch_compania 
          And c.clase_nomina = @cch_clase_nom
          And c.id_calendario = @csi_id_cal
          And c.id_rep = @psi_id_rep
          And c.usuariow = @pvc_usuario
          And a.banco = @pti_banco
        
SgteRegistro:
        Fetch Next From c_reg_procesa Into @cch_compania, @cch_clase_nom, @cch_tipo_nom, @csi_anio, @cti_periodo, @csi_id_cal                    
    End--B1  
    
    Close c_reg_procesa
    Deallocate c_reg_procesa
    
    --Finalizado el Cursor, se debe agrupar por una sola cuenta por trabajador, los registros de los varios
    --tipos de nomina seleccionados
    Insert Into fdt_bancos_tmp(id_rep, usuariow, trabajador, importe, ins_dep, cuenta_dep, simbolo_mon, tipo, tipo_cuenta, tipo_doc)
    Select tn.id_rep, tn.usuariow, tn.trabajador, SUM(tn.importe), tn.ins_dep, tn.cuenta_dep, tn.simbolo_mon, 2, tn.tipo_cuenta, tn.tipo_doc
    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_cuenta, tn.tipo_doc
    
    --Ahora el detalle
    /* FORMATO SAP */
    Insert Into fdt_bancos_mm(id_rep, usuariow, banco, sec_archivo, secuencia, registros)
    Select @psi_id_rep, @pvc_usuario, @pti_banco, 1, 2, 
        '6' +                                                                         --Tipo de registro
        Right('000000000000000' + Left(LTrim(IsNull(t.clave_unica,'0')), 15), 15) +   --Nit Beneficiario
        Convert(char(18), Upper(Left(dbo.fn_nombre_plano(Lower(t.nombre)), 18))) +    --Nombre del beneficiario
        Right('000000000' + LTrim(RTrim(IsNull(tns.ins_dep,'0'))), 9) +               --Banco cuenta del beneficiario
        Right('00000000000000000' + Left(IsNull(tns.cuenta_dep, '0'), 17), 17) +      --Numero de cuenta del beneficiario
        'S' +                                                                         --Indicador Lugar de pago
        Left(IsNull(tns.tipo_cuenta, '37'), 2) +                                      --Tipo de transacción
        Right('0000000000' + RTrim(Floor(tns.importe)), 10) +                         --Valor transacción
        'NOMINA OR' +                                                                 --Concepto
        Space(12) +                                                                   --Referencia
        ' '                                                                           --Filler
    From trabajadores t
    Inner Join fdt_bancos_tmp tns On t.trabajador = tns.trabajador
    Where tns.id_rep = @psi_id_rep
      And tns.usuariow = @pvc_usuario
      And tns.tipo = 2
    
    ------------------------------------
    --  Generar Estadisticas del Proceso
    ------------------------------------
    Select @vdc_total = IsNull(SUM(importe), 0), @vsi_nro_trab = COUNT(trabajador)
    From fdt_bancos_tmp tns 
    Where tns.id_rep = @psi_id_rep And tns.usuariow = @pvc_usuario And tipo = 2
    
    --armamos la estructura del archivo plano. La cabeza
    --Aqui queda el valor de cuenta, tipo cuenta de la ultima cia recorrida
    /* FORMATO SAP */
    Insert Into fdt_bancos_mm(id_rep, usuariow, banco, sec_archivo, secuencia, registros)
    Select
        @psi_id_rep, @pvc_usuario, @pti_banco, 1, 1,
        '1' +                                                                                            --Tipo de registro
        Right('0000000000' + SubString(Replace(Replace(registro_fiscal, '-', ''), '.', ''), 1, 9), 10) + --Nit del pagador
        Convert(char(16), Left(IsNull(nombre_cia, ''), 16)) +                                            --Nombre del pagador
        '225' +                                                                                          --Clase de transacción
        'PGO NOMINA' +                                                                                   --Descripcion
        Convert(char(6), @pdt_fecha, 12) +                                                               --Fecha de transmision
        'A' +                                                                                            --Secuencia
        Convert(char(6), @pdt_fecha, 12) +                                                               --Fecha de aplicacion
        Right('000000' + RTrim(@vsi_nro_trab), 6) +                                                      --Numero de registros
        '000000000000' +                                                                                 --Sumatoria de débitos        
        Right('000000000000' + RTrim(Floor(@vdc_total)), 12) +                                           --Sumatoria de créditos
        Right('00000000000' + Left(@vvc_cuenta_bco, 11), 11) +                                           --Cuenta cliente a debitar
        Case @vti_tipo_cta When 2 Then 'S' Else 'D' End
    From companias
    Where compania In (Select Distinct compania From ri_procesar Where id_rep = @psi_id_rep And usuariow = @pvc_usuario)      

    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)
    
    --si se debe generar reporte 
    If Exists (Select 1 From fdt_rutas Where operacion = 'FCO1070' And codigo = @pti_banco And tipo = 1)
    Begin --rep
        Delete fdt_bancos_rep Where id_rep = @psi_id_rep And usuariow = @pvc_usuario And banco = @pti_banco
        
        Insert Into fdt_bancos_rep (id_rep, usuariow, banco, fecha, responsable, trabajador, importe, cuenta_dep, simbolo_moneda)
        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
    End--rep    
End
Go
Grant Execute On dbo.sp_fco_gen_bco_colombia To Public
Go
