Information about Oracle APIs and How to find APIs of Oracle Modules

Generally developers will be in need of APIs to add/modify data on existing tables but its a bit difficult task to find out APIs

What is an API?

An application programming interface (API) is a source code based specification intended to be used as an interface by software components to communicate with each other. Even ever a data is entered on an Oracle Form, the data will be picked up by the corresponding APIs, validated and inserted into respective predefined tables by taking processing constraints into consideration.

Types of Oracle APIs

• Private APIs: Private APIs are for internal, development use only. Details are not provided to anyone outside of the immediate development environment, nor are they intended for use by anyone outside of the e-Business Suite development environment.
• Public APIs: Public APIs are designed for customers and Oracle consultants to integrate non-Oracle systems into Oracle e-Business Suite or to extend the functionality of the base products. Oracle does not support public APIs unless they are published in a reference manual such as this one. The user accepts all risk and responsibility for working with non-published public APIs.
• Public, published APIs: Public Published APIs are guaranteed by Oracle to remain valid from release to release and that patches will not alter the API behavior. Public, published APIs are supported by Oracle to the same extent as released software. For non-published APIs, Oracle expressly does not provide any guarantees regarding consistency of naming, usage, or behavior of any API (public or private) between releases. It is also possible that a patch could alter any characteristic of any non-published e-Business Suite API. As such, those who choose to use these APIs do so at their own risk. However, Oracle does attempt to minimize all changes to public APIs, even if not published.

For more information on type of APIs click here

How to find APIs?

Its difficult for a developer to find out APIs from DBA_OBJECTS table, so Oracle Corp. has come up with a web portal named Oracle Integration Repository (iRep) to access all public APIs.

Oracle® Integration Repository

Oracle Integration Repository is a compilation of information about the numerous interface endpoints exposed by Oracle applications. The full list of public APIs and the purpose of each API is available in the integration repository. It provides a complete catalog of Oracle E-Business Suite’s business interfaces, and a comprehensive view of the interface mechanisms available. You can use this tool to easily discover and deploy the appropriate business interface from the catalog for integration with any system, application, or business partner.
Website URL: http://www.irep.oracle.com/

Release 12 Integration Repository

In Oracle EBS R12, Integration Repository is bundled as a part of EBS suite as a responsibility “Integrated SOA Gateway”.

Where are these APIs Stored?

Each API is placed in a file with extension as ‘pls’(pl/sql source code file). These pls files are stored in Unix box in the following path

$APPLICATION_TOP/patch/115/sql/

Query to find APIs

You can use the below query to find out the APIs to some extent

 SELECT SUBSTR(a.OWNER,1,20) ,
  SUBSTR(a.NAME,1,30)        ,
  SUBSTR(a.TYPE,1,20)        ,
  SUBSTR(u.STATUS,1,10) Stat ,
  u.last_ddl_time            ,
  SUBSTR(text,1,80) Description
   FROM dba_source a,
  dba_objects u
  WHERE 2         =2
AND u.object_name = a.name
AND a.text LIKE '%Header%'
AND a.TYPE = u.object_type
AND a.name LIKE 'PA_%API%'
ORDER BY a.owner,
  a.name;

Simple DBA scripts

File Versions

This can be handy to pull out file versions:

select text from dba_source where name = 'PA_TXN_ACCUMS' and text like '%Header%';

This is another version

SELECT af.app_short_name "Application"      , af.filename "File Name"      , afv.file_version_id      , afv.creation_date      , afv.version "File Version"      , NVL (aap.patch_name, 'Default Installation') "Patch Number"      , '$' || fa.basepath || '/' || af.subdir "Location"   FROM apps.ad_files af      , apps.ad_patch_run_bug_actions apa      , apps.ad_patch_run_bugs aprb      , apps.ad_patch_runs apr      , apps.ad_patch_drivers apd      , apps.ad_applied_patches aap      , apps.ad_file_versions afv      , apps.fnd_application fa  WHERE af.filename = 'PABRLYRB.pls'    AND af.file_id = apa.file_id(+)    AND apa.common_action_id(+) = 4042    AND apa.patch_run_bug_id = aprb.patch_run_bug_id(+)    AND aprb.patch_run_id = apr.patch_run_id(+)    AND apr.patch_driver_id = apd.patch_driver_id(+)    AND apd.applied_patch_id = aap.applied_patch_id(+)    AND af.file_id = afv.file_id    AND af.app_short_name = fa.application_short_name;

Installed patches

SELECT *   FROM apps.ad_bugs ab  WHERE ab.bug_number = '14765798';   SELECT *   FROM apps.ad_applied_patches aap  WHERE aap.patch_name = '14765798';

Latest patchset level for a module

SELECT app_short_name        , MAX (patch_level)     FROM ad_patch_driver_minipks GROUP BY app_short_name ORDER BY app_short_name;

Product Patch Level

SELECT   fat.application_name        , fat.creation_date        , fat.description        , fpi.creation_date        , fpi.product_version        , fpi.status        , fpi.patch_level     FROM applsys.fnd_product_installations fpi        , applsys.fnd_application_tl fat    WHERE fat.application_id = fpi.application_id --     AND fat.application_name LIKE '%ontr%' --     AND fpi.status = 'I' ORDER BY 1;

Invalid Objects

-- INVALID OBJECTS -- http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php SELECT owner,        object_type,        object_name,        status FROM   dba_objects WHERE  status = 'INVALID' ORDER BY owner, object_type, object_name;

Nodes

