Mulitple Reporting Currency – MRC – In Oracle Applications


The Multiple Reporting Currencies (MRC) feature allows you to report and maintain accounting records at the transaction level, in more than one functional currency. You do this by defining one or more reporting sets of books, in addition to your primary set of books. In your reporting sets of books, you maintain records in a functional currency other than your primary functional currency. You can set up multiple reporting sets of books and associate them with a primary set of books.Your primary functional currency is the currency you use to record transactions and maintain your accounting data within Oracle Applications. The functional currency is generally the currency in which you transact most of your business and the one you use for legal reporting.

A reporting functional currency is a currency other than your primary functional currency for which you need to report accounting data. You must define a set of books for each of your reporting functional currencies.

When you enter transactions in Oracle Applications, they are converted, as needed, into your primary functional currency and each of your reporting functional currencies. You log into a reporting responsibility to inquire and report on transactions and account balances in your reporting functional currencies.
Oracle Applications Support for MRC

 

· Receivables
· Purchasing

· Payables

· General Ledger

The following Oracle Applications support Multiple Reporting Currencies:

· Cash Management
 
 

 

· Projects

 

 

 

 

 

* MRC is not intended as a replacement for General Ledger’s translation feature.

3) You operate in a country that is part of the European Monetary Union (EMU), and you want to concurrently report in Euro in preparation for the pan-European currency.

2) Your company is multinational, and you need to report financial information in a common functional currency other than that of the transaction or your primary functional currency.

1) You operate in a country whose unstable currency makes it unsuitable for managing your business. As a result, you need to manage your business in a more stable currency and still be able to report your transactions and account balances in the unstable local currency.

Typically, you should consider using MRC when:

 

In Oracle Applications you record day-to-day business transactions in your organization’s primary set of books or post transactions to the primary set of books from your subledgers. From the primary set of books, you can report your account balances in your primary functional currency. To use MRC, you must define additional sets of books, called reporting sets of books, and associate them with a primary set of books. When defining a reporting set of books, you specify your reporting functional currency as the set of book’s functional currency. This is the currency in which you want to inquire and report your transactions and account balances.

 

 

 

 

For example, assume your business is located in Canada. You use a primary set of books whose functional currency is Canadian Dollars (CAD), but you also need to inquire and report on your transactions and balances in U.S. Dollars (USD), since this is the functional currency of your parent organization. You define a reporting set of books with a functional currency of USD, then you associate this reporting set of books with your primary set of books. [The full range of General Ledger functionality is available from a reporting set of books. You can post journals, revalue and translate balances, perform consolidations, query account balances, submit standard General Ledger reports, and define custom financial reports.]
2- Transaction-Level Conversion:

 

 

 

 

When you enter transactions in Oracle Applications that support MRC, they are converted, as needed, into your primary functional currency and each of your reporting functional currencies, as follows:
Primary functional currency transactions: All transactions denominated in your primary functional currency are recorded in this currency. The transactions are also converted automatically to each of your reporting functional currencies.
Foreign currency transactions: Transactions denominated in a foreign currency are converted automatically to your primary set of books’ functional currency and to each of your reporting functional currencies.
3- Subledger Transactions:
 

When you enter transactions into the subledgers of Oracle Applications that support MRC, the transactions are converted to your reporting functional currencies at the time of original entry. The primary functional currency amounts and their associated reporting currency amounts are stored together in your subledgers. You must post subledger transactions to General Ledger in both the primary set of books and in each reporting set of books.

Since conversion occurs when the transactions are entered, your reporting currency amounts are always synchronized with your primary currency amounts.
4- General Ledger Journals
 
 
 

 

: Journal entries that originate in General Ledger, such as manual journals, recurring journals, and MassAllocations, as well as journals that you import from sources other than Oracle Applications’ subledgers, are converted to your reporting functional currencies when you post the journals in General Ledger in your primary set of books.

The converted journals are then copied from your primary set of books to each of the associated reporting sets of books. The converted journals must be posted separately in each reporting set of books.
The balances in your reporting sets of books will not be synchronized with the balances in the associated primary set of books until you:
– Post your subledger transactions to General Ledger from both your primary and associated reporting sets of books
– Post all journals in your primary set of books
– Post the converted journals in each of the associated reporting sets of books

 

 

 

 
 
 

 

 

Step Description
Step 1 ­ Enable or define primary set of books :Set of Books window(General Ledger)

When to Use MRC:

MRC Features
1- Reporting Sets of Books:
Inquiry and Reporting in Multiple Currencies

