Delete a Descriptive Flexfield Context Value

How often that you created a DFF context value, and then you were told that this context value required extensive changes? Oracle does not allow us to delete a DFF Context Value since it could be used by the application. I agree that if it is used, then we should not allow to delete it.  How about if it is not even been used? We are not able to rename the Context Value to something else so that this record can be reused meaningfully. It also causes a problem that the Attribute columns are occupied by this Context Value and cannot be assigned to other Context Value. What a waste. So here is a script to delete a particular DFF context value.

Warning: DO NOT use this script for the DFF Context Values used for SIT / EIT in Human Resource module since it does not take care the security side of it.

If you issue an commit, make sure you go back to the DFF Form, unfreeze/freeze the definition and compile the DFF again.

set serveroutput on
declare

-- Copy these values for your DDF Context Value
  var_AppName  VARCHAR2(100) := 'Order Management';
  var_dffTitle VARCHAR2(200) := 'Additional Line Attribute Information';
  var_dffCode  VARCHAR2(50)  := 'XXXXX';
  var_lang     VARCHAR2(5)   := 'US';
  
  num_appID      NUMBER;
  var_dffName    VARCHAR2(100);
  num_rowDeleted NUMBER;

begin
for rs in (
  select b.application_id
       , a.descriptive_flexfield_name
    from FND_DESCRIPTIVE_FLEXS_TL a
       , FND_APPLICATION_TL b
   where a.language=var_lang
     and b.language=var_lang
     and b.application_id   = a.application_id
     and b.application_name = var_AppName
     and a.title            = var_dffTitle
     ) loop

  num_appID := rs.application_id;
  var_dffName := rs.descriptive_flexfield_name;

end loop;

if num_appID is null or var_dffName is null then
  dbms_output.put_line('DFF not found');
  return;
end if;

delete from FND_DESCR_FLEX_COLUMN_USAGES
 where descriptive_flex_context_code = var_dffCode
  and descriptive_flexfield_name     = var_dffName
  and application_id                 = num_appID;

num_rowDeleted := SQL%ROWCOUNT;
dbms_output.put_line('No. of row delete in FND_DESCR_FLEX_COLUMN_USAGES : ' || num_rowDeleted);

delete from FND_DESCR_FLEX_COL_USAGE_TL
 where descriptive_flex_context_code = var_dffCode
  and descriptive_flexfield_name     = var_dffName
  and application_id                 = num_appID;

num_rowDeleted := SQL%ROWCOUNT;
dbms_output.put_line('No. of row delete in FND_DESCR_FLEX_COL_USAGE_TL  : ' || num_rowDeleted);

delete from FND_DESCR_FLEX_CONTEXTS
 where descriptive_flex_context_code = var_dffCode
  and descriptive_flexfield_name     = var_dffName
  and application_id                 = num_appID;

num_rowDeleted := SQL%ROWCOUNT;
dbms_output.put_line('No. of row delete in FND_DESCR_FLEX_CONTEXTS      : ' || num_rowDeleted);

delete from FND_DESCR_FLEX_CONTEXTS_TL
 where descriptive_flex_context_code = var_dffCode
  and descriptive_flexfield_name     = var_dffName
  and application_id                 = num_appID;

num_rowDeleted := SQL%ROWCOUNT;
dbms_output.put_line('No. of row delete in FND_DESCR_FLEX_CONTEXTS_TL   : ' || num_rowDeleted);

dbms_output.put_line('Issue a COMMIT to confirm or ROLLBACK to revert');
end;
/

How to Delete XML Publisher Definition and Template

How often do you create a XML publisher definition with a wrong Codes (Template or Data Definition)? Or you want to change the Code so that it is more meaningful?

In the XML Publisher's OA Framework pages, both Template and Data Definition pages do not provide an option to delete anything. Moreover, the Template Code or Definition Code is not allowed to be updated.

The reason is that: concurrent program with XML output matches the Short Name with the template Code to find out which XML Publisher template to use for post processing. If you delete this template, the Post Processor cannot find the template, and then give errors.

You cannot change the Concurrent Program Short Name in the Form, and you cannot change the XML Template Code, and you cannot change the Data definition Code. If you make a typo in any one, disable it and create another one with the correct name. That's what Oracle suggests.

Come on...I WANT TO DELETE THEM, rather than recreating everything, and leave the wrong stuff in the system.

