Monday, 21 November 2011

Query to find Concurrent Program Respo and Request Group name


SELECT DISTINCT
  FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
  APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE
          FRG.APPLICATION_ID=fapp.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE :conc_prg_name
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';

Apps Profile Definition Table Info


Profile definition is stored in the following tables:
FND_PROFILE_OPTIONS
FND_PROFILE_OPTIONS_TL
These tables can be joined by column PROFILE_OPTION_NAME.

The value for a profile is stored in the following table:
FND_PROFILE_OPTION_VALUES
We can use the following statement to retrieve the value of a profile during run time:

l_profile_value := FND_PROFILE.VALUE(‘<Profile Short Name>’);

One of the most widely used profile is ‘MO: Operating Unit’ Profile. This profile has a code of ORG_ID. To get the value of current operating unit, use the following statement:

l_org_id := FND_PROFILE.VALUE(‘ORG_ID’);

To set a particular operating unit (for example, in SQLPLUS or TOAD), use the following PL/SQL code:
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(‘204’);
--204 is the ORG_ID value.
END;

Wednesday, 17 August 2011

WHO COLUMNS


WHO columns are used to track the information updated or inserted by the users against the tables. FND_STANDARD package is used for this purpose. FND_STANDARD.SET_WHO Procedure is used to update the WHO columns in a Table when a DML operation s (i.e. INSERT, UPDATE) performed.

        1) Created by

        2) Creation date

        3) Last _updated_by

        4) last_update_date

        5) last_update_login

Use fnd_profile.VALUE (‘USER_ID’) for retrieving the user_id which will be used by created_by column.
Creation date and last_update_date will be normally SYSDATE.
last_updated_by is same as created_by.
Use USERENV (‘SESSIONID’) for getting the last_update_login id.

Friday, 5 August 2011

Oracle Forms Questions


1. How do you control the constraints in forms? 

This can be done by selecting the Use Constrain Property to ON.

2.When will ON-VALIDATE-FIELD trigger executed? 

ON-VALIDATE-FIELD triggers are used for field validation. It fires when the field validation status is New or changed. However, if the field status is already valid then any further change to the value in the field will not fire this trigger.

3. What is the difference between system.current_field and system.cursor_field? 

The only difference between these two is that System.current_field gives name of the field and System.cursor_field gives name of the field with block name.

4. What are dynamic reports? How will you create them? 

Following steps should be followed to build a Dynamic Report:

Create a temporary table first.
Then insert data to the temporary table using the After Form Trigger.
Generate the report using this temporary table in the Data Model.
Delete all records from the temporary table in the After Report trigger.

5. Difference between Oracle Forms and Apps Forms.

Oracle Forms

Oracle Forms renders applications using metadata stored in an .fmx file.
It runs client-side PL/SQL.
It is accessed using a web browser and its user interface is rendered using a JVM.
It uses exact positioning.
It provides robust field-level validation and event processing.
It uses BI Beans as its integrated charting engine.
It supports a range of locking models with pessimistic as the default.
Each connected user in It maintains a synchronous connection to the Oracle database.
It uses synchronous connections to allow transactions to span multiple screen interactions.
With Oracle Forms, Application logic is processed in the Oracle database, a mid-tier Forms Server, or in the rich client.

Apps Forms

It renders applications using metadata stored in an Oracle database.
It uses server-side PL/SQL.
It is also invoked from a Web browser but its user interface is HTML and JavaScript.
It uses HTML-relative positioning.
It supports declarative page-level validation and event processing. Programmatic field-level validation and event processing requires Javascript and AJAX.
It uses Flash Charts as its integrated charting engine.
Due to its asynchronous architecture, It uses an optimistic locking model.
It does not transparently allow transactions to span page views. It programmatically supports transactions spanning page views using collections.
Its users are asynchronously connected to the Oracle database.
With Apps Forms, PL/SQL application logic is processed within the Oracle database. Client-side logic is implemented using JavaScript. HTTP communications are facilitated using Apache and Mod/PLSQL.

6. Sequence of firing triggers in forms. 

The following sequence should be used to fire triggers when a form opens:

1. Pre-Form
2. Pre-Block
3. Pre-Record
4. Pre-Text-Item
5. When-New-Form-Instance
6. When-New-Block-Instance
7. When-New-Record-Instance
8. When-New-Item-Instance

The folowing sequence should be followed when you exit from the form:

1. post text item
2. post record
3. post block
4. post form

7. What is a record Group? What are different types of record group? 

A record group is an oracle forms data structure that has a column/row framework similar to a database table.

There are three types of record groups: query record groups, non-query record groups, and static record groups.

Query record group
A query record group is a record group that has an associated SELECT statement. Query record groups can be created and modified at design time or at runtime.

Non-query record group
A non-query record group is a group that does not have an associated query. Non-query record groups can be created and modified only at runtime.

Static record group
A static record group is not associated with a query. Static record groups can be created and modified only at design time.

8. What is a trace file and how is it created? 

The files that are created when an oracle background process encounters an exception are Trace files.

Processes like dbwr, lgwr, pmon, smon create them.

Trace files are also created by ORA-00600 error or due to some diagnostic dump events

9. Can a single canvas have multiple windows ? 
ANs. NO, In a window we have more than one canvas .


Use of FND_GLOBAL.APPS_INITIALIZE


FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API's in Oracle Ebusiness suite. 


- Use the API FND_GLOBAL.APPS_INITIALIZE to set the applications context in standalone sessions that were not initialized through normal means. 


- Typically, you would use this API in external custom programs that are establishing their own connections.


- Its not required for all the API's but its recommended that you set this profile before making any calls to either private or public API.




- Listed below is a sample call to FND_GLOBAL.APPS_INITIALIZE function


fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
resp_id=>l_resp_id,
resp_appl_id=>l_resp_appl_id);


1. l_user_id is the fnd user ID which will be utilized during the call.
2. l_resp_id is the responsibility ID
3. l_resp_appl_id is the responsibility application ID.


- This procedure sets up global variables and profile values in a database session. 


- Call this procedure to initialize the global security context for a database session.


- You can also use this procedure to set up a database session for manually testing application code using SQL*Plus. 

Various Canvas Types in Oracle Form


Various Canvas Types in Oracle Form

What is a Canvas?
A canvas is a surface inside a window container on which you place visual objects such as interface items and graphics. It is similar to the canvas upon which a picture is painted. To see a canvas and its contents at run time, you must display it in a window. A canvas always displays in the window to which it is assigned.
Canvas Types
Oracle Forms provides four types of canvases, all of which can be displayed in the same window at runtime. A canvas’ type defines how Oracle Forms will display it in the window to which it is assigned. When you create a canvas, you specify its type by setting the Canvas Type property.
The four canvas types are:
  • Content
  • Stacked
  • Tab
  • Toolbar
Content Canvas:
The most common canvas type is the content canvas (the default type). A content canvas is the “base” view that occupies the entire content pane of the window in which it is displayed. You must define at least one content canvas for each window you create.
When building an application, one of the first steps is to create content canvases that will be displayed in the windows of your form(s). While you can assign more than one content canvas to the same window at design time, at runtime only one content canvas is displayed at one time in the window.
Stacked Canvas:
A stacked canvas is displayed at top—or stacked on—the content canvas assigned to the current window. Stacked canvases obscure some part of the underlying content canvas, and often are shown and hidden programmatically. You can display more than one stacked canvas in a window at the same time.
Stacked canvases are displayed in a window along with the window’s content canvas(es) and any number of other stacked canvases. You can set the bevel, color, and pattern attributes of a stacked canvas to make it look different than the underlying content canvas.
Creating a stacked canvas is similar to creating a content canvas. To define a stacked canvas, you need to set certain canvas properties that apply only to stacked canvases, and create items and boilerplate text and graphics as you would for a content canvas. To convert an existing content canvas to a stacked canvas, simply change its Canvas Type property from Content to Stacked.
Tab Canvas:
A tab canvas—made up of one or more tab pages—allows you to group and display a large amount of related information on a single dynamic Oracle Forms canvas object. Like stacked canvases, tab canvases are displayed on top of a content canvas, partly obscuring it. Tab pages (that collectively comprise the tab canvas) each display a subset of the information displayed on the entire tab canvas.
A tab canvas can have many tab pages, and must have at least one. Think of tab pages as the folders in a filing system. Each individual tab page (folder) has a labelled tab that developers and end users click to access the page. At design time or runtime, you click the labelled tab to display the page at the front of the tab canvas, thereby obscuring any other page(s).
Tab pages are sub-objects of a tab canvas. Like the canvas to which it is attached, each tab page has properties; similarly, any item you place on a tab canvas has a canvas property as well as tab page properties. The ordering of tab pages in the Object Navigator determines the left-to-right (or top-to-bottom) order of the tabs at runtime.
Toolbar Canvas:
A toolbar canvas often is used to create toolbars for individual windows. You can create two types of toolbar canvases: horizontal or vertical. Horizontal toolbar canvases are displayed at the top of a window, just under its menu bar, while vertical toolbars are displayed along the far left edge of a window.
You can create toolbar canvases, both horizontal and vertical, for any window in a form. Oracle Forms displays horizontal toolbar canvases across the top of a window, and vertical toolbar canvases on the left edge of a window.
When you create a toolbar canvas, you assign it to a window by setting the canvas Window property, and then register it with the window by setting the window’s Vertical Toolbar Canvas or Horizontal Toolbar Canvas properties as appropriate. You can change the appearance of a toolbar at runtime by dynamically showing and hiding different items on the toolbar. You also can create more than one toolbar for the same window, and display them in response to navigation events and programmatic control, much like stacked canvases assigned to the same window.

Modal & Modeless Windows in Oracle Forms


Modal & Modeless Windows in Oracle Forms

A window in oracle forms is a container for all visual objects that make up a Forms application. You can create two different type of windows in oracle forms. Lets have a brief comparisons between these two types of windows.
Modal Window:
modal window is a restricted window that the user must respond to before moving the input focus to another window. Modal windows:
  • Must be dismissed before control can be returned to a modeless window
  • Become active as soon as they display
  • Require a means of exit or dismissal
