Results for “as_dossier_reps_unionall_v”

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

Overview

The view AS_DOSSIER_REPS_UNIONALL_V belongs to the Oracle EBS AS – Sales Foundation product family and is documented under ETRM 12.2.2 (with applicability to 12.1.1). Its stated purpose is to provide a union all view of order history sales representatives. In practice, this view consolidates sales credit information recorded in the Oracle Order Management / Sales Foundation schema so that sales representatives associated with historical orders can be reported on through a single, query-friendly structure. This is significant because order-level sales credits and line-level sales credits are physically stored differently in the base tables, and a union-all construct is required to reconcile both into one result set.

The view is typically consumed by dossier and sales-performance reporting, quota attribution, and integrations that need a normalized list of (order header, sales representative, credit amount) tuples. The metadata notes it is not implemented in the current database, meaning the definition is supplied for reference and may exist in other environments or release levels.

Underlying Base Objects

Although the ETRM metadata lists no explicitly referenced base objects under “Referenced base objects: none documented”, the view text reveals the underlying Sales Foundation and Order Management tables that drive it:

• SO_SALES_CREDITS (SC) – stores sales credit assignments linking a header and/or line to a salesrep, credit type and percent.
• SO_SALES_CREDIT_TYPES / SO_SALES_CREDIT_TYPES_115 (SCT) – defines the credit type and its quota flag, used to characterize each credit.
• SO_LINES (LN, LN2) – the order lines; LN2 is self-joined on PARENT_LINE_ID to resolve model/configuration relationships. This is the object most closely tied to the “so_lines” search term.
• RA_SALESREPS (SR) – the sales representative master, supplying SALESREP_ID and NAME.

The union-all consists of two branches: the first handles credits tied to regular lines (including model parent lines), and the second handles header-level credits where SC.LINE_ID IS NULL, filtered by a NOT EXISTS clause against SO_SALES_CREDITS and SO_SALES_CREDIT_TYPES using QUOTA_FLAG. This prevents duplicate quota-flagged credits from being double-counted.

Key Columns

• HEADER_ID – the order header identifier, joining to OE_ORDER_HEADERS_ALL.
• HEADER_CREDITS – the credit amount attributed at header level (populated in the second union branch).
• LINE_CREDITS – the credit amount attributed at line level, computed from ordered quantity less cancelled quantity, selling price, service duration and credit percent (populated in the first branch).
• CREDIT_TYPE – the sales credit type name from SO_SALES_CREDIT_TYPES.
• SALESREP_ID – the sales representative identifier from RA_SALESREPS.
• SALESREP – the sales representative name.

Note the amount logic applies DECODE for ITEM_TYPE_CODE values such as ‘MODEL’ and ‘SERVICE’, multiplying by SC.PERCENT/100 so credits are prorated by the assigned credit percentage.

Common Use Cases and Queries

Typical scenarios include sales-credit reconciliation, quota analysis and dossier reporting by representative. A representative-level credit summary can be written as:

• SELECT salesrep_id, salesrep, SUM(NVL(line_credits,0) + NVL(header_credits,0)) credit_total FROM as_dossier_reps_unionall_v GROUP BY salesrep_id, salesrep;
• Filtering by order to inspect credited reps: SELECT header_id, salesrep, credit_type, line_credits, header_credits FROM as_dossier_reps_unionall_v WHERE header_id = :p_header_id;
• Joining the view to SO_LINES/OE_ORDER_HEADERS_ALL on HEADER_ID to enrich results with order number, customer and line detail.

Because the view resolves credits from multiple base tables into consistent columns, it is best treated as a read-only reporting object rather than a transactional source.