money.materialized_billable_xact_summary

↑ money schema

Data-Modifying Triggers: This table has BEFORE ROW trigger(s) that modify row data before write. Values you INSERT or UPDATE may differ from what is actually stored. See the Triggers section below.

Written by External Triggers: This table can receive writes triggered by operations on other tables:

Columns

Column Type Nullable Default Notes

id PK

bigint

No

usr

integer

Yes

xact_start

timestamp with time zone

Yes

xact_finish

timestamp with time zone

Yes

total_paid

numeric

Yes

last_payment_ts

timestamp with time zone

Yes

last_payment_note

text

Yes

last_payment_type

name

Yes

total_owed

numeric

Yes

last_billing_ts

timestamp with time zone

Yes

last_billing_note

text

Yes

last_billing_type

text

Yes

balance_owed

numeric

Yes

xact_type

name

Yes

Primary Key

(id)

Indexes

Index Method Definition

materialized_billable_xact_summary_pkey PK

btree

CREATE UNIQUE INDEX materialized_billable_xact_summary_pkey ON money.materialized_billable_xact_summary USING btree (id)

money_mat_summary_usr_idx

btree

CREATE INDEX money_mat_summary_usr_idx ON money.materialized_billable_xact_summary USING btree (usr)

money_mat_summary_xact_start_idx

btree

CREATE INDEX money_mat_summary_xact_start_idx ON money.materialized_billable_xact_summary USING btree (xact_start)

Triggers

Trigger Timing Event Level Function

mat_summary_refund_balance_check_tgr

BEFORE

UPDATE

ROW

money.mbts_refundable_balance_check()

Trigger Bodies

mat_summary_refund_balance_check_tgr

Function: money.mbts_refundable_balance_check()
Timing: BEFORE UPDATE ROW

This trigger modifies the row before it is written (returns a modified NEW).

 
BEGIN
    -- Check if the raw xact balance has gone negative (balance_owed may be adjusted by this very trigger!)
    IF NEW.total_owed - NEW.total_paid < 0.0 THEN

        -- If negative (a refund), we increase it by the non-refundable payment total, but only up to 0.0
        SELECT  LEAST(
                    COALESCE(SUM(amount),0.0) -- non-refundable payment total
                      + (NEW.total_owed - NEW.total_paid), -- raw balance
                    0.0
                ) INTO NEW.balance_owed -- update the NEW record
          FROM  money.payment_view
          WHERE NOT refundable
                AND xact = NEW.id
                AND NOT voided;
    END IF;

    RETURN NEW;
END;
Previous
Next