XML_IA_CD_ESPVW

(SQL View)
 Index Back

Employee IA Data VW ESP

View to fetch IA description based on the contract data effective date

SELECT CD.EMPLID , CD.CONTRACT_NUM , WKF.EFFDT , DEP.SSN_EMPLOYER , DEP.SCHEME_ID_ESP , DEP.INDSTRY_CD_ESP , %subrec(ADDRESS_SBR,A) FROM PS_CONTRACT_DATA CD , PS_ESTAB_SSEC_ESP DEP , PS_JOB JB , PS_ESTAB_TBL A , PS_JOB_JR JR , PS_WKF_CNT_TYP_ESP WKF WHERE JB.EMPLID = CD.EMPLID AND JB.CONTRACT_NUM = CD.CONTRACT_NUM AND CD.EMPLID = JB.EMPLID AND JB.HR_STATUS = 'A' AND JR.EMPLID = JB.EMPLID AND JR.EMPL_RCD = JB.EMPL_RCD AND JR.EFFDT = JB.EFFDT AND JR.EFFSEQ = JB.EFFSEQ AND CD.EMPLID = WKF.EMPLID AND CD.CONTRACT_NUM = WKF.CONTRACT_NUM AND JB.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE JB.EMPLID = A_ED.EMPLID AND JB.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.HR_STATUS = 'A' AND (A_ED.EFFDT <= CD.CONTRACT_BEGIN_DT OR (A_ED.EFFDT <= %CurrentDateIn AND CD.CONTRACT_BEGIN_DT IS NULL)) AND JB.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE JB.EMPLID = A_ES.EMPLID AND JB.EMPL_RCD = A_ES.EMPL_RCD AND A_ES.HR_STATUS = 'A' AND JB.EFFDT = A_ES.EFFDT) ) AND CD.EMPLID = JB.EMPLID AND JB.ESTABID = DEP.ESTABID AND DEP.EFFDT = ( SELECT MAX(DEP1.EFFDT) FROM PS_ESTAB_SSEC_ESP DEP1 WHERE DEP1.ESTABID = DEP.ESTABID AND DEP1.SSN_EMPLOYER = DEP.SSN_EMPLOYER AND DEP1.SSN_EMPLOYER = JR.SSN_EMPLOYER AND DEP1.EFFDT <= JB.EFFDT) AND A.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_ESTAB_TBL B WHERE B.ESTABID = A.ESTABID AND B.EFF_STATUS = 'A' AND B.EFFDT <= JB.EFFDT) AND A.ESTABID = DEP.ESTABID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number
5 SCHEME_ID_ESP Character(4) VARCHAR2(4) NOT NULL Scheme ID - ESP
6 INDSTRY_CD_ESP Character(5) VARCHAR2(5) NOT NULL Industry Activity Code

Prompt Table: INDSTRY_ACT_ESP

7 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

8 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
9 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
10 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
11 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
12 CITY Character(30) VARCHAR2(30) NOT NULL City
13 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
14 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
15 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference
16 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
17 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
18 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
19 COUNTY Character(30) VARCHAR2(30) NOT NULL County
20 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

21 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
22 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
23 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit