Reading delimited variable length files

open 'file' as 1 for-reading record=2000:vary
read 1 into $rec
unstring $rec by ',' into $var1 $var2 $var3 .........

yesterday in UNIX

date '+%m %d %Y'
{
read MONTH
DAY YEAR DAY=`expr "$DAY" - 1`
case "$DAY" in
0)
MONTH=`expr "$MONTH" - 1`
case "$MONTH" in
0)
MONTH=12
YEAR=`expr "$YEAR" - 1`
;;
esac
DAY=`cal $MONTH $YEAR grep . fmt -1 tail -1`
esac
echo "Yesterday was: $MONTH $DAY $YEAR"

Encyption PGP to add to key ring

1. ftp in ascii format vendor_publickey.asc to /appsdhr/psoft/bea/pgp
2. /usr/local/bin/pgp-6.5.8/pgp -ka vendor_publickey.asc
3. view the 'user id' of the vendor key name in the key ring /usr/local/bin/pgp-6.5.8/pgp -kv
4. Substitute the 'user id' in the command below to accept key (note you need the quotes) /usr/local/bin/pgp-6.5.8/pgp -ks 'user id'
- you will be prompted for password - answer with ...
5. /usr/local/bin/pgp-6.5.8/pgp -e test.txt 'FTP Vendor'

PeopleSoft HR reports

http://download.oracle.com/docs/cd/B28716_01/psft/acrobat/hrr83all-r1001.pdf

Portal Security

http://www.erpassociates.com/peoplesoft-corner-weblog/security/understanding-portal-security-sync.html

PeopleBooks On-Line

http://www.peoplesoftcity.com/

Check if any changes to the record

SavePreChange
If Record.record.IsChanged Then
let record.field = 'Change';
End-If;

Tips for languages, peoplecode, navigation, and email

1) You can use this SQL to find out which languages arealready installed for your installation.select * from PSLANGUAGES where installed = 1
2)You can use this in your peoplecode to determine ifthe current logged in user has role assigned to him.&IND = %Roles.Find("MYROLENAME");IF &IND <> 0 Then /* User has this Role */or /* User does not have this Role */end-ifYou can also use the peoplecode functionIsUserInRole("ROLENAME") to find the same.
3)If you do not see a folder or content Reference (MenuItem) in Left hand side navigation, (Applicable to8.4x ) then you must check to see if the folder orcontent reference is not marked as hidden. Otherreasons could be security. Here is a SQL to find outall the objects that are hidden.To find all the folders which are hidden from PortalNavigation.select * from PSPRSMSYSATTRVL where portal_name ='EMPLOYEE' and PORTAL_ATTR_NAM ='PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'F' selecta.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_labelfrom PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c wherea.portal_name = 'EMPLOYEE' and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and b.portal_Reftype = 'F' and a.portal_name =b.portal_name and a.portal_objname = b.portal_objnameand b.portal_name = c.portal_nameand b.portal_prntobjname = c.portal_objname To find all the content references which are hiddenfrom Portal Navigation. select * from PSPRSMSYSATTRVL where portal_name ='EMPLOYEE' and PORTAL_ATTR_NAM ='PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'C' selecta.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_labelfrom PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c wherea.portal_name = 'EMPLOYEE' and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and b.portal_Reftype = 'C' and a.portal_name =b.portal_name and a.portal_objname = b.portal_objnameand b.portal_name = c.portal_nameand b.portal_prntobjname = c.portal_objname Replace EMPLOYEE with Your portal name. Some of theOther names are. CUSTOMERDEMOSITEEMPLOYEEMOBILEPORTALPS_SITETEMPLATESUPPLIER Portal Content Reference/folder Attributes are storedin PSPRSMSYSATTR PSPRSMSYSATTRVL
4)Tuesday, February 20, 2007Troubleshoot Workflow EMAIL (SMTP Server) issues. Many time End users get an Error message : Unable toSend Email, Invalid ID, while saving the page. Thisusually means some kind of issue while sending emailfrom your appserver or batchserver using the SMTPServer. The error message is not correct and to getthe exact reason, why send email is failing, you needto add/Update the following in your appserverconfiguration (PSAPPSERV.CFG) or batch serverConfiguration (PSPRCS.CFG) under SMTP Settings.SMTPTrace=11 means enabled, 0 - Disabled. This setting issometimes not present in PSPRCS.CFG. You have to addit manually opening the file. This setting is dynamicand does not require reboot of the Server, which isvery good for troubleshooting in productionenvironments. Once turned on, it will generate Trace file SMTP.LOGin LOGS Folder under $PS_HOME/tools/appserv/DOMAIN forAppserver and $PS_HOME/tools/appserv/prcs/DOMAIN forBatchserver.Open SMTP.LOG and fix the issues logged in it. Thiswill make your users to save the page.

