CNT_USED_VW (SQL View)	Index Back
Used Contract View This view is used to get all the used Contract per Employees. That means, any Contract used in JOB per Employee. It's referenced, particularly, in the CONTRACT_DATA transaction, to check if a Contract Status can be set to 'Inactive'.

CNT_USED_VW

(SQL View)

Used Contract View

This view is used to get all the used Contract per Employees. That means, any Contract used in JOB per Employee. It's referenced, particularly, in the CONTRACT_DATA transaction, to check if a Contract Status can be set to 'Inactive'.

SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.CONTRACT_NUM ,B.CONTRACT_STATUS ,C.CONTRACT_TYPE ,B.CONTRACT_BEGIN_DT ,B.CONTRACT_END_DT ,B.CONTRCT_EXP_END_DT FROM PS_JOB A ,PS_CONTRACT_DATA B ,PS_WKF_CNT_TYPE C WHERE A.EMPLID = B.EMPLID AND A.CONTRACT_NUM = B.CONTRACT_NUM AND A.EMPLID = C.EMPLID AND A.CONTRACT_NUM = C.CONTRACT_NUM AND (A.EFFDT >= %CurrentDateIn OR (A.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE A.EMPLID = B2.EMPLID AND A.EMPL_RCD = B2.EMPL_RCD AND B2.EFFDT <= %CurrentDateIn AND A.EFFSEQ=( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE A.EMPLID = B3.EMPLID AND A.EMPL_RCD = B3.EMPL_RCD AND A.EFFDT=B3.EFFDT ) ) ) AND C.EFFDT <= A.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PERSON

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

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

4 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number

5 CONTRACT_STATUS Character(1) VARCHAR2(1) NOT NULL Contract Status - The values 'N - N/A' and 'P - Pending' are suppressed from release 8.
A=Active
I=Inactive

6 CONTRACT_TYPE Character(3) VARCHAR2(3) NOT NULL Contract Type
Prompt Table: PERS_CNTRCT_TYP

7 CONTRACT_BEGIN_DT Date(10) DATE Contract Begin Date

8 CONTRACT_END_DT Date(10) DATE Contract End Date

9 CONTRCT_EXP_END_DT Date(10) DATE Contract Expected End Date

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
3	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
4	CONTRACT_NUM	Character(25)	VARCHAR2(25) NOT NULL	Contract Number
5	CONTRACT_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Contract Status - The values 'N - N/A' and 'P - Pending' are suppressed from release 8. A=Active I=Inactive
6	CONTRACT_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Contract Type Prompt Table: PERS_CNTRCT_TYP
7	CONTRACT_BEGIN_DT	Date(10)	DATE	Contract Begin Date
8	CONTRACT_END_DT	Date(10)	DATE	Contract End Date
9	CONTRCT_EXP_END_DT	Date(10)	DATE	Contract Expected End Date