SELECT fn.node_name        , fn.description        , fn.support_cp        , fn.support_forms        , fn.support_web        , fn.support_admin        , fn.status        , fn.server_address        , fn.HOST        , fn.domain        , fn.webhost        , fn.support_db     FROM applsys.fnd_nodes fn ORDER BY node_id;

DBA System / Session Info

SELECT release_name FROM apps.fnd_product_groups; -- current release SELECT * from v$version; SELECT NAME FROM v$database; SELECT instance_name FROM v$instance; SELECT * FROM GLOBAL_NAME; SELECT VALUE FROM v$parameter WHERE NAME = 'db_name'; SELECT TO_NUMBER(TRANSLATE(SUBSTR(VERSION, 1, 9), '1.$', '1')) FROM v$instance; SELECT s.machine FROM v$session s WHERE s.audsid = USERENV('sessionid'); SELECT GLOBAL_NAME FROM GLOBAL_NAME; SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS INSTANCE FROM DUAL; SELECT SYS_CONTEXT('USERENV', 'TERMINAL') FROM DUAL;

EXCLUSIONS in Oracle Apps

SIMPLE EXCLUSIONS LIST

SELECT frt.responsibility_name      , frf.creation_date      , frf.created_by      , DECODE (frf.rule_type,  'M', 'Menu',  'F', 'Function') type_      , CASE           WHEN frf.rule_type = 'M'           THEN              (SELECT fmv.user_menu_name                 FROM apps.fnd_menus_vl fmv                WHERE frf.action_id = fmv.menu_id                  AND frf.rule_type = 'M')           WHEN frf.rule_type = 'F'           THEN              (SELECT ffvl.user_function_name                 FROM apps.fnd_form_functions_vl ffvl                WHERE frf.action_id = ffvl.function_id                  AND frf.rule_type = 'F')        END           detail      , CASE           WHEN frf.rule_type = 'M'           THEN              (SELECT fmv.menu_name                 FROM apps.fnd_menus fmv                WHERE frf.action_id = fmv.menu_id                  AND frf.rule_type = 'M')           WHEN frf.rule_type = 'F'           THEN              (SELECT ffvl.function_name                 FROM apps.fnd_form_functions ffvl                WHERE frf.action_id = ffvl.function_id                  AND frf.rule_type = 'F')        END           detail2   FROM apps.fnd_resp_functions frf      , applsys.fnd_responsibility_tl frt      , applsys.fnd_user fu  WHERE frf.responsibility_id = frt.responsibility_id    AND frf.created_by = fu.user_id    AND frt.responsibility_name LIKE '%General%Ledger%';

ALL EXCLUSIONS ON RESPONSIBILITIES, ANOTHER WAY TO GET INFO USING A UNION

SELECT frt.responsibility_name      , fmv.menu_name      , fmv.user_menu_name menu      , fmv.description menu_desc      , '' fcn_name      , '' fcn      , '' fcn_desc   FROM apps.fnd_resp_functions frf      , applsys.fnd_responsibility_tl frt      , apps.fnd_menus_vl fmv  WHERE frf.responsibility_id = frt.responsibility_id    AND frf.action_id = fmv.menu_id    AND frf.rule_type = 'M'    AND frt.responsibility_name = 'AP Inquiry' UNION SELECT frt.responsibility_name      , ffvl.function_name      , '' menu      , '' menu_desc      , ffvl.function_name fcn_name      , ffvl.user_function_name fcn      , ffvl.description fcn_desc   FROM apps.fnd_resp_functions frf      , applsys.fnd_responsibility_tl frt      , apps.fnd_form_functions_vl ffvl  WHERE frf.responsibility_id = frt.responsibility_id    AND frf.action_id = ffvl.function_id    AND frf.rule_type = 'F'    AND frt.responsibility_name = 'AP Inquiry';

ALL EXCLUSIONS ON RESPONSIBILITIES WITH RESP ASSIGNED STATS

SELECT frt.responsibility_name      , (SELECT DISTINCT COUNT (*)     FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg     WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id     AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE     AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) user_ct      , fmv.menu_name      , fmv.user_menu_name menu      , fmv.description menu_desc      , '' fcn_name      , '' fcn      , '' fcn_desc   FROM apps.fnd_resp_functions frf      , applsys.fnd_responsibility_tl frt      , apps.fnd_menus_vl fmv  WHERE frf.responsibility_id = frt.responsibility_id    AND frf.action_id = fmv.menu_id    AND frf.rule_type = 'M' --   AND frt.responsibility_name = 'AP Inquiry'    AND (SELECT DISTINCT COUNT (*)     FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg     WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id     AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE     AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) > 0 UNION SELECT frt.responsibility_name      , (SELECT DISTINCT COUNT (*)     FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg     WHERE furg.user_id = fu.user_id     AND frt.responsibility_id = furg.responsibility_id     AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE     AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) user_ct      , ffvl.function_name      , '' menu      , '' menu_desc      , ffvl.function_name fcn_name      , ffvl.user_function_name fcn      , ffvl.description fcn_desc   FROM apps.fnd_resp_functions frf      , applsys.fnd_responsibility_tl frt      , apps.fnd_form_functions_vl ffvl  WHERE frf.responsibility_id = frt.responsibility_id    AND frf.action_id = ffvl.function_id    AND frf.rule_type = 'F' --   AND frt.responsibility_name = 'AP Inquiry'    AND (SELECT DISTINCT COUNT (*)     FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg     WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id     AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE     AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) > 0;

MENU EXCLUSIONS ON RESPONSIBILITIES

