I know that Bethesda games relies heavily on scripts to ensure that the right things happen at the right times. I understand their basic functions and I understand that they can do some really complex things, but what can Papyrus really do?

I know a bit of object-oriented and logic programming so concepts and fundamentals are there, as is the basis to learn a computer "language" if you will. However, I feel that I should know at least some of what modders use Papyrus to do in their mods. I will most likely be implementing it in my own mod, but what examples can you give me of why scripts are neccesary or why you would use a Papyrus script in your mod (and for what).

Thanks and I'm sorry if this question is a bit abstract or obtuse. I just want to get a handle on what the standard practices are as well as the "bar" of implementation in certain mods.

This question is abstract. One can do nearly anything with Papyrus, but there are a few specific limitations. Check out Cipcsis' Papyrus tutorials for more info, or go to the creationkit wiki and start looking through script documentation.

I'd suggest having a look at mods that have been released. Most people include the source files for their scripts, which is a great way to see how they implemented a certain feature.

I also recommend getting the http://skyrim.nexusmods.com/mods/13430. It is an easy way to find pretty much any info related to Papyrus (click on an item to open up the appropriate article in the Creation Kit wiki). It isn't up to date, so there are a few things it doesn't contain.

Just have a look at the list of functions:

http://www.creationkit.com/Category:Papyrus

That gives you a pretty good idea what kind of things you can do with it.

In an abstract sense, Papyrus is fundamentally event-driven--it is used for having something other than the game default happen when the player does something, or has something done to them.

My mod is basically just a script (7 Script). The script is the foundation of my mod.
http://skyrim.nexusmods.com/mods/18309/

4 cart model, 1 map marker, 2 spells, 1 container and ca. 1200 line papyrus code

The main scrpit is the menu and handle the cart models, harness the horse mount the charakter etc.
One Script is for the Map marker. My marker is independent and goes behind the cart via script.
One script gives the player two spells and the two spells also are scripts
One script run, if the player load a save game, and reconfigure the cart.
One script ist for Versions update

The game is mainly CBSE (Component Based Software Engineering) not OOP (Object-Oriented Programming). Although there is naturally crossover as CBSE is arguably just a different way of thinking about OOP. Figuring out the whole event-driven CBSE paradigm will help you understand how to better utilize Papyrus as it can pretty much do anything.

Good, simple explanation of CBSE for Unity Engine:
http://www.openchord.org/wordpress/2011/09/unity-and-the-component-model/

Good examples in C++, but more technical:
http://gameprogrammingpatterns.com/component.html