EMPLIDEMPL_RCDEFFDTGPFR_URSRISK_VW(SQL View) |
Index Back |
|---|---|
View for risk rates for URSSAFView for risk rates for URSSAF. Looking into JOB and then into ESTAB_TBL |
| SELECT A.EMPLID , A.EMPL_RCD , B.EFFDT , B.ESTABID , C.RISKCD , C.AT_SECTION , C.STANDARD_RT , C.AT_CODE_FRA FROM PS_JOB A , PS_ESTAB_TBL B , PS_RISK_TBL C WHERE A.ESTABID = B.ESTABID AND ( B.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_ESTAB_TBL D WHERE A.ESTABID = D.ESTABID AND D.EFFDT <= A.EFFDT AND D.EFF_STATUS = 'A' ) OR ( B.EFFDT > A.EFFDT AND (B.EFFDT < ( SELECT MIN(E.EFFDT) FROM PS_JOB E WHERE E.EMPLID = A.EMPLID AND E.EMPL_RCD = A.EMPL_RCD AND E.EFFDT > A.EFFDT) OR NOT EXISTS ( SELECT 'X' FROM PS_JOB E WHERE E.EMPLID = A.EMPLID AND E.EMPL_RCD = A.EMPL_RCD AND E.EFFDT > A.EFFDT AND E.COMPANY = A.COMPANY) ) ) ) AND B.EFF_STATUS = 'A' AND A.ESTABID = C.ESTABID AND B.EFFDT = C.EFFDT |
| # | 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 | EFFDT |
Date(10) | DATE | Effective Date |
| 4 | ESTABID | Character(12) | VARCHAR2(12) NOT NULL | Establishment ID |
| 5 | RISKCD | Character(6) | VARCHAR2(6) NOT NULL | Risk Code |
| 6 | AT_SECTION | Character(2) | VARCHAR2(2) NOT NULL | AT SECTION |
| 7 | STANDARD_RT | Number(19,6) | DECIMAL(18,6) NOT NULL | Standard Rate |
| 8 | AT_CODE_FRA | Character(3) | VARCHAR2(3) NOT NULL | Code AT |