Initializing Oracle Applications environment in Oracle Release 12i

Initializing Oracle Applications environment in Oracle Release 12i
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Problem Description : Not able to see data from views synonyms even after setting the apps envirenment in Oracle applications release 12i
----------------------------------------------
We need to set the mo global parameter to see the data in 12i , If
R12 Multi-Org Access Control (MOAC) exists in setup.

begin
fnd_global.apps_initialize(1210, 20678, 222);
mo_global.init('AR');
end;

The parameters used here are:
1. User_ID
2. Responsibility_ID
3. Responsibility_Application_ID


Link for more details
http://www.ltsolutions.eu/r12_pdf/R12_Multi-Org_Access_Control.pdf
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Method to call a report (Concurrent Program) from a form or from plsql block in oracle applications

Method to call a report (Concurrent Program) from a form or from plsql block in oracle applications
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
v_req:=FND_REQUEST.SUBMIT_REQUEST
(
'ONT',
'TWOEXP01',
'',
to_char(SYSDATE,'DD-MON-YYYY HH24:MI'),
FALSE,
:g_invoice_no,
:g_mail_code,
:g_org_id,
:g_ou_id,
chr(0),
'','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','');-- 100 ARGUMENTS FROM THE BEGINNING if v_req<=0 then
dbms_output.put_line('!!!ERROR!!! SUBMIT REQUEST ERROR.');
:g_debug:='error';
end if;

COMMIT;
dbms_output.put_line(' the req id is 'to_char(v_req));

1. where 'ONT' is the application short name
2. where 'TWOEXP01' is the concurrent short name
3. :g_invoice_no,:g_mail_code,:g_org_id,:g_ou_id are the arguments, and chr(0) must be the last argument.
4. 'Commit;' is required!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications SQL Query to know all the menus Responsibilities name where one given form is refered

