Elapsed Periods on iDempiere

-- View: v_anio_mes

-- DROP VIEW v_anio_mes;

CREATE OR REPLACE VIEW v_anio_mes AS
 WITH cte_periodo AS (
         SELECT date_part('year'::text, c.validfrom::date)::integer AS anio,
            date_part('month'::text, c.validfrom::date)::integer AS mes
           FROM c_conversion_rate c
             JOIN c_conversiontype cc ON cc.c_conversiontype_id = c.c_conversiontype_id
          WHERE c.ad_client_id = 1000000::numeric AND c.c_currency_id = 100::numeric AND c.c_currency_id_to = 209::numeric
          GROUP BY (date_part('year'::text, c.validfrom::date)::integer), (date_part('month'::text, c.validfrom::date)::integer)
          ORDER BY (date_part('year'::text, c.validfrom::date)::integer), (date_part('month'::text, c.validfrom::date)::integer)
        ), cte_secuencia AS (
         SELECT p.anio,
            p.mes,
            row_number() OVER (ORDER BY 1::integer, 2::integer) * 10 AS seq
           FROM cte_periodo p
        )
 SELECT s.seq::numeric(10,0) AS v_anio_mes_id,
    1000000::numeric(10,0) AS ad_client_id,
    1000000::numeric(10,0) AS ad_org_id,
    'Y'::character(1) AS isactive,
    now()::timestamp without time zone AS created,
    0::numeric(10,0) AS createdby,
    now()::timestamp without time zone AS updated,
    0::numeric(10,0) AS updatedby,
    s.anio,
    s.mes,
    ((fnt_nombre_mes(s.mes)::text || '-'::text) || s.anio::character varying::text)::character varying AS mes_anio
   FROM cte_secuencia s
  ORDER BY (s.seq::numeric(10,0)) DESC;

ALTER TABLE v_anio_mes
  OWNER TO adempiere;
GRANT ALL ON TABLE v_anio_mes TO adempiere;