Frequently Used Oracle Apps Technical queries..

1) How to find Oracle Applications Login URL from database

   Execute either of the following query logging into Oracle Applications database from "APPS" user:-

a.SELECT home_url FROM icx_parameters;


b.SELECT profile_option_value
    FROM fnd_profile_option_values
   WHERE profile_option_id= (SELECT profile_option_id
    FROM fnd_profile_options WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')
   AND level_value = 0;

2) How to set APPS environment for newly created user in UNIX


a) When your UNIX box user is created for Oracle apps instance. And if you are unable to change standard tops using environemnt variables such as $AU_TOP, $FND_TOP. You just follow the below steps to set the environment variables.

b) 1. Login to UNIX box using your id

c) 2. cd

d) if you dont know the path, you can login to Oracle EBS application and get using these steps, open any form, Help->Diagnostics->Examine-> Choose Block "$ENVIRONMENT$", Field "FND_TOP" copy the value which is the path of FND_TOP

e) 3. once you get the path, cd

f) 4. change directory two time to reach $APPL_TOP path

g) 5. Here you find the environemt file as below format

h) APPS_.env

i) now, get the current path using pwd

j) copy the current path and environment file

k) ex: /opt/oracle/pub11demoappl/APPSPUBDEMO11i_mycomp.env

l) 6. cd $HOME , you reach your home directory

m) 7. vi .profile

n) 8.reach the end of file

o) 9. create a line as . environemt file with full path

p) ex:

q) . /opt/oracle/pub11demoappl/APPSPUBDEMO11i_mycomp.env

r) dot(.) must be prefixed with a space to execute the file.

s)now save your .profile file using

t) :wq!

u) once your file is saved, exit and login to UNIX box using your user credentials

v) Once you login, you should be able to change directories such as $AU_TOP

w) try cd $AU_TOP and do pwd to see whether your directory is changed...



3) How do I know which version of EBS I have.

    From the Application: Help > About Oracle Applications
     From the Database: Query FND_PRODUCT_GROUPS (RELEASE_NAME column).



4) Query to get the responsibility corresponding to a form

   SELECT DISTINCT u.user_name,


rtl.responsibility_name,

ff.function_name,

ffl.user_function_name

FROM fnd_compiled_menu_functions cmf,

fnd_form_functions ff,

fnd_form_functions_tl ffl,

fnd_responsibility r,

fnd_responsibility_tl rtl,

fnd_user_resp_groups urg,

fnd_user u

WHERE cmf.function_id = ff.function_id

AND r.menu_id = cmf.menu_id

AND urg.responsibility_id = r.responsibility_id

AND rtl.responsibility_id = r.responsibility_id

AND cmf.grant_flag = 'Y'

AND r.application_id = urg.responsibility_application_id

AND u.user_id = urg.user_id

AND UPPER (ffl.user_function_name) = UPPER ('transactions')

AND ff.function_id = ffl.function_id

ORDER BY u.user_name


5) Query to find Responsibility for the Concurrent Program


SELECT frt.responsibility_name,
frg.request_group_name,
frg.description
FROM fnd_request_groups frg ,
fnd_request_group_units frgu ,
fnd_concurrent_programs fcp ,
fnd_concurrent_programs_tl fcpt ,
fnd_responsibility_tl frt ,

fnd_responsibility frs

WHERE frgu.unit_application_id = fcp.application_id

AND frgu.request_unit_id = fcp.concurrent_program_id

AND frg.request_group_id = frgu.request_group_id

AND frg.application_id = frgu.application_id

AND fcpt.source_lang = USERENV('LANG')

AND fcp.application_id = fcpt.application_id

AND fcp.concurrent_program_id = fcpt.concurrent_program_id

AND frs.application_id = frt.application_id

AND frs.responsibility_id = frt.responsibility_id

AND frt.source_lang = USERENV('LANG')

AND frs.request_group_id = frg.request_group_id

AND frs.application_id = frg.application_id

AND fcp.concurrent_program_name =

AND fcpt.user_concurrent_program_name LIKE



b) 2nd query



SELECT prog.user_concurrent_program_name "program name",


prog.concurrent_program_name "program short name",

appl.application_name "program application name",

prog.description "program description",

exe.executable_name "executable name",

exe.execution_file_name "executable file name",

DECODE( exe.execution_method_code, 'I', 'PLSQL Stored Procedure', 'P', 'Report', 'L', 'SQL Loader','Q','SQL*Plus', exe.execution_method_code) "execution method"

FROM fnd_executables exe,

fnd_application_tl appl,

fnd_concurrent_programs_vl prog

WHERE exe.application_id = appl.application_id

AND exe.executable_id = prog.executable_id

AND appl.language ='US'

AND prog.user_concurrent_program_name =:p_conc_prog_name;





Query to Retrive concurrent program executed details along with logfile location ,timestamp etc..



SELECT DISTINCT fcr.request_id, fcr.actual_start_date,

fcr.actual_completion_date,

floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600) HOURS,

