Oracle Application – Top useful SQL Queries

Find reps name with conc prg name
---------------

SELECT responsibility_name
FROM fnd_responsibility_vl
WHERE request_group_id IN
  (SELECT request_group_id
  FROM fnd_request_group_units
  WHERE request_unit_id IN
    (SELECT concurrent_program_id
    FROM fnd_concurrent_programs_tl
    WHERE user_concurrent_program_name LIKE 'XXAP - Remittance Advice Send Mail Supplier'
    )
  )


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 f.application_id = b.application_id
AND a.language = 'US';


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,
frg.description
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
ORDER BY 1;


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,
patch_level
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,
fcpv.user_concurrent_program_name,
description,
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’,
fcpv.execution_method_code
) exe_method,
output_file_type, program_type, printer_name,
minimum_width,
minimum_length, concurrent_program_name,
concurrent_program_id
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,
floor(((f.actual_completion_datef.
actual_start_date)*24*60*60)/3600)
|| ‘ HOURS ‘ ||
floor((((f.actual_completion_datef.
actual_start_date)*24*60*60) -
floor(((f.actual_completion_datef.
actual_start_date)*24*60*60)/3600)*3600)/60)
|| ‘ MINUTES ‘ ||
round((((f.actual_completion_datef.
actual_start_date)*24*60*60) -
floor(((f.actual_completion_datef.
actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_datef.
actual_start_date)*24*60*60) -
floor(((f.actual_completion_datef.
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 is cost center used for in Oracle EBS?

Cost center is one of the accounting flexfield components in Oracle General Ledger, which is part of Oracle Financial modules in Oracle EBS
The accounting flexfield may have segments like:
1 Company
2 Dept......[cost Center]
3 Account
4 Sub-Account
5 Product

Cost centers indicate functional areas of your organization, such as Accounting, Facilities, Shipping, and so on.....

Oracle Assets Module and Oracle Projects Module requires to qualify a segment as cost center in your account

Benefits are you can group your Assets by Cost Center or Department, ascertain project cost for a particular project based on cost centers and so on...


 Cost center is a department in an Organization.
Then again it depends on your line of business. LOB.
For example if your Operating units are Vision Ops US and Vision Ops UK you will have different cost centers tied to these two units.

A cost center will depend on your company and how it views things. Keep in mind this is still part of the g/l account accounting flexfield. A key flexfield will contain its own value set, independent of other Oracle tables. Now you may create values in the value set for the segment that is the cost center that may correspond to, say, your HR Organization untis, but that will be only because you created them that way. If you add a new HR Unit, it will NOT show in the cost center value set until someone goes into that maintenance and adds the value there as well. Just have to understand that relationship.

Subquries

Types of Subquries:-

     Single Row sub queries: - return zero rows or one row to the outer SQL stmt.There is a special case of single row that contains exactly one column, this type of sub queries are called as scalar sub query.

Multiple row sub queries: - return one or more rows to the outer SQL statement

In addition there are three subtypes of sub queries that may return single row or multiple rows.

Multiple – column sub queries :- return more than one column to the outer SQL stmt

Correlated sub queries :- reference one or more columns in the outer SQL statement .These are called correlated sub queries because they are related to the outer SQL stmt through the same columns.

Nested sub queries :-  are placed within another sub query. you can nest sub queries to depth of 255.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

     Single Row sub queries: -

                              

1)      select name,salary from emp where empno = (select empno from emp where name = ‘RAJESH’);

2)      select product_id,name ,price from products where price > (select avg(price) from products );

3)      select product_type_id,avg(price) from products group by product_type_id having avg(price) < (select max(avg)price)) from products group by product_type_id order by product_type_id;

4)      sub queries in from clause(Inline Queries)

        

        select product_id from (select procust_id from products where product_id <3);

        select prds.product_id,price,purchase_data.product_count from products prds,

                  (select product_id ,count(product_id) product_count from purchases

                    Group by product_id) purchase_data

        Where prds.product_id =  purchase_data.prodcut_id;

Here the sub query is just another source of data to the from clause of the outer query.

Errors you might encounter

    Single row sub queries may return maximum of one row . If your sub query return one row you will get the following error

ORA-01427 single-row subquery return more than one row.

    Sub queries may not contain an order by clause . instead any ordering must be done in the outer query .for example

Select product_id ,  name , price fromm products where price > (select avg(price) frin products )order by product_id desc;

Multiple row sub queries

     You use a multiple – row sub query to return one or more rows to outer SQL stmt.TO handle this case your outer query may use IN,ANY,or ALL operator.you can also use the EXISTS operator to check of a value is in a list returned by a correlated sub query.

IN:-

1)   Select product_id,name from products where product_id IN (select product_id from products from products where name like ‘%Laptop%’);

 2)  Select product_id,name from products where product_id NOT IN (select product_id from products from products where name like ‘%Laptop%’);