Oracle Subledgers: When you enter transactions into the subledgers of Oracle Applications that support MRC, the transactions are converted to your reporting functional currencies at the time of original entry. As a result, your reporting currency information is immediately available for inquiry and reporting in the subledgers. Each inquiry or report that normally displays information in the primary functional currency can also be displayed in any of the associated reporting currencies. To do so, you log into a reporting responsibility, then view and report transactions in the reporting currencies associated with that responsibility. You must post subledger transactions to General Ledger in both the primary set of books and in each reporting set of books. After you have posted the transactions, you can log into a General Ledger reporting responsibility, post the newly created journals, then report on the journals and the account balances of the associated reporting set of books.

Oracle General Ledger: For General Ledger journals, you must complete the posting process in both your primary set of books and each associated reporting set of books before you can report on the updated balances. Note that separate balances are updated for each set of books.Before you can report on your updated balances, you must also post your subledger transactions to General Ledger from both your primary and associated reporting sets of books, and post the newly created journals in both your primary and associated reporting sets of books. Each General Ledger report or inquiry that normally displays information in the primary functional currency can also be displayed in any of the associated reporting currencies. To inquire or report on the account balances of a reporting set of books, you log into the associated General Ledger reporting responsibility. When you inquire on account balances in a reporting set of books, you can drill down to the subledger details (in your reporting functional currency) using General Ledger’s standard drilldown features.

For reconciliation purposes, you can use the Financial Statement Generator (FSG) to create a custom comparison report that lists balances from your primary and reporting sets of books in separate columns. Use this report as the basis for reconciling your primary and reporting sets of books.
Euro Support: MRC allows Oracle Applications to support organizations that are transitioning from their national currency to the Euro:

Transition Period: If you currently use Oracle Applications, you can continue to maintain your current set of books in your national currency and use MRC to begin reporting transactions and financial results in the Euro. If you are implementing Oracle Applications for the first time, you can set up your primary set of books using Euro as the functional currency and use MRC to report transactions and financial results in your national currency.

Currency Conversion: MRC observes the fixed-rate relationships you defined between the Euro and EMU currencies, as well as the effective starting dates of those relationships, when converting transaction amounts to or from the Euro or EMU currencies. When converting amounts from your primary functional currency to your reporting functional currencies, MRC fully complies with the conversion guidelines established by the European Commission.
Setting Up MRC The following provides a summary of the steps you must follow to set up MRC in your applications. These steps are described in more detail in the next section. Note: You must install MRC before you can begin the setup steps in this section. See: Oracle Applications Installation Manual for information about installing MRC.

Step 2 ­ Enable and/or define reporting currencies :Currencies window(General Ledger)

Step 3 ­ Define reporting sets of books :Set of Books window(General Ledger)

Step 4 ­ Assign reporting sets of books to primary set of books: Assign Reporting Sets of Books window(General Ledger)

Step 5 ­ Define conversion options for each application: Conversion Options window(General Ledger)

Step 6 ­ Define General Ledger conversion rules: GL Conversion Rules window(General Ledger)

Step 7 ­ Define reporting responsibilities : Responsibilities window(System Administrator)

Step 8 ­ Assign reporting sets of books to reporting responsibilities : System Profile Values window (System Administrator)Daily rates are used to convert your primary set of book’s transactions to the appropriate reporting currencies. If you do not currently maintain daily rates, you must do so when you implement MRC.

*

· Assets

 

Oracle Applications – development, customization, extension


Oracle Supported way to include business needs without creating real customization

When developing with the Oracle eBusiness Suite, there are situations where Developers can use “hooks” in standard Applications code to insert or redirect to custom code. Oracle Applications provide some helpful layers of abstraction to make life easier for customization. Where possible it is nice for this to be done using a supported method. If a true blue customization is required, then its best to try to preserve the existing code so that any future upgrades are more likely to break your custom code by reverting to standard code, plus if you use a “hook” to independent code it is easier to maintain and upgrade the custom code. I regularly use a “copy and modify” approach to Apps Development where I use hooks to call the new code.

Concurrent Program Executable. Where an concurrent program is automagically submitted from a form or another concurrent process, e.g. “Print Pack Slip” in Oracle Shipping actions, as long as parameter requirements are the same, then a quick an easy method to code your own report is: Create a new executable registered under your custom application, e.g. XMODS_WSHRDPAK, Query the called concurrent program and update the executable to your custom executable, e.g. Query WSHRDPAK and replace executable with XMODS_WSHRDPAK

Personalization – Forms and Framework (OAF) plus CUSTOM.pll. Forms PL/SQL Library. Personalization has provided functionality to cover alot of the customizations traditionally coded to CUSTOM.pll, but both Personalization and CUSTOM.pll provide a number of hooks into the front end logic.

Database Triggers. Triggers on tables used carefully can provide hooks where all other methods don’t dare to tread. Of course watch out for the exception and try to avoid putting triggers on fnd_concurrent_requests!

Menu. Provides a prominent method to call new Forms, while potentially hiding behind the same prompt, Reports, Discoverer Workbooks, external links/URLs, etc. Gives the ability to save in the “Favourites” list. If you need to customize a standard form, copy it and create a new menu entry where possible.

Unix Softlink. An alternative to the concurrent program executable hook. Backup an existing standard Oracle file. Remove the existing standard Oracle file. Replace with a softlink to a file under you custom application code “top” directory

Workflow. Workflow customizations are “allowed” and a method I use here is to take a standard function call in workflow, copy and modify the underlying package, then change the function call in workflow to the custom function. E.g. AP Remittance Advice workflow (APPEWF) has “Get Check Info” function calling AP_PAYMENT_EVENT_WF_PKG.get_check_info. Copy AP_PAYMENT_EVENT_WF_PKG to XMODS_AP_PAYMENT_EVENT_WF_PKG and change function in workflow function “Get Check Info”

Printer Driver. Whenever concurrent request file post processing is required, e.g. to FTP or email a file – printer drivers provide an excellent way to perform post processing. Create a new printer driver with the appropriate command. Remember to restart the concurrent manager to pickup updates to print drivers.

Business Events. Not a widely used mechanism, but provides supported hooks into key events such as Payment Confirmations (AP Payment event).

Thanks – Shivmohan Purohit

Oracle Receivables – FAQ , Interview Questions ( Functional & Technical)


 

Oracle Receivables – FAQ , Interview Questions ( Functional & Technical) 

Hello Friends, As part of putting Oracle Application Interview Questions for various modules, here i am putting Receivables , most of them are quite functional and link with Oracle Receivables features, still it is good to learn and know for a technical developer to get overall understanding. do share your feedback n thoughts. 

What is MRC and what is its use?
The Multi Reporting Currency Feature allows you to report and maintain records at the transaction level in more than one functional currency. You can do by defining one or more set of books in addition to primary set of books.
 

 

 

Accointing for invoice in advance
a) Receivable A/c …………………Dr.
To Unearned revenue a/c

(when we raise the invoice with invoicing rule as advance)

b) Unearned Revenue A/c ………….. Dr.
To Revenue A/c
(when we receive the payment, the number of journal entry (b) is depend upon the accounting rules which can be fixed or variable)

Accounting for invoice in arrear

a) Unbilled receivable a/c ………………..Dr.

To Revenue a/c

(when we receive the payment of unbilled invoice, the number of journal entry (a) is depend upon the accounting rules which can be fixed or variable))

b) Receivable a/c …………………….Dr.
To Unbilled receivable a/c
(when we raise the invoice, with invoicing rule arrear)

 

What is the use of Transaction Flexfield in Autoinvoice ?
Transaction Flexfield actually identifies the the uniqueness among the Multiple lines of a single Invoice
 

 

What are value sets?
Value sets are the defined as list of possible values for a specific purpose. These are assigned to flexfields. These are the only possible values to be choosen from. This eliminates the data entry errors.
Value set is a set of possible values.

There are 8 value set types:

1.Dependent

2.Independent

3.Table

4. None

5.Pair

6.Translatable Dependent

7.Translatable Independent

8. Special

Describe the main tables involved in AR, and what is the data stored in them?
RA_BATCHES_ALL — Information about Transaction BATCHES RA_CUSTOMER_TRX_ALL — Header information about Transaction
RA_CUSTOMER_TRX_LINES_ALL — Lines information about Transaction

RA_CUST_TRX_LINE_GL_DIST_ALL – Distribution information about Transaction

RA_CUST_TRX_LINE_SALESREPS_ALL — Sales representative of Transaction Information

AR_PAYMENT_SCHDULES_ALL – Information about Payment Schedules

AR_APPLICATION_PAYABLES_ALL

RA_INTERFACE_ERRORS – Errors in AutoInvoice Interface Data

RA_INTERFACE_LINES_ALL – Use this table to enter Header and Lines information in AutoInvoice Interface program

