July 2011 | Oracle Apps

Naming convention of oracle apps DB objects

Following is the naming convention that oracle apps uses on its Database objects:

Object name ending with	Description of the Object
_B	The Main base tables
_ALL	Contains multi-org data
_V	View created on base table
_TL	Table that supports multi language
_VL	View created on multi language tables
_F	Data tracking tables. Used in HRMS
_S	Sequence related table
_DFV / _KFV	The DFF/KFF table created on the base table

Oracle apps PLSQL interview questions - 2

1) What is ERP? A packaged business software system that lets a company automate and integrate the majority of its business processes; share common data and practices across the enterprise; [and] produce and access information in a real-time environment.

Some of the important Join conditions between Oracle apps modules

GL AND AP

GL_CODE_COMBINATIONS AP_INVOICES_ALL

code_combination_id = acct_pay_code_combination_id

GL_CODE_COMBINATIONS AP_INVOICES_DISTRIBUTIONS_ALL

code_combination_id = dist_code_combination_id

GL_SETS_OF_BOOKS AP_INVOICES_ALL

set_of_books_id = set_of_books_id

GL AND AR

GL_CODE_COMBINATIONS RA_CUST_TRX_LINE__GL_DIST_ALL

code_combination_id = code_combination_id

How to use table indexes for better performance

Table index is one of the most important areas, while optimizing a query.

Here is the best practice of using these indexes, to get the optimal performance gain.

1. Consider creating indexes 'only' on those columns which are frequently used in WHERE, ORDER BY and GROUP BY clauses.

2. Do not create duplicate or un-necessary indexes. These indexes hampers insert, delete and update performance of any query. Consider dropping any such indexes, if already there.

3. Make sure table/index statistics are updated regularly.

SELECT Syntax statement

SELECT [hint][DISTINCT] select_list

FROM table_list

[WHERE conditions]

[GROUP BY group_by_list]

[HAVING search_conditions]

[ORDER BY order_list [ASC | DESC] ]

[FOR UPDATE for_update_options]

select_list
column1, column2, column3

table.column1, table.column2

table.column1 C_1_Alias, table.column2 C_2_Alias

schema.table.column1 Col_1_Alias, schema.table.column2 c_2_Alias

schema.table.*

expr1, expr2

(subquery [WITH READ ONLY | WITH CHECK OPTION [CONSTRAINT constraint]])

In the select_lists above, 'table' may be replaced with view or snapshot.
Using the * expression will return all columns. If a Column_Alias is specified this will appear at the top of any column headings in the query output.

Concurrent Program Monitoring Scripts

Most frequently in support projects we need to monitor and track the concurrent programs. Here are some the important queries to track the status of concurrent program. These are also very useful for the oracle apps system administrator

SQL to identify the query which takes long time

Step1 : Run the first query , this will list all the programs that currently running in Application. Take the SID and use it in the second query.

Oracle Apps Account Receivable tables

AR Table	Description
HZ_SRCH_CPTS	This table stores the transformed data for the contact points in the interface tables.
HZ_SRCH_CONTACTS	This table stores the transformed data for the contacts in the interface tables.
HZ_SRCH_PSITES	This table stores the transformed data for the addresses in the interface tables.
HZ_SRCH_PARTIES	This table stores the transformed data for the parties in the interface tables.
HZ_STAGED_PARTY_SITES	Party Site information that is staged for DQM.

Make a DFF Non Editable

How to make a DFF field Non Editable:

Go to the DFF Definition screen for the particular context value
Select the Particular DFF Attribute
Open the Valueset attached to it
In the value set “Edit information”, provide the following values in “Event Edit” & “Event Validate”

FND SQL “BEGIN NULL; END;”

Oracle Apps Online Trainings Videos

Sharing knowledge is not about giving people something, or getting something from them. We believe that "Knowledge Increases By Sharing". This is the main motto in creating our blog.

Visit our online training videos on oracle apps: Click Here

We offer training on the following topics:

1. Oracle Applications Core HRMS

2. Oracle SCM (OM, PO & Other Inventory concepts Needed for OM & PO)

3. OAF – Oracle Application Frame work

4. ADF – Application Development Frame work

5. Oracle Applications Financials

6. SOA – Service Oriented Architecture

Mail to oracleapps4all@gmail.com

Free public access to Oracle Apps 11i/R12 Application Instances

As an application implementer, it is not always easy to access a working demo environment of Oracle EBS. Oracle offers Application Demo Services (ADS) to its partners, but it is limited to Oracle Application Resellers and Systems Integrators. So, for independent consultants, it can be challenging.

Solution Beacon, a certified Oracle partner and a provider of Oracle resources, offers instances of Release 11i Vision for public access.

Interfaces and Conversions in Oracle Applications

I have consolidated all the interfaces and conversions in oracle applications.

Oracle Apps Order Management Tables

Check the blog page: Oracle Apps Order Management setups & flows

Entered
OE_ORDER_HEADERS_ALL	1 record created in header table
OE_ORDER_LINES_ALL	Lines for particular records
OE_PRICE_ADJUSTMENTS	When discount gets applied
OE_ORDER_PRICE_ATTRIBS	If line has price attributes then populated
OE_ORDER_HOLDS_ALL	If any hold applied for order like credit check etc
Booked
OE_ORDER_HEADERS_ALL	Booked_Flag=Y, Order booked.
WSH_DELIVERY_DETAILS	Released_Status Ready to release
Pick Released
WSH_DELIVERY_DETAILS	Released_Status=Y Released to Warehouse (Line has been released to Inventory for processing)
WSH_PICKING_BATCHES	After batch is created for pick release
MTL_RESERVATIONS	This is only soft reservations. No physical movement of stock

Oracle apps PLSQL interview questions - 1

Difference b/w procedure and function? A procedure may return (one or more values using OUT & INOUT Parameters) or may not return a value. But a function has to return a single value and has the return clause in its definition. Function can be called in select statements but procedure can only be called in a pl/sql block. Procedure's parameters can have IN or OUT or INOUT parameters. But function's parameters can only have IN parameters.

Oracle apps interview questions and answers

I have browsed thru many sites for some good oracle apps interview questions. I’ve spent hours in doing so, but could not get the best one. I’ve ended up in preparing some list of questions after searching the net.

You may like
Sample resumes, Oracle apps Jobs, Oracle apps trainings

Hope these will be useful for my oracle apps friends around the world. Happy Reading.

Usually questions will be asked on PL/SQL, technical & functional topics of oracle apps. So i will cover all the 3 topics.

Oracle Apps

PAGES

Pages