IN `p_employee_name` VARCHAR(255)

BEGIN
    -- Declare variables
    DECLARE total_received DECIMAL(10,2) DEFAULT 0;
    DECLARE total_deducted DECIMAL(10,2) DEFAULT 0;
    DECLARE net_advance DECIMAL(10,2) DEFAULT 0;
    
    -- Get total received advances
    SELECT COALESCE(SUM(amount), 0) INTO total_received
    FROM advance_tracker
    WHERE employee_name = p_employee_name AND status = 'received';
    
    -- Get total deducted advances
    SELECT COALESCE(SUM(amount), 0) INTO total_deducted
    FROM advance_tracker
    WHERE employee_name = p_employee_name AND status = 'deducted';
    
    -- Calculate net advance
    SET net_advance = total_received - total_deducted;
    
    -- Update the employee's advance_avl field
    UPDATE employees
    SET advance_avl = net_advance
    WHERE employee = p_employee_name;
END