Converting nVision reports

CONVERTING nVision reports developed in older version of excel to the the latest Ms Excel 2016

Tools used 8.57.06

You have to follow the following steps for all the nVision layouts and drill downs.

1. Open the layout in the current Excel version i.e 2016 .
2. Click on Save As->Excel Macro-Enabled Workbook
3. Enter the name of the layout in double quotes: ".xnv".  ("account_summary.xnv")
4. Click on Save to complete the conversion.
5. Close the layout.

Once we did the above steps, you will be able to run the nVision via PIA successfully.

For all the Layouts including DrillDowns , steps 1 through 5  above for each layout that needs to be converted.    

Configuring Elastic Search PeopleSoft

Installation of Elastic Search

Open Command Prompt in administrator mode.

Make directory d:\apps\elastic

Download zip file ELASTICSEARCH-DPK-WIN-2.3.2_03.zip to d:\apps\elastic\ from MOS

Downloading Using getMOSPatch.jar :
  Download getMOSPatch-master.zip from github website.
  Unzip getMOSPatch-master.zip

From command prompt cd to d:\apps\elastic

echo %JAVA_HOME% -- If java is not installed , download and installed.

d:\apps\elastic>java -jar d:\apps\elastic\getMOSPatch-master\getMOSpatch.jar PATCH=26172221 PLATFORM=233P download=all
Provide your MOS userid and password

ELASTICSEARCH-DPK-WIN-2.3.2_03.zip will be download.

Unzip ELASTICSEARCH-DPK-WIN-2.3.2_03.zip and change directory to d:\apps\elastic\setup

run the following command to install Elastic Search.
psft-dpk-setup.bat --install --install_base_dir "d:\apps\esearch"

You will be prompted to provide password for following users
esadmin, people

You will be prompted to provide ES Clustername, HTTP Port (default is 9200)
Discoveryhost - leave it as default

You should get a installation complete message.

Setting up Elastic Search in People Application

