Schema for opdr

CREATE TABLE ar_assets_accts_rcvbl(oid BIGINT, acct_type BIGINT, "name" VARCHAR, past_due_90 BIGINT, past_due_180 BIGINT, liquidated BIGINT, total BIGINT, rpt_id BIGINT, PRIMARY KEY(oid), FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id), FOREIGN KEY (acct_type) REFERENCES ar_erds_codes(code));
CREATE TABLE ar_assets_fixed(oid BIGINT, description VARCHAR, cost_basis BIGINT, depreciation BIGINT, book_value BIGINT, "value" BIGINT, asset_type BIGINT, rpt_id BIGINT, PRIMARY KEY(oid), FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id), FOREIGN KEY (asset_type) REFERENCES ar_erds_codes(code));
CREATE TABLE ar_assets_investments(oid BIGINT, inv_type BIGINT, "name" VARCHAR, amount BIGINT, rpt_id BIGINT, PRIMARY KEY(oid), FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id), FOREIGN KEY (inv_type) REFERENCES ar_erds_codes(code));
CREATE TABLE ar_assets_loans_rcvbl(oid BIGINT, loan_type BIGINT, "name" VARCHAR, purpose VARCHAR, "security" VARCHAR, terms VARCHAR, outstanding_start_amt BIGINT, outstanding_end_amt BIGINT, new_loan_amt BIGINT, cash_repayments BIGINT, non_cash_repayments BIGINT, rpt_id BIGINT, PRIMARY KEY(oid), FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id), FOREIGN KEY (loan_type) REFERENCES ar_erds_codes(code));
CREATE TABLE ar_assets_other(oid BIGINT, description VARCHAR, book_value BIGINT, rpt_id BIGINT, PRIMARY KEY(oid), FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id));
CREATE TABLE ar_assets_total(rpt_id BIGINT, cash_start BIGINT, cash_end BIGINT, accounts_receivable_start BIGINT, accounts_receivable_end BIGINT, investments_start BIGINT, investments_end BIGINT, fixed_assets_start BIGINT, fixed_assets_end BIGINT, treasury_securities_start BIGINT, treasury_securities_end BIGINT, other_assets_start BIGINT, other_assets_end BIGINT, loans_receivable_start BIGINT, loans_receivable_end BIGINT, total_start BIGINT, market_sec_total_cost BIGINT, market_sec_total_book_value BIGINT, other_inv_total_cost BIGINT, other_inv_total_book_value BIGINT, FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id));
CREATE TABLE ar_disbursements_benefits(oid BIGINT, description VARCHAR, paid_to VARCHAR, amount BIGINT, rpt_id BIGINT, PRIMARY KEY(oid), FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id));
CREATE TABLE ar_disbursements_emp_off(oid BIGINT, emp_off_type BIGINT, first_name VARCHAR, middle_name VARCHAR, last_name VARCHAR, title VARCHAR, status_other_payer VARCHAR, gross_salary BIGINT, allowances BIGINT, official_business BIGINT, other_not_rptd BIGINT, total BIGINT, rep_pct BIGINT, pol_pct BIGINT, cont_pct BIGINT, gen_ovrhd_pct BIGINT, admin_pct BIGINT, rpt_id BIGINT, item_num BIGINT, PRIMARY KEY(oid));
CREATE TABLE ar_disbursements_genrl(oid BIGINT, disbursement_type BIGINT, purpose VARCHAR, date VARCHAR, amount BIGINT, payer_payee_id BIGINT, rpt_id BIGINT, PRIMARY KEY(oid));
CREATE TABLE ar_disbursements_inv_purchases(oid BIGINT, description VARCHAR, "cost" BIGINT, book_value BIGINT, cash_paid BIGINT, rpt_id BIGINT, PRIMARY KEY(oid));
CREATE TABLE ar_disbursements_total(rpt_id BIGINT, representational BIGINT, political BIGINT, contributions BIGINT, general_overhead BIGINT, union_administration BIGINT, withheld BIGINT, members BIGINT, supplies BIGINT, fees BIGINT, administration BIGINT, direct_taxes BIGINT, strike_benefits BIGINT, per_capita_tax BIGINT, to_officers BIGINT, investments BIGINT, benefits BIGINT, loans_made BIGINT, loans_payment BIGINT, affiliates BIGINT, other_disbursements BIGINT, to_employees BIGINT, less_total_disbursed BIGINT, withheld_not_disbursed BIGINT, officer_deductions BIGINT, employee_deductions BIGINT, inv_purch_reinvestments BIGINT, all_other_rep_activities BIGINT, all_other_pol_activities BIGINT, all_other_contributions BIGINT, all_other_gen_overhead BIGINT, all_other_union_admin BIGINT, off_admin_expense BIGINT, edu_pub_expense BIGINT, pro_fees BIGINT, FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id));
CREATE TABLE ar_erds_codes(code_type VARCHAR, code BIGINT, "name" VARCHAR, code_description VARCHAR, PRIMARY KEY(code));
CREATE TABLE ar_liabilities_accts_paybl(oid BIGINT, acct_type BIGINT, "name" VARCHAR, total BIGINT, past_due_90 BIGINT, past_due_180 BIGINT, liquidated BIGINT, rpt_id BIGINT, PRIMARY KEY(oid), FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id), FOREIGN KEY (acct_type) REFERENCES ar_erds_codes(code));
CREATE TABLE ar_liabilities_loans_paybl(oid BIGINT, "source" VARCHAR, loans_owed_start BIGINT, loans_obtained BIGINT, cash_repayment BIGINT, non_cash_repayment BIGINT, loans_owed_end BIGINT, rpt_id BIGINT, PRIMARY KEY(oid), FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id));
CREATE TABLE ar_liabilities_other(oid BIGINT, description VARCHAR, amount BIGINT, rpt_id BIGINT, PRIMARY KEY(oid));
CREATE TABLE ar_liabilities_total(rpt_id BIGINT, accounts_payable_start BIGINT, accounts_payable_end BIGINT, loans_payable_start BIGINT, loans_payable_end BIGINT, mortgage_payable_start BIGINT, mortgage_payable_end BIGINT, other_liabilities_start BIGINT, other_liabilities_end BIGINT, total_start BIGINT, FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id));
CREATE TABLE ar_membership(oid BIGINT, membership_type BIGINT, category VARCHAR, number BIGINT, voting_eligibility VARCHAR, rpt_id BIGINT, PRIMARY KEY(oid), FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id), FOREIGN KEY (membership_type) REFERENCES ar_erds_codes(code));
CREATE TABLE ar_payer_payee(payer_payee_id BIGINT, payer_payee_type BIGINT, rcpt_disb_type BIGINT, "name" VARCHAR, po_box VARCHAR, street VARCHAR, city VARCHAR, state VARCHAR, zip VARCHAR, type_or_class VARCHAR, itemized BIGINT, non_itemized BIGINT, total BIGINT, rpt_id BIGINT, PRIMARY KEY(payer_payee_id));
CREATE TABLE ar_rates_dues_fees(oid BIGINT, rate_type BIGINT, amount VARCHAR, unit VARCHAR, minimum VARCHAR, maximum VARCHAR, rpt_id BIGINT, PRIMARY KEY(oid));
CREATE TABLE ar_receipts_inv_fa_sales(oid BIGINT, description VARCHAR, "cost" BIGINT, book_value BIGINT, gross_sales_price BIGINT, amount_recd BIGINT, rpt_id BIGINT, PRIMARY KEY(oid), FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id));
CREATE TABLE ar_receipts_other(oid BIGINT, receipt_type BIGINT, purpose VARCHAR, date VARCHAR, amount BIGINT, payer_payee_id BIGINT, rpt_id BIGINT, PRIMARY KEY(oid));
CREATE TABLE ar_receipts_total(rpt_id BIGINT, dues BIGINT, tax BIGINT, investments BIGINT, supplies BIGINT, loans_made BIGINT, interest BIGINT, dividends BIGINT, rents BIGINT, fees BIGINT, loans_obtained BIGINT, other_receipts BIGINT, affiliates BIGINT, members BIGINT, inv_sale_reinvestments BIGINT, all_other_receipts BIGINT, FOREIGN KEY (rpt_id) REFERENCES lm_data(rpt_id));
CREATE TABLE lm_data(union_name VARCHAR, aff_abbr VARCHAR, f_num BIGINT, fye BIGINT, unit_name VARCHAR, est_date DATE, term_date DATE, shortage VARCHAR, maximum_bond BIGINT, pd_covered_from DATE, pd_covered_to DATE, constitution_bylaw VARCHAR, terminate VARCHAR, ttl_assets BIGINT, subsidiary VARCHAR, desiq_pre VARCHAR, desig_num BIGINT, desig_suf VARCHAR, desig_name VARCHAR, ttl_liabilities BIGINT, ttl_receipts BIGINT, ttl_disbursements BIGINT, members BIGINT, register_date TIMESTAMP, amended VARCHAR, hardship VARCHAR, has_trust VARCHAR, pac_funds VARCHAR, outside_audit VARCHAR, has_property_change VARCHAR, assets_pledged VARCHAR, contingent VARCHAR, next_election VARCHAR, has_liquidated_liabilities VARCHAR, has_extended_loan_credit VARCHAR, has_liquidated_receivables VARCHAR, has_subsidiary VARCHAR, num_attachments BIGINT, rpt_id BIGINT, yr_covered BIGINT, amendment BIGINT, receive_date TIMESTAMP, adr_id BIGINT, address_type BIGINT, mail_firstname VARCHAR, mail_lastname VARCHAR, build_num VARCHAR, street_adr VARCHAR, city VARCHAR, state VARCHAR, zip VARCHAR, voice VARCHAR, mod_date TIMESTAMP, mod_id BIGINT, record_kept VARCHAR, form_type VARCHAR, PRIMARY KEY(rpt_id), FOREIGN KEY (address_type) REFERENCES ar_erds_codes(code), FOREIGN KEY (fye) REFERENCES ar_erds_codes(code));