SELECT frt.responsibility_name      , fmv.menu_name      , fmv.user_menu_name ex_name      , fmv.description menu_description   FROM apps.fnd_resp_functions frf      , applsys.fnd_responsibility_tl frt      , apps.fnd_menus_vl fmv  WHERE frf.responsibility_id = frt.responsibility_id    AND frf.action_id = fmv.menu_id    AND frf.rule_type = 'M'    AND frt.responsibility_name = 'Inventory';

FUNCTION EXCLUSIONS ON RESPONSIBILITIES

SELECT frt.responsibility_name      , ffvl.function_name      , ffvl.user_function_name      , ffvl.description function_description   FROM apps.fnd_resp_functions frf      , applsys.fnd_responsibility_tl frt      , apps.fnd_form_functions_vl ffvl  WHERE frf.responsibility_id = frt.responsibility_id    AND frf.action_id = ffvl.function_id    AND frf.rule_type = 'F'    AND ffvl.function_name = 'INV_INVTTMTX_MISC' --   AND frt.responsibility_name = 'Inventory'    AND 1 = 1;

Responsibilities User Access in Oracle Apps

User Access List, linked to HR tables

SELECT   papf.full_name        , papf.employee_number empno        , fu.user_name un        , fu.description        , haout.NAME hr_org             , frt.responsibility_name        , papf.email_address        , hlat.description user_location        , hlat.location_code user_location_code        , fat.application_name application        , furg.start_date resp_start     FROM applsys.fnd_user fu        , applsys.fnd_user fu2        , applsys.fnd_user fu3        , hr.per_all_people_f papf        , hr.per_all_assignments_f paaf        , hr.hr_all_organization_units_tl haout        , hr.hr_locations_all_tl hlat        , hr.per_person_type_usages_f pptu        , apps.fnd_user_resp_groups_direct furg        , apps.fnd_responsibility fr        , apps.fnd_responsibility_tl frt        , applsys.fnd_application fa        , applsys.fnd_application_tl fat    WHERE papf.person_id = fu.employee_id(+)      AND paaf.location_id = hlat.location_id(+)      AND fa.application_id = fat.application_id      AND haout.organization_id = paaf.organization_id      AND frt.application_id = fat.application_id      AND fr.responsibility_id = frt.responsibility_id      AND paaf.person_id = papf.person_id      AND furg.user_id = fu.user_id      AND frt.responsibility_id = furg.responsibility_id      AND furg.created_by = fu3.user_id      AND furg.last_updated_by = fu2.user_id      AND papf.person_id = pptu.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 pptu.effective_start_date AND pptu.effective_end_date      AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE      AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE      AND papf.current_employee_flag = 'Y'      AND paaf.assignment_type = 'E'      AND paaf.primary_flag = 'Y'      AND fu.user_name = 'SYSADMIN' --     AND FA.APPLICATION_SHORT_NAME = 'INV' --     AND frt.responsibility_name = 'System Administrator' ORDER BY papf.full_name        , frt.responsibility_name;

Simple access list, with no link to HR tables

Sometimes it can be handy to look at access without joining to HR tables, as HR tables are date tracked and can sometimes return strange data depending on how today's date interacts with the effective dates on the HR tables.

SELECT DISTINCT fu.user_name               , fu.description               , fu.email_address               , fu.last_logon_date               , furg.creation_date resp_added               , furg.start_date resp_start_date               , frt.responsibility_name               , fmt.user_menu_name            FROM applsys.fnd_user fu               , apps.fnd_user_resp_groups_direct furg               , apps.fnd_responsibility_tl frt               , apps.fnd_responsibility fr               , apps.fnd_application fa               , applsys.fnd_menus_tl fmt           WHERE fr.responsibility_id = frt.responsibility_id             AND frt.responsibility_id = furg.responsibility_id             AND furg.user_id = fu.user_id             AND fr.application_id = fa.application_id             AND fr.menu_id = fmt.menu_id             AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE             AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE             AND frt.responsibility_name = 'System Administrator' --            AND fu.user_name = :un --            AND furg.CREATION_DATE >= '01-JAN-2007' --            AND fa.application_short_name = 'PO' --            AND fu.user_name = 'SYSADMIN'               AND 1 = 1;

User Count Per Application

SELECT DISTINCT fa.application_short_name, count(*) ct            FROM applsys.fnd_user fu               , apps.fnd_user_resp_groups_direct furg               , apps.fnd_responsibility_tl frt               , apps.fnd_responsibility fr               , apps.fnd_application fa           WHERE fr.responsibility_id = frt.responsibility_id             AND frt.responsibility_id = furg.responsibility_id             AND furg.user_id = fu.user_id             AND fr.application_id = fa.application_id             AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE             AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE --            AND fu.user_name = :un --            AND furg.CREATION_DATE >= '01-JAN-2007' --            AND frt.responsibility_name = 'System Administrator' --            AND fa.application_short_name = 'INV'        GROUP BY fa.application_short_name        ORDER BY 1;

User Count Per Responsibility

SELECT fat.application_name module      , frt.responsibility_name      , fr.responsibility_key      , (SELECT DISTINCT COUNT(*)                    FROM applsys.fnd_user fu                       , apps.fnd_user_resp_groups_direct furg                   WHERE furg.user_id = fu.user_id                     AND frt.responsibility_id = furg.responsibility_id                     AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE) user_ct   FROM applsys.fnd_responsibility fr      , applsys.fnd_responsibility_tl frt      , applsys.fnd_application_tl fat      , applsys.fnd_request_groups frg  WHERE fr.responsibility_id = frt.responsibility_id    AND frt.application_id = fat.application_id    AND fr.request_group_id = frg.request_group_id    AND frt.responsibility_name LIKE '%GL%'    AND fr.responsibility_key NOT LIKE '%SCH%'    AND fat.application_name = 'General Ledger'    AND (SELECT COUNT(*)           FROM applsys.fnd_user fu              , apps.fnd_user_resp_groups_direct furg          WHERE furg.user_id = fu.user_id            AND frt.responsibility_id = furg.responsibility_id            AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE) > 1    AND NVL(fr.end_date, SYSDATE + 1) > SYSDATE;

