PeopleSoft Upgrade Related Script

PeopleSoft Upgrade Related Script

UPDATE PSIBRTNGDEFN SET sendernodename = 'PSFT_FINT92' where sendernodename = 'PSFT_FCNV92';
UPDATE PSIBRTNGDEFN SET receivernodename = 'PSFT_FINT92' where receivernodename = 'PSFT_FCNV92';
UPDATE PSRTNGDFNPARM SET sendernodename = 'PSFT_FINT92' where sendernodename = 'PSFT_FCNV92';
UPDATE PSRTNGDFNPARM SET receivernodename = 'PSFT_FINT92' where receivernodename = 'PSFT_FCNV92';
UPDATE PSMSGNODEDEFN SET msgnodename = 'PSFT_FINT92' where MSGNODENAME = 'PSFT_FCNV92';

UPDATE PSNODECONPROP set PROPVALUE = 't3://UXESBDEV:' where PROPVALUE like '%t3%' and msgnodename like 'WE_%';

update PSNODECONPROP set propvalue = '\\NT-WRFSDATA\PSoft_Fin\FINT92\Inter\IN\out\Hubbell' where PROPVALUE = '\\NT-WRFSDATA\PSoft_Fin\FCNV92\Inter\IN\out\Hubbell';
update PSNODECONPROP set propvalue = '\\NT-WRFSDATA\Psoft_Fin\FINT92\Inter\PO' where PROPVALUE = '\\NT-WRFSDATA\Psoft_Fin\FCNV92\Inter\PO';
update PSNODECONPROP set propvalue = '\\NT-WRFSDATA\PSoft_Fin\FINT92\Inter\AP' where PROPVALUE = '\\NT-WRFSDATA\PSoft_Fin\FCNV92\Inter\AP';
update PSNODECONPROP set propvalue = '\\NT-WRFSDATA\PSoft_Fin\FINT92\Inter' where PROPVALUE = '\\NT-WRFSDATA\PSoft_Fin\FCNV92\Inter';
update PSNODECONPROP set propvalue = '\\NT-WRFSDATA\PSoft_Fin\FINT92\Inter\IN\out\Koppers' where PROPVALUE = '\\NT-WRFSDATA\PSoft_Fin\FCNV92\Inter\IN\out\Koppers';
update PSNODECONPROP set propvalue = 'http://othercorp.com.com8880/PSAttachServlet/FINT/' where PROPVALUE = 'http://fscm.wr.com/PSAttachServlet/FCNV92/';
update sysadm.ps_BSP_IMPORT set FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FINT92\Inter\TR\in\USB\' where FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FCNV92\Inter\TR\in\USB\';
update sysadm.ps_BSP_IMPORT set FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FINT92\Inter\TR\in\USB\' where FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FCNV92\Inter\TR\in\USB\';
update sysadm.ps_BSP_IMPORT set FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FINT92\Inter\TR\in' where FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FCNV92\Inter\TR\in';
update sysadm.ps_BSP_IMPORT set FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FINT92\Inter\TR\in\' where FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FCNV92\Inter\TR\in\';
update sysadm.ps_BSP_IMPORT set FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FINT92\Inter\TR\in\WF\' where FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FCNV92\Inter\TR\in\WF\';
update sysadm.ps_BSP_IMPORT set FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FINT92\Inter\TR\in' where FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FCNV92\Inter\TR\in';
update sysadm.ps_BSP_IMPORT set FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FINT92\Inter\TR\in\JPM\' where FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FCNV92\Inter\TR\in\JPM\';
update sysadm.ps_BSP_IMPORT set FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FINT92\Inter\TR\in\WF\' where FILE_PATH_NAME = '\\NT-WRFSDATA\PSoft_Fin\FCNV92\Inter\TR\in\WF\';

UPDATE PS_FILE_DIR_FS set FILE_DIRECTORY = '\\nt-wrfsdata\PSoft_Fin\FINT92\File_Loc\EMA\' where FILE_DIRECTORY = '\\nt-wrfsdata\PSoft_Fin\FCNV92\File_Loc\EMA\';
UPDATE PS_FILE_DIR_FS set FILE_DIRECTORY = '\\nt-wrfsdata\PSoft_Fin\FINT92\File_Loc\EMA' where FILE_DIRECTORY = '\\nt-wrfsdata\PSoft_Fin\FCNV92\File_Loc\EMA';
UPDATE PS_FILE_DIR_FS set FILE_DIRECTORY = '\\nt-wrfsdata\PSoft_Fin\FINT92\File_Loc\DOC' where FILE_DIRECTORY = '\\nt-wrfsdata\PSoft_Fin\FCNV92\File_Loc\DOC';
UPDATE PS_FILE_DIR_FS set FILE_DIRECTORY = '\\nt-wrfsdata\PSoft_Fin\FINT92\File_Loc\EDX\' where FILE_DIRECTORY = '\\nt-wrfsdata\PSoft_Fin\FCNV92\File_Loc\EDX\';

