34 lines
882 B
SQL
34 lines
882 B
SQL
-- Recreate PO/GRN views after locations merge
|
|
|
|
DROP VIEW IF EXISTS v_purchase_orders CASCADE;
|
|
DROP VIEW IF EXISTS v_grn CASCADE;
|
|
|
|
CREATE VIEW v_purchase_orders AS
|
|
SELECT
|
|
po.*,
|
|
pl.code AS plant_code,
|
|
pl.name AS plant_name,
|
|
wh.code AS warehouse_code,
|
|
wh.name AS warehouse_name,
|
|
v.vendor_code,
|
|
v.vendor_name
|
|
FROM purchase_orders po
|
|
JOIN locations pl ON pl.id = po.plant_id AND pl.type = 'plant'
|
|
LEFT JOIN locations wh ON wh.id = po.warehouse_id AND wh.type = 'warehouse'
|
|
JOIN vendors v ON v.id = po.vendor_id
|
|
WHERE po.deleted_at IS NULL;
|
|
|
|
CREATE VIEW v_grn AS
|
|
SELECT
|
|
g.*,
|
|
po.po_number,
|
|
v.vendor_code,
|
|
v.vendor_name,
|
|
wh.code AS warehouse_code,
|
|
wh.name AS warehouse_name
|
|
FROM grn g
|
|
JOIN purchase_orders po ON po.id = g.po_id
|
|
JOIN vendors v ON v.id = g.vendor_id
|
|
JOIN locations wh ON wh.id = g.warehouse_id AND wh.type = 'warehouse'
|
|
WHERE g.deleted_at IS NULL;
|