User Management SQL

-- ONCE ON RELEASE 12, USER MANAGEMENT ALLOWS ACCESS TO BE GRANTED VIA 'ROLES' -- THIS SQL CAN BE HANDY TO FIND OUT ABOUT ROLES ASSIGNED TO USERS   SELECT   fu.user_name        , fu.description        , urav.display_name     FROM apps.umx_role_assignments_v urav        , applsys.fnd_user fu    WHERE urav.user_id = fu.user_id      AND urav.user_name IN ('SYSADMIN')      AND role_name LIKE '%UMX%' --     AND status_code = 'APPROVED' --     AND urav.display_name = 'Chief Cook and Bottle Washer' ORDER BY fu.description        , urav.display_name;

RESPONSIBILITY NAME WITH MENUS DETAILS IN ORACLE APPS

-- CHECK FOR A RESPONSIBILITY NAME WITH MENUS, etc.

  SELECT frt.responsibility_id "ID"
       , frt.responsibility_name "Responsibility Name"
       , (SELECT DISTINCT COUNT (*)
                 FROM applsys.fnd_user fu
                        , apps.fnd_user_resp_groups_direct furg
                    WHERE furg.user_id = fu.user_id
                      AND frt.responsibility_id = furg.responsibility_id
                      AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE
                      AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE
                      AND fr.end_date IS NULL) user_ct
       , fa.application_id "Resp Appl ID"
       , fa.application_short_name "Application"
       , fat.application_name "Application Name"
       , fr.responsibility_key "Responsibility Key"
       , frt.description "Responsibility Description"
       , fr.start_date "Start Date"
       , DECODE (fr.version, '4', 'Oracle Applications', 'W', 'Oracle Self Service Web Applications') "Available From"
       , fdg.data_group_name "Data Group Name"
       , fat2.application_name "Data Group Application"
       , fmt.user_menu_name "Menu"
       , frg.request_group_name "Request Group Name"
       , fat3.application_name "Request Group Application"
       , fr.creation_date "Responsibility Creation Date"
       , fu.description "Responsibility Created By"
    FROM applsys.fnd_responsibility_tl frt
       , applsys.fnd_responsibility fr
       , applsys.fnd_application fa
       , applsys.fnd_application_tl fat
       , applsys.fnd_application_tl fat2
       , applsys.fnd_application_tl fat3
       , applsys.fnd_data_groups fdg
       , applsys.fnd_menus_tl fmt
       , applsys.fnd_request_groups frg
       , applsys.fnd_user fu
   WHERE fr.responsibility_id = frt.responsibility_id
     AND fr.application_id = fat.application_id
     AND fa.application_id = fat.application_id
     AND fr.data_group_application_id = fat2.application_id(+)
     AND fr.group_application_id = fat3.application_id(+)
     AND fr.data_group_id = fdg.data_group_id(+)
     AND fr.menu_id = fmt.menu_id(+)
     AND fr.request_group_id = frg.request_group_id(+)
     AND fr.end_date IS NULL
     AND frt.responsibility_name = 'System Administrator'
ORDER BY 2;

-- SIMPLE CHECK FOR A RESPONSIBILITY NAME

  SELECT frt.responsibility_name r_name
       , frt.responsibility_id id
       , frt.application_id app_id
       , fr.responsibility_key key
       , fa.application_short_name app_short
       , fat.application_name app_full
       , fr.start_date
       , fr.end_date
       , frt.description
       , frt.creation_date created_on
    FROM applsys.fnd_responsibility_tl frt
       , applsys.fnd_responsibility fr
       , applsys.fnd_application fa
       , applsys.fnd_application_tl fat
   WHERE fr.responsibility_id = frt.responsibility_id
     AND fa.application_id = fat.application_id
     AND fr.application_id = fat.application_id
     AND frt.responsibility_name = 'System Administrator'
     AND NVL (fr.end_date, SYSDATE + 1) > SYSDATE
ORDER BY frt.responsibility_name;

EXECUTABLES - basic details

SELECT fe.executable_name
     , fe.execution_method_code
     , DECODE (fe.execution_method_code
             , 'A', 'Spawned'
             , 'B', 'Request Set Stage Function'
             , 'E', 'Perl Concurrent Program'
             , 'H', 'Host'
             , 'I', 'PL/SQL Stored Procedure'
             , 'J', 'Java Stored Procedure'
             , 'K', 'Java Concurrent Program'
             , 'L', 'SQL*Loader'
             , 'M', 'Multi Language Function'
             , 'P', 'Oracle Reports'
             , 'Q', 'SQL*Plus'
             , 'S', 'Immediate'
             , 'Other')
          execution_method_code_decode
     , fet.user_executable_name
     , fet.description executable_description
     , fe.execution_file_name
  FROM applsys.fnd_executables fe
     , applsys.fnd_executables_tl fet
 WHERE fe.executable_id = fet.executable_id;

