Has Mario and Luigi being plumbers had any effect on the plot of any game?

They were first shown to be plumbers in the original Mario Bros. game

^{(Thanks to Chase Sandmann for the box art correction.)}

The original Mario Bros. game, first released in arcades in 1983, was the first game in which Mario and his brother were shown to be plumbers. In fact, the entire premise of the game was about their plumbing, in which they were attempting to eliminate strange creatures in the sewers of New York. During the game, enemies would come out of the pipes at the top of the screen and make their way down to the pipes at the bottom, which they would enter to loop back to the top.

This was a change from Mario's first appearance in Donkey Kong, where Mario (at the time called Jumpman) was instead a carpenter.

It was a plot point in Mario & Luigi: Superstar Saga

Relatively early on in this RPG for the Game Boy Advance, the Mario Bros. are required to fix the plumbing system in Beanbean Castle, supposedly to prove their identities. Having been explicitly called out in dialogue, this is likely the most well-known instance of the brothers' profession being relevant to the plot of one of the games.

In Super Mario 3D World for the Wii U, the intro shows the Mario Brothers stumbling upon a broken pipe and they set about fixing it with their plumbing powers. It's an extremely minor plot point but a plot point nonetheless.