Search Results cn_sca_lines_interface_all




Overview
Explore in AI Chat

Overview

CN_SCA_LINES_INTERFACE_ALL is a staging (interface) table within the Oracle Incentive Compensation module (product code CN). It functions as the transactional detail layer that accompanies CN_SCA_HEADERS_INTERFACE_ALL: while the header interface table carries the top-level transaction context, CN_SCA_LINES_INTERFACE_ALL holds the corresponding resources and their assigned roles for each of those transactions. In practical terms, the table is the inbound staging area through which external sales-credit and resource-assignment data is loaded before the Incentive Compensation collection and crediting processes validate and move it into the permanent transaction schema.

The object is owned by the CN schema and is classified as VALID in the documented ETRM 12.2.2 metadata. Its physical structure is intentionally narrow — thirteen columns — reflecting its role as a transient, high-volume loading surface. From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure suggests treating this object as a standalone entity: it participates in no downstream hub-style relationships within the interface layer and instead acts as an independent staging record keyed to its own surrogate identifier.

Key Information Stored

The table's contents center on the identity of the resource being credited and the role that resource plays in a given transaction.

  • SCA_LINES_INTERFACE_ID — the surrogate primary key for each staging row; together with ORG_ID it forms the unique business-key candidate enforced by index CN_SCA_LINES_INTERFACE_U1.
  • ORG_ID — the operating unit (multi-org) discriminator. It is the second component of the unique index, ensuring line uniqueness is scoped per organization.
  • SCA_HEADERS_INTERFACE_ID — the foreign-key link back to the parent header interface row; this is the join that associates each resource line with its transaction.
  • RESOURCE_ID — identifies the resource (typically a salesperson or other creditable party) being recorded against the transaction.
  • ROLE_ID — the role that the resource holds for that transaction (for example, the credit or attribution role). Together, RESOURCE_ID and ROLE_ID capture the substantive business content of each line.
  • SOURCE_TRX_ID — a reference to the originating source transaction, with a documented foreign key to IGI_DOS_TRX_SOURCES, supporting traceability back to the feeding system.
  • SECURITY_GROUP_ID — a foreign key to FND_SECURITY_GROUPS, used to restrict row visibility by security group.
  • OBJECT_VERSION_NUMBER — the optimistic-locking version counter used by the Oracle Applications framework to detect concurrent updates.
  • Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN capture the standard "who/when" WHO-column audit trail.

Note that the surrogate key (SCA_LINES_INTERFACE_ID) is distinct from the business-key candidate (SCA_LINES_INTERFACE_ID, ORG_ID) enforced by the unique index.

Common Use Cases and Queries

Because this is an interface table, its principal use cases are load validation and pre-collection reconciliation:

  • Verifying loaded lines for a run: join the line interface to the header interface to review all resources staged for a given transaction set.
  • Orphan detection: find line rows whose SCA_HEADERS_INTERFACE_ID has no matching header row, indicating a load error.
  • Role/resource audit: report the distribution of RESOURCE_ID and ROLE_ID values to confirm attribution data before the collection program processes the batch.
  • Source traceability: trace staged lines back to the feeding transaction via SOURCE_TRX_ID and IGI_DOS_TRX_SOURCES.

A representative query pattern joins the two interface tables on the header surrogate key:

SELECT l.sca_lines_interface_id, l.sca_headers_interface_id,
       l.resource_id, l.role_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 = :p_org_id;

Reporting is typically scoped by ORG_ID and CREATION_DATE, given the multi-org and time-based nature of interface loads.

Related Objects

The following objects are most significant in relation to CN_SCA_LINES_INTERFACE_ALL:

  • CN_SCA_HEADERS_INTERFACE_ALL — the parent header interface table; lines reference it via SCA_HEADERS_INTERFACE_ID.
  • IGI_DOS_TRX_SOURCES — referenced through SOURCE_TRX_ID, providing source-transaction traceability.
  • FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID for row-level security.
  • CN_SCA_LINES_ALL — the permanent transaction line table into which validated interface rows are ultimately collected and credited.
  • CN_SCA_LINES_INTERFACE_U1 — the unique index (SCA_LINES_INTERFACE_ID, ORG_ID) that guarantees line-level uniqueness and supports lookups.

These relationships confirm the table's role as a dependent staging surface feeding the core Incentive Compensation transaction model rather than a source of independent business data.