OHF HCD Incremental View Logic Bug - HCD.V_ORDER_F_INCR Missing The Bridge Table HDM_ENC_ORD (Doc ID 2958526.1)

Last updated on JULY 13, 2023

Applies to:

Symptoms

Oracle Healthcare Foundation - Version 8.2 and later
Information in this document applies to any platform.

Problem Description
------------------------------
There is a bug in the view incremental logic for V_ORDER_F_INCR. Below is a snippet of the delta logic for this base object. It is missing the bridge table HDM_ENC_ORD. Customer may occasionally (very few occurrences) have the HDM_ORD early/before the HDM_ENC_ORD record comes into OHF. This will cause the ENCOUNTER_WID for W_HCD_ORDER_F to initially be "-1" for that record. However, customer would expect that once the HDM_ENC_ORD record shows up, it should update ENCOUNTER_WID on W_HCD_ORDER_F. But it doesn’t, because it is missing from the delta logic.

WITH
INCR_ID_WITH
AS
(SELECT ORD_ID
FROM HDM_ORD
WHERE HDM_ORD.CURRENT_FLG = 'Y'
AND UPDATE_DT > (SELECT EXTRCT_FRM_DT
FROM HCD_ETL_LOAD_DT_G
WHERE ETL_NM = 'W_HCD_ORDER_F')
UNION
SELECT ORD_ID
FROM HDM_ORD_RSN
WHERE HDM_ORD_RSN.CURRENT_FLG = 'Y'
AND HDM_ORD_RSN.UPDATE_DT >
(SELECT EXTRCT_FRM_DT
FROM HCD_ETL_LOAD_DT_G
WHERE ETL_NM = 'W_HCD_ORDER_F')
UNION
SELECT ORD_ID
FROM HDM_ORD_SVCPRV
WHERE HDM_ORD_SVCPRV.CURRENT_FLG = 'Y'
AND UPDATE_DT > (SELECT EXTRCT_FRM_DT
FROM HCD_ETL_LOAD_DT_G
WHERE ETL_NM = 'W_HCD_ORDER_F')
UNION
SELECT ORD_ID
FROM HDM_ORD, HDM_PT
WHERE HDM_ORD.CURRENT_FLG = 'Y'
AND HDM_PT.CURRENT_FLG = 'Y'
AND HDM_ORD.PT_ID = HDM_PT.PT_ID
AND HDM_PT.UPDATE_DT > (SELECT EXTRCT_FRM_DT
FROM HCD_ETL_LOAD_DT_G

EXPECTED BEHAVIOR
-----------------------
When loading an HDI_ORD record before an HDI ORD_ENC record expect the ENC WID be be updated from the default -1 value.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Load an HDI_ORD record before an HDI ORD_ENC record
2. Check ENC WID is updated from the default -1 value

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.