PO Amendment Management

-- Approval Matrix Calculation
SELECT
    amendment_id,
    value_change_percent,
    CASE
        WHEN value_change_percent <= 5 THEN 'PROCUREMENT_OFFICER'
        WHEN value_change_percent <= 10 THEN 'DEPARTMENT_HEAD'
        WHEN value_change_percent <= 15 THEN 'PROCUREMENT_MANAGER'
        WHEN value_change_percent <= 25 THEN 'DIRECTOR'
        ELSE 'CFO'
    END AS required_approver,
    -- Add one level for price increases
    CASE WHEN amendment_type = 'PRICE_INCREASE' THEN TRUE ELSE FALSE END AS escalate_one_level
FROM po.po_amendments;

-- Partial Receipt Validation
CREATE FUNCTION validate_partial_receipt_amendment(
    p_amendment_id UUID,
    p_po_item_id UUID,
    p_new_quantity DECIMAL
) RETURNS BOOLEAN AS $$
DECLARE
    v_received_qty DECIMAL;
    v_invoiced_amount DECIMAL;
BEGIN
    -- Get received and invoiced amounts
    SELECT
        COALESCE(SUM(gi.accepted_qty), 0),
        COALESCE(SUM(ii.line_total), 0)
    INTO v_received_qty, v_invoiced_amount
    FROM po.po_items pi
    LEFT JOIN logistics.grn_items gi ON gi.po_item_id = pi.id
    LEFT JOIN logistics.invoice_items ii ON ii.po_item_id = pi.id
    WHERE pi.id = p_po_item_id;

    -- Validate: new quantity >= received quantity
    IF p_new_quantity < v_received_qty THEN
        RAISE EXCEPTION 'Cannot reduce quantity below received: %', v_received_qty;
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;