Check Scheduled Concurrent Programs in Oracle Apps

SELECT fcr.request_id,
       DECODE (fcpt.user_concurrent_program_name,
               'Report Set', 'Report Set:' || fcr.description,
               fcpt.user_concurrent_program_name)
          CONC_PROG_NAME,
       argument_text PARAMETERS,
       NVL2 (fcr.resubmit_interval,
             'PERIODICALLY',
             NVL2 (fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE'))
          PROG_SCHEDULE_TYPE,
       DECODE (
          NVL2 (fcr.resubmit_interval,
                'PERIODICALLY',
                NVL2 (fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
          'PERIODICALLY',    'EVERY '
                          || fcr.resubmit_interval
                          || ' '
                          || fcr.resubmit_interval_unit_code
                          || ' FROM '
                          || fcr.resubmit_interval_type_code
                          || ' OF PREV RUN',
          'ONCE',    'AT :'
                  || TO_CHAR (fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
          'EVERY: ' || fcrc.class_info)
          PROG_SCHEDULE,
       fu.user_name USER_NAME,
       requested_start_date START_DATE
  FROM apps.fnd_concurrent_programs_tl fcpt,
       apps.fnd_concurrent_requests fcr,
       apps.fnd_user fu,
       apps.fnd_conc_release_classes fcrc
 WHERE     fcpt.application_id = fcr.program_application_id
       AND fcpt.concurrent_program_id = fcr.concurrent_program_id
       AND fcr.requested_by = fu.user_id
       AND fcr.phase_code = 'P'
       AND fcr.requested_start_date > SYSDATE
       AND fcpt.LANGUAGE = 'US'
       AND fcrc.release_class_id(+) = fcr.release_class_id
       AND fcrc.application_id(+) = fcr.release_class_app_id;

Retrieve DFF data in Oracle Apps

  SELECT ffv.descriptive_flexfield_name        "DFF Name",
         ffv.application_table_name            "Table Name",
         ffv.title                             "Title",
         ap.application_name                   "Application",
         ffc.descriptive_flex_context_code     "Context Code",
         ffc.descriptive_flex_context_name     "Context Name",
         ffc.description                       "Context Desc",
         ffc.enabled_flag                      "Context Enable Flag",
         att.column_seq_num                    "Segment Number",
         att.form_left_prompt                  "Segment Name",
         att.application_column_name           "Column",
         fvs.flex_value_set_name               "Value Set",
         att.display_flag                      "Displayed",
         att.enabled_flag                      "Enabled",
         att.required_flag                     "Required"
    FROM apps.fnd_descriptive_flexs_vl   ffv,
         apps.fnd_descr_flex_contexts_vl ffc,
         apps.fnd_descr_flex_col_usage_vl att,
         apps.fnd_flex_value_sets        fvs,
         apps.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 descriptive_flexfield_name like '%Site Uses%'
         AND fvs.flex_value_set_id(+) = att.flex_value_set_id
         --AND (ffv.title) like 'Site Use Information'
         --and ffv.application_table_name='RA_INTERFACE_LINES_ALL'
         AND ffv.descriptive_flexfield_name = 'RA_INTERFACE_LINES'
         AND ffc.descriptive_flex_context_code LIKE 'ORDER ENTRY'
ORDER BY att.column_seq_num

Change Oracle Apps password from Backend

SET SERVEROUTPUT ON;

DECLARE
      flag BOOLEAN;
BEGIN
      flag := fnd_user_pkg.changepassword(username=> 'MY_USER'
                                          ,newpassword => 'welcome1');
    IF flag
     THEN
           DBMS_OUTPUT.PUT_LINE('Your Password has been successfully reset');
     ELSE
           DBMS_OUTPUT.PUT_LINE('Password reset has failed');
     END IF;
END;
/
COMMIT;

Query to check Responsibilities Assigned to an Apps 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     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');

Execution History of an SQL ID

-- This script allows you to view the execution history of a SQL ID. Use it to investigate things like:

-- 1. Changing plan hash values.

-- 2. High elapsed time per execution.

-- 3. High % of cluster waits.

-- 4. Whether a SQL profile is being used.

SELECT   snap_id "Snap"
       , sql_id "SQL ID"
       , plan_hash_value "Plan"
       , end_interval_time "End Time"
       , executions "Execs"
       , ROUND(CASE WHEN executions > 0 THEN elapsed_time / executions ELSE elapsed_time END, 4) "ElapPexec"
       , ROUND(CASE WHEN executions > 0 THEN rows_processed / executions ELSE rows_processed END, 2) "RowPexec"
       , ROUND(CASE WHEN executions > 0 THEN buffer_gets / executions ELSE buffer_gets END, 0) "GetPexec"
       , ROUND(CASE WHEN executions > 0 THEN disk_reads / executions ELSE disk_reads END, 2) "ReadPexec"
       , ROUND(CASE WHEN executions > 0 THEN gc_time / executions ELSE gc_time END, 4)  "GCpExec"
       , ROUND(CASE WHEN elapsed_time > 0 THEN 100 * (gc_time / elapsed_time) ELSE 0 END, 2)  "GC %"
       , ROUND(CASE WHEN executions > 0 THEN cpu_time / executions ELSE cpu_time END, 4)  "CPUpExec"
       , ROUND(CASE WHEN executions > 0 THEN ccwait / executions ELSE ccwait END, 4)  "ConcPexec"
       , ROUND(CASE WHEN elapsed_time > 0 THEN 100 * (cpu_time / elapsed_time) ELSE 0 END, 2)  "CPU %"
       , ROUND(CASE WHEN elapsed_time > 0 THEN 100 * (ccwait / elapsed_time) ELSE 0 END, 2)  "Conc %"
       , ROUND(elapsed_time, 1) "Elapsed"
       , parsing_schema_name "Schema"
       , sql_profile "SQL profile"
       , min_inst_id "LowInst"
       , max_inst_id "HghInst"
       , ROUND(cpu_time, 1) "CPU"
       , buffer_gets "Gets"
       , disk_reads  "Reads"
       , ROUND(gc_time, 1) "Cluster"
       , rows_processed "Rows"
       , ROUND(ccwait, 1) "Concurrency"
       , ROUND(CASE WHEN executions > 0 THEN plsql_time / executions ELSE plsql_time END, 4) "PLSpExec"
       , ROUND(CASE
                  WHEN executions > 0
AND                    rows_processed > 0
                  THEN
                     elapsed_time / executions / rows_processed / 1000000
                  WHEN executions = 0
AND                    rows_processed > 0
                  THEN
                     elapsed_time / rows_processed / 1000000
                  ELSE
                     0
               END
             , 4)
            "ElapProw"
       , sql_text "SQL Text"
FROM     (SELECT   s.snap_id
                 , sq.sql_id
                 , sq.plan_hash_value
                 , sq.parsing_schema_name
                 , TRUNC(s.end_interval_time, 'MI') end_interval_time
                 , SUM(sq.executions_delta) executions
                 , SUM(sq.disk_reads_delta) disk_reads
                 , SUM(sq.buffer_gets_delta) buffer_gets
                 , SUM(sq.rows_processed_delta) rows_processed
                 , SUM(sq.cpu_time_delta) / 1000000 cpu_time
                 , SUM(sq.plsexec_time_delta) / 1000000 plsql_time
                 , SUM(sq.clwait_delta) / 1000000 gc_time
                 , SUM(sq.ccwait_delta) / 1000000 ccwait
                 , SUM(elapsed_time_delta) / 1000000 elapsed_time
                 , sq.force_matching_signature
                 , MIN(sq.instance_number) min_inst_id
                 , MAX(sq.instance_number) max_inst_id
                 , sq.sql_profile
                 , DBMS_LOB.SUBSTR(sl.sql_text, 200, 1) sql_text
          FROM     dba_hist_sqlstat sq, dba_hist_snapshot s, dba_hist_sqltext sl
          WHERE    1 = 1
          AND      sq.sql_id = :sqlid
          --and   sq.plan_hash_value=3983895616
          AND      s.snap_id = sq.snap_id
          --and   s.snap_id between 37279 and 37305
          AND      TRUNC(s.end_interval_time, 'MI') > SYSDATE - 90
          --and   sq.parsing_schema_name in ('APPS_FR')
          AND      s.dbid = sq.dbid
          AND      sq.dbid = sl.dbid
          AND      s.instance_number = sq.instance_number
          AND      sq.sql_id = sl.sql_id
          AND      sq.elapsed_time_delta > 0
          GROUP BY s.snap_id
                 , sq.sql_id
                 , sq.plan_hash_value
                 , sq.force_matching_signature
                 , sq.parsing_schema_name
                 , TRUNC(s.end_interval_time, 'MI')
                 , sq.sql_profile
                 , DBMS_LOB.SUBSTR(sl.sql_text, 200, 1))
ORDER BY 1 DESC;

-- Once you have the SQL, use the following SQL to extract all the execution plans:

SELECT * FROM TABLE(DBMS_XPLAN.display_awr(sql_id => :sqlid , format => 'ADVANCED -ALIAS -OUTLINE'))

Oracle Apps: Find Fields in DFFs

1. Query to Find All the fields in a particular DFF:-

SELECT ffv.descriptive_flexfield_name DFFName,
                 ffv.application_table_name TableName,
                 ffv.title Title,
                 ap.application_name Application,
                 att.column_seq_num SegmentNumber,
                 att.form_left_prompt SegmentName,
                 att.application_column_name,
                 fvs.flex_value_set_name ValueSet,
                 att.required_flag
            FROM apps.fnd_descriptive_flexs_vl ffv,
                 apps.fnd_descr_flex_contexts_vl ffc,
                 apps.fnd_descr_flex_col_usage_vl att,
                 apps.fnd_flex_value_sets fvs,
                 apps.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 ffv.title IN ('Invoice Line Information')
        ORDER BY ffv.title;


2. Query to Find Only Required fields in a particular DFF :-

SELECT *
  FROM (  SELECT ffv.descriptive_flexfield_name DFFName,
                 ffv.application_table_name TableName,
                 ffv.title Title,
                 ap.application_name Application,
                 att.column_seq_num SegmentNumber,
                 att.form_left_prompt SegmentName,
                 att.application_column_name,
                 fvs.flex_value_set_name ValueSet,
                 att.required_flag
            FROM apps.fnd_descriptive_flexs_vl ffv,
                 apps.fnd_descr_flex_contexts_vl ffc,
                 apps.fnd_descr_flex_col_usage_vl att,
                 apps.fnd_flex_value_sets fvs,
                 apps.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 ffv.title IN ('Invoice Line Information')
        ORDER BY ffv.title)
 WHERE required_flag = 'Y';

MVC Architecture

•  MODEL
•  VIEW
•  CONTROLLER