Trace Peoplecode & SQL

http://notepad-plus.sourceforge.net/uk/download.php3)Below method from customer connection gives a moreprecise trace..1. Navigate to the page where the problem exists andbe ready to click on the button. In this example, wewant to get an Online Trace for the time we Add a newJournal.2. In the right corner of your screen, click on 'NewWindow' to open a new window. On the new window,navigate to the following menu: PeopleTools >Utilities > Debug. Under this menu, there are severaloptions for the tracing:-Trace SQL-TracePeopleCode-Trace Page.3. For our example, we will create an online trace forSQL as we want to see what the table is being selectedand updated. In most cases, selecting 'Trace SQLStatement' and 'Trace SQL Bind' will be sufficient.For PeopleCode, usually, all the 'Show' options arethe selection that we need. Save the page, and do notclose it.4. Now, check for the existing trace file. The tracewill be created in Logs folder on your ApplicationServer.[PS_HOME]\appserv\[domain]LOGS. For example:E880G71P\appserv\E880G71P\LOGS.5. The online trace filename will be similar to [username]_[machine name].tracesql. For example:VP1_FLINAN12345.tracesql. If you find an existing one,rename or delete it.6. Now go back to your original page. And click on'ADD' button.7. When the process is finished, it should create thetrace file on the Logs directory. Copy the file toyour workstation as it will be appended with newinformation everytime you turn on the online trace.8. Don't forget to turn off the tracing.9. HAPPY TRACING!

do not want multiple inclusion of security join

http://hotjobs.yahoo.com/job-JPB26NW55EIAfter upgrading from 8.3 to 8.9, many customers sawthat their Query security joins started causingperformance issues. 1)Prior to 8.44, the security record was joined oncefor two records with the same one. This caused data tobe missing in some circumstances. If you do not wantmultiple inclusion of security join (insert intopsversion(Iobjecttypename, version) values('qryselfj',1))

Trace in PeopleSoft COBOL & AppEngine

