OAF MVC Architecture


OAF is a java based application framework to develop web based applications that link to Oracle Applications instance while maintaining all the security features of that apps instance. A framework is a specialized set of related classes designed to make application development easier.  In effect, a framework implements part of an application so developers don’t have to write all of its code from scratch; they can use the framework as the basis for their work and while focusing on the additional code required to implement their specific application requirements.

OA Framework follows Model, View and Controller (MVC) Architecture as described below:

1] Model

Model contains the components which handles data directly from Database. It Includes Business Components for Java (BC4J Objects) which mainly are:

Entity Objects (EO):

Entity Objects are generally based on one table which encapsulate the business rules. These objects are used by OAF page to perform update/insert/delete operations. You can join two EOs using Entity Associations.

View Objects (VO):

These objects contain a SQL query that queries the data from database and present it in the OAF page. VOs can be based on one or many EOs or a SQL query. Two VO can be linked together through a View Link.

Application Module (AM):

It is a container for related BC4J objects and provides the database connection. It also provides Transaction Context (OADBTransaction) or Transaction Management. An AM can have more nested AM contained in it along with other BC4J components. But it is mandatory to have an AM for an OAF page.

2] View

View contains the actual page items on page which user can see. The view in OAF comprises of various page level items like text fields, buttons, regions, links etc. These items are visible on any OAF page. These items can either be tied to VO attribute or having a constant value or populated at run time based through controller logic.

Please note that- View Layer is altogether different than View Object!!!!!

3] Controller

Controller handles all the user actions done on the page. OAF requires a java controller class to be defined for a page/region which handles various page level actions. The important methods in this class are:

A] ProcessRequest

     This request is called when page is rendered. Any logic to be executed during page initialization is kept here.

B] ProcessFormRequest

Any page submit action causes ProcessFormRequest to be executed. The logic put here typically is that which needs to be executed after actions like button click or any other page submit action.

The Controller class is mostly used to put logic for actions on page such as button clicks, navigation to other pages. The two objects that are passed to controller methods are OAPageContext and OAWebBean. OAPageContext provides access to objects like AM class, page parameters, session values, navigation methods. OAWebBean is generally used to get a handle of page items.

In coming posts, I will try to write more details about these components and how they works….so stay tuned!

Advertisements

How to Clear Cache from Oracle Application without Bouncing Listener?


Caching Framework in oracle apps R12 comes with an administration User interface and it is available under the Functional Administrator responsibility. This interface can be used to perform administrative operations including changing the time-out values for cache components, looking at cache usage statistics, and clearing caches.

Here are the steps to clear all Global Cache:

1] Login to Functional Administrator responsibility – then choose Home.

2] Choose the ‘Core Services’ Tab – then the “Caching Framework” Sub-Menu.

3] Proceed to choose ‘Global Configuration’ from the left hand side menu.

4] In the far right choose ‘Clear all Cache’ button.

5] A screen prompts and confirms that the action will clear all cache on the middle tier server – choose Yes. Essentially, this just forces all user sessions to engage and validate – rather than using cached values.

6] A confirmation message is displayed, confirming that all cache has been cleared across middle tiers.

7] Proceed to test and confirm whatever change was made to the preference, profile, etc….

Please note that clearing the OA Framework cache can cause data issues if multiple users are engaged and transacting data in the application at the time cache is cleared. Please use this utility with proper care.

You can also clear cache for specific component. To do that you need to go to Core Services -> Caching Framework -> Tuning. Query the application (for example iProcurement) or by Name or code.  Select the component and clear the cache.

Few Interesting Questions on Oracle GL Journals Entry


Few Interesting Questions on Oracle GL Journals Entry


Is There a Report That Displays Information of One Specific Journal Entry Unposted/Posted?

No. General Ledger reports display information of journal batches posted or unposted. However, you can use the below sql query to find information of a particular journal entry.

select  b.je_batch_id batch_id ,
        h.je_header_id header_id ,
        l.je_line_num line ,
        l.code_combination_id ccid ,
        g.segment1 || '.' || g.segment2 || '.' || g.segment3 ||
        '.' || g.segment4 || '.' || g.segment5 || '.' || g.segment6 ||
        '.' || g.segment7 || '.' || g.segment8 || '.' || g.segment9 ||
        '.' || g.segment10 combination ,
        l.entered_dr entered_dr,
        l.entered_cr entered_cr,
        l.accounted_dr accounted_dr,
        l.accounted_cr accounted_cr,
        l.status
