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 APRIL 04, 2025
Applies to:
Oracle Product Hub - Version 12.1.3 and laterInformation in this document applies to any platform.
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
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 |