75 lines
7.7 KiB
PL/PgSQL
75 lines
7.7 KiB
PL/PgSQL
BEGIN;
|
|
|
|
-- ============================================================
|
|
-- Scotia Passport Visa recurring transaction models
|
|
-- ============================================================
|
|
|
|
-- SmartStop Storage → 6010 Building Rent + HST (partner 11463)
|
|
INSERT INTO account_reconcile_model (name, company_id, trigger, match_label, match_label_param, mapped_partner_id, can_be_proposed, sequence, active, create_uid, write_uid, create_date, write_date)
|
|
VALUES ('{"en_US": "SmartStop Storage Rent"}', 1, 'auto_reconcile', 'contains', 'smartstop', 11463, true, 130, true, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line (model_id, company_id, account_id, amount_type, amount, amount_string, label, sequence, create_uid, write_uid, create_date, write_date)
|
|
VALUES (currval('account_reconcile_model_id_seq'), 1, 560, 'percentage', 100, '100', '{"en_US": "SmartStop Storage Unit Rent"}', 10, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line_account_tax_rel (account_reconcile_model_line_id, account_tax_id)
|
|
VALUES (currval('account_reconcile_model_line_id_seq'), 20);
|
|
|
|
-- QuickBooks / Intuit → 6050 IT Expenses + HST (partner 5830)
|
|
INSERT INTO account_reconcile_model (name, company_id, trigger, match_label, match_label_param, mapped_partner_id, can_be_proposed, sequence, active, create_uid, write_uid, create_date, write_date)
|
|
VALUES ('{"en_US": "QuickBooks / Intuit Subscription"}', 1, 'auto_reconcile', 'contains', 'quickbooks', 5830, true, 131, true, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line (model_id, company_id, account_id, amount_type, amount, amount_string, label, sequence, create_uid, write_uid, create_date, write_date)
|
|
VALUES (currval('account_reconcile_model_id_seq'), 1, 495, 'percentage', 100, '100', '{"en_US": "QuickBooks Online Subscription"}', 10, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line_account_tax_rel (account_reconcile_model_line_id, account_tax_id)
|
|
VALUES (currval('account_reconcile_model_line_id_seq'), 20);
|
|
|
|
-- Also catch the "qbooks" variant
|
|
INSERT INTO account_reconcile_model (name, company_id, trigger, match_label, match_label_param, mapped_partner_id, can_be_proposed, sequence, active, create_uid, write_uid, create_date, write_date)
|
|
VALUES ('{"en_US": "QBooks Online (Intuit)"}', 1, 'auto_reconcile', 'contains', 'qbooks', 5830, true, 132, true, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line (model_id, company_id, account_id, amount_type, amount, amount_string, label, sequence, create_uid, write_uid, create_date, write_date)
|
|
VALUES (currval('account_reconcile_model_id_seq'), 1, 495, 'percentage', 100, '100', '{"en_US": "QuickBooks Online Subscription"}', 10, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line_account_tax_rel (account_reconcile_model_line_id, account_tax_id)
|
|
VALUES (currval('account_reconcile_model_line_id_seq'), 20);
|
|
|
|
-- Vistaprint → 6270 Stationery and Printing + HST (partner 5027)
|
|
INSERT INTO account_reconcile_model (name, company_id, trigger, match_label, match_label_param, mapped_partner_id, can_be_proposed, sequence, active, create_uid, write_uid, create_date, write_date)
|
|
VALUES ('{"en_US": "Vistaprint Printing"}', 1, 'auto_reconcile', 'contains', 'vistaprint', 5027, true, 133, true, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line (model_id, company_id, account_id, amount_type, amount, amount_string, label, sequence, create_uid, write_uid, create_date, write_date)
|
|
VALUES (currval('account_reconcile_model_id_seq'), 1, 519, 'percentage', 100, '100', '{"en_US": "Vistaprint Business Cards/Printing"}', 10, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line_account_tax_rel (account_reconcile_model_line_id, account_tax_id)
|
|
VALUES (currval('account_reconcile_model_line_id_seq'), 20);
|
|
|
|
-- OpenRouter (AI API) → 6050 IT Expenses, no HST (US company)
|
|
INSERT INTO account_reconcile_model (name, company_id, trigger, match_label, match_label_param, can_be_proposed, sequence, active, create_uid, write_uid, create_date, write_date)
|
|
VALUES ('{"en_US": "OpenRouter AI API"}', 1, 'auto_reconcile', 'contains', 'openrouter', true, 134, true, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line (model_id, company_id, account_id, amount_type, amount, amount_string, label, sequence, create_uid, write_uid, create_date, write_date)
|
|
VALUES (currval('account_reconcile_model_id_seq'), 1, 495, 'percentage', 100, '100', '{"en_US": "OpenRouter AI API Usage"}', 10, 2, 2, NOW(), NOW());
|
|
|
|
-- Crunchyroll → 6070 Dues and Subscriptions, no HST (US company)
|
|
INSERT INTO account_reconcile_model (name, company_id, trigger, match_label, match_label_param, can_be_proposed, sequence, active, create_uid, write_uid, create_date, write_date)
|
|
VALUES ('{"en_US": "Crunchyroll Subscription"}', 1, 'auto_reconcile', 'contains', 'crunchyroll', true, 135, true, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line (model_id, company_id, account_id, amount_type, amount, amount_string, label, sequence, create_uid, write_uid, create_date, write_date)
|
|
VALUES (currval('account_reconcile_model_id_seq'), 1, 501, 'percentage', 100, '100', '{"en_US": "Crunchyroll Streaming Subscription"}', 10, 2, 2, NOW(), NOW());
|
|
|
|
-- Punjabi Community Health → 6490 Donation, no HST
|
|
INSERT INTO account_reconcile_model (name, company_id, trigger, match_label, match_label_param, can_be_proposed, sequence, active, create_uid, write_uid, create_date, write_date)
|
|
VALUES ('{"en_US": "Punjabi Community Health Donation"}', 1, 'auto_reconcile', 'contains', 'punjabi community', true, 136, true, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line (model_id, company_id, account_id, amount_type, amount, amount_string, label, sequence, create_uid, write_uid, create_date, write_date)
|
|
VALUES (currval('account_reconcile_model_id_seq'), 1, 528, 'percentage', 100, '100', '{"en_US": "Punjabi Community Health Centre Donation"}', 10, 2, 2, NOW(), NOW());
|
|
|
|
-- Canada Worldwide Services → need to check account... shipping/customs likely
|
|
-- Using 8010 Shipping & Delivery + HST as best guess
|
|
INSERT INTO account_reconcile_model (name, company_id, trigger, match_label, match_label_param, can_be_proposed, sequence, active, create_uid, write_uid, create_date, write_date)
|
|
VALUES ('{"en_US": "Canada Worldwide Services - Shipping"}', 1, 'auto_reconcile', 'contains', 'canada worldwide', true, 137, true, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line (model_id, company_id, account_id, amount_type, amount, amount_string, label, sequence, create_uid, write_uid, create_date, write_date)
|
|
VALUES (currval('account_reconcile_model_id_seq'), 1, 518, 'percentage', 100, '100', '{"en_US": "Canada Worldwide Shipping/Customs"}', 10, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line_account_tax_rel (account_reconcile_model_line_id, account_tax_id)
|
|
VALUES (currval('account_reconcile_model_line_id_seq'), 20);
|
|
|
|
-- Apple Bill → 6050 IT Expenses + HST (already have model 50 for APPLE.COM but not "apple bill")
|
|
INSERT INTO account_reconcile_model (name, company_id, trigger, match_label, match_label_param, can_be_proposed, sequence, active, create_uid, write_uid, create_date, write_date)
|
|
VALUES ('{"en_US": "Apple Bill - iCloud/Services"}', 1, 'auto_reconcile', 'contains', 'apple bill', true, 138, true, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line (model_id, company_id, account_id, amount_type, amount, amount_string, label, sequence, create_uid, write_uid, create_date, write_date)
|
|
VALUES (currval('account_reconcile_model_id_seq'), 1, 495, 'percentage', 100, '100', '{"en_US": "Apple iCloud/Services Subscription"}', 10, 2, 2, NOW(), NOW());
|
|
INSERT INTO account_reconcile_model_line_account_tax_rel (account_reconcile_model_line_id, account_tax_id)
|
|
VALUES (currval('account_reconcile_model_line_id_seq'), 20);
|
|
|
|
COMMIT;
|