Oracle HR related API List

Recently I had to work on lot of HR related interfaces, which use Oracle APIs. While I was working on the interfaces, I came across with the following website by Puneet Rajkumar, which I thought a very good resource for all HRMS related APIs. He provided some easy good examples.

https://blogs.oracle.com/prajkumar/entry/oracle_hrms_apis


You can also refer to Oracle article for more information:
Note: 179243.1 - Application Programmatic Interfaces (APIs) in Oracle HRMS


Or, you can run the following query:

SELECT SUBSTR (ds.owner, 1, 20)   owner,

       SUBSTR (ds.name, 1, 30)    object_name,

       SUBSTR (ds.type, 1, 20)    object_type,

       SUBSTR (do.status, 1, 10)  status,

       do.last_ddl_time           last_ddl

  FROM dba_source  ds,

       dba_objects do

 WHERE 1 = 1

   --

   AND ds.name = do.object_name

   AND ds.type = do.object_type

   --

   AND ds.text LIKE '%Header%'

   AND ds.type = 'PACKAGE BODY'

   AND ds.name LIKE 'HR_%API%'

   --

 ORDER BY ds.owner, ds.name;

Active Responsibility List with Active User Count

If you need to create an ad-hoc report showing all the currently active Oracle responsibilities with all active users that are using them, below is a simple query that can help you.

SELECT

       fat.application_name         "Application Name",

       frv.responsibility_name      "Active Responsibility Name",

       COUNT(fu.user_name)          "Active User Count"

  FROM

       fnd_user                     fu,

       fnd_user_resp_groups_direct  furgd,

       fnd_responsibility_vl        frv,

       fnd_application_tl           fat

 WHERE

       1=1

   --

   AND furgd.end_date IS NULL

   --

   AND TRUNC(SYSDATE) BETWEEN

          TRUNC(furgd.start_date) AND TRUNC(NVL(furgd.end_date, SYSDATE+1))

   AND TRUNC(SYSDATE) BETWEEN

          TRUNC(frv.start_date)   AND TRUNC(NVL(frv.end_date, SYSDATE+1))

   AND TRUNC(SYSDATE) BETWEEN

          TRUNC(fu.start_date)    AND TRUNC(NVL(fu.end_date, SYSDATE+1))

   --

   AND fat.application_id       =  frv.application_id

   --

   AND furgd.responsibility_id  =  frv.responsibility_id

   AND furgd.user_id            =  fu.user_id

   --

 GROUP BY fat.application_name, frv.responsibility_name

 ORDER BY fat.application_name, frv.responsibility_name;

Delete Concurrent Program from the Back-End

If you create an Executable without creating a concurrent program, the system will allow to delete the Executable. But once you create the Concurrent Program for that Executable, the system never allows you to delete the program -- it only gives the option to disable the Concurrent Program.

At that point, your only option is to delete the Concurrent Program and its Executable from the back-end. Following is a simple straight-forward query that you can use for deleting a Concurrent Program. This query first checks if the concurrent program and its executable exist in the system. If found, it will delete the program; if not found, it will just display a message.

In this example, 'XX_TEST' is my Concurrent Program's Short Name and 'XX' is the Application Short Name. You will have to use appropriate program name and application short name according to your need.

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

-- delete concurrent program definition and executable from back-end

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

-- syntax:

--     delete_program    (program_short_name, application_short_name)

--     delete_executable (program_short_name, application_short_name)

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

DECLARE

  lv_prog_short_name    VARCHAR2(240);

  lv_appl_short_name    VARCHAR2(240);

BEGIN

   -- set the variables first

   lv_prog_short_name := 'XX_TEST';     -- concurrent program short name

   lv_appl_short_name := 'XX';          -- application short name

  

   -- see if the program exists. if found, delete the program

   IF fnd_program.program_exists    (lv_prog_short_name, lv_appl_short_name) AND

      fnd_program.executable_exists (lv_prog_short_name, lv_appl_short_name)    

   THEN

     

      fnd_program.delete_program(lv_prog_short_name, lv_appl_short_name);

      fnd_program.delete_executable(lv_prog_short_name, lv_appl_short_name);

     

      COMMIT;

  

      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' deleted successfully');

  

   -- if the program does not exist in the system

   ELSE

      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' not found');

   END IF;

  

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);

  

