EGOIREL: Import Related Items Via Interface MTL_RELATED_ITEMS_INTERFACE Fails With Error: Relationship type id 1 is not a valid relationship type (Doc ID 2043035.1)

Last updated on JUNE 15, 2021

Applies to:

Symptoms

Trying to create related items with interface table.

Error
Relationship type id 1 is not a valid relationship type.

What is working
Other types (like substitute item) work except type 1 / related item.

Steps
1. Insert relationship into interface.
2. Run interface (concurrent program Import Related Items).
3. Record fails with error.

Example Insert statement creating a related item between two items prompting for the part numbers (not item id but segment1), organization id, and user id:

INSERT INTO Mtl_Related_Items_Interface
(
Inventory_Item_Id ,
Organization_Id ,
Related_Item_Id ,
Relationship_Type_Id,
Reciprocal_Flag,
Transaction_Type,
Process_Flag,
Last_Update_Date,
Last_Updated_By ,
Creation_Date ,
Created_By
)
VALUES
(
(    SELECT inventory_item_id
    FROM apps.mtl_system_items_b
    WHERE organization_id = '&YourOrgID'
    AND segment1 = '&YourItemNumber1')
,'3'
,     (SELECT inventory_item_id
    FROM apps.mtl_system_items_b
    WHERE organization_id = '&YourOrgID'
    AND segment1 = '&YourItemNumber2')
,'1' -- 1=related
,'Y' -- Reciprocal = Yes
,'CREATE'
,'1' -- 1 = process
,sysdate
,'&UserID'
,sysdate
,'&UserID'
);

This is an example from an internal Vision environment where the item id 1555979 and 1555978 where related in V1 (organization id 204):

INSERT INTO MTL_RELATED_ITEMS_INTERFACE
(
INVENTORY_ITEM_ID   ,
RELATED_ITEM_ID     ,
RELATIONSHIP_TYPE_ID,
RECIPROCAL_FLAG     ,
ORGANIZATION_ID     ,
PROCESS_FLAG        ,
SET_PROCESS_ID      ,
TRANSACTION_TYPE
)
VALUES
(
1555979,--INVENTORY_ITEM_ID - 3-11025387111b
1555978,--RELATED_ITEM_ID - 3-11025387111
2             ,--RELATIONSHIP_TYPE_ID
'Y'            ,--RECIPROCAL_FLAG
204         ,--ORGANIZATION_ID
1             ,--PROCESS_FLAG
1000000   ,--SET_PROCESS_ID
'CREATE'  --TRANSACTION_TYPE
);

Log Details
Example log error:

EGOIREL module: Import Related Items
+---------------------------------------------------------------------------+

Current system time is 11-AUG-2015 08:57:02

+---------------------------------------------------------------------------+

**Starts**11-AUG-2015 08:57:03
**Ends**11-AUG-2015 08:57:05
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
Entering process Related Items Import.
Set Process Id :
Error Message for Transaction 141 : Relationship type id 1 is not a valid relationship type.
************************************
TRANSACTION ID : 141
ORGANIZATION ID :
TABLE NAME : MTL_RELATED_ITEMS_INTERFACE
COLUMN NAME :
MESSAGE NAME : EGO_REL_TYPE_NOT_VALID
ERROR MESSAGE : Relationship type id 1 is not a valid relationship type.
Processed 1 records.
Exiting Related Items Import.

Example Relationship Types

This SQL can be used to look at the available relationship types. All worked except the first option 1/Related.

SELECT FLV.LOOKUP_CODE, FLV.MEANING, FLV.DESCRIPTION, FLV.LANGUAGE
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'MTL_RELATIONSHIP_TYPES'
AND FLV.LANGUAGE = Userenv('LANG')
AND FLV.ENABLED_FLAG = 'Y' ;

Example output:

1    Related
2    Substitute
3    Cross-Sell
4    Up-Sell
5    Service
6    Prerequisite
7    Collateral
8    Superseded
9    Complimentary
10   Impact
11   Conflict
12   Mandatory Charge
13   Optional Charge
14   Promotional Upgrade
15   Split
16   Merge
17   Migration
18   Repair To
19   Regulatory

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.