UPDATE PS_FILE_DIR_FS set FILE_DIR_SERVER = '\\nt-wrfsdata\PSoft_Fin\FINT92\File_Loc\EMA\' where FILE_DIR_SERVER = '\\nt-wrfsdata\PSoft_Fin\FCNV92\File_Loc\EMA\';
UPDATE PS_FILE_DIR_FS set FILE_DIR_SERVER = '\\nt-wrfsdata\PSoft_Fin\FINT92\File_Loc\EMA' where FILE_DIR_SERVER = '\\nt-wrfsdata\PSoft_Fin\FCNV92\File_Loc\EMA';
UPDATE PS_FILE_DIR_FS set FILE_DIR_SERVER = '\\nt-wrfsdata\PSoft_Fin\FINT92\File_Loc\DOC' where FILE_DIR_SERVER = '\\nt-wrfsdata\PSoft_Fin\FCNV92\File_Loc\DOC';

select * from PSREN;
select * from PSMCFRENURLID;
delete from PSREN;
delete from PSMCFRENURLID;
SELECT * FROM PSMCFRENURLID;
UPDATE PSMCFRENURLID SET MCFREN_URL_ID = 'RENCLSTR_0005', MCFREN_INT_URL='http://other:8390', MCFREN_BROWSER_URL='http://other.corp.top.wr.com:8390' WHERE MCFREN_URL_ID='RENCLSTR_0005';


DELETE FROM PS_SERVERDEFN where SERVERNAME = 'PSNTB';
DELETE FROM PS_SERVERDEFN where SERVERNAME = 'PSNVS';
DELETE FROM PS_SERVERDEFN where SERVERNAME = 'PSNTBB';

UPDATE PS_SERVERDEFN SET DISTNODENAME = 'FINT92';

UPDATE PS_VENDOR_PAY SET EMAILID = 'zafrulla.khan@xyz.com' where EMAILID <> ' ';
update PSOPRDEFN set EMAILID = ' ' where OPRID not like 'PSF%';
update PSUSEREMAIL set EMAILID = ' ' where OPRID not like 'PSF%';
update ps_rolexlatopr set EMAILID = ' ' where OPRID not like 'PSF%';

--*******************************************************************************************************

Select A.PNLGRPNAME, C.PTSF_SRCCAT_NAME from PSPNLGRPDEFNEXT A, PSPNLGRPDEFN B, PSPTSF_SRCCAT C where A.PNLGRPNAME = B.PNLGRPNAME AND B.OBJECTOWNERID = C.OBJECTOWNERID ORDER BY 1;
select * from PSPNLGRPDEFNEXT;
SELECT * FROM PSPNLGRPDEFN;
SELECT * from PS_PTSF_SRCHCAT;
Select 'Search Categories',' ',PTSF_SRCCAT_NAME,DESCR100,LASTUPDDTTM,LASTUPDOPRID from PSPTSF_SRCCAT;
Select * from PSPTSF_SRCCAT;

SELECT * FROM PS_PTSF_SRCHCAT;
SELECT * FROM PS_PTSF_ATTRS;
SELECT * FROM PS_PTSF_CAT_ADVFLD;
SELECT * FROM PS_PTSF_CAT_DSPFLD;
SELECT * FROM PS_PTSF_CAT_FACETS;
SELECT * FROM PS_PTSF_SBO;
SELECT * FROM PS_PTSF_SBO_ATTR;
SELECT * FROM PS_PTSF_SBO_DOCACL;
SELECT * FROM PS_PTSF_SBO_DOCATR;
SELECT * FROM PS_PTSF_SBO_PNLGRP;
SELECT * FROM PS_PTSF_SRCHCATATR;
SELECT * FROM PS_PTSF_URLDEFN;

select 'SELECT * FROM PS_' || recname || ';' from psrecdefn where recname like 'PTSF_%' and rectype=0 and sqltablename=' ';


UPDATE PSPROJECTDEFN SET RELEASELABEL='All Projects' WHERE PROJECTNAME IN('WE_MASTER_PRJ_SIT1');

SELECT PROJECTNAME, LASTUPDDTTM,RELEASELABEL,RELEASEDTTM FROM PSPROJECTDEFN WHERE LASTUPDDTTM > '10-MAY-2015' AND LASTUPDDTTM < '09-JUN-2015' AND LASTUPDOPRID <> 'PPLSOFT' ORDER BY LASTUPDDTTM;

UPDATE PSPROJECTDEFN SET RELEASELABEL='WE_MASTER_PRJ_SIT1' WHERE LASTUPDDTTM > '10-MAY-2015' AND LASTUPDDTTM < '09-JUN-2015' AND LASTUPDOPRID <> 'PPLSOFT';
SELECT PROJECTNAME, LASTUPDDTTM,RELEASELABEL,RELEASEDTTM FROM PSPROJECTDEFN WHERE RELEASELABEL = ' ' AND LASTUPDDTTM >= '09-JUN-2015'AND LASTUPDOPRID <> 'PPLSOFT' ORDER BY LASTUPDDTTM;

