Oracle Applications: Production to Development Refresh/Clone Parameter Change SQL Script.
- MySQL snippets – CREATE TABLE statement sample as bash shell.
- Oracle Applications: Submitting Concurrent Request Using FND_CONCURRENT.WAIT_FOR_REQUEST Sample Code
- Simple PL/SQL script to remove the unwanted non-ascii characters from oracle or 11i database.
- Oracle Applications Script : Sample Script to Debug the Procedure, Packages Running At Backend
- How to Default Timestamp and/or Update timestamps with MySQL?
Big companies that follow Process oriented project management team, with multiple projects executed at phased manner, may face challenges during database refresh from production system to development or test systems. Many programs customized to connect to external systems such as lockbox servers, payment processing servers and other boundary server systems.
After every refresh, it is imperative to change the development systems to point to null or Development external test servers instead of production servers. If this is not done immediately after the database refresh, development data may accidentally reach the production external servers that cause havoc! For example, if some developers start automatic payment/receipt programs in DEV servers that connects to production payment processors, customers may get charged without any product shipped! This is illegal and I had seen, two times in last 10 years, which affects customer confidence!
We have created a simple table that stores development default values and production default values and a simple plsql block that reads the table and changes the default parameter depending on the instances. This can be a manual or automatic run after a refresh by DBAs. In many companies, this is maintained by DBAs. However, using this, Any one responsible can maintain the parameter and DBAs can run the script to change the values. We have even created a form to review, add, edit or delete. Currently, I am not providing the form (*.fmb) but the table and plsql block for your reference.
Here is the table script:
CREATE TABLE xxcus_con_default_value
(
CONC_PROGRAM_NAME VARCHAR2(30 BYTE) NOT NULL,
PARAM_NAME VARCHAR2(30 BYTE) NOT NULL,
DEV_DEFAULT_VALUE VARCHAR2(240 BYTE),
PROD_DEFAULT_VALUE VARCHAR2(240 BYTE),
CREATED_BY NUMBER(15) NOT NULL,
CREATION_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER(15) NOT NULL,
LAST_UPDATE_LOGIN NUMBER(15),
LAST_UPDATE_DATE DATE NOT NULL
)
Here is the form:
Here is the PLSQL script:
–REM =====================================================================================
/* Created on : 09-Jan-2006 */
/* Created By : Jiltin */
/* ======================================================================================= */
SET serveroutput ON size 1000000
SET arraysize 1
SET feed off
SET echo off
DECLARE
l_instance VARCHAR2(30):= ‘&1′;
l_report_only VARCHAR2(30):= ‘&2′;
CURSOR parm_list IS
SELECT conc.conc_program_name,
conc.param_name,
conc.dev_default_value,
conc.prod_default_value,
arg.DEFAULT_VALUE old_default_value
FROM xxcus_con_default_value conc,
FND_DESCR_FLEX_COL_USAGE_VL arg
WHERE arg.descriptive_flexfield_name = ‘$SRS$.’”conc.conc_program_name
AND arg.END_USER_COLUMN_NAME=conc.param_name
ORDER BY 1,2;
l_rowid rowid;
l_default_value VARCHAR2(240);
l_status VARCHAR2(3000):=‘Report Only’;
BEGIN
fnd_file.put_line(fnd_file.LOG,‘Instance: ‘”l_instance);
fnd_file.put_line(fnd_file.output,‘Instance Name: ‘”l_instance);
fnd_file.put_line(fnd_file.output,‘Resport Only Mode: ‘”l_report_only);
fnd_file.put_line(fnd_file.output,‘Program Name Parameter Current Value New Value Status’);
fnd_file.put_line(fnd_file.output,‘============ ========= ============= ========= ======’);
FOR rec IN parm_list LOOP
IF (l_instance = ‘Production’ ) THEN
l_default_value := rec.prod_default_value;
ELSE
l_default_value := rec.dev_default_value;
END IF;
IF (l_report_only <> ‘Yes’) THEN
BEGIN
UPDATE fnd_descr_flex_column_usages
SET DEFAULT_VALUE = l_default_value,
LAST_UPDATE_LOGIN = fnd_global.user_id,
LAST_UPDATE_DATE = sysdate
WHERE descriptive_flexfield_name = ‘$SRS$.’”rec.conc_program_name
AND END_USER_COLUMN_NAME= rec.param_name
AND DEFAULT_VALUE <> l_default_value;
fnd_file.put_line(fnd_file.LOG,rec.conc_program_name”‘,’”rec.param_name”‘,’”l_default_value”‘,’”SQL%ROWCOUNT);
IF (SQL%ROWCOUNT > 0 ) THEN
l_status := ‘Updated’;
ELSE
l_status := ‘Not Updated’;
END IF;
EXCEPTION
WHEN others THEN
l_status := sqlerrm;
END;
END IF;
fnd_file.put_line(fnd_file.output,rpad(rec.conc_program_name,20,‘ ‘)”‘ ‘”rpad(rec.param_name,30,‘ ‘)”‘ ‘”rpad(rec.old_default_value,70)”‘ ‘”rpad(l_default_value,70,‘ ‘)”‘ ‘”l_status);
END LOOP;
IF (l_report_only <> ‘Yes’) THEN
commit;
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.LOG,‘Error occurred while executing: ‘ ” SUBSTR(SQLERRM, 1, 150));
END;
/
So, for me, this plugin did not work. If you have suggestions or recommendations, I would very much appreciate them. Thanks.