Modeless Window:
modeless window is an unrestricted window that the user can exit freely. Modeless windows:
  • Can display many at once
  • Are not necessarily active when displayed
  • Are the default window type

Triggers in Oracle Forms



Triggers in Oracle Forms

Triggers are blocks of PL/SQL code that are written to perform tasks when a specific event occurs within an application. In effect, an Oracle Forms trigger is an event-handler written in PL/SQL to augment (or occasionally replace) the default processing behavior. Every trigger has a name, and contains one or more PL/SQL statements. A trigger encapsulates PL/SQL code so that it can be associated with an event and executed and maintained as a distinct object.

Block Processing Triggers:

Block processing triggers fire in response to events related to record management in a block.
  • When-Create-Record Perform an action whenever Oracle Forms attempts to create a new record in a block.
  • When-Clear-Block Perform an action whenever Oracle Forms flushes the current block; that is, removes all records from the block.
  • When-Database-Record Perform an action whenever Oracle Forms changes a record’s status to Insert or Update, thus indicating that the record should be processed by the next COMMIT_FORM operation.

Interface Event Triggers:

Interface event triggers fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control.
  • When-Button-Pressed Initiate an action when an operator selects a button, either with the mouse or through keyboard selection.
  • When-Checkbox-Changed Initiate an action when the operator toggles the state of a check box, either with the mouse or through keyboard selection.
  • When-Image-Activated Initiate an action whenever the operator double-clicks an image item.
  • When-Image-Pressed Initiate an action whenever an operator clicks on an image item.
  • When-Radio-Changed Initiate an action when an operator changes the current radio button selected in a radio group item.
  • When-Window-Activated Initiate an action whenever an operator or the application activates a window.
  • When-Window-Closed Initiate an action whenever an operator closes a window with the window manager’s Close command.
  • When-Window-Deactivated Initiate an action whenever a window is deactivated as a result of another window becoming the active window.

Master/Detail Triggers:

Oracle Forms generates master/detail triggers automatically when a master/detail relation is defined between blocks. The default master/detail triggers enforce coordination between records in a detail block and the master record in a master block. Unless developing custom block-coordination schemes, you do not need to define these triggers.
  • On-Check-Delete-Master Fires when Oracle Forms attempts to delete a record in a block that is a master block in a master/detail relation.
  • On-Clear-Details Fires when Oracle Forms needs to clear records in a block that is a detail block in a master/detail relation because those records no longer correspond to the current record in the master block.
  • On-Populate-Details Fires when Oracle Forms needs to fetch records into a block that is the detail block in a master/detail relation so that detail records are synchronized with the current record in the master block.

Message-Handling Triggers:

Oracle Forms automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events.
  • On-Error Replace a default error message with a custom error message, or to trap and recover from an error.
  • On-Message To trap and respond to a message; for example, to replace a default message issued by Oracle Forms with a custom message.

Validation Triggers:

Validation triggers fire when Oracle Forms validates data in an item or record. Oracle Forms performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation.
  • When-Validate-Item
  • When-Validate-Record

Navigational Triggers:

Navigational triggers fire in response to navigational events. Navigational triggers can be further sub-divided into two categories: Pre- and Post- triggers, and When-New-Instance triggers. Pre- and Post- Triggers fire as Oracle Forms navigates internally through different levels of the object hierarchy. When-New-Instance-Triggers fire at the end of a navigational sequence that places the input focus on a different item.
  • Pre-Form Perform an action just before Oracle Forms navigates to the form from “outside” the form, such as at form startup.
  • Pre-Block Perform an action before Oracle Forms navigates to the block level from the form level.
  • Pre-Record Perform an action before Oracle Forms navigates to the record level from the block level.
  • Pre-Text-Item Perform an action before Oracle Forms navigates to a text item from the record level.
  • Post-Text-Item Manipulate an item when Oracle Forms leaves a text item and navigates to the record level.
  • Post-Record Manipulate a record when Oracle Forms leaves a record and navigates to the block level.
  • Post-Block Manipulate the current record when Oracle Forms leaves a block and navigates to the form level.
  • Post-Form Perform an action before Oracle Forms navigates to “outside” the form, such as when exiting the form.
  • When-New-Form-Instance Perform an action at form start-up. (Occurs after the Pre-Form trigger fires).
  • When-New-Block-Instance Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.
  • When-New-Record-Instance Perform an action immediately after the input focus moves to an item in a different record.
  • When-New-Item-Instance Perform an action immediately after the input focus moves to a different item. 

Transactional Triggers:

Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source.
  • On-Delete
  • On-Insert
  • On-Update
  • On-Logon
  • On-Logout
  • Post-Database-Commit
  • Post-Delete
  • Post-Insert
  • Post-Update
  • Pre-Commit
  • Pre-Delete
  • Pre-Insert
  • Pre-Update

Query-Time Triggers:

Query-time triggers fire just before and just after the operator or the application executes a query in a block.
  • Pre-Query Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.
  • Post-Query Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. Fires once for each record fetched into the block.