Find Table and Column Name with table and column description

SELECT fa.application_id,
  fa.application_short_name ,
  fat.application_name ,
  table_name ,
  column_name ,
  ft.description table_description ,
  fc.description column_description
FROM fnd_tables ft ,
  fnd_columns fc ,
  fnd_application_tl fat ,
  fnd_application fa
WHERE ft.table_id      = fc.table_id
AND fat.application_id = ft.application_id
AND fa.application_id  = fat.application_id
AND fc.column_name =:column_name;

Complex Sql Queries - Part 2

1. Display the names of the employees who are working in the company for The past 5 years.
      SELECT ename
      FROM emp
      WHERE TO_CHAR (sysdate,'YYYY')-TO_CHAR(hiredate,'YYYY')>=5;

2. Display your age in days.
      SELECT to_date (sysdate)-to_date ('01-apr-96')
      FROM dual;

3. Display your age in months.
      SELECT months_between (sysdate,'01-apr-96')
      FROM dual;

4. Display the 10th record of emp table without using group by and rowid?
      SELECT *
      FROM EMP
      WHERE ROWNUM<11
      MINUS
      SELECT * FROM EMP WHERE ROWNUM <10;

5. Display the various jobs and total number of employees within each job Group.
      SELECT job,
      COUNT (job)
     FROM EMP
     GROUP BY job;

6. Display the depart numbers and max salary for each department.
      SELECT deptno,
      MAX (sal)
     FROM EMP
     GROUP BY deptno;

7. Display the depart numbers with more than three employees in each dept.
      SELECT deptno,
     COUNT (deptno)
     FROM EMP
     GROUP BY deptno
     HAVING COUNT (*)>3;

8. Display the name of the employee who earns the highest salary.
      SELECT ename
     FROM emp
     WHERE sal=
     (SELECT MAX (sal) FROM emp);

9. Display the names of the employees who earn the highest salary in their respective departments.
     SELECT ename,
                    sal,
                    deptno
     FROM EMP
     WHERE sal IN
     (SELECT MAX (sal) FROM EMP GROUP BY deptno);

10. Find out the top 5 earners of the company?
     SELECT DISTINCT SAL
     FROM EMP E
    WHERE 5>=
    (SELECT COUNT (DISTINCT SAL) FROM EMP A WHERE A.SAL>=E.SAL)
    ORDER BY SAL DESC;

     

Complex SQL Queries

1. Display Salary in Words.
    SELECT ename,
                   sal,
               TO_CHAR(to_date(sal,'jsp'),'jsp')"in words"
    FROM emp;

2. Find the 3rd MAX salary in the emp table.
    SELECT DISTINCT sal
    FROM emp e1
    WHERE 3 =
   (SELECT COUNT(DISTINCT sal) FROM emp e2 WHERE e1.sal <= e2.sal);

3. Find the 3rd MIN salary in the emp table.
     SELECT DISTINCT sal
     FROM emp e1
    WHERE 3 =
   (SELECT COUNT(DISTINCT sal) FROM emp e2 where e1.sal >= e2.sal);

4. Select FIRST n records from a table.
     SELECT * FROM emp WHERE rownum <= &n;

5. Select LAST n records from a table.
     SELECT * FROM emp
     MINUS
    SELECT * FROM emp WHERE rownum <=
   (SELECT COUNT(*) - &n FROM emp);

6. How to get 3 Max salaries?.
    SELECT DISTINCT sal
    FROM emp a
   WHERE 3 >=
   (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal <= b.sal  )
   ORDER BY a.sal DESC;

7. How to get 3 Min salaries ?.
    SELECT DISTINCT sal
    FROM emp a
   WHERE 3 >=
  (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal >= b.sal);

8. How to get nth max salaries?
   SELECT DISTINCT hire_date
   FROM emp a
   WHERE
   &n =
  (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal >= b.sal );

Query to Get the Account Flex field Structure

SELECT DISTINCT sob.name Ledger_Name ,
  sob.set_of_books_id,
  sob.chart_of_accounts_id coa_id ,
  fifst.id_flex_structure_name struct_name ,
  ifs.segment_name ,
  ifs.application_column_name column_name ,
  sav1.attribute_value BALANCING ,
  sav2.attribute_value COST_CENTER ,
  sav3.attribute_value NATURAL_ACCOUNT ,
  sav4.attribute_value INTERCOMPANY ,
  sav5.attribute_value SECONDARY_TRACKING ,
  sav6.attribute_value GLOBAL ,
  ffvs.flex_value_set_name ,
  ffvs.flex_value_set_id
