IF OBJECT_ID('dbo.fntb_fco_dian_1008') IS NOT NULL
    DROP FUNCTION dbo.fntb_fco_dian_1008
GO
CREATE FUNCTION dbo.fntb_fco_dian_1008
(
    @pch_compania char(4),
    @psi_anio smallint
)
-- JCL FCO 25-ABR-07
-- FORMATO 1008 PARA LA DIAN
RETURNS TABLE
AS
RETURN
(
/* 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 = 1008
  AND a.concepto = 1317
  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 = 105)

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 = 1008
  AND a.concepto = 1317
  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 = 105)
GROUP BY a.concepto
)
GO
GRANT SELECT ON dbo.fntb_fco_dian_1008 TO PUBLIC
GO