from    gl_je_lines l,
        gl_je_headers h,
        gl_je_batches b,
        gl_code_combinations g
where   b.je_batch_id = h.je_batch_id
        and h.je_header_id = &je_header_id
        and l.je_header_id = h.je_header_id
        and h.je_batch_id = b.je_batch_id
        and l.code_combination_id = g.code_combination_id
order by h.je_header_id, l.je_line_num;

Can a Posted General Ledger Journal Entry be deleted?

After a journal entry is posted, it cannot be deleted. Posted journal entries cannot be deleted because that would eliminate the audit trail. To nullify the accounting effect of the posted journal entry, you can reverse it.

When can not a journal batch be deleted or modified?

A journal batch cannot be deleted or modified under the following circumstances:

a. The source is frozen

b. Funds have been reserved for the batch

c. Funds are in the process of being reserved for the batch

d. The batch is in the process of being posted

e. The batch is posted

f. The batch is approved

g. The batch is in the process of being approved

A journal batch should not be updated if it comes from a sub-ledger.

Changing accounting information in a journal that originated in a sub-module will unsynchronize the accounting information between the ledger and the sub-ledger. Instead of changing the sub-ledger journal, define a new journal to adjust the accounting information if necessary.

A journal batch that has funds reversed cannot be updated because the funds would not be re-reserved appropriately.

Which report shows details of a posted journal batch?

Journals – General (180 Char) and Journals – General (132 Char)

Is possible to restrict users from entering negative amounts in journal lines?

Unfortunately, it is not possible to restrict users from entering negative amounts in journal entry lines.

How to set up journal approval in General Ledger?

This is set up using Oracle Workflow Builder. The basics steps to setup Journal Approval are as below

a) Enable Journal Approval at the Ledger level
b) Setup Journal Sources for Journal Approval
c) Configure the profile options that control how the approval list will be built
d) Define Employees and Supervisors
e) Define Approval limits for approvers
f) Associate the employees to Oracle Apps users
g) Optional Workflow Configuration

For more information refer metalink notes: ID 176459.1 & ID 278349.1

How do you attach an Excel spreadsheet to a journal entry in Oracle General Ledger?

  1. Query the Journal that needs the spreadsheet attachment.

  2. Click on the paperclip on the tool bar.

  3. Fill the following fields in the Attachment form.

     Category    –  Choose Journal from LOV

     Description –  optional

     Data Type   –  OLE Object from the LOV

  4. Right click on the large white portion of the Attachment form choose ‘Insert Object’ from the drop box.

  5. When the Insert Object Form appears check “create from file” and click on “Browse” to choose the file that should be attached from the directory structure.

  6. Save.

How do you easily copy a journal entry from one set of books to another?

There is no standard feature to copy journal entries between sets of books. However, there are some alternatives. Refer Metalink note: ID 204082.1

How to prevent user’s ability to reverse unposted journals?

For 11i, there is not a method to prevent users from reversing unposted journals. This is intended functionality to incorporate the maximum flexibility that users may require. However you can limit user access to journal reversals through user menus set up in Sys Admin responsibility.

This functionality changed in R12 – see Note 734848.1 In Release 12, a batch must be posted before it can be reversed.

How do you automatically generate a reversal journal entry for a journal category in the previous accounting period?

If you routinely generate and post large numbers of journal reversals as part of your month end closing and opening procedures, you can save time and reduce entry errors by using Automatic Journal Reversal to automatically generate and post your journal reversals.

First you define journal reversal criteria for journal categories. Journal reversal criteria let you specify the reversal method, period and date. You can also choose to enable automatic generation and posting of journals.

When you create a journal entry you want to automatically reverse, specify a journal category that has assigned reversal criteria. Your journal will be reversed based on the method, period and date criteria you defined for that journal category.

In Release 12, a reversal journal that is Unposted cannot be modified. Why?

This is the expected functionality in Release 12. However the profile GL: Edit Reverse Journals can be set to allow the modification. Refer metalink note: ID 567641.1