In another blog I show the way to delete concurrent program, and in here I will show you how to delete XML publisher template and the definition associated with this template. Change the parameters to fit your needs.

SET SERVEROUTPUT ON

DECLARE
   -- Change the following two parameters
   var_templateCode    VARCHAR2 (100) := 'SYMPLIK-TEST2';     -- Template Code
   boo_deleteDataDef   BOOLEAN := TRUE;     -- delete the associated Data Def.
BEGIN
   FOR RS
      IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,
                 T1.DATA_SOURCE_CODE,
                 T2.APPLICATION_SHORT_NAME DEF_APP_NAME
            FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
           WHERE T1.TEMPLATE_CODE = var_templateCode
                 AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)
   LOOP
      XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

      DELETE FROM XDO_LOBS
            WHERE     LOB_CODE = var_templateCode
                  AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
                  AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

      DELETE FROM XDO_CONFIG_VALUES
            WHERE     APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
                  AND TEMPLATE_CODE = var_templateCode
                  AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
                  AND CONFIG_LEVEL = 50;

      DBMS_OUTPUT.PUT_LINE ('Template ' || var_templateCode || ' deleted.');

      IF boo_deleteDataDef
      THEN
         XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,
                                            RS.DATA_SOURCE_CODE);

         DELETE FROM XDO_LOBS
               WHERE LOB_CODE = RS.DATA_SOURCE_CODE
                     AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
                     AND LOB_TYPE IN
                            ('XML_SCHEMA',
                             'DATA_TEMPLATE',
                             'XML_SAMPLE',
                             'BURSTING_FILE');

         DELETE FROM XDO_CONFIG_VALUES
               WHERE     APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
                     AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
                     AND CONFIG_LEVEL = 30;

         DBMS_OUTPUT.PUT_LINE (
            'Data Defintion ' || RS.DATA_SOURCE_CODE || ' deleted.');
      END IF;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE (
      'Issue a COMMIT to make the changes or ROLLBACK to revert.');
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (
         'Unable to delete XML Publisher Template ' || var_templateCode);
      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));
END;
/

How to Delete Responsibility From User

System Administrator assigned a wrong responsibility to a user. To "remove" this responsibility from that user, the only way, as provided by Oracle, is to end-date this responsibility from this user.

The reason behind of not allowing to delete responsibility from user is that: if a user owns a particular responsibility, this user can do certain business transactions as this responsibility provided, and leave a trail in the tables LAST_UPDATED_BY column for the data this user altered. For auditing purpose (and other finger-pointing excuses), we need to know who, when, how this transaction is created, and more importantly what is business purpose and logic behind of this transaction. So we should keep the responsibility record in order to explain why this user is able to do such business transactions.

Back to System Administrator that he assigned a wrong responsibility to a user, and he found it out once he pressed Ctrl-S. He wants to remove this record...but there has no way to do it in the GUI (Users Form) or OA framework pages (User Management).

In terms of API, there has a FND_USER_PKG.DELREP exists but this stored procedure will end-date the responsibility, instead of deleting this responsibility from user.

In here I present a way to remove this incorrect responsibility from database. I incorporate this method with Form Personalization so that System Administrator can easily remove any responsibility from user without opening a SQL*Plus prompt.

Part 1 - Create Stored Procedure

CREATE OR REPLACE PROCEDURE APPS.XX_DELETE_RESP_FROM_USER (p_userID IN NUMBER, p_respID IN NUMBER, p_commit IN BOOLEAN) IS
BEGIN
FOR RS IN (
SELECT t2.USER_NAME,
t3.RESPONSIBILITY_KEY
FROM WF_LOCAL_USER_ROLES t1
, FND_USER            t2
, FND_RESPONSIBILITY  t3
WHERE t1.ROLE_NAME LIKE 'FND_RESP|%' || t3.RESPONSIBILITY_KEY || '|%'
AND t1.USER_NAME         = t2.USER_NAME
AND t2.USER_ID           = p_userID
AND t3.RESPONSIBILITY_ID = p_respID
) LOOP
DELETE FROM WF_LOCAL_USER_ROLES
WHERE USER_NAME=RS.USER_NAME
AND (ROLE_NAME LIKE 'FND_RESP%' || RS.RESPONSIBILITY_KEY || '%'
OR ROLE_NAME LIKE 'FND_RESP%' || p_respID || '%')
AND ROLE_ORIG_SYSTEM LIKE 'FND_RESP%'
AND ROLE_ORIG_SYSTEM_ID=p_respID;

