dodil/erp-brick-expenses

DODIL ERP brick: expenses

src/brick_expenses/migrations/00007_rates/up.sql

-- 00007_rates - slice B item 5: mileage and daily allowance by the country's rules as dated rows (EXP-03, EXP-04, EXP-23): exp_rate, what a computed
-- line keeps of how it was computed, and the statutory rates every company starts with (UK AMAP 45p / 25p, DE 28.00 / 14.00 and 0.30 a km). Additive
-- only. ADD COLUMN is DISRUPTIVE on DataK3: migrate the test copy first, the live one at a quiet hour.

ALTER TABLE exp_line ADD COLUMN miles_or_km NUMERIC(19,4);

ALTER TABLE exp_line ADD COLUMN trip_from TIMESTAMP;

ALTER TABLE exp_line ADD COLUMN trip_to TIMESTAMP;

ALTER TABLE exp_line ADD COLUMN meals_provided_json VARCHAR;

ALTER TABLE exp_line ADD COLUMN calc_json VARCHAR;

ALTER TABLE exp_line ADD COLUMN rate_country VARCHAR;

ALTER TABLE exp_line ADD COLUMN rate_variant VARCHAR;

CREATE TABLE IF NOT EXISTS exp_rate (
    kind                VARCHAR NOT NULL,
    country             VARCHAR NOT NULL,
    variant             VARCHAR NOT NULL,
    band                INTEGER NOT NULL,
    valid_from          DATE NOT NULL,
    rate_amount         NUMERIC(19,4),
    currency            VARCHAR,
    band_upto           NUMERIC(19,4),
    unit                VARCHAR,
    partial_day_amount  NUMERIC(19,4),
    breakfast_pct       NUMERIC(19,4),
    lunch_pct           NUMERIC(19,4),
    dinner_pct          NUMERIC(19,4),
    year_start          VARCHAR,
    source              VARCHAR,
    set_by              VARCHAR,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (kind, country, variant, band, valid_from)
);


INSERT INTO exp_rate (kind, country, variant, band, valid_from, rate_amount, currency, band_upto, unit, partial_day_amount, breakfast_pct, lunch_pct, dinner_pct, year_start, source, set_by, created_at, updated_at) VALUES
    ('mileage', 'GB', 'car', 1, DATE '2011-04-06', 0.4500, 'GBP', 10000, 'mile', NULL, 0, 0, 0, '04-06', 'HMRC approved mileage allowance payments, ITEPA 2003 ss. 229-236', 'migration', NOW(), NOW()),
    ('mileage', 'GB', 'car', 2, DATE '2011-04-06', 0.2500, 'GBP', NULL, 'mile', NULL, 0, 0, 0, '04-06', 'HMRC approved mileage allowance payments, ITEPA 2003 ss. 229-236', 'migration', NOW(), NOW()),
    ('mileage', 'GB', 'motorcycle', 1, DATE '2011-04-06', 0.2400, 'GBP', NULL, 'mile', NULL, 0, 0, 0, '04-06', 'HMRC approved mileage allowance payments, ITEPA 2003 ss. 229-236', 'migration', NOW(), NOW()),
    ('mileage', 'GB', 'bicycle', 1, DATE '2011-04-06', 0.2000, 'GBP', NULL, 'mile', NULL, 0, 0, 0, '04-06', 'HMRC approved mileage allowance payments, ITEPA 2003 ss. 229-236', 'migration', NOW(), NOW()),
    ('mileage', 'DE', 'car', 1, DATE '2001-01-01', 0.3000, 'EUR', NULL, 'km', NULL, 0, 0, 0, '01-01', 's. 9 (1) no. 4a EStG (0.30 EUR a kilometre by car)', 'migration', NOW(), NOW()),
    ('per_diem', 'DE', 'domestic', 1, DATE '2025-01-01', 28.0000, 'EUR', NULL, NULL, 14.0000, 20, 40, 40, '01-01', 's. 9 (4a) EStG (Verpflegungsmehraufwand); BMF', 'migration', NOW(), NOW());