GPCH_SI_VARI_VW

(SQL View)
 Index Back

Social Insurance View

Social Insurance View

SELECT AA.EMPLID , BB.GRADE , AA.SEX , BB.COMPANY , CC.HIRE_DT , BB.ANNUAL_RT , BB.CURRENCY_CD FROM PS_PRIMARY_JOB_VW EE , PS_PERS_DATA_EFFDT AA , PS_PER_ORG_ASGN_VW CC , PS_JOB BB WHERE CC.PER_ORG = 'EMP' AND BB.ACTION NOT IN ( 'HIR' ,'REH') AND CC.EMPLID = EE.EMPLID AND CC.EMPL_RCD = EE.EMPL_RCD AND AA.EMPLID = EE.EMPLID AND AA.EFFDT = ( SELECT MAX(EFFDT) FROM PS_PERS_DATA_EFFDT AA2 WHERE AA.EMPLID = AA2.EMPLID AND AA2.EFFDT <= %CurrentDateIn ) AND BB.EMPLID = EE.EMPLID AND BB.EMPL_RCD = EE.EMPL_RCD AND BB.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB BB2 WHERE BB2.EMPLID = BB.EMPLID AND BB2.EMPL_RCD = BB.EMPL_RCD AND BB2.EFFDT <= %CurrentDateIn) AND BB.EFFSEQ = ( SELECT MAX(BB3.EFFSEQ) FROM PS_JOB BB3 WHERE BB3.EMPLID = BB.EMPLID AND BB3.EMPL_RCD = BB.EMPL_RCD AND BB3.EFFDT = BB.EFFDT) UNION SELECT A.EMPLID , B.GRADE , A.SEX , B.COMPANY , C.HIRE_DT , B.ANNUAL_RT , B.CURRENCY_CD FROM PS_PRIMARY_JOB_VW E , PS_PERS_DATA_EFFDT A , PS_PER_ORG_ASGN_VW C , PS_JOB B WHERE C.PER_ORG = 'EMP' AND (B.ACTION IN ( 'HIR' ,'REH') OR EXISTS ( SELECT 'X' FROM PS_JOB X WHERE X.EMPLID = B.EMPLID AND X.EMPL_RCD = B.EMPL_RCD AND X.EFFDT = B.EFFDT AND X.EFFSEQ < B.EFFSEQ AND X.ACTION IN ('HIR', 'REH') AND NOT EXISTS ( SELECT 'X' FROM PS_JOB Z WHERE Z.EMPLID = X.EMPLID AND Z.EMPL_RCD = X.EMPL_RCD AND Z.EFFDT = X.EFFDT AND Z.EFFSEQ > X.EFFSEQ AND Z.EFFSEQ < B.EFFSEQ AND Z.ACTION IN ('TER', 'TWB', 'TWP')))) AND C.EMPLID = E.EMPLID AND C.EMPL_RCD = E.EMPL_RCD AND A.EMPLID = E.EMPLID AND A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_PERS_DATA_EFFDT A2 WHERE A.EMPLID = A2.EMPLID ) AND B.EMPLID = E.EMPLID AND B.EMPL_RCD = E.EMPL_RCD AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB B2 WHERE B2.EMPLID = B.EMPLID AND B2.EMPL_RCD = B.EMPL_RCD AND B2.ACTION=B.ACTION) AND B.EFFSEQ = ( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B3.EMPLID = B.EMPLID AND B3.EMPL_RCD = B.EMPL_RCD AND B3.EFFDT = B.EFFDT) AND NOT EXISTS ( SELECT 1 FROM PS_PRIMARY_JOB_VW EE , PS_PERS_DATA_EFFDT AA , PS_PER_ORG_ASGN_VW CC , PS_JOB BB WHERE B.EMPLID = BB.EMPLID AND B.EMPL_RCD = BB.EMPL_RCD AND CC.PER_ORG = 'EMP' AND BB.ACTION NOT IN ( 'HIR' ,'REH') AND CC.EMPLID = EE.EMPLID AND CC.EMPL_RCD = EE.EMPL_RCD AND AA.EMPLID = EE.EMPLID AND AA.EFFDT = ( SELECT MAX(EFFDT) FROM PS_PERS_DATA_EFFDT AA2 WHERE AA.EMPLID = AA2.EMPLID AND AA2.EFFDT <= %CurrentDateIn ) AND BB.EMPLID = EE.EMPLID AND BB.EMPL_RCD = EE.EMPL_RCD AND BB.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB BB2 WHERE BB2.EMPLID = EE.EMPLID AND BB2.EMPL_RCD = EE.EMPL_RCD AND BB2.EFFDT <= %CurrentDateIn) AND BB.EFFSEQ = ( SELECT MAX(BB3.EFFSEQ) FROM PS_JOB BB3 WHERE BB3.EMPLID = BB.EMPLID AND BB3.EMPL_RCD = BB.EMPL_RCD AND BB3.EFFDT = BB.EFFDT) )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade

Prompt Table: SAL_GRADE_TBL

3 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
4 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

5 HIRE_DT Date(10) DATE First Start Date
6 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code