NaturalOracle DataGaurd tutorial Series Coming Soon!! Natural
For Any queries, please mail us at support@funoracleapps.com

End Date a Responsibility for Users

1 comment
If we need to end date a responsibility for a user then we can use the below syntax.

Syntax:

DECLARE
   p_user_name           VARCHAR2 (50);
   p_resp_name           VARCHAR2 (50) := 'Application Developer'; /*Responsibility Name*/
   v_user_id             NUMBER (10) := 0;
   v_responsibility_id   NUMBER (10) := 0;
   v_application_id      NUMBER (10) := 0;
BEGIN
   BEGIN
      SELECT application_id, responsibility_id
        INTO v_application_id, v_responsibility_id
        FROM fnd_responsibility_vl
       WHERE UPPER (responsibility_name) = UPPER (p_resp_name);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.put_line ('Responsibility not found.');
         RAISE;
      WHEN TOO_MANY_ROWS
      THEN
         DBMS_OUTPUT.
         put_line ('More than one responsibility found with this name.');
         RAISE;
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Error finding responsibility.');
         RAISE;
   END;


   /*DBMS_OUTPUT.
   put_line (
         'Responsibility ID'
      || v_responsibility_id
      || 'and'
      || 'Application ID'
      || v_application_id);*/
  

   FOR u
      IN (SELECT fu.user_id, furg.start_date,furg.description
            FROM fnd_user_resp_groups_direct furg,
                 fnd_user fu,
                 fnd_responsibility_tl fr
           WHERE     fr.responsibility_name = p_resp_name
                 AND furg.user_id = fu.user_id
                 AND fu.user_name <> 'SYSADMIN'
                 AND furg.responsibility_id = fr.responsibility_id
                 AND fr.language = USERENV ('LANG'))
   LOOP
      v_user_id := u.user_id;

      /*DBMS_OUTPUT.put_line ('User_name' || v_user_id);*/

      BEGIN
         DBMS_OUTPUT.put_line ('Initializing The Application');

         fnd_global.
         apps_initialize (user_id        => v_user_id,
                          resp_id        => v_responsibility_id,
                          resp_appl_id   => v_application_id);

         DBMS_OUTPUT.
         put_line (
            'Calling FND_USER_RESP_GROUPS_API API To Insert/Update Resp');

         fnd_user_resp_groups_api.
         update_assignment (
            user_id                         => v_user_id,
            responsibility_id               => v_responsibility_id,
            responsibility_application_id   => v_application_id,
            security_group_id               => 0,
            start_date                      => u.start_date,
            end_date                        => TRUNC (SYSDATE) - 1,
            description                     => u.description);

         DBMS_OUTPUT.
         put_line ('The End Date has been set for responsibility');
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Error calling the API');
            RAISE;
      END;
   END LOOP;
END;

1 comment :

Post a Comment

Finding Patch Level of a Product in Oracle Application

1 comment
If we need to find the latest patch level for a particular product then we can use the below query.

Syntax:

select patch_level
from fnd_product_installations where patch_level like upper('%&product_name%')


Output:

PATCH_LEVEL                  
------------------------------
11i.AD.I.7
                   

1 comment :

Post a Comment

APP-FND-01926 - The custom event WHEN-LOGON-CHANGED raised unhandled exception. ORA-06508 PL/SQL could not find program unit being called

5 comments
This error occurs while opening Forms in Oracle Applications.

Reason:This issue accurs mostly after Upgrade/Clone of applications.The invalid objects in Custom.pll, form throws an error when opened.

 
Solution:Compile Custom.pll using the below command to generate Custom.plx. Always make sure to backup the previous CUSTOM.plx

 

Syntax:
f60gen module=$AU_TOP/resource/CUSTOM.pll userid=APPS/APPS output_file=$AU_TOP/resource/CUSTOM.plx module_type=library batch=no compile_all=special

5 comments :

Post a Comment

Finding Concurrent Program Past Execution Details

1 comment
If we need to find the Concurrent Program previous\past execution details then the below query will be useful.

