ALL_CCC_ESP_VW

(SQL View)
 Index Back

Establishment ER Soc.Sec View

Establishment Employer Social Security View - ESP

SELECT B.SSN_EMPLOYER , B.EFFDT , B.SSN_EMPL_TYPE , B.SCHEME_ID_ESP , B.SCHEME_ID_ESP %Concat %Substring(B.SSN_EMPLOYER, 1, 11) , B.INDSTRY_CD_ESP , A.ESTABID , A.EFF_STATUS , A.DESCR , A.COUNTRY , A.ADDRESS1 , A.ADDR_FIELD1 %Concat ' ' %Concat A.ADDRESS1 %Concat ' ' %Concat A.ADDR_FIELD2 , A.ADDRESS2 , A.CITY , A.NUM1 , A.NUM2 , A.ADDR_FIELD1 , A.ADDR_FIELD2 , A.ADDR_FIELD3 , A.STATE , A.POSTAL , D.PHONE , A.INSUR_COMP_CD_ESP , C.COMPANY , C.DESCR , C.FISCAL_ID_CD FROM PS_ESTAB_SSEC_ESP B , PS_COMPANY_TBL C , PS_ESTAB_TBL A LEFT OUTER JOIN PS_ESTAB_PHONE D ON A.ESTABID = D.ESTABID AND A.EFFDT = D.EFFDT AND D.PHONE_TYPE = 'BUSN' WHERE C.COMPANY = A.COMPANY AND B.ESTABID = A.ESTABID AND B.EFFDT = A.EFFDT AND C.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_COMPANY_TBL B_ED WHERE C.COMPANY = B_ED.COMPANY AND B_ED.EFFDT <= A.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number
2 EFFDT Date(10) DATE Effective Date

Default Value: %date

3 SSN_EMPL_TYPE Character(3) VARCHAR2(3) NOT NULL Social Security Number Type
1=Regular
2=Apprenticeship
3=Training
4 SCHEME_ID_ESP Character(4) VARCHAR2(4) NOT NULL Scheme ID - ESP
5 SCHEME_CCC_ESP Character(15) VARCHAR2(15) NOT NULL Scheme ID + SSN Employer.
6 INDSTRY_CD_ESP Character(5) VARCHAR2(5) NOT NULL Industry Activity Code

Prompt Table: INDSTRY_ACT_ESP

7 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
8 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

Default Value: A

9 DESCR Character(30) VARCHAR2(30) NOT NULL Description
10 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

11 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
12 ADDRESS100 Character(100) VARCHAR2(100) NOT NULL Address 1
13 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
14 CITY Character(30) VARCHAR2(30) NOT NULL City
15 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
16 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
17 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
18 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
19 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
20 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

21 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
22 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
23 INSUR_COMP_CD_ESP Character(5) VARCHAR2(5) NOT NULL Insurance Company Code

Prompt Table: INSUR_COMP_ESP

24 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

25 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
26 FISCAL_ID_CD Character(10) VARCHAR2(10) NOT NULL Fiscal Identification Code