IF OBJECT_ID('dbo.fntb_fco_dian_1001') IS NOT NULL
    DROP FUNCTION dbo.fntb_fco_dian_1001
GO
CREATE FUNCTION dbo.fntb_fco_dian_1001
(
    @pch_compania char(4),
    @psi_anio smallint
)
-- JCL FCO 25-ABR-07
-- FORMATO 1001 PARA LA DIAN
RETURNS TABLE
AS
RETURN
(
/* PARTE 1. SALARIOS */
/* Cuantías Mayores */
SELECT 
    0 As secuencia, 
    registros =  
    RTrim(concepto) + ',' + tdoc + ',' + cedula + ',,' + -- DV
    ape1 + ',' + ape2 + ',' + nom1 + ',' + nom2 + ',,' + -- Razón Social
    dire + ',"' + dpto + '","' + mpio + '",' + RTrim(Floor(valor))
FROM vw_fco_dian_x_ced a
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5001
  AND a.valor >=
  (Select Convert(decimal(19,6), x.dato) From vw_fdt_parametros x
   Where x.pais = 'COL' And x.compania = a.compania And x.parametro = 'otros' And x.secuencia = 100)

UNION

/* Cuantías Menores */
SELECT 
    1 As secuencia, 
    registros =  
    RTrim(a.concepto) + ',,222222222,,,,,,CUANTÍAS MENORES,,,,' + RTrim(Sum(a.valor))
FROM vw_fco_dian_x_ced a
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5001
  AND a.valor <
  (Select Convert(decimal(19,6), x.dato) From vw_fdt_parametros x
   Where x.pais = 'COL' And x.compania = a.compania And x.parametro = 'otros' And x.secuencia = 100)
GROUP BY a.concepto

UNION

/* PARTE 2. CAJAS DE COMPENSACIÓN */
SELECT
    2 As secuencia,
    registros =
    RTrim(a.concepto) + ',NIT,' +
    IsNull(b.registro_fiscal, '') + ',' +
    IsNull(b.digito_verificacion, '') + ',' +
    ',,,,' +
    IsNull(b.descripcion, '') + ',' +
    IsNull(b.direccion, '') + ',' + 
    '"' + IsNull(b.departamento, '') + '",' + 
    '"' + IsNull(b.municipio, '') + '",' + 
    RTrim(Round(Sum(a.valor), 0))
FROM fco_dian a
INNER JOIN vw_fco_administradoras b ON a.compania = b.compania AND a.entidad = b.codigo
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5010
  AND a.subconcepto = 'CCF'
  AND b.tipo = 'CCF'
GROUP BY a.concepto, b.registro_fiscal, b.digito_verificacion, b.descripcion, b.direccion, b.departamento, b.municipio

UNION

/* PARTE 3. SENA */
SELECT
    3 As secuencia,
    registros =
    RTrim(a.concepto) + ',NIT,' +
    IsNull(Left(b.nit_SENA, 9), '899999034') + ',' +
    IsNull(Right(b.nit_SENA, 1), '1') + ',' +
    ',,,,' +
    IsNull(b.nombre_SENA, 'SENA') + ',' +
    IsNull(b.dir_SENA, 'Calle 57 No 8-69') + ',' + 
    '"11","001",' +
    RTrim(Round(a.valor, 0))
FROM fco_dian a
INNER JOIN vw_fco_cias_datos_adi b ON a.compania = b.compania
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5010
  AND a.subconcepto = 'SENA'

UNION

/* PARTE 4. ICBF */
SELECT
    4 As secuencia,
    registros =
    RTrim(a.concepto) + ',NIT,' +
    IsNull(Left(b.nit_ICBF, 9), '899999239') + ',' +
    IsNull(Right(b.nit_ICBF, 1), '2') + ',' +
    ',,,,' +
    IsNull(b.nombre_ICBF, 'INSTITUTO COLOMBIANO DE BIENESTAR FAMILIAR') + ',' +
    IsNull(b.dir_ICBF, 'Av 68 64-01 piso 3') + ',' + 
    '"11","001",' +
    RTrim(Round(a.valor, 0))
FROM fco_dian a
INNER JOIN vw_fco_cias_datos_adi b ON a.compania = b.compania
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5010
  AND a.subconcepto = 'ICBF'

UNION

/* PARTE 5. ENTIDADES PROMOTORAS DE SALUD */
SELECT
    5 As secuencia,
    registros =
    RTrim(a.concepto) + ',NIT,' +
    IsNull(b.registro_fiscal, '') + ',' +
    IsNull(b.digito_verificacion, '') + ',' +
    ',,,,' +
    IsNull(b.descripcion, '') + ',' +
    IsNull(b.direccion, '') + ',' + 
    '"' + IsNull(b.departamento, '') + '",' + 
    '"' + IsNull(b.municipio, '') + '",' + 
    RTrim(Round(Sum(a.valor), 0))
FROM fco_dian a
INNER JOIN vw_fco_administradoras b ON a.compania = b.compania AND a.entidad = b.codigo
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5011
  AND a.subconcepto = 'EPS'
  AND b.tipo = 'EPS'
GROUP BY a.concepto, b.registro_fiscal, b.digito_verificacion, b.descripcion, b.direccion, b.departamento, b.municipio

UNION

/* PARTE 6. ADMINISTRADORA DE RIESGOS PROFESIONALES */
SELECT
    6 As secuencia,
    registros =
    RTrim(a.concepto) + ',NIT,' +
    IsNull(Left(b.nit_ARP, 9), '') + ',' +
    IsNull(Right(b.nit_ARP, 1), '') + ',' +
    ',,,,' +
    IsNull(b.nombre_ARP, '') + ',' +
    IsNull(b.dir_ARP, '') + ',' + 
    '"' + IsNull(b.cod_dpto, '') + '",' + 
    '"' + IsNull(b.cod_ciudad, '') + '",' +
    RTrim(Round(a.valor, 0))
FROM fco_dian a
INNER JOIN vw_fco_cias_datos_adi b ON a.compania = b.compania
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5011
  AND a.subconcepto = 'ARP'

UNION

/* PARTE 7. ADMINISTRADORAS DE FONDOS DE PENSIONES */
SELECT
    7 As secuencia,
    registros =
    RTrim(a.concepto) + ',NIT,' +
    IsNull(b.registro_fiscal, '') + ',' +
    IsNull(b.digito_verificacion, '') + ',' +
    ',,,,' +
    IsNull(b.descripcion, '') + ',' +
    IsNull(b.direccion, '') + ',' + 
    '"' + IsNull(b.departamento, '') + '",' + 
    '"' + IsNull(b.municipio, '') + '",' + 
    RTrim(Round(Sum(a.valor), 0))
FROM fco_dian a
INNER JOIN vw_fco_administradoras b ON a.compania = b.compania AND a.entidad = b.codigo
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5012
  AND a.subconcepto = 'AFP'
  AND b.tipo = 'AFP'
GROUP BY a.concepto, b.registro_fiscal, b.digito_verificacion, b.descripcion, b.direccion, b.departamento, b.municipio

)
GO
GRANT SELECT ON dbo.fntb_fco_dian_1001 TO PUBLIC
GO
