TL_WC_ALERTS_VW

(SQL View)
 Index Back

Alert for TL Fluid WorkCenter

Combines alerts with information from the alert criteria

SELECT J.EMPLID , J.EMPL_RCD , N.NAME_DISPLAY , N.LAST_NAME , N.FIRST_NAME , J.BUSINESS_UNIT , J.JOBCODE , JC.DESCR , J.DEPTID , D.DESCR , J.SUPERVISOR_ID , J.REPORTS_TO , J.LOCATION , ' ' , J.COMPANY , J.PAYGROUP , J.GP_PAYGROUP , ED.WORKGROUP , ED.TASKGROUP , J.POSITION_NBR , ' ' , 'N' , AR.OPRID , A.ALERT_ID , A.START_DATE , A.FIRST_PROC_DT , A.END_DATE , AC.ALERT_TYPE , AC.ALERT_SOURCE , A.ALERT_STATUS , A.ALERT_VAL_NUM , A.ALERT_VAL_CHAR , AC.DESCR , %Cast(A.START_DATE , DATE , CHARACTER) %Concat ' - ' %Concat %Cast(A.END_DATE , DATE , CHARACTER) , I.MENUNAME , I.BARNAME , AC.ITEMNAME , AC.PANELNAME FROM PS_TL_ALERT_RCP AR , PS_TL_ALERT A , PS_TL_ALRT_CRT AC , PSMENUITEM I , PSPRSMDEFN P , PS_NAMES N , PS_JOBCODE_TBL JC , PS_JOB J , PS_TL_EMPL_DATA ED , PS_DEPT_TBL D WHERE A.EMPLID = AR.EMPLID AND A.EMPL_RCD = AR.EMPL_RCD AND A.ALERT_ID = AR.ALERT_ID AND A.START_DATE = AR.START_DATE AND A.END_DATE = AR.END_DATE AND A.ALERT_ID = AC.ALERT_ID AND AC.PORTAL_NAME = P.PORTAL_NAME AND AC.PORTAL_OBJNAME = P.PORTAL_OBJNAME AND I.MENUNAME = P.PORTAL_URI_SEG1 AND ((I.ITEMNAME = P.PORTAL_URI_SEG2 AND I.ITEMNAME = I.PNLGRPNAME) OR (I.PNLGRPNAME= P.PORTAL_URI_SEG2 AND I.ITEMNAME <> I.PNLGRPNAME)) AND A.EMPLID = N.EMPLID AND N.NAME_TYPE = 'PRI' AND N.EFFDT = ( SELECT MAX(SN.EFFDT) FROM PS_NAMES SN WHERE SN.EMPLID = N.EMPLID AND SN.NAME_TYPE=N.NAME_TYPE AND SN.EFFDT <= %CurrentDateIn) AND A.EMPLID = J.EMPLID AND A.EMPL_RCD = J.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT ) AND JC.SETID = J.SETID_JOBCODE AND JC.JOBCODE = J.JOBCODE AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = J.SETID_JOBCODE AND JC1.JOBCODE = J.JOBCODE AND JC1.EFFDT <= %CurrentDateIn) AND J.EMPLID = ED.EMPLID AND J.EMPL_RCD = ED.EMPL_RCD AND ED.EFFDT = ( SELECT MAX(ED1.EFFDT) FROM PS_TL_EMPL_DATA ED1 WHERE ED1.EMPLID = ED.EMPLID AND ED1.EMPL_RCD = ED.EMPL_RCD AND ED1.EFFDT <= %CurrentDateIn) AND D.SETID = J.SETID_DEPT AND D.DEPTID = J.DEPTID AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.SETID = J.SETID_DEPT AND D1.DEPTID = J.DEPTID AND D1.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: TL_EMPL_NAME_VW

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
5 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_HR

7 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
8 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description

Prompt Table: TL_JOB_DESC_VW

9 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

10 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
11 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

Prompt Table: TL_SUPRVSR_VW

12 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number

Prompt Table: TL_RPTS_TO_VW

13 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL

14 LOC_DESCR_SS Character(30) VARCHAR2(30) NOT NULL Location Description
15 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

16 PAYGROUP Character(3) VARCHAR2(3) NOT NULL North American Pay Group

Prompt Table: PAYGROUP_TBL

17 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
18 WORKGROUP Character(10) VARCHAR2(10) NOT NULL Workgroup

Prompt Table: TL_WRKGRP_TBL

19 TASKGROUP Character(10) VARCHAR2(10) NOT NULL Taskgroup

Prompt Table: TL_TASKGRP_TBL

20 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
21 SCHEDULE_GRP Character(10) VARCHAR2(10) NOT NULL Scheduling Organizational Group Code
22 PROCESSED Character(1) VARCHAR2(1) NOT NULL Processed?
I=In Process
N=Not Processed
Y=Processed

Y/N Table Edit

Default Value: N

23 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
24 ALERT_ID Character(10) VARCHAR2(10) NOT NULL Alert Criterion ID
25 START_DATE Date(10) DATE Start Date for Gen Standing PO
26 FIRST_PROC_DT Date(10) DATE GP Core/Abs Mgt field to track when an absence was first processed. This date will not change regardless of how many retro runs are run against the absence subsequent to the initial calculation.
27 END_DATE Date(10) DATE End Date
28 ALERT_TYPE Character(2) VARCHAR2(2) NOT NULL Alert Type
B=Balances
E=Exception
L=Limit
P=Pattern
S=Status
U=Unprocessed Data
V=Data Verification
29 ALERT_SOURCE Character(2) VARCHAR2(2) NOT NULL Alert Source
A=Absence Event
B=Benefit Plan
C=Comp or Leave Time
E=Exceptions
P=Payable Time
R=Reported Time
30 ALERT_STATUS Character(2) VARCHAR2(2) NOT NULL Alerts Status
AA=Complete
AU=Accepted - Unrestricted
OP=Open
PC=Pending Correction
RA=Removed by Administrator
RW=Reviewed
31 ALERT_VAL_NUM Signed Number(20,6) DECIMAL(18,6) NOT NULL Alert Triggering value. The value that was found in the data source - e.g. the sum of hours in Reported Time - that triggered a given alert to be created.
32 ALERT_VAL_CHAR Character(30) VARCHAR2(30) NOT NULL Alert Triggering value. The value that was found in the data source - e.g. the reported TRC - that triggered a given alert to be created.
33 DESCR Character(30) VARCHAR2(30) NOT NULL Description
34 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
35 MENUNAME Character(30) VARCHAR2(30) NOT NULL Menu Name (see PSMENUDEFN).
36 BARNAME Character(30) VARCHAR2(30) NOT NULL Menu Bar Name
37 ITEMNAME Character(30) VARCHAR2(30) NOT NULL Item Name
38 PANELNAME Character(18) VARCHAR2(18) NOT NULL Panelname