Release 39

by

For the resolution of a serious duplicate/bug

project_details_plus

Observation

EMPLOYEE_DETAILS_OL contains duplicates for PERSON_ID within in the same company

Identified Actions

Our immediate action is to gather more details from [QDIM_PERSON_INFO_OL$], and drop link to EMPLOYEE_DETAILS_OL for manager details, and resolve the in DataMart duplicate.

Please could you verify if Colleen Martin (1000602) are valid entries in a key DIM table

Warning…

#Martyn Cole … src.PROJECT_ID in (‘D101343’, ‘D101534’, ‘N100025’) may contain duplicates in Project.Details_Plus 202409 (all managed by Colleen Martin)

Expected Condition (Assumption)

PERSON_ID should uniquely identify a person object within a company. This combination acts as an Employee Code and should appear only once per company.

As Found Condition

Duplicates of PERSON_ID were found within a company in the EMPLOYEE_DETAILS_OL table.

Gap/Consequence

As no active flag, or similar, is provided, it is impossible to distinguish between different entries. This leads to duplicates when passing through this table.

Steps to Reproduce

The issue was identified when linking a Project Manager to their name and employee details:

SELECT 

    src.COMPANY,
    src.PROJECT_ID,

   src.MANAGER,
   manager_label.EMPLOYEE_NAME AS Project_Manager_Name,
   CONCAT_WS(' - ', src.MANAGER, manager_label.EMPLOYEE_NAME) AS Project_Manager_Title,
   employee_email.EMAIL AS Project_Manager_Email  -- Project Manager Email linked via Employee.Detail_Plus

FROM ifs.DIM_PROJECT_OL$ AS src

    -- Project Manager Details
    LEFT JOIN ifs.DIM_EMPLOYEE_OL AS manager_label
        ON manager_label.PERSON_ID = src.MANAGER
        AND manager_label.COMPANY = src.COMPANY
    LEFT JOIN (
        SELECT PERSON_ID,[VALUE] as email
        FROM [ifs].[QDIM_PERSON_INFO_OL$] as person
        LEFT JOIN 
        (SELECT [IDENTITY],[VALUE] FROM [ifs].[QDIM_FND_USER_PROPERTY_OL$] where [NAME] = 'SMTP_MAIL_ADDRESS') as prop
        ON prop.[IDENTITY] = person.[USER_ID]
        ) AS employee_email
        ON employee_email.PERSON_ID = src.MANAGER

  WHERE
   src.MANAGER = '1000602'

Immediate Actions

Our immediate action is to gather more details from [QDIM_PERSON_INFO_OL$], and drop link to EMPLOYEE_DETAILS_OL for manager details, and resolve the duplicate.

Please could you verify if Colleen Martin (1000602) are valid entries in a key DIM table

Notes

Files sql/svw/project/project_details_plus.sql (DEV) and sql/svw/project/project_details_plus.sql (PRD) are different.
Files sql/svw/project/202404/project_details_plus_202404.sql (DEV) and sql/svw/project/202404/project_details_plus_202404.sql (PRD) are different.
Files sql/svw/project/202406/project_details_plus_202406.sql (DEV) and sql/svw/project/202406/project_details_plus_202406.sql (PRD) are different.
Files sql/svw/project/202407/project_details_plus_202407.sql (DEV) and sql/svw/project/202407/project_details_plus_202407.sql (PRD) are different.

Leave a Comment