Results for “xnp_sv_audits”

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

Overview

XNP_SV_AUDITS is a transactional table in the Oracle E-Business Suite Number Portability (XNP) module, owned by the XNP schema. It stores all subscription audits issued from a service provider, capturing the results of number-portability audits performed against telephone number ranges provisioned by or associated with service providers. The table records audit identity, audit type, audited number ranges, status, and the specific number-attribute checks applied (routing number, CNAM, ISVM, LIDB, CLASS, WSMSC, and RN flags). It also holds creation and update audit columns and a security group reference for multi-organization data segregation.

Under the heuristic Data Vault classification supplied in the metadata, XNP_SV_AUDITS is satellite-leaning. This is consistent with its structure: a surrogate primary key (SV_AUDIT_ID), descriptive and flag-based attributes, and foreign keys to dimension-like parents (service providers) rather than to other transactional hubs. Modelers may therefore treat it as a source of descriptive audit detail attached to service-provider and audit business keys.

The table is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2 environments, with the physical schema in 12.2.2 containing 26 columns under the XNP owner.

Key Information Stored

The surrogate primary key is SV_AUDIT_ID, defined by the XNP_SV_AUDITS_PK constraint. Three unique indexes document business-key candidates:

• XNP_SV_AUDITS_U1 on SV_AUDIT_ID
• XNP_SV_AUDITS_U2 on OBJECT_REFERENCE, the externally meaningful audit reference identifier
• XNP_SV_AUDITS_U3 on SP_ID and STARTING_NUMBER, defining audit uniqueness per service provider and starting number

Most significant descriptive columns include:

• SP_ID – the service provider that issued the subscription audit.
• FOR_SP_ID – the service provider the audit applies to, which may differ from the issuing provider.
• AUDIT_NAME and AUDIT_TYPE – the audit's display name and categorization.
• STATUS_CODE – lifecycle status of the audit.
• STARTING_NUMBER and ENDING_NUMBER – the telephone number range under audit.
• NUMBER_OF_TN_AUDITED – count of telephone numbers actually audited.
• ACTIVATION_START_DATE and ACTIVATION_END_DATE – activation window used to scope the audit.
• ROUTING_NUMBER_FLAG, CNAM_FLAG, ISVM_FLAG, LIDB_FLAG, CLASS_FLAG, WSMSC_FLAG, RN_FLAG – attribute-level audit indicators.
• NOTES – free-form audit commentary.
• SECURITY_GROUP_ID – multi-organization security group reference.
• LAST_UPDATE_DATE – standard audit/timestamp column.

Common Use Cases and Queries

XNP_SV_AUDITS supports operational and reconciliation reporting for number portability compliance. Typical scenarios include listing open or completed audits for a provider, measuring audit coverage per number range, and tracing discrepancies or SMS delivery failures back to an audit.

• Audits by provider: filter on SP_ID or FOR_SP_ID and STATUS_CODE to report active or historical audits.
• Range-level reporting: query STARTING_NUMBER, ENDING_NUMBER, and NUMBER_OF_TN_AUDITED to summarize audited volume.
• Discrepancy drill-down: join XNP_AUDIT_DISCREPANCIES on AUDIT_ID = SV_AUDIT_ID to detail failed checks.
• Notification-failure analysis: join XNP_AUDIT_SMS_FAILURES on AUDIT_ID = SV_AUDIT_ID to identify audits whose notifications failed.
• Attribute-flag analysis: aggregate the flag columns to quantify which number attributes (CNAM, LIDB, CLASS, etc.) drive discrepancy volume.

A representative query pattern joins the provider dimension and discrepancy detail on the documented keys:

SELECT a.SV_AUDIT_ID, a.OBJECT_REFERENCE, a.AUDIT_NAME, a.STATUS_CODE, a.STARTING_NUMBER, a.ENDING_NUMBER, a.NUMBER_OF_TN_AUDITED FROM XNP.XNP_SV_AUDITS a WHERE a.SP_ID = :p_sp_id AND a.STATUS_CODE = :p_status ORDER BY a.STARTING_NUMBER;

Related Objects

The FK relationships documented in the ETRM metadata identify the primary dependencies of XNP_SV_AUDITS:

• XNP_SERVICE_PROVIDERS – referenced twice, via XNP_SV_AUDITS.SP_ID and XNP_SV_AUDITS.FOR_SP_ID. This is the primary parent dimension for provider identification.
• FND_SECURITY_GROUPS – referenced via SECURITY_GROUP_ID for multi-organization access control.
• XNP_AUDIT_DISCREPANCIES – child table referencing XNP_SV_AUDITS through AUDIT_ID; stores individual discrepancies discovered by an audit.
• XNP_AUDIT_SMS_FAILURES – child table referencing XNP_SV_AUDITS through AUDIT_ID; stores SMS notification failures associated with an audit.

These four related tables form the immediate dependency set. XNP_SERVICE_PROVIDERS supplies provider context; XNP_AUDIT_DISCREPANCIES and XNP_AUDIT_SMS_FAILURES provide the detail rows that make XNP_SV_AUDITS the parent audit header for reporting and reconciliation in the XNP module.