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.

Delete Duplicate Records in Oracle


Delete Duplicate Records in Oracle

There are times when duplicate rows somehow creep into a table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully. Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:

CREATE TABLE dup_test (
    Emp_Id VARCHAR2(5),
  Name VARCHAR2(15),
    Phone  NUMBER);

INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('101','Dave',561982);

SELECT * FROM dup_test;

Use subquery to delete duplicate rows:

Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:

DELETE FROM
   dup_test A
WHERE
  a.rowid >
   ANY (
     SELECT
        B.rowid
     FROM
        dup_test B
     WHERE
        A.Emp_Id = B.Emp_Id
     AND
        A.Name = B.Name
     AND
        A.Phone = B.Phone
        );

Use analytics to delete duplicate rows:

You can also detect and delete duplicate rows using Oracle analytic functions:

DELETE FROM dup_test
WHERE ROWID IN
  (SELECT ROWID FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Id ORDER BY Emp_Id) rnk FROM dup_test)
    WHERE rnk>1);

Use another table to delete duplicate rows:

This is the simplest method to remove duplicity.

CREATE TABLE dup_test_1 as select distinct * from dup_test;
DROP TABLE dup_test;
RENAME dup_test_1 to dup_test;

Use RANK to delete duplicate rows:

This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:

DELETE FROM dup_test where rowid in
  (
  select "rowid" from
  (select "rowid", rank_n from
  (select rank() over (partition by Emp_Id order by rowid) rank_n, rowid as "rowid"
             from dup_test
             )
         )
     where rank_n > 1
);

The above methods are only standard methods. You can also use your own techniques to remove duplicate records.

Alerts in Oracle Application


Alerts in Oracle Application

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want. You can define one of two types of alerts: an event alert or a periodic alert.

Event Alert:

An event alert immediately notifies you of activity in your database as it occurs. When you create an event alert, you specify the following:

• A database event that you want to monitor, that is, an insert and/or an update to a specific database table.

• A SQL Select statement that retrieves specific database information as a result of the database event.

• Actions that you want Oracle Alert to perform as a result of the database event. An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

Periodic Alert:

A periodic alert, on the other hand, checks the database for information according to a schedule you define. In a periodic alert specify the following:

• A SQL Select statement that retrieves specific database information.

• The frequency that you want the periodic alert to run the SQL statement.

• Actions that  Oracle Alert to perform once it runs the SQL statement. An action can entail sending the retrieved information to someone in an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. We include all the actions we want Oracle Alert to perform, in an action set.

Navigation in Oracle Apps to define an alert:

Go to “Alert Manager” Responsibility
Alert >> Define

Transfer Alert from one instance/database to other:

Go to “Alert Manager” Responsibility
Alert >> Define
Go to “Tools” Menu on top
Click on “Transfer Alert”
Enter source and destination fields and click Transfer.

How to define an periodic alert:

  1. Go to Alert Manager > Alert > Define.
  2. Select the ‘Periodic’ Tab.
  3. Enter the name of the application that owns the alert in the Application field.
  4. Name the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
  5. Check Enabled to enable your periodic alert.
  6. Set the frequency for the periodic alert to any of the following:
  • On Demand
  • On Day of the Month
  • On Day of the Week
  • Every N Calendar Days
  • Every Day
  • Every Other Day
  • Every N Business Days
  • Every Business Day
  • Every Other Business Day

Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define. Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement.

Here is an example of a periodic alert Select statement that looks for users who have not changed their passwords within the number of days specified by the value in :THRESHOLD_DAYS.:

SELECT user_name,
password_date,
:THRESHOLD_DAYS
INTO &USER,
&LASTDATE,
&NUMDAYS
FROM fnd_user
WHERE sysdate = NVL(password_date,
sysdate) + :THRESHOLD_DAYS
ORDER BY user_name

Although Oracle Alert does not support PL/SQL statements as the alert SQL statement definition, you can create a PL/SQL packaged function that contains PL/SQL logic and enter a SQL Select statement that calls that packaged function.

You can verify the accuracy and effectiveness of your Select statement. Choose Verify to parse your Select statement and display the result in a Note window.

Choose Run to execute the Select statement in one of your application’s Oracle IDs, and display the number of rows returned in a Note window.

Once you are satisfied with the SQL statement, save your work.

Specifying Alert Details:

Once you define an event or periodic alert in the Alerts window, you need to display to the Alert Details window to complete the alert definition. The Alert Details window includes information such as which Application installations you want the alert to run against, what default values you want your inputs variables to use, and what additional characteristics you want your output variables to have.

Creating Alert Actions:

