Search Results as_mc_sales_credits_den_pk

Overview

The AS_MC_SALES_CREDITS_DEN table is a core multi-currency data repository within the Oracle E-Business Suite (EBS) Sales Foundation module (AS). Its primary function is to store sales credit amounts that have been converted from their original transaction currency into various reporting currencies. This conversion supports global financial consolidation and reporting by utilizing both daily and pseudo period exchange rates. The table is owned by the OSM schema and is integral to the system's ability to present accurate, currency-normalized sales performance data across different geographical and organizational reporting structures in both EBS 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to track currency conversions for sales credits. While a full column list is not provided in the excerpt, the documented relationships and primary key reveal critical data points. The primary key, MC_SALES_CREDIT_ID, uniquely identifies each converted amount record. A fundamental column is SALES_CREDIT_ID, which is a foreign key linking back to the original sales credit in the AS_SALES_CREDITS_DENORM table. The table also stores details about the conversion process itself, including CONVERSION_TYPE, CONVERSION_PERIOD_TYPE, CONVERSION_PERIOD_SET_NAME, and CONVERSION_PERIOD_NAME. These columns define the rate type (e.g., Corporate, User), the period type for the rate (e.g., Daily, Period), and the specific accounting period used for the conversion, enabling auditability and flexibility in financial reporting.

Common Use Cases and Queries

This table is primarily accessed for consolidated sales reporting and commission calculations in multiple currencies. A typical use case involves generating a report of sales credits for a specific salesperson or territory, with all amounts expressed in a common reporting currency (e.g., EUR or USD) for management review. Another scenario is reconciling commission amounts calculated in local transaction currencies with the amounts reported in the corporate ledger. A common query pattern joins this table to the sales credit denormalized table to fetch both original and converted amounts.

SELECT scd.salesrep_id,
       scd.transaction_currency_code,
       mc.reporting_currency_code,
       scd.revenue_amount_raw,
       mc.converted_revenue_amount,
       mc.conversion_type,
       mc.conversion_date
FROM   as_sales_credits_denorm scd,
       as_mc_sales_credits_den mc
WHERE  mc.sales_credit_id = scd.sales_credit_id
AND    mc.reporting_currency_code = 'USD'
AND    scd.transaction_date BETWEEN :p_start_date AND :p_end_date;

Related Objects

The table maintains defined foreign key relationships with several other EBS objects, as documented in the ETRM metadata:

• AS_SALES_CREDITS_DENORM: This is the source table for the original sales credit data. The relationship is established via AS_MC_SALES_CREDITS_DEN.SALES_CREDIT_ID = AS_SALES_CREDITS_DENORM.SALES_CREDIT_ID.
• AS_MC_TYPE_MAPPINGS: This table validates and provides reference data for the conversion configuration. The join uses multiple columns: CONVERSION_PERIOD_TYPE, CONVERSION_TYPE, and CONVERSION_PERIOD_SET_NAME.
• GL_PERIODS: This General Ledger table validates the accounting period used for the currency conversion. The join is on CONVERSION_PERIOD_SET_NAME and CONVERSION_PERIOD_NAME.

The primary key constraint AS_MC_SALES_CREDITS_DEN_PK on MC_SALES_CREDIT_ID ensures the uniqueness of each multi-currency conversion record.