UPDATE PSPROJECTDEFN SET RELEASELABEL='NEW' WHERE RELEASELABEL = ' ' AND LASTUPDDTTM >= '09-JUN-2015'AND LASTUPDOPRID <> 'PPLSOFT';

UPDATE PSPROJECTDEFN SET RELEASELABEL='NEW' WHERE RELEASELABEL = ' ' AND LASTUPDDTTM >= '09-JUN-2015'AND LASTUPDOPRID <> 'PPLSOFT' and PROJECTNAME='WE_PSEXI004';

SELECT PROJECTNAME, LASTUPDDTTM,LASTUPDOPRID, RELEASELABEL,RELEASEDTTM FROM psprojectdefn;

SELECT PROJECTNAME from PSPROJECTDEFN where releaselabel='NEW';
DELETE FROM PSPROJECTITEM WHERE PROJECTNAME ='WE_MASTER_PRJ_SIT1_ADDED';
insert into psprojectitem select distinct 'WE_MASTER_PRJ_SIT1_ADDED'    ,OBJECTTYPE    ,OBJECTID1    ,OBJECTVALUE1    ,OBJECTID2    ,OBJECTVALUE2    ,OBJECTID3    ,OBJECTVALUE3    ,OBJECTID4    ,OBJECTVALUE4    ,0,0,0,0,0,0 from psprojectitem where projectname in (select projectname from psprojectdefn where releaselabel = 'NEW');
SELECT PROJECTNAME from PSPROJECTDEFN where releaselabel='NEW';

SET OUTPUT D:\temp\prjexp.dat;
EXPORT psprojectDEFN where projectname in (select projectname from psprojectdefn where releaselabel = 'NEW');
EXPORT psprojectitem where projectname in (select projectname from psprojectdefn where releaselabel = 'NEW');


SET INPUT D:\temp\prjexp.dat;
SET LOG D:\temp\IMP_prjexp.log;
SET UPDATE_DUPS;

IMPORT *;


SELECT PROJECTNAME from PSPROJECTDEFN where releaselabel='All after WE_MASTER_PRJ_SIT1';
UPDATE PSPROJECTDEFN SET RELEASELABEL='All after WE_MASTER_PRJ_SIT1' WHERE RELEASELABEL = 'NEW';

-- Inactive Chartfield whose FldtoIdx to be unchecked

SELECT A.* FROM PSPTSF_SD_ATTR A WHERE PTSF_SBO_NAME IN ('EP_AP_VENDOR', 'EP_AP_VOUCHERS', 'EP_CS_DOC_CONTRACTS', 'EP_CS_DOC_PURCHORDRS', 'EP_EX_REPORT', 'EP_GL_JOURNAL', 'EP_PO_CONTRACTS', 'EP_PO_PURCHASE_ORDERS', 'EP_PO_RECEIPTS', 'EP_PO_REQUISITIONS', 'EP_PV_EXPRESSITEMS_SD', 'EP_PV_MASTERITEMVENDOR_SD', 'EP_PV_MASTERITEM_SD', 'PTPORTALREGISTRY', 'PTSEARCHREPORTS', 'PTWL_GEN_MSG_WL') AND PTSF_ISFIELDTOIDX = 'Y' AND SUBSTR(QRYFLDNAME, INSTR(QRYFLDNAME,'.')+1, 50) IN (SELECT FIELDNAME FROM PS_FS_CF_TMPLT_VW WHERE CF_ACTIVE_STATUS<>'A') ORDER BY 1,2,3;


--Active Chartfield whose FldtoIdx to be Checked
SELECT A.* FROM PSPTSF_SD_ATTR A WHERE PTSF_SBO_NAME IN ('EP_AP_VENDOR', 'EP_AP_VOUCHERS', 'EP_CS_DOC_CONTRACTS', 'EP_CS_DOC_PURCHORDRS', 'EP_EX_REPORT', 'EP_GL_JOURNAL', 'EP_PO_CONTRACTS', 'EP_PO_PURCHASE_ORDERS', 'EP_PO_RECEIPTS', 'EP_PO_REQUISITIONS', 'EP_PV_EXPRESSITEMS_SD', 'EP_PV_MASTERITEMVENDOR_SD', 'EP_PV_MASTERITEM_SD', 'PTPORTALREGISTRY', 'PTSEARCHREPORTS', 'PTWL_GEN_MSG_WL') AND PTSF_ISFIELDTOIDX <> 'Y' AND SUBSTR(QRYFLDNAME, INSTR(QRYFLDNAME,'.')+1, 50) IN (SELECT FIELDNAME FROM PS_FS_CF_TMPLT_VW WHERE CF_ACTIVE_STATUS ='A') ORDER BY 1,2,3;