Oracle EBS 11i setup -Set of Books setup

In this post we will see how to setup a new Set of books in Oracle. We will set of a new set of books for a company called IPL for it's India operations.

Pre-requisite for setting up Set of Books in Oracle is,
1. Chart of Accounts (COA) setup
Check out the earlier video on COA setup video. I have considered a 5 segment chart of accounts as part of our DEMO setups for a company called IPL.

2. Calender

3. Currency

This is the well known 3C (chart of accounts, calender, currency) for SOB setup in Oracle. Once the above pre-requisites are setup, set of book can be defined.

Some other key elements for setting up of SOB apart from the above are,

a. Retained earnings account: This is the share holders / equity account. Oracle GL make necessary updates to this account whenever you do transactions in expense / income account through journals / subledgers etc. The account type for this account should be "Ownership/Stockholder's Equity"

b. Translation Adjustment account: This is the account used by Oracle GL for any difference happen (like difference due to rounding) while you translate your account level transactions from functional Currency to any other currency to make your journal balanced at account level in your translated currency. Again the account type for hti saccount is "Ownership/Stockholder's Equity"

Check out the following video for setting up Calender, currency and set of books,

Oracle EBS 11i Setup -Chart of Accounts Setup

Chart of Accounts setup in Oracle financials is the most important setup. Thorough analysis, consideration must be done before finalizing the chart of accounts of any organization with inputs from finance department such as reporting requirement, level at which transaction details are required etc.

Following video shows how to setup Chart of Accounts in Oracle EBS 11i,

Oracle EBS 11i - New Entity Setup

I have been trying to post this since long but due to current workload and bandwidth issues was not able to post this. Here I am going to explain the steps to setup a new entity / operating unit in Oracle EBS 11i. This is from my experience of setting up new books and subledger and is completely practical. I was at a client site, one of the top internet companies which has businesses spanning almost all of the countries in the world for which we had to setup GL books and subledger for the newly added business regions.

Steps to define a New Entity in Oracle EBS 11i,

Step 1 - Define Set of Books (SOB)
Before defining SOB, you have to setup followings-
1.1 Define "Chart of Accounts"
>Define "Value sets" for Accounting Segments
>Define "Chart of Account structure"
>Define "Chart of Account Segments"
>Define "Segment Values"
Check out this video for setting up Chart of Accounts,



1.2 Define "Calender" for the SOB

1.3 Define "Currency" for the SOB

1.4 Define Set of Books (SOB)
Check out the following video for 1.2, 1.3, 1.4


Step 2 - Define GL responsibilities and assign following profile values,
a> Assign profile "GL Set of Books Name", value: Enter the new SOB name at responsibility level
b> Assign profile "HR: User Type", value:"HR User" at responsibility level

Step 3 - Define New Legal Entity
2.1 Define Location for New Legal Entity
2.2 Define the New Legal Entity

Step 4 - Define Opearting Unit

Step 5 - Define Inventory Organization
Define Receiving options (Pay special attention while assigning accounting code combinations)

Step 6 - Navigate to "System Administrator" responsibility > Go to submit request screen > Run "Replicate Seed Data" request > Enter the new operating unit name as the parameter for the request

Step 7 - Define subledger responsibilities (Setup, Transaction, Manager, Inquiry) for the new operating unit and assign following profiles at responsibility level,

a> "GL Set of Books Name", value: Enter the new SOB name
b> "HR: User Type", value:"HR User"
c> "MO: Operating Unit", value:Enter the new OU name
d> "MO: Top Reporting Level", value:"Operating Unit"
e> Optionally setup profile "AP: Use Invoice Batch Controls" if you want to use Invoice Batch workbench
f> Optionally setup profile "Sequential Numbering"

Step 8 - Define Cross validation rules

Step 9 - Define Security rules and assign to newly created responsibilities

Step 10 - Request for the new responsibilities

Step 11 - Setup Purchasing

Click here for Purchasing setup

Step 12 - Setup Payables

Click here for Payables setup.

Step 13 - Open first GL period