Reversing journal was deleted from the system, how can you still reverse the original journal?

General Ledger does not allow you to reverse a journal entry twice. . Refer metalink note: ID 145043.1 for details.

A journal entry with a source set up for automatic reversal is not reversed. Why?

General Ledger automatically submits the AutoReverse program when a period is opened if the profile option, GL: Launch AutoReverse After Open Period, is set to Yes. If a journal is created after the period has already been opened, then the AutoReverse program will need to be submitted manually.

A journal has been created and is unposted.  The following period has a reversing journal for the original journal and it is posted. Why it is so?

This is currently the functionality of the application to allow the reversing journal to be posted even if the original journal is not.

Query to get Customer Information in R12


Query to get Customer Information in R12

This query is tested in R12.1.3 instance. The query may take few minutes to execute depending upon the size of the data that are present in various HZ Tables.

select  distinct
        hp.party_name "Customer Name",
        hca.account_number,
        hca.status,
        hcsu.location,
        hcsu.site_use_code,
        hcsu.status loc_stat,
        ps.class,
        hcsu.site_use_id,
        hcpc.name profile_name,
        hl.address1,
        hl.address2,
        hl.address3,
        hl.city,
        hl.state,
        hl.postal_code,
        ps.customer_id,
        ps.customer_site_use_id,
        hps.identifying_address_flag,
        ps.trx_date,
        HOU.NAME "Operating Unit"
from    apps.hz_parties hp,
        apps.hz_party_sites hps,
        apps.hz_locations hl,
        apps.hz_cust_accounts hca,
        apps.hz_cust_acct_sites hcas,
        apps.hz_cust_site_uses hcsu,
        apps.hz_customer_profiles hcp,
        apps.hz_cust_profile_classes hcpc,
        apps.ar_payment_schedules_all ps,
        apps.hr_operating_units hou
where   hp.party_id = hca.party_id(+)
        and hp.party_id = hcp.party_id
        and hp.party_id = hps.party_id
        and hps.party_site_id = hcas.party_site_id
        and hps.location_id = hl.location_id
        and hca.cust_account_id = hcas.cust_account_id
        and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
        and hca.cust_account_id = hcp.cust_account_id
        and hca.cust_account_id = ps.customer_id
        and hcp.profile_class_id = hcpc.profile_class_id
        and ps.customer_site_use_id = hcsu.site_use_id
        and hcsu.org_id = hou.organization_id;

Multi-Org or multiple organization access (MOAC) in R12


Multi-Org or multiple organization access (MOAC) in R12

What is MOAC?

Multi-Org or multiple organization access (MOAC) is basically an ability to access multiple operating units from a single application responsibility.

Why it has been created?

Prior to R12, end users use to toggle / switch / change responsibilities in order to do transactions (like invoice / payment processing in AP) in different operating units. This is a very time consuming and inefficient way of recording transactions when you have 100s of operating units specially Internet based organizations who have worldwide operations in almost all the countries.

To address this, a new feature in R12 has been introduced in which user can switch between operating units within a responsibility something similar to “Change Organization” feature in inventory. Prior to R12, user would have to switch responsibilities in order to enter transactions in respective operating units (tagged to the responsibility).

What are its advantages?

  • Multi-Org Access Control (MOAC) enables companies that have implemented a Shared Services operating model to efficiently process business transactions by allowing them to access, process and report on data for an unlimited number of operating units within a single applications responsibility.
  • This increases the productivity of Shared Service Centers, as users no longer have to switch application responsibilities when processing transactions for multiple operating units at a time.
  • Ability to view data from multiple operating units from a single responsibility, gives users more information. This enables them to make better decisions.

The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them.

SELECT   psp.SECURITY_PROFILE_NAME,
         psp.SECURITY_PROFILE_ID,
         hou.NAME,
         hou.ORGANIZATION_ID
FROM     PER_SECURITY_PROFILES psp,
         PER_SECURITY_ORGANIZATIONS pso,
         HR_OPERATING_UNITS hou
WHERE    pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
         AND pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

There are three Profile Options you need to be aware of related to Multi-Org that should be set at the Responsibility Level.

  • MO: Security Profile– Always evaluated first.
  • MO: Operating Unit– Secondary priority being evaluated after ‘MO: Security Profile’
  • MO: Default Operating Unit– Sets the default Operating Unit for transactions when running under a Security Profile.

