Learn Oracle Apps: AOL Queries

AOL Queries / Oracle apps Queries

Query to find Active User session in oracle ebiz / Active Database Session

select distinct CLIENT_IDENTIFIER USER_NAME , status ,osuser, logon_time ,ROUND ((SYSDATE - logon_time) * 24, 2) "session_time(HR)", type ,schemaname from v$session where status='ACTIVE'order by logon_time desc;

___________________________________________________________________

Query to Find the function attached to Responsibility or MENU

SELECT DISTINCT faa.application_name application, rtl.responsibility_name,

ffl.user_function_name,

ff.function_name,

ffl.description,( select MENU_NAME from FND_MENUS where menu_id=r.menu_id) MEnu_name,

ff.TYPE

FROM fnd_compiled_menu_functions cmf,

fnd_form_functions ff,

fnd_form_functions_tl ffl,

fnd_responsibility r,

fnd_responsibility_vl rtl,

apps.fnd_application_all_view faa

WHERE cmf.function_id = ff.function_id

AND r.menu_id = cmf.menu_id

AND rtl.responsibility_id = r.responsibility_id

AND cmf.grant_flag = 'Y'

AND ff.function_id = ffl.function_id

AND faa.application_id(+) = r.application_id

AND r.end_date IS NULL

AND rtl.end_date IS NULL

and rtl.responsibility_name like 'AP Barwa Real Estate Project Accountant%'

and ff.function_name like 'XXXE%'

ORDER BY rtl.responsibility_name;

SQL Query for finding responsibility attached to user

Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE

from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c

where a.user_id = b.user_id

and a.responsibility_id = c.responsibility_id

and b.user_name='&username';

Query to find users attached to responsibility ( active users with active responsibility )

-----------------------

SELECT DISTINCT b.user_name, c.responsibility_name,

a.start_date resp_start_date, a.end_date resp_end_date,

b.start_date user_start_date, b.end_date user_end_date

FROM fnd_user_resp_groups_direct a,

fnd_user b,

fnd_responsibility_tl c

WHERE a.user_id = b.user_id

AND a.responsibility_id = c.responsibility_id

AND c.responsibility_name = '&responsibility_name'

and ( a.end_date is null or a.end_date >sysdate )

and ( b.end_date is null or b.end_date >sysdate )

Query to Find Responsibility associated with Operating unit

/* Formatted on 2015/08/17 15:41 (Formatter Plus v4.8.8) */

SELECT frv.responsibility_name, profile_option_value org_id,

(SELECT NAME

FROM hr_operating_units

WHERE organization_id = profile_option_value) "Operating Unit",

fa.application_short_name, fpo.application_id

FROM fnd_profile_options_vl fpo,

fnd_profile_option_values fpov,

fnd_responsibility_vl frv,

fnd_application fa

WHERE fpo.profile_option_name = 'ORG_ID'

AND fpov.application_id = fpo.application_id

AND fpov.profile_option_id = fpo.profile_option_id

AND frv.responsibility_id = fpov.level_value

AND fa.application_id = fpo.application_id

AND fpov.level_id = 10003;

Query to find Responsibilities Associated With Application

SELECT distinct frt.responsibility_name "Responsibility Name",

fr.responsibility_key "Responsibility Key",

fa.application_short_name "Application Short Name",

(SELECT

--fa.application_id "Application ID",

fat.application_name "Application Name"

FROM fnd_application fa1,

fnd_application_tl fat

WHERE fa.application_id = fat.application_id

AND fat.LANGUAGE = USERENV ('LANG')

AND fa1.application_short_name = fa.application_short_name)

application_name

FROM applsys.fnd_responsibility_tl frt,

applsys.fnd_responsibility fr,

applsys.fnd_application_tl fat,

applsys.fnd_application fa

WHERE 1 = 1

AND fr.responsibility_id = frt.responsibility_id

AND fa.application_id = fat.application_id

AND fr.application_id = fat.application_id

AND frt.LANGUAGE = USERENV ('LANG')

AND UPPER (fa.application_short_name) IN ('SQLAP', 'PO', 'AP', 'ICX')

ORDER BY frt.responsibility_name;

Responsibility /application / operating unit

/* Formatted on 2015/08/17 15:59 (Formatter Plus v4.8.8) */

SELECT DISTINCT frt.responsibility_name "Responsibility Name",

fr.responsibility_key "Responsibility Key",

fa.application_short_name "Application Short Name",

(SELECT

--fa.application_id "Application ID",

fat.application_name

"Application Name"

FROM fnd_application fa1,

fnd_application_tl fat

WHERE fa.application_id = fat.application_id

AND fat.LANGUAGE = USERENV ('LANG')

AND fa1.application_short_name = fa.application_short_name)

application_name,

(SELECT (SELECT NAME

FROM hr_operating_units

WHERE organization_id =

profile_option_value)

FROM fnd_profile_options_vl fpo,

fnd_profile_option_values fpov,

fnd_responsibility_vl frv

WHERE fpo.profile_option_name = 'ORG_ID'

AND fpov.application_id = fpo.application_id

AND fpov.profile_option_id = fpo.profile_option_id

AND frv.responsibility_id = fpov.level_value

AND fpov.level_id = 10003

AND frv.responsibility_id = frt.responsibility_id)

