My Oracle Support Banner

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

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


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.