End Date a Responsibility For an FND User(Backend Script)

 DECLARE

    v_user_name             VARCHAR2 (100) := 'ROHAN_RAJPUT';

    v_responsibility_name   VARCHAR2 (100) := 'AR XX Superuser';  -- Resp name

    v_application_name      VARCHAR2 (100) := NULL;

    v_responsibility_key    VARCHAR2 (100) := NULL;

    v_security_group        VARCHAR2 (100) := NULL;

BEGIN

    SELECT fa.application_short_name,

           fr.responsibility_key,

           frg.security_group_key

      INTO v_application_name, v_responsibility_key, v_security_group

      FROM fnd_responsibility     fr,

           fnd_application        fa,

           fnd_security_groups    frg,

           fnd_responsibility_tl  frt

     WHERE     fr.application_id = fa.application_id

           AND fr.data_group_id = frg.security_group_id

           AND fr.responsibility_id = frt.responsibility_id

           AND frt.LANGUAGE = USERENV ('LANG')

           AND frt.responsibility_name = v_responsibility_name;

    fnd_user_pkg.delresp (username         => v_user_name,

                          resp_app         => v_application_name,

                          resp_key         => v_responsibility_key,

                          security_group   => v_security_group);

    COMMIT;

    DBMS_OUTPUT.put_line (

           'Responsiblity '

        || v_responsibility_name

        || ' is removed from the user '

        || v_user_name

        || ' Successfully');

EXCEPTION

    WHEN OTHERS

    THEN

        DBMS_OUTPUT.put_line (

               'Error encountered while deleting responsibilty from the user and the error is '

            || SQLERRM);

END;

/

ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body "package name" has been invalidated

Ideally this error comes when the package in question is Invalid but sometime we see this error even if our package is Valid.

Instead of getting into too much details and debugging just request your DBAs to recompile the package or do it yourself if you are a confident developer with apps access ;) 

To compile both of sepc and body :-

                                Alter package pkg_name compile;

To compile only Body :-

                                Alter package pkg_name compile body;

If its recompiled without any error then just retest.

That's it.

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'))