RS_ED_WKR_AVA(SQL View) |
Index Back |
|---|---|
Resource Availability |
| SELECT A.EMPLID , W.BEGIN_DT , J.EFFDT , J.EMPL_STATUS , A.STD_DAYS , %Round(%DecDiv(%DecDiv(%DecDiv(%DecMult(J.STD_HOURS,C.FREQ_ANNUAL_FACTOR),52),A.STD_DAYS),D.STD_HOURS), 4) , J.STD_HOURS FROM %Table(RS_WORKER_TBL) A , %Table(RS_WRKR_EFFDT) B , %Table(JOB) J , %Table(FREQUENCY_TBL) C , %Table(INSTALLATION_RS) D , %Table(CAL_DETP_TBL) W , %Table(INSTALLATION_PC) I WHERE A.EMPLID = B.EMPLID AND B.SYSTEM_SOURCE = 'RS' AND %EffdtCheck(RS_WRKR_EFFDT, B, %CurrentDateIn) AND B.EMPLID = J.EMPLID AND B.EMPL_RCD = J.EMPL_RCD AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM %Table(JOB) J2 WHERE J.EMPLID = J2.EMPLID AND J.EMPL_RCD = J2.EMPL_RCD AND J.EFFDT = J2.EFFDT) AND A.STD_DAYS > 0 AND %EffdtCheck(FREQUENCY_TBL, C, %CurrentDateIn) AND C.FREQUENCY_ID = J.STD_HRS_FREQUENCY AND W.SETID = I.ANALYSIS_SETID AND W.CALENDAR_ID = I.CAL_TYPE_WEEK AND W.BEGIN_DT BETWEEN %DateAdd(%CurrentDateIn,-182) AND %DateAdd(%CurrentDateIn,365) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | DATE_WEEK_BEGIN | Date(10) | DATE | 01/30/98 SCC CN#SCHEW-XZ6L8: New field. |
| 3 | EFFDT | Date(10) | DATE | Effective Date |
| 4 | EMPL_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Employee Status
A=Active D=Deceased I=Inactive L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration |
| 5 | STD_DAYS | Number(4,2) | DECIMAL(3,2) NOT NULL | Standard Number of Days/Week |
| 6 | STD_DAY_PERCENT | Number(7,4) | DECIMAL(6,4) NOT NULL | The standard work day represented as a ratio of the worker's standard day to the general standard day. |
| 7 | STD_HOURS | Number(7,2) | DECIMAL(6,2) NOT NULL | Standard Hours |