Changing a Customer's Client Code#

Identify the tables that need to be changed#

select 
  --table_name, column_name, data_type
  --'select count(*) from '|| table_name ||' where '|| column_name ||' = ''PXP'';'
  'update '|| table_name ||' set '|| column_name ||' = :new where '|| column_name ||' = :old;'
from user_tab_cols
where column_name in ('CUSTOMER_CODE', 'USER_CODE', 'CUST_CODE')
  and table_name not in (select view_name from user_views)
  and data_type <> 'NUMBER'
--where column_name like '%USER%'
order by table_name, column_id, column_name
;

Run in SQL Developer#

1. update HLC_ASSIGNMENTS set CUST_CODE = :new where CUST_CODE = :old;
2. update HLC_CUST_ENVIRONMENTS set USER_CODE = :new where USER_CODE = :old;
3. update HLC_CUST_PROFILE set USER_CODE = :new where USER_CODE = :old;
4. update HLC_CUST_SUBORGS set USER_CODE = :new where USER_CODE = :old;
5. update HLC_FILES_DOWNLOADED set USER_CODE = :new where USER_CODE = :old;
6. update HLC_HUG set CUST_CODE = :new where CUST_CODE = :old;
7. update HLC_HUG_2007_PARTICIPANTS set CUST_CODE = :new where CUST_CODE = :old;
8. update HLC_HUG_2010_PARTICIPANTS set CUST_CODE = :new where CUST_CODE = :old;
9. update HLC_NOTIFICATIONS_SENT set CUSTOMER_CODE = :new where CUSTOMER_CODE = :old;
10. update HLC_PATCHES_SENT set CUSTOMER_CODE = :new where CUSTOMER_CODE = :old;
11. update HLC_QUOTES set CUSTOMER_CODE = :new where CUSTOMER_CODE = :old;
12. update HLC_TASKS set CUSTOMER_CODE = :new where CUSTOMER_CODE = :old;
13. update HLC_TIMESHEETS set USER_CODE = :new where USER_CODE = :old;
14. update HLC_TRIP_REPORTS set CUST_CODE = :new where CUST_CODE = :old;
15. update HLC_WORK_DAYS set USER_CODE = :new where USER_CODE = :old;
16. update NXSCUSTE set USER_CODE = :new where USER_CODE = :old;

HighLine.Standard Operating Procedures