If you have ever tried to dig into the PeopleSoft HRMS* view
definitions related to row-level security, you have seen many
"meta-SQL" clauses. These begin with "%SQL" and they are
references to SQL objects. A SQL object is simply a
piece of SQL text (possibly including other %SQL clauses).
The %SQL clause is replaced with the text from the SQL
This is a simple concept. The difficulty comes in trying to
read SQL that contains numerous nested %SQL clauses. Also,
SQL objects can receive parameters. These are referenced
as %P(1), %P(2), etc. A %SQL clause can then pass parameters
to the corresponding SQL object. The value of each parameter
replaces its %P() marker. This increases the flexibility
of SQL objects, allowing them to be reused in several places.
But all of these nested and parameterized references
can be hard to follow.
Fortunately, there is an easy way to expand all of the SQL.
Open the view definition in App Designer. Right-click on
the text and select "Resolve Meta SQL" from the pop-up menu.
All of the %SQL clauses will be replaced and you will be
able to read the entire view definition as normal SQL.
Note that SQL objects are also used in PeopleCode
to instantiate SQL objects in memory. These can then be used
in issuing a database query or update operation.
But it is probably more
common to see them used in view definitions.
I know that it's called HCM now.
But I haven't been able to make the transition from calling people
"resources", which is bad enough, to calling them "capital". I'm
waiting for the next version, which I believe will be called
HLM, or "Human Livestock Management". We already do headcount
reporting—what could be more appropriate?