12.2.4.1 - New User Exception Query With Among Condition Returns Records Outside The Filter (Doc ID 2118819.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

12.2.4.1

PROBLEM
-------------
1. Query Past Due Orders with Planner AMONG - Planner1 and Planner2 does not work
-- gives back all past due orders (count 1441)

2. Query Past Due orders with Planner EQUAL - Planner1
- gives back count = 133

3. Query Past Due orders with Planner EQUAL - Planner2
- gives back count = 264

SO - #1 using AMONG should have returned 133+264 = 397 instead of 1441 exceptions

Check details and see other planners.

Check trace and see that planner is not included in the query to load the data into MSC_PQ_RESULTS

INSERT INTO MSC_PQ_RESULTS ( QUERY_ID, PLAN_ID, ORGANIZATION_ID,
  SR_INSTANCE_ID, SUMMARY_DATA,source_type , sequence_id, EXCEPTION_TYPE,
  EXCEPTION_ID,created_by, creation_date, last_update_date, last_updated_by,
  last_update_login) select distinct -126008, nvl(plan_id,-1),
  nvl(organization_id,-1), nvl(sr_instance_id,-1), 2,1,10,EXCEPTION_TYPE,
  EXCEPTION_ID,1140,sysdate,sysdate,1140, null FROM MSC_EXCEPTION_DETAILS_V
  where plan_id = 7021 and ( ( ( exception_type = 10 AND source_type = 1
  ) AND ( ( ORGANIZATION_ID = 411 AND SR_INSTANCE_ID = 1 ) AND ( 11=11 )
  )) ) and ( source_type = 1 and exception_type = 10)


EXPECTED BEHAVIOR
----------------------------
AMONG will work and only return correct exceptions


BUSINESS IMPACT
----------------------
cannot use this query

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