Results for “cn_salesreps_api_all”

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

Overview

CN_SALESREPS_API_ALL is a table in the CN (Incentive Compensation) schema within Oracle E-Business Suite releases 12.1.1 and 12.2.2. The object is documented in the ETRM repository with the internal description "Not currently used," indicating that it is a retained but dormant interface table that Oracle no longer actively populates or references in the standard Incentive Compensation processing flow. Despite this designation, the table remains VALID in the data dictionary and participates in a defined foreign-key topology.

The table is named as an "_API_ALL" interface object, which in EBS convention denotes a staging or integration structure used to load sales representative records from external sources. Its physical schema at 12.2.2 contains 38 columns. Based on the documented foreign-key structure, a heuristic Data Vault classification of hub is proposed: the table exposes a single surrogate primary key, SALESREP_API_ID, and holds descriptive sales-representative attributes, while dependent transactional tables reference it through their SALESREP_ID columns. This positioning as a central reference point for sales-rep identity makes the hub classification a reasonable modeling suggestion, even though the table is not currently used.

Key Information Stored

The table is keyed by the surrogate primary key constraint CN_SALESREPS_API_PK on the column SALESREP_API_ID. A unique index, CN_SALESREPS_API_U1, spans SALESREP_API_ID and ORG_ID, making the combination a business-key candidate that enforces organization-level uniqueness of each sales-rep interface record.

The most significant columns include:

• SALESREP_API_ID — Surrogate primary key and foreign-key target for all downstream dependencies.
• ORG_ID — Operating unit identifier; part of the unique business key.
• NAME — Sales representative name.
• EMPLOYEE_NUMBER — HR employee number for the representative.
• PERSON_ID — Link to the underlying HR person record.
• START_PROCESSING_PERIOD / END_PROCESSING_PERIOD — Processing period range during which the rep is active in compensation calculations.
• START_DATE_ACTIVE / END_DATE_ACTIVE — Effective date bounds for the representative record.
• ASSIGN_TO_USER_NAME — FND user assigned to the representative.
• STATUS — Lifecycle or processing status of the interface row.
• COST_CENTER / CHARGE_TO_COST_CENTER — Cost-center attribution for compensation charges.
• CURRENCY_CODE — Currency applicable to the representative.
• EMAIL_ADDRESS — Contact address for the representative.
• LOADING_STATUS — Interface loading state indicator.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS for data security grouping.
• ATTRIBUTE1 through ATTRIBUTE15 and ATTRIBUTE_CATEGORY — Descriptive flexfield (DFF) columns for client-specific extensions.

Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — provide audit lineage.

Common Use Cases and Queries

Because the table is documented as not currently used, production queries should be treated as diagnostic or historical rather than operational. A typical verification query checks whether any interface rows were ever staged for an operating unit:

• SELECT salesrep_api_id, name, employee_number, org_id, status FROM cn.cn_salesreps_api_all WHERE org_id = :p_org_id;
• SELECT COUNT(*) FROM cn.cn_salesreps_api_all WHERE loading_status IS NOT NULL;
• SELECT s.* FROM cn.cn_salesreps_api_all s WHERE NOT EXISTS (SELECT 1 FROM cn.cn_commission_lines_all c WHERE c.credited_salesrep_id = s.salesrep_api_id);

Common scenarios include auditing orphan interface rows, reconciling sales representatives that never resolved into commission lines, and confirming that the interface was depopulated after upgrade. Reporting can join the table to CN_PROCESS_BATCHES_ALL or CN_PAYMENT_API_ALL on SALESREP_ID to trace historical representative attribution.

Related Objects

CN_SALESREPS_API_ALL is referenced by several dependent CN interface and transaction tables through their SALESREP_ID or CREDITED_SALESREP_ID columns:

• CN_COMMISSION_LINES_ALL — joins on CN_COMMISSION_LINES_ALL.CREDITED_SALESREP_ID = CN_SALESREPS_API_ALL.SALESREP_API_ID.
• CN_COMM_LINES_API_ALL — joins on CN_COMM_LINES_API_ALL.SALESREP_ID = CN_SALESREPS_API_ALL.SALESREP_API_ID.
• CN_PAYMENT_API_ALL — joins on CN_PAYMENT_API_ALL.SALESREP_ID = CN_SALESREPS_API_ALL.SALESREP_API_ID.
• CN_PROCESS_BATCHES_ALL — joins on CN_PROCESS_BATCHES_ALL.SALESREP_ID = CN_SALESREPS_API_ALL.SALESREP_API_ID.
• CN_WORKSHEET_BONUSES_ALL — joins on CN_WORKSHEET_BONUSES_ALL.SALESREP_ID = CN_SALESREPS_API_ALL.SALESREP_API_ID.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for security-group assignment.

Collectively, this relationship set confirms the hub-like position of the table as the referenced parent of five CN sub-ledger and interface objects plus one FND security object.