money.credit_payment

↑ money schema

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.payment_id_seq'::regclass)

xact

bigint

No

payment_ts

timestamp with time zone

No

now()

voided

boolean

No

false

amount

numeric(6,2)

No

note

text

Yes

amount_collected

numeric(6,2)

No

accepting_usr

integer

No

Primary Key

(id)

Indexes

Index Method Definition

credit_payment_pkey PK

btree

CREATE UNIQUE INDEX credit_payment_pkey ON money.credit_payment USING btree (id)

money_credit_id_idx

btree

CREATE INDEX money_credit_id_idx ON money.credit_payment USING btree (id)

money_credit_payment_accepting_usr_idx

btree

CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment USING btree (accepting_usr)

money_credit_payment_payment_ts_idx

btree

CREATE INDEX money_credit_payment_payment_ts_idx ON money.credit_payment USING btree (payment_ts)

money_credit_payment_xact_idx

btree

CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment USING btree (xact)

Triggers

Trigger Timing Event Level Function

mat_summary_add_tgr

AFTER

INSERT

ROW

money.materialized_summary_payment_add()

mat_summary_del_tgr

BEFORE

DELETE

ROW

money.materialized_summary_payment_del()

mat_summary_upd_tgr

AFTER

UPDATE

ROW

money.materialized_summary_payment_update()

Trigger Bodies

mat_summary_add_tgr

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

BEGIN
	IF NOT NEW.voided THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_paid = COALESCE(total_paid, 0.0::numeric) + NEW.amount,
			last_payment_ts = NEW.payment_ts,
			last_payment_note = NEW.note,
			last_payment_type = TG_ARGV[0],
			balance_owed = balance_owed - NEW.amount
		  WHERE	id = NEW.xact;
	END IF;

	RETURN NEW;
END;

mat_summary_del_tgr

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

DECLARE
	prev_payment	money.payment_view%ROWTYPE;
	old_payment	money.payment_view%ROWTYPE;
BEGIN
	SELECT * INTO prev_payment FROM money.payment_view WHERE xact = OLD.xact AND NOT voided ORDER BY payment_ts DESC LIMIT 1 OFFSET 1;
	SELECT * INTO old_payment FROM money.payment_view WHERE xact = OLD.xact AND NOT voided ORDER BY payment_ts DESC LIMIT 1;

	IF OLD.id = old_payment.id THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	last_payment_ts = prev_payment.payment_ts,
			last_payment_note = prev_payment.note,
			last_payment_type = prev_payment.payment_type
		  WHERE	id = OLD.xact;
	END IF;

	IF NOT OLD.voided THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_paid = total_paid - 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_payment_update()
Timing: AFTER UPDATE ROW

DECLARE
	old_payment	money.payment_view%ROWTYPE;
	old_voided	money.payment_view%ROWTYPE;
BEGIN

	SELECT * INTO old_payment FROM money.payment_view WHERE xact = NEW.xact AND NOT voided ORDER BY payment_ts DESC LIMIT 1;
	SELECT * INTO old_voided FROM money.payment_view WHERE xact = NEW.xact ORDER BY payment_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_payment_ts = old_payment.payment_ts,
				last_payment_note = old_payment.note,
				last_payment_type = old_payment.payment_type
			  WHERE	id = OLD.xact;
		END IF;

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

	ELSIF NOT NEW.voided AND OLD.voided THEN

		IF OLD.id = old_payment.id THEN
			UPDATE	money.materialized_billable_xact_summary
			  SET	last_payment_ts = old_payment.payment_ts,
				last_payment_note = old_payment.note,
				last_payment_type = old_payment.payment_type
			  WHERE	id = OLD.xact;
		END IF;

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

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

	RETURN NEW;
END;
Previous
Next