f Get Oracle Apps Traning Here: November 2006

This work is licensed under a Creative Commons -NonCommercial 2.5 License.



Sunday, November 26, 2006

Develop Oracle Reports concurrent program from Scratch

Please find an article about that explains how to develop Oracle Reports from Scratch.

To take you a step beyond, it also contains step by step approach for using XML Publisher with Oracle Reports [Concurrent Program]

XML Publisher based Concurrent Program Report

Monday, November 20, 2006

How to customize Oracle Apps Reports

A step by step approach for customization of Oracle Apps Reports has been listed in this link

Thanks,
Anil Passi

Thursday, November 16, 2006

Article on Key flexfields in Oracle Apps

The article for KeyFlexfields can be accessed via the below Link.

Key Flexfields Article



Thanks,
Anil Passi

Wednesday, November 15, 2006

Data migration of People records in HR

During next week, I will be covering working examples of various APIs in Oracle HRMS that can be used during data migration.

The first article in that series can be accessed from this link

Case insensitive query in Oracle Apps

This is response to a question posted by a comment in Article..Why Is it Called Apps
Click on the field in question, and click on Help/Diagnostic/Properties/Item



In the property field, select case insensitive query


If the value is TRUE, then you will know that Case Insensitive Query is possible on this field.

Monday, November 13, 2006

Apps Data Migration - Example Program

Please find the link below, that explains how to design and build programs for doing data migration in Oracle Applications.


Example for Data Migration Program


Thursday, November 02, 2006

Steps for your first pl/sql Concurrent Program in Oracle Apps

I think this topic is already covered partially in one of the previous training lesson[ for concurrent programs], but I would like to touch base on this again.

Lets revisit some basics first

Question: What are the ingredients for a concurrent program?
Answer: A concurrent executable and a program attached to that executable.

Question: Will executable for a pl/sql concurrent program be a database stored procedure?
Answer: Yes, but in addition to above you can also register a procedure within a package as an executable. However, you can't make a Function to become the executable for a stored procedure.

Question: Does this stored procedure need to have some specific parameters, in order to become an executable of a concurrent program?
Answer: Yes, such procedure must have at least two parameters
( errbuff out VARCHAR2, retcode out NUMBER)

Question: Can we add additional parameters to such pl/sql procedures that happen to be Conc Prog Executables?
Answer: Sure you can, but those parameters must be defined after the first two parameters. Effectively I mean first two parameters must always be errbuff and retcode. The sequence of the remaining parameters must match with the sequence in which parameters are registered in define concurrent program-parameters window.

Question: Can those parameters be validated or will these parameters be free text?
Answer: These parameters can be attached to a value set, hence this will avoid users passing free text values.

Question: What are the possible things that a concurrent pl/sql program can do?
Answer: Firstly your stored procedure would have been created in apps. This concurrent program will connect to "apps schema" from where access to all the tabes across every module will be available.
You can do the following:-
1. Insert records in tables(usually interface or temp tables)
2. Update and delete records
3. Initiate workflows
4. Display messages in the output file or the log file of the concurrent program.
5. Make this concurrent program complete with status Error or Warning or Normal.

Question: Please give me an example of a pl/sql concurrent program in Oracle apps in real life?
Answer: Lets say you have an external application which is integrated with apps. Assume that it is Siebel application where the new customer records are created. Siebel is assumingly hosted on a Oracle database from where it has database links to your Oracle apps database.
In this case, siebel inserts sales order records into your custom staging tables.
You can then develop a concurrent process which will do the following:--------
Loop through the records in that staging table
Check if the customer already exists in Oracle AR TCA
If customer already exists, thencall the api to update existing customer
If this is a new customer, then update existing TCA Customer/Party record

Question: Ok, how do I do the above?
Answer: Find the steps below for doing so

Step 1
Connect xxschema/password
Create table xx_stage_siebel_customers ( customer_Id integer, customer name varchar2(400));
Grant all on xx_stage_siebel_customers to apps ;

Step 2
Connect apps/apps_password
Create or replace synonym xx_stage_siebel_customers for xxschema.xx_stage_siebel_customers ;

Step 3 ( again in apps schema)
Create or replace procedure xx_synch_siebel_cust ( errbuff out varchar2, retcode out varchar2 ) is
n_ctr INTEGER := 0 ;
Begin
for p_rec in ( select * from
xx_synch_siebel_cust ) LOOP
Select count(*) into n_ctr from hz_parties where party_number = p_rec.customer_number;
If n_ctr=0 then
Hz_party_create(pass appropriate parameters here).
Else
Hz_party_update(
pass appropriate parameters here);
End if;
END LOOP ;
delete from
xx_synch_siebel_cust ;
End xx_synch_siebel_cust

Step 4
Create concurrent program executable ( for example of screenshot, visit link )

Step 5
Create concurrent program for that executable

Step 6
Add this concurrent program to request group

Now your program is ready to roll....

Advanced Oracle Apps Training Index

Dear All,

In response to a query that I received asking "Once all the training index topics have been covered, will this blog be stopped?"

Other readers might have had a similar question in mind.

Just so that you know, once these topics have been exhausted, we will then work upon an "Advanced Apps Training" index, one that will have articles influenced by real life problems faced during implementation and their resolutions.

Thanks

Oracle Forms Basic Concepts

ARTICLE AUTHOR: Rakesh Sreenivasa

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.

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

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

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:

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.

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.

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;

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.

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.

Conclusion:

I started creating this document to include the necessary concepts required for a consultant who wants to harness his skills on Oracle Forms. I have tried to include most of the necessary topics required before jumping to develop a form. For further details on Forms, you can always refer the Form Builder Help Topics.

ARTICLE AUTHOR: Rakesh Sreenivasa

Wednesday, November 01, 2006

New Custom Form in Apps -Article Added

Please find the training article on how to create a "Custom Form" from scratch in Oracle Apps.
I assume that you have followed the instructions in previous chapters regarding TEMPLATE.fmb.


Link for the article


Your feedback is precious, in helping us improve the quality of the training material. Based upon the feedback, this article will be amened to add further details where required.

Welcome Rakesh Sreenivasa

For the very first time, I received an email from a reader that wanted to contribute to this blog.

I would like to Welcome Rakesh as a contributor to this blog. Rakesh Sreenivasa will be publishing some documents on Oracle Apps Forms and Apps Reports, and who knows...may be more.

Hence I will add Rakesh as a contributor to this blog. Welcome on board Rakesh.

IMPORTANT NOTE:- I received another email where someone desired to use AudioVisual Workflow Training material of the blog in their training course. Surely I have denied them this request, because I do not want the knowledge on this blog/website to be sold. This information is free and should remain free. In case anyone ever discovers a professional training course using the material on this website in exchange of money, then please bring that to my attention by mailing me at anilpassi@gmail.com