Oracle SLA Questions and Answers

Also refer to the blog "Interview pointers" section in header page for more details....

Is SUBLEDGER ACCOUNTING a different Module?

No, it is not a separate module; it does not have a separate responsibility for the users to

login. Subledger Accounting Method menus are clubbed together with the respective

Subledger modules wherever applicable.

Every Module, which is covered by a Subledger accounting method, will have Subledger

accounting method menu in it. One cannot make a setup for one module from different

module, i.e. one cannot make a change on the Subledger setup of payables module from

receivables module. Since Subledger menu available in each module are specific to the

setups in that module only.

Is it mandatory to define a Subledger accounting method during Release 12

implementation?

One must understand that every fresh installation of oracle apps comes with 5 seeded

accounting methods stated earlier, which are sufficient enough to meet the generic

requirements of the client and business. However due to different statutory requirements

through out the world and different nature of businesses, accounting requirements varies

between client to client. In those circumstances alone, based on the clients requirement

one can define their own Subledger accounting method

What will happen if I made alterations to the existing seeded Subledger accounting

methods?

You cannot make alterations to the seeded Subledger accounting methods provided by

oracle. In any Subledger form whenever you see ORACLE name under the owner field it

means that, those setups are owned by ORACLE, which cannot be altered.

If I want to make very few changes to the existing Subledger accounting method seeded

by oracle, since it meets 99% of my business requirement apart from minor changes, and

I don’t want to create a new Subledger method from scratch in order to meet the few

unique requirements, what can be done?

In such a case oracle provides you with an option of copying a seeded Subledger

accounting method and make alterations in the copy and use it as a Subledger method for

your business. So that you need not perform all the setups required for a Subledger

method.

Oracle Applications R12 – Subledger Accounting

While creating a Subledger accounting method, I have created few conditions,

which are illogical, but the system hasn’t stopped me from proceeding. Does oracle

checks each and every setup created for a new Subledger accounting method at

every stage?

Oracle allows you to create only logical setups in a Subledger accounting method, even

though the validations are not performed at each and every stage, after performing all the

setups required for a Subledger accounting method , you must validate the Subledger

accounting method as a whole in order to make it available for usage. At that time system

will prevent you from proceeding with all illogical combinations.

I have made a change in my Subledger accounting which was working fine

previously, but after making changes, it is not performing any accounting operation

at all, why is that?

Whenever you make any change to any part of Subledger accounting method, saving

those changes alone is not enough, you need to validate it again only then the changes

made will come in to effect. Whenever you face any such errors please always check the

Subledger accounting method form, whether the validate button is enabled or disabled. If

it is enabled means, the Subledger accounting method requires validation, if it is disabled,

then it does not allow you to validate further, since it is already validated.

Due to the recent changes in statute, my accounting method needs to be changed

with immediate effect, should I implement a new ledger with new accounting

method to proceed further?

No need for that. You can change your Subledger accounting method from the

Accounting Setup Manager screen itself. The new transactions, which are going to be

entered, will be accounted as per the new accounting method attached.

For what ledgers one can setup Subledger accounting options?

For Primary and Secondary ledgers

How has to one link the accounting method to the Subledger module used, is there a

profile option?

All subledgers assigned to a ledger inherit the Subledger Accounting method assigned for

that ledger.

I need to stop all accounting related operations to one of my Subledgers, will that be

possible?

Oracle Applications R12 – Subledger Accounting

Yes, you can disable the Subledger accounting for a particular application, however it is

available only for secondary ledgers. Subledger Accounting will generate no entries if the

application is in disabled status.

I have created a new SLA method, which is used for all subledgers. In future if I

need to upgrade to higher version, does the new patches or codes affect my SLA

method created? Will I be required to recreate it again?

Definitions created by users are not overwritten by upgrades to Subledger Accounting.

However upgrades can still affect definitions if your Subledger Accounting method uses

the seeded components.

What are Transaction Chart of Accounts and Accounting Chart of Accounts?

For recording of transactions one will use the Transaction Chart of Accounts and for

generating journal entries one will use the Accounting Chart of Accounts.

The usage or the distinction means more only when the user implements Secondary

ledgers in his setups, if the user does not have secondary ledgers, then for his primary

ledger the transaction and accounting chart of accounts remains one and the same.

In case of Secondary ledgers how the system correlates between Transaction chart

of accounts and Accounting Chart of Accounts?

One has to perform Chart of Accounts Mapping between Transaction and Accounting

chart of accounts.

Oracle Applications R12 – Subledger Accounting

POINTS TO REMEMBER

One can never be able to alter an seeded accounting method i.e. accounting

method owned by oracle, we can only take a copy of the seeded accounting

method and change the validations and insert rules, where ever it is appropriate.

Subledger accounting method created by Oracle cannot be modified or deleted.

Subledger accounting does not interfere with Auto Accounting or Workflow

Account Generator for account generation, which continues to perform the same

operations as it was in earlier versions.

One cannot define an illogical or impossible accounting rule since it allows you to

define, test and validate accounting rules; therefore, journal entries generated by

SLA have very little risk of error.

If you want to make changes to the standard seeded Subledger accounting

method, then you can make a copy of the available Subledger accounting and

make the necessary changes you wanted in it.

Whenever you make alteration to any rules to any of the components in Subledger

accounting, you need to validate your SLA method again, only then you can use

it, else it will throw an error.

The transaction and accounting charts of accounts are always the same for the

primary ledger.

The Subledger Accounting option is visible only for secondary ledgers in the

ledger options view described in Subledger Accounting Options Setup Pages.

Subledger Accounting will generate no entries for an application if Subledger

Accounting is disabled for the ledger.

Use Primary Ledger Amount field is only displayed for a secondary ledger if the

corresponding Subledger application has been enabled for Valuation Method.

Oracle Performance Tuning Tips !!

Performance of the SQL queries of an application often play a big role in the overall performance of the underlying application. The response time may at times be really irritating for the end users if the application doesn't have fine-tuned SQL queries. There are several ways of tuning SQl statements

The following is a list of some tips which can be used as guideline to write and review custom SQL queries. This is by no means an exhaustive list to get the best tuning results but can serve as a ready reference to avoid the common pitfalls while working with Oracle SQL:

1. Do not use the set operator UNION if the objective can be achieved through an UNION ALL. UNION incurs an extra sort operation which can be avoided.

2. Select ONLY those columns in a query which are required. Extra columns which are not actually used, incur more I/O on the database and increase network traffic.

3. Do not use the keyword DISTINCT if the objective can be achieved otherwise. DISTINCT incurs an extra sort operation and therefore slows your queries down.

4. If it is required to use a composite index, try to use the “Leading” column in the “WHERE” clause. Though Index skip scan is possible, it incurs extra cost in creating virtual indexes and may not be always possible depending on the cardinality of the leading columns.

5. There should not be any Cartesian product in the query unless there is a definite requirement to do so. I know this is a silly point but we all have done this mistake at one point 

6. Wherever multiple tables are used, always refer to a column by either using an alias or using the fully qualified name. Do not leave the guess work for Oracle.

7. SQL statements should be formatted consistently (e.g the keywords should be in CAPS only) to aid readability. Now, this is not a performance tip really. However, it’s important and part of the practices.

8. If possible use bind variables instead of constant/literal values in the predicate filter conditions to reduce repeated parsing of the same statement.

9. Use meaningful aliases for tables/views

10. When writing sub-queries make use of the EXISTS operator where possible as Oracle knows that once a match has been found it can stop and avoid a full table scan (it does a SEMI JOIN).

11. If the selective predicate is in the sub query, then use IN.

12. If the selective predicate is in the parent query, then use EXISTS.

13. Do not modify indexed columns with functions such as RTRIM, TO_CHAR, UPPER, TRUNC as this will prevent the optimizer from identifying the index. If possible perform the modification on the constant side of the condition. If the indexed column is usually accessed through a function (e.g NVL), consider creating a function based index.

14. Try to use an index if less than 5% of the data needs to be accessed from a data set. The exception is a small table (a few hundred rows) which is usually best accessed through a FULL table scan irrespective of the percentage of data required.

15. Use equi-joins whenever possible, they improve SQL efficiency

16. Avoid the following kinds of complex expressions:

• NVL (col1,-999) = ….
• TO_DATE(), TO_NUMBER(), and so on

These expressions prevent the optimizer from assigning valid cardinality or selectivity estimates and can in turn affect the overall plan and the join method

17. It is always better to write separate SQL statements for different tasks, but if you must use one SQL statement, then you can make a very complex statement slightly less complex by using the UNION ALL operator

18. Joins to complex views are not recommended, particularly joins from one complex view to another. Often this results in the entire view being instantiated, and then the query is run against the view data

19. Querying from a view requires all tables from the view to be accessed for the data to be returned. If that is not required, then do not use the view. Instead, use the base table(s), or if necessary, define a new view.

20. While querying on a partitioned table try to use the partition key in the “WHERE” clause if possible. This will ensure partition pruning.

21. Consider using the PARALLEL hint (only when additional resources can be allocated) while accessing large data sets.

22. Avoid doing an ORDER BY on a large data set especially if the response time is important.

23. Consider changing the OPTIMIZER MODE to FIRST_ROWS(n) if the response time is important. The default is ALL_ROWS which gives better throughput.

24. Use CASE statements instead of DECODE (especially where nested DECODEs are involved) because they increase the readability of the query immensely.

25. Do not use HINTS unless the performance gains clear.

26. Check if the statistics for the objects used in the query are up to date. If not, use the DBMS_STATS package to collect the same.

27. It is always good to understand the data both functionally and it’s diversity and volume in order to tune the query. Selectivity (predicate) and Cardinality (skew) factors have a big impact on query plan. Use of Statistics and Histograms can drive the query towards a better plan.

28. Read explain plan and try to make largest restriction (filter) as the driving site for the query, followed by the next largest, this will minimize the time spent on I/O and execution in subsequent phases of the plan.

29. If Query requires quick response rather than good throughput is the objective, try to avoid sorts (group by, order by, etc.). For good throughput, optimizer mode should be set to ALL ROWS.

30. Queries tend to perform worse as they age due to volume increase, structural changes in the database and application, upgrades etc. Use Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM) to better understand change in execution plan and throughput of top queries over a period of time.

31. SQL Tuning Advisor and SQL Access Advisor can be used for system advice on tuning specific SQL and their join and access paths, however, advice generated by these tools may not be always applicable (point 28).

32. SQL Access paths for joins are an component determining query execution time. Hash Joins are preferable when 2 large tables need to be joined. Nested loops make work better when a large table is joined with a small table.