Purchase Order Lifecycle & Revisions

-- Example: 1000 units split across 4 deliveries
-- PO Item: 1000 units of Item XYZ

INSERT INTO po.po_schedule_lines (po_item_id, schedule_number, quantity, delivery_date)
VALUES
    (:po_item_id, 1, 250, '2024-02-01'),
    (:po_item_id, 2, 250, '2024-02-15'),
    (:po_item_id, 3, 250, '2024-03-01'),
    (:po_item_id, 4, 250, '2024-03-15');

SELECT
    po.po_number,
    poi.line_number,
    poi.item_description,
    poi.quantity - poi.received_qty - poi.cancelled_qty AS outstanding_qty,
    poi.promised_date,
    v.vendor_name,
    CASE
        WHEN poi.promised_date < CURRENT_DATE THEN 'OVERDUE'
        ELSE 'ON_TIME'
    END AS delivery_status
FROM po.po_items poi
JOIN po.purchase_orders po ON poi.po_id = po.id
JOIN vendor.vendors v ON po.vendor_id = v.id
WHERE po.status IN ('SENT', 'ACKNOWLEDGED', 'PARTIAL')
  AND poi.status IN ('OPEN', 'PARTIAL')
ORDER BY poi.promised_date;

SELECT
    v.vendor_name,
    COUNT(po.id) AS total_pos,
    SUM(CASE WHEN po.is_fully_received THEN 1 ELSE 0 END) AS fully_received,
    SUM(CASE WHEN po.is_fully_invoiced THEN 1 ELSE 0 END) AS fully_invoiced,
    SUM(po.total_amount) AS total_value,
    SUM(po.total_invoiced_amount) AS invoiced_value
FROM po.purchase_orders po
JOIN vendor.vendors v ON po.vendor_id = v.id
WHERE po.po_date >= :start_date
  AND po.status NOT IN ('DRAFT', 'CANCELLED')
GROUP BY v.id, v.vendor_name
ORDER BY total_value DESC;

SELECT
    po.po_number,
    poi.line_number,
    psl.schedule_number,
    psl.quantity AS scheduled_qty,
    psl.received_qty,
    psl.delivery_date,
    psl.status,
    l.location_name AS ship_to
FROM po.po_schedule_lines psl
JOIN po.po_items poi ON psl.po_item_id = poi.id
JOIN po.purchase_orders po ON poi.po_id = po.id
LEFT JOIN organization.locations l ON psl.ship_to_location_id = l.id
WHERE psl.status = 'SCHEDULED'
  AND psl.delivery_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '7 days'
ORDER BY psl.delivery_date;