Results for “xtr_dealer_codes”

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

Overview

The XTR_DEALER_CODES table resides in the XTR schema, which supports the Oracle E-Business Suite Treasury (ETRM) module. Its documented description, "Treasury users information," identifies it as the master reference table that defines the dealers authorized to conduct treasury transactions. Each row represents a single dealer entity and cross-references the corresponding application user through the USER_ID column, linking the dealer business identity to the Oracle EBS user account.

Functionally, XTR_DEALER_CODES operates as the central dealer registry against which deal capture, dealer limit enforcement, company authority rules, and hedge strategy definitions are validated. Because seven foreign keys across five dependent tables point into this object, it behaves as a reference or master entity rather than a transactional table. Under the heuristic Data Vault classification supplied in the metadata, XTR_DEALER_CODES is modeled as a hub, meaning it should be treated as the canonical source of the dealer business key (DEALER_CODE), with descriptive attributes potentially separated into a satellite in a formal Data Vault design. The table is marked VALID in the ETRM 12.2.2 data dictionary and contains nine documented columns.

Key Information Stored

The table's structure separates the dealer business identity from the EBS user identity, and the identifying columns are governed by two unique indexes:

• DEALER_CODE — The primary key of XTR_DEALER_CODES, backed by the unique index XTR_DEALDER_CODES_U1. This is the business key referenced by every foreign key documented for the table. (Note: the index name contains a documented spelling variation, "DEALDER.")
• USER_ID — The EBS application user identifier associated with the dealer, protected by the unique index XTR_DEALER_CODES_U2. This column is the join point to Oracle EBS user and responsibility security models and provides a second business-key candidate.
• DEALER_USER_NAME — The user-facing dealer name or login-style identifier presented in treasury forms and reports.
• ALLOW_OVERRIDE — A control flag determining whether the dealer may override standard limit or authority validation during deal entry.
• CREATED_BY / CREATED_ON — Standard Oracle EBS audit columns recording the creating user and creation timestamp.
• UPDATED_BY / UPDATED_ON — Standard audit columns recording the most recent modifying user and modification timestamp.
• AUDIT_INDICATOR — Audit-related classification attribute used by the EBS audit framework.

The surrogate primary key is therefore DEALER_CODE, with DEALER_CODE and USER_ID both functioning as documented business-key candidates through their unique indexes.

Common Use Cases and Queries

Typical treasury reporting and validation scenarios include resolving dealer codes to user names, auditing dealer-to-user assignments, and identifying dealers permitted to override limits. A basic lookup joins the dealer registry to dealer limit records:

• Dealer authority review: SELECT d.DEALER_CODE, d.DEALER_USER_NAME, a.COMPANY_CODE FROM XTR_DEALER_CODES d, XTR_COMPANY_AUTHORITIES a WHERE d.DEALER_CODE = a.DEALER_CODE;
• Dealer limit exposure: SELECT l.DEALER_CODE, l.* FROM XTR_DEALER_LIMITS l, XTR_DEALER_CODES d WHERE l.DEALER_CODE = d.DEALER_CODE AND d.ALLOW_OVERRIDE = 'Y';
• Deal activity by dealer: SELECT t.DEALER_CODE, d.DEALER_USER_NAME, COUNT(*) FROM XTR_DEALS t, XTR_DEALER_CODES d WHERE t.DEALER_CODE = d.DEALER_CODE GROUP BY t.DEALER_CODE, d.DEALER_USER_NAME;
• Hedge strategy dealer attribution: join XTR_HEDGE_STRATEGIES or XTR_HEDGE_ATTRIBUTES on PROS_DEALER_CODE or RETRO_DEALER_CODE to trace prospective and retrospective dealer responsibility in hedge programs.

These patterns are also useful for user provisioning reconciliation between FND_USER and dealer records via USER_ID.

Related Objects

The following tables reference XTR_DEALER_CODES through documented foreign keys, using DEALER_CODE (and the two hedge dealer columns) as the join column:

• XTR_DEALS — references XTR_DEALER_CODES.DEALER_CODE via XTR_DEALS.DEALER_CODE, recording the executing dealer on each treasury deal.
• XTR_DEALER_LIMITS — references DEALER_CODE, defining exposure limits per dealer.
• XTR_COMPANY_AUTHORITIES — references DEALER_CODE, controlling which companies a dealer may transact for.
• XTR_HEDGE_ATTRIBUTES — references DEALER_CODE twice, through PROS_DEALER_CODE and RETRO_DEALER_CODE.
• XTR_HEDGE_STRATEGIES — references DEALER_CODE twice, through PROS_DEALER_CODE and RETRO_DEALER_CODE.

Together these dependencies confirm XTR_DEALER_CODES as a foundational hub within the XTR treasury schema, and any change to DEALER_CODE values must respect all seven documented foreign key constraints.