Different Entities Data – Single Applet (ReadOnly)

In one of my project there was a requirement when business user asked us to show the data in read only mode which resides in three different tables. There are multiple approachs to fullfill this requirement but in this post I’ll explain the one which we used that time.

Approach goes like below.    

Ø  Create a Database view e.g. ‘SiebelCrmTipView’. The process of creating a new Database view is following: 

• Start SQL Developer and connect to your database.
• Check if you have sufficient rights to create and modify a database view.
• Create a Database view. Below is a sample SQL query used to create a sample database view.

Create view SiebelCrmTipView as select p.PostNo, p.Title, p.PublishDt, l.LikedDt, l.LikedBy, l.TotalLikes from Post p, Likes l where p.PostId=l.ParPostId;

• Write a similar SQL query based upon your requirement to create the database view.
• Also run the below mentioned SQL statement to grant SELECT access to other user on the database view:

Grant select on siebel.SiebelCrmTipView to SSE_ROLE;

Ø  Now, Go to your database connection and navigate to ‘Views’. You can see the newly created database view e.g. ‘SiebelCrmTipView’ under ‘Views’ tab. One can see ‘Columns’, ‘Data’, ‘Grant’, Dependencies’, ‘Details’ and the SQL details of the database view here.

Ø  After creating database views on physical database, create a table in Siebel tools with the same name as of the Database view i.e. ‘SiebelCrmTipView’.

Note: The ‘Type’ of this table should be ‘Virtual Table’ as it is mandatory.

Ø  Now, create all the columns as it is mentioned while creating the ‘Database view’.

Ø  Check In the newly created table ‘SiebelCrmTipView’ to Server.

Note: No need to Apply and Activate the table as it is a database view and it is already created on the physical schema. 

Ø  Now, create a business component on the table ‘SiebelCrmTipView’. Also, create all the mandatory fields based upon the column in the table ‘SiebelCrmTipView’. 

Ø  Create a new applet to expose these fields on the user interface and Add that applet into a view. Compile the changes and enjoy.

Hope this helps you and Your comments are always welcome… Happy Siebeling… J