ANY:-

1)      select empno,nme from emp where salary < ANY (select low_salary from salary_grades);

      ALL:-

         Select empno,name form emp where salary > ALL(select high_salary form salary_grades);

        Results :-  no rows selected

        Multiple Column sub queries :-

 

             Select product_id ,product_type_id name ,price from products where (product_id,price ) IN    (select product_id,MIN(price) from products group by product_type_id);

     Note that sub query returns more than one column .

Correlated Subquries:-

     Correlated sub queries references one or more columns in the outer SQL stmt.

You typically use a correlated sub queries when you need an answer to a question that depends on a values in each row contained in an outer query.For example you might want to see whether there is a relationship between the data but don’t care how many rows are returned by the sub query.

That is you just wan tot check whether any rows are retrned but you don’t care how many.

    Correlated sub query us run in once for each row in the outer query: this is different from non – correlated sub query , which is run once prior to running the outer query.in addition a correlated sub query can resolve null values.

Select product_id ,produt_type_id,name,price

From products outer

Where price >

   (select avg(price) from products inner where inner.product_type_id = outer.product_type_id);

Using Exists ,Not Exists

   Select empno,name

   From emp

    Where exists

               (select emono from emp inner

                 Where inner.manager_num = outer.empno);

Or

   Select empno,name

   From emp

    Where exists

               (select 1 from emp inner

                 Where inner.manager_num = outer.empno);

Exists and Not Exists Versus In and NOT IN

    Exists is different from IN : Exists checks just for the existence of rows ,where as IN check for actual actualvalues

  Exists typically offers better performance than IN with subqueries ,therefore you should use exists rather than IN wherever possible.

You should be careful when using Exists /Not Exists and IN  /Not IN . When a list of values contains a null value ,Not exists returns true ,but Not In returns false.

For example :-

Query to fetch the product types that don’t have any products of that type in the products table.

Select product_type_id,name

From product_type outer

Where Not Exists

( select 1 from products inner

    Where inner.product_type_id = outer.product_type_id);

Product_type_id       name

---------------------      -----------------------

5              Computers

Notice one row is returned one row

When you use Not in :

Select product_type_id,name

From product_type outer

Where Not IN

( select product_type_id  from products);

No rows returned

Because the subquery returns a list of product_id values,one of which is null.

When can overcome this problem using NVL

Select product_type_id,name

From product_type outer

Where Not IN

( select nvl(product_type_id ,0) from products);

Product_type_id       name

---------------------      -----------------------

5          Computers

Nested Subquereis

  You can nest sub queries inside the other sub queries to depth of 255.

Select product_type_id,avg(price)

From products

Group by product_type_id

Having avg(price) <

(select max(avg(price))

 From products

Where product_type_id in

   (select product_type_id

    From purchase

Where quantity >1

Group by product_typ_id)

Order by product_typ_id;

We can write upodate and delete statements containing subqueries

  Update emp

  Set salary =

       (select avg(high_salary)

          From salary_grades)

Where empno =4;

   Delete from emp

Where salary >

   Select avg(slary) from salary_grades);