GPJP_PN_SCTY_VW

(SQL View)
 Index Back

Personal Number Japan (Report)

View used to retrieve emplids accessible to an Oprid by resolving the JPN Personal Number Security setup. This should be used only for reports. This view specifically allows access to JPN Personal Nbr even if the user is authorized a "View Only" access on JPN Personal Number security.

SELECT A.EMPLID , A.OPRID FROM PS_EMPLMT_SRCH_QRY A WHERE NOT EXISTS ( SELECT EMP.EMPLID FROM PS_PER_SEC_ADD_JPN EMP WHERE (EMP.NO_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 '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).