, , , ,

Oracle Forms Basic Concepts

The focus of the document is for consultants who are new to Oracle Forms and needs a kick-start on the concepts for better understanding of the subject.
Let’s start understanding the basic but important concepts in Forms.
What is Form :It is a developmental tool that is used for designing data entry and query screens. It is a front-end tool that runs in a Graphical User Interface (GUI).
GUI Concepts:
These concepts holds good for any user-interface.
To develop an effective GUI there are 4 basic stages:
  1. Define User Requirements
  2. Plan the User Interface
  3. Build the User Interface Elements (Create/Modify elements/functionality)
  4. User Feedback (Holds Key on the functionality and basis of the requirement)
Let’s move on to Forms Developer
How many components in Forms?
There are 3 components involved in the application development
  1. Form Builder
  2. Form Compiler
  3. Form Runtime
Form builder consists of following tools to perform a specific task
  1. Object Navigator
  2. Layout Editor
  3. Property Palette
  4. PL/SQL Editor
  5. Menu Editor
  6. Object Library

Object Navigator: It’s a hierarchal representation of all objects.
Layout Editor: It provides a virtual representation of the application user interface.
Property Palette: Each object in the form module has a property associated to it. Developer can view/set properties for one/multiple object.
PL/SQL Editor: Programmatically to enhance the functionality and appearance of an application.
Menu Editor: Create menu as per applications requirement and can add various functionality to various menu options.
Object Library: Creation of objects on some default specification. Storing some standard objects that can be re-used in other forms/menu.
Blocks: Logically related interface items are grouped into functional units called Blocks.
Types of Block:
Data Block: It is associated with or bound, to a database table or view or a set of stored procedures.
Control Block: It is not associated with any database table but items that will control the behavior of the application.
Let’s move on to the next scheme of things…
Canvas: It is a surface inside a window on which we place the interface that end user interacts.
Types of Canvas:
  1. Stacked Canvas
  2. Content Canvas
  3. Horizontal Toolbar
  4. Vertical Toolbar
  5. Tab Canvas

Let’s discuss briefly about the triggers in this section, for more information you can look through the Forms Builder Help Topics.
Note: The hierarchy of Objects in a form is
Form
Block
Record
Item
What is Triggers: These are program units which enhance the functionality of a form/application.
The following triggers can be used to enhance the functionality of the form:
What are Types of triggers in Oracle Form?

