GPFR_URSRIS2_VW

(SQL View)
 Index Back

View for risk rates for URSSAF

View for risk rates for URSSAF. Looking into JOB and then into DEPT_TBL

SELECT A.EMPLID , A.EMPL_RCD , B.EFFDT , B.SETID , B.DEPTID , B.RISKCD , D.AT_SECTION , D.STANDARD_RT , D.AT_CODE_FRA FROM PS_JOB A , PS_DEPT_TBL B , PS_ESTAB_TBL C , PS_RISK_TBL D WHERE A.SETID_DEPT = B.SETID AND A.DEPTID = B.DEPTID AND ( B.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_DEPT_TBL E WHERE A.SETID_DEPT = E.SETID AND A.DEPTID = E.DEPTID AND E.EFFDT <= A.EFFDT AND E.EFF_STATUS = 'A' ) OR ( B.EFFDT > A.EFFDT AND (B.EFFDT < ( SELECT MIN(F.EFFDT) FROM PS_JOB F WHERE F.EMPLID = A.EMPLID AND F.EMPL_RCD = A.EMPL_RCD AND F.EFFDT > A.EFFDT) OR NOT EXISTS ( SELECT 'X' FROM PS_JOB F WHERE F.EMPLID = A.EMPLID AND F.EMPL_RCD = A.EMPL_RCD AND F.EFFDT > A.EFFDT) ) ) ) AND B.EFF_STATUS = 'A' AND B.ESTABID = C.ESTABID AND B.RISKCD = D.RISKCD AND B.ESTABID = D.ESTABID AND A.ESTABID = C.ESTABID AND C.EFF_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(F.EFFDT) FROM PS_ESTAB_TBL F WHERE B.ESTABID = F.ESTABID AND F.EFFDT <= A.EFFDT AND F.EFF_STATUS = 'A' ) AND D.EFFDT = ( SELECT MAX(F.EFFDT) FROM PS_RISK_TBL F , PS_GP_PYE_SEG_STAT G WHERE B.ESTABID = F.ESTABID AND A.EMPLID = G.EMPLID AND A.EMPL_RCD = G.EMPL_RCD AND G.CALC_ACTION = 'C' AND B.RISKCD = D.RISKCD AND F.EFFDT <= G.SEG_END_DT)

# 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 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
5 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
6 RISKCD Character(6) VARCHAR2(6) NOT NULL Risk Code
7 AT_SECTION Character(2) VARCHAR2(2) NOT NULL AT SECTION
8 STANDARD_RT Number(19,6) DECIMAL(18,6) NOT NULL Standard Rate
9 AT_CODE_FRA Character(3) VARCHAR2(3) NOT NULL Code AT