Script:

select request_id, phase_code, status_code ,
to_char(requested_start_date, 'DD-MM-YY hh24:mi:ss') "Requested start date",
to_char(actual_start_date, 'DD-MM-YY hh24:mi:ss') "Actually started date",
to_char(actual_completion_date,'DD-MM-YY hh24:mi:ss') "Actually completed date",
substr(ARGUMENT_TEXT,1,100) Parameter_Passed ,
TRUNC (MOD ((nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date) * 24,
                     24
                    )
               ) "Hr",
         TRUNC (MOD (  (nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date)
                     * 24
                     * 60,
                     60
                    )
               ) "Mins",
         TRUNC (MOD (  (nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date)
                     * 24
                     * 60
                     * 60,
                     60
                    )
               ) "Sec"
from fnd_concurrent_requests  cr where concurrent_program_id in
( select concurrent_program_id
  from fnd_concurrent_programs
  where concurrent_program_name = '&prog_short_name' )
and actual_start_date>sysdate - &noofdaysago
order by phase_code,actual_start_date asc

1 comment :

Post a Comment

Finding the Blocking Session at Database

1 comment
The below query can be used to find the blocking sessions at the database level.

Query

SELECT (SELECT username
          FROM v$session
         WHERE sid = a.sid)
          blocker,
       a.sid,
       ' is blocking ' "IS BLOCKING",
       (SELECT username
          FROM v$session
         WHERE sid = b.sid)
          blockee,
       b.sid,
       a.TYPE "WITH LOCK TYPE"
  FROM v$lock a, v$lock b
 WHERE a.block = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2

1 comment :

Post a Comment

Viewing Request Output and Log Files in HTML/Browser

No comments
If the ouput and log files of the concurrent needs to be viewed in Browser, then we can perform the below action item.

1. Goto System Administrator responsibility and select Profile>System
2. Search for the 'Viewer:%' profile.


3. Set the profile Viewer: Application for Text to Browser. If this has to be set for all users then it has to be set at Site Level or for a particular user we can set it at user level.

No comments :

Post a Comment

ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old

No comments
If we are facing the issue ORA-20002, then we can perform the below work around.This basically occurs if any Database is upgraded.

Issue:
ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old

Solution:
1. Connect to sqlplus / as sysdba
2. Take a Backup of FND_STATTAB table
3. Run EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(‘APPLSYS’,'FND_STATTAB’);

No comments :

Post a Comment

Oracle HTTP Server MLR Patchset

No comments
There might be requirement from Oracle to check the HTTP server level MLR Patch Set, we can use the below information to get the deatils.

RUN command  "$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v"


httpd -v
Output:Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built:   Dec  6 2006 17:56:59 (iAS 1.0.2.2.2 rollup 5)


The meaning of the various rollup are as below

Oracle HTTP Server Patch List

Release
Rollup Version
iAS Patch Number
Applications Interop patch
Patch Included in Rapid Install
1.0.2.2.2
Rollup 5
None
1.0.2.2.2
Rollup 4
11.5.10
1.0.2.2.2
Rollup 3
11.5.9
1.0.2.2.2
Rollup 2
11.5.8
1.0.2.2.2
Rollup 1
11.5.7
1.0.2.1S
Rollup 1
None



No comments :

Post a Comment

Tables which are purged with FND_CONC_CLONE.SETUP_CLEAN

1 comment
When EXEC FND_CONC_CLONE.SETUP_CLEAN is executed various tables are purged.

To know which tables are purged/cleaned we can view $FND_TOP/patch/115/sql/AFCPCLNB.pls.

1 comment :

Post a Comment

Finding the Languages Installed in Oracle Applications

1 comment
The below query can be used for the finding the languages installed in Oracle Application.

SELECT Language_code, NLS_language, Installed_flag
  FROM fnd_languages
 WHERE installed_flag IN ('I', 'B')

The Installed_Flag has 3 values

I- Installed
B- Base
D- Disabled

1 comment :

Post a Comment