Workforce Profile Bug

If you are using Oracle BI Application HR Analytics version 7.9.6.3, be aware of the following bug in the Workforce Profile subject area:

In the ETL mapping PLP_WorkforceEventGroupDimensionAggregate_Load there is incorrect SQL in the Source Qualifier which effects the incremental load. The SQL is over-ridden in the full load so this SQL is only run in the incremental load.

The SQL does a lookup to check if a row already exists in W_WRKFC_EVENT_GROUP_D for the key columns. However, the subquery to do the lookup has an incorrect join on one of the columns which results in all the rows being duplicated in W_WRKFC_EVENT_GROUP_D. This in turn impacts the W_WRKFC_EVT_A aggregate fact table causing additional incorrect rows to be inserted into the table.

Lookup SQL:

SELECT
DISTINCT
W_EVENT_GRP_CODE
,W_EVENT_SUBG_CODE
,DATASOURCE_NUM_ID
,TENANT_ID
FROM
W_WRKFC_EVENT_TYPE_D D
WHERE
    D.ETL_PROC_WID = $$ETL_PROC_WID AND
NOT EXISTS
    (
    SELECT 1
    FROM
        W_WRKFC_EVENT_GROUP_D AD
    WHERE
        D. W_EVENT_GRP_CODE = AD.W_EVENT_SUBG_CODE AND
        D. W_EVENT_SUBG_CODE = AD. W_EVENT_SUBG_CODE AND
        D.DATASOURCE_NUM_ID = AD.DATASOURCE_NUM_ID AND
        D.TENANT_ID = AD.TENANT_ID

)

In the above code, the line:

D.W_EVENT_GRP_CODE = AD.W_EVENT_SUBG_CODE AND

Should be:

D. W_EVENT_GRP_CODE = AD.W_EVENT_GRP_CODE AND