RA_INTERFACE_DISTRIBUTIONS_ALL – Distribution Table in AutoInvoice Interface program

RA_INTERFACE_SALESCREDITS_ALL – Sales Credits information in AutoInvoice Interface

 

 

What do you mean by HZ_ in customer tables?
HZ stands for Human Zone(HZ_). Anytihing which is related to the human like Customer profiles, their accounts, locations, relationships are stored in these tables only. From release 11i TCA came into picture in Accounts Recievable module, where oracle has grouped all the customer information at one place. Most important tables in TCA are-
HZ_PARTIES,

HZ_CUST_ACCOUNTS_ALL,

HZ_CUST_ACCT_SITES_ALL,

HZ_CUST_SITE_USES_ALL,

HZ_LOCATIONS,

HZ_PARTY_SITES,

HZ_PARTY_SITE_USES,

HZ_CONTACT_POINTS. few to name.

What is the difference between _all, _tl, _vl, _v tables in Oracle Apps ? Also name various other table suffix.
_ALL : Table holds all the information about different operating units. Multi-Org environment. You can also set the client_info to specific operating unit to see the data specific to that operating unit only.
_TL are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.

_B these are the BASE tables. They are very important and the data is stored in the table with all validations. It is supposed that these table will always contain the perfect format data. If anything happens to the BASE table data, then it is a data corruption issue.

_F these are date tracked tables, which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap. Many think they are Secured data. Guess someone from Oracle confirms.

_V tables are the views created on base tables _VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV(’LANG’).

_S are sequences, used for finding new values for the primary key of a table.

_A are Audit Shadow Tables

_AVN and _ACN are Audit Shadow Views (when data was changed, and with what values)

How many Flex fields are there in AR and what are they?

Required Key Flex fields:
1. Territory Flex field
2. Sales Tax Location flex field

Optional Key Flex fields:

1. Transaction flex field (requied only if Auto Invoicing is enabled)

2. System Items Flex field (If Inventory or OM is installed this should be defined there. other wise, it should be set up in AR).

 

 

 

Batch sources control the standard transaction type assigned to a transaction and determine whether Receivables automatically numbers your transactions and transaction batches. You can define two types of transaction batch sources-Manual and Imported

 

What is the importance of Batch Source set up in AR ?

 

 

Happy Go Daddy Customer!
 
 
 

 

 

 

Explain Accounting for invoice in Advance and Arrears.

 

 

How is the balance of an invoice derived ?

Differences between Oracle Technical, Oracle Functional and Oracle Techno-Functional


We can take these three areas as how people/ professional pursue their interest as well how business and technology piece link together. The importance of all three role is same and only context of business and IT Project lifecycle along with complexity of process define/ decide which is best suited and workout. My genuine defintion is all three are very much influence in business scenarios. Also moving from one area to other take good amount of  study, hardwork and committment.

 

 

Technical

Techno-Functional

Functional

Educations

Technical degree ie BE, Mtech, MCA

Technical degree ie BE, Mtech, MCA

Business degree i.e. MBA, CFA, CA, CPIM etc

Profile

Technical development and like to get expertise in technical areas i.e. architecture, DBA, developer, research

Start with technical and pursue on analysis driven activities ie. Business analyst, technical analyst, cordinations

Work toware operations, business site with little or no knowledge on IT side

Type of task

Development, debugging,

Production support, coordination

Implementation, User training

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As a team or as anorganization, all three type of role and people require, it is the work context which define which type of profile more suited in particular phase of project lifecycle. In idea scenario blending is best but having blending cause confusion and overlapping of outline responsibility that is quite tricky and risky.

 

In project / Product lifecycle, while in research, analysis, requirement phase, there is lot of need of people having functional insight of business, process, procedure along with government law, tax, market etc. In development technical people need to convert and bring all feature and product in best technical part, there if functional people did their job nicely they technical team also deliver with good quality. After that in transition, training or early life or later ongoing support, to bring more productivity as well value of money, people with technical background having enough business awareness are best suited, we call they techno-functional.

 

Friends , i surely like to know your views and feedback on my thoughts, also plz add your thoughts in this topic to get more clarity and outline some useful way to explain these three terms — Oracle Technial , Oracle Functional , Oracle Techno-Functional.

 

Wishes — Shivmohan

Oracle – Technical FAQ – Part 3


Q11) How do you implement the If statement in the select statement?
A11) We can implement the if statement in the select statement by using the decode statement.
e.g. select DECODE(EMP_CAT,’1′,’First’,’2′,’Second’,Null);
Here the Null is the else statement where null is done.
 
