Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Tuesday, September 20, 2011

03/23 FORMS - Tab canvas, Master-Detail relationship

Master-Detail Relationship

A master/detail relationship or parent/child relationship is a relationship between two data blocks. A primary key of a master table is referenced by a foreign key in the detail table.

Tab canvas, Master-Detail relationship (Hands-On)

Hands-on introduction

Having performed Fact Finding, and determine the user's initial requirements, you are ready to create a data entry form based on those requirements. Keeping in mind that this is an iterative process, and the User is closely involved in the design. During your interviews, you have identified that tables Customer, Ord, and Items will be required, and you have an idea of the "Look-and-Feel" of the Form.

Your client asks you to create an easy to use data entry Form for their “Customer Order” data entry application.

See Figure 1, 2, and 3 for screen layout requirements.

Your tasks are:

1- Create a Form that contains three tabs for each Entity. The tabs should be labeled as "Customer", "Orders", and "Items

2- Develop a "Master-Detail" relationship using the "Tab Canvas" to establish a relationship between "Customer to Order" and "Order to Items."

3- Review your iterations with the User as prototypes of the final product.

4- Enhance your prototype. Working with you, the User has requested a more robust application.

5- Run and test all user functional requirements.

In this Hands-On, you will learn how to use: tab canvas, “object navigator,” “Data Blocks,” “Layout Editor,” “Property Palette,” “Run Form,” “Execute Query,” “Auto-Join data blocks,” and Master-Detail relationship.

Figure 1

Figure 2

Figure 3

Create a Module

In the ‘Object Navigator’ window, highlight module1. This is a default name. Go to the Main menu and choose “File,” select “Save as” to store the new object in the “iself” folder and save it as customer order. "c:_orders."

Create a Data Block

In the ‘Object Navigator’ window, highlight "Data Blocks,” and click on the "create” icon. The ‘Create’ icon is in the vertical tool bar in the ‘Object Navigator’ window. It is a green ‘+’ sign.

New Data Block

In the ‘New Data Block’ window, choose the default option “Data Block Wizard” and click "OK."

Welcome Data Block

In the ‘Welcome Data Block Wizard’ window click “NEXT.”

Type of Data Block

Select the type of data block you would like to create by clicking on a radio button. Select the default option ‘Table or View’ and then click “NEXT” again.

Selecting Tables

Click on “browse.” In the ‘Tables’ window, highlight the "customer” table; then click "OK."

Selecting columns for the Data Block Wizard

To choose all columns, click on the two arrow signs in the ‘Data Block Wizard’ window. To choose selected columns, click on the one arrow sign. For this hands-on exercise select all columns, and click “next.”

Layout Wizard

End of the Data Block Wizard and beginning of the Layout Wizard

In the ‘Congratulations’ screen, use the default checkmark radio button (Create the data block, then call the Layout Wizard), and click "Finish." You can also use the Data Block Wizard to modify your existing data block. Simply select the data block in the Object Navigator and click the Data Block Wizard toolbar button, or choose ‘Data Block wizard’ from the ‘Tools’ menu.

Welcome screen

In the ‘Welcome to the Layout Wizard’ window, click ”Next.”

Selecting canvas

In the ‘Layout Wizard’ window, select the "new canvas" option. Canvas is a place that you will have your objects such as columns, titles, pictures, etc. If you have already have your canvas, select the canvas and then click on the next.

The following are different types of canvases: Content, Stacked, Vertical Toolbar, Horizontal Toolbar, and Tab. Think of the ‘Content’ canvas as one flat place to have all your objects. In the stacked canvas, you can have multiple layers of objects and it is the same as the tab canvas. You use the vertical or horizontal toolbar canvases for your push buttons. Check the different types of canvases by clicking on the ‘down arrow’ box next to the ‘Type’ field. Select "Tab Canvas," then click “Next.”

Selecting Columns for the Layout Wizard

In the ‘Layout Wizard’ window, select all the columns. These are the columns that you want to display them on the canvas. Then click “Next.”

Change your objects appearances

Change size or prompt if needed. In this window, you can enter a prompt, width, and height for each item on the canvas. You can change the measurement units. As a default the default units for item width and height are points. You can change it to inch or centimeter. When you change size, click “Next.”

