Siebel Database Upgrade Guide > Preparing Siebel Application Data for Upgrade >

Migrating Siebel Address Data from Custom Extension Columns

Upgrades:

• From Siebel Financial Services 7.0.x to Siebel Industry Applications (SIA) 8.0.x
• From Siebel Business Applications 8.0.x to Siebel SIA 8.0.x
• From Siebel Financial Services 6.2.1 on IBM z/OS platforms to Siebel SIA 8.0.x on IBM z/OS platforms

Environments: Development and production test.

Platforms: Windows, UNIX, IBM z/OS.

NOTE:  This topic does not apply to Siebel Business Applications (HOR) that you are upgrading to a later release of Siebel Business Applications (HOR).

This topic is part of an upgrade process. See How to Perform a Siebel Database Upgrade.

If you have custom extension columns on the Address table S_ADDR_ORG, you must manually edit your upgrade scripts to prevent loss of data during an upgrade. Perform the following task, and contact your Oracle sales representative for Oracle Advanced Customer Services if you need assistance from Oracle's Application Expert Services.

Prerequisite. You must have run the Database Server Configuration Utilities in upgrep mode. This creates the files required for this procedure. You perform this procedure before running the Upgrade Wizard.

To migrate address data from custom extension columns

1. Development environment upgrades only. Open the ddl.ctl file with a text editor.

   Here is the path to the file in the Siebel Database Server installation:

   Windows: DBSRVR_ROOT\database_platform

   UNIX: DBSRVR_ROOT/database_platform

   where

   database_platform is the database type, for example DB2.

2. Development environment upgrades only. Add the custom extension columns from S_ADDR_PER and S_ADDR_ORG that are attributes of the association of the address to contact or account to the S_CON_ADDR definition.

   Example of the file:

   [Object nnn]

   Type = Table

   Name = S_CON_ADDR

   Space = USERSPACE1

   Column 1 = ROW_ID VARCHAR(15) OTNULL

   Column 2 = CREATED TIMESTAMP NOTNULL DEFAULT %NOW%

   Column 3 = CREATED_BY VARCHAR(15) NOTNULL

   Column 4 = LAST_UPD TIMESTAMP NOTNULL DEFAULT %NOW%

   Column 5 = LAST_UPD_BY VARCHAR(15) NOTNULL

   Column 6 = DCKING_NUM NUMERIC(22,7) DEFAULT 0

   ... ... ...

   Column 46 = RURAL_ROUTE_NUM VARCHAR(10)

   Column 47 = START_DT TIMESTAMP

   Column 48 = X_CUSTOM_EXTENSION_COLUMN1 VARCHAR(15)

   Column 49 = X_CUSTOM_EXTENSION_COLUMN2 VARCHAR(15)