After you define your alert you need to create the actions you want your alert to perform. There are four types of actions you can create:

• message actions

• concurrent program actions

• operating script actions

• SQL statement script actions

Choose Actions

Enter a name (up to 80 characters) and description (up to 240 characters) for your alert action.

Select a level for your action: Detail, Summary, or No Exception.

Choose Action Details to display the Action Details window.

Select the type of action you want to create in the Action Type field

Creating an Event Alert:

Specify the name of the application and the database table that you want Oracle Alert to monitor.

Note: You cannot use a view as the event table for your alert.

Check After Insert and/or After Update if you want to run your event alert when an application user inserts and/or updates a row in the database table.

Specify a value in the Keep _ Days field to indicate the number of days of exceptions, actions, and response actions history you want to keep for this alert.

Specify a value in the End Date field if you want to disable your alert by a certain date.

Important Alert Tables:

  • ALR_ALERTS
  • ALR_ACTIONS
  • ALR_ACTION_SETS
  • ALR_ACTION_SET_INPUTS
  • ALR_ACTION_SET_OUTPUTS
  • ALR_ACTION_SET_MEMBERS
  • ALR_ALERT_CHECKS
  • ALR_ALERT_INPUTS
  • ALR_ALERT_OUTPUTS
  • ALR_ACTION_SET_CHECKS
  • ALR_RESPONSE_SETS
  • ALR_RESPONSE_ACTIONS
  • ALR_VALID_RESONSES

Oracle Alert uses the following internal views:

  •  ALR_ALERT_ACTIONS_VIEW
  •  ALR_ALERT_HISTORY_VIEW
  •  ALR_CHECK_ACTION_HISTORY_VIEW
  •  ALR_INSTALLATIONS_VIEW
  •  ALR_PERIODIC_ALERTS_VIEW
  •  ALR_RESPONSE_ACTIONS_VIEW
  •  ALR_SCHEDULED_PROGRAMS
  •  ALR_VARIABLES_AND_OUTPUTS

For complete details you can refer Oracle Alert User’s Guide

KEY FLEX FIELDS (KFF)


KEY FLEX FIELDS (KFF)

KFF are used to capture mandatory or Key Business information of the Organization. Each Key Flex Field is having its won base Table.

A key flexfield is a field made up of segments, each of which has both a value and a meaning. You can think of a key flexfield as an “intelligent” field that your business can use to store information represented as codes.

Most organizations use codes to identify general ledger accounts, part numbers, and other business entities. Each segment in the code represents a characteristic of the entity. A combination of segment values, also known as a key flexfield code combination, uniquely describes a business entity stored in a key flexfield.

The organization decides the following four basic information for each key flexfield:

  1. How many segments an entity has?
  2. What each segment means?
  3. What values each segment can have?
  4. What each segment value means?

Few Key Flexfields in different oracle modules are:

Module: GL

  1. Accounting

Module: HRMS

  1. Job
  2. Position
  3. Grade
  4. Personal Analysis   

Module: Inventory

  1. Account Aliases
  2. Item Catalogs
  3. Item Categories
  4. Sales Orders
  5. Stock Locations
  6. System Items

Module: Fixed Assets

  1. Asset
  2. Category
  3. Location

Module: AR

  1. Sales Tax Location
  2. Territory

Note:

Flexfields consists of Structures > Structures consists of Segments > Segments consists of Value Set >Value Set consists of Parameters.

Main Tables:

FND_ID_FLEXS:

