diff options
Diffstat (limited to 'db/migrations/0097_poa_initial_setup.sql')
| -rw-r--r-- | db/migrations/0097_poa_initial_setup.sql | 95 |
1 files changed, 0 insertions, 95 deletions
diff --git a/db/migrations/0097_poa_initial_setup.sql b/db/migrations/0097_poa_initial_setup.sql deleted file mode 100644 index fae3f4d1..00000000 --- a/db/migrations/0097_poa_initial_setup.sql +++ /dev/null @@ -1,95 +0,0 @@ --- Drop existing tables and views -DROP VIEW IF EXISTS change_orders_detail_view; -DROP TABLE IF EXISTS change_order_items CASCADE; -DROP TABLE IF EXISTS change_orders CASCADE; -DROP VIEW IF EXISTS poa_detail_view; -DROP TABLE IF EXISTS poa CASCADE; - --- Create POA table -CREATE TABLE poa ( - id SERIAL PRIMARY KEY, - contract_no VARCHAR(100) NOT NULL, - original_contract_no VARCHAR(100) NOT NULL, - project_id INTEGER NOT NULL, - vendor_id INTEGER NOT NULL, - original_contract_name VARCHAR(255) NOT NULL, - original_status VARCHAR(50) NOT NULL, - delivery_terms TEXT, - delivery_date DATE, - delivery_location VARCHAR(255), - currency VARCHAR(10), - total_amount NUMERIC(12,2), - discount NUMERIC(12,2), - tax NUMERIC(12,2), - shipping_fee NUMERIC(12,2), - net_total NUMERIC(12,2), - change_reason TEXT, - approval_status VARCHAR(50) DEFAULT 'PENDING', - created_at TIMESTAMP NOT NULL DEFAULT NOW(), - updated_at TIMESTAMP NOT NULL DEFAULT NOW(), - CONSTRAINT poa_original_contract_no_contracts_contract_no_fk - FOREIGN KEY (original_contract_no) - REFERENCES contracts(contract_no) - ON DELETE CASCADE, - CONSTRAINT poa_project_id_projects_id_fk - FOREIGN KEY (project_id) - REFERENCES projects(id) - ON DELETE CASCADE, - CONSTRAINT poa_vendor_id_vendors_id_fk - FOREIGN KEY (vendor_id) - REFERENCES vendors(id) - ON DELETE CASCADE -); - --- Create POA detail view -CREATE VIEW poa_detail_view AS -SELECT - -- POA primary information - poa.id, - poa.contract_no, - poa.change_reason, - poa.approval_status, - - -- Original PO information - poa.original_contract_no, - poa.original_contract_name, - poa.original_status, - c.start_date as original_start_date, - c.end_date as original_end_date, - - -- Project information - poa.project_id, - p.code as project_code, - p.name as project_name, - - -- Vendor information - poa.vendor_id, - v.vendor_name, - - -- Changed delivery details - poa.delivery_terms, - poa.delivery_date, - poa.delivery_location, - - -- Changed financial information - poa.currency, - poa.total_amount, - poa.discount, - poa.tax, - poa.shipping_fee, - poa.net_total, - - -- Timestamps - poa.created_at, - poa.updated_at, - - -- Electronic signature status - EXISTS ( - SELECT 1 - FROM contract_envelopes - WHERE contract_envelopes.contract_id = poa.id - ) as has_signature -FROM poa -LEFT JOIN contracts c ON poa.original_contract_no = c.contract_no -LEFT JOIN projects p ON poa.project_id = p.id -LEFT JOIN vendors v ON poa.vendor_id = v.id;
\ No newline at end of file |