ORA-01427 When Collecting ISOs in Demantra: (Doc ID 1414063.1)

Last updated on OCTOBER 17, 2016

Applies to:

Oracle Demantra Demand Management - Version 7.3.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 04-Dev-2013***

Symptoms


On : 7.3.0.1 version, LoadData

The Demantra request set for collecting internal sales orders is failing in production.
Within the request set for CCI_DM_I_01 Sales History from ICOMS to Demantra there is a process Populate Staging Tables with parameters: EQ_SALES_TMPL_ITEM, 1, 2
Whenever this request set is run to collect internal sales orders it errors with:


ERROR
-----------------------
Update the site codes to descriptive format
Updating Member Codes in Sales Data for SITE
ORA-01427: single-row subquery returns more than one row

Turns out that even though he found and deleted the duplicate records found in t_ep_sites, the Update Location Level Codes concurrent request still erred with the unique constraints error signifying duplicate records. After further investigation of the code for the concurrent request, found that it also is looking at the t_src_sales_tmpl staging table abd specifically at the EBS_SITE_SR_PK column.
Customer also duplicates records in staging tables, removed them, and then reran the Update Location Level Codes request.
This time it was successful.

Customer set up the profile option MSD_DEM :Use new site code format is set to "Yes"
Run Collect Shipment and Booking History with Collect Internal Orders set to yes.
Now it populates Populate Staging Request but the records are getting rejected with Duplicate attribute value - ebs_org_dest_key


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Collect Shipment and Booking History with Collect Internal Orders set to yes.
2. See the error in the log file


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Not able to use the forecast to plan replenishment of the distribution centers, this is delaying customer fulfillment.

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