Inconsistent Behavior On The Late Arriving Rule Processing When Running WIL Workflow..

(Doc ID 2337329.1)

Last updated on JANUARY 10, 2018

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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms