Complex and Frequent queries in Oracle E-Business Suite

Query to find out Profile Option values at Various levels

SELECT fpo.profile_option_name SHORT_NAME,
         fpot.user_profile_option_name NAME,
         DECODE (fpov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Responsibility',
                 10004, 'User',
                 10005, 'Server',
                 'UnDef')
            LEVEL_SET,
         DECODE (TO_CHAR (fpov.level_id),
                 '10001', '',
                 '10002', fap.application_short_name,
                 '10003', frsp.responsibility_key,
                 '10005', fnod.node_name,
                 '10006', hou.name,
                 '10004', fu.user_name,
                 'UnDef')
            "CONTEXT",
         fpov.profile_option_value VALUE
    FROM fnd_profile_options fpo,
         fnd_profile_option_values fpov,
         fnd_profile_options_tl fpot,
         fnd_user fu,
         fnd_application fap,
         fnd_responsibility frsp,
         fnd_nodes fnod,
         hr_operating_units hou
   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)
         AND fpo.profile_option_name = fpot.profile_option_name
         AND fu.user_id(+) = fpov.level_value
         AND frsp.application_id(+) = fpov.level_value_application_id
         AND frsp.responsibility_id(+) = fpov.level_value
         AND fap.application_id(+) = fpov.level_value
         AND fnod.node_id(+) = fpov.level_value
         AND hou.organization_id(+) = fpov.level_value
         AND fpot.user_profile_option_name IN ('&User_Profile_Option_Name')
ORDER BY short_name;

Active Concurrent requests 

SELECT B.USER_CONCURRENT_PROGRAM_NAME       

      ,A.ACTUAL_START_DATE

      ,A.ACTUAL_COMPLETION_DATE

      ,SYSDATE

      ,ROUND ((((SYSDATE - A.ACTUAL_START_DATE) * 24) * 60), 2) ELAPSED_TIME

      ,ROUND (SYSDATE - A.ACTUAL_START_DATE, 2) DAYS

  FROM FND_CONCURRENT_REQUESTS A, FND_CONCURRENT_PROGRAMS_TL B

 WHERE PHASE_CODE = 'R'

   AND STATUS_CODE = 'R'                                 --request_id=63702366

   AND A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID

   

Concurrent Program attached to a Responsibility Query

select responsibility_name

from fnd_responsibility_tl rsp_tl, fnd_responsibility fr, --fnd_request_groups frg,

fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt

where rsp_tl.responsibility_id = fr.responsibility_id

--and frg.request_group_id = fr.request_group_id

and fr.request_group_id = frgu.request_group_id

and fcpt.concurrent_program_id = frgu.request_unit_id

and fcpt.concurrent_program_id =118341

   

Concurrent Program attached to  Responsibility and Request group Query

SELECT frt.responsibility_name,

  frg.request_group_name,

  frgu.request_unit_type,

  frgu.request_unit_id,

  fcpt.user_concurrent_program_name

FROM  fnd_Responsibility fr,

  fnd_responsibility_tl frt,

  fnd_request_groups frg,

  fnd_request_group_units frgu,

  fnd_concurrent_programs_tl fcpt

WHERE  frt.responsibility_id = fr.responsibility_id

  AND     frg.request_group_id = fr.request_group_id

  AND     frgu.request_group_id = frg.request_group_id

  AND     fcpt.concurrent_program_id = frgu.request_unit_id

  AND     fcpt.user_concurrent_program_name = '&conc_program_name'

ORDER BY 1,2,3,4

Oracle Form attached to a responsibility query

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

Non active users in Oracle EBS instance 

 SELECT   DISTINCT

           user_name,

           p.full_name,

           application_name,

           responsibility_name,

           security_group_name,

           mp.organization_name,

           NVL (mp.organization_code, '995'),

           b.region_1,

           b.region_2,

           GREATEST (TO_DATE (u.start_date),

                     TO_DATE (ur.start_date),

                     TO_DATE (r.start_date))

              start_date,

           DECODE (

              LEAST (NVL (u.end_date, TO_DATE ('01/01/4712', 'DD/MM/YYYY')),

                     NVL (ur.end_date, TO_DATE ('01/01/4712', 'DD/MM/YYYY')),

                     NVL (r.end_date, TO_DATE ('01/01/4712', 'DD/MM/YYYY'))),

              TO_DATE ('01/01/4712', 'DD/MM/YYYY'),

              '',

              LEAST (NVL (u.end_date, NVL (ur.end_date, r.end_date)),

                     NVL (ur.end_date, NVL (u.end_date, r.end_date)),

                     NVL (r.end_date, NVL (u.end_date, ur.end_date)))

           )

              end_date

    FROM   fnd_user u,

           fnd_user_resp_groups_all ur,

           fnd_responsibility_vl r,

           fnd_application_vl a,

           fnd_security_groups_vl s,

           PER_ALL_PEOPLE_F p,

           per_all_assignments_f pa,

           org_organization_definitions mp,

           HR_ORGANIZATION_UNITS_V b

   WHERE       a.application_id = r.application_id

           AND u.user_id = ur.user_id

           AND p.person_id = u.employee_id

           --and u.user_id=p.user_id

           AND r.application_id = ur.responsibility_application_id

           AND r.responsibility_id = ur.responsibility_id

           AND ur.start_date <= SYSDATE

           AND NVL (ur.end_date, SYSDATE + 1) > SYSDATE

           AND u.start_date <= SYSDATE

           AND NVL (u.end_date, SYSDATE + 1) < SYSDATE

           AND r.start_date <= SYSDATE

           AND NVL (r.end_date, SYSDATE + 1) > SYSDATE

           AND ur.security_group_id = s.security_group_id

           AND r.version IN ('4', 'W', 'M')

           AND TRUNC (SYSDATE) BETWEEN TRUNC (p.effective_start_date)

                                   AND  TRUNC (p.effective_end_date)

           AND pa.location_id = b.location_id

           AND TRUNC (SYSDATE) BETWEEN TRUNC (pa.effective_start_date)

                                   AND  TRUNC (pa.effective_end_date)

           AND pa.person_id = p.person_id

           AND mp.organization_id(+) = pa.organization_id

