View For Attribute Group With Value Set Which Uses $OBJECT$ References: EGOCPAGV AGV Compilation Programs Fails if 2 Tables Joined With :$OBJECT$ (Doc ID 2004001.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Product Hub - Version 12.2.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.3 version,

Not able to Generate Database View for attribute group with value set which uses $OBJECT$ references

We have defined attribute groups with attributes having table type value set. In this value set we have used $OBJECT$.inventory_item_id and $OBJECT$.organization_id references.

While creating Database views using Generate Database view, the EGOCPAGV AGV Compilation Programs Fails if 2 Tables Joined With :$OBJECT$ its gives error 'ORA-00918: column ambiguously defined' even though the status of concurrent request shows that its successful. Error is attached.

Create view statement which is generated is not able to handle $OBJECT$ references. Hence it gives error that create statement has ambiguous columns defined.

Where clause of the value set is like: When we are creating value set, need to give below join condition as we
select tables MTL_SYSTEM_ITEMS_B MSIB, MTL_RELATED_ITEMS MRI.
 
MRI.ORGANIZATION_ID=:$OBJECT$.ORGANIZATION_ID AND
  MRI.INVENTORY_ITEM_ID=:$OBJECT$.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID=MRI.ORGANIZATION_ID
  AND MSIB.INVENTORY_ITEM_ID= MRI.INVENTORY_ITEM_ID


ERROR
-----------------------
ORA-00918


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
We are not able to generate database view for such attribute groups. We use views to fetch details of the attribute group. This issue is preventing us from using views in our code.

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