Results for “status_phase”

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

Overview

The XNP_SV_SOA_VL view is an APPS-owned database object within the Oracle E-Business Suite XNP – Number Portability product. It presents porting information for a single telephone number, consolidating data from the Service Order Application (SOA) records maintained by the Number Portability module. The view serves as the reporting and integration layer through which porting transactions, service provider relationships, status conditions, and subscriber contact details are exposed to downstream consumers such as concurrent programs, OAF pages, and external provisioning interfaces.

The view is documented as VALID in the ETRM repository for releases 12.1.1 and 12.2.2. Its historic design note records that the predicate SVA.CREATED_BY_SP_ID = SPR_OWN.SP_ID was deliberately removed from the WHERE clause so that Service Bureau visibility is enforced automatically through Virtual Private Database (VPD) policies rather than an explicit join predicate. This architectural decision centralizes row-level security in the VPD layer and prevents leakage of cross-provider porting records.

Underlying Base Objects

The view is defined over the following documented base objects, exposed to APPS through synonyms:

• XNP_SV_SOA — the primary Service Order Application table that supplies the majority of columns, aliased as SVA in the view text.
• XNP_SV_STATUS_TYPES_B — the status type base table, aliased as STE_B, contributing PHASE_INDICATOR.
• XNP_SV_STATUS_TYPES_TL — the translated status type table, aliased as STE_TL, contributing DISPLAY_NAME and description attributes.
• XNP_ROUTING_NUMBERS — referenced for routing number identifiers.
• XNP_SERVICE_PROVIDERS — referenced for service provider context, including the donor and recipient provider relationships.

Because the view joins the SOA record to its status translation tables, consumers receive both the coded status (STATUS_TYPE_CODE) and the user-facing phase and display name in a single query, avoiding redundant lookups in custom reports.

Key Columns

The CHANGED_BY_SP_ID column, which is the column most directly associated with the user's search, identifies the service provider responsible for the most recent modification of the porting record. It is distinct from CREATED_BY_SP_ID, which records the originating provider, and from MEDIATOR_SP_ID, which identifies an intermediary provider in the porting chain.

• SV_SOA_ID — primary identifier of the service order application row.
• PORTING_ID — object reference linking to the porting transaction.
• SUBSCRIPTION_TN — the telephone number being ported.
• DONOR_SP_ID / RECIPIENT_SP_ID — the losing and gaining service providers.
• STATUS_TYPE_CODE, STATUS_PHASE, STATUS_DISPLAY_NAME — current status and its phase and translated name.
• ACTIVATION_DUE_DATE, NEW_SP_DUE_DATE, OLD_SP_DUE_DATE, DISCONNECT_DUE_DATE — scheduling dates governing the port.
• CONCURRENCE_FLAG, BLOCKED_FLAG, LOCKED_FLAG — process control indicators.
• CUSTOMER_ID, CUSTOMER_NAME, CONTACT_NAME, ADDRESS_LINE1/2, CITY, STATE, ZIP_CODE, COUNTRY — subscriber and contact attributes.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

The view is typically queried to audit porting activity by provider or to report on port orders approaching a due date.

• Identify all porting records last changed by a specific provider:
  SELECT subscription_tn, porting_id, status_display_name, changed_by_sp_id FROM xnp_sv_soa_vl WHERE changed_by_sp_id = :p_sp_id;
• List active ports for a telephone number with provider context:
  SELECT subscription_tn, donor_sp_id, recipient_sp_id, mediator_sp_id, created_by_sp_id, changed_by_sp_id, status_phase FROM xnp_sv_soa_vl WHERE subscription_tn = :p_tn;
• Report ports due for activation within a date range:
  SELECT porting_id, subscription_tn, activation_due_date, status_display_name FROM xnp_sv_soa_vl WHERE activation_due_date BETWEEN :p_from AND :p_to ORDER BY activation_due_date;

Results returned through these queries are constrained automatically by VPD policies, ensuring each service provider sees only the porting records it is entitled to access regardless of the column predicates applied.