open 'file' as 1 for-reading record=2000:vary
read 1 into $rec
unstring $rec by ',' into $var1 $var2 $var3 .........
Sunday, December 30, 2007
Wednesday, December 26, 2007
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"
{
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'
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
Check if any changes to the record
SavePreChange
If Record.record.IsChanged Then
let record.field = 'Change';
End-If;
If Record.record.IsChanged Then
let record.field = 'Change';
End-If;
Tuesday, December 11, 2007
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.
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
/
-- 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
/
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
-----------------------------------------------------------------------
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
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
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
Friday, November 30, 2007
Master resolution for Tax Update 07-E 8.8
Especial Important:
1) Workaround for Error - failure during Copy Database process due to index dropped.
2) Fix for tax960fd.sqr and W2addr.sqc
3) Run DMS after w2 forms created for 2007
Tax Update 07-E, Date of Availability on Customer Connection for CommercialVersion of HCM Monday, October 29th, 2007 (Close of business 5:00 PST).The intent of this document is to describe solutions for problems orquestions related to Tax Update 07-E. This solution applies to customerson PeopleSoft Commercial, Federal, E&G, Public Sector Payroll and CampusSolution Release 8.X & 9.0. This is a living document and will be updatedas new solutions are created; please review on a regular basis.PeopleSoft Commercial Payroll Tax Update 07-F is currently tentativelytargeted for availability Monday, November 5th. This solution will beupdated if the 07-F target availability date changes.PeopleSoft GSC will not be able to provide any information until the TaxUpdate gets posted, because this information is considered confidential andsubject to change until posted.FYI, for more info please seeSOLUTION 201044165: HRMS 8.01(SP1) AND Tax Updates.Tax Update 07-B was the last tax update for Commercial version of HRMS 8.01(SP1). As of March 31, 2007 support for HRMS 8.0 SP1 Commercial version hasofficially retired. Oracle has offered a few of its Premier SupportCustomers a fee-based extension program named "North American Payroll TaxUpdate Package". This Supplemental Support will extend support so theseCustomers may download new tax updates from the Customer Connectionwebsite.For HRMS 8.01(SP1) Student Admin version, Tax Update 08-D will be the lasttax update.As you know that Student Admin version of tax updates are usually posted 2weeks after the posting of US Commercial version Tax Update._____________________________________________________________ICE Report ID Release Resolution ID Bundle IDTranslation ID1684509000 8.3 SP1 726636 81081684509000 8.8 SP1 726637 81091684509000 8.9 72663881101684509000 9.0 7266398111_____________________________________________________________PREREQUISITES:HRMS 8.3(SP1)The first Tax Update for HCM 8.30 was 01-F. As delivered "out of the box"Release 8.30 included tax updates up through Tax Update 01-E. Clients needto apply from Tax update 01-F through 06-B, as a prerequisite for this TaxUpdate 06-C.HCM 8.3(SP1) has tax update 03-B included and you need to applyfrom tax update 03-C through 07-D before applying 07-E.HRMS 8.8(SP1)The first Tax Update for HCM 8.80 was 02-F. As delivered "out of the box"Release 8.80 includes tax updates up through Tax Update 02-E. Clients needto apply from Tax update 02-F through 06-C as a prerequisite for this TaxUpdate 06-D. For HCM 8.81(SP1) tax update 03-F was included in it and Taxupdate 03-G was the first tax update and customers need to apply from 03-Gthrough 07-D before applying tax update 07-E.HCM 8.9The first Tax Update for HCM 8.9 was 04-E. As delivered "out of the box"Release 8.9 includes tax updates up through Tax Update 04-D. Customers needto apply Tax update 04-E through 07-D as a prerequisite for this Tax Update07-EHCM 9.0As delivered "out of the box" Release 9.0 includes through tax update 06-E.The first tax update for HCM 9.0 is 06-F. Customers need to start applyingfrom tax update 06-F onwards. Customers need to apply Tax update 06-Fthrough 07-D as a prerequisite for this Tax Update 07-E._____________________________________________________________DOCUMENTATION ISSUES:ISSUE #1:I have downloaded Tax Update 07E for HRMS 8.3 and found that one Cobolprogram (PSPTBUPD.CBL) is missing. On Page 19 of document"TAX07E831-notes", there are 6 programs that have been changed for this TaxUpdate. But there are only 5 programs supplied for this Tax Update.SOLUTION #1:This is a documentation error. PSPTBUPD.cbl was not redelivered for 8.3SP1.ISSUE #2Year End Processing Guide 2007Setting Up Tax Reporting Tables, Page 4IMPORTANT: You must complete this manual step before proceeding to useChange Assistant to apply the DataMover scripts upd726638_02.dms deliveredwith Tax Update 07-E.Enter the Tax Form Identification W-2 and the Effective Date of 01/01/2006on the Tax Form Definition search page and select Search. Add a new row inthe table that duplicates all of the 2006 entries by selecting the Add (+)button in the Effective Date Group Box. Change the Effective Date for thisnewly inserted row to 01/01/2007. Save this new entry. Then follow thissame procedure for Tax Form Identifications W-2c, W-2AS, W-2cAS, W-2GU,W-2cGU, W-2PR, W-2cPR, W-2VI, and W-2cVI. Save these new entries.SOLUTION #2Correction on DATE: The Effective Date should be 01/01/2006 instead of01/01/2005.ISSUE #3In Tax Update 07-E release notes documentation, Page 4 it showsSteps 2 10 are for ALL PeopleSoft Payroll UsersBut there are only 8 steps.SOLUTION #3Yes, it is documentation typo and there should be only 8 steps and not 10steps._____________________________________________________________APPLICATION UPGRADE ISSUES:ISSUE #1Customer applied Tax updated - 07E - applied into demo. No issue -Getting Unique constraint error while copying from Demo to Dev. Copyproject - Illegal uncommitted SQL cursor detected. Closing down all windowsand reboot.=== Error Message ========Records Application Upgrade Copy started: 2007-10-29-15.42.06SQL error. Stmt #: 3371 Error Position: 0 Return: 805 - ORA-00001: uniqueconstraint (SYSADM.PS_PSKEYDEFN) violatedINSERT INTO PSKEYDEFN (RECNAME, INDEXID, KEYPOSN, FIELDNAME, ASCDESC)VALUES (:1,:2,:3,:4,:5)System Error : RecordsError - failure during Copy Database process.SOLUTION 201054508; EPY: When Applying 07-E getting error 805 - ORA-00001:(SYSADM.PS_PSKEYDEFN)SOLUTION #1PeopleSoft was unable to replicate this issue using PT8.48.The following tests that were ran:Copied Tax Update 07-E project from file to an DEMO database : No error.Copied Tax Update 07-E project from an DEMO database to DEV database: Noerror.Ran Alter Tables/Create Indexes for record TAXFORM_ERN after applying TaxUpdate 07-E project: PS0TAXFORM_ERN index dropped successfully.Prior to 07-E, record TAXFORM_ERN had 2 indexes, i.e. Key and Index 0.Applied software update using Change Assistant: No error.Record TAXFORM_ERN had 2 system-generated indexes,i.e. Key index and 0 index, prior to Tax Update 07-E. After 07-E andAlter/Create Index of record TAXFORM_ERN, it should have only one index,i.e. the Key index.WORK AROUND:The error in the tax 07-E migration is caused by the change done to fieldBOX in table PS_TAXFORM_ERN. The tax update removed the Alternate SearchKey attribute on this filed. The migration is trying to add duplicateindexes, when it should delete an index. The index is a PeopleSoftapplication index, not a database index.1) Unchecking the alternate search key check box in the record fieldproperties of the Box Field on the TAXFORM_ERN table in the Target systembefore migrating the project seemed to work.2) This problem seems to occur when the action "copyprop" is flagged for"Upgrade" and an index is being deleted. Turn off the "Upgrade" flag for"copyprop" for TAXFORM_ERN and the project should copy successfully. Andthen turn the action flag back on for "copyprop" and it should then becopied without error since the index definition was removed from the targetbased on the field changes to the record whichwere migrated to the target in the first pass.3) Try dropping the index on PSKEYDEFN then copy the project. Recreate the2 indexes on the table, The unique index PS_PSKEYDEFN should blow up onthe recreate, if there are any duplicates.We noticed the following after the update. Before tax update tableSYSADM.PS_TAXFORM_ERN has two indexes. After the update the indexSYSADM.PS0TAXFORM_ERN was dropped.CREATE UNIQUE INDEX SYSADM.PS_TAXFORM_ERN ON SYSADM.PS_TAXFORM_ERN(TAXFORM_ID, EFFDT, BOX, ERNCD) ;CREATE INDEX SYSADM.PS0TAXFORM_ERN ON SYSADM.PS_TAXFORM_ERN(BOX, TAXFORM_ID, EFFDT, ERNCD) ;KEYWORDS: 07E, 07-E, PS_TAXFORM_ERN, PSKEYDEFNEPY: Tax Update 07-E Master Solution for 8.X & 9.0 HCM - COM, E&G, PUB, CS& FED Details: SOLUTION 201049812: EPY 07E - Tax Update 07-E Master Solutionfor 8.X & 9.0 HCM ---Commercial, Public Sector, Campus Solution, FederalAND E & G (FAQ).SPECIFIC TO: Enterprise, North American Payroll, Release { 8.3(SP1), 8.8(SP1), 8.9 & 9.0}._____________________________________________________________TABLE ADDITIONS/UPDATES ISSUES:NONE Reported_____________________________________________________________COBOL & DMS ISSUES:PLEASE, when you apply tax updates do GLOBAL recompile of ALL the COBOLprograms & reload ALL your stored statements.Solution 200730878: Why run STOREPT, STOREPAY, STOREBAS, STOREHR, STOREPYI,etc.Solution 43949: Why recompile ALL the COBOL?NONE Reported_____________________________________________________________SQR ISSUES:ISSUE #1Tax960fd.sqr that was delivered as part of 07-E is failing with followingerror: (SQR 4713) Cannot open the #INCLUDE file: 'W2addr.sqc' (2): No such file or directoryThis is happening because of the uppercase letter in 'W2addr.sqc' used inthe sqr include statement, and will affect environments where filenames arecase sensitive, e.g.: UNIX. The file w2addr.sqc was delivered with 07E.This affects all releases.SOLUTION #1Incident created to address this: 1713032000 - Tax update 07-Etax960fd.sqr erroring out on include of W2addr.sqc The workaround, is tocorrect the typo and change the W2addr.sqc reference in the INCLUDEstatement to all lowercase: w2addr.sqcISSUE #2Issue with TAX960FD.SQR using new w2addr.sqc.Getting error: (SQR 5528) DB2 SQL OPEN/EXECUTE error -301 in cursor 43:SQL0301N The value of input host variable or parameter number "" cannot beused because of its data type. SQLSTATE=42895Cursor #43: SQL = SELECT YE.ADDRESS1, YE.ADDRESS2 FROM PS_YE_DATA YE,PS_W2_COMPANY W2C WHERE W2C.W2_REPORTING_CO = ? AND W2C.CALENDAR_YEAR = ? AND YE.COMPANY = W2C.COMPANY AND YE.EMPLID = ? AND YE.CALENDAR_YEAR = W2C.CALENDAR_YEAR AND YE.SEQUENCE_NUMBER =1Compiles = 2Executes = 0Rows = 0SOLUTION #2We have created a priority 2 "production effected" ICE Report ID 1719071000for addressing this issue and the fix will be delivered soon along with TaxUpdate 07-F.WORK AROUND:With in your w2addr.sqc for this procedure Get-W2-AddressAND W2C.CALENDAR_YEAR = $W2.Balance_YearAND W2C.CALENDAR_YEAR = 2007 <------Replace $W2.Balance_Year with2007_____________________________________________________________Previous Tax update Master Resolutions in Year 2007SOLUTION 201028179: EPY 8.x: Tax Update 07-A Master Solution for 8.X HCM -COM, E&G, PUB, CS & FEDSOLUTION 201038689: EPY 07B - Tax Update 07-B Master Solution for 8.X & 9.0HCMSOLUTION 201041376: EPY 07C - Tax Update 07-C Master Solution for 8.X & 9.0HCMSOLUTION 201048574 : EPY 07D - Tax Update 07-D Master Solution for 8.X &9.0 HCM____________________________________________________________RELATED RESOURCES:Solution 14138 Tax Update Procedures: How To Apply Tax Updates(Master Resolution)Solution 303939: What are the different ". DMS" files deliveredwith tax updates, and what do we doSolution 41450 All About Stored StatementsSolution 698826 Invalid or missing PeopleCode program then givesname of (component).Solution 200742195: Tax Updates for HRMS 8.3 after service pack 1Solution 200740082: Master FAQ document for HRMS 8.3 Service Pack 1.Solution 705695: PeopleTools release required for product patchesSolution 707920: Messages Catalog entries listed in fix or taxupdate projects do not get copiedSolution 201001870: EPY: 8.9 MP1 Delta Documentation Payroll for NorthAmericaSolution 201033943; EHCM: HCM 9.0 & Tax Updates?Solution 201030656; EHCM: What are the Upgrade paths to HCM 9.0?Solution 699259: Outcome of Tax Update Survey - What will beincluded in Tax Updates?You can find Tax Update Schedule info on Customer Connectionhttp://www.peoplesoft.com/corp/en/support/roadmap/taxSchedule.asp?prod=All
1) Workaround for Error - failure during Copy Database process due to index dropped.
2) Fix for tax960fd.sqr and W2addr.sqc
3) Run DMS after w2 forms created for 2007
Tax Update 07-E, Date of Availability on Customer Connection for CommercialVersion of HCM Monday, October 29th, 2007 (Close of business 5:00 PST).The intent of this document is to describe solutions for problems orquestions related to Tax Update 07-E. This solution applies to customerson PeopleSoft Commercial, Federal, E&G, Public Sector Payroll and CampusSolution Release 8.X & 9.0. This is a living document and will be updatedas new solutions are created; please review on a regular basis.PeopleSoft Commercial Payroll Tax Update 07-F is currently tentativelytargeted for availability Monday, November 5th. This solution will beupdated if the 07-F target availability date changes.PeopleSoft GSC will not be able to provide any information until the TaxUpdate gets posted, because this information is considered confidential andsubject to change until posted.FYI, for more info please seeSOLUTION 201044165: HRMS 8.01(SP1) AND Tax Updates.Tax Update 07-B was the last tax update for Commercial version of HRMS 8.01(SP1). As of March 31, 2007 support for HRMS 8.0 SP1 Commercial version hasofficially retired. Oracle has offered a few of its Premier SupportCustomers a fee-based extension program named "North American Payroll TaxUpdate Package". This Supplemental Support will extend support so theseCustomers may download new tax updates from the Customer Connectionwebsite.For HRMS 8.01(SP1) Student Admin version, Tax Update 08-D will be the lasttax update.As you know that Student Admin version of tax updates are usually posted 2weeks after the posting of US Commercial version Tax Update._____________________________________________________________ICE Report ID Release Resolution ID Bundle IDTranslation ID1684509000 8.3 SP1 726636 81081684509000 8.8 SP1 726637 81091684509000 8.9 72663881101684509000 9.0 7266398111_____________________________________________________________PREREQUISITES:HRMS 8.3(SP1)The first Tax Update for HCM 8.30 was 01-F. As delivered "out of the box"Release 8.30 included tax updates up through Tax Update 01-E. Clients needto apply from Tax update 01-F through 06-B, as a prerequisite for this TaxUpdate 06-C.HCM 8.3(SP1) has tax update 03-B included and you need to applyfrom tax update 03-C through 07-D before applying 07-E.HRMS 8.8(SP1)The first Tax Update for HCM 8.80 was 02-F. As delivered "out of the box"Release 8.80 includes tax updates up through Tax Update 02-E. Clients needto apply from Tax update 02-F through 06-C as a prerequisite for this TaxUpdate 06-D. For HCM 8.81(SP1) tax update 03-F was included in it and Taxupdate 03-G was the first tax update and customers need to apply from 03-Gthrough 07-D before applying tax update 07-E.HCM 8.9The first Tax Update for HCM 8.9 was 04-E. As delivered "out of the box"Release 8.9 includes tax updates up through Tax Update 04-D. Customers needto apply Tax update 04-E through 07-D as a prerequisite for this Tax Update07-EHCM 9.0As delivered "out of the box" Release 9.0 includes through tax update 06-E.The first tax update for HCM 9.0 is 06-F. Customers need to start applyingfrom tax update 06-F onwards. Customers need to apply Tax update 06-Fthrough 07-D as a prerequisite for this Tax Update 07-E._____________________________________________________________DOCUMENTATION ISSUES:ISSUE #1:I have downloaded Tax Update 07E for HRMS 8.3 and found that one Cobolprogram (PSPTBUPD.CBL) is missing. On Page 19 of document"TAX07E831-notes", there are 6 programs that have been changed for this TaxUpdate. But there are only 5 programs supplied for this Tax Update.SOLUTION #1:This is a documentation error. PSPTBUPD.cbl was not redelivered for 8.3SP1.ISSUE #2Year End Processing Guide 2007Setting Up Tax Reporting Tables, Page 4IMPORTANT: You must complete this manual step before proceeding to useChange Assistant to apply the DataMover scripts upd726638_02.dms deliveredwith Tax Update 07-E.Enter the Tax Form Identification W-2 and the Effective Date of 01/01/2006on the Tax Form Definition search page and select Search. Add a new row inthe table that duplicates all of the 2006 entries by selecting the Add (+)button in the Effective Date Group Box. Change the Effective Date for thisnewly inserted row to 01/01/2007. Save this new entry. Then follow thissame procedure for Tax Form Identifications W-2c, W-2AS, W-2cAS, W-2GU,W-2cGU, W-2PR, W-2cPR, W-2VI, and W-2cVI. Save these new entries.SOLUTION #2Correction on DATE: The Effective Date should be 01/01/2006 instead of01/01/2005.ISSUE #3In Tax Update 07-E release notes documentation, Page 4 it showsSteps 2 10 are for ALL PeopleSoft Payroll UsersBut there are only 8 steps.SOLUTION #3Yes, it is documentation typo and there should be only 8 steps and not 10steps._____________________________________________________________APPLICATION UPGRADE ISSUES:ISSUE #1Customer applied Tax updated - 07E - applied into demo. No issue -Getting Unique constraint error while copying from Demo to Dev. Copyproject - Illegal uncommitted SQL cursor detected. Closing down all windowsand reboot.=== Error Message ========Records Application Upgrade Copy started: 2007-10-29-15.42.06SQL error. Stmt #: 3371 Error Position: 0 Return: 805 - ORA-00001: uniqueconstraint (SYSADM.PS_PSKEYDEFN) violatedINSERT INTO PSKEYDEFN (RECNAME, INDEXID, KEYPOSN, FIELDNAME, ASCDESC)VALUES (:1,:2,:3,:4,:5)System Error : RecordsError - failure during Copy Database process.SOLUTION 201054508; EPY: When Applying 07-E getting error 805 - ORA-00001:(SYSADM.PS_PSKEYDEFN)SOLUTION #1PeopleSoft was unable to replicate this issue using PT8.48.The following tests that were ran:Copied Tax Update 07-E project from file to an DEMO database : No error.Copied Tax Update 07-E project from an DEMO database to DEV database: Noerror.Ran Alter Tables/Create Indexes for record TAXFORM_ERN after applying TaxUpdate 07-E project: PS0TAXFORM_ERN index dropped successfully.Prior to 07-E, record TAXFORM_ERN had 2 indexes, i.e. Key and Index 0.Applied software update using Change Assistant: No error.Record TAXFORM_ERN had 2 system-generated indexes,i.e. Key index and 0 index, prior to Tax Update 07-E. After 07-E andAlter/Create Index of record TAXFORM_ERN, it should have only one index,i.e. the Key index.WORK AROUND:The error in the tax 07-E migration is caused by the change done to fieldBOX in table PS_TAXFORM_ERN. The tax update removed the Alternate SearchKey attribute on this filed. The migration is trying to add duplicateindexes, when it should delete an index. The index is a PeopleSoftapplication index, not a database index.1) Unchecking the alternate search key check box in the record fieldproperties of the Box Field on the TAXFORM_ERN table in the Target systembefore migrating the project seemed to work.2) This problem seems to occur when the action "copyprop" is flagged for"Upgrade" and an index is being deleted. Turn off the "Upgrade" flag for"copyprop" for TAXFORM_ERN and the project should copy successfully. Andthen turn the action flag back on for "copyprop" and it should then becopied without error since the index definition was removed from the targetbased on the field changes to the record whichwere migrated to the target in the first pass.3) Try dropping the index on PSKEYDEFN then copy the project. Recreate the2 indexes on the table, The unique index PS_PSKEYDEFN should blow up onthe recreate, if there are any duplicates.We noticed the following after the update. Before tax update tableSYSADM.PS_TAXFORM_ERN has two indexes. After the update the indexSYSADM.PS0TAXFORM_ERN was dropped.CREATE UNIQUE INDEX SYSADM.PS_TAXFORM_ERN ON SYSADM.PS_TAXFORM_ERN(TAXFORM_ID, EFFDT, BOX, ERNCD) ;CREATE INDEX SYSADM.PS0TAXFORM_ERN ON SYSADM.PS_TAXFORM_ERN(BOX, TAXFORM_ID, EFFDT, ERNCD) ;KEYWORDS: 07E, 07-E, PS_TAXFORM_ERN, PSKEYDEFNEPY: Tax Update 07-E Master Solution for 8.X & 9.0 HCM - COM, E&G, PUB, CS& FED Details: SOLUTION 201049812: EPY 07E - Tax Update 07-E Master Solutionfor 8.X & 9.0 HCM ---Commercial, Public Sector, Campus Solution, FederalAND E & G (FAQ).SPECIFIC TO: Enterprise, North American Payroll, Release { 8.3(SP1), 8.8(SP1), 8.9 & 9.0}._____________________________________________________________TABLE ADDITIONS/UPDATES ISSUES:NONE Reported_____________________________________________________________COBOL & DMS ISSUES:PLEASE, when you apply tax updates do GLOBAL recompile of ALL the COBOLprograms & reload ALL your stored statements.Solution 200730878: Why run STOREPT, STOREPAY, STOREBAS, STOREHR, STOREPYI,etc.Solution 43949: Why recompile ALL the COBOL?NONE Reported_____________________________________________________________SQR ISSUES:ISSUE #1Tax960fd.sqr that was delivered as part of 07-E is failing with followingerror: (SQR 4713) Cannot open the #INCLUDE file: 'W2addr.sqc' (2): No such file or directoryThis is happening because of the uppercase letter in 'W2addr.sqc' used inthe sqr include statement, and will affect environments where filenames arecase sensitive, e.g.: UNIX. The file w2addr.sqc was delivered with 07E.This affects all releases.SOLUTION #1Incident created to address this: 1713032000 - Tax update 07-Etax960fd.sqr erroring out on include of W2addr.sqc The workaround, is tocorrect the typo and change the W2addr.sqc reference in the INCLUDEstatement to all lowercase: w2addr.sqcISSUE #2Issue with TAX960FD.SQR using new w2addr.sqc.Getting error: (SQR 5528) DB2 SQL OPEN/EXECUTE error -301 in cursor 43:SQL0301N The value of input host variable or parameter number "" cannot beused because of its data type. SQLSTATE=42895Cursor #43: SQL = SELECT YE.ADDRESS1, YE.ADDRESS2 FROM PS_YE_DATA YE,PS_W2_COMPANY W2C WHERE W2C.W2_REPORTING_CO = ? AND W2C.CALENDAR_YEAR = ? AND YE.COMPANY = W2C.COMPANY AND YE.EMPLID = ? AND YE.CALENDAR_YEAR = W2C.CALENDAR_YEAR AND YE.SEQUENCE_NUMBER =1Compiles = 2Executes = 0Rows = 0SOLUTION #2We have created a priority 2 "production effected" ICE Report ID 1719071000for addressing this issue and the fix will be delivered soon along with TaxUpdate 07-F.WORK AROUND:With in your w2addr.sqc for this procedure Get-W2-AddressAND W2C.CALENDAR_YEAR = $W2.Balance_YearAND W2C.CALENDAR_YEAR = 2007 <------Replace $W2.Balance_Year with2007_____________________________________________________________Previous Tax update Master Resolutions in Year 2007SOLUTION 201028179: EPY 8.x: Tax Update 07-A Master Solution for 8.X HCM -COM, E&G, PUB, CS & FEDSOLUTION 201038689: EPY 07B - Tax Update 07-B Master Solution for 8.X & 9.0HCMSOLUTION 201041376: EPY 07C - Tax Update 07-C Master Solution for 8.X & 9.0HCMSOLUTION 201048574 : EPY 07D - Tax Update 07-D Master Solution for 8.X &9.0 HCM____________________________________________________________RELATED RESOURCES:Solution 14138 Tax Update Procedures: How To Apply Tax Updates(Master Resolution)Solution 303939: What are the different ". DMS" files deliveredwith tax updates, and what do we doSolution 41450 All About Stored StatementsSolution 698826 Invalid or missing PeopleCode program then givesname of (component).Solution 200742195: Tax Updates for HRMS 8.3 after service pack 1Solution 200740082: Master FAQ document for HRMS 8.3 Service Pack 1.Solution 705695: PeopleTools release required for product patchesSolution 707920: Messages Catalog entries listed in fix or taxupdate projects do not get copiedSolution 201001870: EPY: 8.9 MP1 Delta Documentation Payroll for NorthAmericaSolution 201033943; EHCM: HCM 9.0 & Tax Updates?Solution 201030656; EHCM: What are the Upgrade paths to HCM 9.0?Solution 699259: Outcome of Tax Update Survey - What will beincluded in Tax Updates?You can find Tax Update Schedule info on Customer Connectionhttp://www.peoplesoft.com/corp/en/support/roadmap/taxSchedule.asp?prod=All
Subscribe to:
Posts (Atom)