Selecting a layout style

Select a layout style for your frame by clicking a radio button. Select "Form," if you want one record at a time to be displayed. Select “Tabular,” if you want more than one record at a time to be displayed. Select "Forms," and then click “next.”

Record layout

Type the Frame Title, Records Displayed, Distance Between Records values and checkmark the ‘Display Scrollbar’ box, when you use multiple records or the ‘Tabular’ option. Then click “Next.”

Congratulation Screen

In the ‘Congratulations’ window, click "Finish."

Now you should see the form layout.

Create another new tab page for the customer's order.

Data Block

In the ‘Object Navigator’ window, highlight "Data Blocks,” and click on the "create” icon.

New Data Block

In the ‘New Data Block’ window, choose the default option “Data Block Wizard” and click "OK."

Welcome Data Block

In the ‘Welcome Data Block Wizard’ window click “NEXT.”

Type of Data Block

Select the type of data block you would like to create by clicking on a radio button. Select the default option ‘Table or View’ and then click “NEXT” again.

Selecting Tables

Click on “browse.” In the ‘Tables’ window, highlight the "ord” table; then click "OK." You can also type ‘ord’ and then click on the "refresh” button.

Selecting columns for the Data Block Wizard

To choose all columns, click on the two arrow signs in the ‘Data Block Wizard’ window and then click “next.”

Relationships

You may optionally create and delete master-detail relationships to other data blocks in your form. Click on the ‘Create Relationship…’ button to select a master data block. In this Hands-on exercise is the ‘Customer’ table. Notice that your master and detail items could represent your primary and foreign keys. Clicking on the ‘Delete Relationship’ button, will remove the relationship. Make sure that if you didn’t establish the primary and foreign keys, you should unmark the ‘Auto-join data blocks’ box. Then click "Create relationship" and “OK.”

End of the Data Block Wizard and beginning of the Layout Wizard

In the ‘Congratulations’ screen, use the default checkmark radio button (Create the data block, then call the Layout Wizard), and click "Finish."

Welcome screen

In the ‘Welcome to the Layout Wizard’ window, click ”Next.”

Selecting canvas

In the ‘Layout Wizard’ window, select the "new canvas" option. Change "tab page" to "new tab page” and click "next."

Selecting Columns for the Layout Wizard

In the ‘Layout Wizard’ window, select all the columns and deselect “CUSTID” since the customer is known and then click “Next.”

Change your objects appearances

Change size or prompt if needed. In this window, you can enter a prompt, width, and height for each item on the canvas. You can change the measurement units. As a default the default units for item width and height are points. You can change it to inch or centimeter. When you change size, click “Next.”

Selecting a layout style

Select “Tabular” since there are more than one order for each customer and then click “next.”

Record layout

Type the Frame Title, Records Displayed, Distance between Records values and checkmark the ‘Display Scrollbar’ box, when you use multiple records or the ‘Tabular’ option. Then click “Next.” Make sure to change the ‘Records Displayed’ item to be more than 1 and checkmark “Display Scrollbar.”

Congratulation Screen

In the ‘Congratulations’ window, click "Finish."

Now, there are two canvases: One for customer information and the other for their orders.

Data Block

In the ‘Object Navigator’ window, highlight "Data Blocks,” and click on the "create” icon.

New Data Block

In the ‘New Data Block’ window, choose the default option “Data Block Wizard” and click "OK."

Welcome Data Block

In the ‘Welcome Data Block Wizard’ window click “NEXT.”

Type of Data Block

Select the type of data block you would like to create by clicking on a radio button. Select the default option ‘Table or View’ and then click “NEXT” again.

Selecting Tables

Click on “browse.” In the ‘Tables’ window, highlight the "items” table; then click "OK." You can also type ‘items’ and then click on the "refresh” button.

Selecting columns for the Data Block Wizard

To choose all columns, click on the two arrow signs in the ‘Data Block Wizard’ window and then click “next.”

Relationships

You may optionally create and delete master-detail relationships to other data blocks in your form. In this Hands-on exercise the master table is the ‘ORD’ table. Then click "Create relationship" and “OK.”

