Oracle Application – Top useful SQL Queries

Friends, here some of quite useful regular sql queries in oracle applications.

Query 1: Select responsibility name along with application name

SELECT application_short_name ,

frt.responsibility_id, frt.responsibility_name

FROM apps.fnd_responsibility_tl frt , fnd_application fa

WHERE fa.application_id = frt.application_id;

Query 2: Get Menu name for Responsibility ID , You can find out responsibility_id from Query 1

SELECT DISTINCT a.responsibility_name, c.user_menu_name

FROM apps.fnd_responsibility_tl a,

apps.fnd_responsibility b,

apps.fnd_menus_tl c,

apps.fnd_menus d,

apps.fnd_application_tl e,

apps.fnd_application f

WHERE a.responsibility_id(+) = b.responsibility_id

AND a.responsibility_id = &resp_id

AND b.menu_id = c.menu_id

AND b.menu_id = d.menu_id

AND e.application_id = f.application_id

AND f.application_id = b.application_id


Query 3: Get User name and related assigned responsibilities

SELECT distinct u.user_id, u.user_name user_name,

r.responsibility_name responsiblity,

a.application_name application

FROM fnd_user u,

fnd_user_resp_groups g,

fnd_application_tl a,

fnd_responsibility_tl r

WHERE g.user_id(+) = u.user_id

AND g.responsibility_application_id = a.application_id

AND a.application_id = r.application_id

AND g.responsibility_id = r.responsibility_id

order by 1;

Query 4: Get Request Group associate with Responsibility Name

SELECT responsibility_name responsibility, request_group_name,


FROM fnd_request_groups frg, fnd_responsibility_vl frv

WHERE frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name

Query 5: Gets Form personalization listing

Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables

applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes

SELECT ffft.user_function_name “User Form Name”, ffcr.SEQUENCE,

ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,

ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query

FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft

WHERE ffcr.ID = ffft.function_id


Query 6: Query to view the patch level status of all modules

SELECT a.application_name,

DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status,


FROM apps.fnd_application_vl a, apps.fnd_product_installations b

WHERE a.application_id = b.application_id;

Query 7: SQL to view all request who have attached to a responsibility

SELECT responsibility_name , frg.request_group_name,

fcpv.user_concurrent_program_name, fcpv.description

FROM fnd_request_groups frg,

fnd_request_group_units frgu,

fnd_concurrent_programs_vl fcpv,

fnd_responsibility_vl frv

WHERE frgu.request_unit_type = ‘P’

AND frgu.request_group_id = frg.request_group_id

AND frgu.request_unit_id = fcpv.concurrent_program_id

AND frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name;

Query 8: SQL to view all requests who have attached to a responsibility

SELECT responsibility_name , frg.request_group_name,

fcpv.user_concurrent_program_name, fcpv.description

FROM fnd_request_groups frg,

fnd_request_group_units frgu,

fnd_concurrent_programs_vl fcpv,

fnd_responsibility_vl frv

WHERE frgu.request_unit_type = ‘P’

AND frgu.request_group_id = frg.request_group_id

AND frgu.request_unit_id = fcpv.concurrent_program_id

AND frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name;

Query 9: SQL to view all types of request Application wise

SELECT fa.application_short_name,



DECODE (fcpv.execution_method_code,

‘B’, ‘Request Set Stage Function’,

‘Q’, ‘SQL*Plus’,

‘H’, ‘Host’,

‘L’, ‘SQL*Loader’,

‘A’, ‘Spawned’,

‘I’, ‘PL/SQL Stored Procedure’,

‘P’, ‘Oracle Reports’,

‘S’, ‘Immediate’,


) exe_method,

output_file_type, program_type, printer_name,


minimum_length, concurrent_program_name,


FROM fnd_concurrent_programs_vl fcpv, fnd_application fa

WHERE fcpv.application_id = fa.application_id

ORDER BY description

Query 10: SQL to view concurrent request processing time, quite useful

SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name

, f.actual_start_date actual_start_date

, f.actual_completion_date actual_completion_date,


|| ‘ HOURS ‘ ||

floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) –


|| ‘ MINUTES ‘ ||

round((((f.actual_completion_date-f.actual_start_date)*24*60*60) –

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 –

(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) –

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))

|| ‘ SECS ‘ time_difference


DECODE(p.concurrent_program_name,’ALECDC’,p.concurrent_program_name||'[‘||f.description||’]’,p.concurrent_program_name) concurrent_program_name

, decode(f.phase_code,’R’,’Running’,’C’,’Complete’,f.phase_code) Phase

, f.status_code

FROM apps.fnd_concurrent_programs p

, apps.fnd_concurrent_programs_tl pt

, apps.fnd_concurrent_requests f

WHERE f.concurrent_program_id = p.concurrent_program_id

and f.program_application_id = p.application_id

and f.concurrent_program_id = pt.concurrent_program_id

and f.program_application_id = pt.application_id

AND pt.language = USERENV(‘Lang’)

and f.actual_start_date is not null

ORDER by f.actual_completion_date-f.actual_start_date desc;

What you like to know or learn ?

Friends, with new initiative, i want to work and public on some of the requests / suggestion from your side.

Let tell what you like to know or read on this blog , i will work on that topic / subject and will deliver good content for your use. Do leave comment here or send me mail.  I am sure putting article on what you need is much better way to share knowledge.

Keep talking & sharing….. Shivmohan.

Book to Read – Drive: The Surprising Truth About What Motivates Us

From Daniel H. Pink, the author of the bestselling A Whole New Mind, comes a paradigm-shattering look at what truly motivates us and how we can use that knowledge to work smarter and live better.

Most of us believe that the best way to motivate ourselves and others is with external rewards like money—the carrot-and-stick approach. That’s a mistake, Daniel H. Pink says in, Drive: The Surprising Truth About What Motivates Us, his provocative and persuasive new book. The secret to high performance and satisfaction—at work, at school, and at home—is the deeply human need to direct our own lives, to learn and create new things, and to do better by ourselves and our world.

Drawing on four decades of scientific research on human motivation, Pink exposes the mismatch between what science knows and what business does—and how that affects every aspect of life. He demonstrates that while carrots and sticks worked successfully in the twentieth century, that’s precisely the wrong way to motivate people for today’s challenges. In Drive, he examines the three elements of true motivation—autonomy, mastery, and purpose—and offers smart and surprising techniques for putting these into action. Along the way, he takes us to companies that are enlisting new approaches to motivation and introduces us to the scientists and entrepreneurs who are pointing a bold way forward.

Drive is bursting with big ideas—the rare book that will change how you think and transform how you live.

Drive by Dan Pink

Implementing ERP – Selection to Rolling out

When implementing new a ERP system, implementing on time and on budget is not by chance. As with any project plan, a detailed and methodical process is required for success. A plan for selecting and implementing new ERP software is no different than any major project being undertaken by a corporation. It requires a significant investment of time and resources, requires the involvement of virtually the entire organization, as well as a considerable amount of research, planning, and reevaluation along the way.

The best project is well thought out and fully researched. It is not limited to a budget and time line, but focuses on tasks, owners, goals, and milestones. It begins at the time of software selection and goes well beyond go-live. And although most projects will stumble along the way, successful implementations that actually end on time and on budget are quite possible if managed properly.

we preach the importance of a solid selection and implementation plan. So much so, that we will voluntarily chose not to participate in the evaluation project if we feel the prospect’s selection process lacks structure. Why would we ever do that? Because the company will never be happy. If they do not know how to evaluate their own requirements or communicate those needs to others, they’ll never be able to successfully evaluate if a vendor’s ERP solution is a good fit for their unique requirements. And unfortunately if we can’t review their requirements with them, we cannot help evaluate if our package is a good fit either.

A solid evaluation project for new ERP software is broken out into some fairly set project goals and milestones. Regardless of the company, a proper project plan should include:

– A Review of Key Business Objects & Long-term Goals
– A Formation of a Selection Team & Project Leader
– An Ongoing Project Plan
– Research & Budget Preparation
– Clearly Defined Requirements & Project Goals
– Vendor Demonstrations & an RFP
– Reference Checks
– Data Migration
– Testing, Testing, & More Testing
– Training, Training, & More Training

In reality, you can easily execute on time, on task, and on budget by controlling the entire project through conception and go live. Led by in house or through an outside professional, flawless executions of software implementations are possible. They do not happen by chance, on their own, or without effort. But they are certainly a reality if given the right team and project plan.

Infectious Enthusiasm

I almost forgot about it. One of my friends reminded of it, and so i decided to write something. Enthusiasm is infectious. No matter regarding what – good or bad – if you are enthusiastic about it, you will get people involved. You will infect others.

Enthusiasm is a communicable disease. It has no cure. It has no medicine. It is a sweet poison which will make you feel better when you are sick with it.

But some people will find a way to get away from any kind of disease. And i have seen such people who have a shield around them, which will not let them get infected. They have a shield of NEGATIVE THINKING and ATTITUDE that will not let them think good and be enthusiastic. Enthusiasm can not go thru that shield, and people do not get infected.

That is one of the reasons i forgot about my infectious enthusiasm, because i have a few people around me who are shielded. They are vaccinated. And if i stay around them for long, I will loose my powers of infection. They will become penicilling for me. God does not want that, and so he sent me a doctors advice. Thanks to my friend for being a Hanuman for me and remind me of the powers i have.

My enthusiasm has helped me a lot. In achieving things, in reaching places, in creating success. Even making new friends. I wish i can stay the same, and be more enthusiastic in future. I wish to infect more and more people every day, so that they can go and infect others. After all enthusiasm and energy are the things that make this world moving.

Go ahead, Spread it. Enjoy it. Enthusiasm is the key to success.

courtesy – Ashish Jain —

Oracle AIM Methodology

Here i Sharing an Presentation on Oracle AIM, planning to prepare a video tutorial for the same, do share your feedback.

Oracle uses AIM (Application Implementation Methodology) to manage all of its Oracle Application implementation projects. AIM can be used for many different software implementations and not just Oracle Applications. However, the methodology is purpose built for Oracle Applications and the detailed deliverables produced are designed with the Oracle Application products in mind.
The following Oracle Applications are covered by AIM:
 Oracle Financials;
 Oracle Distribution;
 Oracle Human Resources;
 Oracle Manufacturing;
 Oracle Process Manufacturing.
AIM incorporates two things. First, it is a methodology showing what tasks are required, what order they should be completed in, and what resources are required. Secondly, it provides deliverable templates for all the tasks that require them. The combination of a methodology with a deliverable templating tool makes AIM a powerful product.
One disadvantage of AIM is that is very complicated. It involves over 200 deliverables. If you tried to use them all you would be spending 18 months implementing a 3 month project. AIM is supposed to be used by experienced project managers that pick and choose the tasks they require for each project.
Oracle’s AIM is a proven approach for implementing packaged applications. It comprises a set of well defined processes that can be managed in several ways to guide you through an application implementation project. AIM provides the tools needed to effectively and efficiently plan, conduct, and control project steps to successfully implement business solutions.
AIM defines business needs at the beginning of the project and maintains their visibility throughout the implementation. It defines internal, external, and time sensitive business events and maps each event to the responding business and system processes. Using this method, the client gains an accurate understanding of the business requirements that need to be focused on during the course of the implementation.

AIM Structure
AIM is a framework of related elements. It involves phases, processes, tasks and dependencies:
The following Oracle Applications are covered by AIM:
 A task is a unit of work, which results in a single deliverable. That deliverable may take many different forms like reports, schedules, code, or test results for example.
 A process is a closely related group of dependent tasks which meets a major objective. A process is usually based on a common discipline.
 A phase is a chronological grouping of tasks. It enables a flexible way to organise tasks, schedule major deliverables, and deliver projects.
Processes and phases are explained in more detail below.

A process in AIM represents a related set of objectives, resource skill requirements, inputs, and deliverable outputs. A task can belong to only one process. Project team members are usually assigned to a process according to their specialisation and background. A brief description of the AIM processes are given below:
1. Business Requirements Definition

Business Requirements Definition defines the business needs that must be met by the implementation project. You document business processes by identifying business events and describing the steps that respond to these events.

2. Business Requirements Mapping

Business Requirements Mapping compares the business requirements to standard application software functionality and identifies gaps that must be addressed to fully meet business needs. As gaps between requirements and functionality emerge, they are resolved by documenting workarounds, alternative solutions, application extensions, or by changing the underlying business process.

3. Application and Technical Architecture

During the Application and Technical Architecture you design an information systems architecture that reflects your business vision. Using the business and information systems requirements, this process facilitates development of a plan for deploying and configuring the hardware required for a successful implementation.

4. Module Design and Build

Module Design and Build produces custom software solutions to gaps in functionality identified during Business Requirements Mapping. Custom software solutions include program modules that must be designed, built, and tested before they can be incorporated into the system.

5. Data Conversion

Data Conversion defines the tasks and deliverables required to convert legacy data to the Oracle Applications tables. The first step of this process explicitly defines the business objects that are required for conversion and the legacy source systems that store these objects. The converted data may be needed for system testing, training, and acceptance testing as well as for production.

6. Documentation

Documentation begins with materials created early in the project. Using detailed documents from the project, the writing staff develops user and technical material that are tailored to the implementation.

7. Business System Testing

Business System Testing focuses on linking test requirements back to business requirements and securing project resources needed for testing. It supports utilising common test information including data profiles to promote testing co-ordination and to minimise duplication of test preparation and execution effort.

8. Performance Testing

Performance Testing enables you to define, build, and execute a performance test. Use the results to make decisions on whether the performance is acceptable for the business and to help propose tactical or strategic changes to address the performance quality shortfall. Performance Testing is closely related to Application and Technical Architecture; they are interdependent.

9. Training

Training prepares both users and administrators to assume on the tasks of running the new application system. It includes development of materials and methods as well as administration. Instructors and courseware developers orient their material toward roles and jobs, and not toward application modules.

10. Production Migration

Production Migration moves the company, system, and people to the new enterprise system. Following production cutover, it monitors and refines the production system and plans for the future. The Production Migration process encompasses transition to production readiness, production cutover, and post-production support.

An AIM project is conducted in phases that provide quality and control checkpoints to co-ordinate project activities that have a common goal. During a project phase, your project team will be executing tasks from several processes. A brief description of the AIM processes are given below:
1. Definition

During Definition you plan the project, review the organisation’s business objectives, and evaluate the feasibility of meeting those objectives under time, resource, and budget constraints. The emphasis is on building an achievable work plan and introducing it with guidelines on how the organisation will work to achieve common objectives. Establishing scope early in the implementation gives the team a common reference point and an effective way to communicate. Strategies, objectives, and approaches are determined for each AIM process, providing the basis for the project plan.

2. Operations Analysis

During Operations Analysis, the project team develops Business Requirements Scenarios based on deliverables from Definition that are used to assess the level of fit between the business requirements and standard application functionality. Gaps are identified and corresponding solutions developed. The analysis results in a proposal for conducting business operations under the envisioned application technical architecture. Solutions for gaps evolve into detailed designs during Solution Design.

3. Solution Design

The purpose of Solution Design is to develop the detailed designs for the optimal solutions to meet the future business requirements. During this phase, project team members create detailed narratives of process solutions developed during Operations Analysis. Supporting business requirements may require building application extensions to standard features; several alternative solutions may have been defined during Operations Analysis. The project team carefully scrutinises these solutions and chooses the most cost effective alternatives.

4. Build

The coding and testing of all customisations and other custom software including enhancements, data conversions, and interfaces is done during Build. Policy and procedure changes relating to business process modifications are developed. Business system testing is performed to validate that the developed solutions meet business requirements.
If customisations, extensions, or conversions are not required, Build is still important because it includes the business system test, which is commonly conducted as a formal conference room pilot. The business system test validates the solutions and is performed in an environment that closely resembles production.

5. Transition

During Transition, the project team deploys the finished solution into the organisation. All the elements of the implementation must come together to transition successfully to actual production. The project team trains the end users while the technical team configures the production environment and converts data. Transition ends with the cutover to production, when end users start performing their job duties using the new system.

6. Production

Production begins immediately with the production cutover. It marks the last phase of the implementation, and the beginning of the system support cycle. Included in this final phase is a series of refinements and performance measurement steps. The Information Systems (IS) personnel work quickly to stabilise the system and begin regular maintenance. They will provide the ongoing support to the organisation for the remaining life of the system. During Production, you compare actual results to project objectives. System refinement begins in a controlled manner to minimise impact to end users. Finally, you start preliminary planning of the future business and technical direction of the company.