COMPDATA_ESP_VW (SQL View)	Index Back
Company Other Data ESP This recoord is used for Contract Printing ESP to retrieve other data like Company Phone or Company Long Address, which cannot accesible directly with the XML Node functions. This record is used too for Contracts ESP for finding Company City.

COMPDATA_ESP_VW

(SQL View)

Company Other Data ESP

This recoord is used for Contract Printing ESP to retrieve other data like Company Phone or Company Long Address, which cannot accesible directly with the XML Node functions. This record is used too for Contracts ESP for finding Company City.

SELECT A.EMPLID , A.CONTRACT_NUM , A.EFFDT , A.EFFSEQ , A.EMPL_RCD , A.COMPANY , C.DESCR , C.ADDR_FIELD1 %Concat ' ' %Concat C.ADDRESS1 %Concat ', ' %Concat C.ADDR_FIELD2 %Concat '. ' %Concat C.ADDR_FIELD3 %Concat ' ' %Concat C.NUM1 %Concat ' ' %Concat C.NUM2 , C.ADDRESS1 , C.ADDRESS2 , C.CITY , C.STATE , B.PHONE_TYPE , B.COUNTRY_CODE , B.PHONE FROM PS_JOB_CNT_ESP_VW A , PS_COMPANY_TBL C LEFT OUTER JOIN PS_COMP_PHONE_TBL B ON C.COMPANY = B.COMPANY WHERE A.COMPANY = C.COMPANY AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_COMP_PHONE_TBL B_ED WHERE B.COMPANY = B_ED.COMPANY AND B_ED.EFFDT <= A.EFFDT) AND B.COMPANY = C.COMPANY AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_COMPANY_TBL C_ED WHERE C.COMPANY = C_ED.COMPANY AND C_ED.EFFDT <= A.EFFDT)

# 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 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

5 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

6 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
Prompt Table: COMPANY_TBL

7 DESCR Character(30) VARCHAR2(30) NOT NULL Description

8 ADDRESS100 Character(100) VARCHAR2(100) NOT NULL Address 1

9 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1

10 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2

11 CITY Character(30) VARCHAR2(30) NOT NULL City

12 STATE Character(6) VARCHAR2(6) NOT NULL State

13 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

14 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code

15 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone

#	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	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
6	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company Prompt Table: COMPANY_TBL
7	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
8	ADDRESS100	Character(100)	VARCHAR2(100) NOT NULL	Address 1
9	ADDRESS1	Character(55)	VARCHAR2(55) NOT NULL	Address 1
10	ADDRESS2	Character(55)	VARCHAR2(55) NOT NULL	Address 2
11	CITY	Character(30)	VARCHAR2(30) NOT NULL	City
12	STATE	Character(6)	VARCHAR2(6) NOT NULL	State
13	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
14	COUNTRY_CODE	Character(3)	VARCHAR2(3) NOT NULL	Country Code
15	PHONE	Character(24)	VARCHAR2(24) NOT NULL	Telephone