How it is done in R12?

In Release 12, one creates a Security Profile and assigns as many operating units as you required. One can tie that security profile to a single responsibility using a profile option called MO: Security Profile. For example, you could assign the security profile to the EMEA Payables responsibility to allow that responsibility to process invoices across all operating units.

In Release 12, define a security profile in HR using the Security profile form or the Global Security profile form, and assign all of the operating units that one would want a responsibility to access. The one needs to run a concurrent request called “Run Security List Maintenance” from HR which will make those security profile available and allow one to assign them to a responsibility via a profile option called MO: Security Profile.

One can define another profile option called MO: Default Operating Unit which is optional and allows one to specify a default operating unit that will be the default when you open different subledger application forms.

What’s New in R12 Financials?


What’s New in R12 Financials?

1] Ledgers and Ledger Sets:

The ledger is a new fundamental concept in Release 12.  The ledger replaces the 11i concept of a set of books.  It represents an accounting representation for one or more legal entities or for a business need such as consolidation or management reporting. 

11i & Prior = Sets of Books (3 C’s)

  • Chart of Accounts
  • Accounting Calendar
  • Currency

R12 = Ledgers (4 C’s)

  • Chart of accounts
  • Ledger currency
  • Accounting calendar
  • Accounting method – new 4th

While a set of books is defined by 3 C’s, chart of accounts, functional currency, and accounting calendar, the ledger is defined by a 4th C: the accounting method.  This 4th C allows you to assign and manage a specific accounting method for each ledger.  Therefore, when a legal entity is subject to multiple reporting requirements, separate ledgers can be used to record the accounting information.

Primary Ledger:

  • The main “Activity” Ledger
  • Usually in the local currency
  • For Operational reporting

Secondary Ledger:

  • Differs from Primary Ledger by Chart of Account, Calendar, and/or Accounting Method
  • For Statutory, Tax or Consolidated reporting

Reporting Currency Ledger:

  • Differs from Primary Ledger by Currency ONLY
  • Just a translation of the Primary Ledger – no rules required
  • For Consolidated reporting

LEDGER SETS:

  • Grouping of ledgers with the same chart of accounts and calendar/period type combination
  • Essentially treats multiple ledgers as one

2] Subledger Accounting:

You can consider SLA as a bridge or an Intermediate platform that talks to Subledger products (these are other applications or modules) and the General ledger. All Accounting entries for your modules (like AP, AR, Projects, Inventory, etc) are treated as Sub-Ledgers and they first sent to the SLA engine. The SLA applies its rules (some or these rules are pre-configured and also you can configure as many rules as you want) and then sends the necessary journal entries to the General ledger.

In a nutshell, the following services are provided by Oracle SLA

  • Rule based Generation and  storing of accounting entries
  • Storing subledger balances
  • Subledger or SLA accounting entries
  • Subledger reporting (some examples could be Open Account Balances Listing and Subledger Journal Reports, etc )

3] Multi-Org Access Control (MOAC):

‘Multi-Org Access Control’ popularly known as ‘MOAC’ in short form is an enhanced feature in Release 12. MOAC will enable users to access secured data in one or more Operating Units from a single responsibility.

End-Users can access/transact data within several operating units based on Security Profile attached to a responsibility. i.e. End-Users can access/transact data on multiple Operating units by accessing one operating unit at a time without changing a responsibility. This Provides flexibility for end-users to work conveniently with multiple Operating Units in shared service Environments with single responsibility.

4] Advanced Global Intercompany System (AGIS):

Advanced Global Intercompany System (AGIS) enables you to create, settle and reconcile intercompany transactions. Intercompany transactions are transactions that occur between two related legal entities in an enterprise or between groups in the same legal entity. The balances of the intercompany transactions must be eliminated or adjusted when preparing the consolidated financial statement, or it might result in overstated financial results, which in turn might lead to legal repercussions against the enterprise. Intercompany transactions can be identified and eliminated by the use of specific accounts to book these transactions.

5] Tax Engine:

