National ID/SSN columns HR Analytics W_EMPLOYEE_D dimension

By default, the Oracle BI Applications do not expose the columns for National Id/Social Security Number on the Employee dimension (W_EMPLOYEE_D).     Typically, there are policies in place within most organizations to closely control access to that information.

The physical columns W_EMPLOYEE_D are NATNL_ID_NUM, NATNL_ID_TYPE_CODE, NATNL_ID_TYPE_NAME.


They are exposed under the Dim - Employee logical table in the RPD but are not exposed at the presentation layer.

Those columns would have to be moved to a Presentation Subject area in order to make them available for reporting.   If necessary, separate security configuration may need to be applied to the column with application roles granting visibility.

The ETL mapping that  used to extract the National Id columns from Peoplesoft is:  SDE_PSFT_EmployeeDimension_Biography2, specifically in mapplet mplt_BC_PSFT_EmployeeDimension_Biography2.    It loads a table called W_PSFT_EMPLOYEE_D_BIO_2_TMP which is then loaded into the W_EMPLOYEE_DS staging table in the mapping SDE_PSFT_EmployeeDimension.

NOTE: Out of the box, the mapping only pulls the National Id's of employees with Work Permits.   It does not pull all Employee SSN's.     See below for a customization to bring in all SSN's.

The flow of the mapplet is from PS_PERS_NID to W_PSFT_EMPLOYEE_D_BIO_2_TMP for the National ID columns.

The SQL in the Source Qualifier in the mapplet can be modified to the following if all SSN/National Id values are required for all Employees versus just those on Work Permit:  (modified code in RED - just comment out the filter conditions).

SELECT
NID.EMPLID ,
CITIZENSHIP.COUNTRY,
NID.COUNTRY,
NID.NATIONAL_ID_TYPE ,
NID.NATIONAL_ID ,
CITIZENSHIP.CITIZENSHIP_STATUS,
VISA.COUNTRY,
VISA.VISA_PERMIT_TYPE,
VISA.DT_ISSUED,
VISA.EXPIRATN_DT,
'0' AS X_CUSTOM

FROM
PS_PERS_NID NID
LEFT OUTER JOIN
    (SELECT A.EMPLID, B.COUNTRY, C.CITIZENSHIP_STATUS
     FROM
     PS_CITIZENSHIP A, (SELECT EMPLID, MAX(COUNTRY) COUNTRY FROM
PS_CITIZENSHIP
                        WHERE (DEPENDENT_ID = ' ' )
                        GROUP BY EMPLID) B,
                        (SELECT EMPLID, COUNTRY, MAX(CITIZENSHIP_STATUS)
CITIZENSHIP_STATUS FROM PS_CITIZENSHIP
                        WHERE (DEPENDENT_ID = ' ' )
                        GROUP BY EMPLID, COUNTRY) C
     WHERE
     A.EMPLID = B.EMPLID
     AND A.COUNTRY = B.COUNTRY
     AND A.CITIZENSHIP_STATUS = C.CITIZENSHIP_STATUS
     AND B.EMPLID = C.EMPLID
     AND B.COUNTRY = C.COUNTRY
     AND (A.DEPENDENT_ID = ' ' )
    ) CITIZENSHIP ON
NID.EMPLID = CITIZENSHIP.EMPLID
LEFT OUTER JOIN PS_VISA_PMT_DATA VISA ON
NID.EMPLID=VISA.EMPLID
--AND NID.COUNTRY<>VISA.COUNTRY
WHERE
NID.PRIMARY_NID = 'Y'
--AND VISA.VISA_WRKPMT_STATUS in ('A','G')
--AND  (VISA.DEPENDENT_ID = ' ' OR
--LTRIM(RTRIM(VISA.DEPENDENT_ID)) IS NULL)
--AND (VISA.EFFDT = (SELECT MAX(EFFDT) FROM PS_VISA_PMT_DATA MAXI
--                   WHERE MAXI.EMPLID=VISA.EMPLID and MAXI.VISA_WRKPMT_STATUS in ('A','G') GROUP BY EMPLID) OR
--VISA.EFFDT IS NULL)