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...
No comments:
Post a Comment