Search Results per_security_profile_id

Overview

APPS.PER_DELETE_UNWANTED_SEC_PROF is a PL/SQL package body in the Oracle E-Business Suite Human Resources (PER) security module. Its business purpose is to identify and purge security profiles that are no longer referenced anywhere in the EBS instance, thereby reducing the volume of orphaned rows in the security profile tables. The package header revision captured for 12.1.1/12.2.2 is perdelsp.pkb 120.1.12020000.1, dated 2012/07/04, confirming it is shipped with the 12.2.2 code line.

The package operates in two distinct modes. A report mode (p_report = 'Y') lists unused security profiles without modifying data, and a maintenance mode (p_report = 'N') removes them, either in bulk or by name. This is the standard "cleanup" utility used by HR administrators who accumulate obsolete security profiles after reorganizations, payroll definition changes, or the retirement of responsibilities and users.

The user search term per_security_profile_id corresponds directly to the primary key column of PER_SECURITY_PROFILES, exposed in this package as the cursor variable l_security_profile_id, which is typed with the %TYPE anchor to that column.

Key Procedures and Functions

• CONCURRENT_PROCESS — the entry point invoked by the concurrent program. It accepts the report/deletion flag, an all-profiles deletion switch, and an optional security profile name. It validates the supplied profile name through the csr_security_prof_name cursor (selecting security_profile_id from per_security_profiles by security_profile_name) and dispatches control to the remaining procedures.
• SHOW_UNWANTED_SEC_PROF — produces the report listing security profiles that are candidates for deletion.
• CHECK_UNWANTED_SEC_PROF — performs the reference check that determines whether a given security profile is still in use.
• DEL_SEC_PROF — deletes a single, named unused security profile.
• STRONG_DELETE_SEC_PROF — performs a forced deletion for a specific profile.
• DEL_ALL_UNWANTED_SEC_PROF — bulk deletes every profile identified as unused. This procedure is the target of the p_del_all_sec_profile = 'Y' branch in CONCURRENT_PROCESS and is passed the strong-delete flag, which is initialised to 'Y' in the driver routine.

Tables Accessed

The package reads the definitional tables PER_SECURITY_PROFILES and PER_SEC_PROFILE_ASSIGNMENTS, and the reference tables FND_PROFILE_OPTIONS, FND_PROFILE_OPTION_VALUES, FND_RESPONSIBILITY and FND_USER to determine whether a profile is still attached to application-level or user-level constructs. For HRMS-specific membership, it queries PER_PERSON_LIST, PER_PERSON_LIST_CHANGES, PER_ASSIGNMENT_LIST, PER_POSITION_LIST, PER_ORGANIZATION_LIST, PER_SECURITY_ORGANIZATIONS and PER_SECURITY_USERS. Payroll-related membership is resolved through PAY_PAYROLL_LIST and PAY_SECURITY_PAYROLLS. Deletion therefore cascades logically across all these association tables once no referencing row is found.

Usage Notes

The package is not referenced by any other PL/SQL package, so it is invoked exclusively through its concurrent program registration in the Human Resources responsibility. Administrators first run it with the report option to review candidates, then resubmit with the deletion option enabled. Because the driver defaults the strong-delete flag to 'Y' and the bulk-delete branch acts on all qualifying profiles, the deletion run is destructive and irreversible; a validated backup of the affected profile tables should be taken beforehand. Direct custom calls to CONCURRENT_PROCESS are possible but uncommon, since the concurrent manager supplies the standard errbuf and retcode output parameters.