A custom view can be created to join multiple Oracle and DB2® tables. This approach can be used for either out-of-the-box or custom tables.
Product APIs are then used to query from the custom views. Custom queries are not required.
A business case necessitates an inventory item search (inventory console) based on extended attributes of yfs_item. Such a search requires the yfs_item and yfs_inventory_item tables to be joined in a view, but yfs_inventory_item is not extensible.
The view entity is defined as shown in the ExtnCatalogSearchConfigProperties.xml file below, and the inventory console is customized to invoke the database APIs corresponding to the view.
The following is the sample ExtnCatalogSearchConfigProperties.xml file:
<DBSchema>
<Entities>
<Entity TableName="EXTN_INV_ITEM_VW"
Description="This view joins YFS_ITEM and YFS_INVENTORY_ITEM
tables to enable querying based on webclass and subclass
attributes"
View="true"
EntityType="VIEW"
HasHistory="False"
AuditRequired="N"
ApiNeeded="Y"
Prefix="EXTN" >
<Attributes>
<Attribute ColumnName="ITEM_INV_VW_KEY"
DataType="Key"
DefaultValue="' '"
Description="Primary key for this view."
Nullable="false"
XMLName="ItemInVwKey"/>
<Attribute ColumnName="ITEM_ID"
DataType="ItemID"
Description="Identifer for this inventory item."
Name="Item_Id"
Nullable="false"
XMLName="ItemID"
DefaultValue="' '" />
<Attribute ColumnName="UOM"
DataType="UOM"
Description="Unit of measure for this inventory item."
Name="Uom"
Nullable="false"
XMLName="UnitOfMeasure"
DefaultValue="' '" />
<Attribute ColumnName="EXTN_SUBCLASS"
DataType="VARCHAR2-24"
Type="VARCHAR2"
Size="24"
DefaultValue="' '"
Nullable="false"
XMLName="ExtnSubclass"/>
<Attribute ColumnName="DEFAULT_PRODUCT_CLASS"
DataType="ProductClass"
DefaultValue="' '"
Description="Default product class of an item."
Name="Default_Product_Class"
Nullable="false"
XMLName="DefaultProductClass"/>
<Attribute ColumnName="PRODUCT_CLASS"
DataType="ProductClass"
DefaultValue="' '"
Description="Product class for the item of this
inventory audit."
Name="Product_Class"
Nullable="false"/>
<Attribute ColumnName="DESCRIPTION"
DataType="ItemDesc"
DefaultValue="' '"
Description="Description of the item."
Name="Description"
Nullable="false"
XMLName="Description"/>
</Attributes>
<PrimaryKey>
<Attribute ColumnName="ITEM_INV_VW_KEY"
Name="Item_Inv_Vw_Key"/>
</PrimaryKey>
</Entity>
</Entities>
</DBSchema>