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.
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.
