EMPLIDDATE_YEAR_VALGPJP_YEA_SEQGPJP_YEA_PEN_VW(SQL View) |
Index Back |
|---|---|
YEA - Personal Pension InsSQL view for retrieving Personal Pension Insurance data in Japanese imperial era format. |
| SELECT A.EMPLID ,A.DATE_YEAR_VAL ,A.GPJP_YEA_SEQ ,A.GPJP_YEA_INSTYPE ,A.GPJP_YEA_INSPROV ,A.GPJP_YEA_INSTERM ,A.GPJP_YEA_INSPOL ,A.GPJP_YEA_INSBENNM ,A.GPJP_YEA_INSBENREL ,A.GPJP_YEA_INSPREM ,A.GPJP_YEA_INSDATE ,%Substring(XLATSHORTNAME,1,1) ,CASE WHEN A.GPJP_YEA_INSDATE >= %Sql(HJP_MEIJI_BEGIN) THEN %Sql(FUNCLIB_HJP_TO_IMP_YYMMDD,A.GPJP_YEA_INSDATE) ELSE ' ' END FROM PS_GPJP_YEA_PENSN A , PSOPTIONS B , PSXLATITEM X WHERE B.LANGUAGE_CD = 'ENG' AND X.FIELDNAME = 'IMPERIAL_ERA_JPN' AND X.FIELDVALUE = %Sql(FUNCLIB_HJP_TO_IMP_ERA,A.GPJP_YEA_INSDATE) UNION SELECT A.EMPLID ,A.DATE_YEAR_VAL ,A.GPJP_YEA_SEQ ,A.GPJP_YEA_INSTYPE ,A.GPJP_YEA_INSPROV ,A.GPJP_YEA_INSTERM ,A.GPJP_YEA_INSPOL ,A.GPJP_YEA_INSBENNM ,A.GPJP_YEA_INSBENREL ,A.GPJP_YEA_INSPREM ,A.GPJP_YEA_INSDATE ,%Substring(XLATSHORTNAME,1,1) ,CASE WHEN A.GPJP_YEA_INSDATE >= %Sql(HJP_MEIJI_BEGIN) THEN %Sql(FUNCLIB_HJP_TO_IMP_YYMMDD,A.GPJP_YEA_INSDATE) ELSE ' ' END FROM PS_GPJP_YEA_PENSN A , PSOPTIONS B , PSXLATITEMLANG X WHERE B.LANGUAGE_CD <> 'ENG' AND X.FIELDNAME = 'IMPERIAL_ERA_JPN' AND X.LANGUAGE_CD = 'ENG' AND X.FIELDVALUE = %Sql(FUNCLIB_HJP_TO_IMP_ERA,A.GPJP_YEA_INSDATE) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | DATE_YEAR_VAL |
Number(4,0) | SMALLINT NOT NULL | Date Year Value |
| 3 | GPJP_YEA_SEQ |
Number(3,0) | SMALLINT NOT NULL | Sequence no. used in YEA feature |
| 4 | GPJP_YEA_INSTYPE | Character(30) | VARCHAR2(30) NOT NULL | YEA Insurance Type |
| 5 | GPJP_YEA_INSPROV | Character(30) | VARCHAR2(30) NOT NULL | YEA Insurance Provider |
| 6 | GPJP_YEA_INSTERM | Number(2,0) | SMALLINT NOT NULL | YEA Insurance Term |
| 7 | GPJP_YEA_INSPOL | Character(30) | VARCHAR2(30) NOT NULL | YEA Insurance POlicy |
| 8 | GPJP_YEA_INSBENNM | Character(30) | VARCHAR2(30) NOT NULL | YEA Ins Beneficiary Name |
| 9 | GPJP_YEA_INSBENREL | Character(10) | VARCHAR2(10) NOT NULL | YEA - Relationship to Employe |
| 10 | GPJP_YEA_INSPREM | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | YEA - Insurance Premium |
| 11 | GPJP_YEA_INSDATE | Date(10) | DATE | YEA - Date of Pension Payment |
| 12 | GPJP_CHRDT_ERA1 | Character(1) | VARCHAR2(1) NOT NULL | Imperial Era |
| 13 | GPJP_CHRDT_YYMMDD1 | Character(6) | VARCHAR2(6) NOT NULL | Character Date as YYMMDD |