money.billing

↑ 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.

Deferrable Constraints: The following FK constraints are deferrable — they are checked at transaction end, not statement end: billing_btype_fkey.

Trigger Side Effects: Writing to this table automatically triggers writes to other tables:

Columns

Column Type Nullable Default Notes

id PK

bigint

No

nextval('money.billing_id_seq'::regclass)

xact

bigint

No

billing_ts

timestamp with time zone

No

voided

boolean

No

false

voider

integer

Yes

void_time

timestamp with time zone

Yes

amount

numeric(6,2)

No

billing_type

text

No

btype FK

integer

No

→ config.billing_type(id)

note

text

Yes

create_date

timestamp with time zone

No

now()

period_start

timestamp with time zone

Yes

period_end

timestamp with time zone

Yes

Primary Key

(id)

Foreign Keys

Column(s) References On Delete On Update Deferrable Constraint

btype

config.billing_type(id)

RESTRICT

NO ACTION

DEFERRED

billing_btype_fkey

Indexes

Index Method Definition

billing_pkey PK

btree

CREATE UNIQUE INDEX billing_pkey ON money.billing USING btree (id)

m_b_create_date_idx

btree

CREATE INDEX m_b_create_date_idx ON money.billing USING btree (create_date)

m_b_period_end_idx

btree

CREATE INDEX m_b_period_end_idx ON money.billing USING btree (period_end)

m_b_period_start_idx

btree

CREATE INDEX m_b_period_start_idx ON money.billing USING btree (period_start)

m_b_time_idx

btree

CREATE INDEX m_b_time_idx ON money.billing USING btree (billing_ts)

m_b_voider_idx

btree

CREATE INDEX m_b_voider_idx ON money.billing USING btree (voider)

m_b_xact_idx

btree

CREATE INDEX m_b_xact_idx ON money.billing USING btree (xact)

Triggers

Trigger Timing Event Level Function

maintain_billing_ts_tgr

BEFORE

INSERT OR UPDATE

ROW

money.maintain_billing_ts()

mat_summary_add_tgr

AFTER

INSERT

ROW

money.materialized_summary_billing_add()

mat_summary_del_tgr

BEFORE

DELETE

ROW

money.materialized_summary_billing_del()

mat_summary_upd_tgr

AFTER

UPDATE

ROW

money.materialized_summary_billing_update()

Trigger Bodies

maintain_billing_ts_tgr

Function: money.maintain_billing_ts()
Timing: BEFORE INSERT OR UPDATE ROW

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

 
BEGIN
	NEW.billing_ts := COALESCE(NEW.period_end, NEW.create_date);
	RETURN NEW;
END;

mat_summary_add_tgr

Function: money.materialized_summary_billing_add()
Timing: AFTER INSERT ROW

BEGIN
	IF NOT NEW.voided THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = COALESCE(total_owed, 0.0::numeric) + NEW.amount,
			last_billing_ts = NEW.billing_ts,
			last_billing_note = NEW.note,
			last_billing_type = NEW.billing_type,
			balance_owed = balance_owed + NEW.amount
		  WHERE	id = NEW.xact;
	END IF;

	RETURN NEW;
END;

mat_summary_del_tgr

Function: money.materialized_summary_billing_del()
Timing: BEFORE DELETE ROW

DECLARE
	prev_billing	money.billing%ROWTYPE;
	old_billing	money.billing%ROWTYPE;
BEGIN
	SELECT * INTO prev_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1 OFFSET 1;
	SELECT * INTO old_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1;

	IF OLD.id = old_billing.id THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	last_billing_ts = prev_billing.billing_ts,
			last_billing_note = prev_billing.note,
			last_billing_type = prev_billing.billing_type
		  WHERE	id = OLD.xact;
	END IF;

	IF NOT OLD.voided THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed - OLD.amount,
			balance_owed = balance_owed - OLD.amount
		  WHERE	id = OLD.xact;
	END IF;

	RETURN OLD;
END;

mat_summary_upd_tgr

Function: money.materialized_summary_billing_update()
Timing: AFTER UPDATE ROW

DECLARE
	old_billing	money.billing%ROWTYPE;
	old_voided	money.billing%ROWTYPE;
BEGIN

	SELECT * INTO old_billing FROM money.billing WHERE xact = NEW.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1;
	SELECT * INTO old_voided FROM money.billing WHERE xact = NEW.xact ORDER BY billing_ts DESC LIMIT 1;

	IF NEW.voided AND NOT OLD.voided THEN
		IF OLD.id = old_voided.id THEN
			UPDATE	money.materialized_billable_xact_summary
			  SET	last_billing_ts = old_billing.billing_ts,
				last_billing_note = old_billing.note,
				last_billing_type = old_billing.billing_type
			  WHERE	id = OLD.xact;
		END IF;

		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed - NEW.amount,
			balance_owed = balance_owed - NEW.amount
		  WHERE	id = NEW.xact;

	ELSIF NOT NEW.voided AND OLD.voided THEN

		IF OLD.id = old_billing.id THEN
			UPDATE	money.materialized_billable_xact_summary
			  SET	last_billing_ts = old_billing.billing_ts,
				last_billing_note = old_billing.note,
				last_billing_type = old_billing.billing_type
			  WHERE	id = OLD.xact;
		END IF;

		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed + NEW.amount,
			balance_owed = balance_owed + NEW.amount
		  WHERE	id = NEW.xact;

	ELSE
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed - (OLD.amount - NEW.amount),
			balance_owed = balance_owed - (OLD.amount - NEW.amount)
		  WHERE	id = NEW.xact;
	END IF;

	RETURN NEW;
END;

Referenced By

The following tables have foreign keys pointing to money.billing (1 referencing table(s)):

Table Referencing Column(s) Referenced Column(s) Constraint

money.account_adjustment

billing

id

account_adjustment_billing_fkey
Previous
Next