PER_SRCH_JPN_VW

(SQL View)
 Index Back

Personal Number Japan

Maps Oprid to Employees with Personal Number based on the Personal Number Security setup.

SELECT A.EMPLID , A.OPRID , B.NAME , B.NAME_DISPLAY_SRCH , B.NAME_PSFORMAT , B.LAST_NAME_SRCH , B.SECOND_LAST_SRCH , B.FIRST_NAME , B.LAST_NAME , B.SECOND_LAST_NAME , B.NAME_AC , B.MIDDLE_NAME , B.NAME_DISPLAY FROM PS_EMPLMT_SRCH_QRY A , PS_PERSON_NAME B WHERE A.EMPLID=B.EMPLID AND NOT EXISTS ( SELECT EMP.EMPLID FROM PS_PER_SEC_ADD_JPN EMP WHERE (EMP.NO_ACCESS_FLAG = 'Y' OR EMP.VIEW_ACCESS_FLAG = 'Y') AND EMP.EMPLID = A.EMPLID AND ((EMP.ACCESS_TYPE_JPN='1' AND EMP.ACCESSTYPE_NAME=A.OPRID) OR (EMP.ACCESS_TYPE_JPN='2' AND EMP.ACCESSTYPE_NAME IN ( SELECT RU6.ROLENAME FROM PSROLEUSER RU6 WHERE RU6.ROLEUSER = A.OPRID)))) AND (EXISTS( SELECT NID.EMPLID FROM PS_PERS_NID_JPN NID WHERE NID.EMPLID = A.EMPLID) OR EXISTS ( SELECT DNID.EMPLID FROM PS_PERS_DNID_JPN DNID WHERE DNID.EMPLID = A.EMPLID)) AND ( (EXISTS( SELECT 'X' FROM PS_INSTALLATION WHERE INSTALLED_GP_JPN = 'Y') AND EXISTS ( SELECT 'X' FROM PS_PERS_SEC_L0_JPN CHK11 WHERE CHK11.ROW_SEC_ACCESS_JPN='Y' AND ((CHK11.ACCESSTYPE_NAME = A.OPRID AND CHK11.ACCESS_TYPE_JPN='1') OR (CHK11.ACCESS_TYPE_JPN = '2' AND CHK11.ACCESSTYPE_NAME IN ( SELECT RLU11.ROLENAME FROM PSROLEUSER RLU11 WHERE RLU11.ROLEUSER = A.OPRID ) ) ) ) ) OR (EXISTS( SELECT 'X' FROM PS_INSTALLATION WHERE INSTALLED_GP_JPN = 'Y') AND EXISTS ( SELECT 'X' FROM PS_PERS_SEC_L0_JPN CHK12 WHERE CHK12.ROW_SEC_ACCESS_JPN='N' AND ((CHK12.ACCESSTYPE_NAME = A.OPRID AND CHK12.ACCESS_TYPE_JPN='1') OR (CHK12.ACCESS_TYPE_JPN = '2' AND CHK12.ACCESSTYPE_NAME IN ( SELECT RLU12.ROLENAME FROM PSROLEUSER RLU12 WHERE RLU12.ROLEUSER = A.OPRID ) ) ) ) AND (EXISTS ( SELECT IT.EMPLID FROM PS_GPJP_IT_PYE_TAX IT WHERE EXISTS ( SELECT SEC1.TAX_ESTAB FROM PS_PER_SEC_JPN SEC1 WHERE SEC1.ESTAB_TYP = 'IT' AND SEC1.TAX_ESTAB = IT.GPJP_TAX_ESTAB AND ((SEC1.ACCESSTYPE_NAME = A.OPRID AND SEC1.ACCESS_TYPE_JPN='1') OR (SEC1.ACCESSTYPE_NAME IN ( SELECT RU.ROLENAME FROM PSROLEUSER RU WHERE RU.ROLEUSER = A.OPRID AND RU.ROLENAME IN ( SELECT ACC.ACCESSTYPE_NAME FROM PS_PER_SEC_JPN ACC WHERE ACC.ACCESS_TYPE_JPN = '2'))))) AND IT.EFFDT = ( SELECT MAX(ED_IT.EFFDT) FROM PS_GPJP_IT_PYE_TAX ED_IT WHERE ED_IT.EMPLID = IT.EMPLID AND ED_IT.EMPL_RCD = IT.EMPL_RCD AND ED_IT.GPJP_TAX_ESTAB = IT.GPJP_TAX_ESTAB AND ED_IT.EFFDT <= %CurrentDateIn) AND IT.EMPLID = A.EMPLID AND IT.EMPL_RCD = A.EMPL_RCD) OR EXISTS ( SELECT LA.EMPLID FROM PS_GPJP_LA_PYE_DTA LA WHERE EXISTS ( SELECT SEC2.TAX_ESTAB FROM PS_PER_SEC_JPN SEC2 WHERE SEC2.ESTAB_TYP = 'LA' AND SEC2.TAX_ESTAB = LA.GPJP_TAX_ESTAB AND ((SEC2.ACCESSTYPE_NAME = A.OPRID AND SEC2.ACCESS_TYPE_JPN='1') OR (SEC2.ACCESSTYPE_NAME IN ( SELECT RU1.ROLENAME FROM PSROLEUSER RU1 WHERE RU1.ROLEUSER = A.OPRID AND RU1.ROLENAME IN ( SELECT ACC1.ACCESSTYPE_NAME FROM PS_PER_SEC_JPN ACC1 WHERE ACC1.ACCESS_TYPE_JPN = '2'))))) AND LA.EFFDT = ( SELECT MAX(ED_LA.EFFDT) FROM PS_GPJP_LA_PYE_DTA ED_LA WHERE ED_LA.EMPLID = LA.EMPLID AND ED_LA.EMPL_RCD = LA.EMPL_RCD AND ED_LA.GPJP_TAX_ESTAB = LA.GPJP_TAX_ESTAB AND ED_LA.EFFDT <= %CurrentDateIn) AND LA.EMPLID = A.EMPLID AND LA.EMPL_RCD = A.EMPL_RCD) OR EXISTS ( SELECT SC.EMPLID FROM PS_GPJP_SC_PYE_DTA SC WHERE EXISTS ( SELECT SEC3.TAX_ESTAB FROM PS_PER_SEC_JPN SEC3 WHERE SEC3.ESTAB_TYP = 'SC' AND SEC3.TAX_ESTAB = SC.GPJP_TAX_ESTAB AND ((SEC3.ACCESSTYPE_NAME = A.OPRID AND SEC3.ACCESS_TYPE_JPN='1') OR (SEC3.ACCESSTYPE_NAME IN ( SELECT RU2.ROLENAME FROM PSROLEUSER RU2 WHERE RU2.ROLEUSER = A.OPRID AND RU2.ROLENAME IN ( SELECT ACC2.ACCESSTYPE_NAME FROM PS_PER_SEC_JPN ACC2 WHERE ACC2.ACCESS_TYPE_JPN = '2'))))) AND SC.EFFDT = ( SELECT MAX(ED_SC.EFFDT) FROM PS_GPJP_SC_PYE_DTA ED_SC WHERE ED_SC.EMPLID = SC.EMPLID AND ED_SC.EMPL_RCD = SC.EMPL_RCD AND ED_SC.GPJP_TAX_ESTAB = SC.GPJP_TAX_ESTAB AND ED_SC.EFFDT <= %CurrentDateIn) AND SC.EMPLID = A.EMPLID AND SC.EMPL_RCD = A.EMPL_RCD) OR EXISTS ( SELECT EMP_P.EMPLID FROM PS_PER_SEC_ADD_JPN EMP_P WHERE EMP_P.FULL_ACCESS_FLAG = 'Y' AND EMP_P.EMPLID = A.EMPLID AND ((EMP_P.ACCESSTYPE_NAME = A.OPRID AND EMP_P.ACCESS_TYPE_JPN='1') OR (EMP_P.ACCESS_TYPE_JPN='2' AND EMP_P.ACCESSTYPE_NAME IN ( SELECT RU3.ROLENAME FROM PSROLEUSER RU3 WHERE RU3.ROLEUSER = A.OPRID ))) ))) OR (EXISTS( SELECT 'X' FROM PS_INSTALLATION WHERE INSTALLED_GP_JPN = 'N') AND ((EXISTS( SELECT 'X' FROM PS_PERS_SEC_L0_JPN CHK WHERE CHK.ROW_SEC_ACCESS_JPN='N' AND ((CHK.ACCESSTYPE_NAME = A.OPRID AND CHK.ACCESS_TYPE_JPN='1') OR (CHK.ACCESS_TYPE_JPN = '2' AND CHK.ACCESSTYPE_NAME IN ( SELECT RLU.ROLENAME FROM PSROLEUSER RLU WHERE RLU.ROLEUSER = A.OPRID )))) AND EXISTS ( SELECT EMP_PP.EMPLID FROM PS_PER_SEC_ADD_JPN EMP_PP WHERE EMP_PP.FULL_ACCESS_FLAG = 'Y' AND EMP_PP.EMPLID = A.EMPLID AND ((EMP_PP.ACCESSTYPE_NAME = A.OPRID AND EMP_PP.ACCESS_TYPE_JPN='1') OR (EMP_PP.ACCESSTYPE_NAME IN ( SELECT RU7.ROLENAME FROM PSROLEUSER RU7 WHERE RU7.ROLEUSER = A.OPRID ))))) OR (EXISTS ( SELECT 'X' FROM PS_PERS_SEC_L0_JPN CHK1 WHERE CHK1.ROW_SEC_ACCESS_JPN='Y' AND ((CHK1.ACCESSTYPE_NAME = A.OPRID AND CHK1.ACCESS_TYPE_JPN='1') OR (CHK1.ACCESS_TYPE_JPN = '2' AND CHK1.ACCESSTYPE_NAME IN ( SELECT RLU.ROLENAME FROM PSROLEUSER RLU WHERE RLU.ROLEUSER = A.OPRID ))))))))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
3 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.
5 NAME_PSFORMAT Character(50) VARCHAR2(50) NOT NULL A Person's name in the Peoplesoft Format (basically LN,FN MI
6 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
7 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
8 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
9 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
10 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
11 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
12 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
13 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country