Search Results oe_sales_credits_u1

Overview

ONT.OE_SALES_CREDITS is a transactional table in the Oracle E-Business Suite Order Management (ONT) schema that stores sales credit allocation information for order headers and order lines. It records which salespersons receive credit for a given order or line, and the percentage of credit assigned to each. Because Orders Management supports multi-level sales credit assignment, a single order header or line may have multiple rows in this table — one per salesperson — each with its own PERCENT value. The table is owned by the ONT schema and resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10; its indexes occupy APPS_TS_TX_IDX.

From a data-modeling perspective, the heuristic Data Vault classification for this object is link. It sits between the order header/line entities and the sales representative/credit-type entities, and resolves many-to-many relationships between orders and the salespeople credited to them. The classification is a modeling suggestion inferred from the foreign-key structure rather than a formal ETRM designation.

Key Information Stored

The table contains 34 documented columns. The most important ones follow.

• SALES_CREDIT_ID — System-generated surrogate primary key. It is also the single column of the unique index OE_SALES_CREDITS_U1, which the user searched for; that index is the authoritative business-key candidate documented for this table.
• HEADER_ID — Foreign key to OE_ORDER_HEADERS_ALL identifying the order header to which the credit applies. Indexed non-uniquely by OE_SALES_CREDITS_N1.
• LINE_ID — Foreign key to OE_ORDER_LINES_ALL identifying the order line, when credit is attributed at line level. Indexed non-uniquely by OE_SALES_CREDITS_N2.
• SALESREP_ID — The salesperson receiving the credit; maps to RA_SALESREPS_ALL.
• PERCENT — The percentage of sales credit assigned to that salesperson.
• SALES_CREDIT_TYPE_ID — Identifies the credit type, referencing SO_SALES_CREDIT_TYPES_115 (for example, quota, revenue, or non-revenue credit).
• SALES_GROUP_ID and SALES_GROUP_UPDATED_FLAG — Support sales group assignment and change tracking.
• ORIG_SYS_CREDIT_REF — Original system reference, used in data migration and integration scenarios.
• WH_UPDATE_DATE — Warehouse update date used by the EBS data warehouse/collection programs; indexed non-uniquely by OE_SALES_CREDITS_N3.
• DW_UPDATE_ADVICE_FLAG — Flag advising the warehouse of updates requiring propagation.
• LOCK_CONTROL — Concurrency control column.
• INST_ID — Multi-organization / instance identifier.
• Standard Who columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.
• Descriptive flexfield columns — CONTEXT plus ATTRIBUTE1 through ATTRIBUTE15 (VARCHAR2(240) each; CONTEXT is VARCHAR2(30)).

Common Use Cases and Queries

Typical reporting needs include sales-credit split analysis, quota-credit rollups by salesperson, and reconciliation of credits between EBS and downstream warehouse extracts.

• Retrieve all credits for an order header:

  SELECT sales_credit_id, salesrep_id, percent, sales_credit_type_id FROM oe_sales_credits WHERE header_id = :p_header_id;

• Retrieve credits at line level:

  SELECT line_id, salesrep_id, percent FROM oe_sales_credits WHERE line_id = :p_line_id;

• Validate that credits sum to 100 per header:

  SELECT header_id, SUM(percent) FROM oe_sales_credits GROUP BY header_id HAVING SUM(percent) <> 100;

• Locate rows pending warehouse collection:

  SELECT * FROM oe_sales_credits WHERE wh_update_date > :last_run_date;

• Direct lookup by primary key uses the OE_SALES_CREDITS_U1 unique index on SALES_CREDIT_ID, while joins on HEADER_ID and LINE_ID benefit from the N1 and N2 indexes respectively.

Related Objects

• OE_ORDER_HEADERS_ALL — joined on OE_SALES_CREDITS.HEADER_ID = OE_ORDER_HEADERS_ALL.HEADER_ID.
• OE_ORDER_LINES_ALL — joined on OE_SALES_CREDITS.LINE_ID = OE_ORDER_LINES_ALL.LINE_ID.
• RA_SALESREPS_ALL — joined on OE_SALES_CREDITS.SALESREP_ID = RA_SALESREPS_ALL.SALESREP_ID.
• SO_SALES_CREDIT_TYPES_115 — joined on OE_SALES_CREDITS.SALES_CREDIT_TYPE_ID for credit-type descriptions.
• OE_SALES_CREDITS_PK — the primary key constraint on SALES_CREDIT_ID.
• OE_SALES_CREDITS_U1, N1, N2, N3 — the supporting unique and non-unique indexes in APPS_TS_TX_IDX.

Applications should generally maintain this table through the Order Management sales-credit APIs and forms rather than by direct DML, since credit rows must remain consistent with order header and line records and with the warehouse update columns.