Query to Find Concurrent Program Details using Executable Name in Oracle Apps

--Find Concurrent Program Details using Executable Name 

SELECT fa.application_short_name,
       frg.request_group_name,
       fe.execution_file_name, 
       fe.executable_name
  FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application fa
 WHERE frgu.request_unit_id = fcp.concurrent_program_id
       AND frgu.request_group_id = frg.request_group_id 
       AND fe.executable_id = fcp.executable_id 
       AND FRG.application_id = fa.application_id
       AND fe.executable_name = :P_Executable_Name

  ;

Query to find out Concurrent programs attached to request group in Oracle Apps

Find Out Concurrent Program attache to user

SELECT request_group_name,
       DECODE (request_unit_type,
               'A', 'Application',
               'P', 'Program',
               'S', 'Request Set',
               '-NA-'
              ) request_unit_type,
       user_concurrent_program_name program_name
  FROM fnd_request_groups a,
       fnd_request_group_units b,
       fnd_concurrent_programs_vl c
 WHERE a.request_group_id = b.request_group_id
   AND b.request_unit_id = c.concurrent_program_id
   AND (c.CONCURRENT_PROGRAM_NAME = :P_Conc_Prog_Short_Name
   OR request_group_name = 'OM Concurrent Programs')
   AND request_unit_type = 'P'
;

List of Responsibility Key along with application short name attached with your fnd_user profile in Oracle Apps

  SELECT r.responsibility_key, fa.application_short_name
    FROM fnd_user u,
         fnd_user_resp_groups ur,
         fnd_responsibility r,
         fnd_application_tl a,
         fnd_application fa
   WHERE     u.user_id = ur.user_id
         AND u.user_name = :p_user_name
         AND r.version = 'W'
         AND ur.responsibility_id = r.responsibility_id
         AND fa.application_id = a.application_id
         AND r.application_id = a.application_id
ORDER BY 1

FND_PROFILE and FND_GLOBAL values in Oracle Apps

Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');

   fnd_profile.value('MFG_ORGANIZATION_ID');

   fnd_profile.value('ORG_ID');

   fnd_profile.value('LOGIN_ID');

   fnd_profile.value('USER_ID');

   fnd_profile.value('USERNAME');

   fnd_profile.value('CONCURRENT_REQUEST_ID');

   fnd_profile.value('GL_SET_OF_BKS_ID');

   fnd_profile.value('SO_ORGANIZATION_ID');

   fnd_profile.value('APPL_SHRT_NAME');

   fnd_profile.value('RESP_NAME');

   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;

   FND_GLOBAL.APPS_INTIALIZE;

   FND_GLOBAL.LOGIN_ID;

   FND_GLOBAL.CONC_LOGIN_ID;

   FND_GLOBAL.PROG_APPL_ID;

   FND_GLOBAL.CONC_PROGRAM_ID;

   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;

   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;

   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;

   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');

   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

   v_resp_appl_id  := fnd_global.resp_appl_id;

   v_resp_id       := fnd_global.resp_id;

   v_user_id       := fnd_global.user_id;

    

   FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);

Query to Check available Table-Space size details and Alter the table-space in Oracle database

-- To Check available tablespace in database



SELECT df.tablespace_name "Tablespace",
       totalusedspace "Used MB",
       (df.totalspace - tu.totalusedspace) "Free MB",
       df.totalspace "Total MB",
       ROUND (100 * ( (df.totalspace - tu.totalusedspace) / df.totalspace))
          "Pct. Free"
  FROM (  SELECT tablespace_name, ROUND (SUM (bytes) / 1048576) TotalSpace
            FROM dba_data_files
        GROUP BY tablespace_name) df,
       (  SELECT ROUND (SUM (bytes) / (1024 * 1024)) totalusedspace,
                 tablespace_name
            FROM dba_segments
        GROUP BY tablespace_name) tu
 WHERE df.tablespace_name = tu.tablespace_name;


--To alter Tablespace


SELECT FILE_NAME
  FROM dba_data_files
 WHERE TABLESPACE_NAME = :P_TABLESPACE_NAME;

 -- copy past the File_name against datafile

ALTER DATABASE
DATAFILE 'enter_file_path/with_file_name'

