How to print bills / invoices in multiple currency formats based on functional currency

Basic business need

Business organization might have requirement to serve  the purpose of presenting bills to global customers in their respective country currencies.

Assume that business headquarters is at Salalah, Sultanate of Oman. Sales team will raise bills in functional currency say Omani riyals.

Business organization has customers across gulf countries say United Arab Emirates and Qatar.

Customers will seek to present bills in UAE Dirhams and Qatari Riyals.

In this scenario, business has a challenge to convert product prices, line amount, and billing amount totals into cross currencies. In our case it should be presented to customers in AED/QAR to fulfill customer needs

Solution Design:

1. Create custom invoice/billing report as like BPA standard report

2. Create parameter - Bill print currency format - Assign currencies list of values

3. Based on user currency selection, bills amount will be automatically converted into foreign currencies based on general ledger daily rates and conversions.

4. Bills totals can be cross verified against functional currencies and can be issued to customer

Detailed Design

Currency conversion based on GL Daily rates

begin

select round(conversion_rate,5) daily_rate

 into l_rates

 from gl_daily_rates

 where from_currency = nvl(p_from_currency,l_currency_code)

 and to_currency = p_to_currency

 and trunc(conversion_date) = trunc(to_date(nvl(p_conversion_date,l_trx_date)))

 --and conversion_date = l_trx_date

 and conversion_type = 1000;

  exception

  when others then

  return 1;

  end;

How to arrive conversion date

It should be date of invoice bill on which it is raised

How to arrive from and to currency

From currency will be functional currency of ledger (OMR)

To currency will be user selected currency (USD, AED, or QAR)

Conversion rates - Direct and inversion is loaded in daily rates master and it is enabled for each conversion types (own conversion, corporate rates, user defined and so on.,)

Rounding off

Rounding off totals, sub-totals, line totals will be based on currency precision

select precision

 into l_precision

 from fnd_currencies_vl

 where enabled_flag = 'Y'

 and currency_flag = 'Y'

 and currency_code = p_currency;

Utility skeleton

CREATE OR REPLACE package dcf_currency_utils as

function dcf_gl_daily_rates ( p_from_currency in varchar2

                                              , p_to_currency in varchar2

                                              , p_conversion_date in date

                                              , p_customer_trx_id in number)  return number;

function currency_precision (p_currency in varchar2) return number;                                            

end dcf_currency_utils;

/