Search Results new_trx_type_id

Overview

The AR_TA_DEDN_RELATE view is a Receivables (AR) reporting object published within the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. It is defined as a "SINGLE_ORG" view, which identifies it as a multi-organization (multi-org) security view that filters data from a corresponding _ALL table down to the organization currently set in the user session. Its name links Trade Management / deduction-related processing ("TA" and "DEDN") with a "RELATE" function, indicating its purpose is to associate or reconcile deduction records against original and newly created transactions.

In practical terms, the view exposes Trade Management deduction relationship records alongside the sales representative and collector to whom each deduction has been assigned. This makes it relevant to deductions and claims workflows, in which a customer deduction on an invoice must be tracked, validated, and routed to the correct owner for resolution.

Underlying Base Objects

The view is defined over AR_TA_DEDN_RELATE_ALL, the multi-org base table holding the complete set of deduction relationship rows across all operating units. No additional base objects are documented in the ETRM metadata for 12.2.2; the view text shows a single-source SELECT with no joins.

The organization filter is enforced through a standard multi-org predicate applied to ORG_ID:

• The current organization is read from USERENV('CLIENT_INFO').
• A leading blank character is handled through SUBSTR and DECODE logic.
• Rows whose ORG_ID matches the session value are returned; a sentinel value of -99 is used when no organization is derivable.

Because the view is a true single-org view, queries against it automatically return only records for the operating unit currently selected by the user, and reporting that needs cross-org visibility must query the underlying _ALL table directly (subject to org security).

Key Columns

The view projects the full column list of the base table. Notable columns include:

• RECORD_TYPE — classifies the deduction relationship record.
• CUSTOMER_NUMBER and CUSTOMER_SITE_USE_ID — identify the customer and the specific site use associated with the deduction.
• ORIG_TRX_ID and NEW_TRX_ID — the original transaction from which the deduction arose and the new transaction created to resolve it.
• NEW_TRX_TYPE_ID — the transaction type of the resolution document.
• DMS_DEDUCTION_NUMBER, DMS_DEDUCTION_DATE, DMS_DEDUCTION_STATUS, DEDUCTION_AMOUNT — the deduction identifier, its date, workflow status, and monetary value.
• ASSIGNED_TO_SALESREP_ID — the sales representative responsible for the deduction record. This is the column most likely targeted by the search term "assigned_to_salesrep_id".
• ASSIGNED_TO_COLLECTOR_ID — the collections agent assigned to pursue the deduction.
• REQUEST_ID — the concurrent request that created or processed the row.
• ORG_ID and the standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN).

Common Use Cases and Queries

Typical uses include identifying which sales representatives own open deductions, measuring deduction exposure by representative or collector, and tracing deductions back to their originating and resolving transactions. A representative-oriented query might be:

SELECT dms_deduction_number,
       dms_deduction_date,
       dms_deduction_status,
       deduction_amount,
       assigned_to_salesrep_id,
       orig_trx_id,
       new_trx_id
FROM   ar_ta_dedn_relate
WHERE  assigned_to_salesrep_id = :salesrep_id
AND    dms_deduction_status = 'OPEN';

A status-summary query would group by deduction status and representative:

SELECT assigned_to_salesrep_id,
       dms_deduction_status,
       COUNT(*)          AS deduction_count,
       SUM(deduction_amount) AS total_amount
FROM   ar_ta_dedn_relate
GROUP  BY assigned_to_salesrep_id, dms_deduction_status;

In the documented 12.2.2 database the view is not implemented, so these statements are valid only in environments where the object has been deployed. Note also that the _ID columns store internal identifiers; joins to RA_SALESREPS_ALL, AR_COLLECTORS, or RA_CUSTOMER_TRX_ALL (as applicable) are required to present names and transaction numbers in reporting output.