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.