What exactly is “pinning” in relation to indexes

As your own question answers: "Oracle Applications is an [ERP]." An "Oracle Apps DBA" will be someone who is experienced in working with the particular schema and tooling around this ERP. There are hundreds (if not thousands) of packages, tables, stored procedures, and interfaces that are specifically built for the ERP. It is not a trivial database to admin. You can get a brief glimpse into some of the things in their ERP by looking around - for example, this blog - or by logging into a Metalink account as someone who is a customer of their ERP solution, where you can get ERDs and other documentation.

What you describe in your second paragraph sounds like the Entity Attribute Value pattern.

This is a known pattern for dealing with the type of data you are talking about. When you need to be able to be extensible, drive the types from the data and allow you to start tracking new types as you grow. It allows you stay flexible and agile and be a little more forgiving and generic in your database design and schema.

This pattern works, even in relational databases, but it does take more work to get good performance, and can cause you headaches down the line depending on how many rows, how many inserts you are doing and how frequently you query it. This SO Question talks about some of the pitfalls nicely in the firs answer listed with 20 votes. I have seen this model work well, especially in environments where the new types and entities really do come in and we want to let that be more application driven or user driven but we were fighting with performance more often in that model than a traditional relational database model but we needed the flexibility and we made it work. Definitely look at that SO question and do some more research on EAV and whatever DBMS you are using to find examples from others who have tried the same.