FROM fnd_id_flex_structures fifs ,
  fnd_id_flex_structures_tl fifst ,
  fnd_segment_attribute_values sav1 ,
  fnd_segment_attribute_values sav2 ,
  fnd_segment_attribute_values sav3 ,
  fnd_segment_attribute_values sav4 ,
  fnd_segment_attribute_values sav5 ,
  fnd_segment_attribute_values sav6 ,
  fnd_id_flex_segments ifs ,
  fnd_flex_value_sets ffvs ,
  gl_sets_of_books sob
WHERE 1                          =1
AND fifs.id_flex_code            = 'GL#'
AND fifs.application_id          = fifst.application_id
AND fifs.id_flex_code            = fifst.id_flex_code
AND fifs.id_flex_num             = fifst.id_flex_num
AND fifs.application_id          = ifs.application_id
AND fifs.id_flex_code            = ifs.id_flex_code
AND fifs.id_flex_num             = ifs.id_flex_num
AND sav1.application_id          = ifs.application_id
AND sav1.id_flex_code            = ifs.id_flex_code
AND sav1.id_flex_num             = ifs.id_flex_num
AND sav1.application_column_name = ifs.application_column_name
AND sav2.application_id          = ifs.application_id
AND sav2.id_flex_code            = ifs.id_flex_code
AND sav2.id_flex_num             = ifs.id_flex_num
AND sav2.application_column_name = ifs.application_column_name
AND sav3.application_id          = ifs.application_id
AND sav3.id_flex_code            = ifs.id_flex_code
AND sav3.id_flex_num             = ifs.id_flex_num
AND sav3.application_column_name = ifs.application_column_name
AND sav4.application_id          = ifs.application_id
AND sav4.id_flex_code            = ifs.id_flex_code
AND sav4.id_flex_num             = ifs.id_flex_num
AND sav4.application_column_name = ifs.application_column_name
AND sav5.application_id          = ifs.application_id
AND sav5.id_flex_code            = ifs.id_flex_code
AND sav5.id_flex_num             = ifs.id_flex_num
AND sav5.application_column_name = ifs.application_column_name
AND sav6.application_id          = ifs.application_id
AND sav6.id_flex_code            = ifs.id_flex_code
AND sav6.id_flex_num             = ifs.id_flex_num
AND sav6.application_column_name = ifs.application_column_name
AND sav1.segment_attribute_type  = 'GL_BALANCING'
AND sav2.segment_attribute_type  = 'FA_COST_CTR'
AND sav3.segment_attribute_type  = 'GL_ACCOUNT'
AND sav4.segment_attribute_type  = 'GL_INTERCOMPANY'
AND sav5.segment_attribute_type  = 'GL_SECONDARY_TRACKING'
AND sav6.segment_attribute_type  = 'GL_GLOBAL'
AND ifs.id_flex_num              = sob.chart_of_accounts_id
AND ifs.flex_value_set_id        = ffvs.flex_value_set_id;

Query to get details of all Concurrent programs used by a Request Set

SELECT rs.user_request_set_name ,
  rs.request_set_name,
  rs.description ,
  rss.display_sequence Seq,
  cp.user_concurrent_program_name ,
  ap.application_name ,
  e.executable_name ,
  e.execution_file_name ,
  lv. meaning
FROM apps.fnd_request_sets_vl rs,
  apps.fnd_req_set_stages_form_v rss,
  applsys.fnd_request_set_programs rsp,
  apps.fnd_concurrent_programs_vl cp,
  apps.fnd_executables e,
  apps.fnd_lookup_values lv,
  apps.fnd_application_vl ap
WHERE rs.application_id          = rss.set_application_id
AND rs.request_set_id            = rss.request_set_id
AND rss.set_application_id       = rsp.set_application_id
AND rss.request_set_id           = rsp.request_set_id
AND rss.request_set_stage_id     = rsp.request_set_stage_id
AND rsp.program_application_id   = cp.application_id
AND rsp.concurrent_program_id    = cp.concurrent_program_id
AND cp.executable_id             = e.executable_id
AND cp.executable_application_id = e.application_id
AND e.application_id             =ap.application_id
AND lv.lookup_code = e.execution_method_code
AND lv.language         ='US'
AND ap.application_name = 'Order Management';

Query to get DFF and Segment Values

SELECT ffv.descriptive_flexfield_name,
  ffv.application_table_name,
  ffv.title,
  ap.application_name ,
  ffc.descriptive_flex_context_code ,
  ffc.descriptive_flex_context_name,
  ffc.description ,
  ffc.enabled_flag ,
  att.column_seq_num ,
  att.form_left_prompt ,
  att.application_column_name ,
  fvs.flex_value_set_name ,
  att.display_flag ,
  att.enabled_flag ,
  att.required_flag
FROM fnd_descriptive_flexs_vl ffv,
  fnd_descr_flex_contexts_vl ffc,
  fnd_descr_flex_col_usage_vl att,
  fnd_flex_value_sets fvs,
  fnd_application_vl ap
WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id                =ffv.application_id
AND ffv.descriptive_flexfield_name   = ffc.descriptive_flexfield_name
AND ffv.application_id               = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id            =att.flex_value_set_id
AND ap.application_name              = 'Order Management'
AND ffv.application_table_name       ='OE_ORDER_HEADERS_ALL';

Query to find out concurrent program details and its parameters

SELECT fcpl.user_concurrent_program_name,
  fcp.concurrent_program_name,
  fcp.concurrent_program_id,
  fav.application_short_name,
  fav.application_name,
  fav.application_id,
  fdfcuv.end_user_column_name,
  fdfcuv.form_left_prompt prompt ,
  fdfcuv.enabled_flag,
  fdfcuv.required_flag,
  fdfcuv.display_flag
FROM fnd_concurrent_programs fcp ,
  fnd_concurrent_programs_tl fcpl ,
  fnd_descr_flex_col_usage_vl fdfcuv ,
  fnd_application_vl fav
WHERE fcp.concurrent_program_id       = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fav.application_id                =fcp.application_id
AND fcpl.language                     = 'US'
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'|| fcp.concurrent_program_name;

Query To Find Concurrent Program Attached To a Responsibility

SELECT fcpt.user_concurrent_program_name,
  frt.responsibility_name,
  frg.request_group_name
FROM fnd_Responsibility fr,
  fnd_responsibility_tl frt,
  fnd_request_groups frg,
  fnd_request_group_units frgu,
  fnd_concurrent_programs_tl fcpt
WHERE frt.responsibility_id           = fr.responsibility_id
AND frg.request_group_id              = fr.request_group_id
AND frgu.request_group_id             = frg.request_group_id
AND fcpt.concurrent_program_id        = frgu.request_unit_id
AND fcpt.user_concurrent_program_name = '&conc_program_name';

Query to list all the responsibilities attached to a user

SELECT fu.user_name,
  fr.responsibility_name,
  furg.start_date,
  furg.end_date
FROM fnd_user_resp_groups_direct furg,
  fnd_user fu,
  fnd_responsibility_tl fr
WHERE upper(fu.user_name)  =upper(:user_name)
AND furg.user_id           = fu.user_id
AND furg.responsibility_id = fr.responsibility_id
AND fr.language            = userenv('lang');

Query to find request group and application name for a concurrent program

SELECT cpt.user_concurrent_program_name "Concurrent Program Name",
  DECODE(rgu.request_unit_type, 'P', 'Program', 'S', 'Set', rgu.request_unit_type) "Unit Type",
  cp.concurrent_program_name "Concurrent Program Short Name",
  rg.application_id "Application ID",
  rg.request_group_name "Request Group Name",
  fat.application_name "Application Name",
  fa.application_short_name "Application Short Name",
  fa. basepath "Basepath"
FROM fnd_request_groups rg,
  fnd_request_group_units rgu,
  fnd_concurrent_programs cp,
  fnd_concurrent_programs_tl cpt,
  fnd_application fa,
  fnd_application_tl fat
WHERE rg.request_group_id    = rgu.request_group_id
AND rgu.request_unit_id      = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND rg.application_id        = fat.application_id
AND fa.application_id        = fat.application_id
AND cpt.language             = USERENV('LANG')
AND fat.language             = USERENV('LANG')
AND cpt.user_concurrent_program_name LIKE 'Reservations Interface Manager';

Oracle Concurrent Request Error Script

SELECT a.request_id "Req Id" ,
  a.phase_code,
  a.status_code ,
  actual_start_date ,
  actual_completion_date ,
  c.concurrent_program_name
  || ': '
  || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,
  APPLSYS.fnd_concurrent_processes b,
  applsys.fnd_concurrent_queues q ,
  APPLSYS.fnd_concurrent_programs c ,
  APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager   = b.concurrent_process_id
AND a.concurrent_program_id   = c.concurrent_program_id
AND a.program_application_id  = c.application_id
AND a.status_code             = 'E'
AND a.phase_code              = 'C'
AND b.queue_application_id    = q.application_id
AND b.concurrent_queue_id     = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE              = 'US'
ORDER BY 5 DESC;

Concurrent Program Queries

SELECT request_id,
  DECODE(phase_code,'C','Completed',phase_code)phase_code,
  DECODE(status_code, 'D', 'Cancelled' , 'E', 'Error', 'G', 'Warning', 'H', 'On Hold', 'T', 'Terminating', 'M', 'No Manager', 'X', 'Terminated', 'C', 'Normal', status_code)status_code,
  TO_CHAR(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date,
  TO_CHAR(actual_completion_date,'dd-mon-yy:hh24:mi:ss'),
  program,
  user_concurrent_program_name,
  completion_text,
  requestor,
  request_date
FROM fnd_conc_req_summary_v
ORDER BY request_date DESC;