TL_ADM_ALERT_VW(SQL View) |
Index Back |
|---|---|
Admin Alert Search ViewUsed to show Admin Alert Search View |
| SELECT A.EMPLID ,A.EMPL_RCD ,A.ALERT_ID ,A.START_DATE ,A.END_DATE ,AC.ALERT_TYPE ,AC.ALERT_SOURCE ,A.ALERT_STATUS ,A.ALERT_VAL_NUM ,A.ALERT_VAL_CHAR ,AR.OPRID ,N.NAME_DISPLAY ,N.FIRST_NAME ,N1.NAME ,JC.DESCR FROM PS_JOBCODE_TBL JC , PS_JOB J , PS_NAMES N , PS_TL_ALERT A , PS_TL_ALERT_RCP AR , PS_TL_ALRT_CRT AC , PS_PERSON_NAME N1 , PSOPRDEFN OPR 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.EMPLID = J.EMPLID AND A.EMPL_RCD = J.EMPL_RCD AND A.EMPLID = N.EMPLID AND AC.ALERT_ID = A.ALERT_ID 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 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 OPR.OPRID = AR.OPRID AND N1.EMPLID = OPR.EMPLID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 3 | ALERT_ID |
Character(10) | VARCHAR2(10) NOT NULL | Alert Criterion ID |
| 4 | START_DATE |
Date(10) | DATE | Start Date for Gen Standing PO |
| 5 | END_DATE |
Date(10) | DATE | End Date |
| 6 | 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 |
| 7 | 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 |
| 8 | 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 |
| 9 | 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. |
| 10 | 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. |
| 11 | OPRID | Character(30) | VARCHAR2(30) NOT NULL |
A user's ID (see PSOPRDEFN).
Prompt Table: TL_ALRT_RAGN_VW |
| 12 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 13 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 14 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 15 | JOB_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Job Description |