Search Results return_legislation_code

Overview

PER_ITS_PKG is a small, single-purpose PL/SQL package in the Oracle E-Business Suite Applications (APPS) schema that addresses a recurring requirement in multi-legislation deployments: determining which legislation (country-specific rule set) applies to a user's current session. Oracle EBS Human Resources and Payroll are "legislated" products, meaning that many business rules, validations, and data structures are driven by the legislation code associated with a Business Group. The PER_ITS_PKG package exposes the legislation code resolution as a callable database function so that other PL/SQL units, forms, and SQL statements can retrieve it consistently without re-implementing the derivation logic.

The package is declared with AUTHID CURRENT_USER, so its unqualified references are resolved using the privileges of the invoking user at runtime. It was originally created during the Oracle HRMS 11i era (its header comment dates to 1999) and remains present and documented in ETRM through releases 12.1.1 and 12.2.2.

Key Procedures and Functions

The package declares exactly one documented member function:

• RETURN_LEGISLATION_CODE — Returns the legislation code for the Business Group associated with a given responsibility. The caller supplies a responsibility identifier; the function returns the legislation code as a VARCHAR2 value. This is the function a user locates when searching for "return_legislation_code," and it is the only public interface the package exposes.

Because the function's parameter purity is declared with PRAGMA RESTRICT_REFERENCES (..., WNDS, RNPS, WNPS), the function writes no database state (WNDS), reads no package state (RNPS), and writes no package state (WNPS). This purity guarantees that the function is safe to invoke directly from SQL statements, including those executed remotely, rather than only from PL/SQL blocks. Oracle's documentation explicitly notes this intent — the purity level "allows remote call from sql statement."

Tables Accessed

The function resolves the legislation code by reading Oracle Application Object Library profile option data, accessed through APPS synonyms:

• FND_PROFILE_OPTIONS — the profile option definition table, consulted to identify the relevant profile option that carries the legislation context.
• FND_PROFILE_OPTION_VALUES — the profile option values table, queried to obtain the effective value for the supplied responsibility.

Both tables are read-only for this package; no INSERT, UPDATE, or DELETE operations are performed, consistent with the declared purity level. ETRM records no other table dependencies drawn through APPS synonyms.

Usage Notes

Custom and Oracle code typically invokes PER_ITS_PKG.RETURN_LEGISLATION_CODE when it needs to branch behavior by legislation without knowing which Business Group a responsibility maps to. Representative callers include:

• Custom PL/SQL in HRMS extensions that must apply legislation-specific validation or defaulting logic.
• Oracle Forms-based HRMS screens or their supporting libraries that require the current legislation at form startup or block initialization.
• SQL-based reports and views, which are possible specifically because the function is declared with the required purity pragmas.
• Concurrent programs or interfaces that process data per Business Group.

ETRM records no packages that reference PER_ITS_PKG within the tracked dependency set, which is unsurprising given its narrow utility. Because the derivation depends on FND profile option values, results reflect the profile configuration effective for the responsibility supplied; if profile values change, subsequent calls return the updated legislation. The package is functionally stable across 12.1.1 and 12.2.2, and its long-standing header indicates no significant structural change since its original release.