Update pipeline's config and status wait condition

What does this MR do?

To address failure #325711 (closed).

• Explicitly require allow_failure: false for pipeline manual job
• Update merge request pipeline status wait condition to "waiting for manual action"
• Going with this option the Merge button doesn't have a dropdown anymore, switch to merge! instead of merge_immediately!

Screenshots (strongly suggested)

Pipeline is blocked when manual job is required to be successful

Does this MR meet the acceptance criteria?

Conformity

• 📋 Does this MR need a changelog?
  • I have included a changelog entry.
  • I have not included a changelog entry because _____.
• [-] Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• [-] Database guides
• [-] Separation of EE specific content

Related to #325711 (closed)