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:
- Define User Requirements
- Plan the User Interface
- Build the User Interface Elements (Create/Modify elements/functionality)
- 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
- Form Builder
- Form Compiler
- Form Runtime
Form builder consists of following tools to perform a specific task
- Object Navigator
- Layout Editor
- Property Palette
- PL/SQL Editor
- Menu Editor
- 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:
- Stacked Canvas
- Content Canvas
- Horizontal Toolbar
- Vertical Toolbar
- 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.
Oracle Forms Basic Concepts
How many components in Forms?
Share this:
List of Tables for Forms Personalization
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;
Share this:
Payment Due Calculation on AP invoice
– Goods Received Date + Receipt Acceptance Days
– Invoice Date
– Terms Date
Payables populates the Payment Due Date with the most recent date among the above dates.
The logic is:
Most Recent( Goods Received Date + Receipt Acceptance Days, Invoice Date, Terms Date )
Goods Received Date:
Goods Received Date gets populated on the invoice based on Terms Date Basis. We can find Terms Date Basis at following levels.
– Supplier Site Level
– Supplier level
– Payables Options
Payables first looks for Terms Date Basis at Supplier Site Level. If there is no value exist here, it will look at Supplier Level. If there is no value here as well, then Payables takes this value from Payables Options.
Receipt Acceptance Days:
We can find Receipt Acceptance Days under Invoice Tab in Payables Options.
Invoice Date:
We can find the Invoice Date in Invoice Date field on Payables Invoice.
Terms Date:
Terms Date is the beginning date from which Payment Terms start when Payables calculates the scheduled payment(s) for an invoice. The invoice Terms Date defaults based on Terms Date Basis option you select:
• System: System date on day of invoice entry.
• Goods Received: The date you receive goods for invoices you match to purchase orders.
• Invoice: Invoice date.
• Invoice Received: Date you receive an invoice.
Payment Due Date calculation in case of a matched Invoice:
In case of a Matched invoice, Payables consider Receipt Transaction Date along with Goods Received Date + Receipt Acceptance Days, Invoice Date, and Terms Date.
The process includes two steps:
1. The system first checks for recent date of Goods Received Date+Receipt Acceptance Days and Receipt Transaction Date
The logic is:
Recent (GOODS RECEIVED DATE+RECEIPT ACCEPTANCE DAYS, RECEIPT TRANSACTION DATE)
2. After step1, the system uses the following logic again
Recent ( Recent Date from step1, Invoice Date, Terms Date)
Payables consider Receipt Date based on the “Recalculate Scheduled Payment” check box under Invoice Tab in Payables Options. If this check box is enabled, Payables consider the Receipt Date for the calculation of Payment Due Date. If you don’t want the system to use Receipt Date in the calculation of Payment Due Date, then disable “Recalculate Scheduled Payment”.
If you want recalculation, then system considers Receipt date for a matched invoice.
Navigation to enable/disable “Recalculate Scheduled Payment”:
Responsibility: Payables Responsibility which has the access to setups
Tab: Invoice
Share this:
Enable/Disable Concurrent program parameter based on Other Parameter
Below is the requirement
There are 2 valuesets parameter. If for parameter 1 user selects Yes then the other paramter should be enabled whereas if No is selected then the parameter should remain disabled. This can be achieved by using a hidden parameter as explained below.
Step1: Need 3 value sets for 3 parameter.
Value set1 = BOM_SRS_YES_NO (Oracle Defined)
Value Set2 = AMS_SRS_CHAR1 (Oracle Defined)
Value Set3 = SV_DEPENDENT_VS (User Defined)
Step2: Create Concurrent program as displayed in the screenshot below
Parameter1: Main Parameter
Parameter2: Hidden PArameter
Parameter3: Dependent Parameter
Step3: Assign concurrent program to a request group and test your program.
Share this:
FNDLOAD Syntax in R12
1) Concurrent Programs
2) Value sets
from one instance to another using FNDLOAD.
Now I will try to cover several other objects that can be migrated using FNDLOAD.
The syntax for moving any objects using FNDLOAD is almost the same except few changes. Following is the list of .lct files that are used for different objects
1) Concurrent Program –> afcpprog.lct
2) Value Sets –> afffload.lct
3) Menus –> afsload.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=’XXXX’
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt
4) Lookups –> aflvmlu.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=’XXXX’ LOOKUP_TYPE=’XXXX’
UploadFNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt
5) Flexfield –> afffload.lct
Descriptive FlexfieldDownload
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX APPLICATION_SHORT_NAME=’XXXX’ DESCRIPTIVE_FLEXFIELD_NAME=’XXXX’
Upload
FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
Key FlexfieldDownload
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX APPLICATION_SHORT_NAME=’XXXX’ DESCRIPTIVE_FLEXFIELD_NAME=’XXXX’
UploadFNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
6) Profile Options –> afscprof.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=’XXXX’ APPLICATION_SHORT_NAME=’XXXX’
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt
7) Responsibility –> afscursp.lct
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=’XXXX’
UploadFNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt
8) Request Groups –> afcpreqg.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=’XXXX’ APPLICATION_SHORT_NAME=’XXXX’ REQUEST_GROUP_UNIT UNIT_NAME=’XXXX’
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt
9) Menus –> afsload.lct
Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=’XXXX’
Upload
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt
10) Forms Personalization –> affrmcus.lct
Download
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES FUNCTION_NAME=’XXXX’
UploadFNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt
Share this: