GPJP_DEPEND_VW(SQL View) |
Index Back |
|---|---|
Personal DataPersonal data with birth date in Japanese imperial era format. |
| SELECT DB.EMPLID ,DBE.DEPENDENT_BENEF ,DBE.EFFDT ,DB.BIRTHDATE ,CASE WHEN DB.BIRTHDATE >= %Sql(HJP_MEIJI_BEGIN) THEN %Sql(FUNCLIB_HJP_TO_IMP_ERA,DB.BIRTHDATE) ELSE ' ' END ,CASE WHEN DB.BIRTHDATE >= %Sql(HJP_MEIJI_BEGIN) THEN %Sql(FUNCLIB_HJP_TO_IMP_ERA_SI,DB.BIRTHDATE) ELSE ' ' END ,CASE WHEN DB.BIRTHDATE >= %Sql(HJP_MEIJI_BEGIN) THEN %Sql(FUNCLIB_HJP_TO_IMP_YYMMDD,DB.BIRTHDATE) ELSE ' ' END ,DB.DT_OF_DEATH ,DBE.RELATIONSHIP ,DBE.MAR_STATUS ,DBE.MAR_STATUS_DT ,DBE.SEX FROM PS_DEP_BEN DB , PS_DEP_BEN_EFF DBE WHERE DB.EMPLID = DBE.EMPLID AND DB.DEPENDENT_BENEF = DBE.DEPENDENT_BENEF |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | DEPENDENT_BENEF |
Character(2) | VARCHAR2(2) NOT NULL |
Dependent Benefit
01=default |
| 3 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 4 | BIRTHDATE | Date(10) | DATE | Date of Birth |
| 5 | GPJP_CHRDT_ERA1 | Character(1) | VARCHAR2(1) NOT NULL | Imperial Era |
| 6 | GPJP_CHRDT_ERA2 | Character(1) | VARCHAR2(1) NOT NULL | Imperial Era |
| 7 | GPJP_CHRDT_YYMMDD1 | Character(6) | VARCHAR2(6) NOT NULL | Character Date as YYMMDD |
| 8 | DT_OF_DEATH | Date(10) | DATE | Date of Death |
| 9 | RELATIONSHIP | Character(2) | VARCHAR2(2) NOT NULL | Relationship to Employee |
| 10 | MAR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Marital Status
C=Common-Law D=Divorced E=Separated H=Head of Household L=DissDeclLost Civil Partner M=Married P=Civil Partnership S=Single T=Surviving Civil Partner U=Unknown V=Dissolved Civil Partnership W=Widowed Default Value: U |
| 11 | MAR_STATUS_DT | Date(10) | DATE | This is the date that the employee's Marital status is/was effective. /* Marital Status date is stored redundantly on PERS_DATA_EFFDT for two reasons. 1. To allow the date to be entered prior to the first PERS_DATA_EFFDT so that the actual date for the marital status on the hire row can be entered. This is the only time that the field is enterable by the user. 2. To allow for easier reporting on the data via SQR and Crystal. This is denormalizing the PERS_DATA_EFFDT record, but the number of requests for this redundancy coupled with the need for #1, has provided the justification for denormalizing this information and taking on the added maintenance. */ |
| 12 | SEX | Character(1) | VARCHAR2(1) NOT NULL |
Gender
F=Female M=Male U=Unknown |