"Operating Unit"

FROM applsys.fnd_responsibility_tl frt,

applsys.fnd_responsibility fr,

applsys.fnd_application_tl fat,

applsys.fnd_application fa

WHERE 1 = 1

AND fr.responsibility_id = frt.responsibility_id

AND fa.application_id = fat.application_id

AND fr.application_id = fat.application_id

AND frt.LANGUAGE = USERENV ('LANG')

AND UPPER (fa.application_short_name) IN

('SQLAP', 'PO', 'AP', 'ICX')

ORDER BY "Operating Unit"

Query to find username /user name and employee name assigned to the user

select papf.full_name, f.* from fnd_user f , Per_All_People_f papf where papf.Person_id = f.Employee_Id and papf.full_name like 'TAMER EL SAYED MOHAMED ABD ELMOATY'

How to delete user form backend /Deleting User from backend

DECLARE

BEGIN

FOR X IN (select * from fnd_user where user_name in ('DUMMY_USER')

LOOP

DELETE FROM fnd_user where user_name=X.user_name;

fnd_function_security_cache.delete_user(X.user_id);

END LOOP;

END;

Query to find the Application short name / Application top name from application name

SELECT fa.application_id "Application ID",

fat.application_name "Application Name",

fa.application_short_name "Application Short Name",

fa.basepath "Basepath"

FROM fnd_application fa,

fnd_application_tl fat

WHERE fa.application_id = fat.application_id

AND fat.language = USERENV('LANG')

and fat.application_name='Mannai Corp Ltd'

-- AND fat.application_name = 'Payables' -- <change it>

ORDER BY fat.application_name;

Find concurrent program name from value set name and vice versa

SELECT fcpl.user_concurrent_program_name

, fcp.concurrent_program_name

, par.end_user_column_name

, par.form_left_prompt prompt

, par.enabled_flag

, par.required_flag

, par.display_flag

, par.flex_value_set_id

, ffvs.flex_value_set_name

, flv.meaning default_type

, par.DEFAULT_VALUE

FROM fnd_concurrent_programs fcp

, fnd_concurrent_programs_tl fcpl

, fnd_descr_flex_col_usage_vl par

, fnd_flex_value_sets ffvs

, fnd_lookup_values flv

WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id

--AND fcpl.user_concurrent_program_name = :conc_prg_name

AND ffvs.flex_value_Set_name like '%FND_STANDARD_DATE%'

AND fcpl.LANGUAGE = 'US'

AND par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name

AND ffvs.flex_value_set_id = par.flex_value_set_id

AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'

AND flv.lookup_code(+) = par.default_type

AND flv.LANGUAGE(+) = USERENV ('LANG')

SQL query to find the responsibility where concurrent program is registered by short name

SELECT fcpl.user_concurrent_program_name "REPORT NAME",

fnrtl.responsibility_name, frg.request_group_name,

fcp.concurrent_program_name "CONCURRENT PROGRAM SHORT NAME"

FROM apps.fnd_request_groups frg,

apps.fnd_request_group_units frgu,

apps.fnd_concurrent_programs fcp,

apps.fnd_concurrent_programs_tl fcpl,

apps.fnd_executables fe,

apps.fnd_responsibility fnr,

apps.fnd_responsibility_tl fnrtl

WHERE frg.application_id = frgu.application_id

AND frg.request_group_id = frgu.request_group_id

AND frg.request_group_id = fnr.request_group_id

AND frg.application_id = fnr.application_id

AND fnr.responsibility_id = fnrtl.responsibility_id

AND frgu.request_unit_id = fcp.concurrent_program_id

AND frgu.unit_application_id = fcp.application_id

AND fcp.concurrent_program_id = fcpl.concurrent_program_id

AND fe.execution_method_code LIKE '%%' --'Oracle Reports'

AND fcp.executable_id = fe.executable_id

AND fe.application_id = fcp.executable_application_id

AND fnrtl.responsibility_name LIKE '%'

-- Example Resp. Name : Inventory, Vision Operations (USA)

AND fnrtl.LANGUAGE = 'US'

AND fcpl.LANGUAGE = 'US'

and fcp.concurrent_program_name =:short_name

SQL query to find the submitted report and parameter passed from any user by entering report name

SELECT r.ROWID row_id, r.request_id, r.phase_code, r.status_code,

r.priority_request_id, r.priority, r.request_date, r.requested_by,

r.requested_start_date, r.hold_flag, r.has_sub_request,

r.is_sub_request, r.update_protected, r.queue_method_code,

r.responsibility_application_id, r.responsibility_id,

r.save_output_flag, r.last_update_date, r.last_updated_by,

r.last_update_login, r.printer, r.print_style, r.parent_request_id,

r.controlling_manager, r.actual_start_date, r.actual_completion_date,

r.completion_text, r.argument_text, r.implicit_code, r.request_type,

r.program_application_id, r.concurrent_program_id,

pb.concurrent_program_name program_short_name,

pb.execution_method_code, pb.enabled_flag enabled,

DECODE (r.description,

NULL, pt.user_concurrent_program_name,

r.description || ' (' || pt.user_concurrent_program_name || ')'

) program,

pb.printer_name fcp_printer, pb.output_print_style fcp_print_style,

pb.required_style fcp_required_style, u.user_name requestor,

s.user_printer_style_name user_print_style, r.description description,

pt.user_concurrent_program_name user_concurrent_program_name

FROM fnd_concurrent_programs_tl pt,

fnd_concurrent_programs pb,

fnd_user u,

fnd_printer_styles_tl s,

fnd_concurrent_requests r

WHERE pb.application_id = r.program_application_id

AND pb.concurrent_program_id = r.concurrent_program_id

AND pb.application_id = pt.application_id

AND pb.concurrent_program_id = pt.concurrent_program_id

AND pt.LANGUAGE = USERENV ('LANG')

AND u.user_id = r.requested_by

AND s.printer_style_name(+) = r.print_style

AND s.LANGUAGE(+) = USERENV ('LANG')

and pt.USER_CONCURRENT_PROGRAM_NAME ='General Ledger Transactions Running Balance Report'

Query to find Concurrent program /Report status/Parameter/Process time and details

SELECT DISTINCT c.user_concurrent_program_name, a.actual_completion_date,

(a.actual_completion_date - a.actual_start_date

) process_time_in_seconds,

ROUND (( (a.actual_completion_date - a.actual_start_date)

* 24

* 60

/ 60

) AS time_in_minute,

a.request_id, a.parent_request_id,

TO_CHAR (a.request_date, 'DD-MON-YY HH24:MI:SS'),

TO_CHAR (a.actual_start_date, 'DD-MON-YY HH24:MI:SS'),

TO_CHAR (a.actual_completion_date, 'DD-MON-YY HH24:MI:SS'),

(a.actual_completion_date - a.request_date)

* 24

* 60

* 60 AS end_to_end,

(a.actual_start_date - a.request_date)

* 24

* 60

* 60 AS lag_time,

d.user_name, a.phase_code, a.status_code, a.argument_text,

a.priority,

( select LOGFILE_NAME from fnd_concurrent_requests

where request_id = a.request_id ) logfile

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_programs b,

apps.fnd_concurrent_programs_tl c,

apps.fnd_user d

WHERE a.concurrent_program_id = b.concurrent_program_id

AND b.concurrent_program_id = c.concurrent_program_id

AND a.requested_by = d.user_id

-- and trunc(a.actual_completion_date) = '24-AUG-2005'

and c.user_concurrent_program_name in(

'xECM Program to get runtime Access to WF users','xECM for Oracle Synchronize Users')

ORDER BY actual_completion_date DESC;

Oracle Apps Attachment Facility /Disable enable

Attachment related tables

1. FND_DOCUMENTS

This table stores language-independent information about a document

2. FND_ATTACHED_DOCUMENTS

3. FND_DOCUMENTS_TL

4. FND_DOCUMENT_DATATYPES.

5. FND_DOCUMENT_CATEGORIES

6. FND_DOCUMENTS_LONG_TEXT (Long text type attachment).

7. FND_DOCUMENTS_SHORT_TEXT (Short text type attachment).

8. FND_DOCUMENTS_LONG_RAW

9. FND_LOBS (File type attachments).

10.FND_ATTACHMENT_FUNCTIONS

This table stores information about those forms and form functions for which the attach-ment feature is enabled.

We can disable( not remove) enable the attachment icon by editing the ENABLED_FLAG

By editing the FND_ATTACHMENT_FUNCTIONS table

Query to find attachments associated with PO/PR /supplier /Invoice etc.

select distinct fad.entity_name , pk1_value header_id_of_object ,fd.file_name ,fdc.name ,fdc.user_name ,fdd.user_name file_type FROM

apps.fnd_attached_documents fad,

apps.fnd_documents fd,

apps.fnd_document_categories_tl fdc,

apps.fnd_document_datatypes fdd,

apps.fnd_documents_tl fdl

where fad.entity_name ='PO_HEADERS' ----------it can be changed to desired objects

AND fad.document_id = fd.document_id

AND fdc.category_id = fd.category_id

AND fdd.datatype_id = fd.datatype_id

AND fdl.document_id = fd.document_id

AND fdl.LANGUAGE = 'US'

AND fdd.LANGUAGE ='US'

and fdc.language ='US' ;

Get Different modules present on instance by query

SELECT FND.APPLICATION_ID, APPLICATION_SHORT_NAME, PRODUCT_CODE, APPLICATION_NAME

FROM FND_APPLICATION FND , FND_APPLICATION_TL FNDTL WHERE FND.APPLICATION_ID=FNDTL.APPLICATION_ID

Monday 17 August 2015

AOL Queries / Oracle apps Queries