My Oracle Support Banner

HCD Incremental View Improperly Loading Deleted DRGs Into W_HCD_ENCOUNTER_F Table (Doc ID 2819712.1)

Last updated on NOVEMBER 08, 2021

Applies to:

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

Symptoms

Oracle Healthcare Foundation - Version 8.0.1 and later

ACTUAL BEHAVIOR
---------------
HCD Incremental view "HCD.V_ENCOUNTER_F_INCR" is improperly loading deleted DRGs records into W_HCD_ENCOUNTER_F table.


drg delete flag needs considered in view HCD.V_ENCOUNTER_F_INCR

 DRG_CD_WITH
  AS
  (SELECT /*+ MATERIALIZE */
  X.ROW_WID, X.ENC_ID, X.CONG_NM
  FROM (SELECT ROW_NUMBER ()
  OVER (
  PARTITION BY HDM_ENC_DRG.ENC_ID,
  ATTRIB_NM
  ORDER BY
  NVL (HDM_ENC_DRG.SEQ_NBR, 999999) ASC,
  HDM_ENC_DRG.EFFECTIVE_FROM_DT DESC)
  SRLNO,
  HDM_ENC_DRG.ENC_ID,
  DRG_D_WITH.ROW_WID,
  (CASE ATTRIB_NM
  WHEN 'ENC_APRDRG_CDTYP' THEN 'APR_DRG'
  WHEN 'ENC_MSDRG_CDTYP' THEN 'MSG_DRG'
  WHEN 'ENC_APDRG_CDTYP' THEN 'AP_DRG'
  END)
  AS CONG_NM
  FROM HDM_ENC_DRG,
  HDM_CD_REPOSITORY HCR,
  DRG_D_WITH,
  HCD_ETL_ENTY_SELCTN_PARAM_G
  WHERE HDM_ENC_DRG.CURRENT_FLG = 'Y' -- ADD DELETE FLAG HERE
  AND HCR.CURRENT_FLG = 'Y'
  AND HDM_ENC_DRG.ENC_DRG_TYP_ID = HCR.CD_ID
  AND HCR.CD =
  HCD_ETL_ENTY_SELCTN_PARAM_G.ATTRIB_VAL
  AND ATTRIB_NM IN
  ('ENC_APRDRG_CDTYP',
  'ENC_MSDRG_CDTYP',
  'ENC_APDRG_CDTYP')
  AND MSTR_ENTY_NM = 'W_HCD_ENCOUNTER_F'
  AND DRG_D_WITH.CD_ID = HDM_ENC_DRG.DRG_CD_ID) X
  WHERE X.SRLNO = 1)

Error Codes
EXPECTED BEHAVIOR
-----------------------
Expect HCD Incremental view "HCD.V_ENCOUNTER_F_INCR" not to load deleted DRGs records into W_HCD_ENCOUNTER_F table. There should be considered HDM_ENC_DRG.DELETE_FLG='N' in the view to prevent that.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run view to load data into W_HCD_ENCOUNTER_F table.
2. Noticed deleted data being populated in W_HCD_ENCOUNTER_F table.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution


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