GL Daily Rates Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

GL Daily Rates Upload ================== Use this upload to upload new or update existing GL Daily Exchange Rate Conversions. If exchange rates do not already exist for the entered data, they will be created. If exchange rates do already exist for the entered data, they will be updated.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Upload Mode, Default User Conversion Type, From Currency, To Currency, Conversion Type, From Date, To Date

Oracle EBS Tables Used

gl_daily_rates, gl_daily_conversion_types, fnd_user

Report Categories

Enginatics, Upload

Related Reports

GL Balance by Account Hierarchy, AP Invoices and Lines, GL Account Analysis, GL Journals (Drilldown) 11g, GL Journals (Drilldown), AR Receipt Register, AR Aging

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource	Link
Excel Example Output	Example
Blitz Report™ XML Import	GL_Daily_Rates_Upload.xml
SQL Source	GL Daily Rates Upload.sql
Full Report on Enginatics	www.enginatics.com/reports/gl-daily-rates-upload/

GL Daily Rates Upload - Case Study & Technical Analysis

Executive Summary

The GL Daily Rates Upload is a utility report and interface tool designed to facilitate the bulk upload or update of daily exchange rates into Oracle EBS. It streamlines the maintenance of currency rates, replacing manual data entry with an efficient Excel-based interface. This tool is vital for organizations that manage high volumes of currency pairs or require frequent rate updates from external treasury systems.

Business Use Cases

• Bulk Rate Maintenance: Enables the mass upload of monthly or daily rates from central treasury systems or external feeds (e.g., Bloomberg, Reuters) directly into Oracle GL.
• Correction of Errors: Allows for the rapid correction of incorrect rates across a date range by simply uploading the correct values, which overwrite the existing entries.
• New Currency Setup: Accelerates the initialization of historical rates when a new currency is introduced to the business.
• Month-End Close: Ensures all necessary period-end rates are populated quickly to allow for timely revaluation and translation processes.

Technical Analysis

Core Tables

• GL_DAILY_RATES_INTERFACE: The interface table where data is initially staged before being validated and imported.
• GL_DAILY_RATES: The final destination table for the validated exchange rates.
• GL_DAILY_CONVERSION_TYPES: Validates the conversion type provided in the upload.
• FND_CURRENCIES: Validates the currency codes.

Key Joins & Logic

• Interface Mechanism: This tool typically functions as a wrapper for the Oracle GL Currency API (GL_CURRENCY_API) or the standard open interface.
• Upsert Logic: The logic checks if a rate already exists for the given currency pair, date, and type.
  • If it exists, the tool performs an UPDATE to modify the rate.
  • If it does not exist, it performs an INSERT to create a new record.
• Validation: It enforces strict validation rules:
  • Currencies must be active and valid.
  • Conversion dates must be in valid open or future periods (depending on configuration).
  • No duplicate rates for the same key combination.

Key Parameters

• Upload Mode: Specifies whether to validate only or validate and load.
• From Currency / To Currency: The currency pair being loaded.
• Conversion Date: The date for which the rate applies.
• Conversion Type: The rate type (e.g., Corporate).
• Conversion Rate: The numerical exchange rate value.

---

Useful Links

• Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
• Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
• Oracle EBS Reporting Toolkits by Blitz Report™
• Blitz Report™ FAQ & Community Q&A
• Supply Chain Hub by Blitz Report™
• Blitz Report™ Customer Case Studies
• Oracle EBS Reporting Blog
• Oracle EBS Reporting Resource Centre

© Enginatics GmbH