Camp9PT849DBrefresh.sql: PS CS database refresh

Author: Robert Lawson
Environment: Windows Server, MS SQL Server, PeopleSoft
Description: This script updates PeopleSoft Campus Solution's v9 database all the steps to make it operational, point to 'test' instances, update security and clean out working tables.
Code:

/*
File: Camp9PT849DBrefresh.sql
Update : 11-Apr-2008/Robert Lawson
Purpose: Post db updates for Campus Solutions 9/PT 8.49

*/
-- Fix database users
exec sp_change_users_login 'update_one','folks','folks'
exec sp_change_users_login 'update_one','Angel_Access','Angel_Access'
exec sp_change_users_login 'update_one','db-user','db-user'

-- \scripts\grant.sql
GRANT SELECT ON PSSTATUS TO folks
GRANT SELECT ON PSACCESSPRFL TO folks
GRANT SELECT ON PSOPRDEFN TO folks


-- Disable student accounts
UPDATE PSOPRDEFN SET ACCTLOCK = 1
WHERE OPRCLASS = 'STUDENT'

-- Clear references to reports
DELETE FROM PS_CDM_LIST WHERE DISTNODENAME <> 'LSTEST'

-- QA: App Messaging
DELETE FROM PSAPMSGDOMSTAT
DELETE FROM PSAPMSGDSPSTAT

-- IB: Local Gateway
UPDATE PSGATEWAY SET
 CONNURL = 'http://cc1np.campus.edu:11/PSIGW/PeopleSoftListeningConnector'
 WHERE CONNGATEWAYID = 'LOCAL'

-- IB: Nodes
UPDATE PSNODECONPROP SET
PROPVALUE = 'http://ff1np.campus.edu:11/PSIGW/PeopleSoftListeningConnector'
WHERE MSGNODENAME = 'PSFT_EP'

UPDATE PSNODECONPROP SET
PROPVALUE = 'http://cc1np.campus.edu:11/PSIGW/PeopleSoftListeningConnector'
 WHERE MSGNODENAME = 'PSFT_LS'

-- IB: Connectors
UPDATE PSCONNPROP SET
PROPVALUE = 'http://ff1np.campus.edu:11/PSIGW/PeopleSoftListeningConnector'
WHERE PROPID = 'FINANCIAL'

UPDATE PSCONNPROP SET
PROPVALUE = 'http://cc1np.campus.edu:11/PSIGW/PeopleSoftListeningConnector'
WHERE PROPID = 'CAMPUS'

-- Report Distribution
UPDATE PS_SERVERDEFN SET DISTNODENAME = 'LSTEST         ' WHERE SERVERNAME = 'PSNT    '

-- Business Interlink (Credit Card Processing)
UPDATE PSIOSETTINGS SET
IOVALUE = REPLACE(IOVALUE,'LSPROD','LSTEST')
WHERE IONAME = 'CREDITCARD_TRANSACTION' AND
IOSETTINGNAME = 'properties_file'

-- REN server (rebuilt by App Server)
DELETE FROM PSMCFRENURLID
DELETE FROM PSREN

-- Change Assistant
UPDATE PSOPTIONS SET
SHORTNAME = 'LSTEST',
LONGNAME = 'LSTEST',
GUID = '',
SYSTEMTYPE = 'STS'

-- Security
INSERT INTO PSROLEUSER
(ROLEUSER, ROLENAME,DYNAMIC_SW) VALUES
('cgreen', 'UPG_STDN_REC','N')
INSERT INTO PSROLEUSER
(ROLEUSER, ROLENAME,DYNAMIC_SW) VALUES
('cgreen', 'UPG_STDTNFIN','N')

-- appmsgpurgeall.dms   (remove log statement; clears app messages)
--  core tables:
DELETE FROM PSAPMSGPUBHDR;
DELETE FROM PSAPMSGPUBDATA;
DELETE FROM PSAPMSGPUBCON;
DELETE FROM PSAPMSGSUBCON;
DELETE FROM PSAPMSGPUBERR;
DELETE FROM PSAPMSGPUBERRP;
DELETE FROM PSAPMSGPUBCERR;
DELETE FROM PSAPMSGPUBCERRP;
DELETE FROM PSAPMSGSUBCERR;
DELETE FROM PSAPMSGSUBCERRP;
DELETE FROM PSAPMSGPCONDATA;
DELETE FROM PSAPMSGSCONDATA;
DELETE FROM PSIBERR;
DELETE FROM PSIBERRP;

-- synchronous core tables:
DELETE FROM PSIBLOGHDR;
DELETE FROM PSIBLOGDATA;
DELETE FROM PSIBLOGERR;
DELETE FROM PSIBLOGERRP;
DELETE FROM PSIBLOGIBINFO;

--  archive tables:
DELETE FROM PSAPMSGARCHPH;
DELETE FROM PSAPMSGARCHPD;
DELETE FROM PSAPMSGARCHPC;
DELETE FROM PSAPMSGARCHSC;
DELETE FROM PSAPMSGARCHPT;
DELETE FROM PSAPMSGARCHST;
DELETE FROM PSIBLOGHDRARCH;
DELETE FROM PSIBLOGDATAARCH;
DELETE FROM PSIBLOGIBINFOAR;