how to get user_id for a APPS user ?

From: Prem Khanna J <premj_at_rediffmail.com>
Date: 20 Aug 2004 10:26:51 -0000
Message-ID: <20040820102651.31462.qmail@webmail7.rediffmail.com>

Hi All,
The environment is Oracle EBS 11.5.9/ Oracle 9.2.0.4/ AIX5.2L.

Below is the procedure is use to get the USER_ID of oracle applications user.This procedure in turn is being used by DBMS_FGA as shown below .

CREATE OR REPLACE PROCEDURE fga.aud_pro (   schema_name VARCHAR2,
  table_name VARCHAR2,
  policy_name VARCHAR2 )
IS

   appuserid number;
   appusername varchar2(30);
BEGIN
  SELECT user_id , user_name into appuserid , appusername   from apps.fnd_user where
  user_id = apps.fnd_profile.value('user_id');

INSERT INTO AUD_LOG VALUES (
appuserid,appusername,table_name,policy_name,sysdate ) ; END; EXECUTE DBMS_FGA.ADD_POLICY (-

OBJECT_SCHEMA => 'APPLSYS',- 
OBJECT_NAME => 'FND_CONCURRENT_PROGRAMS',- 
POLICY_NAME => 'TEST_POLICY',-

AUDIT_CONDITION => 'CONCURRENT_PROGRAM_NAME = ''TEST_CONC''',- AUDIT_COLUMN => 'CONCURRENT_PROGRAM_NAME',- HANDLER_SCHEMA => 'FGA',-
HANDLER_MODULE => 'AUD_PRO',-
ENABLE => TRUE );

But the user_id and user_name returned is -1 and ANONYMOUS and not the correct one.Whereas i have logged on as user "SCOTT". How do i get the proper values for userid and user_name ?

Can someone let me know !

Regards,
Prem.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Fri Aug 20 2004 - 05:22:34 CDT