My Oracle Support Banner

Inconsistent Behavior On The Late Arriving Rule Processing When Running WIL Workflow.. (Doc ID 2337329.1)

Last updated on FEBRUARY 20, 2019

Applies to:

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

Symptoms

On : 7.1.1 and OHF 7.1.2 (WIL)

ACTUAL BEHAVIOR
---------------
When running a WIL workflow, the source query which consolidates the late arriving rules for the interface table is missing the "order by" clause. And therefore, it returns the data in different order between the environments.

SELECT COUNT(*) OVER() AS SRC_CNT,
 ROW_NUMBER() OVER (ORDER BY ETL_RL_ID) AS SEQ,
 ETL_RL_ID,
 INTRFC_TBL_NM,
 RL_DSN_ID,
 TARG_TBL_NM AS REFERENCE_TBL_NAME,
 APPEND_RI,
 ACTN_PERFMD,
 MAX_SUSPNS_RETRY_CNT,
 NULL_COND
FROM
 (SELECT MIN(ETL_RL_ID) AS ETL_RL_ID,
  MIN(INTRFC_TBL_NM) AS INTRFC_TBL_NM,
  MIN(TARG_TBL_NM) AS TARG_TBL_NM,
  MIN(MAX_SUSPNS_RETRY_CNT) AS MAX_SUSPNS_RETRY_CNT,
  MIN(ACTN_PERFMD) AS ACTN_PERFMD,
  MIN(LST) AS APPEND_RI,
  MIN(RL_DSN_ID) AS RL_DSN_ID,
  MIN(NULL_COND) AS NULL_COND
 FROM
  (SELECT HDI_ETL_RL_G.ETL_RL_ID,
  HDI_ETL_RL_G.INTRFC_TBL_NM,
  HDI_ETL_RL_REFRNTL_INTGRTY_G.INTRFC_COL_NM,
  HDI_ETL_RL_REFRNTL_INTGRTY_G.TARG_TBL_NM,
  HDI_ETL_RL_REFRNTL_INTGRTY_G.TARG_COL_NM,
  HDI_ETL_RL_REFRNTL_INTGRTY_G.MAX_SUSPNS_RETRY_CNT,
  HDI_ETL_RL_REFRNTL_INTGRTY_G.ACTN_PERFMD,
  HDI_ETL_RL_REFRNTL_INTGRTY_G.COMP_RL_IDNTFR,
  LISTAGG(HDI_ETL_RL_REFRNTL_INTGRTY_G.TARG_TBL_NM
  ||'.'
  ||HDI_ETL_RL_REFRNTL_INTGRTY_G.TARG_COL_NM
  ||'='
  ||HDI_ETL_RL_G.INTRFC_TBL_NM
  ||'.'
  ||HDI_ETL_RL_REFRNTL_INTGRTY_G.INTRFC_COL_NM, ' AND ') WITHIN GROUP (
  ORDER BY HDI_ETL_RL_G.ETL_RL_ID) OVER (PARTITION BY HDI_ETL_RL_REFRNTL_INTGRTY_G.TARG_TBL_NM , HDI_ETL_RL_REFRNTL_INTGRTY_G.COMP_RL_IDNTFR) AS LST,
  LISTAGG(HDI_ETL_RL_G.INTRFC_TBL_NM
  ||'.'
  ||HDI_ETL_RL_REFRNTL_INTGRTY_G.INTRFC_COL_NM
  || ' IS NOT NULL ', ' AND ') WITHIN GROUP (
  ORDER BY HDI_ETL_RL_G.ETL_RL_ID) OVER (PARTITION BY HDI_ETL_RL_REFRNTL_INTGRTY_G.TARG_TBL_NM , HDI_ETL_RL_REFRNTL_INTGRTY_G.COMP_RL_IDNTFR) AS NULL_COND,
  DECODE(DATASOURCE_NUM_IDS, NULL,'' ,'DATASOURCE_NUM_IDS='
  || SUBSTR(DATASOURCE_NUM_IDS,1


EXPECTED BEHAVIOR
-----------------------
Expected the query to have "order by" clause in order the data to be returned in the same order between the environments.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run a WIL workflow
2. Data is returned in different order between the environments.

 

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
References


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