RS_ED_EFFDT_WKR(SQL View) |
Index Back |
|---|---|
Effective Worker view |
| SELECT A.EMPLID , J.DESCR , A.SUPERVISOR_ID , C.PER_STATUS , P.POOL_ID , P.POOL_NAME , R.BUSINESS_UNIT , R.PROJECT_ID , R.PROJ_DESCR , R.PC_ACTUAL_HRS , R.ACTUAL_AMOUNT , R.BILLED_TO_DATE , CASE WHEN R.PC_ACTUAL_HRS > 0 THEN %Round(%DecDiv(R.BILLED_TO_DATE, R.PC_ACTUAL_HRS), 2) ELSE 0 END , CASE WHEN R.PC_ACTUAL_HRS > 0 THEN %Round(%DecDiv(R.ACTUAL_AMOUNT, R.PC_ACTUAL_HRS), 2) ELSE 0 END , %Coalesce(L.DTTM_MODIFIED, %Dttm(B.EFFDT, %TIMEIN('23.59.00.000000'))) AS LASTUPDDTTM , %Coalesce(P.DTTM_IN_EFFECT, %DATETIMEIN('1900-01-01 00.00.00.000000')) FROM %Table(JOB) A , %Table(PERSONAL_DATA) C , ((%Table(RS_WRKR_EFFDT) B LEFT OUTER JOIN PS_RS_ED_ETL_LOG L ON L.DOCUMENT = B.EMPLID) LEFT OUTER JOIN %Table(RS_ED_WKR_POOL) P ON B.EMPLID = P.EMPLID) LEFT OUTER JOIN %Table(RS_ED_RATES) R ON B.EMPLID = R.EMPLID , PS_JOBCODE_TBL J WHERE A.EMPLID = B.EMPLID AND B.SYSTEM_SOURCE = 'RS' AND B.EMPLID = C.EMPLID AND B.EFFDT = ( SELECT MAX(D.EFFDT) FROM %Table(RS_WRKR_EFFDT) D WHERE D.EMPLID = B.EMPLID AND D.SYSTEM_SOURCE = B.SYSTEM_SOURCE) AND A.EMPL_RCD = B.EMPL_RCD AND A.EFFDT = ( SELECT MAX(E.EFFDT) FROM %Table(JOB) E WHERE E.EMPLID = A.EMPLID AND E.EMPL_RCD = A.EMPL_RCD AND E.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(F.EFFSEQ) FROM %Table(JOB) F WHERE F.EMPLID = A.EMPLID AND F.EMPL_RCD = A.EMPL_RCD AND F.EFFDT = A.EFFDT) AND A.EMPL_STATUS NOT IN ('I','T','S','D','U','V','Q','R','X') AND J.JOBCODE = A.JOBCODE AND J.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_JOBCODE_TBL X WHERE J.SETID = X.SETID AND J.JOBCODE = X.JOBCODE AND X.EFFDT <= %CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | JOBCODE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Jobcode Description |
| 3 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 4 | PER_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Personnel Status
E=Employee N=Non-Employee |
| 5 | POOL_ID | Character(10) | VARCHAR2(10) NOT NULL | Pool ID field. |
| 6 | POOL_NAME | Character(30) | VARCHAR2(30) NOT NULL | 11/15/2004- FS |
| 7 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 8 | PROJECT_ID | Character(15) | VARCHAR2(15) NOT NULL | Project Id ChartField |
| 9 | PROJ_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Project ID Description |
| 10 | PC_ACTUAL_HRS | Signed Number(16,2) | DECIMAL(14,2) NOT NULL | Actual Hours |
| 11 | ACTUAL_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Actual Invoice Amount |
| 12 | BILLED_TO_DATE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Billed To Date |
| 13 | BILL_RATE_ACT | Number(27,3) | DECIMAL(26,3) NOT NULL | Bill Rate in Activity team (ARL). |
| 14 | COST_RATE | Number(27,3) | DECIMAL(26,3) NOT NULL | Cost Rate |
| 15 | LASTUPDDTTM | DateTime(26) | TIMESTAMP | Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts. |
| 16 | DTTM_IN_EFFECT | DateTime(26) | TIMESTAMP | Effective Date-Time |