floor((((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60) -

floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600)/60) MINUTES,

round((((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60) -

floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600 -

(floor((((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60) -

floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) SECS,

DECODE (fcr.phase_code,

'C', 'Completed',

'I', 'Inactive',

'P', 'Pending',

'R', 'Running',

'N/A'

) phase_code,

DECODE (fcr.status_code,

'A', 'Waiting',

B', 'Resuming',

'C', 'Normal',

'D', 'Cancelled',

'E', 'Errored',

'F', 'Scheduled',

'G', 'Warning',

'H', 'On Hold',

'I', 'Normal',

'M', 'No Manager',

'Q', 'Standby',

'R', 'Normal',

'S', 'Suspended',

'T', 'Terminating',

'U', 'Disabled',

'W', 'Paused',

'X', 'Terminated',

'Z', 'Waiting',

'N/A'

) status_code,

fcr.outfile_name, fcr.number_of_arguments, fcr.argument_text,

frt.responsibility_name, fav.application_name, fav.application_short_name appl_short_name, fu.user_name,

fu.description user_description, fu.start_date user_start_date,

fcp.user_concurrent_program_name,

fcp.concurrent_program_name short_name, fe.executable_name,

DECODE (fe.execution_method_code,

'B', 'Request Set Stage Function',

'Q', 'SQL*Plus',

'H', 'Host',

'L', 'SQL*Loader',

'A', 'Spawned',

'I', 'PL/SQL Stored Procedure',

'P', 'Oracle Reports',

'S', 'Immediate',

'N/A'

) execution_method,

fe.execution_file_name

FROM apps.fnd_concurrent_requests fcr,

apps.fnd_user fu,

apps.fnd_application_vl fav,

apps.fnd_responsibility_tl frt,

apps.fnd_concurrent_programs_vl fcp,

apps.fnd_executables fe

WHERE fcr.requested_by = fu.user_id

AND fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcr.responsibility_id = frt.responsibility_id

AND fcr.responsibility_application_id = fav.application_id

AND FCP.EXECUTABLE_ID = FE.EXECUTABLE_ID

AND fcp.user_concurrent_program_name like ':program name'



6) Query to find out concurrent program details


select

prog.user_concurrent_program_name "program name",

prog.concurrent_program_name "program short name",

appl.application_name "program application name",

prog.description "program description",

exe.executable_name "executable name",

exe.execution_file_name "executable file name",

decode( exe.execution_method_code, 'I', 'PLSQL Stored Procedure', 'P', 'Report', 'L', 'SQL Loader','Q','SQL*Plus', exe.execution_method_code) "execution method"

from

fnd_executables exe,

fnd_application_tl appl,

fnd_concurrent_programs_vl prog

where exe.application_id = appl.application_id

AND exe.executable_id = prog.executable_id

AND appl.language='US'

AND prog.user_concurrent_program_name =:p_conc_prog_name


7) Query to get the personalization info from backend



SELECT ffcr.function_name, ffcr.form_name, ffcr.SEQUENCE "P Seq", ffcr.enabled "Rule Enabled?", ffcr.fire_in_enter_query, ffcr.rule_type,

ffcr.description "Rule Description", ffcr.trigger_event, ffcr.condition, ffca.SEQUENCE "Action sequence",

DECODE (ffca.action_type,

'P', 'Property',

'M', 'Message',

'S', 'Menu',

'B', 'Builtin'

) "Action Type", ffca.summary " Action Desc", ffca.enabled "Action Enabled?", ffca.object_type, ffca.target_object, ffca.property_value,

DECODE (ffca.MESSAGE_TYPE,

'W', 'Warn',

'H', 'Hint',

'E', 'Error',

'D', 'Debug',

'S', 'Show',

NULL

) "Message Type",

decode(ffca.builtin_type, 'G', 'Go Item', 'C', 'Launch SRS Form', 'E', 'Launch a Function', 'U', 'Launch a URL', 'D', 'Do Key',

'P', 'Execute a Procedure', 'B', 'GO_BLOCK', 'F', 'FORMS_DDL', 'R', 'RAISE_FORM_TRIGGER_FAILURE', 'T', 'EXECUTE_TRIGGER', 'S', 'SYNCHRONIZE',

'L', 'Call Custom Library', 'Q', 'Create Record Group from Query', 'X', 'Set Profile Value in Cache', NULL) "Builtin Type",

ffca.builtin_arguments, ffca.property_name, ffca.menu_entry, ffca.menu_label, ffca.menu_seperator,

ffca.menu_enabled_in, ffca.menu_argument_long, ffca.menu_argument_short

FROM applsys.fnd_form_custom_rules ffcr, applsys.fnd_form_custom_actions ffca

WHERE ffcr.ID = ffca.rule_id;



8) Data base Instance name
      select * from global_name

9)  Apps intilize  Query
   procedure APPS_INITIALIZE(user_id in number,

                                                resp_id in number,
                                            resp_appl_id in number);
10 ) MRC


  BEGIN

         select mrc_sob_type_code,currency_code

into g_mrcsobtype, g_currency

from gl_sets_of_books

where set_of_books_id = p_set_of_book_id;

EXCEPTION

WHEN OTHERS THEN

g_mrcsobtype := 'P';

END;

IF UPPER(g_mrcsobtype) ='R'

THEN

fnd_client_info.set_currency_context(p_set_of_book_id);

END IF;

10 ) Request Set Identifications

"select description from fnd_concurrent_requests

where request_id in (select priority_request_id
from fnd_concurrent_requests where request_id=3341987)


2)

