Script to migrate Form Personalization from one environment to another

In this blog we will use a PL/SQL script to migrate Oracle form personalization from one environment  to another in place of using FNDLOAD ldt file. This script will append the new personalizations in migration environment in place of replacement.

When we UPLOAD form personalization using FNDLOAD from first environment to migration environment then in migration environment, it first delete the existing personalization and then add all those personalizations which are available in first environment. In this case there are changes to loose those personalization in migration environment which are new and does not exist in first environment.


Below are the steps you have to follow to migrate the personalizations.

1. Run the below script from SQL Developer or Toad. This script will ask few input parameters like personalized form name, function name and newly added personalization sequence number from and to. Also, before running this script change the location of spool, where you want to generate a file in your local system.

Script:

/***********************************************************************/
SET SERVEROUTPUT ON FORMAT WRAPPED
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF
DBMS_OUTPUT.ENABLE(1000000);
spool 'D:\Gaurav\personalization.sql'
DECLARE
  CURSOR rules_cur
  IS
    SELECT *
FROM fnd_form_custom_rules
WHERE rule_key IS NULL
    AND form_name = UPPER('&FORM_NAME')
AND function_name = UPPER('&FUNCTION_NAME')
AND sequence BETWEEN &SEQUENCE_FROM AND &SEQUENCE_TO
ORDER BY sequence;
  CURSOR actions_cur (p_rule_id NUMBER)
  IS
    SELECT *
FROM fnd_form_custom_actions
WHERE rule_id = p_rule_id
ORDER BY sequence;
  CURSOR params_cur (p_action_id NUMBER)
  IS
    SELECT *
FROM fnd_form_custom_params
WHERE action_id = p_action_id
ORDER BY sequence;
  CURSOR scopes_cur (p_rule_id NUMBER)
  IS
    SELECT *
FROM fnd_form_custom_scopes
WHERE rule_id = p_rule_id;

  l_count     NUMBER;

  TYPE t_rules_tab IS TABLE OF rules_cur%rowtype INDEX BY BINARY_INTEGER;
  l_rules_tab  t_rules_tab;