EXECUTABLES - COUNTING DETAILS

 
          SELECT fe.execution_method_code
       , DECODE (fe.execution_method_code
               , 'A', 'Spawned'
               , 'B', 'Request Set Stage Function'
               , 'E', 'Perl Concurrent Program'
               , 'H', 'Host'
               , 'I', 'PL/SQL Stored Procedure'
               , 'J', 'Java Stored Procedure'
               , 'K', 'Java Concurrent Program'
               , 'L', 'SQL*Loader'
               , 'M', 'Multi Language Function'
               , 'P', 'Oracle Reports'
               , 'Q', 'SQL*Plus'
               , 'S', 'Immediate'
               , 'Other')
            execution_method_code
       , COUNT (*) ct
    FROM applsys.fnd_executables fe
       , applsys.fnd_executables_tl fet
   WHERE fe.executable_id = fet.executable_id
GROUP BY fe.execution_method_code
       , DECODE (fe.execution_method_code
               , 'A', 'Spawned'
               , 'B', 'Request Set Stage Function'
               , 'E', 'Perl Concurrent Program'
               , 'H', 'Host'
               , 'I', 'PL/SQL Stored Procedure'
               , 'J', 'Java Stored Procedure'
               , 'K', 'Java Concurrent Program'
               , 'L', 'SQL*Loader'
               , 'M', 'Multi Language Function'
               , 'P', 'Oracle Reports'
               , 'Q', 'SQL*Plus'
               , 'S', 'Immediate'
               , 'Other')
ORDER BY 3 DESC;

CONCURRENT REQUEST BASIC DETAILS

SELECT fcpt.user_concurrent_program_name
     , fcp.concurrent_program_name
     , fcp.output_file_type
     , fat.application_name application
     , fcpt.description prog_description
     , fe.executable_name
     , DECODE (fe.execution_method_code
             , 'A', 'Spawned'
             , 'B', 'Request Set Stage Function'
             , 'E', 'Perl Concurrent Program'
             , 'H', 'Host'
             , 'I', 'PL/SQL Stored Procedure'
             , 'J', 'Java Stored Procedure'
             , 'K', 'Java Concurrent Program'
             , 'L', 'SQL*Loader'
             , 'M', 'Multi Language Function'
             , 'P', 'Oracle Reports'
             , 'Q', 'SQL*Plus'
             , 'S', 'Immediate'
             , 'Other') execution_method_code
     , fet.user_executable_name
     , fet.description executable_description
     , fe.execution_file_name
  FROM applsys.fnd_concurrent_programs fcp
     , applsys.fnd_concurrent_programs_tl fcpt
     , applsys.fnd_application_tl fat
     , applsys.fnd_executables fe
     , applsys.fnd_executables_tl fet
 WHERE fcp.concurrent_program_id = fcpt.concurrent_program_id
   AND fcp.application_id = fat.application_id
   AND fcp.executable_id = fe.executable_id(+)
   AND NVL (fe.executable_id, 0) = fet.executable_id
   AND fcpt.user_concurrent_program_name LIKE '%Requisi%Import%'
--   AND fat.application_name = 'General Ledger'
   AND 1 = 1;

Menus - Tree Walking

SELECT fmev.entry_sequence seq
                , LEVEL
                , LPAD (' ', (LEVEL - 1) * 3, ' ') || fmv.user_menu_name menu
                , LPAD (' ', (LEVEL - 1) * 3, ' ') || fmev.prompt prompt
                , fmv.menu_name
                , fmv.user_menu_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT user_function_name
                           FROM apps.fnd_form_functions_vl
                          WHERE function_id = fmev.function_id)
                  END
                     user_function_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT function_name
                           FROM apps.fnd_form_functions_vl
                          WHERE function_id = fmev.function_id)
                  END
                     function_name
                , fmev.description
             FROM apps.fnd_menus_vl fmv
                , apps.fnd_menu_entries_vl fmev
            WHERE fmev.menu_id = fmv.menu_id
       CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id
       START WITH fmv.user_menu_name = 'INV_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence;

Menu Flat View

 

SELECT fmev.entry_sequence seq
     , fmev.prompt
     , fmev.description
     -- sub menu details
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT user_menu_name FROM apps.fnd_menus_vl e WHERE e.menu_id = fmev.sub_menu_id) END submenu
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT creation_date FROM apps.fnd_menus_vl e WHERE e.menu_id = fmev.sub_menu_id) END submenu_cr_date
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT fu.user_name FROM apps.fnd_menus_vl e, applsys.fnd_user fu WHERE e.menu_id = fmev.sub_menu_id AND e.created_by = fu.user_id) END submenu_cr_by            
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT last_update_date FROM apps.fnd_menus_vl e WHERE e.menu_id = fmev.sub_menu_id) END submenu_up_date
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT fu.user_name FROM apps.fnd_menus_vl e, applsys.fnd_user fu WHERE e.menu_id = fmev.sub_menu_id AND e.last_updated_by = fu.user_id) END submenu_up_by                  
     -- function details
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT function_name FROM apps.fnd_form_functions_vl WHERE function_id = fmev.function_id) END fcn
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT user_function_name FROM apps.fnd_form_functions_vl WHERE function_id = fmev.function_id) END user_fcn_name
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT fmev.creation_date FROM apps.fnd_form_functions_vl WHERE function_id = fmev.function_id) END fcn_added_to_menu_date
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT fu.user_name FROM apps.fnd_form_functions_vl f, applsys.fnd_user fu WHERE function_id = fmev.function_id AND fmev.created_by = fu.user_id) END fcn_added_to_menu_cr_by         
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT fmev.last_update_date FROM apps.fnd_form_functions_vl WHERE function_id = fmev.function_id) END fcn_added_to_menu_update_date    
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT fu.user_name FROM apps.fnd_form_functions_vl f, applsys.fnd_user fu WHERE function_id = fmev.function_id AND fmev.last_updated_by = fu.user_id) END fcn_added_to_menu_up_by                   
  FROM apps.fnd_menus_vl fmv
     , apps.fnd_menu_entries_vl fmev
 WHERE fmev.menu_id = fmv.menu_id
   AND user_menu_name = 'INV_NAVIGATE';

