key for join between part_catalog and technical object reference

Is the following query what you are looking for?

select pc.part_no, tor.technical_class
  from ifsapp.part_catalog pc
 inner join ifsapp.TECHNICAL_OBJECT_REFERENCE tor
    on pc.part_no = ifsapp.client_sys.Get_Key_Reference_value(tor.key_ref, 'PART_NO')

Thanks a lot! Very close to it. I actually wanted to include it here. But a.part_no in the following is not working.

select 
a.part_no as "Part No",
Inventory_Part_Status_Par_API.Get_Description(a.part_status) as "Desc.",
a.unit_meas as "Inventory UoM",
(select tor.technical_class
  from ifsapp.part_catalog pc
 inner join ifsapp.TECHNICAL_OBJECT_REFERENCE tor
    on pc.part_no = ifsapp.client_sys.Get_Key_Reference_value(tor.key_ref, a.part_no)) as "Technical Class",
round (Inventory_Part_Unit_Cost_API.Get_Inventory_Value_By_Method(b.CONTRACT,b.PART_NO,b.CONFIGURATION_ID,b.LOT_BATCH_NO,b.SERIAL_NO),2) as "Cost per Part",
c.base_price_site as "Base Price Site",
c.base_price "Base Price",
Company_Finance_API.Get_Currency_Code(Site_API.Get_Company(c.BASE_PRICE_SITE)) as "Currency",
c.state as "Status Base Price"
FROM inventory_part_cfv a 
INNER JOIN inventory_part_unit_cost_sum b
ON a.contract = b.contract AND a.part_no = b.part_no
INNER JOIN
SALES_PART_BASE_PRICE c
ON a.contract = c.base_price_site AND a.part_no = c.catalog_no
INNER 
where 
a.contract = '111' and
('&[-C--L]catalog_no' LIKE '%' ||c.catalog_no||'%' OR c.catalog_no LIKE NVL('&[-C--L]catalog_no','%'))

But I cant figure out how to get the right value for ‘PART_NO’ in such a scenario. Do you have an idea?

I found a solution: 

For the ones who are interested and might have similar difficulties: 

select 
a.contract as "Site",
a.part_no as "Part No",
a.description as "Description", 
a.type_code as "Part Type",
a.PART_STATUS as "Part Status",
Inventory_Part_Status_Par_API.Get_Description(a.part_status) as "Desc.",
a.unit_meas as "Inventory UoM",
(select  
tor.technical_class 
from Part_catalog pc 
INNER JOIN TECHNICAL_OBJECT_REFERENCE tor 
ON pc.part_no =  (Regexp_substr(tor.key_value, '([^^]+)',1,1)) where pc.part_no = c.catalog_no) as "Technical Class",
TECHNICAL_CLASS_API.Get_Description((select  
tor.technical_class 
from Part_catalog pc 
INNER JOIN TECHNICAL_OBJECT_REFERENCE tor 
ON pc.part_no =  (Regexp_substr(tor.key_value, '([^^]+)',1,1)) where pc.part_no = c.catalog_no)) as "Technical Class Description",
a.FREQUENCY_CLASS as "Frequency Class",
a.part_product_code as "Product Code",
a.part_product_family as "Product Family",
a.CF$_PRIM_OWNER_SITE as "Primary Owner Site",
round (Inventory_Part_Unit_Cost_API.Get_Inventory_Value_By_Method(b.CONTRACT,b.PART_NO,b.CONFIGURATION_ID,b.LOT_BATCH_NO,b.SERIAL_NO),2) as "Cost per Part",
c.base_price_site as "Base Price Site",
c.base_price "Base Price",
Company_Finance_API.Get_Currency_Code(Site_API.Get_Company(c.BASE_PRICE_SITE)) as "Currency",
c.state as "Status Base Price"
FROM inventory_part_cfv a 
INNER JOIN inventory_part_unit_cost_sum b
ON a.contract = b.contract AND a.part_no = b.part_no
INNER JOIN
SALES_PART_BASE_PRICE c
ON a.contract = c.base_price_site AND a.part_no = c.catalog_no
where 
a.contract = '111' and
('&[-C--L]catalog_no' LIKE '%' ||c.catalog_no||'%' OR c.catalog_no LIKE NVL('&[-C--L]catalog_no','%'))