REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	a_conv_terr_name.sql
REM
REM <SQLDIR_GRP>APPS</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy (www.dbatoolz.com)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Converts Territory Short Name to UPPER case
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	a_conv_terr_name.sql
REM 
REM Example:
REM	a_conv_terr_name.sql
REM
REM
REM History:
REM	12-01-1998	VMOGILEV	Created
REM
REM

accept dummy prompt "Here Are all TERRITORIES Before UPDATE [Press Enter Now To View]:"

select count(*), TERRITORY_SHORT_NAME
from APPLSYS.FND_TERRITORIES_TL
group by TERRITORY_SHORT_NAME;

accept dummy prompt "Do you want to convert TERRITORY_SHORT_NAME to UPPER CASE [Press Enter or ctl-C]:"


declare
   cursor lower_name_cur IS
   select    TERRITORY_SHORT_NAME
   from      APPLSYS.FND_TERRITORIES_TL;
BEGIN
 FOR lower_name_rec IN lower_name_cur LOOP
   update   APPLSYS.FND_TERRITORIES_TL
   set      TERRITORY_SHORT_NAME = upper(lower_name_rec.TERRITORY_SHORT_NAME)
   where    TERRITORY_SHORT_NAME = lower_name_rec.TERRITORY_SHORT_NAME;
 END LOOP;
END;
/


accept dummy prompt "Here Are all TERRITORIES After UPDATE [Press Enter Now To View]:"

select count(*), TERRITORY_SHORT_NAME
from APPLSYS.FND_TERRITORIES_TL
group by TERRITORY_SHORT_NAME;

prompt "If you want to save changes enter COMMIT; to rollback enter ROLLBACK;"