VCP 12.2.4.1 OR VCP 12.2.5 with 12c RDBMS - Double Wip Job Demand Seen In The Collected Data (Doc ID 2112920.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.2.4 to 12.2.5 [Release 12.2]
Information in this document applies to any platform.

Symptoms

12.2.4.1 in Production
RDBMS is 12.1.0.2.0
 - Was also reported on 12.2.5

PROBLEM
-------------
Found double wip job component demands in our ASCP plan today.
Had call with support and got SQL outputs for msc_supplies and msc_demands.
In those outputs, we can see double wip job demands are in the collected data - so this is collections problem.

1. SO - ran SQL to check EBS source snapshots and views
10-DEC late afternoon EST - got back results showing this was showing up on EBS Source as double demand in snapshot for the job in question
see file - dc_wdj_1041349.zip - which is output of dc_wdj.sql from Note 558477.1

2. So had them run normal collections and check after plan run overnight
Again they find double data in the collected data
see file - dc_entity_155023411.zip from 11-DEC
This DOES SHOW double demand in file - MRP_SN_WREQ_OPRS.csv (this is query against the synonym of snapshot WIP_WREQ_OPRS_SN)
and so we also see it in the view - MRP_AP_WIP_COMP_DEMANDS_V.csv

3. So had them check for how many of these demands are doubled using SQL
see file - MRP_SN_WREQ_OPRS.xls

output of SQL
select count(*),
INVENTORY_ITEM_ID,
MPS_DATE_REQUIRED,
MPS_REQUIRED_QUANTITY,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
ORGANIZATION_ID
from MRP_SN_WREQ_OPRS (this is synonym of snapshot - WIP_WREQ_OPRS_SN)
group by INVENTORY_ITEM_ID,
MPS_DATE_REQUIRED,
MPS_REQUIRED_QUANTITY,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
ORGANIZATION_ID
having count(*) >1;

4. Also checked how they are running Refresh Snapshots on the EBS source instance.
see file - Collection_Snapshots_KEPR.xls

We see they are launching Refresh Snapshots as a standalone request two times each day 1600 and 2200
and then for regular collections at 1 AM
09-DEC-2015 22:00:25 / 1, ALL SNAPSHOTS, , 0 <<<< THIS IS FAST REFRESH
09-DEC-2015 16:00:23 / 2, ALL SNAPSHOTS, , 0 <<<09-DEC-2015 00:01:18 / 4, COLL SNAPSHOTS, 0, 0, 1, C, 1, 63, KEP, KEPR_TO_KVCPPROD <<< THIS IS FORCE REFRESH during standard collections

SO -- Refresh Snapshot Process works as follows:
A> Main request launches - Refresh Collection Snapshots
B> This launches 50 child requests to refresh each snapshot individually (APS Collections Refresh Snapshot Thread)
C> When all child refresh requests are completed, then main request does one more refresh of ALL the snapshots as atomic transaction for read consistency.

5 Suspect the following:
The Fast Refresh at 2200 is might be the cause or the Force refresh might be the problem - but more likely is the Fast
I think we did not see the issue yesterday in late afternoon because the 1600 run with Complete Refresh fixed the data problem we would see today before the Complete Refresh is run at 1600

EXPECTED BEHAVIOR
----------------------------
no double demands and double planned orders in ASCP plan output

BUSINESS IMPACT
----------------------
cannot use this plan output
PROD Down for business.
Very critical as we have had several issues lately and been unable to plan properly for several days

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