Patient Fact Table Is Not Accounting For Inactive Service Provider
(Doc ID 2691553.1)
Last updated on JULY 20, 2020
Applies to:
Oracle Healthcare Foundation - Clinical Module - Version 7.1.1 and laterInformation in this document applies to any platform.
Symptoms
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.
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.
Changes
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 |
Changes |
Cause |
Solution |