It Centrally manage tax transactions across entire E-Business Suite.

  • Single Repository of transactions for global business insight
  • Centralized rules applied to transactions to manage globally and reduce risk
  • Automation of tax processes on transactions to improve operational efficiency
  • Improved Reporting
  • Effective Date Setup
  • Extensible architecture that supports additions, e.g. Self-assessed Use Tax

6] Bank Model:

Because of changing business need and high demand of global partners, the R12 release witness great changes ever into the bank model. Bank account is now associated with Legal entity rather than Operating Unit and hence single bank account serves multiple Operating Units. This makes bank with strong capability to pay across operating units. More over banks accounts can be shared by applications and can be designed for use by Payables, Receivables and Payroll.

The new bank account model allow you to define and keep track of all bank accounts in the e-Business Suite in one place and explicitly grant account access to multiple operating units/functions and users. The new model reduces the number of access points to manage bank accounts by providing a centralized user interface where all internal bank accounts can be set up.

Query to find accounting flexfield structure


Query to find accounting flexfield structure

select sob.name Ledger_Name
, sob.ledger_id Ledger_Id
, sob.chart_of_accounts_id coa_id
, fifst.id_flex_structure_name struct_name
, ifs.segment_name
, ifs.application_column_name column_name
, sav1.attribute_value BALANCING
, sav2.attribute_value COST_CENTER
, sav3.attribute_value NATURAL_ACCOUNT
, sav4.attribute_value INTERCOMPANY
, sav5.attribute_value SECONDARY_TRACKING
, sav6.attribute_value GLOBAL
, ffvs.flex_value_set_name
, ffvs.flex_value_set_id
from fnd_id_flex_structures fifs
, fnd_id_flex_structures_tl fifst
, fnd_segment_attribute_values sav1
, fnd_segment_attribute_values sav2
, fnd_segment_attribute_values sav3
, fnd_segment_attribute_values sav4
, fnd_segment_attribute_values sav5
, fnd_segment_attribute_values sav6
, fnd_id_flex_segments ifs
, fnd_flex_value_sets ffvs
, gl_ledgers sob
where 1=1
and fifs.id_flex_code = 'GL#'
and fifs.application_id = fifst.application_id
and fifs.id_flex_code = fifst.id_flex_code
and fifs.id_flex_num = fifst.id_flex_num
and fifs.application_id = ifs.application_id
and fifs.id_flex_code = ifs.id_flex_code
and fifs.id_flex_num = ifs.id_flex_num
and sav1.application_id = ifs.application_id
and sav1.id_flex_code = ifs.id_flex_code
and sav1.id_flex_num = ifs.id_flex_num
and sav1.application_column_name = ifs.application_column_name
and sav2.application_id = ifs.application_id
and sav2.id_flex_code = ifs.id_flex_code
and sav2.id_flex_num = ifs.id_flex_num
and sav2.application_column_name = ifs.application_column_name
and sav3.application_id = ifs.application_id
and sav3.id_flex_code = ifs.id_flex_code
and sav3.id_flex_num = ifs.id_flex_num
and sav3.application_column_name = ifs.application_column_name
and sav4.application_id = ifs.application_id
and sav4.id_flex_code = ifs.id_flex_code
and sav4.id_flex_num = ifs.id_flex_num
and sav4.application_column_name = ifs.application_column_name
and sav5.application_id = ifs.application_id
and sav5.id_flex_code = ifs.id_flex_code
and sav5.id_flex_num = ifs.id_flex_num
and sav5.application_column_name = ifs.application_column_name
and sav6.application_id = ifs.application_id
and sav6.id_flex_code = ifs.id_flex_code
and sav6.id_flex_num = ifs.id_flex_num
and sav6.application_column_name = ifs.application_column_name
and sav1.segment_attribute_type = 'GL_BALANCING'
and sav2.segment_attribute_type = 'FA_COST_CTR'
and sav3.segment_attribute_type = 'GL_ACCOUNT'
and sav4.segment_attribute_type = 'GL_INTERCOMPANY'
and sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING'
and sav6.segment_attribute_type = 'GL_GLOBAL'
and ifs.id_flex_num = sob.chart_of_accounts_id
and ifs.flex_value_set_id = ffvs.flex_value_set_id
and sob.ledger_id =
nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.ledger_id)
order by sob.name, sob.chart_of_accounts_id, ifs.application_column_name;

Note: The query is tested in R12 Environment.