The flute items only have an effect in Gen 3 and 4 (plus OR/AS), and don't exist in Gen 7+. The effects are:
The flutes are not consumed upon use.
In Gen 5, the flutes can be sold to a maniac in Undella Town for extra money.
There is also:
- the Poke Flute, which is used ingame to wake up the sleeping Snorlax in the Kanto games. It can also wake up your Pokemon in battle.
- the Azure Flute which allows access to Arceus once you get to the top of Mt. Coronet.
- the Time Flute which awakens Celebi to purify your Shadow Pokemon in Pokemon Colosseum.
- the Sun and Moon Flutes that play a role in the storyline of Gen 7.
- the Celestica Flute that summons ridable Pokemon in Legends: Arceus.