-- and mp.organization_id = b.organization_id (+)

ORDER BY   user_name,

           application_name,

           responsibility_name,

           security_group_name;

Trace File path query in Oracle 

  Select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid 

|| nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File"

from V$PARAMETER u_dump 

cross join V$PARAMETER instance 

cross join V$PROCESS 

join V$SESSION on v$process.addr = V$SESSION.paddr 

where u_dump.name = 'user_dump_dest' 

and instance.name = 'instance_name' 

and V$SESSION.audsid=sys_context('userenv','sessionid');

 Oracle EBS installation query and information

  SELECT   fav.application_short_name,

           fav.application_name,

           fpi.patch_level,

           DECODE (fpi.status,

                   'N',

                   'None',

                   'I',

                   'Installed',

                   'S',

                   'Shared')

    FROM   fnd_product_installations fpi, fnd_application_vl fav

   WHERE   fav.application_id = fpi.application_id

           AND application_name LIKE '%Oracle%'

ORDER BY   1;

Concurrent Scheduled Request Queries

SELECT fcr.request_id ,

  fcpt.user_concurrent_program_name

  || NVL2(fcr.description, ' ('

  || fcr.description

  || ')', NULL) conc_prog ,

  fu.user_name requestor ,

  fu.description requested_by ,

  fu.email_address ,

  frt.responsibility_name requested_by_resp ,

  TRIM(fl.meaning) STATUS ,

  fcr.phase_code ,

  fcr.status_code ,

  fcr.argument_text "PARAMETERS" ,

  TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested ,

  TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start ,

  TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time ,

  DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold ,

  CASE

    WHEN fcr.hold_flag = 'Y'

    THEN SUBSTR( fu.description , 0 , 40 )

  END last_update_by ,

  CASE

    WHEN fcr.hold_flag = 'Y'

    THEN fcr.last_update_date

  END last_update_date ,

  fcr.increment_dates ,

  CASE

    WHEN fcrc.CLASS_INFO IS NULL

    THEN 'Yes: '

      || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')

    ELSE 'n/a'

  END run_once ,

  CASE

    WHEN fcrc.class_type = 'P'

    THEN 'Repeat every '

      || SUBSTR(fcrc.class_info, 1, instr(fcrc.class_info, ':')           - 1)

      || DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1), 'N', ' minutes', 'M', ' months', 'H', ' hours', 'D', ' days')

      || DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1), 'S', ' from the start of the prior run', 'C', ' from the completion of the prior run')

    ELSE 'n/a'

  END set_days_of_week ,

  CASE

    WHEN fcrc.class_type                         = 'S'

    AND instr(SUBSTR(fcrc.class_info, 33),'1',1) > 0

    THEN 'Days of week: '

      || DECODE(SUBSTR(fcrc.class_info, 33, 1), '1', 'Sun, ')

      || DECODE(SUBSTR(fcrc.class_info, 34, 1), '1', 'Mon, ')

      || DECODE(SUBSTR(fcrc.class_info, 35, 1), '1', 'Tue, ')

      || DECODE(SUBSTR(fcrc.class_info, 36, 1), '1', 'Wed, ')

      || DECODE(SUBSTR(fcrc.class_info, 37, 1), '1', 'Thu, ')

      || DECODE(SUBSTR(fcrc.class_info, 38, 1), '1', 'Fri, ')

      || DECODE(SUBSTR(fcrc.class_info, 39, 1), '1', 'Sat ')

    ELSE 'n/a'

  END days_of_week

FROM apps.fnd_concurrent_requests fcr ,

  apps.fnd_user fu ,

  apps.fnd_concurrent_programs fcp ,

  apps.fnd_concurrent_programs_tl fcpt ,

  apps.fnd_printer_styles_tl fpst ,

  apps.fnd_conc_release_classes fcrc ,

  apps.fnd_responsibility_tl frt ,

  apps.fnd_lookups fl

WHERE fcp.application_id       = fcpt.application_id

AND fcr.requested_by           = fu.user_id

AND fcr.concurrent_program_id  = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_id

AND fcr.concurrent_program_id  = fcpt.concurrent_program_id

AND fcr.responsibility_id      = frt.responsibility_id

AND fcr.print_style            = fpst.printer_style_name(+)

AND fcr.release_class_id       = fcrc.release_class_id(+)

AND fcr.status_code            = fl.lookup_code

AND fl.lookup_type             = 'CP_STATUS_CODE'

AND fcr.phase_code             = 'P'

AND frt.language               = 'US'

AND fpst.language              = 'US'

AND fcpt.language              = 'US'

ORDER BY Fu.Description,

  Fcr.Requested_Start_Date ASC

Concurrent Program pkg dependencies

select

 fcr.request_id

,fcpv.user_concurrent_program_name

,fcpv.concurrent_program_name

,fcpv.concurrent_program_id

,fcr.status_code

,fcr.phase_code

from fnd_concurrent_programs_vl fcpv

,fnd_executables fe

,sys.dba_dependencies dd

,fnd_concurrent_requests fcr

where fcpv.executable_id = fe.executable_id

and fe.execution_method_code = 'i'

and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)

and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required

--and referenced_owner = 'xxcus'

--and dd.referenced_name = upper('&package_name')

and fcr.concurrent_program_id = fcpv.concurrent_program_id

and fcr.phase_code not in ( 'c','p');