select * from apps.FND_REQUEST_SETS_VL where REQUEST_SET_ID in (
select REQUEST_SET_ID from
apps.FND_REQUEST_SET_PROGRAMS where concurrent_program_id =45187)

3) SELECT t.responsibility_name, b.request_group_id, c.request_group_name,
d.request_unit_id, e.user_request_set_name
FROM apps.fnd_responsibility_tl t,
apps.fnd_responsibility b,
apps.fnd_request_groups c,
apps.fnd_request_group_units d,
apps.fnd_request_sets_tl e
WHERE b.responsibility_id = t.responsibility_id
AND b.application_id = t.application_id
AND b.request_group_id = c.request_group_id
AND c.request_group_id = d.request_group_id
AND d.request_unit_type = 'S'
AND d.request_unit_id = e.request_set_id
AND e.LANGUAGE = t.LANGUAGE
AND t.LANGUAGE = 'US'
AND e.USER_REQUEST_SET_NAME = :Request_name"

 11) Operating Units        select * from apps.hr_operating_units   12) Deleting conurent program   Begin

fnd_program.delete_program('program short name','schema');

fnd_program.delete_executable('program short name','schema');

commit;

End;


11) To find the apps password  in 11i
 Login into unix and navigate to the following path and open the file wdbsvr.app file.

In 11i we have $IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app file.

12 To know the Oracle Apps Release Name,Version number,Product Groups, Multi Language Support,Appl Short Name

select * from fnd_product_groups  

13 To know the Installed Application 
select * from fnd_product_installations   

 14 To Know the Oracle Database Version

     select * from v$version   

 15   To Know the uncommitted Transaction 

select * from v$transaction  

16 To Know the Instance_Name(SID), Host Name, Version, Instance Creation date, Active Status
select * from v$instance 


17 To Know the data base file creation Directory path
select * from v$parameter
examples
--where name like '%utl_file_dir%'  -- To Know the data base file creation Directory path using UTL_FILE Package 
--where name like '%result%'       -- To Know the Result cache mode and sizes
--where name like 'user_dump_dest' -- To know the Trace file stored Directory Address, this parameter is defined in the init.ora File

18 To know the Database Property details

select * from v$database  

19 To know the Oracle Apps Url name 
select * from icx_parameters   

20  Query to find all the TOP's in oracle application 
 select variable_name, value

from fnd_env_context
where variable_name like '%\_TOP' escape '\'
and concurrent_process_id = (select max(concurrent_process_id) from fnd_env_context)
order by 1;

21) How to get the oracle application  user password in R12

Create a package and execute the below query 
-----------------------------------------------------------------------------
create or replace
PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
-------------------------------------------------------------------------------------------------------------------
--> Package Body
CREATE OR REPLACE PACKAGE BODY GET_PWD AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2 AS
   LANGUAGE JAVA
   NAME'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
end GET_PWD;

---------------------------------------------------------------------------------------
Execute the below query 

SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt(fnd_web_sec.get_guest_username_pwd,usertable.encrypted_foundation_password) FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name=(SELECT SUBSTR(fnd_web_sec.get_guest_username_pwd,1,INSTR(fnd_web_sec.get_guest_username_pwd,'/')-1) FROM DUAL)
           )
           ,usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr

 WHERE usr.user_name like 'NTEST';

22)  How to find the patches applied in oracle applications
       select * from ad_applied_patches

23)  How to attach concurrent  program to a request group via pl/sql
Code # In this code a concurrent program is attached to receivables request group

DECLARE
BEGIN
FND_PROGRAM.add_to_group
(
PROGRAM_SHORT_NAME  =>'Test_XML_SAMPLE'
,PROGRAM_APPLICATION =>'AR'
,REQUEST_GROUP       => 'Receivables All'
,GROUP_APPLICATION   =>'AR'
) ;
commit;
exception
when others then
dbms_output.put_line('Object already exists');
END ;

24)   Fetch file versions from oracle database
     
   The seeded table will only contain versions for seeded Oracle files, custom components data will not be there.
     The following are the file types for which data can be retrived (.fmb, *.xml,*.rdf,*.pls,*.class etc)

SELECT   adf.app_short_name, fap.basepath, adf.subdir, adf.filename, adv.file_version_id,
         adv.VERSION, adv.creation_date
    FROM ad_files adf, ad_file_versions adv, fnd_application fap
   WHERE adf.file_id = adv.file_id
   and fap.application_short_name = adf.app_short_name
     AND adf.filename IN ('APXINWKB.fmb')
ORDER BY adf.filename, adv.creation_date DESC