Q12) How many types of exceptions are there?

A12) a) System exceptions

e.g. When no_data_found, when too_many_rows

b) User Defined Exception

e.g. When my_exception then

 

Q13) What are the inline and the precompiler directives ?

A13) The inline and precompiler directives detect the values directly.

 

Q14) How do you use the same lov for 2 columns ?

A14) We can use the same lov for 2 columns by passing the return values in global values and using the global values in the code.

 

Q15) How many minimum groups are required for a matrix report ?

A15) The minimum number of groups in matrix report are 4.

 

Q16) What is the difference between the static and dynamic lov?

A16) The static lov contains the predetermined values while the dynamic lov contains values that come at run time.

 

Q17) What are snap shots and views ?

A17) Snapshots are mirror or replicas of tables. Views are build using the columns from one or more tables. The single table view can be updated but the view with multi table can not be updated.

 

 

 

 

 

 

Excel output directly from Oracle Application Concurrent Request Output


Need to create Microsoft Excel style files directly openable from Oracle Applications concurrent request output … without using BI Publisher?

A little know file format with acronym SYKL is a handy tool for create files readable in Microsoft Excel. here’s the recipe for a very simple Excel (SYLK) output concurrent program for an FND_USERS listing with creation date from/to parameters. So, without further ado, here’s the recipe for a very simple Excel (SYLK) output concurrent program for an FND_USERS listing with creation date from/to parameters

   1. Take a PL/SQL package based on the Oracle provided OWA SYLK ppckage (owasylk.sql / owa_sylk.sql) and make some changes: ( http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:728625409049 )

  • rename it to owa_sylk_apps
  • Change it to use fnd_file.put_line(fnd_file.output instead of utl_file.put_line(g_file
  • Remove parameters for p_file

   2. Create a PL/SQL package for the concurrent program that outputs SYLK file to the concurrent request output.

create or replace package XXXV8_USERS_SYLK_PKG AUTHID CURRENT_USER

AS

procedure main

( errbuf      out varchar2

, retcode     out varchar2

, p_date_from in  varchar2

, p_date_to   in  varchar2

);

end XXXV8_USERS_SYLK_PKG;

/

create or replace package body XXXV8_USERS_SYLK_PKG

AS

procedure main

( errbuf      out varchar2

, retcode     out varchar2

, p_date_from in  varchar2

, p_date_to   in  varchar2

) as

  l_date_from date;

  l_date_to   date;

begin

  l_date_from := fnd_date.canonical_to_date(p_date_from);

  l_date_to   := fnd_date.canonical_to_date(p_date_to);

  owa_sylk_apps.show(

        p_query => ‘select user_id user_id, user_name user_name, ‘

                   ‘       description description, creation_date created ‘

                   ‘from fnd_user ‘

                   ‘where trunc(creation_date) >  :DATE_FROM ‘

                   ‘and   trunc(creation_date) <= :DATE_TO ‘,

        p_parm_names =>

                 owa_sylk_apps.owaSylkArray( ‘DATE_FROM’, ‘DATE_TO’),

        p_parm_values =>

                 owa_sylk_apps.owaSylkArray(l_date_from ,l_date_to),

        p_widths =>

                 owa_sylk_apps.owaSylkArray(20,20,20,20)

                 );

end main;

END XXXV8_USERS_SYLK_PKG;

   3. Setup the concurrent program

   4. Hijack one of the little used Viewer Options (PCL) so that we can get the Concurrent Request output browser to automatically open Microsoft Excel. Note this is optional, you can just create a new Viewer Option but then you may get the “Choose Viewer” box when viewing concurrent request output. NB: Navigation path is System Administrator, Install, Viewer Options

update fnd_mime_types_tl

set    mime_type = ‘application/vnd.ms-excel’

,      description = ‘Excel (SYLK) used to be application/vnd.hp-PCL: Printer Control Language’

,      last_updated_by = 0

,      last_update_date = sysdate

where  file_format_code = ‘PCL’

and    mime_type = ‘application/vnd.hp-PCL’;

commit;

   5. Assign the concurrent program to the appropriate request group (e.g. System Administrator, All Reports, Application Object Library) and run the concurrent program to test it all out! And there you have it – Excel style output direct from concurrent request generated by PL/SQL!

Article courtesy and credit goes to  —  http://garethroberts.blogspot.com/2007/10/excel-file-output-from-oracle.html