XML_DLT_CMP_VW

(SQL View)
Index Back

View Delt@ Company Data

View Delt@ for Company Data

SELECT A.EMPLID , A.COMPANY , A.EFFDT , A.EFF_STATUS , A.DESCR , A.COUNTRY , A.ADDR_FIELD1 %Concat ' ' %Concat A.ADDRESS1 %Concat ', ' %Concat A.ADDR_FIELD2 , A.ADDRESS1 , A.ADDRESS2 , A.CITY , A.NUM1 , A.NUM2 , A.ADDR_FIELD1 , A.ADDR_FIELD2 , A.ADDR_FIELD3 , A.STATE , A.POSTAL , A.FISCAL_ID_CD , A.PHONE_TYPE , A.COUNTRY_CODE , A.PHONE , A.INSUR_COMP_CD_ESP , A.SSN_EMPLOYER , A.ESTABID , A.SSN_EMPL_TYPE , A.INDSTRY_CD_ESP , C.DESCR50 , A.SCHEME_ID_ESP , A.DLT_PREVENTIVA_ESP FROM PS_COMPDATA_ESP_VW A , PS_XML_DLT_INJ_VW B , PS_INDSTRY_ACT_ESP C WHERE A.EMPLID = B.EMPLID AND A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_COMPDATA_ESP_VW A_ED WHERE B.EMPLID = A_ED.EMPLID AND A_ED.EFFDT <= B.ABS_BEGIN_DT) AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_INDSTRY_ACT_ESP C_ED WHERE C.INDSTRY_CD_ESP = C_ED.INDSTRY_CD_ESP AND C_ED.EFFDT <= B.ABS_BEGIN_DT)

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

Default Value: %date

4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive
5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
6 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
7 ADDRESS100 Character(100) VARCHAR2(100) NOT NULL Address 1
8 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
9 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
10 CITY Character(30) VARCHAR2(30) NOT NULL City
11 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
12 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
13 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
14 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
15 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
16 STATE Character(6) VARCHAR2(6) NOT NULL State
17 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
18 FISCAL_ID_CD Character(10) VARCHAR2(10) NOT NULL Fiscal Identification Code
19 PHONE_TYPE Character(4) VARCHAR2(4) NOT NULL Phone Type
BUSN=Business
CAMP=Campus
CELL=Mobile
DORM=Dormitory
FAX=FAX
HOME=Home
MAIN=Main
OTR=Other
PGR1=Pager 1
PGR2=Pager 2
TELX=Telex
WORK=Work
20 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code
21 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
22 INSUR_COMP_CD_ESP Character(5) VARCHAR2(5) NOT NULL Insurance Company Code
23 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number
24 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
25 SSN_EMPL_TYPE Character(3) VARCHAR2(3) NOT NULL Social Security Number Type
1=Regular
2=Apprenticeship
3=Training
26 INDSTRY_CD_ESP Character(5) VARCHAR2(5) NOT NULL Industry Activity Code
27 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
28 SCHEME_ID_ESP Character(4) VARCHAR2(4) NOT NULL Scheme ID - ESP
29 DLT_PREVENTIVA_ESP Character(1) VARCHAR2(1) NOT NULL Preventive
A=Business Owner
E=External
I=Internal
M=Shared
N=None
W=Designed Workers