Responsibilities attached to a Menu

  SELECT DISTINCT frt.responsibility_name
                , fr.responsibility_key
                , fr.responsibility_id
                , fm.menu_name
                , fmt.user_menu_name
    FROM applsys.fnd_responsibility fr
       , applsys.fnd_responsibility_tl frt
       , applsys.wf_local_user_roles wlur
       , applsys.fnd_menus_tl fmt
       , applsys.fnd_menus fm
   WHERE fr.application_id = frt.application_id
     AND fr.responsibility_id = frt.responsibility_id
     AND fr.responsibility_id = wlur.role_orig_system_id
     AND fm.menu_id = fmt.menu_id
     AND fr.menu_id = fmt.menu_id
     AND fr.menu_id IN
            (    SELECT menu_id
                   FROM applsys.fnd_menu_entries fme
             CONNECT BY PRIOR fme.menu_id = fme.sub_menu_id
             START WITH fme.menu_id = (SELECT fmv.menu_id
                                         FROM apps.fnd_menus_vl fmv
                                        WHERE fmv.user_menu_name = :menu))
ORDER BY 2;

-- without linking to responsibilities (since some menus are not linked to responsibilities,
-- but might be a parent menu containing a sub menu you want to delete)

  SELECT DISTINCT fm.menu_name
                , fmt.user_menu_name
    FROM applsys.fnd_menus_tl fmt
       , applsys.fnd_menus fm
   WHERE fm.menu_id = fmt.menu_id
     AND fmt.user_menu_name != :menu
     AND fmt.menu_id IN
            (    SELECT menu_id
                   FROM applsys.fnd_menu_entries fme
             CONNECT BY PRIOR fme.menu_id = fme.sub_menu_id
             START WITH fme.menu_id = (SELECT fmv.menu_id
                                         FROM apps.fnd_menus_vl fmv
                                        WHERE fmv.user_menu_name = :menu))
ORDER BY 2;

Responsibilities attached to a Function

Return resps with access to a particular function, any level down, not just top level
 enter function name - .e.g AR_ARXCWMAI_QIT

SELECT DISTINCT frt.responsibility_id
              , frt.responsibility_name
              , fr.responsibility_key
              , fa.application_short_name
           FROM applsys.fnd_responsibility fr
              , applsys.fnd_responsibility_tl frt
              , applsys.fnd_application fa
              , applsys.wf_local_user_roles wlur
          WHERE fa.application_id = fr.application_id
            AND fr.application_id = frt.application_id
            AND fr.responsibility_id = frt.responsibility_id
            AND fr.responsibility_id = wlur.role_orig_system_id
            AND wlur.role_orig_system = 'FND_RESP'
            AND SYSDATE BETWEEN fr.start_date AND NVL(fr.end_date
                                                  , SYSDATE + 1)
            AND SYSDATE BETWEEN wlur.start_date AND NVL(wlur.expiration_date, SYSDATE + 1)
            AND fr.menu_id IN(
                   SELECT     menu_id
                         FROM applsys.fnd_menu_entries fme
                   CONNECT BY PRIOR fme.menu_id = fme.sub_menu_id
                   START WITH fme.function_id =
                                     (SELECT function_id
                                        FROM applsys.fnd_form_functions fff
                                       WHERE fff.function_name = :function_name));

Po without Invoice Detail In Oracle Apps

/* Formatted on 12/27/2013 4:02:40 PM (QP5 v5.114.809.3010) */
  SELECT   pha.segment1,
            pla.line_num,
           as1.vendor_name,
           ass.vendor_site_code,
           pla.quantity,
           pll.quantity_billed,
           pll.quantity_received
    FROM   po_headers_all pha,
           po_lines_all pla,
           po_line_locations_all pll,
           po_distributions_all pda,
           ap_suppliers as1,
           ap_supplier_sites_all ass
   WHERE       pha.po_header_id = pla.po_header_id
           AND pla.po_line_id = pll.po_line_id
           AND pll.line_location_id = pda.line_location_id
           AND pha.vendor_id = as1.vendor_id
           AND pha.vendor_site_id = ass.vendor_site_id
           AND as1.vendor_id = ass.vendor_id
           AND pha.org_id in(123)
           AND pll.quantity > pll.quantity_billed
           AND pll.quantity = pll.quantity_received                      --> 0
           AND pll.quantity_billed = 0
           AND pha.authorization_status = 'APPROVED'
           AND ROWNUM < 10
--           AND pha.segment1 = '29302'
ORDER BY   pha.creation_date DESC

HRMS basic SQL in oracle apps

Most Basic HR SQL

A lot of HR tables have date tracking on them as part of the HRMS date tracking functionality... as such, you'll often seen bits in the SQL to do with looking at the effective_start_date and effective_end_dates.
If you search for this:

SELECT   papf.full_name
       , papf.person_id
       , papf.employee_number empno
       , papf.last_update_date last_updated
       , fu.description updated_by
       , papf.business_group_id bg
       , papf.current_employee_flag cur_emp_flag
       , papf.effective_start_date
       , papf.effective_end_date
    FROM hr.per_all_people_f papf
   WHERE papf.full_name = "Smith, Mr Unique"