BEGIN
  OPEN rules_cur;
  FETCH rules_cur BULK COLLECT INTO l_rules_tab;
  CLOSE rules_cur;
  
  l_count := l_rules_tab.count;
  DBMS_OUTPUT.PUT_LINE('SET DEFINE OFF;');
  DBMS_OUTPUT.PUT_LINE('DECLARE
  l_seq   NUMBER;');
  DBMS_OUTPUT.PUT_LINE('BEGIN');
  FOR i IN  l_rules_tab.FIRST .. l_rules_tab.LAST LOOP
    IF (i = 1 ) THEN
DBMS_OUTPUT.PUT_LINE('  BEGIN
    SELECT NVL(MAX(first),1)
    INTO l_seq
    FROM (SELECT sequence first,
             lead(sequence) over(order by sequence) last
          FROM fnd_form_custom_rules
          WHERE rule_key IS NULL
          AND form_name = '''||l_rules_tab(i).form_name||'''
      AND  function_name = '''||l_rules_tab(i).function_name||''')
    WHERE first <> last
    AND ((last-first-1)-'||l_count||') >=0;
  EXCEPTION
  WHEN OTHERS THEN
l_seq := 1;
  END;');
   END IF;
    DBMS_OUTPUT.PUT_LINE('  INSERT INTO fnd_form_custom_rules(
      id,
      function_name,
      description,
      trigger_event,
      trigger_object,
      condition,
      sequence,
      created_by, 
      creation_date,
      last_updated_by, 
      last_update_date, 
      last_update_login,
      enabled,
      fire_in_enter_query,
      rule_key,
      form_name,
      rule_type)
  VALUES(
      FND_FORM_CUSTOM_RULES_S.NextVal,
      '''||l_rules_tab(i).function_name||''',
      '''||replace(l_rules_tab(i).description,'''','''''')||''',
      '''||l_rules_tab(i).trigger_event||''',
      '''||l_rules_tab(i).trigger_object||''',
      '''||replace(l_rules_tab(i).Condition,'''','''''')||''',
      l_seq+'||i||',
      1,
      SYSDATE,
      1,
      SYSDATE,
      -1,
      '''||l_rules_tab(i).enabled||''',
      '''||l_rules_tab(i).fire_in_enter_query||''',
      '''||l_rules_tab(i).rule_key||''',
      '''||l_rules_tab(i).form_name||''',
      '''||l_rules_tab(i).rule_type||''');');
FOR actions_rec IN actions_cur (l_rules_tab(i).id) LOOP
DBMS_OUTPUT.PUT_LINE('  INSERT INTO fnd_form_custom_actions(
  sequence,
      property_value,
      argument_type,
      created_by, 
  creation_date,
      last_updated_by, 
  last_update_date, 
  last_update_login,
      target_object,
      action_type,
      enabled,
      object_type,
      folder_prompt_block,
      message_type,
      message_text,
      summary,
      builtin_type,
      builtin_arguments,
      language,
      rule_id,
      property_name,
      menu_entry,
      menu_label,
      menu_seperator,
      menu_enabled_in,
      menu_action,
      menu_argument_long,
      menu_argument_short,
      action_id,
      request_application_id)
  VALUES(
  '||actions_rec.sequence||',
  '''||REPLACE(actions_rec.property_value,'''','''''')||''',
  '''||actions_rec.argument_type||''',
  1,
      SYSDATE,
      1,
      SYSDATE,
      -1,
  '''||actions_rec.target_object||''',
  '''||actions_rec.action_type||''',
  '''||actions_rec.enabled||''',
  '''||actions_rec.object_type||''',
  '''||REPLACE(actions_rec.folder_prompt_block,'''','''''')||''',
  '''||actions_rec.message_type||''',
  '''||REPLACE(actions_rec.message_text,'''','''''')||''',
  '''||REPLACE(actions_rec.summary,'''','''''')||''',
  '''||actions_rec.builtin_type||''',
  '''||REPLACE(actions_rec.builtin_arguments,'''','''''')||''',
  '''||actions_rec.language||''',
  FND_FORM_CUSTOM_RULES_S.CurrVal,
  '''||actions_rec.property_name||''',
  '''||actions_rec.menu_entry||''',
  '''||REPLACE(actions_rec.menu_label,'''','''''')||''',
  '''||actions_rec.menu_seperator||''',
  '''||actions_rec.menu_enabled_in||''',
  '''||actions_rec.menu_action||''',
  '''||REPLACE(actions_rec.menu_argument_long,'''','''''')||''',
  '''||REPLACE(actions_rec.menu_argument_short,'''','''''')||''',
  FND_FORM_CUSTOM_ACTIONS_S.NextVal,
  '''||actions_rec.request_application_id||'''
  );');
FOR params_rec IN params_cur (actions_rec.action_id) LOOP
DBMS_OUTPUT.PUT_LINE('  INSERT INTO fnd_form_custom_params(
      action_id,
      name,
      value,
      sequence,
      default_type,
      inherit,
      last_updated_by, 
  last_update_date,
      created_by, 
  creation_date, 
  last_update_login)
  VALUES(
  FND_FORM_CUSTOM_ACTIONS_S.CurrVal,
  '''||REPLACE(params_rec.name,'''','''''')||''',
  '''||REPLACE(params_rec.value,'''','''''')||''',
  '''||params_rec.sequence||''',
  '''||params_rec.default_type||''',
  '''||params_rec.inherit||''',
  1,
      SYSDATE,
      1,
      SYSDATE,
      -1);');
END LOOP;
END LOOP;
FOR scopes_rec IN scopes_cur(l_rules_tab(i).id) LOOP
DBMS_OUTPUT.PUT_LINE('  INSERT INTO fnd_form_custom_scopes(
       rule_id,
       level_id,
       level_value,
       level_value_application_id,
       last_updated_by, 
   last_update_date,
       created_by, 
   creation_date, 
   last_update_login)
  VALUES(
   FND_FORM_CUSTOM_RULES_S.CurrVal,
   '||scopes_rec.level_id||',
   '''||REPLACE(scopes_rec.level_value,'''','''''')||''',
   '''||scopes_rec.level_value_application_id||''',
   1,
      SYSDATE,
      1,
      SYSDATE,
      -1
);');
END LOOP;
  END LOOP;
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE('EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(''Error while uploading the personalization. Error: ''||SQLERRM);
END;
/
COMMIT;
EXIT;
');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error while downloading the personalization. Error: '||SQLERRM);
END;
/
spool off;

/

/***********************************************************************/

2. After running this script, you will get a file at spool location with the name "personalization.sql".

3. Run this file (personalization.sql script) in migration environment.

4. Validate the personalization in migration environment.