DELETE FROM WF_USER_ROLE_ASSIGNMENTS
WHERE USER_NAME=RS.USER_NAME
AND (ROLE_NAME LIKE 'FND_RESP%' || RS.RESPONSIBILITY_KEY || '%'
OR ROLE_NAME LIKE 'FND_RESP%' || p_respID || '%')
AND ROLE_ORIG_SYSTEM LIKE 'FND_RESP%'
AND ROLE_ORIG_SYSTEM_ID=p_respID;

END LOOP;
IF p_commit = TRUE THEN
COMMIT;
END IF;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, 'Unable to delete this responsiiblity from user.');
END;
/

Part 2 - Create Form Personalization
You can load this personalization file by FNDLOADER, or create these 2 rules manually as shown below.

Rule: Add Menu Item when a Responsibility is selected

Rule: Invoke stored procedure when menu item is selected

Part 3 - Testing

Query a user, select a responsibility and select "Delete Selected Responsibility" from Menu

Click "OK" to confirm the deletion, or "Cancel" to leave.

How to Delete Responsibility

In what situation we need to delete a responsibility?

We can disable a responsibility, end-date the users' responsibility, change the responsibility name with the words "do not use", nullify the menu attached to this responsibility, or whatever means to make a responsibility obsolete.

However, we cannot change the Responsibility Key for that incorrect responsibility.
And more importantly, I have to reuse this responsibility key for the correct responsibility. Since the we have no way to change the Responsibility Key, the alternative is to get rid of this incorrect value from the system.

Using the following script you will able to delete a responsibility from the system, using the API FND_RESPONSIBILITY_PKG.DELETE_ROW. This script will abort if such responsibility is used by any FND user or referenced in other places (e.g. HR SIT & EIT Security).

This script DOES NOT remove a responsibility from a user. Please read my next blog for this kind of removal.

SET SERVEROUTPUT ON

DECLARE
  -- Change these 2 variables to fit your needs
  p_respKey      VARCHAR2 (100) := [RESP. KEY TO BE DELETED];
  p_ForceDelete  BOOLEAN        := [TRUE|FALSE];  
 
  num_respID     NUMBER;
  num_appID      NUMBER;
  var_appName    VARCHAR2 (100);
  var_sqlStmt    VARCHAR2 (1000);
  var_respName   VARCHAR2 (100);
  num_count      NUMBER;
  boo_proceed    BOOLEAN := TRUE;
