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