Action Requested: FYILink: http://www.peoplesoft.com/psp/portprd/CUSTOMER/CRM/c/CALLCENTER.RC_CASE_MAP.GBL?page=RC_CASE_NOTE&BUSINESS_UNIT=GBLSC&CASE_ID=4113114Message Text:Note Created Date : 24/01/2006 7:41PMNote Summary : Email to customerNote Detail:SergeyI have attached two resolutions for you to familiarize when setting up a trace file. If the issue resurfaces again, try and set a trace and then we could try and isolate the issue by looking at the insert statement from the trace file. I hope this helps. Since the issue did not resurface in your current payprocess and as per your suggestion iam closing this case at this point of time. Resolution 694679 -- TRACE & LOG FILES FOR COBOL, REMOTE CALL & APPLICATION ENGINE=> ISSUE:FINDING TRACE & LOG FILES FOR COBOL, REMOTE CALL & APPLICATION ENGINEANSWERS:=> APPLICATION ENGINEApplication Engine Execution Log file:Client:%TEMP%\PS\DBName\AEADHOC_.log%TEMP%\PS\DBName\PTPEMAIN_.log%TEMP%\PS\DBName\_.logServer:$PS_HOME/appserv/prcs/DBName/logs/AEADHOC_.log$PS_HOME/appserv/prcs/DBName/logs/PTPEMAIN_.log$PS_HOME/appserv/prcs/DBName/logs/_.logApplication Engine SQL Trace:This trace file is generated by the Application Engine at your request from Go, PeopleTools, Application Engine, Use, Application Engine, Application, Update/Display. Setting TRACE to an option other than OFF will create a trace file during your test run. It generates a trace of the sequence of application SQL your application used.Before you can view trace files on the client in the Application Engine viewer, you must set the AETRACE environment variable to the client trace file location. In general you will set AETRACE=%TEMP%\PS\DBName where DBName is the name of the database you are working with. AETRACE should be defined as an NT System Environment or within the AUTOEXEC.BAT for Windows 95/98. The Application Engine SQL Trace file will have the following naming convention: .aetThe Application Engine viewer can only view the Application Engine SQL Trace files on the Client. If you're running your application on the Server however, these SQL Trace files exist on the Server and not on the Client. On the Unix Server, trace files will reside in $PS_HOME/appserv/prcs/DBName/logs.COBOL SQL Trace:On the Client, check EVERYTHING on the left hand side under SQL Trace on the Trace tab of the Configuration Manager (except for the Sybase Information). On the Unix Server, set TraceSQL to 255 in the $PS_HOME/appserv/prcs/DBName/psprcs.cfg file (the configuration file used by PSADMIN) and then re-boot the Process Scheduler. The COBOL SQL Trace file will reside in the directory structure below with the following naming convention:Client:%TEMP%\PS\DBName\cobsql_ae_.trcServer:$PS_HOME/appserv/prcs/DBName/logs/cobsql_ae_.trc=> REMOTE CALL:Remote Call Redirect Output:If running a Remote Call process in TWO TIER, on the Remote Call tab of the Configuration Manager is a "Remote Call Redirect Output" flag. Check the flag and rerun the Remote Call process. This will create two files in your %TEMP% directory, one ending with .out, the other with .err (program-name_oprid.out and program-name_oprid.err). The program-name_oprid.out file will more than likely contain the reason why the process didn't complete successfully.If running a Remote Call process in THREE TIER, the COBOL process runs on the Application Server. Therefore, the same flag is set, but this time in the Application Server configuration file ($PS_HOME/appserv/Domain-Name/psappsrv.cfg). The parameter RCCBL Redirect (Remote Call child process output redirection) is defaulted to 0. If it is changed to 1 (RCCBL Redirect=1) and the AppServer re-booted, the same two files will be written to the Application Server logs directory ($PS_HOME/appserv/Domain-Name/logs), providing additional information as to why the Remote Call process failed.COBOL SQL Trace:To generate a cobsql trace of the COBOL program in TWO TIER, check EVERYTHING on the left hand side under SQL Trace on the Trace tab of the Configuration Manager (except for the Sybase Information). Also, check the Redirect Output box on the Process Scheduler tab of the Configuration Manager too. The cobsql trace file will reside under %TEMP%\PS\DBName in the format cobsql_program-name_datetime.trc.To generate a cobsql trace in THREE TIER, set TraceSQL equal to 255 in the $PS_HOME/appserv/Domain-Name/psappsrv.cfg (the configuration file used by PSADMIN). You must also add a section for the Process Scheduler. For example:[Process Scheduler]Log Directory=%PS_SERVDIR%/logsThen re-boot the AppServer. The cobsql trace file will reside under $PS_HOME/appserv/Domain-Name/logs in the format cobsql_program-name_datetime.trc.=> COBOLCOBOL Execution Log File:On the Server, the COBOL Log file will reside under $PS_HOME/appserv/prcs/DBName/logs in the format PROGRAM-NAME_instance#.log.On the Client using PT7.5x, check the Redirect Output box on the Process Scheduler tab of the Configuration Manger. The COBOL Log file will reside under %TEMP%\PS\DBName in the format PROGRAM-NAME_instance#.log.On the Client using PT7.0x and Micro Focus v4.0, make sure the environment variable COBSW is set to +L,+S5. If using NetExpress, please refer to Incident #T-RBOLEL-9D4QY / NetExpress PT7.03, 7.04 and 7.05 doesn't have a way to keep the MS-DOS window open. This is targeted to be resolved in PT7.06. Workarond is to run the COBOL program from MS-DOS and pipe the output to a file.COBOL SQL Trace:On the Client, check EVERYTHING on the left hand side under SQL Trace on the Trace tab of the Configuration Manager (except for the Sybase Information). On the Unix Server, set TraceSQL to 255 in the $PS_HOME/appserv/prcs/DBName/psprcs.cfg file (the configuration file used by PSADMIN) and then re-boot the Process Scheduler. The COBOL SQL Trace file will reside in the directory structure below with the following naming convention:Client:%TEMP%\PS\DBName\cobsql__.trcServer:$PS_HOME/appserv/prcs/DBName/logs/cobsql__.trcADDITIONAL RESOURCES:Resolution 689883 -- Preliminary Remote Call questions -- What customers should do before calling the GSCResolution 42114 -- Where do Remote Call processes run? How can a customer determine where a Remote Call process runs?Resolution 43481 -- INFO: Online Checks -- Where does the process run? Where does the output go? (Version 7.x+)Resolution 39654 -- INFO: Where is the Log file for Online Check error messages? How to debug Online Check errors?Resolution 610752 -- Remote Call Error: "Could not open file [path]\rmtcall_out.nnn to read child return data" -- MASTER Resolution=================================================================================Resolution #44268 How to create a COBOL trace that shows dynamic SQL when executing on UNIX serverYou are able to trace all SQL activity generated by PeopleSoft COBOL programs executed on a Unix server.The COBOL SQL trace on the server is your best tool to figure out why a program failed if: - you are running a COBOL program on a Unix server - you know the COBOL program has started - the COBOL program fails for some reason=> Enabling COBOL SQL trace on the Unix server: You can enable COBOL tracing on Unix servers by setting a variable in the pstools.ini file on the server. On the Unix server, open (edit) the file $PS_HOME/bin/pstools.ini. Under the section [PSTOOLS], you will find a line saying: TraceSql=0. Comments above this line explain how you can set TraceSql to a value other than zero in order to generate COBOL SQL tracing information.=> Location of COBOL SQL trace output file on the Unix server:On Unix machines, the COBOL SQL trace files are written to the $PS_HOME directory in the subdirectory log/. The trace file will start with 'COBSQL_' and have an extension of '.trc'. After 'COBSQL_', the file name may have the program's name and/or the process instance.Here is a sample file name with the process instance: $PS_HOME/log/FSDMO/COBSQL_1914.trc=> LOG files -- Naming convention and where to find them:In addition to the creation of a trace (.trc) file, there is a log file created on the Unix server whenever you execute a PeopleSoft COBOL process. The log file contains the processing status and program error/abend messages that are normally written by the program, even when tracing is not turned on. You should find the log file in the following directory: $PS_HOME/log/. The log file will have the following naming convention: _.log Here is a sample file name for the AP posting program: /apps/psoft/log/FSDMO/APPPVCHR_12638.logFor more information on COBOL SQL tracing on the server, see the following section in the PeopleTools 7.x PeopleBook: Administrative Tools Performance Tuning Batch Program Monitoring and Tuning Utilities SQL Trace=> Additional Resources: Customer Connection > Support > Tips & Techniques > Keyword COBOL: GSC COBOL Answer Book for PeopleTools 6, 7 & 7.5 Tracing in PeopleSoft for PeopleTools [your release]Santhosh PintoORACLE Global Support Center