BEGIN
  SELECT A.RESPONSIBILITY_NAME
       , B.RESPONSIBILITY_ID
       , B.APPLICATION_ID
       , C.APPLICATION_NAME
    INTO var_respName
       , num_respID
       , num_appID
       , var_appName
    FROM FND_RESPONSIBILITY_TL A
       , FND_RESPONSIBILITY B
       , FND_APPLICATION_TL C
   WHERE A.RESPONSIBILITY_ID = B.RESPONSIBILITY_ID
     AND A.LANGUAGE = 'US'
     AND B.RESPONSIBILITY_KEY = p_respKey
     AND C.LANGUAGE = 'US'
     AND C.APPLICATION_ID = B.APPLICATION_ID;

  DBMS_OUTPUT.PUT_LINE ('Resp. ID  : ' || num_respID);
  DBMS_OUTPUT.PUT_LINE ('Resp. Name: ' || var_respName);
  DBMS_OUTPUT.PUT_LINE ('Application ID   : ' || num_appID);
  DBMS_OUTPUT.PUT_LINE ('Application Name : ' || var_appName);
  DBMS_OUTPUT.PUT_LINE ('-------------------------------------------');
  DBMS_OUTPUT.PUT_LINE ('Scanning tables...');

  FOR RS IN (  SELECT a.owner
                    , a.table_name
                 FROM dba_tab_columns a
                    , dba_objects b
                WHERE a.column_name = 'RESPONSIBILITY_ID'
                  AND a.table_name = b.object_name
                  AND b.object_type = 'TABLE'
                  AND a.table_name NOT IN ('FND_RESPONSIBILITY', 'FND_RESPONSIBILITY_TL')
             ORDER BY 1
                    , 2) LOOP
    var_sqlStmt   := 'SELECT COUNT(*) FROM ' || RS.OWNER || '.' || RS.TABLE_NAME || ' WHERE RESPONSIBILITY_ID = ' || num_respID;

    EXECUTE IMMEDIATE var_sqlStmt INTO num_count;

    IF num_count > 0 THEN
      boo_proceed   := FALSE;
      DBMS_OUTPUT.PUT_LINE (RS.TABLE_NAME || ' (' || num_count || ')');
    END IF;
  END LOOP;

  SELECT COUNT (*)
    INTO num_count
    FROM WF_LOCAL_USER_ROLES
   WHERE ROLE_ORIG_SYSTEM_ID = num_respID
     AND ROLE_ORIG_SYSTEM LIKE 'FND_RESP%'
     AND ROLE_NAME LIKE 'FND_RESP%';

  IF num_count > 0 THEN
    boo_proceed   := FALSE;
    DBMS_OUTPUT.PUT_LINE ('WF_LOCAL_USER_ROLES (' || num_count || ')');
  END IF;

  SELECT COUNT (*)
    INTO num_count
    FROM WF_USER_ROLE_ASSIGNMENTS
   WHERE ROLE_ORIG_SYSTEM_ID = num_respID
     AND ROLE_ORIG_SYSTEM LIKE 'FND_RESP%'
     AND ROLE_NAME LIKE 'FND_RESP%';

  IF num_count > 0 THEN
    boo_proceed   := FALSE;
    DBMS_OUTPUT.PUT_LINE ('WF_USER_ROLE_ASSIGNMENTS (' || num_count || ')');
  END IF;

  DBMS_OUTPUT.PUT_LINE ('-------------------------------------------');

  IF boo_proceed = TRUE OR p_ForceDelete = TRUE THEN
    FND_RESPONSIBILITY_PKG.DELETE_ROW (num_respID
                                     , num_appID);
    DBMS_OUTPUT.PUT_LINE ('Responsibility ' || var_respName || ' deleted successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE ('Responsibility ' || var_respName || ' cannot be deleted.');
  END IF;
  
  EXCEPTION 
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Responsibility key ' || p_respKey || ' is not found.');  
END;
/

How to Delete Concurrent Program

When you design a Concurrent Program. one of the annoyance is that once the Program Short Name is used by a Concurrent Program, the GUI does not provide anyway to change it.  You can delete Concurrent Program Executable (if it is not being assigned in any Concurrent Program), but you cannot delete Concurrent Program.  Why?

• If this Concurrent Program has been executed and it will leave records in the Concurrent Requests tables (FND_CONCURRENT_PROCESSES, FND_CONCURRENT_REQUESTS, etc)
• This Concurrent Program could be a part of a Concurrent Program Set.
• This Concurrent Program is used in a non-SRS way (e.g. AP format payment, WSH shipment Document Set)

If this Concurrent Program does not referenced or used in anywhere, it is safe to delete it.  Oracle does provide API to do this:  FND_PROGRAM.DELETE_PROGRAM. You can run the following script to delete such Concurrent Program, and you need to issue a COMMIT to really delete it.

SET SERVEROUTPUT ON

DECLARE
-- Change the following two parameters to fit your needs
p_progShortName   VARCHAR2 (100) := '[Program short name to be deleted]';
p_forceDelete     BOOLEAN        := [TRUE|FALSE];

num_programID     NUMBER;
var_progName      VARCHAR2 (100);
num_appID         NUMBER;
num_appName       VARCHAR2 (100);

var_sqlStmt       VARCHAR2 (1000);
num_count         NUMBER;
boo_proceed       BOOLEAN := TRUE;


BEGIN
SELECT A.CONCURRENT_PROGRAM_ID
, B.USER_CONCURRENT_PROGRAM_NAME
, C.APPLICATION_ID
, C.APPLICATION_NAME
INTO num_programID
, var_progName
, num_appID
, num_appName
FROM FND_CONCURRENT_PROGRAMS A
, FND_CONCURRENT_PROGRAMS_TL B
, FND_APPLICATION_TL C
WHERE A.CONCURRENT_PROGRAM_NAME = p_progShortName
AND A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID
AND A.APPLICATION_ID = C.APPLICATION_ID
AND B.LANGUAGE = 'US'
AND C.LANGUAGE = 'US';

DBMS_OUTPUT.PUT_LINE ('Program Name     : ' || var_progName);
DBMS_OUTPUT.PUT_LINE ('Program ID       : ' || num_programID);
DBMS_OUTPUT.PUT_LINE ('Application Name : ' || num_appID);
DBMS_OUTPUT.PUT_LINE ('Application ID   : ' || num_appName);

DBMS_OUTPUT.PUT_LINE ('Scanning CONCURRENT_PROGRAM_ID...');

FOR RS IN (
 SELECT b.owner
, a.table_name
, b.object_type
FROM dba_tab_columns a
, dba_objects b
WHERE a.column_name = 'CONCURRENT_PROGRAM_ID'
AND a.table_name = b.object_name
AND a.owner = b.owner
AND b.object_type NOT IN ('VIEW', 'SYNONYM')
AND a.table_name NOT IN ('FND_CONCURRENT_PROGRAMS', 
'FND_CONCURRENT_PROGRAMS_TL', 
'FND_CONC_PROG_ONSITE_INFO')
ORDER BY 2,1,3
) LOOP
var_sqlStmt   := 'SELECT COUNT(*) FROM ' || RS.OWNER || '.' || RS.TABLE_NAME || ' WHERE CONCURRENT_PROGRAM_ID = ' || num_programID;

EXECUTE IMMEDIATE var_sqlStmt INTO num_count;

IF num_count > 0 THEN
boo_proceed   := FALSE;
DBMS_OUTPUT.PUT_LINE (RS.TABLE_NAME || ' (' || num_count || ')');
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE ('FND_REQUEST_GROUP_UNITS :');

FOR RS IN (SELECT a.request_group_name
, c.application_name
FROM FND_REQUEST_GROUPS A
, FND_REQUEST_GROUP_UNITS B
, FND_APPLICATION_TL C
WHERE A.request_group_id = B.request_group_id
AND B.request_unit_id = num_programID
AND B.request_unit_type = 'P'
AND A.application_id = C.application_id
AND C.language = 'US') LOOP
boo_proceed   := FALSE;
DBMS_OUTPUT.PUT_LINE (RS.request_group_name || '(' || RS.application_name || ')');
END LOOP;

IF boo_proceed = TRUE OR p_forceDelete = TRUE THEN
FND_PROGRAM.DELETE_PROGRAM (p_progShortName , num_appName);
DELETE FROM FND_CONCURRENT_REQUESTS WHERE CONCURRENT_PROGRAM_ID =  num_programID;

DBMS_OUTPUT.PUT_LINE ('Concurrent program deleted. Please issue a commit to save the changes.');

ELSE
DBMS_OUTPUT.PUT_LINE ('Cannot delete this concurrent program since it has been referenced.');  
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Invalid Concurrent program short name: ' || p_progShortName);
END;
/

How to delete "Object" in Oracle EBS ? (PREFACE)

After years of working with Oracle EBS, I am really piss off that I cannot delete some objects in the Oracle EBS. Yes, I understand the reason behind, and I'm fully support the decision on this limitation, e.g. you cannot delete Inventory or Engineering Item if this item have been utilized; or you cannot delete OE Transaction Type (or known as Order Type) if it has been used in Sales Orders. Once an object has been referenced, we should keep it in the system, for the sake of data integrity, completeness, auditing, etc. It is a no-brainer, common sense, undoubtedly argument, no room for discussion. period.

However, if an object is not referenced in anywhere, WHY I CANNOT DELETE IT ?

Accidentally we create something we don't want (typo, wrong naming conversion, business rule change, just for testing, trial-and-error, etc). This is human nature that we make mistakes, and sometimes it is beyond our control. However, we want to clean up the mess we created but Oracle don't allow us to do so.

Oracle simply say "You cannot delete XXX once it is created. You can end-date it, disable it, add 'do not use' in object name, tell users not to use it, charge penalty if it is used, give users warning letter if they use it...or whatever way to prevent it from being used by end users."

I WANT IT REMOVED FROM THE SYSTEM. I REALLY WANT TO.

In here I will post a series of blog (label with EBS Delete) to tell how to remove objects in Oracle EBS, even it is not allowed in the GUI or even in API.

Disclaimer: As shown in the Metalink everywhere - please try it in the development instance before deploy it to production. You should full understand the impact and solely responsible for what you do.