This table captures the information of all the Key FlexFields. The main columns in this table are:

  • APPLICATION_ID ‐ Column consists of Application ID
  • ID_FLEX_CODE ‐ Column KFF Code (like ‘GL#’, ‘AR#’ etc.)
  • ID_FLEX_NAME  –  KFF Name (like ‘Accounting Flexfield’, ‘Category Flexfield’..etc.)
  • APPLICATION_TABLE_NAME – Name of combination table (like ‘GL_CODE_COMBINATIONS’ , ‘FA_LOCATIONS’ etc.)

FND_ID_FLEX_STRUCTURES:

This table stores structure information about key Flexfields. Each Structure is uniquely identified by

  • APPLICATION_ID – Module Code
  • ID_FLEX_CODE  – Code of KFF
  • ID_FLEX_NUM – Number of a Structure

FND_ID_FLEX_SEGMENTS:

It captures the information of Segments. Each Segment is Uniquely identified by

  • APPLICATION_ID – Module Code
  • ID_FLEX_CODE – Key Flexfield code
  • ID_FLEX_NUM – Key flexfield structure number
  • SEG_NUM – Segment number
  • FLEX_VALUE_SET_ID – Flexfield value set identifier

FND_FLEX_VALUE_SETS:

This table captures the information of each Segment’s Value Set. Each Value Set is Uniquely identified by FLEX_VALUE_SET_ID as Foreign Key of FND_ID_FLEX_SEGMENTS Table.

FND_FLEX_VALUES:

This table captures the information each Value codes of a Value Set of a Segment. Each Value Code is uniquely identified by

  • FLEX_VALUE_SET_ID
  • FLEX_VALUE_ID

FND_FLEX_VALUES_TL:

This table captures the information of each Value Description of a Value Set of a Segment. Each Value Description is uniquely identified by FLEX_VALUE_ID.

Query:

select  FIF.APPLICATION_ID  ,
        FIF.ID_FLEX_CODE    ,
        FIF.ID_FLEX_NAME    ,
        FIF.APPLICATION_TABLE_NAME ,
        FIF.DESCRIPTION     ,
        FIFS.ID_FLEX_NUM    ,
        FIFS.ID_FLEX_STRUCTURE_CODE  ,
        FIFSE.SEGMENT_NAME,
        FIFSE.SEGMENT_NUM,
        FIFSE.FLEX_VALUE_SET_ID
from    FND_ID_FLEXS FIF    ,
        FND_ID_FLEX_STRUCTURES FIFS ,
        FND_ID_FLEX_SEGMENTS FIFSE
where   FIF.APPLICATION_ID = FIFS.APPLICATION_ID
and     FIF.ID_FLEX_CODE   = FIFS.ID_FLEX_CODE
and     FIFSE.APPLICATION_ID = FIF.APPLICATION_ID
and     FIFSE.ID_FLEX_CODE = FIF.ID_FLEX_CODE
and     FIFSE.ID_FLEX_NUM = FIFS.ID_FLEX_NUM
and     FIF.ID_FLEX_CODE LIKE 'GL#'
and     FIF.ID_FLEX_NAME LIKE 'Accounting Flexfield';

Steps required to register a New Key Flexfield (KFF):

1] Create a KFF Table in Module Specific Schema.

2] Create a Public Synonym in APPS Schema.

3] Register the Table with AOL Module.

Go to Application Developer > Functions > Application > Database > Table

We can also register a Table using Application DBA Data Definitions (AD_DD) Package from the Back End.

procedure register_table
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);

procedure register_column
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);

You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables.

4] Register the KFF with AOL Module.

Go to Application Developer > FlexField > Key > Register

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.

GL Budget Interface


GL Budget Interface

This program lets you prepare and analyze your budget outside of General Ledger and then transfer your budget information into General Ledger. This enables you to perform your budgeting in the environment you choose, and still maintain the integrity of your database.

Interface Table:

GL_BUDGET_INTERFACE is used to upload budget data into your Oracle General Ledger application from an external source. Each row includes one fiscal year’s worth of budget amounts for an account. When you load this table, you must supply all NOT NULL columns with data. In addition, you must supply a valid account combination in the SEGMENT columns appropriate to your chart of accounts. Finally, you must supply the budget amounts in the appropriate AMOUNT columns.

The mandatory (not null) columns of the Interface table are:

  • BUDGET_NAME
  • BUDGET_ENTITY_NAME (the budget organization)
  • CURRENCY_CODE
  • FISCAL_YEAR
  • UPDATE_LOGIC_TYPE (A for Add, R for Replace)

Other important columns are:

  • SET_OF_BOOKS_ID
  • BUDGET_VERSION_ID
  • PERIOD1_AMOUNT through PERIOD60_AMOUNT
  • SEGMENT1 through SEGMENT30

Validations:

  • Budget Name and Budget Entity Name
  • Currency Code
  • Account Segments(Code Combination in GL_CODE_COMBINATIONS Table)

To upload a budget:

  1. Navigate to the Upload Budget window (Budgets > Enter > Upload).
  2. Enter the Budget and Budget Organization.
  3. click Upload.

General Ledger submits a concurrent process to upload budget information from the GL_BUDGET_INTERFACE table.

Budget Upload Validation:

Budget Upload validates all of your budget information for compatibility with General Ledger. Budget Upload also checks to make sure that the following conditions are true:

• Your account is assigned to a budget organization

• The budget entry type for your account is Entered

• Your budget is not Frozen

• Your budget organization is not Frozen

• Your budget fiscal year is open for your budget

Once updated, General Ledger automatically deletes the rows of budget records in the Budget Interface table.

Base Tables:

  • GL_BUDGETS
  • GL_BUDGET_ASSIGNMENTS
  • GL_BUDGET_TYPES

For more information see Oracle General Ledger User Guide