Results for “xnp_audit_sms_failures”

24 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The XNP.XNP_AUDIT_SMS_FAILURES table is a transactional table within the Oracle E-Business Suite Number Portability (XNP) module. It stores the list of service providers (SMS carriers) that failed to return the requested audit information during an audit cycle. This list is explicitly optional in an audit result set: records appear only when one or more service providers did not respond successfully. The table therefore serves as an exception log associated with audit activity, allowing operations and compliance teams to identify non-responsive carriers and initiate follow-up.

The object is documented as VALID in the XNP schema and carries ten physical columns in the ETRM 12.2.2 schema. From a Data Vault modeling perspective, the mined FP structure suggests this table behaves as a link — it connects audits (via AUDIT_ID) to service providers (via SP_ID), with FAILURE_DATE acting as a degenerate/effectivity attribute. This is a heuristic classification and should be treated as a modeling suggestion rather than a mandated design.

Key Information Stored

The business-key candidate is the composite unique index XNP_AUDIT_SMS_FAILURES_U2 on (AUDIT_ID, SP_ID, FAILURE_DATE). This establishes that a given provider can appear at most once per audit per failure date, distinguishing the business identity from the surrogate SMS_FAILURE_ID.

Common Use Cases and Queries

Typical uses revolve around audit exception reporting and carrier follow-up. A simple query to list failures for a specific audit joins back to the service provider master:

SELECT f.sms_failure_id, f.audit_id, f.sp_id, s.sp_name, f.failure_date
FROM xnp.xnp_audit_sms_failures f, xnp.xnp_service_providers s
WHERE f.sp_id = s.sp_id
AND f.audit_id = :audit_id
ORDER BY f.failure_date;

Reporting scenarios include: identifying the most frequently failing carriers over a period, measuring audit response timeliness versus FAILURE_DATE, and feeding a remediation workflow that tracks whether a failed provider later supplied data. Because the table links to XNP_SV_AUDITS, aggregation by audit run is straightforward and supports SLA dashboards for number-portability audit operations.

Related Objects

  • XNP_SV_AUDITS — Parent audit table; joined via XNP_AUDIT_SMS_FAILURES.AUDIT_ID. Provides the audit context (portability/audit request) for each recorded failure.
  • XNP_SERVICE_PROVIDERS — Service provider master; joined via XNP_AUDIT_SMS_FAILURES.SP_ID. Supplies carrier names and identity attributes for reporting.
  • FND_SECURITY_GROUPS — Referenced via SECURITY_GROUP_ID; governs row-level security visibility consistent with other XNP tables.
  • XNP_AUDIT_SMS_FAILURES_PK / XNP_AUDIT_SMS_FAILURES_U1 / XNP_AUDIT_SMS_FAILURES_U2 — Primary and unique constraints enforcing the surrogate and business keys.

Understanding these relationships enables correct joins and reliable exception reporting across the XNP Number Portability audit process.