Step 14 - Open first Payable period

Step 15 - Open first Purchasing period

Zero Payment -Payments

Here we will be discussing about,
What is "Zero Payament" payment
Why is "Zero Payment" payment required
What are the required setups for "Zero Payment" payments in Oracle payables
How to do "Zero Payment" payments in Oracle payables

What is "Zero Payment" Payment?

As the name suggests, a "Zero Payment" payment is a payment having value 0 (zero) i.e. the payment can be 0 USD / EUR / INR etc depending on the payment currency.

Why is "Zero Payment" payment required?

There can be various reasons to make a "Zero payment". Few possibles reasons are,
--Legal requirements of the country
There can be a legal requirements in countries (like EMEA) where business is being carried out / transactions are booked, wherein you just can not cancel an accounted invoice. To cancel such invoices you need to generate an internal DEBIT memo or CREDIT memo (for paid invoices) and then apply to the invoice which needs to be canceled. In such case the original invoice is not canceled and you are just making ZERO liability to the supplier for the invoice. By doing so, the invoice remains unpaid and also the accounting transaction does not flow through all the events like payment, cash clearing. For all invoices the transactions need to flow through all the events as mentioned above if it has not been canceled.
In order to acheive that you make a "Zero Payment" payment just to record the transaction. This payment would not go to the supplier.

--Accounting segment values getting deactivated
If any of the charge account segment is deactivated (like cost center / natural account etc) then an accounted invoice can not be canceled and the only alternative to make the liability zero is to create a debit memo or CREDIT memo (for paid invoices) and then apply to the invoice which needs to be canceled as mentioned above.

Below video shows,
--Required setup to be done in Oracle for creating "Zero Payment" payments
--Creating a "Zero Payment" payment

11i Vs R12

Difference between 11i & R12

1. Oracle Purchasing
a) Document Types (Setup > Purchasing > Document Types)
11i - The form is Oracle native form based.
R12 - The GUI is self-service OAF page. As shown below,

b) Purchasing Options (Setup > Organizations > Purchasing Options)
11i - The form is Oracle native form based.
R12 - The GUI is self-service OAF page. As shown below,

c) Payables workbench is changed. In R12, now you can directly mention the PO number in the first screen itself instead of going to next screen saving 1 click compare to R11i.

d> Buyers setup GUI is self-service page.
e> Financial Options:
--GUI is still core form.
--Now no need to switch responsibilities to setup multi-org financial options.

f> Receiving Options - GUI is self-service page. No functionality change.

No Change-
1. Oracle Purchasing
a) No change in Purchase Order entry workbench-This remains the same as in 11i.
b> No change in Auto-create PO
c> PO workbench is mostly same

R12:How MOAC enviornment is setup programmatically

Pre-requisites for understanding how MOAC works in R12 are (Although these are not mandatory but knowledge of these would definitely help),

1. Setting ORG context prior to R12 version
Prior to R12, if you want to set ORG context in Oracle apps on any multi org table then following need to be done,

a. Create the table
b. Create a view on the table with following condition

WHERE NVL (org_id,
NVL (TO_NUMBER (DECODE (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 1),
' ', NULL,
SUBSTRB (USERENV ('CLIENT_INFO'), 1,
10)
)
),
-99
)
) =
NVL (TO_NUMBER (DECODE (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 1),
' ', NULL,
SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)
)
),
-99
);

As an example, let us consider the PO_HEADERS_ALL table. This is owned by PO schema having records for every operating unit. To implement Org security, a view has been created on top of it i.e. PO_HEADERS and is being owned by APPS schema.

If you see the content of this view, you would see the view has been created by accessing the value of CLIENT_INFO session field value which is being set by respective applications like PO, AP using DBMS_APPLICATION_INFO.SET_CLIENT_INFO API.

So, if you want to give the access only for US operating unit (lets assume the ORG_ID to be 100) then you set

begin
dbms_application_info.set_client_info(100);
end;

If you query PO_HEADERS after the above statement is executed in your session then you would see all the POs only for operating unit 100. This is to secure the data as per the user access,

