Search Results sms_failure_id




Overview
Explore in AI Chat

Overview

The XNP_AUDIT_SMS_FAILURES table is a supporting data structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, residing in the XNP (Oracle Communications Billing and Revenue Management) schema. Its primary function is to log instances where external service providers fail to supply requested audit information. This table acts as an optional audit trail, capturing failure events to facilitate troubleshooting and compliance reporting for service management audits. Notably, the provided ETRM documentation states this table is designated for future use and is not utilized in the current release at the time of documentation.

Key Information Stored

The table is centered on tracking audit failure events. The core columns include the SMS_FAILURE_ID, which serves as the unique primary key identifier for each failure record. The AUDIT_ID and SP_ID columns are foreign keys linking to the specific audit request and the non-responsive service provider, respectively. The FAILURE_DATE records the precise timestamp of the failure event. The table also incorporates standard EBS "Who" columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) for tracking record creation and modifications, along with a SECURITY_GROUP_ID for Virtual Private Database (VPD) support in multi-org hosted environments.

  • SMS_FAILURE_ID: The unique sequence-generated identifier for each failure log entry.
  • AUDIT_ID: References the parent audit request from the XNP_SV_AUDITS table.
  • SP_ID: References the specific service provider from the XNP_SERVICE_PROVIDERS table that failed to respond.
  • FAILURE_DATE: The critical timestamp indicating when the failure was recorded.

Common Use Cases and Queries

While documented as unused in the current release, its design supports specific analytical and operational scenarios in a live implementation. A primary use case would be generating compliance reports to identify service providers with a history of audit non-compliance. Administrators could run queries to analyze failure trends over time or isolate failures for a particular audit cycle. A typical diagnostic query would join to related master tables to provide contextual information.

Sample Query: List Recent Audit Failures with Provider Details
SELECT f.sms_failure_id,
f.audit_id,
sp.service_provider_name,
f.failure_date,
f.created_by
FROM xnp.xnp_audit_sms_failures f,
xnp.xnp_service_providers sp
WHERE f.sp_id = sp.sp_id
AND f.failure_date > SYSDATE - 30
ORDER BY f.failure_date DESC;

Related Objects

The table maintains defined foreign key relationships with two key master tables in the XNP schema, ensuring referential integrity for the logged data.

  • Primary Key: XNP_AUDIT_SMS_FAILURES_PK on column SMS_FAILURE_ID.
  • Foreign Key (AUDIT_ID): References the XNP_SV_AUDITS table. This links each failure record to a specific audit request instance.
  • Foreign Key (SP_ID): References the XNP_SERVICE_PROVIDERS table. This identifies the specific service provider entity that failed to provide the requested audit information.

The table is also indexed for performance on its unique constraints (SMS_FAILURE_ID, and the combination of AUDIT_ID, SP_ID, and FAILURE_DATE) and on the SP_ID column for non-unique lookups.