TL_SRCH_JOB_VW (SQL View)	Index Back
TL Job Data View This record has been created to extract Job Data and to bypass the query security record.

TL_SRCH_JOB_VW

(SQL View)

TL Job Data View

This record has been created to extract Job Data and to bypass the query security record.

SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.EFFSEQ ,A.PER_ORG ,A.DEPTID ,A.JOBCODE ,A.POSITION_NBR ,A.SUPERVISOR_ID ,A.HR_STATUS ,A.EMPL_STATUS ,A.ACTION ,A.ACTION_DT ,A.ACTION_REASON ,A.LOCATION ,A.TAX_LOCATION_CD ,A.REG_TEMP ,A.FULL_PART_TIME ,A.COMPANY ,A.PAYGROUP ,A.EMPL_TYPE ,A.BUSINESS_UNIT ,A.SETID_DEPT ,A.SETID_JOBCODE ,A.SETID_LOCATION ,A.POI_TYPE ,I.DESCR ,G.DESCR ,F.DESCR ,K.DESCR ,J.DESCR ,D.DESCR ,A.LASTUPDDTTM ,A.LASTUPDOPRID FROM PS_JOB A LEFT OUTER JOIN PS_TL_SRCH_JB_D_VW I ON I.DEPTID = A.DEPTID AND A.SETID_DEPT = I.SETID LEFT OUTER JOIN PS_TL_SRCH_JB_L_VW F ON F.LOCATION = A.LOCATION AND F.SETID = A.SETID_LOCATION LEFT OUTER JOIN PS_TL_SRCH_JB_P_VW G ON G.POSITION_NBR = A.POSITION_NBR LEFT OUTER JOIN PS_BUSUNIT_HR_VW J ON J.BUSINESS_UNIT = A.BUSINESS_UNIT LEFT OUTER JOIN PS_COMPANY_VW K ON K.COMPANY = A.COMPANY LEFT OUTER JOIN PS_TL_SRCH_JB_J_VW D ON D.JOBCODE = A.JOBCODE AND D.SETID = A.SETID_JOBCODE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Default Value: PER_ORG_ASGN.EMPLID
Prompt Table: PERS_SRCH_GBL

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

3 EFFDT Date(10) DATE NOT NULL Effective Date
Default Value: %date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

5 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest
Default Value: PER_ORG_ASGN.PER_ORG

6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: DEPT_TBL

7 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
Prompt Table: JOBCODE_TBL

8 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
Prompt Table: POSN_DATA_VW

9 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
Prompt Table: HCR_SUP_ID_DVW

10 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive
Default Value: A

11 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration
Default Value: A

12 ACTION Character(3) VARCHAR2(3) NOT NULL Action
Prompt Table: ACTION_TBL

13 ACTION_DT Date(10) DATE Action Date
Default Value: %date

14 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code
Prompt Table: ACTN_REASON_TBL

15 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
Prompt Table: LOCATION_TBL

16 TAX_LOCATION_CD Character(10) VARCHAR2(10) NOT NULL Tax Location Code
Prompt Table: %RECNAME_EDIT

17 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
R=Regular
T=Temporary
Default Value: R

18 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
Default Value: F

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

20 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
Prompt Table: %EDITTABLE5

21 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried
Prompt Table: PYGRP_EMPTYP_VW

22 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT
Prompt Table: BUSUNIT_HR_VW

23 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
Default Value: OPR_DEF_TBL_HR.SETID

24 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
Default Value: OPR_DEF_TBL_HR.SETID

25 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID

26 POI_TYPE Character(5) VARCHAR2(5) NOT NULL Defines the Transactional usage of POIs in HRMS.

27 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description

28 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description

29 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description

30 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr

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

32 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description

33 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
Default Value: %datetime

34 LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Default Value: PER_ORG_ASGN.EMPLID Prompt Table: PERS_SRCH_GBL
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
3	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	PER_ORG	Character(3)	VARCHAR2(3) NOT NULL	Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest. CWR=Contingent Worker EMP=Employee POI=Person of Interest Default Value: PER_ORG_ASGN.PER_ORG
6	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPT_TBL
7	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code Prompt Table: JOBCODE_TBL
8	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number Prompt Table: POSN_DATA_VW
9	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID Prompt Table: HCR_SUP_ID_DVW
10	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive Default Value: A
11	EMPL_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Payroll Status A=Active D=Deceased L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration Default Value: A
12	ACTION	Character(3)	VARCHAR2(3) NOT NULL	Action Prompt Table: ACTION_TBL
13	ACTION_DT	Date(10)	DATE	Action Date Default Value: %date
14	ACTION_REASON	Character(3)	VARCHAR2(3) NOT NULL	Reason Code Prompt Table: ACTN_REASON_TBL
15	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code Prompt Table: LOCATION_TBL
16	TAX_LOCATION_CD	Character(10)	VARCHAR2(10) NOT NULL	Tax Location Code Prompt Table: %RECNAME_EDIT
17	REG_TEMP	Character(1)	VARCHAR2(1) NOT NULL	Regular/Temporary R=Regular T=Temporary Default Value: R
18	FULL_PART_TIME	Character(1)	VARCHAR2(1) NOT NULL	Full/Part Time D=On Demand F=Full-Time P=Part-Time Default Value: F
19	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company Prompt Table: COMPANY_TBL
20	PAYGROUP	Character(3)	VARCHAR2(3) NOT NULL	Pay Group Prompt Table: %EDITTABLE5
21	EMPL_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Employee Type E=Exception Hourly H=Hourly N=Not Applicable S=Salaried Prompt Table: PYGRP_EMPTYP_VW
22	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT Prompt Table: BUSUNIT_HR_VW
23	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID Default Value: OPR_DEF_TBL_HR.SETID
24	SETID_JOBCODE	Character(5)	VARCHAR2(5) NOT NULL	Job Code Set ID Default Value: OPR_DEF_TBL_HR.SETID
25	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location Set ID
26	POI_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Defines the Transactional usage of POIs in HRMS.
27	DEPT_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Department Description
28	POSN_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Position Description
29	LOCATION_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Location Description
30	COMPANY_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Company Descr
31	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
32	JOBCODE_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Job Code Description
33	LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts. Default Value: %datetime
34	LASTUPDOPRID	Character(30)	VARCHAR2(30) NOT NULL	Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.