select TL_RULE_ID
from PS_TL_RULE_STEPS
where SQL_ID = 'XXX'
Monday, 7 June 2010
SQL to Find the Rules that SQL contain a Given SQL Object
Wednesday, 5 May 2010
Getting Details Of A Table's Columns
The following Oracle SQL gets the columns in a table.
-- -------------------
-- Get Table's Columns
-- -------------------
select COLUMN_NAME,
DATA_TYPE
from ALL_TAB_COLUMNS
where TABLE_NAME = 'PS_TY_TL_SDF_EVT'
Tuesday, 13 April 2010
Changing PeopleSoft Passwords by the Back End
If the same password key is used across multiple PeopleSoft environments you can set the password in one account equal to that in another by creating a database link between the two and using the following SQL: -
Along with changing the password you also need to set the last password change date, LASTPSWDCHANGE equal to the current date. Otherwise you could change your password, but have it expire on you immediately.
It also makes sense to reset the account locked flag, ACCTLOCK, in case the account has been locked.
update PSOPRDEFN@HR89XXX OPXXX
set OPXXX.OPERPSWD = ( select OP.OPERPSWD
from PSOPRDEFN OP
where OPXXX.OPRID = OP.OPRID
),
OPXXX.ACCTLOCK = 0,
OPXXX.LASTPSWDCHANGE = SYSDATE
where OPXXX.OPRID = 'USERNAME'
Along with changing the password you also need to set the last password change date, LASTPSWDCHANGE equal to the current date. Otherwise you could change your password, but have it expire on you immediately.
It also makes sense to reset the account locked flag, ACCTLOCK, in case the account has been locked.
Tuesday, 26 January 2010
Last User Exit To Brooklyn
T&L rules can include PeopleCode steps by making them User Exit rules. To create a user exit rule, do the following.
- Open the TL_TA_RULES application engine and create a new section for your rule. For this section you must check the Access Pulblic check box.
- Add your rule steps to this section.
- Navigate to Home > Setup HRMS > Product Realted > Time and Labor > Validation Criteria > AE Section Definition and define your newly created section. The program name is TL_TA_RULES and theprocess type should be Rule (User Exit).
- Navigate to Home > Setup HRMS >System Administration > Utilities > Build Time And Labor Rules > Rules and create a rule definition, checking the User Exit box and entering the name of the AE section you created earlier.
- Add the new rule to any desired rule programs.
- In the DMS script you use to migrate your rule you will need to include the table PS_TL_AE_SECTION which contains user exit section details. The migration project will need to contain your AE Section and any associated steps.
Monday, 5 October 2009
PeopleSoft Hints
The PeopleSoft password hints table, PSUSERATTR, is not encrypted, so avoid using any hint that you use elsewhere, such as What is your Mother's Maiden name?
If you are forced to use a standard question give a bogus answer not one which will compromise your personal security e.g. What is your favourite Sport? response WIBBLE.
If you are forced to use a standard question give a bogus answer not one which will compromise your personal security e.g. What is your favourite Sport? response WIBBLE.
Sunday, 23 August 2009
Getting Oracle Table Information
The following SQL gets useful information about tables in an Oracle Database, such as the tablespace used.
select TABLE_NAME,
OWNER,
INITIAL_EXTENT,
PCT_USED,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
TABLESPACE_NAME
from dba_tables
where TABLE_NAME in ( 'PS_TY_TL_RAP_H_HST',
'PS_TY_TL_RAP_T_HST',
'PS_TY_TL_RPT_T_HST',
'PS_TY_TL_PAY_T_HST',
'PS_TY_SCH_AD_D_HST',
'PS_TY_SCH_MN_S_HST',
'PS_TY_TL_TR_CO_HST' )
select TABLE_NAME,
OWNER,
INITIAL_EXTENT,
PCT_USED,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
TABLESPACE_NAME
from dba_tables
where TABLE_NAME in ( 'PS_TY_TL_RAP_H_HST',
'PS_TY_TL_RAP_T_HST',
'PS_TY_TL_RPT_T_HST',
'PS_TY_TL_PAY_T_HST',
'PS_TY_SCH_AD_D_HST',
'PS_TY_SCH_MN_S_HST',
'PS_TY_TL_TR_CO_HST' )
Tuesday, 26 May 2009
Getting the Mass Time Run Control Parameters
Getting the mass time run-control parameters is a bit tricky as it has no run-control table as such. Rather there are a number of base tables that are populated by the run page and used to run the process. The following SQL allows you to examine these tables to allow you to extrapolate what was entered.
Get the process instances of the Mass time process.
To get at the data you need you require the process instance numbers of the ST_LIB process. This allows you to lookup the other details you need.
Select Time Reporters
Next use your newly acquired process instance number to work out what was entered on the Select Time Reporters page.
Select Time Reporting Method
Now get the dates entered and how the time was reported.
And finally get the TRC details and Use Profile entered.
Now if only PeopleSoft had just used a good old fashioned run control table for this process...
Get the process instances of the Mass time process.
To get at the data you need you require the process instance numbers of the ST_LIB process. This allows you to lookup the other details you need.
-- -------------------------------------
-- Get process instances for ST Lib job.
-- -------------------------------------
select PRCS.PRCSINSTANCE,
PRCS.OPRID,
PRCS.PRCSJOBNAME,
PRCS.PRCSNAME,
PRCS.RUNCNTLID,
XLAT.XLATLONGNAME,
to_char(PRCS.BEGINDTTM,'yyyy-mm-dd-hh24.mi.ss') as Began,
to_char(PRCS.ENDDTTM,'yyyy-mm-dd-hh24.mi.ss') as Ended,
(((TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'J')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'J'))) * 86400)+(TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'SSSSS')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'SSSSS')))) as DURSEC
from PSPRCSRQST PRCS,
XLATTABLE_VW XLAT
where PRCS.RUNSTATUS = XLAT.FIELDVALUE
and PRCS.PRCSNAME = 'TL_ST_LIB'
and XLAT.FIELDNAME = 'RUNSTATUS'
and XLAT.EFFDT = (select max(XLAT1.EFFDT)
from XLATTABLE_VW XLAT1
where XLAT1.FIELDNAME = XLAT.FIELDNAME
and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
and XLAT1.EFFDT <= SYSDATE)
and XLAT.EFF_STATUS = 'A'
and PRCS.BEGINDTTM between to_date('2009-05-19-01.00.00','YYYY-MM-DD-HH24.MI.SS')
and to_date('2009-05-19-23.59.00','YYYY-MM-DD-HH24.MI.SS')
and PRCS.ENDDTTM is not null
and (((TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'J')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'J'))) * 86400)+(TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'SSSSS')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'SSSSS')))) > 500
-- order by PRCS.BEGINDTTM Desc
order by DURSEC Desc
Select Time Reporters
Next use your newly acquired process instance number to work out what was entered on the Select Time Reporters page.
-- -----------------------------------------------------------------------------
-- 1. Select Time Reporters Page
-- -----------------------------------------------------------------------------
-- Note that it is not possible to determine the parameters entered on this page
-- as the only thing stored in PeopleSoft is the employee IDs selected who
-- matched the criteria.
--
-- To allow an educated guess to be made as to what they were the following SQL
-- dumps the mebers prompt field details out, so if for example you have a
-- number of members returned who have the same group, then it stands a good
-- chance that this group was entered.
-- -----------------------------------------------------------------------------
-- Get Time Reporter(s) who were selected.
-- ------------------------------------------
select GDT.GROUP_ID,
EDT.EMPLID,
upper(NAM.LAST_NAME) as LAST_NAME,
NAM.FIRST_NAME,
JOB.BUSINESS_UNIT,
JOB.JOBCODE,
JOB.DEPTID,
JOB.TY_LOCAL_ID
from PS_TL_WRKGRP_TBL WRK,
PS_TL_EMPL_DATA EDT,
PS_TL_MASS_TR MAS,
PS_JOB JOB,
PS_TL_GROUP_DTL GDT,
PS_NAMES NAM
where WRK.EFFDT = ( select max(WRK1.EFFDT)
from PS_TL_WRKGRP_TBL WRK1
where WRK.WORKGROUP = WRK1.WORKGROUP
and WRK1.EFFDT <= SYSDATE )
and WRK.WORKGROUP = EDT.WORKGROUP
and EDT.EFFDT = ( select max(EDT1.EFFDT)
from PS_TL_EMPL_DATA EDT1
where EDT.EMPLID = EDT1.EMPLID
and EDT.EMPL_RCD = EDT1.EMPL_RCD
and EDT1.EFFDT <= SYSDATE )
and EDT.TIME_RPTG_STATUS = 'A'
and EDT.EMPLID = MAS.EMPLID
and EDT.EMPL_RCD = MAS.EMPL_RCD
and MAS.ST_INSTANCE = ( select PRCS.RUNCNTLID as ST_INSTANCE
from PSPRCSRQST PRCS
where PRCS.PRCSINSTANCE = :PRCSINSTANCE )
and EDT.EMPLID = JOB.EMPLID
and EDT.EMPL_RCD = JOB.EMPL_RCD
and JOB.EFFDT = (select max(JOB1.EFFDT)
from PS_JOB JOB1
where JOB.EMPLID = JOB1.EMPLID
and JOB.EMPL_RCD = JOB1.EMPL_RCD
and JOB1.EFFDT <= SYSDATE)
and JOB.EFFSEQ = (select max(JOB2.EFFSEQ)
from PS_JOB JOB2
where JOB.EMPLID = JOB2.EMPLID
and JOB.EMPL_RCD = JOB2.EMPL_RCD
and JOB.EFFDT = JOB2.EFFDT)
and JOB.EMPLID = GDT.EMPLID
and JOB.EMPL_RCD = GDT.EMPL_RCD
and GDT.GROUP_ID not like '%ALL'
and GDT.GROUP_ID not like '%TST'
and GDT.GROUP_ID not like 'MFAL%'
and GDT.GROUP_ID not like 'MFTA%'
and GDT.GROUP_ID <> 'MFSEE'
and GDT.EMPLID = NAM.EMPLID
and NAM.NAME_TYPE = 'PRI'
and NAM.EFFDT = ( select max(NAM1.EFFDT)
from PS_NAMES NAM1
where NAM.EMPLID = NAM1.EMPLID
and NAM.NAME_TYPE = NAM1.NAME_TYPE
and NAM.EFFDT <= SYSDATE )
order by GDT.GROUP_ID,
JOB.DEPTID,
EDT.EMPLID
Select Time Reporting Method
Now get the dates entered and how the time was reported.
-- -----------------------------------------------------------------------------
-- 2. Select Time Reporting Method Page
-- -----------------------------------------------------------------------------
-- Get the dates entered, how time was reported and whether Offdays should be
-- included in the schedule.
--
-- If just one date was selected then the End Date will be blank and start date
-- will correspond to 'Date:'. If not then start date will be the 'From:' date
-- entered and end date will be the 'To:'.
-- ----------------------------------------------------------------------------
select SESS.START_DATE,
SESS.END_DATE,
XLAT.XLATLONGNAME as HOW_TO_REPORT_TIME,
SESS.APPLY_OFFDAY_IND
from PS_TL_MASS_SESSION SESS,
XLATTABLE_VW XLAT
where SESS.ST_INSTANCE = ( select PRCS.RUNCNTLID as ST_INSTANCE
from PSPRCSRQST PRCS
where PRCS.PRCSINSTANCE = :PRCSINSTANCE )
and SESS.MASS_USER_ACTION = XLAT.FIELDVALUE
and XLAT.FIELDNAME = 'MASS_USER_ACTION'
and XLAT.EFFDT = (select max(XLAT1.EFFDT)
from XLATTABLE_VW XLAT1
where XLAT1.FIELDNAME = XLAT.FIELDNAME
and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
and XLAT1.EFFDT <= SYSDATE)
And finally get the TRC details and Use Profile entered.
-- -----------------------------------------------------------------------------
-- 3. Report Time Page
-- -----------------------------------------------------------------------------
-- -----------------
-- 3.1 Get Taskgroup
-- -----------------
select MASS.TASKGROUP
from PS_TL_MASS_SESSION MASS
where MASS.ST_INSTANCE = ( select PRCS.RUNCNTLID as ST_INSTANCE
from PSPRCSRQST PRCS
where PRCS.PRCSINSTANCE = :PRCSINSTANCE )
-- --------------------------------------------------------------
-- 3.2 Get Time Reporting Code details entered and 'Use Profile'.
-- --------------------------------------------------------------
select MT.TRC,
MT.TL_QUANTITY,
MT.COUNTRY,
MT.CURRENCY_CD,
MT.STATE,
MT.LOCALITY,
MT.COMP_RATECD,
MT.BILLABLE_IND,
MT.OVERRIDE_RATE,
XLAT.XLATLONGNAME as PROFILE
from PS_TL_MASS_TRANS MT,
XLATTABLE_VW XLAT
where MT.ST_INSTANCE = ( select PRCS.RUNCNTLID as ST_INSTANCE
from PSPRCSRQST PRCS
where PRCS.PRCSINSTANCE = :PRCSINSTANCE )
and MT.USE_PROFILE_IND = XLAT.FIELDVALUE
and XLAT.FIELDNAME = 'USE_PROFILE_IND'
and XLAT.EFFDT = (select max(XLAT1.EFFDT)
from XLATTABLE_VW XLAT1
where XLAT1.FIELDNAME = XLAT.FIELDNAME
and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
and XLAT1.EFFDT <= SYSDATE)
Now if only PeopleSoft had just used a good old fashioned run control table for this process...
Labels:
Mass Time,
PeopleSoft,
Run-Control,
SQL,
Troubleshooting
Subscribe to:
Posts (Atom)