Oracle Applications SQL Query to know all the menus Responsibilities name where one given form is refered
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
RESPONSIBILITY_NAME FROM fnd_responsibility_vl
WHERE
MENU_ID IN
(SELECT menu_id
FROM fnd_menu_entries_vl
WHERE
FUNCTION_ID IN
( SELECT
FUNCTION_ID
FROM
fnd_menu_entries_vl
WHERE
PROMPT = 'Define'))
UNION
SELECT
RESPONSIBILITY_NAME
FROM fnd_responsibility_vl
WHERE
MENU_ID IN
(SELECT
MENU_ID
FROM
fnd_menu_entries_vl
WHERE
SUB_MENU_ID IN
(SELECT
menu_id
FROM
fnd_menu_entries_vl
WHERE
FUNCTION_ID IN
( SELECT
FUNCTION_ID
FROM
fnd_menu_entries_vl
WHERE
PROMPT = 'Define')))
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications Query to get the current users of oracle apps and forms or concurrent programms opened by them (Through front end and back end

Oracle Applications Query to get the current users of oracle apps and forms or concurrent programms opened by them (Through front end and back end
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Front End ---> System Administration - Security - User - Monitor

Back End Query --- SELECT
USER_NAME, RESPONSIBILITY_NAME, USER_FORM_NAME, TIME, PID, USER_ID, RESP_APPL_ID, RESPONSIBILITY_ID, FORM_ID, FORM_APPL_ID
FROM
FND_SIGNON_AUDIT_VIEW
where
RESPONSIBILITY_NAME IS NOT NULL
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications SQL Query to get apps user data in just plain sql using something like userenv or srw to extract say their org_id, etc.?

Oracle Applications SQL Query to get apps user data in just plain sql using something like userenv or srw to extract say their org_id, etc.?
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
select
fnd_global.user_id,
fnd_global.resp_id,
fnd_global.resp_appl_id,
fnd_global.security_group_id,
fnd_global.user_name,
fnd_global.resp_name,
fnd_global.application_name,
fnd_global.application_short_name,
fnd_global.login_id,
fnd_global.conc_login_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
fnd_global.conc_request_id,
fnd_global.current_language,
fnd_global.base_language,
fnd_global.org_id,
fnd_global.org_name
from dual;
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications Query to know the fmb name if you know the form name and the path for its fmx and fmb as well

Oracle Applications Query to know the fmb name if you know the form name and the path for its fmx and fmb as well
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
ffv.form_name, ffv.user_form_name, ffv.description,
(SELECT application_name FROM fnd_application_tl WHERE application_id = ffv.application_id) application_name,
(SELECT basepath FROM fnd_application WHERE application_id = ffv.application_id) basepath,
form_id,
audit_enabled_flag
FROM
fnd_form_vl ffv
WHERE
(form_id >= 0) AND UPPER (user_form_name) LIKE '%MASS CHANGE%'

-----Path we have to derive from BASEPATH variable displayed in query



!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications Query to get the actual concurrent program file executable if we know the concurrent program name

Oracle Applications Query to get the actual concurrent program file executable if we know the concurrent program name
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
B.CONCURRENT_PROGRAM_NAME , A.USER_EXECUTABLE_NAME ,
DECODE( A.EXECUTION_METHOD_CODE , 'I' , 'PL/SQL Stored Procedure' , 'H' ,'Host' , 'S' ,'Immediate' , 'J' ,'Java Stored Procedure' , 'K', 'Java concurrent program' , 'M', 'Multi Language Function' , 'P', 'Oracle reports' , 'B', 'Request Set Stage Function' , 'A', 'Spawned' , 'L', 'SQL*Loader' , 'Q', 'SQL*Plus' , 'E', 'Pearl concurrent Programm' , 'Unkown Type') ,
A.EXECUTION_FILE_NAME , A.EXECUTION_FILE_PATH
FROM
FND_EXECUTABLES_FORM_V A , FND_CONCURRENT_PROGRAMS_VL B
WHERE
A.EXECUTABLE_ID = B.EXECUTABLE_ID AND A.APPLICATION_ID = B.APPLICATION_ID AND A.executable_id > 4 AND upper(B.USER_CONCURRENT_PROGRAM_NAME) LIKE 'XX INVENTORY CONVERSION%'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications Query to get all the oracle apps responsibilities responsibility having by one oracle apps user

Oracle Applications Query to get all the oracle apps responsibilities responsibility having by one oracle apps user
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

SELECT
FRT.RESPONSIBILITY_NAME, FAT.APPLICATION_NAME, FURGD.START_DATE, FURGD.END_DATE
FROM
FND_USER_RESP_GROUPS_DIRECT FURGD, FND_RESPONSIBILITY_TL FRT, FND_RESPONSIBILITY FT, FND_APPLICATION_TL FAT, FND_USER FU
WHERE
FURGD.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID AND FURGD.RESPONSIBILITY_APPLICATION_ID = FRT.APPLICATION_ID AND FURGD.RESPONSIBILITY_APPLICATION_ID = FAT.APPLICATION_ID AND FURGD.RESPONSIBILITY_ID = FT.RESPONSIBILITY_ID AND FURGD.RESPONSIBILITY_APPLICATION_ID = FT.APPLICATION_ID AND FURGD.USER_ID =FU.USER_ID AND FT.VERSION IN ('4','W','M','H') AND FU.USER_NAME= 'PGARG'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Oracle Applications SQL Query to get all the apps users from FND_USER having particular Responsibility

Oracle Applications SQL Query to get all the apps users from FND_USER having particular Responsibility
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

SELECT
fu.user_name, frt.responsibility_name, fat.application_name, furgd.start_date, furgd.end_date
FROM
fnd_user_resp_groups_direct furgd, fnd_responsibility_tl frt, fnd_responsibility ft, fnd_application_tl fat, fnd_user fu
WHERE
furgd.responsibility_id = frt.responsibility_id AND furgd.responsibility_application_id = frt.application_id AND furgd.responsibility_application_id = fat.application_id AND furgd.responsibility_id = ft.responsibility_id AND furgd.responsibility_application_id = ft.application_id AND furgd.user_id = fu.user_id AND ft.VERSION IN ('4', 'W', 'M', 'H') AND frt.responsibility_name = 'Application Developer'

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!