Query Migration SQL

-- @C:\QU.SQL
-- Run all the script individually in HR83 environment before running make sure database link should exist.
SET ECHO ON FEEDBACK ON
DELETE FROM PSQRYBIND WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYCRITERIA WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYDEFN WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYEXPR WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYFIELD WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYRECORD WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYLINK WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYFIELD WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
DELETE FROM PSQRYSELECT WHERE UPPER(QRYNAME) = 'CDC_ZJ_2ND_YEAR_TOT';
INSERT INTO PSQRYBIND
SELECT
OPRID
,QRYNAME
,BNDNAME
,BNDNUM
,FIELDNAME
,HDGTYPE
,HEADING
,FIELDTYPE
,LENGTH
,DECIMALPOS
,FORMAT
,EDITTABLE
,USECOUNT
,USEEDIT
FROM PSQRYBIND@fpra WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
where G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/
INSERT INTO PSQRYCRITERIA SELECT OPRID
,QRYNAME
,SELNUM
,CRTNUM
,COMBTYPE
,NEGATION
,LPARENLVL
,LCRTSELNUM
,LCRTFLDNUM
,CONDTYPE
,EXPRTYPE
,R1CRTSELNUM
,R1CRTFLDNUM
,R1CRTEXPNUM
,R2CRTSELNUM
,R2CRTFLDNUM
,R2CRTEXPNUM
,RPARENLVL , 0 FROM PSQRYCRITERIA@FPRA
WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@FPRA G
WHERE
G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/

