Selection Criteria Too Broad In HCD.V_ENC_IND_SVC_PROV_FB_INCR View (Doc ID 2900705.1)

Last updated on JULY 13, 2023

Applies to:

Symptoms

Oracle Healthcare Foundation - Version 8.2 and later

ACTUAL BEHAVIOR
---------------
After upgrading to OHF 8.2, selection criteria is too broad in HCD.V_ENC_IND_SVC_PROV_FB_INCR view.

EXPECTED BEHAVIOR
-----------------------
Expect an update in the following section of the HCD.V_ENC_IND_SVC_PROV_FB_INCR view of the code which also increases view  performance.
"
ID_INCR_WITH
  AS
  (SELECT /*+ MATERIALIZE */
  ENC_ID
  FROM HDM_ENC_SVCPRV
  WHERE CURRENT_FLG = 'Y'
  AND UPDATE_DT >
  (SELECT EXTRCT_FRM_DT
  FROM HCD_ETL_LOAD_DT_G
  WHERE ETL_NM = 'W_HCD_ENC_IND_SVC_PROV_FB')
  UNION
  SELECT ENC_ID
  FROM HDM_ENC_SVCPRV, HDM_SVCPRV
  WHERE HDM_ENC_SVCPRV.CURRENT_FLG = 'Y'
  AND HDM_SVCPRV.CURRENT_FLG = 'Y'
  AND HDM_SVCPRV.UPDATE_DT >
  (SELECT EXTRCT_FRM_DT
  FROM HCD_ETL_LOAD_DT_G
  WHERE ETL_NM = 'W_HCD_ENC_IND_SVC_PROV_FB')
  AND HDM_ENC_SVCPRV.SVCPRV_ID = HDM_SVCPRV.SVCPRV_ID
"

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Examine selection criteria of HCD.V_ENC_IND_SVC_PROV_FB_INCR view
2. Suggested need improvement/update on the section of the code.

Cause

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