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

                        / 60

                       ),

                       2

                      ) 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