How to create a custom view to join multiple tables in Sterling OMS

 Introduction:

There can be a business requirement to fetch data from different tables from database and use it for further processing. For example - In Inventory console, we need to search and display data from extended attributes of yfs_item.

We can either write a custom logic to fetch the data from required tables and then do post processing to return the output in desired format. There is also a possibility to join required tables in a View. Latter one also requires customization but can be better performing.

Views can be created for out-of-the-tables and/or custom tables. Let's see how we can create custom views in IBM Sterling OMS.

In SQL, View is a virtual table based on the result-set of an SQL statement. It contains rows and column just like real tables. Fields in a view are fields from one or more real tables. You can explore further on View.

Implementation in Sterling:

In above example, we can create a view by joining yfs_item and yfs_inventory_item tables. DB framework will generate db classes and APIs on the view to retrieve the records.

Step 1: Create the View in the Database

CREATE VIEW EXTN_INV_ITEM_VW AS SELECT ITEM.ITEM_ID,INV_ITEM.UOM,ITEM.DEFAULT_PRODUCT_CLASS, INV_ITEM.PRODUCT_CLASS,ITEM.DESCRIPTION FROM YFS_ITEM ITEM,YFS_INVENTORY_ITEM INV_ITEM WHERE ITEM.ITEM_ID = INV_ITEM.ITEM_ID;

Once the View is created, you can check the if it's fetching the data properly as per your requirement or not and then make the necessary changes if required.

SELECT * FROM EXTN_INV_ITEM_VW;

Step 2: Update Entity extensions to create a new Entity the View

Go to <sterling_home>/extensions/global/entities/

You can update Extensions.xml or create a new file with the name EXTN_INV_ITEM_VW.xml

 

<DBSchema>   <Entities>     <Entity TableName="EXTN_INV_ITEM_VW"             Description="This view joins YFS_ITEM and YFS_INVENTORY_ITEM tables to enable querying based on given attributes"             View="true"             EntityType="VIEW"             HasHistory="False"             AuditRequired="N"             ApiNeeded="Y"             Prefix="EXTN" >       <Attributes>         <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="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_ID"                    Name="Item_Id"/>       </PrimaryKey>     </Entity>   </Entities> </DBSchema>

 

Step 3: Build entities.jar and smcfs.ear

Go to <sterling_home>/bin

Run deployer.cmd -t entitydeployer

This will generate the entities.jar with db classes and APIs on the view to retrieve records.

Run command to build smcfs.ear

Deploy latest smcfs.ear to your Application server 

Note 1: dbverify does not process or handle views. Any change made in the extension xml file for a view is not processed by dbverify. It means you will have to make and run any SQL related changes for your Views manually.

 

 Step 4: Configure Services to access Extended Database API

After successful build and deployment, Framework will generate 5 Extended Database APIs for the View, which you can access by creating Services (Custom APIs) for each one of them.

1. getEXTNInvItemVw
2. getEXTNInvItemVwList
3. createEXTNInvItemVw
4. changeEXTNInvItemVw
5. deleteEXTNInvItemVw

Note 2: Even though framework will generate the 5 db extended apis but classes or APIs generated for the view support the get and list API functions only, and cannot be used to modify, create or delete data.

Sample i/p and o/p for getEXTNInvItemVwList will be:

Input	Output
<EXTNInvItemVw />	<EXTNInvItemVwList> <EXTNInvItemVw DefaultProductClass="Good" Description="" ItemID="Laptop" ProductClass="Good" UnitOfMeasure="EACH"/> <EXTNInvItemVw DefaultProductClass="Good" Description="" ItemID="CPU" ProductClass="Good" UnitOfMeasure="EACH"/> </EXTNInvItemVwList>

 

 

 Step 5: Customize your console corresponding to view and Build/Deploy customizations

Customize the appropriate console to invoke the database APIs corresponding to the view.

Build and deploy your customization.

 

References:

https://www.ibm.com/docs/en/order-management-sw/10.0?topic=database-create-custom-view-join-multiple-tables

https://www.w3schools.com/sql/sql_view.asp