Search Results check_assigned_sec_profile




Overview
Explore in AI Chat

Overview

APPS.PER_SECURITY_PROFILES_PKG is the Oracle E-Business Suite 12.1.1 / 12.2.2 PL/SQL package that provides the DML and validation engine behind Oracle HRMS security profiles. A security profile is the core data structure that determines which people, assignments, organizations, positions, payrolls, applicants, candidates, and contacts a given Oracle Applications user is permitted to see inside the HRMS and related self-service products. The package implements the canonical Oracle Forms-style API pattern — Insert_Row, Lock_Row, Update_Row, and Delete_Row — against the PER_SECURITY_PROFILES entity, and supplements those with uniqueness, referential, and SQL-fragment validation routines. All procedures operate on the APPS schema and reference the underlying tables through APPS synonyms.

The package header is dated 2007/01/22 (peser01t.pkh 120.0.12000000.1), which places its most recent shipped revision at the 12.0 level; the same body is carried forward unchanged into 12.1.1 and 12.2.2, so behavior and signatures are identical on both releases. Custom code written against 12.1.1 therefore ports to 12.2.2 without modification.

Because the package is classified as OTHER in ETRM (not a formally published public API), Oracle does not guarantee its interface across patches, and customers extending it do so at their own risk. It is nonetheless the only supported programmatic route to create or maintain security profile rows, and it is referenced directly by two other shipped packages.

Key Procedures and Functions

  • INSERT_ROW — Creates a new row in PER_SECURITY_PROFILES and its _S (translation) child. It accepts the full security-profile attribute surface: business group, position, organization, position structure, organization structure, the various "view all" flags (employees, applicants, payrolls, positions, organizations, contingents/CWK, contacts, candidates), include-top-org and include-top-position flags, payroll include/exclude flag, reporting Oracle username, granted-user allowance, supervisor restriction level, custom restriction text, and the WHO columns (request_id, program_application_id, program_id, program_update_date).
  • LOCK_ROW — Locks the identified security profile row for update using the standard Forms optimistic-locking pattern, validating that the row has not been modified by another session before the caller proceeds to update or delete.
  • UPDATE_ROW — Applies modified attribute values to an existing security profile row after a successful LOCK_ROW.
  • DELETE_ROW — Removes a security profile row and its translations; guarded by PRE_DELETE_VALIDATION.
  • CHECK_UNIQUENESS — Enforces that the security profile name is unique within its business group, preventing duplicate profile definitions.
  • CHK_REPORTING_USERNAME_UNIQUE — Validates that a reporting Oracle username is not already associated with another security profile, protecting the "Restrict by Supervisor" / reporting-user feature from ambiguity.
  • PRE_DELETE_VALIDATION — Confirms that a profile is not still assigned to users before deletion.
  • CHECK_SQL_FRAGMENT — Validates the custom SQL fragment supplied when a profile uses custom restriction, guarding against malformed or unsafe dynamic SQL. It leverages DBMS_SQL and the ALL_TABLES data dictionary view for this purpose.
  • CHECK_ASSIGNED_SEC_PROFILE — Determines whether a given security profile is currently assigned to any user, typically called from PRE_DELETE_VALIDATION.

Tables Accessed

  • PER_SECURITY_PROFILES — the base table holding the security profile definition (the primary insert/update/delete target).
  • PER_SECURITY_PROFILES_S — the translated (language) child of the base table.
  • PER_SEC_PROFILE_ASSIGNMENTS — the assignment bridge between profiles and users; read by CHECK_ASSIGNED_SEC_PROFILE and PRE_DELETE_VALIDATION to block deletion of in-use profiles.
  • FND_PROFILE_OPTIONS / FND_PROFILE_OPTION_VALUES — the profile-option infrastructure used to persist security-profile selection against a user's login.
  • PER_PERSON_LIST — the person-list view consumed when evaluating named-person and supervisor-based restrictions.
  • ALL_TABLES / DBMS_SQL — referenced by CHECK_SQL_FRAGMENT to parse and validate custom SQL restriction text.

Usage Notes

The package is normally invoked from the Security Profile form (PERWSSCP) and from concurrent/process logic that mass-creates or copies security profiles, rather than being called directly by end users. Both INSERT_ROW and UPDATE_ROW rely on the WHO columns and should be called with the caller's request_id, program_application_id, program_id, and program_update_date so that the audit trail is properly populated.

The search trigger "SECURITY_PROFILE_ID = 0" is significant: PER_SECURITY_PROFILES.SECURITY_PROFILE_ID is generated by the package's sequence, so a value of 0 is not a valid persisted profile — it is the default/initial value of the X_Security_Profile_Id IN OUT NOCOPY parameter before INSERT_ROW assigns the sequence nextval. Developers searching for SECURITY_PROFILE_ID = 0 are therefore usually troubleshooting an uninitialized bind variable, a Forms block that has not yet executed INSERT_ROW, or a query against PER_SEC_PROFILE_ASSIGNMENTS in which a zero has been stored by a defect or a failed custom program. Correct diagnosis requires checking whether the row was ever committed; a genuine security profile always has a positive sequence-derived ID.