You can also use FND_CLIENT_INFO.SET_ORG_CONTEXT API to set the ORG context which ultimately uses the above to set the ORG context.

begin
fnd_client_info.set_org_context(100);
end;

This is how all the applications (PO, AP etc) set the CLIENT_INFO field and set the ORG_CONTEXT prior to R12.


2. RLS (Row level security)

RLS feature was introduced in Oracle since Oracle 8i. The prime feature of RLS is implementing row level security while reading / inserting / deleting the data from the Database. In brief, you can use RLS to restrict data based on user permissions / privileges.


How MOAC works in R12
In R12, Oracle applications use RLS feature to implement MOAC.

Now going back to our same example- PO_HEADERS_ALL table.
In R12 these tables are still owned by PO schema but what changed is the view now no longer exist in R12. In R12, the multi org based views have been replaced with SYNONYMS in APPS.
So, for PO_HEADERS_ALL you now would see PO_HEADERS synonym in APPS schema pointing to APPS.PO_HEADERS_ALL table.

Now if you query the following in R12 APPS DB,

SELECT * FROM DBA_POLICIES WHERE OBJECT_NAME='PO_HEADERS'

You would see the following,

As seen in the picture above, whenever you query PO_HEADERS in R12, the DB is going to apply the policy defined on this synonym which in turn would call MO_GLOBAL.ORG_SECURITY API. This API returns the predicates which would be automatically applied to the query by Oracle DB.
If you check the content of this API, you would see that it basically prepares the where condition for the ORG_ID to which user has access to, which is being read from the TEMP table MO_GLOB_ORG_ACCESS_TMP.
MO_GLOBAL.INIT API is there in "prior to R12" releases but it was not being used in the RLS and there is no policy defined on the multi-org enabled tables / views.

The temp table is populated when you set user / org context.

Prior to R12, FND_CLIENT_INFO.SET_ORG_CONTEXT is used to set the ORG context
This would set the ORG Context and then the multi org related views can be accessed which would return records only for that operating unit.


But from R12, following is used to set the org context

--Use FND_GLOBAL.APPS_INITIALIZE API to set the user environment
--Use MO_GLOBAL.INIT to populate the temp table MO_GLOB_ORG_ACCESS_TMP based on the responsibilities to which the user has access to and which would then be used by the RLS security policy.

After you set these up, if you query PO_HEADERS synonym then Oracle DB would automatically add the predicate (the list of operating units to which user has access to) to the query and would only return rows for the operating units to which user has access to.

For e.g. run the following in R12,
(Refer my earlier post on "how to setup MOAC in R12" to see the operating units that OPERATIONS user has access to)

begin

FND_GLOBAL.APPS_INITIALIZE(1318 ---user 'OPERATIONS'
,20639 ---responsibility "Payables Manager"
,200 ---Application "Oracle Payable"
);

MO_GLOBAL.INIT('SQLAP');


end;

Then query the temp table,
select * from MO_GLOB_ORG_ACCESS_TMP

Now the query returns the list of operating units to which OPERATIONS user has access to. And if you query from PO_HEADERS, you would see result from these operating units.

Summary:
A. Oracle uses RLS to implement MOAC in R12
B. All multi-org tables now have synonym in APPS
C. The synonyms now have a policy defined which can be found in DBA_POLICIES
D. To setup multi-org context in R12 from the back end programmatically,
1. Call FND_GLOBAL.APPS_INITIALIZE
2. Call MO_GLOBAL.INIT

Oracle Applications 11i: Setup Queries

While doing any setup in Oracle Applications 11i, few questions always come in mind,

--Am I doing the right setup?
--Is it consistent with other Operating units except few intended differences specific to this Operating unit?
--Does it adhere to the overall naming conventions?

So, to address all the above questions and simplify setups in Oracle Applications 11i, I have listed some Oracle Financial and FND setup related queries. Executing these queries would most likely address the above questions and minimize human errors.

1. Oracle Financials 11i / Set of Books Query-


2. Oracle Payables Internal Bank Setup Query-