How to Force-Logout Idle or Stuck Oracle EBS Users With One PL/SQL Package (Free Script Inside)

If you've ever administered Oracle E-Business Suite, you know the pain: a user's session freezes, they lock a record, they leave for the day without logging out, or a "ghost" ICX session just sits there blocking data. Killing it manually through System Administrator > Monitor > Users works — until you need to do it for dozens of users at once, or automate it as part of a nightly job.

This post walks through a lightweight, production-ready PL/SQL package — XXBFARM_SESSION_LOGOUT_PKG — that force-disables active EBS sessions for one user (or all users) in a single concurrent program run, with full logging built in.

What the Script Actually Does

At its core, the package does three things:

1. Finds active sessions in ICX_SESSIONS that connected within the last 30 minutes and aren't already disabled.
2. Logs full session context — user name, responsibility, application, form, and last connect time — to the concurrent request log via FND_FILE.PUT_LINE.
3. Disables each session by flipping disabled_flag to 'Y', which is the same mechanism EBS itself uses when a session is terminated.

It's designed to run as a standard Concurrent Program, so you can schedule it, run it on demand, or trigger it from a workflow.

The Package Spec

CREATE OR REPLACE PACKAGE XXBFARM_SESSION_LOGOUT_PKG AS
  PROCEDURE logout_active_sessions
  (
    errbuf      OUT VARCHAR2,
    retcode     OUT VARCHAR2,
    p_user_name IN  VARCHAR2
  );
END XXBFARM_SESSION_LOGOUT_PKG;
/

Note the standard errbuf / retcode OUT parameters — this is what makes the procedure directly registerable as a Concurrent Program executable in EBS without any wrapper needed.

The Package Body

CREATE OR REPLACE PACKAGE BODY XXBFARM_SESSION_LOGOUT_PKG AS
  PROCEDURE logout_active_sessions
  (
    errbuf      OUT VARCHAR2,
    retcode     OUT VARCHAR2,
    p_user_name IN  VARCHAR2
  )
  IS
    l_updated_count NUMBER := 0;

    CURSOR c_sessions IS
      SELECT fu.user_id,
             fu.user_name,
             fu.description AS full_name,
             TO_CHAR(icxs.last_connect, 'DD-MON-YYYY HH24:MI:SS') AS last_connect,
             icxs.session_id,
             icxs.responsibility_application_id,
             fat.application_name,
             icxs.responsibility_id,
             frt.responsibility_name,
             icxs.function_id,
             fft.user_function_name AS form_name
      FROM icx_sessions icxs,
           fnd_user fu,
           fnd_application_tl fat,
           fnd_responsibility_tl frt,
           fnd_form_functions_tl fft
      WHERE icxs.user_id = fu.user_id
        AND icxs.responsibility_application_id = fat.application_id(+)
        AND icxs.responsibility_id = frt.responsibility_id(+)
        AND icxs.responsibility_application_id = frt.application_id(+)
        AND icxs.function_id = fft.function_id(+)
        AND NVL(icxs.disabled_flag, 'N') = 'N'
        AND icxs.last_connect > SYSDATE - (30 / 1440)
        AND fu.user_name = NVL(p_user_name, fu.user_name);
  BEGIN
    FND_FILE.PUT_LINE(FND_FILE.LOG, '==============================================');
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'EBS Active Session Logout Program Started');
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Time          : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameter User Name : ' || NVL(p_user_name, 'ALL'));
    FND_FILE.PUT_LINE(FND_FILE.LOG, '==============================================');

    FOR r IN c_sessions LOOP
      FND_FILE.PUT_LINE(FND_FILE.LOG, '----------------------------------------------');
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'User ID        : ' || r.user_id);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'User Name      : ' || r.user_name);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Full Name      : ' || r.full_name);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Session ID     : ' || r.session_id);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Last Connect   : ' || r.last_connect);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Application    : ' || r.application_name);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Responsibility : ' || r.responsibility_name);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Form Name      : ' || r.form_name);

      UPDATE icx_sessions
      SET disabled_flag = 'Y'
      WHERE session_id = r.session_id
        AND NVL(disabled_flag, 'N') = 'N';

      IF SQL%ROWCOUNT > 0 THEN
        l_updated_count := l_updated_count + SQL%ROWCOUNT;
        FND_FILE.PUT_LINE(FND_FILE.LOG, 'Status         : LOGOUT UPDATED');
      ELSE
        FND_FILE.PUT_LINE(FND_FILE.LOG, 'Status         : NOT UPDATED / ALREADY DISABLED');
      END IF;
    END LOOP;

    COMMIT;

    FND_FILE.PUT_LINE(FND_FILE.LOG, '==============================================');
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Sessions Logged Out : ' || l_updated_count);
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'End Time : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'EBS Active Session Logout Program Completed');
    FND_FILE.PUT_LINE(FND_FILE.LOG, '==============================================');

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      retcode := '2';
      errbuf  := SQLERRM;
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR OCCURRED');
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Message : ' || SQLERRM);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Program rolled back.');
  END logout_active_sessions;
