Fin8PT844PostDBRefresh.sql: PS Financial database refresh

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

/*
File   : Fin8PT844PostDBRefresh.sql
Update : 22-Oct-2004/Robert Lawson
Purpose: Post db updates for Finance 8.8


*/
-- Fix user
exec sp_change_users_login 'update_one','folkss','folkss'
exec sp_change_users_login 'update_one','fdb-user','fdb-user'

-- Report node
UPDATE PS_SERVERDEFN SET DISTNODENAME = 'FITEST         ' WHERE SERVERNAME = 'PSNT    '

-- Change Assistant
UPDATE PSOPTIONS SET GUID = ''
UPDATE PSOPTIONS SET LONGNAME  = 'FITEST', SHORTNAME = 'FITEST'

-- Home brandings
UPDATE PSMSGCATDEFN SET
MESSAGE_TEXT = 'FITEST Home'
WHERE MESSAGE_SET_NBR = '95'
AND MESSAGE_NBR = '401'

/*
-- non-core users lock out, removed 6/16/2005
UPDATE PSOPRDEFN SET ACCTLOCK = 1 WHERE
 OPRID NOT IN ('VENUS','BINGO','weepy','sneezy','grouchy','sleepy')
*/

-- REN server  (not working right now)
-- DELETE FROM PSREN
-- DELETE FROM PSMCFRENURLID

-- Integration Broker
DELETE FROM PSAPMSGDOMSTAT
DELETE FROM PSAPMSGDSPSTAT

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

-- Node/Gateway
UPDATE PSNODECONPROP SET
PROPVALUE = 'http://fs1np.campus.edu:12/PSIGW/PeopleSoftListeningConnector'
WHERE MSGNODENAME = 'PSFT_EP'

UPDATE PSNODECONPROP SET
PROPVALUE = 'http://cs1.soka.campus:12/PSIGW/PeopleSoftListeningConnector'
 WHERE MSGNODENAME = 'PSFT_LS'

-- IB, Local Gateway, Connectors

UPDATE PSCONNPROP SET
PROPVALUE = 'http://fs1np.campus.edu:12/PSIGW/PeopleSoftListeningConnector'
WHERE PROPID = 'FINANCIAL'

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

-- DM/QA: 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;

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

--  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;