Test Environment After Restore Script

set log c:\temp\clone_DBNAME.log;
delete from PS.PSDBOWNER;
insert into PS.PSDBOWNER values('DBNAME','SCHEMA');
grant select on PSSTATUS to USER people;
grant select on PSOPRDEFN to USER people;
grant select on PS.PSDBOWNER to USER people;
grant select on PSACCESSPRFL to USER people;

--Invalidate all email addresses

update ps_rolexlatopr
set email_user_sw = 'N';

update PSUSEREMAIL
set emailid = 'xxxxxx@glic.com';

update psoprdefn set emailid = 'xxxxxx@glic.com';

--MASK Vendor Names on Single Pay vendors

UPDATE SCHEMA.PS_VCHR_VNDR_INFO B
SET NAME1 = 'TEST NAME1'
where exists (SELECT 'x'
FROM SCHEMA.PS_VOUCHER
WHERE BUSINESS_UNIT = B.BUSINESS_UNIT
AND VOUCHER_ID = B.VOUCHER_ID
AND VOUCHER_STYLE = 'SGLP'
AND VENDOR_ID LIKE 'SNGL%');

UPDATE SCHEMA.PS_PYCYCL_RST_TBL B
SET NAME1 = 'TEST NAME1'
WHERE EXISTS (SELECT 'X'
FROM SCHEMA.PS_VOUCHER
WHERE BUSINESS_UNIT = B.BUSINESS_UNIT
AND VOUCHER_ID = B.VOUCHER_ID
AND VOUCHER_STYLE = 'SGLP'
AND B.REMIT_VENDOR LIKE 'SNGL%');

UPDATE SCHEMA.PS_PAYMENT_TBL B
SET NAME1 = 'TEST NAME1'
WHERE EXISTS (SELECT 'X'
FROM SCHEMA.PS_PYCYCL_RST_TBL
WHERE BUSINESS_UNIT = B.BUSINESS_UNIT_GL
AND PYMNT_ID = B.PYMNT_ID
AND REMIT_VENDOR LIKE 'SNGL%');

--MASK TIN

UPDATE PS_VENDOR_WTHD SET TIN = '999999999'
WHERE TIN <> '';

-- ********************************************************
-- Purges all entries found in the Process Request tables
-- ********************************************************
-- RUN D:\FSCM851\scripts\prcsclr.dms;
-- ******************************************************************
-- Purges all Application Messaging queue data. Data in both live
-- and archived tables are purged.
-- ******************************************************************
-- RUN D:\FSCM851\scripts\appmsgpurgeall.dms;

-- Update Attachement Servers
-- This needs to have a path to where all of the attachment files will be stored to
-- Usually this would be ftp server directory + environment key
-- User id and password need to have ftp priviledge on PeopleSoft server

UPDATE PS_PV_ATT_SERVERS
SET ATT_FTPSRV_LOGIN = 'user', ATT_FTPSRV_PASSWD = 'password', ATT_SRV_NAME = 'servername',
SERVER_PATH = 'attachment_directory', DEFAULT_SERVER_FLG = 'Y';

-- ******************************************************************
-- Update Attachement Paths
-- Set (e.g. SCM, DEV, PRC,TST) Compare options
-- (this should be NT App Server for the environment )
-- Update (e.g. SCM, DEV, PRC,TST) build index run controls with proper paths
-- ******************************************************************

UPDATE SCHEMA.PS_INSTALLATION_CS
SET CS_VERITY_SRCH = ' ' , CS_CHUNK_SIZE = 20,
APPSERVER_PATH = '/tmp',
CS_NTSERVER_PATH = 'd:\temp', CS_NT_SERVER_URL = 'server:port'

-- ******************************************************************
-- CONFIGURE PUNCHOUT NODES WITH TEST VENDOR URLS
-- UPDATE THIS SECTION IN CASE ADDITIONAL PUNCHOUT/ PO DISPATCH VENDORS ARE ADDED
-- ******************************************************************

UPDATE SCHEMA.PSNODECONPROP
SET PROPVALUE = 'https://punchout url'
WHERE
MSGNODENAME = 'NODENAME'
AND PROPNAME = 'URL';

-- ******************************************************************
-- Update Link Supplier connection properties
-- ******************************************************************

UPDATE SCHEMA.PS_PV_MS_SUPP_NV
SET PV_NV_VALUE = 'value'
WHERE VENDOR_ID = 'vendorid'
AND PV_NV_NAME = 'SHARED_SECRET';

UPDATE SCHEMA.PS_PV_MS_SUPP_NV
SET PV_NV_VALUE = 'value'
WHERE VENDOR_ID = 'vendorid'
AND PV_NV_NAME IN ('SENDER_IDENTITY','FROM_IDENTITY');

-- ******************************************************************
-- CONFIGURE PUNCHOUT ROUTINGS WITH TEST VENDOR URLS
-- UPDATE THIS SECTION IN CASE ADDITIONAL PUNCHOUT/ PO DISPATCH VENDORS ARE ADDED
-- ******************************************************************
UPDATE SCHEMA.PSRTNGDFNCONPRP
SET PROPVALUE = 'https://punchout url'
WHERE
ROUTINGDEFNNAME = 'NODENAME'
AND PROPNAME = 'URL';

UPDATE SCHEMA.PSRTNGDFNCONPRP
SET PROPVALUE = 'https://dispatch url'
WHERE
ROUTINGDEFNNAME = 'NODENAME'
AND PROPNAME = 'URL';

-- ******************************************************************
-- Process Scheduler, Distribution Node changes
-- ******************************************************************

-- If you are using Reverse Proxies
UPDATE SCHEMA.PSWEBPROFNVP
SET PT_PROPVALUE = 'server url'
WHERE WEBPROFILENAME = 'WEB PROFILE' and PROPERTYNAME = 'PSWEBSERVERNAME';

UPDATE SCHEMA.PSWEBPROFNVP
SET PT_PROPVALUE = 'server url:port number'
WHERE WEBPROFILENAME = 'WEBPROFILENAME' and PROPERTYNAME = 'RPS';

-- If you are storing some urls that need to be updated based on the envronment
UPDATE SCHEMA.PSURLDEFN
SET URL =
'https://new url'
WHERE URL_ID = 'URLID';

-- Update report distribution node
UPDATE PS_CDM_DIST_NODE
SET URL='https://SERVER:PORT/PIA_NAME/psreports/ENVNAME',
URI_RESOURCE='SchedulerTransfer/ENVNAME',
uri_rpt='http://servername:port/psc/ENVNAME/EMPLOYEE/ERP/c/CDM_RPT.CDM_RPT.GBL?Page=CDM_RPT_INDEX&Action=U&CDM_ID=',
URI_HOST='servername',
URI_PORT=port
WHERE DISTNODENAME = 'HTTP';

UPDATE PSOPTIONS SET GUID = '';

-- ******************************************************************
-- In case you are using SignOn PeopleCode and need to disable it, e.g. custom single sign on solutions
-- and you need to disable it in test environment to use PeopleSoft security
-- ******************************************************************

UPDATE PSOPTIONS SET OPRID = '';