Search Results cn_sca_lines_interface_n1

Overview

CN.CN_SCA_LINES_INTERFACE_ALL is a transactional interface table in the Oracle E-Business Suite Channel Revenue Management (CN) module. It functions as the detail (child) table to CN_SCA_HEADERS_INTERFACE_ALL, storing the individual resources and their associated roles for each sales compensation or channel transaction staged in the parent header interface. The table is owned by the CN schema and resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. Records are typically accessible through the CN_SCA_LINES_INTERFACE_ALL and CN_SCA_LINES_INTERFACE synonyms/views registered under the APPS schema, which Oracle describes as inheriting the base structure of the underlying CN table.

From a data vault modeling perspective, the metadata classifies this object as standalone. This suggests it is best modeled as an independent satellite-like structure that does not participate in an enforced hub-and-link pattern at the database level, although the SCA_LINES_INTERFACE_ID and ORG_ID pair effectively behaves as a unique business key.

Key Information Stored

The table records the line-level detail that complements the parent header interface. The most significant columns are:

• SCA_LINES_INTERFACE_ID – Surrogate unique identifier for each interface line record.
• SCA_HEADERS_INTERFACE_ID – Foreign key to CN_SCA_HEADERS_INTERFACE_ALL, establishing the master-detail relationship.
• RESOURCE_ID – Identifier of the resource (for example, a salesperson or channel participant) associated with the line.
• ROLE_ID – Identifier of the role the resource plays within the transaction (for example, primary or secondary salesrep).
• SOURCE_TRX_ID – The source transaction identifier; in the OIC (Oracle Incentive Compensation) context this holds the COMM_LINE_API_ID from CN_COMM_LINES_API.
• ORG_ID – Operating unit / organization identifier used for multi-org security.
• OBJECT_VERSION_NUMBER – Optimistic locking column supporting concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – Standard WHO audit columns.
• SECURITY_GROUP_ID – Legacy security group identifier, foreign keyed to FND_SECURITY_GROUPS.

The unique business-key candidate is defined by the composite index CN_SCA_LINES_INTERFACE_U1 on (SCA_LINES_INTERFACE_ID, ORG_ID), which the user search referenced. A non-unique index, CN_SCA_LINES_INTERFACE_N1, supports retrieval by SCA_HEADERS_INTERFACE_ID. The surrogate primary key remains SCA_LINES_INTERFACE_ID; the unique index adds ORG_ID to enforce per-operating-unit uniqueness.

Common Use Cases and Queries

This table is primarily accessed during the interface upload and validation cycle for sales compensation and channel transactions. Typical scenarios include:

• Validating staged interface lines before they are processed into their final base tables.
• Diagnosing missing or duplicate resource-role assignments by joining to the header interface.
• Reconciling the interface record back to its source transaction via SOURCE_TRX_ID.
• Reporting on resources and roles associated with a given header transaction.

A representative query joining the parent header table is:

SELECT l.SCA_LINES_INTERFACE_ID,
       l.RESOURCE_ID,
       l.ROLE_ID,
       l.SOURCE_TRX_ID,
       l.ORG_ID
FROM   CN.CN_SCA_LINES_INTERFACE_ALL l,
       CN.CN_SCA_HEADERS_INTERFACE_ALL h
WHERE  l.SCA_HEADERS_INTERFACE_ID = h.SCA_HEADERS_INTERFACE_ID
AND    l.ORG_ID = :org_id;

A lookup by business key uses the unique index:

SELECT *
FROM   CN.CN_SCA_LINES_INTERFACE_ALL
WHERE  SCA_LINES_INTERFACE_ID = :line_id
AND    ORG_ID = :org_id;

Related Objects

The most significant related objects are:

• CN.CN_SCA_HEADERS_INTERFACE_ALL – Parent master table; joined via SCA_HEADERS_INTERFACE_ID.
• CN.CN_COMM_LINES_API – Source of the transaction identifier held in SOURCE_TRX_ID for OIC integration.
• IGI_DOS_TRX_SOURCES – Referenced through the SOURCE_TRX_ID foreign key relationship.
• FND_SECURITY_GROUPS – Referenced through SECURITY_GROUP_ID.
• APPS.CN_SCA_LINES_INTERFACE and APPS.CN_SCA_LINES_INTERFACE_ALL – Synonym/views through which applications and reports access the base CN table.

These dependencies confirm the table's role as a staging detail object tightly coupled to its header interface and to the underlying compensation transaction data.