Search Results xnp_audit_sms_failures_uk1

Overview

The XNP_AUDIT_SMS_FAILURES table is a core data object within the Oracle E-Business Suite (EBS) Number Portability (XNP) module. It functions as an audit trail and exception management table, specifically designed to track service providers who fail to provide requested audit information during number portability processes. Its primary role is to support compliance and operational oversight by maintaining an optional list of failures within the broader audit results framework. This table is critical for identifying non-responsive or non-compliant service providers, enabling administrators to follow up on incomplete audit requests and ensure the integrity of the portability audit lifecycle in EBS versions 12.1.1 and 12.2.2.

Key Information Stored

The table stores a focused set of columns that capture the essential details of each audit failure event. The primary identifier, SMS_FAILURE_ID, is a unique sequence-generated key for each record. The AUDIT_ID column links the failure to a specific parent audit record in the XNP_SV_AUDITS table, establishing the audit context. The SP_ID column identifies the specific service provider that failed to respond, referencing the XNP_SERVICE_PROVIDERS table. Finally, the FAILURE_DATE column records the timestamp of the failure event. Together with the AUDIT_ID and SP_ID, this date forms a unique constraint (XNP_AUDIT_SMS_FAILURES_UK1), preventing duplicate failure entries for the same provider within the same audit.

Common Use Cases and Queries

The primary use case involves generating exception reports for audit process managers. These reports highlight service providers requiring escalation or manual follow-up due to their non-response. A common analytical query would join this table with related master data to produce a comprehensible report. For instance, to list all recent audit failures with provider details, a query might be: SELECT sp.SP_NAME, aud.AUDIT_NAME, fail.FAILURE_DATE FROM xnp_audit_sms_failures fail, xnp_service_providers sp, xnp_sv_audits aud WHERE fail.sp_id = sp.sp_id AND fail.audit_id = aud.audit_id AND fail.failure_date > SYSDATE - 7;. Another operational use case is to check if a specific provider has a history of audit failures before initiating a new audit cycle, which can be achieved by querying for records filtered by a particular SP_ID.

Related Objects

The XNP_AUDIT_SMS_FAILURES table has defined foreign key relationships with two key master tables in the XNP schema, as documented in the ETRM metadata.

• XNP_SV_AUDITS: Linked via the column XNP_AUDIT_SMS_FAILURES.AUDIT_ID. This relationship ties each failure record to its originating audit request, providing the broader audit context and details.
• XNP_SERVICE_PROVIDERS: Linked via the column XNP_AUDIT_SMS_FAILURES.SP_ID. This relationship provides the identity and master information for the service provider that failed to respond to the audit request.

These relationships are essential for constructing meaningful reports and ensuring referential integrity within the Number Portability audit data model.