GPTH_PERNMW_VW

(SQL View)
 Index Back

Current Primary Name View

This view retrieves the currently effective Names record for all the Primary Name, used in workmen compensation report, remove duplicate from GPTH_PERSON_NM, used in workmen compensation report

SELECT A.EMPLID ,A.NAME_TITLE ,A.NAME_PREFIX ,%TrimSubstr(A.FIRST_NAME %Concat ' ' %Concat A.MIDDLE_NAME, 1, 61) ,A.FIRST_NAME ,A.MIDDLE_NAME ,A.LAST_NAME ,A.NAME_TYPE ,A.EFFDT ,G.PROCESS_INSTANCE FROM PS_NAMES A , PS_GPTH_RPT_G_VW G WHERE A.EMPLID=G.EMPLID AND A.NAME_TYPE = 'PRI' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = A.NAME_TYPE AND B.EFFDT <= G.PRD_END_DT ) GROUP BY A.EMPLID ,A.NAME_TITLE ,A.NAME_PREFIX ,A.FIRST_NAME ,A.MIDDLE_NAME ,A.LAST_NAME ,A.NAME_TYPE ,A.EFFDT ,G.PROCESS_INSTANCE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSON

2 NAME_TITLE Character(30) VARCHAR2(30) NOT NULL Title
3 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix

Prompt Table: NAME_PREFIX_TBL

4 GPTH_NAME1 Character(80) VARCHAR2(80) NOT NULL Tax-Payee's First Name
5 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
6 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
7 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
8 NAME_TYPE Character(3) VARCHAR2(3) NOT NULL Type of Name

Prompt Table: NAME_TYPE_TBL

9 ASOFDATE Date(10) DATE As of Date
10 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance