Use SELECT Query In Fast Formula

SQL can be used in fast formula with help of TABLE type value set

Steps:

1) Go to Setup and Maintenance >> Search Manage Value Sets for Global Human Resource task >> Create a new value Set
>> Validation Type = TABLE

Module                       -  Module Name  ( Extract etc)
From Clause               -  TABLE Name ( cmp_salary)
Value Column Name  -  ColumnName ( Salary_amount )
WHERE Clause          - Conditions

2) Use Parameter in WHERE Clause if required. ( Parameter will be passed from fast Formula only).
Example
Assignment_id = :{PARAMETER.ASSIGNMENT_ID}

ASSIGNMENT_ID is parameter Name, which need to be passed from Fast Formula.

3) Create Fast Formula and fetch value set value using following method  GET_VALUE_SET('ValueSetName','BindVariable')

ValueSetName - Mandatory - Pass ValueSet Name
BindVariable   - Optional  -  Value for Parameters

Syntex for Bind variable -
'|=PARAMNAME1='''''||Value1||'|PARAMNAME2=''''||Value2||''''


** This method might work only for payroll flow. I could not get return while using from Extract

List of Programs in Request Set

--SQL – List of Request Set Programs
SELECT USER_REQUEST_SET_NAME,
  a.REQUEST_SET_ID,
  Sequence,
  b.CONCURRENT_PROGRAM_ID,
  c.USER_CONCURRENT_PROGRAM_NAME
FROM FND_REQUEST_SETS_VL a,
  FND_REQUEST_SET_PROGRAMS b,
  FND_CONCURRENT_PROGRAMS_VL c
WHERE 1=1
--AND USER_REQUEST_SET_NAME LIKE '%RequestSetname%'
AND a.REQUEST_SET_ID        = B.REQUEST_SET_ID
AND b.concurrent_program_id = c.concurrent_program_id
ORDER BY USER_REQUEST_SET_NAME,sequence ;

Logical and Physical Query in OTBI

OTBI reporting is based on pre-configured subject areas.  Oracle provide multiple SAs for respective area which can be used to create an OTBI analysis by drag drop and calculations.

Based on selected SA and columns system generates a logical query, which can be viewed from Advanced  => SQL.


Physical query is SQL used to fetch logical query from Oracle DB. Physical query can be view in nqquery.log [Doc ID 1469168.1 - How to generate nqquery.log]

Steps to view physical query 
1) Get the logical query
2) Go to Administraton >> SQL Issues >> Paste following line followed by logical query
SET VARIABLE DISABLE_CACHE_HIT=1,DISABLE_PLAN_CACHE_HIT=1;
<<logicalQuery>>
3) Click on view log