END XXBFARM_SESSION_LOGOUT_PKG;
/

Registering It as a Concurrent Program

To use this from the EBS front end (not just SQL*Plus), register it like any custom PL/SQL concurrent program:

1. Define the Executable System Administrator > Concurrent > Program > Executable

   • Execution Method: PL/SQL Stored Procedure
   • Execution File Name: XXBFARM_SESSION_LOGOUT_PKG.logout_active_sessions

2. Define the Concurrent Program System Administrator > Concurrent > Program > Define

   • Attach the executable above
   • Add one parameter: P_USER_NAME (optional, VARCHAR2) — leave blank to log out all active users, or supply a specific user name to target just one.

3. Add to a Request Group so it's runnable from your responsibility of choice.

4. Run it from Submit a New Request, check the log for a full audit trail of who got logged out and when.

Why This Is Handy

• Targeted or bulk: pass a user name to force-logout one person, or leave the parameter null to sweep every active session.
• Full audit trail: every disabled session is logged with user, responsibility, form, and timestamp — useful for security reviews or troubleshooting "why was I logged out" tickets.
• Safe by design: only touches sessions connected in the last 30 minutes and not already disabled, so it won't churn through your entire session history.
• Standard EBS pattern: uses errbuf/retcode and FND_FILE, so it behaves exactly like any other concurrent program your ops team already knows how to run and monitor.

A Word of Caution

Before running this in production:

• Test in a non-prod instance first. Force-disabling a session mid-transaction can interrupt unsaved work.
• Restrict access to this concurrent program to admin-level responsibilities only — this is a session-management tool, not something to expose broadly.
• Consider notifying users if you're running this as a scheduled sweep, so people aren't surprised by sudden logouts

How to Call a Seeded Oracle Form from a Custom Form (Step-by-Step Guide)

 How to Call a Seeded Oracle Form from a Custom Form (Step-by-Step Guide)

Introduction
In Oracle E-Business Suite, it is a common requirement to call a seeded (standard) Oracle form from a custom-developed form and pass values automatically.

This blog explains how to:

• Call a seeded form using FND_FUNCTION.EXECUTE
• Pass values using Global Variables
• Use Form Personalization to auto-populate fields

Business Requirement
From a custom form, users want to:

• Click a button (e.g., “Call Release Sales Order Form”)
• Open the standard Release Sales Order form
• Automatically pass:
• Order Number
• Picking Rule

Step 1: Create Button in Custom Form

In your custom form, create a button like:
CALL_FORM

Add a trigger:
👉 WHEN-BUTTON-PRESSED

Step 2: Write Code in Trigger

BEGIN

    -- Assign values to Global Variables

   :GLOBAL.G_ORDER_NO    := :BLOCK.ORDER_NUMBER;

   :GLOBAL.G_PICKING_RULE := :BLOCK.PICKING_RULE;

    -- Call Seeded Form

   FND_FUNCTION.EXECUTE

   (

      FUNCTION_NAME => 'WSH_WSHFRREL',

      OPEN_FLAG     => 'Y',

      SESSION_FLAG  => 'Y'

   );

 END;

Step 3: What Happens Here?

• Values from custom form fields are stored in Global Variables
• Seeded form is opened using FND_FUNCTION.EXECUTE
• Global variables act as a bridge between forms

 

Step 4: Form Personalization in Seeded Form

Navigate to:

Help → Diagnostics → Custom Code → Personalize

In function: WSH_WSHFRREL

Add actions:

 

Action 1: Set Picking Rule

• Object Type: Item
• Target Object: RELEASE.PICKING_RULE
• Property Name: VALUE
• Value::GLOBAL.G_PICKING_RULE

 

Action 2: Set Order Number

• Object Type: Item
• Target Object: RELEASE.ORDER_NUMBER
• Property Name: VALUE
• Value::GLOBAL.G_ORDER_NO

 

Step 5: Result

When the user clicks the button:

• Seeded form opens automatically
• Order Number and Picking Rule are auto-filled
• No manual input required

 