Block Processing Triggers: It fires in response to events related to record management in block.
e.g., When_Create_Record,When_Clear_Block,…
Interface Event Triggers: It fires in response to events that occur in form interface.
e.g., When_Button_Pressed,When_Checkbox_Changed,…
Master-Detail Triggers: It fires automatically when defined master-detail relationship between blocks. (Master-Detail relationship discussed further in the document)
e.g.,On_Checkdelete_Master,On_Clear_Details,…
Message Handling Triggers: It fires to issue appropriate error and information messages in response to runtime events.
e.g.,On_Error,On_Message,..
Navigational Triggers: It fires in response to Navigational Items.
e.g., Pre_Form, Post_Form, When_New_Form_Instance, When_New_Block_Instance,..
Query Time Triggers: It fires before/after the operator/application executes a query.
e.g.,Pre_Query,Post_Query,…
Transactional Triggers: It fires in response to wide variety of events that occur as a form interacts with data source.
e.g.,On_Delete,On_Update,..
Validation Triggers: It fires when it validates data in an item/record.
e.g.,When_Validate_Item,When_Validate_Record,..
Mouse Event Triggers: It fires for a mouse event.
e.g.,When_Mouse_Enter,When_Mouse_Click,..
Key Triggers: It has one to one relationship with specific Keys.
e.g.,Key F1,Key Enter,..
There are lot number triggers that can be used, please use as per the requirement with reference to Form Builder Help Topics.
What is Master- Detail Relationship? or What is Parent -Child Relationship?
Master- Detail Relationship : It is an association between two datablocks.One block is called Master Block and other Detail block. The relationship signifies that there is a primary key to foreign key relationship between the tables on the blocks associated.
What are Properties associated with blocks in a master-detail relationship?
Isolated : If you delete master records, associated detail records are not deleted from the database.
Non-Isolated: You cannot delete master records if the associated detail records exist in database.
Cascading: If you delete master records then automatically detail records will be automatically deleted from the database.
Windows : It is a container for all visual objects that make up a form, including canvases.
There are 2 types of Windows:
Document Window : It typically display the main canvases and work areas of the application where most data entry, and data retrieval is performed. It always remains within the application window frame.
Dialog Window: are free-floating, windows typically used for modal dialogs that require immediate user interaction.
Modality of the window depends on the functionality required i.e., Modal or Modeless.
Alert : It is a modal window that displays message to inform user about some application condition. E.g., STOP,CAUTION,NOTE,…
Invoking an alert : show_alert(alert_name)
Return number;
What is Record Group: It is an internal form builder structure that has column/row structure similar to database table. Static and Query based record groups can be used on the functionality of the form.
What is List of Values (LOV) : It is a pop-up window that provides end user selection list. LOV’s can be invoked programmatically or statically based on the record group. It can be positional based or automatic display.
The most important features of LOV are it provides auto-reduction and search features due to which user can locate specific values easily.
Let’s get to items on canvas which holds the key points.
Boilerplate Text Tool is used to create or edit a graphics text in the form. Graphics text is a static text in the form. E.g. Labels for items
Text Item Tool is used to create text item. It is an interface control that displays and allows editing of a text. It can be single or multi-line format.
Display Item tool are similar to text items but display items only store and displayed fetched or assigned values.
Buttons is a tool to execute commands or initiate buttons. E.g., OK ,CANCEL,..
Types : Text and Iconic Buttons
List Item is a list of text elements. A list item displays a fixed number of elements.
Types: Tlist,Pop List, Combo Box
Checkbox: It is a control that has 2 states i.e., checked or unchecked. It is used to indicate whether a certain condition is true or false.
Radio Button/Box : It is a logical set of options.
Editors: are used to edit item values in form. There are three editors that can be used at run time: Default editor, System Editor, User Named Editor
Property Class: Form builder provides a facility to create a named list of common properties and their values. This object is known as property class. Once you create a property class, you can base other objects on it. It is similar to the OOPS concept in programming languages.
Visual attribute : is a list of font, color and pattern properties and their values. This visual attribute can be attached to various objects to define object’s visual attributes.
/by
, , , , , ,

List of Tables for Forms Personalization

Below is the list of tables that are populated when any Forms Personalization is done

FND_FORM_CUSTOM_RULES
FND_FORM_CUSTOM_SCOPES
FND_FORM_CUSTOM_ACTIONS
FND_FORM_CUSTOM_PARAMS
FND_FORM_CUSTOM_PROP_VALUES
FND_FORM_CUSTOM_PROP_LIST

You can FNDLOAD utility to move one instance to another.

Or export and import this records from one instance to other. But make sure the sequences are altered 🙂

The below script list all the forms in Oracle Applications that have been customized using Forms Personlization:

select distinct a.form_name , a.enabled, c.USER_FORM_NAME, d.APPLICATION_NAME 
from FND_FORM_CUSTOM_RULES a,
     FND_FORM b,
     FND_FORM_TL c,
     fnd_application_tl d
where enabled = ‘Y’
and a.form_name = b.form_name
and b.form_id = c.form_id
and b.application_id = d.application_id

order by application_name;

/by
, , , , , ,

What is Subinventory Quantities report?