END;

Query to find the first word from a string

Recently I had a challenge to extract the first word from a string field. For example, I had to extract "Toshiba" from the string field, "Toshiba America Medical Systems" or "California" from the string "California", and so on.

Although, I initially created a CASE-WHEN statement for my query, using SUBSTR and INSTR functionality - which in fact worked just fine, but I kept thinking there must be a better way to handle this.

Sure enough, with a little bit of Google search, I stumbled upon the following link, where the username "jeneesh" showed an example using REGEXP_REPLACE expression to retrieve the same information -- which seemed better one line solution in my opinion:

https://forums.oracle.com/thread/2543764?start=0&tstart=0

So, I just wanted to share the query in case it helps in future.

WITH qry

AS

(

   SELECT NULL field_name FROM dual

      UNION ALL

   SELECT 'Aopu' FROM dual

      UNION ALL

   SELECT 'Abul Mohsin' FROM dual

      UNION ALL

   SELECT 'Abul M Mohsin' FROM dual

      UNION ALL

   SELECT 'Abul M. Mohsin' FROM dual

      UNION ALL

   SELECT 'Princess Cruise Lines, Ltd' FROM dual

      UNION ALL

   SELECT 'Princess Cruise Lines, Ltd.' FROM dual

      UNION ALL

   SELECT 'Princess Cruise Lines Ltd' FROM dual

)

SELECT field_name,

       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\1')  exp1,

       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\2')  exp2,

       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\3')  exp3

  FROM qry;

Web-ADI Template Settings for Microsoft Excel

Often times, WebADI can be very confusing to work with. There are just too many issues if it is not properly set up. Sometimes you may encounter VBProject Runtime Error when trying to open Web-ADI template file, or sometimes the Excel spreadsheet just hangs up, or can't be opened.

There are few steps you will have to take before WebADI can be properly used:

• Microsoft Office Version
• Microsoft Excel Settings
• Internet Explorer Settings

Follow the steps below given in the screenshots and apply the settings exactly as they are depicted. The screenshots are from Excel 2010, however the settings should also work for 2000, 2003, 2007 versions.

A)  Check the Microsoft Office Version

Make sure you are using 32-bit version of Microsoft Office. Click on Excel --> File --> Help. If you are running 64-bit version, you may want to uninstall 64-bit and re-install the 32-bit version of Microsoft Office.

B)  Open Microsoft Excel --> File --> Options --> Trust Center --> Trust Center Settings

B-1)  Trusted Documents:

Make sure to check "Allow documents on a network to be trusted".

B-2)  Add-Ins:

Make sure to uncheck all the options.

B-3)  ActiveX Settings:

Check only "Enable all controls without restrictions and without prompting". All other options should be unchecked.

B-4)  Macro Settings:

Check "Enable all macros" and "Trust access to the VBA project object model" options.

B-5)  Protected View:

Check only "Enable Protected View for Outlook attachments" and "Enable Data Execution Prevention mode" options. All other options should be unchecked.

In case the WebADI still does not work, then keep all the options unchecked, and retry.

B-6)  Message Bar:

Check "Show the Message Bar in all applications when achieve content, such as ActiveX controls and macros, has been blocked".

B-7)  External Content:

Check the "Enable all Data Connections" and "Enable automatic update for all Workbook Links" options only.

C)  Internet Explorer Settings:

Open Internet Explorer --> Tools --> Internet Options --> Security Tab --> Custom Level.

C-1)  Downloads

Scroll down to Downloads section, and make sure File Download is Enabled.

C-2)  Miscellaneous: 

Scroll down to Miscellaneous section, and make sure to match the following setting.

C-3)  Scripting: 

Scroll down to Scripting section, and make sure to match the following setting. Then press OK.

Now log out from your Oracle Applications and re-log back in. Retry the Web-ADI upload.

References:
Web ADI Errors & Solutions
R12 Web ADI: Make it work with MS Office 2010
Troubleshooting user issues with Oracle Web ADI