Advantages

• Saves user time
• Reduces manual errors
• Seamless integration between custom and standard forms

 

Best Practices

• Always clear global variables if not required later
• Use meaningful variable names
• Add validations before calling seeded form

 

Conclusion
Using FND_FUNCTION.EXECUTE along with Global Variables and Form Personalization is a powerful way to integrate custom forms with standard Oracle forms. This approach improves usability and ensures smooth data flow across modules.

Delete Organization Classification and Organization Using API when BG - Business Group Setup

 

Oracle HRMS – Delete Organization Classification and Organization Using API

📘 Overview

In Oracle HRMS, each organization is classified and stored in the HR_ALL_ORGANIZATION_UNITS and HR_ORGANIZATION_INFORMATION tables.
At times, system administrators or technical consultants may need to remove obsolete or test organization structures.

Oracle provides public APIs to safely perform these deletions:

• HR_ORGANIZATION_API.DISABLE_ORG_CLASSIFICATION – disables and removes organization classifications.

• HR_ORGANIZATION_API.DELETE_ORGANIZATION – deletes the organization record itself.

The following scripts demonstrate how to use these APIs effectively with proper exception handling and output messages.

---

⚙️ Script 1: Delete Organization Classification

/*===========================================================
 Script Name  : Delete_Organization_Classification.sql
 Purpose      : To disable and remove organization classifications 
                from HR_ORGANIZATION_INFORMATION table.
 Author       : Danish Halim
 Created Date : 05-Nov-2025
===========================================================*/

DECLARE
   CURSOR fetch_details
   IS
      SELECT hoi.*
        FROM hr_all_organization_units haou,
             hr_organization_information hoi
       WHERE haou.organization_id = hoi.organization_id
         AND hoi.organization_id = 321;  -- Organization ID to delete classification for
BEGIN
   FOR i IN fetch_details LOOP
      BEGIN
         hr_organization_api.disable_org_classification (
            p_validate              => FALSE,
            p_effective_date        => SYSDATE,
            p_org_information_id    => i.org_information_id,
            p_org_info_type_code    => i.org_information_context,
            p_object_version_number => i.object_version_number);

         COMMIT;

         DBMS_OUTPUT.put_line (
            i.org_information_id || ' - Classification has been deleted successfully!'
         );

      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.put_line (
               'Failure: ' || i.org_information_id ||
               ' could not be deleted. Error: ' || SQLERRM
            );
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
/

🔍 Explanation

• Purpose: Removes all active classifications for a specific organization (e.g., 321).

• Key Tables Used:

  • HR_ALL_ORGANIZATION_UNITS – master table for organizations.

  • HR_ORGANIZATION_INFORMATION – stores classification and context details.

• API Used:

  • DISABLE_ORG_CLASSIFICATION removes assigned classifications safely using API logic.

• Parameters:

  • p_validate: If set to TRUE, only validates. FALSE actually deletes.

  • p_effective_date: Date of deletion.

  • p_org_information_id: Primary key for classification row.

  • p_org_info_type_code: Type of classification (HR, Payroll, etc.).

  • p_object_version_number: Ensures record version control.

---

⚙️ Script 2: Delete Organization

/*===========================================================
 Script Name  : Delete_Organization.sql
 Purpose      : To delete an organization record completely 
                from HR_ALL_ORGANIZATION_UNITS table.
 Author       : Danish Halim
 Created Date : 05-Nov-2025
===========================================================*/

DECLARE
   CURSOR c1
   IS
      SELECT *
        FROM hr_all_organization_units
       WHERE organization_id = 226;   -- Organization ID to delete
BEGIN
   FOR i IN c1 LOOP
      BEGIN
         hr_organization_api.delete_organization (
            p_validate              => FALSE,
            p_organization_id       => i.organization_id,
            p_object_version_number => i.object_version_number
         );

         DBMS_OUTPUT.put_line ('Organization has been deleted successfully!');

      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
      END;
   END LOOP;

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
/

🔍 Explanation

• Purpose: Deletes the organization itself from the system after all classifications are removed.

• Key Table Used:

  • HR_ALL_ORGANIZATION_UNITS – main organization storage table.

• API Used:

  • DELETE_ORGANIZATION safely deletes an organization along with related information.

• Best Practice:
  Always disable or delete all classifications before running this script to prevent referential constraint errors.

---

✅ Execution Steps

1. Login to Oracle database as APPS or an authorized schema user.

2. Enable DBMS Output in SQL Developer or TOAD.