Form: INVIRSIQ
Use the Subinventory Quantities Report to show inventory item quantities by subinventory.
Report Submission
Use the On-hand Quantity Reports or Submit Requests window and enter Subinventory quantities report in the Name field to submit the report.
Report Parameters
Item Range
Choose one of the following options:
Full listing   :    Report all subinventories.
Partial list by :  Report only those subinventories for a inventory item  partialrange of items.
Specific          :  Report only the subinventory you subinventory  specify.
Items From/To
Enter a beginning and ending item to restrict the report to a range of items. You can enter an item here only if you enter Partial list by inventory item in the Item Range field.
Subinventory
Enter a subinventory for which to report on-hand quantity. You can enter a value here only if you enter Specific subinventory in the Item
Range field.
The original report has the following columns:
1.Item
2.Description
3.Rev
4.Locator
5.UOM
6.Quantity
Customization:
–Add the price of every item.
–Calculate the price*qty=total
–Add the subinventory total
This would give us the value of the sub inventory.
Tables Used:
1.MTL_SYSTEM_ITEMS (msi)
2.MTL_ONHAND_QUANTITIES_DETAIL (moqd)
3.MTL_SECONDARY_INVENTORIES (si)
4.MTL_ITEM_LOCATIONS (mil)
The tables added:
1.CST_ITEMS_COST (cs)
MTL_SYSTEM_ITEMS:
MTL_SYSTEM_ITEMS_B is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. You can specify item-related information in fields such as: Bill of Material, Costing, Purchasing, Receiving, Inventory,
Physical attributes, General Planning, MPS/MRP Planning, Lead times, Work in Process, Order Management, and Invoicing. You can set up the item with multiple segments, since it is implemented as a flexfield.
Use the standard ’System Items’ flexfield that is shipped with the product to configure your item flexfield. The flexfield code is MSTK. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Each item is initially defined in an item master organization. The user then assigns the item to other organizations that need to recognize this item; a row is inserted for each new organization the item is assigned to.
Many columns such as MTL_TRANSACTIONS_ENABLED_ FLAG and BOM_ENABLED_FLAG correspond to item attributes defined in the MTL_ITEM_ATTRIBUTES table. The attributes that are available to the user depend on which Oracle applications are installed. The table MTL_ATTR_APPL_DEPENDENCIES maintains the relationships between item attributes and Oracle applications. Two unit of measure columns are stored in MTL_SYSTEM_ITEMS table.
PRIMARY_UOM_CODE is the 3-character unit that is used throughout Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE is the 25-character unit that is used throughout Oracle Purchasing. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL.
Translations table (MTL_SYSTEM_ITEMS_TL) holds item descriptions in multiple languages. DESCRIPTION column in the base table (MTL_SYSTEM_ITEMS_B) is for backward compatibility and is maintained in the installation base language only.
MTL_ONHAND_QUANTITIES_DETAIL
MTL_ONHAND_QUANTITIES stores quantity on hand information
by control level and location.
MTL_ONHAND_QUANTITIES is maintained as a stack of receipt
records, which are consumed by issue transactions in FIFO order. The quantity on hand of an item at any particular control level and location can be found by summing TRANSACTION_QUANTITY for all records that match the criteria. Note that any transactions which are
committed to the table MTL_MATERIAL_TRANSACTIONS_TEMP are
considered to be played out as far as quantity on hand is concerned in Inventory transaction forms. All our Inquiry forms and ABC compile are only based on MTL_ONHAND_QUANTITIES.MTL_ONHAND_QUANTITIES has two columns, CREATE_TRANSACTION_ID and UPDATE_TRANSACTION_IDs to join to MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID the
transactions that created the row and the transaction that last updated a row.
MTL_SECONDARY_INVENTORIES
MTL_SECONDARY_INVENTORIES is the definition table for the
subinventory. A subinventory is a section of inventory, i.e., raw material, finished goods, etc. Subinventories are assigned to items (in a many to one relationship), indicating a list of valid places where this
item will physically exist in inventory.
Other attributes include general ledger accounts, demand picking order, locator type, availability type, and reservable type. You can also specify whether this subinventory is an expense or asset subinventory (ASSET_INVENTORY), or whether quantities are tracked
(QUANTITY_TRACKED).
MTL_ITEM_LOCATIONS
MTL_ITEM_LOCATIONS is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.
The locator is a key flexfield. The Flexfield Code is MTLL.
CST_ITEMS_COST
CST_ITEM_COSTS stores item cost control information by cost type.
For standard costing organizations, the item cost control information for the Frozen cost type is created when you enter a new item. For average cost organizations, item cost control information is created when you transact the item for the first time.
You can use the Item Costs window to enter cost control information.
Where clause of the original report:
1.si.secondary_inventory_name = moqd.subinventory_code(+)
2.si.organization_id = moqd.organization_id (+)
3.moqd.organization_id = msi.organization_id(+)
4.moqd.inventory_item_id = msi.inventory_item_id (+)
5.moqd.locator_id = mil.inventory_location_id(+)
6.moqd.organization_id = mil.organization_id(+)
Additional where clause added for the customized report:
1.cs.inventory_item_id = msi.inventory_item_id
2.cs.organization_id = msi.organization_id
/by
, , , ,

Hierarchical Trees Developement Using Oracle Forms Builder

