DB Lock in the Oracle Objects

 SELECT object_name, machine, s.sid, s.serial#, s.*

FROM gv$locked_object l, dba_objects o, gv$session s  

WHERE l.object_id = o.object_id

AND l.session_id = s.sid;

Release SESSION SQL:  
--alter system kill session 'sid, serial#';  
ALTER system kill session '23, 1647';

Flexfields server-side validation package report error. virtual_value_set() exception : ORA-06502: PLSQL numeric or value error: number precision too large column#x

First of all we need to understand that this issue is coming from a seeded package. i.e. fnd_flex_server1

In this package you can search with the procedure name you are seeing in the error message to confirm where the error is coming from.

The issue might be due to either KFF or DFF as the same package is being used for both the flex fields.

In my case the below query was returning the record and it was failing while iterating the data from the cursor.

You need to pass table_id, application id, flex field name, context to get the records using the cursor.

Query:

SELECT g.end_user_column_name, g.application_column_name,

              c.column_type, c.width, g.required_flag,

              g.security_enabled_flag, g.concatenation_description_len,

              g.default_type, g.default_value, g.flex_value_set_id,

              g.runtime_property_function,

              NULL

       FROM fnd_descr_flex_column_usages g, fnd_columns c

      WHERE g.application_id = descstruct.application_id

        AND g.descriptive_flexfield_name = descstruct.desc_flex_name

        AND g.descriptive_flex_context_code = descstruct.desc_flex_context

        AND g.enabled_flag = 'Y'

        AND c.application_id = t_apid

        AND c.table_id = t_id

        AND c.column_name = g.application_column_name

      ORDER BY g.column_seq_num;

 

Check Rollback Blocks Post Concurrent Request is Cancelled

  SELECT inst_id,

         usn,

         state,

         undoblockstotal                     "Total",

         undoblocksdone                      "Done",

         undoblockstotal - undoblocksdone    "ToDo",

         inst_id,

         xid,

         rcvservers,

         DECODE (

             cputime,

             0, 'unknown',

               SYSDATE

             + (  (  (undoblockstotal - undoblocksdone)

                   / (undoblocksdone / DECODE (cputime, 0, 1)))

                / 86400))                    "Estimated time to complete"

    FROM gv$fast_start_transactions

   WHERE state != 'RECOVERED'

ORDER BY 1, 2;

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