GPJP_RCD_4_SRCH (SQL View)	Index Back
Use to search Employee record This record was created for the following Reason: GJP8.9: Unable to save Social Insurance data if same effective date that employee is terminated in job date is used

GPJP_RCD_4_SRCH

(SQL View)

Use to search Employee record

This record was created for the following Reason: GJP8.9: Unable to save Social Insurance data if same effective date that employee is terminated in job date is used

SELECT DISTINCT B.EMPLID , B.EMPL_RCD , CASE WHEN B.EFFDT > A.EFFDT THEN B.EFFDT ELSE CASE WHEN A.HR_STATUS = 'I' THEN %Sql(FUNCLIB_HR_DATE_DAY_ADD,A.EFFDT,1) ELSE A.EFFDT END END , A.HR_STATUS , B.GPJP_IT_TYPE FROM PS_JOB A , PS_GPJP_IT_PYE_TAX B WHERE A.PAY_SYSTEM_FLG = 'GP' AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND ((A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = A.EMPLID AND EMPL_RCD = A.EMPL_RCD AND EFFDT <= B.EFFDT) AND A.EFFDT <= B.EFFDT) OR (B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GPJP_IT_PYE_TAX WHERE EMPLID = B.EMPLID AND EMPL_RCD = B.EMPL_RCD AND EFFDT <= A.EFFDT) AND B.EFFDT <= A.EFFDT))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

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

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 GPJP_IT_TYPE Character(2) VARCHAR2(2) NOT NULL Used in Income Tax Data.
10=KOU
20=OTSU
30=Designated Rate

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
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	GPJP_IT_TYPE	Character(2)	VARCHAR2(2) NOT NULL	Used in Income Tax Data. 10=KOU 20=OTSU 30=Designated Rate