On 12.2.4 , Planning ODS Load Worker errors with ORA-00001: unique constraint (MSC.MSC_INTERORG_SHIP_METHODS_U1) while trying to update previously collected Legacy Data (Doc ID 2169866.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

On 12.2.4, Users are unable to update data that has been previously collected using Legacy Collections.

The The Planning ODS Load Worker Completes with the message: ORA-0001: Unique constraint (MSC.MSC_INTERORG_SHIP_METHODS_U1) violated


Following is the log of the MSCPDCW: Planning ODS Load Worker
Purge Batch Size is set to : .
MSC_DL_TASK_START_PARTIAL (PROCEDURE=MSC_CL_OTHER_ODS_LOAD.LOAD_SOURCING)
================================================================
Error in PROCEDURE=LOAD_SOURCING, TABLE=MSC_INTERORG_SHIP_METHODS
COLUMN=FROM_ORGANIZATION_CODE, VALUE=AC1:B91
COLUMN=TO_ORGANIZATION_CODE, VALUE=AC1:ES1
COLUMN=FROM_LOCATION_ID, VALUE=-1
COLUMN=TO_LOCATION_ID, VALUE=-1
COLUMN=SHIP_METHOD, VALUE=AIR
ORA-00001: unique constraint (MSC.MSC_INTERORG_SHIP_METHODS_U1) violated
0 minutes elapsed.


Following are the sequence of activities as performed by the user:
=====================================================

1) There are 2 ship methods have already been collected in a previous legacy data collection. Table MSC.MSC_INTERORG_SHIP_METHODS returns 4 lines, 2 for each plan (Plan ID = ‘-1’ (collection), and Plan ID = ‘22’). The requirement is to update the ship methods by assigning a cost.

2) Update of the legacy collection file ‘ShipmentMethod’ with the information such as : a) Weight Capacity b) Weight UOM and c) Cost per weight unit

3) The file is then uploaded using the following path: Collections --> Legacy Systems--> Collect Flat File Data - Self Service --> Select the File and click on (B) Start Load Now

4) It is found that the Planning ODS Load Worker Completes with Warning. The Log shows the following : ORA-0001: Unique constraint (MSC.MSC_INTERORG_SHIP_METHODS_U1) violated

5) Table MSC.MSC_ST_INTERORG_SHIP_METHODS returns a new row with the stuck ship method, giving error code ‘Default Ship Method is already defined from FROM_ORGANIZATION_CODE, FROM_LOCATION_CODE, FROM_REGION_CODE to TO_ORGANIZATION, TO_LOCATION_CODE, TO_REGION_CODE’

It is hence Impossible to update an existing ship method.

NB : the ship method is not assigned to any sourcing rule so it’s not currently in use.

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