Results for “xnp_sv_soa_jn”

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

Overview

XNP_SV_SOA_JN is the audit journalling table that Oracle E-Business Suite maintains for the XNP_SV_SOA base table within the XNP – Number Portability product. Oracle's journaling architecture pairs every transactional table with a shadow "_JN" table whose purpose is to capture a before/after image of each row affected by an INSERT, UPDATE, or DELETE. As a result, XNP_SV_SOA_JN preserves the full historical state of Service Order Administration (SOA) records used in local number portability processing, including subscription telephone numbers, donor and recipient service providers, and the many operational timestamps that drive porting workflows.

The table resides in the XNP schema and is documented as VALID in both ETRM 12.1.1 and 12.2.2. Its physical schema contains 105 columns. The implementation is entirely invisible to end users: journal rows are written by Oracle's journaling triggers and are normally consumed only by audit, reconciliation, and diagnostic processes. No Oracle Forms or concurrent program writes to this table directly.

Mined relationship data classifies XNP_SV_SOA_JN as a link object in a Data Vault heuristic model, since the table sits between XNP_SV_SOA and a network of business entities (service providers, routing numbers) through foreign keys. This is a modeling suggestion rather than an Oracle-declared classification; the table is fundamentally a historical satellite of XNP_SV_SOA.

Key Information Stored

The journal composite primary key XNP_SV_SOA_JN_PK consists of SV_SOA_ID, JN_ORACLE_USER, JN_DATETIME, and JN_OPERATION. SV_SOA_ID is the surrogate key inherited from XNP_SV_SOA and identifies the business record affected. The remaining three columns are audit metadata: the database user responsible, the timestamp of the change, and the operation type (INSERT, UPDATE, or DELETE). Additional journaling columns include JN_NOTES, JN_APPLN, and JN_SESSION, which record application context and session information.

Business-key content is carried forward from the base table, most importantly SUBSCRIPTION_TN (the telephone number being ported) together with OBJECT_REFERENCE and SUBSCRIPTION_TYPE. Service provider associations are held in DONOR_SP_ID, RECIPIENT_SP_ID, MEDIATOR_SP_ID, CREATED_BY_SP_ID, and CHANGED_BY_SP_ID, each of which is a foreign key to XNP_SERVICE_PROVIDERS. ROUTING_NUMBER_ID links to XNP_ROUTING_NUMBERS. Workflow state is captured by STATUS_TYPE_CODE, PREV_STATUS_TYPE_CODE, and PTO_FLAG, while lifecycle dates such as ACTIVATION_DUE_DATE, NEW_SP_DUE_DATE, OLD_SP_CUTOFF_DUE_DATE, PORTED_DATE-equivalents, and STATUS_CHANGE_DATE define the porting schedule. Standard EBS audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) as well as SECURITY_GROUP_ID and twenty ATTRIBUTE flex columns are also populated.

Common Use Cases and Queries

Because journal data is only useful in a historical sense, queries typically filter on SV_SOA_ID or on the journal timestamps. A representative pattern retrieves the full change history of a single subscription order:

• SELECT JN_OPERATION, JN_ORACLE_USER, JN_DATETIME, STATUS_TYPE_CODE, PREV_STATUS_TYPE_CODE FROM XNP_SV_SOA_JN WHERE SV_SOA_ID = :id ORDER BY JN_DATETIME;
• Reconciling a disputed porting event by comparing current XNP_SV_SOA values against the journaled image from a specific date range.
• Auditing which database user modified a service order during a compliance review.
• Reporting on donor and recipient service provider transitions over time, joining the journal to XNP_SERVICE_PROVIDERS by the relevant SP_ID column.
• Detecting bulk changes for support escalation by grouping on JN_ORACLE_USER and JN_OPERATION.

Performance consideration: XNP_SV_SOA_JN has no secondary indexes documented beyond the primary key, so ad hoc reporting should be limited to narrow date windows or specific SV_SOA_ID values.

Related Objects

The following objects are the most significant neighbors of XNP_SV_SOA_JN based on the documented foreign keys:

• XNP_SV_SOA — the base table whose rows this journal mirrors; SV_SOA_ID is the join key.
• XNP_SERVICE_PROVIDERS — referenced five times via CHANGED_BY_SP_ID, CREATED_BY_SP_ID, DONOR_SP_ID, RECIPIENT_SP_ID, and MEDIATOR_SP_ID.
• XNP_ROUTING_NUMBERS — referenced through ROUTING_NUMBER_ID.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID, enforcing multi-org style security on the record.
• FND_USER / FND_LOGINS — implicit dependencies for interpreting JN_ORACLE_USER and JN_SESSION values.

Together these relationships make XNP_SV_SOA_JN a central historical link in the number portability data model, bridging subscription orders, service providers, and routing infrastructure.