1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
|
-- 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;
|
