58 lines
2.8 KiB
SQL
58 lines
2.8 KiB
SQL
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)));;
|