Production to Development Refresh/Clone Parameter Change SQL Script.

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.
Download parameterchange.sql
Here is the table script:

DROP TABLE xxcus_con_default_value CASCADE CONSTRAINTS; 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:

–Source File                :ParameterChange.sql
–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;
/