INSERT INTO PSQRYDEFN
SELECT OPRID, QRYNAME, DESCR, VERSION,QRYTYPE, SELCOUNT, EXPCOUNT, BNDCOUNT,QRYVALID,SYSDATE,'BFRIDLEY',
' ',' ',sysdate,' ',sysdate,' ', ' ', ' ',DESCR
FROM PSQRYDEFN@fpra
WHERE QRYNAME in (Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
WHERE G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/
DECLARE
CURSOR TEMP_CURSOR IS SELECT *
FROM PSQRYEXPR@fpra
WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
WHERE G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
;
P TEMP_CURSOR%ROWTYPE
;
BEGIN
OPEN TEMP_CURSOR
;
LOOP
FETCH TEMP_CURSOR INTO P
;
EXIT WHEN TEMP_CURSOR%NOTFOUND
;
INSERT INTO PSQRYEXPR (
OPRID,
QRYNAME,
EXPNUM,
FIELDTYPE,
LENGTH,
DECIMALPOS,
USECOUNT,
EXPRESSIONTEXT)
VALUES(
P.OPRID,
P.QRYNAME,
P.EXPNUM,
P.FIELDTYPE,
P.LENGTH,
P.DECIMALPOS,
P.USECOUNT,
P.EXPRESSIONTEXT)
;
COMMIT
;
END LOOP
;
CLOSE TEMP_CURSOR
;
END
;
/

INSERT INTO PSQRYFIELD
SELECT
OPRID
,QRYNAME
,QRYFLDNAME
,SELNUM
,FLDNUM
,RECNAME
,FIELDNAME
,FLDRCDNUM
,FLDEXPNUM
,HDGTYPE
,HEADING
,COLUMNNUM
,GROUPBYNUM
,ORDERBYNUM
,ORDERBYDIR
,TTLTYPE
,SUBTTLNUM
,USECOUNT
,XLATTYPE
,XLATEXPRTYPE
,XLATFLDNUM
,XLATEXPNUM
,AGGREGATEFUNC
FROM PSQRYFIELD@fpra
WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
WHERE G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/

INSERT INTO PSQRYLINK SELECT * FROM PSQRYLINK@fpra
WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
WHERE G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/
INSERT INTO PSQRYRECORD SELECT * FROM PSQRYRECORD@fpra
WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
WHERE G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/
INSERT INTO PSQRYSELECT SELECT * FROM PSQRYSELECT@fpra
WHERE QRYNAME in (
Select DISTINCT G.QRYNAME
FROM PSQRYRECORD@fpra G
WHERE G.QRYNAME = 'CDC_ZJ_2ND_YEAR_TOT')
/
COMMIT
/

Pathway to Fusion 8-9 Presentation

http://www.und.nodak.edu/dept/cndtrain/ConnectND%20HEUG/HRMS/8-9-upgrade.pdf

Explain Plan

rem
-----------------------------------------------------------------------
rem Filename: x_plan.sqlrem Purpose: Explain SQL Execution plan
rem Date: 12-Apr-1998r
em Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
DELETE FROM plan_table WHERE statement_id = 'XXX';COMMIT;
EXPLAIN PLAN SET STATEMENT_ID = 'XXX'
FOR
my sql
/
-- Oracle 9.2 and above:
set linesize 132
SELECT * FROM TABLE(dbms_xplan.DISPLAY('PLAN_TABLE','XXX'));
set doc off

Portal Solution Upgrade 8.8-9.0

http://download.oracle.com/docs/cd/B40041_01/psft/acrobat/ups88to90.pdf

DECODE function

http://techonthenet.com/oracle/functions/decode.php

PeopleSoft Blogs

http://www.peoplesoftguys.com/category/upgrade/
http://peoplesoftexperts.blogspot.com
list http://ket000.tadalist.com/lists/public/217073
http://www.passportgeek.com/?q=forum/52
http://ideatec.blogspot.com/
http://www.vijaymukhi.com/pcode/pindex.htm
http://www.geekinterview.com/articles/peoplesoft-tutorials,-peoplesoft-online-training,-peoplesoft-articles.html