RESIZE 800M;

Find Employee- Supervisor Hierarchy in Oracle Apps

  SELECT papf.employee_number,           papf.person_id,
           papf.full_name,
           ppg.segment1 officer_flag, --executive flag for officers (Y is officer, N is not)
           NVL (spapf.full_name, 'NONE') supervisor_name,
           spapf.person_id supv_person_id,
           LEVEL
      FROM per_people_x papf,
           PER_PEOPLE_X SPAPF,
           per_assignments_x paaf,
           pay_people_groups ppg
     WHERE     papf.person_id = paaf.person_id
           AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
           AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date
           and sysdate between SPAPF.EFFECTIVE_START_DATE and SPAPF.EFFECTIVE_END_DATE
           --AND xxx_person_type.is_employee(:p_eff_date, papf.person_id) = 'TRUE'
           AND paaf.assignment_type IN ('E', 'C')
           AND paaf.supervisor_id = spapf.person_id
           AND paaf.people_group_id = ppg.people_group_id
           AND paaf.primary_flag = 'Y'
START WITH papf.employee_number = :p_emp_num --emp num of employee or top level supervisor?
CONNECT BY PRIOR spapf.employee_number = papf.employee_number --AND LEVEL < :p_level
  ORDER BY LEVEL DESC
;

--------------------------------------------------------------------
OR

    SELECT LEVEL seq,
           e.person_id,
           e.grade_id,
           e.job_id,
           e.supervisor_id,
           e.employee_number,
           e.full_name
      FROM (SELECT DISTINCT paf.person_id,
                            paf.grade_id,
                            paf.job_id,
                            paf.supervisor_id,
                            ppf.employee_number,
                            ppf.full_name
              FROM    per_all_people_f ppf
                   LEFT JOIN
                      per_all_assignments_f paf
                   ON     ppf.person_id = paf.person_id
                      AND paf.person_id IS NOT NULL
                      AND (SYSDATE BETWEEN ppf.effective_start_date
                                       AND ppf.effective_end_date)
                      AND (SYSDATE BETWEEN paf.effective_start_date
                                       AND paf.effective_end_date)) e
CONNECT BY person_id = PRIOR supervisor_id
START WITH employee_number = :p_employee_number         --person_id = :P_PERSON_ID
  ORDER BY LEVEL
;

Query to find the Responsibility attached to Form Function in Oracle Apps

SELECT DISTINCT responsibility_id, responsibility_name
  FROM apps.fnd_responsibility_vl a
 WHERE     a.end_date IS NULL
       AND a.menu_id IN
              (    SELECT menu_id
                     FROM apps.fnd_menu_entries_vl
               START WITH menu_id IN
                             (SELECT menu_id
                                FROM apps.fnd_menu_entries_vl
                               WHERE function_id IN
                                        (SELECT function_id
                                           FROM applsys.fnd_form_functions a
                                          WHERE function_name =
                                                   :pc_function_name))
               CONNECT BY PRIOR menu_id = sub_menu_id)
       AND a.responsibility_id NOT IN
              (SELECT responsibility_id
                 FROM apps.fnd_responsibility_vl
                WHERE responsibility_id IN
                         (SELECT responsibility_id
                            FROM applsys.fnd_resp_functions resp
                           WHERE action_id IN
                                    (SELECT function_id
                                       FROM applsys.fnd_form_functions a
                                      WHERE function_name = :pc_function_name)))
       AND a.responsibility_id NOT IN
              (SELECT responsibility_id
                 FROM apps.fnd_responsibility_vl
                WHERE responsibility_id IN
                         (SELECT responsibility_id
                            FROM applsys.fnd_resp_functions resp
                           WHERE action_id IN
                                    (    SELECT menu_id
                                           FROM apps.fnd_menu_entries_vl
                                     START WITH menu_id IN
                                                   (SELECT menu_id
                                                      FROM apps.fnd_menu_entries_vl
                                                     WHERE function_id IN
                                                              (SELECT function_id
                                                                 FROM applsys.fnd_form_functions a
                                                                WHERE function_name =
                                                                         :pc_function_name))
                                     CONNECT BY PRIOR menu_id = sub_menu_id)))