ORDER BY papf.last_update_date DESC;

You might get a number of different rows. That's because even though only one man called "Unique Smith" worked for your organisation, he might have had a number of changes over the course of his employment.
As these are HR tables, when changes are made, instead of just updating the same record, the existing record is end-dated and a new one is created.
To restrict the SQL to only return the row relevant today's date, change the SQL above to this:

SELECT   papf.full_name        , papf.person_id        , papf.employee_number empno        , papf.last_update_date last_updated        , fu.description updated_by        , papf.business_group_id bg        , papf.current_employee_flag cur_emp_flag        , papf.effective_start_date        , papf.effective_end_date     FROM hr.per_all_people_f papf    WHERE papf.full_name = "Smith, Mr Unique"      AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date ORDER BY papf.last_update_date DESC;

HR Assignments

The concept of date tracking become even more apparent in the assignments table, since over the course of their employment staff can have any number of changes made to the job they do, e.g. as they work their way up through the organisation, they get promoted and are given new jobs.
Some people have more than one current job as well - in which case you might want to include a check in the SQL to only return their primary assignment.
To include assignment information, the above SQL can be developed to include assignment, job and hr-org tables:\

SELECT papf.business_group_id bg      , papf.employee_number empno      , papf.full_name      , paaf.last_update_date assg_updated      , papf.last_update_date papf_updated      , fu.description assg_updated_by      , papf.effective_start_date papf_start      , papf.effective_end_date papf_end      , paaf.assignment_number assg_num      , paaf.effective_start_date paaf_start      , paaf.effective_end_date paaf_end      , papf.current_employee_flag      , haou.name hr_org      , pj.name job_title      , papf.email_address      , paaf.normal_hours     FROM hr.per_all_people_f papf        , hr.per_all_assignments_f paaf        , hr.per_jobs pj        , hr.hr_all_organization_units haou    WHERE papf.person_id = paaf.person_id      AND paaf.job_id = pj.job_id(+)      AND paaf.organization_id = haou.organization_id(+)      AND papf.full_name = "Smith, Mr Unique"      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 papf.current_employee_flag = 'Y' -- person is current_employee      AND paaf.assignment_type = 'E'       -- another useful flag to check      AND paaf.primary_flag = 'Y'          -- return only primary assignment      AND papf.full_name LIKE :pn ORDER BY papf.last_update_date DESC;

Seeing information from the past

You can alter your SQL to look at information from the past - for example, to see the job someone did on August 5th 2009:

SELECT papf.business_group_id bg      , papf.employee_number empno      , papf.full_name      , paaf.last_update_date assg_updated      , papf.last_update_date papf_updated      , fu.description assg_updated_by      , papf.effective_start_date papf_start      , papf.effective_end_date papf_end      , paaf.assignment_number assg_num      , paaf.effective_start_date paaf_start      , paaf.effective_end_date paaf_end      , papf.current_employee_flag      , haou.name hr_org      , pj.name job_title      , papf.email_address      , paaf.normal_hours     FROM hr.per_all_people_f papf        , hr.per_all_assignments_f paaf        , hr.per_jobs pj        , hr.hr_all_organization_units haou    WHERE papf.person_id = paaf.person_id      AND paaf.job_id = pj.job_id(+)      AND paaf.organization_id = haou.organization_id(+)      AND papf.full_name = "Smith, Mr Unique"      AND '05-AUG-2009' BETWEEN papf.effective_start_date AND papf.effective_end_date      AND '05-AUG-2009' BETWEEN paaf.effective_start_date AND paaf.effective_end_date      AND papf.current_employee_flag = 'Y' -- person is current_employee      AND paaf.assignment_type = 'E'       -- another useful flag to check      AND paaf.primary_flag = 'Y'          -- return only primary assignment      AND papf.full_name LIKE :pn ORDER BY papf.last_update_date DESC;

Supervisors / location info etc.

As per the intro to this page, the staff listing reports page has some additional examples, showing how you can find out who a person's supervisor is, the location they work in, their approval limit etc. etc.

Form personalisations Query

