Query Module Event Duration Unit Conversion Functionality Not Working

(Doc ID 796484.1)

Last updated on JULY 02, 2009

Applies to:

Adverse Event Reporting System - Version: 4.6.1.15
This problem can occur on any platform.

Symptoms

AERS 4.6.1.15

In the AERS Query Module, the Unit Conversion logic does not appear to be working after applying AERS iPatch 4.6.1.15, the supplied unit entered by user is not taken into account by the underlying SQL query.

Steps To Reproduce:

The issue can be reproduced at will in AERS 4.6.1.15 environment using the following steps:

  1. Launch AERS 4.6.1.15
  2. Open the Query Module 
  3. Navigate to the Events window 
  4. In Event Duration enter <2
  5. In Event Duration Unit enter DAY
  6. Blank out the default deleted criteria (just to make the SQL simple)
  7. Open the SQL window
  8. Note the resulting SQL is per below:

((SELECT USERENV('SESSIONID') , AE_CASES.CASE_ID

FROM AE_EVENTS ,AE_CASES

WHERE AE_CASES.CASE_ID= AE_EVENTS.CASE_ID

AND QueryPkg.IsCriteriaMet(UPPER(AE_EVENTS.DURATION),'2',UPPER(AE_EVENTS.DURATION_UNIT),'','<','TIME','FEW',4)= 1

AND UPPER(AE_EVENTS.DURATION_UNIT) = 'DAY'

AND 0 = CHECKBLINDED(AE_CASES.CASE_ID, AE_CASES.PROD_CD,
AE_CASES.STUDY_ID, AE_CASES.CASE_TYPE, '608951', 'Q', 'N') ))



Note 

The Call to QueryPkg.IsCriteriaMet is missing the unit and that there is an "extra" and clause for the unit AND UPPER(AE_EVENTS.DURATION_UNIT) = 'DAY'

Therefore execution of query does not retrieve correct results



Changes

AERS 4.6.1.15 has recently been applied

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