dodil/erp-brick-payments

DODIL ERP brick: payments

src/brick_payments/migrations/00006_v6/up.sql

-- 00006_v6 - additive only (payments v6: PAY-49..PAY-51): a cash bag another module sent to the bank (deposit batch kind cash), the expected card
-- settlements and the payouts to a person. ADD COLUMN is DISRUPTIVE: migrate the test copy first, the live one at a quiet hour.

ALTER TABLE pay_deposit_batch ADD COLUMN kind VARCHAR;

ALTER TABLE pay_deposit_batch ADD COLUMN batch_ref VARCHAR;

ALTER TABLE pay_deposit_batch ADD COLUMN source_module VARCHAR;

ALTER TABLE pay_deposit_batch ADD COLUMN clearing_purpose VARCHAR;

ALTER TABLE pay_deposit_batch ADD COLUMN expected_on DATE;

ALTER TABLE pay_deposit_batch ADD COLUMN company_code VARCHAR;

ALTER TABLE pay_deposit_batch ADD COLUMN difference NUMERIC(18,2);

ALTER TABLE pay_deposit_batch ADD COLUMN accepted_by VARCHAR;

ALTER TABLE pay_deposit_batch ADD COLUMN accepted_sub VARCHAR;

CREATE TABLE IF NOT EXISTS pay_card_settlement (
    settlement_ref    VARCHAR NOT NULL,
    provider          VARCHAR,
    company_code      VARCHAR,
    currency          VARCHAR,
    gross             NUMERIC(18,2),
    business_date     DATE,
    transaction_count INTEGER,
    source_module     VARCHAR,
    lifecycle         VARCHAR,
    bank_account_id   VARCHAR,
    statement_line_id VARCHAR,
    fees              NUMERIC(18,2),
    fee_pct           NUMERIC(19,4),
    matched_by        VARCHAR,
    matched_sub       VARCHAR,
    accepted_by       VARCHAR,
    accepted_sub      VARCHAR,
    accept_reason     VARCHAR,
    claim             VARCHAR,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (settlement_ref)
);

CREATE TABLE IF NOT EXISTS pay_payout (
    payout_ref        VARCHAR NOT NULL,
    payout_id         VARCHAR,
    payee_bp_id       BIGINT,
    person_id         VARCHAR,
    company_code      VARCHAR,
    currency          VARCHAR,
    amount            NUMERIC(18,2),
    due_on            DATE,
    reference         VARCHAR,
    source_module     VARCHAR,
    source_document   VARCHAR,
    clearing_purpose  VARCHAR,
    lifecycle         VARCHAR,
    bank_account_id   VARCHAR,
    requested_by      VARCHAR,
    approved_by       VARCHAR,
    approved_sub      VARCHAR,
    approved_on       DATE,
    exported_by       VARCHAR,
    exported_sub      VARCHAR,
    exported_on       DATE,
    export_key        VARCHAR,
    paid_on           DATE,
    bank_ref          VARCHAR,
    statement_line_id VARCHAR,
    journal_id        VARCHAR,
    returned_on       DATE,
    cancel_reason     VARCHAR,
    cancelled_by      VARCHAR,
    told_source_at    TIMESTAMP,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (payout_ref)
);