Including header and action details

  SELECT ffcr.function_name
       , ffcr.form_name
       , fft.user_form_name
       , fat.application_name application
       , ffcr.sequence seq
       , ffcr.description
       , DECODE (ffcr.rule_type,  'A', 'Function',  'F', 'Form') level_
       , ffcr.enabled
       , ffcr.creation_date
       , fu.description created_by
       , ffcr.trigger_event condition_trigger_event
       , ffcr.trigger_object condition_trigger_object
       , ffcr.condition
       , DECODE (ffcr.fire_in_enter_query
               , 'N', 'Not in Enter-Query Mode'
               , 'O', 'Only in Enter-Query Mode'
               , 'Y', 'Both')
            condition_processing_mode
       , ffca.summary
       , ffca.sequence action_seq
       , ffca.object_type action_object_type      
       , CASE
            WHEN ffca.action_type = 'P' THEN 'Property'
            WHEN ffca.action_type = 'M' THEN 'Message'
            WHEN ffca.action_type = 'B' THEN 'Bultin'
            WHEN ffca.action_type = 'S' THEN 'Menu'
            ELSE ffca.action_type
         END
            action_type
       , ffca.summary action_desc
       , ffca.enabled action_enabled
       , CASE
            WHEN ffca.action_type = 'P'
            THEN
               ffca.target_object
            WHEN ffca.action_type = 'M'
            THEN
               CASE
                  WHEN ffca.MESSAGE_TYPE = 'S' THEN 'Show'
                  WHEN ffca.MESSAGE_TYPE = 'H' THEN 'Hint'
                  WHEN ffca.MESSAGE_TYPE = 'E' THEN 'Error'
                  WHEN ffca.MESSAGE_TYPE = 'D' THEN 'Debug'
                  WHEN ffca.MESSAGE_TYPE = 'W' THEN 'Warn'
                  ELSE ffca.MESSAGE_TYPE
               END
            WHEN ffca.action_type = 'B'
            THEN
               CASE
                  WHEN ffca.builtin_type = 'U'
                  THEN
                     'Launch URL'
                  WHEN ffca.builtin_type = 'C'
                  THEN
                     'Launch SRS Form'
                  WHEN ffca.builtin_type = 'E'
                  THEN
                     'Launch a Function'
                  WHEN ffca.builtin_type = 'D'
                  THEN
                     'Do Key'
                  WHEN ffca.builtin_type = 'P'
                  THEN
                     'Exceute a Procedure'
                  WHEN ffca.builtin_type = 'G'
                  THEN
                     'Go Item'
                  WHEN ffca.builtin_type = 'B'
                  THEN
                     'Go Block'
                  WHEN ffca.builtin_type = 'F'
                  THEN
                     'Forms DDL'
                  WHEN ffca.builtin_type = 'R'
                  THEN
                     'Raise Forms Trigger Failure'
                  WHEN ffca.builtin_type = 'T'
                  THEN
                     'Execute Trigger'
                  WHEN ffca.builtin_type = 'S'
                  THEN
                     'Synchronize'
                  ELSE
                     ffca.builtin_type
               END
            WHEN ffca.action_type = 'S'
            THEN
               ffca.menu_entry
         END
            action_target_object
       , CASE
            WHEN ffca.action_type = 'P' THEN ffcpl.property_name
            WHEN ffca.action_type = 'M' THEN ffca.MESSAGE_TEXT
            WHEN ffca.action_type = 'B' THEN ffca.builtin_arguments
            WHEN ffca.action_type = 'S' THEN ffca.menu_label
         END
            action_property_name
       , CASE
            WHEN ffca.action_type = 'P'
            THEN
               CASE
                  WHEN ffca.property_value = '4' THEN 'True'
                  WHEN ffca.property_value = '5' THEN 'False'
                  ELSE ffca.property_value
               END
            WHEN ffca.action_type = 'B'
            THEN
               ffca.menu_argument_short
            WHEN ffca.action_type = 'S'
            THEN
               ffca.menu_argument_short
         END
            action_value
    FROM applsys.fnd_form_custom_rules ffcr
       , applsys.fnd_form_custom_actions ffca
       , applsys.fnd_form_custom_prop_list ffcpl
       , applsys.fnd_application_tl fat
       , applsys.fnd_form ff
       , applsys.fnd_form_tl fft
       , applsys.fnd_user fu
   WHERE ffcr.id = ffca.rule_id
     AND ffcr.form_name = ff.form_name
     AND ff.form_id = fft.form_id
     AND ff.application_id = fat.application_id
     AND ffca.property_name = ffcpl.property_id(+)
     AND ffca.object_type = ffcpl.field_type(+)
     AND ffcr.created_by = fu.user_id
     AND ff.form_name = 'INVTTMTX'
--     AND ffcr.creation_date > '02-JAN-2013'
ORDER BY ffcr.function_name
       , ffcr.form_name
       , ffcr.sequence;

PERSONALIZATION CONTEXT/SCOPE

  SELECT ffcr.function_name
       , ffcr.form_name
       , fft.user_form_name
       , fat.application_name application
       , ffcr.sequence seq
       , ffcr.description
       , CASE
            WHEN ffcs.level_id = 10 THEN 'Industry'
            WHEN ffcs.level_id = 30 THEN 'Responsibility'
            WHEN ffcs.level_id = 40 THEN 'User'
            ELSE 'Site'
         END
            set_against
       , CASE
            WHEN ffcs.level_id = 10 -- industry
            THEN
               (SELECT industry_name
                  FROM applsys.fnd_industries fi
                 WHERE fi.industry_id = ffcs.level_value)
            WHEN ffcs.level_id = 30 -- responsibility
            THEN
               (SELECT responsibility_name
                  FROM applsys.fnd_responsibility_tl frt
                 WHERE frt.responsibility_id = ffcs.level_value)
            WHEN ffcs.level_id = 40 -- user
            THEN
               (SELECT fu.user_name || '(' || fu.description || ')'
                  FROM applsys.fnd_user fu
                 WHERE fu.user_id = ffcs.level_value)
         END
            set_against_value
       , ffcs.creation_date
       , scope_by.description scope_created_by
    FROM applsys.fnd_form_custom_rules ffcr
       , applsys.fnd_form_custom_scopes ffcs
       , applsys.fnd_user scope_by
       , applsys.fnd_application_tl fat
       , applsys.fnd_form ff
       , applsys.fnd_form_tl fft
   WHERE ffcr.id = ffcs.rule_id(+)
     AND ffcr.form_name = ff.form_name
     AND ff.form_id = fft.form_id
     AND ff.application_id = fat.application_id
     AND ffcs.created_by = scope_by.user_id
     AND ff.form_name = 'PAXTRAPE'
--     AND ffcr.creation_date > '01-JAN-2013'
ORDER BY ffcr.function_name
       , ffcr.form_name
       , ffcr.sequence;