About Hierarchical Trees
The hierarchical tree displays data in the form of a standard navigator.
We can populate a hierarchical tree with values contained in a Record Group or Query Text.
At runtime, you can programmatically add, remove, modify, or evaluate elements in a hierarchical tree.
The amount of data displayed at any one time depends upon the expansion of individual data nodes.
Triggers
Following Triggers are Provided by Form Builder exclusively for hierarchical tree Items.
1.       When-Tree-Node-Activated
2.       When-Tree-Node-Expanded
3.       When-Tree-Node-Selected
Built-ins
Following built-in subprograms are used to add, remove, modify, or evaluate elements in a hierarchical tree Items.
All built-ins are located in the FTREE built-in package.
1.       Add_Tree_Data
2.       Add_Tree_Node
3.       Delete_Tree_Node
4.       Find_Tree_Node
5.       Get_Tree_Node_Parent
6.       Get_Tree_Selection
7.       Populate_Group_From_Tree
8.       Populate_Tree
9.       Set_Tree_Selection
Now, we will see, how to Develop a form Using Hierarchical Tree Item for Oracle Application.
Objectives :
1.       Create the Hierarchical Tree Item in a Non-Database Block.
2.       Populate the Data to the Hierarchical Tree Item Using Record Group at Runtime.
3.       Create a Database Block to Display the Data(s) based on the Element Navigating on the Hierarchical Tree Item.
Database Objects Required to Develop this Form :
 
Find the Attachment contains the Script to Create Database Objects & Sample Data’s to be Inserted.
In the Canvas Tool Palette we can I find the Hierarchical Tree.
Note : Hierarchical Tree Item Should be in a separate Block as a Individual Item.
There are 2 ways to populate a hierarchical tree :
1.       Record Group
2.       Query Text
The record Group query should be in the specified structure., which requires 5 Columns.
SELECT STATUS, LEVEL, LABEL, ICON, VALUE FROM TABLE;
STATUS à Indicates the initiate status of the Node (Normally Value is 1).
LEVEL  à This is a specific pseudo-column Derived from “CONNECT BY”.
LABEL  à This is the visible label of the Node.
ICON   à That contains the icon name of the Node (can be NULL).
VALUE  à That contains the value of the Node.
Below is the Query used in the Record Group to Populate the Data in the Hierarchical Tree Item.
SELECT 1, LEVEL, ENAME, NULL, TO_CHAR(EMPNO) APPS.EMP CONNECT BY PRIOR EMP.EMPNO = EMP.MGR;
Object Navigator :
 
 
Canvas Design :
 
 
At Run Time :

Kindly have a look on the Below Triggers :

1. WHEN-NEW-FORM-INSTANCE (Form Level)
2. WHEN-TREE-NODE-SELECTED (Item Level)

I hope this will be Useful for custom Developments.

/by
, , , , , , ,

Oracle Database Links (DB Link)

A database link is a path through which a remote user in another database can connect to any other database. Once created the database link exists as an object in the user schema.

 Type of DB Links
There are 3 types of DB links. They are as follows:

1. PRIVATE: When the DB links is created, it is created under Private mode as default. The Private DBLINK is only available to the user who has created it. It is not possible for a user to grant access on a private DBLINK to other users.

2. PUBLIC: The Public DBLINK is available to all the users and all users can have the access without any restrictions.

 3. SHARED: Shared database link uses share the server connection to support database link connection. If there are multiple concurrent database link access into a remote database, shared database link can be used to reduce the number of server connections required. Without the shared clause each database link connection requires a separate connection to the remote database.

 Types of Logins:
In dblink we can use 2 types of login. They are as follows:

1. DEFAULT LOGIN: The User name and Password is same in both the databases.
Syntax
======
CREATE [PUBLIC] DATABASE LINK CONNECT TO CURRENT_USER USING
Code: (Text)
Create public database link daslink connect to current_user using ‘ORCL’


2. EXPLICIT LOGIN: The User Name and Password is different in both the databases.
Syntax
======
CREATE [PUBLIC|SHARED] DATABASE LINK CONNECT TOIDENTIFIED BY USING
Code: (text)
CREATE PUBLIC DATABASE LINK DDLNK CONNECT TO SCOTT IDENTIFIED BY TIGER USING ‘ORCL’

 Note: To create the public DBLINK the user must have create public database link system privileges.

/by