End of the Data Block Wizard and beginning of the Layout Wizard

In the ‘Congratulations’ screen, use the default checkmark radio button (Create the data block, then call the Layout Wizard), and click "Finish."

Welcome screen

In the ‘Welcome to the Layout Wizard’ window, click ”Next.”

Selecting canvas

In the ‘Layout Wizard’ window, select the "new canvas" option. Change "tab page" to "new tab page” and click "next."

Selecting Columns for the Layout Wizard

In the ‘Layout Wizard’ window, select all the columns and deselect “ORDID” since the order is known and then click “Next.”

Change your objects appearances

Change size or prompt if needed. In this window, you can enter a prompt, width, and height for each item on the canvas. You can change the measurement units. As a default the default units for item width and height are points. You can change it to inch or centimeter. When you change size, click “Next.”

Selecting a layout style

Select “Tabular” since there is more than one item for each order and then click “next.”

Record layout

Type the Frame Title, Records Displayed, Distance between Records values and checkmark the ‘Display Scrollbar’ box, when you use multiple records or the ‘Tabular’ option. Then click “Next.” Make sure to change the ‘Records Displayed’ item to be more than 1 and checkmark “Display Scrollbar.”

Congratulation Screen

In the ‘Congratulations’ window, click "Finish."

Now, there are three canvases:

Navigate the Tab pages

Navigate through the tab pages. Notice that page3, 5, and 7 don't mean anything. Your page number could be different.

Change the Tab labels

Select page3, right click and open its "property palette." Change its “name” and “label” to customer. Press the enter key. Notice! The name always changes to upper case.

Click on page5 to replace its properties and do the same for “Orders.” Be sure to press the enter key to confirm the changes.

Click on page7 to replace its properties and do the same for “Items.” Then close the window.

Now, the tabs are more meaningful. Navigate through the tab pages.

Collapse the objects to view items easier in the ”object navigator” window.

Select “customer_orders,” and right click to open its "property palette." Change "First Navigation Data Block" to “Customer.” Then close the window.

Run Form

Click on the "Run" icon to compile and execute the module.

Execute Query

Click "execute query" to query the customer information. Navigate through the “tab” options.

On the "orders" tab, select an item by highlighting the item. Then click on the ‘items’ tab. Notice on the “items” tab, all its items correspond to the selected order.

Repeat this.

On the customer tab, position the cursor on “Customer ID” and navigate through the table by clicking on “NEXT Record.”

Do the same for the previous record.

Enter Query

Click on "Enter Query.” The items in the form become blank. Type 106, and click "Execute Query." Navigate through the customer’s orders and its items.

Save the transaction

Close the window. Save the changes.

Questions:

Q: How can you create a "Datablock" for a form application using the "Form Builder" tool?

Q: How can you create a "layout" for a form application using the "Form Builder" tool?

Q: How can you use a "Content canvas" for a form application using the "Form Builder" tool?

Q: Use the "Execute Query" option to query customer's information.

Q: How can you navigate a table using the "Form Builder" tool?

Q: What does the "NEXT RECORD" option do?

Q: What does the "PREVIOUS RECORD" option do?

Q: What does the "Enter Query" option do?

Q: How can you manipulate table's record?

Q: Insert, delete and update a record.

Q: How can you save a transaction using the "Form Builder" tool?

Q: What is a Master/Detail relationship between two data blocks in the Form Module?

Q: Can a Form Module contain more than one canvas?

Q: Having performed Fact Finding, and determine the user's initial requirements, you are ready to create a data entry form based on those requirements. Keeping in mind that this is an iterative process, and the User is closely involved in the design. During your interviews, you have identified that tables Customer, Ord, and Items will be required, and you have an idea of the "Look-and-Feel" of the Form. Your client asks you to create an easy to use data entry Form for their “Customer Order” data entry application.

See Figure 1, 2, and 3 for screen layout requirements.

1 comment:

Anonymous said...

How can insert and commit data in two table through master-detail form in oracle 6i and 10g.
I cannot get my answer. Can you pls. describe me how can I done my work? email ID lovelyislam1980@yahoo.com

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect