rv_c_invoice_prodmonth

SELECT il.ad_client_id,

    il.ad_org_id,

    il.m_product_category_id,

    firstof((il.dateinvoiced)::timestamp with time zone, 'MM'::character varying) AS dateinvoiced,

    sum(il.linenetamt) AS linenetamt,

    sum(il.linelistamt) AS linelistamt,

    sum(il.linelimitamt) AS linelimitamt,

    sum(il.linediscountamt) AS linediscountamt,

        CASE

            WHEN (sum(il.linelistamt) = (0)::numeric) THEN (0)::numeric

            ELSE currencyround((((sum(il.linelistamt) - sum(il.linenetamt)) / sum(il.linelistamt)) * (100)::numeric), i.c_currency_id, 'N'::character varying)

        END AS linediscount,

    sum(il.lineoverlimitamt) AS lineoverlimitamt,

        CASE

            WHEN (sum(il.linenetamt) = (0)::numeric) THEN (0)::numeric

            ELSE ((100)::numeric - currencyround((((sum(il.linenetamt) - sum(il.lineoverlimitamt)) / sum(il.linenetamt)) * (100)::numeric), i.c_currency_id, 'N'::character varying))

        END AS lineoverlimit,

    sum(il.qtyinvoiced) AS qtyinvoiced,

    il.issotrx

   FROM (rv_c_invoiceline il

     JOIN c_invoice i ON ((i.c_invoice_id = il.c_invoice_id)))

  GROUP BY il.ad_client_id, il.ad_org_id, il.m_product_category_id, (firstof((il.dateinvoiced)::timestamp with time zone, 'MM'::character varying)), il.issotrx, i.c_currency_id;