oracle apps - forms
TRANSCRIPT
ORACLE APPS - FORMS
Agenda
Introduction to Forms Developer - Basics Creating Simple Form ModuleCreating and Managing Menu Modules Forms Builder Messages & Alerts Triggers & Objects & Item Interaction
Triggers
Introduction to Oracle Forms Developer - Basics
Objectives
• What is Oracle Forms Builder?• Forms in Client/Server Mode• Forms in Web Mode• Form Builder Key Features• Saving User Preferences• How to Start?
What is Oracle Forms Builder?
Used to develop - Data Entry Screens - Query Screens
Forms in Client/Server Mode
Client Database Server
Net8
2-Tier Architecture
Client Database Server
Net8HTTP
Oracle 9i Application Server
Forms in Web Mode
3-Tier Architecture
Form Builder Key Features
• Supports GUI Applications• Rapid Application Development Tool• Flexible source control• Extended scalability• Application partitioning• Object reuse• Oracle 10g/11g datatypes support• Improved Developer Productivity
User Preferences
• Click on Tools menu- Select Preferences
User Preferences cont..
• Click on Tools menu- Select Preferences
User Preferences fileWindows – Cauprefs.oraUnix – Prefs.ora
How to Start?
• Start Form Builder• Welcome Page Displays• Connect to the Database by selecting
- File -> connect or use Ctrl+J• Provide Username, Password and Connect String
and click connect.
Summary
• Introduction to Form Builder• Forms in Client/Server and Web• Key Features• User Preferences• Starting Form Builder
Creating Simple Form Module
Objectives
• Creating a Simple Form Module Using wizards• Types of Blocks• Control Block • Discuss properties of Data Block• Creating a Master-Detail Form• Relationship between data blocks
Crating a New Form Module
• Use one of the options to create a new form module 1. Use The Data Block Wizard
2. Build a new form manually3. Build a form Based on a Template.
Using Wizards..
Use Form Builder Wizards:
• Data Block Wizard: Used to create a data block with a data source.
• Layout Wizard: Used to display the data block items for Presentation.
Data Block wizard- Type of data block
Data Block wizard- Table or view name
Layout wizard- Changing Prompts
Layout wizard- Layout Style
Layout wizard-Rows Page
Data Block Functionality
Once you create a data block with the wizards, Form Builder automatically creates:
• A form module with database functionality including query, insert, update, delete
• A frame object• Items in the data block• A prompt for each item
Modifying the Layout
• You can modify the layout after creating the data block by using re-entrant wizards
– Select Tools -> Data block wizard to modify the data block– Select Tools -> Layout wizard to modify the Layout of the form.– Changes can be made manually also.
Types of Blocks
Data Block Control Block
- Base Table Block - Non Base Table Block
Creating a Control Block
• Select the Data Blocks node and click the Create icon. or Select Navigator—>Create.• Select the “Build a new data block manually” option in the New Data
Block dialog box.
– Invoke the property pallet for the block– Change the name of the block to CONTROL– Set the Database Data block property to No.
Data Block Properties
• General• Navigation• Records• Database• Advanced Database• Scrollbar• Font and Color• Character Mode• International
Database Properties for Data block
Use properties in the Database group to control:
• Type of block—data block or control block• Query, insert, update, and delete operations on the data block• Data block’s data source type and Name• Query search criteria and sort order• Maximum query time• Maximum number of records fetched
Navigation PropertiesORDERS
Order
Next NavigationData Block
SameRecordChangeRecord
PreviousNavigation Block
Visual Attributes
• Visual attributes are font, color and pattern properties applied for form and menu objects.
Data blocks and Relationships
Master
Detail
Master
Detail
Detail
Detail Detail
Master
Data block wizard – Master-Detail Relationship
Relation Object
• Relationship object is always created on the master block• Triggers and program units are generated automatically by form
builder• The default name of the relation object is Masterblock_DetailBlock
Creating Relation Manually
Deletion Behavior
• Non-Isolated (default setting) Prevents the deletion of a master record when associated
detail records exist in the database. • Isolated Allows the master record to be deleted
and does not affect associated detail records in the database.
• Cascading Allows the master record to be deleted and automatically deletes any
associated detail records in the detail block's base table at commit time.
Block Coordination
Immediate Deferred
WithAutoQuery
WithoutAutoQuery
Text files and Documentation
• Convert a binary file to a text file OR text file to birnary.By Selecting File-> Administration -> Convert.
• Create a text file for a form module for Documentation.By Selecting File->Administration -> Object List Report
.fmb .fmt
Summary
• Creating a simple Form module• Types of blocks• Master-Detail Form• Data block relationships • Data block Properties
Creating and Managing Menu Modules
Objectives
• Identify Menu components• Create, save and attach Menu modules• Modifying properties of Menu items• Creating Popup menu and attaching• Controlling Menus Programmatically• Manage Menu Security
Menu Module Components
MainMenu
IndividualMenu
MenuToolbar
MenuItem
Default Menu
Action
SaveClear AllPrintPrint SetupExit
Edit
CutCopyPasteEditDisplay List
Block
PreviousNextClear
Field
PreviousNextClearDuplicate
Record
PreviousNextScroll UpScroll DownInsertRemoveLockDuplicate Clear
Query
EnterExecuteCancelLast CriteriaCount HitsFetch Next Set
Help
HelpKeysListDisplay ErrorDebug
Menu EditorDisplaymenu
Create Down Create
Right
SwitchOrientation
Menu tabExpanded
menu Collapsedmenu
Creating a Menu Module
ObjectNavigator
Menu Editor
Select Tools Menu Editor
Menu Module Properties
Main MenuMenu DirectoryMenu FilenameStartup CodeShare Library with Form
Use SecurityModule Roles
Menu Item Properties
• Enabled• Label• Menu Item Type• Magic Item• Menu Item Radio
Group • Command Type
• Menu Item Code• Submenu Name• Icon in Menu• Icon Filename
Menu Item Types
Separator
Plain
CheckMagic
Radio
Magic Items
• About• Undo• Clear• Copy• Cut
• Paste• Help• Quit• Window
Menu Item Command Types
Null
Menu
PL/SQL
Plus
Form
Macro
Does not issue a command
Invokes a submenu
Executes a PL/SQL command
Spawns a process to SQL*Plus
Backward compatibility
Backward compatibility
Menu Module File Types
Menu module definition: • .mmb (Menu Module Binary)• Stored in files or database tablesMenu module executable:• .mmx (Menu Module Executable) • Stored in filesMenu module text:• .mmt (Menu Module Text)• Stored in files
Attaching Menu Module
• Open Form Module PropertyPallet.
• Modify Menu Module Property.• Specify the name of your menu
module.
Pop-up Menus
Menu that appears on the screen at thelocation it was invoked• Enables users to
access commonly used functions easily
• Is accessed by pressing the right mouse button (Microsoft Windows and Motif)
Using Pop-up Menus
• Pop-up menus are:– Form module objects in the object navigator,
such as alerts or Data Blocks – Built through the Property Palette or
Menu Editor– Associated with items and canvases
with a pop-up menu property
• Pre-Popup-Menu trigger providesdynamic control
Writing PL/SQL Code in Menu Triggers
• Similar to PL/SQL code in form triggers• You can share code between a form
module and a menu module by using the following:– Libraries– User-defined triggers– DO_KEY built-in
Writing PL/SQL Code in Menu Triggers
• Menu modules generated independently• Restrictions:
– Cannot directly reference values of form objects
– Must use NAME_IN built-in function– Cannot use direct assignment for
form objects– Must use COPY built-in procedure
Built-ins Used
• FIND_MENU_ITEM• GET_MENU_ITEM_PROPERTY• SET_MENU_ITEM_PROPERTY• ITEM_ENABLED• MENU_SHOW_KEYS
Built-ins Showing and Hiding
REPLACE_MENUCharacter mode built-ins:• HIDE_MENU• SHOW_MENU• MENU_REDISPLAY
Managing Menu Security
• Defining security roles• Choosing roles for the menu module• Assigning access to menu items• Setting the Use Security property
Roles
Role
Privileges
Users
Menu Module Roles
• Select Menu Module Roles Property from the menu Module properties.• Enter Database Role Names.• Use Security Property Set to Yes.
Menu Item Roles
• Invoke Property pallet of a Menu item.• Choose Menu Item roles property.• Select Menu Item roles.• Press OK button.
Summary
• Creating Menu Modules• Attaching Menu Modules to Forms• Popup menu items• Pre-Popup menu trigger.• Built-ins for Managing Menu items programmatically.• Menu Module security with Roles.
Form Builder Messages & Alerts
Displaying Messages to Operators
MessagesApplicationWorkingInformativeError
AlertsSystemApplication
Errors and Built-ins
• Built-in failure does not cause an exception.• Test built-in success with FORM_SUCCESS function.• What went wrong?
– ERROR_CODE, ERROR_TEXT, ERROR_TYPE– MESSAGE _CODE, MESSAGE _TEXT, MESSAGE _TYPE
Example: GO_BLOCK(‘EMP’);IF FORM_SUCCESS THEN
EXECUTE_QUERY; END IF;
FORM_FAILURE
FORM_FATAL
Returns TRUE OR
FALSE.
Message Severity Level
>25
20
15
10
5
0
25
All (default)
More critical
Define by:
:SYSTEM.MESSAGE_LEVEL
Fatal Error
Error Triggers
• On-Error Trigger– Fires when system error message is issued. Built-ins to Use:– Use ERROR_TYPE– ERROR_CODE– ERROR_TEXT
• On-Message Trigger– Fires when informative message is issued. Built-ins to Use:– MESSAGE_TYPE– MESSAGE_CODE– MESSAGE_TEXT
Alerts Properties
Title
ALLERT_BUTTON1 ALLERT_BUTTON3ALLERT_BUTTON2
Alert Message Text (Max 200 Characters)
Alert Style
Showing Alerts
DECLARE n Number;BEGIN n:=SHOW_ALERT(‘Del_Alert’);
IF n = ALERT_BUTTON1 THENDELETE_RECORD;
END IF;END;ORIF SHOW_ALERT('DEL_ALERT‘) = ALERT_BUTTON1 THEN
DELETE_RECORD;END IF;
NOTE: SHOW_ALERT Function Returns Number.
Displaying Errors to an Alert
Changing the Alert Button Label:SET_ALERT_BUTTON_PROPERTY(‘Err_Alert’,
ALERT_BUTTON1,LABEL,’O.K’);
SET_ALERT_PROPERTY(’Err_Alert’,ALERT_MESSAGE_TEXT, ERROR_TYPE||’-’||TO_CHAR(ERROR_CODE)|| ’: ’||ERROR_TEXT); n := SHOW_ALERT(’Err_Alert’);
Summary
• Displaying Messages in Form Builder• Built-ins
– ERROR_CODE, ERROR_TYPE, ERROR_TEXT– MESSAGE_CODE, MESSAGE_TYPE, MESSAGE_TEXT.
• Testing Built-ins with FORM_SUCCESS• Alerts• Setting alert Properties at runtime.
Form Builder - Triggers
Form Builder Triggers
• A trigger is a PL/SQL Block which fires implicitly whenever an Event occurs.
Events:Item Interaction
Errors/MessagesQueries
Validation
Navigation
Mouse Events
others
PL/SQLBLOCK
Trigger Components- Scope
Scope
Type
CodeForm LevelBlock LevelItem Level
Trigger Components - Type
Scope
Type
Code
Pre-Post-WhenOn-Key-
Trigger Components - Code
SQL StatementsPL/SQL StatementsBuilt-in Sub-programsUser Defined Sub-programs
Scope
Type
Code
Execution Hierarchy
Item Level
Block Level
Form Level
On-Error
EH=After
On-Error
EH=After
On-Error
EH=Override
Event
Summary
• Trigger: Implicitly Fires• Type: Defines the event that fires it• Prefixes:
– Pre- – Post– When– On– Key
• Code: Anonymous PL/SQL Block• Scope: Form, block, or item level
Oracle Objects Features In Forms
Objectives
• Identify which object types are supported• Describe how object types are
represented within Form Builder• Create a block based on an object table• Create a block based on a relation table
with an object or an REF column• Populate a REF column with an LOV
What is an Object Type?
interest
Close
withdraw
Check status
Attributes
Methods
ACCOUNTAc_no
Ac_typeDeposit
Open_date
Overview on Objects
• Object tables• Object columns• Object views• REF Columns
REF
Oracle 8i Features in Forms
Supported Oracle8i features• Large Objects: BLOB, CLOB, NCLOB, BFILE• User-defined objects
– Object table– Column object– REF column
Unsupported Oracle8i features• Collection types• Stored procedures that return object values
How Form Treats Objects
Object_B
Attr_B1
Attr_B2
Attr_A3
Nested Objects
Attr_A1
Attr_A2
Object_A
Attr_A1
Object_A
Attr_A3
Attr_A2_B1
Attr_A2_B2
Items in a Form
Creating Data Blocks
• Blocks based on object tables• Blocks based on object columns• Blocks with REF Lookups
Block Based on Object Tables
Block Based on Object Columns
Blocks with REF Lookups
Using REF Lookup Value
Select REF attribute columns: This causes the columns to appear as data items at run-time.Select the REF item itself: • This causes the item to be placed on a Null canvas.• The item does not appear at run time.• The item is available for coding purposes.
LOVs for REFs
Summary
• Identify supported Oracle8i object types• Define data blocks based on object tables• Define data blocks based on tables
with object columns or REF columns• Populate REF columns with an LOV
Item Interaction Triggers
Objectives
• Adding Functionality to Input and Non-input Items by adding triggers and Built-ins.
• Displaying LOVs from Buttons• Interacting with Checkboxes• Interacting with Radio Buttons• Interacting with List items• Loading Image into Image item• Populating Hierarchical Tree Item
Item interaction Trigger
• Valid commands:– SELECT statements– PL/SQL expressions– All built-in subprograms– User Defined Subprograms
Examples
When-Button-Pressed
When-Checkbox-Changed
When-Radio-Changed
When-List-Changed
When-List-Activated
When-Image-Pressed
When-Image-Activated
When-Tree-Node-Selected
Displaying LOVs from Buttons
• Uses:– Convenient alternative for accessing LOVs– Can display independently of text items
• Needs:– When-Button-Pressed trigger– LIST_VALUES or SHOW_LOV built-inDECLARE x Boolean;BEGIN x:=SHOW_LOV(‘Dept_Lov’);END;
Interacting With Checkboxes
• WHEN-CHECKBOX-CHANGED trigger
IF CHECKBOX_CHECKED(‘Control.case’) THEN SET_ITEM_PROPERTY(‘Emp.ename’,CASE_INSENSITIVE_QUERY, PROPERTY_FALSE);
ELSE SET_ITEM_PROPERTY('Emp.ename',CASE_INSENSITIVE_QUERY, PROPERTY_TRUE);
END IF;
Interacting With Radio Button
• WHEN-RADIO-CHANGED trigger
IF :emp.job=‘MANAGER’ THEN :emp.sal:=7000;ELSIF :emp.job=‘CLERK’ THEN :emp.sal:=5000;ELSE :emp.sal:=2000;END IF;
Interacting with List Items
ADD_LIST_ELEMENT(‘emp.job’, 4, ‘Analyst’, ‘ANALYST’);
DELETE_LIST_ELEMENT(‘emp.job’ 2);
President
Manager
Clerk
Salesman
Index
1
2
3
Loading Images
Fetch onQuery
READ_IMAGE_FILE
WRITE_IMAGE_FILE
Read_Image_File Built-in
• Used to Load Images into Image Items.
READ_IMAGE_FILE(‘E_’||:emp.empno||’.bmp’,’BMP’,’emp.emp_image’);
Populating Hierarchical Tree Item
• Set the Data Query Property – SELECT 1,level,ename,null,empno FROM emp
CONNECT BY PRIOR empno=mgrSTART WITH JOB=‘PRESIDENT’
Note: Do not terminate query with semicolon.
• Write When-New Form Instance Trigger at Form LevelFtree.Populate_Tree(‘tree_block.emp_tree’);
Summary
• Item Interaction Triggers and Built-ins• Interacting with Various Items
– Push Button– Checkbox– Radio Button– List Item– Image Item– Hierarchical Tree Item
Thank You
Bhaskara Reddy Sannapureddyhttps://www.linkedin.com/in/bhaskara-reddy-sannapureddy-57052b11/