CREATE OR REPLACE VIEW public.vw_utility_invoices AS
 WITH invoice_lines_agg AS (
         SELECT vil."intInvoiceId",
            sum(vil."dblQuantity") AS "dblTotalQuantity",
            sum(vil."dblLineTotal") AS "dblTotalLineAmount",
            max(vil."intContractLineId") AS "intContractLineId"
           FROM vw_utility_invoice_lines vil
          GROUP BY vil."intInvoiceId"
        ), payments_agg AS (
         SELECT am."intOriginId" AS "intInvoiceId",
            sum(am."dblAmount") AS "dblPaidAmountInBaseCurrency",
            sum(am."dblAmountSecondCurrency") AS "dblPaidAmountInSecondCurrency",
            count(*) AS "intCountPayments"
           FROM vw_utility_account_move am
          WHERE ((1 = 1) AND (am."intReconciliation" > 0) AND ((am."strAccountJournalCode")::text = 'CASH'::text) AND (am."strOrigin" = 'account.invoice'::text))
          GROUP BY am."intOriginId"
        )
 SELECT ai.id AS "intInvoiceId",
    ai.company AS "intCompanyId",
    company_party.name AS "strCompanyName",
    cur.name AS "strCompanyCurrency",
    COALESCE(ai.number, ''::character varying) AS "strInvoiceNumber",
    ai.invoice_date AS "dtmInvoiceDate",
    invoice_party.name AS "strPartyName",
    ai.type AS "strInvoiceType",
    aipt.name AS "strPaymentTerm",
    ai.accounting_date AS "dtmAccountingDate",
    ai.currency AS "intCurrencyId",
    cc.name AS "strCurrencyCode",
    ai.state AS "strInvoiceState",
    COALESCE(ai.move, 0) AS "intMoveId",
    ai.account AS "intAccountId",
    aa.name AS "strAccountName",
    ila."dblTotalQuantity" AS "dblInvoiceQuantity",
    ila."dblTotalLineAmount" AS "dblInvoiceAmount",
    ila."intContractLineId",
    pa."dblPaidAmountInBaseCurrency",
    pa."dblPaidAmountInSecondCurrency",
        CASE
            WHEN ((cur.name)::text = (cc.name)::text) THEN COALESCE(pa."dblPaidAmountInBaseCurrency", (0)::numeric)
            ELSE COALESCE(pa."dblPaidAmountInSecondCurrency", (0)::numeric)
        END AS "dblPaidAmount",
    (ila."dblTotalLineAmount" -
        CASE
            WHEN ((cur.name)::text = (cc.name)::text) THEN COALESCE(pa."dblPaidAmountInBaseCurrency", (0)::numeric)
            ELSE COALESCE(pa."dblPaidAmountInSecondCurrency", (0)::numeric)
        END) AS "dblOpenedAmount"
   FROM (((((((((account_invoice ai
     JOIN invoice_lines_agg ila ON ((ila."intInvoiceId" = ai.id)))
     JOIN currency_currency cc ON ((cc.id = ai.currency)))
     JOIN party_party invoice_party ON ((invoice_party.id = ai.party)))
     JOIN company_company comp ON ((comp.id = ai.company)))
     JOIN currency_currency cur ON ((comp.currency = cur.id)))
     JOIN party_party company_party ON ((company_party.id = comp.party)))
     JOIN account_invoice_payment_term aipt ON ((aipt.id = ai.payment_term)))
     LEFT JOIN account_account aa ON ((aa.id = ai.account)))
     LEFT JOIN payments_agg pa ON ((pa."intInvoiceId" = ai.id)));;