Integration Gateway has to be configured.
Check Domain status (Peopletools->Integration Broker -> Service Operations Monitor->Administration
Configure Nodes all the local nodes
Peopletools -> Integration Broker-> Integration Setup-> Nodes
  Open Local Nodes(PSFT_HR,HRMS) -> Portal tab ->
  change
  Content Uri Text http://server:port/psc/sitename/
  Portal URI text.  http://server:port/psp/sitename/

  Open Node-Anonymous and change the user that has Search Roles and Permission
       Changed it to PS
     
   PeopleTools -> Search Framework->Administration->Search Instance
    Add a new instance
    Search Instance :ELASTIC
    Search Provided :Elasticsearch
 
    Properties
     Hostname:server on which ES is installed.
     Port : Default port 9200 (can be changed)
     ssl option (disabled)
     Username : esadmin / give password
     Proxy Name : people / give password
   
   
   Call Back Properties
     Url: Peoplesoft PIA url using REST Listening Connector.
         http://     User, Password
   
     Verfiy by clicking on Ping,test login, proxy login, Validate.

Error: UserName not defined in database  ( errorLog.html)

Give appropriate user in Anonymous node (Like PS)

   
   

Change Assistant Step Parameters

This a list of parameters that we can use when we are designing a totally customized Change Assistant Template.

Used a customized template in a project where the customer had payroll in 9.2 and Core HR in a lower version. I created a customized template for the upgrade for Core HR. These list came in quite handy so thought of sharing it.

Parameter	Description
#Project=	Used primarily for functions that require a project name, like Build Project, Create Project, and Merge Project. For the Merge Project step type, you can specify two projects separated by a comma. For example, #PROJECT=PRJ656265,PRJ656265_IB_PRE848
#Directory=	Used when you need to run a script that is not located in the one of standard home directories defined for the environment. For example: #Directory=#OutputDirectory
#RunLocation= #FileLocation=	By default the scripts and processes are run on one of the current home directories defined for the environment. However if the script or process needs to run on the old release, you need to specify the old release home directories using the variable #ALL_OLD_PATHS_TGT. Example: #RunLocation=#ALL_OLD_PATHS_TGT #FileLocation=#ALL_OLD_PATHS_TGT
#P1= through #P5=	Used to pass parameters to SQR reports, for example, TEST.sqr. In this case, you would pass the necessary value, such as: #P1=#OutputDirectory
#OutputDirectory=	Used to specify the Output Directory variable that is defined in the Options, Change Assistant, Directories screen.
#NT=	Used for DB2 Command Center, for Non-Terminated SQL Scripts. Note: The #NT parameter applies to DB2 LUW only. It is ignored for DB2 zOS.
#Type=	Enables you to specify the type of record to insert into the project. Choose from the following record types: All Records, Table, View/Query, View/Derived, SubRecord, Stored Procedure, Temporary Table, Dynamic View.
#RCID=	Enables the user to override the run control ID used for Application Engine processes.
#CI =	Connect ID (Used for Data Mover – Tools).
#CW =	Connect password (Used for Data Mover – Tools).
#EXTRACT_DMS=	Extracts DMS export script from file (Used for Data Mover – Tools).
#DBSETUP=	Extracts dbsetup DMS import script from file and database connectivity parameters (Used for Data Mover – Tools).
#UNICODE=	Generates DMS script for UNICODE database (default is NON-UNICODE) (Used for Data Mover – Tools).
#TABLESPACE=	Default tablespace (PTMINITS) (Used for Data Mover – Tools, DB2 LUW, Oracle and Informix only).
#DBSPACE=	Physical dbname.tablespace (PTMINIDB.TABLESPACE) ) (Used for Data Mover – Tools, DB2 z/OS only).
#STOGROUP_TS=	Storage group for tablespace (Used for Data Mover – Tools, DB2 z/OS only).
#STOGROUP_IDX=	Storage group for index (Used for Data Mover – Tools, DB2 z/OS only).
#TABLEOWNER=	Database owner ID (same as sqlid and tableowner) (Used for Data Mover – Tools, DB2 z/OS only).
#INDEXSPC=	Default tablespace (PTMINITS) (Used for Data Mover – Tools, Informix only).
#USE_PRCS_SERVER=	(Used only for ProcessScheduler step types). Enter the name of the Process Scheduler server to run the step. Valid values are SERVER1 or SERVER2, which correlate to the Process Scheduler server definitions you have defined in your upgrade environment.
#PROCESS_TYPE=	(Used only for ProcessScheduler step types). Enter the process type, as defined in Process Scheduler. For example, Application Engine, SQR Report, Data Mover, and so on. Note: This parameter is case sensitive. That is, the value must appear in the exact case as the process type in Process Scheduler. For example, for an Application Engine program, the process type parameter should appear as PROCESS_TYPE=Application Engine, not PROCESS_TYPE=APPLICATION ENGINE.
#PROCESS_NAME=	(Used only for ProcessScheduler step types). Enter the process name, such as DDDAUDIT.
#RUNCONTROLID=	(Used only for ProcessScheduler step types). Enter the appropriate run control ID. Note: If NUM_INSTANCES is greater than one, Change Assistant will append unique sequence numbers to the end of the Run Control ID before requests are submitted to the Process Scheduler. This is required for submitting multiple instances of the same process. If NUM_INSTANCES is equal to one, Change Assistant will NOT append unique sequence numbers to the end of the Run Control ID.
#NUM_INSTANCES=	(Used only for ProcessScheduler step types). Used by Change Assistant to schedule multiple processes through Process Scheduler as individual process requests. However, the actual number of instances simultaneously executed on the Process Server is controlled by the Max Concurrent setting for the process type in the Process Scheduler server definition.
#TST	Used with the PTFTest step to identify the test
#TC	Used with the PTFTest step to identify the test cas.
#PFX	Used with the PTFTest step to identify the test prefix

Variable	Description
#ALL_PATHS_TGT	Uses the path values defined for the target database current environment.
#ALL_PATHS_SRC	Uses the path values defined for the source database current environment.
#ALL_PATHS_ODMO	Uses the path values defined for the copy of demo database current environment.
#ALL_PATHS_PROD	Uses the path values defined for the production database current environment.
#ALL_OLD_PATHS_TGT	Uses the path values defined for the target database old environment.

#PLATFORM_TGT	Uses the platform values defined for the target database current environment.
#PLATFORM_SRC	Uses the platform values defined for the source database current environment.
#PLATFORM_ODMO	Uses the platform values defined for the copy of demo database current environment.
#PLATFORM_PROD	Uses the platform values defined for the production database current environment.

Platform Used in Command Line, Press Enter to collapse

Steps where the command line includes the platform are:

·         ApplicationEngine

·         ApplicationEngineWithRunControl

·         BuildProject

·         CompareAndReport

·         CopyDatabase

·         CopyfromFile

·         CopyToFile

·         CreateProject

·         MergeProject

·         DataMoverBootstrap

·         DataMoverUser

Converting nVision Reports (Tools Upgrade)

After upgrading tools version and moving from msoffice 2003 to 10 we have to convert the nvision reports used in PeopleSoft FSCM.

It is fairly simple and here are the 12 steps .. just follow


1.  Download MS Office conversion utility and install 
      Link to download
      or
      search for nvision MigrationPlanningManager.exe in Google
2.  Take a backup of nvision directory
3.  Navigate to %PS_HOME%\nvision\LAYOUT
4.  Rename all xnv files to xls 
      for /R %1 in (*.xnv) do ren "%1" "%~n1.xls"
5.  Navigate to the directory where you have downloaded the conversion utility
6.  Edit ofc.ini
7.  Change the location to pick xls files: change it to  %PS_HOME%\nvision\LAYOUT in ofc.ini file
8.  Run ofc.exe - this will convert all xls file to xlsx
9.  It will created "Converted" directory under %PS_HOME%
10. Rename %PS_HOME%\nvision\ %PS_HOME\nvision_b4_conversion
11. Copy -R %PS_HOME%\Converted\nvision %PS_HOME%\nvision
12. Rename all xlsx files to xnv
     for /R %1 in (*.xlsx) do ren "%1" "%~n1.xnv"

PTUPGCONVERT FAILS : UPGPTHASH

Run the SYSAUDIT and try to clear them.

Thanks to Smart Panda.

Check for Licensecodes as well depending on the error.

PeopleCode-1:

DELETE FROM PSPCMNAME WHERE NOT EXISTS (SELECT ‘X’FROM PSPCMPROG B WHERE B.OBJECTID1 = PSPCMNAME.OBJECTID1 AND B.OBJECTVALUE1 = PSPCMNAME.OBJECTVALUE1 AND B.OBJECTID2 = PSPCMNAME.OBJECTID2 AND B.OBJECTVALUE2 = PSPCMNAME.OBJECTVALUE2 AND B.OBJECTID3 = PSPCMNAME.OBJECTID3 AND B.OBJECTVALUE3 = PSPCMNAME.OBJECTVALUE3 AND B.OBJECTID4 = PSPCMNAME.OBJECTID4 AND B.OBJECTVALUE4 = PSPCMNAME.OBJECTVALUE4 AND B.OBJECTID5 = PSPCMNAME.OBJECTID5 AND B.OBJECTVALUE5 = PSPCMNAME.OBJECTVALUE5 AND B.OBJECTID6 = PSPCMNAME.OBJECTID6 AND B.OBJECTVALUE6 = PSPCMNAME.OBJECTVALUE6)

PeopleCode-2:

DELETE FROM PSPCMPROG WHERE NAMECOUNT <> 0 AND NOT EXISTS (SELECT ‘X’FROM PSPCMNAME B WHERE PSPCMPROG.OBJECTID1 = B.OBJECTID1 AND PSPCMPROG.OBJECTVALUE1 = B.OBJECTVALUE1 AND PSPCMPROG.OBJECTID2 = B.OBJECTID2 AND PSPCMPROG.OBJECTVALUE2 = B.OBJECTVALUE2 AND PSPCMPROG.OBJECTID3 = B.OBJECTID3 AND PSPCMPROG.OBJECTVALUE3 = B.OBJECTVALUE3 AND PSPCMPROG.OBJECTID4 = B.OBJECTID4 AND PSPCMPROG.OBJECTVALUE4 = B.OBJECTVALUE4 AND PSPCMPROG.OBJECTID5 = B.OBJECTID5 AND PSPCMPROG.OBJECTVALUE5 = B.OBJECTVALUE5 AND PSPCMPROG.OBJECTID6 = B.OBJECTID6 AND PSPCMPROG.OBJECTVALUE6 = B.OBJECTVALUE6)

PeopleCode-3:

UPDATE PSPCMPROG SET NAMECOUNT = (SELECT COUNT(*) FROM PSPCMNAME C WHERE C.OBJECTID1 = PSPCMPROG.OBJECTID1 AND C.OBJECTVALUE1 = PSPCMPROG.OBJECTVALUE1 AND C.OBJECTID2 = PSPCMPROG.OBJECTID2 AND C.OBJECTVALUE2 = PSPCMPROG.OBJECTVALUE2 AND C.OBJECTID3 = PSPCMPROG.OBJECTID3 AND C.OBJECTVALUE3 = PSPCMPROG.OBJECTVALUE3 AND C.OBJECTID4 = PSPCMPROG.OBJECTID4 AND C.OBJECTVALUE4 = PSPCMPROG.OBJECTVALUE4 AND C.OBJECTID5 = PSPCMPROG.OBJECTID5 AND C.OBJECTVALUE5 = PSPCMPROG.OBJECTVALUE5 AND C.OBJECTID6 = PSPCMPROG.OBJECTID6 AND C.OBJECTVALUE6 = PSPCMPROG.OBJECTVALUE6)

PeopleCode-4, PeopleCode-5, PeopleCode-6:

Open the PeopleCode program in Application Designer and correct the invalid reference

PeopleCode-7:

DELETE FROM PSPCMPROG WHERE OBJECTID1 = 104 AND OBJECTID2 = 107 AND OBJECTVALUE2 NOT IN (SELECT APPCLASSID FROM PSAPPCLASSDEFN P WHERE P.PACKAGEROOT = OBJECTVALUE1 AND P.APPCLASSID = OBJECTVALUE2)

Export selective 1000 tables using expdp, include clause

We had discrepancy in PeopleSoft DDDAUDIT. Around 1200 tables were there in the database
but not in the  Application.

We had to drop them ,but had to take a backup of those tables.

Using Data Pump and putting all those 1200 tables in the TABLES= list was a giving me value too large error.

So to resolve

1. Created a table "LIST_OF_TBL' with one column "TNAME"
2. Inserted those 1200 tables names in the above table(step 1 above)
3. created a par file as below

DIRECTORY=dv_exp1
DUMPFILE=dv_exp1.dmp
LOGFILE=dv_exp1.log
INCLUDE=TABLE:"IN (select tname from list_of_tbl)"

That's it , 

expdp sysadm/sysadm parfile=exp1.par

Applying PeopleTools 8.51.11 patch

A. Stop all the application servers, web servers and process schedulers.

B. Follow the below steps

FILE SERVER CHANGES

On windows 2008

1. rename d:\apps\pshome\ d:\apps\pshome_8.51.09

2. copy d:\apps\pshome_8.51.09 d:\apps\pshome_8.51.11

3. Apply 8.51.11 to d:\apps\pshome_8.51.11 directory

4. junction link name directory i.e. junction.exe pshome d:\apps\pshome_8.51.09

(for deleting links use the junction –d link unlike removing link in linux (rm link name))

5. copying jrockit

a. copy Q:\apps\technical\PEOPLETOOLS\pt851\jrockit-jdk1.6.0_26 d:\apps\

b. change the jrmc junction

c. d:\apps\junction –d jrmc

d. d:\apps\junction jrmc d:\apps\jrockit-jdk1.6.0_26

e. (documentation asks use to change D:\oracle\weblogic\common\bin\commEnv.cmd , change java home .. start using junction you created above)

On Linux

6. mv /apps/pshome /apps/pshome_8.51.09

7. cp /apps/pshome_8.51.09 /apps/pshome_8.51.11

8. Apply 8.51.11. to /apps/pshome_8.51.11

9. ln –s directory linkname i.e. ln –s /apps/pshome_8.51.11 pshome

10. copying jrockit (steps for people using links .. way to go)

a. unzip Q:\apps\technical\PEOPLETOOLS\pt851\ p12706519_2814_Linux-x86-64.zip and ftp it linux box (we can choose any method)

b. jrockit-jdk1.6.0_26 (linux) should be placed under /apps

c. change the jrmc link

d. rm jrmc

e. ln –s /apps/ jrockit-jdk1.6.0_26 jrmc

DATABASE CHANGES

On Windows

11. Login to Application Designer

12. Go - Data Mover

13. Run d:\apps\pshome_8.51.11\scripts\ptpatch.dms and exit Data Mover

14. From Application Designer

15. Copy Project from file - d:\apps\pshome_8.51.11\projects\PATCH851

16. IMPORTANT

a. After importing the patch851 project, instructions say to build the project if there are any database changes.

b. I am surprised with this instruction. (so I decided take a call at the end)

c . So in Demo, after looking at the DDD Audit, it is pretty clear that there are no changes to the database as DDD Audit is very clean.

d. Only 1 table and 2 views are there, that are in the database but not defined in the Application Designer.

e. Need to decide what to do with it.

17.

18. Exit from Application Designer

19. Login to Data Mover in boot strap mode

20. Run d:\apps\pshome_8.51.11\scripts\msgtlsupg.dms

21. Clear all the cache directories from Web , Application and process schedulers

a. /apps/user/DEMO/appserv/CACHE

b. /apps/user/DEMO/appserv/prcs/CACHE

c. D:\apps\user\DEMO\appserv\prcs\CACHE

d. D:\apps\user\DEMO\webserv\peoplesoft\applications\peoplesoft\PORTAL.war\ps\cache

22. Redeploy PIA

23. Start all the application servers, process schedulers, webservers.

24. Run all the process of each type.

25. Run DDDAudit, SysAudit and review.