3. Run the first script (Delete_Organization_Classification.sql) to disable all classifications for the target organization ID.

4. Run the second script (Delete_Organization.sql) to delete the organization itself.

5. Verify Results:

   • Query HR_ALL_ORGANIZATION_UNITS to ensure the record is removed.

   • Check DBMS_OUTPUT messages for confirmation or failure logs.

---

⚠️ Precautions

• Always take a backup before performing deletions.

• Perform deletions in a non-production environment first to validate dependencies.

• Use correct organization_id values — deletion is irreversible.

• If organizations are linked to other HR or Payroll data, you may need to remove dependent relationships first.

---

🧩 References

• Oracle HRMS API Reference Guide

• Table: HR_ALL_ORGANIZATION_UNITS

• Table: HR_ORGANIZATION_INFORMATION

• Package: HR_ORGANIZATION_API

How to make responsibilities as read only in oracle apps EBS R12 Using Custom.Pll Or Creating a read only Responsibility in oracle apps EBS R12 Using Custom.Pll

 

How to make responsibilities as read only in oracle apps EBS R12 Using Custom.Pll

Or Creating a read only Responsibility in oracle apps EBS R12 Using Custom.Pll

 

·  Navigate to the AU_TOP/resource directory on the application server:

cd $AU_TOP/resource

·  Open the CUSTOM.pll file using  Forms Builder.

 

 

--

  -- Real code starts here

  --

   formname    VARCHAR2 (30)  := NAME_IN ('system.current_form');

   blockname   VARCHAR2 (30)  := NAME_IN ('system.cursor_block');

   itemname    VARCHAR2 (240) := NAME_IN ('system.cursor_item');

BEGIN

     --    FND_MESSAGE.SET_STRING('USER_NAME = ' ||FND_PROFILE.VALUE('USER_NAME'));

     --    FND_MESSAGE.SHOW;

     --    FND_MESSAGE.SET_STRING('USER_ID = ' ||FND_GLOBAL.USER_ID);

     --    FND_MESSAGE.SHOW;

 

   IF event_name = 'WHEN-NEW-FORM-INSTANCE'

   THEN

      IF fnd_global.user_id IN ('1161', '1191')

      THEN                                                         --'1251'--

         --FND_MESSAGE.SET_STRING('USER_ID = ' ||FND_GLOBAL.USER_ID);

         --FND_MESSAGE.SHOW;

         BEGIN

            COPY ('Entering app_form.query_only_mode.', 'global.frd_debug');

            COPY ('YES', 'PARAMETER.QUERY_ONLY');

            app_menu2.set_prop ('FILE.SAVE', enabled, property_off);

            app_menu2.set_prop ('FILE.ACCEPT', enabled, property_off);

            formname := NAME_IN ('system.current_form');

            blockname := GET_FORM_PROPERTY (formname, first_block);

 

            IF formname <> 'FNDRSRUN'

            THEN

               -- This is added for ignor the Submit a New Request Form ---

 

               --FND_MESSAGE.SET_STRING('formname = ' ||formname);

               --FND_MESSAGE.SHOW;

               WHILE (blockname IS NOT NULL)

               LOOP

                  IF (GET_BLOCK_PROPERTY (blockname, base_table) IS NOT NULL )

                  THEN

                     SET_BLOCK_PROPERTY (blockname,insert_allowed,property_false);

                     SET_BLOCK_PROPERTY (blockname,update_allowed,property_false);

                     SET_BLOCK_PROPERTY (blockname,delete_allowed,property_false);

                  END IF;

 

                  blockname := GET_BLOCK_PROPERTY (blockname, nextblock);

               END LOOP;

            END IF;

         END;

      END IF;

   END IF;

END event;

Before putting the Custom.pll file into the Server Please take a Backup First. The both Files CUSTOM.pll and CUSTOM.plx for Safe.Then Move the CUSTOM.pll into the server and then Compile the CUSTOM.pll script into the Server using Putty command.

frmcmp_batch module=CUSTOM.pll userid=apps/apps output_file=$AU_TOP/resource/CUSTOM.plx compile_all=special module_type=LIBRARY

 

After compile the command in Putty. Please closed ALL the tabs of the Browsers and then closed the Browsers so that use can see the effects of the custom.pll Files.

 

Then Login to the Ebs using the Example User Name : 92526 That’s User ID Is (1251).

That make all responsibilities as read only using CUSTOM.pll

One of my Colleague requirement is Please open the ‘Submit a New Request’ Form so that User can Run the Reports. SO that I have added an command in Form that is given below.