TL_MNG_ALERT_VW(SQL View) |
Index Back |
|---|---|
Manage Alert Search ViewUsed to show Manage Alert Search View |
| 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' , 'OPRID' , ' ' , %DateNull , %DateNull , %DateNull , ' ' , ' ' , ' ' , 0 , ' ' , ' ' , ' ' , ' ' FROM PS_JOBCODE_TBL JC , PS_JOB J , PS_TL_EMPL_DATA ED , PS_TL_GROUP_DTL F , PS_TL_GRP_SECURITY S , PS_NAMES N , PS_DEPT_TBL D , PS_TL_ALERT E WHERE E.EMPLID = J.EMPLID AND E.EMPL_RCD = J.EMPL_RCD AND S.TL_GROUP_ID = F.TL_GROUP_ID AND F.EMPLID = J.EMPLID AND F.EMPL_RCD = J.EMPL_RCD AND F.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 F.EMPLID = ED.EMPLID AND F.EMPL_RCD = ED.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 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 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 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 | MENUNAME | Character(30) | VARCHAR2(30) NOT NULL | Menu Name (see PSMENUDEFN). |
| 33 | BARNAME | Character(30) | VARCHAR2(30) NOT NULL | Menu Bar Name |
| 34 | ITEMNAME | Character(30) | VARCHAR2(30) NOT NULL | Item Name |
| 35 | PANELNAME | Character(18) | VARCHAR2(18) NOT NULL | Panelname |