Patient Fact Table Is Not Accounting For Inactive Service Provider (Doc ID 2691553.1)

Oracle Healthcare Foundation - Version 7.1.1 and later

ACTUAL BEHAVIOR
---------------
The source view logic is not considering the delete flag from "HDM_PT_SVCPRV". Because of that, customer is getting the inactive PCPs in HCD patient fact table.

Following is the code snippet from the view,

IND_SVC_PROV_ROW_WID AS (
  (SELECT
  /*+ materialize */
  *
  FROM
  (SELECT DISTINCT HDM_PT_SVCPRV.PT_ID,
  W_HCD_IND_SVC_PROV_D.ROW_WID,
  ROW_NUMBER( ) OVER (PARTITION BY HDM_PT_SVCPRV.PT_ID ORDER BY NVL(HDM_PT_SVCPRV.SEQ_NBR,999999) ASC, NVL(HDM_PT_SVCPRV.STRT_DT,to_date('01-JAN-0001 23:59:59','DD-MON-YYYY HH24:MI:SS')) DESC, HDM_PT_SVCPRV.EFFECTIVE_FROM_DT DESC) SRLNO
  FROM W_HCD_IND_SVC_PROV_D ,
  HDM_PT_SVCPRV,
  HDM_SVCPRV,
  HDM_CD_REPOSITORY HCR,
  HCD_GLBL_PARAM_G GLOBAL_PARM,
  HCD_ETL_ENTY_SELCTN_PARAM_G ETL_G
  WHERE HDM_PT_SVCPRV.SVCPRV_ID = HDM_SVCPRV.SVCPRV_ID
  AND GLOBAL_PARM.PARAM_VALUE IS NULL
  AND GLOBAL_PARM.PARAM_NAME = 'MPI_RELTD_SVCPRV'
  AND HDM_SVCPRV.INTEGRATION_ID = W_HCD_IND_SVC_PROV_D.INTEGRATION_ID
  AND HDM_SVCPRV.DATASOURCE_NUM_ID = W_HCD_IND_SVC_PROV_D.DATASOURCE_NUM_ID
  AND HDM_PT_SVCPRV.CURRENT_FLG = 'Y'
  AND HDM_SVCPRV.CURRENT_FLG = 'Y'
  AND HCR.CURRENT_FLG ='Y'
  AND PT_SVCPRV_RLSHPTYP_ID =HCR.CD_ID
  AND HCR.CD =ETL_G.ATTRIB_VAL
  AND ETL_G.mstr_enty_nm ='W_HCD_PATIENT_F'
  AND ETL_G.ATTRIB_NM = 'PT_SVCPRV_RLSHPTYP_PRMRY_CARE_SVCPRV_CD'
  )
  WHERE SRLNO =1
  )


EXPECTED BEHAVIOR
-----------------------
Expect Patient Fact Table to account for inactive service provider.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Deploy Patient Data in to the warehouse.
2. Noticed source view logic is not considering the delete flag from "HDM_PT_SVCPRV".
3. Because of that, customer is getting the inactive PCPs in HCD patient fact table.