3. Development environment upgrades only.In the ddl.ctl file, add the custom extension columns from S_ADDR_ORG that are attributes of the address to the S_ADDR_PER definition (provided corresponding columns do not already exist on S_ADDR_PER) similar to the example illustrated in the previous step.
4. Development environment and production test environment upgrades. In the file preschm_sia.sql, add the custom extension columns for S_CON_ADDR to the migration scripts if any custom extension column was added to the definition of S_CON_ADDR in the ddl.ctl file in the preceding steps.

   Here is the path to the file in the Siebel Database Server installation:

   Windows: DBSRVR_ROOT\database_platform\upgrade\version

   UNIX: DBSRVR_ROOT/database_platform/upgrade/version

   Example of the script (this example is for inserts into S_CON_ADDR from S_ADDR_PER. (The example also applies to custom extension columns to be inserted on S_CON_ADDR from S_ADDR_ORG):

   Insert into S_CON_ADDR

   (ROW_ID

   ,CREATED

   ,CREATED_BY

   ,LAST_UPD

   ,LAST_UPD_BY

   ,MODIFICATION_NUM

   ,CONFLICT_ID

   ,ADDR_PER_ID

   ,ADDR_TYPE_CD

   ,CONTACT_ID

   ,EMAIL_ADDR

   ,FAX_PH_NUM

   ,PH_NUM

   ,ADDR_MAIL_CD

   ,END_DT

   ,START_DT

   ,ACTIVE_FLG

   ,X_CUSTOM_EXTENSION_COLUMN1

   ,X_CUSTOM_ EXTENSION_COLUMN2)

   select

   ROW_ID

   ,CREATED

   ,CREATED_BY

   ,LAST_UPD

   ,LAST_UPD_BY

   ,MODIFICATION_NUM

   ,CONFLICT_ID

   ,ROW_ID

   ,ADDR_TYPE_CD

   ,PER_ID

   ,EMAIL_ADDR

   ,FAX_PH_NUM

   ,PH_NUM

   ,ADDR_MAIL_CD

   ,END_DT

   ,START_DT

   ,case

   when ACTIVE_FLG is null then 'Y'

   else ACTIVE_FLG

   end

   ,X_CUSTOM_ EXTENSION_COLUMN1

   ,X_CUSTOM_ EXTENSION_COLUMN2

   from S_ADDR_PER APT

   where not exists

   (select 'x' from S_CON_ADDR CAD

   where CAD.CONTACT_ID = APT.PER_ID

   and CAD.ADDR_PER_ID = APT.ROW_ID

   and CAD.CONFLICT_ID = APT.CONFLICT_ID

   )

   and not exists

   (select 'x' from S_CON_ADDR CAD1

   where CAD1.ROW_ID = APT.ROW_ID

   )

   and APT.PER_ID is not null

5. Development environment and production test environment upgrades. In the file preschm_sia.sql, add the custom extension columns for S_ADDR_PER to the migration script if any custom extension column was added to the definition of S_ADDR_PER in the ddl.ctl file.

   Here is the path to the file in the Siebel Database Server installation:

   Windows: DBSRVR_ROOT\database_platform\upgrade\version

   UNIX: DBSRVR_ROOT/database_platform/upgrade/version

   Example of the file:

   insert into S_ADDR_PER

   (ROW_ID

   ,CREATED

   ,CREATED_BY

   ,LAST_UPD

   ,LAST_UPD_BY

   ,MODIFICATION_NUM

   ,CONFLICT_ID

   ,DISA_CLEANSE_FLG

   ,ADDR

   ,ADDR_NUM

   ,ADDR_TYPE_CD

   ,CITY

   ,COMMENTS

   ,COUNTRY

   ,COUNTY

   ,INTEGRATION_ID

   ,INTEGRATION2_ID

   ,INTEGRATION3_ID

   ,PROVINCE

   ,STATE

   ,ZIPCODE

   ,ADDR_NAME

   ,NAME_LOCK_FLG

   ,DESCRIPTOR

   ,EMAIL_ADDR

   ,FAX_PH_NUM

   ,PH_NUM

   ,ADDR_LINE_2

   ,ADDR_LINE_3

   ,DCKING_NUM

   ,LATITUDE

   ,LONGITUDE

   ,ACTIVE_FLG

   ,ADDR_MAIL_CD

   ,ADDR_SUB_CD

   ,END_DT

   ,RURAL_ROUTE_NUM

   ,START_DT

   ,X_CUSTOM_EXTENSION_COLUMN1

   ,X_CUSTOM_EXTENSION_COLUMN2

   )

   select

   ROW_ID

   ,CREATED

   ,CREATED_BY

   ,LAST_UPD

   ,LAST_UPD_BY

   ,MODIFICATION_NUM

   ,CONFLICT_ID

   ,DISA_CLEANSE_FLG

   ,ADDR

   ,ADDR_NUM

   ,ADDR_TYPE_CD

   ,CITY

   ,COMMENTS

   ,COUNTRY

   ,COUNTY

   ,INTEGRATION_ID

   ,INTEGRATION2_ID

   ,INTEGRATION3_ID

   ,PROVINCE

   ,STATE

   ,ZIPCODE

   ,case when ADDR_NAME = (select t.DUP_ADDR_NAME

   from TMPTBL_ADDR t

   where t.DUP_ADDR_NAME = S_ADDR_ORG.ADDR_NAME and t.MIN_ROW_ID <> S_ADDR_ORG.ROW_ID

   )

   then {fn concat({fn concat({fn rtrim({fn substring(ADDR_NAME,1,99-{fn length(ROW_ID)})})},':')},ROW_ID)}

   else ADDR_NAME

   end

   ,NAME_LOCK_FLG

   ,DESCRIPTOR

   ,EMAIL_ADDR

   ,FAX_PH_NUM

   ,PH_NUM

   ,ADDR_LINE_2

   ,ADDR_LINE_3

   ,DCKING_NUM

   ,LATITUDE

   ,LONGITUDE

   ,ACTIVE_FLG

   ,ADDR_MAIL_CD

   ,ADDR_SUB_CD

   ,END_DT

   ,RURAL_ROUTE_NUM

   ,START_DT

   ,X_CUSTOM_EXTENSION_COLUMN1

   ,X_CUSTOM_EXTENSION_COLUMN2

   from S_ADDR_ORG

6. Development environment upgrades only. In Siebel Tools, add the custom extension columns defined in the previous steps to S_CON_ADDR and S_ADDR_PER to the Prior Customer Repository.

   Remember that the Prior Customer Repository is the renamed Siebel Repository for the release your are upgrading from.

7. Development environment upgrades only. Configure your application to expose the custom extension columns added to S_ADDR_PER.

Related Topics

Upgrade Planning for Migrating Siebel Address Data

Preparing Siebel Address Data for Upgrade